create or replace package body pkg_nkw_dvp
timestamp '2006-07-07:10:10:10'
is
   ------------------------------------------------------------------
   -- 2006 DataPRO Developers - m@urelio
   ------------------------------------------------------------------
   -- Version: 2.0.2
   ------------------------------------------------------------------
   -- Collections, Records, Variables, Constants, Exceptions, Cursors
   ------------------------------------------------------------------
   ------------------------------------------------------------------
   ----------------------- Private Section --------------------------
   ------------------------------------------------------------------
   ------------------------------------------------------------------
   -- BUILD_DNT
   ------------------------------------------------------------------
   procedure build_dnt
   is
      i binary_integer;
   begin
      i := gt_data.first;
      if (i is not null) then
	 gv_stmt := 'create or replace trigger dnt_' || lower(gt_data(i).table_name) || chr(10) ||
		    'before insert or update or delete on ' || lower(gt_data(i).table_name) || chr(10) ||
		    'for each row' || chr(10) ||
		    'begin' || chr(10) ||
		    '	------------------------------------------------------------------' || chr(10) ||
		    '	-- 2006 DataPRO Developers' || chr(10) ||
		    '	------------------------------------------------------------------' || chr(10) ||
		    '	-- Dynamic Trigger Generator' || chr(10) ||
		    '	------------------------------------------------------------------' || chr(10) ||
		    '	pkg_nkw_dnt.init_dnt(''' || upper(gt_data(i).table_name) || ''');' || chr(10);
      end if;
      << bind_loop >>
      while (i is not null) loop
	 if (upper(gt_data(i).data_type) in ('DATE','NUMBER','VARCHAR2')) then
	    gv_stmt := gv_stmt ||
		    '	pkg_nkw_dnt.bind_column(''' || lower(gt_data(i).column_name) || ''', :old.' ||
		    lower(gt_data(i).column_name) || ', :new.' || lower(gt_data(i).column_name) || ');' || chr(10);
	 else
	    gv_stmt := gv_stmt ||
		    '	--pkg_nkw_dnt.bind_column(''' || lower(gt_data(i).column_name) || ''', :old.' ||
		    lower(gt_data(i).column_name) || ', :new.' || lower(gt_data(i).column_name) || ');' || chr(10);
	 end if;
	 i := gt_data.next(i);
      end loop bind_loop;
      i := gt_data.first;
      if (i is not null) then
	 gv_stmt := gv_stmt ||
		    '	pkg_nkw_dnt.parse_action;' || chr(10) ||
		    '	if (not dbms_standard.deleting) then' || chr(10);
      end if;
      << set_loop >>
      while (i is not null) loop
	 if (upper(gt_data(i).data_type) in ('DATE','NUMBER','VARCHAR2')) then
	    gv_stmt := gv_stmt ||
		      '      pkg_nkw_dnt.set_new(''' || lower(gt_data(i).column_name) ||
					 ''', :new.' || lower(gt_data(i).column_name) || ');' || chr(10);
	 else
	    gv_stmt := gv_stmt ||
		      '      --pkg_nkw_dnt.set_new(''' || lower(gt_data(i).column_name) ||
					 ''', :new.' || lower(gt_data(i).column_name) || ');' || chr(10);
	 end if;
	 i := gt_data.next(i);
      end loop set_loop;
      i := gt_data.first;
      if (i is not null) then
	 gv_stmt := gv_stmt ||
		    '	end if;' || chr(10) ||
		    '	exception' || chr(10) ||
		    '	   when others then raise;' || chr(10) ||
		    'end;' || chr(10);
	 pkg_nkw_rpl.run_code(gv_stmt);
      end if;
      exception
	 when others then raise;
   end build_dnt;
   ------------------------------------------------------------------
   ------------------------ Public Section --------------------------
   ------------------------------------------------------------------
   ------------------------------------------------------------------
   -- POP_DNT
   ------------------------------------------------------------------
   procedure pop_dnt(fv_object in pkg_nkw_env.gv_char%TYPE := null)
   is
   begin
      begin
	 declare cursor pop_cur is
		 select a.obj#, a.name
		   from sys.obj$ a
		  where (a.name like upper(nvl(fv_object,'VI%')))
		    and (a.owner# = pkg_nkw_sys.get_nkid)
		    and (a.type# = 2);
		pop_row pop_cur%ROWTYPE;
	 begin
	    if (not pop_cur%ISOPEN) then
	       open pop_cur;
	    end if;
	    << pop_loop >>
	    loop
	       fetch pop_cur into pop_row;
	       exit pop_loop when pop_cur%NOTFOUND;
	       pkg_nkw_rpl.run_code('lock table ' || pop_row.name || ' in exclusive mode nowait');
	       gt_data.delete;
	       gt_data := pkg_nkw_obj.get_columns(pop_row.name);
	       build_dnt;
	    end loop pop_loop;
	    if (pop_cur%ISOPEN) then
	       close pop_cur;
	    end if;
	 end;
      end;
      exception
	 when others then raise;
   end pop_dnt;
   ------------------------------------------------------------------
   -- BUILD_VIEW
   ------------------------------------------------------------------
   procedure build_view(ft_view in gt_view%TYPE)
   is
      i binary_integer;
   begin
      i := ft_view.first;
      while (i is not null) loop
	 pkg_nkw_rpl.run_code('create or replace view ' || ft_view(i).view_name || ' as ' || ft_view(i).text);
	 i := ft_view.next(i);
      end loop;
      exception
	 when others then raise;
   end build_view;
   ------------------------------------------------------------------
   -- GET_VIEWS
   ------------------------------------------------------------------
   procedure get_views
   is
   begin
      delete from nkw_viw;
      begin
	 declare cursor get_cur is
		 select a.view_name, a.text
		   from user_views a
		  where (a.view_name like 'VIW%');
		get_row get_cur%ROWTYPE;
	 begin
	    if (not get_cur%ISOPEN) then
	       open get_cur;
	    end if;
	    << get_loop >>
	    loop
	       fetch get_cur into get_row;
	       exit get_loop when (get_cur%NOTFOUND);
	       insert into nkw_viw(view_name, text)
	       values(get_row.view_name, get_row.text);
	    end loop get_loop;
	    if (get_cur%ISOPEN) then
	       close get_cur;
	    end if;
	 end;
      end;
      exception
	 when others then raise;
   end get_views;
   ------------------------------------------------------------------
   -- POP_VIEWS
   ------------------------------------------------------------------
   procedure pop_views(fv_name in varchar2 := null)
   is
   begin
      begin
	 declare cursor get_cur is
		 select a.view_name, a.text
		   from nkw_viw a
		  where (a.view_name like upper(nvl(fv_name,'%')));
		get_row get_cur%ROWTYPE;
	 begin
	    if (not get_cur%ISOPEN) then
	       open get_cur;
	    end if;
	    << get_loop >>
	    loop
	       fetch get_cur into get_row;
	       exit get_loop when (get_cur%NOTFOUND);
	       begin
		  pkg_nkw_rpl.run_code('create or replace view ' || get_row.view_name ||
				       ' as ' || get_row.text);
		  exception
		     when others then
			dbms_output.put_line('Error Compiling: ' || get_row.view_name || chr(10) || sqlerrm);
	       end;
	    end loop get_loop;
	    if (get_cur%ISOPEN) then
	       close get_cur;
	    end if;
	 end;
      end;
      exception
	 when others then raise;
   end pop_views;
end pkg_nkw_dvp;