/*
* Автор: Галкин А.И.
* Дата: 2014-03-25
* Задача: http://jira.softage.ru/jira/browse/SDFO-275
* Назначение: Доработка механизма ОКЕД
* Изменяются объекты:
		drop table OKED2AC
		update package PKG_ANALYTIC
		update package PKG_MESS_CFM_2_1
		update package PKG_MESSAGES_XML
*/

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

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

/

DECLARE
   v_column_exists   NUMBER := 0;
BEGIN
   SELECT COUNT (*)
     INTO v_column_exists
     FROM USER_TABLES ut
     WHERE ut.table_name = 'OKED2AC';

   IF (v_column_exists > 0)
   THEN
      EXECUTE IMMEDIATE
         'drop table OKED2AC';
   END IF;
END;

/

CREATE OR REPLACE PACKAGE KFM."PKG_ANALYTIC" as
  function GetAnalyticCodesInfo(pAnalyticType  in number,
                                pCodesListType in NUMBER,
                                pResCursor     out sys_refcursor)
    return number;
  /* Вывести информацию о кодах для заданного типа,

  Входные параметры: - pAnalyticType - тип аналитической информации
   pCodesListType - тип списка:
                  0 - закрытые
                  1 - открытые
                  2 - все
  Выходные параметры: - pResCursor - курсор структуры
     AnalyticCode    NUMBER,   - код справочника;
     AnalyticName    VARCHAR2, - наименование;
     ShortName       VARCHAR2, - краткое наименование;
     RelationCode1   NUMBER,   - код справочника связи;
     isenabled       number    - показатель закрыто (0) или нет (1)
  Возврат:
     -1;--Общая ошибка
     0;--Успех
  */

  function GetAnalyticTypesInfo(pTypesListType in NUMBER,
                                pResCursor     out sys_refcursor)
    return number;
  /* Вывести информацию о кодах для заданного типа,

  Входные параметры: -
   pTypesListType - тип списка:
                  0 - закрытые
                  1 - открытые
                  2 - все
  Выходные параметры: - pResCursor - курсор структуры
     AnalyticType    NUMBER,   - тип справочника;
     AnalyticName    VARCHAR2, - наименование;
     RelationCode1   NUMBER,   - код справочника связи;
  Возврат:
     -1;--Общая ошибка
     0;--Успех
  */

  function AddAnalyticCode(pAnalyticType_in  in number,
                           pAnalyticCode_in  in number,
                           pAnalyticName_in  in varchar2,
                           pShortName_in     in varchar2,
                           pRelationType1_in in number) return number;
  /* Добавить новый код для заданного типа
  (установить alterdate в системную, яltermode в 0)
  Входные параметры: - pAnalyticType_in - тип справочника,
                     - pAnalyticCode_in - код справочника,
                     - pAnalyticName_in - наименование,
                     - pShortName_in in - краткое наименование,
                     - pRelationType1_in - код справочника связи.
  Возврат:
     -408;--повторная запись для заданного кода аналитической информации
     -409;--нет такого типа аналитической информации
     -412;--обнаружены одинаковые типы аналитической информации
     -1;--Общая ошибка
     0;--Успех
  */

  function AddAnalyticType(pAnalyticType_in  in number,
                           pAnalyticName_in  in varchar2,
                           pRelationType1_in in number) return number;
  /* Добавить новый тип (установить alterdate в системную, яltermode в 0)
  Входные параметры: - pAnalyticType_in - тип справочника,
                     - pAnalyticName_in - наименование,
                     - pRelationType1_in - тип связанного справочника.
  Возврат:
     -1;--Общая ошибка
     0;--Успех
  */

  function ChangeAnalyticCode(pAnalyticType_in  in number,
                              pAnalyticCode_in  in number,
                              pAnalyticName_in  in varchar2,
                              pShortName_in     in varchar2,
                              pRelationType1_in in number) return number;
  /* Изменить уже существующий код для заданного типа
  (установить alterdate в системную, яltermode в 1)
  Входные параметры: - pAnalyticType_in - тип справочника,
                     - pAnalyticCode_in - код справочника,
                     - pAnalyticName_in - наименование,
                     - pShortName_in in - краткое наименование,
                     - pRelationType1_in - код справочника связи.
  Возврат:
     -409;--нет такого типа аналитической информации
     -410;--нет такого кода аналитической информации
     -412;--обнаружены одинаковые типы аналитической информации
     -413;--обнаружены одинаковые коды аналитической информации
     -1;--Общая ошибка
     0;--Успех
  */

  function ChangeAnalyticType(pAnalyticType_in  in number,
                              pAnalyticName_in  in varchar2,
                              pRelationType1_in in number) return number;
  /* Изменить уже существующий тип (установить alterdate в системную,
                         altermode в 1)
  Входные параметры: - pAnalyticType_in - тип справочника,
                     - pAnalyticName_in - наименование,
                     - pRelationType1_in - тип связанного справочника.
  Возврат:
     -409;--нет такого типа аналитической информации
     -412;--обнаружены одинаковые типы аналитической информации
     -1;--Общая ошибка
     0;--Успех
  */

  function DeleteAnalyticCode(pAnalyticType_in in number,
                              pAnalyticCode_in in number) return number;
  /* Сделать код скрытым (установить alterdate в системную,
                         altermode в -1)
  Входные параметры: - pAnalyticType_in - тип справочника,
                     - pAnalyticCode_in - код справочника.
  Возврат:
     -1;--Общая ошибка
     0;--Успех
  */

  function DeleteAnalyticType(pAnalyticType_in in number) return number;
  /* Сделать тип скрытым (установить alterdate в системную,
                         altermode в -1)
  Входные параметры: - pAnalyticType_in - тип справочника.
          Примечание: все коды, имеющие соответствующий тип в таблице
          ANALYTICCODES таккже станут скрытыми
  Возврат:
     -1;--Общая ошибка
     0;--Успех
  */
  function OpenAnalyticType(pAnalyticType in number) return number;
  /* Сделать открытым тип
  */
  function OpenAnalyticCode(pAnalyticType in number,
                            pAnalyticCode in number) return number;
  /* Сделать открытым код
  */

  function containsCode1(pAnalyticType_in in number,
                         pAnalyticCode_in in varchar2) return number;

  function containsCode(pAnalyticType_in in number,
                        pAnalyticCode_in in number) return boolean;
  /* Проверить существует ли код, имеющий заданный тип
  (код существует, даже если он является скрытым)
     (если типа не существует, то считается, что кода также не существует)
  Входные параметры: - pAnalyticType_in - тип справочника,
                     - pAnalyticCode_in - код справочника.
  Возврат:
     true;--содержит заданный код аналитической информации
     false;--не содержит заданный код аналитической информации,
                либо содержит более одного кода,
                либо не содержит тип,
                либо содержит более одного типа
     null;--Общая ошибка
  */

  function containsType(pAnalyticType_in in number) return boolean;
  /* Проверить существует ли тип (тип существует, даже если он является скрытым)
  Входные параметры: - pAnalyticType_in - тип справочника.
  Возврат:
     true;--содержит заданный тип аналитической информации
     false;--не содержит заданный тип аналитической информации,
                либо содержит более одного
     null;--Общая ошибка
  */

  function GetFullAnalyticInfo(pMode_in char, pResCursor out sys_refcursor)
    return number;
  /* Вывести полную информацию из справочника,
  НЕ включая скрытые коды
  Входные параметры: - pMode_in - режим объединения таблиц, допустимые значения:
                     'l' - "left join", в курсор будут обязательно добавлены все данные из
                           таблицы ANALYTICTYPES, даже если соответствующие типы не используются
                           в таблице ANALYTICCODES
                     'r' - "right join", в курсор будут обязательно добавлены все данные из
                           таблицы ANALYTICCODES, даже если соответствующие типы не используются
                           в таблице ANALYTICTYPES (ситуация возникновения типа, содержащегося в
                           таблице ANALYTICCODES, но не содержащегося в ANALYTICTYPES не возможна)
                     'f' - "full join", в курсор будут обязательно добавлены все данные
                     'i' - "inner join", в курсор будут обязательно добавлены данные,
                           содержащиеся в обеих таблицах
                           Примечание: использование параметров 'i' и 'r' - эквивалентны,
                                       'l' и 'f' - также эквивалентны.
  Выходные параметры: - pResCursor - курсор структуры
     AnalyticName     VARCHAR2     - наименование в таблице ANALYTICTYPES,
     AnalyticType     NUMBER       - тип справочника в таблице ANALYTICTYPES,
     AnalyticType     NUMBER       - тип справочника в таблице ANALYTICCODES,
     AnalyticCode     NUMBER       - код справочника в таблице ANALYTICCODES,
     AnalyticName     VARCHAR2     - наименование в таблице ANALYTICCODES.
  Возврат:
      -411;--неправильно введен параметр режима объединения таблиц
      -1;--Общая ошибка
      0;--Успех
  */

  function GetDescription(pCode in number, pType in number) return varchar2;
  /*Получить описание по коду и типу*/

  function GetShortDescription(pCode in number, pType in number)
    return varchar2;
  /*Получить краткое описание по коду и типу*/

  function GetFullAnalyticInfoWithHiden(pMode_in   char,
                                        pResCursor out sys_refcursor)
    return number;

  /* Вывести полную информацию из справочника,
  включая скрытые коды
  Входные параметры: - pMode_in - режим объединения таблиц, допустимые значения:
                     'l' - "left join", в курсор будут обязательно добавлены все данные из
                           таблицы ANALYTICTYPES, даже если соответствующие типы не используются
                           в таблице ANALYTICCODES
                     'r' - "right join", в курсор будут обязательно добавлены все данные из
                           таблицы ANALYTICCODES, даже если соответствующие типы не используются
                           в таблице ANALYTICTYPES (ситуация возникновения типа, содержащегося в
                           таблице ANALYTICCODES, но не содержащегося в ANALYTICTYPES не возможна)
                     'f' - "full join", в курсор будут обязательно добавлены все данные
                     'i' - "inner join", в курсор будут обязательно добавлены данные,
                           содержащиеся в обеих таблицах
                           Примечание: использование параметров 'i' и 'r' - эквивалентны,
                                       'l' и 'f' - также эквивалентны.
  Выходные параметры: - pResCursor - курсор структуры
     AnalyticName     VARCHAR2     - наименование в таблице ANALYTICTYPES,
     AnalyticType     NUMBER       - тип справочника в таблице ANALYTICTYPES,
     AnalyticType     NUMBER       - тип справочника в таблице ANALYTICCODES,
     AnalyticCode     NUMBER       - код справочника в таблице ANALYTICCODES,
     AnalyticName     VARCHAR2     - наименование в таблице ANALYTICCODES.
  Возврат:
      -411;--неправильно введен параметр режима объединения таблиц
      -1;--Общая ошибка
      0;--Успех
  */
  --function DeleteAnalyticCode(pAnalyticType_in in number,
  --                            pAnalyticCode_in in number) return number;
  /* Удалить код
  Входные параметры: - pAnalyticType_in - тип справочника,
                     - pAnalyticCode_in - код справочника.
  Возврат:
     -1;--Общая ошибка
     0;--Успех
  Примечание: не рекомендуется использовать данную функцию, желательно
  использование функции HideAnalyticCode
  */

  -- function DeleteAnalyticType(pAnalyticType_in in number) return number;
  /* Удалить тип
  Входные параметры: - pAnalyticType_in - тип справочника.
          Примечание: также будут удалены все записи из таблицы ANALYTICODES,
          соответствующие заданному типу
  Возврат:
     -1;--Общая ошибка
     0;--Успех
  Примечание: не рекомендуется использовать данную функцию, желательно
  использование функции HideAnalyticType
  */
  function GetMessName(messtypeid number) return nvarchar2;
  function GetCodeDescription(pCodeVal  number,
                              pDataCode number,
                              pVersion  number) return varchar2;
  function GetShortCodeDescription(pCodeVal  number,
                                   pDataCode number,
                                   pVersion  number) return varchar2;

  function GetRelationCodesInfo(pRelationAnalyticType in number,
                                pRelationAnalyticCode in number,
                                pCodesListType        in number,
                                pResCursor            out sys_refcursor,
                                pLanguageCode         in number default 1)
    return number;
  /* Вывести информацию о кодах для заданного типа,

  Входные параметры: -
     pRelationAnalyticType    NUMBER,   - код типа справочника, список данных которого необходимо получить;
     pRelationAnalyticCode    NUMBER,   - код значения справочника связи, являющегося родителем (RelationCode1);
     pCodesListType - тип списка:
                  0 - закрытые
                  1 - открытые
                  2 - все
  Выходные параметры: - pResCursor - курсор структуры
     AnalyticName       NVARCHAR2, - полное наименование;
     ShortName        NVARCHAR2, - краткое наименование;
     RelationCode1    NUMBER,   - код значения справочника связи;
     isenabled        number    - показатель закрыто (0) или нет (1)
  Возврат:
     -1;--Общая ошибка
     0;--Успех
  */
  /*Получить список кодов банков по начальной  части кода
  pBankSwiftCode VARCHAR2 - начало кода
  pBankSwiftCodesList - курсор структуры
     BankSwiftCode       NVARCHAR2, - наименование
  */
  function GetListOfSwiftCodes(pBankSwiftCode      in varchar2,
                               pBankSwiftCodesList out sys_refcursor)
    return number;
  /*Получить параметры банка по вбыранному коду
  pBankSwiftCode VARCHAR2     - код банка
  pBankSwiftName - NVARCHAR2, - наименование
  pBankSwiftCountry   NUMBER, - страна
  */
  function GetSwiftCodeParams(pBankSwiftCode    in varchar2,
                              pBankSwiftName    out varchar2,
                              pBankSwiftCountry out number,
                              pCity             out varchar2) return number;
  /*
  Получение наименование страны по коду аналитики или 2-х символьной аббревиатуре
  */
   function GetCountryDescription(pCode in varchar2) return varchar2;

end PKG_Analytic;
/

CREATE OR REPLACE PUBLIC SYNONYM PKG_ANALYTIC FOR KFM.PKG_ANALYTIC
/


GRANT EXECUTE ON KFM.PKG_ANALYTIC TO KFM_ROLE
/

CREATE OR REPLACE PACKAGE BODY KFM."PKG_ANALYTIC" as

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

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

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

    end case;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

end PKG_Analytic;
/

CREATE OR REPLACE PUBLIC SYNONYM PKG_ANALYTIC FOR KFM.PKG_ANALYTIC
/


GRANT EXECUTE ON KFM.PKG_ANALYTIC TO KFM_ROLE
/

CREATE OR REPLACE PACKAGE KFM."PKG_MESS_CFM_2_1" AS
  --
  function mess_cfm_2_empty_xml return xmltype;
  --
  function mess_cfm_2_xml(p_MESS_CFM_ID in NUMBER) return xmltype;
  --данные из таблиц mess_cfm
  function personal_data_2_xml_v2(p_MESS_CFM_ID in NUMBER) return xmltype;
  --данные о паспорте из таблицы mess_cfm, данные о пользователе из таблицы users
  function personal_data_2_xml_v3(p_MESS_CFM_ID in NUMBER) return xmltype;
  --
  function message_information_2_xml_v1 return xmltype;
  --
  function message_information_2_xml_v2(p_MESS_CFM_ID in NUMBER) return xmltype;
  --
  function refers_2_xml(p_MESS_CFM_ID in NUMBER) return xmltype;
  --
  function participants_2_xml(p_MESS_CFM_ID in NUMBER) return xmltype;
  --
  function correspondents_info_2_xml(p_MESS_CFM_ID in NUMBER,
                                     p_MEMBER_ID   in NUMBER) return xmltype;
  --

    function OKED_2_xml(p_MESS_CFM_ID in NUMBER,
                                     p_MEMBER_ID   in NUMBER) return xmltype;
    --

    function OKSMT_2_xml(p_AnCode in number, FieldName in varchar2) return xmltype;
  --

  function founders_2_xml(p_MESS_CFM_ID in NUMBER,
                          p_MEMBER_ID   in NUMBER) return xmltype;
  --
  function additional_person_info_2_xml(p_MESS_CFM_ID in NUMBER,
                                        p_MEMBER_ID   in NUMBER) return xmltype;
  --
END PKG_MESS_CFM_2_1;
/

CREATE OR REPLACE PUBLIC SYNONYM PKG_MESS_CFM_2_1 FOR KFM.PKG_MESS_CFM_2_1
/


GRANT EXECUTE ON KFM.PKG_MESS_CFM_2_1 TO KFM_ROLE
/

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    return v_result;
  end;
  --

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

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

    return v_result;
  end;
  --

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

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

  begin
    v_result := null;
   SELECT XMLElement("MemberCommunityCityCode",oa.OKSMTCODE) AS xmltype
    INTO v_result
    FROM OKSMT2AC oa
    WHERE oa.ancode = p_AnCode;
   return v_result;
 exception
    when others then
         return null; --Ошибка разбора XML
  end;
  --

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

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

    return v_result;
  end;
  --


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

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

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

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

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

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

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

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

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

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

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

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

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

    return v_result;
  end;
  --
END PKG_MESS_CFM_2_1;
/

CREATE OR REPLACE PUBLIC SYNONYM PKG_MESS_CFM_2_1 FOR KFM.PKG_MESS_CFM_2_1
/


GRANT EXECUTE ON KFM.PKG_MESS_CFM_2_1 TO KFM_ROLE
/


CREATE OR REPLACE PACKAGE KFM."PKG_MESSAGES_XML" as
  function Create_XMLFromClob(pMessClob in CLOB,
                              pXML      out xmltype) return number;
  function Get_MessagePrefix(pXML       in xmltype,
                             pPrefixXml out varchar2) return number;
  function Get_MessageVersion(pXML       in xmltype,
                              pVersion   out varchar2,
                              pPrefixXml out varchar2) return number;
  function Get_FieldFromXML(pXML        in xmltype,
                            pFieldName  in varchar2,
                            pFieldValue out varchar2) return number;

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

  function GetParsedXML(xmldoc in clob) return xmldom.DOMnode;
  function GetSingleVal(xmlnode in xmldom.DOMNode,
                        pattern in varchar2) return varchar2;
  function GetSingleDateVal(xmlnode in xmldom.DOMNode,
                            pattern in varchar2) return date;
  function GetSingleDateTimeVal(xmlnode in xmldom.DOMNode,
                                pattern in varchar2) return date;
  function GetNodesList(xmlnode in xmldom.DOMNode,
                        pattern in varchar2) return xmldom.DOMNodeList;

  function GetSingleOKEDVal(v_OKEDCODE in varchar2,pmessid in number) return number;
  -- Iieo?aai 5 cia?iue eia IEYA
  function GetSingleOKEDVal5Code(v_okedcode in varchar2) return varchar2;
  function GetSingleOKSMTVal(v_OKSMTCODE in varchar2) return number;
  -- Iieo?aai ec eiaa no?aiu aaa?aaeaoo?o
  function GetSingleOKSMTAbbr(v_OKSMTCODE in varchar2) return varchar2;

end PKG_MESSAGES_XML;
/

CREATE OR REPLACE PUBLIC SYNONYM PKG_MESSAGES_XML FOR KFM.PKG_MESSAGES_XML
/


GRANT EXECUTE ON KFM.PKG_MESSAGES_XML TO KFM_ROLE
/


CREATE OR REPLACE PACKAGE BODY KFM."PKG_MESSAGES_XML" as

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

    pXML := XMLTYPE.createxml(pMessClob);

    Return(Result);

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

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

    vRootXml := pXML.getrootelement();

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

    Return(Result);

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

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

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

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

    Return(Result);

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

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

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

    vMessType  integer;
    vVersionID integer;

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

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

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

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

    vrootname := xmldom.getTagName(elem);

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

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

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

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

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

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

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

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

    vRootXml := vXML.getrootelement();

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

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

    Return(Result);

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

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

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

  function GetSingleOKSMTVal(v_oksmtcode in varchar2) return number is
    v_result number default 0;
  begin
   select ancode
    into v_result
    from OKSMT2AC
    where oksmtcode = v_OKSMTCODE;
   return v_result;
  exception
    when others then
         return null; --Ioeaea ?acai?a XML
  end;

 function GetSingleOKSMTAbbr(v_OKSMTCODE in varchar2) return varchar2 is
   v_result varchar2(2);
  begin
   select oks.oksmtcode
    into v_result
    from OKSMT2AC oks
    where oks.ancode = v_OKSMTCODE;
   return v_result;
  exception
    when others then
         return null; --Ioeaea ?acai?a XML
  end;

end PKG_MESSAGES_XML;
/

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