Função para pegar o Id dos Insumos de uma composição
USE [CORPORERM]
GO
/****** Object: UserDefinedFunction [dbo].[GETIDCOMPOSICAO] Script Date: 08/11/2015 09:32:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GETIDCOMPOSICAO] ( @CODCOLIGADA INT, @IDPRJ INT,@IDCMP INT )
RETURNS
@TABELA_RETORNO TABLE (
CODCOLIGADA INT,
IDPRJ INT,
IDCMP INT,
IDCMPFILHA INT,
IDISM INT,
QUANTIDADE NUMERIC(38,4),
QUANTIDADE_ACUMULADA NUMERIC(38,4),
IDCMP_PAI INT,
IDCMP_RAIZ INT,
NIVEL INT
)
AS
BEGIN
DECLARE @v_CODCOLIGADA INT
DECLARE @v_IDPRJ INT
DECLARE @v_IDCMP INT
DECLARE @v_IDISM INT
DECLARE @v_IDCMPFILHA INT
DECLARE @v_QUANTIDADE NUMERIC(38,4)
DECLARE @v_QUANTIDADE_ACUMULADA NUMERIC(38,4)
DECLARE @v_IDCMP_PAI INT
DECLARE @NIVEL INT
DECLARE @v_NIVEL INT
DECLARE @CONTINUA INT
DECLARE @tmpTable TABLE (CODCOLIGADA INT,IDPRJ INT,IDCMP INT, IDISM INT, IDCMPFILHA INT,QUANTIDADE NUMERIC(38,4), QUANTIDADE_ACUMULADA NUMERIC(38,4), IDCMP_PAI INT, IDCMP_RAIZ INT, NIVEL INT)
DECLARE @tmpTable2 TABLE (CODCOLIGADA INT,IDPRJ INT,IDCMP INT, IDISM INT, IDCMPFILHA INT,QUANTIDADE NUMERIC(38,4), QUANTIDADE_ACUMULADA NUMERIC(38,4), IDCMP_PAI INT, IDCMP_RAIZ INT, NIVEL INT)
SET @NIVEL = 0
SET @CONTINUA = 0
--Alimenta a tabela com as informações iniciais
insert into @tmpTable select CODCOLIGADA, IDPRJ, IDCMP, IDISM , IDCMPFILHA, QUANTIDADE/10000 , QUANTIDADE/10000 as QUANTIDADE_ACUMULADA, @IDCMP, @IDCMP, @NIVEL from MRECCMP where CODCOLIGADA = @CODCOLIGADA and IDPRJ = @IDPRJ and idcmp = @IDCMP -- and IDCMPFILHA = 620108
-- Insere no retorno dos registros ok que não tem filhos
insert into @TABELA_RETORNO select CODCOLIGADA, IDPRJ, IDCMP, IDCMPFILHA, IDISM, QUANTIDADE, QUANTIDADE as QUANTIDADE_ACUMULADA, @IDCMP, @IDCMP, @NIVEL as NIVEL from @tmpTable -- where IDCMPFILHA is null
-- remove da tmptable os OK que já estão no retorno(que não tem filhos)
delete from @tmpTable where IDCMPFILHA is null
-------------------------------------------------------------------------------------
--verifica se tem mais composições filhas
SET @CONTINUA = (select COUNT(IDCMP) from @tmpTable where IDCMPFILHA is not null and idcmp = @IDCMP)
WHILE( @CONTINUA > 0) BEGIN
SET @NIVEL = @NIVEL + 1
DECLARE myCursor CURSOR FOR
select CODCOLIGADA, IDPRJ, IDCMP, IDISM, IDCMPFILHA , IDCMP_PAI from @tmpTable where IDCMPFILHA is not null --pega os idcmp das filhas
OPEN myCursor
FETCH NEXT FROM myCursor INTO @v_CODCOLIGADA, @v_IDPRJ, @v_IDCMP, @v_IDISM, @v_IDCMPFILHA, @v_IDCMP_PAI
WHILE (@@FETCH_STATUS = 0)
BEGIN
insert into @tmpTable2 select CODCOLIGADA, IDPRJ, IDCMP, IDISM , IDCMPFILHA, QUANTIDADE/10000, QUANTIDADE/10000 as QUANTIDADE_ACUMULADA, @v_IDCMP, @IDCMP, @NIVEL as NIVEL from MRECCMP where CODCOLIGADA = @CODCOLIGADA and IDPRJ = @IDPRJ and idcmp = @v_IDCMPFILHA
FETCH NEXT FROM myCursor INTO @v_CODCOLIGADA, @v_IDPRJ, @v_IDCMP, @v_IDISM, @v_IDCMPFILHA, @v_IDCMP_PAI
END
CLOSE myCursor
DEALLOCATE myCursor
delete from @tmpTable --Limpa temp table
insert into @tmpTable select * from @tmpTable2 --pega os valores do nivel anterior e passa para temporaria
delete from @tmpTable2 ----Limpa temp table do novel
insert into @TABELA_RETORNO select CODCOLIGADA, IDPRJ, IDCMP, IDCMPFILHA, IDISM, QUANTIDADE, QUANTIDADE_ACUMULADA, IDCMP_PAI, @IDCMP, @NIVEL as NIVEL from @tmpTable --where IDCMPFILHA is null --insere no retorno apenas quem não tem próximo nivel
delete from @tmpTable where IDCMPFILHA is null --limpa as inseridas no retorno
SET @CONTINUA = (select COUNT(IDCMP) from @tmpTable where IDCMPFILHA is not null) --verifica se tem mais niveis
END
----Acumula as quantidades--------------------------------------------------------------------------------------------------------
DECLARE @v_SOMA NUMERIC(38,4)
--DECLARE @v_TESTE NUMERIC(38,4)
DECLARE CUR_NIVEL CURSOR FOR --Seleciona os níveis no cursor CUR_NIVEL para acumular nivel por nivel gradualmente
select distinct NIVEL from @TABELA_RETORNO where NIVEL > 0 order by NIVEL -- o nivel 0 não precisa ser acumulado é nescessário seguir os niveis na ordem crescente
OPEN CUR_NIVEL
FETCH NEXT FROM CUR_NIVEL INTO @v_NIVEL --Etapa um separa os níveis ACUMULA NIVEL A NIVEL
WHILE (@@FETCH_STATUS = 0) --controla o fim dos registros so cursor
BEGIN
DECLARE CUR_QUANT CURSOR FOR ---Seleciona todos os itens do nível na variável @v_NIVEL
select CODCOLIGADA,IDPRJ,IDCMP,IDCMPFILHA,IDISM,QUANTIDADE,QUANTIDADE_ACUMULADA,IDCMP_PAI,NIVEL FROM @TABELA_RETORNO where NIVEL = @v_NIVEL
OPEN CUR_QUANT
FETCH NEXT FROM CUR_QUANT INTO @v_CODCOLIGADA, @v_IDPRJ, @v_IDCMP, @v_IDCMPFILHA, @v_IDISM, @v_QUANTIDADE, @v_QUANTIDADE_ACUMULADA, @v_IDCMP_PAI, @NIVEL
WHILE (@@FETCH_STATUS = 0) --controla o fim dos registros so cursor
BEGIN
SET @v_SOMA = (select QUANTIDADE_ACUMULADA from @TABELA_RETORNO where NIVEL = @v_NIVEL -1 and CODCOLIGADA = @v_CODCOLIGADA and IDPRJ = @v_IDPRJ and IDCMP = @v_IDCMP_PAI and IDCMPFILHA = @v_IDCMP) -- (Pega a quantidade do pai e passa para varialvel soma)
IF @v_SOMA IS NOT NULL BEGIN
IF @v_IDISM is null BEGIN -- Quando a variavel @v_IDISM tiver valor é ultimo nível
--SET @v_TESTE = (select QUANTIDADE_ACUMULADA from @TABELA_RETORNO where CODCOLIGADA = @v_CODCOLIGADA and IDPRJ = @v_IDPRJ and idcmp = @v_IDCMP and IDCMPFILHA = @v_IDCMPFILHA and IDISM is null AND NIVEL = @v_NIVEL)
--Atualiza acumulado enquanto aida não for ultimo nível
update @TABELA_RETORNO set QUANTIDADE_ACUMULADA = QUANTIDADE_ACUMULADA * @v_SOMA where CODCOLIGADA = @v_CODCOLIGADA and IDPRJ = @v_IDPRJ and idcmp = @v_IDCMP and IDCMPFILHA = @v_IDCMPFILHA and IDISM is null AND NIVEL = @v_NIVEL --pega o a qu de nivel anterios e multiplica pelo valor de nivel atual
END ELSE BEGIN
--Atualiza acumulado quanto for ultimo Nível
update @TABELA_RETORNO set QUANTIDADE_ACUMULADA = QUANTIDADE_ACUMULADA * @v_SOMA where CODCOLIGADA = @v_CODCOLIGADA and IDPRJ = @v_IDPRJ and idcmp = @v_IDCMP and IDISM = @v_IDISM AND NIVEL = @v_NIVEL --pega o a qu de nivel anterios e multiplica pelo valor de nivel atual
END
END
--passa para o proximo registro se ouver podendo ser um insumo ou uma composição
FETCH NEXT FROM CUR_QUANT INTO @v_CODCOLIGADA, @v_IDPRJ, @v_IDCMP, @v_IDCMPFILHA, @v_IDISM, @v_QUANTIDADE, @v_QUANTIDADE_ACUMULADA, @v_IDCMP_PAI, @NIVEL
END
CLOSE CUR_QUANT
DEALLOCATE CUR_QUANT
-- passa para o proximo nivel se ouver
FETCH NEXT FROM CUR_NIVEL INTO @v_NIVEL
END
CLOSE CUR_NIVEL
DEALLOCATE CUR_NIVEL
--tira as composições do retorno deixando apenas os isumos
delete @TABELA_RETORNO where IDISM is null
RETURN
END