/*
* Автор: Галкин А.И.
* Дата: 2014-03-31
* Задача: http://jira.softage.ru/jira/browse/SDFO-279
* Назначение: Добавление обработки кодов стран
* Изменяются объекты:
		update package PKG_ANALYTIC
		update package PKG_MESS_CFM_1
		update package PKG_MESS_CFM_2_1
		update package PKG_MESSAGES_XML
		update package PKG_REPORTS
*/

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

declare
	filename      varchar2(256) := 'sfm.database-update.2014-03-31.CountriesCodesHandle.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;

	/*
	****** Begin update script ******
	*/
		-- Транзакционный апдейт
		-- execute immediate '';
	/*
	****** End update script ******
	*/
	commit;

exception
	when others then
		rollback;
		raise;
end;
/


CREATE OR REPLACE PACKAGE BODY KFM."PKG_ANALYTIC" as

  --GetAnalyticCodesInfo
  function GetAnalyticCodesInfo(pAnalyticType  in number,
                                pCodesListType in NUMBER,
                                pResCursor     out sys_refcursor) --версия справочника
   return number is
    Result number default 0;
    vlang  varchar2(10 char) default 'Russian';
    --  isenabled   boolean default true;
  begin
    vlang := pkg_main.GetUserLangSettings;
    case pCodesListType
      when 1 then
        open pResCursor for
          select analyticcode,
                 decode(vlang, 'Kazakh', shortname_kz, shortname) as shortname,
                 decode(vlang, 'Kazakh', analyticname_kz, analyticname) as analyticname,
                 relationcode1,
                 1 as isenabled
            from analyticcodes
           where analytictype = pAnalyticType
             and altermode != -1
           order by analyticcode;
      when 2 then
        open pResCursor for
          select analyticcode,
                 decode(vlang, 'Kazakh', shortname_kz, shortname) as shortname,
                 decode(vlang, 'Kazakh', analyticname_kz, analyticname) as analyticname,
                 relationcode1,
                 decode(altermode, -1, 0, 1) as isenabled
            from analyticcodes
           where analytictype = pAnalyticType
           order by analyticcode;
      when 0 then
        open pResCursor for
          select analyticcode,
                 decode(vlang, 'Kazakh', shortname_kz, shortname) as shortname,
                 decode(vlang, 'Kazakh', analyticname_kz, analyticname) as analyticname,
                 relationcode1,
                 0 as isenabled
            from analyticcodes
           where analytictype = pAnalyticType
             and altermode = -1
           order by analyticcode;

    end case;
    return Result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_analytic.GetAnalyticCodesInfo');
      Result := -1; --Общая ошибка
      return Result;
  end GetAnalyticCodesInfo;

  --GetAnalyticTypesInfo
  function GetAnalyticTypesInfo(pTypesListType in NUMBER,
                                pResCursor     out sys_refcursor)
    return number is
    Result number default 0;
  begin
    case pTypesListType
      when 1 then
        open pResCursor for
          select analytictype,
                 analyticname,
                 relationtype1 as "RelationCode1"
            from analytictypes
           where altermode != -1
             and visibleforusers = 1
           order by analyticname;
      when 2 then
        open pResCursor for
          select analytictype,
                 analyticname,
                 relationtype1 as "RelationCode1"
            from analytictypes
           where visibleforusers = 1
           order by analyticname;
      when 0 then
        open pResCursor for
          select analytictype,
                 analyticname,
                 relationtype1 as "RelationCode1"
            from analytictypes
           where altermode = -1
             and visibleforusers = 1
           order by analyticname;

    end case;

    return Result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_analytic.GetAnalyticTypesInfo');
      Result := -1; --Общая ошибка
      return Result;
  end;

  function GetFullAnalyticInfo(pMode_in char, pResCursor out sys_refcursor)
    return number is
    Result number default 0;
  begin
    case pMode_in
      when 'r' then
        open pResCursor for
          select t.analyticname,
                 t.analytictype,
                 c.analytictype,
                 c.analyticcode,
                 c.analyticname
            from analytictypes t
           right join analyticcodes c
              on t.analytictype = c.analytictype
           where c.altermode != -1
           order by t.analytictype;
      when 'l' then
        open pResCursor for
          select t.analyticname,
                 t.analytictype,
                 c.analytictype,
                 c.analyticcode,
                 c.analyticname
            from analytictypes t
            left join analyticcodes c
              on t.analytictype = c.analytictype
           where c.altermode != -1
              or c.altermode is null
           order by t.analytictype;
      when 'i' then
        open pResCursor for
          select t.analyticname,
                 t.analytictype,
                 c.analytictype,
                 c.analyticcode,
                 c.analyticname
            from analytictypes t
           inner join analyticcodes c
              on t.analytictype = c.analytictype
           where c.altermode != -1
           order by t.analytictype;
      when 'f' then
        open pResCursor for
          select t.analyticname,
                 t.analytictype,
                 c.analytictype,
                 c.analyticcode,
                 c.analyticname
            from analytictypes t
            full join analyticcodes c
              on t.analytictype = c.analytictype
           where c.altermode != -1
              or c.altermode is null
           order by t.analytictype;
      else
        Result := -411; --неправильно введен параметр режима объединения таблиц
    end case;
    return Result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_analytic.GetFullAnalyticInfo');
      Result := -1; --Общая ошибка
      return Result;
  end GetFullAnalyticInfo;

  function GetFullAnalyticInfoWithHiden(pMode_in   char,
                                        pResCursor out sys_refcursor)
    return number is
    Result number default 0;
  begin
    case pMode_in
      when 'r' then
        open pResCursor for
          select t.analyticname,
                 t.analytictype,
                 c.analytictype,
                 c.analyticcode,
                 c.analyticname
            from analytictypes t
           right join analyticcodes c
              on t.analytictype = c.analytictype
           order by t.analytictype;
      when 'l' then
        open pResCursor for
          select t.analyticname,
                 t.analytictype,
                 c.analytictype,
                 c.analyticcode,
                 c.analyticname
            from analytictypes t
            left join analyticcodes c
              on t.analytictype = c.analytictype
           order by t.analytictype;
      when 'i' then
        open pResCursor for
          select t.analyticname,
                 t.analytictype,
                 c.analytictype,
                 c.analyticcode,
                 c.analyticname
            from analytictypes t
           inner join analyticcodes c
              on t.analytictype = c.analytictype
           order by t.analytictype;
      when 'f' then
        open pResCursor for
          select t.analyticname,
                 t.analytictype,
                 c.analytictype,
                 c.analyticcode,
                 c.analyticname
            from analytictypes t
            full join analyticcodes c
              on t.analytictype = c.analytictype
           order by t.analytictype;
      else
        Result := -411; --неправильно введен параметр режима объединения таблиц
    end case;
    return Result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_analytic.GetFullAnalyticInfoWithHiden');
      Result := -1; --Общая ошибка
      return Result;
  end GetFullAnalyticInfoWithHiden;

  --AddAnalyticCode
  function AddAnalyticCode(pAnalyticType_in  in number,
                           pAnalyticCode_in  in number,
                           pAnalyticName_in  in varchar2,
                           pShortName_in     in varchar2,
                           pRelationType1_in in number) return number is
    Result             number default 0;
    vCountAnalyticType number;
    cAlterMode constant number := 0;
  begin
    select count(*)
      into vCountAnalyticType
      from analytictypes t
     where t.analytictype = pAnalyticType_in;
    if vCountAnalyticType = 1 then
      begin
        select count(*)
          into Result
          from analyticcodes c
         where c.analyticcode = pAnalyticCode_in
           and c.analytictype = pAnalyticType_in;
        if Result = 0 then
          begin
            insert into analyticcodes
              (analytictype,
               analyticcode,
               analyticname,
               shortname,
               relationcode1,
               alterdate,
               altermode)
            values
              (pAnalyticType_in,
               pAnalyticCode_in,
               pAnalyticName_in,
               pShortName_in,
               pRelationType1_in,
               sysdate,
               cAlterMode);
          end;
        else
          Result := -408; --повторная запись для заданного кода аналитической информации
        end if;
      end;
    elsif vCountAnalyticType = 0 then
      begin
        Result := -409; --нет такого типа аналитической информации
      end;
    else
      Result := -412; --обнаружены одинаковые типы аналитической информации
    end if;
    return Result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_analytic.AddAnalyticCode');
      Result := -1; --Общая ошибка
      return Result;
  end AddAnalyticCode;

  --AddAnalyticType
  function AddAnalyticType(pAnalyticType_in  in number,
                           pAnalyticName_in  in varchar2,
                           pRelationType1_in in number) return number is
    Result number default 0;
    cAlterMode constant number := 0;
  begin
    select count(*)
      into Result
      from analytictypes t
     where t.analytictype = pAnalyticType_in;
    if Result = 0 then
      begin
        insert into analytictypes
          (analytictype,
           analyticname,
           relationtype1,
           alterdate,
           altermode,
           visibleforusers)
        values
          (pAnalyticType_in,
           pAnalyticName_in,
           pRelationType1_in,
           sysdate,
           cAlterMode,
           1);
        Result := 0;
      end;
    else
      Result := -408; --повторная запись для заданного типа аналитической информации
    end if;
    return Result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_analytic.AddAnalyticType');
      Result := -1; --Общая ошибка
      return Result;
  end AddAnalyticType;

  --ChangeAnalyticCode
  function ChangeAnalyticCode(pAnalyticType_in  in number,
                              pAnalyticCode_in  in number,
                              pAnalyticName_in  in varchar2,
                              pShortName_in     in varchar2,
                              pRelationType1_in in number) return number is
    Result             number default 0;
    vCountAnalyticType number;
    vCountAnalyticCode number;
    --cAlterMode constant number := 1;
  begin
    select count(*)
      into vCountAnalyticType
      from analytictypes t
     where t.analytictype = pAnalyticType_in;
    if vCountAnalyticType = 1 then
      begin
        select count(*)
          into vCountAnalyticCode
          from analyticcodes c
         where c.analyticcode = pAnalyticCode_in
           and c.analytictype = pAnalyticType_in;
        if vCountAnalyticCode = 1 then
          begin
            update analyticcodes
               set analyticname  = pAnalyticName_in,
                   shortname     = pShortName_in,
                   relationcode1 = pRelationType1_in,
                   alterdate     = sysdate
             where analyticcode = pAnalyticCode_in
               and analytictype = pAnalyticType_in;
            update analytictypes
               set alterdate = sysdate
             where analytictype = pAnalyticType_in;
            Result := 0;
          end;
        elsif vCountAnalyticCode = 0 then
          Result := -410; --нет такого кода аналитической информации
        else
          Result := -413; --обнаружены одинаковые коды аналитической информации
        end if;
      end;
    elsif vCountAnalyticType = 0 then
      Result := -409; --нет такого типа аналитической информации
    else
      Result := -412; --обнаружены одинаковые типы аналитической информации
    end if;
    return Result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_analytic.ChangeAnalyticCode');
      Result := -1; --Общая ошибка
      return Result;
  end ChangeAnalyticCode;

  --ChangeAnalyticType
  function ChangeAnalyticType(pAnalyticType_in  in number,
                              pAnalyticName_in  in varchar2,
                              pRelationType1_in in number) return number is
    Result             number default 0;
    vCountAnalyticType number;
    --cAlterMode constant number := 1;
  begin
    select count(*)
      into vCountAnalyticType
      from analytictypes t
     where t.analytictype = pAnalyticType_in;
    if vCountAnalyticType = 1 then
      begin
        update analytictypes
           set analyticname  = pAnalyticName_in,
               relationtype1 = pRelationType1_in,
               alterdate     = sysdate
         where analytictype = pAnalyticType_in;
        Result := 0;
      end;
    elsif vCountAnalyticType = 0 then
      Result := -409; --нет такого типа аналитической информации
    else
      Result := -412; --обнаружены одинаковые типы аналитической информации
    end if;
    return Result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_analytic.ChangeAnalyticType');
      Result := -1; --Общая ошибка
      return Result;
  end ChangeAnalyticType;

  --containsCode
  function containsCode(pAnalyticType_in in number,
                        pAnalyticCode_in in number) return boolean is
    Result             boolean default false;
    vCountAnalyticType number;
    vCountAnalyticCode number;
  begin
    select count(*)
      into vCountAnalyticType
      from analytictypes t
     where t.analytictype = pAnalyticType_in;
    if vCountAnalyticType = 1 then
      begin
        select count(*)
          into vCountAnalyticCode
          from analyticcodes c
         where c.analyticcode = pAnalyticCode_in
           and c.analytictype = pAnalyticType_in;
        if vCountAnalyticCode = 1 then
          Result := true;
        else
          Result := false;
        end if;
      end;
    else
      Result := false;
    end if;
    return Result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_analytic.containsCode');
      return null;
  end containsCode;

  function containsCode1(pAnalyticType_in in number,
                         pAnalyticCode_in in varchar2) return number is
    Result             number default 0;
    vCountAnalyticType number;
    vCountAnalyticCode number;
  begin
    if pAnalyticCode_in is null then
      return 1;
    end if;
    begin
      vCountAnalyticCode := to_number(pAnalyticCode_in);
    exception
      when others then
        return 0;
    end;

    select count(*)
      into vCountAnalyticType
      from analytictypes t
     where t.analytictype = pAnalyticType_in;
    if vCountAnalyticType = 1 then
      begin
        select count(*)
          into vCountAnalyticCode
          from analyticcodes c
         where c.analyticcode = pAnalyticCode_in
           and c.analytictype = pAnalyticType_in;
        if vCountAnalyticCode = 1 then
          Result := 1;
        else
          Result := 0;
        end if;
      end;
    else
      Result := 0;
    end if;
    return Result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_analytic.containsCode');
      return null;
  end containsCode1;

  --containsType
  function containsType(pAnalyticType_in in number) return boolean is
    Result             boolean default false;
    vCountAnalyticType number;
  begin
    select count(*)
      into vCountAnalyticType
      from analytictypes t
     where t.analytictype = pAnalyticType_in;
    if vCountAnalyticType = 1 then
      Result := true;
    else
      Result := false;
    end if;
    return Result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_analytic.containsType');
      return null;
  end containsType;

  --DeleteAnalyticCode
  function DeleteAnalyticCode(pAnalyticType_in in number,
                              pAnalyticCode_in in number) return number is
    Result number default 0;
    cAlterMode constant number := -1;
  begin
    update analyticcodes
       set alterdate = sysdate, altermode = cAlterMode
     where analytictype = pAnalyticType_in
       and analyticcode = pAnalyticCode_in;
    return Result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_analytic.HideAnalyticCode');
      Result := -1; --Общая ошибка
      return Result;
  end DeleteAnalyticCode;

  --DeleteAnalyticType
  function DeleteAnalyticType(pAnalyticType_in in number) return number is
    Result number default 0;
    cAlterMode constant number := -1;
  begin

    update analyticcodes
       set alterdate = sysdate, altermode = cAlterMode
     where analytictype = pAnalyticType_in;
    update analytictypes
       set alterdate = sysdate, altermode = cAlterMode
     where analytictype = pAnalyticType_in;

    return Result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_analytic.HideAnalyticType');
      Result := -1; --Общая ошибка
      return Result;
  end DeleteAnalyticType;

  --OpenAnalyticType
  function OpenAnalyticType(pAnalyticType in number) return number is
  begin
    update Analytictypes
       set altermode = 0, alterdate = sysdate
     where analytictype = pAnalyticType
       and altermode = -1;
    return 0;
  end;

  --OpenAnalyticCode
  function OpenAnalyticCode(pAnalyticType in number,
                            pAnalyticCode in number) return number is
    vcount number default 0;
  begin
    select count(*)
      into vcount
      from Analytictypes
     where altermode <> -1
       and analytictype = pAnalyticType;
    if vcount = 1 then
      update Analyticcodes
         set altermode = 0, alterdate = sysdate
       where analytictype = pAnalyticType
         and analyticcode = pAnalyticCode
         and altermode = -1;
    else
      return - 414; -- Невозможно восстановить код, так как закрыт справочник
    end if;
    return 0;
  end;

  --GetDescription
  function GetDescription(pCode in number, pType in number) return varchar2 is
    result varchar2(1000 CHAR) default null;
  begin
    if pCode is null then
      result := '';
    else
      select analyticname
        into result
        from analyticcodes
       where analytictype = pType
         and analyticcode = pCode;
    end if;
    return result;
  exception
    when others then
      return pCode || '--Значение не найдено в справочнике';
  end;

  --GetShortDescription
  function GetShortDescription(pCode in number,
                               pType in number) return varchar2 is
    result varchar2(1000 CHAR) default null;
  begin
 Case pType
    when 41021 then -- OKEDCODE 2 OKED2AC
    	SELECT AC.SHORTNAME
          INTO result
          FROM ANALYTICCODES ac
         WHERE AC.ANALYTICTYPE = 41021 AND AC.OUTER_CODE = pCode;
    when 41019 then -- OKSMT 2 OKED2AC
	 	 SELECT AC.SHORTNAME
          INTO result
          FROM ANALYTICCODES ac
         WHERE AC.ANALYTICTYPE = 41019 AND AC.OUTER_CODE = pCode;
     else
      select a.shortname
      into result
      from analyticcodes a
      where analytictype = pType
      and analyticcode = pCode;
    end case;
    return result;
  exception
    when others then
      return pCode || '--Значение не найдено в справочнике';
  end GetShortDescription;

  function GetMessName(messtypeid number) return nvarchar2 is
    mess  nvarchar2(300);
    vlang varchar2(30 CHAR);
  begin
    vlang := pkg_main.GetUserLangSettings;
    case vlang
      when 'Kazakh' then
        select mess_name_kz
          into mess
          from messages_types
         where mess_type = messtypeid;
      else
        select mess_name
          into mess
          from messages_types
         where mess_type = messtypeid;
    end case;
    return mess;
  exception
    when others then
      begin
        case vlang
          when 'Kazakh' then
            select mess_name_kz
              into mess
              from messages_types
             where mess_type = -1;
          else
            select mess_name
              into mess
              from messages_types
             where mess_type = -1;
        end case;
        return mess;
      exception
        when others then
          return 'Неизвестный тип';
      end;
  end;

  function GetCodeDescription(pCodeVal  number,
                              pDataCode number,
                              pVersion  number) return varchar2 is
    pType number;
  begin
    begin

      select analytictype
        into pType
        from analytictypes_vers_depend
       where datacode = pDataCode
         and version = pVersion;

    exception
      when no_data_found then
        return pCodeVal || ' - Значение не найдено в справочнике'; --Ошибка в прааметрах настройки и прописанных справочниках
    end;
    return GetDescription(pCodeVal, pType);
  end;

  function GetShortCodeDescription(pCodeVal  number,
                                   pDataCode number,
                                   pVersion  number) return varchar2 is
    pType number;
  begin
    begin

      select analytictype
        into pType
        from analytictypes_vers_depend
       where datacode = pDataCode
         and version = pVersion;

    exception
      when no_data_found then
        return pCodeVal || ' - Значение не найдено в справочнике'; --Ошибка в прааметрах настройки и прописанных справочниках
    end;
    return GetShortDescription(pCodeVal, pType);
  end;

  function GetRelationCodesInfo(pRelationAnalyticType in number,
                                pRelationAnalyticCode in number,
                                pCodesListType        in number,
                                pResCursor            out sys_refcursor,
                                pLanguageCode         in number default 1)
    return number is
    Result number default 0;
    --  isenabled   boolean default true;
  begin
    case pCodesListType
      when 1 then
        open pResCursor for
          select analyticcode,
                 shortname,
                 analyticname,
                 relationcode1,
                 1 as isenabled
            from analyticcodes
           where analytictype = pRelationAnalyticType
             and relationcode1 = pRelationAnalyticCode
             and altermode != -1
           order by analyticcode;
      when 2 then
        open pResCursor for
          select analyticcode,
                 shortname,
                 analyticname,
                 relationcode1,
                 decode(altermode, -1, 0, 1) as isenabled
            from analyticcodes
           where analytictype = pRelationAnalyticType
             and relationcode1 = pRelationAnalyticCode
           order by analyticcode;
      when 0 then
        open pResCursor for
          select analyticcode,
                 shortname,
                 analyticname,
                 relationcode1,
                 0 as isenabled
            from analyticcodes
           where analytictype = pRelationAnalyticType
             and relationcode1 = pRelationAnalyticCode
             and altermode = -1
           order by analyticcode;

    end case;
    return Result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'pkg_analytic.GetAnalyticCodesInfo');
      Result := -1; --Общая ошибка
      return Result;
  end;

  function GetListOfSwiftCodes(pBankSwiftCode      in varchar2,
                               pBankSwiftCodesList out sys_refcursor)
    return number is
    result number default 0;
  begin
    open pBankSwiftCodesList for
      select bic as BankSwiftCode --name
        from swift_codes
       where bic like pBankSwiftCode || '%'
          or pBankSwiftCode is null
       order by bic;
    return result;
  end;

  function GetSwiftCodeParams(pBankSwiftCode    in varchar2,
                              pBankSwiftName    out varchar2,
                              pBankSwiftCountry out number,
                              pCity             out varchar2) return number is
    result number default 0;
  begin

    select name,
           case country
             when 'KAZAKHSTAN' then
              398
             else
              null
           end,
           city
      into pBankSwiftName, pBankSwiftCountry, pCity
      from swift_codes
     where bic = pBankSwiftCode;
    return result;
  exception
    when others then
      return Result;
  end;

 -- Проверка varchar - если число - возвращает число, иначе null
FUNCTION to_Number_Or_Null (p_Value VARCHAR2) RETURN NUMBER IS
  v_res Number;
BEGIN
  RETURN to_Number (p_Value);
EXCEPTION
  WHEN others THEN
    RETURN NULL;
END to_Number_Or_Null;

    --GetCountryDescription - наименование страны, или из кодов аналитики (Web) или из перевязочной таблицы (АРМ)
  function GetCountryDescription(pCode in varchar2) return varchar2 is
    result varchar2(1000 CHAR) default null;
   -- v_pCode Number;
  begin
    select oa.oksmtname
      into result
      from oksmt2ac oa
      where oa.oksmtcode = pCode;-- or ancode = to_number(pCode);
    return result;
  exception
    when others then
      return pCode || '--Значение не найдено в справочнике';
  end GetCountryDescription;

end PKG_Analytic;
/

CREATE OR REPLACE PUBLIC SYNONYM PKG_ANALYTIC FOR KFM.PKG_ANALYTIC
/


GRANT EXECUTE ON KFM.PKG_ANALYTIC TO KFM_ROLE
/


CREATE OR REPLACE PACKAGE BODY KFM."PKG_MESS_CFM_1" as
  --length cursor is

  function ExtractMessInSignedData(pXML      in clob,
                                   pExist    out number,
                                   pSender   out varchar2,
                                   pReceiver out varchar,
                                   pDate     out date) return number is
    result number default 0;
    vXML   xmltype;
  begin
    begin
      vXML := xmltype.createxml(pXML);
    exception
      when others then
        return - 305; --Сообщение is not valid
    end;
    select existsNode(vXML, '//ExportData')
    into pExist
    from dual;
    if pExist is null then
      pExist := 0;
    end if;

    select extractvalue(vXML, '/ExportData/SignedData/Sender'),
           extractvalue(vXML, '/ExportData/SignedData/Receiver'),
           to_date(extractvalue(vXML, '/ExportData/SignedData/TimeStamp'), 'dd.mm.yyyy hh24:mi:ss')
    into pSender,
         pReceiver,
         pDate
    from dual;
    return result;
  exception
    when others then
      return - 315; --В сообщении отсутствует информация  об отправителе и получателе
  end;

  function ExtractSignatureData(pXML       in clob,
                                pSignature out clob) return number is
    result number default 0;
    vXML   xmltype;
    outXML xmltype;
  begin
    begin
      vXML := xmltype.createxml(pXML);
    exception
      when others then
        return - 305; --Сообщение is not valid
    end;
    /*  select extractvalue(vXML,'/ExportData/SignedData/Signature')
    into pSignature
    from dual;*/
    select extract(vXML, '/ExportData/SignedData/Signature/text()')
    into outXML
    from dual;

    if outXML is null then
      pSignature := null;
    else
      pSignature := outXml.getclobval();
    end if;

    return result;
  exception
    when others then
      return - 316; --В сообщении отсутствует информация  об отправителе и получателе
  end;

  /*
  function ExtractMessOutSignedData(pXML in clob, pSender out varchar2,pReceiver out varchar, pDate out date) return number
  is
   result number default 0;
   vXML xmltype;
  begin
    begin
        vXML:=xmltype.createxml(pXML);
    exception
     when others then
        return -305; --Сообщение is not valid
    end;

    select extractvalue(vXML,'/ExportData/SignedData/Sender'),
           extractvalue(vXML,'/ExportData/SignedData/Receiver'),
           to_date(extractvalue(vXML,'/ExportData/SignedData/TimeStamp'),'dd.mm.yyyy HH24:mi:ss')
      into pReceiver,
           pSender,
           pDate
      from dual;
   return result;
  exception
   when others then
    return -315;--В сообщении отсутствует информация  об отправителе и получателе
  end;
  */

  function ParseMessageInfo(pnode   xmldom.domnode,
                            pmessid in number) return number is
    result    number default 0;
    vMess     mess_cfm_announce_info%rowtype;
    pmessdate date;

  begin
    select send_date
    into pmessdate
    from mess_cfm
    where mess_cfm_id = pmessid; -- Пока нету в сообщении, затем изменить
    vMess.mess_cfm_id      := pmessid;
    vMess.mess_number      := pkg_messages_xml.getsingleval(pnode, 'MessageInformation/MessageNumber');
    vMess.mess_date        := to_date(pkg_messages_xml.getsingleval(pnode, 'MessageInformation/LastModifyDate'), 'yyyy-mm-dd'); --pmessdate; -- Выдергивать напрямую из XML
    vMess.mess_type        := pkg_messages_xml.getsingleval(pnode, 'MessageInformation/ReportsType');
    vMess.mess_oper_status := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/OperationStatuses');
    vMess.mess_reason_code := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/SubmissionsBasises');
    vMess.mess_suspic_code := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/SuspicionsCriterians');

    insert into mess_cfm_announce_info
    values vMess;
    return result;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      return - 1000; --Сообщение не найдено
    WHEN OTHERS THEN
      return - 3004; --- 6001; --  Ошибка экпорта данных о сообщении по причине их неверного формата

  end;

  function ParseCMFInfo(pnode   xmldom.domnode,
                        pmessid in number) return number is
    result number default 0;
    vCFM   mess_cfm_announce_subj%rowtype;
  begin
    vCFM.mess_cfm_id       := pmessid;
    vCFM.cfm_code          := pkg_messages_xml.getsingleval(pnode, 'PersonalData/OrganisationCode');
    vCFM.cfm_name          := pkg_messages_xml.getsingleval(pnode, 'PersonalData/Organisation');
    vCFM.cfm_taxcode       := pkg_messages_xml.getsingleval(pnode, 'PersonalData/RNN');
    vCFM.cfm_maincode      := pkg_messages_xml.getsingleval(pnode, 'PersonalData/IINBIN');
    vCFM.cfm_seat_area     := pkg_messages_xml.getsingleval(pnode, 'PersonalData/OrganisationArea');
    vCFM.cfm_seat_region   := pkg_messages_xml.getsingleval(pnode, 'PersonalData/OrganisationDistrict');
    vCFM.cfm_seat_sity     := pkg_messages_xml.getsingleval(pnode, 'PersonalData/OrganisationSity');
    vCFM.cfm_seat_street   := pkg_messages_xml.getsingleval(pnode, 'PersonalData/OrganisationStreet');
    vCFM.cfm_seat_house    := pkg_messages_xml.getsingleval(pnode, 'PersonalData/OrganisationHouse');
    vCFM.cfm_seat_office   := pkg_messages_xml.getsingleval(pnode, 'PersonalData/OrganisationOffice');
    vCFM.cfm_seat_postcode := pkg_messages_xml.getsingleval(pnode, 'PersonalData/OrganisationPostalIndex');
    vCFM.CFM_AC_FIRSTNAME  := pkg_messages_xml.getsingleval(pnode, 'PersonalData/HeadName');
    vCFM.CFM_AC_SECONDNAME := pkg_messages_xml.getsingleval(pnode, 'PersonalData/HeadSurname');
    vCFM.CFM_AC_MIDDLENAME := pkg_messages_xml.getsingleval(pnode, 'PersonalData/HeadMidleName');
    insert into mess_cfm_announce_subj
    values vcfm;
    return result;
  EXCEPTION
    WHEN OTHERS THEN
      return - 3001; ---6000; --Ошибка экпорта данных о субъекте, подающем отчет, по причине их неверного формата

  end;

  function ParseCFMContactInfo(pnode   xmldom.domnode,
                               pmessid in number) return number is
    result     number default 0;
    vCFMConact mess_cfm_announce_cont%rowtype;
  begin
    vCFMConact.mess_cfm_id        := pmessid;
    vCFMConact.contact_secondname := pkg_messages_xml.getsingleval(pnode, 'PersonalData/FirstName');
    vCFMConact.contact_firstname  := pkg_messages_xml.getsingleval(pnode, 'PersonalData/SecondName');
    vCFMConact.contact_middlename := pkg_messages_xml.getsingleval(pnode, 'PersonalData/MiddleName');
    vCFMConact.contact_job        := pkg_messages_xml.getsingleval(pnode, 'PersonalData/JobName');
    vCFMConact.contact_phone      := pkg_messages_xml.getsingleval(pnode, 'PersonalData/Phone');
    vCFMConact.contact_email      := pkg_messages_xml.getsingleval(pnode, 'PersonalData/Email');
    insert into mess_cfm_announce_cont
    values vcfmconact;
    return result;
  EXCEPTION
    WHEN OTHERS THEN
      return - 3002; ---6002; --Ошибка экпорта данных о контактном лице субъекта по причине их неверного формата
  end;

  function ParseOperationInfo(pnode   xmldom.domnode,
                              pmessid in number) return number is
    result     number default 0;
    vOperation mess_cfm_announce_oper%rowtype;
  begin
    vOperation.mess_cfm_id           := pmessid;
    vOperation.oper_number           := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/OperationNumber');
    vOperation.oper_idview           := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/IDViewOperation');
    vOperation.oper_idtype           := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/IDTypeOperation');
    vOperation.oper_members_count    := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/ParticipantCount');
    vOperation.oper_currency_code   := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/CurrencyCode');
    vOperation.oper_currency_code := pkg_common.ConvertToNumber(pkg_messages_xml.getsingleval(pNode, 'MessageInformation/AmountCurrency'));
    --??? vOperation.oper_currency_amount := pkg_common.ConvertToNumber(pkg_messages_xml.getsingleval(pNode, 'MessageInformation/AmountCurrency'));
    vOperation.oper_tenge_amount     := pkg_common.ConvertToNumber(pkg_messages_xml.getsingleval(pNode, 'MessageInformation/AmountCurrencyTenge'));
    vOperation.oper_reason           := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/DocOperationName');
    vOperation.oper_doc_date         := to_date(pkg_messages_xml.getsingleval(pNode, 'MessageInformation/DocOperationDate'), 'yyyy-mm-dd');
    vOperation.oper_doc_number       := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/DocOperationNumber');
    vOperation.oper_susp_first       := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/SuspicionFirst');
    vOperation.oper_susp_second      := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/SuspicionSecond');
    vOperation.oper_susp_third       := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/SuspicionThird');
    vOperation.oper_difficulties     := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/DescriptionDifficulties');
    vOperation.oper_dopinfo          := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/MoreInformation');
    insert into mess_cfm_announce_oper
    values vOperation;
    return result;
  EXCEPTION
    WHEN OTHERS THEN
      return - 3000; ---6003; --Ошибка экпорта данных об операции причине их неверного формата
      return result;
  end;

  function ParseMemberInfo(pnode     xmldom.domnode,
                           pmessid   in number,
                           pmemberid in number) return number is
    result      number default 0;
    pMember     mess_cfm_announce_memb%rowtype;
    pAcInfoPath varchar2(50 char) := 'AdditionalPersonInfo/AdditionalInformationAc/';
    pIpInfoPath varchar2(50 char) := 'AdditionalPersonInfo/AdditionalInformationIp/';
    pUrInfoPath varchar2(50 char) := 'AdditionalPersonInfo/AdditionalInformationUr/';
    pInfoPath   varchar2(50 char);
  begin
    pMember.mess_cfm_id := pmessid;
    pMember.member_id   := pmemberid + 1;

    pMember.member_kind := pkg_messages_xml.getsingleval(pnode, 'ParticipantsView');
    pMember.member_role := pkg_messages_xml.getsingleval(pnode, 'ParticipantOperation');
    if (pkg_messages_xml.getsingleval(pnode, 'MemberCommunity') = 'true') then
      pMember.member_residence := 1;
    elsif (pkg_messages_xml.getsingleval(pnode, 'MemberCommunity') = 'false') then
      pMember.member_residence := 0;
    end if;
    pMember.member_residence_countrycode := to_number(pkg_messages_xml.GetSingleOKSMTVal(pkg_messages_xml.getsingleval(pnode, 'MemberCommunitySityCode')));
    pMember.member_type                  := to_number(pkg_messages_xml.getsingleval(pnode, 'ParticipantsType'));
    pMember.member_foreign               := pkg_messages_xml.getsingleval(pnode, 'ForeignPerson');
    pMember.member_foreign_add           := pkg_messages_xml.getsingleval(pnode, 'ForeignAdditionalField');

    pMember.member_bank_code    := pkg_messages_xml.getsingleval(pnode, 'BankCode');
    pMember.member_bank_city    := pkg_messages_xml.getsingleval(pnode, 'BankCity');
    pMember.member_bank_name    := pkg_messages_xml.getsingleval(pnode, 'CorrespondentBankName');
    pMember.member_bank_account := pkg_messages_xml.getsingleval(pnode, 'CorrespondentBankAccountNumber');
    pMember.member_bank_address := pkg_messages_xml.GetSingleOKSMTVal(pkg_messages_xml.getsingleval(pnode, 'CorrespondentBankAddress'));
    pMember.member_bank_offshore_addr := pkg_messages_xml.getsingleval(pnode, 'CorrespondentBankOffshoreAddress');
    pMember.member_taxcode      := pkg_messages_xml.getsingleval(pnode, 'RNN');
    pMember.member_okpo         := pkg_messages_xml.getsingleval(pnode, 'OKPO');
    pMember.member_oked    :=  to_char(pkg_messages_xml.GetSingleOKEDVal(pkg_messages_xml.getsingleval(pnode, 'OKED'),pmessid));
    pMember.member_maincode     := pkg_messages_xml.getsingleval(pnode, 'IndividualIssue');

    pMember.member_phone    := to_number(pkg_messages_xml.getsingleval(pnode, 'PhoneNumber'));
    pMember.member_email    := pkg_messages_xml.getsingleval(pnode, 'Email');
    pMember.member_comments := pkg_messages_xml.getsingleval(pnode, 'AdditionalInformation');

    case pMember.member_type
      when 1 then
        pInfoPath := pUrInfoPath;
      when 2 then
        pInfoPath := pAcInfoPath;
      when 3 then
        pInfoPath := pIpInfoPath;
      else
        return - 3007; ---6010; --Неправильный код тип участника
    end case;

    pMember.member_reg_counrycode  := pkg_messages_xml.GetSingleOKSMTVal(pkg_messages_xml.getsingleval(pnode, pInfoPath || 'URAddress/Country'));
    pMember.member_reg_area        := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'URAddress/Area');
    pMember.member_reg_region      := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'URAddress/District');
    pMember.member_reg_sity        := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'URAddress/Town');
    pMember.member_reg_street      := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'URAddress/Street');
    pMember.member_reg_house       := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'URAddress/HomeNumber');
    pMember.member_reg_office      := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'URAddress/OfficeNumber');
    pMember.member_reg_postcode    := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'URAddress/PostalCode');
    pMember.member_seat_counrycode := pkg_messages_xml.GetSingleOKSMTVal(pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ACAddress/Country'));
    pMember.member_seat_area       := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ACAddress/Area');
    pMember.member_seat_region     := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ACAddress/District');
    pMember.member_seat_sity       := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ACAddress/Town');
    pMember.member_seat_street     := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ACAddress/Street');
    pMember.member_seat_house      := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ACAddress/HomeNumber');
    pMember.member_seat_office     := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ACAddress/OfficeNumber');
    pMember.member_seat_postcode   := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ACAddress/PostalCode');

    pMember.member_ur_name          := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'FullName');
    pMember.member_ur_fisthead_name := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'FirstHead');

    pMember.member_ac_secondname   := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'FirstName');
    pMember.member_ac_firstname    := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'SecondName');
    pMember.member_ac_middlename   := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'MiddleName');
    pMember.member_ac_doc_typecode := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'DocumentIdentity');
    pMember.member_ac_doc_number   := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'NumberDocIdentity');
    pMember.member_ac_doc_series   := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'SeriesDocIdentity');
    pMember.member_ac_doc_whom     := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'IssuedDocument');
    pMember.member_ac_doc_date     := to_date(pkg_messages_xml.getsingleval(pnode, pInfoPath || 'DateIssuance'), 'yyyy-mm-dd');
    pMember.member_ac_birth_date   := to_date(pkg_messages_xml.getsingleval(pnode, pInfoPath || 'DateBirdth'), 'yyyy-mm-dd');
    pMember.member_ac_birth_place  := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'PlaceBirdth');

    insert into mess_cfm_announce_memb
    values pMember;
    return result;
  EXCEPTION
    WHEN OTHERS THEN
      return - 3003; ---6004; --Ошибка экпорта данных об участнике операции по причине их неверного формата
      return result;
  end;

  function ParseFounderInfo(pnode      xmldom.domnode,
                            pmessid    in number,
                            pmemberid  in number,
                            pfounderid in number) return number is
    result      number default 0;
    pFounnder   mess_cfm_announce_found%rowtype;
    pAcInfoPath varchar2(50 char) := 'FounderAC/';

    pUrInfoPath varchar2(50 char) := 'founderUR/';
    pInfoPath   varchar2(50 char);
  begin
    pFounnder.Founder_Id   := pfounderid + 1;
    pFounnder.mess_cfm_id  := pmessid;
    pFounnder.member_id    := pmemberid + 1;
    pFounnder.founder_type := pkg_messages_xml.GetSingleVal(pnode, 'PersonType');
    case pFounnder.founder_type
      when 1 then
        pInfoPath := pAcInfoPath;
      when '' then   
       pInfoPath := pAcInfoPath;
        when null then   
       pInfoPath := pAcInfoPath;
      when 2 then
        pInfoPath := pUrInfoPath;
      else
        return - 3006; ---6011; --неправильно указан код типа учредителя
    end case;
    pFounnder.founder_maincode        := nvl(pkg_messages_xml.getsingleval(pnode, pInfoPath || 'BIN'), pkg_messages_xml.getsingleval(pnode, pInfoPath || 'IIN'));
    pFounnder.founder_reg_counrycode  := pkg_messages_xml.GetSingleOKSMTVal(pkg_messages_xml.getsingleval(pnode, pInfoPath || 'URAddress/Country'));
    pFounnder.founder_reg_area        := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'URAddress/Area');
    pFounnder.founder_reg_region      := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'URAddress/District');
    pFounnder.founder_reg_sity        := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'URAddress/Town');
    pFounnder.founder_reg_street      := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'URAddress/Street');
    pFounnder.founder_reg_house       := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'URAddress/HomeNumber');
    pFounnder.founder_reg_office      := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'URAddress/OfficeNumber');
    pFounnder.founder_reg_postcode    := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'URAddress/PostalCode');
    pFounnder.founder_seat_counrycode := pkg_messages_xml.GetSingleOKSMTVal(pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ACAddress/Country'));
    pFounnder.founder_seat_area       := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ACAddress/Area');
    pFounnder.founder_seat_region     := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ACAddress/District');
    pFounnder.founder_seat_sity       := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ACAddress/Town');
    pFounnder.founder_seat_street     := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ACAddress/Street');
    pFounnder.founder_seat_house      := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ACAddress/HomeNumber');
    pFounnder.founder_seat_office     := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ACAddress/OfficeNumber');
    pFounnder.founder_seat_postcode   := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ACAddress/PostalCode');
    pFounnder.founder_ur_name         := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'FounderName');

    if (pkg_messages_xml.getsingleval(pnode, pInfoPath || 'Residence') = 'true') then
      pFounnder.founder_ur_residence := 1;
    elsif (pkg_messages_xml.getsingleval(pnode, pInfoPath || 'Residence') = 'false') then
      pFounnder.founder_ur_residence := 0;
    end if;

    pFounnder.founder_ur_residence_c_code := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ResidenceSity');
    pFounnder.founder_ur_reg_whom         := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'Authority');
    pFounnder.founder_ur_reg_number       := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'RegistrationNumber');
    pFounnder.founder_ur_reg_date         := to_date(pkg_messages_xml.getsingleval(pnode, pInfoPath || 'RegistrationDate'), 'yyyy-mm-dd');
    pFounnder.founder_ur_taxcode          := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'RNN');
    pFounnder.founder_ac_secondname       := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'FirstName');
    pFounnder.founder_ac_firstname        := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'SecondName');
    pFounnder.founder_ac_middlename       := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'MiddleName');
    pFounnder.founder_ac_birth_date       := to_date(pkg_messages_xml.getsingleval(pnode, pInfoPath || 'DateBirdth'), 'yyyy-mm-dd');
    pFounnder.founder_ac_reg_number       := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'PassportNumber');
    pFounnder.founder_ac_reg_series       := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'PassportSeries');
    pFounnder.founder_ac_reg_whom         := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'IssuedDocument');
    pFounnder.founder_ac_reg_date         := to_date(pkg_messages_xml.getsingleval(pnode, pInfoPath || 'DateIssuance'), 'yyyy-mm-dd');

    insert into mess_cfm_announce_found
    values pFounnder;
    return result;
  EXCEPTION
    WHEN OTHERS THEN
      return - 3005; ---6004; --Ошибка экпорта данных об участнике операции по причине их неверного формата
      return result;
  end;

  function ParseFoundersInfo(pnode     xmldom.domnode,
                             pmessid   in number,
                             pmemberid in number) return number is
    result    number default 0;
    vNodeList xmldom.domnodelist;
    vnode     xmldom.domnode;

  begin
    vNodeList := pkg_messages_xml.GetNodesList(pnode, 'FoundersList/Founder');
    for i in 0 .. xmldom.getLength(vNodeList) - 1 loop
      vnode  := xmldom.item(vNodeList, i);
      result := ParseFounderInfo(vnode, pmessid, pmemberid, i);
      if result <> 0 then
        return result;
      end if;
    end loop;
    return result;
  EXCEPTION
    WHEN OTHERS THEN
      return - 3005; --Ошибка экпорта данных об учредителе участнике операции по причине их неверного формата
      return result;
  end;

  function ParseMembersInfo(pnode   xmldom.domnode,
                            pmessid in number) return number is
    result    number default 0;
    vNodeList xmldom.domnodelist;
    vnode     xmldom.domnode;

  begin
    vNodeList := pkg_messages_xml.GetNodesList(pnode, 'ParticipantsList/Participant');
    for i in 0 .. xmldom.getLength(vNodeList) - 1 loop
      vnode  := xmldom.item(vNodeList, i);
      result := ParseMemberInfo(vnode, pmessid, i);
      if result <> 0 then
        return result;
      end if;
      result := ParseFoundersInfo(vnode, pmessid, i);
      if result <> 0 then
        return result;
      end if;
    end loop;
    ------
    return result;
  end;

  function parseAnnounceXML(pmessid in number) return number is
    result   number default 0;
    vres     number default 0;
    vnode    xmldom.domnode;
    pxmlclob clob;
    tempres  number default 0;

  begin
    select mess_xml
    into pxmlclob
    from mess_cfm_xml
    where mess_cfm_id = pmessid;

    vnode  := pkg_messages_xml.GetParsedXML(pxmlclob);
    result := ParseMessageInfo(vnode, pmessid);

    if result <> 0 then
      vres    := SetErrorCode(pmessid, result);
      tempres := result;
    end if;
    result := ParseCmfInfo(vnode, pmessid);

    if result <> 0 then
      vres    := SetErrorCode(pmessid, result);
      tempres := result;
    end if;
    result := ParseCfmcontactInfo(vnode, pmessid);

    if result <> 0 then
      vres    := SetErrorCode(pmessid, result);
      tempres := result;
    end if;
    result := ParseOperationInfo(vnode, pmessid);

    if result <> 0 then
      vres    := SetErrorCode(pmessid, result);
      tempres := result;
    end if;
    result := ParseMembersInfo(vnode, pmessid);

    if result <> 0 then
      vres    := SetErrorCode(pmessid, result);
      tempres := result;
    end if;
    if tempres = 0 then
      vres := SetErrorCode(pmessid, 0);
      return vres;
    end if;
    return tempres;
  EXCEPTION
    WHEN no_data_found THEN
      return - 1000;
    WHEN OTHERS THEN
      return - 305; --Неверный XML документ  -- изменть
      return result;
  end;

  function parseCheckXML(pmessid in number) return number is
    result   number default 0;
    vnode    xmldom.domnode;
    pxmlclob clob;

    vCheck mess_cfm_check%rowtype;
  begin
    select mess_xml
    into pxmlclob
    from mess_cfm_xml
    where mess_cfm_id = pmessid;
    vCheck.Mess_Cfm_Id := pmessid;

    vnode := pkg_messages_xml.GetParsedXML(pxmlclob);

    vCheck.Errorcode := pkg_messages_xml.getsingleval(vnode, 'ErrorCode');
    vCheck.ErrorName := pkg_messages_xml.getsingleval(vnode, 'ErrorName');
    insert into mess_cfm_check
    values vcheck;

    return result;
  EXCEPTION
    WHEN no_data_found THEN
      return - 1000;
    WHEN OTHERS THEN
      result := SetErrorCode(pmessid, -3008);
      return - 3008; --Неверный формат данных в квитанции
      return result;
  end;

  function parseDopInfXML(pmessid in number) return number is
    result   number default 0;
    vnode    xmldom.domnode;
    pxmlclob clob;

    vDopInf mess_cfm_dopinf%rowtype;
  begin
    select mess_xml
    into pxmlclob
    from mess_cfm_xml
    where mess_cfm_id = pmessid;
    vDopInf.Mess_Cfm_Id := pmessid;

    vnode := pkg_messages_xml.GetParsedXML(pxmlclob);

    vDopInf.Description := pkg_messages_xml.getsingleval(vnode, 'Description');
    vDopInf.Days_Count  := pkg_messages_xml.getsingleval(vnode, 'CountDays');
    insert into mess_cfm_dopinf
    values vDopInf;

    return result;
  EXCEPTION
    WHEN no_data_found THEN
      return - 1000;
    WHEN OTHERS THEN
      result := SetErrorCode(pmessid, -3009);
      return - 3009; --Неверный формат данных в ответе на дополнительную информацию
      return result;
  end;

  function parseDopInfAnswXML(pmessid in number) return number is
    result    number default 0;
    vnode     xmldom.domnode;
    pnode     xmldom.domnode;
    vNodeList xmldom.domnodelist;
    pxmlclob  clob;

    vDopInfAnsw mess_cfm_dopinf_answ%rowtype;
  begin
    select mess_xml
    into pxmlclob
    from mess_cfm_xml
    where mess_cfm_id = pmessid;
    vDopInfAnsw.Mess_Cfm_Id := pmessid;

    vnode := pkg_messages_xml.GetParsedXML(pxmlclob);

    vNodeList := pkg_messages_xml.GetNodesList(pnode, 'Attachments/Attachment');
    for i in 0 .. xmldom.getLength(vNodeList) - 1 loop
      vnode                     := xmldom.item(vNodeList, i);
      vDopInfAnsw.Attach_Id     := i;
      vDopInfAnsw.Attach_Name   := pkg_messages_xml.getsingleval(vnode, 'FileName');
      vDopInfAnsw.Attach_Length := pkg_messages_xml.getsingleval(vnode, 'Length');
      insert into mess_cfm_dopinf_answ
      values vDopInfansw;
    end loop;

    return result;
  EXCEPTION
    WHEN no_data_found THEN
      return - 1000;
    WHEN OTHERS THEN
      result := SetErrorCode(pmessid, -3010); -- Неверный формат данных в ответе на дополнительную информацию
      return - 3010; --Неверный XML документ
      return result;
  end;

  function parseUpdateRequestXML(pmessid in number) return number --Запрос на обновление
   is
    result number default 0;
    vnode  xmldom.domnode;

    pxmlclob clob;

    vUpdate mess_cfm_update_info%rowtype;
  begin
    select mess_xml
    into pxmlclob
    from mess_cfm_xml
    where mess_cfm_id = pmessid;
    vUpdate.Mess_Cfm_Id := pmessid;

    vnode := pkg_messages_xml.GetParsedXML(pxmlclob);

    vUpdate.Update_Version := pkg_messages_xml.getsingleval(vnode, 'RequestedVersion');
    -- vUpdate.Release_Date:=to_date(pkg_messages_xml.getsingleval(vnode,'ReleaseDate'),'yyyy-mm-dd');

    insert into mess_cfm_update_info
    values vupdate;
    return result;
  EXCEPTION
    WHEN no_data_found THEN
      return - 1000;
    WHEN OTHERS THEN
      result := SetErrorCode(pmessid, -3013); --Неверный формат данных в запросе на обновление
      return - 3013; --Неверный XML документ
      return result;
  end;

  function ParseUpdateInfoXML(pmessid in number) return number is
    result     number default 0;
    vnode      xmldom.domnode;
    pnode      xmldom.domnode;
    pnode1     xmldom.domnode;
    vNodeList  xmldom.domnodelist;
    vNodeList1 xmldom.domnodelist;
    pxmlclob   clob;

    vUpdateVersion varchar2(300);
    vDateRelease   date;
    vmodulename    varchar2(100 char);

  begin
    select mess_xml
    into pxmlclob
    from mess_cfm_xml
    where mess_cfm_id = pmessid;
    vnode := pkg_messages_xml.GetParsedXML(pxmlclob);

    --Головное обновление
    vUpdateVersion := pkg_messages_xml.getsingleval(vnode, 'UpdateVersion');
    vDateRelease   := to_date(pkg_messages_xml.getsingleval(vnode, 'ReleaseDate'), 'yyyy-mm-dd');
    insert into mess_cfm_update_info
      (mess_cfm_id,
       update_version,
       release_date)
    values
      (pmessid,
       vupdateversion,
       vDateRelease);

    if pkg_updates_cfm.existsInHisory(vUpdateVersion, vDateRelease) = 0 then
      insert into updates_cfm_hist
        (messid,
         version,
         daterelease)
      values
        (pmessid,
         vUpdateVersion,
         vDateRelease);

      --Используемые модули
      vNodeList := pkg_messages_xml.GetNodesList(vnode, 'Using/Use');
      for i in 0 .. xmldom.getLength(vnodelist) - 1 loop
        pnode       := xmldom.item(vnodelist, i);
        vmodulename := xslprocessor.valueOf(pnode, 'text()');
        insert into updates_cfm_hist_mod
          (version,
           modulename,
           messid)
        values
          (vUpdateVersion,
           vmodulename,
           pmessid);
      end loop;
    end if;

    --История версий обновлений
    vNodeList := pkg_messages_xml.GetNodesList(vnode, 'Histories/History');
    for i in 0 .. xmldom.getLength(vnodelist) - 1 loop
      pnode          := xmldom.item(vnodelist, i);
      vUpdateVersion := pkg_messages_xml.getsingleval(pnode, 'Version');
      vDateRelease   := to_date(pkg_messages_xml.getsingleval(pnode, 'ReleaseDate'), 'yyyy-mm-dd');

      if pkg_updates_cfm.existsInHisory(vUpdateVersion, vDateRelease) = 0 then
        insert into updates_cfm_hist
          (messid,
           version,
           daterelease)
        values
          (pmessid,
           vUpdateVersion,
           vDateRelease);

        --Используемые модули
        vnodelist1 := pkg_messages_xml.GetNodesList(pnode, 'Using/Use');
        for j in 0 .. xmldom.getLength(vnodelist1) - 1 loop
          pnode1      := xmldom.item(vnodelist1, j);
          vmodulename := xslprocessor.valueOf(pnode1, 'text()');
          insert into updates_cfm_hist_mod
            (version,
             modulename,
             messid)
          values
            (vUpdateVersion,
             vmodulename,
             pmessid);
        end loop;
      end if;
    end loop;

    return result;
  EXCEPTION
    WHEN no_data_found THEN
      return - 1000;
    WHEN OTHERS THEN
      result := SetErrorCode(pmessid, -3011); --Неверный формат данных в информационнном сообщении об обновлении
      return - 3011; --Неверный XML документ
      return result;
  end;

  function ParseUpdateXML(pmessid in number) return number is
    result    number default 0;
    vnode     xmldom.domnode;
    vNodeList xmldom.domnodelist;
    pxmlclob  clob;

    vUpdateVersion varchar2(300);
    vDateRelease   date;
    vmodulename    varchar2(100 char);
    cursor moduleCur is
      select distinct modulename
      from updates_cfm_modulenames;

  begin
    select mess_xml
    into pxmlclob
    from mess_cfm_xml
    where mess_cfm_id = pmessid;
    vnode          := pkg_messages_xml.GetParsedXML(pxmlclob);
    vUpdateVersion := pkg_messages_xml.getsingleval(vnode, 'UpdateVersion');
    vDateRelease   := to_date(pkg_messages_xml.getsingleval(vnode, 'ReleaseDate'), 'yyyy-mm-dd');
    insert into mess_cfm_update_info
      (mess_cfm_id,
       update_version,
       release_date)
    values
      (pmessid,
       vupdateversion,
       vDateRelease);

    insert into updates_cfm_hist
      (messid,
       version,
       daterelease)
    values
      (pmessid,
       vUpdateVersion,
       vDateRelease);

    --Используемые модули
    vnodelist := xslprocessor.selectNodes(vnode, 'UpdateInformations/UpdateInformation/Using/Use/ModuleName');
    for i in 0 .. xmldom.getLength(vnodelist) - 1 loop
      vnode       := xmldom.item(vnodelist, i);
      vmodulename := xslprocessor.valueOf(vnode, 'text()');
      insert into updates_cfm_modulenames
        (modulename)
      values
        (vmodulename);
    end loop;

    for modulerow in moduleCur loop
      insert into updates_cfm_hist_mod
        (version,
         modulename,
         messid)
      values
        (vUpdateVersion,
         modulerow.modulename,
         pmessid);
    end loop;

    return result;
  EXCEPTION
    WHEN no_data_found THEN
      return - 1000;
    WHEN OTHERS THEN
      result := SetErrorCode(pmessid, -3012); --Неверный формат данных в информационнном сообщении собновлением
      return - 3012; --Неверный XML документ
      return result;
  end;

  function SetErrorCode(pmessid  in number,
                        perrcode in number) return number is
  begin
    update mess_cfm
    set int_err_code = perrcode
    where mess_cfm_id = pmessid;
    return 0;

  exception
    when no_data_found then
      return - 1000;
  end;

  function getcolumn_length(p_table_name in varchar2,
                            p_name       in varchar2,
                            p_length     out number) return number is
    result number default 0;
  begin

    select data_length
    into p_length
    from USER_TAB_COLUMNS
    where table_name = upper(p_table_name)
    and column_name = upper(p_name);
    return result;

  exception
    when others then
      return - 2099;

  end;

--function GetNoDateVal()

end PKG_MESS_CFM_1;
/

CREATE OR REPLACE PUBLIC SYNONYM PKG_MESS_CFM_1 FOR KFM.PKG_MESS_CFM_1
/


GRANT EXECUTE ON KFM.PKG_MESS_CFM_1 TO KFM_ROLE
/


CREATE OR REPLACE PACKAGE BODY KFM."PKG_MESS_CFM_2_1" AS
  --экспорт данных в xml согласно версии 3
  --
  function mess_cfm_2_empty_xml return xmltype as
    v_result xmltype;
  begin
    v_result := null;

    SELECT XMLElement("Root",
                      XMLATTRIBUTES('http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi", 'http://www.w3.org/2001/XMLSchema' as "xmlns:xsd"),
                      pkg_rqst_cfm.rqst_personal_data,
                      message_information_2_xml_v1,
                      XMLElement("Version", 'C6EC3F8D-2BE1-46A9-841B-5CF1130F0A12'),
                      XMLElement("DocumentUniqueIdentifier", sys_guid()))
    INTO v_result
    FROM dual;

    return v_result;
  end;
  --
  function mess_cfm_2_xml(p_MESS_CFM_ID in NUMBER) return xmltype as
    v_result xmltype;
  begin
    v_result := null;

    SELECT XMLElement("Root",
                      XMLATTRIBUTES('http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi", 'http://www.w3.org/2001/XMLSchema' as "xmlns:xsd"),
                      personal_data_2_xml_v3(p_MESS_CFM_ID),
                      message_information_2_xml_v2(p_MESS_CFM_ID),
                      refers_2_xml(p_MESS_CFM_ID),
                      participants_2_xml(p_MESS_CFM_ID),
                      XMLElement("Version", 'C6EC3F8D-2BE1-46A9-841B-5CF1130F0A12'),
                      XMLElement("DocumentUniqueIdentifier", ta.mess_form_guid))
    INTO v_result
    FROM mess_cfm ta
    WHERE ta.MESS_CFM_ID = p_MESS_CFM_ID;

    return v_result;
  end;
  --
  function personal_data_2_xml_v2(p_MESS_CFM_ID in NUMBER) return xmltype as
    v_result xmltype;
  begin
    v_result := null;

    SELECT XMLElement("PersonalData",
                      XMLElement("FirstName", tb.contact_secondname),
                      XMLElement("SecondName", tb.contact_firstname),
                      XMLElement("MiddleName", tb.contact_middlename),
                      XMLElement("JobName", tb.contact_job),
                      XMLElement("Phone", tb.contact_phone),
                      XMLElement("Email", tb.contact_email),
                      --
                      XMLElement("HeadName", ta.cfm_ac_firstname),
                      XMLElement("HeadSurname", ta.cfm_ac_secondname),
                      XMLElement("HeadMidleName", ta.cfm_ac_middlename),
                      --
                      nvl2(ta.cfm_code, XMLElement("OrganisationCode", ta.cfm_code), XMLElement("OrganisationCode", XMLATTRIBUTES('true' as "xsi:nil"))),
                      XMLElement("Organisation", ta.cfm_name),
                      XMLElement("OrganisationOPF", ta.cfm_ur_opf_code),
                      XMLElement("OrganisationArea", XMLATTRIBUTES(ta.cfm_seat_area_code as "Code"), ta.cfm_seat_area),
                      XMLElement("OrganisationCity", XMLATTRIBUTES(ta.cfm_seat_sity_code as "Code"), ta.cfm_seat_sity),
                      XMLElement("OrganisationDistrict", XMLATTRIBUTES(ta.cfm_seat_region_code as "Code"), ta.cfm_seat_region),
                      XMLElement("OrganisationStreet", XMLATTRIBUTES(ta.cfm_seat_street_code as "Code"), ta.cfm_seat_street),
                      XMLElement("OrganisationHouse", XMLATTRIBUTES(ta.cfm_seat_house_code as "Code"), ta.cfm_seat_house),
                      XMLElement("OrganisationOffice", XMLATTRIBUTES(ta.cfm_seat_office_code as "Code"), ta.cfm_seat_office),
                      XMLElement("OrganisationPostalIndex", ta.cfm_seat_postcode),
                      XMLElement("RNN", ta.cfm_taxcode),
                      XMLElement("IINBIN", ta.cfm_maincode),
                      CASE ta.Cfm_Isac
                        WHEN 1 THEN
                         XMLElement("AdditionalAcData",
                                    XMLATTRIBUTES('True' as "IsAc"),
                                    XMLElement("DocumentIdentity", ta.Cfm_Doc_Typecode),
                                    XMLElement("SeriesDocIdentity", ta.Cfm_Doc_Series),
                                    XMLElement("NumberDocIdentity", ta.Cfm_Doc_Number),
                                    XMLElement("DateIssuance", to_char(ta.Cfm_Doc_Date, 'dd.mm.yyyy')),
                                    XMLElement("DocumentIssued", ta.Cfm_Doc_Whom))
                        ELSE
                         XMLElement("AdditionalAcData",
                                    XMLATTRIBUTES('False' as "IsAc"),
                                    XMLElement("DocumentIdentity", ta.Cfm_Doc_Typecode),
                                    XMLElement("SeriesDocIdentity", ta.Cfm_Doc_Series),
                                    XMLElement("NumberDocIdentity", ta.Cfm_Doc_Number),
                                    XMLElement("DateIssuance", to_char(ta.Cfm_Doc_Date, 'dd.mm.yyyy')),
                                    XMLElement("DocumentIssued", ta.Cfm_Doc_Whom))
                      END) AS xmltype
    INTO v_result
    FROM mess_cfm_announce_subj ta
    LEFT OUTER JOIN mess_cfm_announce_cont tb
    ON ta.MESS_CFM_ID = tb.MESS_CFM_ID
    WHERE ta.MESS_CFM_ID = p_MESS_CFM_ID;

    return v_result;
  end;
  --
  function personal_data_2_xml_v3(p_MESS_CFM_ID in NUMBER) return xmltype as
    v_result xmltype;
  begin
    v_result := null;

    SELECT XMLElement("PersonalData",
                      XMLElement("FirstName", tb.lastname),
                      XMLElement("SecondName", tb.firstname),
                      XMLElement("MiddleName", tb.middlename),
                      XMLElement("JobName", tb.jobname),
                      XMLElement("Phone", tb.phones),
                      XMLElement("Email", tb.emails),
                      --
                      XMLElement("HeadName", ta.cfm_ac_firstname),
                      XMLElement("HeadSurname", ta.cfm_ac_secondname),
                      XMLElement("HeadMidleName", ta.cfm_ac_middlename),
                      --
                      nvl2(ta.cfm_code, XMLElement("OrganisationCode", ta.cfm_code), XMLElement("OrganisationCode", XMLATTRIBUTES('true' as "xsi:nil"))),
                      XMLElement("Organisation", ta.cfm_name),
                      XMLElement("OrganisationOPF", ta.cfm_ur_opf_code),
                      XMLElement("OrganisationArea", XMLATTRIBUTES(ta.cfm_seat_area_code as "Code"), ta.cfm_seat_area),
                      XMLElement("OrganisationCity", XMLATTRIBUTES(ta.cfm_seat_sity_code as "Code"), ta.cfm_seat_sity),
                      XMLElement("OrganisationDistrict", XMLATTRIBUTES(ta.cfm_seat_region_code as "Code"), ta.cfm_seat_region),
                      XMLElement("OrganisationStreet", XMLATTRIBUTES(ta.cfm_seat_street_code as "Code"), ta.cfm_seat_street),
                      XMLElement("OrganisationHouse", XMLATTRIBUTES(ta.cfm_seat_house_code as "Code"), ta.cfm_seat_house),
                      XMLElement("OrganisationOffice", XMLATTRIBUTES(ta.cfm_seat_office_code as "Code"), ta.cfm_seat_office),
                      XMLElement("OrganisationPostalIndex", ta.cfm_seat_postcode),
                      XMLElement("RNN", ta.cfm_taxcode),
                      XMLElement("IINBIN", ta.cfm_maincode),
                      CASE ta.Cfm_Isac
                        WHEN 1 THEN
                         XMLElement("AdditionalAcData",
                                    XMLATTRIBUTES('True' as "IsAc"),
                                    XMLElement("DocumentIdentity", ta.Cfm_Doc_Typecode),
                                    XMLElement("SeriesDocIdentity", ta.Cfm_Doc_Series),
                                    XMLElement("NumberDocIdentity", ta.Cfm_Doc_Number),
                                    XMLElement("DateIssuance", to_char(ta.Cfm_Doc_Date, 'dd.mm.yyyy')),
                                    XMLElement("DocumentIssued", ta.Cfm_Doc_Whom))
                        ELSE
                         XMLElement("AdditionalAcData",
                                    XMLATTRIBUTES('False' as "IsAc"),
                                    XMLElement("DocumentIdentity", ta.Cfm_Doc_Typecode),
                                    XMLElement("SeriesDocIdentity", ta.Cfm_Doc_Series),
                                    XMLElement("NumberDocIdentity", ta.Cfm_Doc_Number),
                                    XMLElement("DateIssuance", to_char(ta.Cfm_Doc_Date, 'dd.mm.yyyy')),
                                    XMLElement("DocumentIssued", ta.Cfm_Doc_Whom))
                      END) AS xmltype
    INTO v_result
    FROM mess_cfm_announce_subj ta,
         users                  tb
    WHERE ta.MESS_CFM_ID = p_MESS_CFM_ID
    AND tb.userid = pkg_users.GetCurrentUserId; ---- исправить

    return v_result;
  end;
  --
  function message_information_2_xml_v1 return xmltype as
    v_result xmltype;
  begin
    v_result := null;

    SELECT XMLElement("MessageInformation",
                      XMLElement("DocumentType", 1),
                      --XMLElement("MessageNumber", null),
                      XMLElement("MessageNumber", pkg_messages_cfm.GetTempMessNumb),
                      XMLElement("LastModifyDate", to_char(sysdate, 'dd.mm.yyyy')),
                      XMLElement("TransactionDate", null),
                      XMLElement("MessTransDate", null),
                      XMLElement("ViewOperationId", null),
                      XMLElement("EknpId", XMLATTRIBUTES('False' as "IsEknpNotSetup"), null),
                      XMLElement("OperationNumber", null),
                      XMLElement("DocOperationReason", null),
                      XMLElement("DocOperationDate", null),
                      XMLElement("DocOperationNumber", null),
                      XMLElement("CurrencyCodeId", null),
                      XMLElement("AmountCurrency", null),
                      XMLElement("AmountCurrencyTenge", null),
                      XMLElement("OperationStatusId", null),
                      XMLElement("ReasonFilingId", null),
                      XMLElement("SuspicionsCriterians", null),
                      XMLElement("SuspicionFirst", null),
                      XMLElement("SuspicionSecond", null),
                      XMLElement("SuspicionThird", null),
                      XMLElement("DescriptionDifficulties", null),
                      XMLElement("MoreInformation", null),
                      XMLElement("ParticipantCount", null),
                      XMLElement("MerchTypes", null),
                      XMLElement("MerchReginfo", null),
                      XMLElement("ReferCount", null)) AS xmltype
    INTO v_result
    FROM dual;

    return v_result;
  end;
  --
  function message_information_2_xml_v2(p_MESS_CFM_ID in NUMBER) return xmltype as
    v_result xmltype;
  begin
    v_result := null;

    SELECT XMLElement("MessageInformation",
                      XMLElement("DocumentType", ta.mess_type),
                      XMLElement("MessageNumber", ta.mess_number),
                      XMLElement("LastModifyDate", to_char(ta.mess_date, 'dd.mm.yyyy')),
                      XMLElement("TransactionDate", to_char(nvl(tb.Oper_Trans_Date, ta.mess_Trans_Date), 'dd.mm.yyyy hh24:mi:ss')),
                      XMLElement("MessTransDate", to_char(nvl(tb.Oper_Trans_Date, ta.mess_Trans_Date), 'dd.mm.yyyy hh24:mi:ss')),
                      XMLElement("ViewOperationId", tb.oper_idview),
                      XMLElement("EknpId", XMLATTRIBUTES(pkg_common.getbool(tb.oper_isnotidtyped) as "IsEknpNotSetup"), tb.oper_idtype),
                      XMLElement("OperationNumber", tb.oper_number),
                      XMLElement("DocOperationReason", tb.oper_reason),
                      XMLElement("DocOperationDate", to_char(tb.oper_doc_date, 'dd.mm.yyyy')),
                      XMLElement("DocOperationNumber", tb.oper_doc_number),
                      XMLElement("CurrencyCodeId", tb.oper_currency_code),
                      XMLElement("AmountCurrency", tb.oper_currency_amount),
                      XMLElement("AmountCurrencyTenge", tb.oper_tenge_amount),
                      XMLElement("OperationStatusId", ta.mess_oper_status),
                      XMLElement("ReasonFilingId", ta.mess_reason_code),
                      XMLElement("SuspicionsCriterians", ta.mess_suspic_code),
                      XMLElement("SuspicionFirst", tb.oper_susp_first),
                      XMLElement("SuspicionSecond", tb.oper_susp_second),
                      XMLElement("SuspicionThird", tb.oper_susp_third),
                      XMLElement("DescriptionDifficulties", tb.oper_difficulties),
                      XMLElement("MoreInformation", tb.oper_dopinfo),
                      XMLElement("ParticipantCount", tb.oper_members_count),
                      XMLElement("MerchTypes", tb.oper_merch_types),
                      XMLElement("MerchReginfo", tb.oper_merch_reginfo),
                      XMLElement("ReferCount", tb.oper_refers_count)) AS xmltype
    INTO v_result
    FROM mess_cfm_announce_info ta
    LEFT OUTER JOIN mess_cfm_announce_oper tb
    ON ta.MESS_CFM_ID = tb.MESS_CFM_ID
    WHERE ta.MESS_CFM_ID = p_MESS_CFM_ID;

    return v_result;
  end;
  --
  function refers_2_xml(p_MESS_CFM_ID in NUMBER) return xmltype as
    v_result xmltype;
  begin
    v_result := null;

    SELECT XMLElement("References",
                      XMLAgg(XMLElement("Reference",
                                        XMLElement("ReferenceId", ta.referid),
                                        XMLElement("ReferenceOperationNumber", ta.refer_doc_oper),
                                        XMLElement("ReferenceDocOperationDate", to_char(ta.refer_doc_date, 'dd.mm.yyyy')),
                                        XMLElement("ReferencerDocOperationNumber", ta.refer_doc_num)))) AS xmltype
    INTO v_result
    FROM mess_cfm_refer ta
    WHERE ta.MESS_CFM_ID = p_MESS_CFM_ID;

    return v_result;
  end;
  --
  function participants_2_xml(p_MESS_CFM_ID in NUMBER) return xmltype as
    v_result xmltype;
  begin
    v_result := null;

    SELECT XMLElement("Participants",
                      XMLAgg(XMLElement("Participant",
                                        XMLElement("MemberId", ta.member_membercode),
                                        XMLElement("ParticipantsView", ta.member_kind),
                                        XMLElement("ParticipantsType", ta.member_type),
                                        XMLElement("IsClientSubject", ta.member_isclient),
                                       -- XMLElement("MemberCommunityCityCode", OKSMT_2_xml(ta.member_residence_countrycode,'MemberCommunityCityCode')),
                                       OKSMT_2_xml(ta.member_residence_countrycode,'MemberCommunityCityCode'),
                                        XMLElement("ForeignPerson", ta.member_foreign),
                                        XMLElement("CorrespondentBank",
                                                   XMLElement("AccountNumber", ta.member_bank_account),
                                                   XMLElement("Name", ta.member_bank_name),
                                                   XMLElement("Code", ta.member_bank_code),
                                                   XMLElement("BankCity", ta.member_bank_city),
                                                   XMLElement("BankAddress", OKSMT_2_xml(ta.member_bank_address,'BankAddress')),
                                                   XMLElement("BankOffshoreAddr", ta.MEMBER_BANK_OFFSHORE_ADDR),
                                                   correspondents_info_2_xml(p_MESS_CFM_ID, ta.member_id)),
                                        XMLElement("RNN", ta.member_taxcode),
                                   --     XMLElement("OKED", ta.member_oked),
                                        OKED_2_xml(p_MESS_CFM_ID, ta.member_id),
                                        XMLElement("IndividualIssue", ta.member_maincode),
                                        XMLElement("PhoneNumber", ta.member_phone),
                                        XMLElement("Email", ta.member_email),
                                        XMLElement("AdditionalInformation", ta.member_comments),
                                        XMLElement("MoneyTransSys", ta.money_trans_sys),
                                        founders_2_xml(p_MESS_CFM_ID, ta.member_id),
                                        additional_person_info_2_xml(p_MESS_CFM_ID, ta.member_id)))) AS xmltype
    INTO v_result
    FROM mess_cfm_announce_memb ta
    WHERE ta.MESS_CFM_ID = p_MESS_CFM_ID;

    return v_result;
  end;
  --

  function correspondents_info_2_xml(p_MESS_CFM_ID in NUMBER,
                                     p_MEMBER_ID   in NUMBER) return xmltype as
    v_result xmltype;
  begin
    v_result := null;

    SELECT XMLElement("CorrespondentsInformations",
                      XMLAgg(XMLElement("CorrespondentInformation", XMLElement("AccountNumber", ta.accountnumber), XMLElement("BankName", ta.bankname), XMLElement("BankCode", ta.bankcode), XMLElement("BankAddress", OKSMT_2_xml(ta.bankaddress,'BankAddress'))))) AS xmltype
    INTO v_result
    FROM mess_cfm_announce_accounts ta
    WHERE ta.MESS_CFM_ID = p_MESS_CFM_ID
    and ta.MEMBERID = p_MEMBER_ID;

    return v_result;
  end;
  --

-- roma 1608
  function OKED_2_xml(p_MESS_CFM_ID in NUMBER,
                                     p_MEMBER_ID   in NUMBER) return xmltype as
    v_result xmltype;
  begin
    v_result := null;
    
    SELECT XMLELEMENT ("OKED", AC.OUTER_CODE) AS xmltype
      INTO v_result
      FROM mess_cfm_announce_memb tm, ANALYTICCODES ac
     WHERE     tm.MESS_CFM_ID = p_MESS_CFM_ID
           AND tm.member_id = p_MEMBER_ID
           AND AC.ANALYTICCODE = tm.member_oked
           AND AC.ANALYTICTYPE = 41021; -- OKED
       
    return v_result;
  end;
  --

  -- roma 1608
  function OKSMT_2_xml(p_AnCode in number, FieldName in varchar2) return xmltype as
    v_result xmltype;

  begin
    v_result := null;
   SELECT XMLELEMENT ("MemberCommunityCityCode", AC.OUTER_CODE) AS xmltype
      INTO v_result
      FROM ANALYTICCODES ac
     WHERE AC.ANALYTICTYPE = 41019 -- ContryCodes
		AND AC.ANALYTICCODE = p_AnCode;

   return v_result;
 exception
    when others then
         return null; --Ошибка разбора XML
  end;
  --

 function founders_2_xml(p_MESS_CFM_ID in NUMBER,
                          p_MEMBER_ID   in NUMBER) return xmltype as
    v_result xmltype;
  begin
    v_result := null;

    SELECT XMLElement("Founders",
                      XMLAgg(CASE 
                               WHEN nvl(ta.founder_type,1) = 1 THEN
                                XMLElement("Founder",
                                           XMLElement("FounderType", ta.founder_type),
                                           XMLElement("FounderOPF", ta.founder_ur_opf_code),
                                           XMLElement("Name", ta.founder_ur_name),
                                           XMLElement("FirstName", null),
                                           XMLElement("SecondName", null),
                                           XMLElement("MiddleName", null),
                                           -- XMLElement("MemberCommunityCityCode", OKSMT_2_xml(ta.founder_ur_residence_c_code,'MemberCommunityCityCode')))
                                           OKSMT_2_xml(ta.founder_ur_residence_c_code,'MemberCommunityCityCode'))
                               WHEN nvl(ta.founder_type,1) = 2 THEN
                                XMLElement("Founder",
                                           XMLElement("FounderType", ta.founder_type),
                                           XMLElement("FounderOPF", null),
                                           XMLElement("Name", null),
                                           XMLElement("FirstName", ta.founder_ac_firstname),
                                           XMLElement("SecondName", ta.founder_ac_secondname),
                                           XMLElement("MiddleName", ta.founder_ac_middlename),
                                           --XMLElement("MemberCommunityCityCode", OKSMT_2_xml(ta.founder_ur_residence_c_code,'MemberCommunityCityCode')))
                                           OKSMT_2_xml(ta.founder_ur_residence_c_code,'MemberCommunityCityCode'))
                               WHEN nvl(ta.founder_type,1) = 3 THEN
                                XMLElement("Founder",
                                           XMLElement("FounderType", ta.founder_type),
                                           XMLElement("FounderOPF", null),
                                           XMLElement("Name", null),
                                           XMLElement("FirstName", ta.founder_ac_firstname),
                                           XMLElement("SecondName", ta.founder_ac_secondname),
                                           XMLElement("MiddleName", ta.founder_ac_middlename),
                                           XMLElement("MemberCommunityCityCode", null))
                                WHEN nvl(ta.founder_type,1) > 3 THEN  --roma
                                XMLElement("Founder",
                                           XMLElement("FounderType", ta.founder_type),
                                           XMLElement("FounderOPF", ta.founder_type),
                                           XMLElement("Name", ta.founder_ur_name),
                                           XMLElement("FirstName", ta.founder_ac_firstname),
                                           XMLElement("SecondName", ta.founder_ac_secondname),
                                           XMLElement("MiddleName", ta.founder_ac_middlename),
                                          -- XMLElement("MemberCommunityCityCode", OKSMT_2_xml(ta.founder_ur_residence_c_code,'MemberCommunityCityCode')))
                                           OKSMT_2_xml(ta.founder_ur_residence_c_code,'MemberCommunityCityCode'))
                             END)) AS xmltype
    INTO v_result
    FROM mess_cfm_announce_found ta
    WHERE ta.MESS_CFM_ID = p_MESS_CFM_ID
    and ta.MEMBER_ID = p_MEMBER_ID;

    return v_result;
  end;
  --


function additional_person_info_2_xml(p_MESS_CFM_ID in NUMBER,
                                        p_MEMBER_ID   in NUMBER) return xmltype as
    v_result xmltype;
  begin
    v_result := null;

    SELECT CASE ta.member_type
             WHEN 1 THEN
              XMLElement("AdditionalPersonInfo",
                         XMLElement("PersonType", 1),
                         XMLElement("AdditionalInformationUr",
                                       XMLElement("URAddress",
                                                  XMLElement("Country", OKSMT_2_xml(member_reg_counrycode,'Country')),
                                                  XMLElement("Area", XMLATTRIBUTES(ta.member_reg_area_code as "Code"),
                                                               CASE WHEN ta.member_reg_counrycode=398 THEN
                                                                 PKG_KATOHOLDING.GetKatoName(ta.member_reg_area_code,1)
                                                                 ELSE 
                                                                 cast(ta.member_reg_area as nvarchar2(300))
                                                                END),                                                
                                                  XMLElement("District", XMLATTRIBUTES(ta.member_reg_region_code as "Code"), 
                                                  CASE WHEN ta.member_reg_counrycode=398 THEN 
                                                   PKG_KATOHOLDING.GetKatoName(ta.member_reg_region_code,1)
                                                   ELSE 
                                                   cast(ta.member_reg_region as nvarchar2(300))
                                                   END),
                                                  XMLElement("Town", XMLATTRIBUTES(ta.member_reg_sity_code as "Code"), 
                                                  CASE WHEN ta.member_reg_counrycode=398 THEN
                                                   PKG_KATOHOLDING.GetKatoName(ta.member_reg_sity_code,1)
                                                    ELSE 
                                                   cast(ta.member_reg_sity as nvarchar2(300))
                                                   END),
                                                  XMLElement("Street", XMLATTRIBUTES(ta.member_reg_street_code as "Code"), ta.member_reg_street),
                                                  XMLElement("HomeNumber", XMLATTRIBUTES(ta.member_reg_house_code as "Code"), ta.member_reg_house),
                                                  XMLElement("OfficeNumber", XMLATTRIBUTES(ta.member_reg_office_code as "Code"), ta.member_reg_office),
                                                  XMLElement("PostalCode", ta.member_reg_postcode)),

                                       XMLElement("ACAddress",
                                                  XMLElement("Country", OKSMT_2_xml(ta.member_seat_counrycode,'Country')),
                                                  XMLElement("Area", XMLATTRIBUTES(ta.member_seat_area_code as "Code"),
                                                  CASE WHEN ta.member_seat_counrycode=398 THEN
                                                   PKG_KATOHOLDING.GetKatoName(ta.member_seat_area_code,1)
                                                   ELSE 
                                                    cast(ta.member_seat_area as nvarchar2(300))
                                                   END),
                                                  XMLElement("District", XMLATTRIBUTES(ta.member_seat_region_code as "Code"), 
                                                   CASE WHEN ta.member_seat_counrycode=398 THEN
                                                   PKG_KATOHOLDING.GetKatoName(ta.member_seat_region_code,1)
                                                   ELSE 
                                                    cast(ta.member_seat_region as nvarchar2(300))
                                                   END),
                                                  XMLElement("Town", XMLATTRIBUTES(ta.member_seat_sity_code as "Code"), 
                                                  CASE WHEN ta.member_seat_counrycode=398 THEN
                                                  PKG_KATOHOLDING.GetKatoName(ta.member_seat_sity_code,1)
                                                  ELSE 
                                                    cast(ta.member_seat_sity as nvarchar2(300))
                                                   END),
                                                  XMLElement("Street", XMLATTRIBUTES(ta.member_seat_street_code as "Code"), ta.member_seat_street),
                                                  XMLElement("HomeNumber", XMLATTRIBUTES(ta.member_seat_house_code as "Code"), ta.member_seat_house),
                                                  XMLElement("OfficeNumber", XMLATTRIBUTES(ta.member_seat_office_code as "Code"), ta.member_seat_office),
                                                  XMLElement("PostalCode", ta.member_seat_postcode)),

                                    XMLElement("FullName", XMLATTRIBUTES(pkg_common.GetBool(ta.member_isnotnamed) as "IsFullNameSetup"), ta.member_ur_name),
                                    XMLElement("FirstHead", XMLElement("FirstName", ta.member_ur_fisthead_firstname), XMLElement("SecondName", ta.member_ur_fisthead_secondname), XMLElement("MiddleName", ta.member_ur_fisthead_middlename)),
                                    XMLElement("ParticipantOPF", ta.member_ur_opf_code)))
             WHEN 2 THEN
              XMLElement("AdditionalPersonInfo",
                         XMLElement("PersonType", 2),
                         XMLElement("AdditionalInformationAc",

                                       XMLElement("URAddress",
                                                  XMLElement("Country", OKSMT_2_xml(member_reg_counrycode,'Country')),

                                                  XMLElement("Area", XMLATTRIBUTES(ta.member_reg_area_code as "Code"),
                                                               CASE WHEN ta.member_reg_counrycode=398 THEN
                                                                 PKG_KATOHOLDING.GetKatoName(ta.member_reg_area_code,1)
                                                                 ELSE 
                                                                 cast(ta.member_reg_area as nvarchar2(300))
                                                                END),                                                
                                                  XMLElement("District", XMLATTRIBUTES(ta.member_reg_region_code as "Code"), 
                                                  CASE WHEN ta.member_reg_counrycode=398 THEN 
                                                   PKG_KATOHOLDING.GetKatoName(ta.member_reg_region_code,1)
                                                   ELSE 
                                                   cast(ta.member_reg_region as nvarchar2(300))
                                                   END),
                                                  XMLElement("Town", XMLATTRIBUTES(ta.member_reg_sity_code as "Code"), 
                                                  CASE WHEN ta.member_reg_counrycode=398 THEN
                                                   PKG_KATOHOLDING.GetKatoName(ta.member_reg_sity_code,1)
                                                    ELSE 
                                                   cast(ta.member_reg_sity as nvarchar2(300))
                                                   END),
                                                  
                                                  XMLElement("Street", XMLATTRIBUTES(ta.member_reg_street_code as "Code"), ta.member_reg_street),
                                                  XMLElement("HomeNumber", XMLATTRIBUTES(ta.member_reg_house_code as "Code"), ta.member_reg_house),
                                                  XMLElement("OfficeNumber", XMLATTRIBUTES(ta.member_reg_office_code as "Code"), ta.member_reg_office),
                                                  XMLElement("PostalCode", ta.member_reg_postcode)),

                                       XMLElement("ACAddress",
                                                  XMLElement("Country", OKSMT_2_xml(ta.member_seat_counrycode,'Country')),

                                                   XMLElement("Area", XMLATTRIBUTES(ta.member_seat_area_code as "Code"),
                                                  CASE WHEN ta.member_seat_counrycode=398 THEN
                                                   PKG_KATOHOLDING.GetKatoName(ta.member_seat_area_code,1)
                                                   ELSE 
                                                    cast(ta.member_seat_area as nvarchar2(300))
                                                   END),
                                                  XMLElement("District", XMLATTRIBUTES(ta.member_seat_region_code as "Code"), 
                                                   CASE WHEN ta.member_seat_counrycode=398 THEN
                                                   PKG_KATOHOLDING.GetKatoName(ta.member_seat_region_code,1)
                                                   ELSE 
                                                    cast(ta.member_seat_region as nvarchar2(300))
                                                   END),
                                                  XMLElement("Town", XMLATTRIBUTES(ta.member_seat_sity_code as "Code"), 
                                                  CASE WHEN ta.member_seat_counrycode=398 THEN
                                                  PKG_KATOHOLDING.GetKatoName(ta.member_seat_sity_code,1)
                                                  ELSE 
                                                    cast(ta.member_seat_sity as nvarchar2(300))
                                                   END),
                                                  
                                                  XMLElement("Street", XMLATTRIBUTES(ta.member_seat_street_code as "Code"), ta.member_seat_street),
                                                  XMLElement("HomeNumber", XMLATTRIBUTES(ta.member_seat_house_code as "Code"), ta.member_seat_house),
                                                  XMLElement("OfficeNumber", XMLATTRIBUTES(ta.member_seat_office_code as "Code"), ta.member_seat_office),
                                                  XMLElement("PostalCode", ta.member_seat_postcode)),

                                    XMLElement("FIO",
                                               XMLATTRIBUTES(pkg_common.GetBool(ta.member_isnotnamed) as "IsFioNotSetup"),
                                               XMLElement("FirstName", ta.member_ac_firstname),
                                               XMLElement("SecondName", ta.member_ac_secondname),
                                               XMLElement("MiddleName", ta.member_ac_middlename)),
                                    XMLElement("PlaceBirdth", ta.member_ac_birth_place),
                                    XMLElement("DateBirdth", to_char(ta.member_ac_birth_date, 'dd.mm.yyyy')),
                                    XMLElement("DocumentIdentity", ta.member_ac_doc_typecode),
                                    XMLElement("SeriesDocIdentity", ta.member_ac_doc_series),
                                    XMLElement("NumberDocIdentity", ta.member_ac_doc_number),
                                    XMLElement("DocumentIssued", ta.member_ac_doc_whom),
                                    XMLElement("DateIssuance", to_char(ta.member_ac_doc_date, 'dd.mm.yyyy'))))
             WHEN 3 THEN
              XMLElement("AdditionalPersonInfo",
                         XMLElement("PersonType", 3),
                         XMLElement("AdditionalInformationIp",

                                       XMLElement("URAddress",
                                                  XMLElement("Country", OKSMT_2_xml(member_reg_counrycode,'Country')),
                                                 XMLElement("Area", XMLATTRIBUTES(ta.member_reg_area_code as "Code"),
                                                               CASE WHEN ta.member_reg_counrycode=398 THEN
                                                                 PKG_KATOHOLDING.GetKatoName(ta.member_reg_area_code,1)
                                                                 ELSE 
                                                                 cast(ta.member_reg_area as nvarchar2(300))
                                                                END),                                                
                                                  XMLElement("District", XMLATTRIBUTES(ta.member_reg_region_code as "Code"), 
                                                  CASE WHEN ta.member_reg_counrycode=398 THEN 
                                                   PKG_KATOHOLDING.GetKatoName(ta.member_reg_region_code,1)
                                                   ELSE 
                                                   cast(ta.member_reg_region as nvarchar2(300))
                                                   END),
                                                  XMLElement("Town", XMLATTRIBUTES(ta.member_reg_sity_code as "Code"), 
                                                  CASE WHEN ta.member_reg_counrycode=398 THEN
                                                   PKG_KATOHOLDING.GetKatoName(ta.member_reg_sity_code,1)
                                                    ELSE 
                                                   cast(ta.member_reg_sity as nvarchar2(300))
                                                   END),
                                                 
                                                  XMLElement("Street", XMLATTRIBUTES(ta.member_reg_street_code as "Code"), ta.member_reg_street),
                                                  XMLElement("HomeNumber", XMLATTRIBUTES(ta.member_reg_house_code as "Code"), ta.member_reg_house),
                                                  XMLElement("OfficeNumber", XMLATTRIBUTES(ta.member_reg_office_code as "Code"), ta.member_reg_office),
                                                  XMLElement("PostalCode", ta.member_reg_postcode)),

                                       XMLElement("ACAddress",
                                                  XMLElement("Country", OKSMT_2_xml(ta.member_seat_counrycode,'Country')),

                                                   XMLElement("Area", XMLATTRIBUTES(ta.member_seat_area_code as "Code"),
                                                  CASE WHEN ta.member_seat_counrycode=398 THEN
                                                   PKG_KATOHOLDING.GetKatoName(ta.member_seat_area_code,1)
                                                   ELSE 
                                                    cast(ta.member_seat_area as nvarchar2(300))
                                                   END),
                                                  XMLElement("District", XMLATTRIBUTES(ta.member_seat_region_code as "Code"), 
                                                   CASE WHEN ta.member_seat_counrycode=398 THEN
                                                   PKG_KATOHOLDING.GetKatoName(ta.member_seat_region_code,1)
                                                   ELSE 
                                                    cast(ta.member_seat_region as nvarchar2(300))
                                                   END),
                                                  XMLElement("Town", XMLATTRIBUTES(ta.member_seat_sity_code as "Code"), 
                                                  CASE WHEN ta.member_seat_counrycode=398 THEN
                                                  PKG_KATOHOLDING.GetKatoName(ta.member_seat_sity_code,1)
                                                  ELSE 
                                                    cast(ta.member_seat_sity as nvarchar2(300))
                                                   END),
                                                  
                                                  XMLElement("Street", XMLATTRIBUTES(ta.member_seat_street_code as "Code"), ta.member_seat_street),
                                                  XMLElement("HomeNumber", XMLATTRIBUTES(ta.member_seat_house_code as "Code"), ta.member_seat_house),
                                                  XMLElement("OfficeNumber", XMLATTRIBUTES(ta.member_seat_office_code as "Code"), ta.member_seat_office),
                                                  XMLElement("PostalCode", ta.member_seat_postcode)),

                                    XMLElement("FIO",
                                               XMLATTRIBUTES(pkg_common.GetBool(ta.member_isnotnamed) as "IsFioNotSetup"),
                                               XMLElement("FirstName", ta.member_ac_firstname),
                                               XMLElement("SecondName", ta.member_ac_secondname),
                                               XMLElement("MiddleName", ta.member_ac_middlename)),
                                    XMLElement("ParticipantOPF", ta.member_ur_opf_code),
                                    XMLElement("PlaceBirdth", ta.member_ac_birth_place),
                                    XMLElement("DateBirdth", to_char(ta.member_ac_birth_date, 'dd.mm.yyyy')),
                                    XMLElement("DocumentIdentity", ta.member_ac_doc_typecode),
                                    XMLElement("SeriesDocIdentity", ta.member_ac_doc_series),
                                    XMLElement("NumberDocIdentity", ta.member_ac_doc_number),
                                    XMLElement("DocumentIssued", ta.member_ac_doc_whom),
                                    XMLElement("DateIssuance", to_char(ta.member_ac_doc_date, 'dd.mm.yyyy'))))
           END AS xmltype
    INTO v_result
    FROM mess_cfm_announce_memb ta
    WHERE ta.MESS_CFM_ID = p_MESS_CFM_ID
    and ta.MEMBER_ID = p_MEMBER_ID;

    return v_result;
  end;
  --
END PKG_MESS_CFM_2_1;
/

CREATE OR REPLACE PUBLIC SYNONYM PKG_MESS_CFM_2_1 FOR KFM.PKG_MESS_CFM_2_1
/


GRANT EXECUTE ON KFM.PKG_MESS_CFM_2_1 TO KFM_ROLE
/


CREATE OR REPLACE PACKAGE BODY KFM."PKG_MESSAGES_XML" as

  function Create_XMLFromClob(pMessClob in CLOB,
                              pXML      out xmltype) return number is
    Result number;
  begin
    Result := 0;

    pXML := XMLTYPE.createxml(pMessClob);

    Return(Result);

  EXCEPTION
    WHEN OTHERS THEN
      Result := -300; --Ошибка разбора XML
      Return(Result);
  end;

  function Get_MessagePrefix(pXML       in xmltype,
                             pPrefixXml out varchar2) return number is
    Result   number;
    vRootXml varchar2(50 char);
  begin
    Result     := 0;
    pPrefixXml := NULL;

    vRootXml := pXML.getrootelement();

    select mrp.prefixxpath
    into pPrefixXml
    from messages_root_prefixes mrp
    where mrp.rootname = vRootXml
    and pXML.existsNode(mrp.maindocpath) <> 0;

    Return(Result);

  EXCEPTION
    WHEN OTHERS THEN
      Result := -301; --Неверный префикс в XML
      Return(Result);
  end;

  function Get_MessageVersion(pXML       in xmltype,
                              pVersion   out varchar2,
                              pPrefixXml out varchar2) return number is
    Result number;
  begin
    Result     := 0;
    pVersion   := NULL;
    pPrefixXml := NULL;

    Result := Get_MessagePrefix(pXML, pPrefixXml);
    if Result <> 0 then
      return Result; ---301
    end if;

    select extractvalue(pXML, pPrefixXml || 'Version')
    into pVersion
    from dual;

    Return(Result);

  EXCEPTION
    WHEN OTHERS THEN
      Result := -302; --Не найдено поле Version
      Return(Result);
  end;

  function Get_FieldFromXML(pXML        in xmltype,
                            pFieldName  in varchar2,
                            pFieldValue out varchar2) return number is
    Result number;

    vPrefixXml   varchar2(500);
    vVersionCode varchar2(100 char);

    vMessType  integer;
    vVersionID integer;

    vXPath varchar2(4000 char);
  begin
    Result      := 0;
    pFieldValue := NULL;

    Result := Get_MessageVersion(pXML, vVersionCode, vPrefixXml);
    if Result <> 0 then
      return Result; ---302
    end if;
    begin
      select mv.mess_type,
             mv.versionid
      into vMessType,
           vVersionID
      from messages_versions mv
      where replace(upper(mv.versioncode), '-') = replace(upper(vVersionCode), '-');
    exception
      when others then
        return - 306; --Версия неверная
    end;
    begin
      select mfd.field_xpath
      into vXPath
      from messages_field_dic mfd
      where mfd.mess_type = vMessType
      and mfd.version_id = vVersionID
      and mfd.field_name = pFieldName;
    exception
      when no_data_found then
        Result := -307; --Неверные настройки пути к узлу
        Return(Result);
    end;
    select extractvalue(pXML, vPrefixXml || vXPath)
    into pFieldValue
    from dual;

    Return(Result);
  EXCEPTION
    WHEN OTHERS THEN
      Result := -303; --В сообщении не найдено поле
      Return(Result);
  end;

  function GetParsedXML(xmldoc in clob) return xmldom.DOMnode is
    parser    xmlparser.parser;
    retval    xmldom.DOMDocument;
    elem      xmldom.DOMElement;
    vrootname varchar2(100 char);
  begin
    parser := xmlparser.newParser;
    xmlparser.setValidationMode(parser, false);
    xmlparser.setPreserveWhiteSpace(parser, true);
    xmlparser.parseClob(parser, xmldoc);
    retval := xmlparser.getDocument(parser);
    elem   := xmldom.getDocumentElement(retval);

    vrootname := xmldom.getTagName(elem);

    return xslprocessor.selectSingleNode(xmldom.makenode(retval), '/' || vrootname);

  exception
    when others then
      return null; --Ошибка разбора XML
  end;

  function GetSingleDateVal(xmlnode in xmldom.DOMNode,
                            pattern in varchar2) return date is
  begin
    return to_date(xslprocessor.valueOf(xmlnode, pattern || '/text()'), 'yyyy-mm-dd');
  exception
    when others then
      return null; --Ошибка разбора XML
  end;

  function GetSingleDateTimeVal(xmlnode in xmldom.DOMNode,
                                pattern in varchar2) return date is
  begin
    return to_date(xslprocessor.valueOf(xmlnode, pattern || '/text()'), 'yyyy-mm-ddTHH24:mi:ss');
  exception
    when others then
      return null; --Ошибка разбора XML
  end;

  function GetSingleVal(xmlnode in xmldom.DOMNode,
                        pattern in varchar2) return varchar2 is
  begin
    return xslprocessor.valueOf(xmlnode, pattern);
  exception
    when others then
      return null; --Ошибка разбора XML
  end;

  function GetNodesList(xmlnode in xmldom.DOMNode,
                        pattern in varchar2) return xmldom.DOMNodeList is
  begin
    return xslprocessor.selectNodes(xmlnode, pattern);
  exception
    when others then
      return null; --Ошибка разбора XML
  end;

  function Get_RootNodeFromXML(pXML      in clob,
                               pRootNode out clob) return number is
    Result number;

    vPrefixXml varchar2(500);
    vXML       xmltype;
    vRootXml   varchar2(50 char);
  begin
    Result := 0;
    begin
      vXML := xmltype.createxml(pXML);
    exception
      when others then
        return - 305; -- (not valid)
    end;
    --Result := Get_MessageVersion(vXML, vVersionCode, vPrefixXml);

    vRootXml := vXML.getrootelement();

    begin
      select mrp.maindocpath
      into vPrefixXml
      from messages_root_prefixes mrp
      where mrp.rootname = vRootXml
      and vXML.existsNode(mrp.maindocpath) <> 0;
    exception
      when others then
        return - 307; -- Неверные настройки пути к узлу документа. Структура документа отлична от предусмотренной.
    end;

    select extract(vXML, vPrefixXml).getClobVal()
    into pRootNode
    from dual;

    Return(Result);

  EXCEPTION
    WHEN OTHERS THEN
      Result := -310; --Неверные настройки пути к узлу документа. Структура документа отлична от предусмотренной.
      Return(Result);
  end;

  function GetSingleOKEDVal(v_okedcode in varchar2,pmessid in number) return number is
    v_result number default 0;
  begin
   SELECT AC.ANALYTICCODE
      INTO v_result
      FROM ANALYTICCODES ac
     WHERE AC.ANALYTICTYPE = 41021 -- OKED
     	AND AC.OUTER_CODE = v_OKEDCODE;
   return v_result;
  exception
    when others then
           return null; --Ioeaea ?acai?a XML
  end;

  function GetSingleOKEDVal5Code(v_okedcode in varchar2) return varchar2 is
    v_result varchar2(5);
  begin
  	SELECT AC.OUTER_CODE
      INTO v_result
      FROM ANALYTICCODES ac
     WHERE AC.ANALYTICTYPE = 41021 -- OKED
     	AND AC.ANALYTICCODE = v_OKEDCODE;
   return v_result;
  exception
    when others then
           return null; --Ioeaea ?acai?a XML
  end;

  function GetSingleOKSMTVal(v_oksmtcode in varchar2) return number is
    v_result number default 0;
  begin
	SELECT AC.ANALYTICCODE
      INTO v_result
      FROM ANALYTICCODES ac
     WHERE AC.ANALYTICTYPE = 41019 -- OKED
     	AND AC.OUTER_CODE = v_oksmtcode;

   return v_result;
  exception
    when others then
         return null; --Ioeaea ?acai?a XML
  end;

 function GetSingleOKSMTAbbr(v_OKSMTCODE in varchar2) return varchar2 is
   v_result varchar2(2);
  begin
	SELECT AC.OUTER_CODE
      INTO v_result
      FROM ANALYTICCODES ac
     WHERE AC.ANALYTICTYPE = 41019 -- OKED
     	AND AC.ANALYTICCODE = v_OKSMTCODE;

   return v_result;
  exception
    when others then
         return null; --Ioeaea ?acai?a XML
  end;

end PKG_MESSAGES_XML;
/

CREATE OR REPLACE PUBLIC SYNONYM PKG_MESSAGES_XML FOR KFM.PKG_MESSAGES_XML
/


GRANT EXECUTE ON KFM.PKG_MESSAGES_XML TO KFM_ROLE
/


CREATE OR REPLACE PACKAGE BODY KFM."PKG_REPORTS" as

  --GetOperationsInfoForPeriod
  function GetOperationsInfoForPeriod(pBeginDate     in date,
                                      pEndDate       in date,
                                      pOperationsCur out sys_refcursor)
    return number is
    result number default 0;
  begin
    open pOperationsCur for
      select mcai.MESS_OPER_STATUS,
             mcao.MESS_CFM_ID,
             mcao.OPER_NUMBER,
             mcao.OPER_IDTYPE,
             mcao.OPER_IDVIEW,
             mcao.OPER_MEMBERS_COUNT,
             mcao.OPER_CURRENCY_CODE,
             mcao.OPER_CURRENCY_AMOUNT,
             mcao.OPER_TENGE_AMOUNT,
             mcao.OPER_REASON,
             mcao.OPER_DOC_NUMBER,
             mcao.OPER_DOC_DATE,
             mcao.OPER_SUSP_FIRST,
             mcao.OPER_SUSP_SECOND,
             mcao.OPER_SUSP_THIRD,
             mcao.OPER_DIFFICULTIES,
             mcao.OPER_DOPINFO
        from mess_cfm_announce_oper mcao
        left join mess_cfm_announce_info mcai
          on mcao.mess_cfm_id = mcai.mess_cfm_id
       where mcao.MESS_CFM_ID in
             (select mc.MESS_CFM_ID
                from mess_cfm mc
               where mc.receive_date between pBeginDate and pEndDate);
    return result;
  exception
    when others then
      return - 1;
  end GetOperationsInfoForPeriod;

  --Отчет по отправленным сообщениям, по которым не пришло ответа за предыдущие сутки
  function GetNoFirstList(pMessagesCur out sys_refcursor) return number is
    result number default 0;
  begin
    open pMessagesCur for
      select mc.mess_cfm_id,
             mc.receive_date,
             mc.send_date,
             mcai.mess_number,
             mcai.mess_type,
             mcai.mess_oper_status,
             mcai.mess_suspic_code,
             mcai.mess_reason_code,
             mcao.OPER_NUMBER,
             mcao.OPER_IDTYPE,
             mcao.OPER_IDVIEW,
             mcao.OPER_MEMBERS_COUNT,
             mcao.OPER_CURRENCY_CODE,
             mcao.OPER_CURRENCY_AMOUNT,
             mcao.OPER_TENGE_AMOUNT,
             mcao.OPER_REASON,
             mcao.OPER_DOC_NUMBER,
             mcao.OPER_DOC_DATE,
             mcao.OPER_SUSP_FIRST,
             mcao.OPER_SUSP_SECOND,
             mcao.OPER_SUSP_THIRD,
             mcao.OPER_DIFFICULTIES,
             mcao.OPER_DOPINFO,
             mv.versionid
        from mess_cfm mc
        left join mess_cfm_announce_oper mcao
          on mc.mess_cfm_id = mcao.mess_cfm_id
        left join mess_cfm_announce_info mcai
          on mcai.mess_cfm_id = mcao.mess_cfm_id
        left join messages_versions mv
          on mv.versioncode = mc.mess_version
       where mc.mess_type = 1
         and (mc.stage = 30 or mc.stage = 40)
         and mc.send_date between trunc(sysdate) - 1 and trunc(sysdate)
         and
            -- не имеется никакой квитанции
             not exists
       (select mcChecks.mess_cfm_id
                from mess_cfm mcChecks
               where mcChecks.Mess_Parentid = mc.Mess_Parentid
                 and (mcChecks.Mess_Type = 3 or mcChecks.Mess_Type = 2));
    return result;
  exception
    when others then
      return - 1;
  end GetNoFirstList;

  --Отчет по отправленным  сообщениям за предыдущие сутки, не прошедшим структурно-логический контроль
  function GetFirstList(pMessagesCur out sys_refcursor) return number is
    result number default 0;
  begin
    open pMessagesCur for
      select mc.mess_cfm_id,
             mc.receive_date,
             mc.send_date,
             mcai.mess_number,
             mcai.mess_type,
             mcai.mess_oper_status,
             mcai.mess_suspic_code,
             mcai.mess_reason_code,
             mcao.OPER_NUMBER,
             mcao.OPER_IDTYPE,
             mcao.OPER_IDVIEW,
             mcao.OPER_MEMBERS_COUNT,
             mcao.OPER_CURRENCY_CODE,
             mcao.OPER_CURRENCY_AMOUNT,
             mcao.OPER_TENGE_AMOUNT,
             mcao.OPER_REASON,
             mcao.OPER_DOC_NUMBER,
             mcao.OPER_DOC_DATE,
             mcao.OPER_SUSP_FIRST,
             mcao.OPER_SUSP_SECOND,
             mcao.OPER_SUSP_THIRD,
             mcao.OPER_DIFFICULTIES,
             mcao.OPER_DOPINFO,
             mv.versionid
        from mess_cfm mc
        left join mess_cfm_announce_oper mcao
          on mc.mess_cfm_id = mcao.mess_cfm_id
        left join mess_cfm_announce_info mcai
          on mcai.mess_cfm_id = mcao.mess_cfm_id
        left join messages_versions mv
          on mv.versioncode = mc.mess_version
       where mc.mess_type = 1
         and (mc.stage = 30 or mc.stage = 40)
         and mc.send_date between trunc(sysdate) - 1 and trunc(sysdate)
         and
            -- имеется квитанция об отказе
             exists (select mcChecks.mess_cfm_id
                from mess_cfm mcChecks
               where mcChecks.Mess_Parentid = mc.mess_cfm_id
                 and mcChecks.Mess_Type = 3);

    return result;
  end GetFirstList;

  --Отчет по ранее отвергнутым и неисправленным в установленный срок (1 день) сообщениям за предыдущие сутки
  function GetSecondList(pMessagesCur out sys_refcursor) return number is
    result number default 0;
  begin
    open pMessagesCur for
      select mc.mess_cfm_id,
             mc.receive_date,
             mc.send_date,
             mcai.mess_number,
             mcai.mess_type,
             mcai.mess_oper_status,
             mcai.mess_suspic_code,
             mcai.mess_reason_code,
             mcao.OPER_NUMBER,
             mcao.OPER_IDTYPE,
             mcao.OPER_IDVIEW,
             mcao.OPER_MEMBERS_COUNT,
             mcao.OPER_CURRENCY_CODE,
             mcao.OPER_CURRENCY_AMOUNT,
             mcao.OPER_TENGE_AMOUNT,
             mcao.OPER_REASON,
             mcao.OPER_DOC_NUMBER,
             mcao.OPER_DOC_DATE,
             mcao.OPER_SUSP_FIRST,
             mcao.OPER_SUSP_SECOND,
             mcao.OPER_SUSP_THIRD,
             mcao.OPER_DIFFICULTIES,
             mcao.OPER_DOPINFO,
             mv.versionid
        from mess_cfm mc
        left join mess_cfm_announce_oper mcao
          on mc.mess_cfm_id = mcao.mess_cfm_id
        left join mess_cfm_announce_info mcai
          on mcai.mess_cfm_id = mcao.mess_cfm_id
        left join messages_versions mv
          on mv.versioncode = mc.mess_version
       where mc.mess_type = 1
         and (mc.stage = 30 or mc.stage = 40)
         and mc.send_date between trunc(sysdate) - 1 and trunc(sysdate)
         and
            -- имеется квитанция об отказе
             exists
       (select mcChecks.mess_cfm_id
                from mess_cfm mcChecks
               where mcChecks.Mess_Parentid = mc.mess_cfm_id
                 and mcChecks.mess_type = 3
                 and not exists
               (select mcRetry.mess_cfm_id
                        from mess_cfm mcRetry
                       inner join mess_cfm_announce_info mcaiRetry
                          on mcaiRetry.mess_cfm_id = mcRetry.Mess_Cfm_Id
                       where mcRetry.Mess_Parentid = mc.mess_cfm_id
                         and mcRetry.mess_type = 1
                         and mcaiRetry.mess_type = 2
                         and --исправление
                             mcRetry.Receive_Date < mcChecks.receive_date + 1));
    --               exists (select mcChecks2.mess_cfm_id
    --                        from mess_cfm mcChecks2
    --                        where mcChecks2.Parentmessid=mc.mess_cfm_id and
    --                        mcChecks2.messtype=2))

    return result;
  end GetSecondList;

  -- Отправлено за текущие сутки
  function GetThirdList(pMessagesCur out sys_refcursor) return number is
    result number default 0;
  begin
    open pMessagesCur for
      select mc.mess_cfm_id,
             mc.receive_date,
             mc.send_date,
             mcai.mess_number,
             mcai.mess_type,
             mcai.mess_oper_status,
             mcai.mess_suspic_code,
             mcai.mess_reason_code,
             mcao.OPER_NUMBER,
             mcao.OPER_IDTYPE,
             mcao.OPER_IDVIEW,
             mcao.OPER_MEMBERS_COUNT,
             mcao.OPER_CURRENCY_CODE,
             mcao.OPER_CURRENCY_AMOUNT,
             mcao.OPER_TENGE_AMOUNT,
             mcao.OPER_REASON,
             mcao.OPER_DOC_NUMBER,
             mcao.OPER_DOC_DATE,
             mcao.OPER_SUSP_FIRST,
             mcao.OPER_SUSP_SECOND,
             mcao.OPER_SUSP_THIRD,
             mcao.OPER_DIFFICULTIES,
             mcao.OPER_DOPINFO,
             mv.versionid
        from mess_cfm mc
        left join mess_cfm_announce_oper mcao
          on mc.mess_cfm_id = mcao.mess_cfm_id
        left join mess_cfm_announce_info mcai
          on mcai.mess_cfm_id = mcao.mess_cfm_id
        left join messages_versions mv
          on mv.versioncode = mc.mess_version
       where mc.mess_type = 1
         and (mc.stage = 30 or mc.stage = 40)
         and mc.receive_date between trunc(sysdate) and sysdate;
    return result;
  end GetThirdList;

  --GetFirstOrSecondListHTML2
  function GetFirstOrSecondListHTML2(pReportName   in varchar2,
                                     pMessagesCur  in sys_refcursor,
                                     pMessagesHTML out clob) return number is
    TAG_STYLED_TR    constant varchar2(31) := '<tr style="background:#cccccc">';
    TAG_END_TR       constant varchar2(5 char) := '</tr>';
    TAG_END_TD       constant varchar2(5 char) := '</td>';
    TAG_TD           constant varchar2(4) := '<td>';
    TAG_END_B_END_TD constant varchar2(9) := '</b></td>';
    TAG_TD_B         constant varchar2(7) := '<td><b>';
    TAG_TR           constant varchar2(4) := '<tr>';
    TAG_TABLE_END    constant varchar2(8) := '</table>';
    TAG_TABLE_BEGIN  constant varchar2(110) := '<table cellpadding="4" border="1" bordercolor=#000000 style="border-collapse:collapse" width="100%">';
    result number default 0;

    vMESSID       mess_cfm.mess_cfm_id%TYPE;
    vRECEIVE_DATE mess_cfm.receive_date%TYPE;
    vSEND_DATE    mess_cfm.send_date%TYPE;

    vMESSNUMBER      mess_cfm_announce_info.mess_number%TYPE;
    vMESSDOCTYPE     mess_cfm_announce_info.mess_type%TYPE;
    vOPERATIONSTATUS mess_cfm_announce_info.mess_oper_status%TYPE;
    vREASONCRITERY   mess_cfm_announce_info.mess_suspic_code%TYPE;
    vREASON          mess_cfm_announce_info.mess_reason_code%TYPE;

    vOPERATIONNUMBER     mess_cfm_announce_oper.oper_number%TYPE;
    vIDTYPEOPERATION     mess_cfm_announce_oper.oper_idtype%TYPE;
    vIDVIEWOPERATION     mess_cfm_announce_oper.oper_idview%TYPE;
    vMEMBERSCOUNT        mess_cfm_announce_oper.oper_members_count%TYPE;
    vCURRENCYCODE        mess_cfm_announce_oper.oper_currency_code%TYPE;
    vAMOUNTCURRENCY      mess_cfm_announce_oper.oper_currency_amount%TYPE;
    vAMOUNTCURRENCYTENGE mess_cfm_announce_oper.oper_tenge_amount%TYPE;

    vDOCOPERATIONNAME   mess_cfm_announce_oper.oper_reason%TYPE;
    vDOCOPERATIONNUMBER mess_cfm_announce_oper.oper_doc_number%TYPE;
    vDOCOPERATIONDATE   mess_cfm_announce_oper.oper_doc_date%TYPE;
    vFISRTCODE          mess_cfm_announce_oper.oper_susp_first%TYPE;
    vSECONDCODE         mess_cfm_announce_oper.oper_susp_second%TYPE;
    vTHIRDCODE          mess_cfm_announce_oper.oper_susp_third%TYPE;
    vTRUBLEDESCRIPTION  mess_cfm_announce_oper.oper_difficulties%TYPE;
    vDOPINFO            mess_cfm_announce_oper.oper_dopinfo%TYPE;
    vVersion            number;

    i                number default 1;
    vStrHeader       VARCHAR2(10000) := '<div align="center"><h3>' ||
                                        pReportName ||
                                        '</h3></div></br></br>';
    vHtmlHeader      varchar2(1000 char) := '<html xmlns="http://www.w3.org/1999/xhtml">' ||
                                       '<head><style type="text/css"> body { background-color: #f0ffff; font-family:Verdana; font-size:10px;  }' ||
                                       '#main_container table tr { border-width: 1px; border-style: solid; font-size:10px; }' ||
                                       '</style> </head> <body> <div id="main_container">';
    vHtmlEnd         varchar2(100 char) := '</div> </body> </html>';
    vTableBeginFirst varchar2(100 char) := '<h3>Документ ';
    vEmptyReport     varchar2(100 char) := '<font>Данные успешно обработаны, соответствующих записей не найдено</font></br></br>';
    tmpClob          clob;
  begin
    pMessagesHTML := vHtmlHeader;
    dbms_lob.append(pMessagesHTML, vStrHeader);
    if pMessagesCur is not null then
      begin
        loop
          fetch pMessagesCur
            into vMESSID,
                 vRECEIVE_DATE,
                 vSEND_DATE,
                 vMESSNUMBER,
                 vMESSDOCTYPE,
                 vOPERATIONSTATUS,
                 vREASONCRITERY,
                 vREASON,
                 vOPERATIONNUMBER,
                 vIDTYPEOPERATION,
                 vIDVIEWOPERATION,
                 vMEMBERSCOUNT,
                 vCURRENCYCODE,
                 vAMOUNTCURRENCY,
                 vAMOUNTCURRENCYTENGE,
                 vDOCOPERATIONNAME,
                 vDOCOPERATIONNUMBER,
                 vDOCOPERATIONDATE,
                 vFISRTCODE,
                 vSECONDCODE,
                 vTHIRDCODE,
                 vTRUBLEDESCRIPTION,
                 vDOPINFO,
                 vVersion;
          exit when pMessagesCur%NOTFOUND;

          tmpClob := vTableBeginFirst || to_char(i) || '</h3>' ||

                     TAG_TABLE_BEGIN || TAG_STYLED_TR ||
                     '<td colspan="4"><b>' || 'Информация о сообщении' ||
                     TAG_END_B_END_TD || TAG_END_TR || TAG_TR || TAG_TD_B ||
                     'Номер сообщения:' || TAG_END_B_END_TD || TAG_TD ||
                     nvl(to_char(vMESSNUMBER), '-') || TAG_END_TD ||
                     TAG_TD_B || 'Дата отправки/получения:' ||
                     TAG_END_B_END_TD || TAG_TD ||
                     nvl(to_char(vRECEIVE_DATE, 'DD.MM.YYYY'), '-') ||
                     TAG_END_TD || TAG_END_TR || TAG_TR || TAG_TD_B ||
                     'Основание для подачи сообщения:' || TAG_END_B_END_TD ||
                     TAG_TD || nvl(pkg_analytic.GetCodeDescription(vREASON,
                                                                   1011,
                                                                   vversion),
                                   '-') || TAG_END_TD || TAG_TD_B ||

                     'Критерий подозрительности операции:' ||
                     TAG_END_B_END_TD || TAG_TD ||
                     nvl(pkg_analytic.GetCodeDescription(vREASONCRITERY,
                                                         1012,
                                                         vversion),
                         '-') || TAG_END_TD || TAG_END_TR || TAG_TABLE_END ||
                     TAG_TABLE_BEGIN || TAG_STYLED_TR ||
                     '<td colspan="10"><b>' || 'Информация об операции' ||
                     TAG_END_B_END_TD || TAG_END_TR || TAG_TR ||
                     '<td colspan="2" rowspan="2"><b>' ||
                     'Внутренний номер операции СФМ:' || TAG_END_B_END_TD ||
                     '<td rowspan="2">' || nvl(vOPERATIONNUMBER, '-') ||
                     TAG_END_TD || '<td rowspan="2"><b>' ||
                     'Количество участников:' || TAG_END_B_END_TD ||
                     '<td rowspan="2">' || nvl(to_char(vMEMBERSCOUNT), '-') ||
                     TAG_END_TD || '<td rowspan="2"><b>' || 'Сумма:' ||
                     TAG_END_B_END_TD || TAG_TD || vAMOUNTCURRENCY ||
                     TAG_END_TD || TAG_TD ||
                     pkg_analytic.GetCodeDescription(vCURRENCYCODE,
                                                     1018,
                                                     vversion) ||
                     TAG_END_TD || '<td rowspan="2"><b>' || 'Состояние:' ||
                     TAG_END_B_END_TD || '<td rowspan="2">' ||
                     nvl(pkg_analytic.GetCodeDescription(vOPERATIONSTATUS,
                                                         1010,
                                                         vversion),
                         '-') || TAG_END_TD || TAG_END_TR || TAG_TR ||
                     TAG_TD || vAMOUNTCURRENCYTENGE || TAG_END_TD || TAG_TD ||
                     'KZT - Тенге' || TAG_END_TD || TAG_END_TR || TAG_TR ||
                     '<td><b>' || 'Код типа:' || TAG_END_B_END_TD ||
                     '<td colspan="9">' || nvl(pkg_analytic.GetCodeDescription(vIDTYPEOPERATION,
                                                                               1017,
                                                                               vversion),
                                               '-') || TAG_END_TD ||
                     TAG_END_TR || TAG_TR || TAG_TD_B || 'Код вида:' ||
                     TAG_END_B_END_TD || '<td colspan="9">' ||
                     nvl(pkg_analytic.GetCodeDescription(vIDVIEWOPERATION,
                                                         1004,
                                                         vversion),
                         '-') || TAG_END_TD || TAG_END_TR || TAG_TABLE_END ||
                     TAG_TABLE_BEGIN || TAG_STYLED_TR ||
                     '<td colspan="6"><b>' ||
                     'Документ, на основании которого произведена операция' ||
                     TAG_END_B_END_TD || TAG_END_TR || TAG_TR || '<td><b>' ||
                     'Основание совершения операции:' || '</b></td>' ||
                     '<td>' || nvl(vDOCOPERATIONNAME, '-') || '</td>' ||
                     '<td><b>' || 'Номер:' || '</b></td>' || '<td>' ||
                     vDOCOPERATIONNUMBER || '</td>' || '<td><b>' ||
                     'Дата выдачи:' || '</b></td>' || '<td>' ||
                     nvl(to_char(vDOCOPERATIONDATE, 'DD.MM.YYYY'), '-') ||
                     '</td>' || TAG_END_TR || TAG_TABLE_END ||
                     TAG_TABLE_BEGIN || TAG_STYLED_TR ||
                     '<td colspan="6"><b>' ||
                     'Коды признака подозрительности  операции:' ||
                     '</b></td>' || TAG_END_TR || TAG_TR || '<td><b>' ||
                     '1-ый:' || '</b></td>' || '<td>' ||
                     nvl(pkg_analytic.GetShortCodeDescription(vFISRTCODE,
                                                              1008,
                                                              vversion),
                         '-') || '</td>' || '<td>' ||
                     nvl(pkg_analytic.GetCodeDescription(vFISRTCODE,
                                                         1008,
                                                         vversion),
                         '-') || '</td>' || TAG_END_TR || TAG_TR ||
                     '<td><b>' || '2-ой:' || '</b></td>' || '<td>' ||
                     nvl(pkg_analytic.GetShortDescription(vSECONDCODE, 1008),
                         '-') || '</td>' || '<td>' ||
                     nvl(pkg_analytic.GetCodeDescription(vSECONDCODE,
                                                         1008,
                                                         vversion),
                         '-') || '</td>' || TAG_END_TR || TAG_TR ||
                     '<td><b>' || '3-ий:' || '</b></td>' || '<td>' ||
                     nvl(pkg_analytic.GetShortCodeDescription(vTHIRDCODE,
                                                              1008,
                                                              vversion),
                         '-') || '</td>' || '<td>' ||
                     nvl(pkg_analytic.GetCodeDescription(vTHIRDCODE,
                                                         1008,
                                                         vversion),
                         '-') || '</td>' || TAG_END_TR || TAG_TABLE_END ||
                     TAG_TABLE_BEGIN || TAG_STYLED_TR || '<td><b>' ||
                     'Описание возникших затруднений квалификации операции как подозрительной:' ||
                     '</b></td>' || TAG_END_TR || TAG_TR || '<td>' ||
                     nvl(vTRUBLEDESCRIPTION, '-') || '</td>' || TAG_END_TR ||
                     TAG_STYLED_TR || '<td><b>' ||
                     'Дополнительная информация по операции:' ||
                     '</b></td>' || TAG_END_TR || TAG_TR || '<td>' ||
                     nvl(vDOPINFO, '-') || '</td>' || TAG_END_TR ||
                     TAG_TABLE_END ||

                     '<hr />';
          dbms_lob.append(pMessagesHTML, tmpClob);
          i := i + 1;
        end loop;
      end;
    end if;
    if i = 1 then
      dbms_lob.append(pMessagesHTML, vEmptyReport);
    end if;
    dbms_lob.append(pMessagesHTML, vHtmlEnd);
    return result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'PKG_Reports.GetFirstOrSecondListHTML2');
      return - 1;
  end GetFirstOrSecondListHTML2;

  --GetPitaOrgListHTML2
  function GetPitaOrgListHTML2(pReportName  in varchar2,
                               pMessagesCur out clob) return number is
    TAG_TABLE_END   constant varchar2(8) := '</table>';
    TAG_TABLE_BEGIN constant varchar2(110) := '<table cellpadding="4" border="1" bordercolor=#000000 style="border-collapse:collapse" width="100%">';

    result       number default 0;
    vHtmlHeader  varchar2(1000 char) := '<html xmlns="http://www.w3.org/1999/xhtml">' ||
                                   '<head><style type="text/css"> body { background-color: #f0ffff; font-family:Verdana; font-size:10px;  }' ||
                                   '#main_container table tr { border-width: 1px; border-style: solid; font-size:10px; }' ||
                                   '</style> </head> <body> <div id="main_container">';
    vHtmlEnd     varchar2(100 char) := '</div> </body> </html>';
    vStrHeader   VARCHAR2(10000) := '<div align="center"><h3>' ||
                                    pReportName || '</h3></div></br></br>';
    vEmptyReport varchar2(100 char) := '<font>Данные успешно обработаны, соответствующих записей не найдено</font></br></br>';
    vTmpLob      clob;
    i            number default 1;
  begin
    pMessagesCur := vHtmlHeader;
    dbms_lob.append(pMessagesCur, vStrHeader);

    FOR vListInfo IN (SELECT m.mess_number as messnumber,
                             m.mess_date as messdate,
                             m.mess_type as messdoctype,
                             ent.MEMBER_TYPE,
                             decode(ent.MEMBER_RESIDENCE,
                                    1,
                                    'Да',
                                    0,
                                    'Нет',
                                    '-') as isResident,
                             ent.MEMBER_RESIDENCE_COUNTRYCODE,
                             ent.MEMBER_UR_NAME,
                             ent.MEMBER_UR_FISTHEAD_NAME,
                             ent.MEMBER_FOREIGN,
                             ent.MEMBER_FOREIGN_ADD,
                             (select o.oper_number
                                from mess_cfm_announce_oper o
                               where o.mess_cfm_id = ent.mess_cfm_id) as opernumber,
                             ent.MEMBER_ROLE,
                             ent.MEMBER_KIND,
                             ent.MEMBER_TAXCODE,
                             ent.MEMBER_OKPO,
                             ent.MEMBER_OKED,
                             ent.MEMBER_MAINCODE,
                             ent.MEMBER_BANK_NAME,
                             ent.MEMBER_BANK_ACCOUNT,
                             ent.MEMBER_BANK_ADDRESS,
                             ent.MEMBER_BANK_CODE,
                             pkg_analytic.GetDescription(ent.MEMBER_REG_COUNRYCODE,
                                                             41019) || ', ' ||
                             ent.MEMBER_REG_AREA || ', ' ||
                             ent.MEMBER_REG_REGION || ', ' ||
                             ent.MEMBER_REG_SITY || ', ' ||
                             ent.MEMBER_REG_STREET || ', ' ||
                             ent.MEMBER_REG_HOUSE || ', ' ||
                             ent.MEMBER_REG_OFFICE || ', ' ||
                             ent.MEMBER_REG_POSTCODE as regaddress,
                             pkg_analytic.GetDescription(ent.MEMBER_SEAT_COUNRYCODE,
                                                             41019) || ', ' ||
                             ent.MEMBER_SEAT_AREA || ', ' ||
                             ent.MEMBER_SEAT_REGION || ', ' ||
                             ent.MEMBER_SEAT_SITY || ', ' ||
                             ent.MEMBER_SEAT_STREET || ', ' ||
                             ent.MEMBER_SEAT_HOUSE || ', ' ||
                             ent.MEMBER_SEAT_OFFICE || ', ' ||
                             ent.MEMBER_SEAT_POSTCODE as seataddress,
                             ent.MEMBER_COMMENTS,
                             ent.MEMBER_PHONE,
                             ent.MEMBER_EMAIL,
                             pkg_pitaholding.ExistsInPitaOrg(ent.mess_cfm_id) as Pitaorg,
                             mv.VersionId
                        FROM mess_cfm_announce_memb ent
                        join mess_cfm_announce_info m
                          on (m.mess_cfm_id = ent.mess_cfm_id)
                        join mess_cfm mc
                          on (mc.mess_cfm_id = ent.mess_cfm_id)
                        join messages_versions mv
                          on (mv.versioncode = mc.mess_version)
                       WHERE ent.member_type = 1) LOOP
      /*     1 - Юридическое лицо
      2 - Физическое лицо
      3 - Индивидуальный предприниматель  */
      if vListInfo.Pitaorg is not null then

        vTmpLob := '<h3>Документ ' || to_char(i) || '</h3>' ||

                   TAG_TABLE_BEGIN ||

                   '<tr><td><b>' || 'Номер сообщения:' || '</b></td><td>' ||
                   nvl(to_char(vListInfo.messnumber), '-') ||
                   '</td><td><b>' || 'Дата сообщения:' || '</b></td><td>' ||
                   nvl(to_char(vListInfo.messdate, 'DD.MM.YYYY'), '-') ||
                   '</td><td><b>' || 'Вид сообщения:' || '</b></td><td>' ||
                   nvl(pkg_analytic.GetCodeDescription(vListInfo.messdoctype,
                                                       1009,
                                                       vListInfo.Versionid),
                       '-') || '</td></tr>' || TAG_TABLE_END ||

                   TAG_TABLE_BEGIN ||

                   '<tr style="background:#cccccc"><td colspan="5"><b>' ||
                   'Информация об объекте:' || '</b></td></tr>' ||

                   '<tr><td><b>' || 'Наименование:' ||
                   '</b></td><td colspan="4">' ||
                   nvl(vListInfo.MEMBER_UR_NAME, '-') || '</td></tr>' ||

                   '<tr><td><b>' || 'ФИО первого руководителя:' ||
                   '</b></td><td colspan="4">' ||
                   nvl(vListInfo.MEMBER_UR_FISTHEAD_NAME, '-') ||
                   '</td></tr>' ||

                   '<tr><td><b>' || 'Тип:' || '</b></td><td>' ||
                   pkg_analytic.GetCodeDescription(vListInfo.MEMBER_TYPE,
                                                   10,
                                                   vListInfo.Versionid) ||
                   '</td><td><b>' || 'Резидент:' || '</b></td><td>' ||
                   vListInfo.isResident || '</td><td colspan="2">' ||
                   nvl(pkg_analytic.GetDescription(vListInfo.MEMBER_RESIDENCE_COUNTRYCODE,
                                                       41019),
                       '-') || '</td></tr>' ||

                   '<tr><td><b>' || 'Аффилированность:' ||
                   '</b></td><td colspan="4">' ||

                   pkg_analytic.GetCodeDescription(vListInfo.MEMBER_FOREIGN,
                                                   1014,
                                                   vListInfo.Versionid) || ' ' ||
                   vListInfo.Member_Foreign_Add || '</td></tr>' ||
                   TAG_TABLE_END || TAG_TABLE_BEGIN ||

                   '<tr style="background:#cccccc"><td colspan="4"><b>' ||
                   'Информация об операции' || '</b></td></tr>' ||

                   '<tr><td><b>' || 'Номер операции:' || '</b></td><td>' ||
                   nvl(to_char(vListInfo.opernumber), ' - ') ||
                   '</td><td><b>' || 'Роль в операции:' || '</b></td><td>' ||
                   pkg_analytic.GetCodeDescription(vListInfo.Member_Role,
                                                   102,
                                                   vListInfo.Versionid) ||
                   '</td></tr><tr><td><b>' || 'Вид участника:' ||
                   '</b></td><td colspan="3">' ||
                   pkg_analytic.GetCodeDescription(vListInfo.Member_Kind,
                                                   1006,
                                                   vListInfo.Versionid) ||
                   '</td></tr>' ||

                   TAG_TABLE_END || TAG_TABLE_BEGIN ||

                   '<tr style="background:#cccccc"><td colspan="8"><b>' ||
                   'Номера' || '</b></td></tr>' ||

                   '<tr><td><b>' || 'РНН' || '</b></td><td>' ||
                   nvl(to_char(vListInfo.Member_Taxcode), '-') ||
                   '</td><td><b>' || 'ОКПО' || '</b></td><td>' ||
                   nvl(to_char(vListInfo.Member_Okpo), '-') ||
                   '</td><td><b>' || 'ОКЕД' || '</b></td><td>' ||
                   nvl(to_char(vListInfo.Member_Oked), '-') ||
                   '</td><td><b>' || 'ИНН/БИН' || '</b></td><td>' ||
                   nvl(to_char(vListInfo.Member_Maincode), '-') ||
                   '</td></tr>' ||

                   TAG_TABLE_END || TAG_TABLE_BEGIN ||

                   '</td></tr><tr style="background:#cccccc"><td colspan="4"><b>' ||
                   'Информация о банке-корреспонденте' || '</b></td></tr>' ||

                   '<tr><td><b>' || 'Наименование:' || '</b></td><td>' ||
                   nvl(vListInfo.Member_Bank_Name, '-') || '</td><td><b>' ||
                   'Код:' || '</b></td><td>' ||
                   nvl(to_char(vListInfo.Member_Bank_Code), ' - ') ||
                   '</td></tr>' ||

                   '<tr><td><b>' || 'Адрес:' || '</b></td><td>' ||
                   nvl(vListInfo.Member_Bank_Address, ' - ') ||
                   '</td><td><b>' || 'Номер счета:' || '</b></td><td>' ||
                   nvl(vListInfo.Member_Bank_Account, '-') || '</td></tr>' ||

                   TAG_TABLE_END || TAG_TABLE_BEGIN ||

                   '<tr style="background:#cccccc"><td colspan="4"><b>' ||
                   'Контактная информация' || '</b></td></tr>' ||

                   '<tr><td><b>' ||
                   'Место регистрации (постоянного жительства):' ||
                   '</b></td><td>' || nvl(vListInfo.regaddress, '-') ||
                   '</td><td><b>' || 'Телефонный номер:' || '</b></td><td>' ||
                   nvl(to_char(vListInfo.Member_Phone), '-') ||
                   '</td></tr>' ||

                   '<tr><td><b>' ||
                   'Место пребывания (фактического нахождения):' ||
                   '</b></td><td>' || nvl(vListInfo.regaddress, '-') ||
                   '</td><td><b>' || 'Адрес электронной почты:' ||
                   '</b></td><td>' || nvl(vListInfo.Member_Email, '-') ||
                   '</td></tr>' || TAG_TABLE_END || TAG_TABLE_BEGIN ||
                   '<tr style="background:#cccccc"><td colspan="1"><b>' ||
                   'Комментарий:' || '</b></td></tr>' || '<tr><td>' ||
                   nvl(vListInfo.Member_Comments, '-') || '</td></tr>' ||
                   TAG_TABLE_END || '<hr/>';

        dbms_lob.append(vTmpLob, vListInfo.Pitaorg);
        dbms_lob.append(pMessagesCur, vTmpLob);
        i := i + 1;

      end if;
    END LOOP;
    if i = 1 then
      dbms_lob.append(pMessagesCur, vEmptyReport);
    end if;
    dbms_lob.append(pMessagesCur, vHtmlEnd);

    return result;
  exception
    when others then
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  'PKG_Reports.GetPitaOrgListHTML2');
      return - 1;
  end GetPitaOrgListHTML2;

  --GetPitaPersListHTML
  function GetPitaPersListHTML(pReportName  in varchar2,
                               pMessagesCur out clob) return number is
    TAG_TABLE_END   constant varchar2(8) := '</table>';
    TAG_TABLE_BEGIN constant varchar2(110) := '<table cellpadding="4" border="1" bordercolor=#000000 style="border-collapse:collapse" width="100%">';
    result      number default 0;
    vTmpLob     clob;
    i           number default 1;
    vHtmlHeader varchar2(1000 char) := '<html xmlns="http://www.w3.org/1999/xhtml">' ||
                                  '<head><style type="text/css"> body { background-color: #f0ffff; font-family:Verdana; font-size:10px;  }' ||
                                  '#main_container table tr { border-width: 1px; border-style: solid; font-size:10px; }' ||
                                  '</style> </head> <body> <div id="main_container">';
    vHtmlEnd    varchar2(100 char) := '</div> </body> </html>';
    vStrHeader  VARCHAR2(10000) := '<div align="center"><h3>' ||
                                   pReportName || '</h3></div></br></br>';

    vEmptyReport varchar2(100 char) := '<font>Данные успешно обработаны, соответствующих записей не найдено</font></br></br>';
  begin
    pMessagesCur := vHtmlHeader;
    dbms_lob.append(pMessagesCur, vStrHeader);

    FOR vListInfo IN (select m.mess_number as messnumber,
                             m.mess_date as messdate,
                             m.mess_type as messdoctype,
                             nvl(ind.member_ur_name,
                                 (ind.MEMBER_AC_SECONDNAME || ' ' ||
                                 ind.MEMBER_AC_FIRSTNAME || ' ' ||
                                 ind.MEMBER_AC_MIDDLENAME)) as fio,
                             ind.MEMBER_TYPE,
                             decode(ind.MEMBER_RESIDENCE,
                                    1,
                                    'Да',
                                    0,
                                    'Нет',
                                    '-') as isResident,
                             ind.MEMBER_RESIDENCE_COUNTRYCODE,
                             ind.MEMBER_FOREIGN,
                             ind.MEMBER_FOREIGN_ADD,
                             (select o.oper_number
                                from mess_cfm_announce_oper o
                               where o.mess_cfm_id = ind.mess_cfm_id) as opernumber,
                             ind.MEMBER_AC_DOC_TYPECODE,
                             ind.MEMBER_AC_DOC_NUMBER,
                             ind.MEMBER_AC_DOC_SERIES,
                             ind.MEMBER_AC_DOC_WHOM,
                             ind.MEMBER_AC_DOC_DATE,
                             ind.MEMBER_AC_BIRTH_DATE,
                             ind.MEMBER_AC_BIRTH_PLACE,
                             ind.MEMBER_ROLE,
                             ind.MEMBER_KIND,
                             ind.MEMBER_TAXCODE,
                             ind.MEMBER_OKPO,
                             ind.MEMBER_OKED,
                             ind.MEMBER_MAINCODE,
                             ind.MEMBER_BANK_NAME,
                             ind.MEMBER_BANK_ACCOUNT,
                             ind.MEMBER_BANK_ADDRESS,
                             ind.MEMBER_BANK_CODE,
                             pkg_analytic.GetDescription(ind.MEMBER_REG_COUNRYCODE,
                                                             41019) || ', ' ||
                             ind.MEMBER_REG_AREA || ', ' ||
                             ind.MEMBER_REG_REGION || ', ' ||
                             ind.MEMBER_REG_SITY || ', ' ||
                             ind.MEMBER_REG_STREET || ', ' ||
                             ind.MEMBER_REG_HOUSE || ', ' ||
                             ind.MEMBER_REG_OFFICE || ', ' ||
                             ind.MEMBER_REG_POSTCODE as regaddress,
                             pkg_analytic.GetDescription(ind.MEMBER_SEAT_COUNRYCODE,
                                                             41019) || ', ' ||
                             ind.MEMBER_SEAT_AREA || ', ' ||
                             ind.MEMBER_SEAT_REGION || ', ' ||
                             ind.MEMBER_SEAT_SITY || ', ' ||
                             ind.MEMBER_SEAT_STREET || ', ' ||
                             ind.MEMBER_SEAT_HOUSE || ', ' ||
                             ind.MEMBER_SEAT_OFFICE || ', ' ||
                             ind.MEMBER_SEAT_POSTCODE as seataddress,
                             ind.MEMBER_COMMENTS,
                             ind.MEMBER_PHONE,
                             ind.MEMBER_EMAIL,
                             pkg_pitaholding.ExistsInPitaPersons(ind.mess_cfm_id) as PitaPerson,
                             mv.versionid
                        FROM mess_cfm_announce_memb ind
                        join mess_cfm_announce_info m
                          on (m.mess_cfm_id = ind.mess_cfm_id)
                        join mess_cfm mc
                          on (mc.mess_cfm_id = ind.mess_cfm_id)
                        join messages_versions mv
                          on (mv.versioncode = mc.mess_version)
                       WHERE ind.member_type = 2
                          or ind.member_type = 3) LOOP
      /*     1 - Юридическое лицо
      2 - Физическое лицо
      3 - Индивидуальный предприниматель  */
      if vListInfo.PitaPerson is not null then
        vTmpLob := '<h3>Документ ' || to_char(i) || '</h3>' ||

                   TAG_TABLE_BEGIN ||

                   '<tr><td><b>' || 'Номер сообщения:' || '</b></td><td>' ||
                   nvl(to_char(vListInfo.messnumber), '-') ||
                   '</td><td><b>' || 'Дата сообщения:' || '</b></td><td>' ||
                   nvl(to_char(vListInfo.messdate, 'DD.MM.YYYY'), '-') ||
                   '</td><td><b>' || 'Вид сообщения:' || '</b></td><td>' ||
                   nvl(pkg_analytic.GetCodeDescription(vListInfo.messdoctype,
                                                       1009,
                                                       vListInfo.versionid),
                       '-') || '</td></tr>' || TAG_TABLE_END ||

                   TAG_TABLE_BEGIN ||

                   '<tr style="background:#cccccc"><td colspan="6"><b>' ||
                   'Информация об объекте:' || '</b></td></tr>' ||

                   '<tr><td><b>' || 'ФИО:' || '</b></td><td colspan="5">' ||
                   nvl(vListInfo.fio, '-') || '</td></tr>' ||

                   '<tr><td><b>' || 'Тип:' || '</b></td><td>' ||
                   pkg_analytic.GetCodeDescription(vListInfo.Member_Type,
                                                   10,
                                                   vListInfo.versionid) ||
                   '</td><td><b>' || 'Резидент:' || '</b></td><td>' ||
                   vListInfo.isResident || '</td><td colspan="2">' ||
                   nvl(pkg_analytic.GetDescription(vListInfo.MEMBER_RESIDENCE_COUNTRYCODE,
                                                       41019),
                       '-') || '</td></tr>' ||

                   '<tr><td><b>' || 'Документ, удостоверяющий личность:' ||
                   '</b></td><td>' ||
                   pkg_analytic.GetCodeDescription(vListInfo.Member_Ac_Doc_Typecode,
                                                   100,
                                                   vListInfo.versionid) ||
                   '</td><td><b>' || 'Серия:' || '</b></td><td>' ||
                   nvl(vListInfo.Member_Ac_Doc_Series, '-') ||
                   '</td><td><b>' || 'Номер:' || '</b></td><td>' ||
                   nvl(to_char(vListInfo.Member_Ac_Doc_Number), '-') ||
                   '</td></tr>' ||

                   '<tr><td><b>' || 'Кем выдан:' ||
                   '</b></td><td colspan="2">' ||
                   nvl(vListInfo.Member_Ac_Doc_Whom, '-') ||
                   '</td><td colspan="2"><b>' || 'Дата выдачи:' ||
                   '</b></td><td>' ||
                   nvl(to_char(vListInfo.Member_Ac_Doc_Date, 'DD.MM.YYYY'),
                       '-') || '</td></tr>' ||

                   '<tr><td><b>' || 'Место рождения:' ||
                   '</b></td><td colspan="2">' ||
                   nvl(vListInfo.Member_Ac_Birth_Place, '-') ||
                   '</td><td colspan="2"><b>' || 'Дата рождения:' ||
                   '</b></td><td>' ||
                   nvl(to_char(vListInfo.Member_Ac_Birth_Date, 'DD.MM.YYYY'),
                       '-') || '</td></tr>' ||

                   '<tr><td><b>' || 'Аффилированность:' ||
                   '</b></td><td colspan="5">' ||
                   pkg_analytic.GetCodeDescription(vListInfo.Member_Foreign,
                                                   1014,
                                                   vListInfo.versionid) || ' ' ||
                   vListInfo.Member_Foreign_Add || '</td></tr>' ||
                   TAG_TABLE_END || TAG_TABLE_BEGIN ||

                   '<tr style="background:#cccccc"><td colspan="4"><b>' ||
                   'Информация об операции' || '</b></td></tr>' ||

                   '<tr><td><b>' || 'Номер операции:' || '</b></td><td>' ||
                   nvl(to_char(vListInfo.opernumber), '-') ||
                   '</td><td><b>' || 'Роль в операции:' || '</b></td><td>' ||
                   pkg_analytic.GetCodeDescription(vListInfo.Member_Role,
                                                   102,
                                                   vListInfo.versionid) ||
                   '</td></tr><tr><td><b>' || 'Вид участника:' ||
                   '</b></td><td colspan="3">' ||
                   pkg_analytic.GetCodeDescription(vListInfo.Member_Kind,
                                                   1006,
                                                   vListInfo.versionid) ||
                   '</td></tr>' ||

                   TAG_TABLE_END || TAG_TABLE_BEGIN ||

                   '<tr style="background:#cccccc"><td colspan="8"><b>' ||
                   'Номера' || '</b></td></tr>' ||

                   '<tr><td><b>' || 'РНН' || '</b></td><td>' ||
                   nvl(to_char(vListInfo.Member_Taxcode), '-') ||
                   '</td><td><b>' || 'ОКПО' || '</b></td><td>' ||
                   nvl(to_char(vListInfo.Member_Okpo), '-') ||
                   '</td><td><b>' || 'ОКЕД' || '</b></td><td>' ||
                   nvl(to_char(vListInfo.Member_Oked), '-') ||
                   '</td><td><b>' || 'ИНН/БИН' || '</b></td><td>' ||
                   nvl(to_char(vListInfo.Member_Maincode), '-') ||
                   '</td></tr>' ||

                   TAG_TABLE_END || TAG_TABLE_BEGIN ||

                   '</td></tr><tr style="background:#cccccc"><td colspan="4"><b>' ||
                   'Информация о банке-корреспонденте' || '</b></td></tr>' ||

                   '<tr><td><b>' || 'Наименование:' || '</b></td><td>' ||
                   nvl(vListInfo.Member_Bank_Name, '-') || '</td><td><b>' ||
                   'Код:' || '</b></td><td>' ||
                   nvl(to_char(vListInfo.Member_Bank_Code), '-') ||
                   '</td></tr>' ||

                   '<tr><td><b>' || 'Адрес:' || '</b></td><td>' ||
                   nvl(vListInfo.Member_Bank_Address, '-') ||
                   '</td><td><b>' || 'Номер счета:' || '</b></td><td>' ||
                   nvl(vListInfo.Member_Bank_Account, '-') || '</td></tr>' ||

                   TAG_TABLE_END || TAG_TABLE_BEGIN ||

                   '<tr style="background:#cccccc"><td colspan="4"><b>' ||
                   'Контактная информация' || '</b></td></tr>' ||

                   '<tr><td><b>' ||
                   'Место регистрации (постоянного жительства):' ||
                   '</b></td><td>' || nvl(vListInfo.regaddress, '-') ||
                   '</td><td><b>' || 'Телефонный номер:' || '</b></td><td>' ||
                   nvl(to_char(vListInfo.Member_Phone), '-') ||
                   '</td></tr>' ||

                   '<tr><td><b>' ||
                   'Место пребывания (фактического нахождения):' ||
                   '</b></td><td>' || nvl(vListInfo.regaddress, '-') ||
                   '</td><td><b>' || 'Адрес электронной почты:' ||
                   '</b></td><td >' || nvl(vListInfo.Member_Email, '-') ||
                   '</td></tr>' || TAG_TABLE_END || TAG_TABLE_BEGIN ||
                   '<tr style="background:#cccccc"><td colspan="1"><b>' ||
                   'Комментарий:' || '</b></td></tr>' || '<tr><td>' ||
                   nvl(vListInfo.Member_Comments, '-') || '</td></tr>' ||
                   TAG_TABLE_END || '<hr/>';

        dbms_lob.append(vTmpLob, vListInfo.Pitaperson);
        dbms_lob.append(pMessagesCur, vTmpLob);
        i := i + 1;
      end if;
    END LOOP;
    if i = 1 then
      dbms_lob.append(pMessagesCur, vEmptyReport);
    end if;
    dbms_lob.append(pMessagesCur, vHtmlEnd);

    return result;
  exception
    when others then
      return - 1;
  end GetPitaPersListHTML;

  --GetReportNames
  function GetReportNames(ReportNamesCur out sys_refcursor) return number is
    result number default 0;
    vlang  varchar2(30 char);
  begin
    vlang := pkg_main.GetUserLangSettings;
    case vlang
      when 'Kazakh' then
        open ReportNamesCur for
          select reportnumber, reportname_kz as "reportname"
            from reports
           where tablename = 'MESSAGES_CFM';
      else
        open ReportNamesCur for
          select reportnumber, reportname
            from reports
           where tablename = 'MESSAGES_CFM';
    end case;
    return result;
  exception
    when others then
      return - 2200; --Ошибка формирования списка отчетов
  end GetReportNames;

  --GetReportName
  function GetReportName(pReportNumber in number, pReportName out varchar2)
    return number is
    result number default 0;
  begin
    select reportname
      into pReportName
      from reports
     where tablename = 'MESSAGES_CFM'
       and reportnumber = pReportNumber;
    return result;
  exception
    when others then
      return - 2201; --Ошибка при получении названия отчета
  end GetReportName;

  --MakeReport
  function MakeReport(pReportNumber in number, pReportBody out clob)
    return number is
    cDATE_FORMAT constant varchar2(21) := 'DD-MM-YYYY hh24:mm:ss';
    result      number default 0;
    pReportName varchar2(1000 char);
    vResult     number default - 1;
    vResCurr    sys_refcursor;
  begin
    vResult := GetReportName(pReportNumber, pReportName);

    case pReportNumber
      when 1 then
        begin
          vResult := GetFirstList(vResCurr);
          if vResult = 0 then
            begin
              pReportName := pReportName || ' (c ' ||
                             to_char(trunc(sysdate) - 1, cDATE_FORMAT) ||
                             ' по ' ||
                             to_char(trunc(sysdate), cDATE_FORMAT) || ')';
              vResult     := GetFirstOrSecondListHTML2(pReportName,
                                                       vResCurr,
                                                       pReportBody);
              if vResult != 0 then
                result := -2202; --Ошибка при формировании отчета
              end if;
              close vResCurr;
            end;
          else
            begin
              result := -2202; --Ошибка при формировании отчета
            end;
          end if;
        end;
      when 2 then
        begin
          vResult := GetNoFirstList(vResCurr);
          if vResult = 0 then
            begin
              pReportName := pReportName || ' (c ' ||
                             to_char(trunc(sysdate) - 1, cDATE_FORMAT) ||
                             ' по ' ||
                             to_char(trunc(sysdate), cDATE_FORMAT) || ')';
              vResult     := GetFirstOrSecondListHTML2(pReportName,
                                                       vResCurr,
                                                       pReportBody);
              if vResult != 0 then
                result := -2202; --Ошибка при формировании отчета
              end if;
              close vResCurr;
            end;
          else
            begin
              result := -2202; --Ошибка при формировании отчета
            end;
          end if;
        end;
      when 3 then
        begin
          vResult := GetSecondList(vResCurr);
          if vResult = 0 then
            begin
              pReportName := pReportName || ' (c ' ||
                             to_char(trunc(sysdate) - 1, cDATE_FORMAT) ||
                             ' по ' ||
                             to_char(trunc(sysdate), cDATE_FORMAT) || ')';
              vResult     := GetFirstOrSecondListHTML2(pReportName,
                                                       vResCurr,
                                                       pReportBody);
              if vResult != 0 then
                result := -2202; --Ошибка при формировании отчета
              end if;
              close vResCurr;
            end;
          else
            begin
              result := -2202; --Ошибка при формировании отчета
            end;
          end if;
        end;
      when 4 then
        vResult := GetPitaOrgListHTML2(pReportName, pReportBody);
        if vResult != 0 then
          result := -2202; --Ошибка при формировании отчета
        end if;
      when 5 then
        vResult := GetPitaPersListHTML(pReportName, pReportBody);
        if vResult != 0 then
          result := -2202; --Ошибка при формировании отчета
        end if;
      when 6 then
        begin
          vResult := GetThirdList(vResCurr);
          if vResult = 0 then
            begin
              pReportName := pReportName || ' (c ' ||
                             to_char(trunc(sysdate), cDATE_FORMAT) ||
                             ' по ' || to_char(sysdate, cDATE_FORMAT) || ')';
              vResult     := GetFirstOrSecondListHTML2(pReportName,
                                                       vResCurr,
                                                       pReportBody);
              if vResult != 0 then
                result := -2202; --Ошибка при формировании отчета
              end if;
              close vResCurr;
            end;
          else
            result := -2202; --Ошибка при формировании отчета
          end if;
        end;
      else
        result := -2203; --Формирование данного отчета не предусмотрено
    end case;

    return result;
  exception
    when others then
      return - 2202; --Ошибка при формировании отчета
  end MakeReport;

end PKG_Reports;
/

CREATE OR REPLACE PUBLIC SYNONYM PKG_REPORTS FOR KFM.PKG_REPORTS;
GRANT EXECUTE ON KFM.PKG_REPORTS TO KFM_ROLE;
