Search for:
Dec 28 2021
BIP – Extract Absence Entry data in HDL Format

The below query extracts the minimum required attributes for absence entry update. In the current example, the absence status is updated to Withdrawn. You can make the changes as per your need:

SELECT DATA_ROW
FROM (
SELECT 'METADATA'
|| CHR (124) || 'PersonAbsenceEntry'
|| CHR (124) || 'PerAbsenceEntryId'
|| CHR (124) || 'AbsenceStatus'
|| CHR (124) || 'AbsenceTypeId'
|| CHR (124) || 'AssignmentId'
|| CHR (124) || 'EmployerId'
|| CHR (124) || 'PersonId'
|| CHR (124) || 'StartDate'
|| CHR (124) || 'EndDate'
|| CHR (124) || 'SourceSystemId' 
|| CHR (124) || 'SourceSystemOwner'  AS DATA_ROW
FROM DUAL
UNION all
select  'MERGE'
|| CHR (124) || 'PersonAbsenceEntry'
|| CHR (124) || apae.per_absence_entry_id
|| CHR (124) || 'ORA_WITHDRAWN'
|| CHR (124) || apae.absence_type_id
|| CHR (124) || apae.assignment_id
|| CHR (124) || apae.legal_entity_id
|| CHR (124) || apae.person_id
|| CHR (124) || to_char(apae.start_date,'RRRR/MM/DD')
|| CHR (124) || to_char(apae.end_date,'RRRR/MM/DD')
|| CHR (124) || hikm.source_system_id
|| CHR (124) || hikm.source_system_owner
FROM ANC_PER_ABS_ENTRIES apae,
	 HRC_INTEGRATION_KEY_MAP hikm
WHERE hikm.surrogate_id = apae.per_absence_entry_id
  --AND apae.absence_type_id = 3000012211
   AND apae.per_absence_entry_id = 300000079292534
)

Sample HDL File:

METADATA|PersonAbsenceEntry|PerAbsenceEntryId|AbsenceStatus|AbsenceTypeId|AssignmentId|EmployerId|PersonId|StartDate|EndDate|SourceSystemId|SourceSystemOwner
MERGE|PersonAbsenceEntry|300000079292534|ORA_WITHDRAWN|300000076789541||300000048608295|300000049253269|2014/06/11|2014/06/13|300000079292534|FUSION
Mandeep Gupta 0 Comments
Dec 20 2021
HDL – Sample file to load Job Model profile data
METADATA|TalentProfile|ProfileId|ProfileCode|Description|PersonId|PersonNumber|Summary|ProfileTypeCode|ProfileTypeId|ProfileStatusCode|ProfileUsageCode|SourceSystemId|SourceSystemOwner
MERGE|TalentProfile||JOB_PROFILE_CODE1|Job Profile for CODE1||||JOB||A|M|TP_JOB_CODE1|HRC_SQLLOADER

METADATA|ProfileRelation|ProfileRelationId|BusinessUnitName|JobCode|JobFamilyName|JobSetCode|LocationCode|LocationSetCode|OrganizationName|PositionCode|ProfileCode|RelationCode|ProfileId|LearningItemNumber|ObjectEffectiveEndDate|ObjectEffectiveStartDate|JobFamilyId|JobId|LearningItemId|LocationId|OrganizationId|PositionId|SourceSystemId|SourceSystemOwner
MERGE|ProfileRelation|||CODE1||COMMON|||||JOB_PROFILE_CODE1|JOB||||2021/01/01|||||||PR_JOB_CODE1|HRC_SQLLOADER

METADATA|ProfileItem|ProfileCode|ContentItem|ContentItemId|ContentType|ContentTypeId|CountryCountryCode|CountryGeographyCode|DateFrom|Importance|InterestLevel|ItemDate1|ItemDate2|RatingModelCode1|RatingModelId1|SectionId|SectionName|DateTo|SourceSystemId|SourceSystemOwner
MERGE|ProfileItem|JOB_PROFILE_CODE1|Process Oriented||COMPETENCY||||2021/01/01|2||||PROFICIENCY|||||PI_JOB_CODE1|HRC_SQLLOADER
Mandeep Gupta 0 Comments
Dec 20 2021
BIP – Query to extract child positions for a logged in person’s position

Fusion HCM provides you the capability of using Position Trees as well as Position Hierarchies. In case you need to extract child position details based on position id of logged in person, you can make use of below query:

SELECT pphf.position_id
  FROM per_position_hierarchy_f pphf
 WHERE 1=1
  START WITH pphf.parent_position_id = NVL((select position_id from per_all_assignments_m paam
                                         where paam.assignment_type = 'E'
					   and paam.person_id = hrc_session_util.get_user_personid
					   and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
					   and paam.effective_latest_change = 'Y'
					   and paam.effective_sequence = 1),300000141634831)
CONNECT BY PRIOR pphf.parent_position_id = 	pphf.position_id
Mandeep Gupta 0 Comments
Dec 19 2021
HDL – Sample HDL to load rehire Workers

Below is the sample file (tested on an older version) to load rehire worker records but the structure should remain same. Few attributes may be deprecated now, so you may get an error. Please remove those and it should work fine.

METADATA|Worker|PersonId|EffectiveStartDate|EffectiveEndDate|PersonNumber|StartDate|DateOfBirth|CountryOfBirth|TownOfBirth|CategoryCode|ActionCode|ReasonCode|GUID|SourceSystemOwner|SourceSystemId

MERGE|Worker||2002/10/07|4712/12/31|10090|2002/10/07|1976/01/02||||HIRE|||HRC_SQLLOADER|1190_PERSON



METADATA|PersonName|PersonNameId|EffectiveStartDate|EffectiveEndDate|PersonId(SourceSystemId)|PersonNumber|LegislationCode|NameType|FirstName|MiddleNames|LastName|Honors|KnownAs|PreNameAdjunct|Suffix|MilitaryRank|PreviousLastName|Title|SourceSystemOwner|SourceSystemId

MERGE|PersonName||2002/10/07|4712/12/31|1190_PERSON||US|GLOBAL|Steven|Flord|Smith|||||||MR.|HRC_SQLLOADER|1190_PERSON_NAME


METADATA|WorkRelationship|PersonNumber|LegalEntityId|DateStart|PersonId(SourceSystemId)|PrimaryFlag|LegalEmployerName|ActionCode|WorkerType|ProjectedTerminationDate|ActualTerminationDate|SourceSystemOwner|SourceSystemId

MERGE|WorkRelationship|10090||2002/10/07|1190_PERSON|Y|ABC Limited|HIRE|E||2008/12/07|HRC_SQLLOADER|283_WORK_RELATIONSHIP

MERGE|WorkRelationship|10090||2015/02/02|1190_PERSON|Y|ABC Limited|REHIRE|E|||HRC_SQLLOADER|101062_WORK_RELATIONSHIP



METADATA|WorkTerms|AssignmentId|AssignmentNumber|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|PeriodOfServiceId(SourceSystemId)|PersonId(SourceSystemId)|PersonNumber|LegalEmployerName|DateStart|AssignmentName|AssignmentStatusTypeCode|AssignmentType|BusinessUnitShortCode|PositionOverrideFlag|PrimaryWorkTermsFlag|ActionCode|WorkerType|PersonTypeCode|SystemPersonType|SourceSystemOwner|SourceSystemId

MERGE|WorkTerms||ET10090|2002/10/07|2008/12/07|1|Y|283_WORK_RELATIONSHIP|1190_PERSON||ABC Limited|2002/10/07||ACTIVE_PROCESS|ET|ABC Business Unit|||HIRE|E|Employee|EMP|HRC_SQLLOADER|283_WORK_TERM

MERGE|WorkTerms||ET10090|2008/12/08|4712/12/31|1|Y|283_WORK_RELATIONSHIP|1190_PERSON||ABC Limited|2002/10/07||INACTIVE_PROCESS|ET|ABC Business Unit|||TERMINATION||||HRC_SQLLOADER|283_WORK_TERM

MERGE|WorkTerms||ET10090-2|2015/02/02|2017/06/30|1|Y|101062_WORK_RELATIONSHIP|1190_PERSON||ABC Limited|2015/02/02||ACTIVE_PROCESS|ET|ABC Business Unit|||ASG_CHANGE|E|Employee|EMP|HRC_SQLLOADER|12608_WORK_TERM

MERGE|WorkTerms||ET10090-2|2017/07/01|4712/12/31|1|Y|101062_WORK_RELATIONSHIP|1190_PERSON||ABC Limited|2015/02/02||ACTIVE_PROCESS|ET|ABC Business Unit|||ASG_CHANGE|E|Employee|EMP|HRC_SQLLOADER|12608_WORK_TERM



METADATA|Assignment|ActionCode|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|WorkTermsAssignmentId(SourceSystemId)|WorkTermsNumber|AssignmentType|AssignmentName|AssignmentNumber|AssignmentStatusTypeCode|BusinessUnitShortCode|DateProbationEnd|WorkerCategory|AssignmentCategory|PermanentTemporary|FullPartTime|InternalFloor|GradeCode|JobCode|LocationCode|NormalHours|Frequency|DepartmentName|PeriodOfServiceId(SourceSystemId)|PersonId(SourceSystemId)|PositionCode|PrimaryAssignmentFlag|PrimaryFlag|PersonTypeCode|SystemPersonType|EstablishmentId|NoticePeriod|NoticePeriodUOM|ProbationPeriod|ProbationUnit|HourlySalariedCode|LabourUnionMemberFlag|ManagerFlag|EndTime|StartTime|WorkAtHomeFlag|SourceSystemOwner|SourceSystemId

MERGE|Assignment|HIRE|2002/10/07|2008/12/07|1|Y|283_WORK_TERM||E||E10090|ACTIVE_PROCESS|ABC Business Unit||||||||||||ABC Limited|283_WORK_RELATIONSHIP|1190_PERSON||Y|Y|Employee|EMP||||||||||||HRC_SQLLOADER|283_ASSIGNMENT

MERGE|Assignment|TERMINATION|2008/12/08|4712/12/31|1|Y|283_WORK_TERM||E||E10090|INACTIVE_PROCESS|ABC Business Unit|||||||72|128|343|||ABC Maintenance|283_WORK_RELATIONSHIP|1190_PERSON||Y|Y||||||||||||||HRC_SQLLOADER|283_ASSIGNMENT

MERGE|Assignment|REHIRE|2015/02/02|2017/06/30|1|Y|12608_WORK_TERM||E||E10090-2|ACTIVE_PROCESS|ABC Business Unit|||||||67|173|142|||ABC Department|101062_WORK_RELATIONSHIP|1190_PERSON|165061|Y|Y|Employee|EMP|||||||N|N||||HRC_SQLLOADER|12608_ASSIGNMENT

MERGE|Assignment|ASG_CHANGE|2017/07/01|4712/12/31|1|Y|12608_WORK_TERM||E||E10090-2|ACTIVE_PROCESS|ABC Business Unit|||||||67|173|142||ABC Department|101062_WORK_RELATIONSHIP|1190_PERSON|627106|Y|Y|Employee|EMP|||||||N|N||||HRC_SQLLOADER|12608_ASSIGNMENT



METADATA|PersonLegislativeData|PersonLegislativeId|EffectiveStartDate|EffectiveEndDate|PersonId(SourceSystemId)|LegislationCode|HighestEducationLevel|MaritalStatus|MaritalStatusDate|Sex|GUID|SourceSystemOwner|SourceSystemId

MERGE|PersonLegislativeData||2002/10/07|4712/12/31|1190_PERSON|US||M||M||HRC_SQLLOADER|1190_PERSON_LEGISLATIVE
Mandeep Gupta 0 Comments
Dec 16 2021
BIP – Extract Length of service

Use the below query to extract the length of service of a worker. In case of rehires this becomes little tricky. Below query makes use of earliest hire date for length of service calculation. If Seniority dates are implemented, then you can avoid using below query:

select papf.person_number, papf.person_id, trunc(months_between(trunc(sysdate),MIN(ppos.date_start))/12,2) LOS
  from per_periods_of_Service ppos
      ,per_all_people_f papf
 where ppos.person_id = papf.person_id
   and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
group by papf.person_number, papf.person_id
Mandeep Gupta 0 Comments
Dec 16 2021
BIP – Query to extract department Tree

Use the following query to extract department tree and its child departments. Please pass correct department name before executing:

WITH org_tree
AS (
        SELECT /*+ materialize */
               DISTINCT *
        FROM (
               SELECT  (
                               SELECT haoufv_p.name
                                 FROM hr_all_organization_units_f_vl haoufv_p
                                WHERE haoufv_p.organization_id = potnv.parent_organization_id
                                  AND TRUNC(SYSDATE) BETWEEN haoufv_p.effective_start_date AND haoufv_p.effective_end_date
                        ) parent_org_name
                      ,(
                               SELECT haoufv_c.name
                                 FROM hr_all_organization_units_f_vl haoufv_c
                                WHERE haoufv_c.organization_id = potnv.organization_id
                                  AND TRUNC(SYSDATE) BETWEEN haoufv_c.effective_start_date AND haoufv_c.effective_end_date
                        ) child_org_name
                       ,potnv.tree_structure_code
                       ,potnv.parent_organization_id parent_org_id
                       ,potnv.organization_id child_org_id
                       ,LEVEL levelcount
               FROM per_dept_tree_node_v potnv
			       ,fnd_tree_version ftv
              WHERE potnv.tree_structure_code = 'PER_DEPT_TREE_STRUCTURE'
			    AND potnv.tree_code = 'Global100'
				AND potnv.tree_version_id = ftv.tree_version_id
				AND ftv.tree_code = potnv.tree_code
				AND ftv.status = 'ACTIVE' 
				AND TRUNC(SYSDATE) BETWEEN ftv.effective_start_date AND ftv.effective_end_date
			   START WITH potnv.parent_organization_id IS NULL 
			 CONNECT BY PRIOR potnv.organization_id = potnv.parent_organization_id
               )
        ORDER BY levelcount ASC
        )
,dept_tree
 AS (
        SELECT /*+ materialize */
              level1.child_org_name "level1"
             ,level2.child_org_name "level2"
             ,level3.child_org_name "level3"
             ,level4.child_org_name "level4"
         FROM org_tree level1
		     ,org_tree level2
		     ,org_tree level3
		     ,org_tree level4
			 ,hr_all_organization_units_f haouf
       WHERE level1.child_org_id = level2.parent_org_id
	     AND level2.child_org_id = level3.parent_org_id
	     AND level3.child_org_id = level4.parent_org_id
         AND level1.parent_org_name IS NULL
		 AND haouf.organization_id = level4.child_org_id
		 AND TRUNC(SYSDATE) BETWEEN haouf.effective_start_date AND haouf.effective_end_date
        )
SELECT *
FROM dept_tree
Mandeep Gupta 0 Comments
Dec 16 2021
HDL – Sample HDL file to update Organization DFF

Use any of the below formats of Organization HDL to update descriptive flexfield attribute:

Using Surrogate IDs:

METADATA|Organization|OrganizationId|EffectiveStartDate|EffectiveEndDate|FLEX:PER_ORGANIZATION_UNIT_DFF|testAttribute(PER_ORGANIZATION_UNIT_DFF=Global Data Elements)
MERGE|Organization|3000000001230139|1951/01/01|4712/12/31|Global Data Elements|Xyz	

METADATA|OrgUnitClassification|OrgUnitClassificationId|OrganizationId|EffectiveStartDate|EffectiveEndDate
MERGE|OrgUnitClassification|3000000001233056|3000000001230139|1951/01/01|4712/12/31

Using User Keys:

METADATA|Organization|Name|ClassificationName|EffectiveStartDate|EffectiveEndDate|FLEX:PER_ORGANIZATION_UNIT_DFF|testAttribute(PER_ORGANIZATION_UNIT_DFF=Global Data Elements)
MERGE|Organization|Test Organization|Department|1951/01/01|4712/12/31|Global Data Elements|Xyz	

METADATA|OrgUnitClassification|OrganizationName|ClassificationName|SetCode|EffectiveStartDate|EffectiveEndDate
MERGE|OrgUnitClassification|Test Organization|Department|COMMON|1951/01/01|4712/12/31

Version Validated :- 21D

Mandeep Gupta 0 Comments