OE_ORDER_PUB: Create and Book Sales Order with Cross Reference Items

 /*Create and Book Sales Order with Cross Reference Items*/

DECLARE
    l_api_version_number         NUMBER := 1;
    l_return_status              VARCHAR2(2000);
    l_msg_count                  NUMBER;
    l_msg_data                   VARCHAR2(2000);
    l_debug_level                NUMBER := 1;
    --IN Variables
    l_header_rec                 oe_order_pub.header_rec_type;
    l_line_tbl                   oe_order_pub.line_tbl_type;
    l_action_request_tbl         oe_order_pub.request_tbl_type;
    -- OUT Variables
    l_header_rec_out             oe_order_pub.header_rec_type;
    l_header_val_rec_out         oe_order_pub.header_val_rec_type;
    l_header_adj_tbl_out         oe_order_pub.header_adj_tbl_type;
    l_header_adj_val_tbl_out     oe_order_pub.header_adj_val_tbl_type;
    l_header_price_att_tbl_out   oe_order_pub.header_price_att_tbl_type;
    l_header_adj_att_tbl_out     oe_order_pub.header_adj_att_tbl_type;
    l_header_adj_assoc_tbl_out   oe_order_pub.header_adj_assoc_tbl_type;
    l_header_scredit_tbl_out     oe_order_pub.header_scredit_tbl_type;
    l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
    l_line_tbl_out               oe_order_pub.line_tbl_type;
    l_line_val_tbl_out           oe_order_pub.line_val_tbl_type;
    l_line_adj_tbl_out           oe_order_pub.line_adj_tbl_type;
    l_line_adj_val_tbl_out       oe_order_pub.line_adj_val_tbl_type;
    l_line_price_att_tbl_out     oe_order_pub.line_price_att_tbl_type;
    l_line_adj_att_tbl_out       oe_order_pub.line_adj_att_tbl_type;
    l_line_adj_assoc_tbl_out     oe_order_pub.line_adj_assoc_tbl_type;
    l_line_scredit_tbl_out       oe_order_pub.line_scredit_tbl_type;
    l_line_scredit_val_tbl_out   oe_order_pub.line_scredit_val_tbl_type;
    l_lot_serial_tbl_out         oe_order_pub.lot_serial_tbl_type;
    l_lot_serial_val_tbl_out     oe_order_pub.lot_serial_val_tbl_type;
    l_action_request_tbl_out     oe_order_pub.request_tbl_type;
    l_msg_index                  NUMBER;
    l_data                       VARCHAR2(2000);
    l_loop_count                 NUMBER;
    l_debug_file                 VARCHAR2(200);
    b_return_status              VARCHAR2(200);
    b_msg_count                  NUMBER;
    b_msg_data                   VARCHAR2(2000);
BEGIN
    IF ( l_debug_level > 0 ) THEN
      l_debug_file := oe_debug_pub.Set_debug_mode('FILE');

      oe_debug_pub.initialize;

      oe_debug_pub.Setdebuglevel(l_debug_level);

      oe_msg_pub.initialize;
    END IF;

    mo_global.Init('ONT');

    mo_global.Set_policy_context('S', 134);

    fnd_global.Apps_initialize (user_id => 2739, resp_id => 51079,
    resp_appl_id => 660);

    l_header_rec := oe_order_pub.g_miss_header_rec;

    l_header_rec.operation := oe_globals.g_opr_create;

    l_header_rec.order_type_id := 4120;

    l_header_rec.sold_to_org_id := 614411;

    l_header_rec.ship_to_org_id := 368514;

    l_header_rec.invoice_to_org_id := 368512;

    l_header_rec.sold_from_org_id := 134;

    l_header_rec.salesrep_id := -3;

    l_header_rec.price_list_id := 9204059;

    l_header_rec.pricing_date := SYSDATE;

    l_header_rec.transactional_curr_code := 'INR';

    l_header_rec.flow_status_code := 'ENTERED'; --p_flow_status_code;–

    l_header_rec.cust_po_number := 'TestEnter1';

    l_header_rec.order_source_id := 0; --p_order_source_id;–0 ;

    -- To BOOK the Sales Order
    L_action_request_tbl(1) := oe_order_pub.g_miss_request_rec;

    L_action_request_tbl(1).request_type := oe_globals.g_book_order;

    L_action_request_tbl(1).entity_code := oe_globals.g_entity_header;

    L_line_tbl(1) := oe_order_pub.g_miss_line_rec;

    L_line_tbl(1).operation := oe_globals.g_opr_create;

    L_line_tbl(1).ordered_item_id := 732310; --Ordered Item : MTL_CUSTOMER_ITEMS.CUSTOMER_ITEM_ID

    --l_line_tbl(1).ordered_item := '111421245'; 
    L_line_tbl(1).inventory_item_id := 10672036; --Internal Item

    L_line_tbl(1).ordered_quantity := 5;

    L_line_tbl(1).ship_to_org_id := 368514;

    L_line_tbl(1).item_identifier_type := 'CUST'; --Required for 'Cross Reference Items'

    dbms_output.Put_line('Calling API');

    oe_order_pub.Process_order(p_api_version_number => l_api_version_number,
    p_header_rec => l_header_rec, p_line_tbl => l_line_tbl,
    p_action_request_tbl => l_action_request_tbl,
    --OUT variables
    x_header_rec => l_header_rec_out, x_header_val_rec => l_header_val_rec_out,
    x_header_adj_tbl => l_header_adj_tbl_out,
    x_header_adj_val_tbl => l_header_adj_val_tbl_out,
    x_header_price_att_tbl => l_header_price_att_tbl_out,
    x_header_adj_att_tbl => l_header_adj_att_tbl_out,
    x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out,
    x_header_scredit_tbl => l_header_scredit_tbl_out,
    x_header_scredit_val_tbl => l_header_scredit_val_tbl_out,
    x_line_tbl => l_line_tbl_out, x_line_val_tbl => l_line_val_tbl_out,
    x_line_adj_tbl => l_line_adj_tbl_out,
    x_line_adj_val_tbl => l_line_adj_val_tbl_out,
    x_line_price_att_tbl => l_line_price_att_tbl_out,
    x_line_adj_att_tbl => l_line_adj_att_tbl_out,
    x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out,
    x_line_scredit_tbl => l_line_scredit_tbl_out,
    x_line_scredit_val_tbl => l_line_scredit_val_tbl_out,
    x_lot_serial_tbl => l_lot_serial_tbl_out,
    x_lot_serial_val_tbl => l_lot_serial_val_tbl_out,
    x_action_request_tbl => l_action_request_tbl_out,
    x_return_status => l_return_status, x_msg_count => l_msg_count,
    x_msg_data => l_msg_data);

    IF l_return_status = fnd_api.g_ret_sts_success THEN
      dbms_output.Put_line('API Return status is success..');

      COMMIT;
    ELSE
      dbms_output.Put_line('Return status failure.. ');

      IF ( l_debug_level > 0 ) THEN
        dbms_output.Put_line('failure');
      END IF;

      ROLLBACK;
    END IF;

    -- Display Return Status
    IF ( l_debug_level > 0 ) THEN
      dbms_output.Put_line('process ORDER ret status IS: '
                           || l_return_status);

      dbms_output.Put_line('process ORDER msg data IS: '
                           || l_msg_data);

      dbms_output.Put_line('header.order_number IS: '
                           || To_char(l_header_rec_out.order_number));

      dbms_output.Put_line('header.header_id IS: '
                           ||l_header_rec_out.header_id);

      dbms_output.Put_line('header.order_source_id IS: '
                           || l_header_rec_out.order_source_id);

      dbms_output.Put_line('header.flow_status_code IS: '
                           || l_header_rec_out.flow_status_code);
    END IF;

    --Display ERROR Messages
    IF ( l_debug_level > 0 ) THEN
      FOR i IN 1 .. l_msg_count LOOP
          l_data := oe_msg_pub.Get(p_msg_index => i, p_encoded => 'F');

          dbms_output.Put_line(i
                               || ') '
                               || l_data);
      END LOOP;
    END IF;

    IF ( l_debug_level > 0 ) THEN
      oe_debug_pub.debug_off;
    END IF;
END; 

P.S.:

Ques: Is There Any API for Customer Items and Customer Item Cross References Data (Doc ID 1961002.1)

Ans: There is currently no published API for Customer Items and Customer Item Cross References data.

(respectively tables MTL_CUSTOMER_ITEMS and MTL_CUSTOMER_ITEM_XREFS)

In table oe_order_lines_all:

ORDERED_ITEM => Customer Reference Item [select CUSTOMER_ITEM_ID from  mtl_customer_items mci where CUSTOMER_ID = <---> and CUSTOMER_ITEM_NUMBER = oe_order_lines_all.ORDERED_ITEM]

INVENTORY_ITEM_ID => Our manufacturing Item Id (mtl_system_items_b) [select INVENTORY_ITEM_ID from  mtl_customer_item_xrefs mcixrf where CUSTOMER_ITEM_ID = <->]