These are the mandatory columns in order management in both headers and lines
OE_headers_iface_all :
Order Headers OE_ORDER_HEADERS_ALL HEADER_ID
ORG_ID
ORDER_NUMBER
ORDER_TYPE_ID OE_TRNSACTION_TYPES_ALL.TRANSACTION_TYPE_ID
ORDER_SOURCE_ID Order Source from OE_ORDER_SOURCES
ORIG_SYS_DOCUMENT_REF Quote Number.Version for Sales & Mkting Quote
OE_ORDER_HEADERS_ALL<HEADER_ID> for OM
SOURCE_DOCUMENT_ID Order Header ID from which order is Copied
PRICE_LIST_ID OE_PRICE_LISTS.PRICE_LIST_ID
ORDERED_DATE
REQUEST_DATE
BOOKED_DATE
TRANSACTIONAL_CURR_CODE
CUSTOMER_PO_NUMBER Mandatory id specified in Transaction Type
PAYMENT_TERM_ID RA_TERMS.TERM_ID
SOLD_TO_ORG_ID CUSTOMER_ID or HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID
SHIP_TO_ORG_ID HZ_CUST_SITE_USES_ALL.SITE_USE_ID
INVOICE_TO_ORG_ID HZ_CUST_SITE_USES_ALL.SITE_USE_ID
CANCELLED_FLAG
OPEN_FLAG
BOOKED_FLAG
SALESREP_ID JTF_RS_SALESREPS.SALESREP_ID
ORDER_CATEGORY_CODE ORDER, RETURN, MIXED
FLOW_STATUS_CODE ENTERED, BOOKED, CLOSED, CANCELLED
Oe_lines_iface_all :
LINE_ID
ORIG_SYS_DOCUMENT_REF OE_ORDER_HEADERS_ALL50479
SOURCE_DOCUMENT_ID 50462
ORIG_SYS_LINE_REF OE_ORDER_LINES_ALL59908
SOURCE_DOCUMENT_LINE_ID 59893
HEADER_ID
LINE_TYPE_ID OE_TRNSACTION_TYPES_ALL.TRANSACTION_TYPE_ID
LINE_NUMBER
ORDERED_ITEM MTL_SYSTEM_ITEMS.SEGMENT1
REQUEST_DATE
PROMISE_DATE
SCHEDULE_SHIP_DATE
CANCELLED_QUANTITY
SHIPPED_QUANTITY
ORDERED_QUANTITY
INVOICED_QUANITITY
SHIP_FROM_ORG_ID WAREHOUSE_ID or INVENTORY ORGANIZATION ID
SHIP_TO_ORG_ID HZ_CUST_SITE_USES_ALL.SITE_USE_ID
INVOICE_TO_ORG_ID HZ_CUST_SITE_USES_ALL.SITE_USE_ID
SOLD_TO_ORG_ID CUSTOMER_ID or HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID
PRICE_LIST_ID QP_LIST_HEADERS_B.PRICE_LIST_ID
INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID
PAYMENT_TERM_ID RA_TERMS.TERM_ID
UNIT_SELLING_PRICE
UNIT_LIST_PRICE OE_PRICE_LIST_LINES
TAX_VALUE
VISIBLE_DEMAND_FLAG
LINE_CATEGORY_CODE ORDER, RETURN
SOURCE_TYPE_CODE INTERNAL, EXTERNAL
CANCELLED_FLAG
OPEN_FLAG
BOOKED_FLAG
DROP_SHIP_FLAG
SALESREP_ID JTF_RS_SALESREPS.SALESREP_ID
ORDER_SOURCE_ID
FLOW_STATUS_CODE
SPLIT_FROM_LINE_ID
now create your own package and procedure :
--package specification
CREATE OR REPLACE PACKAGE om_so_pkg
AS
PROCEDURE om_so_proc (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
IS
END om_so_pkg;
/
--package and procedure specification
/* Formatted on 2011/07/07 16:17 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PACKAGE BODY om_so_pkg
AS
PROCEDURE om_so_proc (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
IS
TYPE om_h_l_tbls IS TABLE OF VARCHAR2 (3000)
INDEX BY BINARY_INTEGER;
------------------DEFINING HEADERS STAGING TABLES COLUMNS
v_orig_sys_document_ref om_h_l_tbls;
v_order_source_id om_h_l_tbls;
v_order_type_id om_h_l_tbls;
v_order_type om_h_l_tbls;
v_order_number om_h_l_tbls;
v_payment_term_id om_h_l_tbls;
v_price_list om_h_l_tbls;
v_customer_name om_h_l_tbls;
v_operation_code om_h_l_tbls;
v_booked_flag om_h_l_tbls;
v_created_by om_h_l_tbls;
v_creation_date om_h_l_tbls;
v_customer_po_number om_h_l_tbls;
v_invoice_to_org_id om_h_l_tbls;
v_last_update_date om_h_l_tbls;
v_last_update_login om_h_l_tbls;
v_last_updated_by om_h_l_tbls;
v_order_category om_h_l_tbls;
v_ordered_date om_h_l_tbls;
v_request_date om_h_l_tbls;
v_ship_from_org_id om_h_l_tbls;
v_ship_to_org_id om_h_l_tbls;
v_sold_to_org_id om_h_l_tbls;
v_transactional_curr_code om_h_l_tbls;
----------------END OF HEADERS STAGING TABLES COLUMNS
-----------------DEFINING LINES STAGING TABLES COLUMNS
v_order_source om_h_l_tbls;
v_orig_sys_document om_h_l_tbls;
v_orig_sys_line om_h_l_tbls;
v_inventory_item om_h_l_tbls;
v_request om_h_l_tbls;
v_promise om_h_l_tbls;
v_ordered_quantity om_h_l_tbls;
v_invoice_to_org om_h_l_tbls;
v_ship_from_org om_h_l_tbls;
v_ship_to om_h_l_tbls;
v_sold_to om_h_l_tbls;
v_unit_list_price om_h_l_tbls;
v_unit_selling_price om_h_l_tbls;
v_payment_term om_h_l_tbls;
v_created om_h_l_tbls;
v_creation om_h_l_tbls;
v_last_updated om_h_l_tbls;
v_last_update om_h_l_tbls;
v_last_update_log om_h_l_tbls;
v_calculate_price_flag om_h_l_tbls;
v_context om_h_l_tbls;
v_line_type om_h_l_tbls;
v_operation om_h_l_tbls;
-----------------END OF LINES STATING TABLES COLUMNS
------------------DECLARE THE CURSOR FOR HEADERS
CURSOR cur_so_headers
IS
SELECT xtg1.orig_sys_document_ref, xtg1.order_source_id,
xtg1.order_type_id, xtg1.order_type, xtg1.order_number,
xtg1.payment_term_id, xtg1.price_list, xtg1.customer_name,
xtg1.operation_code, xtg1.booked_flag, xtg1.created_by,
xtg1.creation_date, xtg1.customer_po_number,
xtg1.invoice_to_org_id, xtg1.last_update_date,
xtg1.last_update_login, xtg1.last_updated_by,
xtg1.order_category, xtg1.ordered_date, xtg1.request_date,
xtg1.ship_from_org_id, xtg1.ship_to_org_id,
xtg1.sold_to_org_id, xtg1.transactional_curr_code
FROM xtg_oe_order_headers_all xtg1;
--------------------DECLARE THE CURSOR FOR LINES
CURSOR cur_so_lines
IS
SELECT xtg2.order_source_id, xtg2.orig_sys_document_ref,
xtg2.orig_sys_line_ref, xtg2.inventory_item_id,
xtg2.request_date, xtg2.promise_date, xtg2.ordered_quantity,
xtg2.invoice_to_org_id, xtg2.ship_from_org_id,
xtg2.ship_to_org_id, xtg2.sold_to_org_id,
xtg2.unit_list_price, xtg2.unit_selling_price,
xtg2.payment_term_id, xtg2.created_by, xtg2.creation_date,
xtg2.last_updated_by, xtg2.last_update_date,
xtg2.last_update_login, xtg2.calculate_price_flag,
xtg2.CONTEXT, xtg2.line_type_id, xtg2.operation_code
FROM xtg_oe_order_lines_all xtg2;
BEGIN
---------------opening cusror for headers data
OPEN cur_so_headers;
FETCH cur_so_headers
BULK COLLECT INTO v_orig_sys_document_ref, v_order_source_id,
v_order_type_id, v_order_type, v_order_number,
v_payment_term_id, v_price_list, v_customer_name,
v_operation_code, v_booked_flag, v_created_by, v_creation_date,
v_customer_po_number, v_invoice_to_org_id, v_last_update_date,
v_last_update_login, v_last_updated_by, v_order_category,
v_ordered_date, v_request_date, v_ship_from_org_id,
v_ship_to_org_id, v_sold_to_org_id, v_transactional_curr_code LIMIT 1000;
FOR cur_so_h IN 1 .. v_orig_sys_document_ref.COUNT
LOOP
INSERT INTO oe_headers_iface_all
(orig_sys_document_ref,
order_source_id,
order_type_id, order_type,
order_number,
payment_term_id, price_list,
customer_name,
operation_code, booked_flag,
created_by, creation_date,
customer_po_number,
invoice_to_org_id, last_update_date,
last_update_login, last_updated_by,
order_category,
ordered_date, request_date,
ship_from_org_id,
ship_to_org_id,
sold_to_org_id,
transactional_curr_code
)
VALUES (v_orig_sys_document_ref (cur_so_h),
v_order_source_id (cur_so_h),
v_order_type_id (cur_so_h), v_order_type (cur_so_h),
v_order_number (cur_so_h),
v_payment_term_id (cur_so_h), v_price_list (cur_so_h),
v_customer_name (cur_so_h),
v_operation_code (cur_so_h), v_booked_flag (cur_so_h),
fnd_global.user_id, SYSDATE,
v_customer_po_number (cur_so_h),
v_invoice_to_org_id (cur_so_h), SYSDATE,
fnd_global.user_id, fnd_global.user_id,
v_order_category (cur_so_h),
v_ordered_date (cur_so_h), v_request_date (cur_so_h),
v_ship_from_org_id (cur_so_h),
v_ship_to_org_id (cur_so_h),
v_sold_to_org_id (cur_so_h),
v_transactional_curr_code (cur_so_h)
);
END LOOP;
fnd_file.put_line (fnd_file.LOG, 'HEADERS DATA INSERTED');
CLOSE cur_so_headers;
----------------end of headers data
---------opening cursor for lines data
OPEN cur_so_lines;
FETCH cur_so_lines
BULK COLLECT INTO v_order_source, v_orig_sys_document, v_orig_sys_line,
v_inventory_item, v_request, v_promise, v_ordered_quantity,
v_invoice_to_org, v_ship_from_org, v_ship_to, v_sold_to,
v_unit_list_price, v_unit_selling_price, v_payment_term,
v_created, v_creation, v_last_updated, v_last_update,
v_last_update_log, v_calculate_price_flag, v_context,
v_line_type, v_operation LIMIT 1000;
FOR cur_so_l IN 1 .. v_order_source_id.COUNT
LOOP
INSERT INTO oe_lines_iface_all
(order_source_id,
orig_sys_document_ref,
orig_sys_line_ref,
inventory_item_id, request_date,
promise_date, ordered_quantity,
invoice_to_org_id,
ship_from_org_id, ship_to_org_id,
sold_to_org_id, unit_list_price,
unit_selling_price,
payment_term_id, created_by,
creation_date, last_updated_by, last_update_date,
last_update_login, calculate_price_flag,
CONTEXT, line_type_id,
operation_code
)
VALUES (v_order_source (cur_so_l),
v_orig_sys_document (cur_so_l),
v_orig_sys_line (cur_so_l),
v_inventory_item (cur_so_l), v_request (cur_so_l),
v_promise (cur_so_l), v_ordered_quantity (cur_so_l),
v_invoice_to_org (cur_so_l),
v_ship_from_org (cur_so_l), v_ship_to (cur_so_l),
v_sold_to (cur_so_l), v_unit_list_price (cur_so_l),
v_unit_selling_price (cur_so_l),
v_payment_term (cur_so_l), fnd_global.user_id,
SYSDATE, fnd_global.user_id, SYSDATE,
fnd_global.user_id, v_calculate_price_flag (cur_so_l),
v_context (cur_so_l), v_line_type (cur_so_l),
v_operation (cur_so_l)
);
fnd_file.put_line (fnd_file.LOG, 'LINES DATA INSERTED');
END LOOP;
CLOSE cur_so_lines;
----------------end of lines data
COMMIT;
END; --procedure
END; ---package
/
