FO_EMPL_XREF_VW

(SQL View)
 Index Back

FO Employee XREF Table


SELECT X.PBM_ASGN_ID , X.EMPLID , X.EMPL_RCD , X.BUSINESS_UNIT_PC , X.PROJECT_ID , X.ACTIVITY_ID , X.CUST_SETID , X.CUST_ID , X.SO_ID , X.SO_LINE , X.ASSIGN_STS , X.OWNERSHIP_ID , X.START_DT , X.END_DT , X.ADDRESS_SEQ_NUM , J.COMPANY , J.DEPTID FROM PS_FO_EMPL_XREF X , PS_JOB J WHERE J.EMPLID = X.EMPLID AND J.EMPL_RCD = X.EMPL_RCD AND J.EFFDT=( SELECT MAX(EFFDT) FROM PS_JOB JJ WHERE JJ.EMPLID=J.EMPLID AND JJ.EMPL_RCD=J.EMPL_RCD AND JJ.EFFDT<=X.START_DT) AND J.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB JJJ WHERE JJJ.EMPLID = J.EMPLID AND JJJ.EMPL_RCD = J.EMPL_RCD AND JJJ.EFFDT = J.EFFDT)

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

Prompt Table: JOB

3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record

Prompt Table: JOB

4 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL Project Costing Business Unit

Prompt Table: BUS_UNIT_TBL_PC

5 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project/Grant

Prompt Table: PROJECT

6 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID

Prompt Table: PROJ_ACTIVITY

7 CUST_SETID Character(5) VARCHAR2(5) NOT NULL Customer SetID
8 CUST_ID Character(15) VARCHAR2(15) NOT NULL Customer ID
9 SO_ID Character(15) VARCHAR2(15) NOT NULL Order ID
10 SO_LINE Number(3,0) SMALLINT NOT NULL Order Line
11 ASSIGN_STS Character(2) VARCHAR2(2) NOT NULL Assignment Status
C=Cancelled
E=Ended
O=Open
12 OWNERSHIP_ID Character(30) VARCHAR2(30) NOT NULL Staffing Branch
13 START_DT Date(10) DATE Start Date
14 END_DT Date(10) DATE end date
15 ADDRESS_SEQ_NUM Number(3,0) SMALLINT NOT NULL Address Sequence Number
16 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
17 DEPTID Character(10) VARCHAR2(10) NOT NULL Department