Link to home
Start Free TrialLog in
Avatar of chalie001
chalie001

asked on

not able to insert into temp table

hi am not able to insert into in my temp table when i select values from my table using oracle forms i have procedure i can show
the file are hear https://drive.google.com/open?id=0B3dKl5pXBYMqdXM2ZXduRUU4VTA
this is my procedure am in oracle database 11g and am using forms 11gR2
PROCEDURE PROC_SELECT_DATA (o_cnt OUT NUMBER)IS
BEGIN
	DECLARE
		ws_unit_cde             number(06);
		wrk_gsb_unit            number(06);
		ws_unit                 number(06);
		ws_char_unit_cde        varchar2(06);
		ws_eng_abbr_nme         varchar2(30);
		wrk_gsb_desc            varchar2(30);
		ws_unit_line_ind        varchar2(01);
		ws_act_des              varchar2(01);
		ws_origin               varchar2(100);
		ws_ser_no               varchar2(30);
		ws_inst_ser_no          varchar2(30);
		ws_scin                 number(11);
		ws_sin                  number(11);
		ws_unit_holding         number(06);
		ws_dept_holding         number(06);
		ws_unit_located_at      number(06);
		ws_cond_cde             varchar2(01);
		ws_srv_status_cde       varchar2(3);
		ws_pers_located_at      varchar2(15);
		ws_itm_purch_prc        number(15,4);
		wrk_purch_prc           varchar2(3);
		ws_heritage_ind         varchar2(1);
    wrk_herit_asset         varchar2(3);		
		ws_res_val              number(15,4);
		ws_fair_val             number(15,4);
		wrk_fair_val            varchar2(3);
		ws_current_val          number(15,4);
		ws_accum_improve_val    number(15,4);
		ws_accum_impair_val     number(15,4);
		ws_useful_lfe           number(4);
		ws_rem_useful_lfe       number(4);
		ws_ass_cat              varchar2(2);
		wrk_ass_cat             varchar2(5);
		ws_dep_sched            varchar2(1);
		wrk_dep_sched           varchar2(15);
		ws_start_dep_dte        date;
		ws_close_reason         varchar2(20);
		ws_lst_dep_fin_YYYYMM   number(6);
		ws_prov_reg_no          varchar2(20);
		ws_nsn                  varchar2(16);
		ws_itm_type_cde         varchar2(01);
		ws_struc_lev_cde        varchar2(01);
		ws_agency_ser_cntrl_ind varchar2(01);
		ws_cntr_body_cde        varchar2(02);
		ws_org_func             varchar2(02);
		ws_nsn_descr            varchar2(200);
		ws_mec                  varchar2(04);
		ws_me_descr             varchar2(50);
		ws_sort_cc              number(02);
		ws_sort_srv             number(02);
		ws_err_ind              varchar2(01);
		ws_err_msg              varchar2(80);
		ws_ldesc                varchar2(50);
		ws_sdesc                varchar2(25);
		ws_short_nme            varchar2(30);
		ws_long_nme             varchar2(50);
		ws_stat_ind             varchar2(01);
		ws_oli_ind              varchar2(01);
		ws_pers_details         varchar2(100);
		ws_desc                 varchar2(150);
		ws_rank                 varchar2(15);
		ws_pers_found           varchar2(01);
		ws_un_no                varchar2(10);
		ws_dummy                varchar2(01);
		ws_m_nsn                varchar2(16);
		proc_cfg_tab_no         number;
		proc_mec                varchar2(04);
		proc_mec_nsn            varchar2(16);
		proc_cfg_tab_desc       varchar2(200);
		proc_mec_desc           varchar2(200);
		proc_mec_nsn_desc       varchar2(200);
		proc_exist_corp_ind     varchar2(01);
		proc_err_ind            varchar2(01);
		proc_err_msg            varchar2(80);
		proc_me_niin            varchar2(13);
		wrk_nsn_desc            varchar2(200);
		wrk_err                 varchar2(1);
		wrk_msg                 varchar2(50);
		wrk_nsn                 varchar2(16);
		v_nsn                   varchar2(16);
		wrk_totals              varchar2(3);
		wrk_all_units           varchar2(3);
		ws_found                varchar2(1);
		ws_dep_active_ind       varchar2(1);
		ws_dep_closed_ind       varchar2(1);
		ws_act_rv_dte           date;
		ws_purch_price          number(15,4);
		ws_cnt                  number := 0;
		
	BEGIN
    ws_unit_cde := 0;
    
		IF :key.wrk_org_func  = 'HQ' or
		  (:key.wrk_org_func  = 'CM' and
			 :key.scr_all_units = 'Y') THEN
			 ws_unit_cde     := 0;
		 	 ws_eng_abbr_nme := '';
		ELSE	  
		   ws_unit_cde     := :key.scr_req_unit;
	     ws_eng_abbr_nme := :key.scr_unit_desc;
		END IF;
		
	--	message('testt09');
	--	message(:key.scr_ser_no);
	
IF :key.scr_ser_no is not null then
     DECLARE
     	 CURSOR         sel_ser_no is
       select         s.sin,
                      s.ser_no, 
       	              e.scin,
       	              e.unit_holding,
       	              me_cat_cde||me_family_cde||me_var_cde mec,
       	              i.nsc||'-'||substr(i.niin,1,2)||'-'||
  	                  substr(i.niin,3,3)||'-'||substr(i.niin,6,4) nsn,
  	                  i.agency_ser_cntrl_ind,
       	              me_desc
       from           ils.me_master_rec          m,
       	              ils.sss_entry_index        s,
	         		        ils.sss_er_core            e,
	         		        ils.item_data              i
      where           s.ser_no			    = :key.scr_ser_no
       and            e.scin 				    = s.scin
			 and            s.sin_change_reas is null
  	   and            s.sin_change_dte is null 
			 and           ((nvl(e.unit_holding,0) = nvl(ws_unit_cde,0) and
  	                  nvl(ws_unit_cde,0) > 0) or
  	                  nvl(ws_unit_cde,0) = 0) 
	     and            m.sin (+)            = s.sin
 	     and            i.sin             = s.sin
-- 	      or             s.sin            = :key.wrk_sin)
  	   and            (m.me_life_cyc (+) != '08' and
                      m.me_rec_stat (+) not in ('C','X'));
	   	 
     BEGIN
     	 FOR rec1 IN sel_ser_no
	     LOOP
      BEGIN
			     select itm_purch_prc,
       	 	        heritage_ind,
       	 	        res_val,
       	 	        fair_val,
       	 	        current_val,
       	 	        accum_improve_val,
       	 	        accum_impair_val,
       	 	        useful_lfe,
       	 	        rem_useful_lfe,
       	 	        ass_cat,
       	 	        act_rv_dte,
       	 	        dep_active_ind,
       	 	        dep_closed_ind,
       	 	        start_dep_dte,
       	 	        lst_dep_fin_YYYYMM,
       	 	        close_reason
			     into   
			            ws_itm_purch_prc,
       	 	        ws_heritage_ind,
       	 	        ws_res_val,
       	 	        ws_fair_val,
       	 	        ws_current_val,
       	 	        ws_accum_improve_val,
       	 	        ws_accum_impair_val,
       	 	        ws_useful_lfe,
       	 	        ws_rem_useful_lfe,
       	 	        ws_ass_cat,
       	 	        ws_act_rv_dte,
       	 	        ws_dep_active_ind,
       	 	        ws_dep_closed_ind,
       	 	        ws_start_dep_dte,
       	 	        ws_lst_dep_fin_YYYYMM,
       	 	        ws_close_reason
				    from   ils.lsst_asset_fin  a
  			   where  scin            = rec1.scin
  			     /*and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
   																	      AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/
   																	      
   					 and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd'))) 
             AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE));												      
   	         																      
  			   ws_found := 'Y';
      EXCEPTION                         
         when no_data_found THEN
			      ws_found := 'N';
-- 			      goto nxt_rec;
            null;
      END;
      
      ws_scin := rec1.scin; 
      ws_sin := rec1.sin;
   	  ws_unit_holding := nvl(rec1.unit_holding,0);
      ws_mec := rec1.mec;
      ws_me_descr := rec1.me_desc;
      ws_ser_no := rec1.ser_no;
      ws_inst_ser_no := '';       	   	  
	     	
      	IF :key.scr_req_unit is null or
       	   :key.wrk_org_func = 'HQ' then
       	   	  ws_unit := ws_unit_holding;
       	ELSE
       	 	 	  IF :key.scr_all_units = 'Y' then
       	 	 	     ws_unit := 0;
       	 	       BEGIN
                   select 'X' 
                     into ws_dummy
                     from dual
                    where ws_unit_holding in (select unit_cde
                                                from ils.unt
                                               where comnd_cde = :key.wrk_comnd_cde);
                   ws_unit := ws_unit_holding;
       	 	       exception
       	 	    	   when no_data_found THEN
       	 	    	        ws_unit := 0;
      	 	    	        goto nxt_rec;
       	 	       END;
       	 	 	  ELSE
       	 	 	  	ws_unit := ws_unit_cde;
       	      END IF;
	     END IF;
	     	IF :key.scr_all_units = 'Y' then
		        wrk_gsb_unit := :key.scr_req_unit;
		        wrk_gsb_desc := :key.scr_unit_name;
		    ELSE
		     BEGIN
		     	    select unit_cde,
			  	           eng_abbr_nme
				      into   wrk_gsb_unit,
			  	           wrk_gsb_desc
  			      from 	 ils.unt
				      where  comnd_cde = :key.wrk_comnd_cde 
				      and    org_func  = 'CM';
			      END;
     END IF;
     
        IF :key.scr_me_cde is not null then
      	 	 	  IF :key.scr_var_cde is not null then
              	 IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then 
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_fam_cde is not null then 
              	 IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_cat_cde is not null then
              	 IF :key.scr_cat_cde = substr(ws_mec,1,1) then
              	 	  null;
              	 ELSE
       	 	 	  	    goto nxt_rec;
         	 	 	   END IF;
              END IF;
           ELSE
      	 	    :key.scr_me_cde  := ws_mec;
      	 	    :key.scr_mec_desc     := ws_me_descr;    
       	 	 END IF;   
-- Unit Description
           IF nvl(ws_unit,0) > 0 then
       	 	    BEGIN
       	 	      select eng_abbr_nme
       	 	        into ws_eng_abbr_nme
       	 	        from ils.unt
       	 	       where unit_cde = ws_unit;
       	 	      exception
       	 	        when no_data_found then
       	 	             ws_eng_abbr_nme := ws_unit ||' Unit not Found';
       	      END;
 	 	       END IF;
 	 	       
 	 	      IF :key.wrk_full_nsn is not null then
       	   	  IF :key.wrk_full_nsn = ws_nsn then
       	   	  	 null;
       	   	  ELSE
      	   	  	 goto nxt_rec;
       	   	  END IF;
       	  END IF;     
 
      BEGIN
			     select ser_no
				   into   ws_ser_no
  			   from   ils.sss_entry_index
  			   where  scin            = rec1.scin
  			   and    SIN_CHANGE_DTE is null;
  		EXCEPTION
      	  when no_data_found then
      	  message('No serial number for this ICN');
      	  raise form_trigger_failure;
	    END; 
	     
	     wrk_nsn := substr(rec1.nsn,1,4)||substr(rec1.nsn,6,2)||
                  substr(rec1.nsn,9,3)||substr(rec1.nsn,13,4);
                  
       wrk_nsn_desc := null;
       
      -- wrk_nsn   := nvl(wrk_nsn,'');
     --  message('error12');
     if :key.SCR_ICN_2ND_2 is not null then
       PROC_NSN_DESC(wrk_nsn,
    	                wrk_nsn_desc,
       	              wrk_err,
       	              wrk_msg); 
      end if;
       	             
      
      BEGIN
           	 insert into ils.lsst_prt_asset_fin
       	 	     (user_id,
       	 	      dte_tme_stamp,
       	 	      unit_cde,
       	 	      nsn,
       	 	      ser_no,
       	 	      unit_desc,
       	 	      nsn_desc,
       	 	      me_cde,
       	 	      me_desc,
       	 	      gsb_unit_cde,
       	 	      gsb_desc,
       	 	      itm_purch_prc,
       	 	      heritage_ind,
       	 	      res_val,
       	 	      fair_val,
       	 	      current_val,
       	 	      accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        dep_sched,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        close_reason)
       	 	   values 
       	  	    (:global.ils_force_no,
                :key.wrk_dte,
       	 	      ws_unit,
       	 	      wrk_nsn,
       	 	      rec1.ser_no,
       	 	      ws_eng_abbr_nme,
       	 	      wrk_nsn_desc,
       	 	      ws_mec,
       	 	      ws_me_descr,
       	 	      wrk_gsb_unit,
       	 	      wrk_gsb_desc,
       	 	      ws_itm_purch_prc,
       	 	      ws_heritage_ind,
       	 	      ws_res_val,
       	 	      ws_fair_val,
       	 	      ws_current_val,
       	 	      ws_accum_improve_val,
       	        ws_accum_impair_val,
       	        ws_useful_lfe,
       	        ws_rem_useful_lfe,
       	        ws_ass_cat,
       	        ws_dep_sched, 
       	        ws_start_dep_dte,
       	        ws_lst_dep_fin_YYYYMM,
       	        ws_close_reason);
      EXCEPTION
       	  when dup_val_on_index then
       	  null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);
--         raise form_trigger_failure;       	        
      END;
     -- end if;
ws_cnt := ws_cnt + 1;
<< nxt_rec >>
     null;
     o_cnt := ws_cnt;          
--message('ws_cnt:'||ws_cnt);pause;
       END LOOP;
     END;	
ELSE
		BEGIN
      DECLARE
  	    cursor ser_data is
  	      select a.ser_no, 
  	             a.scin,
  	             a.sin,
  	             nvl(b.unit_holding,0) unit_holding,
  	             nvl(b.dept_holding,0) dept_holding,
  	             nvl(b.unit_located_at,0) unit_located_at,
  	             b.cond_cde,
  	             b.srv_status_cde,
  	             b.pers_located_at,
  	             b.nha_sin,
  	             b.nha_ser_no,
  	             c.nsc||'-'||substr(c.niin,1,2)||'-'||
  	             substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
  	             c.itm_type_cde,
  	             c.struc_lev_cde,
  	             c.agency_ser_cntrl_ind,
  	             d.itm_nme,
  	             e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
  	             e.me_desc  	             
  	        from ils.sss_entry_index a,
  	             ils.sss_er_core b,
  	             ils.item_data c,
  	             ils.names d,
  	             ils.me_master_rec e
  	       where a.sin_change_reas is null
  	         and a.sin_change_dte is null
  	         and b.scin = a.scin
  	         and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
  	               nvl(ws_unit_cde,0) > 0) or
  	               nvl(ws_unit_cde,0) = 0)
  	         and c.sin = a.sin
  	         and d.sin = c.sin
  	         and d.itm_nme_type = '1'
  	         and d.name_seq = '1'
  	         and e.sin (+) = a.sin
  	         and (e.me_life_cyc (+) != '08' and
                  e.me_rec_stat (+) not in ('C','X'))
           order by b.unit_holding, a.ser_no;

       BEGIN
       	 For rec1 in ser_data LOOP
         BEGIN     	 	
       	 Select scin,
       	        itm_purch_prc,
       	        heritage_ind,
       	        res_val,
       	        fair_val,
       	        current_val,
       	        accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        act_rv_dte,
       	        dep_active_ind,
       	 	      dep_closed_ind,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        close_reason
       	  into  ws_scin,
       	        ws_itm_purch_prc,
       	        ws_heritage_ind,
       	        ws_res_val,
       	        ws_fair_val,
       	        ws_current_val,
       	        ws_accum_improve_val,
       	        ws_accum_impair_val,
       	        ws_useful_lfe,
       	        ws_rem_useful_lfe,
       	        ws_ass_cat,
       	        ws_act_rv_dte,
     	 	        ws_dep_active_ind,
     	 	        ws_dep_closed_ind,
       	        ws_start_dep_dte,
       	        ws_lst_dep_fin_YYYYMM,
       	        ws_close_reason
       	  from  ils.lsst_asset_fin a
       	 where  rec1.scin = scin
       	   /*and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
   																  	  AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/
   																  	  
   		and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd'))) 
             AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE));															  	  
   																  	  
   																  	  
 			   ws_found := 'Y';
       EXCEPTION                         
         when no_data_found then
            ws_found := 'N';
--              goto nxt_rec;
              null;
       END;
--message('ws_scin from table: '||ws_scin);pause;           	 	
   	 	 ws_ser_no := '';
   	 	 ws_inst_ser_no := '';
       	   IF rec1.srv_status_cde = 'C02' then
       	   	  ws_ser_no := rec1.nha_ser_no;
       	   	  ws_inst_ser_no := rec1.ser_no;
       	   	  ws_pers_located_at := rec1.pers_located_at;
       	      BEGIN
       	   	    select a.scin, a.sin,
       	   	           b.srv_status_cde,
       	   	           b.unit_holding,
       	   	           nvl(b.dept_holding,0),
       	   	           nvl(b.unit_located_at,0),
       	   	           b.cond_cde,
       	   	           lpad(to_char(c.nsc),4,'0')||'-'||substr(c.niin,1,2)||'-'||
       	   	           substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
       	   	           c.itm_type_cde,
       	   	           c.struc_lev_cde,
       	   	           c.agency_ser_cntrl_ind,
       	   	           c.cntr_body_cde,
       	   	           e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
       	   	           e.me_desc
       	   	      into ws_scin, ws_sin,
       	   	           ws_srv_status_cde,
       	   	           ws_unit_holding,
       	   	           ws_dept_holding,
       	   	           ws_unit_located_at,
       	   	           ws_cond_cde,
       	   	           ws_nsn,
       	   	           ws_itm_type_cde,
       	   	           ws_struc_lev_cde,
       	   	           ws_agency_ser_cntrl_ind,
       	   	           ws_cntr_body_cde,
       	   	           ws_mec,
       	   	           ws_me_descr
       	   	      from ils.sss_entry_index a,
       	   	           ils.sss_er_core b,
       	   	           ils.item_data c,
       	   	           ils.names d,
       	   	           ils.me_master_rec e
       	   	     where a.ser_no = rec1.nha_ser_no
       	   	       and a.sin = rec1.nha_sin
       	   	       and a.sin_change_reas is null
       	   	       and a.sin_change_dte is null
       	   	       and b.scin = a.scin
       	   	       and c.sin = a.sin
       	   	       and d.sin = c.sin
       	   	       and d.itm_nme_type = '1'
       	   	       and d.name_seq = '1'
       	   	       and e.sin (+) = a.sin
       	   	       and (e.me_life_cyc (+) != '08' and
       	   	            e.me_rec_stat (+) not in ('C','X'));
      	      exception
       	      	  when no_data_found then
       	      	       ws_ser_no := 'INVALID - '||ws_ser_no;
       	      	       ws_srv_status_cde := '';
       	      END;
       	   ELSE
       	   	  ws_scin := rec1.scin; 
      	   	  ws_sin := rec1.sin;
   	          ws_srv_status_cde := rec1.srv_status_cde;
       	   	  ws_unit_holding := nvl(rec1.unit_holding,0);
       	   	  ws_dept_holding := nvl(rec1.dept_holding,0);
       	   	  ws_unit_located_at := nvl(rec1.unit_located_at,0);
       	   	  ws_cond_cde := rec1.cond_cde;
       	   	  ws_pers_located_at := rec1.pers_located_at;
       	   	  ws_nsn := rec1.nsn;
       	   	  ws_itm_type_cde := rec1.itm_type_cde;
       	   	  ws_struc_lev_cde := rec1.struc_lev_cde;
       	   	  ws_agency_ser_cntrl_ind := rec1.agency_ser_cntrl_ind;
              ws_mec := rec1.mec;
              ws_me_descr := rec1.me_desc;
      	   	  ws_ser_no := rec1.ser_no;
       	   	  ws_inst_ser_no := '';
       	   END IF;
-- Set up values for print table       	                     	                      
       	   IF :key.scr_req_unit is null or
       	   	  :key.wrk_org_func = 'HQ' then
       	   	  ws_unit := ws_unit_holding;
      	   ELSE
       	 	 	  IF :key.scr_all_units = 'Y' then
       	 	 	     ws_unit := 0;
       	 	       BEGIN
                   select 'X' 
                     into ws_dummy
                     from dual
                    where ws_unit_holding in (select unit_cde
                                                from ils.unt
                                               where comnd_cde = :key.wrk_comnd_cde);
                   ws_unit := ws_unit_holding;
                 exception
       	 	    	   when no_data_found then
       	 	    	        ws_unit := 0;
       	 	    	        goto nxt_rec;
       	 	       END;
       	 	 	  ELSE
       	 	 	  	ws_unit := ws_unit_cde;
      	      END IF;
       	   END IF;
       	
       	IF :key.scr_all_units = 'Y' then
		        wrk_gsb_unit := :key.scr_req_unit;
		        wrk_gsb_desc := :key.scr_unit_name;
		     ELSE
		     BEGIN
		     	    select unit_cde,
			  	           eng_abbr_nme
				      into   wrk_gsb_unit,
			  	           wrk_gsb_desc
  			      from 	 ils.unt
				      where  comnd_cde = :key.wrk_comnd_cde 
				      and    org_func  = 'CM';
			      END;
      END IF;
       	   
       	  IF :key.wrk_full_nsn is not null then
       	   	  IF :key.wrk_full_nsn = ws_nsn then
       	   	  	 null;
       	   	  ELSE
       	   	  	 goto nxt_rec;
       	   	  END IF;
       	  END IF; 

       	  IF :key.scr_me_cde is not null then
      	 	 	  IF :key.scr_var_cde is not null then
              	 IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then 
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_fam_cde is not null then 
              	 IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_cat_cde is not null then
              	 IF :key.scr_cat_cde = substr(ws_mec,1,1) then
              	 	  null;
              	 ELSE
       	 	 	  	    goto nxt_rec;
         	 	 	   END IF;
              END IF;
      	 	 ELSE
      	 	    :key.scr_me_cde  := ws_mec;
      	 	    :key.scr_mec_desc     := ws_me_descr;   	
       	 	 END IF;
  
-- Unit Description
           IF nvl(ws_unit,0) > 0 then
       	 	    BEGIN
       	 	      select eng_abbr_nme
       	 	        into ws_eng_abbr_nme
       	 	        from ils.unt
       	 	       where unit_cde = ws_unit;
       	 	      exception
       	 	        when no_data_found then
       	 	             ws_eng_abbr_nme := ws_unit ||' Unit not Found';
       	      END;
 	 	       END IF;

--       NSN Description
        wrk_nsn := substr(ws_nsn,1,4)||substr(ws_nsn,6,2)||
                   substr(ws_nsn,9,3)||substr(ws_nsn,13,4);
        wrk_nsn_desc := null;

/*message('wrk_nsn = '||wrk_nsn);
message('wrk_nsn_desc = '||wrk_nsn_desc);
message('wrk_err = '||wrk_err);
message('wrk_msg = '||wrk_msg);*/
message('error12344');
-- wrk_nsn  := nvl( wrk_nsn ,'');-message('error12');
if :key.SCR_ICN_2ND_2 is not  null then 
--	message('insideif');
        PROC_NSN_DESC(wrk_nsn,
    	                wrk_nsn_desc,
       	              wrk_err,
       	              wrk_msg);
    end if;

message('afterif');
/** New additions/changes to Spec: 22/11/2010 - Marius**/
       if ws_found = 'N' then
          IF  :key.scr_depr_sched in ('N','A') then
         	    IF  :key.scr_purch_price in ('N','A')
         	    AND :key.scr_fair_val in ('N','A') 
         	    AND :key.scr_asset_cat = 'A'
         	    AND :key.scr_herit_asset in ('N','A') then
         	        NULL; 
         	    ELSE
         	        goto nxt_rec;
         	    END IF;
         	ELSE
         	   goto nxt_rec;
         	END IF;
       ELSIF ws_found = 'Y' then	
              IF :key.scr_depr_sched = 'A' then
         	       null;
         	    ELSIF  :key.scr_depr_sched = 'N' then
         	          IF  (ws_dep_active_ind = 'N'
         	          AND  ws_dep_closed_ind = 'N') then
         	              NULL;
         	          ELSE
         	             goto nxt_rec;
         	          END IF;     
         	    ELSIF  :key.scr_depr_sched = 'I' 
    	          AND  ws_dep_active_ind = 'Y' then
        	           NULL; 
              ELSIF  :key.scr_depr_sched = 'C' 
     	          AND  ws_dep_closed_ind = 'Y' then
        	           NULL; 
         	    ELSE          	     	      	         	
         	       goto nxt_rec;
         	    END IF;
         ELSE	     
         	  goto nxt_rec;   
         END IF;
        
       	              
/*       IF :key.scr_depr_sched = 'A' then
         	null;
       ELSIF   :key.scr_depr_sched = 'N' then
         	     IF  (ws_dep_active_ind = 'N'
         	         AND  ws_dep_closed_ind = 'N')
         	         OR   ws_found = 'N' then	
         	         NULL; 
         	     ELSE
         	     	  goto nxt_rec;
         	     END IF; 
        ELSIF  :key.scr_depr_sched = 'I' then
    	         IF ws_found = 'Y'
    	            AND ws_dep_active_ind = 'Y' then
        	        NULL; 
         	     ELSE 
         	     	  goto nxt_rec;
         	     END IF;
        ELSIF  :key.scr_depr_sched = 'C' then
     	         IF ws_found = 'Y'
    	            AND ws_dep_closed_ind = 'Y' then
        	        NULL; 
         	     ELSE          	     	      	         	
         	        goto nxt_rec;
         	     END IF;
         ELSE	     
         	  goto nxt_rec;   
         END IF; */
       
       IF :key.scr_rec_dte_fr is not null then
       	  IF (:key.scr_rec_dte_fr = ws_act_rv_dte
       	   	   OR :key.scr_rec_dte_fr < ws_act_rv_dte)
       	   	  AND ws_found = 'Y' then
       	   	  NULL;
          ELSE
       	   	  goto nxt_rec;
          END IF;
       ELSE
       	  null;
       END IF;   
       
       IF :key.scr_rec_dte_to is not null then
          IF (:key.scr_rec_dte_to >= ws_act_rv_dte
       	   	  AND ws_found = 'Y') then
       	   	  NULL;
          ELSE
       	      goto nxt_rec;
          END IF;
       ELSE
       	  null;
       END IF;   
              
       
       IF ws_found = 'Y' then
          IF :key.scr_purch_price = 'A' then 
      	      null;
      	  ELSIF    	
       	     :key.scr_purch_price = 'Y' then
         	   IF ws_purch_price is not null then 
       	   	    NULL;
       	   	 ELSE
       	   	    goto nxt_rec;   
         	   END IF;
         	ELSIF  
         	   	:key.scr_purch_price = 'N' then
         	   	   IF ws_purch_price is null then 
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	  ELSE	     
       	  	  goto nxt_rec;   
       	  END IF;
       ELSIF ws_found = 'N' then
          	 IF :key.scr_purch_price in ('A','N') then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;	  
       ELSE
          goto nxt_rec;    	
       END IF;
       
       
       IF 	ws_found = 'Y' then
           IF :key.scr_fair_val = 'A' then
       	      null;
       	   ELSIF   	
       	      :key.scr_fair_val = 'Y' then
       	   	  IF ws_fair_val is not null then 
       	   	     NULL;
       	   	  ELSE
       	   	     goto nxt_rec;   
         	    END IF;
         	  ELSIF  
         	   	:key.scr_fair_val = 'N' then 
         	   	   IF ws_fair_val is null then 
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	    ELSE	     
       	   	  goto nxt_rec;   
       	    END IF;
       ELSIF ws_found = 'N' then
          	 IF :key.scr_fair_val in ('A','N') then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;	   
       ELSE
          goto nxt_rec;	   	
       END IF;
     
         
       IF 	ws_found = 'Y' then
           IF :key.scr_asset_cat = 'A' then
       	      IF ws_ass_cat ='MJ' or
       	         ws_ass_cat = 'MN' then	
                 null;
       	      END IF;
       	   ELSIF   
       	      :key.scr_asset_cat = 'MJ' then
         	    IF ws_ass_cat ='MJ' then
       	   	     NULL;
       	   	  ELSE
       	   	     goto nxt_rec;   
         	    END IF;
         	 ELSIF  
         	   	:key.scr_asset_cat = 'MN' then 
         	   	   IF ws_ass_cat = 'MN' then
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	   ELSE	     
       	   	  goto nxt_rec;   
       	   END IF;
       ELSIF ws_found = 'N' then
          	 IF :key.scr_herit_asset = 'A' then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;
       ELSE
         goto nxt_rec;
       END IF;
       
       IF ws_found = 'Y' then
       	  IF :key.scr_herit_asset = 'A' then
       	     null;
       	  ELSIF   
       	    :key.scr_herit_asset = 'Y' then
         	  IF ws_heritage_ind = 'Y' then 
       	  	    NULL;
       	    ELSE
       	        goto nxt_rec;   
         	  END IF;
         	ELSIF  
         	   	:key.scr_herit_asset = 'N' then 
         	   	   IF ws_heritage_ind = 'N' then
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	   ELSE	     
       	   	  goto nxt_rec;   
         	 END IF;	
       ELSIF ws_found = 'N' then
          	 IF :key.scr_herit_asset in ('A','N') then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;	  
       ELSE
         goto nxt_rec;
       END IF;
       
       BEGIN
           	 insert into ils.lsst_prt_asset_fin
       	 	     (user_id,
       	 	      dte_tme_stamp,
       	 	      unit_cde,
       	 	      nsn,
       	 	      ser_no,
       	 	      unit_desc,
       	 	      nsn_desc,
       	 	      me_cde,
       	 	      me_desc,
       	 	      gsb_unit_cde,
       	 	      gsb_desc,
       	 	      itm_purch_prc,
       	 	      heritage_ind,
       	 	      res_val,
       	 	      fair_val,
       	 	      current_val,
       	 	      accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        dep_sched,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        close_reason)
       	 	   values 
       	  	    (:global.ils_force_no,
                :key.wrk_dte,
       	 	      ws_unit,
       	 	      wrk_nsn,
       	 	      ws_ser_no,
       	 	      ws_eng_abbr_nme,
       	 	      wrk_nsn_desc,
       	 	      ws_mec,
       	 	      ws_me_descr,
       	 	      wrk_gsb_unit,
       	 	      wrk_gsb_desc,
       	 	      ws_itm_purch_prc,
       	 	      ws_heritage_ind,
       	 	      ws_res_val,
       	 	      ws_fair_val,
       	 	      ws_current_val,
       	 	      ws_accum_improve_val,
       	        ws_accum_impair_val,
       	        ws_useful_lfe,
       	        ws_rem_useful_lfe,
       	        ws_ass_cat,
       	        ws_dep_sched, 
       	        ws_start_dep_dte,
       	        ws_lst_dep_fin_YYYYMM,
       	        ws_close_reason);
       EXCEPTION
       	  when dup_val_on_index then
       	  null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);pause;
--         raise form_trigger_failure; */
       END;
     --  end if;
      
<< nxt_rec >>
       null; 
ws_cnt:= ws_cnt + 1; 
    o_cnt := ws_cnt;           
      	 END LOOP;
       END;
END;
	END IF;
	
END;	
END;

Open in new window

Avatar of Kavi Pachkawade
Kavi Pachkawade
Flag of India image

hello,
can u send me the error msg
Avatar of chalie001
chalie001

ASKER

there is no error message am just not able to insert into temp table after executing the procedure there is still no data in temp table
the error is the report is not printing because the temp table is empty so i have to sort this out first the report select value from temp table
Capture12321.PNG
Avatar of slightwv (䄆 Netminder)
You are likely hitting one of the exceptions.  

You have a null exception handler.

When the insert fails, the entire insert fails and is rolled back not just the duplicate values.

Comment out the exception handlers for the inserts and see if it raises an exception.
hi when I comment this exception
 EXCEPTION                        
         when no_data_found then
            ws_found := 'N';
--              goto nxt_rec;
              null;
the second one I got this error
User generated image
the exception in the insert does not give  any error
That message would indicate that one of your select statements returns no rows.
but when I run the sql in sql developer I do get row
this is the sql
BEGIN
              For rec1 in ser_data LOOP
         BEGIN                  
              Select scin,
                     itm_purch_prc,
                     heritage_ind,
                     res_val,
                     fair_val,
                     current_val,
                     accum_improve_val,
                     accum_impair_val,
                     useful_lfe,
                     rem_useful_lfe,
                     ass_cat,
                     act_rv_dte,
                     dep_active_ind,
                          dep_closed_ind,
                     start_dep_dte,
                     lst_dep_fin_YYYYMM,
                     close_reason
               into  ws_scin,
                     ws_itm_purch_prc,
                     ws_heritage_ind,
                     ws_res_val,
                     ws_fair_val,
                     ws_current_val,
                     ws_accum_improve_val,
                     ws_accum_impair_val,
                     ws_useful_lfe,
                     ws_rem_useful_lfe,
                     ws_ass_cat,
                     ws_act_rv_dte,
                          ws_dep_active_ind,
                          ws_dep_closed_ind,
                     ws_start_dep_dte,
                     ws_lst_dep_fin_YYYYMM,
                     ws_close_reason
               from  ils.lsst_asset_fin a
              where  rec1.scin = scin
                /*and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
                                                                                                             AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/
                                                                                                            
               and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd')))
             AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE));                                                                                                    
                                                                                                            
                                                                                                            
                      ws_found := 'Y';
       EXCEPTION                        
         when no_data_found then
            ws_found := 'N';
--              goto nxt_rec;
              null;
       END;
That select statement has an exception that handles a NO_DATA_FOUND exception.  It would have to be a statement that doesn't have a handler for that error.
am not passing this value althou
(:key.scr_rec_dte_fr

(:key.scr_rec_dte_to

when I test I put null and it return value
what do you mean  It would have to be a statement that doesn't have a handler for that error.
It has to be a select statement that doesn't have an exception block that handles a NO_DATA_FOUND exception.  The statement you posted has a exception block that handles that error, so that is not the statement that is causing the error.  By my quick count, I see about 12 select statements in what you posted, check each one for a NO_DATA_FOUND exception.  I know I saw at least 2 that didn't seem to be covered by an exception handler.
I did this
BEGIN
		     	    select unit_cde,
			  	           eng_abbr_nme
				      into   wrk_gsb_unit,
			  	           wrk_gsb_desc
  			      from 	 ils.unt
				      where  comnd_cde = :key.wrk_comnd_cde 
				      and    org_func  = 'CM';
		     exception
		     	when no_data_found then
		     	null;
			      END;

Open in new window


nothing happen no error it just go through but does not insert
So that would be the select that returns no rows.  You are just ignoring the error.  Is that the intended result?  If not, then there is likely an error in your logic.
am not ignoring the error is just i don't know where is the error this select statements return rows in my sql developer
You are ignoring the error:
exception
		     	when no_data_found then
		     	null;

Open in new window


When you have a null exception handler, you tell Oracle that you don't care about that error and to just keep processing.

>>select statements return rows in my sql developer

To return rows in SQL Deverloper means that you have likely hard-coded the values for the variables.  When the code runs in the form, one or more of the variables you think has a value, probably does not.
what do you mean am ignoring the error
exception
		     	when no_data_found then
		     	null;

Open in new window


this was not there before I just put it now but still not able to insert what exception must I put
hi I know where is the problem now is in this second block
	BEGIN
      DECLARE
  	    cursor ser_data is
  	      select a.ser_no, 
  	             a.scin,
  	             a.sin,
  	             nvl(b.unit_holding,0) unit_holding,
  	             nvl(b.dept_holding,0) dept_holding,
  	             nvl(b.unit_located_at,0) unit_located_at,
  	             b.cond_cde,
  	             b.srv_status_cde,
  	             b.pers_located_at,
  	             b.nha_sin,
  	             b.nha_ser_no,
  	             c.nsc||'-'||substr(c.niin,1,2)||'-'||
  	             substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
  	             c.itm_type_cde,
  	             c.struc_lev_cde,
  	             c.agency_ser_cntrl_ind,
  	             d.itm_nme,
  	             e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
  	             e.me_desc  	             
  	        from ils.sss_entry_index a,
  	             ils.sss_er_core b,
  	             ils.item_data c,
  	             ils.names d,
  	             ils.me_master_rec e
  	       where a.sin_change_reas is null
  	         and a.sin_change_dte is null
  	         and b.scin = a.scin
  	         and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
  	               nvl(ws_unit_cde,0) > 0) or
  	               nvl(ws_unit_cde,0) = 0)
  	         and c.sin = a.sin
  	         and d.sin = c.sin
  	         and d.itm_nme_type = '1'
  	         and d.name_seq = '1'
  	         and e.sin (+) = a.sin
  	         and (e.me_life_cyc (+) != '08' and
                  e.me_rec_stat (+) not in ('C','X'))
           order by b.unit_holding, a.ser_no;

       BEGIN
       	 For rec01 in ser_data LOOP
         BEGIN     	 	
       	 Select scin,
       	        itm_purch_prc,
       	        heritage_ind,
       	        res_val,
       	        fair_val,
       	        current_val,
       	        accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        act_rv_dte,
       	        dep_active_ind,
       	 	      dep_closed_ind,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        close_reason
       	  into  ws_scin,
       	        ws_itm_purch_prc,
       	        ws_heritage_ind,
       	        ws_res_val,
       	        ws_fair_val,
       	        ws_current_val,
       	        ws_accum_improve_val,
       	        ws_accum_impair_val,
       	        ws_useful_lfe,
       	        ws_rem_useful_lfe,
       	        ws_ass_cat,
       	        ws_act_rv_dte,
     	 	        ws_dep_active_ind,
     	 	        ws_dep_closed_ind,
       	        ws_start_dep_dte,
       	        ws_lst_dep_fin_YYYYMM,
       	        ws_close_reason
       	  from  ils.lsst_asset_fin a
       	 where  rec01.scin = a.scin
       
             
             AND  TRUNC(a.act_rv_dte) BETWEEN NVL(:key.scr_rec_dte_fr,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(:key.scr_rec_dte_to,SYSDATE);
             
   																  	  
 			   ws_found := 'Y';
         	exception
         when no_data_found then
            ws_found := 'N';
--              goto nxt_rec;
              null;
       END;

Open in new window


it does select value in cursor but the second select is not getting value  from curso I don't know why is there a why I can check how or  why
somehow is selecting the value from the coursor but not selecting tham in the second query,because when i deburg i get 260 record which are value from the corsor but the second select only have 8 records
By "ignoring an error" we mean that you are trapping the error and not doing anything with it.  Typically that is not what you want to do, but this is your logic, we don't know what the right action is.  If doing nothing when that query returns no rows is the correct thing to do, then fine.

Usually, in a NO_DATA_FOUND exception, you are setting some or all of the variables being selected into to some default value or NULL.
ok the exception is not a problem the block above is the problem
I see you've opened a new question on this.  Where do you want to continue the discussion, here or in the new one?
we can continue hear must I close the other one but I have post something there
I'll repost my responses here.  Post in the other question when you would like it deleted and I can take care of that.
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
hi this is the desc of table
SQL> desc ils.sss_entry_index
Name            Type         Nullable Default Comments
--------------- ------------ -------- ------- --------
SCIN            NUMBER(11)                            
SIN             NUMBER(11)                            
SER_NO          VARCHAR2(30)                          
SIN_CHANGE_REAS VARCHAR2(6)  Y                        
SIN_CHANGE_DTE  DATE         Y                        
 
all field in the cursor select are been used they may not be be used in the second select statement but they are been used in logic like belo you can check the whole procedure
         IF rec01.srv_status_cde = 'C02' then
                        ws_ser_no := rec01.nha_ser_no;
                        ws_inst_ser_no := rec01.ser_no;
                        ws_pers_located_at := rec01.pers_located_at;

if you what other table desc you can check hear
https://drive.google.com/open?id=0B3dKl5pXBYMqdXM2ZXduRUU4VTA
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
hi thanks for helping i have found something when i hard code this scin 100027816 am geting no data found but when i hard code other scin it goes through but does not insert in tem table
User generated imagei have created this procedure for testing to see whats happening
PROCEDURE PROC_SELECT_DATA_test (o_cnt OUT NUMBER)IS
BEGIN
      DECLARE
            ws_unit_cde             number(06);
            wrk_gsb_unit            number(06);
            ws_unit                 number(06);
            ws_char_unit_cde        varchar2(06);
            ws_eng_abbr_nme         varchar2(30);
            wrk_gsb_desc            varchar2(30);
            ws_unit_line_ind        varchar2(01);
            ws_act_des              varchar2(01);
            ws_origin               varchar2(100);
            ws_ser_no               varchar2(30);
            ws_inst_ser_no          varchar2(30);
            ws_scin                 number(11);
            ws_sin                  number(11);
            ws_unit_holding         number(06);
            ws_dept_holding         number(06);
            ws_unit_located_at      number(06);
            ws_cond_cde             varchar2(01);
            ws_srv_status_cde       varchar2(3);
            ws_pers_located_at      varchar2(15);
            ws_itm_purch_prc        number(15,4);
            wrk_purch_prc           varchar2(3);
            ws_heritage_ind         varchar2(1);
    wrk_herit_asset         varchar2(3);            
            ws_res_val              number(15,4);
            ws_fair_val             number(15,4);
            wrk_fair_val            varchar2(3);
            ws_current_val          number(15,4);
            ws_accum_improve_val    number(15,4);
            ws_accum_impair_val     number(15,4);
            ws_useful_lfe           number(4);
            ws_rem_useful_lfe       number(4);
            ws_ass_cat              varchar2(2);
            wrk_ass_cat             varchar2(5);
            ws_dep_sched            varchar2(1);
            wrk_dep_sched           varchar2(15);
            ws_start_dep_dte        date;
            ws_close_reason         varchar2(20);
            ws_lst_dep_fin_YYYYMM   number(6);
            ws_prov_reg_no          varchar2(20);
            ws_nsn                  varchar2(16);
            ws_itm_type_cde         varchar2(01);
            ws_struc_lev_cde        varchar2(01);
            ws_agency_ser_cntrl_ind varchar2(01);
            ws_cntr_body_cde        varchar2(02);
            ws_org_func             varchar2(02);
            ws_nsn_descr            varchar2(200);
            ws_mec                  varchar2(04);
            ws_me_descr             varchar2(50);
            ws_sort_cc              number(02);
            ws_sort_srv             number(02);
            ws_err_ind              varchar2(01);
            ws_err_msg              varchar2(80);
            ws_ldesc                varchar2(50);
            ws_sdesc                varchar2(25);
            ws_short_nme            varchar2(30);
            ws_long_nme             varchar2(50);
            ws_stat_ind             varchar2(01);
            ws_oli_ind              varchar2(01);
            ws_pers_details         varchar2(100);
            ws_desc                 varchar2(150);
            ws_rank                 varchar2(15);
            ws_pers_found           varchar2(01);
            ws_un_no                varchar2(10);
            ws_dummy                varchar2(01);
            ws_m_nsn                varchar2(16);
            proc_cfg_tab_no         number;
            proc_mec                varchar2(04);
            proc_mec_nsn            varchar2(16);
            proc_cfg_tab_desc       varchar2(200);
            proc_mec_desc           varchar2(200);
            proc_mec_nsn_desc       varchar2(200);
            proc_exist_corp_ind     varchar2(01);
            proc_err_ind            varchar2(01);
            proc_err_msg            varchar2(80);
            proc_me_niin            varchar2(13);
            wrk_nsn_desc            varchar2(200);
            wrk_err                 varchar2(1);
            wrk_msg                 varchar2(50);
            wrk_nsn                 varchar2(16);
            v_nsn                   varchar2(16);
            wrk_totals              varchar2(3);
            wrk_all_units           varchar2(3);
            ws_found                varchar2(1);
            ws_dep_active_ind       varchar2(1);
            ws_dep_closed_ind       varchar2(1);
            ws_act_rv_dte           date;
            ws_purch_price          number(15,4);
            ws_cnt                  number := 0;
      
             CURSOR         sel_ser_no is
       select         s.sin,
                      s.ser_no,
                           e.scin,
                           e.unit_holding,
                           me_cat_cde||me_family_cde||me_var_cde mec,
                           i.nsc||'-'||substr(i.niin,1,2)||'-'||
                          substr(i.niin,3,3)||'-'||substr(i.niin,6,4) nsn,
                          i.agency_ser_cntrl_ind,
                           me_desc
       from           ils.me_master_rec          m,
                           ils.sss_entry_index        s,
                                   ils.sss_er_core            e,
                                   ils.item_data              i
      where           s.ser_no                      = :key.scr_ser_no
       and            e.scin                             = s.scin
                   and            s.sin_change_reas is null
           and            s.sin_change_dte is null
                   and           ((nvl(e.unit_holding,0) = nvl(ws_unit_cde,0) and
                          nvl(ws_unit_cde,0) > 0) or
                          nvl(ws_unit_cde,0) = 0)
           and            m.sin (+)            = s.sin
            and            i.sin             = s.sin
--             or             s.sin            = :key.wrk_sin)
           and            (m.me_life_cyc (+) != '08' and
                      m.me_rec_stat (+) not in ('C','X'));
                                
                                     BEGIN
            FOR rec1 IN sel_ser_no
           LOOP
      BEGIN
                       select itm_purch_prc,
                            heritage_ind,
                            res_val,
                            fair_val,
                            current_val,
                            accum_improve_val,
                            accum_impair_val,
                            useful_lfe,
                            rem_useful_lfe,
                            ass_cat,
                            act_rv_dte,
                            dep_active_ind,
                            dep_closed_ind,
                            start_dep_dte,
                            lst_dep_fin_YYYYMM,
                            close_reason
                       into  
                              ws_itm_purch_prc,
                            ws_heritage_ind,
                            ws_res_val,
                            ws_fair_val,
                            ws_current_val,
                            ws_accum_improve_val,
                            ws_accum_impair_val,
                            ws_useful_lfe,
                            ws_rem_useful_lfe,
                            ws_ass_cat,
                            ws_act_rv_dte,
                            ws_dep_active_ind,
                            ws_dep_closed_ind,
                            ws_start_dep_dte,
                            ws_lst_dep_fin_YYYYMM,
                            ws_close_reason
                            from   ils.lsst_asset_fin  a
                       where  trim(a.scin)            = trim(rec1.scin)
                      -- where scin = 102672956
                         /*and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
                                                                                                               AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/                                                                                                             
                                  /*and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd')))
             AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE))and  (:key.scr_rec_dte_fr is null or :key.scr_rec_dte_to is null);*/            
             AND  TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE);
            -- AND  TRUNC(a.act_rv_dte) BETWEEN NVL(:key.scr_rec_dte_fr,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(:key.scr_rec_dte_to,SYSDATE);
             
                                                                               
                                                                                                                        
                       ws_found := 'Y';
      /*EXCEPTION  
            when others then
            null;*/
            exception
         when no_data_found THEN
                        ws_found := 'N';
--                         goto nxt_rec;
            null;
                  
                         insert into ils.lsst_prt_asset_fin
                         (user_id,
                          dte_tme_stamp,
                          unit_cde,
                          nsn,
                          ser_no,
                          unit_desc,
                          nsn_desc,
                          me_cde,
                          me_desc,
                          gsb_unit_cde,
                          gsb_desc,
                          itm_purch_prc,
                          heritage_ind,
                          res_val,
                          fair_val,
                          current_val,
                          accum_improve_val,
                     accum_impair_val,
                     useful_lfe,
                     rem_useful_lfe,
                     ass_cat,
                     dep_sched,
                     start_dep_dte,
                     lst_dep_fin_YYYYMM,
                     close_reason)
                       values
                         ('l01075408',
                20160909,
                          ws_unit,
                          wrk_nsn,
                          rec1.ser_no,
                          ws_eng_abbr_nme,
                          wrk_nsn_desc,
                          ws_mec,
                          ws_me_descr,
                          wrk_gsb_unit,
                          wrk_gsb_desc,
                          ws_itm_purch_prc,
                          ws_heritage_ind,
                          ws_res_val,
                          ws_fair_val,
                          ws_current_val,
                          ws_accum_improve_val,
                     ws_accum_impair_val,
                     ws_useful_lfe,
                     ws_rem_useful_lfe,
                     ws_ass_cat,
                     ws_dep_sched,
                     ws_start_dep_dte,
                     ws_lst_dep_fin_YYYYMM,
                     ws_close_reason);
      EXCEPTION
               when dup_val_on_index then
               null;
      END;
this procedure is not using anything from form you can test it mybe you will see what is the problem the database script are at
https://drive.google.com/open?id=0B3dKl5pXBYMqdXM2ZXduRUU4VTA
You shouldn't need the TRIM function.  I was thinking that if the column was a CHAR or VARCHAR2, you might has some spaces in the data.  Since it a number, no need to TRIM.

I missed this initially, you have another variable in the where clause:   s.ser_no = :key.scr_ser_no   Make sure that value is what you think it is.

Anyway, back to the question:
The 'blank' record for that scin could be caused by bad data.  Something that needs to join up for that specific record doesn't have the correct foreign key references in the other tables.
this where           s.ser_no                      = :key.scr_ser_no  you can leave it null for now am not even passing it when testing

you can do this   where           s.ser_no                      is null
First:  I'm not testing anything.

>>you can leave it null for now am not even passing it when testing

That could be the issue when you test.  If you don't pass in the value and it is null and you have the '=' in your test code, then nothing will be returned.
so i must do like this

where           s.ser_no                      = :key.scr_ser_no or :key.scr_ser_no is null
I don't know your system so I cannot say what you need to do.

If :key.scr_ser_no can be null then yes, you need you check for null in addition to a value.

Be careful of your operator precedence.  You'll need to group the OR:
where          ( s.ser_no = :key.scr_ser_no or :key.scr_ser_no is null) and ...
that not a problem the cursor which making problem  problem is this on
 
cursor ser_data is
  	      select a.ser_no, 
  	             a.scin,
  	             a.sin,
  	             nvl(b.unit_holding,0) unit_holding,
  	             nvl(b.dept_holding,0) dept_holding,
  	             nvl(b.unit_located_at,0) unit_located_at,
  	             b.cond_cde,
  	             b.srv_status_cde,
  	             b.pers_located_at,
  	             b.nha_sin,
  	             b.nha_ser_no,
  	             c.nsc||'-'||substr(c.niin,1,2)||'-'||
  	             substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
  	             c.itm_type_cde,
  	             c.struc_lev_cde,
  	             c.agency_ser_cntrl_ind,
  	             d.itm_nme,
  	             e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
  	             e.me_desc  	             
  	        from ils.sss_entry_index a,
  	             ils.sss_er_core b,
  	             ils.item_data c,
  	             ils.names d,
  	             ils.me_master_rec e,
  	             ils.lsst_asset_fin fin
  	       where a.sin_change_reas is null
  	         and a.sin_change_dte is null
  	         and b.scin = a.scin
  	         and fin.scin = b.scin
  	         and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
  	               nvl(ws_unit_cde,0) > 0) or
  	               nvl(ws_unit_cde,0) = 0)
  	         and c.sin = a.sin
  	         and d.sin = c.sin
  	        and b.scin = 106235476
  	         and d.itm_nme_type = '1'
  	         and d.name_seq = '1'
  	         and e.sin (+) = a.sin
  	         and (e.me_life_cyc (+) != '08' and
                  e.me_rec_stat (+) not in ('C','X'))
           order by b.unit_holding, a.ser_no;	

Open in new window

I thought you said above the cursor wasn't the problem that it was the select inside the cursor loop that was the problem?
the problem is in this part
 cursor ser_data is
  	      select a.ser_no, 
  	             a.scin,
  	             a.sin,
  	             nvl(b.unit_holding,0) unit_holding,
  	             nvl(b.dept_holding,0) dept_holding,
  	             nvl(b.unit_located_at,0) unit_located_at,
  	             b.cond_cde,
  	             b.srv_status_cde,
  	             b.pers_located_at,
  	             b.nha_sin,
  	             b.nha_ser_no,
  	             c.nsc||'-'||substr(c.niin,1,2)||'-'||
  	             substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
  	             c.itm_type_cde,
  	             c.struc_lev_cde,
  	             c.agency_ser_cntrl_ind,
  	             d.itm_nme,
  	             e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
  	             e.me_desc  	             
  	        from ils.sss_entry_index a,
  	             ils.sss_er_core b,
  	             ils.item_data c,
  	             ils.names d,
  	             ils.me_master_rec e,
  	             ils.lsst_asset_fin fin
  	       where a.sin_change_reas is null
  	         and a.sin_change_dte is null
  	         and b.scin = a.scin
  	         and fin.scin = b.scin
  	         and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
  	               nvl(ws_unit_cde,0) > 0) or
  	               nvl(ws_unit_cde,0) = 0)
  	         and c.sin = a.sin
  	         and d.sin = c.sin
  	        and b.scin = 106235476
  	         and d.itm_nme_type = '1'
  	         and d.name_seq = '1'
  	         and e.sin (+) = a.sin
  	         and (e.me_life_cyc (+) != '08' and
                  e.me_rec_stat (+) not in ('C','X'))
           order by b.unit_holding, a.ser_no;					  
					       BEGIN
     	 FOR rec1 IN sel_ser_no
	     LOOP
      BEGIN
			     select itm_purch_prc,
       	 	        heritage_ind,
       	 	        res_val,
       	 	        fair_val,
       	 	        current_val,
       	 	        accum_improve_val,
       	 	        accum_impair_val,
       	 	        useful_lfe,
       	 	        rem_useful_lfe,
       	 	        ass_cat,
       	 	        act_rv_dte,
       	 	        dep_active_ind,
       	 	        dep_closed_ind,
       	 	        start_dep_dte,
       	 	        lst_dep_fin_YYYYMM,
       	 	        close_reason
			     into   
			            ws_itm_purch_prc,
       	 	        ws_heritage_ind,
       	 	        ws_res_val,
       	 	        ws_fair_val,
       	 	        ws_current_val,
       	 	        ws_accum_improve_val,
       	 	        ws_accum_impair_val,
       	 	        ws_useful_lfe,
       	 	        ws_rem_useful_lfe,
       	 	        ws_ass_cat,
       	 	        ws_act_rv_dte,
       	 	        ws_dep_active_ind,
       	 	        ws_dep_closed_ind,
       	 	        ws_start_dep_dte,
       	 	        ws_lst_dep_fin_YYYYMM,
       	 	        ws_close_reason
				    from   ils.lsst_asset_fin  a
  			   where  trim(a.scin)            = trim(rec1.scin)
  			  -- where scin = 102672956 
  			     /*and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
   																	      AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/  																	      
   					 /*and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd'))) 
             AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE))and  (:key.scr_rec_dte_fr is null or :key.scr_rec_dte_to is null);*/		
             AND  TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE);
            -- AND  TRUNC(a.act_rv_dte) BETWEEN NVL(:key.scr_rec_dte_fr,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(:key.scr_rec_dte_to,SYSDATE);
             
             										      
   	         																      
  			   ws_found := 'Y';
      /*EXCEPTION  
      	when others then
      	null;*/
      	exception
         when no_data_found THEN
			      ws_found := 'N';
-- 			      goto nxt_rec;
            null;

Open in new window

the original procedure
is
BEGIN
      DECLARE
  	    cursor ser_data is
  	      select a.ser_no, 
  	             a.scin,
  	             a.sin,
  	             nvl(b.unit_holding,0) unit_holding,
  	             nvl(b.dept_holding,0) dept_holding,
  	             nvl(b.unit_located_at,0) unit_located_at,
  	             b.cond_cde,
  	             b.srv_status_cde,
  	             b.pers_located_at,
  	             b.nha_sin,
  	             b.nha_ser_no,
  	             c.nsc||'-'||substr(c.niin,1,2)||'-'||
  	             substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
  	             c.itm_type_cde,
  	             c.struc_lev_cde,
  	             c.agency_ser_cntrl_ind,
  	             d.itm_nme,
  	             e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
  	             e.me_desc  	             
  	        from ils.sss_entry_index a,
  	             ils.sss_er_core b,
  	             ils.item_data c,
  	             ils.names d,
  	             ils.me_master_rec e
  	       where a.sin_change_reas is null
  	         and a.sin_change_dte is null
  	         and b.scin = a.scin
  	         and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
  	               nvl(ws_unit_cde,0) > 0) or
  	               nvl(ws_unit_cde,0) = 0)
  	         and c.sin = a.sin
  	         and d.sin = c.sin
  	         and d.itm_nme_type = '1'
  	         and d.name_seq = '1'
  	         and e.sin (+) = a.sin
  	         and (e.me_life_cyc (+) != '08' and
                  e.me_rec_stat (+) not in ('C','X'))
           order by b.unit_holding, a.ser_no;

       BEGIN
       	 For rec1 in ser_data LOOP
         BEGIN     	 	
       	 Select scin,
       	        itm_purch_prc,
       	        heritage_ind,
       	        res_val,
       	        fair_val,
       	        current_val,
       	        accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        act_rv_dte,
       	        dep_active_ind,
       	 	      dep_closed_ind,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        close_reason
       	  into  ws_scin,
       	        ws_itm_purch_prc,
       	        ws_heritage_ind,
       	        ws_res_val,
       	        ws_fair_val,
       	        ws_current_val,
       	        ws_accum_improve_val,
       	        ws_accum_impair_val,
       	        ws_useful_lfe,
       	        ws_rem_useful_lfe,
       	        ws_ass_cat,
       	        ws_act_rv_dte,
     	 	        ws_dep_active_ind,
     	 	        ws_dep_closed_ind,
       	        ws_start_dep_dte,
       	        ws_lst_dep_fin_YYYYMM,
       	        ws_close_reason
       	  from  ils.lsst_asset_fin a
       	 where  rec1.scin = scin
       	   /*and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
   																  	  AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/
   																  	  
   		and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd'))) 
             AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE));															  	  
   																  	  
   																  	  
 			   ws_found := 'Y';
       EXCEPTION                         
         when no_data_found then
            ws_found := 'N';
--              goto nxt_rec;
              null;
       END;

Open in new window


must i hire you to help me
>>must i hire you to help me

I've provided all the help I can.  I don't have Forms.  I haven't coded Forms for over 20 years.

I can help from a general SQL aspect and I've done all of that I can.

The issue has to be one of the two things I mentioned above.  I cannot think of any other possibility.

I'll send out a call for help to see if I can get additional Experts to see if they can think of anything else.
If the problem is in the block you mentioned , debug it.   Put messages on strategic places (or use a procedure that write to os-file or using a discrete transaction to a database file)  and check where the flow does not behave like you expect.
Are you sure that the problem is not the fact the insert into a temporary table happens in one database session (from an Oracle Form)  and your report may be running in a separate Oracle Session.  If that is true, the insert in the Forms session may succeed but the report will never see it, because the contents of global temporary tables are never visible across database sessions.

When I use global temporary tables with Oracle Reports, I only use them inside the Oracle report (like in a before-report or after pform trigger).
Unless something changed, I don't think it a global temp table.
Maybe I am blind but in your code (post ID: 41541578) the cursor ser_data is not opened.
you mean I must do this
open ser_data ;
 For rec1 in ser_data LOOP
No, I would try it with impilcit cursor (http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#LNPLS553)

FOR rec1 IN (select a.ser_no,
                 a.scin,
                 a.sin,
                 nvl(b.unit_holding,0) unit_holding,
                 nvl(b.dept_holding,0) dept_holding,
                 nvl(b.unit_located_at,0) unit_located_at,
                 b.cond_cde,
                 b.srv_status_cde,
                 b.pers_located_at,
                 b.nha_sin,
                 b.nha_ser_no,
                 c.nsc||'-'||substr(c.niin,1,2)||'-'||
                 substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
                 c.itm_type_cde,
                 c.struc_lev_cde,
                 c.agency_ser_cntrl_ind,
                 d.itm_nme,
                 e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
                 e.me_desc                  
            from ils.sss_entry_index a,
                 ils.sss_er_core b,
                 ils.item_data c,
                 ils.names d,
                 ils.me_master_rec e
           where a.sin_change_reas is null
             and a.sin_change_dte is null
             and b.scin = a.scin
             and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
                   nvl(ws_unit_cde,0) > 0) or
                   nvl(ws_unit_cde,0) = 0)
             and c.sin = a.sin
             and d.sin = c.sin
             and d.itm_nme_type = '1'
             and d.name_seq = '1'
             and e.sin (+) = a.sin
             and (e.me_life_cyc (+) != '08' and
                  e.me_rec_stat (+) not in ('C','X'))
           order by b.unit_holding, a.ser_no)
LOOP
...          
END LOOP;
am getting this error
User generated imagethis is the whole code where must I put rec01
PROCEDURE PROC_SELECT_DATA (o_cnt OUT NUMBER)IS
BEGIN
	DECLARE
		ws_unit_cde             number(06);
		wrk_gsb_unit            number(06);
		ws_unit                 number(06);
		ws_char_unit_cde        varchar2(06);
		ws_eng_abbr_nme         varchar2(30);
		wrk_gsb_desc            varchar2(30);
		ws_unit_line_ind        varchar2(01);
		ws_act_des              varchar2(01);
		ws_origin               varchar2(100);
		ws_ser_no               varchar2(30);
		ws_inst_ser_no          varchar2(30);
		ws_scin                 number(11);
		ws_sin                  number(11);
		ws_unit_holding         number(06);
		ws_dept_holding         number(06);
		ws_unit_located_at      number(06);
		ws_cond_cde             varchar2(01);
		ws_srv_status_cde       varchar2(3);
		ws_pers_located_at      varchar2(15);
		ws_itm_purch_prc        number(15,4);
		wrk_purch_prc           varchar2(3);
		ws_heritage_ind         varchar2(1);
    wrk_herit_asset         varchar2(3);		
		ws_res_val              number(15,4);
		ws_fair_val             number(15,4);
		wrk_fair_val            varchar2(3);
		ws_current_val          number(15,4);
		ws_accum_improve_val    number(15,4);
		ws_accum_impair_val     number(15,4);
		ws_useful_lfe           number(4);
		ws_rem_useful_lfe       number(4);
		ws_ass_cat              varchar2(2);
		wrk_ass_cat             varchar2(5);
		ws_dep_sched            varchar2(1);
		wrk_dep_sched           varchar2(15);
		ws_start_dep_dte        date;
		ws_close_reason         varchar2(20);
		ws_lst_dep_fin_YYYYMM   number(6);
		ws_prov_reg_no          varchar2(20);
		ws_nsn                  varchar2(16);
		ws_itm_type_cde         varchar2(01);
		ws_struc_lev_cde        varchar2(01);
		ws_agency_ser_cntrl_ind varchar2(01);
		ws_cntr_body_cde        varchar2(02);
		ws_org_func             varchar2(02);
		ws_nsn_descr            varchar2(200);
		ws_mec                  varchar2(04);
		ws_me_descr             varchar2(50);
		ws_sort_cc              number(02);
		ws_sort_srv             number(02);
		ws_err_ind              varchar2(01);
		ws_err_msg              varchar2(80);
		ws_ldesc                varchar2(50);
		ws_sdesc                varchar2(25);
		ws_short_nme            varchar2(30);
		ws_long_nme             varchar2(50);
		ws_stat_ind             varchar2(01);
		ws_oli_ind              varchar2(01);
		ws_pers_details         varchar2(100);
		ws_desc                 varchar2(150);
		ws_rank                 varchar2(15);
		ws_pers_found           varchar2(01);
		ws_un_no                varchar2(10);
		ws_dummy                varchar2(01);
		ws_m_nsn                varchar2(16);
		proc_cfg_tab_no         number;
		proc_mec                varchar2(04);
		proc_mec_nsn            varchar2(16);
		proc_cfg_tab_desc       varchar2(200);
		proc_mec_desc           varchar2(200);
		proc_mec_nsn_desc       varchar2(200);
		proc_exist_corp_ind     varchar2(01);
		proc_err_ind            varchar2(01);
		proc_err_msg            varchar2(80);
		proc_me_niin            varchar2(13);
		wrk_nsn_desc            varchar2(200);
		wrk_err                 varchar2(1);
		wrk_msg                 varchar2(50);
		wrk_nsn                 varchar2(16);
		v_nsn                   varchar2(16);
		wrk_totals              varchar2(3);
		wrk_all_units           varchar2(3);
		ws_found                varchar2(1);
		ws_dep_active_ind       varchar2(1);
		ws_dep_closed_ind       varchar2(1);
		ws_act_rv_dte           date;
		ws_purch_price          number(15,4);
		ws_cnt                  number := 0;
		
	BEGIN
    ws_unit_cde := 0;
    
		IF :key.wrk_org_func  = 'HQ' or
		  (:key.wrk_org_func  = 'CM' and
			 :key.scr_all_units = 'Y') THEN
			 ws_unit_cde     := 0;
		 	 ws_eng_abbr_nme := '';
		ELSE	  
		   ws_unit_cde     := :key.scr_req_unit;
	     ws_eng_abbr_nme := :key.scr_unit_desc;
		END IF;
		
	--	message('testt09');
	--	message(:key.scr_ser_no);
	message('cmd = '||:key.wrk_comnd_cde );
	message('unitcde = '||ws_unit_cde);
IF :key.scr_ser_no is not null then
     DECLARE
     	 CURSOR         sel_ser_no is
       select         s.sin,
                      s.ser_no, 
       	              e.scin,
       	              e.unit_holding,
       	              me_cat_cde||me_family_cde||me_var_cde mec,
       	              i.nsc||'-'||substr(i.niin,1,2)||'-'||
  	                  substr(i.niin,3,3)||'-'||substr(i.niin,6,4) nsn,
  	                  i.agency_ser_cntrl_ind,
       	              me_desc
       from           ils.me_master_rec          m,
       	              ils.sss_entry_index        s,
	         		        ils.sss_er_core            e,
	         		        ils.item_data              i
      where           s.ser_no			    = :key.scr_ser_no 
       and            e.scin 				    = s.scin
			 and            s.sin_change_reas is null
  	   and            s.sin_change_dte is null 
			 and           ((nvl(e.unit_holding,0) = nvl(ws_unit_cde,0) and
  	                  nvl(ws_unit_cde,0) > 0) or
  	                  nvl(ws_unit_cde,0) = 0) 
	     and            m.sin (+)            = s.sin
 	     and            i.sin             = s.sin
-- 	      or             s.sin            = :key.wrk_sin)
  	   and            (m.me_life_cyc (+) != '08' and
                      m.me_rec_stat (+) not in ('C','X'));
	   	 
     BEGIN
     	 FOR rec1 IN sel_ser_no
	     LOOP
      BEGIN
			     select itm_purch_prc,
       	 	        heritage_ind,
       	 	        res_val,
       	 	        fair_val,
       	 	        current_val,
       	 	        accum_improve_val,
       	 	        accum_impair_val,
       	 	        useful_lfe,
       	 	        rem_useful_lfe,
       	 	        ass_cat,
       	 	        act_rv_dte,
       	 	        dep_active_ind,
       	 	        dep_closed_ind,
       	 	        start_dep_dte,
       	 	        lst_dep_fin_YYYYMM,
       	 	        close_reason
			     into   
			            ws_itm_purch_prc,
       	 	        ws_heritage_ind,
       	 	        ws_res_val,
       	 	        ws_fair_val,
       	 	        ws_current_val,
       	 	        ws_accum_improve_val,
       	 	        ws_accum_impair_val,
       	 	        ws_useful_lfe,
       	 	        ws_rem_useful_lfe,
       	 	        ws_ass_cat,
       	 	        ws_act_rv_dte,
       	 	        ws_dep_active_ind,
       	 	        ws_dep_closed_ind,
       	 	        ws_start_dep_dte,
       	 	        ws_lst_dep_fin_YYYYMM,
       	 	        ws_close_reason
				    from   ils.lsst_asset_fin  a
  			   where  trim(a.scin)            = trim(rec1.scin)
  			  -- where scin = 102672956 
  			     /*and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
   																	      AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/  																	      
   					 /*and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd'))) 
             AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE))and  (:key.scr_rec_dte_fr is null or :key.scr_rec_dte_to is null);*/		
             AND  TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE);
            -- AND  TRUNC(a.act_rv_dte) BETWEEN NVL(:key.scr_rec_dte_fr,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(:key.scr_rec_dte_to,SYSDATE);
             
             										      
   	         																      
  			   ws_found := 'Y';
      /*EXCEPTION  
      	when others then
      	null;*/
      	exception
         when no_data_found THEN
			      ws_found := 'N';
-- 			      goto nxt_rec;
            null;
      END;
      
      ws_scin := rec1.scin; 
      ws_sin := rec1.sin;
   	  ws_unit_holding := nvl(rec1.unit_holding,0);
      ws_mec := rec1.mec;
      ws_me_descr := rec1.me_desc;
      ws_ser_no := rec1.ser_no;
      ws_inst_ser_no := '';       	   	  
	     	
      	IF :key.scr_req_unit is null or
       	   :key.wrk_org_func = 'HQ' then
       	   	  ws_unit := ws_unit_holding;
       	ELSE
       	 	 	  IF :key.scr_all_units = 'Y' then
       	 	 	     ws_unit := 0;
       	 	       BEGIN
                   select 'X' 
                     into ws_dummy
                     from dual
                    where ws_unit_holding in (select unit_cde
                                                from ils.unt
                                               where comnd_cde = :key.wrk_comnd_cde);
                   ws_unit := ws_unit_holding;
       	 	       exception
       	 	    	   when no_data_found THEN
       	 	    	        ws_unit := 0;
      	 	    	        goto nxt_rec;
       	 	       END;
       	 	 	  ELSE
       	 	 	  	ws_unit := ws_unit_cde;
       	      END IF;
	     END IF;
	     	IF :key.scr_all_units = 'Y' then
		        wrk_gsb_unit := :key.scr_req_unit;
		        wrk_gsb_desc := :key.scr_unit_name;
		    ELSE
		     BEGIN
		     	    select unit_cde,
			  	           eng_abbr_nme
				      into   wrk_gsb_unit,
			  	           wrk_gsb_desc
  			      from 	 ils.unt
				      where  comnd_cde = :key.wrk_comnd_cde 
				      and    org_func  = 'CM';
			      END;
     END IF;
     
        IF :key.scr_me_cde is not null then
      	 	 	  IF :key.scr_var_cde is not null then
              	 IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then 
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_fam_cde is not null then 
              	 IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_cat_cde is not null then
              	 IF :key.scr_cat_cde = substr(ws_mec,1,1) then
              	 	  null;
              	 ELSE
       	 	 	  	    goto nxt_rec;
         	 	 	   END IF;
              END IF;
           ELSE
      	 	    :key.scr_me_cde  := ws_mec;
      	 	    :key.scr_mec_desc     := ws_me_descr;    
       	 	 END IF;   
-- Unit Description
           IF nvl(ws_unit,0) > 0 then
       	 	    BEGIN
       	 	      select eng_abbr_nme
       	 	        into ws_eng_abbr_nme
       	 	        from ils.unt
       	 	       where unit_cde = ws_unit;
       	 	      exception
       	 	        when no_data_found then
       	 	             ws_eng_abbr_nme := ws_unit ||' Unit not Found';
       	      END;
 	 	       END IF;
 	 	       
 	 	      IF :key.wrk_full_nsn is not null then
       	   	  IF :key.wrk_full_nsn = ws_nsn then
       	   	  	 null;
       	   	  ELSE
      	   	  	 goto nxt_rec;
       	   	  END IF;
       	  END IF;     
 
      BEGIN
			     select ser_no
				   into   ws_ser_no
  			   from   ils.sss_entry_index
  			   where  scin            = rec1.scin
  			   and    SIN_CHANGE_DTE is null;
  		EXCEPTION
      	  when no_data_found then
      	  message('No serial number for this ICN');
      	  raise form_trigger_failure;
	    END; 
	     
	     wrk_nsn := substr(rec1.nsn,1,4)||substr(rec1.nsn,6,2)||
                  substr(rec1.nsn,9,3)||substr(rec1.nsn,13,4);
                  
       wrk_nsn_desc := null;
           message('values = '||:global.ils_force_no);
     message('values = '||:key.wrk_dte);
     message('value122 = '||ws_unit);
     message('value123 = '|| wrk_nsn);
       
      -- wrk_nsn   := nvl(wrk_nsn,'');
       message('error12');
     if :key.SCR_ICN_2ND_2 is not null then
       PROC_NSN_DESC(wrk_nsn,
    	                wrk_nsn_desc,
       	              wrk_err,
       	              wrk_msg); 
     end if;
     
     
     message('values = '||:global.ils_force_no);
     message('values = '||:key.wrk_dte);
     message('value122 = '||ws_unit);
     message('value123 = '|| wrk_nsn);
                /*,
       	 	      ws_unit,
       	 	      wrk_nsn,
       	 	      rec1.ser_no,
       	 	      ws_eng_abbr_nme,
       	 	      wrk_nsn_desc,
       	 	      ws_mec,
       	 	      ws_me_descr,
       	 	      wrk_gsb_unit,
       	 	      wrk_gsb_desc,
       	 	      ws_itm_purch_prc,
       	 	      ws_heritage_ind,
       	 	      ws_res_val,
       	 	      ws_fair_val,
       	 	      ws_current_val,
       	 	      ws_accum_improve_val,
       	        ws_accum_impair_val,
       	        ws_useful_lfe,
       	        ws_rem_useful_lfe,
       	        ws_ass_cat,
       	        ws_dep_sched, 
       	        ws_start_dep_dte,
       	        ws_lst_dep_fin_YYYYMM,
       	        ws_close_reason);*/
       	             
      
      BEGIN
           	 insert into ils.lsst_prt_asset_fin
       	 	     (user_id,
       	 	      dte_tme_stamp,
       	 	      unit_cde,
       	 	      nsn,
       	 	      ser_no,
       	 	      unit_desc,
       	 	      nsn_desc,
       	 	      me_cde,
       	 	      me_desc,
       	 	      gsb_unit_cde,
       	 	      gsb_desc,
       	 	      itm_purch_prc,
       	 	      heritage_ind,
       	 	      res_val,
       	 	      fair_val,
       	 	      current_val,
       	 	      accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        dep_sched,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        close_reason)
       	 	   values 
       	  	    (:global.ils_force_no,
                :key.wrk_dte,
       	 	      ws_unit,
       	 	      wrk_nsn,
       	 	      rec1.ser_no,
       	 	      ws_eng_abbr_nme,
       	 	      wrk_nsn_desc,
       	 	      ws_mec,
       	 	      ws_me_descr,
       	 	      wrk_gsb_unit,
       	 	      wrk_gsb_desc,
       	 	      ws_itm_purch_prc,
       	 	      ws_heritage_ind,
       	 	      ws_res_val,
       	 	      ws_fair_val,
       	 	      ws_current_val,
       	 	      ws_accum_improve_val,
       	        ws_accum_impair_val,
       	        ws_useful_lfe,
       	        ws_rem_useful_lfe,
       	        ws_ass_cat,
       	        ws_dep_sched, 
       	        ws_start_dep_dte,
       	        ws_lst_dep_fin_YYYYMM,
       	        ws_close_reason);
      EXCEPTION
       	  when dup_val_on_index then
       	  null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);
--         raise form_trigger_failure;       	        
      END;
     -- end if;
ws_cnt := ws_cnt + 1;
<< nxt_rec >>
     null;
     o_cnt := ws_cnt;          
--message('ws_cnt:'||ws_cnt);pause;
       END LOOP;
     END;	
ELSE
		[b]BEGIN
      DECLARE
  	    cursor ser_data is
  	      select a.ser_no, 
  	             a.scin,
  	             a.sin,
  	             nvl(b.unit_holding,0) unit_holding,
  	             nvl(b.dept_holding,0) dept_holding,
  	             nvl(b.unit_located_at,0) unit_located_at,
  	             b.cond_cde,
  	             b.srv_status_cde,
  	             b.pers_located_at,
  	             b.nha_sin,
  	             b.nha_ser_no,
  	             c.nsc||'-'||substr(c.niin,1,2)||'-'||
  	             substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
  	             c.itm_type_cde,
  	             c.struc_lev_cde,
  	             c.agency_ser_cntrl_ind,
  	             d.itm_nme,
  	             e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
  	             e.me_desc  	             
  	        from ils.sss_entry_index a,
  	             ils.sss_er_core b,
  	             ils.item_data c,
  	             ils.names d,
  	             ils.me_master_rec e,
  	             ils.lsst_asset_fin fin
  	       where a.sin_change_reas is null
  	         and a.sin_change_dte is null
  	         and b.scin = a.scin
  	         and fin.scin = b.scin
  	         and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
  	               nvl(ws_unit_cde,0) > 0) or
  	               nvl(ws_unit_cde,0) = 0)
  	         and c.sin = a.sin
  	         and d.sin = c.sin
  	         --and b.scin = 20202029920
  	         and d.itm_nme_type = '1'
  	         and d.name_seq = '1'
  	         and e.sin (+) = a.sin
  	         and (e.me_life_cyc (+) != '08' and
                  e.me_rec_stat (+) not in ('C','X'))
           order by b.unit_holding, a.ser_no;

      BEGIN
      	
      	 For rec01 in ser_data LOOP
       	 BEGIN   
       	 Select scin,
       	        itm_purch_prc,
       	        heritage_ind,
       	        res_val,
       	        fair_val,
       	        current_val,
       	        accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        act_rv_dte,
       	        dep_active_ind,
       	 	      dep_closed_ind,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        nvl(close_reason,'2222')
       	  into  ws_scin,
       	        ws_itm_purch_prc,
       	        ws_heritage_ind,
       	        ws_res_val,
       	        ws_fair_val,
       	        ws_current_val,
       	        ws_accum_improve_val,
       	        ws_accum_impair_val,
       	        ws_useful_lfe,
       	        ws_rem_useful_lfe,
       	        ws_ass_cat,
       	        ws_act_rv_dte,
     	 	        ws_dep_active_ind,
     	 	        ws_dep_closed_ind,
       	        ws_start_dep_dte,
       	        ws_lst_dep_fin_YYYYMM,
       	        ws_close_reason
       	  from  ils.lsst_asset_fin a
        where  trim(rec01.scin) = trim(a.scin)
      
              AND  TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE);
             
[/b]           
   																  	  
 			   ws_found := 'Y';
        /* EXCEPTION  
         	when others then
         	null;*/
        /*	exception
         when no_data_found then
            ws_found := 'N';
              goto nxt_rec;
             null;*/
       END;
--message('ws_scin from table: '||ws_scin);pause;           	 	
   	 	 ws_ser_no := '';
   	 	 ws_inst_ser_no := '';
       	   IF rec01.srv_status_cde = 'C02' then
       	   	  ws_ser_no := rec01.nha_ser_no;
       	   	  ws_inst_ser_no := rec01.ser_no;
       	   	  ws_pers_located_at := rec01.pers_located_at;
       	      BEGIN
       	   	    select a.scin,
       	   	           a.sin,
       	   	           b.srv_status_cde,
       	   	           b.unit_holding,
       	   	           nvl(b.dept_holding,0),
       	   	           nvl(b.unit_located_at,0),
       	   	           b.cond_cde,
       	   	           lpad(to_char(c.nsc),4,'0')||'-'||substr(c.niin,1,2)||'-'||
       	   	           substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
       	   	           c.itm_type_cde,
       	   	           c.struc_lev_cde,
       	   	           c.agency_ser_cntrl_ind,
       	   	           c.cntr_body_cde,
       	   	           e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
       	   	           e.me_desc
       	   	      into ws_scin, 
       	   	           ws_sin,
       	   	           ws_srv_status_cde,
       	   	           ws_unit_holding,
       	   	           ws_dept_holding,
       	   	           ws_unit_located_at,
       	   	           ws_cond_cde,
       	   	           ws_nsn,
       	   	           ws_itm_type_cde,
       	   	           ws_struc_lev_cde,
       	   	           ws_agency_ser_cntrl_ind,
       	   	           ws_cntr_body_cde,
       	   	           ws_mec,
       	   	           ws_me_descr
       	   	      from ils.sss_entry_index a,
       	   	           ils.sss_er_core b,
       	   	           ils.item_data c,
       	   	           ils.names d,
       	   	           ils.me_master_rec e
       	   	     where a.ser_no = rec01.nha_ser_no
       	   	       and a.sin = rec01.nha_sin
       	   	       and a.sin_change_reas is null
       	   	       and a.sin_change_dte is null
       	   	       and b.scin = a.scin
       	   	       and c.sin = a.sin
       	   	       and d.sin = c.sin
       	   	       and d.itm_nme_type = '1'
       	   	       and d.name_seq = '1'
       	   	       and e.sin (+) = a.sin
       	   	       and (e.me_life_cyc (+) != '08' and
       	   	            e.me_rec_stat (+) not in ('C','X'));
      	      exception
       	      	  when no_data_found then
       	      	       ws_ser_no := 'INVALID - '||ws_ser_no;
       	      	       ws_srv_status_cde := '';
       	      END;
       	   ELSE
       	   	  ws_scin := rec01.scin; 
      	   	  ws_sin := rec01.sin;
   	          ws_srv_status_cde := rec01.srv_status_cde;
       	   	  ws_unit_holding := nvl(rec01.unit_holding,0);
       	   	  ws_dept_holding := nvl(rec01.dept_holding,0);
       	   	  ws_unit_located_at := nvl(rec01.unit_located_at,0);
       	   	  ws_cond_cde := rec01.cond_cde;
       	   	  ws_pers_located_at := rec01.pers_located_at;
       	   	  ws_nsn := rec01.nsn;
       	   	  ws_itm_type_cde := rec01.itm_type_cde;
       	   	  ws_struc_lev_cde := rec01.struc_lev_cde;
       	   	  ws_agency_ser_cntrl_ind := rec01.agency_ser_cntrl_ind;
              ws_mec := rec01.mec;
              ws_me_descr := rec01.me_desc;
      	   	  ws_ser_no := rec01.ser_no;
       	   	  ws_inst_ser_no := '';
       	   END IF;
-- Set up values for print table       	                     	                      
       	   IF :key.scr_req_unit is null or
       	   	  :key.wrk_org_func = 'HQ' then
       	   	  ws_unit := ws_unit_holding;
      	   ELSE
       	 	 	  IF :key.scr_all_units = 'Y' then
       	 	 	     ws_unit := 0;
       	 	       BEGIN
                   select 'X' 
                     into ws_dummy
                     from dual
                    where ws_unit_holding in (select unit_cde
                                                from ils.unt
                                               where comnd_cde = :key.wrk_comnd_cde);
                   ws_unit := ws_unit_holding;
                 exception
       	 	    	   when no_data_found then
       	 	    	        ws_unit := 0;
       	 	    	        goto nxt_rec;
       	 	       END;
       	 	 	  ELSE
       	 	 	  	ws_unit := ws_unit_cde;
      	      END IF;
       	   END IF;
       	
       	IF :key.scr_all_units = 'Y' then
		        wrk_gsb_unit := :key.scr_req_unit;
		        wrk_gsb_desc := :key.scr_unit_name;
		     ELSE
		     BEGIN
		     	    select unit_cde,
			  	           eng_abbr_nme
				      into   wrk_gsb_unit,
			  	           wrk_gsb_desc
  			      from 	 ils.unt
				      where  comnd_cde = :key.wrk_comnd_cde 
				      and    org_func  = 'CM';
			      END;
      END IF;
       	   
       	  IF :key.wrk_full_nsn is not null then
       	   	  IF :key.wrk_full_nsn = ws_nsn then
       	   	  	 null;
       	   	  ELSE
       	   	  	 goto nxt_rec;
       	   	  END IF;
       	  END IF; 

       	  IF :key.scr_me_cde is not null then
      	 	 	  IF :key.scr_var_cde is not null then
              	 IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then 
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_fam_cde is not null then 
              	 IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_cat_cde is not null then
              	 IF :key.scr_cat_cde = substr(ws_mec,1,1) then
              	 	  null;
              	 ELSE
       	 	 	  	    goto nxt_rec;
         	 	 	   END IF;
              END IF;
      	 	 ELSE
      	 	    :key.scr_me_cde  := ws_mec;
      	 	    :key.scr_mec_desc     := ws_me_descr;   	
       	 	 END IF;
  
-- Unit Description
           IF nvl(ws_unit,0) > 0 then
       	 	    BEGIN
       	 	      select eng_abbr_nme
       	 	        into ws_eng_abbr_nme
       	 	        from ils.unt
       	 	       where unit_cde = ws_unit;
       	 	      exception
       	 	        when no_data_found then
       	 	             ws_eng_abbr_nme := ws_unit ||' Unit not Found';
       	      END;
 	 	       END IF;

--       NSN Description
        wrk_nsn := substr(ws_nsn,1,4)||substr(ws_nsn,6,2)||
                   substr(ws_nsn,9,3)||substr(ws_nsn,13,4);
        wrk_nsn_desc := null;
        
      

/*message('wrk_nsn = '||wrk_nsn);
message('wrk_nsn_desc = '||wrk_nsn_desc);
message('wrk_err = '||wrk_err);
message('wrk_msg = '||wrk_msg);*/
--message('error12344');
-- wrk_nsn  := nvl( wrk_nsn ,'');-message('error12');
if :key.SCR_ICN_2ND_2 is not  null then 
--	message('insideif');
        PROC_NSN_DESC(wrk_nsn,
    	                wrk_nsn_desc,
       	              wrk_err,
       	              wrk_msg);
  --  end if;

--message('afterif');
/** New additions/changes to Spec: 22/11/2010 - Marius**/
       elsif ws_found = 'N' then
          IF  :key.scr_depr_sched in ('N','A') then
         	    IF  :key.scr_purch_price in ('N','A')
         	    AND :key.scr_fair_val in ('N','A') 
         	    AND :key.scr_asset_cat = 'A'
         	    AND :key.scr_herit_asset in ('N','A') then
         	        NULL; 
         	    ELSE
         	        goto nxt_rec;
         	    END IF;
         	ELSE
         	   goto nxt_rec;
         	END IF;
       ELSIF ws_found = 'Y' then	
              IF :key.scr_depr_sched = 'A' then
         	       null;
         	    ELSIF  :key.scr_depr_sched = 'N' then
         	          IF  (ws_dep_active_ind = 'N'
         	          AND  ws_dep_closed_ind = 'N') then
         	              NULL;
         	          ELSE
         	             goto nxt_rec;
         	          END IF;     
         	    ELSIF  :key.scr_depr_sched = 'I' 
    	          AND  ws_dep_active_ind = 'Y' then
        	           NULL; 
              ELSIF  :key.scr_depr_sched = 'C' 
     	          AND  ws_dep_closed_ind = 'Y' then
        	           NULL; 
         	    ELSE          	     	      	         	
         	       goto nxt_rec;
         	    END IF;
         ELSE	     
         	  goto nxt_rec;   
         END IF;
         
         message('inserted32');
        
       	              
/*       IF :key.scr_depr_sched = 'A' then
         	null;
       ELSIF   :key.scr_depr_sched = 'N' then
         	     IF  (ws_dep_active_ind = 'N'
         	         AND  ws_dep_closed_ind = 'N')
         	         OR   ws_found = 'N' then	
         	         NULL; 
         	     ELSE
         	     	  goto nxt_rec;
         	     END IF; 
        ELSIF  :key.scr_depr_sched = 'I' then
    	         IF ws_found = 'Y'
    	            AND ws_dep_active_ind = 'Y' then
        	        NULL; 
         	     ELSE 
         	     	  goto nxt_rec;
         	     END IF;
        ELSIF  :key.scr_depr_sched = 'C' then
     	         IF ws_found = 'Y'
    	            AND ws_dep_closed_ind = 'Y' then
        	        NULL; 
         	     ELSE          	     	      	         	
         	        goto nxt_rec;
         	     END IF;
         ELSE	     
         	  goto nxt_rec;   
         END IF; */
       
       IF :key.scr_rec_dte_fr is not null then
       	  IF (:key.scr_rec_dte_fr = ws_act_rv_dte
       	   	   OR :key.scr_rec_dte_fr < ws_act_rv_dte)
       	   	  AND ws_found = 'Y' then
       	   	  NULL;
          ELSE
       	   	  goto nxt_rec;
          END IF;
       ELSE
       	  null;
       END IF;   
       
       message('inserted123');
       
       IF :key.scr_rec_dte_to is not null then
          IF (:key.scr_rec_dte_to >= ws_act_rv_dte
       	   	  AND ws_found = 'Y') then
       	   	  NULL;
          ELSE
       	      goto nxt_rec;
          END IF;
       ELSE
       	  null;
       END IF;   
         message('inserted12');     
       
       IF ws_found = 'Y' then
          IF :key.scr_purch_price = 'A' then 
      	      null;
      	  ELSIF    	
       	     :key.scr_purch_price = 'Y' then
         	   IF ws_purch_price is not null then 
       	   	    NULL;
       	   	 ELSE
       	   	    goto nxt_rec;   
         	   END IF;
         	ELSIF  
         	   	:key.scr_purch_price = 'N' then
         	   	   IF ws_purch_price is null then 
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	  ELSE	     
       	  	  goto nxt_rec;   
       	  END IF;
       ELSIF ws_found = 'N' then
          	 IF :key.scr_purch_price in ('A','N') then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;	  
       ELSE
          goto nxt_rec;    	
       END IF;
       
       message('inserted1');
       
       IF 	ws_found = 'Y' then
           IF :key.scr_fair_val = 'A' then
       	      null;
       	   ELSIF   	
       	      :key.scr_fair_val = 'Y' then
       	   	  IF ws_fair_val is not null then 
       	   	     NULL;
       	   	  ELSE
       	   	     goto nxt_rec;   
         	    END IF;
         	  ELSIF  
         	   	:key.scr_fair_val = 'N' then 
         	   	   IF ws_fair_val is null then 
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	    ELSE	     
       	   	  goto nxt_rec;   
       	    END IF;
       ELSIF ws_found = 'N' then
          	 IF :key.scr_fair_val in ('A','N') then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;	   
       ELSE
          goto nxt_rec;	   	
       END IF;
     
     message('inserted');
         
       IF 	ws_found = 'Y' then
           IF :key.scr_asset_cat = 'A' then
       	      IF ws_ass_cat ='MJ' or
       	         ws_ass_cat = 'MN' then	
                 null;
       	      END IF;
       	   ELSIF   
       	      :key.scr_asset_cat = 'MJ' then
         	    IF ws_ass_cat ='MJ' then
       	   	     NULL;
       	   	  ELSE
       	   	     goto nxt_rec;   
         	    END IF;
         	 ELSIF  
         	   	:key.scr_asset_cat = 'MN' then 
         	   	   IF ws_ass_cat = 'MN' then
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	   ELSE	     
       	   	  goto nxt_rec;   
       	   END IF;
       ELSIF ws_found = 'N' then
          	 IF :key.scr_herit_asset = 'A' then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;
       ELSE
         goto nxt_rec;
       END IF;
       
       IF ws_found = 'Y' then
       	  IF :key.scr_herit_asset = 'A' then
       	     null;
       	  ELSIF   
       	    :key.scr_herit_asset = 'Y' then
         	  IF ws_heritage_ind = 'Y' then 
       	  	    NULL;
       	    ELSE
       	        goto nxt_rec;   
         	  END IF;
         	ELSIF  
         	   	:key.scr_herit_asset = 'N' then 
         	   	   IF ws_heritage_ind = 'N' then
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	   ELSE	     
       	   	  goto nxt_rec;   
         	 END IF;	
       ELSIF ws_found = 'N' then
          	 IF :key.scr_herit_asset in ('A','N') then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;	  
       ELSE
         goto nxt_rec;
       END IF;
       
       message('inserted');
       
       BEGIN
           	 insert into ils.lsst_prt_asset_fin
       	 	     (user_id,
       	 	      dte_tme_stamp,
       	 	      unit_cde,
       	 	      nsn,
       	 	      ser_no,
       	 	      unit_desc,
       	 	      nsn_desc,
       	 	      me_cde,
       	 	      me_desc,
       	 	      gsb_unit_cde,
       	 	      gsb_desc,
       	 	      itm_purch_prc,
       	 	      heritage_ind,
       	 	      res_val,
       	 	      fair_val,
       	 	      current_val,
       	 	      accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        dep_sched,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        close_reason)
       	 	   values 
       	  	    (:global.ils_force_no,
                :key.wrk_dte,
       	 	      ws_unit,
       	 	      wrk_nsn,
       	 	      ws_ser_no,
       	 	      ws_eng_abbr_nme,
       	 	      wrk_nsn_desc,
       	 	      ws_mec,
       	 	      ws_me_descr,
       	 	      wrk_gsb_unit,
       	 	      wrk_gsb_desc,
       	 	      ws_itm_purch_prc,
       	 	      ws_heritage_ind,
       	 	      ws_res_val,
       	 	      ws_fair_val,
       	 	      ws_current_val,
       	 	      ws_accum_improve_val,
       	        ws_accum_impair_val,
       	        ws_useful_lfe,
       	        ws_rem_useful_lfe,
       	        ws_ass_cat,
       	        ws_dep_sched, 
       	        ws_start_dep_dte,
       	        ws_lst_dep_fin_YYYYMM,
       	        ws_close_reason);
       EXCEPTION
       	  when dup_val_on_index then
       	  null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);pause;
--         raise form_trigger_failure; */
       END;
     --  end if;
     
     message('itcomes');
    	message('scin1 = '||rec01.scin);
      
<< nxt_rec >>
       null; 
ws_cnt:= ws_cnt + 1; 
    o_cnt := ws_cnt;           
      	 END LOOP;
      
       END;
      
		 END;
		-- message('itcomes22');
	END IF;
--	message('itcomes9999');
END;	
--message('itcome78855s');
END;

Open in new window

Please, don't use INTO here
FOR rec1 IN (scin,
                  itm_purch_prc ,

It must be done this way:

FOR rec1 IN (scin,
                  itm_purch_prc,

...
LOOP
ws_scin:=rec1.scin;
ws_itm_purch_prc:=rec1.itm_purch_prc;
...

END LOOP;
am getting this error
User generated imagethis is the latest code
PROCEDURE PROC_SELECT_DATA (o_cnt OUT NUMBER)IS
BEGIN
	DECLARE
		ws_unit_cde             number(06);
		wrk_gsb_unit            number(06);
		ws_unit                 number(06);
		ws_char_unit_cde        varchar2(06);
		ws_eng_abbr_nme         varchar2(30);
		wrk_gsb_desc            varchar2(30);
		ws_unit_line_ind        varchar2(01);
		ws_act_des              varchar2(01);
		ws_origin               varchar2(100);
		ws_ser_no               varchar2(30);
		ws_inst_ser_no          varchar2(30);
		ws_scin                 number(11);
		ws_sin                  number(11);
		ws_unit_holding         number(06);
		ws_dept_holding         number(06);
		ws_unit_located_at      number(06);
		ws_cond_cde             varchar2(01);
		ws_srv_status_cde       varchar2(3);
		ws_pers_located_at      varchar2(15);
		ws_itm_purch_prc        number(15,4);
		wrk_purch_prc           varchar2(3);
		ws_heritage_ind         varchar2(1);
    wrk_herit_asset         varchar2(3);		
		ws_res_val              number(15,4);
		ws_fair_val             number(15,4);
		wrk_fair_val            varchar2(3);
		ws_current_val          number(15,4);
		ws_accum_improve_val    number(15,4);
		ws_accum_impair_val     number(15,4);
		ws_useful_lfe           number(4);
		ws_rem_useful_lfe       number(4);
		ws_ass_cat              varchar2(2);
		wrk_ass_cat             varchar2(5);
		ws_dep_sched            varchar2(1);
		wrk_dep_sched           varchar2(15);
		ws_start_dep_dte        date;
		ws_close_reason         varchar2(20);
		ws_lst_dep_fin_YYYYMM   number(6);
		ws_prov_reg_no          varchar2(20);
		ws_nsn                  varchar2(16);
		ws_itm_type_cde         varchar2(01);
		ws_struc_lev_cde        varchar2(01);
		ws_agency_ser_cntrl_ind varchar2(01);
		ws_cntr_body_cde        varchar2(02);
		ws_org_func             varchar2(02);
		ws_nsn_descr            varchar2(200);
		ws_mec                  varchar2(04);
		ws_me_descr             varchar2(50);
		ws_sort_cc              number(02);
		ws_sort_srv             number(02);
		ws_err_ind              varchar2(01);
		ws_err_msg              varchar2(80);
		ws_ldesc                varchar2(50);
		ws_sdesc                varchar2(25);
		ws_short_nme            varchar2(30);
		ws_long_nme             varchar2(50);
		ws_stat_ind             varchar2(01);
		ws_oli_ind              varchar2(01);
		ws_pers_details         varchar2(100);
		ws_desc                 varchar2(150);
		ws_rank                 varchar2(15);
		ws_pers_found           varchar2(01);
		ws_un_no                varchar2(10);
		ws_dummy                varchar2(01);
		ws_m_nsn                varchar2(16);
		proc_cfg_tab_no         number;
		proc_mec                varchar2(04);
		proc_mec_nsn            varchar2(16);
		proc_cfg_tab_desc       varchar2(200);
		proc_mec_desc           varchar2(200);
		proc_mec_nsn_desc       varchar2(200);
		proc_exist_corp_ind     varchar2(01);
		proc_err_ind            varchar2(01);
		proc_err_msg            varchar2(80);
		proc_me_niin            varchar2(13);
		wrk_nsn_desc            varchar2(200);
		wrk_err                 varchar2(1);
		wrk_msg                 varchar2(50);
		wrk_nsn                 varchar2(16);
		v_nsn                   varchar2(16);
		wrk_totals              varchar2(3);
		wrk_all_units           varchar2(3);
		ws_found                varchar2(1);
		ws_dep_active_ind       varchar2(1);
		ws_dep_closed_ind       varchar2(1);
		ws_act_rv_dte           date;
		ws_purch_price          number(15,4);
		ws_cnt                  number := 0;
		
	BEGIN
    ws_unit_cde := 0;
    
		IF :key.wrk_org_func  = 'HQ' or
		  (:key.wrk_org_func  = 'CM' and
			 :key.scr_all_units = 'Y') THEN
			 ws_unit_cde     := 0;
		 	 ws_eng_abbr_nme := '';
		ELSE	  
		   ws_unit_cde     := :key.scr_req_unit;
	     ws_eng_abbr_nme := :key.scr_unit_desc;
		END IF;
		
	--	message('testt09');
	--	message(:key.scr_ser_no);
	message('cmd = '||:key.wrk_comnd_cde );
	message('unitcde = '||ws_unit_cde);
IF :key.scr_ser_no is not null then
     DECLARE
     	 CURSOR         sel_ser_no is
       select         s.sin,
                      s.ser_no, 
       	              e.scin,
       	              e.unit_holding,
       	              me_cat_cde||me_family_cde||me_var_cde mec,
       	              i.nsc||'-'||substr(i.niin,1,2)||'-'||
  	                  substr(i.niin,3,3)||'-'||substr(i.niin,6,4) nsn,
  	                  i.agency_ser_cntrl_ind,
       	              me_desc
       from           ils.me_master_rec          m,
       	              ils.sss_entry_index        s,
	         		        ils.sss_er_core            e,
	         		        ils.item_data              i
      where           s.ser_no			    = :key.scr_ser_no 
       and            e.scin 				    = s.scin
			 and            s.sin_change_reas is null
  	   and            s.sin_change_dte is null 
			 and           ((nvl(e.unit_holding,0) = nvl(ws_unit_cde,0) and
  	                  nvl(ws_unit_cde,0) > 0) or
  	                  nvl(ws_unit_cde,0) = 0) 
	     and            m.sin (+)            = s.sin
 	     and            i.sin             = s.sin
-- 	      or             s.sin            = :key.wrk_sin)
  	   and            (m.me_life_cyc (+) != '08' and
                      m.me_rec_stat (+) not in ('C','X'));
	   	 
     BEGIN
     	 FOR rec1 IN sel_ser_no
	     LOOP
      BEGIN
			     select itm_purch_prc,
       	 	        heritage_ind,
       	 	        res_val,
       	 	        fair_val,
       	 	        current_val,
       	 	        accum_improve_val,
       	 	        accum_impair_val,
       	 	        useful_lfe,
       	 	        rem_useful_lfe,
       	 	        ass_cat,
       	 	        act_rv_dte,
       	 	        dep_active_ind,
       	 	        dep_closed_ind,
       	 	        start_dep_dte,
       	 	        lst_dep_fin_YYYYMM,
       	 	        close_reason
			     into   
			            ws_itm_purch_prc,
       	 	        ws_heritage_ind,
       	 	        ws_res_val,
       	 	        ws_fair_val,
       	 	        ws_current_val,
       	 	        ws_accum_improve_val,
       	 	        ws_accum_impair_val,
       	 	        ws_useful_lfe,
       	 	        ws_rem_useful_lfe,
       	 	        ws_ass_cat,
       	 	        ws_act_rv_dte,
       	 	        ws_dep_active_ind,
       	 	        ws_dep_closed_ind,
       	 	        ws_start_dep_dte,
       	 	        ws_lst_dep_fin_YYYYMM,
       	 	        ws_close_reason
				    from   ils.lsst_asset_fin  a
  			   where  trim(a.scin)            = trim(rec1.scin)
  			  -- where scin = 102672956 
  			     /*and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
   																	      AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/  																	      
   					 /*and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd'))) 
             AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE))and  (:key.scr_rec_dte_fr is null or :key.scr_rec_dte_to is null);*/		
             AND  TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE);
            -- AND  TRUNC(a.act_rv_dte) BETWEEN NVL(:key.scr_rec_dte_fr,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(:key.scr_rec_dte_to,SYSDATE);
             
             										      
   	         																      
  			   ws_found := 'Y';
      /*EXCEPTION  
      	when others then
      	null;*/
      	exception
         when no_data_found THEN
			      ws_found := 'N';
-- 			      goto nxt_rec;
            null;
      END;
      
      ws_scin := rec1.scin; 
      ws_sin := rec1.sin;
   	  ws_unit_holding := nvl(rec1.unit_holding,0);
      ws_mec := rec1.mec;
      ws_me_descr := rec1.me_desc;
      ws_ser_no := rec1.ser_no;
      ws_inst_ser_no := '';       	   	  
	     	
      	IF :key.scr_req_unit is null or
       	   :key.wrk_org_func = 'HQ' then
       	   	  ws_unit := ws_unit_holding;
       	ELSE
       	 	 	  IF :key.scr_all_units = 'Y' then
       	 	 	     ws_unit := 0;
       	 	       BEGIN
                   select 'X' 
                     into ws_dummy
                     from dual
                    where ws_unit_holding in (select unit_cde
                                                from ils.unt
                                               where comnd_cde = :key.wrk_comnd_cde);
                   ws_unit := ws_unit_holding;
       	 	       exception
       	 	    	   when no_data_found THEN
       	 	    	        ws_unit := 0;
      	 	    	        goto nxt_rec;
       	 	       END;
       	 	 	  ELSE
       	 	 	  	ws_unit := ws_unit_cde;
       	      END IF;
	     END IF;
	     	IF :key.scr_all_units = 'Y' then
		        wrk_gsb_unit := :key.scr_req_unit;
		        wrk_gsb_desc := :key.scr_unit_name;
		    ELSE
		     BEGIN
		     	    select unit_cde,
			  	           eng_abbr_nme
				      into   wrk_gsb_unit,
			  	           wrk_gsb_desc
  			      from 	 ils.unt
				      where  comnd_cde = :key.wrk_comnd_cde 
				      and    org_func  = 'CM';
			      END;
     END IF;
     
        IF :key.scr_me_cde is not null then
      	 	 	  IF :key.scr_var_cde is not null then
              	 IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then 
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_fam_cde is not null then 
              	 IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_cat_cde is not null then
              	 IF :key.scr_cat_cde = substr(ws_mec,1,1) then
              	 	  null;
              	 ELSE
       	 	 	  	    goto nxt_rec;
         	 	 	   END IF;
              END IF;
           ELSE
      	 	    :key.scr_me_cde  := ws_mec;
      	 	    :key.scr_mec_desc     := ws_me_descr;    
       	 	 END IF;   
-- Unit Description
           IF nvl(ws_unit,0) > 0 then
       	 	    BEGIN
       	 	      select eng_abbr_nme
       	 	        into ws_eng_abbr_nme
       	 	        from ils.unt
       	 	       where unit_cde = ws_unit;
       	 	      exception
       	 	        when no_data_found then
       	 	             ws_eng_abbr_nme := ws_unit ||' Unit not Found';
       	      END;
 	 	       END IF;
 	 	       
 	 	      IF :key.wrk_full_nsn is not null then
       	   	  IF :key.wrk_full_nsn = ws_nsn then
       	   	  	 null;
       	   	  ELSE
      	   	  	 goto nxt_rec;
       	   	  END IF;
       	  END IF;     
 
      BEGIN
			     select ser_no
				   into   ws_ser_no
  			   from   ils.sss_entry_index
  			   where  scin            = rec1.scin
  			   and    SIN_CHANGE_DTE is null;
  		EXCEPTION
      	  when no_data_found then
      	  message('No serial number for this ICN');
      	  raise form_trigger_failure;
	    END; 
	     
	     wrk_nsn := substr(rec1.nsn,1,4)||substr(rec1.nsn,6,2)||
                  substr(rec1.nsn,9,3)||substr(rec1.nsn,13,4);
                  
       wrk_nsn_desc := null;
           message('values = '||:global.ils_force_no);
     message('values = '||:key.wrk_dte);
     message('value122 = '||ws_unit);
     message('value123 = '|| wrk_nsn);
       
      -- wrk_nsn   := nvl(wrk_nsn,'');
       message('error12');
     if :key.SCR_ICN_2ND_2 is not null then
       PROC_NSN_DESC(wrk_nsn,
    	                wrk_nsn_desc,
       	              wrk_err,
       	              wrk_msg); 
     end if;
     
     
     message('values = '||:global.ils_force_no);
     message('values = '||:key.wrk_dte);
     message('value122 = '||ws_unit);
     message('value123 = '|| wrk_nsn);
                /*,
       	 	      ws_unit,
       	 	      wrk_nsn,
       	 	      rec1.ser_no,
       	 	      ws_eng_abbr_nme,
       	 	      wrk_nsn_desc,
       	 	      ws_mec,
       	 	      ws_me_descr,
       	 	      wrk_gsb_unit,
       	 	      wrk_gsb_desc,
       	 	      ws_itm_purch_prc,
       	 	      ws_heritage_ind,
       	 	      ws_res_val,
       	 	      ws_fair_val,
       	 	      ws_current_val,
       	 	      ws_accum_improve_val,
       	        ws_accum_impair_val,
       	        ws_useful_lfe,
       	        ws_rem_useful_lfe,
       	        ws_ass_cat,
       	        ws_dep_sched, 
       	        ws_start_dep_dte,
       	        ws_lst_dep_fin_YYYYMM,
       	        ws_close_reason);*/
       	             
      
      BEGIN
           	 insert into ils.lsst_prt_asset_fin
       	 	     (user_id,
       	 	      dte_tme_stamp,
       	 	      unit_cde,
       	 	      nsn,
       	 	      ser_no,
       	 	      unit_desc,
       	 	      nsn_desc,
       	 	      me_cde,
       	 	      me_desc,
       	 	      gsb_unit_cde,
       	 	      gsb_desc,
       	 	      itm_purch_prc,
       	 	      heritage_ind,
       	 	      res_val,
       	 	      fair_val,
       	 	      current_val,
       	 	      accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        dep_sched,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        close_reason)
       	 	   values 
       	  	    (:global.ils_force_no,
                :key.wrk_dte,
       	 	      ws_unit,
       	 	      wrk_nsn,
       	 	      rec1.ser_no,
       	 	      ws_eng_abbr_nme,
       	 	      wrk_nsn_desc,
       	 	      ws_mec,
       	 	      ws_me_descr,
       	 	      wrk_gsb_unit,
       	 	      wrk_gsb_desc,
       	 	      ws_itm_purch_prc,
       	 	      ws_heritage_ind,
       	 	      ws_res_val,
       	 	      ws_fair_val,
       	 	      ws_current_val,
       	 	      ws_accum_improve_val,
       	        ws_accum_impair_val,
       	        ws_useful_lfe,
       	        ws_rem_useful_lfe,
       	        ws_ass_cat,
       	        ws_dep_sched, 
       	        ws_start_dep_dte,
       	        ws_lst_dep_fin_YYYYMM,
       	        ws_close_reason);
      EXCEPTION
       	  when dup_val_on_index then
       	  null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);
--         raise form_trigger_failure;       	        
      END;
     -- end if;
ws_cnt := ws_cnt + 1;
<< nxt_rec >>
     null;
     o_cnt := ws_cnt;          
--message('ws_cnt:'||ws_cnt);pause;
       END LOOP;
     END;	
ELSE
		BEGIN
      DECLARE
  	    cursor ser_data is
  	      select a.ser_no, 
  	             a.scin,
  	             a.sin,
  	             nvl(b.unit_holding,0) unit_holding,
  	             nvl(b.dept_holding,0) dept_holding,
  	             nvl(b.unit_located_at,0) unit_located_at,
  	             b.cond_cde,
  	             b.srv_status_cde,
  	             b.pers_located_at,
  	             b.nha_sin,
  	             b.nha_ser_no,
  	             c.nsc||'-'||substr(c.niin,1,2)||'-'||
  	             substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
  	             c.itm_type_cde,
  	             c.struc_lev_cde,
  	             c.agency_ser_cntrl_ind,
  	             d.itm_nme,
  	             e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
  	             e.me_desc  	             
  	        from ils.sss_entry_index a,
  	             ils.sss_er_core b,
  	             ils.item_data c,
  	             ils.names d,
  	             ils.me_master_rec e,
  	             ils.lsst_asset_fin fin
  	       where a.sin_change_reas is null
  	         and a.sin_change_dte is null
  	         and b.scin = a.scin
  	         and fin.scin = b.scin
  	         and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
  	               nvl(ws_unit_cde,0) > 0) or
  	               nvl(ws_unit_cde,0) = 0)
  	         and c.sin = a.sin
  	         and d.sin = c.sin
  	         --and b.scin = 20202029920
  	         and d.itm_nme_type = '1'
  	         and d.name_seq = '1'
  	         and e.sin (+) = a.sin
  	         and (e.me_life_cyc (+) != '08' and
                  e.me_rec_stat (+) not in ('C','X'))
           order by b.unit_holding, a.ser_no;

      BEGIN
      	
      	for rec1 in (scin,
       	        itm_purch_prc,
       	        heritage_ind,
       	        res_val,
       	        fair_val,
       	        current_val,
       	        accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        act_rv_dte,
       	        dep_active_ind,
       	 	      dep_closed_ind,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        close_reason)
       	        --nvl(close_reason,'2222')
       --	  from  ils.lsst_asset_fin a
       --	  where  TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE)
      --  where  trim(ser_data.scin) = trim(a.scin);       
        loop
        	ws_scin := rec1.scin;
       	  ws_itm_purch_prc := rec1.itm_purch_prc;
       	  ws_heritage_ind := rec1.res_val;
       	  ws_res_val := rec1.fair_val;
       	  ws_fair_val := rec1.current_val;
       	  ws_current_val := rec1.accum_improve_val;
       	  ws_accum_improve_val := rec1. accum_impair_val;
       	  ws_accum_impair_val := rec1. useful_lfe;
       	  ws_useful_lfe ;= rec1.ass_cat;
       	  ws_rem_useful_lfe := rec1.act_rv_dte;
       	  ws_ass_cat := rec1.ass_cat;
       	  ws_act_rv_dte := rec1.act_rv_dte;
     	 	  ws_dep_active_ind := rec1.dep_active_ind;
     	 	  ws_dep_closed_ind := rec1.dep_closed_ind;
       	  ws_start_dep_dte :=  rec1.start_dep_dte;
       	  ws_lst_dep_fin_YYYYMM := lst_dep_fin_YYYYMM;
       	  ws_close_reason := rec1.close_reason;
        	
      	
      /*	 For rec01 in ser_data LOOP
       	 BEGIN   
       	 Select scin,
       	        itm_purch_prc,
       	        heritage_ind,
       	        res_val,
       	        fair_val,
       	        current_val,
       	        accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        act_rv_dte,
       	        dep_active_ind,
       	 	      dep_closed_ind,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        nvl(close_reason,'2222')
       	  into  ws_scin,
       	        ws_itm_purch_prc,
       	        ws_heritage_ind,
       	        ws_res_val,
       	        ws_fair_val,
       	        ws_current_val,
       	        ws_accum_improve_val,
       	        ws_accum_impair_val,
       	        ws_useful_lfe,
       	        ws_rem_useful_lfe,
       	        ws_ass_cat,
       	        ws_act_rv_dte,
     	 	        ws_dep_active_ind,
     	 	        ws_dep_closed_ind,
       	        ws_start_dep_dte,
       	        ws_lst_dep_fin_YYYYMM,
       	        ws_close_reason
       	  from  ils.lsst_asset_fin a
        where  trim(rec01.scin) = trim(a.scin)
       --	where scin = 20202029920 
       	   /*and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
   																  	  AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/
   				  /*	and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd'))) 
             AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE));*/            
           --  AND  TRUNC(a.act_rv_dte) BETWEEN NVL(:key.scr_rec_dte_fr,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(:key.scr_rec_dte_to,SYSDATE);
         --     AND  TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE);
             
            /* and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd'))) 
             AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE))and  (:key.scr_rec_dte_fr is null or :key.scr_rec_dte_to is null);*/															  	    																  	  
   						
   						message('scin03212+++ = '||rec01.scin);
   						message('ws_close_reason = '||ws_close_reason);
   						message('ws_itm_purch_prc = '||ws_itm_purch_prc);
   																  	  
 			   ws_found := 'Y';
        /* EXCEPTION  
         	when others then
         	null;*/
        /*	exception
         when no_data_found then
            ws_found := 'N';
              goto nxt_rec;
             null;*/
       END;
--message('ws_scin from table: '||ws_scin);pause;           	 	
   	 	 ws_ser_no := '';
   	 	 ws_inst_ser_no := '';
       	   IF rec01.srv_status_cde = 'C02' then
       	   	  ws_ser_no := rec01.nha_ser_no;
       	   	  ws_inst_ser_no := rec01.ser_no;
       	   	  ws_pers_located_at := rec01.pers_located_at;
       	      BEGIN
       	   	    select a.scin,
       	   	           a.sin,
       	   	           b.srv_status_cde,
       	   	           b.unit_holding,
       	   	           nvl(b.dept_holding,0),
       	   	           nvl(b.unit_located_at,0),
       	   	           b.cond_cde,
       	   	           lpad(to_char(c.nsc),4,'0')||'-'||substr(c.niin,1,2)||'-'||
       	   	           substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
       	   	           c.itm_type_cde,
       	   	           c.struc_lev_cde,
       	   	           c.agency_ser_cntrl_ind,
       	   	           c.cntr_body_cde,
       	   	           e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
       	   	           e.me_desc
       	   	      into ws_scin, 
       	   	           ws_sin,
       	   	           ws_srv_status_cde,
       	   	           ws_unit_holding,
       	   	           ws_dept_holding,
       	   	           ws_unit_located_at,
       	   	           ws_cond_cde,
       	   	           ws_nsn,
       	   	           ws_itm_type_cde,
       	   	           ws_struc_lev_cde,
       	   	           ws_agency_ser_cntrl_ind,
       	   	           ws_cntr_body_cde,
       	   	           ws_mec,
       	   	           ws_me_descr
       	   	      from ils.sss_entry_index a,
       	   	           ils.sss_er_core b,
       	   	           ils.item_data c,
       	   	           ils.names d,
       	   	           ils.me_master_rec e
       	   	     where a.ser_no = rec01.nha_ser_no
       	   	       and a.sin = rec01.nha_sin
       	   	       and a.sin_change_reas is null
       	   	       and a.sin_change_dte is null
       	   	       and b.scin = a.scin
       	   	       and c.sin = a.sin
       	   	       and d.sin = c.sin
       	   	       and d.itm_nme_type = '1'
       	   	       and d.name_seq = '1'
       	   	       and e.sin (+) = a.sin
       	   	       and (e.me_life_cyc (+) != '08' and
       	   	            e.me_rec_stat (+) not in ('C','X'));
      	      exception
       	      	  when no_data_found then
       	      	       ws_ser_no := 'INVALID - '||ws_ser_no;
       	      	       ws_srv_status_cde := '';
       	      END;
       	   ELSE
       	   	  ws_scin := rec01.scin; 
      	   	  ws_sin := rec01.sin;
   	          ws_srv_status_cde := rec01.srv_status_cde;
       	   	  ws_unit_holding := nvl(rec01.unit_holding,0);
       	   	  ws_dept_holding := nvl(rec01.dept_holding,0);
       	   	  ws_unit_located_at := nvl(rec01.unit_located_at,0);
       	   	  ws_cond_cde := rec01.cond_cde;
       	   	  ws_pers_located_at := rec01.pers_located_at;
       	   	  ws_nsn := rec01.nsn;
       	   	  ws_itm_type_cde := rec01.itm_type_cde;
       	   	  ws_struc_lev_cde := rec01.struc_lev_cde;
       	   	  ws_agency_ser_cntrl_ind := rec01.agency_ser_cntrl_ind;
              ws_mec := rec01.mec;
              ws_me_descr := rec01.me_desc;
      	   	  ws_ser_no := rec01.ser_no;
       	   	  ws_inst_ser_no := '';
       	   END IF;
-- Set up values for print table       	                     	                      
       	   IF :key.scr_req_unit is null or
       	   	  :key.wrk_org_func = 'HQ' then
       	   	  ws_unit := ws_unit_holding;
      	   ELSE
       	 	 	  IF :key.scr_all_units = 'Y' then
       	 	 	     ws_unit := 0;
       	 	       BEGIN
                   select 'X' 
                     into ws_dummy
                     from dual
                    where ws_unit_holding in (select unit_cde
                                                from ils.unt
                                               where comnd_cde = :key.wrk_comnd_cde);
                   ws_unit := ws_unit_holding;
                 exception
       	 	    	   when no_data_found then
       	 	    	        ws_unit := 0;
       	 	    	        goto nxt_rec;
       	 	       END;
       	 	 	  ELSE
       	 	 	  	ws_unit := ws_unit_cde;
      	      END IF;
       	   END IF;
       	
       	IF :key.scr_all_units = 'Y' then
		        wrk_gsb_unit := :key.scr_req_unit;
		        wrk_gsb_desc := :key.scr_unit_name;
		     ELSE
		     BEGIN
		     	    select unit_cde,
			  	           eng_abbr_nme
				      into   wrk_gsb_unit,
			  	           wrk_gsb_desc
  			      from 	 ils.unt
				      where  comnd_cde = :key.wrk_comnd_cde 
				      and    org_func  = 'CM';
			      END;
      END IF;
       	   
       	  IF :key.wrk_full_nsn is not null then
       	   	  IF :key.wrk_full_nsn = ws_nsn then
       	   	  	 null;
       	   	  ELSE
       	   	  	 goto nxt_rec;
       	   	  END IF;
       	  END IF; 

       	  IF :key.scr_me_cde is not null then
      	 	 	  IF :key.scr_var_cde is not null then
              	 IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then 
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_fam_cde is not null then 
              	 IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_cat_cde is not null then
              	 IF :key.scr_cat_cde = substr(ws_mec,1,1) then
              	 	  null;
              	 ELSE
       	 	 	  	    goto nxt_rec;
         	 	 	   END IF;
              END IF;
      	 	 ELSE
      	 	    :key.scr_me_cde  := ws_mec;
      	 	    :key.scr_mec_desc     := ws_me_descr;   	
       	 	 END IF;
  
-- Unit Description
           IF nvl(ws_unit,0) > 0 then
       	 	    BEGIN
       	 	      select eng_abbr_nme
       	 	        into ws_eng_abbr_nme
       	 	        from ils.unt
       	 	       where unit_cde = ws_unit;
       	 	      exception
       	 	        when no_data_found then
       	 	             ws_eng_abbr_nme := ws_unit ||' Unit not Found';
       	      END;
 	 	       END IF;

--       NSN Description
        wrk_nsn := substr(ws_nsn,1,4)||substr(ws_nsn,6,2)||
                   substr(ws_nsn,9,3)||substr(ws_nsn,13,4);
        wrk_nsn_desc := null;
        
      

/*message('wrk_nsn = '||wrk_nsn);
message('wrk_nsn_desc = '||wrk_nsn_desc);
message('wrk_err = '||wrk_err);
message('wrk_msg = '||wrk_msg);*/
--message('error12344');
-- wrk_nsn  := nvl( wrk_nsn ,'');-message('error12');
if :key.SCR_ICN_2ND_2 is not  null then 
--	message('insideif');
        PROC_NSN_DESC(wrk_nsn,
    	                wrk_nsn_desc,
       	              wrk_err,
       	              wrk_msg);
  --  end if;

--message('afterif');
/** New additions/changes to Spec: 22/11/2010 - Marius**/
       elsif ws_found = 'N' then
          IF  :key.scr_depr_sched in ('N','A') then
         	    IF  :key.scr_purch_price in ('N','A')
         	    AND :key.scr_fair_val in ('N','A') 
         	    AND :key.scr_asset_cat = 'A'
         	    AND :key.scr_herit_asset in ('N','A') then
         	        NULL; 
         	    ELSE
         	        goto nxt_rec;
         	    END IF;
         	ELSE
         	   goto nxt_rec;
         	END IF;
       ELSIF ws_found = 'Y' then	
              IF :key.scr_depr_sched = 'A' then
         	       null;
         	    ELSIF  :key.scr_depr_sched = 'N' then
         	          IF  (ws_dep_active_ind = 'N'
         	          AND  ws_dep_closed_ind = 'N') then
         	              NULL;
         	          ELSE
         	             goto nxt_rec;
         	          END IF;     
         	    ELSIF  :key.scr_depr_sched = 'I' 
    	          AND  ws_dep_active_ind = 'Y' then
        	           NULL; 
              ELSIF  :key.scr_depr_sched = 'C' 
     	          AND  ws_dep_closed_ind = 'Y' then
        	           NULL; 
         	    ELSE          	     	      	         	
         	       goto nxt_rec;
         	    END IF;
         ELSE	     
         	  goto nxt_rec;   
         END IF;
         
         message('inserted32');
        
       	              
/*       IF :key.scr_depr_sched = 'A' then
         	null;
       ELSIF   :key.scr_depr_sched = 'N' then
         	     IF  (ws_dep_active_ind = 'N'
         	         AND  ws_dep_closed_ind = 'N')
         	         OR   ws_found = 'N' then	
         	         NULL; 
         	     ELSE
         	     	  goto nxt_rec;
         	     END IF; 
        ELSIF  :key.scr_depr_sched = 'I' then
    	         IF ws_found = 'Y'
    	            AND ws_dep_active_ind = 'Y' then
        	        NULL; 
         	     ELSE 
         	     	  goto nxt_rec;
         	     END IF;
        ELSIF  :key.scr_depr_sched = 'C' then
     	         IF ws_found = 'Y'
    	            AND ws_dep_closed_ind = 'Y' then
        	        NULL; 
         	     ELSE          	     	      	         	
         	        goto nxt_rec;
         	     END IF;
         ELSE	     
         	  goto nxt_rec;   
         END IF; */
       
       IF :key.scr_rec_dte_fr is not null then
       	  IF (:key.scr_rec_dte_fr = ws_act_rv_dte
       	   	   OR :key.scr_rec_dte_fr < ws_act_rv_dte)
       	   	  AND ws_found = 'Y' then
       	   	  NULL;
          ELSE
       	   	  goto nxt_rec;
          END IF;
       ELSE
       	  null;
       END IF;   
       
       message('inserted123');
       
       IF :key.scr_rec_dte_to is not null then
          IF (:key.scr_rec_dte_to >= ws_act_rv_dte
       	   	  AND ws_found = 'Y') then
       	   	  NULL;
          ELSE
       	      goto nxt_rec;
          END IF;
       ELSE
       	  null;
       END IF;   
         message('inserted12');     
       
       IF ws_found = 'Y' then
          IF :key.scr_purch_price = 'A' then 
      	      null;
      	  ELSIF    	
       	     :key.scr_purch_price = 'Y' then
         	   IF ws_purch_price is not null then 
       	   	    NULL;
       	   	 ELSE
       	   	    goto nxt_rec;   
         	   END IF;
         	ELSIF  
         	   	:key.scr_purch_price = 'N' then
         	   	   IF ws_purch_price is null then 
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	  ELSE	     
       	  	  goto nxt_rec;   
       	  END IF;
       ELSIF ws_found = 'N' then
          	 IF :key.scr_purch_price in ('A','N') then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;	  
       ELSE
          goto nxt_rec;    	
       END IF;
       
       message('inserted1');
       
       IF 	ws_found = 'Y' then
           IF :key.scr_fair_val = 'A' then
       	      null;
       	   ELSIF   	
       	      :key.scr_fair_val = 'Y' then
       	   	  IF ws_fair_val is not null then 
       	   	     NULL;
       	   	  ELSE
       	   	     goto nxt_rec;   
         	    END IF;
         	  ELSIF  
         	   	:key.scr_fair_val = 'N' then 
         	   	   IF ws_fair_val is null then 
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	    ELSE	     
       	   	  goto nxt_rec;   
       	    END IF;
       ELSIF ws_found = 'N' then
          	 IF :key.scr_fair_val in ('A','N') then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;	   
       ELSE
          goto nxt_rec;	   	
       END IF;
     
     message('inserted');
         
       IF 	ws_found = 'Y' then
           IF :key.scr_asset_cat = 'A' then
       	      IF ws_ass_cat ='MJ' or
       	         ws_ass_cat = 'MN' then	
                 null;
       	      END IF;
       	   ELSIF   
       	      :key.scr_asset_cat = 'MJ' then
         	    IF ws_ass_cat ='MJ' then
       	   	     NULL;
       	   	  ELSE
       	   	     goto nxt_rec;   
         	    END IF;
         	 ELSIF  
         	   	:key.scr_asset_cat = 'MN' then 
         	   	   IF ws_ass_cat = 'MN' then
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	   ELSE	     
       	   	  goto nxt_rec;   
       	   END IF;
       ELSIF ws_found = 'N' then
          	 IF :key.scr_herit_asset = 'A' then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;
       ELSE
         goto nxt_rec;
       END IF;
       
       IF ws_found = 'Y' then
       	  IF :key.scr_herit_asset = 'A' then
       	     null;
       	  ELSIF   
       	    :key.scr_herit_asset = 'Y' then
         	  IF ws_heritage_ind = 'Y' then 
       	  	    NULL;
       	    ELSE
       	        goto nxt_rec;   
         	  END IF;
         	ELSIF  
         	   	:key.scr_herit_asset = 'N' then 
         	   	   IF ws_heritage_ind = 'N' then
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	   ELSE	     
       	   	  goto nxt_rec;   
         	 END IF;	
       ELSIF ws_found = 'N' then
          	 IF :key.scr_herit_asset in ('A','N') then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;	  
       ELSE
         goto nxt_rec;
       END IF;
       
       message('inserted');
       
       BEGIN
           	 insert into ils.lsst_prt_asset_fin
       	 	     (user_id,
       	 	      dte_tme_stamp,
       	 	      unit_cde,
       	 	      nsn,
       	 	      ser_no,
       	 	      unit_desc,
       	 	      nsn_desc,
       	 	      me_cde,
       	 	      me_desc,
       	 	      gsb_unit_cde,
       	 	      gsb_desc,
       	 	      itm_purch_prc,
       	 	      heritage_ind,
       	 	      res_val,
       	 	      fair_val,
       	 	      current_val,
       	 	      accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        dep_sched,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        close_reason)
       	 	   values 
       	  	    (:global.ils_force_no,
                :key.wrk_dte,
       	 	      ws_unit,
       	 	      wrk_nsn,
       	 	      ws_ser_no,
       	 	      ws_eng_abbr_nme,
       	 	      wrk_nsn_desc,
       	 	      ws_mec,
       	 	      ws_me_descr,
       	 	      wrk_gsb_unit,
       	 	      wrk_gsb_desc,
       	 	      ws_itm_purch_prc,
       	 	      ws_heritage_ind,
       	 	      ws_res_val,
       	 	      ws_fair_val,
       	 	      ws_current_val,
       	 	      ws_accum_improve_val,
       	        ws_accum_impair_val,
       	        ws_useful_lfe,
       	        ws_rem_useful_lfe,
       	        ws_ass_cat,
       	        ws_dep_sched, 
       	        ws_start_dep_dte,
       	        ws_lst_dep_fin_YYYYMM,
       	        ws_close_reason);
       EXCEPTION
       	  when dup_val_on_index then
       	  null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);pause;
--         raise form_trigger_failure; */
       END;
     --  end if;
     
     message('itcomes');
    	message('scin1 = '||rec01.scin);
      
<< nxt_rec >>
       null; 
ws_cnt:= ws_cnt + 1; 
    o_cnt := ws_cnt;           
      	 END LOOP;
      	/* message('itcomes2233');
      	   message('values = '||:global.ils_force_no);
     message('values = '||:key.wrk_dte);
     message('value122 = '||ws_unit);
     message('value123 = '|| wrk_nsn);*/
       END;
      /* message('itcomes112');
         message('values = '||:global.ils_force_no);
     message('values = '||:key.wrk_dte);
     message('value122 = '||ws_unit);
     message('value123 = '|| wrk_nsn);*/
		 END;
		-- message('itcomes22');
	END IF;
--	message('itcomes9999');
END;	
--message('itcome78855s');
END;

Open in new window

There is missing SELECT and FROM:

for rec1 in (SELECT scin,
                     itm_purch_prc,
                     heritage_ind,
                     res_val,
                     fair_val,
                     current_val,
                     accum_improve_val,
                     accum_impair_val,
                     useful_lfe,
                     rem_useful_lfe,
                     ass_cat,
                     act_rv_dte,
                     dep_active_ind,
                          dep_closed_ind,
                     start_dep_dte,
                     lst_dep_fin_YYYYMM,
                     close_reason
from  ils.lsst_asset_fin a)
                     --nvl(close_reason,'2222')
       --        from  ils.lsst_asset_fin a
       --        where  TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE)
      --  where  trim(ser_data.scin) = trim(a.scin);      
        loop
              ws_scin := rec1.scin;
               ws_itm_purch_prc := rec1.itm_purch_prc;
               ws_heritage_ind := rec1.res_val;
               ws_res_val := rec1.fair_val;
               ws_fair_val := rec1.current_val;
               ws_current_val := rec1.accum_improve_val;
               ws_accum_improve_val := rec1. accum_impair_val;
               ws_accum_impair_val := rec1. useful_lfe;
               ws_useful_lfe ;= rec1.ass_cat;
               ws_rem_useful_lfe := rec1.act_rv_dte;
               ws_ass_cat := rec1.ass_cat;
               ws_act_rv_dte := rec1.act_rv_dte;
                    ws_dep_active_ind := rec1.dep_active_ind;
                    ws_dep_closed_ind := rec1.dep_closed_ind;
               ws_start_dep_dte :=  rec1.start_dep_dte;
               ws_lst_dep_fin_YYYYMM := lst_dep_fin_YYYYMM;
               ws_close_reason := rec1.close_reason;
where do I close the loop am getting this error now
User generated imagethis si the latest code
PROCEDURE PROC_SELECT_DATA (o_cnt OUT NUMBER)IS
BEGIN
	DECLARE
		ws_unit_cde             number(06);
		wrk_gsb_unit            number(06);
		ws_unit                 number(06);
		ws_char_unit_cde        varchar2(06);
		ws_eng_abbr_nme         varchar2(30);
		wrk_gsb_desc            varchar2(30);
		ws_unit_line_ind        varchar2(01);
		ws_act_des              varchar2(01);
		ws_origin               varchar2(100);
		ws_ser_no               varchar2(30);
		ws_inst_ser_no          varchar2(30);
		ws_scin                 number(11);
		ws_sin                  number(11);
		ws_unit_holding         number(06);
		ws_dept_holding         number(06);
		ws_unit_located_at      number(06);
		ws_cond_cde             varchar2(01);
		ws_srv_status_cde       varchar2(3);
		ws_pers_located_at      varchar2(15);
		ws_itm_purch_prc        number(15,4);
		wrk_purch_prc           varchar2(3);
		ws_heritage_ind         varchar2(1);
    wrk_herit_asset         varchar2(3);		
		ws_res_val              number(15,4);
		ws_fair_val             number(15,4);
		wrk_fair_val            varchar2(3);
		ws_current_val          number(15,4);
		ws_accum_improve_val    number(15,4);
		ws_accum_impair_val     number(15,4);
		ws_useful_lfe           number(4);
		ws_rem_useful_lfe       number(4);
		ws_ass_cat              varchar2(2);
		wrk_ass_cat             varchar2(5);
		ws_dep_sched            varchar2(1);
		wrk_dep_sched           varchar2(15);
		ws_start_dep_dte        date;
		ws_close_reason         varchar2(20);
		ws_lst_dep_fin_YYYYMM   number(6);
		ws_prov_reg_no          varchar2(20);
		ws_nsn                  varchar2(16);
		ws_itm_type_cde         varchar2(01);
		ws_struc_lev_cde        varchar2(01);
		ws_agency_ser_cntrl_ind varchar2(01);
		ws_cntr_body_cde        varchar2(02);
		ws_org_func             varchar2(02);
		ws_nsn_descr            varchar2(200);
		ws_mec                  varchar2(04);
		ws_me_descr             varchar2(50);
		ws_sort_cc              number(02);
		ws_sort_srv             number(02);
		ws_err_ind              varchar2(01);
		ws_err_msg              varchar2(80);
		ws_ldesc                varchar2(50);
		ws_sdesc                varchar2(25);
		ws_short_nme            varchar2(30);
		ws_long_nme             varchar2(50);
		ws_stat_ind             varchar2(01);
		ws_oli_ind              varchar2(01);
		ws_pers_details         varchar2(100);
		ws_desc                 varchar2(150);
		ws_rank                 varchar2(15);
		ws_pers_found           varchar2(01);
		ws_un_no                varchar2(10);
		ws_dummy                varchar2(01);
		ws_m_nsn                varchar2(16);
		proc_cfg_tab_no         number;
		proc_mec                varchar2(04);
		proc_mec_nsn            varchar2(16);
		proc_cfg_tab_desc       varchar2(200);
		proc_mec_desc           varchar2(200);
		proc_mec_nsn_desc       varchar2(200);
		proc_exist_corp_ind     varchar2(01);
		proc_err_ind            varchar2(01);
		proc_err_msg            varchar2(80);
		proc_me_niin            varchar2(13);
		wrk_nsn_desc            varchar2(200);
		wrk_err                 varchar2(1);
		wrk_msg                 varchar2(50);
		wrk_nsn                 varchar2(16);
		v_nsn                   varchar2(16);
		wrk_totals              varchar2(3);
		wrk_all_units           varchar2(3);
		ws_found                varchar2(1);
		ws_dep_active_ind       varchar2(1);
		ws_dep_closed_ind       varchar2(1);
		ws_act_rv_dte           date;
		ws_purch_price          number(15,4);
		ws_cnt                  number := 0;
		
	BEGIN
    ws_unit_cde := 0;
    
		IF :key.wrk_org_func  = 'HQ' or
		  (:key.wrk_org_func  = 'CM' and
			 :key.scr_all_units = 'Y') THEN
			 ws_unit_cde     := 0;
		 	 ws_eng_abbr_nme := '';
		ELSE	  
		   ws_unit_cde     := :key.scr_req_unit;
	     ws_eng_abbr_nme := :key.scr_unit_desc;
		END IF;
		
	--	message('testt09');
	--	message(:key.scr_ser_no);
	message('cmd = '||:key.wrk_comnd_cde );
	message('unitcde = '||ws_unit_cde);
IF :key.scr_ser_no is not null then
     DECLARE
     	 CURSOR         sel_ser_no is
       select         s.sin,
                      s.ser_no, 
       	              e.scin,
       	              e.unit_holding,
       	              me_cat_cde||me_family_cde||me_var_cde mec,
       	              i.nsc||'-'||substr(i.niin,1,2)||'-'||
  	                  substr(i.niin,3,3)||'-'||substr(i.niin,6,4) nsn,
  	                  i.agency_ser_cntrl_ind,
       	              me_desc
       from           ils.me_master_rec          m,
       	              ils.sss_entry_index        s,
	         		        ils.sss_er_core            e,
	         		        ils.item_data              i
      where           s.ser_no			    = :key.scr_ser_no 
       and            e.scin 				    = s.scin
			 and            s.sin_change_reas is null
  	   and            s.sin_change_dte is null 
			 and           ((nvl(e.unit_holding,0) = nvl(ws_unit_cde,0) and
  	                  nvl(ws_unit_cde,0) > 0) or
  	                  nvl(ws_unit_cde,0) = 0) 
	     and            m.sin (+)            = s.sin
 	     and            i.sin             = s.sin
-- 	      or             s.sin            = :key.wrk_sin)
  	   and            (m.me_life_cyc (+) != '08' and
                      m.me_rec_stat (+) not in ('C','X'));
	   	 
     BEGIN
     	 FOR rec1 IN sel_ser_no
	     LOOP
      BEGIN
			     select itm_purch_prc,
       	 	        heritage_ind,
       	 	        res_val,
       	 	        fair_val,
       	 	        current_val,
       	 	        accum_improve_val,
       	 	        accum_impair_val,
       	 	        useful_lfe,
       	 	        rem_useful_lfe,
       	 	        ass_cat,
       	 	        act_rv_dte,
       	 	        dep_active_ind,
       	 	        dep_closed_ind,
       	 	        start_dep_dte,
       	 	        lst_dep_fin_YYYYMM,
       	 	        close_reason
			     into   
			            ws_itm_purch_prc,
       	 	        ws_heritage_ind,
       	 	        ws_res_val,
       	 	        ws_fair_val,
       	 	        ws_current_val,
       	 	        ws_accum_improve_val,
       	 	        ws_accum_impair_val,
       	 	        ws_useful_lfe,
       	 	        ws_rem_useful_lfe,
       	 	        ws_ass_cat,
       	 	        ws_act_rv_dte,
       	 	        ws_dep_active_ind,
       	 	        ws_dep_closed_ind,
       	 	        ws_start_dep_dte,
       	 	        ws_lst_dep_fin_YYYYMM,
       	 	        ws_close_reason
				    from   ils.lsst_asset_fin  a
  			   where  trim(a.scin)            = trim(rec1.scin)
  			  -- where scin = 102672956 
  			     /*and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
   																	      AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/  																	      
   					 /*and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd'))) 
             AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE))and  (:key.scr_rec_dte_fr is null or :key.scr_rec_dte_to is null);*/		
             AND  TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE);
            -- AND  TRUNC(a.act_rv_dte) BETWEEN NVL(:key.scr_rec_dte_fr,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(:key.scr_rec_dte_to,SYSDATE);
             
             										      
   	         																      
  			   ws_found := 'Y';
      /*EXCEPTION  
      	when others then
      	null;*/
      	exception
         when no_data_found THEN
			      ws_found := 'N';
-- 			      goto nxt_rec;
            null;
      END;
      
      ws_scin := rec1.scin; 
      ws_sin := rec1.sin;
   	  ws_unit_holding := nvl(rec1.unit_holding,0);
      ws_mec := rec1.mec;
      ws_me_descr := rec1.me_desc;
      ws_ser_no := rec1.ser_no;
      ws_inst_ser_no := '';       	   	  
	     	
      	IF :key.scr_req_unit is null or
       	   :key.wrk_org_func = 'HQ' then
       	   	  ws_unit := ws_unit_holding;
       	ELSE
       	 	 	  IF :key.scr_all_units = 'Y' then
       	 	 	     ws_unit := 0;
       	 	       BEGIN
                   select 'X' 
                     into ws_dummy
                     from dual
                    where ws_unit_holding in (select unit_cde
                                                from ils.unt
                                               where comnd_cde = :key.wrk_comnd_cde);
                   ws_unit := ws_unit_holding;
       	 	       exception
       	 	    	   when no_data_found THEN
       	 	    	        ws_unit := 0;
      	 	    	        goto nxt_rec;
       	 	       END;
       	 	 	  ELSE
       	 	 	  	ws_unit := ws_unit_cde;
       	      END IF;
	     END IF;
	     	IF :key.scr_all_units = 'Y' then
		        wrk_gsb_unit := :key.scr_req_unit;
		        wrk_gsb_desc := :key.scr_unit_name;
		    ELSE
		     BEGIN
		     	    select unit_cde,
			  	           eng_abbr_nme
				      into   wrk_gsb_unit,
			  	           wrk_gsb_desc
  			      from 	 ils.unt
				      where  comnd_cde = :key.wrk_comnd_cde 
				      and    org_func  = 'CM';
			      END;
     END IF;
     
        IF :key.scr_me_cde is not null then
      	 	 	  IF :key.scr_var_cde is not null then
              	 IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then 
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_fam_cde is not null then 
              	 IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_cat_cde is not null then
              	 IF :key.scr_cat_cde = substr(ws_mec,1,1) then
              	 	  null;
              	 ELSE
       	 	 	  	    goto nxt_rec;
         	 	 	   END IF;
              END IF;
           ELSE
      	 	    :key.scr_me_cde  := ws_mec;
      	 	    :key.scr_mec_desc     := ws_me_descr;    
       	 	 END IF;   
-- Unit Description
           IF nvl(ws_unit,0) > 0 then
       	 	    BEGIN
       	 	      select eng_abbr_nme
       	 	        into ws_eng_abbr_nme
       	 	        from ils.unt
       	 	       where unit_cde = ws_unit;
       	 	      exception
       	 	        when no_data_found then
       	 	             ws_eng_abbr_nme := ws_unit ||' Unit not Found';
       	      END;
 	 	       END IF;
 	 	       
 	 	      IF :key.wrk_full_nsn is not null then
       	   	  IF :key.wrk_full_nsn = ws_nsn then
       	   	  	 null;
       	   	  ELSE
      	   	  	 goto nxt_rec;
       	   	  END IF;
       	  END IF;     
 
      BEGIN
			     select ser_no
				   into   ws_ser_no
  			   from   ils.sss_entry_index
  			   where  scin            = rec1.scin
  			   and    SIN_CHANGE_DTE is null;
  		EXCEPTION
      	  when no_data_found then
      	  message('No serial number for this ICN');
      	  raise form_trigger_failure;
	    END; 
	     
	     wrk_nsn := substr(rec1.nsn,1,4)||substr(rec1.nsn,6,2)||
                  substr(rec1.nsn,9,3)||substr(rec1.nsn,13,4);
                  
       wrk_nsn_desc := null;
           message('values = '||:global.ils_force_no);
     message('values = '||:key.wrk_dte);
     message('value122 = '||ws_unit);
     message('value123 = '|| wrk_nsn);
       
      -- wrk_nsn   := nvl(wrk_nsn,'');
       message('error12');
     if :key.SCR_ICN_2ND_2 is not null then
       PROC_NSN_DESC(wrk_nsn,
    	                wrk_nsn_desc,
       	              wrk_err,
       	              wrk_msg); 
     end if;
     
     
     message('values = '||:global.ils_force_no);
     message('values = '||:key.wrk_dte);
     message('value122 = '||ws_unit);
     message('value123 = '|| wrk_nsn);
                /*,
       	 	      ws_unit,
       	 	      wrk_nsn,
       	 	      rec1.ser_no,
       	 	      ws_eng_abbr_nme,
       	 	      wrk_nsn_desc,
       	 	      ws_mec,
       	 	      ws_me_descr,
       	 	      wrk_gsb_unit,
       	 	      wrk_gsb_desc,
       	 	      ws_itm_purch_prc,
       	 	      ws_heritage_ind,
       	 	      ws_res_val,
       	 	      ws_fair_val,
       	 	      ws_current_val,
       	 	      ws_accum_improve_val,
       	        ws_accum_impair_val,
       	        ws_useful_lfe,
       	        ws_rem_useful_lfe,
       	        ws_ass_cat,
       	        ws_dep_sched, 
       	        ws_start_dep_dte,
       	        ws_lst_dep_fin_YYYYMM,
       	        ws_close_reason);*/
       	             
      
      BEGIN
           	 insert into ils.lsst_prt_asset_fin
       	 	     (user_id,
       	 	      dte_tme_stamp,
       	 	      unit_cde,
       	 	      nsn,
       	 	      ser_no,
       	 	      unit_desc,
       	 	      nsn_desc,
       	 	      me_cde,
       	 	      me_desc,
       	 	      gsb_unit_cde,
       	 	      gsb_desc,
       	 	      itm_purch_prc,
       	 	      heritage_ind,
       	 	      res_val,
       	 	      fair_val,
       	 	      current_val,
       	 	      accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        dep_sched,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        close_reason)
       	 	   values 
       	  	    (:global.ils_force_no,
                :key.wrk_dte,
       	 	      ws_unit,
       	 	      wrk_nsn,
       	 	      rec1.ser_no,
       	 	      ws_eng_abbr_nme,
       	 	      wrk_nsn_desc,
       	 	      ws_mec,
       	 	      ws_me_descr,
       	 	      wrk_gsb_unit,
       	 	      wrk_gsb_desc,
       	 	      ws_itm_purch_prc,
       	 	      ws_heritage_ind,
       	 	      ws_res_val,
       	 	      ws_fair_val,
       	 	      ws_current_val,
       	 	      ws_accum_improve_val,
       	        ws_accum_impair_val,
       	        ws_useful_lfe,
       	        ws_rem_useful_lfe,
       	        ws_ass_cat,
       	        ws_dep_sched, 
       	        ws_start_dep_dte,
       	        ws_lst_dep_fin_YYYYMM,
       	        ws_close_reason);
      EXCEPTION
       	  when dup_val_on_index then
       	  null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);
--         raise form_trigger_failure;       	        
      END;
     -- end if;
ws_cnt := ws_cnt + 1;
<< nxt_rec >>
     null;
     o_cnt := ws_cnt;          
--message('ws_cnt:'||ws_cnt);pause;
       END LOOP;
     END;	
ELSE
		BEGIN
      DECLARE
  	    cursor ser_data is
  	      select a.ser_no, 
  	             a.scin,
  	             a.sin,
  	             nvl(b.unit_holding,0) unit_holding,
  	             nvl(b.dept_holding,0) dept_holding,
  	             nvl(b.unit_located_at,0) unit_located_at,
  	             b.cond_cde,
  	             b.srv_status_cde,
  	             b.pers_located_at,
  	             b.nha_sin,
  	             b.nha_ser_no,
  	             c.nsc||'-'||substr(c.niin,1,2)||'-'||
  	             substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
  	             c.itm_type_cde,
  	             c.struc_lev_cde,
  	             c.agency_ser_cntrl_ind,
  	             d.itm_nme,
  	             e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
  	             e.me_desc  	             
  	        from ils.sss_entry_index a,
  	             ils.sss_er_core b,
  	             ils.item_data c,
  	             ils.names d,
  	             ils.me_master_rec e,
  	             ils.lsst_asset_fin fin
  	       where a.sin_change_reas is null
  	         and a.sin_change_dte is null
  	         and b.scin = a.scin
  	         and fin.scin = b.scin
  	         and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
  	               nvl(ws_unit_cde,0) > 0) or
  	               nvl(ws_unit_cde,0) = 0)
  	         and c.sin = a.sin
  	         and d.sin = c.sin
  	         --and b.scin = 20202029920
  	         and d.itm_nme_type = '1'
  	         and d.name_seq = '1'
  	         and e.sin (+) = a.sin
  	         and (e.me_life_cyc (+) != '08' and
                  e.me_rec_stat (+) not in ('C','X'))
           order by b.unit_holding, a.ser_no;

      BEGIN
      	
      	for rec1 in (select scin,
       	        itm_purch_prc,
       	        heritage_ind,
       	        res_val,
       	        fair_val,
       	        current_val,
       	        accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        act_rv_dte,
       	        dep_active_ind,
       	 	      dep_closed_ind,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        close_reason
       	        --nvl(close_reason,'2222')
        from  ils.lsst_asset_fin a
      	  where  TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE))
      --  where  trim(ser_data.scin) = trim(a.scin)       
        loop
        	ws_scin := rec1.scin;
       	  ws_itm_purch_prc := rec1.itm_purch_prc;
       	  ws_heritage_ind := rec1.res_val;
       	  ws_res_val := rec1.fair_val;
       	  ws_fair_val := rec1.current_val;
       	  ws_current_val := rec1.accum_improve_val;
       	  ws_accum_improve_val := rec1. accum_impair_val;
       	  ws_accum_impair_val := rec1. useful_lfe;
       	  ws_useful_lfe := rec1.ass_cat;
       	  ws_rem_useful_lfe := rec1.act_rv_dte;
       	  ws_ass_cat := rec1.ass_cat;
       	  ws_act_rv_dte := rec1.act_rv_dte;
     	 	  ws_dep_active_ind := rec1.dep_active_ind;
     	 	  ws_dep_closed_ind := rec1.dep_closed_ind;
       	  ws_start_dep_dte :=  rec1.start_dep_dte;
       	  ws_lst_dep_fin_YYYYMM := lst_dep_fin_YYYYMM;
       	  ws_close_reason := rec1.close_reason;
        	
      	
      /*	 For rec01 in ser_data LOOP
       	 BEGIN   
       	 Select scin,
       	        itm_purch_prc,
       	        heritage_ind,
       	        res_val,
       	        fair_val,
       	        current_val,
       	        accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        act_rv_dte,
       	        dep_active_ind,
       	 	      dep_closed_ind,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        nvl(close_reason,'2222')
       	  into  ws_scin,
       	        ws_itm_purch_prc,
       	        ws_heritage_ind,
       	        ws_res_val,
       	        ws_fair_val,
       	        ws_current_val,
       	        ws_accum_improve_val,
       	        ws_accum_impair_val,
       	        ws_useful_lfe,
       	        ws_rem_useful_lfe,
       	        ws_ass_cat,
       	        ws_act_rv_dte,
     	 	        ws_dep_active_ind,
     	 	        ws_dep_closed_ind,
       	        ws_start_dep_dte,
       	        ws_lst_dep_fin_YYYYMM,
       	        ws_close_reason
       	  from  ils.lsst_asset_fin a
        where  trim(rec01.scin) = trim(a.scin)
       --	where scin = 20202029920 
       	   /*and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
   																  	  AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/
   				  /*	and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd'))) 
             AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE));*/            
           --  AND  TRUNC(a.act_rv_dte) BETWEEN NVL(:key.scr_rec_dte_fr,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(:key.scr_rec_dte_to,SYSDATE);
         --     AND  TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE);
             
            /* and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd'))) 
             AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE))and  (:key.scr_rec_dte_fr is null or :key.scr_rec_dte_to is null);*/															  	    																  	  
   						
   						message('scin03212+++ = '||rec01.scin);
   						message('ws_close_reason = '||ws_close_reason);
   						message('ws_itm_purch_prc = '||ws_itm_purch_prc);
   																  	  
 			   ws_found := 'Y';
        /* EXCEPTION  
         	when others then
         	null;*/
        /*	exception
         when no_data_found then
            ws_found := 'N';
              goto nxt_rec;
             null;*/
       END;
--message('ws_scin from table: '||ws_scin);pause;           	 	
   	 	 ws_ser_no := '';
   	 	 ws_inst_ser_no := '';
       	   IF rec01.srv_status_cde = 'C02' then
       	   	  ws_ser_no := rec01.nha_ser_no;
       	   	  ws_inst_ser_no := rec01.ser_no;
       	   	  ws_pers_located_at := rec01.pers_located_at;
       	      BEGIN
       	   	    select a.scin,
       	   	           a.sin,
       	   	           b.srv_status_cde,
       	   	           b.unit_holding,
       	   	           nvl(b.dept_holding,0),
       	   	           nvl(b.unit_located_at,0),
       	   	           b.cond_cde,
       	   	           lpad(to_char(c.nsc),4,'0')||'-'||substr(c.niin,1,2)||'-'||
       	   	           substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
       	   	           c.itm_type_cde,
       	   	           c.struc_lev_cde,
       	   	           c.agency_ser_cntrl_ind,
       	   	           c.cntr_body_cde,
       	   	           e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
       	   	           e.me_desc
       	   	      into ws_scin, 
       	   	           ws_sin,
       	   	           ws_srv_status_cde,
       	   	           ws_unit_holding,
       	   	           ws_dept_holding,
       	   	           ws_unit_located_at,
       	   	           ws_cond_cde,
       	   	           ws_nsn,
       	   	           ws_itm_type_cde,
       	   	           ws_struc_lev_cde,
       	   	           ws_agency_ser_cntrl_ind,
       	   	           ws_cntr_body_cde,
       	   	           ws_mec,
       	   	           ws_me_descr
       	   	      from ils.sss_entry_index a,
       	   	           ils.sss_er_core b,
       	   	           ils.item_data c,
       	   	           ils.names d,
       	   	           ils.me_master_rec e
       	   	     where a.ser_no = rec01.nha_ser_no
       	   	       and a.sin = rec01.nha_sin
       	   	       and a.sin_change_reas is null
       	   	       and a.sin_change_dte is null
       	   	       and b.scin = a.scin
       	   	       and c.sin = a.sin
       	   	       and d.sin = c.sin
       	   	       and d.itm_nme_type = '1'
       	   	       and d.name_seq = '1'
       	   	       and e.sin (+) = a.sin
       	   	       and (e.me_life_cyc (+) != '08' and
       	   	            e.me_rec_stat (+) not in ('C','X'));
      	      exception
       	      	  when no_data_found then
       	      	       ws_ser_no := 'INVALID - '||ws_ser_no;
       	      	       ws_srv_status_cde := '';
       	      END;
       	   ELSE
       	   	  ws_scin := rec01.scin; 
      	   	  ws_sin := rec01.sin;
   	          ws_srv_status_cde := rec01.srv_status_cde;
       	   	  ws_unit_holding := nvl(rec01.unit_holding,0);
       	   	  ws_dept_holding := nvl(rec01.dept_holding,0);
       	   	  ws_unit_located_at := nvl(rec01.unit_located_at,0);
       	   	  ws_cond_cde := rec01.cond_cde;
       	   	  ws_pers_located_at := rec01.pers_located_at;
       	   	  ws_nsn := rec01.nsn;
       	   	  ws_itm_type_cde := rec01.itm_type_cde;
       	   	  ws_struc_lev_cde := rec01.struc_lev_cde;
       	   	  ws_agency_ser_cntrl_ind := rec01.agency_ser_cntrl_ind;
              ws_mec := rec01.mec;
              ws_me_descr := rec01.me_desc;
      	   	  ws_ser_no := rec01.ser_no;
       	   	  ws_inst_ser_no := '';
       	   END IF;
-- Set up values for print table       	                     	                      
       	   IF :key.scr_req_unit is null or
       	   	  :key.wrk_org_func = 'HQ' then
       	   	  ws_unit := ws_unit_holding;
      	   ELSE
       	 	 	  IF :key.scr_all_units = 'Y' then
       	 	 	     ws_unit := 0;
       	 	       BEGIN
                   select 'X' 
                     into ws_dummy
                     from dual
                    where ws_unit_holding in (select unit_cde
                                                from ils.unt
                                               where comnd_cde = :key.wrk_comnd_cde);
                   ws_unit := ws_unit_holding;
                 exception
       	 	    	   when no_data_found then
       	 	    	        ws_unit := 0;
       	 	    	        goto nxt_rec;
       	 	       END;
       	 	 	  ELSE
       	 	 	  	ws_unit := ws_unit_cde;
      	      END IF;
       	   END IF;
       	
       	IF :key.scr_all_units = 'Y' then
		        wrk_gsb_unit := :key.scr_req_unit;
		        wrk_gsb_desc := :key.scr_unit_name;
		     ELSE
		     BEGIN
		     	    select unit_cde,
			  	           eng_abbr_nme
				      into   wrk_gsb_unit,
			  	           wrk_gsb_desc
  			      from 	 ils.unt
				      where  comnd_cde = :key.wrk_comnd_cde 
				      and    org_func  = 'CM';
			      END;
      END IF;
       	   
       	  IF :key.wrk_full_nsn is not null then
       	   	  IF :key.wrk_full_nsn = ws_nsn then
       	   	  	 null;
       	   	  ELSE
       	   	  	 goto nxt_rec;
       	   	  END IF;
       	  END IF; 

       	  IF :key.scr_me_cde is not null then
      	 	 	  IF :key.scr_var_cde is not null then
              	 IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then 
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_fam_cde is not null then 
              	 IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_cat_cde is not null then
              	 IF :key.scr_cat_cde = substr(ws_mec,1,1) then
              	 	  null;
              	 ELSE
       	 	 	  	    goto nxt_rec;
         	 	 	   END IF;
              END IF;
      	 	 ELSE
      	 	    :key.scr_me_cde  := ws_mec;
      	 	    :key.scr_mec_desc     := ws_me_descr;   	
       	 	 END IF;
  
-- Unit Description
           IF nvl(ws_unit,0) > 0 then
       	 	    BEGIN
       	 	      select eng_abbr_nme
       	 	        into ws_eng_abbr_nme
       	 	        from ils.unt
       	 	       where unit_cde = ws_unit;
       	 	      exception
       	 	        when no_data_found then
       	 	             ws_eng_abbr_nme := ws_unit ||' Unit not Found';
       	      END;
 	 	       END IF;

--       NSN Description
        wrk_nsn := substr(ws_nsn,1,4)||substr(ws_nsn,6,2)||
                   substr(ws_nsn,9,3)||substr(ws_nsn,13,4);
        wrk_nsn_desc := null;
        
      

/*message('wrk_nsn = '||wrk_nsn);
message('wrk_nsn_desc = '||wrk_nsn_desc);
message('wrk_err = '||wrk_err);
message('wrk_msg = '||wrk_msg);*/
--message('error12344');
-- wrk_nsn  := nvl( wrk_nsn ,'');-message('error12');
if :key.SCR_ICN_2ND_2 is not  null then 
--	message('insideif');
        PROC_NSN_DESC(wrk_nsn,
    	                wrk_nsn_desc,
       	              wrk_err,
       	              wrk_msg);
  --  end if;

--message('afterif');
/** New additions/changes to Spec: 22/11/2010 - Marius**/
       elsif ws_found = 'N' then
          IF  :key.scr_depr_sched in ('N','A') then
         	    IF  :key.scr_purch_price in ('N','A')
         	    AND :key.scr_fair_val in ('N','A') 
         	    AND :key.scr_asset_cat = 'A'
         	    AND :key.scr_herit_asset in ('N','A') then
         	        NULL; 
         	    ELSE
         	        goto nxt_rec;
         	    END IF;
         	ELSE
         	   goto nxt_rec;
         	END IF;
       ELSIF ws_found = 'Y' then	
              IF :key.scr_depr_sched = 'A' then
         	       null;
         	    ELSIF  :key.scr_depr_sched = 'N' then
         	          IF  (ws_dep_active_ind = 'N'
         	          AND  ws_dep_closed_ind = 'N') then
         	              NULL;
         	          ELSE
         	             goto nxt_rec;
         	          END IF;     
         	    ELSIF  :key.scr_depr_sched = 'I' 
    	          AND  ws_dep_active_ind = 'Y' then
        	           NULL; 
              ELSIF  :key.scr_depr_sched = 'C' 
     	          AND  ws_dep_closed_ind = 'Y' then
        	           NULL; 
         	    ELSE          	     	      	         	
         	       goto nxt_rec;
         	    END IF;
         ELSE	     
         	  goto nxt_rec;   
         END IF;
         
         message('inserted32');
        
       	              
/*       IF :key.scr_depr_sched = 'A' then
         	null;
       ELSIF   :key.scr_depr_sched = 'N' then
         	     IF  (ws_dep_active_ind = 'N'
         	         AND  ws_dep_closed_ind = 'N')
         	         OR   ws_found = 'N' then	
         	         NULL; 
         	     ELSE
         	     	  goto nxt_rec;
         	     END IF; 
        ELSIF  :key.scr_depr_sched = 'I' then
    	         IF ws_found = 'Y'
    	            AND ws_dep_active_ind = 'Y' then
        	        NULL; 
         	     ELSE 
         	     	  goto nxt_rec;
         	     END IF;
        ELSIF  :key.scr_depr_sched = 'C' then
     	         IF ws_found = 'Y'
    	            AND ws_dep_closed_ind = 'Y' then
        	        NULL; 
         	     ELSE          	     	      	         	
         	        goto nxt_rec;
         	     END IF;
         ELSE	     
         	  goto nxt_rec;   
         END IF; */
       
       IF :key.scr_rec_dte_fr is not null then
       	  IF (:key.scr_rec_dte_fr = ws_act_rv_dte
       	   	   OR :key.scr_rec_dte_fr < ws_act_rv_dte)
       	   	  AND ws_found = 'Y' then
       	   	  NULL;
          ELSE
       	   	  goto nxt_rec;
          END IF;
       ELSE
       	  null;
       END IF;   
       
       message('inserted123');
       
       IF :key.scr_rec_dte_to is not null then
          IF (:key.scr_rec_dte_to >= ws_act_rv_dte
       	   	  AND ws_found = 'Y') then
       	   	  NULL;
          ELSE
       	      goto nxt_rec;
          END IF;
       ELSE
       	  null;
       END IF;   
         message('inserted12');     
       
       IF ws_found = 'Y' then
          IF :key.scr_purch_price = 'A' then 
      	      null;
      	  ELSIF    	
       	     :key.scr_purch_price = 'Y' then
         	   IF ws_purch_price is not null then 
       	   	    NULL;
       	   	 ELSE
       	   	    goto nxt_rec;   
         	   END IF;
         	ELSIF  
         	   	:key.scr_purch_price = 'N' then
         	   	   IF ws_purch_price is null then 
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	  ELSE	     
       	  	  goto nxt_rec;   
       	  END IF;
       ELSIF ws_found = 'N' then
          	 IF :key.scr_purch_price in ('A','N') then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;	  
       ELSE
          goto nxt_rec;    	
       END IF;
       
       message('inserted1');
       
       IF 	ws_found = 'Y' then
           IF :key.scr_fair_val = 'A' then
       	      null;
       	   ELSIF   	
       	      :key.scr_fair_val = 'Y' then
       	   	  IF ws_fair_val is not null then 
       	   	     NULL;
       	   	  ELSE
       	   	     goto nxt_rec;   
         	    END IF;
         	  ELSIF  
         	   	:key.scr_fair_val = 'N' then 
         	   	   IF ws_fair_val is null then 
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	    ELSE	     
       	   	  goto nxt_rec;   
       	    END IF;
       ELSIF ws_found = 'N' then
          	 IF :key.scr_fair_val in ('A','N') then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;	   
       ELSE
          goto nxt_rec;	   	
       END IF;
     
     message('inserted');
         
       IF 	ws_found = 'Y' then
           IF :key.scr_asset_cat = 'A' then
       	      IF ws_ass_cat ='MJ' or
       	         ws_ass_cat = 'MN' then	
                 null;
       	      END IF;
       	   ELSIF   
       	      :key.scr_asset_cat = 'MJ' then
         	    IF ws_ass_cat ='MJ' then
       	   	     NULL;
       	   	  ELSE
       	   	     goto nxt_rec;   
         	    END IF;
         	 ELSIF  
         	   	:key.scr_asset_cat = 'MN' then 
         	   	   IF ws_ass_cat = 'MN' then
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	   ELSE	     
       	   	  goto nxt_rec;   
       	   END IF;
       ELSIF ws_found = 'N' then
          	 IF :key.scr_herit_asset = 'A' then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;
       ELSE
         goto nxt_rec;
       END IF;
       
       IF ws_found = 'Y' then
       	  IF :key.scr_herit_asset = 'A' then
       	     null;
       	  ELSIF   
       	    :key.scr_herit_asset = 'Y' then
         	  IF ws_heritage_ind = 'Y' then 
       	  	    NULL;
       	    ELSE
       	        goto nxt_rec;   
         	  END IF;
         	ELSIF  
         	   	:key.scr_herit_asset = 'N' then 
         	   	   IF ws_heritage_ind = 'N' then
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	   ELSE	     
       	   	  goto nxt_rec;   
         	 END IF;	
       ELSIF ws_found = 'N' then
          	 IF :key.scr_herit_asset in ('A','N') then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;	  
       ELSE
         goto nxt_rec;
       END IF;
       
       message('inserted');
       
       BEGIN
           	 insert into ils.lsst_prt_asset_fin
       	 	     (user_id,
       	 	      dte_tme_stamp,
       	 	      unit_cde,
       	 	      nsn,
       	 	      ser_no,
       	 	      unit_desc,
       	 	      nsn_desc,
       	 	      me_cde,
       	 	      me_desc,
       	 	      gsb_unit_cde,
       	 	      gsb_desc,
       	 	      itm_purch_prc,
       	 	      heritage_ind,
       	 	      res_val,
       	 	      fair_val,
       	 	      current_val,
       	 	      accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        dep_sched,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        close_reason)
       	 	   values 
       	  	    (:global.ils_force_no,
                :key.wrk_dte,
       	 	      ws_unit,
       	 	      wrk_nsn,
       	 	      ws_ser_no,
       	 	      ws_eng_abbr_nme,
       	 	      wrk_nsn_desc,
       	 	      ws_mec,
       	 	      ws_me_descr,
       	 	      wrk_gsb_unit,
       	 	      wrk_gsb_desc,
       	 	      ws_itm_purch_prc,
       	 	      ws_heritage_ind,
       	 	      ws_res_val,
       	 	      ws_fair_val,
       	 	      ws_current_val,
       	 	      ws_accum_improve_val,
       	        ws_accum_impair_val,
       	        ws_useful_lfe,
       	        ws_rem_useful_lfe,
       	        ws_ass_cat,
       	        ws_dep_sched, 
       	        ws_start_dep_dte,
       	        ws_lst_dep_fin_YYYYMM,
       	        ws_close_reason);
       EXCEPTION
       	  when dup_val_on_index then
       	  null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);pause;
--         raise form_trigger_failure; */
       END;
     --  end if;
     
     message('itcomes');
    	message('scin1 = '||rec01.scin);
      
<< nxt_rec >>
       null; 
ws_cnt:= ws_cnt + 1; 
    o_cnt := ws_cnt;           
      	 END LOOP;
      	/* message('itcomes2233');
      	   message('values = '||:global.ils_force_no);
     message('values = '||:key.wrk_dte);
     message('value122 = '||ws_unit);
     message('value123 = '|| wrk_nsn);*/
       END;
      /* message('itcomes112');
         message('values = '||:global.ils_force_no);
     message('values = '||:key.wrk_dte);
     message('value122 = '||ws_unit);
     message('value123 = '|| wrk_nsn);*/
		 END;
		-- message('itcomes22');
	END IF;
--	message('itcomes9999');
END;	
--message('itcome78855s');
END;

Open in new window

The code in the image that shows the error doesn't look like the code you posted in the code window.

There is no select and from in the code in the image.
There is rec01, but it is uncommented. I would recommend remove all uncommented parts.
there is select
User generated imagecheck attachment this is the latest code
PROCEDURE PROC_SELECT_DATA (o_cnt OUT NUMBER)IS
BEGIN
	DECLARE
		ws_unit_cde             number(06);
		wrk_gsb_unit            number(06);
		ws_unit                 number(06);
		ws_char_unit_cde        varchar2(06);
		ws_eng_abbr_nme         varchar2(30);
		wrk_gsb_desc            varchar2(30);
		ws_unit_line_ind        varchar2(01);
		ws_act_des              varchar2(01);
		ws_origin               varchar2(100);
		ws_ser_no               varchar2(30);
		ws_inst_ser_no          varchar2(30);
		ws_scin                 number(11);
		ws_sin                  number(11);
		ws_unit_holding         number(06);
		ws_dept_holding         number(06);
		ws_unit_located_at      number(06);
		ws_cond_cde             varchar2(01);
		ws_srv_status_cde       varchar2(3);
		ws_pers_located_at      varchar2(15);
		ws_itm_purch_prc        number(15,4);
		wrk_purch_prc           varchar2(3);
		ws_heritage_ind         varchar2(1);
    wrk_herit_asset         varchar2(3);		
		ws_res_val              number(15,4);
		ws_fair_val             number(15,4);
		wrk_fair_val            varchar2(3);
		ws_current_val          number(15,4);
		ws_accum_improve_val    number(15,4);
		ws_accum_impair_val     number(15,4);
		ws_useful_lfe           number(4);
		ws_rem_useful_lfe       number(4);
		ws_ass_cat              varchar2(2);
		wrk_ass_cat             varchar2(5);
		ws_dep_sched            varchar2(1);
		wrk_dep_sched           varchar2(15);
		ws_start_dep_dte        date;
		ws_close_reason         varchar2(20);
		ws_lst_dep_fin_YYYYMM   number(6);
		ws_prov_reg_no          varchar2(20);
		ws_nsn                  varchar2(16);
		ws_itm_type_cde         varchar2(01);
		ws_struc_lev_cde        varchar2(01);
		ws_agency_ser_cntrl_ind varchar2(01);
		ws_cntr_body_cde        varchar2(02);
		ws_org_func             varchar2(02);
		ws_nsn_descr            varchar2(200);
		ws_mec                  varchar2(04);
		ws_me_descr             varchar2(50);
		ws_sort_cc              number(02);
		ws_sort_srv             number(02);
		ws_err_ind              varchar2(01);
		ws_err_msg              varchar2(80);
		ws_ldesc                varchar2(50);
		ws_sdesc                varchar2(25);
		ws_short_nme            varchar2(30);
		ws_long_nme             varchar2(50);
		ws_stat_ind             varchar2(01);
		ws_oli_ind              varchar2(01);
		ws_pers_details         varchar2(100);
		ws_desc                 varchar2(150);
		ws_rank                 varchar2(15);
		ws_pers_found           varchar2(01);
		ws_un_no                varchar2(10);
		ws_dummy                varchar2(01);
		ws_m_nsn                varchar2(16);
		proc_cfg_tab_no         number;
		proc_mec                varchar2(04);
		proc_mec_nsn            varchar2(16);
		proc_cfg_tab_desc       varchar2(200);
		proc_mec_desc           varchar2(200);
		proc_mec_nsn_desc       varchar2(200);
		proc_exist_corp_ind     varchar2(01);
		proc_err_ind            varchar2(01);
		proc_err_msg            varchar2(80);
		proc_me_niin            varchar2(13);
		wrk_nsn_desc            varchar2(200);
		wrk_err                 varchar2(1);
		wrk_msg                 varchar2(50);
		wrk_nsn                 varchar2(16);
		v_nsn                   varchar2(16);
		wrk_totals              varchar2(3);
		wrk_all_units           varchar2(3);
		ws_found                varchar2(1);
		ws_dep_active_ind       varchar2(1);
		ws_dep_closed_ind       varchar2(1);
		ws_act_rv_dte           date;
		ws_purch_price          number(15,4);
		ws_cnt                  number := 0;
		
	BEGIN
    ws_unit_cde := 0;
    
		IF :key.wrk_org_func  = 'HQ' or
		  (:key.wrk_org_func  = 'CM' and
			 :key.scr_all_units = 'Y') THEN
			 ws_unit_cde     := 0;
		 	 ws_eng_abbr_nme := '';
		ELSE	  
		   ws_unit_cde     := :key.scr_req_unit;
	     ws_eng_abbr_nme := :key.scr_unit_desc;
		END IF;
		
	--	message('testt09');
	--	message(:key.scr_ser_no);
	
IF :key.scr_ser_no is not null then
     DECLARE
     	 CURSOR         sel_ser_no is
       select         s.sin,
                      s.ser_no, 
       	              e.scin,
       	              e.unit_holding,
       	              me_cat_cde||me_family_cde||me_var_cde mec,
       	              i.nsc||'-'||substr(i.niin,1,2)||'-'||
  	                  substr(i.niin,3,3)||'-'||substr(i.niin,6,4) nsn,
  	                  i.agency_ser_cntrl_ind,
       	              me_desc
       from           ils.me_master_rec          m,
       	              ils.sss_entry_index        s,
	         		        ils.sss_er_core            e,
	         		        ils.item_data              i
      where           s.ser_no			    = :key.scr_ser_no
       and            e.scin 				    = s.scin
			 and            s.sin_change_reas is null
  	   and            s.sin_change_dte is null 
			 and           ((nvl(e.unit_holding,0) = nvl(ws_unit_cde,0) and
  	                  nvl(ws_unit_cde,0) > 0) or
  	                  nvl(ws_unit_cde,0) = 0) 
	     and            m.sin (+)            = s.sin
 	     and            i.sin             = s.sin
-- 	      or             s.sin            = :key.wrk_sin)
  	   and            (m.me_life_cyc (+) != '08' and
                      m.me_rec_stat (+) not in ('C','X'));
	   	 
     BEGIN
     	 FOR rec1 IN sel_ser_no
	     LOOP
      BEGIN
			     select itm_purch_prc,
       	 	        heritage_ind,
       	 	        res_val,
       	 	        fair_val,
       	 	        current_val,
       	 	        accum_improve_val,
       	 	        accum_impair_val,
       	 	        useful_lfe,
       	 	        rem_useful_lfe,
       	 	        ass_cat,
       	 	        act_rv_dte,
       	 	        dep_active_ind,
       	 	        dep_closed_ind,
       	 	        start_dep_dte,
       	 	        lst_dep_fin_YYYYMM,
       	 	        close_reason
			     into   
			            ws_itm_purch_prc,
       	 	        ws_heritage_ind,
       	 	        ws_res_val,
       	 	        ws_fair_val,
       	 	        ws_current_val,
       	 	        ws_accum_improve_val,
       	 	        ws_accum_impair_val,
       	 	        ws_useful_lfe,
       	 	        ws_rem_useful_lfe,
       	 	        ws_ass_cat,
       	 	        ws_act_rv_dte,
       	 	        ws_dep_active_ind,
       	 	        ws_dep_closed_ind,
       	 	        ws_start_dep_dte,
       	 	        ws_lst_dep_fin_YYYYMM,
       	 	        ws_close_reason
				    from   ils.lsst_asset_fin  a
  			   where  scin            = rec1.scin
  			     /*and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
   																	      AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/
   																	      
   					 and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd'))) 
             AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE));												      
   	         																      
  			   ws_found := 'Y';
      EXCEPTION                         
         when no_data_found THEN
			      ws_found := 'N';
-- 			      goto nxt_rec;
            null;
      END;
      
      ws_scin := rec1.scin; 
      ws_sin := rec1.sin;
   	  ws_unit_holding := nvl(rec1.unit_holding,0);
      ws_mec := rec1.mec;
      ws_me_descr := rec1.me_desc;
      ws_ser_no := rec1.ser_no;
      ws_inst_ser_no := '';       	   	  
	     	
      	IF :key.scr_req_unit is null or
       	   :key.wrk_org_func = 'HQ' then
       	   	  ws_unit := ws_unit_holding;
       	ELSE
       	 	 	  IF :key.scr_all_units = 'Y' then
       	 	 	     ws_unit := 0;
       	 	       BEGIN
                   select 'X' 
                     into ws_dummy
                     from dual
                    where ws_unit_holding in (select unit_cde
                                                from ils.unt
                                               where comnd_cde = :key.wrk_comnd_cde);
                   ws_unit := ws_unit_holding;
       	 	       exception
       	 	    	   when no_data_found THEN
       	 	    	        ws_unit := 0;
      	 	    	        goto nxt_rec;
       	 	       END;
       	 	 	  ELSE
       	 	 	  	ws_unit := ws_unit_cde;
       	      END IF;
	     END IF;
	     	IF :key.scr_all_units = 'Y' then
		        wrk_gsb_unit := :key.scr_req_unit;
		        wrk_gsb_desc := :key.scr_unit_name;
		    ELSE
		     BEGIN
		     	    select unit_cde,
			  	           eng_abbr_nme
				      into   wrk_gsb_unit,
			  	           wrk_gsb_desc
  			      from 	 ils.unt
				      where  comnd_cde = :key.wrk_comnd_cde 
				      and    org_func  = 'CM';
			      END;
     END IF;
     
        IF :key.scr_me_cde is not null then
      	 	 	  IF :key.scr_var_cde is not null then
              	 IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then 
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_fam_cde is not null then 
              	 IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_cat_cde is not null then
              	 IF :key.scr_cat_cde = substr(ws_mec,1,1) then
              	 	  null;
              	 ELSE
       	 	 	  	    goto nxt_rec;
         	 	 	   END IF;
              END IF;
           ELSE
      	 	    :key.scr_me_cde  := ws_mec;
      	 	    :key.scr_mec_desc     := ws_me_descr;    
       	 	 END IF;   
-- Unit Description
           IF nvl(ws_unit,0) > 0 then
       	 	    BEGIN
       	 	      select eng_abbr_nme
       	 	        into ws_eng_abbr_nme
       	 	        from ils.unt
       	 	       where unit_cde = ws_unit;
       	 	      exception
       	 	        when no_data_found then
       	 	             ws_eng_abbr_nme := ws_unit ||' Unit not Found';
       	      END;
 	 	       END IF;
 	 	       
 	 	      IF :key.wrk_full_nsn is not null then
       	   	  IF :key.wrk_full_nsn = ws_nsn then
       	   	  	 null;
       	   	  ELSE
      	   	  	 goto nxt_rec;
       	   	  END IF;
       	  END IF;     
 
      BEGIN
			     select ser_no
				   into   ws_ser_no
  			   from   ils.sss_entry_index
  			   where  scin            = rec1.scin
  			   and    SIN_CHANGE_DTE is null;
  		EXCEPTION
      	  when no_data_found then
      	  message('No serial number for this ICN');
      	  raise form_trigger_failure;
	    END; 
	     
	     wrk_nsn := substr(rec1.nsn,1,4)||substr(rec1.nsn,6,2)||
                  substr(rec1.nsn,9,3)||substr(rec1.nsn,13,4);
                  
       wrk_nsn_desc := null;
       
      -- wrk_nsn   := nvl(wrk_nsn,'');
     --  message('error12');
     if wrk_nsn is not null then
       PROC_NSN_DESC(wrk_nsn,
    	                wrk_nsn_desc,
       	              wrk_err,
       	              wrk_msg); 
       	             
      
      BEGIN
           	 insert into ils.lsst_prt_asset_fin
       	 	     (user_id,
       	 	      dte_tme_stamp,
       	 	      unit_cde,
       	 	      nsn,
       	 	      ser_no,
       	 	      unit_desc,
       	 	      nsn_desc,
       	 	      me_cde,
       	 	      me_desc,
       	 	      gsb_unit_cde,
       	 	      gsb_desc,
       	 	      itm_purch_prc,
       	 	      heritage_ind,
       	 	      res_val,
       	 	      fair_val,
       	 	      current_val,
       	 	      accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        dep_sched,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        close_reason)
       	 	   values 
       	  	    (:global.ils_force_no,
                :key.wrk_dte,
       	 	      ws_unit,
       	 	      wrk_nsn,
       	 	      rec1.ser_no,
       	 	      ws_eng_abbr_nme,
       	 	      wrk_nsn_desc,
       	 	      ws_mec,
       	 	      ws_me_descr,
       	 	      wrk_gsb_unit,
       	 	      wrk_gsb_desc,
       	 	      ws_itm_purch_prc,
       	 	      ws_heritage_ind,
       	 	      ws_res_val,
       	 	      ws_fair_val,
       	 	      ws_current_val,
       	 	      ws_accum_improve_val,
       	        ws_accum_impair_val,
       	        ws_useful_lfe,
       	        ws_rem_useful_lfe,
       	        ws_ass_cat,
       	        ws_dep_sched, 
       	        ws_start_dep_dte,
       	        ws_lst_dep_fin_YYYYMM,
       	        ws_close_reason);
      EXCEPTION
       	  when dup_val_on_index then
       	  null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);
--         raise form_trigger_failure;       	        
      END;
      end if;
ws_cnt := ws_cnt + 1;
<< nxt_rec >>
     null;
     o_cnt := ws_cnt;          
--message('ws_cnt:'||ws_cnt);pause;
       END LOOP;
     END;	
ELSE
		BEGIN
      DECLARE
  	    cursor ser_data is
  	      select a.ser_no, 
  	             a.scin,
  	             a.sin,
  	             nvl(b.unit_holding,0) unit_holding,
  	             nvl(b.dept_holding,0) dept_holding,
  	             nvl(b.unit_located_at,0) unit_located_at,
  	             b.cond_cde,
  	             b.srv_status_cde,
  	             b.pers_located_at,
  	             b.nha_sin,
  	             b.nha_ser_no,
  	             c.nsc||'-'||substr(c.niin,1,2)||'-'||
  	             substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
  	             c.itm_type_cde,
  	             c.struc_lev_cde,
  	             c.agency_ser_cntrl_ind,
  	             d.itm_nme,
  	             e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
  	             e.me_desc  	             
  	        from ils.sss_entry_index a,
  	             ils.sss_er_core b,
  	             ils.item_data c,
  	             ils.names d,
  	             ils.me_master_rec e
  	       where a.sin_change_reas is null
  	         and a.sin_change_dte is null
  	         and b.scin = a.scin
  	         and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
  	               nvl(ws_unit_cde,0) > 0) or
  	               nvl(ws_unit_cde,0) = 0)
  	         and c.sin = a.sin
  	         and d.sin = c.sin
  	         and d.itm_nme_type = '1'
  	         and d.name_seq = '1'
  	         and e.sin (+) = a.sin
  	         and (e.me_life_cyc (+) != '08' and
                  e.me_rec_stat (+) not in ('C','X'))
           order by b.unit_holding, a.ser_no;

      BEGIN
      		for rec1 in (select scin,
       	        itm_purch_prc,
       	        heritage_ind,
       	        res_val,
       	        fair_val,
       	        current_val,
       	        accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        act_rv_dte,
       	        dep_active_ind,
       	 	      dep_closed_ind,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        close_reason
       	        --nvl(close_reason,'2222')
        from  ils.lsst_asset_fin a
      	  where  TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE))
      	 /* and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd'))) 
             AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE));				*/											  	  
   		
      --  where  trim(ser_data.scin) = trim(a.scin)       
        loop
        	ws_scin := rec1.scin;
       	  ws_itm_purch_prc := rec1.itm_purch_prc;
       	  ws_heritage_ind := rec1.res_val;
       	  ws_res_val := rec1.fair_val;
       	  ws_fair_val := rec1.current_val;
       	  ws_current_val := rec1.accum_improve_val;
       	  ws_accum_improve_val := rec1. accum_impair_val;
       	  ws_accum_impair_val := rec1. useful_lfe;
       	  ws_useful_lfe := rec1.ass_cat;
       	  ws_rem_useful_lfe := rec1.act_rv_dte;
       	  ws_ass_cat := rec1.ass_cat;
       	  ws_act_rv_dte := rec1.act_rv_dte;
     	 	  ws_dep_active_ind := rec1.dep_active_ind;
     	 	  ws_dep_closed_ind := rec1.dep_closed_ind;
       	  ws_start_dep_dte :=  rec1.start_dep_dte;
       	  ws_lst_dep_fin_YYYYMM := lst_dep_fin_YYYYMM;
       	  ws_close_reason := rec1.close_reason;
      	
     														  	  
 			   ws_found := 'Y';
       /*EXCEPTION                         
         when no_data_found then
            ws_found := 'N';
--              goto nxt_rec;
              null;*/
       END;
--message('ws_scin from table: '||ws_scin);pause;           	 	
   	 	 ws_ser_no := '';
   	 	 ws_inst_ser_no := '';
       	   IF rec1.srv_status_cde = 'C02' then
       	   	  ws_ser_no := rec1.nha_ser_no;
       	   	  ws_inst_ser_no := rec1.ser_no;
       	   	  ws_pers_located_at := rec1.pers_located_at;
       	      BEGIN
       	   	    select a.scin, a.sin,
       	   	           b.srv_status_cde,
       	   	           b.unit_holding,
       	   	           nvl(b.dept_holding,0),
       	   	           nvl(b.unit_located_at,0),
       	   	           b.cond_cde,
       	   	           lpad(to_char(c.nsc),4,'0')||'-'||substr(c.niin,1,2)||'-'||
       	   	           substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
       	   	           c.itm_type_cde,
       	   	           c.struc_lev_cde,
       	   	           c.agency_ser_cntrl_ind,
       	   	           c.cntr_body_cde,
       	   	           e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
       	   	           e.me_desc
       	   	      into ws_scin, ws_sin,
       	   	           ws_srv_status_cde,
       	   	           ws_unit_holding,
       	   	           ws_dept_holding,
       	   	           ws_unit_located_at,
       	   	           ws_cond_cde,
       	   	           ws_nsn,
       	   	           ws_itm_type_cde,
       	   	           ws_struc_lev_cde,
       	   	           ws_agency_ser_cntrl_ind,
       	   	           ws_cntr_body_cde,
       	   	           ws_mec,
       	   	           ws_me_descr
       	   	      from ils.sss_entry_index a,
       	   	           ils.sss_er_core b,
       	   	           ils.item_data c,
       	   	           ils.names d,
       	   	           ils.me_master_rec e
       	   	     where a.ser_no = rec1.nha_ser_no
       	   	       and a.sin = rec1.nha_sin
       	   	       and a.sin_change_reas is null
       	   	       and a.sin_change_dte is null
       	   	       and b.scin = a.scin
       	   	       and c.sin = a.sin
       	   	       and d.sin = c.sin
       	   	       and d.itm_nme_type = '1'
       	   	       and d.name_seq = '1'
       	   	       and e.sin (+) = a.sin
       	   	       and (e.me_life_cyc (+) != '08' and
       	   	            e.me_rec_stat (+) not in ('C','X'));
      	      exception
       	      	  when no_data_found then
       	      	       ws_ser_no := 'INVALID - '||ws_ser_no;
       	      	       ws_srv_status_cde := '';
       	      END;
       	   ELSE
       	   	  ws_scin := rec1.scin; 
      	   	  ws_sin := rec1.sin;
   	          ws_srv_status_cde := rec1.srv_status_cde;
       	   	  ws_unit_holding := nvl(rec1.unit_holding,0);
       	   	  ws_dept_holding := nvl(rec1.dept_holding,0);
       	   	  ws_unit_located_at := nvl(rec1.unit_located_at,0);
       	   	  ws_cond_cde := rec1.cond_cde;
       	   	  ws_pers_located_at := rec1.pers_located_at;
       	   	  ws_nsn := rec1.nsn;
       	   	  ws_itm_type_cde := rec1.itm_type_cde;
       	   	  ws_struc_lev_cde := rec1.struc_lev_cde;
       	   	  ws_agency_ser_cntrl_ind := rec1.agency_ser_cntrl_ind;
              ws_mec := rec1.mec;
              ws_me_descr := rec1.me_desc;
      	   	  ws_ser_no := rec1.ser_no;
       	   	  ws_inst_ser_no := '';
       	   END IF;
-- Set up values for print table       	                     	                      
       	   IF :key.scr_req_unit is null or
       	   	  :key.wrk_org_func = 'HQ' then
       	   	  ws_unit := ws_unit_holding;
      	   ELSE
       	 	 	  IF :key.scr_all_units = 'Y' then
       	 	 	     ws_unit := 0;
       	 	       BEGIN
                   select 'X' 
                     into ws_dummy
                     from dual
                    where ws_unit_holding in (select unit_cde
                                                from ils.unt
                                               where comnd_cde = :key.wrk_comnd_cde);
                   ws_unit := ws_unit_holding;
                 exception
       	 	    	   when no_data_found then
       	 	    	        ws_unit := 0;
       	 	    	        goto nxt_rec;
       	 	       END;
       	 	 	  ELSE
       	 	 	  	ws_unit := ws_unit_cde;
      	      END IF;
       	   END IF;
       	
       	IF :key.scr_all_units = 'Y' then
		        wrk_gsb_unit := :key.scr_req_unit;
		        wrk_gsb_desc := :key.scr_unit_name;
		     ELSE
		     BEGIN
		     	    select unit_cde,
			  	           eng_abbr_nme
				      into   wrk_gsb_unit,
			  	           wrk_gsb_desc
  			      from 	 ils.unt
				      where  comnd_cde = :key.wrk_comnd_cde 
				      and    org_func  = 'CM';
			      END;
      END IF;
       	   
       	  IF :key.wrk_full_nsn is not null then
       	   	  IF :key.wrk_full_nsn = ws_nsn then
       	   	  	 null;
       	   	  ELSE
       	   	  	 goto nxt_rec;
       	   	  END IF;
       	  END IF; 

       	  IF :key.scr_me_cde is not null then
      	 	 	  IF :key.scr_var_cde is not null then
              	 IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then 
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_fam_cde is not null then 
              	 IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_cat_cde is not null then
              	 IF :key.scr_cat_cde = substr(ws_mec,1,1) then
              	 	  null;
              	 ELSE
       	 	 	  	    goto nxt_rec;
         	 	 	   END IF;
              END IF;
      	 	 ELSE
      	 	    :key.scr_me_cde  := ws_mec;
      	 	    :key.scr_mec_desc     := ws_me_descr;   	
       	 	 END IF;
  
-- Unit Description
           IF nvl(ws_unit,0) > 0 then
       	 	    BEGIN
       	 	      select eng_abbr_nme
       	 	        into ws_eng_abbr_nme
       	 	        from ils.unt
       	 	       where unit_cde = ws_unit;
       	 	      exception
       	 	        when no_data_found then
       	 	             ws_eng_abbr_nme := ws_unit ||' Unit not Found';
       	      END;
 	 	       END IF;

--       NSN Description
        wrk_nsn := substr(ws_nsn,1,4)||substr(ws_nsn,6,2)||
                   substr(ws_nsn,9,3)||substr(ws_nsn,13,4);
        wrk_nsn_desc := null;


if wrk_nsn is  null then 
--	message('insideif');
        PROC_NSN_DESC(wrk_nsn,
    	                wrk_nsn_desc,
       	              wrk_err,
       	              wrk_msg);

--message('afterif');
/** New additions/changes to Spec: 22/11/2010 - Marius**/
       if ws_found = 'N' then
          IF  :key.scr_depr_sched in ('N','A') then
         	    IF  :key.scr_purch_price in ('N','A')
         	    AND :key.scr_fair_val in ('N','A') 
         	    AND :key.scr_asset_cat = 'A'
         	    AND :key.scr_herit_asset in ('N','A') then
         	        NULL; 
         	    ELSE
         	        goto nxt_rec;
         	    END IF;
         	ELSE
         	   goto nxt_rec;
         	END IF;
       ELSIF ws_found = 'Y' then	
              IF :key.scr_depr_sched = 'A' then
         	       null;
         	    ELSIF  :key.scr_depr_sched = 'N' then
         	          IF  (ws_dep_active_ind = 'N'
         	          AND  ws_dep_closed_ind = 'N') then
         	              NULL;
         	          ELSE
         	             goto nxt_rec;
         	          END IF;     
         	    ELSIF  :key.scr_depr_sched = 'I' 
    	          AND  ws_dep_active_ind = 'Y' then
        	           NULL; 
              ELSIF  :key.scr_depr_sched = 'C' 
     	          AND  ws_dep_closed_ind = 'Y' then
        	           NULL; 
         	    ELSE          	     	      	         	
         	       goto nxt_rec;
         	    END IF;
         ELSE	     
         	  goto nxt_rec;   
         END IF;
        
       	              
/*       IF :key.scr_depr_sched = 'A' then
         	null;
       ELSIF   :key.scr_depr_sched = 'N' then
         	     IF  (ws_dep_active_ind = 'N'
         	         AND  ws_dep_closed_ind = 'N')
         	         OR   ws_found = 'N' then	
         	         NULL; 
         	     ELSE
         	     	  goto nxt_rec;
         	     END IF; 
        ELSIF  :key.scr_depr_sched = 'I' then
    	         IF ws_found = 'Y'
    	            AND ws_dep_active_ind = 'Y' then
        	        NULL; 
         	     ELSE 
         	     	  goto nxt_rec;
         	     END IF;
        ELSIF  :key.scr_depr_sched = 'C' then
     	         IF ws_found = 'Y'
    	            AND ws_dep_closed_ind = 'Y' then
        	        NULL; 
         	     ELSE          	     	      	         	
         	        goto nxt_rec;
         	     END IF;
         ELSE	     
         	  goto nxt_rec;   
         END IF; */
       
       IF :key.scr_rec_dte_fr is not null then
       	  IF (:key.scr_rec_dte_fr = ws_act_rv_dte
       	   	   OR :key.scr_rec_dte_fr < ws_act_rv_dte)
       	   	  AND ws_found = 'Y' then
       	   	  NULL;
          ELSE
       	   	  goto nxt_rec;
          END IF;
       ELSE
       	  null;
       END IF;   
       
       IF :key.scr_rec_dte_to is not null then
          IF (:key.scr_rec_dte_to >= ws_act_rv_dte
       	   	  AND ws_found = 'Y') then
       	   	  NULL;
          ELSE
       	      goto nxt_rec;
          END IF;
       ELSE
       	  null;
       END IF;   
              
       
       IF ws_found = 'Y' then
          IF :key.scr_purch_price = 'A' then 
      	      null;
      	  ELSIF    	
       	     :key.scr_purch_price = 'Y' then
         	   IF ws_purch_price is not null then 
       	   	    NULL;
       	   	 ELSE
       	   	    goto nxt_rec;   
         	   END IF;
         	ELSIF  
         	   	:key.scr_purch_price = 'N' then
         	   	   IF ws_purch_price is null then 
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	  ELSE	     
       	  	  goto nxt_rec;   
       	  END IF;
       ELSIF ws_found = 'N' then
          	 IF :key.scr_purch_price in ('A','N') then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;	  
       ELSE
          goto nxt_rec;    	
       END IF;
       
       
       IF 	ws_found = 'Y' then
           IF :key.scr_fair_val = 'A' then
       	      null;
       	   ELSIF   	
       	      :key.scr_fair_val = 'Y' then
       	   	  IF ws_fair_val is not null then 
       	   	     NULL;
       	   	  ELSE
       	   	     goto nxt_rec;   
         	    END IF;
         	  ELSIF  
         	   	:key.scr_fair_val = 'N' then 
         	   	   IF ws_fair_val is null then 
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	    ELSE	     
       	   	  goto nxt_rec;   
       	    END IF;
       ELSIF ws_found = 'N' then
          	 IF :key.scr_fair_val in ('A','N') then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;	   
       ELSE
          goto nxt_rec;	   	
       END IF;
     
         
       IF 	ws_found = 'Y' then
           IF :key.scr_asset_cat = 'A' then
       	      IF ws_ass_cat ='MJ' or
       	         ws_ass_cat = 'MN' then	
                 null;
       	      END IF;
       	   ELSIF   
       	      :key.scr_asset_cat = 'MJ' then
         	    IF ws_ass_cat ='MJ' then
       	   	     NULL;
       	   	  ELSE
       	   	     goto nxt_rec;   
         	    END IF;
         	 ELSIF  
         	   	:key.scr_asset_cat = 'MN' then 
         	   	   IF ws_ass_cat = 'MN' then
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	   ELSE	     
       	   	  goto nxt_rec;   
       	   END IF;
       ELSIF ws_found = 'N' then
          	 IF :key.scr_herit_asset = 'A' then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;
       ELSE
         goto nxt_rec;
       END IF;
       
       IF ws_found = 'Y' then
       	  IF :key.scr_herit_asset = 'A' then
       	     null;
       	  ELSIF   
       	    :key.scr_herit_asset = 'Y' then
         	  IF ws_heritage_ind = 'Y' then 
       	  	    NULL;
       	    ELSE
       	        goto nxt_rec;   
         	  END IF;
         	ELSIF  
         	   	:key.scr_herit_asset = 'N' then 
         	   	   IF ws_heritage_ind = 'N' then
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	   ELSE	     
       	   	  goto nxt_rec;   
         	 END IF;	
       ELSIF ws_found = 'N' then
          	 IF :key.scr_herit_asset in ('A','N') then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;	  
       ELSE
         goto nxt_rec;
       END IF;
       
       BEGIN
           	 insert into ils.lsst_prt_asset_fin
       	 	     (user_id,
       	 	      dte_tme_stamp,
       	 	      unit_cde,
       	 	      nsn,
       	 	      ser_no,
       	 	      unit_desc,
       	 	      nsn_desc,
       	 	      me_cde,
       	 	      me_desc,
       	 	      gsb_unit_cde,
       	 	      gsb_desc,
       	 	      itm_purch_prc,
       	 	      heritage_ind,
       	 	      res_val,
       	 	      fair_val,
       	 	      current_val,
       	 	      accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        dep_sched,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        close_reason)
       	 	   values 
       	  	    (:global.ils_force_no,
                :key.wrk_dte,
       	 	      ws_unit,
       	 	      wrk_nsn,
       	 	      ws_ser_no,
       	 	      ws_eng_abbr_nme,
       	 	      wrk_nsn_desc,
       	 	      ws_mec,
       	 	      ws_me_descr,
       	 	      wrk_gsb_unit,
       	 	      wrk_gsb_desc,
       	 	      ws_itm_purch_prc,
       	 	      ws_heritage_ind,
       	 	      ws_res_val,
       	 	      ws_fair_val,
       	 	      ws_current_val,
       	 	      ws_accum_improve_val,
       	        ws_accum_impair_val,
       	        ws_useful_lfe,
       	        ws_rem_useful_lfe,
       	        ws_ass_cat,
       	        ws_dep_sched, 
       	        ws_start_dep_dte,
       	        ws_lst_dep_fin_YYYYMM,
       	        ws_close_reason);
       EXCEPTION
       	  when dup_val_on_index then
       	  null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);pause;
--         raise form_trigger_failure; */
       END;
       end if;
      
<< nxt_rec >>
       null; 
ws_cnt:= ws_cnt + 1; 
    o_cnt := ws_cnt;           
      	 END LOOP;
       END;
		 END;
	END IF;
	
END;	
END;

Open in new window


I remove unnecessary comment  parts
I'm not going to walk through about 1000 lines of code looking for a syntax error.

Look at the error message provided:  It says it found the ';' when it expected 'IF'.  That means that somewhere in the code you have an IF statement without and END IF and the compiler ran out of code to process before it found it.
problem is END;
END LOOP; ought to be before END;, but i think that this END ought to be removed.
BEGIN
                  for rec1 in (select scin,
                     itm_purch_prc,
                     heritage_ind,
                     res_val,
                     fair_val,
                     current_val,
                     accum_improve_val,
                     accum_impair_val,
                     useful_lfe,
                     rem_useful_lfe,
                     ass_cat,
                     act_rv_dte,
                     dep_active_ind,
                          dep_closed_ind,
                     start_dep_dte,
                     lst_dep_fin_YYYYMM,
                     close_reason
                     --nvl(close_reason,'2222')
        from  ils.lsst_asset_fin a
              where  TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE))
             /* and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd')))
             AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE));                        */                                                                            
               
      --  where  trim(ser_data.scin) = trim(a.scin)      
        loop
              ws_scin := rec1.scin;
               ws_itm_purch_prc := rec1.itm_purch_prc;
               ws_heritage_ind := rec1.res_val;
               ws_res_val := rec1.fair_val;
               ws_fair_val := rec1.current_val;
               ws_current_val := rec1.accum_improve_val;
               ws_accum_improve_val := rec1. accum_impair_val;
               ws_accum_impair_val := rec1. useful_lfe;
               ws_useful_lfe := rec1.ass_cat;
               ws_rem_useful_lfe := rec1.act_rv_dte;
               ws_ass_cat := rec1.ass_cat;
               ws_act_rv_dte := rec1.act_rv_dte;
                    ws_dep_active_ind := rec1.dep_active_ind;
                    ws_dep_closed_ind := rec1.dep_closed_ind;
               ws_start_dep_dte :=  rec1.start_dep_dte;
               ws_lst_dep_fin_YYYYMM := lst_dep_fin_YYYYMM;
               ws_close_reason := rec1.close_reason;
            
                                                                                                  
                      ws_found := 'Y';
       /*EXCEPTION                        
         when no_data_found then
            ws_found := 'N';
--              goto nxt_rec;
              null;*/
       END;

slightwv is right, you have to do your job :)
thats fair enough i did fix the error but its seems this solution its not going to solve my problem
User generated imagethis is the latest code
PROCEDURE PROC_SELECT_DATA (o_cnt OUT NUMBER)IS
BEGIN
	DECLARE
		ws_unit_cde             number(06);
		wrk_gsb_unit            number(06);
		ws_unit                 number(06);
		ws_char_unit_cde        varchar2(06);
		ws_eng_abbr_nme         varchar2(30);
		wrk_gsb_desc            varchar2(30);
		ws_unit_line_ind        varchar2(01);
		ws_act_des              varchar2(01);
		ws_origin               varchar2(100);
		ws_ser_no               varchar2(30);
		ws_inst_ser_no          varchar2(30);
		ws_scin                 number(11);
		ws_sin                  number(11);
		ws_unit_holding         number(06);
		ws_dept_holding         number(06);
		ws_unit_located_at      number(06);
		ws_cond_cde             varchar2(01);
		ws_srv_status_cde       varchar2(3);
		ws_pers_located_at      varchar2(15);
		ws_itm_purch_prc        number(15,4);
		wrk_purch_prc           varchar2(3);
		ws_heritage_ind         varchar2(1);
    wrk_herit_asset         varchar2(3);		
		ws_res_val              number(15,4);
		ws_fair_val             number(15,4);
		wrk_fair_val            varchar2(3);
		ws_current_val          number(15,4);
		ws_accum_improve_val    number(15,4);
		ws_accum_impair_val     number(15,4);
		ws_useful_lfe           number(4);
		ws_rem_useful_lfe       number(4);
		ws_ass_cat              varchar2(2);
		wrk_ass_cat             varchar2(5);
		ws_dep_sched            varchar2(1);
		wrk_dep_sched           varchar2(15);
		ws_start_dep_dte        date;
		ws_close_reason         varchar2(20);
		ws_lst_dep_fin_YYYYMM   number(6);
		ws_prov_reg_no          varchar2(20);
		ws_nsn                  varchar2(16);
		ws_itm_type_cde         varchar2(01);
		ws_struc_lev_cde        varchar2(01);
		ws_agency_ser_cntrl_ind varchar2(01);
		ws_cntr_body_cde        varchar2(02);
		ws_org_func             varchar2(02);
		ws_nsn_descr            varchar2(200);
		ws_mec                  varchar2(04);
		ws_me_descr             varchar2(50);
		ws_sort_cc              number(02);
		ws_sort_srv             number(02);
		ws_err_ind              varchar2(01);
		ws_err_msg              varchar2(80);
		ws_ldesc                varchar2(50);
		ws_sdesc                varchar2(25);
		ws_short_nme            varchar2(30);
		ws_long_nme             varchar2(50);
		ws_stat_ind             varchar2(01);
		ws_oli_ind              varchar2(01);
		ws_pers_details         varchar2(100);
		ws_desc                 varchar2(150);
		ws_rank                 varchar2(15);
		ws_pers_found           varchar2(01);
		ws_un_no                varchar2(10);
		ws_dummy                varchar2(01);
		ws_m_nsn                varchar2(16);
		proc_cfg_tab_no         number;
		proc_mec                varchar2(04);
		proc_mec_nsn            varchar2(16);
		proc_cfg_tab_desc       varchar2(200);
		proc_mec_desc           varchar2(200);
		proc_mec_nsn_desc       varchar2(200);
		proc_exist_corp_ind     varchar2(01);
		proc_err_ind            varchar2(01);
		proc_err_msg            varchar2(80);
		proc_me_niin            varchar2(13);
		wrk_nsn_desc            varchar2(200);
		wrk_err                 varchar2(1);
		wrk_msg                 varchar2(50);
		wrk_nsn                 varchar2(16);
		v_nsn                   varchar2(16);
		wrk_totals              varchar2(3);
		wrk_all_units           varchar2(3);
		ws_found                varchar2(1);
		ws_dep_active_ind       varchar2(1);
		ws_dep_closed_ind       varchar2(1);
		ws_act_rv_dte           date;
		ws_purch_price          number(15,4);
		ws_cnt                  number := 0;
		
	BEGIN
    ws_unit_cde := 0;
    
		IF :key.wrk_org_func  = 'HQ' or
		  (:key.wrk_org_func  = 'CM' and
			 :key.scr_all_units = 'Y') THEN
			 ws_unit_cde     := 0;
		 	 ws_eng_abbr_nme := '';
		ELSE	  
		   ws_unit_cde     := :key.scr_req_unit;
	     ws_eng_abbr_nme := :key.scr_unit_desc;
		END IF;
		
	--	message('testt09');
	--	message(:key.scr_ser_no);
	
IF :key.scr_ser_no is not null then
     DECLARE
     	 CURSOR         sel_ser_no is
       select         s.sin,
                      s.ser_no, 
       	              e.scin,
       	              e.unit_holding,
       	              me_cat_cde||me_family_cde||me_var_cde mec,
       	              i.nsc||'-'||substr(i.niin,1,2)||'-'||
  	                  substr(i.niin,3,3)||'-'||substr(i.niin,6,4) nsn,
  	                  i.agency_ser_cntrl_ind,
       	              me_desc
       from           ils.me_master_rec          m,
       	              ils.sss_entry_index        s,
	         		        ils.sss_er_core            e,
	         		        ils.item_data              i
      where           s.ser_no			    = :key.scr_ser_no
       and            e.scin 				    = s.scin
			 and            s.sin_change_reas is null
  	   and            s.sin_change_dte is null 
			 and           ((nvl(e.unit_holding,0) = nvl(ws_unit_cde,0) and
  	                  nvl(ws_unit_cde,0) > 0) or
  	                  nvl(ws_unit_cde,0) = 0) 
	     and            m.sin (+)            = s.sin
 	     and            i.sin             = s.sin
-- 	      or             s.sin            = :key.wrk_sin)
  	   and            (m.me_life_cyc (+) != '08' and
                      m.me_rec_stat (+) not in ('C','X'));
	   	 
     BEGIN
     	 FOR rec1 IN sel_ser_no
	     LOOP
      BEGIN
			     select itm_purch_prc,
       	 	        heritage_ind,
       	 	        res_val,
       	 	        fair_val,
       	 	        current_val,
       	 	        accum_improve_val,
       	 	        accum_impair_val,
       	 	        useful_lfe,
       	 	        rem_useful_lfe,
       	 	        ass_cat,
       	 	        act_rv_dte,
       	 	        dep_active_ind,
       	 	        dep_closed_ind,
       	 	        start_dep_dte,
       	 	        lst_dep_fin_YYYYMM,
       	 	        close_reason
			     into   
			            ws_itm_purch_prc,
       	 	        ws_heritage_ind,
       	 	        ws_res_val,
       	 	        ws_fair_val,
       	 	        ws_current_val,
       	 	        ws_accum_improve_val,
       	 	        ws_accum_impair_val,
       	 	        ws_useful_lfe,
       	 	        ws_rem_useful_lfe,
       	 	        ws_ass_cat,
       	 	        ws_act_rv_dte,
       	 	        ws_dep_active_ind,
       	 	        ws_dep_closed_ind,
       	 	        ws_start_dep_dte,
       	 	        ws_lst_dep_fin_YYYYMM,
       	 	        ws_close_reason
				    from   ils.lsst_asset_fin  a
  			   where  scin            = rec1.scin
  			     /*and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
   																	      AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/
   																	      
   					 and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd'))) 
             AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE));												      
   	         																      
  			   ws_found := 'Y';
      EXCEPTION                         
         when no_data_found THEN
			      ws_found := 'N';
-- 			      goto nxt_rec;
            null;
      END;
      
      ws_scin := rec1.scin; 
      ws_sin := rec1.sin;
   	  ws_unit_holding := nvl(rec1.unit_holding,0);
      ws_mec := rec1.mec;
      ws_me_descr := rec1.me_desc;
      ws_ser_no := rec1.ser_no;
      ws_inst_ser_no := '';       	   	  
	     	
      	IF :key.scr_req_unit is null or
       	   :key.wrk_org_func = 'HQ' then
       	   	  ws_unit := ws_unit_holding;
       	ELSE
       	 	 	  IF :key.scr_all_units = 'Y' then
       	 	 	     ws_unit := 0;
       	 	       BEGIN
                   select 'X' 
                     into ws_dummy
                     from dual
                    where ws_unit_holding in (select unit_cde
                                                from ils.unt
                                               where comnd_cde = :key.wrk_comnd_cde);
                   ws_unit := ws_unit_holding;
       	 	       exception
       	 	    	   when no_data_found THEN
       	 	    	        ws_unit := 0;
      	 	    	        goto nxt_rec;
       	 	       END;
       	 	 	  ELSE
       	 	 	  	ws_unit := ws_unit_cde;
       	      END IF;
	     END IF;
	     	IF :key.scr_all_units = 'Y' then
		        wrk_gsb_unit := :key.scr_req_unit;
		        wrk_gsb_desc := :key.scr_unit_name;
		    ELSE
		     BEGIN
		     	    select unit_cde,
			  	           eng_abbr_nme
				      into   wrk_gsb_unit,
			  	           wrk_gsb_desc
  			      from 	 ils.unt
				      where  comnd_cde = :key.wrk_comnd_cde 
				      and    org_func  = 'CM';
			      END;
     END IF;
     
        IF :key.scr_me_cde is not null then
      	 	 	  IF :key.scr_var_cde is not null then
              	 IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then 
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_fam_cde is not null then 
              	 IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_cat_cde is not null then
              	 IF :key.scr_cat_cde = substr(ws_mec,1,1) then
              	 	  null;
              	 ELSE
       	 	 	  	    goto nxt_rec;
         	 	 	   END IF;
              END IF;
           ELSE
      	 	    :key.scr_me_cde  := ws_mec;
      	 	    :key.scr_mec_desc     := ws_me_descr;    
       	 	 END IF;   
-- Unit Description
           IF nvl(ws_unit,0) > 0 then
       	 	    BEGIN
       	 	      select eng_abbr_nme
       	 	        into ws_eng_abbr_nme
       	 	        from ils.unt
       	 	       where unit_cde = ws_unit;
       	 	      exception
       	 	        when no_data_found then
       	 	             ws_eng_abbr_nme := ws_unit ||' Unit not Found';
       	      END;
 	 	       END IF;
 	 	       
 	 	      IF :key.wrk_full_nsn is not null then
       	   	  IF :key.wrk_full_nsn = ws_nsn then
       	   	  	 null;
       	   	  ELSE
      	   	  	 goto nxt_rec;
       	   	  END IF;
       	  END IF;     
 
      BEGIN
			     select ser_no
				   into   ws_ser_no
  			   from   ils.sss_entry_index
  			   where  scin            = rec1.scin
  			   and    SIN_CHANGE_DTE is null;
  		EXCEPTION
      	  when no_data_found then
      	  message('No serial number for this ICN');
      	  raise form_trigger_failure;
	    END; 
	     
	     wrk_nsn := substr(rec1.nsn,1,4)||substr(rec1.nsn,6,2)||
                  substr(rec1.nsn,9,3)||substr(rec1.nsn,13,4);
                  
       wrk_nsn_desc := null;
       
      -- wrk_nsn   := nvl(wrk_nsn,'');
     --  message('error12');
     if wrk_nsn is not null then
       PROC_NSN_DESC(wrk_nsn,
    	                wrk_nsn_desc,
       	              wrk_err,
       	              wrk_msg); 
       	             
      
      BEGIN
           	 insert into ils.lsst_prt_asset_fin
       	 	     (user_id,
       	 	      dte_tme_stamp,
       	 	      unit_cde,
       	 	      nsn,
       	 	      ser_no,
       	 	      unit_desc,
       	 	      nsn_desc,
       	 	      me_cde,
       	 	      me_desc,
       	 	      gsb_unit_cde,
       	 	      gsb_desc,
       	 	      itm_purch_prc,
       	 	      heritage_ind,
       	 	      res_val,
       	 	      fair_val,
       	 	      current_val,
       	 	      accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        dep_sched,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        close_reason)
       	 	   values 
       	  	    (:global.ils_force_no,
                :key.wrk_dte,
       	 	      ws_unit,
       	 	      wrk_nsn,
       	 	      rec1.ser_no,
       	 	      ws_eng_abbr_nme,
       	 	      wrk_nsn_desc,
       	 	      ws_mec,
       	 	      ws_me_descr,
       	 	      wrk_gsb_unit,
       	 	      wrk_gsb_desc,
       	 	      ws_itm_purch_prc,
       	 	      ws_heritage_ind,
       	 	      ws_res_val,
       	 	      ws_fair_val,
       	 	      ws_current_val,
       	 	      ws_accum_improve_val,
       	        ws_accum_impair_val,
       	        ws_useful_lfe,
       	        ws_rem_useful_lfe,
       	        ws_ass_cat,
       	        ws_dep_sched, 
       	        ws_start_dep_dte,
       	        ws_lst_dep_fin_YYYYMM,
       	        ws_close_reason);
      EXCEPTION
       	  when dup_val_on_index then
       	  null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);
--         raise form_trigger_failure;       	        
      END;
      end if;
ws_cnt := ws_cnt + 1;
<< nxt_rec >>
     null;
     o_cnt := ws_cnt;          
--message('ws_cnt:'||ws_cnt);pause;
       END LOOP;
     END;	
ELSE
		BEGIN
      DECLARE
  	    cursor ser_data is
  	      select a.ser_no, 
  	             a.scin,
  	             a.sin,
  	             nvl(b.unit_holding,0) unit_holding,
  	             nvl(b.dept_holding,0) dept_holding,
  	             nvl(b.unit_located_at,0) unit_located_at,
  	             b.cond_cde,
  	             b.srv_status_cde,
  	             b.pers_located_at,
  	             b.nha_sin,
  	             b.nha_ser_no,
  	             c.nsc||'-'||substr(c.niin,1,2)||'-'||
  	             substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
  	             c.itm_type_cde,
  	             c.struc_lev_cde,
  	             c.agency_ser_cntrl_ind,
  	             d.itm_nme,
  	             e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
  	             e.me_desc  	             
  	        from ils.sss_entry_index a,
  	             ils.sss_er_core b,
  	             ils.item_data c,
  	             ils.names d,
  	             ils.me_master_rec e
  	       where a.sin_change_reas is null
  	         and a.sin_change_dte is null
  	         and b.scin = a.scin
  	         and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
  	               nvl(ws_unit_cde,0) > 0) or
  	               nvl(ws_unit_cde,0) = 0)
  	         and c.sin = a.sin
  	         and d.sin = c.sin
  	         and d.itm_nme_type = '1'
  	         and d.name_seq = '1'
  	         and e.sin (+) = a.sin
  	         and (e.me_life_cyc (+) != '08' and
                  e.me_rec_stat (+) not in ('C','X'))
           order by b.unit_holding, a.ser_no;

       BEGIN
       	 For rec1 in (select scin,
       	        itm_purch_prc,
       	        heritage_ind,
       	        res_val,
       	        fair_val,
       	        current_val,
       	        accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        act_rv_dte,
       	        dep_active_ind,
       	 	      dep_closed_ind,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        close_reason
        from  ils.lsst_asset_fin a
      	  where  TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE)) 
      	  LOOP
       --  BEGIN     	 	
       		ws_scin := rec1.scin;
       	  ws_itm_purch_prc := rec1.itm_purch_prc;
       	  ws_heritage_ind := rec1.res_val;
       	  ws_res_val := rec1.fair_val;
       	  ws_fair_val := rec1.current_val;
       	  ws_current_val := rec1.accum_improve_val;
       	  ws_accum_improve_val := rec1.accum_impair_val;
       	  ws_accum_impair_val := rec1.accum_impair_val;
       	  ws_useful_lfe := rec1.useful_lfe;
       	  ws_rem_useful_lfe := rec1.rem_useful_lfe;
       	  ws_ass_cat := rec1.ass_cat;
       	  ws_act_rv_dte := rec1.act_rv_dte;
     	 	  ws_dep_active_ind := rec1.dep_active_ind;
     	 	  ws_dep_closed_ind := rec1.dep_closed_ind;
       	  ws_start_dep_dte :=  rec1.start_dep_dte;
       	  ws_lst_dep_fin_YYYYMM := rec1.lst_dep_fin_YYYYMM;
       	  ws_close_reason := rec1.close_reason;														  	  
   																  	  
   																  	  
 			   ws_found := 'Y';
       /*EXCEPTION                         
         when no_data_found then
            ws_found := 'N';
--              goto nxt_rec;
              null;*/
       END;
--message('ws_scin from table: '||ws_scin);pause;           	 	
   	 	 ws_ser_no := '';
   	 	 ws_inst_ser_no := '';
       	   IF rec1.srv_status_cde = 'C02' then
       	   	  ws_ser_no := rec1.nha_ser_no;
       	   	  ws_inst_ser_no := rec1.ser_no;
       	   	  ws_pers_located_at := rec1.pers_located_at;
       	      BEGIN
       	   	    select a.scin, a.sin,
       	   	           b.srv_status_cde,
       	   	           b.unit_holding,
       	   	           nvl(b.dept_holding,0),
       	   	           nvl(b.unit_located_at,0),
       	   	           b.cond_cde,
       	   	           lpad(to_char(c.nsc),4,'0')||'-'||substr(c.niin,1,2)||'-'||
       	   	           substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
       	   	           c.itm_type_cde,
       	   	           c.struc_lev_cde,
       	   	           c.agency_ser_cntrl_ind,
       	   	           c.cntr_body_cde,
       	   	           e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
       	   	           e.me_desc
       	   	      into ws_scin, ws_sin,
       	   	           ws_srv_status_cde,
       	   	           ws_unit_holding,
       	   	           ws_dept_holding,
       	   	           ws_unit_located_at,
       	   	           ws_cond_cde,
       	   	           ws_nsn,
       	   	           ws_itm_type_cde,
       	   	           ws_struc_lev_cde,
       	   	           ws_agency_ser_cntrl_ind,
       	   	           ws_cntr_body_cde,
       	   	           ws_mec,
       	   	           ws_me_descr
       	   	      from ils.sss_entry_index a,
       	   	           ils.sss_er_core b,
       	   	           ils.item_data c,
       	   	           ils.names d,
       	   	           ils.me_master_rec e
       	   	     where a.ser_no = rec1.nha_ser_no
       	   	       and a.sin = rec1.nha_sin
       	   	       and a.sin_change_reas is null
       	   	       and a.sin_change_dte is null
       	   	       and b.scin = a.scin
       	   	       and c.sin = a.sin
       	   	       and d.sin = c.sin
       	   	       and d.itm_nme_type = '1'
       	   	       and d.name_seq = '1'
       	   	       and e.sin (+) = a.sin
       	   	       and (e.me_life_cyc (+) != '08' and
       	   	            e.me_rec_stat (+) not in ('C','X'));
      	      exception
       	      	  when no_data_found then
       	      	       ws_ser_no := 'INVALID - '||ws_ser_no;
       	      	       ws_srv_status_cde := '';
       	      END;
       	   ELSE
       	   	  ws_scin := rec1.scin; 
      	   	  ws_sin := rec1.sin;
   	          ws_srv_status_cde := rec1.srv_status_cde;
       	   	  ws_unit_holding := nvl(rec1.unit_holding,0);
       	   	  ws_dept_holding := nvl(rec1.dept_holding,0);
       	   	  ws_unit_located_at := nvl(rec1.unit_located_at,0);
       	   	  ws_cond_cde := rec1.cond_cde;
       	   	  ws_pers_located_at := rec1.pers_located_at;
       	   	  ws_nsn := rec1.nsn;
       	   	  ws_itm_type_cde := rec1.itm_type_cde;
       	   	  ws_struc_lev_cde := rec1.struc_lev_cde;
       	   	  ws_agency_ser_cntrl_ind := rec1.agency_ser_cntrl_ind;
              ws_mec := rec1.mec;
              ws_me_descr := rec1.me_desc;
      	   	  ws_ser_no := rec1.ser_no;
       	   	  ws_inst_ser_no := '';
       	   END IF;
-- Set up values for print table       	                     	                      
       	   IF :key.scr_req_unit is null or
       	   	  :key.wrk_org_func = 'HQ' then
       	   	  ws_unit := ws_unit_holding;
      	   ELSE
       	 	 	  IF :key.scr_all_units = 'Y' then
       	 	 	     ws_unit := 0;
       	 	       BEGIN
                   select 'X' 
                     into ws_dummy
                     from dual
                    where ws_unit_holding in (select unit_cde
                                                from ils.unt
                                               where comnd_cde = :key.wrk_comnd_cde);
                   ws_unit := ws_unit_holding;
                 exception
       	 	    	   when no_data_found then
       	 	    	        ws_unit := 0;
       	 	    	        goto nxt_rec;
       	 	       END;
       	 	 	  ELSE
       	 	 	  	ws_unit := ws_unit_cde;
      	      END IF;
       	   END IF;
       	
       	IF :key.scr_all_units = 'Y' then
		        wrk_gsb_unit := :key.scr_req_unit;
		        wrk_gsb_desc := :key.scr_unit_name;
		     ELSE
		     BEGIN
		     	    select unit_cde,
			  	           eng_abbr_nme
				      into   wrk_gsb_unit,
			  	           wrk_gsb_desc
  			      from 	 ils.unt
				      where  comnd_cde = :key.wrk_comnd_cde 
				      and    org_func  = 'CM';
			      END;
      END IF;
       	   
       	  IF :key.wrk_full_nsn is not null then
       	   	  IF :key.wrk_full_nsn = ws_nsn then
       	   	  	 null;
       	   	  ELSE
       	   	  	 goto nxt_rec;
       	   	  END IF;
       	  END IF; 

       	  IF :key.scr_me_cde is not null then
      	 	 	  IF :key.scr_var_cde is not null then
              	 IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then 
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_fam_cde is not null then 
              	 IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
       	 	 	  	    null;
              	 ELSE
              	 	  goto nxt_rec;
              	 END IF;
              ELSIF :key.scr_cat_cde is not null then
              	 IF :key.scr_cat_cde = substr(ws_mec,1,1) then
              	 	  null;
              	 ELSE
       	 	 	  	    goto nxt_rec;
         	 	 	   END IF;
              END IF;
      	 	 ELSE
      	 	    :key.scr_me_cde  := ws_mec;
      	 	    :key.scr_mec_desc     := ws_me_descr;   	
       	 	 END IF;
  
-- Unit Description
           IF nvl(ws_unit,0) > 0 then
       	 	    BEGIN
       	 	      select eng_abbr_nme
       	 	        into ws_eng_abbr_nme
       	 	        from ils.unt
       	 	       where unit_cde = ws_unit;
       	 	      exception
       	 	        when no_data_found then
       	 	             ws_eng_abbr_nme := ws_unit ||' Unit not Found';
       	      END;
 	 	       END IF;

--       NSN Description
        wrk_nsn := substr(ws_nsn,1,4)||substr(ws_nsn,6,2)||
                   substr(ws_nsn,9,3)||substr(ws_nsn,13,4);
        wrk_nsn_desc := null;

/*message('wrk_nsn = '||wrk_nsn);
message('wrk_nsn_desc = '||wrk_nsn_desc);
message('wrk_err = '||wrk_err);
message('wrk_msg = '||wrk_msg);*/
--message('error12344');
-- wrk_nsn  := nvl( wrk_nsn ,'');
--message('error12');
if wrk_nsn is  null then 
--	message('insideif');
        PROC_NSN_DESC(wrk_nsn,
    	                wrk_nsn_desc,
       	              wrk_err,
       	              wrk_msg);

--message('afterif');
/** New additions/changes to Spec: 22/11/2010 - Marius**/
       if ws_found = 'N' then
          IF  :key.scr_depr_sched in ('N','A') then
         	    IF  :key.scr_purch_price in ('N','A')
         	    AND :key.scr_fair_val in ('N','A') 
         	    AND :key.scr_asset_cat = 'A'
         	    AND :key.scr_herit_asset in ('N','A') then
         	        NULL; 
         	    ELSE
         	        goto nxt_rec;
         	    END IF;
         	ELSE
         	   goto nxt_rec;
         	END IF;
       ELSIF ws_found = 'Y' then	
              IF :key.scr_depr_sched = 'A' then
         	       null;
         	    ELSIF  :key.scr_depr_sched = 'N' then
         	          IF  (ws_dep_active_ind = 'N'
         	          AND  ws_dep_closed_ind = 'N') then
         	              NULL;
         	          ELSE
         	             goto nxt_rec;
         	          END IF;     
         	    ELSIF  :key.scr_depr_sched = 'I' 
    	          AND  ws_dep_active_ind = 'Y' then
        	           NULL; 
              ELSIF  :key.scr_depr_sched = 'C' 
     	          AND  ws_dep_closed_ind = 'Y' then
        	           NULL; 
         	    ELSE          	     	      	         	
         	       goto nxt_rec;
         	    END IF;
         ELSE	     
         	  goto nxt_rec;   
         END IF;
        
       	              
/*       IF :key.scr_depr_sched = 'A' then
         	null;
       ELSIF   :key.scr_depr_sched = 'N' then
         	     IF  (ws_dep_active_ind = 'N'
         	         AND  ws_dep_closed_ind = 'N')
         	         OR   ws_found = 'N' then	
         	         NULL; 
         	     ELSE
         	     	  goto nxt_rec;
         	     END IF; 
        ELSIF  :key.scr_depr_sched = 'I' then
    	         IF ws_found = 'Y'
    	            AND ws_dep_active_ind = 'Y' then
        	        NULL; 
         	     ELSE 
         	     	  goto nxt_rec;
         	     END IF;
        ELSIF  :key.scr_depr_sched = 'C' then
     	         IF ws_found = 'Y'
    	            AND ws_dep_closed_ind = 'Y' then
        	        NULL; 
         	     ELSE          	     	      	         	
         	        goto nxt_rec;
         	     END IF;
         ELSE	     
         	  goto nxt_rec;   
         END IF; */
       
       IF :key.scr_rec_dte_fr is not null then
       	  IF (:key.scr_rec_dte_fr = ws_act_rv_dte
       	   	   OR :key.scr_rec_dte_fr < ws_act_rv_dte)
       	   	  AND ws_found = 'Y' then
       	   	  NULL;
          ELSE
       	   	  goto nxt_rec;
          END IF;
       ELSE
       	  null;
       END IF;   
       
       IF :key.scr_rec_dte_to is not null then
          IF (:key.scr_rec_dte_to >= ws_act_rv_dte
       	   	  AND ws_found = 'Y') then
       	   	  NULL;
          ELSE
       	      goto nxt_rec;
          END IF;
       ELSE
       	  null;
       END IF;   
              
       
       IF ws_found = 'Y' then
          IF :key.scr_purch_price = 'A' then 
      	      null;
      	  ELSIF    	
       	     :key.scr_purch_price = 'Y' then
         	   IF ws_purch_price is not null then 
       	   	    NULL;
       	   	 ELSE
       	   	    goto nxt_rec;   
         	   END IF;
         	ELSIF  
         	   	:key.scr_purch_price = 'N' then
         	   	   IF ws_purch_price is null then 
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	  ELSE	     
       	  	  goto nxt_rec;   
       	  END IF;
       ELSIF ws_found = 'N' then
          	 IF :key.scr_purch_price in ('A','N') then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;	  
       ELSE
          goto nxt_rec;    	
       END IF;
       
       
       IF 	ws_found = 'Y' then
           IF :key.scr_fair_val = 'A' then
       	      null;
       	   ELSIF   	
       	      :key.scr_fair_val = 'Y' then
       	   	  IF ws_fair_val is not null then 
       	   	     NULL;
       	   	  ELSE
       	   	     goto nxt_rec;   
         	    END IF;
         	  ELSIF  
         	   	:key.scr_fair_val = 'N' then 
         	   	   IF ws_fair_val is null then 
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	    ELSE	     
       	   	  goto nxt_rec;   
       	    END IF;
       ELSIF ws_found = 'N' then
          	 IF :key.scr_fair_val in ('A','N') then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;	   
       ELSE
          goto nxt_rec;	   	
       END IF;
     
         
       IF 	ws_found = 'Y' then
           IF :key.scr_asset_cat = 'A' then
       	      IF ws_ass_cat ='MJ' or
       	         ws_ass_cat = 'MN' then	
                 null;
       	      END IF;
       	   ELSIF   
       	      :key.scr_asset_cat = 'MJ' then
         	    IF ws_ass_cat ='MJ' then
       	   	     NULL;
       	   	  ELSE
       	   	     goto nxt_rec;   
         	    END IF;
         	 ELSIF  
         	   	:key.scr_asset_cat = 'MN' then 
         	   	   IF ws_ass_cat = 'MN' then
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	   ELSE	     
       	   	  goto nxt_rec;   
       	   END IF;
       ELSIF ws_found = 'N' then
          	 IF :key.scr_herit_asset = 'A' then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;
       ELSE
         goto nxt_rec;
       END IF;
       
       IF ws_found = 'Y' then
       	  IF :key.scr_herit_asset = 'A' then
       	     null;
       	  ELSIF   
       	    :key.scr_herit_asset = 'Y' then
         	  IF ws_heritage_ind = 'Y' then 
       	  	    NULL;
       	    ELSE
       	        goto nxt_rec;   
         	  END IF;
         	ELSIF  
         	   	:key.scr_herit_asset = 'N' then 
         	   	   IF ws_heritage_ind = 'N' then
       	   	        NULL;
       	   	     ELSE
       	   	        goto nxt_rec;   
       	   	     END IF;
       	   ELSE	     
       	   	  goto nxt_rec;   
         	 END IF;	
       ELSIF ws_found = 'N' then
          	 IF :key.scr_herit_asset in ('A','N') then
          	 	  null;
          	 ELSE
          	 	 goto nxt_rec;
          	 END IF;	  
       ELSE
         goto nxt_rec;
       END IF;
       
       BEGIN
           	 insert into ils.lsst_prt_asset_fin
       	 	     (user_id,
       	 	      dte_tme_stamp,
       	 	      unit_cde,
       	 	      nsn,
       	 	      ser_no,
       	 	      unit_desc,
       	 	      nsn_desc,
       	 	      me_cde,
       	 	      me_desc,
       	 	      gsb_unit_cde,
       	 	      gsb_desc,
       	 	      itm_purch_prc,
       	 	      heritage_ind,
       	 	      res_val,
       	 	      fair_val,
       	 	      current_val,
       	 	      accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        dep_sched,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        close_reason)
       	 	   values 
       	  	    (:global.ils_force_no,
                :key.wrk_dte,
       	 	      ws_unit,
       	 	      wrk_nsn,
       	 	      ws_ser_no,
       	 	      ws_eng_abbr_nme,
       	 	      wrk_nsn_desc,
       	 	      ws_mec,
       	 	      ws_me_descr,
       	 	      wrk_gsb_unit,
       	 	      wrk_gsb_desc,
       	 	      ws_itm_purch_prc,
       	 	      ws_heritage_ind,
       	 	      ws_res_val,
       	 	      ws_fair_val,
       	 	      ws_current_val,
       	 	      ws_accum_improve_val,
       	        ws_accum_impair_val,
       	        ws_useful_lfe,
       	        ws_rem_useful_lfe,
       	        ws_ass_cat,
       	        ws_dep_sched, 
       	        ws_start_dep_dte,
       	        ws_lst_dep_fin_YYYYMM,
       	        ws_close_reason);
       EXCEPTION
       	  when dup_val_on_index then
       	  null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);pause;
--         raise form_trigger_failure; */
       END;
       end if;
      
<< nxt_rec >>
       null; 
ws_cnt:= ws_cnt + 1; 
    o_cnt := ws_cnt;           
      	 END LOOP;
       END;
		 END;
	END IF;
	
END;	
END;

Open in new window

Line 419:  For rec1 in (select scin,

In that cursor named rec1 there is no column srv_status_cde selected.
yes that true that's why am saying to henka this solution will not work because in table lsst_asset_fin there is no column srv_status_cde
I believe what Henka was suggestion was not declaring the cursor sel_ser_no in the declaration section of the procedure but moving it directly into the FOR loop.  To me, it doesn't make any difference either way.

If you moved the cursor as it was into the FOR loop, all the columns would be there.
ok than this solution fail I will keep on trying if you have time maybe you can come with new suggestion this si what I was trying to do today
And keep in mind  a COMMIT  in Oracle Forms is not a always a commit in the database.
Oracle Forms keeps track of changed blocks in the form:  a "forms"-commit checks if there are such changes : if there are non,  the "forms"-commit is considered ready and there wil be no database-commit. An insert-statement within an oracle-forms procedure does not change a block.
If you want todo a COMMIT of inserted records without a change in the blocks you could issue a call to standard.commit from within an oracle procedure (from exampe the on_commit trigger) in previous forms versions, but that is not possible in the later versions.  So for myself I created a  package xxx with a procedure do_commit in the database.  The database procedure issues a commit and I call that procedure from oracle-forms.
If you do have a forms block based on a database-record within the form you can also force a real commit by changing such a record whenever you do insert records not from a block but from a procedure. It can be a dummy change:   :block1.item1 := :block1.item1;

But considering you where inserting into a temporary table  a real commit could cause the temporary data to be lost , depending on the way you defined your 'global temporary table'.  (will that table be empty at a commit or at the end of the session).
I cant move my procedure to database there are block item which am using in my logic
You can move your procedure to the database, block items can be passed to the procedure as parameters.
that will not change anything am am already doing that even if I move code to block the code will be still the same so the error will not dispear
User generated image
In this answer (ID: 41542303) markgeer spoke about different sessions in forms and reports. Have you test it ? E.g. instead of using PROC_SELECT_DATA in KEY-COMMIT trigger you can try insert record directly to the template table.
If this insert will be successfull then you have to debug PROC_SELECT_DATA, put messages there (as it was recommended in previous posts) with relevant block item values.
am trying that I create this procedure currently is selecting all values from the cursor instead of the link values rec1.scin = scin I what to see if I will be able to insert without other logic

PROCEDURE PROC_SELECT_DATA (o_cnt OUT NUMBER)IS
BEGIN
	DECLARE
		ws_unit_cde             number(06) := 10223;
		wrk_gsb_unit            number(06);
		ws_unit                 number(06) := 10223;
		ws_char_unit_cde        varchar2(06);
		ws_eng_abbr_nme         varchar2(30);
		wrk_gsb_desc            varchar2(30);
		ws_unit_line_ind        varchar2(01);
		ws_act_des              varchar2(01);
		ws_origin               varchar2(100);
		ws_ser_no               varchar2(30);
		ws_inst_ser_no          varchar2(30);
		ws_scin                 number(11);
		ws_sin                  number(11);
		ws_unit_holding         number(06);
		ws_dept_holding         number(06);
		ws_unit_located_at      number(06);
		ws_cond_cde             varchar2(01);
		ws_srv_status_cde       varchar2(3);
		ws_pers_located_at      varchar2(15);
		ws_itm_purch_prc        number(15,4);
		wrk_purch_prc           varchar2(3);
		ws_heritage_ind         varchar2(1);
    wrk_herit_asset         varchar2(3);		
		ws_res_val              number(15,4);
		ws_fair_val             number(15,4);
		wrk_fair_val            varchar2(3);
		ws_current_val          number(15,4);
		ws_accum_improve_val    number(15,4);
		ws_accum_impair_val     number(15,4);
		ws_useful_lfe           number(4);
		ws_rem_useful_lfe       number(4);
		ws_ass_cat              varchar2(2);
		wrk_ass_cat             varchar2(5);
		ws_dep_sched            varchar2(1);
		wrk_dep_sched           varchar2(15);
		ws_start_dep_dte        date;
		ws_close_reason         varchar2(20);
		ws_lst_dep_fin_YYYYMM   number(6);
		ws_prov_reg_no          varchar2(20);
		ws_nsn                  varchar2(16);
		ws_itm_type_cde         varchar2(01);
		ws_struc_lev_cde        varchar2(01);
		ws_agency_ser_cntrl_ind varchar2(01);
		ws_cntr_body_cde        varchar2(02);
		ws_org_func             varchar2(02);
		ws_nsn_descr            varchar2(200);
		ws_mec                  varchar2(04);
		ws_me_descr             varchar2(50);
		ws_sort_cc              number(02);
		ws_sort_srv             number(02);
		ws_err_ind              varchar2(01);
		ws_err_msg              varchar2(80);
		ws_ldesc                varchar2(50);
		ws_sdesc                varchar2(25);
		ws_short_nme            varchar2(30);
		ws_long_nme             varchar2(50);
		ws_stat_ind             varchar2(01);
		ws_oli_ind              varchar2(01);
		ws_pers_details         varchar2(100);
		ws_desc                 varchar2(150);
		ws_rank                 varchar2(15);
		ws_pers_found           varchar2(01);
		ws_un_no                varchar2(10);
		ws_dummy                varchar2(01);
		ws_m_nsn                varchar2(16);
		proc_cfg_tab_no         number;
		proc_mec                varchar2(04);
		proc_mec_nsn            varchar2(16);
		proc_cfg_tab_desc       varchar2(200);
		proc_mec_desc           varchar2(200);
		proc_mec_nsn_desc       varchar2(200);
		proc_exist_corp_ind     varchar2(01);
		proc_err_ind            varchar2(01);
		proc_err_msg            varchar2(80);
		proc_me_niin            varchar2(13);
		wrk_nsn_desc            varchar2(200);
		wrk_err                 varchar2(1);
		wrk_msg                 varchar2(50);
		wrk_nsn                 varchar2(16);
		v_nsn                   varchar2(16);
		wrk_totals              varchar2(3);
		wrk_all_units           varchar2(3);
		ws_found                varchar2(1);
		ws_dep_active_ind       varchar2(1);
		ws_dep_closed_ind       varchar2(1);
		ws_act_rv_dte           date;
		ws_purch_price          number(15,4);
		ws_cnt                  number := 0;
		
		 cursor ser_data is
  	      select a.ser_no, 
  	             a.scin,
  	             a.sin,
  	             nvl(b.unit_holding,0) unit_holding,
  	             nvl(b.dept_holding,0) dept_holding,
  	             nvl(b.unit_located_at,0) unit_located_at,
  	             b.cond_cde,
  	             b.srv_status_cde,
  	             b.pers_located_at,
  	             b.nha_sin,
  	             b.nha_ser_no,
  	             c.nsc||'-'||substr(c.niin,1,2)||'-'||
  	             substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
  	             c.itm_type_cde,
  	             c.struc_lev_cde,
  	             c.agency_ser_cntrl_ind,
  	             d.itm_nme,
  	             e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
  	             e.me_desc  	             
  	        from ils.sss_entry_index a,
  	             ils.sss_er_core b,
  	             ils.item_data c,
  	             ils.names d,
  	             ils.me_master_rec e
  	       where a.sin_change_reas is null
  	         and a.sin_change_dte is null
  	         and b.scin = a.scin
  	         and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
  	               nvl(ws_unit_cde,0) > 0) or
  	               nvl(ws_unit_cde,0) = 0)
  	         and c.sin = a.sin
  	         and d.sin = c.sin
  	         and d.itm_nme_type = '1'
  	         and d.name_seq = '1'
  	         and e.sin (+) = a.sin
  	         and (e.me_life_cyc (+) != '08' and
                  e.me_rec_stat (+) not in ('C','X'))
           order by b.unit_holding, a.ser_no;
		
	BEGIN
		For rec1 in ser_data LOOP
         BEGIN     	 	
       	 Select scin,
       	        itm_purch_prc,
       	        heritage_ind,
       	        res_val,
       	        fair_val,
       	        current_val,
       	        accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        act_rv_dte,
       	        dep_active_ind,
       	 	      dep_closed_ind,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        close_reason
       	  into  ws_scin,
       	        ws_itm_purch_prc,
       	        ws_heritage_ind,
       	        ws_res_val,
       	        ws_fair_val,
       	        ws_current_val,
       	        ws_accum_improve_val,
       	        ws_accum_impair_val,
       	        ws_useful_lfe,
       	        ws_rem_useful_lfe,
       	        ws_ass_cat,
       	        ws_act_rv_dte,
     	 	        ws_dep_active_ind,
     	 	        ws_dep_closed_ind,
       	        ws_start_dep_dte,
       	        ws_lst_dep_fin_YYYYMM,
       	        ws_close_reason
       	  from  ils.lsst_asset_fin a
       	 where  rec1.scin = scin
       	   /*and  TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
   																  	  AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/
   						and  TRUNC(a.act_rv_dte) BETWEEN NVL(:key.scr_rec_dte_fr,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(:key.scr_rec_dte_to,SYSDATE);		
   						
   			
   			
   			EXCEPTION                         
         when no_data_found then
            ws_found := 'N';
--              goto nxt_rec;
              null;
   			
   				END;		
   					
   					 wrk_nsn := substr(rec1.nsn,1,4)||substr(rec1.nsn,6,2)||
                  substr(rec1.nsn,9,3)||substr(rec1.nsn,13,4);	
                  
                   ws_ser_no := rec1.ser_no;
                   
                   IF nvl(ws_unit,0) > 0 then
       	 	    BEGIN
       	 	      select eng_abbr_nme
       	 	        into ws_eng_abbr_nme
       	 	        from ils.unt
       	 	       where unit_cde = ws_unit;
       	 	      exception
       	 	        when no_data_found then
       	 	             ws_eng_abbr_nme := ws_unit ||' Unit not Found';
       	      END;
	END IF;
	
	                MESSAGE('NSN = '||wrk_nsn);
                  message('serno = '||ws_ser_no);
                  message('unit desc = '||ws_eng_abbr_nme);
                  message('nsn desc = '||wrk_nsn_desc);
	
	
	      PROC_NSN_DESC(wrk_nsn,
    	                wrk_nsn_desc,
       	              wrk_err,
       	              wrk_msg);
                  
                 
   						
   						
   						      -- BEGIN
           	 insert into ils.lsst_prt_asset_fin
       	 	     (user_id,
       	 	      dte_tme_stamp,
       	 	      unit_cde,
       	 	      nsn,
       	 	      ser_no,
       	 	      unit_desc,
       	 	      nsn_desc,
       	 	      me_cde,
       	 	      me_desc,
       	 	      gsb_unit_cde,
       	 	      gsb_desc,
       	 	      itm_purch_prc,
       	 	      heritage_ind,
       	 	      res_val,
       	 	      fair_val,
       	 	      current_val,
       	 	      accum_improve_val,
       	        accum_impair_val,
       	        useful_lfe,
       	        rem_useful_lfe,
       	        ass_cat,
       	        dep_sched,
       	        start_dep_dte,
       	        lst_dep_fin_YYYYMM,
       	        close_reason)
       	 	   values 
       	  	    (:global.ils_force_no,
                :key.wrk_dte,
       	 	      ws_unit,
       	 	      wrk_nsn,
       	 	      ws_ser_no,
       	 	      ws_eng_abbr_nme,
       	 	      wrk_nsn_desc,
       	 	      ws_mec,
       	 	      ws_me_descr,
       	 	      wrk_gsb_unit,
       	 	      wrk_gsb_desc,
       	 	      ws_itm_purch_prc,
       	 	      ws_heritage_ind,
       	 	      ws_res_val,
       	 	      ws_fair_val,
       	 	      ws_current_val,
       	 	      ws_accum_improve_val,
       	        ws_accum_impair_val,
       	        ws_useful_lfe,
       	        ws_rem_useful_lfe,
       	        ws_ass_cat,
       	        ws_dep_sched, 
       	        ws_start_dep_dte,
       	        ws_lst_dep_fin_YYYYMM,
       	        ws_close_reason);
       	        
       	        
       	         message('scin = '||ws_scin);
       	     message('itm_purch = '||ws_itm_purch_prc);
       	     message('ws_heritage_ind = '||ws_heritage_ind);
       	     message('ws_res_val = '||ws_res_val);   
       	     message('ws_fair_val = '||ws_fair_val);   
       	     message('ws_current_val = '||ws_current_val);   
       	     message('ws_accum_improve_val = '||ws_accum_improve_val);   
       	     message('ws_accum_impair_val = '||ws_accum_impair_val);    
       	     message('ws_useful_lfe = '||ws_useful_lfe);   
       	     message('ws_rem_useful_lfe = '||ws_rem_useful_lfe);   
       	     message('ws_ass_cat = '||ws_ass_cat);     
       	     message('ws_act_rv_dte = '||ws_act_rv_dte);   
     	 	     message('ws_dep_active_ind = '||ws_dep_active_ind);   
     	 	     message('ws_dep_closed_ind = '||ws_dep_closed_ind);   
       	     message('ws_start_dep_dte = '||ws_start_dep_dte);   
       	     message('ws_lst_dep_fin_YYYYMM = '||ws_lst_dep_fin_YYYYMM);   
       	     message('ws_close_reason = '||ws_close_reason);   		
       	        
       	        
       	        
       /*EXCEPTION
       	  when dup_val_on_index then
       	  null;*/
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);pause;
--         raise form_trigger_failure; */
       END LOOP;
      --END;
      END;
   END; 

Open in new window

this error has nothing to do with report am not yet calling the report yet just doing insert in form
I have created exception to handle no data