HR_MSSCT_DTL2_V

(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.ORIGINATORID , EMP.NAME , REQ.NAME , A.POSITION_NBR , A.BUSINESS_UNIT , A.DEPTID , A.LOCATION , A.POSITION_FLAG , A.JOBCODE , CASE WHEN (A.REPORTS_TO <> ' ') THEN ' ' ELSE A.SUPERVISOR_ID END , CASE WHEN (A.REPORTS_TO <> ' ') 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 , 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.ACTION_DT_SS) , 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 ,POSN.DESCR %Concat ' - ' %Concat A.POSITION_NBR ,POSN_CUR.DESCR %Concat ' - ' %Concat B.POSITION_NBR , ' ' , ' ' 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 , 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 WHERE A.EMPLID = EMP.EMPLID AND A.ORIGINATORID = OPR.OPRID AND OPR.EMPLID =REQ.EMPLID 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 ORIGINATORID Character(30) VARCHAR2(30) NOT NULL Originator Identifier
8 EMPLNAME Character(50) VARCHAR2(50) NOT NULL EmplName
9 NAME_DESCR Character(50) VARCHAR2(50) NOT NULL Description
10 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number

Prompt Table: POSITION_BY_BUS

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

Prompt Table: BUS_UNIT_TBL_HR

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

Prompt Table: DEPT_TBL

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

Prompt Table: LOCATION_TBL

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

Prompt Table: JOBCODE_TBL

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

Prompt Table: HR_ACTIVE_DVW

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