GEP Business Intelligence

    /* Formula 1068 - TIPO AT  -  Ativo                        (ASSETS)              */

    SELECT (case EXTRACT(month FROM CAST(:WDTINI AS DATE))

    when 1 then coalesce(SUM (sdomes_atu_01),0) when 2 then coalesce(SUM(sdomes_atu_02),0)

    when 3 then coalesce(SUM(sdomes_atu_03),0)  when 4 then coalesce(SUM(sdomes_atu_04),0)

    when 5 then coalesce(SUM(sdomes_atu_05),0)  when 6 then coalesce(SUM(sdomes_atu_06),0)

    when 7 then coalesce(SUM(sdomes_atu_07),0)  when 8 then coalesce(SUM(sdomes_atu_08),0)

    when 9 then coalesce(SUM(sdomes_atu_09),0)  when 10 then coalesce(SUM(sdomes_atu_10),0)

    when 11 then coalesce(SUM(sdomes_atu_11),0) when 12 then coalesce(SUM(sdomes_atu_12),0) ELSE 0 END) as VALOR FROM sic0805

    WHERE TIPO = 'AT' and

    ano = EXTRACT(year FROM CAST(:WDTINI AS DATE))

    AND PAI = 'S'

    /* Formula 1069 - TIPO AC  -  Ativo Circulante          (CURRENT ASSETS)     */

    SELECT coalesce((case EXTRACT(month FROM CAST(:WDTINI AS DATE))

    when 1 then SUM(sdomes_atu_01)      when 2 then SUM(sdomes_atu_02)

    when 3 then SUM(sdomes_atu_03)      when 4 then SUM(sdomes_atu_04)

    when 5 then SUM(sdomes_atu_05)      when 6 then SUM(sdomes_atu_06)

    when 7 then SUM(sdomes_atu_07)      when 8 then SUM(sdomes_atu_08)

    when 9 then SUM(sdomes_atu_09)      when 10 then SUM(sdomes_atu_10)

    when 11 then SUM(sdomes_atu_11)     when 12 then SUM(sdomes_atu_12) ELSE 0 END),0)   AS VALOR FROM sic0805

    WHERE TIPO = 'AC'

    and ano = EXTRACT(year FROM CAST(:WDTINI AS DATE))

    /* Formula 1070 - TIPO CX  -  Caixa Geral               (GENERAL BOX)  */

    SELECT (case EXTRACT(month FROM CAST(:WDTINI AS DATE))

    when 1 then coalesce(SUM (sdomes_atu_01),0) when 2 then coalesce(SUM(sdomes_atu_02),0)

    when 3 then coalesce(SUM(sdomes_atu_03),0)  when 4 then coalesce(SUM(sdomes_atu_04),0)

    when 5 then coalesce(SUM(sdomes_atu_05),0)  when 6 then coalesce(SUM(sdomes_atu_06),0)

    when 7 then coalesce(SUM(sdomes_atu_07),0)  when 8 then coalesce(SUM(sdomes_atu_08),0)

    when 9 then coalesce(SUM(sdomes_atu_09),0)  when 10 then coalesce(SUM(sdomes_atu_10),0)

    when 11 then coalesce(SUM(sdomes_atu_11),0) when 12 then coalesce(SUM(sdomes_atu_12),0) ELSE 0 END) AS VALOR FROM sic0805

    WHERE TIPO = 'CX'

    and ano = EXTRACT(year FROM CAST(:WDTINI AS DATE))

    /* Formula 1071 - TIPO DI  -  Disponivel                (AVAILABEL)        */

    SELECT (case EXTRACT(month FROM CAST(:WDTINI AS DATE))

    when 1 then SUM(sdomes_atu_01) when 2 then SUM(sdomes_atu_02)

    when 3 then SUM(sdomes_atu_03) when 4 then SUM(sdomes_atu_04)

    when 5 then SUM(sdomes_atu_05) when 6 then SUM(sdomes_atu_06)

    when 7 then SUM(sdomes_atu_07) when 8 then SUM(sdomes_atu_08)

    when 9 then SUM(sdomes_atu_09) when 10 then SUM(sdomes_atu_10)

    when 11 then SUM(sdomes_atu_11) when 12 then SUM(sdomes_atu_12) ELSE 0 END) AS VALOR FROM sic0805

    WHERE TIPO = 'DI'

    and ano = EXTRACT(year FROM CAST(:WDTINI AS DATE))

    /* Formula 1072 - TIPO LL  -  Lucro do Periodo            ( PERIODO PROFIT)    */

    SELECT coalesce((case EXTRACT(month FROM CAST(:WDTFIM AS DATE))

    when 1 then SUM(sdomes_atu_01)  when 2 then SUM(sdomes_atu_02)

    when 3 then SUM(sdomes_atu_03)  when 4 then SUM(sdomes_atu_04)

    when 5 then SUM(sdomes_atu_05)  when 6 then SUM(sdomes_atu_06)

    when 7 then SUM(sdomes_atu_07)  when 8 then SUM(sdomes_atu_08)

    when 9 then SUM(sdomes_atu_09)  when 10 then SUM(sdomes_atu_10)

    when 11 then SUM(sdomes_atu_11) when 12 then SUM(sdomes_atu_12) ELSE 0 END),1) AS VALOR FROM sic0805

    WHERE TIPO = 'LL'

    and ano = EXTRACT(year FROM CAST(:WDTFIM AS DATE))

    /* Formula 1073 - TIPO RO  -  Resultado Operacional         (OPERATIONAL RESULT)   */

    select first 1 ((SELECT (case EXTRACT(month FROM CAST(:WDTINI AS DATE))

    when 1 then SUM(creditos_mes_01) when 2 then SUM(creditos_mes_02)

    when 3 then SUM(creditos_mes_03) when 4 then SUM(creditos_mes_04)

    when 5 then SUM(creditos_mes_05) when 6 then SUM(creditos_mes_06)

    when 7 then SUM(creditos_mes_07) when 8 then SUM(creditos_mes_08)

    when 9 then SUM(creditos_mes_09) when 10 then SUM(creditos_mes_10)

    when 11 then SUM(creditos_mes_11) when 12 then SUM(creditos_mes_12) ELSE 0 END) FROM sic0805

    WHERE TIPO = 'VL'

    and ano = EXTRACT(year FROM CAST(:WDTINI AS DATE)))-

    (SELECT (case EXTRACT(month FROM CAST(:WDTINI AS DATE))

    when 1 then SUM(debitos_mes_01) when 2 then SUM(debitos_mes_02)

    when 3 then SUM(debitos_mes_03) when 4 then SUM(debitos_mes_04)

    when 5 then SUM(debitos_mes_05) when 6 then SUM(debitos_mes_06)

    when 7 then SUM(debitos_mes_07)      when 8 then SUM(debitos_mes_08)

    when 9 then SUM(debitos_mes_09)      when 10 then SUM(debitos_mes_10)

    when 11 then SUM(debitos_mes_11) when 12 then SUM(debitos_mes_12) ELSE 0 END) FROM sic0805

    WHERE TIPO = 'PT'

    and ano = EXTRACT(year FROM CAST(:WDTINI AS DATE)))) AS VALOR from sic0805

    /* Formula 1074 - TIPO PC   -  Passivo Circulante         (CURRENT LIABILITIES)    */

    SELECT coalesce((case EXTRACT(month FROM CAST(:WDTINI AS DATE))

    when 1 then SUM(sdomes_atu_01*-1)      when 2 then SUM(sdomes_atu_02*-1)

    when 3 then SUM(sdomes_atu_03*-1)      when 4 then SUM(sdomes_atu_04*-1)

    when 5 then SUM(sdomes_atu_05*-1)      when 6 then SUM(sdomes_atu_06*-1)

    when 7 then SUM(sdomes_atu_07*-1)      when 8 then SUM(sdomes_atu_08*-1)

    when 9 then SUM(sdomes_atu_09*-1)      when 10 then SUM(sdomes_atu_10*-1)

    when 11 then SUM(sdomes_atu_11*-1)     when 12 then SUM(sdomes_atu_12*-1) ELSE 0 END),1) AS VALOR      FROM sic0805

    WHERE TIPO = 'PC'

    and ano = EXTRACT(year FROM CAST(:WDTINI AS DATE))

    /* Formula 1075 - TIPO PL  -  Patrimonio Liquido          (NET WORTH)     */

    SELECT (case EXTRACT(month FROM CAST(:WDTINI AS DATE))

    when 1 then SUM(sdomes_atu_01)      when 2 then SUM(sdomes_atu_02)

    when 3 then SUM(sdomes_atu_03)      when 4 then SUM(sdomes_atu_04)

    when 5 then SUM(sdomes_atu_05)      when 6 then SUM(sdomes_atu_06)

    when 7 then SUM(sdomes_atu_07)      when 8 then SUM(sdomes_atu_08)

    when 9 then SUM(sdomes_atu_09)      when 10 then SUM(sdomes_atu_10)

    when 11 then SUM(sdomes_atu_11)      when 12 then SUM(sdomes_atu_12) ELSE 0 END)AS VALOR FROM sic0805

    WHERE TIPO = 'PL' and ano = EXTRACT(year FROM CAST(:WDTINI AS DATE))

    /* Formula 1076 - TIPO PT  -  Passivo                      (LIBILITIES)   */

    SELECT (case EXTRACT(month FROM CAST(:WDTINI AS DATE))

    when 1 then SUM(sdomes_atu_01*-1)      when 2 then SUM(sdomes_atu_02*-1)

    when 3 then SUM(sdomes_atu_03*-1)      when 4 then SUM(sdomes_atu_04*-1)

    when 5 then SUM(sdomes_atu_05*-1)      when 6 then SUM(sdomes_atu_06*-1)

    when 7 then SUM(sdomes_atu_07*-1)      when 8 then SUM(sdomes_atu_08*-1)

    when 9 then SUM(sdomes_atu_09*-1)      when 10 then SUM(sdomes_atu_10*-1)

    when 11 then SUM(sdomes_atu_11*-1)      when 12 then SUM(sdomes_atu_12*-1) ELSE 0 END) AS VALOR       FROM sic0805

    WHERE TIPO = 'PT'

    and ano = EXTRACT(year FROM CAST(:WDTINI AS DATE))

    /* Formula 1077 - TIPO RC - Realizavel a Curto Prazo      (ACHIEVABLE IN THE SHORT TIME) */

    SELECT (case EXTRACT(month FROM CAST(:WDTINI AS DATE))

    when 1 then SUM(sdomes_atu_01)      when 2 then SUM(sdomes_atu_02)

    when 3 then SUM(sdomes_atu_03)      when 4 then SUM(sdomes_atu_04)

    when 5 then SUM(sdomes_atu_05)      when 6 then SUM(sdomes_atu_06)

    when 7 then SUM(sdomes_atu_07)      when 8 then SUM(sdomes_atu_08)

    when 9 then SUM(sdomes_atu_09)      when 10 then SUM(sdomes_atu_10)

    when 11 then SUM(sdomes_atu_11)     when 12 then SUM(sdomes_atu_12) ELSE 0 END)       FROM sic0805

    WHERE TIPO = 'RC'

    and ano = EXTRACT(year FROM CAST(:WDTINI AS DATE))

    AND PAI = 'S'

    /* Formula 1078 - TIPO RL - Receita Liquido      (NET REVENUE)  */

     SELECT COALESCE(case EXTRACT(month FROM CAST(:WDTINI AS DATE))

     when 1 then SUM(creditos_mes_01)  when 2 then SUM(creditos_mes_02)

     when 3 then SUM(creditos_mes_03)  when 4 then SUM(creditos_mes_04)

     when 5 then SUM(creditos_mes_05)  when 6 then SUM(creditos_mes_06)

     when 7 then SUM(creditos_mes_07)  when 8 then SUM(creditos_mes_08)

     when 9 then SUM(creditos_mes_09)  when 10 then SUM(creditos_mes_10)

     when 11 then SUM(creditos_mes_11)  when 12 then SUM(creditos_mes_12) ELSE 0 END,0) AS VALOR FROM sic0805

     WHERE TIPO = 'RL'

     and ano = EXTRACT(year FROM CAST(:WDTINI AS DATE))

     AND PAI = 'S'

    /* Formula 1079 - TIPO VL - Vendas Liquidas         (LIQUID SALES)*/

    select first 1 ((SELECT (case EXTRACT(month FROM CAST(:WDTINI AS DATE))

    when 1 then SUM(creditos_mes_01) when 2 then SUM(creditos_mes_02)

    when 3 then SUM(creditos_mes_03) when 4 then SUM(creditos_mes_04)

    when 5 then SUM(creditos_mes_05) when 6 then SUM(creditos_mes_06)

    when 7 then SUM(creditos_mes_07) when 8 then SUM(creditos_mes_08)

    when 9 then SUM(creditos_mes_09)  when 10 then SUM(creditos_mes_10)

    when 11 then SUM(creditos_mes_11) when 12 then SUM(creditos_mes_12) ELSE 0 END)  FROM sic0805

    WHERE TIPO = 'VL'

    and ano = EXTRACT(year FROM CAST(:WDTINI AS DATE)))) AS VALOR from sic0805

    /* Formula 1080 - TIPO ES - Estoque                 (INVENTORY)  */

    SELECT coalesce((case EXTRACT(month FROM CAST(:WDTFIM AS DATE))

    when 1 then SUM(sdomes_atu_01)      when 2 then SUM(sdomes_atu_02)

    when 3 then SUM(sdomes_atu_03)      when 4 then SUM(sdomes_atu_04)

    when 5 then SUM(sdomes_atu_05)      when 6 then SUM(sdomes_atu_06)

    when 7 then SUM(sdomes_atu_07)      when 8 then SUM(sdomes_atu_08)

    when 9 then SUM(sdomes_atu_09)      when 10 then SUM(sdomes_atu_10)

    when 11 then SUM(sdomes_atu_11)     when 12 then SUM(sdomes_atu_12) ELSE 0 END),1) AS VALOR       FROM sic0805

    WHERE TIPO = 'ES'

    and ano = EXTRACT(year FROM CAST(:WDTFIM AS DATE))

    /* Formula 1092 - TIPO PN - Passivo Nao Circulante       (NON-CURRENT LIABILITIES) */

    SELECT COALESCE(case EXTRACT(month FROM CAST(:WDTINI AS DATE))

    when 1 then SUM(sdomes_atu_01)  when 2 then SUM(sdomes_atu_02)

    when 3 then SUM(sdomes_atu_03)  when 4 then SUM(sdomes_atu_04)

    when 5 then SUM(sdomes_atu_05)  when 6 then SUM(sdomes_atu_06)

    when 7 then SUM(sdomes_atu_07)  when 8 then SUM(sdomes_atu_08)

    when 9 then SUM(sdomes_atu_09)  when 10 then SUM(sdomes_atu_10)

    when 11 then SUM(sdomes_atu_11) when 12 then SUM(sdomes_atu_12) ELSE 0 END,0) AS VALOR FROM sic0805

    WHERE TIPO = 'PN'

    and ano = EXTRACT(year FROM CAST(:WDTINI AS DATE))

    /* Formula 1093  - TIPO PC + PN   Passivo Nao Circulante       (NON-CURRENT LIABILITIES)*/

    select FIRST 1 ((SELECT (case EXTRACT(month FROM CAST(:WDTINI AS DATE))

    when 1 then SUM(sdomes_atu_01)  when 2 then SUM(sdomes_atu_02)

    when 3 then SUM(sdomes_atu_03)  when 4 then SUM(sdomes_atu_04)

    when 5 then SUM(sdomes_atu_05)  when 6 then SUM(sdomes_atu_06)

    when 7 then SUM(sdomes_atu_07)  when 8 then SUM(sdomes_atu_08)

    when 9 then SUM(sdomes_atu_09)  when 10 then SUM(sdomes_atu_10)

    when 11 then SUM(sdomes_atu_11) when 12 then SUM(sdomes_atu_12) ELSE 0 END) FROM sic0805

    WHERE TIPO = 'PC'

    and ano = EXTRACT(year FROM CAST(:WDTINI AS DATE)))+

    (SELECT (case EXTRACT(month FROM CAST(:WDTINI AS DATE))

    when 1 then SUM(sdomes_atu_01)  when 2 then SUM(sdomes_atu_02)

    when 3 then SUM(sdomes_atu_03)  when 4 then SUM(sdomes_atu_04)

    when 5 then SUM(sdomes_atu_05)  when 6 then SUM(sdomes_atu_06)

    when 7 then SUM(sdomes_atu_07)  when 8 then SUM(sdomes_atu_08)

    when 9 then SUM(sdomes_atu_09)  when 10 then SUM(sdomes_atu_10)

    when 11 then SUM(sdomes_atu_11) when 12 then SUM(sdomes_atu_12) ELSE 0 END) FROM sic0805

    WHERE TIPO = 'PN'

    and ano = EXTRACT(year FROM CAST(:WDTINI AS DATE)))) AS VALOR from sic0805

    /* Formula 1094 - TIPO IN   Investimentos             INVESTMENTS  */

    SELECT coalesce((case EXTRACT(month FROM CAST(:WDTFIM AS DATE))

    when 1 then SUM(sdomes_atu_01)  when 2 then SUM(sdomes_atu_02)

    when 3 then SUM(sdomes_atu_03)  when 4 then SUM(sdomes_atu_04)

    when 5 then SUM(sdomes_atu_05)  when 6 then SUM(sdomes_atu_06)

    when 7 then SUM(sdomes_atu_07)  when 8 then SUM(sdomes_atu_08)

    when 9 then SUM(sdomes_atu_09)  when 10 then SUM(sdomes_atu_10)

    when 11 then SUM(sdomes_atu_11) when 12 then SUM(sdomes_atu_12) ELSE 0 END),1) AS VALOR FROM sic0805

    WHERE TIPO = 'IN'

    and ano = EXTRACT(year FROM CAST(:WDTFIM AS DATE))

/* Formula 1095  - TIPO EB = LL + IR + CS+ DP + AM + RN     EBITIDA    */

    SELECT (case EXTRACT(month FROM CAST(:WDTINI AS DATE))

    when 1 then SUM(sdomes_atu_01)  when 2 then SUM(sdomes_atu_02)

    when 3 then SUM(sdomes_atu_03)  when 4 then SUM(sdomes_atu_04)

    when 5 then SUM(sdomes_atu_05)  when 6 then SUM(sdomes_atu_06)

    when 7 then SUM(sdomes_atu_07)  when 8 then SUM(sdomes_atu_08)

    when 9 then SUM(sdomes_atu_09)  when 10 then SUM(sdomes_atu_10)

    when 11 then SUM(sdomes_atu_11) when 12 then SUM(sdomes_atu_12) ELSE 0 END) AS VALOR FROM sic0805

    WHERE (TIPO = 'LL' or TIPO = 'IR' or TIPO = 'CS' or TIPO = 'DP' or TIPO = 'AM' or TIPO = 'RN')

    and ano = EXTRACT(year FROM CAST(:WDTINI AS DATE))

    /* Formula 1096 - TIPO PP   Passivo a Curto Prazo             SHORT-TERM LIABILITIES  */

    SELECT coalesce((case EXTRACT(month FROM CAST(:WDTFIM AS DATE))

    when 1 then SUM(sdomes_atu_01)  when 2 then SUM(sdomes_atu_02)

    when 3 then SUM(sdomes_atu_03)  when 4 then SUM(sdomes_atu_04)

    when 5 then SUM(sdomes_atu_05)  when 6 then SUM(sdomes_atu_06)

    when 7 then SUM(sdomes_atu_07)  when 8 then SUM(sdomes_atu_08)

    when 9 then SUM(sdomes_atu_09)  when 10 then SUM(sdomes_atu_10)

    when 11 then SUM(sdomes_atu_11) when 12 then SUM(sdomes_atu_12) ELSE 0 END),1) AS VALOR FROM sic0805

    WHERE TIPO = 'PP'

    and ano = EXTRACT(year FROM CAST(:WDTFIM AS DATE))

    /* Formula 1097 - TIPO DO - Despesas Operacionais         (OPERATION EXPENSES)*/

    select first 1 ((SELECT (case EXTRACT(month FROM CAST(:WDTINI AS DATE))

    when 1 then SUM(debitos_mes_01) when 2 then SUM(debitos_mes_02)

    when 3 then SUM(debitos_mes_03) when 4 then SUM(debitos_mes_04)

    when 5 then SUM(debitos_mes_05) when 6 then SUM(debitos_mes_06)

    when 7 then SUM(debitos_mes_07) when 8 then SUM(debitos_mes_08)

    when 9 then SUM(debitos_mes_09)  when 10 then SUM(debitos_mes_10)

    when 11 then SUM(debitos_mes_11) when 12 then SUM(debitos_mes_12) ELSE 0 END)  FROM sic0805

    WHERE DRE = 'DO'

    and ano = EXTRACT(year FROM CAST(:WDTINI AS DATE)))) AS VALOR from sic0805

    /* Formula 1098 - TIPO CP - Cogs         (Cuto de Produtos)   */

    select first 1 ((SELECT (case EXTRACT(month FROM CAST(:WDTINI AS DATE))

    when 1 then SUM(debitos_mes_01) when 2 then SUM(debitos_mes_02)

    when 3 then SUM(debitos_mes_03) when 4 then SUM(debitos_mes_04)

    when 5 then SUM(debitos_mes_05) when 6 then SUM(debitos_mes_06)

    when 7 then SUM(debitos_mes_07) when 8 then SUM(debitos_mes_08)

    when 9 then SUM(debitos_mes_09)  when 10 then SUM(debitos_mes_10)

    when 11 then SUM(debitos_mes_11) when 12 then SUM(debitos_mes_12) ELSE 0 END)  FROM sic0805

    WHERE DRE = 'CP'

    and ano = EXTRACT(year FROM CAST(:WDTINI AS DATE)))) AS VALOR from sic0805

   /* Grafico 1102 -  Endividamento (de Mes_Ini a Mes_Fin)      (Indebtedness -  From Months_ini a Months_Fin)          */

    SELECT ((a.ano)||'-'||a.mes) as DATA,

   (select cast(coalesce(sum(VALOR),0) as numeric(15,2)) from gep9911

    where IDINDICADOR = 28

    and extract  (month  FROM gep9911.DATA) = a.mes

    and extract (year from gep9911.DATA) =   a.ano) as DATA2,

    (select cast(coalesce(sum(VALOR),0) as numeric(15,2)) from gep9911

    where IDINDICADOR = 29

    and extract  (month  FROM gep9911.DATA) = a.mes

    and extract (year from gep9911.DATA) =   a.ano) as DATA1,

    (select cast(coalesce(sum(VALOR),0) as numeric(15,2)) from gep9911

    where IDINDICADOR = 30

    and extract  (month  FROM gep9911.DATA) = a.mes

    and extract (year from gep9911.DATA) =   a.ano) as DATA3,

    (select cast(coalesce(sum(VALOR),0) as numeric(15,2)) from gep9911

    where IDINDICADOR = 31

    and extract  (month  FROM gep9911.DATA) = a.mes

    and extract (year from gep9911.DATA) =   a.ano) as DATA4 FROM GEP9952 a

    where A.DATA >= :WDTINI

    and a.data <= :WDTFIM group by a.mes, a.ano

    order by a.ano, a.mes

    /* Grafico 1153 -  Endividamento (Ultimos 12 Meses)           (Indebtedness -  Last 12 Months)  */

    select a.mes,a.ano, lpad(a.mes,2,0)||'-'||a.ano as data,

    (select  cast(coalesce(sum(b.valor),0) as numeric (15,2)) from gep9911 b

    WHERE (extract (month from b.DATA) = a.mes

    and (extract(year from b.DATA) = a.ano))

    and b.idindicador = 28) as DATA1,

    (select  cast(coalesce(sum(b.valor),0) as numeric (15,2)) from gep9911 b

    WHERE (extract (month from b.DATA) = a.mes

    and (extract(year from b.DATA) = a.ano))

    and b.idindicador = 32) as DATA2 from GEP9952 a

    where a.data >=

    (select gep9952.data from gep9952

    where extract(day from gep9952.data) = 1

    and gep9952.mes = extract(month from current_date)

    and gep9952.ano = extract(year from current_date)-1)

    and a.data <(select gep9952.data  from gep9952

    where extract(day from gep9952.data) = 1

    and gep9952.mes = extract(month from current_date)

    and gep9952.ano = extract(year from current_date))

    group by 1,2 order by a.ano

    /* Grafico 1150 -  Receita Liquida x Margem Liquida (Ultimos 12 Meses)    (Net Revenue x Net Margin -  Last 12 Months) */

    select a.mes,a.ano, lpad(a.mes,2,0)||'-'||a.ano as data,

    (select  cast(coalesce(sum(b.valor),0) as numeric (15,2)) from gep9911 b

    WHERE (extract (month from b.DATA) = a.mes

    and (extract(year from b.DATA) = a.ano))

    and b.idindicador = 33) as DATA1,

    (select  cast(coalesce(sum(b.valor),0) as numeric (15,2)) from gep9911 b

    WHERE (extract (month from b.DATA) = a.mes

    and (extract(year from b.DATA) = a.ano))

    and b.idindicador = 37) as DATA2  from GEP9952 a

    where a.data >=

    (select gep9952.data from gep9952

    where extract(day from gep9952.data) = 1

    and gep9952.mes = extract(month from current_date)

    and gep9952.ano = extract(year from current_date)-1)

    and a.data <

    (select gep9952.data  from gep9952

    where extract(day from gep9952.data) = 1

    and gep9952.mes = extract(month from current_date)

    and gep9952.ano = extract(year from current_date))

    group by 1,2 order by a.ano

    /* Grafico 1152  -  Receita Liquida x Despesas x Custo (Ultimos 12 Meses)    (Net Revenue x Expenses x Cogs  -  Last 12 Months) */

    select a.mes,a.ano, lpad(a.mes,2,0)||'-'||a.ano as data,

    (select  cast(coalesce(sum(b.valor),0) as numeric (15,2)) from gep9911 b

    WHERE (extract (month from b.DATA) = a.mes

    and (extract(year from b.DATA) = a.ano))

    and b.idindicador = 33) as DATA1,

    (select  cast(coalesce(sum(b.valor),0) as numeric (15,2)) from gep9911 b

    WHERE (extract (month from b.DATA) = a.mes

    and (extract(year from b.DATA) = a.ano))

    and b.idindicador = 39) as DATA3,

    (select  cast(coalesce(sum(b.valor),0) as numeric (15,2)) from gep9911 b

    WHERE (extract (month from b.DATA) = a.mes

    and (extract(year from b.DATA) = a.ano))

    and b.idindicador = 40) as DATA2  from GEP9952 a

    where a.data >= (select gep9952.data from gep9952

    where extract(day from gep9952.data) = 1

    and gep9952.mes = extract(month from current_date)

    and gep9952.ano = extract(year from current_date)-1)

    and a.data <(select gep9952.data  from gep9952

    where extract(day from gep9952.data) = 1

    and gep9952.mes = extract(month from current_date)

    and gep9952.ano = extract(year from current_date))

    group by 1,2 order by a.ano