SELECT DISTINCT P.PERSON_NUMBER,
(
SELECT DISTINCT NAME.LIST_NAME
FROM PER_PERSON_NAMES_F NAME
WHERE NAME.PERSON_ID = P.PERSON_ID
AND NAME.NAME_TYPE = 'GLOBAL'
AND SYSDATE BETWEEN NAME.EFFECTIVE_START_DATE
AND NAME.EFFECTIVE_END_DATE
) AS EMPLOYEE_NAME,
TO_CHAR(A.EFFECTIVE_START_DATE, 'MM/DD/YYYY') AS ASSIGNMENT_START_DATE,
TO_CHAR(A.EFFECTIVE_END_DATE, 'MM/DD/YYYY') AS ASSIGNMENT_END_DATE,
A.ASSIGNMENT_NUMBER,
P22.PAYROLL_RELATIONSHIP_NUMBER,
LEG.NAME AS LDG,
(
SELECT DISTINCT LE.NAME
FROM PER_LEGAL_EMPLOYERS LE
WHERE LE.ORGANIZATION_ID = A.LEGAL_ENTITY_ID
AND SYSDATE BETWEEN LE.EFFECTIVE_START_DATE
AND LE.EFFECTIVE_END_DATE
) AS LEGAL_EMPLOYER,
(
SELECT DISTINCT DE.NAME
FROM HR_ALL_ORGANIZATION_UNITS DE
WHERE DE.ORGANIZATION_ID = A.ORGANIZATION_ID
AND SYSDATE BETWEEN DE.EFFECTIVE_START_DATE
AND DE.EFFECTIVE_END_DATE
) AS DEPARTMENT,
(
SELECT DISTINCT PALP.REPORTING_NAME
FROM PAY_ASSIGNED_PAYROLLS_DN PASPD,
PAY_ALL_PAYROLLS_F PALP
WHERE P4.PAYROLL_TERM_ID = PASPD.PAYROLL_TERM_ID
AND PALP.PAYROLL_ID = PASPD.PAYROLL_ID
AND SYSDATE BETWEEN PASPD.START_DATE
AND PASPD.END_DATE
AND SYSDATE BETWEEN PALP.EFFECTIVE_START_DATE
AND PALP.EFFECTIVE_END_DATE
AND PASPD.START_DATE = (
SELECT MAX(PASPD1.START_DATE)
FROM PAY_ASSIGNED_PAYROLLS_DN PASPD1
WHERE PASPD1.PAYROLL_TERM_ID = PASPD.PAYROLL_TERM_ID
AND PASPD1.START_DATE <= SYSDATE
)
) AS PAYROLL_NAME,
PAY.NAME AS PPM_NAME,
TO_CHAR(PAY.EFFECTIVE_START_DATE, 'MM/DD/YYYY') AS PPM_START,
TO_CHAR(PAY.EFFECTIVE_END_DATE, 'MM/DD/YYYY') AS PPM_END,
PAY.PAYMENT_AMOUNT_TYPE AS PPM_AMOUNT_TYPE,
PAY.AMOUNT AS PPM_AMOUNT,
PAY.PERCENTAGE AS PPM_PERCENTAGE,
PAY.PRIORITY AS PPM_PRIORITY,
BA.BANK_ACCOUNT_NAME,
BA.BANK_ACCOUNT_NUM,
BA.BANK_BRANCH_NAME,
BA.BRANCH_NUMBER
FROM PER_ALL_PEOPLE_F P,
PER_ALL_ASSIGNMENTS_F A,
PAY_PAY_RELATIONSHIPS_DN P22,
PAY_PAYROLL_ASSIGNMENTS P4,
PER_LEGAL_EMPLOYERS LEG,
PAY_PERSON_PAY_METHODS_F PAY,
PAY_BANK_ACCOUNTS BA
WHERE P.PERSON_ID = A.PERSON_ID
AND A.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE','SUSPENDED')
AND A.ASSIGNMENT_TYPE = 'E'
AND P22.PAYROLL_RELATIONSHIP_ID = P4.PAYROLL_RELATIONSHIP_ID
AND P4.HR_ASSIGNMENT_ID = A.ASSIGNMENT_ID
AND LEG.ORGANIZATION_ID = A.LEGAL_ENTITY_ID
AND PAY.PAYROLL_RELATIONSHIP_ID = P22.PAYROLL_RELATIONSHIP_ID
AND PAY.BANK_ACCOUNT_ID = BA.BANK_ACCOUNT_ID
AND SYSDATE BETWEEN A.EFFECTIVE_START_DATE
AND A.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN LEG.EFFECTIVE_START_DATE
AND LEG.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN P.EFFECTIVE_START_DATE
AND P.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN P22.START_DATE
AND P22.END_DATE
AND SYSDATE BETWEEN P4.START_DATE
AND P4.END_DATE
AND SYSDATE BETWEEN PAY.EFFECTIVE_START_DATE
AND PAY.EFFECTIVE_END_DATE
ORDER BY P.PERSON_NUMBER
