Bom update api in oracle apps

Bom update api in oracle apps

In this post , We will discuss about bom update api in oracle apps. Oracle has provided the Standard api in oracle apps to update the BOM information’s. This API helps to update the Existing component information’s of the BOM as well as to insert the new components in the existing BOM. Here below is the complete code of the bom update api in oracle apps.

Sample code of bom update api in oracle apps.

Declare

--script for sweep the supply tupe from bulk to Assembly Pull

l_bom_header_rec Bom_Bo_Pub.Bom_Head_Rec_Type := Bom_Bo_Pub.G_MISS_BOM_HEADER_REC;

l_bom_revision_tbl Bom_Bo_Pub.Bom_Revision_Tbl_Type := Bom_Bo_Pub.G_MISS_BOM_REVISION_TBL;

l_bom_component_tbl Bom_Bo_Pub.Bom_Comps_Tbl_Type := Bom_Bo_Pub.G_MISS_BOM_COMPONENT_TBL;

l_bom_ref_designator_tbl Bom_Bo_Pub.Bom_Ref_Designator_Tbl_type := Bom_Bo_Pub.G_MISS_BOM_REF_DESIGNATOR_TBL;

l_bom_sub_component_tbl Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type := Bom_Bo_Pub.G_MISS_BOM_SUB_COMPONENT_TBL;

l_error_message_list Error_handler.error_tbl_type;

l_x_bom_header_rec Bom_Bo_Pub.bom_Head_Rec_Type;

l_x_bom_revision_tbl Bom_Bo_Pub.Bom_Revision_Tbl_Type;

l_x_bom_component_tbl Bom_Bo_pub.Bom_Comps_Tbl_Type;

l_x_bom_ref_designator_tbl Bom_Bo_Pub.Bom_Ref_Designator_Tbl_Type;

l_x_bom_sub_component_tbl Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type;

l_x_return_status VARCHAR2(2000);

l_x_msg_count NUMBER;

i NUMBER;

cursor c1 is

select  ood.organization_code,

ood.organization_id,

  msi.segment1, 

msi.description ,

bic.item_num,

bic.operation_seq_num,

bic.component_item_id, 

msi1.segment1 COMPONENT,

ml.meaning,

bic.wip_supply_type,

bic.effectivity_date,

bic.disable_date

from

mtl_system_items_b msi,

org_organization_definitions ood,

bom_bill_of_materials bom,

bom_inventory_components bic,

mtl_system_items_b msi1,

mfg_lookups ml

where bom.assembly_item_id = msi.inventory_item_id

and bom.bill_sequence_id = bic.bill_sequence_id

and msi.organization_id = ood.organization_id

and bom.organization_id = ood.organization_id

and msi1.inventory_item_status_code ='Active'

and bic.component_item_id = msi1.inventory_item_id

and msi1.organization_id = ood.organization_id

and ml.lookup_code(+) = bic.wip_supply_type

and ml.lookup_type(+) = 'WIP_SUPPLY'

and ood.organization_id = :p_organization_id

and  msi.segment1=:p_Assembly_Item_name

and  msi1.segment1=:p_Component_Item_Name;

Begin

 

FND_GLOBAL.apps_initialize (1001255, 50326, 700, 0);

 

for j in c1 loop

i :=1;

dbms_output.put_line(' Assembly_Item_name ' || j.SEGMENT1);

dbms_output.put_line(' Component_Item_Name' || j.COMPONENT);

l_bom_component_tbl(i)  := bom_bo_pub.g_miss_bom_component_rec;

l_bom_component_tbl(i).transaction_type  := 'UPDATE';

l_bom_component_tbl(i).Organization_CODE := j.ORGANIZATION_CODE;

l_bom_component_tbl(i).Assembly_Item_name :=  j.SEGMENT1;

l_bom_component_tbl(i).Start_effective_date := j.EFFECTIVITY_DATE;

l_bom_component_tbl(i).Component_Item_Name := j.COMPONENT;

l_bom_component_tbl(i).Wip_Supply_Type := 2;

l_bom_component_tbl(i).Item_Sequence_Number := j.ITEM_NUM;

l_bom_component_tbl(i).operation_Sequence_Number := j.OPERATION_SEQ_NUM;

l_bom_component_tbl(i).return_status := NULL;

Error_Handler.Initialize;

bom_bo_pub.Process_Bom

( p_bo_identifier => 'BOM'

, p_api_version_number => 1.0

, p_init_msg_list => TRUE

, p_bom_header_rec => l_bom_header_rec

, p_bom_revision_tbl => l_bom_revision_tbl

, p_bom_component_tbl => l_bom_component_tbl

, p_bom_ref_designator_tbl => l_bom_ref_designator_tbl

, p_bom_sub_component_tbl => l_bom_sub_component_tbl

, x_bom_header_rec => l_x_bom_header_rec

, x_bom_revision_tbl => l_x_bom_revision_tbl

, x_bom_component_tbl => l_x_bom_component_tbl

, x_bom_ref_designator_tbl => l_x_bom_ref_designator_tbl

, x_bom_sub_component_tbl => l_x_bom_sub_component_tbl

, x_return_status => l_x_return_status

, x_msg_count => l_x_msg_count

, p_debug => 'N'

, p_output_dir => ''

, p_debug_filename => ''

);

dbms_output.put_line('Return Status = '||l_x_return_status);

dbms_output.put_line('Message Count = '||l_x_msg_count);

Error_Handler.Get_message_list(l_error_message_list);

if l_x_return_status <> 'S'

then

for k in 1..l_x_msg_count loop

dbms_output.put_line(TO_CHAR(k)||' MESSAGE TEXT '||SUBSTR(l_error_message_list(k).message_text,1,250));

dbms_output.put_line(TO_CHAR(k)||' MESSAGE TYPE '||l_error_message_list(k).message_type);

end loop;

rollback;

else

commit;

end if;

end loop;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line(' OTHER exception ');

end;