GP_WC_TERMINATE

(SQL View)
 Index Back

GP WC terminate

GP WC terminate

SELECT C.CAL_RUN_ID , C.CAL_ID , C.RUN_TYPE , A.EMPLID , A.EMPL_RCD ,A.EFFDT ,A.EFFSEQ ,A.COMPANY , A.BUSINESS_UNIT , A.DEPTID , A.LOCATION , A.JOBCODE , A.ESTABID , A.HR_STATUS , A.EMPL_STATUS , A.GP_PAYGROUP , A.EMPL_CLASS , A.PER_ORG , A.COMPRATE , A.FTE , A.ACTION , A.LAST_HIRE_DT , A.TERMINATION_DT , A.MONTHLY_RT , (CASE WHEN A.EFFDT = B.PRD_END_DT + 1 THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 17000 AND MESSAGE_NBR = 5090) WHEN A.EFFDT <> B.PRD_END_DT +1 THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 17000 AND MESSAGE_NBR = 5091) END ) TERMINATION_TYPE FROM PS_JOB A , PS_GP_CAL_RUN_DTL B , PS_GP_PYE_PRC_STAT C WHERE B.CALC_TYPE = 'P' AND A.EFFDT BETWEEN B.PRD_BGN_DT AND B.PRD_END_DT +1 AND A.ACTION IN ( SELECT T.ACTION FROM PS_GP_WC_ACT_DTL T WHERE ( T.COUNTRY = C.COUNTRY OR ( T.COUNTRY = 'ALL' AND (NOT EXISTS( SELECT S.COUNTRY FROM PS_GP_WC_ACT_DTL S WHERE S.COUNTRY = C.COUNTRY )) ) ) AND T.GP_WC_ACTION_ID = 'T' ) AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.GP_PAYGROUP = A.GP_PAYGROUP AND A.EFFDT-1 BETWEEN C.PRD_BGN_DT AND C.PRD_END_DT AND C.GP_PAYGROUP = B.GP_PAYGROUP AND C.CAL_RUN_ID = B.CAL_RUN_ID AND C.CAL_ID = B.CAL_ID AND NOT EXISTS( SELECT 1 FROM PS_JOB J WHERE J.EMPLID = A.EMPLID AND J.EMPL_RCD = A.EMPL_RCD AND J.EFFDT BETWEEN B.PRD_BGN_DT AND B.PRD_END_DT +1 AND J.EFFDT <= A.EFFDT AND J.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND J1.EFFDT = J.EFFDT) AND J.ACTION IN ( SELECT T.ACTION FROM PS_GP_WC_ACT_DTL T WHERE ( T.COUNTRY = C.COUNTRY OR ( T.COUNTRY = 'ALL' AND (NOT EXISTS( SELECT S.COUNTRY FROM PS_GP_WC_ACT_DTL S WHERE S.COUNTRY = C.COUNTRY )) ) ) AND T.GP_WC_ACTION_ID = 'H' ) ) AND NOT EXISTS( SELECT 1 FROM PS_GP_PYE_PRC_STAT PY WHERE PY.EMPLID = C.EMPLID AND PY.EMPL_RCD = C.EMPL_RCD AND PY.GP_PAYGROUP = C.GP_PAYGROUP AND PY.PRD_BGN_DT = A.EFFDT AND PY.PRC_ORD_TS > C.PRC_ORD_TS AND PY.PRD_TYPE = 'R' ) UNION SELECT C.CAL_RUN_ID , C.CAL_ID , C.RUN_TYPE , A.EMPLID , A.EMPL_RCD ,A.EFFDT ,A.EFFSEQ ,A.COMPANY , A.BUSINESS_UNIT , A.DEPTID , A.LOCATION , A.JOBCODE , A.ESTABID , A.HR_STATUS , A.EMPL_STATUS , A.GP_PAYGROUP , A.EMPL_CLASS , A.PER_ORG , A.COMPRATE , A.FTE , A.ACTION , A.LAST_HIRE_DT , A.TERMINATION_DT , A.MONTHLY_RT , ( ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 17000 AND MESSAGE_NBR = 5092) ) TERMINATION_TYPE FROM PS_JOB A , PS_GP_CAL_RUN_DTL B , PS_GP_PYE_PRC_STAT C WHERE B.CALC_TYPE = 'P' AND A.ACTION IN ( SELECT T.ACTION FROM PS_GP_WC_ACT_DTL T WHERE ( T.COUNTRY = C.COUNTRY OR ( T.COUNTRY = 'ALL' AND (NOT EXISTS( SELECT S.COUNTRY FROM PS_GP_WC_ACT_DTL S WHERE S.COUNTRY = C.COUNTRY )) ) ) AND T.GP_WC_ACTION_ID = 'T' ) AND A.ACTION_DT > ( SELECT MAX(B1.CAL_IDNT_TS) FROM PS_GP_CAL_RUN_DTL B1 WHERE B1.GP_PAYGROUP = B.GP_PAYGROUP AND B1.CAL_FINAL_TS IS NOT NULL ) AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.GP_PAYGROUP = A.GP_PAYGROUP AND C.GP_PAYGROUP = B.GP_PAYGROUP AND C.CAL_ID = B.CAL_ID AND C.PRD_TYPE = 'R' AND EXISTS( SELECT * FROM PS_GP_TRGR_VALUE WHERE COUNTRY = C.COUNTRY AND RECNAME = 'JOB' AND TRGR_TYPE = 'R' AND FIELDNAME = 'ACTION' AND ( A.EFFDT + OFFSET_DAYS ) BETWEEN B.PRD_BGN_DT AND B.PRD_END_DT AND TRGR_FLD_VAL_CHAR IN ( SELECT T.ACTION FROM PS_GP_WC_ACT_DTL T WHERE ( T.COUNTRY = C.COUNTRY OR ( T.COUNTRY = 'ALL' AND (NOT EXISTS( SELECT S.COUNTRY FROM PS_GP_WC_ACT_DTL S WHERE S.COUNTRY = C.COUNTRY )) ) ) AND T.GP_WC_ACTION_ID = 'T' ) ) UNION SELECT B.CAL_RUN_ID , B.CAL_ID , B.RUN_TYPE , A.EMPLID , A.EMPL_RCD ,A.EFFDT ,A.EFFSEQ ,A.COMPANY , A.BUSINESS_UNIT , A.DEPTID , A.LOCATION , A.JOBCODE , A.ESTABID , A.HR_STATUS , A.EMPL_STATUS , A.GP_PAYGROUP , A.EMPL_CLASS , A.PER_ORG , A.COMPRATE , A.FTE , A.ACTION , A.LAST_HIRE_DT , A.TERMINATION_DT , A.MONTHLY_RT , ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 17000 AND MESSAGE_NBR = 5093) TERMINATION_TYPE FROM PS_JOB A , PS_GP_CAL_RUN_DTL B , PS_GP_PYE_PRC_STAT C WHERE B.CALC_TYPE = 'P' AND A.EFFDT BETWEEN B.PRD_BGN_DT AND B.PRD_END_DT +1 AND A.ACTION IN ( SELECT T.ACTION FROM PS_GP_WC_ACT_DTL T WHERE ( T.COUNTRY = C.COUNTRY OR ( T.COUNTRY = 'ALL' AND (NOT EXISTS( SELECT S.COUNTRY FROM PS_GP_WC_ACT_DTL S WHERE S.COUNTRY = C.COUNTRY )) ) ) AND T.GP_WC_ACTION_ID = 'T' ) AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.GP_PAYGROUP = A.GP_PAYGROUP AND C.GP_PAYGROUP = B.GP_PAYGROUP AND C.CAL_RUN_ID = B.CAL_RUN_ID AND C.CAL_ID = B.CAL_ID AND EXISTS( SELECT 1 FROM PS_JOB J WHERE J.EMPLID = A.EMPLID AND J.EMPL_RCD = A.EMPL_RCD AND J.EFFDT BETWEEN B.PRD_BGN_DT AND B.PRD_END_DT +1 AND J.EFFDT <= A.EFFDT AND J.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND J1.EFFDT = J.EFFDT) AND J.ACTION IN ( SELECT T.ACTION FROM PS_GP_WC_ACT_DTL T WHERE ( T.COUNTRY = C.COUNTRY OR ( T.COUNTRY = 'ALL' AND (NOT EXISTS( SELECT S.COUNTRY FROM PS_GP_WC_ACT_DTL S WHERE S.COUNTRY = C.COUNTRY )) ) ) AND T.GP_WC_ACTION_ID = 'H' ) )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id
2 CAL_ID Character(18) VARCHAR2(18) NOT NULL calendar id
3 RUN_TYPE Character(10) VARCHAR2(10) NOT NULL Run Type Name
4 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
5 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
6 EFFDT Date(10) DATE NOT NULL Effective Date

Default Value: %date

7 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
8 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

Prompt Table: COMPANY_TBL

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

Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT

Prompt Table: BUSUNIT_HR_VW

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

Prompt Table: DEPT_TBL

11 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

Prompt Table: LOCATION_JOB_VW

12 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

Prompt Table: JOBCODE_TBL

13 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID
14 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
A=Active
I=Inactive
15 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
16 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
17 EMPL_CLASS Character(3) VARCHAR2(3) NOT NULL Employee Classification
18 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
19 COMPRATE Number(19,6) DECIMAL(18,6) NOT NULL Compensation Rate
20 FTE Number(8,6) DECIMAL(7,6) NOT NULL This field represent Full Time Equivalence
21 ACTION Character(3) VARCHAR2(3) NOT NULL Action
22 LAST_HIRE_DT Date(10) DATE Latest Start Date - The latest time that an Employee or a Contingent Worker has started - based on EMPLID/EMPL_RCD.
23 TERMINATION_DT Date(10) DATE Termination Date
24 MONTHLY_RT Number(19,3) DECIMAL(18,3) NOT NULL Monthly Rate
25 GP_WORKITEM_TYPE Character(254) VARCHAR2(254) NOT NULL GP WC Type of Employee