<?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.rys.daos.ReporteRevenueYSplitsDAO">
	   	  	  	  	  
  <select id="obtenerRevenueYSplits" resultType="java.util.LinkedHashMap" parameterType="map">
        SELECT
            NVL(DECODE( NVL( tit.NOMBRE_ESTANDAR, '-' ), '-', '', reg.DESCRIPCION ), ' ') as REGION,
            CASE
                WHEN reg.DESCRIPCION is null and tit.NOMBRE_ESTANDAR is null THEN
                    'Grand Total'
                WHEN tit.NOMBRE_ESTANDAR is null THEN
                    NVL(reg.DESCRIPCION, ' ')||' Sum'
                ELSE
                    NVL(tit.NOMBRE_ESTANDAR, ' ')
            END  as ASSET_NAME,
            NVL(fil.DESC_FILIAL, ' ') as COUNTRY,
            NVL(tit.ACP_RSESTUDIO, ' ') as REVENUE,
            NVL(TO_CHAR(ROUND( tit.ACP_PPEPAGAR, 2) ),  ' ') as VAT,
            NVL(TO_CHAR(SUM( tit.CANTIDAD )),  ' ') as MES,
           NVL( TO_CHAR(ROUND( SUM( tit.ACP_RS_MENSUAL ), 2 )),  ' ') as TOTAL
        FROM
            DDA_T_CONTRATO cnt,
            DDA_T_CARGA_TRANSACCION cga,
            DDA_T_CARGA_TRANSACCION_TITULO tit,
            DDA_C_FILIAL fil,
            DDA_C_EMPRESA_VENTA emp,
            DDA_C_PAIS_VENTA pvta,
            DDA_C_REGION_VENTA reg
        WHERE
                tit.ID_CONTRATO = cnt.ID_CONTRATO_ORIGINAL            
            AND cga.ID_CARGA_TRANS = tit.ID_CARGA_TRANS
            AND tit.ID_FILIAL = fil.ID_FILIAL
            AND fil.ID_EMPRESA = emp.ID_EMPRESA
            AND emp.ID_PAIS = pvta.ID_PAIS
            AND pvta.ID_REGION = reg.ID_REGION
            AND cnt.ID_ESTUDIO = #{id_estudio}
            AND tit.TYPE_ABONO = #{tipo_abono}
            AND cnt.ID_ESTATUS IN ( 5, 6, 8, 9 ) 
            <if test="validaEstatusCarga == 'SI'">
                AND cga.ESTATUS_CARGA = 'OK'
			</if>               
            AND TO_DATE( TO_CHAR( cga.MES , 'mm-yyyy' ), 'mm-yyyy' ) = TO_DATE( #{mes} || '-' || #{anio}, 'mm-yyyy' )

        GROUP BY
            GROUPING SETS
            (
                (),
                (reg.DESCRIPCION),
                (reg.DESCRIPCION, tit.NOMBRE_ESTANDAR, fil.DESC_FILIAL, tit.ACP_RSESTUDIO, tit.ACP_PPEPAGAR)
            )
        ORDER BY
            reg.DESCRIPCION NULLS LAST, 
            tit.NOMBRE_ESTANDAR NULLS LAST
  </select>
	 
</mapper>