2 Replies Latest reply: Aug 19, 2013 12:31 AM by User529649-OC RSS

    ORA-01403: no data found in Package OE_Order_PVT Procedure Lines - r12.1.3

    User529649-OC

      Hello Gurus.

       

      I am getting following error in my process_order API call.   I did go through the forum link by Hussein below, but I am facing this error in r12.1.3 environment and , as you will notice in below mentioned code,  I am in fact passing ship_from_org_id both at header and line level.   So, what is causing this error and what am I missing?  Any advise is greatly appreciated.  - Thanks, Darsh

       

      https://forums.oracle.com/thread/1045259

      CREATE OR REPLACE PACKAGE BODY APPS.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               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;

            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_header_rec.ship_from_org_id := 1;

               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;

                  xbreak := '206';

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

                   xbreak := '207';

                    l_line_tbl (l_line_tbl_index).inventory_item_id := 149724;

                  xbreak := '208';

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

                  xbreak := '209';

                  l_line_tbl (l_line_tbl_index).payment_term_id := 1000;

                  xbreak := '210';

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

                  xbreak := '211';

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

                  xbreak := '212';

                  l_line_tbl (l_line_tbl_index).request_date := SYSDATE;

                  xbreak := '213';

                  l_line_tbl (l_line_tbl_index).promise_date := SYSDATE;

                  xbreak := '214';

                  l_line_tbl (l_line_tbl_index).shipping_method_code := NULL;

                  xbreak := '215';

                  l_line_tbl (l_line_tbl_index).freight_terms_code := NULL;

                  xbreak := '216';

                  l_line_tbl (l_line_tbl_index).line_number := 1;

                  xbreak := '217';

                  l_line_tbl (l_line_tbl_index).ordered_quantity := 1;

                  xbreak := '218';

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

                  xbreak := '219';

                  l_line_tbl (l_line_tbl_index).unit_selling_price := NULL;

                  xbreak := '220';

                  l_line_tbl (l_line_tbl_index).ship_from_org_id := 1;

      --            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.order_number)

                                 || '-'

                                 || l_msg_data);

       

       

               COMMIT;

               xbreak := '106';

       

       

             

      xbreak := '107';

               p_order_number := l_header_rec_out.order_number;

            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;

      /