<?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.oyp.daos.ReporteOveragesYPaymentsDAO">
	   	  	  	  	  
  <select id="obtenerOveragesYPayments" resultType="java.util.LinkedHashMap" parameterType="map">
    SELECT
        NVL(ORDEN, -1) AS ORDEN,
        NVL(TYPE_PAGO, ' ') AS TYPE_PAGO,
        NVL(NUMERO_CONTRATO, -1) AS NUMERO_CONTRATO,
        NVL(ASSET_NAME, ' ') AS ASSET_NAME,
        NVL(REPORTE_MG, 0) AS MG,
        NVL(REPORTE_CLASS, ' ') AS CLASS,
        ${sumatorias_mensuales},
        NVL(MAX( REPORTE_SPLIT ), 0) AS SPLIT,
        CASE
          WHEN NVL(REPORTE_MG, 0) > NVL(MAX( REPORTE_SPLIT ), 0)
          THEN NVL(REPORTE_MG, 0)
          ELSE NVL(MAX( REPORTE_SPLIT ), 0)
        END AS MAX,
        CASE
          WHEN NVL(REPORTE_MG, 0) > NVL(MAX( REPORTE_SPLIT ), 0)
          THEN 0
          ELSE NVL(MAX( REPORTE_SPLIT ), 0) - NVL(REPORTE_MG, 0)
        END AS OVERAGE
    FROM
    (
      SELECT
          ORDEN,
          NUMERO_CONTRATO,
          TYPE_PAGO,
          ASSET_NAME,
          REPORTE_MG,
          REPORTE_CLASS, 
          ${meses_seleccionados},
          REPORTE_SPLIT
      FROM
        (
        SELECT
            pgo.ID_CNT_PAGO,
            pgo.TYPE_PAGO,
            cnt.ID_ESTUDIO,
            cnt.NUMERO_CONTRATO,
            CASE
               WHEN pgo.TIPO_PAGO = 'OVERAGE' and pgo.NOMBRE_ESTANDAR is not null THEN
                    0
               WHEN pgo.TIPO_PAGO = 'OVERAGE' THEN
                    1
               ELSE
                    2
            END as ORDEN,
            NVL( pgo.NOMBRE_ESTANDAR, '['||cnt.NUMERO_CONTRATO||'] Pago global '||pgo.TYPE_PAGO ) as ASSET_NAME,
            TO_NUMBER( pgo.REPORTE_MG ) as REPORTE_MG,
            pgo.REPORTE_CLASS,
            TO_NUMBER( TO_CHAR( pgo.FECHA_PAGO, 'mmyyyy' ) ) as MES_PAGO,
            pgo.TIPO_PAGO,
            pgo.REPORTE_MES as PAGO_MES,
            pgo.ID_CARGA_TRANS,
            TO_NUMBER( pgo.REPORTE_SPLIT ) as REPORTE_SPLIT
        FROM
            DDA_T_CNT_PAGO pgo,
            DDA_T_CONTRATO cnt,
            DDA_T_CARGA_TRANSACCION cga
        WHERE
            pgo.ID_CONTRATO_ORIGINAL = cnt.ID_CONTRATO_ORIGINAL
            AND cnt.ID_ESTUDIO = #{id_estudio}
            AND pgo.TYPE_PAGO = #{tipo_pago}
            AND cnt.ID_ESTATUS in ( 5, 6, 8, 9 )
            AND ( pgo.TIPO_PAGO = 'OVERAGE' OR pgo.TIPO_PAGO = 'OVERAGE_TI')
            AND pgo.ID_CARGA_TRANS = cga.ID_CARGA_TRANS
            <if test="validaEstatusCarga == 'SI'">
                AND cga.ESTATUS_CARGA = 'OK'
			</if>
            AND TO_DATE( TO_CHAR( pgo.FECHA_PAGO , 'mm-yyyy' ), 'mm-yyyy' ) >= TO_DATE(#{mesInicial} || '-' || #{anioInicial}, 'mm-yyyy' )
            AND TO_DATE( TO_CHAR( pgo.FECHA_PAGO , 'mm-yyyy' ), 'mm-yyyy' ) &lt;= TO_DATE(#{mesFinal} || '-' || #{anioFinal}, 'mm-yyyy' )
        )
      PIVOT( SUM( PAGO_MES ) FOR 
         MES_PAGO in ( ${meses_pivote}  
                     )
      ) 
    )
    GROUP BY 
       TYPE_PAGO,
       ORDEN,
       NUMERO_CONTRATO,
       ASSET_NAME,
       REPORTE_MG,
       REPORTE_CLASS
    ORDER BY NUMERO_CONTRATO, TYPE_PAGO, ORDEN
  </select>
	 
</mapper>