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

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

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

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

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

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

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

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paDelFcNotas @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2)
 AS 
 DELETE FROM Trn_FcNotas
 WHERE TipDoc=@pmTipDoc AND NumNota=@pmNumNota AND 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 paInsFcNotas @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdVehiculo VARCHAR(10),@pmIdPropietario VARCHAR(16),@pmIdConductor VARCHAR(16),@pmVehPropio BIT
,@pmVrNota MONEY,@pmVrNeto MONEY,@pmVrAplicado MONEY,@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16),@pmReferencia VARCHAR(50),@pmTipRec VARCHAR(3),@pmReciboFc INT,@pmIdCiaRec CHAR(2),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2)
,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_FcNotas (TipDoc,NumNota,IdCia,Fecha,IdConcepto,IdVehiculo,IdPropietario,IdConductor,VehPropio,VrNota,VrNeto,VrAplicado,CdCCosto,CdSubCos,Referencia,TipRec,ReciboFc,IdCiaRec,TipCom,Comprobante,IdCiaCom,Anulado,FecDev,Observacion,IdEstado,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmNumNota,@pmIdCia,@pmFecha,@pmIdConcepto,@pmIdVehiculo,@pmIdPropietario,@pmIdConductor,@pmVehPropio,@pmVrNota,@pmVrNeto,@pmVrAplicado,@pmCdCCosto,@pmCdSubCos,@pmReferencia,@pmTipRec,@pmReciboFc,@pmIdCiaRec
,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_Semanas_Sel  @pmTipRec VARCHAR(3),@pmRecibo INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_Semanas (tmNumero,tmItem,tmVehiculo,tmSemana,tmVrTotal,tmVrPagado,tmDescripcion)
 SELECT  @pmtmNumero,Item, IdVehiculo, nSemana
,0,CASE WHEN VrAbono>0  THEN VrAbono ELSE VrAbono*-1 END,Descripcion
 FROM   Trn_FcAbonos
 WHERE  TipRec=@pmTipRec  AND Recibo=@pmRecibo  AND IdCia=@pmIdCia

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryCausacVenceLca @pmIdVehiculo VARCHAR(10)
AS
SELECT  TipDoc, Documento, IdCia, Item,V.IdCargo AS IdCarg,Concepto,Detalle,ValorFactura-ValorAbono AS ValorSaldo,ValorFactura,ValorAbono,FecEmision,FecVence, IdCliente,T.RazonSocial AS NomCliente,IdAgencia
, IdVehiculo,VehPropio,IdVendedor,C.RazonSocial AS Conductor,V.IdClase AS CodClase,ClaseCuenta,Factura, Referencia,TipRec, Recibo, IdCiaRec,ValorAtcpo,CombCant
 FROM   Trn_CausacVence AS V INNER JOIN Terceros AS T ON  V.IdCliente=T.IdTercero
 INNER JOIN  BusClaseCue AS U ON  V.IdClase=U.IdClase
 INNER JOIN Terceros AS C ON  V.IdVendedor=C.IdTercero
 INNER JOIN  Fijos AS F ON  V.IdCargo=F.IdCargo
 WHERE  ValorFactura>ValorAbono  AND  IdVehiculo=@pmIdVehiculo
ORDER BY Prioridad,FecVence
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryFcNotas @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumNota,IdCia,Fecha,IdConcepto,IdVehiculo,IdPropietario,IdConductor,VehPropio,VrNota,VrNeto,VrAplicado,CdCCosto,CdSubCos
,Referencia,TipRec,ReciboFc,IdCiaRec,TipCom,Comprobante,IdCiaCom,Anulado,FecDev,Observacion,IdEstado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_FcNotas
 WHERE TipDoc=@pmTipDoc AND NumNota=@pmNumNota AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFcNotasFmt @pmTipDoc VARCHAR(3),@pmNumNotaIni INT,@pmNumNotaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT  NumNota,N.IdCia AS CdCia,Compania,N.Fecha AS FecNota,N.IdConcepto AS CdConcepto,Concepto,N.IdVehiculo AS PlacaVeh,NumVeh,N.IdPropietario AS NitPropietario,T.RazonSocial AS Propietario,N.IdConductor AS CedConductor,CDT.RazonSocial AS Conductor
,VehPropio,VrNota,VrNeto,VrAplicado,CdCCosto,CCosto,CdSubCos,SubCosto,Referencia,N.TipRec AS TipRecFc,ReciboFc,IdCiaRec,TipCom,TipoCom,Comprobante,IdCiaCom
,Anulado,FecDev,N.Observacion AS Observ,N.IdEstado AS CdEstado,Estado,OrigenAdd,TimeSys,IdCiaCrea,N.IdUsuario AS CdUsuario,Usuario,TipDoc
--datos detalle
,Item,nSemana,VrAbono,A.Descripcion AS DetallePago
--datos del vehículo
,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,Modelo,TipoAfil,FecIngreso,V.Inactivo AS VehInactivo
--tablas
 FROM Trn_FcNotas AS N INNER JOIN Companias AS CI ON N.IdCia=CI.IdCia INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario
  INNER JOIN  Conceptos AS CN ON N.IdConcepto=CN.IdConcepto  INNER JOIN Terceros AS T ON N.IdPropietario=T.IdTercero
  INNER JOIN EstadoDoc AS ED ON N.IdEstado=ED.IdEstado
  INNER JOIN Vehiculos AS V ON N.IdVehiculo=V.IdVehiculo 
  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca
  LEFT JOIN Terceros AS CDT ON N.IdConductor=CDT.IdTercero LEFT JOIN CentroCosto AS CC ON N.CdCCosto=CC.IdCCosto
  LEFT JOIN  SubCentros AS SC ON N.CdSubCos=SC.IdSubCos LEFT JOIN TiposCom AS TCM ON N.TipCom=TCM.IdCom
  LEFT JOIN Trn_FcAbonos AS A ON N.TipDoc=A.TipRec  AND N.NumNota=A.Recibo AND N.IdCia=A.IdCia
  WHERE N.TipDoc=@pmTipDoc AND N.NumNota BETWEEN @pmNumNotaIni AND @pmNumNotaFin AND N.IdCia=@pmIdCia
  ORDER BY NumNota
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFcNotasLta @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPropietario VARCHAR(16)=Null
 AS 
 SELECT TipDoc,NumNota,N.IdCia AS CdCia,Compania,N.Fecha AS FecNota,N.IdConcepto AS CdConcepto,Concepto,N.IdVehiculo AS PlacaVeh,NumVeh,N.IdPropietario AS NitPropietario,T.RazonSocial AS Propietario,N.IdConductor AS CedConductor,CDT.RazonSocial AS Conductor
,VehPropio,VrNota,VrNeto,VrAplicado,CdCCosto,CCosto,CdSubCos,SubCosto,Referencia,N.TipRec AS TipRecFc,ReciboFc,IdCiaRec,TipCom,TipoCom,Comprobante,IdCiaCom
,Anulado,FecDev,N.Observacion AS Observ,N.IdEstado AS CdEstado,Estado,OrigenAdd,TimeSys,IdCiaCrea,N.IdUsuario AS CdUsuario,Usuario
--datos del vehículo
,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,Modelo,TipoAfil,FecIngreso,V.Inactivo AS VehInactivo
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,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
--tablas
 FROM Trn_FcNotas AS N INNER JOIN Companias AS CI ON N.IdCia=CI.IdCia INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario
  INNER JOIN  Conceptos AS CN ON N.IdConcepto=CN.IdConcepto  INNER JOIN Terceros AS T ON N.IdPropietario=T.IdTercero
  INNER JOIN EstadoDoc AS ED ON N.IdEstado=ED.IdEstado
  INNER JOIN Vehiculos AS V ON N.IdVehiculo=V.IdVehiculo 
  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca
  INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
  LEFT JOIN Terceros AS CDT ON N.IdConductor=CDT.IdTercero LEFT JOIN CentroCosto AS CC ON N.CdCCosto=CC.IdCCosto
  LEFT JOIN  SubCentros AS SC ON N.CdSubCos=SC.IdSubCos LEFT JOIN TiposCom AS TCM ON N.TipCom=TCM.IdCom
  WHERE N.TipDoc=@pmTipDoc AND N.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND N.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND N.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND N.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
 AND N.IdPropietario LIKE ISNULL(@pmIdPropietario,'%')
 ORDER BY N.IdCia,NumNota
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpFcNotas @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdVehiculo VARCHAR(10),@pmIdPropietario VARCHAR(16),@pmIdConductor VARCHAR(16),@pmVehPropio BIT,@pmVrNota MONEY,@pmVrNeto MONEY,@pmVrAplicado MONEY
,@pmCdCCosto VARCHAR(16),@pmCdSubCos VARCHAR(16),@pmReferencia VARCHAR(50),@pmTipRec VARCHAR(3),@pmReciboFc INT,@pmIdCiaRec CHAR(2),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_FcNotas SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,IdVehiculo=@pmIdVehiculo,IdPropietario=@pmIdPropietario,IdConductor=@pmIdConductor,VehPropio=@pmVehPropio,VrNota=@pmVrNota,VrNeto=@pmVrNeto,VrAplicado=@pmVrAplicado,CdCCosto=@pmCdCCosto,CdSubCos=@pmCdSubCos
,Referencia=@pmReferencia,TipRec=@pmTipRec,ReciboFc=@pmReciboFc,IdCiaRec=@pmIdCiaRec,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND NumNota=@pmNumNota AND IdCia=@pmIdCia

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpFcNotasDev @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2)
,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4)
 AS 
 UPDATE Trn_FcNotas SET  Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion
,IdEstado=@pmIdEstado
 WHERE TipDoc=@pmTipDoc AND NumNota=@pmNumNota AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO