API for Creating BOM and BOM Inventory Components

create or replace PROCEDURE GEPS_BOM_INV_COMP_PRG
(
errbuf                      OUT VARCHAR2
,retcode                   OUT NUMBER
)
as
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;
l_cnt NUMBER;
l_supply_type mfg_lookups.lookup_code%type;
l_hdritem_cnt number;
l_err_message varchar2(2000);
l_err_message_type varchar2(2000);

cursor c_component is
select *
from GEPS_BOM_INV_COMPONENTS_STG
where PROCESS_FLAG ='P';
Begin
FND_FILE.PUT_LINE(FND_FILE.LOG,'  Starting of GEPS_BOM_INV_COMP_PRG Procedure');
FND_FILE.PUT_LINE(FND_FILE.LOG,'  Initializing Apps');
    FND_GLOBAL.Apps_Initialize(FND_GLOBAL.USER_ID, FND_GLOBAL.RESP_ID, FND_GLOBAL.RESP_APPL_ID);
for l_rec in c_component loop
FND_FILE.PUT_LINE(FND_FILE.LOG,'  loop start');
l_cnt :=1;
begin
select   count(*)
into l_hdritem_cnt
from
mtl_system_items_b msi,
org_organization_definitions ood,
bom_bill_of_materials bom
where bom.assembly_item_id = msi.inventory_item_id
and msi.organization_id = ood.organization_id
and bom.organization_id = ood.organization_id
and ood.organization_code = l_rec.ORGANIZATION_CODE
and  msi.segment1 =l_rec.ITEM;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'l_hdritem_cnt:' || l_hdritem_cnt);
exception when others then
l_hdritem_cnt :=0;
end;
FND_FILE.PUT_LINE(FND_FILE.LOG,'l_hdritem_cnt:' || l_hdritem_cnt);
if l_hdritem_cnt < 1 then
FND_FILE.PUT_LINE(FND_FILE.LOG,'inside l_hdritem_cnt IF' );
l_bom_header_rec.Assembly_item_name := l_rec.ITEM;--'03401100';
l_bom_header_rec.Organization_code := l_rec.ORGANIZATION_CODE;--'E01';
l_bom_header_rec.Assembly_type := 1;
l_bom_header_rec.Transaction_Type := 'CREATE';--l_rec.TRANSACTION_TYPE;
l_Bom_Header_Rec.Return_Status := NULL;
else
FND_FILE.PUT_LINE(FND_FILE.LOG,'inside l_hdritem_cnt ELSIF' );
l_bom_header_rec.Assembly_item_name := null;--'03401100';
l_bom_header_rec.Organization_code := null;--'E01';
l_bom_header_rec.Assembly_type := null;
l_bom_header_rec.Transaction_Type := null;--l_rec.TRANSACTION_TYPE;
l_Bom_Header_Rec.Return_Status := NULL;
end if;
begin
select lookup_code
into l_supply_type
from mfg_lookups ml
where ml.lookup_type = 'WIP_SUPPLY'
and ml.meaning =l_rec.SUPPLY_TYPE
and ml.ENABLED_FLAG ='Y'
and nvl(ml.END_DATE_ACTIVE,sysdate) >=sysdate;
exception when others then
l_supply_type := 1;
end;
FND_FILE.PUT_LINE(FND_FILE.LOG,'l_supply_type:' ||l_supply_type );
--dbms_output.put_line(' Assembly_Item_name ' || j.SEGMENT1);
--dbms_output.put_line(' Component_Item_Name' || j.COMPONENT);
l_bom_component_tbl(l_cnt)                            := bom_bo_pub.g_miss_bom_component_rec;
if l_rec.TRANSACTION_TYPE like '%CREATE%' then
l_bom_component_tbl(l_cnt).transaction_type           :=  'CREATE';-- l_rec.TRANSACTION_TYPE;--'CREATE';
else
l_bom_component_tbl(l_cnt).transaction_type           :=  'UPDATE';
end if;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'sss1' );
l_bom_component_tbl(l_cnt).Organization_CODE := l_rec.ORGANIZATION_CODE;--'E01';--j.ORGANIZATION_CODE;
l_bom_component_tbl(l_cnt).Assembly_Item_name := l_rec.ITEM; --'SLIT_QWOV_OC';--j.SEGMENT1;
l_bom_component_tbl(l_cnt).Item_Sequence_Number := l_rec.ITEM_SEQ;--'790';--j.ITEM_NUM;--(i*10);
l_bom_component_tbl(l_cnt).operation_Sequence_Number :=  l_rec.OPERATION_SEQ;--'1';--j.OPERATION_SEQ_NUM;
l_bom_component_tbl(l_cnt).Component_Item_Name := l_rec.COMPONENT;--'01000015';--j.COMPONENT;
l_bom_component_tbl(l_cnt).Quantity_Per_Assembly := l_rec.COMPONENT_QUANTITY;--2;--v_QtyPerAssy;
--l_bom_component_tbl(l_cnt).Assembly_Item_id :=  '169531';
--FND_FILE.PUT_LINE(FND_FILE.LOG,'sss2' );
l_bom_component_tbl(l_cnt).Start_effective_date := nvl(to_date(l_rec.COMPONENT_FROM_DATE, 'dd-mm-rrrr hh24:mi:ss'),sysdate);--to_date(l_rec.COMPONENT_FROM_DATE, 'dd-mm-rrrr hh24:mi:ss');--to_date('04-DEC-2008 20:54:49', 'dd-MON-yyyy hh24:mi:ss');--j.EFFECTIVITY_DATE;----sysdate;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'sss2.1' );
l_bom_component_tbl(l_cnt).projected_yield := l_rec.COMPONENT_YIELD_FACTOR;--0.9620;
--l_bom_component_tbl(l_cnt).ENFORCE_INT_REQUIREMENTS := decode(l_rec.ENFORCE_INT_REQUIREMENTS,'None',0,'Up',1,'Down',2,0);--'1';
--l_bom_component_tbl(l_cnt).Include_In_Cost_Rollup := decode(l_rec.INCLUDE_IN_COST_ROLLUP,'Y',1,2);--2;
l_bom_component_tbl(l_cnt).Wip_Supply_Type := l_supply_type;--1;
l_bom_component_tbl(l_cnt).Supply_Subinventory := l_rec.SUPPLY_SUBINVENTORY;
l_bom_component_tbl(l_cnt).Location_Name := l_rec.LOCATOR;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'sss3' );
--l_bom_component_tbl(l_cnt).check_atp := decode(l_rec.CHECK_ATP,'Y',1,2);
--l_bom_component_tbl(l_cnt).Optional := decode(l_rec.OPTIONAL,'Y',1,2);
--l_bom_component_tbl(l_cnt).Mutually_Exclusive := decode(l_rec.MUTUALLY_EXCLUSIVE_OPTIONS,'Y',1,2);
l_bom_component_tbl(l_cnt).Minimum_Allowed_Quantity := l_rec.LOW_QUANTITY;
l_bom_component_tbl(l_cnt).Maximum_Allowed_Quantity := l_rec.HIGH_QUANTITY;
--l_bom_component_tbl(l_cnt).So_Basis := decode(l_rec.SO_BASIS,'Y',1,2);
--l_bom_component_tbl(l_cnt).Include_On_Ship_Docs := decode(l_rec.INCLUDE_ON_SHIP_DOCS,'Y',1,2);
--l_bom_component_tbl(l_cnt).Required_To_Ship := decode(l_rec.REQUIRED_TO_SHIP,'Y',1,2);
--l_bom_component_tbl(l_cnt).Required_For_Revenue := decode(l_rec.REQUIRED_FOR_REVENUE,'Y',1,2);
l_bom_component_tbl(l_cnt).Comments := l_rec.COMPONENT_REMARKS;
l_bom_component_tbl(l_cnt).return_status := NULL;
--FND_FILE.PUT_LINE(FND_FILE.LOG,'sss4' );
--l_bom_component_tbl(l_cnt).Component_Item_id := '23612';
/*l_bom_component_tbl(l_cnt).Alternate_bom_code := NULL;
l_bom_component_tbl(l_cnt).projected_yield := NULL;
l_bom_component_tbl(l_cnt).planning_percent := NULL;
l_bom_component_tbl(l_cnt).quantity_related := NULL;
l_bom_component_tbl(l_cnt).check_atp := NULL;
l_bom_component_tbl(l_cnt).Include_In_Cost_Rollup := NULL;*/
/*l_bom_component_tbl(l_cnt).So_Basis := NULL;
l_bom_component_tbl(l_cnt).Optional := NULL;
l_bom_component_tbl(l_cnt).Mutually_Exclusive := NULL;
l_bom_component_tbl(l_cnt).Shipping_Allowed := NULL;
l_bom_component_tbl(l_cnt).Required_To_Ship := NULL;
l_bom_component_tbl(l_cnt).Required_For_Revenue := NULL;
l_bom_component_tbl(l_cnt).Include_On_Ship_Docs := NULL;
l_bom_component_tbl(l_cnt).Supply_Subinventory := NULL;
l_bom_component_tbl(l_cnt).Location_Name := NULL;
l_bom_component_tbl(l_cnt).Minimum_Allowed_Quantity := NULL;
l_bom_component_tbl(l_cnt).Maximum_Allowed_Quantity := NULL;
l_bom_component_tbl(l_cnt).Comments := NULL;
l_bom_component_tbl(l_cnt).from_end_item_unit_number := NULL;
l_bom_component_tbl(l_cnt).to_end_item_unit_number := NULL;*/
--l_bom_component_tbl(l_cnt).Transaction_Type := 'CREATE';
--l_cnt := l_cnt +1;
--end loop;

--dbms_output.put_line(' start5');
Error_Handler.Initialize;
-- Call the Public API
-- The public API is the user's interface to the import program. The user must call it
-- programatically, while sending in one business object at a time. The public API returns
-- the processed business object, the business object status, and a count of all
-- associated error and warning messages.
--dbms_output.put_line(' start6 ');
FND_FILE.PUT_LINE(FND_FILE.LOG,'before bom_bo_pub.Process_Bom'  );
bom_bo_pub.Process_Bom
( p_bo_identifier => 'BOM'
, p_api_version_number => 1.0 -- This parameter is required. It is used by the
-- API to compare the version number of incoming
-- calls to its current version number.
, p_init_msg_list => TRUE -- This parameter is set to TRUE, allows callers to
-- to request that the API do the initialization
-- of message list on their behalf.
, p_bom_header_rec => l_bom_header_rec -- This is a set of data structures that represent
-- the incoming business objects. This is a record
-- that holds the Bill of Materials header for the
-- BOM
, p_bom_revision_tbl => l_bom_revision_tbl -- All the p*_tbl parameters are data structure
-- that represent incoming business objects They
-- are PL/SQL tables of records that hold records
-- for each of the other entities.
, 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 -- All the x*_tbl parameters are data structure
-- that represent outgoing business objects They
-- are PL/SQL tables of records that hold records
-- for each of the other entities except now they
-- have all the changes that the import program
-- made to it through all the steps.
, 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 -- This is a flag that indicates the state of the
-- whole business object after the import.
-- 'S' - Success
-- 'E' - Error
-- 'F' - Fatal Error
-- 'U' - Unexpected Error
, x_msg_count => l_x_msg_count -- This holds the number of messages in the API
-- message stack after the import.
, p_debug => 'N'
, p_output_dir => ''
, p_debug_filename => ''
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'after bom_bo_pub.Process_Bom'  );
FND_FILE.PUT_LINE(FND_FILE.LOG,'Return Status for '||l_rec.COMPONENT||' = '||l_x_return_status);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Message Count for '||l_rec.COMPONENT||' = '||l_x_msg_count);
/**** Error messages ****/
Error_Handler.Get_message_list(l_error_message_list);
if l_x_return_status <> 'S'
then
-- Error Processing
l_err_message := null;
l_err_message_type := null;
for k in 1..l_x_msg_count loop
if l_err_message is null then
l_err_message := SUBSTR(l_error_message_list(k).message_text,1,250);
else
l_err_message := l_err_message ||'~'||SUBSTR(l_error_message_list(k).message_text,1,250);
end if;
if l_err_message_type is null then
l_err_message_type := l_error_message_list(k).message_type;
else
l_err_message_type := l_err_message_type ||'~'|| l_error_message_list(k).message_type;
end if;
FND_FILE.PUT_LINE(FND_FILE.LOG,' MESSAGE TEXT '||SUBSTR(l_error_message_list(k).message_text,1,250));
FND_FILE.PUT_LINE(FND_FILE.LOG,' MESSAGE TYPE '||l_error_message_list(k).message_type);
end loop;
update GEPS_BOM_INV_COMPONENTS_STG
set PROCESS_FLAG ='E',
ERROR_MESSAGE =l_err_message,
ERROR_MESSAGE_TYPE = l_err_message_type
where COMPONENT = l_rec.COMPONENT
and ITEM = l_rec.ITEM
and ITEM_SEQ =l_rec.ITEM_SEQ
and OPERATION_SEQ =l_rec.OPERATION_SEQ;
commit;
else
update GEPS_BOM_INV_COMPONENTS_STG
set PROCESS_FLAG ='S'
where COMPONENT = l_rec.COMPONENT
and ITEM = l_rec.ITEM
and ITEM_SEQ =l_rec.ITEM_SEQ
and OPERATION_SEQ =l_rec.OPERATION_SEQ;
commit;
end if;
--i :=i+1;
--end if;
end loop;

EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,' OTHER exception ');
end GEPS_BOM_INV_COMP_PRG;
/