HR_PG_MVMT_TMP

(SQL View)
 Index Back

Temp View for HC Movement- Mgr


SELECT JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , JOB.EFFSEQ , '0' , JOB.COMPANY , %Coalesce(( SELECT CMPNY.DESCR FROM PS_COMPANY_TBL CMPNY WHERE JOB.COMPANY = CMPNY.COMPANY AND CMPNY.EFF_STATUS = 'A' AND CMPNY.EFFDT = ( SELECT MAX(CMPNY1.EFFDT) FROM PS_COMPANY_TBL CMPNY1 WHERE CMPNY1.COMPANY = CMPNY.COMPANY AND CMPNY1.EFF_STATUS = CMPNY.EFF_STATUS AND CMPNY1.EFFDT <= (JOB.EFFDT))),' ') , JOB.BUSINESS_UNIT , %Coalesce(( SELECT BU.DESCR FROM PS_BUS_UNIT_TBL_HR BU WHERE JOB.BUSINESS_UNIT = BU.BUSINESS_UNIT),' ') , JOB.DEPTID , %Coalesce(( SELECT DEPT.DESCR FROM PS_DEPT_TBL DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND DEPT.EFFDT = ( SELECT MAX(DEPT1.EFFDT) FROM PS_DEPT_TBL DEPT1 WHERE DEPT.SETID = DEPT1.SETID AND DEPT.DEPTID = DEPT1.DEPTID AND DEPT1.EFFDT <= %CURRENTDATEIN)),' ') , JOB.JOBCODE , %Coalesce(( SELECT JCODE.DESCR FROM PS_JOBCODE_TBL JCODE WHERE JOB.SETID_DEPT = JCODE.SETID AND JOB.JOBCODE = JCODE.JOBCODE AND JCODE.EFFDT = ( SELECT MAX(JCODE1.EFFDT) FROM PS_JOBCODE_TBL JCODE1 WHERE JCODE.SETID = JCODE1.SETID AND JCODE.JOBCODE = JCODE1.JOBCODE AND JCODE1.EFFDT <= %CURRENTDATEIN)),' ') , JOB.LOCATION , %Coalesce(( SELECT LOCN.DESCR FROM PS_LOCATION_TBL LOCN WHERE JOB.SETID_DEPT = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND LOCN.EFFDT = ( SELECT MAX(LOCN1.EFFDT) FROM PS_LOCATION_TBL LOCN1 WHERE LOCN.SETID = LOCN1.SETID AND LOCN.LOCATION = LOCN1.LOCATION AND LOCN.EFFDT <= %CURRENTDATEIN)),' ') , JOB.POSITION_NBR , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE JOB.POSITION_NBR = A6.POSITION_NBR AND A6.EFFDT = ( SELECT MAX(A61.EFFDT) FROM PS_POSITION_DATA A61 WHERE A6.POSITION_NBR = A61.POSITION_NBR AND A61.EFFDT <= %CURRENTDATEIN)),' ') , JOB.FULL_PART_TIME , ( 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 A7.EFFDT = ( SELECT MAX(A71.EFFDT) FROM PSXLATITEM A71 WHERE A71.FIELDNAME = A7.FIELDNAME AND A71.FIELDVALUE = A7.FIELDVALUE AND A71.EFF_STATUS=A7.EFF_STATUS AND A71.EFFDT <= %CurrentDateIn) ) , JOB.PER_ORG , ( SELECT A8.XLATLONGNAME FROM PSXLATITEM A8 WHERE A8.FIELDNAME = 'PER_ORG' AND A8.FIELDVALUE = JOB.PER_ORG AND A8.EFF_STATUS = 'A' AND A8.EFFDT = ( SELECT MAX(A81.EFFDT) FROM PSXLATITEM A81 WHERE A81.FIELDNAME = A8.FIELDNAME AND A81.FIELDVALUE = A8.FIELDVALUE AND A81.EFF_STATUS=A8.EFF_STATUS AND A81.EFFDT <= %CurrentDateIn) ) , JOB.SAL_ADMIN_PLAN , JOB.REG_REGION , JOB.ESTABID , %Coalesce(( SELECT A12.DESCR FROM PS_ESTAB_TBL A12 WHERE A12.ESTABID = JOB.ESTABID AND A12.EFF_STATUS = 'A' AND A12.EFFDT = ( SELECT MAX(A121.EFFDT) FROM PS_ESTAB_TBL A121 WHERE A121.ESTABID = A12.ESTABID AND A121.EFF_STATUS=A12.EFF_STATUS AND A121.EFFDT <= %CURRENTDATEIN)),' '), C.NAME_DISPLAY , JOB.ACTION , 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 ACT.EFFDT = ( SELECT MAX(ACT1.EFFDT) FROM PS_ACTION_TBL ACT1 WHERE ACT.ACTION = ACT1.ACTION AND ACT.EFF_STATUS = ACT1.EFF_STATUS AND ACT1.EFFDT <= %CurrentDateIn)) END ,%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 SA.EFFDT = ( SELECT MAX(SA1.EFFDT) FROM PS_SAL_PLAN_TBL SA1 WHERE SA.SETID = SA1.SETID AND SA.SAL_ADMIN_PLAN = SA1.SAL_ADMIN_PLAN AND SA.EFF_STATUS = SA1.EFF_STATUS AND SA1.EFFDT <= (JOB.EFFDT)) AND SA.EFF_STATUS<>'I'),' ') , %Coalesce(( SELECT RR.DESCR50 FROM PS_REG_REGION_TBL RR WHERE RR.REG_REGION = JOB.REG_REGION),' '),JOB.SUPERVISOR_ID , %Coalesce(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = JOB.SUPERVISOR_ID),' ') ,JOB.REPORTS_TO , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND A6.EFFDT = ( SELECT MAX(A61.EFFDT) FROM PS_POSITION_DATA A61 WHERE A6.POSITION_NBR = A61.POSITION_NBR AND A61.EFFDT <= %CURRENTDATEIN)),' ') ,JOB.HR_STATUS ,( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND X.EFFDT = ( SELECT MAX(X1.EFFDT) FROM PSXLATITEM X1 WHERE X1.FIELDNAME = X.FIELDNAME AND X1.FIELDVALUE = X.FIELDVALUE AND X1.EFFDT <= %CurrentDateIn)),%DateNull ,%DateNull ,( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30168) FROM PS_JOB JOB , PS_PERSONAL_DATA C WHERE JOB.EMPLID = C.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 , JOB.EFFSEQ , '1' , JOB.COMPANY , %Coalesce(( SELECT AA1.DESCR FROM PS_COMPANY_TBL AA1 WHERE JOB.COMPANY = AA1.COMPANY AND AA1.EFF_STATUS = 'A' AND AA1.EFFDT = ( SELECT MAX(AA11.EFFDT) FROM PS_COMPANY_TBL AA11 WHERE AA11.COMPANY = AA1.COMPANY AND AA11.EFF_STATUS = AA1.EFF_STATUS AND AA11.EFFDT <= (JOB.EFFDT))),' ') , JOB.BUSINESS_UNIT ,%Coalesce(( SELECT BU.DESCR FROM PS_BUS_UNIT_TBL_HR BU WHERE BU.BUSINESS_UNIT = JOB.BUSINESS_UNIT),' ') , JOB.DEPTID , %Coalesce(( SELECT AA3.DESCR FROM PS_DEPT_TBL AA3 WHERE JOB.SETID_DEPT = AA3.SETID AND JOB.DEPTID = AA3.DEPTID AND AA3.EFFDT = ( SELECT MAX(AA31.EFFDT) FROM PS_DEPT_TBL AA31 WHERE AA3.SETID = AA31.SETID AND AA3.DEPTID = AA31.DEPTID AND AA31.EFFDT <= %CURRENTDATEIN)),' ') , JOB.JOBCODE , %Coalesce(( SELECT AA4.DESCR FROM PS_JOBCODE_TBL AA4 WHERE JOB.SETID_DEPT = AA4.SETID AND JOB.JOBCODE = AA4.JOBCODE AND AA4.EFFDT = ( SELECT MAX(AA41.EFFDT) FROM PS_JOBCODE_TBL AA41 WHERE AA4.SETID = AA41.SETID AND AA4.JOBCODE = AA41.JOBCODE AND AA41.EFFDT <= %CURRENTDATEIN)),' ') , JOB.LOCATION , %Coalesce(( SELECT AA5.DESCR FROM PS_LOCATION_TBL AA5 WHERE JOB.SETID_DEPT = AA5.SETID AND JOB.LOCATION = AA5.LOCATION AND AA5.EFFDT = ( SELECT MAX(AA51.EFFDT) FROM PS_LOCATION_TBL AA51 WHERE AA5.SETID = AA51.SETID AND AA5.LOCATION = AA51.LOCATION AND AA51.EFFDT <= %CURRENTDATEIN)),' ') , JOB.POSITION_NBR , %Coalesce(( SELECT AA6.DESCR FROM PS_POSITION_DATA AA6 WHERE JOB.POSITION_NBR = AA6.POSITION_NBR AND AA6.EFFDT = ( SELECT MAX(AA61.EFFDT) FROM PS_POSITION_DATA AA61 WHERE AA6.POSITION_NBR = AA61.POSITION_NBR AND AA61.EFFDT<=%CURRENTDATEIN)),' ') , JOB.FULL_PART_TIME , ( SELECT AA7.XLATLONGNAME FROM PSXLATITEM AA7 WHERE AA7.FIELDNAME = 'FULL_PART_TIME' AND AA7.FIELDVALUE = JOB.FULL_PART_TIME AND AA7.EFF_STATUS = 'A' AND AA7.EFFDT = ( SELECT MAX(AA71.EFFDT) FROM PSXLATITEM AA71 WHERE AA71.FIELDNAME = AA7.FIELDNAME AND AA71.FIELDVALUE = AA7.FIELDVALUE AND AA71.EFF_STATUS = AA7.EFF_STATUS AND AA71.EFFDT <= %CurrentDateIn) ) , JOB.PER_ORG , ( SELECT AA8.XLATLONGNAME FROM PSXLATITEM AA8 WHERE AA8.FIELDNAME = 'PER_ORG' AND AA8.FIELDVALUE = JOB.PER_ORG AND AA8.EFF_STATUS = 'A' AND AA8.EFFDT = ( SELECT MAX(AA81.EFFDT) FROM PSXLATITEM AA81 WHERE AA81.FIELDNAME = AA8.FIELDNAME AND AA81.FIELDVALUE = AA8.FIELDVALUE AND AA81.EFF_STATUS = AA8.EFF_STATUS AND AA81.EFFDT <= %CurrentDateIn) ) , JOB.SAL_ADMIN_PLAN , JOB.REG_REGION , JOB.ESTABID , %Coalesce(( SELECT AA12.DESCR FROM PS_ESTAB_TBL AA12 WHERE AA12.ESTABID = JOB.ESTABID AND AA12.EFF_STATUS = 'A' AND AA12.EFFDT = ( SELECT MAX(AA121.EFFDT) FROM PS_ESTAB_TBL AA121 WHERE AA121.ESTABID = AA12.ESTABID AND AA121.EFF_STATUS = AA12.EFF_STATUS AND AA121.EFFDT <= %CURRENTDATEIN)),' ') , CC.NAME_DISPLAY , JOB.ACTION , ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30163) ,%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 SA.EFFDT = ( SELECT MAX(SA1.EFFDT) FROM PS_SAL_PLAN_TBL SA1 WHERE SA.SETID = SA1.SETID AND SA.SAL_ADMIN_PLAN = SA1.SAL_ADMIN_PLAN AND SA.EFF_STATUS = SA1.EFF_STATUS AND SA1.EFFDT <= (JOB.EFFDT)) AND SA.EFF_STATUS<>'I'), ' ') , %Coalesce(( SELECT RR.DESCR50 FROM PS_REG_REGION_TBL RR WHERE RR.REG_REGION = JOB.REG_REGION),' '), JOB.SUPERVISOR_ID , %Coalesce(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = JOB.SUPERVISOR_ID),' ') ,JOB.REPORTS_TO ,%Coalesce(( SELECT AA6.DESCR FROM PS_POSITION_DATA AA6 WHERE JOB.REPORTS_TO = AA6.POSITION_NBR AND AA6.EFFDT = ( SELECT MAX(AA61.EFFDT) FROM PS_POSITION_DATA AA61 WHERE AA6.POSITION_NBR = AA61.POSITION_NBR AND AA61.EFFDT<=%CURRENTDATEIN)),' '), JOB.HR_STATUS ,( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND X.EFFDT = ( SELECT MAX(X1.EFFDT) FROM PSXLATITEM X1 WHERE X1.FIELDNAME = X.FIELDNAME AND X1.FIELDVALUE = X.FIELDVALUE AND X1.EFFDT <= %CurrentDateIn)),%DateNull ,%DateNull ,( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30168) FROM PS_JOB JOB , PS_HR_PG_XFR_IN BB , PS_PERSONAL_DATA CC WHERE JOB.EMPLID = BB.EMPLID AND JOB.EMPL_RCD = BB.EMPL_RCD AND JOB.EFFSEQ = BB.EFFSEQ 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 ) AND CC.EMPLID = JOB.EMPLID

# 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 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
5 DIRVALUEFLAG Character(1) VARCHAR2(1) NOT NULL Directory Value?
6 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
7 COMPANY_DESCR Character(30) VARCHAR2(30) NOT NULL Company Descr
8 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
9 BUSINESS_DESCR Character(60) VARCHAR2(60) NOT NULL Business Description
10 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
11 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
12 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
13 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
14 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
15 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
16 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
17 POSN_DESCR Character(30) VARCHAR2(30) NOT NULL Position Description
18 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
D=On Demand
F=Full-Time
P=Part-Time
19 DESCR Character(30) VARCHAR2(30) NOT NULL Description
20 PER_ORG Character(3) VARCHAR2(3) NOT NULL Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
CWR=Contingent Worker
EMP=Employee
POI=Person of Interest
21 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
22 SAL_ADMIN_PLAN Character(4) VARCHAR2(4) NOT NULL Salary Administration Plan
23 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region
24 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID
25 ESTAB_DESCR Character(30) VARCHAR2(30) NOT NULL Description
26 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
27 ACTION Character(3) VARCHAR2(3) NOT NULL Action
28 DESCR100_2 Character(100) VARCHAR2(100) NOT NULL Description
29 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
30 DESCR50 Character(50) VARCHAR2(50) NOT NULL Description of length 50
31 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
32 SUPERVISOR_NAME Character(50) VARCHAR2(50) NOT NULL Supervisor Name
33 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number
34 NAME2 Character(40) VARCHAR2(40) NOT NULL Name 2
35 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
A=Active
I=Inactive
36 DESCR50_1 Character(50) VARCHAR2(50) NOT NULL Description of length 50
37 FROM_DT Date(10) DATE From Date
38 TO_DT Date(10) DATE To Date
39 DESCR25 Character(25) VARCHAR2(25) NOT NULL Short description