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.
Want to give back to the society? (Do you have something which you think can be used by folks working on Oracle world wide?) Then Click here, share your email id and a brief description of the topic. And keep your photo and the content ready.
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports. 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.