View: PAY_JP_PRE_ITAX_V1

Product:	PAY - Payroll
Description:	This view is used by Japanese statutory report of PAYJPITT, PAYJPITW and PAYJPPRS and valueset.
Implementation/DBA Data:	APPS.PAY_JP_PRE_ITAX_V1

View Text

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')

Columns