VCHRSP_NOPAY_VW(SQL View) |
Index Back |
|---|---|
Voucher Inquiry - Single Pmt 2 |
| SELECT A.BUSINESS_UNIT , A.VOUCHER_ID , A.INVOICE_ID , A.INVOICE_DT , A.VENDOR_ID , A.VENDOR_SETID , %Upper(%Substring(B.NAME1,1,14)) , %Upper(B.NAME1) , A.VNDR_LOC , A.TXN_CURRENCY_CD , A.VOUCHER_STYLE , A.PAY_AMT , 0 , 0 , 0 , 0 , 0 , MAX(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 , D.DOC_SEQ , A.ORIGIN , A.OPRID , A.CNTRCT_ID , A.GRP_AP_ID , A.VCHR_SRC , MAX(A.LS_KEY) , A.VAT_RGSTRN_SELLER , A.PROCESS_INSTANCE , A.ENTRY_STATUS , 'V' , MAX(VCHR_DRAFT_FLG) , MAX(E.ATTACHMENT_NUM) FROM PS_VOUCHER A , PS_VCHR_VNDR_INFO B , PS_PYMNT_VCHR_XREF C , PS_BUS_UNIT_TBL_GL D , PS_AP_VCHR_ATT_CNT E WHERE A.VOUCHER_STYLE = 'SGLP' AND A.ENTRY_STATUS <> 'X' AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.VOUCHER_ID = B.VOUCHER_ID AND A.VOUCHER_ID = C.VOUCHER_ID AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND C.PYMNT_SELCT_STATUS NOT IN ('P', 'S') AND NOT EXISTS ( SELECT 'X' FROM PS_PYMNT_VCHR_XREF P WHERE P.BUSINESS_UNIT = C.BUSINESS_UNIT AND P.VOUCHER_ID = C.VOUCHER_ID AND P.PYMNT_SELCT_STATUS = 'P') AND A.BUSINESS_UNIT_GL = D.BUSINESS_UNIT AND A.BUSINESS_UNIT = E.BUSINESS_UNIT AND A.VOUCHER_ID = E.VOUCHER_ID GROUP BY A.BUSINESS_UNIT, A.VOUCHER_ID, A.INVOICE_ID, A.INVOICE_DT, A.VENDOR_ID, A.VENDOR_SETID, B.NAME1, A.VNDR_LOC, A.TXN_CURRENCY_CD, A.VOUCHER_STYLE, A.PAY_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, D.DOC_SEQ, A.ORIGIN, A.OPRID, A.CNTRCT_ID, A.GRP_AP_ID, A.VCHR_SRC, A.VAT_RGSTRN_SELLER, A.PROCESS_INSTANCE, A.ENTRY_STATUS |
| # | 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 | VNDR_LOC | Character(10) | VARCHAR2(10) NOT NULL | Vendor Location |
| 10 | TXN_CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Transaction Currency |
| 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 | GROSS_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Gross Invoice Amount |
| 13 | AVAILABLE_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Prepayment Available Amount |
| 14 | 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. |
| 15 | VCHR_BALANCE_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Left to Balance |
| 16 | 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. |
| 17 | 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. |
| 18 | TOTAL_MISC | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Misc Charges Total |
| 19 | VAT_ENTRD_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Entered VAT Amount |
| 20 | POST_STATUS_AP | Character(1) | VARCHAR2(1) NOT NULL |
Post Status
N=Payment Not Applied P=Posted U=Unposted |
| 21 | 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 |
| 22 | CLOSE_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Close Status Indicator
C=Closed O=Open |
| 23 | 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. |
| 24 | TERMS_BASIS_DT | Date(10) | DATE | Payment Terms Basis Date |
| 25 | ACCOUNTING_DT | Date(10) | DATE | The accounting entry construction date for a given transaction (a generic field that crosses multiple |
| 26 | ENTERED_DT | Date(10) | DATE | Entered on |
| 27 | 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 |
| 28 | 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 |
| 29 | 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 |
| 30 | DOC_SEQ | Character(1) | VARCHAR2(1) NOT NULL |
Enable Document Sequencing
Y/N Table Edit |
| 31 | ORIGIN | Character(3) | VARCHAR2(3) NOT NULL | Origin |
| 32 | OPRID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 33 | CNTRCT_ID | Character(25) | VARCHAR2(25) NOT NULL | Buying Agreement ID |
| 34 | GRP_AP_ID | Character(10) | VARCHAR2(10) NOT NULL | Control Group ID |
| 35 | VCHR_SRC | Character(4) | VARCHAR2(4) NOT NULL | Voucher Source |
| 36 | LS_KEY | Number(15,0) | DECIMAL(15) NOT NULL | Lease Key, Unique Key |
| 37 | VAT_RGSTRN_SELLER | Character(20) | VARCHAR2(20) NOT NULL | Seller's VAT Registration |
| 38 | PROCESS_INSTANCE | Number(10,0) | DECIMAL(10) NOT NULL | Process Instance |
| 39 | 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 |
| 40 | OFAC_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
OFAC Status
B=Blocked C=Cleared R=Review V=Valid |
| 41 | VCHR_DRAFT_FLG | Character(1) | VARCHAR2(1) NOT NULL |
Voucher Draft Flag
N=Complete Voucher Y=Incomplete Voucher |
| 42 | ATTACHMENT_NUM | Number(5,0) | INTEGER NOT NULL | Attachment Number |