EM_BUYER_VCH_VW(SQL View) |
Index Back |
|---|---|
Supplier Manage Bills ViewThis view is used by eSettlements to retrieve all of the voucher inquiry panel rows. |
| SELECT DISTINCT B.OPRID , a.business_unit , a.voucher_id , a.invoice_id , a.vendor_setid , a.vendor_id , a.vndr_loc , a.invoice_dt , a.txn_currency_cd , a.gross_amt , a.dscnt_amt , a.due_dt , a.entered_dt , a.voucher_style , E.DESCRSHORT , a.DSCNT_DUE_DT FROM PS_VOUCHER a , PS_EM_BU_USRSEC_VW B , ps_set_cntrl_group gp , PS_PYMT_TRMS_HDR E WHERE a.vendor_setid = E.setid AND a.pymnt_terms_cd = E.PYMNT_TERMS_CD AND a.APPR_STATUS = 'A' AND a.GROSS_AMT > 0 AND gp.setcntrlvalue = a.business_unit AND gp.rec_group_id = 'FS_14' AND ( a.process_man_close = 'N' OR a.close_status <> 'C') AND a.entry_status <> 'X' AND a.VOUCHER_STYLE = 'REG' AND NOT EXISTS ( SELECT 'X' FROM PS_EM_PROPOSAL_VW F WHERE a.business_unit = F.EM_BUSINESS_UNIT AND a.voucher_id = F.VOUCHER_ID AND F.PROPOSAL_STATUS <> 'D' AND F.PROPOSAL_STATUS <> 'C') AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND EXISTS ( SELECT 'X' FROM PS_EM_AGREEMENT C WHERE A.BUSINESS_UNIT = C.SETID AND A.VENDOR_SETID = C.VENDOR_SETID AND A.VENDOR_ID = C.VENDOR_ID AND A.VNDR_LOC = C.VNDR_LOC) AND NOT EXISTS ( SELECT 'X' FROM ps_pymnt_vchr_xref d WHERE a.business_unit = d.business_unit AND a.voucher_id = d.voucher_id AND d.pymnt_selct_status NOT IN ('N', 'X')) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  OPRID |
Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 2 | EM_BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL | From Business Unit |
| 3 | VOUCHER_ID |
Character(8) | VARCHAR2(8) NOT NULL | Voucher ID |
| 4 | INVOICE_ID | Character(30) | VARCHAR2(30) NOT NULL |
Invoice Number
Prompt Table: EM_INVOI_USR_VW |
| 5 | VENDOR_SETID | Character(5) | VARCHAR2(5) NOT NULL | Vendor SetID |
| 6 | VENDOR_ID | Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 7 | VNDR_LOC | Character(10) | VARCHAR2(10) NOT NULL | Vendor Location |
| 8 | INVOICE_DT | Date(10) | DATE | Invoice Date |
| 9 | TXN_CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Transaction Currency |
| 10 | GROSS_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Gross Invoice Amount |
| 11 | DSCNT_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Discount Amount |
| 12 | 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. |
| 13 | ENTERED_DT | Date(10) | DATE | Entered on |
| 14 | 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 |
| 15 | DESCRSHORT | Character(10) | VARCHAR2(10) NOT NULL | Short Description |
| 16 | DSCNT_DUE_DT | Date(10) | DATE | Discount Due Date |