HR_MSS_CT_FA_VW

(SQL View)
 Index Back

GSS - Fluid Approvals

Contains Fluid Approval new information for the GSS transactions. This is a placeholder view where much of the data is not populated, but will be populated at runtime as needed. Used by most of the GSS Fluid Approval transactions.

SELECT 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 , A.SUPERVISOR_ID , A.REPORTS_TO , A.FULL_PART_TIME , A.STD_HOURS , 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 , B.COMPRATE , B.ANNUAL_RT , 'RSN.DESCR' , 'DESCR1' , 'DESCR2' , 'DESCR3' , 'DESCR4' , 'DESCR5' , 'DESCR6' , 'DESCR7' , 'DESCR8' , 'DESCR1A' , 'DESCR2A' , 'DESCR3A' , 'DESCR4A' , 'DESCR5A' , 'DESCR6A' , 'DESCR7A' , 'DESCR8A' , %Cast(STA.COMMENTS, Long, Character) ,'BULOC_CUR.DESCR' ,'DEPT_CUR.DESCR' ,'JC_CUR.DESCR' ,'BULOC_CUR.DESCR2' ,'POSN_CUR.DESCR' ,'POSN_RPT_CUR.DESCR' ,'SUPCUR.NAME_DISPLAY' ,B.CURRENCY_CD , 'AC.ACTION_DESCR' , 'NAME_DISPLAY' ,B.SETID_JOBCODE ,B.SETID_LOCATION ,B.SETID_DEPT FROM PS_HR_MSS_CT_DAT A , PS_PERSON_NAME EMP , PS_PERSON_NAME REQ , PSOPRDEFN OPR , PS_HR_MSS_CT_STA STA , PS_JOB B WHERE A.EMPLID = EMP.EMPLID AND A.ORIGINATORID = OPR.OPRID AND OPR.EMPLID =REQ.EMPLID 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 <= A.ACTION_DT_SS) AND B.EFFSEQ = ( SELECT MAX(B2.EFFSEQ) FROM PS_JOB B2 WHERE B2.EMPLID = B.EMPLID AND B2.EMPL_RCD = B.EMPL_RCD AND B2.EFFDT = B.EFFDT) 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 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 LAST_DATE_WORKED Date(10) DATE Last Date Worked
24 EXPECTED_RETURN_DT Date(10) DATE Expected Return Date
25 POSITION_NBR2 Character(8) VARCHAR2(8) NOT NULL Position Number 2
26 BUSINESS_UNIT2 Character(5) VARCHAR2(5) NOT NULL Business Unit
27 DEPTID2 Character(10) VARCHAR2(10) NOT NULL Department
28 LOCATION2 Character(10) VARCHAR2(10) NOT NULL Location 2
29 JOBCODE2 Character(6) VARCHAR2(6) NOT NULL Jobcode 2
30 SUPERVISOR_ID2 Character(11) VARCHAR2(11) NOT NULL Designates EMPLID of Supervisor of employee.
31 REPORTS_TO2 Character(8) VARCHAR2(8) NOT NULL Designates EMPLID of Reports To position of employee.
32 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
D=On Demand
F=Full-Time
P=Part-Time
33 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
34 COMPRATE2 Number(22,6) DECIMAL(21,6) NOT NULL Comp Rate field for discounts and perks eligibility file.
35 ANNUAL_RT2 Number(19,3) DECIMAL(18,3) NOT NULL Annual Rate
36 DESCR Character(30) VARCHAR2(30) NOT NULL Description
37 DESCR_HCM1 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
38 DESCR_HCM2 Character(30) VARCHAR2(30) NOT NULL This field is used display description
39 DESCR_HCM3 Character(30) VARCHAR2(30) NOT NULL This field is used display description
40 DESCR_HCM4 Character(30) VARCHAR2(30) NOT NULL This field is used display description
41 DESCR_HCM5 Character(30) VARCHAR2(30) NOT NULL This field is used display description
42 DESCR_HCM6 Character(30) VARCHAR2(30) NOT NULL This field is used display description
43 DESCR_HCM7 Character(30) VARCHAR2(30) NOT NULL This field is used display description
44 DESCR_HCM8 Character(30) VARCHAR2(30) NOT NULL This field is used display description
45 DESCR_HCM1A Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
46 DESCR_HCM2A Character(30) VARCHAR2(30) NOT NULL This field is used display description
47 DESCR_HCM3A Character(30) VARCHAR2(30) NOT NULL This field is used display description
48 DESCR_HCM4A Character(30) VARCHAR2(30) NOT NULL This field is used display description
49 DESCR_HCM5A Character(30) VARCHAR2(30) NOT NULL This field is used display description
50 DESCR_HCM6A Character(30) VARCHAR2(30) NOT NULL This field is used display description
51 DESCR_HCM7A 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 GSS_BU_DESCR2 Character(50) VARCHAR2(50) NOT NULL GSS Business Unit description
55 GSS_DEPT_DESCR2 Character(50) VARCHAR2(50) NOT NULL GSS Department Description
56 GSS_JOBCODE_DESCR2 Character(50) VARCHAR2(50) NOT NULL GSS Jobcode Description
57 GSS_LOC_DESCR2 Character(50) VARCHAR2(50) NOT NULL GSS Location Description
58 GSS_POSITION_DESC2 Character(50) VARCHAR2(50) NOT NULL GSS Position Description
59 GSS_RPT_TO_DESCR2 Character(50) VARCHAR2(50) NOT NULL GSS Reports To Description
60 GSS_SUPERV_DESCR2 Character(50) VARCHAR2(50) NOT NULL GSS Supervisor description
61 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
62 ACTION_DESCR Character(50) VARCHAR2(50) NOT NULL Action Description
63 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
64 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID
65 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID
66 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID