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