SET TERM ^ ;

CREATE OR ALTER procedure SPC_NFS_ITEM_FAMILIA (
    NFS_ID integer)
returns (
    FAMILIA_ID integer,
    PERCENTUAL numeric(15,5))
as
declare variable PERC_TOTAL numeric(15,5);
begin

   perc_total = 0;
   FOR
   SELECT FAMILIA_ID,
          CAST(ROUND(VALOR / SUM(VALOR) OVER (),5) AS NUMERIC(15,5)) TOTAL
   FROM (
   SELECT P.FAMILIA_INVENTARIO FAMILIA_ID, SUM(I.VALOR_TOTAL) VALOR
     FROM TBNFS_ITEM I, TBPRODUTO P, TBOPERACAO O
    WHERE I.PRODUTO_CODIGO = P.CODIGO
      AND I.OPERACAO_CODIGO = O.CODIGO
      AND O.CONTROLE_FATURAMENTO = '1'
      AND I.NFS_CONTROLE = :NFS_ID
      GROUP BY P.FAMILIA_INVENTARIO
   )
   INTO :FAMILIA_ID, :PERCENTUAL
   DO
      BEGIN
      PERC_TOTAL = PERC_TOTAL + :PERCENTUAL;
      IF (PERC_TOTAL > 1.00000) THEN
          BEGIN
          :PERCENTUAL = :PERCENTUAL - (PERC_TOTAL-1.00000);
          end 
      SUSPEND;
      END

end
^

SET TERM ; ^

