HR_MSSCT_FPT_VW

(SQL View)
 Index Back

HTML Email - GSS

Contains HTML Email Approval new information for the GSS transactions.

SELECT A.EMPLID ,A.EMPL_RCD ,A.ACTION_DT_SS ,A.EFFSEQ ,B.NAME ,A.WF_STATUS ,A.ACTION ,A.ACTION_REASON_SS ,A.FULL_PART_TIME ,X.XLATSHORTNAME ,C.FULL_PART_TIME ,XX.XLATLONGNAME ,XX.XLATLONGNAME ,A.ORIGINATORID ,D.Name ,C.STD_HOURS ,A.STD_HOURS ,A.FTE ,C.FTE ,C.HOURLY_RT ,C.ANNUAL_RT ,C.CURRENCY_CD ,%Sql(HR_HT_EMAIL_DATE,A.ACTION_DT_SS) ,RSN.DESCR ,CAST(STA.COMMENTS AS VARCHAR(4000)) ,'X' , JC.DESCR , JC.JOBCODE , A.BUSINESS_UNIT FROM PS_HR_MSS_CT_DAT A 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 B , PS_CURRENT_JOB_ALL C , PS_PERSON_NAME D , PSOPRDEFN OPR , PSXLATITEM X , PSXLATITEM XX , PS_HR_MSS_CT_STA STA , PS_BU_JOBCD_VW JC WHERE A.EMPLID = B.EMPLID AND A.EMPLID = C.EMPLID AND A.EMPLID=STA.EMPLID AND A.HR_MSS_CT_NAME='HR_FULL_PART_STATUS' AND a.empl_rcd = sta.empl_rcd AND A.EMPL_RCD = C.EMPL_RCD AND a.action_dt_ss = sta.action_dt_ss AND A.EFFSEQ = STA.EFFSEQ AND STA.SEQNO=1 AND OPR.EMPLID=D.EMPLID AND OPR.OPRID=A.ORIGINATORID AND X.FIELDVALUE= A.FULL_PART_TIME AND X.FIELDNAME='FULL_PART_TIME' AND XX.FIELDNAME='FULL_PART_TIME' AND XX.FIELDVALUE = C.FULL_PART_TIME AND JC.BUSINESS_UNIT_JOBC =C.BUSINESS_UNIT AND JC.JOBCODE=C.JOBCODE AND %EffdtCheck(BU_JOBCD_VW JC1, JC, %Currentdatein) AND C.EFFDT=( SELECT MAX(EFFDT) FROM PS_CURRENT_JOB_ALL J WHERE J.EMPLID=C.EMPLID AND J.EMPL_RCD=C.EMPL_RCD AND J.EFFDT <= a.action_dt_ss)

# 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 NOT NULL Termination Date
4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
5 NAME Character(50) VARCHAR2(50) NOT NULL Name
6 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
7 ACTION Character(3) VARCHAR2(3) NOT NULL Action

Default Value: DTA

Prompt Table: ACTION_TBL

8 ACTION_REASON_SS Character(3) VARCHAR2(3) NOT NULL Action Reason field for use on Self Service panels.
9 FULL_PART_TIME_NEW Character(1) VARCHAR2(1) NOT NULL New Full/Part Time Status
D=On Demand
F=Full-Time
P=Part-Time
10 XLATSHORTNAME Character(10) VARCHAR2(10) NOT NULL Translate Value Short Name
11 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
D=On Demand
F=Full-Time
P=Part-Time
12 XLATLONGNAME Character(30) VARCHAR2(30) NOT NULL Translate Long Name
13 FL_PT_TIME_LONGNAM Character(30) VARCHAR2(30) NOT NULL Translate Long Name
14 ORIGINATORID Character(30) VARCHAR2(30) NOT NULL Originator Identifier
15 MANAGER_NAME Character(30) VARCHAR2(30) NOT NULL Manager Name
16 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
17 STD_HOURS_NEW Number(7,2) DECIMAL(6,2) NOT NULL *New Standard Hours
18 FTE_NEW Number(8,6) DECIMAL(7,6) NOT NULL This field represent Full Time Equivalence
19 FTE Number(8,6) DECIMAL(7,6) NOT NULL This field represent Full Time Equivalence
20 HOURLY_RT Number(19,6) DECIMAL(18,6) NOT NULL Hourly Rate
21 ANNUAL_RT Number(19,3) DECIMAL(18,3) NOT NULL Annual Rate
22 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
23 DESCR20 Character(20) VARCHAR2(20) NOT NULL Description
24 DESCR Character(30) VARCHAR2(30) NOT NULL Description
25 COMMENTS Long Character CLOB Comment
26 DESCR_COMMENTS_HIS Long Character CLOB This field is used in a complex query that shows many descr fields.
27 DESCR_HCM5 Character(30) VARCHAR2(30) NOT NULL This field is used display description
28 JOBCODE2 Character(6) VARCHAR2(6) NOT NULL Jobcode 2
29 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit