WTHD_PYMNT_VW0

(SQL View)

SELECT DISTINCT W.APPL_VENDOR_SETID , W.APPL_VENDOR_ID , X.BUSINESS_UNIT , X.VOUCHER_ID , X.PYMNT_ID , P.PYMNT_ID_REF , X.PYMNT_SELCT_STATUS , X.BANK_SETID , X.BANK_CD , X.BANK_ACCT_KEY , P.PYMNT_AMT , P.PYMNT_DT , P.SCHEDULE_ID , X.CURRENCY_PYMNT , X.TXN_CURRENCY_CD , P.PYMNT_METHOD FROM PS_PYMNT_VCHR_WTHD W , PS_PYMNT_VCHR_XREF X , PS_PAYMENT_TBL P , PS_BANK_CD_VW BNK WHERE W.BUSINESS_UNIT = X.BUSINESS_UNIT AND W.VOUCHER_ID = X.VOUCHER_ID AND ((W.PYMNT_CNT = X.PYMNT_CNT AND X.PYMNT_TYPE <> 'W' ) OR (W.PYMNT_CNT = X.ORIG_PYMNT_CNT AND X.PYMNT_TYPE = 'W' )) AND X.PYMNT_SELCT_STATUS='P' AND P.BANK_SETID = X.BANK_SETID AND P.BANK_CD = X.BANK_CD AND P.BANK_ACCT_KEY = X.BANK_ACCT_KEY AND P.PYMNT_ID = X.PYMNT_ID AND P.PYMNT_STATUS <> 'X' AND BNK.SETID = P.BANK_SETID AND BNK.BANK_CD = P.BANK_CD

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID

2 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

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

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

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

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

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

10 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

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

12 PYMNT_DT Date(10) DATE Payment Date

13 SCHEDULE_ID Character(10) VARCHAR2(10) NOT NULL Schedule ID

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

15 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency

16 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

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	VENDOR_SETID	Character(5)	VARCHAR2(5) NOT NULL	Vendor SetID
2	VENDOR_ID	Character(10)	VARCHAR2(10) NOT NULL	Vendor Identifier
3	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
4	VOUCHER_ID	Character(8)	VARCHAR2(8) NOT NULL	Voucher ID
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_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
8	BANK_SETID	Character(5)	VARCHAR2(5) NOT NULL	The PeopleSoft tableset ID associated with a given bank/counterparty.
9	BANK_CD	Character(5)	VARCHAR2(5) NOT NULL	Bank Code
10	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
11	PYMNT_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Payment Amount
12	PYMNT_DT	Date(10)	DATE	Payment Date
13	SCHEDULE_ID	Character(10)	VARCHAR2(10) NOT NULL	Schedule ID
14	CURRENCY_PYMNT	Character(3)	VARCHAR2(3) NOT NULL	Payment Currency
15	TXN_CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Transaction Currency
16	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