In this article, let’s try to pull the Assigned Payroll Information.
AssignedPayroll.dat HDL file will be used to load the Assigned Payroll information into the HCM system.
METADATA|AssignedPayroll|EffectiveStartDate|AssignmentNumber|PayrollDefinitionCode|LegislativeDataGroupName|StartDate
MERGE|AssignedPayroll|2021/03/01|E12345|Weekly Payroll|Fusion HCM LDG|2021/03/01
SQL Query to get this information:
SELECT ASSF.ASSIGNMENT_ID,
ASSF.ASSIGNMENT_NUMBER,
(SELECT PAPF.PERSON_NUMBER
FROM PER_ALL_PEOPLE_F PAPF
WHERE PAPF.PERSON_ID = ASSF.PERSON_ID
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
) PERSON_NUMBER,
SOURCE_ID.SOURCE_SYSTEM_ID,
ALLP.PAYROLL_NAME,
TO_CHAR(ALLP.EFFECTIVE_START_DATE, 'YYYY/MM/DD') PAYROLL_EFFECTIVE_START_DATE,
TO_CHAR(ALLP.EFFECTIVE_END_DATE, 'YYYY/MM/DD') PAYROLL_EFFECTIVE_END_DATE,
TO_CHAR(ASSP.START_DATE, 'YYYY/MM/DD') ASSIGNED_PAYROLL_START_DATE,
TO_CHAR(ASSP.END_DATE, 'YYYY/MM/DD') ASSIGNED_PAYROLL_END_DATE,
TO_CHAR(ASSF.EFFECTIVE_END_DATE, 'YYYY/MM/DD') ASSG_EFFECTIVE_END_DATE,
TO_CHAR(ASSF.EFFECTIVE_START_DATE, 'YYYY/MM/DD') ASSG_EFFECTIVE_START_DATE,
ASSP.LSED,
ASSP.LSPD,
ASSP.FINC,
TO_CHAR(ASSP.FSED, 'YYYY/MM/DD') FSED
FROM PAY_REL_GROUPS_DN RELG,
PER_ALL_ASSIGNMENTS_F ASSF,
PAY_ASSIGNED_PAYROLLS_DN ASSP,
PAY_ALL_PAYROLLS_F ALLP,
HRC_INTEGRATION_KEY_MAP SOURCE_ID
WHERE SOURCE_ID.SURROGATE_ID = ASSF.ASSIGNMENT_ID
AND SYSDATE BETWEEN ASSF.EFFECTIVE_START_DATE AND ASSF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) <= NVL(ASSP.LSED, SYSDATE + 1)
AND RELG.GROUP_TYPE = 'A'
AND RELG.PARENT_REL_GROUP_ID = ASSP.PAYROLL_TERM_ID
AND ASSP.PAYROLL_ID = ALLP.PAYROLL_ID
AND RELG.ASSIGNMENT_ID = ASSF.ASSIGNMENT_ID
AND SOURCE_ID.OBJECT_NAME = 'Assignment'
Sample output will look like:
