SELECT M.HPYP_CC_ID , CC.DESCR40 , M.PIN_CHART2_VAL , D.DESCR ,M.EFFDT , M.PAY_ENTITY , M.BUSINESS_UNIT , M.HPYP_CNTRL_AREA ,D.SETID FROM PS_GP_CC_MAP M , PS_HPYP_CC_TBL CC , PS_DEPT_TBL D , PS_GP_CC_SET S WHERE M.PAY_ENTITY = S.PAY_ENTITY AND M.BUSINESS_UNIT = S.BUSINESS_UNIT AND S.SEQNUM = 1 AND M.EFFDT = S.EFFDT AND M.HPYP_CNTRL_AREA = CC.HPYP_CNTRL_AREA AND M.HPYP_CC_ID = CC.HPYP_CC_ID AND CC.EFF_STATUS = 'A' AND CC.BUSINESS_UNIT_GL = S.BUSINESS_UNIT AND CC.EFFDT = ( SELECT MAX(CC1.EFFDT) FROM PS_HPYP_CC_TBL CC1 WHERE CC.HPYP_CNTRL_AREA = CC1.HPYP_CNTRL_AREA AND CC.HPYP_CC_ID = CC1.HPYP_CC_ID AND CC.BUSINESS_UNIT_GL = CC1.BUSINESS_UNIT_GL AND CC.EFF_STATUS = CC1.EFF_STATUS AND CC1.EFFDT <= M.EFFDT ) AND M.PIN_CHART2_VAL = D.DEPTID AND D.EFF_STATUS = 'A' AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_DEPT_TBL D1 WHERE D.SETID = D1.SETID AND D.DEPTID = D1.DEPTID AND D.EFF_STATUS = 'A' AND D1.EFFDT <= M.EFFDT )
|