pa_reporteConsoliadoRO

USE [SNSMART_ANALISIS]
GO
/****** Object:  StoredProcedure [SN].[pa_reporteConsoliadoRO]    Script Date: 27/01/2017 10:00:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [SN].[pa_reporteConsoliadoRO] (
    @fechaini datetime,
    @fechafin datetime
)
AS
BEGIN






SELECT case MONTH(@fechaini)when 1 then 'ENERO'
                when 2 then 'FEBRERO'
                when 3 then 'MARZO'
                when 4 then 'ABRIL'
                when 5 then 'MAYO'
                when 6 then 'JUNIO'
                when 7 then 'JULIO'
                when 8 then 'AGOSTO'
                when 9 then 'SEPTIEMBRE'
                when 10 then 'OCTUBRE'
                when 11 then 'NOVIEMBRE'
                else 'DICIEMBRE' end as MES, COUNT(nroescritura), SUM(MontoOperacion) AS Total FROM (
select distinct * from (

            select '2' as tipoesc,
             convert(int, SUBSTRING(a.Tm_NumeroEscritura, 6, 5)) nroescritura,
                                SN.fc_formatearFecha('03', a.tm_fechafirmaesc) as fechaNum,
                s.tm_codigoROF + ': ' + s.tm_nombreROF as Moneda,
                p.Td_ValorExpVeh as MontoOperacion
                --,
                --              convert(varchar(max), round(100 / j.CountPersona, 2)) as porcentaje,
                --                  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.vstInterviniente2 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.ta_tabla as l on h.Ta_TipoIdentifica = l.ta_codigo and l.ta_campo = 'Ta_TipoIdentifica'
                                    left join SYSMART.dbo.ta_tabla as m on h.Ta_Nacionalidad = m.ta_codigo and m.ta_campo = 'Ta_Nacionalidad'
                                    left join SYSMART.dbo.ta_tabla as n on h.Ta_EstCivil = n.ta_codigo and n.ta_campo = 'Ta_EstCivil'

                                    inner join SYSMART.dbo.tm_ubigeo as oDist on h.tm_idubigeo = oDist.tm_idubigeo
                                    inner join SYSMART.dbo.tm_ubigeo as oProv2 on oDist.tm_refcodigo_ubi = oProv2.tm_idubigeo
                                    inner join SYSMART.dbo.tm_ubigeo as oDep1 on oProv2.tm_refcodigo_ubi = oDep1.tm_idubigeo

                                    inner join SN.Td_Expediente_Vehiculo as p on b.Td_idexpedienteacto = p.Td_IdExpedienteActo
                                    inner join SYSMART.dbo.tm_forma_pago as q on p.Tm_Idformapago = q.tm_idformapago

                                    inner join SYSMART.dbo.ta_tabla as r on p.Ta_MedioPago = r.ta_codigo and r.ta_campo = 'Ta_MedioPago'

                                    inner join SYSMART.dbo.Tm_moneda as s on p.Tm_IdMoneda = s.tm_idmoneda
                                    inner join SN.Tp_Vehiculos as t on p.Tp_IdVehiculo = t.Tp_IdVehiculo
                                where  a.Tm_FechaGenEsc >= @fechaini and a.Tm_FechaGenEsc <= @fechafin
                                    and i.Ta_DefTipoInterv in ('00', '01')
                                    and f.Tp_IdTipointerv <> 15
                                    and a.Tm_idRegistro = 9
                                    and not a.Tm_idescritura in (4717, 4744, 5696, 6354, 6476, 6482, 6541, 7126, 7152, 7380, 8085, 8147, 8151, 8154, 8163, 8198, 8244, 8304, 8489, 8516)  --- ESCRITURAS EXCLUIDAS
                                    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 case s.tm_codigoROF when 'PEN' then (p.Td_ValorExpVeh / 2.821) when 'USD' then p.Td_ValorExpVeh else (p.Td_ValorExpVeh / 3.521) end > 2500
union all

            select
            '1' as tipoesc,
            convert(int, SUBSTRING(Tm_NumeroEscritura, 6, 5)) nroescritura,
            SN.fc_formatearFecha('03', Tm_FechaGenEsc) as fechaNum,
                Moneda,
                MontoOperacion from (

            select a.Tm_NumeroEscritura,
                                a.Tm_FechaGenEsc,
                s.tm_codigoROF + ': ' + s.tm_nombreROF as Moneda,
                p.Td_ValorExpTrans as MontoOperacion
                --,
                --              convert(varchar(max), round(100 / j.CountPersona, 2)) as porcentaje,
                --                  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.vstInterviniente2 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.ta_tabla as l on h.Ta_TipoIdentifica = l.ta_codigo and l.ta_campo = 'Ta_TipoIdentifica'
                                    left join SYSMART.dbo.ta_tabla as m on h.Ta_Nacionalidad = m.ta_codigo and m.ta_campo = 'Ta_Nacionalidad'
                                    left join SYSMART.dbo.ta_tabla as n on h.Ta_EstCivil = n.ta_codigo and n.ta_campo = 'Ta_EstCivil'

                                    inner join SYSMART.dbo.tm_ubigeo as oDist on h.tm_idubigeo = oDist.tm_idubigeo
                                    inner join SYSMART.dbo.tm_ubigeo as oProv2 on oDist.tm_refcodigo_ubi = oProv2.tm_idubigeo
                                    inner join SYSMART.dbo.tm_ubigeo as oDep1 on oProv2.tm_refcodigo_ubi = oDep1.tm_idubigeo

                                    inner join SN.Td_Expediente_Transferencia as p on b.Td_idexpedienteacto = p.Td_IdExpedienteActo
                                    inner join SYSMART.dbo.tm_forma_pago as q on p.Tm_Idformapago = q.tm_idformapago

                                    inner join SYSMART.dbo.ta_tabla as r on p.Ta_MedioPago = r.ta_codigo and r.ta_campo = 'Ta_MedioPago'

                                    inner join SYSMART.dbo.Tm_moneda as s on p.Tm_IdMoneda = s.tm_idmoneda
                                    inner join SN.Tp_Predios as t on p.Tp_IdBien = t.Tp_IdPredio
                                    inner join SN.Tp_OficinaReg as u on t.Tp_IdOficinaReg = u.Tp_IdOficinaReg
                                where  a.Tm_FechaGenEsc >= @fechaini and a.Tm_FechaGenEsc <= @fechafin
                                    and i.Ta_DefTipoInterv in ('00', '01')
                                    and f.Tp_IdTipointerv <> 15
                                    and a.Tm_idRegistro = 6
                                    and not a.Tm_idescritura in (4717, 4744, 5696, 6354, 6476, 6482, 6541, 7126, 7152, 7380, 8085, 8147, 8151, 8154, 8163, 8198, 8244, 8304, 8489, 8516)  --- ESCRITURAS EXCLUIDAS
                                                                and not c.Ta_NatActo is null
                            and b.Tp_idacto not in (35, 36, 37, 38, 39, 40)
                            and case s.tm_codigoROF when 'PEN' then (p.Td_ValorExpTrans / 2.821) when 'USD' then p.Td_ValorExpTrans else (p.Td_ValorExpTrans / 3.521) end > 2500

        UNION ALL
        --- CONSTITUCIONES CON PREDIOS

            select

             a.Tm_NumeroEscritura,
                                a.Tm_FechaGenEsc,
                s.tm_codigoROF + ': ' + s.tm_nombreROF as Moneda,
                d.Td_CapitalSocial as MontoOperacion
            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.vstInterviniente2 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 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 g on e.Td_IdExpSocAportes = g.Td_IdExpSocAportes and g.Ta_TipoBien = '01'

                    inner join SN.Tp_Predios as k on g.Tp_IdBien = k.Tp_IdPredio
                    inner join SN.Tp_OficinaReg as u on k.Tp_IdOficinaReg = u.Tp_IdOficinaReg

                    inner join SYSMART.dbo.ta_tabla as l on h.Ta_TipoIdentifica = l.ta_codigo and l.ta_campo = 'Ta_TipoIdentifica'
                    left join SYSMART.dbo.ta_tabla as m on h.Ta_Nacionalidad = m.ta_codigo and m.ta_campo = 'Ta_Nacionalidad'
                    left join SYSMART.dbo.ta_tabla as n on h.Ta_EstCivil = n.ta_codigo and n.ta_campo = 'Ta_EstCivil'

                    inner join SYSMART.dbo.tm_ubigeo as oDist on h.tm_idubigeo = oDist.tm_idubigeo
                    inner join SYSMART.dbo.tm_ubigeo as oProv2 on oDist.tm_refcodigo_ubi = oProv2.tm_idubigeo
                    inner join SYSMART.dbo.tm_ubigeo as oDep1 on oProv2.tm_refcodigo_ubi = oDep1.tm_idubigeo

                    inner join SYSMART.dbo.Tm_moneda as s on d.Tm_IdMoneda = s.tm_idmoneda

                where  a.Tm_FechaGenEsc >= @fechaini and a.Tm_FechaGenEsc <= @fechafin
                    and i.Ta_DefTipoInterv in ('00', '01')
                    and f.Tp_IdTipointerv <> 15
                    and a.Tm_idRegistro = 6
                    and not a.Tm_idescritura in (4717, 4744, 5696, 6354, 6476, 6482, 6541, 7126, 7152, 7380, 8085, 8147, 8151, 8154, 8163, 8198, 8244, 8304, 8489, 8516)  --- ESCRITURAS EXCLUIDAS
                    and not c.Ta_NatActo is null
                    and b.Tp_idacto in (35, 36, 37, 38, 39, 40)


            UNION ALL
            --- CONSTITUCIONES CON VEHICULOS

            select

            a.Tm_NumeroEscritura,
                                a.Tm_FechaGenEsc,
                s.tm_codigoROF + ': ' + s.tm_nombreROF as Moneda,
                 d.Td_CapitalSocial as MontoOperacion
            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.vstInterviniente2 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 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 g on e.Td_IdExpSocAportes = g.Td_IdExpSocAportes  and g.Ta_TipoBien = '00'

                    inner join SN.Tp_Vehiculos as k on g.Tp_IdBien = k.Tp_IdVehiculo


                    inner join SYSMART.dbo.ta_tabla as l on h.Ta_TipoIdentifica = l.ta_codigo and l.ta_campo = 'Ta_TipoIdentifica'
                    left join SYSMART.dbo.ta_tabla as m on h.Ta_Nacionalidad = m.ta_codigo and m.ta_campo = 'Ta_Nacionalidad'
                    left join SYSMART.dbo.ta_tabla as n on h.Ta_EstCivil = n.ta_codigo and n.ta_campo = 'Ta_EstCivil'

                    inner join SYSMART.dbo.tm_ubigeo as oDist on h.tm_idubigeo = oDist.tm_idubigeo
                    inner join SYSMART.dbo.tm_ubigeo as oProv2 on oDist.tm_refcodigo_ubi = oProv2.tm_idubigeo
                    inner join SYSMART.dbo.tm_ubigeo as oDep1 on oProv2.tm_refcodigo_ubi = oDep1.tm_idubigeo

                    inner join SYSMART.dbo.Tm_moneda as s on d.Tm_IdMoneda = s.tm_idmoneda

                where  a.Tm_FechaGenEsc >= @fechaini and a.Tm_FechaGenEsc <= @fechafin
                    and i.Ta_DefTipoInterv in ('00', '01')
                    and f.Tp_IdTipointerv <> 15
                    and a.Tm_idRegistro = 6
                    and not a.Tm_idescritura in (4717, 4744, 5696, 6354, 6476, 6482, 6541, 7126, 7152, 7380, 8085, 8147, 8151, 8154, 8163, 8198, 8244, 8304, 8489, 8516)  --- ESCRITURAS EXCLUIDAS
                    and not c.Ta_NatActo is null
                    and b.Tp_idacto in (35, 36, 37, 38, 39, 40)

            UNION ALL

            -- CONSTITUCIONES CON OTROS BIENES

            select

            a.Tm_NumeroEscritura,
                                a.Tm_FechaGenEsc,

                s.tm_codigoROF + ': ' + s.tm_nombreROF as Moneda,
                 d.Td_CapitalSocial as MontoOperacion
            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.vstInterviniente2 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 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 g on e.Td_IdExpSocAportes = g.Td_IdExpSocAportes and g.Ta_TipoBien = '02'


                    inner join SYSMART.dbo.ta_tabla as l on h.Ta_TipoIdentifica = l.ta_codigo and l.ta_campo = 'Ta_TipoIdentifica'
                    left join SYSMART.dbo.ta_tabla as m on h.Ta_Nacionalidad = m.ta_codigo and m.ta_campo = 'Ta_Nacionalidad'
                    left join SYSMART.dbo.ta_tabla as n on h.Ta_EstCivil = n.ta_codigo and n.ta_campo = 'Ta_EstCivil'

                    inner join SYSMART.dbo.tm_ubigeo as oDist on h.tm_idubigeo = oDist.tm_idubigeo
                    inner join SYSMART.dbo.tm_ubigeo as oProv2 on oDist.tm_refcodigo_ubi = oProv2.tm_idubigeo
                    inner join SYSMART.dbo.tm_ubigeo as oDep1 on oProv2.tm_refcodigo_ubi = oDep1.tm_idubigeo

                    inner join SYSMART.dbo.Tm_moneda as s on d.Tm_IdMoneda = s.tm_idmoneda

                where  a.Tm_FechaGenEsc >= @fechaini and a.Tm_FechaGenEsc <= @fechafin
                    and i.Ta_DefTipoInterv in ('00', '01')
                    and f.Tp_IdTipointerv <> 15
                    and a.Tm_idRegistro = 6
                    and not a.Tm_idescritura in (4717, 4744, 5696, 6354, 6476, 6482, 6541, 7126, 7152, 7380, 8085, 8147, 8151, 8154, 8163, 8198, 8244, 8304, 8489, 8516)  --- ESCRITURAS EXCLUIDAS
                    and not c.Ta_NatActo is null
                    and b.Tp_idacto in (35, 36, 37, 38, 39, 40)


    ) as x
    union all
            select
            --ROW_NUMBER() OVER(ORDER BY a.Tm_NumeroEscritura ASC) AS Item,
        '5' as tipoesc,
             convert(int, SUBSTRING(a.Tm_NumeroEscritura, 6, 5)) nroescritura,
                                SN.fc_formatearFecha('03', a.tm_fechafirmaesc) as fechaNum,
                s.tm_codigoROF + ': ' + s.tm_nombreROF as Moneda,
                p.Td_MontoGravamen as MontoOperacion

                                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.vstInterviniente2 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.ta_tabla as l on h.Ta_TipoIdentifica = l.ta_codigo and l.ta_campo = 'Ta_TipoIdentifica'
                                    left join SYSMART.dbo.ta_tabla as m on h.Ta_Nacionalidad = m.ta_codigo and m.ta_campo = 'Ta_Nacionalidad'
                                    left join SYSMART.dbo.ta_tabla as n on h.Ta_EstCivil = n.ta_codigo and n.ta_campo = 'Ta_EstCivil'

                                    inner join SYSMART.dbo.tm_ubigeo as oDist on h.tm_idubigeo = oDist.tm_idubigeo
                                    inner join SYSMART.dbo.tm_ubigeo as oProv2 on oDist.tm_refcodigo_ubi = oProv2.tm_idubigeo
                                    inner join SYSMART.dbo.tm_ubigeo as oDep1 on oProv2.tm_refcodigo_ubi = oDep1.tm_idubigeo

                                    inner join SN.Td_Expediente_Gravamen as p on b.Td_idexpedienteacto = p.Td_IdExpedienteActo

                                    inner join SYSMART.dbo.Tm_moneda as s on p.Tm_IdMoneda = s.tm_idmoneda
                                    inner join SN.Tp_Vehiculos as t on p.Tp_idbien = t.Tp_IdVehiculo
                                where  a.Tm_FechaGenEsc >= @fechaini and a.Tm_FechaGenEsc <= @fechafin
                                    and i.Ta_DefTipoInterv in ('00', '01')
                                    and f.Tp_IdTipointerv <> 15
                                    and a.Tm_idRegistro = 11
) as DISTINTO
) AS PETERETE
end