This package will explode BOM details at all levels of a Finished Good and write the data to a file in the Directory
CREATE OR REPLACE PACKAGE XX_FINISHED_GOOD_BOM_PKG AS
PROCEDURE create_bom_list (
x_error_message OUT VARCHAR2,
x_error_code OUT VARCHAR2,
p_org_id IN NUMBER,
p_item_id IN NUMBER,
p_ord_qty IN NUMBER
);
PROCEDURE explode_bom_list (
x_error_message OUT VARCHAR2,
x_error_code OUT VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE
);
PROCEDURE explode_bom_list_main (
p_err_buff OUT VARCHAR2,
p_ret_code OUT VARCHAR2,
p_start_date IN VARCHAR2,
p_end_date IN VARCHAR2
);
END XX_FINISHED_GOOD_BOM_PKG;
/
CREATE OR REPLACE PACKAGE BODY XX_FINISHED_GOOD_BOM_PKG AS
PROCEDURE create_bom_list (
x_error_message OUT VARCHAR2,
x_error_code OUT VARCHAR2,
p_org_id IN NUMBER,
p_item_id IN NUMBER,
p_ord_qty IN NUMBER
)
IS
BEGIN
x_error_code:=NULL;
x_error_message:=NULL;
/***** Clearing Temp Table of the BOM Records from Earlier execution within this Session */
DELETE FROM bom_small_expl_temp;
COMMIT;
bompxinq.exploder_userexit(verify_flag => 0,
org_id => p_org_id,– Inventory Org ID
order_by => 1,
grp_id => 1, — Auto generated Unique Number
session_id => 0,
levels_to_explode => 7, — Multiple Levels of BOM to be exploded
bom_or_eng => 1,
impl_flag => 1,
plan_factor_flag => 2,
explode_option => 1,
module => 2,
cst_type_id => 0,
std_comp_flag => 2,
expl_qty => p_ord_qty,
item_id=> p_item_id,– Inventory Item ID of Finished Goods (BOM)
alt_desg => NULL,
comp_code => NULL,
unit_number_from => ”,
unit_number_to => ”,
rev_date => NULL,
show_rev => 1,
material_ctrl => 2,
lead_time => 2,
err_msg=> x_error_message,
error_code => x_error_code);
EXCEPTION
WHEN OTHERS THEN
x_error_message := substr(‘ERROR -‘ || sqlerrm,1,300);
x_error_code := substr(‘ERROR -‘ || sqlcode,1,300);
END create_bom_list;
PROCEDURE explode_bom_list (
x_error_message OUT VARCHAR2,
x_error_code OUT VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE
) AS
l_count number:=0;
cursor c1 is
SELECT
ooha.header_id,
ooha.order_number,
hl_ship.country,
oola.inventory_item_id
FROM
oe_order_headers_all ooha,
oe_order_lines_all oola,
hz_cust_site_uses_all hcs_ship,
hz_cust_acct_sites_all hca_ship,
hz_party_sites hps_ship,
hz_parties hp_ship,
hz_locations hl_ship
WHERE
oola.header_id = ooha.header_id
AND ooha.ship_to_org_id = hcs_ship.site_use_id
AND hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id
AND hca_ship.party_site_id = hps_ship.party_site_id
AND hps_ship.party_id = hp_ship.party_id
AND hps_ship.location_id = hl_ship.location_id
AND hl_ship.country ‘US’
–AND oola.inventory_item_id=75923
AND ooha.booked_date BETWEEN p_start_date and p_end_date;
begin
fnd_file.put_line (fnd_file.LOG,’Start of the BOM Explode program’);
delete from xdmc_bom_small_expl;
commit;
For i in c1 loop
l_count:=0;
select count(1) into l_count from xdmc_bom_small_expl where component_item_id=i.inventory_item_id
and top_item_id=i.inventory_item_id;
IF l_count=0 then
XDMC_FINISHED_GOOD_BOM_PKG.create_bom_list (
x_error_message,
x_error_code,
124,
i.inventory_item_id,
1
);
fnd_file.put_line (fnd_file.LOG,’x_error_code ‘||x_error_code);
insert into xdmc_bom_small_expl (select * from bom_small_expl_temp);
commit;
END IF;
end loop;
fnd_file.put_line (fnd_file.LOG,’End of the BOM Explode program’);
x_error_code:=NULL;
EXCEPTION WHEN OTHERS THEN
x_error_message := substr(‘ERROR -‘ || sqlerrm,1,300);
x_error_code := substr(‘ERROR -‘ || sqlcode,1,300);
end;
PROCEDURE explode_bom_list_main (
p_err_buff OUT VARCHAR2,
p_ret_code OUT VARCHAR2,
p_start_date IN VARCHAR2,
p_end_date IN VARCHAR2
) AS
l_error_message varchar2(1000);
l_error_code varchar2(100);
ld_start_date DATE := TO_DATE (p_start_date, ‘YYYY/MM/DD HH24:MI:SS’);
ld_end_date DATE := TO_DATE (p_end_date, ‘YYYY/MM/DD HH24:MI:SS’);
cursor c1 is
SELECT
ooha.order_number,
hl_ship.country exported_to_country,
msip.segment1 finished_good,
msic.segment1 imported_component,
aps.country imported_from_country,
xb.component_yield_factor,
xb.extended_quantity,
pol.quantity quote_quantity,
pol.unit_price quote_unit_price
FROM
oe_order_headers_all ooha,
oe_order_lines_all oola,
hz_cust_site_uses_all hcs_ship,
hz_cust_acct_sites_all hca_ship,
hz_party_sites hps_ship,
hz_parties hp_ship,
hz_locations hl_ship,
xdmc_bom_small_expl xb,
po_lines_all pol,
po_headers_all poh,
ap_supplier_sites_all aps,
mtl_system_items_b msip,
mtl_system_items_b msic
WHERE
oola.header_id = ooha.header_id
AND ooha.ship_to_org_id = hcs_ship.site_use_id
AND hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id
AND hca_ship.party_site_id = hps_ship.party_site_id
AND hps_ship.party_id = hp_ship.party_id
AND hps_ship.location_id = hl_ship.location_id
AND hl_ship.country ‘US’
–AND oola.inventory_item_id=75923
AND ooha.booked_date BETWEEN ld_start_date AND ld_end_date
and xb.top_item_id=oola.inventory_item_id
and xb.plan_level0
and pol.item_id=xb.component_item_id
and pol.po_header_id=poh.po_header_id
and poh.type_lookup_code = ‘QUOTATION’
and poh.status_lookup_code=’A’
and aps.vendor_site_id=poh.vendor_site_id
and aps.country’US’
and msip.inventory_item_id=oola.inventory_item_id
and msip.organization_id=124
and msic.inventory_item_id=pol.item_id
and msic.organization_id=124
;
v_fileid UTL_FILE.file_type;
vHeader VARCHAR2 (4000);
Val1 VARCHAR2 (8000);
lc_today VARCHAR2 (20) := TO_CHAR (SYSDATE, ‘YYYYMMDDHHMISS’);
lc_filedir VARCHAR2 (150) := NULL;
lc_filename VARCHAR2 (150) := NULL;
e_directory_path EXCEPTION;
begin
fnd_file.put_line (fnd_file.LOG,’Start of the Main program’);
explode_bom_list(l_error_message,l_error_code,ld_start_date,ld_end_date);
IF l_error_code is null then
FOR i in c1 loop
fnd_file.put_line (fnd_file.OUTPUT,i.order_number||’,’||i.finished_good);
end loop;
end if;
fnd_file.put_line (fnd_file.LOG,’End of the Main program’);
fnd_file.put_line (
fnd_file.LOG,
‘ Starting Extract ->’
|| ‘ ‘
|| TO_CHAR (SYSDATE, ‘MM-DD-YY HH:MI:SS’));
BEGIN
SELECT directory_path
INTO lc_filedir
FROM ALL_directories
WHERE DIRECTORY_NAME = ‘TEMP_DIR’;– Provide directory name here
EXCEPTION
WHEN OTHERS
THEN
RAISE e_directory_path;
END;
lc_filename := ‘Finished_Good_BOM_’ || lc_today || ‘.tsv’;
apps.fnd_file.put_line (apps.FND_FILE.LOG, ‘Directory – ‘ || lc_filedir);
apps.fnd_file.put_line (apps.FND_FILE.LOG, ‘File – ‘ || lc_filename);
v_fileid := UTL_FILE.fopen (lc_filedir, lc_filename, ‘W’,6400);
IF UTL_FILE.is_open (v_fileid)
THEN
fnd_file.put_line (fnd_file.LOG, ‘File Opened for Write’);
ELSE
v_fileid := UTL_FILE.fopen (lc_filedir, lc_filename, ‘W’);
fnd_file.put_line (fnd_file.LOG, ‘Still Closed’);
END IF;
vHeader :=’Order Number,Country Exported,Finished Good,Imported Component,Country Imported,Yield Factor,Extended Qty,Quote Qty,Quote Unit Price’;
UTL_FILE.put_line (v_fileid, vHeader);
fnd_file.put_line (fnd_file.LOG,’ Starting SQL ->’ || ‘ ‘ || TO_CHAR (SYSDATE, ‘MM-DD-YY HH:MI:SS’));
FOR i in c1 loop
Val1 := i.order_number||’,’||
i.exported_to_country||’,’||
i.finished_good||’,’||
i.imported_component||’,’||
i.imported_from_country||’,’||
i.component_yield_factor||’,’||
i.extended_quantity||’,’||
i.quote_quantity||’,’||
i.quote_unit_price;
UTL_FILE.put_line (v_fileid, Val1);
end loop;
UTL_FILE.fclose (v_fileid);
fnd_file.put_line (fnd_file.LOG,’ Ending Extraction ->’ || ‘ ‘ || TO_CHAR (SYSDATE, ‘MM-DD-YY HH:MI:SS’));
EXCEPTION
WHEN NO_DATA_FOUND
THEN
UTL_FILE.fclose (v_fileid);
fnd_file.put_line (
fnd_file.LOG,
‘ Exiting Extraction ->’
|| ‘ ‘
|| SUBSTR (SQLERRM, 1, 100)
|| ‘ ‘
|| TO_CHAR (SYSDATE, ‘MM-DD-YY HH:MI:SS’));
raise_application_error (
-20001,
‘ Exiting Extraction ->’ || ‘ ‘ || SUBSTR (SQLERRM, 1, 100));
WHEN OTHERS
THEN
UTL_FILE.fclose (v_fileid);
fnd_file.put_line (
fnd_file.LOG,
‘ Exiting Extraction Others ->’
|| ‘ ‘
|| SUBSTR (SQLERRM, 1, 100)
|| ‘ ‘
|| TO_CHAR (SYSDATE, ‘MM-DD-YY HH:MI:SS’));
raise_application_error (
-20002,
‘ Exiting Criteo Feed Others ->’
|| ‘ ‘
|| SUBSTR (SQLERRM, 1, 100));
END;
END XX_FINISHED_GOOD_BOM_PKG;
/
Recent Posts