BOM Routing Query in Oracle Apps

Hi friends, we are going to discuss about the BOM routing query in oracle apps. We will share the detail sql query which helps to extract the complete BOM routing detail in oracle apps. We will also share the important BOM routing tables which helps to develop the BOM routing query. Using this sql query , we can develop the BOM routing report which helps to share the complete BOM routing details in oracle apps. Please find below the complete detail about BOM Routing query in oracle apps.

BOM Routing query in oracle apps

2 Important Tables used by BOM Routing query in oracle apps

1.BOM_OPERATIONAL_ROUTINGS_V

2.BOM_OPERATION_SEQUENCES_V 

Detail SQL Query to Extract the BOM Routing details in Oracle Apps

Here below is the detail sql query , which helps to extract the complete BOM routing details in oracle apps. We can use this query to develop 

select msb.segment1||'.'||msb.segment2||'.'||msb.segment3||'.'||msb.segment4||'.'||msb.segment5 assembly,msb.description,a1.completion_subinventory
,OPERATION_SEQ_NUM,

OPERATION_DESCRIPTION ,

RESOURCE_SEQ_NUM,RESOURCE_CODE,

BR.UOM RESOURCE_UOM,

USAGE_RATE_OR_AMOUNT,
DEPARTMENT_CODE,

decode(SCHEDULE_FLAG,1,'Y',2,'N') SCHDULE 

from BOM_OPERATIONAL_ROUTINGS_V  a1,

BOM_OPERATION_SEQUENCES_V  a2,

mtl_system_items_b msb,

BOM_OPERATION_resourceS_V br
where a1.ROUTING_SEQUENCE_ID=a2.ROUTING_SEQUENCE_ID
and a1.ASSEMBLY_ITEM_ID=msb.INVENTORY_ITEM_ID
and a1.organization_id=msb.organization_id

and a1.organization_id=85

and nvl(a2.disable_date,'02-APR-2020')='20-APR-2020'
and br.OPERATION_SEQUENCE_ID=a2.OPERATION_SEQUENCE_ID;

BOM Routing query in oracle apps

BOM Routing query in oracle apps