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].[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].[paQryRequisicionPen]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryRequisicionPen]
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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE 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),@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,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario)
 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,@pmOrigenAdd,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE 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,NomContacto,TelsContacto,EmailContacto,OrigenAdd,Anulado,FecDev,Observacion,IdEstado
,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_Requisicion
 WHERE TipDoc=@pmTipDoc AND Requisicion=@pmRequisicion 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 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,OrigenAdd,Anulado,NomContacto,TelsContacto,EmailContacto,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
 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 QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE 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
,NomContacto,TelsContacto,EmailContacto,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 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 QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryRequisicionPen @pmRequisicionIni INT=Null,@pmRequisicionFin INT=Null,@pmIdCia CHAR(2)=Null
,@pmIdRespons VARCHAR(16)=Null,@pmNitCliente VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT  R.Requisicion AS NumRequis,R.IdCia AS CdCia,Fecha,FechaVence,IdRespons,T.RazonSocial AS NomResponsable
,Item,CdProducto,Descripcion,D.Cantidad AS CantArt,D.IdUnd AS CdUnid,Unidad,VrUnitario,D.Cantidad*VrUnitario AS ValorTotal
,R.IdConcepto AS CdConcepto,Concepto,R.IdCCosto AS CdCentCosto,CCosto,R.IdSubCos AS CdSubCent,R.IdDep AS CdDep,Dependencia
,NContrato,IdCiaCont,NitCliente,CL.RazonSocial AS NomCliente,CdAgencia,Modalidad,DirEntrega,NumAprob,FecAprob,CdUsuAprob
,R.Observacion AS Observ,TipOrd,NumOrden,IdCiaOrd,NomContacto,TelsContacto,EmailContacto
 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 Dependencias AS DP ON R.IdDep=DP.IdDep
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd
 LEFT JOIN Terceros AS CL ON R.NitCliente =CL.IdTercero
 WHERE  Anulado=0 AND R.IdEstado='0001' AND NumAprob>0
 AND  R.Requisicion BETWEEN ISNULL(@pmRequisicionIni,0) AND ISNULL(@pmRequisicionFin,2147483647)
 AND R.IdCia LIKE ISNULL(@pmIdCia,'%%')  AND IdRespons LIKE ISNULL(@pmIdRespons,'%')
 AND NitCliente LIKE ISNULL(@pmNitCliente,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 ORDER BY R.IdCia,R.Requisicion,Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE 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),@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,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND Requisicion=@pmRequisicion AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO