VIEW: APPS.PAY_JP_SOE_RETRO_SASSACTS_V

Source

View Text - Preformatted

SELECT paa.assignment_action_id, paa2.assignment_action_id, paa2.action_sequence, paa2.action_status, substrb(hr_general.decode_lookup('ACTION_STATUS',ppa2.action_status), 1, 80), ppa2.action_type, substrb(hr_general.decode_lookup('ACTION_TYPE',ppa2.action_type), 1, 80), ppa2.effective_date from pay_action_classifications pac, pay_payroll_actions ppa2, /* Source pact */ pay_assignment_actions paa2, /* Source assact */ pay_payroll_actions ppa, /* Retro pact */ pay_assignment_actions paa /* Retro assact */ where ppa.payroll_action_id = paa.payroll_action_id and ppa.action_type = 'G' and paa2.assignment_id = paa.assignment_id and paa2.action_sequence < paa.action_sequence and ppa2.payroll_action_id = paa2.payroll_action_id and ppa2.effective_date between ppa.start_date and ppa.effective_date and pac.classification_name = 'BACKPAID' and pac.action_type = ppa2.action_type and exists( select NULL from pay_element_entries_f pee where pee.assignment_id = paa.assignment_id and pee.entry_type = 'E' and ppa.effective_date between pee.effective_start_date and pee.effective_end_date and pee.creator_id = paa.assignment_action_id and pee.creator_type = 'R' and pee.source_id = paa2.assignment_action_id)

View Text - HTML Formatted

SELECT PAA.ASSIGNMENT_ACTION_ID
, PAA2.ASSIGNMENT_ACTION_ID
, PAA2.ACTION_SEQUENCE
, PAA2.ACTION_STATUS
, SUBSTRB(HR_GENERAL.DECODE_LOOKUP('ACTION_STATUS'
, PPA2.ACTION_STATUS)
, 1
, 80)
, PPA2.ACTION_TYPE
, SUBSTRB(HR_GENERAL.DECODE_LOOKUP('ACTION_TYPE'
, PPA2.ACTION_TYPE)
, 1
, 80)
, PPA2.EFFECTIVE_DATE
FROM PAY_ACTION_CLASSIFICATIONS PAC
, PAY_PAYROLL_ACTIONS PPA2
, /* SOURCE PACT */ PAY_ASSIGNMENT_ACTIONS PAA2
, /* SOURCE ASSACT */ PAY_PAYROLL_ACTIONS PPA
, /* RETRO PACT */ PAY_ASSIGNMENT_ACTIONS PAA /* RETRO ASSACT */
WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE = 'G'
AND PAA2.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PAA2.ACTION_SEQUENCE < PAA.ACTION_SEQUENCE
AND PPA2.PAYROLL_ACTION_ID = PAA2.PAYROLL_ACTION_ID
AND PPA2.EFFECTIVE_DATE BETWEEN PPA.START_DATE
AND PPA.EFFECTIVE_DATE
AND PAC.CLASSIFICATION_NAME = 'BACKPAID'
AND PAC.ACTION_TYPE = PPA2.ACTION_TYPE
AND EXISTS( SELECT NULL
FROM PAY_ELEMENT_ENTRIES_F PEE
WHERE PEE.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PEE.ENTRY_TYPE = 'E'
AND PPA.EFFECTIVE_DATE BETWEEN PEE.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE
AND PEE.CREATOR_ID = PAA.ASSIGNMENT_ACTION_ID
AND PEE.CREATOR_TYPE = 'R'
AND PEE.SOURCE_ID = PAA2.ASSIGNMENT_ACTION_ID)