FO_PREPOP_ADDVW

(SQL View)
 Index Back

SELECT DISTINCT RP.ST_INSTANCE , PH.TL_GROUP_ID , PH.PERIOD_ID , PH.START_DT , PH.END_DT , RH.RAPID_TMPLT_ID , RP.EMPLID , RP.EMPL_RCD , XF.SO_ID , RP.PROJECT_ID , XF.CUST_SETID , XF.CUST_ID , XF.OWNERSHIP_ID , ED.WORKGROUP , RP.TASKGROUP , RP.BUSINESS_UNIT , RP.SETID_DEPT , RP.DEPTID , XF.NAME1 , RP.COMPANY , J.PAYGROUP , J.GP_PAYGROUP , PD.FIRST_NAME_SRCH , PD.LAST_NAME_SRCH FROM %Table(FO_POPASG_HDR) PH , %Table(TL_RAPID_TIME) RP , %Table(FO_EMPL_XREF) XF , %Table(TL_EMPL_DATA) ED , %Table(TL_RAPID_HEADER) RH , %Table(PERSONAL_DATA) PD , %Table(JOB) J WHERE PH.ST_INSTANCE = RH.ST_INSTANCE AND RP.ST_INSTANCE = RH.ST_INSTANCE AND XF.EMPLID = RP.EMPLID AND XF.EMPL_RCD = RP.EMPL_RCD AND XF.EMPLID = ED.EMPLID AND XF.EMPL_RCD = ED.EMPL_RCD AND ED.EFFDT = ( SELECT MAX(Z.EFFDT) FROM PS_TL_EMPL_DATA Z WHERE Z.EMPLID = ED.EMPLID AND Z.EMPL_RCD = ED.EMPL_RCD AND Z.EFFDT <= %CurrentDateIn) AND XF.EMPLID = PD.EMPLID AND XF.EMPLID = J.EMPLID AND XF.EMPL_RCD = J.EMPL_RCD AND J.EFFDT = ( SELECT MAX(Y.EFFDT) FROM PS_JOB Y WHERE Y.EMPLID = J.EMPLID AND Y.EMPL_RCD = J.EMPL_RCD AND Y.EFFDT <= %CurrentDateIn) AND J.EFFSEQ = ( SELECT MAX(X.EFFSEQ) FROM PS_JOB X WHERE X.EMPLID = J.EMPLID AND X.EMPL_RCD = J.EMPL_RCD AND X.EFFDT = J.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 ST_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Submit Time Instance

Default Value: 9999999999

2 TL_GROUP_ID Character(7) VARCHAR2(7) NOT NULL Time and Labor Groups

Prompt Table: TL_MASS_GRP_VW

3 PERIOD_ID Character(12) VARCHAR2(12) NOT NULL Time Period ID

Prompt Table: FO_TIME_PERIODS

4 START_DT Date(10) DATE Start Date

Prompt Table: FO_TL_CLNDR_ST

5 END_DT Date(10) DATE end date

Prompt Table: FO_TL_CLNDR_END

6 RAPID_TMPLT_ID Character(10) VARCHAR2(10) NOT NULL Rapid Time Template

Prompt Table: TL_RPDTME_TMPLT

7 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: FO_XREF_EMPLID

8 EMPL_RCD Number(3,0) SMALLINT NOT NULL Employment Record Number

Prompt Table: FO_XREF_EMPLRCD

9 SO_ID Character(15) VARCHAR2(15) NOT NULL Order ID

Prompt Table: FO_XREF_SOID

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

Prompt Table: FO_XREF_PROJ_ID

11 CUST_SETID Character(5) VARCHAR2(5) NOT NULL Customer SetID

Prompt Table: FO_XREF_C_SETID

12 CUST_ID Character(15) VARCHAR2(15) NOT NULL Customer ID

Prompt Table: FO_XREF_CUSTID

13 OWNERSHIP_ID Character(30) VARCHAR2(30) NOT NULL Staffing Branch

Prompt Table: FO_XREF_OWNRSHP

14 WORKGROUP Character(10) VARCHAR2(10) NOT NULL Workgroup

Prompt Table: TL_WRKGRP_TBL

15 TASKGROUP Character(10) VARCHAR2(10) NOT NULL Taskgroup

Prompt Table: TL_TASKGRP_TBL

16 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: BUS_UNIT_TBL_HR

17 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID

Prompt Table: SETID_TBL

18 DEPTID Character(10) VARCHAR2(10) NOT NULL Department ID

Prompt Table: SET_DEPT_VW

19 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1

Prompt Table: FO_REPORT_TO

20 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

Prompt Table: COMPANY_TBL

21 PAYGROUP Character(3) VARCHAR2(3) NOT NULL PNA/PI Pay Group

Prompt Table: PAYGROUP_TBL

22 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group

Prompt Table: GP_PYGRP

23 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name

Prompt Table: FO_WRKR_FNAME

24 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name

Prompt Table: FO_WRKR_LNAME