HR_PG_CURR_M_I

(SQL View)
 Index Back

HR Admin's Current HC Profile


SELECT OPR.OPRID , A.EMPLID , A.EMPL_RCD , A.EFFDT , CASE C.HR_DR_LEVEL WHEN 1 THEN ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR = 1000 AND M.MESSAGE_NBR = 30168) ELSE ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR = 1000 AND M.MESSAGE_NBR = 30162) END, COALESCE(( SELECT A3.DESCR FROM PS_DEPT_TBL A3 WHERE A.SETID_DEPT = A3.SETID AND A.DEPTID = A3.DEPTID AND %EffdtCheck(DEPT_TBL A31, A3, %CurrentDateIn)), ' '), COALESCE(( SELECT A4.DESCR FROM PS_JOBCODE_TBL A4 WHERE A.SETID_DEPT = A4.SETID AND A.JOBCODE = A4.JOBCODE AND %EffdtCheck(JOBCODE_TBL A41, A4, %CurrentDateIn)), ' '), COALESCE(( SELECT A5.DESCR FROM PS_LOCATION_TBL A5 WHERE A.SETID_LOCATION = A5.SETID AND A.LOCATION = A5.LOCATION AND %EffdtCheck(LOCATION_TBL A51, A5, %CurrentDateIn)), ' '), COALESCE(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE A.POSITION_NBR = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)), ' '), COALESCE(( SELECT A7.XLATLONGNAME FROM PSXLATITEM A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = A.FULL_PART_TIME AND A7.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM A71, A7, %CurrentDateIn)), ' '), COALESCE(( SELECT A8.XLATLONGNAME FROM PSXLATITEM A8 WHERE A8.FIELDNAME = 'PER_ORG' AND A8.FIELDVALUE = A.PER_ORG AND A8.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM A81, A8, %CurrentDateIn)), ' '), COALESCE(( SELECT A12.DESCR FROM PS_ESTAB_TBL A12 WHERE A12.ESTABID = A.ESTABID AND A12.EFF_STATUS = 'A' AND %EffdtCheck(ESTAB_TBL A121, A12, %CurrentDateIn)), ' '), B.NAME_DISPLAY, COALESCE(CASE WHEN A.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 = A.ACTION AND ACT.EFF_STATUS = 'A' AND %EffdtCheck(ACTION_TBL ACT1, ACT, %CurrentDateIn)) END, ' '), COALESCE(( SELECT SA.DESCR FROM PS_SAL_PLAN_TBL SA WHERE SA.SETID = A.SETID_SALARY AND SA.SAL_ADMIN_PLAN = A.SAL_ADMIN_PLAN AND %EffdtCheck(SAL_PLAN_TBL SA1, SA, %CurrentDateIn) AND SA.EFF_STATUS <> 'I'), ' '), C.SUPERVISOR_ID, COALESCE(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = A.SUPERVISOR_ID), ' '), A.REPORTS_TO, COALESCE(( SELECT A6A.DESCR FROM PS_POSITION_DATA A6A WHERE A.REPORTS_TO = A6A.POSITION_NBR AND %EffdtCheck(POSITION_DATA A6A1, A6A, %CurrentDateIn)), ' '), COALESCE(( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = A.HR_STATUS AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)), ' ') , %DateNull , %DateNull FROM PS_JOB A , PS_PERSONAL_DATA B , PS_HR_DIRECT_REP_2 C , PSOPRDEFN OPR WHERE A.EMPLID = B.EMPLID AND C.DRILL_DOWN_FLAG = 'Y' AND '2' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'UX_DIRECTS_PLT' AND J.MARKET = 'GBL' ) AND A.EFFDT = ( SELECT MAX(JO.EFFDT) FROM PS_JOB JO WHERE JO.EMPLID=A.EMPLID AND JO.EMPL_RCD=A.EMPL_RCD AND JO.EFFDT <=%CurrentDateIn ) AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.JOB_EFFDT = A.EFFDT AND A.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = A.EMPLID AND J1.EMPL_RCD = A.EMPL_RCD AND J1.EFFDT = A.EFFDT) AND OPR.EMPLID = C.SUPERVISOR_ID UNION ALL SELECT OPR.OPRID , A.EMPLID , A.EMPL_RCD , A.EFFDT , CASE C.HR_DR_LEVEL WHEN 1 THEN ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR = 1000 AND M.MESSAGE_NBR = 30168) ELSE ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR = 1000 AND M.MESSAGE_NBR = 30162) END , COALESCE(( SELECT A3.DESCR FROM PS_DEPT_TBL A3 WHERE A.SETID_DEPT = A3.SETID AND A.DEPTID = A3.DEPTID AND %EffdtCheck(DEPT_TBL A31, A3, %CurrentDateIn)), ' '), COALESCE(( SELECT A4.DESCR FROM PS_JOBCODE_TBL A4 WHERE A.SETID_DEPT = A4.SETID AND A.JOBCODE = A4.JOBCODE AND %EffdtCheck(JOBCODE_TBL A41, A4, %CurrentDateIn)), ' '), COALESCE(( SELECT A5.DESCR FROM PS_LOCATION_TBL A5 WHERE A.SETID_LOCATION = A5.SETID AND A.LOCATION = A5.LOCATION AND %EffdtCheck(LOCATION_TBL A51, A5, %CurrentDateIn)), ' '), COALESCE(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE A.POSITION_NBR = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)), ' '), COALESCE(( SELECT A7.XLATLONGNAME FROM PSXLATITEM A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = A.FULL_PART_TIME AND A7.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM A71, A7, %CurrentDateIn)), ' '), COALESCE(( SELECT A8.XLATLONGNAME FROM PSXLATITEM A8 WHERE A8.FIELDNAME = 'PER_ORG' AND A8.FIELDVALUE = A.PER_ORG AND A8.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM A81, A8, %CurrentDateIn)), ' '), COALESCE(( SELECT A12.DESCR FROM PS_ESTAB_TBL A12 WHERE A12.ESTABID = A.ESTABID AND A12.EFF_STATUS = 'A' AND %EffdtCheck(ESTAB_TBL A121, A12, %CurrentDateIn)), ' '), B.NAME_DISPLAY, COALESCE(CASE WHEN A.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 = A.ACTION AND ACT.EFF_STATUS = 'A' AND %EffdtCheck(ACTION_TBL ACT1, ACT, %CurrentDateIn)) END, ' '), COALESCE(( SELECT SA.DESCR FROM PS_SAL_PLAN_TBL SA WHERE SA.SETID = A.SETID_SALARY AND SA.SAL_ADMIN_PLAN = A.SAL_ADMIN_PLAN AND %EffdtCheck(SAL_PLAN_TBL SA1, SA, %CurrentDateIn) AND SA.EFF_STATUS <> 'I'), ' '), C.SUPERVISOR_ID, COALESCE(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = A.SUPERVISOR_ID), ' '), A.REPORTS_TO, COALESCE(( SELECT A6A.DESCR FROM PS_POSITION_DATA A6A WHERE A.REPORTS_TO = A6A.POSITION_NBR AND %EffdtCheck(POSITION_DATA A6A1, A6A, %CurrentDateIn)), ' '), COALESCE(( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = A.HR_STATUS AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)), ' ') , %DateNull , %DateNull FROM PS_JOB A , PS_PERSONAL_DATA B , PS_HR_DIRECT_REP_3 C , PSOPRDEFN OPR WHERE A.EMPLID = B.EMPLID AND C.DRILL_DOWN_FLAG = 'Y' AND '3' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'UX_DIRECTS_PLT' AND J.MARKET = 'GBL' ) AND A.EFFDT = ( SELECT MAX(JO.EFFDT) FROM PS_JOB JO WHERE JO.EMPLID=A.EMPLID AND JO.EMPL_RCD=A.EMPL_RCD AND JO.EFFDT <=%CurrentDateIn ) AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.JOB_EFFDT = A.EFFDT AND A.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = A.EMPLID AND J1.EMPL_RCD = A.EMPL_RCD AND J1.EFFDT = A.EFFDT) AND OPR.EMPLID = C.SUPERVISOR_ID UNION ALL SELECT OPR.OPRID , A.EMPLID , A.EMPL_RCD , A.EFFDT , CASE C.HR_DR_LEVEL WHEN 1 THEN ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR = 1000 AND M.MESSAGE_NBR = 30168) ELSE ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR = 1000 AND M.MESSAGE_NBR = 30162) END , COALESCE(( SELECT A3.DESCR FROM PS_DEPT_TBL A3 WHERE A.SETID_DEPT = A3.SETID AND A.DEPTID = A3.DEPTID AND %EffdtCheck(DEPT_TBL A31, A3, %CurrentDateIn)), ' '), COALESCE(( SELECT A4.DESCR FROM PS_JOBCODE_TBL A4 WHERE A.SETID_DEPT = A4.SETID AND A.JOBCODE = A4.JOBCODE AND %EffdtCheck(JOBCODE_TBL A41, A4, %CurrentDateIn)), ' '), COALESCE(( SELECT A5.DESCR FROM PS_LOCATION_TBL A5 WHERE A.SETID_LOCATION = A5.SETID AND A.LOCATION = A5.LOCATION AND %EffdtCheck(LOCATION_TBL A51, A5, %CurrentDateIn)), ' '), COALESCE(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE A.POSITION_NBR = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)), ' '), COALESCE(( SELECT A7.XLATLONGNAME FROM PSXLATITEM A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = A.FULL_PART_TIME AND A7.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM A71, A7, %CurrentDateIn)), ' '), COALESCE(( SELECT A8.XLATLONGNAME FROM PSXLATITEM A8 WHERE A8.FIELDNAME = 'PER_ORG' AND A8.FIELDVALUE = A.PER_ORG AND A8.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM A81, A8, %CurrentDateIn)), ' '), COALESCE(( SELECT A12.DESCR FROM PS_ESTAB_TBL A12 WHERE A12.ESTABID = A.ESTABID AND A12.EFF_STATUS = 'A' AND %EffdtCheck(ESTAB_TBL A121, A12, %CurrentDateIn)), ' '), B.NAME_DISPLAY, COALESCE(CASE WHEN A.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 = A.ACTION AND ACT.EFF_STATUS = 'A' AND %EffdtCheck(ACTION_TBL ACT1, ACT, %CurrentDateIn)) END, ' '), COALESCE(( SELECT SA.DESCR FROM PS_SAL_PLAN_TBL SA WHERE SA.SETID = A.SETID_SALARY AND SA.SAL_ADMIN_PLAN = A.SAL_ADMIN_PLAN AND %EffdtCheck(SAL_PLAN_TBL SA1, SA, %CurrentDateIn) AND SA.EFF_STATUS <> 'I'), ' '), C.SUPERVISOR_ID, COALESCE(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = A.SUPERVISOR_ID), ' '), A.REPORTS_TO, COALESCE(( SELECT A6A.DESCR FROM PS_POSITION_DATA A6A WHERE A.REPORTS_TO = A6A.POSITION_NBR AND %EffdtCheck(POSITION_DATA A6A1, A6A, %CurrentDateIn)), ' '), COALESCE(( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = A.HR_STATUS AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)), ' ') , %DateNull , %DateNull FROM PS_JOB A , PS_PERSONAL_DATA B , PS_HR_DIRECT_REP_4 C , PSOPRDEFN OPR WHERE A.EMPLID = B.EMPLID AND C.DRILL_DOWN_FLAG = 'Y' AND '4' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'UX_DIRECTS_PLT' AND J.MARKET = 'GBL' ) AND A.EFFDT = ( SELECT MAX(JO.EFFDT) FROM PS_JOB JO WHERE JO.EMPLID=A.EMPLID AND JO.EMPL_RCD=A.EMPL_RCD AND JO.EFFDT <=%CurrentDateIn ) AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.JOB_EFFDT = A.EFFDT AND A.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = A.EMPLID AND J1.EMPL_RCD = A.EMPL_RCD AND J1.EFFDT = A.EFFDT) AND OPR.EMPLID = C.SUPERVISOR_ID UNION ALL SELECT OPR.OPRID , A.EMPLID , A.EMPL_RCD , A.EFFDT , CASE C.HR_DR_LEVEL WHEN 1 THEN ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR = 1000 AND M.MESSAGE_NBR = 30168) ELSE ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR = 1000 AND M.MESSAGE_NBR = 30162) END , COALESCE(( SELECT A3.DESCR FROM PS_DEPT_TBL A3 WHERE A.SETID_DEPT = A3.SETID AND A.DEPTID = A3.DEPTID AND %EffdtCheck(DEPT_TBL A31, A3, %CurrentDateIn)), ' '), COALESCE(( SELECT A4.DESCR FROM PS_JOBCODE_TBL A4 WHERE A.SETID_DEPT = A4.SETID AND A.JOBCODE = A4.JOBCODE AND %EffdtCheck(JOBCODE_TBL A41, A4, %CurrentDateIn)), ' '), COALESCE(( SELECT A5.DESCR FROM PS_LOCATION_TBL A5 WHERE A.SETID_LOCATION = A5.SETID AND A.LOCATION = A5.LOCATION AND %EffdtCheck(LOCATION_TBL A51, A5, %CurrentDateIn)), ' '), COALESCE(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE A.POSITION_NBR = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)), ' '), COALESCE(( SELECT A7.XLATLONGNAME FROM PSXLATITEM A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = A.FULL_PART_TIME AND A7.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM A71, A7, %CurrentDateIn)), ' '), COALESCE(( SELECT A8.XLATLONGNAME FROM PSXLATITEM A8 WHERE A8.FIELDNAME = 'PER_ORG' AND A8.FIELDVALUE = A.PER_ORG AND A8.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM A81, A8, %CurrentDateIn)), ' '), COALESCE(( SELECT A12.DESCR FROM PS_ESTAB_TBL A12 WHERE A12.ESTABID = A.ESTABID AND A12.EFF_STATUS = 'A' AND %EffdtCheck(ESTAB_TBL A121, A12, %CurrentDateIn)), ' '), B.NAME_DISPLAY, COALESCE(CASE WHEN A.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 = A.ACTION AND ACT.EFF_STATUS = 'A' AND %EffdtCheck(ACTION_TBL ACT1, ACT, %CurrentDateIn)) END, ' '), COALESCE(( SELECT SA.DESCR FROM PS_SAL_PLAN_TBL SA WHERE SA.SETID = A.SETID_SALARY AND SA.SAL_ADMIN_PLAN = A.SAL_ADMIN_PLAN AND %EffdtCheck(SAL_PLAN_TBL SA1, SA, %CurrentDateIn) AND SA.EFF_STATUS <> 'I'), ' '), C.SUPERVISOR_ID, COALESCE(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = A.SUPERVISOR_ID), ' '), A.REPORTS_TO, COALESCE(( SELECT A6A.DESCR FROM PS_POSITION_DATA A6A WHERE A.REPORTS_TO = A6A.POSITION_NBR AND %EffdtCheck(POSITION_DATA A6A1, A6A, %CurrentDateIn)), ' '), COALESCE(( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = A.HR_STATUS AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)), ' ') , %DateNull , %DateNull FROM PS_JOB A , PS_PERSONAL_DATA B , PS_HR_DIRECT_REP_5 C , PSOPRDEFN OPR WHERE A.EMPLID = B.EMPLID AND C.DRILL_DOWN_FLAG = 'Y' AND '5' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'UX_DIRECTS_PLT' AND J.MARKET = 'GBL' ) AND A.EFFDT = ( SELECT MAX(JO.EFFDT) FROM PS_JOB JO WHERE JO.EMPLID=A.EMPLID AND JO.EMPL_RCD=A.EMPL_RCD AND JO.EFFDT <=%CurrentDateIn ) AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.JOB_EFFDT = A.EFFDT AND A.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = A.EMPLID AND J1.EMPL_RCD = A.EMPL_RCD AND J1.EFFDT = A.EFFDT) AND OPR.EMPLID = C.SUPERVISOR_ID UNION ALL SELECT OPR.OPRID , A.EMPLID , A.EMPL_RCD , A.EFFDT ,CASE C.HR_DR_LEVEL WHEN 1 THEN ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR = 1000 AND M.MESSAGE_NBR = 30168) ELSE ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR = 1000 AND M.MESSAGE_NBR = 30162) END , COALESCE(( SELECT A3.DESCR FROM PS_DEPT_TBL A3 WHERE A.SETID_DEPT = A3.SETID AND A.DEPTID = A3.DEPTID AND %EffdtCheck(DEPT_TB L A31, A3, %CurrentDateIn)), ' '), COALESCE(( SELECT A4.DESCR FROM PS_JOBCODE_TBL A4 WHERE A.SETID_DEPT = A4.SETID AND A.JOBCODE = A4.JOBCODE AND %EffdtCheck(JOBCODE_TBL A41, A4, %CurrentDateIn)), ' '), COALESCE(( SELECT A5.DESCR FROM PS_LOCATION_TBL A5 WHERE A.SETID_LOCATION = A5.SETID AND A.LOCATION = A5.LOCATION AND %EffdtCheck(LOCATION_TBL A51, A5, %CurrentDateIn)), ' '), COALESCE(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE A.POSITION_NBR = A6.POSITION_NBR AND %EffdtCheck(POSITION_DATA A61, A6, %CurrentDateIn)), ' '), COALESCE(( SELECT A7.XLATLONGNAME FROM PSXLATITEM A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = A.FULL_PART_TIME AND A7.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM A71, A7, %CurrentDateIn)), ' '), COALESCE(( SELECT A8.XLATLONGNAME FROM PSXLATITEM A8 WHERE A8.FIELDNAME = 'PER_ORG' AND A8.FIELDVALUE = A.PER_ORG AND A8.EFF_STATUS = 'A' AND %EffdtCheck(PSXLATITEM A81, A8, %CurrentDateIn)), ' '), COALESCE(( SELECT A12.DESCR FROM PS_ESTAB_TBL A12 WHERE A12.ESTABID = A.ESTABID AND A12.EFF_STATUS = 'A' AND %EffdtCheck(ESTAB_TBL A121, A12, %CurrentDateIn)), ' '), B.NAME_DISPLAY, COALESCE(CASE WHEN A.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 = A.ACTION AND ACT.EFF_STATUS = 'A' AND %EffdtCheck(ACTION_TBL ACT1, ACT, %CurrentDateIn)) END, ' '), COALESCE(( SELECT SA.DESCR FROM PS_SAL_PLAN_TBL SA WHERE SA.SETID = A.SETID_SALARY AND SA.SAL_ADMIN_PLAN = A.SAL_ADMIN_PLAN AND %EffdtCheck(SAL_PLAN_TBL SA1, SA, %CurrentDateIn) AND SA.EFF_STATUS <> 'I'), ' '), C.SUPERVISOR_ID, COALESCE(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = A.SUPERVISOR_ID), ' '), A.REPORTS_TO, COALESCE(( SELECT A6A.DESCR FROM PS_POSITION_DATA A6A WHERE A.REPORTS_TO = A6A.POSITION_NBR AND %EffdtCheck(POSITION_DATA A6A1, A6A, %CurrentDateIn)), ' '), COALESCE(( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = A.HR_STATUS AND %EffdtCheck(PSXLATITEM X1, X, %CurrentDateIn)), ' ') , %DateNull , %DateNull FROM PS_JOB A , PS_PERSONAL_DATA B , PS_HR_DIRECT_REP_6 C , PSOPRDEFN OPR WHERE A.EMPLID = B.EMPLID AND C.DRILL_DOWN_FLAG = 'Y' AND '6' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'UX_DIRECTS_PLT' AND J.MARKET = 'GBL' ) AND A.EFFDT = ( SELECT MAX(JO.EFFDT) FROM PS_JOB JO WHERE JO.EMPLID=A.EMPLID AND JO.EMPL_RCD=A.EMPL_RCD AND JO.EFFDT <=%CurrentDateIn ) AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.JOB_EFFDT = A.EFFDT AND A.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = A.EMPLID AND J1.EMPL_RCD = A.EMPL_RCD AND J1.EFFDT = A.EFFDT) AND OPR.EMPLID = C.SUPERVISOR_ID

  • Related Language Record: HR_PG_CURR_LNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    4 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    5 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.
    6 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    7 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
    8 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    9 POSN_DESCR Character(30) VARCHAR2(30) NOT NULL Position Description
    10 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    11 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    12 ESTAB_DESCR Character(30) VARCHAR2(30) NOT NULL Description
    13 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    14 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
    15 DESCR50 Character(50) VARCHAR2(50) NOT NULL Description of length 50
    16 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
    17 SUPERVISOR_NAME Character(50) VARCHAR2(50) NOT NULL Supervisor Name
    18 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number
    19 NAME2 Character(40) VARCHAR2(40) NOT NULL Name 2
    20 DESCR50_1 Character(50) VARCHAR2(50) NOT NULL Description of length 50
    21 FROM_DT Date(10) DATE From Date
    22 TO_DT Date(10) DATE To Date