/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_isiPicking5_OperacoesDocumentos
	(
	CodOper smallint NOT NULL,
	Or_Sector nvarchar(3) NOT NULL,
	Or_TpDoc nvarchar(3) NOT NULL,
	Or_SERIE nvarchar(10) NOT NULL,
	De_Sector nvarchar(3) NOT NULL,
	De_TpDoc nvarchar(3) NOT NULL,
	De_SERIE nvarchar(10) NOT NULL,
	CTB bit NULL,
	Imprime bit NULL,
	Finaliza bit NULL,
	CodUtil nvarchar(10) NOT NULL,
	Id int NOT NULL IDENTITY (1, 1)
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_isiPicking5_OperacoesDocumentos SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_isiPicking5_OperacoesDocumentos OFF
GO
IF EXISTS(SELECT * FROM dbo.isiPicking5_OperacoesDocumentos)
	 EXEC('INSERT INTO dbo.Tmp_isiPicking5_OperacoesDocumentos (CodOper, Or_Sector, Or_TpDoc, Or_SERIE, De_Sector, De_TpDoc, De_SERIE, CTB, Imprime, Finaliza, CodUtil)
		SELECT CodOper, Or_Sector, Or_TpDoc, Or_SERIE, De_Sector, De_TpDoc, De_SERIE, CTB, Imprime, Finaliza, CodUtil FROM dbo.isiPicking5_OperacoesDocumentos WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.isiPicking5_OperacoesDocumentos
GO
EXECUTE sp_rename N'dbo.Tmp_isiPicking5_OperacoesDocumentos', N'isiPicking5_OperacoesDocumentos', 'OBJECT' 
GO
ALTER TABLE dbo.isiPicking5_OperacoesDocumentos ADD CONSTRAINT
	PK_isiPicking5_UtilizadoresPorDocumentoOperacao PRIMARY KEY CLUSTERED 
	(
	CodOper,
	Or_Sector,
	Or_TpDoc,
	Or_SERIE,
	De_Sector,
	De_TpDoc,
	De_SERIE,
	CodUtil
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

/****** Object:  StoredProcedure [dbo].[isiPicking5_InfoStock]    Script Date: 05-03-2023 17:32:20 ******/
DROP PROCEDURE [dbo].[isiPicking5_InfoStock]
GO

/****** Object:  StoredProcedure [dbo].[isiPicking5_InfoStock]    Script Date: 05-03-2023 17:32:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[isiPicking5_InfoStock]
	@CodigoArtigo	nvarchar(15)	,
	@Armazem		nvarchar(6)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT	'Stock Real'	as	Nome,        
			STDISP			AS	Stock
	FROM	ARTIGOS
	WHERE	(CODIGO = @CodigoArtigo)
	Union all
	SELECT	'Stock no Armazem'	as	Nome,			
			EXISTENC			AS Stock
	FROM    ARTARM
	WHERE	(ARTIGO = @CodigoArtigo) 
	AND		(ARMAZEM = @Armazem)
	Union all
	SELECT	'Stock Já picado e não descarregado'	as	Nome,
			Sum(isiPicking5_DocLin.Qtd_Pic) as Stock
	FROM	isiPicking5_DocLin INNER JOIN isiPicking5_DocCab ON isiPicking5_DocLin.Pic_Id = isiPicking5_DocCab.Pic_Id	
	WHERE	(isiPicking5_DocLin.Artigo = @CodigoArtigo) 
	AND		(isiPicking5_DocCab.Estado = 0) 
END
GO


