prompt
prompt	Stergere tabela CRM_COLOANE_CONTRACT
prompt
prompt
drop table CRM_COLOANE_CONTRACT;

prompt
prompt	Stergere tabela CRM_MODELE_CONTRACT
prompt
prompt
drop table CRM_MODELE_CONTRACT;

prompt
prompt	Stergere tabela CRM_MODELE_OFERTA
prompt
prompt
drop table CRM_MODELE_OFERTA;

prompt
prompt	Stergere tabela CRM_TAG_CONTRACTE
prompt
prompt
drop table CRM_TAG_CONTRACTE;

prompt
prompt	Stergere secventa SEQ_CRM_MODELE_CONTRACT
prompt
prompt
drop sequence SEQ_CRM_MODELE_CONTRACT;

prompt
prompt	Stergere secventa SEQ_CRM_MODELE_CTR
prompt
prompt
drop sequence SEQ_CRM_MODELE_CTR;

prompt
prompt	Stergere secventa SEQ_CRM_MODELE_OFERTA
prompt
prompt
drop sequence SEQ_CRM_MODELE_OFERTA;

prompt
prompt	Stergere secventa SEQ_CRM_TAG_CONTRACT
prompt
prompt
drop sequence SEQ_CRM_TAG_CONTRACT;

prompt
prompt	Stergere secventa SEQ_CRM_TAG_OFERTA
prompt
prompt
drop sequence SEQ_CRM_TAG_OFERTA;

prompt
prompt	Stergere view CRM_VCOLOANE_CONTRACT
prompt
prompt
drop view CRM_VCOLOANE_CONTRACT;

prompt
prompt	Stergere view CRM_VMODELE_CONTRACT
prompt
prompt
drop view CRM_VMODELE_CONTRACT;

prompt
prompt	Stergere view CRM_VMODELE_OFERTA
prompt
prompt
drop view CRM_VMODELE_OFERTA;

prompt
prompt	Creare tabela CRM_TIP_MODEL_DOC
prompt
prompt

create table crm_tip_model_doc
(
  ID_TIP_MODEL_DOC NUMBER(10) not null,
  DENUMIRE         varchar2(100) not null,
  STERS            NUMBER(1) default 0 not null,
  ID_UTIL          NUMBER(10) not null,
  DATAORA          DATE default SYSDATE not null,
  ID_UTILS         NUMBER(10),
  DATAORAS         DATE
)
tablespace ROA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table crm_tip_model_doc
  add constraint PK_CRM_TIP_MODEL_DOC primary key (ID_TIP_MODEL_DOC)
  using index 
  tablespace ROA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table crm_tip_model_doc
  add constraint FK_CRM_TIP_MODEL_DOC_001 foreign key (ID_UTIL)
  references CONTAFIN_ORACLE.UTILIZATORI (ID_UTIL);
alter table crm_tip_model_doc
  add constraint FK_CRM_TIP_MODEL_DOC_002 foreign key (ID_UTILS)
  references CONTAFIN_ORACLE.UTILIZATORI (ID_UTIL);

prompt
prompt	Creare tabela CRM_MODELE_DOCUMENTE
prompt
prompt
-- Create table
create table crm_modele_documente
(
  ID_MODEL_DOC NUMBER(10) not null,
  id_tip_model NUMBER(10) not null,
  descriere    varchar2(100),
  link         varchar2(100),
  STERS        NUMBER(1) default 0 not null,
  ID_UTIL      NUMBER(10) not null,
  DATAORA      DATE default SYSDATE not null,
  ID_UTILS     NUMBER(10),
  DATAORAS     DATE
)
tablespace ROA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table crm_modele_documente
  add constraint PK_CRM_MODELE_DOC primary key (ID_MODEL_DOC)
  using index 
  tablespace ROA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table crm_modele_documente
  add constraint FK_CRM_MODELE_DOC_001 foreign key (ID_UTIL)
  references CONTAFIN_ORACLE.UTILIZATORI (ID_UTIL);
alter table crm_modele_documente
  add constraint FK_CRM_MODELE_DOC_002 foreign key (ID_UTILS)
  references CONTAFIN_ORACLE.UTILIZATORI (ID_UTIL);
alter table crm_modele_documente
  add constraint FK_CRM_MODELE_DOC_003 foreign key (ID_TIP_MODEL)
  references crm_tip_model_doc (ID_TIP_MODEL_DOC);

prompt
prompt	Creare tabela CRM_TABELE_COLOANE
prompt
prompt
-- Create table
create table CRM_TABELE_COLOANE
(
  ID_TABELA  NUMBER(10) not null,
  NUMETABELA VARCHAR2(100) not null,
  CHEIE      VARCHAR2(100) not null,
  ORDINE     VARCHAR2(100),
  STERS      NUMBER(1) default 0 not null,
  ID_UTIL    NUMBER(8) not null,
  DATAORA    DATE default SYSDATE not null,
  ID_UTILS   NUMBER(8),
  DATAORAS   DATE
)
tablespace ROA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table CRM_TABELE_COLOANE
  add constraint PK_CRM_TABELE_COLOANE primary key (ID_TABELA)
  using index 
  tablespace ROA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table CRM_TABELE_COLOANE
  add constraint FK_CRM_TABELE_COLOANE_001 foreign key (ID_UTIL)
  references CONTAFIN_ORACLE.UTILIZATORI (ID_UTIL);
alter table CRM_TABELE_COLOANE
  add constraint FK_CRM_TABELE_COLOANE_002 foreign key (ID_UTILS)
  references CONTAFIN_ORACLE.UTILIZATORI (ID_UTIL);
/

prompt
prompt	Creare tabela CRM_COLOANE_DOCUMENTE
prompt
prompt
-- Create table
create table CRM_COLOANE_DOCUMENTE
(
  ID_COLOANA_DOC   NUMBER(10) not null,
  NUMECOLOANA      VARCHAR2(100) not null,
  DESCRIERE        VARCHAR2(100),
  ID_TIP_MODEL_DOC NUMBER(10),
  ID_TABELA        NUMBER(10) not null,
  INACTIV          NUMBER(1) default 0 not null,
  STERS            NUMBER(1) default 0 not null,
  ID_UTIL          NUMBER(10) not null,
  DATAORA          DATE default SYSDATE not null,
  ID_UTILS         NUMBER(10),
  DATAORAS         DATE
)
tablespace ROA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table CRM_COLOANE_DOCUMENTE
  add constraint PK_CRM_COLOANE_DOC primary key (ID_COLOANA_DOC)
  using index 
  tablespace ROA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table CRM_COLOANE_DOCUMENTE
  add constraint FK_CRM_COLOANE_DOC_001 foreign key (ID_UTIL)
  references CONTAFIN_ORACLE.UTILIZATORI (ID_UTIL);
alter table CRM_COLOANE_DOCUMENTE
  add constraint FK_CRM_COLOANE_DOC_002 foreign key (ID_UTILS)
  references CONTAFIN_ORACLE.UTILIZATORI (ID_UTIL);
alter table CRM_COLOANE_DOCUMENTE
  add constraint FK_CRM_COLOANE_DOC_003 foreign key (ID_TIP_MODEL_DOC)
  references CRM_TIP_MODEL_DOC (ID_TIP_MODEL_DOC);
alter table CRM_COLOANE_DOCUMENTE
  add constraint FK_CRM_COLOANE_DOC_004 foreign key (ID_TABELA)
  references CRM_TABELE_COLOANE (ID_TABELA);

prompt
prompt	Creare tabela CRM_TIP_TAG_DOCUMENT
prompt
prompt
-- Create table
create table CRM_TIP_TAG_DOCUMENT
(
  ID_TIP_TAG_DOC NUMBER(10) not null,
  TIP_TAG        VARCHAR2(32),
  DESCRIERE      VARCHAR2(100),
  STERS          NUMBER(1) default 0 not null,
  ID_UTIL        NUMBER(10) not null,
  DATAORA        DATE default SYSDATE not null,
  ID_UTILS       NUMBER(10),
  DATAORAS       DATE,
  INACTIV        NUMBER(1) default 0 not null
)
tablespace ROA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table CRM_TIP_TAG_DOCUMENT
  add constraint PK_CRM_TIP_TAG_DOC primary key (ID_TIP_TAG_DOC)
  using index 
  tablespace ROA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table CRM_TIP_TAG_DOCUMENT
  add constraint FK_CRM_TIP_TAG_DOC_001 foreign key (ID_UTIL)
  references CONTAFIN_ORACLE.UTILIZATORI (ID_UTIL);
alter table CRM_TIP_TAG_DOCUMENT
  add constraint FK_CRM_TIP_TAG_DOC_002 foreign key (ID_UTILS)
  references CONTAFIN_ORACLE.UTILIZATORI (ID_UTIL);

prompt
prompt	Creare tabela CRM_ELEMENTE_DOCUMENT
prompt
prompt
-- Create table
create table CRM_ELEMENTE_DOCUMENT
(
  ID_ELEMENT_DOC NUMBER(10) not null,
  ID_MODEL_DOC   number(10) not null,
  ID_COLOANA_DOC NUMBER(10) not null,
  ID_TIP_TAG_DOC NUMBER(10) not null,
  POZITIE        number(5) not null,
  STERS          NUMBER(1) default 0 not null,
  ID_UTIL        NUMBER(10) not null,
  DATAORA        DATE default SYSDATE not null,
  ID_UTILS       NUMBER(10),
  DATAORAS       DATE
)
tablespace ROA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table CRM_ELEMENTE_DOCUMENT
  add constraint PK_CRM_ELEMENTE_DOC primary key (ID_ELEMENT_DOC)
  using index 
  tablespace ROA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table CRM_ELEMENTE_DOCUMENT
  add constraint FK_CRM_ELEMENTE_DOC_001 foreign key (ID_UTIL)
  references CONTAFIN_ORACLE.UTILIZATORI (ID_UTIL);
alter table CRM_ELEMENTE_DOCUMENT
  add constraint FK_CRM_ELEMENTE_DOC_002 foreign key (ID_UTILS)
  references CONTAFIN_ORACLE.UTILIZATORI (ID_UTIL);
alter table CRM_ELEMENTE_DOCUMENT
  add constraint FK_CRM_ELEMENTE_DOC_003 foreign key (ID_MODEL_DOC)
  references CRM_MODELE_DOCUMENTE (ID_MODEL_DOC);
alter table CRM_ELEMENTE_DOCUMENT
  add constraint FK_CRM_ELEMENTE_DOC_004 foreign key (ID_COLOANA_DOC)
  references CRM_COLOANE_DOCUMENTE (ID_COLOANA_DOC);
alter table CRM_ELEMENTE_DOCUMENT
  add constraint FK_CRM_ELEMENTE_DOC_005 foreign key (ID_TIP_TAG_DOC)
  references crm_tip_tag_document (ID_TIP_TAG_DOC);

prompt
prompt	Creare secventa SEQ_CRM_MODELE_DOC
prompt
prompt
-- Create sequence 
create sequence SEQ_CRM_MODELE_DOC
minvalue 1
maxvalue 99999999999999999999999999
start with 1
increment by 1
nocache;

prompt
prompt	Creare secventa SEQ_CRM_TIP_MODEL_DOC
prompt
prompt
-- Create sequence 
create sequence SEQ_CRM_TIP_MODEL_DOC
minvalue 3
maxvalue 99999999999999999999999999
start with 3
increment by 1
nocache;

prompt
prompt	Creare secventa SEQ_CRM_ELEMENTE_DOCUMENT
prompt
prompt
-- Create sequence 
create sequence SEQ_CRM_ELEMENTE_DOCUMENT
minvalue 1
maxvalue 99999999999999999999999999
start with 1
increment by 1
nocache;

prompt
prompt	Creare secventa SEQ_CRM_TIP_TAG_DOCUMENT
prompt
prompt
-- Create sequence 
create sequence SEQ_CRM_TIP_TAG_DOCUMENT
minvalue 5
maxvalue 99999999999999999999999999
start with 5
increment by 1
nocache;

prompt
prompt	Creare secventa SEQ_CRM_COLOANE_DOCUMENTE
prompt
prompt
-- Create sequence 
create sequence SEQ_CRM_COLOANE_DOCUMENTE
minvalue 1
maxvalue 99999999999999999999999999
start with 1
increment by 1
nocache;

prompt
prompt	Creare secventa SEQ_CRM_TABELE_COLOANE
prompt
prompt
create sequence SEQ_CRM_TABELE_COLOANE
minvalue 8
maxvalue 99999999999999999999999999
start with 8
increment by 1
nocache;

prompt
prompt	Creare trigger TRG_CRM_TABELE_COL_BEFOINS
prompt
prompt
create or replace trigger TRG_CRM_TABELE_COL_BEFOINS
  before insert on CRM_TABELE_COLOANE
  referencing old as old new as new
  for each row
begin
  select SEQ_CRM_TABELE_COLOANE.NEXTVAL into :new.ID_TABELA from dual;
end;

prompt
prompt	Creare trigger TRG_CRM_MODELE_DOC_BEFOINS
prompt
prompt
create or replace trigger TRG_CRM_MODELE_DOC_BEFOINS
  before insert on CRM_MODELE_DOCUMENTE
  referencing old as old new as new
  for each row
begin
  select SEQ_CRM_MODELE_DOC.NEXTVAL into :new.ID_MODEL_DOC from dual;
end;
/

prompt
prompt	Creare trigger TRG_CRM_TIP_MODEL_DOC_BEFOINS
prompt
prompt
create or replace trigger TRG_CRM_TIP_MODEL_DOC_BEFOINS
  before insert on CRM_TIP_MODEL_DOC
  referencing old as old new as new
  for each row
begin
  select SEQ_CRM_TIP_MODEL_DOC.NEXTVAL into :new.ID_TIP_MODEL_DOC from dual;
end;
/

prompt
prompt	Creare trigger TRG_CRM_ELEMENTE_DOC_BEFOINS
prompt
prompt
create or replace trigger TRG_CRM_ELEMENTE_DOC_BEFOINS
  before insert on CRM_ELEMENTE_DOCUMENT
  referencing old as old new as new
  for each row
begin
  select SEQ_CRM_ELEMENTE_DOCUMENT.NEXTVAL into :new.ID_ELEMENT_DOC from dual;
end;
/

prompt
prompt	Creare trigger TRG_CRM_TIP_TAG_DOC_BEFOINS
prompt
prompt
create or replace trigger TRG_CRM_TIP_TAG_DOC_BEFOINS
  before insert on CRM_TIP_TAG_DOCUMENT
  referencing old as old new as new
  for each row
begin
  select SEQ_CRM_TIP_TAG_DOCUMENT.NEXTVAL into :new.ID_TIP_TAG_DOC from dual;
end;
/

prompt
prompt	Creare trigger TRG_CRM_COLOANE_DOC_BEFOINS
prompt
prompt
create or replace trigger TRG_CRM_COLOANE_DOC_BEFOINS
  before insert on CRM_COLOANE_DOCUMENTE
  referencing old as old new as new
  for each row
begin
  select SEQ_CRM_COLOANE_DOCUMENTE.NEXTVAL into :new.ID_COLOANA_DOC from dual;
end;
/

prompt
prompt	Initializare CRM_TIP_MODEL_DOC
prompt
prompt
alter table CRM_TIP_MODEL_DOC disable all triggers;
delete from CRM_TIP_MODEL_DOC where id_tip_model_doc<3;
insert into crm_tip_model_doc(id_tip_model_doc,denumire,id_util) values(1,'OFERTA',1);
insert into crm_tip_model_doc(id_tip_model_doc,denumire,id_util) values(2,'CONTRACT',1);
alter table CRM_TIP_MODEL_DOC enable all triggers;

prompt
prompt	Creare view VNOM_ARTICOLE_CRM
prompt
prompt
create or replace view vnom_articole_crm as
select A.ID_ARTICOL,
       A.CODMAT,
       A.DENUMIRE,
       A.UM,
       A.COMISION,
       A.INACTIV,
       A.CODBARE,
       A.IN_STOC,
       A.IN_CRM,
       A.ID_CATEG,
       C.DENUMIRE AS DEN_CATEG
  from NOM_ARTICOLE A
  JOIN VCRM_CATEG_ARTICOLE C ON C.ID_CATEG = A.ID_CATEG
 where A.STERS = 0
   and in_crm = 1
/

prompt
prompt	Creare view CRM_VMODELE_CONTRACT
prompt
prompt
create or replace view crm_vmodele_contract as
select a.id_model_doc,
       a.descriere,
       a.link,
       a.id_util,
       a.dataora,
       a.id_tip_model,
       b.utilizator
  from crm_modele_documente a
  left join syn_utilizatori b on a.id_util = b.id_util
 where a.sters = 0
   and a.id_tip_model = 2
/

prompt
prompt	Creare view CRM_VMODELE_OFERTA
prompt
prompt
create or replace view crm_vmodele_oferta as
select a.id_model_doc,
       a.descriere,
       a.link,
       a.id_util,
       a.dataora,
       a.id_tip_model,
       b.utilizator
  from crm_modele_documente a
  left join syn_utilizatori b on a.id_util = b.id_util
 where a.sters = 0
   and a.id_tip_model = 1
/

prompt
prompt	Modificare tabela CRM_OFERTE
prompt
prompt
-- Add/modify columns 
alter table CRM_OFERTE add ID_MODEL_DOC number(10);
-- Create/Recreate primary, unique and foreign key constraints 
alter table CRM_OFERTE
  add constraint FK_CRM_OFERTE_007 foreign key (ID_MODEL_DOC)
  references crm_modele_documente (ID_MODEL_DOC);

prompt
prompt	Modificare tabela CRM_CONTRACTE_OFERTA
prompt
prompt
-- Add/modify columns 
alter table CRM_CONTRACTE_OFERTA add ID_MODEL_DOC number(10);
-- Create/Recreate primary, unique and foreign key constraints 
alter table CRM_CONTRACTE_OFERTA
  add constraint FK_CRM_CTR_OFERTA_007 foreign key (ID_MODEL_DOC)
  references crm_modele_documente (ID_MODEL_DOC);

prompt
prompt	Modificare tabela CRM_VTIP_TAG_DOCUMENT
prompt
prompt
create or replace view crm_vtip_tag_document as
select a.id_tip_tag_doc,
       a.tip_tag,
       a.descriere,
       a.inactiv
  from crm_tip_tag_document a
  where a.sters=0
/

prompt
prompt	Creare trigger TRG_CRM_TIP_TAG_DOC_BEFOUPD
prompt
prompt
CREATE OR REPLACE TRIGGER TRG_CRM_TIP_TAG_DOC_BEFOUPD
BEFORE UPDATE ON CRM_TIP_TAG_DOCUMENT
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
begin

  if :old.ID_TIP_TAG_DOC <> :new.ID_TIP_TAG_DOC then
    INSERT INTO LOG
      (tabel, id_tabel, camp, tip_camp, valoarev, valoaren, id_util)
    values
      ('CRM_TIP_TAG_DOCUMENT',
       :OLD.ID_TIP_TAG_DOC,
       'ID_TIP_TAG_DOC',
       'N',
       :OLD.ID_TIP_TAG_DOC,
       :NEW.ID_TIP_TAG_DOC,
       PACK_SESIUNE.get_id_util());
  end if;

  if :old.TIP_TAG <> :new.TIP_TAG then
    INSERT INTO LOG
      (tabel, id_tabel, camp, tip_camp, valoarev, valoaren, id_util)
    values
      ('CRM_TIP_TAG_DOCUMENT',
       :OLD.ID_TIP_TAG_DOC,
       'TIP_TAG',
       'C',
       :OLD.TIP_TAG,
       :NEW.TIP_TAG,
       PACK_SESIUNE.get_id_util());
  end if;

  if :old.DESCRIERE <> :new.DESCRIERE then
    INSERT INTO LOG
      (tabel, id_tabel, camp, tip_camp, valoarev, valoaren, id_util)
    values
      ('CRM_TIP_TAG_DOCUMENT',
       :OLD.ID_TIP_TAG_DOC,
       'DESCRIERE',
       'C',
       :OLD.DESCRIERE,
       :NEW.DESCRIERE,
       PACK_SESIUNE.get_id_util());
  end if;

  if :old.INACTIV <> :new.INACTIV then
    INSERT INTO LOG
      (tabel, id_tabel, camp, tip_camp, valoarev, valoaren, id_util)
    values
      ('CRM_TIP_TAG_DOCUMENT',
       :OLD.ID_TIP_TAG_DOC,
       'INACTIV',
       'N',
       :OLD.INACTIV,
       :NEW.INACTIV,
       PACK_SESIUNE.GET_ID_UTIL());
  end if;

  if :old.STERS <> :new.STERS then
    INSERT INTO LOG
      (tabel, id_tabel, camp, tip_camp, valoarev, valoaren, id_util)
    values
      ('CRM_TIP_TAG_DOCUMENT',
       :OLD.ID_TIP_TAG_DOC,
       'STERS',
       'N',
       :OLD.STERS,
       :NEW.STERS,
       PACK_SESIUNE.GET_ID_UTIL());
  end if;

  if :old.DATAORA <> :new.DATAORA then
    INSERT INTO LOG
      (tabel, id_tabel, camp, tip_camp, valoarev, valoaren, id_util)
    values
      ('CRM_TIP_TAG_DOCUMENT',
       :OLD.ID_TIP_TAG_DOC,
       'DATAORA',
       'D',
       :OLD.DATAORA,
       :NEW.DATAORA,
       PACK_SESIUNE.GET_ID_UTIL());
  end if;

  if :old.ID_UTIL <> :new.ID_UTIL then
    INSERT INTO LOG
      (tabel, id_tabel, camp, tip_camp, valoarev, valoaren, id_util)
    values
      ('CRM_TIP_TAG_DOCUMENT',
       :OLD.ID_TIP_TAG_DOC,
       'ID_UTIL',
       'N',
       :OLD.ID_UTIL,
       :NEW.ID_UTIL,
       PACK_SESIUNE.GET_ID_UTIL());
  end if;

  if :old.DATAORAS <> :new.DATAORAS then
    INSERT INTO LOG
      (tabel, id_tabel, camp, tip_camp, valoarev, valoaren, id_util)
    values
      ('CRM_TIP_TAG_DOCUMENT',
       :OLD.ID_TIP_TAG_DOC,
       'DATAORAS',
       'D',
       :OLD.DATAORAS,
       :NEW.DATAORAS,
       PACK_SESIUNE.GET_ID_UTIL());
  end if;

  if :old.ID_UTILS <> :new.ID_UTILS then
    INSERT INTO LOG
      (tabel, id_tabel, camp, tip_camp, valoarev, valoaren, id_util)
    values
      ('CRM_TIP_TAG_DOCUMENT',
       :OLD.ID_TIP_TAG_DOC,
       'ID_UTILS',
       'N',
       :OLD.ID_UTILS,
       :NEW.ID_UTILS,
       PACK_SESIUNE.GET_ID_UTIL());
  end if;
end;

prompt
prompt	Creare view CRM_VCOLOANE_DOCUMENTE
prompt
prompt
create or replace view crm_vcoloane_documente as
select a.id_coloana_doc,
       a.id_tabela,
       a.numecoloana,
       a.descriere,
       a.inactiv,
       a.id_util,
       a.dataora,
       a.id_tip_model_doc,
       b.denumire,
       c.numetabela,
       c.cheie,
       c.ordine,
       d.utilizator
   from crm_coloane_documente a
   left join crm_tip_model_doc b on a.id_tip_model_doc=b.id_tip_model_doc
   left join crm_tabele_coloane c on a.id_tabela=c.id_tabela
   left join syn_utilizatori d on a.id_util=d.id_util
   where a.sters=0
/

prompt
prompt	Creare type StringAggType
prompt
prompt
create or replace type StringAggType as object
(
  theString varchar2(4000),

  static function ODCIAggregateInitialize(sctx IN OUT StringAggType)
    return number,

  member function ODCIAggregateIterate(self  IN OUT StringAggType,
                                       value IN varchar2) return number,

  member function ODCIAggregateTerminate(self        IN StringAggType,
                                         returnValue OUT varchar2,
                                         flags       IN number)
    return number,

  member function ODCIAggregateMerge(self IN OUT StringAggType,
                                     ctx2 IN StringAggType) return number)
/

prompt
prompt	Creare type body StringAggType
prompt
prompt
create or replace type body StringAggType is

  static function ODCIAggregateInitialize(sctx IN OUT StringAggType)
    return number is
  begin
    sctx := StringAggType(null);
    return ODCIConst.Success;
  end;

  member function ODCIAggregateIterate(self  IN OUT StringAggType,
                                       value IN varchar2) return number is
  begin
    self.theString := self.theString || ',' || value;
    return ODCIConst.Success;
  end;

  member function ODCIAggregateTerminate(self        IN StringAggType,
                                         returnValue OUT varchar2,
                                         flags       IN number) return number is
  begin
    returnValue := rtrim(ltrim(self.theString, ','), ',');
    return ODCIConst.Success;
  end;

  member function ODCIAggregateMerge(self IN OUT StringAggType,
                                     ctx2 IN StringAggType) return number is
  begin
    self.theString := self.theString || ',' || ctx2.theString;
    return ODCIConst.Success;
  end;

end;
/

prompt
prompt	Creare functie stringAgg
prompt
prompt
CREATE OR REPLACE FUNCTION stringAgg(input varchar2) RETURN varchar2
  PARALLEL_ENABLE
  AGGREGATE USING StringAggType;
/

prompt
prompt	Creare view CRM_VELEMENTE_DOCUMENT
prompt
prompt
create or replace view CRM_VELEMENTE_DOCUMENT as
select a.id_model_doc,
       a.id_coloana_doc,
       a.id_tip_tag_doc,
       stringAgg(pozitie) as pozitie,
       b.tip_tag
  from crm_elemente_document a
  left join crm_tip_tag_document b on a.id_tip_tag_doc = b.id_tip_tag_doc
 where a.sters = 0
 group by a.id_model_doc, a.id_coloana_doc, a.id_tip_tag_doc, b.tip_tag
/

prompt
prompt	Creare package PACK_OFERTA
prompt
prompt
create or replace package PACK_OFERTA is

  -- Author  : MARIUS.ATANASIU
  -- Created : 2/17/2006 11:00:00 AM
  -- Purpose : 

  procedure adauga_zona(V_SCHEMA  IN VARCHAR2,
                        V_ZONA    IN VARCHAR2,
                        V_INACTIV IN NUMBER,
                        V_ID_UTIL IN NUMBER);

  procedure modifica_zona(V_SCHEMA  IN VARCHAR2,
                          V_ID_ZONA IN NUMBER,
                          V_ZONA    IN VARCHAR2,
                          V_INACTIV IN NUMBER,
                          V_ID_UTIL IN NUMBER);

  procedure sterge_zona(V_SCHEMA  IN VARCHAR2,
                        V_ID_ZONA IN NUMBER,
                        V_ID_UTIL IN NUMBER);

  procedure adauga_agent(V_SCHEMA   IN VARCHAR2,
                         V_NUME     IN VARCHAR2,
                         V_ID_ZONA  IN NUMBER,
                         V_FUNCTIA  IN VARCHAR2,
                         V_TELEFON1 IN VARCHAR2,
                         V_TELEFON2 IN VARCHAR2,
                         V_ID_UTIL  IN NUMBER);

  procedure modifica_agent(V_SCHEMA   IN VARCHAR2,
                           V_ID_AGENT IN NUMBER,
                           V_NUME     IN VARCHAR2,
                           V_ID_ZONA  IN NUMBER,
                           V_FUNCTIA  IN VARCHAR2,
                           V_TELEFON1 IN VARCHAR2,
                           V_TELEFON2 IN VARCHAR2,
                           V_ID_UTIL  IN NUMBER);

  procedure sterge_agent(V_SCHEMA   IN VARCHAR2,
                         V_ID_AGENT IN NUMBER,
                         V_ID_UTIL  IN NUMBER);

  procedure adauga_articol(V_SCHEMA   IN VARCHAR2,
                           V_CODMAT   IN VARCHAR2,
                           V_DENUMIRE IN VARCHAR2,
                           V_UM       IN VARCHAR2,
                           V_ID_CATEG IN NUMBER,
                           V_INACTIV  IN NUMBER,
                           V_ID_UTIL  IN NUMBER);

  procedure modifica_articol(V_SCHEMA     IN VARCHAR2,
                             V_ID_ARTICOL IN NUMBER,
                             V_CODMAT     IN VARCHAR2,
                             V_DENUMIRE   IN VARCHAR2,
                             V_UM         IN VARCHAR2,
                             V_ID_CATEG   IN NUMBER,
                             V_INACTIV    IN NUMBER,
                             V_ID_UTIL    IN NUMBER);

  procedure sterge_articol(V_SCHEMA     IN VARCHAR2,
                           V_ID_ARTICOL IN NUMBER,
                           V_ID_UTIL    IN NUMBER);

  procedure adauga_politica(V_SCHEMA    IN VARCHAR2,
                            V_NUME      IN VARCHAR2,
                            V_DATAI     IN DATE,
                            V_DATAS     IN DATE,
                            V_ID_VALUTA IN NUMBER,
                            V_SIR       IN VARCHAR2,
                            V_ID_UTIL   IN NUMBER);

  procedure modifica_politica(V_SCHEMA    IN VARCHAR2,
                              V_ID_POL    IN NUMBER,
                              V_NUME      IN VARCHAR2,
                              V_DATAI     IN DATE,
                              V_DATAS     IN DATE,
                              V_ID_VALUTA IN NUMBER,
                              V_SIR       IN VARCHAR2,
                              V_ID_UTIL   IN NUMBER);

  procedure sterge_politica(V_SCHEMA  IN VARCHAR2,
                            V_ID_POL  IN NUMBER,
                            V_ID_UTIL IN NUMBER);

  procedure adauga_curs(tcSchema    varchar2,
                        tnCurs      number,
                        tdData      date,
                        tdData2     date,
                        tnId_valuta number);

  procedure modifica_curs(tcSchema    varchar2,
                          tnId_curs   number,
                          tnCurs      number,
                          tdData      date,
                          tdData2     date,
                          tnId_valuta number);

  procedure sterge_curs(tcSchema varchar2, tnId_curs number);

  procedure adauga_nota_vanzari(tcSchema   varchar2,
                                tcDenumire varchar2,
                                tcScd      varchar2,
                                tcAscd     varchar2,
                                tcScc      varchar2,
                                tcAscc     varchar2,
                                tnId_util  number);

  procedure modifica_nota_vanzari(tcSchema   varchar2,
                                  tnId_nota  number,
                                  tcDenumire varchar2,
                                  tcScd      varchar2,
                                  tcAscd     varchar2,
                                  tcScc      varchar2,
                                  tcAscc     varchar2,
                                  tnId_util  number);

  procedure sterge_nota_vanzari(tcSchema   varchar2,
                                tnId_nota  number,
                                tnId_utils number);

  procedure adauga_categ_art(tcSchema   varchar2,
                             tcDenumire varchar2,
                             tnId_nota  number,
                             tnId_util  number);

  procedure modifica_categ_art(tcSchema   varchar2,
                               tnId_categ number,
                               tcDenumire varchar2,
                               tnId_nota  number,
                               tnId_util  number);

  procedure sterge_categ_art(tcSchema   varchar2,
                             tnId_categ number,
                             tnId_utils number);

  procedure adauga_categ_ctr(tcSchema     varchar2,
                             tcCateg_ctr  varchar2,
                             tcExplicatie varchar2,
                             tnId_util    number);

  procedure modifica_categ_ctr(tcSchema       varchar2,
                               tnId_categ_ctr number,
                               tcCateg_ctr    varchar2,
                               tcExplicatie   varchar2,
                               tnId_util      number);

  procedure sterge_categ_ctr(tcSchema       varchar2,
                             tnId_categ_ctr number,
                             tnId_utils     number);

  procedure adauga_tip_tag(V_SCHEMA    IN VARCHAR2,
                           V_TIP_TAG   IN CRM_TIP_TAG_DOCUMENT.TIP_TAG%TYPE,
                           V_DESCRIERE IN CRM_TIP_TAG_DOCUMENT.DESCRIERE%TYPE,
                           V_INACTIV   IN CRM_TIP_TAG_DOCUMENT.INACTIV%TYPE,
                           V_ID_UTIL   IN SYN_UTILIZATORI.ID_UTIL%TYPE);

  procedure modifica_tip_tag(V_SCHEMA         IN VARCHAR2,
                             V_ID_TIP_TAG_DOC IN CRM_TIP_TAG_DOCUMENT.ID_TIP_TAG_DOC%TYPE,
                             V_TIP_TAG        IN CRM_TIP_TAG_DOCUMENT.TIP_TAG%TYPE,
                             V_DESCRIERE      IN CRM_TIP_TAG_DOCUMENT.DESCRIERE%TYPE,
                             V_INACTIV        IN CRM_TIP_TAG_DOCUMENT.INACTIV%TYPE);

  procedure sterge_tip_tag(V_SCHEMA         IN VARCHAR2,
                           V_ID_TIP_TAG_DOC IN CRM_TIP_TAG_DOCUMENT.ID_TIP_TAG_DOC%TYPE,
                           V_ID_UTIL        IN SYN_UTILIZATORI.ID_UTIL%TYPE);

  procedure adauga_model_document(V_SCHEMA       IN VARCHAR2,
                                  V_ID_TIP_MODEL IN NUMBER,
                                  V_DESCRIERE    IN VARCHAR2,
                                  V_LINK         IN VARCHAR2,
                                  V_SIR          IN VARCHAR2,
                                  V_ID_UTIL      IN NUMBER);

  procedure modifica_model_document(V_SCHEMA       IN VARCHAR2,
                                    V_ID_MODEL_DOC IN NUMBER,
                                    V_ID_TIP_MODEL IN NUMBER,
                                    V_DESCRIERE    IN VARCHAR2,
                                    V_LINK         IN VARCHAR2,
                                    V_SIR          IN VARCHAR2,
                                    V_ID_UTIL      IN NUMBER);

  procedure sterge_model_document(V_SCHEMA       IN VARCHAR2,
                                  V_ID_MODEL_DOC IN NUMBER,
                                  V_ID_UTIL      IN NUMBER);
end PACK_OFERTA;
/

prompt
prompt	Creare package body PACK_OFERTA
prompt
prompt
create or replace package body PACK_OFERTA is
  procedure adauga_zona(V_SCHEMA  IN VARCHAR2,
                        V_ZONA    IN VARCHAR2,
                        V_INACTIV IN NUMBER,
                        V_ID_UTIL IN NUMBER) is
  begin
    EXECUTE IMMEDIATE 'INSERT INTO ' || V_SCHEMA || '.CRM_ZONE ' ||
                      '(ZONA,INACTIV,ID_UTIL) VALUES (:1,:2,:3)'
      USING V_ZONA, V_INACTIV, V_ID_UTIL;
  end;
  -------------------------------------------------------------------------------------------
  procedure modifica_zona(V_SCHEMA  IN VARCHAR2,
                          V_ID_ZONA IN NUMBER,
                          V_ZONA    IN VARCHAR2,
                          V_INACTIV IN NUMBER,
                          V_ID_UTIL IN NUMBER) is
  begin
    EXECUTE IMMEDIATE 'UPDATE ' || V_SCHEMA || '.CRM_ZONE ' ||
                      'SET ZONA=:1,INACTIV=:2,ID_UTIL=:3,DATAORA=:4 ' ||
                      'WHERE ID_ZONA=:5'
      USING V_ZONA, V_INACTIV, V_ID_UTIL, SYSDATE, V_ID_ZONA;
  end;
  -------------------------------------------------------------------------------------------
  procedure sterge_zona(V_SCHEMA  IN VARCHAR2,
                        V_ID_ZONA IN NUMBER,
                        V_ID_UTIL IN NUMBER) is
  begin
    EXECUTE IMMEDIATE 'UPDATE ' || V_SCHEMA || '.CRM_ZONE ' ||
                      'SET STERS=:1,DATAORAS=:2,ID_UTILS=:3 ' ||
                      'WHERE ID_ZONA=:4'
      USING 1, SYSDATE, V_ID_UTIL, V_ID_ZONA;
  end;
  -------------------------------------------------------------------------------------------
  procedure adauga_agent(V_SCHEMA   IN VARCHAR2,
                         V_NUME     IN VARCHAR2,
                         V_ID_ZONA  IN NUMBER,
                         V_FUNCTIA  IN VARCHAR2,
                         V_TELEFON1 IN VARCHAR2,
                         V_TELEFON2 IN VARCHAR2,
                         V_ID_UTIL  IN NUMBER) is
    V_E_AGENT number(1);
  begin
    V_E_AGENT := 1;
    EXECUTE IMMEDIATE 'INSERT INTO ' || V_SCHEMA || '.NOM_RESPONSABILI ' ||
                      '(NUME,ID_ZONA,FUNCTIE,TELEFON1,TELEFON2,ID_UTIL,E_AGENT) ' ||
                      'VALUES(:1,:2,:3,:4,:5,:6,:7)'
      USING V_NUME, V_ID_ZONA, V_FUNCTIA, V_TELEFON1, V_TELEFON2, V_ID_UTIL, V_E_AGENT;
  end;
  -------------------------------------------------------------------------------------------           
  procedure modifica_agent(V_SCHEMA   IN VARCHAR2,
                           V_ID_AGENT IN NUMBER,
                           V_NUME     IN VARCHAR2,
                           V_ID_ZONA  IN NUMBER,
                           V_FUNCTIA  IN VARCHAR2,
                           V_TELEFON1 IN VARCHAR2,
                           V_TELEFON2 IN VARCHAR2,
                           V_ID_UTIL  IN NUMBER) is
    V_E_AGENT number(1);
  begin
    V_E_AGENT := 1;
    EXECUTE IMMEDIATE 'UPDATE ' || V_SCHEMA || '.NOM_RESPONSABILI ' ||
                      'SET NUME=:1,FUNCTIE=:2,ID_ZONA=:3,' ||
                      'TELEFON1=:4,TELEFON2=:5,ID_UTIL=:6,' ||
                      'DATAORA=:7 WHERE ID_RESPONSABIL=:8 AND E_AGENT=:9'
      USING V_NUME, V_FUNCTIA, V_ID_ZONA, V_TELEFON1, V_TELEFON2, V_ID_UTIL, SYSDATE, V_ID_AGENT, V_E_AGENT;
  end;
  -------------------------------------------------------------------------------------------
  procedure sterge_agent(V_SCHEMA   IN VARCHAR2,
                         V_ID_AGENT IN NUMBER,
                         V_ID_UTIL  IN NUMBER) is
    V_NUMAR_INREGISTRARI NUMBER(10);
    V_E_AGENT            NUMBER(1);
  begin
    V_E_AGENT := 1;
    RAISE_APPLICATION_ERROR(-20000, 'N/A');
    EXECUTE IMMEDIATE 'SELECT /*+ NO_INDEX */ COUNT(*) FROM ' || V_SCHEMA ||
                      '.CRM_EVENIMENTE A JOIN ' || V_SCHEMA ||
                      '.CRM_OFERTE B ON A.ID_RESPONSABIL=B.ID_RESPONSABIL ' ||
                      'LEFT JOIN ' || V_SCHEMA ||
                      '.CRM_CONTRACTE_OFERTA C ON B.ID_RESPONSABIL=C.ID_RESPONSABIL ' ||
                      'WHERE A.ID_RESPONSABIL=:1 OR B.ID_RESPONSABIL=:2 OR C.ID_RESPONSABIL=:3'
      INTO V_NUMAR_INREGISTRARI
      USING V_ID_AGENT, V_ID_AGENT, V_ID_AGENT;
  
    IF V_NUMAR_INREGISTRARI <> 0 THEN
      RAISE_APPLICATION_ERROR(-20000,
                              'Nu puteti sterge acest agent, deoarece exista inregistrari pe numele sau!');
    END IF;
    EXECUTE IMMEDIATE 'UPDATE ' || V_SCHEMA || '.NOM_RESPONSABILI ' ||
                      'SET STERS=:1,ID_UTIL=:2,DATAORA=:3 ' ||
                      'WHERE ID_RESPONSABIL=:4 AND E_AGENT=:5'
      USING 1, V_ID_UTIL, SYSDATE, V_ID_AGENT, V_E_AGENT;
  end;
  -------------------------------------------------------------------------------------------
  procedure adauga_articol(V_SCHEMA   IN VARCHAR2,
                           V_CODMAT   IN VARCHAR2,
                           V_DENUMIRE IN VARCHAR2,
                           V_UM       IN VARCHAR2,
                           V_ID_CATEG IN NUMBER,
                           V_INACTIV  IN NUMBER,
                           V_ID_UTIL  IN NUMBER) is
  begin
    EXECUTE IMMEDIATE 'INSERT INTO ' || V_SCHEMA ||
                      '.NOM_ARTICOLE (CODMAT,DENUMIRE,UM,ID_CATEG,INACTIV,IN_CRM,IN_STOC) ' ||
                      'VALUES (:1,:2,:3,:4,:5,:6,:7)'
      USING V_CODMAT, V_DENUMIRE, V_UM, V_ID_CATEG, V_INACTIV, 1, 0;
  end;
  -------------------------------------------------------------------------------------------
  procedure modifica_articol(V_SCHEMA     IN VARCHAR2,
                             V_ID_ARTICOL IN NUMBER,
                             V_CODMAT     IN VARCHAR2,
                             V_DENUMIRE   IN VARCHAR2,
                             V_UM         IN VARCHAR2,
                             V_ID_CATEG   IN NUMBER,
                             V_INACTIV    IN NUMBER,
                             V_ID_UTIL    IN NUMBER) is
  begin
    EXECUTE IMMEDIATE 'UPDATE ' || V_SCHEMA ||
                      '.NOM_ARTICOLE SET CODMAT=:1,' ||
                      'DENUMIRE=:2,UM=:3,
                      ID_CATEG=:4,INACTIV=:5 WHERE ID_ARTICOL=:6'
      USING V_CODMAT, V_DENUMIRE, V_UM, V_ID_CATEG, V_INACTIV, V_ID_ARTICOL;
  end;
  -------------------------------------------------------------------------------------------
  procedure sterge_articol(V_SCHEMA     IN VARCHAR2,
                           V_ID_ARTICOL IN NUMBER,
                           V_ID_UTIL    IN NUMBER) is
  begin
    EXECUTE IMMEDIATE 'UPDATE ' || V_SCHEMA ||
                      '.NOM_ARTICOLE SET STERS=:1 WHERE ID_ARTICOL=:2'
      USING 1, V_ID_ARTICOL;
  end;
  -------------------------------------------------------------------------------------------
  procedure adauga_politica(V_SCHEMA    IN VARCHAR2,
                            V_NUME      IN VARCHAR2,
                            V_DATAI     IN DATE,
                            V_DATAS     IN DATE,
                            V_ID_VALUTA IN NUMBER,
                            V_SIR       IN VARCHAR2,
                            V_ID_UTIL   IN NUMBER) is
    V_ID_POL      NUMBER(10);
    V_LUNGIME_SIR NUMBER(10);
    V_ELEMENT     VARCHAR2(100);
    V_ID_ARTICOL  NUMBER(10);
    V_UM          VARCHAR2(10);
    V_PRET        NUMBER(10);
  begin
    EXECUTE IMMEDIATE 'INSERT INTO ' || V_SCHEMA ||
                      '.CRM_POLITICI_PRETURI ' ||
                      '(NUME,DATAI,DATAS,ID_VALUTA,ID_UTIL) VALUES(:1,:2,:3,:4,:5) ' ||
                      'RETURNING ID_POL INTO :6'
      USING V_NUME, V_DATAI, V_DATAS, V_ID_VALUTA, V_ID_UTIL
      RETURNING INTO V_ID_POL;
  
    IF V_SIR IS NOT NULL THEN
      V_LUNGIME_SIR := SIR_IN_SIR(V_SIR, ';');
    
      FOR i IN 1 .. V_LUNGIME_SIR LOOP
        V_ELEMENT    := ELEMENT_DIN_SIR(V_SIR, ';', i) || ',';
        V_ID_ARTICOL := TO_NUMBER(ELEMENT_DIN_SIR(V_ELEMENT, ',', 1));
        V_UM         := ELEMENT_DIN_SIR(V_ELEMENT, ',', 2);
        V_PRET       := TO_NUMBER(TRANSLATE(ELEMENT_DIN_SIR(V_ELEMENT,
                                                            ',',
                                                            3),
                                            ',',
                                            '.'));
      
        EXECUTE IMMEDIATE 'INSERT INTO ' || V_SCHEMA ||
                          '.CRM_POLITICI_PRET_ART (ID_POL,ID_ART,PRET,UM,ID_VALUTA,ID_UTIL) ' ||
                          'VALUES(:1,:2,:3,:4,:5,:6)'
          USING V_ID_POL, V_ID_ARTICOL, V_PRET, V_UM, V_ID_VALUTA, V_ID_UTIL;
      END LOOP;
    
    END IF;
  end;
  -------------------------------------------------------------------------------------------
  procedure modifica_politica(V_SCHEMA    IN VARCHAR2,
                              V_ID_POL    IN NUMBER,
                              V_NUME      IN VARCHAR2,
                              V_DATAI     IN DATE,
                              V_DATAS     IN DATE,
                              V_ID_VALUTA IN NUMBER,
                              V_SIR       IN VARCHAR2,
                              V_ID_UTIL   IN NUMBER) is
  begin
    pack_oferta.sterge_politica(V_SCHEMA, V_ID_POL, V_ID_UTIL);
    pack_oferta.adauga_politica(V_SCHEMA,
                                V_NUME,
                                V_DATAI,
                                V_DATAS,
                                V_ID_VALUTA,
                                V_SIR,
                                V_ID_UTIL);
  end;
  -------------------------------------------------------------------------------------------
  procedure sterge_politica(V_SCHEMA  IN VARCHAR2,
                            V_ID_POL  IN NUMBER,
                            V_ID_UTIL IN NUMBER) is
  begin
    EXECUTE IMMEDIATE 'UPDATE ' || V_SCHEMA || '.CRM_POLITICI_PRETURI ' ||
                      'SET STERS=:1,DATAORAS=:2,ID_UTILS=:3 WHERE ID_POL=:4'
      USING 1, SYSDATE, V_ID_UTIL, V_ID_POL;
  
    EXECUTE IMMEDIATE 'UPDATE ' || V_SCHEMA || '.CRM_POLITICI_PRET_ART ' ||
                      'SET STERS=:1,DATAORAS=:2,ID_UTILS=:3 WHERE ID_POL=:4'
      USING 1, SYSDATE, V_ID_UTIL, V_ID_POL;
  end;
  -------------------------------------------------------------------------------------------
  procedure adauga_curs(tcSchema    varchar2,
                        tnCurs      number,
                        tdData      date,
                        tdData2     date,
                        tnId_valuta number) is
  
  begin
    execute immediate 'insert into ' || tcSchema ||
                      '.curs
(curs, data, data2, id_valuta) values (:1,:2,:3,:4)'
      using tnCurs, tdData, tdData2, tnId_valuta;
  end adauga_curs;
  -------------------------------------------------------------------------------------------  
  procedure modifica_curs(tcSchema    varchar2,
                          tnId_curs   number,
                          tnCurs      number,
                          tdData      date,
                          tdData2     date,
                          tnId_valuta number) is
  
  begin
    execute immediate 'update  ' || tcSchema ||
                      '.curs
                      set curs=:1, data=:2, data2=:3, id_valuta=:4
                      where id_curs=:5'
      using tnCurs, tdData, tdData2, tnId_valuta, tnId_curs;
  end modifica_curs;
  -------------------------------------------------------------------------------------------
  procedure sterge_curs(tcSchema varchar2, tnId_curs number) is
  begin
    execute immediate 'update  ' || tcSchema ||
                      '.curs
                      set sters=1 where id_curs=:1'
      using tnId_curs;
  end sterge_curs;
  -------------------------------------------------------------------------------------------
  procedure adauga_nota_vanzari(tcSchema   varchar2,
                                tcDenumire varchar2,
                                tcScd      varchar2,
                                tcAscd     varchar2,
                                tcScc      varchar2,
                                tcAscc     varchar2,
                                tnId_util  number) is
  
  begin
    execute immediate 'insert into ' || tcSchema ||
                      '.crm_note_vanzari
  (denumire, scd, ascd, scc, ascc, id_util, dataora)
  values (:1, :2, :3, :4, :5, :6, :7)'
      using tcDenumire, tcScd, tcAscd, tcScc, tcAscc, tnId_util, SYSDATE;
  end adauga_nota_vanzari;
  -------------------------------------------------------------------------------------------
  procedure modifica_nota_vanzari(tcSchema   varchar2,
                                  tnId_nota  number,
                                  tcDenumire varchar2,
                                  tcScd      varchar2,
                                  tcAscd     varchar2,
                                  tcScc      varchar2,
                                  tcAscc     varchar2,
                                  tnId_util  number) is
  begin
    execute immediate 'update ' || tcSchema || '.crm_note_vanzari
  set denumire=:1, scd=:2, ascd=:3, scc=:4, ascc=:5, id_util=:6, dataora=:7
  where id_nota=:8'
      using tcDenumire, tcScd, tcAscd, tcScc, tcAscc, tnId_util, SYSDATE, tnId_nota;
  
  end modifica_nota_vanzari;
  -------------------------------------------------------------------------------------------
  procedure sterge_nota_vanzari(tcSchema   varchar2,
                                tnId_nota  number,
                                tnId_utils number) is
  begin
    execute immediate 'update ' || tcSchema || '.crm_note_vanzari
  set sters=:1, id_utils=:2, dataoras=:3
   where id_nota=:4'
      using 1, tnId_utils, SYSDATE, tnId_nota;
  end sterge_nota_vanzari;
  -------------------------------------------------------------------------------------------
  procedure adauga_categ_art(tcSchema   varchar2,
                             tcDenumire varchar2,
                             tnId_nota  number,
                             tnId_util  number) is
  
  begin
    execute immediate 'insert into ' || tcSchema ||
                      '.crm_categ_articole
  (denumire, id_nota, id_util, dataora)
  values (:1, :2, :3, :4)'
      using tcDenumire, tnId_nota, tnId_util, SYSDATE;
  end adauga_categ_art;
  -------------------------------------------------------------------------------------------
  procedure modifica_categ_art(tcSchema   varchar2,
                               tnId_categ number,
                               tcDenumire varchar2,
                               tnId_nota  number,
                               tnId_util  number) is
  begin
    execute immediate 'update ' || tcSchema || '.crm_categ_articole
  set denumire=:1, id_nota=:2, id_util=:3, dataora=:4
  where id_categ=:5'
      using tcDenumire, tnId_nota, tnId_util, SYSDATE, tnId_categ;
  
  end modifica_categ_art;
  -------------------------------------------------------------------------------------------
  procedure sterge_categ_art(tcSchema   varchar2,
                             tnId_categ number,
                             tnId_utils number) is
  begin
    execute immediate 'update ' || tcSchema || '.crm_categ_articole
  set sters=:1, id_utils=:2, dataoras=:3
   where id_categ=:4'
      using 1, tnId_utils, SYSDATE, tnId_categ;
  end sterge_categ_art;
  -------------------------------------------------------------------------------------------
  procedure adauga_categ_ctr(tcSchema     varchar2,
                             tcCateg_ctr  varchar2,
                             tcExplicatie varchar2,
                             tnId_util    number) is
  
  begin
    execute immediate 'insert into ' || tcSchema ||
                      '.crm_categ_contracte
  (categ_ctr, explicatie, id_util, dataora)
  values (:1, :2, :3, :4)'
      using tcCateg_ctr, tcExplicatie, tnId_util, SYSDATE;
  end adauga_categ_ctr;
  -------------------------------------------------------------------------------------------
  procedure modifica_categ_ctr(tcSchema       varchar2,
                               tnId_categ_ctr number,
                               tcCateg_ctr    varchar2,
                               tcExplicatie   varchar2,
                               tnId_util      number) is
  begin
    execute immediate 'update ' || tcSchema || '.crm_categ_contracte
  set categ_ctr=:1, explicatie=:2, id_util=:3, dataora=:4
  where id_categ_ctr=:5'
      using tcCateg_ctr, tcExplicatie, tnId_util, SYSDATE, tnId_categ_ctr;
  
  end modifica_categ_ctr;
  -------------------------------------------------------------------------------------------
  procedure sterge_categ_ctr(tcSchema       varchar2,
                             tnId_categ_ctr number,
                             tnId_utils     number) is
  begin
    execute immediate 'update ' || tcSchema || '.crm_categ_contracte
  set sters=:1, id_utils=:2, dataoras=:3
   where id_categ_ctr =:4'
      using 1, tnId_utils, SYSDATE, tnId_categ_ctr;
  end sterge_categ_ctr;
  -------------------------------------------------------------------------------------------
  procedure adauga_tip_tag(V_SCHEMA    IN VARCHAR2,
                           V_TIP_TAG   IN CRM_TIP_TAG_DOCUMENT.TIP_TAG%TYPE,
                           V_DESCRIERE IN CRM_TIP_TAG_DOCUMENT.DESCRIERE%TYPE,
                           V_INACTIV   IN CRM_TIP_TAG_DOCUMENT.INACTIV%TYPE,
                           V_ID_UTIL   IN SYN_UTILIZATORI.ID_UTIL%TYPE) is
  begin
    EXECUTE IMMEDIATE 'INSERT INTO ' || V_SCHEMA ||
                      '.CRM_TIP_TAG_DOCUMENT ' ||
                      '(TIP_TAG,DESCRIERE,INACTIV,ID_UTIL) ' ||
                      'VALUES (:1,:2,:3,:4)'
      USING V_TIP_TAG, V_DESCRIERE, V_INACTIV, V_ID_UTIL;
  end;
  -------------------------------------------------------------------------------------------
  procedure modifica_tip_tag(V_SCHEMA         IN VARCHAR2,
                             V_ID_TIP_TAG_DOC IN CRM_TIP_TAG_DOCUMENT.ID_TIP_TAG_DOC%TYPE,
                             V_TIP_TAG        IN CRM_TIP_TAG_DOCUMENT.TIP_TAG%TYPE,
                             V_DESCRIERE      IN CRM_TIP_TAG_DOCUMENT.DESCRIERE%TYPE,
                             V_INACTIV        IN CRM_TIP_TAG_DOCUMENT.INACTIV%TYPE) is
  begin
    EXECUTE IMMEDIATE 'UPDATE ' || V_SCHEMA || '.CRM_TIP_TAG_DOCUMENT ' ||
                      'SET TIP_TAG=:1,DESCRIERE=:2,INACTIV=:3 ' ||
                      'WHERE ID_TIP_TAG_DOC=:4'
      USING V_TIP_TAG, V_DESCRIERE, V_INACTIV, V_ID_TIP_TAG_DOC;
  end;
  -------------------------------------------------------------------------------------------
  procedure sterge_tip_tag(V_SCHEMA         IN VARCHAR2,
                           V_ID_TIP_TAG_DOC IN CRM_TIP_TAG_DOCUMENT.ID_TIP_TAG_DOC%TYPE,
                           V_ID_UTIL        IN SYN_UTILIZATORI.ID_UTIL%TYPE) is
  begin
    EXECUTE IMMEDIATE 'UPDATE ' || V_SCHEMA || '.CRM_TIP_TAG_DOCUMENT ' ||
                      'SET STERS=:1,ID_UTILS=:2,DATAORAS=:3 WHERE ID_TIP_TAG_DOC=:4'
      USING 1, V_ID_UTIL, SYSDATE, V_ID_TIP_TAG_DOC;
  end;
  -------------------------------------------------------------------------------------------
  procedure adauga_model_document(V_SCHEMA       IN VARCHAR2,
                                  V_ID_TIP_MODEL IN NUMBER,
                                  V_DESCRIERE    IN VARCHAR2,
                                  V_LINK         IN VARCHAR2,
                                  V_SIR          IN VARCHAR2,
                                  V_ID_UTIL      IN NUMBER) is
    V_LUNGIME_SIR1 NUMBER(10);
    V_LUNGIME_SIR2 NUMBER(10);
    V_ELEMENT1     VARCHAR2(2000);
    V_ELEMENT2     VARCHAR2(2000);
  
    V_ID_MODEL_DOC   CRM_MODELE_DOCUMENTE.ID_MODEL_DOC%TYPE;
    V_ID_COLOANA_DOC CRM_ELEMENTE_DOCUMENT.ID_COLOANA_DOC%TYPE;
    V_ID_TIP_TAG_DOC CRM_ELEMENTE_DOCUMENT.ID_TIP_TAG_DOC%TYPE;
    V_POZITIE        CRM_ELEMENTE_DOCUMENT.POZITIE%TYPE;
  begin
    IF V_SIR IS NOT NULL THEN
      EXECUTE IMMEDIATE 'INSERT /*+ APPEND */ INTO ' || V_SCHEMA ||
                        '.CRM_MODELE_DOCUMENTE ' ||
                        '(ID_TIP_MODEL,DESCRIERE,LINK,ID_UTIL) VALUES(:1,:2,:3,:4) ' ||
                        'RETURNING ID_MODEL_DOC INTO :5'
        USING V_ID_TIP_MODEL, V_DESCRIERE, V_LINK, V_ID_UTIL
        RETURNING INTO V_ID_MODEL_DOC;
    
      V_LUNGIME_SIR1 := SIR_IN_SIR(V_SIR, ';');
    
      FOR i IN 1 .. V_LUNGIME_SIR1 LOOP
        V_ELEMENT1       := ELEMENT_DIN_SIR(V_SIR, ';', i) || ',';
        V_ID_COLOANA_DOC := TO_NUMBER(ELEMENT_DIN_SIR(V_ELEMENT1, ',', 1));
        V_ID_TIP_TAG_DOC := TO_NUMBER(ELEMENT_DIN_SIR(V_ELEMENT1, ',', 2));
        V_ELEMENT2       := ELEMENT_DIN_SIR(V_ELEMENT1, ',', 3);
      
        IF SUBSTR(V_ELEMENT2, LENGTH(V_ELEMENT2) - 1) <> '.' THEN
          V_ELEMENT2 := V_ELEMENT2 || '.';
        END IF;
      
        V_LUNGIME_SIR2 := SIR_IN_SIR(V_ELEMENT2, '.');
      
        FOR j IN 1 .. V_LUNGIME_SIR2 LOOP
          V_POZITIE := TO_NUMBER(ELEMENT_DIN_SIR(V_ELEMENT2, '.', j));
          EXECUTE IMMEDIATE 'INSERT INTO ' || V_SCHEMA ||
                            '.CRM_ELEMENTE_DOCUMENT ' ||
                            '(ID_MODEL_DOC,POZITIE,ID_COLOANA_DOC,ID_TIP_TAG_DOC,ID_UTIL) ' ||
                            'VALUES(:1,:2,:3,:4,:5)'
            USING V_ID_MODEL_DOC, V_POZITIE, V_ID_COLOANA_DOC, V_ID_TIP_TAG_DOC, V_ID_UTIL;
        END LOOP;
      
      END LOOP;
    
    END IF;
  
  end;

  -------------------------------------------------------------------------------------------
  procedure modifica_model_document(V_SCHEMA       IN VARCHAR2,
                                    V_ID_MODEL_DOC IN NUMBER,
                                    V_ID_TIP_MODEL IN NUMBER,
                                    V_DESCRIERE    IN VARCHAR2,
                                    V_LINK         IN VARCHAR2,
                                    V_SIR          IN VARCHAR2,
                                    V_ID_UTIL      IN NUMBER) is
    V_LUNGIME_SIR1 NUMBER(10);
    V_LUNGIME_SIR2 NUMBER(10);
    V_ELEMENT1     VARCHAR2(2000);
    V_ELEMENT2     VARCHAR2(2000);
  
    V_ID_COLOANA_DOC CRM_ELEMENTE_DOCUMENT.ID_COLOANA_DOC%TYPE;
    V_ID_TIP_TAG_DOC CRM_ELEMENTE_DOCUMENT.ID_TIP_TAG_DOC%TYPE;
    V_POZITIE        CRM_ELEMENTE_DOCUMENT.POZITIE%TYPE;
  begin
    /*    pack_oferta.sterge_model_document(V_SCHEMA, V_ID_MODEL_DOC, V_ID_UTIL);
    
    pack_oferta.adauga_model_document(V_SCHEMA,
                                      V_ID_TIP_MODEL,
                                      V_DESCRIERE,
                                      V_LINK,
                                      V_SIR,
                                      V_ID_UTIL);*/
    EXECUTE IMMEDIATE 'UPDATE ' || V_SCHEMA || '.CRM_MODELE_DOCUMENTE ' ||
                      'SET DESCRIERE=:1,LINK=:2 WHERE ID_MODEL_DOC=:3'
      USING V_DESCRIERE, V_LINK, V_ID_MODEL_DOC;
  
    EXECUTE IMMEDIATE 'UPDATE ' || V_SCHEMA || '.CRM_ELEMENTE_DOCUMENT ' ||
                      'SET STERS=:1,ID_UTILS=:2,DATAORAS=:3 ' ||
                      'WHERE ID_MODEL_DOC=:4 AND STERS=:5'
      USING 1, V_ID_UTIL, SYSDATE, V_ID_MODEL_DOC, 0;
  
    IF V_SIR IS NOT NULL THEN
      V_LUNGIME_SIR1 := SIR_IN_SIR(V_SIR, ';');
    
      FOR i IN 1 .. V_LUNGIME_SIR1 LOOP
        V_ELEMENT1       := ELEMENT_DIN_SIR(V_SIR, ';', i) || ',';
        V_ID_COLOANA_DOC := TO_NUMBER(ELEMENT_DIN_SIR(V_ELEMENT1, ',', 1));
        V_ID_TIP_TAG_DOC := TO_NUMBER(ELEMENT_DIN_SIR(V_ELEMENT1, ',', 2));
        V_ELEMENT2       := ELEMENT_DIN_SIR(V_ELEMENT1, ',', 3);
      
        IF SUBSTR(V_ELEMENT2, LENGTH(V_ELEMENT2) - 1) <> '.' THEN
          V_ELEMENT2 := V_ELEMENT2 || '.';
        END IF;
      
        V_LUNGIME_SIR2 := SIR_IN_SIR(V_ELEMENT2, '.');
      
        FOR j IN 1 .. V_LUNGIME_SIR2 LOOP
          V_POZITIE := TO_NUMBER(ELEMENT_DIN_SIR(V_ELEMENT2, '.', j));
          EXECUTE IMMEDIATE 'INSERT INTO ' || V_SCHEMA ||
                            '.CRM_ELEMENTE_DOCUMENT ' ||
                            '(ID_MODEL_DOC,POZITIE,ID_COLOANA_DOC,ID_TIP_TAG_DOC,ID_UTIL) ' ||
                            'VALUES(:1,:2,:3,:4,:5)'
            USING V_ID_MODEL_DOC, V_POZITIE, V_ID_COLOANA_DOC, V_ID_TIP_TAG_DOC, V_ID_UTIL;
        END LOOP;
      
      END LOOP;
    END IF;
  end;
  -------------------------------------------------------------------------------------------
  procedure sterge_model_document(V_SCHEMA       IN VARCHAR2,
                                  V_ID_MODEL_DOC IN NUMBER,
                                  V_ID_UTIL      IN NUMBER) is
  begin
    EXECUTE IMMEDIATE 'UPDATE ' || V_SCHEMA || '.CRM_MODELE_DOCUMENTE ' ||
                      'SET STERS=:1,ID_UTILS=:2,DATAORAS=:3 ' ||
                      'WHERE ID_MODEL_DOC=:4'
      USING 1, V_ID_UTIL, SYSDATE, V_ID_MODEL_DOC;
  
    EXECUTE IMMEDIATE 'UPDATE ' || V_SCHEMA || '.CRM_ELEMENTE_DOCUMENT ' ||
                      'SET STERS=:1,ID_UTILS=:2,DATAORAS=:3 ' ||
                      'WHERE ID_MODEL_DOC=:4'
      USING 1, V_ID_UTIL, SYSDATE, V_ID_MODEL_DOC;
  end;
  -------------------------------------------------------------------------------------------
end PACK_OFERTA;

prompt
prompt	Creare view CRM_VMODELE_DOCUMENTE
prompt
prompt
create or replace view crm_vmodele_documente as
select a.id_model_doc,
       a.id_tip_model,
       a.descriere,
       a.link,
       b.denumire
  from crm_modele_documente a
  left join crm_tip_model_doc b on a.id_tip_model=b.id_tip_model_doc
  where a.sters=0
/

prompt
prompt	Creare view CRM_VCATEG_CONTRACTE
prompt
prompt
create or replace view crm_vcateg_contracte as
select id_categ_ctr,
       categ_ctr,
       explicatie,
       id_util,
       dataora,
       id_utils,
       dataoras
  from crm_categ_contracte 
 where sters=0
/