Length of Service Derived Factors
SELECT BLF.NAME NAME_MDLOS,
HR_GENERAL.DECODE_LOOKUP('BEN_LOS_DT_TO_USE', BLF.LOS_DT_TO_USE_CD) PERIOD_START_MDLOS,
BLF.MN_LOS_NUM MN_LOS_NUM_MDLOS,
BLF.MX_LOS_NUM MX_LOS_NUM_MDLOS,
HR_GENERAL.DECODE_LOOKUP('BEN_TM_UOM', BLF.LOS_UOM) UNIT_MEASURE_MDLOS,
HR_GENERAL.DECODE_LOOKUP('BEN_LOS_DET', BLF.LOS_DET_CD) DETERMINATION_RULE_MDLOS,
HR_GENERAL.DECODE_LOOKUP('BEN_RNDG', BLF.RNDG_CD) ROUNDING_RULE_MDLOS,
FFFORMULAF2.BASE_FORMULA_NAME LOS_FACTOR_FORMULA_MDLOS,
FFFORMULAF1.BASE_FORMULA_NAME START_DATE_FORMULA_MDLOS,
FFFORMULAF3.BASE_FORMULA_NAME LOS_DET_RL_MDLOS,
FFFORMULAF4.BASE_FORMULA_NAME RNDG_RL_MDLOS
FROM BEN_LOS_FCTR BLF ,
FF_FORMULAS_B_F FFFORMULAF1
,FF_FORMULAS_B_F FFFORMULAF2
,FF_FORMULAS_B_F FFFORMULAF3
,FF_FORMULAS_B_F FFFORMULAF4
WHERE BLF.LOS_DT_TO_USE_RL = FFFORMULAF1.FORMULA_ID(+)
AND BLF.LOS_CALC_RL = FFFORMULAF2.FORMULA_ID(+)
AND BLF.LOS_DET_RL = FFFORMULAF3.FORMULA_ID(+)
AND BLF.RNDG_RL = FFFORMULAF4.FORMULA_ID(+)
AND :P_AS_OF_DATE BETWEEN FFFORMULAF1.effective_start_date(+) AND FFFORMULAF1.effective_end_date(+)
AND :P_AS_OF_DATE BETWEEN FFFORMULAF2.effective_start_date(+) AND FFFORMULAF2.effective_end_date(+)
AND :P_AS_OF_DATE BETWEEN FFFORMULAF3.effective_start_date(+) AND FFFORMULAF3.effective_end_date(+)
AND :P_AS_OF_DATE BETWEEN FFFORMULAF4.effective_start_date(+) AND FFFORMULAF4.effective_end_date(+)
AND (( :P_CREATION_TYPE= 'Custom' AND BLF.created_by <> 'SEED_DATA_FROM_APPLICATION')
OR ( :P_CREATION_TYPE = 'Seeded' AND BLF.created_by = 'SEED_DATA_FROM_APPLICATION')
OR ( :P_CREATION_TYPE = 'Seeded Modified' AND BLF.created_by = 'SEED_DATA_FROM_APPLICATION' AND BLF.last_updated_by <> 'SEED_DATA_FROM_APPLICATION')
OR (:P_CREATION_TYPE = 'ALL' ))
ORDER BY 1
