EM_SP_VCHIQ_VW2(SQL View) |
Index Back |
|---|---|
Supplier Invoice Inquiry ViewThis view is used by eSettlements to retrieve all of the Invoices in fluid Supplier Invoice Inquiry |
| SELECT A.BUSINESS_UNIT , SEC.OPRID , A.VOUCHER_ID , A.INVOICE_ID , B.SETID , B.VENDOR_ID , A.VNDR_LOC , A.INVOICE_DT , A.TXN_CURRENCY_CD , A.GROSS_AMT , A.VOUCHER_STYLE , ( SELECT COUNT(*) FROM PS_PYMNT_VCHR_XREF P1 WHERE P1.PYMNT_SELCT_STATUS <> 'X' AND P1.PYMNT_TYPE <> 'W' AND ((A.VOUCHER_STYLE = 'PPAY' AND P1.PREPAID_STATUS = 'P') OR (A.VOUCHER_STYLE <> 'PPAY')) AND (( A.PROCESS_MAN_CLOSE = 'N' OR A.CLOSE_STATUS <> 'C') OR ( A.PROCESS_MAN_CLOSE <> 'N' AND A.CLOSE_STATUS = 'C' AND P1.PYMNT_SELCT_STATUS ='E' ) ) AND P1.BUSINESS_UNIT = A.BUSINESS_UNIT AND P1.VOUCHER_ID = A.VOUCHER_ID ) , ( SELECT COUNT(*) FROM PS_PYMNT_VCHR_XREF P2 WHERE P2.PYMNT_TYPE <> 'W' AND ((A.VOUCHER_STYLE = 'PPAY' AND P2.PREPAID_STATUS = 'P') OR (A.VOUCHER_STYLE <> 'PPAY')) AND P2.PYMNT_SELCT_STATUS IN ('P','E','S') AND (( A.PROCESS_MAN_CLOSE = 'N' OR A.CLOSE_STATUS <> 'C') OR ( A.PROCESS_MAN_CLOSE <> 'N' AND A.CLOSE_STATUS = 'C' AND P2.PYMNT_SELCT_STATUS ='E' ) ) AND P2.BUSINESS_UNIT = A.BUSINESS_UNIT AND P2.VOUCHER_ID = A.VOUCHER_ID ) ,' ' ,'X' FROM PS_VOUCHER A , PS_VENDOR B , PS_VENDOR_USER SEC , PS_EM_BU_VNAGRT_VW BUSEC WHERE SEC.SETID = A.VENDOR_SETID AND SEC.VENDOR_ID = A.VENDOR_ID AND A.VENDOR_SETID = B.SETID AND A.VENDOR_ID = B.VENDOR_ID AND BUSEC.BUSINESS_UNIT = A.BUSINESS_UNIT AND BUSEC.OPRID = SEC.OPRID AND A.ENTRY_STATUS = 'P' AND A.VCHR_DRAFT_FLG <> 'Y' AND ( A.PROCESS_MAN_CLOSE = 'N' OR A.CLOSE_STATUS <> 'C') UNION ALL SELECT A.BUSINESS_UNIT , SEC.OPRID , A.VOUCHER_ID , A.INVOICE_ID , B.SETID , B.VENDOR_ID , A.VNDR_LOC , A.INVOICE_DT , A.TXN_CURRENCY_CD , A.GROSS_AMT , A.VOUCHER_STYLE , 0 , 0 ,'X' ,A.VCHR_BLD_STATUS FROM PS_VCHR_HDR_QV A , PS_VENDOR B , PS_VENDOR_USER SEC , PS_EM_BU_VNAGRT_VW BUSEC WHERE SEC.SETID = A.VENDOR_SETID AND SEC.VENDOR_ID = A.VENDOR_ID AND A.VENDOR_SETID = B.SETID AND A.VENDOR_ID = B.VENDOR_ID AND BUSEC.BUSINESS_UNIT = A.BUSINESS_UNIT AND BUSEC.OPRID = SEC.OPRID AND A.VCHR_BLD_STATUS IN ('B','E','I','N') |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  EM_BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL | Buying Organization |
| 2 | OPRID |
Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 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
Prompt Table: EM_VNDR_LOC_VW |
| 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 | 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 |
| 12 | EM_COUNT1 | Number(6,0) | INTEGER NOT NULL | Number of Not Paid Invoices |
| 13 | EM_COUNT2 | Number(6,0) | INTEGER NOT NULL | Number of Vouchers |
| 14 | EM_INV_PYMT_STAT | Character(2) | VARCHAR2(2) NOT NULL |
Invoice Payment Status
A=All P=Paid PP=Partially Paid U=Unpaid |
| 15 | EM_INV_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
eSettlements Invoice Status - Supplier Facing
B=Creating Voucher E=Error Correction Required I=Save For Later N=Submitted V=Voucher Created |