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

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

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

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

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

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

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

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

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsPolizas @pmNumPoliza VARCHAR(30),@pmFecha SMALLDATETIME,@pmVigencia SMALLDATETIME,@pmNitCompania VARCHAR(16),@pmCompania VARCHAR(150)
,@pmNitTomador VARCHAR(16),@pmTomador VARCHAR(150),@pmNitAseg VARCHAR(16),@pmAsegurado VARCHAR(150),@pmNitBenef VARCHAR(16),@pmBeneficiario VARCHAR(150)
,@pmInactivo BIT,@pmDescFirma VARCHAR(2000),@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Polizas (NumPoliza,Fecha,Vigencia,NitCompania,Compania,NitTomador,Tomador,NitAseg,Asegurado,NitBenef,Beneficiario,DescFirma,Inactivo,FechaAdd,IdUsuario)
 VALUES (@pmNumPoliza,@pmFecha,@pmVigencia,@pmNitCompania,@pmCompania,@pmNitTomador,@pmTomador,@pmNitAseg,@pmAsegurado
,@pmNitBenef,@pmBeneficiario,@pmDescFirma,@pmInactivo,@pmFechaAdd,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsRutas @pmIdRuta VARCHAR(4),@pmRuta VARCHAR(100),@pmIdLocOri VARCHAR(8),@pmIdLocDes VARCHAR(8)
,@pmEsLocal BIT,@pmDistkmt DECIMAL(14,4),@pmTmHoras VARCHAR(8),@pmCodigoMt VARCHAR(5),@pmFleteTon MONEY,@pmInactivo BIT
 AS 
 INSERT INTO Rutas (IdRuta,Ruta,IdLocOri,IdLocDes,EsLocal,Distkmt,TmHoras,CodigoMt,FleteTon,Inactivo)
 VALUES (@pmIdRuta,@pmRuta,@pmIdLocOri,@pmIdLocDes,@pmEsLocal,@pmDistkmt,@pmTmHoras,@pmCodigoMt,@pmFleteTon,@pmInactivo)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryPolizas @pmNumPoliza VARCHAR(30)
 AS 
 SELECT NumPoliza,Fecha,Vigencia,NitCompania,Compania,NitTomador,Tomador,NitAseg,Asegurado
,NitBenef,Beneficiario,Inactivo,FechaAdd,FechaUpdate,IdUsuario,DescFirma
 FROM Polizas
 WHERE NumPoliza=@pmNumPoliza
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryPolizasLta
 AS 
 SELECT NumPoliza,Fecha,Vigencia,NitCompania,Compania,NitTomador,Tomador,NitAseg,Asegurado
,NitBenef,Beneficiario,DescFirma,P.IdUsuario AS IdUsuari,Usuario,FechaAdd,FechaUpdate
 FROM Polizas AS P INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 WHERE P.Inactivo=0
 ORDER BY NumPoliza
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryRutas @pmIdRuta VARCHAR(4)
 AS 
 SELECT IdRuta,Ruta,IdLocOri,IdLocDes,EsLocal,Distkmt,TmHoras,CodigoMt,FleteTon,Inactivo
 FROM Rutas
 WHERE IdRuta=@pmIdRuta
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryRutasLta  @pmIdLocOri VARCHAR(8)=Null,@pmIdLocDes VARCHAR(8)=Null,@pmEsLocal BIT=Null
 AS 
 SELECT IdRuta,Ruta,IdLocOri,LO.Localidad AS CiuOrigen,LO.IdDep AS CdDptoOrig,D.Departamento AS DptoOrigen,IdLocDes
,LD.Localidad AS CiuDestino,LD.IdDep AS CdDptoDest,DD.Departamento AS DptoDestino,EsLocal,Distkmt,TmHoras,CodigoMt,FleteTon
 FROM Rutas AS R INNER JOIN Localidades AS LO ON R.IdLocOri=LO.IdLocal
 INNER JOIN Departamentos AS D ON LO.IdDep=D.IdDep
 INNER JOIN Localidades AS LD ON R.IdLocDes=LD.IdLocal
 INNER JOIN Departamentos AS DD ON LD.IdDep=DD.IdDep
 WHERE R.Inactivo=0 AND  IdLocOri like ISNULL(@pmIdLocOri,'%') AND IdLocDes LIKE ISNULL(@pmIdLocDes,'%')
 AND (EsLocal=ISNULL(@pmEsLocal,0) or EsLocal=ISNULL(@pmEsLocal,1))
 ORDER BY IdRuta
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_KdexParc @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmNumero,tmItem,tmIdProducto,tmIdBodega,tmCdTanque,tmEntradas,tmSalidas,tmVrUnitario
,tmVrPrecio,tmVrCostProm,tmVrProm,tmVrBruto,tmCdUbic,tmNumLote,tmFechLote,tmFecVceLote,tmDescripcion,tmReferencia
,tmCdProdEquiv,tmCdMngra,tmNumInicial,tmNumFinal,tmServcios,tmNoVentas,tmEsCombo,tmEsProdBase,tmItemCbo
,tmCdSubgrupo,tmComptmntos
,DescripProd,TipoRef,IdSubgrupo,IdBodega,IdUbic,VrCostAnt,VrCosto,VrCostPmd,ExtciaMin,ExtciaMax,ExtciaAct
,Seriales,Lotes,Combo,Tanques
 FROM tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto
 WHERE tmNumero=@pmtmNumero AND tmEsProdBase=0
 ORDER BY tmIdProducto,tmItem

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpPolizas @pmNumPoliza VARCHAR(30),@pmFecha SMALLDATETIME,@pmVigencia SMALLDATETIME,@pmNitCompania VARCHAR(16),@pmCompania VARCHAR(150),@pmNitTomador VARCHAR(16)
,@pmTomador VARCHAR(150),@pmNitAseg VARCHAR(16),@pmAsegurado VARCHAR(150),@pmNitBenef VARCHAR(16),@pmBeneficiario VARCHAR(150),@pmDescFirma VARCHAR(2000),@pmInactivo BIT,@pmFechaUpdate SMALLDATETIME
 AS 
 UPDATE Polizas SET Fecha=@pmFecha,Vigencia=@pmVigencia,NitCompania=@pmNitCompania,Compania=@pmCompania,NitTomador=@pmNitTomador,Tomador=@pmTomador,NitAseg=@pmNitAseg
,Asegurado=@pmAsegurado,NitBenef=@pmNitBenef,Beneficiario=@pmBeneficiario,Inactivo=@pmInactivo,DescFirma=@pmDescFirma,FechaUpdate=@pmFechaUpdate
 WHERE NumPoliza=@pmNumPoliza
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpRutas @pmIdRuta VARCHAR(4),@pmRuta VARCHAR(100),@pmIdLocOri VARCHAR(8),@pmIdLocDes VARCHAR(8),@pmEsLocal BIT
,@pmDistkmt DECIMAL(14,4),@pmTmHoras VARCHAR(8),@pmCodigoMt VARCHAR(5),@pmFleteTon MONEY,@pmInactivo BIT
 AS 
 UPDATE Rutas SET Ruta=@pmRuta,IdLocOri=@pmIdLocOri,IdLocDes=@pmIdLocDes,EsLocal=@pmEsLocal,Distkmt=@pmDistkmt
,TmHoras=@pmTmHoras,CodigoMt=@pmCodigoMt,FleteTon=@pmFleteTon,Inactivo=@pmInactivo
 WHERE IdRuta=@pmIdRuta
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO