I have two DB’s. One for Oracle EBS and other for DW. Now I want to have BOM explode data in DW table. To acheive it I need to use BOM Explode API Bompexpl.exploder_userexit.
Issue is As i want the data in DW and i cannot run this API in DW.
Please suggest How it can be acheived.

I tried define this API in a procedure in EBS DB and call this using DBLINK. Below is the procedure.

CREATE OR REPLACE PROCEDURE bom_explode(levels_to_explode NUMBER,org_id NUMBER,grp_id NUMBER,item_id NUMBER,rev_date DATE,
impl_flag NUMBER,explode_option NUMBER,MODULE NUMBEr,std_comp_flag NUMBER,x_err_msg OUT VARCHAR2,
x_error_code OUT NUMBER) IS

`BEGIN

Bompexpl.exploder_userexit
  (
     levels_to_explode    => levels_to_explode
     ,org_id              => org_id
     ,grp_id              => grp_id
     ,item_id             => item_id
     ,rev_date            => rev_date
     ,expl_qty            => 1
     ,impl_flag           => impl_flag
     ,explode_option      => explode_option
     ,MODULE              => MODULE
     ,std_comp_flag       => std_comp_flag
     ,err_msg             => x_err_msg
     ,error_code          => x_error_code
  ) ; 
END;`

But I need scripts to grand permission on this Procdure to DW from EBS.
Can you please let me know the scripts is very much appreciated

Or if there is other suitable way of getting the data in DW i am open to it as well.

Thank you