Home » Developer & Programmer » Forms » FRM 40735 key-next-item trigger raised unhandled exception ORA 01722 (ORACLE FORMS 6i)
|
|
| Re: FRM 40735 key-next-item trigger raised unhandled exception ORA 01722 [message #521781 is a reply to message #521778] |
Fri, 02 September 2011 01:23   |
|
|
I am wondering..heres the old code
declare
vSale number;
vCust number;
vApl number;
vIs number;
vOnline number; -- MU 2011-076
al_button number;
vPymntOr number;
begin
if :rg_trans_type = 'S' and :txt_appl_no is not null then
if func_is_valid_appl_format(:txt_appl_no) then
--20090908HFS
if substr(:txt_appl_no,1,2) = 'OO' and to_number(substr(:txt_appl_no,5,6)) > 346000 then
set_alert_property ('note_alert', title, 'Warning');
set_alert_property ('note_alert', alert_message_text, 'The ''OO'' series is until 346000 only. Please check the Application No.');
al_button := show_alert ('note_alert');
raise form_trigger_failure;
end if;
if substr(:txt_appl_no,1,2) = 'PA' and to_number(substr(:txt_appl_no,5,6)) < 346001 then
set_alert_property ('note_alert', title, 'Warning');
set_alert_property ('note_alert', alert_message_text, 'The ''PA'' series is from 346001. Please check the Application No.');
al_button := show_alert ('note_alert');
raise form_trigger_failure;
end if;
--chris o herrera, MU 2011-061
if :txt_plan_type not in ('212', '214') and substr(:txt_appl_no,1,2) = 'PP' and to_number(substr(:txt_appl_no,3,8)) <= 0 then
set_alert_property ('note_alert', title, 'Warning');
set_alert_property ('note_alert', alert_message_text, 'The ''PP'' series started from 1. Please check the Application No.');
set_alert_property ('note_alert', alert_message_text, 'Given Plan Type does not match the given PAF number'); -- chris o herrera, 06082011 validation for the series number for the application
al_button := show_alert ('note_alert');
raise form_trigger_failure;
end if;
if :txt_plan_type <> '215' and substr(:txt_appl_no,1,2) = 'PG' and to_number(substr(:txt_appl_no,3,8)) <= 0 then
set_alert_property ('note_alert', title, 'Warning');
set_alert_property ('note_alert', alert_message_text, 'The ''PG'' series started from 1. Please check the Application No.');
set_alert_property ('note_alert', alert_message_text, 'Given Plan Type does not match the given PAF number'); -- chris o herrera, 06082011 validation for the series number for the application
al_button := show_alert ('note_alert');
raise form_trigger_failure;
end if;
if substr(:txt_appl_no,1,1) = '4' and to_number(substr(:txt_appl_no,2,9)) <= 0 then
set_alert_property ('note_alert', title, 'Warning');
set_alert_property ('note_alert', alert_message_text, 'The ''4'' series started from 1. Please check the Application No.');
al_button := show_alert ('note_alert');
raise form_trigger_failure;
else
--20090908HFS
select count(*)
into vCust
from customer
where cust_application_no = :txt_appl_no;
select count(*)
into vSale
from suspf_sales
where ssales_application_no = :txt_appl_no;
select count(*)
into vPymntOr
from pymnt_or
where or_appl_nr = :txt_appl_no;
select count(*)
into vApl
from appl_or_dtls
where appl_appl_nr = :txt_appl_no;
select count(*)
into vIs
from is_appl_link
where isappl_appl_nr = :txt_appl_no;
if nvl(vCust,0) + nvl(vPymntOr,0) + nvl(vSale,0) > 0 and :rg_search = 0 and :rg_ts_code not in (2,3) then
set_alert_property ('note_alert', title, 'Warning');
set_alert_property ('note_alert', alert_message_text, 'Application No. ' ||:txt_appl_no||' already exists.');
al_button := show_alert ('note_alert');
raise form_trigger_failure;
elsif nvl(vIs,0) > 0 then
set_alert_property ('note_alert', title, 'Warning');
set_alert_property ('note_alert', alert_message_text, 'Application No. ' ||:txt_appl_no||' already exists.');
al_button := show_alert ('note_alert');
raise form_trigger_failure;
elsif nvl(vPymntOr,0) = 0 and nvl(vApl,0) > 0 then
if :rg_search = 0 then
select appl_ph_name,
appl_ph_address,
appl_ph_birthdate,
appl_prod_category,
appl_mode,
appl_term,
appl_gross_price,
appl_amount_due
into :txt_ph_name,
:txt_ph_add,
:txt_ph_birthdate, -- chris o herrera, 03042011
:txt_plan_type,
:txt_mode,
:txt_term,
:txt_gp,
:txt_amt_due
from appl_or_dtls
where appl_appl_nr = :txt_appl_no
and appl_trans_id = (select min(appl_trans_id)
from appl_or_dtls
where appl_appl_nr = :txt_appl_no);
else
select appl_ph_name,
appl_ph_address,
appl_ph_birthdate, -- additional field for appl_or_dtls, birthdate, chris herrera 03032011
appl_prod_category,
appl_mode,
appl_term,
appl_gross_price,
appl_amount_due
into :txt_ph_name,
:txt_ph_add,
:txt_ph_birthdate, -- chris o herrera, 03042011
:txt_plan_type,
:txt_mode,
:txt_term,
:txt_gp,
:txt_amt_due
from appl_or_dtls
where appl_appl_nr = :txt_appl_no
and appl_trans_id = (select min(appl_trans_id)
from appl_or_dtls
where appl_appl_nr = :txt_appl_no);
--and appl_trans_id = :disp_trans_id;
end if;
end if; --20090908HFS
end if;
end if;
else
set_alert_property ('note_alert', title, 'Warning');
set_alert_property ('note_alert', alert_message_text, 'Invalid format for application ' ||:txt_appl_no);
al_button := show_alert ('note_alert');
raise form_trigger_failure; --20100205HFS
end if;
end;
and i update my program to this
declare
vSale number;
vCust number;
vApl number;
vIs number;
vOnline number; -- MU 2011-076
al_button number;
vPymntOr number;
begin
if :rg_trans_type = 'S' and :txt_appl_no is not null then
if func_is_valid_appl_format(:txt_appl_no) then
--20090908HFS
if substr(:txt_appl_no,1,2) = 'OO' and to_number(substr(:txt_appl_no,5,6)) > 346000 then
set_alert_property ('note_alert', title, 'Warning');
set_alert_property ('note_alert', alert_message_text, 'The ''OO'' series is until 346000 only. Please check the Application No.');
al_button := show_alert ('note_alert');
raise form_trigger_failure;
end if;
if substr(:txt_appl_no,1,2) = 'PA' and to_number(substr(:txt_appl_no,5,6)) < 346001 then
set_alert_property ('note_alert', title, 'Warning');
set_alert_property ('note_alert', alert_message_text, 'The ''PA'' series is from 346001. Please check the Application No.');
al_button := show_alert ('note_alert');
raise form_trigger_failure;
end if;
--chris o herrera, MU 2011-061
if :txt_plan_type not in ('212', '214') and substr(:txt_appl_no,1,2) = 'PP' and to_number(substr(:txt_appl_no,3,8)) <= 0 then
set_alert_property ('note_alert', title, 'Warning');
set_alert_property ('note_alert', alert_message_text, 'The ''PP'' series started from 1. Please check the Application No.');
set_alert_property ('note_alert', alert_message_text, 'Given Plan Type does not match the given PAF number'); -- chris o herrera, 06082011 validation for the series number for the application
al_button := show_alert ('note_alert');
raise form_trigger_failure;
end if;
if :txt_plan_type <> '215' and substr(:txt_appl_no,1,2) = 'PG' and to_number(substr(:txt_appl_no,3,8)) <= 0 then
set_alert_property ('note_alert', title, 'Warning');
set_alert_property ('note_alert', alert_message_text, 'The ''PG'' series started from 1. Please check the Application No.');
set_alert_property ('note_alert', alert_message_text, 'Given Plan Type does not match the given PAF number'); -- chris o herrera, 06082011 validation for the series number for the application
al_button := show_alert ('note_alert');
raise form_trigger_failure;
end if;
if substr(:txt_appl_no,1,1) = '4' and to_number(substr(:txt_appl_no,2,9)) <= 0 then
set_alert_property ('note_alert', title, 'Warning');
set_alert_property ('note_alert', alert_message_text, 'The ''4'' series started from 1. Please check the Application No.');
al_button := show_alert ('note_alert');
raise form_trigger_failure;
else
--20090908HFS
select count(*)
into vCust
from customer
where cust_application_no = :txt_appl_no;
select count(*)
into vSale
from suspf_sales
where ssales_application_no = :txt_appl_no;
select count(*)
into vPymntOr
from pymnt_or
where or_appl_nr = :txt_appl_no;
select count(*)
into vApl
from appl_or_dtls
where appl_appl_nr = :txt_appl_no;
select count(*)
into vIs
from is_appl_link
where isappl_appl_nr = :txt_appl_no;
--chris o herrera, MU-2011-076
select count (*)
into VOnline
from online_customer
where onlc_application_no = :txt_appl_no;
if nvl(vCust,0) + nvl(vPymntOr,0) + nvl(vSale,0) > 0 and :rg_search = 0 and :rg_ts_code not in (2,3) then
set_alert_property ('note_alert', title, 'Warning');
set_alert_property ('note_alert', alert_message_text, 'Application No. ' ||:txt_appl_no||' already exists.');
al_button := show_alert ('note_alert');
raise form_trigger_failure;
elsif nvl(vIs,0) > 0 then
set_alert_property ('note_alert', title, 'Warning');
set_alert_property ('note_alert', alert_message_text, 'Application No. ' ||:txt_appl_no||' already exists.');
al_button := show_alert ('note_alert');
raise form_trigger_failure;
i add this to the bottom
elsif nvl(vOnline,0) > 0 then
if :rg_search = 0 then
select onlc_last_name||', '||onlc_first_name||' '||onlc_middle_name online_name,
onlc_home_number||' '||onlc_home_street||', '||onlc_home_village||', '||onlc_home_city||', '||onlc_home_province||' '||onlc_home_zip online_address,
onlc_birth_date,
onlc_prod_category,
onlc_mode,
onlc_term,
onlc_gross_price,
onlc_initial_pymnt
into :txt_ph_name,
:txt_ph_add,
:txt_ph_birthdate, -- chris o herrera, 03042011
:txt_plan_type,
:txt_mode,
:txt_term,
:txt_gp,
:txt_amt_due
from online_customer
where onlc_application_no = :txt_appl_no;
else
select onlc_last_name||', '||onlc_first_name||' '||onlc_middle_name online_name,
onlc_home_number||' '||onlc_home_street||', '||onlc_home_village||', '||onlc_home_city||', '||onlc_home_province||' '||onlc_home_zip online_address,
onlc_birth_date,
onlc_prod_category,
onlc_mode,
onlc_term,
onlc_gross_price,
onlc_initial_pymnt
into :txt_ph_name,
:txt_ph_add,
:txt_ph_birthdate,
:txt_plan_type,
:txt_mode,
:txt_term,
:txt_gp,
:txt_amt_due
from online_customer
where onlc_application_no = :txt_appl_no;
end if;
go_item('TXT_AMT_PAID');
elsif nvl(vPymntOr,0) = 0 and nvl(vApl,0) > 0 then
if :rg_search = 0 then
select appl_ph_name,
appl_ph_address,
appl_ph_birthdate,
appl_prod_category,
appl_mode,
appl_term,
appl_gross_price,
appl_amount_due
into :txt_ph_name,
:txt_ph_add,
:txt_ph_birthdate, -- chris o herrera, 03042011
:txt_plan_type,
:txt_mode,
:txt_term,
:txt_gp,
:txt_amt_due
from appl_or_dtls
where appl_appl_nr = :txt_appl_no
and appl_trans_id = (select min(appl_trans_id)
from appl_or_dtls
where appl_appl_nr = :txt_appl_no);
else
select appl_ph_name,
appl_ph_address,
appl_ph_birthdate, -- additional field for appl_or_dtls, birthdate, chris herrera 03032011
appl_prod_category,
appl_mode,
appl_term,
appl_gross_price,
appl_amount_due
into :txt_ph_name,
:txt_ph_add,
:txt_ph_birthdate, -- chris o herrera, 03042011
:txt_plan_type,
:txt_mode,
:txt_term,
:txt_gp,
:txt_amt_due
from appl_or_dtls
where appl_appl_nr = :txt_appl_no
and appl_trans_id = (select min(appl_trans_id)
from appl_or_dtls
where appl_appl_nr = :txt_appl_no);
--and appl_trans_id = :disp_trans_id;
end if;
end if; --20090908HFS
end if;
end if;
else
set_alert_property ('note_alert', title, 'Warning');
set_alert_property ('note_alert', alert_message_text, 'Invalid format for application ' ||:txt_appl_no);
al_button := show_alert ('note_alert');
raise form_trigger_failure; --20100205HFS
end if;
end;
then i got an error.
please help
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Sep 08 23:27:48 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
