GP_WC_BANK_VW(SQL View) |
Index Back |
|---|---|
GP WC Incomplete BankingGP WC Incomplete Banking |
| 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)) ,E.HIRE_DT , E.EMPL_STATUS,A.CAL_RUN_ID, E.GP_PAYGROUP,E.COMPANY , E.DEPTID ,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,A.CAL_RUN_ID)) ,(%Sql(GP_WC_LIMIT_DAYS,A.CAL_ID,A.CAL_RUN_ID) ) FROM PS_GP_PYE_PRC_STAT A ,PS_GP_CAL_RUN J , PS_JOB E WHERE A.CALC_TYPE = 'P' AND A.RSLT_REV_NUM = 1 AND A.RSLT_VER_NUM = 1 AND J.CAL_RUN_ID=A.CAL_RUN_ID AND EXISTS ( SELECT G.GP_PAYGROUP FROM PS_GP_WC_USR_PYGRP G WHERE G.GP_PAYGROUP = A.GP_PAYGROUP ) AND A.EMPLID = E.EMPLID AND E.EFFDT = ( SELECT MAX(E_ED.EFFDT) FROM PS_GP_JB_DESCR_VW E_ED WHERE E.EMPLID = E_ED.EMPLID AND E_ED.EFFDT <= %DateAdd(%DateIn(A.PRD_END_DT),1)) AND E.EFFSEQ = ( SELECT MAX(E_ES.EFFSEQ) FROM PS_GP_JB_DESCR_VW E_ES WHERE E.EMPLID = E_ES.EMPLID AND E.EFFDT = E_ES.EFFDT) AND E.GP_PAYGROUP <> ' ' AND ( 0 = ( SELECT COUNT(*) FROM PS_PYE_BANKACCT F WHERE F.EMPLID = A.EMPLID AND F.EFF_STATUS = 'A' )) |
| # | 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 | GP_PA_HIRE_DT | Date(10) | DATE | used in payroll analytics |
| 4 | 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 |
| 5 | CAL_RUN_ID | Character(18) | VARCHAR2(18) NOT NULL | Calendar Run Id |
| 6 | GP_PAYGROUP | Character(10) | VARCHAR2(10) NOT NULL | Global Payroll pay group |
| 7 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 8 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 9 | GP_CAL_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Calculation status
B=Finalized with Banking F=Finalized O=Open |
| 10 | GP_WC_FINAL_DAYS | Number(4,0) | SMALLINT NOT NULL | Finalized day count |
| 11 | GP_WC_LIMIT_DAY | Number(4,0) | SMALLINT NOT NULL | Work item day count |