Bill Of Material - Routing query in oracle -R12
A routing defines the step-by-step operations you perform to manufacture a product. Each routing can have any number of operations. For each operation you specify a department that determines the resources you may use for that operation.
Here sharing query to get routing,item and its uom(unit of measure) information :
SELECT distinct bor.line_code,
mtlp.organization_code,
msi.segment1 parent_item_code,
bor.alternate_routing_designator Alternate_routing,
msi.description item_desc,
msi.primary_uom_code uom,
bos.operation_seq_num,
operation_code,
bos.operation_description,
dep.department_code,
borv.RESOURCE_SEQ_NUM,
borv.resource_code,
br.DESCRIPTION,
crc.resource_rate,
borv.usage_rate_or_amount,
borv.usage_rate_or_amount_inverse,
borv.ASSIGNED_UNITS
FROM apps.bom_operational_routings_v bor,
apps.bom_operation_sequences bos,
apps.mtl_system_items_b msi,
apps.mtl_parameters mtlp,
apps.bom_departments dep,
apps.bom_standard_operations bso,
apps.bom_operation_resources_v borv,
apps.bom_resources br,
apps.cst_resource_costs crc
WHERE bor.routing_sequence_id = bos.routing_sequence_id
AND msi.organization_id(+) = bor.organization_id
AND msi.inventory_item_id(+) = bor.assembly_item_id
AND msi.organization_id = mtlp.organization_id
AND bos.department_id = dep.department_id
AND bso.standard_operation_id(+) = bos.standard_operation_id
AND borv.operation_sequence_id(+) = bos.operation_sequence_id
AND crc.resource_id = borv.resource_id
AND br.resource_id = borv.resource_id
AND br.organization_id = crc.organization_id
and crc.COST_TYPE_ID = 1
AND bos.disable_date is null
AND bor.cfm_routing_flag != 1
and msi.organization_id=P_organization_id
