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 C.Id_trans,
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(10,2),round(sum(R.IMPORTE*(A.IMPORTE/C.IMP_TOTAL) * (P.Porc_Desc_Liquidacion/100)),2,1))
  Else
   Convert(numeric(10,2),round(sum(R.IMPORTE*(A.IMPORTE/C.IMP_TOTAL) * (V.Porc_Desc_Liquidacion/100)),2,1))
 End
 AS IMP_VERMINAL
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_trans = c.id_trans
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.id_trans,C.FECHA,C.Comprobante_Nro,C.DESTINATARIO,C.CUIT,v.Porc_Desc_Liquidacion


SELECT ID_TRANS,FECHA,Comprobante_Nro,DESTINATARIO,CUIT,SUM(IMP_VERMINAL) AS IMP_VERMINAL into #PRORRATEO 
FROM  #RESULTADO 
group by ID_TRANS,FECHA,Comprobante_Nro,DESTINATARIO,CUIT


SELECT P.FECHA,Comprobante_Nro,P.DESTINATARIO,P.CUIT, R.IMPORTE - P.IMP_VERMINAL AS IMP_COMITENTE , P.IMP_VERMINAL AS IMP_VERMINAL, R.IMPORTE AS TOTAL_RETEN  
FROM  #PRORRATEO P, #RETEN R WHERE R.ID_TRANS = P.ID_TRANS
ORDER by FECHA,Comprobante_Nro,DESTINATARIO,CUIT



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



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