EMPLIDCOUNTRYTRGR_EFFDTTRGR_EVENT_IDLANGUAGE_CDGP_PAYGROUPCAL_RUN_IDB=Finalized with Banking
F=Finalized
O=Open
GP_WC_RTOLNG_VW(SQL View) |
Index Back |
|---|---|
GP WC RetroGP WC Retro |
| SELECT DISTINCT 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.COUNTRY , A.TRGR_EFFDT , A.TRGR_EVENT_ID, E.LANGUAGE_CD, E.DESCR , A.RECNAME , A.FIELDNAME , %Cast(A.TRGR_FLD_VAL_DT, Date,Character) %Concat (CASE WHEN A.TRGR_FLD_VAL_NUM = 0 THEN '' ELSE %NumToChar(A.TRGR_FLD_VAL_NUM) END) %Concat A.TRGR_FLD_VAL_CHAR , A.TRGR_DESCR , C.GP_PAYGROUP , C.CAL_RUN_ID ,C.CAL_ID ,B.COMPANY,B.DEPTID, CASE WHEN ( SELECT DISTINCT D1.RUN_OPEN_IND FROM PS_GP_CAL_RUN D1 WHERE D1.CAL_RUN_ID = D.CAL_RUN_ID AND RUN_FINALIZED_IND = 'N') = 'Y' THEN 'O' WHEN ( SELECT DISTINCT D.RUN_FINALIZED_IND FROM PS_GP_CAL_RUN D2 WHERE D2.CAL_RUN_ID = D.CAL_RUN_ID AND D2.PMT_SENT_IND = 'N') = 'Y' THEN 'F' WHEN ( SELECT DISTINCT D.RUN_FINALIZED_IND FROM PS_GP_CAL_RUN D3 WHERE D3.CAL_RUN_ID = C.CAL_RUN_ID AND D3.PMT_SENT_IND = 'Y') = 'Y' THEN 'B' END ,(%Sql(GP_WC_FINAL_TS,C.CAL_RUN_ID)), %DateDiff(A.TRGR_EFFDT , %Datein(%CurrentDateOut)) FROM (PS_GP_RTO_TRGR A LEFT OUTER JOIN PS_GP_RTO_EVT_LNG E ON A.COUNTRY = E.COUNTRY AND A.TRGR_EVENT_ID = E.TRGR_EVENT_ID ) , PS_JOB B , PS_GP_CAL_RUNOF_VW C, PS_GP_CAL_RUN D WHERE A.EMPLID = B.EMPLID AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_JOB B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.EMPL_RCD = B_ED.EMPL_RCD AND B_ED.EFFDT <= %CurrentDateIn) AND B.EFFSEQ = ( SELECT MAX(B_ES.EFFSEQ) FROM PS_JOB B_ES WHERE B.EMPLID = B_ES.EMPLID AND B.EMPL_RCD = B_ES.EMPL_RCD AND B.EFFDT = B_ES.EFFDT) AND C.CAL_RUN_ID = D.CAL_RUN_ID AND B.GP_PAYGROUP = C.GP_PAYGROUP AND A.COUNTRY = C.COUNTRY AND A.COUNTRY = D.COUNTRY AND ((A.TRGR_EFFDT < C.PRD_BGN_DT AND A.TRGR_STATUS ='U') OR ( A.CAL_RUN_ID = C.CAL_RUN_ID AND A.TRGR_STATUS = 'P')) AND EXISTS ( SELECT F.GP_PAYGROUP FROM PS_GP_WC_USR_PYGRP F WHERE ( F.GP_PAYGROUP = B.GP_PAYGROUP )) |
| # | 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 | COUNTRY |
Character(3) | VARCHAR2(3) NOT NULL | Country |
| 4 | TRGR_EFFDT |
Date(10) | DATE | Trigger Effective Date |
| 5 | TRGR_EVENT_ID |
Character(10) | VARCHAR2(10) NOT NULL | Trigger Event ID |
| 6 | LANGUAGE_CD |
Character(3) | VARCHAR2(3) NOT NULL | Language Code |
| 7 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 8 | RECNAME | Character(15) | VARCHAR2(15) NOT NULL | Record (Table) Name (see PSRECDEFN). |
| 9 | FIELDNAME | Character(18) | VARCHAR2(18) NOT NULL | Field Name (see PSDBFIELD). |
| 10 | FIELD_VALUE | Character(30) | VARCHAR2(30) NOT NULL | Field Value |
| 11 | TRGR_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Trigger Tag |
| 12 | GP_PAYGROUP |
Character(10) | VARCHAR2(10) NOT NULL | Global Payroll pay group |
| 13 | CAL_RUN_ID |
Character(18) | VARCHAR2(18) NOT NULL | Calendar Run Id |
| 14 | CAL_ID | Character(18) | VARCHAR2(18) NOT NULL | calendar id |
| 15 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 16 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 17 | GP_CAL_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Calculation status
B=Finalized with Banking F=Finalized O=Open |
| 18 | GP_WC_FINAL_DAYS | Number(4,0) | SMALLINT NOT NULL | Finalized day count |
| 19 | GP_WC_LIMIT_DAY | Number(4,0) | SMALLINT NOT NULL | Work item day count |