CREATE PROCEDURE [dbo].[CP_Cbtes_Control]    
@FECHA_DESDE	DATETIME,
@FECHA_HASTA	DATETIME,
@PROCESOS		VARCHAR(100)

AS

IF ISNULL(@PROCESOS,'')='' SET @PROCESOS='T'

IF (ISDATE(@FECHA_DESDE)!=1 OR ISDATE(@FECHA_HASTA) != 1 )
	RETURN 


IF @PROCESOS = 'T' OR CHARINDEX('1001',@PROCESOS)>0
		BEGIN 
			SELECT  CA.Id_Trans,CA.Id_Trans_D
			,Ori.Id_Cliente AS CLIENTE_ORI
			,OT.Id_Tipo_Cbte AS TIPO_ORI
			,Dest.Id_Cliente AS CLIENTE_TRANS_D
			,DT.Id_Tipo_Cbte AS TIPO_TRANS_D
			,(case When isnull(Clie_O.Id_Cliente_ref,0)=0 then Clie_O.Id_Cliente else   Clie_O.Id_Cliente_Ref  end) AS CLIENTE_TITULAR
			,(case When isnull(Clie_D.Id_Cliente_ref,0)=0 then Clie_D.Id_Cliente else   Clie_D.Id_Cliente_Ref  end) AS CLIENTE_DESTINO
			

			INTO #TEMPO1

			FROM Cbtes_Aplica CA, cbtes Ori , Cbtes Dest, Cbtes_Tipos OT, Cbtes_Tipos DT, Clientes Clie_O, Clientes Clie_D 
			WHERE CA.ID_TRANS >0 
			AND CA.ID_TRANS_D>0
			AND Ori.Id_Trans = CA.Id_Trans
			AND Dest.Id_Trans = CA.Id_Trans_D
			AND Ori.Id_Tipo_Cbte = OT.Id_Tipo_Cbte
			AND (OT.Compromete_Factura IN (1,-1) OR OT.Compromete_Recibos IN (1,-1))
			AND Dest.Id_Tipo_Cbte = DT.Id_Tipo_Cbte
			AND (DT.Compromete_Factura IN (1,-1) OR DT.Compromete_Recibos IN (1,-1))
			AND Ori.Id_Cliente != Dest.Id_Cliente
			AND Dest.Id_Cliente = Clie_D.Id_Cliente
			AND Ori.Id_Cliente = Clie_O.Id_Cliente
			AND Ori.Fecha BETWEEN @FECHA_DESDE AND @FECHA_HASTA 
			/* ESTE RESULTADO PARA LOS OTROS   */
			SELECT  C.Fecha
			        ,T.TIPO_ORI as Id_Tipo_Cbte 
					,dbo.CF_Cbte_Descrip(T.CLIENTE_ORI) as Cbte_Descrip
					,C.Destinatario
					,C.Imp_Total
					,C.Id_Trans,
			        '1001' as Error,
					'Inconsitencia en Titular de Cbte, cliente '+ CONVERT(VARCHAR(20),T.Id_Trans_D) 
					       + ' aplicado a con transacción = '+ CONVERT(VARCHAR(20),T.Id_Trans_D)+' '+ T.TIPO_TRANS_D + ' Cliente: '+ CONVERT(VARCHAR(20),T.CLIENTE_DESTINO)
					       as Observacion  
				INTO #AUX01
				FROM #TEMPO1 T, Cbtes C
				WHERE CLIENTE_DESTINO != CLIENTE_TITULAR  
					AND C.Id_Trans = T.Id_Trans
					ORDER BY 3,1
					DROP TABLE #TEMPO1
		END 

	IF @PROCESOS = 'T' OR CHARINDEX('1002',@PROCESOS)>0
		BEGIN
		SELECT  C.Fecha
				,C.Id_Tipo_Cbte 
				,dbo.CF_Cbte_Descrip(c.id_Cliente) as Cbte_Descrip
				,C.Destinatario
				,C.Imp_Total
				,C.Id_Trans 
				,'1002' as Error
				,'Documento No debe estar en la Cuenta Corriente del Cliente' AS Observacion
				INTO #AUX02
				FROM CBTES C 
				WHERE C.ID_ESTADO_CBTE <> 3 
				AND C.ID_COND_PAGO = 1 
				AND EXISTS (SELECT * FROM Clientes_CtaCte K WHERE C.ID_TRANS = K.ID_TRANS)
				AND C.Fecha BETWEEN @FECHA_DESDE AND @FECHA_HASTA  ;
		END

	IF OBJECT_ID (N'tempdb..#AUX01', N'U') IS NOT NULL AND
		OBJECT_ID (N'tempdb..#AUX02', N'U') IS NOT NULL 
		SELECT * FROM #AUX02 UNION SELECT * FROM #AUX01 ORDER BY 1,4,2

	IF OBJECT_ID (N'tempdb..#AUX01', N'U') IS NOT NULL AND OBJECT_ID (N'tempdb..#AUX02', N'U') IS NULL 
		SELECT * FROM #AUX01 ORDER BY 1,4,2

	IF OBJECT_ID (N'tempdb..#AUX01', N'U') IS NULL AND OBJECT_ID (N'tempdb..#AUX02', N'U') IS NOT NULL 
		SELECT * FROM #AUX02 ORDER BY 1,4,2

	IF OBJECT_ID (N'tempdb..#AUX01', N'U') IS NOT NULL 
	DROP TABLE #AUX01 
	IF OBJECT_ID (N'tempdb..#AUX02', N'U') IS NOT NULL 
	DROP TABLE #AUX02 
	 
	
