eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PAY_JP_PRE_ITAX_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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