View: PAY_KR_SEP_RESULT_TAX_V

Product:	PAY - Payroll
Description:	This view returns Result of Separation Pay Tax based on assignment_action_id.
Implementation/DBA Data:	APPS.PAY_KR_SEP_RESULT_TAX_V

View Text

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

Columns