Listing a BOM within a BOM

Here is the view in Oracle... Because we have so many parts we ended up doing a materialized view that we update with a process in Compiere everytime we change a BOM.  We did that so we could report off of this view faster.

I am sure someone out here could write a much better query than this but here is what I did.  Basically, created a view on top of itself so that the parent records would null.  Then used the connect by clause to generate the hierarchy.  You will also notice the use of connect_by_root = Top level of bill.  Connect_by_ISLEAF = bottom level of bill.  Level = what level of the hierarchy.

CREATE OR REPLACE VIEW BOM_INDENTED
(TOPLEVELID, TOPLEVEL, TOPLEVEL_DESC, PRODUCTNUM, PRODDEC,
M_PRODUCTBOM_ID, BOM_LEVEL, BOMQTY, DESCRIPTION, AD_CLIENT_ID,
AD_ORG_ID, ROWNUMBER, BOM_LEVEL_NUM, BOTTOMCOMPONENT)
AS

SELECT   CONNECT_BY_ROOT M_ProductBom_ID AS TOPLEVELID,CONNECT_BY_ROOT Value AS TOPLEVEL,CONNECT_BY_ROOT b.DESCRIPTION AS TOPLEVEL_DESC, Value AS ProductNum, b.DESCRIPTION AS ProdDesc, M_PRODUCTBOM_ID,LPAD(' ',2*(LEVEL-1)) || TO_CHAR(LEVEL) BOM_Level, BOMQTY, a.DESCRIPTION,a.AD_CLIENT_ID, a.AD_ORG_ID, ROWNUM, LEVEL, Connect_By_ISLEAF
  FROM
 
-- part 2 subquerry combining top level and bom to create null to start from
( SELECT "M_PRODUCT_BOM_ID","AD_CLIENT_ID","AD_ORG_ID","ISACTIVE",--"CREATED","CREATEDBY","UPDATED","UPDATEDBY",  (remove becuase unnnecessarry)
"LINE","M_PRODUCT_ID","M_PRODUCTBOM_ID","BOMQTY","DESCRIPTION","BOMTYPE"
FROM M_PRODUCT_BOM

UNION
-- part 1 the tope level querry creating the nulls that is combined in part 2
SELECT M_PRODUCT_BOM_1.M_PRODUCT_BOM_ID, M_PRODUCT_BOM.AD_CLIENT_ID, M_PRODUCT_BOM.AD_ORG_ID, M_PRODUCT_BOM_1.ISACTIVE,-- M_PRODUCT_BOM_1.CREATED, M_PRODUCT_BOM_1.CREATEDBY, M_PRODUCT_BOM_1.UPDATED, M_PRODUCT_BOM_1.UPDATEDBY, (removed unnecessary)
M_PRODUCT_BOM_1.LINE,M_PRODUCT_BOM_1.M_PRODUCTBOM_ID AS M_PRODUCT_ID, M_PRODUCT_BOM.M_PRODUCT_ID AS M_PRODUCTBOM_ID,  M_PRODUCT_BOM_1.BOMQTY, M_PRODUCT_BOM_1.DESCRIPTION, M_PRODUCT_BOM_1.BOMTYPE
FROM M_PRODUCT_BOM LEFT JOIN M_PRODUCT_BOM  M_PRODUCT_BOM_1 ON M_PRODUCT_BOM.M_PRODUCT_ID = M_PRODUCT_BOM_1.M_PRODUCTBOM_ID
GROUP BY M_PRODUCT_BOM_1.M_PRODUCT_BOM_ID, M_PRODUCT_BOM.AD_CLIENT_ID, M_PRODUCT_BOM.AD_ORG_ID, M_PRODUCT_BOM_1.ISACTIVE, M_PRODUCT_BOM_1.CREATED, M_PRODUCT_BOM_1.CREATEDBY, M_PRODUCT_BOM_1.UPDATED, M_PRODUCT_BOM_1.UPDATEDBY, M_PRODUCT_BOM_1.LINE, M_PRODUCT_BOM.M_PRODUCT_ID, M_PRODUCT_BOM_1.M_PRODUCTBOM_ID, M_PRODUCT_BOM_1.BOMQTY, M_PRODUCT_BOM_1.DESCRIPTION, M_PRODUCT_BOM_1.BOMTYPE
HAVING (((M_PRODUCT_BOM_1.M_PRODUCTBOM_ID) IS NULL))) a
-- part 3 continued
   INNER JOIN M_PRODUCT b
   ON a.M_PRODUCTBOM_ID = b.M_PRODUCT_ID
  START WITH a.M_PRODUCT_ID IS NULL
  CONNECT BY PRIOR  M_PRODUCTBOM_ID = a.M_PRODUCT_ID
  ORDER SIBLINGS BY b.Value
/

If you want to use materialized views... we did it the following way (I really know very little about them, so if someone has a better idea of how to use them for this, it would be appreciated)
The materialized view would be

CREATE MATERIALIZED VIEW BOM_INDENTED_MV
(PTX_Path,TOPLEVELID, TOPLEVEL, TOPLEVEL_DESC, PRODUCTNUM, PRODDEC,
M_PRODUCTBOM_ID, BOM_LEVEL, BOMQTY, DESCRIPTION, AD_CLIENT_ID,
AD_ORG_ID, BOM_LEVEL_NUM, BOTTOMCOMPONENT,TOPLEVEL_ISACTIVE, COMP_ISACTIVE, TOP_Client)

Build IMMEDIATE
REFRESH ON DEMAND
DISABLE QUERY REWRITE

AS
SELECT Path, TOPLEVELID,TOPLEVEL,TOPLEVEL_DESC, ProductNum, ProdDesc, M_PRODUCTBOM_ID, BOM_Level, BOMQTY, f1.DESCRIPTION,mp2.AD_CLIENT_ID, mp2.AD_ORG_ID, BomLevelNum, BottomComponent, MP.ISACTIVE AS TOPLEV_ISACTIVE, MP2.ISACTIVE AS COMP_ISACTIVE, mp.AD_CLIENT_ID AS TOPLEVEL_Client
  FROM (
SELECT DISTINCT SYS_CONNECT_BY_PATH (M_PRODUCTBOM_ID,'/') AS Path, CONNECT_BY_ROOT M_ProductBom_ID AS TOPLEVELID,CONNECT_BY_ROOT Value AS TOPLEVEL,CONNECT_BY_ROOT b.DESCRIPTION AS TOPLEVEL_DESC, Value AS ProductNum, b.DESCRIPTION AS ProdDesc, M_PRODUCTBOM_ID,LPAD(' ',2*(LEVEL-1)) || TO_CHAR(LEVEL) BOM_Level, BOMQTY, a.DESCRIPTION,a.AD_CLIENT_ID, a.AD_ORG_ID, LEVEL AS BomLevelNum, Connect_By_ISLEAF AS BottomComponent
  FROM
 
-- part 2 subquerry combining top level and bom to create null to start from
( SELECT "M_PRODUCT_BOM_ID","AD_CLIENT_ID","AD_ORG_ID","ISACTIVE",--"CREATED","CREATEDBY","UPDATED","UPDATEDBY",  (remove becuase unnnecessarry)
"LINE","M_PRODUCT_ID","M_PRODUCTBOM_ID","BOMQTY","DESCRIPTION","BOMTYPE"
FROM M_PRODUCT_BOM

UNION
-- part 1 the tope level querry creating the nulls that is combined in part 2
SELECT M_PRODUCT_BOM_1.M_PRODUCT_BOM_ID, M_PRODUCT_BOM.AD_CLIENT_ID, M_PRODUCT_BOM.AD_ORG_ID, M_PRODUCT_BOM_1.ISACTIVE,-- M_PRODUCT_BOM_1.CREATED, M_PRODUCT_BOM_1.CREATEDBY, M_PRODUCT_BOM_1.UPDATED, M_PRODUCT_BOM_1.UPDATEDBY, (removed unnecessary)
M_PRODUCT_BOM_1.LINE,M_PRODUCT_BOM_1.M_PRODUCTBOM_ID AS M_PRODUCT_ID, M_PRODUCT_BOM.M_PRODUCT_ID AS M_PRODUCTBOM_ID,  M_PRODUCT_BOM_1.BOMQTY, M_PRODUCT_BOM_1.DESCRIPTION, M_PRODUCT_BOM_1.BOMTYPE
FROM M_PRODUCT_BOM LEFT JOIN M_PRODUCT_BOM  M_PRODUCT_BOM_1 ON M_PRODUCT_BOM.M_PRODUCT_ID = M_PRODUCT_BOM_1.M_PRODUCTBOM_ID
GROUP BY M_PRODUCT_BOM_1.M_PRODUCT_BOM_ID, M_PRODUCT_BOM.AD_CLIENT_ID, M_PRODUCT_BOM.AD_ORG_ID, M_PRODUCT_BOM_1.ISACTIVE, M_PRODUCT_BOM_1.CREATED, M_PRODUCT_BOM_1.CREATEDBY, M_PRODUCT_BOM_1.UPDATED, M_PRODUCT_BOM_1.UPDATEDBY, M_PRODUCT_BOM_1.LINE, M_PRODUCT_BOM.M_PRODUCT_ID, M_PRODUCT_BOM_1.M_PRODUCTBOM_ID, M_PRODUCT_BOM_1.BOMQTY, M_PRODUCT_BOM_1.DESCRIPTION, M_PRODUCT_BOM_1.BOMTYPE
HAVING (((M_PRODUCT_BOM_1.M_PRODUCTBOM_ID) IS NULL))) a
-- part 3 continued
   INNER JOIN M_PRODUCT b
   ON a.M_PRODUCTBOM_ID = b.M_PRODUCT_ID
  START WITH a.M_PRODUCT_ID IS NULL
  CONNECT BY PRIOR  M_PRODUCTBOM_ID = a.M_PRODUCT_ID
  ORDER SIBLINGS BY b.Value
) f1
INNER JOIN M_PRODUCT mp ON TOPLEVELID=mp.M_PRODUCT_ID
INNER JOIN M_PRODUCT mp2 ON M_PRODUCTBOM_ID = mp2.M_PRODUCT_ID
ORDER BY Path
/

Then to refresh the materialize view we used the following database procedure where we used a parameter in the "report process" window to identify which MV to refresh:

CREATE OR REPLACE PROCEDURE Indented_Bom_Mv_Refresh
(
    PInstance_ID            IN NUMBER
       
)
AS
--    Logistice
    ResultStr                        VARCHAR2(2000);
    Message                            VARCHAR2(2000);
    Record_ID                        NUMBER;
    --    Parameter
    CURSOR Cur_Parameter (PInstance NUMBER) IS
        SELECT i.Record_ID, p.ParameterName, p.P_String, p.P_Number, p.P_Date
        FROM AD_PINSTANCE i, AD_PINSTANCE_PARA p
        WHERE i.AD_PInstance_ID=PInstance
        AND i.AD_PInstance_ID=p.AD_PInstance_ID(+)
        ORDER BY p.SeqNo;
    --    Parameter Variables
    p_MVIEW                        VARCHAR2(2000);

BEGIN
    DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing ' || PInstance_ID);
    ResultStr := 'PInstanceNotFound';
    UPDATE AD_PINSTANCE
    SET Created = SYSDATE,
        IsProcessing = 'Y'
    WHERE AD_PInstance_ID=PInstance_ID;
    COMMIT;
--    Get Parameters
    ResultStr := 'ReadingParameters';
    FOR p IN Cur_Parameter (PInstance_ID) LOOP
        Record_ID := p.Record_ID;
        IF (p.ParameterName = 'MVIEW') THEN
             P_PTX_MVIEW := p.P_String;
            DBMS_OUTPUT.PUT_LINE('  MVIEW=' || p_MVIEW);
        ELSE
            DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || p.ParameterName);
         END IF;
    END LOOP;    --    Get Parameter
    DBMS_OUTPUT.PUT_LINE('  Record_ID=' || Record_ID);

       
DBMS_MVIEW.REFRESH (p_PTX_MVIEW, 'c');

<<FINISH_PROCESS>>
    --  Update AD_PInstance
    DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || Message);
    UPDATE  AD_PINSTANCE
    SET Updated = SYSDATE,
        IsProcessing = 'N',
        Result = 1,                 -- success
        ErrorMsg = Message
    WHERE   AD_PInstance_ID=PInstance_ID;
    COMMIT;
    RETURN;

EXCEPTION
    WHEN  OTHERS THEN
        ResultStr := ResultStr || ': ' || SQLERRM || ' - ' || Message;
        DBMS_OUTPUT.PUT_LINE(ResultStr);
        UPDATE  AD_PINSTANCE
        SET Updated = SYSDATE,
            IsProcessing = 'N',
            Result = 0,             -- failure
            ErrorMsg = ResultStr
        WHERE   AD_PInstance_ID=PInstance_ID;
        COMMIT;
        RETURN;
END ;
/

I hope this is helpful.