SQL Query to Extract the BOM Routing Details in Oracle Fusion

SQL Query to Extract the BOM Routing Details in Oracle Fusion

In this post , We will be discuss about SQL Query to Extract the BOM Routing Details in Oracle Fusion.We will share the detail sql query which helps to extract all the informations about BOM and Routing Details in oracle fusion. We can extract the BOM components details and the routing details using these Oracle fusion bom routing tables. Please find below the complete details about Oracle Fusion bom routing tables.

SQL Query to Extract the BOM Routing Details in Oracle Fusion

5 BOM Routing Tables used by SQL Query to Extract the BOM Routing Details

1.WIS_WORK_DEFINITIONS_V
2.WIS_WD_OPERATIONS_V
3.WIS_WD_OPERATION_RESOURCES_V
4.INV_ORGANIZATION_DEFINITIONS_V
5.WIS_WD_OPERATION_MATERIALS_V

SQL Query to Extract the BOM Routing Details in Oracle Fusion

SELECT  IOD.BUSINESS_UNIT_NAME,
        IOD.ORGANIZATION_CODE,
        IOD.ORGANIZATION_NAME,
        WWD.WORK_DEFINITION_TYPE,
        WWD.WORK_DEFINITION_CODE,
        WWD.WORK_DEF_NAME,
        WWD.WORK_DEF_NAME_DESCRIPTION,
        WWD.WORK_METHOD_CODE,
        WWD.WORK_METHOD_NAME,
        WWD.STRUCTURE_TYPE_NAME,
        WWD.STRUCTURE_DISPLAY_NAME,
        WWD.BILL_SEQUENCE_ID,
        (SELECT ITEM_NUMBER FROM EGP_SYSTEM_ITEMS_B WHERE ORGANIZATION_ID = WWD.ORGANIZATION_ID AND INVENTORY_ITEM_ID = WWD.INVENTORY_ITEM_ID) ASS_ITEM_CODE,
        WWD.UOM_CODE ASS_UOM_CODE,
        WWO.OPERATION_TYPE,
        WWO.WD_OPERATION_ID,
        WWO.OPERATION_SEQ_NUMBER,
        WWO.OPERATION_NAME,
        WWO.OPERATION_DESCRIPTION,
        WWO.WORK_CENTER_CODE,
        WWO.WORK_CENTER_NAME,
        WWO.WORK_CENTER_DESCRIPTION,
        (SELECT ITEM_NUMBER FROM EGP_SYSTEM_ITEMS_B WHERE ORGANIZATION_ID = WOM.ORGANIZATION_ID AND INVENTORY_ITEM_ID = WOM.INVENTORY_ITEM_ID) COMP_ITEM_CODE,
        WOM.UOM_CODE COMP_UOM_CODE,
        WOM.YIELD_FACTOR,
        WOM.SUPPLY_SUBINVENTORY_NAME,
        WOM.COMPONENT_QUANTITY,
        WOR.RESOURCE_SEQ_NUMBER,
        WOR.RESOURCE_TYPE,
        WOR.RESOURCE_CODE,
        WOR.RESOURCE_NAME
FROM WIS_WORK_DEFINITIONS_V WWD,
     WIS_WD_OPERATIONS_V WWO,
     WIS_WD_OPERATION_RESOURCES_V WOR,
     INV_ORGANIZATION_DEFINITIONS_V IOD,
     WIS_WD_OPERATION_MATERIALS_V WOM
WHERE IOD.BUSINESS_UNIT_NAME =:P_BU
AND IOD.ORGANIZATION_CODE=:P_INV_ORG
AND WWD.INVENTORY_ITEM_ID=:P_ITEM_ID
AND WWD.ORGANIZATION_ID = IOD.ORGANIZATION_ID
AND WWD.ORGANIZATION_ID = WWO.ORGANIZATION_ID(+)
AND WWD.WORK_DEFINITION_ID = WWO.WORK_DEFINITION_ID
AND WWO.WORK_CENTER_ID = WOR.WORK_CENTER_ID
AND WWO.WD_OPERATION_ID = WOR.WD_OPERATION_ID
AND WWO.EFFECTIVE_TO_DATE IS NULL
AND WWD.ORGANIZATION_ID = WOM.ORGANIZATION_ID
AND WWO.WORK_DEFINITION_ID = WOM.WORK_DEFINITION_ID
AND WWO.WD_OPERATION_ID = WOM.WD_OPERATION_ID
AND WOM.DISABLE_DATE IS NULL
GROUP BY IOD.ORGANIZATION_CODE,
        IOD.ORGANIZATION_NAME,
        WWD.WORK_DEFINITION_TYPE,
        WWD.WORK_DEFINITION_CODE,
        WWD.WORK_DEF_NAME,
        WWD.WORK_DEF_NAME_DESCRIPTION,
        WWD.WORK_METHOD_CODE,
        WWD.WORK_METHOD_NAME,
        WWD.STRUCTURE_TYPE_NAME,
        WWD.STRUCTURE_DISPLAY_NAME,
        WWD.BILL_SEQUENCE_ID,
        WWD.ORGANIZATION_ID,
        WWD.INVENTORY_ITEM_ID,
        WWD.UOM_CODE,
        WWO.OPERATION_TYPE,
        WWO.WD_OPERATION_ID,
        WWO.OPERATION_SEQ_NUMBER,
        WWO.OPERATION_NAME,
        WWO.OPERATION_DESCRIPTION,
        WWO.WORK_CENTER_CODE,
        WWO.WORK_CENTER_NAME,
        WWO.WORK_CENTER_DESCRIPTION,
        WOM.ORGANIZATION_ID,
        WOM.INVENTORY_ITEM_ID,
        WOM.UOM_CODE,
        WOM.YIELD_FACTOR,
        WOM.SUPPLY_SUBINVENTORY_NAME,
        WOM.COMPONENT_QUANTITY,
        WOR.RESOURCE_SEQ_NUMBER,
        WOR.RESOURCE_TYPE,
        WOR.RESOURCE_CODE,
        WOR.RESOURCE_NAME,
        IOD.BUSINESS_UNIT_NAME
ORDER BY BILL_SEQUENCE_ID, OPERATION_SEQ_NUMBER, RESOURCE_SEQ_NUMBER

SQL Query to Extract the BOM Routing Details in Oracle Fusion