4 Replies Latest reply: Jul 30, 2013 8:10 AM by 1008891 RSS

    How to calculate  extended quantity in BOM IMPLOSION QUERY

    1008891

      Hi guys,

       

      I have given an assignment to create BOM  item where used report, For this purpose I am using an BOM implosion API  (PACKAGE.PROCEDURE = BOMPIINQ.IMPLODER_USEREXIT)

       

      I am calling this API like this-

       

      declare

        V_SEQUENCE_ID    number;

        v_err_msg        VARCHAR2(80);

        v_err_code       NUMBER;

        V_DATE           VARCHAR2 (30);

      begin

      delete BOM_SMALL_IMPL_TEMP;

      -- V_SEQUENCE_ID:=142354;

      SELECT BOM_IMPLOSION_TEMP_S.NEXTVAL INTO V_SEQUENCE_ID FROM SYS.DUAL;

      SELECT TO_CHAR (SYSDATE, 'YYYY/MM/DD HH24:MI') INTO V_DATE FROM DUAL;

       

      BOMPIINQ.IMPLODER_USEREXIT (

              SEQUENCE_ID => V_SEQUENCE_ID,        

              ENG_MFG_FLAG => 1,                   

              ORG_ID => :org_id,             

              IMPL_FLAG => 1,                    

              DISPLAY_OPTION => 1,                    

              LEVELS_TO_IMPLODE => 10,             

              ITEM_ID =>:item_id,           --34077520002

              IMPL_DATE => V_DATE,

              unit_number_from =>null,

                unit_number_to=>null,               

              ERR_MSG => v_err_msg,

              ERR_CODE =>v_err_code,

              organization_option =>1,

              organization_hierarchy =>null,

              serial_number_from =>null,

                serial_number_to =>null

               );

      end;

       

       

      Now I query the table BOM_SMALL_IMPL_TEMP and it shows the data as per the structure of the item where used.

       

      NOW ALL I WANT IS I WANT TO CALCULATE EXTENDED QUANTITY  (PARENT-QTY * ITS_CHILD_QTY)

       

      CURRENTLY IT SHOWS ONLY COMPONENT QUANTITY. HOW CAN I CALCULATE EXTENDED QUANTITY LIKE WE DO IN BOM EXPLOSION?

       

      Can any one help me on this?

       

      Thanks,

      Himanshu Gupta

        • 1. Re: How to calculate  extended quantity in BOM IMPLOSION QUERY
          Sai_k

          You would need to write another Query on the BOM_SMALL_IMPL_TEMP to get that .

          • 2. Re: How to calculate  extended quantity in BOM IMPLOSION QUERY
            1008891

            I have written this one

             

            select

            --round(NVL(exp(SUM(log(2.718,component_quantity))),1),3) from BOM_SMALL_IMPL_TEMP I WHERE I.him = O.him AND I.parent_item_id <= O.parent_item_id ) as RunningProduct,

            CURRENT_LEVEL,

            --,ROWNUM t

            PARENT_SORT_CODE, SORT_CODE, --temp

            LOWEST_ITEM_COST, --temp

            LOWEST_ITEM,

            CURRENT_ITEM,

            PARENT_ITEM, --parent_item_description,

            asdq component_qty,

            --DECODE(COMPONENT_QUANTITY, COMPONENT_QUANTITY, (select nvl(asl.component_quantity,1)* nvl(asd.component_quantity,1)

            --from BOM_SMALL_IMPL_TEMP asl, BOM_SMALL_IMPL_TEMP ASD

                --WHERE ASL.SORT_CODE = ASD.PARENT_SORT_CODE ) ) ASD,

            --SIMPLE_LOG_PRODUCT,

            --him,

            exten2, aslq,

            --decode(current_level,3, aslq, exten2, null),

            --RUNNINGPRODUCT,

            --PARENT_ITEM_DESCRIPTION,

            PARENT_ITEM_TYPE,

            ORG_CODE,

            EFFECTIVITY_DATE,

            DISABLE_DATE

            --DECODE(CURRENT_LEVEL, 1, COMPONENT_QUANTITY, COMPONENT_QUANTITY, 2, SIMPLE_LOG_PRODUCT) XYZ,

            --DECODE(CURRENT_LEVEL, 1, COMPONENT_QUANTITY, COMPONENT_QUANTITY, 2,   (SELECT BSIT.COMPONENT_QUANTITY FROM BOM_SMALL_IMPL_TEMP BSIT WHERE BSIT.CURRENT_LEVEL=1 AND ROWNUM = 1) ) S, 

            from (

            SELECT 

            ASD.CURRENT_LEVEL, ASD.PARENT_SORT_CODE, ASD.SORT_CODE, --| CURRENT_LEVEL,,

            DECODE(ASD.LOWEST_ITEM_ID,ASD.LOWEST_ITEM_ID,(SELECT DISTINCT SEGMENT1 FROM MSI WHERE INVENTORY_ITEM_ID = ASD.LOWEST_ITEM_ID))   LOWEST_ITEM,

            DECODE(ASD.LOWEST_ITEM_ID,ASD.LOWEST_ITEM_ID,(SELECT DISTINCT  ROUND (ITEM_COST,3) FROM  CST_ITEM_COSTS WHERE INVENTORY_ITEM_ID = ASD.LOWEST_ITEM_ID

            AND ORGANIZATION_ID = (SELECT DISTINCT ORGANIZATION_ID FROM BOM_SMALL_IMPL_TEMP)))   LOWEST_ITEM_COST,

            DECODE(ASD.CURRENT_ITEM_ID,ASD.CURRENT_ITEM_ID,(SELECT DISTINCT SEGMENT1 FROM MSI WHERE INVENTORY_ITEM_ID = ASD.CURRENT_ITEM_ID))   CURRENT_ITEM,

            DECODE(ASD.parent_ITEM_ID,ASD.parent_ITEM_ID,(SELECT DISTINCT SEGMENT1 FROM MSI WHERE INVENTORY_ITEM_ID = ASD.parent_item_id)) PARENT_ITEM,

            --DECODE (current_level, 1, parent_item_ID, current_item_ID) him,

            --(SELECT XX_BOMIMPL ((select parent_item_id from BOM_SMALL_IMPL_TEMP),1) FROM DUAL) gh,

            ASD.component_quantity asdq , asl.component_quantity aslq,

            nvl(asl.component_quantity,1)* nvl(asd.component_quantity,1) EXTEN2,

            --(select round(NVL(exp(SUM(log(2.718,component_quantity))),1),3) from BOM_SMALL_IMPL_TEMP) SIMPLE_log_product,

            DECODE(asd.parent_ITEM_ID,asd.parent_ITEM_ID,(SELECT DISTINCT DESCRIPTION FROM MSI WHERE INVENTORY_ITEM_ID = asd.parent_item_id)) PARENT_ITEM_DESCRIPTION,

            DECODE(ASD.parent_ITEM_ID,ASD.parent_ITEM_ID,(SELECT DISTINCT ITEM_TYPE FROM MSI WHERE INVENTORY_ITEM_ID = ASD.parent_item_id)) PARENT_ITEM_TYPE,

            DECODE(ASD.ORGANIZATION_ID, 125, 'MU1', 126, 'MU2') ORG_CODE, ASD.EFFECTIVITY_DATE, ASD.DISABLE_DATE --asl.sort_code asls, asd.sort_code asds

            FROM  BOM_SMALL_IMPL_TEMP asl, BOM_SMALL_IMPL_TEMP ASD

            WHERE ASL.SORT_CODE = ASD.PARENT_SORT_CODE 

            --where current_level in (select max(current_level) from BOM_SMALL_IMPL_TEMP)

            order by sort_code

            )

             

            see exten2 it will give me extended quantity for level2 items but it failed for level3 and level4 items. what needs to be done in this query to achieve extended qty in level3 and leve4 items?

             

            Please help.

            • 3. Re: How to calculate  extended quantity in BOM IMPLOSION QUERY
              Sai_k

              When you find Item where used , it would give you component Quantity based on Assembly Qty 1 to be produced . So its just a matter of simple multiplication of parent Quantity of what you need to the Component Quantity.

               

              let me explain . For a Assembly A you have components

              A1   -2Qty

              A2   - 4Qty

              A3   - 1Qty

               

               

              When you find Where used for Item A1  , it would say used in A - Needs 2 Qty - this is for 1 A end assembly you would need 2 A2's  . So for 2A's you would need 4 A2's, and it would always show up w.r.t 1 .

               

              Check this Query if you are allowed to not use the API , this Query might help you . tweak it as you like for eff dates and Qty .

               

              SELECT LEVEL, bbom.assembly_item_id, msi1.segment1 parent , bic.component_item_id,

                     (SELECT msi2.segment1 FROM mtl_system_items msi2

                      WHERE inventory_item_id = bic.component_item_id

                      AND    organization_id = :P_org_id) comp ,bic.COMPONENT_QUANTITY

              FROM   bom_bill_of_materials bbom

                     ,apps.bom_inventory_components bic

                     ,mtl_system_items msi1

              WHERE bbom.bill_Sequence_id = bic.bill_sequence_id

              AND      bbom.assembly_item_id = msi1.inventory_item_id

              AND      bbom.organization_id = msi1.organization_id

              START WITH component_item_id =:P_component ----45076

              AND bbom.organization_id = :P_org_id

              CONNECT BY PRIOR bbom.assembly_item_id=bic.component_item_id

              AND bbom.organization_id = :P_org_id

              • 4. Re: How to calculate  extended quantity in BOM IMPLOSION QUERY
                1008891

                Thanks , Yes it works but need to work out.

                 

                I have used rather calling API and then query using this-

                 

                select t1.*,

                DECODE(T1.PART,T1.PART,(SELECT DISTINCT SEGMENT1 FROM MSI WHERE INVENTORY_ITEM_ID = T1.PART)) LOWEST_ITEM,

                DECODE(T1.PART,T1.PART,(SELECT DISTINCT DESCRIPTION FROM MSI WHERE INVENTORY_ITEM_ID = T1.PART)) LOWEST_ITEM_DESC,

                t2.segment1 PARENT_ITEM,

                T2.DESCRIPTION PARENT_DESCRIPTION,

                T2.ITEM_TYPE PARENT_ITEM_TYPE,

                DECODE(T2.ORGANIZATION_ID, 125, 'MU1', 126, 'MU2') ORG_CODE,

                T3.ITEM_COST,

                T1.QTY*T3.ITEM_COST EXTENDED_COST

                from

                (select t.*

                         from (WITH t AS (SELECT distinct SYS_CONNECT_BY_PATH(a.parent_item_id, '\') || '\' || a.current_item_id || '\' as path,

                                                          a.component_quantity qty_per_assembly

                                            FROM BOM_SMALL_IMPL_TEMP1 a,

                                                 mtl_system_items_b      b

                                           where a.parent_item_id = b.inventory_item_id

                                             and b.organization_id = a.organization_id

                                           START WITH b.item_type IN ('SA','FG MFG')

                                          --CONNECT BY  a.current_item_id = PRIOR a.parent_item_id

                                          CONNECT BY nocycle a.parent_item_id = PRIOR

                                                     a.current_item_id)

                                SELECT root_part, part, SUM(qty) AS qty

                                  FROM (SELECT SUBSTR(t1.path,

                                                      2,

                                                      INSTR(t1.path,'\', 1, 2) -

                                                      INSTR(t1.path, '\', 1, 1) - 1) as ROOT_PART,

                                               SUBSTR(t1.path,

                                                      INSTR(t1.path, '\', -1, 2) + 1,

                                                      INSTR(t1.path,'\', -1, 1) -

                                                      INSTR(t1.path, '\', -1, 2) - 1) as PART,

                                               (SELECT EXP(SUM(LN(t2.qty_per_assembly)))

                                                  FROM t t2

                                                 WHERE t1.path LIKE t2.path || '%') AS qty

                                          FROM t t1

                                         GROUP BY t1.path)

                                 GROUP BY root_part, part) t

                               where t.part = :lt_part

                       ) t1,

                      mtl_system_items_b t2, CST_ITEM_COSTS T3

                where t1.root_part = t2.inventory_item_id

                AND T1.PART = T3.INVENTORY_ITEM_ID

                AND T2.ORGANIZATION_ID = T3

                .ORGANIZATION_ID

                   --and t2.item_type = 'FG MFG'

                   and t2.organization_id = :lt_org;

                 

                and now it is running perfectly