• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:August 5, 2021
  • Reading time:2 mins read
You are currently viewing SQL Query to pull Assigned Payrolls Information
SQL Query to pull Assigned Payrolls Information
- SQL Query to pull Assigned Payrolls Information
Total Visits: 8

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:

image 11 1024x450 - SQL Query to pull Assigned Payrolls Information
If you like the content, please follow us on LinkedInFacebook, and Twitter to get updated with the latest content.