WM_MST_ITEM_VW

(SQL View)
 Index Back

Item Master Prompt View

WM Item Master prompt view

SELECT A.BUSINESS_UNIT , MST.SETID , MST.INV_ITEM_ID , MST.DESCR , A.BUSINESS_UNIT_IN , MST.UNIT_MEASURE_STD , MST.STAGED_DATE_FLAG , MST.INVENTORY_ITEM , MST.PROMISE_OPTION , MST.INV_ITEM_GROUP , MST.INV_PROD_FAM_CD , MST.CATEGORY_ID , CAT.CATEGORY_CD , MST.PHYSICAL_NATURE , MST.LAST_DTTM_UPDATE FROM PS_BUS_UNIT_TBL_WM A , PS_MASTER_ITEM_TBL MST , PS_ITM_CAT_TBL CAT WHERE 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')

  • Related Language Record: WM_MST_ITM_LVW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    2 SETID Character(5) VARCHAR2(5) NOT NULL SetID

    Default Value: OPR_DEF_TBL_FS.SETID

    3 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
    4 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    5 BUSINESS_UNIT_IN Character(5) VARCHAR2(5) NOT NULL Inventory Unit
    6 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
    7 STAGED_DATE_FLAG Character(1) VARCHAR2(1) NOT NULL Staged Date Control
    N=Staged Date Tracking OFF
    Y=Staged Date Tracking ON
    8 INVENTORY_ITEM Character(1) VARCHAR2(1) NOT NULL Inventory Item
    N=No
    Y=Yes
    9 PROMISE_OPTION Character(1) VARCHAR2(1) NOT NULL Order Promise Option
    2=Perform ATP Reservations
    10 INV_ITEM_GROUP Character(15) VARCHAR2(15) NOT NULL Item Group

    Prompt Table: INV_ITEM_GROUP

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

    Prompt Table: INV_ITEM_FAM

    12 CATEGORY_ID Character(5) VARCHAR2(5) NOT NULL Category ID
    13 CATEGORY_CD Character(18) VARCHAR2(18) NOT NULL Category Code
    14 PHYSICAL_NATURE Character(1) VARCHAR2(1) NOT NULL Physical Nature
    G=Goods
    S=Services
    15 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.