VCHR_CLOSE_VW3

(SQL View)
 Index Back

SELECT A.BUSINESS_UNIT , A.VOUCHER_ID , A.BANK_CD , A.BANK_ACCT_KEY , A.PYMNT_ID , B.PYMNT_ID_REF , A.PYMNT_GROSS_AMT , A.CURRENCY_PYMNT , A.PAID_AMT , B.PYMNT_AMT , B.PYMNT_DT , B.PYMNT_STATUS , B.REMIT_SETID , B.REMIT_VENDOR , C.VENDOR_NAME_SHORT , B.BANK_SETID , A.PYMNT_SELCT_STATUS , A.PREPAID_STATUS , D.TXN_CURRENCY_CD , B.PYMNT_METHOD FROM PS_PYMNT_VCHR_XREF A , PS_PAYMENT_TBL B , PS_VENDOR C , 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 B.REMIT_SETID = C.SETID AND B.REMIT_VENDOR = C.VENDOR_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 ('A','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_CD Character(5) VARCHAR2(5) NOT NULL Bank Code
4 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
5 PYMNT_ID Character(10) VARCHAR2(10) NOT NULL Payment Number
6 PYMNT_ID_REF Character(20) VARCHAR2(20) NOT NULL Payment Reference
7 PYMNT_GROSS_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Gross Payment Amount
8 CURRENCY_PYMNT Character(3) VARCHAR2(3) NOT NULL Payment Currency
9 PAID_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Paid Amount
10 PYMNT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Payment Amount
11 PYMNT_DT Date(10) DATE Payment Date
12 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
13 REMIT_SETID Character(5) VARCHAR2(5) NOT NULL Remit SetID
14 REMIT_VENDOR Character(10) VARCHAR2(10) NOT NULL Remit Vendor
15 VENDOR_NAME_SHORT Character(14) VARCHAR2(14) NOT NULL Short Vendor Name
16 BANK_SETID Character(5) VARCHAR2(5) NOT NULL The PeopleSoft tableset ID associated with a given bank/counterparty.
17 PYMNT_SELCT_STATUS Character(1) VARCHAR2(1) NOT NULL Payment Selection Status
C=Closed
D=Negative Voucher Sum
E=Externally Paid
F=Federal Sanctions Error
I=In Process - EFT
N=Not Selected for Payment
O=Financial Sanctions Review
P=Paid
R=Requested for Payment
S=Settlement Payment
V=Convert Error
X=Canceled
Y=Prepaid
18 PREPAID_STATUS Character(1) VARCHAR2(1) NOT NULL Prepaid Status
A=Applied
B=Pending Application
C=Pending Unapply
D=Pending Change
F=Fully Applied
I=Inactive
P=Available Prepayment
R=Restricted
U=Unapplied
X=Cancelled
19 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency
20 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