create table REGISTRATURA
(
  id_reg         NUMBER(8) not null,
  id_part        NUMBER(10),
  id_pers        NUMBER(10),
  numar          VARCHAR2(20),
  data           DATE default SYSDATE,
  numar_intern   VARCHAR2(20),
  data_intern    DATE default SYSDATE,
  ie             NUMBER(1),
  descriere      VARCHAR2(100),
  id_util        NUMBER(8),
  dataora        DATE default SYSDATE,
  avizat         NUMBER(1) default 0,
  id_util_avizat NUMBER(8),
  dataora_avizat DATE,
  id_utils       NUMBER(8),
  dataoras       DATE,
  sters          NUMBER(1) default 0,
  observatii     CLOB,
  id_fdoc        NUMBER(5),
  id_mediu       NUMBER(5),
  nr_pag         NUMBER(4) default 1,
  id_responsabil NUMBER(10),
  id_proiect     NUMBER(10)
);
  
comment on column REGISTRATURA.ie
  is 'intrare/iesire';
comment on column REGISTRATURA.id_mediu
  is 'mediu tr/rec';
comment on column REGISTRATURA.nr_pag
  is 'cate pagini contine documentul';
alter table REGISTRATURA add constraint PK_REGISTRATURA primary key (ID_REG);


create or replace view vregistratura as
select r.ID_REG,
       r.ID_PART,
       p.DENUMIRE,
       r.ID_PERS,
       pc.DENUMIRE as pers_contact,
       r.NUMAR,
       r.DATA,
       r.NUMAR_INTERN,
       r.DATA_INTERN,
       r.IE,
       r.DESCRIERE,
       r.ID_UTIL,
       util2.utilizator AS util,
       r.DATAORA,
       r.AVIZAT,
       r.ID_UTIL_AVIZAT,
       UTIL3.UTILIZATOR as util_avizat,
       r.DATAORA_AVIZAT,
       r.ID_UTILS,
       util1.utilizator AS utils,
       r.DATAORAS,
       r.observatii,
       r.id_fdoc,
       f.FEL_DOCUMENT,
       r.id_mediu,
       mt.MEDIU_TRANSMISIE,
       nvl(l.are_link, 0) are_link,
       (case
         when r.observatii is not null then
          1
         else
          0
       end) are_obs,
       r.id_responsabil,
       resp.denumire AS nresp,
       r.nr_pag,
       r.sters,
       r.id_proiect,
       pr.denumire as proiect
  from registratura r
  left join vnom_parteneri p on p.ID_PART = r.id_part
  left join vnom_fdoc f on f.ID_FDOC = r.id_fdoc
  left join vpers_contact pc on pc.id_pers = r.id_pers
  left join vnom_medii_transmisie mt on mt.ID_MEDIU = r.id_mediu
  LEFT JOIN syn_vUTILIZATORI UTIL1 ON r.ID_UTILS = UTIL1.ID_UTIL
  LEFT JOIN syn_vUTILIZATORI UTIL2 ON r.ID_UTIL = UTIL2.ID_UTIL
  LEFT JOIN syn_vUTILIZATORI UTIL3 ON r.ID_UTIL_avizat = UTIL3.ID_UTIL
  LEFT JOIN nom_parteneri resp ON r.id_responsabil = resp.id_part
  left join con_proiecte pr on r.id_proiect = pr.id_proiect
  left join (select distinct id_entitate,
                             id_program,
                             (case
                               when (count(id_entitate)
                                     over(partition by id_entitate, id_program)) > 0 then
                                1
                               else
                                0
                             end) as are_link
               from atas_referinte
              where sters = 0
                and id_program = 70) l on l.id_entitate = r.id_reg
 order by to_number(r.numar_intern) desc;

begin pack_registratura.adauga_registratura(?@pnId_reg, ?pnNumarIntern); end;

select id_fdoc, fel_document FROM vnom_fdoc WHERE 2=2 and FEL_DOCUMENT LIKE 'CONTRAC%' AND (inactiv = 0)   ORDER BY FEL_DOCUMENT;

SELECT 0 as TIP, B.ID_PART, B.DENUMIRE AS NUME, B.COD_FISCAL, B.DENUMIRE  FROM NOM_PARTENERI B  WHERE 2=2 and DENUMIRE LIKE 'MIDIA%' AND (B.STERS = 0 AND B.INACTIV = 0)   ORDER BY 1,NUME;

begin pack_registratura.modifica_registratura(1470,4974,NULL,NULL, TO_DATE('20250509','YYYY-MM-DD'),'', TO_DATE('20250509','YYYY-MM-DD'),'3755',2,'contract',' ',683,NULL,1,8); end;


CREATE OR REPLACE PACKAGE BODY "PACK_REGISTRATURA" is

  procedure adauga_reg_link(tnId_reg     number,
                            tcDenumire   varchar2,
                            tcLink       varchar2,
                            tcObservatii varchar2,
                            tnId_Link    out number,
                            tnId_proiect number) IS

  begin
    insert into reg_linkuri
      (id_reg, denumire, link, observatii)
    VALUES
      (tnId_reg, tcDenumire, tcLink, tcObservatii)
    returning id_link into tnId_Link;

    update registratura set id_proiect = tnId_proiect where id_reg = tnId_reg;
    if tnId_proiect is not null then
       pack_contracte.adauga_link_proiect(tnId_proiect,
                            tcDenumire ,
                            tcLink    ,
                            tcObservatii ,
                            tnId_Link);
     end if;
  end adauga_reg_link;
  -------------------------------------------------------------------------------------------
  procedure sterge_reg_link(tnId_link number) IS
  begin
    update reg_linkuri
       set sters = 1
     where id_link = tnId_link
       and sters = 0;

  end sterge_reg_link;
  -------------------------------------------------------------------------------------------
  procedure adauga_registratura(tnNext_id out number, tcNumarIntern IN REGISTRATURA.NUMAR_INTERN%TYPE) is

  begin

    insert into registratura
      (id_reg, numar_intern)
    values
      (tnNext_id, tcNumarIntern)
    returning id_reg into tnNext_id;

  end adauga_registratura;
  -------------------------------------------------------------------------------------------
  procedure modifica_registratura(tnId_reg         number,
                                  tnId_part        number,
                                  tnId_pers        number,
                                  tnId_responsabil number,
                                  tdData           date,
                                  tcNumar          varchar2,
                                  tdData_intern    date,
                                  tcNumar_intern   varchar2,
                                  tnIE             number,
                                  tcDescriere      varchar2,
                                  tcObservatii     clob,
                                  tnId_fdoc        number,
                                  tnId_mediu       number,
                                  tnNr_pag         number,
                                  tnId_util        number) is
  begin
    update registratura
       set id_part        = tnId_part,
           id_pers        = tnId_pers,
           id_responsabil = tnId_responsabil,
           data           = tdData,
           numar          = tcNumar,
           data_intern    = tdData_intern,
           numar_intern   = tcNumar_intern,
           ie             = tnIE,
           descriere      = tcDescriere,
           observatii     = tcObservatii,
           id_fdoc        = tnId_fdoc,
           id_mediu       = tnId_mediu,
           nr_pag         = tnNr_pag,
           id_util        = tnId_util,
           dataora        = SYSDATE
     where id_reg = tnId_reg;

  end modifica_registratura;
  -------------------------------------------------------------------------------------------
  procedure sterge_registratura(tnId_reg number, tnId_util number) is
  begin

    update registratura
       set sters = 1, id_utils = tnId_util, dataoras = SYSDATE
     where id_reg = tnId_reg;

    update reg_linkuri
       set sters = 1, id_utils = tnId_util, dataoras = SYSDATE
     where id_reg = tnId_reg;

  end sterge_registratura;
  -------------------------------------------------------------------------------------------
  procedure caut_referinte(tcNumar   varchar2,
                           tdData    date,
                           tnId_part number,
                           p_cursor  OUT pack_types.tip_cursor) IS

    lcSelect varchar2(4000) := '';

  begin

    lcSelect := 'select * from (select count(*) as nr_aparitii,
  ''Registru jurnal'' as unde from
(select distinct cod from act
where sters = 0 and trim(to_char(nract)) = :1 and dataact = :2
and (id_partd=:3 or id_partc=:4))
union
select count(*) as nr_aparitii, ''Contracte'' as unde from
(select * from contracte
where sters = 0 and trim(numar) = :5
and data = :6
and id_part = :7)
union
select count(*) as nr_aparitii, ''Comenzi'' as unde from
(select * from comenzi
where sters = 0 and to_char(nr_comanda) = :8
and data_comanda = :9
and id_part = :10))
where nr_aparitii <> 0';

    /*union
    select count(*) as nr_aparitii, 'Oferte' as unde from
    (select * from rof_oferte
    where sters = 0 and nrof = 10
    and data_oferta = to_date('20070331','YYYYMMDD')
    and id_part = 36)*/

    open p_cursor for lcSelect
      using trim(tcNumar), tdData, tnId_part, tnId_part, trim(tcNumar), tdData, tnId_part, trim(tcNumar), tdData, tnId_part;

  end caut_referinte;
  -------------------------------------------------------------------------------------------
  procedure avizeaza_document(tnId_reg  number,
                              tnValue   number,
                              tnId_util number) is
  begin

    update registratura
       set avizat         = tnValue,
           id_util_avizat = tnId_util,
           dataora_avizat = sysdate
     where id_reg = tnId_reg;

  end avizeaza_document;
  -------------------------------------------------------------------------------------------

end PACK_REGISTRATURA;


--------------------------------------------------------------
AUTENTIFICARE si INTRARE IN APLICATIE

09/05/2025 11:20 AM LENOVO-AIO-BIRO # mmari
09/05/2025 11:20 AM LENOVO-AIO-BIRO # mmari DEMO
          FRM_LOGIN1.DO_LOGIN          Incercare de conectare:
09/05/2025 11:20:05 AM LENOVO-AIO-BIRO # mmari DEMO
          OEXECUTOR.OEXECUTE          ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ".,"
09/05/2025 11:20:05 AM LENOVO-AIO-BIRO # mmari DEMO
          OEXECUTOR.OEXECUTE          SELECT pack_drepturi.verificaidutil(8) as utilizator from dual
09/05/2025 11:20 AM LENOVO-AIO-BIRO # mmari DEMO
          FRM_LOGIN1.DO_LOGIN          Connected: LENOVO-AIO-BIRO # mmari
09/05/2025 11:20:05 AM LENOVO-AIO-BIRO # mmari DEMO
          OEXECUTOR.OEXECUTE          {call syn_pack_drepturi.citeste_firme_program(?pnIdProgram,?pnIdUtil)}
09/05/2025 11:20:05 AM LENOVO-AIO-BIRO # mmari DEMO
          OEXECUTOR.OEXECUTE          select * from syn_lunilean
09/05/2025 11:20:05 AM LENOVO-AIO-BIRO # mmari DEMO
          OEXECUTOR.OEXECUTE          ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ".,"
09/05/2025 11:20 AM LENOVO-AIO-BIRO # mmari DEMO
          SCHIMBA_FIRMA          CONECTARE ROMFAST AHORA ID_FIRMA         -1 HANDLE = 1
09/05/2025 11:20:05 AM LENOVO-AIO-BIRO # mmari AHORA
          OEXECUTOR.OEXECUTE          select varvalue, col_an, col_luna, tip_col_an, tip_col_luna from syn_optiuni_programe WHERE id_prg_owner = 70 and UPPER(varname) = 'CALENDAR'
09/05/2025 11:20:05 AM LENOVO-AIO-BIRO # mmari AHORA
          OEXECUTOR.OEXECUTE          select an as an,  to_char(luna)  as nl  from AHORA.CALENDAR order by an * 12 + luna
09/05/2025 11:20:06 AM LENOVO-AIO-BIRO # mmari AHORA
          OEXECUTOR.OEXECUTE          ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ".,"
09/05/2025 11:20 AM LENOVO-AIO-BIRO # mmari AHORA
          SCHIMBA_FIRMA          CONECTARE ROMFAST ROMFAST ID_FIRMA         -1 HANDLE = 1
09/05/2025 11:20:06 AM LENOVO-AIO-BIRO # mmari ROMFAST
          OEXECUTOR.OEXECUTE          select varvalue, col_an, col_luna, tip_col_an, tip_col_luna from syn_optiuni_programe WHERE id_prg_owner = 70 and UPPER(varname) = 'CALENDAR'
09/05/2025 11:20:06 AM LENOVO-AIO-BIRO # mmari ROMFAST
          OEXECUTOR.OEXECUTE          select an as an,  to_char(luna)  as nl  from ROMFAST.CALENDAR order by an * 12 + luna
09/05/2025 11:20:07 AM LENOVO-AIO-BIRO # mmari ROMFAST
          OEXECUTOR.OEXECUTE          select varvalue, col_an, col_luna, tip_col_an, tip_col_luna from syn_optiuni_programe WHERE id_prg_owner = 70 and UPPER(varname) = 'CALENDAR'
09/05/2025 11:20:07 AM LENOVO-AIO-BIRO # mmari ROMFAST
          OEXECUTOR.OEXECUTE          select * from ROMFAST.calendar order by anul, luna
09/05/2025 11:20:07 AM LENOVO-AIO-BIRO # mmari ROMFAST
          OEXECUTOR.OEXECUTE          begin pack_def.citesteNumeEnt(?gnIdUtil,?@gcNumeUtilEnt); end;
09/05/2025 11:20:07 AM LENOVO-AIO-BIRO # mmari ROMFAST
          OEXECUTOR.OEXECUTE          begin PACK_FIRMA.IS_LUNA_BLOCATA(?pcS, ?pnIdePrg, ?pnAn, ?pnLuna, ?@pnLunaBlocata); end;
09/05/2025 11:20:07 AM LENOVO-AIO-BIRO # mmari ROMFAST
          OEXECUTOR.OEXECUTE          select pack_migrare.VerificaVersiune('ROAREGISTRATURA', '2.0.4', '2014_02_04_05') as retvalue from dual
09/05/2025 11:20:07 AM LENOVO-AIO-BIRO # mmari ROMFAST
          OEXECUTOR.OEXECUTE          select * from lunilean
09/05/2025 11:20:07 AM LENOVO-AIO-BIRO # mmari ROMFAST
          OEXECUTOR.OEXECUTE          begin pack_sesiune.set_Id_Util(?gnIdUtil); end;
09/05/2025 11:20:07 AM LENOVO-AIO-BIRO # mmari ROMFAST
          OEXECUTOR.OEXECUTE          begin pack_sesiune.setluna(?gnLuna); pack_sesiune.setan(?gnAn); end;
09/05/2025 11:20:07 AM LENOVO-AIO-BIRO # mmari ROMFAST
          OEXECUTOR.OEXECUTE          begin pack_sesiune.setlunabal(?gnLuna); pack_sesiune.setanbal(?gnAn); end;
09/05/2025 11:20:07 AM LENOVO-AIO-BIRO # mmari ROMFAST
          OEXECUTOR.OEXECUTE          begin pack_contafin.SET_DATA_RON(?gcS); end;
09/05/2025 11:20:07 AM LENOVO-AIO-BIRO # mmari ROMFAST
          OEXECUTOR.OEXECUTE          begin pack_sesiune.set_data_ron(?gcS); end;
09/05/2025 11:20:07 AM LENOVO-AIO-BIRO # mmari ROMFAST
          OEXECUTOR.OEXECUTE          begin pack_contafin.set_id_sucursala(?gnIdSucursala); end;
09/05/2025 11:20:07 AM LENOVO-AIO-BIRO # mmari ROMFAST
          OEXECUTOR.OEXECUTE          begin pack_contafin.set_id_firma(?gnIdFirma); end;
09/05/2025 11:20:07 AM LENOVO-AIO-BIRO # mmari ROMFAST
          OEXECUTOR.OEXECUTE          begin SCRIE_OPTIUNI('CONTAFIN_ORACLE'); end;
09/05/2025 11:20:07 AM LENOVO-AIO-BIRO # mmari ROMFAST
          OEXECUTOR.OEXECUTE          select * from ROMFAST.optiuni order by varname
09/05/2025 11:20:08 AM LENOVO-AIO-BIRO # mmari ROMFAST
          OEXECUTOR.OEXECUTE          begin SCRIE_CORESP_CONT_TIPGEST('ROMFAST'); end;
09/05/2025 11:20:08 AM LENOVO-AIO-BIRO # mmari ROMFAST
          OEXECUTOR.OEXECUTE          select * from optiuni_util where id_util = ?gnIdUtil order by varname
09/05/2025 11:20:08 AM LENOVO-AIO-BIRO # mmari ROMFAST
          OEXECUTOR.OEXECUTE          select * from contafin_oracle.lunilean
09/05/2025 11:20:08 AM LENOVO-AIO-BIRO # mmari ROMFAST
          OEXECUTOR.OEXECUTE          select cheie,id_firma from contafin_oracle.vdef_util_obiecte where id_util = ?gnIdUtil and id_program=?gnIdProgram and id_firma=?gnIdFirma
09/05/2025 11:20 AM LENOVO-AIO-BIRO # mmari ROMFAST
          DECA_BAZA.CA_BAZA1.AFISARE          select id_reg, id_part, denumire, id_pers, pers_contact, data, numar, numar_intern, data_intern,  descriere, ie, observatii, id_fdoc, fel_document, id_mediu, mediu_transmisie, avizat,  util, dataora, utils, dataoras, util_avizat, dataora_avizat, are_obs, are_link, id_responsabil, nresp, nr_pag, sters,id_proiect,proiect  from vregistratura WHERE STERS=0
09/05/2025 11:20:09 AM LENOVO-AIO-BIRO # mmari ROMFAST
          OEXECUTOR.OEXECUTE          {call contafin_oracle.pack_drepturi.programe_utilizator(8)}
09/05/2025 11:20:09 AM LENOVO-AIO-BIRO # mmari ROMFAST
          OEXECUTOR.OEXECUTE          {call contafin_oracle.pack_drepturi.programe_utilizator(8)}
09/05/2025 11:20 AM LENOVO-AIO-BIRO # mmari ROMFAST
          DECA_BAZA.CA_BAZA1.AFISARE          select id_reg, id_part, denumire, id_pers, pers_contact, data, numar, numar_intern, data_intern,  descriere, ie, observatii, id_fdoc, fel_document, id_mediu, mediu_transmisie, avizat,  util, dataora, utils, dataoras, util_avizat, dataora_avizat, are_obs, are_link, id_responsabil, nresp, nr_pag, sters,id_proiect,proiect  from vregistratura WHERE STERS=0

---------------------------------------------------------------