VIEW: APPS.PAY_HK_BALANCES_BY_DATE_V2

Source

View Text - Preformatted

SELECT paf.rowid row_id, ppa.payroll_id, paa.assignment_id, paa.action_sequence, pbt.balance_type_id, paa.assignment_action_id, pbttl.balance_name, pdb.defined_balance_id, pbd.database_item_suffix, paa.tax_unit_id, null source_id, pay_hk_exc.calculated_value(pdb.defined_balance_id ,paa.assignment_action_id ,paa.tax_unit_id ,null ,ppa.effective_date) value, ppa.effective_date, pay_hk_exc.get_expiry_date(pdb.defined_balance_id ,paa.assignment_action_id) expiry_date from per_assignments_f paf, pay_payroll_actions ppa, pay_balance_types pbt, pay_balance_types_tl pbttl, pay_balance_dimensions pbd, pay_defined_balances pdb, pay_assignment_actions paa WHERE ((pbt.legislation_code is null and pbt.business_group_id = ppa.business_group_id) or pbt.legislation_code = 'HK') and paf.assignment_id = paa.assignment_id and pbt.balance_type_id = pbttl.balance_type_id and pbttl.language = userenv('LANG') and ppa.effective_date between paf.effective_start_date and paf.effective_end_date and paa.payroll_action_id = ppa.payroll_action_id and pdb.balance_type_id = pbt.balance_type_id and pdb.balance_dimension_id = pbd.balance_dimension_id and not exists (select '' from ff_route_context_usages frcu, ff_contexts fc where frcu.route_id = pbd.route_id and fc.context_id = frcu.context_id and fc.context_name not in ('ASSIGNMENT_ACTION_ID','TAX_UNIT_ID')) and exists (select '' from pay_balance_feeds_f pbf, pay_run_result_values prrv, pay_run_results prr where prr.assignment_action_id = paa.assignment_action_id and prr.run_result_id = prrv.run_result_id and prrv.input_value_id = pbf.input_value_id and pbf.balance_type_id = pbt.balance_type_id and ppa.effective_date between pbf.effective_start_date and pbf.effective_end_date) union select paf.rowid, ppa.payroll_id, paa.assignment_id, paa.action_sequence, pbt.balance_type_id, paa.assignment_action_id, pbttl.balance_name, pdb.defined_balance_id, pbd.database_item_suffix, paa.tax_unit_id, pac.context_value, pay_hk_exc.calculated_value(pdb.defined_balance_id ,paa.assignment_action_id ,paa.tax_unit_id ,pac.context_value ,ppa.effective_date), ppa.effective_date, pay_hk_exc.get_expiry_date(pdb.defined_balance_id ,paa.assignment_action_id) from per_assignments_f paf, pay_payroll_actions ppa, pay_balance_types pbt, pay_balance_types_tl pbttl, pay_balance_dimensions pbd, pay_defined_balances pdb, pay_assignment_actions paa, pay_action_contexts pac, ff_contexts ffc where ((pbt.legislation_code is null and pbt.business_group_id = ppa.business_group_id) or pbt.legislation_code = 'HK') and paf.assignment_id = paa.assignment_id and pbt.balance_type_id = pbttl.balance_type_id and pbttl.language = userenv('LANG') and ppa.effective_date between paf.effective_start_date and paf.effective_end_date and paa.payroll_action_id = ppa.payroll_action_id and pdb.balance_type_id = pbt.balance_type_id and pdb.balance_dimension_id = pbd.balance_dimension_id and paa.assignment_action_id = pac.assignment_action_id(+) and ffc.context_id = nvl(pac.context_id,ffc.context_id) and ffc.context_name = 'SOURCE_ID' and exists (select '' from ff_route_context_usages frcu, ff_contexts fc where frcu.route_id = pbd.route_id and fc.context_id = frcu.context_id and fc.context_name in ('SOURCE_ID')) and exists (select '' from pay_balance_feeds_f pbf, pay_run_result_values prrv, pay_run_results prr where prr.assignment_action_id = paa.assignment_action_id and prr.run_result_id = prrv.run_result_id and prrv.input_value_id = pbf.input_value_id and pbf.balance_type_id = pbt.balance_type_id and ppa.effective_date between pbf.effective_start_date and pbf.effective_end_date)

View Text - HTML Formatted

SELECT PAF.ROWID ROW_ID
, PPA.PAYROLL_ID
, PAA.ASSIGNMENT_ID
, PAA.ACTION_SEQUENCE
, PBT.BALANCE_TYPE_ID
, PAA.ASSIGNMENT_ACTION_ID
, PBTTL.BALANCE_NAME
, PDB.DEFINED_BALANCE_ID
, PBD.DATABASE_ITEM_SUFFIX
, PAA.TAX_UNIT_ID
, NULL SOURCE_ID
, PAY_HK_EXC.CALCULATED_VALUE(PDB.DEFINED_BALANCE_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.TAX_UNIT_ID
, NULL
, PPA.EFFECTIVE_DATE) VALUE
, PPA.EFFECTIVE_DATE
, PAY_HK_EXC.GET_EXPIRY_DATE(PDB.DEFINED_BALANCE_ID
, PAA.ASSIGNMENT_ACTION_ID) EXPIRY_DATE
FROM PER_ASSIGNMENTS_F PAF
, PAY_PAYROLL_ACTIONS PPA
, PAY_BALANCE_TYPES PBT
, PAY_BALANCE_TYPES_TL PBTTL
, PAY_BALANCE_DIMENSIONS PBD
, PAY_DEFINED_BALANCES PDB
, PAY_ASSIGNMENT_ACTIONS PAA
WHERE ((PBT.LEGISLATION_CODE IS NULL
AND PBT.BUSINESS_GROUP_ID = PPA.BUSINESS_GROUP_ID) OR PBT.LEGISLATION_CODE = 'HK')
AND PAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PBT.BALANCE_TYPE_ID = PBTTL.BALANCE_TYPE_ID
AND PBTTL.LANGUAGE = USERENV('LANG')
AND PPA.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PDB.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND PDB.BALANCE_DIMENSION_ID = PBD.BALANCE_DIMENSION_ID
AND NOT EXISTS (SELECT ''
FROM FF_ROUTE_CONTEXT_USAGES FRCU
, FF_CONTEXTS FC
WHERE FRCU.ROUTE_ID = PBD.ROUTE_ID
AND FC.CONTEXT_ID = FRCU.CONTEXT_ID
AND FC.CONTEXT_NAME NOT IN ('ASSIGNMENT_ACTION_ID'
, 'TAX_UNIT_ID'))
AND EXISTS (SELECT ''
FROM PAY_BALANCE_FEEDS_F PBF
, PAY_RUN_RESULT_VALUES PRRV
, PAY_RUN_RESULTS PRR
WHERE PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PRRV.INPUT_VALUE_ID = PBF.INPUT_VALUE_ID
AND PBF.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND PPA.EFFECTIVE_DATE BETWEEN PBF.EFFECTIVE_START_DATE
AND PBF.EFFECTIVE_END_DATE) UNION SELECT PAF.ROWID
, PPA.PAYROLL_ID
, PAA.ASSIGNMENT_ID
, PAA.ACTION_SEQUENCE
, PBT.BALANCE_TYPE_ID
, PAA.ASSIGNMENT_ACTION_ID
, PBTTL.BALANCE_NAME
, PDB.DEFINED_BALANCE_ID
, PBD.DATABASE_ITEM_SUFFIX
, PAA.TAX_UNIT_ID
, PAC.CONTEXT_VALUE
, PAY_HK_EXC.CALCULATED_VALUE(PDB.DEFINED_BALANCE_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.TAX_UNIT_ID
, PAC.CONTEXT_VALUE
, PPA.EFFECTIVE_DATE)
, PPA.EFFECTIVE_DATE
, PAY_HK_EXC.GET_EXPIRY_DATE(PDB.DEFINED_BALANCE_ID
, PAA.ASSIGNMENT_ACTION_ID)
FROM PER_ASSIGNMENTS_F PAF
, PAY_PAYROLL_ACTIONS PPA
, PAY_BALANCE_TYPES PBT
, PAY_BALANCE_TYPES_TL PBTTL
, PAY_BALANCE_DIMENSIONS PBD
, PAY_DEFINED_BALANCES PDB
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_ACTION_CONTEXTS PAC
, FF_CONTEXTS FFC
WHERE ((PBT.LEGISLATION_CODE IS NULL
AND PBT.BUSINESS_GROUP_ID = PPA.BUSINESS_GROUP_ID) OR PBT.LEGISLATION_CODE = 'HK')
AND PAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PBT.BALANCE_TYPE_ID = PBTTL.BALANCE_TYPE_ID
AND PBTTL.LANGUAGE = USERENV('LANG')
AND PPA.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PDB.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND PDB.BALANCE_DIMENSION_ID = PBD.BALANCE_DIMENSION_ID
AND PAA.ASSIGNMENT_ACTION_ID = PAC.ASSIGNMENT_ACTION_ID(+)
AND FFC.CONTEXT_ID = NVL(PAC.CONTEXT_ID
, FFC.CONTEXT_ID)
AND FFC.CONTEXT_NAME = 'SOURCE_ID'
AND EXISTS (SELECT ''
FROM FF_ROUTE_CONTEXT_USAGES FRCU
, FF_CONTEXTS FC
WHERE FRCU.ROUTE_ID = PBD.ROUTE_ID
AND FC.CONTEXT_ID = FRCU.CONTEXT_ID
AND FC.CONTEXT_NAME IN ('SOURCE_ID'))
AND EXISTS (SELECT ''
FROM PAY_BALANCE_FEEDS_F PBF
, PAY_RUN_RESULT_VALUES PRRV
, PAY_RUN_RESULTS PRR
WHERE PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PRRV.INPUT_VALUE_ID = PBF.INPUT_VALUE_ID
AND PBF.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND PPA.EFFECTIVE_DATE BETWEEN PBF.EFFECTIVE_START_DATE
AND PBF.EFFECTIVE_END_DATE)