CREATE OR ALTER procedure SP_GRADE_MODELO (
    ID integer = 0,
    TIPO varchar(10) = 'P')
returns (
    GRADE integer,
    TAMANHO integer,
    TAMANHO_DESCRICAO varchar(10))
as
declare variable MODELO_TAM varchar(1000);
begin

    grade = 0;


    if(TIPO = 'P')then
    begin
      select first 1 m.grade_codigo,
        M.T01||'|'||M.T02||'|'||M.T03||'|'||M.T04||'|'||M.T05||'|'||
        M.T06||'|'||M.T07||'|'||M.T08||'|'||M.T09||'|'||M.T10||'|'||
        M.T11||'|'||M.T12||'|'||M.T13||'|'||M.T14||'|'||M.T15||'|'||
        M.T16||'|'||M.T17||'|'||M.T18||'|'||M.T19||'|'||M.T20
      from tbproduto p, tbmodelo m
      where p.codigo = :ID and m.codigo = p.modelo_codigo
      into grade, modelo_tam;

      if(coalesce(grade,0) = 0)then
      begin

            select first 1 p.grade_codigo,
           iif(char_length(M.T01) > 0,1,0)||'|'||
           iif(char_length(M.T02) > 0,1,0)||'|'||
           iif(char_length(M.T03) > 0,1,0)||'|'||
           iif(char_length(M.T04) > 0,1,0)||'|'||
           iif(char_length(M.T05) > 0,1,0)||'|'||
           iif(char_length(M.T06) > 0,1,0)||'|'||
           iif(char_length(M.T07) > 0,1,0)||'|'||
           iif(char_length(M.T08) > 0,1,0)||'|'||
           iif(char_length(M.T09) > 0,1,0)||'|'||
           iif(char_length(M.T10) > 0,1,0)||'|'||
           iif(char_length(M.T11) > 0,1,0)||'|'||
           iif(char_length(M.T12) > 0,1,0)||'|'||
           iif(char_length(M.T13) > 0,1,0)||'|'||
           iif(char_length(M.T14) > 0,1,0)||'|'||
           iif(char_length(M.T15) > 0,1,0)||'|'||
           iif(char_length(M.T16) > 0,1,0)||'|'||
           iif(char_length(M.T17) > 0,1,0)||'|'||
           iif(char_length(M.T18) > 0,1,0)||'|'||
           iif(char_length(M.T19) > 0,1,0)||'|'||
           iif(char_length(M.T20) > 0,1,0)
          from tbproduto p, tbgrade m
          where p.codigo = :ID
          and m.codigo = p.grade_codigo
          into grade, modelo_tam;

      end

    end else
    begin
        select first 1 m.grade_codigo,
            M.T01||'|'||M.T02||'|'||M.T03||'|'||M.T04||'|'||M.T05||'|'||
            M.T06||'|'||M.T07||'|'||M.T08||'|'||M.T09||'|'||M.T10||'|'||
            M.T11||'|'||M.T12||'|'||M.T13||'|'||M.T14||'|'||M.T15||'|'||
            M.T16||'|'||M.T17||'|'||M.T18||'|'||M.T19||'|'||M.T20
          from tbmodelo m
          where m.codigo = :ID
          into grade, modelo_tam;
    end

  for
     select
        POSICAO,
        TAMANHO
     from(
        select
            POSICAO,
            TAMANHO,
            fn_array(:modelo_tam,POSICAO,'|') as HABILITADO
        from(
    
            select
                row_number() over() as POSICAO,
                fn_tamanho_grade(:grade,row_number() over()) as TAMANHO
            from SP_GERAR_LINHAS(20)
    
        ) where char_length(TAMANHO) > 0
    ) where HABILITADO = '1'
  into
    :TAMANHO,
    :TAMANHO_DESCRICAO
  do begin
    suspend;
  end

end;

COMMENT ON PARAMETER SP_GRADE_MODELO.TIPO IS
'P - Produto, M - Modelo';

