Search for:
Apr 21 2022
HDL – Query to DELETE Assigned Payroll data from Fusion

In case, you need to delete assigned payroll data for masking purpose in lower environment or for some other reason like correction etc, use below query to pull the data in HDL format:

SELECT 'METADATA|AssignedPayroll|AssignedPayrollId|EffectiveStartDate|AssignmentId|LegislativeDataGroupId|PayrollId|PayrollTermId|SourceSystemId|SourceSystemOwner' HEADER, 1 DATA_ORDER
  FROM DUAL
UNION ALL
SELECT 'DELETE|AssignedPayroll'
	   ||'|'||
       papd.ASSIGNED_PAYROLL_ID
	   ||'|'||
       to_char(papd.start_date,'RRRR/MM/DD')
	   ||'|'||	   
	   paam.assignment_id
	   ||'|'||	   
	   papd.LEGISLATIVE_DATA_GROUP_ID
	   ||'|'||	   
	   papd.Payroll_Id
	   ||'|'||	   
	   papd.Payroll_Term_Id
	   ||'|'||	   
       hikm.source_system_id
	   ||'|'||	   
       hikm.source_system_owner HEADER, 2 DATA_ORDER
  FROM pay_assigned_payrolls_dn papd
	  ,pay_payroll_terms ppt
	  ,pay_pay_relationships_dn prrd
	  ,pay_all_payrolls_f papf_pay
	  ,pay_rel_groups_dn prgd
	  ,pay_rel_groups_f prgf
	  ,per_all_people_f papf
	  ,per_person_names_f ppnf
	  ,per_all_assignments_m paam 
	  ,hrc_integration_key_map hikm
 WHERE papd.payroll_term_id = ppt.payroll_term_id
   AND ppt.payroll_relationship_id = prrd.payroll_relationship_id
   AND papd.payroll_id = papf_pay.payroll_id
   AND prrd.person_id = papf.person_id
   AND prrd.payroll_relationship_id = prgd.payroll_relationship_id
   AND prgd.relationship_group_id = prgf.relationship_group_id
   AND prgd.assignment_id = paam.assignment_id
   AND paam.effective_latest_change = 'Y'
   AND prgd.group_type = 'A'
   AND ppnf.person_id = papf.person_id
   AND ppnf.name_type = 'GLOBAL'
   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN prgf.effective_start_date AND prgf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
   AND hikm.surrogate_id = papd.ASSIGNED_PAYROLL_ID
   AND trunc(papd.creation_date) < trunc(sysdate)-1
   --AND papf.person_number = '11111'
 ORDER BY 2

Sample HDL File:

METADATA|AssignedPayroll|AssignedPayrollId|EffectiveStartDate|AssignmentId|LegislativeDataGroupId|PayrollId|PayrollTermId|SourceSystemId|SourceSystemOwner
DELETE|AssignedPayroll|300000012343876|2022/01/19|300000046430123|300000004188123|300000014605111|300000046430342|ASGN_PAYROLL_12345|EBS-HR
Mandeep Gupta 0 Comments
Mar 21 2022
HDL – Load Position Model Profile Extra Info

For Position Model profiles, there are additional attributes like Description, Qualification and Responsibilities. To load these, one should make use of ModelProfileExtraInfo child business object of TalentProfile.dat.

Attributes values for Description, Qualification and Responsibilities should be stored in txt files. The txt file(s) should then be placed under the ClobFiles folder and should be zipped together with .dat file.

Sample HDL File to load position model profile extra info:

METADATA|TalentProfile|ProfileCode|ProfileId|ProfileTypeId|ProfileUsageCode|SourceSystemOwner|SourceSystemId|ProfileStatusCode|Description|Summary
MERGE|TalentProfile|TEST_POS_PROFILE_2||3|M|HRC_SQLLOADER|TEST_POS_PROFILE_2|A|A Test Position Profile2|A Test Position Profile 2
METADATA|ModelProfileExtraInfo|ProfileExtraInformationId|Description|ProfileId|Qualifications|Responsibilities|ProfileCode|SourceSystemId|SourceSystemOwner
MERGE|ModelProfileExtraInfo||Test_Desc.txt||Test_Qual.txt|Test_Resp.txt|TEST_POS_PROFILE_2|TEST_POS_PROFILE_2_MFEI|HRC_SQLLOADER

zip File Structure:

Once the data is loaded, same can be viewed in UI:
Mandeep Gupta 0 Comments
Mar 21 2022
HDL – Loading Competencies against Worker

TalentProfile.dat can be used to create worker talent profiles and loading content items against the talent profiles. While some of the content items load is straight forward, loading competencies is little tricky one. This is because there are some additional fields like QualifierId1 and QualifierId2 which should be supplied to make the loaded data available on UI.

Sample HDL file to load competencies:

METADATA|ProfileItem|ProfileItemId|ProfileId|ProfileCode|ContentItemId|ContentTypeId|ContentType|CountryId|DateFrom|DateTo|RatingModelId1|RatingModelCode1|RatingLevelId1|RatingLevelCode1|RatingModelId2|RatingModelCode2|RatingLevelId2|RatingLevelCode2|SectionId|SourceSystemOwner|SourceSystemId|QualifierId1|QualifierId2|ItemDate6|ContentItem
MERGE|ProfileItem|||PERSON_112211||104|COMPETENCY||2022/02/07||300000236131123||||300000236131123|PERFORMANCE||3|300000236137110|HRC_SQLLOADER|PERSON_112211|300000236137121|100000086708123|2018/08/18|Leadership Skills

Use the below query to extract qualifiers data:

select QUALIFIER_ID,DESCRIPTION
  from FUSION.HRT_QUALIFIERS_VL
 where QUALIFIER_SET_ID in (
select QUALIFIER_SET_ID from              fusion.HRT_QUALIFIER_SETS_VL where SECTION_ID = 300000236137110)

Sample data from above query:

By default on UI, View By is set to “Official”:

Navigation :- Person Management -> Talent Profile

So, if you pass QualifierId1 as something different from the Id for “Official”, the loaded data will not be visible. Make sure to set “View By” to All.

Below is the high level description of some of the main attributes of ProfileItem business object:

ProfileId – profile Id of the person. If you don’t have it, pass it blank and pass the value under ProfileCode.

ProfileCode – Use this if you don’t know ProfileId (above).

DateFrom – From Date

RatingLevelCode2 – Pass values 1,2,3,4 or 5 based on your rating level (under rating model you can see).

SourceSystemId – Pass same as Profile Id or Profile Code

ItemDate6 – Review Date

ContentItem – Competency Name

QualifierId1 – Qualifier_id for an appropriate evaluator type. Can be used from the query above.

QualifierId2 – Person Id corresponding to evaluator type. For example, if using qualifier_id for Self, pass person_id of the worker in qualifierId2.

Mandeep Gupta 0 Comments
Mar 18 2022
HCM Extracts – Inbound HCM Extract “Assertion Failure” Error

HCM Extracts with inbound delivery are one of the most powerful technical features of the Fusion HCM cloud. HCM Extracts with inbound delivery enables the technical consultants to do customizations and automations by making use of tools like BIP, HCM Extracts and HCM Data Loader.

You can follow the below post for more details on the topic:

Extract – Loopback Inbound Extract

One has to take care a number of things when designing the Inbound HCM Extracts or the Loopback extract, otherwise you may get variety of errors.

One of the most commonly encountered error is:

An assertion failure was detected at location BatchProcArchivePA.getRepMapId:2. error while running

There can be multiple reasons to this error. To resolve this, please check below:

  1. Are you using a BIP report to generate the output? If yes, can you please check if permissions are assigned to the report and data model (to FUSION%APPS%HCM%ESS%APPID)?
  2. Please download the sample output from data model and check if you can generate a sample output using BI Publisher plugin.
  3. Check if report path is correct if you are using.
  4. Also, please check if you are able to deliver the output to UCM.
  5. Check if the extract parameters are correct and the related value sets/ fast formula are present in the environment.
Mandeep Gupta 0 Comments
Mar 18 2022
BIP – Query to extract Rating Model Levels in HDL Format

This is second post in a series to extract Rating Model data from Fusion environment in HDL format. The first post has a query to extract only the rating model data and second post describes the BIP query to extract rating model levels data.

Query to extract Rating Model data:

BIP – Query to extract Rating Model data in HDL Format

Query to extract Rating Model levels data:

SELECT 'METADATA|RatingLevel|RatingModelCode|RatingLevelCode|CareerStrDev|RatingDescription|DateFrom|FromPoints|MaximumRatingDistribution|MinimumRatingDistribution|NumericRating|ReviewPoints|ReviewRatingDescr|RatingShortDescr|StarRating|DateTo|ToPoints|SourceSystemId|SourceSystemOwner' DATA_LINE, 1 ORDER_NUM
  FROM DUAL
UNION
select
'MERGE'
||'|'||
'RatingLevel'
||'|'||
hrmb.rating_model_code
||'|'||
hrlv.rating_level_code
||'|'||
hrlv.career_str_dev
||'|'||
hrlv.rating_description
||'|'||
TO_CHAR(hrlv.date_from,'YYYY/MM/DD')
||'|'||
hrlv.from_points
||'|'||
hrlv.max_rating_distribution
||'|'||
hrlv.min_rating_distribution
||'|'||
hrlv.numeric_rating
||'|'||
hrlv.review_points
||'|'||
hrlv.review_rating_descr
||'|'||
hrlv.rating_short_descr
||'|'||
hrlv.star_rating
||'|'||
TO_CHAR(hrlv.date_to,'YYYY/MM/DD')
||'|'||
hrlv.to_points
||'|'||
hikm.source_system_id
||'|'||
hikm.source_system_owner DATA_LINE, 2 ORDER_NUM
FROM hrt_rating_models_b hrmb
   , hrt_rating_levels_vl hrlv
   , hrc_integration_key_map hikm
WHERE hrmb.rating_model_id = hrlv.rating_model_id
  AND hikm.surrogate_id = hrlv.rating_level_id
Mandeep Gupta 0 Comments
Mar 18 2022
BIP – Query to extract Rating Model data in HDL Format

You can use below query to extract Rating Model data from fusion environment for updates.

SELECT 'METADATA|RatingModel|RatingModelCode|DateFrom|DateTo|RatingName|RatingDescription|SourceSystemId|SourceSystemOwner' DATA_LINE, 1 ORDER_NUM
  FROM DUAL
UNION
select
'MERGE'
||'|'||
'RatingModel'
||'|'||
hrmb.rating_model_code
||'|'||
TO_CHAR(hrmb.date_from,'YYYY/MM/DD')
||'|'||
TO_CHAR(hrmb.date_to,'YYYY/MM/DD')
||'|'||
rtl.rating_name
||'|'||
rtl.rating_description
||'|'||
hikm.source_system_id
||'|'||
hikm.source_system_owner  DATA_LINE, 2 ORDER_NUM
FROM hrt_rating_models_b hrmb,
     hrt_rating_models_tl rtl,
     hrc_integration_key_map hikm
WHERE hrmb.rating_model_id = rtl.rating_model_id
  AND hikm.surrogate_id = hrmb.rating_model_id
  AND rtl.language = 'US'
ORDER BY ORDER_NUM

Save the data as RatingModel.dat before uploading to Fusion. You can make use of this query to extract data for reference or use it as a method to extract data from one instance and upload it to a different instance.

Check below post for sample RatingModel HDL file:

https://fusionhcmconsulting.com/2022/03/hdl-sample-file-to-upload-rating-model/

Mandeep Gupta 0 Comments
Mar 18 2022
HDL – Sample file to Upload Rating Model

Rating models can be uploaded in bulk using HDL. Use RatingModel business object.

Below is a sample file:

METADATA|RatingModel|RatingModelCode|DateFrom|DateTo|RatingName|RatingDescription|SourceSystemId|SourceSystemOwner
MERGE|RatingModel|INTEREST_TEST|1900/01/01|4712/12/31|Interest Rating Model - Test|Test Rating model used to indicate level of interest.|HRC_SQLLOADER_101|HRC_SQLLOADER

METADATA|RatingLevel|RatingModelCode|RatingLevelCode|CareerStrDev|RatingDescription|DateFrom|FromPoints|MaximumRatingDistribution|MinimumRatingDistribution|NumericRating|ReviewPoints|ReviewRatingDescr|RatingShortDescr|StarRating|DateTo|ToPoints|SourceSystemId|SourceSystemOwner
MERGE|RatingLevel|INTEREST_TEST|1||Undesirable|1900/01/01|0|||1|1|Undesirable|Undesirable|1|4712/12/31|0|HRC_SQLLOADER_101_1|HRC_SQLLOADER
MERGE|RatingLevel|INTEREST_TEST|2||No Preference|1900/01/01|0|||2|2|No Preference|No Preference|2|4712/12/31|0|HRC_SQLLOADER_101_2|HRC_SQLLOADER
MERGE|RatingLevel|INTEREST_TEST|3||Desirable|1900/01/01|0|||3|3|Desirable|Desirable|3|4712/12/31|0|HRC_SQLLOADER_101_3|HRC_SQLLOADER
MERGE|RatingLevel|INTEREST_TEST|4||Very desirable|1900/01/01|0|||4|4|Very desirable|Very Desirable|4|4712/12/31|0|HRC_SQLLOADER_101_4|HRC_SQLLOADER
Mandeep Gupta 0 Comments
Feb 25 2022
HDL – Sample HDL to Update User Table value

SQL Query to extract data for a particular value of a user table:

SELECT DATA
FROM (
SELECT 'METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|LegislativeDataGroupId|UserColumnId|UserColumnInstanceId|UserRowId|Value|SourceSystemOwner|SourceSystemId|UserTableId' DATA, 1 DATA_ROW
FROM DUAL
UNION ALL
SELECT 
'MERGE'
||'|'||
'UserDefinedTableColumnInstance'
||'|'||
to_char(fuci.EFFECTIVE_END_DATE,'YYYY/MM/DD')
||'|'||
to_char(fuci.EFFECTIVE_START_DATE,'YYYY/MM/DD')
||'|'||
fuci.LEGISLATIVE_DATA_GROUP_ID
||'|'||
fuci.USER_COLUMN_ID
||'|'||
fuci.USER_COLUMN_INSTANCE_ID
||'|'||
fuci.USER_ROW_ID
||'|'||
fuci.Value
||'|'||
hikm.source_system_owner  
||'|'||
hikm.source_system_id
||'|'||
fuc.USER_TABLE_ID
 DATA, 2 DATA_ROW
FROM 
FF_USER_COLUMN_INSTANCES_F fuci,
FF_USER_COLUMNS fuc,
FF_USER_TABLES ft,
HRC_INTEGRATION_KEY_MAP hikm
WHERE fuci.USER_COLUMN_ID=fuc.USER_COLUMN_ID
AND fuc.USER_TABLE_ID=ft.USER_TABLE_ID
AND hikm.surrogate_id = fuci.USER_COLUMN_INSTANCE_ID
AND ft.BASE_USER_TABLE_NAME = 'TEST_WAGE'
) ORDER BY DATA_ROW

Sample HDL:

METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|LegislativeDataGroupId|UserColumnId|UserColumnInstanceId|UserRowId|Value|SourceSystemOwner|SourceSystemId|UserTableId

MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|300000046974912|300000175181219|300000175188123|300000175187989|5.20|FUSION|300000175144123|300000175181333
Mandeep Gupta 0 Comments
Feb 25 2022
BIP – HDL to update Grade Rate values

SQL to extract data in HDL Format:

SELECT DATA
FROM(
SELECT 'METADATA|GradeRateValue|SourceSystemId|SourceSystemOwner|EffectiveStartDate|EffectiveEndDate|RateId|GradeCode|SetCode|LegislativeDataGroup|MinimumAmount|MaximumAmount|MidValueAmount|ValueAmount' DATA, 1 DATA_ROW
FROM DUAL
UNION ALL
SELECT 'MERGE|GradeRateValue|'
||
hikm.source_system_id 
||'|'||
hikm.source_system_owner
||'|'||
TO_CHAR(prvf.effective_start_date,'YYYY/MM/DD')
||'|'||
TO_CHAR(prvf.effective_end_date,'YYYY/MM/DD')
||'|'||
prvf.rate_id
||'|'||
pgf.grade_code
||'|'||
fssv.set_code
||'|'||
ldg.name
||'|'||
prvf.minimum
||'|'||
prvf.maximum
||'|'||
prvf.mid_value
||'|'||
prvf.value DATA, 2 DATA_ROW
FROM  per_rate_values_f prvf,
      per_legislative_data_groups_tl ldg,
      per_grades_f pgf,
      fnd_setid_sets_vl fssv,
      per_rates_f pr,
      hrc_integration_key_map hikm
where 1=1
and pr.legislative_data_group_id = ldg.legislative_data_group_id
and ldg.language = USERENV('LANG')
and trunc(sysdate) between prvf.effective_start_date and prvf.effective_end_date
and upper(prvf.rate_object_type) = 'GRADE'
and prvf.rate_object_id = pgf.grade_id
and trunc(sysdate) between pgf.effective_start_date and pgf.effective_end_date
AND   pgf.set_id=fssv.set_id
and prvf.rate_id = pr.rate_id
and trunc(sysdate) between pr.effective_start_date and pr.effective_end_date
and hikm.surrogate_id = prvf.rate_value_id

) ORDER BY DATA_ROW

Sample HDL:

METADATA|GradeRateValue|SourceSystemId|SourceSystemOwner|EffectiveStartDate|EffectiveEndDate|RateId|GradeCode|SetCode|LegislativeDataGroup|MinimumAmount|MaximumAmount|MidValueAmount|ValueAmount

MERGE|GradeRateValue|300000107518119|FUSION|1951/01/01|4712/12/31|300000106295381|ADMIN05|COMMON|KZ Legislative Data Group|870|962|913.5|
Mandeep Gupta 0 Comments
Feb 25 2022
BIP – Query to extract Person Address Details
select

distinct

PAPF.PERSON_NUMBER,

PAPF.PERSON_ID,

PPAUF.PERSON_ADDR_USAGE_ID "ADDRESS_ID",

TO_CHAR(PA.EFFECTIVE_START_DATE,'YYYY/MM/DD')"EFFECTIVE_START_DATE",

TO_CHAR(PA.EFFECTIVE_END_DATE,'YYYY/MM/DD')"EFFECTIVE_END_DATE",

PPAUF.ADDRESS_TYPE,

PA.ADDRESS_LINE_1,

PA.ADDRESS_LINE_2,

PA.ADDRESS_LINE_3

from

PER_ADDRESSES_F PA,

PER_ALL_PEOPLE_F PAPF,

PER_PERSON_ADDR_USAGES_F PPAUF

where

  PAPF.PERSON_ID = PPAUF.PERSON_ID(+)

AND PPAUF.ADDRESS_ID = PA.ADDRESS_ID(+)
Mandeep Gupta 0 Comments
Feb 25 2022
HDL – Sample file to update Default Expense Account
METADATA|WorkTerms|AssignmentId|PeriodOfServiceId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode

MERGE|WorkTerms|300000066966135|300000066966134|Y|1|2002/06/16|4712/12/31|300000066966135|FUSION|HIRE



METADATA|Assignment|AssignmentId|WorkTermsAssignmentId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode|DefaultExpenseAccount

MERGE|Assignment|300000066966140|300000066966135|Y|1|2002/06/16|4712/12/31|300000066966140|FUSION|HIRE|143-000-215123-0000-000-0000-0000
Mandeep Gupta 0 Comments
Jan 25 2022
HDL – Sample file to update user name to email address
SELECT 'METADATA|User|PersonNumber|Username|SourceSystemId|SourceSystemOwner' Headerrow, 1 dataorder
FROM DUAL
UNION
SELECT 'METADATA|User'
      ||'|'||papf.person_number 
      ||'|'||pea.email_address
      ||'|'||hikm.source_system_id
      ||'|'||hikm.source_system_owner Headerrow, 2 dataorder
  FROM per_all_people_f papf
      ,per_email_addresses pea
	  ,per_users pu
	  ,hrc_integration_key_map hikm
 WHERE papf.person_id = pea.person_id
   and pu.person_id = pea.person_id
   and pu.user_id = hikm.surrogate_id
   and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
order by dataorder

Sample File:

METADATA|User|PersonNumber|Username|SourceSystemId|SourceSystemOwner
MERGE|User|11101|[email protected]|300000120863888|FUSION
Mandeep Gupta 1 Comments
Jan 25 2022
HDL – Query to generate worker email in HDL format
select 'METADATA|PersonEmail|EmailAddressId|PersonId|DateFrom|EmailType|PrimaryFlag|EmailAddress|SourceSystemId|SourceSystemOwner'
  from dual
UNION ALL
select 'MERGE|PersonEmail' 						||'|'||												
       NULL 									||'|'||										
       papf.person_id							||'|'||								
       to_char(ppos.date_start,'RRRR/MM/DD')	||'|'||					    
       'W1' 									||'|'||				
	   'Y'													||'|'||		
       'sendmail-discard_'||papf.person_number||'@xyz.com' 	||'|'||
      'PER_EMAIL_'||papf.person_number						||'|'||
      'HRC_SQLLOADER' 												
  from per_all_people_f papf
      ,per_periods_of_service ppos
 where 1=1
   and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
   and papf.person_id = ppos.person_id
   and ppos.date_start = (select MAX(ppos2.date_start) from per_periods_of_service ppos2
                           where ppos2.person_id = ppos.person_id)

Sample HDL File:

METADATA|PersonEmail|EmailAddressId|PersonId|DateFrom|EmailType|PrimaryFlag|EmailAddress|SourceSystemId|SourceSystemOwner
MERGE|PersonEmail||100000015400564|2021/03/15|W1|Y|[email protected]|PER_EMAIL_00603|HRC_SQLLOADER
Mandeep Gupta 0 Comments
Jan 16 2022
HDL – Sample file to cancel Pending Worker Work Relationship

In case, you want to cancel work relationship of a Pending worker, below HDL can be used. Make sure to pass CancelWorkRelationshipFlag as Y.

Sample HDL:

METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|PersonId|CancelWorkRelationshipFlag
DELETE|WorkRelationship|FUSION|300000092554146|300000092554127|Y

You can extract the relevant Ids using below SQL:

select hikm.source_system_owner, hikm.source_system_id, ppos.person_id
  from per_periods_of_service ppos
      ,hrc_integration_key_map hikm
 where ppos.period_of_service_id = hikm.surrogate_id
   and ppos.person_id in (select person_id from per_all_people_f
where person_number = '1894')
Mandeep Gupta 0 Comments
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