GPGB_LEAVER_VW (SQL View)	Index Back
GUK - JOB Leavers View Joining JOB and Process Action Reason definition 'P45' to identify Global Payroll UK Leavers. EFFDT is calculated as the day before (JOB EFFDT - 1)

GPGB_LEAVER_VW

(SQL View)

GUK - JOB Leavers

View Joining JOB and Process Action Reason definition 'P45' to identify Global Payroll UK Leavers. EFFDT is calculated as the day before (JOB EFFDT - 1)

SELECT A.EMPLID ,A.EMPL_RCD ,%DateAdd(A.EFFDT, -1) ,A.EFFSEQ ,A.ACTION ,A.ACTION_REASON ,( SELECT A1.GP_PAYGROUP FROM PS_JOB A1 WHERE A1.EMPLID = A.EMPLID AND A1.EMPL_RCD = A.EMPL_RCD AND A1.EFFDT = ( SELECT MAX(A2.EFFDT) FROM PS_JOB A2 WHERE A2.EMPLID = A1.EMPLID AND A2.EMPL_RCD = A.EMPL_RCD AND A2.EFFDT < A.EFFDT) AND A1.EFFSEQ = ( SELECT MAX(A2.EFFSEQ) FROM PS_JOB A2 WHERE A2.EMPLID = A1.EMPLID AND A2.EMPL_RCD = A1.EMPL_RCD AND A2.EFFDT = A1.EFFDT)) ,A.PER_ORG ,A.PAY_SYSTEM_FLG FROM PS_JOB A , PS_GPGB_PRC_ACTION B WHERE B.GPGB_PROCESS_CODE = 'P45' 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 EXISTS ( SELECT 'X' FROM PS_JOB J , PS_GP_PYGRP D , PS_GP_PYENT E WHERE J.EMPLID = A.EMPLID AND J.EMPL_RCD = A.EMPL_RCD AND J.EFFDT = ( SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <= A.EFFDT AND J2.PAY_SYSTEM_FLG = 'GP' ) AND J.EFFSEQ = ( SELECT MAX(J3.EFFSEQ) FROM PS_JOB J3 WHERE J3.EMPLID = J.EMPLID AND J3.EMPL_RCD = J.EMPL_RCD AND J3.EFFDT = J.EFFDT AND J3.PAY_SYSTEM_FLG = 'GP') AND D.GP_PAYGROUP = J.GP_PAYGROUP AND E.PAY_ENTITY = D.PAY_ENTITY AND E.COUNTRY = 'GBR')

# 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 Record

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 Record
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