if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsMercancias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsMercancias]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_BalCom_SelAcc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_BalCom_SelAcc]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_IntAhorro]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_IntAhorro]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_MovCue_Acc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_MovCue_Acc]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_OccComp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_OccComp]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAprobacionLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryAprobacionLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFacturasCom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryFacturasCom]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMercancias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryMercancias]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMercanciasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryMercanciasLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRecBusesIca]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryRecBusesIca]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRecBusesNetIca]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryRecBusesNetIca]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_BalCom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrytm_BalCom]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_CargosCmp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrytm_CargosCmp]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_IntAhorro]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrytm_IntAhorro]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_IntAhorroLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrytm_IntAhorroLta]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVehiculosNom]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryVehiculosNom]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpMercancias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpMercancias]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsMercancias @pmIdMercancia VARCHAR(16),@pmDescripMcia VARCHAR(150),@pmCodigoMcia VARCHAR(16),@pmIdGrupo VARCHAR(10),@pmUndMed VARCHAR(10)
,@pmIdUnd VARCHAR(4),@pmIdNat VARCHAR(4),@pmIdMnjo VARCHAR(4),@pmIdTmcia VARCHAR(4),@pmContenedor BIT,@pmIdProducto VARCHAR(16),@pmIdEstado VARCHAR(4),@pmInactivo BIT
,@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Mercancias (IdMercancia,DescripMcia,CodigoMcia,IdGrupo,UndMed,IdUnd,IdNat,IdMnjo,IdTmcia,Contenedor,IdProducto,IdEstado,Inactivo,FechaAdd,IdUsuario)
 VALUES (@pmIdMercancia,@pmDescripMcia,@pmCodigoMcia,@pmIdGrupo,@pmUndMed,@pmIdUnd,@pmIdNat,@pmIdMnjo,@pmIdTmcia,@pmContenedor,@pmIdProducto
,@pmIdEstado,@pmInactivo,@pmFechaAdd,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE  paInstm_BalCom_SelAcc @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCuenta VARCHAR(16)=Null,@pmIdTercero VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdCCosto VARCHAR(16)=Null
,@pmIdSubCos VARCHAR(16)=Null,@pmCueIni VARCHAR(16)=Null,@pmCueFin VARCHAR(16)=Null,@pmCodAgncia VARCHAR(16)=Null
AS
--terceros Accionistas
 IF @pmCueIni IS NULL --NO RANGOS
  INSERT INTO tm_BalCom (tmEst,tmIdCuenta,tmItem,tmIdTercero,tmIdVehiculo,tmIdCCosto,tmIdSubCos,tmCodAgncia,tmTipo,tmSaldoAnterior,tmNuevoSaldo,tmTotalDebitos,tmTotalCreditos)
  SELECT @pmtmEst,IdCuenta,0,'0','0','0','0','0','0',0,0,SUM(VrDebito),SUM(VrCredito)
  FROM   Trn_ComDetalle AS D INNER JOIN Terceros AS T ON D.IdTercero=T.IdTercero
  WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (EsAccnista<>0 OR D.IdTercero='0') AND IdCia LIKE ISNULL(@pmIdCia,'%%')
  AND IdCuenta LIKE ISNULL(@pmIdCuenta,'%')
  AND D.IdTercero LIKE ISNULL(@pmIdTercero,'%')  AND IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
  AND IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND IdSubCos LIKE ISNULL(@pmIdSubCos,'%')
  AND CodAgncia LIKE ISNULL(@pmCodAgncia,'%')
  GROUP BY  IdCuenta
 ELSE --RANGOS 
  INSERT INTO tm_BalCom (tmEst,tmIdCuenta,tmItem,tmIdTercero,tmIdVehiculo,tmIdCCosto,tmIdSubCos,tmCodAgncia,tmTipo,tmSaldoAnterior,tmNuevoSaldo,tmTotalDebitos,tmTotalCreditos)
  SELECT @pmtmEst,IdCuenta,0,'0','0','0','0','0','0',0,0,SUM(VrDebito),SUM(VrCredito)
  FROM   Trn_ComDetalle AS D INNER JOIN Terceros AS T ON D.IdTercero=T.IdTercero
  WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (EsAccnista<>0 OR D.IdTercero='0') AND IdCuenta BETWEEN @pmCueIni AND @pmCueFin
  AND IdCia LIKE ISNULL(@pmIdCia,'%%')
  AND D.IdTercero LIKE ISNULL(@pmIdTercero,'%')  AND IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
  AND IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND IdSubCos LIKE ISNULL(@pmIdSubCos,'%')
 AND CodAgncia LIKE ISNULL(@pmCodAgncia,'%')
  GROUP BY  IdCuenta
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_IntAhorro @pmtmEst CHAR(2),@pmtmItem INT,@pmtmIdVehiculo VARCHAR(10),@pmtmIdTercero VARCHAR(16),@pmtmSaldoBase MONEY
,@pmtmTasa DECIMAL(14,4),@pmtmValor MONEY,@pmtmNmes INT,@pmtmVehPropio BIT,@pmtmIdConductor VARCHAR(16),@pmtmIdPropietario VARCHAR(16)
, @pmtmConductor VARCHAR(100),@pmtmTercero VARCHAR(100),@pmtmRetencion MONEY,@pmtmVrAbono MONEY
 AS
 INSERT INTO tm_IntAhorro (tmEst, tmItem, tmIdVehiculo, tmIdTercero, tmSaldoBase, tmTasa, tmValor, tmNmes, tmVehPropio, tmIdConductor, tmIdPropietario, tmConductor,tmTercero,tmRetencion,tmVrAbono)
 VALUES (@pmtmEst, @pmtmItem, @pmtmIdVehiculo, @pmtmIdTercero, @pmtmSaldoBase, @pmtmTasa, @pmtmValor, @pmtmNmes, @pmtmVehPropio, @pmtmIdConductor
, @pmtmIdPropietario, @pmtmConductor,@pmtmTercero,@pmtmRetencion,@pmtmVrAbono)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_MovCue_Acc @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
,@pmIdTercero VARCHAR(16)=Null,@pmIdCCosto VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdSubCos VARCHAR(16)=Null,@pmCodAgncia VARCHAR(16)=Null
,@pmTipCom VARCHAR(3)=Null,@pmIntegrado BIT=Null,@pmCueIni VARCHAR(16)=Null,@pmCueFin VARCHAR(16)=Null
 AS 
 IF @pmCueIni IS NULL
  INSERT INTO tm_MovCue (tmEst,TipCom,Comprobante,IdCia,Item,Fecha,IdCuenta,Detalle,VrDebito,VrCredito,IdTercero,IdVehiculo,IdCCosto,IdSubCos,VrBase,TarifaBase,TipDoc,Documento,IdCiaDoc,CodConce,NitDoc,TipFac,Factura
 ,IdCiaFac,ItemFac,FecVence,CodCta,NumCheque,Integrado,TipoAplica,Consolida,CodCargo,NitOtros,CodSubgpo,CiuOrigen,CodAgncia,VehPropio,Referncia,TipDocRef,DocRef,IdCiaRef)
  SELECT  @pmtmEst,TipCom,Comprobante,IdCia,Item,Fecha,IdCuenta,Detalle,VrDebito,VrCredito,D.IdTercero,IdVehiculo,IdCCosto,IdSubCos,VrBase,TarifaBase,TipDoc,Documento,IdCiaDoc,CodConce,NitDoc,TipFac,Factura
  ,IdCiaFac,ItemFac,FecVence,CodCta,NumCheque,Integrado,TipoAplica,Consolida,CodCargo,NitOtros,CodSubgpo,CiuOrigen,CodAgncia,VehPropio,Referncia,TipDocRef,DocRef,IdCiaRef
  FROM Trn_ComDetalle AS D INNER JOIN Terceros AS T ON D.IdTercero=T.IdTercero
  WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND EsAccnista<>0 AND  IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND IdCuenta LIKE ISNULL(@pmIdCuenta,'%') AND D.IdTercero LIKE ISNULL(@pmIdTercero,'%') AND IdCCosto LIKE ISNULL(@pmIdCCosto,'%')
 AND IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND IdSubCos LIKE ISNULL(@pmIdSubCos,'%') AND CodAgncia LIKE ISNULL(@pmCodAgncia,'%')
 AND TipCom LIKE ISNULL(@pmTipCom,'%')
 AND  (Integrado=ISNULL(@pmIntegrado,0) or  Integrado=ISNULL(@pmIntegrado,1))
ELSE
  INSERT INTO tm_MovCue (tmEst,TipCom,Comprobante,IdCia,Item,Fecha,IdCuenta,Detalle,VrDebito,VrCredito,IdTercero,IdVehiculo,IdCCosto,IdSubCos,VrBase,TarifaBase,TipDoc,Documento,IdCiaDoc,CodConce,NitDoc,TipFac,Factura
 ,IdCiaFac,ItemFac,FecVence,CodCta,NumCheque,Integrado,TipoAplica,Consolida,CodCargo,NitOtros,CodSubgpo,CiuOrigen,CodAgncia,VehPropio,Referncia,TipDocRef,DocRef,IdCiaRef)
  SELECT  @pmtmEst,TipCom,Comprobante,IdCia,Item,Fecha,IdCuenta,Detalle,VrDebito,VrCredito,D.IdTercero,IdVehiculo,IdCCosto,IdSubCos,VrBase,TarifaBase,TipDoc,Documento,IdCiaDoc,CodConce,NitDoc,TipFac,Factura
  ,IdCiaFac,ItemFac,FecVence,CodCta,NumCheque,Integrado,TipoAplica,Consolida,CodCargo,NitOtros,CodSubgpo,CiuOrigen,CodAgncia,VehPropio,Referncia,TipDocRef,DocRef,IdCiaRef
  FROM Trn_ComDetalle AS D INNER JOIN Terceros AS T ON D.IdTercero=T.IdTercero
  WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND IdCuenta BETWEEN @pmCueIni AND @pmCueFin AND EsAccnista<>0
 AND IdCia LIKE ISNULL(@pmIdCia,'%%')  AND D.IdTercero LIKE ISNULL(@pmIdTercero,'%') AND IdCCosto LIKE ISNULL(@pmIdCCosto,'%')
 AND IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND IdSubCos LIKE ISNULL(@pmIdSubCos,'%') AND CodAgncia LIKE ISNULL(@pmCodAgncia,'%')
 AND TipCom LIKE ISNULL(@pmTipCom,'%')
 AND  (Integrado=ISNULL(@pmIntegrado,0) or  Integrado=ISNULL(@pmIntegrado,1))
  
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_OccComp @pmtmTipDoc VARCHAR(3),@pmtmOCargue INT,@pmtmIdCia CHAR(2)
,@pmtmNumComp INT,@pmtmIdProducto VARCHAR(16),@pmtmCantidad DECIMAL(14,4),@pmtmDescProd VARCHAR(50)
,@pmtmComp2 INT,@pmtmIdProd2 VARCHAR(16),@pmtmDescProd2 VARCHAR(50),@pmtmCantidad2 DECIMAL(14,4)
,@pmtmComp3 INT,@pmtmIdProd3 VARCHAR(16),@pmtmDescProd3 VARCHAR(50),@pmtmCantidad3 DECIMAL(14,4)
,@pmtmComp4 INT,@pmtmIdProd4 VARCHAR(16),@pmtmDescProd4 VARCHAR(50),@pmtmCantidad4 DECIMAL(14,4)
,@pmtmComp5 INT,@pmtmIdProd5 VARCHAR(16),@pmtmDescProd5 VARCHAR(50),@pmtmCantidad5 DECIMAL(14,4)
 AS 
 INSERT INTO tm_OccComp (tmTipDoc,tmOCargue,tmIdCia,tmNumComp,tmIdProducto,tmCantidad,tmDescProd,tmComp2,tmIdProd2,tmDescProd2,tmCantidad2
,tmComp3,tmIdProd3,tmDescProd3,tmCantidad3,tmComp4,tmIdProd4,tmDescProd4,tmCantidad4,tmComp5,tmIdProd5,tmDescProd5,tmCantidad5)
 VALUES (@pmtmTipDoc,@pmtmOCargue,@pmtmIdCia,@pmtmNumComp,@pmtmIdProducto,@pmtmCantidad,@pmtmDescProd,@pmtmComp2,@pmtmIdProd2
,@pmtmDescProd2,@pmtmCantidad2,@pmtmComp3,@pmtmIdProd3,@pmtmDescProd3,@pmtmCantidad3,@pmtmComp4,@pmtmIdProd4,@pmtmDescProd4
,@pmtmCantidad4,@pmtmComp5,@pmtmIdProd5,@pmtmDescProd5,@pmtmCantidad5)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryAprobacionLta @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME
,@pmAprobacionIni INT=Null,@pmAprobacionFin INT=Null,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null
,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null,@pmPedido INT=Null
 AS 
 SELECT  Aprobacion,A.IdCia AS CdCia,Compania,Fecha,TipoPed,Pedido,IdCiaPed,FecPedido,IdCliente,RazonSocial,IdAgencia
,pVehiculo,VrCupoCred,VrSaldoAct,ChequesDev,ClienteMora,OrigenAdd,Anulado,FecDev,A.Observacion AS Observ
,A.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,A.IdUsuario AS IdUsuari,Usuario
 FROM Trn_Aprobacion AS A INNER JOIN Terceros AS T ON A.IdCliente=T.IdTercero
 INNER JOIN EstadoDoc AS ED ON A.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario 
 INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia
 WHERE TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
 AND Aprobacion BETWEEN ISNULL(@pmAprobacionIni,0) AND ISNULL(@pmAprobacionFin,2147483647)
  AND A.IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') 
  AND A.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 AND (Pedido>=ISNULL(@pmPedido,0)  AND Pedido<=ISNULL(@pmPedido,2147483647))
 ORDER BY A.IdCia,Aprobacion
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFacturasCom @pmTipDoc VARCHAR(3),@pmFacturaIni INT,@pmFacturaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Factura,F.IdCia AS CdCia,Compania,Fecha,IdCliente,T.RazonSocial AS NomCliente,F.IdAgencia AS IdAgenc,A.Agencia AS NomAgencia
,A.CodAgencia AS Cod_Agencia,F.IdVend AS NitVend,VN.RazonSocial AS Vendedor
,DirEnvio,F.IdLocEnv AS IdCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio
,F.NitContac AS FacNitContac,F.NomContac AS FacNomContac,F.TelContac AS FacTelContac,F.emlContac AS FacEmailContac,CargoContac
,NitEmpTrans,EmpTrans,F.pVehiculo AS PlacaVeh,CdConductor,TC.RazonSocial AS Conductor,CdRuta,Ruta,TipPed,Pedido,IdCiaPed
,TipRem,F.Remision AS NumGuia,F.IdCiaRem AS CdCiaGuia,Modalidad,Anulado,NumDev,FecDev,F.Observacion AS Observ,F.IdUsuario AS CdUsuario,Usuario
--datos del contenedor
,tmNumComp,tmIdProducto,tmCantidad,tmDescProd,tmComp2,tmIdProd2,tmDescProd2,tmCantidad2
,tmComp3,tmIdProd3,tmDescProd3,tmCantidad3,tmComp4,tmIdProd4,tmDescProd4,tmCantidad4
,tmComp5,tmIdProd5,tmDescProd5,tmCantidad5
 FROM Trn_Facturas AS F  INNER JOIN Companias AS CN ON F.IdCia=CN.IdCia 
 INNER JOIN Terceros AS T ON F.IdCliente=T.IdTercero  INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario
 INNER JOIN  Terceros AS VN ON F.IdVend=VN.IdTercero
 INNER JOIN Agencias  AS A ON F.IdAgencia=A.IdAgencia 
 LEFT JOIN tm_OccComp AS CMP ON F.TipDoc =CMP.tmTipDoc  AND F.Factura=CMP.tmOCargue AND F.IdCia=CMP.tmIdCia
 LEFT JOIN  Localidades AS LE ON F.IdLocEnv=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Terceros AS TC ON F.CdConductor=TC.IdTercero LEFT JOIN Rutas AS R ON F.CdRuta=R.IdRuta
 WHERE  TipDoc=@pmTipDoc AND Factura BETWEEN @pmFacturaIni AND @pmFacturaFin AND F.IdCia=@pmIdCia

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryMercancias @pmIdMercancia VARCHAR(16)
 AS 
 SELECT IdMercancia,DescripMcia,CodigoMcia,IdGrupo,UndMed,IdUnd,IdNat,IdMnjo,IdTmcia,Contenedor,IdProducto
,IdEstado,Inactivo,FechaAdd,FechaUpdate,IdUsuario
 FROM Mercancias
 WHERE IdMercancia=@pmIdMercancia

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryMercanciasLta @pmIdGrupo VARCHAR(10)=Null,@pmIdNat VARCHAR(4)=Null,@pmIdTmcia VARCHAR(4)=Null
,@pmInactivo BIT=Null
 AS 
 SELECT IdMercancia,DescripMcia,M.IdGrupo AS CdGrupo,GrupoMcia,M.UndMed AS Und_Med,M.IdUnd AS CdUndPre,Unidad,UM.IdEmp AS CdEmp
,M.IdNat AS CdNat,Natlzaprod,M.IdMnjo AS CdMnjo,ManejoMcia,M.IdTmcia AS CdTmcia,TipoMcia,Contenedor,M.IdProducto AS CdProducto,DescripProd,CodigoMcia
,M.IdEstado AS CdEstado,Estado,M.Inactivo AS Inactvo,M.FechaAdd AS FechaCrea,M.FechaUpdate AS FechaAct,M.IdUsuario AS CdUsuario,Usuario
 FROM Mercancias AS M INNER JOIN GruposMcia AS G ON M.IdGrupo=G.IdGrupo
 INNER JOIN UndMed AS UM ON M.IdUnd=UM.IdUnd    INNER JOIN TiposNat AS N ON M.IdNat=N.IdNat
 INNER JOIN TiposMnjo AS MM ON M.IdMnjo=MM.IdMnjo INNER JOIN TiposMcia AS TM ON M.IdTmcia=TM.IdTmcia
 INNER JOIN EstadoPro AS EP ON M.IdEstado=EP.IdEstado  INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario 
 LEFT JOIN ProdMcias AS P ON M.IdProducto=P.IdProducto
 WHERE M.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND M.IdNat LIKE ISNULL(@pmIdNat,'%') AND M.IdTmcia LIKE ISNULL(@pmIdTmcia,'%')
  AND (M.Inactivo=ISNULL(@pmInactivo,0) or  M.Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY DescripMcia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE  paQryRecBusesIca @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
AS
 SELECT IdCia,IdCliente,R.IdVehiculo AS PlacaVeh,NumVeh,R.IdConductor AS CedConductor,SUM(ValorNeto) AS Total_Neto
 FROM  Trn_RecBuses AS R INNER JOIN Vehiculos AS V ON R.IdVehiculo=V.IdVehiculo
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  VehPropio=0 AND ValorNeto>0--TipoAfil<>'PROPIO' 
 AND  IdCia LIKE ISNULL(@pmIdCia,'%%') 
 GROUP BY  IdCia,IdCliente,R.IdVehiculo,NumVeh,R.IdConductor
 UNION 
 SELECT IdCia,IdCliente,D.IdVehiculo AS PlacaVeh,NumVeh,D.IdConductor AS CedConductor,SUM(ValorNeto*-1) AS Total_Neto
 FROM     Trn_DevRecBuses AS D  INNER JOIN Vehiculos AS V ON D.IdVehiculo=V.IdVehiculo
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  VehPropio=0 AND ValorNeto>0 --TipoAfil<>'PROPIO' 
 AND  IdCia LIKE ISNULL(@pmIdCia,'%%') 
 GROUP BY  IdCia,IdCliente,D.IdVehiculo,NumVeh,D.IdConductor
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE  paQryRecBusesNetIca @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
AS
 SELECT TipoDoc,R.IdCia AS CdCia,Compania,IdCliente,T.RazonSocial AS NomCliente,R.IdVehiculo AS PlacaVeh,NumVeh,R.IdConductor AS CedConductor,CD.RazonSocial AS NomConductor
,SUM(ValorNeto) AS Total_Neto
 FROM  Trn_RecBuses AS R INNER JOIN Vehiculos AS V ON R.IdVehiculo=V.IdVehiculo
 INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero INNER JOIN Terceros AS CD ON R.IdConductor=CD.IdTercero
 INNER JOIN Companias AS C ON R.IdCia=C.IdCia INNER JOIN Sys_TiposDoc AS TD ON R.TipDoc=TD.IdDoc
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  VehPropio=0 AND ValorNeto>0--TipoAfil<>'PROPIO' 
 AND  R.IdCia LIKE ISNULL(@pmIdCia,'%%') 
 GROUP BY  TipoDoc,R.IdCia,Compania,IdCliente,T.RazonSocial,R.IdVehiculo,NumVeh,R.IdConductor,CD.RazonSocial
 UNION 
 SELECT TipoDoc,R.IdCia AS CdCia,Compania,IdCliente,T.RazonSocial AS NomCliente,R.IdVehiculo AS PlacaVeh,NumVeh,R.IdConductor AS CedConductor,CD.RazonSocial AS NomConductor
,SUM(ValorNeto*-1) AS Total_Neto
 FROM  Trn_DevRecBuses AS R INNER JOIN Vehiculos AS V ON R.IdVehiculo=V.IdVehiculo
 INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero INNER JOIN Terceros AS CD ON R.IdConductor=CD.IdTercero
 INNER JOIN Companias AS C ON R.IdCia=C.IdCia INNER JOIN Sys_TiposDoc AS TD ON R.TipDev=TD.IdDoc
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  VehPropio=0 AND ValorNeto>0--TipoAfil<>'PROPIO' 
 AND  R.IdCia LIKE ISNULL(@pmIdCia,'%%') 
 GROUP BY  TipoDoc,R.IdCia,Compania,IdCliente,T.RazonSocial,R.IdVehiculo,NumVeh,R.IdConductor,CD.RazonSocial
 ORDER BY R.IdCia,R.IdVehiculo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_BalCom @pmtmEst CHAR(2),@pmtmIdCuenta VARCHAR(16)=Null
,@pmtmIdTercero VARCHAR(16)=Null,@pmtmCodAgncia VARCHAR(16)=Null
 AS 
 SELECT BC.tmIdCuenta AS IdCuent,NomCuenta,tmTipo,tmSaldoAnterior,tmTotalDebitos,tmTotalCreditos
,tmSaldoAnterior+tmTotalDebitos-tmTotalCreditos AS NuevoSaldo
,tmIdTercero,RazonSocial,tmCodAgncia,A.Agencia AS NomAgencia,tmIdVehiculo,tmIdCCosto,CCosto,tmIdSubCos,SubCosto
,tmVrPresupto
--información del tercero
,TipoId,Dv,Direccion,T.IdLocal AS CdCiudad,Localidad,L.IdDep AS CdDepto,Departamento,Telefono,EsAccnista,EsCliente,EsPropietario 
--información de agencia y vehículos
,CodAgencia,Referencia--,NumVeh
,tmItem,tmNivel1,tmNivel2,tmNivel4,tmNivel6,tmNivel9,tmNivel12,Tercero,CentroCosto
 FROM tm_BalCom  AS  BC INNER JOIN Puc AS P ON BC.tmIdCuenta=P.IdCuenta
 INNER JOIN Terceros AS T ON BC.tmIdTercero=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 INNER JOIN CentroCosto AS CC ON BC.tmIdCCosto=CC.IdCCosto
 LEFT JOIN SubCentros AS SC ON BC.tmIdSubCos=SC.IdSubCos
 LEFT JOIN tm_NivCue AS NV ON BC.tmIdCuenta=NV.tmIdCuenta
 LEFT JOIN  Agencias AS A ON BC.tmCodAgncia=A.IdAgencia
-- LEFT JOIN Vehiculos AS V ON BC.tmIdVehiculo=V.IdVehiculo
 WHERE tmEst=@pmtmEst AND BC.tmIdCuenta LIKE ISNULL(@pmtmIdCuenta,'%')
 AND tmIdTercero LIKE ISNULL(@pmtmIdTercero,'%')
 AND tmCodAgncia LIKE ISNULL(@pmtmCodAgncia,'%')
 ORDER BY BC.tmIdCuenta,RazonSocial,tmCodAgncia,tmIdCCosto,tmItem
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_CargosCmp @pmtmNumero VARCHAR(5)
 AS
   SELECT tmIdCliente,tmIdVehiculo,tmVehPropio,tmIdConductor, tmIdCuenta
,SUM(tmValor) AS SValFac,SUM(tmCombValor) AS TotalBase
  FROM    tm_Cargos  WHERE tmNumero=@pmtmNumero  
 GROUP BY tmIdCliente,tmIdVehiculo,tmVehPropio,tmIdConductor,tmIdCuenta

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_IntAhorro @pmtmEst CHAR(2),@pmtmIdVehiculo VARCHAR(10)=Null,@pmtmIdTercero VARCHAR(16)=Null,@pmtmNmes INT=Null
 AS
 SELECT  tmEst, tmItem, tmIdVehiculo, tmIdTercero, tmSaldoBase, tmTasa, tmValor, tmNmes, tmVehPropio, tmIdConductor
, tmIdPropietario, tmConductor,tmTercero,tmRetencion,tmVrAbono
 FROM   tm_IntAhorro
 WHERE tmEst=@pmtmEst AND tmIdVehiculo LIKE ISNULL(@pmtmIdVehiculo,'%')
 AND tmIdTercero LIKE ISNULL(@pmtmIdTercero,'%')
 AND (tmNmes>=ISNULL(@pmtmNmes,0)  AND tmNmes<=ISNULL(@pmtmNmes,2147483647))
 ORDER BY tmItem
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_IntAhorroLta @pmtmEst CHAR(2)
 AS
 SELECT  tmItem, tmIdVehiculo,NumVeh,tmIdTercero,tmTercero,tmSaldoBase,tmVrAbono, tmTasa, tmValor
, tmNmes,tmVehPropio, tmIdConductor,tmConductor, tmIdPropietario
 FROM   tm_IntAhorro AS T INNER JOIN  Vehiculos AS V ON T.tmIdVehiculo=V.IdVehiculo
 WHERE tmEst=@pmtmEst 
 ORDER BY tmIdVehiculo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE paQryVehiculosNom @pmIdVehiculo VARCHAR(10)
 AS 
 SELECT IdVehiculo,NumVeh,ClaseVeh,V.IdTipoVeh AS CdTipo,TipoVehiculo,V.IdMarca AS CdMarca,M.Marca AS MarcaVeh,V.IdLinea AS CdLinea,LineaVeh
,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,FecRep,Config,ClaseMat,V.IdCom AS CdTipComb,TipoComb
,NumMotor,SerieChasis,NumSerie,CdRemque,CarrCapac,UndCapc,Comptmtos,CapComp
,IdPropietario,NP.RazonSocial AS Propietario,IdPoseedor,NT.RazonSocial AS Poseedor,IdConductor,NC.RazonSocial AS Conductor
,V.IdPpd AS CdTipProp,TipoProp,VehPropio,NContrato,Descripcion,CdCenSer,CentroServ,CdLocal,LU.Localidad AS CiuUbicacion
,LU.IdDep AS CodDpto,Departamento,Ubicacion,V.IdEstado AS CdEstado,Estado,V.Inactivo AS Inactvo,EV.NColor AS NumColor,OutDemand
, V.IdNiv AS CdNivel,NivelServicio,TipoAfil
 FROM Vehiculos  AS V INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh
 INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca INNER JOIN MarcasLin AS L ON V.IdLinea=L.IdLinea
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposFuel AS TF ON V.IdCom=TF.IdCom 
 INNER JOIN Terceros AS NP ON  V.IdPropietario=NP.IdTercero  INNER JOIN Terceros AS NT ON  V.IdPoseedor=NT.IdTercero
 INNER JOIN Terceros AS NC ON  V.IdConductor=NC.IdTercero INNER JOIN TiposPpt AS TP ON V.IdPpd=TP.IdPpd
 INNER JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado 
 LEFT JOIN CentrosServ AS CS ON V.CdCenSer =CS.IdCenSer 
 LEFT JOIN Localidades AS LU ON V.CdLocal=LU.IdLocal LEFT JOIN Departamentos AS DU ON LU.IdDep=DU.IdDep
 LEFT JOIN TiposNivs AS NS ON V.IdNiv=NS.IdNiv
 WHERE IdVehiculo=@pmIdVehiculo
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpMercancias @pmIdMercancia VARCHAR(16),@pmDescripMcia VARCHAR(150),@pmCodigoMcia VARCHAR(16),@pmIdGrupo VARCHAR(10),@pmUndMed VARCHAR(10),@pmIdUnd VARCHAR(4)
,@pmIdNat VARCHAR(4),@pmIdMnjo VARCHAR(4),@pmIdTmcia VARCHAR(4),@pmContenedor BIT,@pmIdProducto VARCHAR(16),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmFechaUpdate SMALLDATETIME
 AS 
 UPDATE Mercancias SET DescripMcia=@pmDescripMcia,CodigoMcia=@pmCodigoMcia,IdGrupo=@pmIdGrupo,UndMed=@pmUndMed,IdUnd=@pmIdUnd,IdNat=@pmIdNat,IdMnjo=@pmIdMnjo,IdTmcia=@pmIdTmcia
,Contenedor=@pmContenedor,IdProducto=@pmIdProducto,IdEstado=@pmIdEstado,Inactivo=@pmInactivo,FechaUpdate=@pmFechaUpdate
 WHERE IdMercancia=@pmIdMercancia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO