CREATE OR REPLACE procedure IKIS_CIS.err_imp_docs_param2(dt_from in date, dt_to in date) is
dt_part date;
num number;
i number :=0;
begin
  EXECUTE IMMEDIATE 'alter session set nls_date_format="dd.mm.yyyy hh24:mi:ss"';
--  if dt_to = dt_from then
  num := to_number(dt_to - dt_from + 1)*24;
 -- else 
--  num := to_number(dt_to - dt_from)*24;
--  end if;
  dt_part := dt_from -1/24;
  
  loop
  i := i + 1;
  dt_part := dt_part +1/24;
  if i <= num then
  insert into ikis_cis.err_docs_param2(date_month,
                              crt_crt_id,
                              DOC_ID,DELAY_PUBLICATION_TERM, IS_BLOCKED) 
SELECT DISTINCT TO_CHAR(d.dt_import, 'mm.yyyy') ,d.crt_crt_id, D.DOC_ID, d.DELAY_PUBLICATION_TERM, D.IS_BLOCKED
  --D.REG_NUMB, D.DT_IMPORT
  FROM ICC_RSR.DOCUMENTS d,
       ICC_RSR.DOC_TEXTS dt                      --, ICC_RSR.DOC_IMAGES di
 WHERE                                                      --D.CRT_CRT_ID=809
      DT.DOC_DOC_ID = D.DOC_ID
       AND (upper(dt.text) like upper('?0=40=') 
       or upper(dt.text) like upper('%:>@8ABL%') 
       or upper(dt.text) like upper('%?@>4>265==O%') 
       or upper(dt.text) like upper('%@57C;LB0BV%') 
       or upper(dt.text) like upper('%@>;V%') 
       or upper(dt.text) like upper('%A8;C%') 
       or upper(dt.text) like upper('%@07V%') 
       or upper(dt.text) like upper('%?@>BO3><%') 
       or upper(dt.text) like upper('%FV;OE%')) 
       AND D.ST_ST_ID not in (7)
       AND DT.IS_ROW_DELETE <> 1
       AND DT.IS_IMPERSONAL <> 0
       AND D.IMP_ST_ST_ID in (15,20)
       and D.ST_ST_ID=6
       AND D.DT_IMPORT >= TO_DATE (dt_part, 'dd.mm.yyyy HH24:MI:SS') 
       and D.DT_IMPORT <= TO_DATE (dt_part, 'dd.mm.yyyy HH24:MI:SS')+1/24;
      commit;
      else return;
  end if;
end loop;

end err_imp_docs_param2;
/

--------------- SQL ---------------

CREATE FUNCTION public.test (
  dt_from date,
  dt_to date,
  i integer = 0
)
RETURNS void AS
$body$
DECLARE
  dt_part timestamp;
  num INTEGER;
  begin
  --EXECUTE IMMEDIATE 'alter session set nls_date_format="dd.mm.yyyy hh24:mi:ss"';
  --  if dt_to = dt_from then
  --i   : = NULL;
  num := to_number(dt_to - dt_from + 1)*24;
  -- else 
  --  num := to_number(dt_to - dt_from)*24;
  --  end if;
  dt_part := dt_from -1/24;
  loop
    i := i + 1;
    dt_part := dt_part +1/24;
    if i <= num then
      insert into
        err_docs_param2(date_month, crt_crt_id, DOC_ID, DELAY_PUBLICATION_TERM, IS_BLOCKED)
      SELECT
        DISTINCT TO_CHAR(d.dt_import, 'mm.yyyy'),
        d.crt_crt_id,
        D.DOC_ID,
        d.DELAY_PUBLICATION_TERM,
        D.IS_BLOCKED
      FROM
        DOCUMENTS d,
        doc_texts dt
      WHERE
        DT.DOC_DOC_ID = D.DOC_ID AND
        (upper(dt.tekst) like upper('?0=40=') or
        upper(dt.tekst) like upper('%:>@8ABL%') or
        upper(dt.tekst) like upper('%?@>4>265==O%') or
        upper(dt.tekst) like upper('%@57C;LB0BV%') or
        upper(dt.tekst) like upper('%@>;V%') or
        upper(dt.tekst) like upper('%A8;C%') or
        upper(dt.tekst) like upper('%@07V%') or
        upper(dt.tekst) like upper('%?@>BO3><%') or
        upper(dt.tekst) like upper('%FV;OE%')) AND
        D.ST_ST_ID not in (7) AND
        DT.IS_ROW_DELETE <> 1 AND
        DT.IS_IMPERSONAL <> 0 AND
        D.IMP_ST_ST_ID in (15, 20) and
        D.ST_ST_ID = 6 AND
        D.DT_IMPORT >= to_timestamp(dt_part, 'dd.mm.yyyy HH24:MI:SS') and
        D.DT_IMPORT <= to_timestamp(dt_part, 'dd.mm.yyyy HH24:MI:SS')+1/24;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;

CREATE OR REPLACE FUNCTION public.test (
  dt_from date,
  dt_to date,
  i integer = 0
)
RETURNS void AS
$body$
DECLARE
  dt_part date;
  num INTEGER;
  begin
  num := (dt_to - dt_from + 1)*24;
  dt_part := dt_from -1/24;
  loop
    i := i + 1;
    dt_part := dt_part+1/24;
    if i <= num then
      insert into
        err_docs_param2(date_month, crt_crt_id, DOC_ID, DELAY_PUBLICATION_TERM, IS_BLOCKED)
      SELECT
        DISTINCT TO_CHAR(d.dt_import, 'mm.yyyy'),
        d.crt_crt_id,
        D.DOC_ID,
        d.DELAY_PUBLICATION_TERM,
        D.IS_BLOCKED
      FROM
        DOCUMENTS d,
        doc_texts dt
      WHERE
        DT.DOC_DOC_ID = D.DOC_ID AND
        (upper(dt.tekst) like upper('?0=40=') or
        upper(dt.tekst) like upper('%:>@8ABL%') or
        upper(dt.tekst) like upper('%?@>4>265==O%') or
        upper(dt.tekst) like upper('%@57C;LB0BV%') or
        upper(dt.tekst) like upper('%@>;V%') or
        upper(dt.tekst) like upper('%A8;C%') or
        upper(dt.tekst) like upper('%@07V%') or
        upper(dt.tekst) like upper('%?@>BO3><%') or
        upper(dt.tekst) like upper('%FV;OE%')) AND
        D.ST_ST_ID not in (7) AND
        DT.IS_ROW_DELETE <> 1 AND
        DT.IS_IMPERSONAL <> 0 AND
        D.IMP_ST_ST_ID in (15, 20) and
        D.ST_ST_ID = 6 AND
        D.DT_IMPORT >= (dt_part) and
        D.DT_IMPORT <= (dt_part)+1/24;
        else return;
  end if;
end loop;
end;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE OR REPLACE FUNCTION public.test (
  dt_from date,
  dt_to date,
  i integer = 0
)
RETURNS void AS
$body$
DECLARE
  dt_part date;
  num INTEGER;
  begin
  set timezone = 'Europe/Kiev';
  num := (dt_to - dt_from + 1)*24;
  dt_part := dt_from -1/24;
  loop
    i := i + 1;
    dt_part := (dt_part)+1/24;
    if i <= num then
      insert into
        err_docs_param2(date_month, crt_crt_id, DOC_ID, DELAY_PUBLICATION_TERM, IS_BLOCKED)
      SELECT
        DISTINCT TO_CHAR(d.dt_import, 'mm.yyyy'),
        d.crt_crt_id,
        D.DOC_ID,
        d.DELAY_PUBLICATION_TERM,
        D.IS_BLOCKED
      FROM
        DOCUMENTS d,
        doc_texts dt
      WHERE
        DT.DOC_DOC_ID = D.DOC_ID AND
        ((dt.tekst) like ('?0=40=') or
        (dt.tekst) like ('%:>@8ABL%') or
        (dt.tekst) like ('%?@>4>265==O%') or
        (dt.tekst) like ('%@57C;LB0BV%') or
        (dt.tekst) like ('%@>;V%') or
        (dt.tekst) like ('%A8;C%') or
        (dt.tekst) like ('%@07V%') or
        (dt.tekst) like ('%?@>BO3><%') or
        (dt.tekst) like ('%FV;OE%')) AND
        D.ST_ST_ID not in (7) AND
        DT.IS_ROW_DELETE <> 1 AND
        DT.IS_IMPERSONAL <> 0 AND
        d.doc_id=66825571 AND
        D.IMP_ST_ST_ID in (15, 20) and
        D.ST_ST_ID = 6 AND
        D.DT_IMPORT >= (dt_part) and
        D.DT_IMPORT <= (dt_part)+1/24;
        else return;
  end if;
end loop;
end;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE OR REPLACE FUNCTION public.test (
  dt_from date,
  dt_to date,
  i integer = 0
)
RETURNS void AS
$body$
DECLARE
  dt_part TIMESTAMP;
  num INTEGER;
  begin
  set timezone = 'Europe/Kiev';
  num := (dt_to - dt_from + 1)*24;
  dt_part := dt_from - INTERVAL'1 HOUR';
  loop
    i := i + 1;
    dt_part := (dt_part)+ INTERVAL'1 HOUR';
    if i <= num then
      insert into
        err_docs_param2(date_month, crt_crt_id, DOC_ID, DELAY_PUBLICATION_TERM, IS_BLOCKED)
      SELECT
        DISTINCT TO_CHAR(d.dt_import, 'mm.yyyy'),
        d.crt_crt_id,
        D.DOC_ID,
        d.DELAY_PUBLICATION_TERM,
        D.IS_BLOCKED
      FROM
        DOCUMENTS d,
        doc_texts dt
      WHERE
        DT.DOC_DOC_ID = D.DOC_ID AND
        ((dt.tekst) like ('?0=40=') or
        (dt.tekst) like ('%:>@8ABL%') or
        (dt.tekst) like ('%?@>4>265==O%') or
        (dt.tekst) like ('%@57C;LB0BV%') or
        (dt.tekst) like ('%@>;V%') or
        (dt.tekst) like ('%A8;C%') or
        (dt.tekst) like ('%@07V%') or
        (dt.tekst) like ('%?@>BO3><%') or
        (dt.tekst) like ('%FV;OE%')) AND
        D.ST_ST_ID not in (7) AND
        DT.IS_ROW_DELETE <> 1 AND
        DT.IS_IMPERSONAL <> 0 AND
        --d.doc_id=66825571 AND
        D.IMP_ST_ST_ID in (15, 20) and
        D.ST_ST_ID = 6 AND
        D.DT_IMPORT >= (dt_part) and
        D.DT_IMPORT <= (dt_part) + INTERVAL'1 HOUR';
        else return;
  end if;
end loop;
end;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE TABLE public.documents (
  doc_id INTEGER NOT NULL,
  case_numb VARCHAR(70),
  crt_crt_id INTEGER,
  st_st_id INTEGER NOT NULL,
  dt_case DATE,
  dt_registration DATE,
  row_version INTEGER NOT NULL,
  is_row_delete NUMERIC(1,0) NOT NULL,
  pkg_pkg_id INTEGER,
  prev_case_numb VARCHAR(70),
  prev_dt_case DATE,
  rc_doc_vr_type INTEGER,
  rc_doc_cs_type INTEGER,
  csc_csc_id INTEGER,
  rc_doc_ins_type INTEGER,
  old_case_numb VARCHAR(70),
  old_dt_case DATE,
  prev_crt_crt_id INTEGER,
  prev_dt_registration DATE,
  is_ph_present NUMERIC(1,0),
  is_gov_present NUMERIC(1,0),
  is_session_public NUMERIC(1,0),
  full_st_st_id INTEGER NOT NULL,
  imp_st_st_id INTEGER,
  dt_created TIMESTAMP WITHOUT TIME ZONE,
  is_blocked NUMERIC(1,0) NOT NULL,
  is_archiv NUMERIC(1,0),
  file_name VARCHAR(255),
  dt_import TIMESTAMP WITHOUT TIME ZONE,
  rc_doc_src_type INTEGER,
  is_err_letter_exists NUMERIC(1,0),
  usr_usr_id INTEGER NOT NULL,
  reg_numb VARCHAR(15) NOT NULL,
  crt_name VARCHAR(255),
  crt_prev_uid VARCHAR(12),
  crt_prev_name VARCHAR(255),
  doc_prev_uid VARCHAR(64),
  dt_notice TIMESTAMP WITHOUT TIME ZONE,
  dt_law DATE,
  judge_fio VARCHAR(90),
  binary_image_count INTEGER NOT NULL,
  crt_crt_tag_id INTEGER,
  tag_numb VARCHAR(30),
  dt_tag DATE,
  crt_uid VARCHAR(12),
  doc_uid VARCHAR(64),
  is_signature_present NUMERIC(1,0) NOT NULL,
  dt_notice_impersonal TIMESTAMP WITHOUT TIME ZONE,
  is_return_on_dep NUMERIC(1,0),
  is_return_on_dest NUMERIC(1,0),
  dt_disabled TIMESTAMP WITHOUT TIME ZONE,
  is_jur_present NUMERIC(1,0),
  rc_doc_process_type INTEGER,
  usr_usr_dest_id INTEGER,
  usr_usr_dep_id INTEGER,
  dt_operation_dest TIMESTAMP WITHOUT TIME ZONE,
  dt_operation_dep TIMESTAMP WITHOUT TIME ZONE,
  is_temp_block_public INTEGER,
  is_problem NUMERIC(1,0) DEFAULT 0,
  delay_publication_term INTEGER,
  dnzs_id BIGINT,
  date_doc_send_from_court TIMESTAMP WITHOUT TIME ZONE,
  dt_doc_sign_send_from_court TIMESTAMP WITHOUT TIME ZONE,
  dt_doc_text_send_from_court DATE,
  dt_zvit DATE,
  hearingclosed INTEGER,
  CONSTRAINT documents_pkey PRIMARY KEY(doc_id),
  CONSTRAINT doc_csc_fk FOREIGN KEY (csc_csc_id)
    REFERENCES public.dict_case_categories(csc_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE,
  CONSTRAINT doc_pkg_fk FOREIGN KEY (pkg_pkg_id)
    REFERENCES public.packs(pkg_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE,
  CONSTRAINT documents_crt_fk FOREIGN KEY (crt_crt_id)
    REFERENCES public.dict_courts(crt_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE,
  CONSTRAINT documents_full_st_fk FOREIGN KEY (full_st_st_id)
    REFERENCES public.dict_statuses(st_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE,
  CONSTRAINT documents_imp_st_fk FOREIGN KEY (imp_st_st_id)
    REFERENCES public.dict_statuses(st_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE,
  CONSTRAINT documents_st_fk FOREIGN KEY (st_st_id)
    REFERENCES public.dict_statuses(st_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE
) 
WITH (oids = false);