<?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.amortizacion.presupuesto.dao.ReporteAmortVsPptoDAO">
		 
	 <resultMap id="repAmortizacionPptoDTO" type="ReporteAmortizacionVsPresupuestoDTO">
	 
	 	<result property="estudio"		column="ESTUDIO" />
	 	<result property="mes_1"			column="MES_1" />
	 	<result property="mes_2"			column="MES_2" />
	 	<result property="mes_3"			column="MES_3" />
	 	<result property="mes_4"			column="MES_4" />
	 	<result property="mes_5"			column="MES_5" />
	 	<result property="mes_6"			column="MES_6" />
	 	<result property="mes_7"			column="MES_7" />
	 	<result property="mes_8"			column="MES_8" />
	 	<result property="mes_9"			column="MES_9" />
	 	<result property="mes_10"		column="MES_10" />
	 	<result property="mes_11"		column="MES_11" />
	 	<result property="mes_12"		column="MES_12" />
	 	<result property="nejercido"		column="EJERCIDO" />
	 	<result property="ppto_anual"	column="PPTO_ANUAL" />
	 	<result property="por_ejercer"	column="POR_EJERCER" />
	 	<result property="p_utilizado"	column="P_UTILIZADO" />	
	 </resultMap>
	   	  	  	  	 
	 
	 <select id="obtenerReporteAmortPpto" resultMap="repAmortizacionPptoDTO" parameterType="map">
	 	     SELECT
				   -- dtos.ORDEN,
				   -- ppto.OCUPADO as OCUPADO_PPTO,
				    dtos.ESTUDIO,
				    dtos.MES_1, 
				    dtos.MES_2, 
				    dtos.MES_3, 
				    dtos.MES_4, 
				    dtos.MES_5, 
				    dtos.MES_6, 
				    dtos.MES_7, 
				    dtos.MES_8, 
				    dtos.MES_9, 
				    dtos.MES_10, 
				    dtos.MES_11, 
				    dtos.MES_12,
				    ( dtos.MES_1 + dtos.MES_2 + dtos.MES_3 + dtos.MES_4 + 
				    dtos.MES_5 + dtos.MES_6 + dtos.MES_7 + dtos.MES_8 +
				    dtos.MES_9 + dtos.MES_10 + dtos.MES_11 + dtos.MES_12 ) as EJERCIDO,
				    ppto.PPTO_ANUAL,
				    ppto.EJERCER as POR_EJERCER,
				    ROUND( ( ppto.OCUPADO / ppto.PPTO_ANUAL ) * 100, 2 ) as P_UTILIZADO
				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_AMORTIZACION amor
			                            where                                 
			                                    TO_DATE( TO_CHAR( amor.MES, 'mm-yyyy' ), 'mm-yyyy' ) &lt;= TO_DATE('12' || '-' || #{annio}, 'mm-yyyy' )
			                                and TO_NUMBER( TO_CHAR( amor.MES, 'yyyy' ) ) = #{annio}
			                        )
			                        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               
			           group by
			                grouping sets
			                    ( 
			                        ( ),
			                        est.DESC_ESTUDIO
			                    )           
			        UNION ALL
			
			        --------------------------------------
			        -------- AMORTIZACION SAP   ----------
			        --------------------------------------
			            select
			                2 as ORDEN,
			                'SAP' 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_ESTUDIO, 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_ESTUDIO, 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        
			                                cnt.ID_ESTUDIO,
			                                MIN( cnt.ID_CONTRATO_ORIGINAL ) as 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 ) * -1 as MONTO
			                            from
			                                DDA_T_AMORTIZACION_SAP amor,
			                                DDA_T_CONTRATO_TITULO tit,
			                                DDA_T_CONTRATO cnt
			                            where                                 
			                                    TO_DATE( TO_CHAR( amor.MES, 'mm-yyyy' ), 'mm-yyyy' ) &lt;= TO_DATE('12' || '-' || #{annio}, 'mm-yyyy' )
			                                and TO_NUMBER( TO_CHAR( amor.MES, 'yyyy' ) ) = #{annio}
			                                and amor.ID_TITULO_CNTORIG = tit.ID_TITULO_CNTORIG
			                                and tit.ID_CONTRATO = cnt.ID_CONTRATO
			                            group by
			                                cnt.ID_ESTUDIO,
			                                amor.ID_TITULO_CNTORIG,                
			                                amor.MES,
			                                amor.MONTO
			                        )
			                        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_ESTUDIO, ID_CONTRATO_ORIGINAL, ANNIO
			                ) sub,                            
			                DDA_C_ESTUDIO est                
			            where
			                est.ID_ESTUDIO = sub.ID_ESTUDIO   
			) dtos,
			(
			    select
			        NVL( ESTUDIO, 'TOTAL' ) as ESTUDIO,
			        ROUND( SUM( PPTO_APEST ), 2 ) as PPTO_ANUAL,
			        ROUND( SUM( OCUPADO_APEST ), 2 ) as OCUPADO,
			        ROUND( SUM( EJER_APEST ), 2 ) as EJERCER
			    from
			    (
			        select
			            pa.ID_APARTADO,
			            pest.NO_ESTUDIOS as TOTAL_ESTUDIOS,            
			            pa.MONTO as MONTO,    
			            poc.OCUPADO as OCUPADO,
			            
			            est.ESTUDIO,
			            pa.MONTO / pest.NO_ESTUDIOS as PPTO_APEST,    
			            ( pa.MONTO - poc.OCUPADO ) / pest.NO_ESTUDIOS as EJER_APEST,
			            ( poc.OCUPADO ) / pest.NO_ESTUDIOS as OCUPADO_APEST
			        from
			            DDA_T_PRESUPUESTO_APARTADO pa,
			            (
			                select
			                    ct.ID_APARTADO, 
			                    SUM( amr.MONTO ) as OCUPADO
			                from
			                    DDA_T_CONTRATO ct,
			                    DDA_T_AMORTIZACION amr
			                where
			                        ct.ID_CONTRATO = amr.ID_CONTRATO_ORIGINAL(+)
			                    and TO_DATE( TO_CHAR( MES, 'mm-yyyy' ), 'mm-yyyy' ) &lt;= TO_DATE('12' || '-' || #{annio}, 'mm-yyyy' )
			                    and TO_NUMBER( TO_CHAR( MES, 'yyyy' ) ) = #{annio}
			                    and ct.ID_ESTATUS in ( 5, 6 )
			                group by ct.ID_APARTADO
			            ) poc,
			            (
			                select distinct 
			                    ct.ID_APARTADO, 
			                    COUNT( distinct ct.ID_ESTUDIO ) as NO_ESTUDIOS
			                from
			                    DDA_T_CONTRATO ct
			                where
			                    ct.ID_ESTATUS = 5
			                and ct.ID_TIPO_CONTRATO = 1
			                group by ct.ID_APARTADO
			            ) pest,
			            (
			                select distinct 
			                    ct.ID_APARTADO, 
			                    ct.ID_ESTUDIO,
			                    estu.DESC_ESTUDIO as ESTUDIO
			                from
			                    DDA_T_CONTRATO ct,
			                    DDA_C_ESTUDIO estu
			                where
			                    ct.ID_ESTUDIO = estu.ID_ESTUDIO
			                and ct.ID_ESTATUS in ( 5, 6 )
			                and ct.ID_TIPO_CONTRATO = 1
			            )est
			        where
			                pest.ID_APARTADO = pa.ID_APARTADO
			            and pa.ID_APARTADO = poc.ID_APARTADO(+)
			            and pa.ID_APARTADO = est.ID_APARTADO
			            and ANNIO  = #{annio} 
			    )
			    group by 
			        grouping sets
			        ( 
			            ( ),
			            ESTUDIO
			        ) 
			) ppto
			where
			    dtos.ESTUDIO = ppto.ESTUDIO(+)
			order by ORDEN, ESTUDIO
	 </select>	  
	 
</mapper>