INSERT INTO RegimenDian (IdRegimen,Regimen,Inactivo,CodFiscal)
VALUES('0004','REGIMEN SIMPLE DE TRIBUTACION',0,'')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemesaFac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraRemesaFac]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraRemesaFac]  @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null
 AS 
SELECT FR.TipDoc AS CdTipDoc,TipoDoc,Factura,FR.IdCia AS CdCia,CN.Compania AS NomCiaFact,FechaFact,Item,TipoReg,TipRem,Remesa,IdCiaRem,CR.Compania AS NomCiaRem,ItemRem,FecRemesa,FR.Descripcion AS DescripRem
,FR.Cantidad AS CantFact,VrUnitario,UndTarifa,VrCosto,UndCosto,CantPago,TarifaDct,VrDescuento,TarifaIva,VrImpuesto,TarifaRet,VrRetencion,TarifaIca,VrReteIca,VrFaltante,CantidadFalt,UnidadFalt,Unidades,PesoNeto,FR.UndMed AS CdUmPeso,Unidad
,Volumen,UndVol,Cases,Cajas,Palets,FR.CdConcepto AS CdConcept,CDV.Concepto AS DescConcept,CdMercancia,DescripMcia,CdAgencia,Agencia,CdCCosto,CCosto,FR.CdSubCos AS CdSubCentro,SubCosto,FR.TipoAfiVehic AS TipoAfiVeh,pVehiculo
,IdOrigen,LO.Localidad AS CiudadOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,IdDestino,LD.Localidad AS CiudadDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,Remision,DocCliente,Referencia1,Referencia2,Referencia3,FR.Anulado AS FacAnulado,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,NumPedRem,CiaPedRem,RemTransb,CostoAdicTbdo
--datos de remesa
,Modalidad,IdRemitente,NomRemite,IdDestinatario,NomDestino,R.IdConductor AS RemCedCond,CDT.RazonSocial AS RemConductor,nRemolque,NumManif,IdCiaManif,R.Observacion AS RemObserv,RA.TipoRuta,RA.TipoMintrans
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo
--datos de factura encabezados
,FacCdConc,CF.Concepto AS FacConcepto,T.TipoId AS TercTipo,FacNitClie,T.Dv AS TercDv,T.RazonSocial AS NombreClie,FacNitVend,VN.RazonSocial AS Vendedor,FacCdCiudad,LF.Localidad AS FacCiudad,LF.IdDep AS FacCdDpto,DF.Departamento AS FacDpto,FacVrNeto
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie
 FROM Trn_TraFacRemesas AS FR INNER JOIN Sys_TiposDoc AS TD ON FR.TipDoc=TD.IdDoc INNER JOIN Companias AS CN ON FR.IdCia=CN.IdCia 
 INNER JOIN Localidades AS LO ON FR.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS LD ON FR.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 --SUBQUERY FACTURAS
 INNER JOIN (SELECT F.TipDoc AS FacTipo,F.Factura AS FacNumero,F.IdCia AS FacCdCia,F.IdConcepto AS FacCdConc,F.IdCliente AS FacNitClie,F.IdAgencia AS FacIdAgenc
 ,F.IdVend AS FacNitVend,F.IdLocEnv AS FacCdCiudad,F.VrCargos AS FacVrCargos,F.VrOtrDcto AS FacVrOtrDctos,F.VrNeto AS FacVrNeto
 FROM Trn_Facturas AS F WHERE F.Fecha BETWEEN @pmFechaIni  AND @pmFechaFin AND  F.Modalidad='TRANSPORTE'  AND F.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND F.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 UNION ALL
 SELECT D.TipDev,D.Devolucion,D.IdCia,D.IdConcepto,D.IdCliente,D.IdAgencia,D.IdVend,D.IdLocEnv,D.VrCargos,D.VrOtrDcto,D.VrNeto
 FROM Trn_DevFcr AS D WHERE D.Fecha BETWEEN @pmFechaIni  AND @pmFechaFin AND D.Modalidad='TRANSPORTE' AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND D.IdCliente LIKE ISNULL(@pmIdCliente,'%') 
 UNION ALL
 SELECT N.TipDoc,N.NumNota,N.IdCia,N.IdConcepto,N.IdCliente,N.IdAgencia,N.IdVend,N.IdLocal,N.VrOtros,0,N.VrNeto
 FROM Trn_Notas AS N WHERE N.Fecha BETWEEN @pmFechaIni  AND @pmFechaFin AND N.Modalidad='REMESAS' AND  N.IdCia LIKE ISNULL(@pmIdCia,'%%')
  AND N.IdCliente LIKE ISNULL(@pmIdCliente,'%')  ) AS F ON FR.TipDoc=F.FacTipo AND FR.Factura=F.FacNumero AND FR.IdCia=F.FacCdCia
 INNER JOIN Terceros AS T ON F.FacNitClie=T.IdTercero INNER JOIN TercCliente AS CLI ON F.FacNitClie=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 Terceros AS VN ON F.FacNitVend=VN.IdTercero INNER JOIN Conceptos AS CF ON F.FacCdConc=CF.IdConcepto
 LEFT JOIN Localidades AS LF ON F.FacCdCiudad=LF.IdLocal LEFT JOIN Departamentos AS DF ON LF.IdDep=DF.IdDep
 LEFT JOIN Companias AS CR ON FR.IdCiaRem=CR.IdCia  LEFT JOIN Sys_Um AS UMP ON FR.UndMed=UMP.UndMed
 LEFT JOIN ConcDiversos AS CDV ON FR.CdConcepto=CDV.IdConcepto  LEFT JOIN Agencias  AS A ON FR.CdAgencia=A.IdAgencia
 LEFT JOIN CentroCosto AS CC ON FR.CdCCosto=CC.IdCCosto  LEFT JOIN SubCentros AS SC ON FR.CdSubCos=SC.IdSubCos
 LEFT JOIN Trn_TraRemesa AS R ON FR.TipRem=R.TipDoc AND FR.Remesa=R.NumOrden AND FR.IdCiaRem=R.IdCia
 LEFT JOIN Trn_TraRemAnexo AS RA ON FR.TipRem=RA.TipDoc AND FR.Remesa=RA.NumOrden AND FR.IdCiaRem=RA.IdCia
 LEFT JOIN Terceros AS CDT ON R.IdConductor=CDT.IdTercero
 LEFT JOIN Vehiculos AS V ON FR.pVehiculo=V.IdVehiculo LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 LEFT JOIN Mercancias AS MC ON FR.CdMercancia=MC.IdMercancia
 WHERE FR.FechaFact BETWEEN @pmFechaIni AND @pmFechaFin AND FR.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND F.FacNitClie LIKE ISNULL(@pmIdCliente,'%')