GP_WC_TERMIN_VW(SQL View) |
Index Back |
|---|---|
GP WC terminateGP WC terminate |
| SELECT A.EMPLID ,( SELECT B.NAME FROM PS_NAMES B WHERE B.EMPLID = A.EMPLID AND B.NAME_TYPE = 'PRI' AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_NAMES B1 WHERE B1.EMPLID = B.EMPLID AND B1.NAME_TYPE = B.NAME_TYPE AND B1.EFFDT <= %CurrentDateIn)), A.EMPL_RCD , A.ACTION, ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_TBL ACT WHERE ACT.ACTION = A.ACTION AND ACT.EFF_STATUS = 'A' AND ACT.EFFDT = ( SELECT MAX(T.EFFDT) FROM PS_ACTION_TBL T WHERE ACT.ACTION = T.ACTION AND T.EFFDT <= %CurrentDateIn)), A.EFFDT, A.TERMINATION_DT ,( SELECT DISTINCT G.PIN_GROSS_VAL FROM PS_GP_PYE_SEG_STAT G WHERE G.EMPLID = C.EMPLID AND G.EMPL_RCD = C.EMPL_RCD AND G.CAL_RUN_ID = C.CAL_RUN_ID AND G.RSLT_VER_NUM = C.RSLT_VER_NUM AND G.RSLT_REV_NUM = C.RSLT_REV_NUM AND G.GP_PAYGROUP = C.GP_PAYGROUP AND G.ORIG_CAL_RUN_ID = C.ORIG_CAL_RUN_ID AND G.CAL_ID = C.CAL_ID AND G.RSLT_SEG_NUM = ( SELECT MAX(G1.RSLT_SEG_NUM) FROM PS_GP_PYE_SEG_STAT G1 WHERE G1.EMPLID = G.EMPLID AND G.EMPL_RCD= G1.EMPL_RCD AND G1.CAL_RUN_ID = G.CAL_RUN_ID AND G1.GP_PAYGROUP = G.GP_PAYGROUP )), ( SELECT DISTINCT G.PIN_NET_VAL FROM PS_GP_PYE_SEG_STAT G WHERE G.EMPLID = C.EMPLID AND G.EMPL_RCD = C.EMPL_RCD AND G.CAL_RUN_ID = C.CAL_RUN_ID AND G.RSLT_VER_NUM = C.RSLT_VER_NUM AND G.RSLT_REV_NUM = C.RSLT_REV_NUM AND G.GP_PAYGROUP = C.GP_PAYGROUP AND G.ORIG_CAL_RUN_ID = C.ORIG_CAL_RUN_ID AND C.CAL_ID = G.CAL_ID AND G.RSLT_SEG_NUM = ( SELECT MAX(G1.RSLT_SEG_NUM) FROM PS_GP_PYE_SEG_STAT G1 WHERE G1.EMPLID = G.EMPLID AND G.EMPL_RCD= G1.EMPL_RCD AND G1.CAL_RUN_ID = G.CAL_RUN_ID AND G1.GP_PAYGROUP = G.GP_PAYGROUP )) ,C.CAL_RUN_ID , C.CAL_ID ,J.OFF_CYCLE,A.GP_PAYGROUP, A.COMPANY , A.DEPTID , A.EMPL_STATUS , A.HR_STATUS , (CASE WHEN A.EFFDT = B.PRD_BGN_DT THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 17000 AND MESSAGE_NBR = 5090) WHEN A.EFFDT <> %DateAdd(%DateIn(B.PRD_END_DT),1) THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 17000 AND MESSAGE_NBR = 5091) END ) TERMINATION_TYPE ,CASE WHEN J.RUN_OPEN_IND = 'Y' THEN 'O' WHEN J.RUN_FINALIZED_IND = 'Y' AND J.PMT_SENT_IND <> 'Y' THEN 'F' WHEN J.PMT_SENT_IND = 'Y' THEN 'B' END ,(%Sql(GP_WC_FINAL_TS,J.CAL_RUN_ID)), %DateDiff(A.EFFDT , %Datein(%CurrentDateOut)) FROM PS_JOB A , PS_GP_CAL_RUNOF_VW B , PS_GP_PYE_PRC_STAT C ,PS_GP_CAL_RUN J WHERE J.CAL_RUN_ID = B.CAL_RUN_ID AND B.CALC_TYPE = 'P' AND A.EFFDT BETWEEN B.PRD_BGN_DT AND %DateAdd(%DateIn(B.PRD_END_DT),1) AND A.EFFDT = ( SELECT MAX(E.EFFDT) FROM PS_JOB E WHERE E.EMPLID = A.EMPLID AND E.EMPL_RCD = A.EMPL_RCD AND E.HR_STATUS = 'I' AND E.GP_PAYGROUP = A.GP_PAYGROUP ) 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.CALC_TYPE = 'P' AND C.PRD_TYPE = 'C' AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.GP_PAYGROUP = A.GP_PAYGROUP AND A.EFFDT 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) OR (B.CAL_ID = ' ' AND J.OFF_CYCLE = 'Y')) 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 %DateAdd(%DateIn(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' AND CALC_TYPE = 'P') UNION SELECT A.EMPLID ,( SELECT B.NAME FROM PS_NAMES B WHERE B.EMPLID = A.EMPLID AND B.NAME_TYPE = 'PRI' AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_NAMES B1 WHERE B1.EMPLID = B.EMPLID AND B1.NAME_TYPE = B.NAME_TYPE AND B1.EFFDT <= %CurrentDateIn)), A.EMPL_RCD , A.ACTION, ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_TBL ACT WHERE ACT.ACTION = A.ACTION AND ACT.EFF_STATUS = 'A' AND ACT.EFFDT = ( SELECT MAX(T.EFFDT) FROM PS_ACTION_TBL T WHERE ACT.ACTION = T.ACTION AND T.EFFDT <= %CurrentDateIn)),A.EFFDT , A.TERMINATION_DT ,G.PIN_GROSS_VAL , G.PIN_NET_VAL ,C.CAL_RUN_ID , C.CAL_ID ,J.OFF_CYCLE,A.GP_PAYGROUP, A.COMPANY , A.DEPTID , A.EMPL_STATUS , A.HR_STATUS , ( ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 17000 AND MESSAGE_NBR = 5092) ) TERMINATION_TYPE ,CASE WHEN J.RUN_OPEN_IND = 'Y' THEN 'O' WHEN J.RUN_FINALIZED_IND = 'Y' AND J.PMT_SENT_IND <> 'Y' THEN 'F' WHEN J.PMT_SENT_IND = 'Y' THEN 'B' END ,(%Sql(GP_WC_FINAL_TS,J.CAL_RUN_ID)) , %DateDiff(A.EFFDT , %Datein(%CurrentDateOut)) FROM PS_JOB A , PS_GP_CAL_RUNOF_VW B , PS_GP_PYE_PRC_STAT C, PS_GP_PYE_SEG_STAT G ,PS_GP_CAL_RUN J WHERE C.CAL_RUN_ID = J.CAL_RUN_ID AND 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 %DatePart(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) OR (B.CAL_ID = ' ' AND J.OFF_CYCLE = 'Y')) AND C.PRD_TYPE = 'R' AND C.CALC_TYPE = 'P' AND G.EMPLID = C.EMPLID AND G.EMPL_RCD = C.EMPL_RCD AND G.CAL_RUN_ID = C.CAL_RUN_ID AND G.RSLT_VER_NUM = C.RSLT_VER_NUM AND G.RSLT_REV_NUM = C.RSLT_REV_NUM AND G.GP_PAYGROUP = C.GP_PAYGROUP AND G.ORIG_CAL_RUN_ID = C.ORIG_CAL_RUN_ID AND G.CAL_ID = C.CAL_ID AND G.RSLT_SEG_NUM = ( SELECT MAX(G1.RSLT_SEG_NUM) FROM PS_GP_PYE_SEG_STAT G1 WHERE G1.EMPLID = G.EMPLID AND G.EMPL_RCD= G1.EMPL_RCD AND G1.CAL_RUN_ID = G.CAL_RUN_ID AND G1.GP_PAYGROUP = G.GP_PAYGROUP ) AND EXISTS( SELECT * FROM PS_GP_TRGR_VALUE WHERE COUNTRY = C.COUNTRY AND RECNAME = 'JOB' AND TRGR_TYPE = 'R' AND FIELDNAME = 'ACTION' AND ( %DateAdd(%DateIn(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 A.EMPLID ,( SELECT B.NAME FROM PS_NAMES B WHERE B.EMPLID = A.EMPLID AND B.NAME_TYPE = 'PRI' AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_NAMES B1 WHERE B1.EMPLID = B.EMPLID AND B1.NAME_TYPE = B.NAME_TYPE AND B1.EFFDT <= %CurrentDateIn)), A.EMPL_RCD , A.ACTION, ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_TBL ACT WHERE ACT.ACTION = A.ACTION AND ACT.EFF_STATUS = 'A' AND ACT.EFFDT = ( SELECT MAX(T.EFFDT) FROM PS_ACTION_TBL T WHERE ACT.ACTION = T.ACTION AND T.EFFDT <= %CurrentDateIn)), A.EFFDT , A.TERMINATION_DT ,( SELECT G.PIN_GROSS_VAL FROM PS_GP_PYE_SEG_STAT G WHERE G.EMPLID = C.EMPLID AND G.EMPL_RCD = C.EMPL_RCD AND G.CAL_RUN_ID = C.CAL_RUN_ID AND G.RSLT_VER_NUM = C.RSLT_VER_NUM AND G.RSLT_REV_NUM = C.RSLT_REV_NUM AND G.GP_PAYGROUP = C.GP_PAYGROUP AND G.CAL_ID = C.CAL_ID AND G.RSLT_SEG_NUM = ( SELECT MAX(G1.RSLT_SEG_NUM) FROM PS_GP_PYE_SEG_STAT G1 WHERE G1.EMPLID = G.EMPLID AND G.EMPL_RCD= G1.EMPL_RCD AND G1.CAL_RUN_ID = G.CAL_RUN_ID AND G1.GP_PAYGROUP = G.GP_PAYGROUP )), ( SELECT G.PIN_NET_VAL FROM PS_GP_PYE_SEG_STAT G WHERE G.EMPLID = C.EMPLID AND G.EMPL_RCD = C.EMPL_RCD AND G.CAL_RUN_ID = C.CAL_RUN_ID AND G.RSLT_VER_NUM = C.RSLT_VER_NUM AND G.RSLT_REV_NUM = C.RSLT_REV_NUM AND G.GP_PAYGROUP = C.GP_PAYGROUP AND C.CAL_ID = G.CAL_ID AND G.RSLT_SEG_NUM = ( SELECT MAX(G1.RSLT_SEG_NUM) FROM PS_GP_PYE_SEG_STAT G1 WHERE G1.EMPLID = G.EMPLID AND G.EMPL_RCD= G1.EMPL_RCD AND G1.CAL_RUN_ID = G.CAL_RUN_ID AND G1.GP_PAYGROUP = G.GP_PAYGROUP )) ,C.CAL_RUN_ID , C.CAL_ID ,J.OFF_CYCLE,A.GP_PAYGROUP,A.COMPANY , A.DEPTID , A.EMPL_STATUS , A.HR_STATUS , ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 17000 AND MESSAGE_NBR = 5093) TERMINATION_TYPE ,CASE WHEN J.RUN_OPEN_IND = 'Y' THEN 'O' WHEN J.RUN_FINALIZED_IND = 'Y' AND J.PMT_SENT_IND <> 'Y' THEN 'F' WHEN J.PMT_SENT_IND = 'Y' THEN 'B' END ,(%Sql(GP_WC_FINAL_TS,J.CAL_RUN_ID)) , %DateDiff(A.EFFDT , %Datein(%CurrentDateOut)) FROM PS_JOB A , PS_GP_CAL_RUNOF_VW B , PS_GP_PYE_PRC_STAT C ,PS_GP_CAL_RUN J WHERE J.CAL_RUN_ID = C.CAL_RUN_ID AND B.CALC_TYPE = 'P' AND A.EFFDT BETWEEN B.PRD_BGN_DT AND %DateAdd(%DateIn(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.CALC_TYPE = 'P' 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) OR (B.CAL_ID = ' ' AND J.OFF_CYCLE = 'Y')) 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 %DateAdd(%DateIn(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 |  EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 3 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 4 | ACTION |
Character(3) | VARCHAR2(3) NOT NULL | HR Action |
| 5 | ACTION_DESCR | Character(50) | VARCHAR2(50) NOT NULL | Action Description |
| 6 | EFFDT |
Date(10) | DATE |
Effective Date
Default Value: %date |
| 7 | TERMINATION_DT | Date(10) | DATE | Termination Date |
| 8 | PIN_GROSS_VAL | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Gross Pay Value |
| 9 | PIN_NET_VAL | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Net Pay Value |
| 10 | CAL_RUN_ID |
Character(18) | VARCHAR2(18) NOT NULL | Calendar Run Id |
| 11 | CAL_ID |
Character(18) | VARCHAR2(18) NOT NULL | calendar id |
| 12 | GP_OFF_CYCLE | Character(2) | VARCHAR2(2) NOT NULL |
OFF CYCLE Translate values
N=No Y=Yes |
| 13 | GP_PAYGROUP | Character(10) | VARCHAR2(10) NOT NULL | Global Payroll pay group |
| 14 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL |
Company
Prompt Table: COMPANY_TBL |
| 15 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL |
Department
Prompt Table: DEPT_TBL |
| 16 | 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 |
| 17 | HR_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
HR Status
A=Active I=Inactive |
| 18 | GP_WC_WORKITM_TYPE | Character(254) | VARCHAR2(254) NOT NULL | GP WorkCenter WorkItem Type |
| 19 | GP_CAL_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Calculation status
B=Finalized with Banking F=Finalized O=Open |
| 20 | GP_WC_FINAL_DAYS | Number(4,0) | SMALLINT NOT NULL | Finalized day count |
| 21 | GP_WC_LIMIT_DAY | Number(4,0) | SMALLINT NOT NULL | Work item day count |