VIEW: APPS.PAY_SG_SOE_BALANCES_V

Source

View Text - Preformatted

SELECT paa.assignment_action_id, paa.action_sequence, pbttl.balance_name, pbt.balance_type_id, paa.tax_unit_id, paa.assignment_id, sum(decode( pbd.database_item_suffix ,'_ASG_LE_YTD',pay_balance_pkg.get_value(pdb.defined_balance_id , paa.assignment_action_id ,paa.tax_unit_id ,null ,null ,null ,null ),0)) ASG_LE_YTD, sum(decode( pbd.database_item_suffix ,'_PER_LE_YTD',pay_balance_pkg.get_value(pdb.defined_balance_id , paa.assignment_action_id ,paa.tax_unit_id ,null ,null ,null ,null ),0)) PER_LE_YTD, sum(decode( pbd.database_item_suffix ,'_PAYMENTS',pay_balance_pkg.get_value(pdb.defined_balance_id , paa.assignment_action_id ),0)) PAYMENTS, sum(decode( pbd.database_item_suffix ,'_ASG_LE_RUN',pay_balance_pkg.get_value(pdb.defined_balance_id , paa.assignment_action_id ,paa.tax_unit_id ,null ,null ,null ,null ),0)) ASG_LE_RUN from pay_balance_types pbt, pay_Balance_types_tl pbttl, pay_balance_dimensions pbd, pay_defined_balances pdb, pay_assignment_actions paa, pay_payroll_actions ppa WHERE ((pbt.legislation_code is null and pbt.business_group_id = ppa.business_group_id) or pbt.legislation_code = 'SG') and pbt.balance_type_id = pbttl.balance_type_id and pbttl.language = userenv('LANG') and pdb.balance_type_id = pbt.balance_type_id and pdb.balance_dimension_id = pbd.balance_dimension_id and paa.payroll_action_id = ppa.payroll_action_id and pbd.database_item_suffix in ('_ASG_LE_YTD','_PER_LE_YTD','_PAYMENTS','_ASG_LE_RUN') and exists ( select ' ' from pay_assignment_actions paa2, pay_balance_feeds_f feed, pay_run_result_values prrv, pay_run_results prr where pbt.balance_type_id = feed.balance_type_id and prr.run_result_id = prrv.run_result_id and feed.input_value_id = prrv.input_value_id and prrv.result_value is not null and prrv.result_value <> '0' and paa2.action_sequence <= paa.action_sequence and paa2.assignment_action_id = prr.assignment_action_id and paa2.assignment_id = paa.assignment_id ) group by paa.assignment_action_id, paa.action_sequence, pbttl.balance_name, pbt.balance_type_id, paa.tax_unit_id, paa.assignment_id

View Text - HTML Formatted

SELECT PAA.ASSIGNMENT_ACTION_ID
, PAA.ACTION_SEQUENCE
, PBTTL.BALANCE_NAME
, PBT.BALANCE_TYPE_ID
, PAA.TAX_UNIT_ID
, PAA.ASSIGNMENT_ID
, SUM(DECODE( PBD.DATABASE_ITEM_SUFFIX
, '_ASG_LE_YTD'
, PAY_BALANCE_PKG.GET_VALUE(PDB.DEFINED_BALANCE_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.TAX_UNIT_ID
, NULL
, NULL
, NULL
, NULL )
, 0)) ASG_LE_YTD
, SUM(DECODE( PBD.DATABASE_ITEM_SUFFIX
, '_PER_LE_YTD'
, PAY_BALANCE_PKG.GET_VALUE(PDB.DEFINED_BALANCE_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.TAX_UNIT_ID
, NULL
, NULL
, NULL
, NULL )
, 0)) PER_LE_YTD
, SUM(DECODE( PBD.DATABASE_ITEM_SUFFIX
, '_PAYMENTS'
, PAY_BALANCE_PKG.GET_VALUE(PDB.DEFINED_BALANCE_ID
, PAA.ASSIGNMENT_ACTION_ID )
, 0)) PAYMENTS
, SUM(DECODE( PBD.DATABASE_ITEM_SUFFIX
, '_ASG_LE_RUN'
, PAY_BALANCE_PKG.GET_VALUE(PDB.DEFINED_BALANCE_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.TAX_UNIT_ID
, NULL
, NULL
, NULL
, NULL )
, 0)) ASG_LE_RUN
FROM PAY_BALANCE_TYPES PBT
, PAY_BALANCE_TYPES_TL PBTTL
, PAY_BALANCE_DIMENSIONS PBD
, PAY_DEFINED_BALANCES PDB
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE ((PBT.LEGISLATION_CODE IS NULL
AND PBT.BUSINESS_GROUP_ID = PPA.BUSINESS_GROUP_ID) OR PBT.LEGISLATION_CODE = 'SG')
AND PBT.BALANCE_TYPE_ID = PBTTL.BALANCE_TYPE_ID
AND PBTTL.LANGUAGE = USERENV('LANG')
AND PDB.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND PDB.BALANCE_DIMENSION_ID = PBD.BALANCE_DIMENSION_ID
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PBD.DATABASE_ITEM_SUFFIX IN ('_ASG_LE_YTD'
, '_PER_LE_YTD'
, '_PAYMENTS'
, '_ASG_LE_RUN')
AND EXISTS ( SELECT ' '
FROM PAY_ASSIGNMENT_ACTIONS PAA2
, PAY_BALANCE_FEEDS_F FEED
, PAY_RUN_RESULT_VALUES PRRV
, PAY_RUN_RESULTS PRR
WHERE PBT.BALANCE_TYPE_ID = FEED.BALANCE_TYPE_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND FEED.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PRRV.RESULT_VALUE IS NOT NULL
AND PRRV.RESULT_VALUE <> '0'
AND PAA2.ACTION_SEQUENCE <= PAA.ACTION_SEQUENCE
AND PAA2.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PAA2.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID ) GROUP BY PAA.ASSIGNMENT_ACTION_ID
, PAA.ACTION_SEQUENCE
, PBTTL.BALANCE_NAME
, PBT.BALANCE_TYPE_ID
, PAA.TAX_UNIT_ID
, PAA.ASSIGNMENT_ID