VIEW: APPS.PAY_KR_SEP_RESULT_BAL_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'