ALTER TABLE Trn_TraOrdenAnt ADD TipoAncpo VARCHAR(10) DEFAULT('EGRESO')
,NumPresAnt INT DEFAULT(0) NOT NULL
GO
ALTER TABLE Trn_ProdTarf ADD CodBod VARCHAR(4)
GO
UPDATE Trn_TraOrdenAnt SET TipoAncpo='EGRESO' WHERE TipoAncpo IS NULL
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsProdTarf]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsProdTarf]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraOrdenAnt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraOrdenAnt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryProdTarf]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryProdTarf]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryProdTarfFec]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryProdTarfFec]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryProdTarfLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryProdTarfLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenAnt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenAnt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenAntFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenAntFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenAntLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenAntLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenAntRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenAntRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpProdTarf]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpProdTarf]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraOrdenAnt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraOrdenAnt]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenAntFmt] @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 FecAnt,A.IdConcepto AS CdConcepto,Concepto,TipOds,A.NumOrden,IdCiaOds,O.Fecha AS FecOrden
,O.IdVehiculo AS PlacaVeh,nRemolque,O.TipoAfiVehic,O.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,O.IdCliente AS NitCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,AG.Agencia,A.VrAnticipo,A.VrAbonado,TipoPago,A.NumCheque,FecCheque
,A.IdCta AS CdCta,NumeroCta,CTA.IdBanco,Banco,A.IdCuenta AS CdCuenta,PU.NomCuenta,CedBenef,Beneficiario,FechaVence,A.TipoAncpo,A.NumPresAnt,A.TipCom,A.Comprobante,A.IdCiaCom,A.Anulado AS Anuldo,A.NumDev
,A.FecDev,A.Observacion AS Observ,A.IdEstado AS CdEstado,Estado,A.OrigenAdd,A.TimeSys AS FechaCrea,A.FecUpdate AS FechaModif,A.IdCiaCrea AS CdCiaCrea,A.IdUsuario AS CdUsuario,Usuario,Leyenda
--datos de la orden
,O.Modalidad,O.TipoOrden,O.VrTotal,O.VrCosto,O.Cantidad,O.IdEstacion AS CdCampo,EstServicio AS NomCampo,NomEstacion,TipoResiduo,RemCliente,NomRecibido,IdDestino,LD.Localidad AS NomDestino
,FecDespacho,FecRecibo,FecIngCertif,FecCertificado,NumRemesa,CdCiaRem,EstFactura,TipFact,NumFactura,CdCiaFact,EstOrden,TipLiq,NumLiquida,CdCiaLiq,O.Observacion AS OdsObserv
--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
--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
--detalles
,Item,DC.IdCuenta AS Cd_Cuenta,PC.NomCuenta AS Nom_Cuenta,Detalle,VrDebito,VrCredito,DC.IdCCosto AS DetCodCentCost,DCC.CCosto AS DetCentCost
,DC.IdSubCos AS DetCodSubCentro,DS.SubCosto AS DetSubCentro,VrBase,TarifaBase,Referncia
 FROM Trn_TraOrdenAnt AS A INNER JOIN Trn_TraOrdenServ AS O ON A.TipOds=O.TipDoc AND A.NumOrden=O.NumOrden AND A.IdCiaOds=O.IdCia
 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 EstadoDoc AS ED ON A.IdEstado=ED.IdEstado
 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 Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Sys_TiposDoc AS TD ON A.TipDoc=TD.IdDoc
 INNER JOIN Vehiculos AS V ON O.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 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 Agencias AS AG ON O.IdAgencia=AG.IdAgencia  LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 LEFT JOIN CtasCorrientes AS CTA ON A.IdCta=CTA.IdCta LEFT JOIN Bancos AS B ON CTA.IdBanco=B.IdBanco
 LEFT JOIN Puc AS PU ON A.IdCuenta=PU.IdCuenta
 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 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
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenAntRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=NULL
 AS 
 SELECT A.TipDoc AS TipoAnt,TipoDoc,Anticipo,A.IdCia AS CdCia,Compania,A.Fecha AS FecAnt,A.IdConcepto AS CdConcepto,Concepto,TipOds,A.NumOrden,IdCiaOds,O.Fecha AS FecOrden
,O.IdVehiculo AS PlacaVeh,nRemolque,O.TipoAfiVehic,O.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,O.IdCliente AS NitCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,AG.Agencia,A.VrAnticipo,A.VrAbonado,A.VrAnticipo-A.VrAbonado AS VrSaldo,TipoPago,A.NumCheque,FecCheque
,A.IdCta AS CdCta,NumeroCta,CTA.IdBanco,Banco,A.IdCuenta AS CdCuenta,PU.NomCuenta,CedBenef,Beneficiario,FechaVence,TipoAncpo,NumPresAnt,A.TipCom,A.Comprobante,A.IdCiaCom,A.Anulado AS Anuldo,A.NumDev
,A.FecDev,A.Observacion AS Observ,A.IdEstado AS CdEstado,Estado,A.OrigenAdd,A.TimeSys AS FechaCrea,A.FecUpdate AS FechaModif,A.IdCiaCrea AS CdCiaCrea,A.IdUsuario AS CdUsuario,Usuario
--datos de la orden
,O.Modalidad,O.TipoOrden,O.VrTotal,O.VrCosto,O.Cantidad,O.IdEstacion AS CdCampo,EstServicio AS NomCampo,NomEstacion,TipoResiduo,RemCliente,NomRecibido,IdDestino,LD.Localidad AS NomDestino
,FecDespacho,FecRecibo,FecIngCertif,FecCertificado,NumRemesa,CdCiaRem,EstCumplido,EstFactura,TipFact,NumFactura,CdCiaFact,EstOrden,TipLiq,NumLiquida,CdCiaLiq,O.Observacion AS OdsObserv
--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
--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
 FROM Trn_TraOrdenAnt AS A INNER JOIN Trn_TraOrdenServ AS O ON A.TipOds=O.TipDoc AND A.NumOrden=O.NumOrden AND A.IdCiaOds=O.IdCia
 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 EstadoDoc AS ED ON A.IdEstado=ED.IdEstado
 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 Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Sys_TiposDoc AS TD ON A.TipDoc=TD.IdDoc
 INNER JOIN Vehiculos AS V ON O.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 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 Agencias AS AG ON O.IdAgencia=AG.IdAgencia  LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 LEFT JOIN CtasCorrientes AS CTA ON A.IdCta=CTA.IdCta LEFT JOIN Bancos AS B ON CTA.IdBanco=B.IdBanco
 LEFT JOIN Puc AS PU ON A.IdCuenta=PU.IdCuenta
 WHERE A.TipDoc=@pmTipDoc AND A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND A.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryProdTarfLta] @pmTipo VARCHAR(3)=Null,@pmCdSubgrupo VARCHAR(8)=Null,@pmCdProducto VARCHAR(16)=Null
,@pmCdClie VARCHAR(16)=Null,@pmFecIni SMALLDATETIME=Null,@pmFecFin SMALLDATETIME=Null
 AS 
 SELECT Tipo,Numero,FecInicial,FecFinal,Tarifa,SimbTfa,CdTarifa,CdSubgrupo,Subgrupo,CdProducto,DescripProd,CdClie,T.RazonSocial AS NombreClie,CdAgencia,Agencia
,TR.LtaPre,CdCia,Compania,CdLinea,Linea,CdGrupo,Grupo,CdMarca,Marca,CdVend,V.RazonSocial AS NombreVend,CdCiudad,Localidad,CdZona,Zona,CdSzona,Subzona
,CdGruCli,GrupoClie,OtrosCrit,CodBod,Bodega,TimeSys,FecUpdate,TR.IdUsuario AS IdUsuari,Usuario
 FROM Trn_ProdTarf AS TR INNER JOIN adm_Usuarios AS U ON TR.IdUsuario=U.IdUsuario
 LEFT JOIN SubGrupos AS S ON TR.CdSubgrupo=S.IdSubgrupo
 LEFT JOIN ProdMcias AS P ON TR.CdProducto=P.IdProducto
 LEFT JOIN Terceros AS T ON TR.CdClie=T.IdTercero
 LEFT JOIN Terceros AS V ON TR.CdVend=V.IdTercero
 LEFT JOIN Companias AS C ON TR.CdCia=C.IdCia
 LEFT JOIN Lineas AS L ON TR.CdLinea=L.IdLinea
 LEFT JOIN Grupos AS G ON TR.CdGrupo=G.IdGrupo
 LEFT JOIN Marcas AS M ON TR.CdMarca=M.IdMarca
 LEFT JOIN  Agencias AS A ON TR.CdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LC ON TR.CdCiudad=LC.IdLocal
 LEFT JOIN Zonas AS Z ON TR.CdZona=Z.IdZona
 LEFT JOIN Subzonas AS SZ ON TR.CdSzona=SZ.IdSzona
 LEFT JOIN GruposCli AS GC ON TR.CdGruCli=GC.IdGrupo
 LEFT JOIN Bodegas AS B ON TR.CodBod=B.IdBodega
 WHERE Anulado=0 AND Tipo LIKE ISNULL(@pmTipo,'%') AND CdSubgrupo LIKE ISNULL(@pmCdSubgrupo,'%')
 AND CdProducto LIKE ISNULL(@pmCdProducto,'%') AND CdClie LIKE ISNULL(@pmCdClie,'%')
 AND (FecInicial>=ISNULL(@pmFecIni,CAST('19100101' AS SMALLDATETIME)) AND FecInicial<=ISNULL(@pmFecFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY Tipo,FecInicial,Numero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraOrdenAnt] @pmTipDoc VARCHAR(3),@pmAnticipo INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipOds VARCHAR(3),@pmNumOrden INT,@pmIdCiaOds CHAR(2),@pmVrAnticipo MONEY,@pmVrAbonado MONEY,@pmTipoPago VARCHAR(10),@pmNumCheque VARCHAR(20),@pmFecCheque SMALLDATETIME,@pmIdCta VARCHAR(4),@pmIdCuenta VARCHAR(16),@pmBeneficiario VARCHAR(150),@pmCedBenef VARCHAR(16),@pmFechaVence SMALLDATETIME
,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmTipoAncpo VARCHAR(10),@pmNumPresAnt INT,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraOrdenAnt (TipDoc,Anticipo,IdCia,Fecha,IdConcepto,TipOds,NumOrden,IdCiaOds,VrAnticipo,VrAbonado,TipoPago,NumCheque,FecCheque,IdCta,IdCuenta,Beneficiario,CedBenef,FechaVence,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario,TipoAncpo,NumPresAnt)
 VALUES (@pmTipDoc,@pmAnticipo,@pmIdCia,@pmFecha,@pmIdConcepto,@pmTipOds,@pmNumOrden,@pmIdCiaOds,@pmVrAnticipo,@pmVrAbonado,@pmTipoPago,@pmNumCheque,@pmFecCheque,@pmIdCta,@pmIdCuenta,@pmBeneficiario,@pmCedBenef,@pmFechaVence,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado,@pmNumDev,@pmFecDev,@pmObservacion,@pmIdEstado,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmTipoAncpo,@pmNumPresAnt)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraOrdenAnt] @pmTipDoc VARCHAR(3),@pmAnticipo INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipOds VARCHAR(3),@pmNumOrden INT,@pmIdCiaOds CHAR(2),@pmVrAnticipo MONEY,@pmVrAbonado MONEY,@pmTipoPago VARCHAR(10),@pmNumCheque VARCHAR(20),@pmFecCheque SMALLDATETIME,@pmIdCta VARCHAR(4),@pmIdCuenta VARCHAR(16),@pmBeneficiario VARCHAR(150),@pmCedBenef VARCHAR(16),@pmFechaVence SMALLDATETIME
,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmTipoAncpo VARCHAR(10),@pmNumPresAnt INT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_TraOrdenAnt SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,TipOds=@pmTipOds,NumOrden=@pmNumOrden,IdCiaOds=@pmIdCiaOds,VrAnticipo=@pmVrAnticipo,VrAbonado=@pmVrAbonado,TipoPago=@pmTipoPago,NumCheque=@pmNumCheque,FecCheque=@pmFecCheque,IdCta=@pmIdCta,IdCuenta=@pmIdCuenta,Beneficiario=@pmBeneficiario,CedBenef=@pmCedBenef,FechaVence=@pmFechaVence,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom
,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,FecUpdate=@pmFecUpdate,TipoAncpo=@pmTipoAncpo,NumPresAnt=@pmNumPresAnt
 WHERE TipDoc=@pmTipDoc AND Anticipo=@pmAnticipo AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenAnt] @pmTipDoc VARCHAR(3),@pmAnticipo INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Anticipo,IdCia,Fecha,IdConcepto,TipOds,NumOrden,IdCiaOds,VrAnticipo,VrAbonado,TipoPago,NumCheque,FecCheque,IdCta,IdCuenta,Beneficiario,CedBenef,FechaVence,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion
,IdEstado,TipoAncpo,NumPresAnt,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_TraOrdenAnt
 WHERE TipDoc=@pmTipDoc AND Anticipo=@pmAnticipo AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenAntLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT Anticipo,A.IdCia AS CdCia,Compania,A.Fecha AS FecAnt,A.IdConcepto AS CdConcepto,Concepto,TipOds,A.NumOrden,IdCiaOds,O.Fecha AS FecOrden,O.IdVehiculo AS PlacaVeh,nRemolque
,O.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,O.IdCliente AS NitCliente,T.RazonSocial AS NomCliente
,O.IdAgencia AS CdAgencia,Agencia,A.VrAnticipo,VrAbonado,VrTotal,Cantidad,TipoPago,NumCheque,FecCheque,A.IdCta AS CdCta,NumeroCta,CT.IdBanco,Banco
,A.IdCuenta AS CdCuenta,CedBenef,Beneficiario,FechaVence,TipoAncpo,NumPresAnt,TipCom,Comprobante,IdCiaCom,A.Anulado AS Anuldo,A.NumDev,A.FecDev,A.Observacion AS Observ
,A.IdEstado AS CdEstado,Estado,A.OrigenAdd,A.TimeSys AS FechaCrea,A.FecUpdate AS FechaModif,A.IdCiaCrea AS CdCiaCrea,A.IdUsuario AS CdUsuario,Usuario
 FROM Trn_TraOrdenAnt AS A INNER JOIN Trn_TraOrdenServ AS O ON A.TipOds=O.TipDoc AND A.NumOrden=O.NumOrden AND A.IdCiaOds=O.IdCia
 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 EstadoDoc AS ED ON A.IdEstado=ED.IdEstado
 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
 LEFT JOIN CtasCorrientes AS CT ON A.IdCta=CT.IdCta LEFT JOIN Bancos AS B ON CT.IdBanco=B.IdBanco
 LEFT JOIN Agencias AS AG ON O.IdAgencia=AG.IdAgencia 
 WHERE A.TipDoc=@pmTipDoc AND A.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND A.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsProdTarf] @pmTipo VARCHAR(3),@pmNumero INT,@pmFecInicial SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmCdCia CHAR(2),@pmCdLinea VARCHAR(8),@pmCdGrupo VARCHAR(8),@pmCdSubgrupo VARCHAR(8),@pmCdMarca VARCHAR(4),@pmCdProducto VARCHAR(16)
,@pmTarifa DECIMAL(14,4),@pmSimbTfa CHAR(1),@pmLtaPre CHAR(1),@pmCdTarifa VARCHAR(4),@pmCdClie VARCHAR(16),@pmCdAgencia VARCHAR(16),@pmCdVend VARCHAR(16),@pmCdCiudad VARCHAR(8),@pmCdZona VARCHAR(4),@pmCdSzona VARCHAR(4),@pmCdGruCli VARCHAR(4),@pmOtrosCrit VARCHAR(250)
,@pmAnulado BIT,@pmCodBod VARCHAR(4),@pmTimeSys SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_ProdTarf (Tipo,Numero,FecInicial,FecFinal,CdCia,CdLinea,CdGrupo,CdSubgrupo,CdMarca,CdProducto,Tarifa,SimbTfa,LtaPre,CdTarifa,CdClie,CdAgencia,CdVend,CdCiudad,CdZona,CdSzona,CdGruCli,OtrosCrit,Anulado,TimeSys,IdUsuario,CodBod)
 VALUES (@pmTipo,@pmNumero,@pmFecInicial,@pmFecFinal,@pmCdCia,@pmCdLinea,@pmCdGrupo,@pmCdSubgrupo,@pmCdMarca,@pmCdProducto,@pmTarifa,@pmSimbTfa,@pmLtaPre,@pmCdTarifa,@pmCdClie,@pmCdAgencia,@pmCdVend,@pmCdCiudad,@pmCdZona,@pmCdSzona,@pmCdGruCli,@pmOtrosCrit,@pmAnulado,@pmTimeSys,@pmIdUsuario,@pmCodBod)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryProdTarfFec] @pmTipo VARCHAR(3),@pmFecha SMALLDATETIME,@pmCdProducto VARCHAR(16)
,@pmCdSubgrupo VARCHAR(8)=Null,@pmCdCia CHAR(2)=Null,@pmCdClie  VARCHAR(16)=Null,@pmCdVend VARCHAR(16)=Null
,@pmLtaPre CHAR(1)
 AS 
 SELECT Tipo,Numero,FecInicial,FecFinal,CdCia,CdLinea,CdGrupo,CdSubgrupo,CdMarca,CdProducto,Tarifa,SimbTfa,LtaPre,CdTarifa,CdClie
,CdAgencia,CdVend,CdCiudad,CdZona,CdSzona,CdGruCli,OtrosCrit,ISNULL(CodBod,'') AS CodBodega,Anulado,TimeSys,FecUpdate,IdUsuario
 FROM Trn_ProdTarf
 WHERE Tipo=@pmTipo  AND Anulado=0 AND Tarifa>0 AND @pmFecha BETWEEN FecInicial AND FecFinal
 AND CdProducto IN (@pmCdProducto,'0') AND CdSubgrupo IN (@pmCdSubgrupo,'0')
 AND CdCia IN (@pmCdCia,'00') AND CdClie IN (@pmCdClie,'0') AND CdVend IN (@pmCdVend,'0')
 AND LtaPre IN (@pmLtaPre,'0')
 ORDER BY Numero
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpProdTarf] @pmTipo VARCHAR(3),@pmNumero INT,@pmFecInicial SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmCdCia CHAR(2),@pmCdLinea VARCHAR(8),@pmCdGrupo VARCHAR(8),@pmCdSubgrupo VARCHAR(8),@pmCdMarca VARCHAR(4),@pmCdProducto VARCHAR(16),@pmTarifa DECIMAL(14,4),@pmSimbTfa CHAR(1),@pmLtaPre CHAR(1),@pmCdTarifa VARCHAR(4)
,@pmCdClie VARCHAR(16),@pmCdAgencia VARCHAR(16),@pmCdVend VARCHAR(16),@pmCdCiudad VARCHAR(8),@pmCdZona VARCHAR(4)
,@pmCdSzona VARCHAR(4),@pmCdGruCli VARCHAR(4),@pmOtrosCrit VARCHAR(250),@pmAnulado BIT,@pmCodBod VARCHAR(4),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_ProdTarf SET FecInicial=@pmFecInicial,FecFinal=@pmFecFinal,CdCia=@pmCdCia,CdLinea=@pmCdLinea,CdGrupo=@pmCdGrupo,CdSubgrupo=@pmCdSubgrupo,CdMarca=@pmCdMarca,CdProducto=@pmCdProducto,Tarifa=@pmTarifa,SimbTfa=@pmSimbTfa,LtaPre=@pmLtaPre,CdTarifa=@pmCdTarifa,CdClie=@pmCdClie,CdVend=@pmCdVend,CdCiudad=@pmCdCiudad,CdZona=@pmCdZona
,CdSzona=@pmCdSzona,CdGruCli=@pmCdGruCli,OtrosCrit=@pmOtrosCrit,Anulado=@pmAnulado,FecUpdate=@pmFecUpdate,CdAgencia=@pmCdAgencia,CodBod=@pmCodBod
 WHERE Tipo=@pmTipo AND Numero=@pmNumero
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryProdTarf] @pmTipo VARCHAR(3),@pmNumero INT
 AS 
 SELECT Tipo,Numero,FecInicial,FecFinal,CdCia,CdLinea,CdGrupo,CdSubgrupo,CdMarca,CdProducto,Tarifa,SimbTfa,LtaPre,CdTarifa,CdClie,CdAgencia
,CdVend,CdCiudad,CdZona,CdSzona,CdGruCli,OtrosCrit,Anulado,CodBod,TimeSys,FecUpdate,IdUsuario
 FROM Trn_ProdTarf
 WHERE Tipo=@pmTipo AND Numero=@pmNumero
GO