EM_SP_INSTL_VW

(SQL View)
 Index Back

Seller Payments/Installments


SELECT a.business_unit , sec.oprid , a.voucher_id , a.invoice_id , a.vendor_setid , a.vendor_id , d.vndr_loc , a.invoice_dt , d.txn_currency_cd , D.PYMNT_GROSS_AMT , a.dscnt_amt , d.currency_pymnt , d.paid_amt , d.due_dt , d.scheduled_pay_dt , d.pymnt_hold , a.entered_dt , d.pymnt_selct_status , a.voucher_style , t.setid , a.pymnt_terms_cd , D.PYMNT_CNT , D.PYMNT_ID , A.MATCH_STATUS_VCHR , D.PYMNT_METHOD , D.REMIT_VENDOR , D.DESCR254_MIXED , D.PAY_CYCLE , D.PAY_CYCLE_SEQ_NUM FROM PS_VOUCHER a , ps_vendor b , ps_vendor_user sec , ps_pymnt_vchr_xref d , ps_set_cntrl_group gp , ps_pymt_trms_hdr t 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 a.business_unit = d.business_unit AND a.voucher_id = d.voucher_id AND gp.setcntrlvalue = a.business_unit AND gp.rec_group_id = 'FS_14' 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 <> 'C') OR ( a.process_man_close <> 'N' AND a.close_status = 'C' AND d.pymnt_selct_status='E' ) ) AND a.entry_status <> 'X' AND d.pymnt_type <> 'W' AND d.pymnt_selct_status <> 'X' AND d.due_dt IS NOT NULL AND ((a.VOUCHER_STYLE='PPAY' AND d.PREPAID_STATUS='P' ) OR a.VOUCHER_STYLE<>'PPAY')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EM_BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL From Business Unit
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 SETID Character(5) VARCHAR2(5) NOT NULL 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 DSCNT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Discount Amount
12 CURRENCY_PYMNT Character(3) VARCHAR2(3) NOT NULL Payment Currency
13 PAID_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Paid Amount
14 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.
15 SCHEDULED_PAY_DT Date(10) DATE Scheduled to Pay
16 PYMNT_HOLD Character(1) VARCHAR2(1) NOT NULL Hold Payment
N=No
Y=Yes
17 ENTERED_DT Date(10) DATE Entered on
18 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
19 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
20 SETID_PAYTRMS Character(5) VARCHAR2(5) NOT NULL Payment Terms Setid
21 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.
22 PYMNT_CNT Number(5,0) INTEGER NOT NULL Payments
23 PYMNT_ID Character(10) VARCHAR2(10) NOT NULL Payment Number
24 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
25 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
26 REMIT_VENDOR Character(10) VARCHAR2(10) NOT NULL Remit Vendor
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