CREATE OR ALTER PROCEDURE SPC_GRADE_PRODUTO (
    produto_id integer)
returns (
    descricao varchar(100),
    codigo integer,
    total_tamanhos integer,
    data date,
    status integer,
    statusexclusao integer,
    usuario_codigo integer,
    t01 varchar(10),
    t02 varchar(10),
    t03 varchar(10),
    t04 varchar(10),
    t05 varchar(10),
    t06 varchar(10),
    t07 varchar(10),
    t08 varchar(10),
    t09 varchar(10),
    t10 varchar(10),
    t11 varchar(10),
    t12 varchar(10),
    t13 varchar(10),
    t14 varchar(10),
    t15 varchar(10),
    t16 varchar(10),
    t17 varchar(10),
    t18 varchar(10),
    t19 varchar(10),
    t20 varchar(10),
    e01 varchar(10),
    e02 varchar(10),
    e03 varchar(10),
    e04 varchar(10),
    e05 varchar(10),
    e06 varchar(10),
    e07 varchar(10),
    e08 varchar(10),
    e09 varchar(10),
    e10 varchar(10),
    e11 varchar(10),
    e12 varchar(10),
    e13 varchar(10),
    e14 varchar(10),
    e15 varchar(10),
    e16 varchar(10),
    e17 varchar(10),
    e18 varchar(10),
    e19 varchar(10),
    e20 varchar(10),
    usa01 varchar(10),
    usa02 varchar(10),
    usa03 varchar(10),
    usa04 varchar(10),
    usa05 varchar(10),
    usa06 varchar(10),
    usa07 varchar(10),
    usa08 varchar(10),
    usa09 varchar(10),
    usa10 varchar(10),
    usa11 varchar(10),
    usa12 varchar(10),
    usa13 varchar(10),
    usa14 varchar(10),
    usa15 varchar(10),
    usa16 varchar(10),
    usa17 varchar(10),
    usa18 varchar(10),
    usa19 varchar(10),
    usa20 varchar(10),
    eur01 varchar(10),
    eur02 varchar(10),
    eur03 varchar(10),
    eur04 varchar(10),
    eur05 varchar(10),
    eur06 varchar(10),
    eur07 varchar(10),
    eur08 varchar(10),
    eur09 varchar(10),
    eur10 varchar(10),
    eur11 varchar(10),
    eur12 varchar(10),
    eur13 varchar(10),
    eur14 varchar(10),
    eur15 varchar(10),
    eur16 varchar(10),
    eur17 varchar(10),
    eur18 varchar(10),
    eur19 varchar(10),
    eur20 varchar(10),
    a01 integer,
    a02 integer,
    a03 integer,
    a04 integer,
    a05 integer,
    a06 integer,
    a07 integer,
    a08 integer,
    a09 integer,
    a10 integer,
    a11 integer,
    a12 integer,
    a13 integer,
    a14 integer,
    a15 integer,
    a16 integer,
    a17 integer,
    a18 integer,
    a19 integer,
    a20 integer)
as
begin
  for
     select first 1
        m.DESCRICAO , m.CODIGO , m.TOTAL_TAMANHOS , m.DATA ,
        m.STATUS , m.STATUSEXCLUSAO , m.USUARIO_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 ,

        m.E01 , m.E02 , m.E03 , m.E04 , m.E05 ,
        m.E06 , m.E07 , m.E08 , m.E09 , m.E10 ,
        m.E11 , m.E12 , m.E13 , m.E14 , m.E15 ,
        m.E16 , m.E17 , m.E18 , m.E19 , m.E20 ,

        m.USA01 , m.USA02 , m.USA03 , m.USA04 ,
        m.USA05 , m.USA06 , m.USA07 , m.USA08 ,
        m.USA09 , m.USA10 , m.USA11 , m.USA12 ,
        m.USA13 , m.USA14 , m.USA15 , m.USA16 ,
        m.USA17 , m.USA18 , m.USA19 , m.USA20 ,

        m.EUR01 , m.EUR02 , m.EUR03 , m.EUR04 ,
        m.EUR05 , m.EUR06 , m.EUR07 , m.EUR08 ,
        m.EUR09 , m.EUR10 , m.EUR11 , m.EUR12 ,
        m.EUR13 , m.EUR14 , m.EUR15 , m.EUR16 ,
        m.EUR17 , m.EUR18 , m.EUR19 , m.EUR20 ,

        IIF(coalesce(p.modelo_codigo,0) = 0,IIF(CHAR_LENGTH(m.T01) > 0,1,0),n.T01) as A01,
        IIF(coalesce(p.modelo_codigo,0) = 0,IIF(CHAR_LENGTH(m.T02) > 0,1,0),n.T02) as A02,
        IIF(coalesce(p.modelo_codigo,0) = 0,IIF(CHAR_LENGTH(m.T03) > 0,1,0),n.T03) as A03,
        IIF(coalesce(p.modelo_codigo,0) = 0,IIF(CHAR_LENGTH(m.T04) > 0,1,0),n.T04) as A04,
        IIF(coalesce(p.modelo_codigo,0) = 0,IIF(CHAR_LENGTH(m.T05) > 0,1,0),n.T05) as A05,
        IIF(coalesce(p.modelo_codigo,0) = 0,IIF(CHAR_LENGTH(m.T05) > 0,1,0),n.T06) as A06,
        IIF(coalesce(p.modelo_codigo,0) = 0,IIF(CHAR_LENGTH(m.T06) > 0,1,0),n.T07) as A07,
        IIF(coalesce(p.modelo_codigo,0) = 0,IIF(CHAR_LENGTH(m.T06) > 0,1,0),n.T08) as A08,
        IIF(coalesce(p.modelo_codigo,0) = 0,IIF(CHAR_LENGTH(m.T07) > 0,1,0),n.T09) as A09,
        IIF(coalesce(p.modelo_codigo,0) = 0,IIF(CHAR_LENGTH(m.T10) > 0,1,0),n.T10) as A10,
        IIF(coalesce(p.modelo_codigo,0) = 0,IIF(CHAR_LENGTH(m.T11) > 0,1,0),n.T11) as A11,
        IIF(coalesce(p.modelo_codigo,0) = 0,IIF(CHAR_LENGTH(m.T12) > 0,1,0),n.T12) as A12,
        IIF(coalesce(p.modelo_codigo,0) = 0,IIF(CHAR_LENGTH(m.T13) > 0,1,0),n.T13) as A13,
        IIF(coalesce(p.modelo_codigo,0) = 0,IIF(CHAR_LENGTH(m.T14) > 0,1,0),n.T14) as A14,
        IIF(coalesce(p.modelo_codigo,0) = 0,IIF(CHAR_LENGTH(m.T15) > 0,1,0),n.T15) as A15,
        IIF(coalesce(p.modelo_codigo,0) = 0,IIF(CHAR_LENGTH(m.T16) > 0,1,0),n.T16) as A16,
        IIF(coalesce(p.modelo_codigo,0) = 0,IIF(CHAR_LENGTH(m.T17) > 0,1,0),n.T17) as A17,
        IIF(coalesce(p.modelo_codigo,0) = 0,IIF(CHAR_LENGTH(m.T18) > 0,1,0),n.T18) as A18,
        IIF(coalesce(p.modelo_codigo,0) = 0,IIF(CHAR_LENGTH(m.T19) > 0,1,0),n.T19) as A19,
        IIF(coalesce(p.modelo_codigo,0) = 0,IIF(CHAR_LENGTH(m.T20) > 0,1,0),n.T20) as A20

    from

        tbgrade m,tbproduto p, tbmodelo n

    where m.codigo = p.GRADE_CODIGO
    and p.codigo = :PRODUTO_ID
    and iif(coalesce(p.modelo_codigo,0) >0,n.codigo = p.modelo_codigo, true)
  into
    :DESCRICAO,
    :CODIGO,
    :TOTAL_TAMANHOS,
    :DATA,
    :STATUS,
    :STATUSEXCLUSAO,
    :USUARIO_CODIGO,
    :T01,
    :T02,
    :T03,
    :T04,
    :T05,
    :T06,
    :T07,
    :T08,
    :T09,
    :T10,
    :T11,
    :T12,
    :T13,
    :T14,
    :T15,
    :T16,
    :T17,
    :T18,
    :T19,
    :T20,
    :E01,
    :E02,
    :E03,
    :E04,
    :E05,
    :E06,
    :E07,
    :E08,
    :E09,
    :E10,
    :E11,
    :E12,
    :E13,
    :E14,
    :E15,
    :E16,
    :E17,
    :E18,
    :E19,
    :E20,
    :USA01,
    :USA02,
    :USA03,
    :USA04,
    :USA05,
    :USA06,
    :USA07,
    :USA08,
    :USA09,
    :USA10,
    :USA11,
    :USA12,
    :USA13,
    :USA14,
    :USA15,
    :USA16,
    :USA17,
    :USA18,
    :USA19,
    :USA20,
    :EUR01,
    :EUR02,
    :EUR03,
    :EUR04,
    :EUR05,
    :EUR06,
    :EUR07,
    :EUR08,
    :EUR09,
    :EUR10,
    :EUR11,
    :EUR12,
    :EUR13,
    :EUR14,
    :EUR15,
    :EUR16,
    :EUR17,
    :EUR18,
    :EUR19,
    :EUR20,
    :A01,
    :A02,
    :A03,
    :A04,
    :A05,
    :A06,
    :A07,
    :A08,
    :A09,
    :A10,
    :A11,
    :A12,
    :A13,
    :A14,
    :A15,
    :A16,
    :A17,
    :A18,
    :A19,
    :A20
  do begin
    suspend;
  end

end;

COMMENT ON PROCEDURE SPC_GRADE_PRODUTO IS 'retorna a grade de um produto verificando o modelo e retornando os tamnahos por posicao e se estao abilitados';