if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreCartera_Apo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_PreCartera_Apo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFinConveniosFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryFinConveniosFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreMovi_Apo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_PreMovi_Apo]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_PreCartera_Apo] @pmtmEst CHAR(2),@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null
,@pmIdVend VARCHAR(16)=Null,@pmIdLinea VARCHAR(4)=Null
 AS 
 INSERT INTO tm_PreCartera (tmEst,TipDoc,IdPrestamo,IdCia,Item,NumCuota,Fecha,FechaVence,VrTotal,VrAbonado,VrSaldo,TipoTasa,DTF_EA,TasaNom,Spread_TA,TasaEfe
,IdCliente,IdAgencia,IdVend,Concepto,TipoCuota,NumCausacion,IdCiaCau,Detalle,IdConcepto,FecUltPago,FecLiqMora,FechaCrea,CtaForma,Beneficiario,RefCheque,NumCheque,CdBanco)
 SELECT @pmtmEst,C.TipDoc,C.IdPrestamo,C.IdCia,C.Item,C.NumCuota,C.Fecha,C.FechaVence,C.VrTotal,C.VrAbonado,C.VrTotal-C.VrAbonado,C.TipoTasa,C.DTF_EA,C.TasaNom,C.Spread_TA,C.TasaEfe
,C.IdCliente,C.IdAgencia,CL.IdVend,C.Concepto,C.TipoCuota,C.NumCausacion,C.IdCiaCau,C.Detalle,'0',C.FecUltPago,C.FecLiqMora,C.FechaCrea,'0','','','',CL.IdBanco
 FROM Trn_FinCuotas AS C LEFT JOIN TercCliePrestamo AS CL ON C.IdCliente=CL.IdClie AND C.IdAgencia=CL.IdAgencia
 WHERE C.TipDoc IN ('APO','CVN') AND VrTotal>VrAbonado AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND C.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND C.CodLinCred LIKE ISNULL(@pmIdLinea,'%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryFinConveniosFmt] @pmTipLiq VARCHAR(3),@pmNumLiquidaIni INT,@pmNumLiquidaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT C.TipLiq,C.NumLiquida,C.IdCia,Compania,C.Fecha,C.IdConcepto,CN.Concepto,C.IdProveedor,T.RazonSocial,C.NumFactura,C.FecFactura,C.FecVence,C.ValorTotal
,C.TipCom,TipoCom,C.Comprobante,C.IdCiaCom,C.Observacion,C.Anulado,C.NumDev,C.FecDev,C.FechaCrea,C.IdCiaCrea,C.OrigenAdd,C.IdUsuario,Usuario
,CU.Item,CU.Fecha AS FecEmision,CU.FechaVence,CU.VrTotal,CU.VrAbonado,CU.IdCliente,TC.RazonSocial AS NomCliente,CU.IdAgencia AS CodAgencia,CL.NomAgencia,CU.Detalle,CU.FecUltPago,CU.NumFactura AS CodCuenta
 FROM Trn_FinConvenios AS C INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia
 INNER JOIN Conceptos AS CN ON C.IdConcepto=CN.IdConcepto INNER JOIN Terceros AS T ON C.IdProveedor=T.IdTercero  
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN Trn_FinCuotas AS CU ON C.TipLiq=CU.TipDoc AND C.NumLiquida=CU.IdPrestamo AND C.IdCia=CU.IdCia
 INNER JOIN Terceros AS TC ON CU.IdCliente=TC.IdTercero
 LEFT JOIN TercCliePrestamo AS CL ON CU.IdCliente=CL.IdClie AND CU.IdAgencia=CL.IdAgencia
 LEFT JOIN TiposCom AS TCO ON C.TipCom=TCO.IdCom
 WHERE C.TipLiq=@pmTipLiq AND C.NumLiquida BETWEEN @pmNumLiquidaIni AND @pmNumLiquidaFin AND C.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE  [dbo].[paInstm_PreMovi_Apo]  @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCliente VARCHAR(16)=Null
,@pmIdAgencia VARCHAR(16)=Null,@pmIdCia CHAR(2)=Null
 AS 
 INSERT INTO tm_PreMovi (tmEst,tmTipDoc,tmNumero,tmIdCia,tmItem,tmFecha,tmIdCliente,tmIdAgencia,tmIdVend,tmCargos,tmAbonos,tmNumCuota,tmTimeSys,tmFecVence,tmCdConcepto,tmConcepto,tmDetalle,tmReferencia
,tmTipoTasa,tmDTF_EA,tmTasaNom,tmPuntos,tmTasaEfe,tmTipRef,tmDocRef,tmIdCiaRef,tmNumForma,tmCdBanco,tmBeneficiario,tmCdForma,tmEnEfectivo,tmTotalAbono,tmVrAfavor,tmTipCom,tmComprobante,tmIdCiaCom
,tmAnulado,tmNumDev,tmFecDev,tmIdUsuario,tmSanClie,tmSanClieCia,tmSanAge,tmSanAgeCia)
 SELECT @pmtmEst,C.TipDoc,C.IdPrestamo,C.IdCia,C.Item,C.Fecha,C.IdCliente,C.IdAgencia,CL.IdVend,C.VrTotal,0,C.NumCuota,C.FechaCrea,C.FechaVence,'0',C.Concepto,C.Detalle,C.Concepto
,C.TipoTasa,C.DTF_EA,C.TasaNom,C.Spread_TA,C.TasaEfe,'CAU',C.NumCausacion,C.IdCiaCau,C.NumFactura,CL.IdBanco,'','0',0,C.VrAbonado,0,'0',0,'00'
,0,0,Null,CL.IdUsuario,0,0,0,0
 FROM Trn_FinCuotas AS C LEFT JOIN TercCliePrestamo AS CL ON C.IdCliente=CL.IdClie AND C.IdAgencia=CL.IdAgencia
 WHERE C.TipDoc IN ('APO','CVN') AND C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND C.IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND C.IdAgencia LIKE ISNULL(@pmIdAgencia,'%') AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO