ALTER TABLE Trn_FcSemanas ADD FecSemIni SMALLDATETIME,FecSemFin SMALLDATETIME
GO
CREATE TABLE Emp_Beneficiarios ( 
IdEmpleado VARCHAR(16) NOT NULL,
IdBenef VARCHAR(16) NOT NULL,
Apellido VARCHAR(40) NOT NULL,
SegApellido VARCHAR(40),
Nombre VARCHAR(40) NOT NULL,
SegNombre VARCHAR(40),
TipoId CHAR(1) DEFAULT ('N') NOT NULL,
Parentesco VARCHAR(10),
ValorUpc MONEY DEFAULT (0) NOT NULL,
Inactivo BIT DEFAULT (0) NOT NULL
 CONSTRAINT PK_Emp_Beneficiarios PRIMARY KEY CLUSTERED (IdEmpleado,IdBenef),
 CONSTRAINT CK_Emp_BeneficiariosApellido CHECK ((len([Apellido]) > 0)),
 CONSTRAINT CK_Emp_BeneficiariosIdBenef CHECK ((len([IdBenef]) > 0)),
 CONSTRAINT CK_Emp_BeneficiariosIdEmpleado CHECK ((len([IdEmpleado]) > 0)),
 CONSTRAINT CK_Emp_BeneficiariosNombre CHECK ((len([Nombre]) > 0)),
 CONSTRAINT CK_Emp_BeneficiariosTipoId CHECK ((len([TipoId]) > 0)))
GO
ALTER TABLE Emp_Beneficiarios ADD 
 CONSTRAINT FK_Emp_Beneficiarios_Empleados FOREIGN KEY (IdEmpleado) REFERENCES [dbo].[Empleados] (IdEmpleado)

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

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

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

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

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsFcSemanas @pmIdVehiculo VARCHAR(10),@pmnSemana INT,@pmNumCargue INT,@pmCiaCargue CHAR(2),@pmTipoSem INT,@pmFecha SMALLDATETIME,@pmFecVence SMALLDATETIME,@pmVrCargo MONEY
,@pmVrOtros MONEY,@pmVrPagado MONEY,@pmTipRec VARCHAR(3),@pmRecibo INT,@pmIdCiaRec CHAR(2),@pmFechaPago SMALLDATETIME,@pmObservacion VARCHAR(250)
, @pmFecSemIni SMALLDATETIME, @pmFecSemFin SMALLDATETIME,@pmTimeSys SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_FcSemanas (IdVehiculo,nSemana,NumCargue,CiaCargue,TipoSem,Fecha,FecVence,VrCargo,VrOtros,VrPagado,TipRec,Recibo,IdCiaRec,FechaPago,Observacion,FecSemIni,FecSemFin,TimeSys,IdUsuario)
 VALUES (@pmIdVehiculo,@pmnSemana,@pmNumCargue,@pmCiaCargue,@pmTipoSem,@pmFecha,@pmFecVence,@pmVrCargo,@pmVrOtros,@pmVrPagado,@pmTipRec,@pmRecibo,@pmIdCiaRec,@pmFechaPago
,@pmObservacion,@pmFecSemIni,@pmFecSemFin,@pmTimeSys,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFcCausacionFmt @pmTipDoc VARCHAR(3),@pmNumDocIni INT,@pmNumDocFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT  NumDoc,C.IdCia AS CdCia,Compania,C.Fecha AS FecDoc,FecCausac,C.IdConcepto AS CdConcepto,Concepto,VrNeto,TipCom,Comprobante,IdCiaCom
,Anulado,NumDev,FecDev,C.Observacion AS Observ,C.IdEstado AS CdEstado,Estado,OrigenAdd,C.TimeSys AS FechaCrea,FecUpdate,IdCiaCrea,C.IdUsuario AS CdUsuario,Usuario,TipDoc
--DETALLES
,F.IdVehiculo AS PlacaVeh,NumVeh,nSemana,TipoSem,F.Fecha AS FecSemana,FecVence,VrCargo,VrOtros,VrPagado,TipRec,Recibo,IdCiaRec,FechaPago,FecSemIni,FecSemFin
--datos del vehículo
,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,Modelo,IdPropietario,T.RazonSocial AS Propietario
,IdPoseedor,P.RazonSocial AS Poseedor,IdConductor,CD.RazonSocial AS Conductor,TipoAfil,FecIngreso,V.FechaAdd AS VehFecCrea,V.Inactivo AS VehInactivo
 FROM Trn_FcCausacion AS C INNER JOIN Trn_FcSemanas AS F ON C.NumDoc=F.NumCargue AND C.IdCia=F.CiaCargue
 INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia INNER JOIN Conceptos AS CN ON C.IdConcepto=CN.IdConcepto
 INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN Vehiculos AS V ON F.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 Terceros AS T ON V.IdPropietario=T.IdTercero INNER JOIN Terceros AS P ON V.IdPoseedor=P.IdTercero
 INNER JOIN Terceros AS CD ON V.IdConductor=CD.IdTercero
 WHERE C.TipDoc=@pmTipDoc AND C.NumDoc BETWEEN @pmNumDocIni AND @pmNumDocFin AND  C.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 paQryFcSemanas @pmIdVehiculo VARCHAR(10),@pmnSemana INT
 AS 
 SELECT IdVehiculo,nSemana,NumCargue,CiaCargue,TipoSem,Fecha,FecVence,VrCargo,VrOtros,VrPagado,TipRec,Recibo,IdCiaRec
,FechaPago,Observacion,FecSemIni,FecSemFin,TimeSys,IdUsuario
 FROM Trn_FcSemanas
 WHERE IdVehiculo=@pmIdVehiculo AND nSemana=@pmnSemana
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFcSemanasLta @pmIdVehiculo VARCHAR(10)=Null,@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
 AS 
 SELECT F.IdVehiculo AS PlacaVeh,NumVeh,nSemana,NumCargue,CiaCargue,TipoSem,Fecha,FecVence,VrCargo,VrOtros,VrPagado
,TipRec,Recibo,IdCiaRec,FechaPago,FecSemIni,FecSemFin,F.Observacion AS Observ,TimeSys,F.IdUsuario AS CdUsuario,Usuario
--datos del vehículo
,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,Modelo,IdPropietario,T.RazonSocial AS Propietario
,IdPoseedor,P.RazonSocial AS Poseedor,IdConductor,CD.RazonSocial AS Conductor,TipoAfil,FecIngreso,V.FechaAdd AS FechaCrea,V.Inactivo AS VehInactivo
 FROM Trn_FcSemanas AS F INNER JOIN Vehiculos AS V ON F.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 Terceros AS T ON V.IdPropietario=T.IdTercero INNER JOIN Terceros AS P ON V.IdPoseedor=P.IdTercero
 INNER JOIN Terceros AS CD ON V.IdConductor=CD.IdTercero
 INNER JOIN adm_Usuarios AS U ON F.IdUsuario=U.IdUsuario
 WHERE F.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')  
 AND (Fecha>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND Fecha<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY F.IdVehiculo,nSemana
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_KdexOdc @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,DescripProd,tmIdBodega,tmCdTanque,tmEntradas,tmVrPrecio,tmTarifaDct,tmVrDcto,tmVrUnitario
,(tmEntradas*tmVrPrecio)-tmVrDcto AS VrTotal,tmTarifaIva,tmVrIva,tmTarifaRet,tmVrRete,tmTarifaIca,tmVrIca,tmUnidades,Bodega,tmReferencia,tmDescripcion,tmImpGlobal,tmSobretasa
,tmCdCCosto,tmCdSubCos,tmCodTarDct,tmCodTarRet,tmCodTarIca,tmServcios,Tanques,tmTipDoc,tmDocumento,tmIdCia
 FROM  tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto INNER JOIN Bodegas AS B ON K.tmIdBodega=B.IdBodega
 WHERE  tmNumero=@pmtmNumero 
 ORDER BY tmItem
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpFcSemanas @pmIdVehiculo VARCHAR(10),@pmnSemana INT,@pmNumCargue INT,@pmCiaCargue CHAR(2),@pmTipoSem INT,@pmFecha SMALLDATETIME,@pmFecVence SMALLDATETIME,@pmVrCargo MONEY,@pmVrOtros MONEY,@pmVrPagado MONEY
,@pmTipRec VARCHAR(3),@pmRecibo INT,@pmIdCiaRec CHAR(2),@pmFechaPago SMALLDATETIME,@pmObservacion VARCHAR(250),@pmFecSemIni SMALLDATETIME,@pmFecSemFin SMALLDATETIME
 AS 
 UPDATE Trn_FcSemanas SET NumCargue=@pmNumCargue,CiaCargue=@pmCiaCargue,TipoSem=@pmTipoSem,Fecha=@pmFecha,FecVence=@pmFecVence,VrCargo=@pmVrCargo,VrOtros=@pmVrOtros,VrPagado=@pmVrPagado
,TipRec=@pmTipRec,Recibo=@pmRecibo,IdCiaRec=@pmIdCiaRec,FechaPago=@pmFechaPago,Observacion=@pmObservacion,FecSemIni=@pmFecSemIni,FecSemFin=@pmFecSemFin
 WHERE IdVehiculo=@pmIdVehiculo AND nSemana=@pmnSemana
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

UPDATE Trn_FcSemanas SET FecSemIni=Fecha,FecSemFin=FecVence
GO