GPGB_STARTR2_VW (SQL View)	Index Back
GUK - JOB Starters View Joining JOB and Process Action Reason definition 'P01' to identify Global Payroll UK Starters.

GPGB_STARTR2_VW

(SQL View)

GUK - JOB Starters

View Joining JOB and Process Action Reason definition 'P01' to identify Global Payroll UK Starters.

SELECT A.EMPLID ,A.EMPL_RCD ,A.EFFDT ,A.EFFSEQ ,A.ACTION ,A.ACTION_REASON ,A.GP_PAYGROUP ,A.PER_ORG ,A.PAY_SYSTEM_FLG FROM PS_JOB A , PS_GPGB_PRC_ACTION B WHERE B.GPGB_PROCESS_CODE = 'P01' AND ( (A.ACTION = B.ACTION AND B.GPGB_SPECIFIED_RSN = 'N' AND B.GPGB_BLANK_REASON = 'N' AND A.ACTION_REASON <> ' ') OR (A.ACTION = B.ACTION AND B.GPGB_SPECIFIED_RSN = 'N' AND B.GPGB_BLANK_REASON = 'Y') OR ( A.ACTION = B.ACTION AND A.ACTION_REASON IN ( SELECT C1.ACTION_REASON FROM PS_GPGB_PRC_REASON C1 WHERE C1.GPGB_PROCESS_CODE = B.GPGB_PROCESS_CODE AND C1.ACTION = B.ACTION) AND B.GPGB_SPECIFIED_RSN = 'Y' AND B.GPGB_BLANK_REASON = 'N') OR (A.ACTION = B.ACTION AND ( A.ACTION_REASON IN ( SELECT C2.ACTION_REASON FROM PS_GPGB_PRC_REASON C2 WHERE C2.GPGB_PROCESS_CODE = B.GPGB_PROCESS_CODE AND C2.ACTION = B.ACTION) OR A.ACTION_REASON = ' ') AND B.GPGB_SPECIFIED_RSN = 'Y' AND B.GPGB_BLANK_REASON = 'Y') ) AND( A.PAY_SYSTEM_FLG <>'OT' AND A.PAY_SYSTEM_FLG <>'PNA') UNION SELECT A.EMPLID ,A.EMPL_RCD ,B. LAST_HIRE_DT ,A.EFFSEQ ,A.ACTION ,A.ACTION_REASON ,A.GP_PAYGROUP ,A.PER_ORG ,A.PAY_SYSTEM_FLG FROM PS_JOB A , PS_PER_ORG_ASGN_VW B WHERE A.EMPLID=B.EMPLID AND A.EMPL_RCD=B.EMPL_RCD AND ( A.PAY_SYSTEM_FLG <>'OT' AND A.PAY_SYSTEM_FLG <>'PNA') AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_JOB A1 WHERE A1.EMPLID = A.EMPLID AND A1.EMPL_RCD = A.EMPL_RCD AND A1.EFFDT <=%CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(A2.EFFSEQ) FROM PS_JOB A2 WHERE A2.EMPLID = A.EMPLID AND A2.EMPL_RCD = A.EMPL_RCD AND A2.EFFDT = A.EFFDT)

# 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 Rcd Nbr

3 EFFDT Date(10) DATE Effective Date
Default Value: %date

4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence

5 ACTION Character(3) VARCHAR2(3) NOT NULL Action

6 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code

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

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 PAY_SYSTEM_FLG Character(2) VARCHAR2(2) NOT NULL Payroll System
GP=Global Payroll
NA=Payroll for North America
OT=Other
PI=Payroll Interface

#	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 Rcd Nbr
3	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
4	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
5	ACTION	Character(3)	VARCHAR2(3) NOT NULL	Action
6	ACTION_REASON	Character(3)	VARCHAR2(3) NOT NULL	Reason Code
7	GP_PAYGROUP	Character(10)	VARCHAR2(10) NOT NULL	Global Payroll pay group
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	PAY_SYSTEM_FLG	Character(2)	VARCHAR2(2) NOT NULL	Payroll System GP=Global Payroll NA=Payroll for North America OT=Other PI=Payroll Interface