Oracle ERP ECO import customization

Import ECO component

1. /*********** Issue a ECO to Add Assembly a new component content with designator and substitute component. *********/
--- ECO Header
insert into ENG_ENG_CHANGES_INTERFACE(ORGANIZATION_CODE,CHANGE_NOTICE,ENG_CHANGES_IFCE_KEY,CHANGE_ORDER_TYPE,APPROVAL_STATUS_NAME,APPROVAL_LIST_NAME, STATUS_NAME,TRANSACTION_TYPE,ECO_DEPARTMENT_NAME,REASON_CODE,PLM_OR_ERP_CHANGE)
values('IM','AA','852','Finshed good','Approved',NULL,'Scheduled','CREATE',NULL,NULL,'ERP');

--- Assembly Primary record
INSERT INTO ENG_REVISED_ITEMS_INTERFACE(ORGANIZATION_CODE,CHANGE_NOTICE,ENG_REVISED_ITEMS_IFCE_KEY,REVISED_ITEM_NUMBER,NEW_ITEM_REVISION,SCHEDULED_DATE,TRANSACTION_TYPE)
values('IM','AA','852','TESTBOM','007',to_date('2007/04/14','YYYY/MM/DD'),'CREATE');

--- Assembly primary record specify component information
INSERT INTO BOM_INVENTORY_COMPS_INTERFACE(ORGANIZATION_CODE,CHANGE_NOTICE,BOM_INVENTORY_COMPS_IFCE_KEY,ASSEMBLY_ITEM_NUMBER,OLD_EFFECTIVITY_DATE,EFFECTIVITY_DATE,OLD_OPERATION_SEQ_NUM,COMPONENT_ITEM_NUMBER,OPERATION_SEQ_NUM,TRANSACTION_TYPE,ACD_TYPE,ITEM_NUM,COMPONENT_QUANTITY,REVISED_ITEM_NUMBER,NEW_REVISED_ITEM_REVISION)
values('IM','AA','852','TESTBOM',null,to_date('2007/04/14','YYYY/MM/DD'),40,'114-2514',40,'CREATE',1,200,1,'TESTBOM','007');

---- Specify Component designator location
insert into BOM_REF_DESGS_INTERFACE(ORGANIZATION_CODE,CHANGE_NOTICE,BOM_REF_DESGS_IFCE_KEY,ASSEMBLY_ITEM_NUMBER,NEW_REVISED_ITEM_REVISION,OPERATION_SEQ_NUM,EFFECTIVITY_DATE,COMPONENT_ITEM_NUMBER,TRANSACTION_TYPE,ACD_TYPE,COMPONENT_REFERENCE_DESIGNATOR)
values('IM','AA','852','TESTBOM','007',40,to_date('2007/04/14','YYYY/MM/DD'),'114-2514','CREATE',1,'ZZZ');

---- Specify substitute component information
insert into BOM_SUB_COMPS_INTERFACE(ORGANIZATION_CODE,CHANGE_NOTICE,BOM_SUB_COMPS_IFCE_KEY,ASSEMBLY_ITEM_NUMBER,NEW_REVISED_ITEM_REVISION,COMPONENT_ITEM_NUMBER,SUBSTITUTE_COMP_NUMBER,EFFECTIVITY_DATE,OPERATION_SEQ_NUM,ACD_TYPE,TRANSACTION_TYPE)
values('IM','AA','852','TESTBOM','007','114-2514','143-6185',to_date('2007/04/14','YYYY/MM/DD'),40,1,'CREATE')

commit;


2. ---- Create import function to process eco record.
function eco_import_api(P_identity_key varchar2)
return varchar2
as
l_eco_rec Eng_Eco_Pub.Eco_Rec_Type;
l_eco_revision_tbl Eng_Eco_Pub.Eco_Revision_Tbl_Type;
l_revised_item_tbl Eng_Eco_Pub.Revised_Item_Tbl_Type;
l_rev_component_tbl Bom_Bo_Pub.Rev_Component_Tbl_Type;
l_sub_component_tbl Bom_Bo_Pub.Sub_Component_Tbl_Type;
l_ref_designator_tbl Bom_Bo_Pub.Ref_Designator_Tbl_Type;
l_rev_operation_tbl Bom_Rtg_Pub.rev_operation_tbl_Type;
l_rev_op_resource_tbl Bom_Rtg_Pub.rev_op_resource_Tbl_Type;
l_rev_sub_resource_tbl Bom_Rtg_Pub.rev_sub_resource_Tbl_Type;
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_Error_Table Error_Handler.Error_Tbl_Type;
l_Message_text VARCHAR2(2000);
p_test_tag varchar2(100);
CURSOR c_eco_rec IS
SELECT *
FROM eng_eng_changes_interface
WHERE eng_changes_ifce_key like p_test_tag;
CURSOR c_eco_rev IS
SELECT *
FROM eng_eco_revisions_interface
WHERE eng_eco_revisions_ifce_key like p_test_tag;
CURSOR c_rev_items IS
SELECT *
FROM eng_revised_items_interface
WHERE eng_revised_items_ifce_key like p_test_tag;
CURSOR c_rev_comps IS
SELECT *
FROM bom_inventory_comps_interface
WHERE bom_inventory_comps_ifce_key like p_test_tag;
CURSOR c_sub_comps IS
SELECT *
FROM bom_sub_comps_interface
WHERE bom_sub_comps_ifce_key like p_test_tag;
CURSOR c_ref_desgs IS
SELECT *
FROM bom_ref_desgs_interface
WHERE bom_ref_desgs_ifce_key like p_test_tag;
i number;
BEGIN
-- Query all the records and call the Private API.
p_test_tag :=P_identity_key;
FOR eco_rec IN c_eco_rec
LOOP
l_eco_rec.eco_name := eco_rec.change_notice;
l_eco_rec.organization_code := eco_rec.organization_code;
l_eco_rec.change_type_code := eco_rec.change_order_type;
l_eco_rec.status_NAME := eco_rec.status_NAME; --HARRY
l_eco_rec.eco_department_name := eco_rec.eco_department_name;
l_eco_rec.priority_code := eco_rec.priority_code;
l_eco_rec.approval_list_name := eco_rec.approval_list_name;
l_eco_rec.approval_status_NAME := eco_rec.approval_status_NAME; --HARRY
l_eco_rec.reason_code := eco_rec.reason_code;
l_eco_rec.eng_implementation_cost := eco_rec.estimated_eng_cost;
l_eco_rec.mfg_implementation_cost := eco_rec.estimated_mfg_cost;
l_eco_rec.cancellation_comments:=eco_rec.cancellation_comments;
l_eco_rec.requestor := eco_rec.requestor_USER_NAME; --HARRY
l_eco_rec.description := eco_rec.description;
l_eco_rec.transaction_type := eco_rec.transaction_type;
l_eco_rec.PLM_OR_ERP_CHANGE := 'ERP';
END LOOP;
-- Fetch ECO Revisions
i := 1;
FOR rev IN c_eco_rev
LOOP
l_eco_revision_tbl(i).eco_name := rev.change_notice;
l_eco_revision_tbl(i).organization_code:= rev.organization_code;
l_eco_revision_tbl(i).revision := rev.revision;
l_eco_revision_tbl(i).new_revision := rev.new_revision;
l_eco_revision_tbl(i).comments := rev.comments;
l_eco_revision_tbl(i).transaction_type := rev.transaction_type;
i := i + 1;
END LOOP;
-- Fetch revised items
i := 1;
FOR ri IN c_rev_items
LOOP
l_revised_item_tbl(i).eco_name := ri.change_notice;
l_revised_item_tbl(i).organization_code := ri.organization_code;
l_revised_item_tbl(i).revised_item_name := ri.revised_item_number;
l_revised_item_tbl(i).New_Revised_Item_Rev_Desc:=ri.descriptive_text; -- By brian
l_revised_item_tbl(i).new_revised_item_revision := ri.new_item_revision;
l_revised_item_tbl(i).start_effective_date := ri.scheduled_date;
l_revised_item_tbl(i).alternate_bom_code := ri.alternate_bom_designator;
l_revised_item_tbl(i).status_type := ri.status_type;
l_revised_item_tbl(i).mrp_active := ri.mrp_active;
l_revised_item_tbl(i).earliest_effective_date := ri.early_schedule_date;
l_revised_item_tbl(i).use_up_item_name := ri.use_up_item_number;
l_revised_item_tbl(i).use_up_plan_name := ri.use_up_plan_name;
l_revised_item_tbl(i).disposition_type := ri.disposition_type;
l_revised_item_tbl(i).update_wip := ri.update_wip;
l_revised_item_tbl(i).cancel_comments := ri.cancel_comments;
-- l_revised_item_tbl(i).change_description := ri.descriptive_text; --By Brian
l_revised_item_tbl(i).transaction_type := ri.transaction_type;
i := i + 1;
END LOOP;
-- Fetch revised components
i := 1;
FOR rc IN c_rev_comps
LOOP
l_rev_component_tbl(i).eco_name := rc.change_notice;
l_rev_component_tbl(i).organization_code:= rc.organization_code;
l_rev_component_tbl(i).revised_item_name := rc.assembly_item_number;
l_rev_component_tbl(i).new_revised_item_revision := rc.NEW_REVISED_ITEM_REVISION;
l_rev_component_tbl(i).start_effective_date := rc.effectivity_date;
l_rev_component_tbl(i).disable_date := rc.disable_date;
l_rev_component_tbl(i).operation_sequence_number := rc.operation_seq_num;
l_rev_component_tbl(i).component_item_name := rc.component_item_number;
l_rev_component_tbl(i).alternate_bom_code := rc.alternate_bom_designator;
l_rev_component_tbl(i).acd_type := rc.acd_type;
l_rev_component_tbl(i).old_effectivity_date := rc.old_effectivity_date;
l_rev_component_tbl(i).old_operation_sequence_number := rc.old_operation_seq_num;
l_rev_component_tbl(i).item_sequence_number := rc.item_num;
l_rev_component_tbl(i).quantity_per_assembly := rc.component_quantity;
l_rev_component_tbl(i).planning_percent := rc.planning_factor;
l_rev_component_tbl(i).projected_yield := rc.component_yield_factor;
l_rev_component_tbl(i).include_in_cost_rollup :=rc.include_in_cost_rollup;
l_rev_component_tbl(i).wip_supply_type := rc.wip_supply_type;
l_rev_component_tbl(i).so_basis := rc.so_basis;
l_rev_component_tbl(i).optional := rc.optional;
l_rev_component_tbl(i).mutually_exclusive := rc.mutually_exclusive_options;
l_rev_component_tbl(i).check_atp := rc.check_atp;
l_rev_component_tbl(i).shipping_allowed := rc.shipping_allowed;
l_rev_component_tbl(i).required_to_ship := rc.required_to_ship;
l_rev_component_tbl(i).required_for_revenue := rc.required_for_revenue;
l_rev_component_tbl(i).include_on_ship_docs := rc.include_on_ship_docs;
l_rev_component_tbl(i).quantity_related := rc.quantity_related;
l_rev_component_tbl(i).supply_subinventory := rc.supply_subinventory;
l_rev_component_tbl(i).location_name := rc.location_name;
l_rev_component_tbl(i).minimum_allowed_quantity := rc.low_quantity;
l_rev_component_tbl(i).maximum_allowed_quantity := rc.high_quantity;
-- l_rev_component_tbl(i).component_remarks :=rc.component_remarks;
--rc.component_remarks; --HARRY
l_rev_component_tbl(i).comments := rc.component_remarks; --HARRY
l_rev_component_tbl(i).transaction_type := rc.transaction_type;
i := i + 1;
END LOOP;
-- Fetch substitute component records
i := 1;
FOR sc IN c_sub_comps
LOOP
l_sub_component_tbl(i).eco_name := sc.change_notice;
l_sub_component_tbl(i).organization_code:= sc.organization_code;
l_sub_component_tbl(i).revised_item_name := sc.assembly_item_number;
l_sub_component_tbl(i).start_effective_date := sc.effectivity_date;
l_sub_component_tbl(i).new_revised_item_revision := sc.NEW_REVISED_ITEM_REVISION; -- Add by Brian ;
l_sub_component_tbl(i).component_item_name := sc.component_item_number;
l_sub_component_tbl(i).alternate_bom_code := sc.alternate_bom_designator;
l_sub_component_tbl(i).substitute_component_name := sc.substitute_comp_number;
l_sub_component_tbl(i).acd_type := sc.acd_type;
l_sub_component_tbl(i).operation_sequence_number := sc.operation_seq_num;
l_sub_component_tbl(i).substitute_item_quantity := sc.substitute_item_quantity;
l_sub_component_tbl(i).transaction_type := sc.transaction_type;
i := i + 1;
END LOOP;
-- Fetch reference designators
i := 1;
FOR rd IN c_ref_desgs
LOOP
l_ref_designator_tbl(i).eco_name := rd.change_notice;
l_ref_designator_tbl(i).organization_code := rd.organization_code;
l_ref_designator_tbl(i).revised_item_name := rd.assembly_item_number;
l_ref_designator_tbl(i).start_effective_date := rd.effectivity_date;
-- l_ref_designator_tbl(i).new_revised_item_revision := null;
l_ref_designator_tbl(i).new_revised_item_revision := rd.NEW_REVISED_ITEM_REVISION; -- Add by Brian
l_ref_designator_tbl(i).operation_sequence_number := rd.operation_seq_num;
l_ref_designator_tbl(i).component_item_name := rd.component_item_number;
l_ref_designator_tbl(i).alternate_bom_code := rd.alternate_bom_designator;
l_ref_designator_tbl(i).reference_designator_name := rd.component_reference_designator;
l_ref_designator_tbl(i).acd_type := rd.acd_type;
l_ref_designator_tbl(i).ref_designator_comment := rd.ref_designator_comment;
l_ref_designator_tbl(i).new_reference_designator := rd.new_designator;
l_ref_designator_tbl(i).transaction_type := rd.transaction_type;
i := i + 1;
END LOOP;
Eng_Globals.G_WHO_REC.org_id := 103;
Eng_Globals.G_WHO_REC.user_id := 1115;
Eng_Globals.G_WHO_REC.login_id := -1;
Eng_Globals.G_WHO_REC.prog_appid := 703;
Eng_Globals.G_WHO_REC.prog_id:= NULL;
Eng_Globals.G_WHO_REC.req_id := NULL;
fnd_global.apps_initialize
( user_id => Eng_Globals.G_WHO_REC.user_id
, resp_id => 50250
, resp_appl_id => Eng_Globals.G_WHO_REC.prog_appid
);
-- dbms_output.put_line('Comes before process_eco call');
-- Call the private API
Eng_Eco_PUB.Process_Eco
( p_api_version_number => 1.0
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, p_eco_rec => l_eco_rec
, p_eco_revision_tbl => l_eco_revision_tbl
, p_revised_item_tbl => l_revised_item_tbl
, p_rev_component_tbl => l_rev_component_tbl
, p_sub_component_tbl => l_sub_component_tbl
, p_ref_designator_tbl => l_ref_designator_tbl
, x_eco_rec => l_eco_rec
, x_eco_revision_tbl => l_eco_revision_tbl
, x_revised_item_tbl => l_revised_item_tbl
, x_rev_component_tbl => l_rev_component_tbl
, x_sub_component_tbl => l_sub_component_tbl
, x_ref_designator_tbl => l_ref_designator_tbl
, x_rev_operation_tbl => l_rev_operation_tbl
, x_rev_op_resource_tbl => l_rev_op_resource_tbl
, x_rev_sub_resource_tbl => l_rev_sub_resource_tbl
);

COMMIT;
return '';
exception
when others then
return ' Import ECO exception' || SQLCODE || SQLERRM;
END eco_import_api;