BNK_CRD_RCN_VW (SQL View)	Index Back
Credit Card Amount Exceptions Credit Card Reconciliation View - Amounts not Equal

BNK_CRD_RCN_VW

(SQL View)

Credit Card Amount Exceptions

Credit Card Reconciliation View - Amounts not Equal

SELECT B.BNK_ID_NBR , B.BANK_ACCOUNT_NUM , B.CURRENCY_CD , B.REMIT_LOCATION , B.CRD_TYPE , B.TRAN_DT , B.TRAN_AMT_GROSS , A.TRAN_AMT_GROSS , A.TRAN_AMT_GROSS-B.TRAN_AMT_GROSS , B.NAME1 , 'AMT' FROM PS_BNK_STMT_CRD A , PS_BNK_RCN_CRD B WHERE (A.BNK_ID_NBR = B.BNK_ID_NBR AND A.BANK_ACCOUNT_NUM = B.BANK_ACCOUNT_NUM AND A.CRD_TYPE = B.CRD_TYPE AND A.REMIT_LOCATION = B.REMIT_LOCATION AND A.TRAN_DT = B.TRAN_DT AND A.TRAN_AMT_GROSS <> B.TRAN_AMT_GROSS AND B.REMIT_TYPE = '3')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BNK_ID_NBR Character(20) VARCHAR2(20) NOT NULL A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.

2 BANK_ACCOUNT_NUM Character(35) VARCHAR2(35) NOT NULL The literal unique identifier associated with a given bank/counterparty account.

3 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

4 REMIT_LOCATION Character(10) VARCHAR2(10) NOT NULL Represents the physical location as to where a point of sale transaction took place. The field i

5 CRD_TYPE Character(1) VARCHAR2(1) NOT NULL The credit card type.
D=Discovery
M=Master Card
N=Diners Card
O=Optima
V=VISA
X=American Express

6 TRAN_DT Date(10) DATE Represents the transaction date in bank reconciliation processing.

7 TRAN_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Represents a transaction amount in bank reconciliation processing.

8 TRAN_AMT_GROSS Signed Number(28,3) DECIMAL(26,3) NOT NULL Credit Card Reconciliation View - Transaction amount gross

9 TRAN_AMT_DIFF Signed Number(28,3) DECIMAL(26,3) NOT NULL Credit Card Reconciliation View -transaction amount difference

10 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1

11 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

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BNK_ID_NBR	Character(20)	VARCHAR2(20) NOT NULL	A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.
2	BANK_ACCOUNT_NUM	Character(35)	VARCHAR2(35) NOT NULL	The literal unique identifier associated with a given bank/counterparty account.
3	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
4	REMIT_LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Represents the physical location as to where a point of sale transaction took place. The field i
5	CRD_TYPE	Character(1)	VARCHAR2(1) NOT NULL	The credit card type. D=Discovery M=Master Card N=Diners Card O=Optima V=VISA X=American Express
6	TRAN_DT	Date(10)	DATE	Represents the transaction date in bank reconciliation processing.
7	TRAN_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Represents a transaction amount in bank reconciliation processing.
8	TRAN_AMT_GROSS	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Credit Card Reconciliation View - Transaction amount gross
9	TRAN_AMT_DIFF	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Credit Card Reconciliation View -transaction amount difference
10	NAME1	Character(40)	VARCHAR2(40) NOT NULL	Name 1
11	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