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