BNK_RCN_SAP_VW1

(SQL View)
 Index Back

Manual/Semi: AP Disbursements

Manual and Semi-Manual Reconciliation view for AP Disbursements

SELECT A.BANK_SETID ,A.BANK_CD ,A.BANK_ACCT_KEY ,A.PYMNT_ID ,A.PYMNT_ID_REF ,A.BNK_ID_NBR ,A.BANK_ACCOUNT_NUM ,A.VALUE_DT ,CASE WHEN A.PMT_ID = ' ' THEN A.PYMNT_ID_REF ELSE A.PMT_ID END ,A.NAME1 ,C.CURRENCY_CD ,%DecMult(SUM(B.PAID_AMT_BNK_BSE) , -1) ,A.PYMNT_METHOD ,A.PYMNT_STATUS ,A.PYMNT_RECONCILE_DT ,A.RECON_TYPE ,A.RECON_STATUS ,A.RECONCILE_OPRID ,A.STTLMNT_DT_ACTUAL ,'02' ,%DecMult(SUM(B.PAID_AMT_BNK_BSE) , -1) ,A.RECON_CYCLE_NBR ,A.RECORD_SEQ_NUMBER ,A.RECON_RUN_ID ,A.PYMNT_STATUS ,A.CASH_CLEARED_FLG FROM PS_PAYMENT_TBL A LEFT OUTER JOIN PS_PYMNT_VCHR_XREF B ON A.BANK_SETID = B.BANK_SETID AND A.BANK_CD = B.BANK_CD AND A.BANK_ACCT_KEY = B.BANK_ACCT_KEY AND A.PYMNT_ID = B.PYMNT_ID , PS_BANK_ACCT_DEFN C , PS_BANK_ACCT_CURR D WHERE C.SETID = A.BANK_SETID AND C.BANK_CD = A.BANK_CD AND C.BANK_CD_CPTY = A.BANK_CD AND C.BANK_ACCT_KEY = A.BANK_ACCT_KEY AND A.PYMNT_STATUS <> 'V' AND D.SETID = C.SETID AND D.BANK_CD = C.BANK_CD AND D.BANK_CD_CPTY = C.BANK_CD_CPTY AND D.BANK_ACCT_KEY = C.BANK_ACCT_KEY AND D.CURRENCY_CD = A.CURRENCY_PYMNT GROUP BY A.BANK_SETID ,A.BANK_CD ,A.BANK_ACCT_KEY ,A.PYMNT_ID ,A.PYMNT_ID_REF ,A.BNK_ID_NBR ,A.BANK_ACCOUNT_NUM ,A.VALUE_DT ,CASE WHEN A.PMT_ID = ' ' THEN A.PYMNT_ID_REF ELSE A.PMT_ID END ,A.NAME1 ,C.CURRENCY_CD ,A.PYMNT_METHOD ,A.PYMNT_STATUS ,A.PYMNT_RECONCILE_DT ,A.RECON_TYPE ,A.RECON_STATUS ,A.RECONCILE_OPRID ,A.STTLMNT_DT_ACTUAL ,A.RECON_CYCLE_NBR ,A.RECORD_SEQ_NUMBER ,A.RECON_RUN_ID ,A.PYMNT_STATUS ,A.CASH_CLEARED_FLG

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BANK_SETID Character(5) VARCHAR2(5) NOT NULL The PeopleSoft tableset ID associated with a given bank/counterparty.
2 BANK_CD Character(5) VARCHAR2(5) NOT NULL Bank Code
3 BANK_ACCT_KEY Character(4) VARCHAR2(4) NOT NULL A user defined unique identifier that facilitates the identification of a given account with a given bank
4 PYMNT_ID Character(10) VARCHAR2(10) NOT NULL Payment Number
5 PYMNT_ID_REF Character(20) VARCHAR2(20) NOT NULL Payment Reference
6 BNK_ID_NBR Character(20) VARCHAR2(20) NOT NULL A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.
7 BANK_ACCOUNT_NUM Character(35) VARCHAR2(35) NOT NULL The literal unique identifier associated with a given bank/counterparty account.
8 TRAN_DT Date(10) DATE Represents the transaction date in bank reconciliation processing.
9 TRAN_REF_ID Character(20) VARCHAR2(20) NOT NULL Represents the unique transaction identifier in bank reconciliation processing.
10 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
11 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
12 TRAN_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Represents a transaction amount in bank reconciliation processing.
13 PYMNT_METHOD Character(3) VARCHAR2(3) NOT NULL Payment Method
ACH=Automated Clearing House
BEF=Draft - Customer EFT
BOO=Draft - Customer Initiated
CHK=System Check
D=Deposit
DD=Direct Debit
DFT=Draft - Supplier Initiated
DRA=Draft
EFT=Electronic Funds Transfer
GE=Giro - EFT
GM=Giro - Manual
LC=Letter of Credit
MAN=Manual Check
TRW=Treasury Wire
WIR=Wire Transfer
14 PYMNT_STATUS Character(1) VARCHAR2(1) NOT NULL Payment Status
A=Alignment
E=ACH/EFT Prenote
L=Stale Dated Payment
N=Reprinted with same ref number
O=Overflow
P=Paid
R=Replaced
S=Stopped
T=Undo Escheatment
U=Undo Cancel
V=Void
W=Escheated Check
X=Deleted
15 RECONCILE_DT Date(10) DATE The transaction reconciliation date.
16 RECON_TYPE Character(1) VARCHAR2(1) NOT NULL Represents a series of options as to how a treasury wishes to perform reconciliation for a given bank
A=Automatically Reconciled
B=Balance System(s) to BankTran
F=Forced to Reconcile
M=Manually Reconciled
S=Semi Manual
U=Unreconciled
17 RECON_STATUS Character(3) VARCHAR2(3) NOT NULL Reconciliation Status
ALG=Alignment Check
AMB=Ambiguous Match
AMT=Amounts Not Equal
CUR=Currency not Defined
DAT=Tran Date <> Value Date
DPI=Duplicate Input Transactions
DUP=Duplicate Transaction
MSC=Miscellaneous Transaction
NAM=Payee Names Not Equal
NTB=Not Found in Statement
NTF=Not Found In System
NTP=Not Found in POS Data
NUL=Null Transaction ID Ref
OVR=Overflow
PND=Pending Reconciliation
REC=Reconciled
RPR=Reprinted Check
RR=Requires Review - BSAC
STP=Stop Payment
TYP=Wrong Transaction Type
UNR=Unreconciled
VOI=Voided Check
18 RECONCILE_OPRID Character(30) VARCHAR2(30) NOT NULL The user ID that performed the reconciliation for a given transaction.
19 STTLMNT_DT_ACTUAL Date(10) DATE Date in which a deal transaction is actually settled with a counterparty.
20 RECON_SOURCE Character(2) VARCHAR2(2) NOT NULL Represents the source system table(s) for reconciling items.
00=Bank Statement
01=Receivables Deposits
02=Payables
03=Treasury
04=Receivables Drafts
05=External Transactions
06=Receivables Payments
07=Direct Debits
21 BANK_ACCT_RVL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Field used to store the amount on a transaction revalued in the base currency of the transactions bank account.
22 RECON_CYCLE_NBR Number(10,0) DECIMAL(10) NOT NULL The bank statement ID.
23 RECORD_SEQ_NUMBER Number(10,0) DECIMAL(10) NOT NULL An internal work field utilized to enumerate database records in bank statement processing.
24 RECON_RUN_ID Character(15) VARCHAR2(15) NOT NULL Recon Run ID
25 STATUS Character(1) VARCHAR2(1) NOT NULL Status Indicator
A=Active
I=Inactive
26 CASH_CLEARED_FLG Character(1) VARCHAR2(1) NOT NULL Cash Cleared Flag