/*
* Автор: Маркелов С.В.
* Дата: 2014-10-30
* Задача: http://192.168.2.17:3000/issues/774
* Назначение: При попытке формирования отчетов отображается сообщение об ошибке.
* Изменяются объекты:
    insert into MESSAGES_LIST_SETUP
*/

/*
  ****** Инструкции для SQLPlus (обязательно) ******
  Для печати сообщений:
  DBMS_OUTPUT.put_line('<Message>');
*/
whenever sqlerror exit 1;
set serveroutput on;
set echo off;

declare
	filename      varchar2(256) := 'sfm.database-update.2014-10-30.FixReports.sql';
	purpose       varchar2(4000) := 'При попытке формирования отчетов отображается сообщение об ошибке.';
	script_exists number := 0;
begin

	--delete from DB_Update_History where script_name = filename;	commit;
	select count(*)
		into script_exists
		from DB_Update_History
	 where script_name = filename;

	if (script_exists > 0) then
		RAISE_APPLICATION_ERROR(-20001, '"' || filename || '" уже присутствует в базе');
	else
		insert into DB_Update_History
			(script_name, purpose)
		values
			(filename, purpose);
	end if;

	commit;

exception
	when others then
		rollback;
		raise;
end;
/

CREATE OR REPLACE PACKAGE BODY KFM."PKG_PITAHOLDING" as

  --AddOrganization
  function AddOrganization(pPITATYPE            in NUMBER,
                           pORGNAME             in varchar2,
                           pORG_REGDATA         in varchar2,
                           pORG_REGWHO          in varchar2,
                           pORG_ADRESS          in varchar2,
                           pORG_LEADERSFULLNAME in varchar2,
                           pREASONS             in varchar2,
                           pORG_UNP             in varchar2,
                           pDESISION_DATE       in date,
                           ppitaorgid           out number) return number is
    result number default 0;
    cADD_ALTER_MODE constant number := 0;
  begin
    if pPITATYPE in (2, 3) then
      insert into pitaorgs
        (PITATYPE,
         ORGNAME,
         ORG_REGDATA,
         ORG_REGWHO,
         ORG_ADRESS,
         ORG_LEADERSFULLNAME,
         REASONS,
         ORG_UNP,
         DESISION_DATE,
         alterdate,
         altermode)
      values
        (pPITATYPE,
         pORGNAME,
         pORG_REGDATA,
         pORG_REGWHO,
         pORG_ADRESS,
         pORG_LEADERSFULLNAME,
         pREASONS,
         pORG_UNP,
         pDESISION_DATE,
         sysdate,
         cADD_ALTER_MODE)
      returning pitaorgid into ppitaorgid;
    else
      result := -3000; --Неверно задан тип оргнаизации вовлеченной в террористическую деятельность
    end if;
    return result;
  end;

  --AddPerson
  function AddPerson(pPITATYPE          in NUMBER,
                     pPERSFULLNAME      in VARCHAR2,
                     pCOUNTRYREG        in VARCHAR2,
                     pPERS_BIRTHDATA    in VARCHAR2,
                     pPERS_RESID_ADRESS in VARCHAR2,
                     pPERS_DOC          in VARCHAR2,
                     pREASONS           in CLOB,
                     pINDIVID_REGDATA   in VARCHAR2,
                     pINDIVIG_REGWHO    in VARCHAR2,
                     pINDIVID_UNP       in varchar2,
                     pPITAPERSID        out NUMBER) return number is
    result number default 0;
    cADD_ALTER_MODE constant number := 0;
  begin
    if pPITATYPE in (0, 1) then
      insert into pitapersons
        (PITATYPE,
         PERSFULLNAME,
         COUNTRYREG,
         PERS_BIRTHDATA,
         PERS_RESID_ADRESS,
         PERS_DOC,
         REASONS,
         INDIVID_REGDATA,
         INDIVIG_REGWHO,
         INDIVID_UNP,
         alterdate,
         altermode)
      values
        (pPITATYPE,
         pPERSFULLNAME,
         pCOUNTRYREG,
         pPERS_BIRTHDATA,
         pPERS_RESID_ADRESS,
         pPERS_DOC,
         pREASONS,
         pINDIVID_REGDATA,
         pINDIVIG_REGWHO,
         pINDIVID_UNP,
         sysdate,
         cADD_ALTER_MODE)
      returning pitapersid into ppitapersid;

    else
      result := -3000; --Неверно задан тип оргнаизации вовлеченной в террористическую деятельность
    end if;
    return result;
  end;

  --ChangeOrganizationData
  function ChangeOrganizationData(ppitaorgid           in number,
                                  pPITATYPE            in NUMBER,
                                  pORGNAME             in varchar2,
                                  pORG_REGDATA         in varchar2,
                                  pORG_REGWHO          in varchar2,
                                  pORG_ADRESS          in varchar2,
                                  pORG_LEADERSFULLNAME in varchar2,
                                  pREASONS             in varchar2,
                                  pORG_UNP             in varchar2,
                                  pDESISION_DATE       in date) return number is
    result number default 0;
    vcount number default 0;
    --cCHANGE_ALTER_MODE constant number := 1;
  begin
    select count(*) into vcount from pitaorgs where pitaorgid = ppitaorgid;
    if vcount = 0 then
      result := -3001; --невреный ID организации
    else
      if ppitatype not in (2, 3) then
        result := -3000; --Неверно задан тип оргнаизации вовлеченной в террористическую деятельность
      else
        update pitaorgs
           set PITATYPE            = pPITATYPE,
               ORGNAME             = pORGNAME,
               ORG_REGDATA         = pORG_REGDATA,
               ORG_REGWHO          = pORG_REGWHO,
               ORG_ADRESS          = pORG_ADRESS,
               ORG_LEADERSFULLNAME = pORG_LEADERSFULLNAME,
               REASONS             = pREASONS,
               ORG_UNP             = pORG_UNP,
               DESISION_DATE       = pDESISION_DATE,
               ALTERDATE           = sysdate
         where pitaorgid = ppitaorgid;
      end if;
    end if;
    return result;
  end;

  --ChangePersonData
  function ChangePersonData(pPITAPERSID        in NUMBER,
                            pPITATYPE          in NUMBER,
                            pPERSFULLNAME      in VARCHAR2,
                            pCOUNTRYREG        in VARCHAR2,
                            pPERS_BIRTHDATA    in VARCHAR2,
                            pPERS_RESID_ADRESS in VARCHAR2,
                            pPERS_DOC          in VARCHAR2,
                            pREASONS           in CLOB,
                            pINDIVID_REGDATA   in VARCHAR2,
                            pINDIVIG_REGWHO    in VARCHAR2,
                            pINDIVID_UNP       in varchar2) return number is
    result number default 0;
    vcount number default 0;
    --cCHANGE_ALTER_MODE constant number := 1;
  begin
    select count(*)
      into vcount
      from pitapersons
     where pitapersid = ppitapersid;
    if vcount = 0 then
      result := -3001; --невреный ID организации
    else
      if ppitatype not in (0, 1) then
        result := -3000; --Неверно задан тип оргнаизации вовлеченной в террористическую деятельность
      else
        update pitapersons
           set PITATYPE          = pPITATYPE,
               PERSFULLNAME      = pPERSFULLNAME,
               COUNTRYREG        = pCOUNTRYREG,
               PERS_BIRTHDATA    = pPERS_BIRTHDATA,
               PERS_RESID_ADRESS = pPERS_RESID_ADRESS,
               PERS_DOC          = pPERS_DOC,
               REASONS           = pREASONS,
               INDIVID_REGDATA   = pINDIVID_REGDATA,
               INDIVIG_REGWHO    = pINDIVIG_REGWHO,
               INDIVID_UNP       = pINDIVID_UNP,
               ALTERDATE         = sysdate
         where pitapersid = ppitapersid;
      end if;
    end if;
    return result;
  end;

  --GetPITAPersonsList
  function GetPITAPersonsList(pType     in number,
                              pListType in number,
                              pPITACur  out sys_refcursor) return number is
    result number default 0;
  begin
    case pListType
      when 1 then
        open pPITACur for
          select PITAPERSID,
                 PERSFULLNAME,
                 COUNTRYREG,
                 PERS_BIRTHDATA,
                 PERS_RESID_ADRESS,
                 PERS_DOC,
                 REASONS,
                 INDIVID_REGDATA,
                 INDIVIG_REGWHO,
                 INDIVID_UNP,
                 1 as isenabled
            from pitapersons
           where pitatype = pType
             and altermode != -1;
      when 2 then
        open pPITACur for
          select PITAPERSID,
                 PERSFULLNAME,
                 COUNTRYREG,
                 PERS_BIRTHDATA,
                 PERS_RESID_ADRESS,
                 PERS_DOC,
                 REASONS,
                 INDIVID_REGDATA,
                 INDIVIG_REGWHO,
                 INDIVID_UNP,
                 decode(altermode, -1, 0, 1) as isenabled
            from pitapersons
           where pitatype = pType;
      when 0 then
        open pPITACur for
          select PITAPERSID,
                 PERSFULLNAME,
                 COUNTRYREG,
                 PERS_BIRTHDATA,
                 PERS_RESID_ADRESS,
                 PERS_DOC,
                 REASONS,
                 INDIVID_REGDATA,
                 INDIVIG_REGWHO,
                 INDIVID_UNP,
                 0 as isenabled
            from pitapersons
           where pitatype = pType
             and altermode = -1;
    end case;

    return result;
  end;

  --GetPITAOrgList
  function GetPITAOrgList(pType     in number,
                          pListType in number,
                          pPITACur  out sys_refcursor) return number is
    result number default 0;
  begin
    case pListType
      when 1 then
        open pPITACur for
          select pitaorgid,
                 ORGNAME,
                 ORG_REGDATA,
                 ORG_REGWHO,
                 ORG_ADRESS,
                 ORG_LEADERSFULLNAME,
                 REASONS,
                 ORG_UNP,
                 DESISION_DATE,
                 1 as isenabled
            from pitaorgs
           where pitatype = pType
             and altermode != -1;
      when 2 then
        open pPITACur for
          select pitaorgid,
                 ORGNAME,
                 ORG_REGDATA,
                 ORG_REGWHO,
                 ORG_ADRESS,
                 ORG_LEADERSFULLNAME,
                 REASONS,
                 ORG_UNP,
                 DESISION_DATE,
                 decode(altermode, -1, 0, 1) as isenabled
            from pitaorgs
           where pitatype = pType;
      when 0 then
        open pPITACur for
          select pitaorgid,
                 ORGNAME,
                 ORG_REGDATA,
                 ORG_REGWHO,
                 ORG_ADRESS,
                 ORG_LEADERSFULLNAME,
                 REASONS,
                 ORG_UNP,
                 DESISION_DATE,
                 0 as isenabled
            from pitaorgs
           where pitatype = pType
             and altermode = -1;
    end case;

    return result;
  end;

  --FindPITAPersonByName
  function FindPITAPersonByName(pName    in varchar2,
                                pPITACur out sys_refcursor) return number is
    cMAX_ROW_COUNT constant integer := 100;
    result number default 0;
  begin
    open pPITACur for
      select PITAPERSID,
             PERSFULLNAME,
             COUNTRYREG,
             PERS_BIRTHDATA,
             PERS_RESID_ADRESS,
             PERS_DOC,
             REASONS,
             INDIVID_REGDATA,
             INDIVIG_REGWHO,
             INDIVID_UNP
        from pitapersons
       where instr(PERSFULLNAME, pName) > 0
         and ROWNUM < cMAX_ROW_COUNT
       order by PERSFULLNAME;
    return result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_pitaholding.FindPITAPersonByName');
      Result := -1; --Общая ошибка
      return Result;
  end FindPITAPersonByName;

  --FindPITAOrgByName
  function FindPITAOrgByName(pName in varchar2, pPITACur out sys_refcursor)
    return number is
    cMAX_ROW_COUNT constant integer := 100;
    result number default 0;
  begin
    open pPITACur for
      select pitaorgid,
             ORGNAME,
             ORG_REGDATA,
             ORG_REGWHO,
             ORG_ADRESS,
             ORG_LEADERSFULLNAME,
             REASONS,
             ORG_UNP,
             DESISION_DATE
        from pitaorgs
       where instr(ORGNAME, pName) > 0
         and ROWNUM < cMAX_ROW_COUNT
       order by ORGNAME;
    return result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_pitaholding.FindPITAOrgByName');
      Result := -1; --Общая ошибка
      return Result;
  end FindPITAOrgByName;

  --DeletePITAPersons
  function DeletePITAPersons(pPITAPERSID in number) return number is
    Result number default 0;
    cDELETE_ALTER_MODE constant number := -1;
  begin
    update PITAPERSONS p
       set p.altermode = cDELETE_ALTER_MODE, p.alterdate = sysdate
     where p.pitapersid = pPITAPERSID;
    return Result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_pitaholding.DeletePITAPersons');
      Result := -1; --Общая ошибка
      return Result;
  end DeletePITAPersons;

  --DeletePITAOrg
  function DeletePITAOrg(pPITAORGID in number) return number is
    Result number default 0;
    cDELETE_ALTER_MODE constant number := -1;
  begin
    update PITAORGS p
       set p.altermode = cDELETE_ALTER_MODE, p.alterdate = sysdate
     where p.pitaorgid = pPITAORGID;
    return Result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_pitaholding.DeletePITAOrg');
      Result := -1; --Общая ошибка
      return Result;
  end DeletePITAOrg;

  --OpenPITAPerson
  function OpenPITAPerson(pPITAPERSID in number) return number is
    Result number default 0;
    cOPEN_ALTER_MODE   constant number := 0;
    cDELETE_ALTER_MODE constant number := -1;
  begin
    update PITAPERSONS p
       set p.altermode = cOPEN_ALTER_MODE, p.alterdate = sysdate
     where p.pitapersid = pPITAPERSID
       and altermode = cDELETE_ALTER_MODE;
    return Result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_pitaholding.OpenPITAPerson');
      Result := -1; --Общая ошибка
      return Result;
  end OpenPITAPerson;

  --OpenPITAOrg
  function OpenPITAOrg(pPITAORGID in number) return number is
    Result number default 0;
    cOPEN_ALTER_MODE   constant number := 0;
    cDELETE_ALTER_MODE constant number := -1;
  begin
    update PITAORGS p
       set p.altermode = cOPEN_ALTER_MODE, p.alterdate = sysdate
     where p.pitaorgid = pPITAORGID
       and altermode = cDELETE_ALTER_MODE;
    return Result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_pitaholding.OpenPITAOrg');
      Result := -1; --Общая ошибка
      return Result;
  end OpenPITAOrg;

  --CheckPerson
  function CheckPerson(pType    in number,
                       pIndific in varchar2,
                       pPITACur out sys_refcursor) return number is
    result number default 0;
  begin
    case pType
      when 1 then
        open ppitacur for
          select pitapersid,
                 PITATYPE,
                 PERSFULLNAME,
                 COUNTRYREG,
                 PERS_BIRTHDATA,
                 PERS_RESID_ADRESS,
                 PERS_DOC,
                 REASONS,
                 INDIVID_REGDATA,
                 INDIVIG_REGWHO,
                 INDIVID_UNP
            from pitapersons
           where upper(trim(PERSFULLNAME)) like
                 '%' || upper(trim(pIndific)) || '%';
      when 2 then
        null;
      else
        null;
    end case;
    return result;
  end;

  --CheckOrg
  function CheckOrg(pType    in number,
                    pIndific in varchar2,
                    pPITACur out sys_refcursor) return number is
    result         number default 0;
    pIndificNumber number;
    pIndificDate   date;
  begin
    case pType
      when 1 then
        open pPITACur for
          select pitaorgid,
                 PITATYPE,
                 ORGNAME,
                 ORG_REGDATA,
                 ORG_REGWHO,
                 ORG_ADRESS,
                 ORG_LEADERSFULLNAME,
                 REASONS,
                 ORG_UNP,
                 DESISION_DATE
            from pitaorgs
           where upper(trim(ORGNAME)) like
                 '%' || upper(trim(pIndific)) || '%';
      when 4 then
        open pPITACur for
          select pitaorgid,
                 PITATYPE,
                 ORGNAME,
                 ORG_REGDATA,
                 ORG_REGWHO,
                 ORG_ADRESS,
                 ORG_LEADERSFULLNAME,
                 REASONS,
                 ORG_UNP,
                 DESISION_DATE
            from pitaorgs
           where upper(trim(ORG_LEADERSFULLNAME)) like
                 '%' || upper(trim(pIndific)) || '%';
      when 6 then
        pIndificNumber := to_number(pIndific);
        open pPITACur for
          select pitaorgid,
                 PITATYPE,
                 ORGNAME,
                 ORG_REGDATA,
                 ORG_REGWHO,
                 ORG_ADRESS,
                 ORG_LEADERSFULLNAME,
                 REASONS,
                 ORG_UNP,
                 DESISION_DATE
            from pitaorgs
           where ORG_UNP = pIndificNumber;
      when 7 then
        pIndificDate := to_date(pIndific, 'yyyy-mm-dd');
        open pPITACur for
          select pitaorgid,
                 PITATYPE,
                 ORGNAME,
                 ORG_REGDATA,
                 ORG_REGWHO,
                 ORG_ADRESS,
                 ORG_LEADERSFULLNAME,
                 REASONS,
                 ORG_UNP,
                 DESISION_DATE
            from pitaorgs
           where DESISION_DATE = pIndificDate;
      else
        null;
    end case;
    return result;
  end;

  --rec
  function rec(sub varchar2, minstr varchar2) return number is
    pos    number default 0;
    result number default 0.0000;
  begin
    if (length(sub) <= 3) and (instr(minstr, sub)) > 0 then
      return 1;
    elsif (length(sub) <= 3) and (instr(minstr, sub)) = 0 then
      return 0;
    else
      if (instr(minstr, sub)) > 0 then
        return 1;
      else
        pos    := length(sub) / 2;
        result := rec(substr(sub, 1, trunc(pos)), minstr) * 0.48 +
                  rec(substr(sub, trunc(pos + 1)), minstr) * 0.48;
        return result;
      end if;
    end if;
    return 0;
  end rec;

  --CompareOrgNames
  function CompareOrgNames(pName in varchar2, pCollNames in varchar2)
    return number is
    result number default 0;
    str    varchar2(300);
    str2   varchar2(100 char);
    -- vcount number default 0;
    pos number default 0;
    cursor porgnames is
      select modulename from updates_modulenames;
    pragma autonomous_transaction;
  begin
    if pName is not null and pCollNames is not null then
      str := pCollNames;
      loop
        exit when length(str) = 0;
        pos := instr(str, chr(10));
        if pos > 0 then
          str2 := substrb(str, 1, pos - 1);
          insert into updates_modulenames modulename values (str2);
          str := substr(str, pos + 1, length(str));
        else
          insert into updates_modulenames modulename values (str);
          exit;
        end if;
      end loop;

      for porgname in porgnames loop
        str  := upper(trim(replace(replace(porgname.modulename, '"'), '.')));
        str2 := upper(trim(replace(replace(pName, '"'), '.')));
        if (length(str) > length(str2)) then
          if rec(str, str2) * 100 > 70 then
            -- commit;
            result := 1;
            exit;
          end if;
          --   commit;
          --   return rec(str, str2);
        else
          if rec(str2, str) * 100 > 70 then
            --commit;
            result := 1;
            exit;
          end if;

          --    commit;
          --    return rec(str2, str);
        end if;
      end loop;
    end if;
    commit;
    return result;
  exception
    when others then
      commit;
      return result;
  end CompareOrgNames;

  --CompareOrgNamesRandomAlg
  function CompareOrgNamesRandomAlg(pName      in varchar2,
                                    pCollNames in varchar2) return number is
    Result           number default 0;
    vRandomStartPos  number; -- первое рандомное число
    vRandomSubstrLen number; -- второе рандомное число
    vStartPos        number; -- Начальная позиция выделиния подстроки (random)
    vSubstrLen       number; -- Длина выделяемой подстроки (random)
    vLeftvSubstrLen  number default 3; -- Левая граница диапазона длины подстроки
    vMaxSubstrLen    number default 4; -- Длина границы диапазона длины подстроки
    vSelectingCount  number default 100; -- Количество запросов для определения вхождения
    vSubStrForSelect varchar2(100 char); -- Подстрока для проверки
    vCountMatches    number default 0; -- Количество совпадений
    cSELECTING_COUNT constant number default 10; -- Константа для вычисления количества запросов
  begin
    if length(pName) < vLeftvSubstrLen then
      Result := -2; -- Длина заданной строки меньше минимальной
    else
      begin
        vSelectingCount := ceil(length(pCollNames) * cSELECTING_COUNT /
                                vMaxSubstrLen);
        select ABS(dbms_random.random) into vRandomSubstrLen from dual;
        select ABS(dbms_random.random) into vRandomStartPos from dual;
        FOR vCounter IN 1 .. vSelectingCount LOOP
          vSubstrLen := vLeftvSubstrLen +
                        MOD(vRandomSubstrLen -
                            MOD(vRandomSubstrLen, vCounter),
                            vMaxSubstrLen);
          if vSubstrLen >= length(pName) then
            vSubstrLen := length(pName);
          end if;
          vStartPos        := MOD(vRandomStartPos -
                                  MOD(vRandomStartPos, vCounter),
                                  length(pName) + 1 - vSubstrLen);
          vSubStrForSelect := substrb(pName, vStartPos, vSubstrLen);
          if instr(pCollNames, vSubStrForSelect) > 0 then
            vCountMatches := vCountMatches + 1;
          end if;
        END LOOP;
        Result := vCountMatches / vSelectingCount; -- Коэффициент совпадения
      end;
    end if;
    return Result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_pitaholding.CompareOrgNamesRandomAlg');
      Result := -1; --Общая ошибка
      return Result;
  end CompareOrgNamesRandomAlg;

  --CompareOrgCodes
  function CompareOrgCodes(pMaincode in number,
                           pTaxcode  in number,
                           pOrg_unp  in varchar2) return number is
    Result number default 0;
  begin
    if upper(trim(to_char(pMaincode))) = upper(trim(pOrg_unp)) or
       upper(trim(to_char(pTaxcode))) = upper(trim(pOrg_unp)) then
      Result := 1;
    else
      Result := 0;
    end if;
    return Result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_pitaholding.CompareOrgCodes');
      Result := -1; --Общая ошибка
      return Result;
  end CompareOrgCodes;

  --ComparePersNames
  function ComparePersNames(pLastname     in varchar2,
                            pFirstname    in varchar2,
                            pMiddlename   in varchar2,
                            pPersfullname in varchar2) return number is
    Result number default 0;
  begin
    if instr(translate(upper(trim(pPersfullname)), '$   , ;:.', '$'),
             upper(trim(pLastname)) || upper(trim(pFirstname)) ||
             upper(trim(pMiddlename))) > 0 then
      Result := 1;
    else
      Result := 0;
    end if;
    return Result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_pitaholding.ComparePersNames');
      Result := -1; --Общая ошибка
      return Result;
  end ComparePersNames;

  --ComparePersNames (добавлено 11.05.2010)
  function ComparePersNames(pFIO in varchar2, pPersfullname in varchar2)
    return number is
    Result number default 0;
  begin
    if instr(translate(upper(trim(pPersfullname)), '$   , ;:.', '$'),
             translate(upper(trim(pFIO)), '$   , ;:.', '$')) > 0 then
      Result := 1;
    else
      Result := 0;
    end if;
    return Result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_pitaholding.ComparePersNames');
      Result := -1; --Общая ошибка
      return Result;
  end ComparePersNames;

  --ComparePersDoc
  function ComparePersDoc(pPers_Docseries in varchar2,
                          pPers_DocNumber in varchar2,
                          pPers_doc       in varchar2) return number is
    Result number default 0;
    --  type docstab is table of varchar2(1000 char) index by varchar2(1000 char);
    --  pstart number default 1;
    --  end number;
    pPerDocVal         varchar2(4000 char);
    pPers_DocseriesVal varchar2(4000 char);
    pPers_DocNumberVal varchar2(4000 char);
    i                  number;
  begin
    pPerDocVal         := ' ' || replace(upper(pPers_doc), ',.№-''";', ' ') || ' ';
    pPers_DocseriesVal := ' ' || upper(trim(pPers_Docseries)) || ' ';
    pPers_DocNumberVal := ' ' || upper(trim(pPers_DocNumber)) || ' ';
    if (instr(pPerDocVal, pPers_DocseriesVal) > 0 and
       instr(pPerDocVal, pPers_DocNumberVal) > 0) then
      Result := 1;
    else
      Result := 0;
    end if;
    return Result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_pitaholding.ComparePersDoc');
      Result := -1; --Общая ошибка
      return Result;
  end ComparePersDoc;

  --ExistsInPitaOrg
  function ExistsInPitaOrg(pEntity_id in number) return clob is
    cTAG_TR_END   constant varchar2(5 char) := '</tr>';
    cTAG_TD_END   constant varchar2(5 char) := '</td>';
    cTAG_TD       constant varchar2(4) := '<td>';
    cTAG_TR       constant varchar2(4) := '<tr>';
    cTABLE_END    constant varchar2(12) := '</table><br>';
    cTABLE_HEADER constant varchar2(686) := '<h4>Террористические организации, в которых идентифицирован участник</h4></br>' ||
                                            '<table cellpadding="4" border="1" bordercolor=#000000 style="border-collapse:collapse" width="100%">' ||
                                            '<tr style="background:#cccccc">' ||
                                            '<td>ID</td>' ||
                                            '<td>Наименование</td>' ||
                                            '<td>Данные о регистрации</td>' ||
                                            '<td>УНП</td>' ||
                                            '<td>Адрес регистрации</td>' ||
                                            '<td>Фамилии руководителей</td></tr>';
    vEntity   mess_cfm_announce_memb%rowtype;
    Result    clob;
    vOrgsList clob;
    vOrg      varchar2(4000 char);
  begin
    select *
      into vEntity
      from mess_cfm_announce_memb
     where mess_cfm_id = pEntity_id
       and member_type = 1;
    for vAppInfo in (select po.pitaorgid,
                            po.orgname,
                            po.org_regdata,
                            po.org_unp,
                            po.org_adress,
                            po.org_leadersfullname
                       from pitaorgs po
                      where pkg_pitaholding.CompareOrgNames(vEntity.Member_Ur_Name,
                                                            po.ORGNAME) = 1
                         or pkg_pitaholding.CompareOrgCodes(vEntity.Member_Maincode,
                                                            vEntity.Member_Taxcode,
                                                            po.org_unp) = 1) loop
      vOrg := cTAG_TR || cTAG_TD || vAppInfo.pitaorgid || cTAG_TD_END ||
              cTAG_TD || vAppInfo.orgname || cTAG_TD_END || cTAG_TD ||
              vAppInfo.Org_Regdata || cTAG_TD_END || cTAG_TD ||
              vAppInfo.Org_Unp || cTAG_TD_END || cTAG_TD ||
              vAppInfo.Org_Adress || cTAG_TD_END || cTAG_TD ||
              vAppInfo.Org_Leadersfullname || cTAG_TD_END || cTAG_TR_END;
      if vOrgsList is not null then
        dbms_lob.append(vOrgsList, vOrg);
      else
        vOrgsList := vOrg;
      end if;
    end loop;
    if vOrgsList is not null then
      Result := cTABLE_HEADER;
      dbms_lob.append(Result, vOrgsList);
      dbms_lob.append(Result, cTABLE_END);
    end if;
    return Result;
  exception
    when no_data_found then
      return null;
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_pitaholding.ExistsInPitaOrg');
      return null;
  end ExistsInPitaOrg;

  --ExistsInPitaPersons
  function ExistsInPitaPersons(pMESS_ID in number) return clob is
    cTAG_TR_END   constant varchar2(5 char) := '</tr>';
    cTAG_TD_END   constant varchar2(5 char) := '</td>';
    cTAG_TD       constant varchar2(4) := '<td>';
    cTAG_TR       constant varchar2(4) := '<tr>';
    cTABLE_END    constant varchar2(12) := '</table><br>';
    cTABLE_HEADER constant varchar2(676) := '<h4>Террористы, в которых идентифицирован участник</h4></br>' ||
                                            '<table cellpadding="4" border="1" bordercolor=#000000 style="border-collapse:collapse" width="100%">' ||
                                            '<tr style="background:#cccccc">' ||
                                            '<td>ID</td>' || '<td>ФИО</td>' ||
                                            '<td>Данные о регистрации</td>' ||
                                            '<td>Дата рождения</td>' ||
                                            '<td>Адрес регистрации</td>' ||
                                            '<td>Документ, удостоверяющий личность</td></tr>';
    vIndividRec mess_cfm_announce_memb%rowtype;
    Result      clob;
    vPersList   clob;
    vPers       varchar2(4000 char);
  begin
    select *
      into vIndividRec
      from mess_cfm_announce_memb
     where mess_cfm_id = pMESS_ID
       and (member_type = 2 or member_type = 3);
    for vAppInfo in (SELECT pp.pitapersid,
                            pp.persfullname,
                            pp.countryreg,
                            pp.pers_birthdata,
                            pp.pers_resid_adress,
                            pp.pers_doc
                       FROM pitapersons pp
                      where pkg_pitaholding.ComparePersNames(vIndividRec.Member_Ac_Secondname,
                                                             vIndividRec.Member_Ac_Firstname,
                                                             vIndividRec.Member_Ac_Middlename,
                                                             pp.persfullname) = 1
                         or pkg_pitaholding.ComparePersNames(vIndividRec.Member_Ur_Name,
                                                             pp.persfullname) = 1
                         or pkg_pitaholding.ComparePersDoc(vIndividRec.Member_Ac_Doc_Series,
                                                           vIndividRec.Member_Ac_Doc_Number,
                                                           pp.pers_doc) = 1) loop
      vPers := cTAG_TR || cTAG_TD || vAppInfo.pitapersid || cTAG_TD_END ||
               cTAG_TD || vAppInfo.persfullname || cTAG_TD_END || cTAG_TD ||
               vAppInfo.countryreg || cTAG_TD_END || cTAG_TD ||
               vAppInfo.pers_birthdata || cTAG_TD_END || cTAG_TD ||
               vAppInfo.pers_resid_adress || cTAG_TD_END || cTAG_TD ||
               vAppInfo.pers_doc || cTAG_TD_END || cTAG_TR_END;
      if vPersList is not null then
        dbms_lob.append(vPersList, vPers);
      else
        vPersList := vPers;
      end if;
    end loop;
    if vPersList is not null then
      Result := cTABLE_HEADER;
      dbms_lob.append(Result, vPersList);
      dbms_lob.append(Result, cTABLE_END);
    end if;
    return Result;
  exception
    when no_data_found then
      return null;
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_pitaholding.ExistsInPitaPersons');
      return null;
  end ExistsInPitaPersons;

/*--Test
  function Test return varchar2 as
    vStrHeader VARCHAR2(10000);
    vStrClient VARCHAR2(10000);
  begin
    vStrHeader := '<style>.td3 {border: 1px solid black; text-align:right; font-size:10px;}' ||
                  '.td1 {border: 1px solid black; text-align:center; font-size:10px;}' ||
                  '.td2 {border: 1px solid black; text-align:center; font-size:10px;font-weight:bold}' ||
                  '.tr1{width:20 cm;font-family: ''Arial'', Arial, sans-serif;background-color:rgb(239,239,239)}' ||
                  '.tr2{width:20 cm;font-family: ''Arial'', Arial, sans-serif}</style>';

    vStrClient := '<font>Проба пера</font></br>';
    vStrClient := vStrClient || '<table border=1>' ||
                  '<tr class=tr1><td class=td2 width=20%>Дата</td><td class=td2 width=80%>Ид код</td></tr>';

    FOR vAppInfo IN (SELECT ac.analytictype, ac.analyticcode
                       FROM analyticcodes ac
                      WHERE ac.analytictype = 666) LOOP
      vStrClient := vStrClient || '<tr class=tr2><td class=td1>' ||
                    vAppInfo.Analytictype || '</td>' || '<td class=td1>' ||
                    vAppInfo.Analyticcode || '</td>' || '</tr>';
    END LOOP;
    vStrClient := vStrClient || '</table><br>';

    return vStrHeader || vStrClient;
  end;*/

end;
/

CREATE OR REPLACE PUBLIC SYNONYM PKG_PITAHOLDING FOR KFM.PKG_PITAHOLDING;

GRANT EXECUTE ON KFM.PKG_PITAHOLDING TO KFM_ROLE;