PKG_NKW_DNT.SPB
Source Package Body
Dynamic Trigger - Pacote principal
create or replace package body pkg_nkw_dnt
timestamp '2006-07-07:10:10:10'
is
------------------------------------------------------------------
-- 2006 DataPRO Developers - m@urelio
------------------------------------------------------------------
-- Version: 2.0.2
------------------------------------------------------------------
-- Collections, Records, Variables, Constants, Exceptions, Cursors
------------------------------------------------------------------
gv_dtgc_table pkg_nkw_env.gv_char%TYPE;
gv_lru_object pkg_nkw_env.gv_char%TYPE;
------------------------------------------------------------------
----------------------- Private Section --------------------------
------------------------------------------------------------------
------------------------------------------------------------------
-- SET_LABEL
------------------------------------------------------------------
procedure set_label
is
lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout ||
'PKG_NKW_DNT.SET_LABEL' || chr(10);
begin
if (gr_session.audsid is null) then
gr_session := pkg_nkw_app.get_session;
gv_session := '#' || chr(10) || '# Dynamic Trigger Generator' || chr(10) ||
'# 1998 Nulaya Knowledgements' || chr(10) || '#' || chr(10) ||
'# SID=' || gr_session.sid || ' SERIAL#=' || gr_session.serial# ||
' AUDSID=' || gr_session.audsid || ' USERID=' || gr_session.user# ||
'-' || gr_session.username || chr(10);
end if;
gv_label := gv_session || '# TABLE=' || gv_object || ' ACTION=' ||
gv_action || chr(10) || '#' || chr(10);
exception
when others then
raise_application_error(-20000, lv_scope || sqlerrm);
end set_label;
------------------------------------------------------------------
-- POP_DTGC
------------------------------------------------------------------
procedure pop_dtgc
is
lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout ||
'PKG_NKW_DNT.POP_DTGC' || chr(10);
begin
if (nvl(gv_object,'+') <> nvl(gv_dtgc_table,'-')) then
gr_dtgc := null;
begin
declare cursor get_cur is
select a.*
from nkw_dnt_dtgc a
where (a.table_name = upper(gv_object));
begin
if (not get_cur%ISOPEN) then
open get_cur;
end if;
fetch get_cur into gr_dtgc;
if (get_cur%ISOPEN) then
close get_cur;
end if;
exception
when others then
if (get_cur%ISOPEN) then
close get_cur;
end if;
raise;
end;
end;
gv_dtgc_table := upper(gv_object);
end if;
exception
when others then
raise_application_error(-20000, lv_scope || sqlerrm);
end pop_dtgc;
------------------------------------------------------------------
-- SET_ACTION
------------------------------------------------------------------
procedure set_action
is
lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout ||
'PKG_NKW_DNT.SET_ACTION' || chr(10);
begin
if (dbms_standard.inserting) then
gv_action := 'INSERT';
elsif (dbms_standard.updating) then
gv_action := 'UPDATE';
elsif (dbms_standard.deleting) then
gv_action := 'DELETE';
end if;
exception
when others then
raise_application_error(-20000, lv_scope || sqlerrm);
end set_action;
------------------------------------------------------------------
-- POP_COLUMNS
------------------------------------------------------------------
procedure pop_columns
is
lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout ||
'PKG_NKW_DNT.POP_COLUMNS' || chr(10);
begin
gt_old_data.delete;
gt_new_data.delete;
gt_data := pkg_nkw_obj.get_columns(gv_object);
gt_old_data := gt_data;
gt_new_data := gt_data;
exception
when others then
raise_application_error(-20000, lv_scope || sqlerrm);
end pop_columns;
------------------------------------------------------------------
-- CHECK_DNT
------------------------------------------------------------------
procedure check_dnt
is
lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout ||
'PKG_NKW_DNT.CHECK_DNT' || chr(10);
begin
if (nvl(gt_old_data.count,0) = 0) then
raise_application_error(-20000, lv_scope || 'None DNT initialization.|');
end if;
end check_dnt;
------------------------------------------------------------------
-- BIND_DATATYPE
------------------------------------------------------------------
procedure bind_datatype(pv_column in pkg_nkw_env.gv_char%TYPE,
pv_data_type in pkg_nkw_env.gv_char%TYPE,
pv_old_date in date := null,
pv_new_date in date := null,
pv_old_number in number := null,
pv_new_number in number := null,
pv_old_varchar2 in pkg_nkw_env.gv_char%TYPE := null,
pv_new_varchar2 in pkg_nkw_env.gv_char%TYPE := null)
is
lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout ||
'PKG_NKW_DNT.BIND_DATATYPE' || chr(10);
lv_col# sys.col$.col#%TYPE;
begin
check_dnt;
if (pv_column is null) then
raise_application_error(-20000, lv_scope || 'Missing column name.|');
end if;
lv_col# := pkg_nkw_obj.index_of(gt_old_data, pv_column);
if (nvl(lv_col#,0) = 0) then
raise_application_error(-20000, lv_scope ||
'Column ' || upper(gv_object) || '.' || upper(pv_column) || ' does not exists.|');
end if;
begin
if (pv_data_type = 'DATE') then
gt_old_data(lv_col#).date_value := pv_old_date;
gt_new_data(lv_col#).date_value := pv_new_date;
elsif (pv_data_type = 'NUMBER') then
gt_old_data(lv_col#).number_value := pv_old_number;
gt_new_data(lv_col#).number_value := pv_new_number;
elsif (pv_data_type = 'VARCHAR2') then
gt_old_data(lv_col#).varchar2_value := pv_old_varchar2;
gt_new_data(lv_col#).varchar2_value := pv_new_varchar2;
end if;
exception
when others then
raise_application_error(-20000, lv_scope || sqlerrm);
end;
exception
when others then raise;
end bind_datatype;
------------------------------------------------------------------
------------------------ Public Section --------------------------
------------------------------------------------------------------
------------------------------------------------------------------
-- INIT_DNT
------------------------------------------------------------------
procedure init_dnt(fv_object in pkg_nkw_env.gv_char%TYPE)
is
lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout ||
'PKG_NKW_DNT.INIT_DRM' || chr(10);
begin
if (not pkg_nkw_sys.is_table(fv_object)) then
raise_application_error(-20000, lv_scope ||
'Table or View ' || upper(fv_object) || ' does not exists.|');
end if;
set_action;
if (upper(fv_object) <> nvl(gv_lru_object,':(')) then
gv_object := upper(fv_object);
pop_columns;
gv_lru_object := upper(fv_object);
end if;
exception
when others then
raise_application_error(-20000, lv_scope || sqlerrm);
end init_dnt;
------------------------------------------------------------------
-- BIND_COLUMN
------------------------------------------------------------------
procedure bind_column(fv_column in pkg_nkw_env.gv_char%TYPE,
fv_old in date,
fv_new in date)
is
begin
bind_datatype(pv_column => fv_column,
pv_data_type => 'DATE',
pv_old_date => fv_old,
pv_new_date => fv_new);
exception
when others then raise;
end bind_column;
------------------------------------------------------------------
procedure bind_column(fv_column in pkg_nkw_env.gv_char%TYPE,
fv_old in number,
fv_new in number)
is
begin
bind_datatype(pv_column => fv_column,
pv_data_type => 'NUMBER',
pv_old_number => fv_old,
pv_new_number => fv_new);
exception
when others then raise;
end bind_column;
------------------------------------------------------------------
procedure bind_column(fv_column in pkg_nkw_env.gv_char%TYPE,
fv_old in pkg_nkw_env.gv_char%TYPE,
fv_new in pkg_nkw_env.gv_char%TYPE)
is
begin
bind_datatype(pv_column => fv_column,
pv_data_type => 'VARCHAR2',
pv_old_varchar2 => fv_old,
pv_new_varchar2 => fv_new);
exception
when others then raise;
end bind_column;
------------------------------------------------------------------
-- PARSE_ACTION
------------------------------------------------------------------
procedure parse_action
is
begin
if (not pkg_nkw_app.appkey_on) then
raise_application_error(-20000, 'Sessão não habilitada para manipulações de dados.');
end if;
set_action;
set_label;
pop_dtgc;
if (gv_action in ('INSERT','UPDATE')) then
if (nvl(gr_dtgc.dvies,'N') = 'Y') then
pkg_nkw_dnt_dvies.parse_action;
end if;
if (nvl(gr_dtgc.ccvies,'N') = 'Y') then
pkg_nkw_dnt_ccvies.parse_action;
end if;
pkg_nkw_dnt_drmies.parse_action(gt_new_data, gv_action);
if (nvl(gr_dtgc.eppc,'N') = 'Y') then
pkg_nkw_dnt_eppc.parse_action;
end if;
if (nvl(gr_dtgc.eapb,'N') = 'Y') then
pkg_nkw_dnt_eapb.parse_action;
end if;
if (nvl(gr_dtgc.logies,'N') = 'Y') then
pkg_nkw_dnt_logies.parse_action;
end if;
elsif (gv_action = 'DELETE') then
pkg_nkw_dnt_drmies.parse_action(gt_old_data, gv_action);
if (nvl(gr_dtgc.eppc,'N') = 'Y') then
pkg_nkw_dnt_eppc.parse_action;
end if;
if (nvl(gr_dtgc.eapb,'N') = 'Y') then
pkg_nkw_dnt_eapb.parse_action;
end if;
if (nvl(gr_dtgc.logies,'N') = 'Y') then
pkg_nkw_dnt_logies.parse_action;
end if;
end if;
exception
when others then raise;
end parse_action;
------------------------------------------------------------------
-- SET_NEW
------------------------------------------------------------------
procedure set_new(fv_column in pkg_nkw_env.gv_char%TYPE,
fv_value out date)
is
lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout ||
'PKG_NKW_DNT.SET_NEW' || chr(10);
lv_col# sys.col$.col#%TYPE;
begin
if (gv_action in ('INSERT','UPDATE')) then
lv_col# := pkg_nkw_obj.index_of(gt_new_data, fv_column);
fv_value := gt_new_data(lv_col#).date_value;
end if;
exception
when others then
raise_application_error(-20000, lv_scope || sqlerrm);
end set_new;
------------------------------------------------------------------
procedure set_new(fv_column in pkg_nkw_env.gv_char%TYPE,
fv_value out number)
is
lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout ||
'PKG_NKW_DNT.SET_NEW' || chr(10);
lv_col# sys.col$.col#%TYPE;
begin
if (gv_action in ('INSERT','UPDATE')) then
lv_col# := pkg_nkw_obj.index_of(gt_new_data, fv_column);
fv_value := gt_new_data(lv_col#).number_value;
end if;
exception
when others then
raise_application_error(-20000, lv_scope || sqlerrm);
end set_new;
------------------------------------------------------------------
procedure set_new(fv_column in pkg_nkw_env.gv_char%TYPE,
fv_value out pkg_nkw_env.gv_char%TYPE)
is
lv_scope constant pkg_nkw_env.gv_shout%TYPE := pkg_nkw_env.gv_shout ||
'PKG_NKW_DNT.SET_NEW' || chr(10);
lv_col# sys.col$.col#%TYPE;
begin
if (gv_action in ('INSERT','UPDATE')) then
lv_col# := pkg_nkw_obj.index_of(gt_new_data, fv_column);
fv_value := gt_new_data(lv_col#).varchar2_value;
end if;
exception
when others then
raise_application_error(-20000, lv_scope || sqlerrm);
end set_new;
end pkg_nkw_dnt;
|
