Creating Sales Order with OE_ORDER_PUB API - Working Sample

Dear Readers

The below post will focus on providing a working sample code which can create sales orders in OM with the help of OE_ORDER_PUB.PROCESS_ORDER API

DECLARE

   lv_return_status                 VARCHAR2 (2000);

   lv_msg_count                     NUMBER;

   lv_msg_data                      VARCHAR2 (2000);

   lv_org                           NUMBER;

   lv_item_id                       NUMBER;

   lv_customer_id                   NUMBER;

   lv_ship_to_org_id                NUMBER;

   lv_inv_to_org_id                 NUMBER;

   lv_ship_from_org_id              NUMBER;

   lv_status                        VARCHAR2(1);

   lv_errmsg                        VARCHAR2(2000);

   lv_header_id                     NUMBER;

   lv_order_type_id                 NUMBER;

   lv_price_list_id                 NUMBER;

   lv_order_source_id               NUMBER;

   lv_line_type_id                  NUMBER;

   lv_order_number                  NUMBER;

   -- INPUT VARIABLES FOR PROCESS_ORDER API

   lv_header_rec                    oe_order_pub.header_rec_type;

   lv_line_tbl                      oe_order_pub.line_tbl_type;

   lv_action_request_tbl            oe_order_pub.request_tbl_type;

   -- OUT VARIABLES FOR PROCESS_ORDER API

   lv_header_rec_out                oe_order_pub.header_rec_type;

   lv_header_val_rec_out            oe_order_pub.header_val_rec_type;

   lv_header_adj_tbl_out            oe_order_pub.header_adj_tbl_type;

   lv_header_adj_val_tbl_out        oe_order_pub.header_adj_val_tbl_type;

   lv_header_price_att_tbl_out      oe_order_pub.header_price_att_tbl_type;

   lv_header_adj_att_tbl_out        oe_order_pub.header_adj_att_tbl_type;

   lv_header_adj_assoc_tbl_out      oe_order_pub.header_adj_assoc_tbl_type;

   lv_header_scredit_tbl_out        oe_order_pub.header_scredit_tbl_type;

   lv_header_scredit_val_tbl_out    oe_order_pub.header_scredit_val_tbl_type;

   lv_line_tbl_out                  oe_order_pub.line_tbl_type;

   lv_line_val_tbl_out              oe_order_pub.line_val_tbl_type;

   lv_line_adj_tbl_out              oe_order_pub.line_adj_tbl_type;

   lv_line_adj_val_tbl_out          oe_order_pub.line_adj_val_tbl_type;

   lv_line_price_att_tbl_out        oe_order_pub.line_price_att_tbl_type;

   lv_line_adj_att_tbl_out          oe_order_pub.line_adj_att_tbl_type;

   lv_line_adj_assoc_tbl_out        oe_order_pub.line_adj_assoc_tbl_type;

   lv_line_scredit_tbl_out          oe_order_pub.line_scredit_tbl_type;

   lv_line_scredit_val_tbl_out      oe_order_pub.line_scredit_val_tbl_type;

   lv_lot_serial_tbl_out            oe_order_pub.lot_serial_tbl_type;

   lv_lot_serial_val_tbl_out        oe_order_pub.lot_serial_val_tbl_type;

   lv_action_request_tbl_out        oe_order_pub.request_tbl_type;

   lv_msg_index                     NUMBER;

   lv_data                          VARCHAR2 (2000);

   lv_loop_count                    NUMBER;

   lv_debug_file                    VARCHAR2 (200);

   lv_user_id                       NUMBER;

   lv_resp_id                       NUMBER;

   lv_resp_appl_id                  NUMBER;

   lv_email_id                      VARCHAR2(240);

   lv_total_rec                     NUMBER := 0;

   lv_proc_rec                      NUMBER;

   lv_error_rec                     NUMBER;

   gc_operating_unit    CONSTANT    VARCHAR2(100) := 'Vision Operations';

   gc_organization_code CONSTANT    VARCHAR2(30)  := 'V1';

   gc_order_source      CONSTANT    VARCHAR2(30)  := 'XXSampleSrc';

   gc_order_type        CONSTANT    VARCHAR2(30)  := 'XXSample Import';

   gc_price_list        CONSTANT    VARCHAR2(30)  := 'Corporate';

   gc_test_item_num     CONSTANT    VARCHAR2(30)  := 'SAMPLE_ITEM';

   gc_user_name         CONSTANT    VARCHAR2(30)  := 'SAMPLE_USER';

BEGIN

   SELECT   user_id, email_address

   INTO     lv_user_id, lv_email_id

   FROM     fnd_user

   WHERE    user_name = gc_user_name;

   SELECT   responsibility_id,

            responsibility_application_id

   INTO     lv_resp_id,

            lv_resp_appl_id

   FROM     fnd_user_resp_groups

   WHERE    user_id = lv_user_id

   AND      responsibility_application_id = 660

   AND      ROWNUM < 2;

   fnd_global.apps_initialize(lv_user_id, lv_resp_id, lv_resp_appl_id);

   SELECT   organization_id

   INTO     lv_org

   FROM     hr_operating_units

   WHERE    name = gc_operating_unit;

   SELECT   organization_id

   INTO     lv_ship_from_org_id

   FROM     mtl_parameters

   WHERE    organization_code = gc_organization_code;

   SELECT   order_source_id

   INTO     lv_order_source_id

   FROM     oe_order_sources

   WHERE    name = gc_order_source;

   SELECT   inventory_item_id

   INTO     lv_item_id

   FROM     mtl_system_items

   WHERE    segment1 = gc_test_item_num

   AND      organization_id = lv_ship_from_org_id;

   SELECT   ota.transaction_type_id, ota.price_list_id

   INTO     lv_order_type_id, lv_price_list_id

   FROM     oe_transaction_types_tl ott, oe_transaction_types_all ota

   WHERE    ott.name = gc_order_type

   AND      ott.transaction_type_id = ota.transaction_type_id;

   IF lv_price_list_id IS NULL THEN

      SELECT   list_header_id

      INTO     lv_price_list_id

      FROM     qp_list_headers

      WHERE    name = gc_price_list;

   END IF;

   SELECT   line_type_id

   INTO     lv_line_type_id

   FROM     oe_workflow_assignments

   WHERE    order_type_id = lv_order_type_id

   AND      line_type_id IS NOT NULL;

   FOR i IN

   (

      SELECT   ROWID row_id, stg.*

      FROM     xx_order_staging_tbl stg

      WHERE    Nvl(process_flag, 'N') = 'N'

   ) LOOP

      lv_status         := 'V';

      lv_errmsg         := NULL;

      lv_customer_id    := NULL;

      lv_ship_to_org_id := NULL;

      lv_inv_to_org_id  := NULL;

      BEGIN

         BEGIN

            SELECT   hca.cust_account_id

            INTO     lv_customer_id

            FROM     hz_cust_accounts hca,

                     hz_parties hp

            WHERE    hca.party_id = hp.party_id

            AND      hp.party_name = i.customer_name

            AND      hca.status = 'A';

         EXCEPTION

            WHEN OTHERS THEN

               lv_status := 'E';

               lv_errmsg := 'Unable to find customer ID: ' || SQLERRM;

               RAISE validation_error;

         END;

         BEGIN

            SELECT   hcsu.site_use_id

            INTO     lv_ship_to_org_id

            FROM     hz_cust_site_uses_all hcsu,

                     hz_cust_acct_sites_all hcas,

                     hz_cust_accounts hca

            WHERE    hca.cust_account_id = lv_customer_id

            AND      hca.cust_account_id = hcas.cust_account_id

            AND      hcas.org_id = lv_org

            AND      hcas.cust_acct_site_id = hcsu.cust_acct_site_id

            AND      hcsu.org_id = lv_org

            AND      hcsu.site_use_code = 'SHIP_TO'

            AND      hcsu.primary_flag = 'Y';

         EXCEPTION

            WHEN OTHERS THEN

               lv_status := 'E';

               lv_errmsg := 'Unable to find Ship To Site Use ID: ' || SQLERRM;

               RAISE validation_error;

         END;

         BEGIN

            SELECT   hcsu.site_use_id

            INTO     lv_inv_to_org_id

            FROM     hz_cust_site_uses_all hcsu,

                     hz_cust_acct_sites_all hcas,

                     hz_cust_accounts hca

            WHERE    hca.cust_account_id = lv_customer_id

            AND      hca.cust_account_id = hcas.cust_account_id

            AND      hcas.org_id = lv_org

            AND      hcas.cust_acct_site_id = hcsu.cust_acct_site_id

            AND      hcsu.org_id = lv_org

            AND      hcsu.site_use_code = 'BILL_TO'

            AND      hcsu.primary_flag = 'Y';

         EXCEPTION

            WHEN OTHERS THEN

               lv_status := 'E';

               lv_errmsg := 'Unable to find Bill To Site Use ID: ' || SQLERRM;

               RAISE validation_error;

         END;

      EXCEPTION

         WHEN validation_error THEN

            NULL;

      END;

      IF lv_status = 'E' THEN

         lv_error_rec := lv_error_rec + 1;

      END IF;

      UPDATE   xx_order_staging_tbl

      SET      process_flag      = lv_status,

               error_message     = lv_errmsg,

               customer_id       = lv_customer_id,

               ship_to_org_id    = lv_ship_to_org_id,

               invoice_to_org_id = lv_inv_to_org_id

      WHERE    ROWID = i.row_id;

   END LOOP;

   COMMIT;

   -- INITIALIZATION REQUIRED FOR R12

   mo_global.set_policy_context ('S', lv_org);

   mo_global.init ('ONT');

   FOR lrec_stg IN

   (

      SELECT   ROWID row_id, stg.*

      FROM     xx_order_staging_tbl stg

      WHERE    process_flag = 'V'

   ) LOOP

      Dbms_Output.put_line('inside loop');

      lv_status      := 'P';

      lv_errmsg      := NULL;

      lv_header_id   := NULL;

      -- INITIALIZE HEADER RECORD

      lv_header_rec                          := oe_order_pub.g_miss_header_rec;

      -- POPULATE REQUIRED ATTRIBUTES

      lv_header_rec.operation                := oe_globals.g_opr_create;

      lv_header_rec.pricing_date             := SYSDATE;

      lv_header_rec.cust_po_number           := lrec_stg.po_number;

      lv_header_rec.sold_to_org_id           := lrec_stg.customer_id;

      lv_header_rec.price_list_id            := lv_price_list_id;

      lv_header_rec.ordered_date             := SYSDATE;

      lv_header_rec.sold_from_org_id         := lv_org;

      lv_header_rec.ship_to_org_id           := lrec_stg.ship_to_org_id;

      lv_header_rec.invoice_to_org_id        := lrec_stg.invoice_to_org_id;

      lv_header_rec.salesrep_id              := -3;

      lv_header_rec.order_type_id            := lv_order_type_id;

      lv_header_rec.order_source_id          := lv_order_source_id;

      lv_header_rec.orig_sys_document_ref    := lrec_stg.order_id;   --any unique value to link your staging to the order

      lv_header_rec.transactional_curr_code  := 'USD';

      lv_action_request_tbl (1)              := oe_order_pub.g_miss_request_rec;

      -- INITIALIZE LINE RECORD

      lv_line_tbl (1)                        := oe_order_pub.g_miss_line_rec;

      lv_line_tbl (1).operation              := oe_globals.g_opr_create; -- Mandatory Operation to Pass

      lv_line_tbl (1).inventory_item_id      := lv_item_id;

      lv_line_tbl (1).ordered_quantity       := 1;

      lv_line_tbl (1).unit_selling_price     := To_Number(lrec_stg.amount);

      lv_line_tbl (1).unit_list_price        := To_Number(lrec_stg.amount);

      lv_line_tbl (1).ship_from_org_id       := lv_ship_from_org_id;

      lv_line_tbl (1).line_type_id           := lv_line_type_id;

      lv_line_tbl (1).calculate_price_flag   := 'N';

      oe_order_pub.process_order

      (

         p_org_id                   => lv_org,

         p_api_version_number       => 1.0,

         p_header_rec               => lv_header_rec,

         p_line_tbl                 => lv_line_tbl,

         p_action_request_tbl       => lv_action_request_tbl,

         -- OUT variables

         x_header_rec               => lv_header_rec_out,

         x_header_val_rec           => lv_header_val_rec_out,

         x_header_adj_tbl           => lv_header_adj_tbl_out,

         x_header_adj_val_tbl       => lv_header_adj_val_tbl_out,

         x_header_price_att_tbl     => lv_header_price_att_tbl_out,

         x_header_adj_att_tbl       => lv_header_adj_att_tbl_out,

         x_header_adj_assoc_tbl     => lv_header_adj_assoc_tbl_out,

         x_header_scredit_tbl       => lv_header_scredit_tbl_out,

         x_header_scredit_val_tbl   => lv_header_scredit_val_tbl_out,

         x_line_tbl                 => lv_line_tbl_out,

         x_line_val_tbl             => lv_line_val_tbl_out,

         x_line_adj_tbl             => lv_line_adj_tbl_out,

         x_line_adj_val_tbl         => lv_line_adj_val_tbl_out,

         x_line_price_att_tbl       => lv_line_price_att_tbl_out,

         x_line_adj_att_tbl         => lv_line_adj_att_tbl_out,

         x_line_adj_assoc_tbl       => lv_line_adj_assoc_tbl_out,

         x_line_scredit_tbl         => lv_line_scredit_tbl_out,

         x_line_scredit_val_tbl     => lv_line_scredit_val_tbl_out,

         x_lot_serial_tbl           => lv_lot_serial_tbl_out,

         x_lot_serial_val_tbl       => lv_lot_serial_val_tbl_out,

         x_action_request_tbl       => lv_action_request_tbl_out,

         x_return_status            => lv_return_status,

         x_msg_count                => lv_msg_count,

         x_msg_data                 => lv_msg_data

      );

      Dbms_Output.put_line('status = ' || lv_return_status);

      -- CHECK RETURN STATUS

      IF lv_return_status = fnd_api.g_ret_sts_success

      THEN

         lv_status := 'P';

         lv_errmsg := NULL;

         lv_proc_rec := lv_proc_rec + 1;

         lv_header_id := lv_header_rec_out.header_id;

         lv_order_number := lv_header_rec_out.order_number;

         COMMIT;

      ELSE

         lv_errmsg := 'API Error : ';

         lv_status := 'E';

         lv_error_rec := lv_error_rec + 1;

         lv_header_id := NULL;

         lv_order_number := NULL;

         FOR i IN 1 .. lv_msg_count

         LOOP

            oe_msg_pub.get

            (

               p_msg_index       => i,

               p_encoded         => fnd_api.g_false,

               p_data            => lv_data,

               p_msg_index_out   => lv_msg_index

            );

            lv_errmsg := lv_errmsg || lv_data || ' : ';

         END LOOP;

         ROLLBACK;

      END IF;

      UPDATE   xx_order_staging_tbl

      SET      process_flag = lv_status,

               error_message = lv_errmsg,

               header_id = lv_header_id,

               order_number = lv_order_number

      WHERE    ROWID = lrec_stg.row_id;

   END LOOP;

END;

Hope this will help

Cheers
A