Age Derived Factors
SELECT baf.name NAME_MDFA
, hl2.meaning AGE_TO_USE_CD_MDFA
, hl3.meaning AGE_UOM_MDFA
, baf.mn_age_num MX_AGE_NUM_MDFA
, baf.mx_age_num MN_AGE_NUM_MDFA
, ff_age_calc.base_formula_name AGE_CALC_RL_MDFA
, hl1.meaning AGE_DET_RULE_MDFA
, ff_age_det.base_formula_name AGE_DET_RL_MDFA
, hl4.meaning RNDG_CD_MDFA
, ff_rndg.base_formula_name RNDG_RL_MDFA
FROM ben_age_fctr baf
, hr_lookups hl1
, hr_lookups hl2
, hr_lookups hl3
, hr_lookups hl4
, ff_formulas_b_f ff_age_calc
, ff_formulas_b_f ff_age_det
, ff_formulas_b_f ff_rndg
WHERE baf.age_det_cd = hl1.lookup_code(+)
AND hl1.lookup_type(+) = 'BEN_AGE_DET'
AND baf.age_to_use_cd = hl2.lookup_code(+)
AND hl2.lookup_type (+)= 'BEN_AGE_TO_USE_CD'
AND baf.age_uom = hl3.lookup_code(+)
AND hl3.lookup_type(+) = 'BEN_TM_UOM'
AND baf.RNDG_CD = hl4.lookup_code(+)
AND hl4.lookup_type(+) = 'BEN_RNDG'
AND baf.age_calc_rl =ff_age_calc.formula_id (+)
AND baf.age_det_rl =ff_age_det.formula_id (+)
AND baf.rndg_rl =ff_rndg.formula_id (+)
AND (( :P_CREATION_TYPE= 'Custom' AND baf.created_by <> 'SEED_DATA_FROM_APPLICATION')
OR ( :P_CREATION_TYPE = 'Seeded' AND baf.created_by = 'SEED_DATA_FROM_APPLICATION')
OR ( :P_CREATION_TYPE = 'Seeded Modified' AND baf.created_by = 'SEED_DATA_FROM_APPLICATION' AND baf.last_updated_by <> 'SEED_DATA_FROM_APPLICATION')
OR (:P_CREATION_TYPE = 'ALL' ))