HR_PROMO_DTLVW2

(SQL View)
 Index Back

HTML Email - EE Promotion

Contains HTML Email Approval new information for the HR Request Promotion transaction.

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<>' ' AND A.POSITION_NBR<>B.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 , 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 , 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 , BULOC_CUR.DESCR %Concat ' - ' %Concat B.BUSINESS_UNIT , DEPT_CUR.DESCR %Concat ' - ' %Concat B.DEPTID , JC_CUR.DESCR %Concat ' - ' %Concat B.JOBCODE , BULOC_CUR.DESCR2 %Concat ' - ' %Concat B.LOCATION , POSN_CUR.DESCR %Concat ' - ' %Concat B.POSITION_NBR , POSN_RPT_CUR.DESCR %Concat ' - ' %Concat B.REPORTS_TO , SUPCUR.NAME_DISPLAY %Concat ' - ' %Concat B.SUPERVISOR_ID , CAST(STA.COMMENTS AS VARCHAR(4000)) , 'X' FROM PS_HR_PROMOTE_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_ACTN_REASON_TBL RSN , PS_HR_XLAT_EFF_VW WF ,PS_HR_PROMOTE_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_VW2 JC ,PS_HR_PRTR_BULOC_V BULOC_CUR , PS_BU_DEPT_VW DEPT_CUR ,PS_BU_JOBCD_VW2 JC_CUR WHERE A.EMPLID = EMP.EMPLID AND A.ORIGINATORID = OPR.OPRID AND OPR.EMPLID =REQ.EMPLID AND RSN.ACTION =A.ACTION AND RSN.ACTION_REASON =A.ACTION_REASON_SS AND %EffdtCheck(ACTN_REASON_TBL RSN1, RSN, %Currentdatein) AND RSN.EFF_STATUS = 'A' 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 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_VW2 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_VW2 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 POSITION_NBR2 Character(8) VARCHAR2(8) NOT NULL Position Number 2
23 BUSINESS_UNIT2 Character(5) VARCHAR2(5) NOT NULL Business Unit
24 DEPTID2 Character(10) VARCHAR2(10) NOT NULL Department
25 LOCATION2 Character(10) VARCHAR2(10) NOT NULL Location 2
26 JOBCODE2 Character(6) VARCHAR2(6) NOT NULL Jobcode 2
27 SUPERVISOR_ID2 Character(11) VARCHAR2(11) NOT NULL Designates EMPLID of Supervisor of employee.
28 REPORTS_TO2 Character(8) VARCHAR2(8) NOT NULL Designates EMPLID of Reports To position of employee.
29 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
D=On Demand
F=Full-Time
P=Part-Time
30 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
31 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
32 DESCR20 Character(20) VARCHAR2(20) NOT NULL Description
33 DESCR Character(30) VARCHAR2(30) NOT NULL Description
34 XLATLONGNAME Character(30) VARCHAR2(30) NOT NULL Translate Long Name
35 DESCR_HCM1 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
36 DESCR_HCM1A Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
37 DESCR_HCM2 Character(30) VARCHAR2(30) NOT NULL This field is used display description
38 DESCR_HCM2A 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_HCM3A Character(30) VARCHAR2(30) NOT NULL This field is used display description
41 DESCR_HCM4 Character(30) VARCHAR2(30) NOT NULL This field is used display description
42 DESCR_HCM4A Character(30) VARCHAR2(30) NOT NULL This field is used display description
43 DESCR_HCM5 Character(30) VARCHAR2(30) NOT NULL This field is used display description
44 DESCR_HCM5A Character(30) VARCHAR2(30) NOT NULL This field is used display description
45 DESCR_HCM6 Character(30) VARCHAR2(30) NOT NULL This field is used display description
46 DESCR_HCM6A Character(30) VARCHAR2(30) NOT NULL This field is used display description
47 DESCR_HCM7 Character(30) VARCHAR2(30) NOT NULL This field is used display description
48 DESCR_HCM7A Character(30) VARCHAR2(30) NOT NULL This field is used display description
49 DESCR_BU2 Character(50) VARCHAR2(50) NOT NULL Stores both "After' BU description & code for Fluid Approval
50 DESCR_DEPT2 Character(50) VARCHAR2(50) NOT NULL Stores both "After" DEPT description & code for Fluid Approval
51 DESCR_JOBCD2 Character(50) VARCHAR2(50) NOT NULL Stores both "After' JOBTITLE description & code for Fluid Approval
52 DESCR_LOC2 Character(50) VARCHAR2(50) NOT NULL Stores both "After' Location description & code for Fluid Approval
53 DESCR_POSN2 Character(50) VARCHAR2(50) NOT NULL Stores both "After' POSITION description & code for Fluid Approval
54 DESCR_REPTO2 Character(50) VARCHAR2(50) NOT NULL Stores both "After' REPORTS_TO description & code for Fluid Approval
55 DESCR_SUPRVSR2 Character(50) VARCHAR2(50) NOT NULL Stores both "After' REPORTS_TO description & code for Fluid Approval
56 COMMENTS Long Character CLOB Comment
57 DESCR_COMMENTS_HIS Long Character CLOB This field is used in a complex query that shows many descr fields.