View: MTL_SYSTEM_ITEMS_ER1_V

Product:	INV - Inventory
Description:	ERES : View to support ERES framework for Items
Implementation/DBA Data:	APPS.MTL_SYSTEM_ITEMS_ER1_V

View Text

SELECT MSI.INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID
, ORG3.ORGANIZATION_CODE
, ORG4.NAME
, CAT.CONCATENATED_SEGMENTS
, MSI.CONCATENATED_SEGMENTS
, MSTL.DESCRIPTION
, MSI.INVENTORY_ITEM_STATUS_CODE
, INV_MEANING_SEL.C_FNDCOMMON(MSI.ITEM_TYPE
, 'ITEM_TYPE')
, INV_MEANING_SEL.C_UNITMEASURE(MSI.PRIMARY_UOM_CODE)
, INV_MEANING_SEL.C_FND_LOOKUP_VL(MSI.TRACKING_QUANTITY_IND
, 'INV_TRACKING_UOM_TYPE')
, INV_MEANING_SEL.C_FND_LOOKUP_VL(MSI.ONT_PRICING_QTY_SOURCE
, 'INV_PRICING_UOM_TYPE')
, INV_MEANING_SEL.C_UNITMEASURE(MSI.SECONDARY_UOM_CODE)
, INV_MEANING_SEL.C_FND_LOOKUP_VL(MSI.SECONDARY_DEFAULT_IND
, 'INV_DEFAULTING_UOM_TYPE')
, MSI.DUAL_UOM_DEVIATION_HIGH
, MSI.DUAL_UOM_DEVIATION_LOW
, INV_MEANING_SEL.C_MFG_LOOKUP(MSI.ALLOWED_UNITS_LOOKUP_CODE
, 'MTL_CONVERSION_TYPE')
, MSTL.LONG_DESCRIPTION
, MSI.INVENTORY_ITEM_FLAG
, MSI.STOCK_ENABLED_FLAG
, MSI.MTL_TRANSACTIONS_ENABLED_FLAG
, INV_MEANING_SEL.C_MFG_LOOKUP(MSI.REVISION_QTY_CONTROL_CODE
, 'MTL_ENG_QUANTITY')
, INV_MEANING_SEL.C_MFG_LOOKUP(MSI.RESERVABLE_TYPE
, 'MTL_RESERVATION_CONTROL')
, MSI.CHECK_SHORTAGES_FLAG
, INV_MEANING_SEL.C_MFG_LOOKUP(MSI.LOCATION_CONTROL_CODE
, 'MTL_LOCATION_CONTROL')
, INV_MEANING_SEL.C_MFG_LOOKUP(MSI.RESTRICT_SUBINVENTORIES_CODE
, 'MTL_SUBINVENTORY_RESTRICTIONS')
, INV_MEANING_SEL.C_MFG_LOOKUP(MSI.RESTRICT_LOCATORS_CODE
, 'MTL_LOCATOR_RESTRICTIONS')
, MSI.LOT_SPLIT_ENABLED
, MSI.LOT_MERGE_ENABLED
, MSI.LOT_TRANSLATE_ENABLED
, MSI.LOT_SUBSTITUTION_ENABLED
, MSI.BULK_PICKED_FLAG
, SUB3.SUBINVENTORY_CODE
, INV_MEANING_SEL.C_MFG_LOOKUP(MSI.SHELF_LIFE_CODE
, 'MTL_SHELF_LIFE')
, MSI.SHELF_LIFE_DAYS
, INV_MEANING_SEL.C_MFG_LOOKUP(MSI.LOT_CONTROL_CODE
, 'MTL_LOT_CONTROL')
, MSI.START_AUTO_LOT_NUMBER
, MSI.AUTO_LOT_ALPHA_PREFIX
, INV_MEANING_SEL.C_MFG_LOOKUP(MSI.SERIAL_NUMBER_CONTROL_CODE
, 'MTL_SERIAL_NUMBER')
, MSI.START_AUTO_SERIAL_NUMBER
, MSI.AUTO_SERIAL_ALPHA_PREFIX
, MSI.LOT_STATUS_ENABLED
, INV_MEANING_SEL.C_LOT_LOOKUP(MSI.DEFAULT_LOT_STATUS_ID)
, MSI.SERIAL_STATUS_ENABLED
, INV_MEANING_SEL.C_SERIAL_LOOKUP(MSI.DEFAULT_SERIAL_STATUS_ID)
, MSI.BOM_ENABLED_FLAG
, INV_MEANING_SEL.C_MFG_LOOKUP(MSI.BOM_ITEM_TYPE
, 'BOM_ITEM_TYPE')
, MSI2.CONCATENATED_SEGMENTS
, MSI2.DESCRIPTION
, INV_MEANING_SEL.C_FND_LOOKUP_VL(MSI.CONFIG_MODEL_TYPE
, 'CZ_CONFIG_MODEL_TYPE')
, INV_MEANING_SEL.C_FND_LOOKUP_VL(MSI.EFFECTIVITY_CONTROL
, 'MTL_EFFECTIVITY_CONTROL')
, MSI.ENG_ITEM_FLAG
, MSI.EAM_ITEM_TYPE
, INV_MEANING_SEL.C_MFG_LOOKUP(MSI.EAM_ACTIVITY_TYPE_CODE
, 'MTL_EAM_ACTIVITY_TYPE')
, INV_MEANING_SEL.C_MFG_LOOKUP(MSI.EAM_ACTIVITY_CAUSE_CODE
, 'MTL_EAM_ACTIVITY_CAUSE')
, INV_MEANING_SEL.C_MFG_LOOKUP(MSI.EAM_ACTIVITY_SOURCE_CODE
, 'MTL_EAM_ACTIVITY_SOURCE')
, INV_MEANING_SEL.C_MFG_LOOKUP(MSI.EAM_ACT_SHUTDOWN_STATUS
, 'BOM_EAM_SHUTDOWN_TYPE')
, MSI.EAM_ACT_NOTIFICATION_FLAG
, MSI.PURCHASING_ITEM_FLAG
, MSI.ALLOW_ITEM_DESC_UPDATE_FLAG
, MSI.PURCHASING_ENABLED_FLAG
, MSI.MUST_USE_APPROVED_VENDOR_FLAG
, MSI.UNIT_OF_ISSUE
, INV_MEANING_SEL.C_PO_HAZARD_CLASS(MSI.HAZARD_CLASS_ID)
, MSI.ROUNDING_FACTOR
, INV_MEANING_SEL.C_PER_PEOPLE(MSI.BUYER_ID)
, INV_MEANING_SEL.C_PO_UN_NUMB(MSI.UN_NUMBER_ID)
, FA.CONCATENATED_SEGMENTS
, MSI.OUTSIDE_OPERATION_FLAG
, INV_MEANING_SEL.C_PO_LOOKUP(MSI.OUTSIDE_OPERATION_UOM_TYPE
, 'OUTSIDE OPERATION UOM TYPE')
, MSI.RECEIPT_REQUIRED_FLAG
, MSI.INSPECTION_REQUIRED_FLAG
, ROUT.ROUTING_NAME
, SUB1.SUBINVENTORY_CODE
, INV_MEANING_SEL.C_PO_LOOKUP(MSI.ENFORCE_SHIP_TO_LOCATION_CODE
, 'RECEIVING CONTROL LEVEL')
, MSI.ALLOW_SUBSTITUTE_RECEIPTS_FLAG
, MSI.ALLOW_EXPRESS_DELIVERY_FLAG
, MSI.ALLOW_UNORDERED_RECEIPTS_FLAG
, INV_MEANING_SEL.C_PO_LOOKUP(MSI.QTY_RCV_EXCEPTION_CODE
, 'RECEIVING CONTROL LEVEL')
, MSI.QTY_RCV_TOLERANCE
, MSI.BUILD_IN_WIP_FLAG
, INV_MEANING_SEL.C_MFG_LOOKUP(MSI.WIP_SUPPLY_TYPE
, 'WIP_SUPPLY')
, MSI.WIP_SUPPLY_SUBINVENTORY
, WIP.CONCATENATED_SEGMENTS
, MSI.OVERCOMPLETION_TOLERANCE_TYPE
, MSI.OVERCOMPLETION_TOLERANCE_VALUE
, INV_MEANING_SEL.C_MFG_LOOKUP(MSI.PLANNING_MAKE_BUY_CODE
, 'MTL_PLANNING_MAKE_BUY')
, MSI.CUSTOMER_ORDER_FLAG
, MSI.INTERNAL_ORDER_FLAG
, MSI.SHIPPABLE_ITEM_FLAG
, MSI.PICK_COMPONENTS_FLAG
, MSI.SHIP_MODEL_COMPLETE_FLAG
, MSI.RETURNABLE_FLAG
, MSI.CUSTOMER_ORDER_ENABLED_FLAG
, MSI.INTERNAL_ORDER_ENABLED_FLAG
, MSI.SO_TRANSACTIONS_FLAG
, MSI.REPLENISH_TO_ORDER_FLAG
, INV_MEANING_SEL.C_MFG_LOOKUP(MSI.RETURN_INSPECTION_REQUIREMENT
, 'MTL_RETURN_INSPECTION')
, ORG1.NAME
, SUB2.SUBINVENTORY_CODE
, MSI.OVER_SHIPMENT_TOLERANCE
, MSI.OVER_RETURN_TOLERANCE
, MSI.UNDER_SHIPMENT_TOLERANCE
, MSI.UNDER_RETURN_TOLERANCE
, MSI.CONTRACT_ITEM_TYPE_CODE
, INV_MEANING_SEL.C_UNITMEASURE(MSI.SERVICE_DURATION_PERIOD_CODE)
, MSI.SERVICE_DURATION
, COV.NAME
, MSI.SUBSCRIPTION_DEPEND_FLAG
, MSI.SERV_IMPORTANCE_LEVEL
, MSI.SERVICEABLE_PRODUCT_FLAG
, MSI.DEFECT_TRACKING_ON_FLAG
, INV_MEANING_SEL.C_FND_LOOKUP_VL(MSI.RECOVERED_PART_DISP_CODE
, 'CSP_RECOVERED_PART_DISP_CODE')
, MSI.COMMS_NL_TRACKABLE_FLAG
, INV_MEANING_SEL.C_FND_LOOKUP_VL(MSI.ASSET_CREATION_CODE
, 'CUN_ASSET_CREATION_CODE')
, INV_MEANING_SEL.C_FND_LOOKUP_VL(MSI.IB_ITEM_INSTANCE_CLASS
, 'CSI_ITEM_CLASS')
, MSI.SERV_REQ_ENABLED_CODE
, MSI.COMMS_ACTIVATION_REQD_FLAG
, MSI.SERVICE_STARTING_DELAY
, MSI.ATTRIBUTE_CATEGORY
, 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.CHILD_LOT_FLAG
, MSI.CHILD_LOT_PREFIX
, MSI.CHILD_LOT_STARTING_NUMBER
, MSI.CHILD_LOT_VALIDATION_FLAG
, MSI.COPY_LOT_ATTRIBUTE_FLAG
, MSI.DEFAULT_GRADE
, MSI.EXPIRATION_ACTION_CODE
, MSI.EXPIRATION_ACTION_INTERVAL
, MSI.GRADE_CONTROL_FLAG
, MSI.HAZARDOUS_MATERIAL_FLAG
, MSI.HOLD_DAYS
, MSI.LOT_DIVISIBLE_FLAG
, MSI.MATURITY_DAYS
, MSI.PARENT_CHILD_GENERATION_FLAG
, MSI.PROCESS_COSTING_ENABLED_FLAG
, MSI.PROCESS_EXECUTION_ENABLED_FLAG
, MSI.PROCESS_QUALITY_ENABLED_FLAG
, GME.CONCATENATED_SEGMENTS
, MSI.PROCESS_SUPPLY_SUBINVENTORY
, GME1.CONCATENATED_SEGMENTS
, MSI.PROCESS_YIELD_SUBINVENTORY
, MSI.RECIPE_ENABLED_FLAG
, MSI.RETEST_INTERVAL
, MSI.CAS_NUMBER
FROM MTL_SYSTEM_ITEMS_B_KFV MSI
, MTL_PARAMETERS ORG3
, MTL_SYSTEM_ITEMS_TL MSTL
, MTL_ITEM_CATALOG_GROUPS_KFV CAT
, HR_ORGANIZATION_UNITS ORG1
, HR_ALL_ORGANIZATION_UNITS_TL ORG4
, RCV_ROUTING_HEADERS ROUT
, OKS_COVERAGE_TEMPLTS_V COV
, MTL_SYSTEM_ITEMS_B_KFV MSI2
, MTL_ITEM_SUB_DEFAULTS SUB1
, MTL_ITEM_SUB_DEFAULTS SUB2
, MTL_ITEM_SUB_DEFAULTS SUB3
, FA_CATEGORIES_B_KFV FA
, MTL_ITEM_LOCATIONS_KFV WIP
, MTL_ITEM_LOCATIONS_KFV GME
, MTL_ITEM_LOCATIONS_KFV GME1
WHERE ORG1.ORGANIZATION_ID (+) = MSI.DEFAULT_SHIPPING_ORG
AND ORG3.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND ORG4.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND ORG4.LANGUAGE = USERENV('LANG')
AND MSTL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSTL.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSTL.LANGUAGE = USERENV('LANG')
AND MSI.ITEM_CATALOG_GROUP_ID = CAT.ITEM_CATALOG_GROUP_ID (+)
AND ROUT.ROUTING_HEADER_ID (+) = MSI.RECEIVING_ROUTING_ID
AND COV.ID (+) = MSI.COVERAGE_SCHEDULE_ID
AND MSI2.INVENTORY_ITEM_ID (+) = MSI.BASE_ITEM_ID
AND MSI2.ORGANIZATION_ID (+) = MSI.ORGANIZATION_ID
AND SUB1.INVENTORY_ITEM_ID (+) = MSI.INVENTORY_ITEM_ID
AND SUB1.ORGANIZATION_ID (+) = MSI.ORGANIZATION_ID
AND SUB1.DEFAULT_TYPE (+) = 2
AND SUB2.INVENTORY_ITEM_ID (+) = MSI.INVENTORY_ITEM_ID
AND SUB2.ORGANIZATION_ID (+) = MSI.ORGANIZATION_ID
AND SUB2.DEFAULT_TYPE (+) = 1
AND SUB3.INVENTORY_ITEM_ID (+) = MSI.INVENTORY_ITEM_ID
AND SUB3.ORGANIZATION_ID (+) = MSI.ORGANIZATION_ID
AND SUB3.DEFAULT_TYPE (+) = 3
AND FA.CATEGORY_ID (+) = MSI.ASSET_CATEGORY_ID
AND WIP.ORGANIZATION_ID (+) = MSI.ORGANIZATION_ID
AND WIP.INVENTORY_LOCATION_ID (+) = MSI.WIP_SUPPLY_LOCATOR_ID
AND WIP.SUBINVENTORY_CODE (+) = MSI.WIP_SUPPLY_SUBINVENTORY
AND GME.ORGANIZATION_ID (+) = MSI.ORGANIZATION_ID
AND GME.INVENTORY_LOCATION_ID (+) = MSI.PROCESS_SUPPLY_LOCATOR_ID
AND GME.SUBINVENTORY_CODE (+) = MSI.PROCESS_SUPPLY_SUBINVENTORY
AND GME1.ORGANIZATION_ID (+) = MSI.ORGANIZATION_ID
AND GME1.INVENTORY_LOCATION_ID (+) = MSI.PROCESS_YIELD_LOCATOR_ID
AND GME1.SUBINVENTORY_CODE (+) = MSI.PROCESS_YIELD_SUBINVENTORY

Columns