VIEW: APPS.CSM_MTL_SYSTEM_ITEMS_V

Source

View Text - Preformatted

SELECT acc.access_id, msi.inventory_item_id , msi.organization_id , msi.segment1 AS inventory_name, msi_tl.description , msi.enabled_flag , msi.start_date_active , msi.end_date_active , msi.primary_uom_code , msi.service_item_flag , msi.serviceable_product_flag , msi.material_billable_flag , msi.returnable_flag , msi.mtl_transactions_enabled_flag , msi.serv_req_enabled_code, msi.serial_number_control_code , msi.lot_control_code , msi.inventory_asset_flag , msi.purchasing_enabled_flag , msi.internal_order_enabled_flag , msi.internal_order_flag , msi.purchasing_item_flag , msi.restrict_subinventories_code, msi.comms_nl_trackable_flag , msi.stock_enabled_flag, msi.attribute1 , msi.attribute2 , msi.attribute3 , msi.attribute4 , msi.attribute5 , msi.attribute6 , msi.attribute7 , msi.attribute8 , msi.attribute9 , msi.attribute10 , msi.attribute11 , msi.attribute12, msi.attribute13 , msi.attribute14 , msi.attribute15 , msi.attribute_category, msi.contract_item_type_code, msi.recovered_part_disp_code, msi.revision_qty_control_code, msi.RESTRICT_LOCATORS_CODE, msi.LOCATION_CONTROL_CODE, msi.LIST_PRICE_PER_UNIT, UPPER(msi.segment1) AS inventory_name_upper FROM mtl_system_items_b msi, mtl_system_items_tl msi_tl, csm_mtl_system_items_acc acc , csm_user_inventory_org uorg WHERE acc.user_id = -1 AND acc.organization_id = uorg.organization_id AND uorg.USER_ID = asg_base.get_user_id AND acc.inventory_item_id = msi.inventory_item_id AND acc.organization_id = msi.organization_id AND msi_tl.inventory_item_id = acc.inventory_item_id AND msi_tl.organization_id = acc.organization_id AND msi_tl.language = asg_base.get_language UNION ALL SELECT acc.access_id, msi.inventory_item_id , msi.organization_id , msi.segment1 AS inventory_name, msi_tl.description , msi.enabled_flag , msi.start_date_active , msi.end_date_active , msi.primary_uom_code , msi.service_item_flag , msi.serviceable_product_flag , msi.material_billable_flag , msi.returnable_flag , msi.mtl_transactions_enabled_flag , msi.serv_req_enabled_code, msi.serial_number_control_code , msi.lot_control_code , msi.inventory_asset_flag , msi.purchasing_enabled_flag , msi.internal_order_enabled_flag , msi.internal_order_flag , msi.purchasing_item_flag , msi.restrict_subinventories_code, msi.comms_nl_trackable_flag , msi.stock_enabled_flag, msi.attribute1 , msi.attribute2 , msi.attribute3 , msi.attribute4 , msi.attribute5 , msi.attribute6 , msi.attribute7 , msi.attribute8 , msi.attribute9 , msi.attribute10 , msi.attribute11 , msi.attribute12, msi.attribute13 , msi.attribute14 , msi.attribute15 , msi.attribute_category, msi.contract_item_type_code, msi.recovered_part_disp_code, msi.revision_qty_control_code, msi.RESTRICT_LOCATORS_CODE, msi.LOCATION_CONTROL_CODE, msi.LIST_PRICE_PER_UNIT, UPPER(msi.segment1) AS inventory_name_upper FROM mtl_system_items_b msi, mtl_system_items_tl msi_tl, csm_mtl_system_items_acc acc WHERE acc.user_id = asg_base.get_user_id AND acc.inventory_item_id = msi.inventory_item_id AND acc.organization_id = msi.organization_id AND msi_tl.inventory_item_id = acc.inventory_item_id AND msi_tl.organization_id = acc.organization_id AND msi_tl.language = asg_base.get_language AND NOT EXISTS( SELECT 1 FROM csm_mtl_system_items_acc acc1, csm_user_inventory_org uorg1 WHERE acc1.user_id =-1 AND acc1.organization_id = uorg1.organization_id AND uorg1.user_id =asg_base.get_user_id AND acc.inventory_item_id = acc1.inventory_item_id AND acc1.organization_id = acc.organization_id)

View Text - HTML Formatted

SELECT ACC.ACCESS_ID
, MSI.INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID
, MSI.SEGMENT1 AS INVENTORY_NAME
, MSI_TL.DESCRIPTION
, MSI.ENABLED_FLAG
, MSI.START_DATE_ACTIVE
, MSI.END_DATE_ACTIVE
, MSI.PRIMARY_UOM_CODE
, MSI.SERVICE_ITEM_FLAG
, MSI.SERVICEABLE_PRODUCT_FLAG
, MSI.MATERIAL_BILLABLE_FLAG
, MSI.RETURNABLE_FLAG
, MSI.MTL_TRANSACTIONS_ENABLED_FLAG
, MSI.SERV_REQ_ENABLED_CODE
, MSI.SERIAL_NUMBER_CONTROL_CODE
, MSI.LOT_CONTROL_CODE
, MSI.INVENTORY_ASSET_FLAG
, MSI.PURCHASING_ENABLED_FLAG
, MSI.INTERNAL_ORDER_ENABLED_FLAG
, MSI.INTERNAL_ORDER_FLAG
, MSI.PURCHASING_ITEM_FLAG
, MSI.RESTRICT_SUBINVENTORIES_CODE
, MSI.COMMS_NL_TRACKABLE_FLAG
, MSI.STOCK_ENABLED_FLAG
, MSI.ATTRIBUTE1
, MSI.ATTRIBUTE2
, MSI.ATTRIBUTE3
, MSI.ATTRIBUTE4
, MSI.ATTRIBUTE5
, MSI.ATTRIBUTE6
, MSI.ATTRIBUTE7
, MSI.ATTRIBUTE8
, MSI.ATTRIBUTE9
, MSI.ATTRIBUTE10
, MSI.ATTRIBUTE11
, MSI.ATTRIBUTE12
, MSI.ATTRIBUTE13
, MSI.ATTRIBUTE14
, MSI.ATTRIBUTE15
, MSI.ATTRIBUTE_CATEGORY
, MSI.CONTRACT_ITEM_TYPE_CODE
, MSI.RECOVERED_PART_DISP_CODE
, MSI.REVISION_QTY_CONTROL_CODE
, MSI.RESTRICT_LOCATORS_CODE
, MSI.LOCATION_CONTROL_CODE
, MSI.LIST_PRICE_PER_UNIT
, UPPER(MSI.SEGMENT1) AS INVENTORY_NAME_UPPER
FROM MTL_SYSTEM_ITEMS_B MSI
, MTL_SYSTEM_ITEMS_TL MSI_TL
, CSM_MTL_SYSTEM_ITEMS_ACC ACC
, CSM_USER_INVENTORY_ORG UORG
WHERE ACC.USER_ID = -1
AND ACC.ORGANIZATION_ID = UORG.ORGANIZATION_ID
AND UORG.USER_ID = ASG_BASE.GET_USER_ID
AND ACC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND ACC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI_TL.INVENTORY_ITEM_ID = ACC.INVENTORY_ITEM_ID
AND MSI_TL.ORGANIZATION_ID = ACC.ORGANIZATION_ID
AND MSI_TL.LANGUAGE = ASG_BASE.GET_LANGUAGE UNION ALL SELECT ACC.ACCESS_ID
, MSI.INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID
, MSI.SEGMENT1 AS INVENTORY_NAME
, MSI_TL.DESCRIPTION
, MSI.ENABLED_FLAG
, MSI.START_DATE_ACTIVE
, MSI.END_DATE_ACTIVE
, MSI.PRIMARY_UOM_CODE
, MSI.SERVICE_ITEM_FLAG
, MSI.SERVICEABLE_PRODUCT_FLAG
, MSI.MATERIAL_BILLABLE_FLAG
, MSI.RETURNABLE_FLAG
, MSI.MTL_TRANSACTIONS_ENABLED_FLAG
, MSI.SERV_REQ_ENABLED_CODE
, MSI.SERIAL_NUMBER_CONTROL_CODE
, MSI.LOT_CONTROL_CODE
, MSI.INVENTORY_ASSET_FLAG
, MSI.PURCHASING_ENABLED_FLAG
, MSI.INTERNAL_ORDER_ENABLED_FLAG
, MSI.INTERNAL_ORDER_FLAG
, MSI.PURCHASING_ITEM_FLAG
, MSI.RESTRICT_SUBINVENTORIES_CODE
, MSI.COMMS_NL_TRACKABLE_FLAG
, MSI.STOCK_ENABLED_FLAG
, MSI.ATTRIBUTE1
, MSI.ATTRIBUTE2
, MSI.ATTRIBUTE3
, MSI.ATTRIBUTE4
, MSI.ATTRIBUTE5
, MSI.ATTRIBUTE6
, MSI.ATTRIBUTE7
, MSI.ATTRIBUTE8
, MSI.ATTRIBUTE9
, MSI.ATTRIBUTE10
, MSI.ATTRIBUTE11
, MSI.ATTRIBUTE12
, MSI.ATTRIBUTE13
, MSI.ATTRIBUTE14
, MSI.ATTRIBUTE15
, MSI.ATTRIBUTE_CATEGORY
, MSI.CONTRACT_ITEM_TYPE_CODE
, MSI.RECOVERED_PART_DISP_CODE
, MSI.REVISION_QTY_CONTROL_CODE
, MSI.RESTRICT_LOCATORS_CODE
, MSI.LOCATION_CONTROL_CODE
, MSI.LIST_PRICE_PER_UNIT
, UPPER(MSI.SEGMENT1) AS INVENTORY_NAME_UPPER
FROM MTL_SYSTEM_ITEMS_B MSI
, MTL_SYSTEM_ITEMS_TL MSI_TL
, CSM_MTL_SYSTEM_ITEMS_ACC ACC
WHERE ACC.USER_ID = ASG_BASE.GET_USER_ID
AND ACC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND ACC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI_TL.INVENTORY_ITEM_ID = ACC.INVENTORY_ITEM_ID
AND MSI_TL.ORGANIZATION_ID = ACC.ORGANIZATION_ID
AND MSI_TL.LANGUAGE = ASG_BASE.GET_LANGUAGE
AND NOT EXISTS( SELECT 1
FROM CSM_MTL_SYSTEM_ITEMS_ACC ACC1
, CSM_USER_INVENTORY_ORG UORG1
WHERE ACC1.USER_ID =-1
AND ACC1.ORGANIZATION_ID = UORG1.ORGANIZATION_ID
AND UORG1.USER_ID =ASG_BASE.GET_USER_ID
AND ACC.INVENTORY_ITEM_ID = ACC1.INVENTORY_ITEM_ID
AND ACC1.ORGANIZATION_ID = ACC.ORGANIZATION_ID)