HR_MSSCT_DTL_VW

(SQL View)
 Index Back

HTML Email - GSS

Contains HTML Email Approval new information for the GSS transactions.

SELECT DISTINCT A.EMPLID , A.EMPL_RCD , A.ACTION_DT_SS , A.EFFSEQ , A.WF_STATUS , A.ACTION , A.ACTION_REASON_SS , A.ORIGINATORID , EMP.NAME , REQ.NAME , A.POSITION_NBR , A.BUSINESS_UNIT , A.DEPTID , A.LOCATION , A.POSITION_FLAG , A.JOBCODE , CASE WHEN (A.POSITION_NBR<>' ' AND A.POSITION_NBR<>B.POSITION_NBR) THEN ' ' ELSE A.SUPERVISOR_ID END , CASE WHEN (A.POSITION_NBR <> ' ') THEN A.REPORTS_TO ELSE ' ' END , CASE WHEN (A.POSITION_NBR <> ' ') THEN POSN.FULL_PART_TIME ELSE B.FULL_PART_TIME END , CASE WHEN (A.POSITION_NBR <> ' ') THEN POSN.STD_HOURS ELSE JC.STD_HOURS END , SUP.NAME_DISPLAY , A.COMPRATE , A.ANNUAL_RT , A.LAST_DATE_WORKED , A.EXPECTED_RETURN_DT , B.POSITION_NBR , B.BUSINESS_UNIT , B.DEPTID , B.LOCATION , B.JOBCODE , B.SUPERVISOR_ID , B.REPORTS_TO , B.FULL_PART_TIME , B.STD_HOURS , SUPCUR.NAME_DISPLAY , B.COMPRATE , B.ANNUAL_RT , %Sql(HR_HT_EMAIL_DATE,A.LAST_DATE_WORKED) , %Sql(HR_HT_EMAIL_DATE,A.EXPECTED_RETURN_DT) , %Sql(HR_HT_EMAIL_DATE,A.ACTION_DT_SS) , RSN.DESCR , WF.XLATLONGNAME , POSN_CUR.DESCR , POSN.DESCR , BULOC_CUR.DESCR , BULOC.DESCR , DEPT_CUR.DESCR , DEPT.DESCR , BULOC_CUR.DESCR2 , BULOC.DESCR2 , JC_CUR.DESCR , JC.DESCR , FT_CUR.XLATLONGNAME , POSN.XLATLONGNAME , POSN_RPT_CUR.DESCR , POSN_RPT.DESCR , ' ' , ' ' , CAST(STA.COMMENTS AS VARCHAR(4000)) , 'X' ,BULOC.DESCR %Concat ' - ' %Concat A.BUSINESS_UNIT ,BULOC_CUR.DESCR %Concat ' - ' %Concat B.BUSINESS_UNIT ,DEPT.DESCR %Concat ' - ' %Concat A.DEPTID ,DEPT_CUR.DESCR %Concat ' - ' %Concat B.DEPTID ,JC.DESCR %Concat ' - ' %Concat A.JOBCODE ,JC_CUR.DESCR %Concat ' - ' %Concat B.JOBCODE ,BULOC.DESCR2 %Concat ' - ' %Concat A.LOCATION ,BULOC_CUR.DESCR2 %Concat ' - ' %Concat B.LOCATION ,POSN.DESCR %Concat ' - ' %Concat A.POSITION_NBR ,POSN_CUR.DESCR %Concat ' - ' %Concat B.POSITION_NBR ,POSN_RPT.DESCR %Concat ' - ' %Concat A.REPORTS_TO ,POSN_RPT_CUR.DESCR %Concat ' - ' %Concat B.REPORTS_TO ,SUP.NAME_DISPLAY %Concat ' - ' %Concat A.SUPERVISOR_ID ,SUPCUR.NAME_DISPLAY %Concat ' - ' %Concat B.SUPERVISOR_ID ,B.CURRENCY_CD , AC.ACTION_DESCR FROM PS_HR_MSS_CT_DAT A LEFT OUTER JOIN PS_HR_PRTR_POSN_VW POSN ON A.POSITION_NBR = POSN.POSITION_NBR LEFT OUTER JOIN PS_HR_SUPERVISR_VW SUP ON A.SUPERVISOR_ID = SUP.EMPLID LEFT OUTER JOIN PS_HR_PRTR_POSN_VW POSN_RPT ON A.REPORTS_TO = POSN_RPT.POSITION_NBR LEFT OUTER JOIN PS_ACTN_REASON_VW RSN ON RSN.ACTION =A.ACTION AND RSN.ACTION_REASON =A.ACTION_REASON_SS AND RSN.EFF_STATUS = 'A' , PS_PERSON_NAME EMP , PS_PERSON_NAME REQ , PSOPRDEFN OPR , PS_HR_XLAT_EFF_VW WF ,PS_HR_MSS_CT_STA STA , PS_JOB B LEFT OUTER JOIN PS_HR_PRTR_POSN_VW POSN_CUR ON B.POSITION_NBR = POSN_CUR.POSITION_NBR LEFT OUTER JOIN PS_HR_XLAT_EFF_VW FT_CUR ON FT_CUR.FIELDNAME='FULL_PART_TIME' AND B.FULL_PART_TIME = FT_CUR.FIELDVALUE LEFT OUTER JOIN PS_HR_SUPERVISR_VW SUPCUR ON B.SUPERVISOR_ID = SUPCUR.EMPLID LEFT OUTER JOIN PS_HR_PRTR_POSN_VW POSN_RPT_CUR ON B.REPORTS_TO = POSN_RPT_CUR.POSITION_NBR ,PS_HR_PRTR_BULOC_V BULOC , PS_BU_DEPT_VW DEPT ,PS_BU_JOBCD_VW JC ,PS_HR_PRTR_BULOC_V BULOC_CUR , PS_BU_DEPT_VW DEPT_CUR ,PS_BU_JOBCD_VW JC_CUR , PS_ACTION_TBL AC WHERE A.EMPLID = EMP.EMPLID AND A.ORIGINATORID = OPR.OPRID AND OPR.EMPLID =REQ.EMPLID AND AC.ACTION =A.ACTION AND AC.EFF_STATUS = 'A' AND %EffdtCheck(ACTION_TBL AC1, AC, %Currentdatein) AND WF.FIELDNAME = 'WF_STATUS' AND WF.FIELDVALUE = A.WF_STATUS AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_JOB B1 WHERE B.EMPLID = B1.EMPLID AND B.EMPL_RCD = B1.EMPL_RCD AND B1.EFFDT=B1.EFFDT AND B1.EFFDT <= %CurrentDateIn) AND B.EFFSEQ = ( SELECT MAX(B2.EFFSEQ) FROM PS_JOB B2 WHERE B2.EMPLID = B.EMPLID AND B2.EFFDT = B.EFFDT) AND A.BUSINESS_UNIT = BULOC.BUSINESS_UNIT AND A.LOCATION=BULOC.LOCATION AND DEPT.BUSINESS_UNIT =A.BUSINESS_UNIT AND DEPT.DEPTID =A.DEPTID AND %EffdtCheck(BU_DEPT_VW DEPT1, DEPT, %Currentdatein) AND JC.BUSINESS_UNIT_JOBC =A.BUSINESS_UNIT AND JC.JOBCODE =A.JOBCODE AND %EffdtCheck(BU_JOBCD_VW JC1, JC, %Currentdatein) AND BULOC_CUR.BUSINESS_UNIT =B.BUSINESS_UNIT AND BULOC_CUR.LOCATION =B.LOCATION AND DEPT_CUR.BUSINESS_UNIT = B.BUSINESS_UNIT AND DEPT_CUR.DEPTID =B.DEPTID AND %EffdtCheck(BU_DEPT_VW DEPT_CUR1, DEPT_CUR, %Currentdatein) AND JC_CUR.BUSINESS_UNIT_JOBC =B.BUSINESS_UNIT AND JC_CUR.JOBCODE=B.JOBCODE AND %EffdtCheck(BU_JOBCD_VW JC_CUR1, JC_CUR, %Currentdatein) AND A.EMPLID = STA.EMPLID AND A.EMPL_RCD = STA.EMPL_RCD AND A.ACTION_DT_SS = STA.ACTION_DT_SS AND A.EFFSEQ = STA.EFFSEQ AND STA.SEQNO=1

# 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 ACTION_DT_SS Date(10) DATE Termination Date
4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
5 WF_STATUS Character(1) VARCHAR2(1) NOT NULL Workflow Status
A=Approved
C=Cancelled
D=Denied
E=Error. Contact Administrator.
F=Awaiting final approval
I=In Approval Process
M=Administrator is Processing
N=Not Available
P=Rework
S=Submitted
V=Data Saved
6 ACTION Character(3) VARCHAR2(3) NOT NULL Action

Default Value: PRO

Prompt Table: ACTION_TBL

7 ACTION_REASON_SS Character(3) VARCHAR2(3) NOT NULL Action Reason field for use on Self Service panels.

Prompt Table: ACTN_REASON_TBL
Set Control Field: ACTION

8 ORIGINATORID Character(30) VARCHAR2(30) NOT NULL Originator Identifier
9 EMPLNAME Character(50) VARCHAR2(50) NOT NULL EmplName
10 NAME_DESCR Character(50) VARCHAR2(50) NOT NULL Description
11 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number

Prompt Table: POSITION_BY_BUS

12 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: BUS_UNIT_TBL_HR

13 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPT_TBL

14 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

Prompt Table: LOCATION_TBL

15 POSITION_FLAG Character(1) VARCHAR2(1) NOT NULL Position Flag
N=No
Y=Yes
16 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

Prompt Table: JOBCODE_TBL

17 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID

Prompt Table: HR_ACTIVE_DVW

18 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number
19 FULL_PART_TIME_NEW Character(1) VARCHAR2(1) NOT NULL New Full/Part Time Status
D=On Demand
F=Full-Time
P=Part-Time
20 STD_HOURS_NEW Number(7,2) DECIMAL(6,2) NOT NULL *New Standard Hours
21 SUPERVISOR_NAME Character(50) VARCHAR2(50) NOT NULL Supervisor Name
22 COMPRATE Number(19,6) DECIMAL(18,6) NOT NULL Compensation Rate
23 ANNUAL_RT Number(19,3) DECIMAL(18,3) NOT NULL Annual Rate
24 LAST_DATE_WORKED Date(10) DATE Last Date Worked
25 EXPECTED_RETURN_DT Date(10) DATE Expected Return Date
26 POSITION_NBR2 Character(8) VARCHAR2(8) NOT NULL Position Number 2
27 BUSINESS_UNIT2 Character(5) VARCHAR2(5) NOT NULL Business Unit
28 DEPTID2 Character(10) VARCHAR2(10) NOT NULL Department
29 LOCATION2 Character(10) VARCHAR2(10) NOT NULL Location 2
30 JOBCODE2 Character(6) VARCHAR2(6) NOT NULL Jobcode 2
31 SUPERVISOR_ID2 Character(11) VARCHAR2(11) NOT NULL Designates EMPLID of Supervisor of employee.
32 REPORTS_TO2 Character(8) VARCHAR2(8) NOT NULL Designates EMPLID of Reports To position of employee.
33 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
D=On Demand
F=Full-Time
P=Part-Time
34 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
35 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
36 COMPRATE2 Number(22,6) DECIMAL(21,6) NOT NULL Comp Rate field for discounts and perks eligibility file.
37 ANNUAL_RT2 Number(19,3) DECIMAL(18,3) NOT NULL Annual Rate
38 DESCR25 Character(25) VARCHAR2(25) NOT NULL Short description
39 DESCR30 Character(30) VARCHAR2(30) NOT NULL Description
40 DESCR20 Character(20) VARCHAR2(20) NOT NULL Description
41 DESCR Character(30) VARCHAR2(30) NOT NULL Description
42 XLATLONGNAME Character(30) VARCHAR2(30) NOT NULL Translate Long Name
43 DESCR_HCM1 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
44 DESCR_HCM1A Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
45 DESCR_HCM2 Character(30) VARCHAR2(30) NOT NULL This field is used display description
46 DESCR_HCM2A Character(30) VARCHAR2(30) NOT NULL This field is used display description
47 DESCR_HCM3 Character(30) VARCHAR2(30) NOT NULL This field is used display description
48 DESCR_HCM3A Character(30) VARCHAR2(30) NOT NULL This field is used display description
49 DESCR_HCM4 Character(30) VARCHAR2(30) NOT NULL This field is used display description
50 DESCR_HCM4A Character(30) VARCHAR2(30) NOT NULL This field is used display description
51 DESCR_HCM5 Character(30) VARCHAR2(30) NOT NULL This field is used display description
52 DESCR_HCM5A Character(30) VARCHAR2(30) NOT NULL This field is used display description
53 DESCR_HCM6 Character(30) VARCHAR2(30) NOT NULL This field is used display description
54 DESCR_HCM6A Character(30) VARCHAR2(30) NOT NULL This field is used display description
55 DESCR_HCM7 Character(30) VARCHAR2(30) NOT NULL This field is used display description
56 DESCR_HCM7A Character(30) VARCHAR2(30) NOT NULL This field is used display description
57 DESCR_HCM8 Character(30) VARCHAR2(30) NOT NULL This field is used display description
58 DESCR_HCM8A Character(30) VARCHAR2(30) NOT NULL This field is used display description
59 COMMENTS Long Character CLOB Comment
60 DESCR_COMMENTS_HIS Long Character CLOB This field is used in a complex query that shows many descr fields.
61 GSS_BU_DESCR Character(50) VARCHAR2(50) NOT NULL GSS Business Unit description
62 GSS_BU_DESCR2 Character(50) VARCHAR2(50) NOT NULL GSS Business Unit description
63 GSS_DEPT_DESCR Character(50) VARCHAR2(50) NOT NULL GSS Department Description
64 GSS_DEPT_DESCR2 Character(50) VARCHAR2(50) NOT NULL GSS Department Description
65 GSS_JOBCODE_DESCR Character(50) VARCHAR2(50) NOT NULL GSS Jobcode Description
66 GSS_JOBCODE_DESCR2 Character(50) VARCHAR2(50) NOT NULL GSS Jobcode Description
67 GSS_LOC_DESCR Character(50) VARCHAR2(50) NOT NULL GSS Location Description
68 GSS_LOC_DESCR2 Character(50) VARCHAR2(50) NOT NULL GSS Location Description
69 GSS_POSITION_DESCR Character(50) VARCHAR2(50) NOT NULL GSS Position Description
70 GSS_POSITION_DESC2 Character(50) VARCHAR2(50) NOT NULL GSS Position Description
71 GSS_RPT_TO_DESCR Character(50) VARCHAR2(50) NOT NULL GSS Reports To Description
72 GSS_RPT_TO_DESCR2 Character(50) VARCHAR2(50) NOT NULL GSS Reports To Description
73 GSS_SUPERV_DESCR Character(50) VARCHAR2(50) NOT NULL GSS Supervisor description
74 GSS_SUPERV_DESCR2 Character(50) VARCHAR2(50) NOT NULL GSS Supervisor description
75 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
76 ACTION_DESCR Character(50) VARCHAR2(50) NOT NULL Action Description