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
ALTER TABLE Trn_ComDetalle ADD CdCuentaNiif VARCHAR(16)
GO
ALTER TABLE tm_Comp ADD tmCuentaNiif VARCHAR(16)
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
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsComDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsComDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Comp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Comp]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Comp_Caj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Comp_Caj]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Comp_Niif]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Comp_Niif]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Comp_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Comp_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_CompauAbr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_CompauAbr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryComDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryComDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryComDetalleLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryComDetalleLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_Comp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_Comp]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_CompDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_CompDso]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_CompLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_CompLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpComDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpComDetalle]
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
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_CompDso] @pmtmNumero VARCHAR(5) 
 AS 
 SELECT tmItem,tmIdCuenta,tmDetalle,tmVrDebito,tmVrCredito,tmIdTercero,tmTercero,tmCodAgncia,tmVrBase,tmTarifaBase
,tmIdCCosto,tmCcosto,tmIdSubCos,tmSubcc,tmIdVehiculo,tmTipoAplica,tmTipFac,tmFactura,tmIdCiaFac,tmItemFac,tmFecVence
,tmTipDoc,tmDocumento,tmIdCiaDoc,tmCodConce,tmNitDoc,tmCodCta,tmNumCheque,tmReferncia,tmTipDocRef,tmDocRef,tmIdCiaRef
,tmNombre,tmNitOtros,tmVehPropio,tmCodSubgpo,tmCiuOrigen,tmConcTrib,tmTarifTrib,tmEstablec,tmPtoEmision,tmAutoriza,tmFechAut,tmCuentaNiif
 FROM tm_Comp
 WHERE tmNumero=@pmtmNumero 
 ORDER BY tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_Comp_Sel] @pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCia CHAR(2)
,@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_Comp (tmNumero,tmItem,tmIdCuenta,tmDetalle,tmVrDebito,tmVrCredito,tmIdTercero,tmIdVehiculo,tmIdCCosto,tmIdSubCos,tmVrBase,tmTarifaBase,tmTipDoc,tmDocumento,tmIdCiaDoc,tmCodConce
,tmTipFac,tmFactura,tmIdCiaFac,tmItemFac,tmFecVence,tmCodCta,tmNumCheque,tmIntegrado,tmTipoAplica,tmConsolida,tmCodCargo,tmNitOtros,tmCodSubgpo
,tmCiuOrigen,tmCodAgncia,tmVehPropio,tmReferncia,tmTipCom,tmComprobante,tmIdCia,tmNombre,tmCcosto,tmSubcc,tmTercero,tmTipDocRef,tmDocRef,tmIdCiaRef,tmNitDoc
,tmConcTrib,tmTarifTrib,tmEstablec,tmPtoEmision,tmAutoriza,tmFechAut,tmCuentaNiif)
 SELECT @pmtmNumero,Item,D.IdCuenta,Detalle,VrDebito,VrCredito,D.IdTercero,IdVehiculo,D.IdCCosto,IdSubCos,VrBase,TarifaBase,TipDoc,Documento,IdCiaDoc,CodConce
,TipFac,Factura,IdCiaFac,ItemFac,FecVence,CodCta,NumCheque,Integrado,TipoAplica,Consolida,CodCargo,NitOtros,CodSubgpo,CiuOrigen,CodAgncia,VehPropio,Referncia
,TipCom,Comprobante,IdCia,SUBSTRING(NomCuenta,1,100),SUBSTRING(CCosto,1,100),'',SUBSTRING(RazonSocial,1,150),TipDocRef,DocRef,IdCiaRef,NitDoc,CdConcTrib,CdTarifTrib,NumEstablec,PtoEmision,Num_Autoriza,FechAutoriza,CdCuentaNiif
 FROM Trn_ComDetalle AS D INNER JOIN Puc AS P ON D.IdCuenta=P.IdCuenta
 LEFT JOIN Terceros AS T ON D.IdTercero=T.IdTercero
 LEFT JOIN CentroCosto AS O ON D.IdCCosto=O.IdCCosto
 WHERE TipCom=@pmTipCom AND Comprobante=@pmComprobante AND IdCia=@pmIdCia
 ORDER BY Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_CompLta] @pmtmNumero VARCHAR(5) 
 AS 
 SELECT tmItem,tmIdCuenta,NomCuenta,tmDetalle,tmVrDebito,tmVrCredito,tmIdTercero,tmTercero,tmVrBase,tmTarifaBase
,tmIdCCosto,CCosto,tmIdSubCos,SubCosto,tmIdVehiculo,tmTipFac,tmFactura,tmIdCiaFac,tmItemFac,tmFecVence
,tmTipDoc,tmDocumento,tmIdCiaDoc,tmCodConce,tmReferncia,tmNitOtros,RazonSocial,tmCuentaNiif
 FROM tm_Comp AS D LEFT JOIN Puc AS P ON D.tmIdCuenta=P.IdCuenta
 LEFT JOIN Terceros AS T ON D.tmNitOtros=T.IdTercero
 LEFT JOIN CentroCosto AS O ON D.tmIdCCosto=O.IdCCosto LEFT JOIN SubCentros AS SC ON D.tmIdSubCos=SC.IdSubCos
 WHERE tmNumero=@pmtmNumero
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_Comp] @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmIdCuenta VARCHAR(16),@pmtmDetalle VARCHAR(250),@pmtmVrDebito MONEY,@pmtmVrCredito MONEY,@pmtmIdTercero VARCHAR(16),@pmtmIdVehiculo VARCHAR(10),@pmtmIdCCosto VARCHAR(16),@pmtmIdSubCos VARCHAR(16)
,@pmtmVrBase MONEY,@pmtmTarifaBase DECIMAL(10,4),@pmtmTipDoc VARCHAR(3),@pmtmDocumento INT,@pmtmIdCiaDoc CHAR(2),@pmtmCodConce VARCHAR(4),@pmtmTipFac VARCHAR(3),@pmtmFactura VARCHAR(15),@pmtmIdCiaFac CHAR(2),@pmtmItemFac INT,@pmtmFecVence SMALLDATETIME,@pmtmCodCta VARCHAR(4)
,@pmtmNumCheque VARCHAR(20),@pmtmIntegrado BIT,@pmtmTipoAplica CHAR(1),@pmtmConsolida BIT,@pmtmCodCargo VARCHAR(4),@pmtmNitOtros VARCHAR(16),@pmtmCodSubgpo VARCHAR(8),@pmtmCiuOrigen VARCHAR(8),@pmtmCodAgncia VARCHAR(16),@pmtmVehPropio INT,@pmtmReferncia VARCHAR(50),@pmtmTipCom VARCHAR(3)
,@pmtmComprobante INT,@pmtmIdCia CHAR(2),@pmtmNombre VARCHAR(100),@pmtmCcosto VARCHAR(100),@pmtmSubcc VARCHAR(100),@pmtmTercero VARCHAR(150),@pmtmTipDocRef VARCHAR(3),@pmtmDocRef INT,@pmtmIdCiaRef CHAR(2),@pmtmNitDoc VARCHAR(16),@pmtmConcTrib VARCHAR(4),@pmtmTarifTrib VARCHAR(4)
,@pmtmEstablec VARCHAR(20),@pmtmPtoEmision VARCHAR(20),@pmtmAutoriza VARCHAR(50),@pmtmFechAut SMALLDATETIME,@pmtmCuentaNiif VARCHAR(16)
 AS 
 INSERT INTO tm_Comp (tmNumero,tmItem,tmIdCuenta,tmDetalle,tmVrDebito,tmVrCredito,tmIdTercero,tmIdVehiculo,tmIdCCosto,tmIdSubCos,tmVrBase,tmTarifaBase,tmTipDoc,tmDocumento,tmIdCiaDoc,tmCodConce,tmTipFac,tmFactura,tmIdCiaFac,tmItemFac,tmFecVence,tmCodCta,tmNumCheque,tmIntegrado,tmTipoAplica,tmConsolida,tmCodCargo,tmNitOtros,tmCodSubgpo
,tmCiuOrigen,tmCodAgncia,tmVehPropio,tmReferncia,tmTipCom,tmComprobante,tmIdCia,tmNombre,tmCcosto,tmSubcc,tmTercero,tmTipDocRef,tmDocRef,tmIdCiaRef,tmNitDoc,tmConcTrib,tmTarifTrib,tmEstablec,tmPtoEmision,tmAutoriza,tmFechAut,tmCuentaNiif)
 VALUES (@pmtmNumero,@pmtmItem,@pmtmIdCuenta,@pmtmDetalle,@pmtmVrDebito,@pmtmVrCredito,@pmtmIdTercero,@pmtmIdVehiculo,@pmtmIdCCosto,@pmtmIdSubCos,@pmtmVrBase,@pmtmTarifaBase,@pmtmTipDoc,@pmtmDocumento,@pmtmIdCiaDoc,@pmtmCodConce,@pmtmTipFac,@pmtmFactura,@pmtmIdCiaFac,@pmtmItemFac,@pmtmFecVence
,@pmtmCodCta,@pmtmNumCheque,@pmtmIntegrado,@pmtmTipoAplica,@pmtmConsolida,@pmtmCodCargo,@pmtmNitOtros,@pmtmCodSubgpo,@pmtmCiuOrigen,@pmtmCodAgncia,@pmtmVehPropio,@pmtmReferncia,@pmtmTipCom,@pmtmComprobante,@pmtmIdCia,@pmtmNombre,@pmtmCcosto,@pmtmSubcc,@pmtmTercero
,@pmtmTipDocRef,@pmtmDocRef,@pmtmIdCiaRef,@pmtmNitDoc,@pmtmConcTrib,@pmtmTarifTrib,@pmtmEstablec,@pmtmPtoEmision,@pmtmAutoriza,@pmtmFechAut,@pmtmCuentaNiif)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_Comp] @pmtmNumero VARCHAR(5),@pmtmItem INT
 AS 
 SELECT tmNumero,tmItem,tmIdCuenta,tmDetalle,tmVrDebito,tmVrCredito,tmIdTercero,tmIdVehiculo,tmIdCCosto,tmIdSubCos,tmVrBase,tmTarifaBase,tmTipDoc,tmDocumento,tmIdCiaDoc,tmCodConce,tmTipFac,tmFactura
,tmIdCiaFac,tmItemFac,tmFecVence,tmCodCta,tmNumCheque,tmIntegrado,tmTipoAplica,tmConsolida,tmCodCargo,tmNitOtros,tmCodSubgpo,tmCiuOrigen,tmCodAgncia,tmVehPropio,tmReferncia,tmTipCom,tmComprobante
,tmIdCia,tmNombre,tmCcosto,tmSubcc,tmTercero,tmTipDocRef,tmDocRef,tmIdCiaRef,tmNitDoc,tmConcTrib,tmTarifTrib,tmEstablec,tmPtoEmision,tmAutoriza,tmFechAut,tmCuentaNiif
 FROM tm_Comp
 WHERE tmNumero=@pmtmNumero 
 AND (tmItem>=ISNULL(@pmtmItem,0)  AND tmItem<=ISNULL(@pmtmItem,2147483647))
 ORDER BY tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_Comp_Niif] @pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCia CHAR(2)
,@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_Comp (tmNumero,tmItem,tmIdCuenta,tmDetalle,tmVrDebito,tmVrCredito,tmIdTercero,tmIdVehiculo,tmIdCCosto,tmIdSubCos,tmVrBase,tmTarifaBase,tmTipDoc,tmDocumento,tmIdCiaDoc,tmCodConce
,tmTipFac,tmFactura,tmIdCiaFac,tmItemFac,tmFecVence,tmCodCta,tmNumCheque,tmIntegrado,tmTipoAplica,tmConsolida,tmCodCargo,tmNitOtros,tmCodSubgpo
,tmCiuOrigen,tmCodAgncia,tmVehPropio,tmReferncia,tmTipCom,tmComprobante,tmIdCia,tmNombre,tmCcosto,tmSubcc,tmTercero,tmTipDocRef,tmDocRef,tmIdCiaRef,tmNitDoc
,tmConcTrib,tmTarifTrib,tmEstablec,tmPtoEmision,tmAutoriza,tmFechAut,tmCuentaNiif)
 SELECT @pmtmNumero,Item,D.IdCuenta,Detalle,VrDebito,VrCredito,D.IdTercero,IdVehiculo,D.IdCCosto,IdSubCos,VrBase,TarifaBase,TipDoc,Documento,IdCiaDoc,CodConce
,TipFac,Factura,IdCiaFac,ItemFac,FecVence,CodCta,NumCheque,Integrado,TipoAplica,Consolida,CodCargo,NitOtros,CodSubgpo,CiuOrigen,CodAgncia,VehPropio,Referncia
,TipCom,Comprobante,IdCia,SUBSTRING(NomCuenta,1,100),SUBSTRING(CCosto,1,100),'',SUBSTRING(RazonSocial,1,150),TipDocRef,DocRef,IdCiaRef,NitDoc,CdConcTrib,CdTarifTrib,NumEstablec,PtoEmision,Num_Autoriza,FechAutoriza,D.IdCuenta
 FROM Trn_NiifDetalle AS D INNER JOIN PucNiif AS P ON D.IdCuenta=P.IdCuenta
 LEFT JOIN Terceros AS T ON D.IdTercero=T.IdTercero
 LEFT JOIN CentroCosto AS O ON D.IdCCosto=O.IdCCosto
 WHERE TipCom=@pmTipCom AND Comprobante=@pmComprobante AND IdCia=@pmIdCia
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_Comp_Caj] @pmtmNumero VARCHAR(5),@pmTipDoc VARCHAR(3),@pmRecibo INT,@pmIdCia CHAR(2)
 AS 
 INSERT INTO tm_Comp (tmNumero,tmItem,tmIdCuenta,tmDetalle,tmVrDebito,tmVrCredito,tmIdTercero,tmIdVehiculo,tmIdCCosto,tmIdSubCos,tmVrBase,tmTarifaBase,tmTipDoc,tmDocumento,tmIdCiaDoc,tmCodConce,tmTipFac,tmFactura,tmIdCiaFac,tmItemFac,tmFecVence
,tmCodCta,tmNumCheque,tmIntegrado,tmTipoAplica,tmConsolida,tmCodCargo,tmNitOtros,tmCodAgncia,tmVehPropio,tmReferncia,tmComprobante,tmNombre,tmCcosto,tmTercero,tmTipDocRef,tmDocRef,tmIdCiaRef,tmConcTrib,tmTarifTrib,tmEstablec,tmPtoEmision,tmAutoriza,tmFechAut,tmCuentaNiif)
SELECT  @pmtmNumero,Item,D.IdCuenta,Detalle,VrDebito,VrCredito,NitTercero,pVehiculo,CdCCosto,CdSubCos,VrBase,TarifaBase,TipDoc,Recibo,IdCia,IdConcepto,TipFac,Factura,IdCiaFac,ItemFac,FecVence
,CodCta,NumCheque,1,TipoAplica,0,IdConcepto,NitOtros,CodAgencia,0,Referncia,0,SUBSTRING(NomCuenta,1,100),SUBSTRING(CCosto,1,100),SUBSTRING(RazonSocial,1,150),TipDocRef,DocRef,IdCiaRef,'','','','','',Null,P.IdCueNiif
 FROM Trn_CompCajaDet AS D INNER JOIN Puc AS P ON D.IdCuenta=P.IdCuenta
 LEFT JOIN Terceros AS T ON D.NitTercero=T.IdTercero
 LEFT JOIN CentroCosto AS O ON D.CdCCosto=O.IdCCosto
 WHERE TipDoc=@pmTipDoc AND Recibo=@pmRecibo AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_CompauAbr] @pmtmNumero VARCHAR(5),@pmtmTipCom VARCHAR(3),@pmtmComprobante INT,@pmtmIdCia CHAR(2)
 AS 
 INSERT INTO  tm_Comp (tmNumero,tmItem,tmIdCuenta,tmDetalle,tmVrDebito,tmVrCredito,tmIdTercero,tmIdVehiculo,tmIdCCosto,tmIdSubCos
,tmVrBase,tmTarifaBase,tmTipDoc,tmDocumento,tmIdCiaDoc,tmCodConce,tmTipFac,tmFactura,tmIdCiaFac,tmItemFac,tmFecVence,tmCodCta,tmNumCheque,tmIntegrado,tmTipoAplica
,tmConsolida,tmCodCargo,tmNitOtros,tmCodSubgpo,tmCiuOrigen,tmCodAgncia,tmVehPropio,tmReferncia,tmNombre,tmCcosto,tmSubcc,tmTercero,tmTipDocRef,tmDocRef,tmIdCiaRef,tmNitDoc
,tmTipCom,tmComprobante,tmIdCia,tmConcTrib,tmTarifTrib,tmEstablec,tmPtoEmision,tmAutoriza,tmFechAut,tmCuentaNiif)
SELECT tmNumero,tmItem,tmIdCuenta,tmDetalle,tmVrDebito,tmVrCredito,tmIdTercero,tmIdVehiculo,tmIdCCosto,tmIdSubCos
,tmVrBase,tmTarifaBase,tmTipDoc,tmDocumento,tmIdCiaDoc,tmCodConce,tmTipFac,tmFactura,tmIdCiaFac,tmItemFac,tmFecVence,tmCodCta,tmNumCheque,tmIntegrado,tmTipoAplica
,tmConsolida,tmCodCargo,tmNitOtros,tmCodSubgpo,tmCiuOrigen,tmCodAgncia,tmVehPropio,tmReferncia,tmNombre,tmCcosto,tmSubcc,tmTercero,tmTipDocRef,tmDocRef,tmIdCiaRef,tmNitDoc
,tmTipCom,tmComprobante,tmIdCia,tmConcTrib,tmTarifTrib,tmEstablec,tmPtoEmision,tmAutoriza,tmFechAut,''
FROM  tm_Compau 
WHERE  tmNumero=@pmtmNumero AND tmTipCom=@pmtmTipCom AND tmComprobante=@pmtmComprobante  AND tmIdCia=@pmtmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryComDetalleLta] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmTipCom VARCHAR(3)=Null
,@pmIdCia CHAR(2)=Null,@pmIdTercero VARCHAR(16)=Null,@pmTipDoc VARCHAR(3)=Null,@pmEsEgreso BIT=Null,@pmIntegrado BIT=Null
,@pmIdCuenta VARCHAR(16)=Null
 AS 
 SELECT C.TipCom AS IdTipoComp,TipoCom,C.Comprobante AS NumComp,C.IdCia AS CdCia,Compania,C.Fecha AS FecComp,C.IdTercero AS Nit,T.RazonSocial AS RaznSocial
,VrTotal,C.IdCta AS CodCta,NumeroCta,CTE.IdBanco AS CodBanco,Banco,EnEfectivo,C.NumCheque AS NroCheque,FecCheque,C.TipDoc AS IdTipoDoc,TipoDoc,C.Documento AS NroDoc,C.IdCiaDoc AS CdCiaDoc
,Anulado,NumDev,FecDev,TipEgr,NEgreso,pVehiculo,C.VehPropio AS EsVehPropio,C.CodConce AS CdConcep,Concepto,Beneficiario,C.Integrado AS EsIntegrado,C.EsEgreso AS EsCompEgreso
,Anticipo,NiifTipo,NiifNumero,FisTipo,FisNumero,C.Observacion AS Observ,C.IdEstado AS CodEstado,Estado,Propddes
--detalles de comprobantes
,Item,D.IdCuenta AS CdCuenta,NomCuenta,Detalle,VrDebito,VrCredito,D.IdTercero AS NitTercero,DT.RazonSocial AS NomTercero,IdVehiculo
,D.IdCCosto AS CdCentCosto,CCosto,D.IdSubCos AS CdSubCentro,SubCosto,VrBase,TarifaBase
,D.TipDoc AS DetTipDoc,D.Documento AS DetNumDoc,D.IdCiaDoc AS DetCiaDoc,D.CodConce AS DetConcept,TipFac,Factura,IdCiaFac,ItemFac,FecVence,CodCta,D.NumCheque AS DetNumCheque
,TipoAplica,Consolida,CodCargo,NitOtros,NIO.RazonSocial AS NomNitOtros,CodSubgpo,Subgrupo,CiuOrigen,LD.Localidad AS NomCiudad,CodAgncia,Referncia
,NitDoc,ND.RazonSocial AS NomNitDoc,TipDocRef,DocRef,IdCiaRef,CdCuentaNiif
,C.TimeSys AS FechaAdd,C.IdCiaCrea AS CdCiaCrea,C.IdUsuario AS IdUsuari,Usuario,VrDivisa1,VrDivisa2,VrDivisa3,TC.IdDiario AS CdDiario,Diario
--información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS TercCiudad,L.IdDep AS CdDep,Departamento,T.Telefono AS TercTelefono
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
 FROM Trn_Comprobantes AS C INNER JOIN Trn_ComDetalle AS D ON C.TipCom=D.TipCom AND C.Comprobante=D.Comprobante
 AND C.IdCia=D.IdCia  INNER JOIN TiposCom AS TC ON C.TipCom=TC.IdCom
 INNER JOIN Diarios AS DR ON TC.IdDiario=DR.IdDiario
 INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia
 INNER JOIN Terceros AS T ON C.IdTercero=T.IdTercero
 INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN  Sys_TiposDoc AS TD ON C.TipDoc=TD.IdDoc
 INNER JOIN CtasCorrientes AS CTE ON C.IdCta=CTE.IdCta INNER JOIN Bancos AS B ON CTE.IdBanco=B.IdBanco
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN Puc AS P ON D.IdCuenta=P.IdCuenta
 INNER JOIN Terceros AS DT ON D.IdTercero=DT.IdTercero
 INNER JOIN CentroCosto AS O ON D.IdCCosto=O.IdCCosto
 LEFT JOIN SubCentros AS SC ON D.IdSubCos=SC.IdSubCos
 LEFT JOIN Terceros AS NIO ON D.NitOtros=NIO.IdTercero
 LEFT JOIN Terceros AS ND ON D.NitDoc=ND.IdTercero
 LEFT JOIN Conceptos AS CN ON C.CodConce=CN.IdConcepto
 LEFT JOIN Localidades AS LD ON D.CiuOrigen=LD.IdLocal
 LEFT JOIN SubGrupos AS SG ON  D.CodSubgpo=SG.IdSubgrupo
 WHERE C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND C.TipCom LIKE ISNULL(@pmTipCom,'%')
 AND C.IdCia LIKE ISNULL(@pmIdCia,'%%') AND C.IdTercero LIKE ISNULL(@pmIdTercero,'%')
 AND C.TipDoc LIKE ISNULL(@pmTipDoc,'%')  AND D.IdCuenta LIKE ISNULL(@pmIdCuenta,'%')
 AND (C.EsEgreso=ISNULL(@pmEsEgreso,0) or C.EsEgreso=ISNULL(@pmEsEgreso,1))
 AND (C.Integrado=ISNULL(@pmIntegrado,0) or C.Integrado=ISNULL(@pmIntegrado,1))
 ORDER BY  C.TipCom,C.IdCia,C.Comprobante,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsComDetalle] @pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCia CHAR(2),@pmItem INT,@pmFecha SMALLDATETIME,@pmIdCuenta VARCHAR(16),@pmDetalle VARCHAR(250),@pmVrDebito MONEY,@pmVrCredito MONEY,@pmIdTercero VARCHAR(16),@pmIdVehiculo VARCHAR(10),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmVehPropio INT,@pmVrBase MONEY,@pmTarifaBase DECIMAL(10,4),@pmTipDoc VARCHAR(3),@pmDocumento INT
,@pmIdCiaDoc CHAR(2),@pmCodConce VARCHAR(4),@pmTipFac VARCHAR(3),@pmFactura VARCHAR(15),@pmIdCiaFac CHAR(2),@pmItemFac INT,@pmFecVence SMALLDATETIME,@pmCodCta VARCHAR(4),@pmNumCheque VARCHAR(20),@pmIntegrado BIT,@pmTipoAplica CHAR(1),@pmConsolida BIT,@pmCodCargo VARCHAR(4),@pmNitOtros VARCHAR(16),@pmCodSubgpo VARCHAR(8),@pmCiuOrigen VARCHAR(8),@pmCodAgncia VARCHAR(16),@pmReferncia VARCHAR(50)
,@pmNitDoc VARCHAR(16),@pmTipDocRef VARCHAR(3),@pmDocRef INT,@pmIdCiaRef CHAR(2),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11),@pmCdConcTrib VARCHAR(4),@pmCdTarifTrib VARCHAR(4),@pmNumEstablec VARCHAR(20),@pmPtoEmision VARCHAR(20),@pmNum_Autoriza VARCHAR(50),@pmFechAutoriza SMALLDATETIME,@pmCdCuentaNiif VARCHAR(16)
 AS 
 INSERT INTO Trn_ComDetalle (TipCom,Comprobante,IdCia,Item,Fecha,IdCuenta,Detalle,VrDebito,VrCredito,IdTercero,IdVehiculo,IdCCosto,VehPropio,VrBase,TarifaBase,TipDoc,Documento,IdCiaDoc,CodConce,TipFac,Factura,IdCiaFac,ItemFac,FecVence,CodCta,NumCheque,Integrado,TipoAplica,Consolida,CodCargo,NitOtros,CodSubgpo,CiuOrigen,IdSubCos,CodAgncia,Referncia,NitDoc
,TipDocRef,DocRef,IdCiaRef,TimeSys,IdCiaCrea,IdUsuario,CdConcTrib,CdTarifTrib,NumEstablec,PtoEmision,Num_Autoriza,FechAutoriza,CdCuentaNiif)
 VALUES (@pmTipCom,@pmComprobante,@pmIdCia,@pmItem,@pmFecha,@pmIdCuenta,@pmDetalle,@pmVrDebito,@pmVrCredito,@pmIdTercero,@pmIdVehiculo,@pmIdCCosto,@pmVehPropio,@pmVrBase,@pmTarifaBase,@pmTipDoc,@pmDocumento,@pmIdCiaDoc,@pmCodConce,@pmTipFac,@pmFactura,@pmIdCiaFac,@pmItemFac,@pmFecVence,@pmCodCta,@pmNumCheque,@pmIntegrado,@pmTipoAplica,@pmConsolida,@pmCodCargo
,@pmNitOtros,@pmCodSubgpo,@pmCiuOrigen,@pmIdSubCos,@pmCodAgncia,@pmReferncia,@pmNitDoc,@pmTipDocRef,@pmDocRef,@pmIdCiaRef,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmCdConcTrib,@pmCdTarifTrib,@pmNumEstablec,@pmPtoEmision,@pmNum_Autoriza,@pmFechAutoriza,@pmCdCuentaNiif)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpComDetalle] @pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCia CHAR(2),@pmItem INT,@pmFecha SMALLDATETIME,@pmIdCuenta VARCHAR(16),@pmDetalle VARCHAR(250),@pmVrDebito MONEY,@pmVrCredito MONEY,@pmIdTercero VARCHAR(16),@pmIdVehiculo VARCHAR(10),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmVehPropio INT,@pmVrBase MONEY,@pmTarifaBase DECIMAL(10,4),@pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCiaDoc CHAR(2),@pmCodConce VARCHAR(4),@pmTipFac VARCHAR(3),@pmFactura VARCHAR(15)
,@pmIdCiaFac CHAR(2),@pmItemFac INT,@pmFecVence SMALLDATETIME,@pmCodCta VARCHAR(4),@pmNumCheque VARCHAR(20),@pmIntegrado BIT,@pmTipoAplica CHAR(1),@pmConsolida BIT,@pmCodCargo VARCHAR(4),@pmNitOtros VARCHAR(16),@pmCodSubgpo VARCHAR(8),@pmCiuOrigen VARCHAR(8),@pmCodAgncia VARCHAR(16),@pmReferncia VARCHAR(50),@pmNitDoc VARCHAR(16),@pmTipDocRef VARCHAR(3),@pmDocRef INT,@pmIdCiaRef CHAR(2),@pmFecUpdate SMALLDATETIME
,@pmCdConcTrib VARCHAR(4),@pmCdTarifTrib VARCHAR(4),@pmNumEstablec VARCHAR(20),@pmPtoEmision VARCHAR(20),@pmNum_Autoriza VARCHAR(50),@pmFechAutoriza SMALLDATETIME,@pmCdCuentaNiif VARCHAR(16)
 AS 
 UPDATE Trn_ComDetalle SET Fecha=@pmFecha,IdCuenta=@pmIdCuenta,Detalle=@pmDetalle,VrDebito=@pmVrDebito,VrCredito=@pmVrCredito,IdTercero=@pmIdTercero,CodAgncia=@pmCodAgncia,IdVehiculo=@pmIdVehiculo,IdCCosto=@pmIdCCosto,VehPropio=@pmVehPropio,VrBase=@pmVrBase,TarifaBase=@pmTarifaBase,TipDoc=@pmTipDoc,Documento=@pmDocumento,IdCiaDoc=@pmIdCiaDoc,CodConce=@pmCodConce,TipFac=@pmTipFac,Factura=@pmFactura,IdCiaFac=@pmIdCiaFac,ItemFac=@pmItemFac,FecVence=@pmFecVence,CodCta=@pmCodCta,NumCheque=@pmNumCheque
,IdSubCos=@pmIdSubCos,Integrado=@pmIntegrado,TipoAplica=@pmTipoAplica,Consolida=@pmConsolida,CodCargo=@pmCodCargo,NitOtros=@pmNitOtros,CodSubgpo=@pmCodSubgpo,CiuOrigen=@pmCiuOrigen,Referncia=@pmReferncia,NitDoc=@pmNitDoc,TipDocRef=@pmTipDocRef,DocRef=@pmDocRef,IdCiaRef=@pmIdCiaRef,FecUpdate=@pmFecUpdate
,CdConcTrib=@pmCdConcTrib,CdTarifTrib=@pmCdTarifTrib,NumEstablec=@pmNumEstablec,PtoEmision=@pmPtoEmision,Num_Autoriza=@pmNum_Autoriza,FechAutoriza=@pmFechAutoriza,CdCuentaNiif=@pmCdCuentaNiif
 WHERE TipCom=@pmTipCom AND Comprobante=@pmComprobante AND IdCia=@pmIdCia AND Item=@pmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryComDetalle] @pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCia CHAR(2),@pmItem INT
 AS 
 SELECT TipCom,Comprobante,IdCia,Item,Fecha,IdCuenta,Detalle,VrDebito,VrCredito,IdTercero,IdVehiculo,IdCCosto,IdSubCos,VehPropio,VrBase,TarifaBase,TipDoc,Documento,IdCiaDoc,CodConce
,TipFac,Factura,IdCiaFac,ItemFac,FecVence,CodCta,NumCheque,Integrado,TipoAplica,Consolida,CodCargo,NitOtros,CodSubgpo,CiuOrigen,CodAgncia,Referncia
,NitDoc,TipDocRef,DocRef,IdCiaRef,TimeSys,FecUpdate,IdCiaCrea,IdUsuario,CdConcTrib,CdTarifTrib,NumEstablec,PtoEmision,Num_Autoriza,FechAutoriza,CdCuentaNiif
 FROM Trn_ComDetalle
 WHERE TipCom=@pmTipCom AND Comprobante=@pmComprobante AND IdCia=@pmIdCia
 AND (Item>=ISNULL(@pmItem,0)  AND Item<=ISNULL(@pmItem,2147483647)) 
 ORDER BY Item
GO