1. Tables and Views for HCM
  2. PAY_PER_ACT_BAL_RSLT_VL

PAY_PER_ACT_BAL_RSLT_VL

Details

  • Schema: FUSION

  • Object owner: PAY

  • Object type: VIEW

Columns

Name

OBJECT_ACTION_ID

PAYROLL_REL_ACTION_ID

BASE_RUN_TYPE_NAME

BAL_GRP_USAGE_ID

GROUP_USAGE_NAME

BALANCE_TYPE_ID

BASE_BALANCE_NAME

BALANCE_NAME

BALANCE_RUN_VALUE

BALANCE_YEAR_VALUE

UOM

CURRENCY_CODE

UOM_CODE

FEED_CHECKING_TYPE

Query

SQL_Statement

SELECT

pra.source_action_id object_action_id,

NULL payroll_rel_action_id,

NULL base_run_type_name,

bgu1.bal_grp_usage_id,

bgu1.group_usage_name,

pbt.balance_type_id,

pbt.base_balance_name,

pbt.balance_name,

SUM(balv.balance_value) balance_run_value,

NULL balance_year_value,

NULL uom,

pbt.currency_code,

NULL uom_code,

NULL feed_checking_type

FROM

pay_payroll_actions ppa,

pay_payroll_rel_actions pra,

LATERAL

(

SELECT

bgu.BAL_GRP_USAGE_ID,

ldg.legislation_code

FROM

(

SELECT *

FROM HCM_EXTENDED_LOOKUP_CODES_VL

WHERE LOOKUP_TYPE = 'ORA_PAY_SOE_BAL_GRP_CONFIG'

) outerWh

UNPIVOT (

grp_name FOR col IN (

INFORMATION1,

INFORMATION2

)

) upv,

pay_bal_grp_usages_vl bgu,

per_legislative_data_groups ldg

WHERE bgu.BASE_GROUP_USAGE_NAME = upv.grp_name

AND ldg.legislative_data_group_id = ppa.legislative_data_group_id

AND DECODE(NVL(upv.INFORMATION6,'Y'),'N','N',NVL(upv.INFORMATION3,'Y')) = 'Y'

AND (

(upv.legislation_code IS NOT NULL

AND upv.legislation_code = ldg.legislation_code)

OR (upv.legislation_code IS NULL

AND NOT EXISTS (

SELECT 1

FROM HCM_EXTENDED_LOOKUP_CODES_VL innerWh

WHERE innerWh.EXTENDED_LOOKUP_CODE = upv.EXTENDED_LOOKUP_CODE

AND innerWh.legislation_code = ldg.legislation_code

)

)

)

) soe_grp,

pay_bal_grp_usages_vl bgu1,

per_legislative_data_groups ldg,

pay_balance_groups pbg,

pay_bal_grp_inclusions pbgi,

pay_bal_att_definitions pbad,

pay_balance_attributes pba,

pay_defined_balances pdb,

pay_balance_types_vl pbt,

pay_balance_dimensions pbd,

pay_run_balances balv

WHERE

pra.payroll_action_id = ppa.payroll_action_id

and pra.retro_component_id is null

AND bgu1.BAL_GRP_USAGE_ID = soe_grp.BAL_GRP_USAGE_ID

AND ppa.legislative_data_group_id = ldg.legislative_data_group_id

AND pbg.balance_group_id = bgu1.balance_group_id

AND ((pbg.legislation_code IS NULL AND pbg.legislative_data_group_id IS NULL)

OR (pbg.legislation_code = ldg.legislation_code AND pbg.legislative_data_group_id IS NULL)

OR pbg.legislative_data_group_id = ldg.legislative_data_group_id)

AND pbg.balance_group_id = pbgi.balance_group_id

AND ((pbgi.legislation_code IS NULL AND pbgi.legislative_data_group_id IS NULL)

OR (pbgi.legislation_code = ldg.legislation_code AND pbgi.legislative_data_group_id IS NULL)

OR pbgi.legislative_data_group_id = ldg.legislative_data_group_id)

AND pbgi.attribute_id = pbad.attribute_id

AND ((pbad.legislation_code IS NULL AND pbad.legislative_data_group_id IS NULL)

OR (pbad.legislation_code = ldg.legislation_code AND pbad.legislative_data_group_id IS NULL)

OR pbad.legislative_data_group_id = ldg.legislative_data_group_id)

AND pbad.attribute_id = pba.attribute_id

AND ((pba.legislation_code IS NULL AND pba.legislative_data_group_id IS NULL)

OR (pba.legislation_code = ldg.legislation_code AND pba.legislative_data_group_id IS NULL)

OR pba.legislative_data_group_id = ldg.legislative_data_group_id)

AND pdb.defined_balance_id = pba.defined_balance_id

AND ((pdb.legislation_code IS NULL AND pdb.legislative_data_group_id IS NULL)

OR (pdb.legislation_code = ldg.legislation_code AND pdb.legislative_data_group_id IS NULL)

OR pdb.legislative_data_group_id = ldg.legislative_data_group_id)

AND pdb.balance_type_id = pbt.balance_type_id

AND ((pbt.legislation_code IS NULL AND pbt.legislative_data_group_id IS NULL)

OR (pbt.legislation_code = ldg.legislation_code AND pbt.legislative_data_group_id IS NULL)

OR pbt.legislative_data_group_id = ldg.legislative_data_group_id)

AND pbd.balance_dimension_id = pdb.balance_dimension_id

AND ((pbd.legislation_code IS NULL AND pbd.legislative_data_group_id IS NULL)

OR (pbd.legislation_code = ldg.legislation_code AND pbd.legislative_data_group_id IS NULL)

OR pbd.legislative_data_group_id = ldg.legislative_data_group_id)

AND pbd.period_type = 'RUN'

AND pbd.dimension_level = 'REL'

AND pbd.BASE_DB_ITEM_SUFFIX = '_CORE_REL_RUN'

AND ppa.action_type IN ('R','Q','B','V')

AND pra.source_action_id IS NOT NULL

AND pra.action_status IN ('M','C')

AND balv.defined_balance_id(+) = pdb.defined_balance_id

AND balv.payroll_rel_action_id(+) = pra.payroll_rel_action_id

AND ppa.effective_date = balv.effective_date(+)

AND balv.balance_value IS NOT NULL

GROUP BY

pra.source_action_id,

bgu1.bal_grp_usage_id,

bgu1.group_usage_name,

pbt.balance_type_id,

pbt.base_balance_name,

pbt.balance_name,

pbt.currency_code

ORDER BY bgu1.group_usage_name