MTL_PARAMETERS can be compared with INV_ORG_PARAMETERS table in Oracle Fusion. Storing the data of Inventory orgs, this is one of the most used tables in Oracle R12 as anything and everything based on Inventory org has to go through this table such as querying the item number in the system which is defined at inventory org level.
Now to query this table, ORGANIZATION_CODE is your column. So find out the ORGANIZATION_ID value for the Inventory org you are interested in by querying this table based on the Organization_code and use it wherever applicable.
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...
MTL_PARAMETERS Table Structure
| Column Name | Number | Nullable |
| 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 |
| ORGANIZATION_CODE | VARCHAR2(3) | Y |
| MASTER_ORGANIZATION_ID | NUMBER | N |
| PRIMARY_COST_METHOD | NUMBER | N |
| COST_ORGANIZATION_ID | NUMBER | N |
| DEFAULT_MATERIAL_COST_ID | NUMBER | Y |
| CALENDAR_EXCEPTION_SET_ID | NUMBER | Y |
| CALENDAR_CODE | VARCHAR2(10) | Y |
| GENERAL_LEDGER_UPDATE_CODE | NUMBER | N |
| DEFAULT_ATP_RULE_ID | NUMBER | Y |
| DEFAULT_PICKING_RULE_ID | NUMBER | Y |
| DEFAULT_LOCATOR_ORDER_VALUE | NUMBER | Y |
| DEFAULT_SUBINV_ORDER_VALUE | NUMBER | Y |
| NEGATIVE_INV_RECEIPT_CODE | NUMBER | N |
| STOCK_LOCATOR_CONTROL_CODE | NUMBER | N |
| MATERIAL_ACCOUNT | NUMBER | Y |
| MATERIAL_OVERHEAD_ACCOUNT | NUMBER | Y |
| MATL_OVHD_ABSORPTION_ACCT | NUMBER | Y |
| RESOURCE_ACCOUNT | NUMBER | Y |
| PURCHASE_PRICE_VAR_ACCOUNT | NUMBER | Y |
| AP_ACCRUAL_ACCOUNT | NUMBER | Y |
| OVERHEAD_ACCOUNT | NUMBER | Y |
| OUTSIDE_PROCESSING_ACCOUNT | NUMBER | Y |
| INTRANSIT_INV_ACCOUNT | NUMBER | Y |
| INTERORG_RECEIVABLES_ACCOUNT | NUMBER | Y |
| INTERORG_PRICE_VAR_ACCOUNT | NUMBER | Y |
| INTERORG_PAYABLES_ACCOUNT | NUMBER | Y |
| COST_OF_SALES_ACCOUNT | NUMBER | Y |
| ENCUMBRANCE_ACCOUNT | NUMBER | Y |
| PROJECT_COST_ACCOUNT | NUMBER | Y |
| INTERORG_TRANSFER_CR_ACCOUNT | NUMBER | Y |
| MATL_INTERORG_TRANSFER_CODE | NUMBER | N |
| INTERORG_TRNSFR_CHARGE_PERCENT | NUMBER | Y |
| SOURCE_ORGANIZATION_ID | NUMBER | Y |
| SOURCE_SUBINVENTORY | VARCHAR2(10) | Y |
| SOURCE_TYPE | NUMBER | Y |
| ORG_MAX_WEIGHT | NUMBER | Y |
| ORG_MAX_WEIGHT_UOM_CODE | VARCHAR2(3) | Y |
| ORG_MAX_VOLUME | NUMBER | Y |
| ORG_MAX_VOLUME_UOM_CODE | VARCHAR2(3) | Y |
| SERIAL_NUMBER_TYPE | NUMBER | Y |
| AUTO_SERIAL_ALPHA_PREFIX | VARCHAR2(30) | Y |
| START_AUTO_SERIAL_NUMBER | VARCHAR2(30) | Y |
| AUTO_LOT_ALPHA_PREFIX | VARCHAR2(30) | Y |
| LOT_NUMBER_UNIQUENESS | NUMBER | N |
| LOT_NUMBER_GENERATION | NUMBER | N |
| LOT_NUMBER_ZERO_PADDING | NUMBER | Y |
| LOT_NUMBER_LENGTH | NUMBER | Y |
| STARTING_REVISION | VARCHAR2(3) | N |
| ATTRIBUTE_CATEGORY | VARCHAR2(30) | Y |
| ATTRIBUTE1 | VARCHAR2(150) | Y |
| ATTRIBUTE2 | VARCHAR2(150) | Y |
| ATTRIBUTE3 | VARCHAR2(150) | Y |
| ATTRIBUTE4 | VARCHAR2(150) | Y |
| ATTRIBUTE5 | VARCHAR2(150) | Y |
| ATTRIBUTE6 | VARCHAR2(150) | Y |
| ATTRIBUTE7 | VARCHAR2(150) | Y |
| ATTRIBUTE8 | VARCHAR2(150) | Y |
| ATTRIBUTE9 | VARCHAR2(150) | Y |
| ATTRIBUTE10 | VARCHAR2(150) | Y |
| ATTRIBUTE11 | VARCHAR2(150) | Y |
| ATTRIBUTE12 | VARCHAR2(150) | Y |
| ATTRIBUTE13 | VARCHAR2(150) | Y |
| ATTRIBUTE14 | VARCHAR2(150) | Y |
| ATTRIBUTE15 | VARCHAR2(150) | Y |
| DEFAULT_DEMAND_CLASS | VARCHAR2(30) | Y |
| ENCUMBRANCE_REVERSAL_FLAG | NUMBER | Y |
| MAINTAIN_FIFO_QTY_STACK_TYPE | NUMBER | Y |
| INVOICE_PRICE_VAR_ACCOUNT | NUMBER | Y |
| AVERAGE_COST_VAR_ACCOUNT | NUMBER | Y |
| SALES_ACCOUNT | NUMBER | Y |
| EXPENSE_ACCOUNT | NUMBER | Y |
| SERIAL_NUMBER_GENERATION | NUMBER | N |
| REQUEST_ID | NUMBER | Y |
| PROGRAM_APPLICATION_ID | NUMBER | Y |
| PROGRAM_ID | NUMBER | Y |
| PROGRAM_UPDATE_DATE | DATE | Y |
| GLOBAL_ATTRIBUTE_CATEGORY | VARCHAR2(30) | Y |
| GLOBAL_ATTRIBUTE1 | VARCHAR2(150) | Y |
| GLOBAL_ATTRIBUTE2 | VARCHAR2(150) | Y |
| GLOBAL_ATTRIBUTE3 | VARCHAR2(150) | Y |
| GLOBAL_ATTRIBUTE4 | VARCHAR2(150) | Y |
| GLOBAL_ATTRIBUTE5 | VARCHAR2(150) | Y |
| GLOBAL_ATTRIBUTE6 | VARCHAR2(150) | Y |
| GLOBAL_ATTRIBUTE7 | VARCHAR2(150) | Y |
| GLOBAL_ATTRIBUTE8 | VARCHAR2(150) | Y |
| GLOBAL_ATTRIBUTE9 | VARCHAR2(150) | Y |
| GLOBAL_ATTRIBUTE10 | VARCHAR2(150) | Y |
| GLOBAL_ATTRIBUTE11 | VARCHAR2(150) | Y |
| GLOBAL_ATTRIBUTE12 | VARCHAR2(150) | Y |
| GLOBAL_ATTRIBUTE13 | VARCHAR2(150) | Y |
| GLOBAL_ATTRIBUTE14 | VARCHAR2(150) | Y |
| GLOBAL_ATTRIBUTE15 | VARCHAR2(150) | Y |
| GLOBAL_ATTRIBUTE16 | VARCHAR2(150) | Y |
| GLOBAL_ATTRIBUTE17 | VARCHAR2(150) | Y |
| GLOBAL_ATTRIBUTE18 | VARCHAR2(150) | Y |
| GLOBAL_ATTRIBUTE19 | VARCHAR2(150) | Y |
| GLOBAL_ATTRIBUTE20 | VARCHAR2(150) | Y |
| MAT_OVHD_COST_TYPE_ID | NUMBER | Y |
| PROJECT_REFERENCE_ENABLED | NUMBER | Y |
| PM_COST_COLLECTION_ENABLED | NUMBER | Y |
| PROJECT_CONTROL_LEVEL | NUMBER | Y |
| AVG_RATES_COST_TYPE_ID | NUMBER | Y |
| TXN_APPROVAL_TIMEOUT_PERIOD | NUMBER | Y |
| MO_SOURCE_REQUIRED | NUMBER | Y |
| MO_PICK_CONFIRM_REQUIRED | NUMBER | Y |
| MO_APPROVAL_TIMEOUT_ACTION | NUMBER | Y |
| BORRPAY_MATL_VAR_ACCOUNT | NUMBER | Y |
| BORRPAY_MOH_VAR_ACCOUNT | NUMBER | Y |
| BORRPAY_RES_VAR_ACCOUNT | NUMBER | Y |
| BORRPAY_OSP_VAR_ACCOUNT | NUMBER | Y |
| BORRPAY_OVH_VAR_ACCOUNT | NUMBER | Y |
| PROCESS_ENABLED_FLAG | VARCHAR2(1) | N |
| PROCESS_ORGN_CODE | VARCHAR2(4) | Y |
| WSM_ENABLED_FLAG | VARCHAR2(1) | N |
| DEFAULT_COST_GROUP_ID | NUMBER | N |
| LPN_PREFIX | VARCHAR2(10) | Y |
| LPN_SUFFIX | VARCHAR2(10) | Y |
| LPN_STARTING_NUMBER | NUMBER | Y |
| WMS_ENABLED_FLAG | VARCHAR2(1) | N |
| PREGEN_PUTAWAY_TASKS_FLAG | NUMBER | Y |
| REGENERATION_INTERVAL | NUMBER | Y |
| TIMEZONE_ID | NUMBER | Y |
| MAX_PICKS_BATCH | NUMBER | Y |
| DEFAULT_WMS_PICKING_RULE_ID | NUMBER | Y |
| DEFAULT_PUT_AWAY_RULE_ID | NUMBER | Y |
| DEFAULT_TASK_ASSIGN_RULE_ID | NUMBER | Y |
| DEFAULT_LABEL_COMP_RULE_ID | NUMBER | Y |
| DEFAULT_CARTON_RULE_ID | NUMBER | Y |
| DEFAULT_CYC_COUNT_HEADER_ID | NUMBER | Y |
| CROSSDOCK_FLAG | NUMBER | Y |
| CARTONIZATION_FLAG | NUMBER | Y |
| COST_CUTOFF_DATE | DATE | Y |
| ENABLE_COSTING_BY_CATEGORY | VARCHAR2(1) | Y |
| COST_GROUP_ACCOUNTING | NUMBER | Y |
| ALLOCATE_SERIAL_FLAG | VARCHAR2(1) | Y |
| DEFAULT_PICK_TASK_TYPE_ID | NUMBER | Y |
| DEFAULT_CC_TASK_TYPE_ID | NUMBER | Y |
| DEFAULT_PUTAWAY_TASK_TYPE_ID | NUMBER | Y |
| DEFAULT_REPL_TASK_TYPE_ID | NUMBER | Y |
| EAM_ENABLED_FLAG | VARCHAR2(1) | Y |
| MAINT_ORGANIZATION_ID | NUMBER | Y |
| PRIORITIZE_WIP_JOBS | NUMBER | Y |
| DEFAULT_CROSSDOCK_SUBINVENTORY | VARCHAR2(10) | Y |
| SKIP_TASK_WAITING_MINUTES | NUMBER | Y |
| QA_SKIPPING_INSP_FLAG | VARCHAR2(1) | N |
| DEFAULT_CROSSDOCK_LOCATOR_ID | NUMBER | Y |
| DEFAULT_MOXFER_TASK_TYPE_ID | NUMBER | Y |
| DEFAULT_MOISSUE_TASK_TYPE_ID | NUMBER | Y |
| DEFAULT_MATL_OVHD_COST_ID | NUMBER | Y |
| DISTRIBUTED_ORGANIZATION_FLAG | VARCHAR2(1) | Y |
| CARRIER_MANIFESTING_FLAG | VARCHAR2(1) | Y |
| DISTRIBUTION_ACCOUNT_ID | NUMBER | Y |
| DIRECT_SHIPPING_ALLOWED | VARCHAR2(1) | Y |
| DEFAULT_PICK_OP_PLAN_ID | NUMBER | Y |
| MAX_CLUSTERS_ALLOWED | NUMBER | Y |
| CONSIGNED_FLAG | VARCHAR2(1) | Y |
| CARTONIZE_SALES_ORDERS | VARCHAR2(1) | Y |
| CARTONIZE_MANUFACTURING | VARCHAR2(1) | Y |
| DEFER_LOGICAL_TRANSACTIONS | NUMBER | Y |
| WIP_OVERPICK_ENABLED | VARCHAR2(1) | Y |
| OVPK_TRANSFER_ORDERS_ENABLED | VARCHAR2(1) | Y |
| TOTAL_LPN_LENGTH | NUMBER | Y |
| UCC_128_SUFFIX_FLAG | VARCHAR2(1) | Y |
| WCS_ENABLED | VARCHAR2(1) | Y |
| ALLOW_DIFFERENT_STATUS | NUMBER | Y |
| CHILD_LOT_ALPHA_PREFIX | VARCHAR2(30) | Y |
| CHILD_LOT_NUMBER_LENGTH | NUMBER | Y |
| CHILD_LOT_VALIDATION_FLAG | VARCHAR2(1) | Y |
| CHILD_LOT_ZERO_PADDING_FLAG | VARCHAR2(1) | Y |
| COPY_LOT_ATTRIBUTE_FLAG | VARCHAR2(1) | Y |
| CREATE_LOT_UOM_CONVERSION | NUMBER | Y |
| GENEALOGY_FORMULA_SECURITY | VARCHAR2(1) | Y |
| PARENT_CHILD_GENERATION_FLAG | VARCHAR2(1) | Y |
| RULES_OVERRIDE_LOT_RESERVATION | VARCHAR2(1) | Y |
| AUTO_DEL_ALLOC_FLAG | VARCHAR2(1) | Y |
| RFID_VERIF_PCNT_THRESHOLD | NUMBER | Y |
| YARD_MANAGEMENT_ENABLED_FLAG | VARCHAR2(1) | Y |
| TRADING_PARTNER_ORG_FLAG | VARCHAR2(1) | Y |
| DEFERRED_COGS_ACCOUNT | NUMBER | Y |
| DEFAULT_CROSSDOCK_CRITERIA_ID | NUMBER | Y |
| ENFORCE_LOCATOR_ALIS_UNQ_FLAG | VARCHAR2(1) | Y |
| EPC_GENERATION_ENABLED_FLAG | VARCHAR2(1) | Y |
| COMPANY_PREFIX | VARCHAR2(30) | Y |
| COMPANY_PREFIX_INDEX | VARCHAR2(30) | Y |
| COMMERCIAL_GOVT_ENTITY_NUMBER | VARCHAR2(30) | Y |
| LABOR_MANAGEMENT_ENABLED_FLAG | VARCHAR2(1) | Y |
| DEFAULT_STATUS_ID | NUMBER | Y |
| LCM_ENABLED_FLAG | VARCHAR2(1) | Y |
| LCM_VAR_ACCOUNT | NUMBER | Y |
| OPSM_ENABLED_FLAG | VARCHAR2(1) | Y |
| ALLOCATE_LOT_FLAG | VARCHAR2(1) | Y |
| CAT_WT_ACCOUNT | NUMBER | Y |
| YARD_ORG_ID | NUMBER | Y |
| YMS_COSTING_ENABLED | VARCHAR2(1) | Y |
| YMS_DEFAULT_SUBINV | VARCHAR2(10) | Y |
| YMS_DEFAULT_LOCATOR | NUMBER | Y |
| DAILY_CAL_START_TIME | NUMBER | Y |
| DAILY_CAL_END_TIME | NUMBER | Y |
| TRADING_PARTNER_ORG_TYPE | NUMBER | Y |
| DISPATCH_INBOUND_TASK | VARCHAR2(1) | Y |
| ENABLE_EKANBAN | NUMBER | Y |
| PICK_VERIFY_SO_ENABLED | VARCHAR2(1) | Y |
| PICK_VERIFY_MO_ENABLED | VARCHAR2(1) | Y |
| PICK_VERIFY_CATEGORY_SET_ID | NUMBER | Y |
| PICK_VERIFY_WT_TOLERANCE_POS | NUMBER | Y |
| PICK_VERIFY_WT_TOLERANCE_NEG | NUMBER | Y |
| CC_WORKFLOW_ENABLED_FLAG | VARCHAR2(1) | Y |
| CC_APPROVAL_TIMEOUT_PERIOD | NUMBER | Y |
| CC_APPROVAL_TIMEOUT_ACTION | NUMBER | Y |
Indexes
| Index Name | Index Type | Uniqueness? | Column Name |
| IDX$$_2BCF0002 | NORMAL | NONUNIQUE | ORGANIZATION_CODE |
| MTL_PARAMETERS_N1 | NORMAL | NONUNIQUE | MASTER_ORGANIZATION_ID |
| MTL_PARAMETERS_N2 | NORMAL | NONUNIQUE | COST_ORGANIZATION_ID |
| MTL_PARAMETERS_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_PARAMETERS: Columns, Indexes & Few Tips”
Comments are closed.