VIEW: APPS.PAY_JP_PRE_ITAX_V1

Source

View Text - Preformatted

SELECT ppa.business_group_id, pai.action_information21 itax_organization_id, ppa.effective_date, ppa.date_earned, paa.assignment_id, paa.assignment_action_id, paa.action_sequence, pai.action_information13 salary_category, pai.action_information22 itax_category, pai.action_information23 itax_yea_category from pay_payroll_actions ppa, pay_assignment_actions paa, pay_action_information pai where ppa.action_type in ('R','Q','B','I') and paa.payroll_action_id = ppa.payroll_action_id and paa.action_status='C' and pai.action_information_category = 'JP_PRE_TAX_1' and pai.action_context_type = 'AAP' and fnd_number.canonical_to_number(pai.action_information1)=paa.assignment_action_id and pai.action_information13 in ('SALARY','BONUS','SP_BONUS','TERM','YEA','RE_YEA') and pai.action_information22 in ('M_KOU','M_OTSU','D_KOU','D_OTSU','D_HEI') /* Check that future YEA assact doesn't exist in the same year */ and not exists( select NULL from pay_assignment_actions paa2, pay_payroll_actions ppa2, pay_action_information pai2 where paa2.assignment_id=paa.assignment_id and paa2.action_status='C' and ppa2.payroll_action_id=paa2.payroll_action_id and ppa2.action_type in ('R','Q','B','I') and to_char(ppa2.effective_date,'YYYY')=to_char(ppa.effective_date,'YYYY') and pai2.action_information_category = 'JP_PRE_TAX_1' and pai2.action_context_type = 'AAP' and fnd_number.canonical_to_number(pai2.action_information1)=paa2.assignment_action_id and pai2.action_information21=pai.action_information21 and ( ( ( ( pai.action_information13 in ('YEA','RE_YEA') AND pai2.action_information13 in ('YEA','RE_YEA') ) OR ( pai.action_information13 in ('SALARY','BONUS','SP_BONUS','TERM') AND pai2.action_information13 in ('SALARY','BONUS','SP_BONUS','TERM') ) ) AND paa2.action_sequence > paa.action_sequence ) OR ( pai.action_information13 in ('SALARY','BONUS','SP_BONUS','TERM') AND pai2.action_information13 in ('YEA','RE_YEA') ) ) and pai2.action_information22 in ('M_KOU','M_OTSU','D_KOU','D_OTSU','D_HEI') and not exists( select NULL from pay_action_interlocks pai3, pay_assignment_actions paa3, pay_payroll_actions ppa3 where pai3.locked_action_id=paa2.assignment_action_id and paa3.assignment_action_id=pai3.locking_action_id and ppa3.payroll_action_id=paa3.payroll_action_id and ppa3.action_type='V')) /* Not locked by Reversal assact */ and not exists( select NULL from pay_action_interlocks pai4, pay_assignment_actions paa4, pay_payroll_actions ppa4 where pai4.locked_action_id=paa.assignment_action_id and paa4.assignment_action_id=pai4.locking_action_id and ppa4.payroll_action_id=paa4.payroll_action_id and ppa4.action_type='V')

View Text - HTML Formatted

SELECT PPA.BUSINESS_GROUP_ID
, PAI.ACTION_INFORMATION21 ITAX_ORGANIZATION_ID
, PPA.EFFECTIVE_DATE
, PPA.DATE_EARNED
, PAA.ASSIGNMENT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ACTION_SEQUENCE
, PAI.ACTION_INFORMATION13 SALARY_CATEGORY
, PAI.ACTION_INFORMATION22 ITAX_CATEGORY
, PAI.ACTION_INFORMATION23 ITAX_YEA_CATEGORY
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_ACTION_INFORMATION PAI
WHERE PPA.ACTION_TYPE IN ('R'
, 'Q'
, 'B'
, 'I')
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS='C'
AND PAI.ACTION_INFORMATION_CATEGORY = 'JP_PRE_TAX_1'
AND PAI.ACTION_CONTEXT_TYPE = 'AAP'
AND FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION1)=PAA.ASSIGNMENT_ACTION_ID
AND PAI.ACTION_INFORMATION13 IN ('SALARY'
, 'BONUS'
, 'SP_BONUS'
, 'TERM'
, 'YEA'
, 'RE_YEA')
AND PAI.ACTION_INFORMATION22 IN ('M_KOU'
, 'M_OTSU'
, 'D_KOU'
, 'D_OTSU'
, 'D_HEI') /* CHECK THAT FUTURE YEA ASSACT DOESN'T EXIST IN THE SAME YEAR */
AND NOT EXISTS( SELECT NULL
FROM PAY_ASSIGNMENT_ACTIONS PAA2
, PAY_PAYROLL_ACTIONS PPA2
, PAY_ACTION_INFORMATION PAI2
WHERE PAA2.ASSIGNMENT_ID=PAA.ASSIGNMENT_ID
AND PAA2.ACTION_STATUS='C'
AND PPA2.PAYROLL_ACTION_ID=PAA2.PAYROLL_ACTION_ID
AND PPA2.ACTION_TYPE IN ('R'
, 'Q'
, 'B'
, 'I')
AND TO_CHAR(PPA2.EFFECTIVE_DATE
, 'YYYY')=TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')
AND PAI2.ACTION_INFORMATION_CATEGORY = 'JP_PRE_TAX_1'
AND PAI2.ACTION_CONTEXT_TYPE = 'AAP'
AND FND_NUMBER.CANONICAL_TO_NUMBER(PAI2.ACTION_INFORMATION1)=PAA2.ASSIGNMENT_ACTION_ID
AND PAI2.ACTION_INFORMATION21=PAI.ACTION_INFORMATION21
AND ( ( ( ( PAI.ACTION_INFORMATION13 IN ('YEA'
, 'RE_YEA')
AND PAI2.ACTION_INFORMATION13 IN ('YEA'
, 'RE_YEA') ) OR ( PAI.ACTION_INFORMATION13 IN ('SALARY'
, 'BONUS'
, 'SP_BONUS'
, 'TERM')
AND PAI2.ACTION_INFORMATION13 IN ('SALARY'
, 'BONUS'
, 'SP_BONUS'
, 'TERM') ) )
AND PAA2.ACTION_SEQUENCE > PAA.ACTION_SEQUENCE ) OR ( PAI.ACTION_INFORMATION13 IN ('SALARY'
, 'BONUS'
, 'SP_BONUS'
, 'TERM')
AND PAI2.ACTION_INFORMATION13 IN ('YEA'
, 'RE_YEA') ) )
AND PAI2.ACTION_INFORMATION22 IN ('M_KOU'
, 'M_OTSU'
, 'D_KOU'
, 'D_OTSU'
, 'D_HEI')
AND NOT EXISTS( SELECT NULL
FROM PAY_ACTION_INTERLOCKS PAI3
, PAY_ASSIGNMENT_ACTIONS PAA3
, PAY_PAYROLL_ACTIONS PPA3
WHERE PAI3.LOCKED_ACTION_ID=PAA2.ASSIGNMENT_ACTION_ID
AND PAA3.ASSIGNMENT_ACTION_ID=PAI3.LOCKING_ACTION_ID
AND PPA3.PAYROLL_ACTION_ID=PAA3.PAYROLL_ACTION_ID
AND PPA3.ACTION_TYPE='V')) /* NOT LOCKED BY REVERSAL ASSACT */
AND NOT EXISTS( SELECT NULL
FROM PAY_ACTION_INTERLOCKS PAI4
, PAY_ASSIGNMENT_ACTIONS PAA4
, PAY_PAYROLL_ACTIONS PPA4
WHERE PAI4.LOCKED_ACTION_ID=PAA.ASSIGNMENT_ACTION_ID
AND PAA4.ASSIGNMENT_ACTION_ID=PAI4.LOCKING_ACTION_ID
AND PPA4.PAYROLL_ACTION_ID=PAA4.PAYROLL_ACTION_ID
AND PPA4.ACTION_TYPE='V')