EMPLIDEMPL_RCDEFFDTDefault Value: %date
EFFSEQCWR=Contingent Worker
EMP=Employee
POI=Person of Interest
GP=Global Payroll
NA=Payroll for North America
OT=Other
PI=Payroll Interface
GPGB_LEAVER_VW(SQL View) |
Index Back |
|---|---|
GUK - JOB LeaversView 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 ,A.GP_PAYGROUP ,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 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 J.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 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 |