oracle eco 开放接口,請教高手--ECO INTERFACE導入问题

最新推荐文章于 2022-10-28 16:15:12 发布

洗心岛

最新推荐文章于 2022-10-28 16:15:12 发布

阅读量560

点赞数

文章标签： oracle eco 开放接口

这段代码展示了在ERP系统中进行变更管理的一系列数据库操作，包括删除和插入记录到不同接口表中，涉及工程变更、修订项和组件变更等。随后，通过调用私有API来处理这些变更记录，并进行错误处理和验证。整个过程涵盖了变更审批、生效日期、组件信息等多个方面。

摘要由CSDN通过智能技术生成

-----Import data into interface

BEGIN

DELETE FROM eng_eng_changes_interface;

DELETE FROM eng_revised_items_interface;

DELETE FROM bom_inventory_comps_interface;

COMMIT;

END;

BEGIN

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, STATUS_NAME, TRANSACTION_TYPE,ECO_DEPARTMENT_NAME )

VALUES

('PPL','MPT6899973','99999','DOCUMENT','DOCUMENT','Approved',NULL,'Scheduled','CREATE','MPTS-PPL Site(PCE)' );

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,BILL_SEQUENCE_ID )

VALUES

('PPL','MPT6899973','99999','934060009823',NULL,'17-NOV-06','CREATE',2415397);

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

('PPL','MPT6899973','99999','934060009823',NULL,'17-NOV-06',1,1,

'344726100013','CREATE',2,10,35,NULL,NULL ) ;

COMMIT;

END;

------Call the API

DECLARE

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 Eng_Eco_Pub.Rev_Component_Tbl_Type;

l_rev_component_tbl Bom_Bo_Pub.Rev_Component_Tbl_Type;

--l_sub_component_tbl Eng_Eco_Pub.Sub_Component_Tbl_Type;

l_sub_component_tbl Bom_Bo_Pub.Sub_Component_Tbl_Type;

--l_ref_designator_tbl Eng_Eco_Pub.Ref_Designator_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 Eng_Eco_Pub.Error_Tbl_Type;

l_Error_Table Error_Handler.Error_Tbl_Type;

l_Message_text VARCHAR2(2000);

p_test_tag VARCHAR2(200);

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 :='99999';

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

l_eco_rec.status_NAME := eco_rec.status_NAME; --HARRY

-- l_eco_rec.eco_department_name := eco_rec.responsible_org_code;

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

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

l_eco_rec.requestor := eco_rec.requestor_USER_NAME; --HARRY

-- l_eco_rec.requestor := eco_rec.requestor_id; --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;

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;

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

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

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

Eng_Globals.G_WHO_REC.org_id := 207;

Eng_Globals.G_WHO_REC.user_id := 1007879;

Eng_Globals.G_WHO_REC.login_id := 2593599;

Eng_Globals.G_WHO_REC.prog_appid := 703;

Eng_Globals.G_WHO_REC.prog_id:= NULL;

Eng_Globals.G_WHO_REC.req_id := NULL;

--ENG_GLOBALS.system_information.org_id := 207; --HARRY

fnd_global.apps_initialize

( user_id => Eng_Globals.G_WHO_REC.user_id

, resp_id => 20567

, 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

);

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

Error_Handler.Get_Message_List( x_message_list => l_error_table );

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('Mesg:'||l_error_table(i).message_text);

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

END LOOP;

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

l_msg_count := Error_Handler.Get_Message_Count;

dbms_output.put_line('Message Count Function:'||to_char(l_msg_count));

Error_Handler.Dump_Message_List;