SELECT A.HRS_JOB_OPENING_ID ,B.JOBCODE ,COALESCE (D.JOB_FAMILY , C.JOB_FAMILY) ,B.JOB_FUNCTION ,COALESCE (D.HRS_DESCR , C.HRS_DESCR) FROM PS_HRS_JOB_OPENING A LEFT OUTER JOIN PS_JOBCODE_TBL B ON A.HRS_PRM_JOBCODE = B.JOBCODE LEFT OUTER JOIN PS_HRS_JOB_FAM_I_3 C ON B.JOB_FAMILY = C.JOB_FAMILY LEFT OUTER JOIN PS_HRS_JOB_FAM_I_3 D ON A.JOB_FAMILY = D.JOB_FAMILY WHERE (B.SETID IS NULL OR (B.SETID = ( SELECT G.SETID FROM PS_SET_CNTRL_GROUP G WHERE G.SETCNTRLVALUE = A.BUSINESS_UNIT AND G.REC_GROUP_ID = ( SELECT H.REC_GROUP_ID FROM PS_REC_GROUP_REC H WHERE H.RECNAME = 'JOBCODE_TBL') ) AND B.EFFDT = ( SELECT MAX(BE.EFFDT) FROM PS_JOBCODE_TBL BE WHERE BE.SETID = B.SETID AND BE.JOBCODE = B.JOBCODE AND BE.EFFDT <= %CurrentDateIn)))
|