/*
* Автор: Борисов Д.Г.
* Дата: 2013-05-29
* Задача: http://jira.softage.ru/jira/browse/SDFO-1
* Описание: Добавлен новый тег "CurrencyName" в XML информационного сообщения и его обработка на стороне OFM.Client
*/

-- Update Get_MessageShortData
-- Update Get_MessagesList
-- Update Get_MessagesListForPeriod
-- Update Get_FilteredMessagesList
-- Update Get_FixedMessagesList
-- Update Get_RelationMessagesList
-- Update Get_MessagesListByParentID
-- 

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

declare
    filename      varchar2(256) := 'sfm.database-update.2013-05-29.AddCurrencyNameTag.sql';
    purpose       varchar2(4000) := 'Добавлен новый тег "CurrencyName" в XML информационного сообщения и его обработка на стороне OFM.Client';
    script_exists number := 0;
begin

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

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

    /*
  ****** Begin update script ******
  */
  
  /*
  ****** End update script ******
  */
    commit;

exception
    when others then
        rollback;
        raise;
end;
/

CREATE OR REPLACE PACKAGE BODY "PKG_MESSAGES_CFM" as

  function Create_XML(pMessID in number,
                      pXML    out xmltype) return number is
    Result     number;
    vXMLString CLOB;
  begin
    Result := 0;

    select mc_xml.mess_xml
    into vXMLString
    from mess_cfm_xml mc_xml
    where mc_xml.mess_cfm_id = pMessID;

    Result := PKG_MESSAGES_XML.Create_XMLFromClob(vXMLString, pXML);

    Return(Result); ---300

  EXCEPTION
    when no_data_found then
      result := -1000;
      Return(Result);
    WHEN OTHERS THEN
      Result := -305; --Неверный XML документ
      Return(Result);
  end;

  function Get_FieldAsVarchar(pMessID    in number,
                              pFieldName in varchar2) return varchar2 is
    Result varchar2(5000);
    pXML   xmltype;
    Res    number;
  begin
    Result := NULL;

    Res := Create_XML(pMessID, pXML);
    if Res <> 0 then
      return Res;
    end if;
    Res := PKG_MESSAGES_XML.Get_FieldFromXML(pXML, pFieldName, Result);
    if Res <> 0 then
      null;
    end if;
    Return(Result);

  EXCEPTION
    WHEN OTHERS THEN
      Result := null;
      Return(Result);
  end;

  function Get_FieldAsNumber(pMessID    in number,
                             pFieldName in varchar2) return number is
    Result         number;
    pFieldValueStr varchar2(50 char);
  begin
    Result := 0;

    pFieldValueStr := Get_FieldAsVarchar(pMessID, pFieldName);

    Result := to_number(pFieldValueStr);

    Return(Result);

  EXCEPTION
    WHEN OTHERS THEN
      Result := NULL;
      Return(Result);
  end;

  function Get_FieldAsDate(pMessID    in number,
                           pFieldName in varchar2) return date is
    Result         date;
    pFieldValueStr varchar2(50 char);
  begin
    Result := null;

    pFieldValueStr := Get_FieldAsVarchar(pMessID, pFieldName);

    Result := to_date(pFieldValueStr, 'YYYY-MM-DD');

    Return(Result);

  EXCEPTION
    WHEN OTHERS THEN
      Result := NULL;
      Return(Result);
  end;

  function RequestCounterLastDate(pLastDate out date) return number as
  begin
    return pkg_rqst_cntr_cfm.RequestCounterLastDate(pLastDate);
  end;

  function RequestCounterReport(pBeginDate in date,
                                pEndDate   in date,
                                pMessID    out number) return number as
    v_result number default 0;
  begin
    return pkg_rqst_cntr_cfm.RequestCounterReport(pBeginDate, pEndDate, pMessID);
  end;

  function GetTempMessNumb return number is
    tempval number;
  begin
    select messnumb_temp.nextval
    into tempval
    from dual;
    return to_number(to_char(sysdate, 'mmdd') || lpad(to_char(tempval), 5, '0'));
  end;

  function GetMessSignedData(pMessID   in number,
                             vsender   out varchar2,
                             vreceiver out varchar2) return number is
    result number default 0;
  begin
    select sender,
           receiver
    into vsender,
         vreceiver
    from mess_cfm
    where mess_cfm_id = pMessID;
    return result;

  exception
    when no_data_found then
      return - 315;
  end;

  procedure UpdateMessNumber(pMessID number,
                             pMessIn clob) is
    pNumber number;
  begin
    select extractvalue(xmltype(pMessIn), '/ExportData/SignedData/FormNumber/text()')
    into pNumber
    from dual;
    --Исправлено 05.01.2012
    if pNumber is not null then
      update mess_cfm_xml
      set mess_xml = updatexml(xmltype(mess_xml),'/Root/MessageInformation/MessageNumber/text()', pNumber).getclobval()
      where mess_cfm_id = pMessID;

      update mess_cfm_announce_info
      set mess_number = pNumber
      where mess_cfm_id = pMessID;

      update mess_cfm_exec
      set mess_number = pNumber
      where mess_cfm_id = pMessID;
    end if;
  end;


  --наоборот, при отправке
  function GetSenderFromParent(pparentid in number,
                               vSender   out varchar2,
                               vReceiver out varchar2) return number is
    result number default 0;
  begin
    if (pparentid = 0 or pparentid is null) then
      return result;
    else
      select Receiver,
             Sender
      into vSender,
           vReceiver
      from mess_cfm
      where mess_cfm_id = pparentid;
    end if;
    return result;
  exception
    when no_data_found then
      return result;
  end;

  function GetSenderFromMessage(pMessID   in number,
                                vSender   out varchar2,
                                vReceiver out varchar2,
                                vDate     out date) return number is
    result number default 0;
  begin
    if (pMessID = 0 or pMessID is null) then
      return result;
    else
      select Sender,
             Receiver,
             send_date
      into vSender,
           vReceiver,
           vdate
      from mess_cfm
      where mess_cfm_id = pMessID;
    end if;
    return result;
  exception
    when no_data_found then
      return result;
  end;

  function Get_CheckXML(pMessID   in number,
                        pCheckXML out CLOB) return number is
    Result number default 0;
    vDocID varchar2(50 char);
    vcount number default 0;
  begin

    --проверить тип, можно ли по нему отправлять квитанцию?
    select count(*)
    into vcount
    from mess_cfm
    where mess_cfm_id = pMessID;
    if vcount = 0 then
      return - 1000;
    end if;
    vDocID := get_fieldasvarchar(pMessID, 'DocumentUniqueIdentifier');

    pCheckXML := '<?xml version="1.0" encoding="UTF-16"?>
               <Check>
                   <Version>5A151B65-0842-4FAA-99BA-F51364F8DDA6</Version>
                   <DocumentUniqueIdentifier>' || sys_guid() || '</DocumentUniqueIdentifier>
                   <OriginalDocumentGuid>' || vDocID || '</OriginalDocumentGuid>
                   <ErrorCode>0</ErrorCode>
                   <ErrorName>Без ошибок</ErrorName>
               </Check>';
    Return(Result);

    --было 4 +R1
  exception
    when NO_DATA_FOUND then
      Return(-1000);
    when others then

      return - 1007; --Ошибка формирования квитанции
  end;

  function Create_Ticket2NO(pMessID     in number,
                            pErrorCode  in number,
                            pAnswerText in varchar2,
                            pCheckXML   out CLOB) return number is
    Result           number;
    vDocID           varchar2(50 char);
    pstatus          number;
    vcount           number;
    ErrorDescription varchar2(3000 char);
    vErrorCode       varchar2(3000 char);

  begin
    vErrorCode := pErrorCode;
    Result     := 0;
    select count(*)
    into vcount
    from mess_cfm
    where mess_cfm_id = pMessID;
    if vcount = 0 then
      return - 1000;
    end if;
    vDocID := get_fieldasvarchar(pMessID, 'DocumentUniqueIdentifier');

    pkg_main.GetError(pErrorCode, vErrorCode, ErrorDescription, pstatus);

    select '<Root>
            <Version>F5982B4F-932C-44EA-BB61-78B0C3218DF3</Version>
            <DocumentUniqueIdentifier>' || sys_guid() || '</DocumentUniqueIdentifier>
            <OriginalDocumentGuid>' || vDocID || '</OriginalDocumentGuid>
            <ErrorCode>' || pErrorCode || '</ErrorCode>
            <ErrorName>' || pAnswerText || ErrorDescription || '</ErrorName>
          </Root>'
    into pCheckXML
    from mess_cfm mc
    where mc.mess_cfm_id = pMessID;

    Return(Result);

  exception
    when NO_DATA_FOUND then
      Return(-1000);
    when others then

      return - 1007; --Ошибка формирования квитанции
  end;

  function Get_AutoErrorCheckXML(pMessID   in number,
                                 pCheckXML out CLOB) return number is
    Result           number default 0;
    vDocID           varchar2(50 char);
    vcount           number default 0;
    errorname        varchar2(4000 char);
    errordescription varchar2(4000 char);
    pstatus          number default 0;
  begin


    --проверить тип, можно ли по нему отправлять квитанцию?
    select count(*)
    into vcount
    from mess_cfm
    where mess_cfm_id = pMessID;
    if vcount = 0 then
      return - 1000;
    end if;

    vDocID := get_fieldasvarchar(pMessID, 'DocumentUniqueIdentifier');

    select int_err_code
    into vcount
    from mess_cfm
    where mess_cfm_id = pMessID;
    pkg_main.GetError(vcount, ErrorName, ErrorDescription, pstatus);

    pCheckXML := '<?xml version="1.0" encoding="UTF-16"?>
               <Check>
                   <Version>F5982B4F-932C-44EA-BB61-78B0C3218DF3</Version>
                   <DocumentUniqueIdentifier>' || sys_guid() || '</DocumentUniqueIdentifier>
                   <OriginalDocumentGuid>' || vDocID || '</OriginalDocumentGuid>
                   <ErrorCode>' || vcount || '</ErrorCode>
                   <ErrorName>' || ErrorName || '</ErrorName>
               </Check>';

    Return(Result);

    --было 4 +R1
  exception
    when NO_DATA_FOUND then
      Return(-1000);
    when others then

      return - 1007; --Ошибка формирования квитанции
  end;

  function Save_Message(pMessXML            in CLOB,
                        pMessID             out number,
                        pCheckXML           out CLOB,
                        pError              in number := -1,
                        pValidationComplete in number := -1,
                        pValidationResult   in clob := null) return number is
    result          number default 0;
    vCount          number default 0;

    vRes            number default 0; -- Временный результат промежуточных проверок
    vMess_cfm       mess_cfm%rowtype; -- Строка ключевой информации

    vMessId         number; -- ID связанного сообщения
    vMessageVersion varchar2(50 char); -- Версия сообщения (из XML)
    vMessageGuid    varchar2(50 char); -- Guid сообщения (из XML)
    vCFMMessageDoc  varchar2(50 char); -- Версия оригинального сообщения (из XML)
    vXML            xmltype;

    vRootNode       clob; -- Сообщение без пакета
    vSignature      clob; -- ЭЦП
    vExistCover     number; -- Флаг наличия конверта
    pVersionId      number; -- ID версии сообщения
  begin

    --ЭЦП

    result := pkg_mess_cfm_1.ExtractSignatureData(pMessXML, vSignature);
    if Result <> 0 then
      return Result;
    end if;


    --Данные отправителя и получателя
    result := pkg_mess_cfm_1.ExtractMessInSignedData(pMessXML, vExistCover, vMess_cfm.Sender, vMess_cfm.Receiver, vMess_cfm.Send_Date);
    if Result <> 0 then
      return Result;
    end if;


    --Распаковка из конверта
    result := pkg_messages_xml.Get_RootNodeFromXML(pMessXML, vRootNode);
    if result <> 0 then
      return result; -- -305 или -307 (not valid или узел не найден)
    end if;

    vXML := XMLTYPE.createxml(vRootNode); --Главная ветка


    --Преобразование, проверка и сохранение версии документа
    Result := pkg_messages_xml.Get_FieldFromXML(vXML, 'Version', vMessageVersion);
    if Result <> 0 then
      return Result;
    end if;
    vres := pkg_common.GetFormattedGuid(vMessageVersion, vMess_cfm.Mess_Version);
    if vres <> 0 then

      return - 1301; -- Неверный формат идентификационного номера версии документа
    else
      begin
        select mv.mess_type,
               versionid
        into vMess_cfm.Mess_Type,

             pVersionId --Тип сообщения
        from messages_versions mv
        where mv.versioncode = vMess_cfm.Mess_Version;
      exception
        when others then

          return - 306; --Неверная версия сообщения. Сообщение не сохранено.
          vMess_cfm.Mess_Type := -1;
      end;
    end if;


    --Преобразование, проверка и сохранение GUID документа
    Result := pkg_messages_xml.Get_FieldFromXML(vXML, 'DocumentUniqueIdentifier', vMess_cfm.Mess_Docid);
    if Result <> 0 then
      return Result;
    end if;
    vres := pkg_common.GetFormattedGuid(vMess_cfm.Mess_Docid, vMess_cfm.Mess_Form_Guid);
    if vres <> 0 then

      return - 1302; --  Неверный формат идентификационного номера документа
    else
      select count(*)
      into vcount
      from mess_cfm
      where Mess_Form_Guid = vMess_cfm.Mess_Form_Guid;
      if vcount > 0 then

        return - 1003; --Сообщение с данным уникальным идентификатором  уже находится в базе
      end if;
    end if;

    --Преобразование, проверка и сохранение GUID оригинального документа
    if vMess_cfm.Mess_Type in (2, 3, 4, 5, 12, 13, 14) then
      Result := pkg_messages_xml.Get_FieldFromXML(vXML, 'OriginalDocumentGuid', vCFMMessageDoc);
      vres   := pkg_common.GetFormattedGuid(vCFMMessageDoc, vMessageGuid);
      if vres <> 0 then

        return - 1303; --  Неверный формат идентификационного номера документа
      else
        begin
          select mc.mess_cfm_id
          into vMess_cfm.Mess_Parentid --Идентификатор родительской записи
          from mess_cfm mc
          where mc.Mess_Form_Guid = vMessageGuid

          and rownum = 1; --Проверить когда такое возможно!
        exception
          when others then
            if vMess_cfm.Mess_Type <> 4 then

              return - 1008; --Не найдено изначальное сообщение, по которому пришло новое сообщение
            else
              vMess_cfm.Mess_Parentid := -1;

              result                  := -1009; --Сообщение сохранено
            end if;
        end;
      end if;
    end if;

    -- назначить отправителя и получателя в случае отправки сформированных откументов
    if vExistCover = 0 then
      result := GetSenderFromParent(vMess_cfm.Mess_Parentid, vMess_cfm.Sender, vMess_cfm.Receiver);
    end if;
    --

    --Сохранение в основной таблице
    vMess_cfm.Status       := 1;
    vMess_cfm.Stage        := 1;
    vMess_cfm.Receive_Date := sysdate;
    vMess_cfm.Ext_Err_Code := pError;
    insert into mess_cfm
    values vMess_cfm
    returning mess_cfm_id into pMessID;
    insert into mess_cfm_xml
      (mess_cfm_id,
       mess_xml,
       mess_sign)
    values
      (pMessID,
       vRootNode,
       vSignature);
    --  commit;

    --   result:=ChangeStatus(pMessID,1,1);

    case vMess_cfm.Mess_Type
      when 1 then
        result := ChangeStatus(pMessID, 1, 1);
        if pversionid = 1 then
          result := pkg_mess_cfm_1.parseAnnounceXML(pMessID);
        else
          result := pkg_mess_cfm_2.parseAnnounceXML(pMessID, pversionid);
        end if;
        if result <> 0 then
          InsertEnchanceData(pMessID);
          return result;
        end if;
      when 2 then
        -- result:=ChangeStatus(pMessID,1,1);
        result := pkg_mess_cfm_1.parseCheckXML(pMessID);
        if result <> 0 then
          InsertEnchanceData(pMessID);
          return result;
        end if;
        vMessid := vMess_cfm.Mess_Parentid;
        if vmessid <> -1 then
          -- if vCFMMessageDocID is not null and vCFMMessageDocID <>-1 then
          -- result:= Get_ParentIDByMessagesID(pMessID,vMessId);
          if GetMessageType(vMessId) in (1, 5) then
            result := ChangeStatus(pMessID, 50, 1);
            vcount := 0;
            select count(*)
            into vcount
            from mess_cfm
            where mess_parentid = vMessId
            and mess_type = 3;

            if vcount = 0 then

              --Не было отвергнуто ранее (в случае нерпавильного порядка загрузки сообщений от транспорта)
              result := ChangeStatus(vMessId, 40, 40);
            end if;
          elsif GetMessageType(vMessId) = 4 then
            result := ChangeStatus(pMessID, 1, 1); --Cформирована
            --     result:=ChangeStatus(vMessId,50,1); --не обработано
          else
            result := ChangeStatus(pMessID, 1, 1);
          end if;
        else
          rollback;

          result := -2111; --Пришла квитанция о принятии неизвестного базе документа. Не сохранена.
        end if;

      when 3 then
        result := pkg_mess_cfm_1.parseCheckXML(pMessID);
        if result <> 0 then
          InsertEnchanceData(pMessID);
          return result; --откат??
        end if;
        vMessid := vMess_cfm.Mess_Parentid;
        if vmessid <> -1 then
          if GetMessageType(vMessId) in (1, 5) then
            result := ChangeStatus(pMessID, 50, 1); --Получена
            if result <> 0 then
              null;
            end if;
            result := ChangeStatus(vMessId, 40, 50); --Пришла квитанция с отказом
          elsif GetMessageType(vMessId) = 4 then
            result := ChangeStatus(pMessID, 1, 1); --Cформирована
          else
            result := ChangeStatus(pMessID, 1, 1);
            -- elsif  GetMessageType(vMessId)=5 then
            --  result:=ChangeStatus(vMessId,30,20); --Ошибка отправки ответа
          end if;
        else
          rollback;

          result := -2112; --Пришла квитанция о непринятии неизвестного базе документа. Не сохранена.*/ --------?????????????
        end if;
      when 4 then
        --result:=ChangeStatus(pMessID,1,1);
        result := pkg_mess_cfm_1.parseDopInfXML(pMessID);
        if result <> 0 then
          vres := Get_AutoErrorCheckXML(pMessID, pCheckXML);
          if vres <> 0 then
            null;
          end if;
          InsertEnchanceData(pMessID);
          return result;
        end if;
        result := ChangeStatus(pMessID, 50, 1);

      when 5 then
        result := ChangeStatus(pMessID, 1, 1);
        if result <> 0 then
          null;
        end if;
        result := pkg_mess_cfm_1.parseDopInfAnswXML(pMessID);
        if result <> 0 then
          InsertEnchanceData(pMessID);
          return result;
        end if;
        vMessid := vMess_cfm.Mess_Parentid;
        if vmessid <> -1 then
          result := ChangeStatus(vMessId, 60, 1); --Отправляется ответ или 60,30????
        end if;

      when 8 then
        --result:=parseupdaterequestxml(pMessID);
        result := ChangeStatus(pMessID, 1, 1);
        if result <> 0 then
          null;
        end if;
        result := pkg_mess_cfm_1.ParseUpdateInfoXML(pMessID);
        if result <> 0 then
          vres := Get_AutoErrorCheckXML(pMessID, pCheckXML);
          if vres <> 0 then
            null;
          end if;
          InsertEnchanceData(pMessID);
          return result;
        end if;
        --   result:=SaveXMLtoDB(vRootNode,vMessageType,vMessageVersion, vMessageDocID,vCFMMessageDoc,pError,vSender, vReceiver, pMessID,pMessDocDate);
        null;
      when 9 then
        --    InsertEnchanceData(pMessID);
        result := ChangeStatus(pMessID, 1, 1);
        if result <> 0 then
          null;
        end if;
        result := pkg_mess_cfm_1.parseupdaterequestxml(pMessID);
        null;
      when 10 then
        result := ChangeStatus(pMessID, 1, 1);
        if result <> 0 then
          null;
        end if;
        --result:=parseupdaterequestxml(pMessID);
        result := pkg_mess_cfm_1.parseupdatexml(pMessID);
        if result <> 0 then
          vres := Get_AutoErrorCheckXML(pMessID, pCheckXML);
          if vres <> 0 then
            null;
          end if;
          InsertEnchanceData(pMessID);
          return result;
        end if;
      when 11 then
        result := ChangeStatus(pMessID, 20, 1);
      when 12 then
        vMessid := vMess_cfm.Mess_Parentid;
        result  := ChangeStatus(vMessId, 30, 40);
      when 13 then
        vMessid := vMess_cfm.Mess_Parentid;
        result  := ChangeStatus(vMessId, 40, 50);
      when 14 then
        vMessid := vMess_cfm.Mess_Parentid;
        result  := ChangeStatus(vMessId, 40, 40);
        result  := pkg_passport_cfm.UpdatePassportData(vRootNode);
        -- result:=SaveXMLtoDB(vRootNode,vMessageType,vMessageVersion, vMessageDocID,vCFMMessageDoc,pError,vSender, vReceiver, pMessID,pMessDocDate);
      when 15 then
        result := ChangeStatus(pMessID, 20, 1);
        result := pkg_counter_report_cfm.FillCounterReportExec(pMessID, vRootNode);
        return result;
      else

        result := -1005; --Невозможно утсановить тип документа.Документ не сохранен.  ---изменить!!!!!!

    end case;

    case pError
      when 2 then
        result := Create_Ticket2NO(pMessID, -350, 'Ошибка ЭЦП документа. ', pCheckXML);
      when 3 then
        result := Create_Ticket2NO(pMessID, -351, 'Ошибка ЭЦП документа. ', pCheckXML);
      when 4 then
        result := Create_Ticket2NO(pMessID, -352, 'Ошибка ЭЦП документа. ', pCheckXML);
      when 5 then
        result := Create_Ticket2NO(pMessID, -353, 'Ошибка ЭЦП документа. ', pCheckXML);
      else


        --Квитанции
        if vMess_cfm.Mess_Type in (4, 8, 10) then
          Result := Get_CheckXML(pMessID, pCheckXML);
          if (result = -1000) then
            return - 1003;
          end if;
        end if;
        null;
    end case;
    InsertEnchanceData(pMessID);
    pkg_audits.WriteUserAudit(8, 'pkg_messages_cfm.SaveMessage', 'ID: ' || pMessID);
    Return(Result);
  exception
    when others then
      pMessID := null;
      pkg_exceptions.AddException(sqlcode, sqlerrm, 'Save_Message');

      return - 2100; --Ошибка операции сохранения
  end;

  function Update_Message(pMessID  in number,
                          pMessXML in CLOB) return number is
    Result          number default 0;
    vcount          number default 0;

    vRes            number default 0; -- Временный результат промежуточных проверок
    vMess_cfm       mess_cfm%rowtype; -- Строка ключевой информации
    vMessageVersion varchar2(50 char); -- Версия сообщения (из XML)
    vCFMMessageDoc  varchar2(50 char); -- Версия оригинального сообщения (из XML)
    vMessageGuid    varchar2(50 char); -- Guid сообщения (из XML)
    vMessGuid       varchar2(50 char); -- Guid сообщения (из БД)
    vXML            xmltype;


    vRootNode       clob; -- Сообщение без пакета
    vStage          number; -- Стадия обработки сообщения
    vStatus         number; -- Статус сообщения
    vMessType       number; -- Тип сообщения
    pversionid      number; -- ID версии сообщения

  begin
    Result := 0;

    select count(*)
    into vcount
    from mess_cfm
    where mess_cfm_id = pMessID;
    if vcount = 0 then

      return - 1000; --Сообщение не найдено
    end if;

    select mc.mess_type,
           mc.status,
           mc.stage,
           mc.mess_form_guid
    into vMessType,
         vStatus,
         vStage,
         vMessGuid
    from mess_cfm mc
    where mess_cfm_id = pMessID;

    if (vStage >= 30) then
      return - 531;
    end if;


    --Распаковка из конверта
    result := pkg_messages_xml.Get_RootNodeFromXML(pMessXML, vRootNode);
    if result <> 0 then
      return result; -- -305 или -307 (not valid или узел не найден)
    end if;

    vXML := xmltype.createxml(vRootNode);


    --Преобразование, проверка и сохранение версии документа
    Result := pkg_messages_xml.Get_FieldFromXML(vXML, 'Version', vMessageVersion);
    if Result <> 0 then
      return Result;
    end if;
    vres := pkg_common.GetFormattedGuid(vMessageVersion, vMess_cfm.Mess_Version);
    if vres <> 0 then

      return - 1301; -- Неверный формат идентификационного номера версии документа
    else
      begin
        select mv.mess_type,
               versionid
        into vMess_cfm.Mess_Type,

             pVersionId --Тип сообщения
        from messages_versions mv
        where mv.versioncode = vMess_cfm.Mess_Version;
      exception
        when others then

          return - 306; --Неверная версия сообщения. Сообщение не сохранено.
          vMess_cfm.Mess_Type := -1;
      end;
    end if;


    --Преобразование, проверка и сохранение GUID документа
    Result := pkg_messages_xml.Get_FieldFromXML(vXML, 'DocumentUniqueIdentifier', vMess_cfm.Mess_Docid);
    if Result <> 0 then
      return Result;
    end if;
    vres := pkg_common.GetFormattedGuid(vMess_cfm.Mess_Docid, vMess_cfm.Mess_Form_Guid);
    if vres <> 0 then

      return - 1302; --  Неверный формат идентификационного номера документа
    else

      ---!!!!!!!!!!!!!!!!!!Если будет доп столбец, то проверять его!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

      if vMess_cfm.Mess_Form_Guid <> vMessGuid then
        return - 1011;

        --!!!!!!!!Оставить вторую ветку, если будет дополнительное поле
      end if;
    end if;

    if vMess_cfm.Mess_Type in (2, 3, 4, 5) then

      --убрать типы
      Result := pkg_messages_xml.Get_FieldFromXML(vXML, 'OriginalDocumentGuid', vCFMMessageDoc);
      vres   := pkg_common.GetFormattedGuid(vCFMMessageDoc, vMessageGuid);
      if vres <> 0 then

        return - 1303; --  Неверный формат идентификационного номера документа
      else
        begin
          select mc.mess_cfm_id
          into vMess_cfm.Mess_Parentid
          from mess_cfm mc
          where mc.Mess_Form_Guid = vMessageGuid
          and rownum = 1;
        exception
          when others then
            if vMess_cfm.Mess_Type <> 4 then

              return - 1008; --Не найдено изначальное сообщение, по которому пришло новое сообщение
            else
              vMess_cfm.Mess_Parentid := -1;

              return - 1009; --Сообщение сохранено(!!!нет)
            end if;
        end;
      end if;
    end if;

    update mess_cfm_xml
    set mess_xml = vRootNode
    where mess_cfm_id = pMessID;
    commit;
    case vMess_cfm.Mess_Type
      when 1 then
        delete from mess_cfm_announce_info
        where mess_cfm_id = pMessID;
        delete from mess_cfm_announce_cont
        where mess_cfm_id = pMessID;
        delete from mess_cfm_announce_found
        where mess_cfm_id = pMessID;
        delete from mess_cfm_announce_memb
        where mess_cfm_id = pMessID;
        delete from mess_cfm_announce_oper
        where mess_cfm_id = pMessID;
        delete from mess_cfm_announce_subj
        where mess_cfm_id = pMessID;
        delete from mess_cfm_announce_accounts
        where mess_cfm_id = pMessID;
        if pversionid = 1 then
          result := pkg_mess_cfm_1.parseAnnounceXML(pMessID);
        else
          result := pkg_mess_cfm_2.parseAnnounceXML(pMessID, pversionid);
        end if;
        if result <> 0 then
          UpdateEnchanceData(pMessID);
          return result;
        end if;

        null;
      when 2 then
        delete from mess_cfm_check
        where mess_cfm_id = pMessID;
        result := pkg_mess_cfm_1.parseCheckXML(pMessID);
        if result <> 0 then
          UpdateEnchanceData(pMessID);
          return result;
        end if;

      when 3 then
        delete from mess_cfm_check
        where mess_cfm_id = pMessID;
        result := pkg_mess_cfm_1.parseCheckXML(pMessID);
        if result <> 0 then
          UpdateEnchanceData(pMessID);
          return result;
        end if;

      when 4 then
        delete from mess_cfm_dopinf
        where mess_cfm_id = pMessID;
        result := pkg_mess_cfm_1.parseDopInfXML(pMessID);
        if result <> 0 then
          UpdateEnchanceData(pMessID);
          return result;
        end if;

      when 5 then
        delete from mess_cfm_dopinf_answ
        where mess_cfm_id = pMessID;
        result := pkg_mess_cfm_1.parseDopInfAnswXML(pMessID);
        if result <> 0 then
          UpdateEnchanceData(pMessID);
          return result;
        end if;

      when 8 then
        delete from mess_cfm_update_info
        where mess_cfm_id = pMessID;
        result := pkg_mess_cfm_1.parseUpdateInfoXML(pMessID);
        if result <> 0 then
          UpdateEnchanceData(pMessID);
          return result;
        end if;

      when 9 then
        delete from mess_cfm_update_info
        where mess_cfm_id = pMessID;
        result := pkg_mess_cfm_1.parseUpdateRequestXML(pMessID);
        if result <> 0 then
          UpdateEnchanceData(pMessID);
          return result;
        end if;
      when 10 then
        delete from mess_cfm_update_info
        where mess_cfm_id = pMessID;
        result := pkg_mess_cfm_1.parseUpdateXML(pMessID);
        if result <> 0 then
          UpdateEnchanceData(pMessID);
          return result;
        end if;
    end case;
    UpdateEnchanceData(pMessID);
    pkg_audits.WriteUserAudit(9, 'pkg_messages_cfm.UpdateMessage', 'ID: ' || pMessID);
    Return(Result);

  exception
    when others then
      return - 2101;

  end;

  function Get_Message(pMessID     in number,
                       pMessXML    out CLOB,
                       pIsEditable out number) return number is
    Result       number;
    vType        number;
    vStage       number;
    vStatus      number;
    vMessDocType number;
  begin
    Result      := 0;
    pIsEditable := 1;
    begin
      select mc_xml.mess_xml,
             mc.mess_type,
             mc.stage,
             mc.status,
             mc_an.mess_type
      into pMessXML,
           vType,
           vStage,
           vStatus,
           vmessdoctype
      from mess_cfm mc
      left join mess_cfm_xml mc_xml
      on (mc.mess_cfm_id = mc_xml.mess_cfm_id)
      left join mess_cfm_announce_info mc_an
      on (mc.mess_cfm_id = mc_an.mess_cfm_id)
      where mc.mess_cfm_id = pMessID; --and mc_xml.mess_cfm_id = pMessID and mc_an.mess_cfm_id = pMessID;

    exception
      when NO_DATA_FOUND then
        Return(-1000);
    end;
    begin
      select iseditable
      into pIsEditable
      from work_stages_dic
      where workstageid = vStage
      and messagetype = vType;
      if vmessdoctype = 4 then
        pIsEditable := 0;
      elsif vStatus = 20 then
        pIsEditable := 0;
      end if;

    exception
      when NO_DATA_FOUND then
        pIsEditable := 1;

        Return(-1001); --Стадия обработки сообщения или его тип задан неверно
    end;
    return result;
  end;

  function Get_MessageShortData(pMessID    in number,
                                pResCursor out sys_refcursor) return number is
    Result      number default 0;
    pMessType   number;
    vuserlang   varchar2(1000 char);
    vmodulename varchar2(1000 char);
  begin

    result := pkg_main.GetCurrentSettings(vuserlang, vmodulename);
    if result <> 0 then
      return result;
    end if;

    case vuserlang
      when 'Kazakh' then
        open pResCursor for
          select mc.mess_cfm_id                as mess_cfm_id,
                 mc.receive_date               as receive_date,
                 mc_exec.cfm_code              as cfmcode,
                 mc_exec.oper_idtype           as IDTypeOperation,
                 mc_exec.oper_idview           as IDViewOperation,
                 mc_exec.oper_number           as OperationNumber,
                 mc_exec.oper_currency_code   as CurrencyCode,
                 currency_info.analyticname   as CurrencyName,
                 mc_exec.oper_currency_amount as AmountCurrency,
                 mc_exec.oper_tenge_amount     as AmountCurrencyTenge,
                 mc_exec.oper_doc_name         as DocOperationName,
                 mc_exec.oper_doc_number       as DocOperationNumber,
                 mc.send_date                  as messdate,
                 mc.status                     as status,
                 mc.mess_version               as Version,
                 mc.mess_docid                 as DocumentUniqueIdentifier,
                 mc.stage                      as workstage,
                 mc.mess_type                  as messtype,
                 mc_exec.update_version        as UpdateVersion,
                 mc_exec.release_date          as DateRelease,
                 mc.sender                     as sender,
                 mc.receiver                   as receiver,
                 mc_exec.mess_number           as messnumber,
                 mc_exec.mess_doctype          as messdoctype,
                 --    mc_exec.mess_reason           as messreason,
                 --    mc_exec.statusname            as statusname,
                 GetEditableField(mc.mess_cfm_id) as iseditable,
                 mc_exec.mess_reason as messreason,
                 mc_exec.statusname_kz as statusname,
                 mc_exec.oper_status as operstatus

          from mess_cfm mc
          join mess_cfm_exec mc_exec
          on (mc.mess_cfm_id = mc_exec.mess_cfm_id)
          left join analyticcodes currency_info
          on (mc_exec.oper_currency_code = currency_info.analyticcode and currency_info.analytictype = 41018)

          where mc.mess_cfm_id = pMessID;
      else
        open pResCursor for
          select mc.mess_cfm_id                as mess_cfm_id,
                 mc.receive_date               as receive_date,
                 mc_exec.cfm_code              as cfmcode,
                 mc_exec.oper_idtype           as IDTypeOperation,
                 mc_exec.oper_idview           as IDViewOperation,
                 mc_exec.oper_number           as OperationNumber,
                 mc_exec.oper_currency_code   as CurrencyCode,
                 currency_info.analyticname   as CurrencyName,
                 mc_exec.oper_currency_amount as AmountCurrency,
                 mc_exec.oper_tenge_amount     as AmountCurrencyTenge,
                 mc_exec.oper_doc_name         as DocOperationName,
                 mc_exec.oper_doc_number       as DocOperationNumber,
                 mc.send_date                  as messdate,
                 mc.status                     as status,
                 mc.mess_version               as Version,
                 mc.mess_docid                 as DocumentUniqueIdentifier,
                 mc.stage                      as workstage,
                 mc.mess_type                  as messtype,
                 mc_exec.update_version        as UpdateVersion,
                 mc_exec.release_date          as DateRelease,
                 mc.sender                     as sender,
                 mc.receiver                   as receiver,
                 mc_exec.mess_number           as messnumber,
                 mc_exec.mess_doctype          as messdoctype,
                 --    mc_exec.mess_reason           as messreason,
                 --    mc_exec.statusname            as statusname,
                 GetEditableField(mc.mess_cfm_id) as iseditable,
                 mc_exec.mess_reason as messreason,
                 mc_exec.statusname as statusname,
                 mc_exec.oper_status as operstatus

          from mess_cfm mc
          join mess_cfm_exec mc_exec
          on (mc.mess_cfm_id = mc_exec.mess_cfm_id)
          left join analyticcodes currency_info
          on (mc_exec.oper_currency_code = currency_info.analyticcode and currency_info.analytictype = 41018)

          where mc.mess_cfm_id = pMessID;
    end case;
    Return(Result);
  exception
    when no_data_found then

      return - 1000; -- Сообщение не найдено
  end;

  function Get_MessagesList(pResCursor out sys_refcursor,
                            pListID    in number := 1) return number is
    Result      number default 0;
    vrolegroup  number;
    vmodulename varchar2(100 char);
    vuserlang   varchar2(100 char);
  begin

    result := pkg_users.GetUsersRolesGroup(vrolegroup);
    if result <> 0 then
      return result;
    end if;
    result := pkg_main.GetCurrentSettings(vuserlang, vmodulename);
    if result <> 0 then
      return result;
    end if;
    case vuserlang
      when 'Kazakh' then
        open pResCursor for
          select mc.mess_cfm_id                as mess_cfm_id,
                 mc.receive_date               as receive_date,
                 mc_exec.cfm_code              as cfmcode,
                 mc_exec.oper_idtype           as IDTypeOperation,
                 mc_exec.oper_idview           as IDViewOperation,
                 mc_exec.oper_number           as OperationNumber,
                 mc_exec.oper_currency_code   as CurrencyCode,
                 currency_info.analyticname   as CurrencyName,
                 mc_exec.oper_currency_amount as AmountCurrency,
                 mc_exec.oper_tenge_amount     as AmountCurrencyTenge,
                 mc_exec.oper_doc_name         as DocOperationName,
                 mc_exec.oper_doc_number       as DocOperationNumber,
                 mc.send_date                  as messdate,
                 mc.status                     as status,
                 mc.mess_version               as Version,
                 mc.mess_docid                 as DocumentUniqueIdentifier,
                 mc.stage                      as workstage,
                 mc.mess_type                  as messtype,
                 mc_exec.update_version        as UpdateVersion,
                 mc_exec.release_date          as DateRelease,
                 mc.sender                     as sender,
                 mc.receiver                   as receiver,
                 mc_exec.mess_number           as messnumber,
                 mc_exec.mess_doctype          as messdoctype,
                 mc_exec.mess_reason           as messreason,
                 mc_exec.statusname_kz         as statusname,
                 mc_exec.oper_status           as operstatus

          from mess_cfm mc
          join mess_cfm_exec mc_exec
          on (mc.mess_cfm_id = mc_exec.mess_cfm_id)
          left join analyticcodes currency_info
          on (mc_exec.oper_currency_code = currency_info.analyticcode and currency_info.analytictype = 41018)

          where exists (select rowid
                 from mess_listdic_roledest --roledest_ofm_mview
                 where (listnum = pListID and softname = vmodulename and roleid = vrolegroup and statusid = mc.status and stagesid = mc.stage and doctype = mc.mess_type))
          order by mc.receive_date desc; --end select

      else

        case pListID
          when 15 then
            --type=8,9,10
            open pResCursor for
              select mc.mess_cfm_id                as mess_cfm_id,
                     mc.receive_date               as receive_date,
                     mc_exec.cfm_code              as cfmcode,
                     mc_exec.oper_idtype           as IDTypeOperation,
                     mc_exec.oper_idview           as IDViewOperation,
                     mc_exec.oper_number           as OperationNumber,
                     mc_exec.oper_currency_code   as CurrencyCode,
                     currency_info.analyticname   as CurrencyName,
                     mc_exec.oper_currency_amount as AmountCurrency,
                     mc_exec.oper_tenge_amount     as AmountCurrencyTenge,
                     mc_exec.oper_doc_name         as DocOperationName,
                     mc_exec.oper_doc_number       as DocOperationNumber,
                     mc.send_date                  as messdate,
                     mc.status                     as status,
                     mc.mess_version               as Version,
                     mc.mess_docid                 as DocumentUniqueIdentifier,
                     mc.stage                      as workstage,
                     mc.mess_type                  as messtype,
                     mc_exec.update_version        as UpdateVersion,
                     mc_exec.release_date          as DateRelease,
                     mc.sender                     as sender,
                     mc.receiver                   as receiver,
                     mc_exec.mess_number           as messnumber,
                     mc_exec.mess_doctype          as messdoctype,
                     mc_exec.mess_reason           as messreason,
                     mc_exec.statusname            as statusname,
                     mc_exec.oper_status           as operstatus

              from mess_cfm mc
              join mess_cfm_exec mc_exec
              on (mc.mess_cfm_id = mc_exec.mess_cfm_id)
              left join analyticcodes currency_info
              on (mc_exec.oper_currency_code = currency_info.analyticcode and currency_info.analytictype = 41018)


              where exists (select rowid
                     from mess_listdic_roledest --roledest_ofm_mview
                     where (listnum = pListID and softname = vmodulename and roleid = vrolegroup and statusid = mc.status and stagesid = mc.stage and doctype = mc.mess_type))
              order by DateRelease desc;

          when 16 then
            --type=8,9,10
            open pResCursor for
              select mc.mess_cfm_id                as mess_cfm_id,
                     mc.receive_date               as receive_date,
                     mc_exec.cfm_code              as cfmcode,
                     mc_exec.oper_idtype           as IDTypeOperation,
                     mc_exec.oper_idview           as IDViewOperation,
                     mc_exec.oper_number           as OperationNumber,
                     mc_exec.oper_currency_code   as CurrencyCode,
                     currency_info.analyticname   as CurrencyName,
                     mc_exec.oper_currency_amount as AmountCurrency,
                     mc_exec.oper_tenge_amount     as AmountCurrencyTenge,
                     mc_exec.oper_doc_name         as DocOperationName,
                     mc_exec.oper_doc_number       as DocOperationNumber,
                     mc.send_date                  as messdate,
                     mc.status                     as status,
                     mc.mess_version               as Version,
                     mc.mess_docid                 as DocumentUniqueIdentifier,
                     mc.stage                      as workstage,
                     mc.mess_type                  as messtype,
                     mc_exec.update_version        as UpdateVersion,
                     mc_exec.release_date          as DateRelease,
                     mc.sender                     as sender,
                     mc.receiver                   as receiver,
                     mc_exec.mess_number           as messnumber,
                     mc_exec.mess_doctype          as messdoctype,
                     mc_exec.mess_reason           as messreason,
                     mc_exec.statusname            as statusname,
                     mc_exec.oper_status           as operstatus

              from mess_cfm mc
              join mess_cfm_exec mc_exec
              on (mc.mess_cfm_id = mc_exec.mess_cfm_id)
              left join analyticcodes currency_info
              on (mc_exec.oper_currency_code = currency_info.analyticcode and currency_info.analytictype = 41018)

              where exists (select rowid
                     from mess_listdic_roledest --roledest_ofm_mview
                     where (listnum = pListID and softname = vmodulename and roleid = vrolegroup and statusid = mc.status and stagesid = mc.stage and doctype = mc.mess_type))
              --             and DateRelease>nvl(pkg_updates_cfm.GetLastReleaseDate,to_date('30.10.2009','mm.dd.yyyy'))
              order by DateRelease desc;

          else
            open pResCursor for
              select mc.mess_cfm_id                as mess_cfm_id,
                     mc.receive_date               as receive_date,
                     mc_exec.cfm_code              as cfmcode,
                     mc_exec.oper_idtype           as IDTypeOperation,
                     mc_exec.oper_idview           as IDViewOperation,
                     mc_exec.oper_number           as OperationNumber,
                     mc_exec.oper_currency_code   as CurrencyCode,
                     currency_info.analyticname   as CurrencyName,
                     mc_exec.oper_currency_amount as AmountCurrency,
                     mc_exec.oper_tenge_amount     as AmountCurrencyTenge,
                     mc_exec.oper_doc_name         as DocOperationName,
                     mc_exec.oper_doc_number       as DocOperationNumber,
                     mc.send_date                  as messdate,
                     mc.status                     as status,
                     mc.mess_version               as Version,
                     mc.mess_docid                 as DocumentUniqueIdentifier,
                     mc.stage                      as workstage,
                     mc.mess_type                  as messtype,
                     mc_exec.update_version        as UpdateVersion,
                     mc_exec.release_date          as DateRelease,
                     mc.sender                     as sender,
                     mc.receiver                   as receiver,
                     mc_exec.mess_number           as messnumber,
                     mc_exec.mess_doctype          as messdoctype,
                     mc_exec.mess_reason           as messreason,
                     mc_exec.statusname            as statusname,
                     mc_exec.oper_status           as operstatus

              from mess_cfm mc
              join mess_cfm_exec mc_exec
              on (mc.mess_cfm_id = mc_exec.mess_cfm_id)
              left join analyticcodes currency_info
              on (mc_exec.oper_currency_code = currency_info.analyticcode and currency_info.analytictype = 41018)

              where exists (select rowid
                     from mess_listdic_roledest --roledest_ofm_mview
                     where (listnum = pListID and softname = vmodulename and roleid = vrolegroup and statusid = mc.status and stagesid = mc.stage and doctype = mc.mess_type)) --end select
              order by mc.receive_date desc;

        end case;
    end case;
    Return(Result);
  exception
    when others then
      return - 2102;

  end;
  function Get_MessagesListForPeriod(pListID           in number := 1,
                                     pReceiveDateBegin in date,
                                     pReceiveDateEnd   in date,
                                     pResCursor        out sys_refcursor) return number is
    Result      number default 0;
    vrolegroup  number;
    vmodulename varchar2(100 char);
    vuserlang   varchar2(100 char);
  begin
    result := pkg_users.GetUsersRolesGroup(vrolegroup);
    if result <> 0 then
      return result;
    end if;
    result := pkg_main.GetCurrentSettings(vuserlang, vmodulename);
    if result <> 0 then
      return result;
    end if;
    case vuserlang
      when 'Kazakh' then
        open pResCursor for
          select mc.mess_cfm_id                as mess_cfm_id,
                 mc.receive_date               as receive_date,
                 mc_exec.cfm_code              as cfmcode,
                 mc_exec.oper_idtype           as IDTypeOperation,
                 mc_exec.oper_idview           as IDViewOperation,
                 mc_exec.oper_number           as OperationNumber,
                 mc_exec.oper_currency_code   as CurrencyCode,
                 currency_info.analyticname  as CurrencyInfo,
                 mc_exec.oper_currency_amount as AmountCurrency,
                 mc_exec.oper_tenge_amount     as AmountCurrencyTenge,
                 mc_exec.oper_doc_name         as DocOperationName,
                 mc_exec.oper_doc_number       as DocOperationNumber,
                 mc.send_date                  as messdate,
                 mc.status                     as status,
                 mc.mess_version               as Version,
                 mc.mess_docid                 as DocumentUniqueIdentifier,
                 mc.stage                      as workstage,
                 mc.mess_type                  as messtype,
                 mc_exec.update_version        as UpdateVersion,
                 mc_exec.release_date          as DateRelease,
                 mc.sender                     as sender,
                 mc.receiver                   as receiver,
                 mc_exec.mess_number           as messnumber,
                 mc_exec.mess_doctype          as messdoctype,
                 mc_exec.mess_reason           as messreason,
                 mc_exec.statusname_kz         as statusname,
                 mc_exec.oper_status           as operstatus

          from mess_cfm mc
          join mess_cfm_exec mc_exec
          on (mc.mess_cfm_id = mc_exec.mess_cfm_id)
          left join analyticcodes currency_info
          on (mc_exec.oper_currency_code = currency_info.analyticcode and currency_info.analytictype = 41018)

          where mc.receive_date between trunc(pReceiveDateBegin) and trunc(pReceiveDateEnd) + 1
          and exists (select rowid
                 from mess_listdic_roledest --roledest_ofm_mview
                 where ( /*listnum  = pListID
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      and */
                        softname = vmodulename and roleid = vrolegroup and statusid = mc.status and stagesid = mc.stage and doctype = mc.mess_type)) --end select
          order by mc.receive_date desc;
      else
        open pResCursor for
          select mc.mess_cfm_id                as mess_cfm_id,
                 mc.receive_date               as receive_date,
                 mc_exec.cfm_code              as cfmcode,
                 mc_exec.oper_idtype           as IDTypeOperation,
                 mc_exec.oper_idview           as IDViewOperation,
                 mc_exec.oper_number           as OperationNumber,
                 mc_exec.oper_currency_code   as CurrencyCode,
                 currency_info.analyticname   as CurrencyName,
                 mc_exec.oper_currency_amount as AmountCurrency,
                 mc_exec.oper_tenge_amount     as AmountCurrencyTenge,
                 mc_exec.oper_doc_name         as DocOperationName,
                 mc_exec.oper_doc_number       as DocOperationNumber,
                 mc.send_date                  as messdate,
                 mc.status                     as status,
                 mc.mess_version               as Version,
                 mc.mess_docid                 as DocumentUniqueIdentifier,
                 mc.stage                      as workstage,
                 mc.mess_type                  as messtype,
                 mc_exec.update_version        as UpdateVersion,
                 mc_exec.release_date          as DateRelease,
                 mc.sender                     as sender,
                 mc.receiver                   as receiver,
                 mc_exec.mess_number           as messnumber,
                 mc_exec.mess_doctype          as messdoctype,
                 mc_exec.mess_reason           as messreason,
                 mc_exec.statusname            as statusname,
                 mc_exec.oper_status           as operstatus

          from mess_cfm mc
          join mess_cfm_exec mc_exec
          on (mc.mess_cfm_id = mc_exec.mess_cfm_id)
          left join analyticcodes currency_info
          on (mc_exec.oper_currency_code = currency_info.analyticcode and currency_info.analytictype = 41018)

          where mc.receive_date between trunc(pReceiveDateBegin) and trunc(pReceiveDateEnd) + 1
          and exists (select rowid
                 from mess_listdic_roledest
                 where ( /*listnum  = pListID
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      and */
                        softname = vmodulename and roleid = vrolegroup and statusid = mc.status and stagesid = mc.stage and doctype = mc.mess_type)) --end select
          order by mc.receive_date desc;
    end case;
    return result;
  end;

  function Get_FilteredMessagesList(pListID                   in number := 1,
                                    pReceiveDateBegin         in date,
                                    pReceiveDateEnd           in date,
                                    pSendDateBegin            in date,
                                    pSendDateEnd              in date,
                                    pMessNumberBegin          in number,
                                    pMessNumberEnd            in number,
                                    pMessReason               in number,
                                    pMessOperationStatus      in number,
                                    pOperationNumber          in varchar2,
                                    pIDTypeOperation          in number,
                                    pIDViewOperation          in number,
                                    pCurrencycode             in number,
                                    pAmountcurrencyBegin      in number,
                                    pAmountcurrencyEnd        in number,
                                    pAmountcurrencytengeBegin in number,
                                    pAmountcurrencytengeEnd   in number,
                                    pResCursor                out sys_refcursor) return number is
    Result      number default 0;
    vrolegroup  number;
    vmodulename varchar2(100 char);
    vuserlang   varchar2(100 char);
  begin

    result := pkg_users.GetUsersRolesGroup(vrolegroup);
    if result <> 0 then
      return result;
    end if;
    result := pkg_main.GetCurrentSettings(vuserlang, vmodulename);
    if result <> 0 then
      return result;
    end if;
    case vuserlang
      when 'Kazakh' then
        open pResCursor for
          select mc.mess_cfm_id                as mess_cfm_id,
                 mc.receive_date               as receive_date,
                 mc_exec.cfm_code              as cfmcode,
                 mc_exec.oper_idtype           as IDTypeOperation,
                 mc_exec.oper_idview           as IDViewOperation,
                 mc_exec.oper_number           as OperationNumber,
                 mc_exec.oper_currency_code   as CurrencyCode,
                 currency_info.analyticname   as CurrencyName,
                 mc_exec.oper_currency_amount as AmountCurrency,
                 mc_exec.oper_tenge_amount     as AmountCurrencyTenge,
                 mc_exec.oper_doc_name         as DocOperationName,
                 mc_exec.oper_doc_number       as DocOperationNumber,
                 mc.send_date                  as messdate,
                 mc.status                     as status,
                 mc.mess_version               as Version,
                 mc.mess_docid                 as DocumentUniqueIdentifier,
                 mc.stage                      as workstage,
                 mc.mess_type                  as messtype,
                 mc_exec.update_version        as UpdateVersion,
                 mc_exec.release_date          as DateRelease,
                 mc.sender                     as sender,
                 mc.receiver                   as receiver,
                 mc_exec.mess_number           as messnumber,
                 mc_exec.mess_doctype          as messdoctype,
                 mc_exec.mess_reason           as messreason,
                 mc_exec.statusname_kz         as statusname,
                 mc_exec.oper_status           as operstatus

          from mess_cfm mc
          join mess_cfm_exec mc_exec
          on (mc.mess_cfm_id = mc_exec.mess_cfm_id)
          left join analyticcodes currency_info
          on (mc_exec.oper_currency_code = currency_info.analyticcode and currency_info.analytictype = 41018)


          where exists (select rowid
                 from mess_listdic_roledest --roledest_ofm_mview
                 where (listnum = pListID and softname = vmodulename and roleid = vrolegroup and statusid = mc.status and stagesid = mc.stage and doctype = mc.mess_type)) --end select

          and mc.receive_date between nvl(pReceiveDateBegin, to_date('31.12.1999', 'dd.mm.yyyy')) and nvl(pReceiveDateEnd, to_date('31.12.2999', 'dd.mm.yyyy'))
          and ((mc.send_date between nvl(pSendDateBegin, to_date('31.12.1999', 'dd.mm.yyyy')) and nvl(pSendDateEnd, to_date('31.12.2999', 'dd.mm.yyyy'))) or (mc.send_date is null and pSendDateEnd is null and pSendDateBegin is null))
          and ((mc_exec.mess_number between nvl(pMessNumberBegin, 0) and pMessNumberEnd) or (pMessNumberEnd is null and mc_exec.mess_number >= nvl(pMessNumberBegin, 0)) or (pMessNumberEnd is null))
          and ((mc_exec.mess_reason = pMessReason) or (pMessReason is null))
          and ((mc_exec.oper_number = pOperationNumber) or (pOperationNumber is null))
          and ((mc_exec.oper_status = pMessOperationStatus) or (pMessOperationStatus is null))
          and ((mc_exec.oper_currency_code = pCurrencycode) or (pCurrencycode is null))
          and ((mc_exec.oper_idtype = pIDTypeOperation) or (pIDTypeOperation is null))
          and ((mc_exec.oper_idview = pIDViewOperation) or (pIDViewOperation is null))
          and ((mc_exec.oper_currency_amount between nvl(pAmountcurrencyBegin, 0) and pAmountcurrencyEnd) or (pAmountcurrencyEnd is null and nvl(mc_exec.oper_currency_amount, 0) >= nvl(pAmountcurrencyBegin, 0)) or
                (mc_exec.oper_currency_amount is null and pAmountcurrencyBegin is null and pAmountcurrencyEnd is null))
          and ((mc_exec.oper_tenge_amount between nvl(pAmountcurrencytengeBegin, 0) and pAmountcurrencytengeEnd) or (pAmountcurrencytengeEnd is null and nvl(mc_exec.oper_tenge_amount, 0) >= nvl(pAmountcurrencytengeBegin, 0)) or
                (mc_exec.oper_tenge_amount is null and pAmountcurrencytengeBegin is null and pAmountcurrencytengeEnd is null))
          order by mc.receive_date desc;
      else

        case pListID

          when 15 then
            --type=8,9,10
            open pResCursor for
              select mc.mess_cfm_id                as mess_cfm_id,
                     mc.receive_date               as receive_date,
                     mc_exec.cfm_code              as cfmcode,
                     mc_exec.oper_idtype           as IDTypeOperation,
                     mc_exec.oper_idview           as IDViewOperation,
                     mc_exec.oper_number           as OperationNumber,
                     mc_exec.oper_currency_code   as CurrencyCode,
                     currency_info.analyticname   as CurrencyName,
                     mc_exec.oper_currency_amount as AmountCurrency,
                     mc_exec.oper_tenge_amount     as AmountCurrencyTenge,
                     mc_exec.oper_doc_name         as DocOperationName,
                     mc_exec.oper_doc_number       as DocOperationNumber,
                     mc.send_date                  as messdate,
                     mc.status                     as status,
                     mc.mess_version               as Version,
                     mc.mess_docid                 as DocumentUniqueIdentifier,
                     mc.stage                      as workstage,
                     mc.mess_type                  as messtype,
                     mc_exec.update_version        as UpdateVersion,
                     mc_exec.release_date          as DateRelease,
                     mc.sender                     as sender,
                     mc.receiver                   as receiver,
                     mc_exec.mess_number           as messnumber,
                     mc_exec.mess_doctype          as messdoctype,
                     mc_exec.mess_reason           as messreason,
                     mc_exec.statusname            as statusname,
                     mc_exec.oper_status           as operstatus

              from mess_cfm mc
              join mess_cfm_exec mc_exec
              on (mc.mess_cfm_id = mc_exec.mess_cfm_id)
              left join analyticcodes currency_info
              on (mc_exec.oper_currency_code = currency_info.analyticcode and currency_info.analytictype = 41018)


              where exists (select rowid
                     from mess_listdic_roledest --roledest_ofm_mview
                     where (listnum = pListID and softname = vmodulename and roleid = vrolegroup and statusid = mc.status and stagesid = mc.stage and doctype = mc.mess_type))
              order by mc_exec.release_date desc;

          when 16 then
            --type=8,9,10
            open pResCursor for
              select mc.mess_cfm_id                as mess_cfm_id,
                     mc.receive_date               as receive_date,
                     mc_exec.cfm_code              as cfmcode,
                     mc_exec.oper_idtype           as IDTypeOperation,
                     mc_exec.oper_idview           as IDViewOperation,
                     mc_exec.oper_number           as OperationNumber,
                     mc_exec.oper_currency_code   as CurrencyCode,
                     currency_info.analyticname   as CurrencyName,
                     mc_exec.oper_currency_amount as AmountCurrency,
                     mc_exec.oper_tenge_amount     as AmountCurrencyTenge,
                     mc_exec.oper_doc_name         as DocOperationName,
                     mc_exec.oper_doc_number       as DocOperationNumber,
                     mc.send_date                  as messdate,
                     mc.status                     as status,
                     mc.mess_version               as Version,
                     mc.mess_docid                 as DocumentUniqueIdentifier,
                     mc.stage                      as workstage,
                     mc.mess_type                  as messtype,
                     mc_exec.update_version        as UpdateVersion,
                     mc_exec.release_date          as DateRelease,
                     mc.sender                     as sender,
                     mc.receiver                   as receiver,
                     mc_exec.mess_number           as messnumber,
                     mc_exec.mess_doctype          as messdoctype,
                     mc_exec.mess_reason           as messreason,
                     mc_exec.statusname            as statusname,
                     mc_exec.oper_status           as operstatus

              from mess_cfm mc
              join mess_cfm_exec mc_exec
              on (mc.mess_cfm_id = mc_exec.mess_cfm_id)
              left join analyticcodes currency_info
              on (mc_exec.oper_currency_code = currency_info.analyticcode and currency_info.analytictype = 41018)

              where exists (select rowid
                     from mess_listdic_roledest --roledest_ofm_mview
                     where (listnum = pListID and softname = vmodulename and roleid = vrolegroup and statusid = mc.status and stagesid = mc.stage and doctype = mc.mess_type))
              order by mc_exec.release_date desc;
            -- and DateRelease>nvl(pkg_updates_cfm.GetLastReleaseDate,'30.10.2009') order by DateRelease;
          else
            open pResCursor for
              select mc.mess_cfm_id                as mess_cfm_id,
                     mc.receive_date               as receive_date,
                     mc_exec.cfm_code              as cfmcode,
                     mc_exec.oper_idtype           as IDTypeOperation,
                     mc_exec.oper_idview           as IDViewOperation,
                     mc_exec.oper_number           as OperationNumber,
                     mc_exec.oper_currency_code   as CurrencyCode,
                     currency_info.analyticname   as CurrencyName,
                     mc_exec.oper_currency_amount as AmountCurrency,
                     mc_exec.oper_tenge_amount     as AmountCurrencyTenge,
                     mc_exec.oper_doc_name         as DocOperationName,
                     mc_exec.oper_doc_number       as DocOperationNumber,
                     mc.send_date                  as messdate,
                     mc.status                     as status,
                     mc.mess_version               as Version,
                     mc.mess_docid                 as DocumentUniqueIdentifier,
                     mc.stage                      as workstage,
                     mc.mess_type                  as messtype,
                     mc_exec.update_version        as UpdateVersion,
                     mc_exec.release_date          as DateRelease,
                     mc.sender                     as sender,
                     mc.receiver                   as receiver,
                     mc_exec.mess_number           as messnumber,
                     mc_exec.mess_doctype          as messdoctype,
                     mc_exec.mess_reason           as messreason,
                     mc_exec.statusname            as statusname,
                     mc_exec.oper_status           as operstatus

              from mess_cfm mc
              join mess_cfm_exec mc_exec
              on (mc.mess_cfm_id = mc_exec.mess_cfm_id)
              left join analyticcodes currency_info
              on (mc_exec.oper_currency_code = currency_info.analyticcode and currency_info.analytictype = 41018)


              where exists (select rowid
                     from mess_listdic_roledest --roledest_ofm_mview
                     where (listnum = pListID and softname = vmodulename and roleid = vrolegroup and statusid = mc.status and stagesid = mc.stage and doctype = mc.mess_type)) --end select

              and mc.receive_date between nvl(pReceiveDateBegin, to_date('31.12.1999', 'dd.mm.yyyy')) and nvl(pReceiveDateEnd, to_date('31.12.2999', 'dd.mm.yyyy'))
              and ((mc.send_date between nvl(pSendDateBegin, to_date('31.12.1999', 'dd.mm.yyyy')) and nvl(pSendDateEnd, to_date('31.12.2999', 'dd.mm.yyyy'))) or (mc.send_date is null and pSendDateEnd is null and pSendDateBegin is null))
              and ((mc_exec.mess_number between nvl(pMessNumberBegin, 0) and pMessNumberEnd) or (pMessNumberEnd is null and mc_exec.mess_number >= nvl(pMessNumberBegin, 0)) or (pMessNumberEnd is null))
              and ((mc_exec.mess_reason = pMessReason) or (pMessReason is null))
              and ((mc_exec.oper_number = pOperationNumber) or (pOperationNumber is null))
              and ((mc_exec.oper_status = pMessOperationStatus) or (pMessOperationStatus is null))
              and ((mc_exec.oper_currency_code = pCurrencycode) or (pCurrencycode is null))
              and ((mc_exec.oper_idtype = pIDTypeOperation) or (pIDTypeOperation is null))
              and ((mc_exec.oper_idview = pIDViewOperation) or (pIDViewOperation is null))
              and ((mc_exec.oper_currency_amount between nvl(pAmountcurrencyBegin, 0) and pAmountcurrencyEnd) or (pAmountcurrencyEnd is null and nvl(mc_exec.oper_currency_amount, 0) >= nvl(pAmountcurrencyBegin, 0)) or
                    (mc_exec.oper_currency_amount is null and pAmountcurrencyBegin is null and pAmountcurrencyEnd is null))
              and ((mc_exec.oper_tenge_amount between nvl(pAmountcurrencytengeBegin, 0) and pAmountcurrencytengeEnd) or (pAmountcurrencytengeEnd is null and nvl(mc_exec.oper_tenge_amount, 0) >= nvl(pAmountcurrencytengeBegin, 0)) or
                    (mc_exec.oper_tenge_amount is null and pAmountcurrencytengeBegin is null and pAmountcurrencytengeEnd is null))
              order by mc.receive_date desc;
        end case;
    end case;
    Return(Result);
  exception
    when others then
      return - 2102;
  end;

  function Get_FixedMessagesList(pReceiveDateBegin         in date,
                                 pReceiveDateEnd           in date,
                                 pSendDateBegin            in date,
                                 pSendDateEnd              in date,
                                 pMessNumberBegin          in number,
                                 pMessNumberEnd            in number,
                                 pMessReason               in number,
                                 pMessOperationStatus      in number,
                                 pOperationNumber          in varchar2,
                                 pIDTypeOperation          in number,
                                 pIDViewOperation          in number,
                                 pCurrencycode             in number,
                                 pAmountcurrencyBegin      in number,
                                 pAmountcurrencyEnd        in number,
                                 pAmountcurrencytengeBegin in number,
                                 pAmountcurrencytengeEnd   in number,
                                 pResCursor                out sys_refcursor) return number is
    Result      number default 0;
    vrolegroup  number;
    vmodulename varchar2(100 char);
    vuserlang   varchar2(100 char);
  begin

    result := pkg_users.GetUsersRolesGroup(vrolegroup);
    if result <> 0 then
      return result;
    end if;
    result := pkg_main.GetCurrentSettings(vuserlang, vmodulename);
    if result <> 0 then
      return result;
    end if;
    /*vmodulename:='SFM.CLIENTARM';
    vrolegroup:=4;*/

    case vuserlang
      when 'Kazakh' then
        open pResCursor for
          select mc.mess_cfm_id                as mess_cfm_id,
                 mc.receive_date               as receive_date,
                 mc_exec.cfm_code              as cfmcode,
                 mc_exec.oper_idtype           as IDTypeOperation,
                 mc_exec.oper_idview           as IDViewOperation,
                 mc_exec.oper_number           as OperationNumber,
                 mc_exec.oper_currency_code   as CurrencyCode,
                 currency_info.analyticname   as CurrencyName,
                 mc_exec.oper_currency_amount as AmountCurrency,
                 mc_exec.oper_tenge_amount     as AmountCurrencyTenge,
                 mc_exec.oper_doc_name         as DocOperationName,
                 mc_exec.oper_doc_number       as DocOperationNumber,
                 mc.send_date                  as messdate,
                 mc.status                     as status,
                 mc.mess_version               as Version,
                 mc.mess_docid                 as DocumentUniqueIdentifier,
                 mc.stage                      as workstage,
                 mc.mess_type                  as messtype,
                 mc_exec.update_version        as UpdateVersion,
                 mc_exec.release_date          as DateRelease,
                 mc.sender                     as sender,
                 mc.receiver                   as receiver,
                 mc_exec.mess_number           as messnumber,
                 mc_exec.mess_doctype          as messdoctype,
                 mc_exec.mess_reason           as messreason,
                 mc_exec.statusname_kz         as statusname,
                 mc_exec.oper_status           as operstatus

          from mess_cfm mc
          join mess_cfm_exec mc_exec
          on (mc.mess_cfm_id = mc_exec.mess_cfm_id)
          left join analyticcodes currency_info
          on (mc_exec.oper_currency_code = currency_info.analyticcode and currency_info.analytictype = 41018)

          where exists (select rowid
                 from mess_listdic_roledest --roledest_ofm_mview
                 where ( /*listnum = pListID
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      and*/
                        softname = vmodulename and roleid = vrolegroup and statusid = mc.status and stagesid = mc.stage and doctype = mc.mess_type)

                 )

          and mc.receive_date between nvl(pReceiveDateBegin, to_date('31.12.1999', 'dd.mm.yyyy')) and nvl(pReceiveDateEnd, to_date('31.12.2999', 'dd.mm.yyyy'))
          and ((mc.send_date between nvl(pSendDateBegin, to_date('31.12.1999', 'dd.mm.yyyy')) and nvl(pSendDateEnd, to_date('31.12.2999', 'dd.mm.yyyy'))) or (mc.send_date is null and pSendDateEnd is null and pSendDateBegin is null))
          and ((mc_exec.mess_number between nvl(pMessNumberBegin, 0) and pMessNumberEnd) or (pMessNumberEnd is null and mc_exec.mess_number >= nvl(pMessNumberBegin, 0)) or (pMessNumberEnd is null))
          and ((mc_exec.mess_reason = pMessReason) or (pMessReason is null))
          and ((mc_exec.oper_number = pOperationNumber) or (pOperationNumber is null))
          and ((mc_exec.oper_status = pMessOperationStatus) or (pMessOperationStatus is null))
          and ((mc_exec.oper_currency_code = pCurrencycode) or (pCurrencycode is null))
          and ((mc_exec.oper_idtype = pIDTypeOperation) or (pIDTypeOperation is null))
          and ((mc_exec.oper_idview = pIDViewOperation) or (pIDViewOperation is null))
          and ((mc_exec.oper_currency_amount between nvl(pAmountcurrencyBegin, 0) and pAmountcurrencyEnd) or (pAmountcurrencyEnd is null and nvl(mc_exec.oper_currency_amount, 0) >= nvl(pAmountcurrencyBegin, 0)) or
                (mc_exec.oper_currency_amount is null and pAmountcurrencyBegin is null and pAmountcurrencyEnd is null))
          and ((mc_exec.oper_tenge_amount between nvl(pAmountcurrencytengeBegin, 0) and pAmountcurrencytengeEnd) or (pAmountcurrencytengeEnd is null and nvl(mc_exec.oper_tenge_amount, 0) >= nvl(pAmountcurrencytengeBegin, 0)) or
                (mc_exec.oper_tenge_amount is null and pAmountcurrencytengeBegin is null and pAmountcurrencytengeEnd is null));
        --order by mc.receive_date;
      else
        open pResCursor for
          select mc.mess_cfm_id                as mess_cfm_id,
                 mc.receive_date               as receive_date,
                 mc_exec.cfm_code              as cfmcode,
                 mc_exec.oper_idtype           as IDTypeOperation,
                 mc_exec.oper_idview           as IDViewOperation,
                 mc_exec.oper_number           as OperationNumber,
                 mc_exec.oper_currency_code   as CurrencyCode,
                 currency_info.analyticname   as CurrencyName,
                 mc_exec.oper_currency_amount as AmountCurrency,
                 mc_exec.oper_tenge_amount     as AmountCurrencyTenge,
                 mc_exec.oper_doc_name         as DocOperationName,
                 mc_exec.oper_doc_number       as DocOperationNumber,
                 mc.send_date                  as messdate,
                 mc.status                     as status,
                 mc.mess_version               as Version,
                 mc.mess_docid                 as DocumentUniqueIdentifier,
                 mc.stage                      as workstage,
                 mc.mess_type                  as messtype,
                 mc_exec.update_version        as UpdateVersion,
                 mc_exec.release_date          as DateRelease,
                 mc.sender                     as sender,
                 mc.receiver                   as receiver,
                 mc_exec.mess_number           as messnumber,
                 mc_exec.mess_doctype          as messdoctype,
                 mc_exec.mess_reason           as messreason,
                 mc_exec.statusname            as statusname,
                 mc_exec.oper_status           as operstatus

          from mess_cfm mc
          join mess_cfm_exec mc_exec
          on (mc.mess_cfm_id = mc_exec.mess_cfm_id)
          left join analyticcodes currency_info
          on (mc_exec.oper_currency_code = currency_info.analyticcode and currency_info.analytictype = 41018)

          where exists (select rowid
                 from mess_listdic_roledest --roledest_ofm_mview
                 where ( /*listnum = pListID
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      and*/
                        softname = vmodulename and roleid = vrolegroup and statusid = mc.status and stagesid = mc.stage and doctype = mc.mess_type))

          and mc.receive_date between nvl(pReceiveDateBegin, to_date('31.12.1999', 'dd.mm.yyyy')) and nvl(pReceiveDateEnd, to_date('31.12.2999', 'dd.mm.yyyy'))
          and ((mc.send_date between nvl(pSendDateBegin, to_date('31.12.1999', 'dd.mm.yyyy')) and nvl(pSendDateEnd, to_date('31.12.2999', 'dd.mm.yyyy'))) or (mc.send_date is null and pSendDateEnd is null and pSendDateBegin is null))
          and ((mc_exec.mess_number between nvl(pMessNumberBegin, 0) and pMessNumberEnd) or (pMessNumberEnd is null and mc_exec.mess_number >= nvl(pMessNumberBegin, 0)) or (pMessNumberEnd is null))
          and ((mc_exec.mess_reason = pMessReason) or (pMessReason is null))
          and ((mc_exec.oper_number = pOperationNumber) or (pOperationNumber is null))
          and ((mc_exec.oper_status = pMessOperationStatus) or (pMessOperationStatus is null))
          and ((mc_exec.oper_currency_code = pCurrencycode) or (pCurrencycode is null))
          and ((mc_exec.oper_idtype = pIDTypeOperation) or (pIDTypeOperation is null))
          and ((mc_exec.oper_idview = pIDViewOperation) or (pIDViewOperation is null))
          and ((mc_exec.oper_currency_amount between nvl(pAmountcurrencyBegin, 0) and pAmountcurrencyEnd) or (pAmountcurrencyEnd is null and nvl(mc_exec.oper_currency_amount, 0) >= nvl(pAmountcurrencyBegin, 0)) or
                (mc_exec.oper_currency_amount is null and pAmountcurrencyBegin is null and pAmountcurrencyEnd is null))
          and ((mc_exec.oper_tenge_amount between nvl(pAmountcurrencytengeBegin, 0) and pAmountcurrencytengeEnd) or (pAmountcurrencytengeEnd is null and nvl(mc_exec.oper_tenge_amount, 0) >= nvl(pAmountcurrencytengeBegin, 0)) or
                (mc_exec.oper_tenge_amount is null and pAmountcurrencytengeBegin is null and pAmountcurrencytengeEnd is null));
        -- order by mc.receive_date;
    end case;
    return result;
  end;

  function Get_RelationMessagesList(pMessID    in number,
                                    pResCursor out sys_refcursor,
                                    pListID    in number := 1) return number is
    Result number default 0;
  begin

    open pResCursor for
      select mc.mess_cfm_id      as mess_cfm_id,
             mc.receive_date     as receive_date,
             mc_an_subj.cfm_code as cfmcode,
             mc.send_date        as messdate,
             mc.status           as status,
             mc.mess_version     as Version,
             mc.mess_docid       as DocumentUniqueIdentifier,
             mc_check.errorcode  as ErrorCode,
             mc_check.errorname  as ErrorName,
             mc.stage            as workstage,
             mc.mess_type        as messtype,
             mc.sender           as sender,
             mc.receiver         as receiver

      from mess_cfm mc

      left join mess_cfm_announce_subj mc_an_subj
      on (mc.mess_cfm_id = mc_an_subj.mess_cfm_id)
      left join mess_cfm_check mc_check
      on (mc.mess_cfm_id = mc_check.mess_cfm_id)

      where mc.mess_parentid = pMessID
      order by receive_date;

    Return(Result);
  end;

  function Get_RelationMessagesTree(pMessID    in number,
                                    pResCursor out sys_refcursor,
                                    pListID    in number := 1) return number is
    Result number default 0;
  begin

    open pResCursor for
      SELECT mc.mess_cfm_id as mess_cfm_id,
             mc.receive_date as receive_date,
             mc.send_date as messdate,
             pkg_analytic.GetMessName(mc.mess_type) as messtype,
             mc.mess_parentid as parentmessid,
             level
      from mess_cfm mc
      where level <> 1
      START WITH mess_cfm_id = pMessID
      CONNECT BY PRIOR mess_cfm_id = mc.mess_parentid
      order by level,
               receive_date;

    Return(Result);
  end;

  function Get_MessagesTypesList(pRolesGroup in number,
                                 pResCursor  out sys_refcursor) return number is
    Result      number default 0;
    vmodulename varchar2(30 char);
    vlanguage   varchar2(30 char);
  begin
    result := pkg_main.GetCurrentSettings(vlanguage, vmodulename);
    if result <> 0 then
      return result;
    end if;
    /* vmodulename:='SFM.CLIENTARM';*/

    case vlanguage
      when 'Russian' then
        open pResCursor for
          select mld.listnum,
                 mld.listname || ' (' || GetCountOfMessagesInGroup(mld.listnum, pRolesGroup, vmodulename) || ')' as listname
          from messages_list_dic mld
          where mld.tablename = 'MESSAGES_CFM'
          and rolemodule = vmodulename
          and bitand(pRolesGroup, mld.roledestination) <> 0
          order by mld.showpriority;

      when 'Kazakh' then
        open pResCursor for
          select mld.listnum,
                 mld.listname_kaz || ' (' || GetCountOfMessagesInGroup(mld.listnum, pRolesGroup, vmodulename) || ')' as listname
          from messages_list_dic mld
          where mld.tablename = 'MESSAGES_CFM'
          and rolemodule = vmodulename
          and bitand(pRolesGroup, mld.roledestination) <> 0
          order by mld.showpriority;
      else
        open pResCursor for
          select mld.listnum,
                 mld.listname || ' (' || GetCountOfMessagesInGroup(mld.listnum, pRolesGroup, vmodulename) || ')' as listname
          from messages_list_dic mld
          where mld.tablename = 'MESSAGES_CFM'
          and rolemodule = vmodulename
          and bitand(pRolesGroup, mld.roledestination) <> 0
          order by mld.showpriority;

    end case;

    Return(Result);

  exception
    when others then
      return - 2102;
  end;

  function Get_MessagesTypesListForPeriod(pRolesGroup       in number,
                                          pReceiveDateBegin in date,
                                          pReceiveDateEnd   in date,
                                          pResCursor        out sys_refcursor) return number is
    Result      number default 0;
    vmodulename varchar2(30 char);
    vlanguage   varchar2(30 char);

  begin
    result := pkg_main.GetCurrentSettings(vlanguage, vmodulename);
    if result <> 0 then
      return result;
    end if;
    /* vmodulename:='SFM.CLIENTARM';*/

    case vlanguage
      when 'Kazakh' then
        open pResCursor for
          select mld.listnum,
                 mld.listname_kaz || ' (' || GetCountOfMessagesInGroup(mld.listnum, pRolesGroup, vmodulename) || ' | ' || GetCountOfMessagesInGroupP(mld.listnum, pRolesGroup, pReceiveDateBegin, pReceiveDateEnd, vmodulename) || ')' as listname
          from messages_list_dic mld
          where mld.tablename = 'MESSAGES_CFM'
          and rolemodule = vmodulename
          and bitand(pRolesGroup, mld.roledestination) <> 0
          order by mld.showpriority;
      else
        open pResCursor for
          select mld.listnum,
                 mld.listname || ' (' || GetCountOfMessagesInGroup(mld.listnum, pRolesGroup, vmodulename) || ' | ' || GetCountOfMessagesInGroupP(mld.listnum, pRolesGroup, pReceiveDateBegin, pReceiveDateEnd, vmodulename) || ')' as listname
          from messages_list_dic mld
          where mld.tablename = 'MESSAGES_CFM'
          and rolemodule = vmodulename
          and bitand(pRolesGroup, mld.roledestination) <> 0
          order by mld.showpriority;
        Return(Result);
    end case;
  exception
    when others then
      return - 2102;
  end;

  function Get_MessagesTypesListFiltered(pRolesGroup               in number,
                                         pReceiveDateBegin         in date,
                                         pReceiveDateEnd           in date,
                                         pSendDateBegin            in date,
                                         pSendDateEnd              in date,
                                         pMessNumberBegin          in number,
                                         pMessNumberEnd            in number,
                                         pMessReason               in number,
                                         pMessOperationStatus      in number,
                                         pOperationNumber          in varchar2,
                                         pIDTypeOperation          in number,
                                         pIDViewOperation          in number,
                                         pCurrencycode             in number,
                                         pAmountcurrencyBegin      in number,
                                         pAmountcurrencyEnd        in number,
                                         pAmountcurrencytengeBegin in number,
                                         pAmountcurrencytengeEnd   in number,
                                         pResCursor                out sys_refcursor) return number is
    Result      number default 0;
    vmodulename varchar2(30 char);
    vlanguage   varchar2(30 char);
  begin
    result := pkg_main.GetCurrentSettings(vlanguage, vmodulename);
    if result <> 0 then
      return result;
    end if;

    /* vmodulename:='SFM.CLIENTARM';*/

    case vlanguage
      when 'Kazakh' then
        open pResCursor for
          select mld.listnum,
                 mld.listname_kaz || ' (' || GetCountOfMessagesInGroup(mld.listnum, pRolesGroup, vmodulename) || ' / ' ||
                 GetCountOfMessagesInGroupF(mld.listnum,
                                            pRolesGroup,
                                            pReceiveDateBegin,
                                            pReceiveDateEnd,
                                            pSendDateBegin,
                                            pSendDateEnd,
                                            pMessNumberBegin,
                                            pMessNumberEnd,
                                            pMessReason,
                                            pMessOperationStatus,
                                            pOperationNumber,
                                            pIDTypeOperation,
                                            pIDViewOperation,
                                            pCurrencycode,
                                            pAmountcurrencyBegin,
                                            pAmountcurrencyEnd,
                                            pAmountcurrencytengeBegin,
                                            pAmountcurrencytengeEnd,
                                            vmodulename) || ')' as listname
          from messages_list_dic mld
          where mld.tablename = 'MESSAGES_CFM'
          and rolemodule = vmodulename
          and bitand(pRolesGroup, mld.roledestination) <> 0
          order by mld.showpriority;
      else
        open pResCursor for
          select mld.listnum,
                 mld.listname || ' (' || GetCountOfMessagesInGroup(mld.listnum, pRolesGroup, vmodulename) || ' / ' ||
                 GetCountOfMessagesInGroupF(mld.listnum,
                                            pRolesGroup,
                                            pReceiveDateBegin,
                                            pReceiveDateEnd,
                                            pSendDateBegin,
                                            pSendDateEnd,
                                            pMessNumberBegin,
                                            pMessNumberEnd,
                                            pMessReason,
                                            pMessOperationStatus,
                                            pOperationNumber,
                                            pIDTypeOperation,
                                            pIDViewOperation,
                                            pCurrencycode,
                                            pAmountcurrencyBegin,
                                            pAmountcurrencyEnd,
                                            pAmountcurrencytengeBegin,
                                            pAmountcurrencytengeEnd,
                                            vmodulename) || ')' as listname
          from messages_list_dic mld
          where mld.tablename = 'MESSAGES_CFM'
          and rolemodule = vmodulename
          and bitand(pRolesGroup, mld.roledestination) <> 0
          order by mld.showpriority;

    end case;

    Return(Result);

  exception
    when others then
      return - 2102;
  end;

  function GetCountOfMessagesInGroup(plistid     in number,
                                     pRolesGroup in number,
                                     pModuleName in varchar2) return number is
    vcount number default 0;
  begin
    select count(*)
    into vcount
    from mess_cfm mc
    where exists (select rowid
           from mess_listdic_roledest --roledest_ofm_mview
           where (listnum = pListID and softname = pmodulename and roleid = prolesgroup and statusid = mc.status and stagesid = mc.stage and doctype = mc.mess_type));
    return vcount;
  end;

  function GetCountOfMessagesInGroupP(plistid           in number,
                                      pRolesGroup       in number,
                                      pReceiveDateBegin in date,
                                      pReceiveDateEnd   in date,
                                      pModuleName       in varchar2) return number is
    vcount number default 0;
    pDate1 date;
    pDate2 date;
  begin
    pDate1 := trunc(pReceiveDateBegin);
    pDate2 := trunc(pReceiveDateEnd) + 1;
    select count(*)
    into vcount
    from mess_cfm mc
    where mc.receive_date between pDate1 and pDate2
    and exists (select rowid
           from mess_listdic_roledest --roledest_ofm_mview
           where (listnum = pListID and softname = pmodulename and roleid = prolesgroup and statusid = mc.status and stagesid = mc.stage and doctype = mc.mess_type));
    return vcount;
  end;

  function GetCountOfMessagesInGroupF(plistid                   in number,
                                      pRolesGroup               in number,
                                      pReceiveDateBegin         in date,
                                      pReceiveDateEnd           in date,
                                      pSendDateBegin            in date,
                                      pSendDateEnd              in date,
                                      pMessNumberBegin          in number,
                                      pMessNumberEnd            in number,
                                      pMessReason               in number,
                                      pMessOperationStatus      in number,
                                      pOperationNumber          in varchar2,
                                      pIDTypeOperation          in number,
                                      pIDViewOperation          in number,
                                      pCurrencycode             in number,
                                      pAmountcurrencyBegin      in number,
                                      pAmountcurrencyEnd        in number,
                                      pAmountcurrencytengeBegin in number,
                                      pAmountcurrencytengeEnd   in number,
                                      pModuleName               in varchar2) return number is
    vcount                       number default 0;
    pReceiveDateBeginFin         date;
    pReceiveDateEndFin           date;
    pSendDateBeginFin            date;
    pSendDateEndFin              date;
    pAmountcurrencyBeginFin      number;
    pAmountcurrencytengeBeginFin number;
    pMessNumberBeginFin          number;
    --type pConditionsType is table of index by

  begin
    pReceiveDateBeginFin         := nvl(pReceiveDateBegin, to_date('31.12.1999', 'dd.mm.yyyy'));
    pReceiveDateEndFin           := nvl(pReceiveDateEnd, to_date('31.12.2999', 'dd.mm.yyyy'));
    pSendDateBeginFin            := nvl(pSendDateBegin, to_date('31.12.1999', 'dd.mm.yyyy'));
    pSendDateEndFin              := nvl(pSendDateEnd, to_date('31.12.2999', 'dd.mm.yyyy'));
    pAmountcurrencyBeginFin      := nvl(pAmountcurrencyBegin, 0);
    pAmountcurrencytengeBeginFin := nvl(pAmountcurrencytengeBegin, 0);
    pMessNumberBeginFin          := nvl(pMessNumberBegin, 0);
    --pAmountcurrencyEndFin    :=   nvl(pAmountcurrencyEnd,0);

    select /*+ ordered_predicates */
     count(*)
    into vcount
    from mess_cfm mc
    join mess_cfm_exec mc_exec
    on (mc.mess_cfm_id = mc_exec.mess_cfm_id)
    where exists (select rowid
           from mess_listdic_roledest --roledest_ofm_mview
           where (listnum = pListID and softname = pmodulename and roleid = prolesgroup and statusid = mc.status and stagesid = mc.stage and doctype = mc.mess_type))
    and mc.receive_date between pReceiveDateBeginFin and pReceiveDateEndFin
    and ((pSendDateEnd is null and pSendDateBegin is null) or (mc.send_date between pSendDateBeginFin and pSendDateEndFin))
    and ((pMessNumberEnd is null and pMessNumberBegin is null) or (pMessNumberEnd is null and mc_exec.mess_number >= pMessNumberBeginFin) or (mc_exec.mess_number between pMessNumberBeginFin and pMessNumberEnd))
    and ((pMessReason is null) or (mc_exec.mess_reason = pMessReason))
    and ((pOperationNumber is null) or (mc_exec.oper_number = pOperationNumber))
    and ((pMessOperationStatus is null) or (mc_exec.oper_status = pMessOperationStatus))
    and ((pCurrencycode is null) or (mc_exec.oper_currency_code = pCurrencycode))
    and ((pIDTypeOperation is null) or (mc_exec.oper_idtype = pIDTypeOperation))
    and ((pIDViewOperation is null) or (mc_exec.oper_idview = pIDViewOperation))
    and ((pAmountcurrencyBegin is null and pAmountcurrencyEnd is null) or (pAmountcurrencyEnd is null and mc_exec.oper_currency_amount >= pAmountcurrencyBeginFin) or
          (mc_exec.oper_currency_amount between pAmountcurrencyBeginFin and pAmountcurrencyEnd))
    and ((pAmountcurrencytengeBegin is null and pAmountcurrencytengeEnd is null) or (pAmountcurrencytengeEnd is null and mc_exec.oper_tenge_amount >= pAmountcurrencytengeBeginFin) or
          (mc_exec.oper_tenge_amount between pAmountcurrencytengeBeginFin and pAmountcurrencytengeEnd));
    return vcount;
  end;

  function Get_MessagesTypesListSettings(pResCursor out sys_refcursor) return number is
    result         number default 0;
    currRoleId     number;
    currLangName   varchar2(30 char);
    currModuleName varchar2(30 char);
  begin
    result := pkg_users.GetUsersRolesGroup(currRoleId);
    if result <> 0 then
      return result;
    end if;

    result := pkg_main.GetCurrentSettings(currLangName, currModuleName);
    if result <> 0 then
      return result;
    end if;

    open pResCursor for
      select listnum,
             statusid,
             stagesid,
             doctype
      from mess_listdic_roledest t
      where t.roleid = currRoleId
      and t.softname = currModuleName;

    return result;
  end;

  function Get_TransformedMessage(pMessID        in number,
                                  pTransformType in number,
                                  pMessOut       out CLOB) return number is
    Result          number default 0;
    pXMLSCH         xmltype;
    pXML            xmltype;
    pXMLClob        clob;
    vParId          number;
    vParId2         number;
    vParNumber      number;
    vPardate        date;
    vParNAme        varchar2(1000 char);
    vMessdate       date;
    vLastModifyDAte date;
    pTempClob       clob;

    pmesstype    number;
    pmesspartype number;
    pmessversion number;

  begin

    case pTransformType
      when 0 then
        Result := GetInternalDocument(pMessID, pMessOut);
      when 1 then
        select mc_xml.mess_xml
        into pMessOut
        from mess_cfm_xml mc_xml
        where mc_xml.mess_cfm_id = pMessID;
      when 3 then
        pmesstype := GetMessageType(pMessID);
        case pmesstype
          when 1 then

            --ФМ-1
            select mc_xml.mess_xml,
                   mv.versionid
            into pXMLClob,
                 pmessversion
            from mess_cfm mc
            join mess_cfm_xml mc_xml
            on (mc_xml.mess_cfm_id = mc.mess_cfm_id)
            join messages_versions mv
            on mc.mess_version = mv.versioncode
            where mc.mess_cfm_id = pMessID
            and mc.mess_type = 1;

            PXMLSCH := xmltype.createxml(pXMLClob);
            pXML    := PXMLSCH;
            case pmessversion
              when 1 then
                pxml := GetOuterAnnounceXML_1(PXMLSCH);
                -- when 2 then
              else
                pxml := pkg_mess_cfm_2.GetOuterAnnounceXML(PXMLSCH, pmessversion);
                --  else null;
            end case;

            select xmltransform(PXML, xmltype.createxml(mv.messxmlschema)).getclobval()
            into pMessOut
            from messages_versions mv
            where mv.mess_type = 1
            and mv.versionid = pmessversion;
          when 2 then

            --О принятии
            select mc_xml.mess_xml,
                   mc.send_date
            into pXMLClob,
                 vmessdate
            from mess_cfm mc
            join mess_cfm_xml mc_xml
            on (mc_xml.mess_cfm_id = mc.mess_cfm_id)
            where mc.mess_cfm_id = pMessID;

            result := Get_ParentIDByMessagesID(pMessID, vParId);
            if result = 0 then
              pmesspartype := GetMessageType(vParId);
              case pmesspartype
                when 1 then
                  select mc.send_date,
                         mc_an_info.mess_number,
                         mc_an_info.mess_date,
                         mc_an_subj.cfm_name
                  into vPardate,
                       vParNumber,
                       vLastModifyDAte,
                       vParNAme
                  from mess_cfm mc
                  left join mess_cfm_announce_info mc_an_info
                  on (mc.mess_cfm_id = mc_an_info.mess_cfm_id)
                  left join mess_cfm_announce_subj mc_an_subj
                  on (mc.mess_cfm_id = mc_an_subj.mess_cfm_id)
                  where mc.mess_cfm_id = vParId;
                  pTempClob := replace(pXMLClob,
                                       '</Check>',
                                       '<MessNumber>' || vParNumber || '</MessNumber>
                                     <MessDate>' || to_char(vLastModifyDAte, 'dd.mm.yyyy') || '</MessDate>
                                     <SendDate>' || to_char(vmessdate, 'dd.mm.yyyy HH24:mi:ss') || '</SendDate>
                                     <MessOwn>' || vParNAme || '</MessOwn></Check>');
                  begin
                    select xmltransform(xmltype.createxml(pTempClob), xmltype.createxml(mv.messxmlschema)).getclobval()
                    into pMessOut
                    from mess_cfm          mc,
                         messages_versions mv
                    where mc.mess_cfm_id = pMessID
                    and mv.mess_type = 2;
                  exception
                    when others then
                      Return(-4000);
                  end;
                when 5 then
                  select mc_an_info.mess_number,
                         mc.send_date
                  into vParNumber,
                       vPardate
                  from mess_cfm mc
                  left join mess_cfm_announce_info mc_an_info
                  on (mc.mess_cfm_id = mc_an_info.mess_cfm_id)
                  where mc.mess_cfm_id = vParId;
                  pTempClob := replace(pXMLClob,
                                       '</Check>',
                                       '<MessDate>' || to_char(vPardate, 'dd.mm.yyyy HH24:mi:ss') || '</MessDate>
                                       <SendDate>' || to_char(vmessdate, 'dd.mm.yyyy HH24:mi:ss') || '</SendDate></Check>');

                  begin
                    select xmltransform(xmltype.createxml(pTempClob), xmltype.createxml(mv.messxmlschema)).getclobval()
                    into pMessOut
                    from mess_cfm          mc,
                         messages_versions mv
                    where mc.mess_cfm_id = pMessID

                    and mv.mess_type = 7; --Квитанция об отклонении
                  exception
                    when others then
                      Return(-4000);
                  end;
                when 4 then
                  select mc_an_info.mess_number,
                         mc.send_date
                  into vParNumber,
                       vPardate
                  from mess_cfm mc
                  left join mess_cfm_announce_info mc_an_info
                  on (mc.mess_cfm_id = mc_an_info.mess_cfm_id)
                  where mc.mess_cfm_id = vParId;

                  --PXMLSCH:= xmltype.createxml(pXMLClob);
                  --select extract(PXMLSCH,'/Root').getclobval() into pXMLClob from dual;
                  pTempClob := replace(pXMLClob,
                                       '</Check>',
                                       '<MessDate>' || to_char(vPardate, 'dd.mm.yyyy HH24:mi:ss') || '</MessDate>
                                            <SendDate>' || to_char(vmessdate, 'dd.mm.yyyy HH24:mi:ss') || '</SendDate></Check>');

                  begin
                    select xmltransform(xmltype.createxml(pTempClob), xmltype.createxml(mv.messxmlschema)).getclobval()
                    into pMessOut
                    from mess_cfm          mc,
                         messages_versions mv
                    where mc.mess_cfm_id = pMessID
                    and mv.mess_type = 6;
                  exception
                    when others then
                      Return(-4000);
                  end;
                else

                  return - 4000; --Принтие приходит только для 2,4,5
              end case;
            end if;
          when 3 then

            --Об отклонении
            select mc_xml.mess_xml,
                   mc.send_date
            into pXMLClob,
                 vmessdate
            from mess_cfm mc
            join mess_cfm_xml mc_xml
            on (mc_xml.mess_cfm_id = mc.mess_cfm_id)
            where mc.mess_cfm_id = pMessID;

            result       := Get_ParentIDByMessagesID(pMessID, vParId);
            pmesspartype := GetMessageType(vParId);
            case pmesspartype
              when 1 then
                select mc.send_date,
                       mc_an_info.mess_number,
                       mc_an_info.mess_date,
                       mc_an_subj.cfm_name
                into vPardate,
                     vParNumber,
                     vLastModifyDAte,
                     vParNAme
                from mess_cfm mc
                left join mess_cfm_announce_info mc_an_info
                on (mc.mess_cfm_id = mc_an_info.mess_cfm_id)
                left join mess_cfm_announce_subj mc_an_subj
                on (mc.mess_cfm_id = mc_an_subj.mess_cfm_id)
                where mc.mess_cfm_id = vParId;
                pTempClob := replace(pXMLClob,
                                     '</Root>',
                                     '<MessNumber>' || vParNumber || '</MessNumber>
                                               <MessDate>' || to_char(vLastModifyDAte, 'dd.mm.yyyy') || '</MessDate>
                                               <SendDate>' || to_char(vmessdate, 'dd.mm.yyyy HH24:mi:ss') || '</SendDate>
                                               <MessOwn>' || vParNAme || '</MessOwn></Root>');

                begin
                  select xmltransform(xmltype.createxml(pTempClob), xmltype.createxml(mv.messxmlschema)).getclobval()
                  into pMessOut
                  from mess_cfm          mc,
                       messages_versions mv
                  where mc.mess_cfm_id = pMessID
                  and mv.mess_type = 3;
                exception
                  when others then
                    Return(-4000);
                end;
              when 5 then
                select mc_an_info.mess_number,
                       mc.send_date
                into vParNumber,
                     vPardate
                from mess_cfm mc
                left join mess_cfm_announce_info mc_an_info
                on (mc.mess_cfm_id = mc_an_info.mess_cfm_id)
                where mc.mess_cfm_id = vParId;
                pTempClob := replace(pXMLClob,
                                     '</Root>',
                                     '<MessDate>' || to_char(vPardate, 'dd.mm.yyyy HH24:mi:ss') || '</MessDate>
                                               <SendDate>' || to_char(vmessdate, 'dd.mm.yyyy HH24:mi:ss') || '</SendDate></Root>');
                begin
                  select xmltransform(xmltype.createxml(pTempClob), xmltype.createxml(mv.messxmlschema)).getclobval()
                  into pMessOut
                  from mess_cfm          mc,
                       messages_versions mv
                  where mc.mess_cfm_id = pMessID
                  and mv.mess_type = 7;
                exception
                  when others then
                    Return(-4000);
                end;
              else
                return - 4000;
            end case;
          when 4 then

            --Запрос ДИ
            select mc_xml.mess_xml
            into pXMLClob
            from mess_cfm mc
            join mess_cfm_xml mc_xml
            on (mc_xml.mess_cfm_id = mc.mess_cfm_id)
            where mc.mess_cfm_id = pMessID;
            result := Get_ParentIDByMessagesID(pMessID, vParId);

            select mc_an_info.mess_number,
                   mc.send_date
            into vParNumber,
                 vPardate
            from mess_cfm mc
            left join mess_cfm_announce_info mc_an_info
            on (mc.mess_cfm_id = mc_an_info.mess_cfm_id)
            where mc.mess_cfm_id = vParId;
            pTempClob := replace(pXMLClob,
                                 '</Root>',
                                 '<MessNumber>' || vParNumber || '</MessNumber>
                                    <MessDate>' || to_char(vPardate, 'dd.mm.yyyy HH24:mi:ss') || '</MessDate>
                                    </Root>');
            begin
              select xmltransform(xmltype.createxml(pTempClob), xmltype.createxml(mv.messxmlschema)).getclobval()
              into pMessOut
              from mess_cfm          mc,
                   messages_versions mv
              where mc.mess_cfm_id = pMessID
              and mv.mess_type = 4;
            exception
              when others then
                pMessOut := PXMLClob;
            end;
          when 5 then

            --Ответ на зпрос ДИ
            select mc_xml.mess_xml
            into pXMLClob
            from mess_cfm mc
            join mess_cfm_xml mc_xml
            on (mc_xml.mess_cfm_id = mc.mess_cfm_id)
            where mc.mess_cfm_id = pMessID;

            result := Get_ParentIDByMessagesID(pMessID, vParId);
            if result <> 0 then
              null;
            end if;
            result := Get_ParentIDByMessagesID(vParId, vParId2);
            if result = 0 then
              select mc_an_info.mess_number,
                     mc.send_date
              into vParNumber,
                   vPardate
              from mess_cfm mc
              left join mess_cfm_announce_info mc_an_info
              on (mc.mess_cfm_id = mc_an_info.mess_cfm_id)
              where mc.mess_cfm_id = vParId2;

              pTempClob := replace(pXMLClob,
                                   '</Root>',
                                   '<MessNumber>' || vParNumber || '</MessNumber>
                                                <MessDate>' || to_char(vPardate, 'dd.mm.yyyy HH24:mi:ss') || '</MessDate></Root>');

            else
              pTempClob := replace(pXMLClob,
                                   '</Root>',
                                   '<MessNumber>' || pkg_main.GetSimpleError(-1013) || '</MessNumber>
                                                <MessDate>' || pkg_main.GetSimpleError(-1013) || '</MessDate></Root>');
              result    := 0;
            end if;
            pmessout := null;
            select xmltransform(xmltype.createxml(pTempClob), xmltype.createxml(mv.messxmlschema)).getclobval()
            into pMessOut
            from mess_cfm          mc,
                 messages_versions mv
            where mc.mess_cfm_id = pMessID
            and mv.mess_type = 5;
            --dbms_lob.append(pXMLClob, '<MessNumber>'||vParNumber||'</MessNumber><MessDate>'||vPardate||'</MessDate>');
          when 11 then
            select mc_xml.mess_xml
            into pTempClob
            from mess_cfm_xml mc_xml
            where mc_xml.mess_cfm_id = pMessID;

            select mv.messxmlschema
            into pXMLClob
            from messages_versions mv
            where mv.mess_type = 11;
            pxml := GetOuterRegistrationData_1(xmltype.createxml(pTempClob));
            select xmltransform(pxml, xmltype.createxml(pXMLClob)).getclobval()
            into pMessOut
            from dual;
          when 12 then
            select xmltransform(xmltype.createxml(mc_xml.mess_xml), xmltype.createxml(mv.messxmlschema)).getclobval()
            into pMessOut
            from mess_cfm_xml      mc_xml,
                 messages_versions mv
            where mc_xml.mess_cfm_id = pMessID
            and mv.mess_type = 12;
          when 13 then
            select xmltransform(xmltype.createxml(mc_xml.mess_xml), xmltype.createxml(mv.messxmlschema)).getclobval()
            into pMessOut
            from mess_cfm_xml      mc_xml,
                 messages_versions mv
            where mc_xml.mess_cfm_id = pMessID
            and mv.mess_type = 13;
          when 14 then
            select xmltransform(xmltype.createxml(mc_xml.mess_xml), xmltype.createxml(mv.messxmlschema)).getclobval()
            into pMessOut
            from mess_cfm_xml      mc_xml,
                 messages_versions mv
            where mc_xml.mess_cfm_id = pMessID
            and mv.mess_type = 14;
          when 15 then

            -- встречные отчеты
            select mess_xml
            into pXMLClob
            from mess_cfm_xml
            where mess_cfm_id = pMessID; -- получаем исходный XML

            Result := pkg_rqst_cntr_cfm.rqst_cntr_transform(pXMLClob, pXMLClob); -- поллучаем подготовленый XML

            select xmltransform(xmltype.createxml(pXMLClob), xmltype.createxml(mv.messxmlschema)).getclobval()
            into pMessOut
            from messages_versions mv
            where mv.mess_type = 15;
          else
            return - 4000;
        end case;

      when 4 then
        select mx.messxml
        into pMessOut
        from messages_cfm_xml mx
        where mx.mess_cfm_id = pMessID
        and mx.transformtype = pTransformType
        and rownum = 1; --  Исправление!!!! ---
    end case;
    Return(Result);

  exception
    when NO_DATA_FOUND then
      Return(-1000);
    when others then
      dbms_output.put_line(sqlerrm);

      return - 2115; --Ошибка преобразования XML
  end;

  function Save_TransformedMessage(pMessID        in number,
                                   pTransformType in number,
                                   pMessIn        in CLOB,
                                   pExtErrorCode  in number := -1) return number is
    Result       number;
    vmessid      number;
    vSender      varchar2(50 char);
    vReceiver    varchar2(50 char);
    pMessDocDate date;
    pType        number;
    vOldStage    number;
    vOldStatus   number;
    vExistCover  number;
  begin
    Result := 0;

    insert into messages_cfm_xml
      (mess_cfm_id,
       transformtype,
       messxml,
       exterrorcode)
    values
      (pMessID,
       pTransformType,
       pMessIn,
       pExtErrorCode);
    if pTransformType = 4 then
      pType := GetMessageType(pMessID);

      if pType in (1, 5, 9, 11, 15) then
        begin
          begin
            select stage,
                   status
            into vOldStage,
                 vOldStatus
            from mess_cfm
            where mess_cfm_id = pMessID;
            if ptype = 1 then
              case
              --   when  vOldStage = 1 then -- редактируется then null;    return -510;
                when (vOldStage = 20 and vOldStatus = 20) then
                  return - 514;
                when (vOldStage = 30) then
                  return - 511;
                when (vOldStage = 40 and vOldStatus = 40) then
                  return - 513;
                when (vOldStage = 40 and vOldStatus = 50) then
                  return - 512;
                when (vOldStage = 1 and vOldStatus in (1, 10)) then
                  return - 521;
                when (vOldStage = 1 and vOldStatus = 20) then
                  return - 522;
                else
                  Result := ChangeStatus(pMessID, 30, 1);
                  UpdateMessNumber(pMessID, pMessIn);

              end case;
              --Result:=ChangeStatus(pMessID,30,1);
            else

              if (vOldStage >= 30) then
                return - 511;
              end if;
              -- Result:=ChangeStatus(pMessID,30,1);
            end if;
          exception
            when no_data_found then

              return - 1000; -- не найдено сообщение
          end;
          if pType = 5 then
            Result := ChangeStatus(pMessID, 30, 1);
            result := Get_ParentIDByMessagesID(pMessID, vMessId);
            result := ChangeStatus(vMessId, 70, 1);
          end if;
          if pType = 11 then
            Result := ChangeStatus(pMessID, 30, 1);
          end if;
          if pType = 15 then
            Result := ChangeStatus(pMessID, 30, 1);
          end if;
          result := pkg_mess_cfm_1.ExtractMessInSignedData(pMessIn, vExistCover, vSender, vReceiver, pMessDocDate);
          update mess_cfm mc
          set mc.sender    = vsender,
              mc.receiver  = vreceiver,
              mc.send_date = pmessdocdate
          where mc.mess_cfm_id = pMessID;
          -- Result:=ChangeStatus(pMessID,30,1);

        exception
          when others then
            null;
        end;

      elsif pType in (2, 3) then
        begin
          result := pkg_mess_cfm_1.ExtractMessInSignedData(pMessIn, vExistCover, vSender, vReceiver, pMessDocDate); --------
          update mess_cfm mc
          set sender    = vsender,
              receiver  = vreceiver,
              send_date = pmessdocdate
          where mc.mess_cfm_id = pMessID;
          result := ChangeStatus(pMessID, 30, 1);
        exception
          when others then
            null;
        end;
      elsif pType in (4, 8, 10) then
        begin
          result := pkg_mess_cfm_1.ExtractMessInSignedData(pMessIn, vExistCover, vSender, vReceiver, pMessDocDate); ---------
          update mess_cfm mc
          set sender    = vsender,
              receiver  = vreceiver,
              send_date = pmessdocdate
          where mc.mess_cfm_id = pMessID;

        exception
          when others then
            null;
        end;
      end if;
    end if;

    Return(Result);

  exception
    when no_data_found then

      return - 1000; --Док не найден
    when others then

      return - 2113; --Ошибка сохранения преобразованного документа
  end;

  function Transform_Message(pMessID        in number,
                             pTransformType in number,
                             pMessOut       out CLOB) return number is
    Result number default 0;
  begin

    Result := Get_TransformedMessage(pMessID, pTransformType, pMessOut);
    Return(Result);

  exception
    when NO_DATA_FOUND then
      Return(-1000);
  end;

  function ChangeStatus(pMessID     in number,
                        pWorkStage  in number,
                        pWorkStatus in number) return number is
    Result number default 0;
    vcount number default 0;
  begin

    select count(*)
    into vcount
    from work_status_dic
    where workstageid = pWorkStage
    and workstatus = pWorkStatus;
    if vcount = 1 then
      update mess_cfm mc
      set mc.status = pWorkStatus,
          mc.stage  = pWorkStage
      where mc.mess_cfm_id = pMessID;

      UpdateEnchanceStatusName(pMessID);
      pkg_audits.writemessaudit('CFM', pMessID, pWorkStage, pWorkStatus);
    else

      result := -1002; -- Не определено входящее соотношение состояния к данному этапу обработки сообщения
    end if;
    Return(Result);

  exception
    when OTHERS then
      rollback;
      Return - 540;
  end;

  function ToNextStage(pMessID number) return number is
    result number default 0;

    vOldStage         number;
    vOldStatus        number;
    vNewStage         number;
    vNewStatus        number;
    resultValidate    varchar2(4000 char);
    expresultValidate clob;
    pType             number;
    pversionid        number;
  begin
    expresultValidate := empty_clob();
    begin
      select mc.stage,
             mc.status,
             mc.mess_type,
             mv.versionid
      into vOldStage,
           vOldStatus,
           pType,
           pversionid
      from mess_cfm mc
      left join messages_versions mv
      on (mc.mess_version = mv.versioncode)
      where mc.mess_cfm_id = pMessID;
    exception
      when no_data_found then

        return - 1000; -- не найдено сообщение
    end;

    begin
      --     select * into vNewStage from (select workstageid  from work_stages_dic where workstageid>vOldStage order by workstageid) where rownum =1;
      --      if (vOldStage,vOldStatus) in (1,20)  - исключено из обработки на этапе редактирования

      --      if (vOldStage,vOldStatus) in (20,20) - исключено из обработки на этапе проверки отв сотрудником
      --      if ()
      case
        when (vOldStage = 1 and (vOldStatus = 1 or vOldStatus = 10)) or (vOldStage = 1 and vOldStatus = 30) then

          -- редактируется
          if (pType = 1 and pversionid = 1) then
            if (pkg_messverifyprocs.VerifyMessage(pMessID, resultValidate, expresultValidate) <> 0) then
              return - 900;
            end if;
          end if;
          vNewStage  := 20;
          vNewStatus := 1;
          result     := ChangeStatus(pMessID, vNewStage, vNewStatus);
        when (vOldStage = 1 and vOldStatus = 20) then
          return - 502;

        else
          return - 503;
      end case;

    exception
      when no_data_found then

        return - 500; --Данная стадия обработки последняя
    end;
    return result;
  end;

  function ToPreviousStage(pMessID number) return number is
    result     number default 0;
    vOldStage  number;
    vOldStatus number;
    vNewStage  number;
    vNewStatus number;
    vMessType  number;
  begin
    begin
      --  if vMessType not in (1,5) then
      select stage,
             status,
             mess_type
      into vOldStage,
           vOldStatus,
           vmesstype
      from mess_cfm
      where mess_cfm_id = pMessID;
    exception
      when no_data_found then

        return - 1000; -- не найдено сообщение
    end;
    begin
      /*  select * into vNewStage from (select workstageid  from work_stages_dic where workstageid<vOldStage order by workstageid DESC) where rownum =1;
       result:= ChangeStatus(pMessID,vNewStage,10);
      */
      case
        when vOldStage = 1 then
          return - 504;
        when (vOldStage = 20 and vOldStatus = 1) or (vOldStage = 1 and (vOldStatus = 30 or vOldStatus = 10)) then

          -- редактируется
          vNewStage  := 1;
          vNewStatus := 10; --Доработка
          result     := ChangeStatus(pMessID, vNewStage, vNewStatus);
        when (vOldStage = 20 and vOldStatus = 20) then
          vNewStage  := 20;
          vNewStatus := 10;
          result     := ChangeStatus(pMessID, vNewStage, vNewStatus);
          return - 505;
        when (vOldStage = 1 and vOldStatus = 20) then

          --Доработка сообщения
          vNewStage  := 1;
          vNewStatus := 10;
          result     := ChangeStatus(pMessID, vNewStage, vNewStatus);
          return - 506;

        else
          return - 503;
      end case;

    exception
      when no_data_found then

        return - 501; --Данная стадия обработки последняя.
    end;
    return result;
  end;

  function Exclude(pMessID number) return number is
    result number default 0;

    vOldStage  number;
    vOldStatus number;

  begin
    begin
      select stage,
             status
      into vOldStage,
           vOldStatus
      from mess_cfm
      where mess_cfm_id = pMessID;
    exception
      when no_data_found then

        return - 1000; -- не найдено сообщение
    end;
    begin
      if (vOldStage in (1, 20) and vOldStatus in (1, 10)) then
        -- select * into vNewStage from (select workstageid  from work_stages_dic where workstageid<vOldStage order by workstageid DESC) where rownum =1;
        result := ChangeStatus(pMessID, vOldStage, 20);
      elsif (vOldStage in (1, 20) and vOldStatus = 20) then
        result := ChangeStatus(pMessID, vOldStage, 10);

      else
        return - 507;
      end if;
    exception
      when no_data_found then

        return - 501; --Данная стадия обработки последняя.
    end;
    return result;
  end;

  function Delete_Message(pMessID number) return number is
    result number default 0;

    vOldStage  number;
    vOldStatus number;

  begin
    begin
      select stage,
             status
      into vOldStage,
           vOldStatus
      from mess_cfm
      where mess_cfm_id = pMessID;
    exception
      when no_data_found then

        return - 1000; -- не найдено сообщение
    end;
    begin
      if (vOldStage in (1, 20) and vOldStatus in (1, 10, 20)) then
        -- select * into vNewStage from (select workstag eid  from work_stages_dic where workstageid<vOldStage order by workstageid DESC) where rownum =1;
        insert into MESS_CFM_GARBAGE
          (mess_cfm_id,
           mess_type,
           mess_version,
           mess_form_guid,
           receive_date,
           truncate_date,
           mess_xml)
          select mc.mess_cfm_id,
                 mc.mess_type,
                 mc.mess_version,
                 mc.mess_form_guid,
                 mc.receive_date,
                 sysdate,
                 mc_xml.mess_xml
          from mess_cfm mc
          join mess_cfm_xml mc_xml
          on mc_xml.mess_cfm_id = mc.mess_cfm_id
          where mc.mess_cfm_id = pMessID;

        delete from mess_cfm_xml
        where mess_cfm_id = pMessID;
        delete from mess_cfm_exec
        where mess_cfm_id = pMessID;

        delete from mess_cfm_update_info
        where mess_cfm_id = pMessID;
        delete from mess_cfm_dopinf_answ
        where mess_cfm_id = pMessID;
        delete from mess_cfm_dopinf
        where mess_cfm_id = pMessID;
        delete from mess_cfm_check
        where mess_cfm_id = pMessID;

        delete from mess_cfm_announce_accounts
        where mess_cfm_id = pMessID;
        delete from mess_cfm_announce_found
        where mess_cfm_id = pMessID;
        delete from mess_cfm_announce_memb
        where mess_cfm_id = pMessID;
        delete from mess_cfm_announce_subj
        where mess_cfm_id = pMessID;
        delete from mess_cfm_announce_oper
        where mess_cfm_id = pMessID;
        delete from mess_cfm_announce_cont
        where mess_cfm_id = pMessID;
        delete from mess_cfm_announce_info
        where mess_cfm_id = pMessID;
        delete from mess_cfm
        where mess_cfm_id = pMessID;

        pkg_audits.WriteUserAudit(10, 'pkg_messages_cfm.Delete_Message', 'ID: ' || pMessID);
      else
        return - 507;
      end if;
    exception
      when others then

        return - 508; --При удалении сообщения возникли ошибки
    end;
    return result;
  end Delete_Message;


  --Возвращается только 1 ID (наиболее поздний)?
  function Get_MessagesIDByParentID(pParentID in number,
                                    pMessID   out number, /*sCur   out sys_refcursor*/
                                    pMessType in number := 4) return number is
    Result number default 0;
  begin

    select nvl(max(mc.mess_cfm_id), 0)
    into pMessID
    from mess_cfm mc
    where mc.mess_parentid = pParentID
    and mc.mess_type = pMessType;

    Return(Result);
  exception
    when no_data_found then
      pMessID := -1;

      return - 1004; --Порожденное сообщение заданного типа не найдено
  end;

  --Возвращается только 1 ID (наиболее поздний)?
  function Get_MessagesListByParentID(pParentID    in number,
                                      pMessagesCur out sys_refcursor,
                                      pMessType    in number := 4) return number is
    Result number default 0;
    vlang  varchar2(10 char) default 'Russian';
  begin
    vlang := pkg_main.GetUserLangSettings;
    open pMessagesCur for
      select mc.mess_cfm_id as mess_cfm_id,
             mc.receive_date as receive_date,
             mc_exec.cfm_code as cfmcode,
             mc_exec.oper_idtype as IDTypeOperation,
             mc_exec.oper_idview as IDViewOperation,
             mc_exec.oper_number as OperationNumber,
             mc_exec.oper_currency_code as CurrencyCode,
             currency_info.analyticname as CurrencyName,
             mc_exec.oper_currency_amount as AmountCurrency,
             mc_exec.oper_tenge_amount as AmountCurrencyTenge,
             mc_exec.oper_doc_name as DocOperationName,
             mc_exec.oper_doc_number as DocOperationNumber,
             mc.send_date as messdate,
             mc.status as status,
             mc.mess_version as Version,
             mc.mess_docid as DocumentUniqueIdentifier,
             mc.stage as workstage,
             mc.mess_type as messtype,
             mc_exec.update_version as UpdateVersion,
             mc_exec.release_date as DateRelease,
             mc.sender as sender,
             mc.receiver as receiver,
             mc_exec.mess_number as messnumber,
             mc_exec.mess_doctype as messdoctype,
             mc_exec.mess_reason as messreason,
             decode(vlang, 'Kazakh', mc_exec.statusname_kz, mc_exec.statusname) as statusname,
             mc_exec.oper_status as operstatus
      from mess_cfm mc
      join mess_cfm_exec mc_exec
      on (mc.mess_cfm_id = mc_exec.mess_cfm_id)
      left join analyticcodes currency_info
      on (mc_exec.oper_currency_code = currency_info.analyticcode and currency_info.analytictype = 41018)

      where mc.mess_parentid = pParentID
      and mc.mess_type = pMessType;

    Return(Result);
  exception
    when no_data_found then
      --pMessID := -1;

      return - 1004; --Порожденное сообщение заданного типа не найдено
  end;

  function Get_ParentIDByMessagesID(pMessID   in number,
                                    pParentID out number) return number is
    Result number default 0;
  begin

    select nvl(mc.mess_parentid, 0)
    into pParentID
    from mess_cfm mc
    where mc.mess_cfm_id = pMessID;

    if pParentID is null or pParentID = -1 then
      case GetMessageType(pMessID)
        when 4 then
          Result := -1009;
        else
          Result := -1013; --
      end case;
    end if;
    Return(Result);

  exception
    when no_data_found then
      pParentID := -1;

      return - 1000; --Сообщение не найдено
  end;

  function GetFirstNodeID(pMessID     number,
                          firstnodeid out number) return number is
    result number default 0;
  begin

    firstnodeid := pMessID;
    for prec in
    --result:=Get_ParentIDByMessagesID(childid,parentid);
     (select mc.mess_cfm_id
      from mess_cfm mc
      connect by prior mc.mess_parentid = mc.mess_cfm_id
      start with mc.mess_cfm_id = pMessID
      order by level desc) loop
      firstnodeid := prec.mess_cfm_id;
      exit;
    end loop;
    return result;
  end;

  function GetInternalDocument(pMessID  in number,
                               pMessOut out CLOB) return number is
    Result    number default 0;
    vMessCLOB CLOB;
  begin
    select mc_xml.mess_xml
    into vMessCLOB
    from mess_cfm_xml mc_xml
    where mc_xml.mess_cfm_id = pMessID;

    Result := pkg_messages_xml.Get_RootNodeFromXML(vMessCLOB, pMessOut);

    Return(Result);

  exception
    when NO_DATA_FOUND then
      Return(-1000);
  end;

  function GetMessageType(pMessID in number) return number is
    result number default 0;
  begin
    select mess_type
    into result
    from mess_cfm
    where mess_cfm_id = pMessID;
    return result;

  exception
    when no_data_found then
      return 0;
  end;

  function GenerateMessageNumber return number is
    result number;
  begin
    select GetTempMessNumb
    into result
    from dual;
    return result;
  end;

  function GenerateSendMessageNumber return number is
    result number;
  begin
    select messnumb.nextval
    into result
    from dual;
    return result;
  end;

  function IsLabelEditable(pMessID     in number,
                           labelCheck  out number,
                           labelRenew  out number,
                           labelDelete out number) return number is
    result         number default 0;
    pMessDocType   number;
    pMessDocNumber number;
    vType          number;
    vcount         number default 0;
    vcount1        number default 0;
    vStage         number;
  begin
    labelCheck  := 1;
    labelRenew  := 1;
    labelDelete := 1;
    select mc_an_info.mess_type,
           mc_an_info.mess_number,
           mc.mess_type,
           mc.stage
    into pMessDocType,
         pMessDocNumber,
         vType,
         vStage
    from mess_cfm mc
    left join mess_cfm_announce_info mc_an_info
    on (mc.mess_cfm_id = mc_an_info.mess_cfm_id)
    where mc.mess_cfm_id = pMessID;

    case pMessDocType
      when 1 then
        select iseditable
        into labelCheck
        from work_stages_dic
        where workstageid = vStage
        and messagetype = vType;
        if labelCheck = 0 then
          select count(*)
          into vcount
          from mess_cfm_announce_info mc_an_info
          where mc_an_info.mess_number = pMessDocNumber
          and mc_an_info.mess_type = 3;
          select count(*)
          into vcount1
          from mess_cfm_announce_info mc_an_info
          where mc_an_info.mess_number = pMessDocNumber
          and mc_an_info.mess_type = 4;
          if vcount1 <> 0 and vcount <> 0 then
            labelRenew  := 0;
            labelDelete := 0;
          elsif vcount = 0 then
            labelRenew := 0;
          elsif vcount = 0 then
            labelDelete := 0;
          end if;

        else
          labelRenew  := 0;
          labelDelete := 0;

        end if;
      when 2 then
        select iseditable
        into labelCheck
        from work_stages_dic
        where workstageid = vStage
        and messagetype = vType;

        --его можно было бы удалить(ели настроить OriginalDocGuid)
        labelRenew := 0;
        select count(*)
        into vcount
        from mess_cfm_announce_info mc_an_info
        where mc_an_info.mess_number = pMessDocNumber
        and mc_an_info.mess_type = 4;
        if vcount <> 0 then
          labelDelete := 0;
        end if;
      when 3 then
        select iseditable
        into labelCheck
        from work_stages_dic
        where workstageid = vStage
        and messagetype = vType;

        --его можно было бы удалить(ели настроить OriginalDocGuid)
        labelRenew := 0;
        select count(*)
        into vcount
        from mess_cfm_announce_info mc_an_info
        where mc_an_info.mess_number = pMessDocNumber
        and mc_an_info.mess_type = 4;
        if vcount <> 0 then
          labelDelete := 0;
        end if;
      when 4 then
        labelCheck := 0;

        --его можно было бы удалить(ели настроить OriginalDocGuid)
        labelRenew  := 0;
        labelDelete := 0;
    end case;

    return result;
  end;

  function GetSendPermission(pMessID in number) return number is
    result    number default 0;
    vStage    number;
    vStatus   number;
    vMessTYpe number;
    vSendDate date;
    vCount    number;
  begin
    --IsSendable:=0;
    select mc.mess_type,
           mc.stage,
           mc.status,
           mc.send_date
    into vMessTYpe,
         VStage,
         vStatus,
         vSendDate
    from mess_cfm mc
    where mc.mess_cfm_id = pMessID;

    if (vMesstype in (4, 8, 10, 12, 13, 14)) then

      return - 520; --не предусмотерно
    elsif vMesstype in (1, 5, 11) and vStatus = 20 then
      return - 522; --исключено
    elsif vMesstype in (1, 5) and vStage in (1, 10) then
      return - 521; --
    elsif vMesstype in (1, 5, 11) and vStage = 30 then

      -- за сутки проверить квитанцию!!!!!
      return - 523;
    elsif vMesstype = 1 and vStage = 40 then
      return - 524;
    elsif vMesstype in (5, 11) and vStage = 40 then
      return - 529;
    elsif vMesstype = 1 and vStage = 50 then
      return - 525;
    elsif vMesstype in (5, 11) and vStage = 50 then
      return - 530;
    elsif vMesstype = 9 and vStage = 30 then
      return - 523;
    elsif vMesstype = 3 and vSendDate is not null then

      return - 527; --данное сообщение уже было отклонено
    elsif vMesstype in (1, 5) then
      select count(*)
      into vCount
      from mess_cfm mc
      where mc.mess_type = 14;
      if vCount = 0 then

        return - 532; --СФМ не зарегистрирован. Сформируйте и отправьте запрос регистрации, дождитеь прихода извещения об одобрении регистрации
      end if;
    end if;
    return result;
  exception
    when no_data_found then
      return - 1000;

      return result;
  end;

  function GetEditableField(pMessID number) return number is
    vType   number;
    vStage  number;
    vStatus number;
    -- vMessDocType number;
    piseditable number default 1;

  begin

    --result:=pkg_users.GetUsersRolesGroup(vrolegroup);
    -- vrolegroup:=4;

    select mc.mess_type,
           mc.stage,
           mc.status
    into vType,
         vStage,
         vStatus
    from mess_cfm mc
    where mc.mess_cfm_id = pMessID; --and mc_xml.mess_cfm_id = pMessID and mc_an.mess_cfm_id = pMessID;

    select iseditable
    into pIsEditable
    from work_stages_dic
    where workstageid = vStage
    and messagetype = vType;
    if piseditable = 1 then
      -- if vstatus=10 and bitand(vrolegroup,);
      --   if bitand(vRoleGroup,4) = 0 then piseditable:=0;
      if vStatus = 20 then
        piseditable := 0;
      end if;
    end if;

    return piseditable;
  exception
    when NO_DATA_FOUND then


      Return 1; --Стадия обработки сообщения или его тип задан неверно
  end;

  function GetMessNumber(pMessID in number) return number is
    result  number default 0;
    vmessid number default 0;

  begin
    result := GetFirstNodeID(pMessID, vmessid);
    select mc_an_info.mess_number
    into result
    from mess_cfm mc
    join mess_cfm_announce_info mc_an_info
    on (mc.mess_cfm_id = mc_an_info.mess_cfm_id)
    where mc.mess_cfm_id = vmessid
    and mc.mess_type = 1;

    return result;
  exception
    when no_data_found then
      return null;
    when others then
      return null;
  end;

  function GetMessOperNumber(pMessID in number) return varchar2 is
    result  varchar2(1000 char);
    vmessid number default 0;
  begin
    result := GetFirstNodeID(pMessID, vmessid);
    select mc_an_oper.oper_number
    into result
    from mess_cfm mc
    join mess_cfm_announce_oper mc_an_oper
    on (mc.mess_cfm_id = mc_an_oper.mess_cfm_id)
    where mc.mess_cfm_id = vmessid
    and mc.mess_type = 1;

    return result;
  exception
    when no_data_found then
      return null;
    when others then
      return null;
  end;

  function GetCFMCodeNumber(pMessID in number) return number is
    result  number default 0;
    vmessid number default 0;

  begin
    result := GetFirstNodeID(pMessID, vmessid);
    select mc_an_subj.cfm_code
    into result
    from mess_cfm mc
    join mess_cfm_announce_subj mc_an_subj
    on (mc.mess_cfm_id = mc_an_subj.mess_cfm_id)
    where mc.mess_cfm_id = vmessid
    and mc.mess_type = 1;

    return result;
  exception
    when no_data_found then
      return null;
    when others then
      return null;
  end;

  function GetCFMName(pMessID in number) return varchar2 is
    result  varchar2(2000);
    vmessid number default 0;
    KFM constant varchar2(100 char) := 'Комитет финансового мониторинга';
    ptype    number;
    ppartype number default 0;
  begin
    ptype := GetMessageType(pMessID);
    if ptype in (4, 8, 10) then
      return KFM;
    else
      if ptype in (2, 3) then
        ppartype := GetMessageType(pMessID);
        if ppartype in (1, 5) then
          return KFM;
        end if;
      end if;
      result := GetFirstNodeID(pMessID, vmessid);

      select nvl(mc_an_subj.cfm_name, mc.sender)
      into result
      from mess_cfm mc
      left join mess_cfm_announce_subj mc_an_subj
      on (mc.mess_cfm_id = mc_an_subj.mess_cfm_id)
      where mc.mess_cfm_id = vmessid
      and mc.mess_type = 1;
    end if;

    return result;
  exception
    when no_data_found then
      return null;
  end;

  function GetMessReason(messreason   in number,
                         suspicreason in number,
                         messversion  varchar2) return number is
    result number := 0;
  begin
    case messversion
      when '52FF370C-8B35-42DA-9B9A-B48F44F10C6F' then
        result := messreason;
      when ('E5505800-A19B-47ED-AF5F-9A8BAF169E52') then
        if messreason = 1 then
          result := 1;
        elsif messreason = 2 then
          if suspicreason = 3 then
            result := 2;
          elsif suspicreason = 1 then
            result := 3;
          elsif suspicreason = 2 then
            result := 3;
          end if;
        end if;
      when 'C6EC3F8D-2BE1-46A9-841B-5CF1130F0A12' then
        if messreason = 1 then
          result := 1;
        elsif messreason = 2 then
          if suspicreason = 3 then
            result := 2;
          elsif suspicreason = 1 then
            result := 3;
          elsif suspicreason = 2 then
            result := 3;
          end if;
        end if;
      else
        null;
    end case;
    return result;
  end;

  function GetStatusDescription(pstatus in number,
                                pstage  in number,
                                ptype   in number) return nvarchar2 is
    vstatusname nvarchar2(120);
    vlangname   varchar2(30 char);
  begin
    vlangname := pkg_main.GetUserLangSettings;
    case vlangname
      when 'Russian' then
        vstatusname := GetStatusDescriptionRus(pstatus, pstage, ptype);

      when 'Kazakh' then
        vstatusname := GetStatusDescriptionKz(pstatus, pstage, ptype);
        /*when 'English' then
        select descr_en
          into vstatusname
          from messages_list_descr
         where tablename = vMessTAble
           and stagesid = pstage
           and statusid = pstatus
           and doctype = ptype;*/

      else
        vstatusname := GetStatusDescriptionRus(pstatus, pstage, ptype);
    end case;
    return vstatusname;
  exception
    when no_data_found then
      return null;

  end;
  function GetStatusDescriptionRus(pstatus in number,
                                   pstage  in number,
                                   ptype   in number) return varchar2 is
    vstatusname nvarchar2(120);
    vMessTAble  varchar2(15 char) := 'MESSAGES_CFM';

  begin
    select descr_rus
    into vstatusname
    from messages_list_descr
    where tablename = vMessTAble
    and stagesid = pstage
    and statusid = pstatus
    and doctype = ptype;
    return vstatusname;
  exception
    when no_data_found then
      return null;

  end;

  function GetStatusDescriptionKz(pstatus in number,
                                  pstage  in number,
                                  ptype   in number) return nvarchar2 is
    vstatusname nvarchar2(120);
    vMessTAble  varchar2(15 char) := 'MESSAGES_CFM';
  begin
    select descr_kaz
    into vstatusname
    from messages_list_descr
    where tablename = vMessTAble
    and stagesid = pstage
    and statusid = pstatus
    and doctype = ptype;
    return vstatusname;
  exception
    when no_data_found then

      return null;

  end;

  function GetStatusDescriptionByID(pMessID in number) return varchar2 is
    result varchar2(1000 char);
  begin
    select GetStatusDescription(status, stage, mess_type)
    into result
    from mess_cfm
    where mess_cfm_id = pMessID;
    return result;
  exception
    when no_data_found then
      return - 1000;
    when others then
      return - 1;
  end;

  function GetcountOfFounders(pMessID   number,
                              pmemberid number) return number is
    result number default 0;
  begin
    select count(*)
    into result
    from mess_cfm_announce_found mf
    where mf.mess_cfm_id = pMessID
    and mf.member_id = pmemberid;
    return result;
  end;

  procedure InsertEnchanceData(pMessID number) is
  begin
    insert into mess_cfm_exec
      (mess_cfm_id,
       cfm_code,
       oper_idtype,
       oper_idview,
       oper_number,
       oper_currency_code,
       oper_currency_amount,
       oper_tenge_amount,
       oper_doc_name,
       oper_doc_number,
       update_version,
       release_date,
       mess_number,
       mess_doctype,
       mess_reason,
       statusname,
       statusname_kz,
       oper_status,
       cfm_name)
      select mc.mess_cfm_id,
             pkg_messages_cfm.GetCFMCodeNumber(mc.mess_cfm_id),
             mc_an_oper.oper_idtype,
             mc_an_oper.oper_idview,
             pkg_messages_cfm.GetMessOperNumber(mc.mess_cfm_id),
             mc_an_oper.oper_currency_code,
             mc_an_oper.oper_currency_amount,
             mc_an_oper.oper_tenge_amount,
             mc_an_oper.oper_reason,
             mc_an_oper.oper_doc_date || ' №' || mc_an_oper.oper_doc_number,
             mc_up_info.update_version,
             mc_up_info.release_date,
             pkg_messages_cfm.GetMessNumber(mc.mess_cfm_id),
             mc_an_info.mess_type,
             pkg_messages_cfm.GetMessReason(mc_an_info.mess_reason_code, mc_an_info.mess_suspic_code, mc.mess_version),
             pkg_messages_cfm.GetStatusDescriptionRUS(mc.status, mc.stage, mc.mess_type),
             pkg_messages_cfm.GetStatusDescriptionKZ(mc.status, mc.stage, mc.mess_type),
             mc_an_info.mess_oper_status,
             null
      from mess_cfm mc
      left join mess_cfm_announce_info mc_an_info
      on (mc.mess_cfm_id = mc_an_info.mess_cfm_id)
      left join mess_cfm_announce_oper mc_an_oper
      on (mc.mess_cfm_id = mc_an_oper.mess_cfm_id)
      left join mess_cfm_update_info mc_up_info
      on (mc.mess_cfm_id = mc_up_info.mess_cfm_id)
      where mc.mess_cfm_id = pMessID;

  end;

  procedure UpdateEnchanceData(pMessID number) is
    pmess_cfm_exec_row mess_cfm_exec%rowtype default null;
  begin
    select mc.mess_cfm_id,
           pkg_messages_cfm.GetCFMCodeNumber(mc.mess_cfm_id),
           mc_an_oper.oper_idtype,
           mc_an_oper.oper_idview,
           pkg_messages_cfm.GetMessOperNumber(mc.mess_cfm_id),
           mc_an_oper.oper_currency_code,
           mc_an_oper.oper_currency_amount,
           mc_an_oper.oper_tenge_amount,
           mc_an_oper.oper_reason,
           mc_an_oper.oper_doc_date || ' №' || mc_an_oper.oper_doc_number,
           mc_up_info.update_version,
           mc_up_info.release_date,
           pkg_messages_cfm.GetMessNumber(mc.mess_cfm_id),
           mc_an_info.mess_type,
           pkg_messages_cfm.GetMessReason(mc_an_info.mess_reason_code, mc_an_info.mess_suspic_code, mc.mess_version),
           pkg_messages_cfm.GetStatusDescriptionRUS(mc.status, mc.stage, mc.mess_type),
           pkg_messages_cfm.GetStatusDescriptionKZ(mc.status, mc.stage, mc.mess_type),
           mc_an_info.mess_oper_status,
           null
    into pmess_cfm_exec_row
    from mess_cfm mc
    left join mess_cfm_announce_info mc_an_info
    on (mc.mess_cfm_id = mc_an_info.mess_cfm_id)
    left join mess_cfm_announce_oper mc_an_oper
    on (mc.mess_cfm_id = mc_an_oper.mess_cfm_id)
    left join mess_cfm_update_info mc_up_info
    on (mc.mess_cfm_id = mc_up_info.mess_cfm_id)
    where mc.mess_cfm_id = pMessID;

    update mess_cfm_exec
    set cfm_code              = pmess_cfm_exec_row.cfm_code,
        oper_idtype           = pmess_cfm_exec_row.oper_idtype,
        oper_idview           = pmess_cfm_exec_row.oper_idview,
        oper_number           = pmess_cfm_exec_row.oper_number,
        oper_currency_code   = pmess_cfm_exec_row.oper_currency_code,
        oper_currency_amount = pmess_cfm_exec_row.oper_currency_amount,
        oper_tenge_amount     = pmess_cfm_exec_row.oper_tenge_amount,
        oper_doc_name         = pmess_cfm_exec_row.oper_doc_name,
        oper_doc_number       = pmess_cfm_exec_row.oper_doc_number,
        update_version        = pmess_cfm_exec_row.update_version,
        release_date          = pmess_cfm_exec_row.release_date,
        mess_number           = pmess_cfm_exec_row.mess_number,
        mess_doctype          = pmess_cfm_exec_row.mess_doctype,
        mess_reason           = pmess_cfm_exec_row.mess_reason,
        statusname            = pmess_cfm_exec_row.statusname,
        statusname_kz         = pmess_cfm_exec_row.statusname_kz,
        cfm_name              = pmess_cfm_exec_row.cfm_name,
        oper_status           = pmess_cfm_exec_row.oper_status
    where mess_cfm_id = pMessID;
  end;

  procedure UpdateEnchanceStatusName(pMessID number) is
    pmess_cfm_exec_row_statusname mess_cfm_exec.statusname%type;
    Pstatusname_kz                mess_cfm_exec.statusname_kz%type;
  begin
    select pkg_messages_cfm.GetStatusDescriptionRUS(mc.status, mc.stage, mc.mess_type),
           pkg_messages_cfm.GetStatusDescriptionKz(mc.status, mc.stage, mc.mess_type)
    into pmess_cfm_exec_row_statusname,
         Pstatusname_kz
    from mess_cfm mc
    where mc.mess_cfm_id = pMessID;

    update mess_cfm_exec
    set statusname    = pmess_cfm_exec_row_statusname,
        statusname_kz = Pstatusname_kz
    where mess_cfm_id = pMessID;

  end;

  function GetOuterAnnounceXML_1(PXMLSCH xmltype) return xmltype is
    pXML    xmltype;
    pXMLOut xmltype;
  begin
    begin
      select updateXml(PXMLSCH,
                       'Root/PersonalData/OrganisationCode/text()',
                       pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/PersonalData/OrganisationCode/text()'), 1005),
                       'Root/MessageInformation/IDViewOperation/text()',
                       pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/MessageInformation/IDViewOperation/text()'), 1004),
                       'Root/MessageInformation/IDTypeOperation/text()',
                       pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/MessageInformation/IDTypeOperation/text()'), 1017),
                       'Root/MessageInformation/CurrencyCode/text()',
                       pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/MessageInformation/CurrencyCode/text()'), 1018),
                       'Root/MessageInformation/SubmissionsBasises/text()',
                       pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/MessageInformation/SubmissionsBasises/text()'), 1011),
                       'Root/MessageInformation/SuspicionsCriterians/text()',
                       pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/MessageInformation/SuspicionsCriterians/text()'), 1012),
                       'Root/MessageInformation/ReportsType/text()',
                       pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/MessageInformation/ReportsType/text()'), 1009),
                       'Root/MessageInformation/OperationStatuses/text()',
                       pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/MessageInformation/OperationStatuses/text()'), 1010)
                       --  'Root/ParticipantsList/Participant[1]/ParticipantOperation/text()',
                       --        pkg_analytic.GetDescription(extractvalue(PXMLSCH,'Root/ParticipantsList/Participant[1]/ParticipantOperation/text()'),1013)
                       )
      into pXML
      from dual;
    exception
      when others then
        pXML := PXMLSCH;
    end;
    pXMLOut := pXML;
    begin
      select updateXml(pXMLOut,
                       'Root/MessageInformation/SuspicionsCriterians/text()',
                       pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/MessageInformation/SuspicionsCriterians/text()'), 1012),
                       'Root/MessageInformation/SuspicionFirst/text()',
                       pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/MessageInformation/SuspicionFirst/text()'), 1008),
                       'Root/MessageInformation/SuspicionSecond/text()',
                       pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/MessageInformation/SuspicionSecond/text()'), 1008),
                       'Root/MessageInformation/SuspicionThird/text()',
                       pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/MessageInformation/SuspicionThird/text()'), 1008)
                       --  'Root/ParticipantsList/Participant[1]/ParticipantOperation/text()',
                       --        pkg_analytic.GetDescription(extractvalue(PXMLSCH,'Root/ParticipantsList/Participant[1]/ParticipantOperation/text()'),1013)
                       )
      into pXML
      from dual;
    exception
      when others then
        pXML := pXMLOut;
    end;
    return pXML;
  end;

  function GetOuterRegistrationData_1(PXMLSCH xmltype) return xmltype is
    pXML    xmltype;
    pXMLOut xmltype;
    i       number;
  begin
    begin
      select updateXml(PXMLSCH,
                       'Root/OrganisationData/CfmCode/text()',
                       pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/OrganisationData/CfmCode/text()'), 31005),
                       'Root/OrganisationData/OpfCode/text()',
                       pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/OrganisationData/OpfCode/text()'), 41003),
                       'Root/OrganisationData/AdditionalAcData[@IsAc=''True'']/DocumentIdentity/text()',
                       pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/OrganisationData/AdditionalAcData[@IsAc=''True'']/DocumentIdentity/text()'), 40100)
                       --  'Root/ParticipantsList/Participant[1]/ParticipantOperation/text()',
                       --        pkg_analytic.GetDescription(extractvalue(PXMLSCH,'Root/ParticipantsList/Participant[1]/ParticipantOperation/text()'),1013)
                       )
      into pXML
      from dual;
    exception
      when others then
        pXML := PXMLSCH;
    end;
    pXMLOut := pXML;
    i       := 1;
    begin
      loop
        if (xmltype.existsNode(pxml, 'Root/OrganisationData/Founders/Founder[' || to_char(i) || ']') = 1) then
          select updateXml(pXMLOut,
                           'Root/OrganisationData/Founders/Founder[' || to_char(i) || ']/FounderType/text()',
                           pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/OrganisationData/Founders/Founder[' || to_char(i) || ']/FounderType/text()'), 10),
                           'Root/OrganisationData/Founders/Founder[' || to_char(i) || ']/OpfCode/text()',
                           pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/OrganisationData/Founders/Founder[' || to_char(i) || ']/OpfCode/text()'), 41003),
                           'Root/OrganisationData/Founders/Founder[' || to_char(i) || ']/ResidenceCode/text()',
                           pkg_analytic.GetDescription(extractvalue(PXMLSCH, 'Root/OrganisationData/Founders/Founder[' || to_char(i) || ']/ResidenceCode/text()'), 41019))
          into pXML
          from dual;
        else
          exit;
        end if;
        pXMLOut := pxml;
        i       := i + 1;
      end loop;
    exception
      when others then
        pXML := pXMLOut;
    end;
    return pXML;
  end;
  --------------------------------------------------------------------------------
  --
  procedure Copy_Info(pMessID in number) as
    vUser     users%rowtype;
    vPassport passport_cfm%rowtype;
  begin
    select *
    into vUser
    from users u
    where u.userid = pkg_users.GetCurrentUserId;

    UPDATE mess_cfm_announce_cont
    SET contact_secondname = vUser.lastname,
        contact_firstname  = vUser.firstname,
        contact_middlename = vUser.middlename,
        contact_job        = vUser.jobname,
        contact_phone      = vUser.phones,
        contact_email      = vUser.emails
    WHERE MESS_CFM_ID = pMessID;

    select *
    into vPassport
    from passport_cfm;

    UPDATE mess_cfm_announce_subj
    SET CFM_CODE      = vPassport.CFMCODE,
        CFM_NAME      = vPassport.FULLNAME,
        CFM_TAXCODE   = vPassport.TAXCODE,
        CFM_MAINCODE  = vPassport.MAINCODE,
        CFM_SEAT_AREA = vPassport.REG_AREA,

        CFM_SEAT_REGION = vPassport.REG_DISTRICT,
        CFM_SEAT_SITY   = vPassport.REG_CITY,
        CFM_SEAT_STREET = vPassport.REG_STREET,
        CFM_SEAT_HOUSE  = vPassport.REG_HOUSE,
        CFM_SEAT_OFFICE = vPassport.REG_OFFICE,

        CFM_ISAC          = vPassport.PASSPORT_STATUS,
        CFM_SEAT_POSTCODE = vPassport.REG_POSTINDEX,
        CFM_DOC_TYPECODE  = vPassport.AC_DOC_TYPECODE,
        CFM_DOC_NUMBER    = vPassport.AC_DOC_NUMBER,
        CFM_DOC_SERIES    = vPassport.AC_DOC_SERIES,
        CFM_DOC_WHOM      = vPassport.AC_DOC_WHOM,

        CFM_DOC_DATE         = vPassport.AC_DOC_DATE,
        CFM_SEAT_AREA_CODE   = vPassport.REG_AREA_CODE,
        CFM_SEAT_REGION_CODE = vPassport.REG_DISTRICT_CODE,
        CFM_SEAT_SITY_CODE   = vPassport.REG_CITY_CODE,
        CFM_SEAT_STREET_CODE = vPassport.REG_STREET_CODE,

        CFM_SEAT_HOUSE_CODE  = vPassport.REG_HOUSE_CODE,
        CFM_SEAT_OFFICE_CODE = vPassport.REG_OFFICE_CODE,
        CFM_UR_OPF_CODE      = vPassport.ORGCODE,
        CFM_AC_FIRSTNAME     = vPassport.FIRSTNAME,
        CFM_AC_MIDDLENAME    = vPassport.MIDDLENAME,
        CFM_AC_SECONDNAME    = vPassport.SECONDNAME
    WHERE MESS_CFM_ID = pMessID;
  end;
  --

  --создает новый пустой документ и заполняет его константами
  function Prepare_Save(pMessID out number) return number as
    pMessXML CLOB;

    result          number default 0;
    vCount          number default 0;

    vRes            number default 0; -- Временный результат промежуточных проверок
    vMess_cfm       mess_cfm%rowtype; -- Строка ключевой информации

    vMessId         number; -- ID связанного сообщения
    vMessageVersion varchar2(50 char); -- Версия сообщения (из XML)
    vMessageGuid    varchar2(50 char); -- Guid сообщения (из XML)
    vCFMMessageDoc  varchar2(50 char); -- Версия оригинального сообщения (из XML)
    vXML            xmltype;

    vRootNode       clob; -- Сообщение без пакета
    pVersionId      number; -- ID версии сообщения
  begin

    --сбор сообщения
    pMessXML := PKG_MESS_CFM_2_1.mess_cfm_2_empty_xml().getclobval();

    --Распаковка из конверта
    result := pkg_messages_xml.Get_RootNodeFromXML(pMessXML, vRootNode);
    if result <> 0 then
      return result; -- -305 или -307 (not valid или узел не найден)
    end if;

    vXML := XMLTYPE.createxml(vRootNode); --Главная ветка


    --Преобразование, проверка и сохранение версии документа
    Result := pkg_messages_xml.Get_FieldFromXML(vXML, 'Version', vMessageVersion);
    if Result <> 0 then
      return Result;
    end if;
    vres := pkg_common.GetFormattedGuid(vMessageVersion, vMess_cfm.Mess_Version);
    if vres <> 0 then

      return - 1301; -- Неверный формат идентификационного номера версии документа
    else
      begin
        select mv.mess_type,
               versionid
        into vMess_cfm.Mess_Type,

             pVersionId --Тип сообщения
        from messages_versions mv
        where mv.versioncode = vMess_cfm.Mess_Version;
      exception
        when others then

          return - 306; --Неверная версия сообщения. Сообщение не сохранено.
          vMess_cfm.Mess_Type := -1;
      end;
    end if;


    --Преобразование, проверка и сохранение GUID документа
    Result := pkg_messages_xml.Get_FieldFromXML(vXML, 'DocumentUniqueIdentifier', vMess_cfm.Mess_Docid);
    if Result <> 0 then
      return Result;
    end if;
    vres := pkg_common.GetFormattedGuid(vMess_cfm.Mess_Docid, vMess_cfm.Mess_Form_Guid);
    if vres <> 0 then

      return - 1302; --  Неверный формат идентификационного номера документа
    else
      select count(*)
      into vcount
      from mess_cfm
      where Mess_Form_Guid = vMess_cfm.Mess_Form_Guid;
      if vcount > 0 then

        return - 1003; --Сообщение с данным уникальным идентификатором  уже находится в базе
      end if;
    end if;

    --Сохранение в основной таблице
    vMess_cfm.Status       := 1;
    vMess_cfm.Stage        := 1;
    vMess_cfm.Receive_Date := sysdate;
    vMess_cfm.Ext_Err_Code := -1;
    insert into mess_cfm
    values vMess_cfm
    returning mess_cfm_id into pMessID;
    insert into mess_cfm_xml
      (mess_cfm_id,
       mess_xml)
    values
      (pMessID,
       vRootNode);
    --commit;

    result := pkg_messages_cfm.ChangeStatus(pMessID, 1, 1);
    result := pkg_mess_cfm_2.parseAnnounceXML(pMessID, 3);
    pkg_messages_cfm.InsertEnchanceData(pMessID);

    Copy_Info(pMessID);
    return result;
  exception
    when others then
      pMessID := null;
      --dbms_output.put_line(sqlerrm);

      return - 2100; --Ошибка операции сохранения
  end;

  --отменяет создание нового документа удаляя документ
  function Cancel_Save(pMessID in number) return number as
  begin
    delete from mess_cfm_announce_info
    where mess_cfm_id = pMessID;
    delete from mess_cfm_announce_cont
    where mess_cfm_id = pMessID;
    delete from mess_cfm_announce_found
    where mess_cfm_id = pMessID;
    delete from mess_cfm_announce_memb
    where mess_cfm_id = pMessID;
    delete from mess_cfm_announce_oper
    where mess_cfm_id = pMessID;
    delete from mess_cfm_announce_subj
    where mess_cfm_id = pMessID;
    delete from mess_cfm_announce_accounts
    where mess_cfm_id = pMessID;
    delete from mess_cfm_xml
    where mess_cfm_id = pMessID;
    delete from mess_cfm_exec
    where mess_cfm_id = pMessID;
    delete from mess_cfm
    where mess_cfm_id = pMessID;

    return 0;

  exception
    when others then

      return - 2102; --Ошибка операции отмены создания документа
  end;

  --подготавливает редактирование нового документа, заполняя необходимые контснаты
  function Prepare_Update(pMessID in number) return number as
    pMessXML CLOB;

    Result          number default 0;
    vcount          number default 0;

    vRes            number default 0; -- Временный результат промежуточных проверок
    vMess_cfm       mess_cfm%rowtype; -- Строка ключевой информации
    vMessageVersion varchar2(50 char); -- Версия сообщения (из XML)
    vCFMMessageDoc  varchar2(50 char); -- Версия оригинального сообщения (из XML)
    vMessageGuid    varchar2(50 char); -- Guid сообщения (из XML)
    vMessGuid       varchar2(50 char); -- Guid сообщения (из БД)
    vXML            xmltype;


    vRootNode       clob; -- Сообщение без пакета
    vStage          number; -- Стадия обработки сообщения
    vStatus         number; -- Статус сообщения
    vMessType       number; -- Тип сообщения
    pversionid      number; -- ID версии сообщения

    l_mess_number varchar(255);

    vUser users%rowtype;
  begin
    select *
    into vUser
    from users u
    where u.userid = pkg_users.GetCurrentUserId;

    UPDATE mess_cfm_announce_cont
    SET contact_secondname = vUser.lastname,
        contact_firstname  = vUser.firstname,
        contact_middlename = vUser.middlename,
        contact_job        = vUser.jobname,
        contact_phone      = vUser.phones,
        contact_email      = vUser.emails
    WHERE MESS_CFM_ID = pMessID;

    Result := 0;

    update mess_cfm_announce_oper
    set oper_refers_count =
        (select count(*)
         from mess_cfm_refer
         where mess_cfm_id = pMessID)
    where mess_cfm_id = pMessID;

    select count(*)
    into vcount
    from mess_cfm
    where mess_cfm_id = pMessID;
    if vcount = 0 then

      return - 1000; --Сообщение не найдено
    end if;

    select mc.mess_type,
           mc.status,
           mc.stage,
           mc.mess_form_guid
    into vMessType,
         vStatus,
         vStage,
         vMessGuid
    from mess_cfm mc
    where mess_cfm_id = pMessID;


    --сбор сообщения
    pMessXML := PKG_MESS_CFM_2_1.mess_cfm_2_xml(pMessID).getclobval();

    --Распаковка из конверта
    result := pkg_messages_xml.Get_RootNodeFromXML(pMessXML, vRootNode);
    if result <> 0 then
      return result; -- -305 или -307 (not valid или узел не найден)
    end if;

    vXML := xmltype.createxml(vRootNode);


    --Преобразование, проверка и сохранение версии документа
    Result := pkg_messages_xml.Get_FieldFromXML(vXML, 'Version', vMessageVersion);
    if Result <> 0 then
      return Result;
    end if;
    vres := pkg_common.GetFormattedGuid(vMessageVersion, vMess_cfm.Mess_Version);
    if vres <> 0 then

      return - 1301; -- Неверный формат идентификационного номера версии документа
    else
      begin
        select mv.mess_type,
               versionid
        into vMess_cfm.Mess_Type,

             pVersionId --Тип сообщения
        from messages_versions mv
        where mv.versioncode = vMess_cfm.Mess_Version;
      exception
        when others then

          return - 306; --Неверная версия сообщения. Сообщение не сохранено.
          vMess_cfm.Mess_Type := -1;
      end;
    end if;


    --Преобразование, проверка и сохранение GUID документа
    Result := pkg_messages_xml.Get_FieldFromXML(vXML, 'DocumentUniqueIdentifier', vMess_cfm.Mess_Docid);
    if Result <> 0 then
      return Result;
    end if;
    vres := pkg_common.GetFormattedGuid(vMess_cfm.Mess_Docid, vMess_cfm.Mess_Form_Guid);
    if vres <> 0 then

      return - 1302; --  Неверный формат идентификационного номера документа
    else

      ---!!!!!!!!!!!!!!!!!!Если будет доп столбец, то проверять его!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

      if vMess_cfm.Mess_Form_Guid <> vMessGuid then
        return - 1011;

        --!!!!!!!!Оставить вторую ветку, если будет дополнительное поле
      end if;
    end if;

    update mess_cfm_xml
    set mess_xml = vRootNode
    where mess_cfm_id = pMessID;
    commit;

    pkg_messages_cfm.UpdateEnchanceData(pMessID);
    return result;

  exception
    when others then
      return - 2101;
  end;

  --сохраняет отредактированные данные
  function Update_Message(pMessID in number) return number as
    pMessXML CLOB;

    Result          number default 0;
    vcount          number default 0;

    vRes            number default 0; -- Временный результат промежуточных проверок
    vMess_cfm       mess_cfm%rowtype; -- Строка ключевой информации
    vMessageVersion varchar2(50 char); -- Версия сообщения (из XML)
    vCFMMessageDoc  varchar2(50 char); -- Версия оригинального сообщения (из XML)
    vMessageGuid    varchar2(50 char); -- Guid сообщения (из XML)
    vMessGuid       varchar2(50 char); -- Guid сообщения (из БД)
    vXML            xmltype;


    vRootNode       clob; -- Сообщение без пакета
    vStage          number; -- Стадия обработки сообщения
    vStatus         number; -- Статус сообщения
    vMessType       number; -- Тип сообщения
    pversionid      number; -- ID версии сообщения

    l_mess_number varchar(255);
  begin
    Result := 0;

    select mess_number
    into l_mess_number
    from mess_cfm_announce_info
    where mess_cfm_id = pMessID;

    if (l_mess_number is null) or (trim(l_mess_number) = '') then
      begin
        update mess_cfm_announce_info
        set mess_number = pkg_messages_cfm.GetTempMessNumb
        where mess_cfm_id = pMessID;
      end;
    end if;

    update mess_cfm_announce_oper
    set oper_refers_count =
        (select count(*)
         from mess_cfm_refer
         where mess_cfm_id = pMessID)
    where mess_cfm_id = pMessID;

    select count(*)
    into vcount
    from mess_cfm
    where mess_cfm_id = pMessID;
    if vcount = 0 then

      return - 1000; --Сообщение не найдено
    end if;

    select mc.mess_type,
           mc.status,
           mc.stage,
           mc.mess_form_guid
    into vMessType,
         vStatus,
         vStage,
         vMessGuid
    from mess_cfm mc
    where mess_cfm_id = pMessID;

    if (vStage >= 30) then
      return - 531;
    end if;

    --сбор сообщения
    pMessXML := PKG_MESS_CFM_2_1.mess_cfm_2_xml(pMessID).getclobval();

    --Распаковка из конверта
    result := pkg_messages_xml.Get_RootNodeFromXML(pMessXML, vRootNode);
    if result <> 0 then
      return result; -- -305 или -307 (not valid или узел не найден)
    end if;

    vXML := xmltype.createxml(vRootNode);


    --Преобразование, проверка и сохранение версии документа
    Result := pkg_messages_xml.Get_FieldFromXML(vXML, 'Version', vMessageVersion);
    if Result <> 0 then
      return Result;
    end if;
    vres := pkg_common.GetFormattedGuid(vMessageVersion, vMess_cfm.Mess_Version);
    if vres <> 0 then

      return - 1301; -- Неверный формат идентификационного номера версии документа
    else
      begin
        select mv.mess_type,
               versionid
        into vMess_cfm.Mess_Type,

             pVersionId --Тип сообщения
        from messages_versions mv
        where mv.versioncode = vMess_cfm.Mess_Version;
      exception
        when others then

          return - 306; --Неверная версия сообщения. Сообщение не сохранено.
          vMess_cfm.Mess_Type := -1;
      end;
    end if;


    --Преобразование, проверка и сохранение GUID документа
    Result := pkg_messages_xml.Get_FieldFromXML(vXML, 'DocumentUniqueIdentifier', vMess_cfm.Mess_Docid);
    if Result <> 0 then
      return Result;
    end if;
    vres := pkg_common.GetFormattedGuid(vMess_cfm.Mess_Docid, vMess_cfm.Mess_Form_Guid);
    if vres <> 0 then

      return - 1302; --  Неверный формат идентификационного номера документа
    else

      ---!!!!!!!!!!!!!!!!!!Если будет доп столбец, то проверять его!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

      if vMess_cfm.Mess_Form_Guid <> vMessGuid then
        return - 1011;

        --!!!!!!!!Оставить вторую ветку, если будет дополнительное поле
      end if;
    end if;

    update mess_cfm_xml
    set mess_xml = vRootNode
    where mess_cfm_id = pMessID;
    commit;

    pkg_messages_cfm.UpdateEnchanceData(pMessID);
    return result;

  exception
    when others then
      return - 2101;
  end;
  --

  /* Roma */
  function CheckRNN(widget_value in varchar2) return number is
    result    number default 0;
    pSum      number default 0;
    pContr    number default 0;
    pWeight   number default 0;
    pCheckVal varchar2(12) DEFAULT '000000000000';
  begin
    if length(widget_value) <> 12 then
      return - 1;
    elsif widget_value = pCheckVal then
      return - 2;
    elsif widget_value = REPLACE(pCheckVal, '0', '1') then
      return - 2;
    elsif widget_value = REPLACE(pCheckVal, '0', '2') then
      return - 2;
    elsif widget_value = REPLACE(pCheckVal, '0', '3') then
      return - 2;
    elsif widget_value = REPLACE(pCheckVal, '0', '4') then
      return - 2;
    elsif widget_value = REPLACE(pCheckVal, '0', '5') then
      return - 2;
    elsif widget_value = REPLACE(pCheckVal, '0', '6') then
      return - 2;
    elsif widget_value = REPLACE(pCheckVal, '0', '7') then
      return - 2;
    elsif widget_value = REPLACE(pCheckVal, '0', '8') then
      return - 2;
    elsif widget_value = REPLACE(pCheckVal, '0', '9') then
      return - 2;

    end if;
    begin

      while (pWeight < 10) loop
        pSum := CAST(SUBSTR(widget_value, 1, 1) as int) * (mod(pWeight, 10) + 1) + CAST(SUBSTR(widget_value, 2, 1) as int) * (mod(pWeight + 1, 10) + 1) + CAST(SUBSTR(widget_value, 3, 1) as int) * (mod(pWeight + 2, 10) + 1) +
                CAST(SUBSTR(widget_value, 4, 1) as int) * (mod(pWeight + 3, 10) + 1) + CAST(SUBSTR(widget_value, 5, 1) as int) * (mod(pWeight + 4, 10) + 1) + CAST(SUBSTR(widget_value, 6, 1) as int) * (mod(pWeight + 5, 10) + 1) +
                CAST(SUBSTR(widget_value, 7, 1) as int) * (mod(pWeight + 6, 10) + 1) + CAST(SUBSTR(widget_value, 8, 1) as int) * (mod(pWeight + 7, 10) + 1) + CAST(SUBSTR(widget_value, 9, 1) as int) * (mod(pWeight + 8, 10) + 1) +
                CAST(SUBSTR(widget_value, 10, 1) as int) * (mod(pWeight + 9, 10) + 1) + CAST(SUBSTR(widget_value, 11, 1) as int) * (mod(pWeight + 10, 10) + 1);

        pContr := mod(pSum, 11);
        if Pcontr < 10 then
          IF Pcontr <> CAST(SUBSTR(widget_value, 12, 1) as int) then
            return - 4;
          else
            return 0;
          end if;
        else
          pWeight := pWeight + 1;
        end if;
      end loop;
      return - 5;
    exception
      when others then

        return - 3; --содержит запрещеные символы
    end;

    return result;
  end;
  --
  function checkBIC(widget_value in VARCHAR2) return number as
  begin
    if widget_value = '400' then
      return 1;
    end if;
    if widget_value = '500' then
      return 1;
    end if;
    if widget_value = '600' then
      return 1;
    end if;
    return 0;
  end;
  --
  function checkINN(widget_value in VARCHAR2) return number is
    result    number default 0;
    pSum      number default 0;
    pContr    number default 0;
    pWeight   number default 0;
    pCheckVal varchar2(12) DEFAULT '000000000000';
  begin
    if length(widget_value) <> 12 then
      return - 1;
    elsif widget_value = pCheckVal then
      return - 2;
    elsif widget_value = REPLACE(pCheckVal, '0', '1') then
      return - 2;
    elsif widget_value = REPLACE(pCheckVal, '0', '2') then
      return - 2;
    elsif widget_value = REPLACE(pCheckVal, '0', '3') then
      return - 2;
    elsif widget_value = REPLACE(pCheckVal, '0', '4') then
      return - 2;
    elsif widget_value = REPLACE(pCheckVal, '0', '5') then
      return - 2;
    elsif widget_value = REPLACE(pCheckVal, '0', '6') then
      return - 2;
    elsif widget_value = REPLACE(pCheckVal, '0', '7') then
      return - 2;
    elsif widget_value = REPLACE(pCheckVal, '0', '8') then
      return - 2;
    elsif widget_value = REPLACE(pCheckVal, '0', '9') then
      return - 2;
    end if;

    begin
      while (pWeight <= 3) loop
        pSum   := CAST(SUBSTR(widget_value, 1, 1) as int) * (mod(pWeight, 11) + 1) + CAST(SUBSTR(widget_value, 2, 1) as int) * (mod(pWeight + 1, 11) + 1) + CAST(SUBSTR(widget_value, 3, 1) as int) * (mod(pWeight + 2, 11) + 1) +
                  CAST(SUBSTR(widget_value, 4, 1) as int) * (mod(pWeight + 3, 11) + 1) + CAST(SUBSTR(widget_value, 5, 1) as int) * (mod(pWeight + 4, 11) + 1) + CAST(SUBSTR(widget_value, 6, 1) as int) * (mod(pWeight + 5, 11) + 1) +
                  CAST(SUBSTR(widget_value, 7, 1) as int) * (mod(pWeight + 6, 11) + 1) + CAST(SUBSTR(widget_value, 8, 1) as int) * (mod(pWeight + 7, 11) + 1) + CAST(SUBSTR(widget_value, 9, 1) as int) * (mod(pWeight + 8, 11) + 1) +
                  CAST(SUBSTR(widget_value, 10, 1) as int) * (mod(pWeight + 9, 11) + 1) + CAST(SUBSTR(widget_value, 11, 1) as int) * (mod(pWeight + 10, 11) + 1);
        pContr := mod(pSum, 11);
        if Pcontr < 10 then
          IF Pcontr <> CAST(SUBSTR(widget_value, 12, 1) as int) then
            return - 4;
          else
            return 0;
          end if;
        else
          pWeight := pWeight + 2;
        end if;
      end loop;
      return - 5;
    exception
      when others then

        return - 3; --содержит запрещеные символы
    end;

    return result;
  end;
  /*Roma*/

  FUNCTION CHECKREGION (widget_value IN VARCHAR2) RETURN NUMBER
  as
    MESSAGE_COUNT          number default 0;
  BEGIN
    BEGIN
        SELECT count(*) INTO MESSAGE_COUNT FROM KATO_REGION_VIEW
                WHERE teparent = widget_value or teparent in
                (select b.TE
                  from KATO b
                        where b.teparent = widget_value
                        group by b.TE);
        IF (MESSAGE_COUNT > 1) THEN RETURN 0; END IF ;
    EXCEPTION
    WHEN OTHERS THEN
        RETURN -1;
    END;
    RETURN -1;
  END;

 FUNCTION CHECKCITY (widget_value IN VARCHAR2) RETURN NUMBER
  as
  MESSAGE_COUNT          number default 0;
  BEGIN
    BEGIN
        SELECT count(*) INTO MESSAGE_COUNT FROM KATO_CITY_VIEW
                WHERE teparent = widget_value or teparent in
                (select b.TE
                  from KATO b
                        where b.teparent = widget_value
                        group by b.TE);
        IF (MESSAGE_COUNT > 1) THEN RETURN 0; END IF ;
    EXCEPTION
    WHEN OTHERS THEN
        RETURN -1;
    END;
    RETURN -1;
  END;
  --users
  --if user ol then return > 1 else return 0
  function check_user_ol(widget_value in VARCHAR2) return number as
    l_result number;
  begin
    select count(*)
    into l_result
    from user_roles
    where userid = (select userid
                    from users
                    where userid = pkg_users.GetCurrentUserId)
    and roleid = 4;

    return l_result;
  end;
  --
end PKG_MESSAGES_CFM;
/

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("CurrencyName", C.ANALYTICNAME),
                      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
    LEFT OUTER JOIN analyticcodes c
    ON TB.oper_currency_code = C.ANALYTICCODE and C.ANALYTICTYPE = 41018
    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", ta.member_residence_countrycode),
                                        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", ta.member_bank_address),
                                                   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),
                                        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", ta.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",oa.OKEDCODE) AS xmltype

    INTO v_result
    FROM mess_cfm_announce_memb tm, OKED2AC oa
    WHERE tm.MESS_CFM_ID = p_MESS_CFM_ID
    and tm.member_id = p_MEMBER_ID
    and oa.ancode = tm.member_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 ta.founder_type
                               WHEN 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", ta.founder_ur_residence_c_code))
                               WHEN 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", ta.founder_ur_residence_c_code))
                               WHEN 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))
                             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", member_reg_counrycode),
                                                  XMLElement("Area", XMLATTRIBUTES(ta.member_reg_area_code as "Code"), ta.member_reg_area),
                                                  XMLElement("District", XMLATTRIBUTES(ta.member_reg_region_code as "Code"), ta.member_reg_region),
                                                  XMLElement("Town", XMLATTRIBUTES(ta.member_reg_sity_code as "Code"), ta.member_reg_sity),
                                                  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", ta.member_seat_counrycode),
                                                  XMLElement("Area", XMLATTRIBUTES(ta.member_seat_area_code as "Code"), ta.member_seat_area),
                                                  XMLElement("District", XMLATTRIBUTES(ta.member_seat_region_code as "Code"), ta.member_seat_region),
                                                  XMLElement("Town", XMLATTRIBUTES(ta.member_seat_sity_code as "Code"), ta.member_seat_sity),
                                                  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", member_reg_counrycode),
                                                  XMLElement("Area", XMLATTRIBUTES(ta.member_reg_area_code as "Code"), ta.member_reg_area),
                                                  XMLElement("District", XMLATTRIBUTES(ta.member_reg_region_code as "Code"), ta.member_reg_region),
                                                  XMLElement("Town", XMLATTRIBUTES(ta.member_reg_sity_code as "Code"), ta.member_reg_sity),
                                                  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", ta.member_seat_counrycode),
                                                  XMLElement("Area", XMLATTRIBUTES(ta.member_seat_area_code as "Code"), ta.member_seat_area),
                                                  XMLElement("District", XMLATTRIBUTES(ta.member_seat_region_code as "Code"), ta.member_seat_region),
                                                  XMLElement("Town", XMLATTRIBUTES(ta.member_seat_sity_code as "Code"), ta.member_seat_sity),
                                                  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", member_reg_counrycode),
                                                  XMLElement("Area", XMLATTRIBUTES(ta.member_reg_area_code as "Code"), ta.member_reg_area),
                                                  XMLElement("District", XMLATTRIBUTES(ta.member_reg_region_code as "Code"), ta.member_reg_region),
                                                  XMLElement("Town", XMLATTRIBUTES(ta.member_reg_sity_code as "Code"), ta.member_reg_sity),
                                                  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", ta.member_seat_counrycode),
                                                  XMLElement("Area", XMLATTRIBUTES(ta.member_seat_area_code as "Code"), ta.member_seat_area),
                                                  XMLElement("District", XMLATTRIBUTES(ta.member_seat_region_code as "Code"), ta.member_seat_region),
                                                  XMLElement("Town", XMLATTRIBUTES(ta.member_seat_sity_code as "Code"), ta.member_seat_sity),
                                                  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;
/

-- Нужно добавить xslt-шаблон для формы ФМ-1

EXEC DBMS_OUTPUT.put_line('Нужно добавить xslt-шаблон для формы ФМ-1');
/
