SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[paQryComFacturaLtaTraDet] @pmSaldo DECIMAL(14,2),@pmFecActual SMALLDATETIME
,@pmIdCuenta VARCHAR(16)=Null,@pmIdProveedor VARCHAR(16)=Null,@pmIdCia CHAR(2)=Null
,@pmTipFac VARCHAR(3)=Null,@pmTipDoc VARCHAR(3)=Null,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null
 AS 
 SELECT C.IdCuenta AS CdCuenta,NomCuenta,IdProveedor,T.RazonSocial,TipFac,TipoDoc,Factura,C.IdCia AS CdCia,Compania
,C.Item,VrFactura,VrAbonado,VrFactura-VrAbonado AS ValorSaldo,FecEmision,FecVence,DATEDIFF(day,FecVence,@pmFecActual) AS DiasMora
,C.TipDoc,Documento,IdCiaDoc,C.TipCom,TipoCom,C.Comprobante,ItemCom,Detalle,Referencia,pVehiculo,TipRef,DocRef,IdCiaRef,EstadoApr
--datos del proveedor
,T.TipoId,T.Dv,T.Codigo AS CodigoProv,T.NomCial,T.SiglaRaz,T.Direccion AS DirProveedor,T.IdLocal AS CdCiudad,L.Localidad,L.IdDep AS CdDep,Departamento
,T.Telefono,T.Fax,T.e_mail,T.SitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte
,TP.IdGrupo AS ProvGrupo,GrupoClie,NitContac,NomContac,TelContac,emlContac,CargContac,DirOrdComp,DiasEntga
,TP.IdClase AS CodClase,NumCuenta,TP.IdBanco AS CdBanco,Banco,Autoret
--DATOS MANIFIESTO 05.02.2013
,TM.IdOrigen,LM.Localidad AS CIUORIGEN,TM.IdDestino,LD.Localidad AS CIUDESTINO, TM.IdVehiculo AS Vehiculo
--DATOS REMITENTE 13.02.2013
,R.IdRemitente,TR.RazonSocial AS NomRemitente
 FROM Trn_ComFactura AS C INNER JOIN Terceros AS T ON C.IdProveedor=T.IdTercero
 INNER JOIN Puc AS P ON C.IdCuenta=P.IdCuenta INNER JOIN  Companias AS CI ON C.IdCia=CI.IdCia
 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 TiposCom AS TC ON C.TipCom=TC.IdCom  LEFT JOIN Sys_TiposDoc AS TD ON C.TipFac=TD.IdDoc
 LEFT JOIN TercProvee AS TP ON C.IdProveedor=TP.IdProv  LEFT JOIN GruposCli AS GP ON TP.IdGrupo=GP.IdGrupo
 LEFT JOIN Bancos AS BP ON TP.IdBanco=BP.IdBanco
 --AGREGADO 05.02.2013
 LEFT JOIN Trn_TraManifiesto AS TM ON C.TipRef=TM.TipDoc AND C.DocRef=TM.Manifiesto AND C.IdCiaRef=TM.IdCia
 LEFT JOIN Localidades AS LM ON TM.IdOrigen=LM.IdLocal lEFT JOIN Localidades AS LD ON TM.IdDestino=LD.IdLocal
 --AGREGADO 13.02.2013
 LEFT JOIN Trn_TraManifRem AS RM ON TM.TipDoc=RM.TipDoc AND TM.Manifiesto=RM.Manifiesto AND TM.IdCia=RM.IdCia
 LEFT JOIN Trn_TraRemesa AS R ON RM.TipRem=R.TipDoc AND RM.Remesa= R.NumOrden AND RM.IdCiaRem=R.IdCia
 LEFT JOIN Terceros AS TR ON R.IdRemitente=TR.IdTercero
 WHERE (VrFactura-VrAbonado)>@pmSaldo AND C.IdCuenta LIKE ISNULL(@pmIdCuenta,'%')
 AND IdProveedor LIKE ISNULL(@pmIdProveedor,'%') AND  C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND C.TipDoc LIKE ISNULL(@pmTipDoc,'%' ) AND TipFac LIKE ISNULL(@pmTipFac,'%')
 AND (FecEmision>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND FecEmision<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY C.IdCuenta,RazonSocial,FecVence