pa_infoPDT_exportar

USE [SNSMART_ANALISIS]
GO
/****** Object:  StoredProcedure [SN].[pa_infoPDT_exportar]    Script Date: 27/01/2017 9:45:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [SN].[pa_infoPDT_exportar]
(
    @tipo varchar(10),
    @anho int,
    @mes int,
    @tiporegistro int
)
as
begin
    declare @strContenidoPDT varchar(max),
            @tipoesc varchar(1),
            @nroescritura varchar(5),
            @fechaNum varchar(10),
            @fechaAut varchar(10),
            @fechaLeg varchar(10),
            @tipoActo varchar(2),
            @secuenciaActo varchar(5),
            @codMoneda varchar(1),
            @valCuotaMC varchar(max),
            @plazoInicial varchar(10),
            @plazoFinal varchar(10),
            @nomContrato varchar(30),
            @fechaSucrip varchar(10),
            @exhibeMedPago varchar(1),
            @MedioPago varchar(3),
            @codMonedaMedPago varchar(1),
            @montoPagado varchar(max),
            @fechaPago varchar(10),
            @nroDocMedPago varchar(25),
            @entFinanciera varchar(2)

    declare @SecuenciaBien varchar(max),
            @TipoBien varchar(20),
            @CodBien varchar(20),
            @OpcionBien varchar(20),
            @NroBien varchar(20),
            @SerieBien varchar(20),
            @OrigenBien varchar(100),
            @UbigeoBien varchar(100),
            @FechaBien varchar(100),
            @DescripBien varchar(100)

    declare @SecuenciaOtorgante varchar(max),
            @TipoDoc varchar(2),
            @NroDoc varchar(20),
            @TipoOtorgante varchar(2),
            @TipoPer varchar(1),
            @ubigeoPer varchar(100),
            @Porcentaje varchar(max),
            @RazonSocial varchar(40),
            @ApellidoPat varchar(15),
            @ApellidoMat varchar(15),
            @Nombre1 varchar(15),
            @Nombre2 varchar(15),
            @Gen3Cat varchar(1),
            @EraSuCasa varchar(1),
            @nroOperacion varchar(10),
            @porcj2Cat varchar(20),
            @nomArchivo varchar(50)
    declare @CuentaOTG int,
                    @i int,
                    @aux varchar(5)

    declare @idRegDesdeTipoReg int

    set @CuentaOTG  = 0
    set @i = 0
    set @aux = ''

    set @tipoesc = ''
    set @nroescritura = ''
    set @fechaNum = ''
    set @fechaAut = ''
    set @fechaLeg = ''
    set @tipoActo = ''
    set @secuenciaActo = ''
    set @codMoneda = ''
    set @valCuotaMC = ''
    set @plazoInicial = ''
    set @plazoFinal = ''
    set @nomContrato = ''
    set @fechaSucrip = ''
    set @exhibeMedPago = ''
    set @MedioPago = ''
    set @codMonedaMedPago = ''
    set @montoPagado = ''
    set @fechaPago = ''
    set @nroDocMedPago = ''
    set @entFinanciera = ''
    set @strContenidoPDT = ''

    set @SecuenciaBien = ''
    set @TipoBien = ''
    set @CodBien = ''
    set @OpcionBien = ''
    set @NroBien = ''
    set @SerieBien = ''
    set @OrigenBien = ''
    set @UbigeoBien = ''
    set @FechaBien = ''
    set @DescripBien = ''

    set @TipoDoc = ''
    set @NroDoc = ''
    set @TipoOtorgante = ''
    set @TipoPer = ''
    set @ubigeoPer = ''
    set @Porcentaje = ''
    set @RazonSocial = ''
    set @ApellidoPat = ''
    set @ApellidoMat = ''
    set @Nombre1 = ''
    set @Nombre2 = ''
    set @Gen3Cat = ''
    set @EraSuCasa = ''
    set @nroOperacion = ''
    set @porcj2Cat = ''
    set @idRegDesdeTipoReg = case @tiporegistro when 1 then 6 when 2 then 9 else 11 end

    set @nomArchivo = '3520' + CONVERT(varchar(4), @anho) + '10181338631'

    declare @xPDT table (
        NomArchivo varchar(50),
        strContenido varchar(max)
    )

    if @tipo = '00'
        begin
            set @nomArchivo = @nomArchivo + '.ACT'

            declare curInfoPDT cursor local for
    select distinct * from (
    select 2 as tipoesc,
     convert(int, SUBSTRING(a.Tm_NumeroEscritura, 6, 5))  Tm_NumeroEscritura,
                    SN.fc_formatearFecha('02', a.Tm_FechaGenEsc) AS fechaGen,
                    SN.fc_formatearFecha('02', a.tm_fechafirmaesc) AS fechaFirma,
                    '' as fechaLeg,
                    '04' as actojur,
                    '1' SecuencialActo,
                    isnull(e.tm_codigoPDT, '2') as PDTMoneda,
                    convert(varchar(max), d.Td_ValorExpVeh) as MercedConductiva,
                    '' as PlazoInicial,
                    '' as PlazoFinal,
                    '' as NomContrato,
                    SN.fc_formatearFecha('02', a.Tm_FechaGenEsc) as fechaMinuta,
                    case isnull(d.Td_AcreditaMP, 0) when 1 then '1' else '0' end AcreditaMP,
                    case isnull(d.Td_AcreditaMP, 0) when 1 then SYSMART.ObtenerNaturalezaTaTabla(isnull(d.Ta_MedioPago, '00'), 'Ta_MedioPago') else '' end as MedioPago,
                    case isnull(d.Td_AcreditaMP, 0) when 1 then isnull(e.tm_codigoPDT, '2') else '' end as PDTMonedaMP,
                    case isnull(d.Td_AcreditaMP, 0) when 1 then convert(varchar(max), isnull(d.Td_ValorExpVeh, 0)) else '' end as MontoPagado,
                    case isnull(d.Td_AcreditaMP, 0) when 1 then SN.fc_formatearFecha('02',a.Tm_FechaGenEsc) else '' end as fechaPago,
                    '-' as nroDocPago,
                    '99' as EntFinanciera
                    from SN.Tm_Escritura  as a
                        inner join SN.Td_Expediente_Acto as b on a.Tm_IdExpediente = b.Tm_idexpediente
                        inner join SN.Tp_Actos as c on b.Tp_idacto = c.Tp_IdActo
                        inner join SN.Td_Expediente_Vehiculo as d on b.Td_idexpedienteacto = d.Td_IdExpedienteActo and b.Tm_idexpediente = d.Tm_IdExpediente
                        inner join SYSMART.tm_moneda as e on d.Tm_IdMoneda = e.tm_idmoneda
                        where year(a.Tm_FechaGenEsc) = @anho AND month(a.Tm_FechaGenEsc) = @mes and a.Tm_idRegistro = 9
                        and not c.Ta_NatActo is null
                    and not c.Tp_IdActo in (17, 24, 33, 104, 34, 147, 47, 125, 148, 119,
40,
39,
38,
37,
36,
35) AND a.Tm_Excluida = 0
    UNION ALL


        select 1 as tipoesc,
                        convert(int, SUBSTRING(a.Tm_NumeroEscritura, 6, 5))Tm_NumeroEscritura,
                    SN.fc_formatearFecha('02', a.Tm_FechaGenEsc) AS fechaGen,
                    SN.fc_formatearFecha('02', a.tm_fechafirmaesc) AS fechaFirma,
                    '' as fechaLeg,
                    c.tp_codigopdt as actojur,
                    '1' SecuencialActo,
                    isnull(e.tm_codigoPDT, '2') as PDTMoneda,
                    convert(varchar(max), d.Td_ValorExpTrans) as MercedConductiva,
                    '' as PlazoInicial,
                    '' as PlazoFinal,
                    '' as NomContrato,
                    SN.fc_formatearFecha('02', case c.Tp_FlagMinuta when 1 then isnull(f.Tm_FechaGenMin, a.Tm_FechaGenEsc) else a.Tm_FechaGenEsc end) as fechaMinuta,
                    case when c.tp_codigopdt in ('04', '10', '24', '26') then case isnull(d.Td_AcreditaMP, 0) when 1 then '1' else '0' end else '' end AcreditaMP,
                    case when c.tp_codigopdt in ('04', '10', '24', '26') then case isnull(d.Td_AcreditaMP, 0) when 1 then SYSMART.ObtenerNaturalezaTaTabla(isnull(d.Ta_MedioPago, '00'), 'Ta_MedioPago') else ''  end else '' end as MedioPago,
                    case when c.tp_codigopdt in ('04', '10', '24', '26') then case isnull(d.Td_AcreditaMP, 0) when 1 then isnull(e.tm_codigoPDT, '2') else '' end else '' end as PDTMonedaMP,
                    case when c.tp_codigopdt in ('04', '10', '24', '26') then case isnull(d.Td_AcreditaMP, 0) when 1 then convert(varchar(max), d.Td_ValorExpTrans) else '' end else '' end as MontoPagado,
                    case when c.tp_codigopdt in ('04', '10', '24', '26') then case isnull(d.Td_AcreditaMP, 0) when 1 then SN.fc_formatearFecha('02', case c.Tp_FlagMinuta when 1 then f.Tm_FechaGenMin else a.Tm_FechaGenEsc end) else '' end else '' end as fechaPago,
                    case when c.tp_codigopdt in ('04', '10', '24', '26') then '-' else '' end as nroDocPago,
                    case when c.tp_codigopdt in ('04', '10', '24', '26') then '99' else '' end as EntFinanciera
                    from SN.Tm_Escritura  as a
                        inner join SN.Td_Expediente_Acto as b on a.Tm_IdExpediente = b.Tm_idexpediente
                        inner join SN.Tp_Actos as c on b.Tp_idacto = c.Tp_IdActo
                        inner join SN.Tm_Minuta as f on a.Tm_IdExpediente = f.Tm_IdExpediente
                        inner join SN.Td_Escritura_Minuta as m on a.Tm_IdEscritura = m.Tm_IdEscritura and f.Tm_IdMinuta = m.Tm_IdMinuta
                        inner join SN.Td_Expediente_Transferencia as d on b.Td_idexpedienteacto = d.Td_IdExpedienteActo
                        inner join SYSMART.tm_moneda as e on d.Tm_IdMoneda = e.tm_idmoneda
                        where year(a.Tm_FechaGenEsc) = @anho AND month(a.Tm_FechaGenEsc) = @mes and a.Tm_idRegistro = 6
                        and (case isnull(d.Td_AcreditaMP, 0) when 1 then case d.Td_ValorExpTrans when 0 then 1 else d.Td_ValorExpTrans end else 1 end) > 0
                        and not c.Ta_NatActo is null
                    and not c.Tp_IdActo in (17, 24, 33, 104, 34, 147, 47, 125, 148, 119,
40,
39,
38,
37,
36,
35) AND a.Tm_Excluida = 0
                        UNION ALL

                            select case a.Tm_idRegistro when 6 then 1 else 5 end as
                            tipoesc,
                        convert(int, SUBSTRING(a.Tm_NumeroEscritura, 6, 5)) Tm_NumeroEscritura,
                    case a.Tm_idRegistro when 6 then SN.fc_formatearFecha('02', a.Tm_FechaGenEsc) else '' end AS fechaGen,
                    case a.Tm_idRegistro when 11 then '' else SN.fc_formatearFecha('02', a.tm_fechafirmaesc) end AS fechaFirma,
                    case a.Tm_idRegistro when 11 then SN.fc_formatearFecha('02', a.Tm_FechaGenEsc) else '' end as fechaLeg,
                    c.tp_codigopdt as actojur,
                    case a.tm_idregistro when 6 then '1' else
                    case c.tp_codigopdt when '09' then '1' else '2' end end as SecuencialActo,
                    isnull(e.tm_codigoPDT, '2') as PDTMoneda,
                    convert(varchar(max), case d.Td_MontoGravamen when 0 then 1 else d.Td_MontoGravamen end) as MercedConductiva,
                    '' as PlazoInicial,
                    '' as PlazoFinal,
                    '' as NomContrato,
                    case when c.tp_codigopdt in ('09', '12') then '' else SN.fc_formatearFecha('02', case c.Tp_FlagMinuta when 1 then isnull(f.Tm_FechaGenMin, a.Tm_FechaGenEsc) else a.Tm_FechaGenEsc end) end as fechaMinuta,
                    '' AcreditaMP,
                    '' as MedioPago,
                    case when c.tp_codigopdt in ('04', '10') then isnull(e.tm_codigoPDT, '2') else '' end as PDTMonedaMP,
                    '' MontoPagado,
                    '' as fechaPago,
                    '' as nroDocPago,
                    '' as EntFinanciera
                    from SN.Tm_Escritura  as a
                        inner join SN.Td_Expediente_Acto as b on a.Tm_IdExpediente = b.Tm_idexpediente
                        inner join SN.Tp_Actos as c on b.Tp_idacto = c.Tp_IdActo
                        inner join SN.Tm_Minuta as f on a.Tm_IdExpediente = f.Tm_IdExpediente
                        inner join SN.Td_Escritura_Minuta as m on a.Tm_IdEscritura = m.Tm_IdEscritura and f.Tm_IdMinuta = m.Tm_IdMinuta
                        inner join SN.Td_Expediente_Gravamen as d on b.Td_idexpedienteacto = d.Td_IdExpedienteActo and  a.Tm_IdExpediente = d.Tm_idexpediente
                        inner join SYSMART.tm_moneda as e on d.Tm_IdMoneda = e.tm_idmoneda
                        where year(a.Tm_FechaGenEsc) = @anho AND month(a.Tm_FechaGenEsc) = @mes and a.Tm_idRegistro in (6,11)
                        and not c.Ta_NatActo is null
                    and not c.Tp_IdActo in (17, 24, 33, 104, 34, 147, 47, 125, 148, 119,
40,
39,
38,
37,
36,
35) AND a.Tm_Excluida = 0
                        UNION ALL


                            select case a.Tm_idRegistro when 6 then 1 when 9 then 2 else 5 end as tipoesc,
                     convert(int, SUBSTRING(a.Tm_NumeroEscritura, 6, 5))  Tm_NumeroEscritura,
                    case a.Tm_idRegistro when 6 then SN.fc_formatearFecha('02', a.Tm_FechaGenEsc) else '' end AS fechaGen,
                    case a.Tm_idRegistro when 11 then '' else SN.fc_formatearFecha('02', a.tm_fechafirmaesc) end AS fechaFirma,
                    case a.Tm_idRegistro when 11 then SN.fc_formatearFecha('02', a.Tm_FechaGenEsc) else '' end as fechaLeg,
                    c.tp_codigopdt as actojur,
                    '1' SecuencialActo,
                    isnull(h.tm_codigoPDT, '2') as PDTMoneda,
                    convert(varchar(max), case d.Td_CapitalSocial when  0 then 1 else  d.Td_CapitalSocial end) as MercedConductiva,
                    '' as PlazoInicial,
                    '' as PlazoFinal,
                    '' as NomContrato,
                    case when c.tp_codigopdt in ('09', '12') then '' else SN.fc_formatearFecha('02', case c.Tp_FlagMinuta when 1 then isnull(f.Tm_FechaGenMin, a.Tm_FechaGenEsc) else a.Tm_FechaGenEsc end) end as fechaMinuta,
                    '' AcreditaMP,
                    '' as MedioPago,
                    '' as PDTMonedaMP,
                    '' MontoPagado,
                    '' as fechaPago,
                    '' as nroDocPago,
                    '' as EntFinanciera
                    from SN.Tm_Escritura  as a
                        inner join SN.Td_Expediente_Acto as b on a.Tm_IdExpediente = b.Tm_idexpediente
                        inner join SN.Tp_Actos as c on b.Tp_idacto = c.Tp_IdActo
                        inner join SN.Tm_Minuta as f on a.Tm_IdExpediente = f.Tm_IdExpediente
                        inner join SN.Td_Escritura_Minuta as m on a.Tm_IdEscritura = m.Tm_IdEscritura and f.Tm_IdMinuta = m.Tm_IdMinuta
                        inner join SN.Td_Expediente_Sociedades as d on b.Td_idexpedienteacto = d.Td_IdExpedienteActo
                        inner join SN.Td_Expediente_SocAportes as e on d.Td_IdExpSociedades = e.Td_IdExpSociedades
                        inner join SYSMART.tm_moneda as h on d.Tm_IdMoneda = h.tm_idmoneda
                        where year(a.Tm_FechaGenEsc) = @anho AND month(a.Tm_FechaGenEsc) = @mes and a.Tm_idRegistro in (6,11)
                        and not c.Ta_NatActo is null
                    and not c.Tp_IdActo in (17, 24, 33, 104, 34, 147, 47, 125, 148, 119,
40,
39,
38,
37,
36,
35)
                    AND a.Tm_Excluida = 0
                        ) x where tipoesc = @tiporegistro
 order by  tipoesc, Tm_NumeroEscritura



            open curInfoPDT
            fetch next from curInfoPDT into @tipoesc,
                    @nroescritura,
                    @fechaNum,
                    @fechaAut,
                    @fechaLeg,
                    @tipoActo,
                    @secuenciaActo,
                    @codMoneda,
                    @valCuotaMC,
                    @plazoInicial,
                    @plazoFinal,
                    @nomContrato,
                    @fechaSucrip,
                    @exhibeMedPago,
                    @MedioPago,
                    @codMonedaMedPago,
                    @montoPagado,
                    @fechaPago,
                    @nroDocMedPago,
                    @entFinanciera
            while @@FETCH_STATUS = 0
                begin
                    /*if LEN(@strContenidoPDT) > 0
                        begin
                            set @strContenidoPDT = @strContenidoPDT + CHAR(13)
                        end*/

                    set @strContenidoPDT =
                        @tipoesc + '|' + convert(varchar(5),@nroescritura) + '|' + @fechaNum + '|' + @fechaAut + '|' + @fechaLeg + '|' + @tipoActo +
                        '|' + @secuenciaActo + '|' + @codMoneda + '|' + @valCuotaMC + '|' + @plazoInicial + '|' + @plazoFinal + '|' +
                        @nomContrato + '|' + @fechaSucrip + '|' + @exhibeMedPago + '|'
                         --@exhibeMedPago + '|' + @MedioPago + '|' + @codMonedaMedPago + '|' + @montoPagado + '|' +
                        --@fechaPago + '|' + @nroDocMedPago + '|' + @entFinanciera + '|'

                    insert into @xPDT (NomArchivo, strContenido)
                    values (@nomArchivo, @strContenidoPDT)

                    fetch next from curInfoPDT into @tipoesc,
                    @nroescritura,
                    @fechaNum,
                    @fechaAut,
                    @fechaLeg,
                    @tipoActo,
                    @secuenciaActo,
                    @codMoneda,
                    @valCuotaMC,
                    @plazoInicial,
                    @plazoFinal,
                    @nomContrato,
                    @fechaSucrip,
                    @exhibeMedPago,
                    @MedioPago,
                    @codMonedaMedPago,
                    @montoPagado,
                    @fechaPago,
                    @nroDocMedPago,
                    @entFinanciera
                end
            close curInfoPDT
            deallocate curInfoPDT


        end
    if @tipo = '01'
        begin
            set @aux = ''
            set @nomArchivo = @nomArchivo + '.BIE'

            declare curInfoPdtBienes cursor local for
                    select * from (
select  2 as tipoesc,
        convert(int, SUBSTRING(a.Tm_NumeroEscritura, 6, 5))  as nroescritura,
                    SN.fc_formatearFecha('02', a.tm_fechafirmaesc) as fechaNum,
                    '1' as SecuencialActo,
                    e.Tp_IdVehiculo as NroSecuencial,
                    'B' as TipoBien,
                    '09' as CodBien,
                     '1' as OpcionBien,
                    e.Tp_PlacavVehiculo as NroBien,
                    '' NroSerie,
                    '' OrigenBien,
                    '' UbigeoBien,
                    '' FechaBien,
                    '' DescripBien
                from SN.Tm_Escritura  as a
                    inner join SN.Td_Expediente_Acto as b on a.Tm_IdExpediente = b.Tm_idexpediente
                    inner join SN.Tp_Actos as c on b.Tp_idacto = c.Tp_IdActo
                    inner join SN.Td_Expediente_Vehiculo d on b.Td_idexpedienteacto = d.Td_IdExpedienteActo
                    inner join SN.Tp_Vehiculos as e on d.Tp_IdVehiculo = e.Tp_IdVehiculo
                    where year(a.Tm_FechaGenEsc) = @anho AND month(a.Tm_FechaGenEsc) = @mes and a.Tm_idRegistro = 9
                    and not c.Ta_NatActo is null
                    and not c.Tp_IdActo in (17, 24, 33, 104, 34, 147, 47, 125, 148, 119,
40,
39,
38,
37,
36,
35) AND a.Tm_Excluida = 0
UNION ALL
                        select 1 as tipoesc,
                        convert(int, SUBSTRING(a.Tm_NumeroEscritura, 6, 5))   as nroescritura,
                    SN.fc_formatearFecha('02', a.tm_fechafirmaesc) as fechaNum,
                    '1' as SecuencialActo,
                    d.Tp_IdBien as NroSecuencial,
                    'B' as TipoBien,
                    SYSMART.dbo.ObtenerNaturalezaTaTabla(d.Ta_tipobien, 'Ta_tipobien')   as CodBien,
                     case when SYSMART.dbo.ObtenerNaturalezaTaTabla(d.Ta_tipobien, 'Ta_tipobien')  in ('01', '07', '09') then '1' else '' end as OpcionBien,
                    '' as NroBien,
                    '' NroSerie,
                    isnull(f.Origen, '1') OrigenBien,
                    isnull(f.Tm_CodigoPDT, '140101') as UbigeoBien,
                    '' FechaBien,
                    case SYSMART.dbo.ObtenerNaturalezaTaTabla(d.Ta_tipobien, 'Ta_tipobien') when '99' then case len(d.Td_AnotaExpTrans) when 0 then case len(e.Tp_Anotacionespredios) when 0 then '-' else e.Tp_Anotacionespredios  end else case len(d.Td_AnotaExpTrans) when 0 then '-' else d.Td_AnotaExpTrans end end else '' end DescripBien
                from SN.Tm_Escritura  as a
                    inner join SN.Td_Expediente_Acto as b on a.Tm_IdExpediente = b.Tm_idexpediente
                    inner join SN.Tp_Actos as c on b.Tp_idacto = c.Tp_IdActo
                    inner join SN.Td_Expediente_Transferencia d on b.Td_idexpedienteacto = d.Td_IdExpedienteActo
                    inner join SN.Tp_Predios as e on d.Tp_IdBien = e.Tp_IdPredio
                    left join SYSMART.dbo.fc_ubigeo(0) as f on e.Tm_idUbigeo = f.tm_idubigeo
                    where year(a.Tm_FechaGenEsc) = @anho AND month(a.Tm_FechaGenEsc) = @mes and a.Tm_idRegistro = 6
                     AND c.Ta_NatActo <> '01'
                    and not c.Ta_NatActo is null
                    and not c.Tp_IdActo in (17, 24, 33, 104, 34, 147, 47, 125, 148, 119,
40,
39,
38,
37,
36,
35) AND a.Tm_Excluida = 0
            UNION ALL

                    select 1 as tipoesc,
                     convert(int, SUBSTRING(a.Tm_NumeroEscritura, 6, 5))   as nroescritura,
                    SN.fc_formatearFecha('02', a.tm_fechafirmaesc) as fechaNum,
                    '1' as SecuencialActo,
                    f.Tp_IdBien as NroSecuencial,
                    'B' as TipoBien,
                    SYSMART.dbo.ObtenerNaturalezaTaTabla(f.Ta_tipobien, 'Ta_tipobien')  as CodBien,
                     '' as OpcionBien,
                    '' as NroBien,
                    '' NroSerie,
                    isnull(i.Origen, '1') OrigenBien,
                    isnull(i.Tm_CodigoPDT, '140101') as UbigeoBien,
                    '' FechaBien,
                    case LEN(g.Tp_Anotacionespredios) when 0 then '-' else g.Tp_Anotacionespredios end DescripBien
                from SN.Tm_Escritura  as a
                    inner join SN.Td_Expediente_Acto as b on a.Tm_IdExpediente = b.Tm_idexpediente
                    inner join SN.Tp_Actos as c on b.Tp_idacto = c.Tp_IdActo
                    inner join SN.Td_Expediente_Sociedades as d on b.Td_idexpedienteacto = d.Td_IdExpedienteActo
                    left join SN.Td_Expediente_SocAportes as e on d.Td_IdExpSociedades = e.Td_IdExpSociedades
                    left join SN.Td_Expediente_DetAportes as f on e.Td_IdExpSocAportes = f.Td_IdExpSocAportes
                    left join SN.Tp_Predios as g on f.Tp_IdBien = g.Tp_IdPredio
                    left join SYSMART.dbo.fc_ubigeo(0) as i on g.Tm_idUbigeo = i.tm_idubigeo
                    where year(a.Tm_FechaGenEsc) = @anho AND month(a.Tm_FechaGenEsc) = @mes and a.Tm_idRegistro = 6
                     and c.Ta_NatActo = '01' and f.Ta_TipoBien = '01'
                     and not c.Ta_NatActo is null
                    and not c.Tp_IdActo in (17, 24, 33, 104, 34, 147, 47, 125, 148, 119,
40,
39,
38,
37,
36,
35) AND a.Tm_Excluida = 0
                    union all

                        select 1 as tipoesc,
                         convert(int, SUBSTRING(a.Tm_NumeroEscritura, 6, 5))   as nroescritura,
                    SN.fc_formatearFecha('02', a.tm_fechafirmaesc) as fechaNum,
                    '1' as SecuencialActo,
                    f.Tp_IdBien as NroSecuencial,
                    'B' as TipoBien,
                    SYSMART.dbo.ObtenerNaturalezaTaTabla(f.Ta_tipobien, 'Ta_tipobien')  as CodBien,
                    case when SYSMART.dbo.ObtenerNaturalezaTaTabla(f.Ta_tipobien, 'Ta_tipobien')  in ('01', '07', '09') then '1' else '' end  as OpcionBien,
                    h.Tp_PlacavVehiculo as NroBien,
                    '' NroSerie,
                    '' OrigenBien,
                    '' as UbigeoBien,
                    '' FechaBien,
                    '' DescripBien
                from SN.Tm_Escritura  as a
                    inner join SN.Td_Expediente_Acto as b on a.Tm_IdExpediente = b.Tm_idexpediente
                    inner join SN.Tp_Actos as c on b.Tp_idacto = c.Tp_IdActo
                    inner join SN.Td_Expediente_Sociedades as d on b.Td_idexpedienteacto = d.Td_IdExpedienteActo
                    left join SN.Td_Expediente_SocAportes as e on d.Td_IdExpSociedades = e.Td_IdExpSociedades
                    left join SN.Td_Expediente_DetAportes as f on e.Td_IdExpSocAportes = f.Td_IdExpSocAportes
                    left join SN.Tp_Vehiculos as h on f.Tp_IdBien = h.Tp_IdVehiculo
                    where year(a.Tm_FechaGenEsc) = @anho AND month(a.Tm_FechaGenEsc) = @mes and a.Tm_idRegistro = 6
                     and c.Ta_NatActo = '01' and f.Ta_TipoBien = '00'
                     and not c.Ta_NatActo is null
                    and not c.Tp_IdActo in (17, 24, 33, 104, 34, 147, 47, 125, 148, 119,
40,
39,
38,
37,
36,
35) AND a.Tm_Excluida = 0
                union all
                    select 1 as tipoesc,
             convert(int, SUBSTRING(a.Tm_NumeroEscritura, 6, 5))   as nroescritura,
                    SN.fc_formatearFecha('02', a.tm_fechafirmaesc) as fechaNum,
                    '1' as SecuencialActo,
                    f.Td_IdExpDetAportes as NroSecuencial,
                    'B' as TipoBien,
                    '99'  as CodBien,
                     '' as OpcionBien,
                    '' as NroBien,
                    '' NroSerie,
                     '' OrigenBien,
                    '' as UbigeoBien,
                    '' FechaBien,
                    substring(f.Td_DetalleBien, 0, 30) DescripBien
                from SN.Tm_Escritura  as a
                    inner join SN.Td_Expediente_Acto as b on a.Tm_IdExpediente = b.Tm_idexpediente
                    inner join SN.Tp_Actos as c on b.Tp_idacto = c.Tp_IdActo
                    inner join SN.Td_Expediente_Sociedades as d on b.Td_idexpedienteacto = d.Td_IdExpedienteActo
                    left join SN.Td_Expediente_SocAportes as e on d.Td_IdExpSociedades = e.Td_IdExpSociedades
                    left join SN.Td_Expediente_DetAportes as f on e.Td_IdExpSocAportes = f.Td_IdExpSocAportes
        where year(a.Tm_FechaGenEsc) = @anho AND month(a.Tm_FechaGenEsc) = @mes and a.Tm_idRegistro = 6
                     and c.Ta_NatActo = '01' and f.Ta_TipoBien = '02'
                     and not c.Ta_NatActo is null
                    and not c.Tp_IdActo in (17, 24, 33, 104, 34, 147, 47, 125, 148, 119,
40,
39,
38,
37,
36,
35) AND a.Tm_Excluida = 0
UNION ALL

                    select  case a.Tm_idRegistro when 6 then 1 else 5 end as tipoesc,
                     convert(int, SUBSTRING(a.Tm_NumeroEscritura, 6, 5))   as nroescritura,
                    SN.fc_formatearFecha('02', a.tm_fechafirmaesc) as fechaNum,
                    case a.tm_idregistro when 6 then '1' else
                    case c.tp_codigopdt when '09' then '1' else '2' end end as SecuencialActo,
                    e.Tp_IdVehiculo as NroSecuencial,
                    'B' as TipoBien,
                    SYSMART.dbo.ObtenerNaturalezaTaTabla(d.Ta_tipobien, 'Ta_tipobien') as CodBien,
                    UPPER(case len(e.Tp_PlacavVehiculo) when 0 then CASE LEN(e.Tp_Serievehiculo) when 0 then '3' else '2' end else '1' end) as OpcionBien,
                    UPPER(case len(e.Tp_PlacavVehiculo) when 0 then CASE LEN(e.Tp_Serievehiculo) when 0 then e.Tp_Motorvehiculo else e.Tp_Serievehiculo end else e.Tp_PlacavVehiculo end) as NroBien,
                    '' NroSerie,
                    '1' OrigenBien,
                    '140101' UbigeoBien,
                    '' FechaBien,
                        case SYSMART.dbo.ObtenerNaturalezaTaTabla(d.Ta_tipobien, 'Ta_tipobien') when '99' then '-' else '' end DescripBien
                from SN.Tm_Escritura  as a
                    inner join SN.Td_Expediente_Acto as b on a.Tm_IdExpediente = b.Tm_idexpediente
                    inner join SN.Tp_Actos as c on b.Tp_idacto = c.Tp_IdActo
                    inner join SN.Td_Expediente_Gravamen as d on b.Td_idexpedienteacto = d.Td_idexpedienteacto  and  a.Tm_IdExpediente = d.Tm_idexpediente
                    inner join SN.Tp_Vehiculos as e on e.Tp_IdVehiculo = d.Tp_idbien and d.Ta_tipobien = '00'
                    where year(a.Tm_FechaGenEsc) = @anho AND month(a.Tm_FechaGenEsc) = @mes and a.Tm_idRegistro in (6,11)
                    and not c.Ta_NatActo is null
                    and not c.Tp_IdActo in (17, 24, 33, 104, 34, 147, 47, 125, 148, 119,
40,
39,
38,
37,
36,
35) AND a.Tm_Excluida = 0
                     union all

            select  case a.Tm_idRegistro when 6 then 1 else 5 end as tipoesc,
            convert(int, SUBSTRING(a.Tm_NumeroEscritura, 6, 5))   as nroescritura,
                    SN.fc_formatearFecha('02', a.tm_fechafirmaesc) as fechaNum,
                    case a.tm_idregistro when 6 then '1' else
                    case c.tp_codigopdt when '09' then '1' else '2' end end as SecuencialActo,
                    f.Tp_IdPredio as NroSecuencial,
                    'B' as TipoBien,
                    SYSMART.dbo.ObtenerNaturalezaTaTabla(d.Ta_tipobien, 'Ta_tipobien') as CodBien,
                    '' as OpcionBien,
                    '' as NroBien,
                    '' NroSerie,
                    isnull(i.Origen, '1') OrigenBien,
                    case isnull(i.Origen, '1') when '1' then isnull(i.Tm_CodigoPDT, '140101') else ISNULL(i.CodigoPDTPais, '589') end as UbigeoBien,
                    '' FechaBien,
                        case SYSMART.dbo.ObtenerNaturalezaTaTabla(d.Ta_tipobien, 'Ta_tipobien') when '99' then case len(isnull(f.Tp_Anotacionespredios, '-')) when 0 then '-' else isnull(f.Tp_Anotacionespredios, '-')  end else '' end DescripBien
                from SN.Tm_Escritura  as a
                    inner join SN.Td_Expediente_Acto as b on a.Tm_IdExpediente = b.Tm_idexpediente
                    inner join SN.Tp_Actos as c on b.Tp_idacto = c.Tp_IdActo
                    inner join SN.Td_Expediente_Gravamen as d on b.Td_idexpedienteacto = d.Td_idexpedienteacto  and  a.Tm_IdExpediente = d.Tm_idexpediente
                    inner join SN.Tp_Predios as f on d.Tp_idbien = f.Tp_IdPredio and d.Ta_tipobien = '01'
                    left join SYSMART.dbo.fc_ubigeo(0) as i on f.Tm_idUbigeo = i.tm_idubigeo
                    where year(a.Tm_FechaGenEsc) = @anho AND month(a.Tm_FechaGenEsc) = @mes and a.Tm_idRegistro in (6,11)
                    and not c.Ta_NatActo is null
                    and not c.Tp_IdActo in (17, 24, 33, 104, 34, 147, 47, 125, 148, 119,
40,
39,
38,
37,
36,
35) AND a.Tm_Excluida = 0


                    ) as x
                 where tipoesc = @tiporegistro
                    order by  tipoesc, nroescritura



            open curInfoPdtBienes
            fetch next from curInfoPdtBienes into @tipoesc,
            @nroescritura,
            @fechaNum,
            @secuenciaActo,
            @SecuenciaBien,
            @TipoBien,
            @CodBien,
            @OpcionBien,
            @NroBien,
            @SerieBien,
            @OrigenBien,
            @UbigeoBien,
            @FechaBien,
            @DescripBien
            while @@FETCH_STATUS = 0
                begin
                    /*if LEN(@strContenidoPDT) > 0
                        begin
                            set @strContenidoPDT = @strContenidoPDT + CHAR(13)
                        end*/
                    if @aux <> @nroescritura
                        begin
                            set @i = 0
                        end

                    set @i = @i + 1

                    set @strContenidoPDT =
                    @tipoesc + '|' + convert(varchar(5), @nroescritura) + '|'
                    + @fechaNum + '|' + @secuenciaActo + '|'
                    + Convert(varchar(max), @i) + '|' + @TipoBien + '|'
                    + @CodBien + '|' + @OpcionBien + '|'
                    + @NroBien + '|' + @SerieBien + '|' + @OrigenBien + '|'
                    + @UbigeoBien + '|' + @FechaBien + '|' + @DescripBien + '|'


                    insert into @xPDT (NomArchivo, strContenido)
                    values (@nomArchivo, @strContenidoPDT)

                    set @aux = @nroescritura

                    fetch next from curInfoPdtBienes into @tipoesc,
                    @nroescritura,
                    @fechaNum,
                    @secuenciaActo,
                    @SecuenciaBien,
                    @TipoBien,
                    @CodBien,
                    @OpcionBien,
                    @NroBien,
                    @SerieBien,
                    @OrigenBien,
                    @UbigeoBien,
                    @FechaBien,
                    @DescripBien
                end
            close curInfoPdtBienes
            deallocate curInfoPdtBienes


        end
    if @tipo = '02'
        begin
            delete from dbo.SecuenciaOtorgante
            DBCC CHECKIDENT ('SecuenciaOtorgante', RESEED, 0)
            declare @cuentaPorcj int,
                    @idexpactox int,
                    @auxIdExpActo int,
                    @auxDefTipoInterv varchar(2),
                    @TaTipoDefInterv varchar(2)
            set @cuentaPorcj = 0
            set @idexpactox = 0
            set @TaTipoDefInterv = ''
            set @aux = ''
            set @auxIdExpActo = 0
            set @auxDefTipoInterv = ''
            set @nomArchivo = @nomArchivo + '.OTG'

            declare @xParticipacion table (
                Ta_DefTipoInterv varchar(2),
                Td_IdExpedienteActo int,
                CountPersona int
            )

            insert into @xParticipacion
                    select c.Ta_DefTipoInterv, a.Td_IdExpedienteActo, COUNT(a.tp_idpersona) as CountPersona
                    from sn.td_expediente_interv as a
                        inner join sn.tp_tipointerv as c on a.Tp_IdTipointerv = c.Tp_IdTipoInterv
                        inner join SN.Tm_Escritura as d on a.Tm_IdExpediente = d.Tm_IdExpediente
                        inner join SN.Td_Expediente_Acto as e on a.Td_IdExpedienteActo = e.Td_idexpedienteacto
                        inner join SN.Tp_Actos as f on e.Tp_idacto = f.Tp_IdActo
                    where c.Ta_DefTipoInterv in ('00', '01') and c.Tp_IdTipointerv <> 15 and year(d.Tm_FechaGenEsc) = @anho AND month(d.Tm_FechaGenEsc) = @mes
                     and d.Tm_idRegistro = @idRegDesdeTipoReg
                    and not f.Ta_NatActo is null
                    and not f.Tp_IdActo in (17, 24, 33, 104, 34, 147, 47, 125, 148, 119,
40,
39,
38,
37,
36,
35)
                     AND d.Tm_Excluida = 0
                    group by c.Ta_DefTipoInterv, a.Td_IdExpedienteActo

declare curInfoPDTOtorgantes cursor local for
select case a.Tm_idRegistro when 6 then 1 when 9 then 2 else 5 end as tipoesc,
 convert(int, SUBSTRING(a.Tm_NumeroEscritura, 6, 5)) ,
                    SN.fc_formatearFecha('02', a.tm_fechafirmaesc) as fechaNum,
                    '1',
                    '',
                    convert(varchar(max), h.Tp_IdPersona),
                    SYSMART.ObtenerNaturalezaTaTabla(h.Ta_TipoIdentifica, 'Ta_TipoIdentifica') as TipoDoc,
                    case SYSMART.ObtenerNaturalezaTaTabla(h.Ta_TipoIdentifica, 'Ta_TipoIdentifica') when '-' then '' else h.Numero end as NroDoc,
                    case a.Tm_idRegistro
                        when 6 then
                            case c.Tp_CodigoPDT
                                when '01' then
                                    case i.Ta_DefTipoInterv when '01' then '01' when '00' then '02' end
                                when '02' then
                                    case i.Ta_DefTipoInterv when '01' then '03' when '00' then '04' end
                                when '03' then
                                    case i.Ta_DefTipoInterv when '01' then '05' when '00' then '06' end
                                when '04' then
                                    case i.Ta_DefTipoInterv when '01' then '01' when '00' then '02' end
                                when '06' then
                                    case i.Ta_DefTipoInterv when '01' then '03' when '00' then '04' end
                                when '07' then
                                    case i.Ta_DefTipoInterv when '01' then '20' when '00' then '21' end
                                when '08' then
                                    case i.Ta_DefTipoInterv when '01' then '13' when '00' then '40' end
                                when '09' then
                                    case i.Ta_DefTipoInterv when '01' then '14' when '00' then '15' end
                                when '10' then
                                    case i.Ta_DefTipoInterv when '01' then '07' when '00' then '08' end
                                when '11' then
                                    case i.Ta_DefTipoInterv when '01' then '19' when '00' then '' end
                                when '12' then
                                    case i.Ta_DefTipoInterv when '01' then '19' when '00' then '19' end
                                when '13' then
                                    case i.Ta_DefTipoInterv when '01' then '09' when '00' then '10' end
                                when '14' then
                                    case i.Ta_DefTipoInterv when '01' then '11' when '00' then '' end
                                when '15' then
                                    case i.Ta_DefTipoInterv when '01' then '26' when '00' then '27' end
                                when '16' then
                                    case i.Ta_DefTipoInterv when '01' then '28' when '00' then '' end
                                when '17' then
                                    case i.Ta_DefTipoInterv when '01' then '24' when '00' then '25' end
                                when '18' then
                                    case i.Ta_DefTipoInterv when '01' then '22' when '00' then '23' end
                                when '19' then
                                    case i.Ta_DefTipoInterv when '01' then '16' when '00' then '17' end
                                when '20' then
                                    case i.Ta_DefTipoInterv when '01' then '03' when '00' then '04' end
                                when '21' then
                                    case i.Ta_DefTipoInterv when '01' then '18' when '00' then '' end
                                when '22' then
                                    case i.Ta_DefTipoInterv when '01' then '01' when '00' then '02' end
                                when '23' then
                                    case i.Ta_DefTipoInterv when '01' then '01' when '00' then '02' end
                            end
                        when 9 then
                            case i.Ta_DefTipoInterv when '01' then '01' when '00' then '02' end
                        when 11 then
                            case c.Tp_CodigoPDT
                                when '09' then
                                    case i.Ta_DefTipoInterv when '01' then '14' when '00' then '15' end
                                when '12' then
                                    case i.Ta_DefTipoInterv when '01' then '19' when '00' then '19' end
                        end
                    end tipoOtorgante,
                    case h.Tp_Idtipopers when '00' then '2' else '1' end as TipoPer,
                    case when len( isnull(k.Tm_CodigoPDT, '') ) = 6 then isnull(k.Tm_CodigoPDT, '') else '' end as Ubigeo,
                    convert(varchar(max), round(100 / j.CountPersona, 2)) as porcentaje,
                    case h.Tp_Idtipopers when '00' then h.RazonSocial else '' end as RazonSocial,
                    h.ApePaterno,
                    h.ApeMaterno,
                    case PATINDEX('% %',rtrim(ltrim(h.Nombres)))
                        when 0 then rtrim(ltrim(h.Nombres))
                        else substring(rtrim(ltrim(h.Nombres)),0,(PATINDEX('% %',rtrim(ltrim(h.Nombres))))) end as Nombre1,
                    case PATINDEX('% %',rtrim(ltrim(h.Nombres)))
                        when 0 then '' else substring(rtrim(ltrim(h.Nombres)),(PATINDEX('% %',rtrim(ltrim(h.Nombres))) + 1), len(rtrim(ltrim(h.Nombres)))) end as Nombre2,
                    case h.Tp_Idtipopers
                        when '00' then ''
                        else
                            case a.Tm_idRegistro
                                when 6 then
                                    case c.Tp_CodigoPDT
                                        when '01' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '0' end
                                        when '02' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '03' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '04' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '0' end
                                        when '06' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '07' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '08' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '09' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '10' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '11' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '12' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '13' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '14' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '15' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '16' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '17' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '18' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '19' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '20' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '21' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '22' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '0' end
                                        when '23' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '0' end
                                    end
                                when 9 then
                                    case i.Ta_DefTipoInterv when '01' then '' when '00' then '0' end
                                when 11 then
                                    case c.Tp_CodigoPDT
                                        when '09' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '12' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        end
                            end
                        end as TieneEnajTerceraCat,
                        case h.Tp_Idtipopers
                        when '00' then ''
                        else
                            case a.Tm_idRegistro
                            when 6 then
                                case c.Tp_CodigoPDT
                                    when '01' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '0' end
                                    when '02' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '03' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '04' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '0' end
                                    when '06' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '07' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '08' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '09' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '10' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '11' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '12' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '13' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '14' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '15' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '16' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '17' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '18' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '19' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '20' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '21' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '22' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '0' end
                                    when '23' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '0' end
                                    end
                                when 9 then
                                    case i.Ta_DefTipoInterv when '01' then '' when '00' then '0' end
                                when 11 then
                                    case c.Tp_CodigoPDT
                                    when '09' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '12' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    end
                            end
                        end as EraSuCasa,
                        case h.Tp_Idtipopers  when '01' then '0' else '' end as NroOperacion,
                        j.CountPersona ,
                        b.Td_idexpedienteacto,
                        i.Ta_DefTipoInterv
                    from SN.Tm_Escritura  as a
                        inner join SN.Td_Expediente_Acto as b on a.Tm_IdExpediente = b.Tm_idexpediente
                        inner join SN.Tp_Actos as c on b.Tp_idacto = c.Tp_IdActo
                        inner join SN.Td_Expediente_Interv as f on b.Td_idexpedienteacto = f.Td_IdExpedienteActo
                        inner join SN.vstInterviniente as h on f.Tp_IdPersona = h.Tp_idpersona
                        inner join SN.Tp_TipoInterv as i on f.Tp_IdTipointerv = i.Tp_IdTipoInterv
                        inner join @xParticipacion j on b.Td_idexpedienteacto = j.Td_IdExpedienteActo and i.Ta_DefTipoInterv = j.Ta_DefTipoInterv
                        inner join SYSMART.dbo.fc_ubigeo(0) k on h.Tm_idubigeo = k.tm_idubigeo
                        where year(a.Tm_FechaGenEsc) = @anho AND month(a.Tm_FechaGenEsc) = @mes
                        and i.Ta_DefTipoInterv in ('00', '01')
                        and f.Tp_IdTipointerv <> 15
                        and a.Tm_idRegistro = @idRegDesdeTipoReg
                         AND a.Tm_Excluida = 0
                        and not c.Ta_NatActo is null
                    and not c.Tp_IdActo in (17, 24, 33, 104, 34, 147, 47, 125, 148, 119,
40,
39,
38,
37,
36,
35)
                         order by tipoesc, a.Tm_NumeroEscritura, tipoOtorgante
            open curInfoPDTOtorgantes
            fetch next from curInfoPDTOtorgantes  into @tipoesc,
            @nroescritura,
            @fechaNum,
            @secuenciaActo,
            @SecuenciaBien,
            @SecuenciaOtorgante,
            @TipoDoc,
            @NroDoc,
            @TipoOtorgante,
            @TipoPer,
            @ubigeoPer,
            @Porcentaje,
            @RazonSocial,
            @ApellidoPat,
            @ApellidoMat,
            @Nombre1,
            @Nombre2,
            @Gen3Cat,
            @EraSuCasa,
            @nroOperacion,
            @CuentaOTG,
            @idexpactox,
            @TaTipoDefInterv
            while @@FETCH_STATUS = 0
                begin
                    /*if LEN(@strContenidoPDT) > 0
                        begin
                            set @strContenidoPDT = @strContenidoPDT + CHAR(13)
                        end*/


                    if @aux <> @nroescritura
                        begin
                            set @i = 0
                        end

                    set @i = @i + 1

                    if @auxIdExpActo = @idexpactox
                        begin
                            if @auxDefTipoInterv <> @TipoOtorgante
                                begin
                                    set @cuentaPorcj = 0
                                end
                        end
                    else
                        begin
                            set @cuentaPorcj = 0
                        end
                    if @CuentaOTG > 1
                        begin
                            set @cuentaPorcj = @cuentaPorcj + CONVERT(int, @Porcentaje)
                            if (100 - @cuentaPorcj) > 0
                                begin
                                    if (100 - @cuentaPorcj) < CONVERT(int, @Porcentaje)
                                        begin
                                            set @Porcentaje = CONVERT(varchar(max), CONVERT(int, @Porcentaje) + (100 - @cuentaPorcj))
                                        end
                                end
                            print @nroescritura + ' ' + convert(varchar(max), @cuentaPorcj) + ' ' + @auxDefTipoInterv
                        end

                    set @RazonSocial = REPLACE( replace( @RazonSocial, 'Ü', 'U' ), 'Ñ', 'N' )
                    set @RazonSocial = REPLACE( replace( @RazonSocial, 'Ú', 'U' ), 'Í', 'I' )
                    set @RazonSocial = REPLACE( replace( @RazonSocial, 'É', 'E' ), 'Ó', 'O' )
                    set @RazonSocial = replace( @RazonSocial, 'Á', 'A' )
                    set @RazonSocial = replace( @RazonSocial, '´', ' ' )
                    set @RazonSocial = replace( @RazonSocial, '-', '' )
                    set @RazonSocial = replace( @RazonSocial, 'º', '' )

                    set @ApellidoPat = REPLACE( replace( @ApellidoPat, 'Ü', 'U' ), 'Ñ', 'N' )
                    set @ApellidoPat = REPLACE( replace( @ApellidoPat, 'Ú', 'U' ), 'Í', 'I' )
                    set @ApellidoPat = REPLACE( replace( @ApellidoPat, 'É', 'E' ), 'Ó', 'O' )
                    set @ApellidoPat = replace( @ApellidoPat, 'Á', 'A' )
                    set @ApellidoPat = replace( @ApellidoPat, '´', ' ' )
                    set @ApellidoPat = replace( @ApellidoPat, '-', '' )
                    set @ApellidoPat = replace( @ApellidoPat, 'º', '' )

                    set @ApellidoMat = REPLACE( replace( @ApellidoMat, 'Ü', 'U' ) , 'Ñ', 'N')
                    set @ApellidoMat = REPLACE( replace( @ApellidoMat, 'Ú', 'U' ), 'Í', 'I' )
                    set @ApellidoMat = REPLACE( replace( @ApellidoMat, 'É', 'E' ), 'Ó', 'O' )
                    set @ApellidoMat = replace( @ApellidoMat, 'Á', 'A' )
                    set @ApellidoMat = replace( @ApellidoMat, '´', ' ' )
                    set @ApellidoMat = replace( @ApellidoMat, '-', '' )
                    set @ApellidoMat = replace( @ApellidoMat, 'º', '' )

                    set @Nombre1 = REPLACE( replace( @Nombre1, 'Ü', 'U' ) , 'Ñ', 'N')
                    set @Nombre1 = REPLACE( replace( @Nombre1, 'Ú', 'U' ), 'Í', 'I' )
                    set @Nombre1 = REPLACE( replace( @Nombre1, 'É', 'E' ), 'Ó', 'O' )
                    set @Nombre1 = replace( @Nombre1, 'Á', 'A' )
                    set @Nombre1 = replace( @Nombre1, '´', ' ' )
                    set @Nombre1 = replace( @Nombre1, '-', '' )
                    set @Nombre1 = replace( @Nombre1, 'º', '' )

                    set @Nombre2 = REPLACE( replace( @Nombre2, 'Ü', 'U' ) , 'Ñ', 'N')
                    set @Nombre2 = REPLACE( replace( @Nombre2, 'Ú', 'U' ), 'Í', 'I' )
                    set @Nombre2 = REPLACE( replace( @Nombre2, 'É', 'E' ), 'Ó', 'O' )
                    set @Nombre2 = replace( @Nombre2, 'Á', 'A' )
                    set @Nombre2 = replace( @Nombre2, '´', ' ' )
                    set @Nombre2 = replace( @Nombre2, '-', '' )
                    set @Nombre2 = replace( @Nombre2, 'º', '' )

                    set @strContenidoPDT =
                    @tipoesc + '|' +  @nroescritura + '|' + @fechaNum +
                    '|' + @secuenciaActo + '|' + @SecuenciaBien + '|' + Convert(varchar(max), @i) + '|' +
                    @TipoDoc + '|' + @NroDoc + '|' + @TipoOtorgante + '|' + @TipoPer + '|' + @ubigeoPer + '|' + @Porcentaje +
                    '|' + @RazonSocial + '|' + @ApellidoPat + '|' + @ApellidoMat + '|'
                    +  @Nombre1 + '|' + @Nombre2 + '|'
                    + @Gen3Cat + '|' + @EraSuCasa + '|' + @nroOperacion + '|'

                    insert into @xPDT (NomArchivo, strContenido)
                    values (@nomArchivo, @strContenidoPDT)


                    set @aux = @nroescritura
                    set @auxDefTipoInterv = @TipoOtorgante
                    set @auxIdExpActo = @idexpactox

                    insert into dbo.SecuenciaOtorgante (IdExpActo, IdPersona, NroSecuencial)
                    values (@idexpactox, Convert(int, @SecuenciaOtorgante), @i)


                    fetch next from curInfoPDTOtorgantes  into @tipoesc,
                    @nroescritura,
                    @fechaNum,
                    @secuenciaActo,
                    @SecuenciaBien,
                    @SecuenciaOtorgante,
                    @TipoDoc,
                    @NroDoc,
                    @TipoOtorgante,
                    @TipoPer,
                    @ubigeoPer,
                    @Porcentaje,
                    @RazonSocial,
                    @ApellidoPat,
                    @ApellidoMat,
                    @Nombre1,
                    @Nombre2,
                    @Gen3Cat,
                    @EraSuCasa,
                    @nroOperacion,
                    @CuentaOTG,
                    @idexpactox,
                    @TaTipoDefInterv
                end

        end
    if @tipo = '03'
        begin
            set @nomArchivo = @nomArchivo + '.MPD'

            declare curMedioPago cursor local for
                select * from (
                        select  2 as tipoesc,
                                convert(int, SUBSTRING(a.Tm_NumeroEscritura, 6, 5)) Tm_NumeroEscritura,
                                '1' as SecuencialActo,
                                SYSMART.dbo.ObtenerNaturalezaTaTabla(Ta_MedioPago, 'Ta_MedioPago') MedioPago,
                                isnull(e.tm_codigoPDT, '2') as PDTMonedaMP,
                                convert(varchar(max), isnull(d.Td_ValorExpVeh, 0)) as MontoPagado,
                                SN.fc_formatearFecha('02',a.Tm_FechaGenEsc) as fechaPago,
                                 case when SYSMART.dbo.ObtenerNaturalezaTaTabla(Ta_MedioPago, 'Ta_MedioPago') in ('008', '009') then '' else  '-' end as nroDocPago,
                                case when SYSMART.dbo.ObtenerNaturalezaTaTabla(Ta_MedioPago, 'Ta_MedioPago') in ('008', '009') then '' else  '99' end as EntFinanciera
                        from SN.Tm_Escritura  as a
                                    inner join SN.Td_Expediente_Acto as b on a.Tm_IdExpediente = b.Tm_idexpediente
                                    inner join SN.Tp_Actos as c on b.Tp_idacto = c.Tp_IdActo
                                    inner join SN.Td_Expediente_Vehiculo as d on b.Td_idexpedienteacto = d.Td_IdExpedienteActo and b.Tm_idexpediente = d.Tm_IdExpediente
                                    inner join SYSMART.tm_moneda as e on d.Tm_IdMoneda = e.tm_idmoneda
                         where d.Td_AcreditaMP = 1 and a.Tm_idRegistro = 9 and YEAR(a.Tm_FechaGenEsc) = @anho
                         and not c.Ta_NatActo is null
                    and not c.Tp_IdActo in (17, 24, 33, 104, 34, 147, 47, 125, 148, 119,
40,
39,
38,
37,
36,
35) AND a.Tm_Excluida = 0
                        union all
                        select  1 as tipoesc,
                                convert(int, SUBSTRING(a.Tm_NumeroEscritura, 6, 5)) Tm_NumeroEscritura,
                                '1' as SecuencialActo,
                                SYSMART.dbo.ObtenerNaturalezaTaTabla(Ta_MedioPago, 'Ta_MedioPago') MedioPago,
                                isnull(e.tm_codigoPDT, '2') as PDTMonedaMP,
                                convert(varchar(max), case d.Td_ValorExpTrans when 0 then 1 else d.Td_ValorExpTrans end ) as MontoPagado,
                                SN.fc_formatearFecha('02',a.Tm_FechaGenEsc) as fechaPago,
                                 case when
                                    SYSMART.dbo.ObtenerNaturalezaTaTabla(Ta_MedioPago, 'Ta_MedioPago') in ('008', '009')
                                    then '' else  '-' end as nroDocPago,
                                case when SYSMART.dbo.ObtenerNaturalezaTaTabla(Ta_MedioPago, 'Ta_MedioPago') in ('008', '009')
                                    then '' else  '99' end as EntFinanciera
                        from SN.Tm_Escritura  as a
                                    inner join SN.Td_Expediente_Acto as b on a.Tm_IdExpediente = b.Tm_idexpediente
                                    inner join SN.Tp_Actos as c on b.Tp_idacto = c.Tp_IdActo
                                    inner join SN.Td_Expediente_Transferencia as d on b.Td_idexpedienteacto = d.Td_IdExpedienteActo and b.Tm_idexpediente = d.Tm_IdExpediente
                                    inner join SYSMART.tm_moneda as e on d.Tm_IdMoneda = e.tm_idmoneda
                         where d.Td_AcreditaMP = 1 and a.Tm_idRegistro = 6 and YEAR(a.Tm_FechaGenEsc) = @anho
                         and not c.Ta_NatActo is null
                    and not c.Tp_IdActo in (17, 24, 33, 104, 34, 147, 47, 125, 148, 119,
40,
39,
38,
37,
36,
35) AND a.Tm_Excluida = 0
                    ) x  WHERE tipoesc = @tiporegistro
                      order by tipoesc, Tm_NumeroEscritura

            open curMedioPago
            fetch next from curMedioPago into @tipoesc,
                @nroescritura,
                @secuenciaActo,
                @MedioPago,
                @codMonedaMedPago,
                @montoPagado,
                @fechaPago,
                @nroDocMedPago,
                @entFinanciera
            while @@FETCH_STATUS = 0
                begin
                    set @strContenidoPDT =
                        @tipoesc + '|' + convert(varchar(5),@nroescritura) + '|' + @secuenciaActo + '|' + @MedioPago + '|' + @codMonedaMedPago + '|' + @montoPagado + '|' +
                        @fechaPago + '|' + @nroDocMedPago + '|' + @entFinanciera + '|'

                    insert into @xPDT (NomArchivo, strContenido)
                    values (@nomArchivo, @strContenidoPDT)

                    fetch next from curMedioPago  into @tipoesc,
                        @nroescritura,
                        @secuenciaActo,
                        @MedioPago,
                        @codMonedaMedPago,
                        @montoPagado,
                        @fechaPago,
                        @nroDocMedPago,
                        @entFinanciera
                end
            close curMedioPago
            deallocate curMedioPago
        end
    if @tipo = '04'
        begin
            set @nomArchivo = @nomArchivo + '.FOR'
            set @i = 0
            declare curFormulario cursor local for
                select b.tipoesc,
                    convert(varchar(max), b.NroEscritura),
                    b.Tp_IdActo,
                    CONVERT(varchar(max), b.NroSecuencial),
                    b.tipoOtorgante,
                    '-',
                    '0.1'
                from SN.Td_Expediente_Interv as a
                    inner join (

select case a.Tm_idRegistro when 6 then '1' when 9 then '2' else '4' end as tipoesc,
 convert(int, SUBSTRING(a.Tm_NumeroEscritura, 6, 5)) as NroEscritura,
                    case when a.Tm_idRegistro in (6, 9) then '1' else
                    case c.tp_codigopdt when '09' then '1' else '2' end end Tp_IdActo,
                    SO.NroSecuencial,
                    f.Td_ExpedienteInterv,
                    case a.Tm_idRegistro
                        when 6 then
                            case c.Tp_CodigoPDT
                                when '01' then
                                    case i.Ta_DefTipoInterv when '01' then '01' when '00' then '02' end
                                when '02' then
                                    case i.Ta_DefTipoInterv when '01' then '03' when '00' then '04' end
                                when '03' then
                                    case i.Ta_DefTipoInterv when '01' then '05' when '00' then '06' end
                                when '04' then
                                    case i.Ta_DefTipoInterv when '01' then '01' when '00' then '02' end
                                when '06' then
                                    case i.Ta_DefTipoInterv when '01' then '03' when '00' then '04' end
                                when '07' then
                                    case i.Ta_DefTipoInterv when '01' then '20' when '00' then '21' end
                                when '08' then
                                    case i.Ta_DefTipoInterv when '01' then '13' when '00' then '40' end
                                when '09' then
                                    case i.Ta_DefTipoInterv when '01' then '14' when '00' then '15' end
                                when '10' then
                                    case i.Ta_DefTipoInterv when '01' then '07' when '00' then '08' end
                                when '11' then
                                    case i.Ta_DefTipoInterv when '01' then '19' when '00' then '' end
                                when '12' then
                                    case i.Ta_DefTipoInterv when '01' then '19' when '00' then '' end
                                when '13' then
                                    case i.Ta_DefTipoInterv when '01' then '09' when '00' then '10' end
                                when '14' then
                                    case i.Ta_DefTipoInterv when '01' then '11' when '00' then '' end
                                when '15' then
                                    case i.Ta_DefTipoInterv when '01' then '26' when '00' then '27' end
                                when '16' then
                                    case i.Ta_DefTipoInterv when '01' then '28' when '00' then '' end
                                when '17' then
                                    case i.Ta_DefTipoInterv when '01' then '24' when '00' then '25' end
                                when '18' then
                                    case i.Ta_DefTipoInterv when '01' then '22' when '00' then '23' end
                                when '19' then
                                    case i.Ta_DefTipoInterv when '01' then '16' when '00' then '17' end
                                when '20' then
                                    case i.Ta_DefTipoInterv when '01' then '03' when '00' then '04' end
                                when '21' then
                                    case i.Ta_DefTipoInterv when '01' then '18' when '00' then '' end
                                when '22' then
                                    case i.Ta_DefTipoInterv when '01' then '01' when '00' then '02' end
                                when '23' then
                                    case i.Ta_DefTipoInterv when '01' then '01' when '00' then '02' end
                            end
                        when 9 then
                            case i.Ta_DefTipoInterv when '01' then '01' when '00' then '02' end
                        when 11 then
                            case c.Tp_CodigoPDT
                                when '09' then
                                    case i.Ta_DefTipoInterv when '01' then '14' when '00' then '15' end
                                when '12' then
                                    case i.Ta_DefTipoInterv when '01' then '19' when '00' then '' end
                        end
                    end tipoOtorgante,
                    case h.Tp_Idtipopers when '00' then '2' else '1' end as TipoPer,
                    case h.Tp_Idtipopers
                        when '00' then ''
                        else
                            case a.Tm_idRegistro
                                when 6 then
                                    case c.Tp_CodigoPDT
                                        when '01' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '0' end
                                        when '02' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '03' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '04' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '0' end
                                        when '06' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '07' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '08' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '09' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '10' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '11' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '12' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '13' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '14' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '15' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '16' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '17' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '18' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '19' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '20' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '21' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '22' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '0' end
                                        when '23' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '0' end
                                    end
                                when 9 then
                                    case i.Ta_DefTipoInterv when '01' then '' when '00' then '0' end
                                when 11 then
                                    case c.Tp_CodigoPDT
                                        when '09' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        when '12' then
                                            case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                        end
                            end
                        end as TieneEnajTerceraCat,
                        case h.Tp_Idtipopers
                        when '00' then ''
                        else
                            case a.Tm_idRegistro
                            when 6 then
                                case c.Tp_CodigoPDT
                                    when '01' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '0' end
                                    when '02' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '03' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '04' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '0' end
                                    when '06' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '07' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '08' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '09' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '10' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '11' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '12' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '13' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '14' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '15' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '16' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '17' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '18' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '19' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '20' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '21' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '22' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '0' end
                                    when '23' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '0' end
                                    end
                                when 9 then
                                    case i.Ta_DefTipoInterv when '01' then '' when '00' then '0' end
                                when 11 then
                                    case c.Tp_CodigoPDT
                                    when '09' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '12' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    end
                            end
                        end as EraSuCasa,
                        case h.Tp_Idtipopers
                        when '00' then ''
                        else
                            case a.Tm_idRegistro
                            when 6 then
                                case c.Tp_CodigoPDT
                                    when '01' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '1' end
                                    when '02' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '03' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '04' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '1' end
                                    when '06' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '07' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '08' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '09' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '10' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '11' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '12' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '13' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '14' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '15' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '16' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '17' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '18' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '19' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '20' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '21' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '22' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '1' end
                                    when '23' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '1' end
                                    end
                                when 9 then
                                    case i.Ta_DefTipoInterv when '01' then '' when '00' then '1' end
                                when 11 then
                                    case c.Tp_CodigoPDT
                                    when '09' then
                                        case i.Ta_DefTipoInterv when '01' then '' when '00' then '' end
                                    when '12' then
                                        case i.Ta_DefTipoInterv when '01' then '' when
                                         '00' then '' end
                                    end
                            end
                        end as ImpPagoEsCero
                    from SN.Tm_Escritura  as a
                        inner join SN.Td_Expediente_Acto as b on a.Tm_IdExpediente = b.Tm_idexpediente
                        inner join SN.Tp_Actos as c on b.Tp_idacto = c.Tp_IdActo
                        inner join SN.Td_Expediente_Interv as f on b.Td_idexpedienteacto = f.Td_IdExpedienteActo
                        inner join SN.vstInterviniente as h on f.Tp_IdPersona = h.Tp_idpersona
                        inner join SN.Tp_TipoInterv as i on f.Tp_IdTipointerv = i.Tp_IdTipoInterv
                        inner join SYSMART.dbo.fc_ubigeo(0) k on h.Tm_idubigeo = k.tm_idubigeo
                        inner join dbo.SecuenciaOtorgante SO on b.Td_Idexpedienteacto = SO.IdExpActo and f.Tp_idpersona = SO.IdPersona
                        where year(a.Tm_FechaGenEsc) = @anho AND month(a.Tm_FechaGenEsc) = @mes
                        and i.Ta_DefTipoInterv in ('00', '01') and a.Tm_idRegistro in (6,9,11)
                        and not c.Ta_NatActo is null
                    and not c.Tp_IdActo in (17, 24, 33, 104, 34, 147, 47, 125, 148, 119,
40,
39,
38,
37,
36,
35) AND a.Tm_Excluida = 0

                    ) as b on a.Td_ExpedienteInterv = b.Td_ExpedienteInterv
                    where b.EraSuCasa = '0' and
                    b.TieneEnajTerceraCat = '0' and b.tipoesc = @tiporegistro
                    order by tipoesc, b.NroEscritura
            open curFormulario
            fetch next from curFormulario into @tipoesc,
                @nroescritura,
                @secuenciaActo,
                @SecuenciaOtorgante,
                @TipoOtorgante,
                @nroOperacion,
                @montoPagado
            while @@FETCH_STATUS = 0
                begin
                    set @i = @i + 1

                    set @strContenidoPDT =
                    @tipoesc + '|' + @nroescritura + '|' +
                    @secuenciaActo + '|' + @SecuenciaOtorgante + '|' + @TipoOtorgante + '|'
                     + CONVERT(varchar(max), @i) + '|' + @montoPagado + '|'

                     insert into @xPDT (NomArchivo, strContenido)
                    values (@nomArchivo, @strContenidoPDT)

                    fetch next from curFormulario into @tipoesc,
                    @nroescritura,
                    @secuenciaActo,
                    @SecuenciaOtorgante,
                    @TipoOtorgante,
                    @nroOperacion,
                    @montoPagado
                end
            close curFormulario
            deallocate curFormulario

            --delete from dbo.SecuenciaOtorgante
        end
    if @tipo = '05'
        begin
            declare @NumFolio varchar(max),
                    @TipoLibro varchar(2)

            set @NumFolio = ''
            set @TipoLibro = ''
            set @nomArchivo = @nomArchivo + '.LIB'

            declare curLibroAperCierre cursor local for
            select
                convert(int, SUBSTRING(a.Td_NumeroApertCierre, 6, 5)) ,
                SN.fc_formatearFecha('02', a.Td_FechaRegistro) as FechaLibro,
                --SYSMART.dbo.ObtenerNaturalezaTaTabla(a.Ta_TipoLibroApertCierre, 'Ta_TipoLibroApertCierre') as TipoLibro,
                d.ta_naturaleza as TipoLibro,
                convert(varchar(max), dbo.EXTRACT_YEAR(a.Td_observalibrocierre)) as NumFolio,
                case c.Tp_Idtipopers when '00' then '2' else '1' end as TipoPer,
                c.Numero,
                c.ApePaterno,
                c.ApeMaterno,
                case PATINDEX('% %',rtrim(ltrim(c.Nombres)))
                        when 0 then rtrim(ltrim(c.Nombres))
                        else substring(rtrim(ltrim(c.Nombres)),0,(PATINDEX('% %',rtrim(ltrim(c.Nombres))))) end as Nombre1,
                    case PATINDEX('% %',rtrim(ltrim(c.Nombres)))
                        when 0 then '' else substring(rtrim(ltrim(c.Nombres)),(PATINDEX('% %',rtrim(ltrim(c.Nombres))) + 1), len(rtrim(ltrim(c.Nombres)))) end as Nombre2,
                c.RazonSocial
                from SN.Td_Expediente_AperturaCierre a
                inner join SN.Td_Expediente_Interv b
                    on a.Tm_IdExpediente = b.Tm_IdExpediente
                    and a.Td_IdexpedienteActo = b.Td_IdExpedienteActo
                inner join SN.vstInterviniente c on b.Tp_IdPersona = c.Tp_idpersona
                inner join SYSMART.dbo.ta_tabla d on a.Ta_TipoLibroApertCierre = d.ta_codigo and d.ta_campo = 'Ta_TipoLibroApertCierre'
                where year(a.Td_FechaRegistro) = @anho AND month(a.Td_FechaRegistro) = @mes
                and isnull(d.ta_naturaleza, '') <> ''
                --and a.Ta_TipoLibroApertCierre = '02'
                --and convert(int, dbo.EXTRACT_YEAR(a.Td_observalibrocierre)) > 0
                and ISNUMERIC ( c.Numero ) = 1
                and LEN(c.Numero) = 11

                order by a.Td_NumeroApertCierre

            open curLibroAperCierre
            fetch next from curLibroAperCierre  into @nroescritura,
            @fechaNum,
            @TipoLibro,
            @NumFolio,
            @TipoPer,
            @NroDoc,
            @ApellidoPat,
            @ApellidoMat,
            @Nombre1,
            @Nombre2,
            @RazonSocial
            while @@FETCH_STATUS = 0
                begin
                    set @RazonSocial = REPLACE( replace( @RazonSocial, 'Ü', 'U' ), 'Ñ', 'N' )
                    set @RazonSocial = REPLACE( replace( @RazonSocial, 'Ú', 'U' ), 'Í', 'I' )
                    set @RazonSocial = REPLACE( replace( @RazonSocial, 'É', 'E' ), 'Ó', 'O' )
                    set @RazonSocial = replace( @RazonSocial, 'Á', 'A' )

                    set @ApellidoPat = REPLACE( replace( @ApellidoPat, 'Ü', 'U' ), 'Ñ', 'N' )
                    set @ApellidoPat = REPLACE( replace( @ApellidoPat, 'Ú', 'U' ), 'Í', 'I' )
                    set @ApellidoPat = REPLACE( replace( @ApellidoPat, 'É', 'E' ), 'Ó', 'O' )
                    set @ApellidoPat = replace( @ApellidoPat, 'Á', 'A' )

                    set @ApellidoMat = REPLACE( replace( @ApellidoMat, 'Ü', 'U' ) , 'Ñ', 'N')
                    set @ApellidoMat = REPLACE( replace( @ApellidoMat, 'Ú', 'U' ), 'Í', 'I' )
                    set @ApellidoMat = REPLACE( replace( @ApellidoMat, 'É', 'E' ), 'Ó', 'O' )
                    set @ApellidoMat = replace( @ApellidoMat, 'Á', 'A' )

                    set @Nombre1 = REPLACE( replace( @Nombre1, 'Ü', 'U' ) , 'Ñ', 'N')
                    set @Nombre1 = REPLACE( replace( @Nombre1, 'Ú', 'U' ), 'Í', 'I' )
                    set @Nombre1 = REPLACE( replace( @Nombre1, 'É', 'E' ), 'Ó', 'O' )
                    set @Nombre1 = replace( @Nombre1, 'Á', 'A' )

                    set @Nombre2 = REPLACE( replace( @Nombre2, 'Ü', 'U' ) , 'Ñ', 'N')
                    set @Nombre2 = REPLACE( replace( @Nombre2, 'Ú', 'U' ), 'Í', 'I' )
                    set @Nombre2 = REPLACE( replace( @Nombre2, 'É', 'E' ), 'Ó', 'O' )
                    set @Nombre2 = replace( @Nombre2, 'Á', 'A' )

                    set @strContenidoPDT =
                    @nroescritura + '|' + @fechaNum + '|' + @TipoLibro + '|' + @NumFolio + '|' +
                    @TipoPer + '|' + @NroDoc + '|' + @ApellidoPat + '|' + @ApellidoMat + '|' +  @Nombre1 + '|' + @Nombre2 + '|' + @RazonSocial + '|'

                    insert into @xPDT (NomArchivo, strContenido)
                    values (@nomArchivo, @strContenidoPDT)

                    fetch next from curLibroAperCierre  into @nroescritura,
                    @fechaNum,
                    @TipoLibro,
                    @NumFolio,
                    @TipoPer,
                    @NroDoc,
                    @ApellidoPat,
                    @ApellidoMat,
                    @Nombre1,
                    @Nombre2,
                    @RazonSocial
                end
        end
    PRINT @strContenidoPDT
    select * from @xPDT
end