declare

	n_months_between   NUMBER;
  d_same_day_occur   DATE;
  n_years            NUMBER;
  n_months           NUMBER;
  n_days             NUMBER;
  v_temp	     NUMBER;
  v_temp1	     number;
  temp_years	     NUMBER:=0;
BEGIN
	
	
  n_months_between := TRUNC(MONTHS_BETWEEN(:txt_received_dt, :txt_ph_birthdate));
  n_years          := TRUNC(n_months_between / 12);
  n_months         := n_months_between - (12*n_years);
  d_same_day_occur := ADD_MONTHS(:txt_ph_birthdate, n_months_between);
  n_days           := :txt_received_dt - d_same_day_occur;


if :txt_plan_type = 212 then     -- MSB Prime
   if :txt_ph_birthdate > sysdate then
   set_alert_property('note_alert',title,'Alert');
   set_alert_property('note_alert',alert_message_text,'Birthdate is greater than the system date.');
raise form_trigger_failure;
  		
elsif ((n_years*10000 + n_months*100 + n_days) > 170600) and ((n_years*10000 + n_months*100 + n_days) > 400529) THEN
  set_alert_property('note_alert',title,'Alert');
  set_alert_property('note_alert',alert_message_text,'MSB Prime is for Age 40 and Below only.');raise form_trigger_failure; 
else
  go_item('txt_term');	
 end if;
 	 
 elsif :txt_plan_type = 214 then  -- MSB Premium
if :txt_ph_birthdate > sysdate then	 	  	 set_alert_property('note_alert',title,'Alert');
 	 	  	set_alert_property('note_alert',alert_message_text,'Birthdate is greater than the system date.');
raise form_trigger_failure;	
 	 	  	
elsif ((n_years*10000 + n_months*100 + n_days) > 400600) and ((n_years*10000 + n_months*100 + n_days) > 550529) THEN
 	 	  		set_alert_property('note_alert',title,'Alert');
  			  set_alert_property('note_alert',alert_message_text,'MSB Premium is for Age 41 to 55 only.');
 raise form_trigger_failure;
 else 
 go_item('txt_term');
 end if;
 end if;
end;

elsif ((n_years*10000 + n_months*100 + n_days) > 170600) and ((n_years*10000 + n_months*100 + n_days) > 400529) THEN

BEGIN

  SELECT <columns> INTO <whatever>
  FROM <table>
  WHERE ....

EXCEPTION WHEN NO_DATA_FOUND THEN

  <do whatever needs doing if the data doesn't exist>

END;

begin
  begin
    select cust_plan_pack, cust_is_code, nvl(cust_avail_status,0)
    into    :cust_plan_pack, v_is, :cust_avail_status from customer
    where   cust_contr        = :tran_contr
    and    cust_check_digit = :chk_digit;

  exception                      -- coh07052010
    when   no_data_found then 
      v_is     := null;
  end;


  if :tran_contr is null then
    message('Please enter contract number!',acknowledge);
    message(' ',no_acknowledge);
    set_item_property('tran_contr',visual_attribute,'suspended');
    go_item('tran_contr');
    return;
    
  else
    set_item_property('tran_contr',visual_attribute,'enable');
  end if;
  
  if :chk_digit is null then
    message('Please enter check digit!',acknowledge);
    message(' ',no_acknowledge);
    set_item_property('chk_digit',visual_attribute,'suspended');
    go_item('chk_digit');
    return;

  else
    set_item_property('chk_digit',visual_attribute,'enable');

  end if;