if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsMudOrdenVeh]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsMudOrdenVeh]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraAnticipos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraAnticipos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraCumplido]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraCumplido]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraFijos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraFijos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenVeh_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenVeh_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenVehRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenVehRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraAnticipos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraAnticipos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraAnticiposFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraAnticiposFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraAnticiposLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraAnticiposLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraAnticiposRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraAnticiposRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraCumplido]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraCumplido]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraCumplidoFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraCumplidoFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraCumplidoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraCumplidoLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevAntRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevAntRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraFijos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraFijos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraFijosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraFijosLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraAnticipos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraAnticipos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraCumplido]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraCumplido]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraFijos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraFijos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryComDetalleMud]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryComDetalleMud]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenServVeh]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenServVeh]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenConcLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenConcLta]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenVehRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT O.TipDoc,O.NumOrden,O.IdCia,O.FecDespacho,O.IdCliente,VH.Item,VH.IdVehiculo,VH.TipoAfiVehic,VH.NitEmpresa,NomEmpresa,VH.nRemolque
,VH.IdConductor,NC.RazonSocial AS Conductor,IdCondAux,NA.RazonSocial AS CondAuxiliar,VH.TipDocRef AS TipRem,VH.NumDocRef AS NumRemesa,VH.CdCiaRef AS CdCiaRem
,VH.CdTipoOper,TipoOperacion,VH.TipoIngreso,VH.TarifaIng,VH.VrIngreso
,V.NumVeh,V.IdMarca AS CdMarca,MV.Marca,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.Modelo,V.IdColor AS CdColor,NomColor,V.Config
,V.IdPoseedor AS CdPoseedor,T.RazonSocial AS Poseedor,V.NumSoat,VigSoat,V.NitEmpSoat,EST.RazonSocial AS NomEmpSoat,V.SerieChasis,V.CarrCapac,V.UndCapc
,CDT.Licencia AS NumLicencia,CDT.CatLicencia,CDT.VigLicencia
,CDA.Licencia AS AuxNumLicencia,CDA.CatLicencia AS AuxCategLic,CDA.VigLicencia AS AuxVigLicencia
--datos de remesas
,ISNULL(RM.RemVrTotal,0) AS RemVrTotal,ISNULL(RM.RemVrPago,0) AS RemVrPago,ISNULL(RM.MucNumero,0) AS MucNumero,ISNULL(RM.MucVrPago,0) AS MucVrPago
,ISNULL(OP.OdpNumero,0) AS NumOrdPago,ISNULL(OP.OdpVrPago,0) AS OdpVrPago,ISNULL(OP.OdpTarifa,0) AS OdpTarifa,ISNULL(OP.OdpVrTotal,0) AS OdpTotal
 FROM Trn_MudOrdenServ AS O INNER JOIN Trn_MudOrdenVeh AS VH ON O.TipDoc=VH.TipDoc AND O.NumOrden=VH.NumOrden AND O.IdCia=VH.IdCia
 INNER JOIN Vehiculos AS V ON VH.IdVehiculo=V.IdVehiculo
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN TiposCol AS C ON V.IdColor=C.IdColor
 INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Terceros AS T ON V.IdPoseedor=T.IdTercero
 INNER JOIN Terceros AS NC ON VH.IdConductor=NC.IdTercero 
 LEFT JOIN Terceros AS NA ON VH.IdCondAux=NA.IdTercero LEFT JOIN Terceros AS EST ON V.NitEmpSoat=EST.IdTercero
 LEFT JOIN TercCndtores AS CDT ON VH.IdConductor=CDT.IdConductor LEFT JOIN TercCndtores AS CDA ON VH.IdCondAux=CDA.IdConductor
 LEFT JOIN TiposOperac AS TP ON VH.CdTipoOper=TP.IdTipoOper
 LEFT JOIN (SELECT D.TipDoc AS RemTipo,D.NumOrden AS RemNumero,D.IdCia AS RemIdCia
 ,SUM(CASE D.UndTarifa WHEN 'PESO' THEN D.PesoNeto*D.TarifClie WHEN 'UNIDADES' THEN D.Cantidad*D.TarifClie WHEN 'VOLUMEN' THEN D.Volumen*D.TarifClie ELSE D.PesoNeto*D.TarifClie END) AS RemVrTotal
 ,SUM(CASE D.UndTarifPago WHEN 'PESO' THEN D.PesoNeto*D.TarifPago WHEN 'UNIDADES' THEN D.Cantidad*D.TarifPago WHEN 'VOLUMEN' THEN D.Volumen*D.TarifPago ELSE D.PesoNeto*D.TarifPago END) AS RemVrPago
 ,SUM(CASE D.UndTarifPago WHEN 'PESO' THEN D.PesoNeto*MR.TarifPago WHEN 'UNIDADES' THEN D.Cantidad*MR.TarifPago WHEN 'VOLUMEN' THEN D.Volumen*MR.TarifPago ELSE D.PesoNeto*MR.TarifPago END) AS MucVrPago
 ,MAX(MR.Manifiesto) AS MucNumero
  FROM Trn_TraRemMcias AS D INNER JOIN Trn_TraRemesa AS R ON D.TipDoc=R.TipDoc AND D.NumOrden=R.NumOrden AND D.IdCia=R.IdCia
  LEFT JOIN Trn_TraManifRem AS MR ON D.TipDoc=MR.TipRem AND D.NumOrden=MR.Remesa AND D.IdCia=MR.IdCiaRem AND D.Item=MR.ItemRem
  LEFT JOIN Trn_TraManifiesto AS M ON MR.TipDoc=M.TipDoc AND MR.Manifiesto=M.Manifiesto AND MR.IdCia=M.IdCia
  WHERE D.TipDoc='RMT' AND R.Anulado=0 AND ISNULL(M.Anulado,0)=0 GROUP BY D.TipDoc,D.NumOrden,D.IdCia) AS RM ON VH.TipDocRef=RM.RemTipo AND VH.NumDocRef=RM.RemNumero AND VH.CdCiaRef=RM.RemIdCia
 LEFT JOIN (SELECT OD.TipRem,OD.Remesa,OD.IdCiaRem,MAX(OD.OrdPago) AS OdpNumero,MAX(OD.Manifiesto) AS OdpNumManif
 ,SUM(CASE OD.UndTarifa WHEN 'PESO' THEN OD.PesoNeto*OD.TarifPago WHEN 'UNIDADES' THEN OD.Cantidad*OD.TarifPago WHEN 'VOLUMEN' THEN OD.Volumen*OD.TarifPago ELSE OD.PesoNeto*OD.TarifPago END) AS OdpVrPago
 ,MAX(OM.TarifaFlete) AS OdpTarifa,SUM(OM.VrTotalFletes-OM.VrDescuento) AS OdpVrTotal
 FROM Trn_TraOrdenRemesas AS OD INNER JOIN Trn_TraOrdenManif AS OM ON OD.TipDoc=OM.TipDoc AND OD.OrdPago=OM.OrdPago AND OD.IdCia=OM.IdCia
 INNER JOIN Trn_TraOrdenPago AS O ON OD.TipDoc=O.TipDoc AND OD.OrdPago=O.OrdPago AND OD.IdCia=O.IdCia
 WHERE OD.TipRem='RMT' AND O.Anulado=0 GROUP BY OD.TipRem,OD.Remesa,OD.IdCiaRem) AS OP ON VH.TipDocRef=OP.TipRem AND VH.NumDocRef=OP.Remesa AND VH.CdCiaRef=OP.IdCiaRem
WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraCumplidoFmt] @pmTipDoc VARCHAR(3),@pmCumplidoIni INT,@pmCumplidoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT CU.TipDoc AS TipCum,TipoDoc,CU.Cumplido AS NumCumplido,CU.IdCia AS CdCia,Compania,CU.Fecha AS FechaCum,TipMuc,CU.Manifiesto AS NumManif,IdCiaMuc,CU.IdVehiculo AS PlacaVeh,Modalidad,DiasPlazo,FecPago
,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,CU.Observacion AS Observ
,CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,CdRuta,R.Ruta AS DescRuta
,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic,CU.IdEstado AS CdEstado,Estado,CU.Anulado AS Anuldo,CU.FecDev AS FechaDev
,CU.TimeSys AS FechaCrea,CU.FecUpdate AS FechaAct,CU.IdCiaCrea AS CdCiaCrea,CU.IdUsuario AS CdUsuario,Usuario,Leyenda
,M.Fecha AS FecManif,FecDespacho,M.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,nRemolque,TipoAfiVehic,M.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos,VrCargos,VrDctos,TarifaFlete,M.Cantidad AS CantTotal,PesoTotal
,IdLocFletes,CF.Localidad AS LugarFletes,FechaPago,PagoCargue,PagoDescargue,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,FechaOdp,EstOrden,M.Observacion AS Observ,TipoComp AS CdTipComp,NumComp AS Comprobante
,TipoCumpMT,MotivoSusp,ConsecSusp,VrAdicCargue,VrAdicDescargue,VrAdicFlete,MotivoVrAdic,VrDctoFlete,MotivoVrDcto,VrAdicAnticipo,FecEntregaDoc,NumRadicaMT,MvoAnulaCump,ObservAnulado,NumViajesCum,PesoLiqPago,PesoLiqFact
--detalle de cumplidos
,D.Item AS DetItem,TipRem,D.Remesa AS NumRemesa,D.IdCiaRem AS CdCiaRem,ItemRem,D.Cantidad AS Cant,D.PesoNeto AS PesoCump,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,D.Volumen AS VolCump,D.UndVol AS Und_Vol
,D.Cases AS CasesCump,D.Cajas AS CajasCump,D.Palets AS PaletsCump,D.TarifClie AS Tarif_Clie,D.TarifPago AS Tarif_Pago,TarifFlete,UndTarifClie,D.UndTarifPago AS UndTarifPag,CantCargue,PesoCargue,VolCargue,CasesCargue,CajasCargue,PaletsCargue
,EstadoCump,D.Remision AS NumRemision,D.DocCliente AS CumDocClie,D.Referencia1 AS CumRef1,D.Referencia2 AS CumRef2,D.Referencia3 AS CumRef3,D.Detalle AS CumDetalle
,IdMercancia,DescripMcias,RM.Cantidad AS RemCant,RM.PesoNeto AS RemPeso,NitRemite,Remitente,NitDestntario,Destinatario,TipoCumRemesa,MotivoSuspRem,HoraLlegaCargue,HoraEntraCargue,HoraSaleCargue
,HoraLlegaDescargue,HoraEntraDescargue,HoraSaleDescargue
--Datos del poseedor
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
--Datos del vehiculo
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraCumplido AS CU INNER JOIN Companias AS CN ON CU.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON CU.TipDoc=TD.IdDoc
 INNER JOIN EstadoDoc AS ED ON CU.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON CU.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraCumRemesas AS D ON CU.TipDoc=D.TipDoc  AND CU.Cumplido=D.Cumplido AND CU.IdCia=D.IdCia
 INNER JOIN Trn_TraManifiesto AS M ON CU.TipMuc=M.TipDoc AND CU.Manifiesto=M.Manifiesto AND CU.IdCiaMuc=M.IdCia
 INNER JOIN Terceros AS  CDT ON M.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS NP ON M.IdPropietario=NP.IdTercero   INNER JOIN Terceros AS T ON M.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON CU.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Localidades AS CO ON CU.CdOrigen=CO.IdLocal LEFT JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS CD ON CU.CdDestino=CD.IdLocal LEFT JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 LEFT JOIN Rutas AS R ON CU.CdRuta=R.IdRuta 
 LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed 
 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
 WHERE CU.TipDoc=@pmTipDoc AND CU.Cumplido BETWEEN @pmCumplidoIni AND @pmCumplidoFin AND CU.IdCia=@pmIdCia
 ORDER BY CU.Cumplido
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraAnticiposFmt] @pmTipDoc VARCHAR(3),@pmAnticipoIni INT,@pmAnticipoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT A.TipDoc AS TipoAnt,TipoDoc,Anticipo,A.IdCia AS CdCia,Compania,A.Fecha AS FechaAnt,A.IdConcepto AS CdConcepto,Concepto,TipMuc,A.Manifiesto AS NumManif,IdCiaMuc,A.IdVehiculo AS PlacaVeh
,A.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor,A.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,A.VrAnticipo AS ValAnticipo,VrAbonado,VrDeduccion,Modalidad,TipoPago,A.NumCheque AS NroCheque,FecCheque
,A.IdCta AS CdCta,NumeroCta,CTA.IdBanco,Banco,A.IdCuenta AS CdCuenta,P.NomCuenta AS DescCuenta,CedBenef,Beneficiario,A.Cantidad AS CantGals,FechaVence,NumPresAnt,PreAnticipo,NumCargue,CiaCargue,A.TipCom AS CdTipCom,TipoCom,A.Comprobante AS NumComp,A.IdCiaCom AS CdCiaComp
,A.Anulado AS Anuldo,NumDev,A.FecDev AS FechaDev,A.Observacion AS Observ,A.IdEstado AS CdEstado,Estado,A.TimeSys AS FechaCrea,A.FecUpdate AS FechaAct,A.IdCiaCrea AS CdCiaCrea,A.IdUsuario AS CdUsuario,Usuario,Leyenda
--datos de manifiesto
,FecDespacho,M.IdOrigen AS CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,M.IdDestino AS CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,M.IdRuta AS CdRuta,R.Ruta AS DescRuta,VrFletes,TarifaFlete,PesoTotal
--datos del poseedor
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
--Datos del vehiculo
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
--comprobante
,Item,DC.IdCuenta AS CodCuenta,PC.NomCuenta AS Nom_Cuenta,Detalle,VrDebito,VrCredito,DC.IdTercero AS NitTercero,DT.RazonSocial AS NomTercero,DC.IdCCosto AS DetCodCentCost,DCC.CCosto AS DetCentCost
,DC.IdSubCos AS DetCodSubCentro,DS.SubCosto  AS DetSubCentro,VrBase,TarifaBase,TipFac,Factura,IdCiaFac,ItemFac,FecVence,DC.NumCheque AS DetNumCheque,TipoAplica,NitOtros,Referncia
 FROM Trn_TraAnticipos AS A  INNER JOIN Companias AS CN ON A.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON A.TipDoc=TD.IdDoc
 INNER JOIN EstadoDoc AS ED ON A.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario 
 INNER JOIN Conceptos AS C ON A.IdConcepto=C.IdConcepto
 INNER JOIN Terceros AS  CDT ON A.IdConductor=CDT.IdTercero  INNER JOIN Terceros AS T ON A.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal 
 INNER JOIN Vehiculos AS V ON A.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN Trn_TraManifiesto AS M ON A.TipMuc=M.TipDoc AND  A.Manifiesto=M.Manifiesto AND  A.IdCiaMuc=M.IdCia
 LEFT JOIN Localidades AS CO ON  M.IdOrigen=CO.IdLocal LEFT JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal LEFT JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 LEFT JOIN Rutas AS R ON M.IdRuta=R.IdRuta 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN TercCndtores AS CT ON A.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN CtasCorrientes AS CTA ON A.IdCta=CTA.IdCta LEFT JOIN Bancos AS B ON CTA.IdBanco=B.IdBanco
 LEFT JOIN Puc AS P ON A.IdCuenta=P.IdCuenta LEFT JOIN TiposCom AS TCM ON A.TipCom=TCM.IdCom 
 LEFT JOIN  Trn_ComDetalle AS DC ON A.TipCom=DC.TipCom AND A.Comprobante=DC.Comprobante AND A.IdCiaCom=DC.IdCia
 LEFT JOIN Puc AS PC ON DC.IdCuenta=PC.IdCuenta LEFT JOIN Terceros AS DT ON DC.IdTercero=DT.IdTercero
 LEFT JOIN CentroCosto AS DCC ON DC.IdCCosto=DCC.IdCCosto  LEFT JOIN SubCentros AS DS ON DC.IdSubCos=DS.IdSubCos
 WHERE A.TipDoc=@pmTipDoc AND A.Anticipo BETWEEN @pmAnticipoIni AND @pmAnticipoFin AND A.IdCia=@pmIdCia
 ORDER BY A.Anticipo
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraAnticiposRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmIdConductor VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT A.TipDoc AS TipoAnt,A.Anticipo AS NumAnticipo,A.IdCia AS CdCia,Compania,A.Fecha AS FechaAnt,A.IdConcepto AS CdConcepto,Concepto,TipMuc,A.Manifiesto AS NumManif,IdCiaMuc,A.IdVehiculo AS PlacaVeh
,A.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor,A.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,A.VrAnticipo AS ValAnticipo,VrAbonado,A.VrAnticipo-VrAbonado AS VrSaldo,Modalidad,TipoPago,NumCheque,FecCheque
,A.IdCta AS CdCta,NumeroCta,CTA.IdBanco,Banco,A.IdCuenta AS CdCuenta,NomCuenta,CedBenef,Beneficiario,A.Cantidad AS CantGals,FechaVence,NumPresAnt,PreAnticipo,NumCargue,CiaCargue,A.VrDeduccion,A.TipCom,TipoCom,A.Comprobante,A.IdCiaCom
,A.Anulado AS Anuldo,NumDev,A.FecDev AS FechaDev,A.Observacion AS Observ,A.IdEstado AS CdEstado,A.TimeSys AS FechaCrea,A.FecUpdate AS FechaAct,A.IdCiaCrea AS CdCiaCrea,A.IdUsuario AS CdUsuario,Usuario
--datos de manifiesto
,FecDespacho,M.IdOrigen AS CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,M.IdDestino AS CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,M.IdRuta AS CdRuta,R.Ruta AS DescRuta,VrFletes,TarifaFlete,PesoTotal,EstCumplido,Cumplido,IdCiaCump,FechaCump
--Datos del vehiculo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion,V.IdGrupo AS CdGrupo,GrupoProp
--,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
 FROM Trn_TraAnticipos AS A  INNER JOIN Companias AS CN ON A.IdCia=CN.IdCia  INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario 
 INNER JOIN Conceptos AS C ON A.IdConcepto=C.IdConcepto
 INNER JOIN Terceros AS  CDT ON A.IdConductor=CDT.IdTercero  INNER JOIN Terceros AS T ON A.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal 
 INNER JOIN Vehiculos AS V ON A.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN Trn_TraManifiesto AS M ON A.TipMuc=M.TipDoc AND A.Manifiesto=M.Manifiesto AND  A.IdCiaMuc=M.IdCia
 LEFT JOIN Localidades AS CO ON  M.IdOrigen=CO.IdLocal LEFT JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal LEFT JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 LEFT JOIN Rutas AS R ON M.IdRuta=R.IdRuta 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN CtasCorrientes AS CTA ON A.IdCta=CTA.IdCta LEFT JOIN Bancos AS B ON CTA.IdBanco=B.IdBanco
 LEFT JOIN Puc AS P ON A.IdCuenta=P.IdCuenta LEFT JOIN TiposCom AS TCM ON A.TipCom=TCM.IdCom 
 LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
-- LEFT JOIN TercCndtores AS CT ON A.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 WHERE A.TipDoc=@pmTipDoc AND A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND A.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND A.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND A.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND A.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 AND A.IdConductor LIKE ISNULL(@pmIdConductor,'%') AND A.Modalidad LIKE ISNULL(@pmModalidad,'%')
 ORDER BY A.IdCia,A.Anticipo
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraDevAntRel] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmIdConductor VARCHAR(16)=Null
 AS 
 SELECT TipDev,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,VrDevolucion,D.TipDoc AS TipoAnt,D.Anticipo AS NumAnticipo,IdCiaDoc,D.FecDoc AS FechaAnt
,D.IdVehiculo AS PlacaVeh,D.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor,D.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,CdCta,NumeroCta,CTA.IdBanco,Banco,CdCuenta,NomCuenta,A.VrDeduccion,ModdDev,D.TipCom AS TipComp,TipoCom,D.Comprobante AS NumComp,D.IdCiaCom AS CdCiaComp,D.Observacion AS Observ
,D.TimeSys AS FechaCrea,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS CdUsuario,Usuario
,TipMuc,A.Manifiesto AS NumManif,IdCiaMuc,Modalidad,TipoPago,NumCheque,FecCheque,Beneficiario,A.Cantidad AS CantGals,PreAnticipo
--datos del vehículo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion,V.IdGrupo AS CdGrupo,GrupoProp
 FROM Trn_TraDevAnt AS D  INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia  INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Conceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN Terceros AS  CDT ON D.IdConductor=CDT.IdTercero  INNER JOIN Terceros AS T ON D.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal 
 INNER JOIN Vehiculos AS V ON D.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 INNER JOIN Trn_TraAnticipos AS A ON D.TipDoc=A.TipDoc AND D.Anticipo=A.Anticipo AND D.IdCiaDoc=A.IdCia
 LEFT JOIN CtasCorrientes AS CTA ON D.CdCta=CTA.IdCta LEFT JOIN Bancos AS B ON CTA.IdBanco=B.IdBanco
 LEFT JOIN Puc AS P ON D.CdCuenta=P.IdCuenta LEFT JOIN TiposCom AS TCM ON D.TipCom=TCM.IdCom 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 WHERE D.TipDev=@pmTipDev AND  D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND D.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND D.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 AND D.IdConductor LIKE ISNULL(@pmIdConductor,'%') 
 ORDER BY D.IdCia,D.Devolucion
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenVeh_Cr] @pmTipDoc VARCHAR(3),@pmNumOrdenIni INT,@pmNumOrdenFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT VH.TipDoc,VH.NumOrden,VH.IdCia,VH.Item,VH.IdVehiculo,VH.TipoAfiVehic,VH.NitEmpresa,NomEmpresa,VH.nRemolque
,VH.IdConductor,NC.RazonSocial AS Conductor,IdCondAux,NA.RazonSocial AS CondAuxiliar,TipDocRef,VH.NumDocRef,CdCiaRef,VH.CdTipoOper,TipoOperacion
,VH.TipoIngreso,VH.TarifaIng,VH.VrIngreso
,V.NumVeh,V.IdMarca AS CdMarca,MV.Marca,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.Modelo,V.IdColor AS CdColor,NomColor,V.Config
,V.IdPoseedor AS CdPoseedor,T.RazonSocial AS Poseedor,V.NumSoat,VigSoat,V.SerieChasis,V.CarrCapac,V.UndCapc
,CDT.Licencia AS NumLicencia,CDT.CatLicencia,CDT.VigLicencia
,CDA.Licencia AS AuxNumLicencia,CDA.CatLicencia AS AuxCategLic,CDA.VigLicencia AS AuxVigLicencia
 FROM Trn_MudOrdenVeh AS VH INNER JOIN Vehiculos AS V ON VH.IdVehiculo=V.IdVehiculo
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN TiposCol AS C ON V.IdColor=C.IdColor
 INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Terceros AS T ON V.IdPoseedor=T.IdTercero
 INNER JOIN Terceros AS NC ON VH.IdConductor=NC.IdTercero
 LEFT JOIN Terceros AS NA ON VH.IdCondAux=NA.IdTercero
 LEFT JOIN TercCndtores AS CDT ON VH.IdConductor=CDT.IdConductor LEFT JOIN TercCndtores AS CDA ON VH.IdCondAux=CDA.IdConductor
 LEFT JOIN TiposOperac AS TP ON VH.CdTipoOper=TP.IdTipoOper
 WHERE VH.TipDoc=@pmTipDoc AND VH.NumOrden BETWEEN @pmNumOrdenIni AND @pmNumOrdenFin AND VH.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraCumplido] @pmTipDoc VARCHAR(3),@pmCumplido INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmTipMuc VARCHAR(3),@pmManifiesto INT,@pmIdCiaMuc CHAR(2),@pmIdVehiculo VARCHAR(10),@pmModalidad VARCHAR(10),@pmDiasPlazo INT
,@pmFecPago SMALLDATETIME,@pmTipoMargen VARCHAR(10),@pmMargenFalt DECIMAL(14,4),@pmUndCalcFalt VARCHAR(10),@pmTarifFaltPago MONEY,@pmTarifFaltCobro MONEY,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(1000),@pmIdEstado VARCHAR(4)
,@pmNRadicaDoc INT,@pmIdCiaRadic CHAR(2),@pmCdCiaOfic CHAR(2),@pmFecRadic SMALLDATETIME,@pmCdRuta VARCHAR(4),@pmCdOrigen VARCHAR(8),@pmCdDestino VARCHAR(8),@pmTipoComp VARCHAR(3),@pmNumComp INT,@pmCodConcepto VARCHAR(4)
,@pmTipoCumpMT VARCHAR(3),@pmMotivoSusp VARCHAR(3),@pmConsecSusp VARCHAR(3),@pmVrAdicCargue DECIMAL(16,4),@pmVrAdicDescargue DECIMAL(16,4),@pmVrAdicFlete DECIMAL(16,4),@pmMotivoVrAdic VARCHAR(3)
,@pmVrDctoFlete DECIMAL(16,4),@pmMotivoVrDcto VARCHAR(3),@pmVrAdicAnticipo DECIMAL(16,4),@pmFecEntregaDoc SMALLDATETIME,@pmMvoAnulaCump VARCHAR(5),@pmObservAnulado VARCHAR(250),@pmNumViajesCum INT,@pmPesoLiqPago INT,@pmPesoLiqFact INT
,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraCumplido (TipDoc,Cumplido,IdCia,Fecha,TipMuc,Manifiesto,IdCiaMuc,IdVehiculo,Modalidad,DiasPlazo,FecPago,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic,CdRuta,CdOrigen,CdDestino
,Anulado,FecDev,TipoComp,NumComp,CodConcepto,Observacion,IdEstado,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario,TipoCumpMT,MotivoSusp,ConsecSusp,VrAdicCargue,VrAdicDescargue,VrAdicFlete,MotivoVrAdic,VrDctoFlete,MotivoVrDcto,VrAdicAnticipo,FecEntregaDoc,NumRadicaMT,MvoAnulaCump,ObservAnulado,NumViajesCum,PesoLiqPago,PesoLiqFact)
 VALUES (@pmTipDoc,@pmCumplido,@pmIdCia,@pmFecha,@pmTipMuc,@pmManifiesto,@pmIdCiaMuc,@pmIdVehiculo,@pmModalidad,@pmDiasPlazo,@pmFecPago,@pmTipoMargen,@pmMargenFalt,@pmUndCalcFalt,@pmTarifFaltPago,@pmTarifFaltCobro,@pmNRadicaDoc,@pmIdCiaRadic,@pmCdCiaOfic,@pmFecRadic
,@pmCdRuta,@pmCdOrigen,@pmCdDestino,@pmAnulado,@pmFecDev,@pmTipoComp,@pmNumComp,@pmCodConcepto,@pmObservacion,@pmIdEstado,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmTipoCumpMT,@pmMotivoSusp,@pmConsecSusp,@pmVrAdicCargue,@pmVrAdicDescargue,@pmVrAdicFlete
,@pmMotivoVrAdic,@pmVrDctoFlete,@pmMotivoVrDcto,@pmVrAdicAnticipo,@pmFecEntregaDoc,0,@pmMvoAnulaCump,@pmObservAnulado,@pmNumViajesCum,@pmPesoLiqPago,@pmPesoLiqFact)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraCumplido] @pmTipDoc VARCHAR(3),@pmCumplido INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmTipMuc VARCHAR(3),@pmManifiesto INT,@pmIdCiaMuc CHAR(2),@pmIdVehiculo VARCHAR(10),@pmModalidad VARCHAR(10),@pmDiasPlazo INT
,@pmFecPago SMALLDATETIME,@pmTipoMargen VARCHAR(10),@pmMargenFalt DECIMAL(14,4),@pmUndCalcFalt VARCHAR(10),@pmTarifFaltPago MONEY,@pmTarifFaltCobro MONEY,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(1000),@pmIdEstado VARCHAR(4)
,@pmNRadicaDoc INT,@pmIdCiaRadic CHAR(2),@pmCdCiaOfic CHAR(2),@pmFecRadic SMALLDATETIME,@pmCdRuta VARCHAR(4),@pmCdOrigen VARCHAR(8),@pmCdDestino VARCHAR(8),@pmTipoComp VARCHAR(3),@pmNumComp INT,@pmCodConcepto VARCHAR(4)
,@pmTipoCumpMT VARCHAR(3),@pmMotivoSusp VARCHAR(3),@pmConsecSusp VARCHAR(3),@pmVrAdicCargue DECIMAL(16,4),@pmVrAdicDescargue DECIMAL(16,4),@pmVrAdicFlete DECIMAL(16,4),@pmMotivoVrAdic VARCHAR(3)
,@pmVrDctoFlete DECIMAL(16,4),@pmMotivoVrDcto VARCHAR(3),@pmVrAdicAnticipo DECIMAL(16,4),@pmFecEntregaDoc SMALLDATETIME,@pmMvoAnulaCump VARCHAR(5),@pmObservAnulado VARCHAR(250),@pmNumViajesCum INT,@pmPesoLiqPago INT,@pmPesoLiqFact INT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_TraCumplido SET Fecha=@pmFecha,TipMuc=@pmTipMuc,Manifiesto=@pmManifiesto,IdCiaMuc=@pmIdCiaMuc,IdVehiculo=@pmIdVehiculo,Modalidad=@pmModalidad,DiasPlazo=@pmDiasPlazo,FecPago=@pmFecPago,TipoMargen=@pmTipoMargen
,MargenFalt=@pmMargenFalt,UndCalcFalt=@pmUndCalcFalt,TarifFaltPago=@pmTarifFaltPago,TarifFaltCobro=@pmTarifFaltCobro,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado
,NRadicaDoc=@pmNRadicaDoc,IdCiaRadic=@pmIdCiaRadic,CdCiaOfic=@pmCdCiaOfic,FecRadic=@pmFecRadic,CdRuta=@pmCdRuta,CdOrigen=@pmCdOrigen,CdDestino=@pmCdDestino,TipoComp=@pmTipoComp,NumComp=@pmNumComp,CodConcepto=@pmCodConcepto,FecUpdate=@pmFecUpdate
,TipoCumpMT=@pmTipoCumpMT,MotivoSusp=@pmMotivoSusp,ConsecSusp=@pmConsecSusp,VrAdicCargue=@pmVrAdicCargue,VrAdicDescargue=@pmVrAdicDescargue,VrAdicFlete=@pmVrAdicFlete,MotivoVrAdic=@pmMotivoVrAdic,VrDctoFlete=@pmVrDctoFlete
,MotivoVrDcto=@pmMotivoVrDcto,VrAdicAnticipo=@pmVrAdicAnticipo,FecEntregaDoc=@pmFecEntregaDoc,MvoAnulaCump=@pmMvoAnulaCump,ObservAnulado=@pmObservAnulado,NumViajesCum=@pmNumViajesCum,PesoLiqPago=@pmPesoLiqPago,PesoLiqFact=@pmPesoLiqFact
 WHERE TipDoc=@pmTipDoc AND Cumplido=@pmCumplido AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraCumplido] @pmTipDoc VARCHAR(3),@pmCumplido INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Cumplido,IdCia,Fecha,TipMuc,Manifiesto,IdCiaMuc,IdVehiculo,Modalidad,DiasPlazo,FecPago
,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,Anulado,FecDev,Observacion,IdEstado
,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic,CdRuta,CdOrigen,CdDestino,TipoComp,NumComp,CodConcepto
,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario,TipoCumpMT,MotivoSusp,ConsecSusp,VrAdicCargue
,VrAdicDescargue,VrAdicFlete,MotivoVrAdic,VrDctoFlete,MotivoVrDcto,VrAdicAnticipo,FecEntregaDoc,NumRadicaMT
,MvoAnulaCump,ObservAnulado,NumViajesCum,PesoLiqPago,PesoLiqFact
 FROM Trn_TraCumplido
 WHERE TipDoc=@pmTipDoc AND Cumplido=@pmCumplido AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraCumplidoLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT C.Cumplido AS NumCumplido,C.IdCia AS CdCia,Compania,C.Fecha AS FecCumplido,C.Manifiesto AS NumManif,IdCiaMuc,M.Fecha AS FecManif,C.IdVehiculo AS PlacaVeh,Modalidad
,M.IdPropietario AS NitPropietario,T.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,NP.RazonSocial AS Poseedor
,M.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,DiasPlazo,FecPago,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic
,C.Anulado AS CumAnulado,C.FecDev AS FechAnulado,TipoComp,NumComp,CodConcepto,C.Observacion AS Observ,C.IdEstado AS CdEstado,Estado
,CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,CdRuta,R.Ruta AS DescRuta
,IdLocFletes,LP.Localidad AS LugarPago,C.TimeSys AS FechaCrea,C.FecUpdate AS FechaAct,C.IdCiaCrea AS CdCiaCrea,C.IdUsuario AS CdUsuario,Usuario
,TipoCumpMT,MotivoSusp,ConsecSusp,VrAdicCargue,VrAdicDescargue,VrAdicFlete,MotivoVrAdic,VrDctoFlete,MotivoVrDcto,VrAdicAnticipo,FecEntregaDoc,NumRadicaMT
,MA.TipoRuta,MvoAnulaCump,ObservAnulado,NumViajesCum,PesoLiqPago,PesoLiqFact,MA.MucMintrans AS TipoViaje
 FROM Trn_TraCumplido AS C INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraManifiesto AS M ON C.TipMuc=M.TipDoc AND C.Manifiesto=M.Manifiesto AND C.IdCiaMuc=M.IdCia
 INNER JOIN Trn_TraManifAnexo AS MA ON C.TipMuc=MA.TipDoc AND C.Manifiesto=MA.Manifiesto AND C.IdCiaMuc=MA.IdCia
 INNER JOIN Terceros AS NC ON M.IdConductor=NC.IdTercero INNER JOIN Terceros AS T ON M.IdPropietario=T.IdTercero
 INNER JOIN Terceros AS NP ON M.IdPoseedor=NP.IdTercero 
 LEFT JOIN Localidades AS CO ON C.CdOrigen=CO.IdLocal LEFT JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS CD ON C.CdDestino=CD.IdLocal LEFT JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 LEFT JOIN Rutas AS R ON C.CdRuta=R.IdRuta LEFT JOIN Localidades AS LP ON M.IdLocFletes=LP.IdLocal 
 WHERE C.TipDoc=@pmTipDoc  AND C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND C.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')  AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 ORDER BY C.IdCia,C.Cumplido
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraAnticiposLta] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmModalidad VARCHAR(10)=Null
,@pmIdCta VARCHAR(4)=Null
 AS 
 SELECT  Anticipo,A.IdCia AS CdCia,Compania,Fecha,TipMuc,Manifiesto,IdCiaMuc,A.IdVehiculo AS PlacaVeh,IdPoseedor,T.RazonSocial AS Poseedor,IdConductor,CDT.RazonSocial AS Conductor,VrAnticipo,VrAbonado,Modalidad
,A.IdConcepto AS CdConcepto,Concepto,TipoPago,NumCheque,FecCheque,A.IdCta AS CdCta,NumeroCta,CT.IdBanco,Banco,A.IdCuenta AS CdCuenta,CedBenef,Beneficiario,VrDeduccion,Cantidad,FechaVence,NumPresAnt,PreAnticipo,NumCargue,CiaCargue
,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,A.Observacion AS Observ,A.IdEstado AS CdEstado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,A.IdUsuario AS Cdusuario,Usuario
 FROM Trn_TraAnticipos AS A INNER JOIN Terceros AS T ON A.IdPoseedor=T.IdTercero INNER JOIN Terceros AS CDT ON A.IdConductor=CDT.IdTercero
 INNER JOIN Conceptos AS C ON A.IdConcepto=C.IdConcepto  INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario
 INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia
 LEFT JOIN CtasCorrientes AS CT ON A.IdCta=CT.IdCta LEFT JOIN Bancos AS B ON CT.IdBanco=B.IdBanco
 WHERE   Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND A.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND A.IdVehiculo  LIKE ISNULL(@pmIdVehiculo ,'%') AND A.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 AND A.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')   AND A.Modalidad LIKE ISNULL(@pmModalidad,'%') 
 AND A.IdCta LIKE ISNULL( @pmIdCta,'%') 
 ORDER BY A.IdCia,Anticipo
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraFijosLta] @pmTipoConc VARCHAR(10)=Null
 AS 
 SELECT Numero,IdConcepto,DescConc,TipoConc,Tarifa,TipoTarif,TipoAfilVeh,CdTipoVeh,TipoVehiculo,TipoTerc
,CdCiuOrigen,LO.Localidad AS CiuOrigen,LO.IdDep AS CdDepOri,DPO.Departamento AS DptoOrigen
,CdCiuDestino,LD.Localidad AS CiuDestino,LD.IdDep AS CdDepDest,DPD.Departamento AS DptoDestino
,pVehiculo,CedConductor,CDT.RazonSocial AS NomConductor,CdRuta,R.Ruta,NitCliente,T.RazonSocial AS NomCliente,CdMercancia,M.DescripMcia
,TipoVigencia,FechaInicial,FechaFinal,F.Anulado
 FROM TraFijos AS F LEFT JOIN TiposVeh AS TV ON F.CdTipoVeh=TV.IdTipoVeh
 LEFT JOIN Localidades AS LO ON F.CdCiuOrigen=LO.IdLocal LEFT JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
 LEFT JOIN Localidades AS LD ON F.CdCiuDestino=LD.IdLocal LEFT JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 LEFT JOIN Rutas AS R ON F.CdRuta=R.IdRuta LEFT JOIN Terceros AS CDT ON F.CedConductor=CDT.IdTercero
 LEFT JOIN Terceros AS T ON F.NitCliente=T.IdTercero LEFT JOIN Mercancias AS M ON F.CdMercancia=M.IdMercancia
 WHERE TipoConc LIKE ISNULL(@pmTipoConc,'%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsMudOrdenVeh] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdVehiculo VARCHAR(10),@pmTipoAfiVehic VARCHAR(10)
,@pmNitEmpresa VARCHAR(16),@pmNomEmpresa VARCHAR(150),@pmnRemolque VARCHAR(10),@pmIdConductor VARCHAR(16),@pmIdCondAux VARCHAR(16)
,@pmTipDocRef VARCHAR(3),@pmNumDocRef INT,@pmCdCiaRef CHAR(2),@pmCdTipoOper VARCHAR(4),@pmTipoIngreso VARCHAR(10),@pmTarifaIng DECIMAL(14,4),@pmVrIngreso MONEY
 AS 
 INSERT INTO Trn_MudOrdenVeh (TipDoc,NumOrden,IdCia,Item,IdVehiculo,TipoAfiVehic,NitEmpresa,NomEmpresa,nRemolque,IdConductor,IdCondAux,TipDocRef,NumDocRef,CdCiaRef,CdTipoOper,TipoIngreso,TarifaIng,VrIngreso)
 VALUES (@pmTipDoc,@pmNumOrden,@pmIdCia,@pmItem,@pmIdVehiculo,@pmTipoAfiVehic,@pmNitEmpresa,@pmNomEmpresa,@pmnRemolque,@pmIdConductor,@pmIdCondAux,@pmTipDocRef,@pmNumDocRef,@pmCdCiaRef,@pmCdTipoOper,@pmTipoIngreso,@pmTarifaIng,@pmVrIngreso)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraFijos] @pmNumero INT
 AS 
 SELECT Numero,IdConcepto,DescConc,TipoConc,Tarifa,TipoTarif,TipoAfilVeh,CdTipoVeh,TipoTerc,Anulado
,CdCiuOrigen,CdCiuDestino,pVehiculo,CedConductor,CdRuta,TipoVigencia,FechaInicial,FechaFinal,NitCliente,CdMercancia
 FROM TraFijos
 WHERE Numero=@pmNumero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraFijos] @pmNumero INT,@pmIdConcepto VARCHAR(4),@pmDescConc VARCHAR(250),@pmTipoConc VARCHAR(10)
,@pmTarifa DECIMAL(14,4),@pmTipoTarif CHAR(1),@pmTipoAfilVeh VARCHAR(10),@pmCdTipoVeh VARCHAR(4),@pmAnulado BIT,@pmTipoTerc CHAR(1)
,@pmCdCiuOrigen VARCHAR(8),@pmCdCiuDestino VARCHAR(8),@pmpVehiculo VARCHAR(10),@pmCedConductor VARCHAR(16),@pmCdRuta VARCHAR(4)
,@pmTipoVigencia CHAR(1),@pmFechaInicial SMALLDATETIME,@pmFechaFinal SMALLDATETIME,@pmNitCliente VARCHAR(16),@pmCdMercancia VARCHAR(16)
 AS 
 INSERT INTO TraFijos (Numero,IdConcepto,DescConc,TipoConc,Tarifa,TipoTarif,TipoAfilVeh,CdTipoVeh,TipoTerc,Anulado,CdCiuOrigen,CdCiuDestino,pVehiculo,CedConductor,CdRuta,TipoVigencia,FechaInicial,FechaFinal,NitCliente,CdMercancia)
 VALUES (@pmNumero,@pmIdConcepto,@pmDescConc,@pmTipoConc,@pmTarifa,@pmTipoTarif,@pmTipoAfilVeh,@pmCdTipoVeh,@pmTipoTerc
,@pmAnulado,@pmCdCiuOrigen,@pmCdCiuDestino,@pmpVehiculo,@pmCedConductor,@pmCdRuta,@pmTipoVigencia,@pmFechaInicial,@pmFechaFinal,@pmNitCliente,@pmCdMercancia)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraFijos] @pmNumero INT,@pmIdConcepto VARCHAR(4),@pmDescConc VARCHAR(250),@pmTipoConc VARCHAR(10),@pmTarifa DECIMAL(14,4),@pmTipoTarif CHAR(1),@pmTipoAfilVeh VARCHAR(10)
,@pmCdTipoVeh VARCHAR(4),@pmAnulado BIT,@pmTipoTerc CHAR(1),@pmCdCiuOrigen VARCHAR(8),@pmCdCiuDestino VARCHAR(8),@pmpVehiculo VARCHAR(10),@pmCedConductor VARCHAR(16),@pmCdRuta VARCHAR(4)
,@pmTipoVigencia CHAR(1),@pmFechaInicial SMALLDATETIME,@pmFechaFinal SMALLDATETIME,@pmNitCliente VARCHAR(16),@pmCdMercancia VARCHAR(16)
 AS 
 UPDATE TraFijos SET IdConcepto=@pmIdConcepto,DescConc=@pmDescConc,TipoConc=@pmTipoConc,Tarifa=@pmTarifa,TipoTarif=@pmTipoTarif,TipoAfilVeh=@pmTipoAfilVeh,CdTipoVeh=@pmCdTipoVeh
,Anulado=@pmAnulado,TipoTerc=@pmTipoTerc,CdCiuOrigen=@pmCdCiuOrigen,CdCiuDestino=@pmCdCiuDestino,pVehiculo=@pmpVehiculo,CedConductor=@pmCedConductor,CdRuta=@pmCdRuta
,TipoVigencia=@pmTipoVigencia,FechaInicial=@pmFechaInicial,FechaFinal=@pmFechaFinal,NitCliente=@pmNitCliente,CdMercancia=@pmCdMercancia
 WHERE Numero=@pmNumero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraAnticipos] @pmTipDoc VARCHAR(3),@pmAnticipo INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Anticipo,IdCia,Fecha,IdConcepto,TipMuc,Manifiesto,IdCiaMuc,IdVehiculo,IdPoseedor,IdConductor,VrAnticipo,VrAbonado,Modalidad,TipoPago
,NumCheque,FecCheque,IdCta,IdCuenta,CedBenef,Beneficiario,Cantidad,FechaVence,NumPresAnt,PreAnticipo,NumCargue,CiaCargue,TipCom,Comprobante,IdCiaCom
,Anulado,NumDev,FecDev,Observacion,IdEstado,VrDeduccion,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_TraAnticipos
 WHERE TipDoc=@pmTipDoc AND Anticipo=@pmAnticipo AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraAnticipos] @pmTipDoc VARCHAR(3),@pmAnticipo INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipMuc VARCHAR(3),@pmManifiesto INT,@pmIdCiaMuc CHAR(2),@pmIdVehiculo VARCHAR(10),@pmIdPoseedor VARCHAR(16),@pmIdConductor VARCHAR(16),@pmVrAnticipo MONEY,@pmVrAbonado MONEY
,@pmModalidad VARCHAR(10),@pmTipoPago VARCHAR(10),@pmNumCheque VARCHAR(20),@pmFecCheque SMALLDATETIME,@pmIdCta VARCHAR(4),@pmIdCuenta VARCHAR(16),@pmBeneficiario VARCHAR(150),@pmCantidad DECIMAL(14,4),@pmFechaVence SMALLDATETIME,@pmNumPresAnt INT, @pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT
,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmCedBenef VARCHAR(16),@pmPreAnticipo INT,@pmNumCargue INT,@pmCiaCargue CHAR(2),@pmVrDeduccion MONEY,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraAnticipos (TipDoc,Anticipo,IdCia,Fecha,IdConcepto,TipMuc,Manifiesto,IdCiaMuc,IdVehiculo,IdPoseedor,IdConductor,VrAnticipo,VrAbonado,Modalidad,TipoPago,NumCheque,FecCheque,IdCta,IdCuenta,Beneficiario,CedBenef,Cantidad,FechaVence,NumPresAnt,PreAnticipo,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario,NumCargue,CiaCargue,VrDeduccion)
 VALUES (@pmTipDoc,@pmAnticipo,@pmIdCia,@pmFecha,@pmIdConcepto,@pmTipMuc,@pmManifiesto,@pmIdCiaMuc,@pmIdVehiculo,@pmIdPoseedor,@pmIdConductor,@pmVrAnticipo,@pmVrAbonado,@pmModalidad,@pmTipoPago,@pmNumCheque,@pmFecCheque,@pmIdCta,@pmIdCuenta,@pmBeneficiario,@pmCedBenef,@pmCantidad,@pmFechaVence,@pmNumPresAnt,@pmPreAnticipo,@pmTipCom,@pmComprobante
,@pmIdCiaCom,@pmAnulado,@pmNumDev,@pmFecDev,@pmObservacion,@pmIdEstado,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmNumCargue,@pmCiaCargue,@pmVrDeduccion)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraAnticipos] @pmTipDoc VARCHAR(3),@pmAnticipo INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipMuc VARCHAR(3),@pmManifiesto INT,@pmIdCiaMuc CHAR(2),@pmIdVehiculo VARCHAR(10),@pmIdPoseedor VARCHAR(16),@pmIdConductor VARCHAR(16),@pmVrAnticipo MONEY,@pmVrAbonado MONEY,@pmModalidad VARCHAR(10)
,@pmTipoPago VARCHAR(10),@pmNumCheque VARCHAR(20),@pmFecCheque SMALLDATETIME,@pmIdCta VARCHAR(4),@pmIdCuenta VARCHAR(16),@pmBeneficiario VARCHAR(150),@pmCantidad DECIMAL(14,4),@pmFechaVence SMALLDATETIME,@pmNumPresAnt INT, @pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME
,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmCedBenef VARCHAR(16),@pmPreAnticipo INT,@pmNumCargue INT,@pmCiaCargue CHAR(2),@pmVrDeduccion MONEY,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_TraAnticipos SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,TipMuc=@pmTipMuc,Manifiesto=@pmManifiesto,IdCiaMuc=@pmIdCiaMuc,IdVehiculo=@pmIdVehiculo,IdPoseedor=@pmIdPoseedor,IdConductor=@pmIdConductor,VrAnticipo=@pmVrAnticipo,VrAbonado=@pmVrAbonado,Modalidad=@pmModalidad,TipoPago=@pmTipoPago,NumCheque=@pmNumCheque,FecCheque=@pmFecCheque
,IdCta=@pmIdCta,IdCuenta=@pmIdCuenta,Beneficiario=@pmBeneficiario,Cantidad=@pmCantidad,FechaVence=@pmFechaVence,NumPresAnt=@pmNumPresAnt,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,CedBenef=@pmCedBenef
,PreAnticipo=@pmPreAnticipo,FecUpdate=@pmFecUpdate,NumCargue=@pmNumCargue,CiaCargue=@pmCiaCargue,VrDeduccion=@pmVrDeduccion
 WHERE TipDoc=@pmTipDoc AND Anticipo=@pmAnticipo AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryComDetalleMud] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT D.TipCom,TipoCom,D.Comprobante,D.IdCia AS CdCia,Compania,D.Item,D.Fecha,D.IdCuenta AS CdCuenta,NomCuenta,D.Detalle,D.VrDebito,D.VrCredito
,D.IdTercero AS NitTercero,T.RazonSocial,D.IdVehiculo,D.IdCCosto,CCosto,D.IdSubCos,SubCosto,D.VehPropio,D.VrBase,D.TarifaBase,D.TipDoc,TipoDoc,D.Documento,D.IdCiaDoc
,CodConce,Concepto,D.TipFac,D.Factura,D.IdCiaFac,D.ItemFac,D.FecVence,D.CodCta,D.NumCheque,D.Integrado,D.TipoAplica,D.Consolida,D.CodCargo,D.NitOtros,NT.RazonSocial AS NomTercOtros
,D.CodSubgpo,D.CiuOrigen,LD.Localidad AS NomCiudad,D.CodAgncia,D.Referncia,D.NitDoc,ND.RazonSocial AS NomTercDoc,D.TipDocRef,D.DocRef,D.IdCiaRef,D.CdConcTrib,D.CdTarifTrib
,D.NumEstablec,D.PtoEmision,D.Num_Autoriza,D.FechAutoriza,D.CdCuentaNiif,D.TimeSys AS FechaCrea,D.FecUpdate AS FechaAct,D.IdCiaCrea,D.IdUsuario AS CdUsuario,Usuario
,P.Movimiento,P.Tercero,P.Vehiculo
 FROM Trn_ComDetalle AS D INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia INNER JOIN TiposCom AS TC ON D.TipCom=TC.IdCom
 INNER JOIN Puc AS P ON D.IdCuenta=P.IdCuenta INNER JOIN Terceros AS T ON D.IdTercero=T.IdTercero
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 LEFT JOIN CentroCosto AS CC ON D.IdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON D.IdSubCos=SC.IdSubCos
 LEFT JOIN Sys_TiposDoc AS TD ON D.TipDoc=TD.IdDoc LEFT JOIN Conceptos AS CN ON D.CodConce=CN.IdConcepto
 LEFT JOIN Localidades AS LD ON D.CiuOrigen=LD.IdLocal LEFT JOIN Terceros AS ND ON D.NitDoc=ND.IdTercero
 LEFT JOIN Terceros AS NT ON D.NitOtros=NT.IdTercero
 WHERE D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND (SUBSTRING(D.IdCuenta,1,1)='5' OR SUBSTRING(D.IdCuenta,1,1)='6' OR SUBSTRING(D.IdCuenta,1,1)='2')
 AND (@pmIdCia IS NULL OR D.IdCia=@pmIdCia)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenServVeh] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT O.TipDoc,TipoDoc,O.NumOrden,O.IdCia AS CdCia,Compania,O.Fecha,O.FecDespacho,O.IdTipoServ AS CdTipoServ,TipoServicio,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia
,O.IdOrigen,LO.Localidad AS CiuOrigen,LO.IdDep AS CdDepOrig,DPO.Departamento AS DptoOrigen,O.IdDestino,LD.Localidad AS CiuDestino,LD.IdDep AS CdDepDest,DPD.Departamento AS DptoDestino
,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest,O.VrServicio,O.VrEmpaque,O.VrBodega,O.VrOtros,O.VrDescuento,O.VrImpuesto,O.VrNeto,O.VrDeclarado,O.VrSeguro
,O.IdVend,VN.RazonSocial AS NomVendedor,O.NomContacto,O.TelsContacto,O.emlContacto,O.ContactoDest,O.emlContDest,O.NitRemitente,O.Remitente,O.NitDestinatario,O.Destinatario,O.Servicios,O.FormaPago,O.Referencia,O.TipInv,O.NumInvent,O.IdCiaInv
,O.EstFactura,O.TipCausac,O.Causacion,O.CdCiaCau,O.EstCumplido,O.FechaCump,O.TipoRuta,O.Volumen,EstadoBod,O.CdBodega,B.Bodega AS NomBodega,FechaIngBod,FechaRetBod,O.Anulado,O.FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado
--Detalles de vehículos
,VH.Item,VH.IdVehiculo,VH.TipoAfiVehic,VH.NitEmpresa,NomEmpresa,VH.nRemolque,VH.IdConductor,NC.RazonSocial AS Conductor,IdCondAux,NA.RazonSocial AS CondAuxiliar,VH.TipDocRef AS TipRem,VH.NumDocRef AS NumRemesa,VH.CdCiaRef AS CdCiaRem
,VH.CdTipoOper,TipoOperacion,VH.TipoIngreso,VH.TarifaIng,VH.VrIngreso,V.NumVeh,V.IdMarca AS CdMarca,MV.Marca,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.Modelo,V.IdColor AS CdColor,NomColor,V.Config,V.IdPoseedor AS CdPoseedor,NP.RazonSocial AS Poseedor
,V.NumSoat,VigSoat,V.NitEmpSoat,EST.RazonSocial AS NomEmpSoat,V.SerieChasis,V.CarrCapac,V.UndCapc
,CDT.Licencia AS NumLicencia,CDT.CatLicencia,CDT.VigLicencia,CDA.Licencia AS AuxNumLicencia,CDA.CatLicencia AS AuxCategLic,CDA.VigLicencia AS AuxVigLicencia
--datos de remesas
,ISNULL(RM.RemVrTotal,0) AS RemVrTotal,ISNULL(RM.RemVrPago,0) AS RemVrPago,ISNULL(RM.MucNumero,0) AS MucNumero,ISNULL(RM.MucVrPago,0) AS MucVrPago
,ISNULL(OP.OdpNumero,0) AS NumOrdPago,ISNULL(OP.OdpVrPago,0) AS OdpVrPago,ISNULL(OP.OdpTarifa,0) AS OdpTarifa,ISNULL(OP.OdpVrTotal,0) AS OdpTotal
--datos del tercero
,T.Codigo AS TercCod,T.TipoId,T.Dv,T.Direccion,T.IdLocal AS TercCdLocal,L.Localidad AS TercLocalidad,T.Telefono AS TercTelefono,T.TelMovil AS TercTerCelular,T.e_mail AS TercEmail
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupo,GrupoClie,CLI.IdPlazo AS CdPlazo,Plazo
,TS.Transporte,TS.Bodega,TS.Empaque,TS.Personal,O.OrigenAdd,O.TimeSys AS FechaCrea,O.FecUpdate,O.IdCiaCrea,O.IdUsuario AS CdUsuario,U.Usuario
 FROM Trn_MudOrdenServ AS O INNER JOIN Trn_MudOrdenVeh AS VH ON O.TipDoc=VH.TipDoc AND O.NumOrden=VH.NumOrden AND O.IdCia=VH.IdCia
 INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN TercCliente AS CLI ON O.IdCliente=CLI.IdClie
 INNER JOIN Terceros AS VN ON O.IdVend=VN.IdTercero INNER JOIN MudServicios AS TS ON O.IdTipoServ=TS.IdTipoServ
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal INNER JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona 
 INNER JOIN Vehiculos AS V ON VH.IdVehiculo=V.IdVehiculo INNER JOIN Terceros AS NC ON VH.IdConductor=NC.IdTercero  
 INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca
 INNER JOIN TiposCol AS CLR ON V.IdColor=CLR.IdColor INNER JOIN Terceros AS NP ON V.IdPoseedor=NP.IdTercero
 LEFT JOIN TercCndtores AS CDT ON VH.IdConductor=CDT.IdConductor
 LEFT JOIN Terceros AS NA ON VH.IdCondAux=NA.IdTercero LEFT JOIN TercCndtores AS CDA ON VH.IdCondAux=CDA.IdConductor
 LEFT JOIN Terceros AS EST ON V.NitEmpSoat=EST.IdTercero LEFT JOIN TiposOperac AS TP ON VH.CdTipoOper=TP.IdTipoOper
 LEFT JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo LEFT JOIN Plazos AS PZ ON CLI.IdPlazo=PZ.IdPlazo
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia LEFT JOIN AlmBodegas AS B ON O.CdBodega=B.IdBodega
 LEFT JOIN (SELECT D.TipDoc AS RemTipo,D.NumOrden AS RemNumero,D.IdCia AS RemIdCia
 ,SUM(CASE D.UndTarifa WHEN 'PESO' THEN D.PesoNeto*D.TarifClie WHEN 'UNIDADES' THEN D.Cantidad*D.TarifClie WHEN 'VOLUMEN' THEN D.Volumen*D.TarifClie ELSE D.PesoNeto*D.TarifClie END) AS RemVrTotal
 ,SUM(CASE D.UndTarifPago WHEN 'PESO' THEN D.PesoNeto*D.TarifPago WHEN 'UNIDADES' THEN D.Cantidad*D.TarifPago WHEN 'VOLUMEN' THEN D.Volumen*D.TarifPago ELSE D.PesoNeto*D.TarifPago END) AS RemVrPago
 ,SUM(CASE D.UndTarifPago WHEN 'PESO' THEN D.PesoNeto*MR.TarifPago WHEN 'UNIDADES' THEN D.Cantidad*MR.TarifPago WHEN 'VOLUMEN' THEN D.Volumen*MR.TarifPago ELSE D.PesoNeto*MR.TarifPago END) AS MucVrPago
 ,MAX(MR.Manifiesto) AS MucNumero
  FROM Trn_TraRemMcias AS D INNER JOIN Trn_TraRemesa AS R ON D.TipDoc=R.TipDoc AND D.NumOrden=R.NumOrden AND D.IdCia=R.IdCia
  LEFT JOIN Trn_TraManifRem AS MR ON D.TipDoc=MR.TipRem AND D.NumOrden=MR.Remesa AND D.IdCia=MR.IdCiaRem AND D.Item=MR.ItemRem
  LEFT JOIN Trn_TraManifiesto AS M ON MR.TipDoc=M.TipDoc AND MR.Manifiesto=M.Manifiesto AND MR.IdCia=M.IdCia
  WHERE D.TipDoc='RMT' AND R.Anulado=0 AND ISNULL(M.Anulado,0)=0 GROUP BY D.TipDoc,D.NumOrden,D.IdCia) AS RM ON VH.TipDocRef=RM.RemTipo AND VH.NumDocRef=RM.RemNumero AND VH.CdCiaRef=RM.RemIdCia
 LEFT JOIN (SELECT OD.TipRem,OD.Remesa,OD.IdCiaRem,MAX(OD.OrdPago) AS OdpNumero,MAX(OD.Manifiesto) AS OdpNumManif
 ,SUM(CASE OD.UndTarifa WHEN 'PESO' THEN OD.PesoNeto*OD.TarifPago WHEN 'UNIDADES' THEN OD.Cantidad*OD.TarifPago WHEN 'VOLUMEN' THEN OD.Volumen*OD.TarifPago ELSE OD.PesoNeto*OD.TarifPago END) AS OdpVrPago
 ,MAX(OM.TarifaFlete) AS OdpTarifa,SUM(OM.VrTotalFletes-OM.VrDescuento) AS OdpVrTotal
 FROM Trn_TraOrdenRemesas AS OD INNER JOIN Trn_TraOrdenManif AS OM ON OD.TipDoc=OM.TipDoc AND OD.OrdPago=OM.OrdPago AND OD.IdCia=OM.IdCia
 INNER JOIN Trn_TraOrdenPago AS O ON OD.TipDoc=O.TipDoc AND OD.OrdPago=O.OrdPago AND OD.IdCia=O.IdCia
 WHERE OD.TipRem='RMT' AND O.Anulado=0 GROUP BY OD.TipRem,OD.Remesa,OD.IdCiaRem) AS OP ON VH.TipDocRef=OP.TipRem AND VH.NumDocRef=OP.Remesa AND VH.CdCiaRef=OP.IdCiaRem
 WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenConcLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT O.TipDoc,O.NumOrden,O.IdCia,O.FecDespacho,O.IdCliente,D.Item,D.IdConcepto AS CdConcepto,D.Descripcion,CD.Concepto,D.ValorTotal,D.TarifaIva,D.TipoConc,D.TipoRubro
,D.CdCuenta,D.NitTercero,NT.RazonSocial AS NomTercero,D.Referencia,D.NumDocRef,D.TipoReg,D.TarifSeguro,D.CodTarSeg,D.PlacaVehic,D.TarifaPago
,D.NumDocFac,D.IndFactItem,D.FechaReg,D.CdUsuario,U.Usuario,CD.RubroConc,CD.MudReajuste
--datos de facturas
,FC.FacNumero,FC.FacFecha,ISNULL(FC.FacValor,0) AS FacVrTotal,ISNULL(FC.FacCosto,0) AS FacVrCosto,ISNULL(FC.FacImpuesto,0) AS FacVrIva,ISNULL(FC.FacSeguro,0) AS FacVrSeguro
,FacBaseIngreso,DfcBaseIngreso,FacReajuste,DfcReajuste
 FROM Trn_MudOrdenServ AS O INNER JOIN Trn_MudOrdenConc AS D ON O.TipDoc=D.TipDoc AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
 INNER JOIN ConcDiversos AS CD ON D.IdConcepto=CD.IdConcepto
 LEFT JOIN Terceros AS NT ON D.NitTercero=NT.IdTercero LEFT JOIN adm_Usuarios AS U ON D.CdUsuario=U.IdUsuario
 --Facturas item por item
 LEFT JOIN (SELECT TipRem AS FacTipOrd,Remesa AS FacNumOrden,IdCiaRem AS FacIdCiaOrd,ItemRem AS FacItemOrd,MAX(FecRemesa) AS FacFecha
,SUM(CASE WHEN SUBSTRING(FR.TipDoc,1,2)='DF' THEN (Cantidad*VrUnitario)*-1 ELSE Cantidad*VrUnitario END) AS FacValor
,SUM(CASE WHEN SUBSTRING(FR.TipDoc,1,2)='DF' THEN (Cantidad*VrCosto)*-1 ELSE Cantidad*VrCosto END) AS FacCosto
,SUM(CASE WHEN SUBSTRING(FR.TipDoc,1,2)='DF' THEN VrImpuesto*-1 ELSE VrImpuesto END) AS FacImpuesto
,SUM(CASE WHEN SUBSTRING(FR.TipDoc,1,2)='DF' THEN VrSeguroRem*-1 ELSE VrSeguroRem END) AS FacSeguro
,SUM(CASE WHEN SUBSTRING(FR.TipDoc,1,2)='FC' THEN VrDeclMcia ELSE 0 END) AS FacDeclarado
,MAX(CASE WHEN SUBSTRING(FR.TipDoc,1,2)='FC' THEN FR.Factura ELSE 0 END) AS FacNumero
,SUM(CASE WHEN FR.Referencia3='TRANSPORTE' AND SUBSTRING(FR.TipDoc,1,2)='FC' THEN Cantidad*VrUnitario ELSE 0 END) AS FacBaseIngreso
,SUM(CASE WHEN FR.Referencia3='TRANSPORTE' AND SUBSTRING(FR.TipDoc,1,2)='DF' THEN (Cantidad*VrUnitario)*-1 ELSE 0 END) AS DfcBaseIngreso
,SUM(CASE WHEN FR.Referencia3='TRANSPORTE' AND C.MudReajuste=1 AND SUBSTRING(FR.TipDoc,1,2)='FC' THEN Cantidad*VrUnitario ELSE 0 END) AS FacReajuste
,SUM(CASE WHEN FR.Referencia3='TRANSPORTE' AND C.MudReajuste=1 AND SUBSTRING(FR.TipDoc,1,2)='DF' THEN Cantidad*VrUnitario ELSE 0 END) AS DfcReajuste
 FROM Trn_TraFacRemesas AS FR LEFT JOIN ConcDiversos AS C ON FR.CdConcepto=C.IdConcepto
 WHERE FR.TipRem='OSM' GROUP BY TipRem,Remesa,IdCiaRem,ItemRem) AS FC ON D.TipDoc=FC.FacTipOrd AND D.NumOrden=FC.FacNumOrden AND D.IdCia=FC.FacIdCiaOrd AND D.Item=FC.FacItemOrd
 WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO