if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAfMantnmto]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAfMantnmto]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraDevLgo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraDevLgo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraOrdenLiq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraOrdenLiq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraOrdenServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraOrdenServ]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAfMantnmto]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryAfMantnmto]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAfMantnmtoCr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryAfMantnmtoCr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAfMantnmtoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryAfMantnmtoLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevLgo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevLgo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevLgoFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevLgoFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevLgoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevLgoLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDevLgoRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDevLgoRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenLiq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenLiq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenLiqFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenLiqFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenLiqLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenLiqLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenLiqRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenLiqRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenServ]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenServFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenServFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenServLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenServLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAfMantnmto]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAfMantnmto]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraDevLgo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraDevLgo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraOrdenLiq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraOrdenLiq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraOrdenServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraOrdenServ]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenLiqFmt] @pmTipDoc VARCHAR(3),@pmLiquidacionIni INT,@pmLiquidacionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT L.TipDoc AS IdTipDoc,TipoDoc,L.Liquidacion,L.IdCia AS CdCia,Compania,L.Fecha AS FechaLiq,L.IdConcepto AS CdConcepto,Concepto,TipOds,L.NumOrden,IdCiaOds,O.Fecha AS FecOrden
,L.IdVehiculo AS PlacaVeh,L.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,L.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,L.TipoAfiVehic
,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia,O.IdDestino,LD.Localidad AS NomDestino,O.TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo
,O.FecDespacho,O.FecRecibo,O.FecIngCertif,O.FecCertificado,TipoResiduo,O.RemCliente,O.NumRemesa,O.CdCiaRem,L.NumDocSop,L.CiaDocSop,L.VrTotal,L.VrCosto,L.VrAnticipo,L.VrGastos,VrIva,VrRetencion,VrReteIca
,VrReteCREE,VrReteIva,VrOtros,VrDescuento,VrNeto,L.Cantidad,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,CodTarRtc
,L.CdTarAvta,L.TarifaAvta,L.VrImpAvTa,L.CdTarBom,L.TarifaBom,L.VrBomberil,L.Referencia,L.Referencia2,L.CxPagar,CdPlazo,Plazo,DiasPago,FechaVence,L.UndMed,Unidad,L.TipCom,L.Comprobante,L.IdCiaCom,L.Anulado,L.NumDev,L.FecDev,L.Observacion AS Observ
,L.IdEstado AS CdEstado,Estado,TipEgr,Egreso,IdCiaEgr,NumCheque,VrConcCargos,VrConcDed,L.OrigenAdd,L.TimeSys AS FechaCrea,L.FecUpdate AS FechaModif,L.IdCiaCrea AS CdCiaCrea,L.IdUsuario AS CdUsuario,Usuario,Leyenda
--vehiuculo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,LT.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,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
,L.CdCCosto,CCosto,L.CdSubCos,SubCosto
 FROM Trn_TraOrdenLiq AS L INNER JOIN Companias AS CN ON L.IdCia=CN.IdCia INNER JOIN Conceptos AS CT ON L.IdConcepto=CT.IdConcepto
 INNER JOIN adm_Usuarios AS U ON L.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS ED ON L.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS NP ON L.IdPoseedor=NP.IdTercero INNER JOIN Terceros AS CDT ON L.IdConductor=CDT.IdTercero
 INNER JOIN Trn_TraOrdenServ AS O ON L.TipOds=O.TipDoc AND L.NumOrden=O.NumOrden AND L.IdCiaOds=O.IdCia
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion 
 INNER JOIN Sys_TiposDoc AS TD ON L.TipDoc=TD.IdDoc INNER JOIN Localidades AS LT ON T.IdLocal=LT.IdLocal
 INNER JOIN Vehiculos AS V ON L.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 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 MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal LEFT JOIN Sys_Um AS UM ON L.UndMed=UM.UndMed
 LEFT JOIN Plazos AS PZ ON L.CdPlazo=PZ.IdPlazo LEFT JOIN CentroCosto AS CC ON L.CdCCosto=CC.IdCCosto
 LEFT JOIN SubCentros AS SC ON L.CdSubCos=SC.IdSubCos
 WHERE L.TipDoc=@pmTipDoc AND L.Liquidacion BETWEEN @pmLiquidacionIni AND @pmLiquidacionFin AND L.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraDevLgoFmt] @pmTipDev VARCHAR(3),@pmDevolucionIni INT,@pmDevolucionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT D.TipDev AS IdTipDev,TipoDoc,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc,D.Liquidacion AS NumLiquida,D.IdCiaDoc,FecDoc
,TipOds,L.NumOrden,IdCiaOds,L.IdVehiculo AS PlacaVeh,L.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,L.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,L.TipoAfiVehic
,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia,O.IdDestino,LD.Localidad AS NomDestino,O.TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo
,O.FecDespacho,O.FecRecibo,O.FecIngCertif,O.FecCertificado,TipoResiduo,O.RemCliente,O.NumRemesa,O.CdCiaRem
,D.VrTotal,D.VrCosto,D.VrAnticipo,D.VrGastos,D.VrIva,D.VrRetencion,D.VrReteIca,D.VrReteCREE,D.VrReteIva,D.VrOtros,D.VrDescuento,L.VrConcCargos,L.VrConcDed,D.VrImpAvTa,D.VrBomberil,D.VrNeto,D.Cantidad
,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,L.TarifaAvta,L.TarifaBom,L.Referencia,CdPlazo,Plazo,DiasPago,FechaVence,L.UndMed,Unidad
,D.CxPagar,D.TipCom,D.Comprobante,D.IdCiaCom,D.ModdDev,D.Observacion AS Observ,D.IdEstado AS CdEstado,D.OrigenAdd,D.TimeSys AS FechaCrea
,D.FecUpdate AS FechaModif,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS CdUsuario,Usuario,Leyenda
--vehiuculo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,LT.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,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraDevLgo AS D INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia INNER JOIN Conceptos AS CT ON D.IdConcepto=CT.IdConcepto
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
 INNER JOIN Trn_TraOrdenLiq AS L ON D.TipDoc=L.TipDoc AND D.Liquidacion=L.Liquidacion AND D.IdCiaDoc=L.IdCia
 INNER JOIN Terceros AS NP ON L.IdPoseedor=NP.IdTercero INNER JOIN Terceros AS CDT ON L.IdConductor=CDT.IdTercero
 INNER JOIN Trn_TraOrdenServ AS O ON L.TipOds=O.TipDoc AND L.NumOrden=O.NumOrden AND L.IdCiaOds=O.IdCia
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion 
 INNER JOIN Localidades AS LT ON T.IdLocal=LT.IdLocal
 INNER JOIN Vehiculos AS V ON L.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 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 MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 LEFT JOIN Plazos AS PZ ON L.CdPlazo=PZ.IdPlazo  LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal LEFT JOIN Sys_Um AS UM ON L.UndMed=UM.UndMed
 WHERE D.TipDev=@pmTipDev AND D.Devolucion BETWEEN @pmDevolucionIni AND @pmDevolucionFin AND D.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenLiqRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT L.TipDoc AS IdTipDoc,TipoDoc,L.Liquidacion,L.IdCia AS CdCia,Compania,L.Fecha AS FechaLiq,L.IdConcepto AS CdConcepto,Concepto,TipOds,L.NumOrden,IdCiaOds,O.Fecha AS FecOrden
,L.IdVehiculo AS PlacaVeh,L.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,L.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,L.TipoAfiVehic
,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia,O.IdDestino,LD.Localidad AS NomDestino,O.Modalidad,O.TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo
,O.FecDespacho,O.FecRecibo,O.FecIngCertif,O.FecCertificado,TipoResiduo,O.Cantidad AS CantOrden,O.RemCliente,O.NumRemesa,O.CdCiaRem,L.VrTotal,L.VrCosto,L.VrAnticipo,L.VrGastos,VrIva,VrRetencion,VrReteIca
,VrReteCREE,VrReteIva,VrOtros,VrDescuento,VrConcCargos,VrConcDed,VrNeto,L.Cantidad,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,CodTarRtc
,L.CdTarAvta,L.TarifaAvta,L.VrImpAvTa,L.CdTarBom,L.TarifaBom,L.VrBomberil,L.Referencia,L.Referencia2,L.CxPagar,CdPlazo,Plazo,DiasPago,FechaVence,L.UndMed,Unidad,L.TipCom,L.Comprobante,L.IdCiaCom,L.Anulado,L.NumDev,L.FecDev,L.Observacion AS Observ,L.IdEstado AS CdEstado,Estado
,L.TipEgr,L.Egreso,L.IdCiaEgr,L.NumCheque,ISNULL(VC.VrTotalAbo,0) AS CxpAbonado,ISNULL(VC.VrSaldo,0) AS CxpSaldo
,L.OrigenAdd,L.TimeSys AS FechaCrea,L.FecUpdate AS FechaModif,L.IdCiaCrea AS CdCiaCrea,L.IdUsuario AS CdUsuario,Usuario
--vehiuculo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,LT.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,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion,L.CdCCosto,CCosto,L.CdSubCos,SubCosto
 FROM Trn_TraOrdenLiq AS L INNER JOIN Companias AS CN ON L.IdCia=CN.IdCia INNER JOIN Conceptos AS CT ON L.IdConcepto=CT.IdConcepto
 INNER JOIN adm_Usuarios AS U ON L.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS ED ON L.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS NP ON L.IdPoseedor=NP.IdTercero INNER JOIN Terceros AS CDT ON L.IdConductor=CDT.IdTercero
 INNER JOIN Trn_TraOrdenServ AS O ON L.TipOds=O.TipDoc AND L.NumOrden=O.NumOrden AND L.IdCiaOds=O.IdCia
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion 
 INNER JOIN Sys_TiposDoc AS TD ON L.TipDoc=TD.IdDoc INNER JOIN Localidades AS LT ON T.IdLocal=LT.IdLocal
 INNER JOIN Vehiculos AS V ON L.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 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 MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal LEFT JOIN Sys_Um AS UM ON L.UndMed=UM.UndMed
 LEFT JOIN Plazos AS PZ ON L.CdPlazo=PZ.IdPlazo
 LEFT JOIN CentroCosto AS CC ON L.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON L.CdSubCos=SC.IdSubCos
 --Subconsulta egresos-pagos
 LEFT JOIN (SELECT TipFac,Factura,IdCia,IdProveedor,Documento,SUM(VrFactura) AS VrTotalFac,SUM(VrAbonado) AS VrTotalAbo,SUM(VrFactura-VrAbonado) AS VrSaldo FROM Trn_ComFactura
 WHERE TipFac=@pmTipDoc AND TipDoc=@pmTipDoc AND IdCia LIKE ISNULL(@pmIdCia,'%%')
 GROUP BY TipFac,Factura,IdCia,IdProveedor,Documento) AS VC ON L.TipDoc=VC.TipFac AND L.Liquidacion=VC.Documento AND L.IdCia=VC.IdCia
 -- FIN Subconsulta
 WHERE L.TipDoc=@pmTipDoc AND L.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND L.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraDevLgoRel] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT D.TipDev AS IdTipDev,TipoDoc,Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc,D.Liquidacion AS NumLiquida,D.IdCiaDoc,FecDoc
,TipOds,L.NumOrden,IdCiaOds,O.Fecha AS FecOrden,L.IdVehiculo AS PlacaVeh,L.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,L.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,L.TipoAfiVehic
,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia,O.IdDestino,LD.Localidad AS NomDestino,O.TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo
,O.FecDespacho,O.FecRecibo,O.FecIngCertif,O.FecCertificado,TipoResiduo,O.RemCliente,O.NumRemesa,O.CdCiaRem
,D.VrTotal,D.VrCosto,D.VrAnticipo,D.VrGastos,D.VrIva,D.VrRetencion,D.VrReteIca,D.VrReteCREE,D.VrReteIva,D.VrOtros,D.VrDescuento,L.VrConcCargos,L.VrConcDed,D.VrImpAvTa,D.VrBomberil,D.VrNeto,D.Cantidad
,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,L.TarifaAvta,L.TarifaBom,L.Referencia,CdPlazo,Plazo,DiasPago,FechaVence,L.UndMed,Unidad
,D.CxPagar,D.TipCom,D.Comprobante,D.IdCiaCom,D.ModdDev,D.Observacion AS Observ,D.IdEstado AS CdEstado,D.OrigenAdd,D.TimeSys AS FechaCrea
,D.FecUpdate AS FechaModif,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS CdUsuario,Usuario
--vehiuculo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,LT.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,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraDevLgo AS D INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia INNER JOIN Conceptos AS CT ON D.IdConcepto=CT.IdConcepto
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON D.TipDev=TD.IdDoc
 INNER JOIN Trn_TraOrdenLiq AS L ON D.TipDoc=L.TipDoc AND D.Liquidacion=L.Liquidacion AND D.IdCiaDoc=L.IdCia
 INNER JOIN Terceros AS NP ON L.IdPoseedor=NP.IdTercero INNER JOIN Terceros AS CDT ON L.IdConductor=CDT.IdTercero
 INNER JOIN Trn_TraOrdenServ AS O ON L.TipOds=O.TipDoc AND L.NumOrden=O.NumOrden AND L.IdCiaOds=O.IdCia
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion 
 INNER JOIN Localidades AS LT ON T.IdLocal=LT.IdLocal
 INNER JOIN Vehiculos AS V ON L.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 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 MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 LEFT JOIN Plazos AS PZ ON L.CdPlazo=PZ.IdPlazo  LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal LEFT JOIN Sys_Um AS UM ON L.UndMed=UM.UndMed
 WHERE D.TipDev=@pmTipDev AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenServFmt] @pmTipDoc VARCHAR(3),@pmNumOrdenIni INT,@pmNumOrdenFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT O.TipDoc AS IdTipDoc,TipoDoc,O.NumOrden,O.IdCia AS CdCia,Compania,O.Fecha,FecDespacho,FecRecibo,O.IdCliente,T.RazonSocial AS NomCliente
,O.IdAgencia AS CdAgencia,Agencia,O.IdVehiculo AS PlacaVeh,NumVeh,O.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,nRemolque,O.TipoAfiVehic,O.Modalidad,O.VrTotal,O.VrCosto,O.Cantidad AS Cant_Total,VrAnticipo,O.CdRuta,Ruta,O.IdOrigen AS CdOrigen,LO.Localidad AS NomOrigen,O.IdDestino AS CdDestino,LD.Localidad AS NomDestino
,TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo,NomEstacion,TipoResiduo,RemCliente,NomRecibido,FecIngCertif,FecCertificado,NumRemesa,CdCiaRem,EstCumplido,NumCump,CdCiaCum,EstFactura,TipFact,NumFactura,CdCiaFact
,NumAprob,IdCiaApr,FecAprob,UsuAprob,ObservAprob,O.NumReferencia,kmtInicial,kmtFinal,Cantkms,EstOrden,TipLiq,NumLiquida,CdCiaLiq,O.CdTarAvta,O.CdTarBom
,O.Anulado,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.TimeSys AS FechaCrea,O.FecUpdate AS FechaModif,O.IdCiaCrea AS CdCiaCrea,OrigenAdd,O.IdUsuario AS CdUsuario,Usuario
--Detalles
,Item,D.IdMercancia AS CdMercancia,DescripMcia,D.Descripcion,D.Cantidad,D.UndMed,Unidad,D.VrUnitario,D.TarifaPago
,CantCump,TarifaCump,PagoCump,DocCliente
--Cliente
,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
--Vehiculo
,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,M.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.IdPoseedor AS NitPoseedor,NPO.RazonSocial AS Poseedor
,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora,V.Descripcion AS VehDescripcion
,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
 FROM Trn_TraOrdenServ AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN Terceros AS NP ON O.IdPoseedor=NP.IdTercero
 INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraOrdenDet AS D ON O.TipDoc=D.TipDoc AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
 INNER JOIN Sys_Um AS UM ON D.UndMed=UM.UndMed  INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria
 INNER JOIN Terceros AS NPO ON V.IdPoseedor=NPO.IdTercero  INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 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 O.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN Mercancias AS MC ON D.IdMercancia=MC.IdMercancia
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia  LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 LEFT JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal LEFT JOIN Rutas AS RT ON O.CdRuta=RT.IdRuta
 WHERE O.TipDoc=@pmTipDoc AND O.NumOrden BETWEEN @pmNumOrdenIni AND @pmNumOrdenFin AND O.IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryAfMantnmtoCr] @pmNumeroIni INT,@pmNumeroFin INT
AS
 SELECT Numero,M.IdCia AS CdCia,CM.Compania AS CiaMnmto,Fecha,M.IdActivo AS CdActivo,NomActivo,M.NumContrato AS Num_Contrato,NitEmpresa,NomEmpresa
,FinContrato,Diagnostico,M.Comentarios,M.IdMtto,TM.TipoMtto,M.VrMantnmto,M.IdUsuario AS CdUsuario,Usuario
,G.IdLinea AS CodLinea,Linea,A.IdGrupo AS CodGrupo,Grupo,A.IdCCosto AS CdCentro,CCosto,A.IdSubCos AS CdSubcentro,SubCosto
,A.IdInstala AS CdInstala,Instlacion,A.IdDep AS CdDep,Dependencia,IdRespons,TR.RazonSocial AS Responsable,A.IdCia AS CdCia,CI.Compania AS CiaAfijo
,VrBaseDep,VrMejoras,VrDisminucion,FecActivacion,A.VidaUtil AS Vida_util,FecFinDep,MetodoDep,Ajustable,Contable
,A.IdEstado AS CdEstado,Estado,FecRetiro
,Mantenimiento,AX.NumContrato AS NContratoActual,FecIniCont,FecFinCont,Periodicidad,FecUltMnmto,NitEmpMnmto,ComentMnmto
 FROM Trn_AfMantnmto AS M INNER JOIN Activos AS A ON M.IdActivo=A.IdActivo INNER JOIN Companias AS CM ON M.IdCia=CM.IdCia
 INNER JOIN AfGrupos AS G ON A.IdGrupo=G.IdGrupo INNER JOIN AfLineas AS L ON G.IdLinea=L.IdLinea
 INNER JOIN CentroCosto AS CC ON A.IdCCosto=CC.IdCCosto INNER JOIN Instalaciones AS I ON A.IdInstala=I.IdInstala
 INNER JOIN Dependencias AS DP ON A.IdDep=DP.IdDep INNER JOIN Terceros AS TR ON A.IdRespons=TR.IdTercero
 INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario  INNER JOIN AfEstados AS ED ON A.IdEstado=ED.IdEstado
 INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia LEFT JOIN SubCentros AS SC ON A.IdSubCos=SC.IdSubCos
 LEFT JOIN Activos_Anexo AS AX ON M.IdActivo=AX.IdActivo LEFT JOIN MttoTipos AS TM ON M.IdMtto=TM.IdMtto
 WHERE  Numero BETWEEN @pmNumeroIni  AND @pmNumeroFin
 ORDER BY  Numero
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryAfMantnmtoLta] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmNumeroIni INT,@pmNumeroFin INT,@pmIdGrupo VARCHAR(4)=Null,@pmIdLinea VARCHAR(4)=Null
,@pmIdInstala VARCHAR(4)=Null,@pmIdActivo VARCHAR(30)=Null
AS
 SELECT Numero,M.IdCia AS CdCia,CM.Compania AS CiaMnmto,Fecha,M.IdActivo AS CdActivo,NomActivo,M.NumContrato AS Num_Contrato,NitEmpresa,NomEmpresa
,FinContrato,Diagnostico,M.Comentarios,M.IdMtto,TM.TipoMtto,M.VrMantnmto,M.IdUsuario AS CdUsuario,Usuario
,G.IdLinea AS CodLinea,Linea,A.IdGrupo AS CodGrupo,Grupo,A.IdCCosto AS CdCentro,CCosto,A.IdSubCos AS CdSubcentro,SubCosto
,A.IdInstala AS CdInstala,Instlacion,A.IdDep AS CdDep,Dependencia,IdRespons,TR.RazonSocial AS Responsable,A.IdCia AS CdCia,CI.Compania AS CiaAfijo
,VrBaseDep,VrMejoras,VrDisminucion,FecActivacion,A.VidaUtil AS Vida_util,FecFinDep,MetodoDep,Ajustable,Contable
,A.IdEstado AS CdEstado,Estado,FecRetiro
,Mantenimiento,AX.NumContrato AS NContratoActual,FecIniCont,FecFinCont,Periodicidad,FecUltMnmto,NitEmpMnmto,ComentMnmto
 FROM Trn_AfMantnmto AS M INNER JOIN Activos AS A ON M.IdActivo=A.IdActivo INNER JOIN Companias AS CM ON M.IdCia=CM.IdCia
 INNER JOIN AfGrupos AS G ON A.IdGrupo=G.IdGrupo INNER JOIN AfLineas AS L ON G.IdLinea=L.IdLinea
 INNER JOIN CentroCosto AS CC ON A.IdCCosto=CC.IdCCosto INNER JOIN Instalaciones AS I ON A.IdInstala=I.IdInstala
 INNER JOIN Dependencias AS DP ON A.IdDep=DP.IdDep INNER JOIN Terceros AS TR ON A.IdRespons=TR.IdTercero
 INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario  INNER JOIN AfEstados AS ED ON A.IdEstado=ED.IdEstado
 INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia LEFT JOIN SubCentros AS SC ON A.IdSubCos=SC.IdSubCos
 LEFT JOIN Activos_Anexo AS AX ON M.IdActivo=AX.IdActivo LEFT JOIN MttoTipos AS TM ON M.IdMtto=TM.IdMtto
 WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND M.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND  A.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%')
 AND  A.IdInstala LIKE ISNULL(@pmIdInstala,'%') AND M.IdActivo LIKE ISNULL(@pmIdActivo,'%')
 AND (Numero>=ISNULL(@pmNumeroIni,-1)  AND Numero<=ISNULL(@pmNumeroFin,2147483647))
 ORDER BY  Numero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraDevLgo] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmVrTotal MONEY,@pmVrCosto MONEY,@pmVrAnticipo MONEY,@pmVrGastos MONEY,@pmVrIva MONEY,@pmVrRetencion MONEY,@pmVrReteIca MONEY
,@pmVrReteCREE MONEY,@pmVrReteIva MONEY,@pmVrOtros MONEY,@pmVrDescuento MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmCxPagar BIT,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmModdDev VARCHAR(10),@pmObservacion VARCHAR(1000),@pmIdEstado VARCHAR(4),@pmVrImpAvTa MONEY,@pmVrBomberil MONEY,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraDevLgo (TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,Liquidacion,IdCiaDoc,FecDoc,VrTotal,VrCosto,VrAnticipo,VrGastos,VrIva,VrRetencion,VrReteIca,VrReteCREE,VrReteIva,VrOtros,VrDescuento,VrNeto,Cantidad,CxPagar,TipCom,Comprobante,IdCiaCom,ModdDev,Observacion,IdEstado,VrImpAvTa,VrBomberil,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDev,@pmDevolucion,@pmIdCia,@pmFecha,@pmIdConcepto,@pmTipDoc,@pmLiquidacion,@pmIdCiaDoc,@pmFecDoc,@pmVrTotal,@pmVrCosto,@pmVrAnticipo,@pmVrGastos,@pmVrIva,@pmVrRetencion,@pmVrReteIca,@pmVrReteCREE,@pmVrReteIva,@pmVrOtros,@pmVrDescuento,@pmVrNeto,@pmCantidad,@pmCxPagar,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmModdDev,@pmObservacion,@pmIdEstado,@pmVrImpAvTa,@pmVrBomberil,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraDevLgo] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmVrTotal MONEY,@pmVrCosto MONEY,@pmVrAnticipo MONEY,@pmVrGastos MONEY,@pmVrIva MONEY,@pmVrRetencion MONEY,@pmVrReteIca MONEY
,@pmVrReteCREE MONEY,@pmVrReteIva MONEY,@pmVrOtros MONEY,@pmVrDescuento MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmCxPagar BIT,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmModdDev VARCHAR(10),@pmObservacion VARCHAR(1000),@pmIdEstado VARCHAR(4),@pmVrImpAvTa MONEY,@pmVrBomberil MONEY,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_TraDevLgo SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,TipDoc=@pmTipDoc,Liquidacion=@pmLiquidacion,IdCiaDoc=@pmIdCiaDoc,FecDoc=@pmFecDoc,VrTotal=@pmVrTotal,VrCosto=@pmVrCosto,VrAnticipo=@pmVrAnticipo,VrGastos=@pmVrGastos,VrIva=@pmVrIva,VrRetencion=@pmVrRetencion,VrReteIca=@pmVrReteIca,VrReteCREE=@pmVrReteCREE,VrReteIva=@pmVrReteIva,VrOtros=@pmVrOtros
,VrDescuento=@pmVrDescuento,VrNeto=@pmVrNeto,Cantidad=@pmCantidad,CxPagar=@pmCxPagar,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,ModdDev=@pmModdDev,Observacion=@pmObservacion,VrImpAvTa=@pmVrImpAvTa,VrBomberil=@pmVrBomberil,IdEstado=@pmIdEstado,FecUpdate=@pmFecUpdate 
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraDevLgo] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,Liquidacion,IdCiaDoc,FecDoc,VrTotal,VrCosto,VrAnticipo,VrGastos,VrIva,VrRetencion,VrReteIca,VrReteCREE,VrReteIva,VrOtros,VrDescuento,VrNeto,Cantidad,CxPagar,TipCom,Comprobante,IdCiaCom,ModdDev,Observacion,IdEstado,VrImpAvTa,VrBomberil
,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_TraDevLgo
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraDevLgoLta] @pmTipDev VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT Devolucion,D.IdCia AS CdCia,Compania,D.Fecha AS FechaDev,D.IdConcepto AS CdConcepto,Concepto,D.TipDoc,D.Liquidacion AS NumLiquida,D.IdCiaDoc,FecDoc
,TipOds,L.NumOrden,IdCiaOds,L.IdVehiculo AS PlacaVeh,L.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,L.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,L.TipoAfiVehic
,D.VrTotal,D.VrCosto,D.VrAnticipo,D.VrGastos,D.VrIva,D.VrRetencion,D.VrReteIca,D.VrReteCREE,D.VrReteIva,D.VrOtros,D.VrDescuento,L.VrConcCargos,L.VrConcDed,D.VrImpAvTa,D.VrBomberil,D.VrNeto,D.Cantidad
,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,L.TarifaAvta,L.TarifaBom,L.Referencia,CdPlazo,FechaVence,UndMed
,D.CxPagar,D.TipCom,D.Comprobante,D.IdCiaCom,D.ModdDev,D.Observacion AS Observ,D.IdEstado AS CdEstado,D.OrigenAdd,D.TimeSys AS FechaCrea
,D.FecUpdate AS FechaModif,D.IdCiaCrea AS CdCiaCrea,D.IdUsuario AS CdUsuario,Usuario
 FROM Trn_TraDevLgo AS D INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia INNER JOIN Conceptos AS CT ON D.IdConcepto=CT.IdConcepto
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraOrdenLiq AS L ON D.TipDoc=L.TipDoc AND D.Liquidacion=L.Liquidacion AND D.IdCiaDoc=L.IdCia
 INNER JOIN Terceros AS NP ON L.IdPoseedor=NP.IdTercero INNER JOIN Terceros AS CDT ON L.IdConductor=CDT.IdTercero
 WHERE D.TipDev=@pmTipDev AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenLiqLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT Liquidacion,L.IdCia AS CdCia,Compania,L.Fecha AS FechaLiq,L.IdConcepto AS CdConcepto,Concepto,TipOds,L.NumOrden,IdCiaOds,O.Fecha AS FecOrden
,L.IdVehiculo AS PlacaVeh,L.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,L.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,L.TipoAfiVehic
,O.IdCliente,T.RazonSocial AS NomCliente,O.IdDestino,Localidad,O.TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo,TipoResiduo,O.RemCliente,O.NumRemesa,O.CdCiaRem,L.NumDocSop,L.CiaDocSop
,L.VrTotal,L.VrCosto,L.VrAnticipo,L.VrGastos,VrIva,VrRetencion,VrReteIca,VrReteCREE,VrReteIva,VrOtros,VrDescuento,VrConcCargos,VrConcDed,VrNeto,L.Cantidad
,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,CodTarRtc,L.CdTarAvta,L.TarifaAvta,L.VrImpAvTa,L.CdTarBom,L.TarifaBom,L.VrBomberil
,Referencia,L.Referencia2,CxPagar,CdPlazo,FechaVence,UndMed,L.TipCom,L.Comprobante,L.IdCiaCom,L.Anulado,L.NumDev,L.FecDev,L.Observacion AS Observ
,L.IdEstado AS CdEstado,Estado,TipEgr,Egreso,IdCiaEgr,NumCheque,L.CdCCosto,L.CdSubCos
,L.OrigenAdd,L.TimeSys AS FechaCrea,L.FecUpdate AS FechaModif,L.IdCiaCrea AS CdCiaCrea,L.IdUsuario AS CdUsuario,Usuario
 FROM Trn_TraOrdenLiq AS L INNER JOIN Companias AS CN ON L.IdCia=CN.IdCia INNER JOIN Conceptos AS CT ON L.IdConcepto=CT.IdConcepto
 INNER JOIN adm_Usuarios AS U ON L.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS ED ON L.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS NP ON L.IdPoseedor=NP.IdTercero INNER JOIN Terceros AS CDT ON L.IdConductor=CDT.IdTercero
 INNER JOIN Trn_TraOrdenServ AS O ON L.TipOds=O.TipDoc AND L.NumOrden=O.NumOrden AND L.IdCiaOds=O.IdCia
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion 
 LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 WHERE L.TipDoc=@pmTipDoc AND L.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND L.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenServLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT NumOrden,O.IdCia AS CdCia,Compania,Fecha,FecDespacho,FecRecibo,IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia
,O.IdVehiculo AS PlacaVeh,O.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,nRemolque,O.TipoAfiVehic,O.Modalidad,VrTotal,VrCosto,Cantidad,VrAnticipo,O.CdRuta,Ruta,O.IdOrigen,LO.Localidad AS Origen,O.IdDestino,LD.Localidad AS Destino
,TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo,NomEstacion,TipoResiduo,RemCliente,NomRecibido,FecIngCertif,FecCertificado,O.NumReferencia,O.CdTarAvta,O.CdTarBom,NumRemesa,CdCiaRem,EstCumplido,NumCump,CdCiaCum
,EstFactura,TipFact,NumFactura,CdCiaFact,EstOrden,TipLiq,NumLiquida,CdCiaLiq,NumAprob,IdCiaApr,FecAprob,UsuAprob,ObservAprob,OrigenAdd,O.Anulado,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado
,O.TimeSys AS FechaCrea,O.FecUpdate AS FechaModif,kmtInicial,kmtFinal,Cantkms,O.IdCiaCrea AS CdCiaCrea,O.IdUsuario AS CdUsuario,Usuario
FROM Trn_TraOrdenServ AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero
INNER JOIN Terceros AS NP ON O.IdPoseedor=NP.IdTercero INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero
INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia  LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
LEFT JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal LEFT JOIN Rutas AS RT ON O.CdRuta=RT.IdRuta
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].[paQryTraOrdenLiq] @pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Liquidacion,IdCia,Fecha,IdConcepto,TipOds,NumOrden,IdCiaOds,IdVehiculo,IdPoseedor,IdConductor,TipoAfiVehic,VrTotal,VrCosto,VrAnticipo,VrGastos,VrIva,VrRetencion,VrReteIca,VrReteCREE,VrReteIva,VrOtros,VrDescuento,VrNeto,Cantidad,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc
,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,CodTarRtc,Referencia,CxPagar,CdPlazo,FechaVence,UndMed,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,TipEgr,Egreso,IdCiaEgr,NumCheque,VrConcCargos,VrConcDed,CdCCosto,CdSubCos,NumDocSop,CiaDocSop,Referencia2,CdTarAvta,TarifaAvta,VrImpAvTa,CdTarBom,TarifaBom,VrBomberil
,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_TraOrdenLiq
 WHERE TipDoc=@pmTipDoc AND Liquidacion=@pmLiquidacion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraOrdenLiq] @pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipOds VARCHAR(3),@pmNumOrden INT,@pmIdCiaOds CHAR(2),@pmIdVehiculo VARCHAR(10),@pmIdPoseedor VARCHAR(16),@pmIdConductor VARCHAR(16),@pmTipoAfiVehic VARCHAR(10),@pmVrTotal MONEY,@pmVrCosto MONEY
,@pmVrAnticipo MONEY,@pmVrGastos MONEY,@pmVrIva MONEY,@pmVrRetencion MONEY,@pmVrReteIca MONEY,@pmVrReteCREE MONEY,@pmVrReteIva MONEY,@pmVrOtros MONEY,@pmVrDescuento MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmTarifaPago MONEY,@pmBaseImp MONEY,@pmBaseRet MONEY,@pmBaseIca MONEY,@pmTarifaImp DECIMAL(14,4),@pmTarifaRet DECIMAL(14,4)
,@pmTarifaIca DECIMAL(16,6),@pmTarifaRiv DECIMAL(14,4),@pmTarifaRtc DECIMAL(14,4),@pmCodTarIva VARCHAR(4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarRiv VARCHAR(4),@pmCodTarRtc VARCHAR(4),@pmReferencia VARCHAR(50),@pmCxPagar BIT,@pmCdPlazo VARCHAR(4),@pmFechaVence SMALLDATETIME,@pmUndMed VARCHAR(10)
,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(1000),@pmIdEstado VARCHAR(4),@pmTipEgr VARCHAR(3),@pmEgreso INT,@pmIdCiaEgr CHAR(2),@pmNumCheque VARCHAR(20),@pmVrConcCargos MONEY,@pmVrConcDed MONEY,@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16),@pmNumDocSop INT,@pmCiaDocSop CHAR(2),@pmReferencia2 VARCHAR(50)
,@pmCdTarAvta VARCHAR(4),@pmTarifaAvta DECIMAL(14,4),@pmVrImpAvTa MONEY,@pmCdTarBom VARCHAR(4),@pmTarifaBom DECIMAL(14,4),@pmVrBomberil MONEY,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraOrdenLiq (TipDoc,Liquidacion,IdCia,Fecha,IdConcepto,TipOds,NumOrden,IdCiaOds,IdVehiculo,IdPoseedor,IdConductor,TipoAfiVehic,VrTotal,VrCosto,VrAnticipo,VrGastos,VrIva,VrRetencion,VrReteIca,VrReteCREE,VrReteIva,VrOtros,VrDescuento,VrNeto,Cantidad,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv
,TarifaRtc,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,CodTarRtc,Referencia,CxPagar,CdPlazo,FechaVence,UndMed,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario,TipEgr,Egreso,IdCiaEgr,NumCheque,VrConcCargos,VrConcDed,CdCCosto,CdSubCos,NumDocSop,CiaDocSop,Referencia2,CdTarAvta,TarifaAvta,VrImpAvTa,CdTarBom,TarifaBom,VrBomberil)
 VALUES (@pmTipDoc,@pmLiquidacion,@pmIdCia,@pmFecha,@pmIdConcepto,@pmTipOds,@pmNumOrden,@pmIdCiaOds,@pmIdVehiculo,@pmIdPoseedor,@pmIdConductor,@pmTipoAfiVehic,@pmVrTotal,@pmVrCosto,@pmVrAnticipo,@pmVrGastos,@pmVrIva,@pmVrRetencion,@pmVrReteIca,@pmVrReteCREE,@pmVrReteIva,@pmVrOtros,@pmVrDescuento,@pmVrNeto,@pmCantidad,@pmTarifaPago,@pmBaseImp
,@pmBaseRet,@pmBaseIca,@pmTarifaImp,@pmTarifaRet,@pmTarifaIca,@pmTarifaRiv,@pmTarifaRtc,@pmCodTarIva,@pmCodTarRet,@pmCodTarIca,@pmCodTarRiv,@pmCodTarRtc,@pmReferencia,@pmCxPagar,@pmCdPlazo,@pmFechaVence,@pmUndMed,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado,@pmNumDev,@pmFecDev,@pmObservacion,@pmIdEstado
,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmTipEgr,@pmEgreso,@pmIdCiaEgr,@pmNumCheque,@pmVrConcCargos,@pmVrConcDed,@pmCdCCosto,@pmCdSubCos,@pmNumDocSop,@pmCiaDocSop,@pmReferencia2,@pmCdTarAvta,@pmTarifaAvta,@pmVrImpAvTa,@pmCdTarBom,@pmTarifaBom,@pmVrBomberil)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraOrdenLiq] @pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipOds VARCHAR(3),@pmNumOrden INT,@pmIdCiaOds CHAR(2),@pmIdVehiculo VARCHAR(10),@pmIdPoseedor VARCHAR(16),@pmIdConductor VARCHAR(16),@pmTipoAfiVehic VARCHAR(10),@pmVrTotal MONEY,@pmVrCosto MONEY
,@pmVrAnticipo MONEY,@pmVrGastos MONEY,@pmVrIva MONEY,@pmVrRetencion MONEY,@pmVrReteIca MONEY,@pmVrReteCREE MONEY,@pmVrReteIva MONEY,@pmVrOtros MONEY,@pmVrDescuento MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmTarifaPago MONEY,@pmBaseImp MONEY,@pmBaseRet MONEY,@pmBaseIca MONEY,@pmTarifaImp DECIMAL(14,4),@pmTarifaRet DECIMAL(14,4)
,@pmTarifaIca DECIMAL(16,6),@pmTarifaRiv DECIMAL(14,4),@pmTarifaRtc DECIMAL(14,4),@pmCodTarIva VARCHAR(4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarRiv VARCHAR(4),@pmCodTarRtc VARCHAR(4),@pmReferencia VARCHAR(50),@pmCxPagar BIT,@pmCdPlazo VARCHAR(4),@pmFechaVence SMALLDATETIME,@pmUndMed VARCHAR(10)
,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(1000),@pmIdEstado VARCHAR(4),@pmTipEgr VARCHAR(3),@pmEgreso INT,@pmIdCiaEgr CHAR(2),@pmNumCheque VARCHAR(20),@pmVrConcCargos MONEY,@pmVrConcDed MONEY,@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16),@pmNumDocSop INT,@pmCiaDocSop CHAR(2),@pmReferencia2 VARCHAR(50)
,@pmCdTarAvta VARCHAR(4),@pmTarifaAvta DECIMAL(14,4),@pmVrImpAvTa MONEY,@pmCdTarBom VARCHAR(4),@pmTarifaBom DECIMAL(14,4),@pmVrBomberil MONEY,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_TraOrdenLiq SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,TipOds=@pmTipOds,NumOrden=@pmNumOrden,IdCiaOds=@pmIdCiaOds,IdVehiculo=@pmIdVehiculo,IdPoseedor=@pmIdPoseedor,IdConductor=@pmIdConductor,TipoAfiVehic=@pmTipoAfiVehic,VrTotal=@pmVrTotal,VrCosto=@pmVrCosto,VrAnticipo=@pmVrAnticipo,VrGastos=@pmVrGastos,VrIva=@pmVrIva,VrRetencion=@pmVrRetencion
,VrReteIca=@pmVrReteIca,VrReteCREE=@pmVrReteCREE,VrReteIva=@pmVrReteIva,VrOtros=@pmVrOtros,VrDescuento=@pmVrDescuento,VrNeto=@pmVrNeto,Cantidad=@pmCantidad,TarifaPago=@pmTarifaPago,BaseImp=@pmBaseImp,BaseRet=@pmBaseRet,BaseIca=@pmBaseIca,TarifaImp=@pmTarifaImp,TarifaRet=@pmTarifaRet,TarifaIca=@pmTarifaIca,TarifaRiv=@pmTarifaRiv,TarifaRtc=@pmTarifaRtc
,CodTarIva=@pmCodTarIva,CodTarRet=@pmCodTarRet,CodTarIca=@pmCodTarIca,CodTarRiv=@pmCodTarRiv,CodTarRtc=@pmCodTarRtc,Referencia=@pmReferencia,CxPagar=@pmCxPagar,CdPlazo=@pmCdPlazo,FechaVence=@pmFechaVence,UndMed=@pmUndMed,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev
,Observacion=@pmObservacion,IdEstado=@pmIdEstado,FecUpdate=@pmFecUpdate,TipEgr=@pmTipEgr,Egreso=@pmEgreso,IdCiaEgr=@pmIdCiaEgr,NumCheque=@pmNumCheque,VrConcCargos=@pmVrConcCargos,VrConcDed=@pmVrConcDed,CdCCosto=@pmCdCCosto,CdSubCos=@pmCdSubCos,NumDocSop=@pmNumDocSop,CiaDocSop=@pmCiaDocSop,Referencia2=@pmReferencia2,CdTarAvta=@pmCdTarAvta,TarifaAvta=@pmTarifaAvta,VrImpAvTa=@pmVrImpAvTa,CdTarBom=@pmCdTarBom,TarifaBom=@pmTarifaBom,VrBomberil=@pmVrBomberil
 WHERE TipDoc=@pmTipDoc AND Liquidacion=@pmLiquidacion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraOrdenServ] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmFecRecibo SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdVehiculo VARCHAR(10),@pmIdPoseedor VARCHAR(16),@pmIdConductor VARCHAR(16),@pmnRemolque VARCHAR(10),@pmTipoAfiVehic VARCHAR(10),@pmModalidad VARCHAR(10)
,@pmVrTotal MONEY,@pmVrCosto MONEY,@pmCantidad DECIMAL(14,4),@pmVrAnticipo MONEY,@pmIdDestino VARCHAR(8),@pmTipoOrden VARCHAR(10),@pmIdEstacion VARCHAR(4),@pmNomEstacion VARCHAR(50),@pmTipoResiduo VARCHAR(100),@pmRemCliente VARCHAR(30),@pmNomRecibido VARCHAR(150),@pmFecIngCertif SMALLDATETIME,@pmFecCertificado SMALLDATETIME,@pmNumRemesa INT,@pmCdCiaRem CHAR(2)
,@pmEstCumplido INT,@pmEstFactura INT,@pmTipFact VARCHAR(3),@pmNumFactura INT,@pmCdCiaFact CHAR(2),@pmkmtInicial INT,@pmkmtFinal INT,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(1000),@pmIdEstado VARCHAR(4),@pmEstOrden INT,@pmTipLiq VARCHAR(3),@pmNumLiquida INT,@pmCdCiaLiq CHAR(2),@pmNumAprob INT,@pmIdCiaApr CHAR(2),@pmFecAprob SMALLDATETIME,@pmUsuAprob VARCHAR(11),@pmObservAprob VARCHAR(250)
,@pmCdRuta VARCHAR(4),@pmIdOrigen VARCHAR(8),@pmCantkms INT,@pmNumCump INT,@pmCdCiaCum CHAR(2),@pmCdTarIva VARCHAR(4),@pmCdTarRiv VARCHAR(4),@pmCdTarRet VARCHAR(4),@pmCdTarIca VARCHAR(4),@pmCdTarRtc VARCHAR(4),@pmNumReferencia VARCHAR(50),@pmCdTarAvta VARCHAR(4),@pmCdTarBom VARCHAR(4),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_TraOrdenServ SET Fecha=@pmFecha,FecDespacho=@pmFecDespacho,FecRecibo=@pmFecRecibo,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,IdVehiculo=@pmIdVehiculo,IdPoseedor=@pmIdPoseedor,IdConductor=@pmIdConductor,nRemolque=@pmnRemolque,TipoAfiVehic=@pmTipoAfiVehic,Modalidad=@pmModalidad,VrTotal=@pmVrTotal,VrCosto=@pmVrCosto,Cantidad=@pmCantidad,VrAnticipo=@pmVrAnticipo
,IdDestino=@pmIdDestino,TipoOrden=@pmTipoOrden,IdEstacion=@pmIdEstacion,NomEstacion=@pmNomEstacion,TipoResiduo=@pmTipoResiduo,RemCliente=@pmRemCliente,NomRecibido=@pmNomRecibido,FecIngCertif=@pmFecIngCertif,FecCertificado=@pmFecCertificado,NumRemesa=@pmNumRemesa,CdCiaRem=@pmCdCiaRem,EstCumplido=@pmEstCumplido,EstFactura=@pmEstFactura,TipFact=@pmTipFact,NumFactura=@pmNumFactura,CdCiaFact=@pmCdCiaFact
,kmtInicial=@pmkmtInicial,kmtFinal=@pmkmtFinal,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,FecUpdate=@pmFecUpdate,EstOrden=@pmEstOrden,TipLiq=@pmTipLiq,NumLiquida=@pmNumLiquida,CdCiaLiq=@pmCdCiaLiq,NumAprob=@pmNumAprob,IdCiaApr=@pmIdCiaApr,FecAprob=@pmFecAprob,UsuAprob=@pmUsuAprob,ObservAprob=@pmObservAprob
,CdRuta=@pmCdRuta,IdOrigen=@pmIdOrigen,Cantkms=@pmCantkms,NumCump=@pmNumCump,CdCiaCum=@pmCdCiaCum,CdTarIva=@pmCdTarIva,CdTarRiv=@pmCdTarRiv,CdTarRet=@pmCdTarRet,CdTarIca=@pmCdTarIca,CdTarRtc=@pmCdTarRtc,NumReferencia=@pmNumReferencia,CdTarAvta=@pmCdTarAvta,CdTarBom=@pmCdTarBom
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenServ] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumOrden,IdCia,Fecha,FecDespacho,FecRecibo,IdCliente,IdAgencia,IdVehiculo,IdPoseedor,IdConductor,nRemolque,TipoAfiVehic,Modalidad,VrTotal,VrCosto,Cantidad,VrAnticipo,IdDestino,TipoOrden,IdEstacion,NomEstacion,TipoResiduo,RemCliente,NomRecibido
,FecIngCertif,FecCertificado,NumRemesa,CdCiaRem,EstCumplido,EstFactura,TipFact,NumFactura,CdCiaFact,kmtInicial,kmtFinal,EstOrden,TipLiq,NumLiquida,CdCiaLiq,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,NumAprob,IdCiaApr,FecAprob,UsuAprob,ObservAprob
,CdRuta,IdOrigen,Cantkms,NumCump,CdCiaCum,CdTarIva,CdTarRiv,CdTarRet,CdTarIca,CdTarRtc,NumReferencia,CdTarAvta,CdTarBom,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_TraOrdenServ
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraOrdenServ] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmFecRecibo SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdVehiculo VARCHAR(10),@pmIdPoseedor VARCHAR(16),@pmIdConductor VARCHAR(16),@pmnRemolque VARCHAR(10),@pmTipoAfiVehic VARCHAR(10),@pmModalidad VARCHAR(10)
,@pmVrTotal MONEY,@pmVrCosto MONEY,@pmCantidad DECIMAL(14,4),@pmVrAnticipo MONEY,@pmIdDestino VARCHAR(8),@pmTipoOrden VARCHAR(10),@pmIdEstacion VARCHAR(4),@pmNomEstacion VARCHAR(50),@pmTipoResiduo VARCHAR(100),@pmRemCliente VARCHAR(30),@pmNomRecibido VARCHAR(150),@pmFecIngCertif SMALLDATETIME,@pmFecCertificado SMALLDATETIME,@pmNumRemesa INT,@pmCdCiaRem CHAR(2)
,@pmEstCumplido INT,@pmEstFactura INT,@pmTipFact VARCHAR(3),@pmNumFactura INT,@pmCdCiaFact CHAR(2),@pmkmtInicial INT,@pmkmtFinal INT,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(1000),@pmIdEstado VARCHAR(4),@pmEstOrden INT,@pmTipLiq VARCHAR(3),@pmNumLiquida INT,@pmCdCiaLiq CHAR(2),@pmNumAprob INT,@pmIdCiaApr CHAR(2),@pmFecAprob SMALLDATETIME,@pmUsuAprob VARCHAR(11),@pmObservAprob VARCHAR(250)
,@pmCdRuta VARCHAR(4),@pmIdOrigen VARCHAR(8),@pmCantkms INT,@pmNumCump INT,@pmCdCiaCum CHAR(2),@pmCdTarIva VARCHAR(4),@pmCdTarRiv VARCHAR(4),@pmCdTarRet VARCHAR(4),@pmCdTarIca VARCHAR(4),@pmCdTarRtc VARCHAR(4),@pmNumReferencia VARCHAR(50),@pmCdTarAvta VARCHAR(4),@pmCdTarBom VARCHAR(4),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraOrdenServ (TipDoc,NumOrden,IdCia,Fecha,FecDespacho,FecRecibo,IdCliente,IdAgencia,IdVehiculo,IdPoseedor,IdConductor,nRemolque,TipoAfiVehic,Modalidad,VrTotal,VrCosto,Cantidad,VrAnticipo,IdDestino,TipoOrden,IdEstacion,NomEstacion,TipoResiduo,RemCliente,NomRecibido,FecIngCertif,FecCertificado,NumRemesa,CdCiaRem,EstCumplido,EstFactura,TipFact,NumFactura,CdCiaFact
,kmtInicial,kmtFinal,EstOrden,TipLiq,NumLiquida,CdCiaLiq,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,NumAprob,IdCiaApr,FecAprob,UsuAprob,ObservAprob,CdRuta,IdOrigen,Cantkms,NumCump,CdCiaCum,CdTarIva,CdTarRiv,CdTarRet,CdTarIca,CdTarRtc,NumReferencia,CdTarAvta,CdTarBom)
 VALUES (@pmTipDoc,@pmNumOrden,@pmIdCia,@pmFecha,@pmFecDespacho,@pmFecRecibo,@pmIdCliente,@pmIdAgencia,@pmIdVehiculo,@pmIdPoseedor,@pmIdConductor,@pmnRemolque,@pmTipoAfiVehic,@pmModalidad,@pmVrTotal,@pmVrCosto,@pmCantidad,@pmVrAnticipo,@pmIdDestino,@pmTipoOrden,@pmIdEstacion,@pmNomEstacion,@pmTipoResiduo,@pmRemCliente,@pmNomRecibido,@pmFecIngCertif,@pmFecCertificado,@pmNumRemesa
,@pmCdCiaRem,@pmEstCumplido,@pmEstFactura,@pmTipFact,@pmNumFactura,@pmCdCiaFact,@pmkmtInicial,@pmkmtFinal,@pmEstOrden,@pmTipLiq,@pmNumLiquida,@pmCdCiaLiq,@pmOrigenAdd,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmNumAprob,@pmIdCiaApr,@pmFecAprob,@pmUsuAprob,@pmObservAprob,@pmCdRuta,@pmIdOrigen,@pmCantkms,@pmNumCump,@pmCdCiaCum,@pmCdTarIva,@pmCdTarRiv,@pmCdTarRet,@pmCdTarIca,@pmCdTarRtc,@pmNumReferencia,@pmCdTarAvta,@pmCdTarBom)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpAfMantnmto] @pmNumero INT,@pmIdCia CHAR(2),@pmIdActivo VARCHAR(30),@pmFecha SMALLDATETIME,@pmNumContrato VARCHAR(20)
,@pmNitEmpresa VARCHAR(16),@pmNomEmpresa VARCHAR(150),@pmFinContrato BIT,@pmDiagnostico VARCHAR(2000),@pmComentarios VARCHAR(4000),@pmIdMtto VARCHAR(4),@pmVrMantnmto MONEY
 AS 
 UPDATE Trn_AfMantnmto SET IdCia=@pmIdCia,IdActivo=@pmIdActivo,Fecha=@pmFecha,NumContrato=@pmNumContrato,NitEmpresa=@pmNitEmpresa,NomEmpresa=@pmNomEmpresa
,FinContrato=@pmFinContrato,Diagnostico=@pmDiagnostico,Comentarios=@pmComentarios,IdMtto=@pmIdMtto,VrMantnmto=@pmVrMantnmto
 WHERE Numero=@pmNumero
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryAfMantnmto] @pmNumero INT
 AS 
 SELECT Numero,IdCia,IdActivo,Fecha,NumContrato,NitEmpresa,NomEmpresa,FinContrato,IdUsuario,Diagnostico,Comentarios,IdMtto,VrMantnmto
 FROM Trn_AfMantnmto
 WHERE Numero=@pmNumero
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsAfMantnmto] @pmIdCia CHAR(2),@pmIdActivo VARCHAR(30),@pmFecha SMALLDATETIME,@pmNumContrato VARCHAR(20)
,@pmNitEmpresa VARCHAR(16),@pmNomEmpresa VARCHAR(150),@pmFinContrato BIT,@pmIdUsuario VARCHAR(11),@pmDiagnostico VARCHAR(2000),@pmComentarios VARCHAR(4000)
,@pmIdMtto VARCHAR(4),@pmVrMantnmto MONEY 
 AS 
 INSERT INTO Trn_AfMantnmto (IdCia,IdActivo,Fecha,NumContrato,NitEmpresa,NomEmpresa,FinContrato,IdUsuario,Diagnostico,Comentarios,IdMtto,VrMantnmto)
 VALUES (@pmIdCia,@pmIdActivo,@pmFecha,@pmNumContrato,@pmNitEmpresa,@pmNomEmpresa,@pmFinContrato,@pmIdUsuario,@pmDiagnostico,@pmComentarios,@pmIdMtto,@pmVrMantnmto)
GO
--nomina Ago 23

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomConvenciones]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomConvenciones]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomNovConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomNovConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomConvenciones]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomConvenciones]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomConvencionesLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomConvencionesLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomConvZonasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomConvZonasLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomNovConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomNovConv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomNovConvLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomNovConvLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomConvenciones]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomConvenciones]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomNovConv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomNovConv]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomNovConv] @pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16),@pmNContrato INT,@pmFecInicial SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmIdConv INT,@pmIdGrupo VARCHAR(4),@pmCodEscfon VARCHAR(4),@pmCodBase VARCHAR(4),@pmCodZona VARCHAR(4),@pmTipoNom VARCHAR(10),@pmCantidad DECIMAL(14,4),@pmVrDevengado MONEY,@pmVrDeducido MONEY,@pmVrSalario MONEY,@pmOrigenReg INT,@pmAnulado BIT
,@pmVrAuxTransp MONEY,@pmVrSalBasDia MONEY,@pmVrAuxTraDia MONEY,@pmVrPriConDia MONEY,@pmIdNom VARCHAR(4),@pmNumRegLog INT,@pmFechaCrea SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_NomNovConv (IdPeriodo,IdEmpleado,NContrato,FecInicial,FecFinal,IdConv,IdGrupo,CodEscfon,CodBase,CodZona,TipoNom,Cantidad,VrDevengado,VrDeducido,VrSalario,OrigenReg,Anulado,FechaCrea,IdUsuario,VrAuxTransp,VrSalBasDia,VrAuxTraDia,VrPriConDia,IdNom,NumRegLog)
 VALUES (@pmIdPeriodo,@pmIdEmpleado,@pmNContrato,@pmFecInicial,@pmFecFinal,@pmIdConv,@pmIdGrupo,@pmCodEscfon,@pmCodBase,@pmCodZona,@pmTipoNom,@pmCantidad,@pmVrDevengado,@pmVrDeducido,@pmVrSalario,@pmOrigenReg,@pmAnulado,@pmFechaCrea,@pmIdUsuario,@pmVrAuxTransp,@pmVrSalBasDia,@pmVrAuxTraDia,@pmVrPriConDia,@pmIdNom,@pmNumRegLog)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomNovConv] @pmId BIGINT,@pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16),@pmNContrato INT,@pmFecInicial SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmIdConv INT,@pmIdGrupo VARCHAR(4),@pmCodEscfon VARCHAR(4),@pmCodBase VARCHAR(4),@pmCodZona VARCHAR(4),@pmTipoNom VARCHAR(10),@pmCantidad DECIMAL(14,4),@pmVrDevengado MONEY,@pmVrDeducido MONEY,@pmVrSalario MONEY,@pmOrigenReg INT,@pmAnulado BIT
,@pmVrAuxTransp MONEY,@pmVrSalBasDia MONEY,@pmVrAuxTraDia MONEY,@pmVrPriConDia MONEY,@pmIdNom VARCHAR(4),@pmNumRegLog INT
 AS 
 UPDATE Trn_NomNovConv SET IdPeriodo=@pmIdPeriodo,IdEmpleado=@pmIdEmpleado,NContrato=@pmNContrato,FecInicial=@pmFecInicial,FecFinal=@pmFecFinal,IdConv=@pmIdConv,IdGrupo=@pmIdGrupo,CodEscfon=@pmCodEscfon,CodBase=@pmCodBase,CodZona=@pmCodZona,TipoNom=@pmTipoNom,Cantidad=@pmCantidad,VrDevengado=@pmVrDevengado,VrDeducido=@pmVrDeducido,VrSalario=@pmVrSalario,OrigenReg=@pmOrigenReg,Anulado=@pmAnulado
 ,VrAuxTransp=@pmVrAuxTransp,VrSalBasDia=@pmVrSalBasDia,VrAuxTraDia=@pmVrAuxTraDia,VrPriConDia=@pmVrPriConDia,IdNom=@pmIdNom,NumRegLog=@pmNumRegLog
 WHERE [Id]=@pmId
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomNovConv] @pmId BIGINT
 AS 
 SELECT [Id],IdPeriodo,IdEmpleado,NContrato,FecInicial,FecFinal,IdConv,IdGrupo,CodEscfon,CodBase,CodZona,TipoNom,IdNom,Cantidad,VrDevengado,VrDeducido,VrSalario
 ,VrAuxTransp,VrSalBasDia,VrAuxTraDia,VrPriConDia,NumRegLog,OrigenReg,Anulado,FechaCrea,IdUsuario
 FROM Trn_NomNovConv
 WHERE [Id]=@pmId
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomNovConvLta] @pmFecInicio SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmNumNovIni INT=Null,@pmNumNovFin INT=Null
 AS 
 SELECT N.Id,N.IdPeriodo,N.IdEmpleado,E.Apellidos,E.Nombres,N.NContrato,N.FecInicial,N.FecFinal,N.IdConv,CV.Proyecto,N.IdGrupo,G.GrupoConv,N.CodEscfon,EC.Escalafon
,EC.IdCargo,CG.Cargo,N.CodBase,B.NombreBase,N.CodZona,Z.ZonaConv,N.IdNom,N.TipoNom,N.Cantidad AS CantTotal,N.VrDevengado,N.VrDeducido,N.VrSalario
,N.VrAuxTransp,N.VrSalBasDia,N.VrAuxTraDia,N.VrPriConDia
--items del detalle 
,D.Item,D.IdConcepto,CN.Concepto,D.Descripcion,D.Cantidad,D.VrUnitario,D.VrTotal,D.VrBaseLiq,D.Tarifa,D.Unidad,D.ClaseCon,D.NitTercero,T.RazonSocial
,D.Referencia,D.HoraInicial,D.HoraFinal,D.TipoReg,D.NumRegVf,D.CantDias,D.CantNov,CN.BaseTrfa
,N.OrigenReg,N.NumRegLog,N.Anulado,N.FechaCrea,N.IdUsuario,Usuario
 FROM Trn_NomNovConv AS N INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado 
 INNER JOIN NomConvenciones AS CV ON N.IdConv=CV.Id INNER JOIN NomGruposConv AS G ON N.IdGrupo=G.IdGrupo
 INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario 
 INNER JOIN Trn_NomNovConc AS D ON N.Id=D.IdNovedad INNER JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto
 LEFT JOIN Terceros AS T ON D.NitTercero=T.IdTercero
 LEFT JOIN NomEscalafones AS EC ON N.CodEscfon=EC.IdEscfon LEFT JOIN Cargos AS CG ON EC.IdCargo=CG.IdCargo
 LEFT JOIN NomZonasConv AS Z ON N.CodZona=Z.IdZona LEFT JOIN NomBasesConv AS B ON N.CodBase=B.IdBase
 WHERE N.Id BETWEEN ISNULL(@pmNumNovIni,0) AND ISNULL(@pmNumNovFin,2147483647)
 AND ((N.FecInicial BETWEEN @pmFecInicio AND @pmFecFinal) OR (N.FecFinal BETWEEN @pmFecInicio AND @pmFecFinal)
 OR (N.FecInicial>=@pmFecInicio AND N.FecFinal<=@pmFecFinal))
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomConvencionesLta]
 AS 
 SELECT C.Id,C.Proyecto,C.Fecha,C.FecInicio,C.FecFinal,C.IdLocal AS CodCiudad,L.Localidad AS NomCiudad,L.IdDep,D.Departamento
,C.NitTercero,RazonSocial,C.Observacion,C.FecProrroga,C.FechaCrea,C.Inactivo
 FROM NomConvenciones AS C INNER JOIN Localidades AS L ON C.IdLocal=L.IdLocal INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 LEFT JOIN Terceros AS T ON C.NitTercero=T.IdTercero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomConvenciones] @pmId INT
 AS 
 SELECT Id,Fecha,Proyecto,FecInicio,FecFinal,IdLocal,NitTercero,Observacion,FecProrroga,FechaCrea,Inactivo
 FROM NomConvenciones
 WHERE [Id]=@pmId
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomConvenciones] @pmId INT,@pmFecha SMALLDATETIME,@pmProyecto VARCHAR(150),@pmFecInicio SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmIdLocal VARCHAR(8),@pmNitTercero VARCHAR(16),@pmObservacion VARCHAR(500),@pmFecProrroga SMALLDATETIME,@pmFechaCrea SMALLDATETIME,@pmInactivo BIT
 AS 
 UPDATE NomConvenciones SET Fecha=@pmFecha,Proyecto=@pmProyecto,FecInicio=@pmFecInicio,FecFinal=@pmFecFinal,IdLocal=@pmIdLocal,NitTercero=@pmNitTercero,Observacion=@pmObservacion,FecProrroga=@pmFecProrroga,Inactivo=@pmInactivo
 WHERE [Id]=@pmId
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomConvenciones] @pmFecha SMALLDATETIME,@pmProyecto VARCHAR(150),@pmFecInicio SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmIdLocal VARCHAR(8),@pmNitTercero VARCHAR(16),@pmObservacion VARCHAR(500),@pmFecProrroga SMALLDATETIME
,@pmFechaCrea SMALLDATETIME,@pmInactivo BIT
 AS 
 INSERT INTO NomConvenciones (Fecha,Proyecto,FecInicio,FecFinal,IdLocal,NitTercero,Observacion,FechaCrea,Inactivo,FecProrroga)
 VALUES (@pmFecha,@pmProyecto,@pmFecInicio,@pmFecFinal,@pmIdLocal,@pmNitTercero,@pmObservacion,@pmFechaCrea,@pmInactivo,@pmFecProrroga)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomConvZonasLta] @pmFecInicio SMALLDATETIME,@pmFecFinal SMALLDATETIME
 AS 
SELECT Z.Id,Z.IdPeriodo,Z.IdEmpleado,E.Apellidos,E.Nombres,Z.NContrato,Z.FechaInicio,Z.FechaFinal,Z.IdConv,CV.Proyecto,Z.CodGrupo,G.GrupoConv
,Z.IdZona,ZN.ZonaConv,Z.FechaCrea,Z.IdUsuario,Usuario
FROM Trn_NomConvZonas AS Z INNER JOIN Empleados AS E ON Z.IdEmpleado=E.IdEmpleado 
INNER JOIN adm_Usuarios AS U ON Z.IdUsuario=U.IdUsuario INNER JOIN NomConvenciones AS CV ON Z.IdConv=CV.Id
INNER JOIN NomZonasConv AS ZN ON Z.IdZona=ZN.IdZona
LEFT JOIN NomGruposConv AS G ON Z.CodGrupo=G.IdGrupo
WHERE ((Z.FechaInicio BETWEEN @pmFecInicio AND @pmFecFinal) OR (Z.FechaFinal BETWEEN @pmFecInicio AND @pmFecFinal)
 OR (Z.FechaInicio>=@pmFecInicio AND Z.FechaFinal<=@pmFecFinal))
GO