VND_LVL_DFHI_VW

(SQL View)
 Index Back

Vndr-Lvl Dflt Hier Opt View

Supplier Level Options for Voucher Defaulting Hierarchy

SELECT A.SETID , A.VENDOR_ID , B.VNDR_LOC , A.WTHD_SW , A.VAT_SW , A.INTERUNIT_VNDR_FLG , A.VNDR_AFFILIATE , B.PRIM_ADDR_SEQ_NUM , B.DOC_TYPE_FLG , B.DOC_TYPE , B.VNDR_SBI_FLG , B.SBI_DOC_DFLT , B.SBI_APPROVAL_FLG , B.ACCT_TEMPL_FLG , B.DST_CNTRL_ID , B.PYMNT_TERMS_CD , B.PAY_TRM_BSE_DT_DFT , B.CURRENCY_CD , B.CUR_RT_TYPE , B.PAY_SCHEDULE_TYPE , B.DATE_CALC_BASIS , B.VCHR_APPRVL_FLG , B.BUSPROCNAME , B.APPR_RULE_SET , B.MATCH_OPT_FLG , B.MATCH_OPT , B.MATCH_CNTRL_ID , B.RECV_ONLY_MTCH_FLG , B.VCHR_MTCH_ADJ_DFLT , B.VCHR_TAX_ADJ_DFT , B.SALES_USE_TX_FLG , B.SHIP_LOC_FLG , B.SHIPTO_ID , B.SUT_BASE_ID , B.ULTIMATE_USE_CD , B.SALETX_TOL_FLG , B.SALETX_TOL_PCT , B.SALETX_TOL_AMT , B.SALETX_TOL_CUR_CD , B.SALETX_TOL_RT_TYPE , B.ERS_INV_DT_DFLT , B.ERS_TAX_TYPE_DFLT , B.CONSIGNED_FLAG , C.DSCNT_ALWAYS_FLG , C.FACTORING_FLG , C.DELAY_DAYS_FLG , CASE WHEN C.DELAY_DAYS_FLG = 'S' THEN C.PYMNT_DELAY_DAYS ELSE 0 END , CASE WHEN C.DELAY_DAYS_FLG = 'S' THEN C.DSCNT_DELAY_DAYS ELSE 0 END , F.DFT_SIGHT_CD , C.LATE_CHRG_FLG , C.LATE_CHRG_OPT , C.LATE_CHRG_CD , D.COUNTRY , E.DUP_INVOICE_IND , E.DUP_INV_NUM_FLG , E.DUP_INV_DT , E.DUP_INV_VENDOR_NUM , E.DUP_INV_GROSS_AMT , E.DUP_INV_BUS_UNIT , E.DUP_INV_COMB_CODE , E.DUP_INVOICE_ACTION , B.REMIT_SETID , B.REMIT_VENDOR , B.REMIT_LOC , B.REMIT_ADDR_SEQ_NUM , F.PYMNT_METHOD , F.PYMNT_HOLD , F.PYMNT_GROUP_CD , F.PYMNT_SEPARATE , F.PYMNT_HANDLING_CD , F.BANK_CD , F.BANK_ACCT_KEY , F.CURRENCY_PYMNT , F.CUR_RT_TYPE_PYMNT , F.EFT_PYMNT_FMT_CD , F.EFT_RTNG_FLG , F.EFT_RTNG_STATUS , F.PRENOTE_STATUS , F.HOL_PROC_OPT , F.HOL_PROC_DAYS , F.HOL_PROC_OVERFL , F.GRP_PYMNT_DT , F.APPLY_NETTING , F.EFT_LAYOUT_CD , B.SPEEDCHART_KEY FROM PS_VENDOR A , PS_VENDOR_LOC B , PS_VENDOR_PAY C , PS_VENDOR_ADDR D , PS_VENDOR_INVOICE E , PS_VENDOR_PAY F WHERE B.SETID = A.SETID AND B.VENDOR_ID = A.VENDOR_ID AND B.EFFDT = ( SELECT MAX(I.EFFDT) FROM PS_VENDOR_LOC I WHERE I.SETID = B.SETID AND I.VENDOR_ID = B.VENDOR_ID AND I.VNDR_LOC = B.VNDR_LOC AND I.EFFDT <= %CurrentDateIn AND I.EFF_STATUS = 'A') AND C.SETID = B.SETID AND C.VENDOR_ID = B.VENDOR_ID AND C.VNDR_LOC = B.VNDR_LOC AND C.EFFDT = B.EFFDT AND C.SEQ_NUM = ( SELECT MAX(J.SEQ_NUM) FROM PS_VENDOR_PAY J WHERE J.SETID = C.SETID AND J.VENDOR_ID = C.VENDOR_ID AND J.VNDR_LOC = C.VNDR_LOC AND J.EFFDT = C.EFFDT AND J.EFF_STATUS = 'A') AND D.SETID = C.SETID AND D.VENDOR_ID = C.VENDOR_ID AND D.ADDRESS_SEQ_NUM = B.PRIM_ADDR_SEQ_NUM AND D.EFFDT = ( SELECT MAX(L.EFFDT) FROM PS_VENDOR_ADDR L WHERE L.SETID = D.SETID AND L.VENDOR_ID = D.VENDOR_ID AND L.ADDRESS_SEQ_NUM = D.ADDRESS_SEQ_NUM AND L.EFFDT <= %CurrentDateIn AND L.EFF_STATUS = 'A') AND E.SETID = A.SETID AND E.VENDOR_ID = A.VENDOR_ID AND E.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_VENDOR_INVOICE M WHERE M.SETID = E.SETID AND M.VENDOR_ID = E.VENDOR_ID AND M.EFFDT <= %CurrentDateIn AND M.EFF_STATUS = 'A') AND F.SETID = B.REMIT_SETID AND F.VENDOR_ID = B.REMIT_VENDOR AND F.VNDR_LOC = B.REMIT_LOC AND F.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_VENDOR_PAY M WHERE M.SETID = F.SETID AND M.VENDOR_ID = F.VENDOR_ID AND M.VNDR_LOC = F.VNDR_LOC AND M.EFF_STATUS = 'A' AND M.EFFDT <= %CurrentDateIn) AND F.SEQ_NUM = ( SELECT MAX(N.SEQ_NUM) FROM PS_VENDOR_PAY N WHERE N.SETID = F.SETID AND N.VENDOR_ID = F.VENDOR_ID AND N.VNDR_LOC = F.VNDR_LOC AND N.EFFDT = F.EFFDT AND N.EFF_STATUS = 'A')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
2 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
3 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
4 WTHD_SW Character(1) VARCHAR2(1) NOT NULL Withholding Applicable Flag
B=Global and 1099
G=Global
T=1099
5 VAT_SW Character(1) VARCHAR2(1) NOT NULL Display VAT Flag
H=Hide VAT checkbox
N=Display VAT checkbox off
Y=Display VAT checkbox on
6 INTERUNIT_VNDR_FLG Character(1) VARCHAR2(1) NOT NULL InterUnit Vendor
N=No
Y=Yes
7 VNDR_AFFILIATE Character(5) VARCHAR2(5) NOT NULL Vendor Affiliate
8 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number
9 DOC_TYPE_FLG Character(1) VARCHAR2(1) NOT NULL Document Type Flag
D=Default from Higher Level
S=Specify at This level
10 DOC_TYPE Character(8) VARCHAR2(8) NOT NULL Specifies the business purpose of a financial transaction in countries that require all financial transactions to be tracked as "documents". A Document Type is associated with one and only one Journal Code.
11 SBI_FLG Character(1) VARCHAR2(1) NOT NULL Selfbillingflag
12 SBI_DOC_OPT Character(1) VARCHAR2(1) NOT NULL SelfBillingNumber
G=Group Vouchers (Auto-Num)
I=Individual Voucher (Auto-Num)
P=Individual Vchr-Invoice Number
13 SBI_APPROVAL_FLG Character(1) VARCHAR2(1) NOT NULL SBI Approval Option
A=Approved
U=Unapproved
14 ACCT_TEMPL_FLG Character(1) VARCHAR2(1) NOT NULL Accounting Template
D=Default from Higher Level
S=Specify at this Level
15 DST_CNTRL_ID Character(10) VARCHAR2(10) NOT NULL Accounting Template
16 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.
17 PAY_TRM_BSE_DT_OPT Character(1) VARCHAR2(1) NOT NULL Payment Terms Basis Date Type
A=Acct Date
C=Doc Date
I=Inv Date
R=Recpt Date
S=Ship Date
U=User Date
18 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency
19 RT_TYPE Character(5) VARCHAR2(5) NOT NULL Defines a category of market rates for currency conversion. Some examples of rate types are commercial, average, floating, and historical.
20 PAY_SCHEDULE_TYPE Character(3) VARCHAR2(3) NOT NULL Pay Schedule Type
MAN=Manual
OTH=Other Non Transportation
TRN=Transportation
21 DATE_CALC_BASIS Character(1) VARCHAR2(1) NOT NULL Date Calculation Basis
A=Agricultural Commodities
D=Dairy
M=Meat
O=Poultry
P=Prompt Pay Basis 7 Days
Q=Prompt Pay Basis 14 Days
22 VCHR_APPRVL_FLG Character(1) VARCHAR2(1) NOT NULL Voucher Approval Flag
D=Default from Higher Level
P=Pre-Approved
S=Virtual Approver
W=Approval Framework
23 BUSPROCNAME Character(30) VARCHAR2(30) NOT NULL Business Process Name (see PSBUSPROCDEFN).
24 APPR_RULE_SET Character(30) VARCHAR2(30) NOT NULL Approval Rule Set
25 MATCH_OPT_FLG Character(1) VARCHAR2(1) NOT NULL Newly added for R-JMACIA-NQ66A
D=Default from Higher Level
S=Specified
26 MATCH_OPT Character(1) VARCHAR2(1) NOT NULL Requires Matching
27 MATCH_CNTRL_ID Character(10) VARCHAR2(10) NOT NULL Match Rule
28 RECV_ONLY_MTCH_FLG Character(1) VARCHAR2(1) NOT NULL Receipt Only Match
29 VCHR_MTCH_ADJ_DFLT Character(1) VARCHAR2(1) NOT NULL Match Adjustments
A=Automatic
D=Default
N=None
S=Stage to Pending File
30 VCHR_TAX_ADJ_DFT Character(1) VARCHAR2(1) NOT NULL Sales and Use Tax Adjustments
A=Automatic
D=Default
N=None
S=Stage to Pending File
31 SALES_USE_TX_FLG Character(1) VARCHAR2(1) NOT NULL Sales/Use Tax Code
N=Non Taxable
S=Sales Tax
U=Use Tax
32 SHIP_LOC_FLG Character(1) VARCHAR2(1) NOT NULL Ship To/End Location Flag
D=Default from Higher Level
S=Specify at this Level
33 SHIPTO_ID Character(10) VARCHAR2(10) NOT NULL Ship To Location
34 SUT_BASE_ID Character(10) VARCHAR2(10) NOT NULL Sales/Use Tax Destination
35 ULTIMATE_USE_CD Character(8) VARCHAR2(8) NOT NULL Ultimate Use Code
36 SALETX_TOL_FLG Character(1) VARCHAR2(1) NOT NULL Sales Tax Tolerance Flag
D=Default from Higher Level
S=Specify at this Level
37 SALETX_TOL_PCT Number(6,2) DECIMAL(5,2) NOT NULL Sales Tax Tolerance Percent
38 SALETX_TOL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Sales Tax Tolerance Amount
39 SALETX_TOL_CUR_CD Character(3) VARCHAR2(3) NOT NULL Sales Tax Tolerance Curr Code
40 SALETX_TOL_RT_TYPE Character(5) VARCHAR2(5) NOT NULL Sales Tax Tolerance Rate Type
41 ERS_INV_DT_DFLT Character(1) VARCHAR2(1) NOT NULL ERS Invoice Date
D=Default from Higher Level
F=Freight Terms
R=Receipt Date
42 ERS_TAX_TYPE_DFLT Character(1) VARCHAR2(1) NOT NULL ERS Tax Type
B=Sales Tax
D=Default from Higher Level
N=No Taxes
U=Use Tax
V=Value Added Tax
43 CONSIGNED_FLAG Character(1) VARCHAR2(1) NOT NULL Consigned Flag
44 DSCNT_ALWAYS_FLG Character(1) VARCHAR2(1) NOT NULL Always take discount
45 FACTORING_FLG Character(1) VARCHAR2(1) NOT NULL Factoring
46 DELAY_DAYS_FLG Character(1) VARCHAR2(1) NOT NULL Delay Days Flag
D=Default from BU
N=Do Not Employ
S=Specify
47 PYMNT_DELAY_DAYS Signed Number(5,0) DECIMAL(4) NOT NULL Payment Delay Days
48 DSCNT_DELAY_DAYS Signed Number(5,0) DECIMAL(4) NOT NULL Discount Delay Days
49 DFT_SIGHT_CD Character(5) VARCHAR2(5) NOT NULL Draft Sight Code
50 LATE_CHRG_FLG Character(1) VARCHAR2(1) NOT NULL Late Charges
D=Default from Higher Level
S=Specify
51 LATE_CHRG_OPT Character(1) VARCHAR2(1) NOT NULL Late Charge Option
C=Compute Charges
N=Not Applicable
52 LATE_CHRG_CD Character(4) VARCHAR2(4) NOT NULL Late Charge Code
53 COUNTRY_SHIP_FROM Character(3) VARCHAR2(3) NOT NULL Specifies the country from which the invoice contents were shipped (for VAT processing only).
54 DUP_INVOICE_IND Character(1) VARCHAR2(1) NOT NULL Duplicate Invoice Indicator
D=Default from Higher Level
S=Specify at this Level
55 DUP_INV_NUM_FLG Character(1) VARCHAR2(1) NOT NULL Invoice Number
N=No
Y=Yes
56 DUP_INV_DT Character(1) VARCHAR2(1) NOT NULL Invoice Date
N=No
Y=Yes
57 DUP_INV_VENDOR_NUM Character(1) VARCHAR2(1) NOT NULL Vendor ID
N=No
Y=Yes
58 DUP_INV_GROSS_AMT Character(1) VARCHAR2(1) NOT NULL Gross Amount
N=No
Y=Yes
59 DUP_INV_BUS_UNIT Character(1) VARCHAR2(1) NOT NULL Business Unit
N=No
Y=Yes
60 DUP_INV_COMB_CODE Number(3,0) SMALLINT NOT NULL Dupl Invoice Combination Code
61 DUP_INVOICE_ACTION Character(1) VARCHAR2(1) NOT NULL Duplicate Invoice Severity
K=Reject
R=Recycle
W=Warning
62 REMIT_SETID Character(5) VARCHAR2(5) NOT NULL Remit SetID
63 REMIT_VENDOR Character(10) VARCHAR2(10) NOT NULL Remit Vendor
64 REMIT_LOC Character(10) VARCHAR2(10) NOT NULL Remit to Location
65 REMIT_ADDR_SEQ_NUM Number(5,0) INTEGER NOT NULL Remitting Location
66 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
67 PYMNT_HOLD Character(1) VARCHAR2(1) NOT NULL Hold Payment
N=No
Y=Yes
68 PYMNT_GROUP_CD Character(2) VARCHAR2(2) NOT NULL Pay Group Code
69 PYMNT_SEPARATE Character(1) VARCHAR2(1) NOT NULL Separate Payment
70 PYMNT_HANDLING_CD Character(2) VARCHAR2(2) NOT NULL Payment Handling
71 BANK_CD Character(5) VARCHAR2(5) NOT NULL Bank Code
72 BANK_ACCT_KEY Character(4) VARCHAR2(4) NOT NULL A user defined unique identifier that facilitates the identification of a given account with a given bank
73 CURRENCY_PYMNT Character(3) VARCHAR2(3) NOT NULL Payment Currency
74 CUR_RT_TYPE_PYMNT Character(5) VARCHAR2(5) NOT NULL Payment Currency Rate Type
75 EFT_PYMNT_FMT_CD Character(3) VARCHAR2(3) NOT NULL Payment Format
00=Standard SWIFT transfer
10=Urgent SWIFT
15=Euro-Transfer
21=Check, w/ receipt confirmation
22=Check, Urgent
23=Check, urgent w/ receipt
40=Postal Order
CTX=Corp Trade Exchange
PBC=Check, standard
PPD=Prearranged Pay & Deposit
WRT=Wire Transfer
76 EFT_RTNG_FLG Character(1) VARCHAR2(1) NOT NULL Complex Routing
D=Use DFI ID
N=None
R=Use Complex Routing
77 EFT_RTNG_STATUS Character(1) VARCHAR2(1) NOT NULL Routing Status
C=Complete
I=Incomplete
78 PRENOTE_STATUS Character(1) VARCHAR2(1) NOT NULL Prenotification Status
C=Confirmed
N=New
P=Pending
R=Rejected
S=Submitted
79 HOL_PROC_OPT Character(1) VARCHAR2(1) NOT NULL Holiday Processing Option
A=Due After Holiday
B=Due Before Holiday
N=Not Applicable
80 HOL_PROC_DAYS Number(3,0) SMALLINT NOT NULL Days before/after Holiday
81 HOL_PROC_OVERFL Character(1) VARCHAR2(1) NOT NULL Allow due date in next month
82 GRP_PYMNT_DT Character(1) VARCHAR2(1) NOT NULL Group by Payment Date
83 APPLY_NETTING Character(1) VARCHAR2(1) NOT NULL Apply Netting
84 EFT_LAYOUT_CD Character(10) VARCHAR2(10) NOT NULL EFT Layout Code
85 SPEEDCHART_KEY Character(10) VARCHAR2(10) NOT NULL SpeedChart Key