BNK_CRD_RCN_VW2 (SQL View)	Index Back
Credit Card Stmt Exceptions Credit Card Reconciliation View - Not found in statement

BNK_CRD_RCN_VW2

(SQL View)

Credit Card Stmt Exceptions

Credit Card Reconciliation View - Not found in statement

SELECT B.BNK_ID_NBR, B.BANK_ACCOUNT_NUM, B.REMIT_LOCATION, B.CRD_TYPE, B.TRAN_DT, 0, B.TRAN_AMT_GROSS, B.TRAN_AMT_GROSS, B.NAME1, 'NTB' FROM PS_BNK_RCN_CRD B WHERE B.REMIT_TYPE = '3' AND NOT EXISTS (SELECT 'X' FROM PS_BNK_STMT_CRD A 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)

# 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 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

4 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

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

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

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

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

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

10 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	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
4	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
5	TRAN_DT	Date(10)	DATE	Represents the transaction date in bank reconciliation processing.
6	TRAN_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Represents a transaction amount in bank reconciliation processing.
7	TRAN_AMT_GROSS	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Credit Card Reconciliation View - Transaction amount gross
8	TRAN_AMT_DIFF	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Credit Card Reconciliation View -transaction amount difference
9	NAME1	Character(40)	VARCHAR2(40) NOT NULL	Name 1
10	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