VIEW: APPS.PAY_HK_IR56_QUARTERS_ACTIONS_V

Source

View Text - Preformatted

SELECT paei.assignment_extra_info_id assignment_extra_info_id, paa.assignment_id assignment_id, paa.assignment_action_id assignment_action_id, paa.action_sequence action_sequence, paa.tax_unit_id legal_employer_id, to_date(paei.aei_information7,'YYYY/MM/DD HH24:MI:SS') start_date, to_date(paei.aei_information8,'YYYY/MM/DD HH24:MI:SS') end_date, to_number(paei.aei_information20) l_source_id from per_assignment_extra_info paei, pay_assignment_actions paa, pay_payroll_actions ppa WHERE paei.assignment_id = paa.assignment_id and paa.payroll_action_id = ppa.payroll_action_id and paei.information_type = 'HR_QUARTERS_INFO_HK' and paa.assignment_action_id in (select paa2.assignment_action_id from pay_action_contexts pac , pay_assignment_actions paa2 , pay_payroll_actions ppa2 , ff_contexts fc where pac.assignment_id = paa2.assignment_id and pac.assignment_action_id = paa2.assignment_action_id and paa2.payroll_action_id = ppa2.payroll_action_id and paa.action_sequence <= paa2.action_sequence and pac.context_value = paei.aei_information20 and pac.context_id = fc.context_id and fc.context_name = 'SOURCE_ID' and ppa2.effective_date >= (to_date('01-04-'||to_char(fnd_number.canonical_to_number( to_char(ppa.effective_date, 'YYYY')) + decode(sign(ppa.effective_date - to_date('01-04-' || to_char(ppa.effective_date, 'YYYY'), 'DD-MM-YYYY')), -1, -1, 0)), 'DD-MM-YYYY')) )

View Text - HTML Formatted

SELECT PAEI.ASSIGNMENT_EXTRA_INFO_ID ASSIGNMENT_EXTRA_INFO_ID
, PAA.ASSIGNMENT_ID ASSIGNMENT_ID
, PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, PAA.ACTION_SEQUENCE ACTION_SEQUENCE
, PAA.TAX_UNIT_ID LEGAL_EMPLOYER_ID
, TO_DATE(PAEI.AEI_INFORMATION7
, 'YYYY/MM/DD HH24:MI:SS') START_DATE
, TO_DATE(PAEI.AEI_INFORMATION8
, 'YYYY/MM/DD HH24:MI:SS') END_DATE
, TO_NUMBER(PAEI.AEI_INFORMATION20) L_SOURCE_ID
FROM PER_ASSIGNMENT_EXTRA_INFO PAEI
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE PAEI.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PAEI.INFORMATION_TYPE = 'HR_QUARTERS_INFO_HK'
AND PAA.ASSIGNMENT_ACTION_ID IN (SELECT PAA2.ASSIGNMENT_ACTION_ID
FROM PAY_ACTION_CONTEXTS PAC
, PAY_ASSIGNMENT_ACTIONS PAA2
, PAY_PAYROLL_ACTIONS PPA2
, FF_CONTEXTS FC
WHERE PAC.ASSIGNMENT_ID = PAA2.ASSIGNMENT_ID
AND PAC.ASSIGNMENT_ACTION_ID = PAA2.ASSIGNMENT_ACTION_ID
AND PAA2.PAYROLL_ACTION_ID = PPA2.PAYROLL_ACTION_ID
AND PAA.ACTION_SEQUENCE <= PAA2.ACTION_SEQUENCE
AND PAC.CONTEXT_VALUE = PAEI.AEI_INFORMATION20
AND PAC.CONTEXT_ID = FC.CONTEXT_ID
AND FC.CONTEXT_NAME = 'SOURCE_ID'
AND PPA2.EFFECTIVE_DATE >= (TO_DATE('01-04-'||TO_CHAR(FND_NUMBER.CANONICAL_TO_NUMBER( TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')) + DECODE(SIGN(PPA.EFFECTIVE_DATE - TO_DATE('01-04-' || TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')
, 'DD-MM-YYYY'))
, -1
, -1
, 0))
, 'DD-MM-YYYY')) )