IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'dbo.CPJ_Productos_Cobros') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE dbo.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),
            @pFlag_Filtrar_Caja		TINYINT		


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')), '')
SET @pFlag_Filtrar_Caja			= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJsonRecibido,'Flag_Filtrar_Caja')), 0)


  CREATE TABLE #Trans (Id_Trans INT)

  CREATE TABLE #Coef_Pagos (Id_Trans Int
	,Imp_Total_Rec decimal(14,2)
	,Imp_Valores decimal(14,2)
	,Imp_PAGOS_APLI decimal(14,2)
	,Imp_Efe decimal(14,2)
	,Imp_Tar decimal(14,2)
	,Imp_CH3 decimal(14,2) 
	,Imp_CHP decimal(14,2)
	,Imp_DB decimal(14,2)
	,Imp_Fin decimal(14,2)
	,Imp_Aju decimal(14,2)
	,Coef_PAGOS_APLI decimal(14,5)
	,Coef_Efe decimal(14,5)
	,Coef_Tar decimal(14,5)
	,Coef_CH3 decimal(14,5) 
	,Coef_CHP decimal(14,5)
	,Coef_DB decimal(14,5)
	,Coef_Fin decimal(14,5)
	,Coef_Aju decimal(14,5)
	)



  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
		  ,@Existe_Tag_Caja  		INT
		  ,@e_Tag_Prod				SMALLINT
		  ,@e_Tag_Prod_Familias		SMALLINT
		  ,@e_Tag_Prod_Cajas		SMALLINT

   DECLARE @Id_Trans_Rec INT 
		  ,@Id_Tipo_Val CHAR(3) 
		  ,@IMPORTE_Val DECIMAL(14,2)
		  ,@EXISTE SMALLINT
		  ,@Imp_Total_Rec DECIMAL(14,2)


  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 
 
  SET @e_Tag_Prod_Cajas = 250
  SET @Existe_Tag_Caja = 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)
 
  IF @pFlag_Filtrar_Caja = 1
	 SET @Existe_Tag_Caja = ISNULL( (SELECT COUNT(*) FROM TagFile_ TAG WHERE TAG.TBL = @e_Tag_Prod_Cajas AND TAG.USR = @pId_Usuario) ,0)


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

  SELECT  cast((i.Imp_Gravado * F.Cotizacion) as decimal(14,2)) as Imp_Gravado_Item													--1
		,cast((i.Imp_Final * F.Cotizacion) as decimal(14,2)) as Imp_Final_Item														--2
		,f.Id_trans as Id_Trans_Fac																			--3
		,cast((f.Imp_Gravado + f.Imp_Gravado2 + f.Imp_Gravado3 + f.Imp_Exento) * F.Cotizacion as decimal(14,2)) as Importe_Fac		--4
		,cast(f.Imp_total * F.Cotizacion  as decimal(14,2)) as Imp_total_Fac														--5
		,r.Id_trans as Id_Trans_Rec																			--6
		,cast(a.Importe as decimal(14,2))  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
		,cast(R.Imp_Total as decimal(14,2)) 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 cast(100 as decimal(14,2))
			 ELSE cast((a.Importe / f.Imp_total) * 100  as decimal(14,2))
			 END as porc_aplicado																			--29
		,CASE ISNULL(f.Imp_total,0) 
		     WHEN 0 THEN cast((i.Imp_Final *  F.Cotizacion )  * (1 -(ISNULL(F.Porc_Bonificacion,0) / 100)) as decimal(14,2))						-- 26/09/2022 (ISNULL(F.Porc_Bonificacion,0)) Ticket 3170
			 ELSE cast((i.Imp_Final *  F.Cotizacion )  * ( a.Importe / f.Imp_total) * (1 -(ISNULL(F.Porc_Bonificacion,0) / 100)) as decimal(14,2))  -- 26/09/2022 (ISNULL(F.Porc_Bonificacion,0)) Ticket 3170
			 END  as Imp_Prod_CIva																			--30
		,CASE ISNULL(f.Imp_total,0) 
		     WHEN 0 THEN cast((i.Imp_Gravado * F.Cotizacion ) * (1 -(ISNULL(F.Porc_Bonificacion,0) / 100)) as decimal(14,2))						-- 26/09/2022 (ISNULL(F.Porc_Bonificacion,0)) Ticket 3170
			 ELSE cast((i.Imp_Gravado * F.Cotizacion ) * ( a.Importe / f.Imp_total) * (1 -(ISNULL(F.Porc_Bonificacion,0) / 100)) as decimal(14,2))  -- 26/09/2022 (ISNULL(F.Porc_Bonificacion,0)) Ticket 3170
			 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(14,2)) AS EFECTIVO																--36
		,cast(0 as decimal(14,2)) AS TARJETA																--37
		,cast(0 as decimal(14,2)) AS CHEQ_TER																--38
		,cast(0 as decimal(14,2)) AS CHEQ_PROP																--39
		,cast(0 as decimal(14,2)) AS DEBITO																	--40
		,cast(0 as decimal(14,2)) AS FINANC																	--41
		,cast(0 as decimal(14,2)) AS OTROS																	--42
		,cast(0 as decimal(14,2)) AS APLIC																	--43
		,cast(0 as decimal(14,2)) AS Total_Pagos															--44
		,cast(0 as decimal(14,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
		,cast(f.Imp_Percep as decimal(14,2)) AS Imp_Total_Percep_Reten_Fac															--50 14/01/2020 Agregado Ticket 18.924
		,cast(r.Imp_Percep as decimal(14,2)) 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
		,cast(I.Bonif as decimal(14,2)) AS Porc_Bonif_Item														--58 13/04/2021 Agregado Ticket 22.253
		,cast(((100 * I.Imp_Final) / (100-I.Bonif)) as decimal(14,2)) AS Imp_Final_Item_Sin_Desc									--59 13/04/2021 Agregado Ticket 22.253
		,cast((((100 * I.Imp_Final) / (100-I.Bonif)) * (I.Bonif / 100)) as decimal(14,2)) AS Imp_Bonif_Item						--60 13/04/2021 Agregado Ticket 22.253
		,cast(0 as decimal(14,2)) 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
		,cast(A.Importe as decimal(14,2)) As Importe                                                        --66 18/03/2022 Agregado por vic
 		,CM.Id_Periodo_Caja        AS Id_Periodo_Caja                                                       --67 21/07/2022 vic cda
		,f.id_tipo_Cbte            AS Id_Tipo_Cbte_Fac                                                      --68 21/07/2022 vic cda
        ,TF.Descripcion            AS Descripcion_Fac
 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

/************************************************************* Cobranzas por recibos ***************************************************************************************/


   SET @SqlSelect =  ' SELECT (i.Imp_Gravado * F.Cotizacion * tf.Compromete_Factura) as Imp_Gravado_Item     --1
							  ,(i.Imp_Final * F.Cotizacion * 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
							  ,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
							  ,CASE (SELECT 1 from cbtes_aplica z where z.id_trans = f.id_trans and z.id_trans_d = r.id_trans  and z.importe > 0 )
									WHEN 1 THEN
										((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 > 0)/f.Imp_Total)*100
									ELSE
									    ((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 < 0)/f.Imp_Total * -1)*100
									END   as porc_aplicado   --29


							  ,CASE (SELECT 1 from cbtes_aplica z where z.id_trans = f.id_trans and z.id_trans_d = r.id_trans  and z.importe > 0 )
									WHEN 1 THEN
										   CASE ISNULL(f.Imp_total,0) 
												WHEN 0 THEN (i.Imp_Final *  F.Cotizacion ) * (1 -(ISNULL(F.Porc_Bonificacion,0) / 100))
												ELSE (i.Imp_Final *  F.Cotizacion )  --valor total del itema
													* ( a.Importe / f.Imp_total)     --porc aplicado de esa factura 
													* /*( (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 > 0)) */ (1 -(ISNULL(F.Porc_Bonificacion,0) / 100)) 
												END
									ELSE
										   CASE ISNULL(f.Imp_total,0) 
												WHEN 0 THEN (i.Imp_Final *-1 *  F.Cotizacion ) * (1 -(ISNULL(F.Porc_Bonificacion,0) / 100))
												ELSE (i.Imp_Final *-1 *  F.Cotizacion )  
													* ( a.Importe / f.Imp_total * -1) 
													* /*((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 < 0)) */ (1 -(ISNULL(F.Porc_Bonificacion,0)/ 100)) 
												END
									END    as Imp_Prod_CIva --30



							  ,CASE (SELECT 1 from cbtes_aplica z where z.id_trans = f.id_trans and z.id_trans_d = r.id_trans  and z.importe > 0 )
									WHEN 1 THEN
										  CASE ISNULL(f.Imp_total,0) 
												WHEN 0 THEN (i.Imp_Gravado * F.Cotizacion ) * (1 -(ISNULL(F.Porc_Bonificacion,0) / 100)) 
												ELSE (i.Imp_Gravado * F.Cotizacion ) 
													* ( a.Importe / 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 > 0)) */ (1 -(ISNULL(F.Porc_Bonificacion,0) / 100)) 
												END 
									ELSE
										  CASE ISNULL(f.Imp_total,0) 
												WHEN 0 THEN (i.Imp_Gravado * -1 * F.Cotizacion ) * (1 -(ISNULL(F.Porc_Bonificacion,0) / 100)) 
												ELSE (i.Imp_Gravado *-1 * F.Cotizacion ) 
													* ( a.Importe / f.Imp_total * -1) 
													* /*((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 < 0)) */ (1 -(ISNULL(F.Porc_Bonificacion,0) / 100)) 
												END 
									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(14,2)) AS EFECTIVO                                           --36
							  ,cast(0 as decimal(14,2)) AS TARJETA                                            --37
							  ,cast(0 as decimal(14,2)) AS CHEQ_TER                                           --38
							  ,cast(0 as decimal(14,2)) AS CHEQ_PROP                                          --39
							  ,cast(0 as decimal(14,2)) AS DEBITO                                             --40
							  ,cast(0 as decimal(14,2)) AS FINANC                                             --41
							  ,cast(0 as decimal(14,2)) AS OTROS 
							  ,cast(0 as decimal(14,2)) AS APLIC                                                --42
							  ,cast(0 as decimal(14,2)) AS Total_Pagos                                        --43
							  ,cast(0 as decimal(14,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
							  ,cast(A.Importe as decimal(14,2)) AS Importe
							  ,CM.Id_Periodo_Caja           AS Id_Periodo_Caja
							  ,f.id_tipo_Cbte               AS Id_Tipo_Cbte_Fac
							  ,ISNULL(TF.Descripcion,'''')  AS Descripcion_Fac
'
/*
							  ,((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 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)'

  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 (@pFlag_Filtrar_Caja = 1)
		SET @SqlWhere = LTRIM(RTRIM(@SqlWhere)) + ' AND ISNULL(CP.Id_Periodo_Caja,0) = ' + cast(@PId_Periodo_Caja as varchar(10))

*/

  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)) + '
								 ) ' 
 	  IF @Existe_Tag_Caja > 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,TagFilePos_ TAG2, 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 (CP.Id_Periodo_Caja = TAG2.PTR 
									  AND TAG2.TBL = ' + CAST(@e_Tag_Prod_Cajas  AS VARCHAR(10))  + '
									  AND TAG2.USR = ' + CAST(@pId_Usuario AS VARCHAR(10)) + '
									 ) ' 
	        END	
  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)) + '
									 ) ' 
		  IF @Existe_Tag_Caja > 0
	         BEGIN
		       SET @SqlWhere = RTRIM(LTRIM(@SqlWhere)) + ' AND (CP.Id_Periodo_Caja = TAG.PTR 
									  AND TAG.TBL = ' + CAST(@e_Tag_Prod_Cajas  AS VARCHAR(10))  + '
									  AND TAG.USR = ' + CAST(@pId_Usuario AS VARCHAR(10)) + '
									 ) ' 
	         END	
	     END	
		ELSE 
		 IF @Existe_Tag_Caja > 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 (CP.Id_Periodo_Caja = TAG.PTR 
									  AND TAG.TBL = ' + CAST(@e_Tag_Prod_Cajas  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 

  --PRINT @sql

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


							 
/************************************************************* agrego forma de pago ***************************************************************************************/
/* hay que tomar las ret , nc aplicadas en el recibo y los pagos a cta, como forma de pago del recibo
y no distribuir la formas de pago del recibo en las filas de nc aplicdadas
tener en cuenta los pagos a cta de recibos parciales 

SELECT * FROM PROD_FAMILIAS
*/

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

		  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

		  DECLARE CUR_GENERAR_DETALLE CURSOR FOR

		  Select VM.Id_Trans as Id_Trans_Rec,V.Id_Tipo_Val,Sum(V.Importe) AS IMPORTE 
		  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

	      OPEN CUR_GENERAR_DETALLE
		  FETCH NEXT FROM CUR_GENERAR_DETALLE INTO  @Id_Trans_Rec,@Id_Tipo_Val,@Importe_Val
	  
		  WHILE @@FETCH_STATUS = 0
			  BEGIN
			       SET @EXISTE  = ISNULL((SELECT COUNT(*) FROM #Coef_Pagos WHERE ID_TRANS = @Id_Trans_Rec),0)
				 
				   IF @EXISTE = 0
					   BEGIN
					    SET @Imp_Total_Rec = isnull((SELECT SUM(isnull(Imp_Gravado,0)) from cbtes_items where id_trans = @Id_Trans_Rec and Imp_Gravado > 0),0)

					    INSERT INTO #Coef_Pagos 
						(Id_Trans
						,Imp_Total_Rec
						,Imp_Valores  
						,Imp_PAGOS_APLI 
						,Imp_Efe 
						,Imp_Tar 
						,Imp_CH3  
						,Imp_CHP 
						,Imp_DB 
						,Imp_Fin 
						,Imp_Aju 
						,Coef_PAGOS_APLI  
						,Coef_Efe 
						,Coef_Tar 
						,Coef_CH3 
						,Coef_CHP 
						,Coef_DB 
						,Coef_Fin  
						,Coef_Aju) 
						values 
						(@Id_Trans_Rec,@Imp_Total_Rec,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
                     END


   			   IF @Id_Tipo_Val = 'EFE'  Update #Coef_Pagos SET Imp_Efe = Imp_Efe + @Importe_Val where Id_Trans = @Id_Trans_Rec
			   IF @Id_Tipo_Val = 'CH3'  Update #Coef_Pagos SET Imp_CH3 = Imp_CH3 + @Importe_Val where Id_Trans = @Id_Trans_Rec
			   IF @Id_Tipo_Val = 'CHP'  Update #Coef_Pagos SET Imp_CHP = Imp_CHP + @Importe_Val where Id_Trans = @Id_Trans_Rec
			   IF @Id_Tipo_Val = 'DB'   Update #Coef_Pagos SET Imp_DB  = Imp_DB  + @Importe_Val where Id_Trans = @Id_Trans_Rec
			   IF @Id_Tipo_Val = 'FIN'  Update #Coef_Pagos SET Imp_Fin = Imp_Fin + @Importe_Val where Id_Trans = @Id_Trans_Rec
			   IF @Id_Tipo_Val = 'AJU'  Update #Coef_Pagos SET Imp_Aju = Imp_Aju + @Importe_Val where Id_Trans = @Id_Trans_Rec
			   IF @Id_Tipo_Val = 'TAR'  Update #Coef_Pagos SET Imp_Tar = Imp_Tar + @Importe_Val where Id_Trans = @Id_Trans_Rec
			   
			   Update #Coef_Pagos SET Imp_Valores = Imp_Valores + @Importe_Val where Id_Trans = @Id_Trans_Rec

			  FETCH NEXT FROM CUR_GENERAR_DETALLE INTO   @Id_Trans_Rec,@Id_Tipo_Val,@Importe_Val 
		 END  
	     CLOSE CUR_GENERAR_DETALLE
	     DEALLOCATE CUR_GENERAR_DETALLE

		 UPDATE #Coef_Pagos set Imp_Pagos_Apli = Imp_Total_Rec - Imp_Valores
		 Update #Coef_Pagos set Coef_PAGOS_APLI = Imp_PAGOS_APLI /  Imp_Total_Rec 
								,Coef_Efe = Imp_Efe /  Imp_Total_Rec 
								,Coef_Tar = Imp_Tar /  Imp_Total_Rec 
								,Coef_CH3 = Imp_Ch3 /  Imp_Total_Rec 
								,Coef_CHP = Imp_Chp /  Imp_Total_Rec 
								,Coef_DB  = Imp_Db  /  Imp_Total_Rec 
								,Coef_Fin = Imp_Fin /  Imp_Total_Rec 
								,Coef_Aju = Imp_Aju /  Imp_Total_Rec 
		                        where Imp_Total_Rec <> 0


		  --select * from  #SUMA_PAGOS 

		  --SELECT D.Id_Trans AS Id_Trans_Rec,R.Id_Trans_Fac,R.Item_Fac,R.IMPORTE,
			 -- Isnull(R.IMPORTE * (Imp_Prod_CIva/c.Imp_Total) * Coef_Efe,0) AS EFECTIVO,
			 -- Isnull(R.IMPORTE * (Imp_Prod_CIva/c.Imp_Total) * Coef_Tar,0) AS TARJETA,
			 -- Isnull(R.IMPORTE * (Imp_Prod_CIva/c.Imp_Total) * Coef_CH3,0) AS CHEQ_TER,
			 -- Isnull(R.IMPORTE * (Imp_Prod_CIva/c.Imp_Total) * Coef_CHP,0) AS CHEQ_PROP,
			 -- Isnull(R.IMPORTE * (Imp_Prod_CIva/c.Imp_Total) * Coef_DB,0)  AS DEBITO,
		 	--  Isnull(R.IMPORTE * (Imp_Prod_CIva/c.Imp_Total) * Coef_FIN,0) AS FINANC,
			 -- Isnull(R.IMPORTE * (Imp_Prod_CIva/c.Imp_Total) * Coef_AJU,0) AS AJUSTE,
			 -- Isnull(R.IMPORTE * (Imp_Prod_CIva/c.Imp_Total) * Coef_PAGOS_APLI,0) AS APLIC,
		 	--  dbo.SF_Cli_Categ_Mes(c.Id_cliente, Fecha_QA) as Categoria,
			 -- Imp_Prod_CIva/C.Imp_Total as Coef_Pago
			 --INTO #DETALLE_PAGOS
			 --FROM #Coef_Pagos D, Cbtes c, #Reporte  R
			 --WHERE D.Id_Trans  = R.Id_Trans_Rec 
			 --      and R.Id_Trans_Fac = C.Id_Trans
			 --      AND C.Imp_Total <> 0
				--   AND Imp_Prod_CIva <> 0
	/*Se modifica porque estaba calculando mal el porcentaje de los valores aplicados Fecha:21/06/2022*/
		  SELECT D.Id_Trans AS Id_Trans_Rec,R.Id_Trans_Fac,R.Item_Fac,R.IMPORTE,
			  Isnull(Imp_Prod_CIva * Coef_Efe,0) AS EFECTIVO,
			  Isnull(Imp_Prod_CIva * Coef_Tar,0) AS TARJETA,
			  Isnull(Imp_Prod_CIva * Coef_CH3,0) AS CHEQ_TER,
			  Isnull(Imp_Prod_CIva * Coef_CHP,0) AS CHEQ_PROP,
			  Isnull(Imp_Prod_CIva * Coef_DB,0)  AS DEBITO,
		 	  Isnull(Imp_Prod_CIva * Coef_FIN,0) AS FINANC,
			  Isnull(Imp_Prod_CIva * Coef_AJU,0) AS AJUSTE,
			  Isnull(Imp_Prod_CIva * Coef_PAGOS_APLI,0) AS APLIC,
		 	  dbo.SF_Cli_Categ_Mes(c.Id_cliente, Fecha_QA) as Categoria,
			  Imp_Prod_CIva/C.Imp_Total as Coef_Pago
			 INTO #DETALLE_PAGOS
			 FROM #Coef_Pagos D, Cbtes c, #Reporte  R
			 WHERE D.Id_Trans  = R.Id_Trans_Rec 
			       and R.Id_Trans_Fac = C.Id_Trans
			       AND C.Imp_Total <> 0
				   AND Imp_Prod_CIva <> 0

/*
         
*/
ALTER TABLE #REPORTE ALTER COLUMN EFECTIVO DECIMAL(14,2)
ALTER TABLE #REPORTE ALTER COLUMN TARJETA DECIMAL(14,2)
ALTER TABLE #REPORTE ALTER COLUMN CHEQ_TER DECIMAL(14,2)
ALTER TABLE #REPORTE ALTER COLUMN CHEQ_PROP DECIMAL(14,2)
ALTER TABLE #REPORTE ALTER COLUMN DEBITO DECIMAL(14,2)
ALTER TABLE #REPORTE ALTER COLUMN OTROS DECIMAL(14,2)
ALTER TABLE #REPORTE ALTER COLUMN APLIC DECIMAL(14,2)
ALTER TABLE #REPORTE ALTER COLUMN Total_Pagos DECIMAL(14,2)
ALTER TABLE #REPORTE ALTER COLUMN Dif DECIMAL(14,2)
ALTER TABLE #REPORTE ALTER COLUMN Coef_Pago DECIMAL(14,5)



		UPDATE  #Reporte SET EFECTIVO = D.EFECTIVO
				,TARJETA = D.TARJETA
 				,CHEQ_TER = D.CHEQ_TER 
 				,CHEQ_PROP = D.CHEQ_PROP
 				,DEBITO = D.DEBITO 
 				,OTROS = D.AJUSTE
				,APLIC = D.APLIC
 		        ,Total_Pagos = D.EFECTIVO + D.TARJETA + D.CHEQ_TER + D.CHEQ_PROP + D.DEBITO + D.AJUSTE + D.APLIC 
				,Dif = D.EFECTIVO + D.TARJETA + D.CHEQ_TER + D.CHEQ_PROP + D.DEBITO + D.AJUSTE + D.APLIC- 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  #Coef_Pagos
		DROP TABLE  #Cbtes_a_Procesar
     END


/************************************************************* Conbranzas por Aplicacion Directa  ***************************************************************************************/
/*No cambiar el orden porque sino en la tabla de aplicacion de forma de pago va a tener NC sin forma de pago*/

   SET @SqlSelect =  ' SELECT (i.Imp_Gravado * F.Cotizacion * tf.Compromete_Factura) as Imp_Gravado_Item     --1
							  ,(i.Imp_Final * F.Cotizacion * 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
							  ,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
							  ,CASE (SELECT 1 from cbtes_aplica z where z.id_trans = f.id_trans and z.id_trans_d = r.id_trans  and z.importe > 0 )
									WHEN 1 THEN
										((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 > 0)/f.Imp_Total)*100
									ELSE
									    ((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 < 0)/f.Imp_Total * -1)*100
									END   as porc_aplicado   --29


							  ,CASE (SELECT 1 from cbtes_aplica z where z.id_trans = f.id_trans and z.id_trans_d = r.id_trans  and z.importe > 0 )
									WHEN 1 THEN
										   CASE ISNULL(f.Imp_total,0) 
												WHEN 0 THEN (i.Imp_Final *  F.Cotizacion ) * (1 -(ISNULL(F.Porc_Bonificacion,0) / 100))
												ELSE (i.Imp_Final *  F.Cotizacion )  --valor total del itema
													* ( a.Importe / f.Imp_total)     --porc aplicado de esa factura 
													* /*( (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 > 0)) */ (1 -(ISNULL(F.Porc_Bonificacion,0) / 100)) 
												END
									ELSE
										   CASE ISNULL(f.Imp_total,0) 
												WHEN 0 THEN (i.Imp_Final *-1 *  F.Cotizacion ) * (1 -(ISNULL(F.Porc_Bonificacion,0) / 100))
												ELSE (i.Imp_Final *-1 *  F.Cotizacion )  
													* ( a.Importe / f.Imp_total * -1) 
													* /*((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 < 0)) */ (1 -(ISNULL(F.Porc_Bonificacion,0) / 100)) 
												END
									END    as Imp_Prod_CIva --30



							  ,CASE (SELECT 1 from cbtes_aplica z where z.id_trans = f.id_trans and z.id_trans_d = r.id_trans  and z.importe > 0 )
									WHEN 1 THEN
										  CASE ISNULL(f.Imp_total,0) 
												WHEN 0 THEN (i.Imp_Gravado * F.Cotizacion ) * (1 -(ISNULL(F.Porc_Bonificacion,0) / 100)) 
												ELSE (i.Imp_Gravado * F.Cotizacion ) 
													* ( a.Importe / 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 > 0)) */ (1 -(ISNULL(F.Porc_Bonificacion,0) / 100)) 
												END 
									ELSE
										  CASE ISNULL(f.Imp_total,0) 
												WHEN 0 THEN (i.Imp_Gravado * -1 * F.Cotizacion ) * (1 -(ISNULL(F.Porc_Bonificacion,0) / 100)) 
												ELSE (i.Imp_Gravado *-1 * F.Cotizacion ) 
													* ( a.Importe / f.Imp_total * -1) 
													* /*((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 < 0)) */ (1 -(ISNULL(F.Porc_Bonificacion,0) / 100)) 
												END 
									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(14,2)) AS EFECTIVO                                           --36
							  ,cast(0 as decimal(14,2)) AS TARJETA                                            --37
							  ,cast(0 as decimal(14,2)) AS CHEQ_TER                                           --38
							  ,cast(0 as decimal(14,2)) AS CHEQ_PROP                                          --39
							  ,cast(0 as decimal(14,2)) AS DEBITO                                             --40
							  ,cast(0 as decimal(14,2)) AS FINANC                                             --41
							  ,cast(0 as decimal(14,2)) AS OTROS 
							  ,cast(0 as decimal(14,2)) AS APLIC                                                --42
							  ,cast(0 as decimal(14,2)) AS Total_Pagos                                        --43
							  ,cast(0 as decimal(14,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
							  ,0						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
							  ,cast(A.Importe as decimal(14,2)) AS Importe
							  ,0                       AS Id_Periodo_Caja 
							  ,f.id_tipo_Cbte          AS Id_Tipo_Cbte_Fac
						      ,ISNULL(TF.Descripcion,'''')  AS Descripcion_Fac
'

  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_factura IN (-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 IN (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, 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, 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, Transacciones TRF, Transacciones TRR'
      END		
  END
  
 
  SET @Sql = @SqlSelect + ' ' + @SqlInto +' '+ @SqlFrom + ' ' + @SqlWhere 

 IF @pFlag_Filtrar_Caja = 0
    EXEC('INSERT INTO #Reporte ' + @sql)





/************************************************************* 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(14,2)) AS EFECTIVO                                         --36
							  ,cast(0 as decimal(14,2)) AS TARJETA                                          --37
							  ,cast(0 as decimal(14,2)) AS CHEQ_TER                                         --38
							  ,cast(0 as decimal(14,2)) AS CHEQ_PROP                                        --39
							  ,cast(0 as decimal(14,2)) AS DEBITO                                           --40
							  ,cast(0 as decimal(14,2)) AS FINANC                                           --41
							  ,cast(0 as decimal(14,2)) AS OTROS   
							  ,cast(0 as decimal(14,2)) AS APLIC                                            --42
							  ,cast(0 as decimal(14,2)) AS Total_Pagos                                      --43
							  ,cast(0 as decimal(14,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
							,CAST(0 as decimal(14,2)) AS IMPORTE
							,CM.Id_Periodo_Caja         AS Id_Periodo_Caja 
							,''                         AS Id_Tipo_Cbte_Fac
							,''                         AS Descripcion_Fac
		   FROM cbtes r, cbtes_tipos tr,  Clientes C LEFT JOIN Cbtes_Cond_Pago CCP ON C.Id_Cond_Pago = CCP.Id_Cond_Pago
		        , Vendedores V,        Clientes_Tipos CT 
				, 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 CT.ID_TIPO_CLI = ISNULL(dbo.SF_Cli_Categ_Mes(r.Id_cliente, r.Fecha),0)
		   AND R.Fecha >= @pFechaDesde
		   AND r.Fecha <  @pFechaHasta+1 
		   
		   --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 -(ISNULL(F.Porc_Bonificacion,0) / 100)) * (TF.Compromete_Factura) as Imp_Gravado_Item            --1
							  ,(i.Imp_Final * F.Cotizacion) * (1 -(ISNULL(F.Porc_Bonificacion,0) / 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(14,2)) AS EFECTIVO                                           --36
							  ,cast(0 as decimal(14,2)) AS TARJETA                                            --37
							  ,cast(0 as decimal(14,2)) AS CHEQ_TER                                           --38
							  ,cast(0 as decimal(14,2)) AS CHEQ_PROP                                          --39
							  ,cast(0 as decimal(14,2)) AS DEBITO                                             --40
							  ,cast(0 as decimal(14,2)) AS FINANC                                             --41
							  ,cast(0 as decimal(14,2)) AS OTROS    
							  ,cast(0 as decimal(14,2)) AS Aplic                                              --42
							  ,cast(0 as decimal(14,2)) AS Total_Pagos                                        --43
							  ,cast(0 as decimal(14,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
                              ,cast(0 as decimal(14,2))     AS IMPORTE 
							  ,0                            AS Id_Periodo_Caja 
							  ,f.id_tipo_Cbte               AS Id_Tipo_Cbte_Fac
							  ,ISNULL(TF.Descripcion,'''')  AS Descripcion_Fac'

  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
  IF @pFlag_Filtrar_Caja = 0
     EXEC('INSERT INTO #Reporte ' + @sql)

END

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


   SET @SqlSelect =  ' SELECT (i.Imp_Gravado * F.Cotizacion) * (1 -(ISNULL(F.Porc_Bonificacion,0) / 100)) * (TF.Compromete_Factura) as Imp_Gravado_Item            --1
							  ,(i.Imp_Final * F.Cotizacion) * (1 -(ISNULL(F.Porc_Bonificacion,0) / 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 -(ISNULL(F.Porc_Bonificacion,0) / 100)) as Imp_Prod_CIva --30
							  ,(i.Imp_Gravado * F.Cotizacion ) * (1 -(ISNULL(F.Porc_Bonificacion,0) / 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(14,2)) AS EFECTIVO                                           --36
							  ,cast(0 as decimal(14,2)) AS TARJETA                                            --37
							  ,cast(0 as decimal(14,2)) AS CHEQ_TER                                           --38
							  ,cast(0 as decimal(14,2)) AS CHEQ_PROP                                          --39
							  ,cast(0 as decimal(14,2)) AS DEBITO                                             --40
							  ,cast(0 as decimal(14,2)) AS FINANC                                             --41
							  ,cast(0 as decimal(14,2)) AS OTROS  
							  ,cast(0 as decimal(14,2)) AS APLIC                                           --42
							  ,cast(0 as decimal(14,2)) AS Total_Pagos                                        --43
							  ,cast(0 as decimal(14,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
                              ,cast(0 as decimal(14,2)) AS IMPORTE 
							  ,CM.Id_Periodo_Caja       AS Id_Periodo_Caja
							  ,f.id_tipo_Cbte           AS Id_Tipo_Cbte_Fac
							  ,ISNULL(TF.Descripcion,'''')  AS Descripcion_Fac'

  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 F.ID_TRANS = CM.ID_TRANS
							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 (@pFlag_Filtrar_Caja = 1)
		SET @SqlWhere = LTRIM(RTRIM(@SqlWhere)) + ' AND ISNULL(CM.Id_Periodo_Caja,0) = ' + cast(@PId_Periodo_Caja as varchar(10))
  
  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, Cajas_Mov CM'
       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)) + '
		 						 ) ' 
	   IF @Existe_Tag_Caja > 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,TagFilePos_ TAG2, 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 (CP.Id_Periodo_Caja = TAG2.PTR 
									  AND TAG2.TBL = ' + CAST(@e_Tag_Prod_Cajas  AS VARCHAR(10))  + '
									  AND TAG2.USR = ' + CAST(@pId_Usuario AS VARCHAR(10)) + '
									 ) ' 
	        END	
    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, Cajas_Mov CM'
		   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)) + '
									 ) ' 

		   IF @Existe_Tag_Caja > 0
				BEGIN
				  SET @SqlWhere = RTRIM(LTRIM(@SqlWhere)) + ' AND (CM.Id_Periodo_Caja = TAG.PTR 
										  AND TAG.TBL = ' + CAST(@e_Tag_Prod_Cajas  AS VARCHAR(10))  + '
										  AND TAG.USR = ' + CAST(@pId_Usuario AS VARCHAR(10)) + '
										 ) ' 
				END	
		 END
		ELSE
		 IF @Existe_Tag_Caja > 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 (CM.Id_Periodo_Caja = TAG.PTR 
									  AND TAG.TBL = ' + CAST(@e_Tag_Prod_Cajas  AS VARCHAR(10))  + '
									  AND TAG.USR = ' + CAST(@pId_Usuario AS VARCHAR(10)) + '
									 ) ' 
	        END	
	      ELSE
		    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, Cajas_Mov CM'
     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 = ''

	    BEGIN
		    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 
				ORDER BY R.Id_Producto,R.Fecha_Rec,R.Desc_Recibo
	    END


  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,R.Id_Centro_Costo
         

     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":"20220401",
			"FechaHasta":"20220401",
			"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":"20220801",
			"FechaHasta":"20220831",
			"Flag_Liquidacion":"0",
			"Id_Periodo_Caja":"1382",
			"Flag_FPago":"1",
			"Tipo_Cob":"",
			"Formato":"D",
			"Flag_Filtrar_Fam":"0",
			"Flag_Inc_Fact_Periodo":"0",
			"Flag_Filtrar_Caja":"1"
		}'

SELECT * FROM TAGFILE_

EXEC CPJ_Productos_Cobros '{"Id_Usuario":"-1"
,"Equate":"90"
,"FechaDesde":"20220801"
,"FechaHasta":"20220930"
,"Flag_Liquidacion":"2"
,"Flag_FPago":"0"
,"Tipo_Cob":"-"
, "Formato":"D"
,"Flag_Filtrar_Prod":"1"
,"Flag_Filtrar_Fam":"0"
,"Flag_Filtrar_Caja":"1"
,"Flag_Inc_Fact_Periodo":"0"}'


*/

--select id_periodo_caja,* from valores order by id_trans desc