Showing posts with label Engineering Change Orders. Show all posts
Showing posts with label Engineering Change Orders. Show all posts

Sunday, August 16, 2009

Single record insert script for Engineering Change Orders

DECLARE
p_eco_rec eng_eco_pub.eco_rec_type;
p_eco_revision_tbl eng_eco_pub.eco_revision_tbl_type;
p_revised_item_tbl eng_eco_pub.revised_item_tbl_type;
p_rev_component_tbl bom_bo_pub.rev_component_tbl_type;
p_ref_designator_tbl bom_bo_pub.ref_designator_tbl_type;
p_sub_component_tbl bom_bo_pub.sub_component_tbl_type;
x_return_status VARCHAR2 (10);
x_msg_count NUMBER;
x_message_list error_handler.error_tbl_type;
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_ref_designator_tbl bom_bo_pub.ref_designator_tbl_type;
x_sub_component_tbl bom_bo_pub.sub_component_tbl_type;
x_rev_operation_tbl bom_rtg_pub.rev_operation_tbl_type;
x_rev_op_resource_tbl bom_rtg_pub.rev_op_resource_tbl_type;
x_rev_sub_resource_tbl bom_rtg_pub.rev_sub_resource_tbl_type;
x_change_line_tbl eng_eco_pub.change_line_tbl_type;
l_change_line_tbl eng_eco_pub.change_line_tbl_type;
error_msg error_handler.error_tbl_type;
BEGIN
p_eco_rec.eco_name := 'ECO009';
p_eco_rec.organization_code := 'V1';
p_eco_rec.change_name := 'A Test ECO History';
p_eco_rec.description := 'A Test ECO History';
p_eco_rec.status_name := 'Scheduled';
p_eco_rec.approval_status_name := 'Approved';
p_eco_rec.approval_date := SYSDATE;
p_eco_rec.approval_request_date := SYSDATE;
p_eco_rec.change_type_code := 'ECO';
p_eco_rec.transaction_type := 'CREATE';
p_eco_rec.plm_or_erp_change := 'ERP';

-----ECO REVISIONS
IF p_eco_revision_tbl.EXISTS (1) IS NOT NULL
THEN
p_eco_revision_tbl.DELETE;
END IF;

p_eco_revision_tbl (1).eco_name := 'ECO009';
p_eco_revision_tbl (1).organization_code := 'V1';
p_eco_revision_tbl (1).revision := 'A';
p_eco_revision_tbl (1).transaction_type := 'CREATE';

-----REVISED ITEMS
IF p_revised_item_tbl.EXISTS (1) IS NOT NULL
THEN
p_revised_item_tbl.DELETE;
END IF;

p_revised_item_tbl (1).eco_name := 'ECO009';
p_revised_item_tbl (1).organization_code := 'V1';
p_revised_item_tbl (1).revised_item_name := 'ASO0012';
p_revised_item_tbl (1).start_effective_date := SYSDATE;
p_revised_item_tbl (1).new_effective_date := SYSDATE + 2;
p_revised_item_tbl (1).transaction_type := 'CREATE';

/* ----history
l_change_line_tbl (1).eco_name := 'ECO002';
l_change_line_tbl (1).scheduled_date := SYSDATE;
l_change_line_tbl (1).assignee_name := '60805 Open';
l_change_line_tbl (1).transaction_type := 'CREATE';
p_revised_item_tbl (1).reschedule_comments := 'Testing revised items';
*/ ---COMPONENT------------
IF p_rev_component_tbl.EXISTS (1) IS NOT NULL
THEN
p_rev_component_tbl.DELETE;
END IF;

p_rev_component_tbl (1).eco_name := 'ECO009';
p_rev_component_tbl (1).organization_code := 'V1';
p_rev_component_tbl (1).revised_item_name := 'ASO0012';
p_rev_component_tbl (1).start_effective_date := SYSDATE;
p_rev_component_tbl (1).operation_sequence_number := 1;
p_rev_component_tbl (1).component_item_name := '060 1918';
p_rev_component_tbl (1).acd_type := 1;
p_rev_component_tbl (1).old_operation_sequence_number := 1;
p_rev_component_tbl (1).new_operation_sequence_number := 1;
p_rev_component_tbl (1).transaction_type := 'CREATE';

-----REFERENCE DESIGNATOR-------------
IF p_ref_designator_tbl.EXISTS (1) IS NOT NULL
THEN
p_ref_designator_tbl.DELETE;
END IF;

p_ref_designator_tbl (1).eco_name := 'ECO009';
p_ref_designator_tbl (1).organization_code := 'V1';
p_ref_designator_tbl (1).revised_item_name := 'ASO0012';
p_ref_designator_tbl (1).start_effective_date := SYSDATE;
p_ref_designator_tbl (1).operation_sequence_number := 1;
p_ref_designator_tbl (1).component_item_name := '060 1918';
p_ref_designator_tbl (1).reference_designator_name := 'ECO009_dsg';
p_ref_designator_tbl (1).acd_type := 1;
p_ref_designator_tbl (1).transaction_type := 'CREATE';
----- SUBSTITUTE COMPONENT ----------------
IF p_sub_component_tbl.EXISTS (1) IS NOT NULL
THEN
p_sub_component_tbl.DELETE;
END IF;

p_sub_component_tbl (1).eco_name := 'ECO009';
p_sub_component_tbl (1).organization_code := 'V1';
p_sub_component_tbl (1).revised_item_name := 'ASO0012';
p_sub_component_tbl (1).start_effective_date := SYSDATE;
p_sub_component_tbl (1).operation_sequence_number := 1;
p_sub_component_tbl (1).component_item_name := '060 1918';
p_sub_component_tbl (1).substitute_component_name := '10-016-0063';
p_sub_component_tbl (1).new_substitute_component_name := '10-016-0063';
p_sub_component_tbl (1).acd_type := 1;
p_sub_component_tbl (1).transaction_type := 'CREATE';
p_sub_component_tbl (1).row_identifier := NULL;
--fnd_client_info.set_org_context (204);

fnd_global.apps_initialize (1007941, 20567, 703);
DBMS_OUTPUT.put_line ('Before API');


eng_eco_pub.process_eco (p_api_version_number => 1.0,
p_init_msg_list => FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
p_bo_identifier => 'ECO',
p_eco_rec => p_eco_rec,
p_eco_revision_tbl => p_eco_revision_tbl,
p_change_line_tbl => l_change_line_tbl,
p_revised_item_tbl => p_revised_item_tbl,
p_rev_component_tbl => p_rev_component_tbl,
p_ref_designator_tbl => p_ref_designator_tbl,
p_sub_component_tbl => p_sub_component_tbl,
p_rev_operation_tbl => x_rev_operation_tbl,
p_rev_op_resource_tbl => x_rev_op_resource_tbl,
p_rev_sub_resource_tbl => x_rev_sub_resource_tbl,
x_eco_rec => x_eco_rec,
x_eco_revision_tbl => x_eco_revision_tbl,
x_change_line_tbl => x_change_line_tbl,
x_revised_item_tbl => x_revised_item_tbl,
x_rev_component_tbl => x_rev_component_tbl,
x_ref_designator_tbl => x_ref_designator_tbl,
x_sub_component_tbl => x_sub_component_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,
p_debug => 'N',
p_output_dir => NULL,
p_debug_filename => 'ECO_BO_Debug.log'
);
DBMS_OUTPUT.put_line ('Return Status ' || x_return_status);

IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
error_handler.get_message_list (x_message_list);

FOR i IN 1 .. x_msg_count
LOOP
error_msg (i).MESSAGE_TEXT := x_message_list (i).MESSAGE_TEXT;
DBMS_OUTPUT.put_line (error_msg (i).MESSAGE_TEXT);
END LOOP;
END IF;

COMMIT;
END;
/

Base Tables and views involved:
ENG_ENGINEERING_CHANGES, ENG_CHANGE_ORDER_REVISIONS, ENG_REVISED_ITEMS, BOM_REFERENCE_DESIGNATORS, BOM_BILL_OF_MATERIALS, BOM_INVENTORY_COMPONENTS, BOM_SUBSTITUTE_COMPONENTS