IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FuncTraCumplidoCobro]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[FuncTraCumplidoCobro] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FuncTraCumplidoPago]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[FuncTraCumplidoPago] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsEdsForItems]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paInsEdsForItems] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsEdsRangos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paInsEdsRangos] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_ForItems]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paInstm_ForItems] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_ForItems_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paInstm_ForItems_Sel] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEdsForItems]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryEdsForItems] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEdsFormas_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryEdsFormas_Cr] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEdsFormasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryEdsFormasLta] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEdsFormasRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryEdsFormasRel] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEdsFormasRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryEdsFormasRelDet] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEdsRangos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryEdsRangos] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEdsRangosFor]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryEdsRangosFor] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEdsRangosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryEdsRangosLta] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_EdsFormasPro]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQrytm_EdsFormasPro] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_ForItems]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQrytm_ForItems] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_ForItemsDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQrytm_ForItemsDso] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_ForItemsTot]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQrytm_ForItemsTot] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraCumplidoRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryTraCumplidoRel] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpEdsRangos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paUpEdsRangos] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[FuncTraCumplidoPago] (@pmTipDoc VARCHAR(3),@pmCumplido INT,@pmIdCia CHAR(2)) RETURNS DECIMAL(16,4) AS BEGIN DECLARE @VrRemPago DECIMAL(16,4) SET @VrRemPago=(SELECT SUM(CASE WHEN (C.PesoLiqPago=2 OR C.PesoLiqPago<=0) THEN (CASE D.UndTarifPago WHEN 'UNIDADES' THEN D.Cantidad*D.TarifPago WHEN 'VOLUMEN' THEN D.Volumen*D.TarifPago ELSE D.PesoNeto*D.TarifPago END) WHEN C.PesoLiqPago=3 THEN (CASE D.UndTarifPago WHEN 'UNIDADES' THEN (CASE WHEN D.Cantidad>0 AND D.Cantidad0 THEN D.CantCargue*D.TarifPago ELSE D.Cantidad*D.TarifPago END) END) WHEN 'VOLUMEN' THEN (CASE WHEN D.Volumen>0 AND D.Volumen0 THEN D.VolCargue*D.TarifPago ELSE D.Volumen*D.TarifPago END) END) ELSE (CASE WHEN D.PesoNeto>0 AND D.PesoNeto0 THEN D.PesoCargue*D.TarifPago ELSE D.PesoNeto*D.TarifPago END) END) END) WHEN C.PesoLiqPago=4 THEN (CASE D.UndTarifPago WHEN 'UNIDADES' THEN (CASE WHEN D.CantCargue>D.Cantidad THEN D.CantCargue*D.TarifPago ELSE D.Cantidad*D.TarifPago END) WHEN 'VOLUMEN' THEN (CASE WHEN D.VolCargue>D.Volumen THEN D.VolCargue*D.TarifPago ELSE D.Volumen*D.TarifPago END) ELSE (CASE WHEN D.PesoCargue>D.PesoNeto THEN D.PesoCargue*D.TarifPago ELSE D.PesoNeto*D.TarifPago END) END ) ELSE (CASE D.UndTarifPago WHEN 'UNIDADES' THEN (CASE WHEN D.CantCargue>0 THEN D.CantCargue*D.TarifPago ELSE D.Cantidad*D.TarifPago END) WHEN 'VOLUMEN' THEN (CASE WHEN D.VolCargue>0 THEN D.VolCargue*D.TarifPago ELSE D.Volumen*D.TarifPago END) ELSE (CASE WHEN D.PesoCargue>0 THEN D.PesoCargue*D.TarifPago ELSE D.PesoNeto*D.TarifPago END) END) END) FROM Trn_TraCumplido AS C INNER JOIN Trn_TraCumRemesas AS D ON C.TipDoc=D.TipDoc AND C.Cumplido=D.Cumplido AND C.IdCia=D.IdCia WHERE C.TipDoc=@pmTipDoc AND C.Cumplido=@pmCumplido AND C.IdCia=@pmIdCia) RETURN @VrRemPago END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[FuncTraCumplidoCobro] (@pmTipDoc VARCHAR(3),@pmCumplido INT,@pmIdCia CHAR(2)) RETURNS DECIMAL(16,4) AS BEGIN DECLARE @VrRemCobro DECIMAL(16,4) SET @VrRemCobro=(SELECT SUM(CASE WHEN (C.PesoLiqFact=2 OR C.PesoLiqFact<=0) THEN (CASE D.UndTarifClie WHEN 'UNIDADES' THEN D.Cantidad*D.TarifClie WHEN 'VOLUMEN' THEN D.Volumen*D.TarifClie ELSE D.PesoNeto*D.TarifClie END) WHEN C.PesoLiqFact=3 THEN (CASE D.UndTarifClie WHEN 'UNIDADES' THEN (CASE WHEN D.Cantidad>0 AND D.Cantidad0 THEN D.CantCargue*D.TarifClie ELSE D.Cantidad*D.TarifClie END) END) WHEN 'VOLUMEN' THEN (CASE WHEN D.Volumen>0 AND D.Volumen0 THEN D.VolCargue*D.TarifClie ELSE D.Volumen*D.TarifClie END) END) ELSE (CASE WHEN D.PesoNeto>0 AND D.PesoNeto0 THEN D.PesoCargue*D.TarifClie ELSE D.PesoNeto*D.TarifClie END) END) END) WHEN C.PesoLiqFact=4 THEN (CASE D.UndTarifClie WHEN 'UNIDADES' THEN (CASE WHEN D.CantCargue>D.Cantidad THEN D.CantCargue*D.TarifClie ELSE D.Cantidad*D.TarifClie END) WHEN 'VOLUMEN' THEN (CASE WHEN D.VolCargue>D.Volumen THEN D.VolCargue*D.TarifClie ELSE D.Volumen*D.TarifClie END) ELSE (CASE WHEN D.PesoCargue>D.PesoNeto THEN D.PesoCargue*D.TarifClie ELSE D.PesoNeto*D.TarifClie END) END ) ELSE (CASE D.UndTarifClie WHEN 'UNIDADES' THEN (CASE WHEN D.CantCargue>0 THEN D.CantCargue*D.TarifClie ELSE D.Cantidad*D.TarifClie END) WHEN 'VOLUMEN' THEN (CASE WHEN D.VolCargue>0 THEN D.VolCargue*D.TarifClie ELSE D.Volumen*D.TarifClie END) ELSE (CASE WHEN D.PesoCargue>0 THEN D.PesoCargue*D.TarifClie ELSE D.PesoNeto*D.TarifPago END) END) END) FROM Trn_TraCumplido AS C INNER JOIN Trn_TraCumRemesas AS D ON C.TipDoc=D.TipDoc AND C.Cumplido=D.Cumplido AND C.IdCia=D.IdCia WHERE C.TipDoc=@pmTipDoc AND C.Cumplido=@pmCumplido AND C.IdCia=@pmIdCia) RETURN @VrRemCobro END GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryEdsFormasRel] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null ,@pmIdCliente VARCHAR(16)=Null AS SELECT F.TipoRango,F.NumSerie,F.NumForma,F.Planilla AS NumPlanilla,F.IdCia AS CdCia,Compania,F.Fecha AS FechaVale,F.VrTotal,F.IdCliente,T.RazonSocial AS NomCliente ,F.IdAgencia AS Id_Agencia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,F.pVehiculo,F.nVehiculo,F.IdCajero AS CdCajero,Usuario,F.IdVend AS NitVend,V.RazonSocial AS Vendedor ,F.CdProducto,DescripProd,F.Cantidad AS Cant,F.VrPrecio AS VrPrecioPub,F.IdCuenta AS CodCuenta,NomCuenta,F.IdForma AS CdForma,FormaPago,F.Referencia AS Referncia,F.NumRef2,F.Observacion AS Observ ,F.TipDcm,F.Documento,F.IdCiaDcm,F.FechaDcm,AgencDcm,AD.Agencia AS DocAgencia,F.Anulado AS EstaAnulado,F.FecDev AS FechaDev ,F.IdRango,F.Item,CP.IdJornada AS CdJornada,Jornada,NumCorte,FecCorte ,ISNULL(D.Cant_Neto,0) AS Cant_Neto,ISNULL(D.Cant_Dev,0) AS Cant_Dev,(ISNULL(D.STOTDEV,0)-ISNULL(D.SDCTDEV,0)+ISNULL(D.SIVADEV,0)) AS TotalDev --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 NomCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono --información del cliente ,CLI.IdSzona AS CdSubzona,Subzona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie ,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia ,CdCCosto,F.CdSubCos AS CodSubCos FROM Trn_EdsFormas AS F INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero INNER JOIN Agencias AS A ON F.IdAgencia=A.IdAgencia INNER JOIN Puc AS P ON F.IdCuenta=P.IdCuenta INNER JOIN adm_Usuarios AS U ON F.IdCajero=U.IdUsuario INNER JOIN Terceros AS V ON F.IdVend=V.IdTercero INNER JOIN Formaspago AS FP ON F.IdForma=FP.IdForma INNER JOIN Companias AS C ON F.IdCia=C.IdCia INNER JOIN TercCliente AS CLI ON F.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep LEFT JOIN ProdMcias AS R ON F.CdProducto=R.IdProducto LEFT JOIN Agencias AS AD ON F.AgencDcm=AD.IdAgencia LEFT JOIN Trn_EdsCortes AS CP ON F.Planilla=CP.Planilla AND F.IdCia=CP.IdCia LEFT JOIN Jornadas AS J ON CP.IdJornada=J.IdJornada LEFT JOIN (SELECT TipoRango,IdRango,NumSerie,NumForma,SUM(Cantidad-CantDev) AS Cant_Neto,SUM(CantDev) AS Cant_Dev,SUM((Cantidad-CantDev)*VrPrecio) AS SUBTOTAL ,SUM(CASE WHEN (CantDev>0 AND TarifaDct>0 AND VrDcto>0) THEN (((Cantidad-CantDev)*VrPrecio)*TarifaDct)/100 ELSE VrDcto END) AS STOTDCT ,SUM(CASE WHEN (CantDev>0 AND TarifaIva>0 AND VrIva>0) THEN ((((Cantidad-CantDev)*VrPrecio)-((((Cantidad-CantDev)*VrPrecio)*TarifaDct)/100))*TarifaIva)/100 ELSE VrIva END) AS STOTIVA ,SUM(CantDev*VrPrecio) AS STOTDEV,SUM(((CantDev*VrPrecio)*TarifaDct)/100) AS SDCTDEV ,SUM((((CantDev*VrPrecio)-(((CantDev*VrPrecio)*TarifaDct)/100))*TarifaIva)/100) AS SIVADEV ,SUM(Cantidad) AS Det_Cant,SUM(Cantidad*VrPrecio) AS Det_Total,SUM(VrDcto) AS Det_VrDcto,SUM(VrIva) AS Det_VrIva FROM Trn_EdsForItems GROUP BY TipoRango,IdRango,NumSerie,NumForma) AS D ON F.TipoRango=D.TipoRango AND F.IdRango=D.IdRango AND F.NumSerie=D.NumSerie AND F.NumForma=D.NumForma WHERE F.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (F.IdCia=@pmIdCia OR @pmIdCia IS NULL) AND (F.IdCliente=@pmIdCliente OR @pmIdCliente IS NULL) GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryEdsFormasRelDet] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null ,@pmIdCliente VARCHAR(16)=Null AS SELECT F.TipoRango AS TipRango,F.NumSerie AS Num_Serie,F.NumForma AS NumVale,F.IdCia AS CdCia,Compania,F.Fecha AS FechaVale --detalle del vale ,FD.Item AS NumItem,FD.IdProducto AS CodProducto,DescripProd,FD.IdBodega AS CdBodega,Bodega,FD.Cantidad AS Cant,FD.VrPrecio,FD.Cantidad*FD.VrPrecio AS ValorTotal,TarifaDct,VrDcto,TarifaIva,VrIva ,Servicios,Descripcion,ListaPrec,FD.CantDev,FD.CantFact,VrTotal AS ValorVale,IdCliente,T.RazonSocial AS NomCliente,F.IdAgencia AS Id_Agencia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia ,pVehiculo,nVehiculo,F.VrPrecio AS VrPrecioPub,F.IdCajero AS CdCajero,Usuario,F.IdVend AS NitVend,V.RazonSocial AS Vendedor ,F.IdCuenta AS CodCuenta,NomCuenta,F.IdForma AS CdForma,FormaPago,F.Referencia AS Referncia,F.NumRef2,F.Observacion AS Observ ,TipDcm,Documento,IdCiaDcm,FechaDcm,AgencDcm,AD.Agencia AS DocAgencia,F.Anulado AS EstaAnulado,F.FecDev AS FechaDev,F.IdRango AS NumRango,Planilla --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 NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono --información del cliente ,CLI.IdSzona AS CdSubzona,Subzona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie ,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia ,CdCCosto,F.CdSubCos AS CodSubCos,FD.CdOperario,OP.RazonSocial AS Operario,FD.Comision FROM Trn_EdsFormas AS F INNER JOIN Trn_EdsForItems AS FD ON F.TipoRango=FD.TipoRango AND F.IdRango=FD.IdRango AND F.NumSerie=FD.NumSerie AND F.NumForma=FD.NumForma INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero INNER JOIN Agencias AS A ON F.IdAgencia=A.IdAgencia INNER JOIN Puc AS P ON F.IdCuenta=P.IdCuenta INNER JOIN adm_Usuarios AS U ON F.IdCajero=U.IdUsuario INNER JOIN Terceros AS V ON F.IdVend=V.IdTercero INNER JOIN Formaspago AS FP ON F.IdForma=FP.IdForma INNER JOIN Companias AS C ON F.IdCia=C.IdCia INNER JOIN TercCliente AS CLI ON F.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep INNER JOIN ProdMcias AS R ON FD.IdProducto=R.IdProducto INNER JOIN Bodegas AS B ON FD.IdBodega=B.IdBodega LEFT JOIN Agencias AS AD ON F.AgencDcm=AD.IdAgencia LEFT JOIN Terceros AS OP ON FD.CdOperario=OP.IdTercero WHERE F.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (F.IdCia=@pmIdCia OR @pmIdCia IS NULL) AND (F.IdCliente=@pmIdCliente OR @pmIdCliente IS NULL) GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryEdsFormas_Cr] @pmTipoRango VARCHAR(3),@pmIdRango INT ,@pmNumSerie VARCHAR(5),@pmNumFormaIni INT,@pmNumFormaFin INT AS SELECT F.TipoRango AS TipRango,F.IdRango AS NumRango,F.NumSerie AS NroSerie,F.NumForma AS NumVale ,Fecha,VrTotal,IdCliente,T.RazonSocial AS NomCliente,F.IdAgencia AS CdAgencia,A.Agencia AS NomAgencia,F.IdCuenta AS CdCuenta,NomCuenta ,pVehiculo,nVehiculo,IdCajero,Usuario,F.IdVend AS CdVend,V.RazonSocial AS Vendedor ,F.Referencia AS Referncia,NumRef2,F.Observacion AS Observ,CantPuntos,TipDcm,Documento,IdCiaDcm,FechaDcm,AgencDcm ,Anulado,NumDev,FecDev,Planilla,F.IdCia AS CdCia,Compania,F.VrPrecio AS VrPrecioUnd --detalles ,D.Item AS nItem,D.IdProducto AS CdProducto,DescripProd,D.IdBodega AS CdBodega,Bodega,D.Cantidad AS Cant,D.VrPrecio,TarifaIva,VrIva,TarifaDct,VrDcto ,Servicios,Descripcion,ListaPrec,PM.IdUnd AS CdUnid,Unidad,CdCCosto,CdSubCos,D.CantDev,D.CantFact,D.CdOperario,OP.RazonSocial AS Operario,D.Comision FROM Trn_EdsFormas AS F INNER JOIN Trn_EdsForItems AS D ON F.TipoRango=D.TipoRango AND F.IdRango=D.IdRango AND F.NumSerie=D.NumSerie AND F.NumForma=D.NumForma INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero INNER JOIN Agencias AS A ON F.IdAgencia=A.IdAgencia INNER JOIN Puc AS P ON F.IdCuenta=P.IdCuenta INNER JOIN adm_Usuarios AS U ON F.IdCajero=U.IdUsuario INNER JOIN Terceros AS V ON F.IdVend=V.IdTercero INNER JOIN ProdMcias AS PM ON D.IdProducto=PM.IdProducto INNER JOIN Bodegas AS B ON D.IdBodega=B.IdBodega INNER JOIN UndMed AS UM ON PM.IdUnd=UM.IdUnd LEFT JOIN Companias AS C ON F.IdCia=C.IdCia LEFT JOIN Terceros AS OP ON D.CdOperario=OP.IdTercero WHERE F.TipoRango=@pmTipoRango AND F.IdRango=@pmIdRango AND F.NumSerie=@pmNumSerie AND F.NumForma BETWEEN @pmNumFormaIni AND @pmNumFormaFin ORDER BY D.Item GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQrytm_ForItemsDso] @pmtmNumero VARCHAR(5) AS SELECT tmItem,tmIdProducto,tmDescripcion,tmIdBodega,Bodega,tmCantidad,tmVrPrecio,tmListaPrec ,tmTarifaDct,tmVrDcto,tmTarifaIva,tmVrIva,((tmCantidad*tmVrPrecio)-tmVrDcto)+tmVrIva AS ValorTotal ,tmCdOperario,tmComsOper,tmServcios,tmCantDev,tmCantFact FROM tm_ForItems AS F INNER JOIN Bodegas AS B ON F.tmIdBodega=B.IdBodega WHERE tmNumero=@pmtmNumero ORDER BY tmItem GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paQryEdsRangosLta] @pmTipoRango VARCHAR(3)=Null,@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME ,@pmRangoIni INT=Null,@pmRangoFin INT=Null,@pmIdCliente VARCHAR(16)=Null,@pmNumSerie VARCHAR(5)=Null,@pmAnulado BIT=Null ,@pmModalidad VARCHAR(10)=Null,@pmIdEstado VARCHAR(4)=Null AS SELECT TipoRango,IdRango,R.IdForma AS CdForma,FormaPago,Fecha,FecVence,IdCliente,RazonSocial,R.IdAgencia AS Id_Agencia,A.Agencia AS NomAgencia,pVehiculo,nVehiculo ,R.IdCuenta AS CdCuenta,NomCuenta,NumSerie,NumInicial,NumFinal,NumTarjeta,VrCupo,VrConsumos,TipoConsumo,ReqPlaca,ReqProd,ReqGalones,VldAgencia,VldPlaca ,Modalidad,R.Observacion AS Observ,Anulado,R.IdEstado AS IdEstad,Estado,NumAuto,NumActual,R.Cod_CCosto,R.Cod_SubCosto,R.IdUsuario AS IdUsuari,Usuario,TimeSys,FecUpdate,IdCiaCrea FROM Trn_EdsRangos AS R INNER JOIN Formaspago AS F ON R.IdForma=F.IdForma INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero INNER JOIN Puc AS P ON R.IdCuenta=P.IdCuenta INNER JOIN EstadoDoc AS E ON R.IdEstado=E.IdEstado INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario LEFT JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia WHERE TipoRango LIKE ISNULL(@pmTipoRango,'%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND NumSerie LIKE ISNULL(@pmNumSerie ,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%') AND R.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND (IdRango>=ISNULL(@pmRangoIni,0) AND IdRango<=ISNULL(@pmRangoFin,2147483647)) AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1)) AND (Fecha>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND Fecha<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME))) ORDER BY TipoRango,IdRango GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryEdsFormasLta] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME ,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null AS SELECT F.TipoRango,F.IdRango,F.NumSerie,F.NumForma,F.Planilla,F.IdCia AS CdCia,Compania,F.Item,F.Fecha,F.VrTotal,F.IdCliente,T.RazonSocial AS NomCliente,F.IdAgencia AS CdAgencia,A.Agencia AS NomAgencia ,F.IdCuenta AS CdCuenta,NomCuenta,F.pVehiculo,F.nVehiculo,F.IdCajero,Usuario,F.IdVend AS CdVend,V.RazonSocial AS Vendedor,F.CdProducto,DescripProd,F.Cantidad,F.IdForma AS CdForma,FormaPago ,F.Referencia AS Referncia,F.NumRef2,F.CantPuntos,F.Observacion AS Observ,F.TipDcm,F.Documento,F.IdCiaDcm,F.FechaDcm,AgencDcm,F.Anulado,F.NumDev,F.FecDev,F.CdCCosto,F.CdSubCos,F.VrPrecio ,ISNULL(D.Cant_Neto,0) AS Cant_Neto,ISNULL(D.Cant_Dev,0) AS Cant_Dev,(ISNULL(D.STOTDEV,0)-ISNULL(D.SDCTDEV,0)+ISNULL(D.SIVADEV,0)) AS TotalDev FROM Trn_EdsFormas AS F INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero INNER JOIN Agencias AS A ON F.IdAgencia=A.IdAgencia INNER JOIN Puc AS P ON F.IdCuenta=P.IdCuenta INNER JOIN adm_Usuarios AS U ON F.IdCajero=U.IdUsuario INNER JOIN Terceros AS V ON F.IdVend=V.IdTercero INNER JOIN Formaspago AS FP ON F.IdForma=FP.IdForma INNER JOIN Companias AS C ON F.IdCia=C.IdCia LEFT JOIN ProdMcias AS R ON F.CdProducto=R.IdProducto LEFT JOIN (SELECT TipoRango,IdRango,NumSerie,NumForma,SUM(Cantidad-CantDev) AS Cant_Neto,SUM(CantDev) AS Cant_Dev,SUM((Cantidad-CantDev)*VrPrecio) AS SUBTOTAL ,SUM(CASE WHEN (CantDev>0 AND TarifaDct>0 AND VrDcto>0) THEN (((Cantidad-CantDev)*VrPrecio)*TarifaDct)/100 ELSE VrDcto END) AS STOTDCT ,SUM(CASE WHEN (CantDev>0 AND TarifaIva>0 AND VrIva>0) THEN ((((Cantidad-CantDev)*VrPrecio)-((((Cantidad-CantDev)*VrPrecio)*TarifaDct)/100))*TarifaIva)/100 ELSE VrIva END) AS STOTIVA ,SUM(CantDev*VrPrecio) AS STOTDEV,SUM(((CantDev*VrPrecio)*TarifaDct)/100) AS SDCTDEV ,SUM((((CantDev*VrPrecio)-(((CantDev*VrPrecio)*TarifaDct)/100))*TarifaIva)/100) AS SIVADEV FROM Trn_EdsForItems GROUP BY TipoRango,IdRango,NumSerie,NumForma) AS D ON F.TipoRango=D.TipoRango AND F.IdRango=D.IdRango AND F.NumSerie=D.NumSerie AND F.NumForma=D.NumForma WHERE (F.Fecha>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND F.Fecha<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME))) AND (F.IdCia=@pmIdCia OR @pmIdCia IS NULL) AND (F.IdCliente=@pmIdCliente OR @pmIdCliente IS NULL) GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQrytm_EdsFormasPro] @pmtmNumero VARCHAR(5) AS SELECT tmTipoRango,tmIdRango,tmIdForma,tmNumSerie,tmNumForma,tmVrTotal ,tmObservacion,tmIdCliente,tmIdAgencia,tmIdCuenta,tmpVehiculo,tmnVehiculo ,tmIdVend,tmCdProducto,tmCantidad,tmReferencia,tmReferncia2,tmCantPuntos,tmCdCCosto,tmCdSubCos --VARIABLES DE ITEM ,Item,IdProducto,IdBodega,Cantidad,VrPrecio,TarifaIva,VrIva,TarifaDct,VrDcto,Servicios,Descripcion,ListaPrec,CantDev ,CdOperario,Comision FROM tm_EdsFormas AS F INNER JOIN Trn_EdsForItems AS I ON F.tmTipoRango=I.TipoRango AND F.tmIdRango=I.IdRango AND F.tmNumSerie=I.NumSerie AND F.tmNumForma=I.NumForma WHERE tmNumero=@pmtmNumero AND tmCredito<>0 AND tmEsCaja<>0 AND (I.Cantidad-I.CantDev)>0 ORDER BY tmItem,Item GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paInsEdsRangos] @pmTipoRango VARCHAR(3),@pmIdRango INT,@pmIdForma VARCHAR(4),@pmFecha SMALLDATETIME,@pmFecVence SMALLDATETIME,@pmIdCliente VARCHAR(16) ,@pmIdAgencia VARCHAR(16),@pmpVehiculo VARCHAR(10),@pmnVehiculo VARCHAR(10),@pmIdCuenta VARCHAR(16),@pmNumSerie VARCHAR(5),@pmNumInicial INT,@pmNumFinal INT,@pmNumTarjeta VARCHAR(30) ,@pmVrCupo MONEY,@pmVrConsumos MONEY,@pmTipoConsumo INT,@pmReqPlaca BIT,@pmReqProd BIT,@pmReqGalones BIT,@pmVldAgencia BIT,@pmVldPlaca VARCHAR(10),@pmModalidad VARCHAR(10) ,@pmAnulado BIT,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmNumAuto BIT,@pmNumActual INT,@pmCod_CCosto VARCHAR(16),@pmCod_SubCosto VARCHAR(16),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11) AS INSERT INTO Trn_EdsRangos (TipoRango,IdRango,IdForma,Fecha,FecVence,IdCliente,IdAgencia,pVehiculo,nVehiculo,IdCuenta,NumSerie,NumInicial,NumFinal,NumTarjeta,VrCupo,VrConsumos,TipoConsumo,ReqPlaca ,ReqProd,ReqGalones,VldAgencia,VldPlaca,Modalidad,Anulado,Observacion,IdEstado,NumAuto,NumActual,TimeSys,IdCiaCrea,IdUsuario,Cod_CCosto,Cod_SubCosto) VALUES (@pmTipoRango,@pmIdRango,@pmIdForma,@pmFecha,@pmFecVence,@pmIdCliente,@pmIdAgencia,@pmpVehiculo,@pmnVehiculo,@pmIdCuenta,@pmNumSerie,@pmNumInicial,@pmNumFinal,@pmNumTarjeta ,@pmVrCupo,@pmVrConsumos,@pmTipoConsumo,@pmReqPlaca,@pmReqProd,@pmReqGalones,@pmVldAgencia,@pmVldPlaca,@pmModalidad,@pmAnulado,@pmObservacion,@pmIdEstado,@pmNumAuto,@pmNumActual,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmCod_CCosto,@pmCod_SubCosto) GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paUpEdsRangos] @pmTipoRango VARCHAR(3),@pmIdRango INT,@pmIdForma VARCHAR(4),@pmFecha SMALLDATETIME,@pmFecVence SMALLDATETIME,@pmIdCliente VARCHAR(16) ,@pmIdAgencia VARCHAR(16),@pmpVehiculo VARCHAR(10),@pmnVehiculo VARCHAR(10),@pmIdCuenta VARCHAR(16),@pmNumSerie VARCHAR(5),@pmNumInicial INT,@pmNumFinal INT,@pmNumTarjeta VARCHAR(30) ,@pmVrCupo MONEY,@pmVrConsumos MONEY,@pmTipoConsumo INT,@pmReqPlaca BIT,@pmReqProd BIT,@pmReqGalones BIT,@pmVldAgencia BIT,@pmVldPlaca VARCHAR(10),@pmModalidad VARCHAR(10) ,@pmAnulado BIT,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmNumAuto BIT,@pmNumActual INT,@pmCod_CCosto VARCHAR(16),@pmCod_SubCosto VARCHAR(16),@pmFecUpdate SMALLDATETIME AS UPDATE Trn_EdsRangos SET IdForma=@pmIdForma,Fecha=@pmFecha,FecVence=@pmFecVence,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,pVehiculo=@pmpVehiculo,nVehiculo=@pmnVehiculo ,IdCuenta=@pmIdCuenta,NumSerie=@pmNumSerie,NumInicial=@pmNumInicial,NumFinal=@pmNumFinal,NumTarjeta=@pmNumTarjeta,VrCupo=@pmVrCupo,VrConsumos=@pmVrConsumos,TipoConsumo=@pmTipoConsumo ,ReqPlaca=@pmReqPlaca,ReqProd=@pmReqProd,ReqGalones=@pmReqGalones,VldAgencia=@pmVldAgencia,VldPlaca=@pmVldPlaca,Modalidad=@pmModalidad,Anulado=@pmAnulado,Observacion=@pmObservacion ,IdEstado=@pmIdEstado,NumAuto=@pmNumAuto,NumActual=@pmNumActual,Cod_CCosto=@pmCod_CCosto,Cod_SubCosto=@pmCod_SubCosto,FecUpdate=@pmFecUpdate WHERE TipoRango=@pmTipoRango AND IdRango=@pmIdRango GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paQryEdsRangos] @pmTipoRango VARCHAR(3),@pmIdRango INT AS SELECT TipoRango,IdRango,IdForma,Fecha,FecVence,IdCliente,IdAgencia,pVehiculo,nVehiculo,IdCuenta,NumSerie,NumInicial,NumFinal ,NumTarjeta,VrCupo,VrConsumos,TipoConsumo,ReqPlaca,ReqProd,ReqGalones,VldAgencia,VldPlaca ,Modalidad,Anulado,Observacion,IdEstado,NumAuto,NumActual,Cod_CCosto,Cod_SubCosto,TimeSys,FecUpdate,IdCiaCrea,IdUsuario FROM Trn_EdsRangos WHERE TipoRango=@pmTipoRango AND IdRango=@pmIdRango GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paQryEdsRangosFor] @pmTipoRango VARCHAR(3),@pmNumSerie VARCHAR(5) ,@pmNumForma INT,@pmIdCia CHAR(2) AS SELECT TipoRango,IdRango,IdForma,Fecha,FecVence,IdCliente,IdAgencia,pVehiculo,nVehiculo,IdCuenta,NumSerie,NumInicial,NumFinal ,NumTarjeta,VrCupo,VrConsumos,TipoConsumo,ReqPlaca,ReqProd,ReqGalones,VldAgencia,VldPlaca ,Modalidad,Anulado,Observacion,IdEstado,NumAuto,NumActual,Cod_CCosto,Cod_SubCosto,TimeSys,FecUpdate,IdCiaCrea,IdUsuario FROM Trn_EdsRangos WHERE TipoRango=@pmTipoRango AND NumSerie=@pmNumSerie AND @pmNumForma BETWEEN NumInicial AND NumFinal AND Anulado=0 AND IdCiaCrea=@pmIdCia ORDER BY IdRango GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paInsEdsForItems] @pmTipoRango VARCHAR(3),@pmIdRango INT,@pmNumSerie VARCHAR(5),@pmNumForma INT,@pmItem INT,@pmIdProducto VARCHAR(16),@pmIdBodega VARCHAR(4),@pmCantidad DECIMAL(14,4) ,@pmVrPrecio MONEY,@pmTarifaIva DECIMAL(14,4),@pmVrIva MONEY,@pmTarifaDct DECIMAL(14,4),@pmVrDcto MONEY,@pmServicios BIT,@pmDescripcion VARCHAR(150),@pmListaPrec CHAR(1),@pmCantDev DECIMAL(14,4),@pmCantFact DECIMAL(14,4) ,@pmCdOperario VARCHAR(16),@pmComision DECIMAL(14,4) AS INSERT INTO Trn_EdsForItems (TipoRango,IdRango,NumSerie,NumForma,Item,IdProducto,IdBodega,Cantidad,VrPrecio,TarifaIva,VrIva,TarifaDct,VrDcto,Servicios,Descripcion,ListaPrec,CantDev,CantFact,CdOperario,Comision) VALUES (@pmTipoRango,@pmIdRango,@pmNumSerie,@pmNumForma,@pmItem,@pmIdProducto,@pmIdBodega,@pmCantidad,@pmVrPrecio,@pmTarifaIva,@pmVrIva,@pmTarifaDct,@pmVrDcto,@pmServicios,@pmDescripcion,@pmListaPrec,@pmCantDev,@pmCantFact,@pmCdOperario,@pmComision) GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paQryEdsForItems] @pmTipoRango VARCHAR(3),@pmIdRango INT,@pmNumSerie VARCHAR(5),@pmNumForma INT,@pmItem INT AS SELECT TipoRango,IdRango,NumSerie,NumForma,Item,IdProducto,IdBodega,Cantidad,VrPrecio,TarifaIva,VrIva,TarifaDct,VrDcto,Servicios,Descripcion ,ListaPrec,CantDev,CantFact,CdOperario,Comision FROM Trn_EdsForItems WHERE TipoRango=@pmTipoRango AND IdRango=@pmIdRango AND NumSerie=@pmNumSerie AND NumForma=@pmNumForma AND (Item>=ISNULL(@pmItem,0) AND Item<=ISNULL(@pmItem,2147483647)) ORDER BY Item GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paInstm_ForItems_Sel] @pmtmNumero VARCHAR(5),@pmTipoRango VARCHAR(3),@pmIdRango INT ,@pmNumSerie VARCHAR(5),@pmNumForma INT AS INSERT INTO tm_ForItems (tmNumero,tmItem,tmIdProducto,tmIdBodega,tmCantidad,tmVrPrecio,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDcto,tmVrBruto,tmDescripcion ,tmServcios,tmListaPrec,tmCantDev,tmCantFact,tmCdOperario,tmComsOper) SELECT @pmtmNumero,Item,IdProducto,IdBodega,Cantidad,VrPrecio,TarifaIva,VrIva,TarifaDct,VrDcto,VrPrecio,Descripcion,Servicios,ListaPrec,CantDev,CantFact,CdOperario,Comision FROM Trn_EdsForItems WHERE TipoRango=@pmTipoRango AND IdRango=@pmIdRango AND NumSerie=@pmNumSerie AND NumForma=@pmNumForma GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paQrytm_ForItems] @pmtmNumero VARCHAR(5),@pmtmItem INT AS SELECT tmNumero,tmItem,tmIdProducto,tmIdBodega,tmCantidad,tmVrPrecio,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDcto ,tmVrBruto,tmDescripcion,tmServcios,tmListaPrec,tmCantDev,tmCantFact,tmCdOperario,tmComsOper FROM tm_ForItems WHERE tmNumero=@pmtmNumero AND (tmItem>=ISNULL(@pmtmItem,0) AND tmItem<=ISNULL(@pmtmItem,2147483647)) GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paInstm_ForItems] @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmIdProducto VARCHAR(16),@pmtmIdBodega VARCHAR(4),@pmtmCantidad DECIMAL(14,4),@pmtmVrPrecio MONEY ,@pmtmTarifaIva DECIMAL(14,4),@pmtmVrIva MONEY,@pmtmTarifaDct DECIMAL(14,4),@pmtmVrDcto MONEY,@pmtmVrBruto MONEY,@pmtmDescripcion VARCHAR(150),@pmtmServcios BIT,@pmtmListaPrec CHAR(1) ,@pmtmCantDev DECIMAL(14,4),@pmtmCantFact DECIMAL(14,4),@pmtmCdOperario VARCHAR(16),@pmtmComsOper DECIMAL(14,4) AS INSERT INTO tm_ForItems (tmNumero,tmItem,tmIdProducto,tmIdBodega,tmCantidad,tmVrPrecio,tmTarifaIva,tmVrIva,tmTarifaDct,tmVrDcto,tmVrBruto,tmDescripcion,tmServcios,tmListaPrec,tmCantDev,tmCantFact,tmCdOperario,tmComsOper) VALUES (@pmtmNumero,@pmtmItem,@pmtmIdProducto,@pmtmIdBodega,@pmtmCantidad,@pmtmVrPrecio,@pmtmTarifaIva,@pmtmVrIva,@pmtmTarifaDct,@pmtmVrDcto,@pmtmVrBruto,@pmtmDescripcion,@pmtmServcios,@pmtmListaPrec,@pmtmCantDev,@pmtmCantFact,@pmtmCdOperario,@pmtmComsOper) GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paQrytm_ForItemsTot] @pmtmNumero VARCHAR(5) AS SELECT COUNT(tmItem) AS SCOUNT,SUM(tmCantidad) AS SCANT,SUM(tmCantidad*tmVrPrecio) AS SSUBTOT ,SUM(tmVrIva) AS SVALIVA,SUM(tmVrDcto) AS SVALDCT,SUM(tmVrBruto) AS SVALBRU ,SUM(tmCantDev) AS SCANTDEV,SUM(tmCantFact) AS SCANTFACT FROM tm_ForItems WHERE tmNumero=@pmtmNumero GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paQryTraCumplidoRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME, @pmIdCia CHAR(2)=Null ,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmIdConductor VARCHAR(16)=Null AS SELECT CU.TipDoc AS TipCum,CU.Cumplido AS NumCumplido,CU.IdCia AS CdCia,Compania,CU.Fecha AS FechaCum,TipMuc,CU.Manifiesto AS NumManif,IdCiaMuc,CU.IdVehiculo AS PlacaVeh,Modalidad,DiasPlazo,FecPago ,TipoMargen,MargenFalt,UndCalcFalt,TarifFaltPago,TarifFaltCobro,NRadicaDoc,IdCiaRadic,CdCiaOfic,FecRadic,CU.Anulado AS Anuldo,CU.FecDev AS FechaDev,TipoComp,NumComp,NumRadicaMT,CU.Observacion AS Observ,CU.IdEstado AS CdEstado,Estado ,CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen ,CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,CdRuta,R.Ruta AS DescRuta ,CU.TimeSys AS FechaCrea,CU.FecUpdate AS FechaAct,CU.IdCiaCrea AS CdCiaCrea,CU.IdUsuario AS CdUsuario,Usuario ,M.Fecha AS FecManif,FecDespacho,M.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,nRemolque,TipoAfiVehic,M.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor ,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos,VrCargos,VrDctos,TarifaFlete,M.Cantidad AS CantTotal,PesoTotal ,IdLocFletes,CF.Localidad AS LugarFletes,FechaPago,PagoCargue,PagoDescargue,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,FechaOdp,EstOrden,M.Observacion AS MucObserv ,MA.TipoRuta,MA.kmsTotal,MA.NomRemite,MA.NomDestino,MA.LugarFletes,MA.NumAnticipo AS NumAnticipo,MA.NumCheque AS Num_Cheque,MA.TipoMintrans,MA.WsSeguro,MA.NumRadSeguro,CU.TipoCumpMT,CU.MotivoSusp,CU.ConsecSusp ,dbo.FuncTraCumplidoCobro(CU.TipDoc,CU.Cumplido,CU.IdCia) AS VrTotalClie,dbo.FuncTraCumplidoPago(CU.TipDoc,CU.Cumplido,CU.IdCia) AS VrTotalPago --Datos del vehiculo ,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail ,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config ,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora ,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion,V.IdGrupo AS CdGrupoPro,GrupoProp ,M.Remesa,M.IdCiaRem,RMT.Fecha AS Fecremesa,RMT.Comprobante AS Cmpremesa,RMT.TipCom AS TipComRmt FROM Trn_TraCumplido AS CU INNER JOIN Companias AS CN ON CU.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON CU.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON CU.IdUsuario=U.IdUsuario INNER JOIN Trn_TraManifiesto AS M ON CU.TipMuc=M.TipDoc AND CU.Manifiesto=M.Manifiesto AND CU.IdCiaMuc=M.IdCia INNER JOIN Trn_TraManifAnexo AS MA ON CU.TipMuc=MA.TipDoc AND CU.Manifiesto=MA.Manifiesto AND CU.IdCiaMuc=MA.IdCia INNER JOIN Terceros AS CDT ON M.IdConductor=CDT.IdTercero INNER JOIN Terceros AS NP ON M.IdPropietario=NP.IdTercero INNER JOIN Terceros AS T ON M.IdPoseedor=T.IdTercero INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal INNER JOIN Vehiculos AS V ON CU.IdVehiculo=V.IdVehiculo INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd LEFT JOIN Localidades AS CO ON CU.CdOrigen=CO.IdLocal LEFT JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep LEFT JOIN Localidades AS CD ON CU.CdDestino=CD.IdLocal LEFT JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep LEFT JOIN Rutas AS R ON CU.CdRuta=R.IdRuta LEFT JOIN Terceros AS NE ON V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo LEFT JOIN (SELECT NumOrden,IdCia,Fecha,TipCom,Comprobante,IdCiaCom FROM Trn_TraRemesa WHERE TipDoc='RMT') AS RMT ON M.Remesa=RMT.NumOrden AND M.IdCiaRem=RMT.IdCia WHERE CU.TipDoc=@pmTipDoc AND CU.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND CU.IdCia LIKE ISNULL(@pmIdCia,'%%') AND CU.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%') AND M.IdConductor LIKE ISNULL(@pmIdConductor,'%') ORDER BY CU.IdCia,CU.Cumplido GO --Oct 30/2019 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsCausacion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paInsCausacion] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsCausacVence]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paInsCausacVence] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsFijos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paInsFijos] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsFijos_Cue]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paInsFijos_Cue] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNotasBuses]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paInsNotasBuses] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Cargos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paInstm_Cargos] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Cargos_Vce]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paInstm_Cargos_Vce] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCausacion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryCausacion] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCausacion_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryCausacion_Cr] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCausacion_Crr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryCausacion_Crr] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCausacionCue]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryCausacionCue] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCausacionD_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryCausacionD_Cr] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCausacionDia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryCausacionDia] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCausacionFij]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryCausacionFij] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCausacionOtr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryCausacionOtr] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCausacVence]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryCausacVence] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCausacVence_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryCausacVence_Cr] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCausacVence_Cra]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryCausacVence_Cra] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCausacVenceLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryCausacVenceLta] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCausacVenceVp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryCausacVenceVp] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFacturasFmtc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryFacturasFmtc] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFijos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryFijos] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFijos_Cue]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryFijos_Cue] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFijos_Cue_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryFijos_Cue_Cr] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFijos_CueDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryFijos_CueDso] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFijosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryFijosLta] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNotasBuses]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryNotasBuses] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNotasBusesCR_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryNotasBusesCR_Cr] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNotasBusesDb_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryNotasBusesDb_Cr] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNotasBusesLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQryNotasBusesLta] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_Cargos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQrytm_Cargos] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_Cargos_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paQrytm_Cargos_Cr] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpCausacion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paUpCausacion] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpCausacVence]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paUpCausacVence] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpFijos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paUpFijos] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpFijos_Cue]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paUpFijos_Cue] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNotasBuses]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[paUpNotasBuses] GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryNotasBusesDb_Cr] @pmTipDoc VARCHAR(3), @pmNumNotaIni INT,@pmNumNotaFin INT, @pmIdCia CHAR(2) AS SELECT N.TipDoc AS TipoNota,TipoDoc,NumNota, N.IdCia AS CodCia,Compania, Fecha, N.FecVence AS FecVenceNota,N.IdConcepto AS IdConcept,C.Concepto AS Concept, N.IdCliente AS NitPropietario,NP.RazonSocial AS Propietario, N.IdAgencia,N.IdVehiculo AS PlacaVeh, N.IdConductor AS CedConductor,NC.RazonSocial AS Conductor , N.VehPropio AS VehEspropio, ValorTotal, ValorIva, ValorReteFte, ValorReteIca, ValorCosto, ValorSancion, ValorNeto, ValorAplicado, N.IdVendedor, N.Comision, IdTarCom, MulPlazos, N.IdPlazo AS IdPlzo,Plazo,DiasPago,NVmto, PrestCaja, IntMora,NumCheque, N.IdBanco AS CodBanco,Banco, N.IdCCosto AS IdCCost,CCosto,N.Referencia AS RefNota ,TipDcm, N.Documento AS NDocumnt, IdCiaDoc, BaseIva, BaseRet, TarifaIva, TarifaRet, IdTarIva, IdTarRet, N.TipCom AS IdTipCom,TM.TipoCom AS TipoComprobante, Comprobante, IdCiaCom, N.Observacion AS Observ, N.IdEstado AS IdEstadDoc,ED.Estado AS EstadDoc,N.IdConcFE, TimeSys, FecUpdate, IdCiaCrea,N.IdUsuario AS IdUsuari,Usuario,Leyenda ,Item,VF.FecVence AS FecVenceFac,ValorFactura,ValorAbono,VF.IdCargo AS IdCarg,F.Concepto AS NomCargo,VF.IdClase AS CodClase,ClaseCuenta,FecUltPago,Factura,VF.Referencia AS RefVce ,Detalle,TipRec,Recibo,IdCiaRec,ValorAtcpo --Datos del vehículo ,NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,Modelo,FecRep,ClaseMat,V.IdCom AS CdComb,TipoComb,NumMotor,SerieChasis,PasjerosPie,PasjerosSen ,V.IdPropietario AS VehNitProp,VNP.RazonSocial AS VehPropietario,V.IdConductor AS VehCedConductor,VNC.RazonSocial AS VehConductor,V.IdPpd AS CdTipProp,TipoProp,TipoAfil,NContrato,V.IdAdmon AS CdAdmon,TipoAdmon ,V.IdNiv AS CdNivel,NivelServicio,V.IdGrupo AS CdGrupo,GrupoProp,CdTarifa,TB.Descripcion AS TipoTarifa,FecIngreso,FecVigencia,FecRetiro,NumSoat,FecSoat,VigSoat,KmActual,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,ObligaTProd,V.IdEstado AS VehCdEstado ,EV.Estado AS VehEstado,V.Inactivo AS VehInactivo --datos del propietario ,NP.Codigo AS PropCodigo,NP.TipoId AS PropTipoId,NP.Dv AS PropDv,NP.Direccion AS PropDireccion,NP.IdLocal AS PropCdCiudad,LP.Localidad AS PropCiudad,LP.IdDep AS PropCdDep ,DP.Departamento AS PropDpato,NP.Telefono AS PropTelefono,NP.Fax AS PropFax,NP.TelMovil AS PropTelMovil --datos de conductor ,NC.Codigo AS CondCodigo,NC.Direccion AS CondDireccion,NC.IdLocal AS CondCdCiudad,LC.Localidad AS CondCiudad,LC.IdDep AS CondCdDpto,DC.Departamento AS CondDpto ,NC.Telefono AS CondTelefono,NC.Fax AS CondFax,NC.TelMovil AS CondTelMovil,NC.e_mail AS CondEmail,NC.EsPropietario AS CondEspropietario,NC.IdLugarCed AS CondCdLugarCed,LCE.Localidad AS CondLugarCed,NC.FecExpCed AS CondFechaExpCedula ,CT.IdModd AS CondCdMod,MCD.Modalidad AS ModConductor,TipoSangre,FactorRh,FecNacmto,LugarNacmto,Sexo,CertJudicial,VigJudicial,Licencia,CatLicencia,VigLicencia,CT.IdLugar AS CdLugarLic,LugarLic FROM Trn_NotasBuses AS N INNER JOIN Trn_CausacVence AS VF ON N.TipDoc = VF.TipDoc AND N.NumNota= VF.Documento AND N.IdCia =VF.IdCia INNER JOIN Sys_TiposDoc AS TD ON N.TipDoc=TD.IdDoc INNER JOIN Companias AS CI ON N.IdCia=CI.IdCia INNER JOIN Conceptos AS C ON N.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON N.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario INNER JOIN Plazos AS PZ ON N.IdPlazo=PZ.IdPlazo INNER JOIN Vehiculos AS V ON N.IdVehiculo=V.IdVehiculo INNER JOIN Terceros AS NP ON N.IdCliente=NP.IdTercero INNER JOIN Terceros AS NC ON N.IdConductor=NC.IdTercero INNER JOIN CentroCosto AS CC ON N.IdCCosto=CC.IdCCosto INNER JOIN Bancos AS B ON N.IdBanco=B.IdBanco INNER JOIN Fijos AS F ON VF.IdCargo=F.IdCargo INNER JOIN BusClaseCue AS CU ON VF.IdClase=CU.IdClase INNER JOIN Localidades AS LP ON NP.IdLocal=LP.IdLocal INNER JOIN Departamentos AS DP ON LP.IdDep=DP.IdDep INNER JOIN Localidades AS LC ON NC.IdLocal=LC.IdLocal INNER JOIN Departamentos AS DC ON LC.IdDep=DC.IdDep INNER JOIN TiposCol AS CO ON V.IdColor=CO.IdColor INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca INNER JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo INNER JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado INNER JOIN Terceros AS VNP ON V.IdPropietario=VNP.IdTercero INNER JOIN Terceros AS VNC ON V.IdConductor=VNC.IdTercero LEFT JOIN TiposFuel AS TC ON V.IdCom=TC.IdCom LEFT JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd LEFT JOIN TiposAdm AS TA ON V.IdAdmon=TA.IdAdmon LEFT JOIN TiposNivs AS VNS ON V.IdNiv=VNS.IdNiv LEFT JOIN TarifBuses AS TB ON V.CdTarifa =TB.IdTarifa LEFT JOIN TercCndtores AS CT ON N.IdConductor=CT.IdConductor LEFT JOIN Localidades AS LCE ON NC.IdLugarCed=LCE.IdLocal LEFT JOIN TiposMod AS MCD ON CT.IdModd=MCD.IdModd LEFT JOIN ExpLicencias AS ELC ON CT.IdLugar=ELC.IdLugar LEFT JOIN TiposCom AS TM ON N.TipCom=TM.IdCom WHERE N.TipDoc=@pmTipDoc AND NumNota BETWEEN @pmNumNotaIni AND @pmNumNotaFin AND N.IdCia=@pmIdCia ORDER BY N.IdCia,NumNota,Item GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryNotasBusesCR_Cr] @pmTipDoc VARCHAR(3), @pmNumNotaIni INT,@pmNumNotaFin INT, @pmIdCia CHAR(2) AS SELECT N.TipDoc AS TipoNota,TipoDoc,NumNota, N.IdCia AS CodCia,Compania, N.Fecha AS FechaNota,N.IdConcepto AS IdConcept,C.Concepto AS Concept, N.IdCliente AS NitPropietario,NP.RazonSocial AS Propietario, N.IdAgencia,N.IdVehiculo AS PlacaVeh, N.IdConductor AS CedConductor,NC.RazonSocial AS Conductor , N.VehPropio AS VehEspropio, ValorTotal, ValorIva, ValorReteFte, ValorReteIca, ValorCosto, ValorSancion, ValorNeto, ValorAplicado, N.IdVendedor, N.Comision, IdTarCom,PrestCaja, IntMora,NumCheque,N.IdBanco, N.IdCCosto AS IdCCost,CCosto,N.Referencia AS RefNota ,TipDcm, N.Documento AS NDocumnt, N.IdCiaDoc AS CiaDocumnt, BaseIva, BaseRet, TarifaIva, TarifaRet, IdTarIva, IdTarRet, N.TipCom AS IdTipCom,TM.TipoCom AS TipoComprobante, Comprobante, IdCiaCom,N.IdConcFE, N.Observacion AS Observ, N.IdEstado AS IdEstadDoc,ED.Estado AS EstadDoc, N.TimeSys, FecUpdate, IdCiaCrea,N.IdUsuario AS IdUsuari,Usuario,Leyenda ,Item,RF.TipDoc AS TipoDocApl,RF.Documento AS NCausacionApl,RF.IdCiaDoc AS CiaDocApl,ItemDoc,RF.IdCargo AS CodCargo,F.Concepto AS Cargo,RF.IdClase AS CodClase,ClaseCuenta,ValorAbono,Detalle,FecPago --Datos del vehículo ,NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,Modelo,FecRep,ClaseMat,V.IdCom AS CdComb,TipoComb,NumMotor,SerieChasis,PasjerosPie,PasjerosSen ,V.IdPropietario AS VehNitProp,VNP.RazonSocial AS VehPropietario,V.IdConductor AS VehCedConductor,VNC.RazonSocial AS VehConductor,V.IdPpd AS CdTipProp,TipoProp,TipoAfil,NContrato,V.IdAdmon AS CdAdmon,TipoAdmon ,V.IdNiv AS CdNivel,NivelServicio,V.IdGrupo AS CdGrupo,GrupoProp,CdTarifa,TB.Descripcion AS TipoTarifa,FecIngreso,FecVigencia,FecRetiro,NumSoat,FecSoat,VigSoat,KmActual,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,ObligaTProd,V.IdEstado AS VehCdEstado ,EV.Estado AS VehEstado,V.Inactivo AS VehInactivo --datos del propietario ,NP.Codigo AS PropCodigo,NP.TipoId AS PropTipoId,NP.Dv AS PropDv,NP.Direccion AS PropDireccion,NP.IdLocal AS PropCdCiudad,LP.Localidad AS PropCiudad,LP.IdDep AS PropCdDep ,DP.Departamento AS PropDpato,NP.Telefono AS PropTelefono,NP.Fax AS PropFax,NP.TelMovil AS PropTelMovil --datos de conductor ,NC.Codigo AS CondCodigo,NC.Direccion AS CondDireccion,NC.IdLocal AS CondCdCiudad,LC.Localidad AS CondCiudad,LC.IdDep AS CondCdDpto,DC.Departamento AS CondDpto ,NC.Telefono AS CondTelefono,NC.Fax AS CondFax,NC.TelMovil AS CondTelMovil,NC.e_mail AS CondEmail,NC.EsPropietario AS CondEspropietario,NC.IdLugarCed AS CondCdLugarCed,LCE.Localidad AS CondLugarCed,NC.FecExpCed AS CondFechaExpCedula ,CT.IdModd AS CondCdMod,MCD.Modalidad AS ModConductor,TipoSangre,FactorRh,FecNacmto,LugarNacmto,Sexo,CertJudicial,VigJudicial,Licencia,CatLicencia,VigLicencia,CT.IdLugar AS CdLugarLic,LugarLic FROM Trn_NotasBuses AS N INNER JOIN Trn_RecFactura AS RF ON N.TipDoc=RF.TipRec AND N.NumNota=RF.Recibo AND N.IdCia=RF.IdCia INNER JOIN Sys_TiposDoc AS TD ON N.TipDoc=TD.IdDoc INNER JOIN Companias AS CI ON N.IdCia=CI.IdCia INNER JOIN Conceptos AS C ON N.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON N.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario INNER JOIN Vehiculos AS V ON N.IdVehiculo=V.IdVehiculo INNER JOIN Terceros AS NP ON N.IdCliente=NP.IdTercero INNER JOIN Terceros AS NC ON N.IdConductor=NC.IdTercero INNER JOIN CentroCosto AS CC ON N.IdCCosto=CC.IdCCosto INNER JOIN Fijos AS F ON RF.IdCargo=F.IdCargo INNER JOIN BusClaseCue AS CU ON RF.IdClase=CU.IdClase INNER JOIN Localidades AS LP ON NP.IdLocal=LP.IdLocal INNER JOIN Departamentos AS DP ON LP.IdDep=DP.IdDep INNER JOIN Localidades AS LC ON NC.IdLocal=LC.IdLocal INNER JOIN Departamentos AS DC ON LC.IdDep=DC.IdDep INNER JOIN TiposCol AS CO ON V.IdColor=CO.IdColor INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca INNER JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo INNER JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado INNER JOIN Terceros AS VNP ON V.IdPropietario=VNP.IdTercero INNER JOIN Terceros AS VNC ON V.IdConductor=VNC.IdTercero LEFT JOIN TiposFuel AS TC ON V.IdCom=TC.IdCom LEFT JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd LEFT JOIN TiposAdm AS TA ON V.IdAdmon=TA.IdAdmon LEFT JOIN TiposNivs AS VNS ON V.IdNiv=VNS.IdNiv LEFT JOIN TarifBuses AS TB ON V.CdTarifa =TB.IdTarifa LEFT JOIN TercCndtores AS CT ON N.IdConductor=CT.IdConductor LEFT JOIN Localidades AS LCE ON NC.IdLugarCed=LCE.IdLocal LEFT JOIN TiposMod AS MCD ON CT.IdModd=MCD.IdModd LEFT JOIN ExpLicencias AS ELC ON CT.IdLugar=ELC.IdLugar LEFT JOIN TiposCom AS TM ON N.TipCom=TM.IdCom WHERE N.TipDoc=@pmTipDoc AND NumNota BETWEEN @pmNumNotaIni AND @pmNumNotaFin AND N.IdCia=@pmIdCia ORDER BY N.IdCia,NumNota,Item GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryCausacion_Crr] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME ,@pmIdCia CHAR(2)=Null,@pmModalidad VARCHAR(10)=Null ,@pmIdConcepto VARCHAR(4)=Null,@pmIdProveedor VARCHAR(16)=Null, @pmIdVehiculo VARCHAR(10)=Null,@pmIdPropietario VARCHAR(16)=Null,@pmCxPagar BIT=Null,@pmAnulado BIT=Null ,@pmIdEstado VARCHAR(4)=Null,@pmIdGrupo VARCHAR(4)=Null,@pmIdAdmon VARCHAR(4)=Null AS SELECT TipDoc,Documento,C.IdCia AS CodCia,Compania,Fecha,Factura,FecFactura,C.IdPlazo AS CodPlazo,Plazo,DiasPago,NVmto,MulPlazos,FecVence,C.Modalidad AS ModCausacion,C.IdProveedor AS NitProveed, NV.RazonSocial AS Proveedor ,C.IdVehiculo AS PlacaVeh,C.VehPropio AS VhPropio,C.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,C.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,ValorTotal, ValorDcto, ValorIva, ValorReteFte, ValorReteIva, ValorReteIca, ValorOtros, ValorFletes, ValorNeto,ValorCCobrar , C.IdConcepto AS CodConcepto,Concepto,CxPagar,CxcVarios, BaseIva, BaseRet, TarifaIva,TarifaRet, TarifaIca,Anulado, NumDev, FecDev,TipCom,TipoCom,Comprobante,IdCiaCom, C.Observacion AS Observ,Referencia, C.IdEstado AS IdEstadDoc ,ED.Estado AS EstadDoc,IndFactura, IdCiaCrea, C.IdUsuario AS IdUsuari,Usuario,TipEgr, Egreso, IdCiaEgr,TipoDoc,TimeSys, FecUpdate --Datos del vehículo ,NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,Modelo,FecRep,ClaseMat,V.IdCom AS CdComb,TipoComb,NumMotor,SerieChasis,PasjerosPie,PasjerosSen ,V.IdPropietario AS VehNitProp,VNP.RazonSocial AS VehPropietario,V.IdConductor AS VehCedConductor,VNC.RazonSocial AS VehConductor,V.IdPpd AS CdTipProp,TipoProp,TipoAfil,NContrato,V.IdAdmon AS CdAdmon,TipoAdmon ,V.IdNiv AS CdNivel,NivelServicio,V.IdGrupo AS CdGrupo,GrupoProp,CdTarifa,TB.Descripcion AS TipoTarifa,FecIngreso,FecVigencia,FecRetiro,NumSoat,FecSoat,VigSoat,KmActual,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,ObligaTProd,V.IdEstado AS VehCdEstado ,EV.Estado AS VehEstado,V.Inactivo AS VehInactivo --datos del propietario ,NP.Codigo AS PropCodigo,NP.TipoId AS PropTipoId,NP.Dv AS PropDv,NP.Direccion AS PropDireccion,NP.IdLocal AS PropCdCiudad,LP.Localidad AS PropCiudad,LP.IdDep AS PropCdDep ,DP.Departamento AS PropDpato,NP.Telefono AS PropTelefono,NP.Fax AS PropFax,NP.TelMovil AS PropTelMovil --datos de conductor ,NC.Codigo AS CondCodigo,NC.Direccion AS CondDireccion,NC.IdLocal AS CondCdCiudad,LC.Localidad AS CondCiudad,LC.IdDep AS CondCdDpto,DC.Departamento AS CondDpto ,NC.Telefono AS CondTelefono,NC.Fax AS CondFax,NC.TelMovil AS CondTelMovil,NC.e_mail AS CondEmail,NC.EsPropietario AS CondEspropietario,NC.IdLugarCed AS CondCdLugarCed,LCE.Localidad AS CondLugarCed,NC.FecExpCed AS CondFechaExpCedula ,CT.IdModd AS CondCdMod,MCD.Modalidad AS ModConductor,TipoSangre,FactorRh,FecNacmto,LugarNacmto,Sexo,CertJudicial,VigJudicial,Licencia,CatLicencia,VigLicencia,CT.IdLugar AS CdLugarLic,LugarLic FROM Trn_Causacion AS C INNER JOIN Sys_TiposDoc AS TD ON C.TipDoc=TD.IdDoc INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia INNER JOIN Conceptos AS CC ON C.IdConcepto=CC.IdConcepto INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario INNER JOIN Plazos AS PZ ON C.IdPlazo=PZ.IdPlazo INNER JOIN Terceros AS NV ON C.IdProveedor=NV.IdTercero INNER JOIN Vehiculos AS V ON C.IdVehiculo=V.IdVehiculo INNER JOIN Terceros AS NP ON C.IdPropietario=NP.IdTercero INNER JOIN Terceros AS NC ON C.IdConductor=NC.IdTercero INNER JOIN Localidades AS LP ON NP.IdLocal=LP.IdLocal INNER JOIN Departamentos AS DP ON LP.IdDep=DP.IdDep INNER JOIN Localidades AS LC ON NC.IdLocal=LC.IdLocal INNER JOIN Departamentos AS DC ON LC.IdDep=DC.IdDep INNER JOIN TiposCol AS CO ON V.IdColor=CO.IdColor INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca INNER JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo INNER JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado INNER JOIN Terceros AS VNP ON V.IdPropietario=VNP.IdTercero INNER JOIN Terceros AS VNC ON V.IdConductor=VNC.IdTercero LEFT JOIN TiposFuel AS TC ON V.IdCom=TC.IdCom LEFT JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd LEFT JOIN TiposAdm AS TA ON V.IdAdmon=TA.IdAdmon LEFT JOIN TiposNivs AS VNS ON V.IdNiv=VNS.IdNiv LEFT JOIN TarifBuses AS TB ON V.CdTarifa =TB.IdTarifa LEFT JOIN TiposCom AS TM ON C.TipCom=TM.IdCom LEFT JOIN TercCndtores AS CT ON C.IdConductor=CT.IdConductor LEFT JOIN Localidades AS LCE ON NC.IdLugarCed=LCE.IdLocal LEFT JOIN TiposMod AS MCD ON CT.IdModd=MCD.IdModd LEFT JOIN ExpLicencias AS ELC ON CT.IdLugar=ELC.IdLugar WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND TipDoc LIKE ISNULL(@pmTipDoc,'%') AND C.IdCia LIKE ISNULL(@pmIdCia,'%%') AND C.Modalidad LIKE ISNULL(@pmModalidad,'%') AND C.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND C.IdProveedor LIKE ISNULL(@pmIdProveedor,'%') AND C.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND C.IdPropietario LIKE ISNULL(@pmIdPropietario,'%') AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND V.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND V.IdAdmon LIKE ISNULL(@pmIdAdmon,'%') AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1)) AND (CxPagar=ISNULL(@pmCxPagar,0) or CxPagar=ISNULL(@pmCxPagar,1)) ORDER BY TipDoc,C.IdCia,Documento GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryCausacion_Cr] @pmTipDoc VARCHAR(3),@pmDocumentoIni INT,@pmDocumentoFin INT,@pmIdCia CHAR(2) AS SELECT C.TipDoc AS CodTipDoc,TipoDoc, C.Documento AS nDocumento, C.IdCia AS CodCia,Compania, Fecha, C.IdConcepto AS CodConcepto,CC.Concepto AS Concept, C.Factura AS NumFactura, C.IdProveedor AS NitProveedor,NV.RazonSocial AS Proveedor ,FecFactura, C.FecVence AS FechaVence, C.Referencia AS Referenc, C.Modalidad AS ModCausac ,C.IdVehiculo AS PlacaVeh,C.IdPropietario AS NitPropietario, C.IdConductor AS CedConductor, C.VehPropio AS VehProp, ValorTotal, ValorDcto, ValorIva, ValorReteFte, ValorReteIva, ValorReteIca, ValorOtros, ValorFletes, ValorNeto,ValorCCobrar , BaseIva, BaseRet, TarifaIva,TarifaRet, C.TarifaIca AS PorceIca, IdTarIva, IdTarRet, IdTarIca, MulPlazos, C.IdPlazo AS CodPlazo,Plazo,DiasPago,NVmto, CxPagar,CxcVarios, TipEgr, Egreso, IdCiaEgr, Anulado, NumDev, FecDev,TipCom,TM.TipoCom AS TipoComprobante ,Comprobante, IdCiaCom, C.Observacion AS Observ, C.IdEstado AS IdEstadDoc,ED.Estado AS EstadDoc,IndFactura,TimeSys, FecUpdate, IdCiaCrea, C.IdUsuario AS IdUsuari,Usuario,Leyenda --detalles ,Item,VF.FecVence AS FecVenceFac,ValorFactura,ValorAbono,IdCliente,IdAgencia,VF.IdVehiculo AS PlacaVehvce,IdVendedor,VF.IdCargo AS IdCarg,F.Concepto AS NomCargo,VF.IdClase AS CodClase ,ClaseCuenta,FecUltPago,VF.Factura AS NFacVce,VF.Referencia AS RefVce,Detalle,TipRec,Recibo,IdCiaRec,ValorAtcpo,IdCuenta,CierreCte,VF.BaseImpu,VF.Tarifa_Iva,VF.VrIva,VF.Tarifa_Ret,VF.VrRetFte,VF.Tarifa_Ica,VF.VrRetIca,VF.TipDocFac,VF.NumDocFac,VF.CdCiaFac --Datos del vehículo ,NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,Modelo,FecRep,ClaseMat,V.IdCom AS CdComb,TipoComb,NumMotor,SerieChasis,PasjerosPie,PasjerosSen ,V.IdPropietario AS VehNitProp,VNP.RazonSocial AS VehPropietario,V.IdConductor AS VehCedConductor,VNC.RazonSocial AS VehConductor,V.IdPpd AS CdTipProp,TipoProp,TipoAfil,NContrato,V.IdAdmon AS CdAdmon,TipoAdmon ,V.IdNiv AS CdNivel,NivelServicio,V.IdGrupo AS CdGrupo,GrupoProp,CdTarifa,TB.Descripcion AS TipoTarifa,FecIngreso,FecVigencia,FecRetiro,NumSoat,FecSoat,VigSoat,KmActual,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,ObligaTProd,V.IdEstado AS VehCdEstado ,EV.Estado AS VehEstado,V.Inactivo AS VehInactivo --datos del propietario ,NP.RazonSocial AS Propietario,NP.Codigo AS PropCodigo,NP.TipoId AS PropTipoId,NP.Dv AS PropDv,NP.Direccion AS PropDireccion,NP.IdLocal AS PropCdCiudad,LP.Localidad AS PropCiudad,LP.IdDep AS PropCdDep ,DP.Departamento AS PropDpato,NP.Telefono AS PropTelefono,NP.Fax AS PropFax,NP.TelMovil AS PropTelMovil --datos de conductor ,NC.RazonSocial AS Conductor,NC.Codigo AS CondCodigo,NC.Direccion AS CondDireccion,NC.IdLocal AS CondCdCiudad,LC.Localidad AS CondCiudad,LC.IdDep AS CondCdDpto,DC.Departamento AS CondDpto ,NC.Telefono AS CondTelefono,NC.Fax AS CondFax,NC.TelMovil AS CondTelMovil,NC.e_mail AS CondEmail,NC.EsPropietario AS CondEspropietario,NC.IdLugarCed AS CondCdLugarCed,LCE.Localidad AS CondLugarCed,NC.FecExpCed AS CondFechaExpCedula ,CT.IdModd AS CondCdMod,MCD.Modalidad AS ModConductor,TipoSangre,FactorRh,FecNacmto,LugarNacmto,Sexo,CertJudicial,VigJudicial,Licencia,CatLicencia,VigLicencia,CT.IdLugar AS CdLugarLic,LugarLic FROM Trn_Causacion AS C INNER JOIN Trn_CausacVence AS VF ON C.TipDoc = VF.TipDoc AND C.Documento = VF.Documento AND C.IdCia =VF.IdCia INNER JOIN Sys_TiposDoc AS TD ON C.TipDoc=TD.IdDoc INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia INNER JOIN Conceptos AS CC ON C.IdConcepto=CC.IdConcepto INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario INNER JOIN Plazos AS PZ ON C.IdPlazo=PZ.IdPlazo INNER JOIN Terceros AS NV ON C.IdProveedor=NV.IdTercero INNER JOIN Terceros AS NP ON VF.IdCliente=NP.IdTercero INNER JOIN Localidades AS LP ON NP.IdLocal=LP.IdLocal INNER JOIN Departamentos AS DP ON LP.IdDep=DP.IdDep INNER JOIN Terceros AS NC ON VF.IdVendedor=NC.IdTercero INNER JOIN Localidades AS LC ON NC.IdLocal=LC.IdLocal INNER JOIN Departamentos AS DC ON LC.IdDep=DC.IdDep INNER JOIN Fijos AS F ON VF.IdCargo=F.IdCargo INNER JOIN BusClaseCue AS CU ON VF.IdClase=CU.IdClase INNER JOIN Vehiculos AS V ON VF.IdVehiculo=V.IdVehiculo INNER JOIN TiposCol AS CO ON V.IdColor=CO.IdColor INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca INNER JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo INNER JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado INNER JOIN Terceros AS VNP ON V.IdPropietario=VNP.IdTercero INNER JOIN Terceros AS VNC ON V.IdConductor=VNC.IdTercero LEFT JOIN TiposFuel AS TC ON V.IdCom=TC.IdCom LEFT JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd LEFT JOIN TiposAdm AS TA ON V.IdAdmon=TA.IdAdmon LEFT JOIN TiposNivs AS VNS ON V.IdNiv=VNS.IdNiv LEFT JOIN TarifBuses AS TB ON V.CdTarifa =TB.IdTarifa LEFT JOIN TiposCom AS TM ON C.TipCom=TM.IdCom LEFT JOIN TercCndtores AS CT ON VF.IdVendedor=CT.IdConductor LEFT JOIN Localidades AS LCE ON NC.IdLugarCed=LCE.IdLocal LEFT JOIN TiposMod AS MCD ON CT.IdModd=MCD.IdModd LEFT JOIN ExpLicencias AS ELC ON CT.IdLugar=ELC.IdLugar WHERE C.TipDoc=@pmTipDoc AND C.Documento BETWEEN @pmDocumentoIni AND @pmDocumentoFin AND C.IdCia=@pmIdCia ORDER BY C.IdCia,C.Documento GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryCausacVence_Cr] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmSaldo DECIMAL(14,2),@pmTipDoc VARCHAR(3)=Null,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null ,@pmIdClase VARCHAR(4)=Null,@pmIdCargo VARCHAR(4)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdGrupo VARCHAR(4)=Null,@pmIdVendedor VARCHAR(16)=Null,@pmIdAdmon VARCHAR(4)=Null AS SELECT IdCliente,NP.RazonSocial AS Propietario,VC.IdVehiculo AS PlacaVeh,TipDoc,TipoDoc,Documento,VC.IdCia AS CodCia,Item, FecEmision, FecVence, ValorFactura, ValorAbono, ValorFactura-ValorAbono AS TotalSaldo ,VC.IdClase AS IdClas,ClaseCuenta,VC.IdCargo AS IdCarg,F.Concepto AS ConceptoCargo,Factura,Referencia,Detalle,VC.IdConcepto AS IdConcep,C.Concepto AS ConcepDoc,IdVendedor AS CedConductor ,NC.RazonSocial AS Conductor,Compania,FecUltPago,NotaInt,FecUltLiquida,TipRec, Recibo, IdCiaRec,ValorAtcpo,IdCuenta,VC.VehPropio AS VhPropio,CombCant,CombValor,BaseImpu,Tarifa_Iva,VrIva,Tarifa_Ret,VrRetFte,Tarifa_Ica,VrRetIca ,CodTarifIva,CodTarifRet,CodTarifIca,TipDocFac,NumDocFac,CdCiaFac --Datos del vehículo ,NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,Modelo,FecRep,ClaseMat,V.IdCom AS CdComb,TipoComb,NumMotor,SerieChasis,PasjerosPie,PasjerosSen ,V.IdPropietario AS VehNitProp,VNP.RazonSocial AS VehPropietario,V.IdConductor AS VehCedConductor,VNC.RazonSocial AS VehConductor,V.IdPpd AS CdTipProp,TipoProp,TipoAfil,NContrato,V.IdAdmon AS CdAdmon,TipoAdmon ,V.IdNiv AS CdNivel,NivelServicio,V.IdGrupo AS CdGrupo,GrupoProp,CdTarifa,TB.Descripcion AS TipoTarifa,FecIngreso,FecVigencia,FecRetiro,NumSoat,FecSoat,VigSoat,KmActual,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,ObligaTProd,V.IdEstado AS VehCdEstado ,EV.Estado AS VehEstado,V.Inactivo AS VehInactivo --datos de conductor ,NC.Codigo AS CondCodigo,NC.Direccion AS CondDireccion,NC.IdLocal AS CondCdCiudad,LC.Localidad AS CondCiudad,LC.IdDep AS CondCdDpto,DC.Departamento AS CondDpto ,NC.Telefono AS CondTelefono,NC.Fax AS CondFax,NC.TelMovil AS CondTelMovil,NC.e_mail AS CondEmail,NC.EsPropietario AS CondEspropietario,NC.IdLugarCed AS CondCdLugarCed,LCE.Localidad AS CondLugarCed,NC.FecExpCed AS CondFechaExpCedula ,CT.IdModd AS CondCdMod,MCD.Modalidad AS ModConductor,TipoSangre,FactorRh,FecNacmto,LugarNacmto,Sexo,CertJudicial,VigJudicial,Licencia,CatLicencia,VigLicencia,CT.IdLugar AS CdLugarLic,LugarLic FROM Trn_CausacVence AS VC INNER JOIN Terceros AS NP ON VC.IdCliente=NP.IdTercero INNER JOIN Vehiculos AS V ON VC.IdVehiculo=V.IdVehiculo INNER JOIN Sys_TiposDoc AS TD ON VC.TipDoc=TD.IdDoc INNER JOIN Companias AS CI ON VC.IdCia=CI.IdCia INNER JOIN BusClaseCue AS CU ON VC.IdClase=CU.IdClase INNER JOIN Fijos AS F ON VC.IdCargo=F.IdCargo INNER JOIN Conceptos AS C ON VC.IdConcepto=C.IdConcepto INNER JOIN Terceros AS NC ON VC.IdVendedor=NC.IdTercero INNER JOIN Localidades AS LC ON NC.IdLocal=LC.IdLocal INNER JOIN Departamentos AS DC ON LC.IdDep=DC.IdDep INNER JOIN TiposCol AS CO ON V.IdColor=CO.IdColor INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca INNER JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo INNER JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado INNER JOIN Terceros AS VNP ON V.IdPropietario=VNP.IdTercero INNER JOIN Terceros AS VNC ON V.IdConductor=VNC.IdTercero LEFT JOIN TiposFuel AS TC ON V.IdCom=TC.IdCom LEFT JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd LEFT JOIN TiposAdm AS TA ON V.IdAdmon=TA.IdAdmon LEFT JOIN TiposNivs AS VNS ON V.IdNiv=VNS.IdNiv LEFT JOIN TarifBuses AS TB ON V.CdTarifa =TB.IdTarifa LEFT JOIN TercCndtores AS CT ON VC.IdVendedor=CT.IdConductor LEFT JOIN Localidades AS LCE ON NC.IdLugarCed=LCE.IdLocal LEFT JOIN TiposMod AS MCD ON CT.IdModd=MCD.IdModd LEFT JOIN ExpLicencias AS ELC ON CT.IdLugar=ELC.IdLugar WHERE FecVence BETWEEN @pmFechaIni AND @pmFechaFin AND (ValorFactura-ValorAbono)>@pmSaldo AND TipDoc LIKE ISNULL(@pmTipDoc,'%') AND VC.IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND VC.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND VC.IdClase LIKE ISNULL(@pmIdClase,'%') AND VC.IdCargo LIKE ISNULL(@pmIdCargo,'%') AND VC.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND V.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND IdVendedor LIKE ISNULL(@pmIdVendedor,'%') AND V.IdAdmon LIKE ISNULL(@pmIdAdmon,'%') ORDER BY NP.RazonSocial,VC.IdVehiculo,TipDoc,VC.IdCia,Documento,Item GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryCausacVence_Cra] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmSaldo DECIMAL(14,2),@pmTipDoc VARCHAR(3)=Null,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null ,@pmIdClase VARCHAR(4)=Null,@pmIdCargo VARCHAR(4)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdGrupo VARCHAR(4)=Null,@pmIdVendedor VARCHAR(16)=Null,@pmIdAdmon VARCHAR(4)=Null AS SELECT VC.IdCliente AS NitCliente,NP.RazonSocial AS Propietario,VC.IdVehiculo AS PlacaVeh,VC.TipDoc AS CdTipDoc,TipoDoc,VC.Documento AS NumDoc,VC.IdCia AS CodCia,VC.Item AS NumItem, FecEmision, FecVence, ValorFactura, VC.ValorAbono AS TotalAbonos, ValorFactura-VC.ValorAbono AS TotalSaldo ,VC.IdClase AS IdClas,ClaseCuenta,VC.IdCargo AS IdCarg,F.Concepto AS ConceptoCargo,Factura,Referencia,VC.Detalle AS DetalleVence,VC.IdConcepto AS IdConcep,C.Concepto AS ConcepDoc,VC.IdVendedor AS CedConductor ,NC.RazonSocial AS Conductor,Compania,FecUltPago,NotaInt,FecUltLiquida,ValorAtcpo,IdCuenta,VC.VehPropio AS VhPropio,CombCant,CombValor,BaseImpu,Tarifa_Iva,VrIva,Tarifa_Ret,VrRetFte,Tarifa_Ica,VrRetIca,CodTarifIva,CodTarifRet,CodTarifIca,TipDocFac,NumDocFac,CdCiaFac ,R.TipRec AS TipDocApl, R.Recibo AS NumRecibo, R.IdCia AS IdCiaApl, R.Item AS ItmApl,Fecha,R.ValorAbono AS VrAbonado,Devolucion, R.Detalle AS DetalleApl,FecPago --Datos del vehículo ,NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,Modelo,FecRep,ClaseMat,V.IdCom AS CdComb,TipoComb,NumMotor,SerieChasis,PasjerosPie,PasjerosSen ,V.IdPropietario AS VehNitProp,VNP.RazonSocial AS VehPropietario,V.IdConductor AS VehCedConductor,VNC.RazonSocial AS VehConductor,V.IdPpd AS CdTipProp,TipoProp,TipoAfil,NContrato,V.IdAdmon AS CdAdmon,TipoAdmon ,V.IdNiv AS CdNivel,NivelServicio,V.IdGrupo AS CdGrupo,GrupoProp,CdTarifa,TB.Descripcion AS TipoTarifa,FecIngreso,FecVigencia,FecRetiro,NumSoat,FecSoat,VigSoat,KmActual,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,ObligaTProd,V.IdEstado AS VehCdEstado ,EV.Estado AS VehEstado,V.Inactivo AS VehInactivo --datos de conductor ,NC.Codigo AS CondCodigo,NC.Direccion AS CondDireccion,NC.IdLocal AS CondCdCiudad,LC.Localidad AS CondCiudad,LC.IdDep AS CondCdDpto,DC.Departamento AS CondDpto ,NC.Telefono AS CondTelefono,NC.Fax AS CondFax,NC.TelMovil AS CondTelMovil,NC.e_mail AS CondEmail,NC.EsPropietario AS CondEspropietario,NC.IdLugarCed AS CondCdLugarCed,LCE.Localidad AS CondLugarCed,NC.FecExpCed AS CondFechaExpCedula ,CT.IdModd AS CondCdMod,MCD.Modalidad AS ModConductor,TipoSangre,FactorRh,FecNacmto,LugarNacmto,Sexo,CertJudicial,VigJudicial,Licencia,CatLicencia,VigLicencia,CT.IdLugar AS CdLugarLic,LugarLic FROM Trn_CausacVence AS VC INNER JOIN Terceros AS NP ON VC.IdCliente=NP.IdTercero INNER JOIN Vehiculos AS V ON VC.IdVehiculo=V.IdVehiculo INNER JOIN Sys_TiposDoc AS TD ON VC.TipDoc=TD.IdDoc INNER JOIN Companias AS CI ON VC.IdCia=CI.IdCia INNER JOIN BusClaseCue AS CU ON VC.IdClase=CU.IdClase INNER JOIN Fijos AS F ON VC.IdCargo=F.IdCargo INNER JOIN Conceptos AS C ON VC.IdConcepto=C.IdConcepto INNER JOIN Terceros AS NC ON VC.IdVendedor=NC.IdTercero INNER JOIN Localidades AS LC ON NC.IdLocal=LC.IdLocal INNER JOIN Departamentos AS DC ON LC.IdDep=DC.IdDep INNER JOIN TiposCol AS CO ON V.IdColor=CO.IdColor INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca INNER JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo INNER JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado INNER JOIN Terceros AS VNP ON V.IdPropietario=VNP.IdTercero INNER JOIN Terceros AS VNC ON V.IdConductor=VNC.IdTercero LEFT JOIN TiposFuel AS TC ON V.IdCom=TC.IdCom LEFT JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd LEFT JOIN TiposAdm AS TA ON V.IdAdmon=TA.IdAdmon LEFT JOIN TiposNivs AS VNS ON V.IdNiv=VNS.IdNiv LEFT JOIN TarifBuses AS TB ON V.CdTarifa =TB.IdTarifa LEFT JOIN TercCndtores AS CT ON VC.IdVendedor=CT.IdConductor LEFT JOIN Localidades AS LCE ON NC.IdLugarCed=LCE.IdLocal LEFT JOIN TiposMod AS MCD ON CT.IdModd=MCD.IdModd LEFT JOIN ExpLicencias AS ELC ON CT.IdLugar=ELC.IdLugar LEFT JOIN Trn_RecFactura AS R ON VC.TipDoc = R.TipDoc AND VC.Documento = R.Documento AND VC.IdCia = R.IdCiaDoc AND VC.Item = R.ItemDoc WHERE FecVence BETWEEN @pmFechaIni AND @pmFechaFin AND (ValorFactura-VC.ValorAbono)>@pmSaldo AND VC.TipDoc LIKE ISNULL(@pmTipDoc,'%') AND VC.IdCia LIKE ISNULL(@pmIdCia,'%%') AND VC.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND VC.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND VC.IdClase LIKE ISNULL(@pmIdClase,'%') AND VC.IdCargo LIKE ISNULL(@pmIdCargo,'%') AND VC.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND V.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND VC.IdVendedor LIKE ISNULL(@pmIdVendedor,'%') AND V.IdAdmon LIKE ISNULL(@pmIdAdmon,'%') ORDER BY NP.RazonSocial,VC.IdVehiculo,VC.TipDoc,VC.IdCia,VC.Documento,VC.Item GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paQryFacturasFmtc] @pmTipDoc VARCHAR(3),@pmFacturaIni INT,@pmFacturaFin INT,@pmIdCia CHAR(2) AS SELECT F.TipDoc AS Tip_Doc,TipoDoc,F.Factura AS NumFactura,F.IdCia AS CdCia,Compania,F.Fecha AS FechaDoc,F.IdConcepto AS CdConcepto,C.Concepto AS DescConcepto,F.IdCliente,T.RazonSocial AS NomCliente ,F.IdAgencia AS IdAgenc,A.Agencia AS NomAgencia,FechaVence,VrSubTotal,F.VrDescuento AS VrDcto,F.VrImpuesto AS VrIva,F.VrRetencion AS VrRetFte,F.VrReteICA AS VrRetIca,F.VrReteIVA,F.VrFletes ,F.VrOtros,F.VrCargos,F.VrOtrDcto,F.VrCostos,F.VrSobretasa,F.VrImpGlobal,F.VrFaltantes,F.VrAnticipos,F.VrNeto,VrAplicado,F.Cantidad AS CantTotal,F.BaseImp,F.BaseRet,F.BaseIca ,F.IdVend AS NitVend,VN.RazonSocial AS Vendedor,TarifaCom,F.NitContac AS FacNitContac,F.NomContac AS FacNomContac,F.TelContac AS FacTelContac,F.emlContac AS FacEmailContac,CargoContac ,F.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,NitEmpTrans,EmpTrans,F.TipRem AS TipRemision,F.Remision AS NumRemision,F.FecPedido,F.Modalidad,F.TipCom,F.Comprobante,F.IdCiaCom ,F.Anulado AS FacAnulado,F.NumDev,F.FecDev,F.Observacion AS Observ,TipoFE,FechaFE,F.IdEstado AS CdEstado,F.TimeSys AS Fec_Add,F.FecUpdate AS Fec_Update,F.IdCiaCrea,F.IdUsuario AS CdUsuario,Usuario,Leyenda --detales ,D.Item,D.IdCargo,CG.Concepto AS Cargo,D.Descripcion,D.ValorCargo,D.TarifaIva,D.VrIva AS DetVrIva,D.TarifaRet,D.VrRetencion AS DetVrRetencion,D.TarifaIca,D.VrReteIca AS DetVerIca,D.CdAgencia ,D.IdVehiculo AS PlacaVeh,D.Referencia,D.CodTarIva,D.CodTarRet,D.CodTarIca,D.CdClaseCue,D.TipCausac,D.NumCausac,D.CiaCausac,D.ItemCausac ,CG.CodFEDian,CG.IdCuentaDeb,CG.IdCuentaCre,CG.IdCueDebVp,CG.IdCueCreVp,CG.CdCuentaIva,CG.CdCuentaRet,CG.CdCuentaIca,CG.IvaIncluido --Información del cliente ,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion ,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail ,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte ,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,Zona ,CLI.IdGrupo AS CdGrupoCli,GrupoClie,ExcIva,LiqFletes,Autoret,VrCupo,VrSaldo ,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia ,FE.EstadoFE,FE.Prefijo,FE.NumFace,FE.CUFE,FE.Resolucion,FE.RangoNum,FE.FecVigencia, FE.CUFE_QR FROM Trn_Facturas AS F INNER JOIN Trn_FactCausacion AS D ON F.TipDoc=D.TipDoc AND F.Factura=D.Factura AND F.IdCia=D.IdCia INNER JOIN Companias AS CN ON F.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON F.TipDoc=TD.IdDoc INNER JOIN Conceptos AS C ON F.IdConcepto=C.IdConcepto INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario INNER JOIN Plazos AS PZ ON F.IdPlazo=PZ.IdPlazo INNER JOIN Terceros AS VN ON F.IdVend=VN.IdTercero INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero 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 Fijos AS CG ON D.IdCargo=CG.IdCargo LEFT JOIN TercCliente AS CLI ON F.IdCliente=CLI.IdClie LEFT JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona LEFT JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona LEFT JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo LEFT JOIN Agencias AS A ON F.IdAgencia=A.IdAgencia LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep LEFT JOIN Trn_Face AS FE ON F.TipDoc=FE.TipDoc AND F.Factura=FE.Documento AND F.IdCia=FE.IdCia WHERE F.TipDoc=@pmTipDoc AND F.Factura BETWEEN @pmFacturaIni AND @pmFacturaFin AND F.IdCia=@pmIdCia GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryCausacionFij] @pmTipDoc VARCHAR(3),@pmModalidad VARCHAR(10),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null AS SELECT IdCliente,VC.IdVehiculo AS PlacaVeh,V.IdAdmon AS CodAdm,TipoAdmon,VC.VehPropio AS Espropio,IdVendedor,VC.IdCargo AS CodCargo,Concepto,IdCuentaDeb,IdCuentaCre ,IdCueDebVp,IdCueCreVp,CdCuentaIva,CdCuentaRet,CdCuentaIca,SUM(ValorFactura) AS SvalFac,SUM(VC.BaseImpu) AS BaseIva,SUM(VC.VrIva) AS TotalIva ,SUM(VC.VrRetFte) AS TotalRet,SUM(VC.VrRetIca) AS TotalIca,MAX(VC.Tarifa_Iva) AS TarifIva,MAX(VC.Tarifa_Ret) AS TarifRet,MAX(VC.Tarifa_Ica) AS TarifIca FROM Trn_Causacion AS C INNER JOIN Trn_CausacVence AS VC ON C.TipDoc=VC.TipDoc AND C.Documento=VC.Documento AND C.IdCia=VC.IdCia INNER JOIN Vehiculos AS V ON VC.IdVehiculo=V.IdVehiculo INNER JOIN TiposAdm AS TA ON V.IdAdmon=TA.IdAdmon INNER JOIN Fijos AS F ON VC.IdCargo=F.IdCargo WHERE Anulado=0 AND Comprobante<=0 AND Modalidad IN ('DIARIO','OCASIONAL') AND C.TipDoc=@pmTipDoc AND FecFactura BETWEEN @pmFechaIni AND @pmFechaFin AND C.IdCia LIKE ISNULL(@pmIdCia,'%%') GROUP BY IdCliente,VC.IdVehiculo,V.IdAdmon,TipoAdmon,VC.VehPropio,IdVendedor,VC.IdCargo,Concepto,IdCuentaDeb,IdCuentaCre,IdCueDebVp,IdCueCreVp,CdCuentaIva,CdCuentaRet,CdCuentaIca GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryCausacionCue] @pmTipDoc VARCHAR(3),@pmModalidad VARCHAR(10),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null AS SELECT VC.IdCargo AS CodCargo,Concepto,V.IdAdmon,TipoAdmon,FC.IdCuentaDeb AS IdCuentDeb,FC.IdCuentaCre AS IdCuentCre ,FC.IdCueDebVp AS IdCueDbVp,FC.IdCueCreVp AS IdCueCrVp,FC.IdCuentaIva,FC.IdCuentaRet,FC.IdCuentaIca,SUM(ValorFactura) AS SvalFac ,SUM(VC.BaseImpu) AS BaseIva,SUM(VC.VrIva) AS TotalIva,SUM(VC.VrRetFte) AS TotalRet,SUM(VC.VrRetIca) AS TotalIca FROM Trn_Causacion AS C INNER JOIN Trn_CausacVence AS VC ON C.TipDoc=VC.TipDoc AND C.Documento=VC.Documento AND C.IdCia=VC.IdCia INNER JOIN Vehiculos AS V ON VC.IdVehiculo=V.IdVehiculo INNER JOIN TiposAdm AS TA ON V.IdAdmon=TA.IdAdmon INNER JOIN Fijos AS F ON VC.IdCargo=F.IdCargo LEFT JOIN Fijos_Cue AS FC ON VC.IdCargo=FC.IdCargo AND V.IdAdmon=FC.IdAdmon WHERE Anulado=0 AND Comprobante<=0 AND C.TipDoc=@pmTipDoc AND Modalidad IN ('DIARIO','OCASIONAL') AND FecFactura BETWEEN @pmFechaIni AND @pmFechaFin AND C.IdCia LIKE ISNULL(@pmIdCia,'%%') GROUP BY VC.IdCargo,Concepto,V.IdAdmon,TipoAdmon,FC.IdCuentaDeb,FC.IdCuentaCre,FC.IdCueDebVp,FC.IdCueCreVp,FC.IdCuentaIva,FC.IdCuentaRet,FC.IdCuentaIca GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryNotasBusesLta] @pmTipDoc VARCHAR(3),@pmIdCia CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME ,@pmIdCliente VARCHAR(16),@pmIdVehiculo VARCHAR(10) AS SELECT NumNota,IdCia,Fecha,FecVence,N.IdConcepto AS IdConcept,Concepto,IdCliente,T.RazonSocial AS Cliente, IdAgencia, IdVehiculo, IdConductor,O.RazonSocial AS Conductor,VehPropio, ValorTotal, ValorIva, ValorReteFte ,ValorReteIca, ValorCosto, ValorSancion, ValorNeto, ValorAplicado, IdVendedor,V.RazonSocial AS Vendedor,N.Comision AS Comison, IdTarCom, MulPlazos, N.IdPlazo AS IdPlaz,DiasPago AS Dias, PrestCaja, IntMora ,NumCheque,N.IdBanco AS IdBanc,Banco, IdCCosto, N.IdClase AS IdClas,IdCargo,Referencia,N.IdConcFE,TipDcm,Documento,IdCiaDoc,BaseIva, BaseRet, TarifaIva, TarifaRet, IdTarIva ,IdTarRet, TipCom, Comprobante, IdCiaCom, N.Observacion AS Observ, N.IdEstado AS IdEstad,Estado,TimeSys, FecUpdate, IdCiaCrea,N.IdUsuario AS IdUsuari FROM Trn_NotasBuses AS N INNER JOIN Conceptos AS C ON N.IdConcepto=C.IdConcepto INNER JOIN Terceros AS T ON N.IdCliente=T.IdTercero INNER JOIN Terceros AS O ON N.IdConductor=O.IdTercero LEFT JOIN Terceros AS V ON N.IdVendedor=V.IdTercero LEFT JOIN Plazos AS P ON N.IdPlazo=P.IdPlazo LEFT JOIN Bancos AS B ON N.IdBanco=B.IdBanco LEFT JOIN EstadoDoc AS E ON N.IdEstado=E.IdEstado WHERE TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') ORDER BY NumNota GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paQryCausacVence] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmItem INT AS SELECT TipDoc,Documento,IdCia,Item,FecEmision,FecVence,ValorFactura,ValorAbono,IdCliente,IdAgencia,IdVehiculo,VehPropio,IdVendedor,Comision ,IdCargo,IdClase,IdConcepto,FecUltPago,NotaInt,FecUltLiquida,Factura,Referencia,Detalle,TipRec,Recibo,IdCiaRec,ValorAtcpo,IdCuenta,CombCant,CombValor ,BaseImpu,Tarifa_Iva,VrIva,Tarifa_Ret,VrRetFte,Tarifa_Ica,VrRetIca,CodTarifIva,CodTarifRet,CodTarifIca,TipDocFac,NumDocFac,CdCiaFac FROM Trn_CausacVence WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia AND (Item>=ISNULL(@pmItem,0) AND Item<=ISNULL(@pmItem,2147483647)) ORDER BY Item GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paInstm_Cargos_Vce] @pmTipDoc VARCHAR(3), @pmDocumento INT, @pmIdCia CHAR(2),@pmtmNumero VARCHAR(5) AS INSERT INTO tm_Cargos (tmNumero, tmItem, tmIdVehiculo, tmVehPropio, tmIdCliente, tmIdCargo, tmConcepto, tmReferencia, tmFactura, tmValor, tmDetalle,tmIdConductor,tmFecVence,tmTipDoc, tmDocumento , tmIdCiaDoc, tmConductor,tmAbonado,tmTipDocAnt , tmDocAnticipo, tmCiaDocAnt ,tmIdCuenta,tmCombCant,tmCombValor,tmBaseImpu,tmTarifaIva,tmVrIva,tmTarifaRet,tmVrRetFte,tmTarifaIca,tmVrRetIca,tmCodTarifIva,tmCodTarifRet,tmCodTarifIca) SELECT @pmtmNumero,Item,IdVehiculo,VehPropio,IdCliente,F.IdCargo,Concepto,Referencia,Factura,ValorFactura,Detalle,IdVendedor,FecVence ,CASE WHEN (TipDoc='ADM' AND NumDocFac>0) THEN TipDocFac ELSE TipDoc END ,CASE WHEN (TipDoc='ADM' AND NumDocFac>0) THEN NumDocFac ELSE Documento END ,CASE WHEN (TipDoc='ADM' AND NumDocFac>0) THEN CdCiaFac ELSE IdCia END ,RazonSocial,ValorAtcpo,TipRec, Recibo, IdCiaRec,IdCuenta,CombCant,CombValor,BaseImpu,Tarifa_Iva,VrIva,Tarifa_Ret,VrRetFte,Tarifa_Ica,VrRetIca,CodTarifIva,CodTarifRet,CodTarifIca FROM Trn_CausacVence AS V INNER JOIN Terceros AS C ON V.IdVendedor=C.IdTercero INNER JOIN Fijos AS F ON V.IdCargo=F.IdCargo WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryCausacVenceLta] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmFechaAct SMALLDATETIME,@pmSaldo DECIMAL(14,4) ,@pmTipDoc VARCHAR(3)=Null,@pmIdCia CHAR(2)=Null,@pmIdClase VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null AS SELECT TipDoc,Documento, IdCia, Item, FecEmision, FecVence,DATEDIFF(Day,FecVence,@pmFechaAct) AS DiasMora,ValorFactura, ValorAbono,ValorFactura-ValorAbono AS ValorSaldo,IdCliente,T.RazonSocial AS NomCliente,IdVehiculo,VehPropio ,IdVendedor AS IdCondctor,C.RazonSocial AS NomConductor,V.IdCargo AS IdCarg,Concepto,V.IdClase AS CodClase, IdConcepto,Factura, Referencia, Detalle,FecUltPago, TipRec, Recibo, IdCiaRec,ValorAtcpo,NotaInt,FecUltLiquida,IdAgencia,Comision ,CombCant,CombValor,BaseImpu,Tarifa_Iva,VrIva,Tarifa_Ret,VrRetFte,Tarifa_Ica,VrRetIca,CodTarifIva,CodTarifRet,CodTarifIca,TipDocFac,NumDocFac,CdCiaFac FROM Trn_CausacVence AS V INNER JOIN Terceros AS T ON V.IdCliente=T.IdTercero INNER JOIN Terceros AS C ON V.IdVendedor=C.IdTercero INNER JOIN Fijos AS F ON V.IdCargo=F.IdCargo WHERE (ValorFactura-ValorAbono)>@pmSaldo AND (FecVence>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND FecVence<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME))) AND IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND TipDoc LIKE ISNULL(@pmTipDoc,'%') AND IdCia LIKE ISNULL(@pmIdCia ,'%%') AND V.IdClase LIKE ISNULL(@pmIdClase ,'%') ORDER BY FecVence DESC,IdCia,TipDoc,Documento,Item GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryCausacVenceVp] @pmIdCliente VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null AS SELECT TipDoc,Documento,IdCia,Item,FecEmision, FecVence, ValorFactura, ValorAbono, IdCliente, IdAgencia, IdVehiculo, VehPropio, IdVendedor ,Comision,IdCargo,V.IdClase AS CodClase,ClaseCuenta,Prioridad,IdConcepto,FecUltPago, NotaInt, FecUltLiquida, Factura, Referencia, Detalle, TipRec, Recibo, IdCiaRec ,ValorAtcpo,IdCuenta,CombCant,CombValor,BaseImpu,Tarifa_Iva,VrIva,Tarifa_Ret,VrRetFte,Tarifa_Ica,VrRetIca,CodTarifIva,CodTarifRet,CodTarifIca,TipDocFac,NumDocFac,CdCiaFac ,ISNULL(tmValorAbono,0) AS TotaAbonos FROM Trn_CausacVence AS V INNER JOIN BusClaseCue AS C ON V.IdClase=C.IdClase LEFT JOIN tm_vce AS TV ON V.TipDoc=TV.tmTipDoc AND V.Documento=TV.tmDocumento AND V.IdCia=TV.tmIdCia AND V.Item=TV.tmItem WHERE ValorFactura>(ISNULL(tmValorAbono,0)+0.5) AND IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND IdCliente like ISNULL(@pmIdCliente,'%') ORDER BY Prioridad,FecVence,TipDoc,Documento,Item GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryCausacionD_Cr] @pmTipDoc VARCHAR(3),@pmModalidad VARCHAR(10),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null AS SELECT C.TipDoc AS TDoc,C.Documento AS NumDoc,C.IdCia AS CodCia,Fecha,FecFactura,IdCliente AS NitPropietario,RazonSocial,V.IdVehiculo AS PlacaVeh,V.VehPropio AS Espropio,IdVendedor AS CedConductor,V.IdCargo AS CodCargo,Concepto ,IdCuentaDeb,IdCuentaCre,IdCueDebVp,IdCueCreVp,CdCuentaIva,CdCuentaRet,SUM(ValorFactura) AS SvalFac,SUM(V.BaseImpu) AS BaseIva,SUM(V.VrIva) AS TotalIva,SUM(V.VrRetFte) AS TotalRet FROM Trn_Causacion AS C INNER JOIN Trn_CausacVence AS V ON C.TipDoc=V.TipDoc AND C.Documento=V.Documento AND C.IdCia=V.IdCia INNER JOIN Fijos AS F ON V.IdCargo=F.IdCargo INNER JOIN Terceros AS P ON V.IdCliente=P.IdTercero WHERE Anulado=0 AND Comprobante<=0 AND Modalidad IN ('DIARIO','OCASIONAL') AND C.TipDoc=@pmTipDoc AND FecFactura BETWEEN @pmFechaIni AND @pmFechaFin AND C.IdCia LIKE ISNULL(@pmIdCia,'%%') GROUP BY C.TipDoc,C.Documento,C.IdCia,Fecha,FecFactura,IdCliente,RazonSocial,V.IdVehiculo,V.VehPropio,IdVendedor,V.IdCargo,Concepto,IdCuentaDeb,IdCuentaCre,IdCueDebVp,IdCueCreVp,CdCuentaIva,CdCuentaRet GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paInsCausacVence] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmItem INT,@pmFecEmision SMALLDATETIME,@pmFecVence SMALLDATETIME,@pmValorFactura MONEY,@pmValorAbono MONEY,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdVehiculo VARCHAR(10),@pmVehPropio BIT,@pmIdVendedor VARCHAR(16),@pmComision DECIMAL(14,4) ,@pmIdCargo VARCHAR(4),@pmIdClase VARCHAR(4),@pmIdConcepto VARCHAR(4),@pmFecUltPago SMALLDATETIME,@pmNotaInt INT,@pmFecUltLiquida SMALLDATETIME,@pmFactura VARCHAR(15),@pmReferencia VARCHAR(20),@pmDetalle VARCHAR(100),@pmTipRec VARCHAR(3),@pmRecibo INT,@pmIdCiaRec CHAR(2),@pmValorAtcpo MONEY,@pmIdCuenta VARCHAR(16),@pmCombCant DECIMAL(14,4),@pmCombValor MONEY ,@pmBaseImpu MONEY,@pmTarifa_Iva DECIMAL(14,4),@pmVrIva MONEY,@pmTarifa_Ret DECIMAL(14,4),@pmVrRetFte MONEY,@pmTarifa_Ica DECIMAL(14,4),@pmVrRetIca MONEY,@pmCodTarifIva VARCHAR(4),@pmCodTarifRet VARCHAR(4),@pmCodTarifIca VARCHAR(4),@pmTipDocFac VARCHAR(3),@pmNumDocFac INT,@pmCdCiaFac CHAR(2) AS INSERT INTO Trn_CausacVence (TipDoc,Documento,IdCia,Item,FecEmision,FecVence,ValorFactura,ValorAbono,IdCliente,IdAgencia,IdVehiculo,VehPropio,IdVendedor,Comision,IdCargo,IdClase,IdConcepto,FecUltPago,NotaInt,FecUltLiquida,Factura,Referencia,Detalle,TipRec,Recibo,IdCiaRec,ValorAtcpo,IdCuenta,CombCant,CombValor ,BaseImpu,Tarifa_Iva,VrIva,Tarifa_Ret,VrRetFte,Tarifa_Ica,VrRetIca,CodTarifIva,CodTarifRet,CodTarifIca,TipDocFac,NumDocFac,CdCiaFac) VALUES (@pmTipDoc,@pmDocumento,@pmIdCia,@pmItem,@pmFecEmision,@pmFecVence,@pmValorFactura,@pmValorAbono,@pmIdCliente,@pmIdAgencia,@pmIdVehiculo,@pmVehPropio,@pmIdVendedor,@pmComision,@pmIdCargo,@pmIdClase,@pmIdConcepto,@pmFecUltPago,@pmNotaInt,@pmFecUltLiquida,@pmFactura,@pmReferencia,@pmDetalle,@pmTipRec ,@pmRecibo,@pmIdCiaRec,@pmValorAtcpo,@pmIdCuenta,@pmCombCant,@pmCombValor,@pmBaseImpu,@pmTarifa_Iva,@pmVrIva,@pmTarifa_Ret,@pmVrRetFte,@pmTarifa_Ica,@pmVrRetIca,@pmCodTarifIva,@pmCodTarifRet,@pmCodTarifIca,@pmTipDocFac,@pmNumDocFac,@pmCdCiaFac) GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paUpCausacVence] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmItem INT,@pmFecEmision SMALLDATETIME,@pmFecVence SMALLDATETIME,@pmValorFactura MONEY,@pmValorAbono MONEY,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdVehiculo VARCHAR(10) ,@pmVehPropio BIT,@pmIdVendedor VARCHAR(16),@pmComision DECIMAL(14,4),@pmIdCargo VARCHAR(4),@pmIdClase VARCHAR(4),@pmIdConcepto VARCHAR(4),@pmFecUltPago SMALLDATETIME,@pmNotaInt INT,@pmFecUltLiquida SMALLDATETIME,@pmFactura VARCHAR(15),@pmReferencia VARCHAR(20),@pmDetalle VARCHAR(100) ,@pmTipRec VARCHAR(3),@pmRecibo INT,@pmIdCiaRec CHAR(2),@pmValorAtcpo MONEY,@pmIdCuenta VARCHAR(16),@pmCombCant DECIMAL(14,4),@pmCombValor MONEY,@pmBaseImpu MONEY,@pmTarifa_Iva DECIMAL(14,4),@pmVrIva MONEY,@pmTarifa_Ret DECIMAL(14,4),@pmVrRetFte MONEY,@pmTarifa_Ica DECIMAL(14,4),@pmVrRetIca MONEY ,@pmCodTarifIva VARCHAR(4),@pmCodTarifRet VARCHAR(4),@pmCodTarifIca VARCHAR(4),@pmTipDocFac VARCHAR(3),@pmNumDocFac INT,@pmCdCiaFac CHAR(2) AS UPDATE Trn_CausacVence SET FecEmision=@pmFecEmision,FecVence=@pmFecVence,ValorFactura=@pmValorFactura,ValorAbono=@pmValorAbono,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,IdVehiculo=@pmIdVehiculo,VehPropio=@pmVehPropio,IdVendedor=@pmIdVendedor,Comision=@pmComision,IdCargo=@pmIdCargo,IdClase=@pmIdClase ,IdConcepto=@pmIdConcepto,FecUltPago=@pmFecUltPago,NotaInt=@pmNotaInt,FecUltLiquida=@pmFecUltLiquida,Factura=@pmFactura,Referencia=@pmReferencia,Detalle=@pmDetalle,TipRec=@pmTipRec,Recibo=@pmRecibo,IdCiaRec=@pmIdCiaRec,ValorAtcpo=@pmValorAtcpo,IdCuenta=@pmIdCuenta,CombCant=@pmCombCant,CombValor=@pmCombValor ,BaseImpu=@pmBaseImpu,Tarifa_Iva=@pmTarifa_Iva,VrIva=@pmVrIva,Tarifa_Ret=@pmTarifa_Ret,VrRetFte=@pmVrRetFte,Tarifa_Ica=@pmTarifa_Ica,VrRetIca=@pmVrRetIca,CodTarifIva=@pmCodTarifIva,CodTarifRet=@pmCodTarifRet,CodTarifIca=@pmCodTarifIca,TipDocFac=@pmTipDocFac,NumDocFac=@pmNumDocFac,CdCiaFac=@pmCdCiaFac WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia AND Item=@pmItem GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paInsNotasBuses] @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmVehPropio BIT,@pmValorTotal MONEY,@pmValorIva MONEY,@pmValorReteFte MONEY,@pmValorReteIca MONEY,@pmValorCosto MONEY,@pmValorSancion MONEY,@pmValorNeto MONEY,@pmValorAplicado MONEY ,@pmIdVendedor VARCHAR(16),@pmComision DECIMAL(14,4),@pmIdTarCom VARCHAR(4),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmPrestCaja BIT,@pmIntMora BIT,@pmNumCheque VARCHAR(20),@pmIdBanco VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdClase VARCHAR(4),@pmIdCargo VARCHAR(4),@pmReferencia VARCHAR(20),@pmTipDcm VARCHAR(3),@pmDocumento INT,@pmIdCiaDoc CHAR(2),@pmBaseIva MONEY,@pmBaseRet MONEY,@pmTarifaIva DECIMAL(14,4),@pmTarifaRet DECIMAL(14,4),@pmIdTarIva VARCHAR(4) ,@pmIdTarRet VARCHAR(4),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmIdConcFE INT,@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11) AS INSERT INTO Trn_NotasBuses (TipDoc,NumNota,IdCia,Fecha,FecVence,IdConcepto,IdCliente,IdAgencia,IdVehiculo,IdConductor,VehPropio,ValorTotal,ValorIva,ValorReteFte,ValorReteIca,ValorCosto,ValorSancion,ValorNeto,ValorAplicado,IdVendedor,Comision,IdTarCom,MulPlazos,IdPlazo,PrestCaja,IntMora,NumCheque,IdBanco,IdCCosto,IdClase,IdCargo,Referencia,TipDcm,Documento,IdCiaDoc,BaseIva,BaseRet,TarifaIva,TarifaRet,IdTarIva,IdTarRet,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,IdConcFE) VALUES (@pmTipDoc,@pmNumNota,@pmIdCia,@pmFecha,@pmFecVence,@pmIdConcepto,@pmIdCliente,@pmIdAgencia,@pmIdVehiculo,@pmIdConductor,@pmVehPropio,@pmValorTotal,@pmValorIva,@pmValorReteFte,@pmValorReteIca,@pmValorCosto,@pmValorSancion,@pmValorNeto,@pmValorAplicado,@pmIdVendedor,@pmComision,@pmIdTarCom,@pmMulPlazos,@pmIdPlazo,@pmPrestCaja,@pmIntMora,@pmNumCheque,@pmIdBanco,@pmIdCCosto,@pmIdClase,@pmIdCargo,@pmReferencia,@pmTipDcm ,@pmDocumento,@pmIdCiaDoc,@pmBaseIva,@pmBaseRet,@pmTarifaIva,@pmTarifaRet,@pmIdTarIva,@pmIdTarRet,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmIdConcFE) GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paUpNotasBuses] @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmVehPropio BIT,@pmValorTotal MONEY,@pmValorIva MONEY,@pmValorReteFte MONEY,@pmValorReteIca MONEY,@pmValorCosto MONEY,@pmValorSancion MONEY,@pmValorNeto MONEY,@pmValorAplicado MONEY ,@pmIdVendedor VARCHAR(16),@pmComision DECIMAL(14,4),@pmIdTarCom VARCHAR(4),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmPrestCaja BIT,@pmIntMora BIT,@pmNumCheque VARCHAR(20),@pmIdBanco VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdClase VARCHAR(4),@pmIdCargo VARCHAR(4),@pmReferencia VARCHAR(20),@pmTipDcm VARCHAR(3),@pmDocumento INT,@pmIdCiaDoc CHAR(2),@pmBaseIva MONEY,@pmBaseRet MONEY,@pmTarifaIva DECIMAL(14,4),@pmTarifaRet DECIMAL(14,4),@pmIdTarIva VARCHAR(4) ,@pmIdTarRet VARCHAR(4),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmIdConcFE INT,@pmFecUpdate SMALLDATETIME AS UPDATE Trn_NotasBuses SET Fecha=@pmFecha,FecVence=@pmFecVence,IdConcepto=@pmIdConcepto,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,IdVehiculo=@pmIdVehiculo,IdConductor=@pmIdConductor,VehPropio=@pmVehPropio,ValorTotal=@pmValorTotal,ValorIva=@pmValorIva,ValorReteFte=@pmValorReteFte,ValorReteIca=@pmValorReteIca,ValorCosto=@pmValorCosto,ValorSancion=@pmValorSancion,ValorNeto=@pmValorNeto,ValorAplicado=@pmValorAplicado,IdVendedor=@pmIdVendedor,Comision=@pmComision ,IdTarCom=@pmIdTarCom,MulPlazos=@pmMulPlazos,IdPlazo=@pmIdPlazo,PrestCaja=@pmPrestCaja,IntMora=@pmIntMora,NumCheque=@pmNumCheque,IdBanco=@pmIdBanco,IdCCosto=@pmIdCCosto,IdClase=@pmIdClase,IdCargo=@pmIdCargo,Referencia=@pmReferencia,TipDcm=@pmTipDcm,Documento=@pmDocumento,IdCiaDoc=@pmIdCiaDoc,BaseIva=@pmBaseIva,BaseRet=@pmBaseRet,TarifaIva=@pmTarifaIva,TarifaRet=@pmTarifaRet,IdTarIva=@pmIdTarIva,IdTarRet=@pmIdTarRet,TipCom=@pmTipCom ,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Observacion=@pmObservacion,IdEstado=@pmIdEstado,IdConcFE=@pmIdConcFE,FecUpdate=@pmFecUpdate WHERE TipDoc=@pmTipDoc AND NumNota=@pmNumNota AND IdCia=@pmIdCia GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryNotasBuses] @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2) AS SELECT TipDoc,NumNota,IdCia,Fecha,FecVence,IdConcepto,IdCliente,IdAgencia,IdVehiculo,IdConductor,VehPropio,ValorTotal,ValorIva,ValorReteFte,ValorReteIca,ValorCosto,ValorSancion,ValorNeto,ValorAplicado,IdVendedor ,Comision,IdTarCom,MulPlazos,IdPlazo,PrestCaja,IntMora,NumCheque,IdBanco,IdCCosto,IdClase,IdCargo,Referencia,TipDcm,Documento,IdCiaDoc,BaseIva,BaseRet,TarifaIva,TarifaRet,IdTarIva,IdTarRet,TipCom,Comprobante ,IdCiaCom,Observacion,IdEstado,IdConcFE,TimeSys,FecUpdate,IdCiaCrea,IdUsuario FROM Trn_NotasBuses WHERE TipDoc=@pmTipDoc AND NumNota=@pmNumNota AND IdCia=@pmIdCia GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryFijos_Cue_Cr] @pmIdCargo VARCHAR(4)=Null, @pmIdAdmon VARCHAR(4)=Null,@pmInactivo BIT=Null AS SELECT FC.IdAdmon AS IdAdmn,TipoAdmon, FC.IdCuentaDeb AS IdCuentDeb,P.NomCuenta AS NomCueDb, FC.IdCuentaCre AS IdCuentCre,PC.NomCuenta AS NomCueCr, FC.IdCueDebVp AS IdCueDbVp,FC.IdCueCreVp AS IdCueCrVp ,FC.IdCargo AS CodCargo,Concepto ,TipoAplica,DiasSemana,DiasMes, ValorMensual, ValorDiario,F.Inactivo AS Fij_Inactivo ,FC.IdCuentaIva,PV.NomCuenta AS NomCueIVA,FC.IdCuentaRet,PR.NomCuenta AS NomCueRet,FC.IdCuentaIca ,F.FechaAdd AS FecAdd, F.FechaUpdate AS FecUpdate, F.IdUsuario AS IdUsuari,Usuario FROM Fijos_Cue AS FC INNER JOIN TiposAdm AS TA ON FC.IdAdmon=TA.IdAdmon INNER JOIN Fijos AS F ON FC. IdCargo=F.IdCargo INNER JOIN Puc AS P ON FC.IdCuentaDeb=P.IdCuenta INNER JOIN Puc AS PC ON FC.IdCuentaCre=PC.IdCuenta INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario LEFT JOIN Puc AS PV ON FC.IdCuentaIva=PV.IdCuenta LEFT JOIN Puc AS PR ON FC.IdCuentaRet=PR.IdCuenta WHERE FC.IdCargo like ISNULL(@pmIdCargo,'%') AND FC.IdAdmon LIKE ISNULL(@pmIdAdmon,'%') AND (F.Inactivo=ISNULL(@pmInactivo,0) or F.Inactivo=ISNULL(@pmInactivo,1)) ORDER BY FC.IdAdmon,FC.IdCargo GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryFijosLta] @pmInactivo BIT=Null AS SELECT IdCargo, Concepto,Obligatorio,TipoAplica,DiasSemana, DiasMes, ValorMensual, ValorDiario ,IdCuentaDeb, IdCuentaCre, IdCueDebVp, IdCueCreVp,NCuotas,DiasVcmto,OrdenAbono,TipoDcto,DctoPpago,VrCargoAdic ,FecLimiteIni,FecLimiteFin,CdTarifaIva,IvaIncluido,CdTarifaRet,CdCuentaIva,CdCuentaRet,CdCuentaIca,CodFEDian,Inactivo,FechaAdd, FechaUpdate, IdUsuario FROM Fijos WHERE (Inactivo=ISNULL(@pmInactivo,0) or Inactivo=ISNULL(@pmInactivo,1)) ORDER BY IdCargo GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryFijos_CueDso] @pmIdCargo VARCHAR(4)=Null,@pmIdAdmon VARCHAR(4)=Null AS SELECT FC.IdAdmon AS IdAdmn,TipoAdmon, FC.IdCuentaDeb AS IdCuentDeb, FC.IdCuentaCre AS IdCuentCre, FC.IdCueDebVp AS IdCueDbVp , FC.IdCueCreVp AS IdCueCrVp,FC.IdCargo AS CodCargo,Concepto,IdCuentaIva,IdCuentaRet,IdCuentaIca FROM Fijos_Cue AS FC INNER JOIN TiposAdm AS TA ON FC.IdAdmon=TA.IdAdmon INNER JOIN Fijos AS F ON FC. IdCargo=F.IdCargo WHERE FC.IdCargo like ISNULL(@pmIdCargo,'%') AND FC.IdAdmon LIKE ISNULL(@pmIdAdmon,'%') ORDER BY FC.IdAdmon,FC.IdCargo GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryFijos] @pmIdCargo VARCHAR(4) AS SELECT IdCargo,Concepto,Obligatorio,TipoAplica,DiasSemana,DiasMes,ValorMensual,ValorDiario ,IdCuentaDeb,IdCuentaCre,IdCueDebVp,IdCueCreVp,NCuotas,DiasVcmto,OrdenAbono,TipoDcto,DctoPpago,VrCargoAdic ,FecLimiteIni,FecLimiteFin,Inactivo,CdTarifaIva,CdTarifaRet,CdCuentaIva,CdCuentaRet,CdCuentaIca,CodFEDian,IvaIncluido,FechaAdd,FechaUpdate,IdUsuario FROM Fijos WHERE IdCargo=@pmIdCargo GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paUpFijos] @pmIdCargo VARCHAR(4),@pmConcepto VARCHAR(100), @pmObligatorio BIT, @pmTipoAplica VARCHAR(10), @pmDiasSemana VARCHAR(20), @pmDiasMes VARCHAR(100) , @pmValorMensual MONEY, @pmValorDiario MONEY, @pmIdCuentaDeb VARCHAR(16), @pmIdCuentaCre VARCHAR(16), @pmIdCueDebVp VARCHAR(16), @pmIdCueCreVp VARCHAR(16),@pmInactivo BIT ,@pmNCuotas INT,@pmDiasVcmto VARCHAR(50),@pmOrdenAbono INT,@pmTipoDcto CHAR(1),@pmDctoPpago DECIMAL(14,4),@pmVrCargoAdic MONEY,@pmFecLimiteIni SMALLDATETIME,@pmFecLimiteFin SMALLDATETIME ,@pmCdTarifaIva VARCHAR(4),@pmCdTarifaRet VARCHAR(4),@pmCdCuentaIva VARCHAR(16),@pmCdCuentaRet VARCHAR(16),@pmCdCuentaIca VARCHAR(16),@pmCodFEDian VARCHAR(20),@pmIvaIncluido BIT,@pmFechaUpdate SMALLDATETIME AS UPDATE Fijos SET Concepto=@pmConcepto,Obligatorio=@pmObligatorio,TipoAplica=@pmTipoAplica, DiasSemana=@pmDiasSemana, DiasMes=@pmDiasMes, ValorMensual=@pmValorMensual, ValorDiario=@pmValorDiario,IdCuentaDeb=@pmIdCuentaDeb ,IdCuentaCre=@pmIdCuentaCre, IdCueDebVp=@pmIdCueDebVp, IdCueCreVp=@pmIdCueCreVp ,NCuotas=@pmNCuotas,DiasVcmto=@pmDiasVcmto,OrdenAbono=@pmOrdenAbono,TipoDcto=@pmTipoDcto,DctoPpago=@pmDctoPpago,VrCargoAdic=@pmVrCargoAdic,Inactivo=@pmInactivo,FecLimiteIni=@pmFecLimiteIni,FecLimiteFin=@pmFecLimiteFin,FechaUpdate= @pmFechaUpdate ,CdTarifaIva=@pmCdTarifaIva,CdTarifaRet=@pmCdTarifaRet,CdCuentaIva=@pmCdCuentaIva,CdCuentaRet=@pmCdCuentaRet,CdCuentaIca=@pmCdCuentaIca,CodFEDian=@pmCodFEDian,IvaIncluido=@pmIvaIncluido WHERE IdCargo=@pmIdCargo GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paInsFijos] @pmIdCargo VARCHAR(4),@pmConcepto VARCHAR(100), @pmObligatorio BIT, @pmTipoAplica VARCHAR(10), @pmDiasSemana VARCHAR(20), @pmDiasMes VARCHAR(100) , @pmValorMensual MONEY, @pmValorDiario MONEY, @pmIdCuentaDeb VARCHAR(16), @pmIdCuentaCre VARCHAR(16), @pmIdCueDebVp VARCHAR(16), @pmIdCueCreVp VARCHAR(16),@pmInactivo BIT ,@pmNCuotas INT,@pmDiasVcmto VARCHAR(50),@pmOrdenAbono INT,@pmTipoDcto CHAR(1),@pmDctoPpago DECIMAL(14,4),@pmVrCargoAdic MONEY,@pmFecLimiteIni SMALLDATETIME,@pmFecLimiteFin SMALLDATETIME ,@pmCdTarifaIva VARCHAR(4),@pmCdTarifaRet VARCHAR(4),@pmCdCuentaIva VARCHAR(16),@pmCdCuentaRet VARCHAR(16),@pmCdCuentaIca VARCHAR(16),@pmCodFEDian VARCHAR(20),@pmIvaIncluido BIT,@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11) AS INSERT INTO Fijos (IdCargo,Concepto,Obligatorio,TipoAplica,DiasSemana,DiasMes,ValorMensual,ValorDiario,IdCuentaDeb,IdCuentaCre,IdCueDebVp,IdCueCreVp,NCuotas,DiasVcmto,OrdenAbono,TipoDcto,DctoPpago ,VrCargoAdic,FecLimiteIni,FecLimiteFin,Inactivo,FechaAdd,IdUsuario,CdTarifaIva,CdTarifaRet,CdCuentaIva,CdCuentaRet,CdCuentaIca,CodFEDian,IvaIncluido) VALUES (@pmIdCargo,@pmConcepto, @pmObligatorio, @pmTipoAplica, @pmDiasSemana, @pmDiasMes , @pmValorMensual, @pmValorDiario, @pmIdCuentaDeb, @pmIdCuentaCre, @pmIdCueDebVp, @pmIdCueCreVp ,@pmNCuotas,@pmDiasVcmto,@pmOrdenAbono,@pmTipoDcto,@pmDctoPpago,@pmVrCargoAdic,@pmFecLimiteIni,@pmFecLimiteFin,@pmInactivo,@pmFechaAdd,@pmIdUsuario,@pmCdTarifaIva,@pmCdTarifaRet,@pmCdCuentaIva,@pmCdCuentaRet,@pmCdCuentaIca,@pmCodFEDian,@pmIvaIncluido) GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paInstm_Cargos] @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmIdVehiculo VARCHAR(10),@pmtmVehPropio BIT,@pmtmIdConductor VARCHAR(16),@pmtmIdCliente VARCHAR(16),@pmtmIdCargo VARCHAR(4),@pmtmConcepto VARCHAR(100),@pmtmReferencia VARCHAR(20) ,@pmtmFactura VARCHAR(15),@pmtmValor MONEY,@pmtmDetalle VARCHAR(100),@pmtmFecVence SMALLDATETIME,@pmtmTipDoc VARCHAR(3),@pmtmDocumento INT,@pmtmIdCiaDoc CHAR(2),@pmtmConductor VARCHAR(100),@pmtmAbonado MONEY,@pmtmTipDocAnt VARCHAR(3) ,@pmtmDocAnticipo INT,@pmtmCiaDocAnt CHAR(2),@pmtmPrestamoID INT,@pmtmIdCiaPre CHAR(2),@pmtmNumCuota INT,@pmtmFecCausa SMALLDATETIME,@pmtmValorCapital MONEY,@pmtmValorInteres MONEY,@pmtmIdCuenta VARCHAR(16),@pmtmCombCant DECIMAL(14,4),@pmtmCombValor MONEY ,@pmtmBaseImpu MONEY,@pmtmTarifaIva DECIMAL(14,4),@pmtmVrIva MONEY,@pmtmTarifaRet DECIMAL(14,4),@pmtmVrRetFte MONEY,@pmtmTarifaIca DECIMAL(14,4),@pmtmVrRetIca MONEY,@pmtmCodTarifIva VARCHAR(4),@pmtmCodTarifRet VARCHAR(4),@pmtmCodTarifIca VARCHAR(4) AS INSERT INTO tm_Cargos (tmNumero,tmItem,tmIdVehiculo,tmVehPropio,tmIdConductor,tmIdCliente,tmIdCargo,tmConcepto,tmReferencia,tmFactura,tmValor,tmDetalle,tmFecVence,tmTipDoc,tmDocumento,tmIdCiaDoc,tmConductor,tmAbonado,tmTipDocAnt,tmDocAnticipo,tmCiaDocAnt,tmPrestamoID,tmIdCiaPre,tmNumCuota ,tmFecCausa,tmValorCapital,tmValorInteres,tmIdCuenta,tmCombCant,tmCombValor,tmBaseImpu,tmTarifaIva,tmVrIva,tmTarifaRet,tmVrRetFte,tmTarifaIca,tmVrRetIca,tmCodTarifIva,tmCodTarifRet,tmCodTarifIca) VALUES (@pmtmNumero,@pmtmItem,@pmtmIdVehiculo,@pmtmVehPropio,@pmtmIdConductor,@pmtmIdCliente,@pmtmIdCargo,@pmtmConcepto,@pmtmReferencia,@pmtmFactura,@pmtmValor,@pmtmDetalle,@pmtmFecVence,@pmtmTipDoc,@pmtmDocumento,@pmtmIdCiaDoc,@pmtmConductor,@pmtmAbonado ,@pmtmTipDocAnt,@pmtmDocAnticipo,@pmtmCiaDocAnt,@pmtmPrestamoID,@pmtmIdCiaPre,@pmtmNumCuota,@pmtmFecCausa,@pmtmValorCapital,@pmtmValorInteres,@pmtmIdCuenta,@pmtmCombCant,@pmtmCombValor,@pmtmBaseImpu,@pmtmTarifaIva,@pmtmVrIva,@pmtmTarifaRet,@pmtmVrRetFte,@pmtmTarifaIca,@pmtmVrRetIca ,@pmtmCodTarifIva,@pmtmCodTarifRet,@pmtmCodTarifIca) GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQrytm_Cargos] @pmtmNumero VARCHAR(5),@pmtmItem INT AS SELECT tmNumero,tmItem,tmIdVehiculo,tmVehPropio,tmIdConductor,tmIdCliente,tmIdCargo,tmConcepto,tmReferencia ,tmFactura,tmValor,tmDetalle,tmFecVence,tmTipDoc,tmDocumento,tmIdCiaDoc,tmConductor,tmAbonado,tmTipDocAnt,tmDocAnticipo ,tmCiaDocAnt,tmPrestamoID,tmIdCiaPre,tmNumCuota,tmFecCausa,tmValorCapital,tmValorInteres,tmIdCuenta,tmCombCant,tmCombValor ,tmBaseImpu,tmTarifaIva,tmVrIva,tmTarifaRet,tmVrRetFte,tmTarifaIca,tmVrRetIca,tmCodTarifIva,tmCodTarifRet,tmCodTarifIca FROM tm_Cargos WHERE tmNumero=@pmtmNumero AND (tmItem>=ISNULL(@pmtmItem,-1) AND tmItem<=ISNULL(@pmtmItem,2147483647)) ORDER BY tmItem GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQrytm_Cargos_Cr] @pmtmNumero VARCHAR(5) AS SELECT tmItem, tmIdCargo, tmConcepto, tmValor,tmDetalle,tmFactura,tmReferencia, tmIdVehiculo,tmVehPropio,tmIdConductor,C.RazonSocial AS Conductor , tmIdCliente,T.RazonSocial AS Propietario,tmNumero, tmFecVence, tmTipDoc, tmDocumento, tmIdCiaDoc, tmConductor,tmAbonado,tmTipDocAnt,tmDocAnticipo,tmCiaDocAnt ,tmPrestamoID, tmIdCiaPre, tmNumCuota, tmFecCausa, tmValorCapital, tmValorInteres,tmIdCuenta,tmCombCant,tmCombValor ,tmBaseImpu,tmTarifaIva,tmVrIva,tmTarifaRet,tmVrRetFte,tmTarifaIca,tmVrRetIca,tmCodTarifIva,tmCodTarifRet,tmCodTarifIca FROM tm_Cargos INNER JOIN Terceros AS T ON tm_Cargos.tmIdCliente=T.IdTercero INNER JOIN Terceros AS C ON tm_Cargos.tmIdConductor=C.IdTercero WHERE tmNumero=@pmtmNumero ORDER BY tmItem GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryCausacionOtr] @pmTipDoc VARCHAR(3),@pmIdCia CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdVehiculo VARCHAR(10) ,@pmIdProveedor VARCHAR(16),@pmDocumentoIni INT=Null,@pmDocumentoFin INT=Null AS SELECT Documento,IdCia, Fecha, IdConcepto, Factura, IdProveedor,P.RazonSocial AS Proveedor, FecFactura, FecVence, Referencia, Modalidad, IdVehiculo, IdPropietario,O.RazonSocial AS Propietario,IdConductor ,D.RazonSocial AS Conductor, VehPropio, ValorTotal, ValorDcto, ValorIva, ValorReteFte, ValorReteIva, ValorReteIca, ValorOtros, ValorFletes, ValorNeto,ValorCCobrar, BaseIva, BaseRet, TarifaIva ,TarifaRet, TarifaIca, IdTarIva, IdTarRet, IdTarIca, MulPlazos,C.IdPlazo AS IdPlaz, CxPagar,CxcVarios, TipEgr, Egreso, IdCiaEgr, Anulado, NumDev, FecDev, TipCom,Comprobante, IdCiaCom,C.Observacion AS Observ ,C.IdEstado AS IdEstad,Estado,CierreCte,IndFactura,TimeSys, FecUpdate, IdCiaCrea, C.IdUsuario AS IdUsuari FROM Trn_Causacion AS C INNER JOIN Terceros AS P ON C.IdProveedor=P.IdTercero INNER JOIN Terceros AS O ON C.IdPropietario=O.IdTercero INNER JOIN Terceros AS D ON C.IdConductor=D.IdTercero INNER JOIN EstadoDoc AS E ON C.IdEstado=E.IdEstado WHERE TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND IdCia LIKE ISNULL(@pmIdCia ,'%%') AND IdVehiculo like ISNULL(@pmIdVehiculo,'%') AND IdProveedor LIKE ISNULL(@pmIdProveedor,'%') AND Documento BETWEEN ISNULL(@pmDocumentoIni,0) AND ISNULL(@pmDocumentoFin,2147483647) ORDER BY IdCia,Documento GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryCausacionDia] @pmTipDoc VARCHAR(3),@pmIdCia CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME ,@pmDocumentoIni INT=Null ,@pmDocumentoFin INT=Null AS SELECT Documento,IdCia,Fecha,Referencia,Modalidad,IdVehiculo, IdPropietario,T.RazonSocial AS Propietario, IdConductor,O.RazonSocial AS Conductor,VehPropio,ValorNeto,Anulado,NumDev,FecDev,IndFactura ,TipCom,Comprobante,IdCiaCom,C.Observacion AS Observ,C.IdEstado AS IdEstad,Estado, TimeSys, FecUpdate, IdCiaCrea,C.IdUsuario AS IdUsuari FROM Trn_Causacion AS C LEFT JOIN Terceros AS T ON C.IdPropietario=T.IdTercero LEFT JOIN Terceros AS O ON C.IdConductor=O.IdTercero LEFT JOIN EstadoDoc AS E ON C.IdEstado=E.IdEstado WHERE TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND Documento BETWEEN ISNULL(@pmDocumentoIni,0) AND ISNULL(@pmDocumentoFin,2147483647) ORDER BY IdCia,Documento GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paUpCausacion] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmFactura VARCHAR(15),@pmIdProveedor VARCHAR(16),@pmFecFactura SMALLDATETIME,@pmFecVence SMALLDATETIME,@pmReferencia VARCHAR(20),@pmModalidad VARCHAR(10),@pmIdVehiculo VARCHAR(10),@pmIdPropietario VARCHAR(16),@pmIdConductor VARCHAR(16) ,@pmVehPropio BIT,@pmValorTotal MONEY,@pmValorDcto MONEY,@pmValorIva MONEY,@pmValorReteFte MONEY,@pmValorReteIva MONEY,@pmValorReteIca MONEY,@pmValorOtros MONEY,@pmValorFletes MONEY,@pmValorNeto MONEY,@pmValorCCobrar MONEY,@pmBaseIva MONEY,@pmBaseRet MONEY,@pmTarifaIva DECIMAL(14,4),@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmIdTarIva VARCHAR(4),@pmIdTarRet VARCHAR(4) ,@pmIdTarIca VARCHAR(4),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmCxPagar BIT,@pmCxcVarios BIT,@pmTipEgr VARCHAR(3),@pmEgreso INT,@pmIdCiaEgr CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmCierreCte BIT,@pmIndFactura INT,@pmFecUpdate SMALLDATETIME AS UPDATE Trn_Causacion SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,Factura=@pmFactura,IdProveedor=@pmIdProveedor,FecFactura=@pmFecFactura,FecVence=@pmFecVence,Referencia=@pmReferencia,Modalidad=@pmModalidad,IdVehiculo=@pmIdVehiculo,IdPropietario=@pmIdPropietario,IdConductor=@pmIdConductor,VehPropio=@pmVehPropio,ValorTotal=@pmValorTotal,ValorDcto=@pmValorDcto,ValorIva=@pmValorIva,ValorReteFte=@pmValorReteFte ,ValorReteIva=@pmValorReteIva,ValorReteIca=@pmValorReteIca,ValorOtros=@pmValorOtros,ValorFletes=@pmValorFletes,ValorNeto=@pmValorNeto,ValorCCobrar=@pmValorCCobrar,BaseIva=@pmBaseIva,BaseRet=@pmBaseRet,TarifaIva=@pmTarifaIva,TarifaRet=@pmTarifaRet,TarifaIca=@pmTarifaIca,IdTarIva=@pmIdTarIva,IdTarRet=@pmIdTarRet,IdTarIca=@pmIdTarIca,MulPlazos=@pmMulPlazos,IdPlazo=@pmIdPlazo,CxPagar=@pmCxPagar,CxcVarios=@pmCxcVarios ,TipEgr=@pmTipEgr,Egreso=@pmEgreso,IdCiaEgr=@pmIdCiaEgr,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Observacion=@pmObservacion,IdEstado=@pmIdEstado,CierreCte=@pmCierreCte,IndFactura=@pmIndFactura,FecUpdate=@pmFecUpdate WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paInsCausacion] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmFactura VARCHAR(15),@pmIdProveedor VARCHAR(16),@pmFecFactura SMALLDATETIME,@pmFecVence SMALLDATETIME,@pmReferencia VARCHAR(20),@pmModalidad VARCHAR(10),@pmIdVehiculo VARCHAR(10),@pmIdPropietario VARCHAR(16),@pmIdConductor VARCHAR(16),@pmVehPropio BIT,@pmValorTotal MONEY ,@pmValorDcto MONEY,@pmValorIva MONEY,@pmValorReteFte MONEY,@pmValorReteIva MONEY,@pmValorReteIca MONEY,@pmValorOtros MONEY,@pmValorFletes MONEY,@pmValorNeto MONEY,@pmValorCCobrar MONEY,@pmBaseIva MONEY,@pmBaseRet MONEY,@pmTarifaIva DECIMAL(14,4),@pmTarifaRet DECIMAL(14,4),@pmTarifaIca DECIMAL(14,4),@pmIdTarIva VARCHAR(4),@pmIdTarRet VARCHAR(4),@pmIdTarIca VARCHAR(4),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4) ,@pmCxPagar BIT,@pmCxcVarios BIT,@pmTipEgr VARCHAR(3),@pmEgreso INT,@pmIdCiaEgr CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmCierreCte BIT,@pmIndFactura INT,@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11) AS INSERT INTO Trn_Causacion (TipDoc,Documento,IdCia,Fecha,IdConcepto,Factura,IdProveedor,FecFactura,FecVence,Referencia,Modalidad,IdVehiculo,IdPropietario,IdConductor,VehPropio,ValorTotal,ValorDcto,ValorIva,ValorReteFte,ValorReteIva,ValorReteIca,ValorOtros,ValorFletes,ValorNeto,ValorCCobrar,BaseIva,BaseRet,TarifaIva,TarifaRet,TarifaIca,IdTarIva,IdTarRet,IdTarIca,MulPlazos,IdPlazo,CxPagar,CxcVarios,TipEgr,Egreso,IdCiaEgr,Anulado,NumDev,FecDev,TipCom,Comprobante,IdCiaCom ,Observacion,IdEstado,CierreCte,TimeSys,IdCiaCrea,IdUsuario,IndFactura) VALUES (@pmTipDoc,@pmDocumento,@pmIdCia,@pmFecha,@pmIdConcepto,@pmFactura,@pmIdProveedor,@pmFecFactura,@pmFecVence,@pmReferencia,@pmModalidad,@pmIdVehiculo,@pmIdPropietario,@pmIdConductor,@pmVehPropio,@pmValorTotal,@pmValorDcto,@pmValorIva,@pmValorReteFte,@pmValorReteIva,@pmValorReteIca,@pmValorOtros,@pmValorFletes,@pmValorNeto,@pmValorCCobrar,@pmBaseIva,@pmBaseRet,@pmTarifaIva,@pmTarifaRet,@pmTarifaIca,@pmIdTarIva,@pmIdTarRet,@pmIdTarIca,@pmMulPlazos ,@pmIdPlazo,@pmCxPagar,@pmCxcVarios,@pmTipEgr,@pmEgreso,@pmIdCiaEgr,@pmAnulado,@pmNumDev,@pmFecDev,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmObservacion,@pmIdEstado,@pmCierreCte,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmIndFactura) GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paQryCausacion] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2) AS SELECT TipDoc,Documento,IdCia,Fecha,IdConcepto,Factura,IdProveedor,FecFactura,FecVence,Referencia,Modalidad,IdVehiculo,IdPropietario,IdConductor,VehPropio,ValorTotal,ValorDcto,ValorIva,ValorReteFte,ValorReteIva,ValorReteIca,ValorOtros,ValorFletes,ValorNeto,ValorCCobrar,BaseIva,BaseRet,TarifaIva,TarifaRet,TarifaIca,IdTarIva,IdTarRet,IdTarIca,MulPlazos,IdPlazo,CxPagar,CxcVarios,TipEgr,Egreso,IdCiaEgr,Anulado,NumDev,FecDev,TipCom,Comprobante,IdCiaCom ,Observacion,IdEstado,CierreCte,IndFactura,TimeSys,FecUpdate,IdCiaCrea,IdUsuario FROM Trn_Causacion WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paUpFijos_Cue] @pmIdCargo VARCHAR(4), @pmIdAdmon VARCHAR(4),@pmIdCuentaDeb VARCHAR(16), @pmIdCuentaCre VARCHAR(16) ,@pmIdCueDebVp VARCHAR(16),@pmIdCueCreVp VARCHAR(16),@pmIdCuentaIva VARCHAR(16),@pmIdCuentaRet VARCHAR(16),@pmIdCuentaIca VARCHAR(16) AS UPDATE Fijos_Cue SET IdCuentaDeb=@pmIdCuentaDeb,IdCuentaCre=@pmIdCuentaCre,IdCueDebVp=@pmIdCueDebVp,IdCueCreVp=@pmIdCueCreVp ,IdCuentaIva=@pmIdCuentaIva,IdCuentaRet=@pmIdCuentaRet,IdCuentaIca=@pmIdCuentaIca WHERE IdCargo=@pmIdCargo AND IdAdmon=@pmIdAdmon GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[paQryFijos_Cue] @pmIdCargo VARCHAR(4),@pmIdAdmon VARCHAR(4) AS SELECT IdCargo,IdAdmon,IdCuentaDeb,IdCuentaCre,IdCueDebVp,IdCueCreVp,IdCuentaIva,IdCuentaRet,IdCuentaIca FROM Fijos_Cue WHERE IdCargo=@pmIdCargo AND IdAdmon LIKE ISNULL(@pmIdAdmon,'%') GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[paInsFijos_Cue] @pmIdCargo VARCHAR(4), @pmIdAdmon VARCHAR(4),@pmIdCuentaDeb VARCHAR(16), @pmIdCuentaCre VARCHAR(16) , @pmIdCueDebVp VARCHAR(16),@pmIdCueCreVp VARCHAR(16),@pmIdCuentaIva VARCHAR(16),@pmIdCuentaRet VARCHAR(16),@pmIdCuentaIca VARCHAR(16) AS INSERT INTO Fijos_Cue (IdCargo,IdAdmon,IdCuentaDeb,IdCuentaCre,IdCueDebVp,IdCueCreVp,IdCuentaIva,IdCuentaRet,IdCuentaIca) VALUES (@pmIdCargo,@pmIdAdmon,@pmIdCuentaDeb,@pmIdCuentaCre,@pmIdCueDebVp,@pmIdCueCreVp,@pmIdCuentaIva,@pmIdCuentaRet,@pmIdCuentaIca) GO