/*
  Programador | Solicitação | Descrição
  Juliano     | 11368       | Ajustes no quadro de formulas do Defis.
*/
/* Insere o "0" na frente do BDNUMITEM do itens que tenham 1 digito, ou 1 digito antes do ponto */
UPDATE VEF_BASE_TCONFDASNFORM f
SET f.BDNUMITEM = '0'||f.BDNUMITEM
WHERE (CHAR_LENGTH(f.BDNUMITEM) = 1)
   OR ((f.BDNUMITEM LIKE '%.%') AND (CHAR_LENGTH(SUBSTRING(f.BDNUMITEM FROM 01 FOR POSITION('.', f.BDNUMITEM)-1)) = 1));
COMMIT WORK;


SET TERM ^ ;

  Execute Block
  AS
     declare variable wSQL      VARCHAR(32000);
     declare variable wQuadro   INTEGER;
     declare variable wCampo    INTEGER;
     declare variable wFormula  VARCHAR(30000);
     declare variable wInativo  VARCHAR(5);
     declare variable wReadOnly VARCHAR(5);
  BEGIN
    FOR
       select q.BDCODQDASN, f.BDCODCONFDASN, f.BDFORMULADASN, f.BDCAMPOINATIVO, f.BDREADONLY from VEF_BASE_TCONFDASNQ q
       left outer join VEF_BASE_TCONFDASNFORM f on (f.BDCODQDASN = q.BDCODQDASN and f.BDNUMCAMPODASN = 5)
       where q.BDCLASSIFICACAO = '01.02'
    INTO :wQuadro, :wCampo, :wFormula, :wInativo, :wReadOnly DO
      BEGIN
        /* Cria formula "05.1" */
        wSQL = 'INSERT INTO VEF_BASE_TCONFDASNFORM (BDCODQDASN, BDCODCONFDASN, BDNUMCAMPODASN, BDDESCFORMDASN, BDFORMULADASN, BDCAMPOINATIVO, BDNUMITEM, BDREADONLY) '||
               ' VALUES ('||wQuadro||','||wCampo||',(select MAX(f.BDNUMCAMPODASN)+1 from VEF_BASE_TCONFDASNFORM f where f.BDCODQDASN = '||wQuadro||' and f.BDCODCONFDASN = '||wCampo||'),'||
               '''Aquisições no mercado interno'', '''||wFormula||''', '''||wInativo||''', ''05.1'', '''||wReadOnly||''');';
        EXECUTE STATEMENT wSQL;
        /* Cria formula "05.2" */
        wSQL = 'INSERT INTO VEF_BASE_TCONFDASNFORM (BDCODQDASN, BDCODCONFDASN, BDNUMCAMPODASN, BDDESCFORMDASN, BDFORMULADASN, BDCAMPOINATIVO, BDNUMITEM, BDREADONLY) '||
               ' VALUES ('||wQuadro||','||wCampo||',(select MAX(f.BDNUMCAMPODASN)+1 from VEF_BASE_TCONFDASNFORM f where f.BDCODQDASN = '||wQuadro||' and f.BDCODCONFDASN = '||wCampo||'),'||
               '''Importações'', Null, '''||wInativo||''', ''05.2'', '''||wReadOnly||''');';
        EXECUTE STATEMENT wSQL;
        /* Atualiza formula "05" */
        wSQL = 'UPDATE VEF_BASE_TCONFDASNFORM f SET BDFORMULADASN = ''(C(01.02,05.1) + C(01.02,05.2))'' '||
               'WHERE (BDCODQDASN = '||wQuadro||') AND (BDCODCONFDASN = '||wCampo||') AND (BDNUMCAMPODASN = 5);';
        EXECUTE STATEMENT wSQL;
      END
    SUSPEND;
  END^

SET TERM ; ^




