USE [BeProd_REIMAN]
GO

/****** Object:  StoredProcedure [dbo].[abateConsumosProducaoSimples]    Script Date: 14/10/2024 11:10:40 ******/
DROP PROCEDURE [dbo].[abateConsumosProducaoSimples]
GO

/****** Object:  StoredProcedure [dbo].[SP_PROD_AbateConsumo_SSCC]    Script Date: 14/10/2024 11:10:40 ******/
DROP PROCEDURE [dbo].[SP_PROD_AbateConsumo_SSCC]
GO

/****** Object:  StoredProcedure [dbo].[SP_PROD_AbateConsumo_SSCC_Elimina]    Script Date: 14/10/2024 11:10:40 ******/
DROP PROCEDURE [dbo].[SP_PROD_AbateConsumo_SSCC_Elimina]
GO

/****** Object:  StoredProcedure [dbo].[SP_PROD_AbateConsumo_SSCC_Elimina]    Script Date: 14/10/2024 11:10:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


 
CREATE PROCEDURE [dbo].[SP_PROD_AbateConsumo_SSCC_Elimina] @ID_MovLin integer,  @strSSCC nvarchar(max), @fltQtd decimal,  @strUser nvarchar(max)  AS
SET NOCOUNT ON;
 


 -- Update ao Mov_SSCC_Cab
UPDATE Mov_SSCC_Cab 
SET fltQtdActual = fltQtdActual + @fltQtd, dtmDataAlteracao = GETDATE(), strUserAlteracao = @strUser WHERE strIA_00_SSCC = @strSSCC

-- Elimina Movimento na Tabela de Produções
UPDATE [Mov_OrdensProducaoSimples_Movimentos]
SET [bitEliminado] = 1,[dtmDataEliminado] = GETDATE(),[strUserEliminado] = @strUser
WHERE [intId] = @ID_MovLin

GO

/****** Object:  StoredProcedure [dbo].[SP_PROD_AbateConsumo_SSCC]    Script Date: 14/10/2024 11:10:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


 
CREATE PROCEDURE [dbo].[SP_PROD_AbateConsumo_SSCC] @strNumDoc nvarchar(max),  @strCodArtigo nvarchar(max), @strHashCab nvarchar(max), @strHashLin nvarchar(max), @strSSCC nvarchar(max), @fltQtd decimal,  @strUserRegisto nvarchar(max)  AS
SET NOCOUNT ON;
 

DECLARE @fltQtdSSCC Decimal
declare @strTipoUn nvarchar(max)
SELECT @fltQtdSSCC=[fltQtdActual]  FROM [Mov_SSCC_Cab] where [strIA_00_SSCC]= @strSSCC
SELECT @strTipoUn=[strCodUnidadeES]    FROM [Tbl_Artigos] where [strCodArtigo]=@strCodArtigo

-- Calcula Qtd a abater ao SSCC
if @fltQtdSSCC < @fltQtd BEgin
set @fltQtd = @fltQtdSSCC  end


 -- Update ao Mov_SSCC_Cab
UPDATE Mov_SSCC_Cab 
SET fltQtdActual = fltQtdActual - @fltQtd, dtmDataAlteracao = GETDATE(), strUserAlteracao = @strUserRegisto WHERE strIA_00_SSCC = @strSSCC

-- Regista Movimento na Tabela de Produções
INSERT INTO [dbo].[Mov_OrdensProducaoSimples_Movimentos]([strNumDoc],[strHashCab],[strHashLin],[strCodArtigo],[fltQtd],[strTipoUn],[strSSCC],[intSinal],[dtmDataRegisto],[strUserRegisto])
     VALUES (@strNumDoc,@strHashCab,@strHashLin,@strCodArtigo,@fltQtd,@strTipoUn,@strSSCC,-1,GETDATE(),@strUserRegisto)
GO

/****** Object:  StoredProcedure [dbo].[abateConsumosProducaoSimples]    Script Date: 14/10/2024 11:10:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[abateConsumosProducaoSimples]  @HashAcao nvarchar(max), @TipoAcao int, @CodArtigo nvarchar(max), @HashOrdem nvarchar(max), @Ordem nvarchar(max), @CodSSCC nvarchar(max), @QtdProduzir int, @Turno nvarchar(max), @Equipa int, @Utilizador nvarchar(max), @SubUtilizador nvarchar(max) AS
SET NOCOUNT ON;
 
BEGIN TRANSACTION;
DECLARE @Resultado int;
 
DECLARE @TblConsumos TABLE
(idx smallint Primary Key IDENTITY(1,1),
col_consumos_artigo NVARCHAR(MAX),
col_consumos_descricao NVARCHAR(MAX),
col_consumos_qtd FLOAT,
col_consumos_qtd_stock FLOAT);
 
DECLARE @IdSSCC int;
SELECT @IdSSCC = intIdCabSSCC FROM Mov_SSCC_Cab WHERE strIA_00_SSCC = @CodSSCC;
 
DECLARE @CodEmp NVARCHAR(10);
DECLARE @TipoDoc NVARCHAR(20);
DECLARE @Ano NVARCHAR(10);
DECLARE @Numero int;
SELECT @CodEmp = strCodEmp, @TipoDoc = strTipoDoc, @Ano = strAno, @Numero = intNumero FROM Mov_OrdensMontagem_Cab WHERE strHash = @HashOrdem;
 
INSERT INTO @TblConsumos (col_consumos_artigo, col_consumos_descricao, col_consumos_qtd, col_consumos_qtd_stock) 
SELECT CodComponente AS [col_consumos_artigo],
	COALESCE(DescComponente, '''') AS [col_consumos_descricao],
	CAST(COALESCE(QtdTotal, 0) AS float) AS [col_consumos_qtd],
	CAST(COALESCE(QtdParaProducao, 0) AS float) AS [col_consumos_qtd_stock]  
FROM USR_RST3_VIEW_Producao_ConsumosOM    
WHERE strCodEmp = @CodEmp AND strTipoDoc = @TipoDoc AND strAno = @Ano AND intNumero = @Numero AND CodArtigo = @CodArtigo
 
DECLARE @VerificaStock int;
--SELECT @VerificaStock = MIN(col_consumos_qtd_stock - (col_consumos_qtd*@QtdProduzir)) FROM @TblConsumos;
SELECT  @VerificaStock = case when(MIN(col_consumos_qtd_stock -(col_consumos_qtd*@QtdProduzir)) < 0)  then -1 else 0 end  from @TblConsumos

IF @VerificaStock < 0 BEGIN
	SET @Resultado = -1;
	GOTO FIM;
END;
  
DECLARE @Maquina NVARCHAR(20);
DECLARE @Localizacao NVARCHAR(20);

SELECT @Maquina = strMaquina FROM Mov_OrdensMontagem_Cab WHERE strNumero = @Ordem
SELECT @Localizacao = strLocalizacao FROM Tbl_Maquinas WHERE strCodigo = @Maquina
 
DECLARE @i int;
DECLARE @NumLinhas int;
DECLARE @iArtigo NVARCHAR(MAX);
DECLARE @iQtdNecessaria float;
DECLARE @iSSCC NVARCHAR(MAX);
DECLARE @iIdSSCC int;
DECLARE @iQtdAtual float;
DECLARE @iQtdAtualizada float;
DECLARE @iLote NVARCHAR(MAX);
DECLARE @iQtdConsumido float;
 
SET @i = 1;
SET @NumLinhas = (SELECT COUNT(*) FROM @TblConsumos);
 
IF @NumLinhas > 0 
BEGIN
	WHILE(@i <= @NumLinhas)
	BEGIN
		SELECT @iArtigo = col_consumos_artigo, @iQtdNecessaria = col_consumos_qtd * @QtdProduzir FROM @TblConsumos WHERE idx = @i;
 
 
		WHILE @iQtdNecessaria > 0
		BEGIN
 
			SELECT TOP(1) @iSSCC = strIA_00_SSCC, @iIdSSCC = intIdCabSSCC, @iLote = strIA_10_Lote, @iQtdAtual = fltQtdActual FROM Mov_SSCC_Cab
			WHERE strArtigoCodigo = @iArtigo and fltQtdActual > 0 and strLocalizacaoDoc = @Ordem and strCodLocalizacao = @Localizacao and bitEliminado = 0 and bitSSCCLogistico = 0 ORDER BY dtmDataTransArm;
 
			IF @iQtdAtual >= @iQtdNecessaria 
				BEGIN
					SET @iQtdConsumido = @iQtdNecessaria
					SET @iQtdAtualizada = @iQtdAtual - @iQtdNecessaria
					SET @iQtdNecessaria = 0
				END 
			ELSE
				BEGIN
					SET @iQtdConsumido = @iQtdAtual
					SET @iQtdNecessaria = @iQtdNecessaria - @iQtdAtual
					SET @iQtdAtualizada = 0
				END
 
			UPDATE Mov_SSCC_Cab SET fltQtdActual = @iQtdAtualizada, dtmDataAlteracao = GETDATE(), strUserAlteracao = @SubUtilizador WHERE intIdCabSSCC = @iIdSSCC
 
			INSERT INTO Mov_SSCC_Lin (strM2004HashAcao, intM2004TipoAcao, intM2003IdCabSSCC, strM2003SSCCCab, intM2004NumItem, strM2004SSCCOrigem, strM2004CodArtigo, strM2004Lote, fltM2004Quantidade, dtmM2004DataRegisto, strM2004User) 
			VALUES (@HashAcao, @TipoAcao, @IdSSCC, @CodSSCC, @i, @iSSCC, @iArtigo, @iLote, @iQtdConsumido, GETDATE(), @SubUtilizador)
		END;
		SET @i = @i + 1;
	END;
END;
 
 
 
SET @Resultado = 1;
FIM:
COMMIT TRANSACTION;
RETURN @Resultado;
GO