CNTRCT_LN_SRCH

(SQL View)
 Index Back

Contract Line Search View


SELECT DISTINCT A.SETID , A.CNTRCT_ID , A.VERSION_NBR , E.CATEGORY_CD , A.CNTRCT_LINE_NBR , A.INV_ITEM_ID , A.DESCR254_MIXED , A.ITM_ID_VNDR , A.VNDR_CATALOG_ID , ' ' , A.MFG_ID , A.MFG_ITM_ID , ' ' , ' ' , A.PHYSICAL_NATURE FROM PS_CNTRCT_LINE A , PS_CNTRCT_HDR D , PS_ITM_CAT_TBL E , PS_CNTRCT_LINE_UOM B WHERE A.CATEGORY_ID <> ' ' AND A.UNIT_OF_MEASURE <> ' ' AND A.CNTRCT_LN_TYPE = 'I' AND A.DESCR254_MIXED <> ' ' AND A.LINE_STATUS = 'O' AND D.VERSION_STATUS = 'C' AND D.CNTRCT_PROC_OPT <> 'MFG' AND A.SETID = D.SETID AND A.CNTRCT_ID = D.CNTRCT_ID AND A.VERSION_NBR = D.VERSION_NBR AND A.ITM_SETID = E.SETID AND A.CATEGORY_ID = E.CATEGORY_ID AND D.SETID =B.SETID AND D.CNTRCT_ID = B.CNTRCT_ID AND D.VERSION_NBR = B.VERSION_NBR AND A.SETID =B.SETID AND A.CNTRCT_ID = B.CNTRCT_ID AND A.VERSION_NBR = B.VERSION_NBR AND A.CNTRCT_LINE_NBR = B.CNTRCT_LINE_NBR AND B.SETID = E.SETID AND EXISTS ( SELECT 'X' FROM PS_CNTRCT_LINE_UOM B , PS_CNTRCT_LN_SHIP C WHERE B.SETID = A.SETID AND B.CNTRCT_ID = A.CNTRCT_ID AND B.VERSION_NBR = A.VERSION_NBR AND B.CNTRCT_LINE_NBR = A.CNTRCT_LINE_NBR AND C.SETID = A.SETID AND C.CNTRCT_ID = A.CNTRCT_ID AND C.VERSION_NBR = A.VERSION_NBR AND C.CNTRCT_LINE_NBR = A.CNTRCT_LINE_NBR AND B.VNDR_LOC <> ' ' AND C.QTY_SCHED <> 0) AND E.EFFDT = ( SELECT MAX(F.EFFDT) FROM PS_ITM_CAT_TBL F WHERE E.SETID = F.SETID AND E.CATEGORY_TYPE = F.CATEGORY_TYPE AND E.CATEGORY_CD = F.CATEGORY_CD AND E.CATEGORY_ID = F.CATEGORY_ID AND F.EFFDT <= %CurrentDateIn) UNION ALL SELECT DISTINCT A.SETID , A.CNTRCT_ID , A.VERSION_NBR , E.CATEGORY_CD , A.CNTRCT_LINE_NBR , A.INV_ITEM_ID , A.DESCR254_MIXED , A.ITM_ID_VNDR , A.VNDR_CATALOG_ID , ' ' , A.MFG_ID , A.MFG_ITM_ID , ' ' , ' ' , A.PHYSICAL_NATURE FROM PS_CNTRCT_LINE A , PS_CNTRCT_HDR D , PS_ITM_CAT_TBL E ,PS_CNTRCT_LINE_UOM B WHERE A.CATEGORY_ID <> ' ' AND A.UNIT_OF_MEASURE <> ' ' AND A.CNTRCT_LN_TYPE = 'I' AND A.DESCR254_MIXED <> ' ' AND A.LINE_STATUS = 'O' AND D.VERSION_STATUS = 'C' AND D.CNTRCT_PROC_OPT = 'MFG' AND A.SETID = D.SETID AND A.CNTRCT_ID = D.CNTRCT_ID AND A.VERSION_NBR = D.VERSION_NBR AND A.ITM_SETID = E.SETID AND A.CATEGORY_ID = E.CATEGORY_ID AND D.SETID =B.SETID AND D.CNTRCT_ID = B.CNTRCT_ID AND D.VERSION_NBR = B.VERSION_NBR AND A.SETID =B.SETID AND A.CNTRCT_ID = B.CNTRCT_ID AND A.CNTRCT_LINE_NBR = B.CNTRCT_LINE_NBR AND A.VERSION_NBR = B.VERSION_NBR AND B.SETID = E.SETID AND E.EFFDT = ( SELECT MAX(F.EFFDT) FROM PS_ITM_CAT_TBL F WHERE E.SETID = F.SETID AND E.CATEGORY_TYPE = F.CATEGORY_TYPE AND E.CATEGORY_CD = F.CATEGORY_CD AND E.CATEGORY_ID = F.CATEGORY_ID AND F.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID

Default Value: OPR_DEF_TBL_FS.SETID

2 CNTRCT_ID Character(25) VARCHAR2(25) NOT NULL Buying Agreement ID

Default Value: NEXT

3 VERSION_NBR Number(5,0) INTEGER NOT NULL Contract Version number
4 CATEGORY_CD Character(18) VARCHAR2(18) NOT NULL Category Code
5 CNTRCT_LINE_NBR Number(5,0) INTEGER NOT NULL Buying Agreement Line Nbr
6 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
7 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description
8 ITM_ID_VNDR Character(50) VARCHAR2(50) NOT NULL Vendor Item ID
9 VNDR_CATALOG_ID Character(20) VARCHAR2(20) NOT NULL Vendor's Catalog Number
10 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
11 MFG_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer ID
12 MFG_ITM_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer's Item ID
13 UPN_TYPE_CD Character(4) VARCHAR2(4) NOT NULL Universal Product Number Format Type
14 UPN_ID Character(20) VARCHAR2(20) NOT NULL Universal Product Number
15 PHYSICAL_NATURE Character(1) VARCHAR2(1) NOT NULL Physical Nature
G=Goods
S=Services