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_ITAX_WITHHELD_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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