CREATE OR REPLACE PACKAGE BODY APPS.XXBSI_ORDER AS PROCEDURE process_order (p_header_rec IN Header_Rec_Type, p_line_tbl IN Line_Tbl_Type, x_return_status OUT VARCHAR2, x_return_message OUT VARCHAR2) IS BEGIN x_return_status := 'S'; x_return_message := NULL; INSERT INTO XXBSI_ORDER_HEADER_STG (reference_number, cust_name, account_number, cust_po_number, order_number, header_id, ordered_date, currency_code, ship_cust, ship_address1, ship_address2, ship_address3, ship_city, ship_state_province, ship_postal_code, ship_territory, ship_phone, shipping_instructions, packing_instructions, creation_date, created_by, last_update_date, last_updated_by, record_status, processed_flag, error_message) VALUES (p_header_rec.reference_number, NULL, p_header_rec.account_number, p_header_rec.cust_po_number, NULL, XXBSI_ORDER_HEADER_STG_S.NEXTVAL, p_header_rec.ordered_date, p_header_rec.currency_code, p_header_rec.ship_cust, p_header_rec.ship_address1, p_header_rec.ship_address2, p_header_rec.ship_address3, p_header_rec.ship_city, p_header_rec.ship_state_province, p_header_rec.ship_postal_code, p_header_rec.ship_territory, p_header_rec.ship_phone, p_header_rec.shipping_instructions, p_header_rec.packing_instructions, SYSDATE, -1, SYSDATE, -1, p_header_rec.record_status, 'N', NULL); FOR i IN 1 .. p_line_tbl.COUNT LOOP INSERT INTO xxbsi_order_line_stg (header_id, line_id, line_reference_number, line_num, item, quantity, qty_uom, selling_price, shipping_instructions, packing_instructions, creation_date, created_by, last_update_date, last_updated_by, record_status, processed_flag, error_message) VALUES (XXBSI_ORDER_HEADER_STG_S.CURRVAL, XXBSI_ORDER_LINE_STG_S.NEXTVAL, p_line_tbl ( i).line_reference_number, p_line_tbl ( i).line_num, p_line_tbl ( i).item, p_line_tbl ( i).quantity, p_line_tbl ( i).qty_uom, p_line_tbl ( i).selling_price, p_line_tbl ( i).shipping_instructions, p_line_tbl ( i).packing_instructions, SYSDATE, -1, SYSDATE, -1, p_line_tbl ( i).record_status, 'N', NULL); NULL; END LOOP; COMMIT; xxbsi_isg_order_import_api.process_order(XXBSI_ORDER_HEADER_STG_S.CURRVAL,l_status, l_message); EXCEPTION WHEN OTHERS THEN x_return_status := 'U'; x_return_message := SQLERRM; END; END XXBSI_ORDER; /