This discussion is archived
4 Replies Latest reply: Aug 16, 2013 4:23 PM by User529649-OC RSS

Process_Order API is not working in OM - r12.1.3

User529649-OC Explorer
Currently Being Moderated

Hello,

 

I am writing a process_order API to create sales orders in Oracle from  Blanket Sales Agreements.  But below mentioned API is neither throwing any errors nor it is importing. Not sure what am I missing.   Complete code is mentioned below.

 

Hope to get lucky in getting advise from gurus on this forum.

 

code

/* Formatted on 8/15/2013 2:54:52 PM (QP5 v5.252.13127.32867) */

CREATE OR REPLACE PACKAGE BODY XXCH_BSA_ORDER

IS

   PROCEDURE BSA_PROCESS_ORDER (errbuf             OUT VARCHAR2,

                                retcode            OUT VARCHAR2,

                                p_blanket_number       NUMBER,

                                p_blanket_line         NUMBER,

                                p_select               VARCHAR2,

                                p_order_type           NUMBER,

                                p_copies               NUMBER)

   AS

      v_error   VARCHAR2 (2000);

   BEGIN

      INSERT INTO LOGFILE (STEPID, MODULE)

           VALUES (1, 'VERIZON');

 

 

      COMMIT;

 

 

      FOR i IN 1 .. p_copies

      LOOP

         INSERT INTO LOGFILE (STEPID, MODULE)

              VALUES (2, 'VERIZON');

 

 

         BSA_API (p_blanket_number   => p_blanket_number,

                  p_blanket_line     => p_blanket_line,

                  p_select           => p_select,

                  p_order_type       => p_order_type);

      END LOOP;

   EXCEPTION

      WHEN OTHERS

      THEN

         v_error := SQLERRM;

         RAISE_APPLICATION_ERROR (-20001, v_error);

   END;

 

 

 

 

 

 

   PROCEDURE BSA_API (p_blanket_number    NUMBER,

                      p_blanket_line      NUMBER,

                      p_select            VARCHAR2,

                      p_order_type        NUMBER)

   AS

      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 (3000);

      l_msg_count                    NUMBER;

      l_msg_data                     VARCHAR2 (3000);

      p_api_version_number           NUMBER := 1.0;

      p_init_msg_list                VARCHAR2 (1000) := fnd_api.g_true;

      p_return_values                VARCHAR2 (1000) := fnd_api.g_true;

      p_action_commit                VARCHAR2 (1000) := fnd_api.g_true;

      x_return_status                VARCHAR2 (10);

      x_msg_count                    NUMBER;

      x_msg_data                     VARCHAR2 (3000);

      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;

      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;

      xbreak                         VARCHAR2 (33);

      errorx                         VARCHAR2 (3000);

      k                              INTEGER := 0;

 

 

      CURSOR BlanketHeader

      IS

         SELECT *

           FROM oe_blanket_headers_all

          WHERE header_id = p_blanket_number;

 

 

      CURSOR BlanketLines (

         v_agreement_id    NUMBER)

      IS

         SELECT *

           FROM oe_blanket_lines_all

          WHERE     header_id = v_agreement_id

                AND line_number = NVL (p_blanket_line, line_number);

   BEGIN

      -- Create Header

      xbreak := '100';

 

 

      fnd_global.apps_initialize (12647, 51839, 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');

      xbreak := '101';

      fnd_file.Put_line (

         fnd_file.LOG,

         '*********************************************** BOOK ORDER **************************');

      fnd_file.put_line (fnd_file.LOG,

                         'Begining of the procedure Book Order');

 

 

 

 

      --    Initialize header record

    

      xbreak := '102';

 

 

      FOR i IN BlanketHeader

      LOOP

     

      insert into logfile(stepid,module) values (23,'BPO:'||to_char(i.header_id));

       l_header_rec := oe_order_pub.g_miss_header_rec;

         xbreak := '103';

         l_header_rec.cust_po_number := i.cust_po_number;

         l_header_rec.ordered_date := SYSDATE;

         l_header_rec.salesrep_id := i.salesrep_id;

         l_header_rec.order_type_id := p_order_type;

         l_header_rec.operation := oe_globals.g_opr_create; --Specifies that Order is getting created

         --xbreak := '104';

         l_header_rec.order_category_code := 'ORDER';

         --         l_num_tbl_index := 1;

         l_header_rec.booked_flag := 'Y';

         xbreak := '105';

         l_header_rec.sold_to_org_id := i.sold_to_org_id;

         l_header_rec.invoice_to_org_id := i.invoice_to_org_id;

         l_header_rec.ship_to_org_id := i.ship_to_org_id;

         l_header_rec.price_list_id := i.price_list_id;

 

 

         --Create Line record

         FOR j IN BlanketLines(i.header_id)

         LOOP

            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 :=

               j.inventory_item_id;

            l_line_tbl (l_line_tbl_index).ordered_quantity := 1;

            -- 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 := NULL;

            l_line_tbl (l_line_tbl_index).ship_from_org_id := j.org_id;

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

         END LOOP;

 

 

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

oe_debug_pub.setdebuglevel (5);

  

         oe_order_pub.process_order (

            p_org_id  => l_org_id,

            p_operating_unit => l_org_id,

            p_api_version_number       => 1.0,

            p_init_msg_list            => fnd_api.g_true,

            p_return_values            => fnd_api.g_true,

            p_action_commit            => fnd_api.g_true,

            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);

         COMMIT;

 

 

         --

         INSERT INTO LOGFILE (STEPID, MODULE)

                 VALUES (

                           22,

                              TO_CHAR (l_header_rec.header_id)

                           || '-'

                           || l_msg_data);

 

 

         COMMIT;

         xbreak := '106';

      END LOOP;

 

 

      --

 

 

      FOR i IN 1 .. l_msg_count

      LOOP

         k := k + 1;

         xbreak := '108';

         Oe_Msg_Pub.get (p_msg_index       => k,

                         p_encoded         => Fnd_Api.G_FALSE,

                         p_data            => l_msg_data,

                         p_msg_index_out   => l_msg_index_out);

                           INSERT INTO LOGFILE (STEPID, MODULE)

                 VALUES (

                           24,l_msg_data);

      END LOOP;

 

 

      /* Check if the process order goes through then it prints the success message, otherwise it prints failed message.*/

      IF l_return_status = FND_API.G_RET_STS_SUCCESS

      THEN

         xbreak := '109';

         fnd_file.put_line (

            fnd_file.LOG,

               'Return from the API:-'

            || 'return status'

            || l_return_status

            || '-'

            || l_header_rec.header_id);

      ELSE

         xbreak := '110';

         fnd_file.put_line (

            fnd_file.LOG,

               'Return from the API'

            || 'return status'

            || l_return_status

            || '-'

            || TO_CHAR (l_header_rec.header_id));

      END IF;

   EXCEPTION

      WHEN OTHERS

      THEN

         errorx := xbreak || '-' || SQLERRM;

         raise_application_error (-20002, errorx);

   END;

END;

Thanks

Darsh

  • 1. Re: Process_Order API is not working in OM - r12.1.3
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated

    Please see if (OE_ORDER_PUB.PROCESS_ORDER is Not Working In R12 (Doc ID 750439.1)) helps.

     

    Thanks,

    Hussein

  • 2. Re: Process_Order API is not working in OM - r12.1.3
    User529649-OC Explorer
    Currently Being Moderated

    Hussein,

     

    As always - Thank you.   I have passed the above issue but now in the same API, it is not importing lines.  I need to enter multiple lines for a given sales order through this API. As per the note from you on this thread, I am now maintaining separate set of input and output variables.

     

    When I searched metalink and google, there are countless examples of process_order APIs  giving example of importing one sales order line along with header at the time of creation.  Then, they ask to add subsequent lines through an UPDATE operations for existing order that was just created. Thus, forcing me to write process_order api call two times.

     

    Can't I just create a loop and add multiple lines right at the time of order creation? Please see the revised code as shown below.  This code is not working - it throws "no data found" for a weird reason.  I know for a fact that data is fetched properly by blanketLines cursor.

     

    /* Formatted on 8/16/2013 4:23:09 PM (QP5 v5.252.13127.32867) */

    CREATE OR REPLACE PACKAGE BODY XXCH_BSA_ORDER

    IS

       PROCEDURE BSA_PROCESS_ORDER (errbuf             OUT VARCHAR2,

                                    retcode            OUT VARCHAR2,

                                    p_blanket_number       NUMBER,

                                    p_select               VARCHAR2,

                                    p_blanket_line         NUMBER,

                                    p_order_type           NUMBER,

                                    p_copies               NUMBER)

       AS

          v_error          VARCHAR2 (2000);

          o_order_number   NUMBER;

       BEGIN

          INSERT INTO LOGFILE (STEPID, MODULE)

               VALUES (1, 'VERIZON');

     

     

          COMMIT;

     

     

          FOR i IN 1 .. p_copies

          LOOP

             INSERT INTO LOGFILE (STEPID, MODULE)

                  VALUES (2, 'VERIZON');

     

     

             BSA_API (p_blanket_number   => p_blanket_number,

                      p_blanket_line     => p_blanket_line,

                      p_select           => p_select,

                      p_order_type       => p_order_type,

                      p_order_number     => o_order_number);

     

     

             fnd_file.put_line (fnd_file.OUTPUT,

                                'Order Number Created:-' || o_order_number);

          END LOOP;

       EXCEPTION

          WHEN OTHERS

          THEN

             v_error := SQLERRM;

             RAISE_APPLICATION_ERROR (-20001, v_error);

       END;

     

     

     

     

     

     

       PROCEDURE BSA_API (p_blanket_number       NUMBER,

                          p_blanket_line         NUMBER,

                          p_select               VARCHAR2,

                          p_order_type           NUMBER,

                          p_order_number     OUT NUMBER)

       AS

          l_header_rec                   oe_order_pub.header_rec_type;

          l_header_rec_out               oe_order_pub.header_rec_type;

          l_line_tbl                     oe_order_pub.line_tbl_type;

          l_line_tbl_out                 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 (3000);

          l_msg_count                    NUMBER;

          l_msg_data                     VARCHAR2 (3000);

          p_api_version_number           NUMBER := 1.0;

          p_init_msg_list                VARCHAR2 (1000) := fnd_api.g_true;

          p_return_values                VARCHAR2 (1000) := fnd_api.g_true;

          p_action_commit                VARCHAR2 (1000) := fnd_api.g_true;

          x_return_status                VARCHAR2 (10);

          x_msg_count                    NUMBER;

          x_msg_data                     VARCHAR2 (3000);

          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;

          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_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;

          x_debug_file                   VARCHAR2 (100);

          l_line_tbl_index               INTEGER := 0;

          l_msg_index_out                NUMBER;

          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;

          xbreak                         VARCHAR2 (33);

          errorx                         VARCHAR2 (3000);

          k                              INTEGER := 0;

          ln_kount                       INTEGER := 0;

          v_order                        NUMBER;

     

     

          CURSOR BlanketHeader

          IS

             SELECT *

               FROM oe_blanket_headers_all

              WHERE header_id = p_blanket_number;

     

     

          CURSOR BlanketLines (

             v_agreement_id    NUMBER)

          IS

             SELECT *

               FROM oe_blanket_lines_all

              WHERE     header_id = v_agreement_id

                    AND line_number = NVL (p_blanket_line, line_number);

       BEGIN

          -- Create Header

          xbreak := '100';

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

     

     

          INSERT INTO logfile (stepid, module)

               VALUES (1, 'orgid:' || TO_CHAR (l_org_id));

     

     

          oe_msg_pub.initialize;

          oe_debug_pub.initialize;

     

     

          mo_global.set_policy_context ('S', l_org_ID);

          mo_global.init ('ONT');

     

     

          fnd_global.apps_initialize (12647, 51839, 660);

          --       pass in user_id, responsibility_id, and application_id

          oe_debug_pub.setdebuglevel (5);

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

     

     

     

     

          INSERT INTO logfile (stepid, module)

               VALUES (25, 'debug:' || x_debug_File);

     

     

          COMMIT;

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

          xbreak := '101';

          fnd_file.Put_line (

             fnd_file.LOG,

             '*********************************************** BOOK ORDER **************************');

          fnd_file.put_line (fnd_file.LOG,

                             'Begining of the procedure Book Order');

     

     

     

     

          --    Initialize header record

     

     

          xbreak := '102';

     

     

          FOR i IN BlanketHeader

          LOOP

             INSERT INTO logfile (stepid, module)

                  VALUES (23, 'BPO:' || TO_CHAR (i.header_id));

     

     

             l_header_rec := oe_order_pub.g_miss_header_rec;

             xbreak := '103';

             l_header_rec.cust_po_number := i.cust_po_number;

             l_header_rec.ordered_date := SYSDATE;

             l_header_rec.salesrep_id := i.salesrep_id;

             l_header_rec.order_type_id := p_order_type;

             l_header_rec.operation := oe_globals.g_opr_create; --Specifies that Order is getting created

             --xbreak := '104';

             l_header_rec.order_category_code := 'ORDER';

             --         l_num_tbl_index := 1;

             l_header_rec.booked_flag := 'N';

             l_header_rec.flow_status_code := 'ENTERED';

             xbreak := '105';

             l_header_rec.sold_to_org_id := i.sold_to_org_id;

             l_header_rec.invoice_to_org_id := i.invoice_to_org_id;

             l_header_rec.ship_to_org_id := i.ship_to_org_id;

             l_header_rec.price_list_id := i.price_list_id;

             l_header_rec.shipping_instructions := i.shipping_instructions;

             l_header_rec.freight_terms_code := i.freight_terms_code;

             l_header_rec.attribute10 := i.attribute1;

             l_header_rec.salesrep_id := i.salesrep_id;

             l_action_request_tbl (1) := OE_ORDER_PUB.G_MISS_REQUEST_REC;

     

     

             INSERT INTO logfile (stepid, module)

                  VALUES (53, 'BPO:' || TO_CHAR (i.header_id));  -- Create Lines .

     

     

             xbreak := '206';

     

     

             FOR j IN BlanketLines (i.header_id)

             LOOP

                xbreak := '205';

                ln_kount := ln_kount + 1;

                l_line_tbl_index := 1;

                l_line_tbl (l_line_tbl_index) := oe_order_pub.g_miss_line_rec;

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

     

     

                l_line_tbl (l_line_tbl_index).inventory_item_id :=

                   j.inventory_item_id;

     

     

                INSERT INTO logfile (stepid, module)

                     VALUES (57, 'BPO:' || TO_CHAR (j.inventory_item_id));

     

     

                l_line_tbl (l_line_tbl_index).order_quantity_uom := 'EA';

                l_line_tbl (l_line_tbl_index).payment_term_id := 1000;

                l_line_tbl (l_line_tbl_index).schedule_arrival_date := SYSDATE + 1;

                l_line_tbl (l_line_tbl_index).schedule_ship_date := SYSDATE + 1;

                l_line_tbl (l_line_tbl_index).request_date := SYSDATE;

                l_line_tbl (l_line_tbl_index).promise_date := SYSDATE;

                l_line_tbl (l_line_tbl_index).shipping_method_code := NULL;

                l_line_tbl (l_line_tbl_index).freight_terms_code := NULL;

                l_line_tbl (l_line_tbl_index).line_number := 1;

                l_line_tbl (l_line_tbl_index).ordered_quantity := 1;

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

                l_line_tbl (l_line_tbl_index).unit_selling_price := NULL;

                l_line_tbl (l_line_tbl_index).ship_from_org_id := j.org_id;

                l_line_tbl (l_line_tbl_index).ship_to_org_id := j.ship_to_org_id;

             END LOOP;

     

     

     

     

             oe_order_pub.process_order (

                p_org_id                   => l_org_id,

                p_operating_unit           => NULL,

                p_api_version_number       => 1.0,

                --            p_init_msg_list => fnd_api.g_true,

                --                                p_return_values => fnd_api.g_true,

                --                                p_action_commit => fnd_api.g_true,

                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_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);

             COMMIT;

     

     

             --

     

     

             FND_FILE.PUT_LINE (

                FND_FILE.LOG,

                   'OM Debug file: '

                || oe_debug_pub.g_dir

                || '/'

                || oe_debug_pub.g_file);

     

     

             INSERT INTO LOGFILE (STEPID, MODULE)

                     VALUES (

                               22,

                                  TO_CHAR (l_header_rec_out.header_id)

                               || '-'

                               || l_msg_data);

     

     

             COMMIT;

             xbreak := '106';

     

     

             SELECT order_number

               INTO v_order

               FROM oe_order_headerS_all

              WHERE header_id = l_header_rec_out.header_id;

     

     

             p_order_number := v_order;

          END LOOP;

     

     

          --

     

     

          FOR i IN 1 .. l_msg_count

          LOOP

             k := k + 1;

             xbreak := '108';

             Oe_Msg_Pub.get (p_msg_index       => k,

                             p_encoded         => Fnd_Api.G_FALSE,

                             p_data            => l_msg_data,

                             p_msg_index_out   => l_msg_index_out);

     

     

             INSERT INTO LOGFILE (STEPID, MODULE)

                  VALUES (24, l_msg_data);

     

     

             fnd_file.put_line (

                fnd_file.LOG,

                   'Return from the API:-'

                || 'return status:-'

                || l_return_status

                || '-'

                || l_msg_data);

          END LOOP;

     

     

          /* Check if the process order goes through then it prints the success message, otherwise it prints failed message.*/

          IF l_return_status = FND_API.G_RET_STS_SUCCESS

          THEN

             xbreak := '109';

             fnd_file.put_line (

                fnd_file.LOG,

                   'Return from the API:-'

                || 'return status:-'

                || l_return_status

                || '-'

                || l_header_rec_out.header_id);

          ELSE

             xbreak := '110';

             fnd_file.put_line (

                fnd_file.LOG,

                   'Return from the API:-'

                || 'return status:-'

                || l_return_status

                || '-'

                || TO_CHAR (l_header_rec_out.header_id));

          END IF;

       EXCEPTION

          WHEN OTHERS

          THEN

             errorx := xbreak || '-' || SQLERRM;

             raise_application_error (-20002, errorx);

       END;

    END;

  • 3. Re: Process_Order API is not working in OM - r12.1.3
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    Can't I just create a loop and add multiple lines right at the time of order creation?


    I'm not sure if this is supported or not -- Please log a SR and Oracle support should be able to answer this question.


    Thanks,

    Hussein

  • 4. Re: Process_Order API is not working in OM - r12.1.3
    User529649-OC Explorer
    Currently Being Moderated

    The "No Data Found" is only  ocurring at following line.

     

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

     

    Just fyi..

     

    Thanks,

    Darsh

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points