CREATE PROCEDURE [dbo].[paQryEdsFormasRelKar] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmTipoRango VARCHAR(3)=Null
,@pmNumSerie VARCHAR(5)=Null,@pmPlanillaIni INT=Null,@pmPlanillaFin INT=Null,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmIdCuenta VARCHAR(16)=Null,@pmDocumentoIni INT=Null,@pmDocumentoFin INT=Null,@pmAnulado BIT=Null
,@pmFechaDocIni SMALLDATETIME=Null,@pmFechaDocFin SMALLDATETIME=Null
 AS 
SELECT F.TipoRango,F.NumSerie,NumForma,F.Planilla AS NumPlanilla,F.IdCia AS CdCia,Compania,F.Fecha AS FechaVale,VrTotal,F.IdCliente,T.RazonSocial AS NomCliente
,F.IdAgencia AS Id_Agencia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,F.pVehiculo,F.nVehiculo,F.IdCajero AS CdCajero,Usuario,F.IdVend AS NitVend,V.RazonSocial AS Vendedor
,CdProducto,DescripProd,F.Cantidad AS Cant,F.IdCuenta AS CodCuenta,NomCuenta,F.IdForma AS CdForma,FormaPago,F.Referencia AS Referncia,F.Observacion AS Observ
,TipDcm,F.Documento,IdCiaDcm,FechaDcm,AgencDcm,AD.Agencia AS DocAgencia,F.Anulado AS EstaAnulado,F.FecDev AS FechaDev
,F.IdRango,F.Item,CP.IdJornada AS CdJornada,Jornada,NumCorte,FecCorte,RA.Observacion AS ObserVale, K.Vrprecio AS PRECIOPRO
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,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
--información del cliente
,CLI.IdSzona AS CdSubzona,Subzona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie
,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
,F.CdCCosto,F.CdSubCos AS CodSubCos
--09.02.2013 INFORMACION FACTURA
,FA.Observacion AS ObserFact,FA.TarifaRet,FA.BaseRet,FA.VrRetencion,R.Precio1,FA.FechaVence,FA.VrOtrDcto,FA.VrReteCREE
 FROM Trn_EdsFormas AS F INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero
 INNER JOIN Agencias AS A ON F.IdAgencia=A.IdAgencia  INNER JOIN Puc AS P ON F.IdCuenta=P.IdCuenta
 INNER JOIN adm_Usuarios AS U ON F.IdCajero=U.IdUsuario  INNER JOIN Terceros AS V ON F.IdVend=V.IdTercero
 INNER JOIN Formaspago AS FP ON F.IdForma=FP.IdForma  INNER JOIN Companias AS C ON F.IdCia=C.IdCia
 INNER JOIN TercCliente AS CLI ON F.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 Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN ProdMcias AS R ON F.CdProducto=R.IdProducto   LEFT JOIN Agencias AS AD ON F.AgencDcm=AD.IdAgencia
 LEFT JOIN Trn_EdsCortes AS CP ON F.Planilla=CP.Planilla AND F.IdCia=CP.IdCia  LEFT JOIN Jornadas AS J ON CP.IdJornada=J.IdJornada
 LEFT JOIN Trn_EdsRangos AS RA ON RA.IdRango=F.IdRango
 LEFT JOIN Trn_Facturas AS FA ON F.TipDcm=FA.TipDoc AND F.Documento=FA.Factura AND F.IdCiaDcm=FA.IdCia
 INNER JOIN Trn_Kardex as k On F.Planilla=k.Documento and F.IdCia=k.iDcIa 
 WHERE F.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND F.TipoRango LIKE ISNULL(@pmTipoRango,'%') AND  F.NumSerie LIKE ISNULL(@pmNumSerie,'%') And k.TipDoc='pla'
 AND F.IdCia LIKE ISNULL(@pmIdCia,'%%') AND F.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND F.IdCuenta LIKE ISNULL(@pmIdCuenta,'%')
 AND F.IdAgencia LIKE ISNULL(@pmIdAgencia,'%')
 AND  F.Planilla BETWEEN ISNULL(@pmPlanillaIni,-1)  AND  ISNULL(@pmPlanillaFin,2147483647)
 AND  F.Documento BETWEEN ISNULL(@pmDocumentoIni,-1)  AND  ISNULL(@pmDocumentoFin,2147483647)
 AND (F.Anulado=ISNULL(@pmAnulado,0) or F.Anulado=ISNULL(@pmAnulado,1))
 AND (ISNULL(FechaDcm,F.Fecha)>=ISNULL(@pmFechaDocIni,CAST('19100101' AS SMALLDATETIME)) AND ISNULL(FechaDcm,F.Fecha)<=ISNULL(@pmFechaDocFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY T.RazonSocial,F.Fecha,TipoRango,NumSerie,NumForma