/*
* Автор: Маркелов С.В.
* Дата: 2014-11-11
* Задача: http://192.168.2.17:3000/issues/798
* Назначение: Не формируется запрос регистрации СФМ
* Изменяются объекты:
* 	insert into ERRORS
*   update ERRORS
*/

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

declare
	filename      varchar2(256) := 'sfm.database-update.2014-11-11.FixORAError_CharacterStringBufferTooSmall.sql';
	purpose       varchar2(4000) := 'Не формируется запрос регистрации СФМ';
	script_exists number := 0;
	cnt           number;
	language      varchar2(4000);
begin

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

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

  	commit;

exception
	when others then
		rollback;
		raise;
end;
/

CREATE OR REPLACE PACKAGE BODY KFM."PKG_MESSVERIFYPROCS" as

  ERROR_XML      constant varchar2(17 char) := 'Ошибка XML';
  PARSING_ERROR  constant varchar2(30 char) := 'Ошибка парсинга';
  DATA_NOT_FOUND constant varchar2(33) := 'Данных не найдено';

  NOT_FOUND     constant varchar2(44 char) := 'Значение не найдено в XML';
  NOT_CONDITION constant varchar2(107 char) := 'Не найдено зависимое значение для осуществления проверки';
  EMPTY         constant varchar2(35 char) := 'Значение не задано';
  OK            constant varchar2(2 char) := 'OK';
  ERROR         constant varchar2(36 char) := 'Контроль не пройден';
  XPATH_ERROR   constant varchar2(54 char) := 'Ошибка выполнения xPath запроса';
  ERROR_RES     constant number := -905; -- ошибка проверки по имени функции

  function GetMessage(pMessage in clob, pError in varchar2) return clob is
    pResult clob;
  begin
    pResult := pMessage;
    if pError is not null then
      if length(pResult) = 0 or pResult is null then
        pResult := pError || ' - не выполнено.';
      else
        dbms_lob.append(pResult,
                        chr(13) || pError || ' - не выполнено.');
      end if;
    end if;
    return pResult;
  end;

  function GetSLKExplanationRecord(pFiledNumberInForm in varchar2,
                                   pPathToFiled       in varchar2,
                                   pErrorDescription  in varchar2)
    return clob is
    pResult clob;
  begin
    pResult := '[' || pFiledNumberInForm || ';' || pPathToFiled || '^' ||
               pErrorDescription || ']';
    return pResult;
  end;

  function AppendSLKExplanation(pSLKExplanation       in clob,
                                pSLKExplanationRecord in clob) return clob is
    pResult clob;
  begin
    pResult := pSLKExplanation;
    if pSLKExplanationRecord is not null then
      if length(pResult) = 0 or pResult is null then
        pResult := pSLKExplanationRecord;
      else
        dbms_lob.append(pResult, pSLKExplanationRecord);
      end if;
    end if;
    return pResult;
  end;

  function GetLastError(pmessid in number) return number is
    result number default 0;
  begin
    select m.int_err_code
      into result
      from mess_cfm m
     where m.mess_cfm_id = pMessID;
    return result;
  exception
    when others then
      return 0;
  end;

  --VerifyMessage
  function VerifyMessage(pMessageID      in number,
                         pVerifyResult   out varchar2,
                         pSLKExplanation out clob) return number is
    SLK_OK    constant varchar2(46 char) := 'Контроль успешно пройден';
    SLK_ERROR constant varchar2(93 char) := 'Контроль не пройден, слишком много неверных данных';
    NEW_LINE  constant varchar2(2) := chr(10);
    vVerifyResult varchar2(4000 char);
    Result        number default 0;
    vResult       number default 0;

    pErrorCode number;
    pversion   number; --19.04.2010
    theXmlDoc  xmldom.DOMDocument;
    pXML       clob;
  begin
    select versionid
      into pversion
      from mess_cfm
      left join messages_versions
        on (mess_version = versioncode)
     where mess_cfm_id = pMessageID;
    if pversion in (1, 2) then
      vResult := ParseAndTestXML(pMessageID, pVerifyResult, theXmlDoc);
      if vResult <> 0 then
        return - 20100; --?????????
      end if;
      pVerifyResult := null;
      -- проверка по процедурам
      for vVerifyRec in (select mvs.verifystring, mvs.verifydescription
                           from mess_verify_setup mvs
                          where mvs.verifytype = 2
                            and mvs.isenable = 1
                            and mvs.messageversion = pversion) loop
        PKG_MessVerifyProcs.VerifyProcs(theXmlDoc,
                                        vVerifyRec.Verifystring,
                                        vVerifyResult,
                                        vResult);
        if vResult != 0 then
          pVerifyResult := pVerifyResult || vVerifyRec.Verifydescription ||
                           ' - ' || vVerifyResult || NEW_LINE;
        end if;
      end loop;
      -- проверка по xPath
      for vVerifyRec in (select mvs.verifystring, mvs.verifydescription
                           from mess_verify_setup mvs
                          where mvs.verifytype = 1
                            and mvs.isenable = 1
                            and mvs.messageversion = pversion) loop
        begin
          EXECUTE IMMEDIATE vVerifyRec.Verifystring
            USING IN pMessageID, OUT vVerifyResult;
          if vVerifyResult != OK then
            pVerifyResult := pVerifyResult || vVerifyRec.Verifydescription ||
                             ' - ' || vVerifyResult || NEW_LINE;
          end if;
        exception
          when others then
            pVerifyResult := pVerifyResult || vVerifyRec.Verifydescription ||
                             ' - ' || XPATH_ERROR || NEW_LINE;
        end;
      end loop;
    else
      --используетсся другой метод валидации для этйо версии сообщения
      return 0;

      select mess_xml
        into PXML
        from mess_cfm_xml
       where mess_cfm_id = pMessageID;
      pVerifyResult := VerifyMessage_Announce(PXML,
                                              1,
                                              pversion,
                                              pMessageID,
                                              pSLKExplanation);
    end if;
    if pVerifyResult is null then
      pErrorCode := GetLastError(pMessageID);
      if pErrorCode = 0 then
        pVerifyResult := SLK_OK;
      else
        /* pkg_main.GetError(pErrorCode,pErrorName,pErrorDescr,result);
        pVerifyResult :='Ошибка cтруктурно-логического контроля: '||replace(pErrorName, ' Сообщение сохранено с ошибками');
        Result := -900; -- СЛК не пройден*/
        pVerifyResult := null;
        result        := 0;
      end if;
    else
      Result := -900; -- СЛК не пройден
    end if;

    if not xmldom.IsNull(theXmlDoc) then
      xml.freeDocument(theXmlDoc);
    end if;

    pkg_audits.WriteUserAudit(10,
                              'pkg_messverifyprocs.VerifyMessage',
                              'ID сообщения: ' || pmessageid ||
                              ' Результат: ' || result);
    return(Result);
  exception
    when others then
      if not xmldom.IsNull(theXmlDoc) then
        xml.freeDocument(theXmlDoc);
      end if;
      pVerifyResult := SLK_ERROR;
      Result        := -900; -- СЛК не пройден
      return(Result);
  end VerifyMessage;

  -- ParseAndTestXML
  function ParseAndTestXML(pMessageID    in number,
                           pVerifyResult out varchar2,
                           theXmlDoc     out xmldom.DOMDocument)
    return number is
    vClob  clob;
    Result number default 0;
  begin
    select m.mess_xml
      into vClob
      from mess_cfm_xml m
     where m.mess_cfm_id = pMessageID;

    if vClob is not null then
      theXmlDoc := xml.parse(vClob);
      if xmldom.IsNull(theXmlDoc) then
        begin
          pVerifyResult := ERROR_XML;
          Result        := -901; --Ошибка clob
        end;
      end if;
    else
      pVerifyResult := PARSING_ERROR;
      Result        := -902; --Ошибка парсинга
    end if;
    pVerifyResult := 'OK';
    return Result;
  exception
    when no_data_found then
      pVerifyResult := DATA_NOT_FOUND;
      Result        := -903; --Данных не найдено
      return Result;
    when others then
      Result := -904; --Иная ошибка
      return Result;
  end ParseAndTestXML;

  --VerifyProcs
  procedure VerifyProcs(theXmlDoc     in xmldom.DOMDocument,
                        pProcName     in varchar2,
                        pVerifyResult out varchar2,
                        pResult       out number) is

  begin
    -- проверка по функциям
    case pProcName
    -- Основания подачи сообщения
      when 'CheckSubmisBas' then
        pResult := CheckSubmisBas(theXmlDoc, pVerifyResult);
      when 'CheckCurrencyTenge' then
        pResult := CheckCurrencyTenge(theXmlDoc, pVerifyResult);
        -- Вид документа
      when 'CheckReportsType' then
        pResult := CheckReportsType(theXmlDoc, pVerifyResult);
        -- Проверка указания критерия подозрительности
      when 'CheckSuspicCrit' then
        pResult := CheckSuspicCrit(theXmlDoc, pVerifyResult);
        -- Порядок задания кодов подозрительности
      when 'CheckSuspicOrder' then
        pResult := CheckSuspicOrder(theXmlDoc, pVerifyResult);
        -- проверка заполнености поля доп инф (если вид документа=3)
      when 'CheckReportsTypeAndMoreInf' then
        pResult := CheckReportsTypeAndMoreInf(theXmlDoc, pVerifyResult);
        -- проверка соответствия количества участников и количества введенной информации
      when 'CheckParticipantCount' then
        pResult := CheckParticipantCount(theXmlDoc, pVerifyResult);
      when 'CheckIDViewOperation' then
        pResult := CheckIDViewOperation(theXmlDoc, pVerifyResult);
        --
      when 'CheckOKPO' then
        pResult := CheckOKPO(theXmlDoc, pVerifyResult);
      when 'CheckOKED' then
        pResult := CheckOKED(theXmlDoc, pVerifyResult);
      when 'CheckResidence' then
        pResult := CheckResidence(theXmlDoc, pVerifyResult);
      when 'CheckComments' then
        pResult := CheckResidence(theXmlDoc, pVerifyResult);
      else
        null;
        pResult       := 0;
        pVerifyResult := OK;
    end case;

  end VerifyProcs;

  --TestForNullByXPath
  procedure TestForNullByXPath(pMessageID    in number,
                               pPath         in varchar2,
                               pVerifyResult out varchar2) is
    XMLParseError EXCEPTION;
    PRAGMA EXCEPTION_INIT(XMLParseError, -20100);
    theXmlDoc xmldom.DOMDocument;
    vResult   number default 0;
  begin
    vResult := ParseAndTestXML(pMessageID, pVerifyResult, theXmlDoc);
    if vResult = 0 then
      declare
        vValue varchar2(100 char);

        vnode     xmldom.domnode;
        pnode     xmldom.domnode;
        vnodelist xmldom.DOMNodeList;
      begin

        --inna 21.01
        PNODE         := xmldom.makeNode(thexmldoc);
        pVerifyResult := OK;

        if instr(ppath, 'ParticipantsList') > 0 then
          if instr(ppath, 'FoundersList') > 0 then
            null; -- по фундерам проверка
            vNodeList := pkg_messages_xml.GetNodesList(PNODE,
                                                       '/Root/ParticipantsList/Participant/FoundersList/Founder');

            for i in 0 .. xmldom.getLength(vNodeList) - 1 loop
              vnode := xmldom.item(vNodeList, i);
              if xpath.test(vnode,
                            replace(pPath,
                                    '/Root/ParticipantsList/Participant/FoundersList/Founder/')) then
                vValue := xslprocessor.valueOf(vnode,
                                               replace(pPath,
                                                       '/Root/ParticipantsList/Participant/FoundersList/Founder/') ||
                                               '/text()');

                if vValue is null then
                  if instr(pPath, 'FounderAC') > 0 then
                    if xslprocessor.valueOf(vnode, 'PersonType/text()') = '1' then
                      pVerifyResult := EMPTY;
                    end if;
                  elsif instr(pPath, 'founderUR') > 0 then
                    if xslprocessor.valueOf(vnode, 'PersonType/text()') = '2' then
                      pVerifyResult := EMPTY;
                    end if;
                  else
                    pVerifyResult := EMPTY;
                  end if;
                end if;
              else
                if instr(pPath, 'FounderAC') > 0 then
                  if xslprocessor.valueOf(vnode, 'PersonType/text()') = '1' then
                    pVerifyResult := NOT_FOUND;
                  end if;
                elsif instr(pPath, 'founderUR') > 0 then
                  if xslprocessor.valueOf(vnode, 'PersonType/text()') = '2' then
                    pVerifyResult := NOT_FOUND;
                  end if;
                else
                  pVerifyResult := NOT_FOUND;
                end if; --тега нет
              end if;
            end loop;
          else
            --от корня вниз
            vNodeList := pkg_messages_xml.GetNodesList(PNODE,
                                                       '/Root/ParticipantsList/Participant');

            for i in 0 .. xmldom.getLength(vNodeList) - 1 loop
              vnode := xmldom.item(vNodeList, i);
              if xpath.test(vnode,
                            replace(pPath,
                                    '/Root/ParticipantsList/Participant/')) then
                vValue := xslprocessor.valueOf(vnode,
                                               replace(pPath,
                                                       '/Root/ParticipantsList/Participant/') ||
                                               '/text()');
                if vValue is null then
                  if instr(pPath, 'AdditionalInformationIp') > 0 then
                    if xslprocessor.valueOf(vnode,
                                            'ParticipantsType/text()') = '3' then
                      pVerifyResult := EMPTY;
                    end if;
                  elsif instr(pPath, 'AdditionalInformationAc') > 0 then
                    if xslprocessor.valueOf(vnode,
                                            'ParticipantsType/text()') = '2' then
                      pVerifyResult := EMPTY;
                    end if;
                  elsif instr(pPath, 'AdditionalInformationUr') > 0 then
                    if xslprocessor.valueOf(vnode,
                                            'ParticipantsType/text()') = '1' then
                      pVerifyResult := EMPTY;
                    end if;
                  else
                    pVerifyResult := EMPTY;
                  end if;
                end if;
              else
                --тега нет
                if instr(pPath, 'AdditionalInformationIp') > 0 then
                  if xslprocessor.valueOf(vnode, 'ParticipantsType/text()') = '3' then
                    pVerifyResult := NOT_FOUND;
                  end if;
                elsif instr(pPath, 'AdditionalInformationAc') > 0 then
                  if xslprocessor.valueOf(vnode, 'ParticipantsType/text()') = '2' then
                    pVerifyResult := NOT_FOUND;
                  end if;
                elsif instr(pPath, 'AdditionalInformationUr') > 0 then
                  if xslprocessor.valueOf(vnode, 'ParticipantsType/text()') = '1' then
                    pVerifyResult := NOT_FOUND;
                  end if;
                else
                  pVerifyResult := NOT_FOUND;
                end if;
              end if;
            end loop;
          end if;
        elsif instr(ppath, 'Participants/Participant') > 0 then
          if instr(ppath, 'Founders/Founder') > 0 then
            null; -- по фундерам проверка
            vNodeList := pkg_messages_xml.GetNodesList(PNODE,
                                                       '/Root/Participants/Participant/Founders/Founder');

            for i in 0 .. xmldom.getLength(vNodeList) - 1 loop
              vnode := xmldom.item(vNodeList, i);
              if xpath.test(vnode,
                            replace(pPath,
                                    '/Root/ParticipantsList/Participant/FoundersList/Founder/')) then
                vValue := xslprocessor.valueOf(vnode,
                                               replace(pPath,
                                                       '/Root/ParticipantsList/Participant/FoundersList/Founder/') ||
                                               '/text()');

                if vValue is null then
                  pVerifyResult := EMPTY;
                end if;
              else
                pVerifyResult := NOT_FOUND;
              end if;
            end loop;
          elsif instr(ppath,
                      'CorrespondentBank/CorrespondentsInformations/CorrespondentInformation') > 0 then
            vNodeList := pkg_messages_xml.GetNodesList(PNODE,
                                                       '/Root/Participants/Participant/CorrespondentBank/CorrespondentsInformations/CorrespondentInformation');

            for i in 0 .. xmldom.getLength(vNodeList) - 1 loop
              vnode := xmldom.item(vNodeList, i);
              if xpath.test(vnode,
                            replace(pPath,
                                    '/Root/ParticipantsList/Participant/CorrespondentBank/CorrespondentsInformations/CorrespondentInformation/')) then
                vValue := xslprocessor.valueOf(vnode,
                                               replace(pPath,
                                                       '/Root/ParticipantsList/Participant/CorrespondentBank/CorrespondentsInformations/CorrespondentInformation/') ||
                                               '/text()');

                if vValue is null then
                  pVerifyResult := EMPTY;
                end if;
              else
                pVerifyResult := NOT_FOUND;
              end if;
            end loop;
          else
            --от корня вниз
            vNodeList := pkg_messages_xml.GetNodesList(PNODE,
                                                       '/Root/Participants/Participant');
            for i in 0 .. xmldom.getLength(vNodeList) - 1 loop
              vnode := xmldom.item(vNodeList, i);
              if xpath.test(vnode,
                            replace(pPath, '/Root/Participants/Participant/')) then
                vValue := xslprocessor.valueOf(vnode,
                                               replace(pPath,
                                                       '/Root/Participants/Participant/') ||
                                               '/text()');
                if vValue is null then
                  if instr(pPath, 'AdditionalInformationIp') > 0 then
                    if xslprocessor.valueOf(vnode,
                                            'ParticipantsType/text()') = '3' then
                      pVerifyResult := EMPTY;
                    end if;
                  elsif instr(pPath, 'AdditionalInformationAc') > 0 then
                    if xslprocessor.valueOf(vnode,
                                            'ParticipantsType/text()') = '2' then
                      pVerifyResult := EMPTY;
                    end if;
                  elsif instr(pPath, 'AdditionalInformationUr') > 0 then
                    if xslprocessor.valueOf(vnode,
                                            'ParticipantsType/text()') = '1' then
                      pVerifyResult := EMPTY;
                    end if;
                  else
                    pVerifyResult := EMPTY;
                  end if;
                end if;
              else
                --тега нет
                if instr(pPath, 'AdditionalInformationIp') > 0 then
                  if xslprocessor.valueOf(vnode, 'ParticipantsType/text()') = '3' then
                    pVerifyResult := NOT_FOUND;
                  end if;
                elsif instr(pPath, 'AdditionalInformationAc') > 0 then
                  if xslprocessor.valueOf(vnode, 'ParticipantsType/text()') = '2' then
                    pVerifyResult := NOT_FOUND;
                  end if;
                elsif instr(pPath, 'AdditionalInformationUr') > 0 then
                  if xslprocessor.valueOf(vnode, 'ParticipantsType/text()') = '1' then
                    pVerifyResult := NOT_FOUND;
                  end if;
                else
                  pVerifyResult := NOT_FOUND;
                end if;
              end if;
            end loop;
          end if;

        else
          if xpath.test(pnode, pPath) then
            vValue := xslprocessor.valueOf(pnode, pPath);
            if vValue is null then
              pVerifyResult := EMPTY;
            end if;
          else
            pVerifyResult := NOT_FOUND;
          end if;
          --обычная проверка
        end if;

        ---- inna
      exception
        when others then
          -- неверное значение
          pVerifyResult := ERROR;
      end;
    else
      pVerifyResult := PARSING_ERROR; --VJAZ
    end if;
    -- freeDocument
    if not xmldom.IsNull(theXmlDoc) then
      xml.freeDocument(theXmlDoc);
    end if;
  exception
    when others then
      -- неверное значение
      pVerifyResult := ERROR;
  end TestForNullByXPath;

  --TestNumberByXPath
  procedure TestNumberByXPath(pMessageID    in number,
                              pPath         in varchar2,
                              pMinValue     in varchar2,
                              pMaxValue     in varchar2,
                              pCompareType  in varchar2,
                              pVerifyResult out varchar2) is
    XMLParseError EXCEPTION;
    PRAGMA EXCEPTION_INIT(XMLParseError, -20100);
    theXmlDoc xmldom.DOMDocument;
    vResult   number default 0;
  begin
    vResult := ParseAndTestXML(pMessageID, pVerifyResult, theXmlDoc);
    if vResult = 0 then
      declare
        vValue    varchar2(100 char);
        vnode     xmldom.domnode;
        pnode     xmldom.domnode;
        vnodelist xmldom.DOMNodeList;
        vNodeName varchar2(100 char);
      begin
        PNODE         := xmldom.makeNode(thexmldoc);
        pVerifyResult := OK;
        vNodeList     := pkg_messages_xml.GetNodesList(PNODE, PPATH);
        for i in 0 .. xmldom.getLength(vNodeList) - 1 loop
          vnode     := xmldom.item(vNodeList, i);
          vNodeName := xmldom.getNodeName(vnode);
          vvalue    := xslprocessor.valueOf(vnode, 'text()');
          if vValue is not null then
            -- не введено значение
            if pMinValue = 'null' and pMaxValue != 'null' then
              if pCompareType = 'strict' then
                if to_number(vValue) < to_number(pMaxValue) then
                  /*pVerifyResult := OK;*/
                  null;
                else
                  pVerifyResult := ERROR;
                end if;
              else
                if to_number(vValue) <= to_number(pMaxValue) then
                  null;
                else
                  pVerifyResult := ERROR;
                end if;
              end if;
            elsif pMinValue != 'null' and pMaxValue = 'null' then
              if pCompareType = 'strict' then
                if to_number(vValue) > to_number(pMinValue) then
                  null;
                else
                  pVerifyResult := ERROR;
                end if;
              else
                if to_number(vValue) >= to_number(pMinValue) then
                  null;
                else
                  pVerifyResult := ERROR;
                end if;
              end if;
            elsif pMinValue != 'null' and pMaxValue != 'null' then
              if pCompareType = 'strict' then
                if to_number(vValue) > to_number(pMinValue) and
                   to_number(vValue) < to_number(pMaxValue) then
                  /*pVerifyResult := OK;*/
                  null;
                else
                  pVerifyResult := ERROR;
                end if;
              else
                if to_number(vValue) >= to_number(pMinValue) and
                   to_number(vValue) <= to_number(pMaxValue) then
                  /*pVerifyResult := OK;*/
                  null;
                else
                  pVerifyResult := ERROR;
                end if;
              end if;
            else
              -- не заданы оба параметра (min и max значения)
              pVerifyResult := OK;
            end if;
          end if;

        end loop;
      exception
        when others then
          -- неверное значение
          pVerifyResult := ERROR;
      end;
    else
      pVerifyResult := PARSING_ERROR; --VJAZ
    end if;
    -- freeDocument
    if not xmldom.IsNull(theXmlDoc) then
      xml.freeDocument(theXmlDoc);
    end if;
  exception
    when VALUE_ERROR then
      pVerifyResult := OK;
    when others then
      -- неверное значение
      pVerifyResult := ERROR;
  end TestNumberByXPath;

  --TestLengthByXPath
  procedure TestLengthByXPath(pMessageID    in number,
                              pPath         in varchar2,
                              pMinValue     in varchar2,
                              pMaxValue     in varchar2,
                              pCompareType  in varchar2,
                              pVerifyResult out varchar2) is

    XMLParseError EXCEPTION;
    PRAGMA EXCEPTION_INIT(XMLParseError, -20100);
    theXmlDoc xmldom.DOMDocument;
    vResult   number default 0;
  begin
    vResult := ParseAndTestXML(pMessageID, pVerifyResult, theXmlDoc);
    if vResult = 0 then
      declare
        vValue    varchar2(100 char);
        vnode     xmldom.domnode;
        pnode     xmldom.domnode;
        vnodelist xmldom.DOMNodeList;
        vNodeName varchar2(100 char);
      begin
        PNODE         := xmldom.makeNode(thexmldoc);
        pVerifyResult := OK;
        vNodeList     := pkg_messages_xml.GetNodesList(PNODE, PPATH);
        for i in 0 .. xmldom.getLength(vNodeList) - 1 loop
          vnode := xmldom.item(vNodeList, i);
          -- dbms_output.put_line(xmldom.makedocument(vnode));
          vNodeName := xmldom.getNodeName(vnode);
          vvalue    := xslprocessor.valueOf(vnode, 'text()');
          if vvalue is not null then
            if pMinValue = 'null' and pMaxValue != 'null' then
              if pCompareType = 'strict' then
                if length(vValue) < to_number(pMaxValue) then
                  null; --inna
                else
                  pVerifyResult := ERROR;
                end if;
              else
                if length(vValue) <= to_number(pMaxValue) then
                  null; --inna
                else
                  pVerifyResult := ERROR;
                end if;
              end if;
            elsif pMinValue != 'null' and pMaxValue = 'null' then
              if pCompareType = 'strict' then
                if length(vValue) > to_number(pMinValue) then
                  null;
                else
                  pVerifyResult := ERROR;
                end if;
              else
                if length(vValue) >= to_number(pMinValue) then
                  null;
                else
                  pVerifyResult := ERROR;
                end if;
              end if;
            elsif pMinValue != 'null' and pMaxValue != 'null' then
              if pCompareType = 'strict' then
                if length(vValue) > to_number(pMinValue) and
                   length(vValue) < to_number(pMaxValue) then
                  null;
                else
                  pVerifyResult := ERROR;
                end if;
              else
                if length(vValue) >= to_number(pMinValue) and
                   length(vValue) <= to_number(pMaxValue) then
                  null;
                else
                  pVerifyResult := ERROR;
                end if;
              end if;
              /*else
              -- не заданы оба параметра (min и max значения)
              pVerifyResult := OK;*/
            end if;
          end if;
        end loop;
      exception
        when others then
          -- неверное значение
          pVerifyResult := ERROR;
      end;
    else
      pVerifyResult := PARSING_ERROR; --VJAZ
    end if;
    -- freeDocument
    if not xmldom.IsNull(theXmlDoc) then
      xml.freeDocument(theXmlDoc);
    end if;
  exception
    when VALUE_ERROR then
      pVerifyResult := OK;
    when others then
      -- неверное значение
      pVerifyResult := ERROR;
  end TestLengthByXPath;

  --TestVarcharByXPath
  procedure TestVarcharByXPath(pMessageID    in number,
                               pPath         in varchar2,
                               pMinValue     in varchar2,
                               pMaxValue     in varchar2,
                               pCompareType  in varchar2,
                               pVerifyResult out varchar2) is

    XMLParseError EXCEPTION;
    PRAGMA EXCEPTION_INIT(XMLParseError, -20100);
    theXmlDoc xmldom.DOMDocument;
    vResult   number default 0;
  begin
    vResult := ParseAndTestXML(pMessageID, pVerifyResult, theXmlDoc);
    if vResult = 0 then
      declare
        vMinValue varchar2(100 char) default pMinValue;
        vMaxValue varchar2(100 char) default pMaxValue;
        vValue    varchar2(100 char);
        vnode     xmldom.domnode;
        pnode     xmldom.domnode;
        vnodelist xmldom.DOMNodeList;
        vNodeName varchar2(100 char);
      begin
        PNODE         := xmldom.makeNode(thexmldoc);
        pVerifyResult := OK;
        vNodeList     := pkg_messages_xml.GetNodesList(PNODE, PPATH);
        for i in 0 .. xmldom.getLength(vNodeList) - 1 loop
          vnode := xmldom.item(vNodeList, i);
          -- dbms_output.put_line(xmldom.makedocument(vnode));
          vNodeName := xmldom.getNodeName(vnode);
          vvalue    := xslprocessor.valueOf(vnode, 'text()');
          if vValue is not null then
            -- не введено значение

            if vMinValue = 'null' and vMaxValue != 'null' then
              if pCompareType = 'strict' then
                if vValue < vMaxValue then
                  null;
                else
                  pVerifyResult := ERROR;
                end if;
              else
                if vValue <= vMaxValue then
                  null;
                else
                  pVerifyResult := ERROR;
                end if;
              end if;
            elsif vMinValue != 'null' and vMaxValue = 'null' then
              if pCompareType = 'strict' then
                if vValue > vMinValue then
                  null;
                else
                  pVerifyResult := ERROR;
                end if;
              else
                if vValue >= vMinValue then
                  null;
                else
                  pVerifyResult := ERROR;
                end if;
              end if;

            elsif vMinValue != 'null' and vMaxValue != 'null' then
              if pCompareType = 'strict' then
                if vValue > vMinValue and vValue < vMaxValue then
                  null;
                else
                  pVerifyResult := ERROR;
                end if;
              else
                if vValue >= vMinValue and vValue <= vMaxValue then
                  null;
                else
                  pVerifyResult := ERROR;
                end if;
              end if;

            end if;
          end if;
        end loop;
      exception
        when others then
          -- неверное значение
          pVerifyResult := ERROR;
      end;
    else
      pVerifyResult := PARSING_ERROR; --VJAZ
    end if;
    -- freeDocument
    if not xmldom.IsNull(theXmlDoc) then
      xml.freeDocument(theXmlDoc);
    end if;
  exception
    when others then
      -- неверное значение
      pVerifyResult := ERROR;
  end TestVarcharByXPath;

  --TestDateByXPath
  procedure TestDateByXPath(pMessageID    in number,
                            pPath         in varchar2,
                            pMinValue     in varchar2,
                            pMaxValue     in varchar2,
                            pCompareType  in varchar2,
                            pVerifyResult out varchar2) is
    XMLParseError EXCEPTION;
    PRAGMA EXCEPTION_INIT(XMLParseError, -20100);
    DATE_ERROR EXCEPTION;
    PRAGMA EXCEPTION_INIT(DATE_ERROR, -01861);
    vResult   number default 0;
    theXmlDoc xmldom.DOMDocument;
  begin
    vResult := ParseAndTestXML(pMessageID, pVerifyResult, theXmlDoc);
    if vResult = 0 then
      declare
        vValue    varchar2(100 char);
        vMinValue varchar2(100 char) default pMinValue;
        vMaxValue varchar2(100 char) default pMaxValue;

        vnode     xmldom.domnode;
        pnode     xmldom.domnode;
        vnodelist xmldom.DOMNodeList;
        vNodeName varchar2(100 char);
      begin
        PNODE         := xmldom.makeNode(thexmldoc);
        pVerifyResult := OK;
        vNodeList     := pkg_messages_xml.GetNodesList(PNODE, PPATH);
        for i in 0 .. xmldom.getLength(vNodeList) - 1 loop
          vnode := xmldom.item(vNodeList, i);
          -- dbms_output.put_line(xmldom.makedocument(vnode));
          vNodeName := xmldom.getNodeName(vnode);
          vvalue    := xslprocessor.valueOf(vnode, 'text()');
          if vValue is not null then
            -- не введено значение

            if vMinValue = 'null' and vMaxValue != 'null' then
              if pCompareType = 'strict' then
                if to_date(vValue, 'yyyy-mm-dd') <
                   to_date(vMaxValue, 'yyyy-mm-dd') then
                  /* pVerifyResult := OK;*/
                  null;
                else
                  pVerifyResult := ERROR;
                end if;
              else
                if to_date(vValue, 'yyyy-mm-dd') <=
                   to_date(vMaxValue, 'yyyy-mm-dd') then
                  /* pVerifyResult := OK;*/
                  null;
                else
                  pVerifyResult := ERROR;
                end if;
              end if;
            elsif vMinValue != 'null' and vMaxValue = 'null' then
              if pCompareType = 'strict' then
                if to_date(vValue, 'yyyy-mm-dd') >
                   to_date(vMinValue, 'yyyy-mm-dd') then
                  /*pVerifyResult := OK;*/
                  null;
                else
                  pVerifyResult := ERROR;
                end if;
              else
                if to_date(vValue, 'yyyy-mm-dd') >=
                   to_date(vMinValue, 'yyyy-mm-dd') then
                  /*pVerifyResult := OK;*/
                  null;
                else
                  pVerifyResult := ERROR;
                end if;
              end if;
            elsif vMinValue != 'null' and vMaxValue != 'null' then
              if pCompareType = 'strict' then
                if to_date(vValue, 'yyyy-mm-dd') >
                   to_date(vMinValue, 'yyyy-mm-dd') and
                   to_date(vValue, 'yyyy-mm-dd') <
                   to_date(vMaxValue, 'yyyy-mm-dd') then
                  /* pVerifyResult := OK;*/
                  null;
                else
                  pVerifyResult := ERROR;
                end if;
              else
                if to_date(vValue, 'yyyy-mm-dd') >=
                   to_date(vMinValue, 'yyyy-mm-dd') and
                   to_date(vValue, 'yyyy-mm-dd') <=
                   to_date(vMaxValue, 'yyyy-mm-dd') then
                  /*pVerifyResult := OK;*/
                  null;
                else
                  pVerifyResult := ERROR;
                end if;
              end if;
            end if;
          end if;

        end loop;
      exception
        when others then
          -- неверное значение
          pVerifyResult := ERROR;
      end;
    else
      pVerifyResult := PARSING_ERROR; --VJAZ
    end if;
    -- freeDocument
    if not xmldom.IsNull(theXmlDoc) then
      xml.freeDocument(theXmlDoc);
    end if;
  exception
    when DATE_ERROR then
      -- ошибка преобразования
      pVerifyResult := ERROR;
    when others then
      -- неверное значение
      pVerifyResult := ERROR;
  end TestDateByXPath;

  -- Test

  --TestForNullByXPathIfAnother
  procedure TestForNullByXPathIfAnother(pMessageID    in number,
                                        pPath         in varchar2,
                                        pAnotherPath  in varchar2,
                                        pVerifyResult out varchar2) is

    XMLParseError EXCEPTION;
    PRAGMA EXCEPTION_INIT(XMLParseError, -20100);
    theXmlDoc xmldom.DOMDocument;
    vResult   number default 0;
  begin
    vResult := ParseAndTestXML(pMessageID, pVerifyResult, theXmlDoc);
    if vResult = 0 then
      begin
        if xpath.test(theXmlDoc, pAnotherPath) then
          if (xpath.valueOf(theXmlDoc, pAnotherPath) is not null) then
            if xpath.test(theXmlDoc, pPath) then
              if (xpath.valueOf(theXmlDoc, pPath) is not null) then
                pVerifyResult := OK;
              else
                pVerifyResult := EMPTY;
              end if;
            else
              -- значениее не задано в XML
              pVerifyResult := NOT_FOUND;
            end if;
          else
            pVerifyResult := OK;
          end if;
        else
          pVerifyResult := OK;
        end if;
      exception
        when others then
          -- неверное значение
          pVerifyResult := ERROR;
      end;
    else
      pVerifyResult := PARSING_ERROR; --VJAZ
    end if;
    -- freeDocument
    if not xmldom.IsNull(theXmlDoc) then
      xml.freeDocument(theXmlDoc);
    end if;
  exception
    when others then
      -- неверное значение
      pVerifyResult := ERROR;
  end TestForNullByXPathIfAnother;

  --TestLengthIfAnother
  procedure TestLengthIfAnother(pMessageID    in number,
                                pPath         in varchar2,
                                pAnotherPath  in varchar2,
                                pMinLength    in number,
                                pMaxLength    in number,
                                pVerifyResult out varchar2) is

    XMLParseError EXCEPTION;
    PRAGMA EXCEPTION_INIT(XMLParseError, -20100);
    theXmlDoc xmldom.DOMDocument;
    vResult   number default 0;
  begin
    vResult := ParseAndTestXML(pMessageID, pVerifyResult, theXmlDoc);
    if vResult = 0 then
      declare
        vValue varchar2(100 char);
      begin
        if xpath.test(theXmlDoc, pAnotherPath) then
          begin
            if xpath.test(theXmlDoc, pPath) then
              vValue := xpath.valueOf(theXmlDoc, pPath);
              if vValue is null then
                -- не введено значение
                pVerifyResult := EMPTY;
              else
                if length(vValue) >= pMinLength and
                   length(vValue) <= pMaxLength then
                  pVerifyResult := OK;
                else
                  pVerifyResult := ERROR;
                end if;
              end if;
            else
              -- значениее не задано в XML
              pVerifyResult := NOT_FOUND;
            end if;
          exception
            when others then
              -- неверное значение
              pVerifyResult := ERROR;
          end;
        else
          pVerifyResult := OK;
        end if;
      exception
        when others then
          -- неверное значение
          pVerifyResult := ERROR;
      end;
    else
      pVerifyResult := PARSING_ERROR; --VJAZ
    end if;
    -- freeDocument
    if not xmldom.IsNull(theXmlDoc) then
      xml.freeDocument(theXmlDoc);
    end if;
  exception
    when others then
      -- неверное значение
      pVerifyResult := ERROR;
  end TestLengthIfAnother;

  function CheckSubmisBas(theXmlDoc     xmldom.DOMDocument,
                          pVerifyResult out varchar2) return number is
    PATH_SubmisBas constant varchar2(50 char) := '/Root/MessageInformation/SubmissionsBasises';
    vValue  varchar(10);
    pResult varchar2(100 char) default 0;
  begin
    if xpath.test(theXmlDoc, PATH_SubmisBas) then
      vValue := xpath.valueOf(theXmlDoc, PATH_SubmisBas);
      if vValue is not null then
        if vValue = '1' or vValue = '2' or vValue = '3' then
          pVerifyResult := OK;
        else
          -- неверное значение
          pVerifyResult := ERROR;
          pResult       := ERROR_RES;
        end if;
      else
        -- не введено значение
        pVerifyResult := EMPTY;
        pResult       := ERROR_RES;
      end if;
    else
      -- значениее не задано в XML
      pVerifyResult := NOT_FOUND;
      pResult       := ERROR_RES;
    end if;
    return pResult;
  exception
    when others then
      -- неверное значение
      pVerifyResult := ERROR;
      pResult       := ERROR_RES;
      return pResult;
  end;

  function CheckCurrencyTenge(theXmlDoc     xmldom.DOMDocument,
                              pVerifyResult out varchar2) return number is
    pResult    varchar2(100 char) default 0;
    vValue     varchar(10);
    vCurrTenge number;
    PATH_Curr_Code    constant varchar2(50 char) := '/Root/MessageInformation/CurrencyCode';
    PATH_Amount_Tenge constant varchar2(50 char) := '/Root/MessageInformation/AmountCurrencyTenge';

  begin
    if xpath.test(theXmlDoc, PATH_Curr_Code) then
      vValue := xpath.valueOf(theXmlDoc, PATH_Curr_Code);
      if vValue is not null then
        if vValue <> '398' then
          if xpath.test(theXmlDoc, PATH_Amount_Tenge) then
            vCurrTenge := xpath.valueOf(theXmlDoc, PATH_Amount_Tenge);
            if vCurrTenge <= 0 then
              pVerifyResult := EMPTY;
              pResult       := ERROR_RES;
            else
              pVerifyResult := OK;
            end if;
          else
            pVerifyResult := NOT_FOUND;
            pResult       := ERROR_RES;
          end if;

        else
          -- неверное значение
          pVerifyResult := OK;
          -- pResult       := ERROR_RES;
        end if;
      else
        -- не введено значение
        pVerifyResult := NOT_CONDITION;
        pResult       := ERROR_RES;
      end if;
    else
      -- значениее не задано в XML
      pVerifyResult := NOT_CONDITION;
      pResult       := ERROR_RES;
    end if;
    return pResult;
  exception
    when others then
      -- неверное значение
      pVerifyResult := ERROR;
      pResult       := ERROR_RES;
      return pResult;
  end;

  function CheckReportsType(theXmlDoc     xmldom.DOMDocument,
                            pVerifyResult out varchar2) return number is
    pResult varchar2(100 char) default 0;
    PATH_ReportsType constant varchar2(50 char) := '/Root/MessageInformation/ReportsType';
    vValue varchar(10);
  begin
    if xpath.test(theXmlDoc, PATH_ReportsType) then
      vValue := xpath.valueOf(theXmlDoc, PATH_ReportsType);
      if vValue is not null then
        if vValue = '1' or vValue = '2' or vValue = '3' or vValue = '4' then
          pVerifyResult := OK;
        else
          -- неверное значение
          pVerifyResult := ERROR;
          pResult       := ERROR_RES;
        end if;
      else
        -- не введено значение
        pVerifyResult := EMPTY;
        pResult       := ERROR_RES;
      end if;
    else
      -- значениее не задано в XML
      pVerifyResult := NOT_FOUND;
      pResult       := ERROR_RES;
    end if;
    return pResult;
  exception
    when others then
      -- неверное значение
      pVerifyResult := ERROR;
      pResult       := ERROR_RES;
      return pResult;
  end;

  function CheckSuspicCrit(theXmlDoc     xmldom.DOMDocument,
                           pVerifyResult out varchar2) return number is
    pResult     varchar2(100 char) default 0;
    vSubmisBas  varchar(10);
    vSuspicCrit varchar(10);
    PATH_SubmisBas  constant varchar2(50 char) := '/Root/MessageInformation/SubmissionsBasises';
    PATH_SuspicCrit constant varchar2(50 char) := '/Root/MessageInformation/SuspicionsCriterians';
  begin
    if xpath.test(theXmlDoc, PATH_SuspicCrit) and
       xpath.test(theXmlDoc, PATH_SubmisBas) then
      vSubmisBas  := xpath.valueOf(theXmlDoc, PATH_SubmisBas);
      vSuspicCrit := xpath.valueOf(theXmlDoc, PATH_SuspicCrit);

      if (vSubmisBas = '3') and vSuspicCrit is null then
        -- неверное значение
        pVerifyResult := ERROR;
        pResult       := ERROR_RES;
      else
        pVerifyResult := OK;
      end if;
    else
      -- значениее не задано в XML
      pVerifyResult := NOT_FOUND;
      pResult       := ERROR_RES;
    end if;
    return pResult;
  exception
    when others then
      -- неверное значение
      pVerifyResult := ERROR;
      pResult       := ERROR_RES;
      return pResult;
  end;

  function CheckSuspicOrder(theXmlDoc     xmldom.DOMDocument,
                            pVerifyResult out varchar2) return number is
    pResult     varchar2(100 char) default 0;
    vValueFirst varchar2(100 char);

    vValue varchar2(100 char);
    PATH_SubmisBas      constant varchar2(50 char) := '/Root/MessageInformation/SubmissionsBasises';
    PATH_SuspicionFirst constant varchar2(50 char) := '/Root/MessageInformation/SuspicionFirst';
  begin

    if xpath.test(theXmlDoc, PATH_SubmisBas) then
      vValue := xpath.valueOf(theXmlDoc, PATH_SubmisBas);
      if vValue is not null then
        if vValue = '2' or vValue = '3' then
          --pVerifyResult := OK;
          if xpath.test(theXmlDoc, PATH_SuspicionFirst) then
            vValueFirst := xpath.valueOf(theXmlDoc, PATH_SuspicionFirst);
            if vValueFirst is null then
              pVerifyResult := ERROR;
              pResult       := ERROR_RES;
            else
              pVerifyResult := OK;
            end if;
          else
            pVerifyResult := NOT_FOUND;
            pResult       := ERROR_RES;
          end if;

        else
          pVerifyResult := OK;
        end if;
      else
        -- не введено значение
        pVerifyResult := NOT_CONDITION;
        pResult       := ERROR_RES;
      end if;

    else
      -- значение не задано в XML
      pVerifyResult := NOT_CONDITION;
      pResult       := ERROR_RES;
    end if;
    return pResult;
  exception
    when others then
      -- неверное значение
      pVerifyResult := ERROR;
      pResult       := ERROR_RES;
      return pResult;
  end;

  function CheckReportsTypeAndMoreInf(theXmlDoc     xmldom.DOMDocument,
                                      pVerifyResult out varchar2)
    return number is
    pResult          varchar2(100 char) default 0;
    vMoreInformation varchar2(100 char);
    vReportsType     varchar2(100 char);
    PATH_ReportsType     constant varchar2(50 char) := '/Root/MessageInformation/ReportsType';
    PATH_MoreInformation constant varchar2(50 char) := '/Root/MessageInformation/MoreInformation';

  begin
    if xpath.test(theXmlDoc, PATH_ReportsType) then
      vReportsType := xpath.valueOf(theXmlDoc, PATH_ReportsType);
      if vReportsType = '3' then
        if xpath.test(theXmlDoc, PATH_MoreInformation) then
          vMoreInformation := xpath.valueOf(theXmlDoc, PATH_MoreInformation);
          if vMoreInformation is null then
            -- неверное значение
            pVerifyResult := ERROR;
            pResult       := ERROR_RES;
          else
            pVerifyResult := OK;
          end if;
        else
          -- значениее не задано в XML
          pVerifyResult := NOT_FOUND;
          pResult       := ERROR_RES;
        end if;
      else
        pVerifyResult := OK;
      end if;
    else
      -- значениее не задано в XML
      pVerifyResult := NOT_FOUND;
      pResult       := ERROR_RES;
    end if;
    return pResult;
  exception
    when others then
      -- неверное значение
      pVerifyResult := ERROR;
      pResult       := ERROR_RES;
      return pResult;
  end;

  function CheckParticipantCount(theXmlDoc     xmldom.DOMDocument,
                                 pVerifyResult out varchar2) return number is
    pResult           varchar2(100 char) default 0;
    vParticipantCount varchar2(100 char);
    vCount            number;
    PATH_ParticipantCount constant varchar2(50 char) := '/Root/MessageInformation/ParticipantCount';
    PATH_Participant      constant varchar2(50 char) := '/Root/ParticipantsList/Participant';
  begin
    if xpath.test(theXmlDoc, PATH_ParticipantCount) then
      vParticipantCount := xpath.valueOf(theXmlDoc, PATH_ParticipantCount);
      vCount            := xpath.countNodes(theXmlDoc, PATH_Participant);
      if vParticipantCount is not null then
        if vParticipantCount != to_char(vCount) then
          -- неверное значение
          pVerifyResult := ERROR;
          pResult       := ERROR_RES;
        else
          pVerifyResult := OK;
        end if;
      else
        -- не введено значение
        pVerifyResult := EMPTY;
        pResult       := ERROR_RES;
      end if;
    else
      -- значениее не задано в XML
      pVerifyResult := NOT_FOUND;
      pResult       := ERROR_RES;
    end if;
    return pResult;
  exception
    when others then
      -- неверное значение
      pVerifyResult := ERROR;
      pResult       := ERROR_RES;
      return pResult;
  end;

  function CheckIDViewOperation(theXmlDoc     xmldom.DOMDocument,
                                pVerifyResult out varchar2) return number is
    pResult varchar2(100 char) default 0;
    vValue  varchar(10);
    PATH_SubmisBas  constant varchar2(50 char) := '/Root/MessageInformation/SubmissionsBasises';
    PATH_IDViewOper constant varchar2(50 char) := '/Root/MessageInformation/IDViewOperation';
  begin
    if xpath.test(theXmlDoc, PATH_SubmisBas) then
      vValue := xpath.valueOf(theXmlDoc, PATH_SubmisBas);
      if vValue is not null then
        if vValue = '2' or vValue = '3' then
          pVerifyResult := OK;
        elsif vValue = '1' then
          vValue := xpath.valueOf(theXmlDoc, PATH_IDViewOper);
          if vValue is null then
            pVerifyResult := EMPTY;
            pResult       := ERROR_RES;
          else
            pVerifyResult := OK;
          end if;
        else
          -- неверное значение
          pVerifyResult := NOT_CONDITION; --неверно задано условие проверки
          pResult       := ERROR_RES;

        end if;
      else
        -- не введено значение
        pVerifyResult := NOT_CONDITION;
        pResult       := ERROR_RES;
      end if;
    else
      -- значение не задано в XML
      pVerifyResult := NOT_CONDITION;
      pResult       := ERROR_RES;
    end if;
    return pResult;
  exception
    when others then
      -- неверное значение
      pVerifyResult := ERROR;
      pResult       := ERROR_RES;
      return pResult;
  end;
  function CheckOKPO(theXmlDoc     xmldom.DOMDocument,
                     pVerifyResult out varchar2) return number is
    pResult   varchar2(100 char) default 0;
    vvalue    varchar(30);
    vnode     xmldom.domnode;
    pnode     xmldom.domnode;
    vnodelist xmldom.DOMNodeList;
    PATH_PersonType  constant varchar2(50 char) := 'ParticipantsType';
    PATH_Participant constant varchar2(50 char) := '/Root/ParticipantsList/Participant';
    PATH_OKPO        constant varchar2(50 char) := 'OKPO';
  begin
    PNODE         := xmldom.makeNode(thexmldoc);
    pVerifyResult := OK;
    vNodeList     := pkg_messages_xml.GetNodesList(PNODE, PATH_Participant);
    for i in 0 .. xmldom.getLength(vNodeList) - 1 loop
      vnode := xmldom.item(vNodeList, i);
      if xpath.test(vnode, PATH_PersonType) then
        vValue := xslprocessor.valueOf(vnode, PATH_PersonType || '/text()');
        if vvalue = '1' then
          if xpath.test(vnode, PATH_OKPO) then
            vValue := xslprocessor.valueOf(vnode, PATH_OKPO || '/text()');
            if vvalue is null then
              pVerifyResult := EMPTY;
              pResult       := ERROR_RES;
            end if;
          else
            pVerifyResult := NOT_FOUND;
            pResult       := ERROR_RES;
          end if;
        end if;

      else
        pVerifyResult := NOT_CONDITION; --изменить на отсутсв инф о типе лица
        pResult       := ERROR_RES;
      end if;
    end loop;
    return pResult;
  exception
    when others then
      -- неверное значение
      pVerifyResult := ERROR;
      pResult       := ERROR_RES;
      return pResult;
  end;
  function CheckOKED(theXmlDoc     xmldom.DOMDocument,
                     pVerifyResult out varchar2) return number is
    pResult varchar2(100 char) default 0;
    vvalue  varchar(30);
    vcount  number;

    vnode     xmldom.domnode;
    pnode     xmldom.domnode;
    vnodelist xmldom.DOMNodeList;
    PATH_PersonType  constant varchar2(50 char) := 'ParticipantsType';
    PATH_Participant constant varchar2(50 char) := '/Root/ParticipantsList/Participant';
    PATH_OKED        constant varchar2(50 char) := 'OKED';
  begin
    PNODE         := xmldom.makeNode(thexmldoc);
    pVerifyResult := OK;
    vNodeList     := pkg_messages_xml.GetNodesList(PNODE, PATH_Participant);
    for i in 0 .. xmldom.getLength(vNodeList) - 1 loop
      vnode := xmldom.item(vNodeList, i);
      if xpath.test(vnode, PATH_PersonType) then
        vValue := xslprocessor.valueOf(vnode, PATH_PersonType || '/text()');
        if vvalue = '1' then
          if xpath.test(vnode, PATH_OKED) then
            vValue := xslprocessor.valueOf(vnode, PATH_OKED || '/text()');
            if vvalue is null then
              pVerifyResult := EMPTY;
              pResult       := ERROR_RES;
            end if;

            select count(*)
              into vcount
              from AnalyticCodes ac
             where ac.analytictype = 1021
               and ac.shortname = vvalue;
            if vcount = 0 then
              pVerifyResult := EMPTY;
              pResult       := ERROR_RES;
            end if;

          else
            pVerifyResult := NOT_FOUND;
            pResult       := ERROR_RES;
          end if;
        end if;

      else
        pVerifyResult := NOT_CONDITION; --изменить на отсутсв инф о типе лица
        pResult       := ERROR_RES;
      end if;
    end loop;
    return pResult;
  exception
    when others then
      -- неверное значение
      pVerifyResult := ERROR;
      pResult       := ERROR_RES;
      return pResult;
  end;

  function CheckResidence(theXmlDoc     xmldom.DOMDocument,
                          pVerifyResult out varchar2) return number is
    pResult   varchar2(100 char) default 0;
    vvalue    varchar(30);
    vvalue2   varchar(30);
    vnode     xmldom.domnode;
    pnode     xmldom.domnode;
    vnodelist xmldom.DOMNodeList;

    RESIDCOMPARE           constant varchar2(61 char) := 'Отметьте флагом поле резиденство';
    PATH_Participant       constant varchar2(50 char) := '/Root/ParticipantsList/Participant';
    PATH_Residence         constant varchar2(50 char) := 'MemberCommunity';
    PATH_Residence_Country constant varchar2(50 char) := 'MemberCommunitySityCode';
  begin
    PNODE         := xmldom.makeNode(thexmldoc);
    pVerifyResult := OK;
    vNodeList     := pkg_messages_xml.GetNodesList(PNODE, PATH_Participant);
    for i in 0 .. xmldom.getLength(vNodeList) - 1 loop
      vnode := xmldom.item(vNodeList, i);
      if xpath.test(vnode, PATH_Residence) then
        vValue := xslprocessor.valueOf(vnode, PATH_Residence || '/text()');

        if vvalue = 'false' then
          if xpath.test(vnode, PATH_Residence_Country) then
            vValue2 := xslprocessor.valueOf(vnode,
                                            PATH_Residence_Country ||
                                            '/text()');
            if vvalue2 is null then
              pVerifyResult := EMPTY;
              pResult       := ERROR_RES; --удалить тк не обязательно
            elsif vvalue2 = 398 then
              pVerifyResult := RESIDCOMPARE;
              pResult       := ERROR_RES;
            end if;
          else
            pVerifyResult := NOT_FOUND;
            pResult       := ERROR_RES;
          end if;
        end if;

      else
        pVerifyResult := NOT_FOUND; --изменить на отсутсв инф о типе лица
        pResult       := ERROR_RES;
      end if;
    end loop;
    return pResult;
  exception
    when others then
      -- неверное значение
      pVerifyResult := ERROR;
      pResult       := ERROR_RES;
      return pResult;
  end;

  function CheckComments(theXmlDoc     xmldom.DOMDocument,
                         pVerifyResult out varchar2) return number is
    pResult varchar2(100 char) default 0;

    vvalue varchar(4000);

    vnode     xmldom.domnode;
    pnode     xmldom.domnode;
    vnodelist xmldom.DOMNodeList;
    PATH_Comments    constant varchar2(50 char) := 'AdditionalInformation';
    PATH_Participant constant varchar2(50 char) := '/Root/ParticipantsList/Participant';
    PATH_SubmisBas   constant varchar2(50 char) := '/Root/MessageInformation/SubmissionsBasises';
  begin
    PNODE         := xmldom.makeNode(thexmldoc);
    pVerifyResult := OK;
    if xpath.test(pnode, PATH_SubmisBas) then
      vValue := xpath.valueOf(pnode, PATH_SubmisBas);
      if vValue is not null then
        if vValue = '1' then
          pVerifyResult := OK;
        elsif vValue = '2' or vValue = '3' then

          vNodeList := pkg_messages_xml.GetNodesList(PNODE,
                                                     PATH_Participant);
          for i in 0 .. xmldom.getLength(vNodeList) - 1 loop
            vnode := xmldom.item(vNodeList, i);
            if xpath.test(vnode, PATH_Comments) then
              vValue := xslprocessor.valueOf(vnode,
                                             PATH_Comments || '/text()');
              if vvalue is null then
                pVerifyResult := EMPTY;
                pResult       := ERROR_RES;
              end if;
            else
              pVerifyResult := NOT_FOUND;
              pResult       := ERROR_RES;
            end if;
          end loop;
        else
          -- неверное значение
          pVerifyResult := NOT_CONDITION;
          pResult       := ERROR_RES;
        end if;
      else
        -- не введено значение
        pVerifyResult := NOT_CONDITION;
        pResult       := ERROR_RES;
      end if;
    else
      -- значениее не задано в XML
      pVerifyResult := NOT_CONDITION;
      pResult       := ERROR_RES;
    end if;
    return pResult;
  exception
    when others then
      -- неверное значение
      pVerifyResult := ERROR;
      pResult       := ERROR_RES;
      return pResult;
  end;

  function GetVerifyList(pMessageVersion varchar2,
                         pResCursor      out sys_refcursor) return number is
    vMessageType   number;
    vMessageVersn  number;
    vMessageVersn1 varchar2(50 char);
    Result         number default 0;
  begin
    result := pkg_common.GetFormattedGuid(pMessageVersion, vMessageVersn1);
    if result <> 0 then
      return result;
    end if;
    select mv.mess_type, mv.versionid
      into vMessageType, vMessageVersn
      from messages_versions mv
     where mv.versioncode = vMessageVersn1;

    open pResCursor for
      select mvs.verifydescription as Name,
             'Root/' || mfd.field_xpath as ExpressionValue,
             mvs.verifytype as ExpressionType,
             mvs.verifyxpathid as Description,
             mvs.verifyxpathtype as Type,
             mvs.verifydescription as ErrorDescription,
             mvs.valuestart as StartValue,
             mvs.valueend as EndValue
        from mess_verify_setup mvs, messages_field_dic mfd
       where mvs.messagetype = vMessageType
         and mvs.messageversion = vMessageVersn
         and mvs.messagetype = mfd.mess_type
         and mvs.messageversion = mfd.version_id
         and mfd.field_id = mvs.paramid
         and mvs.verifytype = 1
         and isenable = 1
      union all
      select mvs.verifydescription,
             mvs.verifystring,
             mvs.verifytype,
             mvs.verifyxpathid,
             mvs.verifyxpathtype,
             mvs.verifydescription,
             mvs.valuestart,
             mvs.valueend
        from mess_verify_setup mvs
       where mvs.messagetype = vMessageType
         and mvs.messageversion = vMessageVersn
         and mvs.verifytype = 2
         and isenable = 1;
    return Result;

  exception
    when others then
      Result := -1; --Общая ошибка
      return Result;
  end GetVerifyList;

  procedure BaseVerifyComplete(pMessXml in clob,
                               pMESSId  in number, -- Version$Type
                               pCfmType in number) is
    result        number default 0;
    vXML          xmltype;
    vVersion      number;
    vType         number;
    tException    varchar2(4000 char) default '';
    tPersonsCount number default 0;
    tPersonType   number default 0;
    ex_not_correct exception;
    pragma exception_init(ex_not_correct, -20004);
    pAreaCode     number;
    pDistrictCode number;
    pCityCode     number;
    pStreetCode   number;
    pHouseCode    number;
    pOfficeCode   number;
    pArea         varchar2(4000 char);
    pDistrict     varchar2(4000 char);
    pCity         varchar2(4000 char);
    pStreet       varchar2(4000 char);
    pHouse        varchar2(4000 char);
    pOffice       varchar2(4000 char);
    RootPath      varchar2(4000 char);
  begin
    vxml := xmltype.createxml(pMessXml);
    select messtype, messversion
      into vType, vVersion
      from tmp_mess_version t
     where t.messid = pMessId;

    for t in (select pkg_analytic.containsCode1(mfd.field_dic_num,
                                                value(t).extract('*/text()')
                                                .getNumberVal()) Verify_Res,
                     chr(10) || chr(13) || mvs.verifydescription as verifydescription
                from mess_verify_setup mvs,
                     mess_verify_setup_cfm mvs_cfm,
                     messages_field_dic mfd,
                     table(XMLSequence(extract(vxml,
                                               '/Root/' || mfd.field_xpath))) t

               where mvs.verifyid = mvs_cfm.verifyid
                 and mfd.field_dic_type = 0
                 and mvs_cfm.cfmcode = pCfmType
                    --pCfmType
                 and verifyxpathid = 6
                 and mvs.base_realisaton = 1
                 and mvs.verifytype = 1
                 and mvs.isenable = 1
                 and mvs_cfm.status = 1
                 and mfd.mess_type = vType
                    --mvs.messagetype
                 and mfd.version_id = vVersion
                    --mvs.messageversion
                 and mfd.field_id = mvs.paramid)

     loop
      if t.verify_res = 0 then
        tException := tException || t.verifydescription ||
                      ' не установлено';
      end if;
    end loop;

    for prow in (select mvs.verifystring, mvs.verifydescription
                   from mess_verify_setup mvs, mess_verify_setup_cfm mvs_cfm
                  where mvs.verifytype = 2
                    and mvs.verifyid = mvs_cfm.verifyid
                    and mvs.isenable = 1
                    and mvs_cfm.status = 1
                    and mvs_cfm.cfmcode = pCfmType
                    and mvs.messagetype = vType
                    and mvs.messageversion = vVersion
                    and mvs.base_realisaton = 1) loop
      case prow.verifystring
        when 'CheckOrgAdress' then
          begin
            if vType = 1 then
              select extractvalue(vxml,
                                  'Root/PersonalData/OrganisationArea/@code'),
                     extractvalue(vxml,
                                  'Root/PersonalData/OrganisationArea/text()'),
                     extractvalue(vxml,
                                  'Root/PersonalData/OrganisationDistrict/@code'),
                     extractvalue(vxml,
                                  'Root/PersonalData/OrganisationDistrict/text()'),
                     extractvalue(vxml,
                                  'Root/PersonalData/OrganisationCity/@code'),
                     extractvalue(vxml,
                                  'Root/PersonalData/OrganisationCity/text()'),
                     extractvalue(vxml,
                                  'Root/PersonalData/OrganisationStreet/@code'),
                     extractvalue(vxml,
                                  'Root/PersonalData/OrganisationStreet/text()'),
                     extractvalue(vxml,
                                  'Root/PersonalData/OrganisationHouse/@code'),
                     extractvalue(vxml,
                                  'Root/PersonalData/OrganisationHouse/text()'),
                     extractvalue(vxml,
                                  'Root/PersonalData/OrganisationOffice/@code'),
                     extractvalue(vxml,
                                  'Root/PersonalData/OrganisationOffice/text()')
                into pAreaCode,
                     pArea,
                     pDistrictCode,
                     pDistrict,
                     pCityCode,
                     pCity,
                     pStreetCode,
                     pStreet,
                     pHouseCode,
                     pHouse,
                     pOfficeCode,
                     pOffice
                from dual;
            elsif vType = 11 then
              select extractvalue(vxml, 'Root/OrganisationData/Area/@code'),
                     extractvalue(vxml, 'Root/OrganisationData/Area/text()'),
                     extractvalue(vxml,
                                  'Root/OrganisationData/District/@code'),
                     extractvalue(vxml,
                                  'Root/OrganisationData/District/text()'),
                     extractvalue(vxml, 'Root/OrganisationData/City/@code'),
                     extractvalue(vxml, 'Root/OrganisationData/City/text()'),
                     extractvalue(vxml,
                                  'Root/OrganisationData/Street/@code'),
                     extractvalue(vxml,
                                  'Root/OrganisationData/Street/text()'),
                     extractvalue(vxml, 'Root/OrganisationData/House/@code'),
                     extractvalue(vxml,
                                  'Root/OrganisationData/House/text()'),
                     extractvalue(vxml,
                                  'Root/OrganisationData/Office/@code'),
                     extractvalue(vxml,
                                  'Root/OrganisationData/Office/text()')
                into pAreaCode,
                     pArea,
                     pDistrictCode,
                     pDistrict,
                     pCityCode,
                     pCity,
                     pStreetCode,
                     pStreet,
                     pHouseCode,
                     pHouse,
                     pOfficeCode,
                     pOffice
                from dual;
            end if;

            result := 0; --PKG_ISARHolding.IsCorrectAdress(pAreaCode,pDistrictCode,pCityCode,pStreetCode,pHouseCode,pOfficeCode,
            --pArea,pDistrict,pCity,pStreet,pHouse,pOffice);
          exception
            when ex_not_correct then
              tException := ltrim(tException || chr(10) || chr(13)) ||
                            prow.verifydescription || ' не выполнена:' ||
                            substr(sqlerrm, instr(sqlerrm, ':') + 1, 99999);
            when others then
              raise;
          end;
        when 'CheckPersonUrAdress' then

          select count(*)
            into tPersonsCount
            from table(xmlsequence(extract(vxml,
                                           'Root/Participants/Participant')));
          for i in 1 .. tPersonsCount loop
            select extractvalue(vxml,
                                'Root/Participants/Participant[' || i ||
                                ']/ParticipantsType/text()')
              into tPersonType
              from dual;
            case tPersonType
              when 1 then
                RootPath := 'Root/Participants/Participant[' || i ||
                            ']/AdditionalPersonInfo/AdditionalInformationUr/URAddress/';
              when 2 then
                RootPath := 'Root/Participants/Participant[' || i ||
                            ']/AdditionalPersonInfo/AdditionalInformationAc/URAddress/';
              when 3 then
                RootPath := 'Root/Participants/Participant[' || i ||
                            ']/AdditionalPersonInfo/AdditionalInformationIp/URAddress/';
              else
                RootPath := null;
            end case;
            if RootPath is not null then
              if xmltype.existsnode(vxml, RootPath || 'Country[text()=398]') = 1 then
                select extractvalue(vxml, RootPath || 'Area/@code'),
                       extractvalue(vxml, RootPath || 'Area/text()'),
                       extractvalue(vxml, RootPath || 'District/@code'),
                       extractvalue(vxml, RootPath || 'District/text()'),
                       extractvalue(vxml, RootPath || 'Town/@code'),
                       extractvalue(vxml, RootPath || 'Town/text()'),
                       extractvalue(vxml, RootPath || 'Street/@code'),
                       extractvalue(vxml, RootPath || 'Street/text()'),
                       extractvalue(vxml, RootPath || 'HomeNumber/@code'),
                       extractvalue(vxml, RootPath || 'HomeNumber/text()'),
                       extractvalue(vxml, RootPath || 'OfficeNumber/@code'),
                       extractvalue(vxml, RootPath || 'OfficeNumber/text()')
                  into pAreaCode,
                       pArea,
                       pDistrictCode,
                       pDistrict,
                       pCityCode,
                       pCity,
                       pStreetCode,
                       pStreet,
                       pHouseCode,
                       pHouse,
                       pOfficeCode,
                       pOffice
                  from dual;
                begin
                  result := 0; --PKG_ISARHolding.IsCorrectAdress(pAreaCode,pDistrictCode,pCityCode,pStreetCode,pHouseCode,pOfficeCode,
                  --pArea,pDistrict,pCity,pStreet,pHouse,pOffice);
                exception
                  when ex_not_correct then
                    tException := ltrim(tException || chr(10) || chr(13)) ||
                                  prow.verifydescription ||
                                  ' не выполнена:' ||
                                  substr(sqlerrm,
                                         instr(sqlerrm, ':') + 1,
                                         99999);
                  when others then
                    raise;
                end;
              end if;
            end if;

          end loop;

        when 'CheckPersonAcAdress' then
          select count(*)
            into tPersonsCount
            from table(xmlsequence(extract(vxml,
                                           'Root/Participants/Participant')));
          for i in 1 .. tPersonsCount loop
            select extractvalue(vxml,
                                'Root/Participants/Participant[' || i ||
                                ']/ParticipantsType/text()')
              into tPersonType
              from dual;
            case tPersonType
              when 1 then
                RootPath := 'Root/Participants/Participant[' || i ||
                            ']/AdditionalPersonInfo/AdditionalInformationUr/ACAddress/';
              when 2 then
                RootPath := 'Root/Participants/Participant[' || i ||
                            ']/AdditionalPersonInfo/AdditionalInformationAc/ACAddress/';
              when 3 then
                RootPath := 'Root/Participants/Participant[' || i ||
                            ']/AdditionalPersonInfo/AdditionalInformationIp/ACAddress/';
              else
                RootPath := null;
            end case;
            if RootPath is not null then
              if xmltype.existsnode(vxml, RootPath || 'Country[text()=398]') = 1 then
                select extractvalue(vxml, RootPath || 'Area/@code'),
                       extractvalue(vxml, RootPath || 'Area/text()'),
                       extractvalue(vxml, RootPath || 'District/@code'),
                       extractvalue(vxml, RootPath || 'District/text()'),
                       extractvalue(vxml, RootPath || 'Town/@code'),
                       extractvalue(vxml, RootPath || 'Town/text()'),
                       extractvalue(vxml, RootPath || 'Street/@code'),
                       extractvalue(vxml, RootPath || 'Street/text()'),
                       extractvalue(vxml, RootPath || 'HomeNumber/@code'),
                       extractvalue(vxml, RootPath || 'HomeNumber/text()'),
                       extractvalue(vxml, RootPath || 'OfficeNumber/@code'),
                       extractvalue(vxml, RootPath || 'OfficeNumber/text()')
                  into pAreaCode,
                       pArea,
                       pDistrictCode,
                       pDistrict,
                       pCityCode,
                       pCity,
                       pStreetCode,
                       pStreet,
                       pHouseCode,
                       pHouse,
                       pOfficeCode,
                       pOffice
                  from dual;
                begin
                  result := 0; --PKG_ISARHolding.IsCorrectAdress(pAreaCode,pDistrictCode,pCityCode,pStreetCode,pHouseCode,pOfficeCode,
                  --pArea,pDistrict,pCity,pStreet,pHouse,pOffice);
                exception
                  when ex_not_correct then
                    tException := ltrim(tException || chr(10) || chr(13)) ||
                                  prow.verifydescription ||
                                  ' не выполнена:' ||
                                  substr(sqlerrm,
                                         instr(sqlerrm, ':') + 1,
                                         99999);
                  when others then
                    raise;
                end;
              end if;
            end if;

          end loop;
        else
          null;
      end case;

    end loop;
    if length(tException) > 0 then
      raise_application_error(-20000, tException);
    end if;

  end;

  function CheckOrgAdress(vxml            xmltype,
                          pMESSType       in number,
                          pMessageVersion in number) return clob is
    result clob default '';

    tException varchar2(4000 char) default '';

    ex_not_correct exception;
    pragma exception_init(ex_not_correct, -20004);
    pAreaCode     number;
    pDistrictCode number;
    pCityCode     number;
    pStreetCode   number;
    pHouseCode    number;
    pOfficeCode   number;
    pArea         varchar2(4000 char);
    pDistrict     varchar2(4000 char);
    pCity         varchar2(4000 char);
    pStreet       varchar2(4000 char);
    pHouse        varchar2(4000 char);
    pOffice       varchar2(4000 char);

  begin
    select extractvalue(vxml, 'Root/OrganisationData/Area/@code'),
           extractvalue(vxml, 'Root/OrganisationData/Area/text()'),
           extractvalue(vxml, 'Root/OrganisationData/District/@code'),
           extractvalue(vxml, 'Root/OrganisationData/District/text()'),
           extractvalue(vxml, 'Root/OrganisationData/City/@code'),
           extractvalue(vxml, 'Root/OrganisationData/City/text()'),
           extractvalue(vxml, 'Root/OrganisationData/Street/@code'),
           extractvalue(vxml, 'Root/OrganisationData/Street/text()'),
           extractvalue(vxml, 'Root/OrganisationData/House/@code'),
           extractvalue(vxml, 'Root/OrganisationData/House/text()'),
           extractvalue(vxml, 'Root/OrganisationData/Office/@code'),
           extractvalue(vxml, 'Root/OrganisationData/Office/text()')
      into pAreaCode,
           pArea,
           pDistrictCode,
           pDistrict,
           pCityCode,
           pCity,
           pStreetCode,
           pStreet,
           pHouseCode,
           pHouse,
           pOfficeCode,
           pOffice
      from dual;

    result := '0'; --to_char(PKG_ISARHolding.IsCorrectAdress(pAreaCode,pDistrictCode,pCityCode,pStreetCode,pHouseCode,pOfficeCode,
    --pArea,pDistrict,pCity,pStreet,pHouse,pOffice));
    return null;
  exception
    when ex_not_correct then
      tException := 'Проверка указания адреса организации в соответствии со справочником ИС АР:' ||
                    substr(substr(sqlerrm, instr(sqlerrm, ':') + 1, 99999),
                           2,
                           99999);
      return tException;
    when others then
      return null;

  end;

  function CheckRNN(pRNN 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(pRNN) <> 12 then
      return - 1;
    elsif pRNN = pCheckVal then
      return - 2;
    elsif pRNN = REPLACE(pCheckVal, '0', '1') then
      return - 2;
    elsif pRNN = REPLACE(pCheckVal, '0', '2') then
      return - 2;
    elsif pRNN = REPLACE(pCheckVal, '0', '3') then
      return - 2;
    elsif pRNN = REPLACE(pCheckVal, '0', '4') then
      return - 2;
    elsif pRNN = REPLACE(pCheckVal, '0', '5') then
      return - 2;
    elsif pRNN = REPLACE(pCheckVal, '0', '6') then
      return - 2;
    elsif pRNN = REPLACE(pCheckVal, '0', '7') then
      return - 2;
    elsif pRNN = REPLACE(pCheckVal, '0', '8') then
      return - 2;
    elsif pRNN = REPLACE(pCheckVal, '0', '9') then
      return - 2;

    end if;
    begin

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

        pContr := mod(pSum, 11);
        if Pcontr < 10 then
          IF Pcontr <> CAST(SUBSTR(pRNN, 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 CheckBIN(pBIN 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(pBIN) <> 12 then
      return - 1;
    elsif pBIN = pCheckVal then
      return - 2;
    elsif pBIN = REPLACE(pCheckVal, '0', '1') then
      return - 2;
    elsif pBIN = REPLACE(pCheckVal, '0', '2') then
      return - 2;
    elsif pBIN = REPLACE(pCheckVal, '0', '3') then
      return - 2;
    elsif pBIN = REPLACE(pCheckVal, '0', '4') then
      return - 2;
    elsif pBIN = REPLACE(pCheckVal, '0', '5') then
      return - 2;
    elsif pBIN = REPLACE(pCheckVal, '0', '6') then
      return - 2;
    elsif pBIN = REPLACE(pCheckVal, '0', '7') then
      return - 2;
    elsif pBIN = REPLACE(pCheckVal, '0', '8') then
      return - 2;
    elsif pBIN = REPLACE(pCheckVal, '0', '9') then
      return - 2;
    end if;

    begin
      while (pWeight <= 3) loop
        pSum   := CAST(SUBSTR(pBIN, 1, 1) as int) * (mod(pWeight, 11) + 1) +
                  CAST(SUBSTR(pBIN, 2, 1) as int) *
                  (mod(pWeight + 1, 11) + 1) + CAST(SUBSTR(pBIN, 3, 1) as int) *
                  (mod(pWeight + 2, 11) + 1) + CAST(SUBSTR(pBIN, 4, 1) as int) *
                  (mod(pWeight + 3, 11) + 1) + CAST(SUBSTR(pBIN, 5, 1) as int) *
                  (mod(pWeight + 4, 11) + 1) + CAST(SUBSTR(pBIN, 6, 1) as int) *
                  (mod(pWeight + 5, 11) + 1) + CAST(SUBSTR(pBIN, 7, 1) as int) *
                  (mod(pWeight + 6, 11) + 1) + CAST(SUBSTR(pBIN, 8, 1) as int) *
                  (mod(pWeight + 7, 11) + 1) + CAST(SUBSTR(pBIN, 9, 1) as int) *
                  (mod(pWeight + 8, 11) + 1) + CAST(SUBSTR(pBIN, 10, 1) as int) *
                  (mod(pWeight + 9, 11) + 1) + CAST(SUBSTR(pBIN, 11, 1) as int) *
                  (mod(pWeight + 10, 11) + 1);
        pContr := mod(pSum, 11);
        if Pcontr < 10 then
          IF Pcontr <> CAST(SUBSTR(pBIN, 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;

  function CheckBINWithBirth(pBIN in varchar2, pBirth in varchar2)
    return boolean is
    result      boolean default true;
    pdate       date;
    pstringdate varchar2(8);
  begin
    pdate       := to_date(pBirth, 'dd.mm.yyyy');
    pstringdate := to_char(pdate, 'yymmdd');
    result      := (substr(pBIN, 1, 6) = pstringdate);
    return result;
  exception
    when others then
      return false;
  end;

  function CheckNoSymbols(pString varchar2, pSymbolsString varchar2)
    return boolean is
    result   boolean default true;
    pCounter number default 1;

    pLen      number;
    pPosition number default 0;
  begin
    pLen := length(pSymbolsString);
    while pCounter <= pLen loop
      pPosition := instr(pString, substr(pSymbolsString, pCounter, 1));
      if pPosition <> 0 then
        return false;
      end if;
      pCounter := pCounter + 1;
    end loop;
    return result;
  end;

  /*  function CheckAllowedSymbols(pString varchar2,pSymbolsString varchar2) return boolean
  is
   result boolean default true;
   pCounter number default 1;

   pLen number;
   pPosition number default 0;
   pAllowedSymbols varchar2(2000):='0123456789ЁЙЦУКЕНГШЩЗХЪФЫВАПРОЛДЖЭЯЧСМИТЬБЮёйцукенгшщзхъфывапролджэячсмитьбюQWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm';
  begin
  \* pAllowedSymbols:=pAllowedSymbols||pSymbolsString;
   pLen:= length(pString);
   while pCounter<=pLen
   loop
      pPosition:=instr(pAllowedSymbols,substr(pString,pCounter,1));
      if pPosition=0 then
         return false;
      end if;
      pCounter:=pCounter+1;
   end loop;*\
   return result;
  end; */

  function CheckAllowedSymbols(pString nvarchar2, pSymbolsString varchar2)
    return boolean is
    result              boolean default true;
    pCounter            number default 1;
    pLen                number;
    pPosition           number default 0;
    pSymbolsString_temp varchar2(4000 char);
    pAllowedSymbols     nvarchar2(2000) := '';
    pDecVal             number;
  begin
    pSymbolsString_temp := pSymbolsString;

    pPosition := instr(pSymbolsString_temp, '\u');
    if pPosition = 0 then
      pAllowedSymbols := pSymbolsString_temp;
    else
      pAllowedSymbols := substr(pSymbolsString_temp, 1, pPosition - 1);
      while pPosition > 0 loop
        pDecVal             := to_number(substr(pSymbolsString_temp,
                                                pPosition + 2,
                                                4),
                                         'xxxx');
        pAllowedSymbols     := pAllowedSymbols ||
                               chr(pDecVal USING NCHAR_CS);
        pSymbolsString_temp := substr(pSymbolsString_temp, pPosition + 6);
        pPosition           := instr(pSymbolsString_temp, '\u');
      end loop;
      pAllowedSymbols := pAllowedSymbols || pSymbolsString_temp;
    end if;
    result := REGEXP_LIKE(pString, pAllowedSymbols);

    return result;
  end;

  function CheckForType(pType      in number,
                        pValue     string,
                        pXpathType number,
                        pMinValue  in varchar2,
                        pMaxValue  in varchar2) return number is
    result        number default 0;
    pNumber       number;
    pDate         date;
    pVarchar      nvarchar2(4000 char);
    pRES          boolean;
    pMinValueNum  number;
    pMaxValueNum  number;
    pMinValueDate date;
    pMaxValueDate date;
  begin
    if length(pValue) <> 0 then
      case pType
        when 2 then
          begin
            pNumber := to_number(pValue);
            if pXpathType not in (7, 8, 9) then
              pMinValueNum := to_number(pMinValue);
            end if;
            case pXpathType
              when 1 then
                pRes := (pNumber > pMinValueNum);
              when 2 then
                pRes := (pNumber >= pMinValueNum);
              when 3 then
                pRes := (pNumber < pMinValueNum);
              when 4 then
                null;
                pRes := (pNumber <= pMinValueNum);
              when 5 then
                pRes := (pNumber = pMinValueNum);
              when 6 then
                pMaxValueNum := to_number(pMaxValue);
                pRes         := (pNumber between pMinValueNum and
                                pMaxValueNum);
              when 7 then
                pRes := true;
              when 8 then
                pRes := CheckNoSymbols(pValue, pMinValue);
              when 9 then
                pRes := CheckAllowedSymbols(pValue, pMinValue);
              else
                null;
            end case;
          exception
            when others then
              return - 2;
          end;
        when 3 then
          begin
            pVarchar := to_char(pValue);
            case pXpathType
              when 1 then
                pRes := (pValue > pMinValue);
              when 2 then
                pRes := (pValue >= pMinValue);
              when 3 then
                pRes := (pValue < pMinValue);
              when 4 then
                null;
                pRes := (pValue <= pMinValue);
              when 5 then
                pRes := (pValue = pMinValue);
              when 6 then
                pRes := (pValue between pMinValue and pMaxValue);
              when 7 then
                pRes := true;
              when 8 then
                pRes := CheckNoSymbols(pValue, pMinValue);
              when 9 then
                pRes := CheckAllowedSymbols(pValue, pMinValue);
              else
                null;
            end case;
          exception
            when others then
              return - 3;
          end;
        when 4 then
          begin
            pDate := to_date(pValue, 'dd.mm.yyyy');
            if pMinValue <> 'sysdate' and pXpathType not in (7, 8, 9) then
              pMinValueDate := to_date(pMinValue, 'yyyy-mm-dd');
            else
              pMinValueDate := trunc(sysdate);
            end if;
            case pXpathType
              when 1 then
                pRes := (pDate > pMinValueDate);
              when 2 then
                pRes := (pDate >= pMinValueDate);
              when 3 then
                pRes := (pDate < pMinValueDate);
              when 4 then
                null;
                pRes := (pDate <= pMinValueDate);
              when 5 then
                pRes := (pDate = pMinValueDate);
              when 6 then
                if pMaxValue <> 'sysdate' then
                  pMaxValueDate := to_date(pMaxValue, 'yyyy-mm-dd');
                else
                  pMaxValueDate := trunc(sysdate);
                end if;
                pRes := (pDate between pMinValueDate and pMaxValueDate);
              when 7 then
                pRes := true;
              when 8 then
                pRes := CheckNoSymbols(pValue, pMinValue);
              when 9 then
                pRes := CheckAllowedSymbols(pValue, pMinValue);
              else
                null;
            end case;
          exception
            when others then
              return - 4;
          end;
        when 5 then
          begin
            pNumber := length(pValue);
            case pXpathType
              when 1 then
                pRes := (pNumber > pMinValue);
              when 2 then
                pRes := (pNumber >= pMinValue);
              when 3 then
                pRes := (pNumber < pMinValue);
              when 4 then
                null;
                pRes := (pNumber <= pMinValue);
              when 5 then
                pRes := (pNumber = pMinValue);
              when 6 then
                pRes := (pNumber between pMinValue and pMaxValue);
              when 7 then
                pRes := true;
              when 8 then
                pRes := CheckNoSymbols(pNumber, pMinValue);
              when 9 then
                pRes := CheckAllowedSymbols(pNumber, pMinValue);
            end case;
          exception
            when others then
              return - 5;
          end;
      end case;
    end if;
    if pRes <> true then
      result := -1;
    end if;
    return result;
  end;

  function CheckXpath(pRootXml        in xmltype,
                      pCfmType        in number,
                      pMESSType       in number, -- Version$Type
                      pMessVersion    in number,
                      pSLKExplanation in out clob) return clob is
    pResult clob;
    pRes    number;
  begin
    pResult := empty_clob();

    for t in (select mfd.field_dic_num,
                     dbms_xmlgen.CONVERT(value(t).extract(mfd.checkedtag)
                                         .getclobval(),
                                         dbms_xmlgen.ENTITY_DECODE) Verify_Res,
                     mvs.verifydescription,
                     mfd.field_num_in_form,
                     mfd.roottag,
                     mfd.checkedtag
                from mess_verify_setup mvs,
                     mess_verify_setup_cfm mvs_cfm,
                     messages_field_dic mfd,
                     table(XMLSequence(extract(pRootXml,
                                               nvl2(mfd.roottag,
                                                    '/Root/' || mfd.roottag,
                                                    '/Root')))) t
               where mvs.verifyid = mvs_cfm.verifyid
                 and mvs_cfm.cfmcode = pCfmType
                    --pCfmType
                 and verifyxpathid = 1
                 and mvs.verifytype = 1
                 and mvs.isenable = 1
                 and mvs_cfm.status = 1
                 and mfd.mess_type = pMESSType
                    --mvs.messagetype
                 and mfd.version_id = pMessVersion
                    --mvs.messageversion
                 and mfd.field_id = mvs.paramid
               order by mvs.verifydescription)

     loop
      --  dbms_output.put_line(
      if length(t.Verify_Res) = 0 or t.Verify_Res is null then
        pSLKExplanation := AppendSLKExplanation(pSLKExplanation,
                                                GetSLKExplanationRecord(t.field_num_in_form,
                                                                        'Root/' ||
                                                                        t.roottag || '/' ||
                                                                        replace(replace(t.checkedtag,
                                                                                        regexp_substr(t.checkedtag,
                                                                                                      '[^/]+/')),
                                                                                '/text()'),
                                                                        t.verifydescription));
        pResult         := GetMessage(pResult, t.verifydescription);
      end if;
    end loop;

    for t in (select mfd.field_dic_num,
                     dbms_xmlgen.CONVERT(value(t).extract('*/text()')
                                         .getstringval(),
                                         dbms_xmlgen.ENTITY_DECODE) Verify_Res,
                     mvs.verifydescription,
                     mvs.verifyxpathid,
                     mvs.verifyxpathtype,
                     mvs.valuestart,
                     mvs.valueend,
                     mfd.field_num_in_form,
                     mfd.field_xpath
                from mess_verify_setup mvs,
                     mess_verify_setup_cfm mvs_cfm,
                     messages_field_dic mfd,
                     table(XMLSequence(extract(pRootXml,
                                               '/Root/' || mfd.field_xpath))) t
               where mvs.verifyid = mvs_cfm.verifyid
                 and mvs_cfm.cfmcode = pCfmType
                    --pCfmType
                 and verifyxpathid in (2, 3, 4, 5)
                 and mvs.verifytype = 1
                 and mvs.isenable = 1
                 and mvs_cfm.status = 1
                 and mfd.mess_type = pMESSType
                    --mvs.messagetype
                 and mfd.version_id = pMessVersion
                    --mvs.messageversion
                 and mfd.field_id = mvs.paramid
               order by mvs.verifyxpathid, mvs.paramid)

     loop
      pRes := CheckForType(t.verifyxpathid,
                           t.Verify_Res,
                           t.verifyxpathtype,
                           t.valuestart,
                           t.valueend);
      if pRes <> 0 then
        pSLKExplanation := AppendSLKExplanation(pSLKExplanation,
                                                GetSLKExplanationRecord(t.field_num_in_form,
                                                                        'Root/' ||
                                                                        t.field_xpath,
                                                                        t.verifydescription));
        pResult         := GetMessage(pResult, t.verifydescription);
      end if;

    end loop;

    for t in (select pkg_analytic.containsCode1(mfd.field_dic_num,
                                                value(t).extract('*/text()')
                                                .getstringval()) Verify_Res,
                     chr(10) || chr(13) || mvs.verifydescription as verifydescription,
                     mfd.field_num_in_form,
                     mfd.field_xpath
                from mess_verify_setup mvs,
                     mess_verify_setup_cfm mvs_cfm,
                     messages_field_dic mfd,
                     table(XMLSequence(extract(pRootXml,
                                               '/Root/' || mfd.field_xpath))) t

               where mvs.verifyid = mvs_cfm.verifyid
                 and mfd.field_dic_type = 0
                 and mvs_cfm.cfmcode = pCfmType
                    --pCfmType
                 and verifyxpathid = 6
                 and mvs.base_realisaton = 1
                 and mvs.verifytype = 1
                 and mvs.isenable = 1
                 and mvs_cfm.status = 1
                 and mfd.mess_type = pMESSType
                    --mvs.messagetype
                 and mfd.version_id = pMessVersion
                    --mvs.messageversion
                 and mfd.field_id = mvs.paramid)

     loop
      if t.verify_res = 0 then
        pSLKExplanation := AppendSLKExplanation(pSLKExplanation,
                                                GetSLKExplanationRecord(t.field_num_in_form,
                                                                        'Root/' ||
                                                                        t.field_xpath,
                                                                        t.verifydescription));
        pResult         := GetMessage(pResult, t.verifydescription);
      end if;
    end loop;

    return pResult;
  end;

  /*function CheckXProcs(pRootXml in xmltype,
                         pCfmType in number,
                         pMESSType  in number, -- Version$Type
                         pMessVersion in number
                        ) return clob
    is
     result clob;
     result2 clob;
     pcfmcode number;
     popfcode number;
     prnn varchar2(50 char);
     pbin varchar2(50 char);
     pRes boolean default true;
      pName varchar2(4000 char);
     pIsAC varchar2(50 char);
     pCount number;
    begin
      begin
         select to_number(extractvalue(pRootXml, '/Root/OrganisationData/CfmCode/text()')),
                to_number(extractvalue(pRootXml, '/Root/OrganisationData/OpfCode/text()'))
  --              ,extractvalue(pRootXml, '/Root/OrganisationData/AdditionalAcData/@IsAc')
           into pcfmcode, popfcode
           --,pIsAC
           from dual;
       exception
         when others then
            return 'Не указан код ОПФ';
       end;
     for t in
      ( select
           mvs.verifystring,
           mvs.verifydescription

        from mess_verify_setup mvs,
             mess_verify_setup_cfm mvs_cfm
       where mvs.verifyid = mvs_cfm.verifyid
         and mvs_cfm.cfmcode = pCfmType
         and mvs.messagetype = pMESSType
         and mvs.messageversion  = pMessVersion
         and mvs.verifytype = 2
         and mvs.isenable = 1
         and mvs_cfm.status = 1
         and mvs.base_realisaton=1
         order by verifystring)
        loop
           pRes:=true;
           case  t.verifystring
            when 'CheckCfmCode' then
               if  popfcode = 45  and pcfmcode not in (61,71,72) then
                 pRes:=false;
               end if;
            when 'CheckRNN' then
              begin
               select extractvalue(pRootXml,'/Root/OrganisationData/RNN/text()')
                 into prnn
                from dual;
               exception
                 when others then
                    null;
               end;
               pRes:=(CheckRnn(prnn)>=0);
            when 'CheckBIN' then null;
              begin
               select extractvalue(pRootXml,'/Root/OrganisationData/IINBIN/text()')
                 into pbin
                from dual;
               exception
                 when others then
                    null;
               end;
               pRes:=(CheckBIN(pbin)>=0);
            when 'CheckBINnotequalRNN' then
              begin
               select extractvalue(pRootXml,'/Root/OrganisationData/IINBIN/text()'),
                      extractvalue(pRootXml,'/Root/OrganisationData/RNN/text()')
                 into pbin,prnn
                from dual;
               exception
                 when others then
                    null;
               end;
               pRes:=(pbin<>prnn);
            when 'CheckOrgAdress' then
                 result2:=CheckOrgAdress(pRootXml,pMESSType,pMessVersion);
                 result:=GEtMessage(result,result2);
             when 'CheckAcSecondName' then
             if  popfcode = 45  and pcfmcode in (61,71,72) then
              begin
               pRes:=(xmltype.existsnode(pRootXml,'/Root/OrganisationData/AdditionalAcData[@IsAc=''True'']/SecondName/text()')>0);
               exception
                 when others then
                    pRes:=false;
               end;
              end if;
            when 'CheckAcFirstName' then
             if  popfcode = 45  and pcfmcode in (61,71,72) then
              begin
               pRes:=(xmltype.existsnode(pRootXml,'/Root/OrganisationData/AdditionalAcData[@IsAc=''True'']/FirstName/text()')>0);
               exception
                 when others then
                     pRes:=false;
               end;
             end if;
            when 'CheckAcDocType' then
            if  popfcode = 45  and pcfmcode in (61,71,72) then
              begin
               pRes:=(xmltype.existsnode(pRootXml,'/Root/OrganisationData/AdditionalAcData[@IsAc=''True'']/DocumentIdentity/text()')>0);
               exception
                 when others then
                     pRes:=false;
               end;
             end if;
            when 'CheckAcDocNumber' then
            if  popfcode = 45  and pcfmcode in (61,71,72) then
              begin
               pRes:=(xmltype.existsnode(pRootXml,'/Root/OrganisationData/AdditionalAcData[@IsAc=''True'']/NumberDocIdentity/text()')>0);
               exception
                 when others then
                     pRes:=false;
               end;
             end if;
            when 'CheckAcDocDate' then
            if  popfcode = 45  and pcfmcode in (61,71,72) then
              begin
               pRes:=(xmltype.existsnode(pRootXml,'/Root/OrganisationData/AdditionalAcData[@IsAc=''True'']/DateIssuance/text()')>0);

               exception
                 when others then
                     pRes:=false;
               end;
             end if;
            when 'CheckAcDocOrg' then
            if  popfcode = 45  and pcfmcode in (61,71,72) then
              begin
               pRes:=(xmltype.existsnode(pRootXml,'/Root/OrganisationData/AdditionalAcData[@IsAc=''True'']/DocumentIssued/text()')>0);
               exception
                 when others then
                     pRes:=false;
               end;
             end if;
            when 'CheckPersonsCount' then
             begin
             select count(*) into pCount from table(XMLSequence(extract(pRootXml,
                                       '/Root/OrganisationData/Persons/Person' ))) t;
               pRes:=(pCount>=1);
               exception
                 when others then
                     pRes:=false;
               end;


           else
              null;
         --   when 'CheckCfmCode' then null;
         --   when 'CheckCfmCode' then null;
           end case;
        if Pres=false then
           result:=GEtMessage(result,t.verifydescription);
        end if;
       end loop;



     return result;
    end;

    function VerifyMessageRegistration (pMessage clob, pMessageType number,pMessageVersion number) return clob
    is
      result clob;
      result2 clob;
      vxml xmltype;
      pCfmType number default null;
    begin
      vxml:=xmltype.createxml(pMessage);
      begin
         select extractvalue(vxml,'/Root/OrganisationData/CfmCode/text()') into pCfmType
         from analyticcodes where analytictype=31005 and analyticcode = to_number(extractvalue(vxml,'/Root/OrganisationData/CfmCode/text()')) ;
      exception
        when others then
         raise;
         result:='Не указан (указан неверный) код организации. Дальнейшие проверки не производятся.';
      end;
      if pCfmType is not null then
        result:=CheckXpath(vxml,pCfmType,pMessageType,pMessageVersion);
        result2:=CheckXProcs(vxml,pCfmType,pMessageType,pMessageVersion);

        if length(result)<>0 and length(result2)<>0 then
           dbms_lob.append(result,chr(13)) ;
           dbms_lob.append(result,result2);
        elsif length(result)=0  then
           result:=result2;
        end if;
      else
        result:='Не указан (указан неверный) код организации. Дальнейшие проверки не производятся.';
      end if;
      return result ;
    end;
    */
  function CheckXProcs(pRootXml     in xmltype,
                       pCfmType     in number,
                       pMESSType    in number, -- Version$Type
                       pMessVersion in number) return clob is
    result   clob;
    result2  clob;
    pcfmcode number;
    popfcode number;
    prnn     varchar2(50 char);
    pbin     varchar2(50 char);
    pName    varchar2(4000 char);
    pIsAC    varchar2(50 char);
    pCount   number;
    pRes     boolean default true;
  begin
    -- To do: IsAc
    begin
      select to_number(extractvalue(pRootXml,
                                    '/Root/OrganisationData/CfmCode/text()')),
             to_number(extractvalue(pRootXml,
                                    '/Root/OrganisationData/OpfCode/text()'))
      --              ,extractvalue(pRootXml, '/Root/OrganisationData/AdditionalAcData/@IsAc')
        into pcfmcode, popfcode
      --,pIsAC
        from dual;
    exception
      when others then
        return 'Не указан код ОПФ';
    end;
    for t in (select mvs.verifystring, mvs.verifydescription

                from mess_verify_setup mvs, mess_verify_setup_cfm mvs_cfm
               where mvs.verifyid = mvs_cfm.verifyid
                 and mvs_cfm.cfmcode = pCfmType
                 and mvs.messagetype = pMESSType
                 and mvs.messageversion = pMessVersion
                 and mvs.verifytype = 2
                 and mvs.isenable = 1
                 and mvs_cfm.status = 1
                 and mvs.base_realisaton = 1
               order by verifystring) loop
      pRes := true;
      case t.verifystring
        when 'CheckCfmCode' then
          if popfcode = 45 and pcfmcode not in (61, 71, 72) then
            pRes := false;
            --    elsif popfcode = 45  and pcfmcode in (61,71,72) then

          end if;
        when 'CheckRNN' then
          begin
            select extractvalue(pRootXml,
                                '/Root/OrganisationData/RNN/text()')
              into prnn
              from dual;
          exception
            when others then
              null;
          end;
          if prnn is not null then
            pRes := (CheckRnn(prnn) >= 0);
          end if;
        when 'CheckBIN' then
          null;
          begin
            select extractvalue(pRootXml,
                                '/Root/OrganisationData/IINBIN/text()')
              into pbin
              from dual;
          exception
            when others then
              null;
          end;
          if pbin is not null then
            pRes := (CheckBIN(pbin) >= 0);
          end if;
        when 'CheckBINnotequalRNN' then
          begin
            select extractvalue(pRootXml,
                                '/Root/OrganisationData/IINBIN/text()'),
                   extractvalue(pRootXml,
                                '/Root/OrganisationData/RNN/text()')
              into pbin, prnn
              from dual;
          exception
            when others then
              null;
          end;
          pRes := (pbin <> prnn) or ((prnn IS NULL) OR (pbin IS NULL));
        when 'CheckOrgAdress' then
          result2 := CheckOrgAdress(pRootXml, pMESSType, pMessVersion);
          result  := GEtMessage(result, result2);
        when 'CheckAcSecondName' then
          if popfcode = 45 and pcfmcode in (61, 71, 72) then
            begin
              pRes := (xmltype.existsnode(pRootXml,
                                          '/Root/OrganisationData/AdditionalAcData[@IsAc=''True'']/SecondName/text()') > 0);
            exception
              when others then
                pRes := false;
            end;
          end if;
        when 'CheckAcFirstName' then
          if popfcode = 45 and pcfmcode in (61, 71, 72) then
            begin
              pRes := (xmltype.existsnode(pRootXml,
                                          '/Root/OrganisationData/AdditionalAcData[@IsAc=''True'']/FirstName/text()') > 0);
            exception
              when others then
                pRes := false;
            end;
          end if;
        when 'CheckAcDocType' then
          if popfcode = 45 and pcfmcode in (61, 71, 72) then
            begin
              pRes := (xmltype.existsnode(pRootXml,
                                          '/Root/OrganisationData/AdditionalAcData[@IsAc=''True'']/DocumentIdentity/text()') > 0);
            exception
              when others then
                pRes := false;
            end;
          end if;
        when 'CheckAcDocNumber' then
          if popfcode = 45 and pcfmcode in (61, 71, 72) then
            begin
              pRes := (xmltype.existsnode(pRootXml,
                                          '/Root/OrganisationData/AdditionalAcData[@IsAc=''True'']/NumberDocIdentity/text()') > 0);
            exception
              when others then
                pRes := false;
            end;
          end if;
        when 'CheckAcDocDate' then
          if popfcode = 45 and pcfmcode in (61, 71, 72) then
            begin
              pRes := (xmltype.existsnode(pRootXml,
                                          '/Root/OrganisationData/AdditionalAcData[@IsAc=''True'']/DateIssuance/text()') > 0);

            exception
              when others then
                pRes := false;
            end;
          end if;
        when 'CheckAcDocOrg' then
          if popfcode = 45 and pcfmcode in (61, 71, 72) then
            begin
              pRes := (xmltype.existsnode(pRootXml,
                                          '/Root/OrganisationData/AdditionalAcData[@IsAc=''True'']/DocumentIssued/text()') > 0);
            exception
              when others then
                pRes := false;
            end;
          end if;
        when 'CheckPersonsCount' then
          begin
            select count(*)
              into pCount
              from table(XMLSequence(extract(pRootXml,
                                             '/Root/OrganisationData/Persons/Person'))) t;
            pRes := (pCount >= 1);
          exception
            when others then
              pRes := false;
          end;
        else
          null;
          --   when 'CheckCfmCode' then null;
      --   when 'CheckCfmCode' then null;
      end case;
      if Pres = false then
        result := GEtMessage(result, t.verifydescription);
      end if;
    end loop;

    return result;
  end;

  function VerifyMessageRegistration(pMessage        clob,
                                     pMessageType    number,
                                     pMessageVersion number) return clob is
    result      clob;
    result2     clob;
    vxml        xmltype;
    pCfmType    number default null;
    pTempSlkExp clob;
  begin
    pTempSlkExp := empty_clob();
    vxml        := xmltype.createxml(pMessage);
    begin
      select extractvalue(vxml, '/Root/OrganisationData/CfmCode/text()')
        into pCfmType
        from analyticcodes
       where analytictype = 31005
         and analyticcode =
             to_number(extractvalue(vxml,
                                    '/Root/OrganisationData/CfmCode/text()'));
    exception
      when others then
        result := 'Не указан (указан неверный) код организации. Дальнейшие проверки не производятся.';
    end;

    if pCfmType is not null then
      result  := CheckXpath(vxml,
                            pCfmType,
                            pMessageType,
                            pMessageVersion,
                            pTempSlkExp);
      result2 := CheckXProcs(vxml, pCfmType, pMessageType, pMessageVersion);

      if length(result) <> 0 and length(result2) <> 0 then
        dbms_lob.append(result, chr(13));
        dbms_lob.append(result, result2);
      elsif length(result) = 0 then
        result := result2;
      end if;
    else
      result := 'Не указан (указан неверный) код организации. Дальнейшие проверки не производятся.';
    end if;
    return result;
  end;

  function CheckXProcs_3(pRootXml        in xmltype,
                         pMESSType       in number, -- Version$Type
                         pMessVersion    in number,
                         pmessid         in number default 0,
                         pSLKExplanation in out clob) return clob is
    result   clob;
    result2  clob;
    pcfmcode number;
    popfcode number;
    prnn     varchar2(300);
    pbin     varchar2(300);
    pName    varchar2(4000 char);
    pIsAC    varchar2(50 char);
    pCount   number;
    pRes     boolean default true;
    pcfmid   number;
    tres     number;

  begin
    -- To do: IsAc
    begin
      select to_number(extractvalue(pRootXml,
                                    '/Root/PersonalData/OrganisationCode/text()')),
             to_number(extractvalue(pRootXml,
                                    '/Root/PersonalData/OrganisationOPF/text()'))
      --              ,extractvalue(pRootXml, '/Root/OrganisationData/AdditionalAcData/@IsAc')
        into pcfmcode, popfcode
      --,pIsAC
        from dual;
    exception
      when others then
        pSLKExplanation := AppendSLKExplanation(pSLKExplanation,
                                                GetSLKExplanationRecord('2.1',
                                                                        'Root/PersonalData/OrganisationCode',
                                                                        'Не указан код ОПФ'));
        pSLKExplanation := AppendSLKExplanation(pSLKExplanation,
                                                GetSLKExplanationRecord('2.2(1)',
                                                                        'Root/PersonalData/OrganisationOPF',
                                                                        'Не указан код ОПФ'));
        return 'Не указан код ОПФ';
    end;

    for t in (select mvs.verifystring, mvs.verifydescription, mvs.verifyid

                from mess_verify_setup mvs, mess_verify_setup_cfm mvs_cfm
               where mvs.verifyid = mvs_cfm.verifyid
                 and mvs_cfm.cfmcode = pcfmcode
                 and mvs.messagetype = pMESSType
                 and mvs.messageversion = pMessVersion
                 and mvs.verifytype = 2
                 and mvs.isenable = 1
                 and mvs_cfm.status = 1

              --      and mvs.base_realisaton=1
               order by verifystring) loop
      pRes := true;
      case t.verifystring
      --10002
        when 'CheckCfmCode' then
          if popfcode = 45 and pcfmcode not in (61, 71, 72) then
            pRes := false;
            --    elsif popfcode = 45  and pcfmcode in (61,71,72) then

          end if;
        when 'CheckOrgRNN' then
          begin
            select extractvalue(pRootXml, '/Root/PersonalData/RNN/text()')
              into prnn
              from dual;
          exception
            when others then
              null;
          end;
          if prnn is not null then
            pRes := (CheckRnn(prnn) >= 0);
          end if;
        when 'CheckPersonRNN' then
          begin
            for i in (select value(t).extract('*/text()').getstringval() prnn
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant/RNN'))) t) loop
              IF i.prnn IS NOT NULL THEN
                pRes := (CheckRnn(i.prnn) >= 0);
                if pRes = false then
                  exit;
                end if;
              END IF;
            end loop;
          end;
        when 'CheckOrgBIN' then
          null;
          begin
            select extractvalue(pRootXml,
                                '/Root/PersonalData/IINBIN/text()')
              into pbin
              from dual;
          exception
            when others then
              null;
          end;
          if pbin is not null then
            pRes := (CheckBIN(pbin) >= 0);
          end if;
        when 'CheckPersonBIN' then
          null;
          begin
            for i in (select value(t).extract('*/text()').getstringval() pbin
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant/IndividualIssue'))) t) loop
              IF i.pbin IS NOT NULL THEN
                pRes := (CheckBIN(i.pbin) >= 0);
                if pres = false then
                  exit;
                end if;
              END IF;
            end loop;
          end;
          --10001
        when 'CheckBINnotequalRNN' then
          begin
            select extractvalue(pRootXml,
                                '/Root/PersonalData/IINBIN/text()'),
                   extractvalue(pRootXml, '/Root/PersonalData/RNN/text()')
              into pbin, prnn
              from dual;
          exception
            when others then
              null;
          end;
          pRes := (pbin <> prnn) OR ((prnn IS NULL) AND (pbin IS NULL));
        when 'CheckPersonBINnotequalRNN' then
          begin
            for i in (select value(t).extract('IndividualIssue/text()')
                             .getstringval() pbin,
                             value(t).extract('RNN/text()').getstringval() prnn
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant'))) t) loop
              pRes := (i.pbin <> i.prnn) OR
                      ((i.prnn IS NULL) AND (i.pbin IS NULL));
              if pres = false then
                exit;
              end if;
            end loop;
          END;
          --10007
        when 'CheckOrgAdress' then
          result2 := CheckOrgAdress(pRootXml, pMESSType, pMessVersion);
          result  := GEtMessage(result, result2);

        when 'CheckPersonUrAdress' then
          declare
            tPersonsCount number default 0;
            tPersonType   number default 0;
            ex_not_correct exception;
            pragma exception_init(ex_not_correct, -20004);
            pAreaCode     number;
            pDistrictCode number;
            pCityCode     number;
            pStreetCode   number;
            pHouseCode    number;
            pOfficeCode   number;
            pArea         varchar2(4000 char);
            pDistrict     varchar2(4000 char);
            pCity         varchar2(4000 char);
            pStreet       varchar2(4000 char);
            pHouse        varchar2(4000 char);
            pOffice       varchar2(4000 char);
            RootPath      varchar2(4000 char);

          begin

            select count(*)
              into tPersonsCount
              from table(xmlsequence(extract(pRootXml,
                                             'Root/Participants/Participant')));
            for i in 1 .. tPersonsCount loop
              select extractvalue(pRootXml,
                                  'Root/Participants/Participant[' || i ||
                                  ']/ParticipantsType/text()')
                into tPersonType
                from dual;
              case tPersonType
                when 1 then
                  RootPath := 'Root/Participants/Participant[' || i ||
                              ']/AdditionalPersonInfo/AdditionalInformationUr/URAddress/';
                when 2 then
                  RootPath := 'Root/Participants/Participant[' || i ||
                              ']/AdditionalPersonInfo/AdditionalInformationAc/URAddress/';
                when 3 then
                  RootPath := 'Root/Participants/Participant[' || i ||
                              ']/AdditionalPersonInfo/AdditionalInformationIp/URAddress/';
                else
                  RootPath := null;
              end case;
              if RootPath is not null then
                if xmltype.existsnode(pRootXml,
                                      RootPath || 'Country[text()=398]') = 1 then
                  select extractvalue(pRootXml, RootPath || 'Area/@Code'),
                         extractvalue(pRootXml, RootPath || 'Area/text()'),
                         extractvalue(pRootXml,
                                      RootPath || 'District/@Code'),
                         extractvalue(pRootXml,
                                      RootPath || 'District/text()'),
                         extractvalue(pRootXml, RootPath || 'Town/@Code'),
                         extractvalue(pRootXml, RootPath || 'Town/text()'),
                         extractvalue(pRootXml, RootPath || 'Street/@Code'),
                         extractvalue(pRootXml, RootPath || 'Street/text()'),
                         extractvalue(pRootXml,
                                      RootPath || 'HomeNumber/@Code'),
                         extractvalue(pRootXml,
                                      RootPath || 'HomeNumber/text()'),
                         extractvalue(pRootXml,
                                      RootPath || 'OfficeNumber/@Code'),
                         extractvalue(pRootXml,
                                      RootPath || 'OfficeNumber/text()')
                    into pAreaCode,
                         pArea,
                         pDistrictCode,
                         pDistrict,
                         pCityCode,
                         pCity,
                         pStreetCode,
                         pStreet,
                         pHouseCode,
                         pHouse,
                         pOfficeCode,
                         pOffice
                    from dual;
                  begin
                    pres := (0 = 0); --(PKG_ISARHolding.IsCorrectAdress(pAreaCode,pDistrictCode,pCityCode,pStreetCode,pHouseCode,pOfficeCode,
                    --pArea,pDistrict,pCity,pStreet,pHouse,pOffice)=0);
                  exception
                    when ex_not_correct then
                      result2 := 'Проверка указания юридического адреса участника в соответствии со справочником ИС АР:' ||
                                 substr(substr(sqlerrm,
                                               instr(sqlerrm, ':') + 1,
                                               99999),
                                        2,
                                        99999);
                      result  := GEtMessage(result, result2);
                  end;
                end if;
              end if;

            end loop;
          end;

        when 'CheckPersonAcAdress' then
          declare
            tPersonsCount number default 0;
            tPersonType   number default 0;
            ex_not_correct exception;
            pragma exception_init(ex_not_correct, -20004);
            pAreaCode     number;
            pDistrictCode number;
            pCityCode     number;
            pStreetCode   number;
            pHouseCode    number;
            pOfficeCode   number;
            pArea         varchar2(4000 char);
            pDistrict     varchar2(4000 char);
            pCity         varchar2(4000 char);
            pStreet       varchar2(4000 char);
            pHouse        varchar2(4000 char);
            pOffice       varchar2(4000 char);
            RootPath      varchar2(4000 char);
          begin
            select count(*)
              into tPersonsCount
              from table(xmlsequence(extract(pRootXml,
                                             'Root/Participants/Participant')));
            for i in 1 .. tPersonsCount loop
              select extractvalue(pRootXml,
                                  'Root/Participants/Participant[' || i ||
                                  ']/ParticipantsType/text()')
                into tPersonType
                from dual;
              case tPersonType
                when 1 then
                  RootPath := 'Root/Participants/Participant[' || i ||
                              ']/AdditionalPersonInfo/AdditionalInformationUr/ACAddress/';
                when 2 then
                  RootPath := 'Root/Participants/Participant[' || i ||
                              ']/AdditionalPersonInfo/AdditionalInformationAc/ACAddress/';
                when 3 then
                  RootPath := 'Root/Participants/Participant[' || i ||
                              ']/AdditionalPersonInfo/AdditionalInformationIp/ACAddress/';
                else
                  RootPath := null;
              end case;
              if RootPath is not null then
                if xmltype.existsnode(pRootXml,
                                      RootPath || 'Country[text()=398]') = 1 then
                  select extractvalue(pRootXml, RootPath || 'Area/@Code'),
                         extractvalue(pRootXml, RootPath || 'Area/text()'),
                         extractvalue(pRootXml,
                                      RootPath || 'District/@Code'),
                         extractvalue(pRootXml,
                                      RootPath || 'District/text()'),
                         extractvalue(pRootXml, RootPath || 'Town/@Code'),
                         extractvalue(pRootXml, RootPath || 'Town/text()'),
                         extractvalue(pRootXml, RootPath || 'Street/@Code'),
                         extractvalue(pRootXml, RootPath || 'Street/text()'),
                         extractvalue(pRootXml,
                                      RootPath || 'HomeNumber/@Code'),
                         extractvalue(pRootXml,
                                      RootPath || 'HomeNumber/text()'),
                         extractvalue(pRootXml,
                                      RootPath || 'OfficeNumber/@Code'),
                         extractvalue(pRootXml,
                                      RootPath || 'OfficeNumber/text()')
                    into pAreaCode,
                         pArea,
                         pDistrictCode,
                         pDistrict,
                         pCityCode,
                         pCity,
                         pStreetCode,
                         pStreet,
                         pHouseCode,
                         pHouse,
                         pOfficeCode,
                         pOffice
                    from dual;
                  begin
                    pres := (0 = 0); --(PKG_ISARHolding.IsCorrectAdress(pAreaCode,pDistrictCode,pCityCode,pStreetCode,pHouseCode,pOfficeCode,
                    --pArea,pDistrict,pCity,pStreet,pHouse,pOffice)=0);
                  exception
                    when ex_not_correct then
                      result2 := 'Проверка указания фактического адреса участника в соответствии со справочником ИС АР:' ||
                                 substr(substr(sqlerrm,
                                               instr(sqlerrm, ':') + 1,
                                               99999),
                                        2,
                                        99999);
                      result  := GEtMessage(result, result2);
                  end;
                end if;
              end if;

            end loop;

          end;
          /*
          when 'CheckAcSecondName' then
           if  popfcode = 45  and pcfmcode in (61,71,72) then
            begin
             pRes:=(xmltype.existsnode(pRootXml,'/Root/OrganisationData/AdditionalAcData[@IsAc=''True'']/SecondName/text()')>0);
             exception
               when others then
                  pRes:=false;
             end;
            end if;
          when 'CheckAcFirstName' then
           if  popfcode = 45  and pcfmcode in (61,71,72) then
            begin
             pRes:=(xmltype.existsnode(pRootXml,'/Root/OrganisationData/AdditionalAcData[@IsAc=''True'']/FirstName/text()')>0);
             exception
               when others then
                   pRes:=false;
             end;
           end if;  */
        when 'CheckOrgAcDocType' then
          if popfcode = 45 and pcfmcode in (61, 71, 72) then
            begin
              pRes := (xmltype.existsnode(pRootXml,
                                          '/Root/PersonalData/AdditionalAcData[@IsAc=''True'']/DocumentIdentity/text()') > 0);
            exception
              when others then
                pRes := false;
            end;
          end if;
        when 'CheckOrgAcDocNumber' then
          if popfcode = 45 and pcfmcode in (61, 71, 72) then
            begin
              pRes := (xmltype.existsnode(pRootXml,
                                          '/Root/PersonalData/AdditionalAcData[@IsAc=''True'']/NumberDocIdentity/text()') > 0);
            exception
              when others then
                pRes := false;
            end;
          end if;
          --10003
        when 'CheckOrgAcDocDate' then
          if popfcode = 45 and pcfmcode in (61, 71, 72) then
            begin
              pRes := (xmltype.existsnode(pRootXml,
                                          '/Root/PersonalData/AdditionalAcData[@IsAc=''True'']/DateIssuance/text()') > 0);

            exception
              when others then
                pRes := false;
            end;
          end if;
        when 'CheckOrgAcDocOrg' then
          if popfcode = 45 and pcfmcode in (61, 71, 72) then
            begin
              pRes := (xmltype.existsnode(pRootXml,
                                          '/Root/PersonalData/AdditionalAcData[@IsAc=''True'']/DocumentIssued/text()') > 0);
            exception
              when others then
                pRes := false;
            end;
          end if;
        when 'ValidateParticipantCount' then
          begin
            select count(*)
              into pCount
              from table(XMLSequence(extract(pRootXml,
                                             '/Root/Participants/Participant'))) t;
            select extractvalue(pRootXml,
                                '/Root/MessageInformation/ParticipantCount/text()')
              into tres
              from dual;
            pRes := (pCount = tres);
          exception
            when others then
              pRes := false;
          end;
        when 'ValidateTransactionDate' then
          begin
            select extractvalue(pRootXml,
                                '/Root/MessageInformation/OperationStatusId/text()')
              into tres
              from dual;
            if tres in (1, 2, 3) then
              declare
                tdate date;
              begin
                pRes := (xmltype.existsnode(pRootXml,
                                            '/Root/MessageInformation/TransactionDate/text()') > 0);
                if Pres then
                  begin
                    select to_date(extractvalue(pRootXml,
                                                '/Root/MessageInformation/TransactionDate/text()'),
                                   'dd.mm.yyyy hh24:mi:ss')
                      into tdate
                      from dual;
                  exception
                    when others then
                      pRes := false;
                  end;
                end if;
              end;
            end if;
          exception
            when others then
              null;
          end;
        when 'ValidateReasonFilingId' then
          begin
            select extractvalue(pRootXml,
                                '/Root/MessageInformation/ReasonFilingId/text()')
              into tres
              from dual;
            if tres = 2 then
              pRes := (xmltype.existsnode(pRootXml,
                                          '/Root/MessageInformation/SuspicionsCriterians/text()') > 0);
            end if;
          exception
            when others then
              null;
              -- pRes:=false;
          end;
        when 'ValidateEknpId' then
          begin
            if not
                (xmltype.existsnode(pRootXml,
                                    '/Root/MessageInformation/EknpId[@IsEknpNotSetup=''False'']/text()') > 0) then
              pRes := ((xmltype.existsnode(pRootXml,
                                           '/Root/MessageInformation/EknpId[@IsEknpNotSetup=''True'']') > 0) and
                      (xmltype.existsnode(pRootXml,
                                           '/Root/MessageInformation/EknpId/text()') = 0));
            end if;
          exception
            when others then
              pRes := false;
          end;
        when 'CheckAmountCurrencyTenge' then
          declare
            pcurr      number;
            pFirstSum  number;
            pSecondSum number;
          begin
            select pkg_common.ConvertToNumber(extractvalue(pRootXml,
                                                           '/Root/MessageInformation/AmountCurrency/text()')),
                   pkg_common.ConvertToNumber(extractvalue(pRootXml,
                                                           '/Root/MessageInformation/AmountCurrencyTenge/text()')),
                   extractvalue(pRootXml,
                                '/Root/MessageInformation/CurrencyCodeId/text()')
              into pFirstSum, pSecondSum, pcurr
              from dual;

            pRES := ((pFirstSum = pSecondSum and pcurr = 398) or
                    pcurr <> 398);
          exception
            when others then
              pRes := false;
          end;
        when 'ValidateSuspicionFirst' then
          begin
            select extractvalue(pRootXml,
                                '/Root/MessageInformation/ReasonFilingId/text()')
              into tres
              from dual;
            if tres = 2 then
              pRES := (xmltype.existsnode(pRootXml,
                                          '/Root/MessageInformation/SuspicionFirst/text()') > 0);
            end if;
          exception
            when others then
              null;
          end;
        when 'CheckSuspicionsFilling' then
          declare
            tres2 number;
            tres3 number;
          begin
            select extractvalue(pRootXml,
                                '/Root/MessageInformation/SuspicionFirst/text()'),
                   extractvalue(pRootXml,
                                '/Root/MessageInformation/SuspicionSecond/text()'),
                   extractvalue(pRootXml,
                                '/Root/MessageInformation/SuspicionThird/text()')
              into tres, tres2, tres3
              from dual;
            pRES := not
                     ((tres3 is not null and (tres2 is null or tres is null)) or
                     (tres2 is not null and tres is null) or (tres = tres2) or
                     (tres = tres3) or (tres2 = tres3));

          exception
            when others then
              null;
          end;
        when 'ValidateMoreInformation' then

          begin
            select extractvalue(pRootXml,
                                '/Root/MessageInformation/DocumentType/text()')
              into tres
              from dual;
            if tres = 3 then
              pRES := (xmltype.existsnode(pRootXml,
                                          '/Root/MessageInformation/MoreInformation/text()') > 0);
            end if;
          exception
            when others then
              null;
          end;
        when 'ValidateIsMemberCommunity' then
          begin
            for i in (select value(t).extract('/Participant/MemberCommunityCityCode/text()')
                             .getstringval() prnn
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant'))) t) loop
              IF i.prnn IS NULL THEN
                pRes := false;
                exit;
              END IF;
            end loop;
          end;
        when 'CheckBankAccountNumber' then
          begin
            select extractvalue(pRootXml,
                                '/Root/MessageInformation/ViewOperationId/text()')
              into tres
              from dual;
            if tres in (9, 20) then
              for i in (select value(t).extract('/Participant/CorrespondentBank/AccountNumber/text()')
                               .getstringval() pName
                          from table(XMLSequence(extract(pRootXml,
                                                         '/Root/Participants/Participant'))) t) loop
                IF i.pName IS NULL THEN
                  pRes := false;
                  exit;
                END IF;
              end loop;
            end if;
          end;
        when 'CheckBankName' then
          begin
            select extractvalue(pRootXml,
                                '/Root/MessageInformation/ViewOperationId/text()')
              into tres
              from dual;
            if tres in (9, 20) then
              for i in (select value(t).extract('/Participant/CorrespondentBank/Name/text()')
                               .getstringval() pName
                          from table(XMLSequence(extract(pRootXml,
                                                         '/Root/Participants/Participant'))) t) loop
                IF i.pName IS NULL THEN
                  pRes := false;
                  exit;
                END IF;
              end loop;
            end if;
          end;
        when 'CheckBankAddress' then
          begin
            select extractvalue(pRootXml,
                                '/Root/MessageInformation/ViewOperationId/text()')
              into tres
              from dual;
            if tres = 6 then
              for i in (select value(t).extract('/Participant/CorrespondentBank/BankAddress/text()')
                               .getstringval() pName
                          from table(XMLSequence(extract(pRootXml,
                                                         '/Root/Participants/Participant'))) t) loop
                IF i.pName IS NULL THEN
                  pRes := false;
                  exit;
                END IF;
              end loop;
            end if;
          end;
        when 'CheckBankCorrespondentInformation' then
          begin
            select extractvalue(pRootXml,
                                '/Root/MessageInformation/CurrencyCodeId/text()')
              into tres
              from dual;
            if tres <> 398 then
              for i in (select value(t).extract('/Participant/CorrespondentBank/CorrespondentsInformations/CorrespondentInformation[1]/AccountNumber/text()')
                               .getstringval() AccountNumber,
                               value(t).extract('/Participant/CorrespondentBank/CorrespondentsInformations/CorrespondentInformation[1]/BankCode/text()')
                               .getstringval() BankCode
                          from table(XMLSequence(extract(pRootXml,
                                                         '/Root/Participants/Participant'))) t) loop
                IF (i.BankCode IS NULL or i.AccountNumber is null) THEN
                  pRes := false;
                  exit;
                END IF;
              end loop;
            end if;
          end;
        when 'ValidateFullNameUrOPF' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationUr/ParticipantOPF/text()')
                             .getstringval() pname
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''1'']'))) t) loop
              IF (i.pname IS NULL) THEN
                pRes := false;
                exit;
              END IF;
            end loop;
          end;

        when 'ValidateFullNameUr' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationUr/FullName/text()')
                             .getstringval() pname,
                             value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationUr/FullName/@IsFullNameSetup')
                             .getstringval() NotSetup
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''1'']'))) t) loop
              IF (i.pname IS NULL and
                 (i.NotSetup is null or i.NotSetup <> 'True')) THEN
                pRes := false;
                exit;
              END IF;
            end loop;
          end;
        when 'ValidateFounders' then
          begin
            select extractvalue(pRootXml,
                                '/Root/MessageInformation/ReasonFilingId/text()')
              into tres
              from dual;
            if tres = 2 then
              for i in (select value(t).extract('/Participant/Founders/Founder[1]/FounderType/text()')
                               .getstringval() FounderType,
                               value(t).extract('/Participant/Founders/Founder[1]/Name/text()')
                               .getstringval() Urname,
                               value(t).extract('/Participant/Founders/Founder[1]/SecondName/text()')
                               .getstringval() SecondName
                          from table(XMLSequence(extract(pRootXml,
                                                         '/Root/Participants/Participant[ParticipantsType=''1'' and IsClientSubject=''2'']'))) t) loop
                IF (i.FounderType IS NULL or
                   (i.FounderType = 1 and i.Urname is null) or
                   (i.FounderType in (2, 3) and i.SecondName is null)) THEN
                  pRes := false;
                  exit;
                END IF;
              end loop;
            end if;
          end;
        when 'ValidateFirstHeadUrFirstName' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationUr/FirstHead/FirstName/text()')
                             .getstringval() pname
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''1'' and IsClientSubject=''2'']'))) t) loop
              IF (i.pname IS NULL) THEN
                pRes := false;
                exit;
              END IF;
            end loop;
          end;
        when 'ValidateFirstHeadUrSecondName' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationUr/FirstHead/SecondName/text()')
                             .getstringval() pname
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''1'' and IsClientSubject=''2'']'))) t) loop
              IF (i.pname IS NULL) THEN
                pRes := false;
                exit;
              END IF;
            end loop;
          end;
        when 'ValidateFirstHeadUrMiddleName' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationUr/FirstHead/MiddleName/text()')
                             .getstringval() pname
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''1'' and IsClientSubject=''2'']'))) t) loop
              IF (i.pname IS NULL) THEN
                pRes := false;
                exit;
              END IF;
            end loop;
          end;
        when 'ValidateRnn' then
          begin
            for i in (select value(t).extract('/Participant/RNN/text()')
                             .getstringval() pname
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[IsClientSubject=''2'']'))) t) loop
              IF (i.pname IS NULL) THEN
                pRes := false;
                exit;
              END IF;
            end loop;
          end;
        when 'ValidateOked' then
          begin
            for i in (select value(t).extract('/Participant/OKED/text()')
                             .getstringval() pname
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''1'' and  IsClientSubject=''2'']'))) t) loop
              IF (i.pname IS NULL) THEN
                pRes := false;
                exit;
              END IF;
            end loop;
          end;
        when 'CheckPersonBINWithBirth' then
          begin
            for i in (select value(t).extract('/Participant/IndividualIssue/text()')
                             .getstringval() pbin,
                             value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationAc/DateBirdth/text()')
                             .getstringval() pdate
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''2'' and  IsClientSubject=''2'']'))) t) loop
              IF (i.pbin IS not NULL) and (i.pdate is not null) THEN
                pRes := CheckBINWithBirth(i.pbin, i.pdate);
                exit;
              END IF;
            end loop;
            for i in (select value(t).extract('/Participant/IndividualIssue/text()')
                             .getstringval() pbin,
                             value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationIp/DateBirdth/text()')
                             .getstringval() pdate
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''3'' and  IsClientSubject=''2'']'))) t) loop
              IF (i.pbin IS not NULL) and (i.pdate is not null) THEN
                pRes := CheckBINWithBirth(i.pbin, i.pdate);
                exit;
              END IF;
            end loop;
          end;
        when 'ValidateFullNameAcFirstName' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationAc/FIO/FirstName/text()')
                             .getstringval() pname,
                             value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationAc/FIO/@IsFioNotSetup')
                             .getstringval() NotSetup,
                             value(t).extract('/Participant/IsClientSubject/text()')
                             .getstringval() is_subject
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''2'']'))) t) loop
              if i.is_subject = '2' then
                IF (i.pname IS NULL) THEN
                  pRes := false;
                  exit;
                END IF;
              else
                IF (i.pname IS NULL and i.NotSetup <> 'True') or
                   (i.pname IS NOT NULL and i.NotSetup <> 'False') THEN
                  pRes := false;
                  exit;
                END IF;
              end if;
            end loop;
          end;
        when 'ValidateFullNameAcSecondName' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationAc/FIO/SecondName/text()')
                             .getstringval() pname,
                             value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationAc/FIO/@IsFioNotSetup')
                             .getstringval() NotSetup,
                             value(t).extract('/Participant/IsClientSubject/text()')
                             .getstringval() is_subject
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''2'']'))) t) loop
              if i.is_subject = '2' then
                IF (i.pname IS NULL) THEN
                  pRes := false;
                  exit;
                END IF;
              else
                IF (i.pname IS NULL and i.NotSetup <> 'True') or
                   (i.pname IS NOT NULL and i.NotSetup <> 'False') THEN
                  pRes := false;
                  exit;
                END IF;
              end if;
            end loop;

          end;
        when 'ValidateFullNameAcMiddleName' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationAc/FIO/MiddleName/text()')
                             .getstringval() pname,
                             value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationAc/FIO/@IsFioNotSetup')
                             .getstringval() NotSetup,
                             value(t).extract('/Participant/IsClientSubject/text()')
                             .getstringval() is_subject
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''2'']'))) t) loop
              if i.is_subject = '2' then
                IF (i.pname IS NULL) THEN
                  pRes := false;
                  exit;
                END IF;
              else
                IF (i.pname IS NULL and i.NotSetup <> 'True') or
                   (i.pname IS NOT NULL and i.NotSetup <> 'False') THEN
                  pRes := false;
                  exit;
                END IF;
              end if;
            end loop;

          end;
        when 'ValidateFullNameIpSecondName' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationIp/FIO/SecondName/text()')
                             .getstringval() pname,
                             value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationIp/FIO/@IsFioNotSetup')
                             .getstringval() NotSetup,
                             value(t).extract('/Participant/IsClientSubject/text()')
                             .getstringval() is_subject
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''3'']'))) t) loop
              if i.is_subject = '2' then
                IF (i.pname IS NULL) THEN
                  pRes := false;
                  exit;
                END IF;
              else
                IF (i.pname IS NULL and i.NotSetup <> 'True') or
                   (i.pname IS NOT NULL and i.NotSetup <> 'False') THEN
                  pRes := false;
                  exit;
                END IF;
              end if;
            end loop;

          end;
        when 'ValidateFullNameIpFirstName' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationIp/FIO/FirstName/text()')
                             .getstringval() pname,
                             value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationIp/FIO/@IsFioNotSetup')
                             .getstringval() NotSetup,
                             value(t).extract('/Participant/IsClientSubject/text()')
                             .getstringval() is_subject
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''3'']'))) t) loop
              if i.is_subject = '2' then
                IF (i.pname IS NULL) THEN
                  pRes := false;
                  exit;
                END IF;
              else
                IF (i.pname IS NULL and i.NotSetup <> 'True') or
                   (i.pname IS NOT NULL and i.NotSetup <> 'False') THEN
                  pRes := false;
                  exit;
                END IF;
              end if;
            end loop;

          end;
        when 'ValidateFullNameIpMiddleName' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationIp/FIO/MiddleName/text()')
                             .getstringval() pname,
                             value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationIp/FIO/@IsFioNotSetup')
                             .getstringval() NotSetup,
                             value(t).extract('/Participant/IsClientSubject/text()')
                             .getstringval() is_subject
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''3'']'))) t) loop
              if i.is_subject = '2' then
                IF (i.pname IS NULL) THEN
                  pRes := false;
                  exit;
                END IF;
              else
                IF (i.pname IS NULL and i.NotSetup <> 'True') or
                   (i.pname IS NOT NULL and i.NotSetup <> 'False') THEN
                  pRes := false;
                  exit;
                END IF;
              end if;
            end loop;

          end;
        when 'ValidatePartipiantsDocumentIdentityAc' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationAc/DocumentIdentity/text()')
                             .getstringval() pname
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''2'' and  IsClientSubject=''2'']'))) t) loop
              IF (i.pname IS NULL) THEN
                pRes := false;
                exit;
              END IF;
            end loop;
          end;
        when 'ValidatePartipiantsDocumentIdentityIp' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationIp/DocumentIdentity/text()')
                             .getstringval() pname
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''3'' and  IsClientSubject=''2'']'))) t) loop
              IF (i.pname IS NULL) THEN
                pRes := false;
                exit;
              END IF;
            end loop;
          end;
          --Указание значения - "4.16 (1). Номер документа, удостоверяющего личность участника" (для физ. лиц с указанным значением "4.15. Документ, удостоверяющий личность" )
        when 'ValidatePartipiantsNumberDocIdentityAc' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationAc/DocumentIdentity/text()')
                             .getstringval() pdoc,
                             value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationAc/NumberDocIdentity/text()')
                             .getstringval() pname
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''2'']'))) t) loop
              IF (i.pdoc IS not NULL) and (i.pname is null) THEN
                pRes := false;
                exit;
              END IF;
            end loop;
          end;
          --Указание значения - "4.16 (1). Номер документа, удостоверяющего личность участника" (для индивид. передпр. с указанным значением "4.15. Документ, удостоверяющий личность" )
        when 'ValidatePartipiantsNumberDocIdentityIp' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationIp/DocumentIdentity/text()')
                             .getstringval() pdoc,
                             value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationIp/NumberDocIdentity/text()')
                             .getstringval() pname
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''3'']'))) t) loop
              IF (i.pdoc IS not NULL) and (i.pname is null) THEN
                pRes := false;
                exit;
              END IF;
            end loop;
          end;
          --Указание значения - "4.16 (2). Серия документа, удостоверяющего личность участника" (для физ. лиц. с указанным значением "4.15. Документ, удостоверяющий личность" )
        when 'ValidatePartipiantsSeriesDocIdentityAc' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationAc/DocumentIdentity/text()')
                             .getstringval() pdoc,
                             value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationAc/SeriesDocIdentity/text()')
                             .getstringval() pname
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''2'']'))) t) loop
              IF (i.pdoc IS not NULL) and (i.pname is null) THEN
                pRes := false;
                exit;
              END IF;
            end loop;
          end;
          --Указание значения - "4.16 (2). Серия документа, удостоверяющего личность участника" (для  индивид. передпр. с указанным значением "4.15. Документ, удостоверяющий личность" )
        when 'ValidatePartipiantsSeriesDocIdentityIp' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationIp/DocumentIdentity/text()')
                             .getstringval() pdoc,
                             value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationIp/SeriesDocIdentity/text()')
                             .getstringval() pname
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''3'']'))) t) loop
              IF (i.pdoc IS not NULL) and (i.pname is null) THEN
                pRes := false;
                exit;
              END IF;
            end loop;
          end;
          --Указание значения - "4.17. Кем выдан документ, удостоверяющий личность участника" (для физ. лиц с указанным значением "4.15. Документ, удостоверяющий личность" )
        when 'ValidatePartipiantsDocumentIssuedAc' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationAc/DocumentIdentity/text()')
                             .getstringval() pdoc,
                             value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationAc/DocumentIssued/text()')
                             .getstringval() pname
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''2'']'))) t) loop
              IF (i.pdoc IS not NULL) and (i.pname is null) THEN
                pRes := false;
                exit;
              END IF;
            end loop;
          end;
          --Указание значения - "4.17. Кем выдан документ, удостоверяющий личность участника" (для индивид. передпр. с указанным значением "4.15. Документ, удостоверяющий личность" )
        when 'ValidatePartipiantsDocumentIssuedIp' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationIp/DocumentIdentity/text()')
                             .getstringval() pdoc,
                             value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationIp/DocumentIssued/text()')
                             .getstringval() pname
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''3'']'))) t) loop
              IF (i.pdoc IS not NULL) and (i.pname is null) THEN
                pRes := false;
                exit;
              END IF;
            end loop;
          end;
          --Указание значения - "4.18.(для физ. лиц)
        when 'ValidatePartipiantsDateIssuanceAc' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationAc/DocumentIdentity/text()')
                             .getstringval() pdoc,
                             value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationAc/DateIssuance/text()')
                             .getstringval() pname
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''2'']'))) t) loop
              IF (i.pdoc IS not NULL) and (i.pname is null) THEN
                pRes := false;
                exit;
              END IF;
            end loop;
          end;
          --Указание значения - "4.18.   (для индивид. передпр.)
        when 'ValidatePartipiantsDateIssuanceIp' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationIp/DocumentIdentity/text()')
                             .getstringval() pdoc,
                             value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationIp/DateIssuance/text()')
                             .getstringval() pname
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''3'']'))) t) loop
              IF (i.pdoc IS not NULL) and (i.pname is null) THEN
                pRes := false;
                exit;
              END IF;
            end loop;
          end;
          --Указание значения - "4.19.(для физ. лиц)
        when 'ValidateAcDateBirdth' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationAc/DocumentIdentity/text()')
                             .getstringval() pdoc,
                             value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationAc/DateBirdth/text()')
                             .getstringval() pname
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''2'']'))) t) loop
              IF (i.pdoc IS not NULL) and (i.pname is null) THEN
                pRes := false;
                exit;
              END IF;
            end loop;
          end;
          --Указание значения - "4.19.(для ИП)
        when 'ValidateIpDateBirdth' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationIp/DocumentIdentity/text()')
                             .getstringval() pdoc,
                             value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationIp/DateBirdth/text()')
                             .getstringval() pname
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''3'']'))) t) loop
              IF (i.pdoc IS not NULL) and (i.pname is null) THEN
                pRes := false;
                exit;
              END IF;
            end loop;
          end;
          --Указание значения - "4.20.(для физ. лиц)
        when 'ValidateAcPlaceBirdth' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationAc/DocumentIdentity/text()')
                             .getstringval() pdoc,
                             value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationAc/PlaceBirdth/text()')
                             .getstringval() pname
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''2'']'))) t) loop
              IF (i.pdoc IS not NULL) and (i.pname is null) THEN
                pRes := false;
                exit;
              END IF;
            end loop;
          end;
          --Указание значения - "4.20.(для ИП)
        when 'ValidateIpPlaceBirdth' then
          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationIp/DocumentIdentity/text()')
                             .getstringval() pdoc,
                             value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationIp/PlaceBirdth/text()')
                             .getstringval() pname
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''3'']'))) t) loop
              IF (i.pdoc IS not NULL) and (i.pname is null) THEN
                pRes := false;
                exit;
              END IF;
            end loop;
          end;
        when 'CheckAcDateBirdth18' then
          declare
            pdate date;

          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationAc/DateBirdth/text()')
                             .getstringval() pname
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''2'']'))) t) loop
              IF (i.pname IS not NULL) THEN
                begin
                  pdate := to_date(i.pname, 'dd.mm.yyyy');
                  pRes  := (trunc(months_between(sysdate, pdate) / 12) >= 18);
                exception
                  when others then
                    pRes := false;
                end;
                if pRes = false then
                  exit;
                end if;
              END IF;
            end loop;
          end;
        when 'CheckIpDateBirdth18' then
          declare
            pdate date;

          begin
            for i in (select value(t).extract('/Participant/AdditionalPersonInfo/AdditionalInformationIp/DateBirdth/text()')
                             .getstringval() pname
                        from table(XMLSequence(extract(pRootXml,
                                                       '/Root/Participants/Participant[ParticipantsType=''3'']'))) t) loop
              IF (i.pname IS not NULL) THEN
                begin
                  pdate := to_date(i.pname, 'dd.mm.yyyy');
                  pRes  := (trunc(months_between(sysdate, pdate) / 12) >= 18);
                exception
                  when others then
                    pRes := false;
                end;
                if pRes = false then
                  exit;
                end if;
                exit;
              END IF;
            end loop;
          end;
        else
          null;

      end case;
      if Pres = false then
        for i in (select dic.field_num_in_form, dic.field_xpath
                    from mess_verify_setup_fielddic s
                    join messages_field_dic dic
                      on (dic.field_id = s.field_id)
                   where s.verify_id = t.verifyid) loop
          pSLKExplanation := AppendSLKExplanation(pSLKExplanation,
                                                  GetSLKExplanationRecord(i.field_num_in_form,
                                                                          'Root/' ||
                                                                          i.field_xpath,
                                                                          t.verifydescription));
        end loop;
        /*      --    for
        pSLKExplanation:=AppendSLKExplanation(pSLKExplanation,GetSLKExplanationRecord(\*t.*\,
                                           \*t.*\),
                                           t.verifydescription));
        pSLKExplanation:=AppendSLKExplanation(pSLKExplanation,GetSLKExplanationRecord(\*t.*\,
                                           \*t.*\),
                                           t.verifydescription)); */
        result := GEtMessage(result, t.verifydescription);
      end if;
    end loop;
    return result;
  end;

  function VerifyMessage_Announce(pMessage        clob,
                                  pMessageType    number,
                                  pMessageVersion number,
                                  pmessid         in number default 0,
                                  pSLKExplanation out clob) return clob is
    result   clob;
    result2  clob;
    vxml     xmltype;
    pCfmType number default null;
  begin
    vxml := xmltype.createxml(pMessage);
    begin
      select extractvalue(vxml,
                          '/Root/PersonalData/OrganisationCode/text()')
        into pCfmType
        from analyticcodes
       where analytictype = 31005
         and analyticcode =
             to_number(extractvalue(vxml,
                                    '/Root/PersonalData/OrganisationCode/text()'));
    exception
      when others then
        pSLKExplanation := AppendSLKExplanation(pSLKExplanation,
                                                GetSLKExplanationRecord('2.1',
                                                                        '/Root/PersonalData/OrganisationCode/',
                                                                        'Не указан (указан неверный) код организации. Дальнейшие проверки не производятся'));
        result          := 'Не указан (указан неверный) код организации. Дальнейшие проверки не производятся.';
        return result;
    end;

    if pCfmType is not null then
      result  := CheckXpath(vxml,
                            pCfmType,
                            pMessageType,
                            pMessageVersion,
                            pSLKExplanation);
      result2 := CheckXProcs_3(vxml,
                               pMessageType,
                               pMessageVersion,
                               pmessid,
                               pSLKExplanation);

      if length(result) <> 0 and length(result2) <> 0 then
        dbms_lob.append(result, chr(13));
        dbms_lob.append(result, result2);
      elsif length(result) = 0 then
        result := result2;
      end if;
    else
      pSLKExplanation := AppendSLKExplanation(pSLKExplanation,
                                              GetSLKExplanationRecord('2.1',
                                                                      '/Root/PersonalData/OrganisationCode/',
                                                                      'Не указан (указан неверный) код организации. Дальнейшие проверки не производятся'));

      result := 'Не указан (указан неверный) код организации. Дальнейшие проверки не производятся.';
    end if;
    return result;
  end;

  -------------------------------------------------

  function GetMandatoryCheckList(pMessageVersion varchar2,
                                 pCFMCode        number,
                                 pSubSystem      varchar2,
                                 pResCursor      out sys_refcursor)
    return number is
    Result         number default 0;
    vMessageVersn  number;
    vMessageVersn1 varchar2(50 CHAR);
    pMessageType   number;
  begin
    result := pkg_common.GetFormattedGuid(pMessageVersion, vMessageVersn1);
    if result <> 0 then
      return result;
    end if;

    select mv.mess_type, mv.versionid
      into pMessageType, vMessageVersn
      from messages_versions mv
     where mv.versioncode = vMessageVersn1;

    open pResCursor for
      select con.verifyid, con.control_path, con.condition_value
        from mess_verify_setup_controls con,
             mess_verify_setup_cfm      cfm,
             mess_verify_setup          mvs
       where con.system_id = pSubSystem
         and con.verifyid = cfm.verifyid
         and cfm.cfmcode = pCFMCode
         and mvs.verifyid = con.verifyid
         and mvs.messagetype = pMessageType
         and mvs.messageversion = vMessageVersn
         and con.control_path is not null;

    return Result;

  exception
    when others then
      Result := -1; --Общая ошибка
      return Result;
  end GetMandatoryCheckList;

end PKG_MessVerifyProcs;
/

CREATE OR REPLACE PUBLIC SYNONYM PKG_MESSVERIFYPROCS FOR KFM.PKG_MESSVERIFYPROCS;

GRANT EXECUTE ON KFM.PKG_MESSVERIFYPROCS TO KFM_ROLE;
