/*
* Автор: Маркелов С.В.
* Дата: 2014-04-08
* Задача: http://jira.softage.ru/jira/browse/SDFO-279
* Назначение: Добавление обработки кодов стран
* Изменяются объекты:
		update package PKG_ANALYTIC
		update package PKG_MESS_CFM_1
		update package PKG_MESS_CFM_2
		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-04-08.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
	select a.shortname
	into result
	from analyticcodes a
	where analytictype = pType
	and analyticcode = pCode;
    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 AC.ANALYTICNAME
          INTO result
          FROM ANALYTICCODES ac
         WHERE AC.ANALYTICTYPE = 41019 AND AC.OUTER_CODE = pCode;

    return result;
  exception
    when others then
      return pCode || '--Значение не найдено в справочнике';
  end GetCountryDescription;

end PKG_Analytic;
/
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 := 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 := to_char(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.GetSingleOKSMTVal(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 PACKAGE BODY KFM."PKG_MESS_CFM_2" as

  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 varchar2) 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/Signature')
    into pSignature
    from dual;

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

  function ParseMessageInfo(pnode       xmldom.domnode,
                            pmessid     in number,
                            pversion_id 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'), 'dd.mm.yyyy'); --pmessdate; -- Выдергивать напрямую из XML
    vMess.mess_type        := to_number(pkg_messages_xml.getsingleval(pnode, 'MessageInformation/DocumentType'));
    vMess.mess_oper_status := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/OperationStatusId');
    vMess.mess_reason_code := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/ReasonFilingId');
    vMess.mess_suspic_code := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/SuspicionsCriterians');

    vMess.Mess_Trans_Date := to_date(pkg_messages_xml.getsingleval(pNode, 'MessageInformation/MessTransDate'), 'dd.mm.yyyy hh24:mi:ss');
    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,
                        pversion_id 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/OrganisationCity');
    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_Isac          := pkg_common.BoolToNumber(pkg_messages_xml.getsingleval(pnode, 'PersonalData/AdditionalAcData/@IsAc'));
    --алексей 2012.10.23
    --if vCFM.Cfm_Isac=1 then
    vCFM.Cfm_Doc_Typecode  := pkg_messages_xml.getsingleval(pnode, 'PersonalData/AdditionalAcData/DocumentIdentity');
    vCFM.Cfm_Doc_Number    := pkg_messages_xml.getsingleval(pnode, 'PersonalData/AdditionalAcData/NumberDocIdentity');
    vCFM.Cfm_Doc_Series    := pkg_messages_xml.getsingleval(pnode, 'PersonalData/AdditionalAcData/SeriesDocIdentity');
    vCFM.Cfm_Doc_Whom      := pkg_messages_xml.getsingleval(pnode, 'PersonalData/AdditionalAcData/DocumentIssued');
    vCFM.Cfm_Doc_Date      := to_date(pkg_messages_xml.getsingleval(pnode, 'PersonalData/AdditionalAcData/DateIssuance'), 'dd.mm.yyyy');
    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');
    --алексей 2012.10.23
    --end if;
    ---Только для версии 3 (Код ОПФ + Страны согласно справочнику ИС АР)------------------------------
    if pversion_id >= 3 then
      vcfm.cfm_ur_opf_code      := pkg_messages_xml.getsingleval(pnode, 'PersonalData/OrganisationOPF');
      vcfm.cfm_seat_area_code   := pkg_messages_xml.getsingleval(pnode, 'PersonalData/OrganisationArea/@Code');
      vcfm.cfm_seat_region_code := pkg_messages_xml.getsingleval(pnode, 'PersonalData/OrganisationDistrict/@Code');
      vcfm.cfm_seat_sity_code   := pkg_messages_xml.getsingleval(pnode, 'PersonalData/OrganisationCity/@Code');
      vcfm.cfm_seat_street_code := pkg_messages_xml.getsingleval(pnode, 'PersonalData/OrganisationStreet/@Code');
      vcfm.cfm_seat_house_code  := pkg_messages_xml.getsingleval(pnode, 'PersonalData/OrganisationHouse/@Code');
      vcfm.cfm_seat_office_code := pkg_messages_xml.getsingleval(pnode, 'PersonalData/OrganisationOffice/@Code');
    end if;
    ---Только для версии 3 (Код ОПФ + Страны согласно справочнику ИС АР)------------------------------
    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,
                               pversion_id 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,
                              pversion_id 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/ViewOperationId');
    vOperation.oper_idtype       := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/EknpId');
    vOperation.Oper_IsNotIDTyped := pkg_common.BoolToNumber(pkg_messages_xml.getsingleval(pNode, 'MessageInformation/EknpId/@IsEknpNotSetup'));
    vOperation.Oper_Trans_Date   := to_date(pkg_messages_xml.getsingleval(pNode, 'MessageInformation/TransactionDate'), 'dd.mm.yyyy hh24:mi:ss');
    --IsEknpNotSetup

    vOperation.oper_members_count    := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/ParticipantCount');
    vOperation.oper_currency_code   := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/CurrencyCodeId');
    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/DocOperationReason');
    vOperation.oper_doc_date         := to_date(pkg_messages_xml.getsingleval(pNode, 'MessageInformation/DocOperationDate'), 'dd.mm.yyyy');
    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');

    vOperation.oper_merch_types   := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/MerchTypes');
    vOperation.oper_merch_reginfo := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/MerchReginfo');
    vOperation.oper_refers_count  := pkg_messages_xml.getsingleval(pNode, 'MessageInformation/ReferCount');

    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,
                           pversion_id 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_membercode := pkg_messages_xml.getsingleval(pnode, 'MemberId'); --участник (4.1)
    pMember.member_isclient   := pkg_messages_xml.getsingleval(pnode, 'IsClientSubject');
    --  pMember.member_residence  :=BoolToNumber(pkg_messages_xml.getsingleval(pnode,'MemberCommunityCityCode/@IsMemberCommunity'));

    pMember.member_residence_countrycode := pkg_messages_xml.GetSingleOKSMTVal(pkg_messages_xml.getsingleval(pnode, 'MemberCommunityCityCode'));
    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, 'CorrespondentBank/Code');
    pMember.member_bank_city    := pkg_messages_xml.getsingleval(pnode, 'CorrespondentBank/BankCity');
    pMember.member_bank_name    := pkg_messages_xml.getsingleval(pnode, 'CorrespondentBank/Name');
    pMember.member_bank_account := pkg_messages_xml.getsingleval(pnode, 'CorrespondentBank/AccountNumber');
    --pMember.member_bank_address := pkg_messages_xml.getsingleval(pnode, 'CorrespondentBank/BankAddress');
    pMember.member_bank_address := to_char(pkg_messages_xml.GetSingleOKSMTVal(pkg_messages_xml.getsingleval(pnode, 'CorrespondentBank/BankAddress')));
    pMember.MEMBER_BANK_OFFSHORE_ADDR := pkg_messages_xml.getsingleval(pnode, 'CorrespondentBank/BankOffshoreAddr');
    if pMember.MEMBER_BANK_OFFSHORE_ADDR is not null then pMember.MEMBER_BANK_IS_OFFSHORE := 1;
     end if;
    pMember.member_taxcode      := pkg_messages_xml.getsingleval(pnode, 'RNN');
    --  pMember.member_okpo         := pkg_messages_xml.getsingleval(pnode,'OKPO');
    --pMember.member_oked     := pkg_messages_xml.getsingleval(pnode, 'OKED');
    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');
    --не было
    pMember.money_trans_sys := pkg_messages_xml.getsingleval(pnode, 'MoneyTransSys');
    if pMember.member_type is null then
      pMember.member_type := 1;
    end if;
    case pMember.member_type
      when 1 then
        pInfoPath := pUrInfoPath;

        pMember.member_ur_name    := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'FullName');
        pMember.Member_IsNotNamed := pkg_common.BoolToNumber(pkg_messages_xml.getsingleval(pnode, pInfoPath || 'FullName/@IsFullNameSetup'));
        --IsFullNameSetup
        if pversion_id = 2 then
          --Для версии 2--------------------------- (ФИО)------------------------------
          pMember.member_ur_fisthead_name := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'FirstHead');
        elsif pversion_id >= 3 then
          ---Только для версии 3 (Код ОПФ + разбиение ФИО)------------------------------
          pMember.Member_Ur_Opf_Code            := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ParticipantOPF');
          pMember.Member_Ur_Fisthead_Firstname  := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'FirstHead/FirstName');
          pMember.Member_Ur_Fisthead_Secondname := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'FirstHead/SecondName');
          pMember.Member_Ur_Fisthead_Middlename := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'FirstHead/MiddleName');
          ---Только для версии 3 (Код ОПФ + разбиение ФИО)------------------------------
        end if;

      when 2 then
        pInfoPath := pAcInfoPath;

        --IsFioNotSetup
        pMember.member_ac_birth_date   := to_date(pkg_messages_xml.getsingleval(pnode, pInfoPath || 'DateBirdth'), 'dd.mm.yyyy');
        pMember.member_ac_birth_place  := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'PlaceBirdth');
        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 || 'DocumentIssued');
        pMember.member_ac_doc_date     := to_date(pkg_messages_xml.getsingleval(pnode, pInfoPath || 'DateIssuance'), 'dd.mm.yyyy');
        pMember.Member_IsNotNamed      := pkg_common.BoolToNumber(pkg_messages_xml.getsingleval(pnode, pInfoPath || 'FIO/@IsFioNotSetup'));
        if pversion_id = 2 then
          --Для версии 2------- (ФИО)------------------------------
          pMember.member_ur_name := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'FIO');
        elsif pversion_id >= 3 then
          ---Только для версии 3 (ФИО)------------------------------
          if pMember.Member_IsNotNamed = 0 then
            pMember.Member_Ac_Firstname  := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'FIO/FirstName');
            pMember.Member_Ac_Secondname := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'FIO/SecondName');
            pMember.Member_Ac_Middlename := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'FIO/MiddleName');
          end if;
          ---Только для версии 3 (ФИО)------------------------------
        end if;

      when 3 then
        pInfoPath := pIpInfoPath;

        --IsFioNotSetup
        pMember.member_ac_birth_date   := to_date(pkg_messages_xml.getsingleval(pnode, pInfoPath || 'DateBirdth'), 'dd.mm.yyyy');
        pMember.member_ac_birth_place  := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'PlaceBirdth');
        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 || 'DocumentIssued');
        pMember.member_ac_doc_date     := to_date(pkg_messages_xml.getsingleval(pnode, pInfoPath || 'DateIssuance'), 'dd.mm.yyyy');
        pMember.Member_IsNotNamed      := pkg_common.BoolToNumber(pkg_messages_xml.getsingleval(pnode, pInfoPath || 'FIO/@IsFioNotSetup'));
        if pversion_id = 2 then
          --Для версии 2------- (ФИО)------------------------------
          pMember.member_ur_name := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'FIO');
        elsif pversion_id >= 3 then
          ---Только для версии 3 (ФИО)------------------------------
          if pMember.Member_IsNotNamed = 0 then
            pMember.Member_Ac_Firstname  := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'FIO/FirstName');
            pMember.Member_Ac_Secondname := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'FIO/SecondName');
            pMember.Member_Ac_Middlename := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'FIO/MiddleName');
          end if;
          ---Только для версии 3 (ФИО)------------------------------
        end if;
      else
        return - 3007; ---6010; --Неправильный код тип участника
    end case;

   -- pMember.member_reg_counrycode := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'URAddress/Country');
    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.getsingleval(pnode, pInfoPath || 'ACAddress/Country');
    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');

    ---Только для версии 3 (Страны согласно справочнику ИС АР)------------------------------
    if pversion_id >= 3 then
        pMember.member_reg_area_code   := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'URAddress/Area/@Code');
        pMember.member_reg_region_code := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'URAddress/District/@Code');
        pMember.member_reg_sity_code   := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'URAddress/Town/@Code');
        pMember.member_reg_street_code := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'URAddress/Street/@Code');
        pMember.member_reg_house_code  := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'URAddress/HomeNumber/@Code');
        pMember.member_reg_office_code := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'URAddress/OfficeNumber/@Code');

        pMember.member_seat_area_code   := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ACAddress/Area/@Code');
        pMember.member_seat_region_code := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ACAddress/District/@Code');
        pMember.member_seat_sity_code   := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ACAddress/Town/@Code');
        pMember.member_seat_street_code := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ACAddress/Street/@Code');
        pMember.member_seat_house_code  := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ACAddress/HomeNumber/@Code');
        pMember.member_seat_office_code := pkg_messages_xml.getsingleval(pnode, pInfoPath || 'ACAddress/OfficeNumber/@Code');
    end if;
    ---Только для версии 3  (Страны согласно справочнику ИС АР)------------------------------

    insert into mess_cfm_announce_memb
    values pMember;

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

  function ParseReferInfo(pnode    xmldom.domnode,
                          pmessid  in number,
                          preferid in number) return number is
    result number default 0;
    pRefer mess_cfm_refer%rowtype;
  begin
    pRefer.mess_cfm_id := pmessid;
    pRefer.referid     := preferid + 1;

    pRefer.refer_doc_oper := pkg_messages_xml.getsingleval(pnode, 'ReferenceOperationNumber');
    pRefer.refer_doc_date := to_date(pkg_messages_xml.getsingleval(pnode, 'ReferenceDocOperationDate'), 'dd.mm.yyyy');
    pRefer.refer_doc_num  := pkg_messages_xml.getsingleval(pnode, 'ReferencerDocOperationNumber');

    insert into mess_cfm_refer
    values pRefer;
    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,
                            pversion_id number) return number is
    result    number default 0;
    pFounnder mess_cfm_announce_found%rowtype;

  begin
    pFounnder.Founder_Id  := pfounderid + 1;
    pFounnder.mess_cfm_id := pmessid;
    pFounnder.member_id   := pmemberid + 1;
    if pversion_id = 2 then
      pFounnder.founder_ur_name := pkg_messages_xml.getsingleval(pnode, 'Name');
    elsif pversion_id >= 3 then
      pFounnder.Founder_Type := pkg_messages_xml.getsingleval(pnode, 'FounderType');
      case
        when pFounnder.Founder_Type = 1 then
          pFounnder.Founder_Ur_Opf_Code := pkg_messages_xml.getsingleval(pnode, 'FounderOPF');
          pFounnder.founder_ur_name     := pkg_messages_xml.getsingleval(pnode, 'Name');
        when pFounnder.Founder_Type = 2 then
          pFounnder.Founder_Ac_Firstname  := pkg_messages_xml.getsingleval(pnode, 'FirstName');
          pFounnder.Founder_Ac_Secondname := pkg_messages_xml.getsingleval(pnode, 'SecondName');
          pFounnder.Founder_Ac_Middlename := pkg_messages_xml.getsingleval(pnode, 'MiddleName');
        when pFounnder.Founder_Type = 3 then
          pFounnder.Founder_Ac_Firstname  := pkg_messages_xml.getsingleval(pnode, 'FirstName');
          pFounnder.Founder_Ac_Secondname := pkg_messages_xml.getsingleval(pnode, 'SecondName');
          pFounnder.Founder_Ac_Middlename := pkg_messages_xml.getsingleval(pnode, 'MiddleName');
         when pFounnder.Founder_Type >3 then
          pFounnder.Founder_Ur_Opf_Code := pkg_messages_xml.getsingleval(pnode, 'FounderType');
          pFounnder.founder_ur_name     := pkg_messages_xml.getsingleval(pnode, 'Name');
          pFounnder.Founder_Ac_Firstname  := pkg_messages_xml.getsingleval(pnode, 'FirstName');
          pFounnder.Founder_Ac_Secondname := pkg_messages_xml.getsingleval(pnode, 'SecondName');
          pFounnder.Founder_Ac_Middlename := pkg_messages_xml.getsingleval(pnode, 'MiddleName');
        else
          return - 3006;
      end case;
    end if;
    --pFounnder.founder_ur_residence_c_code := pkg_messages_xml.getsingleval(pnode, 'MemberCommunityCityCode');
    pFounnder.founder_ur_residence_c_code := pkg_messages_xml.GetSingleOKSMTVal(pkg_messages_xml.getsingleval(pnode, 'MemberCommunityCityCode'));
    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,
                             pversion_id number) return number is
    result    number default 0;
    vNodeList xmldom.domnodelist;
    vnode     xmldom.domnode;

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

  function ParseAccountInfo(pnode       xmldom.domnode,
                            pmessid     in number,
                            pmemberid   in number,
                            paccountid  in number,
                            pversion_id number) return number is
    result   number default 0;
    pAccount mess_cfm_announce_accounts%rowtype;

  begin
    pAccount.accountId   := paccountid + 1;
    pAccount.mess_cfm_id := pmessid;
    pAccount.memberid    := pmemberid + 1;

    pAccount.Accountnumber := pkg_messages_xml.getsingleval(pnode, 'AccountNumber');
    pAccount.Bankname      := pkg_messages_xml.getsingleval(pnode, 'BankName');
    pAccount.Bankcode      := pkg_messages_xml.getsingleval(pnode, 'BankCode');
    -- pAccount.Bankaddress   := pkg_messages_xml.getsingleval(pnode, 'BankAddress');
    pAccount.Bankaddress   := to_char(pkg_messages_xml.GetSingleOKSMTVal(pkg_messages_xml.getsingleval(pnode, 'BankAddress')));

    insert into mess_cfm_announce_accounts
    values pAccount;
    return result;
  EXCEPTION
    WHEN OTHERS THEN
      return - 3014; -- Неверный формат данных в информации о корреспондентских счетах
      return result;
  end;

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

  begin
    vNodeList := pkg_messages_xml.GetNodesList(pnode, 'CorrespondentBank/CorrespondentsInformations/CorrespondentInformation');
    for i in 0 .. xmldom.getLength(vNodeList) - 1 loop
      vnode  := xmldom.item(vNodeList, i);
      result := ParseAccountInfo(vnode, pmessid, pmemberid, i, pversion_id);
      if result <> 0 then
        return result;
      end if;
    end loop;
    return result;
  EXCEPTION
    WHEN OTHERS THEN
      return - 3014; --Неверный формат данных в информации о корреспондентских счетах
      return result;
  end;

  function ParseMembersInfo(pnode       xmldom.domnode,
                            pmessid     in number,
                            pversion_id number) return number is
    result    number default 0;
    vNodeList xmldom.domnodelist;
    vnode     xmldom.domnode;
    ptemperr  number;
  begin
    vNodeList := pkg_messages_xml.GetNodesList(pnode, 'Participants/Participant');
    for i in 0 .. xmldom.getLength(vNodeList) - 1 loop
      vnode  := xmldom.item(vNodeList, i);
      result := ParseMemberInfo(vnode, pmessid, i, pversion_id);
      if result <> 0 then
        return result;
      end if;
      result := ParseAccountsInfo(vnode, pmessid, i, pversion_id);
      if result <> 0 then
        ptemperr := result;
      end if;
      result := ParseFoundersInfo(vnode, pmessid, i, pversion_id);
      if result <> 0 then
        return result;
      elsif ptemperr <> 0 then
        return ptemperr;
      end if;
    end loop;
    ------
    return result;
  end;

  function ParseRefersInfo(pnode   xmldom.domnode,
                           pmessid in number) return number is
    result    number default 0;
    vNodeList xmldom.domnodelist;
    vnode     xmldom.domnode;
    ptemperr  number;
  begin
    vNodeList := pkg_messages_xml.GetNodesList(pnode, 'References/Reference');
    for i in 0 .. xmldom.getLength(vNodeList) - 1 loop
      vnode  := xmldom.item(vNodeList, i);
      result := ParseReferInfo(vnode, pmessid, i);
      if result <> 0 then
        return result;
      end if;
    end loop;
    return result;
  end;

  function parseAnnounceXML(pmessid     in number,
                            pversion_id 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); end if;
     result:=ParseCmfInfo(vnode,pmessid);
      if result<>0 then vres:=SetErrorCode(pmessid,result); end if;
     result:=ParseCfmcontactInfo(vnode,pmessid);
      if result<>0 then vres:=SetErrorCode(pmessid,result); end if;
     result:=ParseOperationInfo(vnode,pmessid);
      if result<>0 then vres:=SetErrorCode(pmessid,result); end if;
     result:=ParseMembersInfo(vnode,pmessid);
      if result<>0 then vres:=SetErrorCode(pmessid,result); end if;
    */

    result := ParseMessageInfo(vnode, pmessid, pversion_id);

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

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

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

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

    if result <> 0 then
      vres    := SetErrorCode(pmessid, result);
      tempres := result;
    end if;
    result := ParseRefersInfo(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 GetOuterAnnounceXML_2(PXMLSCH1 xmltype) return xmltype is
    pXML    xmltype;
    PXMLSCH xmltype;
    i       number default 1;
    j       number default 1;
    --i number
  begin
    begin
      select updateXml(PXMLSCH1,
                       --     'Root/PersonalData/OrganisationCode/text()',
                       --       pkg_analytic.GetDescription(extractvalue(PXMLSCH,'Root/PersonalData/OrganisationCode/text()'),1005),
                       --     'Root/MessageInformation/ViewOperationId/text()',
                       --       pkg_analytic.GetDescription(extractvalue(PXMLSCH,'Root/MessageInformation/ViewOperationId/text()'),21004),
                       --     'Root/MessageInformation/EknpId/text()',
                       --       pkg_analytic.GetDescription(extractvalue(PXMLSCH,'Root/MessageInformation/EknpId/text()'),21017),
                       --     'Root/MessageInformation/CurrencyCodeId/text()',
                       --       pkg_analytic.GetDescription(extractvalue(PXMLSCH,'Root/MessageInformation/CurrencyCodeId/text()'),21018),
                       --     'Root/MessageInformation/ReasonFilingId/text()',
                       --       pkg_analytic.GetDescription(extractvalue(PXMLSCH,'Root/MessageInformation/ReasonFilingId/text()'),21011),
                       'Root/MessageInformation/SuspicionsCriterians/text()',
                       pkg_analytic.GetDescription(extractvalue(PXMLSCH1, 'Root/MessageInformation/SuspicionsCriterians/text()'), 21012),
                       'Root/MessageInformation/DocumentType/text()',
                       pkg_analytic.GetDescription(extractvalue(PXMLSCH1, 'Root/MessageInformation/DocumentType/text()'), 21009),
                       'Root/MessageInformation/OperationStatusId/text()',
                       pkg_analytic.GetDescription(extractvalue(PXMLSCH1, 'Root/MessageInformation/OperationStatusId/text()'), 21010),
                       'Root/MessageInformation/SuspicionFirst/text()',
                       pkg_analytic.GetShortDescription(extractvalue(PXMLSCH1, 'Root/MessageInformation/SuspicionFirst/text()'), 21008),
                       'Root/MessageInformation/SuspicionSecond/text()',
                       pkg_analytic.GetShortDescription(extractvalue(PXMLSCH1, 'Root/MessageInformation/SuspicionSecond/text()'), 21008),
                       'Root/MessageInformation/SuspicionThird/text()',
                       pkg_analytic.GetShortDescription(extractvalue(PXMLSCH1, 'Root/MessageInformation/SuspicionThird/text()'), 21008))
      into pXML
      from dual;
      PXMLSCH := pxml;

      select appendChildXML(PXMLSCH, 'Root/MessageInformation/ReasonFilingId', XMLType('<Descr>' || pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/MessageInformation/ReasonFilingId/text()'), 21011) || '</Descr>'))
      into pxml
      from dual;
    exception
      when others then
        pXML := PXMLSCH1;
    end;

    PXMLSCH := pxml;

    begin
      loop
        if (xmltype.existsNode(pxml, 'Root/Participants/Participant[' || to_char(i) || ']') = 1) then
          select updateXml(PXMLSCH,
                           'Root/Participants/Participant[' || to_char(i) || ']/MemberId/text()',
                           pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/Participants/Participant[' || to_char(i) || ']/MemberId/text()'), 21013),
                           'Root/Participants/Participant[' || to_char(i) || ']/ParticipantsView/text()',
                           pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/Participants/Participant[' || to_char(i) || ']/ParticipantsView/text()'), 21006),
                           --     'Root/Participants/Participant['||to_char(i)||']/ParticipantsType/text()',
                           --       pkg_analytic.GetDescription(extractvalue(PXMLSCH,'Root/Participants/Participant['||to_char(i)||']/ParticipantsType/text()'),10),
                           'Root/Participants/Participant[' || to_char(i) || ']/IsClientSubject/text()',
                           pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/Participants/Participant[' || to_char(i) || ']/IsClientSubject/text()'), 21020),
                           'Root/Participants/Participant[' || to_char(i) || ']/MemberCommunityCityCode/text()',
                           pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/Participants/Participant[' || to_char(i) || ']/MemberCommunityCityCode/text()'), 21019),
                           'Root/Participants/Participant[' || to_char(i) || ']/ForeignPerson/text()',
                           pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/Participants/Participant[' || to_char(i) || ']/ForeignPerson/text()'), 21014),
                           'Root/Participants/Participant[' || to_char(i) || ']/CorrespondentBank/BankAddress/text()',
                           pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/Participants/Participant[' || to_char(i) || ']/CorrespondentBank/BankAddress/text()'), 21019),
                           'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationAc/URAddress/Country/text()',
                           pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationAc/URAddress/Country/text()'), 21019),
                           'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationAc/ACAddress/Country/text()',
                           pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationAc/ACAddress/Country/text()'), 21019),
                           'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationIp/URAddress/Country/text()',
                           pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationIp/URAddress/Country/text()'), 21019),
                           'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationIp/ACAddress/Country/text()',
                           pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationIp/ACAddress/Country/text()'), 21019),
                           'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationUr/URAddress/Country/text()',
                           pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationUr/URAddress/Country/text()'), 21019),
                           'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationUr/ACAddress/Country/text()',
                           pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationUr/ACAddress/Country/text()'), 21019)
                           --     'Root/Participants/Participant['||to_char(i)||']/AdditionalPersonInfo/AdditionalInformationAc/DocumentIdentity/text()',
                           --       pkg_analytic.GetDescription(extractvalue(PXMLSCH,'Root/Participants/Participant['||to_char(i)||']/AdditionalPersonInfo/AdditionalInformationAc/DocumentIdentity/text()'),20100) ,
                           --     'Root/Participants/Participant['||to_char(i)||']/AdditionalPersonInfo/AdditionalInformationIp/DocumentIdentity/text()',
                           --       pkg_analytic.GetDescription(extractvalue(PXMLSCH,'Root/Participants/Participant['||to_char(i)||']/AdditionalPersonInfo/AdditionalInformationIp/DocumentIdentity/text()'),20100)

                           )
          into pXML
          from dual;
          PXMLSCH := pxml;
          select appendChildXML(PXMLSCH,
                                'Root/Participants/Participant[' || to_char(i) || ']/ParticipantsType',
                                XMLType('<Descr>' || pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/Participants/Participant[' || to_char(i) || ']/ParticipantsType/text()'), 10) || '</Descr>'))
          into pxml
          from dual;

          PXMLSCH := pxml;
          j       := 1;
          loop
            if (xmltype.existsNode(pxml, 'Root/Participants/Participant[' || to_char(i) || ']/CorrespondentBank/CorrespondentsInformations/CorrespondentInformation[' || to_char(j) || ']') = 1) then
              select updateXml(PXMLSCH,
                               'Root/Participants/Participant[' || to_char(i) || ']/CorrespondentBank/CorrespondentsInformations/CorrespondentInformation[' || to_char(j) || ']/BankAddress/text()',
                               pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/Participants/Participant[' || to_char(i) || ']/CorrespondentBank/CorrespondentsInformations/CorrespondentInformation[' || to_char(j) || ']/BankAddress/text()'),
                                                           21019))
              into pXML
              from dual;
            else
              exit;
            end if;
            PXMLSCH := pxml;
            j       := j + 1;
          end loop;
          j := 1;
          loop
            if (xmltype.existsNode(pxml, 'Root/Participants/Participant[' || to_char(i) || ']/Founders/Founder[' || to_char(j) || ']') = 1) then
              select updateXml(PXMLSCH,
                               'Root/Participants/Participant[' || to_char(i) || ']/Founders/Founder[' || to_char(j) || ']/MemberCommunityCityCode/text()',
                               pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/Participants/Participant[' || to_char(i) || ']/Founders/Founder[' || to_char(j) || ']/MemberCommunityCityCode/text()'), 21019))
              into pXML
              from dual;
            else
              exit;

            end if;
            PXMLSCH := pxml;
            j       := j + 1;
          end loop;
        else
          exit;
        end if;
        i := i + 1;
      end loop;
    exception
      when others then
        pXML := PXMLSCH;
    end;
    return pxml;
  end;

    function getouterannouncexml_3(pxmlsch1 xmltype) return xmltype is
    pxml    xmltype;
    pxmlsch xmltype;
    i       number default 1;
    j       number default 1;
    --i number
  begin
    begin
      select updatexml(pxmlsch1,
                       'Root/PersonalData/OrganisationOPF/text()',
                       pkg_analytic.getdescription(extractvalue(pxmlsch1, 'Root/PersonalData/OrganisationOPF/text()'), 41003),
                       'Root/MessageInformation/SuspicionsCriterians/text()',
                       pkg_analytic.getdescription(extractvalue(pxmlsch1, 'Root/MessageInformation/SuspicionsCriterians/text()'), 41012),
                       'Root/MessageInformation/DocumentType/text()',
                       pkg_analytic.getdescription(extractvalue(pxmlsch1, 'Root/MessageInformation/DocumentType/text()'), 21009),
                       'Root/MessageInformation/OperationStatusId/text()',
                       pkg_analytic.getdescription(extractvalue(pxmlsch1, 'Root/MessageInformation/OperationStatusId/text()'), 21010),

                       'Root/MessageInformation/ViewOperationId/text()',
                       pkg_analytic.getshortdescription(extractvalue(pxmlsch1, 'Root/MessageInformation/ViewOperationId/text()'), 41004),

                       'Root/MessageInformation/MerchTypes/text()',
                       pkg_analytic.getshortdescription(extractvalue(pxmlsch1, 'Root/MessageInformation/MerchTypes/text()'), 41055),

                       'Root/MessageInformation/EknpId/text()',
                       pkg_analytic.getshortdescription(extractvalue(pxmlsch1, 'Root/MessageInformation/EknpId/text()'), 41017),
                      'Root/MessageInformation/CurrencyCodeId/text()',
                       pkg_analytic.getshortdescription(extractvalue(pxmlsch1, 'Root/MessageInformation/CurrencyCodeId/text()'), 41018),
                      'Root/MessageInformation/DocOperationReason/text()',
                       pkg_analytic.getdescription(extractvalue(pxmlsch1, 'Root/MessageInformation/DocOperationReason/text()'), 41015),
                       'Root/MessageInformation/SuspicionFirst/text()',
                       pkg_analytic.getshortdescription(extractvalue(pxmlsch1, 'Root/MessageInformation/SuspicionFirst/text()'), 41008),
                       'Root/MessageInformation/SuspicionSecond/text()',
                       pkg_analytic.getshortdescription(extractvalue(pxmlsch1, 'Root/MessageInformation/SuspicionSecond/text()'), 41008),
                       'Root/MessageInformation/SuspicionThird/text()',
                      pkg_analytic.getshortdescription(extractvalue(pxmlsch1, 'Root/MessageInformation/SuspicionThird/text()'), 41008) --,
                     )
      into pxml
      from dual;
      pxmlsch := pxml;
      select appendchildxml(pxmlsch, 'Root/MessageInformation/ReasonFilingId', xmltype('<Descr>' || pkg_analytic.getdescription(extractvalue(pxmlsch, 'Root/MessageInformation/ReasonFilingId/text()'), 21011) || '</Descr>')) --
      into pxml
      from dual;
    exception
      when others then
        pxml := pxmlsch1;
    end;
    pxmlsch := pxml;
    begin
      loop
        if (xmltype.existsnode(pxml, 'Root/Participants/Participant[' || to_char(i) || ']') = 1) then
          select updatexml(pxmlsch,
                           'Root/Participants/Participant[' || to_char(i) || ']/MemberId/text()',
                           pkg_analytic.getdescription(extractvalue(pxmlsch, 'Root/Participants/Participant[' || to_char(i) || ']/MemberId/text()'), 21013),
                           'Root/Participants/Participant[' || to_char(i) || ']/ParticipantsView/text()',
                           pkg_analytic.getdescription(extractvalue(pxmlsch, 'Root/Participants/Participant[' || to_char(i) || ']/ParticipantsView/text()'), 41006),
                           'Root/Participants/Participant[' || to_char(i) || ']/IsClientSubject/text()',
                           pkg_analytic.getdescription(extractvalue(pxmlsch, 'Root/Participants/Participant[' || to_char(i) || ']/IsClientSubject/text()'), 21020),
                           'Root/Participants/Participant[' || to_char(i) || ']/MemberCommunityCityCode/text()',
                           pkg_analytic.GetCountryDescription(extractvalue(PXMLSCH, 'Root/Participants/Participant[' || to_char(i) || ']/MemberCommunityCityCode/text()')),
                           'Root/Participants/Participant[' || to_char(i) || ']/ForeignPerson/text()',
                           pkg_analytic.getdescription(extractvalue(pxmlsch, 'Root/Participants/Participant[' || to_char(i) || ']/ForeignPerson/text()'), 21014),
                           'Root/Participants/Participant[' || to_char(i) || ']/CorrespondentBank/BankOffshoreAddr/text()',
                           pkg_analytic.getshortdescription(extractvalue(pxmlsch, 'Root/Participants/Participant[' || to_char(i) || ']/CorrespondentBank/BankOffshoreAddr/text()'), 41071),
                           'Root/Participants/Participant[' || to_char(i) || ']/CorrespondentBank/BankAddress/text()',
                           pkg_analytic.GetCountryDescription(extractvalue(pxmlsch, 'Root/Participants/Participant[' || to_char(i) || ']/CorrespondentBank/BankAddress/text()')),
                           'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationAc/URAddress/Country/text()',
                           pkg_analytic.GetCountryDescription(extractvalue(pxmlsch, 'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationAc/URAddress/Country/text()')),
                           'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationAc/ACAddress/Country/text()',
                           pkg_analytic.GetCountryDescription(extractvalue(pxmlsch, 'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationAc/ACAddress/Country/text()')),
                           'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationIp/URAddress/Country/text()',
                           pkg_analytic.GetCountryDescription(extractvalue(pxmlsch, 'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationIp/URAddress/Country/text()')),
                           'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationIp/ACAddress/Country/text()',
                           pkg_analytic.GetCountryDescription(extractvalue(pxmlsch, 'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationIp/ACAddress/Country/text()')),
                           'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationUr/URAddress/Country/text()',
                           pkg_analytic.GetCountryDescription(extractvalue(pxmlsch, 'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationUr/URAddress/Country/text()')),
                           'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationUr/ACAddress/Country/text()',
                           pkg_analytic.GetCountryDescription(extractvalue(pxmlsch, 'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationUr/ACAddress/Country/text()')),



                           'Root/Participants/Participant[' || to_char(i) || ']/MoneyTransSys/text()',
                           pkg_analytic.getshortdescription(extractvalue(pxmlsch, 'Root/Participants/Participant[' || to_char(i) || ']/MoneyTransSys/text()'), 41070),
                           'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationUr/ParticipantOPF/text()',
                           pkg_analytic.getdescription(extractvalue(pxmlsch, 'Root/Participants/Participant[' || to_char(i) || ']/AdditionalPersonInfo/AdditionalInformationUr/ParticipantOPF/text()'), 41003),
                          'Root/Participants/Participant['||to_char(i)||']/AdditionalPersonInfo/AdditionalInformationAc/DocumentIdentity/text()',
                           pkg_analytic.GetDescription(extractvalue(PXMLSCH,'Root/Participants/Participant['||to_char(i)||']/AdditionalPersonInfo/AdditionalInformationAc/DocumentIdentity/text()'),40100)
                           --     'Root/Participants/Participant['||to_char(i)||']/AdditionalPersonInfo/AdditionalInformationIp/DocumentIdentity/text()',
                           --       pkg_analytic.GetDescription(extractvalue(PXMLSCH,'Root/Participants/Participant['||to_char(i)||']/AdditionalPersonInfo/AdditionalInformationIp/DocumentIdentity/text()'),40100)
                           )
          into pxml
          from dual;
          pxmlsch := pxml;
          select appendchildxml(pxmlsch,
                                'Root/Participants/Participant[' || to_char(i) || ']/ParticipantsType',
                                xmltype('<Descr>' || pkg_analytic.getdescription(extractvalue(pxmlsch, 'Root/Participants/Participant[' || to_char(i) || ']/ParticipantsType/text()'), 10) || '</Descr>'))
          into pxml
          from dual;
          pxmlsch := pxml;
          j       := 1;
          loop
            if (xmltype.existsnode(pxml, 'Root/Participants/Participant[' || to_char(i) || ']/CorrespondentBank/CorrespondentsInformations/CorrespondentInformation[' || to_char(j) || ']') = 1) then
              select updatexml(pxmlsch,
                               'Root/Participants/Participant[' || to_char(i) || ']/CorrespondentBank/CorrespondentsInformations/CorrespondentInformation[' || to_char(j) || ']/BankAddress/text()',
                               pkg_analytic.GetCountryDescription(extractvalue(pxmlsch, 'Root/Participants/Participant[' || to_char(i) || ']/CorrespondentBank/CorrespondentsInformations/CorrespondentInformation[' || to_char(j) || ']/BankAddress/text()')
                                                           ))
              into pxml
              from dual;
            else
              exit;
            end if;
            pxmlsch := pxml;
            j       := j + 1;
          end loop;
          j := 1;
          loop
            if (xmltype.existsnode(pxml, 'Root/Participants/Participant[' || to_char(i) || ']/Founders/Founder[' || to_char(j) || ']') = 1) then
              select updatexml(pxmlsch,
                               'Root/Participants/Participant[' || to_char(i) || ']/Founders/Founder[' || to_char(j) || ']/MemberCommunityCityCode/text()',
                               pkg_analytic.GetCountryDescription(extractvalue(pxmlsch, 'Root/Participants/Participant[' || to_char(i) || ']/Founders/Founder[' || to_char(j) || ']/MemberCommunityCityCode/text()')),
                               'Root/Participants/Participant[' || to_char(i) || ']/Founders/Founder[' || to_char(j) || ']/FounderOPF/text()',
                               pkg_analytic.getshortdescription(extractvalue(pxmlsch, 'Root/Participants/Participant[' || to_char(i) || ']/Founders/Founder[' || to_char(j) || ']/FounderOPF/text()'), 41003))
              into pxml
              from dual;
            else
              exit;
            end if;
            pxmlsch := pxml;
            j       := j + 1;
          end loop;
        else
          exit;
        end if;
        i := i + 1;
      end loop;
    exception
      when others then
        pxml := pxmlsch;
    end;
    return pxml;
  end;

  function GetOuterAnnounceXML(PXMLSCH1 xmltype,
                               pversion number) return xmltype is
  begin
    case pversion
      when 2 then
        return GetOuterAnnounceXML_2(PXMLSCH1);
      else
        return GetOuterAnnounceXML_3(PXMLSCH1);
    end case;
  end;

end PKG_MESS_CFM_2;
/

/*
  ****** Инструкции для SQLPlus (обязательно) ******
*/
