GP_WC_NEWHIRE

(SQL View)
 Index Back

GP WC NewHire

GP WC NewHire

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.EFFDT , E.TERMINATION_DT , A.MONTHLY_RT , (CASE WHEN A.EFFDT = B.PRD_BGN_DT THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 17000 AND MESSAGE_NBR = 5094) WHEN A.EFFDT <> B.PRD_BGN_DT THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 17000 AND MESSAGE_NBR = 5087) END ) NEW_HIRE_TYPE FROM PS_JOB A , PS_GP_CAL_RUN_DTL B , PS_EMPLOYMENT E WHERE B.CALC_TYPE = 'P' AND A.EMPLID = E.EMPLID AND A.EMPL_RCD = E.EMPL_RCD AND B.GP_PAYGROUP = A.GP_PAYGROUP AND A.EFFDT BETWEEN B.PRD_BGN_DT AND B.PRD_END_DT AND A.ACTION IN( SELECT T.ACTION FROM PS_GP_WC_ACT_DTL T WHERE ( T.COUNTRY = B.COUNTRY OR ( T.COUNTRY = 'ALL' AND (NOT EXISTS( SELECT S.COUNTRY FROM PS_GP_WC_ACT_DTL S WHERE S.COUNTRY = B.COUNTRY )) ) ) AND T.GP_WC_ACTION_ID = 'H' ) AND NOT EXISTS( SELECT 1 FROM PS_JOB J WHERE J.EMPLID = A.EMPLID AND J.EMPL_RCD = A.EMPL_RCD AND J.EFFDT-1 BETWEEN B.PRD_BGN_DT AND B.PRD_END_DT AND J.EFFDT-1 >= 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 = B.COUNTRY OR ( T.COUNTRY = 'ALL' AND (NOT EXISTS( SELECT S.COUNTRY FROM PS_GP_WC_ACT_DTL S WHERE S.COUNTRY = B.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.EFFDT , E.TERMINATION_DT , A.MONTHLY_RT , ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 17000 AND MESSAGE_NBR = 5088) NEW_HIRE_TYPE FROM PS_JOB A , PS_GP_CAL_RUN_DTL B , PS_GP_PYE_PRC_STAT C , PS_EMPLOYMENT E WHERE B.CALC_TYPE = 'P' AND A.EMPLID = E.EMPLID AND A.EMPL_RCD = E.EMPL_RCD AND A.ACTION IN( SELECT T.ACTION FROM PS_GP_WC_ACT_DTL T WHERE ( T.COUNTRY = B.COUNTRY OR ( T.COUNTRY = 'ALL' AND (NOT EXISTS( SELECT S.COUNTRY FROM PS_GP_WC_ACT_DTL S WHERE S.COUNTRY = B.COUNTRY )) ) ) AND T.GP_WC_ACTION_ID = 'H' ) 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.PRD_TYPE = 'R' AND B.GP_PAYGROUP = C.GP_PAYGROUP AND B.CAL_ID = C.CAL_ID AND A.EFFDT BETWEEN B.PRD_BGN_DT AND B.PRD_END_DT 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.EFFDT ,J.TERMINATION_DT , A.MONTHLY_RT , ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 17000 AND MESSAGE_NBR = 5089) NEW_HIRE_TYPE FROM PS_JOB A , PS_GP_CAL_RUN_DTL B , PS_JOB J , PS_EMPLOYMENT E WHERE B.CALC_TYPE = 'P' AND A.EMPLID = E.EMPLID AND A.EMPL_RCD = E.EMPL_RCD AND A.EFFDT BETWEEN B.PRD_BGN_DT AND B.PRD_END_DT AND B.GP_PAYGROUP = A.GP_PAYGROUP AND A.ACTION IN( SELECT T.ACTION FROM PS_GP_WC_ACT_DTL T WHERE ( T.COUNTRY = B.COUNTRY OR ( T.COUNTRY = 'ALL' AND (NOT EXISTS( SELECT S.COUNTRY FROM PS_GP_WC_ACT_DTL S WHERE S.COUNTRY = B.COUNTRY )) ) ) AND T.GP_WC_ACTION_ID = 'H' ) AND J.EMPLID = A.EMPLID AND J.EMPL_RCD = A.EMPL_RCD AND J.EFFDT-1 BETWEEN B.PRD_BGN_DT AND B.PRD_END_DT AND J.EFFDT-1 >= 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 = B.COUNTRY OR ( T.COUNTRY = 'ALL' AND (NOT EXISTS( SELECT S.COUNTRY FROM PS_GP_WC_ACT_DTL S WHERE S.COUNTRY = B.COUNTRY )) ) ) AND T.GP_WC_ACTION_ID = 'T' ) AND J.EFFDT = ( SELECT MIN(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND J1.ACTION = J.ACTION AND J1.LAST_HIRE_DT= A.EFFDT)

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