eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PAY_BALANCES_V

Source

View Text - Preformatted


SELECT rrs.assignment_action_id , (bttl.balance_name || decode(ff_dbi_utils_pkg.translation_supported(pbg.legislation_code), 'Y', bmtl.database_item_suffix, bm.database_item_suffix)) balance_name_and_suffix , bd.defined_balance_id , bt.balance_type_id , bm.balance_dimension_id , pay_payroll_actions_pkg.latest_balance_exists (rrs.assignment_action_id , bd.defined_balance_id) latest_balance_exist , bt.balance_uom c_balance_uom , hr_general.decode_lookup('UNITS',bt.balance_uom) d_balance_uom , bt.currency_code d_currency_code , bd.business_group_id + 0 business_group_id , bd.legislation_code , min(baf.effective_start_date) effective_start_date , max(baf.effective_end_date) effective_end_date from pay_assignment_actions paa ,pay_payroll_actions ppa ,pay_run_results rrs ,pay_run_result_values rrv ,pay_balance_feeds_f baf ,pay_balance_types_tl bttl ,pay_balance_types bt ,pay_defined_balances bd ,pay_balance_dimensions bm ,pay_balance_dimensions_tl bmtl ,per_business_groups_perf pbg WHERE ppa.payroll_action_id = paa.payroll_action_id and pbg.business_group_id = ppa.business_group_id and rrs.assignment_action_id = paa.assignment_action_id and bt.balance_type_id = bttl.balance_type_id and bttl.language = userenv('LANG') and rrs.run_result_id = rrv.run_result_id and rrv.input_value_id = baf.input_value_id and ppa.effective_date between baf.effective_start_date and baf.effective_end_date and baf.balance_type_id = bt.balance_type_id and bt.balance_type_id = bd.balance_type_id and bd.balance_dimension_id = bm.balance_dimension_id and bm.balance_dimension_id = bmtl.balance_dimension_id and bmtl.language = userenv('LANG') and ((not exists (select 1 from ff_route_context_usages rcu , ff_contexts con where rcu.route_id = bm.route_id and rcu.context_id = con.context_id and con.context_name not in ('ASSIGNMENT_ACTION_ID' ,'TAX_UNIT_ID') ) ) OR (exists (select 'Y' from pay_legislative_field_info lfi where lfi.validation_type = 'BLOCK_PROPERTY' and lfi.validation_name = 'DISPLAY' and lfi.rule_type = 'BALANCE_BREAKDOWN' and lfi.rule_mode = 'Y' and lfi.field_name = 'CONTEXT_VALUE' and lfi.target_location = 'PAYWSACT' and lfi.legislation_code = pbg.legislation_code) AND not exists (select 1 from ff_route_context_usages rcu , ff_contexts con where rcu.route_id = bm.route_id and rcu.context_id = con.context_id and (con.context_name not in ('ASSIGNMENT_ACTION_ID' ,'TAX_UNIT_ID' ,'JURISDICTION_CODE' ,'TAX_GROUP' ,'SOURCE_ID' ,'ORIGINAL_ENTRY_ID' ,'SOURCE_TEXT' ,'TIME_DEFINITION_ID' , 'BALANCE_DATE' , 'SOURCE_TEXT2' ) or (con.context_name in ('JURISDICTION_CODE' ,'TAX_GROUP' ,'SOURCE_ID' ,'ORIGINAL_ENTRY_ID' ,'SOURCE_TEXT' ,'TIME_DEFINITION_ID' , 'BALANCE_DATE' , 'SOURCE_TEXT2' ) and not exists (select 1 from pay_action_contexts pac where pac.assignment_action_id = rrs.assignment_action_id and pac.context_id = rcu.context_id) ) ) ) ) ) group by rrs.assignment_action_id ,(bttl.balance_name || decode(ff_dbi_utils_pkg.translation_supported(pbg.legislation_code), 'Y', bmtl.database_item_suffix, bm.database_item_suffix)) , bd.defined_balance_id , bt.balance_type_id , bm.balance_dimension_id , pay_payroll_actions_pkg.latest_balance_exists (rrs.assignment_action_id, bd.defined_balance_id) , bt.balance_uom , hr_general.decode_lookup('UNITS',bt.balance_uom) , bt.currency_code , bd.business_group_id , bd.legislation_code

View Text - HTML Formatted

SELECT RRS.ASSIGNMENT_ACTION_ID
, (BTTL.BALANCE_NAME || DECODE(FF_DBI_UTILS_PKG.TRANSLATION_SUPPORTED(PBG.LEGISLATION_CODE)
, 'Y'
, BMTL.DATABASE_ITEM_SUFFIX
, BM.DATABASE_ITEM_SUFFIX)) BALANCE_NAME_AND_SUFFIX
, BD.DEFINED_BALANCE_ID
, BT.BALANCE_TYPE_ID
, BM.BALANCE_DIMENSION_ID
, PAY_PAYROLL_ACTIONS_PKG.LATEST_BALANCE_EXISTS (RRS.ASSIGNMENT_ACTION_ID
, BD.DEFINED_BALANCE_ID) LATEST_BALANCE_EXIST
, BT.BALANCE_UOM C_BALANCE_UOM
, HR_GENERAL.DECODE_LOOKUP('UNITS'
, BT.BALANCE_UOM) D_BALANCE_UOM
, BT.CURRENCY_CODE D_CURRENCY_CODE
, BD.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID
, BD.LEGISLATION_CODE
, MIN(BAF.EFFECTIVE_START_DATE) EFFECTIVE_START_DATE
, MAX(BAF.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_RUN_RESULTS RRS
, PAY_RUN_RESULT_VALUES RRV
, PAY_BALANCE_FEEDS_F BAF
, PAY_BALANCE_TYPES_TL BTTL
, PAY_BALANCE_TYPES BT
, PAY_DEFINED_BALANCES BD
, PAY_BALANCE_DIMENSIONS BM
, PAY_BALANCE_DIMENSIONS_TL BMTL
, PER_BUSINESS_GROUPS_PERF PBG
WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PBG.BUSINESS_GROUP_ID = PPA.BUSINESS_GROUP_ID
AND RRS.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND BT.BALANCE_TYPE_ID = BTTL.BALANCE_TYPE_ID
AND BTTL.LANGUAGE = USERENV('LANG')
AND RRS.RUN_RESULT_ID = RRV.RUN_RESULT_ID
AND RRV.INPUT_VALUE_ID = BAF.INPUT_VALUE_ID
AND PPA.EFFECTIVE_DATE BETWEEN BAF.EFFECTIVE_START_DATE
AND BAF.EFFECTIVE_END_DATE
AND BAF.BALANCE_TYPE_ID = BT.BALANCE_TYPE_ID
AND BT.BALANCE_TYPE_ID = BD.BALANCE_TYPE_ID
AND BD.BALANCE_DIMENSION_ID = BM.BALANCE_DIMENSION_ID
AND BM.BALANCE_DIMENSION_ID = BMTL.BALANCE_DIMENSION_ID
AND BMTL.LANGUAGE = USERENV('LANG')
AND ((NOT EXISTS (SELECT 1
FROM FF_ROUTE_CONTEXT_USAGES RCU
, FF_CONTEXTS CON
WHERE RCU.ROUTE_ID = BM.ROUTE_ID
AND RCU.CONTEXT_ID = CON.CONTEXT_ID
AND CON.CONTEXT_NAME NOT IN ('ASSIGNMENT_ACTION_ID'
, 'TAX_UNIT_ID') ) ) OR (EXISTS (SELECT 'Y'
FROM PAY_LEGISLATIVE_FIELD_INFO LFI
WHERE LFI.VALIDATION_TYPE = 'BLOCK_PROPERTY'
AND LFI.VALIDATION_NAME = 'DISPLAY'
AND LFI.RULE_TYPE = 'BALANCE_BREAKDOWN'
AND LFI.RULE_MODE = 'Y'
AND LFI.FIELD_NAME = 'CONTEXT_VALUE'
AND LFI.TARGET_LOCATION = 'PAYWSACT'
AND LFI.LEGISLATION_CODE = PBG.LEGISLATION_CODE)
AND NOT EXISTS (SELECT 1
FROM FF_ROUTE_CONTEXT_USAGES RCU
, FF_CONTEXTS CON
WHERE RCU.ROUTE_ID = BM.ROUTE_ID
AND RCU.CONTEXT_ID = CON.CONTEXT_ID
AND (CON.CONTEXT_NAME NOT IN ('ASSIGNMENT_ACTION_ID'
, 'TAX_UNIT_ID'
, 'JURISDICTION_CODE'
, 'TAX_GROUP'
, 'SOURCE_ID'
, 'ORIGINAL_ENTRY_ID'
, 'SOURCE_TEXT'
, 'TIME_DEFINITION_ID'
, 'BALANCE_DATE'
, 'SOURCE_TEXT2' ) OR (CON.CONTEXT_NAME IN ('JURISDICTION_CODE'
, 'TAX_GROUP'
, 'SOURCE_ID'
, 'ORIGINAL_ENTRY_ID'
, 'SOURCE_TEXT'
, 'TIME_DEFINITION_ID'
, 'BALANCE_DATE'
, 'SOURCE_TEXT2' )
AND NOT EXISTS (SELECT 1
FROM PAY_ACTION_CONTEXTS PAC
WHERE PAC.ASSIGNMENT_ACTION_ID = RRS.ASSIGNMENT_ACTION_ID
AND PAC.CONTEXT_ID = RCU.CONTEXT_ID) ) ) ) ) ) GROUP BY RRS.ASSIGNMENT_ACTION_ID
, (BTTL.BALANCE_NAME || DECODE(FF_DBI_UTILS_PKG.TRANSLATION_SUPPORTED(PBG.LEGISLATION_CODE)
, 'Y'
, BMTL.DATABASE_ITEM_SUFFIX
, BM.DATABASE_ITEM_SUFFIX))
, BD.DEFINED_BALANCE_ID
, BT.BALANCE_TYPE_ID
, BM.BALANCE_DIMENSION_ID
, PAY_PAYROLL_ACTIONS_PKG.LATEST_BALANCE_EXISTS (RRS.ASSIGNMENT_ACTION_ID
, BD.DEFINED_BALANCE_ID)
, BT.BALANCE_UOM
, HR_GENERAL.DECODE_LOOKUP('UNITS'
, BT.BALANCE_UOM)
, BT.CURRENCY_CODE
, BD.BUSINESS_GROUP_ID
, BD.LEGISLATION_CODE