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