8 Replies Latest reply: Apr 8, 2013 10:14 AM by HamidHelal RSS

    ORA-01403: no data found ---- FRM-40735: WHEN-VALIDATE-ITEM trigger raised

    Krishn Kushwaha
      Scenario: I have one Master Detail form. after entering values in master Form, Navigate to Detail form, there I have to enter more that 5000 lines, it's very tough for user to enter huge amount of data.

      Workaround: Give one button on Master form and written a cursor to populate all the 5000(relavent) number of record on detail block.

      Issue: while populating detail data block after around 3000 record detail form start showing Error as

      ORA-01403: no data found

      --------------------------------------------------------------
      FRM-40735: WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-06502.

      Need suggestion


      Code Written on find button as below

      BEGIN
                --XX customized
                if (:ADJ_IP_CTRL.DUE_DT_FROM is null OR :ADJ_IP_CTRL.DUE_DT_TO is null) then
                fnd_message.set_string('Due Date from and Due Date To Must be entered.');
                fnd_message.Show;
                raise form_trigger_failure;
                end if;
                

           BEGIN     

                go_block('ADJ_INV_PAY');
      clear_block(no_validate);

           for inv_rec in (
                               SELECT v.invoice_num,
                v.invoice_id,
                v.invoice_type,
                v.pay_alone,
                v.exclusive_payment_flag,
                v.payment_num,
                v.amount_remaining,
                --TO_CHAR (v.amount_remaining,fnd_currency.get_format_mask(v.currency_code, 42)) char_amount_remaining,
                TO_CHAR (v.amount_remaining,'FM999G999G999G999G999G999G999G999G990D00') char_amount_remaining,
                ap_payment_schedules_pkg.get_discount_available (
                v.invoice_id,
                v.payment_num,
                :pay_sum_folder.check_date,
                :pay_sum_folder.currency_code)
                discount_available,
                /*TO_CHAR (ap_payment_schedules_pkg.get_discount_available (
                v.invoice_id,
                v.payment_num,
                :pay_sum_folder.check_date,
                :pay_sum_folder.currency_code),
                fnd_currency.get_format_mask (v.currency_code, 42))*/
                TO_CHAR (ap_payment_schedules_pkg.get_discount_available (
                v.invoice_id,
                v.payment_num,
                :pay_sum_folder.check_date,
                :pay_sum_folder.currency_code),'FM999G999G999G999G999G999G999G999G990D00')
                char_discount_available,
                ap_payment_schedules_pkg.get_discount_date (
                v.invoice_id,
                v.payment_num,
                :pay_sum_folder.check_date)
                disc_date,
                v.always_take_disc_flag,
                v.discount_amount_available,
                v.discount_date,
                v.second_discount_date,
                v.second_disc_amt_available,
                v.third_discount_date,
                v.third_disc_amt_available,
                v.gross_amount,
                v.description,
                v.accts_pay_code_combi_id,
                v.due_date,
                v.REMIT_TO_SUPPLIER_NAME,
                v.REMIT_TO_SUPPLIER_ID,
                v.REMIT_TO_SUPPLIER_SITE,
                v.REMIT_TO_SUPPLIER_SITE_ID,
                v.RELATIONSHIP_ID,
                v.external_bank_account_id,
                ieba.bank_account_num external_bank_account_num,
                ieba.bank_account_name external_bank_account_name
                FROM ap_invoices_ready_to_pay_v v, iby_ext_bank_accounts ieba
                WHERE v.party_id = :pay_sum_folder.party_id /* and v.invoice_num like :adj_inv_pay.invoice_num||'%' */
                AND ( (:pay_sum_folder.payment_type_flag =
                'M')
                OR (:pay_sum_folder.payment_type_flag =
                'R'
                AND v.invoice_type IN
                ('CREDIT',
                'STANDARD',
                'DEBIT',
                'EXPENSE REPORT',
                'MIXED',
                'AWT'))
                OR /*Bug5948003, Bug6069211*/
                (:pay_sum_folder.payment_type_flag =
                'Q'
                /*AND (v.vendor_site_id =
                :pay_sum_folder.vendor_site_id
                OR v.invoice_type =
                'PAYMENT REQUEST')*/
                AND ( (:SYSTEM.LAST_RECORD =
                'TRUE'
                AND :SYSTEM.cursor_record =
                '1')
                OR (NVL (
                v.exclusive_payment_flag,
                'N') =
                'N'
                AND NVL (
                :parameter.pay_alone,
                'N') =
                'N'))))
                AND v.currency_code = :pay_sum_folder.currency_code
                AND v.payment_method_code = :pay_sum_folder.payment_method_code
                AND NVL (v.payment_function, 'PAYABLES_DISB') =
                NVL (:pay_sum_folder.payment_function, 'PAYABLES_DISB')
                AND v.set_of_books_id = :pay_sum_folder.set_of_books_id
                AND NVL (v.future_dated_payment_ccid, -1) =
                DECODE (:parameter.manual_fdp_site_acct_src_flag,
                'Y', NVL (:parameter.site_fdp_account_ccid, -1),
                NVL (v.future_dated_payment_ccid, -1))
                AND v.external_bank_account_id = ieba.ext_bank_account_id(+)
                AND v.due_date BETWEEN :ADJ_IP_CTRL.DUE_DT_FROM and :ADJ_IP_CTRL.DUE_DT_TO
                                    ORDER BY v.due_date, UPPER (invoice_num)          
                          --added 08apr2012 (end)
                removed 08apr2012 ORDER BY UPPER (invoice_num)
                ) loop
                
                     :ADJ_INV_PAY.INVOICE_NUM := inv_rec.INVOICE_NUM;
      :ADJ_INV_PAY.INVOICE_ID := inv_rec.INVOICE_ID;
      :ADJ_INV_PAY.INVOICE_TYPE := inv_rec.INVOICE_TYPE;
      :ADJ_INV_PAY.EXCLUSIVE_PAYMENT_FLAG := inv_rec.EXCLUSIVE_PAYMENT_FLAG;
      :ADJ_INV_PAY.PAYMENT_NUM := inv_rec.PAYMENT_NUM;
      :ADJ_INV_PAY.AMOUNT_REMAINING := inv_rec.AMOUNT_REMAINING;
      :ADJ_INV_PAY.DISCOUNT_AVAILABLE:= inv_rec.DISCOUNT_AVAILABLE;
      :ADJ_INV_PAY.DISC_DATE := inv_rec.DISC_DATE;
      :ADJ_INV_PAY.ALWAYS_TAKE_DISC_FLAG := inv_rec.ALWAYS_TAKE_DISC_FLAG;
      :ADJ_INV_PAY.DISCOUNT_AMOUNT_AVAILABLE := inv_rec.DISCOUNT_AMOUNT_AVAILABLE;
      :ADJ_INV_PAY.SECOND_DISCOUNT_DATE := inv_rec.SECOND_DISCOUNT_DATE;
      :ADJ_INV_PAY.SECOND_DISC_AMT_AVAILABLE:= inv_rec.SECOND_DISC_AMT_AVAILABLE;
      :ADJ_INV_PAY.THIRD_DISCOUNT_DATE:= inv_rec.THIRD_DISCOUNT_DATE;
      :ADJ_INV_PAY.THIRD_DISC_AMT_AVAILABLE := inv_rec.THIRD_DISC_AMT_AVAILABLE;
      :ADJ_INV_PAY.GROSS_AMOUNT := inv_rec.GROSS_AMOUNT;
      :ADJ_INV_PAY.ACCTS_PAY_CODE_COMBINATION_ID := inv_rec.ACCTS_PAY_CODE_COMBI_ID;
      :ADJ_INV_PAY.DUE_DATE := inv_rec.DUE_DATE;
      :ADJ_INV_PAY.REMIT_TO_SUPPLIER_NAME := inv_rec.REMIT_TO_SUPPLIER_NAME;
      :ADJ_INV_PAY.REMIT_TO_SUPPLIER_ID := inv_rec.REMIT_TO_SUPPLIER_ID;
      :ADJ_INV_PAY.REMIT_TO_SUPPLIER_SITE := inv_rec.REMIT_TO_SUPPLIER_SITE;
      :ADJ_INV_PAY.REMIT_TO_SUPP_SITE_ID := inv_rec.REMIT_TO_SUPPLIER_SITE_ID;
      :ADJ_INV_PAY.APS_EXTERNAL_BANK_ACCOUNT_ID := inv_rec.EXTERNAL_BANK_ACCOUNT_ID;               
      --               go_item ('ADJ_INV_PAY.INVOICE_NUM');
      --               EXECUTE_TRIGGER('WHEN-VALIDATE-ITEM');
                validate(record_scope);
                
                if form_success then
                next_record;
                end if;
                
                end loop;
                first_record;
           exception
                     when others then
                     raise form_trigger_failure;
                END;
      synchronize;
      END;




      Thanks
      -Krishn