EM_MULTINSTL_VW(SQL View) |
Index Back |
|---|---|
Vouchers w/Multiple InstallmtsRetrieves vouchers with multiple installments/payments. |
| SELECT a.business_unit , sec.oprid , a.voucher_id , a.invoice_id , b.vendor_id , b.vendor_name_short , a.invoice_dt , d.txn_currency_cd , a.gross_amt , a.dscnt_amt , d.currency_pymnt , d.due_dt , d.pymnt_hold , a.entered_dt , d.pymnt_selct_status , a.voucher_style FROM PS_VOUCHER a , ps_vendor b , ps_sec_bu_opr sec , ps_pymnt_vchr_xref d WHERE sec.business_unit = a.business_unit AND a.vendor_setid = b.setid AND a.vendor_id = b.vendor_id AND a.business_unit = d.business_unit AND a.voucher_id = d.voucher_id AND ((a.process_man_close = 'N' OR a.close_status <> 'C') OR (a.process_man_close <> 'N' AND d.pymnt_selct_status = 'E')) AND a.entry_status <> 'X' AND d.pymnt_type <> 'W' AND d.pymnt_selct_status <> 'X' AND ((a.VOUCHER_STYLE='PPAY' AND d.PREPAID_STATUS='P' ) OR a.VOUCHER_STYLE<>'PPAY') AND d.due_dt IS NOT NULL AND D.PYMNT_CNT > 1 |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EM_BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | From Buyer |
| 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_ID | Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 6 | VENDOR_NAME_SHORT | Character(14) | VARCHAR2(14) NOT NULL | Short Vendor Name |
| 7 | INVOICE_DT | Date(10) | DATE | Invoice Date |
| 8 | TXN_CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Transaction Currency |
| 9 | GROSS_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Gross Invoice Amount |
| 10 | DSCNT_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Discount Amount |
| 11 | CURRENCY_PYMNT | Character(3) | VARCHAR2(3) NOT NULL | Payment Currency |
| 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 | PYMNT_HOLD | Character(1) | VARCHAR2(1) NOT NULL |
Hold Payment
N=No Y=Yes |
| 14 | ENTERED_DT | Date(10) | DATE | Entered on |
| 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 | 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 |