SET TERM ^ ;

CREATE OR ALTER function FN_TAMANHO_GRADE (
    GRADE integer,
    TAMANHO integer,
    TIPO varchar(10) = 'BR')
returns varchar(50)
AS
DECLARE VARIABLE TAM_DESCRICAO VARCHAR(50);
BEGIN

    IF ( TIPO = 'USA' ) THEN
    BEGIN

        SELECT FIRST 1
            CASE :TAMANHO
                WHEN 01 THEN USA01
                WHEN 02 THEN USA02
                WHEN 03 THEN USA03
                WHEN 04 THEN USA04
                WHEN 05 THEN USA05
                WHEN 06 THEN USA06
                WHEN 07 THEN USA07
                WHEN 08 THEN USA08
                WHEN 09 THEN USA09
                WHEN 10 THEN USA10
                WHEN 11 THEN USA11
                WHEN 12 THEN USA12
                WHEN 13 THEN USA13
                WHEN 14 THEN USA14
                WHEN 15 THEN USA15
                WHEN 16 THEN USA16
                WHEN 17 THEN USA17
                WHEN 18 THEN USA18
                WHEN 19 THEN USA19
                WHEN 20 THEN USA20
            END  TAM_DESCRICAO
          FROM TBGRADE G
         WHERE G.CODIGO = :GRADE
          INTO :TAM_DESCRICAO;
    END
    ELSE
    IF ( TIPO = 'EUR' ) THEN
    BEGIN

        SELECT FIRST 1
            CASE :TAMANHO
                WHEN 01 THEN EUR01
                WHEN 02 THEN EUR02
                WHEN 03 THEN EUR03
                WHEN 04 THEN EUR04
                WHEN 05 THEN EUR05
                WHEN 06 THEN EUR06
                WHEN 07 THEN EUR07
                WHEN 08 THEN EUR08
                WHEN 09 THEN EUR09
                WHEN 10 THEN EUR10
                WHEN 11 THEN EUR11
                WHEN 12 THEN EUR12
                WHEN 13 THEN EUR13
                WHEN 14 THEN EUR14
                WHEN 15 THEN EUR15
                WHEN 16 THEN EUR16
                WHEN 17 THEN EUR17
                WHEN 18 THEN EUR18
                WHEN 19 THEN EUR19
                WHEN 20 THEN EUR20
            END  TAM_DESCRICAO
          FROM TBGRADE G
         WHERE G.CODIGO = :GRADE
          INTO :TAM_DESCRICAO;
    END
    ELSE
    IF ( TIPO = 'ETQ' ) THEN
    BEGIN

        SELECT FIRST 1
            CASE :TAMANHO
                WHEN 01 THEN ETQ01
                WHEN 02 THEN ETQ02
                WHEN 03 THEN ETQ03
                WHEN 04 THEN ETQ04
                WHEN 05 THEN ETQ05
                WHEN 06 THEN ETQ06
                WHEN 07 THEN ETQ07
                WHEN 08 THEN ETQ08
                WHEN 09 THEN ETQ09
                WHEN 10 THEN ETQ10
                WHEN 11 THEN ETQ11
                WHEN 12 THEN ETQ12
                WHEN 13 THEN ETQ13
                WHEN 14 THEN ETQ14
                WHEN 15 THEN ETQ15
                WHEN 16 THEN ETQ16
                WHEN 17 THEN ETQ17
                WHEN 18 THEN ETQ18
                WHEN 19 THEN ETQ19
                WHEN 20 THEN ETQ20
            END  TAM_DESCRICAO
          FROM TBGRADE G
         WHERE G.CODIGO = :GRADE
          INTO :TAM_DESCRICAO;
    END
    ELSE
    BEGIN
        SELECT FIRST 1
            CASE :TAMANHO
                WHEN 01 THEN T01
                WHEN 02 THEN T02
                WHEN 03 THEN T03
                WHEN 04 THEN T04
                WHEN 05 THEN T05
                WHEN 06 THEN T06
                WHEN 07 THEN T07
                WHEN 08 THEN T08
                WHEN 09 THEN T09
                WHEN 10 THEN T10
                WHEN 11 THEN T11
                WHEN 12 THEN T12
                WHEN 13 THEN T13
                WHEN 14 THEN T14
                WHEN 15 THEN T15
                WHEN 16 THEN T16
                WHEN 17 THEN T17
                WHEN 18 THEN T18
                WHEN 19 THEN T19
                WHEN 20 THEN T20
            END  TAM_DESCRICAO
          FROM TBGRADE G
         WHERE G.CODIGO = :GRADE
          INTO :TAM_DESCRICAO;
    END

    RETURN Tam_Descricao;

END
^

SET TERM ; ^

/************************************************************************/
/************************************************************************/

SET TERM ^ ;

CREATE OR ALTER function FN_TAMANHO_MODELO (
    MODELO_ID integer,
    TAMANHO integer,
    TIPO varchar(10) = 'BR')
returns varchar(50)
AS
declare variable TAM_DESCRICAO varchar(50);
BEGIN

    IF ( TIPO = 'USA' ) THEN
    BEGIN

        SELECT FIRST 1
            CASE :TAMANHO
                WHEN 01 THEN USA01
                WHEN 02 THEN USA02
                WHEN 03 THEN USA03
                WHEN 04 THEN USA04
                WHEN 05 THEN USA05
                WHEN 06 THEN USA06
                WHEN 07 THEN USA07
                WHEN 08 THEN USA08
                WHEN 09 THEN USA09
                WHEN 10 THEN USA10
                WHEN 11 THEN USA11
                WHEN 12 THEN USA12
                WHEN 13 THEN USA13
                WHEN 14 THEN USA14
                WHEN 15 THEN USA15
                WHEN 16 THEN USA16
                WHEN 17 THEN USA17
                WHEN 18 THEN USA18
                WHEN 19 THEN USA19
                WHEN 20 THEN USA20
            END  TAM_DESCRICAO
          FROM TBGRADE G,
               TBMODELO P
         WHERE G.CODIGO = P.GRADE_CODIGO
           AND P.CODIGO = :MODELO_ID
          INTO :TAM_DESCRICAO;
    END
    ELSE
    IF ( TIPO = 'EUR' ) THEN
    BEGIN

        SELECT FIRST 1
            CASE :TAMANHO
                WHEN 01 THEN EUR01
                WHEN 02 THEN EUR02
                WHEN 03 THEN EUR03
                WHEN 04 THEN EUR04
                WHEN 05 THEN EUR05
                WHEN 06 THEN EUR06
                WHEN 07 THEN EUR07
                WHEN 08 THEN EUR08
                WHEN 09 THEN EUR09
                WHEN 10 THEN EUR10
                WHEN 11 THEN EUR11
                WHEN 12 THEN EUR12
                WHEN 13 THEN EUR13
                WHEN 14 THEN EUR14
                WHEN 15 THEN EUR15
                WHEN 16 THEN EUR16
                WHEN 17 THEN EUR17
                WHEN 18 THEN EUR18
                WHEN 19 THEN EUR19
                WHEN 20 THEN EUR20
            END  TAM_DESCRICAO
          FROM TBGRADE G,
               TBMODELO P
         WHERE G.CODIGO = P.GRADE_CODIGO
           AND P.CODIGO = :MODELO_ID
          INTO :TAM_DESCRICAO;
    END
    ELSE
    IF ( TIPO = 'ETQ' ) THEN
    BEGIN

        SELECT FIRST 1
            CASE :TAMANHO
                WHEN 01 THEN ETQ01
                WHEN 02 THEN ETQ02
                WHEN 03 THEN ETQ03
                WHEN 04 THEN ETQ04
                WHEN 05 THEN ETQ05
                WHEN 06 THEN ETQ06
                WHEN 07 THEN ETQ07
                WHEN 08 THEN ETQ08
                WHEN 09 THEN ETQ09
                WHEN 10 THEN ETQ10
                WHEN 11 THEN ETQ11
                WHEN 12 THEN ETQ12
                WHEN 13 THEN ETQ13
                WHEN 14 THEN ETQ14
                WHEN 15 THEN ETQ15
                WHEN 16 THEN ETQ16
                WHEN 17 THEN ETQ17
                WHEN 18 THEN ETQ18
                WHEN 19 THEN ETQ19
                WHEN 20 THEN ETQ20
            END  TAM_DESCRICAO
          FROM TBGRADE G,
               TBMODELO P
         WHERE G.CODIGO = P.GRADE_CODIGO
           AND P.CODIGO = :MODELO_ID
          INTO :TAM_DESCRICAO;
    END
    ELSE
    BEGIN
        SELECT FIRST 1
            CASE :TAMANHO
                WHEN 01 THEN G.T01
                WHEN 02 THEN G.T02
                WHEN 03 THEN G.T03
                WHEN 04 THEN G.T04
                WHEN 05 THEN G.T05
                WHEN 06 THEN G.T06
                WHEN 07 THEN G.T07
                WHEN 08 THEN G.T08
                WHEN 09 THEN G.T09
                WHEN 10 THEN G.T10
                WHEN 11 THEN G.T11
                WHEN 12 THEN G.T12
                WHEN 13 THEN G.T13
                WHEN 14 THEN G.T14
                WHEN 15 THEN G.T15
                WHEN 16 THEN G.T16
                WHEN 17 THEN G.T17
                WHEN 18 THEN G.T18
                WHEN 19 THEN G.T19
                WHEN 20 THEN G.T20
            END  TAM_DESCRICAO
          FROM TBGRADE G,
               TBMODELO P
         WHERE G.CODIGO = P.GRADE_CODIGO
           AND P.CODIGO = :MODELO_ID
          INTO :TAM_DESCRICAO;
    END

    RETURN Tam_Descricao;

END
^

SET TERM ; ^

/************************************************************************/
/************************************************************************/

SET TERM ^ ;

CREATE OR ALTER function FN_TAMANHO_PRODUTO (
    PRODUTO_ID integer,
    TAMANHO integer,
    TIPO varchar(10) = 'BR')
returns varchar(50)
AS
DECLARE VARIABLE TAM_DESCRICAO VARCHAR(50);
BEGIN

    IF ( TIPO = 'USA' ) THEN
    BEGIN

        SELECT FIRST 1
            CASE :TAMANHO
                WHEN 01 THEN USA01
                WHEN 02 THEN USA02
                WHEN 03 THEN USA03
                WHEN 04 THEN USA04
                WHEN 05 THEN USA05
                WHEN 06 THEN USA06
                WHEN 07 THEN USA07
                WHEN 08 THEN USA08
                WHEN 09 THEN USA09
                WHEN 10 THEN USA10
                WHEN 11 THEN USA11
                WHEN 12 THEN USA12
                WHEN 13 THEN USA13
                WHEN 14 THEN USA14
                WHEN 15 THEN USA15
                WHEN 16 THEN USA16
                WHEN 17 THEN USA17
                WHEN 18 THEN USA18
                WHEN 19 THEN USA19
                WHEN 20 THEN USA20
            END  TAM_DESCRICAO
          FROM TBGRADE G,
               TBPRODUTO P
         WHERE G.CODIGO = P.GRADE_CODIGO
           AND P.CODIGO = :PRODUTO_ID
          INTO :TAM_DESCRICAO;
    END
    ELSE
    IF ( TIPO = 'EUR' ) THEN
    BEGIN

        SELECT FIRST 1
            CASE :TAMANHO
                WHEN 01 THEN EUR01
                WHEN 02 THEN EUR02
                WHEN 03 THEN EUR03
                WHEN 04 THEN EUR04
                WHEN 05 THEN EUR05
                WHEN 06 THEN EUR06
                WHEN 07 THEN EUR07
                WHEN 08 THEN EUR08
                WHEN 09 THEN EUR09
                WHEN 10 THEN EUR10
                WHEN 11 THEN EUR11
                WHEN 12 THEN EUR12
                WHEN 13 THEN EUR13
                WHEN 14 THEN EUR14
                WHEN 15 THEN EUR15
                WHEN 16 THEN EUR16
                WHEN 17 THEN EUR17
                WHEN 18 THEN EUR18
                WHEN 19 THEN EUR19
                WHEN 20 THEN EUR20
            END  TAM_DESCRICAO
          FROM TBGRADE G,
               TBPRODUTO P
         WHERE G.CODIGO = P.GRADE_CODIGO
           AND P.CODIGO = :PRODUTO_ID
          INTO :TAM_DESCRICAO;
    END
    ELSE
    IF ( TIPO = 'ETQ' ) THEN
    BEGIN

        SELECT FIRST 1
            CASE :TAMANHO
                WHEN 01 THEN ETQ01
                WHEN 02 THEN ETQ02
                WHEN 03 THEN ETQ03
                WHEN 04 THEN ETQ04
                WHEN 05 THEN ETQ05
                WHEN 06 THEN ETQ06
                WHEN 07 THEN ETQ07
                WHEN 08 THEN ETQ08
                WHEN 09 THEN ETQ09
                WHEN 10 THEN ETQ10
                WHEN 11 THEN ETQ11
                WHEN 12 THEN ETQ12
                WHEN 13 THEN ETQ13
                WHEN 14 THEN ETQ14
                WHEN 15 THEN ETQ15
                WHEN 16 THEN ETQ16
                WHEN 17 THEN ETQ17
                WHEN 18 THEN ETQ18
                WHEN 19 THEN ETQ19
                WHEN 20 THEN ETQ20
            END  TAM_DESCRICAO
          FROM TBGRADE G,
               TBPRODUTO P
         WHERE G.CODIGO = P.GRADE_CODIGO
           AND P.CODIGO = :PRODUTO_ID
          INTO :TAM_DESCRICAO;
    END
    ELSE
    BEGIN
        SELECT FIRST 1
            CASE :TAMANHO
                WHEN 01 THEN T01
                WHEN 02 THEN T02
                WHEN 03 THEN T03
                WHEN 04 THEN T04
                WHEN 05 THEN T05
                WHEN 06 THEN T06
                WHEN 07 THEN T07
                WHEN 08 THEN T08
                WHEN 09 THEN T09
                WHEN 10 THEN T10
                WHEN 11 THEN T11
                WHEN 12 THEN T12
                WHEN 13 THEN T13
                WHEN 14 THEN T14
                WHEN 15 THEN T15
                WHEN 16 THEN T16
                WHEN 17 THEN T17
                WHEN 18 THEN T18
                WHEN 19 THEN T19
                WHEN 20 THEN T20
            END  TAM_DESCRICAO
          FROM TBGRADE G,
               TBPRODUTO P
         WHERE G.CODIGO = P.GRADE_CODIGO
           AND P.CODIGO = :PRODUTO_ID
          INTO :TAM_DESCRICAO;
    END

    RETURN Tam_Descricao;

END
^

SET TERM ; ^

