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; / show errors