CNT_SRCH_VW_O2

(SQL View)
 Index Back

Contract Search temp 1

Temp table for contract search.

SELECT %Sql(CNTRCT_SRC_HDR_FLDS) , 0 , 0 , C.UNIT_OF_MEASURE , C.CURRENCY_CD , C.VNDR_LOC , 'O' , 3 , C.SETID , C.INV_ITEM_ID , ' ' , ' ' , 0 , 0 , 0 , 0 , 0 , 0 , 0 , ' ' , 0 , 0 , ' ' , C.PRICE_VNDR , 0 , ' ' , ' ' , A.CNTRCT_CORP_FLG , ' ' , ' ' , ' ' , B.CNTRCT_STYLE_NAME , A.BUYER_ID , A.VNDR_CNTRCT_REF , D.MFG_ID , D.MFG_ITM_ID , D.ITM_ID_VNDR , D.VNDR_CATALOG_ID , D.ADJ_BEFORE , D.AMT_ONLY_FLG , D.PRICE_CAN_CHANGE , D.PRICE_DT_TYPE , D.PRICE_SHIPTO_ADJ , D.PRICE_UOM_ADJ , ' ' , ' ' , 0 , 2 , ' ' , 0 , ' ' , 0 FROM PS_CNTRCT_HDR A , PS_ITM_VNDR_UOM_PR C , PS_CNTRCT_STYLE B , PS_CNTRCT_LINE D WHERE A.CNTRCT_STATUS = 'A' AND A.VERSION_STATUS = 'C' AND A.CNTRCT_TYPE <> 'AP' AND A.VENDOR_SETID = C.VENDOR_SETID AND A.VENDOR_ID = C.VENDOR_ID AND A.SETID = D.SETID AND A.CNTRCT_ID = D.CNTRCT_ID AND A.VERSION_NBR = D.VERSION_NBR AND A.CNTRCT_STYLE = B.CNTRCT_STYLE AND B.CNTRCT_STRUCT_CD NOT IN ('ADHD','GRPM','GRPS','SPP','AP','PPAY', 'POD','SED', 'MFG', 'DST') AND 0 = ( SELECT COUNT(*) FROM PS_CNTRCT_BPO_XREF C WHERE C.SETID = A.SETID AND C.CNTRCT_ID = A.CNTRCT_ID) AND C.EFFDT = ( SELECT MAX(EFFDT) FROM PS_ITM_VNDR_UOM_PR WHERE C.SETID = SETID AND C.INV_ITEM_ID = INV_ITEM_ID AND C.VENDOR_SETID = VENDOR_SETID AND C.VENDOR_ID = VENDOR_ID AND C.VNDR_LOC = VNDR_LOC AND C.UNIT_OF_MEASURE = UNIT_OF_MEASURE AND C.CURRENCY_CD = CURRENCY_CD AND C.QTY_MIN = QTY_MIN AND C.EFF_STATUS = 'A')

# 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 CNTR_LN_TYPE_ID Character(30) VARCHAR2(30) NOT NULL Contract Line Type ID
42 CNTRCT_CORP_FLG Character(1) VARCHAR2(1) NOT NULL Corporate Contract Flag
N=No
Y=Yes
43 QTY_TYPE Character(1) VARCHAR2(1) NOT NULL Quantity to Use for Prices
L=Line Quantity
S=Schedule Quantity
44 USE_CNTRCT_BASE Character(1) VARCHAR2(1) NOT NULL Use Contract Base Price
N=No
Y=Yes
45 LINE_PRICE_TYPE Character(1) VARCHAR2(1) NOT NULL Line Qty/Price Type
C=Current Order Quantity
Q=Quantity To Date
46 CNTRCT_STYLE_NAME Character(30) VARCHAR2(30) NOT NULL Style Description
47 BUYER_ID Character(30) VARCHAR2(30) NOT NULL Buyer
48 VNDR_CNTRCT_REF Character(30) VARCHAR2(30) NOT NULL Supplier Contract Ref
49 MFG_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer ID
50 MFG_ITM_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer's Item ID
51 ITM_ID_VNDR Character(50) VARCHAR2(50) NOT NULL Vendor Item ID
52 VNDR_CATALOG_ID Character(20) VARCHAR2(20) NOT NULL Vendor's Catalog Number
53 ADJ_BEFORE Character(1) VARCHAR2(1) NOT NULL Adjust Supplier Pricing First
N=After Contract Adjustments
Y=Before Contract Adjustments
54 AMT_ONLY_FLG Character(1) VARCHAR2(1) NOT NULL Amount Only
N=No
Y=Yes
55 PRICE_CAN_CHANGE Character(1) VARCHAR2(1) NOT NULL Price Can Be Changed on Order
N=No
Y=Yes
56 PRICE_DT_TYPE Character(1) VARCHAR2(1) NOT NULL Date to Use for Prices
D=Due Date
P=PO Date
57 PRICE_SHIPTO_ADJ Character(1) VARCHAR2(1) NOT NULL Use Supplier Price Shipto Adj
N=No
Y=Yes
58 PRICE_UOM_ADJ Character(1) VARCHAR2(1) NOT NULL Use Supp Price UOM Adjustments
N=No
Y=Yes
59 UPN_ID Character(20) VARCHAR2(20) NOT NULL Universal Product Number
60 UPN_TYPE_CD Character(4) VARCHAR2(4) NOT NULL Universal Product Number Format Type
61 TRANSACTION_PRICE Signed Number(28,3) DECIMAL(26,3) NOT NULL The preferred business price for a given deal transaction.
62 CNTRCT_PRIORITY Number(3,0) SMALLINT NOT NULL PO002 Manufacturer Contract. Contract Priority for contract search and auto default.
63 CNTRCT_ID_DST Character(25) VARCHAR2(25) NOT NULL Contract ID To
64 PRIORITY Number(3,0) SMALLINT NOT NULL Priority
65 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
66 PRICE_INITIAL Signed Number(17,5) DECIMAL(15,5) NOT NULL Punchout Price