<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.mx.dla.dda.reporte.cpview.daos.ReporteCostPerViewDAO">
		 
	 <resultMap id="resultQueryMensual" type="CostPerViewEstudioMensualDatosDTO">
	 	<result property="estudio"		column="ESTUDIO" />
	 	<result property="nivel"		column="NIVEL" />
	 	<result property="amortizacionEnero"		column="AMOR_ENE" />
	 	<result property="visualizacionesEnero"		column="VIS_ENE" />
	 	<result property="costoVisualizacionEnero"	column="CPV_ENE" />
	 	<result property="amortizacionFebrero"		column="AMOR_FEB" />
	 	<result property="visualizacionesFebrero"		column="VIS_FEB" />
	 	<result property="costoVisualizacionFebrero"	column="CPV_FEB" />
	 	<result property="amortizacionMarzo"		column="AMOR_MZO" />
	 	<result property="visualizacionesMarzo"		column="VIS_MZO" />
	 	<result property="costoVisualizacionMarzo"	column="CPV_MZO" />
	 	<result property="amortizacionAbril"		column="AMOR_ABR" />
	 	<result property="visualizacionesAbril"		column="VIS_ABR" />
	 	<result property="costoVisualizacionAbril"	column="CPV_ABR" />
	 	<result property="amortizacionMayo"		column="AMOR_MYO" />
	 	<result property="visualizacionesMayo"		column="VIS_MYO" />
	 	<result property="costoVisualizacionMayo"	column="CPV_MYO" />
	 	<result property="amortizacionJunio"		column="AMOR_JUN" />
	 	<result property="visualizacionesJunio"		column="VIS_JUN" />
	 	<result property="costoVisualizacionJunio"	column="CPV_JUN" />
	 	<result property="amortizacionJulio"		column="AMOR_JUL" />
	 	<result property="visualizacionesJulio"		column="VIS_JUL" />
	 	<result property="costoVisualizacionJulio"	column="CPV_JUL" />
	 	<result property="amortizacionAgosto"		column="AMOR_AGO" />
	 	<result property="visualizacionesAgosto"		column="VIS_AGO" />
	 	<result property="costoVisualizacionAgosto"	column="CPV_AGO" />
	 	<result property="amortizacionSeptiembre"		column="AMOR_SEP" />
	 	<result property="visualizacionesSeptiembre"		column="VIS_SEP" />
	 	<result property="costoVisualizacionSeptiembre"	column="CPV_SEP" />
	 	<result property="amortizacionOctubre"		column="AMOR_OCT" />
	 	<result property="visualizacionesOctubre"		column="VIS_OCT" />
	 	<result property="costoVisualizacionOctubre"	column="CPV_OCT" />
	 	<result property="amortizacionNoviembre"		column="AMOR_NOV" />
	 	<result property="visualizacionesNoviembre"		column="VIS_NOV" />
	 	<result property="costoVisualizacionNoviembre"	column="CPV_NOV" />
	 	<result property="amortizacionDiciembre"		column="AMOR_DIC" />
	 	<result property="visualizacionesDiciembre"		column="VIS_DIC" />
	 	<result property="costoVisualizacionDiciembre"	column="CPV_DIC" />
	 </resultMap>
	 
	 <resultMap id="resultQueryAnual" type="CostPerViewEstudioAnualDatosDTO">
	 	<result property="estudio"		            column="ESTUDIO" />
	 	<result property="amortizacionAnio1"		column="AMOR_ANIO_0" />
	 	<result property="visualizacionesAnio1"		column="VIS_ANIO_0" />
	 	<result property="costoVisualizacionAnio1"	column="CPV_ANIO_0" />
	 	<result property="amortizacionAnio2"		column="AMOR_ANIO_1" />
	 	<result property="visualizacionesAnio2"		column="VIS_ANIO_1" />
	 	<result property="costoVisualizacionAnio2"	column="CPV_ANIO_1" />
	 	<result property="amortizacionAnio3"		column="AMOR_ANIO_2" />
	 	<result property="visualizacionesAnio3"		column="VIS_ANIO_2" />
	 	<result property="costoVisualizacionAnio3"	column="CPV_ANIO_2" />
	 	<result property="amortizacionAnio4"		column="AMOR_ANIO_3" />
	 	<result property="visualizacionesAnio4"		column="VIS_ANIO_3" />
	 	<result property="costoVisualizacionAnio4"	column="CPV_ANIO_3" />
	 	<result property="amortizacionAnio5"		column="AMOR_ANIO_4" />
	 	<result property="visualizacionesAnio5"		column="VIS_ANIO_4" />
	 	<result property="costoVisualizacionAnio5"	column="CPV_ANIO_4" />
	 </resultMap>
	 
	 <resultMap id="resultVisualizacionesPrincipal" type="CargaVisualizacionesPrincipalDTO">
	 	<result property="idCarga"		      column="ID_CARGA" />
	 	<result property="archivoXlsx"	      column="ARCHIVO_XLSX" />
	 	<result property="expediente"		  column="EXPEDIENTE" />
	 	<result property="fechaEntrega"	      column="FECHA_CARGA" />
	 	<result property="totalRegistros"	  column="TOTAL_REGISTROS" />
	 	<result property="totalCargados"	  column="TOTAL_CARGADOS" />
	 	<result property="totalError"	      column="TOTAL_ERROR" />
	 	<result property="mes"		          column="MES" />
	 </resultMap>
	 
	 <resultMap id="resultVisualizacionesDetalle" type="CargaVisualizacionesDetalleDTO">
	 	<result property="idLdr"		column="ID_LDR" />
	 	<result property="idCarga"		column="ID_CARGA" />
	 	<result property="idBroadview"		column="ID_BROADVIEW" />
	 	<result property="titulo"	column="TITULO" />
	 	<result property="visualizaciones"	column="VISUALIZACIONES" />
	 	<result property="mes"		column="MES" />
	 	<result property="estatus"		column="ESTATUS" />
	 	<result property="comentarios"	column="COMENTARIOS" />
	 </resultMap>
	   	  	  	  	  
   <select id="obtenerCostPerViewMensualDatosEstudioNivel" resultMap="resultQueryMensual" parameterType="map">
    select
        amor.ESTUDIO,
        amor.MES_1 as AMOR_ENE,
        visual.MES_1 as VIS_ENE,
        DECODE( visual.MES_1, 0, 0, ROUND( amor.MES_1 / visual.MES_1, 2 ) ) as CPV_ENE,
        amor.MES_2 as AMOR_FEB,
        visual.MES_2 as VIS_FEB,
        DECODE( visual.MES_2, 0, 0, ROUND( CASE WHEN amor.MES_2 IS NULL OR amor.MES_2 = 0  THEN amor.MES_1 ELSE amor.MES_2 END / visual.MES_2, 2 ) ) as CPV_FEB,
        amor.MES_3 as AMOR_MZO,
        visual.MES_3 as VIS_MZO,
        DECODE( visual.MES_3, 0, 0, ROUND( CASE WHEN amor.MES_3 IS NULL OR amor.MES_3 = 0  THEN amor.MES_2 ELSE amor.MES_3 END  / visual.MES_3, 2 ) ) as CPV_MZO,
        amor.MES_4 as AMOR_ABR,
        visual.MES_4 as VIS_ABR,
        DECODE( visual.MES_4, 0, 0, ROUND( CASE WHEN amor.MES_4 IS NULL OR amor.MES_4 = 0  THEN amor.MES_3 ELSE amor.MES_4 END  / visual.MES_4, 2 ) ) as CPV_ABR,
        amor.MES_5 as AMOR_MYO,
        visual.MES_5 as VIS_MYO,
        DECODE( visual.MES_5, 0, 0, ROUND( CASE WHEN amor.MES_5 IS NULL OR amor.MES_5 = 0  THEN amor.MES_4 ELSE amor.MES_5 END  / visual.MES_5, 2 ) ) as CPV_MYO,
        amor.MES_6 as AMOR_JUN,
        visual.MES_6 as VIS_JUN,
        DECODE( visual.MES_6, 0, 0,  ROUND( CASE WHEN amor.MES_6 IS NULL OR amor.MES_6 = 0  THEN amor.MES_5 ELSE amor.MES_6 END  / visual.MES_6, 2 ) ) as CPV_JUN,
        amor.MES_7 as AMOR_JUL,
        visual.MES_7 as VIS_JUL,
        DECODE( visual.MES_7, 0, 0, ROUND( CASE WHEN amor.MES_7 IS NULL OR amor.MES_7 = 0  THEN amor.MES_6 ELSE amor.MES_7 END  / visual.MES_7, 2 ) ) as CPV_JUL,
        amor.MES_8 as AMOR_AGO,
        visual.MES_8 as VIS_AGO,
        DECODE( visual.MES_8, 0, 0, ROUND( CASE WHEN amor.MES_8 IS NULL OR amor.MES_8 = 0  THEN amor.MES_7 ELSE amor.MES_8 END  / visual.MES_8, 2 ) ) as CPV_AGO,
        amor.MES_9 as AMOR_SEP,
        visual.MES_9 as VIS_SEP,
        DECODE( visual.MES_9, 0, 0, ROUND( CASE WHEN amor.MES_9 IS NULL OR amor.MES_9 = 0  THEN amor.MES_8 ELSE amor.MES_9 END  / visual.MES_9, 2 ) ) as CPV_SEP,
        amor.MES_10 as AMOR_OCT,
        visual.MES_10 as VIS_OCT,
        DECODE( visual.MES_10, 0, 0, ROUND( CASE WHEN amor.MES_10 IS NULL OR amor.MES_10 = 0  THEN amor.MES_11 ELSE amor.MES_10 END  / visual.MES_10, 2 ) ) as CPV_OCT,
        amor.MES_11 as AMOR_NOV,
        visual.MES_11 as VIS_NOV,
        DECODE( visual.MES_11, 0, 0, ROUND( CASE WHEN amor.MES_11 IS NULL OR amor.MES_11 = 0  THEN amor.MES_10 ELSE amor.MES_11 END  / visual.MES_11, 2 ) ) as CPV_NOV,
        amor.MES_12 as AMOR_DIC,
        visual.MES_12 as VIS_DIC,
        DECODE( visual.MES_12, 0, 0, ROUND( CASE WHEN amor.MES_12 IS NULL OR amor.MES_12 = 0  THEN amor.MES_11 ELSE amor.MES_12 END  / visual.MES_12, 2 ) ) as CPV_DIC,
        amor.ORDEN
    from
        (
            select
                DECODE( NVL( est.DESC_ESTUDIO, '-' ), '-', 1, 0 ) as ORDEN,
                NVL( est.DESC_ESTUDIO, 'TOTAL' ) as ESTUDIO,                                 
                ROUND( SUM( sub.MES_1 ), 2 ) as MES_1, 
                ROUND( SUM( sub.MES_2 ), 2 ) as MES_2, 
                ROUND( SUM( sub.MES_3 ), 2 ) as MES_3, 
                ROUND( SUM( sub.MES_4 ), 2 ) as MES_4, 
                ROUND( SUM( sub.MES_5 ), 2 ) as MES_5, 
                ROUND( SUM( sub.MES_6 ), 2 ) as MES_6, 
                ROUND( SUM( sub.MES_7 ), 2 ) as MES_7, 
                ROUND( SUM( sub.MES_8 ), 2 ) as MES_8, 
                ROUND( SUM( sub.MES_9 ), 2 ) as MES_9, 
                ROUND( SUM( sub.MES_10 ), 2 ) as MES_10, 
                ROUND( SUM( sub.MES_11 ), 2 ) as MES_11, 
                ROUND( SUM( sub.MES_12 ), 2 ) as MES_12
            from 
                (
                    select 
                        ID_CONTRATO_ORIGINAL, ANNIO,
                        SUM( MES_1 ) as MES_1, SUM( MES_2 ) as MES_2, SUM( MES_3 ) as MES_3, SUM( MES_4 ) as MES_4, SUM( MES_5 ) as MES_5, SUM( MES_6 ) as MES_6,
                        SUM( MES_7 ) as MES_7, SUM( MES_8 ) as MES_8, SUM( MES_9 ) as MES_9, SUM( MES_10 ) as MES_10, SUM( MES_11 ) as MES_11, SUM( MES_12 ) as MES_12
                    from (
                        select
                            ID_CONTRATO_ORIGINAL, ANNIO, 
                            NVL( MES_1, 0 ) as MES_1, NVL( MES_2, 0 ) as MES_2, 
                            NVL( MES_3, 0 ) as MES_3, NVL( MES_4, 0 ) as MES_4, 
                            NVL( MES_5, 0 ) as MES_5, NVL( MES_6, 0 ) as MES_6, 
                            NVL( MES_7, 0 ) as MES_7, NVL( MES_8, 0 ) as MES_8, 
                            NVL( MES_9, 0 ) as MES_9, NVL( MES_10, 0 ) as MES_10, 
                            NVL( MES_11, 0 ) as MES_11, NVL( MES_12, 0 ) as MES_12
                        from
                        (
                            select       
                                amor.ID_CONTRATO_ORIGINAL,
                                amor.ID_TITULO_CNTORIG,                
                                TO_NUMBER( TO_CHAR( amor.MES, 'yyyy' ) ) as ANNIO,
                                TO_NUMBER( TO_CHAR( amor.MES, 'mm' ) ) as MESES, amor.MES,
                                TO_NUMBER( amor.MONTO ) as MONTO
                            from
                                DDA_T_AMORTIZACIONCPV amor
                            where                                 
                                TO_DATE( TO_CHAR( amor.MES, 'mm-yyyy' ), 'mm-yyyy' ) &lt;= TO_DATE(#{mes} || '-' || #{anio}, 'mm-yyyy' )
                                and TO_NUMBER( TO_CHAR( amor.MES, 'yyyy' ) ) = #{anio}
                        )
                        PIVOT( SUM( MONTO ) FOR 
                            MESES in ( 1 as MES_1, 2 as MES_2, 3 as MES_3, 4 as MES_4, 5 as MES_5, 6 as MES_6,
                                        7 as MES_7, 8 as MES_8, 9 as MES_9, 10 as MES_10, 11 as MES_11, 12 as MES_12 )
                            )                            
                    )
                    group by  ID_CONTRATO_ORIGINAL, ANNIO
                ) sub,  
                DDA_T_CONTRATO cnt,
                DDA_C_ESTUDIO est
          where
                    sub.ID_CONTRATO_ORIGINAL = cnt.ID_CONTRATO
                    and cnt.ID_ESTUDIO = est.ID_ESTUDIO 
                    and cnt.ID_ESTATUS in ( 4, 5, 6 )
           group by
                grouping sets
                    ( 
                        ( ),
                        est.DESC_ESTUDIO
                    ) 
        ) amor,
        (
            select 
                NVL( DESC_ESTUDIO, 'TOTAL' ) as ESTUDIO,
                SUM( MES_1 ) as MES_1, SUM( MES_2 ) as MES_2, SUM( MES_3 ) as MES_3, SUM( MES_4 ) as MES_4, SUM( MES_5 ) as MES_5, SUM( MES_6 ) as MES_6,
                SUM( MES_7 ) as MES_7, SUM( MES_8 ) as MES_8, SUM( MES_9 ) as MES_9, SUM( MES_10 ) as MES_10, SUM( MES_11 ) as MES_11, SUM( MES_12 ) as MES_12
            from 
            (
                select
                    DESC_ESTUDIO, 
                    NVL( MES_1, 0 ) as MES_1, NVL( MES_2, 0 ) as MES_2, 
                    NVL( MES_3, 0 ) as MES_3, NVL( MES_4, 0 ) as MES_4, 
                    NVL( MES_5, 0 ) as MES_5, NVL( MES_6, 0 ) as MES_6, 
                    NVL( MES_7, 0 ) as MES_7, NVL( MES_8, 0 ) as MES_8, 
                    NVL( MES_9, 0 ) as MES_9, NVL( MES_10, 0 ) as MES_10, 
                    NVL( MES_11, 0 ) as MES_11, NVL( MES_12, 0 ) as MES_12
                from
                (
                    SELECT
                        e.DESC_ESTUDIO,   
                        TO_NUMBER( TO_CHAR( vc.MES, 'yyyy' ) ) as ANNIO,
                        TO_NUMBER( TO_CHAR( vc.MES, 'mm' ) ) as MESES, 
                        SUM( vc.CANTIDAD ) as TOTAL
                    FROM
                        (  
                          WITH base_query AS (      
                             SELECT  ct.ID_LISTA, ct.ID_BV, ct.ID_ESTUDIO, v.MES, v.CANTIDAD, ct.NOMBRE_UNICO, ct.DESC_TITULO
                             FROM  DDA_T_VISUALIZACION v
                               INNER JOIN(   
                                 SELECT  t.ID_LISTA, t.ID_BV, c.ID_ESTUDIO, t.FECHA_INICIO, t.FECHA_FIN, t.NOMBRE_UNICO, t.DESC_TITULO
                                 FROM DDA_T_CONTRATO_TITULO t
                                   INNER JOIN DDA_T_CONTRATO c on c.ID_CONTRATO = t.ID_CONTRATO
                                 WHERE c.ID_ESTATUS in ( 4, 5, 6 )
                               )ct ON ct.ID_BV = v.ID_BV
                             WHERE v.MES BETWEEN ADD_MONTHS((LAST_DAY(ct.FECHA_INICIO)+1),-1) AND LAST_DAY(ct.FECHA_FIN)
                                  --AND v.ID_BV IN (116233630)
                                  AND TO_DATE( TO_CHAR( v.MES, 'mm-yyyy' ), 'mm-yyyy' ) &lt;= TO_DATE(#{mes} || '-' ||#{anio}, 'mm-yyyy' )                 
                                  AND TO_NUMBER( TO_CHAR( v.MES, 'yyyy' ) ) = #{anio}
                          )
                           SELECT DISTINCT a.ID_BV, a.ID_ESTUDIO, a.MES, a.CANTIDAD, a.NOMBRE_UNICO, a.DESC_TITULO 
                           FROM base_query a
                             INNER JOIN (
                               SELECT ID_BV, ID_ESTUDIO, MES, MAX(ID_LISTA) AS ID_LISTA  FROM base_query 
                               GROUP BY ID_BV, ID_ESTUDIO, MES
                           ) b ON a.ID_BV = b.ID_BV AND a.MES = b.MES AND a.ID_ESTUDIO = b.ID_ESTUDIO AND a.ID_LISTA = b.ID_LISTA 
                        ) vc
                        INNER JOIN DDA_C_ESTUDIO e ON e.ID_ESTUDIO = vc.ID_ESTUDIO 
                    GROUP BY e.DESC_ESTUDIO, vc.MES
                )
                PIVOT( SUM( TOTAL ) FOR 
                    MESES in ( 1 as MES_1, 2 as MES_2, 3 as MES_3, 4 as MES_4, 5 as MES_5, 6 as MES_6,
                                7 as MES_7, 8 as MES_8, 9 as MES_9, 10 as MES_10, 11 as MES_11, 12 as MES_12 )
                    )   
            )
            group by
                grouping sets
                (
                    (),
                    DESC_ESTUDIO
                )
        ) visual
        where
            amor.ESTUDIO = visual.ESTUDIO(+)
        order by
            ORDEN, amor.ESTUDIO 
   </select>
	 
   <select id="obtenerCostPerViewMensualDatosTituloNivel" resultMap="resultQueryMensual" parameterType="map">
    SELECT /* obtenerCostPerViewMensualDatosTituloNivel   */
        amor.DESCRIPCION as ESTUDIO,
        amor.NIVEL AS NIVEL,
        amor.MES_1 as AMOR_ENE,
        visual.MES_1 as VIS_ENE,
        DECODE( visual.MES_1, 0, 0, ROUND( amor.MES_1 / visual.MES_1, 2 ) ) as CPV_ENE,
        amor.MES_2 as AMOR_FEB,
        visual.MES_2 as VIS_FEB,
        DECODE( visual.MES_2, 0, 0, ROUND( CASE WHEN amor.MES_2 IS NULL OR amor.MES_2 = 0  THEN amor.MES_1 ELSE amor.MES_2 END / visual.MES_2, 2 ) ) as CPV_FEB,
        amor.MES_3 as AMOR_MZO,
        visual.MES_3 as VIS_MZO,
        DECODE( visual.MES_3, 0, 0, ROUND( CASE WHEN amor.MES_3 IS NULL OR amor.MES_3 = 0  THEN amor.MES_2 ELSE amor.MES_3 END  / visual.MES_3, 2 ) ) as CPV_MZO,
        amor.MES_4 as AMOR_ABR,
        visual.MES_4 as VIS_ABR,
        DECODE( visual.MES_4, 0, 0, ROUND( CASE WHEN amor.MES_4 IS NULL OR amor.MES_4 = 0  THEN amor.MES_3 ELSE amor.MES_4 END  / visual.MES_4, 2 ) ) as CPV_ABR,
        amor.MES_5 as AMOR_MYO,
        visual.MES_5 as VIS_MYO,
        DECODE( visual.MES_5, 0, 0, ROUND( CASE WHEN amor.MES_5 IS NULL OR amor.MES_5 = 0  THEN amor.MES_4 ELSE amor.MES_5 END  / visual.MES_5, 2 ) ) as CPV_MYO,
        amor.MES_6 as AMOR_JUN,
        visual.MES_6 as VIS_JUN,
        DECODE( visual.MES_6, 0, 0,  ROUND( CASE WHEN amor.MES_6 IS NULL OR amor.MES_6 = 0  THEN amor.MES_5 ELSE amor.MES_6 END  / visual.MES_6, 2 ) ) as CPV_JUN,
        amor.MES_7 as AMOR_JUL,
        visual.MES_7 as VIS_JUL,
        DECODE( visual.MES_7, 0, 0, ROUND( CASE WHEN amor.MES_7 IS NULL OR amor.MES_7 = 0  THEN amor.MES_6 ELSE amor.MES_7 END  / visual.MES_7, 2 ) ) as CPV_JUL,
        amor.MES_8 as AMOR_AGO,
        visual.MES_8 as VIS_AGO,
        DECODE( visual.MES_8, 0, 0, ROUND( CASE WHEN amor.MES_8 IS NULL OR amor.MES_8 = 0  THEN amor.MES_7 ELSE amor.MES_8 END  / visual.MES_8, 2 ) ) as CPV_AGO,
        amor.MES_9 as AMOR_SEP,
        visual.MES_9 as VIS_SEP,
        DECODE( visual.MES_9, 0, 0, ROUND( CASE WHEN amor.MES_9 IS NULL OR amor.MES_9 = 0  THEN amor.MES_8 ELSE amor.MES_9 END  / visual.MES_9, 2 ) ) as CPV_SEP,
        amor.MES_10 as AMOR_OCT,
        visual.MES_10 as VIS_OCT,
        DECODE( visual.MES_10, 0, 0, ROUND( CASE WHEN amor.MES_10 IS NULL OR amor.MES_10 = 0  THEN amor.MES_11 ELSE amor.MES_10 END  / visual.MES_10, 2 ) ) as CPV_OCT,
        amor.MES_11 as AMOR_NOV,
        visual.MES_11 as VIS_NOV,
        DECODE( visual.MES_11, 0, 0, ROUND( CASE WHEN amor.MES_11 IS NULL OR amor.MES_11 = 0  THEN amor.MES_10 ELSE amor.MES_11 END  / visual.MES_11, 2 ) ) as CPV_NOV,
        amor.MES_12 as AMOR_DIC,
        visual.MES_12 as VIS_DIC,
        DECODE( visual.MES_12, 0, 0, ROUND( CASE WHEN amor.MES_12 IS NULL OR amor.MES_12 = 0  THEN amor.MES_11 ELSE amor.MES_12 END  / visual.MES_12, 2 ) ) as CPV_DIC
    FROM
      (
        SELECT * FROM (
           SELECT
                DESC_ESTUDIO,
                NOMBRE_UNICO,
                DESC_TITULO,
                CASE 
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NOT NULL AND DESC_TITULO IS NOT NULL AND NOMBRE_UNICO = DESC_TITULO THEN
                  1
                   ELSE
                  0              
                END as NOT_SERIE_SINGLE,
                CASE
                   WHEN DESC_ESTUDIO IS NULL AND NOMBRE_UNICO IS NULL AND DESC_TITULO IS NULL THEN
                  0
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NULL AND DESC_TITULO IS NULL THEN
                  1
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NOT NULL AND DESC_TITULO IS NULL THEN
                  2
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NOT NULL AND DESC_TITULO IS NOT NULL THEN
                  3              
                END as NIVEL,
                CASE 
                   WHEN DESC_ESTUDIO IS NULL AND NOMBRE_UNICO IS NULL AND DESC_TITULO IS NULL THEN
                  1
                   ELSE
                  0               
                END as ORDEN_N1,
                CASE 
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NULL AND DESC_TITULO IS NULL THEN
                  1
                   ELSE
                  0               
                END as ORDEN_N2,
                CASE 
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NOT NULL AND DESC_TITULO IS NULL THEN
                  1
                   ELSE
                  0               
                END as ORDEN_N3,
                CASE 
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NOT NULL AND DESC_TITULO IS NOT NULL THEN
                  1
                   ELSE
                  0               
                END as ORDEN_N4,
                CASE
                   WHEN DESC_ESTUDIO IS NULL AND NOMBRE_UNICO IS NULL AND DESC_TITULO IS NULL THEN
                  'TOTAL'
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NULL AND DESC_TITULO IS NULL THEN
                  DESC_ESTUDIO
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NOT NULL AND DESC_TITULO IS NULL THEN
                  NOMBRE_UNICO
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NOT NULL AND DESC_TITULO IS NOT NULL THEN
                  DESC_TITULO               
                END as DESCRIPCION,
                ROUND( SUM( MES_1 ), 2 ) as MES_1, 
                ROUND( SUM( MES_2 ), 2 ) as MES_2, 
                ROUND( SUM( MES_3 ), 2 ) as MES_3, 
                ROUND( SUM( MES_4 ), 2 ) as MES_4, 
                ROUND( SUM( MES_5 ), 2 ) as MES_5, 
                ROUND( SUM( MES_6 ), 2 ) as MES_6, 
                ROUND( SUM( MES_7 ), 2 ) as MES_7, 
                ROUND( SUM( MES_8 ), 2 ) as MES_8, 
                ROUND( SUM( MES_9 ), 2 ) as MES_9, 
                ROUND( SUM( MES_10 ), 2 ) as MES_10, 
                ROUND( SUM( MES_11 ), 2 ) as MES_11, 
                ROUND( SUM( MES_12 ), 2 ) as MES_12
           FROM 
                (
                        SELECT
                            DESC_ESTUDIO, ANNIO, DESC_TITULO, NOMBRE_UNICO,
                            NVL( MES_1, 0 ) as MES_1, NVL( MES_2, 0 ) as MES_2, 
                            NVL( MES_3, 0 ) as MES_3, NVL( MES_4, 0 ) as MES_4, 
                            NVL( MES_5, 0 ) as MES_5, NVL( MES_6, 0 ) as MES_6, 
                            NVL( MES_7, 0 ) as MES_7, NVL( MES_8, 0 ) as MES_8, 
                            NVL( MES_9, 0 ) as MES_9, NVL( MES_10, 0 ) as MES_10, 
                            NVL( MES_11, 0 ) as MES_11, NVL( MES_12, 0 ) as MES_12
                        FROM
                        (
                            SELECT       
                                est.DESC_ESTUDIO,
                                amor.ID_TITULO_CNTORIG,  
                                ct.DESC_TITULO,
                                ct.NOMBRE_UNICO,
                                TO_NUMBER( TO_CHAR( amor.MES, 'yyyy' ) ) as ANNIO,
                                TO_NUMBER( TO_CHAR( amor.MES, 'mm' ) ) as MESES, 
                                TO_NUMBER( amor.MONTO ) as MONTO
                            FROM
                                DDA_T_AMORTIZACIONCPV amor
                                INNER JOIN ( 
                                    SELECT 
                                    scnt.ID_CONTRATO_ORIGINAL AS ID_CONTRATO,
                                    ct.ID_TITULO_CNTORIG,
                                    ct.ID_BV,
                                    ct.DESC_TITULO,
                                    ct.NOMBRE_UNICO,
                                    ct.TEMPORADA,
                                    ct.EPISODIO,
                                    ct.FECHA_INICIO,
                                    ct.FECHA_FIN,
                                    ct.COSTO
                                    FROM DDA_T_CONTRATO_TITULO ct,
                                         (SELECT ID_CONTRATO_ORIGINAL,
                                          MAX( ID_CONTRATO ) AS ID_CONTRATO
                                          FROM DDA_T_CONTRATO
                                          WHERE ID_TIPO_CONTRATO = 1
                                          GROUP BY ID_CONTRATO_ORIGINAL) scnt
                                    WHERE ct.ID_CONTRATO = scnt.ID_CONTRATO
                                     <if test="nombreUnico != 'Todos'">
                                    		and ct.nombre_unico = (select nombre_unico from DDA_T_CONTRATO_TITULO where ID_TITULO_CNT = #{nombreUnico}) 
			                    	</if> 
			                    	<if test="temporada != 'Todos'">
                                    	AND ct.temporada = #{temporada}
			                    	</if> 
                                    ) 
                                ct on ct.ID_TITULO_CNTORIG = amor.ID_TITULO_CNTORIG
                                INNER JOIN  DDA_T_CONTRATO cnt on cnt.ID_CONTRATO = amor.ID_CONTRATO_ORIGINAL  
                                       AND  cnt.ID_CONTRATO = ct.ID_CONTRATO
                                INNER JOIN DDA_C_ESTUDIO est on est.ID_ESTUDIO = cnt.ID_ESTUDIO 
                            WHERE                                 
                                TO_DATE( TO_CHAR( amor.MES, 'mm-yyyy' ), 'mm-yyyy' ) &lt;= TO_DATE(#{mes} || '-' || #{anio}, 'mm-yyyy' )
                                AND TO_NUMBER( TO_CHAR( amor.MES, 'yyyy' ) ) = #{anio}
                                AND cnt.ID_ESTATUS in ( 4, 5, 6 )
                                <if test="estudio != null">
                                    AND cnt.ID_ESTUDIO = #{estudio}
			                    </if>  
			            )
                        PIVOT( SUM( MONTO ) FOR 
                            MESES in ( 1 as MES_1, 2 as MES_2, 3 as MES_3, 4 as MES_4, 5 as MES_5, 6 as MES_6,
                                        7 as MES_7, 8 as MES_8, 9 as MES_9, 10 as MES_10, 11 as MES_11, 12 as MES_12 )
                            )                            

           )GROUP BY
                GROUPING SETS
                    ( 
                        ( ),
                        (DESC_ESTUDIO),
                        (DESC_ESTUDIO, NOMBRE_UNICO),
                        (DESC_ESTUDIO, NOMBRE_UNICO, DESC_TITULO)
                    ) 
        ) WHERE NOT_SERIE_SINGLE != 1 
      ) amor,
      (
        SELECT * FROM (
           SELECT 
                CASE 
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NOT NULL AND DESC_TITULO IS NOT NULL AND NOMBRE_UNICO = DESC_TITULO THEN
                  1
                   ELSE
                  0              
                END as NOT_SERIE_SINGLE,
                CASE
                   WHEN DESC_ESTUDIO IS NULL AND NOMBRE_UNICO IS NULL AND DESC_TITULO IS NULL THEN
                  'TOTAL'
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NULL AND DESC_TITULO IS NULL THEN
                  DESC_ESTUDIO
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NOT NULL AND DESC_TITULO IS NULL THEN
                  NOMBRE_UNICO
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NOT NULL AND DESC_TITULO IS NOT NULL THEN
                  DESC_TITULO               
                END as DESCRIPCION,
                SUM( MES_1 ) as MES_1, SUM( MES_2 ) as MES_2, SUM( MES_3 ) as MES_3, SUM( MES_4 ) as MES_4, SUM( MES_5 ) as MES_5, SUM( MES_6 ) as MES_6,
                SUM( MES_7 ) as MES_7, SUM( MES_8 ) as MES_8, SUM( MES_9 ) as MES_9, SUM( MES_10 ) as MES_10, SUM( MES_11 ) as MES_11, SUM( MES_12 ) as MES_12
           FROM 
            (
                SELECT
                    DESC_ESTUDIO, 
                    DESC_TITULO,
                    NOMBRE_UNICO,
                    NVL( MES_1, 0 ) as MES_1, NVL( MES_2, 0 ) as MES_2, 
                    NVL( MES_3, 0 ) as MES_3, NVL( MES_4, 0 ) as MES_4, 
                    NVL( MES_5, 0 ) as MES_5, NVL( MES_6, 0 ) as MES_6, 
                    NVL( MES_7, 0 ) as MES_7, NVL( MES_8, 0 ) as MES_8, 
                    NVL( MES_9, 0 ) as MES_9, NVL( MES_10, 0 ) as MES_10, 
                    NVL( MES_11, 0 ) as MES_11, NVL( MES_12, 0 ) as MES_12
                FROM
                (
                    SELECT
                        e.DESC_ESTUDIO,  
                        vc.DESC_TITULO, vc.NOMBRE_UNICO,
                        TO_NUMBER( TO_CHAR( vc.MES, 'yyyy' ) ) as ANNIO,
                        TO_NUMBER( TO_CHAR( vc.MES, 'mm' ) ) as MESES, 
                        SUM( vc.CANTIDAD ) as TOTAL
                    FROM
                        (  
                          WITH base_query AS (      
                             SELECT  ct.ID_LISTA, ct.ID_BV, ct.ID_ESTUDIO, v.MES, v.CANTIDAD, ct.NOMBRE_UNICO, ct.DESC_TITULO
                             FROM  DDA_T_VISUALIZACION v
                               INNER JOIN(   
                                 SELECT  t.ID_LISTA, t.ID_BV, c.ID_ESTUDIO, t.FECHA_INICIO, t.FECHA_FIN, t.NOMBRE_UNICO, t.DESC_TITULO
                                 FROM DDA_T_CONTRATO_TITULO t
                                   INNER JOIN DDA_T_CONTRATO c on c.ID_CONTRATO = t.ID_CONTRATO
                                 WHERE c.ID_ESTATUS in ( 4, 5, 6 )
                                <if test="estudio != null">
                                       AND c.ID_ESTUDIO = #{estudio}
                                </if>
                                 <if test="nombreUnico != 'Todos'">
                                    		and t.nombre_unico = (select nombre_unico from DDA_T_CONTRATO_TITULO where ID_TITULO_CNT = #{nombreUnico}) 
			                    	</if> 
			                    	<if test="temporada != 'Todos'">
                                    	AND t.temporada = #{temporada}
			                    	</if> 
                                
                               )ct ON ct.ID_BV = v.ID_BV
                             WHERE v.MES BETWEEN ADD_MONTHS((LAST_DAY(ct.FECHA_INICIO)+1),-1) AND LAST_DAY(ct.FECHA_FIN)
                                  --AND v.ID_BV IN (116233630)
                                  AND TO_DATE( TO_CHAR( v.MES, 'mm-yyyy' ), 'mm-yyyy' ) &lt;= TO_DATE(#{mes} || '-' ||#{anio}, 'mm-yyyy' )                 
                                  AND TO_NUMBER( TO_CHAR( v.MES, 'yyyy' ) ) = #{anio}
                          )
                           SELECT DISTINCT a.ID_BV, a.ID_ESTUDIO, a.MES, a.CANTIDAD, a.NOMBRE_UNICO, a.DESC_TITULO 
                           FROM base_query a
                             INNER JOIN (
                               SELECT ID_BV, ID_ESTUDIO, MES, MAX(ID_LISTA) AS ID_LISTA  FROM base_query 
                               GROUP BY ID_BV, ID_ESTUDIO, MES
                           ) b ON a.ID_BV = b.ID_BV AND a.MES = b.MES AND a.ID_ESTUDIO = b.ID_ESTUDIO AND a.ID_LISTA = b.ID_LISTA 

                        ) vc
                        INNER JOIN DDA_C_ESTUDIO e ON e.ID_ESTUDIO = vc.ID_ESTUDIO 
                    GROUP BY e.DESC_ESTUDIO, vc.MES, vc.DESC_TITULO, vc.NOMBRE_UNICO
                )
                PIVOT( SUM( TOTAL ) FOR 
                    MESES in ( 1 as MES_1, 2 as MES_2, 3 as MES_3, 4 as MES_4, 5 as MES_5, 6 as MES_6,
                                7 as MES_7, 8 as MES_8, 9 as MES_9, 10 as MES_10, 11 as MES_11, 12 as MES_12 )
                    )   
            )GROUP BY
                GROUPING SETS
                (
                      ( ),
                      (DESC_ESTUDIO),
                      (DESC_ESTUDIO, NOMBRE_UNICO),
                      (DESC_ESTUDIO, NOMBRE_UNICO, DESC_TITULO)
                )
        ) WHERE NOT_SERIE_SINGLE != 1
      ) visual
    WHERE
      amor.DESCRIPCION = visual.DESCRIPCION(+)
    ORDER BY ORDEN_N1, DESC_ESTUDIO, ORDEN_N2 DESC, NOMBRE_UNICO, ORDEN_N3 DESC, DESC_TITULO, ORDEN_N4 DESC
   </select>
	 
   <select id="obtenerCostPerViewAnualDatosEstudioNivel" resultMap="resultQueryAnual" parameterType="map">
    select
        amor.ESTUDIO,
        amor.ANIO_0 as AMOR_ANIO_0,
        visual.ANIO_0 as VIS_ANIO_0,
        DECODE( visual.ANIO_0, 0, 0, ROUND( amor.ANIO_0 / visual.ANIO_0, 2 ) ) as CPV_ANIO_0,
        amor.ANIO_1 as AMOR_ANIO_1,
        visual.ANIO_1 as VIS_ANIO_1,
        DECODE( visual.ANIO_1, 0, 0, ROUND( amor.ANIO_1 / visual.ANIO_1, 2 ) ) as CPV_ANIO_1,
        amor.ANIO_2 as AMOR_ANIO_2,
        visual.ANIO_2 as VIS_ANIO_2,
        DECODE( visual.ANIO_2, 0, 0, ROUND( amor.ANIO_2 / visual.ANIO_2, 2 ) ) as CPV_ANIO_2,
        amor.ANIO_3 as AMOR_ANIO_3,
        visual.ANIO_3 as VIS_ANIO_3,
        DECODE( visual.ANIO_3, 0, 0, ROUND( amor.ANIO_3 / visual.ANIO_3, 2 ) ) as CPV_ANIO_3,
        amor.ANIO_4 as AMOR_ANIO_4,
        visual.ANIO_4 as VIS_ANIO_4,
        DECODE( visual.ANIO_4, 0, 0, ROUND( amor.ANIO_4 / visual.ANIO_4, 2 ) ) as CPV_ANIO_4,
        amor.ORDEN
    from
        (
            select
                DECODE( NVL( est.DESC_ESTUDIO, '-' ), '-', 1, 0 ) as ORDEN,
                NVL( est.DESC_ESTUDIO, 'TOTAL' ) as ESTUDIO,                                 
                ROUND( SUM( sub.ANIO_0 ), 2 ) as ANIO_0, 
                ROUND( SUM( sub.ANIO_1 ), 2 ) as ANIO_1, 
                ROUND( SUM( sub.ANIO_2 ), 2 ) as ANIO_2, 
                ROUND( SUM( sub.ANIO_3 ), 2 ) as ANIO_3, 
                ROUND( SUM( sub.ANIO_4 ), 2 ) as ANIO_4
            from 
                (
                    select 
                        ID_CONTRATO_ORIGINAL, 
                        SUM( ANIO_0 ) as ANIO_0, SUM( ANIO_1 ) as ANIO_1, 
                        SUM( ANIO_2 ) as ANIO_2, SUM( ANIO_3 ) as ANIO_3, 
                        SUM( ANIO_4 ) as ANIO_4
                    from (
                        select
                            ID_CONTRATO_ORIGINAL,  
                            NVL( ANIO_0, 0 ) as ANIO_0, NVL( ANIO_1, 0 ) as ANIO_1, 
                            NVL( ANIO_2, 0 ) as ANIO_2, NVL( ANIO_3, 0 ) as ANIO_3, 
                            NVL( ANIO_4, 0 ) as ANIO_4
                        from
                        (
                            select       
                                amor.ID_CONTRATO_ORIGINAL,
                                amor.ID_TITULO_CNTORIG,                
                                TO_NUMBER( TO_CHAR( amor.MES, 'yyyy' ) ) as ANNIO,
                                TO_NUMBER( TO_CHAR( amor.MES, 'mm' ) ) as MESES, 
                                amor.MES,
                                TO_NUMBER( amor.MONTO ) as MONTO
                            from
                                DDA_T_AMORTIZACIONCPV amor
                            where                                 
                                TO_NUMBER( TO_CHAR( amor.MES, 'yyyy' ) ) >= #{anio_inicial}
                                and TO_DATE( TO_CHAR( amor.MES, 'mm-yyyy' ), 'mm-yyyy' ) &lt;= TO_DATE(#{mes} || '-' || #{anio_final}, 'mm-yyyy' )
                        )
                        PIVOT( SUM( MONTO ) FOR 
                    ANNIO IN 
 (                          ${pivot_data}))                            
                    )
                    group by  ID_CONTRATO_ORIGINAL
                ) sub,  
                DDA_T_CONTRATO cnt,
                DDA_C_ESTUDIO est
          where
                    sub.ID_CONTRATO_ORIGINAL = cnt.ID_CONTRATO
                    and cnt.ID_ESTUDIO = est.ID_ESTUDIO
                    and cnt.ID_ESTATUS in ( 4, 5, 6 )                
           group by
                grouping sets
                    ( 
                        ( ),
                        est.DESC_ESTUDIO
                    ) 
        ) amor,
        (
            select 
                NVL( DESC_ESTUDIO, 'TOTAL' ) as ESTUDIO,
                SUM( ANIO_0 ) as ANIO_0, SUM( ANIO_1 ) as ANIO_1, SUM( ANIO_2 ) as ANIO_2, SUM( ANIO_3 ) as ANIO_3, SUM( ANIO_4 ) as ANIO_4
                
            from 
            (
                select
                    DESC_ESTUDIO, 
                    NVL( ANIO_0, 0 ) as ANIO_0, NVL( ANIO_1, 0 ) as ANIO_1, 
                    NVL( ANIO_2, 0 ) as ANIO_2, NVL( ANIO_3, 0 ) as ANIO_3, 
                    NVL( ANIO_4, 0 ) as ANIO_4
                from
                (
                    SELECT
                        e.DESC_ESTUDIO,   
                        TO_NUMBER( TO_CHAR( vc.MES, 'yyyy' ) ) as ANNIO,
                        TO_NUMBER( TO_CHAR( vc.MES, 'mm' ) ) as MESES, 
                        SUM( vc.CANTIDAD ) as TOTAL
                    FROM
                        (  
                          WITH base_query AS (      
                             SELECT  ct.ID_LISTA, ct.ID_BV, ct.ID_ESTUDIO, v.MES, v.CANTIDAD, ct.NOMBRE_UNICO, ct.DESC_TITULO
                             FROM  DDA_T_VISUALIZACION v
                               INNER JOIN(   
                                 SELECT  t.ID_LISTA, t.ID_BV, c.ID_ESTUDIO, t.FECHA_INICIO, t.FECHA_FIN, t.NOMBRE_UNICO, t.DESC_TITULO
                                 FROM DDA_T_CONTRATO_TITULO t
                                   INNER JOIN DDA_T_CONTRATO c on c.ID_CONTRATO = t.ID_CONTRATO
                                 WHERE c.ID_ESTATUS in ( 4, 5, 6 )
                               )ct ON ct.ID_BV = v.ID_BV
                             WHERE v.MES BETWEEN ADD_MONTHS((LAST_DAY(ct.FECHA_INICIO)+1),-1) AND LAST_DAY(ct.FECHA_FIN)
                                  AND TO_DATE( TO_CHAR( v.MES, 'mm-yyyy' ), 'mm-yyyy' ) &lt;= TO_DATE(#{mes} || '-' ||#{anio_final}, 'mm-yyyy' )                 
                                  AND TO_NUMBER( TO_CHAR( v.MES, 'yyyy' ) ) = #{anio_final}
                          )
                           SELECT DISTINCT a.ID_BV, a.ID_ESTUDIO, a.MES, a.CANTIDAD, a.NOMBRE_UNICO, a.DESC_TITULO 
                           FROM base_query a
                             INNER JOIN (
                               SELECT ID_BV, ID_ESTUDIO, MES, MAX(ID_LISTA) AS ID_LISTA  FROM base_query 
                               GROUP BY ID_BV, ID_ESTUDIO, MES
                           ) b ON a.ID_BV = b.ID_BV AND a.MES = b.MES AND a.ID_ESTUDIO = b.ID_ESTUDIO AND a.ID_LISTA = b.ID_LISTA 
 
                        ) vc
                        INNER JOIN DDA_C_ESTUDIO e ON e.ID_ESTUDIO = vc.ID_ESTUDIO 
                    GROUP BY e.DESC_ESTUDIO, vc.MES
                )
                PIVOT( SUM( TOTAL ) FOR 
                    ANNIO IN 
				       (${pivot_data})
                    )   
            )
            group by
                grouping sets
                (
                    (),
                    DESC_ESTUDIO
                )
        ) visual
        where
            amor.ESTUDIO = visual.ESTUDIO(+)
        order by
            ORDEN, amor.ESTUDIO
   </select>
	 
   <select id="obtenerCostPerViewAnualDatosTituloNivel" resultMap="resultQueryAnual" parameterType="map">
    SELECT /* obtenerCostPerViewAnualDatosTituloNivel */ 
    	AMOR.DESCRIPCION AS ESTUDIO,
       AMOR.NIVEL AS NIVEL,
       AMOR.ANIO_0 AS AMOR_ANIO_0,
       VISUAL.ANIO_0 AS VIS_ANIO_0,
       DECODE (VISUAL.ANIO_0, 0, 0, ROUND (AMOR.ANIO_0 / VISUAL.ANIO_0, 2)) CPV_ANIO_0,
       AMOR.ANIO_1 AS AMOR_ANIO_1,
       VISUAL.ANIO_1 AS VIS_ANIO_1,
       DECODE (VISUAL.ANIO_1, 0, 0, ROUND (AMOR.ANIO_1 / VISUAL.ANIO_1, 2)) CPV_ANIO_1,
       AMOR.ANIO_2 AS AMOR_ANIO_2,
       VISUAL.ANIO_2 AS VIS_ANIO_2,
       DECODE (VISUAL.ANIO_2, 0, 0, ROUND (AMOR.ANIO_2 / VISUAL.ANIO_2, 2)) CPV_ANIO_2,
       AMOR.ANIO_3 AS AMOR_ANIO_3,
       VISUAL.ANIO_3 AS VIS_ANIO_3,
       DECODE (VISUAL.ANIO_3, 0, 0, ROUND (AMOR.ANIO_3 / VISUAL.ANIO_3, 2)) CPV_ANIO_3,
       AMOR.ANIO_4 AS AMOR_ANIO_4,
       VISUAL.ANIO_4 AS VIS_ANIO_4,
       DECODE (VISUAL.ANIO_4, 0, 0, ROUND (AMOR.ANIO_4 / VISUAL.ANIO_4, 2)) CPV_ANIO_4
  FROM (SELECT * FROM (SELECT DESC_ESTUDIO,NOMBRE_UNICO,DESC_TITULO,
               CASE
                    WHEN DESC_ESTUDIO IS NULL
                         AND NOMBRE_UNICO IS NULL
                         AND DESC_TITULO IS NULL
                    THEN 0
                    WHEN DESC_ESTUDIO IS NOT NULL
                     AND NOMBRE_UNICO IS NULL
                     AND DESC_TITULO IS NULL
                    THEN 1
                    WHEN DESC_ESTUDIO IS NOT NULL
                         AND NOMBRE_UNICO IS NOT NULL
                         AND DESC_TITULO IS NULL
                    THEN 2
                    WHEN DESC_ESTUDIO IS NOT NULL
                         AND NOMBRE_UNICO IS NOT NULL
                         AND DESC_TITULO IS NOT NULL
                    THEN 3
               END AS NIVEL,
               CASE
                    WHEN DESC_ESTUDIO IS NULL
                         AND NOMBRE_UNICO IS NULL
                         AND DESC_TITULO IS NULL
                    THEN 1
                    ELSE 0
               END AS ORDEN_N1,
               CASE
                    WHEN DESC_ESTUDIO IS NOT NULL
                         AND NOMBRE_UNICO IS NULL
                         AND DESC_TITULO IS NULL
                    THEN 1
                    ELSE 0
               END AS ORDEN_N2,
               CASE
                    WHEN DESC_ESTUDIO IS NOT NULL
                         AND NOMBRE_UNICO IS NOT NULL
                         AND DESC_TITULO IS NULL
                    THEN 1
                    ELSE 0
               END AS ORDEN_N3,
               CASE
                    WHEN DESC_ESTUDIO IS NOT NULL
                         AND NOMBRE_UNICO IS NOT NULL
                         AND DESC_TITULO IS NOT NULL
                    THEN 1
                    ELSE 0
               END AS ORDEN_N4,
               CASE
                    WHEN DESC_ESTUDIO IS NULL
                         AND NOMBRE_UNICO IS NULL
                         AND DESC_TITULO IS NULL
                    THEN 'TOTAL'
                    WHEN DESC_ESTUDIO IS NOT NULL
                         AND NOMBRE_UNICO IS NULL
                         AND DESC_TITULO IS NULL
                    THEN DESC_ESTUDIO
                    WHEN DESC_ESTUDIO IS NOT NULL
                         AND NOMBRE_UNICO IS NOT NULL
                         AND DESC_TITULO IS NULL
                    THEN NOMBRE_UNICO
                    WHEN DESC_ESTUDIO IS NOT NULL
                         AND NOMBRE_UNICO IS NOT NULL
                         AND DESC_TITULO IS NOT NULL
                    THEN DESC_TITULO
              END AS DESCRIPCION,
       ROUND (SUM (ANIO_0), 2) AS ANIO_0,
       ROUND (SUM (ANIO_1), 2) AS ANIO_1,
       ROUND (SUM (ANIO_2), 2) AS ANIO_2,
       ROUND (SUM (ANIO_3), 2) AS ANIO_3,
       ROUND (SUM (ANIO_4), 2) AS ANIO_4
 FROM (SELECT DESC_ESTUDIO,NOMBRE_UNICO,DESC_TITULO,
              CASE 
                    WHEN DESC_ESTUDIO IS NOT NULL
                         AND NOMBRE_UNICO IS NOT NULL
                         AND DESC_TITULO IS NOT NULL
                         AND NOMBRE_UNICO = DESC_TITULO
                    THEN 1
                    ELSE 0
               END AS NOT_SERIE_SINGLE,
              ROUND ((ANIO_0), 2) AS ANIO_0,
              ROUND ((ANIO_1), 2) AS ANIO_1,
              ROUND ((ANIO_2), 2) AS ANIO_2,
              ROUND ((ANIO_3), 2) AS ANIO_3,
              ROUND ((ANIO_4), 2) AS ANIO_4
         FROM (SELECT DESC_ESTUDIO, DESC_TITULO, NOMBRE_UNICO,
                      NVL (ANIO_0, 0) AS ANIO_0,
                      NVL (ANIO_1, 0) AS ANIO_1,
                      NVL (ANIO_2, 0) AS ANIO_2,
                      NVL (ANIO_3, 0) AS ANIO_3,
                      NVL (ANIO_4, 0) AS ANIO_4
                FROM (
                            SELECT       
                                est.DESC_ESTUDIO,
                                amor.ID_TITULO_CNTORIG,  
                                ct.DESC_TITULO,
                                ct.NOMBRE_UNICO,
                                TO_NUMBER( TO_CHAR( amor.MES, 'yyyy' ) ) as ANNIO,
                                TO_NUMBER( TO_CHAR( amor.MES, 'mm' ) ) as MESES, 
                                TO_NUMBER( amor.MONTO ) as MONTO
                            FROM
                                DDA_T_AMORTIZACIONCPV amor
                                INNER JOIN DDA_T_CONTRATO_TITULO ct on ct.ID_TITULO_CNTORIG = amor.ID_TITULO_CNTORIG
                                INNER JOIN  DDA_T_CONTRATO cnt on cnt.ID_CONTRATO = amor.ID_CONTRATO_ORIGINAL  
                                      AND  cnt.ID_CONTRATO = ct.ID_CONTRATO
                                INNER JOIN DDA_C_ESTUDIO est on est.ID_ESTUDIO = cnt.ID_ESTUDIO 
                            WHERE                                 
                                amor.MES &lt;= TO_DATE(#{mes} || '-' || #{anio_final}, 'mm-yyyy' )
                                AND TO_NUMBER( TO_CHAR( amor.MES, 'yyyy' ) ) >= #{anio_inicial}
                                AND cnt.ID_ESTATUS in ( 4, 5, 6 )
                                <if test="estudio != null">
                                  AND cnt.ID_ESTUDIO = #{estudio}
			                    </if> 
			                     <if test="nombreUnico != 'Todos'">
                                    		and ct.nombre_unico = (select nombre_unico from DDA_T_CONTRATO_TITULO where ID_TITULO_CNT = #{nombreUnico}) 
			                    	</if> 
			                    	<if test="temporada != 'Todos'">
                                    	AND ct.temporada = #{temporada}
			                    	</if> 
                        )
                        PIVOT( SUM( MONTO ) FOR 
                            ANNIO in ( ${pivot_data} ))
                            ))
 WHERE NOT_SERIE_SINGLE != 1
              GROUP BY GROUPING SETS (( ),
                                                      (DESC_ESTUDIO),
                                                      (DESC_ESTUDIO, NOMBRE_UNICO),
                                                      (DESC_ESTUDIO, NOMBRE_UNICO, DESC_TITULO))
              )
      ) amor,
      (
        SELECT * FROM (
           SELECT 
                CASE 
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NOT NULL AND DESC_TITULO IS NOT NULL AND NOMBRE_UNICO = DESC_TITULO THEN
                  1
                   ELSE
                  0              
                END as NOT_SERIE_SINGLE,
                CASE
                   WHEN DESC_ESTUDIO IS NULL AND NOMBRE_UNICO IS NULL AND DESC_TITULO IS NULL THEN
                  'TOTAL'
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NULL AND DESC_TITULO IS NULL THEN
                  DESC_ESTUDIO
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NOT NULL AND DESC_TITULO IS NULL THEN
                  NOMBRE_UNICO
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NOT NULL AND DESC_TITULO IS NOT NULL THEN
                  DESC_TITULO               
                END as DESCRIPCION,
                SUM( ANIO_0 ) as ANIO_0, SUM( ANIO_1 ) as ANIO_1, SUM( ANIO_2 ) as ANIO_2, SUM( ANIO_3 ) as ANIO_3, SUM( ANIO_4 ) as ANIO_4
           FROM 
            (
                SELECT
                    DESC_ESTUDIO, 
                    DESC_TITULO,
                    NOMBRE_UNICO,
                    NVL( ANIO_0, 0 ) as ANIO_0, NVL( ANIO_1, 0 ) as ANIO_1, 
                    NVL( ANIO_2, 0 ) as ANIO_2, NVL( ANIO_3, 0 ) as ANIO_3, 
                    NVL( ANIO_4, 0 ) as ANIO_4
                FROM
                (
                    SELECT
                        e.DESC_ESTUDIO, 
                        vc.DESC_TITULO, vc.NOMBRE_UNICO,  
                        TO_NUMBER( TO_CHAR( vc.MES, 'yyyy' ) ) as ANNIO,
                        TO_NUMBER( TO_CHAR( vc.MES, 'mm' ) ) as MESES, 
                        SUM( vc.CANTIDAD ) as TOTAL
                    FROM
                        (  
                          WITH base_query AS (      
                             SELECT  ct.ID_LISTA, ct.ID_BV, ct.ID_ESTUDIO, v.MES, v.CANTIDAD, ct.NOMBRE_UNICO, ct.DESC_TITULO
                             FROM  DDA_T_VISUALIZACION v
                               INNER JOIN(   
                                 SELECT  t.ID_LISTA, t.ID_BV, c.ID_ESTUDIO, t.FECHA_INICIO, t.FECHA_FIN, t.NOMBRE_UNICO, t.DESC_TITULO
                                 FROM DDA_T_CONTRATO_TITULO t
                                   INNER JOIN DDA_T_CONTRATO c on c.ID_CONTRATO = t.ID_CONTRATO
                                 WHERE c.ID_ESTATUS in ( 4, 5, 6 )
                                <if test="estudio != null">
                                       AND c.ID_ESTUDIO = #{estudio}
                                </if>
                                 <if test="nombreUnico != 'Todos'">
                                    		and t.nombre_unico = (select nombre_unico from DDA_T_CONTRATO_TITULO where ID_TITULO_CNT = #{nombreUnico}) 
			                    	</if> 
			                    	<if test="temporada != 'Todos'">
                                    	AND t.temporada = #{temporada}
			                    	</if> 
                               )ct ON ct.ID_BV = v.ID_BV
                             WHERE v.MES BETWEEN ADD_MONTHS((LAST_DAY(ct.FECHA_INICIO)+1),-1) AND LAST_DAY(ct.FECHA_FIN)
                                  AND v.MES &lt;= TO_DATE(#{mes} || '-' ||#{anio_final}, 'mm-yyyy' )                 
                                  AND TO_NUMBER( TO_CHAR( v.MES, 'yyyy' ) ) = #{anio_final}
                          )
                           SELECT DISTINCT a.ID_BV, a.ID_ESTUDIO, a.MES, a.CANTIDAD, a.NOMBRE_UNICO, a.DESC_TITULO 
                           FROM base_query a
                             INNER JOIN (
                               SELECT ID_BV, ID_ESTUDIO, MES, MAX(ID_LISTA) AS ID_LISTA  FROM base_query 
                               GROUP BY ID_BV, ID_ESTUDIO, MES
                           ) b ON a.ID_BV = b.ID_BV AND a.MES = b.MES AND a.ID_ESTUDIO = b.ID_ESTUDIO AND a.ID_LISTA = b.ID_LISTA 

                        ) vc
                        INNER JOIN DDA_C_ESTUDIO e ON e.ID_ESTUDIO = vc.ID_ESTUDIO 
                    GROUP BY e.DESC_ESTUDIO, vc.MES, vc.DESC_TITULO, vc.NOMBRE_UNICO
                )
                PIVOT( SUM( TOTAL ) FOR 
                    ANNIO in ( ${pivot_data} )
                    )   
            )GROUP BY
                GROUPING SETS
                (
                      ( ),
                      (DESC_ESTUDIO),
                      (DESC_ESTUDIO, NOMBRE_UNICO),
                      (DESC_ESTUDIO, NOMBRE_UNICO, DESC_TITULO)
                )
        ) WHERE NOT_SERIE_SINGLE != 1
      ) visual
    WHERE
      amor.DESCRIPCION = visual.DESCRIPCION(+)
    ORDER BY ORDEN_N1, DESC_ESTUDIO, ORDEN_N2 DESC, NOMBRE_UNICO, ORDEN_N3 DESC, DESC_TITULO, ORDEN_N4 DESC
   </select>
	 
	 <select id="obtenerVisualizacionesPrincipal" resultMap="resultVisualizacionesPrincipal" parameterType="map">
	 SELECT *
       FROM DDA_T_CARGAVS_PRINCIPAL
       WHERE ID_CARGA =
        (SELECT MAX(ID_CARGA) AS ID_CARGA
          FROM DDA_T_CARGAVS_PRINCIPAL
          WHERE TRUNC(mes, 'MONTH') = TO_DATE(#{mes} || '-' || #{anio}, 'mm-yyyy' )       
        )
	 </select>
	 
	 <select id="obtenerVisualizacionesDetalle" resultMap="resultVisualizacionesDetalle" parameterType="map">
     SELECT a.*
       FROM DDA_T_CARGAVS_DETALLE_LDR a
       WHERE a.ID_CARGA =
        (SELECT MAX(ID_CARGA)
          FROM DDA_T_CARGAVS_PRINCIPAL
          WHERE TRUNC(mes, 'MONTH') = TO_DATE(#{mes} || '-' || #{anio}, 'mm-yyyy' )        
        )
	 </select>
	 
	 <select id="obtenerVisualizaciones" resultMap="resultVisualizacionesDetalle" parameterType="map">
     SELECT *
     FROM
       (SELECT a.ID_BV AS ID_BROADVIEW, a.MES, a.CANTIDAD AS VISUALIZACIONES, a.TITULO, a.ID_CARGA,
         ROWNUM rnum
       FROM
         (SELECT v.*,
           TO_CHAR(v.mes, 'mon-YY') AS MES_STRING
         FROM DDA_T_VISUALIZACION v
         WHERE v.ID_CARGA =
           (SELECT MAX(ID_CARGA)
           FROM DDA_T_CARGAVS_PRINCIPAL
           WHERE TRUNC(mes, 'MONTH') = TO_DATE(#{mes}
             || '-'
             || #{anio}, 'mm-yyyy' )
           )
         ORDER BY v.ID_VISUALIZACION ASC
         ) a
       WHERE ROWNUM &lt;= 1000
       )
     WHERE rnum >= 1
	 </select>	
	 
   <select id="obtenerCostPerViewDiciembreDatosEstudioNivel" resultMap="resultQueryMensual" parameterType="map">
    select
        amor.ESTUDIO,
        amor.MES_12 as AMOR_DIC,
        visual.MES_12 as VIS_DIC,
        DECODE( visual.MES_12, 0, 0, ROUND( amor.MES_12 / visual.MES_12, 2 ) ) as CPV_DIC,
        amor.ORDEN
    from
        (
            select
                DECODE( NVL( est.DESC_ESTUDIO, '-' ), '-', 1, 0 ) as ORDEN,
                NVL( est.DESC_ESTUDIO, 'TOTAL' ) as ESTUDIO,                                 
                ROUND( SUM( sub.MES_12 ), 2 ) as MES_12
            from 
                (
                    select 
                        ID_CONTRATO_ORIGINAL, ANNIO,
                         SUM( MES_12 ) as MES_12
                    from (
                        select
                            ID_CONTRATO_ORIGINAL, ANNIO, 
                              NVL( MES_12, 0 ) as MES_12
                        from
                        (
                            select       
                                amor.ID_CONTRATO_ORIGINAL,
                                amor.ID_TITULO_CNTORIG,                
                                TO_NUMBER( TO_CHAR( amor.MES, 'yyyy' ) ) as ANNIO,
                                TO_NUMBER( TO_CHAR( amor.MES, 'mm' ) ) as MESES, amor.MES,
                                TO_NUMBER( amor.MONTO ) as MONTO
                            from
                                DDA_T_AMORTIZACIONCPV amor
                            where                                 
                                TO_DATE( TO_CHAR( amor.MES, 'mm-yyyy' ), 'mm-yyyy' ) &lt;= TO_DATE(12 || '-' || #{anio}, 'mm-yyyy' )
                                and TO_NUMBER( TO_CHAR( amor.MES, 'yyyy' ) ) = #{anio}
                        )
                        PIVOT( SUM( MONTO ) FOR 
                            MESES in ( 12 as MES_12 )
                            )                            
                    )
                    group by  ID_CONTRATO_ORIGINAL, ANNIO
                ) sub,  
                DDA_T_CONTRATO cnt,
                DDA_C_ESTUDIO est
          where
                    sub.ID_CONTRATO_ORIGINAL = cnt.ID_CONTRATO
                    and cnt.ID_ESTUDIO = est.ID_ESTUDIO 
                    and cnt.ID_ESTATUS in ( 4, 5, 6 )
           group by
                grouping sets
                    ( 
                        ( ),
                        est.DESC_ESTUDIO
                    ) 
        ) amor,
        (
            select 
                NVL( DESC_ESTUDIO, 'TOTAL' ) as ESTUDIO,
                 SUM( MES_12 ) as MES_12
            from 
            (
                select
                    DESC_ESTUDIO, 
                    NVL( MES_12, 0 ) as MES_12
                from
                (
                    SELECT
                        e.DESC_ESTUDIO,   
                        TO_NUMBER( TO_CHAR( vc.MES, 'yyyy' ) ) as ANNIO,
                        TO_NUMBER( TO_CHAR( vc.MES, 'mm' ) ) as MESES, 
                        SUM( vc.CANTIDAD ) as TOTAL
                    FROM
                        (  
                          WITH base_query AS (      
                             SELECT  ct.ID_LISTA, ct.ID_BV, ct.ID_ESTUDIO, v.MES, v.CANTIDAD, ct.NOMBRE_UNICO, ct.DESC_TITULO
                             FROM  DDA_T_VISUALIZACION v
                               INNER JOIN(   
                                 SELECT  t.ID_LISTA, t.ID_BV, c.ID_ESTUDIO, t.FECHA_INICIO, t.FECHA_FIN, t.NOMBRE_UNICO, t.DESC_TITULO
                                 FROM DDA_T_CONTRATO_TITULO t
                                   INNER JOIN DDA_T_CONTRATO c on c.ID_CONTRATO = t.ID_CONTRATO
                                 WHERE c.ID_ESTATUS in ( 4, 5, 6 )
                               )ct ON ct.ID_BV = v.ID_BV
                             WHERE v.MES BETWEEN ADD_MONTHS((LAST_DAY(ct.FECHA_INICIO)+1),-1) AND LAST_DAY(ct.FECHA_FIN)
                                  AND TO_DATE( TO_CHAR( v.MES, 'mm-yyyy' ), 'mm-yyyy' ) &lt;= TO_DATE(12 || '-' ||#{anio}, 'mm-yyyy' )                 
                                  AND TO_NUMBER( TO_CHAR( v.MES, 'yyyy' ) ) = #{anio}
                          )
                           SELECT DISTINCT a.ID_BV, a.ID_ESTUDIO, a.MES, a.CANTIDAD, a.NOMBRE_UNICO, a.DESC_TITULO 
                           FROM base_query a
                             INNER JOIN (
                               SELECT ID_BV, ID_ESTUDIO, MES, MAX(ID_LISTA) AS ID_LISTA  FROM base_query 
                               GROUP BY ID_BV, ID_ESTUDIO, MES
                           ) b ON a.ID_BV = b.ID_BV AND a.MES = b.MES AND a.ID_ESTUDIO = b.ID_ESTUDIO AND a.ID_LISTA = b.ID_LISTA 

                        ) vc
                        INNER JOIN DDA_C_ESTUDIO e ON e.ID_ESTUDIO = vc.ID_ESTUDIO 
                    GROUP BY e.DESC_ESTUDIO, vc.MES
                )
                PIVOT( SUM( TOTAL ) FOR 
                    MESES in ( 12 as MES_12 )
                    )   
            )
            group by
                grouping sets
                (
                    (),
                    DESC_ESTUDIO
                )
        ) visual
        where
            amor.ESTUDIO = visual.ESTUDIO(+)
        order by
            ORDEN, amor.ESTUDIO
     </select>  
     
   <select id="obtenerCostPerViewDiciembreDatosTituloNivel" resultMap="resultQueryMensual" parameterType="map">
    SELECT /*obtenerCostPerViewDiciembreDatosTituloNivel*/
        amor.DESCRIPCION as ESTUDIO,
        amor.MES_12 as AMOR_DIC,
        visual.MES_12 as VIS_DIC,
        DECODE( visual.MES_12, 0, 0, ROUND( amor.MES_12 / visual.MES_12, 2 ) ) as CPV_DIC
    FROM
      (
        SELECT * FROM (
           SELECT
                DESC_ESTUDIO,
                NOMBRE_UNICO,
                DESC_TITULO,
                CASE 
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NOT NULL AND DESC_TITULO IS NOT NULL AND NOMBRE_UNICO = DESC_TITULO THEN
                  1
                   ELSE
                  0              
                END as NOT_SERIE_SINGLE,
                CASE 
                   WHEN DESC_ESTUDIO IS NULL AND NOMBRE_UNICO IS NULL AND DESC_TITULO IS NULL THEN
                  1
                   ELSE
                  0               
                END as ORDEN_N1,
                CASE 
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NULL AND DESC_TITULO IS NULL THEN
                  1
                   ELSE
                  0               
                END as ORDEN_N2,
                CASE 
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NOT NULL AND DESC_TITULO IS NULL THEN
                  1
                   ELSE
                  0               
                END as ORDEN_N3,
                CASE 
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NOT NULL AND DESC_TITULO IS NOT NULL THEN
                  1
                   ELSE
                  0               
                END as ORDEN_N4,
                CASE
                   WHEN DESC_ESTUDIO IS NULL AND NOMBRE_UNICO IS NULL AND DESC_TITULO IS NULL THEN
                  'TOTAL'
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NULL AND DESC_TITULO IS NULL THEN
                  DESC_ESTUDIO
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NOT NULL AND DESC_TITULO IS NULL THEN
                  NOMBRE_UNICO
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NOT NULL AND DESC_TITULO IS NOT NULL THEN
                  DESC_TITULO               
                END as DESCRIPCION,
                ROUND( SUM( MES_12 ), 2 ) as MES_12
           FROM 
                (
                        SELECT
                            DESC_ESTUDIO, ANNIO, DESC_TITULO, NOMBRE_UNICO, NVL( MES_12, 0 ) as MES_12
                        FROM
                        (
                            SELECT       
                                est.DESC_ESTUDIO,
                                amor.ID_TITULO_CNTORIG,  
                                ct.DESC_TITULO,
                                ct.NOMBRE_UNICO,
                                TO_NUMBER( TO_CHAR( amor.MES, 'yyyy' ) ) as ANNIO,
                                TO_NUMBER( TO_CHAR( amor.MES, 'mm' ) ) as MESES, --amor.MES,
                                TO_NUMBER( amor.MONTO ) as MONTO
                            FROM
                                DDA_T_AMORTIZACIONCPV amor
                                INNER JOIN DDA_T_CONTRATO_TITULO ct on ct.ID_TITULO_CNTORIG = amor.ID_TITULO_CNTORIG
                                INNER JOIN  DDA_T_CONTRATO cnt on cnt.ID_CONTRATO = amor.ID_CONTRATO_ORIGINAL  
                                       AND  cnt.ID_CONTRATO = ct.ID_CONTRATO
                                INNER JOIN DDA_C_ESTUDIO est on est.ID_ESTUDIO = cnt.ID_ESTUDIO 
                            WHERE                                 
                                TO_DATE( TO_CHAR( amor.MES, 'mm-yyyy' ), 'mm-yyyy' ) &lt;= TO_DATE( 12|| '-' || #{anio}, 'mm-yyyy' )
                                AND TO_NUMBER( TO_CHAR( amor.MES, 'yyyy' ) ) = #{anio}
                                AND cnt.ID_ESTATUS in ( 4, 5, 6 )
                                <if test="estudio != null">
                                    AND cnt.ID_ESTUDIO = #{estudio}
			                    </if> 
			                    <if test="nombreUnico != 'Todos'">
                                    and ct.nombre_unico = (select nombre_unico from DDA_T_CONTRATO_TITULO where ID_TITULO_CNT = #{nombreUnico}) 
			                    </if> 
		                    	<if test="temporada != 'Todos'">
                                   	AND ct.temporada = #{temporada}
		                    	</if> 
                        )
                        PIVOT( SUM( MONTO ) FOR 
                            MESES in (  12 as MES_12 )
                            )                            

           )GROUP BY
                GROUPING SETS
                    ( 
                        ( ),
                        (DESC_ESTUDIO),
                        (DESC_ESTUDIO, NOMBRE_UNICO),
                        (DESC_ESTUDIO, NOMBRE_UNICO, DESC_TITULO)
                    ) 
        ) WHERE NOT_SERIE_SINGLE != 1 
      ) amor,
      (
        SELECT * FROM (
           SELECT 
                CASE 
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NOT NULL AND DESC_TITULO IS NOT NULL AND NOMBRE_UNICO = DESC_TITULO THEN
                  1
                   ELSE
                  0              
                END as NOT_SERIE_SINGLE,
                CASE
                   WHEN DESC_ESTUDIO IS NULL AND NOMBRE_UNICO IS NULL AND DESC_TITULO IS NULL THEN
                  'TOTAL'
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NULL AND DESC_TITULO IS NULL THEN
                  DESC_ESTUDIO
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NOT NULL AND DESC_TITULO IS NULL THEN
                  NOMBRE_UNICO
                   WHEN DESC_ESTUDIO IS NOT NULL AND NOMBRE_UNICO IS NOT NULL AND DESC_TITULO IS NOT NULL THEN
                  DESC_TITULO               
                END as DESCRIPCION, SUM( MES_12 ) as MES_12
           FROM 
            (
                SELECT
                    DESC_ESTUDIO, 
                    DESC_TITULO,
                    NOMBRE_UNICO, NVL( MES_12, 0 ) as MES_12
                FROM
                (
                    SELECT
                        e.DESC_ESTUDIO, 
                        vc.DESC_TITULO, vc.NOMBRE_UNICO,  
                        TO_NUMBER( TO_CHAR( vc.MES, 'yyyy' ) ) as ANNIO,
                        TO_NUMBER( TO_CHAR( vc.MES, 'mm' ) ) as MESES, 
                        SUM( vc.CANTIDAD ) as TOTAL
                    FROM
                        (  
                          WITH base_query AS (      
                             SELECT  ct.ID_LISTA, ct.ID_BV, ct.ID_ESTUDIO, v.MES, v.CANTIDAD, ct.NOMBRE_UNICO, ct.DESC_TITULO
                             FROM  DDA_T_VISUALIZACION v
                               INNER JOIN(   
                                 SELECT  t.ID_LISTA, t.ID_BV, c.ID_ESTUDIO, t.FECHA_INICIO, t.FECHA_FIN, t.NOMBRE_UNICO, t.DESC_TITULO
                                 FROM DDA_T_CONTRATO_TITULO t
                                   INNER JOIN DDA_T_CONTRATO c on c.ID_CONTRATO = t.ID_CONTRATO
                                 WHERE c.ID_ESTATUS in ( 4, 5, 6 )
                                <if test="estudio != null">
                                       AND c.ID_ESTUDIO = #{estudio}
                                </if>
                                <if test="nombreUnico != 'Todos'">
                                    and t.nombre_unico = (select nombre_unico from DDA_T_CONTRATO_TITULO where ID_TITULO_CNT = #{nombreUnico}) 
			                    </if> 
		                    	<if test="temporada != 'Todos'">
                                   	AND t.temporada = #{temporada}
		                    	</if>
                               )ct ON ct.ID_BV = v.ID_BV
                             WHERE v.MES BETWEEN ADD_MONTHS((LAST_DAY(ct.FECHA_INICIO)+1),-1) AND LAST_DAY(ct.FECHA_FIN)
                                  AND TO_DATE( TO_CHAR( v.MES, 'mm-yyyy' ), 'mm-yyyy' ) &lt;= TO_DATE(12 || '-' ||#{anio}, 'mm-yyyy' )                 
                                  AND TO_NUMBER( TO_CHAR( v.MES, 'yyyy' ) ) = #{anio}
                          )
                           SELECT DISTINCT a.ID_BV, a.ID_ESTUDIO, a.MES, a.CANTIDAD, a.NOMBRE_UNICO, a.DESC_TITULO 
                           FROM base_query a
                             INNER JOIN (
                               SELECT ID_BV, ID_ESTUDIO, MES, MAX(ID_LISTA) AS ID_LISTA  FROM base_query 
                               GROUP BY ID_BV, ID_ESTUDIO, MES
                           ) b ON a.ID_BV = b.ID_BV AND a.MES = b.MES AND a.ID_ESTUDIO = b.ID_ESTUDIO AND a.ID_LISTA = b.ID_LISTA 

                        ) vc
                        INNER JOIN DDA_C_ESTUDIO e ON e.ID_ESTUDIO = vc.ID_ESTUDIO 
                    GROUP BY e.DESC_ESTUDIO, vc.MES, vc.DESC_TITULO, vc.NOMBRE_UNICO                
                )
                PIVOT( SUM( TOTAL ) FOR 
                    MESES in ( 12 as MES_12 )
                    )   
            )GROUP BY
                GROUPING SETS
                (
                      ( ),
                      (DESC_ESTUDIO),
                      (DESC_ESTUDIO, NOMBRE_UNICO),
                      (DESC_ESTUDIO, NOMBRE_UNICO, DESC_TITULO)
                )
        ) WHERE NOT_SERIE_SINGLE != 1
      ) visual
    WHERE
      amor.DESCRIPCION = visual.DESCRIPCION(+)
    ORDER BY ORDEN_N1, DESC_ESTUDIO, ORDEN_N2 DESC, NOMBRE_UNICO, ORDEN_N3 DESC, DESC_TITULO, ORDEN_N4 DESC
   </select> 	 
	 
</mapper>