PROCEDURE xxbom_explode_bom_for_abc(i_item_id IN NUMBER, i_organization_id number, i_num_so_line_id number)
IS
g_chr_excp_msg VARCHAR2(4000);
g_chr_log_msg VARCHAR2(4000);
g_dte_sysdate DATE := SYSDATE;
g_num_elig_comp_exist NUMBER := 0;
g_num_item_inc NUMBER := 0;
v_num_boe_open_qty NUMBER;
v_num_bill_sequence_id NUMBER;
v_rec_inc NUMBER := 0;
v_num_boe_new_qty NUMBER;
v_num_star_item_chk NUMBER;
v_is_pci_non_phantom VARCHAR2(1) := ‘N’;
v_std_item_exist_for_phantom NUMBER;
v_item_type_count NUMBER;
v_num_bom_header_id NUMBER;
v_num_component_item_id mtl_system_items_b.inventory_item_id%type;
v_chr_description mtl_system_items_b.description%type;
v_chr_base_unit mtl_system_items_b.primary_uom_code%type;
v_chr_bom_item mtl_system_items_b.segment1%type;
v_item_type mtl_system_items_b.item_type%TYPE;
v_bom_item_type mtl_system_items_b.bom_item_type%TYPE;
v_wip_supply_type mtl_system_items_b.wip_supply_type%TYPE;
———
—
BEGIN
fnd_file.put_line(fnd_file.log, ‘Start of Explode_BOM procedure at ‘||TO_CHAR(SYSDATE,’RRRR/MM/DD HH24:MI:SS’) );
fnd_file.put_line(fnd_file.log, ‘Parameters of Explode_BOM procedure’);
fnd_file.put_line(fnd_file.log, ‘i_item_id – ‘||i_item_id);
—
IF v_chr_assem_bom_number IS NULL THEN
v_chr_assem_bom_number := xxbom_abc_bom_variant;
END IF;
—
BEGIN
SELECT DISTINCT msib.segment1 bom_number,
msib.inventory_item_id component_item_id,
SUBSTR(msib.description, 1, 30) description,
primary_uom_code baseUnit
INTO v_chr_bom_item,
v_num_component_item_id,
v_chr_description,
v_chr_base_unit
FROM oe_order_lines_all oola, mtl_system_items_b msib
WHERE 1 = 1
AND oola.line_id = i_num_so_line_id
AND oola.inventory_item_id = msib.inventory_item_id
AND oola.ship_from_org_id = msib.organization_id;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log, ‘Error occured when deriving main assembly in sub assembly’);
END;
—
BEGIN
BEGIN
fnd_file.put_line(fnd_file.log, ‘Deriving Item type, BOM Item Type, WIP Supply Type’);
SELECT msib.item_type, msib.bom_item_type, msib.wip_supply_type
INTO v_item_type, v_bom_item_type, v_wip_supply_type
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id = i_item_id
AND msib.organization_id = i_organization_id;
EXCEPTION
WHEN OTHERS THEN
v_item_type := NULL;
v_bom_item_type := NULL;
v_wip_supply_type := NULL;
g_chr_excp_msg := ‘Exception occured while deriving item type,wip supply type for item id ‘||i_item_id||’ error message – ‘||SQLERRM;
fnd_file.put_line(fnd_file.log, g_chr_excp_msg);
END;
fnd_file.put_line(fnd_file.log, ‘v_item_type => ‘||v_item_type);
fnd_file.put_line(fnd_file.log, ‘v_bom_item_type => ‘||v_bom_item_type);
fnd_file.put_line(fnd_file.log, ‘v_wip_supply_type => ‘||v_wip_supply_type);
—
IF v_item_type_count > 0 AND v_wip_supply_type <> 6 THEN
v_is_pci_non_phantom := ‘Y’;
ELSE
v_is_pci_non_phantom := ‘N’;
END IF;
fnd_file.put_line(fnd_file.log, ‘v_is_pci_non_phantom => ‘||v_is_pci_non_phantom);
IF v_bom_item_type = 4 AND v_wip_supply_type = 6 THEN
BEGIN
—
SELECT COUNT(1)
INTO v_std_item_exist_for_phantom
FROM bom_inventory_components bic,mtl_system_items_b msib,bom_bill_of_materials bom
WHERE msib.inventory_item_id = bic.component_item_id
AND msib.bom_item_type = 4
AND msib.inventory_item_flag = ‘Y’
AND NVL(effectivity_date,g_dte_sysdate) <= g_dte_sysdate
AND NVL(bic.disable_date,g_dte_sysdate) >= g_dte_sysdate
AND bom.alternate_bom_designator IS NULL
AND bom.assembly_item_id = i_item_id
AND bic.bill_sequence_id = bom.common_bill_sequence_id
AND bom.organization_id = i_organization_id
AND msib.organization_id = i_organization_id;
—
fnd_file.put_line(fnd_file.log, ‘v_std_item_exist_for_phantom => ‘||v_std_item_exist_for_phantom);
—
IF v_std_item_exist_for_phantom = 0 THEN
RETURN;
END IF;
fnd_file.put_line(fnd_file.log, ‘v_std_item_exist_for_phantom <> 0 ‘);
EXCEPTION
WHEN OTHERS THEN
g_chr_excp_msg := ‘OTHERS – No BOM exist for item id ‘||i_item_id||’ and inv org id – ‘||i_organization_id||’, error message – ‘||SQLERRM;
fnd_file.put_line(fnd_file.log, g_chr_excp_msg);
END;
END IF;
EXCEPTION WHEN OTHERS THEN
g_chr_excp_msg := ‘Exception occured for ‘||i_item_id||’ and inv org id – ‘||i_organization_id||’, error message – ‘||SQLERRM;
fnd_file.put_line(fnd_file.log, g_chr_excp_msg);
END;
fnd_file.put_line(fnd_file.log, ‘IF v_num_boe_open_qty = 0 OR v_num_boe_new_qty > 0 ‘);
fnd_file.put_line(fnd_file.log, ‘v_num_boe_open_qty=’||v_num_boe_open_qty||’,v_num_boe_new_qty=’||v_num_boe_new_qty);
—
BEGIN
SELECT bom.common_bill_sequence_id
INTO v_num_bill_sequence_id
FROM bom_bill_of_materials bom
WHERE bom.alternate_bom_designator IS NULL
AND bom.assembly_item_id = i_item_id
AND bom.organization_id = i_organization_id;
fnd_file.put_line(fnd_file.log, ‘v_num_bill_sequence_id – ‘||v_num_bill_sequence_id||’,i_item_id – ‘||i_item_id||’,i_organization_id – ‘||i_organization_id);
EXCEPTION WHEN OTHERS THEN
g_chr_excp_msg := ‘No BOM exist for item id ‘||i_item_id||’ and inv org id – ‘||i_organization_id||’, error message – ‘||SQLERRM;
fnd_file.put_line(fnd_file.log, g_chr_excp_msg);
END;
—
IF v_is_pci_non_phantom = ‘N’ THEN
fnd_file.put_line(fnd_file.log, ‘rec_comp component cursor start ‘);
FOR rec_comp IN (SELECT msib.wip_supply_type,
bic.COMPONENT_YIELD_FACTOR YIELD,
bic.component_item_id,
msib.segment1 partNr,
bic.component_quantity comp_item_qty,
msib.primary_uom_code unit
FROM bom_inventory_components bic, mtl_system_items_b msib
WHERE msib.inventory_item_id = bic.component_item_id
AND msib.inventory_item_flag = ‘Y’
AND msib.bom_item_type = 4
AND NVL(effectivity_date,g_dte_sysdate) <= g_dte_sysdate
AND NVL(bic.disable_date,g_dte_sysdate) >= g_dte_sysdate
AND bic.bill_sequence_id = v_num_bill_sequence_id
AND msib.organization_id = i_organization_id
ORDER BY bic.item_num)
LOOP
v_rec_inc := v_rec_inc + 1;
g_num_elig_comp_exist := g_num_elig_comp_exist + 1;
g_chr_log_msg := rec_comp.partnr;
v_num_star_item_chk := length(rec_comp.partnr) – length(replace(rec_comp.partnr, ‘*’,”));
IF rec_comp.wip_supply_type !=6 and rec_comp.comp_item_qty > 0 and v_num_star_item_chk = 0 THEN
g_num_line_count := g_num_line_count + 1;
v_num_bom_header_id := xxbom_abc_iface_extract_s.nextval;
BEGIN
INSERT INTO xxbom_abc_iface_extract
(bom_header_id,
company,
plant,
bom_item,
bom_number,
bom_variant,
assembly_item_id,
description,
so_base_quantity,
so_base_unit,
so_line_id,
valid_from_date,
component_item_id,
component_line_no,
component_part_number,
per_assembly_qty,
unit,
yield,
process_flag,
org_id,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
values
(v_num_bom_header_id,
v_chr_company_code,
v_chr_plant_code,
v_chr_bom_item,
v_chr_assem_bom_number,
‘1’,
v_num_component_item_id,
v_chr_description,
1,
v_chr_base_unit,
i_num_so_line_id,
SYSDATE,
rec_comp.component_item_id,
g_num_line_count,
rec_comp.partnr,
rec_comp.comp_item_qty,
rec_comp.unit,
rec_comp.yield,
‘N’,
g_org_id,
g_request_id,
g_user_id,
SYSDATE,
g_user_id,
SYSDATE,
g_login_id
);
EXCEPTION WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log, ‘Exception occured while inserting the components. ‘||SQLERRM);
END;
END IF;
fnd_file.put_line(fnd_file.log, g_chr_log_msg);
fnd_file.put_line(fnd_file.log, ‘Calling Explode_BOM procedure for component item id – ‘||rec_comp.component_item_id||’, comp qty – ‘||rec_comp.comp_item_qty );
IF rec_comp.wip_supply_type = 6 or v_num_star_item_chk > 0 THEN
xxbom_explode_bom_for_abc(rec_comp.component_item_id, i_organization_id, i_num_so_line_id );
END IF;
END LOOP;
END IF;
—
IF v_rec_inc = 0 THEN
fnd_file.put_line(fnd_file.log, ‘IF v_rec_inc = 0 THEN’);
fnd_file.put_line(fnd_file.log, ‘i_item_id – ‘||i_item_id);
fnd_file.put_line(fnd_file.log, ‘v_num_boe_new_qty – ‘||v_num_boe_new_qty);
g_num_item_inc := g_num_item_inc + 1;
END IF;
—
IF v_num_boe_open_qty > 0 THEN
fnd_file.put_line(fnd_file.log, ‘IF v_num_boe_open_qty > 0 ‘);
fnd_file.put_line(fnd_file.log, ‘g_num_item_inc – ‘||g_num_item_inc);
fnd_file.put_line(fnd_file.log, ‘i_item_id – ‘||i_item_id);
fnd_file.put_line(fnd_file.log, ‘v_num_boe_open_qty – ‘||v_num_boe_open_qty);
END IF;
fnd_file.put_line(fnd_file.log, ‘End of Explode_BOM procedure at ‘||TO_CHAR(SYSDATE,’RRRR/MM/DD HH24:MI:SS’) );
EXCEPTION
WHEN OTHERS THEN
g_chr_excp_msg := ‘Exception occured in XXINV_PROCESS_BOE_BOX_VLVS_PKG.Explode_BOM ‘||SQLERRM;
fnd_file.put_line(fnd_file.log, g_chr_excp_msg);
END xxbom_explode_bom_for_abc;
