MTL_SYSTEM_ITEMS_B: Table used by Oracle R12 for storing the Item information. Or in other words, it is R12 equivalent of EGP_SYSTEM_ITEMS_B table in Oracle Fusion. Slight difference is that MTL_SYSTEM_ITEMS_B table stores the item number in segment1 field. And since the item is defined at Inventory Org level, Combination of Inventory_item_Id and Organization_Id will give you a unique record. Oh wait, Organization_id can be driven from table MTL_PARAMETERS table based on the column Organization_Code.
Since table mtl_system_items_b contain the item number and not the item description, you’ll have to query the table mtl_system_items_tl using inventory_item_id, organization_id and the language to find out the description of the item in a particular language.
Again as we discussed, this is the master table to hold the item information, almost ( Playing safe here, in my exp it is 100% time) anywhere in the system item is referred, this is the table you need to query. Though sometimes column name might not be Inventory_item_id. Such as in PO_LINES_ALL table, name of the column is ITEM_ID. So just be careful while finding out the linking column.
Want the query to fetch the item details, please click here.
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
Here in this post, we will have a look at the table structure of MTL_SYSTEM_ITEMS_B table and what are the indexes available for you to use to query the table more efficiently.
MTL_SYSTEM_ITEMS_B Table Structure
| Column Name | Number | Nullable |
| INVENTORY_ITEM_ID | NUMBER | N |
| ORGANIZATION_ID | NUMBER | N |
| LAST_UPDATE_DATE | DATE | N |
| LAST_UPDATED_BY | NUMBER | N |
| CREATION_DATE | DATE | N |
| CREATED_BY | NUMBER | N |
| LAST_UPDATE_LOGIN | NUMBER | Y |
| SUMMARY_FLAG | VARCHAR2(1) | N |
| ENABLED_FLAG | VARCHAR2(1) | N |
| START_DATE_ACTIVE | DATE | Y |
| END_DATE_ACTIVE | DATE | Y |
| DESCRIPTION | VARCHAR2(240) | Y |
| BUYER_ID | NUMBER | Y |
| ACCOUNTING_RULE_ID | NUMBER | Y |
| INVOICING_RULE_ID | NUMBER | Y |
| SEGMENT1 | VARCHAR2(40) | Y |
| SEGMENT2 | VARCHAR2(40) | Y |
| SEGMENT3 | VARCHAR2(40) | Y |
| SEGMENT4 | VARCHAR2(40) | Y |
| SEGMENT5 | VARCHAR2(40) | Y |
| SEGMENT6 | VARCHAR2(40) | Y |
| SEGMENT7 | VARCHAR2(40) | Y |
| SEGMENT8 | VARCHAR2(40) | Y |
| SEGMENT9 | VARCHAR2(40) | Y |
| SEGMENT10 | VARCHAR2(40) | Y |
| SEGMENT11 | VARCHAR2(40) | Y |
| SEGMENT12 | VARCHAR2(40) | Y |
| SEGMENT13 | VARCHAR2(40) | Y |
| SEGMENT14 | VARCHAR2(40) | Y |
| SEGMENT15 | VARCHAR2(40) | Y |
| SEGMENT16 | VARCHAR2(40) | Y |
| SEGMENT17 | VARCHAR2(40) | Y |
| SEGMENT18 | VARCHAR2(40) | Y |
| SEGMENT19 | VARCHAR2(40) | Y |
| SEGMENT20 | VARCHAR2(40) | Y |
| ATTRIBUTE_CATEGORY | VARCHAR2(30) | Y |
| ATTRIBUTE1- ATTRIBUTE30 | VARCHAR2(240) | Y |
| PURCHASING_ITEM_FLAG | VARCHAR2(1) | N |
| SHIPPABLE_ITEM_FLAG | VARCHAR2(1) | N |
| CUSTOMER_ORDER_FLAG | VARCHAR2(1) | N |
| INTERNAL_ORDER_FLAG | VARCHAR2(1) | N |
| SERVICE_ITEM_FLAG | VARCHAR2(1) | N |
| INVENTORY_ITEM_FLAG | VARCHAR2(1) | N |
| ENG_ITEM_FLAG | VARCHAR2(1) | N |
| INVENTORY_ASSET_FLAG | VARCHAR2(1) | N |
| PURCHASING_ENABLED_FLAG | VARCHAR2(1) | N |
| CUSTOMER_ORDER_ENABLED_FLAG | VARCHAR2(1) | N |
| INTERNAL_ORDER_ENABLED_FLAG | VARCHAR2(1) | N |
| SO_TRANSACTIONS_FLAG | VARCHAR2(1) | N |
| MTL_TRANSACTIONS_ENABLED_FLAG | VARCHAR2(1) | N |
| STOCK_ENABLED_FLAG | VARCHAR2(1) | N |
| BOM_ENABLED_FLAG | VARCHAR2(1) | N |
| BUILD_IN_WIP_FLAG | VARCHAR2(1) | N |
| REVISION_QTY_CONTROL_CODE | NUMBER | Y |
| ITEM_CATALOG_GROUP_ID | NUMBER | Y |
| CATALOG_STATUS_FLAG | VARCHAR2(1) | Y |
| RETURNABLE_FLAG | VARCHAR2(1) | Y |
| DEFAULT_SHIPPING_ORG | NUMBER | Y |
| COLLATERAL_FLAG | VARCHAR2(1) | Y |
| TAXABLE_FLAG | VARCHAR2(1) | Y |
| QTY_RCV_EXCEPTION_CODE | VARCHAR2(25) | Y |
| ALLOW_ITEM_DESC_UPDATE_FLAG | VARCHAR2(1) | Y |
| INSPECTION_REQUIRED_FLAG | VARCHAR2(1) | Y |
| RECEIPT_REQUIRED_FLAG | VARCHAR2(1) | Y |
| MARKET_PRICE | NUMBER | Y |
| HAZARD_CLASS_ID | NUMBER | Y |
| RFQ_REQUIRED_FLAG | VARCHAR2(1) | Y |
| QTY_RCV_TOLERANCE | NUMBER | Y |
| LIST_PRICE_PER_UNIT | NUMBER | Y |
| UN_NUMBER_ID | NUMBER | Y |
| PRICE_TOLERANCE_PERCENT | NUMBER | Y |
| ASSET_CATEGORY_ID | NUMBER | Y |
| ROUNDING_FACTOR | NUMBER | Y |
| UNIT_OF_ISSUE | VARCHAR2(25) | Y |
| ENFORCE_SHIP_TO_LOCATION_CODE | VARCHAR2(25) | Y |
| ALLOW_SUBSTITUTE_RECEIPTS_FLAG | VARCHAR2(1) | Y |
| ALLOW_UNORDERED_RECEIPTS_FLAG | VARCHAR2(1) | Y |
| ALLOW_EXPRESS_DELIVERY_FLAG | VARCHAR2(1) | Y |
| DAYS_EARLY_RECEIPT_ALLOWED | NUMBER | Y |
| DAYS_LATE_RECEIPT_ALLOWED | NUMBER | Y |
| RECEIPT_DAYS_EXCEPTION_CODE | VARCHAR2(25) | Y |
| RECEIVING_ROUTING_ID | NUMBER | Y |
| INVOICE_CLOSE_TOLERANCE | NUMBER | Y |
| RECEIVE_CLOSE_TOLERANCE | NUMBER | Y |
| AUTO_LOT_ALPHA_PREFIX | VARCHAR2(30) | Y |
| START_AUTO_LOT_NUMBER | VARCHAR2(30) | Y |
| LOT_CONTROL_CODE | NUMBER | Y |
| SHELF_LIFE_CODE | NUMBER | Y |
| SHELF_LIFE_DAYS | NUMBER | Y |
| SERIAL_NUMBER_CONTROL_CODE | NUMBER | Y |
| START_AUTO_SERIAL_NUMBER | VARCHAR2(30) | Y |
| AUTO_SERIAL_ALPHA_PREFIX | VARCHAR2(30) | Y |
| SOURCE_TYPE | NUMBER | Y |
| SOURCE_ORGANIZATION_ID | NUMBER | Y |
| SOURCE_SUBINVENTORY | VARCHAR2(10) | Y |
| EXPENSE_ACCOUNT | NUMBER | Y |
| ENCUMBRANCE_ACCOUNT | NUMBER | Y |
| RESTRICT_SUBINVENTORIES_CODE | NUMBER | Y |
| UNIT_WEIGHT | NUMBER | Y |
| WEIGHT_UOM_CODE | VARCHAR2(3) | Y |
| VOLUME_UOM_CODE | VARCHAR2(3) | Y |
| UNIT_VOLUME | NUMBER | Y |
| RESTRICT_LOCATORS_CODE | NUMBER | Y |
| LOCATION_CONTROL_CODE | NUMBER | Y |
| SHRINKAGE_RATE | NUMBER | Y |
| ACCEPTABLE_EARLY_DAYS | NUMBER | Y |
| PLANNING_TIME_FENCE_CODE | NUMBER | Y |
| DEMAND_TIME_FENCE_CODE | NUMBER | Y |
| LEAD_TIME_LOT_SIZE | NUMBER | Y |
| STD_LOT_SIZE | NUMBER | Y |
| CUM_MANUFACTURING_LEAD_TIME | NUMBER | Y |
| OVERRUN_PERCENTAGE | NUMBER | Y |
| MRP_CALCULATE_ATP_FLAG | VARCHAR2(1) | Y |
| ACCEPTABLE_RATE_INCREASE | NUMBER | Y |
| ACCEPTABLE_RATE_DECREASE | NUMBER | Y |
| CUMULATIVE_TOTAL_LEAD_TIME | NUMBER | Y |
| PLANNING_TIME_FENCE_DAYS | NUMBER | Y |
| DEMAND_TIME_FENCE_DAYS | NUMBER | Y |
| END_ASSEMBLY_PEGGING_FLAG | VARCHAR2(1) | Y |
| REPETITIVE_PLANNING_FLAG | VARCHAR2(1) | Y |
| PLANNING_EXCEPTION_SET | VARCHAR2(10) | Y |
| BOM_ITEM_TYPE | NUMBER | N |
| PICK_COMPONENTS_FLAG | VARCHAR2(1) | N |
| REPLENISH_TO_ORDER_FLAG | VARCHAR2(1) | N |
| BASE_ITEM_ID | NUMBER | Y |
| ATP_COMPONENTS_FLAG | VARCHAR2(1) | N |
| ATP_FLAG | VARCHAR2(1) | N |
| FIXED_LEAD_TIME | NUMBER | Y |
| VARIABLE_LEAD_TIME | NUMBER | Y |
| WIP_SUPPLY_LOCATOR_ID | NUMBER | Y |
| WIP_SUPPLY_TYPE | NUMBER | Y |
| WIP_SUPPLY_SUBINVENTORY | VARCHAR2(10) | Y |
| PRIMARY_UOM_CODE | VARCHAR2(3) | Y |
| PRIMARY_UNIT_OF_MEASURE | VARCHAR2(25) | Y |
| ALLOWED_UNITS_LOOKUP_CODE | NUMBER | Y |
| COST_OF_SALES_ACCOUNT | NUMBER | Y |
| SALES_ACCOUNT | NUMBER | Y |
| DEFAULT_INCLUDE_IN_ROLLUP_FLAG | VARCHAR2(1) | Y |
| INVENTORY_ITEM_STATUS_CODE | VARCHAR2(10) | N |
| INVENTORY_PLANNING_CODE | NUMBER | Y |
| PLANNER_CODE | VARCHAR2(10) | Y |
| PLANNING_MAKE_BUY_CODE | NUMBER | Y |
| FIXED_LOT_MULTIPLIER | NUMBER | Y |
| ROUNDING_CONTROL_TYPE | NUMBER | Y |
| CARRYING_COST | NUMBER | Y |
| POSTPROCESSING_LEAD_TIME | NUMBER | Y |
| PREPROCESSING_LEAD_TIME | NUMBER | Y |
| FULL_LEAD_TIME | NUMBER | Y |
| ORDER_COST | NUMBER | Y |
| MRP_SAFETY_STOCK_PERCENT | NUMBER | Y |
| MRP_SAFETY_STOCK_CODE | NUMBER | Y |
| MIN_MINMAX_QUANTITY | NUMBER | Y |
| MAX_MINMAX_QUANTITY | NUMBER | Y |
| MINIMUM_ORDER_QUANTITY | NUMBER | Y |
| FIXED_ORDER_QUANTITY | NUMBER | Y |
| FIXED_DAYS_SUPPLY | NUMBER | Y |
| MAXIMUM_ORDER_QUANTITY | NUMBER | Y |
| ATP_RULE_ID | NUMBER | Y |
| PICKING_RULE_ID | NUMBER | Y |
| RESERVABLE_TYPE | NUMBER | Y |
| POSITIVE_MEASUREMENT_ERROR | NUMBER | Y |
| NEGATIVE_MEASUREMENT_ERROR | NUMBER | Y |
| ENGINEERING_ECN_CODE | VARCHAR2(50) | Y |
| ENGINEERING_ITEM_ID | NUMBER | Y |
| ENGINEERING_DATE | DATE | Y |
| SERVICE_STARTING_DELAY | NUMBER | Y |
| VENDOR_WARRANTY_FLAG | VARCHAR2(1) | N |
| SERVICEABLE_COMPONENT_FLAG | VARCHAR2(1) | Y |
| SERVICEABLE_PRODUCT_FLAG | VARCHAR2(1) | N |
| BASE_WARRANTY_SERVICE_ID | NUMBER | Y |
| PAYMENT_TERMS_ID | NUMBER | Y |
| PREVENTIVE_MAINTENANCE_FLAG | VARCHAR2(1) | Y |
| PRIMARY_SPECIALIST_ID | NUMBER | Y |
| SECONDARY_SPECIALIST_ID | NUMBER | Y |
| SERVICEABLE_ITEM_CLASS_ID | NUMBER | Y |
| TIME_BILLABLE_FLAG | VARCHAR2(1) | Y |
| MATERIAL_BILLABLE_FLAG | VARCHAR2(30) | Y |
| EXPENSE_BILLABLE_FLAG | VARCHAR2(1) | Y |
| PRORATE_SERVICE_FLAG | VARCHAR2(1) | Y |
| COVERAGE_SCHEDULE_ID | NUMBER | Y |
| SERVICE_DURATION_PERIOD_CODE | VARCHAR2(10) | Y |
| SERVICE_DURATION | NUMBER | Y |
| WARRANTY_VENDOR_ID | NUMBER | Y |
| MAX_WARRANTY_AMOUNT | NUMBER | Y |
| RESPONSE_TIME_PERIOD_CODE | VARCHAR2(30) | Y |
| RESPONSE_TIME_VALUE | NUMBER | Y |
| NEW_REVISION_CODE | VARCHAR2(30) | Y |
| INVOICEABLE_ITEM_FLAG | VARCHAR2(1) | N |
| TAX_CODE | VARCHAR2(50) | Y |
| INVOICE_ENABLED_FLAG | VARCHAR2(1) | N |
| MUST_USE_APPROVED_VENDOR_FLAG | VARCHAR2(1) | N |
| REQUEST_ID | NUMBER | Y |
| PROGRAM_APPLICATION_ID | NUMBER | Y |
| PROGRAM_ID | NUMBER | Y |
| PROGRAM_UPDATE_DATE | DATE | Y |
| OUTSIDE_OPERATION_FLAG | VARCHAR2(1) | N |
| OUTSIDE_OPERATION_UOM_TYPE | VARCHAR2(25) | Y |
| SAFETY_STOCK_BUCKET_DAYS | NUMBER | Y |
| AUTO_REDUCE_MPS | NUMBER | Y |
| COSTING_ENABLED_FLAG | VARCHAR2(1) | N |
| AUTO_CREATED_CONFIG_FLAG | VARCHAR2(1) | N |
| CYCLE_COUNT_ENABLED_FLAG | VARCHAR2(1) | N |
| ITEM_TYPE | VARCHAR2(30) | Y |
| MODEL_CONFIG_CLAUSE_NAME | VARCHAR2(10) | Y |
| SHIP_MODEL_COMPLETE_FLAG | VARCHAR2(1) | Y |
| MRP_PLANNING_CODE | NUMBER | Y |
| RETURN_INSPECTION_REQUIREMENT | NUMBER | Y |
| ATO_FORECAST_CONTROL | NUMBER | Y |
| RELEASE_TIME_FENCE_CODE | NUMBER | Y |
| RELEASE_TIME_FENCE_DAYS | NUMBER | Y |
| CONTAINER_ITEM_FLAG | VARCHAR2(1) | Y |
| VEHICLE_ITEM_FLAG | VARCHAR2(1) | Y |
| MAXIMUM_LOAD_WEIGHT | NUMBER | Y |
| MINIMUM_FILL_PERCENT | NUMBER | Y |
| CONTAINER_TYPE_CODE | VARCHAR2(30) | Y |
| INTERNAL_VOLUME | NUMBER | Y |
| WH_UPDATE_DATE | DATE | Y |
| PRODUCT_FAMILY_ITEM_ID | NUMBER | Y |
| GLOBAL_ATTRIBUTE_CATEGORY | VARCHAR2(150) | Y |
| GLOBAL_ATTRIBUTE1 – GLOBAL_ATTRIBUTE20 | VARCHAR2(150) | Y |
| PURCHASING_TAX_CODE | VARCHAR2(50) | Y |
| OVERCOMPLETION_TOLERANCE_TYPE | NUMBER | Y |
| OVERCOMPLETION_TOLERANCE_VALUE | NUMBER | Y |
| EFFECTIVITY_CONTROL | NUMBER | Y |
| CHECK_SHORTAGES_FLAG | VARCHAR2(1) | Y |
| OVER_SHIPMENT_TOLERANCE | NUMBER | Y |
| UNDER_SHIPMENT_TOLERANCE | NUMBER | Y |
| OVER_RETURN_TOLERANCE | NUMBER | Y |
| UNDER_RETURN_TOLERANCE | NUMBER | Y |
| EQUIPMENT_TYPE | NUMBER | Y |
| RECOVERED_PART_DISP_CODE | VARCHAR2(30) | Y |
| DEFECT_TRACKING_ON_FLAG | VARCHAR2(1) | Y |
| USAGE_ITEM_FLAG | VARCHAR2(1) | Y |
| EVENT_FLAG | VARCHAR2(1) | Y |
| ELECTRONIC_FLAG | VARCHAR2(1) | Y |
| DOWNLOADABLE_FLAG | VARCHAR2(1) | Y |
| VOL_DISCOUNT_EXEMPT_FLAG | VARCHAR2(1) | Y |
| COUPON_EXEMPT_FLAG | VARCHAR2(1) | Y |
| COMMS_NL_TRACKABLE_FLAG | VARCHAR2(1) | Y |
| ASSET_CREATION_CODE | VARCHAR2(30) | Y |
| COMMS_ACTIVATION_REQD_FLAG | VARCHAR2(1) | Y |
| ORDERABLE_ON_WEB_FLAG | VARCHAR2(1) | Y |
| BACK_ORDERABLE_FLAG | VARCHAR2(1) | Y |
| WEB_STATUS | VARCHAR2(30) | Y |
| INDIVISIBLE_FLAG | VARCHAR2(1) | Y |
| DIMENSION_UOM_CODE | VARCHAR2(3) | Y |
| UNIT_LENGTH | NUMBER | Y |
| UNIT_WIDTH | NUMBER | Y |
| UNIT_HEIGHT | NUMBER | Y |
| BULK_PICKED_FLAG | VARCHAR2(1) | Y |
| LOT_STATUS_ENABLED | VARCHAR2(1) | Y |
| DEFAULT_LOT_STATUS_ID | NUMBER | Y |
| SERIAL_STATUS_ENABLED | VARCHAR2(1) | Y |
| DEFAULT_SERIAL_STATUS_ID | NUMBER | Y |
| LOT_SPLIT_ENABLED | VARCHAR2(1) | Y |
| LOT_MERGE_ENABLED | VARCHAR2(1) | Y |
| INVENTORY_CARRY_PENALTY | NUMBER | Y |
| OPERATION_SLACK_PENALTY | NUMBER | Y |
| FINANCING_ALLOWED_FLAG | VARCHAR2(1) | Y |
| EAM_ITEM_TYPE | NUMBER | Y |
| EAM_ACTIVITY_TYPE_CODE | VARCHAR2(30) | Y |
| EAM_ACTIVITY_CAUSE_CODE | VARCHAR2(30) | Y |
| EAM_ACT_NOTIFICATION_FLAG | VARCHAR2(1) | Y |
| EAM_ACT_SHUTDOWN_STATUS | VARCHAR2(30) | Y |
| DUAL_UOM_CONTROL | NUMBER | Y |
| SECONDARY_UOM_CODE | VARCHAR2(3) | Y |
| DUAL_UOM_DEVIATION_HIGH | NUMBER | Y |
| DUAL_UOM_DEVIATION_LOW | NUMBER | Y |
| CONTRACT_ITEM_TYPE_CODE | VARCHAR2(30) | Y |
| SUBSCRIPTION_DEPEND_FLAG | VARCHAR2(1) | Y |
| SERV_REQ_ENABLED_CODE | VARCHAR2(30) | Y |
| SERV_BILLING_ENABLED_FLAG | VARCHAR2(1) | Y |
| SERV_IMPORTANCE_LEVEL | NUMBER | Y |
| PLANNED_INV_POINT_FLAG | VARCHAR2(1) | Y |
| LOT_TRANSLATE_ENABLED | VARCHAR2(1) | Y |
| DEFAULT_SO_SOURCE_TYPE | VARCHAR2(30) | Y |
| CREATE_SUPPLY_FLAG | VARCHAR2(1) | Y |
| SUBSTITUTION_WINDOW_CODE | NUMBER | Y |
| SUBSTITUTION_WINDOW_DAYS | NUMBER | Y |
| IB_ITEM_INSTANCE_CLASS | VARCHAR2(30) | Y |
| CONFIG_MODEL_TYPE | VARCHAR2(30) | Y |
| LOT_SUBSTITUTION_ENABLED | VARCHAR2(1) | Y |
| MINIMUM_LICENSE_QUANTITY | NUMBER | Y |
| EAM_ACTIVITY_SOURCE_CODE | VARCHAR2(30) | Y |
| LIFECYCLE_ID | NUMBER | Y |
| CURRENT_PHASE_ID | NUMBER | Y |
| OBJECT_VERSION_NUMBER | NUMBER | Y |
| TRACKING_QUANTITY_IND | VARCHAR2(30) | Y |
| ONT_PRICING_QTY_SOURCE | VARCHAR2(30) | Y |
| SECONDARY_DEFAULT_IND | VARCHAR2(30) | Y |
| OPTION_SPECIFIC_SOURCED | NUMBER | Y |
| APPROVAL_STATUS | VARCHAR2(30) | Y |
| VMI_MINIMUM_UNITS | NUMBER | Y |
| VMI_MINIMUM_DAYS | NUMBER | Y |
| VMI_MAXIMUM_UNITS | NUMBER | Y |
| VMI_MAXIMUM_DAYS | NUMBER | Y |
| VMI_FIXED_ORDER_QUANTITY | NUMBER | Y |
| SO_AUTHORIZATION_FLAG | NUMBER | Y |
| CONSIGNED_FLAG | NUMBER | Y |
| ASN_AUTOEXPIRE_FLAG | NUMBER | Y |
| VMI_FORECAST_TYPE | NUMBER | Y |
| FORECAST_HORIZON | NUMBER | Y |
| EXCLUDE_FROM_BUDGET_FLAG | NUMBER | Y |
| DAYS_TGT_INV_SUPPLY | NUMBER | Y |
| DAYS_TGT_INV_WINDOW | NUMBER | Y |
| DAYS_MAX_INV_SUPPLY | NUMBER | Y |
| DAYS_MAX_INV_WINDOW | NUMBER | Y |
| DRP_PLANNED_FLAG | NUMBER | Y |
| CRITICAL_COMPONENT_FLAG | NUMBER | Y |
| CONTINOUS_TRANSFER | NUMBER | Y |
| CONVERGENCE | NUMBER | Y |
| DIVERGENCE | NUMBER | Y |
| CONFIG_ORGS | VARCHAR2(30) | Y |
| CONFIG_MATCH | VARCHAR2(30) | Y |
| CAS_NUMBER | VARCHAR2(30) | Y |
| CHILD_LOT_FLAG | VARCHAR2(1) | Y |
| CHILD_LOT_PREFIX | VARCHAR2(30) | Y |
| CHILD_LOT_STARTING_NUMBER | NUMBER | Y |
| CHILD_LOT_VALIDATION_FLAG | VARCHAR2(1) | Y |
| COPY_LOT_ATTRIBUTE_FLAG | VARCHAR2(1) | Y |
| DEFAULT_GRADE | VARCHAR2(150) | Y |
| EXPIRATION_ACTION_CODE | VARCHAR2(32) | Y |
| EXPIRATION_ACTION_INTERVAL | NUMBER | Y |
| GRADE_CONTROL_FLAG | VARCHAR2(1) | Y |
| HAZARDOUS_MATERIAL_FLAG | VARCHAR2(1) | Y |
| HOLD_DAYS | NUMBER | Y |
| LOT_DIVISIBLE_FLAG | VARCHAR2(1) | Y |
| MATURITY_DAYS | NUMBER | Y |
| PARENT_CHILD_GENERATION_FLAG | VARCHAR2(1) | Y |
| PROCESS_COSTING_ENABLED_FLAG | VARCHAR2(1) | Y |
| PROCESS_EXECUTION_ENABLED_FLAG | VARCHAR2(1) | Y |
| PROCESS_QUALITY_ENABLED_FLAG | VARCHAR2(1) | Y |
| PROCESS_SUPPLY_LOCATOR_ID | NUMBER | Y |
| PROCESS_SUPPLY_SUBINVENTORY | VARCHAR2(10) | Y |
| PROCESS_YIELD_LOCATOR_ID | NUMBER | Y |
| PROCESS_YIELD_SUBINVENTORY | VARCHAR2(10) | Y |
| RECIPE_ENABLED_FLAG | VARCHAR2(1) | Y |
| RETEST_INTERVAL | NUMBER | Y |
| CHARGE_PERIODICITY_CODE | VARCHAR2(3) | Y |
| REPAIR_LEADTIME | NUMBER | Y |
| REPAIR_YIELD | NUMBER | Y |
| PREPOSITION_POINT | VARCHAR2(1) | Y |
| REPAIR_PROGRAM | NUMBER | Y |
| SUBCONTRACTING_COMPONENT | NUMBER | Y |
| OUTSOURCED_ASSEMBLY | NUMBER | Y |
| EGO_MASTER_ITEMS_DFF_CTX | VARCHAR2(1) | Y |
| GDSN_OUTBOUND_ENABLED_FLAG | VARCHAR2(1) | Y |
| TRADE_ITEM_DESCRIPTOR | VARCHAR2(35) | Y |
| STYLE_ITEM_ID | NUMBER | Y |
| STYLE_ITEM_FLAG | VARCHAR2(1) | Y |
| LAST_SUBMITTED_NIR_ID | NUMBER | Y |
| DEFAULT_MATERIAL_STATUS_ID | NUMBER | Y |
| GLOBAL_ATTRIBUTE11 | VARCHAR2(150) | Y |
| SERIAL_TAGGING_FLAG | VARCHAR2(1) | Y |
| IB_ITEM_TRACKING_LEVEL | VARCHAR2(30) | N |
| MCC_CLASSIFICATION_TYPE | VARCHAR2(30) | Y |
| MCC_CONTROL_CODE | NUMBER | Y |
| MCC_TRACKING_CODE | VARCHAR2(30) | Y |
Indexes
| Index Name | Index Type | Uniqueness? | Column Name |
| IDX$$_2BCF0006 | NORMAL | NONUNIQUE | INVENTORY_ITEM_ID |
| IDX$$_2BCF0006 | NORMAL | NONUNIQUE | SEGMENT1 |
| IDX$$_2BCF0006 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| MTL_SYSTEM_ITEMS_B_ECC1 | NORMAL | NONUNIQUE | LAST_UPDATE_DATE |
| MTL_SYSTEM_ITEMS_B_N10 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| MTL_SYSTEM_ITEMS_B_N10 | NORMAL | NONUNIQUE | PLANNER_CODE |
| MTL_SYSTEM_ITEMS_B_N11 | NORMAL | NONUNIQUE | WEB_STATUS |
| MTL_SYSTEM_ITEMS_B_N11 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| MTL_SYSTEM_ITEMS_B_N12 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| MTL_SYSTEM_ITEMS_B_N12 | NORMAL | NONUNIQUE | CUSTOMER_ORDER_ENABLED_FLAG |
| MTL_SYSTEM_ITEMS_B_N12 | NORMAL | NONUNIQUE | SERVICE_ITEM_FLAG |
| MTL_SYSTEM_ITEMS_B_N12 | NORMAL | NONUNIQUE | VENDOR_WARRANTY_FLAG |
| MTL_SYSTEM_ITEMS_B_N12 | NORMAL | NONUNIQUE | USAGE_ITEM_FLAG |
| MTL_SYSTEM_ITEMS_B_N13 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| MTL_SYSTEM_ITEMS_B_N13 | NORMAL | NONUNIQUE | WIP_SUPPLY_LOCATOR_ID |
| MTL_SYSTEM_ITEMS_B_N14 | NORMAL | NONUNIQUE | BASE_ITEM_ID |
| MTL_SYSTEM_ITEMS_B_N14 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| MTL_SYSTEM_ITEMS_B_N15 | NORMAL | NONUNIQUE | STYLE_ITEM_ID |
| MTL_SYSTEM_ITEMS_B_N15 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| MTL_SYSTEM_ITEMS_B_N16 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| MTL_SYSTEM_ITEMS_B_N16 | NORMAL | NONUNIQUE | EAM_ITEM_TYPE |
| MTL_SYSTEM_ITEMS_B_N16 | NORMAL | NONUNIQUE | SERIAL_NUMBER_CONTROL_CODE |
| MTL_SYSTEM_ITEMS_B_N17 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| MTL_SYSTEM_ITEMS_B_N17 | NORMAL | NONUNIQUE | SEGMENT1 |
| MTL_SYSTEM_ITEMS_B_N17 | NORMAL | NONUNIQUE | CONTAINER_ITEM_FLAG |
| MTL_SYSTEM_ITEMS_B_N2 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| MTL_SYSTEM_ITEMS_B_N2 | NORMAL | NONUNIQUE | DESCRIPTION |
| MTL_SYSTEM_ITEMS_B_N3 | NORMAL | NONUNIQUE | INVENTORY_ITEM_STATUS_CODE |
| MTL_SYSTEM_ITEMS_B_N4 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| MTL_SYSTEM_ITEMS_B_N4 | NORMAL | NONUNIQUE | AUTO_CREATED_CONFIG_FLAG |
| MTL_SYSTEM_ITEMS_B_N5 | NORMAL | NONUNIQUE | WH_UPDATE_DATE |
| MTL_SYSTEM_ITEMS_B_N6 | NORMAL | NONUNIQUE | ITEM_CATALOG_GROUP_ID |
| MTL_SYSTEM_ITEMS_B_N6 | NORMAL | NONUNIQUE | CATALOG_STATUS_FLAG |
| MTL_SYSTEM_ITEMS_B_N7 | NORMAL | NONUNIQUE | PRODUCT_FAMILY_ITEM_ID |
| MTL_SYSTEM_ITEMS_B_N7 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| MTL_SYSTEM_ITEMS_B_N8 | NORMAL | NONUNIQUE | COMMS_NL_TRACKABLE_FLAG |
| MTL_SYSTEM_ITEMS_B_N8 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| MTL_SYSTEM_ITEMS_B_N9 | NORMAL | NONUNIQUE | ORGANIZATION_ID |
| MTL_SYSTEM_ITEMS_B_N9 | NORMAL | NONUNIQUE | BUYER_ID |
| MTL_SYSTEM_ITEMS_B_U1 | NORMAL | UNIQUE | INVENTORY_ITEM_ID |
| MTL_SYSTEM_ITEMS_B_U1 | NORMAL | UNIQUE | ORGANIZATION_ID |
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com
Check out the Amazon Deals of the day based on your interests..
2 thoughts on “MTL_SYSTEM_ITEMS_B: Columns, Indexes & Few Tricks”
Comments are closed.