VCHR_INQCNA_VW

(SQL View)
 Index Back

Voucher Inquiry

Voucher Inquiry view to select count

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 , 0 , 0 , 0 , 0 , (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 , ' ' , A.ORIGIN , A.OPRID , A.CNTRCT_ID , A.GRP_AP_ID , A.VCHR_SRC , A.LS_KEY , A.VAT_RGSTRN_SELLER , A.PROCESS_INSTANCE , A.ENTRY_STATUS , B.OFAC_STATUS , A.VCHR_DRAFT_FLG , 0 , A.VCHR_APPRVL_FLG FROM PS_VOUCHER A , PS_VENDOR B WHERE A.VOUCHER_STYLE IN ('ADJ', 'CLBK', 'CORR', 'JRNL', 'REG', 'RGTR', 'THRD', 'TMPL', 'AMR', 'PPAY') AND A.VENDOR_SETID = B.SETID AND A.VENDOR_ID = B.VENDOR_ID UNION SELECT AA.BUSINESS_UNIT , AA.VOUCHER_ID , AA.INVOICE_ID , AA.INVOICE_DT , AA.VENDOR_ID , AA.VENDOR_SETID , %Upper(%Substring(BB.NAME1, 1, 14)) , %Upper(BB.NAME1) , %Upper(BB.NAME2) , AA.VNDR_LOC , AA.TXN_CURRENCY_CD , AA.VOUCHER_STYLE , AA.PAY_AMT , 0 , 0 , 0 , 0 , 0 , (AA.USETAX_AMT + AA.SALETX_AMT + AA.FREIGHT_AMT + AA.MISC_AMT) , AA.VAT_ENTRD_AMT , AA.POST_STATUS_AP , AA.APPR_STATUS , AA.CLOSE_STATUS , AA.DUE_DT , AA.TERMS_BASIS_DT , AA.ACCOUNTING_DT , AA.ENTERED_DT , AA.BUDGET_HDR_STATUS , AA.BUDGET_MISC_STATUS , AA.MATCH_STATUS_VCHR , ' ' , AA.ORIGIN , AA.OPRID , AA.CNTRCT_ID , AA.GRP_AP_ID , AA.VCHR_SRC , AA.LS_KEY , AA.VAT_RGSTRN_SELLER , AA.PROCESS_INSTANCE , AA.ENTRY_STATUS , 'V' , AA.VCHR_DRAFT_FLG , 0 , AA.VCHR_APPRVL_FLG FROM PS_VOUCHER AA , PS_VCHR_VNDR_INFO BB WHERE AA.VOUCHER_STYLE = 'SGLP' AND AA.BUSINESS_UNIT = BB.BUSINESS_UNIT AND AA.VOUCHER_ID = BB.VOUCHER_ID

  • Related Language Record: VCHR_INQ_RL_VW

    • # 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
    44 VCHR_APPRVL_FLG Character(1) VARCHAR2(1) NOT NULL Voucher Approval Flag
    D=Default from Higher Level
    P=Pre-Approved
    S=Virtual Approver
    W=Approval Framework