HCR_OA_HDR_VW

(SQL View)
 Index Back

Header Info for Fluid Approval

This view would fetch information required to display the header information in fluid approvals.

SELECT A.EMPLID , A.EMPL_RCD , A.EFFDT , A.EFFSEQ , A.HCR_ACTN_SEQN , N.NAME_DISPLAY , ' ' ,G.PER_ORG ,X.XLATLONGNAME ,X.XLATLONGNAME ,P.EMPLOYEE_PHOTO , A.EMPLID FROM PS_HCR_JOB_STG A LEFT OUTER JOIN PS_USER_PH_LIST_FL P ON A.EMPLID=P.EMPLID INNER JOIN PS_NAMES N ON A.EMPLID=N.EMPLID AND N.EFFDT = ( SELECT MAX (NM1.EFFDT) FROM PS_NAMES NM1 WHERE N.EMPLID = NM1.EMPLID AND NM1.NAME_TYPE = 'PRI' AND NM1.EFFDT <= %CurrentDateIn) INNER JOIN PS_PER_ORG_ASG_STG G ON A.EMPLID=G.EMPLID AND A.EMPL_RCD = G.EMPL_RCD AND A.EFFDT = G.EFFDT AND A.EFFSEQ = G.EFFSEQ AND A.HCR_ACTN_SEQN = G.HCR_ACTN_SEQN INNER JOIN PSXLATITEM X ON X.FIELDVALUE = G.PER_ORG AND X.FIELDNAME = 'PER_ORG'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Default Value: NEW

Prompt Table: PERSON

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
3 EFFDT Date(10) DATE NOT NULL Effective Date

Default Value: %date

4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
5 HCR_ACTN_SEQN Number(3,0) SMALLINT NOT NULL Sequence number
6 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
7 JOBTITLE Character(30) VARCHAR2(30) NOT NULL Job Title
8 PER_ORG Character(3) VARCHAR2(3) NOT NULL Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
CWR=Contingent Worker
EMP=Employee
POI=Person of Interest
9 DESCR Character(30) VARCHAR2(30) NOT NULL Description
10 HR_ORG_REL Character(30) VARCHAR2(30) NOT NULL Org Relation
11 EMPLOYEE_PHOTO Image / Attachment(256) VARCHAR2(256) Employee Photograph
12 DESCR254 Character(254) VARCHAR2(254) NOT NULL Description of length 254