HR_TM_CURR_TMP

(SQL View)
 Index Back

Temp View for Current HC - Mgr


SELECT A.EMPLID , A.EMPL_RCD , A.EFFDT , A.EFFSEQ , A.COMPANY , %Coalesce(( SELECT A1.DESCR FROM PS_COMPANY_TBL A1 WHERE A.COMPANY = A1.COMPANY AND A1.EFF_STATUS = 'A' AND A1.EFFDT = ( SELECT MAX(A11.EFFDT) FROM PS_COMPANY_TBL A11 WHERE A11.COMPANY = A1.COMPANY AND A11.EFF_STATUS=A1.EFF_STATUS AND A11.EFFDT <= (A.EFFDT))),' ') , A.BUSINESS_UNIT ,%Coalesce(( SELECT A2.DESCR FROM PS_BUS_UNIT_TBL_HR A2 WHERE A.BUSINESS_UNIT = A2.BUSINESS_UNIT),' ') , A.DEPTID , %Coalesce(( SELECT A3.DESCR FROM PS_DEPT_TBL A3 WHERE A.SETID_DEPT = A3.SETID AND A.DEPTID = A3.DEPTID AND A3.EFFDT = ( SELECT MAX(A31.EFFDT) FROM PS_DEPT_TBL A31 WHERE A3.SETID = A31.SETID AND A3.DEPTID = A31.DEPTID AND A31.EFFDT <= %CurrentDateIn)),' ') , A.JOBCODE , %Coalesce(( SELECT A4.DESCR FROM PS_JOBCODE_TBL A4 WHERE A.SETID_DEPT = A4.SETID AND A.JOBCODE = A4.JOBCODE AND A4.EFFDT = ( SELECT MAX(A41.EFFDT) FROM PS_JOBCODE_TBL A41 WHERE A4.SETID = A41.SETID AND A4.JOBCODE = A41.JOBCODE AND A41.EFFDT <= %CurrentDateIn)),' ') , A.LOCATION , %Coalesce(( SELECT A5.DESCR FROM PS_LOCATION_TBL A5 WHERE A.SETID_DEPT = A5.SETID AND A.LOCATION = A5.LOCATION AND A5.EFFDT = ( SELECT MAX(A51.EFFDT) FROM PS_LOCATION_TBL A51 WHERE A5.SETID = A51.SETID AND A5.LOCATION = A51.LOCATION AND A51.EFFDT <= %CurrentDateIn)),' ') , A.POSITION_NBR , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE A.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)),' ') ,A.FULL_PART_TIME , ( 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 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) ) , A.PER_ORG , ( SELECT A8.XLATLONGNAME FROM PSXLATITEM A8 WHERE A8.FIELDNAME = 'PER_ORG' AND A8.FIELDVALUE = A.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) ) , A.SAL_ADMIN_PLAN , A.REG_REGION , A.ESTABID , %Coalesce(( SELECT A12.DESCR FROM PS_ESTAB_TBL A12 WHERE A12.ESTABID = A.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)),' '), B.NAME_DISPLAY , A.ACTION , 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 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 = A.SETID_SALARY AND SA.SAL_ADMIN_PLAN = A.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 <= (A.EFFDT)) AND SA.EFF_STATUS<>'I'),' ') , %Coalesce(( SELECT RR.DESCR50 FROM PS_REG_REGION_TBL RR WHERE RR.REG_REGION = A.REG_REGION),' '),A.SUPERVISOR_ID , %Coalesce(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = A.SUPERVISOR_ID),' '),A.REPORTS_TO , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE A.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)),' '),A.HR_STATUS ,( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = A.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 A , PS_PERSONAL_DATA B WHERE A.EMPLID = B.EMPLID AND A.EFFDT = ( SELECT MAX(X.EFFDT) FROM PS_JOB X WHERE X.EMPLID = A.EMPLID AND X.EMPL_RCD = A.EMPL_RCD AND X.EFFDT <= %CurrentDateIn)

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