<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt"
    xmlns:user="http://mycompany.com/mynamespace"
    xmlns:js="urn:the-xml-files:xslt-csharp"
    xmlns:ntd='http://www.ntd.ru/ntd/'>

  <xsl:output method="text" />

  <xsl:template match="text()"/>

  <!--<xsl:template match="root/Dictionaries">
    DECLARE @dbid int

    <xsl:for-each select="Dictionary">
      exec @dbid = sp_GenerateIdForEntity 'DICTIONARY_BASE', 'PrimaryKey', 1
      INSERT INTO AKUZ.T_DICTIONARY_BASE (DictionaryID, Code, Name, DateAdd, type_)
      VALUES(@dbid, <xsl:value-of select="@Code" />, '<xsl:value-of select="@Name" />', GETDATE(), <xsl:value-of select="@Type" />)
    </xsl:for-each>
    GO
  </xsl:template>-->

  <xsl:template match="root/InsuranceCompanies">
    <xsl:for-each select="InsuranceCompany">
      -- Страх.организация
      create or replace function TempFunc () returns void as $$
      declare
      _orgid uuid;
      _type integer;
      begin
      _orgid := '<xsl:value-of select="@OrganizationID"/>';
      _type := (select EntityID FROM AKUZ.T_ENTITIES
      WHERE EntName='INSURANCE_COMPANY' and Schema='AKUZ');
      INSERT INTO AKUZ.T_ORGANIZATION
      (OrganizationID, Name, ShortName, type_)
      VALUES (_orgID, '<xsl:value-of select="Name"/>', '<xsl:value-of select="ShortName"/>', _type);

      INSERT INTO AKUZ.T_INSURANCE_COMPANY (OrganizationID, Code, PolicyTemplate)
      VALUES (_orgID, '<xsl:value-of select="@Code"/>', <xsl:choose>
        <xsl:when test="string(@PolicyTemplate)">
          '<xsl:value-of select="@PolicyTemplate"/>'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>);
      end;$$ LANGUAGE plpgsql;
      select TempFunc();
    </xsl:for-each>
  </xsl:template>

  <!-- V009_RESULT: V009.xml -->
  <xsl:template match="packet[(zglv[type='Rezult'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="RezultZap" />
    </xsl:for-each>
  </xsl:template>
  <!-- V009 "<zap>" template -->
  <xsl:template name="RezultZap" >
    if not exists(select * from AKUZ.T_V009_RESULT r where r.IDRMP = <xsl:value-of select="IDRMP" />)
    INSERT INTO AKUZ.T_V009_RESULT (IDRMP, RMPNAME, DL_USLOV, DATEBEG, DATEEND)
    SELECT <xsl:value-of select="IDRMP" />
    , '<xsl:value-of select="RMPNAME" />'
    , <xsl:value-of select="DL_USLOV" />
    , convert_to('<xsl:value-of select="DATEBEG" />', 'dd.mm.yyyy')::timestamp
    , <xsl:if test="DATEEND != ''">
      convert_to('<xsl:value-of select="DATEEND" />', 'dd.mm.yyyy')::timestamp
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    else
    update AKUZ.T_V009_RESULT r
    set RMPNAME = '<xsl:value-of select="RMPNAME" />'
    , DL_USLOV = <xsl:value-of select="DL_USLOV" />
    , DATEBEG = convert_to('<xsl:value-of select="DATEBEG" />', 'dd.mm.yyyy')::timestamp
    , DATEEND = <xsl:if test="DATEEND != ''">
      convert_to('<xsl:value-of select="DATEEND" />', 'dd.mm.yyyy')::timestamp
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where r.IDRMP = <xsl:value-of select="IDRMP" />
    --pgsql:go
  </xsl:template>

  <!-- V020_STRUCTURE_BED: V020.xml -->
  <xsl:template match="packet[(zglv[type='KoPr'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="KoPrZap" />
    </xsl:for-each>
  </xsl:template>

  <xsl:template name="KoPrZap">
    DO $$
    BEGIN
    IF NOT EXISTS(SELECT 1 FROM AKUZ.T_V020_STRUCTURE_BED WHERE IDK_PR = <xsl:value-of select="IDK_PR" />) THEN
    INSERT INTO AKUZ.T_V020_STRUCTURE_BED (IDK_PR, K_PRNAME, DATEBEG, DATEEND)
    VALUES(
    <xsl:value-of select="IDK_PR" />,
    '<xsl:value-of select="K_PRNAME" />',
    to_date('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        to_date('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    );
    ELSE
    UPDATE AKUZ.T_V020_STRUCTURE_BED SET
    K_PRNAME = '<xsl:value-of select="K_PRNAME" />',
    DATEBEG = to_date('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    DATEEND = <xsl:choose>
      <xsl:when test="DATEEND != ''">
        to_date('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    WHERE IDK_PR = <xsl:value-of select="IDK_PR" />;
    END IF;
    END;
    $$;
    --pgsql:go
  </xsl:template>

  <!-- V021_SPECIALITY: V021.xml -->

  <xsl:template match="packet[(zglv[type='MedSpec'])]">
    <xsl:for-each select="zap">
      <!-- insert or update V021_SPECIALITY -->
      <xsl:call-template name="MedSpecZap" />
    </xsl:for-each>
  </xsl:template>
  <!-- V021 "<zap>" template -->

  <xsl:template name="MedSpecZap" >
    do
    $$
    begin
    if not exists(select * from AKUZ.T_V021_SPECIALITY s where s.ID = <xsl:value-of select="IDSPEC" /> and s.DateBegin = to_date('<xsl:value-of select="DATEBEG" />', 'dd.mm.yyyy')::timestamp)
    then
    INSERT INTO AKUZ.T_V021_SPECIALITY
    ("id", "name", DateBegin, DateEnd, V021SpecialityID, POSTNAME, IDPOST_MZ)
    VALUES
    (
    '<xsl:value-of select="IDSPEC" />'
    , '<xsl:value-of select="SPECNAME" />'
    , to_date('<xsl:value-of select="DATEBEG" />', 'dd.mm.yyyy')::timestamp
    , <xsl:choose>
      <xsl:when test="string(DATEEND)">
        to_date('<xsl:value-of select="DATEEND" />', 'dd.mm.yyyy')::timestamp
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    , gen_random_uuid()
    , <xsl:choose>
      <xsl:when test="string(POSTNAME)">
        '<xsl:value-of select="POSTNAME"/>'
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    , <xsl:choose>
      <xsl:when test="string(IDPOST_MZ)">
        '<xsl:value-of select="IDPOST_MZ"/>'
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    );
    else
    update akuz.t_v021_speciality s
    set "name" = '<xsl:value-of select="SPECNAME" />'
    , DateBegin = to_date('<xsl:value-of select="DATEBEG" />', 'dd.mm.yyyy')::timestamp
    , DateEnd = <xsl:choose>
      <xsl:when test="string(DATEEND)">
        to_date('<xsl:value-of select="DATEEND" />', 'dd.mm.yyyy')::timestamp
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    , POSTNAME = <xsl:choose>
      <xsl:when test="string(POSTNAME)">
        '<xsl:value-of select="POSTNAME"/>'
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    , IDPOST_MZ = <xsl:choose>
      <xsl:when test="string(IDPOST_MZ)">
        '<xsl:value-of select="IDPOST_MZ"/>'
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    where s.Id = '<xsl:value-of select="IDSPEC" />' and s.DateBegin = to_date('<xsl:value-of select="DATEBEG" />', 'dd.mm.yyyy')::timestamp;
    end if;
    end;
    $$;
    --pgsql:go
  </xsl:template>

  <!-- V015_SPECIALITY: V015.xml -->
  <!--<xsl:template match="packet[(zglv[@type='MedSpec'])]">
    <xsl:for-each select="zap">
      -->
  <!-- insert or update V015_SPECIALITY -->
  <!--
      <xsl:call-template name="MedSpecZap" />
    </xsl:for-each>
  </xsl:template>
  -->
  <!-- V015 "<zap>" template -->
  <!--
  <xsl:template name="MedSpecZap" >
    if not exists(select * from [AKUZ].[T_V015_SPECIALITY] s where s.[RECID] = <xsl:value-of select="@RECID" />)
    INSERT INTO [AKUZ].[T_V015_SPECIALITY] ([RECID] ,[CODE] ,[NAME] ,[HIGH] ,[OKSO] ,[DATEBEG] ,[DATEEND])
    SELECT <xsl:value-of select="@RECID" />
    , <xsl:value-of select="@CODE" />
    , LEFT('<xsl:value-of select="@NAME" />', 256)
    , <xsl:if test="@HIGH != ''">
      '<xsl:value-of select="@HIGH" />'
    </xsl:if><xsl:if test="@HIGH = ''">NULL</xsl:if>
    , <xsl:if test="@OKSO != ''">
      '<xsl:value-of select="@OKSO" />'
    </xsl:if><xsl:if test="@OKSO = ''">NULL</xsl:if>
    , CONVERT(datetime, '<xsl:value-of select="@DATEBEG" />', 104)
    , <xsl:if test="@DATEEND != ''">
      CONVERT(datetime, '<xsl:value-of select="@DATEEND" />', 104)
    </xsl:if><xsl:if test="@DATEEND = ''">NULL</xsl:if>
    else
    update s
    set s.[CODE] = <xsl:value-of select="@CODE" />
    , s.[NAME] = LEFT('<xsl:value-of select="@NAME" />', 256)
    , s.[HIGH] = <xsl:if test="@HIGH != ''">
      '<xsl:value-of select="@HIGH" />'
    </xsl:if><xsl:if test="@HIGH = ''">NULL</xsl:if>
    , s.[OKSO] = <xsl:if test="@OKSO != ''">
      '<xsl:value-of select="@OKSO" />'
    </xsl:if><xsl:if test="@OKSO = ''">NULL</xsl:if>
    , s.[DATEBEG] = CONVERT(datetime, '<xsl:value-of select="@DATEBEG" />', 104)
    , s.[DATEEND] = <xsl:if test="@DATEEND != ''">
      CONVERT(datetime, '<xsl:value-of select="@DATEEND" />', 104)
    </xsl:if><xsl:if test="@DATEEND = ''">NULL</xsl:if>
    from [AKUZ].[T_V015_SPECIALITY] s
    where s.[RECID] = <xsl:value-of select="@RECID" />
    GO
  </xsl:template>
  -->

  <!-- V016_DISP_TYPE: V016.xml -->
  <xsl:template match="packet[(zglv[type='DispT'])]">
    <xsl:for-each select="zap">
      <!-- insert or update V016_DISP_TYPE -->
      <xsl:call-template name="DispT" />
    </xsl:for-each>
  </xsl:template>
  <!-- V016 "<zap>" template -->
  <xsl:template name="DispT">
    DO $$
    BEGIN
    IF NOT EXISTS(
    SELECT 1 FROM AKUZ.T_V016_DISP_TYPE
    WHERE IDDT = LEFT('<xsl:value-of select="IDDT" />', 3)
    ) THEN
    INSERT INTO AKUZ.T_V016_DISP_TYPE (IDDT, DTNAME, DATEBEG, DATEEND, DTRULE)
    VALUES(
    LEFT('<xsl:value-of select="IDDT" />', 3),
    LEFT('<xsl:value-of select="DTNAME" />', 256),
    to_date('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        to_date('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    '<xsl:for-each select="DTRULE/RULE">
      <xsl:value-of select="." />;
    </xsl:for-each>'
    );
    ELSE
    UPDATE AKUZ.T_V016_DISP_TYPE SET
    DTNAME = LEFT('<xsl:value-of select="DTNAME" />', 256),
    DATEBEG = to_date('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    DATEEND = <xsl:choose>
      <xsl:when test="DATEEND != ''">
        to_date('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    DTRULE = '<xsl:for-each select="DTRULE/RULE">
      <xsl:value-of select="." />;
    </xsl:for-each>'
    WHERE IDDT = LEFT('<xsl:value-of select="IDDT" />', 3)
    AND DATEBEG &lt; to_date('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY');
    END IF;
    END;
    $$;
    --pgsql:go
  </xsl:template>

  <!-- V017_DISP_RESULT: V017.xml -->
  <xsl:template match="packet[(zglv[type='DispR'])]">
    <xsl:for-each select="zap">
      <!-- insert or update V017_DISP_RESULT -->
      <xsl:call-template name="DispR" />
    </xsl:for-each>
  </xsl:template>
  <!-- V017 "<zap>" template -->
  <xsl:template name="DispR">
    DO $$
    BEGIN
    IF NOT EXISTS(SELECT 1 FROM AKUZ.T_V017_DISP_RESULT WHERE IDDR = <xsl:value-of select="IDDR" />) THEN
    INSERT INTO AKUZ.T_V017_DISP_RESULT (IDDR, DRNAME, DATEBEG, DATEEND)
    VALUES(
    <xsl:value-of select="IDDR" />,
    LEFT('<xsl:value-of select="DRNAME" />', 256),
    to_date('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        to_date('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    );
    ELSE
    UPDATE AKUZ.T_V017_DISP_RESULT SET
    DRNAME = LEFT('<xsl:value-of select="DRNAME" />', 256),
    DATEBEG = to_date('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    DATEEND = <xsl:choose>
      <xsl:when test="DATEEND != ''">
        to_date('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    WHERE IDDR = <xsl:value-of select="IDDR" />;
    END IF;
    END;
    $$;
    --pgsql:go
  </xsl:template>

  <!-- VMP_SERVICE: V019.xml elements -->
  <xsl:template match="packet[(zglv[type='HMet'])]">

    <!-- Create MedicalServiceType -->

    DO $$
    BEGIN
    IF NOT EXISTS (
    SELECT 1 FROM akuz.t_medical_service_type ms WHERE ms.medicalservicetypeid = '642809B3-0C46-4673-8E97-76A6D60C14F8'
    ) THEN
    INSERT INTO akuz.t_medical_service_type (medicalservicetypeid, name)
    SELECT '642809B3-0C46-4673-8E97-76A6D60C14F8', 'ВМП(V019)';
    END IF;
    END $$;

    <xsl:for-each select="zap">

      <!-- clear MedicalServiceMkbLinks -->
      DELETE FROM akuz.t_medical_service_mkb_link msml
      USING akuz.t_medical_service ms
      WHERE msml.medicalservice = ms.medicalserviceid
      and ms.type_ = 1113 and ms.medicalservicetype = '642809B3-0C46-4673-8E97-76A6D60C14F8'
      and ms.code = '<xsl:value-of select="IDHM" />'
      and ms.economiccode = '<xsl:value-of select="HVID" />'
      and (ms.PatientModelCode = <xsl:choose>
        <xsl:when test="IDMODP != ''">
          <xsl:value-of select="IDMODP" />
        </xsl:when>
        <xsl:otherwise>0</xsl:otherwise>
      </xsl:choose> or ms.PatientModelCode is null)
      and ms.DateBegin = to_date('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY');

    </xsl:for-each>

    <xsl:for-each select="zap">

      <!-- insert or update MedicalService -->

      <xsl:call-template name="HMetZapElements" />

      <!-- insert new MedicalServiceMkbLinks -->
      <xsl:call-template name="HMetDiagnosisElements" />

    </xsl:for-each>
  </xsl:template>

  <!-- V019 "<zap>" template -->
  <xsl:template name="HMetZapElements" >
    do $$
    begin
    if not exists(select 1 from AKUZ.T_MEDICAL_SERVICE ms where ms.type_ = 1113 and ms.MedicalServiceType = '642809B3-0C46-4673-8E97-76A6D60C14F8'
    and ms.Code = '<xsl:value-of select="IDHM" />'
    and ms.EconomicCode = '<xsl:value-of select="HVID" />'
    and (ms.PatientModelCode = <xsl:choose>
      <xsl:when test="IDMODP != ''">
        <xsl:value-of select="IDMODP" />
      </xsl:when>
      <xsl:otherwise>0</xsl:otherwise>
    </xsl:choose> or ms.PatientModelCode is null)
    and ms.DateBegin = to_date('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'))
    then
    insert into akuz.t_medical_service (Code, Name, NameFull, Profile, MedicalServiceID, type_, MedicalServiceType, ShortName, PatientModelCode, EconomicCode, InternalCode, PayType, DateBegin, DateEnd, IsCommonService)
    values (
    '<xsl:value-of select="IDHM" />'
    , LEFT('<xsl:value-of select="HMNAME" />', 256)
    , LEFT('<xsl:value-of select="HMNAME" />', 256)
    , 3
    , gen_random_uuid()
    , 1113
    , '642809B3-0C46-4673-8E97-76A6D60C14F8'
    , LEFT('<xsl:value-of select="HMNAME" />', 24)
    , <xsl:choose>
      <xsl:when test="IDMODP != ''">
        <xsl:value-of select="IDMODP" />
      </xsl:when>
      <xsl:otherwise>0</xsl:otherwise>
    </xsl:choose>
    , '<xsl:value-of select="HVID" />'
    , '<xsl:value-of select="HGR" />'
    , 1
    , to_date('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , <xsl:if test="DATEEND != ''">
      <xsl:text>to_date('</xsl:text>
      <xsl:value-of select="DATEEND" />
      <xsl:text>', 'DD.MM.YYYY')</xsl:text>
    </xsl:if>
    <xsl:if test="DATEEND = ''">
      <xsl:text>NULL</xsl:text>
    </xsl:if>
    , false
    );
    else
    update akuz.t_medical_service ms
    set Name = LEFT('<xsl:value-of select="HMNAME" />', 256)
    , NameFull = LEFT('<xsl:value-of select="HMNAME" />', 256)
    , ShortName = LEFT('<xsl:value-of select="HMNAME" />', 24)
    , PatientModelCode = <xsl:choose>
      <xsl:when test="IDMODP != ''">
        <xsl:value-of select="IDMODP" />
      </xsl:when>
      <xsl:otherwise>0</xsl:otherwise>
    </xsl:choose>
    , InternalCode = '<xsl:value-of select="HGR" />'
    , DateEnd = <xsl:if test="DATEEND != ''">
      <xsl:text>to_date('</xsl:text>
      <xsl:value-of select="DATEEND" />
      <xsl:text>', 'DD.MM.YYYY')</xsl:text>
    </xsl:if>
    <xsl:if test="DATEEND = ''">
      <xsl:text>NULL</xsl:text>
    </xsl:if>
    where ms.type_ = 1113 and ms.MedicalServiceType = '642809B3-0C46-4673-8E97-76A6D60C14F8'
    and ms.Code = '<xsl:value-of select="IDHM" />'
    and ms.EconomicCode = '<xsl:value-of select="HVID" />'
    and (ms.PatientModelCode = <xsl:choose>
      <xsl:when test="IDMODP != ''">
        <xsl:value-of select="IDMODP" />
      </xsl:when>
      <xsl:otherwise>0</xsl:otherwise>
    </xsl:choose> or ms.PatientModelCode is null)
    and ms.DateBegin = to_date('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY');
    end if;
    end
    $$;
  </xsl:template>

  <!-- V019 "DIAG" template -->
  <xsl:template name="HMetDiagnosisElements">
    INSERT INTO AKUZ.T_MEDICAL_SERVICE_MKB_LINK (MedicalServiceMkbLinkID, MedicalService, MkbCode)
    SELECT
    gen_random_uuid() as MedicalServiceMkbLinkID
    , ms.MedicalServiceID
    , MkbCode.Split as MkbCode
    from AKUZ.T_MEDICAL_SERVICE ms
    cross join lateral public.fn_Split(REPLACE(REPLACE('<xsl:value-of select="DIAG" />',' ',''),',',';'), ';') as MkbCode
    where ms.type_ = 1113 and ms.MedicalServiceType = '642809B3-0C46-4673-8E97-76A6D60C14F8'
    and ms.Code = '<xsl:value-of select="IDHM" />'
    and ms.EconomicCode = '<xsl:value-of select="HVID" />'
    and ms.DateBegin = to_date('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    and not exists
    (
    select 1 MedicalServiceMkbLinkID
    from AKUZ.T_MEDICAL_SERVICE_MKB_LINK msl
    where ms.MedicalServiceID = msl.MedicalService
    and MkbCode.Split = msl.MkbCode
    );
  </xsl:template>

  <!-- V002_PROFILE: V002.xml -->
  <xsl:template match="packet[(zglv[@type='ProfOt'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="ProfOtZap" />
    </xsl:for-each>
  </xsl:template>

  <!-- V002 "<zap>" template -->
  <xsl:template name="ProfOtZap">
    INSERT INTO AKUZ.T_V002_PROFILE (Id, Name, DateBegin, DateEnd)
    SELECT
    '<xsl:value-of select="@IDPR" />',
    SUBSTRING('<xsl:value-of select="@PRNAME" />', 1, 256),
    TO_DATE('<xsl:value-of select="@DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="@DATEEND != ''">
        TO_DATE('<xsl:value-of select="@DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (Id) DO UPDATE SET
    Name = EXCLUDED.Name,
    DateBegin = EXCLUDED.DateBegin,
    DateEnd = EXCLUDED.DateEnd;
  </xsl:template>

  <!-- V011_DRUG: V011.xml -->
  <xsl:template match="book[(entries/zglv[type='V011_Drugs'])]">
    <xsl:for-each select="entry">
      <xsl:call-template name="Drugs" />
    </xsl:for-each>
  </xsl:template>

  <!-- V011 "<zap>" template -->
  <xsl:template name="Drugs">
    INSERT INTO AKUZ.T_V011_DRUG (ID, S_NUM_REG, DATE_REG, DATE_END_REG, S_ORG_NAME, S_ORG_COUNTRY,
    S_TORG_NAME, S_MNN, S_FORMS, S_PROD_PHASE, S_DOCS, S_FARM_GROUP, S_CODES)
    SELECT
    <xsl:value-of select="ID" />,
    SUBSTRING('<xsl:value-of select="S_NUM_REG" />', 1, 50),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    SUBSTRING('<xsl:value-of select="S_ORG_NAME" />', 1, 255),
    SUBSTRING('<xsl:value-of select="S_ORG_COUNTRY" />', 1, 255),
    SUBSTRING('<xsl:value-of select="S_TORG_NAME" />', 1, 255),
    SUBSTRING('<xsl:value-of select="S_MNN" />', 1, 255),
    SUBSTRING('<xsl:value-of select="S_FORMS" />', 1, 255),
    SUBSTRING('<xsl:value-of select="S_PROD_PHASE" />', 1, 255),
    SUBSTRING('<xsl:value-of select="S_DOCS" />', 1, 255),
    SUBSTRING('<xsl:value-of select="S_FARM_GROUP" />', 1, 255),
    SUBSTRING('<xsl:value-of select="S_CODES" />', 1, 255)
    ON CONFLICT (ID) DO UPDATE SET
    S_NUM_REG = EXCLUDED.S_NUM_REG,
    DATE_REG = EXCLUDED.DATE_REG,
    DATE_END_REG = EXCLUDED.DATE_END_REG,
    S_ORG_NAME = EXCLUDED.S_ORG_NAME,
    S_ORG_COUNTRY = EXCLUDED.S_ORG_COUNTRY,
    S_TORG_NAME = EXCLUDED.S_TORG_NAME,
    S_MNN = EXCLUDED.S_MNN,
    S_FORMS = EXCLUDED.S_FORMS,
    S_PROD_PHASE = EXCLUDED.S_PROD_PHASE,
    S_DOCS = EXCLUDED.S_DOCS,
    S_FARM_GROUP = EXCLUDED.S_FARM_GROUP,
    S_CODES = EXCLUDED.S_CODES;
  </xsl:template>

  <!-- V024_DopKr: V024.xml -->
  <xsl:template match="packet[(zglv[type='DopKr'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="DopKr" />
    </xsl:for-each>
  </xsl:template>

  <!-- V024 "<zap>" template -->
  <xsl:template name="DopKr">
    INSERT INTO AKUZ.T_V024 (V024DoprKrID, IDDKK, DKKNAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    SUBSTRING('<xsl:value-of select="IDDKK" />', 1, 255),
    <xsl:choose>
      <xsl:when test="DKKNAME != ''">
        SUBSTRING('<xsl:value-of select="DKKNAME" />', 1, 500)
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (IDDKK, DATEBEG) DO UPDATE SET
    <xsl:if test="DKKNAME != ''">DKKNAME = EXCLUDED.DKKNAME,</xsl:if>
    DATEBEG = EXCLUDED.DATEBEG,
    DATEEND = EXCLUDED.DATEEND;
  </xsl:template>

  <!-- V028_RefType: V028.xml -->
  <xsl:template match="packet[(zglv[type='RefType'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="RefType" />
    </xsl:for-each>
  </xsl:template>

  <!-- V028 "<zap>" template -->
  <xsl:template name="RefType">
    INSERT INTO AKUZ.T_V028_REFERRAL_TYPE (IDVN, N_VN, DATEBEG, DATEEND)
    SELECT
    <xsl:value-of select="IDVN" />,
    <xsl:choose>
      <xsl:when test="N_VN != ''">
        SUBSTRING('<xsl:value-of select="N_VN" />', 1, 255)
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (IDVN) DO UPDATE SET
    <xsl:if test="N_VN != ''">N_VN = EXCLUDED.N_VN,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;
  </xsl:template>

  <!-- V029_ResMet: V029.xml -->
  <xsl:template match="packet[(zglv[type='ResMet'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="ResMet" />
    </xsl:for-each>
  </xsl:template>

  <!-- V029 "<zap>" template -->
  <xsl:template name="ResMet">
    INSERT INTO AKUZ.T_V029_RESEARCH_METHOD (IDMET, N_MET, DATEBEG, DATEEND)
    SELECT
    <xsl:value-of select="IDMET" />,
    <xsl:choose>
      <xsl:when test="N_MET != ''">
        SUBSTRING('<xsl:value-of select="N_MET" />', 1, 255)
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (IDMET) DO UPDATE SET
    <xsl:if test="N_MET != ''">N_MET = EXCLUDED.N_MET,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;
  </xsl:template>

  <!-- V031_GroupDrug: V031.xml -->
  <xsl:template match="packet[(zglv[type='GroupDrug'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="GroupDrug" />
    </xsl:for-each>
  </xsl:template>

  <!-- V031 "<zap>" template -->
  <xsl:template name="GroupDrug">
    INSERT INTO AKUZ.T_V031_GROUP_DRUG (V031GroupDrugID, DrugGroupCode, ManIndMNN, DrugGroup, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="DrugGroupCode" />,
    <xsl:choose>
      <xsl:when test="ManIndMNN != ''">
        <xsl:value-of select="ManIndMNN" />
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    SUBSTRING('<xsl:value-of select="DrugGroup" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (DrugGroupCode, DATEBEG) DO UPDATE SET
    <xsl:if test="ManIndMNN != ''">ManIndMNN = EXCLUDED.ManIndMNN,</xsl:if>
    <xsl:if test="DrugGroup != ''">DrugGroup = EXCLUDED.DrugGroup,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;
  </xsl:template>

  <!-- V032_DrugGrReg: V032.xml -->
  <xsl:template match="packet[(zglv[type='DrugGrReg'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="DrugGrReg" />
    </xsl:for-each>
  </xsl:template>

  <!-- V032 "<zap>" template -->
  <xsl:template name="DrugGrReg">
    INSERT INTO AKUZ.T_V032_DRUG_GR_REG (V032DrugGrRegID, ScheDrugGrCd, Name, SchemCode, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    SUBSTRING('<xsl:value-of select="ScheDrugGrCd" />', 1, 255),
    <xsl:choose>
      <xsl:when test="Name != ''">
        SUBSTRING('<xsl:value-of select="Name" />', 1, 255)
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    <xsl:choose>
      <xsl:when test="SchemCode != ''">
        SUBSTRING('<xsl:value-of select="SchemCode" />', 1, 255)
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ScheDrugGrCd, DATEBEG) DO UPDATE SET
    <xsl:if test="Name != ''">Name = EXCLUDED.Name,</xsl:if>
    <xsl:if test="SchemCode != ''">SchemCode = EXCLUDED.SchemCode,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;
  </xsl:template>

  <!-- V033_SchemCodDrug: V033.xml -->
  <xsl:template match="packet[(zglv[type='SchemCodDrug'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="SchemCodDrug" />
    </xsl:for-each>
  </xsl:template>

  <!-- V033 "<zap>" template -->
  <xsl:template name="SchemCodDrug">
    INSERT INTO AKUZ.T_V033_SCHEM_COD_DRUG (V033SchemCodDrugID, ScheDrugGrCd, DrugCode, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    SUBSTRING('<xsl:value-of select="ScheDrugGrCd" />', 1, 255),
    SUBSTRING('<xsl:value-of select="DrugCode" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ScheDrugGrCd, DrugCode, DATEBEG) DO UPDATE SET
    DATEEND = EXCLUDED.DATEEND;
  </xsl:template>

  <!-- V022_ModPac: V022.xml -->
  <xsl:template match="packet[(zglv[type='ModPac'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="ModPac" />
    </xsl:for-each>
  </xsl:template>

  <!-- V022 "<zap>" template -->
  <xsl:template name="ModPac">
    INSERT INTO AKUZ.T_V022_PATIENT_MODEL (IDMPAC, MPACNAME, DATEBEG, DATEEND)
    SELECT
    <xsl:value-of select="IDMPAC" />,
    <xsl:choose>
      <xsl:when test="MPACNAME != ''">
        SUBSTRING('<xsl:value-of select="MPACNAME" />', 1, 2000)
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (IDMPAC) DO UPDATE SET
    <xsl:if test="MPACNAME != ''">MPACNAME = EXCLUDED.MPACNAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;
  </xsl:template>

  <!-- N001_OnkPrOt: N001.xml -->
  <xsl:template match="packet[(zglv[type='OnkPrOt'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkPrOt" />
    </xsl:for-each>
  </xsl:template>

  <!-- N001 "<zap>" template -->
  <xsl:template name="OnkPrOt">
    INSERT INTO AKUZ.T_N001_OnkPrOt (N001ID, ID_PrOt, PrOt_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_PrOt" />,
    SUBSTRING('<xsl:value-of select="PrOt_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_PrOt, DATEBEG) DO UPDATE SET
    <xsl:if test="PrOt_NAME != ''">PrOt_NAME = EXCLUDED.PrOt_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N001_OnkPrOt t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N001_OnkPrOt tt
    WHERE t.ID_PrOt = tt.ID_PrOt AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N002_OnkStad: N002.xml -->
  <xsl:template match="packet[(zglv[type='OnkStad'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkStad" />
    </xsl:for-each>
  </xsl:template>

  <!-- N002 "<zap>" template -->
  <xsl:template name="OnkStad">
    INSERT INTO AKUZ.T_N002_OnkStad (N002ID, ID_St, DS_St, KOD_St, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_St" />,
    SUBSTRING('<xsl:value-of select="DS_St" />', 1, 5),
    SUBSTRING('<xsl:value-of select="KOD_St" />', 1, 5),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_St, DATEBEG) DO UPDATE SET
    DS_St = EXCLUDED.DS_St,
    KOD_St = EXCLUDED.KOD_St,
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N002_OnkStad t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N002_OnkStad tt
    WHERE t.ID_St = tt.ID_St AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N003_OnkT: N003.xml -->
  <xsl:template match="packet[(zglv[type='OnkT'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkT" />
    </xsl:for-each>
  </xsl:template>

  <!-- N003 "<zap>" template -->
  <xsl:template name="OnkT">
    INSERT INTO AKUZ.T_N003_OnkT (N003ID, ID_T, DS_T, KOD_T, T_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_T" />,
    SUBSTRING('<xsl:value-of select="DS_T" />', 1, 5),
    SUBSTRING('<xsl:value-of select="KOD_T" />', 1, 255),
    SUBSTRING('<xsl:value-of select="T_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_T, DATEBEG) DO UPDATE SET
    DS_T = EXCLUDED.DS_T,
    KOD_T = EXCLUDED.KOD_T,
    <xsl:if test="T_NAME != ''">T_NAME = EXCLUDED.T_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N003_OnkT t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N003_OnkT tt
    WHERE t.ID_T = tt.ID_T AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N004_OnkN: N004.xml -->
  <xsl:template match="packet[(zglv[type='OnkN'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkN" />
    </xsl:for-each>
  </xsl:template>

  <!-- N004 "<zap>" template -->
  <xsl:template name="OnkN">
    INSERT INTO AKUZ.T_N004_OnkN (N004ID, ID_N, DS_N, KOD_N, N_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_N" />,
    SUBSTRING('<xsl:value-of select="DS_N" />', 1, 5),
    SUBSTRING('<xsl:value-of select="KOD_N" />', 1, 5),
    SUBSTRING('<xsl:value-of select="N_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_N, DATEBEG) DO UPDATE SET
    DS_N = EXCLUDED.DS_N,
    KOD_N = EXCLUDED.KOD_N,
    <xsl:if test="N_NAME != ''">N_NAME = EXCLUDED.N_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N004_OnkN t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N004_OnkN tt
    WHERE t.ID_N = tt.ID_N AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N005_OnkM: N005.xml -->
  <xsl:template match="packet[(zglv[type='OnkM'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkM" />
    </xsl:for-each>
  </xsl:template>
  <!-- N005 "<zap>" template -->
  <xsl:template name="OnkM">
    INSERT INTO AKUZ.T_N005_OnkM (N005ID, ID_M, DS_M, KOD_M, M_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_M" />,
    SUBSTRING('<xsl:value-of select="DS_M" />', 1, 5),
    SUBSTRING('<xsl:value-of select="KOD_M" />', 1, 5),
    SUBSTRING('<xsl:value-of select="M_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_M, DATEBEG) DO UPDATE SET
    DS_M = EXCLUDED.DS_M,
    KOD_M = EXCLUDED.KOD_M,
    <xsl:if test="M_NAME != ''">M_NAME = EXCLUDED.M_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N005_OnkM t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N005_OnkM tt
    WHERE t.ID_M = tt.ID_M AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N006_OnkTNM: N006.xml -->
  <xsl:template match="packet[(zglv[type='OnkTNM'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkTNM" />
    </xsl:for-each>
  </xsl:template>
  <!-- N006 "<zap>" template -->
  <xsl:template name="OnkTNM">
    INSERT INTO AKUZ.T_N006_OnkTNM (N006ID, ID_gr, DS_gr, ID_St, ID_T, ID_N, ID_M, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_gr" />,
    SUBSTRING('<xsl:value-of select="DS_gr" />', 1, 5),
    <xsl:value-of select="ID_St" />,
    <xsl:value-of select="ID_T" />,
    <xsl:value-of select="ID_N" />,
    <xsl:value-of select="ID_M" />,
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_gr, DATEBEG) DO UPDATE SET
    DS_gr = EXCLUDED.DS_gr,
    ID_St = EXCLUDED.ID_St,
    ID_T = EXCLUDED.ID_T,
    ID_N = EXCLUDED.ID_N,
    ID_M = EXCLUDED.ID_M,
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N006_OnkTNM t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N006_OnkTNM tt
    WHERE t.ID_gr = tt.ID_gr AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N007_OnkMrf: N007.xml -->
  <xsl:template match="packet[(zglv[type='OnkMrf'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkMrf" />
    </xsl:for-each>
  </xsl:template>
  <!-- N007 "<zap>" template -->
  <xsl:template name="OnkMrf">
    INSERT INTO AKUZ.T_N007_OnkMrf (N007ID, ID_Mrf, Mrf_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_Mrf" />,
    SUBSTRING('<xsl:value-of select="Mrf_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_Mrf, DATEBEG) DO UPDATE SET
    <xsl:if test="Mrf_NAME != ''">Mrf_NAME = EXCLUDED.Mrf_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N007_OnkMrf t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N007_OnkMrf tt
    WHERE t.ID_Mrf = tt.ID_Mrf AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N008_OnkMrfRt: N008.xml -->
  <xsl:template match="packet[(zglv[type='OnkMrfRt'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkMrfRt" />
    </xsl:for-each>
  </xsl:template>
  <!-- N008 "<zap>" template -->
  <xsl:template name="OnkMrfRt">
    INSERT INTO AKUZ.T_N008_OnkMrfRt (N008ID, ID_R_M, ID_Mrf, R_M_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_R_M" />,
    <xsl:value-of select="ID_Mrf" />,
    SUBSTRING('<xsl:value-of select="R_M_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_R_M, DATEBEG) DO UPDATE SET
    ID_Mrf = EXCLUDED.ID_Mrf,
    <xsl:if test="R_M_NAME != ''">R_M_NAME = EXCLUDED.R_M_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N008_OnkMrfRt t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N008_OnkMrfRt tt
    WHERE t.ID_R_M = tt.ID_R_M AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N009_OnkMrtDS: N009.xml -->
  <xsl:template match="packet[(zglv[type='OnkMrtDS'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkMrtDS" />
    </xsl:for-each>
  </xsl:template>
  <!-- N009 "<zap>" template -->
  <xsl:template name="OnkMrtDS">
    INSERT INTO AKUZ.T_N009_OnkMrtDS (N009ID, ID_M_D, DS_Mrf, ID_Mrf, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_M_D" />,
    SUBSTRING('<xsl:value-of select="DS_Mrf" />', 1, 5),
    <xsl:value-of select="ID_Mrf" />,
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_M_D, DATEBEG) DO UPDATE SET
    DS_Mrf = EXCLUDED.DS_Mrf,
    ID_Mrf = EXCLUDED.ID_Mrf,
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N009_OnkMrtDS t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N009_OnkMrtDS tt
    WHERE t.ID_M_D = tt.ID_M_D AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N010_OnkIgh: N010.xml -->
  <xsl:template match="packet[(zglv[type='OnkIgh'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkIgh" />
    </xsl:for-each>
  </xsl:template>
  <!-- N010 "<zap>" template -->
  <xsl:template name="OnkIgh">
    INSERT INTO AKUZ.T_N010_OnkIgh (N010ID, ID_Igh, KOD_Igh, Igh_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_Igh" />,
    SUBSTRING('<xsl:value-of select="KOD_Igh" />', 1, 255),
    SUBSTRING('<xsl:value-of select="Igh_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_Igh, DATEBEG) DO UPDATE SET
    KOD_Igh = EXCLUDED.KOD_Igh,
    <xsl:if test="Igh_NAME != ''">Igh_NAME = EXCLUDED.Igh_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N010_OnkIgh t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N010_OnkIgh tt
    WHERE t.ID_Igh = tt.ID_Igh AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N011_OnkIghRt: N011.xml -->
  <xsl:template match="packet[(zglv[type='OnkIghRt'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkIghRt" />
    </xsl:for-each>
  </xsl:template>
  <!-- N011 "<zap>" template -->
  <xsl:template name="OnkIghRt">
    INSERT INTO AKUZ.T_N011_OnkIghRt (N011ID, ID_R_I, ID_Igh, KOD_R_I, R_I_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_R_I" />,
    <xsl:value-of select="ID_Igh" />,
    SUBSTRING('<xsl:value-of select="KOD_R_I" />', 1, 255),
    SUBSTRING('<xsl:value-of select="R_I_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_R_I, DATEBEG) DO UPDATE SET
    ID_Igh = EXCLUDED.ID_Igh,
    KOD_R_I = EXCLUDED.KOD_R_I,
    <xsl:if test="R_I_NAME != ''">R_I_NAME = EXCLUDED.R_I_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N011_OnkIghRt t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N011_OnkIghRt tt
    WHERE t.ID_R_I = tt.ID_R_I AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N012_OnkIghDS: N012.xml -->
  <xsl:template match="packet[(zglv[type='OnkIghDS'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkIghDS" />
    </xsl:for-each>
  </xsl:template>
  <!-- N012 "<zap>" template -->
  <xsl:template name="OnkIghDS">
    INSERT INTO AKUZ.T_N012_OnkIghDS (N012ID, ID_I_D, ID_Igh, DS_Igh, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_I_D" />,
    <xsl:value-of select="ID_Igh" />,
    SUBSTRING('<xsl:value-of select="DS_Igh" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_I_D, DATEBEG) DO UPDATE SET
    ID_Igh = EXCLUDED.ID_Igh,
    DS_Igh = EXCLUDED.DS_Igh,
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N012_OnkIghDS t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N012_OnkIghDS tt
    WHERE t.ID_I_D = tt.ID_I_D AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N013_OnkLech: N013.xml -->
  <xsl:template match="packet[(zglv[type='OnkLech'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkLech" />
    </xsl:for-each>
  </xsl:template>
  <!-- N013 "<zap>" template -->
  <xsl:template name="OnkLech">
    INSERT INTO AKUZ.T_N013_OnkLech (N013ID, ID_TLech, TLech_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_TLech" />,
    SUBSTRING('<xsl:value-of select="TLech_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_TLech, DATEBEG) DO UPDATE SET
    <xsl:if test="TLech_NAME != ''">TLech_NAME = EXCLUDED.TLech_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N013_OnkLech t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N013_OnkLech tt
    WHERE t.ID_TLech = tt.ID_TLech AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N014_OnkHir: N014.xml -->
  <xsl:template match="packet[(zglv[type='OnkHir'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkHir" />
    </xsl:for-each>
  </xsl:template>
  <!-- N014 "<zap>" template -->
  <xsl:template name="OnkHir">
    INSERT INTO AKUZ.T_N014_OnkHir (N014ID, ID_THir, THir_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_THir" />,
    SUBSTRING('<xsl:value-of select="THir_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_THir, DATEBEG) DO UPDATE SET
    <xsl:if test="THir_NAME != ''">THir_NAME = EXCLUDED.THir_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N014_OnkHir t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N014_OnkHir tt
    WHERE t.ID_THir = tt.ID_THir AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N015_OnkLek_L: N015.xml -->
  <xsl:template match="packet[(zglv[type='OnkLek_L'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkLek_L" />
    </xsl:for-each>
  </xsl:template>
  <!-- N015 "<zap>" template -->
  <xsl:template name="OnkLek_L">
    INSERT INTO AKUZ.T_N015_OnkLek_L (N015ID, ID_TLek_L, TLek_NAME_L, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_TLek_L" />,
    SUBSTRING('<xsl:value-of select="TLek_NAME_L" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_TLek_L, DATEBEG) DO UPDATE SET
    <xsl:if test="TLek_NAME_L != ''">TLek_NAME_L = EXCLUDED.TLek_NAME_L,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N015_OnkLek_L t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N015_OnkLek_L tt
    WHERE t.ID_TLek_L = tt.ID_TLek_L AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N016_OnkLek_V: N016.xml -->
  <xsl:template match="packet[(zglv[type='OnkLek_V'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkLek_V" />
    </xsl:for-each>
  </xsl:template>
  <!-- N016 "<zap>" template -->
  <xsl:template name="OnkLek_V">
    INSERT INTO AKUZ.T_N016_OnkLek_V (N016ID, ID_TLek_V, TLek_NAME_V, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_TLek_V" />,
    SUBSTRING('<xsl:value-of select="TLek_NAME_V" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_TLek_V, DATEBEG) DO UPDATE SET
    <xsl:if test="TLek_NAME_V != ''">TLek_NAME_V = EXCLUDED.TLek_NAME_V,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N016_OnkLek_V t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N016_OnkLek_V tt
    WHERE t.ID_TLek_V = tt.ID_TLek_V AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N017_OnkLuch: N017.xml -->
  <xsl:template match="packet[(zglv[type='OnkLuch'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkLuch" />
    </xsl:for-each>
  </xsl:template>
  <!-- N017 "<zap>" template -->
  <xsl:template name="OnkLuch">
    INSERT INTO AKUZ.T_N017_OnkLuch (N017ID, ID_TLuch, TLuch_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_TLuch" />,
    SUBSTRING('<xsl:value-of select="TLuch_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_TLuch, DATEBEG) DO UPDATE SET
    <xsl:if test="TLuch_NAME != ''">TLuch_NAME = EXCLUDED.TLuch_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N017_OnkLuch t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N017_OnkLuch tt
    WHERE t.ID_TLuch = tt.ID_TLuch AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N018_OnkReas: N018.xml -->
  <xsl:template match="packet[(zglv[type='OnkReas'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkReas" />
    </xsl:for-each>
  </xsl:template>
  <!-- N018 "<zap>" template -->
  <xsl:template name="OnkReas">
    INSERT INTO AKUZ.T_N018_OnkReas (N018ID, ID_REAS, REAS_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_REAS" />,
    SUBSTRING('<xsl:value-of select="REAS_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_REAS, DATEBEG) DO UPDATE SET
    <xsl:if test="REAS_NAME != ''">REAS_NAME = EXCLUDED.REAS_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N018_OnkReas t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N018_OnkReas tt
    WHERE t.ID_REAS = tt.ID_REAS AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N019_OnkCons: N019.xml -->
  <xsl:template match="packet[(zglv[type='OnkCons'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkCons" />
    </xsl:for-each>
  </xsl:template>
  <!-- N019 "<zap>" template -->
  <xsl:template name="OnkCons">
    INSERT INTO AKUZ.T_N019_OnkCons (N019ID, ID_CONS, CONS_NAME, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_CONS" />,
    SUBSTRING('<xsl:value-of select="CONS_NAME" />', 1, 255),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_CONS, DATEBEG) DO UPDATE SET
    <xsl:if test="CONS_NAME != ''">CONS_NAME = EXCLUDED.CONS_NAME,</xsl:if>
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N019_OnkCons t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N019_OnkCons tt
    WHERE t.ID_CONS = tt.ID_CONS AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N021_OnkLpsh: N021.xml -->
  <xsl:template match="packet[(zglv[type='OnkLpsh'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkLpsh" />
    </xsl:for-each>
  </xsl:template>

  <!-- N021 "<zap>" template -->
  <xsl:template name="OnkLpsh">
    INSERT INTO AKUZ.T_N021_OnkLpsh (N021ID, ID_ZAP, CODE_SH, ID_LEKP, DATEBEG, DATEEND, ID_LEKP_EXT, LEKP_EXT)
    SELECT
    gen_random_uuid(),
    SUBSTRING('<xsl:value-of select="ID_ZAP" />', 1, 15),
    SUBSTRING('<xsl:value-of select="CODE_SH" />', 1, 10),
    SUBSTRING('<xsl:value-of select="ID_LEKP" />', 1, 6),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>,
    SUBSTRING('<xsl:value-of select="ID_LEKP_EXT" />', 1, 25),
    SUBSTRING('<xsl:value-of select="LEKP_EXT" />', 1, 256)
    ON CONFLICT (ID_ZAP, DATEBEG) DO UPDATE SET
    CODE_SH = EXCLUDED.CODE_SH,
    ID_LEKP = EXCLUDED.ID_LEKP,
    DATEEND = EXCLUDED.DATEEND,
    ID_LEKP_EXT = EXCLUDED.ID_LEKP_EXT,
    LEKP_EXT = EXCLUDED.LEKP_EXT;

    UPDATE AKUZ.T_N021_OnkLpsh t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N021_OnkLpsh tt
    WHERE t.ID_ZAP = tt.ID_ZAP AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- N020_OnkLekp: N020.xml -->
  <xsl:template match="packet[(zglv[type='OnkLekp'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="OnkLekp" />
    </xsl:for-each>
  </xsl:template>

  <!-- N020 "<zap>" template -->
  <xsl:template name="OnkLekp">
    INSERT INTO AKUZ.T_N020_OnkLekp (N020ID, ID_LEKP, MNN, DATEBEG, DATEEND)
    SELECT
    gen_random_uuid(),
    SUBSTRING('<xsl:value-of select="ID_LEKP" />', 1, 6),
    SUBSTRING('<xsl:value-of select="MNN" />', 1, 300),
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (ID_LEKP, DATEBEG) DO UPDATE SET
    MNN = EXCLUDED.MNN,
    DATEEND = EXCLUDED.DATEEND;

    UPDATE AKUZ.T_N020_OnkLekp t
    SET DATEEND = (tt.DATEBEG - INTERVAL '1 day')::date
    FROM AKUZ.T_N020_OnkLekp tt
    WHERE t.ID_LEKP = tt.ID_LEKP AND tt.DATEBEG > t.DATEBEG
    AND t.DATEEND IS NULL;
  </xsl:template>

  <!-- CouncilGoals: N019.xml -->
  <xsl:template match="packet[(zglv[type='N019CouncilGoals'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="N019CouncilGoals" />
    </xsl:for-each>
  </xsl:template>

  <!-- N019 "<zap>" template -->
  <xsl:template name="N019CouncilGoals">
    INSERT INTO AKUZ.T_COUNCIL_GOAL (CouncilGoalID, Code, Name, Type, DateBegin, DateEnd)
    SELECT
    gen_random_uuid(),
    <xsl:value-of select="ID_CONS" />,
    SUBSTRING('<xsl:value-of select="CONS_NAME" />', 1, 255),
    1,
    TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY'),
    <xsl:choose>
      <xsl:when test="DATEEND != ''">
        TO_DATE('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    ON CONFLICT (Code, Type) WHERE Type = 1 DO UPDATE SET
    <xsl:if test="CONS_NAME != ''">Name = EXCLUDED.Name,</xsl:if>
    DateEnd = EXCLUDED.DateEnd;
  </xsl:template>

  <!-- F014_RESULT_DENY_REASON: F014.xml -->
  <xsl:template match="packet[(zglv[type='OplOtk'])]">
    <xsl:for-each select="zap">
      <!-- insert or update F014_RESULT_DENY_REASON -->
      <xsl:call-template name="OplOtkZap" />
    </xsl:for-each>
  </xsl:template>
  <!-- F014_RESULT_DENY_REASON "<zap>" template -->
  <xsl:template name="OplOtkZap" >
    create extension if not exists "uuid-ossp";
    do $$
    begin
    if not exists(select * from AKUZ.T_F014_DENY_REASON  where Kod = <xsl:value-of select="Kod" /> and DATEBEG = TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')) then
    insert into AKUZ.T_F014_DENY_REASON (F014DenyReasonID, Kod, Naim, DATEBEG, DATEEND, Osn, Komment, IDVID, KodPG)
    values(
    uuid_generate_v4()
    , <xsl:value-of select="Kod" />
    , '<xsl:value-of select="Naim" />'
    , TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , <xsl:if test="DATEEND != ''">
      <xsl:text>TO_DATE('</xsl:text>
      <xsl:value-of select="DATEEND" />
      <xsl:text>', 'DD.MM.YYYY')</xsl:text>
    </xsl:if>
    <xsl:if test="DATEEND = ''">NULL</xsl:if>
    , '<xsl:value-of select="Osn" />'
    , '<xsl:value-of select="Komment" />'
    , <xsl:if test="IDVID != ''">
      <xsl:value-of select="IDVID" />
    </xsl:if>
    <xsl:if test="IDVID = ''">NULL</xsl:if>
    , <xsl:if test="KodPG != ''">
      '<xsl:value-of select="KodPG" />'
    </xsl:if>
    <xsl:if test="KodPG = ''">NULL</xsl:if>
    );
    else
    update AKUZ.T_F014_DENY_REASON
    set
    Kod = <xsl:value-of select="Kod" />
    , Naim = '<xsl:value-of select="Naim" />'
    , DATEBEG = TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , DATEEND = <xsl:if test="DATEEND != ''">
      <xsl:text>TO_DATE('</xsl:text>
      <xsl:value-of select="DATEEND" />
      <xsl:text>', 'DD.MM.YYYY')</xsl:text>
    </xsl:if>
    <xsl:if test="DATEEND = ''">NULL</xsl:if>
    , Osn = '<xsl:value-of select="Osn" />'
    , Komment = '<xsl:value-of select="Komment" />'
    where Kod = <xsl:value-of select="Kod" /> and DATEBEG = TO_DATE('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY');
    end if;
    end;
    $$;
  </xsl:template>

  <!-- F002 INSURER: F002.xml -->
  <xsl:template match="PACKET[insCompany]">
    <xsl:for-each select="insCompany">
      <!-- insert or update F002 INSURER -->
      <xsl:call-template name="InsCompany"/>
    </xsl:for-each>
  </xsl:template>
  <!-- F002 INSURER "<insCompany>" template -->
  <xsl:template name="InsCompany">
    create extension if not exists "uuid-ossp";
    do $$
    begin
    if not exists(select * from AKUZ.T_INSURER where SmoCod = '<xsl:value-of select="smocod"/>' and Gk_start = TO_DATE('<xsl:value-of select="insInclude/d_begin"/>', 'DD.MM.YYYY')) then
    INSERT INTO AKUZ.T_INSURER
    (InsurerID, OKATO, SmoCod, Name, ShortName, INN, OGRN, KPP, IndexLegal, AddressLegal,
    IndexReal, Address, OKOPF, DirectorLastName, DirectorFirstName, DirectorFatherName, Phone, Fax, <!--hot_line,--> EMail, SAIT,
    N_DOC, Date_b, Date_e, D_TERM, ORG, Gk_start, Gk_end, NAME_E, NAL_P, YEAR_WORK, DUVED,
    KOL_ZL, Date_red)
    values(
    uuid_generate_v4()
    , '<xsl:value-of select="TF_OKATO"/>'
    , '<xsl:value-of select="smocod"/>'
    , '<xsl:value-of select="nam_smop"/>'
    , '<xsl:value-of select="nam_smok"/>'
    , '<xsl:value-of select="INN"/>'
    , '<xsl:value-of select="Ogrn"/>'
    , '<xsl:value-of select="KPP"/>'
    , '<xsl:value-of select="jurAddress/INDEX_J"/>'
    , '<xsl:value-of select="jurAddress/ADDR_J"/>'
    , '<xsl:value-of select="pstAddress/INDEX_F"/>'
    , '<xsl:value-of select="pstAddress/addr_f"/>'
    , '<xsl:value-of select="OKOPF"/>'
    , '<xsl:value-of select="fam_ruk"/>'
    , '<xsl:value-of select="im_ruk"/>'
    , '<xsl:value-of select="ot_ruk"/>'
    , '<xsl:value-of select="Phone"/>'
    , '<xsl:value-of select="Fax"/>'
    , '<xsl:value-of select="hot_line"/>'
    , '<xsl:value-of select="e_mail"/>'
    , '<xsl:value-of select="WWW"/>'
    , '<xsl:value-of select="licenziy/N_DOC"/>'
    , TO_DATE('<xsl:value-of select="insInclude/d_begin"/>', 'DD.MM.YYYY')
    , <xsl:choose>
      <xsl:when test="insInclude/d_end != '' and string-length(insInclude/d_end) &gt; 0">
        TO_DATE('<xsl:value-of select="insInclude/d_end"/>', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    , TO_DATE('<xsl:value-of select="licenziy/D_TERM"/>', 'DD.MM.YYYY')
    , <xsl:value-of select="ORG" />
    , TO_DATE('<xsl:value-of select="licenziy/D_START"/>', 'DD.MM.YYYY')
    , <xsl:choose>
      <xsl:when test="licenziy/DATE_E != '' and string-length(licenziy/DATE_E) &gt; 0">
        <xsl:text>TO_DATE('</xsl:text>
        <xsl:value-of select="licenziy/DATE_E"/>
        <xsl:text>', 'DD.MM.YYYY')</xsl:text>
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    , <xsl:choose>
      <xsl:when test="insInclude/NAME_E != '' and string-length(insInclude/NAME_E) &gt; 0">
        <xsl:value-of select="insInclude/NAME_E"/>
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    , <xsl:choose>
      <xsl:when test="insInclude/NAL_P != '' and string-length(insInclude/NAL_P) &gt; 0">
        <xsl:value-of select="insInclude/NAL_P"/>
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    , '<xsl:value-of select="insAdvice/YEAR_WORK"/>'
    , TO_DATE('<xsl:value-of select="insAdvice/DUVED"/>', 'DD.MM.YYYY')
    , <xsl:choose>
      <xsl:when test="insAdvice/KOL_ZL != '' and string-length(insAdvice/KOL_ZL) &gt; 0">
        <xsl:value-of select="insAdvice/KOL_ZL"/>
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    , TO_DATE('<xsl:value-of select="D_EDIT"/>', 'DD.MM.YYYY')
    );
    else
    update AKUZ.T_INSURER
    set
    OKATO = '<xsl:value-of select="TF_OKATO"/>'
    , SmoCod = '<xsl:value-of select="smocod"/>'
    , Name = '<xsl:value-of select="nam_smop"/>'
    , ShortName = '<xsl:value-of select="nam_smok"/>'
    , OGRN = '<xsl:value-of select="Ogrn"/>'
    , KPP = '<xsl:value-of select="KPP"/>'
    , IndexLegal = '<xsl:value-of select="jurAddress/INDEX_J"/>'
    , AddressLegal = '<xsl:value-of select="jurAddress/ADDR_J"/>'
    , IndexReal = '<xsl:value-of select="pstAddress/INDEX_F"/>'
    , Address = '<xsl:value-of select="pstAddress/addr_f"/>'
    , OKOPF = '<xsl:value-of select="OKOPF"/>'
    , DirectorLastName = '<xsl:value-of select="fam_ruk"/>'
    , DirectorFirstName = '<xsl:value-of select="im_ruk"/>'
    , DirectorFatherName = '<xsl:value-of select="ot_ruk"/>'
    , Phone = '<xsl:value-of select="Phone"/>'
    , Fax = '<xsl:value-of select="Fax"/>'
    , hot_line = '<xsl:value-of select="hot_line"/>'
    , EMail = '<xsl:value-of select="e_mail"/>'
    , SAIT = '<xsl:value-of select="WWW"/>'
    , N_DOC = '<xsl:value-of select="licenziy/N_DOC"/>'
    , Date_b = TO_DATE('<xsl:value-of select="insInclude/d_begin" />', 'DD.MM.YYYY')
    , Date_e = <xsl:choose>
      <xsl:when test="insInclude/d_end != '' and string-length(insInclude/d_end) &gt; 0">
        TO_DATE('<xsl:value-of select="insInclude/d_end"/>', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    , D_TERM = TO_DATE('<xsl:value-of select="licenziy/D_TERM"/>', 'DD.MM.YYYY')
    , ORG = <xsl:value-of select="ORG" />
    , Gk_start = TO_DATE('<xsl:value-of select="licenziy/D_START"/>', 'DD.MM.YYYY')
    , Gk_end = TO_DATE('<xsl:value-of select="licenziy/DATE_E"/>', 'DD.MM.YYYY')
    , NAME_E = <xsl:choose>
      <xsl:when test="insInclude/NAME_E != '' and string-length(insInclude/NAME_E) &gt; 0">
        <xsl:value-of select="insInclude/NAME_E"/>
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    , NAL_P = <xsl:choose>
      <xsl:when test="insInclude/NAL_P != '' and string-length(insInclude/NAL_P) &gt; 0">
        <xsl:value-of select="insInclude/NAL_P"/>
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    , YEAR_WORK = '<xsl:value-of select="insAdvice/YEAR_WORK"/>'
    , DUVED = TO_DATE('<xsl:value-of select="insAdvice/DUVED"/>', 'DD.MM.YYYY')
    , KOL_ZL = <xsl:choose>
      <xsl:when test="insAdvice/KOL_ZL != '' and string-length(insAdvice/KOL_ZL) &gt; 0">
        <xsl:value-of select="insAdvice/KOL_ZL"/>
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>
    , Date_red = TO_DATE('<xsl:value-of select="D_EDIT"/>', 'DD.MM.YYYY')
    where SmoCod = '<xsl:value-of select="smocod"/>' and Gk_start = TO_DATE('<xsl:value-of select="insInclude/d_begin"/>', 'DD.MM.YYYY');
    end if;
    end;
    $$;
  </xsl:template>

  <!-- Шаблон загрузки настроек сводной информации ВИМИС регистра -->
  <xsl:template match="VimisConsolidationSettings/Settings">
    <xsl:variable name="priorityPath">
      <xsl:call-template name="replace">
        <xsl:with-param name="text">
          <xsl:value-of select="@priorityXpath" />
        </xsl:with-param>
        <xsl:with-param name="replace">'</xsl:with-param>
        <xsl:with-param name="by">''</xsl:with-param>
      </xsl:call-template>
    </xsl:variable>
    <xsl:variable name="priorityLexem">
      <xsl:call-template name="replace">
        <xsl:with-param name="text">
          <xsl:value-of select="@priorityLexem" />
        </xsl:with-param>
        <xsl:with-param name="replace">'</xsl:with-param>
        <xsl:with-param name="by">''</xsl:with-param>
      </xsl:call-template>
    </xsl:variable>
    <xsl:variable name="xsltTemplate">
      <xsl:call-template name="replace">
        <xsl:with-param name="text">
          <xsl:value-of select="@template" />
        </xsl:with-param>
        <xsl:with-param name="replace">'</xsl:with-param>
        <xsl:with-param name="by">''</xsl:with-param>
      </xsl:call-template>
    </xsl:variable>

    INSERT INTO AKUZ.S_VIMIS_CONSOLIDATION_GroupType(value) select '<xsl:value-of select="@group" />' where not exists (select * from AKUZ.S_VIMIS_CONSOLIDATION_GroupType where value = '<xsl:value-of select="@group" />');

    INSERT INTO AKUZ.T_VIMIS_CONSOLIDATION_SETTING
    (VimisConsolidationSettingID
    ,Entities
    ,GroupType
    ,PriorityXpath
    ,PriorityLexem
    ,XsltTemplate)
    SELECT
    gen_random_uuid()--VimisConsolidationSettingID, uniqueidentifier,
    ,(select e.EntityID from VCLib.T_ENTITIES e where e."schema" || '.' || e.EntName = '<xsl:value-of select="@entity" />' limit 1)--Entities, int,
    ,(select v.id from AKUZ.S_VIMIS_CONSOLIDATION_GroupType v where v.value = '<xsl:value-of select="@group" />' limit 1)--GroupType, int,
    ,<xsl:choose>
      <xsl:when test="@priorityXpath!=''">
        '<xsl:value-of select="$priorityPath" />'
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>--PriorityXpath, varchar(1000),
    ,<xsl:choose>
      <xsl:when test="@priorityLexem!=''">
        '<xsl:value-of select="$priorityLexem" />'
      </xsl:when>
      <xsl:otherwise>NULL</xsl:otherwise>
    </xsl:choose>--PriorityLexem, varchar(1000),
    ,'<xsl:value-of select="$xsltTemplate" />'--XsltTemplate, varchar(max),)
    where not exists
    (
    select *
    from AKUZ.T_VIMIS_CONSOLIDATION_SETTING cs
    join VCLib.T_ENTITIES e on e.EntityID = cs.Entities
    join AKUZ.S_VIMIS_CONSOLIDATION_GroupType v on v.id = cs.GroupType
    where e."schema" || '.' || e.EntName = '<xsl:value-of select="@entity" />'
    and
    v.value = '<xsl:value-of select="@group" />'
    and
    cs.PriorityXpath <xsl:choose>
      <xsl:when test="@priorityXpath!=''">
        = '<xsl:value-of select="$priorityPath" />'
      </xsl:when>
      <xsl:otherwise>is NULL</xsl:otherwise>
    </xsl:choose>
    and
    cs.PriorityLexem <xsl:choose>
      <xsl:when test="@priorityLexem!=''">
        = '<xsl:value-of select="$priorityLexem" />'
      </xsl:when>
      <xsl:otherwise>is NULL</xsl:otherwise>
    </xsl:choose>
    );
    update AKUZ.T_VIMIS_CONSOLIDATION_SETTING cs
    set XsltTemplate = '<xsl:value-of select="$xsltTemplate" />'
    from VCLib.T_ENTITIES e, AKUZ.S_VIMIS_CONSOLIDATION_GroupType v
    where e.EntityID = cs.Entities
    and
    v.id = cs.GroupType
    and
    e."schema" || '.' || e.EntName = '<xsl:value-of select="@entity" />'
    and
    v.value = '<xsl:value-of select="@group" />'
    and
    PriorityXpath <xsl:choose>
      <xsl:when test="@priorityXpath!=''">
        = '<xsl:value-of select="$priorityPath" />'
      </xsl:when>
      <xsl:otherwise>is NULL</xsl:otherwise>
    </xsl:choose>
    and
    PriorityLexem <xsl:choose>
      <xsl:when test="@priorityLexem!=''">
        = '<xsl:value-of select="$priorityLexem" />'
      </xsl:when>
      <xsl:otherwise>is NULL</xsl:otherwise>
    </xsl:choose>;
  </xsl:template>

  <xsl:template name="replace">
    <xsl:param name="text"/>
    <xsl:param name="replace"/>
    <xsl:param name="by"/>
    <xsl:choose>
      <xsl:when test="contains($text, $replace)">
        <xsl:value-of select="substring-before($text,$replace)" />
        <xsl:value-of select="$by" />
        <xsl:call-template name="replace">
          <xsl:with-param name="text" select="substring-after($text,$replace)" />
          <xsl:with-param name="replace" select="$replace"/>
          <xsl:with-param name="by" select="$by"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="$text" />
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
</xsl:stylesheet>