<?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()"/>
  
    <!-- F007: F007.xml -->
  <xsl:template match="packet[(zglv[type='Vedom'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="Vedom" />
    </xsl:for-each>
  </xsl:template>
  <xsl:template name="Vedom" >
    INSERT INTO ISZL.T_F007_DEPARTMENT (IDVED, VEDNAME, DATEBEG, DATEEND)
    SELECT 
	  '<xsl:value-of select="IDVED" />'
	, LEFT('<xsl:value-of select="VEDNAME" />', 254)
    , to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where not exists(select * from ISZL.T_F007_DEPARTMENT dk where dk.IDVED = '<xsl:value-of select="IDVED" />');
	
    update ISZL.T_F007_DEPARTMENT
    set VEDNAME = LEFT('<xsl:value-of select="VEDNAME" />', 254)
    , DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , DATEEND = <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where IDVED = '<xsl:value-of select="IDVED" />';
    --PGSQL:GO
</xsl:template>

  <!-- F003_MO: F003.xml -->
  <xsl:template match="packet[(medCompany)]">
    <xsl:for-each select="medCompany">
      <!-- insert or update ISZL.T_F003 -->
      <xsl:call-template name="medCompany" />
    </xsl:for-each>
  </xsl:template>
  <!-- F003 "<medCompany>" template -->
  <xsl:template name="medCompany" >
    INSERT INTO ISZL.T_F003 (TF_OKATO ,MCOD ,NAM_MOP ,NAM_MOK ,INN ,OGRN ,KPP ,INDEX_J ,ADDR_J ,OKOPF ,VEDPRI ,ORG ,FAM_RUK ,IM_RUK ,OT_RUK ,PHONE ,FAX ,E_MAIL ,WWW ,D_BEGIN ,D_END ,D_EDIT ,Holidays ,IsActive,MP)
    SELECT
      LEFT('<xsl:value-of select="tf_okato" />', 5)
    , '<xsl:value-of select="mcod" />'
    , LEFT('<xsl:value-of select="nam_mop" />', 250)
    , LEFT('<xsl:value-of select="nam_mok" />', 250)
    , LEFT('<xsl:value-of select="inn" />', 12)
    , LEFT('<xsl:value-of select="Ogrn" />', 15)
    , LEFT('<xsl:value-of select="KPP" />', 9)
    , LEFT('<xsl:value-of select="jurAddress/index_j" />', 6)
    , LEFT('<xsl:value-of select="jurAddress/addr_j" />', 254)
    , (select o005.O005OkopfID from ISZL.T_O005_OKOPF o005 where KOD = '<xsl:value-of select="okopf" />'  limit 1)
    , '<xsl:value-of select="vedpri" />'
    , LEFT('<xsl:value-of select="org" />', 3)
    , LEFT('<xsl:value-of select="fam_ruk" />', 40)
    , LEFT('<xsl:value-of select="im_ruk" />', 40)
    , LEFT('<xsl:value-of select="ot_ruk" />', 40)
    , LEFT('<xsl:value-of select="phone" />', 40)
    , LEFT('<xsl:value-of select="fax" />', 40)
    , LEFT('<xsl:value-of select="e_mail" />', 50)
    , LEFT('<xsl:value-of select="www" />', 50)
    , to_timestamp('<xsl:value-of select="medInclude/d_begin" />', 'DD.MM.YYYY')
    , <xsl:choose>
      <xsl:when test="d_end != ''">
	    to_timestamp('<xsl:value-of select="d_end" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>
        NULL
      </xsl:otherwise>
    </xsl:choose>
	,to_timestamp('<xsl:value-of select="d_edit" />', 'DD.MM.YYYY')
    , '64'
    , '1'
    , ''
	where not exists(select * from ISZL.T_F003 s where s.MCOD = '<xsl:value-of select="mcod" />');

    update ISZL.T_F003
    set TF_OKATO = LEFT('<xsl:value-of select="tf_okato" />', 5)
    , NAM_MOP = LEFT('<xsl:value-of select="nam_mop" />', 250)
    , NAM_MOK = LEFT('<xsl:value-of select="nam_mok" />', 250)
    , INN = LEFT('<xsl:value-of select="inn" />', 12)
    , OGRN = LEFT('<xsl:value-of select="Ogrn" />', 15)
    , KPP = LEFT('<xsl:value-of select="KPP" />', 9)
    , INDEX_J = LEFT('<xsl:value-of select="jurAddress/index_j" />', 6)
    , ADDR_J = LEFT('<xsl:value-of select="jurAddress/addr_j" />', 254)
    , OKOPF = (select o005.O005OkopfID from ISZL.T_O005_OKOPF o005 where KOD = '<xsl:value-of select="okopf" />'  limit 1)
    , VEDPRI = '<xsl:value-of select="vedpri" />'
    , ORG = LEFT('<xsl:value-of select="org" />', 3)
    , FAM_RUK = LEFT('<xsl:value-of select="fam_ruk" />', 40)
    , IM_RUK = LEFT('<xsl:value-of select="im_ruk" />', 40)
    , OT_RUK = LEFT('<xsl:value-of select="ot_ruk" />', 40)
    , PHONE = LEFT('<xsl:value-of select="phone" />', 40)
    , FAX = LEFT('<xsl:value-of select="fax" />', 40)
    , E_MAIL = LEFT('<xsl:value-of select="e_mail" />', 50)
    , WWW = LEFT('<xsl:value-of select="www" />', 50)
    , D_BEGIN = to_timestamp('<xsl:value-of select="medInclude/d_begin" />', 'DD.MM.YYYY')
    , D_END = <xsl:choose>
      <xsl:when test="d_end != ''">
        to_timestamp('<xsl:value-of select="d_end" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>
        NULL
      </xsl:otherwise>
    </xsl:choose>
    , D_EDIT = to_timestamp('<xsl:value-of select="d_edit" />', 'DD.MM.YYYY')
    , Holidays = '64'
    , IsActive = '1'
    where MCOD = '<xsl:value-of select="mcod" />';

    update ISZL.T_F003
     set IsActive = case when D_END is NULL or D_END > CURRENT_TIMESTAMP then true else false end
    where MCOD = '<xsl:value-of select="mcod" />';
 
    <!--Creation of organization with MO type-->
    INSERT INTO ISZL.T_ORGANIZATION (ORGANIZATIONID, NAME, TYPE_, CODE)
    SELECT uuid_generate_v4()
    , LEFT('<xsl:value-of select="nam_mok" />', 250)
    , (select entityid from vclib.t_entities where entname = 'MO')
    , LEFT('<xsl:value-of select="mcod" />', 6)
   where not exists(select * from ISZL.T_ORGANIZATION where code = LEFT('<xsl:value-of select="mcod" />', 6) and type_ = (select entityid from vclib.t_entities where entname = 'MO')); 
   
   	update ISZL.T_ORGANIZATION
    set NAME = LEFT('<xsl:value-of select="nam_mok" />', 250)
    where CODE = LEFT('<xsl:value-of select="mcod" />', 6);
    --PGSQL:GO
	
  </xsl:template>

 <!-- F002_SMO: F002.xml -->
  <xsl:template match="packet[(insCompany)]">
    <xsl:for-each select="insCompany">
      <!-- insert or update F002_SMO -->
      <xsl:call-template name="insCompany" />
    </xsl:for-each>
  </xsl:template>
  <!-- F002 "<insCompany>" template -->
  <xsl:template name="insCompany" >
    <xsl:variable name="d_start">
      <xsl:for-each select="licenziy">
        <xsl:sort select="d_start" order="descending" />
        <xsl:if test="position() = 1">
          <xsl:value-of select="d_start"/>
        </xsl:if>
      </xsl:for-each>
    </xsl:variable>
    <xsl:variable name="n_doc">
      <xsl:for-each select="licenziy">
        <xsl:sort select="d_start" order="descending" />
        <xsl:if test="position() = 1">
          <xsl:value-of select="n_doc"/>
        </xsl:if>
      </xsl:for-each>
    </xsl:variable>
    <xsl:variable name="date_e">
      <xsl:for-each select="licenziy">
        <xsl:sort select="d_start" order="descending" />
        <xsl:if test="position() = 1">
          <xsl:value-of select="date_e"/>
        </xsl:if>
      </xsl:for-each>
    </xsl:variable>
    <xsl:variable name="d_begin">
      <xsl:for-each select="insInclude">
        <xsl:sort select="d_begin" order="descending" />
        <xsl:if test="position() = 1">
          <xsl:value-of select="d_begin"/>
        </xsl:if>
      </xsl:for-each>
    </xsl:variable>
    <xsl:variable name="d_end">
      <xsl:for-each select="insInclude">
        <xsl:sort select="d_begin" order="descending" />
        <xsl:if test="position() = 1">
          <xsl:value-of select="d_end"/>
        </xsl:if>
      </xsl:for-each>
    </xsl:variable>
    <xsl:variable name="name_e">
      <xsl:for-each select="insInclude">
        <xsl:sort select="d_begin" order="descending" />
        <xsl:if test="position() = 1">
          <xsl:value-of select="name_e"/>
        </xsl:if>
      </xsl:for-each>
    </xsl:variable>
    <xsl:variable name="DUVED">
      <xsl:for-each select="insAdvice">
        <xsl:sort select="DUVED" order="descending" />
        <xsl:if test="position() = 1">
          <xsl:value-of select="DUVED"/>
        </xsl:if>
      </xsl:for-each>
    </xsl:variable>
    <xsl:variable name="Nal_p">
      <xsl:for-each select="insInclude">
        <xsl:sort select="d_begin" order="descending" />
        <xsl:if test="position() = 1">
          <xsl:value-of select="Nal_p"/>
        </xsl:if>
      </xsl:for-each>
    </xsl:variable>
    <xsl:variable name="kol_zl">
      <xsl:for-each select="insAdvice">
        <xsl:sort select="DUVED" order="descending" />
        <xsl:if test="position() = 1">
          <xsl:value-of select="kol_zl"/>
        </xsl:if>
      </xsl:for-each>
    </xsl:variable>
     
    INSERT INTO ISZL.T_F002_SMO
    (TF_OKATO ,SMOCOD ,NAM_SMOP ,NAM_SMOK ,INN ,OGRN ,KPP ,INDEX_J ,ADDR_J ,OKOPF ,ORG ,FAM_RUK ,IM_RUK ,OT_RUK ,PHONE ,FAX ,E_MAIL ,N_DOC ,D_START ,DATA_E ,WWW ,D_BEGIN ,
	D_END ,NAME_E ,DUVED ,D_EDIT ,INDEX_F ,ADDR_F ,NAL_P ,KOL_ZL ,IsActive ,Local)
    SELECT
    LEFT('<xsl:value-of select="tf_okato" />', 5)
    , LEFT('<xsl:value-of select="smocod" />', 5)
    , LEFT('<xsl:value-of select="nam_smop" />', 254)
    , LEFT('<xsl:value-of select="nam_smok" />', 250)
    , LEFT('<xsl:value-of select="inn" />', 12)
    , LEFT('<xsl:value-of select="Ogrn" />', 15)
    , LEFT('<xsl:value-of select="KPP" />', 9)
    , LEFT('<xsl:value-of select="jurAddress/index_j" />', 6)
    , LEFT('<xsl:value-of select="jurAddress/addr_j" />', 254)
    , (select o005.O005OkopfID from ISZL.T_O005_OKOPF o005 where KOD = '<xsl:value-of select="okopf" />' limit 1)
    , '<xsl:value-of select="org" />'
    , LEFT('<xsl:value-of select="fam_ruk" />', 40)
    , LEFT('<xsl:value-of select="im_ruk" />', 40)
    , LEFT('<xsl:value-of select="ot_ruk" />', 40)
    , LEFT('<xsl:value-of select="phone" />', 40)
    , LEFT('<xsl:value-of select="fax" />', 40)
    , LEFT('<xsl:value-of select="e_mail" />', 50)
    , LEFT('<xsl:value-of select="$n_doc" />', 20)
    , to_timestamp('<xsl:value-of select="$d_start" />', 'DD.MM.YYYY')
    , <xsl:choose>
      <xsl:when test="$date_e != ''">
        to_timestamp('<xsl:value-of select="$date_e" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>
        NULL
      </xsl:otherwise>
    </xsl:choose>
    , LEFT('<xsl:value-of select="www" />', 100)
    , to_timestamp('<xsl:value-of select="$d_begin" />', 'DD.MM.YYYY')
    , <xsl:choose>
      <xsl:when test="$d_end != ''">
        to_timestamp('<xsl:value-of select="$d_end" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>
        NULL
      </xsl:otherwise>
    </xsl:choose>
    , LEFT('<xsl:value-of select="$name_e" />', 10)
    , <xsl:choose>
      <xsl:when test="$DUVED != ''">
       to_timestamp('<xsl:value-of select="$DUVED" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>
        NULL
      </xsl:otherwise>
    </xsl:choose>
    , <xsl:choose>
      <xsl:when test="d_edit != ''">
        to_timestamp('<xsl:value-of select="d_edit" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>
        NULL
      </xsl:otherwise>
    </xsl:choose>
    , LEFT('<xsl:value-of select="pstAddress/index_f" />', 254)
    , LEFT('<xsl:value-of select="pstAddress/addr_f" />', 254)
    , LEFT('<xsl:value-of select="$Nal_p" />', 1)
    , <xsl:choose>
      <xsl:when test="$kol_zl != ''">
        <xsl:value-of select="$kol_zl" />
      </xsl:when>
      <xsl:otherwise>
        NULL
      </xsl:otherwise>
    </xsl:choose>
    , <xsl:if test="$d_end != ''">
      '0'
    </xsl:if><xsl:if test="$d_end = ''">'1'</xsl:if>
    , '0'
   where not exists(select * from ISZL.T_F002_SMO s where s.SMOCOD = '<xsl:value-of select="smocod" />');
   
    UPDATE ISZL.T_F002_SMO
    set
      TF_OKATO = LEFT('<xsl:value-of select="tf_okato" />', 5)
    , NAM_SMOP = LEFT('<xsl:value-of select="nam_smop" />', 254)
    , NAM_SMOK = LEFT('<xsl:value-of select="nam_smok" />', 250)
    , INN = LEFT('<xsl:value-of select="inn" />', 12)
    , OGRN = LEFT('<xsl:value-of select="Ogrn" />', 15)
    , KPP = LEFT('<xsl:value-of select="KPP" />', 9)
    , INDEX_J = LEFT('<xsl:value-of select="jurAddress/index_j" />', 6)
    , ADDR_J = LEFT('<xsl:value-of select="jurAddress/addr_j" />', 254)
    , OKOPF = (select o005.O005OkopfID from ISZL.T_O005_OKOPF o005 where KOD = '<xsl:value-of select="okopf" />' limit 1)
    , ORG = '<xsl:value-of select="org" />'
    , FAM_RUK = LEFT('<xsl:value-of select="fam_ruk" />', 40)
    , IM_RUK = LEFT('<xsl:value-of select="im_ruk" />', 40)
    , OT_RUK = LEFT('<xsl:value-of select="ot_ruk" />', 40)
    , PHONE = LEFT('<xsl:value-of select="phone" />', 40)
    , FAX = LEFT('<xsl:value-of select="fax" />', 40)
    , E_MAIL = LEFT('<xsl:value-of select="e_mail" />', 50)
    , N_DOC = LEFT('<xsl:value-of select="$n_doc" />', 20)
    , D_START = to_timestamp('<xsl:value-of select="$d_start" />', 'DD.MM.YYYY')
    , DATA_E = <xsl:choose>
      <xsl:when test="$date_e != ''">
        to_timestamp('<xsl:value-of select="$date_e" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>
        NULL
      </xsl:otherwise>
    </xsl:choose>
    , WWW = LEFT('<xsl:value-of select="www" />', 100)
    , D_BEGIN = to_timestamp('<xsl:value-of select="$d_begin" />','DD.MM.YYYY')
    , D_END = <xsl:choose>
      <xsl:when test="$d_end != ''">
        to_timestamp('<xsl:value-of select="$d_end" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>
        NULL
      </xsl:otherwise>
    </xsl:choose>
    , NAME_E = LEFT('<xsl:value-of select="$name_e" />', 10)
    , DUVED = <xsl:choose>
      <xsl:when test="$DUVED != ''">
        to_timestamp('<xsl:value-of select="$DUVED" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>
        NULL
      </xsl:otherwise>
    </xsl:choose>
    , D_EDIT = <xsl:choose>
      <xsl:when test="d_edit != ''">
        to_timestamp('<xsl:value-of select="d_edit" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>
        NULL
      </xsl:otherwise>
    </xsl:choose>
    , INDEX_F = LEFT('<xsl:value-of select="pstAddress/index_f" />', 254)
    , ADDR_F = LEFT('<xsl:value-of select="pstAddress/addr_f" />', 254)
    , NAL_P = LEFT('<xsl:value-of select="$Nal_p" />', 1)
    , KOL_ZL = <xsl:choose>
      <xsl:when test="$kol_zl != ''">
        <xsl:value-of select="$kol_zl" />
      </xsl:when>
      <xsl:otherwise>
        NULL
      </xsl:otherwise>
    </xsl:choose>
    where SMOCOD = LEFT('<xsl:value-of select="smocod" />', 5);
    
    <!--Creation of organization with SMO type-->
    INSERT INTO ISZL.T_ORGANIZATION (ORGANIZATIONID, NAME, TYPE_, CODE)
    SELECT uuid_generate_v4()
    , LEFT('<xsl:value-of select="nam_smok" />', 250)
    , (select entityid from vclib.t_entities where entname = 'SMO')
    , LEFT('<xsl:value-of select="smocod" />', 5)
   where not exists(select * from ISZL.T_ORGANIZATION where code = LEFT('<xsl:value-of select="smocod"/>', 5) and type_ = (select entityid from vclib.t_entities where entname = 'SMO'));
   
    update ISZL.T_ORGANIZATION
    set NAME = LEFT('<xsl:value-of select="nam_smok" />', 250)
    where CODE = LEFT('<xsl:value-of select="smocod" />', 5);
    --PGSQL:GO
  </xsl:template>

  <!-- F001_TFOMS: F001.xml -->
  <xsl:template match="packet[(TFOMS)]">
    <xsl:for-each select="TFOMS">
      <!-- insert or update F001_SMO -->
      <xsl:call-template name="TFOMS" />
    </xsl:for-each>
  </xsl:template>
  <!--F001 TFOMS template-->
  <xsl:template name="TFOMS" >
    INSERT INTO ISZL.T_F001_TFOMS (TF_KOD, TF_OKATO, TF_OGRN, NAME_TFP, NAME_TFK, INDEX_, ADDRESS_, FAM_DIR, IM_DIR, OT_DIR, PHONE, FAX, E_MAIL, KF_TF, WWW, BIK, INN, KPP, KBK, OKTMO, L_NAIM, L_B, L_RS, T_NAIM, T_B, T_RS, D_EDIT, D_END)
    SELECT
    LEFT('<xsl:value-of select="tf_kod" />', 2)
    , LEFT('<xsl:value-of select="tf_okato" />', 5)
    , LEFT('<xsl:value-of select="tf_ogrn" />', 13)
    , LEFT('<xsl:value-of select="name_tfp" />', 250)
    , LEFT('<xsl:value-of select="name_tfk" />', 250)
    , LEFT('<xsl:value-of select="index" />', 6)
    , LEFT('<xsl:value-of select="address" />', 250)
    , LEFT('<xsl:value-of select="fam_dir" />', 40)
    , LEFT('<xsl:value-of select="im_dir" />', 40)
    , LEFT('<xsl:value-of select="ot_dir" />', 40)
    , LEFT('<xsl:value-of select="phone" />', 40)
    , LEFT('<xsl:value-of select="fax" />', 40)
    , LEFT('<xsl:value-of select="e_mail" />', 50)
    , '<xsl:value-of select="kf_tf" />'
    , LEFT('<xsl:value-of select="www" />', 100)
    , LEFT('<xsl:value-of select="bic" />', 11)
    , LEFT('<xsl:value-of select="inn" />', 13)
    , LEFT('<xsl:value-of select="kpp" />', 10)
    , '<xsl:value-of select="kbk" />'
    , LEFT('<xsl:value-of select="oktmo" />', 13)
    , LEFT('<xsl:value-of select="L_NAIM" />', 250)
    , LEFT('<xsl:value-of select="L_B" />', 100)
    , LEFT('<xsl:value-of select="L_RS" />', 20)
    , LEFT('<xsl:value-of select="T_NAIM" />', 250)
    , LEFT('<xsl:value-of select="T_B" />', 100)
    , LEFT('<xsl:value-of select="T_RS" />', 20)
    , <xsl:choose>
      <xsl:when test="d_edit != ''">
	    to_timestamp('<xsl:value-of select="d_edit" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>
        NULL
      </xsl:otherwise>
      </xsl:choose>
    , <xsl:choose>
      <xsl:when test="d_end != ''">
	    to_timestamp('<xsl:value-of select="d_end" />', 'DD.MM.YYYY')
      </xsl:when>
      <xsl:otherwise>
        NULL
      </xsl:otherwise>
    </xsl:choose>
   where not exists(select * from ISZL.T_F001_TFOMS s where s.tf_kod = '<xsl:value-of select="tf_kod" />');
    
    <!--Updating current not-null values-->
    update ISZL.T_F001_TFOMS
    set TF_OKATO = LEFT('<xsl:value-of select="tf_okato" />', 5)
    , TF_OGRN = LEFT('<xsl:value-of select="tf_ogrn" />', 13)
    , NAME_TFP = LEFT('<xsl:value-of select="name_tfp" />', 250)
    , NAME_TFK = LEFT('<xsl:value-of select="name_tfk" />', 250)
    , ADDRESS_ = LEFT('<xsl:value-of select="address" />', 250)
   where TF_KOD = LEFT('<xsl:value-of select="tf_kod" />', 2);
   
    <!--Creation of organization with FOND type-->
    INSERT INTO ISZL.T_ORGANIZATION (ORGANIZATIONID, NAME, TYPE_, CODE)
    SELECT uuid_generate_v4()
    , LEFT('<xsl:value-of select="name_tfk" />', 250)
    , (select entityid from vclib.t_entities where entname = 'FOND')
    , LEFT('<xsl:value-of select="tf_kod" />', 2)
   where not exists(select * from ISZL.T_ORGANIZATION where code = LEFT('<xsl:value-of select="tf_kod"/>', 2) and type_ = (select entityid from vclib.t_entities where entname = 'FOND'));
    --PGSQL:GO
  </xsl:template>
  
  <!-- F015_FEDERAL_DISTRICT: F015.xml -->
  <xsl:template match="PACKET[(zglv[type='Okrug'])]">
    <xsl:for-each select="ZAP">
      <xsl:call-template name="Okrug" />
    </xsl:for-each>
  </xsl:template>
  <!-- F015 "<zap>" template -->
  <xsl:template name="Okrug" >
    
    INSERT INTO ISZL.T_F015_FEDERAL_DISTRICT (KOD_OK, OKRNAME, DATEBEG, DATEEND)
    SELECT '<xsl:value-of select="KOD_OK" />'
    , LEFT('<xsl:value-of select="OKRNAME" />', 255)
    , to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where not exists(select * from ISZL.T_F015_FEDERAL_DISTRICT f where f.KOD_OK = '<xsl:value-of select="KOD_OK" />');
	
    update ISZL.T_F015_FEDERAL_DISTRICT
    set OKRNAME = LEFT('<xsl:value-of select="OKRNAME" />', 255)
    , DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , DATEEND = <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where KOD_OK = '<xsl:value-of select="KOD_OK" />';
    --PGSQL:GO
  </xsl:template>


  <!-- F010_SUBJECT: F010.xml -->
  <xsl:template match="packet[(zglv[type='Subekti'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="Subekti" />
    </xsl:for-each>
  </xsl:template>
  <!-- F010 "<zap>" template -->
  <xsl:template name="Subekti" >
    INSERT INTO ISZL.T_F010_SUBJECT (subjectid, KOD_TF, KOD_OKATO, SUBNAME, OKRUG, DATEBEG, DATEEND)
    SELECT 
        '<xsl:value-of select="KOD_TF" />'
      , '<xsl:value-of select="KOD_TF" />'
      , LEFT('<xsl:value-of select="KOD_OKATO" />', 256)
      , LEFT('<xsl:value-of select="SUBNAME" />', 256)
      , '<xsl:value-of select="OKRUG" />'
      , to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
      , <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
      </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where not exists(select * from ISZL.T_F010_SUBJECT s where s.KOD_OKATO = '<xsl:value-of select="KOD_OKATO" />');

    update ISZL.T_F010_SUBJECT
    set KOD_OKATO = LEFT('<xsl:value-of select="KOD_OKATO" />', 256)
      , SUBNAME = LEFT('<xsl:value-of select="SUBNAME" />', 256)
      , OKRUG = '<xsl:value-of select="OKRUG" />'
      , DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
      , DATEEND = <xsl:if test="DATEEND != ''">
                    to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
                  </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where KOD_TF = '<xsl:value-of select="KOD_TF" />';
    --PGSQL:GO
  </xsl:template>

  <!-- V001_NOMENCLATURE: V001.xml -->
  <xsl:template match="PACKET[(type='Nom_Serv')]">
    <xsl:for-each select="PART/SERVICES">
      <!-- insert or update -->
      <xsl:call-template name="Nom_Serv" />
    </xsl:for-each>
  </xsl:template>
  <xsl:template name="Nom_Serv" >
    INSERT INTO ISZL.T_V001_NOMENCLATURE (NomenclatureID, Code, Name, DATEBEG, DATEEND, IsOperation)
    SELECT 
	  gen_random_uuid()
    , LEFT('<xsl:value-of select="SERVICE_CODE" />', 15)
    , LEFT('<xsl:value-of select="SERVICE_NAME" />', 255)
    , to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
      <xsl:if test="DATEEND != ''">
    , to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    , '<xsl:value-of select="NEW_NOM" />'
    where not exists(select * from ISZL.T_V001_NOMENCLATURE p where p.Code = '<xsl:value-of select="SERVICE_CODE" />');
	
    update ISZL.T_V001_NOMENCLATURE
    set Name = LEFT('<xsl:value-of select="SERVICE_NAME" />', 255)
    , DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , DATEEND = <xsl:if test="DATEEND != ''">
     to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
	, IsOperation = '<xsl:value-of select="NEW_NOM" />'
    where Code = '<xsl:value-of select="IDRMP" />';
    --PGSQL:GO
  </xsl:template>

  <!-- V002_PROFILE: V002.xml -->
  <xsl:template match="packet[(zglv[type='ProfOt'])]">
    <xsl:for-each select="zap">
      <!-- insert or update -->
      <xsl:call-template name="ProfOtZap" />
    </xsl:for-each>
  </xsl:template>
  <xsl:template name="ProfOtZap" >
    INSERT INTO ISZL.T_V002_PROFILE (Id, Name, DateBegin, DateEnd)
    SELECT '<xsl:value-of select="IDPR" />'
    , LEFT('<xsl:value-of select="PRNAME" />', 256)
    , to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where not exists(select * from ISZL.T_V002_PROFILE p where p.Id = '<xsl:value-of select="IDPR" />');
	
    update ISZL.T_V002_PROFILE
    set Name = LEFT('<xsl:value-of select="PRNAME" />', 256)
    , DateBegin = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , DateEnd = <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where Id = '<xsl:value-of select="IDPR" />';
    --PGSQL:GO
    
    INSERT INTO ISZL.T_STRUCTURE_BED (Code, CodeMz, Name, DateBegin, DateEnd, StructureBedType)
    SELECT '<xsl:value-of select="IDPR" />'
    , LEFT('<xsl:value-of select="IDPR" />', 3)
    , LEFT('<xsl:value-of select="PRNAME" />', 1000)
    , to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    , '0'
	 where not exists(select * from ISZL.T_STRUCTURE_BED p where p.Code = '<xsl:value-of select="IDPR" />');
	 
    update ISZL.T_STRUCTURE_BED
    set Name = LEFT('<xsl:value-of select="PRNAME" />', 1000)
    , CodeMz = LEFT('<xsl:value-of select="IDPR" />', 3)
    , DateBegin = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , DateEnd = <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where Code = '<xsl:value-of select="IDPR" />';
    --PGSQL:GO
  </xsl:template>

  <!-- V008_MEDICAL_TYPE: V008.xml -->
  <xsl:template match="packet[(zglv[type='VidMp'])]">
    <xsl:for-each select="zap">
      <!-- insert or update -->
      <xsl:call-template name="VidMp" />
    </xsl:for-each>
  </xsl:template>
  <xsl:template name="VidMp" >
    INSERT INTO ISZL.T_V008_MEDICAL_TYPE (IDVMP, VMPNAME, DATEBEG, DATEEND)
    SELECT '<xsl:value-of select="IDVMP" />'
    , LEFT('<xsl:value-of select="VMPNAME" />', 255)
    , to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where not exists(select * from ISZL.T_V008_MEDICAL_TYPE mt where mt.IDVMP = '<xsl:value-of select="IDVMP" />');
	
    update ISZL.T_V008_MEDICAL_TYPE
    set VMPNAME = LEFT('<xsl:value-of select="VMPNAME" />', 255)
    , DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , DATEEND = <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where IDVMP = '<xsl:value-of select="IDVMP" />';
    --PGSQL:GO
  </xsl:template>

   <!-- V009_RESULT: V009.xml -->
  <xsl:template match="packet[(zglv[type='Rezult'])]">
    <xsl:for-each select="zap">
      <!-- insert or update-->
      <xsl:call-template name="ResultZap" />
    </xsl:for-each>
  </xsl:template>
  <xsl:template name="ResultZap" >
    INSERT INTO ISZL.T_V009_RESULT (IDRMP, RMPNAME, DL_USLOV, DATEBEG, DATEEND)
    SELECT '<xsl:value-of select="IDRMP" />'
    , LEFT('<xsl:value-of select="RMPNAME" />', 256)
	, '<xsl:value-of select="DL_USLOV" />'
    , to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where not exists(select * from ISZL.T_V009_RESULT p where p.IDRMP = '<xsl:value-of select="IDRMP" />');
	
    update ISZL.T_V009_RESULT
    set RMPNAME = LEFT('<xsl:value-of select="RMPNAME" />', 256)
	, DL_USLOV = '<xsl:value-of select="DL_USLOV" />'
    , DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , DATEEND = <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where IDRMP = '<xsl:value-of select="IDRMP" />';
    --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>
  <xsl:template name="MedSpecZap" >
    INSERT INTO ISZL.T_V021_SPECIALITY (Id, Name, DateBegin, DateEnd)
    SELECT <xsl:value-of select="IDSPEC" />
    , LEFT('<xsl:value-of select="SPECNAME" />', 256)
    , to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
	where not exists(select * from ISZL.T_V021_SPECIALITY s where s.Id = <xsl:value-of select="IDSPEC" />);
	
    update ISZL.T_V021_SPECIALITY
    set Name = LEFT('<xsl:value-of select="SPECNAME" />', 256)
    , DateBegin = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , DateEnd = <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where Id = '<xsl:value-of select="IDSPEC" />';
    --PGSQL:GO
  </xsl:template>
  
  <!-- V024: V024.xml -->
  <xsl:template match="packet[(zglv[type='DopKr'])]">
    <xsl:for-each select="zap">
      <!-- insert or update -->
      <xsl:call-template name="DopKr" />
    </xsl:for-each>
  </xsl:template>
  <xsl:template name="DopKr" >
    INSERT INTO ISZL.T_V024 (IDDKK, DKKNAME, DATEBEG, DATEEND)
    SELECT 
	  '<xsl:value-of select="IDDKK" />'
	, LEFT('<xsl:value-of select="DKKNAME" />', 255)
    , to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where not exists(select * from ISZL.T_V024 dk where dk.IDDKK = '<xsl:value-of select="IDDKK" />');
	
    update ISZL.T_V024
    set DKKNAME = LEFT('<xsl:value-of select="DKKNAME" />', 255)
    , DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , DATEEND = <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where IDDKK = '<xsl:value-of select="IDDKK" />';
    --PGSQL:GO
  </xsl:template>


  <!-- N001_OnkPrOt: N001.xml -->
  <xsl:template match="packet[(zglv[type='OnkPrOt'])]">
    <xsl:for-each select="zap">
      <!-- insert or update -->
      <xsl:call-template name="OnkPrOt" />
    </xsl:for-each>
  </xsl:template>
  <xsl:template name="OnkPrOt" >
    INSERT INTO ISZL.T_N001_OnkPrOt (ID_PrOt, PrOt_NAME, DATEBEG, DATEEND,N001ID)
    SELECT '<xsl:value-of select="ID_PrOt" />'
	, LEFT('<xsl:value-of select="PrOt_NAME" />', 255)
    , to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
	, gen_random_uuid()
     where not exists(select * from ISZL.T_N001_OnkPrOt opo where opo.ID_PrOt = '<xsl:value-of select="ID_PrOt" />');

    update ISZL.T_N001_OnkPrOt
    set PrOt_NAME = LEFT('<xsl:value-of select="PrOt_NAME" />', 255)
    , DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , DATEEND = <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where ID_PrOt = '<xsl:value-of select="ID_PrOt" />';
    --PGSQL:GO
  </xsl:template>

  <!-- N002_OnkStad: N002.xml -->
  <xsl:template match="packet[(zglv[type='OnkStad'])]">
    <xsl:for-each select="zap">
      <!-- insert or update-->
      <xsl:call-template name="OnkStad" />
    </xsl:for-each>
  </xsl:template>
  <xsl:template name="OnkStad" >
    INSERT INTO ISZL.T_N002_OnkStad (ID_St, DS_St,KOD_St, DATEBEG, DATEEND, N002ID)
      SELECT <xsl:value-of select="ID_St" />
	, LEFT('<xsl:value-of select="DS_St" />', 5)
    , LEFT('<xsl:value-of select="KOD_St" />', 5)
    , to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
	, gen_random_uuid()
    where not exists(select * from ISZL.T_N002_OnkStad os where os.ID_St = <xsl:value-of select="ID_St" />);
	
    update ISZL.T_N002_OnkStad
    set DS_St = LEFT('<xsl:value-of select="DS_St" />', 5)
	, KOD_St = LEFT('<xsl:value-of select="KOD_St" />', 5)
    , DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , DATEEND = <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where ID_St = <xsl:value-of select="ID_St" />;
    --PGSQL:GO
  </xsl:template>

  <!-- F008_OMS_TYPE: F008.xml -->
  <xsl:template match="packet[(zglv[type='TipOMS'])]">
    <xsl:for-each select="zap">
      <!-- insert or update -->
      <xsl:call-template name="tipOms" />
    </xsl:for-each>
  </xsl:template>
  <!-- F008 "<zap>" template -->
  <xsl:template name="tipOms" >
INSERT INTO ISZL.T_F008_OMS_TYPE
           (IDDOC ,DOCNAME ,DATEBEG ,DATEEND)
SELECT
           <xsl:value-of select="IDDOC" />--IDDOC, int,
          ,LEFT('<xsl:value-of select="DOCNAME" />', 254)--DOCNAME, varchar(254),
          ,to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')--DATEBEG, datetime,
          ,<xsl:choose>
              <xsl:when test="DATEEND!=''">to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')</xsl:when>
              <xsl:otherwise>null</xsl:otherwise>
           </xsl:choose>--DATEEND, datetime,
where not exists(select * from ISZL.T_F008_OMS_TYPE f8 where f8.IDDOC = <xsl:value-of select="IDDOC" />);

update ISZL.T_F008_OMS_TYPE f8
set DOCNAME = LEFT('<xsl:value-of select="DOCNAME" />', 254)
   ,DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
   ,DATEEND = <xsl:choose>
                 <xsl:when test="DATEEND!=''">to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')</xsl:when>
                 <xsl:otherwise>null</xsl:otherwise>
              </xsl:choose>--DATEEND, datetime,
where f8.IDDOC = <xsl:value-of select="IDDOC" />;
--PGSQL:GO
  </xsl:template>

  <!-- F011_DOCUMENT_TYPE: F011.xml -->
  <xsl:template match="packet[(zglv[type='Tipdoc'])]">
    <xsl:for-each select="zap">
      <!-- insert or update -->
      <xsl:call-template name="tipDoc" />
    </xsl:for-each>
  </xsl:template>
  <!-- F011 "<zap>" template -->
  <xsl:template name="tipDoc" >
INSERT INTO ISZL.T_F011_DOCUMENT_TYPE
           (IDDOC ,DOCNAME ,DOCSER ,DOCNUM ,DATEBEG ,DATEEND)
SELECT
           '<xsl:value-of select="IDDoc" />'--IDDOC, varchar(2),
          ,LEFT('<xsl:value-of select="DocName" />', 254)--DOCNAME, varchar(254),
          ,LEFT('<xsl:value-of select="DocSer" />', 10)--DOCSER, varchar(10),
          ,LEFT('<xsl:value-of select="DocNum" />', 20)--DOCNUM, varchar(20),
          ,to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')--DATEBEG, datetime,
          ,<xsl:choose>
              <xsl:when test="DATEEND!=''">to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')</xsl:when>
              <xsl:otherwise>null</xsl:otherwise>
           </xsl:choose>--DATEEND, datetime,
where not exists(select * from ISZL.T_F011_DOCUMENT_TYPE f11 where f11.IDDOC = '<xsl:value-of select="IDDoc" />');

update ISZL.T_F011_DOCUMENT_TYPE f11
set DOCNAME = LEFT('<xsl:value-of select="DocName" />', 254)
   ,DOCSER = LEFT('<xsl:value-of select="DocSer" />', 10)
   ,DOCNUM = LEFT('<xsl:value-of select="DocNum" />', 20)
   ,DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
   ,DATEEND = <xsl:choose>
                 <xsl:when test="DATEEND!=''">to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')</xsl:when>
                 <xsl:otherwise>null</xsl:otherwise>
              </xsl:choose>--DATEEND, datetime,
where f11.IDDOC = '<xsl:value-of select="IDDoc" />';
--PGSQL:GO
  </xsl:template>

  <!-- V017: V017.xml -->
  <xsl:template match="packet[(zglv[type='DispR'])]">
    <xsl:for-each select="zap">
      <xsl:call-template name="DispR" />
    </xsl:for-each>
  </xsl:template>
  <!-- V017 "<zap>" template -->
  <xsl:template name="DispR" >
    INSERT INTO ISZL.T_V017_DISP_RESULT (IDDR, DRNAME, DATEBEG, DATEEND)
    SELECT 
	  <xsl:value-of select="IDDR" />
	, LEFT('<xsl:value-of select="DRNAME" />', 255)
    , to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
   where not exists(select * from ISZL.T_V017_DISP_RESULT d where d.IDDR = <xsl:value-of select="IDDR" />);

    update ISZL.T_V017_DISP_RESULT
    set DRNAME = LEFT('<xsl:value-of select="DRNAME" />', 255)
    , DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , DATEEND = <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where IDDR = <xsl:value-of select="IDDR" />;
    --PGSQL:GO
  </xsl:template>


  <!-- N003_OnkT: N003.xml -->
  <xsl:template match="packet[(zglv[type='OnkT'])]">
    <xsl:for-each select="zap">
      <!-- insert or update -->
      <xsl:call-template name="OnkT" />
    </xsl:for-each>
  </xsl:template>
  <!-- N003 "<zap>" template -->
  <xsl:template name="OnkT" >
    INSERT INTO ISZL.T_N003_OnkT (ID_T, DS_T, KOD_T, T_NAME, DATEBEG, DATEEND, N003ID)
    SELECT '<xsl:value-of select="ID_T" />'
	, LEFT('<xsl:value-of select="DS_T" />', 255)
	, LEFT('<xsl:value-of select="KOD_T" />', 255)
	, LEFT('<xsl:value-of select="T_NAME" />', 255)
    , to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
	, gen_random_uuid()
    where not exists(select * from ISZL.T_N003_OnkT onk where onk.ID_T = '<xsl:value-of select="ID_T" />');

    update ISZL.T_N003_OnkT
    set DS_T = LEFT('<xsl:value-of select="DS_T" />', 255)
	, KOD_T = LEFT('<xsl:value-of select="KOD_T" />', 255)
	, T_NAME = LEFT('<xsl:value-of select="T_NAME" />', 255)
    , DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , DATEEND = <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where ID_T = '<xsl:value-of select="ID_T" />';
    --PGSQL:GO
  </xsl:template>

  <!-- N004_OnkN: N004.xml -->
  <xsl:template match="packet[(zglv[type='OnkN'])]">
    <xsl:for-each select="zap">
      <!-- insert or update -->
      <xsl:call-template name="OnkN" />
    </xsl:for-each>
  </xsl:template>
  <!-- N004 "<zap>" template -->
  <xsl:template name="OnkN" >
    INSERT INTO ISZL.T_N004_OnkN (ID_N, DS_N, KOD_N, N_NAME, DATEBEG, DATEEND, N004ID)
    SELECT '<xsl:value-of select="ID_N" />'
	, LEFT('<xsl:value-of select="DS_N" />', 255)
	, LEFT('<xsl:value-of select="KOD_N" />', 255)
	, LEFT('<xsl:value-of select="N_NAME" />', 255)
    , to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
	, gen_random_uuid()
    where not exists(select * from ISZL.T_N004_OnkN onkn where onkn.ID_N = '<xsl:value-of select="ID_N" />');
 
    update ISZL.T_N004_OnkN
    set DS_N = LEFT('<xsl:value-of select="DS_N" />', 255)
	, KOD_N = LEFT('<xsl:value-of select="KOD_N" />', 255)
	, N_NAME = LEFT('<xsl:value-of select="N_NAME" />', 255)
    , DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , DATEEND = <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where ID_N = '<xsl:value-of select="ID_N" />';
    --PGSQL:GO
  </xsl:template>


  <!-- N005_OnkM: N005.xml -->
  <xsl:template match="packet[(zglv[type='OnkM'])]">
    <xsl:for-each select="zap">
      <!-- insert or update -->
      <xsl:call-template name="OnkM" />
    </xsl:for-each>
  </xsl:template>
  <!-- N005 "<zap>" template -->
  <xsl:template name="OnkM" >
    INSERT INTO ISZL.T_N005_OnkM (ID_M, DS_M, KOD_M, M_NAME, DATEBEG, DATEEND, N005ID)
    SELECT '<xsl:value-of select="ID_M" />'
	, LEFT('<xsl:value-of select="DS_M" />', 255)
	, LEFT('<xsl:value-of select="KOD_M" />', 255)
	, LEFT('<xsl:value-of select="M_NAME" />', 255)
    , to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
	, gen_random_uuid()
    where not exists(select * from ISZL.T_N005_OnkM onkm where onkm.ID_M = '<xsl:value-of select="ID_M" />');

    update ISZL.T_N005_OnkM
    set DS_M = LEFT('<xsl:value-of select="DS_M" />', 255)
	, KOD_M = LEFT('<xsl:value-of select="KOD_M" />', 255)
	, M_NAME = LEFT('<xsl:value-of select="M_NAME" />', 255)
    , DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , DATEEND = <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where ID_M = '<xsl:value-of select="ID_M" />';
    --PGSQL:GO
  </xsl:template>

  <!-- N006_OnkTNM: N006.xml -->
  <xsl:template match="packet[(zglv[type='OnkTNM'])]">
    <xsl:for-each select="zap">
      <!-- insert or update  -->
      <xsl:call-template name="OnkTNM" />
    </xsl:for-each>
  </xsl:template>
  <!-- N006 "<zap>" template -->
  <xsl:template name="OnkTNM" >
    INSERT INTO ISZL.T_N006_OnkTNM (ID_gr, DS_gr, ID_St, ID_T, ID_N, ID_M, DATEBEG, DATEEND, N006ID)
    SELECT '<xsl:value-of select="ID_gr" />'
	, LEFT('<xsl:value-of select="DS_gr" />', 255)
	, '<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_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
	, gen_random_uuid()
    where not exists(select * from ISZL.T_N006_OnkTNM onkt where onkt.ID_gr = '<xsl:value-of select="ID_gr" />');

    update ISZL.T_N006_OnkTNM
    set DS_gr = LEFT('<xsl:value-of select="DS_gr" />', 255)
	, ID_St = '<xsl:value-of select="ID_St" />'
	, ID_T = '<xsl:value-of select="ID_T" />'
	, ID_N = '<xsl:value-of select="ID_N" />'
	, ID_M = '<xsl:value-of select="ID_M" />'
    , DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , DATEEND = <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where ID_gr = '<xsl:value-of select="ID_gr" />';
    --PGSQL:GO
  </xsl:template>

  <!-- N007_OnkMrf: N007.xml -->
  <xsl:template match="packet[(zglv[type='OnkMrf'])]">
    <xsl:for-each select="zap">
      <!-- insert or update MedicalService -->
      <xsl:call-template name="OnkMrf" />
    </xsl:for-each>
  </xsl:template>
  <!-- N007 "<zap>" template -->
  <xsl:template name="OnkMrf" >
    INSERT INTO ISZL.T_N007_OnkMrf (ID_Mrf, Mrf_NAME, DATEBEG, DATEEND, N007ID)
    SELECT '<xsl:value-of select="ID_Mrf" />'
	, LEFT('<xsl:value-of select="Mrf_NAME" />', 255)
    , to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
	, gen_random_uuid()
   where not exists(select * from ISZL.T_N007_OnkMrf omr where omr.ID_Mrf = '<xsl:value-of select="ID_Mrf" />');

    update ISZL.T_N007_OnkMrf
    set Mrf_NAME = LEFT('<xsl:value-of select="Mrf_NAME" />', 255)
    , DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , DATEEND = <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where ID_Mrf = '<xsl:value-of select="ID_Mrf" />';
    --PGSQL:GO
  </xsl:template>

  <!-- N010_OnkIgh: N010.xml -->
  <xsl:template match="packet[(zglv[type='OnkIgh'])]">
    <xsl:for-each select="zap">
      <!-- insert or update -->
      <xsl:call-template name="OnkIgh" />
    </xsl:for-each>
  </xsl:template>
  <!-- N010 "<zap>" template -->
  <xsl:template name="OnkIgh" >
    INSERT INTO ISZL.T_N010_OnkIgh (ID_Igh, KOD_Igh, Igh_NAME, DATEBEG, DATEEND, N010ID)
    SELECT '<xsl:value-of select="ID_Igh" />'
	, LEFT('<xsl:value-of select="KOD_Igh" />', 255)
	, LEFT('<xsl:value-of select="Igh_NAME" />', 255)
    , to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
	, gen_random_uuid()
    where not exists(select * from ISZL.T_N010_OnkIgh oi where oi.ID_Igh = '<xsl:value-of select="ID_Igh" />');

    update ISZL.T_N010_OnkIgh
    set KOD_Igh = LEFT('<xsl:value-of select="KOD_Igh" />', 255)
	, Igh_NAME = LEFT('<xsl:value-of select="Igh_NAME" />', 255)
    , DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , DATEEND = <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where ID_Igh = '<xsl:value-of select="ID_Igh" />';
    --PGSQL:GO
  </xsl:template>

  <!-- N011_OnkIghRt: N011.xml -->
  <xsl:template match="packet[(zglv[type='OnkIghRt'])]">
    <xsl:for-each select="zap">
      <!-- insert or update -->
      <xsl:call-template name="OnkIghRt" />
    </xsl:for-each>
  </xsl:template>
  <!-- N011 "<zap>" template -->
  <xsl:template name="OnkIghRt" >
    INSERT INTO ISZL.T_N011_OnkIghRt (ID_R_I, ID_Igh, KOD_R_I, R_I_NAME, DATEBEG, DATEEND, N011ID)
    SELECT '<xsl:value-of select="ID_R_I" />'
	, '<xsl:value-of select="ID_Igh" />'
	, LEFT('<xsl:value-of select="KOD_R_I" />', 255)
	, LEFT('<xsl:value-of select="R_I_NAME" />', 255)
    , to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
	, gen_random_uuid()
    where not exists(select * from ISZL.T_N011_OnkIghRt oir where oir.ID_R_I = '<xsl:value-of select="ID_R_I" />');

    update ISZL.T_N011_OnkIghRt
    set ID_Igh = '<xsl:value-of select="ID_Igh" />'
	, KOD_R_I = LEFT('<xsl:value-of select="KOD_R_I" />', 255)
	, R_I_NAME = LEFT('<xsl:value-of select="R_I_NAME" />', 255)
    , DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , DATEEND = <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where ID_R_I = '<xsl:value-of select="ID_R_I" />';
    --PGSQL:GO
  </xsl:template>

  <!-- N013_OnkLech: N013.xml -->
  <xsl:template match="packet[(zglv[type='OnkLech'])]">
    <xsl:for-each select="zap">
      <!-- insert or update -->
      <xsl:call-template name="OnkLech" />
    </xsl:for-each>
  </xsl:template>
  <!-- N013 "<zap>" template -->
  <xsl:template name="OnkLech" >
    INSERT INTO ISZL.T_N013_OnkLech (ID_TLech, TLech_NAME, DATEBEG, DATEEND, N013ID)
    SELECT '<xsl:value-of select="ID_TLech" />'
	, LEFT('<xsl:value-of select="TLech_NAME" />', 255)
    , to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
	, gen_random_uuid()
    where not exists(select * from ISZL.T_N013_OnkLech okl where okl.ID_TLech = '<xsl:value-of select="ID_TLech" />');

    update ISZL.T_N013_OnkLech
    set TLech_NAME = LEFT('<xsl:value-of select="TLech_NAME" />', 255)
    , DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , DATEEND = <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where ID_TLech = '<xsl:value-of select="ID_TLech" />';
    --PGSQL:GO
  </xsl:template>

  <!-- N014_OnkHir: N014.xml -->
  <xsl:template match="packet[(zglv[type='OnkHir'])]">
    <xsl:for-each select="zap">
      <!-- insert or update -->
      <xsl:call-template name="OnkHir" />
    </xsl:for-each>
  </xsl:template>
  <!-- N014 "<zap>" template -->
  <xsl:template name="OnkHir" >
    INSERT INTO ISZL.T_N014_OnkHir (ID_THir, THir_NAME, DATEBEG, DATEEND, N014ID)
    SELECT '<xsl:value-of select="ID_THir" />'
	, LEFT('<xsl:value-of select="THir_NAME" />', 255)
    , to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
	, gen_random_uuid()
    where not exists(select * from ISZL.T_N014_OnkHir oh where oh.ID_THir = '<xsl:value-of select="ID_THir" />');

    update ISZL.T_N014_OnkHir
    set THir_NAME = LEFT('<xsl:value-of select="THir_NAME" />', 255)
    , DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , DATEEND = <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where ID_THir = '<xsl:value-of select="ID_THir" />';
    --PGSQL:GO
  </xsl:template>

  <!-- N016_OnkLek_V: N016.xml -->
  <xsl:template match="packet[(zglv[type='OnkLek_V'])]">
    <xsl:for-each select="zap">
      <!-- insert or update-->
      <xsl:call-template name="OnkLek_V" />
    </xsl:for-each>
  </xsl:template>
  <!-- N016 "<zap>" template -->
  <xsl:template name="OnkLek_V" >
    INSERT INTO ISZL.T_N016_OnkLek_V (ID_TLek_V, TLek_NAME_V, DATEBEG, DATEEND, N016ID)
    SELECT '<xsl:value-of select="ID_TLek_V" />'
	, LEFT('<xsl:value-of select="TLek_NAME_V" />', 255)
    , to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
	, gen_random_uuid()
    where not exists(select * from ISZL.T_N016_OnkLek_V olv where olv.ID_TLek_V = '<xsl:value-of select="ID_TLek_V" />');
	
    update ISZL.T_N016_OnkLek_V
    set TLek_NAME_V = LEFT('<xsl:value-of select="TLek_NAME_V" />', 255)
    , DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
    , DATEEND = <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where ID_TLek_V = '<xsl:value-of select="ID_TLek_V" />';
    --PGSQL:GO
  </xsl:template>

        <!-- V016_MEDICAL_TERMS: V016.xml -->
  <xsl:template match="packet[(zglv[type='DispT'])]">
    <xsl:for-each select="zap">
      <!-- insert or update -->
      <xsl:call-template name="DispT" />
    </xsl:for-each>
  </xsl:template>
  <xsl:template name="DispT" >
    INSERT INTO  ISZL.T_V016_DISPT (IDDT ,DTNAME, DATEBEG, DATEEND, V016ID)
    SELECT
               '<xsl:value-of select="IDDT" />'
			  ,LEFT('<xsl:value-of select="DTNAME" />', 254)
              ,to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
              ,<xsl:choose>
                  <xsl:when test="DATEEND!=''">to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')</xsl:when>
                  <xsl:otherwise>null</xsl:otherwise>
               </xsl:choose>
			  ,gen_random_uuid()
where not exists(select * from ISZL.T_V016_DISPT d where d.IDDT = '<xsl:value-of select="IDDT" />');

    update ISZL.T_V016_DISPT
    set DTNAME = LEFT('<xsl:value-of select="DTNAME" />', 254)
       ,DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
       ,DATEEND = <xsl:choose>
                         <xsl:when test="DATEEND!=''">to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')</xsl:when>
                         <xsl:otherwise>null</xsl:otherwise>
                      </xsl:choose>
    where IDDT = '<xsl:value-of select="IDDT" />';
--PGSQL:GO
  </xsl:template>
  
        <!-- F006: F006.xml -->
  <xsl:template match="packet[(zglv[type='VidExp'])]">
    <xsl:for-each select="zap">
      <!-- insert or update -->
      <xsl:call-template name="VidExp" />
    </xsl:for-each>
  </xsl:template>
  <xsl:template name="VidExp" >
    INSERT INTO ISZL.T_F006_CONTROL_TYPE(IDVID, VIDNAME, DATEBEG, DATEEND, f006controltypeid)
    SELECT
               '<xsl:value-of select="IDVID" />'
			  ,LEFT('<xsl:value-of select="VIDNAME" />', 350)
              ,to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')S
              ,<xsl:choose>
                  <xsl:when test="DATEEND!=''">to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')</xsl:when>
                  <xsl:otherwise>null</xsl:otherwise>
               </xsl:choose>
			  ,gen_random_uuid()
where not exists(select * from ISZL.T_F006_CONTROL_TYPE ct where ct.IDVID = '<xsl:value-of select="IDVID" />');

    update ISZL.T_F006_CONTROL_TYPE
    set VIDNAME = LEFT('<xsl:value-of select="VIDNAME" />', 350)
       ,DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
       ,DATEEND = <xsl:choose>
                         <xsl:when test="DATEEND!=''">to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')</xsl:when>
                         <xsl:otherwise>null</xsl:otherwise>
                      </xsl:choose>
    where IDVID = '<xsl:value-of select="IDVID" />';
--PGSQL:GO
  </xsl:template>
  
 <!-- N017: N017.xml -->
  <xsl:template match="packet[(zglv[type='OnkLuch'])]">
    <xsl:for-each select="zap">
      <!-- insert or update -->
      <xsl:call-template name="OnkLuch" />
    </xsl:for-each>
  </xsl:template>
  <xsl:template name="OnkLuch" >
    INSERT INTO ISZL.T_N017_OnkLuch(ID_TLuch, TLuch_NAME, DATEBEG, DATEEND, N017ID)
    SELECT
               '<xsl:value-of select="ID_TLuch" />'
			  ,LEFT('<xsl:value-of select="TLuch_NAME" />', 255)
              ,to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
              ,<xsl:choose>
                  <xsl:when test="DATEEND!=''">to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')</xsl:when>
                  <xsl:otherwise>null</xsl:otherwise>
               </xsl:choose>
			  ,gen_random_uuid()
    Where not exists(select * from ISZL.T_N017_OnkLuch t where t.ID_TLuch = '<xsl:value-of select="ID_TLuch" />');

    update ISZL.T_N017_OnkLuch
    set TLuch_NAME = LEFT('<xsl:value-of select="TLuch_NAME" />', 255)
       ,DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
       ,DATEEND = <xsl:choose>
                         <xsl:when test="DATEEND!=''">to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')</xsl:when>
                         <xsl:otherwise>null</xsl:otherwise>
                      </xsl:choose>
    where ID_TLuch = '<xsl:value-of select="ID_TLuch" />';
--PGSQL:GO
  </xsl:template>
  
    <!-- V018_VID_VMP: V018.xml -->
  <xsl:template match="packet[(zglv[type='HVid'])]">
    <xsl:for-each select="zap">
      <!-- insert or update -->
      <xsl:call-template name="HVid" />
    </xsl:for-each>
  </xsl:template>
  <xsl:template name="HVid" >
    INSERT INTO  ISZL.T_V018_VID_VMP (V018VidVmpID, IDHVID, HVIDNAME, DATEBEG, DATEEND)
    SELECT gen_random_uuid()
	      ,LEFT('<xsl:value-of select="IDHVID" />', 16)
	      ,LEFT('<xsl:value-of select="HVIDNAME" />', 1024)
          ,to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
          , <xsl:if test="DATEEND != ''">
          to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if> 
    where not exists(select * from ISZL.T_V018_VID_VMP where IDHVID = '<xsl:value-of select="IDHVID" />');

    update ISZL.T_V018_VID_VMP
    set 
	    HVIDNAME = LEFT('<xsl:value-of select="HVIDNAME" />', 1024)
       ,DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
       ,DATEEND = <xsl:if test="DATEEND != ''">
        to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where IDHVID = '<xsl:value-of select="IDHVID" />';
--PGSQL:GO
  </xsl:template>
  
  <!-- V019_METOD_VMP: V019.xml -->
  <xsl:template match="packet[(zglv[type='HMet'])]">
    <xsl:for-each select="zap">
      <!-- insert or update -->
      <xsl:call-template name="HMet" />
    </xsl:for-each>
  </xsl:template>
  <xsl:template name="HMet" > 
    INSERT INTO  ISZL.T_V019_METOD_VMP (V019MetodVmpID, IDHM, HMNAME, DIAG, HVID, HGR, HMODP, IDMODP, DATEBEG, DATEEND)
    SELECT  gen_random_uuid()
	      ,'<xsl:value-of select="IDHM" />'
	      ,LEFT('<xsl:value-of select="HMNAME" />', 1024)
		  ,LEFT('<xsl:value-of select="DIAG" />', 256)
		  ,LEFT('<xsl:value-of select="HVID" />', 16)
		  ,<xsl:value-of select="HGR" /><xsl:if test="HGR = ''">NULL</xsl:if>
		  ,LEFT('<xsl:value-of select="HMODP" />',256)
		  ,<xsl:value-of select="IDMODP" /> <xsl:if test="IDMODP = ''">NULL</xsl:if>
          ,to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
              ,<xsl:choose>
           <xsl:when test="DATEEND!=''">to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')</xsl:when>
                  <xsl:otherwise>null</xsl:otherwise> 
               </xsl:choose>
where not exists(select * from ISZL.T_V019_METOD_VMP mv where mv.IDHM = '<xsl:value-of select="IDHM" />');

    update ISZL.T_V019_METOD_VMP
    set 
	    HMNAME = LEFT('<xsl:value-of select="HMNAME" />', 1024)
	   ,DIAG = LEFT('<xsl:value-of select="DIAG" />', 256)
	   ,HVID = LEFT('<xsl:value-of select="HVID" />', 16)
	   ,HGR = <xsl:value-of select="HGR" /><xsl:if test="HGR = ''">NULL</xsl:if>
	   ,HMODP = LEFT('<xsl:value-of select="HMODP" />',256)
	   ,IDMODP = <xsl:value-of select="IDMODP" /><xsl:if test="IDMODP = ''">NULL</xsl:if>
       ,DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
       ,DATEEND = <xsl:if test="DATEEND != ''">
      to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')
    </xsl:if><xsl:if test="DATEEND = ''">NULL</xsl:if>
    where IDHM = '<xsl:value-of select="IDHM" />'
--PGSQL:GO
  </xsl:template>
  
  <!--Violation Settings-->
  <xsl:template match="violations/groups/group">
    <xsl:call-template name="violationsGroup" />
    <xsl:for-each select="settings/setting">
      <xsl:call-template name="violation" />
      <xsl:call-template name="violationSetting" />
    </xsl:for-each>
  </xsl:template>
  <xsl:template name="violationsGroup" > 
update ISZL.T_VIOLATIONS_GROUP_SETTING vgs
set Entities = (select EntityID from VCLib.T_ENTITIES where Schema || '.' || EntName = '<xsl:value-of select="@entity"/>'),
	ViolationsEntities = (select EntityID from VCLib.T_ENTITIES where Schema || '.' || EntName = '<xsl:value-of select="@violationEntity"/>'),
	ViolationCaseEntities = (select EntityID from VCLib.T_ENTITIES where Schema || '.' || EntName = '<xsl:value-of select="@violationCaseEntity"/>'),
	ChildAttributes = '<xsl:value-of select="@childs"/>'
where vgs.Name = '<xsl:value-of select="@name"/>';

INSERT INTO ISZL.T_VIOLATIONS_GROUP_SETTING
           (ViolationsGroupSettingID ,Name ,Entities ,ViolationsEntities ,ChildAttributes ,ViolationCaseEntities)
SELECT
           gen_random_uuid()--ViolationsGroupSettingID, uniqueidentifier,
          ,'<xsl:value-of select="@name"/>'--Name, varchar(255),
          ,(select EntityID from VCLib.T_ENTITIES where Schema || '.' || EntName = '<xsl:value-of select="@entity"/>')--Entities, int,
          ,(select EntityID from VCLib.T_ENTITIES where Schema || '.' || EntName = '<xsl:value-of select="@violationEntity"/>')--ViolationsEntities, int,
          ,'<xsl:value-of select="@childs"/>'--ChildAttributes, varchar(max),
          ,(select EntityID from VCLib.T_ENTITIES where Schema || '.' || EntName = '<xsl:value-of select="@violationCaseEntity"/>')--ViolationCaseEntities, int,
where not exists
	(
		select *
		from ISZL.T_VIOLATIONS_GROUP_SETTING vgs
		where vgs.Name = '<xsl:value-of select="@name"/>'
	);

delete from ISZL.T_VIOLATION_SETTING vs
using ISZL.T_VIOLATIONS_GROUP_SETTING vgs 
where vgs.ViolationsGroupSettingID = vs.ViolationsGroupSetting
    and
  vgs.Name = '<xsl:value-of select="@name"/>';
--PGSQL:GO
  </xsl:template>
  <xsl:template name="violation" > 
update ISZL.T_VIOLATION v
set Name = '<xsl:value-of select="violation/@name"/>'
where v.Code = '<xsl:value-of select="violation/@code"/>' and v.type_ = (select EntityID from VCLib.T_ENTITIES where Schema || '.' || EntName = '<xsl:value-of select="violation/@type"/>');

INSERT INTO ISZL.T_VIOLATION
           (ViolationID ,Code ,Name ,type_)
SELECT
           gen_random_uuid()--ViolationID, uniqueidentifier
          ,'<xsl:value-of select="violation/@code"/>'--Code, varchar(11)
          ,'<xsl:value-of select="violation/@name"/>'--Name, varchar(255)
          ,(select EntityID from VCLib.T_ENTITIES where Schema || '.' || EntName = '<xsl:value-of select="violation/@type"/>')--type_, int)
where not exists
	(
		select *
		from ISZL.T_VIOLATION v
		where v.Code = '<xsl:value-of select="violation/@code"/>' and v.type_ = (select EntityID from VCLib.T_ENTITIES where Schema || '.' || EntName = '<xsl:value-of select="violation/@type"/>')
	);
--PGSQL:GO
  </xsl:template>
  <xsl:template name="violationSetting" > 
INSERT INTO ISZL.T_VIOLATION_SETTING
           (ViolationSettingID ,ViolationsGroupSetting ,Entities ,Filter ,Violation ,Alias ,OrderNum)
SELECT
           gen_random_uuid()--ViolationSettingID, uniqueidentifier
          ,vgs.ViolationsGroupSettingID--ViolationsGroupSetting, uniqueidentifier
          ,(select EntityID from VCLib.T_ENTITIES where Schema || '.' || EntName = '<xsl:value-of select="@entity"/>')--Entities, int
          ,'<xsl:call-template name="serializeNodeToString"><xsl:with-param name="node" select="filter/*"/></xsl:call-template>'--Filter, varchar(max)
          ,(select v.ViolationID from ISZL.T_VIOLATION v where v.Code = '<xsl:value-of select="violation/@code"/>' and v.type_ = (select EntityID from VCLib.T_ENTITIES where Schema || '.' || EntName = '<xsl:value-of select="violation/@type"/>'))--Violation, uniqueidentifier
          ,'<xsl:value-of select="@alias"/>'--Alias, varchar(255)
          ,<xsl:value-of select="@order"/>--OrderNum, int)
from ISZL.T_VIOLATIONS_GROUP_SETTING vgs
where vgs.Name = '<xsl:value-of select="../../@name"/>';
--PGSQL:GO
  </xsl:template>

  <xsl:template name="serializeNodeToString">
    <xsl:param name="node" />
    <xsl:variable name="name" select="name($node)" />
    <xsl:text>&lt;</xsl:text>
    <xsl:value-of select="$name" />
    <xsl:for-each select="$node/@*">
      <xsl:text> </xsl:text>
      <xsl:value-of select="name()" />
      <xsl:text>=&quot;</xsl:text>
      <xsl:call-template name ="replaceSpecCharacters">
        <xsl:with-param name="text" select="."/>
      </xsl:call-template>
      <xsl:text>&quot;</xsl:text>
    </xsl:for-each>
    <xsl:if test="namespace-uri($node)">
      <xsl:text> xmlns:</xsl:text>
      <xsl:value-of select="substring-before(name($node),':')"/>
      <xsl:text>="</xsl:text>
      <xsl:value-of select="namespace-uri($node)"/>
      <xsl:text>"</xsl:text>
    </xsl:if>
    <xsl:text>&gt;</xsl:text>
    <xsl:if test="./text()">
      <xsl:text disable-output-escaping="yes">&lt;![CDATA[</xsl:text>
      <xsl:value-of select="./text()" />
      <xsl:text disable-output-escaping="yes">]]&gt;</xsl:text>
    </xsl:if>
    <xsl:for-each select="$node/*">
      <xsl:call-template name="serializeNodeToString">
        <xsl:with-param name="node" select="."/>
      </xsl:call-template>
    </xsl:for-each>
    <xsl:text>&lt;/</xsl:text>
    <xsl:value-of select="$name" />
    <xsl:text>&gt;</xsl:text>
  </xsl:template>

  <xsl:template name="replaceSpecCharacters">
    <xsl:param name="text" />
    <xsl:call-template name ="replace">
      <xsl:with-param name="text">
        <xsl:call-template name ="replace">
          <xsl:with-param name="text">
            <xsl:call-template name ="replace">
              <xsl:with-param name="text">
                <xsl:call-template name ="replace">
                  <xsl:with-param name="text">
                    <xsl:call-template name ="replace">
                      <xsl:with-param name="text" select="$text"/>
                      <xsl:with-param name="replace">&amp;</xsl:with-param>
                      <xsl:with-param name="by">&amp;amp;</xsl:with-param>
                    </xsl:call-template>
                  </xsl:with-param>
                  <xsl:with-param name="replace">&lt;</xsl:with-param>
                  <xsl:with-param name="by">&amp;lt;</xsl:with-param>
                </xsl:call-template>
              </xsl:with-param>
              <xsl:with-param name="replace">&gt;</xsl:with-param>
              <xsl:with-param name="by">&amp;gt;</xsl:with-param>
            </xsl:call-template>
          </xsl:with-param>
          <xsl:with-param name="replace">&apos;</xsl:with-param>
          <xsl:with-param name="by">&amp;apos;</xsl:with-param>
        </xsl:call-template>
      </xsl:with-param>
      <xsl:with-param name="replace">&quot;</xsl:with-param>
      <xsl:with-param name="by">&amp;quot;</xsl:with-param>
    </xsl:call-template>
  </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>

  <!-- V025_DISPT: V025.xml -->
  <xsl:template match="packet[(zglv[type='KPC'])]">
    <xsl:for-each select="zap">
      <!-- insert or update -->
      <xsl:call-template name="KPC" />
    </xsl:for-each>
  </xsl:template>
  <xsl:template name="KPC" >
    INSERT INTO  ISZL.T_V025_KPC (V025KpcID,IDPC ,N_PC, DATEBEG, DATEEND)
    SELECT
	           gen_random_uuid()
              ,LEFT('<xsl:value-of select="IDPC" />', 3)
	          ,LEFT('<xsl:value-of select="N_PC" />', 254)
              ,to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
              ,<xsl:choose>
                  <xsl:when test="DATEEND!=''"> to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')</xsl:when>
                  <xsl:otherwise>null</xsl:otherwise>
               </xsl:choose>
where not exists(select * from ISZL.T_V025_KPC where IDPC = LEFT('<xsl:value-of select="IDPC" />', 3));

    update ISZL.T_V025_KPC
    set N_PC = LEFT('<xsl:value-of select="N_PC" />', 254)
       ,DATEBEG = to_timestamp('<xsl:value-of select="DATEBEG" />', 'DD.MM.YYYY')
       ,DATEEND = <xsl:choose>
                         <xsl:when test="DATEEND!=''">to_timestamp('<xsl:value-of select="DATEEND" />', 'DD.MM.YYYY')</xsl:when>
                         <xsl:otherwise>null</xsl:otherwise>
                      </xsl:choose>
    where IDPC = LEFT('<xsl:value-of select="IDPC" />', 3);
--PGSQL:GO
  </xsl:template>

  <xsl:template match="root/MkbNamedRanges">
    <xsl:for-each select="MkbNamedRange">
      -- user columns
      do $$
      declare
      v_name varchar(100) = <xsl:choose>
        <xsl:when test="Name">
          '<xsl:value-of select="Name" />'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>;
      v_mkb_from varchar(100) = <xsl:choose>
        <xsl:when test="MkbFrom">
          '<xsl:value-of select="MkbFrom" />'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>;
      v_mkb_to varchar(100) = <xsl:choose>
        <xsl:when test="MkbTo">
          '<xsl:value-of select="MkbTo" />'
        </xsl:when>
        <xsl:otherwise>NULL</xsl:otherwise>
      </xsl:choose>;
      begin
        IF NOT EXISTS (SELECT 1 FROM ISZL.T_MKB_NAMED_RANGE WHERE Name = v_name and MkbFrom = v_mkb_from and MkbTo = v_mkb_to)
        THEN
        BEGIN
	        INSERT INTO iszl.t_mkb_named_range(MkbNamedRangeID, Name, MkbFrom, MkbTo)
	        VALUES(gen_random_uuid()
	        ,v_name
	        ,v_mkb_from
	        ,v_mkb_to
	        );
        END;
        END IF;
      end;$$;
    </xsl:for-each>
  </xsl:template>

</xsl:stylesheet>
