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;
/
No comments:
Post a Comment