GP_BNK_EXCP_VW

(SQL View)
 Index Back

Pymt Recon Exception View

Used to display system transactions from GP_PYMT_TBL where recon stat is not reconciled

SELECT A.CAL_RUN_ID , A.EMPLID , A.EMPL_RCD , A.GP_PAYGROUP , A.CAL_ID , A.ORIG_CAL_RUN_ID , A.RSLT_SEG_NUM , A.PIN_NUM , A.INSTANCE , A.PYMT_ID , B.SRC_BANK_ID , 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 , A.PYMT_NBR , A.GP_PMT_AMT , A.PYMT_DT , B.PAYMENT_MTHD , 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 A.INSTANCE = B.INSTANCE AND B.SRC_BANK_ID = D.SRC_BANK_ID 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 NOT IN ('04', '02', '06', '01')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id
2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
4 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
5 CAL_ID Character(18) VARCHAR2(18) NOT NULL calendar id
6 ORIG_CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Original Calendar Group ID
7 RSLT_SEG_NUM Number(4,0) SMALLINT NOT NULL Result Segment Number
8 PIN_NUM Number(8,0) INTEGER NOT NULL PIN Number
9 INSTANCE Number(3,0) SMALLINT NOT NULL Instance Number
10 PYMT_ID Character(6) VARCHAR2(6) NOT NULL Payment Identification
11 SRC_BANK_ID Character(8) VARCHAR2(8) NOT NULL Source Bank ID
12 BANK_CD Character(11) VARCHAR2(11) NOT NULL Bank ID
13 ACCOUNT_EC_ID Character(35) VARCHAR2(35) NOT NULL Account Number used in banking.
14 BNK_FILE_ID Number(10,0) DECIMAL(10) NOT NULL Auto generated number when Bank File is loaded
15 BK_PYMT_NBR Number(18,0) DECIMAL(18) NOT NULL Bank Transaction Payment Number
16 BK_TRAN_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL Bank Transaction Amount
17 RECON_BNK_DT Date(10) DATE Reconciliation Bank Date

Default Value: %date

18 TRAN_CD Character(2) VARCHAR2(2) NOT NULL Bank Transaction Code
11=Check
22=Bank Transfer
33=Wire Transfer
19 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
20 PYMT_NBR Number(18,0) DECIMAL(18) NOT NULL Payment Number
21 GP_PMT_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL Amount field on GP PAYMENT table
22 PYMT_DT Date(10) DATE Payment Date
23 PAYMENT_MTHD Character(1) VARCHAR2(1) NOT NULL Payment Method
A=Cash
C=Check
O=Pay Primary Account Only
P=Postal Order
T=Bank Transfer
U=Use Normal Distribution
W=Wire Transfer
24 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Default Value: GP_PAYMENT.CURRENCY_CD