PYKB_US_RET_VW(SQL View) |
Index Back |
|---|---|
Payroll Insights US RetropayPayroll Insights US Retropay View |
| SELECT A.RETROPAY_SEQ_NO ,A.EMPLID ,A.EMPL_RCD ,A.RETROPAY_EFFDT ,A.RETRO_TRG_EVENT_ID ,A.SEQNUM ,A.RECNAME AS PY_RETRO_SRC ,A.ERNCD ,B.COMPANY ,B.PAYGROUP ,D.RETROPAY_PRCS_FLAG ,D.RETRO_PAY_END_DT ,D.PROC_DTTMSTAMP FROM PS_RETROPAY_TRGDTL A ,PS_JOB B ,PS_PAYGROUP_TBL C ,PS_RETROPAY_RQST D WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND B.EFFDT = ( SELECT MAX(B2.EFFDT) FROM PS_JOB B2 WHERE B2.EMPLID = B.EMPLID AND B2.EMPL_RCD = B.EMPL_RCD AND B2.EFFDT <= %CurrentDateIn) AND B.EFFSEQ = ( SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE B3.EMPLID = B.EMPLID AND B3.EMPL_RCD = B.EMPL_RCD AND B3.EFFDT = B.EFFDT) AND B.COMPANY = C.COMPANY AND B.PAYGROUP = C.PAYGROUP AND C.EFFDT = ( SELECT MAX(C2.EFFDT) FROM PS_PAYGROUP_TBL C2 WHERE C2.COMPANY = C.COMPANY AND C2.PAYGROUP = C.PAYGROUP AND C2.EFFDT <= %CurrentDateIn) AND C.COUNTRY = 'USA' AND A.RETROPAY_SEQ_NO = D.RETROPAY_SEQ_NO AND A.EMPLID = D.EMPLID AND A.RETROPAY_EFFDT = D.RETROPAY_EFFDT AND D.RETRO_PAY_END_DT IS NOT NULL |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  RETROPAY_SEQ_NO |
Character(15) | VARCHAR2(15) NOT NULL | Retro Pay Sequence Number |
| 2 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 3 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 4 | RETROPAY_EFFDT |
Date(10) | DATE | Retro Pay Effective Date |
| 5 | RETRO_TRG_EVENT_ID |
Number(3,0) | SMALLINT NOT NULL | Retroactive Trigger Event ID |
| 6 | SEQNUM |
Number(3,0) | SMALLINT NOT NULL | Sequence Number |
| 7 | PY_RETRO_SRC |
Character(15) | VARCHAR2(15) NOT NULL | Payroll Retro Source |
| 8 | ERNCD |
Character(3) | VARCHAR2(3) NOT NULL | Earnings Code |
| 9 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 10 | PAYGROUP | Character(3) | VARCHAR2(3) NOT NULL | Pay Group |
| 11 | RETROPAY_PRCS_FLAG | Character(1) | VARCHAR2(1) NOT NULL |
If translate values are added/modified for RETROPAY_PRCS_FLAG field, same changes must be done for RETROPAY_PRCS_FLG1 field.
A=Action Required C=Calculated F=Confirmed Payment M=Manually Loaded to Paysheet N=Not Processed P=Loaded to Paysheets R=Recalculate Request S=Suspended V=Paycheck Reversed W=Cancelled / Withdrawn X=Extracted |
| 12 | RETRO_PAY_END_DT | Date(10) | DATE | Retro Pay - Pay End Date |
| 13 | PROC_DTTMSTAMP | DateTime(26) | TIMESTAMP | Process Date Timestamp |