Wednesday, June 10, 2015

Bill of Materials - Exploding BOM using - bompxinq.exploder_userexit

THE MAIN purpose OF exploding BOM IS TO get THE details FROM THE TEMPORARY TABLE that oracle uses TO hold data.
Its SESSION based. So once you ARE OUT OF that SESSION THE DATA IS gone.
BOM_SMALL_EXPL_TEMP IS THE TABLE used TO hold THE DATA IN A session.
DATA cannot be retrived be querying THE VIEW FROM FORM --> HELP-->Record History.
Hence explode THE BOM FOR THE Item you needed data.
There ARE many ways  OF holding THE data.You can WRITE TO A FILE, PRINT report so on..Its upto you.
IN this post i used a table BOM_SMALL_EXPL_TEMP1 to hold the data.
Remember its not a temporary table, its a copy of BOM_SMALL_EXPL_TEMP with all the columns.
I too found it difficult to find the values to explode BOM.
So i have explored the standard oracle form to find out what columns its uses to explode.
 All the comments after assignments are the standard oracle Block.FieldName (Block Name -> B_BILL_OF_MATLS).
 So you can pass the values of your Appliction if needed.



/* Formatted on 6/10/2015 2:34:04 PM (QP5 v5.265.14096.37972) */
CREATE OR REPLACE PROCEDURE EAM_BOM_EXPLODE (p_item_id IN NUMBER) /* The BOM assembly which you want to explode */
IS
   v_group_id        NUMBER;
   x_error_message   VARCHAR2 (2000);
   x_error_code      NUMBER;
   sess_id           NUMBER;
   l_rec_count       NUMBER;
BEGIN
   -- fnd_global.apps_initialize (<>, <>, <>);

   DELETE FROM BOM_SMALL_EXPL_TEMP1;

   COMMIT;

   SELECT bom_explosion_temp_s.NEXTVAL INTO v_group_id FROM DUAL;

   SELECT bom_explosion_temp_session_s.NEXTVAL INTO sess_id FROM DUAL;

   bompxinq.exploder_userexit (Verify_Flag         => 0,
                               Org_Id              => 1213,
                               Order_By            => 1, --:B_Bill_Of_Matls.Bom_Bill_Sort_Order_Type,
                               Grp_Id              => v_group_id,
                               Session_Id          => 0,
                               Levels_To_Explode   => 20, --:B_Bill_Of_Matls.Levels_To_Explode,
                               Bom_Or_Eng          => 1, -- :Parameter.Bom_Or_Eng,
                               Impl_Flag           => 1, --:B_Bill_Of_Matls.Impl_Only,
                               Plan_Factor_Flag    => 2, --:B_Bill_Of_Matls.Planning_Percent,
                               Explode_Option      => 3, --:B_Bill_Of_Matls.Bom_Inquiry_Display_Type,
                               Module              => 2, --:B_Bill_Of_Matls.Costs,
                               Cst_Type_Id         => 0, --:B_Bill_Of_Matls.Cost_Type_Id,
                               Std_Comp_Flag       => 2,
                               Expl_Qty            => 1, --:B_Bill_Of_Matls.Explosion_Quantity,
                               Item_Id             => p_item_id, --:B_Bill_Of_Matls.Assembly_Item_Id,
                               Alt_Desg            => NULL, --:B_Bill_Of_Matls.Alternate_Bom_Designator,
                               Comp_Code           => NULL,
                               Unit_Number_From    => 0, --NVL(:B_Bill_Of_Matls.Unit_Number_From, :CONTEXT.UNIT_NUMBER_FROM),
                               Unit_Number_To      => 'ZZZZZZZZZZZZZZZZZ', --NVL(:B_Bill_Of_Matls.Unit_Number_To, :CONTEXT.UNIT_NUMBER_TO),
                               Rev_Date            => SYSDATE, --:B_Bill_Of_Matls.Disp_Date,
                               Show_Rev            => 1,                -- yes
                               Material_Ctrl       => 2, --:B_Bill_Of_Matls.Material_Control,
                               Lead_Time           => 2, --:B_Bill_Of_Matls.Lead_Time,
                               err_msg             => x_error_message, --err_msg
                               ERROR_CODE          => x_error_code); --error_code

   SELECT COUNT (*)
     INTO l_rec_count
     FROM                                            --BOM_EXPLOSION_TEMP temp
         BOM_SMALL_EXPL_TEMP temp
    WHERE temp.GROUP_ID = v_group_id;

   DBMS_OUTPUT.PUT_LINE ('l_rec_count = ' || l_rec_count);

   INSERT INTO BOM_SMALL_EXPL_TEMP1
      SELECT * FROM BOM_SMALL_EXPL_TEMP;

   COMMIT;

   DBMS_OUTPUT.PUT_LINE (x_error_message);
   DBMS_OUTPUT.PUT_LINE (x_error_code);
   DBMS_OUTPUT.PUT_LINE ('grp_id = ' || v_group_id);
   DBMS_OUTPUT.PUT_LINE ('sess_id = ' || sess_id);
END;




Once you ARE done compiling EXECUTE THE FOLLOWING command TO INSERT THE DATA INTO THE TABLE you have created IN THE above CASE its BOM_SMALL_EXPL_TEMP1.

BEGIN
   EAM_BOM_EXPLODE (p_item_id);
END;
/

SELECT * FROM BOM_SMALL_EXPL_TEMP1;

No comments:

Post a Comment