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_V2

Source

View Text - Preformatted


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

View Text - HTML Formatted

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