CREATE OR ALTER PROCEDURE SPC_REMESSA_MODELO_GRADE (
    REMESSA VARCHAR(20))
RETURNS (
    REMESSA_ID INTEGER,
    MODELO_ID INTEGER,
    MODELO_DESCRICAO VARCHAR(100),
    COR_ID INTEGER,
    COR_DESCRICAO VARCHAR(50),
    T01 NUMERIC(15,0),
    T02 NUMERIC(15,0),
    T03 NUMERIC(15,0),
    T04 NUMERIC(15,0),
    T05 NUMERIC(15,0),
    T06 NUMERIC(15,0),
    T07 NUMERIC(15,0),
    T08 NUMERIC(15,0),
    T09 NUMERIC(15,0),
    T10 NUMERIC(15,0),
    T11 NUMERIC(15,0),
    T12 NUMERIC(15,0),
    T13 NUMERIC(15,0),
    T14 NUMERIC(15,0),
    T15 NUMERIC(15,0),
    T16 NUMERIC(15,0),
    T17 NUMERIC(15,0),
    T18 NUMERIC(15,0),
    T19 NUMERIC(15,0),
    T20 NUMERIC(15,0),
    G01 VARCHAR(10),
    G02 VARCHAR(10),
    G03 VARCHAR(10),
    G04 VARCHAR(10),
    G05 VARCHAR(10),
    G06 VARCHAR(10),
    G07 VARCHAR(10),
    G08 VARCHAR(10),
    G09 VARCHAR(10),
    G10 VARCHAR(10),
    G11 VARCHAR(10),
    G12 VARCHAR(10),
    G13 VARCHAR(10),
    G14 VARCHAR(10),
    G15 VARCHAR(10),
    G16 VARCHAR(10),
    G17 VARCHAR(10),
    G18 VARCHAR(10),
    G19 VARCHAR(10),
    G20 VARCHAR(10),
    TMIN INTEGER,
    TMAX INTEGER,
    TOT NUMERIC(15,0),
    GTOT VARCHAR(10),
    TAM_MAX_MIN INTEGER)
AS
DECLARE VARIABLE PROX_REM_MOD VARCHAR(100);
DECLARE VARIABLE Q01          NUMERIC(15,0);
DECLARE VARIABLE Q02          NUMERIC(15,0);
DECLARE VARIABLE Q03          NUMERIC(15,0);
DECLARE VARIABLE Q04          NUMERIC(15,0);
DECLARE VARIABLE Q05          NUMERIC(15,0);
DECLARE VARIABLE Q06          NUMERIC(15,0);
DECLARE VARIABLE Q07          NUMERIC(15,0);
DECLARE VARIABLE Q08          NUMERIC(15,0);
DECLARE VARIABLE Q09          NUMERIC(15,0);
DECLARE VARIABLE Q10          NUMERIC(15,0);
DECLARE VARIABLE Q11          NUMERIC(15,0);
DECLARE VARIABLE Q12          NUMERIC(15,0);
DECLARE VARIABLE Q13          NUMERIC(15,0);
DECLARE VARIABLE Q14          NUMERIC(15,0);
DECLARE VARIABLE Q15          NUMERIC(15,0);
DECLARE VARIABLE Q16          NUMERIC(15,0);
DECLARE VARIABLE Q17          NUMERIC(15,0);
DECLARE VARIABLE Q18          NUMERIC(15,0);
DECLARE VARIABLE Q19          NUMERIC(15,0);
DECLARE VARIABLE Q20          NUMERIC(15,0);
DECLARE VARIABLE QTOT         NUMERIC(15,0);
DECLARE VARIABLE GQTOT        NUMERIC(15,0); /* TOTAL GERAL */
begin

    q01 = 0; q02 = 0; q03 = 0; q04 = 0;
    q05 = 0; q06 = 0; q07 = 0; q08 = 0;
    q09 = 0; q10 = 0; q11 = 0; q12 = 0;
    q13 = 0; q14 = 0; q15 = 0; q16 = 0;
    q17 = 0; q18 = 0; q19 = 0; q20 = 0;
    qTot= 0;
    gqTot= 0;

    FOR
        SELECT REMESSA,
               MODELO_ID, MODELO_DESCRICAO,
               COR_ID, COR_DESCRICAO,
               T01,T02,T03,T04,T05,T06,T07,T08,T09,T10,
               T11,T12,T13,T14,T15,T16,T17,T18,T19,T20,
               G01,G02,G03,G04,G05,G06,G07,G08,G09,G10,
               G11,G12,G13,G14,G15,G16,G17,G18,G19,G20,
               PROX_REM_MOD,
               TMIN,
               TMAX,
               MAX(TAM_MAX_MIN) OVER ()
          FROM (
                SELECT REMESSA,
                       MODELO_ID, MODELO_DESCRICAO,
                       COR_ID, COR_DESCRICAO,
                       T01,T02,T03,T04,T05,T06,T07,T08,T09,T10,
                       T11,T12,T13,T14,T15,T16,T17,T18,T19,T20,
                       G01,G02,G03,G04,G05,G06,G07,G08,G09,G10,
                       G11,G12,G13,G14,G15,G16,G17,G18,G19,G20,
                       PROX_REM_MOD,
                       MIN(TAM_MIN) OVER (PARTITION BY REMESSA,MODELO_ID) TMIN,
                       MAX(TAM_MAX) OVER (PARTITION BY REMESSA,MODELO_ID) TMAX,
                       TAM_MAX - TAM_MIN + 2 as TAM_MAX_MIN
                  FROM (
                        SELECT REMESSA,
                               MODELO_ID,MODELO_DESCRICAO,
                               COR_ID,COR_DESCRICAO,
                               T01,T02,T03,T04,T05,T06,T07,T08,T09,T10,
                               T11,T12,T13,T14,T15,T16,T17,T18,T19,T20,
                               G01,G02,G03,G04,G05,G06,G07,G08,G09,G10,
                               G11,G12,G13,G14,G15,G16,G17,G18,G19,G20,
                               LEAD (REM_MOD) OVER (ORDER BY REMESSA, MODELO_ID, COR_DESCRICAO) PROX_REM_MOD,
                               IIF(T01>0,1,  IIF(T02>0,2,    IIF(T03>0,3,  IIF(T04>0,4,
                               IIF(T05>0,5,  IIF(T06>0,6,    IIF(T07>0,7,  IIF(T08>0,8,
                               IIF(T09>0,9,  IIF(T10>0,10,   IIF(T11>0,11, IIF(T12>0,12,
                               IIF(T13>0,13, IIF(T14>0,14,   IIF(T15>0,15, IIF(T16>0,16,
                               IIF(T17>0,17, IIF(T18>0,18,   IIF(T19>0,19, 20))))))))))))))))))) TAM_MIN,
                               IIF(T20>0,20, IIF(T19>0,19,   IIF(T18>0,18, IIF(T17>0,17,
                               IIF(T16>0,16, IIF(T15>0,15,   IIF(T14>0,14, IIF(T13>0,13,
                               IIF(T12>0,12, IIF(T11>0,11,   IIF(T10>0,10, IIF(T09>0,9,
                               IIF(T08>0,8,  IIF(T07>0,7,    IIF(T06>0,6,  IIF(T05>0,5,
                               IIF(T04>0,4,  IIF(T03>0,3,    IIF(T02>0,2,  1))))))))))))))))))) TAM_MAX 
                          FROM (
                                SELECT X.REMESSA,
                                       X.MODELO_ID,
                                       X.MODELO_DESCRICAO,
                                       X.COR_ID,
                                       X.COR_DESCRICAO,
                                       X.REMESSA||'-'||X.MODELO_ID AS REM_MOD,
                                       SUM(T01) T01,SUM(T02) T02,SUM(T03) T03,SUM(T04) T04,
                                       SUM(T05) T05,SUM(T06) T06,SUM(T07) T07,SUM(T08) T08,
                                       SUM(T09) T09,SUM(T10) T10,SUM(T11) T11,SUM(T12) T12,
                                       SUM(T13) T13,SUM(T14) T14,SUM(T15) T15,SUM(T16) T16,
                                       SUM(T17) T17,SUM(T18) T18,SUM(T19) T19,SUM(T20) T20,
                                       G01,G02,G03,G04,G05,G06,G07,G08,G09,G10,
                                       G11,G12,G13,G14,G15,G16,G17,G18,G19,G20
                                 FROM (
                                            SELECT L.REMESSA,
                                                   M.CODIGO AS MODELO_ID,
                                                   M.DESCRICAO AS MODELO_DESCRICAO,
                                                   C.CODIGO AS COR_ID,
                                                   C.DESCRICAO AS COR_DESCRICAO,

                                                   (L.T01 * IIF(A.ORIGEM = 22599, L.QUANTIDADE, 1)) AS T01,
                                                   (L.T02 * IIF(A.ORIGEM = 22599, L.QUANTIDADE, 1)) AS T02,
                                                   (L.T03 * IIF(A.ORIGEM = 22599, L.QUANTIDADE, 1)) AS T03,
                                                   (L.T04 * IIF(A.ORIGEM = 22599, L.QUANTIDADE, 1)) AS T04,
                                                   (L.T05 * IIF(A.ORIGEM = 22599, L.QUANTIDADE, 1)) AS T05,
                                                   (L.T06 * IIF(A.ORIGEM = 22599, L.QUANTIDADE, 1)) AS T06,
                                                   (L.T07 * IIF(A.ORIGEM = 22599, L.QUANTIDADE, 1)) AS T07,
                                                   (L.T08 * IIF(A.ORIGEM = 22599, L.QUANTIDADE, 1)) AS T08,
                                                   (L.T09 * IIF(A.ORIGEM = 22599, L.QUANTIDADE, 1)) AS T09,
                                                   (L.T10 * IIF(A.ORIGEM = 22599, L.QUANTIDADE, 1)) AS T10,
                                                   (L.T11 * IIF(A.ORIGEM = 22599, L.QUANTIDADE, 1)) AS T11,
                                                   (L.T12 * IIF(A.ORIGEM = 22599, L.QUANTIDADE, 1)) AS T12,
                                                   (L.T13 * IIF(A.ORIGEM = 22599, L.QUANTIDADE, 1)) AS T13,
                                                   (L.T14 * IIF(A.ORIGEM = 22599, L.QUANTIDADE, 1)) AS T14,
                                                   (L.T15 * IIF(A.ORIGEM = 22599, L.QUANTIDADE, 1)) AS T15,
                                                   (L.T16 * IIF(A.ORIGEM = 22599, L.QUANTIDADE, 1)) AS T16,
                                                   (L.T17 * IIF(A.ORIGEM = 22599, L.QUANTIDADE, 1)) AS T17,
                                                   (L.T18 * IIF(A.ORIGEM = 22599, L.QUANTIDADE, 1)) AS T18,
                                                   (L.T19 * IIF(A.ORIGEM = 22599, L.QUANTIDADE, 1)) AS T19,
                                                   (L.T20 * IIF(A.ORIGEM = 22599, L.QUANTIDADE, 1)) AS T20,

                                                   G.T01 AS G01, G.T02 AS G02, G.T03 AS G03, G.T04 AS G04,
                                                   G.T05 AS G05, G.T06 AS G06, G.T07 AS G07, G.T08 AS G08,
                                                   G.T09 AS G09, G.T10 AS G10, G.T11 AS G11, G.T12 AS G12,
                                                   G.T13 AS G13, G.T14 AS G14, G.T15 AS G15, G.T16 AS G16,
                                                   G.T17 AS G17, G.T18 AS G18, G.T19 AS G19, G.T20 AS G20
                                              FROM TBREMESSA A
                                             INNER JOIN TBREMESSA_ACUMULADO L ON L.REMESSA = A.NUMERO
                                             INNER JOIN TBPRODUTO P ON P.CODIGO = L.PRODUTO_CODIGO
                                             INNER JOIN TBMODELO M ON M.CODIGO = P.MODELO_CODIGO
                                             INNER JOIN TBCOR C ON C.CODIGO = P.COR_CODIGO
                                              LEFT JOIN TBGRADE G ON G.CODIGO = P.GRADE_CODIGO
                                             WHERE A.REMESSA = :REMESSA
                                         ) X
                                   GROUP BY X.REMESSA,
                                            X.MODELO_ID,
                                            X.MODELO_DESCRICAO,
                                            X.COR_ID,
                                            X.COR_DESCRICAO,
                                            G01,G02,G03,G04,G05,G06,G07,G08,G09,G10,
                                            G11,G12,G13,G14,G15,G16,G17,G18,G19,G20
                           )
                   )
           ORDER BY REMESSA, MODELO_ID, COR_DESCRICAO
        )

    INTO :REMESSA_ID,
         :MODELO_ID,:MODELO_DESCRICAO,:COR_ID,:COR_DESCRICAO,
         :T01,:T02,:T03,:T04,:T05,:T06,:T07,:T08,:T09,:T10,
         :T11,:T12,:T13,:T14,:T15,:T16,:T17,:T18,:T19,:T20,
         :G01,:G02,:G03,:G04,:G05,:G06,:G07,:G08,:G09,:G10,
         :G11,:G12,:G13,:G14,:G15,:G16,:G17,:G18,:G19,:G20,
         :PROX_REM_MOD,:TMIN,:TMAX,:TAM_MAX_MIN
     Do Begin

             gTot = 'Total';
             Tot  = t01+t02+t03+t04+t05+t06+t07+t08+t09+t10+
                    t11+t12+t13+t14+t15+t16+t17+t18+t19+t20;

                   /* "Puxa" para as primeiras posições os valores > 0 */
                   if (TMIN = 2) Then
                       Begin
                       T01 = T02;  T02 = T03; T03 = T04;  T04 = T05;
                       T05 = T06;  T06 = T07; T07 = T08;  T08 = T09;
                       T09 = T10;  T10 = T11; T11 = T12;  T12 = T13;
                       T13 = T14;  T14 = T15; T15 = T16;  T16 = T17;
                       T17 = T18;  T18 = T19; T19 = T20;  T20 = 0;
                       G01 = G02;  G02 = G03; G03 = G04;  G04 = G05;
                       G05 = G06;  G06 = G07; G07 = G08;  G08 = G09;
                       G09 = G10;  G10 = G11; G11 = G12;  G12 = G13;
                       G13 = G14;  G14 = G15; G15 = G16;  G16 = G17;
                       G17 = G18;  G18 = G19; G19 = G20;  G20 = '';
                       End else
                   if (TMIN = 3) Then
                       Begin
                       T01 = T03; T02 = T04; T03 = T05; T04 = T06;
                       T05 = T07; T06 = T08; T07 = T09; T08 = T10;
                       T09 = T11; T10 = T12; T11 = T13; T12 = T14;
                       T13 = T15; T14 = T16; T15 = T17; T16 = T18;
                       T17 = T19; T18 = T20; T19 = 0;   T20 = 0;
                       G01 = G03; G02 = G04; G03 = G05; G04 = G06;
                       G05 = G07; G06 = G08; G07 = G09; G08 = G10;
                       G09 = G11; G10 = G12; G11 = G13; G12 = G14;
                       G13 = G15; G14 = G16; G15 = G17; G16 = G18;
                       G17 = G19; G18 = G20; G19 = '';   G20 = '';
                       End else
                   if (TMIN = 4) Then
                       Begin
                       T01 = T04; T02 = T05; T03 = T06; T04 = T07;
                       T05 = T08; T06 = T09; T07 = T10; T08 = T11;
                       T09 = T12; T10 = T13; T11 = T14; T12 = T15;
                       T13 = T16; T14 = T17; T15 = T18; T16 = T19;
                       T17 = T20; T18 = 0;   T19 = 0;   T20 = 0;
                       G01 = G04; G02 = G05; G03 = G06; G04 = G07;
                       G05 = G08; G06 = G09; G07 = G10; G08 = G11;
                       G09 = G12; G10 = G13; G11 = G14; G12 = G15;
                       G13 = G16; G14 = G17; G15 = G18; G16 = G19;
                       G17 = G20; G18 = '';   G19 = '';   G20 = '';
                       End else
                   if (TMIN = 5) Then
                       Begin
                       T01 = T05; T02 = T06; T03 = T07; T04 = T08;
                       T05 = T09; T06 = T10; T07 = T11; T08 = T12;
                       T09 = T13; T10 = T14; T11 = T15; T12 = T16;
                       T13 = T17; T14 = T18; T15 = T19; T16 = T20;
                       T17 = 0;   T18 = 0;   T19 = 0;   T20 = 0;
                       G01 = G05; G02 = G06; G03 = G07; G04 = G08;
                       G05 = G09; G06 = G10; G07 = G11; G08 = G12;
                       G09 = G13; G10 = G14; G11 = G15; G12 = G16;
                       G13 = G17; G14 = G18; G15 = G19; G16 = G20;
                       G17 = '';   G18 = '';   G19 = '';   G20 = '';
                       End else
                   if (TMIN = 6) Then
                       Begin
                       T01 = T06; T02 = T07; T03 = T08; T04 = T09;
                       T05 = T10; T06 = T11; T07 = T12; T08 = T13;
                       T09 = T14; T10 = T15; T11 = T16; T12 = T17;
                       T13 = T18; T14 = T19; T15 = T20; T16 = 0;
                       T17 = 0;   T18 = 0;   T19 = 0;   T20 = 0;
                       G01 = G06; G02 = G07; G03 = G08; G04 = G09;
                       G05 = G10; G06 = G11; G07 = G12; G08 = G13;
                       G09 = G14; G10 = G15; G11 = G16; G12 = G17;
                       G13 = G18; G14 = G19; G15 = G20; G16 = '';
                       G17 = '';   G18 = '';   G19 = '';   G20 = '';
                       End else
                   if (TMIN = 7) Then
                       Begin
                       T01 = T07; T02 = T08; T03 = T09; T04 = T10;
                       T05 = T11; T06 = T12; T07 = T13; T08 = T14;
                       T09 = T15; T10 = T16; T11 = T17; T12 = T18;
                       T13 = T19; T14 = T20; T15 = 0;   T16 = 0;
                       T17 = 0;   T18 = 0;   T19 = 0;   T20 = 0;
                       G01 = G07; G02 = G08; G03 = G09; G04 = G10;
                       G05 = G11; G06 = G12; G07 = G13; G08 = G14;
                       G09 = G15; G10 = G16; G11 = G17; G12 = G18;
                       G13 = G19; G14 = G20; G15 = '';   G16 = '';
                       G17 = '';   G18 = '';   G19 = '';   G20 = '';
                       End else
                   if (TMIN = 8) Then
                       Begin
                       T01 = T08; T02 = T09; T03 = T10; T04 = T11;
                       T05 = T12; T06 = T13; T07 = T14; T08 = T15;
                       T09 = T16; T10 = T17; T11 = T18; T12 = T19;
                       T13 = T20; T14 = 0;   T15 = 0;   T16 = 0;
                       T17 = 0;   T18 = 0;   T19 = 0;   T20 = 0;
                       G01 = G08; G02 = G09; G03 = G10; G04 = G11;
                       G05 = G12; G06 = G13; G07 = G14; G08 = G15;
                       G09 = G16; G10 = G17; G11 = G18; G12 = G19;
                       G13 = G20; G14 = '';   G15 = '';   G16 = '';
                       G17 = '';   G18 = '';   G19 = '';   G20 = '';
                       End else
                   if (TMIN = 9) Then
                       Begin
                       T01 = T09; T02 = T10; T03 = T11; T04 = T12;
                       T05 = T13; T06 = T14; T07 = T15; T08 = T16;
                       T09 = T17; T10 = T18; T11 = T19; T12 = T20;
                       T13 = 0;   T14 = 0;   T15 = 0;   T16 = 0;
                       T17 = 0;   T18 = 0;   T19 = 0;   T20 = 0;
                       G01 = G09; G02 = G10; G03 = G11; G04 = G12;
                       G05 = G13; G06 = G14; G07 = G15; G08 = G16;
                       G09 = G17; G10 = G18; G11 = G19; G12 = G20;
                       G13 = '';   G14 = '';   G15 = '';   G16 = '';
                       G17 = '';   G18 = '';   G19 = '';   G20 = '';
                       End else
                   if (TMIN =10) Then
                       Begin
                       T01 = T10; T02 = T11; T03 = T12; T04 = T13;
                       T05 = T14; T06 = T15; T07 = T16; T08 = T17;
                       T09 = T18; T10 = T19; T11 = T20; T12 = 0;
                       T13 = 0;   T14 = 0;   T15 = 0;   T16 = 0;
                       T17 = 0;   T18 = 0;   T19 = 0;   T20 = 0;
                       G01 = G10; G02 = G11; G03 = G12; G04 = G13;
                       G05 = G14; G06 = G15; G07 = G16; G08 = G17;
                       G09 = G18; G10 = G19; G11 = G20; G12 = '';
                       G13 = '';   G14 = '';   G15 = '';   G16 = '';
                       G17 = '';   G18 = '';   G19 = '';   G20 = '';
                       End else
                   if (TMIN =11) Then
                       Begin
                       T01 = T11; T02 = T12; T03 = T13; T04 = T14;
                       T05 = T15; T06 = T16; T07 = T17; T08 = T18;
                       T09 = T19; T10 = T20; T11 = 0;   T12 = 0;
                       T13 = 0;   T14 = 0;   T15 = 0;   T16 = 0;
                       T17 = 0;   T18 = 0;   T19 = 0;   T20 = 0;
                       G01 = G11; G02 = G12; G03 = G13; G04 = G14;
                       G05 = G15; G06 = G16; G07 = G17; G08 = G18;
                       G09 = G19; G10 = G20; G11 = '';   G12 = '';
                       G13 = '';   G14 = '';   G15 = '';   G16 = '';
                       G17 = '';   G18 = '';   G19 = '';   G20 = '';
                       End else
                   if (TMIN =12) Then
                       Begin
                       T01 = T12; T02 = T13; T03 = T14; T04 = T15;
                       T05 = T16; T06 = T17; T07 = T18; T08 = T19;
                       T09 = T20; T10 = 0;   T11 = 0;   T12 = 0;
                       T13 = 0;   T14 = 0;   T15 = 0;   T16 = 0;
                       T17 = 0;   T18 = 0;   T19 = 0;   T20 = 0;
                       G01 = G12; G02 = G13; G03 = G14; G04 = G15;
                       G05 = G16; G06 = G17; G07 = G18; G08 = G19;
                       G09 = G20; G10 = '';   G11 = '';   G12 = '';
                       G13 = '';   G14 = '';   G15 = '';   G16 = '';
                       G17 = '';   G18 = '';   G19 = '';   G20 = '';
                       End else
                   if (TMIN =13) Then
                       Begin
                       T01 = T13; T02 = T14; T03 = T15; T04 = T16;
                       T05 = T17; T06 = T18; T07 = T19; T08 = T20;
                       T09 = 0;   T10 = 0;   T11 = 0;   T12 = 0;
                       T13 = 0;   T14 = 0;   T15 = 0;   T16 = 0;
                       T17 = 0;   T18 = 0;   T19 = 0;   T20 = 0;
                       G01 = G13; G02 = G14; G03 = G15; G04 = G16;
                       G05 = G17; G06 = G18; G07 = G19; G08 = G20;
                       G09 = '';   G10 = '';   G11 = '';   G12 = '';
                       G13 = '';   G14 = '';   G15 = '';   G16 = '';
                       G17 = '';   G18 = '';   G19 = '';   G20 = '';
                       End else
                   if (TMIN =14) Then
                       Begin
                       T01 = T14; T02 = T15; T03 = T16; T04 = T17;
                       T05 = T18; T06 = T19; T07 = T20; T08 = 0;
                       T09 = 0;   T10 = 0;   T11 = 0;   T12 = 0;
                       T13 = 0;   T14 = 0;   T15 = 0;   T16 = 0;
                       T17 = 0;   T18 = 0;   T19 = 0;   T20 = 0;
                       G01 = G14; G02 = G15; G03 = G16; G04 = G17;
                       G05 = G18; G06 = G19; G07 = G20; G08 = '';
                       G09 = '';   G10 = '';   G11 = '';   G12 = '';
                       G13 = '';   G14 = '';   G15 = '';   G16 = '';
                       G17 = '';   G18 = '';   G19 = '';   G20 = '';
                       End else
                   if (TMIN =15) Then
                       Begin
                       T01 = T15; T02 = T16; T03 = T17; T04 = T18;
                       T05 = T19; T06 = T20; T07 = 0;   T08 = 0;
                       T09 = 0;   T10 = 0;   T11 = 0;   T12 = 0;
                       T13 = 0;   T14 = 0;   T15 = 0;   T16 = 0;
                       T17 = 0;   T18 = 0;   T19 = 0;   T20 = 0;
                       G01 = G15; G02 = G16; G03 = G17; G04 = G18;
                       G05 = G19; G06 = G20; G07 = '';   G08 = '';
                       G09 = '';   G10 = '';   G11 = '';   G12 = '';
                       G13 = '';   G14 = '';   G15 = '';   G16 = '';
                       G17 = '';   G18 = '';   G19 = '';   G20 = '';
                       End else
                   if (TMIN =16) Then
                       Begin
                       T01 = T16; T02 = T17; T03 = T18; T04 = T19;
                       T05 = T20; T06 = 0;   T07 = 0;   T08 = 0;
                       T09 = 0;   T10 = 0;   T11 = 0;   T12 = 0;
                       T13 = 0;   T14 = 0;   T15 = 0;   T16 = 0;
                       T17 = 0;   T18 = 0;   T19 = 0;   T20 = 0;
                       G01 = G16; G02 = G17; G03 = G18; G04 = G19;
                       G05 = G20; G06 = '';   G07 = '';   G08 = '';
                       G09 = '';   G10 = '';   G11 = '';   G12 = '';
                       G13 = '';   G14 = '';   G15 = '';   G16 = '';
                       G17 = '';   G18 = '';   G19 = '';   G20 = '';
                       End else
                   if (TMIN =17) Then
                       Begin
                       T01 = T17; T02 = T18; T03 = T19; T04 = T20;
                       T05 = 0;   T06 = 0;   T07 = 0;   T08 = 0;
                       T09 = 0;   T10 = 0;   T11 = 0;   T12 = 0;
                       T13 = 0;   T14 = 0;   T15 = 0;   T16 = 0;
                       T17 = 0;   T18 = 0;   T19 = 0;   T20 = 0;
                       G01 = G17; G02 = G18; G03 = G19; G04 = G20;
                       G05 = '';   G06 = '';   G07 = '';   G08 = '';
                       G09 = '';   G10 = '';   G11 = '';   G12 = '';
                       G13 = '';   G14 = '';   G15 = '';   G16 = '';
                       G17 = '';   G18 = '';   G19 = '';   G20 = '';
                       End else
                   if (TMIN =18) Then
                       Begin
                       T01 = T18; T02 = T19; T03 = T20; T04 = 0;
                       T05 = 0;   T06 = 0;   T07 = 0;   T08 = 0;
                       T09 = 0;   T10 = 0;   T11 = 0;   T12 = 0;
                       T13 = 0;   T14 = 0;   T15 = 0;   T16 = 0;
                       T17 = 0;   T18 = 0;   T19 = 0;   T20 = 0;
                       G01 = G18; G02 = G19; G03 = G20; G04 = '';
                       G05 = '';   G06 = '';   G07 = '';   G08 = '';
                       G09 = '';   G10 = '';   G11 = '';   G12 = '';
                       G13 = '';   G14 = '';   G15 = '';   G16 = '';
                       G17 = '';   G18 = '';   G19 = '';   G20 = '';
                       End else
                   if (TMIN =19) Then
                       Begin
                       T01 = T19; T02 = T20;  T03 = 0;   T04 = 0;
                       T05 = 0;   T06 = 0;    T07 = 0;   T08 = 0;
                       T09 = 0;   T10 = 0;    T11 = 0;   T12 = 0;
                       T13 = 0;   T14 = 0;    T15 = 0;   T16 = 0;
                       T17 = 0;   T18 = 0;    T19 = 0;   T20 = 0;
                       G01 = G19; G02 = G20;  G03 = '';   G04 = '';
                       G05 = '';   G06 = '';    G07 = '';   G08 = '';
                       G09 = '';   G10 = '';    G11 = '';   G12 = '';
                       G13 = '';   G14 = '';    G15 = '';   G16 = '';
                       G17 = '';   G18 = '';    G19 = '';   G20 = '';
                       End else
                   if (TMIN =20) Then
                       Begin
                       T01 = T20;  T02 = 0;  T03 = 0;    T04 = 0;
                       T05 = 0;    T06 = 0;  T07 = 0;    T08 = 0;
                       T09 = 0;    T10 = 0;  T11 = 0;    T12 = 0;
                       T13 = 0;    T14 = 0;  T15 = 0;    T16 = 0;
                       T17 = 0;    T18 = 0;  T19 = 0;    T20 = 0;
                       G01 = G20;  G02 = '';  G03 = '';    G04 = '';
                       G05 = '';    G06 = '';  G07 = '';    G08 = '';
                       G09 = '';    G10 = '';  G11 = '';    G12 = '';
                       G13 = '';    G14 = '';  G15 = '';    G16 = '';
                       G17 = '';    G18 = '';  G19 = '';    G20 = '';
                       end

                   /* Calcula os Totalizadores */
                   /* Totais do Modelo */
                   q01 = q01 + :t01;
                   q02 = q02 + :t02;
                   q03 = q03 + :t03;
                   q04 = q04 + :t04;
                   q05 = q05 + :t05;
                   q06 = q06 + :t06;
                   q07 = q07 + :t07;
                   q08 = q08 + :t08;
                   q09 = q09 + :t09;
                   q10 = q10 + :t10;
                   q11 = q11 + :t11;
                   q12 = q12 + :t12;
                   q13 = q13 + :t13;
                   q14 = q14 + :t14;
                   q15 = q15 + :t15;
                   q16 = q16 + :t16;
                   q17 = q17 + :t17;
                   q18 = q18 + :t18;
                   q19 = q19 + :t19;
                   q20 = q20 + :t20;
                   qTot= q01+q02+q03+q04+q05+q06+q07+q08+q09+q10+
                         q11+q12+q13+q14+q15+q16+q17+q18+q19+q20;

                   /* Limpa os Tamanhos e Grades das últimas posições sem salores */
                   if (TMAX - TMIN + 1 = 1) Then
                       Begin
                       T02 = null; G02 = '';  
                       T03 = null; G03 = '';  T04 = null; G04 = '';
                       T05 = null; G05 = '';  T06 = null; G06 = '';
                       T07 = null; G07 = '';  T08 = null; G08 = '';
                       T09 = null; G09 = '';  T10 = null; G10 = '';
                       T11 = null; G11 = '';  T12 = null; G12 = '';
                       T13 = null; G13 = '';  T14 = null; G14 = '';
                       T15 = null; G15 = '';  T16 = null; G16 = '';
                       T17 = null; G17 = '';  T18 = null; G18 = '';
                       T19 = null; G19 = '';  T20 = null; G20 = '';
                       end else
                   if (TMAX - TMIN + 1 = 2) Then
                       Begin
                       T03 = null; G03 = '';  T04 = null; G04 = '';
                       T05 = null; G05 = '';  T06 = null; G06 = '';
                       T07 = null; G07 = '';  T08 = null; G08 = '';
                       T09 = null; G09 = '';  T10 = null; G10 = '';
                       T11 = null; G11 = '';  T12 = null; G12 = '';
                       T13 = null; G13 = '';  T14 = null; G14 = '';
                       T15 = null; G15 = '';  T16 = null; G16 = '';
                       T17 = null; G17 = '';  T18 = null; G18 = '';
                       T19 = null; G19 = '';  T20 = null; G20 = '';
                       end else
                   if (TMAX - TMIN + 1 = 3) Then
                       Begin
                       T04 = null; G04 = '';
                       T05 = null; G05 = '';  T06 = null; G06 = '';
                       T07 = null; G07 = '';  T08 = null; G08 = '';
                       T09 = null; G09 = '';  T10 = null; G10 = '';
                       T11 = null; G11 = '';  T12 = null; G12 = '';
                       T13 = null; G13 = '';  T14 = null; G14 = '';
                       T15 = null; G15 = '';  T16 = null; G16 = '';
                       T17 = null; G17 = '';  T18 = null; G18 = '';
                       T19 = null; G19 = '';  T20 = null; G20 = '';
                       end else
                   if (TMAX - TMIN + 1 = 4) Then
                       Begin
                       T05 = null; G05 = '';  T06 = null; G06 = '';
                       T07 = null; G07 = '';  T08 = null; G08 = '';
                       T09 = null; G09 = '';  T10 = null; G10 = '';
                       T11 = null; G11 = '';  T12 = null; G12 = '';
                       T13 = null; G13 = '';  T14 = null; G14 = '';
                       T15 = null; G15 = '';  T16 = null; G16 = '';
                       T17 = null; G17 = '';  T18 = null; G18 = '';
                       T19 = null; G19 = '';  T20 = null; G20 = '';
                       end else
                   if (TMAX - TMIN + 1 = 5) Then
                       Begin
                       T06 = null; G06 = '';
                       T07 = null; G07 = '';  T08 = null; G08 = '';
                       T09 = null; G09 = '';  T10 = null; G10 = '';
                       T11 = null; G11 = '';  T12 = null; G12 = '';
                       T13 = null; G13 = '';  T14 = null; G14 = '';
                       T15 = null; G15 = '';  T16 = null; G16 = '';
                       T17 = null; G17 = '';  T18 = null; G18 = '';
                       T19 = null; G19 = '';  T20 = null; G20 = '';
                       end else
                   if (TMAX - TMIN + 1 = 6) Then
                       Begin
                       T07 = null; G07 = '';  T08 = null; G08 = '';
                       T09 = null; G09 = '';  T10 = null; G10 = '';
                       T11 = null; G11 = '';  T12 = null; G12 = '';
                       T13 = null; G13 = '';  T14 = null; G14 = '';
                       T15 = null; G15 = '';  T16 = null; G16 = '';
                       T17 = null; G17 = '';  T18 = null; G18 = '';
                       T19 = null; G19 = '';  T20 = null; G20 = '';
                       end else
                   if (TMAX - TMIN + 1 = 7) Then
                       Begin
                       T08 = null; G08 = '';
                       T09 = null; G09 = '';  T10 = null; G10 = '';
                       T11 = null; G11 = '';  T12 = null; G12 = '';
                       T13 = null; G13 = '';  T14 = null; G14 = '';
                       T15 = null; G15 = '';  T16 = null; G16 = '';
                       T17 = null; G17 = '';  T18 = null; G18 = '';
                       T19 = null; G19 = '';  T20 = null; G20 = '';
                       end else
                   if (TMAX - TMIN + 1 = 8) Then
                       Begin
                       T09 = null; G09 = '';  T10 = null; G10 = '';
                       T11 = null; G11 = '';  T12 = null; G12 = '';
                       T13 = null; G13 = '';  T14 = null; G14 = '';
                       T15 = null; G15 = '';  T16 = null; G16 = '';
                       T17 = null; G17 = '';  T18 = null; G18 = '';
                       T19 = null; G19 = '';  T20 = null; G20 = '';
                       end else
                   if (TMAX - TMIN + 1 = 9) Then
                       Begin
                       T10 = null; G10 = '';
                       T11 = null; G11 = '';  T12 = null; G12 = '';
                       T13 = null; G13 = '';  T14 = null; G14 = '';
                       T15 = null; G15 = '';  T16 = null; G16 = '';
                       T17 = null; G17 = '';  T18 = null; G18 = '';
                       T19 = null; G19 = '';  T20 = null; G20 = '';
                       end else
                   if (TMAX - TMIN + 1 =10) Then
                       Begin
                       T11 = null; G11 = '';  T12 = null; G12 = '';
                       T13 = null; G13 = '';  T14 = null; G14 = '';
                       T15 = null; G15 = '';  T16 = null; G16 = '';
                       T17 = null; G17 = '';  T18 = null; G18 = '';
                       T19 = null; G19 = '';  T20 = null; G20 = '';
                       end else
                   if (TMAX - TMIN + 1 =11) Then
                       Begin
                       T12 = null; G12 = '';
                       T13 = null; G13 = '';  T14 = null; G14 = '';
                       T15 = null; G15 = '';  T16 = null; G16 = '';
                       T17 = null; G17 = '';  T18 = null; G18 = '';
                       T19 = null; G19 = '';  T20 = null; G20 = '';
                       end else
                   if (TMAX - TMIN + 1 =12) Then
                       Begin
                       T13 = null; G13 = '';  T14 = null; G14 = '';
                       T15 = null; G15 = '';  T16 = null; G16 = '';
                       T17 = null; G17 = '';  T18 = null; G18 = '';
                       T19 = null; G19 = '';  T20 = null; G20 = '';
                       end else
                   if (TMAX - TMIN + 1 =13) Then
                       Begin
                       T14 = null; G14 = '';
                       T15 = null; G15 = '';  T16 = null; G16 = '';
                       T17 = null; G17 = '';  T18 = null; G18 = '';
                       T19 = null; G19 = '';  T20 = null; G20 = '';
                       end else
                   if (TMAX - TMIN + 1 =14) Then
                       Begin
                       T15 = null; G15 = '';  T16 = null; G16 = '';
                       T17 = null; G17 = '';  T18 = null; G18 = '';
                       T19 = null; G19 = '';  T20 = null; G20 = '';
                       end else
                   if (TMAX - TMIN + 1 =15) Then
                       Begin
                       T16 = null; G16 = '';
                       T17 = null; G17 = '';  T18 = null; G18 = '';
                       T19 = null; G19 = '';  T20 = null; G20 = '';
                       end else
                   if (TMAX - TMIN + 1 =16) Then
                       Begin
                       T17 = null; G17 = '';  T18 = null; G18 = '';
                       T19 = null; G19 = '';  T20 = null; G20 = '';
                       end else
                   if (TMAX - TMIN + 1 =17) Then
                       Begin
                       T18 = null; G18 = '';
                       T19 = null; G19 = '';  T20 = null; G20 = '';
                       end else
                   if (TMAX - TMIN + 1 =18) Then
                       Begin
                       T19 = null; G19 = '';  T20 = null; G20 = '';
                       end else
                   if (TMAX - TMIN + 1 =19) Then
                       Begin
                       T20 = null; G20 = '';
                       end

                   IF(TOT > 0) THEN SUSPEND;

                   IF ( (REMESSA||'-'||MODELO_ID <> PROX_REM_MOD) OR (PROX_REM_MOD IS NULL) ) THEN
                       Begin
                         /* preenche o registro do totalizador por modelo */
                         :COR_ID = null;
                         :COR_DESCRICAO = '<b>TOTAL<b>';
                         t01 = q01; t02 = q02; t03 = q03; t04 = q04;
                         t05 = q05; t06 = q06; t07 = q07; t08 = q08;
                         t09 = q09; t10 = q10; t11 = q11; t12 = q12;
                         t13 = q13; t14 = q14; t15 = q15; t16 = q16;
                         t17 = q17; t18 = q18; t19 = q19; t20 = q20;
                         Tot = qTot;

                           /* Limpa os Tamanhos das últimas posições sem salores */
                           if (TMAX - TMIN + 1 = 1) Then
                               Begin
                               T02 = null; T03 = null; T04 = null; T05 = null;
                               T06 = null; T07 = null; T08 = null; T09 = null;
                               T10 = null; T11 = null; T12 = null; T13 = null;
                               T14 = null; T15 = null; T16 = null; T17 = null;
                               T18 = null; T19 = null; T20 = null;
                               end else
                           if (TMAX - TMIN + 1 = 2) Then
                               Begin
                               T03 = null; T04 = null; T05 = null; T06 = null;
                               T07 = null; T08 = null; T09 = null; T10 = null;
                               T11 = null; T12 = null; T13 = null; T14 = null;
                               T15 = null; T16 = null; T17 = null; T18 = null;
                               T19 = null; T20 = null;
                               end else
                           if (TMAX - TMIN + 1 = 3) Then
                               Begin
                               T04 = null;
                               T05 = null; T06 = null; T07 = null; T08 = null;
                               T09 = null; T10 = null; T11 = null; T12 = null;
                               T13 = null; T14 = null; T15 = null; T16 = null;
                               T17 = null; T18 = null; T19 = null; T20 = null;
                               end else
                           if (TMAX - TMIN + 1 = 4) Then
                               Begin
                               T05 = null; T06 = null; T07 = null; T08 = null;
                               T09 = null; T10 = null; T11 = null; T12 = null;
                               T13 = null; T14 = null; T15 = null; T16 = null;
                               T17 = null; T18 = null; T19 = null; T20 = null;
                               end else
                           if (TMAX - TMIN + 1 = 5) Then
                               Begin
                               T06 = null; T07 = null; T08 = null; T09 = null;
                               T10 = null; T11 = null; T12 = null; T13 = null;
                               T14 = null; T15 = null; T16 = null; T17 = null;
                               T18 = null; T19 = null; T20 = null;
                               end else
                           if (TMAX - TMIN + 1 = 6) Then
                               Begin
                               T07 = null; T08 = null; T09 = null; T10 = null;
                               T11 = null; T12 = null; T13 = null; T14 = null;
                               T15 = null; T16 = null; T17 = null; T18 = null;
                               T19 = null; T20 = null;
                               end else
                           if (TMAX - TMIN + 1 = 7) Then
                               Begin
                               T08 = null;
                               T09 = null; T10 = null; T11 = null; T12 = null;
                               T13 = null; T14 = null; T15 = null; T16 = null;
                               T17 = null; T18 = null; T19 = null; T20 = null;
                               end else
                           if (TMAX - TMIN + 1 = 8) Then
                               Begin
                               T09 = null; T10 = null; T11 = null; T12 = null;
                               T13 = null; T14 = null; T15 = null; T16 = null;
                               T17 = null; T18 = null; T19 = null; T20 = null;
                               end else
                           if (TMAX - TMIN + 1 = 9) Then
                               Begin
                               T10 = null; T11 = null; T12 = null; T13 = null;
                               T14 = null; T15 = null; T16 = null; T17 = null;
                               T18 = null; T19 = null; T20 = null;
                               end else
                           if (TMAX - TMIN + 1 =10) Then
                               Begin
                               T11 = null; T12 = null; T13 = null; T14 = null;
                               T15 = null; T16 = null; T17 = null; T18 = null;
                               T19 = null; T20 = null;
                               end else
                           if (TMAX - TMIN + 1 =11) Then
                               Begin
                               T12 = null; T13 = null; T14 = null; T15 = null;
                               T16 = null; T17 = null; T18 = null; T19 = null;
                               T20 = null;
                               end else
                           if (TMAX - TMIN + 1 =12) Then
                               Begin
                               T13 = null; T14 = null; T15 = null; T16 = null;
                               T17 = null; T18 = null; T19 = null; T20 = null;
                               end else
                           if (TMAX - TMIN + 1 =13) Then
                               Begin
                               T14 = null; T15 = null; T16 = null; T17 = null;
                               T18 = null; T19 = null; T20 = null;
                               end else
                           if (TMAX - TMIN + 1 =14) Then
                               Begin
                               T15 = null; T16 = null; T17 = null; T18 = null;
                               T19 = null; T20 = null;
                               end else
                           if (TMAX - TMIN + 1 =15) Then
                               Begin
                               T16 = null; T17 = null; T18 = null; T19 = null;
                               T20 = null;
                               end else
                           if (TMAX - TMIN + 1 =16) Then
                               Begin
                               T17 = null; T18 = null; T19 = null; T20 = null;
                               end else
                           if (TMAX - TMIN + 1 =17) Then
                               Begin
                               T18 = null; T19 = null; T20 = null;
                               end else
                           if (TMAX - TMIN + 1 =18) Then
                               Begin
                               T19 = null; T20 = null;
                               end else
                           if (TMAX - TMIN + 1 =19) Then
                               Begin
                               T20 = null;
                               end

                         IF(TOT > 0) THEN
                            Begin
                            /* Total */
                            gqTot = gqTot + Tot;
                            SUSPEND;
                            End

                         q01 = 0; q02 = 0; q03 = 0; q04 = 0;
                         q05 = 0; q06 = 0; q07 = 0; q08 = 0;
                         q09 = 0; q10 = 0; q11 = 0; q12 = 0;
                         q13 = 0; q14 = 0; q15 = 0; q16 = 0;
                         q17 = 0; q18 = 0; q19 = 0; q20 = 0;
                         qTot= 0;
                       End
                    
                   IF ( (PROX_REM_MOD IS NULL) ) THEN
                       Begin
                           -- preenche o registro do totalizador  
                           :COR_ID = null;
                           :COR_DESCRICAO = '<b>***** TOTAL DA REMESSA: '||:REMESSA||'<b>';
                           Tot = gqTot;

                           -- Limpa os Tamanhos e Grades
                           T01 = null; T02 = null; T03 = null; T04 = null;
                           T05 = null; T06 = null; T07 = null; T08 = null;
                           T09 = null; T10 = null; T11 = null; T12 = null;
                           T13 = null; T14 = null; T15 = null; T16 = null;
                           T17 = null; T18 = null; T19 = null; T20 = null;
                           IF(gqTOT > 0) THEN SUSPEND;
                           gqTot= 0;
                       End
                 

                End
End;

CREATE OR ALTER VIEW VWMODELO_FICHA_CONSUMO(
    TIPO,
    TIPO_DESCRICAO_COMPLETA,
    TIPO_DESCRICAO,
    MODELO_ID,
    MODELO_DESCRICAO,
    GRADE_CODIGO,
    TAMANHO,
    TAMANHO_DESCRICAO,
    COR_ID,
    COR_DESCRICAO,
    FAMILIA_ID,
    UM,
    CONSUMO_NIVEL,
    CONSUMO_FAMILIA_ID,
    CONSUMO_FAMILIA_DESCRICAO,
    CONSUMO_PRODUTO_ID,
    CONSUMO_PRODUTO_DESCRICAO,
    CONSUMO_PRODUTO_STATUS,
    CONSUMO_COR_ID,
    CONSUMO_MODELO_ID,
    CONSUMO_TAMANHO,
    CONSUMO_TAMANHO_DESCRICAO,
    CONSUMO_QUANTIDADE,
    CONSUMO_FATOR_CALCULO,
    CONSUMO_FATOR_CALCULO_TIPO,
    CONSUMO_FATOR_DIVISOR,
    CONSUMO_FATOR_MULTIPLICADOR,
    CONSUMO_UM,
    CONSUMO_UM_ALTERNATIVA,
    CONSUMO_MATRIZ_ID,
    CONSUMO_DENSIDADE,
    CONSUMO_ESPESSURA,
    CONSUMO_LOCALIZACAO_ID,
    HABILITA_GERAR_CONSUMO,
    COR_PADRAO,
    TAM_PADRAO,
    PERFIL_MOD,
    PERFIL_DESCRICAO,
    LINHA_ID,
    LINHA_DESCRICAO,
    SETOR_ID,
    PECA_CONJUNTO)
AS
SELECT
    TIPO,
    TIPO_DESCRICAO_COMPLETA,
    TIPO_DESCRICAO,
    MODELO_ID,
    MODELO_DESCRICAO,
    GRADE_CODIGO,
    TAMANHO,
    TAMANHO_DESCRICAO,
    COR_ID,
    COR_DESCRICAO,
    FAMILIA_ID,
    UM,
    CONSUMO_NIVEL,
    CONSUMO_FAMILIA_ID,
    CONSUMO_FAMILIA_DESCRICAO,
    CONSUMO_PRODUTO_ID,
    CONSUMO_PRODUTO_DESCRICAO,
    STATUS,
    CONSUMO_MODELO_ID,
    CONSUMO_COR_ID,
    CONSUMO_TAMANHO,
    CONSUMO_TAMANHO_DESCRICAO,
    CONSUMO_QUANTIDADE,
    CONSUMO_FATOR_CALCULO,
    CONSUMO_FATOR_CALCULO_TIPO,
    CONSUMO_FATOR_DIVISOR,
    CONSUMO_FATOR_MULTIPLICADOR,
    CONSUMO_UM,
    CONSUMO_UM_ALTERNATIVA,
    MATRIZ_ID,
    DENSIDADE,
    ESPESSURA,
    CONSUMO_LOCALIZACAO_ID,
    HABILITA_GERAR_CONSUMO,
    IIF(COR_ID  = 0 or COR_ID = COR_PADRAO_ID,1,0) COR_PADRAO,
    IIF(TAMANHO = 0 or TAMANHO = TAMANHO_PADRAO_ID,1,0) TAM_PADRAO,
    PERFIL_MOD,
    PERFIL_DESCRICAO,
    LINHA_ID,
    LINHA_DESCRICAO,
    SETOR_ID,
    PECA_CONJUNTO
    FROM (
            SELECT
            TIPO,
            TIPO_DESCRICAO_COMPLETA,
            TIPO_DESCRICAO,
            MODELO_ID,
            MODELO_DESCRICAO,
            GRADE_CODIGO,
            TAMANHO,
            TAMANHO_DESCRICAO,
            COR_ID,
            COR_DESCRICAO,
            FAMILIA_ID,
            UM,
            CONSUMO_NIVEL,
            CONSUMO_FAMILIA_ID,
            CONSUMO_FAMILIA_DESCRICAO,
            CONSUMO_PRODUTO_ID,
            CONSUMO_PRODUTO_DESCRICAO,
            STATUS,
            CONSUMO_MODELO_ID,
            CONSUMO_COR_ID,
            CONSUMO_TAMANHO,
            CONSUMO_TAMANHO_DESCRICAO,
            CONSUMO_QUANTIDADE,
            CONSUMO_FATOR_CALCULO,
            CONSUMO_FATOR_CALCULO_TIPO,
            CONSUMO_FATOR_DIVISOR,
            CONSUMO_FATOR_MULTIPLICADOR,
            CONSUMO_UM,
            CONSUMO_UM_ALTERNATIVA,
            MATRIZ_ID,
            DENSIDADE,
            ESPESSURA,
            CONSUMO_LOCALIZACAO_ID,
            HABILITA_GERAR_CONSUMO,
            COALESCE((SELECT FIRST 1 MP.COR_ID  FROM TBMODELO_PADRAO MP WHERE MP.MODELO_ID = Z.MODELO_ID),0) COR_PADRAO_ID,
            COALESCE((SELECT FIRST 1 MP.TAMANHO FROM TBMODELO_PADRAO MP WHERE MP.MODELO_ID = Z.MODELO_ID),0) TAMANHO_PADRAO_ID,
            PERFIL_MOD,
            (SELECT FIRST 1 P.DESCRICAO FROM TBPERFIL P WHERE P.TABELA = 'MOD' AND P.ID = Z.PERFIL_MOD) PERFIL_DESCRICAO,
            LINHA_ID,
            (SELECT FIRST 1 L.DESCRICAO FROM TBMODELO_LINHA L WHERE L.CODIGO = Z.LINHA_ID) LINHA_DESCRICAO,
            SETOR_ID,
            PECA_CONJUNTO
            FROM (

            SELECT *
            FROM (
                SELECT                                                                      
                    '1' TIPO,
                    TRIM('FICHA TÉCNICA DE CONSUMO DO MODELO POR COR') TIPO_DESCRICAO_COMPLETA,
                    TRIM('FICHA POR COR') TIPO_DESCRICAO,
                    CC.MODELO_ID,
                    M.DESCRICAO MODELO_DESCRICAO,       
                    M.GRADE_CODIGO,
                    S.TAMANHO,
                    FN_TAMANHO_GRADE(M.GRADE_CODIGO,S.TAMANHO) TAMANHO_DESCRICAO,
                    CC.COR_ID,
                    C.DESCRICAO COR_DESCRICAO,      
                    M.FAMILIA_INVENTARIO FAMILIA_ID,
                    M.UNIDADEMEDIDA_SIGLA UM,
                    CC.NIVEL CONSUMO_NIVEL,
                    F.CODIGO CONSUMO_FAMILIA_ID,
                    F.DESCRICAO CONSUMO_FAMILIA_DESCRICAO,
                    CC.PRODUTO_ID CONSUMO_PRODUTO_ID,
                    P.DESCRICAO CONSUMO_PRODUTO_DESCRICAO,
                    P.STATUS,
                    P.MODELO_CODIGO CONSUMO_MODELO_ID,
                    P.COR_CODIGO CONSUMO_COR_ID,
                    CC.TAM CONSUMO_TAMANHO,
                    FN_TAMANHO_GRADE(P.GRADE_CODIGO,CC.TAM) CONSUMO_TAMANHO_DESCRICAO,
            
                    IIF(COALESCE(M.GRADE_CODIGO,0) = 0 AND S.TAMANHO = 0,CC.QUANTIDADE,
                     CASE S.TAMANHO
                     WHEN 01 THEN CC.T01
                     WHEN 02 THEN CC.T02
                     WHEN 03 THEN CC.T03
                     WHEN 04 THEN CC.T04
                     WHEN 05 THEN CC.T05
                     WHEN 06 THEN CC.T06
                     WHEN 07 THEN CC.T07
                     WHEN 08 THEN CC.T08
                     WHEN 09 THEN CC.T09
                     WHEN 10 THEN CC.T10
                     WHEN 11 THEN CC.T11
                     WHEN 12 THEN CC.T12
                     WHEN 13 THEN CC.T13
                     WHEN 14 THEN CC.T14
                     WHEN 15 THEN CC.T15
                     WHEN 16 THEN CC.T16
                     WHEN 17 THEN CC.T17
                     WHEN 18 THEN CC.T18
                     WHEN 19 THEN CC.T19
                     WHEN 20 THEN CC.T20
                     END) CONSUMO_QUANTIDADE,
                    NULL CONSUMO_FATOR_CALCULO,
                    NULL CONSUMO_FATOR_CALCULO_TIPO,
                    CC.FATOR_CONVERSAO CONSUMO_FATOR_DIVISOR,
                    CC.FATOR_MATRIZ CONSUMO_FATOR_MULTIPLICADOR,
                    P.UNIDADEMEDIDA_SIGLA CONSUMO_UM,
                    F.UNIDADEMEDIDA_ALTERNATIVO CONSUMO_UM_ALTERNATIVA,
                    CC.MATRIZ_ID,
                    (SELECT FIRST 1 Z.DENSIDADE FROM TBMODELO Z WHERE Z.CODIGO = P.MODELO_CODIGO) DENSIDADE,
                    (SELECT FIRST 1 Z.ESPESSURA FROM TBMODELO Z WHERE Z.CODIGO = P.MODELO_CODIGO) ESPESSURA,
                    P.LOCALIZACAO_CODIGO CONSUMO_LOCALIZACAO_ID,
                    CC.HABILITA_GERAR_CONSUMO,
                    M.CLASSIFICACAO AS PERFIL_MOD,
                    M.LINHA_CODIGO AS LINHA_ID,
                    CC.SETOR_ID,
                    CC.PECA_CONJUNTO

                FROM
                    TBMODELO_CONSUMO_COR CC,
                    TBFAMILIA F,
                    TBMODELO M,
                    TBCOR C,
                    TBPRODUTO P,
                    TBGRADE_SEQ S
                
                WHERE TRUE
                AND M.CODIGO = CC.MODELO_ID
                AND C.CODIGO = CC.COR_ID
                AND P.CODIGO = CC.PRODUTO_ID
                AND F.CODIGO = P.FAMILIA_INVENTARIO
            
            ) X
            WHERE CONSUMO_QUANTIDADE > 0
            
            UNION
            
            SELECT *
            FROM (
                SELECT                                                             
                    '2' TIPO,
                    TRIM('FICHA TÉCNICA DE CONSUMO DO MODELO') TIPO_DESCRICAO_COMPLETA,
                    TRIM('FICHA GERAL') TIPO_DESCRICAO,
                    CC.MODELO_CODIGO,
                    M.DESCRICAO MODELO_DESCRICAO,       
                    M.GRADE_CODIGO,
                    S.TAMANHO,
                    FN_TAMANHO_GRADE(M.GRADE_CODIGO,S.TAMANHO) TAMANHO_DESCRICAO,
                    0 COR_ID,
                    ' TODAS AS CORES' COR_DESCRICAO,
                    M.FAMILIA_INVENTARIO FAMILIA_ID,
                    M.UNIDADEMEDIDA_SIGLA UM,
                    CC.NIVEL CONSUMO_NIVEL,
                    F.CODIGO CONSUMO_FAMILIA_ID,
                    F.DESCRICAO CONSUMO_FAMILIA_DESCRICAO,
                    CC.CONSUMO_PRODUTO_CODIGO CONSUMO_PRODUTO_ID,
                    P.DESCRICAO CONSUMO_PRODUTO_DESCRICAO,   
                    P.STATUS, 
                    P.MODELO_CODIGO CONSUMO_MODELO_ID,
                    P.COR_CODIGO CONSUMO_COR_ID,
                    CC.TAM CONSUMO_TAMANHO,
                    FN_TAMANHO_GRADE(P.GRADE_CODIGO,CC.TAM) CONSUMO_TAMANHO_DESCRICAO,
            
                    IIF(COALESCE(M.GRADE_CODIGO,0) = 0 AND S.TAMANHO = 0,CC.CONSUMO_QUANTIDADE,
                     CASE S.TAMANHO
                     WHEN 01 THEN CC.CONSUMO_QUANTIDADE_T01
                     WHEN 02 THEN CC.CONSUMO_QUANTIDADE_T02
                     WHEN 03 THEN CC.CONSUMO_QUANTIDADE_T03
                     WHEN 04 THEN CC.CONSUMO_QUANTIDADE_T04
                     WHEN 05 THEN CC.CONSUMO_QUANTIDADE_T05
                     WHEN 06 THEN CC.CONSUMO_QUANTIDADE_T06
                     WHEN 07 THEN CC.CONSUMO_QUANTIDADE_T07
                     WHEN 08 THEN CC.CONSUMO_QUANTIDADE_T08
                     WHEN 09 THEN CC.CONSUMO_QUANTIDADE_T09
                     WHEN 10 THEN CC.CONSUMO_QUANTIDADE_T10
                     WHEN 11 THEN CC.CONSUMO_QUANTIDADE_T11
                     WHEN 12 THEN CC.CONSUMO_QUANTIDADE_T12
                     WHEN 13 THEN CC.CONSUMO_QUANTIDADE_T13
                     WHEN 14 THEN CC.CONSUMO_QUANTIDADE_T14
                     WHEN 15 THEN CC.CONSUMO_QUANTIDADE_T15
                     WHEN 16 THEN CC.CONSUMO_QUANTIDADE_T16
                     WHEN 17 THEN CC.CONSUMO_QUANTIDADE_T17
                     WHEN 18 THEN CC.CONSUMO_QUANTIDADE_T18
                     WHEN 19 THEN CC.CONSUMO_QUANTIDADE_T19
                     WHEN 20 THEN CC.CONSUMO_QUANTIDADE_T20
                     END) CONSUMO_QUANTIDADE,
            
                    IIF(COALESCE(M.GRADE_CODIGO,0) = 0 AND S.TAMANHO = 0,CC.TAMANHO,
                     CASE S.TAMANHO
                     WHEN 01 THEN CC.TAM01
                     WHEN 02 THEN CC.TAM02
                     WHEN 03 THEN CC.TAM03
                     WHEN 04 THEN CC.TAM04
                     WHEN 05 THEN CC.TAM05
                     WHEN 06 THEN CC.TAM06
                     WHEN 07 THEN CC.TAM07
                     WHEN 08 THEN CC.TAM08
                     WHEN 09 THEN CC.TAM09
                     WHEN 10 THEN CC.TAM10
                     WHEN 11 THEN CC.TAM11
                     WHEN 12 THEN CC.TAM12
                     WHEN 13 THEN CC.TAM13
                     WHEN 14 THEN CC.TAM14
                     WHEN 15 THEN CC.TAM15
                     WHEN 16 THEN CC.TAM16
                     WHEN 17 THEN CC.TAM17
                     WHEN 18 THEN CC.TAM18
                     WHEN 19 THEN CC.TAM19
                     WHEN 20 THEN CC.TAM20
                     END) CONSUMO_FATOR_CALCULO,
                    CC.TIPO_PESO_NF CONSUMO_FATOR_CALCULO_TIPO,
                    CC.FATOR_CONVERSAO CONSUMO_FATOR_DIVISOR,
                    1 CONSUMO_FATOR_MULTIPLICADOR,
                    P.UNIDADEMEDIDA_SIGLA CONSUMO_UM,
                    F.UNIDADEMEDIDA_ALTERNATIVO CONSUMO_UM_ALTERNATIVA,
                    CC.MATRIZ_ID MATRIZ_ID,
                    (SELECT FIRST 1 Z.DENSIDADE FROM TBMODELO Z WHERE Z.CODIGO = P.MODELO_CODIGO) DENSIDADE,
                    (SELECT FIRST 1 Z.ESPESSURA FROM TBMODELO Z WHERE Z.CODIGO = P.MODELO_CODIGO) ESPESSURA,  
                    P.LOCALIZACAO_CODIGO CONSUMO_LOCALIZACAO_ID,
                    CC.HABILITA_GERAR_CONSUMO,
                    M.CLASSIFICACAO AS PERFIL_MOD,
                    M.LINHA_CODIGO AS LINHA_ID,
                    CC.SETOR_CODIGO AS SETOR_ID,
                    CC.PECA_CONJUNTO

                FROM
                    TBMODELO_CONSUMO CC,
                    TBFAMILIA F,
                    TBMODELO M,
                    TBPRODUTO P,
                    TBGRADE_SEQ S
                
                WHERE TRUE
                AND M.CODIGO = CC.MODELO_CODIGO
                AND P.CODIGO = CC.CONSUMO_PRODUTO_CODIGO
                AND F.CODIGO = P.FAMILIA_INVENTARIO
            
            ) X
            WHERE CONSUMO_QUANTIDADE > 0
         ) Z
    );

