IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paInsTraAnticipos]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[paInsTraAnticipos] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paInsTraCumplido]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[paInsTraCumplido] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paQryTraAnticipos]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[paQryTraAnticipos] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paQryTraAnticiposFmt]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[paQryTraAnticiposFmt] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paQryTraAnticiposLta]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[paQryTraAnticiposLta] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paQryTraAnticiposRel]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[paQryTraAnticiposRel] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paQryTraAnticiposRelAbo]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[paQryTraAnticiposRelAbo] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paQryTraCumplido]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[paQryTraCumplido] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paQryTraCumplidoFmt]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[paQryTraCumplidoFmt] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paQryTraCumplidoLta]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[paQryTraCumplidoLta] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paQryTraCumplidoRel]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[paQryTraCumplidoRel] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paQryTraCumplidoRelDet]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[paQryTraCumplidoRelDet] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paUpTraAnticipos]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[paUpTraAnticipos] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paUpTraCumplido]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[paUpTraCumplido] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [paQryTraAnticiposRelAbo] @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 ,AP.TipDoc AS TipDocAbo,AP.Documento AS NumDocAbo,AP.IdCia AS CdCiaAbo,Item,AP.Fecha AS FechaAbo,VrAbono,TipoAplica,Detalle ,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,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 --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 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 INNER JOIN Trn_TraManifiesto AS M ON A.TipMuc=M.TipDoc AND A.Manifiesto=M.Manifiesto AND A.IdCiaMuc=M.IdCia INNER JOIN Localidades AS CO ON M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep INNER 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 Trn_TraAntAbonos AS AP ON A.TipDoc=AP.TipAnt AND A.Anticipo=AP.Anticipo AND A.IdCia=AP.IdCiaAnt 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 ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [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,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,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 CdCuenta,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 INNER JOIN Trn_TraManifiesto AS M ON A.TipMuc=M.TipDoc AND A.Manifiesto=M.Manifiesto AND A.IdCiaMuc=M.IdCia INNER JOIN Localidades AS CO ON M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep INNER 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 [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,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 --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 --,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 INNER JOIN Trn_TraManifiesto AS M ON A.TipMuc=M.TipDoc AND A.Manifiesto=M.Manifiesto AND A.IdCiaMuc=M.IdCia INNER JOIN Localidades AS CO ON M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep INNER 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 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 ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [paQryTraCumplidoRelDet] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME, @pmIdCia CHAR(2)=Null ,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmIdConductor VARCHAR(16)=Null AS SELECT CU.TipDoc AS TipCum,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,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic,CU.Anulado AS Anuldo,CU.FecDev AS FechaDev,CU.Observacion AS Observ,CU.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 ,CU.TimeSys AS FechaCrea,CU.FecUpdate AS FechaAct,CU.IdCiaCrea AS CdCiaCrea,CU.IdUsuario AS CdUsuario,Usuario ,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 MucObserv --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,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 --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 FROM Trn_TraCumplido AS CU INNER JOIN Companias AS CN ON CU.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON CU.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON CU.IdUsuario=U.IdUsuario 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 INNER JOIN Trn_TraCumRemesas AS D ON CU.TipDoc=D.TipDoc AND CU.Cumplido=D.Cumplido AND CU.IdCia=D.IdCia LEFT JOIN Localidades AS CO ON CU.CdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep LEFT JOIN Localidades AS CD ON CU.CdDestino=CD.IdLocal INNER 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 LEFT JOIN Terceros AS NE ON V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero WHERE CU.TipDoc=@pmTipDoc AND CU.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND CU.IdCia LIKE ISNULL(@pmIdCia,'%%') AND CU.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%') AND M.IdConductor LIKE ISNULL(@pmIdConductor,'%') ORDER BY CU.IdCia,CU.Cumplido GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [paQryTraCumplidoRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME, @pmIdCia CHAR(2)=Null ,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmIdConductor VARCHAR(16)=Null AS SELECT CU.TipDoc AS TipCum,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,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic,CU.Anulado AS Anuldo,CU.FecDev AS FechaDev,CU.Observacion AS Observ,CU.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 ,CU.TimeSys AS FechaCrea,CU.FecUpdate AS FechaAct,CU.IdCiaCrea AS CdCiaCrea,CU.IdUsuario AS CdUsuario,Usuario ,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 MucObserv --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 FROM Trn_TraCumplido AS CU INNER JOIN Companias AS CN ON CU.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON CU.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON CU.IdUsuario=U.IdUsuario 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 Localidades AS CO ON CU.CdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep LEFT JOIN Localidades AS CD ON CU.CdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep LEFT JOIN Rutas AS R ON CU.CdRuta=R.IdRuta LEFT JOIN Terceros AS NE ON V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero WHERE CU.TipDoc=@pmTipDoc AND CU.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND CU.IdCia LIKE ISNULL(@pmIdCia,'%%') AND CU.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%') AND M.IdConductor LIKE ISNULL(@pmIdConductor,'%') ORDER BY CU.IdCia,CU.Cumplido GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [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 --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,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 --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 INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep LEFT JOIN Localidades AS CD ON CU.CdDestino=CD.IdLocal INNER 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 [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,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 ,C.TimeSys AS FechaCrea,C.FecUpdate AS FechaAct,C.IdCiaCrea AS CdCiaCrea,C.IdUsuario AS CdUsuario,Usuario FROM Trn_TraCumplido AS C INNER JOIN Trn_TraManifiesto AS M ON C.TipMuc=M.TipDoc AND C.Manifiesto=M.Manifiesto AND C.IdCiaMuc=M.IdCia 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 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 INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep LEFT JOIN Localidades AS CD ON C.CdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep LEFT JOIN Rutas AS R ON C.CdRuta=R.IdRuta 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 [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),@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,FecUpdate=@pmFecUpdate WHERE TipDoc=@pmTipDoc AND Anticipo=@pmAnticipo AND IdCia=@pmIdCia GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [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),@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,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario) 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,@pmTipCom,@pmComprobante ,@pmIdCiaCom,@pmAnulado,@pmNumDev,@pmFecDev,@pmObservacion,@pmIdEstado,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario) GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [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,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,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 [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,Cantidad,FechaVence,NumPresAnt,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 [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,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario 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 [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),@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,FecUpdate=@pmFecUpdate WHERE TipDoc=@pmTipDoc AND Cumplido=@pmCumplido AND IdCia=@pmIdCia GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [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),@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,Observacion,IdEstado,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario) 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,@pmObservacion,@pmIdEstado,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)