OE_ORDER_PUB.PROCESS_ORDER API to create Standard Oracle Order / Order Quote

OE_ORDER_PUB.PROCESS_ORDER API can be used to create Standard Oracle Order with Lines/Order quotes. This API can be used to create more order attributes than mentioned below.
Refer Oracle Order API documents for more information.


Same API  can be used to create a Order Quote also. All quote fields are commented below in API. You can uncomment and use if quote has to be created instead of order. Order Quote uses order tables with few extra columns.

Also included code to initialize quote workflow at the end of code. Uncomment and use it for quote.

-----------------------------------------------------------------------------------------

DECLARE

  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;

  l_header_adj_tbl             oe_order_pub.header_adj_tbl_type;

  l_line_adj_tbl               oe_order_pub.line_adj_tbl_type;

  l_header_scr_tbl             oe_order_pub.header_scredit_tbl_type;

  l_line_scredit_tbl           oe_order_pub.line_scredit_tbl_type;

  l_request_rec                oe_order_pub.request_rec_type;

  l_return_status              VARCHAR2(1000);

  l_msg_count                  NUMBER;

  l_msg_data                   VARCHAR2(1000);

  p_api_version_number         NUMBER := 1.0;

  p_init_msg_list              VARCHAR2(10) := fnd_api.g_false;

  p_return_values              VARCHAR2(10) := fnd_api.g_false;

  p_action_commit              VARCHAR2(10) := fnd_api.g_false;

  x_return_status              VARCHAR2(1);

  x_msg_count                  NUMBER;

  x_msg_data                   VARCHAR2(100);

  p_header_rec                 oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;

  p_old_header_rec             oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;

  p_header_val_rec             oe_order_pub.header_val_rec_type := oe_order_pub.g_miss_header_val_rec;

  p_old_header_val_rec         oe_order_pub.header_val_rec_type := oe_order_pub.g_miss_header_val_rec;

  p_header_adj_tbl             oe_order_pub.header_adj_tbl_type := oe_order_pub.g_miss_header_adj_tbl;

  p_old_header_adj_tbl         oe_order_pub.header_adj_tbl_type := oe_order_pub.g_miss_header_adj_tbl;

  p_header_adj_val_tbl         oe_order_pub.header_adj_val_tbl_type := oe_order_pub.g_miss_header_adj_val_tbl;

  p_old_header_adj_val_tbl     oe_order_pub.header_adj_val_tbl_type := oe_order_pub.g_miss_header_adj_val_tbl;

  p_header_price_att_tbl       oe_order_pub.header_price_att_tbl_type := oe_order_pub.g_miss_header_price_att_tbl;

  p_old_header_price_att_tbl   oe_order_pub.header_price_att_tbl_type := oe_order_pub.g_miss_header_price_att_tbl;

  p_header_adj_att_tbl         oe_order_pub.header_adj_att_tbl_type := oe_order_pub.g_miss_header_adj_att_tbl;

  p_old_header_adj_att_tbl     oe_order_pub.header_adj_att_tbl_type := oe_order_pub.g_miss_header_adj_att_tbl;

  p_header_adj_assoc_tbl       oe_order_pub.header_adj_assoc_tbl_type := oe_order_pub.g_miss_header_adj_assoc_tbl;

  p_old_header_adj_assoc_tbl   oe_order_pub.header_adj_assoc_tbl_type := oe_order_pub.g_miss_header_adj_assoc_tbl;

  p_header_scredit_tbl         oe_order_pub.header_scredit_tbl_type := oe_order_pub.g_miss_header_scredit_tbl;

  p_old_header_scredit_tbl     oe_order_pub.header_scredit_tbl_type := oe_order_pub.g_miss_header_scredit_tbl;

  p_header_scredit_val_tbl     oe_order_pub.header_scredit_val_tbl_type := oe_order_pub.g_miss_header_scredit_val_tbl;

  p_old_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type := oe_order_pub.g_miss_header_scredit_val_tbl;

  p_line_tbl                   oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl;

  p_old_line_tbl               oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl;

  p_line_val_tbl               oe_order_pub.line_val_tbl_type := oe_order_pub.g_miss_line_val_tbl;

  p_old_line_val_tbl           oe_order_pub.line_val_tbl_type := oe_order_pub.g_miss_line_val_tbl;

  p_line_adj_tbl               oe_order_pub.line_adj_tbl_type := oe_order_pub.g_miss_line_adj_tbl;

  p_old_line_adj_tbl           oe_order_pub.line_adj_tbl_type := oe_order_pub.g_miss_line_adj_tbl;

  p_line_adj_val_tbl           oe_order_pub.line_adj_val_tbl_type := oe_order_pub.g_miss_line_adj_val_tbl;

  p_old_line_adj_val_tbl       oe_order_pub.line_adj_val_tbl_type := oe_order_pub.g_miss_line_adj_val_tbl;

  p_line_price_att_tbl         oe_order_pub.line_price_att_tbl_type := oe_order_pub.g_miss_line_price_att_tbl;

  p_old_line_price_att_tbl     oe_order_pub.line_price_att_tbl_type := oe_order_pub.g_miss_line_price_att_tbl;

  p_line_adj_att_tbl           oe_order_pub.line_adj_att_tbl_type := oe_order_pub.g_miss_line_adj_att_tbl;

  p_old_line_adj_att_tbl       oe_order_pub.line_adj_att_tbl_type := oe_order_pub.g_miss_line_adj_att_tbl;

  p_line_adj_assoc_tbl         oe_order_pub.line_adj_assoc_tbl_type := oe_order_pub.g_miss_line_adj_assoc_tbl;

  p_old_line_adj_assoc_tbl     oe_order_pub.line_adj_assoc_tbl_type := oe_order_pub.g_miss_line_adj_assoc_tbl;

  p_line_scredit_tbl           oe_order_pub.line_scredit_tbl_type := oe_order_pub.g_miss_line_scredit_tbl;

  p_old_line_scredit_tbl       oe_order_pub.line_scredit_tbl_type := oe_order_pub.g_miss_line_scredit_tbl;

  p_line_scredit_val_tbl       oe_order_pub.line_scredit_val_tbl_type := oe_order_pub.g_miss_line_scredit_val_tbl;

  p_old_line_scredit_val_tbl   oe_order_pub.line_scredit_val_tbl_type := oe_order_pub.g_miss_line_scredit_val_tbl;

  p_lot_serial_tbl             oe_order_pub.lot_serial_tbl_type := oe_order_pub.g_miss_lot_serial_tbl;

  p_old_lot_serial_tbl         oe_order_pub.lot_serial_tbl_type := oe_order_pub.g_miss_lot_serial_tbl;

  p_lot_serial_val_tbl         oe_order_pub.lot_serial_val_tbl_type := oe_order_pub.g_miss_lot_serial_val_tbl;

  p_old_lot_serial_val_tbl     oe_order_pub.lot_serial_val_tbl_type := oe_order_pub.g_miss_lot_serial_val_tbl;

  p_action_request_tbl         oe_order_pub.request_tbl_type := oe_order_pub.g_miss_request_tbl;

  x_header_val_rec             oe_order_pub.header_val_rec_type;

  x_header_adj_tbl             oe_order_pub.header_adj_tbl_type;

  x_header_adj_val_tbl         oe_order_pub.header_adj_val_tbl_type;

  x_header_price_att_tbl       oe_order_pub.header_price_att_tbl_type;

  x_header_adj_att_tbl         oe_order_pub.header_adj_att_tbl_type;

  x_header_adj_assoc_tbl       oe_order_pub.header_adj_assoc_tbl_type;

  x_header_scredit_tbl         oe_order_pub.header_scredit_tbl_type;

  x_header_scredit_val_tbl     oe_order_pub.header_scredit_val_tbl_type;

  x_line_val_tbl               oe_order_pub.line_val_tbl_type;

  x_line_adj_tbl               oe_order_pub.line_adj_tbl_type;

  x_line_adj_val_tbl           oe_order_pub.line_adj_val_tbl_type;

  x_line_price_att_tbl         oe_order_pub.line_price_att_tbl_type;

  x_line_adj_att_tbl           oe_order_pub.line_adj_att_tbl_type;

  x_line_adj_assoc_tbl         oe_order_pub.line_adj_assoc_tbl_type;

  x_line_scredit_tbl           oe_order_pub.line_scredit_tbl_type;

  x_line_scredit_val_tbl       oe_order_pub.line_scredit_val_tbl_type;

  x_lot_serial_tbl             oe_order_pub.lot_serial_tbl_type;

  x_lot_serial_val_tbl         oe_order_pub.lot_serial_val_tbl_type;

  x_action_request_tbl         oe_order_pub.request_tbl_type;

  x_debug_file                 VARCHAR2(100);

  l_line_tbl_index             NUMBER;

  l_msg_index_out              NUMBER(10);

  l_user_id                    fnd_user.user_id%TYPE;

  l_appl_id                    fnd_application.application_id%TYPE;

  l_resp_id                    fnd_responsibility.responsibility_id%TYPE;

  l_org_id                     hr_operating_units.organization_id%TYPE;

  l_sold_to_org_id             hz_cust_accounts.cust_account_id%TYPE;

  l_ship_to_org_id             hz_cust_site_uses_all.site_use_id%TYPE;

  l_cust_acct_site_id          hz_cust_acct_sites_all.cust_acct_site_id%TYPE;

  l_party_id                   hz_parties.party_id%TYPE;

  l_price_list_id              qp_list_headers_b.list_header_id%TYPE;

  l_primary_salesrep_id        ra_salesreps.salesrep_id%TYPE;

  l_order_type_id              oe_transaction_types_all.transaction_type_id%TYPE;

  l_order_line_type_id         oe_transaction_types_all.transaction_type_id%TYPE;

  l_order_category_code        oe_transaction_types_all.order_category_code%TYPE;

  l_inventory_item_id          mtl_system_items_b.inventory_item_id%TYPE;

  l_currency_code              qp_list_headers.currency_code%TYPE;

  l_party_site_id              hz_party_sites.party_site_id%TYPE;

BEGIN

  DBMS_OUTPUT.ENABLE(1000000);

  fnd_global.apps_initialize(21321, 51889, 660);

  -- pass in user_id, responsibility_id, and application_id

  oe_msg_pub.initialize;

  oe_debug_pub.initialize;

  l_org_id := fnd_profile.VALUE('ORG_ID');

  -- Get Price List ID, Primary SalesRep, Order Type ID and Order Category Code

  BEGIN

    SELECT price_list_id,

           order_category_code,

           default_outbound_line_type_id

      INTO l_price_list_id, l_order_category_code, l_order_line_type_id

      FROM oe_transaction_types_all

     WHERE transaction_type_id =

           (SELECT transaction_type_id

              FROM oe_transaction_types_all

             WHERE transaction_type_id IN

                   (SELECT transaction_type_id

                      FROM oe_transaction_types_tl

                     WHERE NAME = 'Standard Order'));

 

    fnd_file.put_line(fnd_file.LOG,

                      'l_price_list_id = ' || l_price_list_id);

    fnd_file.put_line(fnd_file.LOG,

                      'l_primary_salesrep_id = ' || l_primary_salesrep_id);

    fnd_file.put_line(fnd_file.LOG,

                      'l_order_type_id = ' || l_order_type_id);

    fnd_file.put_line(fnd_file.LOG,

                      'l_order_category_code = ' || l_order_category_code);

    fnd_file.put_line(fnd_file.LOG,

                      'l_order_line_type_id = ' || l_order_line_type_id);

  EXCEPTION

    WHEN OTHERS THEN

      fnd_file.put_line(fnd_file.LOG,

                        'Error getting Price List/Pri.Sales Rep./Order Type/Order Category Code');

      DBMS_OUTPUT.put_line('Error getting Price List/Pri.Sales Rep./Order Type/Order Category Code');

      --RAISE snrdr_exception;

  END;

  BEGIN

    SELECT currency_code

      INTO l_currency_code

      FROM gl_sets_of_books sob, org_organization_definitions ood

     WHERE ood.organization_id = l_org_id

       AND sob.set_of_books_id = ood.set_of_books_id;

  EXCEPTION

    WHEN OTHERS THEN

      NULL;

  END;

  x_debug_file := oe_debug_pub.set_debug_mode('FILE');

  oe_debug_pub.setdebuglevel(5);

  -- Use 5 for the most debuging output, I warn you its a lot of data

  DBMS_OUTPUT.put_line('START OF NEW DEBUG');

  --This is to CREATE an order header and an order line

  --Create Header record

  --Initialize header record to missing

  l_header_rec := oe_order_pub.g_miss_header_rec;

  --l_header_rec.transactional_curr_code := l_currency_code;

  l_header_rec.pricing_date := SYSDATE;

  --l_header_rec.cust_po_number := 'Test123';

  l_header_rec.sold_to_org_id    := 123456; -- Customer Id

  l_header_rec.ship_to_org_id    := 123; -- Ship Site use Id

  l_header_rec.invoice_to_org_id := 456; -- Bill Site use Id

  l_header_rec.price_list_id     := l_price_list_id;

  l_header_rec.ordered_date      := SYSDATE;

  --l_header_rec.shipping_method_code := 'DHL';

  l_header_rec.sold_from_org_id := l_org_id;

  l_header_rec.salesrep_id      := -3;

  l_header_rec.order_type_id    := l_order_type_id;

  l_header_rec.operation        := oe_globals.g_opr_create;

  --Create Line record

  l_line_tbl_index := 1;

  -- FIRST LINE RECORD

  -- Initialize record to missing

  l_line_tbl(l_line_tbl_index) := oe_order_pub.g_miss_line_rec;

  -- Line attributes

  l_line_tbl(l_line_tbl_index).inventory_item_id := 999999;

  l_line_tbl(l_line_tbl_index).ordered_quantity := 10;

  -- Set to 'N' when you need to pass unit_selling_price

  l_line_tbl(l_line_tbl_index).calculate_price_flag := 'N';

  l_line_tbl(l_line_tbl_index).unit_selling_price := 10;

  l_line_tbl(l_line_tbl_index).ship_from_org_id := l_org_id;

  --l_line_tbl (l_line_tbl_index).subinventory := 'FGI';

  l_line_tbl(l_line_tbl_index).operation := oe_globals.g_opr_create;

  -- CALL TO PROCESS ORDER Check the return status and then commit.

  oe_order_pub.process_order(p_api_version_number => 1.0,

           p_init_msg_list      => fnd_api.g_false,

           p_return_values      => fnd_api.g_false,

           p_action_commit      => fnd_api.g_false,

           x_return_status      => l_return_status,

           x_msg_count          => l_msg_count,

           x_msg_data           => l_msg_data,

           p_header_rec         => l_header_rec,

           p_line_tbl           => l_line_tbl,

           p_action_request_tbl => l_action_request_tbl

           -- OUT PARAMETERS

          ,

           x_header_rec             => l_header_rec,

           x_header_val_rec         => x_header_val_rec,

           x_header_adj_tbl         => x_header_adj_tbl,

           x_header_adj_val_tbl     => x_header_adj_val_tbl,

           x_header_price_att_tbl   => x_header_price_att_tbl,

           x_header_adj_att_tbl     => x_header_adj_att_tbl,

           x_header_adj_assoc_tbl   => x_header_adj_assoc_tbl,

           x_header_scredit_tbl     => x_header_scredit_tbl,

           x_header_scredit_val_tbl => x_header_scredit_val_tbl,

           x_line_tbl               => l_line_tbl,

           x_line_val_tbl           => x_line_val_tbl,

           x_line_adj_tbl           => x_line_adj_tbl,

           x_line_adj_val_tbl       => x_line_adj_val_tbl,

           x_line_price_att_tbl     => x_line_price_att_tbl,

           x_line_adj_att_tbl       => x_line_adj_att_tbl,

           x_line_adj_assoc_tbl     => x_line_adj_assoc_tbl,

           x_line_scredit_tbl       => x_line_scredit_tbl,

           x_line_scredit_val_tbl   => x_line_scredit_val_tbl,

           x_lot_serial_tbl         => x_lot_serial_tbl,

           x_lot_serial_val_tbl     => x_lot_serial_val_tbl,

           x_action_request_tbl     => l_action_request_tbl);

  DBMS_OUTPUT.put_line('OM Debug file: ' || oe_debug_pub.g_dir || '/' ||

                       oe_debug_pub.g_file);

  -- Retrieve messages

  FOR i IN 1 .. l_msg_count LOOP

    oe_msg_pub.get(p_msg_index     => i,

                   p_encoded       => fnd_api.g_false,

                   p_data          => l_msg_data,

                   p_msg_index_out => l_msg_index_out);

    DBMS_OUTPUT.put_line('message is: ' || l_msg_data);

    DBMS_OUTPUT.put_line('message index is: ' || l_msg_index_out);

  END LOOP;

  -- Check the return status

  IF l_return_status = fnd_api.g_ret_sts_success THEN

    DBMS_OUTPUT.put_line('Process Quote Sucess');

    DBMS_OUTPUT.put_line('Quote Number ' || l_header_rec.quote_number);

    DBMS_OUTPUT.put_line('Quote Header_id ' || l_header_rec.header_id);

  ELSE

    DBMS_OUTPUT.put_line('Process Quote Failed');

  END IF;

END;