Hours Worked Derived Factors
select BHWIPF.name NAME_MDFHW,
HR_GENERAL.DECODE_LOOKUP('BEN_HRS_WKD_SRC',BHWIPF.hrs_src_cd) HRS_SRC_CD_MDFHW,
BHWIPF.mn_hrs_num MN_HRS_NUM_MDFHW,
BHWIPF.mx_hrs_num MX_HRS_NUM_MDFHW,
HR_GENERAL.DECODE_LOOKUP('BEN_FREQ',BHWIPF.pyrl_freq_cd) PYRL_FREQ_CD_MDFHW,
HR_GENERAL.DECODE_LOOKUP('BEN_HRS_WKD_DET',BHWIPF.hrs_wkd_det_cd ) HRS_WKD_DET_CD_MDFHW,
HR_GENERAL.DECODE_LOOKUP('BEN_RNDG', BHWIPF.RNDG_cd) ROUNDING_RULE_MDFHW,
HR_GENERAL.DECODE_LOOKUP('BEN_ONCE_R_CNTNG', BHWIPF.ONCE_R_CNTUG_CD) EVALUTION_FREQ_MDFHW
,Decode(BHWIPF.NO_MN_HRS_WKD_FLAG,'Y','Yes','N','No') MN_HRS_WKD_FLAG_MDFHW
,Decode(BHWIPF.NO_MX_HRS_WKD_FLAG,'Y','Yes','N','No') MX_HRS_WKD_FLAG_MDFHW
,FFFORMULAF1.BASE_FORMULA_NAME DET_FORMULA_MDFHW
,FFFORMULAF2.BASE_FORMULA_NAME RNDG_FORMULA_MDFHW
,BBBF.NAME BNF_BAL_MDFHW
,FFFORMULAF3.BASE_FORMULA_NAME HRS_WKD_FACTR_FORMULA_MDFHW
,PBT.BASE_BALANCE_NAME||' '||PBD.BASE_DIMENSION_NAME DEFINED_BAL_MDFHW
FROM ben_hrs_wkd_in_perd_fctr BHWIPF
,FF_FORMULAS_B_F FFFORMULAF1
,FF_FORMULAS_B_F FFFORMULAF2
,FF_FORMULAS_B_F FFFORMULAF3
,BEN_BNFTS_BAL_F BBBF
,PAY_DEFINED_BALANCES PDB
,PAY_BALANCE_TYPES PBT
,PAY_BALANCE_DIMENSIONS PBD
WHERE BHWIPF.HRS_WKD_DET_RL = FFFORMULAF1.FORMULA_ID(+)
AND BHWIPF.RNDG_RL = FFFORMULAF2.FORMULA_ID(+)
AND BBBF.BNFTS_BAL_ID(+) = BHWIPF.BNFTS_BAL_ID
AND BHWIPF.HRS_WKD_CALC_RL = FFFORMULAF3.FORMULA_ID(+)
AND PDB.DEFINED_BALANCE_ID(+) = BHWIPF.DEFINED_BALANCE_ID
AND PBT.balance_type_id(+) = PDB.balance_type_id
AND PBD.BALANCE_DIMENSION_ID(+) = PDB.BALANCE_DIMENSION_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 BBBF.effective_start_date(+) AND BBBF.effective_end_date(+)
AND (( :P_CREATION_TYPE= 'Custom' AND BHWIPF.created_by <> 'SEED_DATA_FROM_APPLICATION')
OR ( :P_CREATION_TYPE = 'Seeded' AND BHWIPF.created_by = 'SEED_DATA_FROM_APPLICATION')
OR ( :P_CREATION_TYPE = 'Seeded Modified' AND BHWIPF.created_by = 'SEED_DATA_FROM_APPLICATION' AND BHWIPF.last_updated_by <> 'SEED_DATA_FROM_APPLICATION')
OR (:P_CREATION_TYPE = 'ALL' ))
