/****** Object:  StoredProcedure [dbo].[paQryFacturasFmt]    Script Date: 06/14/2017 12:03:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[paQryFacturasFmt] @pmTipDoc VARCHAR(3),@pmFacturaIni INT,@pmFacturaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT F.TipDoc AS Tip_Doc,TipoDoc,F.Factura AS NumFactura,F.IdCia AS CdCia,Compania,F.Fecha AS FechaDoc,F.IdConcepto AS CdConcepto,C.Concepto AS DescConcepto,F.IdCliente,T.RazonSocial AS NomCliente
,F.IdAgencia AS IdAgenc,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,FechaVence,VrSubTotal,F.VrDescuento AS VrDcto,F.VrImpuesto AS VrIva,F.VrRetencion AS VrRetFte,F.VrReteICA AS VrRetIca,F.VrReteIVA,F.VrFletes
,F.VrOtros,F.VrCargos,F.VrOtrDcto,F.VrCostos,F.VrSobretasa,F.VrImpGlobal,F.VrFaltantes,F.VrAnticipos,F.VrNeto,VrAplicado,F.Cantidad AS CantTotal,F.PesoTotal,F.UnidTotal,F.VolTotal,CantFalt,F.VrCostoRem,F.VrCostoAfi,F.BaseImp,F.BaseRet,F.BaseIca,F.BaseRiv
,F.TarifaIva AS TarifIva,F.TarifaRet AS TarifRet,F.TarifaIca AS TarifIca,TarifaRiv,F.IdVend AS NitVend,VN.RazonSocial AS Vendedor,TarifaCom
,DirEnvio,F.IdLocEnv AS IdCiuEnvio,LE.Localidad AS CiudadEnvio,DiasEntraga,F.NitContac AS FacNitContac,F.NomContac AS FacNomContac,F.TelContac AS FacTelContac,F.emlContac AS FacEmailContac,CargoContac
,F.IdForma AS CdForma,F.DetallePago,MulPlazos,F.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,NitEmpTrans,EmpTrans,F.TipPed,F.Pedido,F.IdCiaPed,F.TipRem AS TipRemision,F.Remision AS NumRemision,TipCot,F.Cotizacion AS NumCotizacion,F.IdCiaCot AS CiaCotiza,F.FecPedido
,AutzaMora,AutzaCupo,F.Modalidad,KmtVehic,F.TipCom,TipoCom,F.Comprobante,F.IdCiaCom,F.Anulado AS FacAnulado,F.NumDev,F.FecDev,F.Observacion AS Observ,VrReteCREE,TarifaRtc,CodTarRtc,F.IdEstado AS CdEstado,F.TimeSys AS Fec_Add,F.FecUpdate AS Fec_Update,F.IdCiaCrea,F.IdUsuario AS CdUsuario,Usuario,Leyenda
--detales
,D.Item,D.TipoReg,D.FechaFact,D.TipRem AS TipRemesa,D.Remesa AS NumRemesa,D.IdCiaRem AS CdCiaRem,ItemRem,D.FecRemesa,D.Descripcion,D.Cantidad AS Cant,D.VrUnitario,D.VrCosto,D.UndTarifa,D.UndCosto,D.Unidades,D.PesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,D.Volumen,D.UndVol,D.Cases,D.Cajas,D.Palets
,D.TarifaIva AS DetTarifIva,D.VrImpuesto AS DetVrIva,TarifaDct,D.VrDescuento AS DetVrDcto,D.TarifaRet AS DetTarifRet,D.VrRetencion AS DetVrRetFte,D.TarifaIca AS DetTarifIca,D.VrReteIca AS DetVrRetIca,VrFaltante,D.Remision AS DetNumRemsion,D.DocCliente,D.Referencia1,D.Referencia2,D.Referencia3,D.CdMercancia,DescripMcia
,D.CdConcepto,CF.Concepto AS DetConcepto,D.CdCCosto,CCosto,D.CdSubCos AS CdSubCentro,SubCosto,NitTercero,NT.RazonSocial AS DetTercero,D.CdAgencia,D.pVehiculo AS PlacaVeh,D.TipoAfiVehic,D.IdOrigen,LO.Localidad AS CiudadOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,D.IdDestino,LD.Localidad AS CiudadDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,D.Anulado AS DetAnulado,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,CantidadFalt,UnidadFalt,NumPedRem,CiaPedRem,VrDeclMcia,TarifaSeg,VrSeguroRem,NitAsegurad,R.CdRutaTarif,RT.Ruta
,RM.SedeRem,SDR.NomSede AS NomSedeRem,RM.SedeDest,SDD.NomSede AS NomSedeDest
--Información del 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,DP.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
,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,ExcIva,LiqFletes,Autoret,VrCupo,VrSaldo
,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
,DM.Comentarios
 FROM Trn_Facturas AS F INNER JOIN  Trn_TraFacRemesas AS D ON F.TipDoc=D.TipDoc  AND F.Factura=D.Factura AND F.IdCia=D.IdCia
 INNER JOIN Companias AS CN ON F.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON F.TipDoc=TD.IdDoc
 INNER JOIN Conceptos AS C ON F.IdConcepto=C.IdConcepto INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario
 INNER JOIN Plazos AS PZ ON F.IdPlazo=PZ.IdPlazo INNER JOIN  Terceros AS VN ON F.IdVend=VN.IdTercero
 INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero  INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  
 INNER JOIN Departamentos AS DP ON L.IdDep=DP.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 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 Agencias AS A ON F.IdAgencia=A.IdAgencia 
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 INNER JOIN Localidades AS LO ON D.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS LD ON D.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 LEFT JOIN  Localidades AS LE ON F.IdLocEnv=LE.IdLocal   LEFT JOIN TiposCom AS TCM ON F.TipCom=TCM.IdCom  
 LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed
 LEFT JOIN Mercancias AS MC ON D.CdMercancia=MC.IdMercancia LEFT JOIN  ConcDiversos AS CF ON D.CdConcepto=CF.IdConcepto
 LEFT JOIN CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto  LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos
 LEFT JOIN Terceros AS NT ON D.NitTercero=NT.IdTercero
 LEFT JOIN Trn_TraRemesa AS R ON D.TipRem=R.TipDoc AND D.Remesa=R.NumOrden AND D.IdCiaRem=R.IdCia
 LEFT JOIN Rutas AS RT ON R.CdRutaTarif=RT.IdRuta 
 LEFT JOIN Trn_DocMemo AS DM ON F.TipDoc=DM.TipDoc AND F.Factura=DM.Documento AND F.IdCia=DM.IdCia
 LEFT JOIN Trn_TraRemMcias AS RM ON D.TipRem=RM.TipDoc AND D.Remesa=RM.NumOrden AND D.IdCiaRem=RM.IdCia AND D.ItemRem=RM.Item
 LEFT JOIN TercSedes AS SDR ON RM.NitRemite=SDR.IdTercero AND RM.SedeRem=SDR.IdSede LEFT JOIN TercSedes AS SDD ON RM.NitDestntario=SDD.IdTercero AND RM.SedeDest=SDD.IdSede
 WHERE F.TipDoc=@pmTipDoc AND F.Factura BETWEEN @pmFacturaIni AND @pmFacturaFin AND F.IdCia=@pmIdCia
 ORDER BY F.Factura