<?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.datosContrato.daos.ReporteDatosContratoDAO">
		 
	 <resultMap id="resultQuery" type="DatosContratoDTO">
<!-- generales -->
	 	<result property="estudio"		column="estudio" />      
		<result property="noContraroDDA"		column="noContraroDDA" />
		<result property="noContratoBV"		column="noContratoBV" />
		<result property="fechaInicio"		column="fechaInicio" />  
		<result property="fechaFin"		column="fechaFin" />     
		<result property="apartado"		column="apartado" />     
		<result property="impuesto"		column="impuesto" />     
		<result property="categorias"		column="categorias" />   
		<result property="servicios"		column="servicios" />    
		<result property="territorios"		column="territorios" />  
		<result property="atributos"		column="atributos" />    
		<result property="filialesType"		column="filialesType" />
 <!-- Terminos comerciales -->
 		<result property="costoC"		column="costoC"/>
		<result property="montoProgramado"		column="montoProgramado"/>
		<result property="anticipo"		column="anticipo"/>
		<result property="tipoPagos"		column="tipoPagos"/>
 <!-- titulos -->		
		<result property="idBv"		column="ID_BV" />
		<result property="descTitulo"		column="DESC_TITULO" />
		<result property="nombreUnico"		column="NOMBRE_UNICO" /> 
		<result property="temporada"		column="temporada" />
		<result property="episodio"		column="episodio" /> 
		<result property="fechaInicioTit"		column="FECHA_INICIO" /> 
		<result property="fechasFinTit"		column="FECHA_FIN" />
		<result property="vidaUtil"		column="vida_util" />
		<result property="desCategoria"		column="desc_categoria" />
		<result property="costo"		column="costo" />
		<result property="precioAnual"		column="precio_anual" />
 <!-- amortizacion -->	
 		<result property="mes1"		column="mes_1" />
 		<result property="mes2"		column="mes_2" />
 		<result property="mes3"		column="mes_3" />
 		<result property="mes4"		column="mes_4" />
 		<result property="mes5"		column="mes_5" />
 		<result property="mes6"		column="mes_6" />
 		<result property="mes7"		column="mes_7" />
 		<result property="mes8"		column="mes_8" />
 		<result property="mes9"		column="mes_9" />
 		<result property="mes10"		column="mes_10" />
 		<result property="mes11"		column="mes_11" />
 		<result property="mes12"		column="mes_12" />
	 </resultMap>
	 <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 != 0">
                                    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 != 0">
                                       AND c.ID_ESTUDIO = #{estudio}
                                </if>
                                 <if test="nombreUnico != 0">
                                    		and t.nombre_unico = (select nombre_unico from DDA_T_CONTRATO_TITULO where ID_TITULO_CNT = #{nombreUnico}) 
			                    	</if> 
			                    	<if test="temporada != 0">
                                    	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> 	 
	 
	 
	<select id="obtenerDatos" resultMap="resultQuery" parameterType="map">
		<if test="dgenerales == 'YES' and tcomerciales == 'NO' and titulos == 'NO' and amortizacion == 'NO'">
SELECT
    dtc.id_contrato,
    dce.desc_estudio estudio,
    dtc.numero_contrato nocontrarodda,
    dtc.id_contrato_bv nocontratobv,
    dtc.fecha_inicio fechainicio,
    dtc.fecha_fin fechafin,
    dta.desc_apartado apartado,
    dcii.descripcion impuesto,
    detalle.categorias,
    detalle.servicios,
    detalle.territorios,
    detalle.atributos,
    detalle.filialesType
FROM
    dda_t_contrato dtc,
    dda_c_estudio dce,
    dda_t_apartado dta,
    dda_c_indicador_iva dcii,
    (
        SELECT
            cat.contrato,
            cat.categorias,
            ser.servicios,
            ter.territorios,
            atr.atributos,
            fil.filialesType
        FROM
            (
                SELECT
                    dce.id_contrato contrato,
                    LISTAGG(ca.desc_categoria,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dce.id_contrato
                    ) categorias
                FROM
                    dda_t_contrato dce,
                    dda_t_contrato_categoria re,
                    dda_c_categoria ca
                WHERE
                    re.id_contrato = dce.id_contrato
                    AND   re.id_categoria = ca.id_categoria
                  
                GROUP BY
                    dce.id_contrato
            ) cat
            LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dst.desc_servicio,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) servicios
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_servicio dtcs,
                    dda_c_servicio_tipo dst
                WHERE
                    dtc.id_contrato = dtcs.id_contrato
                    AND   dst.id_tipo_servicio = dtcs.id_tipo_servicio
                   
                GROUP BY
                    dtc.id_contrato
            ) ser ON cat.contrato = ser.contrato
            LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dct.desc_territorio,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) territorios
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_territorio dtct,
                    dda_c_territorio dct
                WHERE
                    dtc.id_contrato = dtct.id_contrato
                    AND   dct.id_territorio = dtct.id_territorio
                 
                GROUP BY
                    dtc.id_contrato
            ) ter ON ser.contrato = ter.contrato
            LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dct.desc_medio_entrega,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) atributos
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_medio_entrega dtcme,
                    dda_c_medio_entrega dct
                WHERE
                    dtc.id_contrato = dtcme.id_contrato
                    AND   dct.id_medio_entrega = dtcme.id_medio_entrega
                   
                GROUP BY
                    dtc.id_contrato
            ) atr ON ser.contrato = atr.contrato
             LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dct.desc_filial,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) filialesType
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_deal_filial dtct,
                    dda_c_filial dct
                WHERE
                    dtc.id_contrato = dtct.id_contrato
                    AND   dct.id_filial = dtct.id_filial
                GROUP BY
                    dtc.id_contrato
            ) fil ON fil.contrato = atr.contrato
    ) detalle
WHERE
    dtc.id_estudio = dce.id_estudio
    AND   dtc.id_estatus IN (
        4,
        5,
        6
    )
    AND   dtc.id_apartado = dta.id_apartado
    AND   dtc.clave_iva = dcii.clave_iva
    AND   detalle.contrato = dtc.id_contrato
    <if test="estudio != 0">
        AND   dtc.id_estudio = #{estudio}
    </if>
    <if test="contrato != 0">
        AND dtc.id_contrato = #{contrato}
    </if>
    
    <if test="nombreUnico != 0 and temporada != 0">
        AND dtc.id_contrato in (select id_contrato from DDA_T_CONTRATO_TITULO where ID_TITULO_CNT = #{nombreUnico} and temporada = #{temporada})
    </if>
    <if test="nombreUnico != 0  and temporada == 0" >
        AND dtc.id_contrato in (select id_contrato from DDA_T_CONTRATO_TITULO where ID_TITULO_CNT = #{nombreUnico})
    </if>		
		 	
		</if>
		<if test="dgenerales == 'YES' and tcomerciales == 'YES' and titulos == 'NO' and amortizacion == 'NO'">
		SELECT
			--generales
			    dtc.id_contrato,
			    dce.desc_estudio estudio,
			    dtc.numero_contrato nocontrarodda,
			    dtc.id_contrato_bv nocontratobv,
			    dtc.fecha_inicio fechainicio,
			    dtc.fecha_fin fechafin,
			    dta.desc_apartado apartado,
			    dcii.descripcion impuesto,
  		        detalle.categorias,
                detalle.servicios,
                detalle.territorios,
                detalle.atributos,
                detalle.filialesType,
			--tComerciales ***************************
			    nvl(dtctep.MONTO,0) montoProgramado,
			    nvl(dtcte.ANTICIPO_MONTO,0) anticipo, 
			    dtcte.TIPO_PAGO tipoPagos,
			    SUM(NVL(dtctt.COSTO,0)) AS costoC
			FROM
			    --generales
			    dda_t_contrato dtc 
			    --tComerciales
			    inner join DDA_T_CONTRATO_TERMINO dtcte on dtc.id_contrato = dtcte.id_contrato 
			    left outer join DDA_T_CONTRATO_TERMINO_PAGO dtctep on dtc.id_contrato = dtctep.id_contrato
			    ,DDA_T_CONTRATO_TITULO dtctt
			    ,dda_c_estudio dce,
			    dda_t_apartado dta,
			    dda_c_indicador_iva dcii,
                (
        SELECT
            cat.contrato,
            cat.categorias,
            ser.servicios,
            ter.territorios,
            atr.atributos,
            fil.filialesType
        FROM
            (
                SELECT
                    dce.id_contrato contrato,
                    LISTAGG(ca.desc_categoria,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dce.id_contrato
                    ) categorias
                FROM
                    dda_t_contrato dce,
                    dda_t_contrato_categoria re,
                    dda_c_categoria ca
                WHERE
                    re.id_contrato = dce.id_contrato
                    AND   re.id_categoria = ca.id_categoria
                    --AND   dce.id_estudio = 196
                GROUP BY
                    dce.id_contrato
            ) cat
            LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dst.desc_servicio,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) servicios
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_servicio dtcs,
                    dda_c_servicio_tipo dst
                WHERE
                    dtc.id_contrato = dtcs.id_contrato
                    AND   dst.id_tipo_servicio = dtcs.id_tipo_servicio
                    --AND   dtc.id_estudio = 196
                GROUP BY
                    dtc.id_contrato
            ) ser ON cat.contrato = ser.contrato
            LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dct.desc_territorio,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) territorios
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_territorio dtct,
                    dda_c_territorio dct
                WHERE
                    dtc.id_contrato = dtct.id_contrato
                    AND   dct.id_territorio = dtct.id_territorio
                    --AND   dtc.id_estudio = 196
                GROUP BY
                    dtc.id_contrato
            ) ter ON ser.contrato = ter.contrato
            LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dct.desc_medio_entrega,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) atributos
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_medio_entrega dtcme,
                    dda_c_medio_entrega dct
                WHERE
                    dtc.id_contrato = dtcme.id_contrato
                    AND   dct.id_medio_entrega = dtcme.id_medio_entrega
                    --AND   dtc.id_estudio = 196
                GROUP BY
                    dtc.id_contrato
            ) atr ON ser.contrato = atr.contrato
             LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dct.desc_filial,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) filialesType
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_deal_filial dtct,
                    dda_c_filial dct
                WHERE
                    dtc.id_contrato = dtct.id_contrato
                    AND   dct.id_filial = dtct.id_filial
                GROUP BY
                    dtc.id_contrato
            ) fil ON fil.contrato = atr.contrato
    ) detalle
			WHERE
			    dtc.id_estudio = dce.id_estudio
			    AND   dtc.id_estatus IN (
			        4,
			        5,
			        6
			    )
			    AND   dtc.id_apartado = dta.id_apartado
			    AND   dtc.clave_iva = dcii.clave_iva
                AND   detalle.contrato = dtc.id_contrato
			<if test="estudio != 0">
			    AND   dtc.id_estudio =#{estudio}
			</if>    
			<if test="contrato != 0">
			  	AND dtc.id_contrato = #{contrato}
			</if>
			<if test="nombreUnico != 0 and temporada != 0">
			  	AND dtc.id_contrato in (select id_contrato from DDA_T_CONTRATO_TITULO where ID_TITULO_CNT = #{nombreUnico} and temporada = #{temporada})
			</if>
			<if test="nombreUnico != 0  and temporada == 0" >
			  	AND dtc.id_contrato in (select id_contrato from DDA_T_CONTRATO_TITULO where ID_TITULO_CNT = #{nombreUnico})
			</if>		
			group by 
			    dtc.id_contrato,
			    dce.desc_estudio,
			    dtc.numero_contrato,
			    dtc.id_contrato_bv,
			    dtc.fecha_inicio,
			    dtc.fecha_fin,
			    dta.desc_apartado,
			    dcii.descripcion,
			   detalle.categorias,
                detalle.servicios,
                detalle.territorios,
                detalle.atributos,
                detalle.filialesType,
			    --tcomerciales
			   dtctep.MONTO,
			   dtcte.ANTICIPO_MONTO, 
			   dtcte.TIPO_PAGO
			
		</if>
		<if test="dgenerales == 'YES' and tcomerciales == 'YES' and titulos == 'YES' and amortizacion == 'NO'">
		
		SELECT
    dtc.id_contrato,
    dce.desc_estudio estudio,
    dtc.numero_contrato nocontrarodda,
    dtc.id_contrato_bv nocontratobv,
    dtc.fecha_inicio fechainicio,
    dtc.fecha_fin fechafin,
    dta.desc_apartado apartado,
    dcii.descripcion impuesto,
    detalle.categorias,
    detalle.servicios,
    detalle.territorios,
    detalle.atributos,
    detalle.filialesType,
    --titulos
     dtctt.ID_BV,
		     dtctt.DESC_TITULO,
		     dtctt.NOMBRE_UNICO, 
		     CASE 
		         WHEN dtctt.temporada is null and dtctt.episodio is not null THEN '00'
		         when dtctt.temporada is null and dtctt.episodio is null then 'PELICULA'
		         when dtctt.temporada is not null and dtctt.episodio is not null THEN dtctt.temporada
		     END as temporada,
		     Nvl(dtctt.EPISODIO,'--') episodio, 
		     dtctt.FECHA_INICIO, 
		     dtctt.FECHA_FIN,
		     dtctt.VIDA_UTIL,
		     dtcct.DESC_CATEGORIA,
		     dtctt.COSTO,
		     dtctt.PRECIO_ANUAL ,
    -- terminos comerciales
    terminos.contrato,
			terminos.montoProgramado,
		    terminos.anticipo, 
		    terminos.tipoPagos,
		    terminos.costoC
    
FROM
    dda_t_contrato dtc,
    dda_c_estudio dce,
    dda_t_apartado dta,
    dda_c_indicador_iva dcii,
    (
        SELECT
            cat.contrato,
            cat.categorias,
            ser.servicios,
            ter.territorios,
            atr.atributos,
            fil.filialesType
        FROM
            (
                SELECT
                    dce.id_contrato contrato,
                    LISTAGG(ca.desc_categoria,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dce.id_contrato
                    ) categorias
                FROM
                    dda_t_contrato dce,
                    dda_t_contrato_categoria re,
                    dda_c_categoria ca
                WHERE
                    re.id_contrato = dce.id_contrato
                    AND   re.id_categoria = ca.id_categoria
                  
                GROUP BY
                    dce.id_contrato
            ) cat
            LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dst.desc_servicio,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) servicios
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_servicio dtcs,
                    dda_c_servicio_tipo dst
                WHERE
                    dtc.id_contrato = dtcs.id_contrato
                    AND   dst.id_tipo_servicio = dtcs.id_tipo_servicio
                   
                GROUP BY
                    dtc.id_contrato
            ) ser ON cat.contrato = ser.contrato
            LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dct.desc_territorio,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) territorios
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_territorio dtct,
                    dda_c_territorio dct
                WHERE
                    dtc.id_contrato = dtct.id_contrato
                    AND   dct.id_territorio = dtct.id_territorio
                 
                GROUP BY
                    dtc.id_contrato
            ) ter ON ser.contrato = ter.contrato
            LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dct.desc_medio_entrega,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) atributos
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_medio_entrega dtcme,
                    dda_c_medio_entrega dct
                WHERE
                    dtc.id_contrato = dtcme.id_contrato
                    AND   dct.id_medio_entrega = dtcme.id_medio_entrega
                   
                GROUP BY
                    dtc.id_contrato
            ) atr ON ser.contrato = atr.contrato
             LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dct.desc_filial,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) filialesType
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_deal_filial dtct,
                    dda_c_filial dct
                WHERE
                    dtc.id_contrato = dtct.id_contrato
                    AND   dct.id_filial = dtct.id_filial
                GROUP BY
                    dtc.id_contrato
            ) fil ON fil.contrato = atr.contrato
    ) detalle 
    left outer JOIN
    (
		SELECT
		    dtc.id_contrato contrato,
			nvl(dtctep.MONTO,0) montoProgramado,
		    nvl(dtcte.ANTICIPO_MONTO,0) anticipo, 
		    dtcte.TIPO_PAGO tipoPagos,
		    SUM(NVL(dtctt.COSTO,0)) AS costoC
		FROM
		    dda_t_contrato dtc 
		    inner join DDA_T_CONTRATO_TERMINO dtcte on dtc.id_contrato = dtcte.id_contrato 
		    left outer join DDA_T_CONTRATO_TERMINO_PAGO dtctep on dtc.id_contrato = dtctep.id_contrato,
		    DDA_T_CONTRATO_TITULO dtctt
		  
		WHERE
		    dtc.id_estatus IN (
		        4,
		        5,
		        6
		    )
		    AND dtc.id_contrato = dtctt.id_contrato 
		group by
		    dtc.id_contrato,
		    dtctep.MONTO
		   	,dtcte.ANTICIPO_MONTO
		   	,dtcte.TIPO_PAGO
		    ) terminos on terminos.contrato = detalle.contrato
    ,DDA_T_CONTRATO_TITULO dtctt
    ,DDA_C_CATEGORIA dtcct
WHERE
    dtc.id_estudio = dce.id_estudio
    AND   dtc.id_estatus IN (
        4,
        5,
        6
    )
    AND   dtc.id_apartado = dta.id_apartado
    AND   dtc.clave_iva = dcii.clave_iva
    AND   detalle.contrato = dtc.id_contrato
    AND dtc.id_contrato = dtctt.id_contrato
	AND   dtctt.ID_CATEGORIA_TIT = dtcct.ID_CATEGORIA
    <if test="estudio != 0">
				AND   dtc.id_estudio = #{estudio}
			</if>
		    <if test="contrato != 0">
			  	AND dtc.id_contrato = #{contrato}
			</if>
		    
		   <if test="nombreUnico != 0 and temporada != 0">
			  	AND dtctt.ID_TITULO_CNT = #{nombreUnico} 
			  	and dtctt.temporada = #{temporada}
			</if>
			<if test="nombreUnico != 0  and temporada == 0" >
			  	AND dtctt.id_titulo_cnt = #{nombreUnico}
			</if>	
		
		
		</if>
		<if test="dgenerales == 'YES' and tcomerciales == 'YES' and titulos == 'YES' and amortizacion == 'YES'">
			SELECT
    --generales
    dtc.id_contrato,
    dce.desc_estudio estudio,
    dtc.numero_contrato nocontrarodda,
    dtc.id_contrato_bv nocontratobv,
    dtc.fecha_inicio fechainicio,
    dtc.fecha_fin fechafin,
    dta.desc_apartado apartado,
    dcii.descripcion impuesto,
    detalle.categorias,
    detalle.servicios,
    detalle.territorios,
    detalle.atributos,
    detalle.filialesType,
    -- terminos comerciales
    terminos.contrato,
    terminos.montoProgramado,
    terminos.anticipo, 
    terminos.tipoPagos,
    terminos.costoC,
    --titulos
    dtctt.id_titulo_cnt,
    dtctt.ID_BV,
    dtctt.DESC_TITULO,
    dtctt.NOMBRE_UNICO, 
    CASE 
        WHEN dtctt.temporada is null and dtctt.episodio is not null THEN '00'
        when dtctt.temporada is null and dtctt.episodio is null then 'PELICULA'
        when dtctt.temporada is not null and dtctt.episodio is not null THEN dtctt.temporada
    END as temporada,
    Nvl(dtctt.EPISODIO,'--') episodio, 
    dtctt.FECHA_INICIO, 
    dtctt.FECHA_FIN,
    dtctt.VIDA_UTIL,
    dtcct.DESC_CATEGORIA,
    dtctt.COSTO,
    dtctt.PRECIO_ANUAL ,
    -- amortizacion
    amor.estudio,
    amor.nocontrarodda,
    amor.nombre_unico,
    amor.episodio,
    amor.mes_1,
    amor.mes_2,
    amor.mes_3,
    amor.mes_4,
    amor.mes_5,
    amor.mes_6,
    amor.mes_7,
    amor.mes_8,
    amor.mes_9,
    amor.mes_10,
    amor.mes_11,
    amor.mes_12
FROM
    dda_t_contrato dtc,
    dda_c_estudio dce,
    dda_t_apartado dta,
    dda_c_indicador_iva dcii,
    (
        SELECT
            cat.contrato,
            cat.categorias,
            ser.servicios,
            ter.territorios,
            atr.atributos,
            fil.filialesType
        FROM
            (
                SELECT
                    dce.id_contrato contrato,
                    LISTAGG(ca.desc_categoria,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dce.id_contrato
                    ) categorias
                FROM
                    dda_t_contrato dce,
                    dda_t_contrato_categoria re,
                    dda_c_categoria ca
                WHERE
                    re.id_contrato = dce.id_contrato
                    AND   re.id_categoria = ca.id_categoria
                  
                GROUP BY
                    dce.id_contrato
            ) cat
            LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dst.desc_servicio,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) servicios
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_servicio dtcs,
                    dda_c_servicio_tipo dst
                WHERE
                    dtc.id_contrato = dtcs.id_contrato
                    AND   dst.id_tipo_servicio = dtcs.id_tipo_servicio
                   
                GROUP BY
                    dtc.id_contrato
            ) ser ON cat.contrato = ser.contrato
            LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dct.desc_territorio,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) territorios
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_territorio dtct,
                    dda_c_territorio dct
                WHERE
                    dtc.id_contrato = dtct.id_contrato
                    AND   dct.id_territorio = dtct.id_territorio
                 
                GROUP BY
                    dtc.id_contrato
            ) ter ON ser.contrato = ter.contrato
            LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dct.desc_medio_entrega,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) atributos
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_medio_entrega dtcme,
                    dda_c_medio_entrega dct
                WHERE
                    dtc.id_contrato = dtcme.id_contrato
                    AND   dct.id_medio_entrega = dtcme.id_medio_entrega
                   
                GROUP BY
                    dtc.id_contrato
            ) atr ON ser.contrato = atr.contrato
             LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dct.desc_filial,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) filialesType
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_deal_filial dtct,
                    dda_c_filial dct
                WHERE
                    dtc.id_contrato = dtct.id_contrato
                    AND   dct.id_filial = dtct.id_filial
                GROUP BY
                    dtc.id_contrato
            ) fil ON fil.contrato = atr.contrato
    ) detalle 
    left outer JOIN
    (
		SELECT
		    dtc.id_contrato contrato,
			nvl(dtctep.MONTO,0) montoProgramado,
		    nvl(dtcte.ANTICIPO_MONTO,0) anticipo, 
		    dtcte.TIPO_PAGO tipoPagos,
		    SUM(NVL(dtctt.COSTO,0)) AS costoC
		FROM
		    dda_t_contrato dtc 
		    inner join DDA_T_CONTRATO_TERMINO dtcte on dtc.id_contrato = dtcte.id_contrato 
		    left outer join DDA_T_CONTRATO_TERMINO_PAGO dtctep on dtc.id_contrato = dtctep.id_contrato,
		    DDA_T_CONTRATO_TITULO dtctt
		  
		WHERE
		   dtc.id_contrato = dtctt.id_contrato 
		group by
		    dtc.id_contrato,
		    dtctep.MONTO
		   	,dtcte.ANTICIPO_MONTO
		   	,dtcte.TIPO_PAGO
		    ) terminos on terminos.contrato = detalle.contrato
    ,DDA_T_CONTRATO_TITULO dtctt
    join (
        SELECT
                desc_estudio estudio,
			    numero_contrato nocontrarodda,
                ID_TITULO_CNT,
			    nombre_unico nombre_unico,
			    temporada episodio,
			    mes_1,
			    mes_2,
			    mes_3,
			    mes_4,
			    mes_5,
			    mes_6,
			    mes_7,
			    mes_8,
			    mes_9,
			    mes_10,
			    mes_11,
			    mes_12
			FROM
			    (
			        SELECT
                        tit.ID_TITULO_CNT,
                        dce.desc_estudio,
			            cnt.numero_contrato,
			            tit.nombre_unico,
			            CASE 
					         WHEN tit.temporada is null and tit.episodio is not null THEN '00'
					         when tit.temporada is null and tit.episodio is null then 'PELICULA'
					         when tit.temporada is not null and tit.episodio is not null THEN tit.temporada
					     END as temporada,
			            TO_CHAR(amr.mes,'MM/yyyy') AS meses,
			            SUM(amr.monto) AS monto
			        FROM
			            dda_t_contrato cnt
			            INNER JOIN dda_t_contrato_titulo tit ON cnt.id_contrato = tit.id_contrato
			            LEFT JOIN dda_t_amortizacion amr ON tit.ID_TITULO_CNT = amr.id_titulo_cntorig
			            , dda_c_estudio dce
			          
			        WHERE
			           1 = 1
			            <if test="estudio != 0">
			                AND   cnt.id_estudio = #{estudio}
			            </if>
			            <if test="contrato != 0">
			                AND   cnt.id_contrato = #{contrato}
			            </if>
			             <if test="nombreUnico != 0 and temporada != 0">
						  	AND tit.ID_TITULO_CNT = #{nombreUnico} 
						  	and tit.temporada = #{temporada}
						</if>
						<if test="nombreUnico != 0  and temporada == 0" >
						  	AND tit.id_titulo_cnt = #{nombreUnico}
						</if>	
			             AND   amr.mes &gt;= add_months(trunc(TO_DATE(#{fecha},'MM/yyyy'),'mm'),-12)
						            AND   amr.mes &lt;= TO_DATE(#{fecha},'MM/yyyy')
			            AND   cnt.id_estudio = dce.id_estudio
			        GROUP BY
                        tit.ID_TITULO_CNT,
			            dce.desc_estudio,
			            cnt.numero_contrato,
			            tit.nombre_unico,
			            CASE 
					         WHEN tit.temporada is null and tit.episodio is not null THEN '00'
					         when tit.temporada is null and tit.episodio is null then 'PELICULA'
					         when tit.temporada is not null and tit.episodio is not null THEN tit.temporada
					     END ,
			            amr.mes
			    )
			         PIVOT ( SUM ( monto )
						            FOR meses
						            IN (  ${pivot_data}  )
						        )
			        order by 1,2,3,4
    ) amor on amor.ID_TITULO_CNT = dtctt.ID_TITULO_CNT
     ,DDA_C_CATEGORIA dtcct
  
WHERE
    dtc.id_estudio = dce.id_estudio
    AND   dtc.id_estatus IN (
        4,
        5,
        6
    )
    AND   dtc.id_apartado = dta.id_apartado
    AND   dtc.clave_iva = dcii.clave_iva
    AND   detalle.contrato = dtc.id_contrato
    AND dtc.id_contrato = dtctt.id_contrato
	AND   dtctt.ID_CATEGORIA_TIT = dtcct.ID_CATEGORIA
   <if test="estudio != 0">
        AND   dtc.id_estudio = #{estudio}
    </if>
    <if test="contrato != 0">
        AND dtc.id_contrato = #{contrato}
    </if>
    
    <if test="nombreUnico != 0 and temporada != 0">
			  	AND dtctt.ID_TITULO_CNT = #{nombreUnico} 
			  	and dtctt.temporada = #{temporada}
			</if>
			<if test="nombreUnico != 0  and temporada == 0" >
			  	AND dtctt.id_titulo_cnt = #{nombreUnico}
			</if>	

    	
    
		
		</if>
		<if test="dgenerales == 'YES' and tcomerciales == 'NO' and titulos == 'YES' and amortizacion == 'YES'">
		SELECT
    --generales
    dtc.id_contrato,
    dce.desc_estudio estudio,
    dtc.numero_contrato nocontrarodda,
    dtc.id_contrato_bv nocontratobv,
    dtc.fecha_inicio fechainicio,
    dtc.fecha_fin fechafin,
    dta.desc_apartado apartado,
    dcii.descripcion impuesto,
    detalle.categorias,
    detalle.servicios,
    detalle.territorios,
    detalle.atributos,
    detalle.filialesType,
    --titulos
    dtctt.id_titulo_cnt,
    dtctt.ID_BV,
    dtctt.DESC_TITULO,
    dtctt.NOMBRE_UNICO, 
    CASE 
        WHEN dtctt.temporada is null and dtctt.episodio is not null THEN '00'
        when dtctt.temporada is null and dtctt.episodio is null then 'PELICULA'
        when dtctt.temporada is not null and dtctt.episodio is not null THEN dtctt.temporada
    END as temporada,
    Nvl(dtctt.EPISODIO,'--') episodio, 
    dtctt.FECHA_INICIO, 
    dtctt.FECHA_FIN,
    dtctt.VIDA_UTIL,
    dtcct.DESC_CATEGORIA,
    dtctt.COSTO,
    dtctt.PRECIO_ANUAL ,
    -- amortizacion
    amor.estudio,
    amor.nocontrarodda,
    amor.nombre_unico,
    amor.episodio,
    amor.mes_1,
    amor.mes_2,
    amor.mes_3,
    amor.mes_4,
    amor.mes_5,
    amor.mes_6,
    amor.mes_7,
    amor.mes_8,
    amor.mes_9,
    amor.mes_10,
    amor.mes_11,
    amor.mes_12
FROM
    dda_t_contrato dtc,
    dda_c_estudio dce,
    dda_t_apartado dta,
    dda_c_indicador_iva dcii,
    (
        SELECT
            cat.contrato,
            cat.categorias,
            ser.servicios,
            ter.territorios,
            atr.atributos,
            fil.filialesType
        FROM
            (
                SELECT
                    dce.id_contrato contrato,
                    LISTAGG(ca.desc_categoria,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dce.id_contrato
                    ) categorias
                FROM
                    dda_t_contrato dce,
                    dda_t_contrato_categoria re,
                    dda_c_categoria ca
                WHERE
                    re.id_contrato = dce.id_contrato
                    AND   re.id_categoria = ca.id_categoria
                  
                GROUP BY
                    dce.id_contrato
            ) cat
            LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dst.desc_servicio,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) servicios
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_servicio dtcs,
                    dda_c_servicio_tipo dst
                WHERE
                    dtc.id_contrato = dtcs.id_contrato
                    AND   dst.id_tipo_servicio = dtcs.id_tipo_servicio
                   
                GROUP BY
                    dtc.id_contrato
            ) ser ON cat.contrato = ser.contrato
            LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dct.desc_territorio,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) territorios
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_territorio dtct,
                    dda_c_territorio dct
                WHERE
                    dtc.id_contrato = dtct.id_contrato
                    AND   dct.id_territorio = dtct.id_territorio
                 
                GROUP BY
                    dtc.id_contrato
            ) ter ON ser.contrato = ter.contrato
            LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dct.desc_medio_entrega,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) atributos
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_medio_entrega dtcme,
                    dda_c_medio_entrega dct
                WHERE
                    dtc.id_contrato = dtcme.id_contrato
                    AND   dct.id_medio_entrega = dtcme.id_medio_entrega
                   
                GROUP BY
                    dtc.id_contrato
            ) atr ON ser.contrato = atr.contrato
             LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dct.desc_filial,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) filialesType
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_deal_filial dtct,
                    dda_c_filial dct
                WHERE
                    dtc.id_contrato = dtct.id_contrato
                    AND   dct.id_filial = dtct.id_filial
                GROUP BY
                    dtc.id_contrato
            ) fil ON fil.contrato = atr.contrato
    ) detalle 
    ,DDA_T_CONTRATO_TITULO dtctt
    join (
        SELECT
                desc_estudio estudio,
			    numero_contrato nocontrarodda,
                ID_TITULO_CNT,
			    nombre_unico nombre_unico,
			    temporada episodio,
			    mes_1,
			    mes_2,
			    mes_3,
			    mes_4,
			    mes_5,
			    mes_6,
			    mes_7,
			    mes_8,
			    mes_9,
			    mes_10,
			    mes_11,
			    mes_12
			FROM
			    (
			        SELECT
                        tit.ID_TITULO_CNT,
                        dce.desc_estudio,
			            cnt.numero_contrato,
			            tit.nombre_unico,
			            CASE 
					         WHEN tit.temporada is null and tit.episodio is not null THEN '00'
					         when tit.temporada is null and tit.episodio is null then 'PELICULA'
					         when tit.temporada is not null and tit.episodio is not null THEN tit.temporada
					     END as temporada,
			            TO_CHAR(amr.mes,'MM/yyyy') AS meses,
			            SUM(amr.monto) AS monto
			        FROM
			            dda_t_contrato cnt
			            INNER JOIN dda_t_contrato_titulo tit ON cnt.id_contrato = tit.id_contrato
			            LEFT JOIN dda_t_amortizacion amr ON tit.ID_TITULO_CNT = amr.id_titulo_cntorig
			            , dda_c_estudio dce
			          
			        WHERE
			           1 = 1
			            <if test="estudio != 0">
			                AND   cnt.id_estudio = #{estudio}
			            </if>
			            <if test="contrato != 0">
			                AND   cnt.id_contrato = #{contrato}
			            </if>
			             <if test="nombreUnico != 0 and temporada != 0">
						  	AND tit.ID_TITULO_CNT = #{nombreUnico} 
						  	and tit.temporada = #{temporada}
						</if>
						<if test="nombreUnico != 0  and temporada == 0" >
						  	AND tit.id_titulo_cnt = #{nombreUnico}
						</if>	
			             AND   amr.mes &gt;= add_months(trunc(TO_DATE(#{fecha},'MM/yyyy'),'mm'),-12)
						            AND   amr.mes &lt;= TO_DATE(#{fecha},'MM/yyyy')
			            AND   cnt.id_estudio = dce.id_estudio
			        GROUP BY
                        tit.ID_TITULO_CNT,
			            dce.desc_estudio,
			            cnt.numero_contrato,
			            tit.nombre_unico,
			            CASE 
					         WHEN tit.temporada is null and tit.episodio is not null THEN '00'
					         when tit.temporada is null and tit.episodio is null then 'PELICULA'
					         when tit.temporada is not null and tit.episodio is not null THEN tit.temporada
					     END ,
			            amr.mes
			    )
			         PIVOT ( SUM ( monto )
						            FOR meses
						            IN (  ${pivot_data}  )
						        )
			        order by 1,2,3,4
    ) amor on amor.ID_TITULO_CNT = dtctt.ID_TITULO_CNT
     ,DDA_C_CATEGORIA dtcct
  
WHERE
    dtc.id_estudio = dce.id_estudio
    AND   dtc.id_estatus IN (
        4,
        5,
        6
    )
    AND   dtc.id_apartado = dta.id_apartado
    AND   dtc.clave_iva = dcii.clave_iva
    AND   detalle.contrato = dtc.id_contrato
    AND dtc.id_contrato = dtctt.id_contrato
	AND   dtctt.ID_CATEGORIA_TIT = dtcct.ID_CATEGORIA
   <if test="estudio != 0">
        AND   dtc.id_estudio = #{estudio}
    </if>
    <if test="contrato != 0">
        AND dtc.id_contrato = #{contrato}
    </if>
    
    <if test="nombreUnico != 0 and temporada != 0">
			  	AND dtctt.ID_TITULO_CNT = #{nombreUnico} 
			  	and dtctt.temporada = #{temporada}
			</if>
			<if test="nombreUnico != 0  and temporada == 0" >
			  	AND dtctt.id_titulo_cnt = #{nombreUnico}
			</if>	
		</if>
		
		<if test="dgenerales == 'NO' and tcomerciales == 'YES' and titulos == 'NO' and amortizacion == 'NO'">
			Select 
			montoProgramado,
		    anticipo, 
		    tipoPagos,
		    costoC
		    from (
		SELECT
		    dtc.id_contrato,
			nvl(dtctep.MONTO,0) montoProgramado,
		    nvl(dtcte.ANTICIPO_MONTO,0) anticipo, 
		    dtcte.TIPO_PAGO tipoPagos,
		    SUM(NVL(dtctt.COSTO,0)) AS costoC
		FROM
		    dda_t_contrato dtc 
		    inner join DDA_T_CONTRATO_TERMINO dtcte on dtc.id_contrato = dtcte.id_contrato 
		    left outer join DDA_T_CONTRATO_TERMINO_PAGO dtctep on dtc.id_contrato = dtctep.id_contrato,
		    DDA_T_CONTRATO_TITULO dtctt
		  
		WHERE
		    dtc.id_estatus IN (
		        4,
		        5,
		        6
		    )
		    AND dtc.id_contrato = dtctt.id_contrato 
		    <if test="estudio != 0">
				AND   dtc.id_estudio = #{estudio}
			</if>
		    <if test="contrato != 0">
			  	AND dtc.id_contrato = #{contrato}
			</if>
		    
		   <if test="nombreUnico != 0 and temporada != 0">
			  	AND dtc.id_contrato in (select id_contrato from DDA_T_CONTRATO_TITULO where ID_TITULO_CNT = #{nombreUnico} and temporada = #{temporada})
			</if>
			<if test="nombreUnico != 0  and temporada == 0" >
			  	AND dtc.id_contrato in (select id_contrato from DDA_T_CONTRATO_TITULO where ID_TITULO_CNT = #{nombreUnico})
			</if>				
		group by
		    dtc.id_contrato,
		    dtctep.MONTO
		   	,dtcte.ANTICIPO_MONTO
		   	,dtcte.TIPO_PAGO
		    )
		
		
		</if>
		<if test="dgenerales == 'NO' and tcomerciales == 'NO' and titulos == 'YES' and amortizacion == 'NO'">
		SELECT
			 dtctt.ID_BV,
		     dtctt.DESC_TITULO,
		     dtctt.NOMBRE_UNICO, 
		     CASE 
		         WHEN dtctt.temporada is null and dtctt.episodio is not null THEN '00'
		         when dtctt.temporada is null and dtctt.episodio is null then 'PELICULA'
		         when dtctt.temporada is not null and dtctt.episodio is not null THEN dtctt.temporada
		     END as temporada,
		     Nvl(dtctt.EPISODIO,'--') episodio, 
		     dtctt.FECHA_INICIO, 
		     dtctt.FECHA_FIN,
		     dtctt.VIDA_UTIL,
		     dtcct.DESC_CATEGORIA,
		     dtctt.COSTO,
		     dtctt.PRECIO_ANUAL 
		 FROM
		    dda_t_contrato dtc 
		    ,DDA_T_CONTRATO_TITULO dtctt
		    ,DDA_C_CATEGORIA dtcct
		 Where 
		    dtc.id_estatus IN (
		        4,
		        5,
		        6
		    )
		    AND dtc.id_contrato = dtctt.id_contrato
		    AND   dtctt.ID_CATEGORIA_TIT = dtcct.ID_CATEGORIA 
		    <if test="estudio != 0">
				AND   dtc.id_estudio = #{estudio}
			</if>
		    <if test="contrato != 0">
			  	AND dtc.id_contrato = #{contrato}
			</if>
		    
		   <if test="nombreUnico != 0 and temporada != 0">
			  	AND dtc.id_contrato in (select id_contrato from DDA_T_CONTRATO_TITULO where ID_TITULO_CNT = #{nombreUnico} and temporada = #{temporada})
			</if>
			<if test="nombreUnico != 0  and temporada == 0" >
			  	AND dtc.id_contrato in (select id_contrato from DDA_T_CONTRATO_TITULO where ID_TITULO_CNT = #{nombreUnico})
			</if>		
		    
		</if>
		<if test="dgenerales == 'NO' and tcomerciales == 'NO' and titulos == 'NO' and amortizacion == 'YES'">
			SELECT
			    desc_estudio estudio,
			    numero_contrato nocontrarodda,
			    nombre_unico nombre_unico,
			    temporada episodio,
			    mes_1,
			    mes_2,
			    mes_3,
			    mes_4,
			    mes_5,
			    mes_6,
			    mes_7,
			    mes_8,
			    mes_9,
			    mes_10,
			    mes_11,
			    mes_12
			FROM
			    (
			        SELECT
			            dce.desc_estudio,
			            cnt.numero_contrato,
			            tit.nombre_unico,
			            CASE 
					         WHEN tit.temporada is null and tit.episodio is not null THEN '00'
					         when tit.temporada is null and tit.episodio is null then 'PELICULA'
					         when tit.temporada is not null and tit.episodio is not null THEN tit.temporada
					     END as temporada,
			            TO_CHAR(amr.mes,'MM/yyyy') AS meses,
			            SUM(amr.monto) AS monto
			        FROM
			            dda_t_contrato cnt
			            INNER JOIN dda_t_contrato_titulo tit ON cnt.id_contrato = tit.id_contrato
			            LEFT JOIN dda_t_amortizacion amr ON tit.id_titulo_cntorig = amr.id_titulo_cntorig
			            , dda_c_estudio dce
			          
			        WHERE
			            1 = 1
			            <if test="estudio != 0">
			                AND   cnt.id_estudio = #{estudio}
			            </if>
			            <if test="contrato != 0">
			                AND   cnt.id_contrato = #{contrato}
			            </if>
			            <if test="nombreUnico != 0 ">
			                AND tit.id_titulo_cnt =#{nombreUnico}
			            </if>
			            <if test="nombreUnico != 0  and temporada == 0" >
			                and tit.temporada =#{temporada}
			            </if>
			             AND   amr.mes &gt;= add_months(trunc(TO_DATE(#{fecha},'MM/yyyy'),'mm'),-12)
						            AND   amr.mes &lt;= TO_DATE(#{fecha},'MM/yyyy')
			            AND   cnt.id_estudio = dce.id_estudio
			        GROUP BY
			            dce.desc_estudio,
			            cnt.numero_contrato,
			            tit.nombre_unico,
			            CASE 
					         WHEN tit.temporada is null and tit.episodio is not null THEN '00'
					         when tit.temporada is null and tit.episodio is null then 'PELICULA'
					         when tit.temporada is not null and tit.episodio is not null THEN tit.temporada
					     END ,
			            amr.mes
			    )
			         PIVOT ( SUM ( monto )
						            FOR meses
						            IN ( ${pivot_data} )
						        )
			        order by 1,2,3,4
		</if>
		
		<if test="dgenerales == 'YES' and tcomerciales == 'NO' and titulos == 'YES' and amortizacion == 'NO'">
			SELECT
    dtc.id_contrato,
    dce.desc_estudio estudio,
    dtc.numero_contrato nocontrarodda,
    dtc.id_contrato_bv nocontratobv,
    dtc.fecha_inicio fechainicio,
    dtc.fecha_fin fechafin,
    dta.desc_apartado apartado,
    dcii.descripcion impuesto,
    detalle.categorias,
    detalle.servicios,
    detalle.territorios,
    detalle.atributos,
    detalle.filialesType,
    --titulos
     dtctt.ID_BV,
		     dtctt.DESC_TITULO,
		     dtctt.NOMBRE_UNICO, 
		     CASE 
		         WHEN dtctt.temporada is null and dtctt.episodio is not null THEN '00'
		         when dtctt.temporada is null and dtctt.episodio is null then 'PELICULA'
		         when dtctt.temporada is not null and dtctt.episodio is not null THEN dtctt.temporada
		     END as temporada,
		     Nvl(dtctt.EPISODIO,'--') episodio, 
		     dtctt.FECHA_INICIO, 
		     dtctt.FECHA_FIN,
		     dtctt.VIDA_UTIL,
		     dtcct.DESC_CATEGORIA,
		     dtctt.COSTO,
		     dtctt.PRECIO_ANUAL 
    
FROM
    dda_t_contrato dtc,
    dda_c_estudio dce,
    dda_t_apartado dta,
    dda_c_indicador_iva dcii,
    (
        SELECT
            cat.contrato,
            cat.categorias,
            ser.servicios,
            ter.territorios,
            atr.atributos,
            fil.filialesType
        FROM
            (
                SELECT
                    dce.id_contrato contrato,
                    LISTAGG(ca.desc_categoria,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dce.id_contrato
                    ) categorias
                FROM
                    dda_t_contrato dce,
                    dda_t_contrato_categoria re,
                    dda_c_categoria ca
                WHERE
                    re.id_contrato = dce.id_contrato
                    AND   re.id_categoria = ca.id_categoria
                  
                GROUP BY
                    dce.id_contrato
            ) cat
            LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dst.desc_servicio,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) servicios
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_servicio dtcs,
                    dda_c_servicio_tipo dst
                WHERE
                    dtc.id_contrato = dtcs.id_contrato
                    AND   dst.id_tipo_servicio = dtcs.id_tipo_servicio
                   
                GROUP BY
                    dtc.id_contrato
            ) ser ON cat.contrato = ser.contrato
            LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dct.desc_territorio,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) territorios
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_territorio dtct,
                    dda_c_territorio dct
                WHERE
                    dtc.id_contrato = dtct.id_contrato
                    AND   dct.id_territorio = dtct.id_territorio
                 
                GROUP BY
                    dtc.id_contrato
            ) ter ON ser.contrato = ter.contrato
            LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dct.desc_medio_entrega,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) atributos
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_medio_entrega dtcme,
                    dda_c_medio_entrega dct
                WHERE
                    dtc.id_contrato = dtcme.id_contrato
                    AND   dct.id_medio_entrega = dtcme.id_medio_entrega
                   
                GROUP BY
                    dtc.id_contrato
            ) atr ON ser.contrato = atr.contrato
             LEFT OUTER JOIN (
                SELECT
                    dtc.id_contrato contrato,
                    LISTAGG(dct.desc_filial,
                    ',') WITHIN GROUP(
                    ORDER BY
                        dtc.id_contrato
                    ) filialesType
                FROM
                    dda_t_contrato dtc,
                    dda_t_contrato_deal_filial dtct,
                    dda_c_filial dct
                WHERE
                    dtc.id_contrato = dtct.id_contrato
                    AND   dct.id_filial = dtct.id_filial
                GROUP BY
                    dtc.id_contrato
            ) fil ON fil.contrato = atr.contrato
    ) detalle
    ,DDA_T_CONTRATO_TITULO dtctt
    ,DDA_C_CATEGORIA dtcct
WHERE
    dtc.id_estudio = dce.id_estudio
    AND   dtc.id_estatus IN (
        4,
        5,
        6
    )
    AND   dtc.id_apartado = dta.id_apartado
    AND   dtc.clave_iva = dcii.clave_iva
    AND   detalle.contrato = dtc.id_contrato
    AND dtc.id_contrato = dtctt.id_contrato
	AND   dtctt.ID_CATEGORIA_TIT = dtcct.ID_CATEGORIA
       <if test="estudio != 0">
        AND   dtc.id_estudio = #{estudio}
    </if>
    <if test="contrato != 0">
        AND dtc.id_contrato = #{contrato}
    </if>
    
    <if test="nombreUnico != 0 and temporada != 0">
        AND dtctt.ID_TITULO_CNT = #{nombreUnico} 
        and dtctt.temporada = #{temporada}
    </if>
    <if test="nombreUnico != 0  and temporada == 0" >
        AND dtctt.ID_TITULO_CNT = #{nombreUnico}
    </if>	
    
    	</if>
		<if test="dgenerales == 'YES' and tcomerciales == 'NO' and titulos == 'NO' and amortizacion == 'YES'">
		
			SELECT
			   *
			FROM
			    (
			        SELECT
			           cnt.id_contrato,
                        dce.desc_estudio estudio,
                        cnt.numero_contrato nocontrarodda,
                        cnt.id_contrato_bv nocontratobv,
                        cnt.fecha_inicio fechainicio,
                        cnt.fecha_fin fechafin,
                        dta.desc_apartado apartado,
                        dcii.descripcion impuesto,
                        (SELECT desc_categoria FROM dda_c_categoria WHERE id_categoria = dtcc.id_categoria) categorias,
                        (SELECT desc_servicio FROM dda_c_servicio_tipo WHERE id_tipo_servicio = dtcs.id_tipo_servicio) servicios,
                        (SELECT desc_territorio FROM dda_c_territorio  WHERE id_territorio = dtct.id_territorio ) territorios,
                        (SELECT desc_medio_entrega FROM dda_c_medio_entrega WHERE id_medio_entrega = dtcme.id_medio_entrega) atributos,
                        
                        TO_CHAR(amr.mes,'MM/yyyy') AS meses,
                        SUM(amr.monto) AS monto
			        FROM
			            dda_t_contrato cnt
                        left outer JOIN dda_t_contrato_titulo tit ON cnt.id_contrato = tit.id_contrato
                        LEFT JOIN dda_t_amortizacion amr ON tit.id_titulo_cntorig = amr.id_titulo_cntorig,
                        dda_c_estudio dce,
                        dda_t_apartado dta,
                        dda_c_indicador_iva dcii,
                        dda_t_contrato_categoria dtcc,
                        dda_t_contrato_servicio dtcs,
                        dda_t_contrato_territorio dtct,
                        dda_t_contrato_medio_entrega dtcme
			          
			        WHERE
			            1 = 1
			            <if test="estudio != 0">
			                AND   cnt.id_estudio = #{estudio}
			            </if>
			            <if test="contrato != 0">
			                AND   cnt.id_contrato = #{contrato}
			            </if>
			            <if test="nombreUnico != 0 ">
			                AND tit.id_titulo_cnt =#{nombreUnico}
			            </if>
			            <if test="nombreUnico != 0  and temporada == 0" >
			                and tit.temporada =#{temporada}
			            </if>
			             AND   amr.mes &gt;= add_months(trunc(TO_DATE(#{fecha},'MM/yyyy'),'mm'),-12)
						            AND   amr.mes &lt;= TO_DATE(#{fecha},'MM/yyyy')
			            AND   cnt.id_estudio = dce.id_estudio
                        
                         AND   cnt.id_estudio = dce.id_estudio
                            AND   cnt.id_estatus IN (
                                4,
                                5,
                                6
                            )
                            AND   cnt.id_apartado = dta.id_apartado
                            AND   cnt.clave_iva = dcii.clave_iva
                            AND   cnt.id_contrato = dtcc.id_contrato
                            AND   cnt.id_contrato = dtcs.id_contrato
                            AND   cnt.id_contrato = dtct.id_contrato
                            AND   cnt.id_contrato = dtcme.id_contrato
                        
			        GROUP BY
			            cnt.id_contrato,
                        dce.desc_estudio,
                        cnt.numero_contrato,
                        cnt.id_contrato_bv,
                        cnt.fecha_inicio,
                        cnt.fecha_fin,
                        dta.desc_apartado,
                        dcii.descripcion,
                        dtcc.id_categoria,
                        dtcs.id_tipo_servicio,
                        dtct.id_territorio,
                        dtcme.id_medio_entrega,
                        amr.mes
			    )
			         PIVOT ( SUM ( monto )
						            FOR meses
						            IN ( ${pivot_data} )
						        )
		</if>
		<if test="dgenerales == 'NO' and tcomerciales == 'YES' and titulos == 'YES' and amortizacion == 'NO'">
		</if>
		<if test="dgenerales == 'NO' and tcomerciales == 'YES' and titulos == 'NO' and amortizacion == 'YES'">
		</if>
		<if test="dgenerales == 'NO' and tcomerciales == 'NO' and titulos == 'YES' and amortizacion == 'YES'">
			 SELECT
			   *
			FROM
			    (
			        SELECT
			           tit.ID_BV,
		     tit.DESC_TITULO,
		     tit.NOMBRE_UNICO, 
		     CASE 
		         WHEN tit.temporada is null and tit.episodio is not null THEN '00'
		         when tit.temporada is null and tit.episodio is null then 'PELICULA'
		         when tit.temporada is not null and tit.episodio is not null THEN tit.temporada
		     END as temporada,
		     Nvl(tit.EPISODIO,'--') episodio, 
		     tit.FECHA_INICIO, 
		     tit.FECHA_FIN,
		     tit.VIDA_UTIL,
		     dtcct.DESC_CATEGORIA,
		     tit.COSTO,
		     tit.PRECIO_ANUAL ,
            
            TO_CHAR(amr.mes,'MM/yyyy') AS meses,
            SUM(amr.monto) AS monto
			        FROM
			           dda_t_contrato cnt
            left JOIN dda_t_contrato_titulo tit ON cnt.id_contrato = tit.id_contrato
            left join DDA_C_CATEGORIA dtcct on tit.ID_CATEGORIA_TIT = dtcct.ID_CATEGORIA
            LEFT JOIN dda_t_amortizacion amr ON tit.id_titulo_cntorig = amr.id_titulo_cntorig
			          
			        WHERE
			            1 = 1
			            <if test="estudio != 0">
			                AND   cnt.id_estudio = #{estudio}
			            </if>
			            <if test="contrato != 0">
			                AND   cnt.id_contrato = #{contrato}
			            </if>
			            <if test="nombreUnico != 0 ">
			                AND tit.id_titulo_cnt =#{nombreUnico}
			            </if>
			            <if test="nombreUnico != 0  and temporada == 0" >
			                and tit.temporada =#{temporada}
			            </if>
			             AND   amr.mes &gt;= add_months(trunc(TO_DATE(#{fecha},'MM/yyyy'),'mm'),-12)
						 AND   amr.mes &lt;= TO_DATE(#{fecha},'MM/yyyy')
			             and cnt.id_estatus IN (
		        4,
		        5,
		        6
		    )
                         
                        
			        GROUP BY
			            tit.ID_BV,
		     tit.DESC_TITULO,
		     tit.NOMBRE_UNICO, 
		     CASE 
		         WHEN tit.temporada is null and tit.episodio is not null THEN '00'
		         when tit.temporada is null and tit.episodio is null then 'PELICULA'
		         when tit.temporada is not null and tit.episodio is not null THEN tit.temporada
		     END,
		     Nvl(tit.EPISODIO,'--') , 
		     tit.FECHA_INICIO, 
		     tit.FECHA_FIN,
		     tit.VIDA_UTIL,
		     dtcct.DESC_CATEGORIA,
		     tit.COSTO,
		     tit.PRECIO_ANUAL ,
            amr.mes
			    )
			         PIVOT ( SUM ( monto )
						            FOR meses
						            IN ( ${pivot_data} )
						        )
			        
		</if>
		
		
		
		
		
		
		

	</select>	 
	 
</mapper>