Compensation Derived Factors
SELECT bclf.name NAME_MDFC
,(SELECT NAME FROM FND_CURRENCIES_TL CURR WHERE CURR.CURRENCY_CODE = bclf.comp_lvl_uom AND LANGUAGE = 'US') COMP_LVL_UOM_MDFC
, hl2.meaning COMP_SRC_MDFC
, bbbf.NAME BNFTS_BAL_ID_MDFC
, hl4.meaning STTD_SAL_PRDCTY_CD_MDFC
, bclf.MX_COMP_VAL MX_COMP_VAL_MDFC
, bclf.MN_COMP_VAL MN_COMP_VAL_MDFC
, hl1.meaning COMP_LVL_DET_MDFC
, ff_comp_level.base_formula_name COMP_LVL_DET_RL_MDFC
, hl3.meaning RNDG_CD_MDFC
, ff_rndg.base_formula_name RNDG_RL_MDFC
, (SELECT FF.FORMULA_NAME FROM FF_FORMULAS_TL FF WHERE FF.FORMULA_ID = bclf.COMP_CALC_RL AND FF.LANGUAGE = 'US') COMP_FFNAME
,(SELECT BT.BALANCE_NAME || ' ' || BASE_DIMENSION_NAME FROM PAY_BALANCE_TYPES_TL BT, PAY_DEFINED_BALANCES DB, PAY_BALANCE_DIMENSIONS BD WHERE bclf.DEFINED_BALANCE_ID = DB.DEFINED_BALANCE_ID AND BT.BALANCE_TYPE_ID = DB.BALANCE_TYPE_ID AND BT.LANGUAGE = 'US' AND DB.BALANCE_DIMENSION_ID = BD.BALANCE_DIMENSION_ID) COMP_DBNAME
FROM BEN_COMP_LVL_FCTR bclf
, BEN_BNFTS_BAL_F bbbf
, hr_lookups hl1
, hr_lookups hl2
, hr_lookups hl3
, hr_lookups hl4
, ff_formulas_b_f ff_comp_level
, ff_formulas_b_f ff_rndg
WHERE bclf.COMP_LVL_DET_CD = hl1.lookup_code(+)
AND hl1.lookup_type(+) = 'BEN_COMP_LVL_DET'
AND bclf.comp_src_cd = hl2.lookup_code(+)
AND hl2.lookup_type(+) = 'BEN_COMP_SRC'
--AND bclf.comp_src_cd = hl3.lookup_code(+)
--AND hl3.lookup_type(+) = 'BEN_RNDG'
AND bclf.STTD_SAL_PRDCTY_CD = hl4.lookup_code(+)
AND hl4.lookup_type(+) = 'BEN_ACTY_REF_PERD'
AND bclf.COMP_LVL_DET_RL =ff_comp_level.formula_id (+)
AND bclf.rndg_rl =ff_rndg.formula_id (+)
and bclf.BNFTS_BAL_ID = bbbf.BNFTS_BAL_ID (+)
and bclf.rndg_cd = hl3.lookup_code (+)
AND (( :P_CREATION_TYPE= 'Custom' AND bclf.created_by <> 'SEED_DATA_FROM_APPLICATION')
OR ( :P_CREATION_TYPE = 'Seeded' AND bclf.created_by = 'SEED_DATA_FROM_APPLICATION')
OR ( :P_CREATION_TYPE = 'Seeded Modified' AND bclf.created_by = 'SEED_DATA_FROM_APPLICATION' AND bclf.last_updated_by <> 'SEED_DATA_FROM_APPLICATION')
OR (:P_CREATION_TYPE = 'ALL' ))
