VIEW: APPS.PAY_KR_SEP_RESULT_TAX_V

Source

View Text - Preformatted

SELECT paa.assignment_action_id assignment_action_id ,paa.assignment_id assignment_id ,ppa.effective_date effective_date ,ppa.date_earned date_earned ,paa.run_type_id run_type_id ,pay_kr_report_pkg.get_result_value_date (paa.assignment_action_id ,ppa.business_group_id ,'WKPD' ,'H_DATE') hiring_date ,pay_kr_report_pkg.get_result_value_date (paa.assignment_action_id ,ppa.business_group_id ,'WKPD' ,'L_DATE') leaving_date ,pay_kr_report_pkg.get_result_value_number (paa.assignment_action_id ,ppa.business_group_id ,'WKPD' ,'WKPD') working_period ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'WKPD_SEP_TAX' ,'_ASG_RUN') wkpd_sep_tax ,pay_kr_report_pkg.get_result_value_date (paa.assignment_action_id ,ppa.business_group_id ,'WKPD' ,'PREV_FH_DATE') prev_first_hiring_date ,pay_kr_report_pkg.get_result_value_date (paa.assignment_action_id ,ppa.business_group_id ,'WKPD' ,'PREV_LL_DATE') prev_last_leaving_date ,pay_kr_report_pkg.get_result_value_date (paa.assignment_action_id ,ppa.business_group_id ,'WKPD_NON_STAT_SEP_PAY' ,'H_DATE') ns_hiring_date ,pay_kr_report_pkg.get_result_value_date (paa.assignment_action_id ,ppa.business_group_id ,'WKPD_NON_STAT_SEP_PAY' ,'L_DATE') ns_leaving_date ,pay_kr_report_pkg.get_result_value_date (paa.assignment_action_id ,ppa.business_group_id ,'WKPD_NON_STAT_SEP_PAY' ,'PREV_FH_DATE') ns_prev_first_hiring_date ,pay_kr_report_pkg.get_result_value_date (paa.assignment_action_id ,ppa.business_group_id ,'WKPD_NON_STAT_SEP_PAY' ,'PREV_LL_DATE') ns_prev_last_leaving_date ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'PREV_WKPD_SEP_TAX' ,'_ASG_RUN') prev_wkpd_sep_tax ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'OVL_WKPD' ,'_ASG_RUN') overlap_wkpd ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'SVPD' ,'_ASG_RUN') service_period ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'SEP_PAY' ,'_ASG_RUN') sep_pay ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'SEP_INS' ,'_ASG_RUN') sep_ins ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'SP_SEP_ALW' ,'_ASG_RUN') sp_sep_alw ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'TOTAL_TAXABLE_EARNINGS' ,'_ASG_RUN') total_taxable_earnings ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'TAXABLE_EARNINGS_WI_PREV' ,'_ASG_RUN') taxable_earnings_wi_prev ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'INCOME_EXEM' ,'_ASG_RUN') income_exem ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'TAXATION_BASE' ,'_ASG_RUN') taxation_base ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'YTAXATION_BASE' ,'_ASG_RUN') ytaxation_base ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'YCALC_TAX' ,'_ASG_RUN') ycalc_tax ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'CALC_TAX' ,'_ASG_RUN') calc_tax ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'TAX_BREAK' ,'_ASG_RUN') tax_break ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'ACTUAL_TAX' ,'_ASG_RUN') actual_tax ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'GROSS_ITAX' ,'_ASG_RUN') gross_itax ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'GROSS_RTAX' ,'_ASG_RUN') gross_rtax ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'GROSS_STAX' ,'_ASG_RUN') gross_stax ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'ITAX' ,'_ASG_RUN') itax ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'RTAX' ,'_ASG_RUN') rtax ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'STAX' ,'_ASG_RUN') stax ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'NON_STAT_SEP_PAY_TAXABLE_EARNINGS' ,'_ASG_RUN') ns_sep_pay_amt ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'NON_STAT_SEP_PAY_WKPD_MONTH' ,'_ASG_RUN') ns_wkpd_sep_tax ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'NON_STAT_SEP_PAY_PREV_WKPD_MONTH' ,'_ASG_RUN') ns_prev_wkpd_sep_tax ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'NON_STAT_SEP_PAY_OVL_WKPD_MONTHS' ,'_ASG_RUN') ns_overlap_wkpd ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'NON_STAT_SEP_PAY_SEP_PAY_INCOME_EXEM' ,'_ASG_RUN') ns_sep_pay_income_exem ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'NON_STAT_SEP_PAY_SVPD_INCOME_EXEM' ,'_ASG_RUN') ns_svpd_income_exem ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'NON_STAT_SEP_PAY_TAXATION_BASE' ,'_ASG_RUN') ns_taxation_base ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'NON_STAT_SEP_PAY_WKPD_YEAR' ,'_ASG_RUN') ns_service_period ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'NON_STAT_SEP_PAY_YTAXATION_BASE' ,'_ASG_RUN') ns_ytaxation_base ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'NON_STAT_SEP_PAY_YCALC_TAX' ,'_ASG_RUN') ns_ycalc_tax ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'NON_STAT_SEP_PAY_CALC_TAX' ,'_ASG_RUN') ns_calc_tax ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'NON_STAT_SEP_PAY_TAX_BREAK' ,'_ASG_RUN') ns_tax_break ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'NON_STAT_SEP_PAY_ACTUAL_TAX' ,'_ASG_RUN') ns_actual_tax ,pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'PREV_ER_INFO_SEP_PAY_ENTRY_VALUE') prev_sep_pay ,pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'PREV_ER_INFO_SEP_INS_ENTRY_VALUE') prev_sep_ins ,pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'PREV_ER_INFO_SP_SEP_ALW_ENTRY_VALUE') prev_sp_sep_alw ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'Excluded Month For Non Statutory Separation Pay' ,'_ASG_RUN') ns_excluded_mth ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'Separation Pension Lump Sum Amount' ,'_ASG_RUN') sep_pen_lump_sum_amt ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'Receivable Separation Pay' ,'_ASG_RUN') receivable_sep_pay ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'Total Lump Sum Amount' ,'_ASG_RUN') total_lump_sum_amt ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'Total Received' ,'_ASG_RUN') total_received ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'Principal And Interest' ,'_ASG_RUN') principal_interest ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'Personal Contribution' ,'_ASG_RUN') personal_contribution ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'Pension Exemption' ,'_ASG_RUN') pension_exem ,pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'SEPARATION_PENSION_ACCOUNT_DETAILS_DEFINED_TYPE_ENTRY_VALUE') pension_defined_types ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'Amount Expected' ,'_ASG_RUN') amt_exp ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'Excluded Month' ,'_ASG_RUN') excluded_mth ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'Separation Pension Lump Sum Non Taxable Amount' ,'_ASG_RUN') sep_pen_lump_sum_non_tax_amt ,pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'PREV_ER_INFO_NON_TAXABLE_EARNING_ENTRY_VALUE') prev_non_tax_ear ,nvl (pay_kr_report_pkg.get_result_value_number (paa.assignment_action_id ,ppa.business_group_id ,'SEP_PAY_OVERSEAS_TAX_BREAK' ,'OVERSEAS_TAX_BREAK') ,0) overseas_tax_break ,nvl (pay_kr_report_pkg.get_result_value_number (paa.assignment_action_id ,ppa.business_group_id ,'NON_STAT_SEP_OVERSEAS_TAX_BRK' ,'OVERSEAS_TAX_BREAK') ,0) ns_overseas_tax_break ,nvl (pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'PREV_SEP_PENS_DTLS_TOTAL_RECEIVED_ENTRY_VALUE') ,0) total_received_prev ,nvl (pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'PREV_SEP_PENS_DTLS_PERS_CONTRIBUTION_ENTRY_VALUE') ,0) personal_contribution_prev ,nvl (pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'PREV_SEP_PENS_DTLS_PRINCIPAL_INTRST_ENTRY_VALUE') ,0) principal_and_interest_prev ,nvl (pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'PREV_SEP_PENS_DTLS_AMT_EXP_STAT_SEP_ENTRY_VALUE') ,0) amount_expected_prev ,nvl (pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'PREV_SEP_PENS_DTLS_AMT_EXP_NONSTAT_SEP_ENTRY_VALUE') ,0) amount_exp_nonstat_prev ,nvl (pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'PREV_SEP_PENS_DTLS_PENS_EXEM_ENTRY_VALUE') ,0) pension_exemption_prev ,nvl (pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'PREV_SEP_PENS_DTLS_DEFERRED_AMOUNT_STAT_ENTRY_VALUE') ,0) deferred_amt_stat_prev ,nvl (pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'PREV_SEP_PENS_DTLS_DEFERRED_AMOUNT_NONSTAT_ENTRY_VALUE') ,0) deferred_amt_nonstat_prev ,nvl (pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'PREV_SEP_PENS_DTLS_RECEIVED_AMOUNT_STAT_ENTRY_VALUE') ,0) received_amt_stat_prev ,nvl (pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'PREV_SEP_PENS_DTLS_RECEIVED_AMOUNT_NONSTAT_ENTRY_VALUE') ,0) received_amt_nonstat_prev ,nvl (pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'SEPARATION_PENSION_ACCOUNT_DETAILS_ACCOUNT_NUMBER_ENTRY_VALUE') ,0) sep_pen_account_number ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'DEFERRED_AMOUNT_STAT' ,'_ASG_RUN') deferred_amt_stat ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'DEFERRED_AMOUNT_NONSTAT' ,'_ASG_RUN') deferred_amt_nst ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'RECEIVED_AMOUNT_STAT' ,'_ASG_RUN') received_amt_stat ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'RECEIVED_AMOUNT_NONSTAT' ,'_ASG_RUN') received_amt_nst ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'AMOUNT_EXPECTED_NONSTAT' ,'_ASG_RUN') amt_exp_nst ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'PREV_EMP_SEP_PENS_LUMP_SUM_AMT' ,'_ASG_RUN') sep_pen_lump_sum_amount_prev ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'RECEIVABLE_NON_STAT_SEP_PAY' ,'_ASG_RUN') receivable_nonstat_sep_pay ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'TOT_LUMP_SUM_AMT_NON_STAT_SEP_PAY' ,'_ASG_RUN') ns_total_lump_sum_amount ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'PREV_EMP_SEP_PEN_LUMP_SUM_NON_TAX_AMT' ,'_ASG_RUN') prev_sep_pen_ntax_lsa ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'PREV_EMP_SEP_PENS_LUMP_SUM_AMT' ,'_ASG_RUN') prev_sep_pen_lsa ,nvl (pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'SEP_PEN_LUMP_SUM_AMT_ENT' ,'_ASG_RUN') ,0) sep_pen_lsa_stat_ent ,nvl (pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'SEP_PEN_LUMP_SUM_NST_ENT' ,'_ASG_RUN') ,0) sep_pen_lsa_nst_ent ,nvl (pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'PREV_SEP_PENS_DTLS_LUMP_SUM_AMT_STAT_ENTRY_VALUE') ,0) sep_pen_lsa_stat_ent_prev ,nvl (pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'PREV_SEP_PENS_DTLS_LUMP_SUM_AMT_NST_ENTRY_VALUE') ,0) sep_pen_lsa_nst_ent_prev ,nvl (pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'SEP_PAY_POST_TAX_DEDUCTION' ,'_ASG_RUN') ,0) post_tax_deduc ,nvl (pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'NON_STAT_SEP_PAY_POST_TAX_DEDUCTION' ,'_ASG_RUN') ,0) post_tax_deduc_ns ,nvl (pay_kr_report_pkg.get_result_value_char (paa.assignment_action_id ,ppa.business_group_id ,'SEP_PAY_POST_TAX_BRK' ,'EXCEPTION_FLAG') ,'N') exception_flag ,nvl (pay_kr_report_pkg.get_result_value_char (paa.assignment_action_id ,ppa.business_group_id ,'PREV_SEP_PENS_DTLS' ,'Account Number') ,0) sep_pen_account_number_prev ,decode (nvl (pay_kr_report_pkg.get_result_value_char (paa.assignment_action_id ,ppa.business_group_id ,'SEP_PAY_POST_TAX_BRK' ,'EXCEPTION_FLAG') ,'N') ,'N' ,nvl (pay_kr_report_pkg.get_result_value_char (paa.assignment_action_id ,ppa.business_group_id ,'SEP_PAY_POST_TAX_BRK' ,'ELIGIBLE_POST_TAX_DEDUC_FLAG') ,'Y') ,'N') ptax_eligibility_flag ,nvl (pay_kr_report_pkg.get_result_value_char (paa.assignment_action_id ,ppa.business_group_id ,'SEP_PAY_POST_TAX_BRK' ,'REASON_FOR_SEP_PAY') ,'3') sep_pay_reason ,decode (pay_kr_report_pkg.get_result_value_char (paa.assignment_action_id ,ppa.business_group_id ,'Separation Pension Account Details' ,'Financial Institute') ,NULL ,NULL ,hr_general.decode_lookup ('PAY_KR_PENSION_FINANCIAL_INST' ,pay_kr_report_pkg.get_result_value_char (paa.assignment_action_id ,ppa.business_group_id ,'Separation Pension Account Details' ,'Financial Institute'))) sep_pen_acct_fin_inst ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'Transferred Amount for Stat Sep Pay' ,'_ASG_RUN') trans_amt_stat ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'Transferred Amount for NonStat Sep Pay' ,'_ASG_RUN') trans_amt_nst ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'Deferred Tax Amount' ,'_ASG_RUN') deferred_tax_amt ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'Working Period Additional Months' ,'_ASG_RUN') wkpd_addtl_mths_stat ,pay_kr_report_pkg.get_balance_value (paa.assignment_action_id ,'Working Period Additional Months for NonStat' ,'_ASG_RUN') wkpd_addtl_mths_nst ,to_char (pay_kr_report_pkg.get_result_value_date (paa.assignment_action_id ,ppa.business_group_id ,'Separation Pay Tax Deferring Details' ,'Transferred Date') ,'DD-MON-YYYY') trans_date ,to_char (pay_kr_report_pkg.get_result_value_date (paa.assignment_action_id ,ppa.business_group_id ,'Separation Pay Tax Deferring Details' ,'Maturity Date') ,'DD-MON-YYYY') maturity_date ,pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'PREV_ER_INFO2_ADDITIONAL_MONTHS_FOR_NONSTAT_ENTRY_VALUE') prev_addtl_mth_nst ,pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'PREV_ER_INFO2_ADDITIONAL_MONTHS_FOR_STAT_ENTRY_VALUE') prev_addtl_mth_stat ,pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'PREV_ER_INFO2_OVERLAPPING_MONTHS_FOR_NONSTAT_ENTRY_VALUE') prev_ovl_mth_nst ,pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'PREV_ER_INFO2_OVERLAPPING_MONTHS_FOR_STAT_ENTRY_VALUE') prev_ovl_mth_stat ,pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'PREV_ER_INFO2_WORKING_YEAR_FOR_NONSTAT_ENTRY_VALUE') prev_work_year_nst ,pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'PREV_ER_INFO2_WORKING_YEAR_FOR_STAT_ENTRY_VALUE') prev_work_year_stat ,pay_kr_report_pkg.get_result_value_char (paa.assignment_action_id ,ppa.business_group_id ,'Executive Sep Pay Information' ,'Separation Pay') exec_sep_pay ,pay_kr_report_pkg.get_dbitem_value (paa.assignment_action_id ,'SEPARATION_PAY_TAX_DEFERRING_DETAILS_TRANS_SEP_PEN_ACCT_NUM_ENTRY_VALUE') trans_account_number ,decode (pay_kr_report_pkg.get_result_value_char (paa.assignment_action_id ,ppa.business_group_id ,'Separation Pay Tax Deferring Details' ,'Transfer Financial Institute') ,NULL ,NULL ,hr_general.decode_lookup ('PAY_KR_PENSION_FINANCIAL_INST' ,pay_kr_report_pkg.get_result_value_char (paa.assignment_action_id ,ppa.business_group_id ,'Separation Pay Tax Deferring Details' ,'Transfer Financial Institute'))) trans_fin_inst FROM pay_payroll_actions ppa ,pay_assignment_actions paa WHERE NOT EXISTS ( SELECT NULL FROM pay_run_types_f prt ,pay_payroll_actions sppa ,pay_assignment_actions spaa ,pay_action_interlocks pai WHERE pai.locking_action_id = paa.assignment_action_id AND spaa.assignment_action_id = pai.locked_action_id AND sppa.payroll_action_id = spaa.payroll_action_id AND prt.run_type_id = spaa.run_type_id AND sppa.effective_date BETWEEN prt.effective_start_date AND prt.effective_end_date AND prt.run_type_name IN ('SEP','SEP_I') ) AND ppa.payroll_action_id = paa.payroll_action_id

View Text - HTML Formatted

SELECT PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID ASSIGNMENT_ID
, PPA.EFFECTIVE_DATE EFFECTIVE_DATE
, PPA.DATE_EARNED DATE_EARNED
, PAA.RUN_TYPE_ID RUN_TYPE_ID
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE (PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'WKPD'
, 'H_DATE') HIRING_DATE
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE (PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'WKPD'
, 'L_DATE') LEAVING_DATE
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_NUMBER (PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'WKPD'
, 'WKPD') WORKING_PERIOD
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'WKPD_SEP_TAX'
, '_ASG_RUN') WKPD_SEP_TAX
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE (PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'WKPD'
, 'PREV_FH_DATE') PREV_FIRST_HIRING_DATE
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE (PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'WKPD'
, 'PREV_LL_DATE') PREV_LAST_LEAVING_DATE
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE (PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'WKPD_NON_STAT_SEP_PAY'
, 'H_DATE') NS_HIRING_DATE
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE (PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'WKPD_NON_STAT_SEP_PAY'
, 'L_DATE') NS_LEAVING_DATE
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE (PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'WKPD_NON_STAT_SEP_PAY'
, 'PREV_FH_DATE') NS_PREV_FIRST_HIRING_DATE
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE (PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'WKPD_NON_STAT_SEP_PAY'
, 'PREV_LL_DATE') NS_PREV_LAST_LEAVING_DATE
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_WKPD_SEP_TAX'
, '_ASG_RUN') PREV_WKPD_SEP_TAX
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'OVL_WKPD'
, '_ASG_RUN') OVERLAP_WKPD
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'SVPD'
, '_ASG_RUN') SERVICE_PERIOD
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'SEP_PAY'
, '_ASG_RUN') SEP_PAY
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'SEP_INS'
, '_ASG_RUN') SEP_INS
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'SP_SEP_ALW'
, '_ASG_RUN') SP_SEP_ALW
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'TOTAL_TAXABLE_EARNINGS'
, '_ASG_RUN') TOTAL_TAXABLE_EARNINGS
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'TAXABLE_EARNINGS_WI_PREV'
, '_ASG_RUN') TAXABLE_EARNINGS_WI_PREV
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'INCOME_EXEM'
, '_ASG_RUN') INCOME_EXEM
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'TAXATION_BASE'
, '_ASG_RUN') TAXATION_BASE
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'YTAXATION_BASE'
, '_ASG_RUN') YTAXATION_BASE
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'YCALC_TAX'
, '_ASG_RUN') YCALC_TAX
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'CALC_TAX'
, '_ASG_RUN') CALC_TAX
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'TAX_BREAK'
, '_ASG_RUN') TAX_BREAK
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'ACTUAL_TAX'
, '_ASG_RUN') ACTUAL_TAX
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'GROSS_ITAX'
, '_ASG_RUN') GROSS_ITAX
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'GROSS_RTAX'
, '_ASG_RUN') GROSS_RTAX
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'GROSS_STAX'
, '_ASG_RUN') GROSS_STAX
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'ITAX'
, '_ASG_RUN') ITAX
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'RTAX'
, '_ASG_RUN') RTAX
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'STAX'
, '_ASG_RUN') STAX
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'NON_STAT_SEP_PAY_TAXABLE_EARNINGS'
, '_ASG_RUN') NS_SEP_PAY_AMT
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'NON_STAT_SEP_PAY_WKPD_MONTH'
, '_ASG_RUN') NS_WKPD_SEP_TAX
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'NON_STAT_SEP_PAY_PREV_WKPD_MONTH'
, '_ASG_RUN') NS_PREV_WKPD_SEP_TAX
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'NON_STAT_SEP_PAY_OVL_WKPD_MONTHS'
, '_ASG_RUN') NS_OVERLAP_WKPD
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'NON_STAT_SEP_PAY_SEP_PAY_INCOME_EXEM'
, '_ASG_RUN') NS_SEP_PAY_INCOME_EXEM
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'NON_STAT_SEP_PAY_SVPD_INCOME_EXEM'
, '_ASG_RUN') NS_SVPD_INCOME_EXEM
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'NON_STAT_SEP_PAY_TAXATION_BASE'
, '_ASG_RUN') NS_TAXATION_BASE
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'NON_STAT_SEP_PAY_WKPD_YEAR'
, '_ASG_RUN') NS_SERVICE_PERIOD
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'NON_STAT_SEP_PAY_YTAXATION_BASE'
, '_ASG_RUN') NS_YTAXATION_BASE
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'NON_STAT_SEP_PAY_YCALC_TAX'
, '_ASG_RUN') NS_YCALC_TAX
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'NON_STAT_SEP_PAY_CALC_TAX'
, '_ASG_RUN') NS_CALC_TAX
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'NON_STAT_SEP_PAY_TAX_BREAK'
, '_ASG_RUN') NS_TAX_BREAK
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'NON_STAT_SEP_PAY_ACTUAL_TAX'
, '_ASG_RUN') NS_ACTUAL_TAX
, PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_ER_INFO_SEP_PAY_ENTRY_VALUE') PREV_SEP_PAY
, PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_ER_INFO_SEP_INS_ENTRY_VALUE') PREV_SEP_INS
, PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_ER_INFO_SP_SEP_ALW_ENTRY_VALUE') PREV_SP_SEP_ALW
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'EXCLUDED MONTH FOR NON STATUTORY SEPARATION PAY'
, '_ASG_RUN') NS_EXCLUDED_MTH
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'SEPARATION PENSION LUMP SUM AMOUNT'
, '_ASG_RUN') SEP_PEN_LUMP_SUM_AMT
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'RECEIVABLE SEPARATION PAY'
, '_ASG_RUN') RECEIVABLE_SEP_PAY
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'TOTAL LUMP SUM AMOUNT'
, '_ASG_RUN') TOTAL_LUMP_SUM_AMT
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'TOTAL RECEIVED'
, '_ASG_RUN') TOTAL_RECEIVED
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PRINCIPAL
AND INTEREST'
, '_ASG_RUN') PRINCIPAL_INTEREST
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PERSONAL CONTRIBUTION'
, '_ASG_RUN') PERSONAL_CONTRIBUTION
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PENSION EXEMPTION'
, '_ASG_RUN') PENSION_EXEM
, PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'SEPARATION_PENSION_ACCOUNT_DETAILS_DEFINED_TYPE_ENTRY_VALUE') PENSION_DEFINED_TYPES
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'AMOUNT EXPECTED'
, '_ASG_RUN') AMT_EXP
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'EXCLUDED MONTH'
, '_ASG_RUN') EXCLUDED_MTH
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'SEPARATION PENSION LUMP SUM NON TAXABLE AMOUNT'
, '_ASG_RUN') SEP_PEN_LUMP_SUM_NON_TAX_AMT
, PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_ER_INFO_NON_TAXABLE_EARNING_ENTRY_VALUE') PREV_NON_TAX_EAR
, NVL (PAY_KR_REPORT_PKG.GET_RESULT_VALUE_NUMBER (PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'SEP_PAY_OVERSEAS_TAX_BREAK'
, 'OVERSEAS_TAX_BREAK')
, 0) OVERSEAS_TAX_BREAK
, NVL (PAY_KR_REPORT_PKG.GET_RESULT_VALUE_NUMBER (PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'NON_STAT_SEP_OVERSEAS_TAX_BRK'
, 'OVERSEAS_TAX_BREAK')
, 0) NS_OVERSEAS_TAX_BREAK
, NVL (PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_SEP_PENS_DTLS_TOTAL_RECEIVED_ENTRY_VALUE')
, 0) TOTAL_RECEIVED_PREV
, NVL (PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_SEP_PENS_DTLS_PERS_CONTRIBUTION_ENTRY_VALUE')
, 0) PERSONAL_CONTRIBUTION_PREV
, NVL (PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_SEP_PENS_DTLS_PRINCIPAL_INTRST_ENTRY_VALUE')
, 0) PRINCIPAL_AND_INTEREST_PREV
, NVL (PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_SEP_PENS_DTLS_AMT_EXP_STAT_SEP_ENTRY_VALUE')
, 0) AMOUNT_EXPECTED_PREV
, NVL (PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_SEP_PENS_DTLS_AMT_EXP_NONSTAT_SEP_ENTRY_VALUE')
, 0) AMOUNT_EXP_NONSTAT_PREV
, NVL (PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_SEP_PENS_DTLS_PENS_EXEM_ENTRY_VALUE')
, 0) PENSION_EXEMPTION_PREV
, NVL (PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_SEP_PENS_DTLS_DEFERRED_AMOUNT_STAT_ENTRY_VALUE')
, 0) DEFERRED_AMT_STAT_PREV
, NVL (PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_SEP_PENS_DTLS_DEFERRED_AMOUNT_NONSTAT_ENTRY_VALUE')
, 0) DEFERRED_AMT_NONSTAT_PREV
, NVL (PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_SEP_PENS_DTLS_RECEIVED_AMOUNT_STAT_ENTRY_VALUE')
, 0) RECEIVED_AMT_STAT_PREV
, NVL (PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_SEP_PENS_DTLS_RECEIVED_AMOUNT_NONSTAT_ENTRY_VALUE')
, 0) RECEIVED_AMT_NONSTAT_PREV
, NVL (PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'SEPARATION_PENSION_ACCOUNT_DETAILS_ACCOUNT_NUMBER_ENTRY_VALUE')
, 0) SEP_PEN_ACCOUNT_NUMBER
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'DEFERRED_AMOUNT_STAT'
, '_ASG_RUN') DEFERRED_AMT_STAT
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'DEFERRED_AMOUNT_NONSTAT'
, '_ASG_RUN') DEFERRED_AMT_NST
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'RECEIVED_AMOUNT_STAT'
, '_ASG_RUN') RECEIVED_AMT_STAT
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'RECEIVED_AMOUNT_NONSTAT'
, '_ASG_RUN') RECEIVED_AMT_NST
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'AMOUNT_EXPECTED_NONSTAT'
, '_ASG_RUN') AMT_EXP_NST
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_EMP_SEP_PENS_LUMP_SUM_AMT'
, '_ASG_RUN') SEP_PEN_LUMP_SUM_AMOUNT_PREV
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'RECEIVABLE_NON_STAT_SEP_PAY'
, '_ASG_RUN') RECEIVABLE_NONSTAT_SEP_PAY
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'TOT_LUMP_SUM_AMT_NON_STAT_SEP_PAY'
, '_ASG_RUN') NS_TOTAL_LUMP_SUM_AMOUNT
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_EMP_SEP_PEN_LUMP_SUM_NON_TAX_AMT'
, '_ASG_RUN') PREV_SEP_PEN_NTAX_LSA
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_EMP_SEP_PENS_LUMP_SUM_AMT'
, '_ASG_RUN') PREV_SEP_PEN_LSA
, NVL (PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'SEP_PEN_LUMP_SUM_AMT_ENT'
, '_ASG_RUN')
, 0) SEP_PEN_LSA_STAT_ENT
, NVL (PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'SEP_PEN_LUMP_SUM_NST_ENT'
, '_ASG_RUN')
, 0) SEP_PEN_LSA_NST_ENT
, NVL (PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_SEP_PENS_DTLS_LUMP_SUM_AMT_STAT_ENTRY_VALUE')
, 0) SEP_PEN_LSA_STAT_ENT_PREV
, NVL (PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_SEP_PENS_DTLS_LUMP_SUM_AMT_NST_ENTRY_VALUE')
, 0) SEP_PEN_LSA_NST_ENT_PREV
, NVL (PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'SEP_PAY_POST_TAX_DEDUCTION'
, '_ASG_RUN')
, 0) POST_TAX_DEDUC
, NVL (PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'NON_STAT_SEP_PAY_POST_TAX_DEDUCTION'
, '_ASG_RUN')
, 0) POST_TAX_DEDUC_NS
, NVL (PAY_KR_REPORT_PKG.GET_RESULT_VALUE_CHAR (PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'SEP_PAY_POST_TAX_BRK'
, 'EXCEPTION_FLAG')
, 'N') EXCEPTION_FLAG
, NVL (PAY_KR_REPORT_PKG.GET_RESULT_VALUE_CHAR (PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'PREV_SEP_PENS_DTLS'
, 'ACCOUNT NUMBER')
, 0) SEP_PEN_ACCOUNT_NUMBER_PREV
, DECODE (NVL (PAY_KR_REPORT_PKG.GET_RESULT_VALUE_CHAR (PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'SEP_PAY_POST_TAX_BRK'
, 'EXCEPTION_FLAG')
, 'N')
, 'N'
, NVL (PAY_KR_REPORT_PKG.GET_RESULT_VALUE_CHAR (PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'SEP_PAY_POST_TAX_BRK'
, 'ELIGIBLE_POST_TAX_DEDUC_FLAG')
, 'Y')
, 'N') PTAX_ELIGIBILITY_FLAG
, NVL (PAY_KR_REPORT_PKG.GET_RESULT_VALUE_CHAR (PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'SEP_PAY_POST_TAX_BRK'
, 'REASON_FOR_SEP_PAY')
, '3') SEP_PAY_REASON
, DECODE (PAY_KR_REPORT_PKG.GET_RESULT_VALUE_CHAR (PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'SEPARATION PENSION ACCOUNT DETAILS'
, 'FINANCIAL INSTITUTE')
, NULL
, NULL
, HR_GENERAL.DECODE_LOOKUP ('PAY_KR_PENSION_FINANCIAL_INST'
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_CHAR (PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'SEPARATION PENSION ACCOUNT DETAILS'
, 'FINANCIAL INSTITUTE'))) SEP_PEN_ACCT_FIN_INST
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'TRANSFERRED AMOUNT FOR STAT SEP PAY'
, '_ASG_RUN') TRANS_AMT_STAT
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'TRANSFERRED AMOUNT FOR NONSTAT SEP PAY'
, '_ASG_RUN') TRANS_AMT_NST
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'DEFERRED TAX AMOUNT'
, '_ASG_RUN') DEFERRED_TAX_AMT
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'WORKING PERIOD ADDITIONAL MONTHS'
, '_ASG_RUN') WKPD_ADDTL_MTHS_STAT
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'WORKING PERIOD ADDITIONAL MONTHS FOR NONSTAT'
, '_ASG_RUN') WKPD_ADDTL_MTHS_NST
, TO_CHAR (PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE (PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'SEPARATION PAY TAX DEFERRING DETAILS'
, 'TRANSFERRED DATE')
, 'DD-MON-YYYY') TRANS_DATE
, TO_CHAR (PAY_KR_REPORT_PKG.GET_RESULT_VALUE_DATE (PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'SEPARATION PAY TAX DEFERRING DETAILS'
, 'MATURITY DATE')
, 'DD-MON-YYYY') MATURITY_DATE
, PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_ER_INFO2_ADDITIONAL_MONTHS_FOR_NONSTAT_ENTRY_VALUE') PREV_ADDTL_MTH_NST
, PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_ER_INFO2_ADDITIONAL_MONTHS_FOR_STAT_ENTRY_VALUE') PREV_ADDTL_MTH_STAT
, PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_ER_INFO2_OVERLAPPING_MONTHS_FOR_NONSTAT_ENTRY_VALUE') PREV_OVL_MTH_NST
, PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_ER_INFO2_OVERLAPPING_MONTHS_FOR_STAT_ENTRY_VALUE') PREV_OVL_MTH_STAT
, PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_ER_INFO2_WORKING_YEAR_FOR_NONSTAT_ENTRY_VALUE') PREV_WORK_YEAR_NST
, PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'PREV_ER_INFO2_WORKING_YEAR_FOR_STAT_ENTRY_VALUE') PREV_WORK_YEAR_STAT
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_CHAR (PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'EXECUTIVE SEP PAY INFORMATION'
, 'SEPARATION PAY') EXEC_SEP_PAY
, PAY_KR_REPORT_PKG.GET_DBITEM_VALUE (PAA.ASSIGNMENT_ACTION_ID
, 'SEPARATION_PAY_TAX_DEFERRING_DETAILS_TRANS_SEP_PEN_ACCT_NUM_ENTRY_VALUE') TRANS_ACCOUNT_NUMBER
, DECODE (PAY_KR_REPORT_PKG.GET_RESULT_VALUE_CHAR (PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'SEPARATION PAY TAX DEFERRING DETAILS'
, 'TRANSFER FINANCIAL INSTITUTE')
, NULL
, NULL
, HR_GENERAL.DECODE_LOOKUP ('PAY_KR_PENSION_FINANCIAL_INST'
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_CHAR (PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'SEPARATION PAY TAX DEFERRING DETAILS'
, 'TRANSFER FINANCIAL INSTITUTE'))) TRANS_FIN_INST
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
WHERE NOT EXISTS ( SELECT NULL
FROM PAY_RUN_TYPES_F PRT
, PAY_PAYROLL_ACTIONS SPPA
, PAY_ASSIGNMENT_ACTIONS SPAA
, PAY_ACTION_INTERLOCKS PAI
WHERE PAI.LOCKING_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND SPAA.ASSIGNMENT_ACTION_ID = PAI.LOCKED_ACTION_ID
AND SPPA.PAYROLL_ACTION_ID = SPAA.PAYROLL_ACTION_ID
AND PRT.RUN_TYPE_ID = SPAA.RUN_TYPE_ID
AND SPPA.EFFECTIVE_DATE BETWEEN PRT.EFFECTIVE_START_DATE
AND PRT.EFFECTIVE_END_DATE
AND PRT.RUN_TYPE_NAME IN ('SEP'
, 'SEP_I') )
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID