WM_ITEM_SRCH_VW

(SQL View)
 Index Back

Item Master Prompt View

Master item view for WM items

SELECT USR.OPRID , A.BUSINESS_UNIT , B.INV_ITEM_ID , C.SETID , C.DESCR , CASE WHEN B.SOURCE_CODE = '5' THEN '1A' ELSE '10' END AS WM_MATL_ITEM_TYPE , A.BUSINESS_UNIT_IN , C.UNIT_MEASURE_STD , B.SOURCE_CODE , ' ' , C.INV_ITEM_GROUP , C.INV_PROD_FAM_CD , ' ' , ' ' , M.MFG_ID , M.MFG_ITM_ID , CASE WHEN B.DT_TIMESTAMP > C.LAST_DTTM_UPDATE THEN B.DT_TIMESTAMP ELSE C.LAST_DTTM_UPDATE END FROM PS_OPR_DEF_TBL_WM USR , PS_BUS_UNIT_TBL_WM A , PS_BU_ITEMS_INV B , (PS_MASTER_ITEM_TBL C LEFT OUTER JOIN PS_ITEM_MFG M ON M.SETID = C.SETID AND M.INV_ITEM_ID = C.INV_ITEM_ID) , PS_SET_CNTRL_REC D WHERE USR.WO_ALLOW_INV = 'Y' AND B.BUSINESS_UNIT = A.BUSINESS_UNIT_IN AND B.INVENTORY_ITEM = 'Y' AND B.ITM_STATUS_CURRENT IN ('1','2','3') AND A.BUSINESS_UNIT_IN = D.SETCNTRLVALUE AND D.RECNAME = 'MASTER_ITEM_TBL' AND C.CFG_CODE_OPT = 'N' AND B.STAGED_DATE_FLAG = 'N' AND C.SETID = D.SETID AND C.INV_ITEM_ID = B.INV_ITEM_ID UNION ALL SELECT USR.OPRID , A.BUSINESS_UNIT , MST.INV_ITEM_ID , MST.SETID , MST.DESCR , '20' AS WM_MATL_ITEM_TYPE , A.BUSINESS_UNIT_IN , MST.UNIT_MEASURE_STD , MST.STAGED_DATE_FLAG , MST.PROMISE_OPTION , MST.INV_ITEM_GROUP , MST.INV_PROD_FAM_CD , MST.CATEGORY_ID , CAT.CATEGORY_CD , MFG.MFG_ID , MFG.MFG_ITM_ID , MST.LAST_DTTM_UPDATE FROM PS_OPR_DEF_TBL_WM USR , PS_BUS_UNIT_TBL_WM A , (PS_MASTER_ITEM_TBL MST LEFT OUTER JOIN PS_ITEM_MFG MFG ON MFG.SETID = MST.SETID AND MFG.INV_ITEM_ID = MST.INV_ITEM_ID) , PS_ITM_CAT_TBL CAT WHERE USR.WO_ALLOW_PROCURE = 'Y' AND MST.SETID = ( SELECT I.SETID FROM PS_SET_CNTRL_REC I WHERE I.SETCNTRLVALUE = A.BUSINESS_UNIT_IN AND I.REC_GROUP_ID = 'FS_18' AND I.RECNAME = 'MASTER_ITEM_TBL') AND MST.CFG_CODE_OPT = 'N' AND MST.STAGED_DATE_FLAG = 'N' AND MST.ITM_STATUS_CURRENT IN ('1','2','3') AND (MST.INVENTORY_ITEM = 'N' OR EXISTS ( SELECT 'X' FROM PS_BU_ITEMS_INV J WHERE J.BUSINESS_UNIT = A.BUSINESS_UNIT_IN AND J.INV_ITEM_ID = MST.INV_ITEM_ID AND (J.INVENTORY_ITEM = 'N' OR J.ITM_STATUS_CURRENT NOT IN ('1','2','3'))) OR NOT EXISTS ( SELECT 'X' FROM PS_BU_ITEMS_INV K WHERE K.BUSINESS_UNIT = A.BUSINESS_UNIT_IN AND K.INV_ITEM_ID = MST.INV_ITEM_ID)) AND CAT.SETID = MST.SETID AND CAT.CATEGORY_ID = MST.CATEGORY_ID AND CAT.EFFDT = ( SELECT MAX(L.EFFDT) FROM PS_ITM_CAT_TBL L WHERE L.SETID = CAT.SETID AND L.CATEGORY_TYPE = CAT.CATEGORY_TYPE AND L.CATEGORY_CD = CAT.CATEGORY_CD AND L.CATEGORY_ID = CAT.CATEGORY_ID AND L.EFF_STATUS = 'A')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
3 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
4 SETID Character(5) VARCHAR2(5) NOT NULL SetID
5 DESCR Character(30) VARCHAR2(30) NOT NULL Description
6 WM_MATL_ITEM_TYPE Character(2) VARCHAR2(2) NOT NULL Material Item Type
10=Inventory
1A=Floor Stock
20=Procurement
2A=On Hand
7 BUSINESS_UNIT_IN Character(5) VARCHAR2(5) NOT NULL Inventory Unit
8 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
9 STAGED_DATE_FLAG Character(1) VARCHAR2(1) NOT NULL Staged Date Control
N=Staged Date Tracking OFF
Y=Staged Date Tracking ON
10 PROMISE_OPTION Character(1) VARCHAR2(1) NOT NULL Order Promise Option
2=Perform ATP Reservations
11 INV_ITEM_GROUP Character(15) VARCHAR2(15) NOT NULL Item Group

Prompt Table: INV_ITEM_GROUP

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

Prompt Table: INV_ITEM_FAM

13 CATEGORY_ID Character(5) VARCHAR2(5) NOT NULL Category ID
14 CATEGORY_CD Character(18) VARCHAR2(18) NOT NULL Category Code
15 MFG_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer ID

Prompt Table: MANUFACTURER

16 MFG_ITM_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer's Item ID

Prompt Table: ITEM_MFG_ID_VW

17 LAST_DTTM_UPDATE DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.