View: PAY_KR_SEP_RESULT_BAL_V

Product:	PAY - Payroll
Description:	This view returns Balance of Separation Pay Process based on assignment_action_id.
Implementation/DBA Data:	APPS.PAY_KR_SEP_RESULT_BAL_V

View Text

SELECT /*+RULE*/ 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
, PRR.RUN_RESULT_ID RUN_RESULT_ID
, PRRV.INPUT_VALUE_ID INPUT_VALUE_ID
, PBF.BALANCE_FEED_ID BALANCE_FEED_ID
, PBF.EFFECTIVE_START_DATE FEFFECTIVE_START_DATE
, PBF.EFFECTIVE_END_DATE FEFFECTIVE_END_DATE
, PBT.BALANCE_TYPE_ID BALANCE_TYPE_ID
, PBT.BALANCE_NAME BALANCE_NAME
, PBTTL.BALANCE_NAME UBALANCE_NAME
, PBT.BALANCE_UOM BALANCE_UOM
, HR_GENERAL.DECODE_LOOKUP('UNITS'
, PBT.BALANCE_UOM) UBALANCE_UOM
FROM PAY_BALANCE_TYPES_TL PBTTL
, PAY_BALANCE_TYPES PBT
, PAY_BALANCE_FEEDS_F PBF
, PAY_RUN_RESULT_VALUES PRRV
, PAY_RUN_RESULTS PRR
, 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 LIKE 'SEP%')
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PRRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PBF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PPA.EFFECTIVE_DATE BETWEEN PBF.EFFECTIVE_START_DATE
AND PBF.EFFECTIVE_END_DATE
AND PBT.BALANCE_TYPE_ID = PBF.BALANCE_TYPE_ID
AND EXISTS (SELECT PBF2.BALANCE_FEED_ID
FROM PAY_BALANCE_FEEDS_F PBF2
WHERE PBF2.BALANCE_TYPE_ID = PBF.BALANCE_TYPE_ID
AND PBF.BALANCE_FEED_ID = (SELECT MAX(PBF3.BALANCE_FEED_ID)
FROM PAY_BALANCE_FEEDS_F PBF3
, PAY_RUN_RESULT_VALUES PRRV2
, PAY_RUN_RESULTS PRR2
WHERE PBF3.BALANCE_TYPE_ID = PBF.BALANCE_TYPE_ID
AND PBF3.INPUT_VALUE_ID = PRRV2.INPUT_VALUE_ID
AND PRRV2.RUN_RESULT_ID = PRR2.RUN_RESULT_ID
AND PRR2.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID GROUP BY PBF3.BALANCE_TYPE_ID ) )
AND PBTTL.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND PBTTL.LANGUAGE = USERENV('LANG')
AND NVL(PBT.BUSINESS_GROUP_ID
, PPA.BUSINESS_GROUP_ID) = PPA.BUSINESS_GROUP_ID
AND NVL(PBT.LEGISLATION_CODE
, 'KR') = 'KR'

Columns