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, decode(sign(pay_kr_report_pkg.get_balance_value(paa.assignment_action_id, 'TAXABLE_EARNINGS_WI_PREV', '_ASG_RUN')- pay_kr_report_pkg.get_balance_value(paa.assignment_action_id, 'INCOME_EXEM', '_ASG_RUN')), -1,pay_kr_report_pkg.get_balance_value(paa.assignment_action_id, 'TAXABLE_EARNINGS_WI_PREV', '_ASG_RUN'), 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_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, pay_kr_report_pkg.get_result_value_number(paa.assignment_action_id, ppa.business_group_id,'SEP_PAY_OVERSEAS_TAX_BREAK','OVERSEAS_TAX_BREAK') overseas_tax_break, 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') ns_overseas_tax_break 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
, DECODE(SIGN(PAY_KR_REPORT_PKG.GET_BALANCE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'TAXABLE_EARNINGS_WI_PREV'
, '_ASG_RUN')- PAY_KR_REPORT_PKG.GET_BALANCE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'INCOME_EXEM'
, '_ASG_RUN'))
, -1
, PAY_KR_REPORT_PKG.GET_BALANCE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'TAXABLE_EARNINGS_WI_PREV'
, '_ASG_RUN')
, 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_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
, PAY_KR_REPORT_PKG.GET_RESULT_VALUE_NUMBER(PAA.ASSIGNMENT_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, 'SEP_PAY_OVERSEAS_TAX_BREAK'
, 'OVERSEAS_TAX_BREAK') OVERSEAS_TAX_BREAK
, 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') NS_OVERSEAS_TAX_BREAK
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