VCHR_PD_DISC_VW

(SQL View)
Index Back

Voucher Inquiry - view 3


SELECT A.BUSINESS_UNIT , A.VOUCHER_ID , A.INVOICE_ID , A.INVOICE_DT , A.VENDOR_ID , B.SETID , B.VENDOR_NAME_SHORT , %Upper(B.NAME1) , %Upper(B.NAME2) , A.VNDR_LOC , A.TXN_CURRENCY_CD , A.VOUCHER_STYLE , A.PAY_AMT , 0 , SUM(C.PYMNT_GROSS_AMT) , 0 , SUM(C.DSCNT_PAY_AMT) , 0 , MAX(A.USETAX_AMT + A.SALETX_AMT + A.FREIGHT_AMT + A.MISC_AMT) , A.VAT_ENTRD_AMT , A.POST_STATUS_AP , A.APPR_STATUS , A.CLOSE_STATUS , A.DUE_DT , A.TERMS_BASIS_DT , A.ACCOUNTING_DT , A.ENTERED_DT , A.BUDGET_HDR_STATUS , A.BUDGET_MISC_STATUS , A.MATCH_STATUS_VCHR , D.DOC_SEQ , MAX(A.ORIGIN) , MAX(A.OPRID) , MAX(A.CNTRCT_ID) , MAX(A.GRP_AP_ID) , MAX(A.VCHR_SRC) , MAX(A.LS_KEY) , MAX(A.VAT_RGSTRN_SELLER) , MAX(A.PROCESS_INSTANCE) , MAX(A.ENTRY_STATUS) , MAX(B.OFAC_STATUS) , MAX(A.VCHR_DRAFT_FLG) , MAX(E.ATTACHMENT_NUM) FROM PS_VOUCHER A , PS_VENDOR B , PS_PYMNT_VCHR_XREF C , PS_BUS_UNIT_TBL_GL D , PS_AP_VCHR_ATT_CNT E WHERE A.VOUCHER_STYLE NOT IN ('PPAY', 'SGLP') AND A.ENTRY_STATUS <> 'X' AND A.VENDOR_SETID = B.SETID AND A.VENDOR_ID = B.VENDOR_ID AND A.VOUCHER_ID = C.VOUCHER_ID AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND C.PYMNT_SELCT_STATUS IN ('P', 'S') AND C.PAID_AMT_BSE <> C.PAID_AMT_GROSS_BSE AND A.BUSINESS_UNIT_GL = D.BUSINESS_UNIT AND A.BUSINESS_UNIT = E.BUSINESS_UNIT AND A.VOUCHER_ID = E.VOUCHER_ID GROUP BY A.BUSINESS_UNIT, A.VOUCHER_ID, A.INVOICE_ID, A.INVOICE_DT, A.VENDOR_ID, B.SETID, B.VENDOR_NAME_SHORT, B.NAME1, B.NAME2, A.VNDR_LOC, A.TXN_CURRENCY_CD, A.VOUCHER_STYLE, A.PAY_AMT, A.VAT_ENTRD_AMT, A.POST_STATUS_AP, A.APPR_STATUS, A.CLOSE_STATUS, A.DUE_DT, A.TERMS_BASIS_DT, A.ACCOUNTING_DT, A.ENTERED_DT, A.BUDGET_HDR_STATUS, A.BUDGET_MISC_STATUS, A.MATCH_STATUS_VCHR, D.DOC_SEQ

# 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 INVOICE_ID Character(30) VARCHAR2(30) NOT NULL Invoice Number
4 INVOICE_DT Date(10) DATE Invoice Date
5 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
6 SETID Character(5) VARCHAR2(5) NOT NULL SetID
7 VENDOR_NAME_SHORT Character(14) VARCHAR2(14) NOT NULL Short Vendor Name
8 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
9 NAME2 Character(40) VARCHAR2(40) NOT NULL Name 2
10 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
11 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency
12 VOUCHER_STYLE Character(4) VARCHAR2(4) NOT NULL Voucher Style
ADJ=Adjustments
AMR=Amortization Voucher
CLBK=Claim Voucher
CORR=Reversal Voucher
JRNL=Journal Voucher
PPAY=Prepaid Voucher
REG=Regular Voucher
RGTR=Register Voucher
SGLP=Single Payment Voucher
THRD=Third Party Voucher
TMPL=Template Voucher
13 GROSS_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Gross Invoice Amount
14 AVAILABLE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Prepayment Available Amount
15 CONV_GROSS_PAID Signed Number(28,3) DECIMAL(26,3) NOT NULL Used for Voucher Inquiry view to hold the paid amounts converted to the txn currency of the voucher.
16 VCHR_BALANCE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Left to Balance
17 CONV_DSCNT_PAID Signed Number(28,3) DECIMAL(26,3) NOT NULL Used for Voucher Inquiry to hold the Discount Paid converted to the voucher txn currency.
18 CONV_NET_PAID Signed Number(28,3) DECIMAL(26,3) NOT NULL Used for Voucher inquiry to hold the Net paid for a voucher converted to the txn currency.
19 TOTAL_MISC Signed Number(28,3) DECIMAL(26,3) NOT NULL Misc Charges Total
20 VAT_ENTRD_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Entered VAT Amount
21 POST_STATUS_AP Character(1) VARCHAR2(1) NOT NULL Post Status
N=Payment Not Applied
P=Posted
U=Unposted
22 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
23 CLOSE_STATUS Character(1) VARCHAR2(1) NOT NULL Close Status Indicator
C=Closed
O=Open
24 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.
25 TERMS_BASIS_DT Date(10) DATE Payment Terms Basis Date
26 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
27 ENTERED_DT Date(10) DATE Entered on
28 BUDGET_HDR_STATUS Character(1) VARCHAR2(1) NOT NULL Budget Checking Header Status
E=Error in Budget Check
I=Document In Processing
N=Not Budget Checked
P=Provisionally Valid
V=Valid
29 BUDGET_MISC_STATUS Character(1) VARCHAR2(1) NOT NULL Budget Checking Misc Status
E=Error in Budget Check
N=Not Budget Checked
P=Provisionally Valid
V=Valid Budget Check
30 MATCH_STATUS_VCHR Character(1) VARCHAR2(1) NOT NULL Match Status
C=Overridden - Credit Note
D=Match Dispute
E=Match Exceptions Exist
M=Matched
N=Not Applicable
O=Manually Overridden
T=To Be Matched
31 DOC_SEQ Character(1) VARCHAR2(1) NOT NULL Enable Document Sequencing

Y/N Table Edit

32 ORIGIN Character(3) VARCHAR2(3) NOT NULL Origin
33 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
34 CNTRCT_ID Character(25) VARCHAR2(25) NOT NULL Buying Agreement ID
35 GRP_AP_ID Character(10) VARCHAR2(10) NOT NULL Control Group ID
36 VCHR_SRC Character(4) VARCHAR2(4) NOT NULL Voucher Source
37 LS_KEY Number(15,0) DECIMAL(15) NOT NULL Lease Key, Unique Key
38 VAT_RGSTRN_SELLER Character(20) VARCHAR2(20) NOT NULL Seller's VAT Registration
39 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
40 ENTRY_STATUS Character(1) VARCHAR2(1) NOT NULL If translate values are added or changed, modify the following code to make sure the values appear accurately in the dropdown list in the search dialog: VCHR_SRCH_DEL.ENTRY_STATUS.SearchInit. Template Voucher status should not be visibile in the drop down.
C=Complete
D=Through batch defaults
E=Edited by Batch Voucher Module
L=Reviewed through Maintenance
M=Modified
O=Open
P=Postable
R=Recycle
S=Scheduled for Payment
T=Template Voucher
X=Deleted
41 OFAC_STATUS Character(1) VARCHAR2(1) NOT NULL OFAC Status
B=Blocked
C=Cleared
R=Review
V=Valid
42 VCHR_DRAFT_FLG Character(1) VARCHAR2(1) NOT NULL Voucher Draft Flag
N=Complete Voucher
Y=Incomplete Voucher
43 ATTACHMENT_NUM Number(5,0) INTEGER NOT NULL Attachment Number