SELECT /*+ ORDERED NO_MERGE(PPT) INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N50) INDEX(PPT PAY_JP_PRE_TAX_U1) */ PPA.BUSINESS_GROUP_ID , PPT.ITAX_ORGANIZATION_ID , PPA.EFFECTIVE_DATE , PPA.DATE_EARNED , PAA.ASSIGNMENT_ID , PAA.ASSIGNMENT_ACTION_ID , PAA.ACTION_SEQUENCE , PPT.SALARY_CATEGORY , PPT.ITAX_CATEGORY , PPT.ITAX_YEA_CATEGORY FROM PAY_PAYROLL_ACTIONS PPA , PAY_ASSIGNMENT_ACTIONS PAA , PAY_JP_PRE_TAX PPT 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 ASSACT DOESN'T EXIST IN THE SAME YEAR */ AND NOT EXISTS( SELECT /*+ ORDERED NO_MERGE(PPT2) INDEX(PAA2 PAY_ASSIGNMENT_ACTIONS_N51) INDEX(PPA2 PAY_PAYROLL_ACTIONS_PK) INDEX(PPT2 PAY_JP_PRE_TAX_U1) */ NULL FROM PAY_ASSIGNMENT_ACTIONS PAA2 , PAY_PAYROLL_ACTIONS PPA2 , PAY_JP_PRE_TAX PPT2 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 /*+ ORDERED INDEX(PAI3 PAY_ACTION_INTERLOCKS_FK2) INDEX(PAA3 PAY_ASSIGNMENT_ACTIONS_PK) INDEX(PPA3 PAY_PAYROLL_ACTIONS_PK) */ 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 /*+ ORDERED INDEX(PAI4 PAY_ACTION_INTERLOCKS_FK2) INDEX(PAA4 PAY_ASSIGNMENT_ACTIONS_PK) INDEX(PPA4 PAY_PAYROLL_ACTIONS_PK) */ 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') UNION /* IN CASE YEAR-END-PROCESS IS NOT PROCESSED */ SELECT /*+ ORDERED NO_MERGE(PPT) INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N50) INDEX(PPT PAY_JP_PRE_TAX_U1) */ PPA.BUSINESS_GROUP_ID , PPT.ITAX_ORGANIZATION_ID , PPA.EFFECTIVE_DATE , PPA.DATE_EARNED , PAA.ASSIGNMENT_ID , PAA.ASSIGNMENT_ACTION_ID , PAA.ACTION_SEQUENCE , PPT.SALARY_CATEGORY , PPT.ITAX_CATEGORY , PPT.ITAX_YEA_CATEGORY FROM PAY_PAYROLL_ACTIONS PPA , PAY_ASSIGNMENT_ACTIONS PAA , PAY_JP_PRE_TAX PPT 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 /*+ ORDERED NO_MERGE(PPT2) INDEX(PAA2 PAY_ASSIGNMENT_ACTIONS_N51) INDEX(PPA2 PAY_PAYROLL_ACTIONS_PK) INDEX(PPT2 PAY_JP_PRE_TAX_U1) */ NULL FROM PAY_ASSIGNMENT_ACTIONS PAA2 , PAY_PAYROLL_ACTIONS PPA2 , PAY_JP_PRE_TAX PPT2 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 /*+ ORDERED INDEX(PAI3 PAY_ACTION_INTERLOCKS_FK2) INDEX(PAA3 PAY_ASSIGNMENT_ACTIONS_PK) INDEX(PPA3 PAY_PAYROLL_ACTIONS_PK) */ 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')) /* CHECK THAT YEA ASSACT DOESN'T EXIST */ AND NOT EXISTS( SELECT /*+ ORDERED NO_MERGE(PPT4) INDEX(PAA4 PAY_ASSIGNMENT_ACTIONS_N51) INDEX(PPA4 PAY_PAYROLL_ACTIONS_PK) INDEX(PPT4 PAY_JP_PRE_TAX_U1) */ NULL FROM PAY_ASSIGNMENT_ACTIONS PAA4 , PAY_PAYROLL_ACTIONS PPA4 , PAY_JP_PRE_TAX PPT4 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 /*+ ORDERED INDEX(PAI5 PAY_ACTION_INTERLOCKS_FK2) INDEX(PAA5 PAY_ASSIGNMENT_ACTIONS_PK) INDEX(PPA5 PAY_PAYROLL_ACTIONS_PK) */ NULL FROM PAY_ACTION_INTERLOCKS PAI5 , PAY_ASSIGNMENT_ACTIONS PAA5 , PAY_PAYROLL_ACTIONS PPA5 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 /*+ ORDERED INDEX(PAI6 PAY_ACTION_INTERLOCKS_FK2) INDEX(PAA6 PAY_ASSIGNMENT_ACTIONS_PK) INDEX(PPA6 PAY_PAYROLL_ACTIONS_PK) */ NULL FROM PAY_ACTION_INTERLOCKS PAI6 , PAY_ASSIGNMENT_ACTIONS PAA6 , PAY_PAYROLL_ACTIONS PPA6 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')
APPS.PAY_JP_PRE_ITAX_V
[Home] [Help]