ALTER TABLE Trn_Requisicion ADD NivelAprob INT DEFAULT(0) NOT NULL
GO
INSERT INTO adm_Opciones (IdOpc,Opcion,TipoDato,Valor,NivUp,nModulo)
 VALUES ('VRQ','VIGENCIA PREDETERMINADA EN LA REQUISICION (NORMAL,PERMANENTE)','VARCHAR','PERMANENTE',5,'MAIN')
INSERT INTO adm_Opciones (IdOpc,Opcion,TipoDato,Valor,NivUp,nModulo)
 VALUES ('ARN','HABILITAR APROBACION DE REQUISICIONES POR NIVELES','BOOLEAN','0',5,'MAIN')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsRequisicion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsRequisicion]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpRequisicion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpRequisicion]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRequisicion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRequisicion]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRequisicion_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRequisicion_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRequisicionLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRequisicionLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpRequisicionApr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpRequisicionApr]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryRequisicion_Cr] @pmTipDoc VARCHAR(3),@pmRequisicionIni INT,@pmRequisicionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT R.TipDoc AS CdTipo,TipoDoc,R.Requisicion AS NumRequis,R.IdCia AS CdCia,Compania,Fecha,FechaVence,R.IdConcepto AS CdConcepto,Concepto,IdRespons,T.RazonSocial AS NomResponsable
,R.IdCCosto AS CdCentCosto,CCosto,R.IdSubCos AS CdSubCent,R.IdDep AS CdDep,Dependencia,VrSubTotal,R.Cantidad AS CantTotal,R.NContrato AS NumContrato,IdCiaCont
,NitCliente,CL.RazonSocial AS NomCliente,CdAgencia,Agencia,CodAgencia,Modalidad,DirEntrega,IdLocEnt,Localidad,Departamento,R.TipSal AS Tip_Sal,NumSalida,R.IdCiaSal AS CdCiaSal
,R.FechaSal AS Fec_Salida,NumAprob,FecAprob,CdUsuAprob,NivelAprob,OrigenAdd,Anulado,NomContacto,TelsContacto,EmailContacto,Num_Vehic,Num_Trailer,TipoVigencia,FecDev,R.Observacion AS Observ
,R.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,R.IdUsuario AS IdUsuari,Usuario
,Item,CdProducto,Descripcion,D.Cantidad AS CantArt,D.IdUnd AS CdUnid,Unidad,D.IdSubgrupo AS CdSubgrupo,Subgrupo,VrUnitario,TipOrd,NumOrden,IdCiaOrd,D.TipSal AS DetTipoSal,NSalida,D.IdCiaSal AS DetCiaSal
,D.FechaSal AS DetFecSalida,CdBodega,Bodega,D.Referencia,D.CantSalida,D.CantDevSal
 FROM Trn_Requisicion AS R INNER JOIN Trn_ReqDetalle AS D ON R.TipDoc=D.TipDoc AND R.Requisicion=D.Requisicion AND R.IdCia=D.IdCia
 INNER JOIN Terceros AS T ON R.IdRespons=T.IdTercero
 INNER JOIN CentroCosto AS CC ON R.IdCCosto=CC.IdCCosto
 INNER JOIN Conceptos AS C ON R.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario 
 INNER JOIN Sys_TiposDoc AS TD ON R.TipDoc=TD.IdDoc
 INNER JOIN Companias AS CI ON R.IdCia=CI.IdCia
 INNER JOIN Dependencias AS DP ON R.IdDep=DP.IdDep
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd
 INNER JOIN SubGrupos AS S ON D.IdSubgrupo=S.IdSubgrupo
 INNER JOIN Localidades AS L ON R.IdLocEnt=L.IdLocal
 INNER JOIN Departamentos AS DT ON L.IdDep=DT.IdDep
 LEFT JOIN Terceros AS CL ON R.NitCliente =CL.IdTercero
 LEFT JOIN Bodegas AS BG ON D.CdBodega =BG.IdBodega
 LEFT JOIN Agencias AS A ON R.CdAgencia=A.IdAgencia
 WHERE R.TipDoc=@pmTipDoc AND R.Requisicion BETWEEN  @pmRequisicionIni AND @pmRequisicionFin 
 AND R.IdCia=@pmIdCia 
 ORDER BY  R.Requisicion,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryRequisicionLta] @pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmRequisicionIni INT=Null
,@pmRequisicionFin INT=Null,@pmIdCia CHAR(2)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdRespons VARCHAR(16)=Null
,@pmIdCCosto VARCHAR(16)=Null,@pmNitCliente VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null,@pmAnulado BIT=Null
,@pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT Requisicion,IdCia,Fecha,FechaVence,R.IdConcepto AS CdConcepto,Concepto,IdRespons,T.RazonSocial AS NomResponsable
,R.IdCCosto AS CdCentCosto,CCosto,R.IdSubCos AS CdSubCent,R.IdDep AS CdDep,Dependencia,VrSubTotal,Cantidad,NContrato,IdCiaCont
,NitCliente,CL.RazonSocial AS NomCliente,CdAgencia,Modalidad,DirEntrega,IdLocEnt,TipSal,NumSalida,IdCiaSal,FechaSal,NumAprob,FecAprob,CdUsuAprob,NivelAprob
,NomContacto,TelsContacto,EmailContacto,Num_Vehic,Num_Trailer,TipoVigencia,OrigenAdd,Anulado,FecDev,R.Observacion AS Observ
,R.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,R.IdUsuario AS IdUsuari,Usuario,TipDoc
 FROM Trn_Requisicion AS R INNER JOIN Terceros AS T ON R.IdRespons=T.IdTercero
 INNER JOIN  CentroCosto AS CC ON R.IdCCosto=CC.IdCCosto
 INNER JOIN  Conceptos AS C ON R.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario 
 INNER JOIN Dependencias AS D ON R.IdDep=D.IdDep
 LEFT JOIN Terceros AS CL ON R.NitCliente =CL.IdTercero
 WHERE TipDoc='REQ' AND Fecha BETWEEN  @pmFechaIni AND @pmFechaFin AND IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND R.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND IdRespons LIKE ISNULL(@pmIdRespons,'%')
 AND R.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND NitCliente LIKE ISNULL(@pmNitCliente,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%') AND R.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 AND  Requisicion BETWEEN ISNULL(@pmRequisicionIni,0) AND ISNULL(@pmRequisicionFin,2147483647)
 ORDER BY IdCia,Requisicion
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpRequisicionApr] @pmTipDoc VARCHAR(3),@pmRequisicion INT,@pmIdCia CHAR(2)
,@pmNumAprob INT,@pmFecAprob SMALLDATETIME,@pmCdUsuAprob VARCHAR(11),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4)
,@pmNivelAprob INT
 AS 
 UPDATE Trn_Requisicion SET  NumAprob=@pmNumAprob,FecAprob=@pmFecAprob,CdUsuAprob=@pmCdUsuAprob
,Observacion=@pmObservacion,IdEstado=@pmIdEstado,NivelAprob=@pmNivelAprob
 WHERE TipDoc=@pmTipDoc AND Requisicion=@pmRequisicion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsRequisicion] @pmTipDoc VARCHAR(3),@pmRequisicion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdRespons VARCHAR(16),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdDep VARCHAR(4),@pmVrSubTotal MONEY,@pmCantidad DECIMAL(14,4)
,@pmNContrato INT,@pmIdCiaCont CHAR(2),@pmNitCliente VARCHAR(16),@pmCdAgencia VARCHAR(16),@pmModalidad VARCHAR(10),@pmDirEntrega VARCHAR(250),@pmIdLocEnt VARCHAR(8),@pmTipSal VARCHAR(3),@pmNumSalida INT,@pmIdCiaSal CHAR(2),@pmFechaSal SMALLDATETIME,@pmNumAprob INT,@pmFecAprob SMALLDATETIME
,@pmCdUsuAprob VARCHAR(11),@pmNomContacto VARCHAR(150),@pmTelsContacto VARCHAR(50),@pmEmailContacto VARCHAR(100),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmNum_Vehic VARCHAR(10),@pmNum_Trailer VARCHAR(10),@pmTipoVigencia VARCHAR(10),@pmNivelAprob INT
,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_Requisicion (TipDoc,Requisicion,IdCia,Fecha,FechaVence,IdConcepto,IdRespons,IdCCosto,IdSubCos,IdDep,VrSubTotal,Cantidad,NContrato,IdCiaCont,NitCliente,CdAgencia,Modalidad,DirEntrega,IdLocEnt,TipSal,NumSalida,IdCiaSal,FechaSal,NumAprob,FecAprob,CdUsuAprob,NomContacto,TelsContacto,EmailContacto
,Num_Vehic,Num_Trailer,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,TipoVigencia,NivelAprob)
 VALUES (@pmTipDoc,@pmRequisicion,@pmIdCia,@pmFecha,@pmFechaVence,@pmIdConcepto,@pmIdRespons,@pmIdCCosto,@pmIdSubCos,@pmIdDep,@pmVrSubTotal,@pmCantidad,@pmNContrato,@pmIdCiaCont,@pmNitCliente,@pmCdAgencia,@pmModalidad,@pmDirEntrega,@pmIdLocEnt,@pmTipSal,@pmNumSalida,@pmIdCiaSal
,@pmFechaSal,@pmNumAprob,@pmFecAprob,@pmCdUsuAprob,@pmNomContacto,@pmTelsContacto,@pmEmailContacto,@pmNum_Vehic,@pmNum_Trailer,@pmOrigenAdd,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmTipoVigencia,@pmNivelAprob)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpRequisicion] @pmTipDoc VARCHAR(3),@pmRequisicion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdRespons VARCHAR(16),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16)
,@pmIdDep VARCHAR(4),@pmVrSubTotal MONEY,@pmCantidad DECIMAL(14,4),@pmNContrato INT,@pmIdCiaCont CHAR(2),@pmNitCliente VARCHAR(16),@pmCdAgencia VARCHAR(16),@pmModalidad VARCHAR(10),@pmDirEntrega VARCHAR(250),@pmIdLocEnt VARCHAR(8)
,@pmTipSal VARCHAR(3),@pmNumSalida INT,@pmIdCiaSal CHAR(2),@pmFechaSal SMALLDATETIME,@pmNumAprob INT,@pmFecAprob SMALLDATETIME,@pmCdUsuAprob VARCHAR(11),@pmNomContacto VARCHAR(150),@pmTelsContacto VARCHAR(50),@pmEmailContacto VARCHAR(100),@pmAnulado BIT
,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmNum_Vehic VARCHAR(10),@pmNum_Trailer VARCHAR(10),@pmTipoVigencia VARCHAR(10),@pmNivelAprob INT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_Requisicion SET Fecha=@pmFecha,FechaVence=@pmFechaVence,IdConcepto=@pmIdConcepto,IdRespons=@pmIdRespons,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,IdDep=@pmIdDep,VrSubTotal=@pmVrSubTotal
,Cantidad=@pmCantidad,NContrato=@pmNContrato,IdCiaCont=@pmIdCiaCont,NitCliente=@pmNitCliente,CdAgencia=@pmCdAgencia,Modalidad=@pmModalidad,DirEntrega=@pmDirEntrega
,IdLocEnt=@pmIdLocEnt,TipSal=@pmTipSal,NumSalida=@pmNumSalida,IdCiaSal=@pmIdCiaSal,FechaSal=@pmFechaSal,NumAprob=@pmNumAprob
,FecAprob=@pmFecAprob,CdUsuAprob=@pmCdUsuAprob,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado
,NomContacto=@pmNomContacto,TelsContacto=@pmTelsContacto,EmailContacto=@pmEmailContacto,Num_Vehic=@pmNum_Vehic,Num_Trailer=@pmNum_Trailer,TipoVigencia=@pmTipoVigencia,NivelAprob=@pmNivelAprob,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND Requisicion=@pmRequisicion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryRequisicion] @pmTipDoc VARCHAR(3),@pmRequisicion INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Requisicion,IdCia,Fecha,FechaVence,IdConcepto,IdRespons,IdCCosto,IdSubCos,IdDep,VrSubTotal,Cantidad
,NContrato,IdCiaCont,NitCliente,CdAgencia,Modalidad,DirEntrega,IdLocEnt,TipSal,NumSalida,IdCiaSal,FechaSal
,NumAprob,FecAprob,CdUsuAprob,NivelAprob,NomContacto,TelsContacto,EmailContacto,Num_Vehic,Num_Trailer,TipoVigencia
,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_Requisicion
 WHERE TipDoc=@pmTipDoc AND Requisicion=@pmRequisicion AND IdCia=@pmIdCia
GO