VIEW: APPS.PAY_AU_ASG_PAY_RUNS_V

Source

View Text - Preformatted

SELECT PER.BUSINESS_GROUP_ID , PAA.ASSIGNMENT_ID , PAA.ASSIGNMENT_ACTION_ID , null, null, 1, PER.PERSON_ID , PER.EMPLOYEE_NUMBER , PER.FULL_NAME , PER.ORDER_NAME, ASF.ASSIGNMENT_NUMBER , PPA.PAYROLL_ID , PPA.PAYROLL_ACTION_ID , PTP.PERIOD_NAME , PTP.START_DATE , PTP.END_DATE , PTP.PAY_ADVICE_DATE , PPA.DATE_EARNED FROM PER_ASSIGNMENTS_F ASF , PER_PEOPLE_F PER , PER_TIME_PERIODS PTP , PAY_ASSIGNMENT_ACTIONS PAA , PAY_PAYROLL_ACTIONS PPA WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND PPA.ACTION_TYPE IN ('R','Q') AND PPA.ACTION_STATUS = 'C' AND PER.PERSON_ID = ASF.PERSON_ID AND PAA.ASSIGNMENT_ID = ASF.ASSIGNMENT_ID AND PPA.DATE_EARNED BETWEEN ASF.EFFECTIVE_START_DATE AND ASF.EFFECTIVE_END_DATE AND PPA.DATE_EARNED BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE AND PTP.PAYROLL_ID = PPA.PAYROLL_ID AND PPA.DATE_EARNED BETWEEN PTP.START_DATE AND PTP.END_DATE AND PAA.ACTION_SEQUENCE = ( SELECT MAX(PAC.ACTION_SEQUENCE) FROM PAY_ASSIGNMENT_ACTIONS PAC WHERE PAC.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND PAC.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID) AND NOT EXISTS (SELECT 1 FROM PAY_COST_ALLOCATION_KEYFLEX PCAK , PAY_COST_ALLOCATIONS_F PCAF WHERE PCAF.COST_ALLOCATION_KEYFLEX_ID =PCAK.COST_ALLOCATION_KEYFLEX_ID AND PCAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND PPA.DATE_EARNED BETWEEN nvl(pcaf.effective_start_date,to_date('01/01/1900','DD/MM/YYYY')) and nvl(pcaf.effective_end_date,to_date('31/12/4712','DD/MM/YYYY')) ) union SELECT PER.BUSINESS_GROUP_ID , PAA.ASSIGNMENT_ID , PAA.ASSIGNMENT_ACTION_ID , to_char(PCAK.COST_ALLOCATION_KEYFLEX_ID), PCAK.CONCATENATED_SEGMENTS , NVL(PCAF.PROPORTION,1) , PER.PERSON_ID , PER.EMPLOYEE_NUMBER , PER.FULL_NAME , PER.ORDER_NAME, ASF.ASSIGNMENT_NUMBER , PPA.PAYROLL_ID , PPA.PAYROLL_ACTION_ID , PTP.PERIOD_NAME , PTP.START_DATE , PTP.END_DATE , PTP.PAY_ADVICE_DATE , PPA.DATE_EARNED FROM PER_ASSIGNMENTS_F ASF , PER_PEOPLE_F PER , PER_TIME_PERIODS PTP , PAY_ASSIGNMENT_ACTIONS PAA , PAY_PAYROLL_ACTIONS PPA , PAY_COST_ALLOCATION_KEYFLEX PCAK , PAY_COST_ALLOCATIONS_F PCAF WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND PPA.ACTION_TYPE IN ('R','Q') AND PPA.ACTION_STATUS = 'C' AND PCAF.COST_ALLOCATION_KEYFLEX_ID = PCAK.COST_ALLOCATION_KEYFLEX_ID(+) AND PCAF.ASSIGNMENT_ID(+) = PAA.ASSIGNMENT_ID AND PER.PERSON_ID = ASF.PERSON_ID AND PAA.ASSIGNMENT_ID = ASF.ASSIGNMENT_ID AND PPA.DATE_EARNED BETWEEN ASF.EFFECTIVE_START_DATE AND ASF.EFFECTIVE_END_DATE AND PPA.DATE_EARNED BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE AND PPA.DATE_EARNED BETWEEN NVL(PCAF.EFFECTIVE_START_DATE,TO_DATE('01/01/1900','DD/MM/YYYY')) AND NVL(PCAF.EFFECTIVE_END_DATE,TO_DATE('31/12/4712', 'DD/MM/YYYY')) AND PTP.PAYROLL_ID = PPA.PAYROLL_ID AND PPA.DATE_EARNED BETWEEN PTP.START_DATE AND PTP.END_DATE AND PAA.ACTION_SEQUENCE = ( SELECT MAX(PAC.ACTION_SEQUENCE) FROM PAY_ASSIGNMENT_ACTIONS PAC WHERE PAC.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND PAC.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID)

View Text - HTML Formatted

SELECT PER.BUSINESS_GROUP_ID
, PAA.ASSIGNMENT_ID
, PAA.ASSIGNMENT_ACTION_ID
, NULL
, NULL
, 1
, PER.PERSON_ID
, PER.EMPLOYEE_NUMBER
, PER.FULL_NAME
, PER.ORDER_NAME
, ASF.ASSIGNMENT_NUMBER
, PPA.PAYROLL_ID
, PPA.PAYROLL_ACTION_ID
, PTP.PERIOD_NAME
, PTP.START_DATE
, PTP.END_DATE
, PTP.PAY_ADVICE_DATE
, PPA.DATE_EARNED
FROM PER_ASSIGNMENTS_F ASF
, PER_PEOPLE_F PER
, PER_TIME_PERIODS PTP
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('R'
, 'Q')
AND PPA.ACTION_STATUS = 'C'
AND PER.PERSON_ID = ASF.PERSON_ID
AND PAA.ASSIGNMENT_ID = ASF.ASSIGNMENT_ID
AND PPA.DATE_EARNED BETWEEN ASF.EFFECTIVE_START_DATE
AND ASF.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND PTP.PAYROLL_ID = PPA.PAYROLL_ID
AND PPA.DATE_EARNED BETWEEN PTP.START_DATE
AND PTP.END_DATE
AND PAA.ACTION_SEQUENCE = ( SELECT MAX(PAC.ACTION_SEQUENCE)
FROM PAY_ASSIGNMENT_ACTIONS PAC
WHERE PAC.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PAC.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID)
AND NOT EXISTS (SELECT 1
FROM PAY_COST_ALLOCATION_KEYFLEX PCAK
, PAY_COST_ALLOCATIONS_F PCAF
WHERE PCAF.COST_ALLOCATION_KEYFLEX_ID =PCAK.COST_ALLOCATION_KEYFLEX_ID
AND PCAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PPA.DATE_EARNED BETWEEN NVL(PCAF.EFFECTIVE_START_DATE
, TO_DATE('01/01/1900'
, 'DD/MM/YYYY'))
AND NVL(PCAF.EFFECTIVE_END_DATE
, TO_DATE('31/12/4712'
, 'DD/MM/YYYY')) ) UNION SELECT PER.BUSINESS_GROUP_ID
, PAA.ASSIGNMENT_ID
, PAA.ASSIGNMENT_ACTION_ID
, TO_CHAR(PCAK.COST_ALLOCATION_KEYFLEX_ID)
, PCAK.CONCATENATED_SEGMENTS
, NVL(PCAF.PROPORTION
, 1)
, PER.PERSON_ID
, PER.EMPLOYEE_NUMBER
, PER.FULL_NAME
, PER.ORDER_NAME
, ASF.ASSIGNMENT_NUMBER
, PPA.PAYROLL_ID
, PPA.PAYROLL_ACTION_ID
, PTP.PERIOD_NAME
, PTP.START_DATE
, PTP.END_DATE
, PTP.PAY_ADVICE_DATE
, PPA.DATE_EARNED
FROM PER_ASSIGNMENTS_F ASF
, PER_PEOPLE_F PER
, PER_TIME_PERIODS PTP
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_COST_ALLOCATION_KEYFLEX PCAK
, PAY_COST_ALLOCATIONS_F PCAF
WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('R'
, 'Q')
AND PPA.ACTION_STATUS = 'C'
AND PCAF.COST_ALLOCATION_KEYFLEX_ID = PCAK.COST_ALLOCATION_KEYFLEX_ID(+)
AND PCAF.ASSIGNMENT_ID(+) = PAA.ASSIGNMENT_ID
AND PER.PERSON_ID = ASF.PERSON_ID
AND PAA.ASSIGNMENT_ID = ASF.ASSIGNMENT_ID
AND PPA.DATE_EARNED BETWEEN ASF.EFFECTIVE_START_DATE
AND ASF.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED BETWEEN NVL(PCAF.EFFECTIVE_START_DATE
, TO_DATE('01/01/1900'
, 'DD/MM/YYYY'))
AND NVL(PCAF.EFFECTIVE_END_DATE
, TO_DATE('31/12/4712'
, 'DD/MM/YYYY'))
AND PTP.PAYROLL_ID = PPA.PAYROLL_ID
AND PPA.DATE_EARNED BETWEEN PTP.START_DATE
AND PTP.END_DATE
AND PAA.ACTION_SEQUENCE = ( SELECT MAX(PAC.ACTION_SEQUENCE)
FROM PAY_ASSIGNMENT_ACTIONS PAC
WHERE PAC.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PAC.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID)