BOM components query in oracle Fusion

BOM components query in oracle Fusion

In this post , We will be discuss about BOM components query in oracle Fusion.  This Query will help to extract the BOM components details in oracle fusion. We can able to extract the complete BOM details in oracle fusion using this sql query. This is one of the important sql query to extract the BOM related details in oracle fusion. Here below , We are sharing the detail BOM components query in oracle Fusion.

BOM components query in oracle Fusion

5 Important Tables used by BOM components query in oracle Fusion

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 BOM components 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

BOM components query in oracle Fusion