IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'CPJ_Productos_Cobros') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE [CPJ_Productos_Cobros]
END
GO

CREATE PROCEDURE [dbo].[CPJ_Productos_Cobros] @pJsonRecibido VARCHAR(8000)
AS 
SET NOCOUNT ON
/*Parametros*/

DECLARE		@pId_Usuario			INT,
			@pFlagUsarTrans			TINYINT,
			@pModoDebug				TINYINT,
			@pFechaDesde			DATETIME,
			@pFechaHasta			DATETIME,
		    @pFlag_Liquidacion		TINYINT,
		    @pId_Periodo_Caja		INT,
		    @pFlag_FPago			TINYINT,
		    @pTipo_Cob				CHAR(1),		-- vacio = todos  M = Manual L=Lote
			@pFormato				VARCHAR(3),     --D  Detallado   F Resumen por Familia     CF  Resumen por Cobrador Familia  CFM  Resumen por Cob,Familia,Mes  P Producto  T Tipo/Categoria    CTA  Por cuenta y CC
			@pFlag_Filtrar_Fam		TINYINT,
			@pFlag_Inc_Fact_Periodo	TINYINT,		--Incluye lo facturado en el periodo y no este cobrado solo para formato  D
			@pFlag_Filtrar_Prod		TINYINT,
			@pFlag_No_Rec_Aplic		TINYINT,		-- Se agrega flag para validar si no se requiere Recibos de Aplicacion- 2021/08/12  0=Con Recibos de Aplica, 1= Sin Recibos de Aplica
			@pFlag_Filtrar_CC		TINYINT,
			@pId_Centro_Costo		VARCHAR(10)
			


SET @pId_Usuario				= ISNULL(CONVERT(SMALLINT, dbo.CF_JSON_VALUE(@pJsonRecibido,'Id_Usuario')), 0)
SET @pFlagUsarTrans				= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJsonRecibido,'FlagUsarTrans')), 0)
SET @pModoDebug					= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJsonRecibido,'ModoDebug')), 0)

SET @pFechaDesde				= ISNULL(CONVERT(DATETIME, dbo.CF_JSON_VALUE(@pJsonRecibido,'FechaDesde')),GETDATE())
SET @pFechaHasta				= ISNULL(CONVERT(DATETIME, dbo.CF_JSON_VALUE(@pJsonRecibido,'FechaHasta')),GETDATE())

SET	@pFlag_Liquidacion			= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJsonRecibido,'Flag_Liquidacion')), 0)
SET	@pId_Periodo_Caja			= ISNULL(CONVERT(INT, dbo.CF_JSON_VALUE(@pJsonRecibido,'Id_Periodo_Caja')), 0)
SET	@pFlag_FPago				= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJsonRecibido,'Flag_FPago')), 0)
SET	@pTipo_Cob					= ISNULL(CONVERT(CHAR(1), dbo.CF_JSON_VALUE(@pJsonRecibido,'Tipo_Cob')), '')
SET	@pFormato					= ISNULL(CONVERT(VARCHAR(3), dbo.CF_JSON_VALUE(@pJsonRecibido,'Formato')), 0)
SET	@pFlag_Filtrar_Fam			= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJsonRecibido,'Flag_Filtrar_Fam')), 0)
SET	@pFlag_Inc_Fact_Periodo		= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJsonRecibido,'Flag_Inc_Fact_Periodo')), 0)
SET	@pFlag_Filtrar_Prod			= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJsonRecibido,'Flag_Filtrar_Prod')), 0)
SET @pFlag_No_Rec_Aplic			= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJsonRecibido,'Flag_No_Rec_Aplic')), 0)
SET @pFlag_Filtrar_CC			= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJsonRecibido,'Flag_Filtrar_CC')), 0)
SET @pId_Centro_Costo	        = ISNULL(CONVERT(VARCHAR(10), dbo.CF_JSON_VALUE(@pJsonRecibido,'Id_Centro_Costo')), '')


  CREATE TABLE #Trans (Id_Trans INT)

  DECLARE @Id_Producto 				VARCHAR(14)
		  ,@Id_Proveedor 			INT
		  ,@Razon_Social		 	VARCHAR(100)
		  ,@Cuit 					VARCHAR(13)
		  ,@Id_Iva 					CHAR(3)
		  ,@Id_Alias_Cta_Cont 		INT
		  ,@Id_Cta_Contable_H 		INT
		  ,@Porc_Comision_Prov 		DECIMAL(7,2) 
		  ,@Importe 				DECIMAL(12,2) 
		  ,@Importe_Credito 		DECIMAL(12,2) 
		  ,@Id_Tipo_Cbte 			CHAR(4) 
		  ,@Fecha 					DATETIME
		  ,@Imp_Prod_SIva  			DECIMAL(12,2) 
		  ,@Id_Trans 				INT
		  ,@Id_Cliente_Externo 		VARCHAR(14)
		  ,@Id_Cta_Banco 			INT
		  ,@Id_Cliente 				INT
		  ,@Total     				DECIMAL(20,2)
		  ,@Existe_Tag_Prod 		INT
		  ,@Existe_Tag_Fam 			INT
		  ,@e_Tag_Prod				SMALLINT
		  ,@e_Tag_Prod_Familias		SMALLINT

  SET @Fecha = (SELECT CAST(GETDATE() AS DATE))

  DECLARE @Sql		    VARCHAR(MAX)
  DECLARE @SqlSaldo	    VARCHAR(MAX)
  DECLARE @SqlSelect	VARCHAR(MAX)
  DECLARE @SqlInto	    VARCHAR(MAX)
  DECLARE @SqlFrom	    VARCHAR(MAX)
  DECLARE @SqlWhere  	VARCHAR(MAX)
  DECLARE @SqlGroup	    VARCHAR(MAX)

  SET @e_Tag_Prod = 90
  SET @Existe_Tag_Prod = 0 
  
  SET @e_Tag_Prod_Familias = 80
  SET @Existe_Tag_Fam = 0 

  IF @pFlag_Filtrar_Prod = 1
	SET @Existe_Tag_Prod = ISNULL( (SELECT COUNT(*) FROM TagFilePos_ TAG WHERE TAG.TBL = @e_Tag_Prod AND TAG.USR = @pId_Usuario) ,0)
  
  IF @pFlag_Filtrar_Fam = 1
	 SET @Existe_Tag_Fam = ISNULL( (SELECT COUNT(*) FROM TagFile_ TAG WHERE TAG.TBL = @e_Tag_Prod_Familias AND TAG.USR = @pId_Usuario) ,0)


/************************************************************* Creo la tabla ***************************************************************************************/
  

  SELECT (i.Imp_Gravado * F.Cotizacion) as Imp_Gravado_Item													--1
		,(i.Imp_Final * F.Cotizacion) as Imp_Final_Item														--2
		,f.Id_trans as Id_Trans_Fac																			--3
		,(f.Imp_Gravado + f.Imp_Gravado2 + f.Imp_Gravado3 + f.Imp_Exento) * F.Cotizacion as Importe_Fac		--4
		,f.Imp_total * F.Cotizacion as Imp_total_Fac														--5
		,r.Id_trans as Id_Trans_Rec																			--6
		,a.Importe  as Importe_Aplicado																		--7
		,i.Fecha_QA																							--8
		,i.Fecha_Venc																						--9
		,i.Id_cliente																						--10
		,C.Nombre_Fantasia																					--11
		,C.Id_Cliente_Externo																				--12
		,C.Razon_Social																						--13
		,C.Id_Tipo_Cli																						--14
		,dbo.CF_Cbte_Descrip(f.id_trans) as Desc_Factura													--15
		,F.Fecha																							--16
		,dbo.CF_Cbte_Descrip(R.id_trans) as Desc_Recibo														--17
		,R.Fecha as Fecha_Rec																				--18
		,R.Imp_Total as Imp_Total_Rec																		--19
		,R.Id_Estado_Cbte																					--20
		,R.Cotizacion as Cotizacion_Rec																		--21
		,F.Cotizacion as Cotizacion_Fac																		--22
		,F.Porc_Bonificacion																				--23
		,F.Id_Cobrador																						--24
		,I.Id_Producto																						--25
		,P.Descripcion_Detallada																			--26
		,P.Id_Familia																						--27
		,FP.Descripcion as Descripcion_Familia																--28
		,CASE ISNULL(f.Imp_total,0) 
		     WHEN 0 THEN 100 
			 ELSE (a.Importe / f.Imp_total) * 100 
			 END as porc_aplicado																			--29
		,CASE ISNULL(f.Imp_total,0) 
		     WHEN 0 THEN (i.Imp_Final *  F.Cotizacion )  * (1 -(F.Porc_Bonificacion / 100))
			 ELSE (i.Imp_Final *  F.Cotizacion )  * ( a.Importe / f.Imp_total) * (1 -(F.Porc_Bonificacion / 100))
			 END  as Imp_Prod_CIva																			--30
		,CASE ISNULL(f.Imp_total,0) 
		     WHEN 0 THEN (i.Imp_Gravado * F.Cotizacion ) * (1 -(F.Porc_Bonificacion / 100))
			 ELSE (i.Imp_Gravado * F.Cotizacion ) * ( a.Importe / f.Imp_total) * (1 -(F.Porc_Bonificacion / 100)) 
			 END as Imp_Prod_SIva																			--31
		,V.DESCRIPCION AS Nombre_Cobrador																	--32
		,dbo.SF_Cli_Categ_Mes(i.Id_cliente, isnull(i.Fecha_QA,F.Fecha)) as Id_Tipo_Cli_Ori					--33
		,CT.Descripcion AS Descripcion_Tipo_Cli																--34
		,isnull(c.id_cliente_ref,0) as id_cliente_ref														--35
		,cast(0 as decimal(12,2)) AS EFECTIVO																--36
		,cast(0 as decimal(12,2)) AS TARJETA																--37
		,cast(0 as decimal(12,2)) AS CHEQ_TER																--38
		,cast(0 as decimal(12,2)) AS CHEQ_PROP																--39
		,cast(0 as decimal(12,2)) AS DEBITO																	--40
		,cast(0 as decimal(12,2)) AS FINANC																	--41
		,cast(0 as decimal(12,2)) AS OTROS																	--42
		,cast(0 as decimal(12,2)) AS APLIC																	--43
		,cast(0 as decimal(12,2)) AS Total_Pagos															--44
		,cast(0 as decimal(12,2)) AS Dif																	--45
		,i.Item as Item_Fac																					--46
		,cast(0 as decimal(12,5)) AS Coef_Pago																--47 
		,ISNULL(C.Id_Cond_Pago, 0) AS Id_Cond_Pago															--48 14/01/2020 Agregado Ticket 18.924
		,ISNULL(CCP.Descripcion, '') AS Cond_Pago															--49 14/01/2020 Agregado Ticket 18.924
		,f.Imp_Percep AS Imp_Total_Percep_Reten_Fac															--50 14/01/2020 Agregado Ticket 18.924
		,r.Imp_Percep AS Imp_Total_Percep_Reten_Rec															--51 14/01/2020 Agregado Ticket 18.924
		,CLFC.Nombre_Fantasia		AS FC_CL_Nombre_Fantasia												--52 27/01/2021
		,CLFC.Id_Cliente_Externo	AS FC_CL_Id_Cliente_Externo												--53 27/01/2021
		,CLFC.Razon_Social			AS FC_CL_Razon_Social													--54 27/01/2021
		,CLFC.Id_Tipo_Cli			AS FC_CL_Id_Tipo_Cli													--55 27/01/2021
		,C.Id_Cobrador				AS CL_Id_Cobrador														--56 04/02/2021
		,CP.Id_Caja					AS Id_Caja																--57 04/02/2021
		,I.Bonif					AS Porc_Bonif_Item														--58 13/04/2021 Agregado Ticket 22.253
		,((100 * I.Imp_Final) / (100-I.Bonif)) AS Imp_Final_Item_Sin_Desc									--59 13/04/2021 Agregado Ticket 22.253
		,(((100 * I.Imp_Final) / (100-I.Bonif)) * (I.Bonif / 100)) AS Imp_Bonif_Item						--60 13/04/2021 Agregado Ticket 22.253
		,0 AS Cantidad_Prod																					--61
		,P.Id_Alias_Cta_Cont AS Alias																		--62
		,P.Id_Centro_Costo   																		     	--63 30/08/2021 agregado ticket 24099 (las cantidades del producto comprado en esa factura, que no incluya las cantidades a valor $0)
		,CONVERT(VARCHAR(20), TRF.Fecha_Mov, 120) AS Fac_Fecha_Mov											--64 26/01/2022 Agregado Ticket 535
		,CONVERT(VARCHAR(20), TRR.Fecha_Mov, 120) AS Rec_Fecha_Mov											--65 26/01/2022 Agregado Ticket 535
   INTO #Reporte
   FROM cbtes_items i,cbtes f,cbtes_aplica a,cbtes r, cbtes_tipos tr, Cbtes_Tipos TF, Clientes C,TagFilePos_ TAG, Productos P,Prod_Familias FP, Vendedores V, Clientes_Tipos CT, Cbtes_Cond_Pago CCP, Clientes CLFC, Cajas_Mov CM, Cajas_Periodos CP, Transacciones TRF, Transacciones TRR
   WHERE 1=0

/************************************************************* Conbranzas por recibos ***************************************************************************************/


   SET @SqlSelect =  ' SELECT (i.Imp_Gravado * F.Cotizacion) as Imp_Gravado_Item     --1
							  ,(i.Imp_Final * F.Cotizacion) as Imp_Final_Item               --2
							  ,f.Id_trans as Id_Trans_Fac                                   --3
							  ,(f.Imp_Gravado + f.Imp_Gravado2 + f.Imp_Gravado3 + f.Imp_Exento) * F.Cotizacion as Importe_Fac   --4
							  ,f.Imp_total * F.Cotizacion as Imp_total_Fac   --5
							  ,r.Id_trans as Id_Trans_Rec                    --6
							  ,a.Importe  as Importe_Aplicado                --7
							  ,i.Fecha_QA                                    --8
							  ,i.Fecha_Venc                                  --9
							  ,CASE
								WHEN i.Id_Cliente IS NULL THEN F.Id_Cliente  --10
								WHEN i.Id_Cliente = 0 THEN F.Id_Cliente
								ELSE i.Id_Cliente
								END AS Id_Cliente
							  ,C.Nombre_Fantasia                             --11
							  ,C.Id_Cliente_Externo                          --12
							  ,C.Razon_Social                                --13
							  ,C.Id_Tipo_Cli                                 --14
							  ,dbo.CF_Cbte_Descrip(f.id_trans) as Desc_Factura    --15
							  ,F.Fecha                                            --16
							  ,dbo.CF_Cbte_Descrip(R.id_trans) as Desc_Recibo     --17
							  ,R.Fecha as Fecha_Rec								  --18
							  ,R.Imp_Total as Imp_Total_Rec					      --19
							  ,R.Id_Estado_Cbte									  --20
							  ,R.Cotizacion as Cotizacion_Rec                     --21
							  ,F.Cotizacion as Cotizacion_Fac                     --22
							  ,F.Porc_Bonificacion								  --23
							  ,F.Id_Cobrador								      --24
							  ,I.Id_Producto									  --25
							  ,P.Descripcion_Detallada						      --26
							  ,P.Id_Familia										  --27
							  ,FP.Descripcion as Descripcion_Familia              --28
							  ,(f.Imp_Total / (select sum(z.Importe) from cbtes_aplica z where z.id_trans = f.id_trans and z.id_trans_d = r.id_trans  and z.importe > 1))*100 as porc_aplicado   --29
							  ,CASE ISNULL(f.Imp_total,0) 
									WHEN 0 THEN (i.Imp_Final *  F.Cotizacion ) * (1 -(F.Porc_Bonificacion / 100))
									ELSE (i.Imp_Final *  F.Cotizacion )  * ( a.Importe / f.Imp_total) * (f.Imp_Total / (select sum(z.Importe) from cbtes_aplica z where z.id_trans = f.id_trans and z.id_trans_d = r.id_trans  and z.importe > 1)) * (1 -(F.Porc_Bonificacion / 100)) 
									END as Imp_Prod_CIva --30
							  ,CASE ISNULL(f.Imp_total,0) 
									WHEN 0 THEN (i.Imp_Gravado * F.Cotizacion ) * (1 -(F.Porc_Bonificacion / 100)) 
									ELSE (i.Imp_Gravado * F.Cotizacion ) * ( a.Importe / f.Imp_total) * (f.Imp_Total / (select sum(z.Importe) from cbtes_aplica z where z.id_trans = f.id_trans and z.id_trans_d = r.id_trans  and z.importe > 1)) * (1 -(F.Porc_Bonificacion / 100)) 
									END as Imp_Prod_SIva --31
							  ,ISNULL(V.DESCRIPCION,'''') AS Nombre_Cobrador                                               --32
							  ,dbo.SF_Cli_Categ_Mes(i.Id_cliente, isnull(i.Fecha_QA,F.Fecha)) as Id_Tipo_Cli_Ori --33
							  ,CT.Descripcion AS Descripcion_Tipo_Cli		                                                                 --34
							  ,isnull(c.id_cliente_ref,0) as id_cliente_ref                                   --35
							  ,cast(0 as decimal(12,2)) AS EFECTIVO                                           --36
							  ,cast(0 as decimal(12,2)) AS TARJETA                                            --37
							  ,cast(0 as decimal(12,2)) AS CHEQ_TER                                           --38
							  ,cast(0 as decimal(12,2)) AS CHEQ_PROP                                          --39
							  ,cast(0 as decimal(12,2)) AS DEBITO                                             --40
							  ,cast(0 as decimal(12,2)) AS FINANC                                             --41
							  ,cast(0 as decimal(12,2)) AS OTROS 
							  ,cast(0 as decimal(12,2)) AS APLIC                                                --42
							  ,cast(0 as decimal(12,2)) AS Total_Pagos                                        --43
							  ,cast(0 as decimal(12,2)) AS Dif                                                --44
							  ,i.Item as Item_Fac															  --45
							  ,cast(0 as decimal(12,5)) AS Coef_Pago										  --46  
							  ,ISNULL(C.Id_Cond_Pago, 0) AS Id_Cond_Pago									--47 14/01/2020 Agregado Ticket 18.924
							  ,ISNULL(CCP.Descripcion, '''') AS Cond_Pago									--48 14/01/2020 Agregado Ticket 18.924
							  ,f.Imp_Percep AS Imp_Total_Percep_Reten_Fac									--49 14/01/2020 Agregado Ticket 18.924
							  ,r.Imp_Percep AS Imp_Total_Percep_Reten_Rec									--50 14/01/2020 Agregado Ticket 18.924
							  ,CLFC.Nombre_Fantasia		AS FC_CL_Nombre_Fantasia							--51 27/01/2021
							  ,CLFC.Id_Cliente_Externo	AS FC_CL_Id_Cliente_Externo							--52 27/01/2021
							  ,CLFC.Razon_Social		AS FC_CL_Razon_Social								--53 27/01/2021
							  ,CLFC.Id_Tipo_Cli			AS FC_CL_Id_Tipo_Cli								--54 27/01/2021
							  ,C.Id_Cobrador			AS CL_Id_Cobrador								    --55 04/02/2021
							  ,CP.Id_Caja				AS Id_Caja											--56 04/02/2021
							  ,I.Bonif					AS Porc_Bonif_Item									--57 13/04/2021 Agregado Ticket 22.253
							  ,CASE ISNULL(I.Imp_Final,0) WHEN 0 THEN 0
							    ELSE  ((I.Imp_Final) / (1-(I.Bonif/100)) ) END AS Imp_Final_Item_Sin_Desc	--58 13/04/2021 Agregado Ticket 22.253
							
							  ,CASE ISNULL(I.Imp_Final,0) WHEN 0 THEN 0
							     ELSE  ((I.Imp_Final) / (1-(I.Bonif/100)) ) - I.Imp_Final END AS Imp_Bonif_Item	--59 13/04/2021 Agregado Ticket 22.253
		                      ,isnull((select SUM(Cantidad) from cbtes_items where id_trans=f.Id_trans  and imp_final>0 and Id_Producto=i.Id_Producto),0) AS Cantidad_Prod		--60 30/08/2021 agregado ticket 24099 (las cantidades del producto comprado en esa factura, que no incluya las cantidades a valor $0)
			                  ,P.Id_Alias_Cta_Cont AS Alias
	                          ,P.Id_Centro_Costo
							  ,CONVERT(VARCHAR(20), TRF.Fecha_Mov, 120)	AS Fac_Fecha_Mov					--64 26/01/2022 Agregado Ticket 535
							  ,CONVERT(VARCHAR(20), TRR.Fecha_Mov, 120)	AS Rec_Fecha_Mov					--65 26/01/2022 Agregado Ticket 535
'
/*
							  ,((100 * I.Imp_Final) / (100-I.Bonif)) AS Imp_Final_Item_Sin_Desc				--58 13/04/2021 Agregado Ticket 22.253
							  ,(((100 * I.Imp_Final) / (100-I.Bonif)) * (I.Bonif / 100)) AS Imp_Bonif_Item	--59 13/04/2021 Agregado Ticket 22.253
							  '
*/
  SET @SqlInto = ' ' 

  SET @SqlWhere = 'WHERE r.id_tipo_cbte = tr.id_tipo_cbte
							AND TRF.Id_Trans = F.Id_Trans
							AND TRR.Id_Trans = R.Id_Trans
						    AND tr.compromete_recibos in (1,-1)
							AND r.id_cliente = c.id_Cliente
							AND ISNULL(F.Id_Cliente, 0) = CLFC.Id_Cliente
							AND a.id_trans_d = r.id_trans 
							AND f.id_trans = a.id_trans 
							AND f.id_estado_cbte in (1,2,4) 
							AND r.id_estado_cbte in (2,4)
							AND i.id_trans = f.id_trans 
							AND f.id_tipo_cbte = TF.id_tipo_cbte 
							AND I.Id_Producto = P.Id_Producto
							AND ISNULL(P.Id_Familia,0) = ISNULL(FP.Id_Familia,0)
							AND ISNULL(F.Id_Cond_Pago, 0) = ISNULL(CCP.Id_Cond_Pago, 0)
							AND CM.Id_Trans = R.Id_Trans
							AND CM.Id_Periodo_Caja = CP.Id_Periodo_Caja
							AND r.Fecha >= ''' + CONVERT(VARCHAR(8),@pFechaDesde   ,112) + '''
							AND r.Fecha <  ''' + CONVERT(VARCHAR(8),@pFechaHasta+1 ,112) + '''
							AND F.Imp_total <> 0
							
							AND TF.Compromete_Factura = 1 
							AND ISNULL(F.Id_Cobrador,'''') = ISNULL(V.Id_Vendedor,'''')
 							AND ISNULL(CT.ID_TIPO_CLI,0) = ISNULL(dbo.SF_Cli_Categ_Mes(i.Id_cliente, isnull(i.Fecha_QA,F.Fecha)),0)'

  IF (@pFlag_No_Rec_Aplic = 1)
  		SET @SqlWhere = LTRIM(RTRIM(@SqlWhere)) + ' AND R.Imp_Total <> 0 '

  IF (@pFlag_Filtrar_CC = 1)
		SET @SqlWhere = LTRIM(RTRIM(@SqlWhere)) + ' AND ISNULL(P.Id_Centro_Costo,'''') = ''' + @pId_Centro_Costo + ''''
		 
  IF UPPER(@pTipo_Cob) = 'M'
	 SET @SqlWhere = LTRIM(RTRIM(@SqlWhere)) + '	AND ISNULL(R.Lote,'''') = '''''

  IF ISNULL(@pTipo_Cob,'') = 'L'
	 SET @SqlWhere = LTRIM(RTRIM(@SqlWhere)) + '	AND ISNULL(R.Lote,'''') != '''''


  IF @Existe_Tag_Prod > 0
  BEGIN
	  SET @SqlFrom = 'FROM cbtes_items i,cbtes f,cbtes_aplica a,cbtes r, cbtes_tipos tr, Cbtes_Tipos TF, Clientes C,TagFilePos_ TAG, Productos P,Prod_Familias FP, Vendedores V, Clientes_Tipos CT, Clientes CLFC, Cbtes_Cond_Pago CCP, Cajas_Mov CM, Cajas_Periodos CP, Transacciones TRF, Transacciones TRR'
      SET @SqlWhere = RTRIM(LTRIM(@SqlWhere)) + ' AND (i.id_producto = TAG.POS 
							      AND TAG.TBL = ' + CAST(@e_Tag_Prod  AS VARCHAR(10))  + '
								  AND TAG.USR = ' + CAST(@pId_Usuario AS VARCHAR(10)) + '
								 ) ' 
  END
  ELSE
  BEGIN
      IF @Existe_Tag_Fam > 0
	  BEGIN
		  SET @SqlFrom = 'FROM cbtes_items i,cbtes f,cbtes_aplica a,cbtes r, cbtes_tipos tr, Cbtes_Tipos TF, Clientes C,TagFile_ TAG, Productos P,Prod_Familias FP, Vendedores V, Clientes_Tipos CT, Clientes CLFC, Cbtes_Cond_Pago CCP, Cajas_Mov CM, Cajas_Periodos CP, Transacciones TRF, Transacciones TRR'
		  SET @SqlWhere = RTRIM(LTRIM(@SqlWhere)) + ' AND (P.Id_Familia = TAG.PTR 
									  AND TAG.TBL = ' + CAST(@e_Tag_Prod_Familias  AS VARCHAR(10))  + '
									  AND TAG.USR = ' + CAST(@pId_Usuario AS VARCHAR(10)) + '
									 ) ' 
	  END	  
	  ELSE
	  BEGIN
		SET @SqlFrom = 'FROM cbtes_items i,cbtes f,cbtes_aplica a,cbtes r, cbtes_tipos tr, Cbtes_Tipos TF, Clientes C, Productos P,Prod_Familias FP, Vendedores V, Clientes_Tipos CT, Clientes CLFC, Cbtes_Cond_Pago CCP, Cajas_Mov CM, Cajas_Periodos CP, Transacciones TRF, Transacciones TRR'
      END		
  END
  

  SET @Sql = @SqlSelect + ' ' + @SqlInto +' '+ @SqlFrom + ' ' + @SqlWhere 

 EXEC('INSERT INTO #Reporte ' + @sql)


							 
/************************************************************* agrego forma de pago ***************************************************************************************/


  IF @pFlag_FPago = 1
      BEGIN 
		  Select Distinct(Id_Trans_Rec) Into #Cbtes_a_Procesar From #Reporte

		  SELECT VM.Id_Trans, MAX(VM.Renglon_Nro) AS Renglon_Nro
		  INTO #Max_Renglon 
		  FROM Valores_Mov VM , #Cbtes_a_Procesar C WHERE VM.Id_Trans = C.Id_Trans_Rec
		  GROUP BY VM.Id_Trans

		  Select VM.Id_Trans as Id_Trans_Rec,V.Id_Tipo_Val,Sum(V.Importe) AS IMPORTE 
		  INTO #SUMA_PAGOS 
		  from #Cbtes_a_Procesar C, Valores_Mov VM, Valores V, #Max_Renglon MR
		  WHERE C.Id_Trans_Rec = VM.Id_Trans AND V.Id_Valor = VM.ID_VALOR AND VM.Id_Trans = MR.Id_Trans AND VM.Renglon_Nro = MR.Renglon_Nro
		  GROUP BY VM.Id_Trans,V.Id_Tipo_Val

		  CREATE INDEX INDEX_SUMA_PAGOS ON #SUMA_PAGOS (Id_Trans_Rec,Id_Tipo_Val)

		  SELECT D.Id_Trans_Rec,R.Id_Trans_Fac,R.Item_Fac,
			 (SELECT ISNULL(SUM(IMPORTE),0) FROM #SUMA_PAGOS WHERE Id_Trans_Rec = D.Id_Trans_Rec AND D.ID_TIPO_VAL = 'EFE') * (Imp_Prod_CIva/Imp_Total_Rec)  AS EFECTIVO,
			 (SELECT ISNULL(SUM(IMPORTE),0) FROM #SUMA_PAGOS WHERE Id_Trans_Rec = D.Id_Trans_Rec AND D.ID_TIPO_VAL = 'TAR') * (Imp_Prod_CIva/Imp_Total_Rec)  AS TARJETA,
			 (SELECT ISNULL(SUM(IMPORTE),0) FROM #SUMA_PAGOS WHERE Id_Trans_Rec = D.Id_Trans_Rec AND D.ID_TIPO_VAL = 'CH3') * (Imp_Prod_CIva/Imp_Total_Rec)  AS CHEQ_TER,
			 (SELECT ISNULL(SUM(IMPORTE),0) FROM #SUMA_PAGOS WHERE Id_Trans_Rec = D.Id_Trans_Rec AND D.ID_TIPO_VAL = 'CHP') * (Imp_Prod_CIva/Imp_Total_Rec)  AS CHEQ_PROP,
			 (SELECT ISNULL(SUM(IMPORTE),0) FROM #SUMA_PAGOS WHERE Id_Trans_Rec = D.Id_Trans_Rec AND D.ID_TIPO_VAL = 'DB')  * (Imp_Prod_CIva/Imp_Total_Rec)  AS DEBITO,
			 (SELECT ISNULL(SUM(IMPORTE),0) FROM #SUMA_PAGOS WHERE Id_Trans_Rec = D.Id_Trans_Rec AND D.ID_TIPO_VAL = 'FIN') * (Imp_Prod_CIva/Imp_Total_Rec)  AS FINANC,
			 (SELECT ISNULL(SUM(IMPORTE),0) FROM #SUMA_PAGOS WHERE Id_Trans_Rec = D.Id_Trans_Rec AND D.ID_TIPO_VAL NOT IN ('EFE','TAR','CH3','CHP','DB','FIN') ) * (Imp_Prod_CIva/Imp_Total_Rec) AS OTROS  
			 ,Imp_Prod_CIva/Imp_Total_Rec as Coef_Pago
			 INTO #DETALLE_PAGOS
			 FROM #SUMA_PAGOS D, #Reporte R 
			 WHERE D.Id_Trans_Rec = R.Id_Trans_Rec  AND Imp_Total_Rec <> 0 

		UPDATE  #Reporte SET EFECTIVO = D.EFECTIVO, TARJETA = D.TARJETA, CHEQ_TER = D.CHEQ_TER, CHEQ_PROP = D.CHEQ_PROP, DEBITO = D.DEBITO, OTROS = D.OTROS
		         ,Total_Pagos = D.EFECTIVO + D.TARJETA + D.CHEQ_TER + D.CHEQ_PROP + D.DEBITO + D.OTROS
				 ,Dif = D.EFECTIVO + D.TARJETA + D.CHEQ_TER + D.CHEQ_PROP + D.DEBITO + D.OTROS - Imp_Prod_CIva
				 ,Coef_Pago = D.Coef_Pago
  				FROM #DETALLE_PAGOS D, #Reporte R 
				 WHERE D.Id_Trans_Rec = R.Id_Trans_Rec 
				   AND D.Id_Trans_Fac = R.Id_Trans_Fac
				   AND D.Item_Fac = R.Item_Fac

		--UPDATE  #Reporte SET  Aplic = (Importe_Aplicado*(Imp_Prod_CIva/Imp_Total_Rec)) - Total_Pagos  where Imp_Total_Rec <> 0 


		--UPDATE  #Reporte SET  Total_Pagos = R.Total_Pagos + R.Aplic	
		--		 FROM #DETALLE_PAGOS D, #Reporte R 
		--		 WHERE D.Id_Trans_Rec = R.Id_Trans_Rec 
		--		   AND D.Id_Trans_Fac = R.Id_Trans_Fac
		--		   AND D.Item_Fac = R.Item_Fac
			  
	    DROP TABLE  #DETALLE_PAGOS
		DROP TABLE  #SUMA_PAGOS
		DROP TABLE  #Cbtes_a_Procesar
     END



/************************************************************* Liquidacion ***************************************************************************************/
 

  IF @pFlag_Liquidacion = 1
	  BEGIN
	  DECLARE CUR_GENERAR CURSOR FOR

	  SELECT R.Id_Producto,P.Id_Proveedor,O.Razon_Social,O.Cuit,O.Id_Iva,P.Id_Alias_Cta_Cont,O.Id_Cta_Contable_H,P.Porc_Comision_Prov,P.Id_Cta_Banco,SUM(R.Imp_Prod_SIva) 
              FROM #Reporte R, Productos P, Proveedores O		
			  Where R.Id_Producto = P.Id_Producto And P.Id_Proveedor = O.Id_Proveedor
			  And (Isnull(P.Id_Proveedor,0) <> 0
			  Or Isnull(P.Id_Cta_Banco,0) <> 0)
			  Group by R.Id_Producto,P.Id_Proveedor,O.Razon_Social,O.Cuit,O.Id_Iva,P.Id_Alias_Cta_Cont,O.Id_Cta_Contable_H,P.Porc_Comision_Prov,P.Id_Cta_Banco

	  OPEN CUR_GENERAR
	  FETCH NEXT FROM CUR_GENERAR INTO  @Id_Producto,@Id_Proveedor,@Razon_Social,@Cuit,@Id_Iva,@Id_Alias_Cta_Cont,@Id_Cta_Contable_H,@Porc_Comision_Prov,@Id_Cta_Banco,@Importe
	  WHILE @@FETCH_STATUS = 0
	      BEGIN
		         DELETE #Trans
                 IF @Id_Proveedor > 0
				    BEGIN
						 SET @Id_Tipo_Cbte =  (select Valor_String from xParametros where setDatos= 1 and idParametro ='T_Cbte_Deb_Liq_Ter')

						 INSERT INTO #Trans
							 EXEC dbo.CP_Cbtes_Cpra_Generar  @Id_Proveedor,@Razon_Social,@Importe,1,@Fecha,@Id_Tipo_Cbte,'',1,10,100,@Id_Trans OUTPUT
                    END
				   ELSE
				    BEGIN
						 SET @Id_Tipo_Cbte =  (select Valor_String from xParametros where setDatos= 1 and idParametro ='T_Cbte_Dep_Cta_Prom')

						 INSERT INTO #Trans
							 EXEC dbo.CP_Cbtes_Deposito_Generar  @Id_Cta_Banco,@Razon_Social,@Importe,1,@Fecha,@Id_Tipo_Cbte,'',2,10,100,@pId_Periodo_Caja,@Id_Trans OUTPUT

						 SET @Id_Tipo_Cbte =  (select Valor_String from xParametros where setDatos= 1 and idParametro ='T_Cbte_OP_Prom')

						 EXEC dbo.CP_Cbtes_OP_Varia_Generar  @Id_Cta_Banco,@Razon_Social,@Importe,1,@Fecha,@Id_Tipo_Cbte,'',2,10,100,@pId_Periodo_Caja,@Id_Trans OUTPUT

						 SET @Id_Trans = 0
					END

				 If @Id_Trans > 0
				    Begin
						 DECLARE CUR_GENERAR_ITEMS CURSOR FOR

						 SELECT R.Razon_Social,R.Imp_Prod_SIva,R.Id_Cliente_Externo 
								  FROM #Reporte R	
								  Where R.Id_Producto = @Id_Producto

						 OPEN CUR_GENERAR_ITEMS
						 FETCH NEXT FROM CUR_GENERAR_ITEMS INTO  @Razon_Social,@Imp_Prod_SIva,@Id_Cliente_Externo
						 WHILE @@FETCH_STATUS = 0
						 BEGIN
				              SET @Razon_Social = LTRIM(RTRIM(@Id_Cliente_Externo)) +' '+ LTRIM(RTRIM(@Razon_Social))
							  SET @Id_Cliente = 0
							  SET @Id_Cta_Banco = 0
							  EXEC dbo.SP_CbteItem_Agr   @Id_Producto,@Id_Trans,1,@Imp_Prod_SIva,@Fecha,'',@Razon_Social,@ID_Cliente,@Id_Cta_Banco
                     
							  FETCH NEXT FROM CUR_GENERAR_ITEMS INTO  @Razon_Social,@Imp_Prod_SIva,@Id_Cliente_Externo 	
						 END  
						 CLOSE CUR_GENERAR_ITEMS
					     DEALLOCATE CUR_GENERAR_ITEMS
                 END
			 	 IF @Porc_Comision_Prov > 0
				    BEGIN
					    SET @Razon_Social = (SELECT RAZON_SOCIAL FROM PROVEEDORES WHERE ID_PROVEEDOR = @Id_Proveedor)
				        SET @Id_Tipo_Cbte =  (select Valor_String from xParametros where setDatos= 1 and idParametro ='T_Cbte_Cred_Liq_Ter')
				        SET @Importe_Credito = (@Importe * (@Porc_Comision_Prov/100)) * -1
			        	EXEC dbo.CP_Cbtes_Cpra_Generar @Id_Proveedor,@Razon_Social,@Importe_Credito,1,@Fecha,@Id_Tipo_Cbte,'PRUEBA',1,10,100,@Id_Trans OUTPUT
		 		    END

	      FETCH NEXT FROM CUR_GENERAR INTO  @Id_Producto,@Id_Proveedor,@Razon_Social,@Cuit,@Id_Iva,@Id_Alias_Cta_Cont,@Id_Cta_Contable_H,@Porc_Comision_Prov,@Id_Cta_Banco,@Importe
	 END  
	 CLOSE CUR_GENERAR
	 DEALLOCATE CUR_GENERAR 
  
  END

/************************************************************* Pagos a Cuenta ***************************************************************************************/


  IF @Existe_Tag_Prod = 0 AND @Existe_Tag_Fam = 0 AND @pFlag_Filtrar_CC = 0
  BEGIN
		INSERT INTO #Reporte
        SELECT 0 as Imp_Gravado_Item                         --1
							  ,0 as Imp_Final_Item                           --2
							  ,0 as Id_Trans_Fac                             --3
							  ,0 as Importe_Fac                              --4
							  ,0 as Imp_total_Fac                            --5
							  ,r.Id_trans as Id_Trans_Rec                    --6
							  ,r.imp_total as Importe_Aplicado                  --7
							  ,r.Fecha  as Fecha_QA                          --8
							  ,r.Fecha  as Fecha_Venc                        --9
							  ,c.Id_cliente                                  --10
			  			      ,C.Nombre_Fantasia                             --11
							  ,C.Id_Cliente_Externo                          --12
							  ,C.Razon_Social                                --13
							  ,C.Id_Tipo_Cli                                 --14
							  ,'' as Desc_Factura                            --15
							  ,r.Fecha                                            --16
							  ,dbo.CF_Cbte_Descrip(R.id_trans) as Desc_Recibo     --17
							  ,R.Fecha as Fecha_Rec								  --18
							  ,R.Imp_Total as Imp_Total_Rec					      --19
							  ,R.Id_Estado_Cbte									  --20
							  ,R.Cotizacion as Cotizacion_Rec                     --21
							  ,0 as Cotizacion_Fac                                --22
							  ,0 as Porc_Bonificacion							  --23
							  ,r.Id_Cobrador								      --24
							  ,'' as Id_Producto								  --25
							  ,'PAGO A CUENTA' AS descripcion_Detallada		      --26
							  ,-1 as Id_Familia									  --27
							  ,'Pagos A Cuenta' as Descripcion_Familia             --28
							  ,0 as porc_aplicado                                 --29
							  ,r.saldo  as Imp_Prod_CIva                          --30
							  ,r.saldo  as Imp_Prod_SIva                          --31
							  ,V.DESCRIPCION AS Nombre_Cobrador                   --32
							  ,ISNULL(dbo.SF_Cli_Categ_Mes(r.Id_cliente, r.Fecha),0) as Id_Tipo_Cli_Ori --33
							  ,CT.Descripcion AS Descripcion_Tipo_Cli		                                   --34
							  ,isnull(c.id_cliente_ref,0) as id_cliente_ref                                 --35
							  ,cast(0 as decimal(12,2)) AS EFECTIVO                                         --36
							  ,cast(0 as decimal(12,2)) AS TARJETA                                          --37
							  ,cast(0 as decimal(12,2)) AS CHEQ_TER                                         --38
							  ,cast(0 as decimal(12,2)) AS CHEQ_PROP                                        --39
							  ,cast(0 as decimal(12,2)) AS DEBITO                                           --40
							  ,cast(0 as decimal(12,2)) AS FINANC                                           --41
							  ,cast(0 as decimal(12,2)) AS OTROS   
							  ,cast(0 as decimal(12,2)) AS APLIC                                            --42
							  ,cast(0 as decimal(12,2)) AS Total_Pagos                                      --43
							  ,cast(0 as decimal(12,2)) AS Dif                                              --44
							  ,1 as Item_Fac															    --45
							  ,cast(0 as decimal(12,5)) AS Coef_Pago										--46  
							  ,ISNULL(C.Id_Cond_Pago, 0) AS Id_Cond_Pago									--47 14/01/2020 Agregado Ticket 18.924
							  ,ISNULL(CCP.Descripcion, '''') AS Cond_Pago									--48 14/01/2020 Agregado Ticket 18.924
							  ,0 AS Imp_Total_Percep_Reten_Fac												--49 14/01/2020 Agregado Ticket 18.924
							  ,r.Imp_Percep AS Imp_Total_Percep_Reten_Rec									--50 14/01/2020 Agregado Ticket 18.924
							,''							AS FC_CL_Nombre_Fantasia							--51 27/01/2021
							,''							AS FC_CL_Id_Cliente_Externo							--52 27/01/2021
							,''							AS FC_CL_Razon_Social								--53 27/01/2021
							,0							AS FC_CL_Id_Tipo_Cli								--54 27/01/2021
							,C.Id_Cobrador				AS CL_Id_Cobrador									--55 04/02/2021
							,CP.Id_Caja					AS Id_Caja											--56 04/02/2021
							,0							AS Porc_Bonif_Item									--57 13/04/2021 Agregado Ticket 22.253
							,0							AS Imp_Final_Item_Sin_Desc							--58 13/04/2021 Agregado Ticket 22.253
							,0							AS Imp_Bonif_Item									--59 13/04/2021 Agregado Ticket 22.253
							,0 	
			                ,0 AS Alias
	                        ,'' AS Id_Centro_Costo 
							,''							AS Fac_Fecha_Mov									--64 26/01/2022 Agregado Ticket 535
							,CONVERT(VARCHAR(20), TRR.Fecha_Mov, 120) AS Rec_Fecha_Mov						--65 26/01/2022 Agregado Ticket 535
													
		   FROM cbtes r, cbtes_tipos tr,  Clientes C, Vendedores V, Clientes_Tipos CT, Cbtes_Cond_Pago CCP, Cajas_Mov CM, Cajas_Periodos CP, Transacciones TRR
		   WHERE r.id_tipo_cbte = tr.id_tipo_cbte 
		   AND TRR.Id_Trans = R.Id_Trans
		   and tr.Compromete_Recibos in (1,-1)
		   AND C.ID_CLIENTE = R.ID_CLIENTE
		   AND R.ID_ESTADO_CBTE IN (1,4,2)
		  -- AND R.SALDO <> 0
		   AND r.Id_Cobrador = v.id_vendedor
		   AND CM.Id_Trans = R.Id_Trans
		   AND CM.Id_Periodo_Caja = CP.Id_Periodo_Caja
		   AND ISNULL(CT.ID_TIPO_CLI,0) = ISNULL(dbo.SF_Cli_Categ_Mes(r.Id_cliente, r.Fecha),0)
		   AND R.Fecha >= @pFechaDesde
		   AND r.Fecha <  @pFechaHasta+1 
		   AND ISNULL(C.Id_Cond_Pago, 0) = ISNULL(CCP.Id_Cond_Pago, 0)
		   and r.id_Trans not in (select id_Trans from cbtes_items)
	END

/***********************************************************Incluir lo pendiente de cobro facturado en el mismo periodo*****************************************************************************************/
IF @pFlag_Inc_Fact_Periodo = 1 AND UPPER(@pFormato) = 'D'
BEGIN
   SET @SqlSelect =  ' SELECT (i.Imp_Gravado * F.Cotizacion) * (1 -(F.Porc_Bonificacion / 100)) * (TF.Compromete_Factura) as Imp_Gravado_Item            --1
							  ,(i.Imp_Final * F.Cotizacion) * (1 -(F.Porc_Bonificacion / 100))  * (TF.Compromete_Factura) as Imp_Final_Item               --2
							  ,f.Id_trans as Id_Trans_Fac                                   --3
							  ,(f.Imp_Gravado + f.Imp_Gravado2 + f.Imp_Gravado3 + f.Imp_Exento) * F.Cotizacion  * (TF.Compromete_Factura) as Importe_Fac --4
							  ,f.Imp_total * F.Cotizacion  * (TF.Compromete_Factura) as Imp_total_Fac   --5
							  ,0 as Id_Trans_Rec                                 --6
							  ,0  as Importe_Aplicado                            --7
							  ,i.Fecha_QA                                    --8
							  ,i.Fecha_Venc                                  --9
							  ,CASE
								WHEN i.Id_Cliente IS NULL THEN F.Id_Cliente  --10
								WHEN i.Id_Cliente = 0 THEN F.Id_Cliente
								ELSE i.Id_Cliente
								END AS Id_Cliente                             
							  ,C.Nombre_Fantasia                             --11
							  ,C.Id_Cliente_Externo                          --12
							  ,C.Razon_Social                                --13
							  ,C.Id_Tipo_Cli                                 --14
							  ,dbo.CF_Cbte_Descrip(f.id_trans) as Desc_Factura    --15
							  ,F.Fecha                                            --16
							  ,'''' as Desc_Recibo                                  --17
							  ,NULL  as Fecha_Rec							    	  --18
							  ,0 as Imp_Total_Rec					              --19
							  ,f.Id_Estado_Cbte									  --20
							  ,0 as Cotizacion_Rec                                --21
							  ,F.Cotizacion as Cotizacion_Fac                     --22
							  ,F.Porc_Bonificacion								  --23
							  ,F.Id_Cobrador								      --24
							  ,I.Id_Producto									  --25
							  ,P.Descripcion_Detallada						      --26
							  ,P.Id_Familia										  --27
							  ,FP.Descripcion as Descripcion_Familia              --28
							  ,0 as porc_aplicado                               --29
							  ,0 as Imp_Prod_CIva --30
							  ,0 as Imp_Prod_SIva --31
							  ,ISNULL(V.DESCRIPCION,'''') AS Nombre_Cobrador                                               --32
							  ,dbo.SF_Cli_Categ_Mes(i.Id_cliente, isnull(i.Fecha_QA,F.Fecha)) as Id_Tipo_Cli_Ori --33
							  ,CT.Descripcion AS Descripcion_Tipo_Cli		                                                                 --34
							  ,isnull(c.id_cliente_ref,0) as id_cliente_ref                                   --35
							  ,cast(0 as decimal(12,2)) AS EFECTIVO                                           --36
							  ,cast(0 as decimal(12,2)) AS TARJETA                                            --37
							  ,cast(0 as decimal(12,2)) AS CHEQ_TER                                           --38
							  ,cast(0 as decimal(12,2)) AS CHEQ_PROP                                          --39
							  ,cast(0 as decimal(12,2)) AS DEBITO                                             --40
							  ,cast(0 as decimal(12,2)) AS FINANC                                             --41
							  ,cast(0 as decimal(12,2)) AS OTROS    
							  ,cast(0 as decimal(12,2)) AS Aplic                                              --42
							  ,cast(0 as decimal(12,2)) AS Total_Pagos                                        --43
							  ,cast(0 as decimal(12,2)) AS Dif                                                --44
							  ,i.Item as Item_Fac															  --45
							  ,cast(0 as decimal(12,5)) AS Coef_Pago										  --46 
							  ,0 AS Id_Cond_Pago										                    --47 14/01/2020 Agregado Ticket 18.924
							  ,ISNULL(CCP.Descripcion, '''') AS Cond_Pago									--48 14/01/2020 Agregado Ticket 18.924
							  ,F.IMP_PERCEP AS Imp_Total_Percep_Reten_Fac		  						    --49 14/01/2020 Agregado Ticket 18.924
							  ,0 AS Imp_Total_Percep_Reten_Rec										        --50 14/01/2020 Agregado Ticket 18.924
							  ,CLFC.Nombre_Fantasia		AS FC_CL_Nombre_Fantasia							--51 27/01/2021
							  ,CLFC.Id_Cliente_Externo	AS FC_CL_Id_Cliente_Externo							--52 27/01/2021
							  ,CLFC.Razon_Social		AS FC_CL_Razon_Social								--53 27/01/2021
							  ,CLFC.Id_Tipo_Cli			AS FC_CL_Id_Tipo_Cli								--54 27/01/2021
							  ,C.Id_Cobrador			AS CL_Id_Cobrador									--55 04/02/2021
							  ,0						AS Id_Caja											--56 04/02/2021
							  ,I.Bonif					AS Porc_Bonif_Item									--57 13/04/2021 Agregado Ticket 22.253
							  ,((100 * I.Imp_Final) / (100-I.Bonif)) AS Imp_Final_Item_Sin_Desc				--58 13/04/2021 Agregado Ticket 22.253
							  ,(((100 * I.Imp_Final) / (100-I.Bonif)) * (I.Bonif / 100)) AS Imp_Bonif_Item	--59 13/04/2021 Agregado Ticket 22.253
							  ,isnull((select SUM(Cantidad) from cbtes_items where id_trans=f.Id_trans  and imp_final>0 and Id_Producto=i.Id_Producto),0) AS Cantidad_Prod
			                  ,P.Id_Alias_Cta_Cont AS Alias
	                          ,P.Id_Centro_Costo
							  ,CONVERT(VARCHAR(20), TRF.Fecha_Mov, 120)	AS Fac_Fecha_Mov					--64 26/01/2022 Agregado Ticket 535
							  ,''''										AS Rec_Fecha_Mov					--65 26/01/2022 Agregado Ticket 535
'
  SET @SqlWhere = 'WHERE  
					f.id_cliente = c.id_Cliente
					AND TRF.Id_Trans = F.Id_Trans
					AND ISNULL(F.Id_Cliente, 0) = CLFC.Id_Cliente
					AND f.id_estado_cbte in (1,2,4) 
					AND i.id_trans = f.id_trans 
					AND f.id_tipo_cbte = TF.id_tipo_cbte 
					AND I.Id_Producto = P.Id_Producto
					AND ISNULL(P.Id_Familia,0) = ISNULL(FP.Id_Familia,0)
					AND f.Fecha >= ''' + CONVERT(VARCHAR(8),@pFechaDesde   ,112) + '''
					AND f.Fecha <  ''' + CONVERT(VARCHAR(8),@pFechaHasta+1 ,112) + '''
					AND F.Imp_total <> 0
					AND ISNULL(F.Id_Cond_Pago, 0) = ISNULL(CCP.Id_Cond_Pago, 0)
					AND TF.Compromete_Factura in (1,-1)
					AND ISNULL(F.Id_Cobrador,'''') = ISNULL(V.Id_Vendedor,'''')
 					AND ISNULL(CT.ID_TIPO_CLI,0) = ISNULL(dbo.SF_Cli_Categ_Mes(i.Id_cliente, isnull(i.Fecha_QA,F.Fecha)),0)
					AND i.Imp_Gravado <> 0 '

  
  IF @Existe_Tag_Prod > 0
  BEGIN
	  SET @SqlFrom = 'FROM cbtes_items i,cbtes f, Cbtes_Tipos TF, Clientes C,TagFilePos_ TAG, Productos P,Prod_Familias FP, Vendedores V, Clientes_Tipos CT, Clientes CLFC, Cbtes_Cond_Pago CCP, Transacciones TRF'
      SET @SqlWhere = RTRIM(LTRIM(@SqlWhere)) + ' AND (i.id_producto = TAG.POS 
							      AND TAG.TBL = ' + CAST(@e_Tag_Prod  AS VARCHAR(10))  + '
								  AND TAG.USR = ' + CAST(@pId_Usuario AS VARCHAR(10)) + '
								 ) ' 
  END
  ELSE
  BEGIN
      IF @Existe_Tag_Fam > 0
	  BEGIN
		  SET @SqlFrom = 'FROM cbtes_items i,cbtes f, Cbtes_Tipos TF, Clientes C,TagFile_ TAG, Productos P,Prod_Familias FP, Vendedores V, Clientes_Tipos CT, Clientes CLFC, Cbtes_Cond_Pago CCP, Transacciones TRF'
		  SET @SqlWhere = RTRIM(LTRIM(@SqlWhere)) + ' AND (P.Id_Familia = TAG.PTR 
									  AND TAG.TBL = ' + CAST(@e_Tag_Prod_Familias  AS VARCHAR(10))  + '
									  AND TAG.USR = ' + CAST(@pId_Usuario AS VARCHAR(10)) + '
									 ) ' 
	  END	  
	  ELSE
	  BEGIN
		SET @SqlFrom = 'FROM cbtes_items i,cbtes f, Cbtes_Tipos TF, Clientes C, Productos P,Prod_Familias FP, Vendedores V, Clientes_Tipos CT, Clientes CLFC, Cbtes_Cond_Pago CCP, Transacciones TRF, Transacciones TRR'
      END		
  END  


  SET @Sql = @SqlSelect + ' ' + @SqlInto +' '+ @SqlFrom + ' ' + @SqlWhere 
  --print @Sql
  EXEC('INSERT INTO #Reporte ' + @sql)

END

/************************************************************* Ventas de Contado ***************************************************************************************/


   SET @SqlSelect =  ' SELECT (i.Imp_Gravado * F.Cotizacion) * (1 -(F.Porc_Bonificacion / 100)) * (TF.Compromete_Factura) as Imp_Gravado_Item            --1
							  ,(i.Imp_Final * F.Cotizacion) * (1 -(F.Porc_Bonificacion / 100))  * (TF.Compromete_Factura) as Imp_Final_Item               --2
							  ,f.Id_trans as Id_Trans_Fac                                                    --3
							  ,(f.Imp_Gravado + f.Imp_Gravado2 + f.Imp_Gravado3 + f.Imp_Exento) * F.Cotizacion   * (TF.Compromete_Factura) as Importe_Fac --4
							  ,f.Imp_total * F.Cotizacion  * (TF.Compromete_Factura) as Imp_total_Fac         --5
							  ,f.Id_trans  as Id_Trans_Rec                                                    --6
							  ,f.Imp_total * F.Cotizacion  * (TF.Compromete_Factura)  as Importe_Aplicado     --7
							  ,i.Fecha_QA                                    --8
							  ,i.Fecha_Venc                                  --9
							  ,CASE
								WHEN i.Id_Cliente IS NULL THEN F.Id_Cliente  --10
								WHEN i.Id_Cliente = 0 THEN F.Id_Cliente
								ELSE i.Id_Cliente
								END AS Id_Cliente
							  ,C.Nombre_Fantasia                             --11
							  ,C.Id_Cliente_Externo                          --12
							  ,C.Razon_Social                                --13
							  ,C.Id_Tipo_Cli                                 --14
							  ,dbo.CF_Cbte_Descrip(f.id_trans) as Desc_Factura    --15
							  ,F.Fecha                                            --16
							  ,''TICKET CONTADO'' as Desc_Recibo                  --17
							  ,F.Fecha  as Fecha_Rec						      --18
							  ,f.Imp_total * F.Cotizacion  * (TF.Compromete_Factura) as Imp_Total_Rec	 --19
							  ,f.Id_Estado_Cbte									  --20
							  ,F.Cotizacion as Cotizacion_Rec                     --21
							  ,F.Cotizacion as Cotizacion_Fac                     --22
							  ,F.Porc_Bonificacion								  --23
							  ,F.Id_Cobrador								      --24
							  ,I.Id_Producto									  --25
							  ,P.Descripcion_Detallada						      --26
							  ,P.Id_Familia										  --27
							  ,FP.Descripcion as Descripcion_Familia              --28
							  ,100 as porc_aplicado                               --29
							  ,(i.Imp_Final *  F.Cotizacion )  * (1 -(F.Porc_Bonificacion / 100)) as Imp_Prod_CIva --30
							  ,(i.Imp_Gravado * F.Cotizacion ) * (1 -(F.Porc_Bonificacion / 100)) as Imp_Prod_SIva --31
							  ,ISNULL(V.DESCRIPCION,'''') AS Nombre_Cobrador                                               --32
							  ,dbo.SF_Cli_Categ_Mes(i.Id_cliente, isnull(i.Fecha_QA,F.Fecha)) as Id_Tipo_Cli_Ori --33
							  ,CT.Descripcion AS Descripcion_Tipo_Cli		                                                                 --34
							  ,isnull(c.id_cliente_ref,0) as id_cliente_ref                                   --35
							  ,cast(0 as decimal(12,2)) AS EFECTIVO                                           --36
							  ,cast(0 as decimal(12,2)) AS TARJETA                                            --37
							  ,cast(0 as decimal(12,2)) AS CHEQ_TER                                           --38
							  ,cast(0 as decimal(12,2)) AS CHEQ_PROP                                          --39
							  ,cast(0 as decimal(12,2)) AS DEBITO                                             --40
							  ,cast(0 as decimal(12,2)) AS FINANC                                             --41
							  ,cast(0 as decimal(12,2)) AS OTROS  
							  ,cast(0 as decimal(12,2)) AS APLIC                                           --42
							  ,cast(0 as decimal(12,2)) AS Total_Pagos                                        --43
							  ,cast(0 as decimal(12,2)) AS Dif                                                --44
							  ,i.Item as Item_Fac															  --45
							  ,cast(0 as decimal(12,5)) AS Coef_Pago										  --46 
							  ,F.Id_Cond_Pago AS Id_Cond_Pago										          --47 14/01/2020 Agregado Ticket 18.924
							  ,ISNULL(CCP.Descripcion, '''') AS Cond_Pago									  --48 14/01/2020 Agregado Ticket 18.924
							  ,F.IMP_PERCEP AS Imp_Total_Percep_Reten_Fac		  						      --49 14/01/2020 Agregado Ticket 18.924
							  ,0 AS Imp_Total_Percep_Reten_Rec										          --50 14/01/2020 Agregado Ticket 18.924
							  ,CLFC.Nombre_Fantasia		AS FC_CL_Nombre_Fantasia							  --51 27/01/2021
							  ,CLFC.Id_Cliente_Externo	AS FC_CL_Id_Cliente_Externo							  --52 27/01/2021
							  ,CLFC.Razon_Social		AS FC_CL_Razon_Social								  --53 27/01/2021
							  ,CLFC.Id_Tipo_Cli			AS FC_CL_Id_Tipo_Cli								  --54 27/01/2021
							  ,C.Id_Cobrador			AS CL_Id_Cobrador								      --55 04/02/2021
							  ,0						AS Id_Caja											  --56 04/02/2021
							  ,I.Bonif					AS Porc_Bonif_Item									  --57 13/04/2021 Agregado Ticket 22.253
							  ,((100 * I.Imp_Final) / (100-I.Bonif)) AS Imp_Final_Item_Sin_Desc				  --58 13/04/2021 Agregado Ticket 22.253
							  ,(((100 * I.Imp_Final) / (100-I.Bonif)) * (I.Bonif / 100)) AS Imp_Bonif_Item	  --59 13/04/2021 Agregado Ticket 22.253
							  ,isnull((select SUM(Cantidad) from cbtes_items where id_trans=f.Id_trans  and imp_final>0 and Id_Producto=i.Id_Producto),0) AS Cantidad_Prod
			                  ,P.Id_Alias_Cta_Cont AS Alias
	                          ,P.Id_Centro_Costo 
							  ,CONVERT(VARCHAR(20), TRF.Fecha_Mov, 120)	AS Fac_Fecha_Mov					  --64 26/01/2022 Agregado Ticket 535
							  ,CONVERT(VARCHAR(20), TRR.Fecha_Mov, 120)	AS Rec_Fecha_Mov					  --65 26/01/2022 Agregado Ticket 535
'
  SET @SqlWhere = 'WHERE  f.id_cliente = c.id_Cliente
							AND TRF.Id_Trans = F.Id_Trans
							AND TRR.Id_Trans = F.Id_Trans
							AND ISNULL(F.Id_Cliente, 0) = CLFC.Id_Cliente
							AND f.id_estado_cbte in (1,2,4) 
							AND i.id_trans = f.id_trans 
							AND f.id_tipo_cbte = TF.id_tipo_cbte 
							AND I.Id_Producto = P.Id_Producto
							AND ISNULL(P.Id_Familia,0) = ISNULL(FP.Id_Familia,0)
							AND f.Fecha >= ''' + CONVERT(VARCHAR(8),@pFechaDesde   ,112) + '''
							AND f.Fecha <  ''' + CONVERT(VARCHAR(8),@pFechaHasta+1 ,112) + '''
							AND F.Imp_total <> 0
							AND ISNULL(F.Id_Cond_Pago, 0) = ISNULL(CCP.Id_Cond_Pago, 0)
							AND TF.Compromete_Factura in (1,-1)
							AND F.ID_COND_PAGO = 1 
							AND ISNULL(F.Id_Cobrador,'''') = ISNULL(V.Id_Vendedor,'''')
 							AND ISNULL(CT.ID_TIPO_CLI,0) = ISNULL(dbo.SF_Cli_Categ_Mes(i.Id_cliente, isnull(i.Fecha_QA,F.Fecha)),0)
							AND i.Imp_Gravado <> 0 '

  IF (@pFlag_Filtrar_CC = 1)
		SET @SqlWhere = LTRIM(RTRIM(@SqlWhere)) + ' AND ISNULL(P.Id_Centro_Costo,'''') = ''' + @pId_Centro_Costo + ''''
		 
  
  IF @Existe_Tag_Prod > 0
  BEGIN
	  SET @SqlFrom = 'FROM cbtes_items i,cbtes f, Cbtes_Tipos TF, Clientes C,TagFilePos_ TAG, Productos P,Prod_Familias FP, Vendedores V, Clientes_Tipos CT, Clientes CLFC, Cbtes_Cond_Pago CCP, Transacciones TRF, Transacciones TRR'
      SET @SqlWhere = RTRIM(LTRIM(@SqlWhere)) + ' AND (i.id_producto = TAG.POS 
							      AND TAG.TBL = ' + CAST(@e_Tag_Prod  AS VARCHAR(10))  + '
								  AND TAG.USR = ' + CAST(@pId_Usuario AS VARCHAR(10)) + '
								 ) ' 
  END
  ELSE
  BEGIN
      IF @Existe_Tag_Fam > 0
	  BEGIN
		  SET @SqlFrom = 'FROM cbtes_items i,cbtes f, Cbtes_Tipos TF, Clientes C,TagFile_ TAG, Productos P,Prod_Familias FP, Vendedores V, Clientes_Tipos CT, Clientes CLFC, Cbtes_Cond_Pago CCP, Transacciones TRF, Transacciones TRR'
		  SET @SqlWhere = RTRIM(LTRIM(@SqlWhere)) + ' AND (P.Id_Familia = TAG.PTR 
									  AND TAG.TBL = ' + CAST(@e_Tag_Prod_Familias  AS VARCHAR(10))  + '
									  AND TAG.USR = ' + CAST(@pId_Usuario AS VARCHAR(10)) + '
									 ) ' 
	  END	  
	  ELSE
	  BEGIN
		SET @SqlFrom = 'FROM cbtes_items i,cbtes f, Cbtes_Tipos TF, Clientes C, Productos P,Prod_Familias FP, Vendedores V, Clientes_Tipos CT, Clientes CLFC, Cbtes_Cond_Pago CCP, Transacciones TRF, Transacciones TRR'
      END		
  END    
 
  SET @Sql = @SqlSelect + ' ' + @SqlInto +' '+ @SqlFrom + ' ' + @SqlWhere 
  --print @Sql
  EXEC('INSERT INTO #Reporte ' + @sql)
  
  SELECT @Total = SUM(ISNULL(Imp_Prod_CIva,0)) FROM #Reporte


  /************************************************************* Resultado ***************************************************************************************/
 
  UPDATE #Reporte SET Id_Centro_Costo = '' WHERE Id_Centro_Costo IS NULL 
  
  IF UPPER(@pFormato) = 'D' OR @pFormato = ''
	 SELECT R.*,P.Descripcion AS Cta, P.Nro_Cta_Contable, CC.Descripcion AS Centro_Costo 
			FROM #Reporte R LEFT JOIN Pla_Ctas_Cuentas_Contables P ON P.Id_Cta_Contable = dbo.CF_Cta_Cont(R.Alias,R.Fecha_Rec) , Centro_Costos CC
			WHERE R.Id_Centro_Costo = CC.Id_Centro_Costo 

  IF UPPER(@pFormato) = 'P'
     SELECT Id_Producto, Descripcion_Detallada, SUM(Imp_Prod_CIva) AS Imp_Prod_CIva,
	        CONVERT(DECIMAL(8,2),SUM(Imp_Prod_CIva) / @Total * 100) AS Participacion
			FROM #Reporte 
			GROUP BY Id_Producto, Descripcion_Detallada
			ORDER BY Descripcion_Detallada

  IF UPPER(@pFormato) = 'F'
     SELECT Id_Familia, Descripcion_Familia, SUM(Imp_Prod_CIva) AS Imp_Prod_CIva,
	        CONVERT(DECIMAL(8,2),SUM(Imp_Prod_CIva) / @Total * 100) AS Participacion
			FROM #Reporte 
			GROUP BY Id_Familia, Descripcion_Familia
			ORDER BY Descripcion_Familia


  IF UPPER(@pFormato) = 'CF'
	 SELECT R.Id_Familia, R.Descripcion_Familia,R.Id_Cobrador,R.Nombre_Cobrador , SUM(Imp_Prod_CIva) AS Imp_Prod_CIva, 
	        CONVERT(DECIMAL(8,2),SUM(Imp_Prod_CIva) / @Total * 100) AS Participacion
			FROM #Reporte R 
			GROUP BY R.Id_Familia, R.Descripcion_Familia,R.Id_Cobrador,R.Nombre_Cobrador
			ORDER BY R.Nombre_Cobrador,R.Descripcion_Familia

  IF UPPER(@pFormato) = 'CFM'
	 SELECT R.Id_Familia, R.Descripcion_Familia,R.Id_Cobrador,R.Nombre_Cobrador , 
			DATEPART(mm,R.Fecha_QA) AS Mes,DATEPART(YY,R.Fecha_QA) AS Anio,
			SUM(Imp_Prod_CIva) AS Imp_Prod_CIva, 
	        CONVERT(DECIMAL(8,2),SUM(Imp_Prod_CIva) / @Total * 100) AS Participacion
			FROM #Reporte R 
			GROUP BY R.Id_Familia, R.Descripcion_Familia,R.Id_Cobrador,R.Nombre_Cobrador,DATEPART(mm,R.Fecha_QA),DATEPART(YY,R.Fecha_QA)
			ORDER BY R.Nombre_Cobrador,R.Id_Cobrador,R.Descripcion_Familia,DATEPART(YY,R.Fecha_QA),DATEPART(mm,R.Fecha_QA)


  IF UPPER(@pFormato) = 'T'
	 SELECT Id_Tipo_Cli_Ori, Descripcion_Tipo_Cli , SUM(Imp_Prod_CIva) AS Imp_Prod_CIva, 
	        CONVERT(DECIMAL(8,2),SUM(Imp_Prod_CIva) / @Total * 100) AS Participacion
			FROM #Reporte  
			GROUP BY Id_Tipo_Cli_Ori, Descripcion_Tipo_Cli
			ORDER BY Descripcion_Tipo_Cli

  IF UPPER(@pFormato) = 'FP'
	 SELECT Fecha_Rec,sum(Efectivo) as Efectivo,sum(Tarjeta) as Tarjeta ,sum(Cheq_ter) as Cheq_ter,sum(CHEQ_PROP) as Cheq_Prop ,sum(DEBITO) as Debito ,sum(FINANC) as Financ ,sum(OTROS) as Otros, sum(APLIC) as Aplic, sum(Total_Pagos) as Total_Pagos
	 			FROM #Reporte  
			GROUP BY Fecha_Rec
			ORDER BY Fecha_Rec


  IF UPPER(@pFormato) = 'CTA'
     BEGIN


		 SELECT R.Alias,C.Nro_Cta_Contable,C.Descripcion ,R.Id_Centro_Costo,CC.Descripcion AS Centro_Costo,SUM(Imp_Prod_CIva) AS Imp_Prod_CIva, 
				CONVERT(DECIMAL(8,2),SUM(Imp_Prod_CIva) / @Total * 100) AS Participacion 
				FROM #Reporte R, Pla_Ctas_Cuentas_Contables C, Centro_Costos CC
				WHERE  C.Id_Cta_Contable = dbo.CF_Cta_Cont(R.Alias,R.Fecha_Rec)
				AND R.Id_Centro_Costo = CC.Id_Centro_Costo
				GROUP BY R.Alias,C.Nro_Cta_Contable,C.Descripcion ,R.Id_Centro_Costo,CC.Descripcion
				ORDER BY C.Nro_Cta_Contable
         

     END


  IF UPPER(@pFormato) = 'CC'
     BEGIN


		 SELECT R.Id_Centro_Costo,CC.Descripcion AS Centro_Costo,SUM(Imp_Prod_CIva) AS Imp_Prod_CIva, 
				CONVERT(DECIMAL(8,2),SUM(Imp_Prod_CIva) / @Total * 100) AS Participacion 
				FROM #Reporte R, Pla_Ctas_Cuentas_Contables C, Centro_Costos CC
				WHERE  C.Id_Cta_Contable = dbo.CF_Cta_Cont(R.Alias,R.Fecha_Rec)
				AND R.Id_Centro_Costo = CC.Id_Centro_Costo
				GROUP BY R.Id_Centro_Costo,CC.Descripcion
				ORDER BY CC.Descripcion
         

     END


	IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] = '#Reporte') 
		DROP TABLE #Reporte

		



/*
EXEC dbo.CPJ_Productos_Cobros   '{
			"Id_Usuario":"-1",
			"Equate":"90",
			"FechaDesde":"20211101",
			"FechaHasta":"20211103",
			"Flag_Liquidacion":"0",
			"Id_Periodo_Caja":"0",
			"Flag_FPago":"1",
			"Tipo_Cob":"",
			"Formato":"D",
			"Flag_Filtrar_Fam":"1",
			"Flag_Inc_Fact_Periodo":"0"
		}'

EXEC dbo.CPJ_Productos_Cobros   '{
			"Id_Usuario":"-1",
			"Equate":"90",
			"FechaDesde":"20211101",
			"FechaHasta":"20211103",
			"Flag_Liquidacion":"0",
			"Id_Periodo_Caja":"0",
			"Flag_FPago":"1",
			"Tipo_Cob":"",
			"Formato":"FP",
			"Flag_Filtrar_Fam":"1",
			"Flag_Inc_Fact_Periodo":"0"
		}'

EXEC dbo.CPJ_Productos_Cobros   '{
			"Id_Usuario":"-1",
			"Equate":"90",
			"FechaDesde":"20210405",
			"FechaHasta":"20210405",
			"Flag_Liquidacion":"0",
			"Id_Periodo_Caja":"0",
			"Flag_FPago":"0",
			"Tipo_Cob":"",
			"Formato":"CF",
			"Flag_Filtrar_Fam":"1",
			"Flag_Inc_Fact_Periodo":"0"
		}'


EXEC dbo.CPJ_Productos_Cobros   '{
			"Id_Usuario":"-1", 
			"Equate":"90",
			"FechaDesde":"20210405",
			"FechaHasta":"20210405",
			"Flag_Liquidacion":"0",
			"Id_Periodo_Caja":"0",
			"Flag_FPago":"0",
			"Tipo_Cob":"",
			"Formato":"CFM",
			"Flag_Filtrar_Fam":"1",
			"Flag_Inc_Fact_Periodo":"0"
		}'

EXEC dbo.CPJ_Productos_Cobros   '{
			"Id_Usuario":"-1",
			"Equate":"90",
			"FechaDesde":"20210405",
			"FechaHasta":"20210405",
			"Flag_Liquidacion":"0",
			"Id_Periodo_Caja":"0",
			"Flag_FPago":"0",
			"Tipo_Cob":"",
			"Formato":"P",
			"Flag_Filtrar_Fam":"1",
			"Flag_Inc_Fact_Periodo":"0"
		}'

EXEC dbo.CPJ_Productos_Cobros   '{
			"Id_Usuario":"-1",
			"Equate":"90",
			"FechaDesde":"20210405",
			"FechaHasta":"20210405",
			"Flag_Liquidacion":"0",
			"Id_Periodo_Caja":"0",
			"Flag_FPago":"0",
			"Tipo_Cob":"",
			"Formato":"T",
			"Flag_Filtrar_Fam":"1",
			"Flag_Inc_Fact_Periodo":"0"
		}'



EXEC dbo.CPJ_Productos_Cobros   '{
			"Id_Usuario":"-1",
			"Equate":"90",
			"FechaDesde":"20210401",
			"FechaHasta":"20210430",
			"Flag_Liquidacion":"0",
			"Id_Periodo_Caja":"0",
			"Flag_FPago":"0",
			"Tipo_Cob":"",
			"Formato":"CC",
			"Flag_Filtrar_Fam":"0",
			"Flag_Inc_Fact_Periodo":"0"
		}'


SELECT * FROM TRANSACCIONES ORDER BY ID_TRANS DESC

*/

