---------------------------------------------------------- -- Export file for user CONTAFIN_ORACLE -- -- Created by marius.atanasiu on 10/27/2005, 4:01:16 PM -- ---------------------------------------------------------- spool drepturi_utilizatori.log prompt prompt Creating table DEF_GRUP prompt ======================= prompt create table DEF_GRUP ( ID_GRUP NUMBER(5) not null, GRUP VARCHAR2(50), STERS NUMBER(1) default 0 not null, ID_UTILOP NUMBER(5) not null, DATAORA DATE not null, NR_GRUP NUMBER(5), ID_TATA NUMBER(5) ) tablespace ROA pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table DEF_GRUP add constraint PK_DFG primary key (ID_GRUP) using index tablespace ROA pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table DEF_GRUP add constraint UK_DFG01 unique (GRUP, STERS) using index tablespace ROA pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table DEF_GRUP add constraint FK_DFG01 foreign key (ID_UTILOP) references UTILIZATORI (ID_UTIL); prompt prompt Creating table DEF_GRUP_DREPT prompt ============================= prompt create table DEF_GRUP_DREPT ( ID_GRUP NUMBER(5) not null, ID_PROGRAM NUMBER(5) not null, ID_DGD NUMBER(5) not null, ID_UTILOP NUMBER(5) not null, DATAORA DATE not null, STERS NUMBER(1) default 0 not null ) tablespace ROA pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table DEF_GRUP_DREPT add constraint PK_DGD primary key (ID_DGD) using index tablespace ROA pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table DEF_GRUP_DREPT add constraint FK_DGD01 foreign key (ID_GRUP) references DEF_GRUP (ID_GRUP); alter table DEF_GRUP_DREPT add constraint FK_DGD02 foreign key (ID_PROGRAM) references NOM_PROGRAME (ID_PROGRAM); alter table DEF_GRUP_DREPT add constraint FK_DGD03 foreign key (ID_UTILOP) references UTILIZATORI (ID_UTIL); prompt prompt Creating table DEF_OBIECTE prompt ========================== prompt create table DEF_OBIECTE ( ID_OBIECT NUMBER(10) not null, ID_PROGRAM NUMBER(2) not null, DESCRIERE VARCHAR2(100) not null, ID_TATA NUMBER(10) not null, COD VARCHAR2(3) not null, ID_UTILOP NUMBER(10), DATAORA DATE, STERS NUMBER(1) default 0 ) tablespace ROA pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table DEF_OBIECTE add constraint PK_DO primary key (ID_OBIECT) using index tablespace ROA pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table DEF_OBIECTE add constraint FK_DO01 foreign key (ID_PROGRAM) references NOM_PROGRAME (ID_PROGRAM); alter table DEF_OBIECTE add constraint FK_DO02 foreign key (ID_UTILOP) references UTILIZATORI (ID_UTIL); prompt prompt Creating table DEF_GRUP_DREPT_OBIECTE prompt ===================================== prompt create table DEF_GRUP_DREPT_OBIECTE ( ID_DGDO NUMBER(10) not null, ID_OBIECT NUMBER(10) not null, ID_GRUP NUMBER(10) not null, ID_UTILOP NUMBER(10) not null, DATAORA DATE not null, STERS NUMBER(1) default 0 not null ) tablespace ROA pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table DEF_GRUP_DREPT_OBIECTE add constraint PK_DGDO primary key (ID_DGDO) using index tablespace ROA pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table DEF_GRUP_DREPT_OBIECTE add constraint FK_DGDO01 foreign key (ID_OBIECT) references DEF_OBIECTE (ID_OBIECT); alter table DEF_GRUP_DREPT_OBIECTE add constraint FK_DGDO02 foreign key (ID_GRUP) references DEF_GRUP (ID_GRUP); alter table DEF_GRUP_DREPT_OBIECTE add constraint FK_DGDO03 foreign key (ID_UTILOP) references UTILIZATORI (ID_UTIL); prompt prompt Creating table DEF_PROGRAME prompt =========================== prompt create table DEF_PROGRAME ( ID_PROG NUMBER(5) not null, DIRECTOR VARCHAR2(30), EXPLICATIE VARCHAR2(150), INSTALAT NUMBER(1), ORDINE NUMBER(2), STERS NUMBER(1) default 0 not null, IDE_PROGRAM NUMBER(2) default 0 not null, ID_UTILOP NUMBER(10) not null, DATAORA DATE default SYSDATE not null ) tablespace ROA pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table DEF_PROGRAME add constraint PK_DEF_PROGRAME primary key (ID_PROG) using index tablespace ROA pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table DEF_PROGRAME add constraint FK_DP01 foreign key (ID_UTILOP) references UTILIZATORI (ID_UTIL); alter table DEF_PROGRAME add constraint FK_DP02 foreign key (IDE_PROGRAM) references nom_programe (ID_PROGRAM); prompt prompt Creating table DEF_UTIL_GRUP prompt ============================ prompt create table DEF_UTIL_GRUP ( ID_DUG NUMBER(10) not null, ID_FIRMA NUMBER(10) not null, ID_GRUP NUMBER(10) not null, ID_UTIL NUMBER(10) not null, DATAORA DATE not null, STERS NUMBER(1) default 0 not null, ID_UTILOP NUMBER(10) not null ) tablespace ROA pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table DEF_UTIL_GRUP add constraint PK_DUG primary key (ID_DUG) using index tablespace ROA pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table DEF_UTIL_GRUP add constraint FK_DUG01 foreign key (ID_GRUP) references DEF_GRUP (ID_GRUP); alter table DEF_UTIL_GRUP add constraint FK_DUG02 foreign key (ID_UTIL) references UTILIZATORI (ID_UTIL); alter table DEF_UTIL_GRUP add constraint FK_DUG03 foreign key (ID_FIRMA) references NOM_FIRME (ID_FIRMA); alter table DEF_UTIL_GRUP add constraint FK_DUG04 foreign key (ID_UTILOP) references UTILIZATORI (ID_UTIL); prompt prompt Creating sequence SEQ_DEF_GDO prompt ============================= prompt create sequence SEQ_DEF_GDO minvalue 1 maxvalue 999999999999999999999999999 start with 1 increment by 1 nocache; prompt prompt Creating sequence SEQ_DEF_GRUP prompt ============================== prompt create sequence SEQ_DEF_GRUP minvalue 1 maxvalue 999999999999999999999999999 start with 1 increment by 1 nocache; prompt prompt Creating sequence SEQ_DEF_GRUP_DREPT prompt ==================================== prompt create sequence SEQ_DEF_GRUP_DREPT minvalue 0 maxvalue 999999999999999999999999999 start with 0 increment by 1 nocache; prompt prompt Creating sequence SEQ_DEF_OBIECTE prompt ================================= prompt create sequence SEQ_DEF_OBIECTE minvalue 1 maxvalue 999999999999999999999999999 start with 2853 increment by 1 nocache; prompt prompt Creating sequence SEQ_DEF_PROGRAME prompt ================================== prompt create sequence SEQ_DEF_PROGRAME minvalue 1 maxvalue 999999999999999999999999999 start with 12 increment by 1 nocache; prompt prompt Creating sequence SEQ_DEF_UTIL_GRUP prompt =================================== prompt create sequence SEQ_DEF_UTIL_GRUP minvalue 0 maxvalue 999999999999999999999999999 start with 0 increment by 1 nocache; prompt prompt Creating view VDEF_GRUP prompt ======================= prompt create or replace view vdef_grup as select id_grup,grup,sys_connect_by_path(grup,'\') as tata,id_tata,level as nivel from def_grup where sters=0 start with id_tata=-1 connect by prior id_grup=id_tata / prompt prompt Creating view VDEF_GRUP_DREPT prompt ============================= prompt create or replace view vdef_grup_drept as select a.id_grup,a.id_program,a.id_dgd,b.denumire,c.grup from def_grup_drept a left join nom_programe b on a.id_program=b.id_program left join def_grup c on a.id_grup=c.id_grup where a.sters=0 and c.sters=0 / prompt prompt Creating view VDEF_GRUP_OBIECTE prompt =============================== prompt create or replace view vdef_grup_obiecte as select id_dgdo,id_obiect,id_grup from def_grup_drept_obiecte where sters=0 / prompt prompt Creating view VDEF_OBIECTE prompt ========================== prompt create or replace view vdef_obiecte as select id_obiect,descriere,sys_connect_by_path(cod,'/') as cale, replace(sys_connect_by_path(cod,'/'),'/','') as cheie,id_program, level as nivel,connect_by_isleaf as frunza from def_obiecte where sters=0 start with id_tata=-1 connect by prior id_obiect=id_tata order by cheie / prompt prompt Creating view VDEF_GRUP_OBIECTE_PROG prompt ==================================== prompt create or replace view vdef_grup_obiecte_prog as select a.id_grup,b.id_program,b.cheie from vdef_grup_obiecte a left join vdef_obiecte b on a.id_obiect=b.id_obiect / prompt prompt Creating view VDEF_UTIL_GRUP prompt ============================ prompt create or replace view vdef_util_grup as select a.id_dug,a.id_firma,a.id_util,a.id_grup,b.grup,c.firma,d.utilizator from def_util_grup a left join def_grup b on a.id_grup=b.id_grup left join nom_firme c on a.id_firma=c.id_firma left join utilizatori d on a.id_util=d.id_util where a.sters=0 and b.sters=0 and d.sters=0 / prompt prompt Creating view VDEF_ORGANIGRAMA_FIRME_GRUP prompt ========================================= prompt create or replace view vdef_organigrama_firme_grup as select c.tip,c.id,c.denumire,c.id_tata,sys_connect_by_path(substr(c.denumire,1,2)||id,'/') as cheie, connect_by_isleaf as frunza,level as nivel from (select 0 as tip,a.id_firma as id,a.firma as denumire,-1 as id_tata from v_nom_firme a union all select distinct 1 as tip,b.id_grup+500 as id,b.grup as denumire,b.id_firma as id_tata from vdef_util_grup b) c start with c.id_tata=-1 connect by prior c.id=c.id_tata order by nivel,tip,denumire / prompt prompt Creating view VDEF_ORGANIGRAMA_FIRME_UTIL prompt ========================================= prompt create or replace view vdef_organigrama_firme_util as select c.tip,c.id,c.denumire,c.id_tata,sys_connect_by_path(substr(c.denumire,1,2)||id,'/') as cheie, connect_by_isleaf as frunza,level as nivel from (select 0 as tip,a.id_firma as id,a.firma as denumire,-1 as id_tata from v_nom_firme a union all select distinct 1 as tip,b.id_util+500 as id,b.utilizator as denumire,b.id_firma as id_tata from vdef_util_grup b) c start with c.id_tata=-1 connect by prior c.id=c.id_tata order by nivel,tip,denumire / prompt prompt Creating view VDEF_ORGANIGRAMA_GRUPURI prompt ====================================== prompt create or replace view vdef_organigrama_grupuri as select c.tip,c.id_grup,c.grup as denumire,c.id_tata,sys_connect_by_path(substr(c.grup,1,2)||id_grup,'/') as cheie, connect_by_isleaf as frunza,level as nivel from (select 0 as tip,a.id_grup,a.grup,a.id_tata from def_grup a where a.sters=0 union all select distinct 1 as tip,b.id_util+500 as id_grup,b.utilizator as grup,b.id_grup as id_tata from vdef_util_grup b) c start with c.id_tata=-1 connect by prior c.id_grup=c.id_tata order by nivel,tip,denumire / prompt prompt Creating view VDEF_PROGRAME prompt =========================== prompt create or replace view vdef_programe as select a.id_prog,b.denumire as nume,a.director,a.explicatie,a.instalat,a.ordine,a.ide_program from def_programe a left join vnom_programe b on a.ide_program=b.id_program where a.sters = 0 / prompt prompt Creating view VDEF_ORGANIGRAMA_PROGRAME_GRUP prompt ============================================ prompt create or replace view vdef_organigrama_programe_grup as select c.tip,c.id,c.denumire,c.id_tata,sys_connect_by_path(substr(c.denumire,1,2)||id,'/') as cheie, connect_by_isleaf as frunza,level as nivel from (select distinct 0 as tip,a.ide_program as id,a.explicatie as denumire,-1 as id_tata from vdef_programe a union all select distinct 1 as tip,b.id_grup+500 as id,b.grup as denumire,b.id_program as id_tata from vdef_grup_drept b) c start with c.id_tata=-1 connect by prior c.id=c.id_tata order by nivel,tip,denumire / prompt prompt Creating view VDEF_ORGANIGRAMA_PROGRAME_UTIL prompt ============================================ prompt create or replace view vdef_organigrama_programe_util as select c.tip,c.id,c.denumire,c.id_tata,sys_connect_by_path(substr(c.denumire,1,2)||id,'/') as cheie, connect_by_isleaf as frunza,level as nivel from (select distinct 0 as tip,a.ide_program as id,a.explicatie as denumire,-1 as id_tata from vdef_programe a union all select distinct 1 as tip,b1.id_util+500 as id,b1.utilizator as denumire,b.id_program as id_tata from vdef_grup_drept b join vdef_util_grup b1 on b.id_grup=b1.id_grup) c start with c.id_tata=-1 connect by prior c.id=c.id_tata order by nivel,tip,denumire / prompt prompt Creating view VDEF_ORGANIGRAMA_PROG_FIRME prompt ========================================= prompt create or replace view vdef_organigrama_prog_firme as select c.tip,c.id,c.denumire,c.id_tata,sys_connect_by_path(substr(c.denumire,1,2)||id,'/') as cheie, connect_by_isleaf as frunza,level as nivel from (select distinct 0 as tip,a.ide_program as id,a.explicatie as denumire,-1 as id_tata from vdef_programe a union all select distinct 1 as tip,b1.id_firma+500 as id,b1.firma as denumire,b.id_program as id_tata from vdef_grup_drept b join vdef_util_grup b1 on b.id_grup=b1.id_grup) c start with c.id_tata=-1 connect by prior c.id=c.id_tata order by nivel,tip,denumire / prompt prompt Creating view VDEF_ORGANIGRAMA_UTIL_FIRME prompt ========================================= prompt create or replace view vdef_organigrama_util_firme as select c.tip,c.id,c.denumire,c.id_tata,sys_connect_by_path(substr(c.denumire,1,2)||id,'/') as cheie, connect_by_isleaf as frunza,level as nivel from (select 0 as tip,a.id_util as id,a.utilizator as denumire,-1 as id_tata from vutilizatori a where a.inactiv=0 union all select distinct 1 as tip,b.id_firma+500 as id,b.firma as denumire,b.id_util as id_tata from vdef_util_grup b) c start with c.id_tata=-1 connect by prior c.id=c.id_tata order by nivel,tip,denumire / prompt prompt Creating view VDEF_ORGANIGRAMA_UTIL_GRUP prompt ======================================== prompt create or replace view vdef_organigrama_util_grup as select c.tip,c.id,c.denumire,c.id_tata,sys_connect_by_path(substr(c.denumire,1,2)||id,'/') as cheie, connect_by_isleaf as frunza,level as nivel from (select 0 as tip,a.id_util as id,a.utilizator as denumire,-1 as id_tata from vutilizatori a where a.inactiv=0 union all select distinct 1 as tip,b.id_grup+500 as id,b.grup as denumire,b.id_util as id_tata from vdef_util_grup b) c start with c.id_tata=-1 connect by prior c.id=c.id_tata order by nivel,tip,denumire / prompt prompt Creating view VDEF_UTIL_FIRME prompt ============================= prompt create or replace view vdef_util_firme as select a.id_program,b.id_util,b.id_firma from def_grup_drept a left join def_util_grup b on a.id_grup=b.id_grup where a.sters=0 and b.sters=0 / prompt prompt Creating view VDEF_UTIL_GRUP_OBIECTE prompt ==================================== prompt create or replace view vdef_util_grup_obiecte as select a.id_util,b.id_grup,b.id_obiect,c.cheie,c.id_program from utilizatori a left join def_grup_drept_obiecte b on a.id_grup=b.id_grup left join vdef_obiecte c on b.id_obiect=c.id_obiect where a.sters=0 and b.sters=0 / prompt prompt Creating view VDEF_UTIL_OBIECTE prompt =============================== prompt create or replace view vdef_util_obiecte as select a.id_firma,a.id_util,c.id_program,c.cheie from def_util_grup a left join def_grup_drept_obiecte b on a.id_grup=b.id_grup left join vdef_obiecte c on b.id_obiect=c.id_obiect where a.sters=0 and b.sters=0 / prompt prompt Creating view VDEF_UTIL_PROGRAME prompt ================================ prompt create or replace view vdef_util_programe as select distinct a.id_util,b.id_program,c.nume,c.director,c.explicatie,c.ordine from def_util_grup a left join def_grup_drept b on a.id_grup=b.id_grup left join vdef_programe c on b.id_program=c.ide_program where a.sters=0 and b.sters=0 and c.instalat=1 / prompt prompt Creating package PACK_DREPTURI prompt ============================== prompt create or replace package PACK_DREPTURI is -- Author : MARIUS.ATANASIU -- Created : 10/7/2005 10:54:27 AM -- Purpose : -- Public type declarations procedure utilizatoriinsproc(V_UTILIZATOR IN VARCHAR2, V_PAROLA IN VARCHAR2, V_INACTIV IN NUMBER, V_ID_UTILAD IN NUMBER); procedure utilizatorimodproc(V_UTILIZATOR IN VARCHAR2, V_PAROLA IN VARCHAR2, V_INACTIV IN NUMBER, V_ID_UTIL IN NUMBER, V_ID_UTILMOD IN NUMBER); procedure utilizatoridelproc(V_ID_UTIL IN NUMBER, V_ID_UTILS IN NUMBER); procedure parolamodproc(V_PAROLA IN VARCHAR2, V_ID_UTIL IN NUMBER); procedure programeinsproc(V_IDE_PROGRAM IN VARCHAR2, V_CALE IN VARCHAR2, V_TITLU IN VARCHAR2, V_INSTALAT IN NUMBER, V_ORDINE IN NUMBER, V_ID_UTILAD IN NUMBER); procedure programemodproc(V_ID_PROGRAM IN NUMBER, V_CALE IN VARCHAR2, V_TITLU IN VARCHAR2, V_INSTALAT IN NUMBER, V_ORDINE IN NUMBER, V_ID_UTILMOD IN NUMBER); procedure programedelproc(V_ID_PROGRAM IN NUMBER, V_ID_UTILS IN NUMBER); procedure firmeinsproc(V_FIRMA IN VARCHAR2, V_FSCURT IN VARCHAR2, V_COD_FISCAL IN VARCHAR2, V_REG_COMERT IN VARCHAR2, V_BANCA1 IN VARCHAR2, V_CONT_BANCA1 IN VARCHAR2, V_BANCA2 IN VARCHAR2, V_CONT_BANCA2 IN VARCHAR2, V_BANCA3 IN VARCHAR2, V_CONT_BANCA3 IN VARCHAR2, V_ADRESA IN VARCHAR2, V_COD_FIRMA IN VARCHAR2, V_PERS_JUR IN NUMBER, V_CODANG IN NUMBER, V_NUME IN VARCHAR2, V_PRENUME IN VARCHAR2, V_TELEFON1 IN VARCHAR2, V_TELEFON2 IN VARCHAR2, V_TELEFON3 IN VARCHAR2, V_FAX IN VARCHAR2, V_EMAIL IN VARCHAR2, V_OASP IN VARCHAR2, V_CAP_SOC_VAR IN NUMBER, V_CAP_SOC_SUB IN NUMBER, V_PUNCT_LUCRU IN VARCHAR2, V_CAEN IN VARCHAR2, V_SCHEMA IN VARCHAR2, V_ID_MAMA IN NUMBER, V_MAMA IN VARCHAR2, V_SUCURSALA IN VARCHAR2, V_ID_LOC IN NUMBER, V_ID_UTILAD IN NUMBER); procedure firmemodproc(V_ID_FIRMA IN NUMBER, V_FIRMA IN VARCHAR2, V_FSCURT IN VARCHAR2, V_COD_FISCAL IN VARCHAR2, V_REG_COMERT IN VARCHAR2, V_BANCA1 IN VARCHAR2, V_CONT_BANCA1 IN VARCHAR2, V_BANCA2 IN VARCHAR2, V_CONT_BANCA2 IN VARCHAR2, V_BANCA3 IN VARCHAR2, V_CONT_BANCA3 IN VARCHAR2, V_ADRESA IN VARCHAR2, V_COD_FIRMA IN VARCHAR2, V_PERS_JUR IN NUMBER, V_CODANG IN NUMBER, V_NUME IN VARCHAR2, V_PRENUME IN VARCHAR2, V_TELEFON1 IN VARCHAR2, V_TELEFON2 IN VARCHAR2, V_TELEFON3 IN VARCHAR2, V_FAX IN VARCHAR2, V_EMAIL IN VARCHAR2, V_OASP IN VARCHAR2, V_CAP_SOC_VAR IN NUMBER, V_CAP_SOC_SUB IN NUMBER, V_PUNCT_LUCRU IN VARCHAR2, V_CAEN IN VARCHAR2, V_SCHEMA IN VARCHAR2, V_ID_MAMA IN NUMBER, V_MAMA IN VARCHAR2, V_SUCURSALA IN VARCHAR2, V_ID_LOC IN NUMBER, V_ID_UTILMOD IN NUMBER); procedure firmedelproc(V_ID_FIRMA IN VARCHAR2, V_ID_UTILS IN NUMBER); procedure grupinsproc(V_GRUP IN VARCHAR2, V_ID_TATA IN NUMBER, V_ID_UTILAD IN NUMBER); procedure grupmodproc(V_ID_GRUP IN NUMBER, V_GRUP IN VARCHAR2, V_ID_TATA IN NUMBER, V_ID_UTILMOD IN NUMBER); procedure grupdelproc(V_ID_GRUP IN NUMBER, V_ID_UTILS IN NUMBER); procedure grupdreptinsproc(V_ID_GRUP IN NUMBER, V_ID_PROGRAM IN NUMBER, V_ACCES IN VARCHAR2, V_ID_UTILAD IN NUMBER); procedure grupdreptmodproc(V_ID_GRUP IN NUMBER, V_ID_PROGRAM IN NUMBER, V_ACCES IN VARCHAR2, V_ID_UTILMOD IN NUMBER); procedure grupdreptdelproc(V_ID_DGD IN NUMBER, V_ID_UTILS IN NUMBER); procedure utildreptinsproc(V_ID_UTIL IN NUMBER, V_ID_GRUP IN NUMBER, V_ID_FIRMA IN NUMBER, V_ID_UTILAD IN NUMBER); procedure utildreptmodproc(V_ID_DUG IN NUMBER, V_ID_UTIL IN NUMBER, V_ID_GRUP IN NUMBER, V_ID_FIRMA IN NUMBER, V_ID_UTILMOD IN NUMBER); procedure utildreptdelproc(V_ID_DUG IN NUMBER, V_ID_UTILS IN NUMBER); procedure adauga_drepturi_grup(V_ID_GRUP IN NUMBER, V_ID_TATA IN NUMBER, V_ID_UTILAD IN NUMBER); procedure modifica_drepturi_grup(V_ID_GRUP IN NUMBER, V_ID_TATA IN NUMBER, V_ID_UTILMOD IN NUMBER); procedure sterge_drepturi_grup(V_ID_GRUP IN NUMBER, V_ID_UTILS IN NUMBER); procedure adauga_drepturi_obiecte(V_ID_GRUP IN NUMBER, V_ID_PROGRAM IN NUMBER, V_ACCES IN VARCHAR2, V_ID_UTILAD IN NUMBER); procedure sterge_drepturi_obiecte(V_ID_GRUP IN NUMBER, V_ID_PROGRAM IN NUMBER, V_ID_UTILS IN NUMBER); procedure adauga_drepturi_firma(V_ID_UTIL IN NUMBER, V_ID_GRUP IN NUMBER, V_ID_FIRMA IN NUMBER, V_ID_UTILAD IN NUMBER); procedure modifica_drepturi_firma(V_ID_DUG IN NUMBER, V_ID_UTIL IN NUMBER, V_ID_GRUP IN NUMBER, V_ID_FIRMA IN NUMBER, V_ID_UTILMOD IN NUMBER); procedure sterge_drepturi_firma(V_ID_DUG IN NUMBER, V_ID_UTILS IN NUMBER); procedure programe_grup(V_ID_GRUP IN DEF_GRUP.ID_GRUP%TYPE, CRS_PROGRAME OUT PACK_TYPES.tip_cursor); procedure obiecte_program(V_ID_GRUP IN DEF_GRUP.ID_GRUP%TYPE, V_ID_PROGRAM IN NUMBER, CRS_OBIECTE OUT PACK_TYPES.tip_cursor); procedure actualizeaza_seq_obiecte; function verificautilizator(V_UTILIZATOR IN VARCHAR2, V_PAROLA IN VARCHAR2) return number; function getid_obiect return number; function calculeaza_checksum(V_ID_UTIL IN NUMBER, V_NR_GRUP IN NUMBER) return number; end PACK_DREPTURI; / prompt prompt Creating package body PACK_DREPTURI prompt =================================== prompt create or replace package body PACK_DREPTURI is procedure utilizatoriinsproc(V_UTILIZATOR IN VARCHAR2, V_PAROLA IN VARCHAR2, V_INACTIV IN NUMBER, V_ID_UTILAD IN NUMBER) is lnNrUtilizatori NUMBER(10); begin EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM VUTILIZATORI WHERE UTILIZATOR=:1' USING V_UTILIZATOR; IF lnNrUtilizatori > 0 THEN RAISE_APPLICATION_ERROR(-20005, 'Mai exista un utilizator cu acest nume!'); END IF; EXECUTE IMMEDIATE 'INSERT INTO UTILIZATORI(UTILIZATOR,PAROLA,INACTIV,ID_UTILOP,DATAORA) ' || 'VALUES (:1,:2,:3,:4,SYSDATE)' USING V_UTILIZATOR, V_PAROLA, V_INACTIV, V_ID_UTILAD; end; ---------------------------------------------------------------------------------------- procedure utilizatorimodproc(V_UTILIZATOR IN VARCHAR2, V_PAROLA IN VARCHAR2, V_INACTIV IN NUMBER, V_ID_UTIL IN NUMBER, V_ID_UTILMOD IN NUMBER) is lnNrUtilizatori NUMBER(10); lcParola UTILIZATORI.PAROLA%TYPE; begin EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM VUTILIZATORI WHERE UTILIZATOR=:1 AND ID_UTIL!=:2' USING V_UTILIZATOR, V_ID_UTIL; IF lnNrUtilizatori > 0 THEN RAISE_APPLICATION_ERROR(-20005, 'Mai exista un utilizator cu acest nume!'); END IF; IF V_INACTIV = 1 THEN lcParola := 'UtIlIzAtOrInAcTiV' || V_ID_UTIL; EXECUTE IMMEDIATE 'UPDATE UTILIZATORI SET PAROLA=:1,INACTIV=:2,ID_UTILOP=:3,DATAORA=SYSDATE ' || 'WHERE ID_UTIL=:4' USING lcParola, V_INACTIV, V_ID_UTILMOD, V_ID_UTIL; ELSE lcParola := V_PAROLA; IF lcParola IS NULL THEN EXECUTE IMMEDIATE 'UPDATE UTILIZATORI SET UTILIZATOR=:1,INACTIV=:2,ID_UTILOP=:3,DATAORA=SYSDATE ' || 'WHERE ID_UTIL=:4' USING V_UTILIZATOR, V_INACTIV, V_ID_UTILMOD, V_ID_UTIL; ELSE EXECUTE IMMEDIATE 'UPDATE UTILIZATORI SET UTILIZATOR=:1,PAROLA=:2,INACTIV=:3,ID_UTILOP=:4,DATAORA=SYSDATE ' || 'WHERE ID_UTIL=:5' USING V_UTILIZATOR, lcParola, V_INACTIV, V_ID_UTILMOD, V_ID_UTIL; END IF; END IF; end; ---------------------------------------------------------------------------------------- procedure utilizatoridelproc(V_ID_UTIL IN NUMBER, V_ID_UTILS IN NUMBER) is begin IF V_ID_UTIL = -1 THEN RAISE_APPLICATION_ERROR(-20005, 'Nu puteti sterge acest utilizator!'); END IF; EXECUTE IMMEDIATE 'UPDATE UTILIZATORI SET STERS=1,ID_UTILOP=:1,DATAORA=SYSDATE ' || 'WHERE ID_UTIL=:2' USING V_ID_UTILS, V_ID_UTIL; end; ---------------------------------------------------------------------------------------- procedure parolamodproc(V_PAROLA IN VARCHAR2, V_ID_UTIL IN NUMBER) is begin EXECUTE IMMEDIATE 'UPDATE UTILIZATORI SET PAROLA=:1,ID_UTILOP=:2,DATAORA=SYSDATE ' || 'WHERE ID_UTIL=:3' USING V_PAROLA, V_ID_UTIL, V_ID_UTIL; end; ---------------------------------------------------------------------------------------- procedure programeinsproc(V_IDE_PROGRAM IN NUMBER, V_CALE IN VARCHAR2, V_TITLU IN VARCHAR2, V_INSTALAT IN NUMBER, V_ORDINE IN NUMBER, V_ID_UTILAD IN NUMBER) is lnPrograme NUMBER(2); lcProgram NOM_PROGRAME.DENUMIRE%TYPE; /* V_IDE_PROGRAM NOM_PROGRAME.ID_PROGRAM%TYPE;*/ begin /* EXECUTE IMMEDIATE 'SELECT ID_PROGRAM FROM NOM_PROGRAME WHERE DENUMIRE=:1' INTO V_IDE_PROGRAM USING V_PROGRAM; EXECUTE IMMEDIATE 'INSERT INTO NOM_PROGRAME(NUME,DIRECTOR,EXPLICATIE,INSTALAT,ORDINE,IDE_PROGRAM) '|| 'VALUES (:1,:2,:3,:4,:5,:6)' USING */ EXECUTE IMMEDIATE 'SELECT DENUMIRE FROM VNOM_PROGRAME WHERE ID_PROGRAM=:1' INTO lcProgram USING V_IDE_PROGRAM; EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM VDEF_PROGRAME WHERE ORDINE=:1' INTO lnPrograme USING V_ORDINE; IF lnPrograme > 0 THEN EXECUTE IMMEDIATE 'UPDATE DEF_PROGRAME SET ORDINE=ORDINE+1 WHERE ORDINE>=:1 AND STERS=0' USING V_ORDINE; END IF; EXECUTE IMMEDIATE 'INSERT INTO DEF_PROGRAME(DIRECTOR,EXPLICATIE,INSTALAT,ORDINE,IDE_PROGRAM,ID_UTILOP,DATAORA) ' || 'VALUES (:1,:2,:3,:4,:5,:6,SYSDATE)' USING V_CALE, V_TITLU, V_INSTALAT, V_ORDINE, V_IDE_PROGRAM, V_ID_UTILAD; end; ---------------------------------------------------------------------------------------- procedure programemodproc(V_ID_PROGRAM IN NUMBER, V_CALE IN VARCHAR2, V_TITLU IN VARCHAR2, V_INSTALAT IN NUMBER, V_ORDINE IN NUMBER, V_ID_UTILMOD IN NUMBER) is lnPrograme NUMBER(2); begin EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM VDEF_PROGRAME WHERE ID_PROG!=:1 ' || 'AND ORDINE=:2 ' INTO lnPrograme USING V_ID_PROGRAM, V_ORDINE; IF lnPrograme > 0 THEN EXECUTE IMMEDIATE 'UPDATE DEF_PROGRAME SET ORDINE=ORDINE+1 WHERE ORDINE>=:1 AND STERS=0' USING V_ORDINE; END IF; EXECUTE IMMEDIATE 'UPDATE DEF_PROGRAME SET DIRECTOR=:1,EXPLICATIE=:2,INSTALAT=:3,' || 'ORDINE=:4,ID_UTILOP=:5,DATAORA=SYSDATE WHERE ID_PROG=:6' USING V_CALE, V_TITLU, V_INSTALAT, V_ORDINE, V_ID_UTILMOD, V_ID_PROGRAM; end; ---------------------------------------------------------------------------------------- procedure programedelproc(V_ID_PROGRAM IN NUMBER, V_ID_UTILS IN NUMBER) is lnOrdine DEF_PROGRAME.ORDINE%TYPE; begin EXECUTE IMMEDIATE 'SELECT ORDINE FROM VDEF_PROGRAME WHERE ID_PROG=:1' INTO lnOrdine USING V_ID_PROGRAM; EXECUTE IMMEDIATE 'UPDATE DEF_PROGRAME SET ORDINE=ORDINE-1 WHERE ORDINE>=:1 AND STERS=0' USING lnOrdine; EXECUTE IMMEDIATE 'UPDATE DEF_PROGRAME SET STERS=1,ORDINE=-1,ID_UTILOP=:1,DATAORA=SYSDATE ' || 'WHERE ID_PROG=:2' USING V_ID_UTILS, V_ID_PROGRAM; end; ---------------------------------------------------------------------------------------- procedure firmeinsproc(V_FIRMA IN VARCHAR2, V_FSCURT IN VARCHAR2, V_COD_FISCAL IN VARCHAR2, V_REG_COMERT IN VARCHAR2, V_BANCA1 IN VARCHAR2, V_CONT_BANCA1 IN VARCHAR2, V_BANCA2 IN VARCHAR2, V_CONT_BANCA2 IN VARCHAR2, V_BANCA3 IN VARCHAR2, V_CONT_BANCA3 IN VARCHAR2, V_ADRESA IN VARCHAR2, V_COD_FIRMA IN VARCHAR2, V_PERS_JUR IN NUMBER, V_CODANG IN NUMBER, V_NUME IN VARCHAR2, V_PRENUME IN VARCHAR2, V_TELEFON1 IN VARCHAR2, V_TELEFON2 IN VARCHAR2, V_TELEFON3 IN VARCHAR2, V_FAX IN VARCHAR2, V_EMAIL IN VARCHAR2, V_OASP IN VARCHAR2, V_CAP_SOC_VAR IN NUMBER, V_CAP_SOC_SUB IN NUMBER, V_PUNCT_LUCRU IN VARCHAR2, V_CAEN IN VARCHAR2, V_SCHEMA IN VARCHAR2, V_ID_MAMA IN NUMBER, V_MAMA IN VARCHAR2, V_SUCURSALA IN VARCHAR2, V_ID_LOC IN NUMBER, V_ID_UTILAD IN NUMBER) is lnFirme NUMBER(2); begin EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM V_NOM_FIRME WHERE SCHEMA=:1' INTO lnFirme USING V_SCHEMA; IF lnFirme > 0 THEN RAISE_APPLICATION_ERROR(-20100, 'Pe aceasta schema a mai fost definita o alta firma!'); END IF; EXECUTE IMMEDIATE 'INSERT INTO NOM_FIRME(FIRMA,FSCURT,COD_FISCAL,REG_COMERT,' || 'BANCA1,CONT_BANCA1,BANCA2,CONT_BANCA2,BANCA3,CONT_BANCA3,ADRESA,COD_FIRMA,' || 'PERSOANA_JURIDICA,CODANG,NUME,PRENUME,TELEFON,TELEFON2,TELEFON3,FAX,EMAIL,OASP,' || 'CAPITAL_SOC_VAR,CAPITAL_SOC_SUB,PUNCT_LUC,CAEN,SCHEMA,ID_MAMA,MAMA,SUCURSALA,' || 'ID_LOC,ID_UTILOP,DATAORA) ' || 'VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,' || ':23,:24,:25,:26,:27,:28,:29,:30,:31,:32,SYSDATE)' USING V_FIRMA, V_FSCURT, V_COD_FISCAL, V_REG_COMERT, V_BANCA1, V_CONT_BANCA1, V_BANCA2, V_CONT_BANCA2, V_BANCA3, V_CONT_BANCA3, V_ADRESA, V_COD_FIRMA, V_PERS_JUR, V_CODANG, V_NUME, V_PRENUME, V_TELEFON1, V_TELEFON2, V_TELEFON3, V_FAX, V_EMAIL, V_OASP, V_CAP_SOC_VAR, V_CAP_SOC_SUB, V_PUNCT_LUCRU, V_CAEN, V_SCHEMA, V_ID_MAMA, V_MAMA, V_SUCURSALA, V_ID_LOC, V_ID_UTILAD; end; ---------------------------------------------------------------------------------------- procedure firmemodproc(V_ID_FIRMA IN NUMBER, V_FIRMA IN VARCHAR2, V_FSCURT IN VARCHAR2, V_COD_FISCAL IN VARCHAR2, V_REG_COMERT IN VARCHAR2, V_BANCA1 IN VARCHAR2, V_CONT_BANCA1 IN VARCHAR2, V_BANCA2 IN VARCHAR2, V_CONT_BANCA2 IN VARCHAR2, V_BANCA3 IN VARCHAR2, V_CONT_BANCA3 IN VARCHAR2, V_ADRESA IN VARCHAR2, V_COD_FIRMA IN VARCHAR2, V_PERS_JUR IN NUMBER, V_CODANG IN NUMBER, V_NUME IN VARCHAR2, V_PRENUME IN VARCHAR2, V_TELEFON1 IN VARCHAR2, V_TELEFON2 IN VARCHAR2, V_TELEFON3 IN VARCHAR2, V_FAX IN VARCHAR2, V_EMAIL IN VARCHAR2, V_OASP IN VARCHAR2, V_CAP_SOC_VAR IN NUMBER, V_CAP_SOC_SUB IN NUMBER, V_PUNCT_LUCRU IN VARCHAR2, V_CAEN IN VARCHAR2, V_SCHEMA IN VARCHAR2, V_ID_MAMA IN NUMBER, V_MAMA IN VARCHAR2, V_SUCURSALA IN VARCHAR2, V_ID_LOC IN NUMBER, V_ID_UTILMOD IN NUMBER) is lnFirme NUMBER(2); begin EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM V_NOM_FIRME WHERE SCHEMA=:1 AND ID_FIRMA!=:2' INTO lnFirme USING V_SCHEMA, V_ID_FIRMA; IF lnFirme > 0 THEN RAISE_APPLICATION_ERROR(-20100, 'Pe aceasta schema a mai fost definita o alta firma!'); END IF; EXECUTE IMMEDIATE 'UPDATE NOM_FIRME SET FIRMA=:1,FSCURT=:2,COD_FISCAL=:3,' || 'REG_COMERT=:4,BANCA1=:5,CONT_BANCA1=:6,BANCA2=:7,CONT_BANCA2=:8,BANCA3=:9,' || 'CONT_BANCA3=:10,ADRESA=:11,COD_FIRMA=:12,PERSOANA_JURIDICA=:13,CODANG=:14,' || 'NUME=:15,PRENUME=:16,TELEFON=:17,TELEFON2=:18,TELEFON3=:19,FAX=:20,' || 'CAPITAL_SOC_VAR=:21,CAPITAL_SOC_SUB=:22,PUNCT_LUC=:23,CAEN=:24,SCHEMA=:25,' || 'ID_MAMA=:26,MAMA=:27,SUCURSALA=:28,ID_LOC=:29,ID_UTILOP=:30,DATAORA=SYSDATE,EMAIL=:31, ' || 'OASP=:32 WHERE ID_FIRMA=:33' USING V_FIRMA, V_FSCURT, V_COD_FISCAL, V_REG_COMERT, V_BANCA1, V_CONT_BANCA1, V_BANCA2, V_CONT_BANCA2, V_BANCA3, V_CONT_BANCA3, V_ADRESA, V_COD_FIRMA, V_PERS_JUR, V_CODANG, V_NUME, V_PRENUME, V_TELEFON1, V_TELEFON2, V_TELEFON3, V_FAX, V_CAP_SOC_VAR, V_CAP_SOC_SUB, V_PUNCT_LUCRU, V_CAEN, V_SCHEMA, V_ID_MAMA, V_MAMA, V_SUCURSALA, V_ID_LOC, V_ID_UTILMOD, V_EMAIL, V_OASP, V_ID_FIRMA; end; ---------------------------------------------------------------------------------------- procedure firmedelproc(V_ID_FIRMA IN VARCHAR2, V_ID_UTILS IN NUMBER) is begin EXECUTE IMMEDIATE 'UPDATE NOM_FIRME SET STERS=1,ID_UTILOP=:1,DATAORA=SYSDATE ' || 'WHERE ID_FIRMA=:2' USING V_ID_UTILS, V_ID_FIRMA; end; ---------------------------------------------------------------------------------------- procedure grupinsproc(V_GRUP IN VARCHAR2, V_ID_TATA IN NUMBER, V_ID_UTILAD IN NUMBER) is V_ID_GRUP DEF_GRUP.ID_GRUP%TYPE; lnAparitii NUMBER(3); begin EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM DEF_GRUP WHERE STERS=0 AND GRUP=:1' INTO lnAparitii USING V_GRUP; IF lnAparitii > 0 THEN RAISE_APPLICATION_ERROR(-20005, 'Mai exista un grup cu acelasi nume!'); END IF; EXECUTE IMMEDIATE 'INSERT INTO DEF_GRUP(GRUP,ID_TATA,ID_UTILOP,DATAORA) ' || 'VALUES (:1,:2,:3,SYSDATE) RETURNING ID_GRUP INTO :4' USING V_GRUP, V_ID_TATA, V_ID_UTILAD RETURNING INTO V_ID_GRUP; IF V_ID_TATA != -1 THEN pack_drepturi.adauga_drepturi_grup(V_ID_GRUP, V_ID_TATA, V_ID_UTILAD); END IF; end; ---------------------------------------------------------------------------------------- procedure grupmodproc(V_ID_GRUP IN NUMBER, V_GRUP IN VARCHAR2, V_ID_TATA IN NUMBER, V_ID_UTILMOD IN NUMBER) is V_ID_TATA_VECHI DEF_GRUP.ID_TATA%TYPE; lnCopii NUMBER(3); lnAparitii NUMBER(3); begin EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM DEF_GRUP WHERE STERS=0 AND GRUP=:1 AND ID_GRUP!=:2' INTO lnAparitii USING V_GRUP, V_ID_GRUP; IF lnAparitii > 0 THEN RAISE_APPLICATION_ERROR(-20005, 'Mai exista un grup cu acelasi nume!'); END IF; EXECUTE IMMEDIATE 'SELECT ID_TATA FROM DEF_GRUP WHERE STERS=0 AND ID_GRUP=:1' INTO V_ID_TATA_VECHI USING V_ID_GRUP; EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM DEF_GRUP WHERE STERS=0 AND ID_TATA=:1' INTO lnCopii USING V_ID_GRUP; IF lnCopii > 0 AND V_ID_TATA_VECHI != V_ID_TATA THEN RAISE_APPLICATION_ERROR(-20005, 'Nu se poate modifica grupul superior al acestui grup,deoarece are definite subgrupuri!'); END IF; EXECUTE IMMEDIATE 'UPDATE DEF_GRUP SET GRUP=:1,ID_TATA=:2,ID_UTILOP=:3,DATAORA=SYSDATE ' || 'WHERE ID_GRUP=:4' USING V_GRUP, V_ID_TATA, V_ID_UTILMOD, V_ID_GRUP; IF V_ID_TATA_VECHI != V_ID_TATA THEN pack_drepturi.modifica_drepturi_grup(V_ID_GRUP, V_ID_TATA, V_ID_UTILMOD); END IF; end; ---------------------------------------------------------------------------------------- procedure grupdelproc(V_ID_GRUP IN NUMBER, V_ID_UTILS IN NUMBER) is lnCopii NUMBER(3); begin IF V_ID_GRUP = 0 THEN RAISE_APPLICATION_ERROR(-20005, 'Nu puteti sterge acest grup!'); END IF; EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM DEF_GRUP WHERE STERS=0 AND ID_TATA=:1' INTO lnCopii USING V_ID_GRUP; IF lnCopii > 0 THEN RAISE_APPLICATION_ERROR(-20005, 'Nu se poate sterge acest grup,deoarece are definite subgrupuri!'); END IF; EXECUTE IMMEDIATE 'UPDATE DEF_GRUP SET STERS=1,ID_UTILOP=:1,DATAORA=SYSDATE ' || 'WHERE ID_GRUP=:2' USING V_ID_UTILS, V_ID_GRUP; pack_drepturi.sterge_drepturi_grup(V_ID_GRUP, V_ID_UTILS); end; ---------------------------------------------------------------------------------------- procedure grupdreptinsproc(V_ID_GRUP IN NUMBER, V_ID_PROGRAM IN NUMBER, V_ACCES IN VARCHAR2, V_ID_UTILAD IN NUMBER) is lcGrup VARCHAR2(50); lnIdGrup DEF_GRUP.ID_GRUP%TYPE; lnGrupuri NUMBER(10); lnSucces NUMBER(10); lcSql VARCHAR2(500); begin EXECUTE IMMEDIATE 'SELECT GRUP FROM VDEF_GRUP WHERE ID_GRUP=:1' INTO lcGrup USING V_ID_GRUP; lcGrup := '%\' || lcGrup || '%'; lcSql := 'SELECT ID_GRUP FROM VDEF_GRUP WHERE TATA LIKE :1'; lnGrupuri := dbms_sql.open_cursor(); dbms_sql.parse(lnGrupuri, lcSql, dbms_sql.native); dbms_sql.define_column(lnGrupuri, 1, lnIdGrup); dbms_sql.bind_variable(lnGrupuri, ':1', lcGrup); lnSucces := dbms_sql.execute(lnGrupuri); LOOP EXIT WHEN dbms_sql.fetch_rows(lnGrupuri) = 0; dbms_sql.column_value(lnGrupuri, 1, lnIdGrup); EXECUTE IMMEDIATE 'INSERT INTO DEF_GRUP_DREPT(ID_GRUP,ID_PROGRAM,ID_UTILOP,DATAORA) ' || 'VALUES (:1,:2,:3,SYSDATE)' USING lnIdGrup, V_ID_PROGRAM, V_ID_UTILAD; IF V_ACCES IS NOT NULL THEN pack_drepturi.adauga_drepturi_obiecte(lnIdGrup, V_ID_PROGRAM, V_ACCES, V_ID_UTILAD); END IF; END LOOP; DBMS_SQL.CLOSE_CURSOR(lnGrupuri); end; ---------------------------------------------------------------------------------------- procedure grupdreptmodproc(V_ID_GRUP IN NUMBER, V_ID_PROGRAM IN NUMBER, V_ACCES IN VARCHAR2, V_ID_UTILMOD IN NUMBER) is lcGrup VARCHAR2(50); lnIdGrup DEF_GRUP.ID_GRUP%TYPE; lnGrupuri NUMBER(10); lnSucces NUMBER(10); lcSql VARCHAR2(500); begin EXECUTE IMMEDIATE 'SELECT GRUP FROM VDEF_GRUP WHERE ID_GRUP=:1' INTO lcGrup USING V_ID_GRUP; lcGrup := '%\' || lcGrup || '%'; lcSql := 'SELECT ID_GRUP FROM VDEF_GRUP WHERE TATA LIKE :1'; lnGrupuri := dbms_sql.open_cursor(); dbms_sql.parse(lnGrupuri, lcSql, dbms_sql.native); dbms_sql.define_column(lnGrupuri, 1, lnIdGrup); dbms_sql.bind_variable(lnGrupuri, ':1', lcGrup); lnSucces := dbms_sql.execute(lnGrupuri); LOOP EXIT WHEN dbms_sql.fetch_rows(lnGrupuri) = 0; dbms_sql.column_value(lnGrupuri, 1, lnIdGrup); pack_drepturi.sterge_drepturi_obiecte(lnIdGrup, V_ID_PROGRAM, V_ID_UTILMOD); IF V_ACCES IS NOT NULL THEN pack_drepturi.adauga_drepturi_obiecte(lnIdGrup, V_ID_PROGRAM, V_ACCES, V_ID_UTILMOD); END IF; END LOOP; DBMS_SQL.CLOSE_CURSOR(lnGrupuri); end; ---------------------------------------------------------------------------------------- procedure grupdreptdelproc(V_ID_DGD IN NUMBER, V_ID_UTILS IN NUMBER) is V_ID_GRUP DEF_GRUP_DREPT.ID_GRUP%TYPE; lnIdProgram DEF_GRUP_DREPT.ID_PROGRAM%TYPE; lcGrup VARCHAR2(50); lnIdGrup DEF_GRUP.ID_GRUP%TYPE; lnGrupuri NUMBER(10); lnSucces NUMBER(10); lcSql VARCHAR2(500); begin EXECUTE IMMEDIATE 'SELECT ID_PROGRAM,ID_GRUP FROM DEF_GRUP_DREPT WHERE ID_DGD=:1' INTO lnIdProgram, V_ID_GRUP USING V_ID_DGD; EXECUTE IMMEDIATE 'SELECT GRUP FROM VDEF_GRUP WHERE ID_GRUP=:1' INTO lcGrup USING V_ID_GRUP; lcGrup := '%\' || lcGrup || '%'; lcSql := 'SELECT ID_GRUP FROM VDEF_GRUP WHERE TATA LIKE :1'; lnGrupuri := dbms_sql.open_cursor(); dbms_sql.parse(lnGrupuri, lcSql, dbms_sql.native); dbms_sql.define_column(lnGrupuri, 1, lnIdGrup); dbms_sql.bind_variable(lnGrupuri, ':1', lcGrup); lnSucces := dbms_sql.execute(lnGrupuri); LOOP EXIT WHEN dbms_sql.fetch_rows(lnGrupuri) = 0; dbms_sql.column_value(lnGrupuri, 1, lnIdGrup); EXECUTE IMMEDIATE 'UPDATE DEF_GRUP_DREPT SET STERS=1,ID_UTILOP=:1,' || 'DATAORA=SYSDATE WHERE STERS=0 AND ' || 'ID_GRUP=:2 AND ID_PROGRAM=:3' USING V_ID_UTILS, lnIdGrup, lnIdProgram; pack_drepturi.sterge_drepturi_obiecte(lnIdGrup, lnIdProgram, V_ID_UTILS); END LOOP; DBMS_SQL.CLOSE_CURSOR(lnGrupuri); end; ---------------------------------------------------------------------------------------- procedure utildreptinsproc(V_ID_UTIL IN NUMBER, V_ID_GRUP IN NUMBER, V_ID_FIRMA IN NUMBER, V_ID_UTILAD IN NUMBER) is lnSucces NUMBER(10); lnFirme NUMBER(2); lnIdFirma NOM_FIRME.ID_FIRMA%TYPE; lcSql VARCHAR2(1000); begin IF V_ID_FIRMA > -1 THEN pack_drepturi.adauga_drepturi_firma(V_ID_UTIL, V_ID_GRUP, V_ID_FIRMA, V_ID_UTILAD); ELSE lcSql := 'SELECT ID_FIRMA FROM V_NOM_FIRME WHERE ID_FIRMA NOT IN ' || '(SELECT ID_FIRMA FROM VDEF_UTIL_GRUP WHERE ID_UTIL=:1 AND ID_GRUP=:2)'; lnFirme := dbms_sql.open_cursor(); dbms_sql.parse(lnFirme, lcSql, dbms_sql.native); dbms_sql.define_column(lnFirme, 1, lnIdFirma); dbms_sql.bind_variable(lnFirme, ':1', V_ID_UTIL); dbms_sql.bind_variable(lnFirme, ':2', V_ID_GRUP); lnSucces := dbms_sql.execute(lnFirme); LOOP EXIT WHEN dbms_sql.fetch_rows(lnFirme) = 0; dbms_sql.column_value(lnFirme, 1, lnIdFirma); pack_drepturi.adauga_drepturi_firma(V_ID_UTIL, V_ID_GRUP, lnIdFirma, V_ID_UTILAD); END LOOP; dbms_sql.close_cursor(lnFirme); END IF; end; ---------------------------------------------------------------------------------------- procedure utildreptmodproc(V_ID_DUG IN NUMBER, V_ID_UTIL IN NUMBER, V_ID_GRUP IN NUMBER, V_ID_FIRMA IN NUMBER, V_ID_UTILMOD IN NUMBER) is lnSucces NUMBER(10); lnFirme NUMBER(2); lnIdFirma NOM_FIRME.ID_FIRMA%TYPE; lcSql VARCHAR2(1000); begin IF V_ID_FIRMA > -1 THEN pack_drepturi.sterge_drepturi_firma(V_ID_DUG, V_ID_UTILMOD); pack_drepturi.adauga_drepturi_firma(V_ID_UTIL, V_ID_GRUP, V_ID_FIRMA, V_ID_UTILMOD); ELSE lcSql := 'SELECT ID_FIRMA FROM V_NOM_FIRME WHERE ID_FIRMA NOT IN ' || '(SELECT ID_FIRMA FROM VDEF_UTIL_GRUP WHERE ID_UTIL=:1 AND ID_GRUP=:2)'; lnFirme := dbms_sql.open_cursor(); dbms_sql.parse(lnFirme, lcSql, dbms_sql.native); dbms_sql.define_column(lnFirme, 1, lnIdFirma); dbms_sql.bind_variable(lnFirme, ':1', V_ID_UTIL); dbms_sql.bind_variable(lnFirme, ':2', V_ID_GRUP); lnSucces := dbms_sql.execute(lnFirme); LOOP EXIT WHEN dbms_sql.fetch_rows(lnFirme) = 0; dbms_sql.column_value(lnFirme, 1, lnIdFirma); pack_drepturi.adauga_drepturi_firma(V_ID_UTIL, V_ID_GRUP, lnIdFirma, V_ID_UTILMOD); END LOOP; dbms_sql.close_cursor(lnFirme); END IF; end; ---------------------------------------------------------------------------------------- procedure utildreptdelproc(V_ID_DUG IN NUMBER, V_ID_UTILS IN NUMBER) is begin pack_drepturi.sterge_drepturi_firma(V_ID_DUG, V_ID_UTILS); end; ---------------------------------------------------------------------------------------- procedure adauga_drepturi_firma(V_ID_UTIL IN NUMBER, V_ID_GRUP IN NUMBER, V_ID_FIRMA IN NUMBER, V_ID_UTILAD IN NUMBER) is lnAparitii NUMBER(10); lcFirma VARCHAR2(100); begin EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM DEF_UTIL_GRUP WHERE ID_UTIL=:1 AND ID_FIRMA=:2 ' || 'AND STERS=0' INTO lnAparitii USING V_ID_UTIL, V_ID_FIRMA; IF lnAparitii > 0 THEN EXECUTE IMMEDIATE 'SELECT FIRMA FROM V_NOM_FIRME WHERE ID_FIRMA=:1' INTO lcFirma USING V_ID_FIRMA; RAISE_APPLICATION_ERROR(-20005, 'Acest utilizator mai are drepturi pe firma ' || lcFirma); ELSE EXECUTE IMMEDIATE 'INSERT INTO DEF_UTIL_GRUP(ID_UTIL,ID_GRUP,ID_FIRMA,ID_UTILOP,DATAORA) ' || 'VALUES (:1,:2,:3,:4,SYSDATE)' USING V_ID_UTIL, V_ID_GRUP, V_ID_FIRMA, V_ID_UTILAD; END IF; end; ---------------------------------------------------------------------------------------- procedure modifica_drepturi_firma(V_ID_DUG IN NUMBER, V_ID_UTIL IN NUMBER, V_ID_GRUP IN NUMBER, V_ID_FIRMA IN NUMBER, V_ID_UTILMOD IN NUMBER) is begin pack_drepturi.sterge_drepturi_firma(V_ID_DUG, V_ID_UTILMOD); pack_drepturi.adauga_drepturi_firma(V_ID_UTIL, V_ID_GRUP, V_ID_FIRMA, V_ID_UTILMOD); end; ---------------------------------------------------------------------------------------- procedure sterge_drepturi_firma(V_ID_DUG IN NUMBER, V_ID_UTILS IN NUMBER) is begin EXECUTE IMMEDIATE 'UPDATE DEF_UTIL_GRUP SET STERS=1,ID_UTILOP=:1,DATAORA=SYSDATE ' || 'WHERE ID_DUG=:2' USING V_ID_UTILS, V_ID_DUG; end; ---------------------------------------------------------------------------------------- procedure adauga_drepturi_grup(V_ID_GRUP IN NUMBER, V_ID_TATA IN NUMBER, V_ID_UTILAD IN NUMBER) is begin EXECUTE IMMEDIATE 'INSERT INTO DEF_GRUP_DREPT(ID_GRUP,ID_PROGRAM,ID_UTILOP,DATAORA) ' || 'SELECT :1 AS ID_GRUP,ID_PROGRAM,:2 AS ID_UTILOP,SYSDATE AS DATAORA FROM ' || 'DEF_GRUP_DREPT WHERE STERS=0 AND ID_GRUP=:3' USING V_ID_GRUP, V_ID_UTILAD, V_ID_TATA; EXECUTE IMMEDIATE 'INSERT INTO DEF_GRUP_DREPT_OBIECTE (ID_OBIECT,ID_GRUP,ID_UTILOP,' || 'DATAORA) SELECT ID_OBIECT,:1 AS ID_GRUP,:2 AS ID_UTILOP,SYSDATE AS DATAORA FROM ' || 'DEF_GRUP_DREPT_OBIECTE WHERE STERS=0 AND ID_GRUP=:3' USING V_ID_GRUP, V_ID_UTILAD, V_ID_TATA; end; ---------------------------------------------------------------------------------------- procedure modifica_drepturi_grup(V_ID_GRUP IN NUMBER, V_ID_TATA IN NUMBER, V_ID_UTILMOD IN NUMBER) is begin pack_drepturi.sterge_drepturi_grup(V_ID_GRUP, V_ID_UTILMOD); IF V_ID_TATA != -1 THEN pack_drepturi.adauga_drepturi_grup(V_ID_GRUP, V_ID_TATA, V_ID_UTILMOD); END IF; end; ---------------------------------------------------------------------------------------- procedure sterge_drepturi_grup(V_ID_GRUP IN NUMBER, V_ID_UTILS IN NUMBER) is begin EXECUTE IMMEDIATE 'UPDATE DEF_GRUP_DREPT SET STERS=1,ID_UTILOP=:1,DATAORA=SYSDATE ' || 'WHERE ID_GRUP=:2 AND STERS=0' USING V_ID_UTILS, V_ID_GRUP; EXECUTE IMMEDIATE 'UPDATE DEF_GRUP_DREPT_OBIECTE SET STERS=1,ID_UTILOP=:1,DATAORA=SYSDATE ' || 'WHERE ID_GRUP=:2 AND STERS=0' USING V_ID_UTILS, V_ID_GRUP; end; ---------------------------------------------------------------------------------------- procedure adauga_drepturi_obiecte(V_ID_GRUP IN NUMBER, V_ID_PROGRAM IN NUMBER, V_ACCES IN VARCHAR2, V_ID_UTILAD IN NUMBER) is lcSir VARCHAR2(10000); lcAcces VARCHAR2(10); lnPozitie NUMBER(20); lnIesire NUMBER(1); lnCaractere NUMBER(2); lnIdObiect NUMBER(10); lnLungime NUMBER(10); begin lcSir := v_acces; lnLungime := LENGTH(v_acces); lnPozitie := 1; loop exit when lnPozitie > lnLungime; lcAcces := ''; lnCaractere := 0; lnIesire := 0; loop if (ascii(substr(lcSir, lnPozitie + lnCaractere, 1)) between 48 and 57 and ascii(substr(lcSir, lnPozitie + lnCaractere + 1, 1)) not between 48 and 57) or (lnPozitie + lnCaractere = lnLungime) then lnIesire := 1; end if; lnCaractere := lnCaractere + 1; exit when lnIesire = 1; end loop; lcAcces := substr(lcSir, lnPozitie, lnCaractere); lnPozitie := lnPozitie + lnCaractere; EXECUTE IMMEDIATE 'SELECT ID_OBIECT FROM VDEF_OBIECTE WHERE CHEIE=:1 AND ID_PROGRAM=:2' INTO lnIdObiect USING lcAcces, v_id_program; EXECUTE IMMEDIATE 'INSERT INTO DEF_GRUP_DREPT_OBIECTE' || '(ID_GRUP,ID_OBIECT,ID_UTILOP,DATAORA) VALUES(:1,:2,:3,SYSDATE)' USING v_id_grup, lnIdObiect, v_id_utilad; end loop; end; ---------------------------------------------------------------------------------------- procedure sterge_drepturi_obiecte(V_ID_GRUP IN NUMBER, V_ID_PROGRAM IN NUMBER, V_ID_UTILS IN NUMBER) is begin EXECUTE IMMEDIATE 'UPDATE DEF_GRUP_DREPT_OBIECTE SET STERS=1,ID_UTILOP=:1,' || 'DATAORA=SYSDATE WHERE ID_GRUP=:2 AND ' || 'ID_OBIECT IN (SELECT ID_OBIECT FROM DEF_OBIECTE WHERE ' || 'ID_PROGRAM=:3 AND STERS=0) AND STERS=0' USING v_id_utils, v_id_grup, v_id_program; end; ---------------------------------------------------------------------------------------- procedure programe_grup(V_ID_GRUP IN DEF_GRUP.ID_GRUP%TYPE, CRS_PROGRAME OUT PACK_TYPES.tip_cursor) is V_ID_TATA DEF_GRUP.ID_TATA%TYPE; lcSelect varchar2(1000); begin EXECUTE IMMEDIATE 'SELECT ID_TATA FROM VDEF_GRUP WHERE ID_GRUP = :1' INTO V_ID_TATA USING V_ID_GRUP; IF V_ID_TATA = -1 THEN lcSelect := 'SELECT IDE_PROGRAM,NUME FROM VDEF_PROGRAME WHERE INSTALAT=1 ' || 'AND IDE_PROGRAM NOT IN (SELECT ID_PROGRAM FROM VDEF_GRUP_DREPT WHERE ' || 'ID_GRUP=:1) ORDER BY NUME'; OPEN CRS_PROGRAME FOR lcSelect USING V_ID_GRUP; ELSE lcSelect := 'SELECT IDE_PROGRAM,NUME FROM VDEF_PROGRAME WHERE IDE_PROGRAM ' || 'IN (SELECT ID_PROGRAM AS IDE_PROGRAM FROM VDEF_GRUP_DREPT WHERE ID_GRUP=:1 ' || 'MINUS SELECT ID_PROGRAM FROM VDEF_GRUP_DREPT WHERE ID_GRUP=:2) AND INSTALAT=1 ' || 'ORDER BY NUME'; OPEN CRS_PROGRAME FOR lcSelect USING V_ID_TATA, V_ID_GRUP; END IF; end; ---------------------------------------------------------------------------------------- procedure obiecte_program(V_ID_GRUP IN DEF_GRUP.ID_GRUP%TYPE, V_ID_PROGRAM IN NUMBER, CRS_OBIECTE OUT PACK_TYPES.tip_cursor) is V_ID_TATA DEF_GRUP.ID_TATA%TYPE; lcSelect varchar2(1000); begin EXECUTE IMMEDIATE 'SELECT ID_TATA FROM VDEF_GRUP WHERE ID_GRUP =:1' INTO V_ID_TATA USING V_ID_GRUP; IF V_ID_TATA = -1 THEN lcSelect := 'SELECT * FROM VDEF_OBIECTE WHERE ID_PROGRAM=:1 ORDER BY CHEIE'; OPEN CRS_OBIECTE FOR lcSelect USING V_ID_PROGRAM; ELSE lcSelect := 'SELECT * FROM VDEF_OBIECTE WHERE ID_OBIECT IN (' || 'SELECT ID_OBIECT FROM DEF_OBIECTE WHERE STERS=0 AND ID_PROGRAM=:1 ' || 'START WITH ID_OBIECT IN (SELECT ID_OBIECT FROM DEF_GRUP_DREPT_OBIECTE WHERE ' || 'STERS=0 AND ID_GRUP=:2) CONNECT BY PRIOR ID_TATA=ID_OBIECT) ORDER BY CHEIE'; OPEN CRS_OBIECTE FOR lcSelect USING V_ID_PROGRAM, V_ID_TATA; END IF; end; ---------------------------------------------------------------------------------------- procedure actualizeaza_seq_obiecte is V_ID NUMBER(10); V_DIF NUMBER(10); V_CURR_VAL NUMBER(10); V_CURR_VAL2 NUMBER(10); begin EXECUTE IMMEDIATE 'SELECT NVL(MAX(ID_OBIECT),1) FROM DEF_OBIECTE' INTO V_ID; EXECUTE IMMEDIATE 'SELECT SEQ_DEF_OBIECTE.NEXTVAL FROM DUAL' INTO V_CURR_VAL; V_DIF := V_ID - V_CURR_VAL; EXECUTE IMMEDIATE 'ALTER SEQUENCE SEQ_DEF_OBIECTE INCREMENT BY ' || V_DIF || ' NOMINVALUE '; EXECUTE IMMEDIATE 'SELECT SEQ_DEF_OBIECTE.NEXTVAL FROM DUAL' INTO V_CURR_VAL2; EXECUTE IMMEDIATE 'ALTER SEQUENCE SEQ_DEF_OBIECTE INCREMENT BY 1 MINVALUE 0'; end; ---------------------------------------------------------------------------------------- function verificautilizator(V_UTILIZATOR IN VARCHAR2, V_PAROLA IN VARCHAR2) return number is lnIdUtilOrig utilizatori.id_util%type; lnNrGrup NUMBER(10); lnIdUtil NUMBER(10); lnCheckSum NUMBER(2); begin begin EXECUTE IMMEDIATE 'SELECT ID_UTIL FROM UTILIZATORI ' || 'WHERE UTILIZATOR=:1 AND PAROLA=:2 ' || 'AND INACTIV=0 AND STERS=0' INTO lnIdUtilOrig USING V_UTILIZATOR, V_PAROLA; begin EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM DEF_UTIL_GRUP ' || 'WHERE STERS=0 AND ID_UTIL=:1 AND ID_GRUP=0' INTO lnNrGrup USING lnIdUtilOrig; exception when NO_DATA_FOUND then lnNrGrup := -1; end; lnCheckSum := pack_drepturi.calculeaza_checksum(lnIdUtilOrig, lnNrGrup); lnIdUtil := lnIdUtilOrig * 100 + lnCheckSum; exception when NO_DATA_FOUND then lnIdUtil := -1; end; return lnIdUtil; end; ---------------------------------------------------------------------------------------- function getid_obiect return number is lnIdObiect obiecte.id_obiect%type; begin select seq_def_obiecte.nextval into lnIdObiect from dual; return lnIdObiect; end; ---------------------------------------------------------------------------------------- function calculeaza_checksum(V_ID_UTIL IN NUMBER, V_NR_GRUP IN NUMBER) return number is lnNumar NUMBER(10); lnSuma NUMBER(10); lnCheckSum NUMBER(2); begin lnSuma := 0; lnNumar := V_ID_UTIL; LOOP EXIT WHEN lnNumar = 0; lnSuma := lnSuma + MOD(lnNumar, 10); lnNumar := TRUNC(lnNumar / 10); END LOOP; lnCheckSum := 13 - MOD(lnSuma, 13); IF V_NR_GRUP < 1 THEN lnCheckSum := lnCheckSum - 1; END IF; return lnCheckSum; end; end PACK_DREPTURI; / prompt prompt Creating trigger TRG_DEF_GDO_BEFOINS prompt ==================================== prompt create or replace trigger TRG_DEF_GDO_BEFOINS before insert on DEF_GRUP_DREPT_OBIECTE referencing old as old new as new for each row begin select SEQ_DEF_GDO.NEXTVAL into :new.ID_DGDO from dual; end; / prompt prompt Creating trigger TRG_DEF_GRUP_BEFOINS prompt ===================================== prompt create or replace trigger TRG_DEF_GRUP_BEFOINS before insert on DEF_GRUP referencing old as old new as new for each row begin select SEQ_DEF_GRUP.NEXTVAL into :new.ID_GRUP from dual; end; / prompt prompt Creating trigger TRG_DEF_GRUP_DREPT_BEFOINS prompt =========================================== prompt create or replace trigger TRG_DEF_GRUP_DREPT_BEFOINS before insert on DEF_GRUP_DREPT referencing old as old new as new for each row begin select SEQ_DEF_GRUP_DREPT.NEXTVAL into :new.ID_DGD from dual; end; / prompt prompt Creating trigger TRG_DEF_OBIECTE_BEFOINS prompt ======================================== prompt create or replace trigger TRG_DEF_OBIECTE_BEFOINS before insert on DEF_OBIECTE referencing old as old new as new for each row begin select SEQ_DEF_OBIECTE.NEXTVAL into :new.ID_OBIECT from dual; end; / prompt prompt Creating trigger TRG_DEF_PROGRAME_BEFOINS prompt ========================================= prompt create or replace trigger TRG_DEF_PROGRAME_BEFOINS before insert on DEF_PROGRAME referencing old as old new as new for each row begin select SEQ_DEF_PROGRAME.NEXTVAL into :new.ID_PROG from dual; end; / prompt prompt Creating trigger TRG_DEF_UTIL_GRUP_BEFOINS prompt ========================================== prompt create or replace trigger TRG_DEF_UTIL_GRUP_BEFOINS before insert on DEF_UTIL_GRUP referencing old as old new as new for each row begin select SEQ_DEF_UTIL_GRUP.NEXTVAL into :new.ID_DUG from dual; end; / CREATE OR REPLACE VIEW V_NOM_FIRME AS SELECT ID_FIRMA, FIRMA, FSCURT, COD_FISCAL, REG_COMERT,BANCA1, CONT_BANCA1,BANCA2, CONT_BANCA2,BANCA3, CONT_BANCA3, ADRESA,COD_FIRMA,ID_MAMA,MAMA,SUCURSALA,ID_LOC,PERSOANA_JURIDICA,CODANG,NUME,PRENUME,TELEFON, FAX,EMAIL,OASP, CAPITAL_SOC_VAR,CAPITAL_SOC_SUB,PUNCT_LUC,CAEN,SCHEMA,LOCALITATE,TELEFON2,TELEFON3 FROM NOM_FIRME WHERE STERS=0; alter table utilizatori add dataora date default sysdate; alter table utilizatori add id_utilop number(5); alter table nom_firme add telefon2 varchar2(20); alter table nom_firme add telefon3 varchar2(20); alter table nom_firme add id_utilop number(10); alter table nom_firme add dataora date default sysdate; alter table nom_firme add sters number(1) default 0; update nom_firme set sters=0; alter table def_grup disable all triggers; insert into def_grup(grup,id_tata,id_utilop,dataora) values ('ADMINISTRARE',-1,1,SYSDATE); alter tabel def_grup enable all triggers; alter table nom_programe disable all triggers; insert into nom_programe (id_program,denumire,descriere) values(0,'ROASTART','Start'); alter table nom_programe enable all triggers; insert into def_grup_drept(id_grup,id_program,id_utilop,dataora) values (0,0,1,SYSDATE); alter table nom_firme disable all triggers; insert into nom_firme(firma,id_firma,schema,sters,id_utilop,dataora) values('START',0,'',1,1,SYSDATE); alter table nom_firme enable all triggers; insert into def_util_grup(id_util,id_grup,id_firma,id_utilop,dataora) values (1,0,0,1,SYSDATE); spool off