/*
* Автор: Борисов Д.Г.
* Дата: 2013-05-14
* Задача: http://jira.softage.ru/jira/browse/SDFO-9
* Назначение: Изменение обязательных/необязательных полей и шаблона формы для просмотра запроса на регистрацию
*/

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


declare
    filename      varchar2(256) := 'sfm.database-update.2013-05-14.TemplateChange.sql';
    purpose       varchar2(4000) := 'Изменение обязательных/необязательных полей и шаблона формы для просмотра запроса на регистрацию';
    script_exists number := 0;
    template_string clob := to_clob('<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format">
  <xsl:template match="/">
    <HTML>
      <HEAD>
        <META http-equiv="Content-Type" content="text/html" charset="utf-8"/>
        <STYLE type="text/css">
          body { font-family:Times New Roman; font-size:12px; }
          .table {border-collapse:collapse; width:100%; }
          .tableheadermain { font-family:arial;    text-align:center;  font-weight:bold; background:#f6fff0;}
          .table1 { font-family:arial;  font-size:12px; text-align:center; border-collapse:collapse; background:#f9925b;font-weight:bold; border-bottom:0px; border-top:0px; width:100%; }
          .table2 { font-size:11px; text-align:left; border-collapse:collapse; border-top:0px; width:100%; }
          .table2 td { padding-left:3px; }
          .tabletop { font-size:11px;   text-align:left;  background:#f0f4fe; border-collapse:collapse;  width:100%; }
          .tabletop td  { padding-left:3px; }
          .tr { border:1px solid #000000; border-top:0px; }
          .td { border:1px solid #000000;  }
          .tdb {border:1px solid #000000; border-bottom:0px; border-top:0px; }
          .tb { border:1px solid #000000; border-bottom:0px;}
          .tdnum {border:1px solid #000000; background:#f0f4fe; text-align:center; width:40px; }
          .tdnum_ { border:1px solid #000000; background:#f0f4fe; text-align:center; width:40px; border-top:0px; }
          .tdname { border:1px solid #000000; text-align:left; width:140px; }
          .tdname_ { border:1px solid #000000;  text-align:left;width:140px;border-top:0px; }
          .tddescr { border:1px solid #000000;text-align:left;}
          .tddescr_ { border:1px solid #000000; text-align:left; border-top:0px;}
          .tdsubname{ border:1px solid #000000; text-align:left; width:180px; }
          .tdsubname_{ border:1px solid #000000; text-align:left; width:180px; border-top:0px;  }
          .table3 { font-size:11px; text-align:left; border-collapse:collapse;  border-top:0px; width:100%; table-layout:fixed;}
          .table3 td { padding-left:3px;}
          .tddescr3 { border:1px solid #000000;text-align:left; word-wrap:break-word;overflow:scroll;overflow:visible}

        </STYLE>
        <META content="MSHTML 6.00.2900.2876" name="GENERATOR"/>
      </HEAD>
      <BODY>
        <TABLE class="table tableheadermain" cellSpacing="0">
          <TR>
            <TD class="td tb">Регистрационные данные СФМ</TD>
          </TR>
        </TABLE>
        <xsl:for-each select="Root">
          <TABLE class="tabletop" cellSpacing="0">
            <TR>
              <TD class="tdnum">№п/п</TD>
              <TD class="tdname">Наименование</TD>
              <TD class="tddescr">Содержание</TD>
            </TR>
          </TABLE>
          <!-- Информация о сообщении-->
          <xsl:for-each select="OrganisationData">
            <TABLE class="table table1" cellSpacing="0">
              <TR>
                <TD class="td tdb">1. Сведения о субъекте финансового мониторинга</TD>
              </TR>
            </TABLE>
            <TABLE class="table table2" cellSpacing="0">
              <TR>
                <TD class="tdnum">1.1</TD>
                <TD class="tdname">Организационно-правовая форма*</TD>
                <TD class="tddescr">
                  <xsl:call-template name="RED">
                    <xsl:with-param name="IsChanged" select="OpfCode/@changed"/>
                    <xsl:with-param name="Text" select="OpfCode/text()"/>
                  </xsl:call-template>
                </TD>
              </TR>
              <TR cellspacing="0">
                <TD class="tdnum">1.2</TD>
                <TD class="tdname">Название СФМ*</TD>
                <TD class="tddescr">
                  <xsl:call-template name="RED">
                    <xsl:with-param name="IsChanged" select="OrgName/@changed"/>
                    <xsl:with-param name="Text">
                      <xsl:value-of select="OrgName/text()"/>
                      (
                      <xsl:value-of select="ShortName/text()"/>
                      )
                    </xsl:with-param>
                  </xsl:call-template>
                </TD>
              </TR>
              <TR cellspacing="0">
                <TD class="tdnum">1.3</TD>
                <TD class="tdname">Код СФМ*</TD>
                <TD class="tddescr">
                  <xsl:call-template name="RED">
                    <xsl:with-param name="IsChanged" select="CfmCode/@changed"/>
                    <xsl:with-param name="Text" select="CfmCode/text()"/>
                  </xsl:call-template>
                </TD>
              </TR>
              <TR cellspacing="0">
                <TD class="tdnum">1.4</TD>
                <TD class="tdname">БИН/ИИН</TD>
                <TD class="tddescr">
                  <xsl:call-template name="RED">
                    <xsl:with-param name="IsChanged" select="IINBIN/@changed"/>
                    <xsl:with-param name="Text" select="IINBIN/text()"/>
                  </xsl:call-template>
                </TD>
              </TR>
              <TR cellspacing="0">
                <TD class="tdnum">1.5</TD>
                <TD class="tdname">РНН*</TD>
                <TD class="tddescr">
                  <xsl:call-template name="RED">
                    <xsl:with-param name="IsChanged" select="RNN/@changed"/>
                    <xsl:with-param name="Text" select="RNN/text()"/>
                  </xsl:call-template>
                </TD>
              </TR>
              <TR cellspacing="0">
                <TD class="tdnum">1.6</TD>
                <TD class="tdname">БИК</TD>
                <TD class="tddescr">
                  <xsl:value-of select="BIK"/>
                </TD>
              </TR>
              <TR cellspacing="0">
                <TD class="tdnum">1.7</TD>
                <TD class="tdname">Номер ОКЭД</TD>
                <TD class="tddescr">
                  <xsl:value-of select="OKED"/>
                </TD>
              </TR>
              <TR cellspacing="0">
                <TD class="tdnum">1.8</TD>
                <TD class="tdname">Дата государственной регистрации</TD>
                <TD class="tddescr">
                  <xsl:value-of select="RegisterDate"/>
                </TD>
              </TR>
              <TR cellspacing="0">
                <TD class="tdnum">1.9</TD>
                <TD class="tdname">Регистрационный номер</TD>
                <TD class="tddescr">
                  <xsl:value-of select="RegisterNumber"/>
                </TD>
              </TR>
              <TR cellspacing="0">
                <TD class="tdnum">1.10</TD>
                <TD class="tdname">Орган регистрации</TD>
                <TD class="tddescr">
                  <xsl:value-of select="RegisterAgency"/>
                </TD>
              </TR>
            </TABLE>
            <TABLE class="table table2" cellSpacing="0" width="100%">
              <TR cellspacing="0">
                <TD style="border-top:0px" class="tdnum" rowspan="7">1.11</TD>
                <TD style="border-top:0px" class="tdname" rowspan="7">Адрес расположения</TD>
                <TD style="border-top:0px" class="tdsubname">1. Область*:</TD>
                <TD style="border-top:0px" class="tddescr">
                  <xsl:value-of select="Area"/>
                </TD>
              </TR>
              <TR cellspacing="0">
                <TD class="tdsubname">2. Район*:</TD>
                <TD class="tddescr">
                  <xsl:value-of select="District"/>
                </TD>
              </TR>
              <TR cellspacing="0">
                <TD class="tdsubname">3. Населенный пункт (город/поселок/село):</TD>
                <TD class="tddescr">
                  <xsl:value-of select="City"/>
                </TD>
              </TR>
              <TR cellspacing="0">
                <TD class="tdsubname">4. Наименование улицы/проспекта/мр-на*:</TD>
                <TD class="tddescr">
                  <xsl:value-of select="Street"/>
                </TD>
              </TR>
              <TR cellspacing="0">
                <TD class="tdsubname">5. № дома*:</TD>
                <TD class="tddescr">
                  <xsl:value-of select="House"/>
                </TD>
              </TR>
              <TR cellspacing="0">
                <TD class="tdsubname">6. № квартиры/офиса  (при наличии):</TD>
                <TD class="tddescr">
                  <xsl:value-of select="Office"/>
                </TD>
              </TR>
              <TR cellspacing="0">
                <TD class="tdsubname">7. Почтовый индекс:</TD>
                <TD class="tddescr">
                  <xsl:value-of select="PostalIndex"/>
                </TD>
              </TR>
            </TABLE>
            <xsl:variable name="ip" select="AdditionalAcData/@IsAc"/>
            <xsl:if test="$ip=''True''">
              <xsl:for-each select="AdditionalAcData">
                <TABLE class="table table1" cellSpacing="0">
                  <TR>
                    <TD class="td tdb">1.2 Информация об индивидуальном предпринимателе</TD>
                  </TR>
                </TABLE>
                <TABLE class="table table2" cellSpacing="0">
                  <TR cellspacing="0">
                    <TD class="tdnum">1.2.1</TD>
                    <TD class="tdname">Фамилия*</TD>
                    <TD class="tddescr">
                      <xsl:value-of select="SecondName"/>
                    </TD>
                  </TR>
                  <TR cellspacing="0">
                    <TD class="tdnum">1.2.2</TD>
                    <TD class="tdname">Имя*</TD>
                    <TD class="tddescr">
                      <xsl:value-of select="FirstName"/>
                    </TD>
                  </TR>
                  <TR cellspacing="0">
                    <TD class="tdnum">1.2.3</TD>
                    <TD class="tdname">Отчество</TD>
                    <TD class="tddescr">
                      <xsl:value-of select="MiddleName"/>
                    </TD>
                  </TR>
                  <TR cellspacing="0">
                    <TD class="tdnum">1.2.4</TD>
                    <TD class="tdname">Тип документа, удостоверяющего личность*</TD>
                    <TD class="tddescr">
                      <xsl:value-of select="DocumentIdentity"/>
                    </TD>
                  </TR>
                  <TR cellspacing="0">
                    <TD class="tdnum">1.2.5</TD>
                    <TD class="tdname">Серия ДУЛ</TD>
                    <TD class="tddescr">
                      <xsl:value-of select="SeriesDocIdentity"/>
                    </TD>
                  </TR>
                  <TR cellspacing="0">
                    <TD class="tdnum">1.2.6</TD>
                    <TD class="tdname">Номер ДУЛ</TD>
                    <TD class="tddescr">
                      <xsl:value-of select="NumberDocIdentity"/>
                    </TD>
                  </TR>
                  <TR cellspacing="0">
                    <TD class="tdnum">1.2.7</TD>
                    <TD class="tdname">Дата выдачи ДУЛ</TD>
                    <TD class="tddescr">
                      <xsl:value-of select="DateIssuance"/>
                    </TD>
                  </TR>
                  <TR cellspacing="0">
                    <TD class="tdnum">1.2.8</TD>
                    <TD class="tdname">Организация, выдавшая ДУЛ</TD>
                    <TD class="tddescr">
                      <xsl:value-of select="DocumentIssued"/>
                    </TD>
                  </TR>
                </TABLE>
              </xsl:for-each>
            </xsl:if>
            <!-- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -->
            <xsl:for-each select="Persons/Person">
              <xsl:variable name="Num" select="position()"/>
              <TABLE class="table table1" cellSpacing="0">
                <TR>
                  <TD class="td tdb">2.<xsl:value-of select="$Num"/>. Информация о контактном лице (ответственном должностном лице):</TD>
                </TR>
              </TABLE>
              <!-- Заполенение данными-->
        <TABLE class="table table3" cellSpacing="0" style="white-space: normal;">
                <TR cellspacing="0">
                  <TD class="tdnum">2.<xsl:value-of select="$Num"/>.1</TD>
                  <TD class="tdname">Фамилия*</TD>
                  <TD class="tddescr">
                    <xsl:value-of select="SecondName"/>
                  </TD>
                </TR>
                <TR cellspacing="0">
                  <TD class="tdnum">2.<xsl:value-of select="$Num"/>.2</TD>
                  <TD class="tdname">Имя*</TD>
                  <TD class="tddescr">
                    <xsl:value-of select="FirstName"/>
                  </TD>
                </TR>
                <TR cellspacing="0">
                  <TD class="tdnum">2.<xsl:value-of select="$Num"/>.3</TD>
                  <TD class="tdname">Отчество</TD>
                  <TD class="tddescr">
                    <xsl:value-of select="MiddleName"/>
                  </TD>
                </TR>
                <TR cellspacing="0">
                  <TD class="tdnum">2.<xsl:value-of select="$Num"/>.4</TD>
                  <TD class="tdname">Личный номер</TD>
                  <TD class=" tddescr">
                    <xsl:value-of select="IndividualNumber"/>
                  </TD>
                </TR>
                <TR cellspacing="0">
                  <TD class="tdnum">2.<xsl:value-of select="$Num"/>.5</TD>
                  <TD class="tdname">Должность</TD>
                  <TD class=" tddescr">
                    <xsl:value-of select="JobName"/>
                  </TD>
                </TR>
                <TR cellspacing="0">
                  <TD class="tdnum">2.<xsl:value-of select="$Num"/>.6</TD>
                  <TD class="tdname">Телефон</TD>
                  <TD class="tddescr">
                    <xsl:value-of select="Phone"/>
                  </TD>
                </TR>
                <TR cellspacing="0">
                  <TD class="tdnum">2.<xsl:value-of select="$Num"/>.7</TD>
                  <TD class="tdname">Адрес электронной почты</TD>
                  <TD class="tddescr">
                    <xsl:value-of select="Email"/>
                  </TD>
                </TR>
                <TR cellspacing="0">
                  <TD class="tdnum">2.<xsl:value-of select="$Num"/>.8</TD>
                  <TD class="tdname">Сертификат открытого ключа</TD>
                  <TD class="tddescr3">
                    <xsl:value-of select="Certificate"/>
                  </TD>
                </TR>
              </TABLE>
            </xsl:for-each>
            <!-- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -->
            <xsl:for-each select="Founders/Founder">
              <xsl:variable name="Num" select="position()"/>
              <TABLE class="table table1" cellSpacing="0">
                <TR>
                  <TD class="td tdb">3.<xsl:value-of select="$Num"/>. Информация об учредителе:</TD>
                </TR>
              </TABLE>
              <!-- Заполенение данными-->
              <TABLE class="table table2" cellSpacing="0">
                <TR cellspacing="0">
                  <TD class="tdnum">3.<xsl:value-of select="$Num"/>.1</TD>
                  <TD class="tdname">Тип учредителя</TD>
                  <TD class="tddescr">
                    <xsl:value-of select="FounderType"/>
                  </TD>
                </TR>
                <TR cellspacing="0">
                  <TD class="tdnum">3.<xsl:value-of select="$Num"/>.2</TD>
                  <TD class="tdname">Организационно-правовая форма</TD>
                  <TD class="tddescr">
                    <xsl:value-of select="OpfCode"/>
                  </TD>
                </TR>
                <TR cellspacing="0">
                  <TD class="tdnum">3.<xsl:value-of select="$Num"/>.3</TD>
                  <TD class="tdname">Название (для ЮЛ)</TD>
                  <TD class="tddescr">
                    <xsl:value-of select="UrName"/>
                  </TD>
                </TR>
                <TR cellspacing="0">
                  <TD class="tdnum">3.<xsl:value-of select="$Num"/>.4</TD>
                  <TD class="tdname">Фамилия (для физических лиц и ИП)</TD>
                  <TD class="tddescr">
                    <xsl:value-of select="AdditionalAcData/SecondName"/>
                  </TD>
                </TR>
                <TR cellspacing="0">
                  <TD class="tdnum">3.<xsl:value-of select="$Num"/>.5</TD>
                  <TD class="tdname">Имя (для физических лиц и ИП)</TD>
                  <TD class="tddescr">
                    <xsl:value-of select="AdditionalAcData/FirstName"/>
                  </TD>
                </TR>
                <TR cellspacing="0">
                  <TD class="tdnum">3.<xsl:value-of select="$Num"/>.6</TD>
                  <TD class="tdname">Отчество (для физических лиц и ИП)</TD>
                  <TD class="tddescr">
                    <xsl:value-of select="AdditionalAcData/MiddleName"/>
                  </TD>
                </TR>
                <TR cellspacing="0">
                  <TD class="tdnum">3.<xsl:value-of select="$Num"/>.7</TD>
                  <TD class="tdname">Резидентство</TD>
                  <TD class=" tddescr">
                    <xsl:value-of select="ResidenceCode"/>
                  </TD>
                </TR>
              </TABLE>
            </xsl:for-each>
          </xsl:for-each>
          <br/>
          <TABLE height="5" cellSpacing="0" width="100%" bgColor="#f0f4fe" border="1" colspan="1" border-collapse="collapse">
            <TR>
              <TD>
                <FONT face="arial" size="1" color="#000000">
                  Системный идентификатор СФМ:  
                  <xsl:call-template name="IsNull">
                    <xsl:with-param name="Value" select="OrganisationData/SystemId"/>
                  </xsl:call-template>
                </FONT>
              </TD>
            </TR>
          </TABLE>

          <TABLE height="5" cellSpacing="0" width="100%" bgColor="#f6fff0" border="1" colspan="1" border-collapse="collapse">
            <TR>
              <TD>
                <FONT face="arial" size="1" color="#228b22">
                                    Версия XML документа:   <xsl:value-of select="Version"/>
                  <BR>Уникальный идентификатор документа:  <xsl:value-of select="DocumentUniqueIdentifier"/>
                  </BR>
                </FONT>
              </TD>
            </TR>
          </TABLE>
          <br/>
        </xsl:for-each>
      </BODY>
    </HTML>
  </xsl:template>
  <xsl:template name="RED">
    <xsl:param name="IsChanged"/>
    <xsl:param name="Text"/>
    <fo:block>
      <xsl:choose>
        <xsl:when test="$IsChanged=''1''">
          <font color="FF0000">
            <xsl:value-of select="$Text"/>
          </font>
        </xsl:when>
        <xsl:otherwise>
          <xsl:value-of select="$Text"/>
        </xsl:otherwise>
      </xsl:choose>
    </fo:block>
  </xsl:template>
  <xsl:template name="IsNull">
    <xsl:param name="Value"/>
    <fo:block>
      <xsl:choose>
        <xsl:when test="string-length($Value)' || chr(38) || 'gt;0">
          <font color="#000000">
            <xsl:value-of select="$Value"/>
          </font>
        </xsl:when>
        <xsl:otherwise>
            не установлено
        </xsl:otherwise>
      </xsl:choose>
    </fo:block>
  </xsl:template>
</xsl:stylesheet>');

begin

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

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

  UPDATE KFM.MESS_VERIFY_SETUP SET ISENABLE = 0 WHERE VERIFYID = 1209;
  UPDATE KFM.MESS_VERIFY_SETUP SET ISENABLE = 1 WHERE VERIFYID = 1193;

  UPDATE KFM.MESSAGES_VERSIONS SET MESSXMLSCHEMA = template_string
  WHERE VERSIONCODE = 'C5ACEF29-0D1D-4B2B-A11F-58BC2C45BD54';

execute immediate '
CREATE OR REPLACE PACKAGE BODY KFM."PKG_PASSPORT_CFM" as

  function UpdatePassport(pCFMCODE           in number,
                          pMAINCODE          in varchar2,
                          pTAXCODE           in varchar2,
                          pOKPO              in varchar2,
                          pOKED              in varchar2,
                          pFULLNAME          in varchar2,
                          pSHORTNAME         in varchar2,
                          pREGISTR_DATE      in date,
                          pREGGISTR_NUMBER   in varchar2,
                          pREGISTR_WHO       in varchar2,
                          pREG_KATO          in varchar2,
                          pREG_STREET        in varchar2,
                          pREG_HOUSE         in varchar2,
                          pREG_OFFICE        in varchar2,
                          pREG_POSTINDEX     in varchar2,
                          pREG_DIRECTORY     in varchar2,
                          pREG_AREA          in varchar2,
                          pREG_DISTRICT      in varchar2,
                          pREG_CITY          in varchar2,
                          pBankCode          in varchar2,
                          pFasti_name        in varchar2,
                          pREG_AREA_CODE     in number default null,
                          pREG_DISTRICT_CODE in number default null,
                          pREG_CITY_CODE     in number default null,
                          pREG_STREET_CODE   in number default null,
                          pREG_HOUSE_CODE    in number default null,
                          pREG_OFFICE_CODE   in number default null,
                          pCFMTYPE           in NUMBER default null,
                          pORGCODE           in NUMBER default null,
                          pSECONDNAME        in VARCHAR2 default null,
                          pFIRSTNAME         in VARCHAR2 default null,
                          pMIDDLENAME        in VARCHAR2 default null,
                          pAC_DOC_TYPECODE   in NUMBER default null,
                          pAC_DOC_NUMBER     in VARCHAR2 default null,
                          pAC_DOC_SERIES     in VARCHAR2 default null,
                          pAC_DOC_WHOM       in VARCHAR2 default null,
                          pAC_DOC_DATE       in DATE default null)
    return number is
    Result      number default 0;
    pPassportId number default null;

  begin

    update passport_cfm
       set CFMCODE           = pCFMCODE,
           MAINCODE          = pMAINCODE,
           TAXCODE           = pTAXCODE,
           OKPO              = pOKPO,
           OKED              = pOKED,
           FULLNAME          = pFULLNAME,
           SHORTNAME         = pSHORTNAME,
           REGISTR_DATE      = pREGISTR_DATE,
           REGGISTR_NUMBER   = pREGGISTR_NUMBER,
           REGISTR_WHO       = pREGISTR_WHO,
           REG_KATO          = pREG_KATO,
           REG_STREET        = pREG_STREET,
           REG_HOUSE         = pREG_HOUSE,
           REG_OFFICE        = pREG_OFFICE,
           REG_POSTINDEX     = pREG_POSTINDEX,
           REG_DIRECTORY     = pREG_DIRECTORY,
           REG_AREA          = pREG_AREA,
           REG_DISTRICT      = pREG_DISTRICT,
           REG_CITY          = pREG_CITY,
           BANKCODE          = pBankcode,
           FASTI_NAME        = pfasti_name,
           REG_AREA_CODE     = pREG_AREA_CODE,
           REG_DISTRICT_CODE = pREG_DISTRICT_CODE,
           REG_CITY_CODE     = pREG_CITY_CODE,
           REG_STREET_CODE   = pREG_STREET_CODE,
           REG_HOUSE_CODE    = pREG_HOUSE_CODE,
           REG_OFFICE_CODE   = pREG_OFFICE_CODE,
           CFMTYPE           = pCFMTYPE,
           ORGCODE           = pORGCODE,
           SECONDNAME        = pSECONDNAME,
           FIRSTNAME         = pFIRSTNAME,
           MIDDLENAME        = pMIDDLENAME,
           AC_DOC_TYPECODE   = pAC_DOC_TYPECODE,
           AC_DOC_NUMBER     = pAC_DOC_NUMBER,
           AC_DOC_SERIES     = pAC_DOC_SERIES,
           AC_DOC_WHOM       = pAC_DOC_WHOM,
           AC_DOC_DATE       = pAC_DOC_DATE,
           PASSPORT_STATUS   = 1 --     данные только изменились
     where (PASSPORTID = 1);

    insert into passport_cfm_history
      (CFMCODE,
       MAINCODE,
       TAXCODE,
       OKPO,
       OKED,
       FULLNAME,
       SHORTNAME,
       REGISTR_DATE,
       REGGISTR_NUMBER,
       REGISTR_WHO,
       REG_KATO,
       REG_STREET,
       REG_HOUSE,
       REG_OFFICE,
       REG_POSTINDEX,
       REG_DIRECTORY,
       REG_AREA,
       REG_DISTRICT,
       REG_CITY,
       BANKCODE,
       FASTI_NAME,
       REG_AREA_CODE,
       REG_DISTRICT_CODE,
       REG_CITY_CODE,
       REG_STREET_CODE,
       REG_HOUSE_CODE,
       REG_OFFICE_CODE,
       CFMTYPE,
       ORGCODE,
       SECONDNAME,
       FIRSTNAME,
       MIDDLENAME,
       AC_DOC_TYPECODE,
       AC_DOC_NUMBER,
       AC_DOC_SERIES,
       AC_DOC_WHOM,
       AC_DOC_DATE,
       alter_date)
    values
      (pCFMCODE,
       pMAINCODE,
       pTAXCODE,
       pOKPO,
       pOKED,
       pFULLNAME,
       pSHORTNAME,
       pREGISTR_DATE,
       pREGGISTR_NUMBER,
       pREGISTR_WHO,
       pREG_KATO,
       pREG_STREET,
       pREG_HOUSE,
       pREG_OFFICE,
       pREG_POSTINDEX,
       pREG_DIRECTORY,
       pREG_AREA,
       pREG_DISTRICT,
       pREG_CITY,
       pBANKCODE,
       pFASTI_NAME,
       pREG_AREA_CODE,
       pREG_DISTRICT_CODE,
       pREG_CITY_CODE,
       pREG_STREET_CODE,
       pREG_HOUSE_CODE,
       pREG_OFFICE_CODE,
       pCFMTYPE,
       pORGCODE,
       pSECONDNAME,
       pFIRSTNAME,
       pMIDDLENAME,
       pAC_DOC_TYPECODE,
       pAC_DOC_NUMBER,
       pAC_DOC_SERIES,
       pAC_DOC_WHOM,
       pAC_DOC_DATE,
       SYSDATE);


    pkg_audits.WriteUserAudit(7, ''pkg_passport_cfm.UpdatePassport'', null);
    return Result;

  exception
    when others then
      Result := -2000; --Попытка добавить неверные по формату  данные в таблицу или иная ошибка
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  '' PKG_PASSPORT.UpdatePassport'');
      return Result;
  end UpdatePassport;

  function SetPassport(pCFMCODE           in number,
                       pMAINCODE          in varchar2,
                       pTAXCODE           in varchar2,
                       pOKPO              in varchar2,
                       pOKED              in varchar2,
                       pFULLNAME          in varchar2,
                       pSHORTNAME         in varchar2,
                       pREGISTR_DATE      in date,
                       pREGGISTR_NUMBER   in varchar2,
                       pREGISTR_WHO       in varchar2,
                       pREG_KATO          in varchar2,
                       pREG_STREET        in varchar2,
                       pREG_HOUSE         in varchar2,
                       pREG_OFFICE        in varchar2,
                       pREG_POSTINDEX     in varchar2,
                       pREG_DIRECTORY     in varchar2,
                       pREG_AREA          in varchar2,
                       pREG_DISTRICT      in varchar2,
                       pREG_CITY          in varchar2,
                       pBankCode          in varchar2,
                       pFasti_name        in varchar2,
                       pREG_AREA_CODE     in number default null, -- изменить обязательно XXX
                       pREG_DISTRICT_CODE in number default null, -- изменить обязательно XXX
                       pREG_CITY_CODE     in number default null,
                       pREG_STREET_CODE   in number default null, -- изменить обязательно XXX
                       pREG_HOUSE_CODE    in number default null, -- изменить обязательно XXX
                       pREG_OFFICE_CODE   in number default null,
                       pCFMTYPE           in NUMBER default 1,
                       pORGCODE           in NUMBER default null, -- изменить обязательно XXX
                       pSECONDNAME        in VARCHAR2 default null,
                       pFIRSTNAME         in VARCHAR2 default null,
                       pMIDDLENAME        in VARCHAR2 default null,
                       pAC_DOC_TYPECODE   in NUMBER default null,
                       pAC_DOC_NUMBER     in VARCHAR2 default null,
                       pAC_DOC_SERIES     in VARCHAR2 default null,
                       pAC_DOC_WHOM       in VARCHAR2 default null,
                       pAC_DOC_DATE       in DATE default null

                       ) return number is
    Result         number default 0;
    result1        number default 0;
    vpassportcount number default 0;
    vcountCahanged number default 0;
    vOldPassport   passport_cfm%rowtype;
    vNewPassport   passport_cfm%rowtype;
    pPassportId    number default null;

  begin
    if (pMAINCODE is null or pCFMCODE is null or pORGCODE is null or
       pFULLNAME is null) then
      return - 2020; -- Не задано ни одно из ключевых значений для Организции, такие как БИН, РНН, ОКПО
    elsif (pREG_AREA_CODE is null or pREG_DISTRICT_CODE is null) then
      result := -2021;

    end if;

    select count(*) into vpassportcount from passport_cfm;
    if vpassportcount = 1 then
      select count(*)
        into vcountCahanged
        from passport_cfm t
       where t.passportid = 1
         and CFMCODE = pCFMCODE
         and MAINCODE = pMAINCODE
         and TAXCODE = pTAXCODE
         and OKPO = pOKPO
         and OKED = pOKED
         and FULLNAME = pFULLNAME
         and SHORTNAME = pSHORTNAME
         and REGISTR_DATE = pREGISTR_DATE
         and REGGISTR_NUMBER = pREGGISTR_NUMBER
         and REGISTR_WHO = pREGISTR_WHO
         and REG_KATO = pREG_KATO
         and REG_STREET = pREG_STREET
         and REG_HOUSE = pREG_HOUSE
         and REG_OFFICE = pREG_OFFICE
         and REG_POSTINDEX = pREG_POSTINDEX
         and REG_DIRECTORY = pREG_DIRECTORY
         and REG_AREA = pREG_AREA
         and REG_DISTRICT = pREG_DISTRICT
         and REG_CITY = pREG_CITY
         and BANKCODE = pBankcode
         and FASTI_NAME = pfasti_name
         and REG_AREA_CODE = pREG_AREA_CODE
         and REG_DISTRICT_CODE = pREG_DISTRICT_CODE
         and REG_CITY_CODE = pREG_CITY_CODE
         and REG_STREET_CODE = pREG_STREET_CODE
         and REG_HOUSE_CODE = pREG_HOUSE_CODE
         and REG_OFFICE_CODE = pREG_OFFICE_CODE
         and CFMTYPE = pCFMTYPE
         and ORGCODE = pORGCODE
         and SECONDNAME = pSECONDNAME
         and FIRSTNAME = pFIRSTNAME
         and MIDDLENAME = pMIDDLENAME
         and AC_DOC_TYPECODE = pAC_DOC_TYPECODE
         and AC_DOC_NUMBER = pAC_DOC_NUMBER
         and AC_DOC_SERIES = pAC_DOC_SERIES
         and AC_DOC_WHOM = pAC_DOC_WHOM
         and AC_DOC_DATE = pAC_DOC_DATE;

      if vcountCahanged = 1 then
        return - 2023;
      end if;
      result1 := UpdatePassport(pCFMCODE,
                                pMAINCODE,
                                pTAXCODE,
                                pOKPO,
                                pOKED,
                                pFULLNAME,
                                pSHORTNAME,
                                pREGISTR_DATE,
                                pREGGISTR_NUMBER,
                                pREGISTR_WHO,
                                pREG_KATO,
                                pREG_STREET,
                                pREG_HOUSE,
                                pREG_OFFICE,
                                pREG_POSTINDEX,
                                pREG_DIRECTORY,
                                pREG_AREA,
                                pREG_DISTRICT,
                                pREG_CITY,
                                pBANKCODE,
                                pFASTI_NAME,
                                pREG_AREA_CODE,
                                pREG_DISTRICT_CODE,
                                pREG_CITY_CODE,
                                pREG_STREET_CODE,
                                pREG_HOUSE_CODE,
                                pREG_OFFICE_CODE,
                                pCFMTYPE,
                                pORGCODE,
                                pSECONDNAME,
                                pFIRSTNAME,
                                pMIDDLENAME,
                                pAC_DOC_TYPECODE,
                                pAC_DOC_NUMBER,
                                pAC_DOC_SERIES,
                                pAC_DOC_WHOM,
                                pAC_DOC_DATE);
      if result1 <> 0 then
        return result1;
      end if;
    else
      insert into passport_cfm
        (PASSPORTID,
         CFMCODE,
         MAINCODE,
         TAXCODE,
         OKPO,
         OKED,
         FULLNAME,
         SHORTNAME,
         REGISTR_DATE,
         REGGISTR_NUMBER,
         REGISTR_WHO,
         REG_KATO,
         REG_STREET,
         REG_HOUSE,
         REG_OFFICE,
         REG_POSTINDEX,
         REG_DIRECTORY,
         REG_AREA,
         REG_DISTRICT,
         REG_CITY,
         BANKCODE,
         FASTI_NAME,
         REG_AREA_CODE,
         REG_DISTRICT_CODE,
         REG_CITY_CODE,
         REG_STREET_CODE,
         REG_HOUSE_CODE,
         REG_OFFICE_CODE,
         CFMTYPE,
         ORGCODE,
         SECONDNAME,
         FIRSTNAME,
         MIDDLENAME,
         AC_DOC_TYPECODE,
         AC_DOC_NUMBER,
         AC_DOC_SERIES,
         AC_DOC_WHOM,
         AC_DOC_DATE)
      values
        (1,
         pCFMCODE,
         pMAINCODE,
         pTAXCODE,
         pOKPO,
         pOKED,
         pFULLNAME,
         pSHORTNAME,
         pREGISTR_DATE,
         pREGGISTR_NUMBER,
         pREGISTR_WHO,
         pREG_KATO,
         pREG_STREET,
         pREG_HOUSE,
         pREG_OFFICE,
         pREG_POSTINDEX,
         pREG_DIRECTORY,
         pREG_AREA,
         pREG_DISTRICT,
         pREG_CITY,
         pBANKCODE,
         pFASTI_NAME,
         pREG_AREA_CODE,
         pREG_DISTRICT_CODE,
         pREG_CITY_CODE,
         pREG_STREET_CODE,
         pREG_HOUSE_CODE,
         pREG_OFFICE_CODE,
         pCFMTYPE,
         pORGCODE,
         pSECONDNAME,
         pFIRSTNAME,
         pMIDDLENAME,
         pAC_DOC_TYPECODE,
         pAC_DOC_NUMBER,
         pAC_DOC_SERIES,
         pAC_DOC_WHOM,
         pAC_DOC_DATE);
    end if;
    insert into passport_cfm_history
      (CFMCODE,
       MAINCODE,
       TAXCODE,
       OKPO,
       OKED,
       FULLNAME,
       SHORTNAME,
       REGISTR_DATE,
       REGGISTR_NUMBER,
       REGISTR_WHO,
       REG_KATO,
       REG_STREET,
       REG_HOUSE,
       REG_OFFICE,
       REG_POSTINDEX,
       REG_DIRECTORY,
       REG_AREA,
       REG_DISTRICT,
       REG_CITY,
       BANKCODE,
       FASTI_NAME,
       REG_AREA_CODE,
       REG_DISTRICT_CODE,
       REG_CITY_CODE,
       REG_STREET_CODE,
       REG_HOUSE_CODE,
       REG_OFFICE_CODE,
       CFMTYPE,
       ORGCODE,
       SECONDNAME,
       FIRSTNAME,
       MIDDLENAME,
       AC_DOC_TYPECODE,
       AC_DOC_NUMBER,
       AC_DOC_SERIES,
       AC_DOC_WHOM,
       AC_DOC_DATE,
       alter_date)
    values
      (pCFMCODE,
       pMAINCODE,
       pTAXCODE,
       pOKPO,
       pOKED,
       pFULLNAME,
       pSHORTNAME,
       pREGISTR_DATE,
       pREGGISTR_NUMBER,
       pREGISTR_WHO,
       pREG_KATO,
       pREG_STREET,
       pREG_HOUSE,
       pREG_OFFICE,
       pREG_POSTINDEX,
       pREG_DIRECTORY,
       pREG_AREA,
       pREG_DISTRICT,
       pREG_CITY,
       pBANKCODE,
       pFASTI_NAME,
       pREG_AREA_CODE,
       pREG_DISTRICT_CODE,
       pREG_CITY_CODE,
       pREG_STREET_CODE,
       pREG_HOUSE_CODE,
       pREG_OFFICE_CODE,
       pCFMTYPE,
       pORGCODE,
       pSECONDNAME,
       pFIRSTNAME,
       pMIDDLENAME,
       pAC_DOC_TYPECODE,
       pAC_DOC_NUMBER,
       pAC_DOC_SERIES,
       pAC_DOC_WHOM,
       pAC_DOC_DATE,
       SYSDATE)
    returning PassportId into pPassportId;
    insert into passport_cfm_stages_history
      (passportid, status, alter_date, request_id, last_answer_id, userid)
    values
      (pPassportId, 1, SYSDATE, null, null, pkg_users.GetCurrentUserId);

    pkg_audits.WriteUserAudit(7, ''pkg_passport_cfm.SetPassport'', null);
    Return Result;

  exception
    when others then
      Result := -2000; --Попытка добавить неверные по формату  данные в таблицу или иная ошибка
      pkg_exceptions.AddException(sqlcode,
                                  sqlerrm,
                                  '' PKG_PASSPORT.SetPassport'');
      return Result;
  end SetPassport;

  function GetPassport(vPassportCur out sys_refcursor) return number is
    Result number := 0;
  begin

    open vPassportCur for
      select CFMCODE,
             MAINCODE,
             TAXCODE,
             OKPO,
             OKED,
             FULLNAME,
             SHORTNAME,
             REGISTR_DATE,
             REGGISTR_NUMBER,
             REGISTR_WHO,
             REG_KATO,
             REG_STREET,
             REG_HOUSE,
             REG_OFFICE,
             REG_POSTINDEX,
             REG_DIRECTORY,
             REG_AREA,
             REG_DISTRICT,
             REG_CITY,
             BANKCODE,
             FASTI_NAME,
             REG_AREA_CODE,
             REG_DISTRICT_CODE,
             REG_CITY_CODE,
             REG_STREET_CODE,
             REG_HOUSE_CODE,
             REG_OFFICE_CODE,
             cfmtype,
             orgcode,
             secondname,
             firstname,
             middlename,
             ac_doc_typecode,
             ac_doc_number,
             ac_doc_series,
             ac_doc_whom,
             ac_doc_date,
             systemid
        from passport_cfm;
    return Result;
  end;

  function SetDirectory(pREG_DIRECTORY in varchar2) return number is
    Result number default 0;
  begin
    update passport_cfm
       set REG_DIRECTORY = pREG_DIRECTORY
     where PASSPORTID = 1;
    return Result;
  end;

  function GetDirectory(pREG_DIRECTORY out varchar2) return number is
    Result number default 0;
  begin
    select REG_DIRECTORY
      into pREG_DIRECTORY
      from passport_cfm
     where passportid = 1;
    return Result;
  end;

  function GetAdditionalAcData_1(pcfmtype in number) return clob is
    pxml clob;

  begin
    --алексей 2012.10.23
    case pcfmtype
      when 1 then
        /*
              select  XMLElement("AdditionalAcData",XMLATTRIBUTES(''False'' as "IsAc"),null).getclobval()
                into pxml from dual;
        */
        select XMLElement("AdditionalAcData", XMLATTRIBUTES(''False'' as "IsAc"), XMLElement("FirstName", firstname), XMLElement("SecondName", secondname), XMLElement("MiddleName", middlename), XMLElement("DocumentIdentity", ac_doc_typecode), XMLElement("SeriesDocIdentity", ac_doc_series), XMLElement("NumberDocIdentity", ac_doc_number), XMLElement("DocumentIssued", ac_doc_whom), XMLElement("DateIssuance", to_char(ac_doc_date, ''dd.mm.yyyy'')))
               .getclobval()
          into pxml
          from passport_cfm
         where passportid = 1;
      else
        select XMLElement("AdditionalAcData", XMLATTRIBUTES(''True'' as "IsAc"), XMLElement("FirstName", firstname), XMLElement("SecondName", secondname), XMLElement("MiddleName", middlename), XMLElement("DocumentIdentity", ac_doc_typecode), XMLElement("SeriesDocIdentity", ac_doc_series), XMLElement("NumberDocIdentity", ac_doc_number), XMLElement("DocumentIssued", ac_doc_whom), XMLElement("DateIssuance", to_char(ac_doc_date, ''dd.mm.yyyy'')))
               .getclobval()
          into pxml
          from passport_cfm
         where passportid = 1;
    end case;
    return pxml;
  end;

  function GetFoundersData_1 return clob is
    pxml   clob;
    qryCtx DBMS_XMLGEN.CTXHANDLE;
  begin



    select XMLElement("Founders", XMLAgg(XMLElement("Founder",
           XMLElement("FounderType", founder_type), XMLElement("OpfCode", founder_orgcode), XMLElement("UrName", founder_ur_name),case
            founder_type
             when 1 then
              XMLElement("AdditionalAcData",
                         XMLATTRIBUTES(''False'' as
                                       "IsAc"),
                         '''')
             else
              XMLElement("AdditionalAcData",
                         XMLATTRIBUTES(''True'' as
                                       "IsAc"),
                         XMLForest(founder_ac_firstname as
                                   "FirstName",
                                   founder_ac_secondname as
                                   "SecondName",
                                   founder_ac_middlename as
                                   "MiddleName"))
           end, XMLElement("ResidenceCode", founder_residence_c_code))))
           .getclobval()
      INTO pxml
      from founders_cfm;
    return pxml;
  end;

  function GetUsersData_1 return clob is
    pxml   clob;
    qryCtx DBMS_XMLGEN.CTXHANDLE;

  begin


    select XMLElement("Persons", XMLAgg(XMLElement("Person",
           XMLElement("FirstName", firstname), XMLElement("SecondName", lastname), XMLElement("MiddleName", middlename), XMLElement("IndividualNumber", pers_number), XMLElement("JobName", jobname), XMLElement("Phone", phones), XMLElement("Email", emails), XMLElement("Certificate", XMLATTRIBUTES(sertificate_name as "Name", sertificate_size as "Size"), sertificate))))
           .getclobval()
      INTO pxml
      from users u
     where exists (select 1
              from user_roles ur
             where ur.userid = u.userid
               and bitand(ur.roleid, 4) = 4
               and sysdate between ur.datebegin and ur.dateend)
       and u.isenable = 1;

    return pxml;
  end;

  function GetPassportXML_1(pOUTXML out clob) return number is
    lRes              clob;
    lXML              xmltype;
    pCfmType          number;
    lAdditionalAcData clob;
    lFoundersData     clob;
    lUsersData        clob;
    resultValidate    clob;

    result number;
  begin

    begin
      select cfmtype into pCFMtype from passport_cfm where passportid = 1;
    exception
      when others then
        pkg_exceptions.AddException(sqlcode,
                                    sqlerrm,
                                    '' PKG_PASSPORT.GetPassportXML_1'');
        return - 2001; ---Не заданы ключевые данные об организации: ИИН/БИН, РНН
    end;

    lAdditionalAcData := GetAdditionalAcData_1(pCfmType);
    lFoundersData     := GetFoundersData_1;
    lUsersData        := GetUsersData_1;
    select XMLElement("Root", XMLElement("OrganisationData",



           XMLElement("SystemId", systemid), XMLElement("CfmCode", cfmcode), XMLElement("OpfCode", orgcode), XMLElement("OrgName", fullname), XMLElement("ShortName", shortname), XMLElement("RNN", taxcode), XMLElement("IINBIN", maincode), XMLElement("BIK", bankcode), XMLElement("OKED", oked), XMLElement("RegisterDate", to_char(registr_date, ''dd.mm.yyyy'')), XMLElement("RegisterNumber", reggistr_number), XMLElement("RegisterAgency", registr_who), xmltype(lAdditionalAcData), XMLElement("PostalIndex", reg_postindex), XMLElement("Area", XMLATTRIBUTES(reg_area_code as "code"), reg_area), XMLElement("District", XMLATTRIBUTES(reg_district_code as "code"), reg_district), XMLElement("City", XMLATTRIBUTES(reg_city_code as "code"), reg_city), XMLElement("Street", XMLATTRIBUTES(reg_street_code as "code"), reg_street), XMLElement("House", XMLATTRIBUTES(reg_house_code as "code"), reg_house), XMLElement("Office", XMLATTRIBUTES(reg_office_code as "code"), reg_office), xmltype(lUsersData), xmltype(lFoundersData)

           ), XMLElement("Version", ''C5ACEF29-0D1D-4B2B-A11F-58BC2C45BD54''), XMLElement("DocumentUniqueIdentifier", sys_guid()))
           .getclobval()
      INTO lRes
      from passport_cfm
     where passportid = 1;

    lXML    := SYS.XMLType.CreateXML(lRes);
    pOUTXML := lXML.getclobval();

    resultValidate := pkg_messverifyprocs.VerifyMessageRegistration(pOUTXML,
                                                                    11,
                                                                    1);

    if length(resultValidate) <> 0 then
      pOutXML := resultValidate;
      return - 900;
    end if;
    return 0;
  end;

  function UpdatePassportData(pXML in clob) return number is
    result number default 0;
  begin
    update passport_cfm
       set SystemId =
           (select extractvalue(xmltype(pXML), ''/Root/SystemId/text()'')
              from dual)
     where passportid = 1
       and SystemId is null;
    return result;

  exception
    when others then
      return - 2035;
  end;

  function GetCfmId return number is
    result number default null;
  begin
    select systemid into result from passport_cfm where passportid = 1;
    return result;
  end;

end PKG_PASSPORT_CFM;';

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

exception
    when others then
        rollback;
        raise;
end;
/
