ST_JOB_GRDT_VW (SQL View)	Index Back
Vw-JOB w/out PeopleCode ST_JOB_VW is a view of the Job record with few fields required in Stock Admin, which screens out all peoplecode.

ST_JOB_GRDT_VW

(SQL View)

Vw-JOB w/out PeopleCode

ST_JOB_VW is a view of the Job record with few fields required in Stock Admin, which screens out all peoplecode.

SELECT A.EMPLID , A.EMPL_RCD , A.EFFDT , A.EFFSEQ , A.PER_ORG , A.EMPL_STATUS , A.ACTION , A.ACTION_DT , A.ACTION_REASON , A.LOCATION , A.TAX_LOCATION_CD , A.COMPANY , A.PAYGROUP , A.BUSINESS_UNIT , A.PAY_SYSTEM_FLG FROM PS_JOB A WHERE A.EFFSEQ = ( SELECT MAX(JB2.EFFSEQ) FROM PS_JOB JB2 WHERE JB2.EMPLID = A.EMPLID AND JB2.EMPL_RCD = A.EMPL_RCD AND JB2.EFFDT = A.EFFDT)

# 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 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
Default Value: PER_ORG_ASGN.PER_ORG

6 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Payroll Status
A=Active
D=Deceased
L=Leave of Absence
P=Leave With Pay
Q=Retired With Pay
R=Retired
S=Suspended
T=Terminated
U=Terminated With Pay
V=Terminated Pension Pay Out
W=Short Work Break
X=Retired-Pension Administration
Default Value: A

7 ACTION Character(3) VARCHAR2(3) NOT NULL Action
Default Value: HIR
Prompt Table: ACTION_TBL

8 ACTION_DT Date(10) DATE Action Date
Default Value: %date

9 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code
Prompt Table: ACTN_REASON_TBL

10 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
Prompt Table: LOCATION_TBL

11 TAX_LOCATION_CD Character(10) VARCHAR2(10) NOT NULL Tax Location Code

12 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
Prompt Table: COMPANY_TBL

13 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group
Prompt Table: PAYGROUP_TBL

14 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT
Prompt Table: BUS_UNIT_TBL_HR

15 PAY_SYSTEM_FLG Character(2) VARCHAR2(2) NOT NULL Payroll System
GP=Global Payroll
NA=Payroll for North America
OT=Other
PI=Payroll Interface
Default Value: OPR_DEF_TBL_HR.PAY_SYSTEM_FLG

#	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	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 Default Value: PER_ORG_ASGN.PER_ORG
6	EMPL_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Payroll Status A=Active D=Deceased L=Leave of Absence P=Leave With Pay Q=Retired With Pay R=Retired S=Suspended T=Terminated U=Terminated With Pay V=Terminated Pension Pay Out W=Short Work Break X=Retired-Pension Administration Default Value: A
7	ACTION	Character(3)	VARCHAR2(3) NOT NULL	Action Default Value: HIR Prompt Table: ACTION_TBL
8	ACTION_DT	Date(10)	DATE	Action Date Default Value: %date
9	ACTION_REASON	Character(3)	VARCHAR2(3) NOT NULL	Reason Code Prompt Table: ACTN_REASON_TBL
10	LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Location Code Prompt Table: LOCATION_TBL
11	TAX_LOCATION_CD	Character(10)	VARCHAR2(10) NOT NULL	Tax Location Code
12	COMPANY	Character(3)	VARCHAR2(3) NOT NULL	Company Prompt Table: COMPANY_TBL
13	PAYGROUP	Character(3)	VARCHAR2(3) NOT NULL	Pay Group Prompt Table: PAYGROUP_TBL
14	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT Prompt Table: BUS_UNIT_TBL_HR
15	PAY_SYSTEM_FLG	Character(2)	VARCHAR2(2) NOT NULL	Payroll System GP=Global Payroll NA=Payroll for North America OT=Other PI=Payroll Interface Default Value: OPR_DEF_TBL_HR.PAY_SYSTEM_FLG