set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER PROCEDURE [dbo].[paInstm_PreExtoDfa] @pmtmEst CHAR(2),@pmtmTipoRep INT,@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCliente VARCHAR(16)=Null
,@pmIdCia CHAR(2)=Null
 AS 
 INSERT INTO tm_PreExto (tmEst,tmTipoRep,tmTipDoc,tmNumero,tmIdCia,tmItem,tmFecha,tmIdCliente,tmIdAgencia,tmCargos,tmAbonos,tmIdLinea,tmConcepto,tmDetalle,tmReferencia,tmCredito,tmVrPrestamo,tmVrCuota
,tmTotAbonos,tmNCuota,tmIdVend,tmTimeSys,tmFecVence,tmTipoAbono,tmDTF_EA,tmTasaNom,tmTasaEfe,tmTipRef,tmDocRef,tmIdCiaRef,tmTipCom,tmComprobante,tmIdCiaCom,tmAnulado,tmNumDev,tmFecDev
,tmSanClie,tmSanCia,tmSanAgenc,tmSanAgeCia,tmSanLinea,tmSanLineaCia,tmSanConc,tmSanConcCia)
SELECT @pmtmEst,@pmtmTipoRep,D.TipDev,D.Devolucion,D.IdCia,0,D.Fecha,D.IdCliente,D.IdAgencia,0,D.VrNeto-ISNULL(RecTotAbonos,0),'0004','INTERESES',D.Observacion,'','FACTURAS',D.VrNeto,0
,ISNULL(RecTotAbonos,0),0,D.IdVend,D.TimeSys,D.Fecha,2,0,0,0,D.TipDoc,D.Factura,D.IdCiaDoc,D.TipCom,D.Comprobante,D.IdCiaCom,0,0,D.FecDoc
,0,0,0,0,0,0,0,0
 FROM Trn_FinDevFac AS D INNER JOIN Trn_FinFactint AS F ON D.TipDoc=F.TipDoc AND D.Factura=F.Factura AND D.IdCiaDoc=F.IdCia
 LEFT JOIN (SELECT TipRec,Recibo,IdCia,SUM(TotalAbono) AS RecTotAbonos FROM Trn_FinAbonos WHERE TipRec='DFI'
 GROUP BY TipRec,Recibo,IdCia) AS A ON D.TipDev=A.TipRec AND D.Devolucion=A.Recibo AND D.IdCia=A.IdCia
 WHERE D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (D.VrNeto-ISNULL(RecTotAbonos,0))>0
 AND F.TipoFactInt='FACTURAS'
 AND D.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')