BU_ITM_INV_VW11

(SQL View)
 Index Back

Item Business Unit Prompt View

BU item prompt view that will bring back all Items with status of Active, Hold, Discontinue, and Inactive in owned locations... this is to exclude consigned inventory.

SELECT DISTINCT A.BUSINESS_UNIT , A.INV_ITEM_ID , B.DESCR , B.INV_ITEM_GROUP , B.INV_PROD_FAM_CD FROM PS_BU_ITEMS_INV A , PS_PHYSICAL_INV D , PS_STOR_LOC_INV E , PS_CM_ITEM_LED_VW F , PS_MASTER_ITEM_TBL B , PS_SET_CNTRL_REC C WHERE A.BUSINESS_UNIT = C.SETCNTRLVALUE AND A.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.BUSINESS_UNIT = F.BUSINESS_UNIT AND A.BUSINESS_UNIT = E.BUSINESS_UNIT AND A.INV_ITEM_ID = B.INV_ITEM_ID AND A.INV_ITEM_ID = D.INV_ITEM_ID AND A.INV_ITEM_ID = F.INV_ITEM_ID AND D.STORAGE_AREA = E.STORAGE_AREA AND D.STOR_LEVEL_1 = E.STOR_LEVEL_1 AND D.STOR_LEVEL_2 = E.STOR_LEVEL_2 AND D.STOR_LEVEL_3 = E.STOR_LEVEL_3 AND D.STOR_LEVEL_4 = E.STOR_LEVEL_4 AND (D.QTY_BASE > 0 OR D.QTY_BASE < 0) AND E.NON_OWN_FLAG = 'N' AND (F.CM_METHOD = '6' OR F.CM_METHOD = '8') AND F.DEFAULT_LEDGER_GRP = 'Y' AND F.PRIMARY_LEDGER = 'Y' AND C.RECNAME = 'MASTER_ITEM_TBL' AND B.SETID = C.SETID AND A.INVENTORY_ITEM = 'Y' AND A.ITM_STATUS_CURRENT IN ('1','2','3','4')

  • Related Language Record: BU_ITM_INV_LVW

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

    Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT

    Prompt Table: SP_BUIN_NONVW

    2 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID

    Prompt Table: BU_ITM_INV_VW11

    3 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    4 INV_ITEM_GROUP Character(15) VARCHAR2(15) NOT NULL Item Group

    Prompt Table: INV_ITEM_GROUP

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

    Prompt Table: INV_ITEM_FAM