GP_BNK_MAN_VW

(SQL View)
Index Back

Payment Recon View

Used in Manual Reconciliation to display System Transactions and update the Payment Table in GP

SELECT DISTINCT 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 , A.PYMT_NBR , B.SRC_BANK_ID , A.PYMT_DT , A.GP_PMT_AMT , A.CURRENCY_CD , A.BNK_FILE_ID , B.PAYMENT_MTHD , A.BK_SEQ_NBR , A.RECON_DT , A.RECON_STAT , A.RECON_MTHD , A.LASTUPDOPRID , A.DESCR , C.BANK_CD , C.ACCOUNT_EC_ID 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 C.BANK_CD = D.BANK_CD AND C.ACCOUNT_EC_ID = D.ACCOUNT_EC_ID AND D.SRC_BANK_ID = B.SRC_BANK_ID AND A.PYMT_ID <> 'PRENOT' AND B.PMT_STATUS = 'F'

# 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 Rcd Nbr
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 PYMT_NBR Number(18,0) DECIMAL(18) NOT NULL Payment Number
12 SRC_BANK_ID Character(8) VARCHAR2(8) NOT NULL Source Bank ID
13 PYMT_DT Date(10) DATE Payment Date
14 GP_PMT_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL Amount field on GP PAYMENT table
15 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
16 BNK_FILE_ID Number(10,0) DECIMAL(10) NOT NULL Auto generated number when Bank File is loaded
17 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
18 BK_SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Bank File Transaction Sequence Number
19 RECON_DT Date(10) DATE Transaction Reconciliation Date
20 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
21 RECON_MTHD Character(2) VARCHAR2(2) NOT NULL Bank Reconciliation Method
10=Manual
20=Auto
22 LASTUPDOPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
23 DESCR Character(30) VARCHAR2(30) NOT NULL Description
24 BANK_CD Character(11) VARCHAR2(11) NOT NULL Bank ID
25 ACCOUNT_EC_ID Character(35) VARCHAR2(35) NOT NULL Account Number used in banking.