oracle eco 开放接口,请教API来做ECO变更的例子，感谢！！

我是参考以下例子做的，发贴者说可以，但我试却没有效果。

例子的链接：ECO interface sample 包括create,change,disable

我的系统是EBS R12，研究了好久，无果。

好像执行：Eng_Eco_PUB.Process_Eco这个API都没起作用，发贴者的是11i版的，应该问题不大吧。

有没有人做过同样的例子成功的，可否共享一下，或指点一下问题点呢！！先谢过了！

1) insert into interface tables

2) Call api to import eco

3) Check eco from engineering module

---------------------------Insert into interface tables---------------------------------

declare

v_eco varchar2(10) := 'ECOT01';

begin

delete from eng_eng_changes_interface;

delete from eng_revised_items_interface;

delete from bom_inventory_comps_interface;

insert into eng_eng_changes_interface

(organization_code,

change_notice,

eng_changes_ifce_key,

change_order_type,

reason_code,

approval_status_name,

approval_list_name,

transaction_type,

eco_department_name,

description)

VALUES

('org_code',

v_eco,

'12345',

'Modify',

null,

'Approved',

NULL,

'CREATE',

null,

'Test ECO interface');

insert into eng_revised_items_interface

(organization_code,

change_notice,

eng_revised_items_ifce_key,

assembly_item_number,

revised_item_number,

new_item_revision,

scheduled_date,

transaction_type,

bill_sequence_id)

VALUES

('org_code',

v_eco,

'12345',

'parent_item',

'parent_item',

NULL,

to_date('11/26/2008','MM/DD/YYYY'),

'CREATE',

36993);

------------------------------------------------------------------------------

-- adding

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,

OPERATION_SEQ_NUM,

COMPONENT_ITEM_NUMBER,

TRANSACTION_TYPE,

ACD_TYPE,

ITEM_NUM,

COMPONENT_QUANTITY,

BILL_SEQUENCE_ID,

COMPONENT_SEQUENCE_ID)

VALUES

('org_code',

v_eco,

'12345',

'parent_item',

NULL,

to_date('11/26/2008','MM/DD/YYYY'),

1,

1,

'added_item',

'CREATE',

1,   --acd_type 1-Add,2-Change,3-Disable

30,

35,

NULL,

NULL);

--Change

insert into bom_inventory_comps_interface

(organization_code,

change_notice,

bom_inventory_comps_ifce_key,

assembly_item_number,

effectivity_date,

operation_seq_num,

component_item_number,

transaction_type,

acd_type,

item_num,

component_quantity,

old_effectivity_date,

old_operation_seq_num

--Get this using sql:

--select c.eng_item_flag,c.segment1 as parent_item,d.segment1 as child_item,

--       b.component_quantity as qty_per,

--           b.component_yield_factor,e.meaning,b.component_item_id,b.operation_seq_num,

--           to_char(b.effectivity_date,'dd/mm/yyyy hh24:mi:ss') Old_Effectivity_Date,

--           b.operation_seq_num OLD_OPERATION_SEQ_NUM

--from bom_bill_of_materials_v a,bom_inventory_components b,

--     mtl_system_items_b c,mtl_system_items_b d,fnd_lookup_values_vl e

--where c.segment1 = 'parent_item'

--  and a.bill_sequence_id = b.bill_sequence_id

--  and a.assembly_item_id = c.inventory_item_id

--  and a.organization_id = c.organization_id

--  and c.organization_id = v_organization_id

--  and b.component_item_id = d.inventory_item_id

--  and d.organization_id = v_organization_id

--  and e.lookup_type = 'WIP_SUPPLY'

--  and e.lookup_code = b.wip_supply_type

--  and d.segment1 = 'changed/diabled item'

--  and b.disable_date is null

)

values

('org_code',

v_eco,

'12345',

'parent_code',

to_date('11/26/2008','MM/DD/YYYY'),

1,

'changed_item',

'CREATE',

2,    --acd_type 1-Add,2-Change,3-Disable

20,

1.2,

to_date('03/12/2007 10:15:07','dd/mm/yyyy hh24:mi:ss'),

1

);

--Disable

insert into bom_inventory_comps_interface

(organization_code,

change_notice,

bom_inventory_comps_ifce_key,

assembly_item_number,

effectivity_date,

operation_seq_num,

component_item_number,

transaction_type,

acd_type,

item_num,

disable_date,

old_effectivity_date,

old_operation_seq_num

)

values

('org_code',

v_eco,

'12345',

'parent_code',

to_date('11/26/2008','MM/DD/YYYY'),

1,

'disabled_item',

'CREATE',

3,    --acd_type 1-Add,2-Change,3-Disable

10,

to_date('11/26/2008','MM/DD/YYYY'),

to_date('03/12/2007 10:15:07','dd/mm/yyyy hh24:mi:ss'),

1

);

commit;

end;

-------------------------------2) Call api to import eco---------------------------

declare

p_test_tag   VARCHAR2(10) := 'ECOT01';

p_user_id    number := v_user_id;

p_resp_id    number := v_responsibily_id;

p_prog_appid number := 703;

p_return_status VARCHAR2(500);

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_return_status         VARCHAR2(1);

l_msg_count             NUMBER;

l_msg_data              VARCHAR2(2000);

l_Error_Table           Error_Handler.Error_Tbl_Type;

x_Error_Table           Error_Handler.Error_Tbl_Type;

l_Message_text          VARCHAR2(2000);

x_eco_rec               Eng_Eco_Pub.Eco_Rec_Type;

x_eco_revision_tbl      Eng_Eco_Pub.Eco_Revision_Tbl_Type;

x_revised_item_tbl      Eng_Eco_Pub.Revised_Item_Tbl_Type;

x_rev_component_tbl     Bom_Bo_Pub.Rev_Component_Tbl_Type;

x_sub_component_tbl     Bom_Bo_Pub.Sub_Component_Tbl_Type;

x_ref_designator_tbl    Bom_Bo_Pub.Ref_Designator_Tbl_Type;

l_rev_operation_tbl     Bom_Rtg_Pub.Rev_Operation_Tbl_Type;        --RSC (for 11.5.8)

l_rev_op_resource_tbl   Bom_Rtg_Pub.Rev_Op_Resource_Tbl_Type;        --RSC (for 11.5.8)

l_rev_sub_resource_tbl  Bom_Rtg_Pub.Rev_Sub_Resource_Tbl_Type;        --RSC (for 11.5.8)

x_rev_operation_tbl     Bom_Rtg_Pub.Rev_Operation_Tbl_Type;        --RSC (for 11.5.8)

x_rev_op_resource_tbl   Bom_Rtg_Pub.Rev_Op_Resource_Tbl_Type;        --RSC (for 11.5.8)

x_rev_sub_resource_tbl  Bom_Rtg_Pub.Rev_Sub_Resource_Tbl_Type;        --RSC (for 11.5.8)

CURSOR c_eco_rec IS SELECT * FROM eng_eng_changes_interface  WHERE change_notice like p_test_tag;

CURSOR c_eco_rev IS SELECT * FROM eng_eco_revisions_interface WHERE change_notice like p_test_tag;

CURSOR c_rev_items IS SELECT * FROM eng_revised_items_interface WHERE change_notice like p_test_tag;

CURSOR c_rev_comps IS SELECT * FROM bom_inventory_comps_interface WHERE change_notice like p_test_tag;

CURSOR c_sub_comps IS SELECT * FROM bom_sub_comps_interface WHERE change_notice like p_test_tag;

CURSOR c_ref_desgs IS SELECT * FROM bom_ref_desgs_interface WHERE change_notice like p_test_tag;

i      number;

BEGIN

-- Query all the records and call the Private API.

----------------------------------- Fetch ECO Header ------------------------------------

l_eco_rec := x_eco_rec; --l_eco_rec initialize

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_type := eco_rec.status_type;

l_eco_rec.status_name := eco_rec.status_name;

l_eco_rec.approval_list_name := eco_rec.approval_list_name;

l_eco_rec.eco_department_name :=  eco_rec.responsible_org_code;

l_eco_rec.priority_code := eco_rec.priority_code;

l_eco_rec.approval_date := eco_rec.approval_date;

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;

l_eco_rec.description := eco_rec.description;

l_eco_rec.transaction_type := eco_rec.transaction_type;

l_eco_rec.organization_hierarchy := eco_rec.organization_hierarchy;

--   l_eco_rec.plm_or_erp_change := eco_rec.plm_or_erp_change;

END LOOP;

dbms_output.put_line('Found ENG_ENG_CHANGES_INTERFACE RECORD WITH CHANGE_NOTICE:  '||p_test_tag);

----------------------------------- Fetch ECO Revisions----------------------------------

i := 0;

FOR rev IN c_eco_rev

LOOP

i := i + 1;

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_sub_component_tbl(i).transaction_type := rev.transaction_type;

END LOOP;

dbms_output.put_line('Found ' || i || ' ECO_REV RECORDS FOR CHANGE_NOTICE:  '||p_test_tag);

----------------------------------- Fetch Revised Items ---------------------------------

l_revised_item_tbl.delete;

i := 0;

FOR ri IN c_rev_items

LOOP

i := i + 1;

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;

IF ri.new_item_revision = FND_API.G_MISS_CHAR THEN

l_revised_item_tbl(i).new_revised_item_revision := NULL;

ELSE

l_revised_item_tbl(i).new_revised_item_revision := ri.new_item_revision;

END IF;

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;

l_revised_item_tbl(i).transaction_type := ri.transaction_type;

END LOOP;

dbms_output.put_line('Found ' || i || ' RI RECORDS FOR CHANGE_NOTICE:  '||p_test_tag);

----------------------------------- Fetch Revised Components ----------------------------

i := 0;

FOR rc IN c_rev_comps

LOOP

i := i + 1;

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).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 := NULL;

l_rev_component_tbl(i).start_effective_date := rc.effectivity_date;

--l_rev_component_tbl(i).new_effective_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).item_sequence_number := rc.item_num;

l_rev_component_tbl(i).disable_date := rc.disable_date;

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;

l_rev_component_tbl(i).transaction_type := rc.transaction_type;

dbms_output.put_line('start_effective_date='||to_char(l_rev_component_tbl(i).start_effective_date,'DD-MON-YYYY HH24:MI:SS'));

END LOOP;

dbms_output.put_line('Found ' || i || ' RC RECORDS FOR CHANGE_NOTICE:  '||p_test_tag);

----------------------------------- Fetch Substitute Component --------------------------

i := 0;

FOR sc IN c_sub_comps

LOOP

i := i + 1;

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 := NULL;

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;

END LOOP;

dbms_output.put_line('Found ' || i || ' SC RECORDS FOR CHANGE_NOTICE:  '||p_test_tag);

----------------------------------- Fetch Reference Designators -------------------------

i := 0;

FOR rd IN c_ref_desgs

LOOP

i := i + 1;

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).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;

END LOOP;

dbms_output.put_line('Found ' || i || ' RD RECORDS FOR CHANGE_NOTICE:  '||p_test_tag);

----------------------------------- Initialize ------------------------------------------

Eng_Globals.G_WHO_REC.user_id := p_user_id;

Eng_Globals.G_WHO_REC.prog_appid := p_prog_appid;

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      => p_resp_id

, resp_appl_id => Eng_Globals.G_WHO_REC.prog_appid           );

----------------------------------- Call API --------------------------------------------

-- Initialize the error handling table

Error_Handler.initialize;

Eng_Eco_PUB.Process_Eco(p_api_version_number         => 1.0

,p_init_msg_list => FALSE

,x_return_status              => l_return_status

,x_msg_count                  => l_msg_count

,p_bo_identifier              => 'ECO'

,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

,p_rev_operation_tbl          => l_rev_operation_tbl

,p_rev_op_resource_tbl        => l_rev_op_resource_tbl

,p_rev_sub_resource_tbl       => l_rev_sub_resource_tbl

,x_rev_operation_tbl          => x_rev_operation_tbl

,x_rev_op_resource_tbl        => x_rev_op_resource_tbl

,x_rev_sub_resource_tbl       => x_rev_sub_resource_tbl

,x_eco_rec                    => x_eco_rec

,x_eco_revision_tbl           => x_eco_revision_tbl

,x_revised_item_tbl           => x_revised_item_tbl

,x_rev_component_tbl          => x_rev_component_tbl

,x_sub_component_tbl          => x_sub_component_tbl

,x_ref_designator_tbl         => x_ref_designator_tbl

,p_debug                      => 'Y'

,p_output_dir                 => '/usr/tmp'

,p_debug_filename             => 'ECO_BO_DEBUG.log'

);

--

-- On return from the PUB API

-- Perform all the error handler operations to verify that the

-- error or warning are displayed and all the error table interface

-- function provided to the user work corrently;

--

IF (l_return_status = 'S') THEN

dbms_output.put_line('Import SUCCESS!!');

commit;

ELSE

l_error_table.DELETE;

Error_Handler.Get_Message_List( x_message_list  => l_error_table);

--   dbms_output.put_line('aa' || to_char(l_error_table.COUNT));

FOR i IN 1..l_error_table.COUNT

LOOP

dbms_output.put_line('Entity Id:  '||l_error_table(i).entity_id);

dbms_output.put_line('Index    :  '||l_error_table(i).entity_index);

dbms_output.put_line('Table Name  '||l_error_table(i).table_name);

dbms_output.put_line('Mesg     :  '||l_error_table(i).message_text);

dbms_output.put_line('row_identifier  '||l_error_table(i).row_identifier);

--dbms_output.put_line('ne');

dbms_output.put_line('---------------------------------------');

END LOOP;

dbms_output.put_line('Total Messages: ' || to_char(i));

END IF ;

p_return_status := l_return_status;

END ;