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].[paInsMayContratos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsMayContratos]
GO

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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTercCliente_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTercCliente_Sel]
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].[paQryAgenciasCli]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryAgenciasCli]
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].[paQryConceptosCia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryConceptosCia]
GO

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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCtasCorrientesDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryCtasCorrientesDso]
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].[paQryTercCliente]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTercCliente]
GO

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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTiposDocPag]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTiposDocPag]
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].[paUpTercCliente]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpTercCliente]
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,@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
,FecIngreso,Referencia,Comentarios,CupoGalones,IdEstado,Inactivo,FechaAdd,IdUsuario)
 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,@pmFecIngreso,@pmReferencia,@pmComentarios,@pmCupoGalones,@pmIdEstado,@pmInactivo,@pmFechaAdd,@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)
 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)
 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)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsTercCliente @pmIdClie VARCHAR(16),@pmNitRepLeg VARCHAR(16),@pmNomRepLeg VARCHAR(150),@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100),@pmCargContac VARCHAR(50),@pmDirEnv VARCHAR(250)
,@pmIdLocEnv VARCHAR(8),@pmDiasEntga INT,@pmIdSzona VARCHAR(4),@pmIdGrupo VARCHAR(4),@pmIdPlazo VARCHAR(4),@pmIdForma VARCHAR(4),@pmIdEstrato VARCHAR(4),@pmIdVend VARCHAR(16),@pmNitFact VARCHAR(16),@pmIdRuta VARCHAR(4),@pmIdClase VARCHAR(4),@pmNumCuenta VARCHAR(30)
,@pmIdBanco VARCHAR(4),@pmCdMney VARCHAR(5),@pmCdDct VARCHAR(4),@pmCdRet VARCHAR(4),@pmCdRiv VARCHAR(4),@pmCdCms VARCHAR(4),@pmExcIva BIT,@pmTrfIntMora DECIMAL(14,4),@pmDiasGracia INT,@pmLiqFletes BIT,@pmFactSold BIT,@pmAutoret BIT,@pmIncRet BIT,@pmIncRiv BIT
,@pmIncIca BIT,@pmFactTipo INT,@pmVrCupo MONEY,@pmVrSaldo MONEY,@pmUidClie VARCHAR(16),@pmPwdClie VARCHAR(10),@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmMatMerc VARCHAR(20),@pmFecMat SMALLDATETIME
,@pmPathFoto VARCHAR(30),@pmPathFirma VARCHAR(30),@pmCmntario1 VARCHAR(250),@pmCmntario2 VARCHAR(250),@pmPlazosImp VARCHAR(20),@pmCdBandera VARCHAR(4),@pmContrato BIT,@pmNContrato INT,@pmCiaContMay CHAR(2),@pmCmntario3 VARCHAR(250),@pmPrendGarant VARCHAR(150)
,@pmFecUpCupo SMALLDATETIME,@pmTipoCliente VARCHAR(10),@pmRestric_Cia BIT,@pmCupoGalones DECIMAL(14,4),@pmCdPlazoComb VARCHAR(4),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO TercCliente (IdClie,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,DirEnv,IdLocEnv,DiasEntga,IdSzona,IdGrupo,IdPlazo,IdForma,IdEstrato,IdVend,NitFact,IdRuta,IdClase,NumCuenta,IdBanco,CdMney,CdDct,CdRet,CdRiv,CdCms,ExcIva,TrfIntMora,DiasGracia,LiqFletes,FactSold,Autoret
,IncRet,IncRiv,IncIca,FactTipo,VrCupo,VrSaldo,UidClie,PwdClie,FecIngreso,FecVigencia,FecRetiro,MatMerc,FecMat,PathFoto,PathFirma,Cmntario1,Cmntario2,PlazosImp,IdEstado,TipoCliente,Inactivo,CdBandera,Contrato,NContrato,CiaContMay,Cmntario3,PrendGarant,FecUpCupo,FechaAdd,IdUsuario,CupoGalones,Restric_Cia,CdPlazoComb)
 VALUES (@pmIdClie,@pmNitRepLeg,@pmNomRepLeg,@pmNitContac,@pmNomContac,@pmTelContac,@pmemlContac,@pmCargContac,@pmDirEnv,@pmIdLocEnv,@pmDiasEntga,@pmIdSzona,@pmIdGrupo,@pmIdPlazo,@pmIdForma,@pmIdEstrato,@pmIdVend,@pmNitFact,@pmIdRuta,@pmIdClase,@pmNumCuenta
,@pmIdBanco,@pmCdMney,@pmCdDct,@pmCdRet,@pmCdRiv,@pmCdCms,@pmExcIva,@pmTrfIntMora,@pmDiasGracia,@pmLiqFletes,@pmFactSold,@pmAutoret,@pmIncRet,@pmIncRiv,@pmIncIca,@pmFactTipo,@pmVrCupo,@pmVrSaldo,@pmUidClie,@pmPwdClie,@pmFecIngreso,@pmFecVigencia,@pmFecRetiro
,@pmMatMerc,@pmFecMat,@pmPathFoto,@pmPathFirma,@pmCmntario1,@pmCmntario2,@pmPlazosImp,@pmIdEstado,@pmTipoCliente,@pmInactivo,@pmCdBandera,@pmContrato,@pmNContrato,@pmCiaContMay,@pmCmntario3,@pmPrendGarant,@pmFecUpCupo,@pmFechaAdd,@pmIdUsuario,@pmCupoGalones,@pmRestric_Cia,@pmCdPlazoComb)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsTercCliente_Sel @pmIdClie VARCHAR(16),@pmNewIdClie VARCHAR(16)
 AS 
 IF EXISTS (SELECT IdClie FROM  TercCliente WHERE  IdClie=@pmIdClie)
  INSERT INTO TercCliente (IdClie,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,DirEnv,IdLocEnv,DiasEntga,IdSzona,IdGrupo,IdPlazo,IdForma,IdEstrato,IdVend,NitFact,IdRuta,IdClase,NumCuenta,IdBanco,CdMney,CdDct,CdRet,CdRiv,CdCms,ExcIva,TrfIntMora,DiasGracia,LiqFletes,FactSold,Autoret
 ,IncRet,IncRiv,IncIca,FactTipo,VrCupo,VrSaldo,UidClie,PwdClie,FecIngreso,FecVigencia,FecRetiro,MatMerc,FecMat,PathFoto,PathFirma,Cmntario1,Cmntario2,PlazosImp,IdEstado,TipoCliente,Inactivo,CdBandera,Contrato,NContrato,CiaContMay,Cmntario3,PrendGarant,FecUpCupo,FechaAdd,IdUsuario,CupoGalones,Restric_Cia,CdPlazoComb)
  SELECT @pmNewIdClie,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,DirEnv,IdLocEnv,DiasEntga,IdSzona,IdGrupo,IdPlazo,IdForma,IdEstrato,IdVend,NitFact,IdRuta,IdClase,NumCuenta,IdBanco,CdMney,CdDct,CdRet,CdRiv,CdCms,ExcIva,TrfIntMora,DiasGracia,LiqFletes,FactSold,Autoret
 ,IncRet,IncRiv,IncIca,FactTipo,VrCupo,VrSaldo,UidClie,PwdClie,FecIngreso,FecVigencia,FecRetiro,MatMerc,FecMat,PathFoto,PathFirma,Cmntario1,Cmntario2,PlazosImp,IdEstado,TipoCliente,Inactivo,CdBandera,Contrato,NContrato,CiaContMay,Cmntario3,PrendGarant,FecUpCupo,FechaAdd,IdUsuario,CupoGalones,Restric_Cia,CdPlazoComb
  FROM  TercCliente 
  WHERE IdClie=@pmIdClie
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,Referencia,Comentarios,CupoGalones,IdEstado,Inactivo,FechaAdd,FechaUpdate,IdUsuario
 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 paQryAgenciasCli @pmIdClie VARCHAR(16)
 AS 
 SELECT IdAgencia,CodAgencia,IdClie,RazonSocial,Codigo,Dv,Agencia,DirAgncia,A.IdLocal AS CdCiudad
,TelAgncia,FaxAgncia,NitCont,NomCont,emlCont,CargoCont,VrCupoCre,VrSaldoAct,Referencia
,IdSzona,IdPlazo,CdPlazoComb,IdForma,CdBandera,IdVend,CdCms,CdDct,IntMora,DiasGracia,FactFletes,FactSold
,FecUpCupo,NContrato,CiaCont,FecIngreso,CupoGalones,A.Inactivo AS Inactvo,A.IdEstado AS Id_Estado,Comentarios
,A.FechaAdd AS FecAdd,A.FechaUpdate AS FecUpd,A.IdUsuario AS IdUsuari
 FROM Agencias AS A INNER JOIN Terceros AS T ON A.IdClie=T.IdTercero
 WHERE A.Inactivo=0 AND IdClie=@pmIdClie
 ORDER BY IdAgencia
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,Referencia,Comentarios,CupoGalones,IdEstado,Inactivo,FechaAdd,FechaUpdate,IdUsuario
 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
,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
,Comentarios,A.FechaUpdate AS FecUpd,A.IdUsuario AS IdUsuari,Usuario
 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 OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryConceptosCia @pmIdDoc VARCHAR(3),@pmIdCia CHAR(2)
 AS 
 SELECT C.IdConcepto,Concepto,Concepto+' '+C.IdConcepto+ '  ('+ IdDoc+')' AS DsCon
 FROM Conceptos AS C LEFT JOIN ConceptosCia AS CN ON C.IdConcepto=CN.IdConcepto
 WHERE Inactivo=0 AND IdDoc=@pmIdDoc AND (CN.IdCia=@pmIdCia OR CN.IdCia IS NULL)
 ORDER BY IdDoc,Concepto

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryCtasCorrientesDsa @pmIdBanco VARCHAR(4)=Null
AS
  SELECT  IdCta,NumeroCta,IdCta+ ' ' +NumeroCta+' ' + Banco AS DsCta,C.IdBanco AS CodBanco,Banco
  FROM   CtasCorrientes AS C INNER JOIN Bancos AS B ON C.IdBanco=B.IdBanco
  WHERE C.Inactivo=0  AND C.IdBanco LIKE ISNULL(@pmIdBanco,'%')
  ORDER BY  IdCta

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryCtasCorrientesDso @pmIdBanco VARCHAR(4),@pmIdCiaDef CHAR(2)=Null
AS
  SELECT  C.IdCta,NumeroCta,C.IdCta+ ' ' +NumeroCta+' ' + Banco AS DsCta,C.IdBanco AS CodBanco,Banco
  FROM   CtasCorrientes AS C INNER JOIN Bancos AS B ON C.IdBanco=B.IdBanco
  LEFT JOIN CtasCorrCia AS CN ON C.IdCta=CN.IdCta
  WHERE C.Inactivo=0   AND (CN.IdCia=@pmIdCiaDef OR CN.IdCia IS NULL)
  AND C.IdBanco LIKE ISNULL(@pmIdBanco,'%')
  ORDER BY  C.IdCta
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
 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
,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null
,@pmIdTipo VARCHAR(4)=Null,@pmIdBonif VARCHAR(4)=Null,@pmConvBonif BIT=Null,@pmAprobJuntaIni INT=Null,@pmAprobJuntaFin INT=Null
,@pmIdEstado VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null
 AS 
 SELECT C.NContrato AS NumContrato,C.IdCia AS CdCia,Compania,Fecha,IdCliente,T.RazonSocial AS NomCliente,C.IdAgencia AS CdAgencia,Agencia,C.IdTipo AS CdTipo,TipContrato,C.IdBonif AS CdBonif,TipoBonif
,ConvBonif,CuotaFija,CondPago,C.DiasGracia AS Dias_Gracia,ModPlazo,Plazo,FechaVence,ModRecursos,VrInversion,VrInventario,VrCuota,ModCuota,C.IdBandera AS CdBandera,TipoBandera
,C.IdVend AS CdVend,V.RazonSocial AS Vendedor,AprobJunta,FechaAprob,ObservAprob,C.Observacion AS Observ,C.IdEstado AS CdEstado,Estado,NColor,TimeSys,FecUpdate,IdCiaCrea,C.IdUsuario AS CdUsuario,Usuario
,FechaInicio,CuotaMin,TipoAprob,NumInterno,InvCombus,CuotaBase
--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,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,Contrato
,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
 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 
 INNER JOIN Trn_MayBonif AS MB ON C.NContrato=MB.NContrato AND C.IdCia=MB.IdCia
 INNER JOIN TercCliente AS CLI ON C.IdCliente=CLI.IdClie 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 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
 LEFT JOIN  Agencias   AS A ON C.IdAgencia=A.IdAgencia
 LEFT JOIN SubGrupos AS SG ON MB.CdSubgrupo=SG.IdSubgrupo
 LEFT JOIN ProdMcias AS PM ON MB.CdProducto=PM.IdProducto
 LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 WHERE  C.IdCia LIKE ISNULL(@pmIdCia,'%%') AND C.NContrato BETWEEN ISNULL(@pmNContratoIni,0) AND ISNULL(@pmNContratoFin,2147483647)
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND C.IdAgencia LIKE ISNULL(@pmIdAgencia,'%')
 AND C.IdTipo LIKE ISNULL(@pmIdTipo,'%') AND C.IdBonif LIKE ISNULL(@pmIdBonif,'%')
 AND (ConvBonif=ISNULL(@pmConvBonif,0) or ConvBonif=ISNULL(@pmConvBonif,1))
 AND AprobJunta BETWEEN ISNULL(@pmAprobJuntaIni,-1) AND ISNULL(@pmAprobJuntaFin,2147483647)
 AND C.IdEstado  LIKE ISNULL(@pmIdEstado,'%') AND C.IdVend LIKE ISNULL(@pmIdVend,'%')
 AND (Fecha>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND Fecha<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME)))
 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,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
--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
 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
,@pmNContratoIni INT=Null,@pmNContratoFin INT=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmIdTipo VARCHAR(4)=Null
,@pmIdBonif VARCHAR(4)=Null,@pmConvBonif BIT=Null,@pmAprobJuntaIni INT=Null,@pmAprobJuntaFin INT=Null,@pmIdEstado VARCHAR(4)=Null
,@pmIdVend VARCHAR(16)=Null
 AS 
 SELECT C.NContrato AS NumContrato,C.IdCia AS CdCia,Compania,Fecha,FechaInicio,IdCliente,T.RazonSocial AS NomCliente,C.IdAgencia AS CdAgencia,Agencia,C.IdTipo AS CdTipo,TipContrato,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,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 C.NContrato BETWEEN ISNULL(@pmNContratoIni,0) AND ISNULL(@pmNContratoFin,2147483647)
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND C.IdAgencia LIKE ISNULL(@pmIdAgencia,'%')
 AND C.IdTipo LIKE ISNULL(@pmIdTipo,'%') AND C.IdBonif LIKE ISNULL(@pmIdBonif,'%')
 AND (ConvBonif=ISNULL(@pmConvBonif,0) or ConvBonif=ISNULL(@pmConvBonif,1))
 AND AprobJunta BETWEEN ISNULL(@pmAprobJuntaIni,-1) AND ISNULL(@pmAprobJuntaFin,2147483647)
 AND C.IdEstado  LIKE ISNULL(@pmIdEstado,'%') AND C.IdVend LIKE ISNULL(@pmIdVend,'%')
 ORDER BY C.NContrato
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTercCliente @pmIdClie VARCHAR(16)
 AS 
 SELECT IdClie,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,DirEnv,IdLocEnv,DiasEntga
,IdSzona,IdGrupo,IdPlazo,CdPlazoComb,IdForma,IdEstrato,IdVend,NitFact,IdRuta,IdClase,NumCuenta,IdBanco,CdMney,CdDct,CdRet,CdRiv
,CdCms,PlazosImp,ExcIva,TrfIntMora,DiasGracia,LiqFletes,FactSold,Autoret,IncRet,IncRiv,IncIca,FactTipo,VrCupo,VrSaldo,UidClie,PwdClie
,FecIngreso,FecVigencia,FecRetiro,MatMerc,FecMat,PathFoto,PathFirma,Cmntario1,Cmntario2,CdBandera,Contrato,NContrato,CiaContMay,Cmntario3,PrendGarant,FecUpCupo
,TipoCliente,CupoGalones,Restric_Cia,IdEstado,Inactivo,FechaAdd,FechaUpdate,IdUsuario
 FROM TercCliente
 WHERE IdClie=@pmIdClie
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryTercClienteLta @pmIdLocal VARCHAR(8)=Null,@pmIdSector VARCHAR(8)=Null,@pmIdRegimen VARCHAR(4)=Null,@pmTipoId CHAR(1)=Null
,@pmIdProf VARCHAR(4)=Null,@pmIdGrupo VARCHAR(4)=Null,@pmIdLocEnv VARCHAR(8)=Null,@pmIdForma VARCHAR(4)=Null,@pmIdBanco VARCHAR(4)=Null,@pmIdZona VARCHAR(4)=Null
,@pmIdSzona VARCHAR(4)=Null,@pmIdPlazo VARCHAR(4)=Null,@pmIdEstrato VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdRuta VARCHAR(4)=Null
,@pmFecIngIni SMALLDATETIME=Null,@pmFecIngFin SMALLDATETIME=Null,@pmIdEstado VARCHAR(4)=Null,@pmIdEstadoTer VARCHAR(4)=Null,@pmInactivo BIT=Null
,@pmInactivoTer BIT=Null
 AS 
 SELECT IdClie,T.RazonSocial AS RaznSocial,T.TipoId AS TipId,T.Dv AS DvNit,T.NomCial AS NombreCmcial,T.SiglaRaz AS SiglaRazSocial
,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,T.Direccion AS Dirccion,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,DirEnv,IdLocEnv,LE.Localidad AS CiuEnvio
,DE.Departamento AS Dptoenvio,DiasEntga,C.IdGrupo AS CdGrupo,GrupoClie,C.IdEstrato AS CdEstrato,Estrato,SZ.IdZona AS CdZona,Zona,C.IdSzona AS CdSubzona,Subzona
,C.IdRuta AS CdRuta,Ruta,C.IdPlazo AS CdPlazo,Plazo,NVmto,PlazosImp,CdPlazoComb,C.IdForma AS CdFrma,FormaPago,T.IdSector AS CdSector,SectorEco,T.IdProf AS CdProf,Profesion,T.IdRegimen AS CdRgmen
,Regimen,T.TipEnte AS TipoEnte,IdVend,TV.RazonSocial AS NomVendedor,NitFact,C.IdClase AS CdClase,ClaseCuenta,NumCuenta,C.IdBanco AS CdBanco,Banco,B.Direccion AS Dirbanco
,CdMney,CdDct,CdRet,CdRiv,CdCms,ExcIva,TrfIntMora,DiasGracia,LiqFletes,FactSold,Autoret,IncRet,IncRiv,IncIca,FactTipo,VrCupo,VrSaldo,UidClie,Contrato,NContrato,CiaContMay,FecIngreso,FecVigencia
,FecRetiro,MatMerc,FecMat,PathFoto,PathFirma,T.Observacion AS Observ,Cmntario1,Cmntario2,Cmntario3,CdBandera,PrendGarant,FecUpCupo,TipoCliente,C.IdEstado AS CdEst_clie,EC.Estado AS EstadoClie,C.Inactivo AS Clie_inactivo
,T.IdEstado AS CdEst_terc,ET.Estado AS Estad_terc,T.Inactivo AS Terc_inactivo,T.Codigo AS CodTerc,T.IdLugarCed AS IdLugCed,LC.Localidad AS LugarCed,T.FecExpCed AS FecExpCedula
,T.EsVendedor AS EsVend,T.EsConductor AS EsCond,T.EsPropietario AS EsProp,T.EsProveedor AS EsProvee,T.EsEmpleado AS EsEmpldo,T.EsOperario AS EsOper,T.EsAccnista AS EsAcc
,T.EsCiaAseg AS EsAseg,C.FechaAdd AS FecAdd,C.FechaUpdate AS FecUpd,C.IdUsuario AS IdUsuari,Usuario,T.IniStgNom AS PosNomb,Restric_Cia,CupoGalones
 FROM TercCliente  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 Estratos AS ETC ON C.IdEstrato=ETC.IdEstrato
 INNER JOIN Subzonas AS SZ ON C.IdSzona=SZ.IdSzona  INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona 
 INNER JOIN Plazos AS PZ ON C.IdPlazo=PZ.IdPlazo INNER JOIN Rutas AS R ON C.IdRuta=R.IdRuta
 INNER JOIN Formaspago AS F ON C.IdForma=F.IdForma
 INNER JOIN ClaseCta AS CTA ON  C.IdClase=CTA.IdClase
 INNER JOIN Bancos AS B ON C.IdBanco=B.IdBanco 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.IdLocEnv=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
 INNER JOIN EstadoTer AS ET ON T.IdEstado=ET.IdEstado INNER JOIN Localidades AS LC ON T.IdLugarCed=LC.IdLocal 
 INNER JOIN Profesiones AS PF ON T.IdProf=PF.IdProf 
 WHERE T.IdLocal LIKE ISNULL(@pmIdLocal,'%') AND T.IdSector LIKE ISNULL(@pmIdSector,'%')  AND T.IdRegimen LIKE ISNULL(@pmIdRegimen,'%')
 AND T.TipoId LIKE ISNULL(@pmTipoId,'%') AND T.IdProf LIKE ISNULL(@pmIdProf,'%') AND C.IdGrupo LIKE ISNULL(@pmIdGrupo,'%')
 AND IdLocEnv LIKE ISNULL(@pmIdLocEnv,'%') AND C.IdForma LIKE ISNULL(@pmIdForma,'%') AND C.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND SZ.IdZona LIKE ISNULL(@pmIdZona,'%') AND C.IdSzona LIKE ISNULL(@pmIdSzona,'%')
 AND C.IdPlazo LIKE ISNULL(@pmIdPlazo,'%') AND C.IdEstrato LIKE ISNULL(@pmIdEstrato,'%')
 AND IdVend LIKE ISNULL(@pmIdVend,'%') AND C.IdRuta LIKE ISNULL(@pmIdRuta,'%')
 AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND T.IdEstado LIKE ISNULL(@pmIdEstadoTer,'%')
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (T.Inactivo=ISNULL(@pmInactivoTer,0) or T.Inactivo=ISNULL(@pmInactivoTer,1))
 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 paQryTiposDocPag
 AS 
 SELECT  IdDoc,TipoDoc,IdDoc+'  '+ TipoDoc AS DsTip
 FROM  Sys_TiposDoc 
 WHERE IdDoc IN ('REC','FCO','FO1','FO2','FO3','FO4','FO5','PLA','RCO'
,'PAS','EGO','CNS','DNP','DVP','DVC','ODB','DEC','DRC','DFO','DO1','DO2','DO3','DO4','DO5','OD1','RC1')
 ORDER BY IdDoc
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,@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
 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)
 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
 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 OFF 
GO

CREATE PROCEDURE paUpTercCliente @pmIdClie VARCHAR(16),@pmNitRepLeg VARCHAR(16),@pmNomRepLeg VARCHAR(150),@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100)
,@pmCargContac VARCHAR(50),@pmDirEnv VARCHAR(250),@pmIdLocEnv VARCHAR(8),@pmDiasEntga INT,@pmIdSzona VARCHAR(4),@pmIdGrupo VARCHAR(4),@pmIdPlazo VARCHAR(4),@pmIdForma VARCHAR(4),@pmIdEstrato VARCHAR(4)
,@pmIdVend VARCHAR(16),@pmNitFact VARCHAR(16),@pmIdRuta VARCHAR(4),@pmIdClase VARCHAR(4),@pmNumCuenta VARCHAR(30),@pmIdBanco VARCHAR(4),@pmCdMney VARCHAR(5),@pmCdDct VARCHAR(4),@pmCdRet VARCHAR(4)
,@pmCdRiv VARCHAR(4),@pmCdCms VARCHAR(4),@pmExcIva BIT,@pmTrfIntMora DECIMAL(14,4),@pmDiasGracia INT,@pmLiqFletes BIT,@pmFactSold BIT,@pmAutoret BIT,@pmIncRet BIT,@pmIncRiv BIT,@pmIncIca BIT,@pmFactTipo INT,@pmVrCupo MONEY
,@pmVrSaldo MONEY,@pmUidClie VARCHAR(16),@pmPwdClie VARCHAR(10),@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmMatMerc VARCHAR(20),@pmFecMat SMALLDATETIME
,@pmPathFoto VARCHAR(30),@pmPathFirma VARCHAR(30),@pmCmntario1 VARCHAR(250),@pmCmntario2 VARCHAR(250),@pmPlazosImp VARCHAR(20),@pmCdBandera VARCHAR(4),@pmContrato BIT,@pmNContrato INT,@pmCiaContMay CHAR(2)
,@pmCmntario3 VARCHAR(250),@pmPrendGarant VARCHAR(150),@pmFecUpCupo SMALLDATETIME,@pmTipoCliente VARCHAR(10),@pmRestric_Cia BIT,@pmCupoGalones DECIMAL(14,4),@pmCdPlazoComb VARCHAR(4),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmFechaUpdate SMALLDATETIME 
 AS 
 UPDATE TercCliente SET NitRepLeg=@pmNitRepLeg,NomRepLeg=@pmNomRepLeg,NitContac=@pmNitContac,NomContac=@pmNomContac,TelContac=@pmTelContac,emlContac=@pmemlContac,CargContac=@pmCargContac,DirEnv=@pmDirEnv,IdLocEnv=@pmIdLocEnv
,DiasEntga=@pmDiasEntga,IdSzona=@pmIdSzona,IdGrupo=@pmIdGrupo,IdPlazo=@pmIdPlazo,IdForma=@pmIdForma,IdEstrato=@pmIdEstrato,IdVend=@pmIdVend,NitFact=@pmNitFact,IdRuta=@pmIdRuta,IdClase=@pmIdClase,NumCuenta=@pmNumCuenta,IdBanco=@pmIdBanco
,CdMney=@pmCdMney,CdDct=@pmCdDct,CdRet=@pmCdRet,CdRiv=@pmCdRiv,CdCms=@pmCdCms,ExcIva=@pmExcIva,TrfIntMora=@pmTrfIntMora,DiasGracia=@pmDiasGracia,LiqFletes=@pmLiqFletes,FactSold=@pmFactSold,Autoret=@pmAutoret,IncRet=@pmIncRet,IncRiv=@pmIncRiv
,IncIca=@pmIncIca,FactTipo=@pmFactTipo,VrCupo=@pmVrCupo,VrSaldo=@pmVrSaldo,UidClie=@pmUidClie,PwdClie=@pmPwdClie,FecIngreso=@pmFecIngreso,FecVigencia=@pmFecVigencia,FecRetiro=@pmFecRetiro,MatMerc=@pmMatMerc,FecMat=@pmFecMat,PathFoto=@pmPathFoto
,PathFirma=@pmPathFirma,Cmntario1=@pmCmntario1,Cmntario2=@pmCmntario2,PlazosImp=@pmPlazosImp,CdBandera=@pmCdBandera,Contrato=@pmContrato,NContrato=@pmNContrato,CiaContMay=@pmCiaContMay,Cmntario3=@pmCmntario3,PrendGarant=@pmPrendGarant,FecUpCupo=@pmFecUpCupo
,CdPlazoComb=@pmCdPlazoComb,IdEstado=@pmIdEstado,Inactivo=@pmInactivo,TipoCliente=@pmTipoCliente,FechaUpdate=@pmFechaUpdate,Restric_Cia=@pmRestric_Cia,CupoGalones=@pmCupoGalones
 WHERE IdClie=@pmIdClie
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO