
/****** Object:  StoredProcedure [dbo].[Calc_Ing_Rooming]    Script Date: 08/17/2014 13:37:22 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO




DROP PROCEDURE dbo.DSS_Calc_Ing_Rooming
GO

CREATE PROCEDURE dbo.DSS_Calc_Ing_Rooming
	   @dia_inic   DATETIME, 
	   @dia_fin    DATETIME, 
	   @mone       INT, 
	   @tasa_ext   NUMERIC(13, 8), 
	   @id_oper    INT, 
	   @ing_Ro_Al  NUMERIC(15, 2)OUTPUT, 
	   @ing_Ro_Pen NUMERIC(15, 2)OUTPUT, 
	   @ing_OAgen  NUMERIC(15, 2)OUTPUT
AS
     
/*  Proced que calcula los Ingresos de Agencias  por Rooming List por concepto de alojamiento, pension  y otros */
/* COL.RAY Se le modifico el historico para que no tome las "NO SHOW" dentro el calculo de ingreso (Estado 3 y 5)  */

BEGIN

	DECLARE @id_rool         INT, 
			@id_uhab         INT, 
			@id_ralo         TINYINT, 
			@id_cfag         INT, 
			@tipo_cfag       TINYINT, 
			@precio          NUMERIC(15, 2), 
			@id_cont         INT, 
			@factor_div      TINYINT, 
			@id_cfag_ralo    INT, 
			@factor_div_ralo TINYINT, 
			@precio_ralo     NUMERIC(15, 2), 
			@tasa_mb         NUMERIC(13, 8), 
			@tasa            NUMERIC(13, 8), 
			@valor           NUMERIC(15, 2), 
			@tipo            CHAR(1), 
			@uhab            INT, 
			@ralo            INT, 
			@aloj_precio     NUMERIC(15, 2), 
			@aloj_porc       NUMERIC(4, 2), 
			@pen_precio      NUMERIC(15, 2), 
			@Impuestos       NUMERIC(15, 2), 
			@pen_porc        NUMERIC(4, 2), 
			@otr_precio      NUMERIC(15, 2), 
			@otr_porc        NUMERIC(4, 2), 
			@cant_pax        INT, 
			@incluido        INT, 
			@aloj            NUMERIC(15, 2), 
			@pen             NUMERIC(15, 2), 
			@otr             NUMERIC(15, 2), 
			@fecha_hotel     DATETIME, 
			@ing_OAgenII     NUMERIC(15, 2)

	DECLARE @Tab_IngOAgenI TABLE
	(
		id_room   INT, 
		id_cfag   INT, 
		precio    NUMERIC(15, 2), 
		cantidad  INT, 
		id_mone   INT, 
		tasa_fija NUMERIC(13, 4), 
		tasa      NUMERIC(13, 4), 
		impuesto  NUMERIC(8, 2), 
		incluido  INT
	)

	DECLARE @Tab_IngOAgenII TABLE
	(
		id_room   INT, 
		id_cfag   INT, 
		precio    NUMERIC(15, 2), 
		cantidad  INT, 
		id_mone   INT, 
		tasa_fija NUMERIC(13, 4), 
		tasa      NUMERIC(13, 4), 
		impuesto  NUMERIC(8, 2), 
		incluido  INT
	)

	DECLARE @Tab_IngHAgenI TABLE
	(
		id_rool  INT, 
		id_uhab  INT, 
		id_ralo  INT, 
		precio   NUMERIC(15, 2), 
		tasa     NUMERIC(13, 4), 
		impuesto NUMERIC(5, 2), 
		incluido INT, 
		id_cont  INT
	)

	DECLARE @Tab_IngHAgenII TABLE
	(
		id_rool  INT, 
		id_uhab  INT, 
		id_ralo  INT, 
		precio   NUMERIC(15, 2), 
		tasa     NUMERIC(13, 4), 
		impuesto NUMERIC(5, 2), 
		incluido INT, 
		id_cont  INT
	)

	SELECT @ing_Ro_Al  = 0, 
		   @ing_Ro_Pen = 0, 
		   @ing_OAgen  = 0

	SELECT @tasa_mb     = tasa, 
		   @fecha_hotel = fecha_hotelera
	FROM       Hotetabl tabl
	INNER JOIN Siclamon sicla ON tabl.id_mone = sicla.id_mone


	/***********************************************  INGRESOS DE PENSION POR CARGOS AUTOMATICOS *************************************************************/
	
	/*

	SELECT @ing_Ro_Pen = SUM(rlau.precio * rlau.cantidad * CASE WHEN cont.id_mone != @mone THEN @tasa_ext / COALESCE(cont.tasa_fija, sicla.tasa)
																ELSE 1
														        END)
	FROM       Hoteroom room
	INNER JOIN Hoterool rool  ON room.id_room = rool.id_room
	INNER JOIN Hotecont cont  ON room.id_cont = cont.id_cont
	INNER JOIN HOTERLAG rlau  ON rool.id_rool = rlau.id_rool
	INNER JOIN Siclamon sicla ON cont.id_mone = sicla.id_mone
	WHERE (@id_oper = 0 OR room.id_oper = @id_oper)
      AND (DATEDIFF(day, rlau.fecha_desde, @dia_inic) >= 0
      AND  DATEDIFF(day, @dia_inic, rlau.fecha_hasta) > 0
       OR  DATEDIFF(day, rlau.fecha_desde, @dia_fin) >= 0
      AND  DATEDIFF(day, @dia_fin, rlau.fecha_hasta) > 0)
      AND (rool.id_estado IN(3, 5) 
			OR (rool.id_estado = 2 
					AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
				)
			) 
      AND rlau.id_cfag in (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_ralo, 0) = rool.id_ralo)
				--(select rali.id_con0 from hoterali rali where rool.id_ralo = rali.id_ralo)

	SET @ing_Ro_Pen = COALESCE(@ing_Ro_Pen, 0) 
		+ COALESCE((SELECT SUM(rlau.precio * rlau.cantidad * CASE WHEN cont.id_mone != @mone THEN @tasa_ext / COALESCE(cont.tasa_fija, sicla.tasa)
																  ELSE 1
																  END)
	FROM       Hotehrom  room
	INNER JOIN Hotehrol  rool  ON room.id_room = rool.id_room
	INNER JOIN Hotecont  cont  ON room.id_cont = cont.id_cont
	INNER JOIN HOTEHRLAG rlau  ON rool.id_rool = rlau.id_rool
	INNER JOIN Siclamon  sicla ON cont.id_mone = sicla.id_mone
	WHERE (@id_oper = 0 OR room.id_oper = @id_oper)
	  AND (DATEDIFF(day, rlau.fecha_desde, @dia_inic) >= 0
	  AND  DATEDIFF(day, @dia_inic, rlau.fecha_hasta) > 0
	   OR  DATEDIFF(day, rlau.fecha_desde, @dia_fin) >= 0
	  AND  DATEDIFF(day, @dia_fin, rlau.fecha_hasta) > 0)
	  AND --(rool.id_estado NOT IN(3, 5) 
			--OR 
			(rool.id_estado = 2 
					AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
				)
			--) 
	  AND rlau.id_cfag in (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_ralo, 0) = rool.id_ralo)
				--(select rali.id_con0 from hoterali rali where rool.id_ralo = rali.id_ralo)
				), 0)
				
	*/
	
	SET @ing_Ro_Pen = 0
     

	/************************************************* INGRESOS DE HABITACION POR CARGOS AUTOMATICOS ***********************************************************/

	SELECT @ing_Ro_Al = SUM(rlau.precio * rlau.cantidad * CASE WHEN cont.id_mone != @mone THEN @tasa_ext / COALESCE(cont.tasa_fija, sicla.tasa)
															   ELSE 1
														       END)
	FROM       Hoteroom room
	INNER JOIN Hoterool rool ON room.id_room = rool.id_room
	INNER JOIN Hotecont cont ON room.id_cont = cont.id_cont
	INNER JOIN HOTERLAG rlau ON rool.id_rool = rlau.id_rool
	INNER JOIN Siclamon sicla ON cont.id_mone = sicla.id_mone
	WHERE (@id_oper = 0 OR room.id_oper = @id_oper)
	  AND (DATEDIFF(day, rlau.fecha_desde, @dia_inic) >= 0
	  AND  DATEDIFF(day, @dia_inic, rlau.fecha_hasta) > 0
	   OR  DATEDIFF(day, rlau.fecha_desde, @dia_fin) >= 0
	  AND  DATEDIFF(day, @dia_fin, rlau.fecha_hasta) > 0)
	  AND (rool.id_estado IN(3, 5) 
			OR (rool.id_estado = 2 
					AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
				)
			) 
	  AND rlau.id_cfag IN (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_uhab, 0) <> 0)

	SELECT @ing_Ro_Al = COALESCE(@ing_Ro_Al, 0) + COALESCE((
		SELECT SUM(rlau.precio * rlau.cantidad * CASE WHEN cont.id_mone != @mone THEN @tasa_ext / COALESCE(cont.tasa_fija, sicla.tasa)
													  ELSE 1
												      END)
		FROM       Hotehrom room
		INNER JOIN Hotehrol rool ON room.id_room = rool.id_room
		INNER JOIN Hotecont cont ON room.id_cont = cont.id_cont
		INNER JOIN HOTEHRLAG rlau ON rool.id_rool = rlau.id_rool
		INNER JOIN Siclamon sicla ON cont.id_mone = sicla.id_mone
		WHERE (@id_oper = 0 OR room.id_oper = @id_oper)
		  AND (DATEDIFF(day, rlau.fecha_desde, @dia_inic) >= 0
		  AND  DATEDIFF(day, @dia_inic, rlau.fecha_hasta) > 0
		   OR  DATEDIFF(day, rlau.fecha_desde, @dia_fin) >= 0
		  AND  DATEDIFF(day, @dia_fin, rlau.fecha_hasta) > 0)
		  AND --(rool.id_estado NOT IN(3, 5) 
			--OR 
			(rool.id_estado = 2 
					AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
				)
			--) 
		  AND rlau.id_cfag IN (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_uhab, 0) <> 0)), 0)
     

	/***************************************************  OTROS INGRESOS POR CARGOS AUTOMATICOS AGENCIA ***********************************************************/
    
	INSERT INTO @Tab_IngOAgenI
	SELECT room.id_room, 
		   rlag.id_cfag, 
		   rlag.precio, 
		   rlag.cantidad, 
		   cont.id_mone, 
		   cont.tasa_fija, 
		   sicla.tasa, 
		   Impuesto = COALESCE(aimp.porc_impu, 0), 
		   incluido
	FROM       Hoteroom room
	INNER JOIN Hoterool rool  ON room.id_room = rool.id_room
	INNER JOIN Hotecont cont  ON room.id_cont = cont.id_cont
	INNER JOIN HOTERLAG rlag  ON rool.id_rool = rlag.id_rool
	INNER JOIN HOTEIMAG imag  ON rlag.id_cfag = imag.id_cfag
	INNER JOIN HOTEIMCO imco  ON cont.id_cont = imco.id_cont AND imag.id_impu = imco.id_impu
	INNER JOIN SICLAIMP aimp  ON imag.id_impu = aimp.id_impu
	INNER JOIN Siclamon sicla ON cont.id_mone = sicla.id_mone
	WHERE (@id_oper = 0 OR room.id_oper = @id_oper)
	  AND (DATEDIFF(day, rlag.fecha_desde, @dia_inic) >= 0
	  AND  DATEDIFF(day, @dia_inic, rlag.fecha_hasta) > 0
	   OR  DATEDIFF(day, rlag.fecha_desde, @dia_fin) >= 0
	  AND  DATEDIFF(day, @dia_fin, rlag.fecha_hasta) > 0)
	  AND (rool.id_estado IN(3, 5) 
			OR (rool.id_estado = 2 
					AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
				)
			) 
	  --AND rlag.id_cfag NOT IN (SELECT thab.id_con0 FROM hotethab thab)
	  --AND rlag.id_cfag NOT IN (SELECT rali.id_con0 FROM hoterali rali)
	  AND rlag.id_cfag NOT IN (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_uhab, 0) <> 0)
	  AND rlag.id_cfag NOT IN (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_ralo, 0) <> 0)
	  AND rlag.id_cfag NOT IN (SELECT cfag.id_cfag FROM HOTECFAG cfag WHERE cfag.id_ralo >= 1)
	
	--UNION
	INSERT INTO @Tab_IngOAgenI
	SELECT room.id_room, 
		   rlag.id_cfag, 
		   rlag.precio, 
		   rlag.cantidad, 
		   cont.id_mone, 
		   cont.tasa_fija, 
		   sicla.tasa, 
		   Impuesto = COALESCE(aimp.porc_impu, 0), 
		   incluido
	FROM       Hotehrom  room
	INNER JOIN Hotehrol  rool  ON room.id_room = rool.id_room
	INNER JOIN Hotecont  cont  ON room.id_cont = cont.id_cont
	INNER JOIN HOTEHRLAG rlag  ON rool.id_rool = rlag.id_rool
	INNER JOIN HOTEIMAG  imag  ON rlag.id_cfag = imag.id_cfag
	INNER JOIN HOTEIMCO  imco  ON cont.id_cont = imco.id_cont AND imag.id_impu = imco.id_impu
	INNER JOIN SICLAIMP  aimp  ON imag.id_impu = aimp.id_impu
	INNER JOIN Siclamon  sicla ON cont.id_mone = sicla.id_mone
	WHERE (@id_oper = 0 OR room.id_oper = @id_oper)
	  AND (DATEDIFF(day, rlag.fecha_desde, @dia_inic) >= 0
	  AND  DATEDIFF(day, @dia_inic, rlag.fecha_hasta) > 0
	   OR  DATEDIFF(day, rlag.fecha_desde, @dia_fin) >= 0
	  AND  DATEDIFF(day, @dia_fin, rlag.fecha_hasta) > 0)
	  AND --(rool.id_estado NOT IN(3, 5) 
			--OR 
			(rool.id_estado = 2 
					AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
				)
			--) 
	  --AND rlag.id_cfag NOT IN (SELECT thab.id_con0 FROM hotethab thab)
	  --AND rlag.id_cfag NOT IN (SELECT rali.id_con0 FROM hoterali rali)
	  AND rlag.id_cfag NOT IN (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_uhab, 0) <> 0)
	  AND rlag.id_cfag NOT IN (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_ralo, 0) <> 0)
	  AND rlag.id_cfag NOT IN (SELECT cfag.id_cfag FROM HOTECFAG cfag WHERE cfag.id_ralo >= 1)

	INSERT INTO @Tab_IngOAgenII
	SELECT *
	FROM   @Tab_IngOAgenI
	WHERE  incluido = 1

	UPDATE @Tab_IngOAgenI
	SET    incluido = 1
	FROM       @Tab_IngOAgenI  T1
	INNER JOIN @Tab_IngOAgenII T2 ON T1.id_room = T2.id_room
	WHERE  t1.incluido = 0

	UPDATE @Tab_IngOAgenI
	SET    impuesto = 0
	WHERE  incluido = 0

	SELECT @ing_OAgenII = SUM(precio / (Impuesto / 100 + 1) * cantidad * CASE WHEN id_mone != 3 THEN 1 / COALESCE(tasa_fija, tasa)
																			  ELSE 1
																		      END)
	FROM(
		SELECT id_room, 
			   precio, 
			   cantidad, 
			   id_mone, 
			   tasa_fija, 
			   tasa, 
			   incluido, 
			   SUM(COALESCE(Impuesto, 0)) AS "Impuesto"
		FROM @Tab_IngOAgenI
		GROUP BY id_room, 
				 precio, 
				 cantidad, 
				 id_mone, 
				 tasa_fija, 
				 tasa, 
				 incluido) AS Total


	SELECT @ing_OAgenII = COALESCE(@ing_OAgenII, 0)
     
	/***************************************************  OTROS INGRESOS POR CARGOS AUTOMATICOS DIRECTOS *******************************************************/

	SELECT @ing_OAgen = SUM(precio / (Impuesto / 100 + 1) * cantidad * CASE WHEN id_mone != 3 THEN 1 / COALESCE(tasa_fija, tasa)
																			ELSE 1
																	        END)
	FROM(
		SELECT rlau.precio, 
			   rlau.cantidad, 
			   t.id_mone, 
			   T.tasa, 
			   cont.tasa_fija, 
			   SUM(COALESCE(D.porc_impu, 0))AS "Impuesto"
		FROM       Hoteroom room
		INNER JOIN Hoterool rool ON room.id_room = rool.id_room
		INNER JOIN Hotecont cont ON room.id_cont = cont.id_cont
		INNER JOIN HOTERLAU rlau ON rool.id_rool = rlau.id_rool
		INNER JOIN HOTEPRE0 B    ON B.id_con0    = rlau.id_con0
		INNER JOIN SICLAMON T    ON t.id_mone    = B.id_mone AND t.tasa = 1
		LEFT  JOIN HOTEIMPU C    ON C.id_pre0    = B.id_pre0 OR  COALESCE(C.id_pre0, 0) = 0
		LEFT  JOIN SICLAIMP D    ON D.id_impu    = C.id_impu
		WHERE (@id_oper = 0 OR room.id_oper = @id_oper)
		  AND (DATEDIFF(day, rlau.fecha_desde, @dia_inic) >= 0
		  AND  DATEDIFF(day, @dia_inic, rlau.fecha_hasta) > 0
		   OR  DATEDIFF(day, rlau.fecha_desde, @dia_fin) >= 0
		  AND  DATEDIFF(day, @dia_fin, rlau.fecha_hasta) > 0)
		  AND (rool.id_estado IN(3, 5) 
			OR (rool.id_estado = 2 
					AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
				)
			) 
		  AND rlau.id_con0 NOT IN (SELECT thab.id_con0 FROM hotethab thab)
		  AND rlau.id_con0 NOT IN (SELECT rali.id_con0 FROM hoterali rali)
		GROUP BY room.id_room, 
				 rlau.precio, 
				 rlau.cantidad, 
				 t.id_mone, 
				 T.tasa, 
				 cont.tasa_fija) AS total

	SELECT @ing_OAgen = COALESCE(@ing_OAgen, 0) + COALESCE((
		SELECT SUM(precio / (Impuesto / 100 + 1) * cantidad * CASE WHEN id_mone != 3 THEN 1 / COALESCE(tasa_fija, tasa)
																   ELSE 1
															       END)
		FROM(
			SELECT rlau.precio, 
				   rlau.cantidad, 
				   t.id_mone, 
				   T.tasa, 
				   cont.tasa_fija, 
				   SUM(COALESCE(D.porc_impu, 0))AS "Impuesto"
			FROM       Hotehrom  room
			INNER JOIN Hotehrol  rool ON room.id_room = rool.id_room
			INNER JOIN Hotecont  cont ON room.id_cont = cont.id_cont
			INNER JOIN HOTEHRLAU rlau ON rool.id_rool = rlau.id_rool
			INNER JOIN HOTEPRE0  B    ON B.id_con0    = rlau.id_con0
			INNER JOIN SICLAMON  T    ON t.id_mone    = B.id_mone AND t.tasa = 1
			LEFT  JOIN HOTEIMPU  C    ON C.id_pre0    = B.id_pre0 OR  COALESCE(C.id_pre0, 0) = 0
			LEFT  JOIN SICLAIMP  D    ON D.id_impu    = C.id_impu
			WHERE (@id_oper = 0 OR room.id_oper = @id_oper)
			  AND (DATEDIFF(day, rlau.fecha_desde, @dia_inic) >= 0
			  AND  DATEDIFF(day, @dia_inic, rlau.fecha_hasta) > 0
			   OR  DATEDIFF(day, rlau.fecha_desde, @dia_fin) >= 0
			  AND  DATEDIFF(day, @dia_fin, rlau.fecha_hasta) > 0)
			  AND --(rool.id_estado NOT IN(3, 5) 
				--OR 
				(rool.id_estado = 2 
						AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
					)
				--) 
			  AND rlau.id_con0 NOT IN (SELECT thab.id_con0 FROM hotethab thab)
			  AND rlau.id_con0 NOT IN (SELECT rali.id_con0 FROM hoterali rali)
			GROUP BY room.id_room, 
					 rlau.precio, 
					 rlau.cantidad, 
					 t.id_mone, 
					 T.tasa, 
					 cont.tasa_fija) AS total), 0)

	SELECT @ing_OAgen = COALESCE(@ing_OAgen, 0) + COALESCE(@ing_OAgenII, 0)
  
	/******************************************************** TABLAS PARA UNIFICAR "INCLUIDO" **********************************************************************/

	INSERT INTO @Tab_IngHAgenI
	SELECT id_rool, 
		   rool.id_uhab, 
		   rool.id_ralo, 
		   precio = CONVERT(NUMERIC(15, 2), precio), 
		   tasa = CASE WHEN cont.id_mone != @mone THEN @tasa_ext / COALESCE(cont.tasa_fija, sicla.tasa)
					   ELSE 1
				       END, 
		   porc_impu = COALESCE(porc_impu, 0), 
		   --incluido = COALESCE(incluido, 2), 
		   1 incluido, -- Esto debido a que en HOTEROOL los precios siempre incluyen el impuesto.   
		   room.id_cont
	FROM       Hoterool rool
	INNER JOIN Hoteroom room  ON rool.id_room = room.id_room
	INNER JOIN Hotecont cont  ON room.id_cont = cont.id_cont
	INNER JOIN HOTECFAG cfag  ON rool.id_uhab = cfag.id_uhab AND cfag.eliminado = 0
	INNER JOIN HOTEIMAG imag  ON cfag.id_cfag = imag.id_cfag
	INNER JOIN HOTEIMCO imco  ON cont.id_cont = imco.id_cont AND imag.id_impu = imco.id_impu
	INNER JOIN SICLAIMP aimp  ON imag.id_impu = aimp.id_impu
	INNER JOIN Siclamon sicla ON cont.id_mone = sicla.id_mone
	WHERE (@id_oper = 0 OR room.id_oper = @id_oper)
	  AND (DATEDIFF(day, rool.fecha_ent, @dia_inic) >= 0
	  AND  DATEDIFF(day, @dia_inic, rool.fecha_sal) > 0
	   OR  DATEDIFF(day, rool.fecha_ent, @dia_fin) >= 0
	  AND  DATEDIFF(day, @dia_fin, rool.fecha_sal) > 0)
	  AND (rool.id_estado IN(3, 5) 
			OR (rool.id_estado = 2 
					AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
				)
			) 
	--UNION
	INSERT INTO @Tab_IngHAgenI
	SELECT id_rool, 
		   rool.id_uhab, 
		   rool.id_ralo, 
		   precio = CONVERT(NUMERIC(15, 2), precio), 
		   tasa = CASE
					   WHEN cont.id_mone != @mone THEN @tasa_ext / COALESCE(cont.tasa_fija, sicla.tasa)
					   ELSE 1
				  END, 
		   porc_impu = COALESCE(porc_impu, 0), 
		   --incluido = COALESCE(incluido, 2), 
		   1 incluido, -- Esto debido a que en HOTEROOL los precios siempre incluyen el impuesto.  
		   room.id_cont
	FROM       Hotehrol rool
	INNER JOIN Hotehrom room  ON rool.id_room = room.id_room
	INNER JOIN Hotecont cont  ON room.id_cont = cont.id_cont
	INNER JOIN HOTECFAG cfag  ON rool.id_uhab = cfag.id_uhab AND cfag.eliminado = 0
	INNER JOIN HOTEIMAG imag  ON cfag.id_cfag = imag.id_cfag
	INNER JOIN HOTEIMCO imco  ON cont.id_cont = imco.id_cont AND imag.id_impu = imco.id_impu
	INNER JOIN SICLAIMP aimp  ON imag.id_impu = aimp.id_impu
	INNER JOIN Siclamon sicla ON cont.id_mone = sicla.id_mone
	WHERE (@id_oper = 0 OR room.id_oper = @id_oper)
	  AND (DATEDIFF(day, rool.fecha_ent, @dia_inic) >= 0
	  AND  DATEDIFF(day, @dia_inic, rool.fecha_sal) > 0
	   OR  DATEDIFF(day, rool.fecha_ent, @dia_fin) >= 0
	  AND  DATEDIFF(day, @dia_fin, rool.fecha_sal) > 0)
	  AND --(rool.id_estado NOT IN(3, 5) 
			--OR 
			(rool.id_estado = 2 
					AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
				)
			--) 

	INSERT INTO @Tab_IngHAgenII
	SELECT *
	FROM   @Tab_IngHAgenI
	WHERE  incluido = 1

	UPDATE @Tab_IngHAgenI
	SET    incluido = 1
	FROM       @Tab_IngHAgenI  T1
	INNER JOIN @Tab_IngHAgenII T2 ON T1.id_rool = T2.id_rool
	WHERE t1.incluido = 0

	UPDATE @Tab_IngHAgenI
	SET    impuesto = 0
	WHERE  incluido = 0
  
	/*************************************************************** CURSOR DE INGRESOS **********************************************************************/

	DECLARE c_rool SCROLL CURSOR
	FOR SELECT *
		FROM(
			SELECT id_rool, 
				   id_uhab, 
				   id_ralo, 
				   precio, 
				   tasa, 
				   SUM(COALESCE(impuesto, 0)) AS "Impuestos", 
				   incluido, 
				   id_cont
			FROM @Tab_IngHAgenI
			GROUP BY id_rool, 
					 id_uhab, 
					 id_ralo, 
					 precio, 
					 tasa, 
					 incluido, 
					 id_cont) AS Total
	FOR READ ONLY

	OPEN c_rool
	FETCH FIRST FROM c_rool INTO @id_rool, @id_uhab, @id_ralo, @precio, @tasa, @Impuestos, @incluido, @id_cont
    
	/******************************************************** BUSQUEDA DE INGRESOS DEL CURSOR **********************************************************************/

	WHILE @@fetch_status = 0
		BEGIN
			SELECT @Impuestos = CASE WHEN @incluido = 2 THEN 1
									 ELSE @Impuestos / 100 + 1
								     END
       
			/* Busca si esta incluido en el contrato */

			SELECT @id_cfag = NULL
      
			/*  Busco el id_cfag  */

			SELECT @id_cfag = id_cfag, 
				   @tipo_cfag = tipo_cfag, 
				   @uhab = cfag.id_uhab, 
				   @ralo = cfag.id_ralo
			FROM  Hotecfag cfag
			WHERE id_uhab IS NOT NULL
			  AND id_ralo IS NOT NULL
			  AND id_uhab = @id_uhab
			  AND id_ralo = @id_ralo
			  AND eliminado = 0

			SELECT @factor_div = factor_div
			FROM   Hoteccon
			WHERE id_cfag = @id_cfag
			  AND id_cont = @id_cont
			  
			IF @id_cfag IS NULL
			BEGIN
				SELECT @id_cfag   = MAX(hotecfag.id_cfag), 
					   @tipo_cfag = MIN(tipo_cfag), 
					   @uhab      = @id_uhab, 
					   @cant_pax  = MAX(max_adultos)
				FROM       Hotecfag
				INNER JOIN hoteccon ON hotecfag.id_cfag = hoteccon.id_cfag
				INNER JOIN hoteuhab ON hoteuhab.id_uhab = hotecfag.id_uhab
				WHERE hotecfag.id_uhab = @id_uhab
				  AND hoteccon.id_cont = @id_cont
				  AND hotecfag.eliminado = 0

				SELECT @factor_div = factor_div
				FROM   Hoteccon
				WHERE  id_cfag = @id_cfag
				  AND  id_cont = @id_cont

				SELECT @id_cfag_ralo = MAX(hotecfag.id_cfag), 
					   @ralo         = @id_ralo, 
					   @precio_ralo  = CONVERT(NUMERIC(15, 2), MAX(precio))
				FROM       Hotecfag
				INNER JOIN hoteccon ON hotecfag.id_cfag = hoteccon.id_cfag
				INNER JOIN hotecpre ON hoteccon.id_ccon = hotecpre.id_ccon
				INNER JOIN hoteperi ON hoteperi.id_peri = hotecpre.id_peri
				WHERE id_ralo = @id_ralo
				  AND hoteccon.id_cont = @id_cont
				  AND hotecfag.eliminado = 0
				  AND DATEDIFF(day, hoteperi.fecha_desde, @dia_inic) >= 0
				  AND DATEDIFF(day, @dia_inic, hoteperi.fecha_hasta) > 0

				SELECT @factor_div_ralo = factor_div
				FROM   Hoteccon
				WHERE  id_cfag = @id_cfag_ralo
				  AND  id_cont = @id_cont
				  
				SELECT @precio_ralo = COALESCE(@precio_ralo * @cant_pax / @factor_div_ralo * @tasa, 0)
				
			END

			SELECT @precio = @precio * @cant_pax / @factor_div * @tasa  

			/* Convierto el precio a moneda final */

			IF @tipo_cfag IN(1, 2, 3)    

			/* No compuesto  */

			BEGIN
				IF @uhab IS NOT NULL AND @ralo IS NOT NULL
				BEGIN
					SELECT @ing_Ro_Al = COALESCE(@ing_Ro_Al, 0) + COALESCE(@precio, 0) / @Impuestos
					SELECT @ing_Ro_Pen = COALESCE(@ing_Ro_Pen, 0) + COALESCE(@precio_ralo, 0)    

				/* Ingreso de pension */

				END
				ELSE
				BEGIN
					IF @uhab IS NOT NULL AND @ralo IS NULL
					BEGIN
					
						/* Ingreso de Alojamiento */
						
						SELECT @ing_Ro_Al = COALESCE(@ing_Ro_Al, 0) + COALESCE(@precio, 0) / @Impuestos     
					END
					ELSE
					BEGIN
						SELECT @ing_OAgen = COALESCE(@ing_OAgen, 0) + COALESCE(@precio, 0)
					END
				END   

			/* Ingreso por otros   */

			END
			ELSE
				BEGIN
				
					/* tipo del concepto compuesto  */
					
					SELECT @tipo = csim.tipo_csim   
					FROM       Hoteccom ccom
					INNER JOIN Hotecsim csim ON ccom.id_csim = csim.id_csim
					WHERE  ccom.id_cfag = @id_cfag 
					
					EXEC Calc_Ing_Desg @id_cfag, @aloj_precio OUTPUT, @aloj_porc OUTPUT
						, @pen_precio OUTPUT, @pen_porc OUTPUT, @otr_precio OUTPUT, @otr_porc OUTPUT

					SELECT @aloj = @aloj_precio * @factor_div + @precio * @aloj_porc / 100, 
						   @pen  = @pen_precio  * @factor_div + @precio * @pen_porc  / 100, 
						   @otr  = @otr_precio  * @factor_div + @precio * @otr_porc  / 100

					IF @precio > 0 AND @aloj <= @precio
					BEGIN
						SELECT @ing_Ro_Al = @ing_Ro_Al + @aloj, 
							   @precio = @precio - @aloj
					END
					ELSE
					BEGIN
						SELECT @ing_Ro_Al = @ing_Ro_Al + @precio, 
							   @precio = 0
					END

					IF @precio > 0 AND @pen <= @precio
					BEGIN
						SELECT @ing_Ro_Pen = @ing_Ro_Pen + @pen, 
							   @precio = @precio - @pen
					END
					ELSE
					BEGIN
						SELECT @ing_Ro_Pen = @ing_Ro_Pen + @precio, 
							   @precio = 0
					END

					IF @precio > 0 AND @otr <= @precio
					BEGIN
						SELECT @ing_OAgen = @ing_OAgen + @otr, 
							   @precio = @precio - @otr
					END
					ELSE
					BEGIN
						SELECT @ing_OAgen = @ing_OAgen + @precio, 
							   @precio = 0
					END

					/* Asigno el resto del desglose del concepto */
					
					IF @precio > 0   
					BEGIN
						IF @tipo = 1
						BEGIN
							SELECT @ing_Ro_Al = @ing_Ro_Al + @precio
						END
						ELSE
						BEGIN
							IF @tipo = 2
							BEGIN
								SELECT @ing_Ro_Pen = @ing_Ro_Pen + @precio
							END
							ELSE
							BEGIN
								IF @tipo = 3
								BEGIN
									SELECT @ing_OAgen = @ing_OAgen + @precio
								END
							END
						END
					END
				END
				
			FETCH NEXT FROM c_rool INTO @id_rool, @id_uhab, @id_ralo, @precio, @tasa, @Impuestos, @incluido, @id_cont
			
		END

	DEALLOCATE c_rool
	
END