GP_BNK_EXCP_VW1(SQL View) |
Index Back |
|---|---|
Pymt Recon Exception ViewSelects from the Bank file transactions updated with a recon status <> reconciled |
| SELECT DISTINCT C.BANK_CD , C.ACCOUNT_EC_ID , C.BNK_FILE_ID , C.BK_PYMT_NBR , C.BK_TRAN_AMT , C.RECON_BNK_DT , C.TRAN_CD , C.RECON_STAT , D.CURRENCY_CD FROM PS_GP_PYMT_BNK_TBL A , PS_GP_PAYMENT B , PS_GP_BNK_FILE_DTL C , PS_SRC_BANK D WHERE A.CAL_RUN_ID = B.CAL_RUN_ID AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.GP_PAYGROUP = B.GP_PAYGROUP AND A.CAL_ID = B.CAL_ID AND A.ORIG_CAL_RUN_ID = B.ORIG_CAL_RUN_ID AND A.RSLT_SEG_NUM = B.RSLT_SEG_NUM AND A.PIN_NUM = B.PIN_NUM AND D.BANK_CD = C.BANK_CD AND D.ACCOUNT_EC_ID = C.ACCOUNT_EC_ID AND A.PYMT_NBR <> C.BK_PYMT_NBR AND C.RECON_STAT IN ('04', '06', '08', '11', '07', '09') |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  BANK_CD |
Character(11) | VARCHAR2(11) NOT NULL | Bank ID |
| 2 | ACCOUNT_EC_ID |
Character(35) | VARCHAR2(35) NOT NULL | Account Number used in banking. |
| 3 | BNK_FILE_ID |
Number(10,0) | DECIMAL(10) NOT NULL | Auto generated number when Bank File is loaded |
| 4 | BK_PYMT_NBR | Number(18,0) | DECIMAL(18) NOT NULL | Bank Transaction Payment Number |
| 5 | BK_TRAN_AMT | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Bank Transaction Amount |
| 6 | RECON_BNK_DT | Date(10) | DATE |
Reconciliation Bank Date
Default Value: %date |
| 7 | TRAN_CD | Character(2) | VARCHAR2(2) NOT NULL |
Bank Transaction Code
11=Check 22=Bank Transfer 33=Wire Transfer |
| 8 | RECON_STAT | Character(2) | VARCHAR2(2) NOT NULL |
Reconciliation Status
01=Unreconciled 02=Reconciled 03=Date Difference 04=Not Found 05=Amount Difference 06=Duplicate 07=Expired 08=Stop Payment 09=Unclaimed 10=Different Pay Method 11=Void 12=Re-Numbered |
| 9 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL |
Currency Code
Default Value: GP_PAYMENT.CURRENCY_CD |