Oracle Forms

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