View: PAY_JP_PRE_ITAX_V2

Product:	PAY - Payroll
Description:	This view is used by Japanese statutory report of yeary earnig report(PAYJPITT).
Implementation/DBA Data:	APPS.PAY_JP_PRE_ITAX_V2

View Text

SELECT /*+ ORDERED NO_MERGE(PPI) NO_MERGE(PPT) INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N51) INDEX(PPA PAY_PAYROLL_ACTIONS_PK) INDEX(PPT PAY_JP_PRE_TAX_U1) INDEX(PA PER_ASSIGNMENTS_F_PK) */ PPI.BUSINESS_GROUP_ID
, TO_NUMBER(TO_CHAR(PPI.EFFECTIVE_DATE
, 'YYYY'))
, PPI.ITAX_ORGANIZATION_ID
, PPI.ASSIGNMENT_ID
, MAX(DECODE(PAA.ACTION_SEQUENCE
, PPI.ACTION_SEQUENCE
, PPI.ACTION_SEQUENCE
, NULL))
, MAX(DECODE(PAA.ACTION_SEQUENCE
, PPI.ACTION_SEQUENCE
, PPA.EFFECTIVE_DATE
, NULL))
, MAX(DECODE(PAA.ACTION_SEQUENCE
, PPI.ACTION_SEQUENCE
, PPA.DATE_EARNED
, NULL))
, MAX(DECODE(PAA.ACTION_SEQUENCE
, PPI.ACTION_SEQUENCE
, PAA.ASSIGNMENT_ACTION_ID
, NULL))
, MAX(DECODE(PAA.ACTION_SEQUENCE
, PPI.ACTION_SEQUENCE
, PA.PERSON_ID
, NULL))
, MAX(DECODE(PAA.ACTION_SEQUENCE
, PPI.ACTION_SEQUENCE
, PA.PERIOD_OF_SERVICE_ID
, NULL))
, MAX(DECODE(PAA.ACTION_SEQUENCE
, PPI.ACTION_SEQUENCE
, PPT.ITAX_CATEGORY
, NULL))
, MAX(DECODE(PAA.ACTION_SEQUENCE
, PPI.ACTION_SEQUENCE
, PPT.ITAX_YEA_CATEGORY
, NULL))
, DECODE( SIGN( SUM( DECODE( PPT.SALARY_CATEGORY
, 'TERM'
, NULL
, DECODE( PPT.ITAX_ORGANIZATION_ID
, PPI.ITAX_ORGANIZATION_ID
, DECODE(PA.EMPLOYMENT_CATEGORY
, 'JP_EX'
, 1
, NULL)
, NULL ) ) ) )
, 1
, 'Y'
, 'N' )
, NVL( SUM( DECODE( PPT.SALARY_CATEGORY
, 'TERM'
, NULL
, DECODE( PPT.ITAX_ORGANIZATION_ID
, PPI.ITAX_ORGANIZATION_ID
, PPT.TAXABLE_SAL_AMT+PPT.TAXABLE_MAT_AMT
, NULL ) ) )
, 0 )
, NVL( SUM( DECODE( PPT.SALARY_CATEGORY
, 'TERM'
, NULL
, DECODE( PPT.ITAX_ORGANIZATION_ID
, PPI.ITAX_ORGANIZATION_ID
, PPT.ITAX+PPT.ITAX_ADJUSTMENT
, NULL ) ) )
, 0 )
, NVL( SUM( DECODE( PPT.SALARY_CATEGORY
, 'TERM'
, NULL
, DECODE( PPT.ITAX_ORGANIZATION_ID
, PPI.ITAX_ORGANIZATION_ID
, PPT.HI_PREM_EE+PPT.WP_PREM_EE+PPT.WPF_PREM_EE+PPT.UI_PREM_EE+PPT.MUTUAL_AID
, NULL ) ) )
, 0 )
, NVL( SUM( DECODE( PPT.SALARY_CATEGORY
, 'TERM'
, NULL
, DECODE( PPT.ITAX_ORGANIZATION_ID
, PPI.ITAX_ORGANIZATION_ID
, PPT.MUTUAL_AID
, NULL ) ) )
, 0 )
, NVL( SUM( DECODE( PPT.SALARY_CATEGORY
, 'TERM'
, NULL
, DECODE( PPT.ITAX_ORGANIZATION_ID
, PPI.ITAX_ORGANIZATION_ID
, NULL
, PPT.TAXABLE_SAL_AMT+PPT.TAXABLE_MAT_AMT ) ) )
, 0 )
, NVL( SUM( DECODE( PPT.SALARY_CATEGORY
, 'TERM'
, NULL
, DECODE( PPT.ITAX_ORGANIZATION_ID
, PPI.ITAX_ORGANIZATION_ID
, NULL
, PPT.ITAX+PPT.ITAX_ADJUSTMENT) ) )
, 0 )
, NVL( SUM( DECODE( PPT.SALARY_CATEGORY
, 'TERM'
, NULL
, DECODE( PPT.ITAX_ORGANIZATION_ID
, PPI.ITAX_ORGANIZATION_ID
, NULL
, PPT.HI_PREM_EE+PPT.WP_PREM_EE+PPT.WPF_PREM_EE+PPT.UI_PREM_EE+PPT.MUTUAL_AID) ) )
, 0 )
, NVL( SUM( DECODE( PPT.SALARY_CATEGORY
, 'TERM'
, NULL
, DECODE( PPT.ITAX_ORGANIZATION_ID
, PPI.ITAX_ORGANIZATION_ID
, NULL
, PPT.MUTUAL_AID) ) )
, 0 )
, NVL( SUM( DECODE( PPT.SALARY_CATEGORY
, 'TERM'
, DECODE( PPT.ITAX_ORGANIZATION_ID
, PPI.ITAX_ORGANIZATION_ID
, PPT.TAXABLE_SAL_AMT+PPT.TAXABLE_MAT_AMT
, NULL )
, NULL ) )
, 0 )
, NVL( SUM( DECODE( PPT.SALARY_CATEGORY
, 'TERM'
, DECODE( PPT.ITAX_ORGANIZATION_ID
, PPI.ITAX_ORGANIZATION_ID
, PPT.ITAX+PPT.ITAX_ADJUSTMENT
, NULL )
, NULL ) )
, 0 )
FROM PAY_JP_PRE_ITAX_V1 PPI
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_JP_PRE_TAX PPT
, PER_ALL_ASSIGNMENTS_F PA
WHERE PAA.ASSIGNMENT_ID=PPI.ASSIGNMENT_ID
AND PAA.ACTION_STATUS='C'
AND PPA.PAYROLL_ACTION_ID=PAA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('R'
, 'Q'
, 'B'
, 'I')
AND TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY') = TO_CHAR(PPI.EFFECTIVE_DATE
, 'YYYY')
AND PPT.ASSIGNMENT_ACTION_ID=PAA.ASSIGNMENT_ACTION_ID
AND PPT.ACTION_STATUS='C'
AND ((PPT.SALARY_CATEGORY IN ('SALARY'
, 'BONUS'
, 'SP_BONUS'
, 'YEA'
, 'RE_YEA')
AND PAA.ACTION_SEQUENCE <= PPI.ACTION_SEQUENCE) OR (PPT.SALARY_CATEGORY = 'TERM'))
AND PPT.ITAX_CATEGORY IN ('M_KOU'
, 'M_OTSU'
, 'D_KOU'
, 'D_OTSU'
, 'D_HEI')
AND PA.ASSIGNMENT_ID=PAA.ASSIGNMENT_ID
AND PPA.EFFECTIVE_DATE BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE
AND NOT EXISTS( SELECT /*+ ORDERED INDEX(PAI2 PAY_ACTION_INTERLOCKS_FK2) INDEX(PAA2 PAY_ASSIGNMENT_ACTIONS_PK) INDEX(PPA2 PAY_PAYROLL_ACTIONS_PK) */ NULL
FROM PAY_ACTION_INTERLOCKS PAI2
, PAY_ASSIGNMENT_ACTIONS PAA2
, PAY_PAYROLL_ACTIONS PPA2
WHERE PAI2.LOCKED_ACTION_ID=PAA.ASSIGNMENT_ACTION_ID
AND PAA2.ASSIGNMENT_ACTION_ID=PAI2.LOCKING_ACTION_ID
AND PPA2.PAYROLL_ACTION_ID=PAA2.PAYROLL_ACTION_ID
AND PPA2.ACTION_TYPE='V') GROUP BY PPI.BUSINESS_GROUP_ID
, TO_NUMBER(TO_CHAR(PPI.EFFECTIVE_DATE
, 'YYYY'))
, PPI.ITAX_ORGANIZATION_ID
, PPI.ASSIGNMENT_ID

Columns