HR_TM_MOVMT_M_I

(SQL View)
 Index Back

Managers HC Movement


SELECT JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , C.NAME_DISPLAY , ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)), %Coalesce(( SELECT RR.DESCR50 FROM PS_REG_REGION_TBL RR WHERE RR.REG_REGION = JOB.REG_REGION),' '), %Coalesce(( SELECT SA.DESCR FROM PS_SAL_PLAN_TBL SA WHERE SA.SETID = JOB.SETID_SALARY AND SA.SAL_ADMIN_PLAN = JOB.SAL_ADMIN_PLAN AND %EffdtCheck(SAL_PLAN_TBL SA1, SA, %CurrentDateIn) AND SA.EFF_STATUS<>'I'),' ') , H.SUPERVISOR_ID, %Coalesce(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = H.SUPERVISOR_ID),' '), %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)),' ') , CASE WHEN JOB.ACTION = 'XFR' THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30164) ELSE ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_TBL ACT WHERE ACT.ACTION = JOB.ACTION AND ACT.EFF_STATUS = 'A' AND %EffdtCheck(ACTION_TBL ACT1, ACT, %CurrentDateIn)) END , ( SELECT A7.XLATLONGNAME FROM PSXLATITEM A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = JOB.FULL_PART_TIME AND A7.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM A71, A7, %CurrentDateIn)) , CASE H.HR_DR_LEVEL WHEN 1 THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30168) ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30162) END, %Coalesce(( SELECT JCODE.DESCR FROM PS_JOBCODE_TBL JCODE WHERE JOB.SETID_DEPT = JCODE.SETID AND JOB.JOBCODE = JCODE.JOBCODE AND %EffdtCheck(JOBCODE_TBL JCODE1, JCODE, %CurrentDateIn)),' ') , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE JOB.POSITION_NBR = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)),' ') , %Coalesce(( SELECT DEPT.DESCR FROM PS_DEPT_TBL DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND %EffdtCheck(DEPT_TBL DEPT1, DEPT, %CurrentDateIn)),' ') , %Coalesce(( SELECT A12.DESCR FROM PS_ESTAB_TBL A12 WHERE A12.ESTABID = JOB.ESTABID AND A12.EFF_STATUS = 'A' AND %EffdtCheck(ESTAB_TBL A121, A12, %CurrentDateIn)),' '), %Coalesce(( SELECT LOCN.DESCR FROM PS_LOCATION_TBL LOCN WHERE JOB.SETID_DEPT = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND %EffdtCheck(LOCATION_TBL LOCN1, LOCN, %CurrentDateIn)),' ') , %DateNull ,%DateNull, O.OPRID FROM PS_JOB JOB , PS_PERSONAL_DATA C , PS_HR_DIRECT_REP_2 H, PSOPRDEFN O WHERE JOB.EMPLID = C.EMPLID AND JOB.EMPLID = H.EMPLID AND JOB.EMPL_RCD = H.EMPL_RCD AND H.DRILL_DOWN_FLAG = 'Y' AND '2' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'HR_DR_TEAM_FLU' AND J.MARKET = 'GBL') AND H.SUPERVISOR_ID = O.EMPLID AND JOB.ACTION IN ('ADD','ADL','ASC','ASG','DEM','HIR','LOA','LOF','LTD','LTO','PLA','PLV','POI','PRO','REC','REH','RET','RFA','RFD','RFL','RNW','RTS','RWB','RWP','SF4','SF5','SF7','SFE','SFF','SFG','SFI','SFK','SFL','SFN','SFP','SFR','SFS','SFT','SFV','STD','STO','SUS','SWB','TAS','TDL','TER','TWB','TWP','XFR') UNION ALL SELECT JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , C.NAME_DISPLAY , ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)), %Coalesce(( SELECT RR.DESCR50 FROM PS_REG_REGION_TBL RR WHERE RR.REG_REGION = JOB.REG_REGION),' '), %Coalesce(( SELECT SA.DESCR FROM PS_SAL_PLAN_TBL SA WHERE SA.SETID = JOB.SETID_SALARY AND SA.SAL_ADMIN_PLAN = JOB.SAL_ADMIN_PLAN AND %EffdtCheck(SAL_PLAN_TBL SA1, SA, %CurrentDateIn) AND SA.EFF_STATUS<>'I'),' ') , H.SUPERVISOR_ID, %Coalesce(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = H.SUPERVISOR_ID),' '), %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)),' ') , CASE WHEN JOB.ACTION = 'XFR' THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30164) ELSE ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_TBL ACT WHERE ACT.ACTION = JOB.ACTION AND ACT.EFF_STATUS = 'A' AND %EffdtCheck(ACTION_TBL ACT1, ACT, %CurrentDateIn)) END , ( SELECT A7.XLATLONGNAME FROM PSXLATITEM A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = JOB.FULL_PART_TIME AND A7.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM A71, A7, %CurrentDateIn)) , CASE H.HR_DR_LEVEL WHEN 1 THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30168) ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30162) END, %Coalesce(( SELECT JCODE.DESCR FROM PS_JOBCODE_TBL JCODE WHERE JOB.SETID_DEPT = JCODE.SETID AND JOB.JOBCODE = JCODE.JOBCODE AND %EffdtCheck(JOBCODE_TBL JCODE1, JCODE, %CurrentDateIn)),' ') , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE JOB.POSITION_NBR = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)),' ') , %Coalesce(( SELECT DEPT.DESCR FROM PS_DEPT_TBL DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND %EffdtCheck(DEPT_TBL DEPT1, DEPT, %CurrentDateIn)),' ') , %Coalesce(( SELECT A12.DESCR FROM PS_ESTAB_TBL A12 WHERE A12.ESTABID = JOB.ESTABID AND A12.EFF_STATUS = 'A' AND %EffdtCheck(ESTAB_TBL A121, A12, %CurrentDateIn)),' '), %Coalesce(( SELECT LOCN.DESCR FROM PS_LOCATION_TBL LOCN WHERE JOB.SETID_DEPT = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND %EffdtCheck(LOCATION_TBL LOCN1, LOCN, %CurrentDateIn)),' ') , %DateNull ,%DateNull, O.OPRID FROM PS_JOB JOB , PS_PERSONAL_DATA C , PS_HR_DIRECT_REP_3 H , PSOPRDEFN O WHERE JOB.EMPLID = C.EMPLID AND JOB.EMPLID = H.EMPLID AND JOB.EMPL_RCD = H.EMPL_RCD AND H.DRILL_DOWN_FLAG = 'Y' AND '3' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'HR_DR_TEAM_FLU' AND J.MARKET = 'GBL') AND H.SUPERVISOR_ID = O.EMPLID AND JOB.ACTION IN ('ADD','ADL','ASC','ASG','DEM','HIR','LOA','LOF','LTD','LTO','PLA','PLV','POI','PRO','REC','REH','RET','RFA','RFD','RFL','RNW','RTS','RWB','RWP','SF4','SF5','SF7','SFE','SFF','SFG','SFI','SFK','SFL','SFN','SFP','SFR','SFS','SFT','SFV','STD','STO','SUS','SWB','TAS','TDL','TER','TWB','TWP','XFR') UNION ALL SELECT JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , C.NAME_DISPLAY , ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)), %Coalesce(( SELECT RR.DESCR50 FROM PS_REG_REGION_TBL RR WHERE RR.REG_REGION = JOB.REG_REGION),' '), %Coalesce(( SELECT SA.DESCR FROM PS_SAL_PLAN_TBL SA WHERE SA.SETID = JOB.SETID_SALARY AND SA.SAL_ADMIN_PLAN = JOB.SAL_ADMIN_PLAN AND %EffdtCheck(SAL_PLAN_TBL SA1, SA, %CurrentDateIn) AND SA.EFF_STATUS<>'I'),' ') , H.SUPERVISOR_ID, %Coalesce(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = H.SUPERVISOR_ID),' '), %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)),' ') , CASE WHEN JOB.ACTION = 'XFR' THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30164) ELSE ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_TBL ACT WHERE ACT.ACTION = JOB.ACTION AND ACT.EFF_STATUS = 'A' AND %EffdtCheck(ACTION_TBL ACT1, ACT, %CurrentDateIn)) END , ( SELECT A7.XLATLONGNAME FROM PSXLATITEM A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = JOB.FULL_PART_TIME AND A7.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM A71, A7, %CurrentDateIn)) , CASE H.HR_DR_LEVEL WHEN 1 THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30168) ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30162) END, %Coalesce(( SELECT JCODE.DESCR FROM PS_JOBCODE_TBL JCODE WHERE JOB.SETID_DEPT = JCODE.SETID AND JOB.JOBCODE = JCODE.JOBCODE AND %EffdtCheck(JOBCODE_TBL JCODE1, JCODE, %CurrentDateIn)),' ') , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE JOB.POSITION_NBR = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)),' ') , %Coalesce(( SELECT DEPT.DESCR FROM PS_DEPT_TBL DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND %EffdtCheck(DEPT_TBL DEPT1, DEPT, %CurrentDateIn)),' ') , %Coalesce(( SELECT A12.DESCR FROM PS_ESTAB_TBL A12 WHERE A12.ESTABID = JOB.ESTABID AND A12.EFF_STATUS = 'A' AND %EffdtCheck(ESTAB_TBL A121, A12, %CurrentDateIn)),' '), %Coalesce(( SELECT LOCN.DESCR FROM PS_LOCATION_TBL LOCN WHERE JOB.SETID_DEPT = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND %EffdtCheck(LOCATION_TBL LOCN1, LOCN, %CurrentDateIn)),' ') , %DateNull ,%DateNull, O.OPRID FROM PS_JOB JOB , PS_PERSONAL_DATA C , PS_HR_DIRECT_REP_4 H , PSOPRDEFN O WHERE JOB.EMPLID = C.EMPLID AND JOB.EMPLID = H.EMPLID AND JOB.EMPL_RCD = H.EMPL_RCD AND H.DRILL_DOWN_FLAG = 'Y' AND '4' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'HR_DR_TEAM_FLU' AND J.MARKET = 'GBL') AND H.SUPERVISOR_ID = O.EMPLID AND JOB.ACTION IN ('ADD','ADL','ASC','ASG','DEM','HIR','LOA','LOF','LTD','LTO','PLA','PLV','POI','PRO','REC','REH','RET','RFA','RFD','RFL','RNW','RTS','RWB','RWP','SF4','SF5','SF7','SFE','SFF','SFG','SFI','SFK','SFL','SFN','SFP','SFR','SFS','SFT','SFV','STD','STO','SUS','SWB','TAS','TDL','TER','TWB','TWP','XFR') UNION ALL SELECT JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , C.NAME_DISPLAY , ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)), %Coalesce(( SELECT RR.DESCR50 FROM PS_REG_REGION_TBL RR WHERE RR.REG_REGION = JOB.REG_REGION),' '), %Coalesce(( SELECT SA.DESCR FROM PS_SAL_PLAN_TBL SA WHERE SA.SETID = JOB.SETID_SALARY AND SA.SAL_ADMIN_PLAN = JOB.SAL_ADMIN_PLAN AND %EffdtCheck(SAL_PLAN_TBL SA1, SA, %CurrentDateIn) AND SA.EFF_STATUS<>'I'),' ') , H.SUPERVISOR_ID, %Coalesce(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = H.SUPERVISOR_ID),' '), %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)),' ') , CASE WHEN JOB.ACTION = 'XFR' THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30164) ELSE ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_TBL ACT WHERE ACT.ACTION = JOB.ACTION AND ACT.EFF_STATUS = 'A' AND %EffdtCheck(ACTION_TBL ACT1, ACT, %CurrentDateIn)) END , ( SELECT A7.XLATLONGNAME FROM PSXLATITEM A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = JOB.FULL_PART_TIME AND A7.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM A71, A7, %CurrentDateIn)) , CASE H.HR_DR_LEVEL WHEN 1 THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30168) ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30162) END, %Coalesce(( SELECT JCODE.DESCR FROM PS_JOBCODE_TBL JCODE WHERE JOB.SETID_DEPT = JCODE.SETID AND JOB.JOBCODE = JCODE.JOBCODE AND %EffdtCheck(JOBCODE_TBL JCODE1, JCODE, %CurrentDateIn)),' ') , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE JOB.POSITION_NBR = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)),' ') , %Coalesce(( SELECT DEPT.DESCR FROM PS_DEPT_TBL DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND %EffdtCheck(DEPT_TBL DEPT1, DEPT, %CurrentDateIn)),' ') , %Coalesce(( SELECT A12.DESCR FROM PS_ESTAB_TBL A12 WHERE A12.ESTABID = JOB.ESTABID AND A12.EFF_STATUS = 'A' AND %EffdtCheck(ESTAB_TBL A121, A12, %CurrentDateIn)),' '), %Coalesce(( SELECT LOCN.DESCR FROM PS_LOCATION_TBL LOCN WHERE JOB.SETID_DEPT = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND %EffdtCheck(LOCATION_TBL LOCN1, LOCN, %CurrentDateIn)),' ') , %DateNull ,%DateNull, O.OPRID FROM PS_JOB JOB , PS_PERSONAL_DATA C , PS_HR_DIRECT_REP_5 H , PSOPRDEFN O WHERE JOB.EMPLID = C.EMPLID AND JOB.EMPLID = H.EMPLID AND JOB.EMPL_RCD = H.EMPL_RCD AND H.DRILL_DOWN_FLAG = 'Y' AND '5' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'HR_DR_TEAM_FLU' AND J.MARKET = 'GBL') AND H.SUPERVISOR_ID = O.EMPLID AND JOB.ACTION IN ('ADD','ADL','ASC','ASG','DEM','HIR','LOA','LOF','LTD','LTO','PLA','PLV','POI','PRO','REC','REH','RET','RFA','RFD','RFL','RNW','RTS','RWB','RWP','SF4','SF5','SF7','SFE','SFF','SFG','SFI','SFK','SFL','SFN','SFP','SFR','SFS','SFT','SFV','STD','STO','SUS','SWB','TAS','TDL','TER','TWB','TWP','XFR') UNION ALL SELECT JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , C.NAME_DISPLAY , ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)), %Coalesce(( SELECT RR.DESCR50 FROM PS_REG_REGION_TBL RR WHERE RR.REG_REGION = JOB.REG_REGION),' '), %Coalesce(( SELECT SA.DESCR FROM PS_SAL_PLAN_TBL SA WHERE SA.SETID = JOB.SETID_SALARY AND SA.SAL_ADMIN_PLAN = JOB.SAL_ADMIN_PLAN AND %EffdtCheck(SAL_PLAN_TBL SA1, SA, %CurrentDateIn) AND SA.EFF_STATUS<>'I'),' ') , H.SUPERVISOR_ID, %Coalesce(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = H.SUPERVISOR_ID),' '), %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)),' ') , CASE WHEN JOB.ACTION = 'XFR' THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30164) ELSE ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_TBL ACT WHERE ACT.ACTION = JOB.ACTION AND ACT.EFF_STATUS = 'A' AND %EffdtCheck(ACTION_TBL ACT1, ACT, %CurrentDateIn)) END , ( SELECT A7.XLATLONGNAME FROM PSXLATITEM A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = JOB.FULL_PART_TIME AND A7.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM A71, A7, %CurrentDateIn)) , CASE H.HR_DR_LEVEL WHEN 1 THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30168) ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30162) END, %Coalesce(( SELECT JCODE.DESCR FROM PS_JOBCODE_TBL JCODE WH ERE JOB.SETID_DEPT = JCODE.SETID AND JOB.JOBCODE = JCODE.JOBCODE AND %EffdtCheck(JOBCODE_TBL JCODE1, JCODE, %CurrentDateIn)),' ') , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE JOB.POSITION_NBR = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)),' ') , %Coalesce(( SELECT DEPT.DESCR FROM PS_DEPT_TBL DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND %EffdtCheck(DEPT_TBL DEPT1, DEPT, %CurrentDateIn)),' ') , %Coalesce(( SELECT A12.DESCR FROM PS_ESTAB_TBL A12 WHERE A12.ESTABID = JOB.ESTABID AND A12.EFF_STATUS = 'A' AND %EffdtCheck(ESTAB_TBL A121, A12, %CurrentDateIn)),' '), %Coalesce(( SELECT LOCN.DESCR FROM PS_LOCATION_TBL LOCN WHERE JOB.SETID_DEPT = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND %EffdtCheck(LOCATION_TBL LOCN1, LOCN, %CurrentDateIn)),' ') , %DateNull ,%DateNull, O.OPRID FROM PS_JOB JOB , PS_PERSONAL_DATA C , PS_HR_DIRECT_REP_6 H , PSOPRDEFN O WHERE JOB.EMPLID = C.EMPLID AND JOB.EMPLID = H.EMPLID AND JOB.EMPL_RCD = H.EMPL_RCD AND H.DRILL_DOWN_FLAG = 'Y' AND '6' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'HR_DR_TEAM_FLU' AND J.MARKET = 'GBL') AND H.SUPERVISOR_ID = O.EMPLID AND JOB.ACTION IN ('ADD','ADL','ASC','ASG','DEM','HIR','LOA','LOF','LTD','LTO','PLA','PLV','POI','PRO','REC','REH','RET','RFA','RFD','RFL','RNW','RTS','RWB','RWP','SF4','SF5','SF7','SFE','SFF','SFG','SFI','SFK','SFL','SFN','SFP','SFR','SFS','SFT','SFV','STD','STO','SUS','SWB','TAS','TDL','TER','TWB','TWP','XFR') UNION ALL SELECT JOB.EMPLID , JOB.EMPL_RCD , BB.EFFDT , C.NAME_DISPLAY , ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)), %Coalesce(( SELECT RR.DESCR50 FROM PS_REG_REGION_TBL RR WHERE RR.REG_REGION = JOB.REG_REGION),' '), %Coalesce(( SELECT SA.DESCR FROM PS_SAL_PLAN_TBL SA WHERE SA.SETID = JOB.SETID_SALARY AND SA.SAL_ADMIN_PLAN = JOB.SAL_ADMIN_PLAN AND %EffdtCheck(SAL_PLAN_TBL SA1, SA, %CurrentDateIn) AND SA.EFF_STATUS<>'I'),' ') , H.SUPERVISOR_ID, %Coalesce(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = H.SUPERVISOR_ID),' '), %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)),' ') , ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30163 ), ( SELECT A7.XLATLONGNAME FROM PSXLATITEM A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = JOB.FULL_PART_TIME AND A7.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM A71, A7, %CurrentDateIn)) , CASE H.HR_DR_LEVEL WHEN 1 THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30168) ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30162) END, %Coalesce(( SELECT JCODE.DESCR FROM PS_JOBCODE_TBL JCODE WHERE JOB.SETID_DEPT = JCODE.SETID AND JOB.JOBCODE = JCODE.JOBCODE AND %EffdtCheck(JOBCODE_TBL JCODE1, JCODE, %CurrentDateIn)),' ') , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE JOB.POSITION_NBR = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)),' ') , %Coalesce(( SELECT DEPT.DESCR FROM PS_DEPT_TBL DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND %EffdtCheck(DEPT_TBL DEPT1, DEPT, %CurrentDateIn)),' ') , %Coalesce(( SELECT A12.DESCR FROM PS_ESTAB_TBL A12 WHERE A12.ESTABID = JOB.ESTABID AND A12.EFF_STATUS = 'A' AND %EffdtCheck(ESTAB_TBL A121, A12, %CurrentDateIn)),' '), %Coalesce(( SELECT LOCN.DESCR FROM PS_LOCATION_TBL LOCN WHERE JOB.SETID_DEPT = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND %EffdtCheck(LOCATION_TBL LOCN1, LOCN, %CurrentDateIn)),' ') , %DateNull ,%DateNull, O.OPRID FROM PS_JOB JOB , PS_JOB BB , PS_PERSONAL_DATA C , PS_HR_DIRECT_REP_2 H , PSOPRDEFN O WHERE JOB.EMPLID = BB.EMPLID AND JOB.EMPL_RCD = BB.EMPL_RCD AND JOB.EFFSEQ = BB.EFFSEQ AND (BB.ACTION = 'XFR' OR (BB.ACTION = 'POS' AND BB.ACTION_REASON = 'XFR')) AND JOB.EMPLID = H.EMPLID AND JOB.EMPL_RCD = H.EMPL_RCD AND H.SUPERVISOR_ID = O.EMPLID AND C.EMPLID = JOB.EMPLID AND H.DRILL_DOWN_FLAG = 'Y' AND '2' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'HR_DR_TEAM_FLU' AND J.MARKET = 'GBL') AND JOB.EFFDT = ( SELECT MAX(AA1.EFFDT) FROM PS_JOB AA1 WHERE AA1.EMPLID = BB.EMPLID AND AA1.EMPL_RCD = BB.EMPL_RCD AND AA1.EFFSEQ = BB.EFFSEQ AND AA1.EFFDT < BB.EFFDT ) UNION ALL SELECT JOB.EMPLID , JOB.EMPL_RCD , BB.EFFDT , C.NAME_DISPLAY , ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)), %Coalesce(( SELECT RR.DESCR50 FROM PS_REG_REGION_TBL RR WHERE RR.REG_REGION = JOB.REG_REGION),' '), %Coalesce(( SELECT SA.DESCR FROM PS_SAL_PLAN_TBL SA WHERE SA.SETID = JOB.SETID_SALARY AND SA.SAL_ADMIN_PLAN = JOB.SAL_ADMIN_PLAN AND %EffdtCheck(SAL_PLAN_TBL SA1, SA, %CurrentDateIn) AND SA.EFF_STATUS<>'I'),' ') , H.SUPERVISOR_ID, %Coalesce(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = H.SUPERVISOR_ID),' '), %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)),' ') , ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30163 ), ( SELECT A7.XLATLONGNAME FROM PSXLATITEM A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = JOB.FULL_PART_TIME AND A7.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM A71, A7, %CurrentDateIn)) , CASE H.HR_DR_LEVEL WHEN 1 THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30168) ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30162) END, %Coalesce(( SELECT JCODE.DESCR FROM PS_JOBCODE_TBL JCODE WHERE JOB.SETID_DEPT = JCODE.SETID AND JOB.JOBCODE = JCODE.JOBCODE AND %EffdtCheck(JOBCODE_TBL JCODE1, JCODE, %CurrentDateIn)),' ') , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE JOB.POSITION_NBR = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)),' ') , %Coalesce(( SELECT DEPT.DESCR FROM PS_DEPT_TBL DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND %EffdtCheck(DEPT_TBL DEPT1, DEPT, %CurrentDateIn)),' ') , %Coalesce(( SELECT A12.DESCR FROM PS_ESTAB_TBL A12 WHERE A12.ESTABID = JOB.ESTABID AND A12.EFF_STATUS = 'A' AND %EffdtCheck(ESTAB_TBL A121, A12, %CurrentDateIn)),' '), %Coalesce(( SELECT LOCN.DESCR FROM PS_LOCATION_TBL LOCN WHERE JOB.SETID_DEPT = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND %EffdtCheck(LOCATION_TBL LOCN1, LOCN, %CurrentDateIn)),' ') , %DateNull ,%DateNull, O.OPRID FROM PS_JOB JOB , PS_JOB BB , PS_PERSONAL_DATA C , PS_HR_DIRECT_REP_3 H , PSOPRDEFN O WHERE JOB.EMPLID = BB.EMPLID AND JOB.EMPL_RCD = BB.EMPL_RCD AND JOB.EFFSEQ = BB.EFFSEQ AND (BB.ACTION = 'XFR' OR (BB.ACTION = 'POS' AND BB.ACTION_REASON = 'XFR')) AND JOB.EMPLID = H.EMPLID AND JOB.EMPL_RCD = H.EMPL_RCD AND H.SUPERVISOR_ID = O.EMPLID AND C.EMPLID = JOB.EMPLID AND H.DRILL_DOWN_FLAG = 'Y' AND '3' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'HR_DR_TEAM_FLU' AND J.MARKET = 'GBL') AND JOB.EFFDT = ( SELECT MAX(AA1.EFFDT) FROM PS_JOB AA1 WHERE AA1.EMPLID = BB.EMPLID AND AA1.EMPL_RCD = BB.EMPL_RCD AND AA1.EFFSEQ = BB.EFFSEQ AND AA1.EFFDT < BB.EFFDT ) UNION ALL SELECT JOB.EMPLID , JOB.EMPL_RCD , BB.EFFDT , C.NAME_DISPLAY , ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)), %Coalesce(( SELECT RR.DESCR50 FROM PS_REG_REGION_TBL RR WHERE RR.REG_REGION = JOB.REG_REGION),' '), %Coalesce(( SELECT SA.DESCR FROM PS_SAL_PLAN_TBL SA WHERE SA.SETID = JOB.SETID_SALARY AND SA.SAL_ADMIN_PLAN = JOB.SAL_ADMIN_PLAN AND %EffdtCheck(SAL_PLAN_TBL SA1, SA, %CurrentDateIn) AND SA.EFF_STATUS<>'I'),' ') , H.SUPERVISOR_ID, %Coalesce(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = H.SUPERVISOR_ID),' '), %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)),' ') , ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30163 ), ( SELECT A7.XLATLONGNAME FROM PSXLATITEM A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = JOB.FULL_PART_TIME AND A7.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM A71, A7, %CurrentDateIn)) , CASE H.HR_DR_LEVEL WHEN 1 THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30168) ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30162) END, %Coalesce(( SELECT JCODE.DESCR FROM PS_JOBCODE_TBL JCODE WHERE JOB.SETID_DEPT = JCODE.SETID AND JOB.JOBCODE = JCODE.JOBCODE AND %EffdtCheck(JOBCODE_TBL JCODE1, JCODE, %CurrentDateIn)),' ') , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE JOB.POSITION_NBR = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)),' ') , %Coalesce(( SELECT DEPT.DESCR FROM PS_DEPT_TBL DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND %EffdtCheck(DEPT_TBL DEPT1, DEPT, %CurrentDateIn)),' ') , %Coalesce(( SELECT A12.DESCR FROM PS_ESTAB_TBL A12 WHERE A12.ESTABID = JOB.ESTABID AND A12.EFF_STATUS = 'A' AND %EffdtCheck(ESTAB_TBL A121, A12, %CurrentDateIn)),' '), %Coalesce(( SELECT LOCN.DESCR FROM PS_LOCATION_TBL LOCN WHERE JOB.SETID_DEPT = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND %EffdtCheck(LOCATION_TBL LOCN1, LOCN, %CurrentDateIn)),' ') , %DateNull ,%DateNull, O.OPRID FROM PS_JOB JOB , PS_JOB BB , PS_PERSONAL_DATA C , PS_HR_DIRECT_REP_4 H , PSOPRDEFN O WHERE JOB.EMPLID = BB.EMPLID AND JOB.EMPL_RCD = BB.EMPL_RCD AND JOB.EFFSEQ = BB.EFFSEQ AND (BB.ACTION = 'XFR' OR (BB.ACTION = 'POS' AND BB.ACTION_REASON = 'XFR')) AND JOB.EMPLID = H.EMPLID AND JOB.EMPL_RCD = H.EMPL_RCD AND H.SUPERVISOR_ID = O.EMPLID AND C.EMPLID = JOB.EMPLID AND H.DRILL_DOWN_FLAG = 'Y' AND '4' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'HR_DR_TEAM_FLU' AND J.MARKET = 'GBL') AND JOB.EFFDT = ( SELECT MAX(AA1.EFFDT) FROM PS_JOB AA1 WHERE AA1.EMPLID = BB.EMPLID AND AA1.EMPL_RCD = BB.EMPL_RCD AND AA1.EFFSEQ = BB.EFFSEQ AND AA1.EFFDT < BB.EFFDT ) UNION ALL SELECT JOB.EMPLID , JOB.EMPL_RCD , BB.EFFDT , C.NAME_DISPLAY , ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)), %Coalesce(( SELECT RR.DESCR50 FROM PS_REG_REGION_TBL RR WHERE RR.REG_REGION = JOB.REG_REGION),' '), %Coalesce(( SELECT SA.DESCR FROM PS_SAL_PLAN_TBL SA WHERE SA.SETID = JOB.SETID_SALARY AND SA.SAL_ADMIN_PLAN = JOB.SAL_ADMIN_PLAN AND %EffdtCheck(SAL_PLAN_TBL SA1, SA, %CurrentDateIn) AND SA.EFF_STATUS<>'I'),' ') , H.SUPERVISOR_ID, %Coalesce(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = H.SUPERVISOR_ID),' '), %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)),' ') , ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30163 ), ( SELECT A7.XLATLONGNAME FROM PSXLATITEM A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = JOB.FULL_PART_TIME AND A7.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM A71, A7, %CurrentDateIn)) , CASE H.HR_DR_LEVEL WHEN 1 THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30168) ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30162) END, %Coalesce(( SELECT JCODE.DESCR FROM PS_JOBCODE_TBL JCODE WHERE JOB.SETID_DEPT = JCODE.SETID AND JOB.JOBCODE = JCODE.JOBCODE AND %EffdtCheck(JOBCODE_TBL JCODE1, JCODE, %CurrentDateIn)),' ') , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE JOB.POSITION_NBR = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)),' ') , %Coalesce(( SELECT DEPT.DESCR FROM PS_DEPT_TBL DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND %EffdtCheck(DEPT_TBL DEPT1, DEPT, %CurrentDateIn)),' ') , %Coalesce(( SELECT A12.DESCR FROM PS_ESTAB_TBL A12 WHERE A12.ESTABID = JOB.ESTABID AND A12.EFF_STATUS = 'A' AND %EffdtCheck(ESTAB_TBL A121, A12, %CurrentDateIn)),' '), %Coalesce(( SELECT LOCN.DESCR FROM PS_LOCATION_TBL LOCN WHERE JOB.SETID_DEPT = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND %EffdtCheck(LOCATION_TBL LOCN1, LOCN, %CurrentDateIn)),' ') , %DateNull ,%DateNull, O.OPRID FROM PS_JOB JOB , PS_JOB BB , PS_PERSONAL_DATA C , PS_HR_DIRECT_REP_5 H , PSOPRDEFN O WHERE JOB.EMPLID = BB.EMPLID AND JOB.EMPL_RCD = BB.EMPL_RCD AND JOB.EFFSEQ = BB.EFFSEQ AND (BB.ACTION = 'XFR' OR (BB.ACTION = 'POS' AND BB.ACTION_REASON = 'XFR')) AND JOB.EMPLID = H.EMPLID AND JOB.EMPL_RCD = H.EMPL_RCD AND H.SUPERVISOR_ID = O.EMPLID AND C.EMPLID = JOB.EMPLID AND H.DRILL_DOWN_FLAG = 'Y' AND '5' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'HR_DR_TEAM_FLU' AND J.MARKET = 'GBL') AND JOB.EFFDT = ( SELECT MAX(AA1.EFFDT) FROM PS_JOB AA1 WHERE AA1.EMPLID = BB.EMPLID AND AA1.EMPL_RCD = BB.EMPL_RCD AND AA1.EFFSEQ = BB.EFFSEQ AND AA1.EFFDT < BB.EFFDT ) UNION ALL SELECT JOB.EMPLID , JOB.EMPL_RCD , BB.EFFDT , C.NAME_DISPLAY , ( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)), %Coalesce(( SELECT RR.DESCR50 FROM PS_REG_REGION_TBL RR WHERE RR.REG_REGION = JOB.REG_REGION),' '), %Coalesce(( SELECT SA.DESCR FROM PS_SAL_PLAN_TBL SA WHERE SA.SETID = JOB.SETID_SALARY AND SA.SAL_ADMIN_PLAN = JOB.SAL_ADMIN_PLAN AND %EffdtCheck(SAL_PLAN_TBL SA1, SA, %CurrentDateIn) AND SA.EFF_STATUS<>'I'),' ') , H.SUPERVISOR_ID, %Coalesce(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = H.SUPERVISOR_ID),' '), %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A 6, %CurrentDateIn)),' ') , ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30163 ), ( SELECT A7.XLATLONGNAME FROM PSXLATITEM A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = JOB.FULL_PART_TIME AND A7.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM A71, A7, %CurrentDateIn)) , CASE H.HR_DR_LEVEL WHEN 1 THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30168) ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30162) END, %Coalesce(( SELECT JCODE.DESCR FROM PS_JOBCODE_TBL JCODE WHERE JOB.SETID_DEPT = JCODE.SETID AND JOB.JOBCODE = JCODE.JOBCODE AND %EffdtCheck(JOBCODE_TBL JCODE1, JCODE, %CurrentDateIn)),' ') , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE JOB.POSITION_NBR = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)),' ') , %Coalesce(( SELECT DEPT.DESCR FROM PS_DEPT_TBL DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND %EffdtCheck(DEPT_TBL DEPT1, DEPT, %CurrentDateIn)),' ') , %Coalesce(( SELECT A12.DESCR FROM PS_ESTAB_TBL A12 WHERE A12.ESTABID = JOB.ESTABID AND A12.EFF_STATUS = 'A' AND %EffdtCheck(ESTAB_TBL A121, A12, %CurrentDateIn)),' '), %Coalesce(( SELECT LOCN.DESCR FROM PS_LOCATION_TBL LOCN WHERE JOB.SETID_DEPT = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND %EffdtCheck(LOCATION_TBL LOCN1, LOCN, %CurrentDateIn)),' ') , %DateNull ,%DateNull, O.OPRID FROM PS_JOB JOB , PS_JOB BB , PS_PERSONAL_DATA C , PS_HR_DIRECT_REP_6 H , PSOPRDEFN O WHERE JOB.EMPLID = BB.EMPLID AND JOB.EMPL_RCD = BB.EMPL_RCD AND JOB.EFFSEQ = BB.EFFSEQ AND (BB.ACTION = 'XFR' OR (BB.ACTION = 'POS' AND BB.ACTION_REASON = 'XFR')) AND JOB.EMPLID = H.EMPLID AND JOB.EMPL_RCD = H.EMPL_RCD AND H.SUPERVISOR_ID = O.EMPLID AND C.EMPLID = JOB.EMPLID AND H.DRILL_DOWN_FLAG = 'Y' AND '6' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'HR_DR_TEAM_FLU' AND J.MARKET = 'GBL') AND JOB.EFFDT = ( SELECT MAX(AA1.EFFDT) FROM PS_JOB AA1 WHERE AA1.EMPLID = BB.EMPLID AND AA1.EMPL_RCD = BB.EMPL_RCD AND AA1.EFFSEQ = BB.EFFSEQ AND AA1.EFFDT < BB.EFFDT )

  • Related Language Record: HR_TM_MVMT_LNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    3 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    4 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    5 DESCR50_1 Character(50) VARCHAR2(50) NOT NULL Description of length 50
    6 DESCR50 Character(50) VARCHAR2(50) NOT NULL Description of length 50
    7 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
    8 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
    9 SUPERVISOR_NAME Character(50) VARCHAR2(50) NOT NULL Supervisor Name
    10 NAME2 Character(40) VARCHAR2(40) NOT NULL Name 2
    11 DESCR100_2 Character(100) VARCHAR2(100) NOT NULL Description
    12 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    13 MESSAGE_TEXT Character(100) VARCHAR2(100) NOT NULL Message Text This field refers to the Text for a particular Message Number in the Message Catalog.
    14 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
    15 POSN_DESCR Character(30) VARCHAR2(30) NOT NULL Position Description
    16 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    17 ESTAB_DESCR Character(30) VARCHAR2(30) NOT NULL Description
    18 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    19 FROM_DT Date(10) DATE From Date
    20 TO_DT Date(10) DATE To Date
    21 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).