View: PAY_JP_ITAX_WITHHELD_V

Product:	PAY - Payroll
Description:	This view is used by Japanese statutory report of GENSEN CHOSYU HYO.
Implementation/DBA Data:	APPS.PAY_JP_ITAX_WITHHELD_V

View Text

SELECT PRE_ITAX_V2.BUSINESS_GROUP_ID
, PRE_ITAX_V2.YEAR
, PRE_ITAX_V2.ITAX_ORGANIZATION_ID
, PRE_ITAX_V2.ASSIGNMENT_ID
, PRE_ITAX_V2.ACTION_SEQUENCE
, PRE_ITAX_V2.SALARY_CATEGORY
, PRE_ITAX_V2.EFFECTIVE_DATE
, PRE_ITAX_V2.DATE_EARNED
, PRE_ITAX_V2.ASSIGNMENT_ACTION_ID
, PRE_ITAX_V2.PERSON_ID
, PRE_ITAX_V2.PERIOD_OF_SERVICE_ID
, PRE_ITAX_V2.ITAX_CATEGORY
, PRE_ITAX_V2.ITAX_YEA_CATEGORY
, PRE_ITAX_V2.EXECUTIVE_FLAG
, PRE_ITAX_V2.TAXABLE_AMT
, PRE_ITAX_V2.ITAX
, PRE_ITAX_V2.SI_PREM
, PRE_ITAX_V2.MUTUAL_AID
, PRE_ITAX_V2.PREV_SWOT_TAXABLE_AMT
, PRE_ITAX_V2.PREV_SWOT_ITAX
, PRE_ITAX_V2.PREV_SWOT_SI_PREM
, PRE_ITAX_V2.PREV_SWOT_MUTUAL_AID
, DECODE(TO_NUMBER(TO_CHAR(PPS.DATE_START
, 'YYYY'))
, PRE_ITAX_V2.YEAR
, PPS.DATE_START
, NULL)
, DECODE(TO_NUMBER(TO_CHAR(PPS.ACTUAL_TERMINATION_DATE
, 'YYYY'))
, PRE_ITAX_V2.YEAR
, PPS.ACTUAL_TERMINATION_DATE
, NULL)
, SUBSTRB(DECODE(TO_NUMBER(TO_CHAR(PPS.ACTUAL_TERMINATION_DATE
, 'YYYY'))
, PRE_ITAX_V2.YEAR
, PPS.LEAVING_REASON
, NULL)
, 1
, 30)
, PRE_ITAX_V2.LEGISLATIVE_PARAMETERS
, PRE_ITAX_V2.ITAX_DPNT_REF_TYPE
FROM PER_PERIODS_OF_SERVICE PPS
, /* MERGED PAY_JP_PRE_ITAX_V2 */ ( SELECT PRE_ITAX_V1.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TO_NUMBER(TO_CHAR(PRE_ITAX_V1.EFFECTIVE_DATE
, 'YYYY')) YEAR
, PRE_ITAX_V1.ITAX_ORGANIZATION_ID ITAX_ORGANIZATION_ID
, PRE_ITAX_V1.ASSIGNMENT_ID ASSIGNMENT_ID
, MAX(DECODE(PAA_2.ACTION_SEQUENCE
, PRE_ITAX_V1.ACTION_SEQUENCE
, PAA_2.ACTION_SEQUENCE
, NULL)) ACTION_SEQUENCE
, MAX(DECODE(PAA_2.ACTION_SEQUENCE
, PRE_ITAX_V1.ACTION_SEQUENCE
, PPT_2.SALARY_CATEGORY
, NULL)) SALARY_CATEGORY
, MAX(DECODE(PAA_2.ACTION_SEQUENCE
, PRE_ITAX_V1.ACTION_SEQUENCE
, PPA_2.EFFECTIVE_DATE
, NULL)) EFFECTIVE_DATE
, MAX(DECODE(PAA_2.ACTION_SEQUENCE
, PRE_ITAX_V1.ACTION_SEQUENCE
, PPA_2.DATE_EARNED
, NULL)) DATE_EARNED
, MAX(DECODE(PAA_2.ACTION_SEQUENCE
, PRE_ITAX_V1.ACTION_SEQUENCE
, PPA_2.LEGISLATIVE_PARAMETERS
, NULL)) LEGISLATIVE_PARAMETERS
, MAX(DECODE(PAA_2.ACTION_SEQUENCE
, PRE_ITAX_V1.ACTION_SEQUENCE
, PAY.PRL_INFORMATION1
, NULL)) ITAX_DPNT_REF_TYPE
, MAX(DECODE(PAA_2.ACTION_SEQUENCE
, PRE_ITAX_V1.ACTION_SEQUENCE
, PAA_2.ASSIGNMENT_ACTION_ID
, NULL)) ASSIGNMENT_ACTION_ID
, MAX(DECODE(PAA_2.ACTION_SEQUENCE
, PRE_ITAX_V1.ACTION_SEQUENCE
, PA_2.PERSON_ID
, NULL)) PERSON_ID
, MAX(DECODE(PAA_2.ACTION_SEQUENCE
, PRE_ITAX_V1.ACTION_SEQUENCE
, PA_2.PERIOD_OF_SERVICE_ID
, NULL)) PERIOD_OF_SERVICE_ID
, MAX(DECODE(PAA_2.ACTION_SEQUENCE
, PRE_ITAX_V1.ACTION_SEQUENCE
, PPT_2.ITAX_CATEGORY
, NULL)) ITAX_CATEGORY
, MAX(DECODE(PAA_2.ACTION_SEQUENCE
, PRE_ITAX_V1.ACTION_SEQUENCE
, PPT_2.ITAX_YEA_CATEGORY
, NULL)) ITAX_YEA_CATEGORY
, DECODE( SIGN( SUM( DECODE( PPT_2.SALARY_CATEGORY
, 'TERM'
, NULL
, DECODE( PPT_2.ITAX_ORGANIZATION_ID
, PRE_ITAX_V1.ITAX_ORGANIZATION_ID
, DECODE( PA_2.EMPLOYMENT_CATEGORY
, 'JP_EX'
, 1
, NULL )
, NULL ) ) ) )
, 1
, 'Y'
, 'N' ) EXECUTIVE_FLAG
, NVL( SUM( DECODE( PPT_2.SALARY_CATEGORY
, 'TERM'
, NULL
, DECODE( PPT_2.ITAX_ORGANIZATION_ID
, PRE_ITAX_V1.ITAX_ORGANIZATION_ID
, PPT_2.TAXABLE_SAL_AMT + PPT_2.TAXABLE_MAT_AMT
, NULL ) ) )
, 0) TAXABLE_AMT
, NVL( SUM( DECODE( PPT_2.SALARY_CATEGORY
, 'TERM'
, NULL
, DECODE( PPT_2.ITAX_ORGANIZATION_ID
, PRE_ITAX_V1.ITAX_ORGANIZATION_ID
, PPT_2.ITAX + PPT_2.ITAX_ADJUSTMENT
, NULL ) ) )
, 0) ITAX
, NVL( SUM( DECODE( PPT_2.SALARY_CATEGORY
, 'TERM'
, NULL
, DECODE( PPT_2.ITAX_ORGANIZATION_ID
, PRE_ITAX_V1.ITAX_ORGANIZATION_ID
, PPT_2.HI_PREM_EE + PPT_2.WP_PREM_EE+PPT_2.WPF_PREM_EE + PPT_2.UI_PREM_EE + PPT_2.MUTUAL_AID
, NULL ) ) )
, 0) SI_PREM
, NVL( SUM( DECODE( PPT_2.SALARY_CATEGORY
, 'TERM'
, NULL
, DECODE( PPT_2.ITAX_ORGANIZATION_ID
, PRE_ITAX_V1.ITAX_ORGANIZATION_ID
, PPT_2.MUTUAL_AID
, NULL ) ) )
, 0) MUTUAL_AID
, NVL( SUM( DECODE( PPT_2.SALARY_CATEGORY
, 'TERM'
, NULL
, DECODE( PPT_2.ITAX_ORGANIZATION_ID
, PRE_ITAX_V1.ITAX_ORGANIZATION_ID
, NULL
, PPT_2.TAXABLE_SAL_AMT + PPT_2.TAXABLE_MAT_AMT ) ) )
, 0) PREV_SWOT_TAXABLE_AMT
, NVL( SUM( DECODE( PPT_2.SALARY_CATEGORY
, 'TERM'
, NULL
, DECODE( PPT_2.ITAX_ORGANIZATION_ID
, PRE_ITAX_V1.ITAX_ORGANIZATION_ID
, NULL
, PPT_2.ITAX + PPT_2.ITAX_ADJUSTMENT) ) )
, 0) PREV_SWOT_ITAX
, NVL( SUM( DECODE( PPT_2.SALARY_CATEGORY
, 'TERM'
, NULL
, DECODE( PPT_2.ITAX_ORGANIZATION_ID
, PRE_ITAX_V1.ITAX_ORGANIZATION_ID
, NULL
, PPT_2.HI_PREM_EE + PPT_2.WP_PREM_EE + PPT_2.WPF_PREM_EE + PPT_2.UI_PREM_EE + PPT_2.MUTUAL_AID) ) )
, 0) PREV_SWOT_SI_PREM
, NVL( SUM( DECODE( PPT_2.SALARY_CATEGORY
, 'TERM'
, NULL
, DECODE( PPT_2.ITAX_ORGANIZATION_ID
, PRE_ITAX_V1.ITAX_ORGANIZATION_ID
, NULL
, PPT_2.MUTUAL_AID) ) )
, 0) PREV_SWOT_MUTUAL_AID
, NVL( SUM( DECODE( PPT_2.SALARY_CATEGORY
, 'TERM'
, DECODE( PPT_2.ITAX_ORGANIZATION_ID
, PRE_ITAX_V1.ITAX_ORGANIZATION_ID
, PPT_2.TAXABLE_SAL_AMT + PPT_2.TAXABLE_MAT_AMT
, NULL )
, NULL ) )
, 0) TERM_TAXABLE_AMT
, NVL( SUM( DECODE( PPT_2.SALARY_CATEGORY
, 'TERM'
, DECODE( PPT_2.ITAX_ORGANIZATION_ID
, PRE_ITAX_V1.ITAX_ORGANIZATION_ID
, PPT_2.ITAX + PPT_2.ITAX_ADJUSTMENT
, NULL )
, NULL ) )
, 0) TERM_ITAX FROM PAY_ALL_PAYROLLS_F PAY
, PER_ALL_ASSIGNMENTS_F PA_2
, PAY_JP_PRE_TAX PPT_2
, PAY_PAYROLL_ACTIONS PPA_2
, PAY_ASSIGNMENT_ACTIONS PAA_2
, /* MERGED PAY_JP_PRE_ITAX_V1 */ ( /*****************************************/ /* IN CASE YEAR-END-PROCESS IS PROCESSED */ /*****************************************/ SELECT PPA.BUSINESS_GROUP_ID
, PPT.ITAX_ORGANIZATION_ID
, PPA.EFFECTIVE_DATE
, PAA.ASSIGNMENT_ID
, PAA.ACTION_SEQUENCE FROM PAY_JP_PRE_TAX PPT
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA WHERE PPA.ACTION_TYPE IN ('R'
, 'Q'
, 'B'
, 'I') AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND PAA.ACTION_STATUS = 'C' AND PPT.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID AND PPT.ACTION_STATUS = 'C' AND PPT.SALARY_CATEGORY IN ('YEA'
, 'RE_YEA') AND PPT.ITAX_CATEGORY IN ('M_KOU'
, 'M_OTSU'
, 'D_KOU'
, 'D_OTSU'
, 'D_HEI') /* CHECK THAT FUTURE YEA ASSACTDOESN'T EXIST IN THE SAME YEAR */ AND NOT EXISTS( SELECT NULL FROM PAY_JP_PRE_TAX PPT2
, PAY_PAYROLL_ACTIONS PPA2
, PAY_ASSIGNMENT_ACTIONS PAA2 WHERE PAA2.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND PAA2.ACTION_SEQUENCE > PAA.ACTION_SEQUENCE 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 PPT2.ASSIGNMENT_ACTION_ID = PAA2.ASSIGNMENT_ACTION_ID AND PPT2.ACTION_STATUS = 'C' AND PPT2.ITAX_ORGANIZATION_ID = PPT.ITAX_ORGANIZATION_ID AND PPT2.SALARY_CATEGORY IN ('YEA'
, 'RE_YEA') AND PPT2.ITAX_CATEGORY IN ('M_KOU'
, 'M_OTSU'
, 'D_KOU'
, 'D_OTSU'
, 'D_HEI') AND NOT EXISTS( SELECT NULL FROM PAY_PAYROLL_ACTIONS PPA3
, PAY_ASSIGNMENT_ACTIONS PAA3
, PAY_ACTION_INTERLOCKS PAI3 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_PAYROLL_ACTIONS PPA4
, PAY_ASSIGNMENT_ACTIONS PAA4
, PAY_ACTION_INTERLOCKS PAI4
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') UNION /*********************************************/ /* IN CASE YEAR-END-PROCESS IS NOT PROCESSED */ /*********************************************/ SELECT PPA.BUSINESS_GROUP_ID
, PPT.ITAX_ORGANIZATION_ID
, PPA.EFFECTIVE_DATE
, PAA.ASSIGNMENT_ID
, PAA.ACTION_SEQUENCE FROM PAY_JP_PRE_TAX PPT
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA WHERE PPA.ACTION_TYPE IN ('R'
, 'Q'
, 'B'
, 'I') AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND PAA.ACTION_STATUS = 'C' AND PPT.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID AND PPT.ACTION_STATUS = 'C' AND PPT.SALARY_CATEGORY IN ('SALARY'
, 'BONUS'
, 'SP_BONUS'
, 'TERM') AND PPT.ITAX_CATEGORY IN ('M_KOU'
, 'M_OTSU'
, 'D_KOU'
, 'D_OTSU'
, 'D_HEI') /* NEWEST ASSIGNMENT_ACTION IN THE TARGET YEAR */ AND NOT EXISTS( SELECT NULL FROM PAY_JP_PRE_TAX PPT2
, PAY_PAYROLL_ACTIONS PPA2
, PAY_ASSIGNMENT_ACTIONS PAA2 WHERE PAA2.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND PAA2.ACTION_SEQUENCE > PAA.ACTION_SEQUENCE 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 PPT2.ASSIGNMENT_ACTION_ID = PAA2.ASSIGNMENT_ACTION_ID AND PPT2.ACTION_STATUS = 'C' AND PPT2.ITAX_ORGANIZATION_ID = PPT.ITAX_ORGANIZATION_ID AND PPT2.SALARY_CATEGORY IN ('SALARY'
, 'BONUS'
, 'SP_BONUS'
, 'TERM') AND PPT2.ITAX_CATEGORY IN ('M_KOU'
, 'M_OTSU'
, 'D_KOU'
, 'D_OTSU'
, 'D_HEI') AND NOT EXISTS( SELECT NULL FROM PAY_PAYROLL_ACTIONS PPA3
, PAY_ASSIGNMENT_ACTIONS PAA3
, PAY_ACTION_INTERLOCKS PAI3 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')) /* CHECK THAT YEA ASSACT DOESN'T EXIST */ AND NOT EXISTS( SELECT NULL FROM PAY_JP_PRE_TAX PPT4
, PAY_PAYROLL_ACTIONS PPA4
, PAY_ASSIGNMENT_ACTIONS PAA4 WHERE PAA4.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND PAA4.ACTION_STATUS = 'C' AND PPA4.PAYROLL_ACTION_ID = PAA4.PAYROLL_ACTION_ID AND PPA4.ACTION_TYPE IN ('R'
, 'Q'
, 'B'
, 'I') AND TO_CHAR(PPA4.EFFECTIVE_DATE
, 'YYYY') = TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY') AND PPT4.ASSIGNMENT_ACTION_ID = PAA4.ASSIGNMENT_ACTION_ID AND PPT4.ACTION_STATUS = 'C' AND PPT4.ITAX_ORGANIZATION_ID = PPT.ITAX_ORGANIZATION_ID AND PPT4.SALARY_CATEGORY IN ('YEA'
, 'RE_YEA') AND PPT4.ITAX_CATEGORY IN ('M_KOU'
, 'M_OTSU'
, 'D_KOU'
, 'D_OTSU'
, 'D_HEI') AND NOT EXISTS( SELECT NULL FROM PAY_PAYROLL_ACTIONS PPA5
, PAY_ASSIGNMENT_ACTIONS PAA5
, PAY_ACTION_INTERLOCKS PAI5 WHERE PAI5.LOCKED_ACTION_ID = PAA4.ASSIGNMENT_ACTION_ID AND PAA5.ASSIGNMENT_ACTION_ID = PAI5.LOCKING_ACTION_ID AND PPA5.PAYROLL_ACTION_ID = PAA5.PAYROLL_ACTION_ID AND PPA5.ACTION_TYPE = 'V')) /* NOT LOCKED BY REVERSAL ASSACT */ AND NOT EXISTS( SELECT NULL FROM PAY_PAYROLL_ACTIONS PPA6
, PAY_ASSIGNMENT_ACTIONS PAA6
, PAY_ACTION_INTERLOCKS PAI6 WHERE PAI6.LOCKED_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID AND PAA6.ASSIGNMENT_ACTION_ID = PAI6.LOCKING_ACTION_ID AND PPA6.PAYROLL_ACTION_ID = PAA6.PAYROLL_ACTION_ID AND PPA6.ACTION_TYPE = 'V') ) PRE_ITAX_V1 WHERE PAA_2.ASSIGNMENT_ID = PRE_ITAX_V1.ASSIGNMENT_ID AND PAA_2.ACTION_STATUS = 'C' AND PPA_2.PAYROLL_ACTION_ID = PAA_2.PAYROLL_ACTION_ID AND PPA_2.ACTION_TYPE IN ('R'
, 'Q'
, 'B'
, 'I') AND TO_CHAR(PPA_2.EFFECTIVE_DATE
, 'YYYY') = TO_CHAR(PRE_ITAX_V1.EFFECTIVE_DATE
, 'YYYY') AND PPT_2.ASSIGNMENT_ACTION_ID = PAA_2.ASSIGNMENT_ACTION_ID AND PPT_2.ACTION_STATUS = 'C' AND ( (PPT_2.SALARY_CATEGORY IN ('SALARY'
, 'BONUS'
, 'SP_BONUS'
, 'YEA'
, 'RE_YEA')
AND PAA_2.ACTION_SEQUENCE <= PRE_ITAX_V1.ACTION_SEQUENCE) OR (PPT_2.SALARY_CATEGORY = 'TERM') ) AND PPT_2.ITAX_CATEGORY IN ('M_KOU'
, 'M_OTSU'
, 'D_KOU'
, 'D_OTSU'
, 'D_HEI') AND PA_2.ASSIGNMENT_ID = PAA_2.ASSIGNMENT_ID AND PPA_2.EFFECTIVE_DATE BETWEEN PA_2.EFFECTIVE_START_DATE
AND PA_2.EFFECTIVE_END_DATE AND PAY.PAYROLL_ID = PPA_2.PAYROLL_ID AND PPA_2.EFFECTIVE_DATE BETWEEN PAY.EFFECTIVE_START_DATE
AND PAY.EFFECTIVE_END_DATE AND NOT EXISTS( SELECT NULL FROM PAY_PAYROLL_ACTIONS PPA2_2
, PAY_ASSIGNMENT_ACTIONS PAA2_2
, PAY_ACTION_INTERLOCKS PAI2_2 WHERE PAI2_2.LOCKED_ACTION_ID = PAA_2.ASSIGNMENT_ACTION_ID AND PAA2_2.ASSIGNMENT_ACTION_ID = PAI2_2.LOCKING_ACTION_ID AND PPA2_2.PAYROLL_ACTION_ID = PAA2_2.PAYROLL_ACTION_ID AND PPA2_2.ACTION_TYPE = 'V') GROUP BY PRE_ITAX_V1.BUSINESS_GROUP_ID
, TO_NUMBER(TO_CHAR(PRE_ITAX_V1.EFFECTIVE_DATE
, 'YYYY'))
, PRE_ITAX_V1.ITAX_ORGANIZATION_ID
, PRE_ITAX_V1.ASSIGNMENT_ID ) PRE_ITAX_V2
WHERE PPS.PERIOD_OF_SERVICE_ID = PRE_ITAX_V2.PERIOD_OF_SERVICE_ID

Columns