VCHR_PYMNT_VW

(SQL View)

SELECT A.BUSINESS_UNIT , A.VOUCHER_ID , A.BANK_SETID , A.BANK_CD , A.BANK_ACCT_KEY , B.PYMNT_ID_REF , B.PYMNT_DT , A.PAID_AMT , B.PYMNT_AMT , A.CURRENCY_PYMNT , B.PYMNT_STATUS , B.PYMNT_METHOD , ' ' , ' ' FROM PS_PYMNT_VCHR_XREF A , PS_PAYMENT_TBL B , PS_VOUCHER D WHERE 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 AND A.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.VOUCHER_ID = D.VOUCHER_ID AND ((D.VOUCHER_STYLE = 'PPAY' AND A.PYMNT_CNT = 1) OR (D.VOUCHER_STYLE <> 'PPAY') AND PREPAID_STATUS IN ('U'))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

2 VOUCHER_ID Character(8) VARCHAR2(8) NOT NULL Voucher ID

3 BANK_SETID Character(5) VARCHAR2(5) NOT NULL The PeopleSoft tableset ID associated with a given bank/counterparty.

4 BANK_CD Character(5) VARCHAR2(5) NOT NULL Bank Code

5 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

6 PYMNT_ID_REF Character(20) VARCHAR2(20) NOT NULL Payment Reference

7 PYMNT_DT Date(10) DATE Payment Date

8 PAID_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Paid Amount

9 PYMNT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Payment Amount

10 CURRENCY_PYMNT Character(3) VARCHAR2(3) NOT NULL Payment Currency

11 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

12 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

13 PREPAID_APPLIED_BU Character(5) VARCHAR2(5) NOT NULL Applied Business Unit

14 PREPAID_APPL_VCHR Character(8) VARCHAR2(8) NOT NULL Applied Voucher ID

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
2	VOUCHER_ID	Character(8)	VARCHAR2(8) NOT NULL	Voucher ID
3	BANK_SETID	Character(5)	VARCHAR2(5) NOT NULL	The PeopleSoft tableset ID associated with a given bank/counterparty.
4	BANK_CD	Character(5)	VARCHAR2(5) NOT NULL	Bank Code
5	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
6	PYMNT_ID_REF	Character(20)	VARCHAR2(20) NOT NULL	Payment Reference
7	PYMNT_DT	Date(10)	DATE	Payment Date
8	PAID_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Paid Amount
9	PYMNT_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Payment Amount
10	CURRENCY_PYMNT	Character(3)	VARCHAR2(3) NOT NULL	Payment Currency
11	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
12	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
13	PREPAID_APPLIED_BU	Character(5)	VARCHAR2(5) NOT NULL	Applied Business Unit
14	PREPAID_APPL_VCHR	Character(8)	VARCHAR2(8) NOT NULL	Applied Voucher ID