SET TERM ^ ;

CREATE OR ALTER procedure SPC_GRADE_ATIVA_PRODUTO (
    PRODUTO_ID integer,
    SO_ATIVOS integer = 1)
returns (
    TAMANHO integer,
    DESCRICAO varchar(20),
    GRADE integer,
    ATIVA integer)
as
declare variable LINHA_CORRENTE integer;
declare variable DESCRICAO2 varchar(100);
declare variable CODIGO integer;
declare variable TOTAL_TAMANHOS integer;
declare variable DATA date;
declare variable STATUS integer;
declare variable STATUSEXCLUSAO integer;
declare variable USUARIO_CODIGO integer;
declare variable T01 varchar(10);
declare variable T02 varchar(10);
declare variable T03 varchar(10);
declare variable T04 varchar(10);
declare variable T05 varchar(10);
declare variable T06 varchar(10);
declare variable T07 varchar(10);
declare variable T08 varchar(10);
declare variable T09 varchar(10);
declare variable T10 varchar(10);
declare variable T11 varchar(10);
declare variable T12 varchar(10);
declare variable T13 varchar(10);
declare variable T14 varchar(10);
declare variable T15 varchar(10);
declare variable T16 varchar(10);
declare variable T17 varchar(10);
declare variable T18 varchar(10);
declare variable T19 varchar(10);
declare variable T20 varchar(10);
declare variable E01 varchar(10);
declare variable E02 varchar(10);
declare variable E03 varchar(10);
declare variable E04 varchar(10);
declare variable E05 varchar(10);
declare variable E06 varchar(10);
declare variable E07 varchar(10);
declare variable E08 varchar(10);
declare variable E09 varchar(10);
declare variable E10 varchar(10);
declare variable E11 varchar(10);
declare variable E12 varchar(10);
declare variable E13 varchar(10);
declare variable E14 varchar(10);
declare variable E15 varchar(10);
declare variable E16 varchar(10);
declare variable E17 varchar(10);
declare variable E18 varchar(10);
declare variable E19 varchar(10);
declare variable E20 varchar(10);
declare variable USA01 varchar(10);
declare variable USA02 varchar(10);
declare variable USA03 varchar(10);
declare variable USA04 varchar(10);
declare variable USA05 varchar(10);
declare variable USA06 varchar(10);
declare variable USA07 varchar(10);
declare variable USA08 varchar(10);
declare variable USA09 varchar(10);
declare variable USA10 varchar(10);
declare variable USA11 varchar(10);
declare variable USA12 varchar(10);
declare variable USA13 varchar(10);
declare variable USA14 varchar(10);
declare variable USA15 varchar(10);
declare variable USA16 varchar(10);
declare variable USA17 varchar(10);
declare variable USA18 varchar(10);
declare variable USA19 varchar(10);
declare variable USA20 varchar(10);
declare variable EUR01 varchar(10);
declare variable EUR02 varchar(10);
declare variable EUR03 varchar(10);
declare variable EUR04 varchar(10);
declare variable EUR05 varchar(10);
declare variable EUR06 varchar(10);
declare variable EUR07 varchar(10);
declare variable EUR08 varchar(10);
declare variable EUR09 varchar(10);
declare variable EUR10 varchar(10);
declare variable EUR11 varchar(10);
declare variable EUR12 varchar(10);
declare variable EUR13 varchar(10);
declare variable EUR14 varchar(10);
declare variable EUR15 varchar(10);
declare variable EUR16 varchar(10);
declare variable EUR17 varchar(10);
declare variable EUR18 varchar(10);
declare variable EUR19 varchar(10);
declare variable EUR20 varchar(10);
declare variable A01 integer;
declare variable A02 integer;
declare variable A03 integer;
declare variable A04 integer;
declare variable A05 integer;
declare variable A06 integer;
declare variable A07 integer;
declare variable A08 integer;
declare variable A09 integer;
declare variable A10 integer;
declare variable A11 integer;
declare variable A12 integer;
declare variable A13 integer;
declare variable A14 integer;
declare variable A15 integer;
declare variable A16 integer;
declare variable A17 integer;
declare variable A18 integer;
declare variable A19 integer;
declare variable A20 integer;
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
    :DESCRICAO2,
    :GRADE,
    :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

  end

  for
    select LINHA_CORRENTE from sp_gerar_linhas(20)
  into
    :LINHA_CORRENTE
  do begin
    TAMANHO = LINHA_CORRENTE;

    if(SO_ATIVOS = 1)then
    begin
        ATIVA = 1;

        if(:LINHA_CORRENTE = 1 and :A01 = '1' and coalesce(:T01,'') <> '' )then begin DESCRICAO = :T01; suspend; end
        if(:LINHA_CORRENTE = 2 and :A02 = '1' and coalesce(:T02,'') <> '' )then begin DESCRICAO = :T02; suspend; end
        if(:LINHA_CORRENTE = 3 and :A03 = '1' and coalesce(:T03,'') <> '' )then begin DESCRICAO = :T03; suspend; end
        if(:LINHA_CORRENTE = 4 and :A04 = '1' and coalesce(:T04,'') <> '' )then begin DESCRICAO = :T04; suspend; end
        if(:LINHA_CORRENTE = 5 and :A05 = '1' and coalesce(:T05,'') <> '' )then begin DESCRICAO = :T05; suspend; end
        if(:LINHA_CORRENTE = 6 and :A06 = '1' and coalesce(:T06,'') <> '' )then begin DESCRICAO = :T06; suspend; end
        if(:LINHA_CORRENTE = 7 and :A07 = '1' and coalesce(:T07,'') <> '' )then begin DESCRICAO = :T07; suspend; end
        if(:LINHA_CORRENTE = 8 and :A08 = '1' and coalesce(:T08,'') <> '' )then begin DESCRICAO = :T08; suspend; end
        if(:LINHA_CORRENTE = 9 and :A09 = '1' and coalesce(:T09,'') <> '' )then begin DESCRICAO = :T09; suspend; end
        if(:LINHA_CORRENTE =10 and :A10 = '1' and coalesce(:T10,'') <> '' )then begin DESCRICAO = :T10; suspend; end
        if(:LINHA_CORRENTE =11 and :A11 = '1' and coalesce(:T11,'') <> '' )then begin DESCRICAO = :T11; suspend; end
        if(:LINHA_CORRENTE =12 and :A12 = '1' and coalesce(:T12,'') <> '' )then begin DESCRICAO = :T12; suspend; end
        if(:LINHA_CORRENTE =13 and :A13 = '1' and coalesce(:T13,'') <> '' )then begin DESCRICAO = :T13; suspend; end
        if(:LINHA_CORRENTE =14 and :A14 = '1' and coalesce(:T14,'') <> '' )then begin DESCRICAO = :T14; suspend; end
        if(:LINHA_CORRENTE =15 and :A15 = '1' and coalesce(:T15,'') <> '' )then begin DESCRICAO = :T15; suspend; end
        if(:LINHA_CORRENTE =16 and :A16 = '1' and coalesce(:T16,'') <> '' )then begin DESCRICAO = :T16; suspend; end
        if(:LINHA_CORRENTE =17 and :A17 = '1' and coalesce(:T17,'') <> '' )then begin DESCRICAO = :T17; suspend; end
        if(:LINHA_CORRENTE =18 and :A18 = '1' and coalesce(:T18,'') <> '' )then begin DESCRICAO = :T18; suspend; end
        if(:LINHA_CORRENTE =19 and :A19 = '1' and coalesce(:T19,'') <> '' )then begin DESCRICAO = :T19; suspend; end
        if(:LINHA_CORRENTE =20 and :A20 = '1' and coalesce(:T20,'') <> '' )then begin DESCRICAO = :T20; suspend; end
    end else
    begin
        if(:LINHA_CORRENTE = 1 and coalesce(:T01,'') <> '' )then begin DESCRICAO = :T01; ATIVA = iif(:A01 = '1' and coalesce(:T01,'') <> '',1,0); suspend; end
        if(:LINHA_CORRENTE = 2 and coalesce(:T02,'') <> '' )then begin DESCRICAO = :T02; ATIVA = iif(:A02 = '1' and coalesce(:T02,'') <> '',1,0); suspend; end
        if(:LINHA_CORRENTE = 3 and coalesce(:T03,'') <> '' )then begin DESCRICAO = :T03; ATIVA = iif(:A03 = '1' and coalesce(:T03,'') <> '',1,0); suspend; end
        if(:LINHA_CORRENTE = 4 and coalesce(:T04,'') <> '' )then begin DESCRICAO = :T04; ATIVA = iif(:A04 = '1' and coalesce(:T04,'') <> '',1,0); suspend; end
        if(:LINHA_CORRENTE = 5 and coalesce(:T05,'') <> '' )then begin DESCRICAO = :T05; ATIVA = iif(:A05 = '1' and coalesce(:T05,'') <> '',1,0); suspend; end
        if(:LINHA_CORRENTE = 6 and coalesce(:T06,'') <> '' )then begin DESCRICAO = :T06; ATIVA = iif(:A06 = '1' and coalesce(:T06,'') <> '',1,0); suspend; end
        if(:LINHA_CORRENTE = 7 and coalesce(:T07,'') <> '' )then begin DESCRICAO = :T07; ATIVA = iif(:A07 = '1' and coalesce(:T07,'') <> '',1,0); suspend; end
        if(:LINHA_CORRENTE = 8 and coalesce(:T08,'') <> '' )then begin DESCRICAO = :T08; ATIVA = iif(:A08 = '1' and coalesce(:T08,'') <> '',1,0); suspend; end
        if(:LINHA_CORRENTE = 9 and coalesce(:T09,'') <> '' )then begin DESCRICAO = :T09; ATIVA = iif(:A09 = '1' and coalesce(:T09,'') <> '',1,0); suspend; end
        if(:LINHA_CORRENTE =10 and coalesce(:T10,'') <> '' )then begin DESCRICAO = :T10; ATIVA = iif(:A10 = '1' and coalesce(:T10,'') <> '',1,0); suspend; end
        if(:LINHA_CORRENTE =11 and coalesce(:T11,'') <> '' )then begin DESCRICAO = :T11; ATIVA = iif(:A11 = '1' and coalesce(:T11,'') <> '',1,0); suspend; end
        if(:LINHA_CORRENTE =12 and coalesce(:T12,'') <> '' )then begin DESCRICAO = :T12; ATIVA = iif(:A12 = '1' and coalesce(:T12,'') <> '',1,0); suspend; end
        if(:LINHA_CORRENTE =13 and coalesce(:T13,'') <> '' )then begin DESCRICAO = :T13; ATIVA = iif(:A13 = '1' and coalesce(:T13,'') <> '',1,0); suspend; end
        if(:LINHA_CORRENTE =14 and coalesce(:T14,'') <> '' )then begin DESCRICAO = :T14; ATIVA = iif(:A14 = '1' and coalesce(:T14,'') <> '',1,0); suspend; end
        if(:LINHA_CORRENTE =15 and coalesce(:T15,'') <> '' )then begin DESCRICAO = :T15; ATIVA = iif(:A15 = '1' and coalesce(:T15,'') <> '',1,0); suspend; end
        if(:LINHA_CORRENTE =16 and coalesce(:T16,'') <> '' )then begin DESCRICAO = :T16; ATIVA = iif(:A16 = '1' and coalesce(:T16,'') <> '',1,0); suspend; end
        if(:LINHA_CORRENTE =17 and coalesce(:T17,'') <> '' )then begin DESCRICAO = :T17; ATIVA = iif(:A17 = '1' and coalesce(:T17,'') <> '',1,0); suspend; end
        if(:LINHA_CORRENTE =18 and coalesce(:T18,'') <> '' )then begin DESCRICAO = :T18; ATIVA = iif(:A18 = '1' and coalesce(:T18,'') <> '',1,0); suspend; end
        if(:LINHA_CORRENTE =19 and coalesce(:T19,'') <> '' )then begin DESCRICAO = :T19; ATIVA = iif(:A19 = '1' and coalesce(:T19,'') <> '',1,0); suspend; end
        if(:LINHA_CORRENTE =20 and coalesce(:T20,'') <> '' )then begin DESCRICAO = :T20; ATIVA = iif(:A20 = '1' and coalesce(:T20,'') <> '',1,0); suspend; end
    end
  end

end
^

SET TERM ; ^

