HR_GSS_APDTL_VW

(SQL View)
 Index Back

HTML Email - Guided Self Svc

Contains HTML Email Approval new information for the HR guided self service transactions. Note - modified view as part of bug 19809015

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 , A.HR_MSS_CT_NAME , CASE WHEN (A.POSITION_NBR<>' ' AND A.SUPERVISOR_ID=' ') THEN ' ' ELSE A.SUPERVISOR_ID END , CASE WHEN (A.POSITION_NBR<>' ') THEN A.REPORTS_TO ELSE ' ' END , A.FULL_PART_TIME , A.STD_HOURS , SUP.NAME_DISPLAY , 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 , %Sql(HR_HT_EMAIL_DATE,A.ACTION_DT_SS) , RSN.DESCR , WF.XLATLONGNAME , POSN_CUR.DESCR , POSN.DESCR , BULOC_CUR.DESCR , BULOC.DESCR , BULOC_CUR.DESCR2 , BULOC.DESCR2 , FT_CUR.XLATLONGNAME , FTPT.XLATLONGNAME , POSN_RPT_CUR.DESCR , POSN_RPT.DESCR , CAST(STA.COMMENTS AS VARCHAR(4000)) , 'X' , A.ANNUAL_RT ,B.ANNUAL_RT , A.COMPRATE ,B.COMPRATE ,B.CURRENCY_CD ,A.LAST_DATE_WORKED ,A.LDW_OVR 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_HR_XLAT_EFF_VW FTPT ON FTPT.FIELDNAME='FULL_PART_TIME' AND A.FULL_PART_TIME=FTPT.FIELDVALUE LEFT OUTER JOIN PS_ACTN_REASON_TBL 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_HR_PRTR_BULOC_V BULOC_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 <= A.ACTION_DT_SS) 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 BULOC_CUR.BUSINESS_UNIT =B.BUSINESS_UNIT AND BULOC_CUR.LOCATION =B.LOCATION 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 HR_MSS_CT_NAME Character(25) VARCHAR2(25) NOT NULL Compound Transaction Name
18 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID

Prompt Table: HR_ACTIVE_DVW

19 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number
20 FULL_PART_TIME_NEW Character(1) VARCHAR2(1) NOT NULL New Full/Part Time Status
D=On Demand
F=Full-Time
P=Part-Time
21 STD_HOURS_NEW Number(7,2) DECIMAL(6,2) NOT NULL *New Standard Hours
22 SUPERVISOR_NAME Character(50) VARCHAR2(50) NOT NULL Supervisor Name
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 DESCR20 Character(20) VARCHAR2(20) NOT NULL Description
34 DESCR Character(30) VARCHAR2(30) NOT NULL Description
35 XLATLONGNAME Character(30) VARCHAR2(30) NOT NULL Translate Long Name
36 DESCR_HCM1 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
37 DESCR_HCM1A 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_HCM2A 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_HCM4A 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_HCM6A Character(30) VARCHAR2(30) NOT NULL This field is used display description
44 DESCR_HCM7 Character(30) VARCHAR2(30) NOT NULL This field is used display description
45 DESCR_HCM7A Character(30) VARCHAR2(30) NOT NULL This field is used display description
46 COMMENTS Long Character CLOB Comment
47 DESCR_COMMENTS_HIS Long Character CLOB This field is used in a complex query that shows many descr fields.
48 ANNUAL_RT Number(19,3) DECIMAL(18,3) NOT NULL Annual Rate
49 ANNUAL_RT_DISP Number(19,3) DECIMAL(18,3) NOT NULL Used to display Annual Rate from JOB.
50 COMPRATE Number(19,6) DECIMAL(18,6) NOT NULL Compensation Rate
51 COMPRATE2 Number(22,6) DECIMAL(21,6) NOT NULL Comp Rate field for discounts and perks eligibility file.
52 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
53 LAST_DATE_WORKED Date(10) DATE Last Date Worked
54 LDW_OVR Character(1) VARCHAR2(1) NOT NULL Used to indicate whether the user has overridden the Last Date Worked - ON THIS JOB_DATES row. It will only be set to 'Y' on the row where the date is entered, it will be 'N' on the later rows. This allows us to identify which JOB_DATES row is the row where the Date was either set or updated without having to look at the previous row. If the LDW = ( EFFDT - 1 ) or the LDW_OVR is 'Y', then this is the row where the LDW was set - and therefore can be corrected.