CNTRCT_SRCH_VW

(SQL View)
 Index Back

Contract Search temp 1

Temp table for contract search.

SELECT SETID , CNTRCT_ID , VENDOR_SETID , VENDOR_ID , VERSION_NBR , CNTRCT_TYPE , CNTRCT_PROC_OPT , CNTRCT_STYLE , DESCR , CNTRCT_BEGIN_DT , CNTRCT_EXPIRE_DT , CNTRCT_AUTO_DFLT , MSTR_CNTRCT_ID , PCHOUTPRICE_OPTION , CONTROL_TYPE , CNTRCT_LINE_NBR , CAT_LINE_NBR , UNIT_OF_MEASURE , CURRENCY_CD , VNDR_LOC , PO_CNTRCT_TYPE , CNT_TYPE_ORDER , ITM_SETID , INV_ITEM_ID , CATEGORY_SETID , CATEGORY_ID , QTY_LINE_MAX , QTY_LINE_MIN , AMT_LINE_MAX , AMT_LINE_MIN , UNIT_PRICE , MERCHANDISE_AMT , AMT_LINE_RELEASED , DESCR254_MIXED , REMAINING_QTY , REMAINING_AMOUNT , AUC_GROUP_ID , PRICE_CNTRCT_BASE , PRICE_ADJUSTED , CURRENCY_CD_CNTR , CNTRCT_CORP_FLG , QTY_TYPE , USE_CNTRCT_BASE , LINE_PRICE_TYPE , CNTRCT_STYLE_NAME , BUYER_ID , VNDR_CNTRCT_REF , MFG_ID , MFG_ITM_ID , ITM_ID_VNDR , VNDR_CATALOG_ID , ADJ_BEFORE , AMT_ONLY_FLG , PRICE_CAN_CHANGE , PRICE_DT_TYPE , PRICE_SHIPTO_ADJ , PRICE_UOM_ADJ , UPN_ID , UPN_TYPE_CD , TRANSACTION_PRICE , CNTRCT_PRIORITY , CNTRCT_ID_DST , PRIORITY , LN_TYPE , PRICE_INITIAL FROM PS_CNTRCT_SRCH_TM1

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 CNTRCT_ID Character(25) VARCHAR2(25) NOT NULL Buying Agreement ID
3 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
4 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
5 VERSION_NBR Number(5,0) INTEGER NOT NULL Contract Version number
6 CNTRCT_TYPE Character(2) VARCHAR2(2) NOT NULL Contract Type
AP=Recurring Voucher
GN=General Contract
MS=Master Contract
PO=Purchase Order
7 CNTRCT_PROC_OPT Character(4) VARCHAR2(4) NOT NULL Contract Process Option
AP=Recurring Voucher
BPO=Release to Single PO Only
DST=Distributor
GN=General Contract
GRPM=Group Multi Supplier
GRPS=Group Single Supplier
MFG=Manufacturer
PADV=Prepaid Voucher w/ Advance PO
PO=Purchase Order
PPAY=Prepaid Voucher
RPOV=Recurring PO Voucher
SPP=Special Purpose
8 CNTRCT_STYLE Character(10) VARCHAR2(10) NOT NULL Contract Style
9 DESCR Character(30) VARCHAR2(30) NOT NULL Description
10 CNTRCT_BEGIN_DT Date(10) DATE Contract Begin Date
11 CNTRCT_EXPIRE_DT Date(10) DATE Expire Date
12 CNTRCT_AUTO_DFLT Character(1) VARCHAR2(1) NOT NULL Auto defaulting option on the Procurement Contract
N=No
Y=Yes
13 MSTR_CNTRCT_ID Character(10) VARCHAR2(10) NOT NULL Master Contract ID

Prompt Table: CNTRCT_MSTR_VW

14 PCHOUTPRICE_OPTION Character(4) VARCHAR2(4) NOT NULL Punchout Pricing Option
AUCP=Always Use Contract Price
AUPP=Always Use Punchout Price
UPPL=Use Punchout Price if Lower
X=Not Used
15 CONTROL_TYPE Character(4) VARCHAR2(4) NOT NULL PO002 Manufacturer Contract
BU=Business Unit
CDOM=Contract Domain
SHIP=Ship To
X=Not Used
16 CNTRCT_LINE_NBR Number(5,0) INTEGER NOT NULL Buying Agreement Line Nbr
17 CAT_LINE_NBR Number(5,0) INTEGER NOT NULL Contract Category Line Number
18 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage

Prompt Table: %EDITTABLE6

19 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
20 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
21 PO_CNTRCT_TYPE Character(2) VARCHAR2(2) NOT NULL Contract Reference Type
C=Category
L=Line Item
O=Open Item
22 CNT_TYPE_ORDER Number(1,0) SMALLINT NOT NULL Sort Order for contract types
23 ITM_SETID Character(5) VARCHAR2(5) NOT NULL Item SetID
24 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
25 CATEGORY_SETID Character(5) VARCHAR2(5) NOT NULL Category SetID
26 CATEGORY_ID Character(5) VARCHAR2(5) NOT NULL Category ID
27 QTY_LINE_MAX Number(15,2) DECIMAL(14,2) NOT NULL Max Line Qty Per Contract
28 QTY_LINE_MIN Number(15,2) DECIMAL(14,2) NOT NULL Min Line Qty Per Order
29 AMT_LINE_MAX Number(27,3) DECIMAL(26,3) NOT NULL Max Line Amt Per Contract
30 AMT_LINE_MIN Number(27,3) DECIMAL(26,3) NOT NULL Min Line Amt Per Order
31 UNIT_PRICE Signed Number(17,5) DECIMAL(15,5) NOT NULL Unit Price
32 MERCHANDISE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Merchandise Amt F_KDHIL_R87H8 062804 MLM: Added new Line Amount Received Label
33 AMT_LINE_RELEASED Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Line Amt Released
34 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description
35 REMAINING_QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL 08/16/01 JNW (CN#PL900-1.0): Added
36 REMAINING_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Remaining Amount
37 AUC_GROUP_ID Character(18) VARCHAR2(18) NOT NULL GroupID
38 PRICE_CNTRCT_BASE Number(16,5) DECIMAL(15,5) NOT NULL Contract Base Price
39 PRICE_ADJUSTED Signed Number(17,5) DECIMAL(15,5) NOT NULL Adjusted Price
40 CURRENCY_CD_CNTR Character(3) VARCHAR2(3) NOT NULL Contract Base Currency
41 CNTRCT_CORP_FLG Character(1) VARCHAR2(1) NOT NULL Corporate Contract Flag
N=No
Y=Yes
42 QTY_TYPE Character(1) VARCHAR2(1) NOT NULL Quantity to Use for Prices
L=Line Quantity
S=Schedule Quantity
43 USE_CNTRCT_BASE Character(1) VARCHAR2(1) NOT NULL Use Contract Base Price
N=No
Y=Yes

Y/N Table Edit

44 LINE_PRICE_TYPE Character(1) VARCHAR2(1) NOT NULL Line Qty/Price Type
C=Current Order Quantity
Q=Quantity To Date
45 CNTRCT_STYLE_NAME Character(30) VARCHAR2(30) NOT NULL Style Description
46 BUYER_ID Character(30) VARCHAR2(30) NOT NULL Buyer
47 VNDR_CNTRCT_REF Character(30) VARCHAR2(30) NOT NULL Supplier Contract Ref
48 MFG_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer ID
49 MFG_ITM_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer's Item ID
50 ITM_ID_VNDR Character(50) VARCHAR2(50) NOT NULL Vendor Item ID
51 VNDR_CATALOG_ID Character(20) VARCHAR2(20) NOT NULL Vendor's Catalog Number
52 ADJ_BEFORE Character(1) VARCHAR2(1) NOT NULL Adjust Supplier Pricing First
N=After Contract Adjustments
Y=Before Contract Adjustments
53 AMT_ONLY_FLG Character(1) VARCHAR2(1) NOT NULL Amount Only
N=No
Y=Yes

Y/N Table Edit

54 PRICE_CAN_CHANGE Character(1) VARCHAR2(1) NOT NULL Price Can Be Changed on Order
N=No
Y=Yes

Y/N Table Edit

55 PRICE_DT_TYPE Character(1) VARCHAR2(1) NOT NULL Date to Use for Prices
D=Due Date
P=PO Date
56 PRICE_SHIPTO_ADJ Character(1) VARCHAR2(1) NOT NULL Use Supplier Price Shipto Adj
N=No
Y=Yes

Y/N Table Edit

57 PRICE_UOM_ADJ Character(1) VARCHAR2(1) NOT NULL Use Supp Price UOM Adjustments
N=No
Y=Yes

Y/N Table Edit

58 UPN_ID Character(20) VARCHAR2(20) NOT NULL Universal Product Number
59 UPN_TYPE_CD Character(4) VARCHAR2(4) NOT NULL Universal Product Number Format Type
60 TRANSACTION_PRICE Signed Number(28,3) DECIMAL(26,3) NOT NULL The preferred business price for a given deal transaction.
61 CNTRCT_PRIORITY Number(3,0) SMALLINT NOT NULL PO002 Manufacturer Contract. Contract Priority for contract search and auto default.
62 CNTRCT_ID_DST Character(25) VARCHAR2(25) NOT NULL Contract ID To
63 PRIORITY Number(3,0) SMALLINT NOT NULL Priority
64 LN_TYPE Character(4) VARCHAR2(4) NOT NULL Line Type
CAT=Catalog Item
CUST=Custom Item
DC=Direct Connect Item
DCEX=Exchange Item
DCPO=Punchout Item
DCRT=Round Trip
DCTP=Transparent Punchout Item
DESR=Ad-hoc Item
EF=Express Form Item
OS=Order Sheet
SDF=Supplemental Data Form
SPRO=Service
SR=ePro Special Request Item
SRFC=Fixed Cost Service Item
SRTM=Time and Material Service Item
SRVC=Variable Cost Service Item
65 PRICE_INITIAL Signed Number(17,5) DECIMAL(15,5) NOT NULL Punchout Price