WM_WO_VNDITM_VW

(SQL View)
 Index Back

WO Vendor Loc'n Item View

Note: used in Sched PO Supplier Item Search

SELECT A.BUSINESS_UNIT , A.VENDOR_ID , A.VNDR_LOC , B.INV_ITEM_ID , D.UNIT_OF_MEASURE , E.CURRENCY_CD , E.QTY_MIN , A.VENDOR_SETID , A.VENDOR_NAME_SHORT , A.VENDOR_NAME1 , A.LOCATION_DESCR , A.DEFAULT_LOC_FLG , B.ITM_ID_VNDR , B.VNDR_CATALOG_ID , B.ITM_STATUS , B.ITM_VNDR_PRIORITY , B.ITMV_PRIORITY_LOC , E.LEAD_TIME , D.DFLT_UOM , CASE WHEN M2.MFG_ID IS NULL THEN ' ' ELSE M2.MFG_ID END , CASE WHEN M2.MFG_NAME IS NULL THEN ' ' ELSE M2.MFG_NAME END , CASE WHEN M2.MFG_ITM_ID IS NULL THEN ' ' ELSE M2.MFG_ITM_ID END , CASE WHEN M2.PREFERRED_MFG IS NULL THEN ' ' ELSE M2.PREFERRED_MFG END , CASE WHEN M2.UPN_TYPE_CD IS NULL THEN ' ' ELSE M2.UPN_TYPE_CD END , CASE WHEN M2.UPN_ID IS NULL THEN ' ' ELSE M2.UPN_ID END , E.PRICE_VNDR , A.ITM_SETID , F.DESCR AS ITEM_DESCR , F.PHYSICAL_NATURE , F.INV_ITEM_GROUP , F.INV_PROD_FAM_CD , F.CATEGORY_CD , F.CATEGORY_ID , F.DESCR AS DESCR254_MIXED FROM PS_WM_WO_VNDLOC_VW A , PS_ITM_VENDOR B , PS_ITM_VENDOR_LOC C , PS_ITM_VNDR_UOM D LEFT OUTER JOIN PS_WM_VITM_MFG2_VW M2 ON M2.VENDOR_SETID = D.VENDOR_SETID AND M2.VENDOR_ID = D.VENDOR_ID AND M2.VNDR_LOC = D.VNDR_LOC AND M2.ITM_SETID = D.SETID AND M2.INV_ITEM_ID = D.INV_ITEM_ID AND M2.UNIT_OF_MEASURE = D.UNIT_OF_MEASURE , PS_ITM_VNDR_UOM_PR E , PS_WM_MST_ITEM_VW F WHERE B.SETID = A.ITM_SETID AND B.VENDOR_SETID = A.VENDOR_SETID AND B.VENDOR_ID = A.VENDOR_ID AND C.SETID = B.SETID AND C.INV_ITEM_ID = B.INV_ITEM_ID AND C.VENDOR_SETID = B.VENDOR_SETID AND C.VENDOR_ID = B.VENDOR_ID AND C.VNDR_LOC = A.VNDR_LOC AND D.SETID = C.SETID AND D.INV_ITEM_ID = C.INV_ITEM_ID AND D.VENDOR_SETID = C.VENDOR_SETID AND D.VENDOR_ID = C.VENDOR_ID AND D.VNDR_LOC = C.VNDR_LOC AND E.SETID = D.SETID AND E.INV_ITEM_ID = D.INV_ITEM_ID AND E.VENDOR_SETID = D.VENDOR_SETID AND E.VENDOR_ID = D.VENDOR_ID AND E.VNDR_LOC = D.VNDR_LOC AND E.UNIT_OF_MEASURE = D.UNIT_OF_MEASURE AND E.EFFDT = ( SELECT MAX(E2.EFFDT) FROM PS_ITM_VNDR_UOM_PR E2 WHERE E2.SETID = E.SETID AND E2.INV_ITEM_ID = E.INV_ITEM_ID AND E2.VENDOR_SETID = E.VENDOR_SETID AND E2.VENDOR_ID = E.VENDOR_ID AND E2.VNDR_LOC = E.VNDR_LOC AND E2.UNIT_OF_MEASURE = E.UNIT_OF_MEASURE AND E2.CURRENCY_CD = E.CURRENCY_CD AND E2.QTY_MIN = E.QTY_MIN AND E2.EFFDT <= %CurrentDateIn AND E2.EFF_STATUS = 'A') AND F.BUSINESS_UNIT = A.BUSINESS_UNIT AND F.SETID = E.SETID AND F.INV_ITEM_ID = E.INV_ITEM_ID

# 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 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
5 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
6 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
7 QTY_MIN Number(16,4) DECIMAL(15,4) NOT NULL Minimum Quantity
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_ID_VNDR Character(50) VARCHAR2(50) NOT NULL Vendor Item ID
14 VNDR_CATALOG_ID Character(20) VARCHAR2(20) NOT NULL Vendor's Catalog Number
15 ITM_STATUS Character(1) VARCHAR2(1) NOT NULL Item Status
A=Active
D=Discntued
H=Hold
I=Inactive
X=To be Del
16 ITM_VNDR_PRIORITY Number(3,0) SMALLINT NOT NULL Item Vendor Priority
17 ITMV_PRIORITY_LOC Character(10) VARCHAR2(10) NOT NULL Item Supplier Priority Loc
18 LEAD_TIME Number(3,0) SMALLINT NOT NULL Lead Time Days
19 DFLT_UOM Character(1) VARCHAR2(1) NOT NULL Vendor Default UOM

Y/N Table Edit

20 MFG_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer ID
21 MFG_NAME Character(60) VARCHAR2(60) NOT NULL Manufacturer Name
22 MFG_ITM_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer's Item ID
23 PREFERRED_MFG Character(1) VARCHAR2(1) NOT NULL Item's Preferred Manufacturer

Y/N Table Edit

24 UPN_TYPE_CD Character(4) VARCHAR2(4) NOT NULL Universal Product Number Format Type
25 UPN_ID Character(20) VARCHAR2(20) NOT NULL Universal Product Number
26 PRICE_VNDR Number(16,5) DECIMAL(15,5) NOT NULL Vendor Price
27 ITM_SETID Character(5) VARCHAR2(5) NOT NULL Item SetID
28 ITEM_DESCR Character(30) VARCHAR2(30) NOT NULL Item Description
29 PHYSICAL_NATURE Character(1) VARCHAR2(1) NOT NULL Physical Nature
G=Goods
S=Services
30 INV_ITEM_GROUP Character(15) VARCHAR2(15) NOT NULL Item Group

Prompt Table: INV_ITEM_GROUP

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

Prompt Table: INV_ITEM_FAM

32 CATEGORY_CD Character(18) VARCHAR2(18) NOT NULL Category Code
33 CATEGORY_ID Character(5) VARCHAR2(5) NOT NULL Category ID
34 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description