BOM Routings – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

Master data report showing bill of material routings with item codes and sequences.

The report optionally shows the full routing detail matching the BOM Routing Upload: routing header attributes and revision, operation scheduling details, operation resources, substitute resources and flow routing operation networks. The detail levels are controlled by the Show Operations, Show Resources, Show Substitute Resources and Show Networks parameters, and the Record Type column identifies each row.

Descriptive flexfield attributes of the routing, routing revision and operation are shown as dynamic columns via the Show BOM DFF Attributes parameter.

Report Parameters

Organization Code, Item, Item Description, Excluded Item Statuses, Assembly Type, Routing Type, Alternate, Display, Effective Date, Implemented Only, Common Routing Assignments, Show Operations, Show Resources, Show Substitute Resources, Routings with Substitutes Only, Show Networks, Routings with Networks Only, Show BOM DFF Attributes

Oracle EBS Tables Used

mtl_item_status_vl, mtl_item_locations_kfv, mtl_system_items_vl, bom_department_resources, bom_setup_types, cst_activities_val_v, hr_all_organization_units_vl, mtl_parameters, bom_operational_routings, mtl_rtg_item_revisions, wip_lines, bom_operation_sequences, bom_departments, bom_standard_operations, bom_operation_resources, bom_resources, bom_sub_operation_resources, bom_operation_networks

Report Categories

Enginatics

Running This SQL Without Blitz Report

Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.

Download & Import Options

Resource	Link
Excel Example Output	BOM Routings 02-Oct-2020 090608.xlsx
Blitz Report™ XML Import	BOM_Routings.xml
Full SQL on Enginatics	www.enginatics.com/reports/bom-routings/

BOM Routings Report

Executive Summary

The BOM Routings report provides a detailed overview of the manufacturing routings for assemblies and subassemblies. This report is an essential tool for production planners, manufacturing engineers, and cost accountants, offering a clear view of the operations, resources, and lead times required to produce an item. By providing a comprehensive view of routings, the report helps to ensure that production is planned and executed efficiently, and that costs are accurately calculated.

Business Challenge

Manufacturing routings are the backbone of the production process. However, managing and maintaining accurate routing information can be a complex and challenging task. Without a clear and comprehensive report, organizations may face:

Inaccurate Production Schedules: If routings are not accurate, production schedules may be unrealistic, leading to delays and disruptions in the manufacturing process.
Incorrect Costing: Inaccurate routings can lead to incorrect product costing, which can have a significant impact on profitability.
Inefficient Resource Utilization: Without a clear understanding of the resources required for each operation, it is difficult to optimize resource utilization and minimize production costs.
Lack of Visibility: Difficulty in getting a clear and up-to-date view of all routings, which can make it difficult to identify and address inefficiencies in the manufacturing process.

The Solution

The BOM Routings report provides a clear and detailed view of all manufacturing routings, helping organizations to:

Improve Production Planning: By providing a clear and accurate view of the operations, resources, and lead times required for each item, the report helps to ensure that production schedules are accurate and achievable.
Enhance Costing Accuracy: The report provides the basis for accurate product costing, which is essential for making informed decisions about pricing, profitability, and product mix.
Optimize Resource Utilization: By providing a detailed breakdown of the resources required for each operation, the report helps to identify opportunities to improve resource utilization and reduce production costs.
Increase Visibility: The report provides a centralized and easy-to-read view of all routings, making it easier to identify and address inefficiencies in the manufacturing process.

Technical Architecture (High Level)

The report is based on a query of several key tables in the Oracle Bills of Material and Work in Process modules. The primary tables used include:

bom_operational_routings: This table stores the main information about each routing, including the assembly item, the routing sequence, and the completion subinventory.
bom_operation_sequences: This table stores the sequence of operations for each routing.
bom_standard_operations: This table provides information about the standard operations that are used in the routings.
bom_departments: This table contains information about the departments where the operations are performed.

Parameters & Filtering

The report includes four parameters that allow you to filter the output by organization, item, item description, and item status.

Organization Code: This parameter allows you to filter the report by a specific organization.
Item: This parameter allows you to select a specific item to view.
Item Description: This parameter allows you to filter the report by the description of the item.
Excluded Item Statuses: This parameter allows you to exclude items with a specific status from the report.

Performance & Optimization

The BOM Routings report is designed to be efficient and fast. It uses direct table access to retrieve the data, which is much faster than relying on intermediate views or APIs. The report is also designed to minimize the use of complex joins and subqueries, which helps to ensure that it runs quickly and efficiently.

FAQ

Q: What is a routing? A: A routing is a sequence of operations that are performed to manufacture a product. It specifies the resources, materials, and time required for each operation.

Q: Why is it important to have accurate routings? A: Accurate routings are essential for ensuring the accuracy of your production schedules and product costs. They can also help you to optimize resource utilization and improve the overall efficiency of your manufacturing operations.

Q: Can I use this report to see the resources that are required for each operation? A: Yes, the report provides a detailed breakdown of each operation, including the resources that are required and the usage rate for each resource.

BOM Routings | Oracle EBS SQL Report

Master data report showing bill of material routings with item codes and sequences. The report optionally shows the full routing detail matching the BOM…