WM_WO_POITEM_VW

(SQL View)
 Index Back

WO Proc Item Search View

Note: used in Sched PO Supplier Item Search

SELECT A.BUSINESS_UNIT , A.VENDOR_ID , A.VNDR_LOC , '1' AS WM_POITEM_SRC_TYPE , A.INV_ITEM_ID AS WM_POITEM_KEYSTR , A.UNIT_OF_MEASURE , A.CURRENCY_CD AS VENDOR_CURRENCY , A.VENDOR_SETID , A.VENDOR_NAME_SHORT , A.VENDOR_NAME1 , A.LOCATION_DESCR , A.DEFAULT_LOC_FLG , A.ITM_SETID , A.INV_ITEM_ID , A.ITEM_DESCR , CASE WHEN A.ITM_VNDR_PRIORITY > 0 AND A.ITM_VNDR_PRIORITY = P1.ITM_VNDR_PREFPRIOR THEN 'Y' ELSE 'N' END AS WM_PREF_VNDR_FLG , ' ' AS CNTRCT_SETID , ' ' AS CNTRCT_ID , 0 AS VERSION_NBR , 0 AS CNTRCT_LINE_NBR , ' ' AS DESCR , 'N' AS WM_CNTRCT_ITEM_FLG , ' ' AS AMT_ONLY_FLG , 0 AS MERCHANDISE_AMT , A.ITM_ID_VNDR , A.VNDR_CATALOG_ID , A.LEAD_TIME , A.DFLT_UOM , A.QTY_MIN , A.PRICE_VNDR , A.MFG_ID , A.MFG_NAME , A.MFG_ITM_ID , A.UPN_TYPE_CD , A.UPN_ID , A.PHYSICAL_NATURE , A.INV_ITEM_GROUP , A.INV_PROD_FAM_CD , A.CATEGORY_CD , A.CATEGORY_ID , A.DESCR254_MIXED FROM PS_WM_WO_VNDITM_VW A LEFT OUTER JOIN PS_PV_ITEM_VNDR_VW P1 ON P1.SETID = A.ITM_SETID AND P1.INV_ITEM_ID = A.INV_ITEM_ID AND P1.VENDOR_SETID = A.VENDOR_SETID AND P1.VENDOR_ID = A.VENDOR_ID AND P1.VNDR_LOC = A.VNDR_LOC WHERE NOT EXISTS ( SELECT 'X' FROM PS_WM_WO_VNDCLN_VW B2 WHERE B2.BUSINESS_UNIT = A.BUSINESS_UNIT AND B2.VENDOR_ID = A.VENDOR_ID AND B2.VNDR_LOC = A.VNDR_LOC AND B2.INV_ITEM_ID = A.INV_ITEM_ID AND B2.UNIT_OF_MEASURE = A.UNIT_OF_MEASURE AND B2.CURRENCY_CD = A.CURRENCY_CD) UNION SELECT B.BUSINESS_UNIT , B.VENDOR_ID , B.VNDR_LOC , CASE WHEN B.INV_ITEM_ID = ' ' THEN '2' ELSE '3' END AS WM_POITEM_SRC_TYPE , (B.CNTRCT_ID %Concat '-' %Concat %NumToChar(B.VERSION_NBR) %Concat '-' %Concat %NumToChar(B.CNTRCT_LINE_NBR)) AS WM_POITEM_KEYSTR , B.UNIT_OF_MEASURE , B.CURRENCY_CD AS VENDOR_CURRENCY , B.VENDOR_SETID , B.VENDOR_NAME_SHORT , B.VENDOR_NAME1 , B.LOCATION_DESCR , B.DEFAULT_LOC_FLG , B.ITM_SETID , B.INV_ITEM_ID , CASE WHEN B.INV_ITEM_ID = ' ' THEN B.DESCR ELSE B.ITEM_DESCR END , CASE WHEN P2.ITM_VNDR_PRIORITY > 0 AND P2.ITM_VNDR_PRIORITY = P2.ITM_VNDR_PREFPRIOR THEN 'Y' ELSE 'N' END AS WM_PREF_VNDR_FLG , B.CNTRCT_SETID , B.CNTRCT_ID , B.VERSION_NBR , B.CNTRCT_LINE_NBR , B.DESCR , CASE WHEN B.CNTRCT_LINE_NBR = 0 THEN 'N' ELSE 'Y' END AS WM_CNTRCT_ITEM_FLG , B.AMT_ONLY_FLG , B.MERCHANDISE_AMT , B.ITM_ID_VNDR , B.VNDR_CATALOG_ID , B.LEAD_TIME , B.DFLT_UOM , B.QTY_LINE_MIN AS QTY_MIN , CASE WHEN B.AMT_ONLY_FLG = 'Y' THEN B.MERCHANDISE_AMT ELSE B.PRICE_CNTRCT_BASE END AS PRICE_VNDR , B.MFG_ID , B.MFG_NAME , B.MFG_ITM_ID , B.UPN_TYPE_CD , B.UPN_ID , B.PHYSICAL_NATURE , B.INV_ITEM_GROUP , B.INV_PROD_FAM_CD , B.CATEGORY_CD , B.CATEGORY_ID , B.DESCR254_MIXED FROM PS_WM_WO_VNDCLN_VW B LEFT OUTER JOIN PS_PV_ITEM_VNDR_VW P2 ON P2.SETID = B.ITM_SETID AND P2.INV_ITEM_ID = B.INV_ITEM_ID AND P2.VENDOR_SETID = B.VENDOR_SETID AND P2.VENDOR_ID = B.VENDOR_ID AND P2.VNDR_LOC = B.VNDR_LOC

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
3 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
4 WM_POITEM_SRC_TYPE Character(1) VARCHAR2(1) NOT NULL Procurement Item Source Type
1=Item Catalog
2=Contract Line
3=Item/Contract
5 WM_POITEM_KEYSTR Character(50) VARCHAR2(50) NOT NULL Procurement Item Key String
6 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
7 VENDOR_CURRENCY Character(3) VARCHAR2(3) NOT NULL Supplier Currency
8 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
9 VENDOR_NAME_SHORT Character(14) VARCHAR2(14) NOT NULL Short Vendor Name
10 VENDOR_NAME1 Character(40) VARCHAR2(40) NOT NULL Vendor Name1
11 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location
12 DEFAULT_LOC_FLG Character(1) VARCHAR2(1) NOT NULL Use Default Location

Y/N Table Edit

13 ITM_SETID Character(5) VARCHAR2(5) NOT NULL Item SetID
14 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
15 ITEM_DESCR Character(30) VARCHAR2(30) NOT NULL Item Description
16 WM_PREF_VNDR_FLG Character(1) VARCHAR2(1) NOT NULL Preferred Supplier
N=No
Y=Yes
17 CNTRCT_SETID Character(5) VARCHAR2(5) NOT NULL SetID
18 CNTRCT_ID Character(25) VARCHAR2(25) NOT NULL Buying Agreement ID
19 VERSION_NBR Number(5,0) INTEGER NOT NULL Contract Version number
20 CNTRCT_LINE_NBR Number(5,0) INTEGER NOT NULL Buying Agreement Line Nbr
21 DESCR Character(30) VARCHAR2(30) NOT NULL Description
22 WM_CNTRCT_ITEM_FLG Character(1) VARCHAR2(1) NOT NULL Contract Item
N=No
Y=Yes
23 AMT_ONLY_FLG Character(1) VARCHAR2(1) NOT NULL Amount Only
N=No
Y=Yes
24 MERCHANDISE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Merchandise Amt F_KDHIL_R87H8 062804 MLM: Added new Line Amount Received Label
25 ITM_ID_VNDR Character(50) VARCHAR2(50) NOT NULL Vendor Item ID
26 VNDR_CATALOG_ID Character(20) VARCHAR2(20) NOT NULL Vendor's Catalog Number
27 LEAD_TIME Number(3,0) SMALLINT NOT NULL Lead Time Days
28 DFLT_UOM Character(1) VARCHAR2(1) NOT NULL Vendor Default UOM

Y/N Table Edit

29 QTY_MIN Number(16,4) DECIMAL(15,4) NOT NULL Minimum Quantity
30 PRICE_VNDR Number(16,5) DECIMAL(15,5) NOT NULL Vendor Price
31 MFG_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer ID
32 MFG_NAME Character(60) VARCHAR2(60) NOT NULL Manufacturer Name
33 MFG_ITM_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer's Item ID
34 UPN_TYPE_CD Character(4) VARCHAR2(4) NOT NULL Universal Product Number Format Type
35 UPN_ID Character(20) VARCHAR2(20) NOT NULL Universal Product Number
36 PHYSICAL_NATURE Character(1) VARCHAR2(1) NOT NULL Physical Nature
G=Goods
S=Services
37 INV_ITEM_GROUP Character(15) VARCHAR2(15) NOT NULL Item Group

Prompt Table: INV_ITEM_GROUP

38 INV_PROD_FAM_CD Character(10) VARCHAR2(10) NOT NULL Family

Prompt Table: INV_ITEM_FAM

39 CATEGORY_CD Character(18) VARCHAR2(18) NOT NULL Category Code
40 CATEGORY_ID Character(5) VARCHAR2(5) NOT NULL Category ID
41 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description