VIEW: APPS.PAY_JP_SOE_ADV_RET_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 ,pay_assignment_actions paa2 ,pay_payroll_actions ppa ,pay_assignment_actions paa WHERE ppa.payroll_action_id = paa.payroll_action_id AND ppa.action_type = 'L' 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 <= 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 = 'RR' OR pee.creator_type = 'EE' ) AND pee.source_asg_action_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
, PAY_ASSIGNMENT_ACTIONS PAA2
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE = 'L'
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 <= 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 = 'RR' OR PEE.CREATOR_TYPE = 'EE' )
AND PEE.SOURCE_ASG_ACTION_ID = PAA2.ASSIGNMENT_ACTION_ID )