if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsCorrResiduos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsCorrResiduos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsDesagregaciones]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsDesagregaciones]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsMercancias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsMercancias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsResiduosPeligrosos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsResiduosPeligrosos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryCorrResiduos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryCorrResiduos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDesagregaciones]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDesagregaciones]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDesagregacionesLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryDesagregacionesLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMercancias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMercancias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMercanciasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMercanciasLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryResiduosPeligrosos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryResiduosPeligrosos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryResiduosPeligrososLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryResiduosPeligrososLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryUndMedDse]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryUndMedDse]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpCorrResiduos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpCorrResiduos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpDesagregaciones]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpDesagregaciones]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpMercancias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpMercancias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpResiduosPeligrosos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpResiduosPeligrosos]
GO



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryResiduosPeligrososLta] @pmInactivo BIT=Null
 AS 
 SELECT R.CodigoUN,R.Designacion,R.Clase,R.PeligroSec,R.GrupoEmb
,R.IdCoRes,C.CodigoCR,C.GrupoRP,C.CorrResiduo,R.IdCRdes,D.CodigoDR,D.Desagregacion,R.Inactivo
 FROM ResiduosPeligrosos AS R LEFT JOIN CorrResiduos AS C ON R.IdCoRes=C.IdCorr
 LEFT JOIN Desagregaciones AS D ON R.IdCRdes=D.IdDrp
 WHERE (R.Inactivo=@pmInactivo OR @pmInactivo IS NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMercanciasLta] @pmIdGrupo VARCHAR(10)=Null,@pmIdNat VARCHAR(4)=Null,@pmIdTmcia VARCHAR(4)=Null
,@pmInactivo BIT=Null
 AS 
 SELECT IdMercancia,DescripMcia,M.IdGrupo AS CdGrupo,GrupoMcia,M.UndMed AS Und_Med,UT.Unidad AS UM_PesoTra,M.IdUnd AS CdUndPre,UM.Unidad,UM.IdEmp AS CdEmp
,M.IdNat AS CdNat,Natlzaprod,M.IdMnjo AS CdMnjo,ManejoMcia,M.IdTmcia AS CdTmcia,TipoMcia,Contenedor,M.IdProducto AS CdProducto,DescripProd,CodigoMcia
,M.IdEmp AS CdEmp,Empaque,EstadoMcia,M.UmCapac,M.UM_Prod,UP.Unidad AS DescUMprod,M.CodigoUN,UN.Designacion,UN.Clase AS ClaseUN,UN.PeligroSec,UN.GrupoEmb
,UN.IdCoRes,CR.GrupoRP,CR.CodigoCR,CR.CorrResiduo,UN.IdCRdes,DG.CodigoDR,DG.Desagregacion
,M.IdEstado AS CdEstado,Estado,M.Inactivo AS Inactvo,M.FechaAdd AS FechaCrea,M.FechaUpdate AS FechaAct,M.IdUsuario AS CdUsuario,Usuario
 FROM Mercancias AS M INNER JOIN GruposMcia AS G ON M.IdGrupo=G.IdGrupo INNER JOIN Sys_Um AS UT ON M.UndMed=UT.UndMed
 INNER JOIN UndMed AS UM ON M.IdUnd=UM.IdUnd INNER JOIN TiposNat AS N ON M.IdNat=N.IdNat
 INNER JOIN TiposMnjo AS MM ON M.IdMnjo=MM.IdMnjo INNER JOIN TiposMcia AS TM ON M.IdTmcia=TM.IdTmcia
 INNER JOIN EstadoPro AS EP ON M.IdEstado=EP.IdEstado INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario 
 LEFT JOIN ProdMcias AS P ON M.IdProducto=P.IdProducto LEFT JOIN Empaques AS E ON M.IdEmp=E.IdEmp
 LEFT JOIN Sys_Um AS UP ON M.UM_Prod=UP.UndMed
 LEFT JOIN ResiduosPeligrosos AS UN ON M.CodigoUN=UN.CodigoUN LEFT JOIN CorrResiduos AS CR ON UN.IdCoRes=CR.IdCorr
 LEFT JOIN Desagregaciones AS DG ON UN.IdCRdes=DG.IdDrp
 WHERE M.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND M.IdNat LIKE ISNULL(@pmIdNat,'%') AND M.IdTmcia LIKE ISNULL(@pmIdTmcia,'%')
  AND (M.Inactivo=ISNULL(@pmInactivo,0) or  M.Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY DescripMcia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryCorrResiduos] @pmIdCorr VARCHAR(8)
 AS 
 IF @pmIdCorr IS NULL
  BEGIN
   SELECT IdCorr,GrupoRP,CodigoCR,CorrResiduo,Inactivo
   FROM CorrResiduos
   WHERE Inactivo=0
  END
 ELSE
  BEGIN
   SELECT IdCorr,GrupoRP,CodigoCR,CorrResiduo,Inactivo
   FROM CorrResiduos
   WHERE IdCorr=@pmIdCorr
  END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsCorrResiduos] @pmIdCorr VARCHAR(8),@pmGrupoRP VARCHAR(4),@pmCodigoCR VARCHAR(10),@pmCorrResiduo VARCHAR(500),@pmInactivo BIT
 AS 
 INSERT INTO CorrResiduos (IdCorr,GrupoRP,CodigoCR,CorrResiduo,Inactivo)
 VALUES (@pmIdCorr,@pmGrupoRP,@pmCodigoCR,@pmCorrResiduo,@pmInactivo)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpCorrResiduos] @pmIdCorr VARCHAR(8),@pmGrupoRP VARCHAR(4),@pmCodigoCR VARCHAR(10),@pmCorrResiduo VARCHAR(500),@pmInactivo BIT
 AS 
 UPDATE CorrResiduos SET GrupoRP=@pmGrupoRP,CodigoCR=@pmCodigoCR,CorrResiduo=@pmCorrResiduo,Inactivo=@pmInactivo
 WHERE IdCorr=@pmIdCorr
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryDesagregacionesLta] @pmInactivo BIT=Null
 AS 
 SELECT D.IdDrp,D.CodigoDR,D.Desagregacion,D.CodigoCR AS IdCorrRes,C.GrupoRP,C.CorrResiduo,C.CodigoCR,D.Inactivo
 FROM Desagregaciones AS D INNER JOIN CorrResiduos AS C ON D.CodigoCR=C.IdCorr
 WHERE (D.Inactivo=@pmInactivo OR @pmInactivo IS NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsResiduosPeligrosos] @pmCodigoUN VARCHAR(30),@pmDesignacion VARCHAR(500),@pmClase VARCHAR(10),@pmPeligroSec VARCHAR(10),@pmGrupoEmb VARCHAR(20),@pmIdCoRes VARCHAR(8),@pmIdCRdes VARCHAR(8),@pmInactivo BIT
 AS 
 INSERT INTO ResiduosPeligrosos (CodigoUN,Designacion,Clase,PeligroSec,GrupoEmb,IdCoRes,IdCRdes,Inactivo)
 VALUES (@pmCodigoUN,@pmDesignacion,@pmClase,@pmPeligroSec,@pmGrupoEmb,@pmIdCoRes,@pmIdCRdes,@pmInactivo)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpResiduosPeligrosos] @pmCodigoUN VARCHAR(30),@pmDesignacion VARCHAR(500),@pmClase VARCHAR(10),@pmPeligroSec VARCHAR(10),@pmGrupoEmb VARCHAR(20),@pmIdCoRes VARCHAR(8),@pmIdCRdes VARCHAR(8),@pmInactivo BIT
 AS 
 UPDATE ResiduosPeligrosos SET Designacion=@pmDesignacion,Clase=@pmClase,PeligroSec=@pmPeligroSec,GrupoEmb=@pmGrupoEmb,IdCoRes=@pmIdCoRes,IdCRdes=@pmIdCRdes,Inactivo=@pmInactivo
 WHERE CodigoUN=@pmCodigoUN
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryResiduosPeligrosos] @pmCodigoUN VARCHAR(30)
 AS 
 SELECT CodigoUN,Designacion,Clase,PeligroSec,GrupoEmb,IdCoRes,IdCRdes,Inactivo
 FROM ResiduosPeligrosos
 WHERE CodigoUN=@pmCodigoUN
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsDesagregaciones] @pmIdDrp VARCHAR(8),@pmCodigoDR VARCHAR(10),@pmDesagregacion VARCHAR(500),@pmCodigoCR VARCHAR(8),@pmInactivo BIT
 AS 
 INSERT INTO Desagregaciones (IdDrp,CodigoDR,Desagregacion,CodigoCR,Inactivo)
 VALUES (@pmIdDrp,@pmCodigoDR,@pmDesagregacion,@pmCodigoCR,@pmInactivo)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpDesagregaciones] @pmIdDrp VARCHAR(8),@pmCodigoDR VARCHAR(10),@pmDesagregacion VARCHAR(500),@pmCodigoCR VARCHAR(8),@pmInactivo BIT
 AS 
 UPDATE Desagregaciones SET CodigoDR=@pmCodigoDR,Desagregacion=@pmDesagregacion,CodigoCR=@pmCodigoCR,Inactivo=@pmInactivo
 WHERE IdDrp=@pmIdDrp
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryDesagregaciones] @pmIdDrp VARCHAR(8)
 AS 
 SELECT IdDrp,CodigoDR,Desagregacion,CodigoCR,Inactivo
 FROM Desagregaciones
 WHERE IdDrp=@pmIdDrp
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryUndMedDse] @pmClaseEmp VARCHAR(10)=Null
 AS 
 IF @pmClaseEmp IS NULL
  BEGIN
   SELECT U.IdUnd,U.Unidad,E.Empaque+ '-'+U.Unidad+' ('+ U.ClaseEmp+')' AS DsUnd
   FROM UndMed AS U LEFT JOIN Empaques AS E ON U.IdEmp=E.IdEmp 
   WHERE U.Inactivo=0
   ORDER BY E.Empaque
  END
 ELSE
  BEGIN 
   IF @pmClaseEmp='?'
    BEGIN
     SELECT U.IdUnd,U.Unidad,E.Empaque+ '-'+U.Unidad+' ('+ U.ClaseEmp+')' AS DsUnd
     FROM UndMed AS U LEFT JOIN Empaques AS E ON U.IdEmp=E.IdEmp 
     WHERE U.ClaseEmp IN ('EMBALAJE','PRIMARIO') AND U.Inactivo=0
     ORDER BY E.Empaque
    END
   ELSE
    BEGIN
     SELECT U.IdUnd,U.Unidad,E.Empaque+ '-'+U.Unidad+' ('+ U.ClaseEmp+')' AS DsUnd
     FROM UndMed AS U LEFT JOIN Empaques AS E ON U.IdEmp=E.IdEmp 
     WHERE U.ClaseEmp=@pmClaseEmp AND U.Inactivo=0
     ORDER BY E.Empaque
    END
   END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsMercancias] @pmIdMercancia VARCHAR(16),@pmDescripMcia VARCHAR(250),@pmCodigoMcia VARCHAR(16),@pmIdGrupo VARCHAR(10),@pmUndMed VARCHAR(10)
,@pmIdUnd VARCHAR(4),@pmIdEmp VARCHAR(4),@pmIdNat VARCHAR(4),@pmIdMnjo VARCHAR(4),@pmIdTmcia VARCHAR(4),@pmEstadoMcia VARCHAR(20),@pmContenedor BIT
,@pmIdProducto VARCHAR(16),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmUmCapac VARCHAR(10),@pmCodigoUN VARCHAR(30),@pmUM_Prod VARCHAR(10),@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Mercancias (IdMercancia,DescripMcia,CodigoMcia,IdGrupo,UndMed,IdUnd,IdEmp,IdNat,IdMnjo,IdTmcia,EstadoMcia,Contenedor,IdProducto,UmCapac,IdEstado,Inactivo,FechaAdd,IdUsuario,CodigoUN,UM_Prod)
 VALUES (@pmIdMercancia,@pmDescripMcia,@pmCodigoMcia,@pmIdGrupo,@pmUndMed,@pmIdUnd,@pmIdEmp,@pmIdNat,@pmIdMnjo,@pmIdTmcia,@pmEstadoMcia,@pmContenedor,@pmIdProducto
,@pmUmCapac,@pmIdEstado,@pmInactivo,@pmFechaAdd,@pmIdUsuario,@pmCodigoUN,@pmUM_Prod)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpMercancias] @pmIdMercancia VARCHAR(16),@pmDescripMcia VARCHAR(250),@pmCodigoMcia VARCHAR(16),@pmIdGrupo VARCHAR(10),@pmUndMed VARCHAR(10),@pmIdUnd VARCHAR(4)
,@pmIdEmp VARCHAR(4),@pmIdNat VARCHAR(4),@pmIdMnjo VARCHAR(4),@pmIdTmcia VARCHAR(4),@pmEstadoMcia VARCHAR(20),@pmContenedor BIT,@pmIdProducto VARCHAR(16)
,@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmUmCapac VARCHAR(10),@pmCodigoUN VARCHAR(30),@pmUM_Prod VARCHAR(10),@pmFechaUpdate SMALLDATETIME
 AS 
 UPDATE Mercancias SET DescripMcia=@pmDescripMcia,CodigoMcia=@pmCodigoMcia,IdGrupo=@pmIdGrupo,UndMed=@pmUndMed,IdUnd=@pmIdUnd
,IdNat=@pmIdNat,IdMnjo=@pmIdMnjo,IdTmcia=@pmIdTmcia,Contenedor=@pmContenedor,IdProducto=@pmIdProducto,IdEstado=@pmIdEstado,Inactivo=@pmInactivo
,IdEmp=@pmIdEmp,EstadoMcia=@pmEstadoMcia,UmCapac=@pmUmCapac,CodigoUN=@pmCodigoUN,UM_Prod=@pmUM_Prod,FechaUpdate=@pmFechaUpdate
 WHERE IdMercancia=@pmIdMercancia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMercancias] @pmIdMercancia VARCHAR(16)
 AS 
 SELECT IdMercancia,DescripMcia,CodigoMcia,IdGrupo,UndMed,IdUnd,IdEmp,IdNat,IdMnjo,IdTmcia,EstadoMcia,Contenedor,IdProducto
,IdEstado,Inactivo,UmCapac,CodigoUN,UM_Prod,FechaAdd,FechaUpdate,IdUsuario
 FROM Mercancias
 WHERE IdMercancia=@pmIdMercancia
GO