EMPLIDEMPL_RCDACTIONLANGUAGE_CDEFFDTDefault Value: %date
CAL_RUN_IDCAL_IDB=Finalized with Banking
F=Finalized
O=Open
GP_WC_TERLNG_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, LNG.LANGUAGE_CD, LNG.ACTION_DESCR, A.EFFDT, C.CAL_RUN_ID , C.CAL_ID ,A.GP_PAYGROUP, (CASE WHEN A.EFFDT = B.PRD_BGN_DT THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 17000 AND MESSAGE_NBR = 5090) WHEN A.EFFDT <> %DateAdd(%DateIn(B.PRD_END_DT),1) THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG 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 , PS_ACTION_LNG LNG 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') AND A.ACTION = LNG.ACTION AND LNG.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_ACTION_LNG B WHERE B.ACTION = LNG.ACTION AND B.LANGUAGE_CD = LNG.LANGUAGE_CD AND B.EFFDT <= %CurrentDateIn) 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, LNG.LANGUAGE_CD, LNG.ACTION_DESCR, A.EFFDT , C.CAL_RUN_ID , C.CAL_ID ,A.GP_PAYGROUP, ( ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG 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 , PS_ACTION_LNG LNG 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' ) ) AND A.ACTION = LNG.ACTION AND LNG.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_ACTION_LNG B WHERE B.ACTION = LNG.ACTION AND B.LANGUAGE_CD = LNG.LANGUAGE_CD AND B.EFFDT <= %CurrentDateIn) 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, LNG.LANGUAGE_CD, LNG.ACTION_DESCR, A.EFFDT , C.CAL_RUN_ID , C.CAL_ID ,A.GP_PAYGROUP, ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG 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 , PS_ACTION_LNG LNG 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' ) ) AND A.ACTION = LNG.ACTION AND LNG.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_ACTION_LNG B WHERE B.ACTION = LNG.ACTION AND B.LANGUAGE_CD = LNG.LANGUAGE_CD AND B.EFFDT <= %CurrentDateIn) |
| # | 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 | LANGUAGE_CD |
Character(3) | VARCHAR2(3) NOT NULL | Language Code |
| 6 | ACTION_DESCR | Character(50) | VARCHAR2(50) NOT NULL | Action Description |
| 7 | EFFDT |
Date(10) | DATE |
Effective Date
Default Value: %date |
| 8 | CAL_RUN_ID |
Character(18) | VARCHAR2(18) NOT NULL | Calendar Run Id |
| 9 | CAL_ID |
Character(18) | VARCHAR2(18) NOT NULL | calendar id |
| 10 | GP_PAYGROUP | Character(10) | VARCHAR2(10) NOT NULL | Global Payroll pay group |
| 11 | GP_WC_WORKITM_TYPE | Character(254) | VARCHAR2(254) NOT NULL | GP WorkCenter WorkItem Type |
| 12 | GP_CAL_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Calculation status
B=Finalized with Banking F=Finalized O=Open |
| 13 | GP_WC_FINAL_DAYS | Number(4,0) | SMALLINT NOT NULL | Finalized day count |
| 14 | GP_WC_LIMIT_DAY | Number(4,0) | SMALLINT NOT NULL | Work item day count |