EM_VCHR_INQ_VW(SQL View) |
Index Back |
---|---|
Invoice Inquiry view |
SELECT a.business_unit ,a.business_unit , a.voucher_id , d.pymnt_cnt , a.invoice_id , b.setid , b.setid , b.vendor_id , d.vndr_loc , a.invoice_dt , d.txn_currency_cd , a.gross_amt , a.dscnt_amt , d.currency_pymnt , SUM(d.paid_amt) , d.due_dt , d.pymnt_hold , a.entered_dt , d.pymnt_selct_status , a.appr_status , a.match_status_vchr , a.voucher_style , t.setid , a.pymnt_terms_cd , A.PYMNT_CNT_TOTAL , D.PYMNT_METHOD , A.DESCR254_MIXED , D.PAY_CYCLE , D.PAY_CYCLE_SEQ_NUM , A.VCHR_LINE_APPR , A.doc_tol_hdr_status , A.budget_hdr_status , A.AP_MATCH_TYPE FROM PS_VOUCHER a , ps_vendor b , ps_pymnt_vchr_xref d , ps_pymt_trms_hdr t WHERE 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.VOUCHER_STYLE <> 'TMPL' AND a.entry_status <> 'R' AND a.close_status <> 'C' AND t.setid = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE REC_GROUP_ID = 'FS_14' AND RECNAME = 'PYMT_TRMS_HDR' AND SETCNTRLVALUE = a.business_unit) AND a.pymnt_terms_cd = t.pymnt_terms_cd AND ((a.process_man_close = 'N' OR a.close_status = 'O') 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 GROUP BY a.business_unit , a.voucher_id, d.pymnt_cnt , a.invoice_id , b.setid, b.vendor_id , d.vndr_loc , 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.appr_status , a.match_status_vchr , a.voucher_style , t.setid, a.pymnt_terms_cd, A.PYMNT_CNT_TOTAL, D.PYMNT_METHOD, A.DESCR254_MIXED, D.PAY_CYCLE , D.PAY_CYCLE_SEQ_NUM, A.VCHR_LINE_APPR, A.doc_tol_hdr_status, A.budget_hdr_status, A.AP_MATCH_TYPE |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | EM_BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Buyer |
2 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: SP_BU_AP_NONVW |
3 | VOUCHER_ID | Character(8) | VARCHAR2(8) NOT NULL | Voucher ID |
4 | PYMNT_CNT | Number(5,0) | INTEGER NOT NULL | Payments |
5 | INVOICE_ID | Character(30) | VARCHAR2(30) NOT NULL |
Invoice Number
Prompt Table: EM_INVOI_USR_VW |
6 | VENDOR_SETID | Character(5) | VARCHAR2(5) NOT NULL | Vendor SetID |
7 | SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
8 | VENDOR_ID | Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
9 | VNDR_LOC | Character(10) | VARCHAR2(10) NOT NULL |
Vendor Location
Prompt Table: EM_VNDR_LOC_VW |
10 | INVOICE_DT | Date(10) | DATE | Invoice Date |
11 | TXN_CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Transaction Currency |
12 | GROSS_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Gross Invoice Amount |
13 | DSCNT_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Discount Amount |
14 | CURRENCY_PYMNT | Character(3) | VARCHAR2(3) NOT NULL | Payment Currency |
15 | PAID_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Paid Amount |
16 | 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. |
17 | PYMNT_HOLD | Character(1) | VARCHAR2(1) NOT NULL |
Hold Payment
N=No Y=Yes |
18 | ENTERED_DT | Date(10) | DATE | Entered on |
19 | 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 |
20 | 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 |
21 | 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 |
22 | 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 |
23 | SETID_PAYTRMS | Character(5) | VARCHAR2(5) NOT NULL | Payment Terms Setid |
24 | PYMNT_TERMS_CD | Character(5) | VARCHAR2(5) NOT NULL | Specifies how the payment due date and discount due date are calculated. A payment terms code is associated with various business units bill-to customers vendors as well as sales orders purchase orders and vouchers. |
25 | PYMNT_CNT_TOTAL | Number(5,0) | INTEGER NOT NULL | Total Payment Count |
26 | PYMNT_METHOD | Character(3) | VARCHAR2(3) NOT NULL |
Payment Method
ACH=Automated Clearing House BEF=Draft - Customer EFT BOO=Draft - Customer Initiated CHK=System Check D=Deposit DD=Direct Debit DFT=Draft - Supplier Initiated DRA=Draft EFT=Electronic Funds Transfer GE=Giro - EFT GM=Giro - Manual LC=Letter of Credit MAN=Manual Check TRW=Treasury Wire WIR=Wire Transfer |
27 | DESCR254_MIXED | Character(254) | VARCHAR2(254) NOT NULL | Description |
28 | PAY_CYCLE | Character(6) | VARCHAR2(6) NOT NULL | Pay Cycle |
29 | PAY_CYCLE_SEQ_NUM | Number(6,0) | INTEGER NOT NULL | Pay Cycle Sequence Number |
30 | VCHR_LINE_APPR | Character(1) | VARCHAR2(1) NOT NULL |
Invoice Line Approval Status
0=Line Routing Not Required 1=Completed 2=Routed for Approval |
31 | DOC_TOL_HDR_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Document Heade Document Tolerance Status
E=Error N=Not Checked R=Reset V=Valid |
32 | 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 |
33 | AP_MATCH_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Match Type
C=Matched with Credit Note D=Matched with Debit Memo O=Matched with Override P=Auto - Matched |