if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAgencias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsAgencias]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsMayCesion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsMayCesion]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsMayContratos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsMayContratos]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTercCliePrestamo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTercCliePrestamo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_ccCupones]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_ccCupones]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAgencias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryAgencias]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAgenciasCod]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryAgenciasCod]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAgenciasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryAgenciasLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMayContratos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryMayContratos]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMayContratos_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryMayContratos_Cr]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMayContratosAct]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryMayContratosAct]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMayContratosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryMayContratosLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMayContratosRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryMayContratosRel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercCliePrestamo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTercCliePrestamo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercCliePrestamoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTercCliePrestamoLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_ccCupones]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrytm_ccCupones]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAgencias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpAgencias]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpMayContratos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpMayContratos]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTercCliePrestamo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpTercCliePrestamo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spFinCuotas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spFinCuotas]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsAgencias @pmIdAgencia VARCHAR(16),@pmCodAgencia VARCHAR(16),@pmIdClie VARCHAR(16),@pmAgencia VARCHAR(150),@pmDirAgncia VARCHAR(250),@pmIdLocal VARCHAR(8),@pmTelAgncia VARCHAR(20),@pmFaxAgncia VARCHAR(20),@pmNitCont VARCHAR(16)
,@pmNomCont VARCHAR(150),@pmemlCont VARCHAR(100),@pmCargoCont VARCHAR(50),@pmIdSzona VARCHAR(4),@pmIdPlazo VARCHAR(4),@pmCdPlazoComb VARCHAR(4),@pmIdForma VARCHAR(4),@pmCdBandera VARCHAR(4),@pmIdVend VARCHAR(16),@pmCdCms VARCHAR(4),@pmCdDct VARCHAR(4),@pmIntMora DECIMAL(14,4)
,@pmDiasGracia INT,@pmFactFletes BIT,@pmFactSold BIT,@pmVrCupoCre MONEY,@pmVrSaldoAct MONEY,@pmFecUpCupo SMALLDATETIME,@pmNContrato INT,@pmCiaCont CHAR(2),@pmFecIngreso SMALLDATETIME,@pmReferencia VARCHAR(50),@pmComentarios VARCHAR(250),@pmCupoGalones DECIMAL(14,4)
,@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmTipoZona VARCHAR(10),@pmCodSicom VARCHAR(20),@pmCdTipAgenc VARCHAR(4),@pmCdSubAgenc VARCHAR(4),@pmCdGrupAgenc VARCHAR(4), @pmCdSubGrupAgenc VARCHAR(4), @pmCdFntePago VARCHAR(4), @pmPlazoSobtasa VARCHAR(20)
,@pmDocFinanc VARCHAR(3), @pmVolContMes DECIMAL(14,4), @pmVolContTotal DECIMAL(14,4), @pmVolViabMes DECIMAL(14,4),@pmVolViabTotal DECIMAL(14,4), @pmFecPriVenta SMALLDATETIME, @pmInvCtbObra MONEY, @pmInvCtbCapital MONEY, @pmInvCtbEfectivo MONEY, @pmInvCtbImagen MONEY, @pmInvCtbMntmto MONEY
,@pmFecDesemb SMALLDATETIME, @pmInvContObra MONEY, @pmInvContCapital MONEY, @pmInvContEfectivo MONEY, @pmInvContImagen MONEY, @pmCrtlAprovInv INT, @pmCrtlAprovImg INT,@pmFecVigCond SMALLDATETIME,@pmTasaIntArranq DECIMAL(14,4),@pmAgencOperando BIT,@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Agencias (IdAgencia,CodAgencia,IdClie,Agencia,DirAgncia,IdLocal,TelAgncia,FaxAgncia,NitCont,NomCont,emlCont,CargoCont,IdSzona,IdPlazo,CdPlazoComb,IdForma,CdBandera,IdVend,CdCms,CdDct,IntMora,DiasGracia,FactFletes,FactSold,VrCupoCre,VrSaldoAct,FecUpCupo,NContrato,CiaCont,CodSicom,FecIngreso,Referencia,Comentarios
,CupoGalones,TipoZona,IdEstado,Inactivo,FechaAdd,IdUsuario,CdTipAgenc, CdSubAgenc, CdGrupAgenc, CdSubGrupAgenc, CdFntePago, PlazoSobtasa, DocFinanc, VolContMes, VolContTotal, VolViabMes,VolViabTotal, FecPriVenta, InvCtbObra, InvCtbCapital, InvCtbEfectivo, InvCtbImagen, InvCtbMntmto, FecDesemb, InvContObra, InvContCapital
,InvContEfectivo, InvContImagen, CrtlAprovInv, CrtlAprovImg,FecVigCond,TasaIntArranq,AgencOperando)
 VALUES (@pmIdAgencia,@pmCodAgencia,@pmIdClie,@pmAgencia,@pmDirAgncia,@pmIdLocal,@pmTelAgncia,@pmFaxAgncia,@pmNitCont,@pmNomCont,@pmemlCont,@pmCargoCont,@pmIdSzona,@pmIdPlazo,@pmCdPlazoComb,@pmIdForma,@pmCdBandera,@pmIdVend
,@pmCdCms,@pmCdDct,@pmIntMora,@pmDiasGracia,@pmFactFletes,@pmFactSold,@pmVrCupoCre,@pmVrSaldoAct,@pmFecUpCupo,@pmNContrato,@pmCiaCont,@pmCodSicom,@pmFecIngreso,@pmReferencia,@pmComentarios,@pmCupoGalones,@pmTipoZona,@pmIdEstado,@pmInactivo,@pmFechaAdd,@pmIdUsuario
,@pmCdTipAgenc, @pmCdSubAgenc,@pmCdGrupAgenc,@pmCdSubGrupAgenc,@pmCdFntePago,@pmPlazoSobtasa,@pmDocFinanc,@pmVolContMes,@pmVolContTotal,@pmVolViabMes,@pmVolViabTotal,@pmFecPriVenta
,@pmInvCtbObra, @pmInvCtbCapital,@pmInvCtbEfectivo,@pmInvCtbImagen,@pmInvCtbMntmto,@pmFecDesemb,@pmInvContObra,@pmInvContCapital,@pmInvContEfectivo,@pmInvContImagen,@pmCrtlAprovInv,@pmCrtlAprovImg,@pmFecVigCond,@pmTasaIntArranq,@pmAgencOperando)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsMayCesion @pmNumero INT,@pmFecha SMALLDATETIME,@pmNContrato INT,@pmIdCia CHAR(2),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdNueClie VARCHAR(16),@pmIdNueAgenc VARCHAR(16)
,@pmFecInicio SMALLDATETIME,@pmClaseCont VARCHAR(10),@pmCdTipoCont VARCHAR(4),@pmCuotaBase DECIMAL(14,4),@pmCuotaMin DECIMAL(14,4),@pmAcumGals DECIMAL(14,4),@pmObservacion VARCHAR(250),@pmTimeSys SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_MayCesion (Numero,Fecha,NContrato,IdCia,IdCliente,IdAgencia,IdNueClie,IdNueAgenc,FecInicio,ClaseCont,CdTipoCont,CuotaBase,CuotaMin,AcumGals,Observacion,TimeSys,IdUsuario)
 VALUES (@pmNumero,@pmFecha,@pmNContrato,@pmIdCia,@pmIdCliente,@pmIdAgencia,@pmIdNueClie,@pmIdNueAgenc,@pmFecInicio,@pmClaseCont,@pmCdTipoCont,@pmCuotaBase,@pmCuotaMin,@pmAcumGals,@pmObservacion,@pmTimeSys,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsMayContratos @pmNContrato INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdTipo VARCHAR(4),@pmIdBonif VARCHAR(4),@pmConvBonif BIT,@pmCuotaFija BIT
,@pmCondPago BIT,@pmDiasGracia INT,@pmModPlazo VARCHAR(10),@pmPlazo INT,@pmFechaVence SMALLDATETIME,@pmModRecursos VARCHAR(150),@pmVrInversion MONEY,@pmVrInventario MONEY,@pmVrCuota MONEY,@pmModCuota VARCHAR(10)
,@pmIdBandera VARCHAR(4),@pmAprobJunta INT,@pmFechaAprob SMALLDATETIME,@pmObservAprob VARCHAR(250),@pmObservacion VARCHAR(250),@pmIdVend VARCHAR(16),@pmIdEstado VARCHAR(4),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
,@pmFechaInicio SMALLDATETIME,@pmCuotaMin DECIMAL(14,4),@pmTipoAprob VARCHAR(10),@pmNumInterno VARCHAR(20),@pmInvCombus DECIMAL(14,4),@pmCuotaBase DECIMAL(14,4),@pmFechaFirma SMALLDATETIME,@pmCdAprobNeg VARCHAR(4),@pmArchivoAprob VARCHAR(50)
,@pmClaseContrato VARCHAR(10),@pmDescTipoCont VARCHAR(250),@pmGarantHipoteca BIT,@pmGarantPrenda BIT,@pmGarantPoliza BIT,@pmGarantBanco BIT,@pmGarantPagare BIT,@pmGarantOtro BIT,@pmCdTipoBien VARCHAR(4),@pmVrAvaludo MONEY,@pmNroEscritura VARCHAR(50)
,@pmCdTipoPoliza VARCHAR(4),@pmDescPoliza VARCHAR(150),@pmVigencPoliza INT,@pmFecVencPoliza SMALLDATETIME,@pmValorPoliza MONEY,@pmEdoGarantias INT,@pmDescGarantOtro VARCHAR(250),@pmInverContab INT,@pmInverContrac INT,@pmVolContMes DECIMAL(14,4)
,@pmVolContTotal DECIMAL(14,4),@pmVolViabMes DECIMAL(14,4),@pmVolViabTotal DECIMAL(14,4),@pmInvCtbObra MONEY,@pmInvCtbCapital MONEY,@pmInvCtbEfectivo MONEY,@pmInvCtbImagen MONEY,@pmInvCtbMntmto MONEY,@pmFecDesemb SMALLDATETIME,@pmInvContObra MONEY
,@pmInvContCapital MONEY,@pmInvContEfectivo MONEY,@pmInvContImagen MONEY,@pmCrtlAprovInv INT,@pmCrtlAprovImg INT,@pmCdTipoBloq VARCHAR(4),@pmEdoDocmtos INT,@pmDescEdoDoc VARCHAR(250),@pmRentabInicial DECIMAL(14,4),@pmGraciaBonif INT
,@pmActFechaIni INT,@pmCdZonaCont VARCHAR(4),@pmContVigente BIT,@pmVolContrac INT,@pmDescHipotec VARCHAR(250),@pmVrAvaComerc MONEY,@pmFecRegEscritura SMALLDATETIME,@pmNotariaEsc VARCHAR(50),@pmCdTipPrenda VARCHAR(4),@pmVrAvaPrenda MONEY
,@pmPolizaPrenda VARCHAR(30),@pmFecVigPrenda SMALLDATETIME,@pmVrAsegPrenda MONEY,@pmContOperando BIT
 AS 
 INSERT INTO Trn_MayContratos (NContrato,IdCia,Fecha,IdCliente,IdAgencia,IdTipo,IdBonif,ConvBonif,CuotaFija,CondPago,DiasGracia,ModPlazo,Plazo,FechaVence,ModRecursos,VrInversion,VrInventario,VrCuota,ModCuota,IdBandera,AprobJunta,FechaAprob
,ObservAprob,Observacion,IdVend,IdEstado,TimeSys,IdCiaCrea,IdUsuario,FechaInicio,CuotaMin,TipoAprob,NumInterno,InvCombus,CuotaBase
,FechaFirma,CdAprobNeg,ArchivoAprob,ClaseContrato,DescTipoCont,GarantHipoteca,GarantPrenda,GarantPoliza,GarantBanco,GarantPagare,GarantOtro,CdTipoBien,VrAvaludo,NroEscritura,CdTipoPoliza,DescPoliza,VigencPoliza,FecVencPoliza,ValorPoliza,EdoGarantias,DescGarantOtro,InverContab,InverContrac
,VolContMes,VolContTotal,VolViabMes,VolViabTotal,InvCtbObra,InvCtbCapital,InvCtbEfectivo,InvCtbImagen,InvCtbMntmto,FecDesemb,InvContObra,InvContCapital,InvContEfectivo,InvContImagen,CrtlAprovInv,CrtlAprovImg,CdTipoBloq,EdoDocmtos,DescEdoDoc,RentabInicial,GraciaBonif
,ActFechaIni,CdZonaCont,ContVigente,VolContrac,DescHipotec,VrAvaComerc,FecRegEscritura,NotariaEsc,CdTipPrenda,VrAvaPrenda,PolizaPrenda,FecVigPrenda,VrAsegPrenda,ContOperando)
 VALUES (@pmNContrato,@pmIdCia,@pmFecha,@pmIdCliente,@pmIdAgencia,@pmIdTipo,@pmIdBonif,@pmConvBonif,@pmCuotaFija,@pmCondPago,@pmDiasGracia,@pmModPlazo,@pmPlazo,@pmFechaVence,@pmModRecursos,@pmVrInversion
,@pmVrInventario,@pmVrCuota,@pmModCuota,@pmIdBandera,@pmAprobJunta,@pmFechaAprob,@pmObservAprob,@pmObservacion,@pmIdVend,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmFechaInicio,@pmCuotaMin,@pmTipoAprob,@pmNumInterno,@pmInvCombus,@pmCuotaBase
,@pmFechaFirma,@pmCdAprobNeg,@pmArchivoAprob,@pmClaseContrato,@pmDescTipoCont,@pmGarantHipoteca,@pmGarantPrenda,@pmGarantPoliza,@pmGarantBanco,@pmGarantPagare,@pmGarantOtro,@pmCdTipoBien,@pmVrAvaludo,@pmNroEscritura,@pmCdTipoPoliza,@pmDescPoliza,@pmVigencPoliza,@pmFecVencPoliza,@pmValorPoliza
,@pmEdoGarantias,@pmDescGarantOtro,@pmInverContab,@pmInverContrac,@pmVolContMes,@pmVolContTotal,@pmVolViabMes,@pmVolViabTotal,@pmInvCtbObra,@pmInvCtbCapital,@pmInvCtbEfectivo,@pmInvCtbImagen,@pmInvCtbMntmto,@pmFecDesemb,@pmInvContObra,@pmInvContCapital,@pmInvContEfectivo,@pmInvContImagen
,@pmCrtlAprovInv,@pmCrtlAprovImg,@pmCdTipoBloq,@pmEdoDocmtos,@pmDescEdoDoc,@pmRentabInicial,@pmGraciaBonif,@pmActFechaIni,@pmCdZonaCont,@pmContVigente,@pmVolContrac,@pmDescHipotec,@pmVrAvaComerc,@pmFecRegEscritura,@pmNotariaEsc,@pmCdTipPrenda,@pmVrAvaPrenda,@pmPolizaPrenda,@pmFecVigPrenda,@pmVrAsegPrenda,@pmContOperando)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsTercCliePrestamo @pmIdClie VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmNitRepLeg VARCHAR(16),@pmNomRepLeg VARCHAR(150),@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100)
,@pmCargContac VARCHAR(50),@pmIdSzona VARCHAR(4),@pmIdGrupo VARCHAR(4),@pmIdTipoTerc VARCHAR(4),@pmIdVend VARCHAR(16),@pmIdClase VARCHAR(4),@pmNumCuenta VARCHAR(30),@pmIdBanco VARCHAR(4),@pmEsExento BIT,@pmCalcIntMora BIT,@pmDiasGracia INT
,@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmVrSalBasico MONEY,@pmMaxDcto DECIMAL(14,4),@pmMatMerc VARCHAR(20),@pmFecMat SMALLDATETIME,@pmNomAgencia VARCHAR(150),@pmDirAgencia VARCHAR(250)
,@pmIdLocalAge VARCHAR(8),@pmComentarios VARCHAR(2000),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmTipoSalario CHAR(1),@pmNumTarj VARCHAR(30),@pmPwdTarj VARCHAR(30),@pmFecVenceTc SMALLDATETIME,@pmFechaCrea SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO TercCliePrestamo (IdClie,IdAgencia,IdSzona,IdGrupo,IdTipoTerc,IdVend,IdClase,NumCuenta,IdBanco,NomAgencia,DirAgencia,IdLocalAge,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,EsExento,CalcIntMora,DiasGracia,FecIngreso,FecVigencia,FecRetiro,VrSalBasico
,MaxDcto,TipoSalario,MatMerc,FecMat,Comentarios,IdEstado,Inactivo,FechaCrea,IdUsuario,NumTarj,PwdTarj,FecVenceTc)
 VALUES (@pmIdClie,@pmIdAgencia,@pmIdSzona,@pmIdGrupo,@pmIdTipoTerc,@pmIdVend,@pmIdClase,@pmNumCuenta,@pmIdBanco,@pmNomAgencia,@pmDirAgencia,@pmIdLocalAge,@pmNitRepLeg,@pmNomRepLeg,@pmNitContac,@pmNomContac,@pmTelContac,@pmemlContac,@pmCargContac,@pmEsExento
,@pmCalcIntMora,@pmDiasGracia,@pmFecIngreso,@pmFecVigencia,@pmFecRetiro,@pmVrSalBasico,@pmMaxDcto,@pmTipoSalario,@pmMatMerc,@pmFecMat,@pmComentarios,@pmIdEstado,@pmInactivo,@pmFechaCrea,@pmIdUsuario,@pmNumTarj,@pmPwdTarj,@pmFecVenceTc)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_ccCupones @pmtmEst CHAR(2),@pmtmItem INT,@pmtmTipoReg INT,@pmtmTipDoc VARCHAR(3),@pmtmDocumento INT,@pmtmIdCia CHAR(2),@pmtmItemDoc INT,@pmtmFecEmision SMALLDATETIME,@pmtmFecVence SMALLDATETIME,@pmtmValorFactura MONEY,@pmtmValorAbono MONEY
,@pmtmIdPropietario VARCHAR(16),@pmtmIdVehiculo VARCHAR(10),@pmtmIdConductor VARCHAR(16),@pmtmIdCargo VARCHAR(4),@pmtmIdClase VARCHAR(4),@pmtmFactura VARCHAR(15),@pmtmReferencia VARCHAR(50),@pmtmDetalle VARCHAR(100),@pmtmDescuento MONEY,@pmtmNCuota INT
 AS 
 INSERT INTO tm_ccCupones (tmEst,tmItem,tmTipoReg,tmTipDoc,tmDocumento,tmIdCia,tmItemDoc,tmFecEmision,tmFecVence,tmValorFactura,tmValorAbono,tmIdPropietario,tmIdVehiculo,tmIdConductor,tmIdCargo,tmIdClase,tmFactura,tmReferencia,tmDetalle,tmDescuento,tmNCuota)
 VALUES (@pmtmEst,@pmtmItem,@pmtmTipoReg,@pmtmTipDoc,@pmtmDocumento,@pmtmIdCia,@pmtmItemDoc,@pmtmFecEmision,@pmtmFecVence,@pmtmValorFactura,@pmtmValorAbono,@pmtmIdPropietario,@pmtmIdVehiculo,@pmtmIdConductor,@pmtmIdCargo,@pmtmIdClase
,@pmtmFactura,@pmtmReferencia,@pmtmDetalle,@pmtmDescuento,@pmtmNCuota)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAgencias @pmIdAgencia VARCHAR(16)
 AS 
 SELECT IdAgencia,CodAgencia,IdClie,Agencia,DirAgncia,IdLocal,TelAgncia,FaxAgncia,NitCont,NomCont,emlCont
,CargoCont,IdSzona,IdPlazo,CdPlazoComb,IdForma,CdBandera,IdVend,CdCms,CdDct,IntMora,DiasGracia,FactFletes,FactSold
,VrCupoCre,VrSaldoAct,FecUpCupo,NContrato,CiaCont,FecIngreso,CodSicom,Referencia,Comentarios,CupoGalones
,IdEstado,Inactivo,TipoZona,FechaAdd,FechaUpdate,IdUsuario,CdTipAgenc,CdSubAgenc,CdGrupAgenc,CdSubGrupAgenc,CdFntePago,PlazoSobtasa,DocFinanc,VolContMes
,VolContTotal,VolViabMes,VolViabTotal,FecPriVenta,InvCtbObra,InvCtbCapital,InvCtbEfectivo,InvCtbImagen,InvCtbMntmto,FecDesemb,InvContObra,InvContCapital,InvContEfectivo
,InvContImagen,CrtlAprovInv,CrtlAprovImg,FecVigCond,TasaIntArranq,AgencOperando
 FROM Agencias
 WHERE IdAgencia=@pmIdAgencia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAgenciasCod @pmIdClie VARCHAR(16),@pmCodAgencia VARCHAR(16)
 AS 
 SELECT IdAgencia,CodAgencia,IdClie,Agencia,DirAgncia,IdLocal,TelAgncia,FaxAgncia,NitCont,NomCont,emlCont
,CargoCont,IdSzona,IdPlazo,CdPlazoComb,IdForma,CdBandera,IdVend,CdCms,CdDct,IntMora,DiasGracia,FactFletes,FactSold
,VrCupoCre,VrSaldoAct,FecUpCupo,NContrato,CiaCont,FecIngreso,CodSicom,Referencia,Comentarios,CupoGalones
,TipoZona,IdEstado,Inactivo,FechaAdd,FechaUpdate,IdUsuario
,CdTipAgenc,CdSubAgenc,CdGrupAgenc,CdSubGrupAgenc,CdFntePago,PlazoSobtasa,DocFinanc,VolContMes
,VolContTotal,VolViabMes,VolViabTotal,FecPriVenta,InvCtbObra,InvCtbCapital,InvCtbEfectivo,InvCtbImagen,InvCtbMntmto,FecDesemb,InvContObra,InvContCapital,InvContEfectivo
,InvContImagen,CrtlAprovInv,CrtlAprovImg,FecVigCond,TasaIntArranq,AgencOperando
 FROM Agencias
 WHERE IdClie=@pmIdClie AND CodAgencia LIKE ISNULL(@pmCodAgencia,'%')
 ORDER BY IdAgencia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAgenciasLta @pmIdClie VARCHAR(16)=Null,@pmIdLocal VARCHAR(8)=Null,@pmIdGrupo VARCHAR(4)=Null
,@pmIdZona VARCHAR(4)=Null,@pmIdSzona VARCHAR(4)=Null,@pmIdEstrato VARCHAR(4)=Null,@pmIdEstado VARCHAR(4)=Null
,@pmIdSector VARCHAR(8)=Null,@pmInactivo BIT=Null
 AS 
 SELECT A.IdClie AS NitCliente,RazonSocial,IdAgencia,CodAgencia,Agencia,DirAgncia,A.IdLocal AS CdCiuAgncia,L.Localidad AS CiudadAgncia
,L.IdDep AS CdDptoAgncia,D.Departamento AS DptoAgncia,TelAgncia,FaxAgncia,NitCont,NomCont,emlCont,CargoCont,VrCupoCre,VrSaldoAct,A.FecUpCupo AS FechaUpCupo,Referencia
,CodSicom,A.NContrato AS NroCont,CiaCont,A.FecIngreso AS Fec_Ingreso,NomCial,SiglaRaz,SZA.IdZona AS Age_CdZona,ZA.Zona AS Age_Zona,A.IdSzona AS Age_IdSubzona,SZA.Subzona AS Age_Subzona
,A.IdPlazo AS CdPlazo,Plazo,NVmto,A.CdPlazoComb AS CdPlazoComb,A.IdForma AS CdForma,FormaPago,A.IdVend AS NitVend,A.CdCms AS CodCms,A.CdDct AS CodDct,IntMora,A.DiasGracia AS Dias_gracia,FactFletes,A.FactSold AS FacturaSold
,A.CupoGalones AS CupoGals
,TipoId,Dv,Direccion,T.IdLocal AS CdCiuClie,LT.Localidad AS CiuClie,LT.IdDep AS CdDptoClie,DT.Departamento AS DptoClie
,NitRepLeg,NomRepLeg,NitContac,NomContac,C.IdGrupo AS CdGrupo,GrupoClie,SZ.IdZona AS CdZona,Z.Zona AS CliZona,C.IdSzona AS CdSubzona,SZ.Subzona AS CliSubZona
,A.CdBandera AS CodBandera,TipoBandera,C.IdEstrato AS CdEstrato,Estrato,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRgmen,Regimen,TipEnte,VrCupo,VrSaldo,Telefono,Fax
,TelMovil,e_mail,C.IdEstado AS CdEstado,Estado,C.Inactivo AS Clie_inactivo,A.IdEstado AS Age_Estado,A.Inactivo AS Age_inactivo,IniStgNom,A.FechaAdd AS FecAdd
,TipoZona,Comentarios,A.FechaUpdate AS FecUpd,A.IdUsuario AS IdUsuari,Usuario
--datos nuevos
,CdTipAgenc,CdSubAgenc,CdGrupAgenc,CdSubGrupAgenc,CdFntePago,PlazoSobtasa,DocFinanc,VolContMes
,VolContTotal,VolViabMes,VolViabTotal,FecPriVenta,InvCtbObra,InvCtbCapital,InvCtbEfectivo,InvCtbImagen,InvCtbMntmto,FecDesemb,InvContObra,InvContCapital,InvContEfectivo
,InvContImagen,CrtlAprovInv,CrtlAprovImg,FecVigCond,TasaIntArranq,AgencOperando
 FROM Agencias AS A INNER JOIN Terceros AS T ON A.IdClie=T.IdTercero
 INNER JOIN TercCliente AS C ON A.IdClie=C.IdClie INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS L ON A.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 INNER JOIN Subzonas AS SZA ON A.IdSzona=SZA.IdSzona INNER JOIN Zonas AS ZA ON SZA.IdZona=ZA.IdZona
 INNER JOIN Localidades AS LT ON T.IdLocal=LT.IdLocal  INNER JOIN Departamentos AS DT ON LT.IdDep=DT.IdDep
 INNER JOIN GruposCli AS G ON C.IdGrupo=G.IdGrupo INNER JOIN Subzonas AS SZ ON C.IdSzona=SZ.IdSzona
 INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona INNER JOIN Estratos AS EC ON C.IdEstrato=EC.IdEstrato
 INNER JOIN SectoresEco AS S ON T.IdSector=S.IdSector INNER JOIN RegimenDian AS R ON T.IdRegimen=R.IdRegimen
 INNER JOIN EstadoTer AS E ON C.IdEstado=E.IdEstado INNER JOIN Plazos AS PZ ON A.IdPlazo=PZ.IdPlazo
 INNER JOIN Formaspago AS FP ON A.IdForma=FP.IdForma
 LEFT JOIN TiposBan AS TB ON A.CdBandera=TB.IdBandera
 WHERE A.Inactivo=0 AND A.IdClie LIKE ISNULL(@pmIdClie,'%') AND A.IdLocal LIKE ISNULL(@pmIdLocal,'%')
 AND C.IdGrupo LIKE ISNULL(@pmIdGrupo ,'%') AND SZA.IdZona LIKE ISNULL(@pmIdZona,'%') AND A.IdSzona LIKE ISNULL(@pmIdSzona,'%')
 AND C.IdEstrato LIKE ISNULL(@pmIdEstrato,'%') AND A.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND T.IdSector LIKE ISNULL(@pmIdSector,'%')
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY RazonSocial,Agencia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryMayContratos @pmNContrato INT,@pmIdCia CHAR(2)
 AS 
 SELECT NContrato,IdCia,Fecha,IdCliente,IdAgencia,IdTipo,IdBonif,ConvBonif,CuotaFija,CondPago,DiasGracia,ModPlazo,Plazo
,FechaVence,ModRecursos,VrInversion,VrInventario,VrCuota,ModCuota,IdBandera,IdVend,AprobJunta,FechaAprob,ObservAprob
,Observacion,IdEstado,FechaInicio,CuotaMin,TipoAprob,NumInterno,InvCombus,CuotaBase,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
,FechaFirma,CdAprobNeg,ArchivoAprob,ClaseContrato,DescTipoCont,GarantHipoteca,GarantPrenda,GarantPoliza,GarantBanco,GarantPagare
,GarantOtro,CdTipoBien,VrAvaludo,NroEscritura,CdTipoPoliza,DescPoliza,VigencPoliza,FecVencPoliza,ValorPoliza,EdoGarantias
,DescGarantOtro,InverContab,InverContrac,VolContMes,VolContTotal,VolViabMes,VolViabTotal,InvCtbObra,InvCtbCapital,InvCtbEfectivo,InvCtbImagen
,InvCtbMntmto,FecDesemb,InvContObra,InvContCapital,InvContEfectivo,InvContImagen,CrtlAprovInv,CrtlAprovImg,CdTipoBloq,EdoDocmtos,DescEdoDoc,RentabInicial,GraciaBonif
,ActFechaIni,CdZonaCont,ContVigente,VolContrac,DescHipotec,VrAvaComerc,FecRegEscritura,NotariaEsc,CdTipPrenda,VrAvaPrenda,PolizaPrenda,FecVigPrenda,VrAsegPrenda,ContOperando
 FROM Trn_MayContratos
 WHERE NContrato=@pmNContrato AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryMayContratos_Cr  @pmNContratoIni INT=Null,@pmNContratoFin INT=Null,@pmIdCia CHAR(2)=Null
 AS 
 SELECT C.NContrato AS NumContrato,C.IdCia AS CdCia,Compania,NumInterno,Fecha,FechaInicio,ActFechaIni,FechaFirma,IdCliente,T.RazonSocial AS NomCliente,C.IdAgencia AS CdAgencia,Agencia
,ClaseContrato,C.IdTipo AS CdTipo,TipContrato,DescTipoCont,C.IdBonif AS CdBonif,TipoBonif,ConvBonif,CuotaFija,CondPago,C.DiasGracia AS Dias_Gracia
,ModPlazo,C.Plazo AS PlazoCont,FechaVence,VrCuota,ModCuota,CuotaMin,CuotaBase,GraciaBonif,C.IdBandera AS CdBandera,TipoBandera,CdZonaCont,TZN.TipoZona AS TipoZonaCont
,C.IdVend AS CdVend,V.RazonSocial AS Vendedor,AprobJunta,FechaAprob,ObservAprob,CdAprobNeg,TA.TipoAprob AS Tipo_Aprob,ArchivoAprob,C.Observacion AS Observ
,GarantHipoteca,GarantPrenda,GarantPoliza,GarantBanco,GarantPagare,GarantOtro,CdTipoBien,TipoBien,VrAvaludo,VrAvaComerc,NroEscritura,FecRegEscritura,NotariaEsc,DescHipotec
,CdTipoPoliza,TipoPoliza,DescPoliza,VigencPoliza,FecVencPoliza,ValorPoliza,CdTipPrenda,TipoPrenda,VrAvaPrenda,PolizaPrenda,FecVigPrenda,VrAsegPrenda,EdoGarantias,DescGarantOtro
,VolContrac,C.VolContMes AS VlmContMes,C.VolContTotal AS VlmContTotal,C.VolViabMes AS VlmViabMes,C.VolViabTotal AS VlmViabTotal,InverContab,InverContrac,C.InvCtbObra AS InvCntbObra,C.InvCtbCapital AS InvCntbCapital
,C.InvCtbEfectivo AS InvCntbEfe,C.InvCtbImagen AS InvCntbImagen,C.InvCtbMntmto AS InvCntbMnto,C.InvContObra AS InvConObra,C.InvContCapital AS InvConCapital,C.InvContEfectivo AS InvConEfe
,C.InvContImagen AS InvConImagen,C.FecDesemb AS FechaDesemb,C.CrtlAprovInv AS CntrolAprobInv,C.CrtlAprovImg AS CntrolAprobImagen,RentabInicial,ModRecursos,VrInversion,VrInventario,InvCombus
,ContVigente,ContOperando,C.IdEstado AS CdEstado,Estado,NColor,CdTipoBloq,TBO.TipoBloqueo AS ConTipoBloq,EdoDocmtos,C.DescEdoDoc AS ConDescEstdo,TimeSys,FecUpdate,IdCiaCrea,C.IdUsuario AS CdUsuario,Usuario
--datos de bonificaciones
,Item,TipoProd,VrInicial,VrFinal,Unidad,Tarifa,TipoTarifa,Periodo,CdProducto,DescripProd,CdSubgrupo,Subgrupo
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo
,CLI.FecUpCupo AS FechaActCupo,Cmntario1,Cmntario2,Cmntario3,CLI.IdPlazo AS CliCdPlazo,PZ.Plazo AS PlazoCli,CLI.CdPlazoComb AS CdPlazoComb,PZC.Plazo AS PlazoComb,CdTipBloq,TBC.TipoBloqueo AS CliTipoBloq
,CLI.DescEdoDoc AS CliDescEdoDoc,EdoRadicaDoc,FecPlazoDoc
--agencias
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto
,A.Referencia AS AgeReferencia,A.IdPlazo AS AgeCdPlazo,PZA.Plazo AS AgePlazo,A.CdPlazoComb AS AgeCdPlazoComb,PZAC.Plazo AS AgePlazoComb,VrCupoCre,A.FecUpCupo AS AgeFecActCupo,CodSicom
,A.FecIngreso AS AgeFecIngreso,Comentarios,A.TipoZona AS AgeTipoZona,CdTipAgenc,TipoAgencia,CdSubAgenc,SubTipoAgenc,CdGrupAgenc,GrupoAgenc
,CdSubGrupAgenc,SubGrupoAgenc,CdFntePago,FuentePago,PlazoSobtasa,DocFinanc,FecVigCond,TasaIntArranq
 FROM Trn_MayContratos AS C INNER JOIN MayTipos AS TC ON C.IdTipo=TC.IdTipo INNER JOIN MayTiposBonif AS B ON C.IdBonif=B.IdBonif
 INNER JOIN Companias AS CN ON C.IdCia=CN.IdCia  INNER JOIN  MayEstados AS E ON C.IdEstado=E.IdEstado
 INNER JOIN  TiposBan AS TB ON C.IdBandera=TB.IdBandera INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero INNER JOIN TercCliente AS CLI ON C.IdCliente=CLI.IdClie
 INNER JOIN Terceros AS V ON C.IdVend=V.IdTercero 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona  INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona
 INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo  INNER JOIN Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 INNER JOIN Plazos AS PZ ON CLI.IdPlazo=PZ.IdPlazo
 INNER JOIN Trn_MayBonif AS MB ON C.NContrato=MB.NContrato AND C.IdCia=MB.IdCia
 LEFT JOIN  TiposBloq AS TBO ON C.CdTipoBloq=TBO.IdTipBloq LEFT JOIN MayTiposAprob AS TA ON C.CdAprobNeg=TA.IdAprob
 LEFT JOIN MayTiposBienes AS TBN ON C.CdTipoBien=TBN.IdTipBien LEFT JOIN MayTiposPoliza AS TPZ ON C.CdTipoPoliza=TPZ.IdTipoPol
 LEFT JOIN TiposPrend AS TPD ON C.CdTipPrenda=TPD.IdPrend LEFT JOIN TiposZona AS TZN ON C.CdZonaCont=TZN.IdTipZona
 LEFT JOIN Plazos AS PZC ON CLI.CdPlazoComb=PZC.IdPlazo LEFT JOIN TiposBloq AS TBC ON CLI.CdTipBloq=TBC.IdTipBloq
 LEFT JOIN  Agencias   AS A ON C.IdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN Plazos AS PZA ON A.IdPlazo=PZA.IdPlazo LEFT JOIN Plazos AS PZAC ON A.CdPlazoComb=PZAC.IdPlazo
 LEFT JOIN TiposAgenc AS TAG ON A.CdTipAgenc=TAG.IdTipAgenc LEFT JOIN TiposComAgenc AS STA ON A.CdSubAgenc=STA.IdSubAgenc
 LEFT JOIN GruposAgenc AS GA ON A.CdGrupAgenc=GA.IdGrupo LEFT JOIN SubGruposAgenc AS SGA ON A.CdSubGrupAgenc=SGA.IdSubAgenc
 LEFT JOIN FuentesPago AS FP ON A.CdFntePago=FP.IdFuente 
 LEFT JOIN SubGrupos AS SG ON MB.CdSubgrupo=SG.IdSubgrupo  LEFT JOIN ProdMcias AS PM ON MB.CdProducto=PM.IdProducto
 WHERE   C.NContrato BETWEEN ISNULL(@pmNContratoIni,0) AND ISNULL(@pmNContratoFin,2147483647)
 AND  C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 ORDER BY C.NContrato
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryMayContratosAct  @pmIdCia CHAR(2)=Null
 AS 
 SELECT C.NContrato AS NumContrato,IdCia,Fecha,IdCliente,T.RazonSocial AS NomCliente,C.IdAgencia AS CdAgencia,Agencia
,IdTipo,C.IdBonif AS CdBonif,TipoBonif,AboCartera,AboPstmo,ConvBonif,CuotaFija,CondPago,C.DiasGracia AS Dias_Gracia,ModPlazo,C.Plazo AS PlazoCont
,FechaVence,VrCuota,ModCuota,C.IdBandera AS CdBandera,C.IdVend AS CdVend,C.IdEstado AS CdEstado,FechaInicio,CuotaMin,TipoAprob,NumInterno,InvCombus,CuotaBase
,FechaFirma,ClaseContrato,EdoDocmtos,GraciaBonif,ActFechaIni,ContOperando,ContVigente
--datos del cliente
,CL.IdPlazo AS CliCdPlazo,PZ.Plazo AS CliPlazo,PZ.NVmto AS CliNVce,PZ.DiasPago AS CliDiasPago
,CL.IdVend AS CliVend,VrCupo,VrSaldo,CL.IdEstado AS CliEstado
--datos de agencia
,A.IdPlazo AS AgeCdPlazo,PA.Plazo AS AgePlazo,PA.NVmto AS AgeNVce,PA.DiasPago AS AgeDiasPago
,A.IdVend AS AgeCdVend,VrCupoCre,VrSaldoAct,Referencia,A.IdEstado AS AgeEstado,A.Inactivo AS AgeInactivo
,FecVigCond,TasaIntArranq,AgencOperando,DocFinanc
 FROM Trn_MayContratos AS C INNER JOIN MayTiposBonif AS B ON C.IdBonif=B.IdBonif
 INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero  INNER JOIN TercCliente AS CL ON C.IdCliente=CL.IdClie
 INNER JOIN Plazos AS PZ ON CL.IdPlazo=PZ.IdPlazo
 LEFT JOIN  Agencias   AS A ON C.IdAgencia=A.IdAgencia  LEFT JOIN Plazos AS PA ON A.IdPlazo=PA.IdPlazo
 WHERE   C.IdEstado<>'0002' AND C.IdEstado<>'9999' AND AprobJunta>0 
 AND ( ConvBonif<>0 OR CuotaFija<>0) AND CL.Inactivo=0
 AND IdCia LIKE ISNULL(@pmIdCia,'%%')
 ORDER BY IdCia,C.NContrato
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryMayContratosLta @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null,@pmIdTipo VARCHAR(4)=Null
 AS 
 SELECT C.NContrato AS NumContrato,C.IdCia AS CdCia,Compania,Fecha,FechaInicio,IdCliente,T.RazonSocial AS NomCliente,C.IdAgencia AS CdAgencia,Agencia,ClaseContrato,C.IdTipo AS CdTipo,TipContrato
,DescTipoCont,C.IdBonif AS CdBonif,TipoBonif,ConvBonif,CuotaFija,CondPago,C.DiasGracia AS Dias_Gracia,ModPlazo,Plazo,FechaVence,ModRecursos,VrInversion,VrInventario,VrCuota,CuotaMin,CuotaBase,ModCuota
,C.IdBandera AS CdBandera,TipoBandera,C.IdVend AS CdVend,V.RazonSocial AS Vendedor,TipoAprob,AprobJunta,FechaAprob,ObservAprob,C.Observacion AS Observ,NumInterno,InvCombus
,FechaFirma,CdAprobNeg,ArchivoAprob,GarantHipoteca,GarantPrenda,GarantPoliza,GarantBanco,GarantPagare
,GarantOtro,CdTipoBien,VrAvaludo,NroEscritura,CdTipoPoliza,DescPoliza,VigencPoliza,FecVencPoliza,ValorPoliza,EdoGarantias
,DescGarantOtro,InverContab,InverContrac,C.VolContMes AS VlmenContMes,C.VolContTotal AS VlmenContTotal,C.VolViabMes AS VlmenViabMes,C.VolViabTotal AS VlmenViabTotal,C.InvCtbObra AS VrCtbObra,C.InvCtbCapital AS VrCtbCapital
,C.InvCtbEfectivo AS VrCtbEfectivo,C.InvCtbImagen AS VrCtbImagen,C.InvCtbMntmto AS VrCtbMnto,C.FecDesemb AS FechDesemb,C.InvContObra AS VrContObra,C.InvContCapital AS VrContCaptial,C.InvContEfectivo AS VrContEfectivo,C.InvContImagen AS VrContImagen
,C.CrtlAprovInv AS ConCrtlInv,C.CrtlAprovImg AS ConCrtlImag,CdTipoBloq,EdoDocmtos,DescEdoDoc,RentabInicial,GraciaBonif,ActFechaIni,CdZonaCont,ContVigente,VolContrac,DescHipotec,VrAvaComerc,FecRegEscritura
,NotariaEsc,CdTipPrenda,VrAvaPrenda,PolizaPrenda,FecVigPrenda,VrAsegPrenda,ContOperando,C.IdEstado AS CdEstado,Estado,NColor,TimeSys,FecUpdate,IdCiaCrea,C.IdUsuario AS CdUsuario,Usuario
 FROM Trn_MayContratos AS C INNER JOIN MayTipos AS TC ON C.IdTipo=TC.IdTipo
 INNER JOIN MayTiposBonif AS B ON C.IdBonif=B.IdBonif INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero
 INNER JOIN Companias AS CN ON C.IdCia=CN.IdCia  INNER JOIN  MayEstados AS E ON C.IdEstado=E.IdEstado
 INNER JOIN  TiposBan AS TB ON C.IdBandera=TB.IdBandera INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN Terceros AS V ON C.IdVend=V.IdTercero  LEFT JOIN  Agencias   AS A ON C.IdAgencia=A.IdAgencia
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND C.IdTipo LIKE ISNULL(@pmIdTipo,'%') 
 ORDER BY C.NContrato
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryMayContratosRel  @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null,@pmIdTipo VARCHAR(4)=Null
 AS 
 SELECT C.NContrato AS NumContrato,C.IdCia AS CdCia,Compania,Fecha,FechaInicio,FechaFirma,IdCliente,T.RazonSocial AS NomCliente,C.IdAgencia AS CdAgencia,Agencia,ClaseContrato,C.IdTipo AS CdTipo,TipContrato
,DescTipoCont,C.IdBonif AS CdBonif,TipoBonif,ConvBonif,CuotaFija,CondPago,C.DiasGracia AS Dias_Gracia,ModPlazo,C.Plazo AS PlazoContrato,FechaVence,VrCuota,CuotaMin,CuotaBase,ModCuota
,C.IdBandera AS CdBandera,TipoBandera,C.IdVend AS CdVend,V.RazonSocial AS Vendedor,CdAprobNeg,TA.TipoAprob AS Tipo_Aprob,AprobJunta,FechaAprob,ObservAprob,ArchivoAprob,C.Observacion AS Observ,NumInterno
,GarantHipoteca,GarantPrenda,GarantPoliza,GarantBanco,GarantPagare,GarantOtro,CdTipoBien,TipoBien,VrAvaludo,NroEscritura,CdTipoPoliza,TipoPoliza,DescPoliza,VigencPoliza,FecVencPoliza,ValorPoliza,EdoGarantias
,DescGarantOtro,InverContab,InverContrac,C.VolContMes AS VlmenContMes,C.VolContTotal AS VlmenContTotal,C.VolViabMes AS VlmenViabMes,C.VolViabTotal AS VlmenViabTotal,C.InvCtbObra AS VrCtbObra,C.InvCtbCapital AS VrCtbCapital
,ModRecursos,VrInversion,VrInventario,InvCombus,C.InvCtbEfectivo AS VrCtbEfectivo,C.InvCtbImagen AS VrCtbImagen,C.InvCtbMntmto AS VrCtbMnto,C.FecDesemb AS FechDesemb,C.InvContObra AS VrContObra,C.InvContCapital AS VrContCaptial
,C.InvContEfectivo AS VrContEfectivo,C.InvContImagen AS VrContImagen,C.CrtlAprovInv AS ConCrtlInv,C.CrtlAprovImg AS ConCrtlImag,CdTipoBloq,TBO.TipoBloqueo AS ConTipoBloq,EdoDocmtos,C.DescEdoDoc AS ConDescEstdo,RentabInicial,GraciaBonif
,ActFechaIni,CdZonaCont,TZN.TipoZona AS TipoZonaCont,ContVigente,VolContrac,DescHipotec,VrAvaComerc,FecRegEscritura,NotariaEsc,CdTipPrenda,TipoPrenda,VrAvaPrenda,PolizaPrenda,FecVigPrenda,VrAsegPrenda,ContOperando
,C.IdEstado AS CdEstado,Estado,NColor,TimeSys,FecUpdate,IdCiaCrea,C.IdUsuario AS CdUsuario,Usuario,dbo.FuncMayAgencias(C.NContrato,C.IdCia) AS CantAgencias,dbo.FuncMayBonifTarif(C.NContrato,C.IdCia)  AS TarifBonif
,dbo.FuncMayNomAgencia(C.NContrato,C.IdCia) AS NombreEds
--datos cliente
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,CLI.FecUpCupo AS FechaActCupo,Cmntario1,Cmntario2,Cmntario3
,CLI.IdPlazo AS CliCdPlazo,PZ.Plazo AS PlazoCli,CLI.CdPlazoComb AS CdPlazoComb,PZC.Plazo AS PlazoComb,CdTipBloq,TBC.TipoBloqueo AS CliTipoBloq ,CLI.DescEdoDoc AS CliDescEdoDoc,EdoRadicaDoc,FecPlazoDoc
--agencia
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
,A.IdPlazo AS AgeCdPlazo,PZA.Plazo AS AgePlazo,A.CdPlazoComb AS AgeCdPlazoComb,PZAC.Plazo AS AgePlazoComb,VrCupoCre,A.FecUpCupo AS AgeFecActCupo,CodSicom,A.FecIngreso AS AgeFecIngreso,Comentarios
,A.TipoZona AS AgeTipoZona,CdTipAgenc,TipoAgencia,CdSubAgenc,SubTipoAgenc,CdGrupAgenc,GrupoAgenc,CdSubGrupAgenc,SubGrupoAgenc,CdFntePago,FuentePago,PlazoSobtasa,DocFinanc,FecVigCond,TasaIntArranq
 FROM Trn_MayContratos AS C INNER JOIN MayTipos AS TC ON C.IdTipo=TC.IdTipo
 INNER JOIN MayTiposBonif AS B ON C.IdBonif=B.IdBonif INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN TercCliente AS CLI ON C.IdCliente=CLI.IdClie  INNER JOIN Companias AS CN ON C.IdCia=CN.IdCia 
 INNER JOIN  MayEstados AS E ON C.IdEstado=E.IdEstado INNER JOIN  TiposBan AS TB ON C.IdBandera=TB.IdBandera
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario INNER JOIN Terceros AS V ON C.IdVend=V.IdTercero 
 INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona  INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona
 INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo INNER JOIN Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 INNER JOIN Plazos AS PZ ON CLI.IdPlazo=PZ.IdPlazo 
 LEFT JOIN  TiposBloq AS TBO ON C.CdTipoBloq=TBO.IdTipBloq LEFT JOIN MayTiposAprob AS TA ON C.CdAprobNeg=TA.IdAprob
 LEFT JOIN MayTiposBienes AS TBN ON C.CdTipoBien=TBN.IdTipBien LEFT JOIN MayTiposPoliza AS TPZ ON C.CdTipoPoliza=TPZ.IdTipoPol
 LEFT JOIN TiposPrend AS TPD ON C.CdTipPrenda=TPD.IdPrend LEFT JOIN TiposZona AS TZN ON C.CdZonaCont=TZN.IdTipZona
 LEFT JOIN Plazos AS PZC ON CLI.CdPlazoComb=PZC.IdPlazo LEFT JOIN TiposBloq AS TBC ON CLI.CdTipBloq=TBC.IdTipBloq
 LEFT JOIN  Agencias   AS A ON C.IdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN Plazos AS PZA ON A.IdPlazo=PZA.IdPlazo LEFT JOIN Plazos AS PZAC ON A.CdPlazoComb=PZAC.IdPlazo
 LEFT JOIN TiposAgenc AS TAG ON A.CdTipAgenc=TAG.IdTipAgenc LEFT JOIN TiposComAgenc AS STA ON A.CdSubAgenc=STA.IdSubAgenc
 LEFT JOIN GruposAgenc AS GA ON A.CdGrupAgenc=GA.IdGrupo LEFT JOIN SubGruposAgenc AS SGA ON A.CdSubGrupAgenc=SGA.IdSubAgenc
 LEFT JOIN FuentesPago AS FP ON A.CdFntePago=FP.IdFuente 
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND C.IdTipo LIKE ISNULL(@pmIdTipo,'%') 
 ORDER BY C.NContrato
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryTercCliePrestamo @pmIdClie VARCHAR(16),@pmIdAgencia VARCHAR(16)
 AS 
 SELECT IdClie,IdAgencia,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,NomAgencia,DirAgencia,IdLocalAge,IdSzona,IdGrupo,IdTipoTerc,IdVend,IdClase,NumCuenta,IdBanco
,EsExento,CalcIntMora,DiasGracia,FecIngreso,FecVigencia,FecRetiro,VrSalBasico,MaxDcto,TipoSalario,MatMerc,FecMat,Comentarios,IdEstado,Inactivo,NumTarj,PwdTarj,FecVenceTc,FechaCrea,FechaAct,IdUsuario
 FROM TercCliePrestamo
 WHERE IdClie=@pmIdClie AND IdAgencia=@pmIdAgencia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTercCliePrestamoLta  @pmIdGrupo VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdSzona VARCHAR(4)=Null
,@pmIdTipoTerc  VARCHAR(4)=Null,@pmFecIngIni SMALLDATETIME=Null,@pmFecIngFin SMALLDATETIME=Null,@pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT IdClie,T.RazonSocial AS Razon_Social,T.TipoId AS TipId,T.Dv AS DvNit,T.NomCial AS NombreCmcial,T.SiglaRaz AS SiglaRazSocial
,IdAgencia,NomAgencia,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,T.Direccion AS TercDireccion,T.IdLocal AS CdCiudad,L.Localidad AS Ciudad,L.IdDep AS CdDpto
,D.Departamento AS Dpto,T.Telefono AS Tlefono,T.Fax AS NumFax,T.TelMovil AS TelCeluar,T.SitioWeb AS WebSite,T.e_mail AS Email
,DirAgencia,IdLocalAge,LE.Localidad AS CiuAgencia,DE.Departamento AS DptoAgencia,C.IdTipoTerc AS CdTipoTerc,TipoTercero
,SZ.IdZona AS CdZona,Zona,C.IdSzona AS CdSubzona,Subzona,C.IdGrupo AS CdGrupo,GrupoClie,T.IdSector AS CdSector,SectorEco,T.IdProf AS CdProf,Profesion,T.IdRegimen AS CdRgmen,Regimen
,T.TipEnte AS TipoEnte,IdVend,TV.RazonSocial AS NomVendedor,C.IdClase AS CdClase,ClaseCuenta,NumCuenta,C.IdBanco AS CdBanco,Banco
,EsExento,CalcIntMora,DiasGracia,FecIngreso,FecVigencia,FecRetiro,VrSalBasico,TipoSalario,MaxDcto,MatMerc,FecMat,NumTarj,FecVenceTc,Comentarios,C.IdEstado AS CdEstado,EC.Estado AS EstadoClie,C.Inactivo AS ClieInactivo,FechaCrea,FechaAct,C.IdUsuario AS CdUsuario,Usuario
,T.IniStgNom AS PosNomb,T.IdLugarCed AS IdLugCed,LC.Localidad AS LugarCed,T.FecExpCed AS FecExpCedula,T.Inactivo AS TercInactivo,T.Codigo AS TercCodigo
 FROM TercCliePrestamo AS C  INNER JOIN Terceros AS T ON C.IdClie=T.IdTercero  INNER JOIN Terceros AS TV ON C.IdVend=TV.IdTercero
 INNER JOIN GruposCli AS G ON C.IdGrupo=G.IdGrupo INNER JOIN TiposTerc AS TC ON C.IdTipoTerc=TC.IdTipoTerc
 INNER JOIN Subzonas AS SZ ON C.IdSzona=SZ.IdSzona  INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona 
 INNER JOIN EstadoTer AS EC ON C.IdEstado=EC.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS LE ON C.IdLocalAge=LE.IdLocal INNER JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 INNER JOIN  SectoresEco AS S ON T.IdSector=S.IdSector  INNER JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen
 LEFT JOIN ClaseCta AS CTA ON  C.IdClase=CTA.IdClase LEFT JOIN Bancos AS B ON C.IdBanco=B.IdBanco 
 LEFT JOIN Localidades AS LC ON T.IdLugarCed=LC.IdLocal  LEFT JOIN Profesiones AS PF ON T.IdProf=PF.IdProf 
 WHERE C.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND IdVend LIKE ISNULL(@pmIdVend,'%')
 AND  C.IdSzona LIKE ISNULL(@pmIdSzona,'%')  AND  C.IdTipoTerc  LIKE ISNULL(@pmIdTipoTerc ,'%')
 AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND (FecIngreso>=ISNULL(@pmFecIngIni,CAST('19100101' AS SMALLDATETIME)) AND FecIngreso<=ISNULL(@pmFecIngFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY T.RazonSocial
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_ccCupones @pmtmEst CHAR(2) 
 AS 
 SELECT tmItem,tmTipoReg,tmTipDoc,tmDocumento,tmIdCia,Compania,tmItemDoc,tmFecEmision,tmFecVence,tmValorFactura,tmValorAbono
,tmIdPropietario,T.RazonSocial AS Propietario,tmIdVehiculo,NumVeh,tmIdConductor,CD.RazonSocial AS Conductor,tmIdCargo,Concepto,NCuotas,TipoDcto,DctoPpago
,tmIdClase,ClaseCuenta,tmFactura,tmReferencia,tmDetalle,tmDescuento,tmNCuota
--datos del tercero
,T.TipoId AS TercTipoId,T.Dv AS TercDv,T.Direccion AS TercDireccion,T.IdLocal AS TercIdCiu,Localidad,T.Telefono AS TercTel,T.Fax AS TercFax,T.TelMovil AS TercMovil,T.e_mail AS TercEmail
 FROM tm_ccCupones AS C INNER JOIN  Companias  AS CI ON C.tmIdCia=CI.IdCia
 INNER JOIN  Terceros AS T ON C.tmIdPropietario=T.IdTercero 
 INNER JOIN Localidades AS LC ON T.IdLocal=LC.IdLocal
 INNER JOIN Vehiculos AS V ON C.tmIdVehiculo=V.IdVehiculo
 INNER JOIN   Terceros AS CD  ON C.tmIdConductor=CD.IdTercero
 LEFT JOIN   Fijos AS  F ON C.tmIdCargo=F.IdCargo  LEFT JOIN  BusClaseCue AS B ON C.tmIdClase=B.IdClase
 WHERE tmEst=@pmtmEst
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpAgencias @pmIdAgencia VARCHAR(16),@pmCodAgencia VARCHAR(16),@pmIdClie VARCHAR(16),@pmAgencia VARCHAR(150),@pmDirAgncia VARCHAR(250),@pmIdLocal VARCHAR(8),@pmTelAgncia VARCHAR(20),@pmFaxAgncia VARCHAR(20),@pmNitCont VARCHAR(16),@pmNomCont VARCHAR(150)
,@pmemlCont VARCHAR(100),@pmCargoCont VARCHAR(50),@pmIdSzona VARCHAR(4),@pmIdPlazo VARCHAR(4),@pmCdPlazoComb VARCHAR(4),@pmIdForma VARCHAR(4),@pmCdBandera VARCHAR(4),@pmIdVend VARCHAR(16),@pmCdCms VARCHAR(4),@pmCdDct VARCHAR(4),@pmIntMora DECIMAL(14,4),@pmDiasGracia INT,@pmFactFletes BIT,@pmFactSold BIT
,@pmVrCupoCre MONEY,@pmVrSaldoAct MONEY,@pmFecUpCupo SMALLDATETIME,@pmNContrato INT,@pmCiaCont CHAR(2),@pmFecIngreso SMALLDATETIME,@pmReferencia VARCHAR(50),@pmComentarios VARCHAR(250),@pmCupoGalones DECIMAL(14,4),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmTipoZona VARCHAR(10),@pmCodSicom VARCHAR(20)
,@pmCdTipAgenc VARCHAR(4),@pmCdSubAgenc VARCHAR(4),@pmCdGrupAgenc VARCHAR(4), @pmCdSubGrupAgenc VARCHAR(4), @pmCdFntePago VARCHAR(4), @pmPlazoSobtasa VARCHAR(20)
,@pmDocFinanc VARCHAR(3), @pmVolContMes DECIMAL(14,4), @pmVolContTotal DECIMAL(14,4), @pmVolViabMes DECIMAL(14,4),@pmVolViabTotal DECIMAL(14,4), @pmFecPriVenta SMALLDATETIME, @pmInvCtbObra MONEY, @pmInvCtbCapital MONEY, @pmInvCtbEfectivo MONEY, @pmInvCtbImagen MONEY, @pmInvCtbMntmto MONEY
,@pmFecDesemb SMALLDATETIME, @pmInvContObra MONEY, @pmInvContCapital MONEY, @pmInvContEfectivo MONEY, @pmInvContImagen MONEY, @pmCrtlAprovInv INT, @pmCrtlAprovImg INT,@pmFecVigCond SMALLDATETIME,@pmTasaIntArranq DECIMAL(14,4),@pmAgencOperando BIT,@pmFechaUpdate SMALLDATETIME
 AS 
 UPDATE Agencias SET CodAgencia=@pmCodAgencia,IdClie=@pmIdClie,Agencia=@pmAgencia,DirAgncia=@pmDirAgncia,IdLocal=@pmIdLocal,TelAgncia=@pmTelAgncia,FaxAgncia=@pmFaxAgncia,NitCont=@pmNitCont,NomCont=@pmNomCont,emlCont=@pmemlCont,CargoCont=@pmCargoCont,IdSzona=@pmIdSzona
,IdPlazo=@pmIdPlazo,CdPlazoComb=@pmCdPlazoComb,IdForma=@pmIdForma,CdBandera=@pmCdBandera,IdVend=@pmIdVend,CdCms=@pmCdCms,CdDct=@pmCdDct,IntMora=@pmIntMora,DiasGracia=@pmDiasGracia,FactFletes=@pmFactFletes,FactSold=@pmFactSold,VrCupoCre=@pmVrCupoCre,VrSaldoAct=@pmVrSaldoAct
,FecUpCupo=@pmFecUpCupo,FecIngreso=@pmFecIngreso,Referencia=@pmReferencia,Comentarios=@pmComentarios,IdEstado=@pmIdEstado,Inactivo=@pmInactivo,NContrato=@pmNContrato,CiaCont=@pmCiaCont,CupoGalones=@pmCupoGalones,FechaUpdate=@pmFechaUpdate,TipoZona=@pmTipoZona,CodSicom=@pmCodSicom
,CdTipAgenc=@pmCdTipAgenc,CdSubAgenc=@pmCdSubAgenc,CdGrupAgenc=@pmCdGrupAgenc,CdSubGrupAgenc=@pmCdSubGrupAgenc,CdFntePago=@pmCdFntePago,PlazoSobtasa=@pmPlazoSobtasa,DocFinanc=@pmDocFinanc,VolContMes=@pmVolContMes,VolContTotal=@pmVolContTotal,VolViabMes=@pmVolViabMes,VolViabTotal=@pmVolViabTotal
,FecPriVenta=@pmFecPriVenta,InvCtbObra=@pmInvCtbObra,InvCtbCapital=@pmInvCtbCapital,InvCtbEfectivo=@pmInvCtbEfectivo,InvCtbImagen=@pmInvCtbImagen,InvCtbMntmto=@pmInvCtbMntmto,FecDesemb=@pmFecDesemb,InvContObra=@pmInvContObra,InvContCapital=@pmInvContCapital,InvContEfectivo=@pmInvContEfectivo,InvContImagen=@pmInvContImagen
,CrtlAprovInv=@pmCrtlAprovInv,CrtlAprovImg=@pmCrtlAprovImg,FecVigCond=@pmFecVigCond,TasaIntArranq=@pmTasaIntArranq,AgencOperando=@pmAgencOperando
 WHERE IdAgencia=@pmIdAgencia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpMayContratos @pmNContrato INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdTipo VARCHAR(4),@pmIdBonif VARCHAR(4),@pmConvBonif BIT
,@pmCuotaFija BIT,@pmCondPago BIT,@pmDiasGracia INT,@pmModPlazo VARCHAR(10),@pmPlazo INT,@pmFechaVence SMALLDATETIME,@pmModRecursos VARCHAR(150),@pmVrInversion MONEY,@pmVrInventario MONEY,@pmVrCuota MONEY
,@pmModCuota VARCHAR(10),@pmIdBandera VARCHAR(4),@pmAprobJunta INT,@pmFechaAprob SMALLDATETIME,@pmObservAprob VARCHAR(250),@pmObservacion VARCHAR(250),@pmIdVend VARCHAR(16),@pmIdEstado VARCHAR(4),@pmFecUpdate SMALLDATETIME
,@pmFechaInicio SMALLDATETIME,@pmCuotaMin DECIMAL(14,4),@pmTipoAprob VARCHAR(10),@pmNumInterno VARCHAR(20),@pmInvCombus DECIMAL(14,4),@pmCuotaBase DECIMAL(14,4),@pmFechaFirma SMALLDATETIME,@pmCdAprobNeg VARCHAR(4),@pmArchivoAprob VARCHAR(50)
,@pmClaseContrato VARCHAR(10),@pmDescTipoCont VARCHAR(250),@pmGarantHipoteca BIT,@pmGarantPrenda BIT,@pmGarantPoliza BIT,@pmGarantBanco BIT,@pmGarantPagare BIT,@pmGarantOtro BIT,@pmCdTipoBien VARCHAR(4),@pmVrAvaludo MONEY,@pmNroEscritura VARCHAR(50)
,@pmCdTipoPoliza VARCHAR(4),@pmDescPoliza VARCHAR(150),@pmVigencPoliza INT,@pmFecVencPoliza SMALLDATETIME,@pmValorPoliza MONEY,@pmEdoGarantias INT,@pmDescGarantOtro VARCHAR(250),@pmInverContab INT,@pmInverContrac INT,@pmVolContMes DECIMAL(14,4)
,@pmVolContTotal DECIMAL(14,4),@pmVolViabMes DECIMAL(14,4),@pmVolViabTotal DECIMAL(14,4),@pmInvCtbObra MONEY,@pmInvCtbCapital MONEY,@pmInvCtbEfectivo MONEY,@pmInvCtbImagen MONEY,@pmInvCtbMntmto MONEY,@pmFecDesemb SMALLDATETIME,@pmInvContObra MONEY
,@pmInvContCapital MONEY,@pmInvContEfectivo MONEY,@pmInvContImagen MONEY,@pmCrtlAprovInv INT,@pmCrtlAprovImg INT,@pmCdTipoBloq VARCHAR(4),@pmEdoDocmtos INT,@pmDescEdoDoc VARCHAR(250),@pmRentabInicial DECIMAL(14,4),@pmGraciaBonif INT
,@pmActFechaIni INT,@pmCdZonaCont VARCHAR(4),@pmContVigente BIT,@pmVolContrac INT,@pmDescHipotec VARCHAR(250),@pmVrAvaComerc MONEY,@pmFecRegEscritura SMALLDATETIME,@pmNotariaEsc VARCHAR(50),@pmCdTipPrenda VARCHAR(4),@pmVrAvaPrenda MONEY
,@pmPolizaPrenda VARCHAR(30),@pmFecVigPrenda SMALLDATETIME,@pmVrAsegPrenda MONEY,@pmContOperando BIT
 AS 
 UPDATE Trn_MayContratos SET Fecha=@pmFecha,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,IdTipo=@pmIdTipo,IdBonif=@pmIdBonif,ConvBonif=@pmConvBonif,CuotaFija=@pmCuotaFija,CondPago=@pmCondPago,DiasGracia=@pmDiasGracia
,ModPlazo=@pmModPlazo,Plazo=@pmPlazo,FechaVence=@pmFechaVence,ModRecursos=@pmModRecursos,VrInversion=@pmVrInversion,VrInventario=@pmVrInventario,VrCuota=@pmVrCuota,ModCuota=@pmModCuota,IdBandera=@pmIdBandera
,AprobJunta=@pmAprobJunta,FechaAprob=@pmFechaAprob,ObservAprob=@pmObservAprob,Observacion=@pmObservacion,IdEstado=@pmIdEstado,IdVend=@pmIdVend,FechaInicio=@pmFechaInicio,CuotaMin=@pmCuotaMin,TipoAprob=@pmTipoAprob
,NumInterno=@pmNumInterno,InvCombus=@pmInvCombus,CuotaBase=@pmCuotaBase,FecUpdate=@pmFecUpdate
,FechaFirma=@pmFechaFirma,CdAprobNeg=@pmCdAprobNeg,ArchivoAprob=@pmArchivoAprob,ClaseContrato=@pmClaseContrato,DescTipoCont=@pmDescTipoCont,GarantHipoteca=@pmGarantHipoteca,GarantPrenda=@pmGarantPrenda,GarantPoliza=@pmGarantPoliza,GarantBanco=@pmGarantBanco
,GarantPagare=@pmGarantPagare,GarantOtro=@pmGarantOtro,CdTipoBien=@pmCdTipoBien,VrAvaludo=@pmVrAvaludo,NroEscritura=@pmNroEscritura,CdTipoPoliza=@pmCdTipoPoliza,DescPoliza=@pmDescPoliza,VigencPoliza=@pmVigencPoliza,FecVencPoliza=@pmFecVencPoliza,ValorPoliza=@pmValorPoliza
,EdoGarantias=@pmEdoGarantias,DescGarantOtro=@pmDescGarantOtro,InverContab=@pmInverContab,InverContrac=@pmInverContrac,VolContMes=@pmVolContMes,VolContTotal=@pmVolContTotal,VolViabMes=@pmVolViabMes,VolViabTotal=@pmVolViabTotal,InvCtbObra=@pmInvCtbObra
,InvCtbCapital=@pmInvCtbCapital,InvCtbEfectivo=@pmInvCtbEfectivo,InvCtbImagen=@pmInvCtbImagen,InvCtbMntmto=@pmInvCtbMntmto,FecDesemb=@pmFecDesemb,InvContObra=@pmInvContObra,InvContCapital=@pmInvContCapital,InvContEfectivo=@pmInvContEfectivo,InvContImagen=@pmInvContImagen,CrtlAprovInv=@pmCrtlAprovInv
,CrtlAprovImg=@pmCrtlAprovImg,CdTipoBloq=@pmCdTipoBloq,EdoDocmtos=@pmEdoDocmtos,DescEdoDoc=@pmDescEdoDoc,RentabInicial=@pmRentabInicial,GraciaBonif=@pmGraciaBonif,ActFechaIni=@pmActFechaIni,CdZonaCont=@pmCdZonaCont,ContVigente=@pmContVigente,VolContrac=@pmVolContrac
,DescHipotec=@pmDescHipotec,VrAvaComerc=@pmVrAvaComerc,FecRegEscritura=@pmFecRegEscritura,NotariaEsc=@pmNotariaEsc,CdTipPrenda=@pmCdTipPrenda,VrAvaPrenda=@pmVrAvaPrenda,PolizaPrenda=@pmPolizaPrenda,FecVigPrenda=@pmFecVigPrenda,VrAsegPrenda=@pmVrAsegPrenda,ContOperando=@pmContOperando
 WHERE NContrato=@pmNContrato AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpTercCliePrestamo @pmIdClie VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmNitRepLeg VARCHAR(16),@pmNomRepLeg VARCHAR(150),@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100),@pmCargContac VARCHAR(50)
,@pmIdSzona VARCHAR(4),@pmIdGrupo VARCHAR(4),@pmIdTipoTerc VARCHAR(4),@pmIdVend VARCHAR(16),@pmIdClase VARCHAR(4),@pmNumCuenta VARCHAR(30),@pmIdBanco VARCHAR(4),@pmEsExento BIT,@pmCalcIntMora BIT,@pmDiasGracia INT,@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME
,@pmFecRetiro SMALLDATETIME,@pmVrSalBasico MONEY,@pmMaxDcto DECIMAL(14,4),@pmMatMerc VARCHAR(20),@pmFecMat SMALLDATETIME,@pmNomAgencia VARCHAR(150),@pmDirAgencia VARCHAR(250),@pmIdLocalAge VARCHAR(8),@pmComentarios VARCHAR(2000),@pmIdEstado VARCHAR(4)
,@pmInactivo BIT,@pmTipoSalario CHAR(1),@pmNumTarj VARCHAR(30),@pmPwdTarj VARCHAR(30),@pmFecVenceTc SMALLDATETIME,@pmFechaAct SMALLDATETIME
 AS 
 UPDATE TercCliePrestamo SET NitRepLeg=@pmNitRepLeg,NomRepLeg=@pmNomRepLeg,NitContac=@pmNitContac,NomContac=@pmNomContac,TelContac=@pmTelContac,emlContac=@pmemlContac,CargContac=@pmCargContac,IdSzona=@pmIdSzona,IdGrupo=@pmIdGrupo,IdTipoTerc=@pmIdTipoTerc,IdVend=@pmIdVend
,IdClase=@pmIdClase,NumCuenta=@pmNumCuenta,IdBanco=@pmIdBanco,EsExento=@pmEsExento,CalcIntMora=@pmCalcIntMora,DiasGracia=@pmDiasGracia,FecIngreso=@pmFecIngreso,FecVigencia=@pmFecVigencia,FecRetiro=@pmFecRetiro,VrSalBasico=@pmVrSalBasico,MaxDcto=@pmMaxDcto,MatMerc=@pmMatMerc,FecMat=@pmFecMat
,Comentarios=@pmComentarios,IdEstado=@pmIdEstado,NomAgencia=@pmNomAgencia,DirAgencia=@pmDirAgencia,IdLocalAge=@pmIdLocalAge,Inactivo=@pmInactivo,FechaAct=@pmFechaAct,TipoSalario=@pmTipoSalario,NumTarj=@pmNumTarj,PwdTarj=@pmPwdTarj,FecVenceTc=@pmFecVenceTc
 WHERE IdClie=@pmIdClie AND IdAgencia=@pmIdAgencia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE spFinCuotas  @pmIdCliente VARCHAR(16),@pmIdAgencia  VARCHAR(16)
 AS 
 SELECT SUM(CASE Causacion WHEN 'QUINCENAL' THEN VrCuota*2 ELSE VrCuota END) AS TotalCuotas
 FROM Trn_FinPrestamo
 WHERE TipDoc='PR1' AND IdCliente=@pmIdCliente AND IdAgencia=@pmIdAgencia 
 AND TipoCredito IN ('PRESTAMO','TC') AND  IdEstado='0001' AND Anulado=0 AND VrPrestamo>AcumCapital
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO