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

create PROCEDURE [dbo].[SP_PRORRATEO_RETEN_IVA]  @FECHA_DESDE DATETIME
                                       ,@FECHA_HASTA DATETIME

AS

select r.id_trans,r.id_tipo_reten ,SUM(R.IMPORTE) AS IMPORTE INTO #RETEN
from cbtes c, cbtes_tipos t, cbtes_reten r, cbtes_reten_tipos rt
where c.id_trans = r.id_trans
and c.id_tipo_cbte = t.id_tipo_cbte 
and t.Compromete_Recibos in (1,-1)
and r.Id_Tipo_Reten = rt.Id_Tipo_Reten
and rt.tipo = 'R'
and rt.tipo_IMP = 'V'
AND RT.Origen = 'N'
and c.fecha >=  @FECHA_DESDE
and c.fecha <  DATEADD(D,1,@FECHA_HASTA)
GROUP BY  r.id_trans,r.id_tipo_reten

-----------------------------------------
	SELECT DISTINCT C.ID_TRANS ,f.ID_TRANS as factura,
               CASE WHEN (select max(V1.Porc_Desc_Liquidacion) 
				FROM cbtes c1, cbtes_tipos t1, cbtes_reten r1 , cbtes f1, cbtes_aplica a1, Cbtes_Cond_Vtas v1,CBTES_RETEN_TIPOS TR1
				where c1.id_trans = r1.id_trans
				AND R1.Id_Tipo_Reten = TR1.ID_TIPO_RETEN
				AND c1.id_trans = a1.id_trans_d
				AND f1.id_trans = a1.id_trans
				AND f1.Id_Cond_Vta = V1.Id_Cond_Vta
				AND c1.id_tipo_cbte = t1.id_tipo_cbte 
				AND t1.Compromete_Recibos in (1,-1)
				AND TR1.TIPO = 'R'
				AND TR1.TIPO_IMP = 'V'
				AND c1.Fecha >= '20210401'
                AND c1.Fecha <  '20210501'
				AND C1.ID_ESTADO_CBTE <> 3
				AND C1.iD_TRANS = C.ID_TRANS
				GROUP BY C1.iD_TRANS) = 0 THEN 30 
		      ELSE
		       (select max(V1.Porc_Desc_Liquidacion) 
				FROM cbtes c1, cbtes_tipos t1, cbtes_reten r1 , cbtes f1, cbtes_aplica a1, Cbtes_Cond_Vtas v1,CBTES_RETEN_TIPOS TR1
				where c1.id_trans = r1.id_trans
				AND R1.Id_Tipo_Reten = TR1.ID_TIPO_RETEN
				AND c1.id_trans = a1.id_trans_d
				AND f1.id_trans = a1.id_trans
				AND f1.Id_Cond_Vta = V1.Id_Cond_Vta
				AND c1.id_tipo_cbte = t1.id_tipo_cbte 
				AND t1.Compromete_Recibos in (1,-1)
				AND TR1.TIPO = 'R'
				AND TR1.TIPO_IMP = 'V'
				AND c1.Fecha >= '20210401'
                AND c1.Fecha <  '20210501'
				AND C1.ID_ESTADO_CBTE <> 3
				AND C1.iD_TRANS = C.ID_TRANS
				GROUP BY C1.iD_TRANS)
		     END AS Porc_Desc_Liquidacion
			 into #Porcentaje 

			 FROM cbtes c, cbtes_tipos t, cbtes_reten r , cbtes f, cbtes_aplica a, Cbtes_Cond_Vtas v,CBTES_RETEN_TIPOS TR
			 where c.id_trans = r.id_trans
			 AND R.Id_Tipo_Reten = TR.ID_TIPO_RETEN
			 AND c.id_trans = a.id_trans_d
			 AND f.id_trans = a.id_trans
			 AND f.Id_Cond_Vta = V.Id_Cond_Vta
			 AND c.id_tipo_cbte = t.id_tipo_cbte 
			 AND t.Compromete_Recibos in (1,-1)
			 AND TR.TIPO = 'R'
			 AND TR.TIPO_IMP = 'V'
			 AND C.ID_ESTADO_CBTE <> 3
		     AND c.Fecha >= '20210401'
             AND c.Fecha <  '20210501'
------------------------------------------------------------


--select id_cliente,Porc_Desc_Liquidacion  into #Porcentaje 
--from Clientes c, Cbtes_Cond_Vtas V where c.Id_Cond_Vta  = v.Id_Cond_Vta


select 
CONVERT(VARCHAR(10), C.FECHA, 103) as FECHA,
C.Comprobante_Nro,
C.DESTINATARIO,
C.CUIT, 
 Case isnull(V.Porc_Desc_Liquidacion,0) when 0 Then  
   convert(numeric(9,2),round(Sum(R.IMPORTE*(A.IMPORTE/C.IMP_TOTAL) - (R.IMPORTE*(A.IMPORTE/C.IMP_TOTAL)*(P.Porc_Desc_Liquidacion/100))),2,1)) 
  Else
   convert(numeric(9,2),round(Sum(R.IMPORTE*(A.IMPORTE/C.IMP_TOTAL) - (R.IMPORTE*(A.IMPORTE/C.IMP_TOTAL)*(V.Porc_Desc_Liquidacion/100))),2,1))
 End
 AS IMP_COMITENTE,
 Case isnull(V.Porc_Desc_Liquidacion,0) when 0 Then  
   convert(numeric(9,2),round(sum(R.IMPORTE*(A.IMPORTE/C.IMP_TOTAL) * (P.Porc_Desc_Liquidacion/100)),2,1))
  else
   convert(numeric(9,2),round(sum(R.IMPORTE*(A.IMPORTE/C.IMP_TOTAL) * (V.Porc_Desc_Liquidacion/100)),2,1))
 end
 AS IMP_VERMINAL,
 Case isnull(V.Porc_Desc_Liquidacion,0) when 0 Then  
   convert(numeric(9,2),round(Sum(R.IMPORTE*(A.IMPORTE/C.IMP_TOTAL) - (R.IMPORTE*(A.IMPORTE/C.IMP_TOTAL)*(P.Porc_Desc_Liquidacion/100)))+sum(R.IMPORTE*(A.IMPORTE/C.IMP_TOTAL)*(P.Porc_Desc_Liquidacion/100)),2,1)) 
  else
   convert(numeric(9,2),round(Sum(R.IMPORTE*(A.IMPORTE/C.IMP_TOTAL) - (R.IMPORTE*(A.IMPORTE/C.IMP_TOTAL)*(V.Porc_Desc_Liquidacion/100)))+sum(R.IMPORTE*(A.IMPORTE/C.IMP_TOTAL)*(V.Porc_Desc_Liquidacion/100)),2,1)) 
 End 
AS TOTAL_RETEN    
INTO #RESULTADO
from cbtes c, cbtes_tipos t, #RETEN r , cbtes f, cbtes_aplica a, Cbtes_Cond_Vtas v,cbtes_reten_tipos rt, #Porcentaje P
where c.id_trans = r.id_trans
and c.id_trans = a.id_trans_d
and f.id_trans = a.id_trans
and f.Id_Cond_Vta = V.Id_Cond_Vta
and c.id_tipo_cbte = t.id_tipo_cbte 
and t.Compromete_Recibos in (1,-1)
and r.Id_Tipo_Reten = rt.Id_Tipo_Reten
and f.id_trans = P.Factura
and rt.tipo_IMP = 'V'
AND RT.Origen = 'N'
and c.fecha >=  @FECHA_DESDE
and c.fecha <  DATEADD(D,1,@FECHA_HASTA)
group by C.FECHA,C.Comprobante_Nro,C.DESTINATARIO,C.CUIT,v.Porc_Desc_Liquidacion


SELECT FECHA,Comprobante_Nro,DESTINATARIO,CUIT,SUM(IMP_COMITENTE),SUM(IMP_VERMINAL),SUM(TOTAL_RETEN)
FROM  #RESULTADO 
group by FECHA,Comprobante_Nro,DESTINATARIO,CUIT

DROP TABLE #RETEN
DROP TABLE #Porcentaje
DROP TABLE #RESULTADO



--- exec [SP_PRORRATEO_RETEN_IVA2]  '20210401','20210430'
                                       