VNDR_PYINQ_VW1 (SQL View)	Index Back
Vchr/vchr line payment info Generic reporting view used by other views, mainly on supplier inquiry window. Joins together most fields on voucher and pymnt voucher xref tables.

VNDR_PYINQ_VW1

(SQL View)

Vchr/vchr line payment info

Generic reporting view used by other views, mainly on supplier inquiry window. Joins together most fields on voucher and pymnt voucher xref tables.

SELECT C.SETID , A.VENDOR_ID , B.BUSINESS_UNIT , B.VOUCHER_ID , B.PYMNT_CNT , C.VENDOR_NAME_SHORT , C.NAME1 , C.AR_NUM , B.PYMNT_GROSS_AMT , B.DSCNT_PAY_AMT , B.DUE_DT , B.DSCNT_DUE_DT , B.SCHEDULED_PAY_DT , B.PYMNT_METHOD , B.PYMNT_SELCT_STATUS , B.PYMNT_HANDLING_CD , B.PYMNT_SEPARATE , B.PYMNT_HOLD , B.PYMNT_HOLD_WTHD , B.PYMNT_HOLD_REASON , A.POST_STATUS_AP , A.INVOICE_ID , A.INVOICE_DT , A.GROSS_AMT , A.TXN_CURRENCY_CD , A.DSCNT_AMT , A.SALETX_AMT , A.FREIGHT_AMT , B.REMIT_VENDOR , A.PYMNT_TERMS_CD , A.DSCNT_DUE_DT , A.DUE_DT , A.APPR_STATUS , D.PYMNT_HOLD , A.DESCR254_MIXED , A.CLOSE_STATUS , A.RT_TYPE FROM PS_VOUCHER A , PS_PYMNT_VCHR_XREF B , PS_VENDOR C , PS_VENDOR_PAY D WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.VOUCHER_ID = B.VOUCHER_ID AND A.VENDOR_SETID = C.SETID AND A.VENDOR_ID = C.VENDOR_ID AND A.VOUCHER_STYLE <> 'TMPL' AND A.ENTRY_STATUS NOT IN ('X', 'C') AND D.SETID = B.REMIT_SETID AND D.VENDOR_ID = B.REMIT_VENDOR AND D.VNDR_LOC = B.VNDR_LOC AND D.SEQ_NUM = 1 AND D.EFFDT = ( SELECT MAX(I.EFFDT) FROM PS_VENDOR_PAY I WHERE I.SETID = D.SETID AND I.VENDOR_ID = D.VENDOR_ID AND I.VNDR_LOC = D.VNDR_LOC AND I.SEQ_NUM = D.SEQ_NUM AND I.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 SETID Character(5) VARCHAR2(5) NOT NULL 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_CNT Number(5,0) INTEGER NOT NULL Payments

6 VENDOR_NAME_SHORT Character(14) VARCHAR2(14) NOT NULL Short Vendor Name

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

8 AR_NUM Character(15) VARCHAR2(15) NOT NULL Our Customer Number

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

10 DSCNT_PAY_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Payment Discount Amount

11 DUE_DT Date(10) DATE Specifies the date that a transaction is due. It is a generic field used in multiple PeopleSoft applications including Receivables Payables and Purchasing. For example it represents the date that payment is due for a voucher or the date that a scheduled shipment is due to be received.

12 DSCNT_DUE_DT Date(10) DATE Discount Due Date

13 SCHEDULED_PAY_DT Date(10) DATE Scheduled to Pay

14 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

15 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

16 PYMNT_HANDLING_CD Character(2) VARCHAR2(2) NOT NULL Payment Handling

17 PYMNT_SEPARATE Character(1) VARCHAR2(1) NOT NULL Separate Payment

18 PYMNT_HOLD Character(1) VARCHAR2(1) NOT NULL Hold Payment
N=No
Y=Yes

19 PYMNT_HOLD_WTHD Character(1) VARCHAR2(1) NOT NULL On Withholding Hold
N=No
Y=Yes

20 PYMNT_HOLD_REASON Character(3) VARCHAR2(3) NOT NULL Hold Reason
ACC=Accounting in Dispute
ACV=Federal Supplier Inactive
ALC=Agency Location Code Required
AMT=Amount in Dispute
CCR=CCR Expired
CRT=Contract Retention
EFT=EFT incomplete or not Prenoted
FNS=Financial Sanctions
GDS=Goods in Dispute
GNV=G-Invoicing Hold
IPC=Inbound IPAC Required
OTH=Other
QTY=Quantity in Dispute
SAM=SAM Expired
WTH=Withholding Hold

21 POST_STATUS_AP Character(1) VARCHAR2(1) NOT NULL Post Status
N=Payment Not Applied
P=Posted
U=Unposted

22 INVOICE_ID Character(30) VARCHAR2(30) NOT NULL Invoice Number

23 INVOICE_DT Date(10) DATE Invoice Date

24 GROSS_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Gross Invoice Amount

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

26 DSCNT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Discount Amount

27 SALETX_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Sales Tax Amount

28 FREIGHT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Freight Amount

29 REMIT_VENDOR Character(10) VARCHAR2(10) NOT NULL Remit Vendor

30 PYMNT_TERMS_CD Character(5) VARCHAR2(5) NOT NULL Specifies how the payment due date and discount due date are calculated. A payment terms code is associated with various business units bill-to customers vendors as well as sales orders purchase orders and vouchers.

31 VCHR_DSCNT_DUE_DT Date(10) DATE Voucher Discount Due Date

32 VCHR_DUE_DT Date(10) DATE Voucher Due Date

33 APPR_STATUS Character(1) VARCHAR2(1) NOT NULL Approval status flag used on appr_inst_log and derived records to track virtual approver status
A=Approved
D=Denied
P=Pending

34 HOLD_FLAG Character(1) VARCHAR2(1) NOT NULL Hold
N=Hold
Y=Hold

35 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description

36 CLOSE_STATUS Character(1) VARCHAR2(1) NOT NULL Close Status Indicator
C=Closed
O=Open

37 RT_TYPE Character(5) VARCHAR2(5) NOT NULL Defines a category of market rates for currency conversion. Some examples of rate types are commercial, average, floating, and historical.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	SETID	Character(5)	VARCHAR2(5) NOT NULL	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_CNT	Number(5,0)	INTEGER NOT NULL	Payments
6	VENDOR_NAME_SHORT	Character(14)	VARCHAR2(14) NOT NULL	Short Vendor Name
7	NAME1	Character(40)	VARCHAR2(40) NOT NULL	Name 1
8	AR_NUM	Character(15)	VARCHAR2(15) NOT NULL	Our Customer Number
9	PYMNT_GROSS_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Gross Payment Amount
10	DSCNT_PAY_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Payment Discount Amount
11	DUE_DT	Date(10)	DATE	Specifies the date that a transaction is due. It is a generic field used in multiple PeopleSoft applications including Receivables Payables and Purchasing. For example it represents the date that payment is due for a voucher or the date that a scheduled shipment is due to be received.
12	DSCNT_DUE_DT	Date(10)	DATE	Discount Due Date
13	SCHEDULED_PAY_DT	Date(10)	DATE	Scheduled to Pay
14	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
15	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
16	PYMNT_HANDLING_CD	Character(2)	VARCHAR2(2) NOT NULL	Payment Handling
17	PYMNT_SEPARATE	Character(1)	VARCHAR2(1) NOT NULL	Separate Payment
18	PYMNT_HOLD	Character(1)	VARCHAR2(1) NOT NULL	Hold Payment N=No Y=Yes
19	PYMNT_HOLD_WTHD	Character(1)	VARCHAR2(1) NOT NULL	On Withholding Hold N=No Y=Yes
20	PYMNT_HOLD_REASON	Character(3)	VARCHAR2(3) NOT NULL	Hold Reason ACC=Accounting in Dispute ACV=Federal Supplier Inactive ALC=Agency Location Code Required AMT=Amount in Dispute CCR=CCR Expired CRT=Contract Retention EFT=EFT incomplete or not Prenoted FNS=Financial Sanctions GDS=Goods in Dispute GNV=G-Invoicing Hold IPC=Inbound IPAC Required OTH=Other QTY=Quantity in Dispute SAM=SAM Expired WTH=Withholding Hold
21	POST_STATUS_AP	Character(1)	VARCHAR2(1) NOT NULL	Post Status N=Payment Not Applied P=Posted U=Unposted
22	INVOICE_ID	Character(30)	VARCHAR2(30) NOT NULL	Invoice Number
23	INVOICE_DT	Date(10)	DATE	Invoice Date
24	GROSS_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Gross Invoice Amount
25	TXN_CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Transaction Currency
26	DSCNT_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Discount Amount
27	SALETX_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Sales Tax Amount
28	FREIGHT_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Freight Amount
29	REMIT_VENDOR	Character(10)	VARCHAR2(10) NOT NULL	Remit Vendor
30	PYMNT_TERMS_CD	Character(5)	VARCHAR2(5) NOT NULL	Specifies how the payment due date and discount due date are calculated. A payment terms code is associated with various business units bill-to customers vendors as well as sales orders purchase orders and vouchers.
31	VCHR_DSCNT_DUE_DT	Date(10)	DATE	Voucher Discount Due Date
32	VCHR_DUE_DT	Date(10)	DATE	Voucher Due Date
33	APPR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Approval status flag used on appr_inst_log and derived records to track virtual approver status A=Approved D=Denied P=Pending
34	HOLD_FLAG	Character(1)	VARCHAR2(1) NOT NULL	Hold N=Hold Y=Hold
35	DESCR254_MIXED	Character(254)	VARCHAR2(254) NOT NULL	Description
36	CLOSE_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Close Status Indicator C=Closed O=Open
37	RT_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Defines a category of market rates for currency conversion. Some examples of rate types are commercial, average, floating, and historical.