Element Entries Query in Oracle HCM

SELECT  papf.person_number "PersonNumber"
        ,to_char(pee.effective_start_date,'YYYY/MM/DD') "ElementEntryEffectiveStartDate"
        ,to_char(pee.effective_end_date,'YYYY/MM/DD') "ElementEntryEffectiveEndtDate"
        ,petl.element_name "ElementName"
        ,pldgt.name "LegislativeDataGroupName"
        ,pldg.legislation_code "LegislationCode"
        ,piv.base_name "InputValueName"
        ,peev.screen_entry_value "ScreenEntryValue"
        ,to_char(peev.EFFECTIVE_START_DATE,'YYYY/MM/DD') "EntryValueStartDate"
        ,to_char(peev.EFFECTIVE_END_DATE,'YYYY/MM/DD') "EntryValueEndDate"
   FROM pay_element_entries_f pee,
        pay_element_types_f pet,
        pay_element_types_tl petl,
        pay_entry_usages peu,
        pay_payroll_assignments ppa,
        per_all_assignments_f paaf,
        per_periods_of_service ppos,
        per_all_people_f papf,
        per_legislative_data_groups pldg,
        per_legislative_data_groups_tl pldgt,
        pay_element_entry_values_f peev,
        pay_input_values_f piv
  WHERE ppos.date_start=(SELECT MAX(ppos1.date_start)
                           FROM per_periods_of_service  ppos1
                          WHERE ppos1.person_id=ppos.person_id
                            )
    AND ppos.period_of_service_id = paaf.period_of_service_id
    AND paaf.person_id = papf.person_id       
    AND paaf.assignment_type IN ('E')
    AND paaf.primary_flag = 'Y'   
    AND pee.element_type_id = pet.element_type_id
    AND pet.element_type_id = petl.element_type_id
    AND pee.element_entry_id = peu.element_entry_id
    AND peu.payroll_relationship_id=ppa.payroll_relationship_id
    AND ppa.hr_assignment_id = paaf.assignment_id
    AND paaf.legislation_code = pldg.legislation_code
    AND pldg.legislative_data_group_id = pldgt.legislative_data_group_id
    AND pee.element_entry_id = peev.element_entry_id
    AND peev.input_value_id = piv.input_value_id
    AND pet.element_type_id = piv.element_type_id
    AND pldgt.language = 'US'
    AND trunc(sysdate) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
    AND trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND trunc(sysdate) BETWEEN peev.effective_start_date AND peev.effective_end_date
AND trunc(sysdate) BETWEEN pee.effective_start_date AND pee.effective_end_date
    AND petl.language = 'US'
    AND petl.element_name = 'ElementName'
and piv.base_name  = 'InputValue'
    AND papf.person_number in 
(
'12345'
)
order by papf.person_number

Extract file naming

 XX_HCM_to_YY_Employees_%y-%m-%d-%H:%M:%S

Pull Data from another attribute in Extract into a Fast formula

 /*******************************************************************
FORMULA NAME : GET_WORK_EMAIL_FF
FORMULA TYPE : Extract Rule
 DESCRIPTION : Get value set value for Email
Change History:
Name                   Date         Comments
--------------------------------------------------------------------------------------------------------------------------

*******************************************************************
*/
DEFAULT FOR DATA_ELEMENTS IS EMPTY_TEXT_TEXT
INPUTS ARE DATA_ELEMENTS (TEXT_TEXT)
l_per_id = ' '
/* To retrieve 'Data element' from attribute */
 
IF (DATA_ELEMENTS.EXISTS('Person_ID') ) THEN
(
   l_per_id = DATA_ELEMENTS['Person_ID'] 
    
   rule_value = GET_VALUE_SET('GET_WORK_EMAIL_VS','|=P_PERSON_ID='''||l_per_id||'''')
   
)

RETURN rule_value

Query to fetch Checklist or Journey related Questionnaire responses

 select responsibility_type,question_text,answer,full_name
from
(
select task.responsibility_type,question_text,nvl2(resp.ANSWER_CODE,resp.LONG_TEXT,resp.ANSWER_CLOB) answer,ppnf.full_name
from Per_allocated_tasks task,
per_allocated_checklists pac,
HRQ_QSTNR_PCPT_RESPONSES_V resp,
HRQ_QUESTIONS_VL ques,
per_person_names_f ppnf
where 1=1
and task.allocated_checklist_id = pac.allocated_checklist_id
and task.questionnaire_id = resp.questionnaire_id
and task.action_type = 'ORA_CHK_QUESTIONNAIRE'
and resp.question_code = ques.question_code
and pac.person_id = ppnf.person_id
and ppnf.name_type = 'GLOBAL'
and task.allocated_task_id = resp.participant_id
and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
and task.allocated_checklist_id = :P_ALLOCATED_CHECKLIST_ID
)
order by responsibility_type,question_text

External bank accounts and owners tables

select * from IBY_EXT_BANK_ACCOUNTS where 1=1 order by creation_date desc

select * from IBY_ACCOUNT_OWNERS where 1=1 order by creation_date desc

How to Load Hire/Termination/Rehire Historical Data with HDL?

 METADATA|Worker|SourceSystemOwner|SourceSystemId|PersonId|ActionCode|ReasonCode|EffectiveStartDate|EffectiveEndDate|PersonNumber|StartDate|DateOfBirth|CountryOfBirth|WaiveDataProtectFlag

MERGE|Worker|HRC_SQLLOADER|40001023PER15||HIRE||1990/07/16|4712/12/31|40001023PER15|1990/07/16|1943/07/12|EE|N

METADATA|PersonLegislativeData|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|PersonId(SourceSystemId)|LegislationCode|MaritalStatus|Sex

MERGE|PersonLegislativeData|HRC_SQLLOADER|40001023PER15|1990/07/16|4712/12/31|40001023PER15|US||F

METADATA|PersonName|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|PersonId(SourceSystemId)|LegislationCode|NameType|FirstName|MiddleNames|LastName|Honors|KnownAs|Title|NameInformation15|NameInformation16

MERGE|PersonName|HRC_SQLLOADER|40001023PER15|1990/07/16|2005/05/03|40001023PER15|US|GLOBAL|WorkerTest01|WorkerTest01|WorkerTest01|||MS.||

MERGE|PersonName|HRC_SQLLOADER|40001023PER15|2005/05/04|4712/12/31|40001023PER15|US|GLOBAL|WorkerTest01|WorkerTest01|WorkerTest01||WorkerTest01|MS.|WorkerTest01|

METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|LegalEmployerName|PersonId(SourceSystemId)|DateStart|WorkerType|LegalEmployerSeniorityDate|ActualTerminationDate|EnterpriseSeniorityDate|LastWorkingDate|NotifiedTerminationDate|OnMilitaryServiceFlag|PrimaryFlag|ActionCode|ReasonCode

MERGE|WorkRelationship|HRC_SQLLOADER|WR-40001023PER15|GBI HCM Widgets USA|40001023PER15|1990/07/16|E||2000/12/31|1990/07/16|||N|Y|RESIGNATION|RESIGN_PERSONAL

MERGE|WorkRelationship|HRC_SQLLOADER|WR-40001023PER1501|GBI_GHR_2TMA|40001023PER15|2001/01/01|E||2005/12/31|1990/07/16|||N|Y|RESIGNATION|RESIGN_PERSONAL

MERGE|WorkRelationship|HRC_SQLLOADER|WR-40001023PER1502|GBI_GHR_2TSA|40001023PER15|2006/01/01|E|||1990/07/16|||N|Y|HIRE|

METADATA|WorkTerms|SourceSystemOwner|SourceSystemId|ActionCode|ReasonCode|LegalEmployerName|AssignmentNumber|AssignmentName|EffectiveStartDate|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|PeriodOfServiceId(SourceSystemId)|PersonTypeCode|AssignmentStatusTypeCode|AssignmentType|BusinessUnitId|NoticePeriod|PrimaryWorkTermsFlag|FreezeStartDate|FreezeUntilDate

MERGE|WorkTerms|HRC_SQLLOADER|WT-40001023PER15|HIRE||GBI HCM Widgets USA|WT-NU-40001023PER15|WT-NA-40001023PER15|1990/07/16|2000/12/31|N|1|WR-40001023PER15|Employee|ACTIVE_NO_PROCESS|ET|100010025072009|0|Y|4712/12/31|1950/01/01

MERGE|WorkTerms|HRC_SQLLOADER|WT-40001023PER15|RESIGNATION|RESIGN_PERSONAL|GBI HCM Widgets USA|WT-NU-40001023PER15|WT-NA-40001023PER15|2001/01/01|4712/12/31|N|1|WR-40001023PER15|Employee|INACTIVE_NO_PROCESS|ET|100010025072009|0|Y|4712/12/31|1950/01/01

MERGE|WorkTerms|HRC_SQLLOADER|WT-40001023PER1501|HIRE||GBI_GHR_2TMA|WT-NU-40001023PER1501|WT-NA-40001023PER1501|2001/01/01|2005/12/31|N|1|WR-40001023PER1501|Employee|ACTIVE_NO_PROCESS|ET|100010025072009|0|Y|4712/12/31|1950/01/01

MERGE|WorkTerms|HRC_SQLLOADER|WT-40001023PER1501|RESIGNATION|RESIGN_PERSONAL|GBI_GHR_2TMA|WT-NU-40001023PER1501|WT-NA-40001023PER1501|2006/01/01|4712/12/31|N|1|WR-40001023PER1501|Employee|INACTIVE_NO_PROCESS|ET|100010025072009|0|Y|4712/12/31|1950/01/01

MERGE|WorkTerms|HRC_SQLLOADER|WT-40001023PER1502|HIRE||GBI_GHR_2TSA|WT-NU-40001023PER1502|WT-NA-40001023PER1502|2006/01/01|2009/12/31|N|1|WR-40001023PER1502|Employee|ACTIVE_NO_PROCESS|ET|100010025072009|0|Y|4712/12/31|1950/01/01

MERGE|WorkTerms|HRC_SQLLOADER|WT-40001023PER1502|ASG_CHANGE||GBI_GHR_2TSA|WT-NU-40001023PER1502|WT-NA-40001023PER1502|2010/01/01|4712/12/31|N|1|WR-40001023PER1502|Employee|ACTIVE_NO_PROCESS|ET|100010025072009|0|Y|4712/12/31|1950/01/01

METADATA|Assignment|SourceSystemOwner|SourceSystemId|ActionCode|ReasonCode|LegalEmployerName|AssignmentNumber|AssignmentName|EffectiveStartDate|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|PeriodOfServiceId(SourceSystemId)|WorkTermsAssignmentId(SourceSystemId)|PersonTypeCode|AssignmentStatusTypeCode|AssignmentType|BusinessUnitId|NoticePeriod|PrimaryAssignmentFlag|FreezeStartDate|FreezeUntilDate

MERGE|Assignment|HRC_SQLLOADER|WA-40001023PER15|HIRE||GBI HCM Widgets USA|WA-NU-40001023PER15|WA-NA-40001023PER15|1990/07/16|2000/12/31|N|1|WR-40001023PER15|WT-40001023PER15|Employee|ACTIVE_NO_PROCESS|E|100010025072009|0|Y|4712/12/31|1950/01/01

MERGE|Assignment|HRC_SQLLOADER|WA-40001023PER15|RESIGNATION|RESIGN_PERSONAL|GBI HCM Widgets USA|WA-NU-40001023PER15|WA-NA-40001023PER15|2001/01/01|4712/12/31|N|1|WR-40001023PER15|WT-40001023PER15|Employee|INACTIVE_NO_PROCESS|E|100010025072009|0|Y|4712/12/31|1950/01/01

MERGE|Assignment|HRC_SQLLOADER|WA-40001023PER1501|HIRE||GBI_GHR_2TMA|WA-NU-40001023PER1501|WA-NA-40001023PER1501|2001/01/01|2005/12/31|N|1|WR-40001023PER1501|WT-40001023PER1501|Employee|ACTIVE_NO_PROCESS|E|100010025072009|0|Y|4712/12/31|1950/01/01

MERGE|Assignment|HRC_SQLLOADER|WA-40001023PER1501|RESIGNATION|RESIGN_PERSONAL|GBI_GHR_2TMA|WA-NU-40001023PER1501|WA-NA-40001023PER1501|2006/01/01|4712/12/31|N|1|WR-40001023PER1501|WT-40001023PER1501|Employee|INACTIVE_NO_PROCESS|E|100010025072009|0|Y|4712/12/31|1950/01/01

MERGE|Assignment|HRC_SQLLOADER|WA-40001023PER1502|HIRE||GBI_GHR_2TSA|WA-NU-40001023PER1502|WA-NA-40001023PER1502|2006/01/01|2009/12/31|N|1|WR-40001023PER1502|WT-40001023PER1502|Employee|ACTIVE_NO_PROCESS|E|100010025072009|0|Y|4712/12/31|1950/01/01

MERGE|Assignment|HRC_SQLLOADER|WA-40001023PER1502|ASG_CHANGE||GBI_GHR_2TSA|WA-NU-40001023PER1502|WA-NA-40001023PER1502|2010/01/01|4712/12/31|N|1|WR-40001023PER1502|WT-40001023PER1502|Employee|ACTIVE_NO_PROCESS|E|100010025072009|0|Y|4712/12/31|1950/01/01

When to use Publish Required in Document of Records setup?

Oracle has explained this clearly in one of the 2018 release updates with an example.

Publish Required: You can make a document record visible on the UI from a specific date. The Publish Date field is now configured to be entered using HDL only, and not from the UI. If you want the document to be visible only after a specific date, you must select Yes or No in the Publish Required field on the Create Document Type page. For example, if you want to make the promotion letter visible starting 1-Apr-2018, select Yes on the Create Document Type page. Then, pass the date of 1-Apr-2018 in the Publish Date field using HDL, when creating document records for the worker.