

/****** Object:  StoredProcedure [dbo].[Facturas_Notas_Cerradas]    Script Date: 11/01/2015 23:55:38 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE dbo.Facturas_Notas_Cerradas
	  @filter     VARCHAR(5000), 
	  @CodEmpresa CHAR(3)
AS

/* Procedimiento que genera un cursor,
   con los datos requeridos,
   para el listado de facturas cerradas ,
   y notas para la fecha hotelera
   agrupadas por cuenta/mon_cob.  
   
   Modificado Por: Luis Abarca
   Fecha: 09 Abril 2013   
   Detalle: Se agrego el prefijo, correspondiente, 
		a la moneda, asi como la empresa a que corresponde
		la factura 
   
*/

	BEGIN

		SET NOCOUNT ON

		DECLARE @pref_fact    CHAR(4), 
				@pref_no_fact CHAR(4)

		SELECT @pref_fact = SUBSTRING(prefijo_fact, 1, 3) + '-', 
			   @pref_no_fact = SUBSTRING(prefijo_no_fact, 1, 3) + '-'
		FROM Hotetabl

		DECLARE @Mone_Base INTEGER, 
				@da        INTEGER, 
				@pt        INTEGER

		SELECT @Mone_Base = id_mone
		FROM hotetabl

		SELECT @da = con0_da, 
			   @pt = con0_pt
		FROM hoteempr
		WHERE id_mone = @Mone_Base

		/** 01 Facturas  de Habitaciones **/

		SELECT enco.cuenta, 
			   hfac.nro_cheque, 
			   nro_documento = dbo.Get_PrefixNDoc(mone.cod_mone, hfac.nro_documento, hfac.factura), 
			   Empresa = dbo.fGet_EmpresaCm(mone.cod_mone), 
			   id_origen = 'H-' + habi.habitacion, 
			   refer_thab = thab.tipo_hab, 
			   cliente = hcli.nombre, 
			   importe_moncob = COALESCE(SUM(COALESCE(hfco.aplicado / hfex.tasa_monfac * hfco.tasa_monpag, 0)), 0), 
			   mon_cob = COALESCE(mone.cod_mone, sicla.cod_mone), 
			   importe_monfact = SUM(COALESCE(hfco.aplicado, 0)), 
			   mon_fact = sicla.cod_mone, 
			   hfac.fecha_cierre, 
			   hfac.id_operador, 
			   tcob.cod_tcob, 
			   lnota = 'N', 
			   enco.descripcion, 
			   hfac.factura, 
			   nro_factura = hfac.nro_documento, 
			   Hospedaje = ingresos.Hospedaje, 
			   ayb = ingresos.ayb, 
			   otros = ingresos.otros, 
			   Depositos = ingresos.depositos, 
			   impuestos = ingresos.impuestos, 
			   operador = oper.nombre, 
			   terminal = term.nombre INTO #temp_fact
		FROM Hoteacum acum
			 INNER JOIN Hotehfac hfac ON acum.id_factura = hfac.id_hfac
			 LEFT JOIN Operador oper ON hfac.id_operador = oper.id_oper
			 LEFT JOIN terminales term ON hfac.terminal = term.id_terminal
			 INNER JOIN Hotehfex hfex ON hfac.id_hfac = hfex.id_hfac
			 INNER JOIN hotehhab hhab ON hfex.id_origen = hhab.id_face
			 INNER JOIN Hotehabi habi ON hhab.id_habi = habi.id_habi
			 INNER JOIN Hotethab thab ON habi.id_thab = thab.id_thab
			 INNER JOIN Siclamon sicla ON hfac.id_mone = sicla.id_mone
			 LEFT JOIN Hotehfco hfco ON hfac.id_hfac = hfco.id_hfac
			 LEFT JOIN Hotetcob tcob ON hfco.id_tcob = tcob.id_tcob
			 LEFT JOIN Siclamon mone ON tcob.id_mone = mone.id_mone
			 LEFT JOIN Hoteenco enco ON hfco.id_cuenta = enco.id_cuenta
			 INNER JOIN(
			SELECT hcli.id_face, 
				   MIN(nombre) nombre
			FROM hotehcli hcli
			GROUP BY hcli.id_face) hcli ON hfex.id_origen = hcli.id_face
			 LEFT JOIN(
			SELECT hfac.nro_cheque, 
				   Hospedaje = SUM(CASE
										WHEN thab.id_con0 IS NOT NULL THEN CONVERT( NUMERIC(19, 2), COALESCE(CONVERT(NUMERIC(19, 2), importe_cargo * CASE
																																								WHEN hfel.corrige_a IS NULL THEN 1
																																								ELSE-1
																																						   END) - CONVERT(NUMERIC(19, 2), COALESCE(importe_dcto, 0.00)) - CONVERT(NUMERIC(19, 2), COALESCE(impu.impuesto, 0.00)), 0.00))
										ELSE 0.00
								   END), 
				   ayb = SUM(CASE
								  WHEN rald.id_pre0 IS NOT NULL THEN CONVERT(       NUMERIC(19, 2), COALESCE(CONVERT(NUMERIC(19, 2), importe_cargo * CASE
																																								WHEN hfel.corrige_a IS NULL THEN 1
																																								ELSE-1
																																						   END) - CONVERT(NUMERIC(19, 2), COALESCE(importe_dcto, 0.00)) - CONVERT(NUMERIC(19, 2), COALESCE(impu.impuesto, 0.00)), 0.00))
								  ELSE 0.00
							 END), 
				   depositos = SUM(CASE
										WHEN pre0.id_con0 = @da
										  OR pre0.id_con0 = @pt THEN CONVERT( NUMERIC(19, 2), COALESCE(CONVERT(NUMERIC(19, 2), importe_cargo * CASE
																																						  WHEN hfel.corrige_a IS NULL THEN 1
																																						  ELSE-1
																																					 END) - CONVERT(NUMERIC(19, 2), COALESCE(importe_dcto, 0.00)) - CONVERT(NUMERIC(19, 2), COALESCE(impu.impuesto, 0.00)), 0.00))
										ELSE 0.00
								   END), 
				   otros = SUM(CASE
									WHEN thab.id_con0 IS NULL
									 AND rald.id_pre0 IS NULL
									 AND pre0.id_con0 != @da
									 AND pre0.id_con0 != @pt THEN CONVERT(     NUMERIC(19, 2), COALESCE(CONVERT(NUMERIC(19, 2), importe_cargo * CASE
																																						   WHEN hfel.corrige_a IS NULL THEN 1
																																						   ELSE-1
																																					  END) - CONVERT(NUMERIC(19, 2), COALESCE(importe_dcto, 0.00)) - CONVERT(NUMERIC(19, 2), COALESCE(impu.impuesto, 0.00)), 0.00))
									ELSE 0.00
							   END), 
				   impuestos = SUM(COALESCE(impu.impuesto, 0.00))
			FROM Hoteacum acum
				 INNER JOIN Hotehfac hfac ON acum.id_factura = hfac.id_hfac
				 INNER JOIN Hotehfex hfex ON hfac.id_hfac = hfex.id_hfac
				 INNER JOIN Hotehhab hhab ON hfex.id_origen = hhab.id_face
				 INNER JOIN Siclamon sicla ON hfac.id_mone = sicla.id_mone
				 LEFT JOIN Hotehfel hfel ON hfac.id_hfac = hfel.id_hfac
				 LEFT JOIN hotepre0 pre0 ON hfel.id_pre0 = pre0.id_pre0
				 LEFT JOIN hotecon0 con0 ON pre0.id_con0 = con0.id_con0
				 LEFT JOIN(
				SELECT DISTINCT id_con0
				FROM hotethab) thab ON con0.id_con0 = thab.id_con0
				 LEFT JOIN(
				SELECT DISTINCT id_pre0, 
								id_ralo
				FROM hoterald
					 INNER JOIN hoterali ON hoterald.id_rali = hoterali.id_rali) rald ON pre0.id_pre0 = rald.id_pre0
																					 AND rald.id_ralo = hhab.id_ralo
				 LEFT JOIN(
				SELECT hotehfel.id_hfel, 
					   impuesto = SUM(COALESCE(importe_imp, 0.00))
				FROM hotehfei
					 INNER JOIN hotehfel ON hotehfei.id_hfel = hotehfel.id_hfel
					 INNER JOIN hotehfex ON hotehfex.id_hfac = hotehfel.id_hfac
					 INNER JOIN hotehfac ON hotehfac.id_hfac = hotehfex.id_hfac
					 INNER JOIN hoteacum ON hoteacum.id_factura = hotehfac.id_hfac
				WHERE hotehfac.origen = '1'
				  AND hoteacum.nota = 0
				GROUP BY hotehfel.id_hfel) impu ON hfel.id_hfel = impu.id_hfel
			WHERE hfac.origen = '1'
			  AND acum.nota = 0
			GROUP BY hfac.nro_cheque) ingresos ON hfac.nro_cheque = ingresos.nro_cheque
		WHERE origen = '1'
		  AND acum.nota = 0
		GROUP BY enco.cuenta, 
				 mone.cod_mone, 
				 hfac.nro_cheque, 
				 oper.nombre, 
				 term.nombre, 
				 ingresos.depositos, 
				 sicla.cod_mone, 
				 hcli.nombre, 
				 ingresos.Hospedaje, 
				 ingresos.ayb, 
				 ingresos.otros, 
				 ingresos.impuestos, 
				 hfac.factura, 
				 hfac.nro_documento, 
				 habi.habitacion, 
				 thab.tipo_hab, 
				 hfac.fecha_cierre, 
				 hfac.id_operador, 
				 hfac.terminal, 
				 tcob.cod_tcob, 
				 enco.descripcion  

				 /** Notas de Facturas de Habitaciones **/
                        
		UNION ALL
		SELECT enco.cuenta, 
			   hfac.nro_cheque, 
			   nro_documento = CASE
									WHEN hfac.factura = 1 THEN @pref_fact
									ELSE @pref_no_fact
							   END + CONVERT( VARCHAR(16), nota.nro_nota), 
			   Empresa = dbo.fGet_EmpresaCm(mone.cod_mone), 
			   id_origen = 'N' + CASE
									  WHEN nota.debito = 1 THEN 'D-'
									  ELSE 'C-'
								 END + 'H-' + habi.habitacion, 
			   refer_thab = thab.tipo_hab, 
			   cliente = hcli.nombre, 
			   importe_moncob = COALESCE(noco.entregado, 0), 
			   mon_cob = mone.cod_mone, 
			   importe_monfact = COALESCE(noco.aplicado, 0), 
			   mon_fact = sicla.cod_mone, 
			   nota.fecha_nota, 
			   nota.id_operador, 
			   tcob.cod_tcob, 
			   lnota = 'S', 
			   enco.descripcion, 
			   hfac.factura, 
			   nro_factura = nota.nro_nota, 
			   Hospedaje = ingresos.Hospedaje, 
			   ayb = ingresos.ayb, 
			   otros = ingresos.otros, 
			   Depositos = ingresos.depositos, 
			   impuestos = ingresos.impuestos, 
			   operador = oper.nombre, 
			   terminal = term.nombre
		FROM Hoteacum acum
			 INNER JOIN Hotenota nota ON acum.id_factura = nota.id_nota
			 LEFT JOIN Operador oper ON nota.id_operador = oper.id_oper
			 LEFT JOIN terminales term ON nota.terminal = term.id_terminal
			 INNER JOIN Hotehfac hfac ON nota.id_hfac = hfac.id_hfac
			 INNER JOIN Hotenoco noco ON nota.id_nota = noco.id_nota
			 INNER JOIN Hoteenco enco ON noco.id_cuenta = enco.id_cuenta
			 INNER JOIN Hotehfex hfex ON hfac.id_hfac = hfex.id_hfac
			 INNER JOIN hotehhab hhab ON hfex.id_origen = hhab.id_face
			 INNER JOIN Hotehabi habi ON hhab.id_habi = habi.id_habi
			 INNER JOIN Hotethab thab ON habi.id_thab = thab.id_thab
			 INNER JOIN Siclamon sicla ON hfac.id_mone = sicla.id_mone
			 INNER JOIN Hotetcob tcob ON noco.id_tcob = tcob.id_tcob
			 INNER JOIN Siclamon mone ON tcob.id_mone = mone.id_mone
			 INNER JOIN(
			SELECT hcli.id_face, 
				   MIN(nombre) nombre
			FROM hotehcli hcli
			GROUP BY hcli.id_face) hcli ON hfex.id_origen = hcli.id_face
			 LEFT JOIN(
			SELECT hfac.nro_cheque, 
				   Hospedaje = SUM(CASE
										WHEN thab.id_con0 IS NOT NULL THEN CONVERT( NUMERIC(19, 2), COALESCE(CONVERT(NUMERIC(19, 2), lnot.importe_nota * CASE
																																									WHEN nota.debito = 1 THEN 1
																																									ELSE-1
																																							   END) - CONVERT(NUMERIC(19, 2), COALESCE(impu.impuesto, 0.00)), 0.00))
										ELSE 0.00
								   END), 
				   ayb = SUM(CASE
								  WHEN rald.id_pre0 IS NOT NULL THEN CONVERT( NUMERIC(19, 2), COALESCE(CONVERT(NUMERIC(19, 2), lnot.importe_nota * CASE
																																									WHEN nota.debito = 1 THEN 1
																																									ELSE-1
																																							   END) - CONVERT(NUMERIC(19, 2), COALESCE(impu.impuesto, 0.00)), 0.00))
								  ELSE 0.00
							 END), 
				   depositos = SUM(CASE
										WHEN pre0.id_con0 = @da
										  OR pre0.id_con0 = @pt THEN CONVERT( NUMERIC(19, 2), COALESCE(CONVERT(NUMERIC(19, 2), lnot.importe_nota * CASE
																																							  WHEN nota.debito = 1 THEN 1
																																							  ELSE-1
																																						 END) - CONVERT(NUMERIC(19, 2), COALESCE(impu.impuesto, 0.00)), 0.00))
										ELSE 0.00
								   END), 
				   otros = SUM(CASE
									WHEN thab.id_con0 IS NULL
									 AND rald.id_pre0 IS NULL
									 AND pre0.id_con0 != @da
									 AND pre0.id_con0 != @pt THEN CONVERT(     NUMERIC(19, 2), COALESCE(CONVERT(NUMERIC(19, 2), lnot.importe_nota * CASE
																																							   WHEN nota.debito = 1 THEN 1
																																							   ELSE-1
																																						  END) - CONVERT(NUMERIC(19, 2), COALESCE(impu.impuesto, 0.00)), 0.00))
									ELSE 0.00
							   END), 
				   impuestos = SUM(COALESCE(impu.impuesto, 0.00))
			FROM Hoteacum acum
				 INNER JOIN Hotenota nota ON acum.id_factura = nota.id_nota
				 INNER JOIN Hotehfac hfac ON nota.id_hfac = hfac.id_hfac
				 LEFT JOIN Hotelnot lnot ON nota.id_nota = lnot.id_nota
				 LEFT JOIN hotepre0 pre0 ON lnot.id_pre0 = pre0.id_pre0
				 LEFT JOIN hotecon0 con0 ON pre0.id_con0 = con0.id_con0
				 LEFT JOIN(
				SELECT DISTINCT id_con0
				FROM hotethab) thab ON con0.id_con0 = thab.id_con0
				 LEFT JOIN(
				SELECT DISTINCT id_pre0, 
								id_ralo
				FROM hoterald
					 INNER JOIN hoterali ON hoterald.id_rali = hoterali.id_rali) rald ON pre0.id_pre0 = rald.id_pre0
				 LEFT JOIN(
				SELECT lnot.id_lnot, 
					   impuesto = SUM(COALESCE(importe, 0.00))
				FROM hotenoim noim
					 INNER JOIN hotelnot lnot ON noim.id_lnot = lnot.id_lnot
					 INNER JOIN hotenota nota ON lnot.id_nota = nota.id_nota
					 INNER JOIN hotehfac hfac ON nota.id_hfac = hfac.id_hfac
					 INNER JOIN hoteacum acum ON hfac.id_hfac = acum.id_factura
				WHERE hfac.origen = '1'
				  AND acum.nota = 1
				GROUP BY lnot.id_lnot) impu ON lnot.id_lnot = impu.id_lnot
			WHERE origen = '1'
			  AND acum.nota = 1
			GROUP BY hfac.nro_cheque) ingresos ON hfac.nro_cheque = ingresos.nro_cheque
		WHERE origen = '1'
		  AND acum.nota = 1
          
				 /* Facturas de Tarjetas de creditos **/
            
		UNION ALL
		SELECT enco.cuenta, 
			   hfac.nro_cheque, 
			   nro_documento = dbo.Get_PrefixNDoc(mone.cod_mone, hfac.nro_documento, hfac.factura), 
			   Empresa = dbo.fGet_EmpresaCm(mone.cod_mone), 
			   id_origen = 'T-' + CONVERT( VARCHAR(20), hcre.num_tarj), 
			   refer_thab = SPACE(9), 
			   hcre.nombre_titular, 
			   importe_moncob = COALESCE(SUM(COALESCE(hfco.aplicado / hfex.tasa_monfac * hfco.tasa_monpag, 0)), 0), 
			   mon_cob = COALESCE(mone.cod_mone, sicla.cod_mone), 
			   importe_monfact = SUM(COALESCE(hfco.aplicado, 0)), 
			   mon_fact = sicla.cod_mone, 
			   hfac.fecha_cierre, 
			   hfac.id_operador, 
			   tcob.cod_tcob, 
			   lnota = 'N', 
			   enco.descripcion, 
			   hfac.factura, 
			   nro_factura = hfac.nro_documento, 
			   Hospedaje = ingresos.Hospedaje, 
			   ayb = ingresos.ayb, 
			   otros = ingresos.otros, 
			   Depositos = ingresos.depositos, 
			   impuestos = ingresos.impuestos, 
			   operador = oper.nombre, 
			   terminal = term.nombre
		FROM Hoteacum acum
			 INNER JOIN Hotehfac hfac ON acum.id_factura = hfac.id_hfac
			 LEFT JOIN Operador oper ON hfac.id_operador = oper.id_oper
			 LEFT JOIN terminales term ON hfac.terminal = term.id_terminal
			 INNER JOIN Hotehfex hfex ON hfac.id_hfac = hfex.id_hfac
			 INNER JOIN hcretarj hcre ON hfex.id_origen = hcre.id_tarj
			 INNER JOIN Siclamon sicla ON hfac.id_mone = sicla.id_mone
			 LEFT JOIN Hotehfco hfco ON hfac.id_hfac = hfco.id_hfac
			 LEFT JOIN Hotetcob tcob ON hfco.id_tcob = tcob.id_tcob
			 LEFT JOIN Siclamon mone ON tcob.id_mone = mone.id_mone
			 LEFT JOIN Hoteenco enco ON hfco.id_cuenta = enco.id_cuenta
			 LEFT JOIN(
			SELECT hfac.nro_cheque, 
				   Hospedaje = SUM(CASE
										WHEN thab.id_con0 IS NOT NULL THEN CONVERT( NUMERIC(19, 2), COALESCE(CONVERT(NUMERIC(19, 2), importe_cargo * CASE
																																								WHEN hfel.corrige_a IS NULL THEN 1
																																								ELSE-1
																																						   END) - CONVERT(NUMERIC(19, 2), COALESCE(importe_dcto, 0.00)) - CONVERT(NUMERIC(19, 2), COALESCE(impu.impuesto, 0.00)), 0.00))
										ELSE 0.00
								   END), 
				   ayb = SUM(CASE
								  WHEN rald.id_pre0 IS NOT NULL THEN CONVERT( NUMERIC(19, 2), COALESCE(CONVERT(NUMERIC(19, 2), importe_cargo * CASE
																																								WHEN hfel.corrige_a IS NULL THEN 1
																																								ELSE-1
																																						   END) - CONVERT(NUMERIC(19, 2), COALESCE(importe_dcto, 0.00)) - CONVERT(NUMERIC(19, 2), COALESCE(impu.impuesto, 0.00)), 0.00))
								  ELSE 0.00
							 END), 
				   depositos = SUM(CASE
										WHEN pre0.id_con0 = @da
										  OR pre0.id_con0 = @pt THEN CONVERT( NUMERIC(19, 2), COALESCE(CONVERT(NUMERIC(19, 2), importe_cargo * CASE
																																						  WHEN hfel.corrige_a IS NULL THEN 1
																																						  ELSE-1
																																					 END) - CONVERT(NUMERIC(19, 2), COALESCE(importe_dcto, 0.00)) - CONVERT(NUMERIC(19, 2), COALESCE(impu.impuesto, 0.00)), 0.00))
										ELSE 0.00
								   END), 
				   otros = SUM(CASE
									WHEN thab.id_con0 IS NULL
									 AND rald.id_pre0 IS NULL
									 AND pre0.id_con0 != @da
									 AND pre0.id_con0 != @pt THEN CONVERT(NUMERIC(19, 2), COALESCE(CONVERT(NUMERIC(19, 2), importe_cargo * CASE
																																						   WHEN hfel.corrige_a IS NULL THEN 1
																																						   ELSE-1
																																					  END) - CONVERT(NUMERIC(19, 2), COALESCE(importe_dcto, 0.00)) - CONVERT(NUMERIC(19, 2), COALESCE(impu.impuesto, 0.00)), 0.00))
									ELSE 0.00
							   END), 
				   impuestos = SUM(COALESCE(impu.impuesto, 0.00))
			FROM Hoteacum acum
				 INNER JOIN Hotehfac hfac ON acum.id_factura = hfac.id_hfac
				 INNER JOIN Hotehfex hfex ON hfac.id_hfac = hfex.id_hfac
				 INNER JOIN hcretarj hcre ON hfex.id_origen = hcre.id_tarj
				 INNER JOIN Siclamon sicla ON hfac.id_mone = sicla.id_mone
				 LEFT JOIN Hotehfel hfel ON hfac.id_hfac = hfel.id_hfac
				 LEFT JOIN hotepre0 pre0 ON hfel.id_pre0 = pre0.id_pre0
				 LEFT JOIN hotecon0 con0 ON pre0.id_con0 = con0.id_con0
				 LEFT JOIN(
				SELECT DISTINCT id_con0
				FROM hotethab) thab ON con0.id_con0 = thab.id_con0
				 LEFT JOIN(
				SELECT DISTINCT id_pre0, 
								id_ralo
				FROM hoterald
					 INNER JOIN hoterali ON hoterald.id_rali = hoterali.id_rali) rald ON pre0.id_pre0 = rald.id_pre0
				 LEFT JOIN(
				SELECT hotehfel.id_hfel, 
					   impuesto = SUM(COALESCE(importe_imp, 0.00))
				FROM hotehfei
					 INNER JOIN hotehfel ON hotehfei.id_hfel = hotehfel.id_hfel
					 INNER JOIN hotehfex ON hotehfex.id_hfac = hotehfel.id_hfac
					 INNER JOIN hotehfac ON hotehfac.id_hfac = hotehfex.id_hfac
					 INNER JOIN hoteacum ON hotehfac.id_hfac = hoteacum.id_factura
				WHERE hotehfac.origen = '2'
				  AND hoteacum.nota = 0
				GROUP BY hotehfel.id_hfel) impu ON hfel.id_hfel = impu.id_hfel
			WHERE hfac.origen = '2'
			  AND acum.nota = 0
			GROUP BY hfac.nro_cheque) ingresos ON hfac.nro_cheque = ingresos.nro_cheque
		WHERE origen = '2'
		  AND acum.nota = 0
		GROUP BY enco.cuenta, 
				 mone.cod_mone, 
				 hfac.nro_cheque, 
				 sicla.cod_mone, 
				 oper.nombre, 
				 term.nombre, 
				 hfac.factura, 
				 hfac.nro_documento, 
				 hcre.num_tarj, 
				 ingresos.depositos, 
				 hcre.nombre_titular, 
				 ingresos.Hospedaje, 
				 ingresos.ayb, 
				 ingresos.otros, 
				 ingresos.impuestos, 
				 hfac.fecha_cierre, 
				 hfac.id_operador, 
				 hfac.terminal, 
				 tcob.cod_tcob, 
				 enco.descripcion  

				 /** Notas de Facturas de Tarjetas de Creditos **/

		UNION ALL
		SELECT enco.cuenta, 
			   hfac.nro_cheque, 
			   nro_documento = CASE
									WHEN hfac.factura = 1 THEN @pref_fact
									ELSE @pref_no_fact
							   END + CONVERT( VARCHAR(16), nota.nro_nota), 
			   Empresa = dbo.fGet_EmpresaCm(mone.cod_mone), 
			   id_origen = 'N' + CASE
									  WHEN nota.debito = 1 THEN 'D-'
									  ELSE 'C-'
								 END + 'T-' + CONVERT(     VARCHAR(20), hcre.num_tarj), 
			   refer_thab = SPACE(9), 
			   cliente = hcre.nombre_titular, 
			   importe_moncob = COALESCE(noco.entregado, 0), 
			   mon_cob = mone.cod_mone, 
			   importe_monfact = COALESCE(noco.aplicado, 0), 
			   mon_fact = sicla.cod_mone, 
			   nota.fecha_nota, 
			   nota.id_operador, 
			   tcob.cod_tcob, 
			   lnota = 'S', 
			   enco.descripcion, 
			   hfac.factura, 
			   nro_factura = nota.nro_nota, 
			   Hospedaje = ingresos.Hospedaje, 
			   ayb = ingresos.ayb, 
			   otros = ingresos.otros, 
			   Depositos = ingresos.depositos, 
			   impuestos = ingresos.impuestos, 
			   operador = oper.nombre, 
			   terminal = term.nombre
		FROM Hoteacum acum
			 INNER JOIN Hotenota nota ON acum.id_factura = nota.id_nota
			 LEFT JOIN Operador oper ON nota.id_operador = oper.id_oper
			 LEFT JOIN terminales term ON nota.terminal = term.id_terminal
			 INNER JOIN Hotehfac hfac ON nota.id_hfac = hfac.id_hfac
			 INNER JOIN Hotenoco noco ON nota.id_nota = noco.id_nota
			 INNER JOIN Hoteenco enco ON noco.id_cuenta = enco.id_cuenta
			 INNER JOIN Hotehfex hfex ON hfac.id_hfac = hfex.id_hfac
			 INNER JOIN hcretarj hcre ON hfex.id_origen = hcre.id_tarj
			 INNER JOIN Siclamon sicla ON hfac.id_mone = sicla.id_mone
			 INNER JOIN Hotetcob tcob ON noco.id_tcob = tcob.id_tcob
			 INNER JOIN Siclamon mone ON tcob.id_mone = mone.id_mone
			 LEFT JOIN(
			SELECT hfac.nro_cheque, 
				   Hospedaje = SUM(CASE
										WHEN thab.id_con0 IS NOT NULL THEN CONVERT( NUMERIC(19, 2), COALESCE(CONVERT(NUMERIC(19, 2), lnot.importe_nota * CASE
																																									WHEN nota.debito = 1 THEN 1
																																									ELSE-1
																																							   END) - CONVERT(NUMERIC(19, 2), COALESCE(impu.impuesto, 0.00)), 0.00))
										ELSE 0.00
								   END), 
				   ayb = SUM(CASE
								  WHEN rald.id_pre0 IS NOT NULL THEN CONVERT(       NUMERIC(19, 2), COALESCE(CONVERT(NUMERIC(19, 2), lnot.importe_nota * CASE
																																									WHEN nota.debito = 1 THEN 1
																																									ELSE-1
																																							   END) - CONVERT(NUMERIC(19, 2), COALESCE(impu.impuesto, 0.00)), 0.00))
								  ELSE 0.00
							 END), 
				   depositos = SUM(CASE
										WHEN pre0.id_con0 = @da
										  OR pre0.id_con0 = @pt THEN CONVERT( NUMERIC(19, 2), COALESCE(CONVERT(NUMERIC(19, 2), lnot.importe_nota * CASE
																																							  WHEN nota.debito = 1 THEN 1
																																							  ELSE-1
																																						 END) - CONVERT(NUMERIC(19, 2), COALESCE(impu.impuesto, 0.00)), 0.00))
										ELSE 0.00
								   END), 
				   otros = SUM(CASE
									WHEN thab.id_con0 IS NULL
									 AND rald.id_pre0 IS NULL
									 AND pre0.id_con0 != @da
									 AND pre0.id_con0 != @pt THEN CONVERT(     NUMERIC(19, 2), COALESCE(CONVERT(NUMERIC(19, 2), lnot.importe_nota * CASE
																																							   WHEN nota.debito = 1 THEN 1
																																							   ELSE-1
																																						  END) - CONVERT(NUMERIC(19, 2), COALESCE(impu.impuesto, 0.00)), 0.00))
									ELSE 0.00
							   END), 
				   impuestos = SUM(COALESCE(impu.impuesto, 0.00))
			FROM Hoteacum acum
				 INNER JOIN Hotenota nota ON acum.id_factura = nota.id_nota
				 INNER JOIN Hotehfac hfac ON nota.id_hfac = hfac.id_hfac
				 LEFT JOIN Hotelnot lnot ON nota.id_nota = lnot.id_nota
				 LEFT JOIN hotepre0 pre0 ON lnot.id_pre0 = pre0.id_pre0
				 LEFT JOIN hotecon0 con0 ON pre0.id_con0 = con0.id_con0
				 LEFT JOIN(
				SELECT DISTINCT id_con0
				FROM hotethab) thab ON con0.id_con0 = thab.id_con0
				 LEFT JOIN(
				SELECT DISTINCT id_pre0, 
								id_ralo
				FROM hoterald
					 INNER JOIN hoterali ON hoterald.id_rali = hoterali.id_rali) rald ON pre0.id_pre0 = rald.id_pre0
				 LEFT JOIN(
				SELECT lnot.id_lnot, 
					   impuesto = SUM(COALESCE(importe, 0.00))
				FROM hotenoim noim
					 INNER JOIN hotelnot lnot ON noim.id_lnot = lnot.id_lnot
					 INNER JOIN hotenota nota ON lnot.id_nota = nota.id_nota
					 INNER JOIN hotehfac hfac ON nota.id_hfac = hfac.id_hfac
					 INNER JOIN hoteacum acum ON hfac.id_hfac = acum.id_factura
				WHERE hfac.origen = '1'
				  AND acum.nota = 1
				GROUP BY lnot.id_lnot) impu ON lnot.id_lnot = impu.id_lnot
			WHERE origen = '2'
			  AND acum.nota = 1
			GROUP BY hfac.nro_cheque) ingresos ON hfac.nro_cheque = ingresos.nro_cheque
		WHERE origen = '2'
		  AND acum.nota = 1
			
				 /** Facturas de Agencias **/
			
		UNION ALL
		SELECT enco.cuenta, 
			   hfac.nro_cheque, 
			   nro_documento = dbo.Get_PrefixNDoc(mone.cod_mone, hfac.nro_documento, hfac.factura), 
			   Empresa = dbo.fGet_EmpresaCm(mone.cod_mone), 
			   id_origen = 'A-' + agen.agencia, 
			   refer_thab = SPACE(9), 
			   cont.contrato, 
			   importe_moncob = COALESCE(SUM(COALESCE(hfco.aplicado / hfag.tasa * hfco.tasa_monpag, 0)), 0), 
			   mon_cob = COALESCE(mone.cod_mone, sicla.cod_mone), 
			   importe_monfact = COALESCE(SUM(COALESCE(hfco.aplicado, 0)), 0), 
			   mon_fact = sicla.cod_mone, 
			   hfac.fecha_cierre, 
			   hfac.id_operador, 
			   tcob.cod_tcob, 
			   lnota = 'N', 
			   enco.descripcion, 
			   hfac.factura, 
			   nro_factura = hfac.nro_documento, 
			   Hospedaje = ingresos.Hospedaje, 
			   ayb = ingresos.ayb, 
			   otros = ingresos.otros, 
			   Depositos = ingresos.depositos, 
			   impuestos = impuage.impuestos, 
			   operador = oper.nombre, 
			   terminal = term.nombre
		FROM Hoteacum acum
			 INNER JOIN Hotehfac hfac ON acum.id_factura = hfac.id_hfac
			 LEFT JOIN Operador oper ON hfac.id_operador = oper.id_oper
			 LEFT JOIN terminales term ON hfac.terminal = term.id_terminal
			 INNER JOIN Hotehfag hfag ON hfac.id_hfac = hfag.id_hfac
			 INNER JOIN Hotecont cont ON hfag.id_cont = cont.id_cont
			 INNER JOIN Hoteagen agen ON cont.id_agen = agen.id_agen
			 INNER JOIN Siclamon sicla ON hfac.id_mone = sicla.id_mone
			 LEFT JOIN Hotehfco hfco ON hfac.id_hfac = hfco.id_hfac
			 LEFT JOIN Hotetcob tcob ON hfco.id_tcob = tcob.id_tcob
			 LEFT JOIN Siclamon mone ON tcob.id_mone = mone.id_mone
			 LEFT JOIN Hoteenco enco ON hfco.id_cuenta = enco.id_cuenta
			 LEFT JOIN(
			SELECT id_faca, 
				   nombre = MIN(nombre), 
				   paxs = COUNT(id_facn), 
				   habitacion = MIN(Habi.habitacion)
			FROM hotehhab hhab
				 INNER JOIN Hotehcli hcli ON hhab.id_face = hcli.id_face
				 INNER JOIN Hotehabi habi ON hhab.id_habi = habi.id_habi
			WHERE id_faca IS NOT NULL
			GROUP BY id_faca) faca ON hfag.id_faca = faca.id_faca
			 LEFT JOIN(
			SELECT hfac.nro_cheque, 
				   Hospedaje = SUM(CASE
										WHEN hfar.id_csim IS NOT NULL -- Concepto Simple
								   THEN CASE
											 WHEN csim.tipo_csim = 1 THEN COALESCE(CONVERT( NUMERIC(19, 2), hfar.importe / hfag.tasa * CASE
																																				  WHEN hfar.corrige_a IS NULL THEN 1
																																				  ELSE-1
																																			 END - COALESCE(0, 0.00)), 0)
											 ELSE 0
										END
										ELSE CASE
												  WHEN cfag.id_uhab IS NOT NULL THEN COALESCE(CONVERT(NUMERIC(19, 2), hfar.importe / hfag.tasa * CASE
																																							WHEN hfar.corrige_a IS NULL THEN 1
																																							ELSE-1
																																					   END - COALESCE(0, 0.00)), 0)
												  ELSE 0
											 END
								   END), 
				   ayb = SUM(CASE
								  WHEN hfar.id_csim IS NOT NULL -- Concepto Simple
							 THEN CASE
									   WHEN csim.tipo_csim = 2 THEN COALESCE(CONVERT(       NUMERIC(19, 2), hfar.importe / hfag.tasa * CASE
																																				  WHEN hfar.corrige_a IS NULL THEN 1
																																				  ELSE-1
																																			 END - COALESCE(0, 0.00)), 0)
									   ELSE 0
								  END
								  ELSE CASE
											WHEN cfag.id_uhab IS NULL
											 AND cfag.id_ralo IS NOT NULL THEN COALESCE(CONVERT(NUMERIC(19, 2), hfar.importe / hfag.tasa * CASE
																																					  WHEN hfar.corrige_a IS NULL THEN 1
																																					  ELSE-1
																																				 END - COALESCE(0, 0.00)), 0)
											ELSE 0
									   END
							 END), 
				   depositos = 0.00, 
				   otros = SUM(CASE
									WHEN hfar.id_csim IS NOT NULL -- Concepto Simple
							   THEN CASE
										 WHEN csim.tipo_csim = 3 THEN COALESCE(CONVERT(     NUMERIC(19, 2), hfar.importe / hfag.tasa * CASE
																																				  WHEN hfar.corrige_a IS NULL THEN 1
																																				  ELSE-1
																																			 END - COALESCE(0, 0.00)), 0)
										 ELSE 0
									END
									ELSE CASE
											  WHEN cfag.id_uhab IS NULL
											   AND cfag.id_ralo IS NULL THEN COALESCE(CONVERT(NUMERIC(19, 2), hfar.importe / hfag.tasa * CASE
																																					WHEN hfar.corrige_a IS NULL THEN 1
																																					ELSE-1
																																			   END - COALESCE(0, 0.00)), 0)
											  ELSE 0
										 END
							   END), 
				   impuestos = CONVERT( NUMERIC(19, 2), 0)
			FROM Hoteacum acum
				 INNER JOIN Hotehfac hfac ON acum.id_factura = hfac.id_hfac
				 INNER JOIN Hotehfar hfar ON hfac.id_hfac = hfar.id_hfac
				 LEFT JOIN Hotecsim csim ON hfar.id_csim = csim.id_csim
				 INNER JOIN Hotecfag cfag ON hfar.id_cfag = cfag.id_cfag
				 INNER JOIN hotehfag hfag ON hfar.id_hfac = hfag.id_hfac
			WHERE hfac.origen = '3'
			  AND acum.nota = 0
			GROUP BY hfac.nro_cheque) ingresos ON hfac.nro_cheque = ingresos.nro_cheque
			 LEFT JOIN(
			SELECT hfac2.nro_cheque, 
				   impuestos = SUM(COALESCE(CONVERT( NUMERIC(17, 2), (importe_imp * cantidad_h * DATEDIFF(day, fecha_1, fecha_2)) / hfag2.tasa), 000.0000))
			FROM hotehfai hfai2
				 INNER JOIN hotehfal hfal2 ON hfal2.id_hfal = hfai2.id_hfal
				 INNER JOIN hotehfac hfac2 ON hfac2.id_hfac = hfal2.id_hfac
				 INNER JOIN hotehfag hfag2 ON hfac2.id_hfac = hfag2.id_hfac
				 INNER JOIN hoteacum acum2 ON hfac2.id_hfac = acum2.id_factura
			WHERE hfac2.origen = '3'
			GROUP BY hfac2.nro_cheque, 
					 hfag2.tasa) impuage ON hfac.nro_cheque = impuage.nro_cheque
		WHERE origen = '3'
		  AND acum.nota = 0
		GROUP BY enco.cuenta, 
				 mone.cod_mone, 
				 hfac.nro_cheque, 
				 sicla.cod_mone, 
				 oper.nombre, 
				 term.nombre, 
				 impuage.impuestos, 
				 ingresos.depositos, 
				 hfac.factura, 
				 hfac.nro_documento, 
				 cont.contrato, 
				 agen.agencia, 
				 ingresos.Hospedaje, 
				 ingresos.ayb, 
				 ingresos.otros, 
				 ingresos.impuestos, 
				 hfac.fecha_cierre, 
				 hfac.id_operador, 
				 hfac.terminal, 
				 tcob.cod_tcob, 
				 enco.descripcion 

					  /** Notas de Facturas de Agencias **/
                   
		UNION ALL
		SELECT enco.cuenta, 
			   hfac.nro_cheque, 
			   nro_documento = CASE
									WHEN hfac.factura = 1 THEN @pref_fact
									ELSE @pref_no_fact
							   END + CONVERT( VARCHAR(16), nota.nro_nota), 
			   Empresa = dbo.fGet_EmpresaCm(mone.cod_mone), 
			   id_origen = 'N' + CASE
									  WHEN nota.debito = 1 THEN 'D-'
									  ELSE 'C-'
								 END + 'A-' + agen.agencia, 
			   refer_thab = SPACE(9), 
			   cliente = cont.contrato, 
			   importe_moncob = COALESCE(noco.entregado, 0), 
			   mon_cob = mone.cod_mone, 
			   importe_monfact = COALESCE(noco.aplicado, 0), 
			   mon_fact = sicla.cod_mone, 
			   nota.fecha_nota, 
			   nota.id_operador, 
			   tcob.cod_tcob, 
			   lnota = 'S', 
			   enco.descripcion, 
			   hfac.factura, 
			   nro_factura = nota.nro_nota, 
			   Hospedaje = ingresos.Hospedaje, 
			   ayb = ingresos.ayb, 
			   otros = ingresos.otros, 
			   Depositos = ingresos.depositos, 
			   impuestos = ingresos.impuestos, 
			   operador = oper.nombre, 
			   terminal = term.nombre
		FROM Hoteacum acum
			 INNER JOIN Hotenota nota ON acum.id_factura = nota.id_nota
			 LEFT JOIN Operador oper ON nota.id_operador = oper.id_oper
			 LEFT JOIN terminales term ON nota.terminal = term.id_terminal
			 INNER JOIN Hotehfac hfac ON nota.id_hfac = hfac.id_hfac
			 INNER JOIN Hotenoco noco ON nota.id_nota = noco.id_nota
			 INNER JOIN Hoteenco enco ON noco.id_cuenta = enco.id_cuenta
			 INNER JOIN Hotehfag hfag ON hfac.id_hfac = hfag.id_hfac
			 INNER JOIN Hotecont cont ON hfag.id_cont = cont.id_cont
			 INNER JOIN Hoteagen agen ON cont.id_agen = agen.id_agen
			 INNER JOIN Siclamon sicla ON hfac.id_mone = sicla.id_mone
			 INNER JOIN Hotetcob tcob ON noco.id_tcob = tcob.id_tcob
			 INNER JOIN Siclamon mone ON tcob.id_mone = mone.id_mone
			 LEFT JOIN(
			SELECT hfac.nro_cheque, 
				   Hospedaje = SUM(CASE
										WHEN thab.id_con0 IS NOT NULL THEN CONVERT( NUMERIC(19, 2), COALESCE(CONVERT(NUMERIC(19, 2), lnot.importe_nota * CASE
																																									WHEN nota.debito = 1 THEN 1
																																									ELSE-1
																																							   END) - CONVERT(NUMERIC(19, 2), COALESCE(impu.impuesto, 0.00)), 0.00))
										ELSE 0.00
								   END), 
				   ayb = SUM(CASE
								  WHEN rald.id_pre0 IS NOT NULL THEN CONVERT(       NUMERIC(19, 2), COALESCE(CONVERT(NUMERIC(19, 2), lnot.importe_nota * CASE
																																									WHEN nota.debito = 1 THEN 1
																																									ELSE-1
																																							   END) - CONVERT(NUMERIC(19, 2), COALESCE(impu.impuesto, 0.00)), 0.00))
								  ELSE 0.00
							 END), 
				   depositos = SUM(CASE
										WHEN pre0.id_con0 = @da
										  OR pre0.id_con0 = @pt THEN CONVERT( NUMERIC(19, 2), COALESCE(CONVERT(NUMERIC(19, 2), lnot.importe_nota * CASE
																																							  WHEN nota.debito = 1 THEN 1
																																							  ELSE-1
																																						 END) - CONVERT(NUMERIC(19, 2), COALESCE(impu.impuesto, 0.00)), 0.00))
										ELSE 0.00
								   END), 
				   otros = SUM(CASE
									WHEN thab.id_con0 IS NULL
									 AND rald.id_pre0 IS NULL
									 AND pre0.id_con0 != @da
									 AND pre0.id_con0 != @pt THEN CONVERT(     NUMERIC(19, 2), COALESCE(CONVERT(NUMERIC(19, 2), lnot.importe_nota * CASE
																																							   WHEN nota.debito = 1 THEN 1
																																							   ELSE-1
																																						  END) - CONVERT(NUMERIC(19, 2), COALESCE(impu.impuesto, 0.00)), 0.00))
									ELSE 0.00
							   END), 
				   impuestos = SUM(COALESCE(impu.impuesto, 0.00))
			FROM Hoteacum acum
				 INNER JOIN Hotenota nota ON acum.id_factura = nota.id_nota
				 INNER JOIN Hotehfac hfac ON nota.id_hfac = hfac.id_hfac
				 LEFT JOIN Hotelnot lnot ON nota.id_nota = lnot.id_nota
				 LEFT JOIN hotepre0 pre0 ON lnot.id_pre0 = pre0.id_pre0
				 LEFT JOIN hotecon0 con0 ON pre0.id_con0 = con0.id_con0
				 LEFT JOIN(
				SELECT DISTINCT id_con0
				FROM hotethab) thab ON con0.id_con0 = thab.id_con0
				 LEFT JOIN(
				SELECT DISTINCT id_pre0, 
								id_ralo
				FROM hoterald
					 INNER JOIN hoterali ON hoterald.id_rali = hoterali.id_rali) rald ON pre0.id_pre0 = rald.id_pre0
				 LEFT JOIN(
				SELECT lnot.id_lnot, 
					   impuesto = SUM(COALESCE(importe, 0.00))
				FROM hotenoim noim
					 INNER JOIN hotelnot lnot ON noim.id_lnot = lnot.id_lnot
					 INNER JOIN hotenota nota ON lnot.id_nota = nota.id_nota
					 INNER JOIN hotehfac hfac ON nota.id_hfac = hfac.id_hfac
					 INNER JOIN hoteacum acum ON hfac.id_hfac = acum.id_factura
				WHERE hfac.origen = '1'
				  AND acum.nota = 1
				GROUP BY lnot.id_lnot) impu ON lnot.id_lnot = impu.id_lnot
			WHERE hfac.origen = '3'
			  AND acum.nota = 1
			GROUP BY hfac.nro_cheque) ingresos ON hfac.nro_cheque = ingresos.nro_cheque
		WHERE origen = '3'
		  AND acum.nota = 1
		ORDER BY lnota, 
				 cuenta, 
				 mon_cob, 
				 hfac.nro_cheque, 
				 mon_fact

		IF @CodEmpresa <> '000'
			BEGIN
				DELETE #temp_fact
				WHERE Empresa <> @CodEmpresa
			END

		EXECUTE ('Select * from #temp_fact'+@filter+' Order by lnota, cuenta,mon_cob,nro_cheque,mon_fact')

	END