if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsDocMemo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsDocMemo]
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].[paQryDocMemo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDocMemo]
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].[paQryRequisicionLtm]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRequisicionLtm]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpDocMemo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpDocMemo]
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].[paUpDocMemoImp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpDocMemoImp]
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,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
 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].[paQryRequisicionLtm] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
,@pmIdCia CHAR(2)=Null,@pmModalidad VARCHAR(10)=Null
AS 
 SELECT R.Requisicion,R.IdCia AS CdCia,Compania,R.Fecha,FechaVence,R.NContrato AS NumOrden,R.IdCiaCont AS IdCiaOdt,R.Modalidad
,R.IdRespons AS CdOperario,T.RazonSocial AS Operario,Num_Vehic,Num_Trailer,R.NitCliente AS NitCentro,CL.RazonSocial AS NomCentroServ
,DirEntrega,IdLocEnt,Localidad,R.IdDep AS CdDep,Dependencia,VrSubTotal,Cantidad,NumAprob,FecAprob,CdUsuAprob
,TipSal,NumSalida,IdCiaSal,FechaSal,R.IdConcepto AS CdConcepto,Concepto,R.IdCCosto AS CdCCosto,CCosto,R.IdSubCos AS CdSubCos
,NomContacto,TelsContacto,EmailContacto,TipoVigencia,R.Anulado,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado
,OrigenAdd,R.TimeSys,R.FecUpdate,R.IdCiaCrea,R.IdUsuario AS CdUsuario,Usuario
 FROM Trn_Requisicion AS R INNER JOIN Companias AS CI ON R.IdCia=CI.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 Dependencias AS D ON R.IdDep=D.IdDep
 LEFT JOIN Terceros AS CL ON R.NitCliente =CL.IdTercero LEFT JOIN Localidades AS L ON R.IdLocEnt=L.IdLocal
 WHERE R.TipDoc=@pmTipDoc AND R.Fecha BETWEEN  @pmFechaIni AND @pmFechaFin AND R.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND R.Modalidad LIKE ISNULL(@pmModalidad,'%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsDocMemo] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmComentarios VARCHAR(6500)
,@pmNota1 VARCHAR(500),@pmNota2 VARCHAR(500),@pmNota3 VARCHAR(500),@pmCantImp INT,@pmArchivoImg VARCHAR(150)
 AS 
 INSERT INTO Trn_DocMemo (TipDoc,Documento,IdCia,Comentarios,Nota1,Nota2,Nota3,CantImp,ArchivoImg)
 VALUES (@pmTipDoc,@pmDocumento,@pmIdCia,@pmComentarios,@pmNota1,@pmNota2,@pmNota3,@pmCantImp,@pmArchivoImg)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryDocMemo] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Documento,IdCia,Comentarios,Nota1,Nota2,Nota3,CantImp,ArchivoImg
 FROM Trn_DocMemo
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpDocMemo] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2),@pmComentarios VARCHAR(6500)
,@pmNota1 VARCHAR(500),@pmNota2 VARCHAR(500),@pmNota3 VARCHAR(500),@pmCantImp INT,@pmArchivoImg VARCHAR(150)
 AS 
 UPDATE Trn_DocMemo SET Comentarios=@pmComentarios
,Nota1=@pmNota1,Nota2=@pmNota2,Nota3=@pmNota3,ArchivoImg=@pmArchivoImg
 WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia
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
,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 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)
,@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)
 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)
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),@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,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,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
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpDocMemoImp] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
AS
 IF EXISTS (SELECT * FROM Trn_DocMemo WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia)
  BEGIN
   UPDATE Trn_DocMemo SET CantImp=ISNULL(CantImp,0)+1
   WHERE TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia
  END
ELSE
  BEGIN
   INSERT INTO Trn_DocMemo (TipDoc,Documento,IdCia,Comentarios,Nota1,Nota2,Nota3,CantImp,ArchivoImg)
   VALUES (@pmTipDoc,@pmDocumento,@pmIdCia,'','','','',1,Null)
  END
GO