ALTER TABLE tm_PreExto ADD tmTipoRep INT DEFAULT(0) NOT NULL
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreExto]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_PreExto]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreExtoApr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_PreExtoApr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreExtoDfa]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_PreExtoDfa]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreExtoDfi]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_PreExtoDfi]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreExtoDpr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_PreExtoDpr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreExtoFif]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_PreExtoFif]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreExtoNca]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_PreExtoNca]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreExtoNcr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_PreExtoNcr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreExtoNdb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_PreExtoNdb]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreExtoOda]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_PreExtoOda]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreExtoOdb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_PreExtoOdb]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreExtoPra]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_PreExtoPra]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreExtoPre]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_PreExtoPre]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreExtoPri]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_PreExtoPri]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreExtoRca]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_PreExtoRca]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreExtoRec]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_PreExtoRec]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_PreExtoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrytm_PreExtoLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreExtoPtmo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_PreExtoPtmo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUptm_PreExtoCuo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUptm_PreExtoCuo]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






CREATE PROCEDURE [dbo].[paQrytm_PreExtoLta] @pmtmEst CHAR(2),@pmFecInicio SMALLDATETIME,@pmFecCorte SMALLDATETIME,@pmtmTipoRep INT
AS 
SELECT tmIdCliente,T.RazonSocial AS NomCliente,tmIdAgencia,NomAgencia AS Agencia,tmFecha,tmFecVence,tmTipDoc,TipoDoc,tmNumero,tmIdCia,Compania,tmItem,tmCargos,tmAbonos
,tmIdLinea,LinCredito,CupoCredito,tmConcepto,tmDetalle,tmReferencia,tmCredito,tmVrPrestamo,tmVrCuota,tmTotAbonos,tmTipoAbono,tmNCuota,tmIdVend,VN.RazonSocial AS NomVendedor
,tmTipRef,tmDocRef,tmIdCiaRef,tmTipCom,tmComprobante,tmIdCiaCom,tmAnulado,tmNumDev,tmFecDev,tmDTF_EA,tmTasaNom,tmTasaEfe
,tmSanClie,tmSanCia,tmSanAgenc,tmSanAgeCia,tmSanLinea,tmSanLineaCia,tmSanConc,tmSanConcCia,IdReg,tmTimeSys
,NumCredito,NPlazos,Causacion,FecPrestamo,TipoCredito,P.Observacion AS Observ,ISNULL(SaldoMora,0) AS VrSaldoMora
--Total pagos a capital
,ISNULL(CASE tmTipDoc WHEN 'PR1' THEN (SELECT SUM(Trn_FinAbonos.TotalAbono) FROM Trn_FinAbonos INNER JOIN Trn_FinCuotas ON Trn_FinAbonos.TipDoc=Trn_FinCuotas.TipDoc
 AND Trn_FinAbonos.IdPrestamo=Trn_FinCuotas.IdPrestamo AND Trn_FinAbonos.IdCiaPre=Trn_FinCuotas.IdCia AND Trn_FinAbonos.ItemPre=Trn_FinCuotas.Item
 WHERE Trn_FinAbonos.TipDoc=EX.tmTipDoc AND Trn_FinAbonos.IdPrestamo=EX.tmNumero AND Trn_FinAbonos.IdCiaPre=EX.tmIdCia
 AND Trn_FinAbonos.Fecha<=@pmFecCorte AND Trn_FinCuotas.Concepto='CAPITAL') ELSE 0 END,0) AS VrPagosCapital
,ISNULL(CASE tmTipDoc WHEN 'PR1' THEN (SELECT COUNT(NumCuota) FROM Trn_FinCuotas WHERE Trn_FinCuotas.TipDoc=EX.tmTipDoc AND Trn_FinCuotas.IdPrestamo=EX.tmNumero AND Trn_FinCuotas.IdCia=EX.tmIdCia
 AND Trn_FinCuotas.Fecha<=@pmFecCorte AND Trn_FinCuotas.Concepto='CAPITAL') ELSE 0 END,0) AS CuotasCausadas
--datos del cliente
,T.TipoId AS ClieTipoId,T.Dv AS ClieDv,T.Direccion AS ClieDireccion,T.IdLocal AS ClieCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,DP.Departamento AS ClieDpto
,T.Telefono AS ClieTelefono,T.Fax AS ClieFax,T.e_mail AS ClieEmail,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdTipoTerc AS CdTipoTerc,TipoTercero,EsExento
FROM tm_PreExto AS EX INNER JOIN Terceros AS T ON EX.tmIdCliente=T.IdTercero INNER JOIN  Companias AS CI ON EX.tmIdCia=CI.IdCia
INNER JOIN LineasCred AS LC ON EX.tmIdLinea=LC.IdLinea
INNER JOIN  TercCliePrestamo AS CLI ON EX.tmIdCliente=CLI.IdClie AND EX.tmIdAgencia=CLI.IdAgencia
INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo  INNER JOIN  TiposTerc AS TTC ON CLI.IdTipoTerc=TTC.IdTipoTerc
INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
LEFT JOIN Terceros AS VN ON EX.tmIdVend=VN.IdTercero
LEFT JOIN TercClieLineas AS CL ON EX.tmIdCliente=CL.IdClie AND EX.tmIdAgencia=CL.IdAgencia AND EX.tmIdLinea=CL.IdLinea
LEFT JOIN Sys_TiposDoc AS TD ON EX.tmTipDoc=TD.IdDoc 
LEFT JOIN Trn_FinPrestamo AS P ON EX.tmTipDoc=P.TipDoc AND EX.tmNumero=P.IdPrestamo AND EX.tmIdCia=P.IdCia
LEFT JOIN (SELECT IdCliente,IdAgencia,CodLinCred,SUM(VrTotal-VrAbonado) AS SaldoMora FROM Trn_FinCuotas WHERE Fecha<@pmFecCorte AND VrTotal>VrAbonado GROUP BY IdCliente,IdAgencia,CodLinCred)
AS SM ON EX.tmIdCliente=SM.IdCliente AND EX.tmIdAgencia=SM.IdAgencia AND EX.tmIdLinea=SM.CodLinCred
WHERE tmEst=@pmtmEst AND tmTipoRep=@pmtmTipoRep

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_PreExtoApr] @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,A.TipRec,A.Recibo,A.IdCia,A.Item,A.Fecha,A.IdCliente,A.IdAgencia,0,A.TotalAbono,ISNULL(CodLinCred,'0001'),C.Concepto,A.Detalle,C.NumFactura,'PRESTAMO',A.TotalAbono,C.VrTotal
,C.VrAbonado,C.NumCuota,A.IdVend,A.TimeSys,A.FecPago,A.TipoAplica,DTF_EA,TasaNom,TasaEfe,A.TipDoc,A.IdPrestamo,A.IdCiaPre,'0',0,'00',0,0,Null
,0,0,0,0,0,0,0,0
 FROM Trn_FinAbonos AS A INNER JOIN Trn_FinCuotas AS C ON A.TipDoc=C.TipDoc AND A.IdPrestamo=C.IdPrestamo AND A.IdCiaPre=C.IdCia AND A.ItemPre=C.Item
 WHERE A.TipRec='PR1' AND A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
 AND A.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND A.IdCia LIKE ISNULL(@pmIdCia,'%%')


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_PreExtoNca] @pmtmEst CHAR(2),@pmtmTipoRep INT,@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCliente VARCHAR(16)=Null
,@pmIdCia CHAR(2)=Null
--notas cred pendiente por aplicar
 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,N.TipDoc,NumNota,N.IdCia,0,N.Fecha,N.IdCliente,N.IdAgencia,0,N.VrNeto-ISNULL(RecTotAbonos,0),'0001','INTERESES',N.Observacion,N.Referencia,N.Modalidad,N.VrNeto,0
,ISNULL(RecTotAbonos,0),0,N.IdVend,N.TimeSys,N.Fecha,2,0,0,0,TipDcm,Documento,IdCiaDcm,N.TipCom,N.Comprobante,N.IdCiaCom,N.Anulado,0,Null
,0,0,0,0,0,0,0,0
 FROM Trn_FinNotas AS N LEFT JOIN (SELECT TipRec,Recibo,IdCia,SUM(TotalAbono) AS RecTotAbonos FROM Trn_FinAbonos WHERE TipRec='NC2'
 GROUP BY TipRec,Recibo,IdCia) AS A ON N.TipDoc=A.TipRec AND N.NumNota=A.Recibo AND N.IdCia=A.IdCia
 WHERE N.TipDoc='NC2' AND N.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (N.VrNeto-ISNULL(RecTotAbonos,0))>0
 AND N.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND N.IdCia LIKE ISNULL(@pmIdCia,'%%')



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE 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 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 D.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_PreExtoDfi] @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,A.Item,D.Fecha,D.IdCliente,D.IdAgencia,0,A.TotalAbono,ISNULL(CodLinCred,'0001'),C.Concepto,A.Detalle,C.NumFactura,'FACTURAS',D.VrNeto,C.VrTotal
,C.VrAbonado,C.NumCuota,A.IdVend,D.TimeSys,A.FecPago,A.TipoAplica,DTF_EA,TasaNom,TasaEfe,A.TipDoc,A.IdPrestamo,A.IdCiaPre,D.TipCom,D.Comprobante,D.IdCiaCom,0,0,FecDoc
,0,0,0,0,0,0,0,0
 FROM Trn_FinDevFac AS D INNER JOIN Trn_FinAbonos AS A ON D.TipDev=A.TipRec AND D.Devolucion=A.Recibo AND D.IdCia=A.IdCia
 INNER JOIN Trn_FinCuotas AS C ON A.TipDoc=C.TipDoc AND A.IdPrestamo=C.IdPrestamo AND A.IdCiaPre=C.IdCia AND A.ItemPre=C.Item
 WHERE D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
 AND D.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_PreExtoOda] @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,D.VrNeto-(ISNULL(RecTotAbonos,0)*-1),0,'0001','CAPITAL',D.Observacion,D.Referencia,R.TipoCredito,D.VrNeto,0
,ISNULL(RecTotAbonos,0)*-1,0,D.IdVend,D.TimeSys,D.FecPago,2,0,0,0,D.TipDoc,D.Recibo,D.IdCiaDoc,D.TipCom,D.Comprobante,D.IdCiaCom,0,0,D.FecDoc
,0,0,0,0,0,0,0,0
 FROM Trn_FinDevRec AS D INNER JOIN Trn_FinRecibos AS R ON D.TipDoc=R.TipDoc AND D.Recibo=R.Recibo AND D.IdCiaDoc=R.IdCia
 LEFT JOIN (SELECT TipRec,Recibo,IdCia,SUM(TotalAbono) AS RecTotAbonos FROM Trn_FinAbonos WHERE TipRec='OD2'
 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)*-1))>0
 AND D.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_PreExtoNdb] @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,C.TipDoc,C.IdPrestamo,C.IdCia,C.Item,C.Fecha,C.IdCliente,C.IdAgencia,C.VrTotal,0,ISNULL(CodLinCred,'0001'),C.Concepto,C.Detalle,N.Referencia,N.Modalidad,0,0
,C.VrAbonado,NumCuota,N.IdVend,N.TimeSys,C.FechaVence,TipoCuota,DTF_EA,TasaNom,TasaEfe,'PR1',NumPreMora,IdCiaCau,TipCom,Comprobante,IdCiaCom,0,0,Null
,0,0,0,0,0,0,0,0
 FROM Trn_FinCuotas AS C INNER JOIN Trn_FinNotas AS N ON C.TipDoc=N.TipDoc AND C.IdPrestamo=N.NumNota AND C.IdCia=N.IdCia
 WHERE C.TipDoc='ND2' AND C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND C.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_PreExto] @pmtmEst CHAR(2),@pmtmTipDoc VARCHAR(3),@pmtmNumero INT,@pmtmIdCia CHAR(2),@pmtmItem INT,@pmtmFecha SMALLDATETIME,@pmtmIdCliente VARCHAR(16),@pmtmIdAgencia VARCHAR(16),@pmtmCargos MONEY,@pmtmAbonos MONEY,@pmtmIdLinea VARCHAR(4),@pmtmConcepto VARCHAR(10),@pmtmDetalle VARCHAR(250),@pmtmReferencia VARCHAR(50),@pmtmCredito VARCHAR(10),@pmtmVrPrestamo MONEY,@pmtmVrCuota MONEY,@pmtmTotAbonos MONEY,@pmtmNCuota INT
,@pmtmIdVend VARCHAR(16),@pmtmTimeSys SMALLDATETIME,@pmtmFecVence SMALLDATETIME,@pmtmTipoAbono INT,@pmtmDTF_EA DECIMAL(16,8),@pmtmTasaNom DECIMAL(16,8),@pmtmTasaEfe DECIMAL(16,8),@pmtmTipRef VARCHAR(3),@pmtmDocRef INT,@pmtmIdCiaRef CHAR(2),@pmtmTipCom VARCHAR(3),@pmtmComprobante INT,@pmtmIdCiaCom CHAR(2),@pmtmAnulado BIT,@pmtmNumDev INT,@pmtmFecDev SMALLDATETIME,@pmtmSanClie MONEY,@pmtmSanCia MONEY,@pmtmSanAgenc MONEY,@pmtmSanAgeCia MONEY
,@pmtmSanLinea MONEY,@pmtmSanLineaCia MONEY,@pmtmSanConc MONEY,@pmtmSanConcCia MONEY,@pmtmTipoRep INT
 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)
 VALUES (@pmtmEst,@pmtmTipoRep,@pmtmTipDoc,@pmtmNumero,@pmtmIdCia,@pmtmItem,@pmtmFecha,@pmtmIdCliente,@pmtmIdAgencia,@pmtmCargos,@pmtmAbonos,@pmtmIdLinea,@pmtmConcepto,@pmtmDetalle,@pmtmReferencia,@pmtmCredito,@pmtmVrPrestamo,@pmtmVrCuota,@pmtmTotAbonos,@pmtmNCuota,@pmtmIdVend,@pmtmTimeSys,@pmtmFecVence,@pmtmTipoAbono,@pmtmDTF_EA,@pmtmTasaNom,@pmtmTasaEfe,@pmtmTipRef,@pmtmDocRef,@pmtmIdCiaRef,@pmtmTipCom,@pmtmComprobante,@pmtmIdCiaCom,@pmtmAnulado,@pmtmNumDev
,@pmtmFecDev,@pmtmSanClie,@pmtmSanCia,@pmtmSanAgenc,@pmtmSanAgeCia,@pmtmSanLinea,@pmtmSanLineaCia,@pmtmSanConc,@pmtmSanConcCia)


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_PreExtoFif] @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,C.TipDoc,C.IdPrestamo,C.IdCia,C.Item,C.Fecha,C.IdCliente,C.IdAgencia,C.VrTotal,0,ISNULL(CodLinCred,'0001'),C.Concepto,C.Detalle,C.NumFactura,F.TipoFactInt,0,0
,C.VrAbonado,NumCuota,F.IdVend,F.TimeSys,C.FechaVence,TipoCuota,DTF_EA,TasaNom,TasaEfe,'PR1',NumPreMora,IdCiaCau,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev
,0,0,0,0,0,0,0,0
 FROM Trn_FinCuotas AS C INNER JOIN Trn_FinFactint AS F ON C.TipDoc=F.TipDoc AND C.IdPrestamo=F.Factura AND C.IdCia=F.IdCia
 WHERE C.TipDoc='FIF' AND C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND C.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')



 

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_PreExtoDpr] @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,TipDev,Devolucion,D.IdCia,0,D.Fecha,D.IdCliente,D.IdAgencia,0,D.VrPrestamo,P.IdLinea,'CAPITAL',D.Observacion,P.NumCredito,P.TipoCredito,D.VrPrestamo,P.VrCuota
,PagosCapital,AcumCuotas,P.IdVend,D.TimeSys,D.FecDoc,-1,0,0,0,D.TipDoc,D.IdPrestamo,D.IdCiaDoc,D.TipCom,D.Comprobante,D.IdCiaCom,0,0,Null
,0,0,0,0,0,0,0,0
 FROM Trn_FinDevPre AS D INNER JOIN Trn_FinPrestamo AS P ON D.TipDoc=P.TipDoc AND D.IdPrestamo=P.IdPrestamo AND D.IdCiaDoc=P.IdCia
 WHERE D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND P.CauAnticipado=0
 AND D.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_PreExtoPri] @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,C.TipDoc,C.IdPrestamo,C.IdCia,C.Item,C.Fecha,C.IdCliente,C.IdAgencia,C.VrTotal,0,ISNULL(CodLinCred,P.IdLinea),C.Concepto,C.Detalle,C.NumFactura,ISNULL(P.TipoCredito,'PRESTAMO'),P.VrPrestamo,P.VrCuota
,C.VrAbonado,NumCuota,P.IdVend,P.TimeSys,C.FechaVence,C.TipoCuota,DTF_EA,TasaNom,TasaEfe,ISNULL(TipFacInt,''),NumFacInt,ISNULL(CdCiaFacInt,''),TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev
,0,0,0,0,0,0,0,0
 FROM Trn_FinCuotas AS C LEFT JOIN Trn_FinPrestamo AS P ON C.TipDoc=P.TipDoc  AND C.IdPrestamo=P.IdPrestamo  AND C.IdCia=P.IdCia
 WHERE C.TipDoc='PR1' AND C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND C.Concepto<>'CAPITAL' AND C.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')



 


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[paInstm_PreExtoPra] @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,C.TipDoc,C.IdPrestamo,C.IdCia,C.Item,P.FecPrestamo,C.IdCliente,C.IdAgencia,C.VrTotal,0,ISNULL(CodLinCred,P.IdLinea),C.Concepto,C.Detalle,C.NumFactura,ISNULL(P.TipoCredito,'PRESTAMO'),P.VrPrestamo,P.VrCuota
,C.VrAbonado,NumCuota,P.IdVend,P.TimeSys,C.FechaVence,C.TipoCuota,DTF_EA,TasaNom,TasaEfe,C.TipDoc,NumPrestRef,ISNULL(CiaPrestRef,''),TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev
,0,0,0,0,0,0,0,0
 FROM Trn_FinCuotas AS C INNER JOIN Trn_FinPrestamo AS P ON C.TipDoc=P.TipDoc  AND C.IdPrestamo=P.IdPrestamo  AND C.IdCia=P.IdCia
 WHERE C.TipDoc='PR1' AND P.FecPrestamo BETWEEN @pmFechaIni AND @pmFechaFin AND C.Concepto='CAPITAL' AND P.CauAnticipado=1
 AND C.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_PreExtoPre] @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,TipDoc,IdPrestamo,IdCia,0,FecPrestamo,IdCliente,IdAgencia,VrPrestamo,0,IdLinea,'CAPITAL',Observacion,NumCredito,TipoCredito,VrPrestamo,VrCuota
,PagosCapital,AcumCuotas,IdVend,TimeSys,FecCorte,-1,0,0,0,TipDoc,NumPrestRef,ISNULL(CiaPrestRef,''),TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev
,0,0,0,0,0,0,0,0
 FROM Trn_FinPrestamo
 WHERE FecPrestamo BETWEEN @pmFechaIni AND @pmFechaFin AND CauAnticipado=0
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND IdCia LIKE ISNULL(@pmIdCia,'%%')


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_PreExtoRca] @pmtmEst CHAR(2),@pmtmTipoRep INT,@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCliente VARCHAR(16)=Null
,@pmIdCia CHAR(2)=Null
--recibo de caja pendiente por aplicar
 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,R.TipDoc,R.Recibo,R.IdCia,0,R.Fecha,R.IdCliente,R.IdAgencia,0,R.VrNeto-ISNULL(RecTotAbonos,0),CASE R.TipoCredito WHEN 'TC' THEN '0005' WHEN 'FACTURAS' THEN '0004' ELSE '0001' END
,CASE R.TipoCredito WHEN 'FACTURAS' THEN 'INTERESES' ELSE 'CAPITAL' END,R.Observacion,R.Referencia,R.TipoCredito,R.VrNeto,0
,ISNULL(RecTotAbonos,0),0,R.IdVend,R.TimeSys,R.FecPago,2,0,0,0,TipDcm,Documento,IdCiaDcm,R.TipCom,R.Comprobante,R.IdCiaCom,R.Anulado,NumDev,FecDev
,0,0,0,0,0,0,0,0
 FROM Trn_FinRecibos AS R LEFT JOIN (SELECT TipRec,Recibo,IdCia,SUM(TotalAbono) AS RecTotAbonos FROM Trn_FinAbonos WHERE TipRec='RC2'
 GROUP BY TipRec,Recibo,IdCia) AS A ON R.TipDoc=A.TipRec AND R.Recibo=A.Recibo AND R.IdCia=A.IdCia
 WHERE R.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (R.VrNeto-ISNULL(RecTotAbonos,0))>0
 AND R.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND R.IdCia LIKE ISNULL(@pmIdCia,'%%')


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_PreExtoRec] @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,R.TipDoc,R.Recibo,R.IdCia,A.Item,R.Fecha,R.IdCliente,R.IdAgencia,0,A.TotalAbono,ISNULL(CodLinCred,'0001'),C.Concepto,A.Detalle,R.Referencia,R.TipoCredito,R.VrNeto,C.VrTotal
,C.VrAbonado,C.NumCuota,A.IdVend,R.TimeSys,A.FecPago,A.TipoAplica,DTF_EA,TasaNom,TasaEfe,A.TipDoc,A.IdPrestamo,A.IdCiaPre,R.TipCom,R.Comprobante,R.IdCiaCom,R.Anulado,NumDev,FecDev
,0,0,0,0,0,0,0,0
 FROM Trn_FinRecibos AS R INNER JOIN Trn_FinAbonos AS A ON R.TipDoc=A.TipRec AND R.Recibo=A.Recibo AND R.IdCia=A.IdCia
 INNER JOIN Trn_FinCuotas AS C ON A.TipDoc=C.TipDoc AND A.IdPrestamo=C.IdPrestamo AND A.IdCiaPre=C.IdCia AND A.ItemPre=C.Item
 WHERE R.Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
 AND R.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND R.IdCia LIKE ISNULL(@pmIdCia,'%%')

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[paInstm_PreExtoNcr] @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,N.TipDoc,N.NumNota,N.IdCia,A.Item,N.Fecha,N.IdCliente,N.IdAgencia,0,A.TotalAbono,ISNULL(CodLinCred,'0001'),C.Concepto,A.Detalle,N.Referencia,N.Modalidad,N.VrNeto,C.VrTotal
,C.VrAbonado,C.NumCuota,A.IdVend,N.TimeSys,A.FecPago,A.TipoAplica,DTF_EA,TasaNom,TasaEfe,A.TipDoc,A.IdPrestamo,A.IdCiaPre,N.TipCom,N.Comprobante,N.IdCiaCom,N.Anulado,0,Null
,0,0,0,0,0,0,0,0
 FROM Trn_FinNotas AS N INNER JOIN Trn_FinAbonos AS A ON N.TipDoc=A.TipRec AND N.NumNota=A.Recibo AND N.IdCia=A.IdCia
 INNER JOIN Trn_FinCuotas AS C ON A.TipDoc=C.TipDoc AND A.IdPrestamo=C.IdPrestamo AND A.IdCiaPre=C.IdCia AND A.ItemPre=C.Item
 WHERE N.TipDoc='NC2' AND N.Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
 AND N.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND N.IdCia LIKE ISNULL(@pmIdCia,'%%')



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_PreExtoOdb] @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,A.Item,D.Fecha,D.IdCliente,D.IdAgencia,A.TotalAbono*-1,0,ISNULL(CodLinCred,'0001'),C.Concepto,A.Detalle,D.Referencia,R.TipoCredito,D.VrNeto,C.VrTotal
,C.VrAbonado,C.NumCuota,A.IdVend,D.TimeSys,A.FecPago,A.TipoAplica,DTF_EA,TasaNom,TasaEfe,A.TipDoc,A.IdPrestamo,A.IdCiaPre,D.TipCom,D.Comprobante,D.IdCiaCom,0,0,FecDoc
,0,0,0,0,0,0,0,0
 FROM Trn_FinDevRec AS D INNER JOIN Trn_FinAbonos AS A ON D.TipDev=A.TipRec AND D.Devolucion=A.Recibo AND D.IdCia=A.IdCia
 INNER JOIN Trn_FinCuotas AS C ON A.TipDoc=C.TipDoc AND A.IdPrestamo=C.IdPrestamo AND A.IdCiaPre=C.IdCia AND A.ItemPre=C.Item
 INNER JOIN Trn_FinRecibos AS R  ON D.TipDoc=R.TipDoc AND D.Recibo=R.Recibo AND D.IdCiaDoc=R.IdCia
 WHERE D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
 AND D.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUptm_PreExtoCuo] @pmtmEst CHAR(2),@pmtmTipoRep INT,@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
 AS 
 UPDATE tm_PreExto SET tmCargos=ISNULL((SELECT SUM(VrTotal) FROM Trn_FinCuotas WHERE TipDoc='PR1' AND TipDoc=tmTipDoc AND IdPrestamo=tmNumero
 AND IdCia=tmIdCia AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Concepto='CAPITAL'),0)
--CUOTA
,tmNCuota=ISNULL((SELECT MAX(NumCuota) FROM Trn_FinCuotas WHERE TipDoc='PR1' AND TipDoc=tmTipDoc AND IdPrestamo=tmNumero
 AND IdCia=tmIdCia AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Concepto='CAPITAL'),0)
,tmDTF_EA=ISNULL((SELECT MAX(DTF_EA) FROM Trn_FinCuotas WHERE TipDoc='PR1' AND TipDoc=tmTipDoc AND IdPrestamo=tmNumero
 AND IdCia=tmIdCia AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Concepto='CAPITAL'),0)
,tmTasaNom=ISNULL((SELECT MAX(TasaNom) FROM Trn_FinCuotas WHERE TipDoc='PR1' AND TipDoc=tmTipDoc AND IdPrestamo=tmNumero
 AND IdCia=tmIdCia AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Concepto='CAPITAL'),0)
,tmTasaEfe=ISNULL((SELECT MAX(TasaEfe) FROM Trn_FinCuotas WHERE TipDoc='PR1' AND TipDoc=tmTipDoc AND IdPrestamo=tmNumero
 AND IdCia=tmIdCia AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Concepto='CAPITAL'),0)
 WHERE tmEst=@pmtmEst AND tmTipoRep=@pmtmTipoRep AND tmTipDoc='PR1' AND tmConcepto='CAPITAL'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_PreExtoPtmo] @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,TipDoc,IdPrestamo,IdCia,0,FecPrestamo,IdCliente,IdAgencia,0,0,IdLinea,'CAPITAL',Observacion,NumCredito,TipoCredito,VrPrestamo,VrCuota
,PagosCapital,AcumCuotas,IdVend,TimeSys,FecCorte,-1,0,0,0,TipDoc,NumPrestRef,ISNULL(CiaPrestRef,''),TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev
,0,0,0,0,0,0,0,0
 FROM Trn_FinPrestamo
 WHERE FecPrestamo BETWEEN @pmFechaIni AND @pmFechaFin 
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND IdCia LIKE ISNULL(@pmIdCia,'%%')