IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'[dbo].[CP_Productos_DEUDA]') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE [dbo].[CP_Productos_DEUDA] 
END
GO
CREATE PROCEDURE [dbo].[CP_Productos_DEUDA](
     @pUsuario    INT
   , @pEquate  SMALLINT
   , @pEquateClientes SMALLINT
   , @FECHA DATETIME
   )
AS
  --CREATE TABLE #Trans (Id_Trans INT)

  Declare @DEUDA table (Id_Trans INT,coef decimal(12,5) )

  IF @pEquate = 0
     RETURN

  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) 
  ,@Imp_prod_siva  Decimal(12,2) 
  ,@TRANS INT
  ,@Id_Cliente_Externo VARCHAR(14)
  ,@Id_Cta_Banco INT
  ,@Id_Cliente INT
  ,@EXISTE_TAG INT

  SET @EXISTE_TAG = 0
  SET @EXISTE_TAG = (SELECT COUNT(*) FROM TagFile_ TAG WHERE TAG.TBL = @pEquateClientes AND TAG.USR = @pUsuario)

  IF @EXISTE_TAG > 0
     BEGIN
	 insert into @DEUDA
		Select c.id_trans,dbo.CF_Cbte_Saldo(c.id_trans,@FECHA)/c.imp_total as coef
			 from cbtes c, cbtes_tipos t, TagFile_ TAG
 			 where c.id_tipo_cbte = t.id_tipo_cbte and t.Compromete_Factura in (1,-1)
					 and c.id_estado_cbte NOT IN (0,3) and dbo.CF_Cbte_Saldo(c.id_trans,@FECHA) <> 0
					 and isnull(c.imp_total,0) <> 0
					 AND (C.id_cliente = TAG.VAL AND TAG.TBL = @pEquateClientes AND TAG.USR = @pUsuario)
     END
    ELSE
     BEGIN
	    insert into @deuda
		Select c.id_trans,dbo.SF_Cbte_Saldo(c.id_trans,@FECHA)/c.imp_total as coef
			 from cbtes c, cbtes_tipos t
 			 where c.id_tipo_cbte = t.id_tipo_cbte 	 and t.Compromete_Factura in (1,-1)
			 		 and isnull(c.imp_total,0) <> 0
					 and c.id_estado_cbte NOT IN (0,3) and  dbo.SF_Cbte_Saldo(c.id_trans,@FECHA) <> 0
 	 
      END

  SET @EXISTE_TAG = 0
  SET @EXISTE_TAG = (SELECT COUNT(*) FROM TagFilePos_ TAG WHERE TAG.TBL = @pEquate AND TAG.USR = @pUsuario)

  IF @EXISTE_TAG > 0
      BEGIN 
        Select f.id_trans,f.Fecha,c.id_cliente,c.Razon_Social,c.id_cliente_externo,
                     i.id_producto,p.descripcion_detallada,i.fecha_qa as mes,
                     i.imp_final,(i.imp_final * tr.compromete_factura)*(1-(isnull(f.Porc_Bonificacion,0)/100)) as Bonificacion ,
                     f.Porc_Bonificacion,
                     (i.imp_final * tr.compromete_factura)*(1-(isnull(f.Porc_Bonificacion,0)/100)) * (1-( isnull(f.Porc_DescEspecial,0)/100)) as DescEspecial , 
                     f.Porc_DescEspecial,
                     (i.imp_final * tr.compromete_factura)*(1-(isnull(f.Porc_Bonificacion,0)/100)) * (1-( isnull(f.Porc_DescEspecial,0)/100)) * (d.coef) as saldo, 
                     i.fecha_venc,Flag_Periodo,Flag_Mes,d.coef,I.ITEM,dbo.CF_Cbte_Descrip(F.Id_Trans) AS CBTE_DESCRIP
				  from cbtes_items i, cbtes F, @DEUDA D, TagFilePos_ Tag , Cbtes_tipos TR, CLIENTES C, PRODUCTOS P
				  WHERE F.ID_TRANS = I.ID_TRANS AND F.ID_TRANS = D.ID_TRANS  
				  AND TR.ID_TIPO_CBTE = F.ID_TIPO_CBTE
				  AND C.ID_CLIENTE = F.ID_CLIENTE
				  AND P.ID_PRODUCTO = I.ID_PRODUCTO
				  AND I.imp_final <> 0
				  AND (i.id_producto = TAG.POS AND TAG.TBL = @pEquate AND TAG.USR = @pUsuario)
				  ORDER BY c.Razon_Social,i.id_producto,i.fecha_qa
       END
      ELSE
       BEGIN 
        Select f.id_trans,f.Fecha,c.id_cliente,c.Razon_Social,c.id_cliente_externo,
                     i.id_producto,p.descripcion_detallada,i.fecha_qa as mes,
                     i.imp_final,(i.imp_final * tr.compromete_factura)*(1-(isnull(f.Porc_Bonificacion,0)/100)) as Bonificacion ,
                     f.Porc_Bonificacion,
                     (i.imp_final * tr.compromete_factura)*(1-(isnull(f.Porc_Bonificacion,0)/100)) * (1-( isnull(f.Porc_DescEspecial,0)/100)) as DescEspecial , 
                     f.Porc_DescEspecial,
                     (i.imp_final * tr.compromete_factura)*(1-(isnull(f.Porc_Bonificacion,0)/100)) * (1-( isnull(f.Porc_DescEspecial,0)/100)) * (d.coef) as saldo, 
                     i.fecha_venc,Flag_Periodo,Flag_Mes,d.coef,I.ITEM,dbo.CF_Cbte_Descrip(F.Id_Trans) AS CBTE_DESCRIP
				  from cbtes_items i, cbtes F, @DEUDA D, Cbtes_tipos TR, CLIENTES C, PRODUCTOS P
				  WHERE F.ID_TRANS = I.ID_TRANS AND F.ID_TRANS = D.ID_TRANS  
				  AND TR.ID_TIPO_CBTE = F.ID_TIPO_CBTE
				  AND C.ID_CLIENTE = F.ID_CLIENTE
				  AND P.ID_PRODUCTO = I.ID_PRODUCTO
				  AND I.imp_final <> 0
				  ORDER BY c.Razon_Social,i.id_producto,i.fecha_qa
        END	   

-- exec dbo.CP_Productos_DEUDA -1,90,400,'20180630'

