4 Replies Latest reply: Aug 16, 2013 6:23 PM by User529649-OC RSS

    Process_Order API is not working in OM - r12.1.3

    User529649-OC

      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

          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

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

                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