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;
/

API for updating bom_inventory_components

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,
--bom.assembly_item_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 = 143
and  msi.segment1 in ('02995502')
and  msi1.segment1 in ('0RM00538');
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).Assembly_Item_id :=  '169531';
l_bom_component_tbl(i).Start_effective_date := j.EFFECTIVITY_DATE;--to_date('04-DEC-2008 20:54:49', 'dd-MON-yyyy hh24:mi:ss');--sysdate;
l_bom_component_tbl(i).Component_Item_Name := j.COMPONENT;
--l_bom_component_tbl(i).Component_Item_id := '23612';
/*l_bom_component_tbl(i).Alternate_bom_code := NULL;
l_bom_component_tbl(i).projected_yield := NULL;
l_bom_component_tbl(i).planning_percent := NULL;
l_bom_component_tbl(i).quantity_related := NULL;
l_bom_component_tbl(i).check_atp := NULL;
l_bom_component_tbl(i).Include_In_Cost_Rollup := NULL;*/
l_bom_component_tbl(i).Wip_Supply_Type := 2;
/*l_bom_component_tbl(i).So_Basis := NULL;
l_bom_component_tbl(i).Optional := NULL;
l_bom_component_tbl(i).Mutually_Exclusive := NULL;
l_bom_component_tbl(i).Shipping_Allowed := NULL;
l_bom_component_tbl(i).Required_To_Ship := NULL;
l_bom_component_tbl(i).Required_For_Revenue := NULL;
l_bom_component_tbl(i).Include_On_Ship_Docs := NULL;
l_bom_component_tbl(i).Supply_Subinventory := NULL;
l_bom_component_tbl(i).Location_Name := NULL;
l_bom_component_tbl(i).Minimum_Allowed_Quantity := NULL;
l_bom_component_tbl(i).Maximum_Allowed_Quantity := NULL;
l_bom_component_tbl(i).Comments := NULL;
l_bom_component_tbl(i).from_end_item_unit_number := NULL;
l_bom_component_tbl(i).to_end_item_unit_number := NULL;*/
l_bom_component_tbl(i).Item_Sequence_Number := j.ITEM_NUM;--(i*10);
l_bom_component_tbl(i).operation_Sequence_Number := j.OPERATION_SEQ_NUM;
--l_bom_component_tbl(i).Transaction_Type := 'CREATE';
--l_bom_component_tbl(i).Quantity_Per_Assembly := 1;--v_QtyPerAssy;
l_bom_component_tbl(i).return_status := NULL;
--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 ');
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 => ''
);
--dbms_output.put_line(' start7 ');
dbms_output.put_line('Return Status = '||l_x_return_status);
dbms_output.put_line('Message Count = '||l_x_msg_count);
/**** Error messages ****/
Error_Handler.Get_message_list(l_error_message_list);
if l_x_return_status <> 'S'
then
-- Error Processing
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;
-- The business object APIs do not issue commits or rollbacks. It is the responsibility of
-- the calling code to issue them. This ensures that parts of the transactions are not left
-- in the database. If an error occurs, the whole transaction is rolled back.
rollback;
else
commit;
end if;
--i :=i+1;
--end if;
end loop;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(' OTHER exception ');
end;
/

link between bom_bill_of_materials and bom_inventory_components

select  distinct ood.organization_code,
--ood.organization_id,
--bom.assembly_item_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
--bic.*
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 bic.wip_supply_type =4
--and nvl(bic.disable_date,sysdate) >= sysdate
and ml.lookup_type(+) = 'WIP_SUPPLY'
and ood.organization_id = 143
--and  msi.segment1 in ('08140070')
and  msi1.segment1 in ('01000013');

Query to findout list of components for manufactured Bill of material

select  distinct ood.organization_code,
--ood.organization_id,
--bom.assembly_item_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
--bic.*
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 bic.wip_supply_type =4
--and nvl(bic.disable_date,sysdate) >= sysdate
and ml.lookup_type(+) = 'WIP_SUPPLY'
and ood.organization_id = 143
and  msi.segment1 in ('08140070');
--and  msi1.segment1 in ('01000013');

How to make the Oracle report concurrent program ends with Error or Warning

In 'after report' trigger, use below piece of code:
---------for Error
function AfterReport return boolean is
  CNC_RET BOOLEAN;
  begin
begin
srw.USER_EXIT ('FND SRWEXIT');
IF :P_FUNCT_DTL  = 1
THEN
CNC_RET := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','Error Message ');
return (CNC_RET);
END IF;    
return (TRUE);
end; 
  return (TRUE);
end;

--------for warning
function AfterReport return boolean is
  CNC_RET BOOLEAN;
  begin
begin
srw.USER_EXIT ('FND SRWEXIT');
IF :P_FUNCT_DTL  = 1
THEN
CNC_RET := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',' Warning Message');
return (CNC_RET);
END IF;    
return (TRUE);
end; 
  return (TRUE);
end;

how to find out the list of responsibilities assign to users

SELECT   fu.user_id,
           fu.user_name,
           fur.responsibility_id,
           fr.responsibility_name
    FROM   fnd_user fu, fnd_user_resp_groups fur, fnd_responsibility_vl fr
   WHERE       fu.user_id = fur.user_id
           AND fr.application_id = fur.responsibility_application_id
           AND fr.responsibility_id = fur.responsibility_id
           AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date)
                                   AND  TRUNC (
                                           NVL ( (fr.end_date - 1), SYSDATE)
                                        )
           AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
                                   AND  TRUNC (
                                           NVL ( (fur.end_date - 1), SYSDATE)
                                        )
--and user_name ='502135771'--- for all user or for perticular user
--AND fr.RESPONSIBILITY_NAME ='System Administrator'
ORDER BY   user_name