--- BEG # 0001
DROP VIEW IF EXISTS `vwexporta_nf_arlindo`;
CREATE VIEW `vwexporta_nf_arlindo` AS 
         SELECT NF.mesano,  
         IF((NF.`ta` = '01A400'), 'Convencional', 
         IF((NF.`ta` = '01AA00'), 'Horo Sazonal Azul', 
         IF((NF.`ta` = '01AV00'), 'Horo Sazonal Verde', 
         IF(((NF.`ta` = '01AL00')  AND (NF.`livreespecial` = 1)), 'Consumidor Livre Especial', 
         IF((NF.`ta` = '01AL00'), 'Consumidor Livre', 
         IF((NF.`ta` = '01AS00'), 'Suprimento', 
         IF((NF.`ta` = '01B100'), 'Residencial', 
         IF((NF.`ta` = '01B200'), 'Rural', 
         IF((NF.`ta` = '01B400'), 'Iluminação Pública', 
         'Demais Classes'))))))))) AS `grupo_tarifario`, 
         NF.`grupo` AS `grupo`, 
         NF.`codclas` AS `codigo_classe`, 
         IF((NF.`codclas` = 9101),'Residencial Normal', 
         IF((NF.`codclas` = 9104),'Residencial Baixa Renda (246)', 
         IF((NF.`codclas` = 9105),'Residencial Baixa Renda (485)', 
         IF((NF.`codclas` = 9106),'Residencial Baixa Renda (694)', 
         IF((NF.`codclas` BETWEEN 0 And 3999),'Industrial', 
         IF((NF.`codclas` BETWEEN 4000 And 4999), 'Comercial', 
         IF((NF.`codclas` BETWEEN 5000 And 5999), 'Rural', 
         IF((NF.`codclas` BETWEEN 6100 And 6199), 'Poder Público Municipal',
         IF((NF.`codclas` BETWEEN 6200 And 6299), 'Poder Público Estadual',
         IF((NF.`codclas` BETWEEN 6300 And 6399), 'Poder Público Federal',
         IF((NF.`codclas` BETWEEN 6400 And 6499), 'Iluminação Pública',
         IF((NF.`codclas` BETWEEN 7200 And 7299), 'Água, Esgosto e Saneamento',
         IF(((NF.`codclas` BETWEEN 7100 And 7199) AND (NF.`codclas` BETWEEN 7300 AND 7999)), 'Serviço Público',
         IF((NF.`codclas` BETWEEN 8000 And 8999), 'Consumo Próprio', 
         'Indefinido')))))))))))))) AS `classe_faturada`,
         `classe`.`descricao` AS `descricao_classe`,
         NF.`zona` AS `zona`,
         NF.`livro` AS `livro`,
         NF.`conta` AS `conta`,
         NF.`banco_fatur` AS `id_banco`,
         NF.`banident` AS `banco`,
         `consum`.`matricula` AS `matricula`,
         `consum`.`nome` AS `nome`,
         NF.`numnf` AS `numero_fatura`,
         NF.`mesano` AS `mesano_fatura`,
         NF.`mesano_ref` AS `mesano_referencia`,
         NF.`anomes_fat` AS `anomes_fat`,
         NF.`dataem` AS `data_emissao`,
         NF.`dataapr` AS `data_apresentacao`,
         NF.`datavenc` AS `data_vencimento`,
         NF.`dataleit` AS `data_leitura_atual`,
         NF.`dataleitant` AS `data_leitura_anterior`,
         NF.`itensserv` AS `itensserv`,
         NF.`banarrec` AS `banarrec`,
          NF.`livreespecial`, 
          NF.`livre_verde`, 
          NF.`parcialmente_cativo`, 
          NF.`flgmedfat`, 
          NF.`fatura_final`, 
          NF.`irrigacao_aquicultura`, 
          NF.`micro_geracao_convenio_sefaz`, 
          NF.`micro_geracao_distribuida`, 
          NF.`tarifa_branca`, 
          NF.`unid_id`, 
          NF.`cons_id`, 
          NF.`ta`, 
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020012') AS `valor_consumo_reativo`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020031') AS `valor_demanda_reativo`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020013') AS `valor_consumo_reativo_ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020014') AS `valor_consumo_reativo_fora_ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020027') AS `valor_demanda_reativo_ponta`, 
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020028') AS `valor_demanda_reativo_fora_ponta`,
         IF((NF.`grupo` = 'B'),(((`BUSCAVALORITEM`(NF.`itensserv`,'030201009') + `BUSCAVALORITEM`(NF.`itensserv`,'0302010190')) + `BUSCAVALORITEM`(NF.`itensserv`,'0302010390')) + `BUSCAVALORITEM`(NF.`itensserv`,'0302010290')),`BUSCAVALORITEM`(NF.`itensserv`,'0302010009')) AS `kwh_faturado`,
         IF((NF.`grupo` = 'B'),`BUSCAVALORITEM`(NF.`itensserv`,'030202009'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020009')) AS `vlr_kwh_faturado`,
         IF((IF((NF.`grupo` = 'A'),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010060'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010010')),0) > 0),IF((NF.`grupo` = 'A'),(`BUSCAVALORITEM`(NF.`itensserv`,'0302010010') - `BUSCAVALORITEM`(NF.`itensserv`,'0302010060')),0),0) AS `kwh_faturado_ponta`,
         IF((IF((NF.`grupo` = 'A'),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020060'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020010')),0) > 0),IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020010'),0),0) AS `valor_kwh_faturado_ponta`,
         IF((IF((NF.`grupo` = 'A'),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010061'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010011')),0) > 0),IF((NF.`grupo` = 'A'),(`BUSCAVALORITEM`(NF.`itensserv`,'0302010011') - `BUSCAVALORITEM`(NF.`itensserv`,'0302010061')),0),0) AS `kwh_faturado_fora_ponta`,
         IF((IF((NF.`grupo` = 'A'),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020061'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020011')),0) > 0),IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020011'),0),0) AS `valor_kwh_faturado_fora_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010041'),0) AS `kwh_devolvido_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020041'),0) AS `valor_kwh_devolvido_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010042'),0) AS `kwh_devolvido_fora_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020042'),0) AS `valor_kwh_devolvido_fora_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010043'),0) AS `kwh_ultrapassado_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020043'),0) AS `valor_kwh_ultrapassado_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010044'),0) AS `kwh_ultrapassado_fora_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020044'),0) AS `valor_kwh_ultrapassado_fora_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010017'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010032')) AS `kw_faturado`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020015'),0) AS `valor_kw_faturado`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010021'),0) AS `kw_faturado_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020021'),0) AS `valor_kw_faturado_ponta`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010025'),0) AS `kw_faturado_fora_ponta`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020023'),0) AS `valor_kw_faturado_fora_ponta`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010018'),0) AS `kw_ultrapassado`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020018'),0) AS `valor_kw_ultrapassado`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010022'),0) AS `kw_ultrapassagem_ponta`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020022'),0) AS `valor_kw_ultrapassagem_ponta`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010026'),0) AS `kw_ultrapassado_fora_ponta`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020026'),0) AS `valor_kw_ultrapassado_fora_ponta`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020051'),0) AS `valor_desconto_provisorio`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020052'),0) AS `valor_desconto_realizado`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020053'),0) AS `valor_devolucao_diferenca_ccee`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020054'),0) AS `valor_cobranca_diferenca_ccee`, 
         `BUSCAVALORITEM`(NF.`itensserv`,'903000')   AS `valor_convenios`, 
         (`BUSCAVALORITEM`(NF.`itensserv`,'908000') - ((`BUSCAVALORITEM`(NF.`itensserv`,'9080000061') + `BUSCAVALORITEM`(NF.`itensserv`,'9080000066')) + `BUSCAVALORITEM`(NF.`itensserv`,'9080000067'))) AS `valor_diversos`,
         (`BUSCAVALORITEM`(NF.`itensserv`,'904000') + `BUSCAVALORITEM`(NF.`itensserv`,'905000')) AS `valor_taxados`,
         (`BUSCAVALORITEM`(NF.`itensserv`,'0602020001') + `BUSCAVALORITEM`(NF.`itensserv`,'0604020001')) AS `Icms_st`,
         (`BUSCAVALORITEM`(NF.`itensserv`,'0602020002') + `BUSCAVALORITEM`(NF.`itensserv`,'0604020002')) AS `pis_st`,
         (`BUSCAVALORITEM`(NF.`itensserv`,'0602020003') + `BUSCAVALORITEM`(NF.`itensserv`,'0604020003')) AS `cofins_st`,
         `BUSCAVALORITEM`(NF.`itensserv`,'907000') AS `valor_devolucao`,
         ((`BUSCAVALORITEM`(NF.`itensserv`,'9080000061') + `BUSCAVALORITEM`(NF.`itensserv`,'9080000066')) + `BUSCAVALORITEM`(NF.`itensserv`,'9080000067')) AS `valor_encargos_financeiros`,
         (`BUSCAVALORITEM`(NF.`itensserv`,'909000') + `BUSCAVALORITEM`(NF.`itensserv`,'9070000088')) AS `valor_retencao`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0301000003') AS `valor_cip`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302050002') AS `valor_juros_ece`,
         0 AS `valor_diferenca_ece`,
         (`BUSCAVALORITEM`(NF.`itensserv`,'0302070002') + `BUSCAVALORITEM`(NF.`itensserv`,'0302070005')) AS `valor_ece`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020032') AS `valor_must`,
         (`BUSCAVALORITEM`(NF.`itensserv`,'0302030009') + `BUSCAVALORITEM`(NF.`itensserv`,'0302030010')) AS `valor_icms`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020029') AS `valor_pis`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020030') AS `valor_cofins`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020410') AS `valor_subvencao`,
         NF.`valortot` AS `valor_total`,
         NF.`valorliq` AS `valor_liquido`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010016') AS `demanda_contratada_convencional`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010020') AS `demanda_contratada_ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010024') AS `demanda_contratada_fora_ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010090') AS `qtde_fx1`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010091') AS `qtde_fx2`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010092') AS `qtde_fx3`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010093') AS `qtde_fx4`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020090') AS `vlr_fx1`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020091') AS `vlr_fx2`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020092') AS `vlr_fx3`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020093') AS `vlr_fx4`,
         IF((NF.`grupo` = 'B'),`BUSCAVALORITEM`(NF.`itensserv`,'030201008'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010008')) AS `kwh_lido`,
         NF.`fase` AS `fase`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302030001') AS `base_icms_1`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302030002') AS `aliquota_icms_1`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302030009') AS `valor_icms_1`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302030003') AS `base_icms_2`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302030013') AS `aliquota_icms_2`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302030010') AS `valor_icms_2`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020033') AS `base_pis`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'40PIS00001'),`BUSCAVALORITEM`(NF.`itensserv`,'0302990303')) AS `aliquota_pis`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020034') AS `base_cofins`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'40COF00001'),`BUSCAVALORITEM`(NF.`itensserv`,'0302990304')) AS `aliquota_cofins`,
         (((`BUSCAVALORITEM`(NF.`itensserv`,'908000') - `BUSCAVALORITEM`(NF.`itensserv`,'9080000098')) - `BUSCAVALORITEM`(NF.`itensserv`,'9080000092')) - ((((((((((((((((((`BUSCAVALORITEM`(NF.`itensserv`,'907000') - `BUSCAVALORITEM`(NF.`itensserv`,'9070000062')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000079')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000081')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000082')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000087')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000088')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000090')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000091')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000092')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000096')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000030')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000031')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000032')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000033')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000034')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000035')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000036')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000037'))) AS `receitas_financeiras`,
         (((((((`BUSCAVALORITEM`(NF.`itensserv`,'9070000030') + `BUSCAVALORITEM`(NF.`itensserv`,'9070000031')) + `BUSCAVALORITEM`(NF.`itensserv`,'9070000032')) + `BUSCAVALORITEM`(NF.`itensserv`,'9070000033')) + `BUSCAVALORITEM`(NF.`itensserv`,'9070000034')) + `BUSCAVALORITEM`(NF.`itensserv`,'9070000035')) + `BUSCAVALORITEM`(NF.`itensserv`,'9070000036')) + `BUSCAVALORITEM`(NF.`itensserv`,'9070000037')) AS `ressarcimento_idqual`,`BUSCAVALORITEM`(NF.`itensserv`,'9080000098') AS `valor_encargo_uso_sis_trans`,(`BUSCAVALORITEM`(NF.`itensserv`,'9070000087') + `BUSCAVALORITEM`(NF.`itensserv`,'9070000091')) AS `desconto_must`,IF((IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010060'),0) = 0),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010010'),0),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010060'),0)) AS `encargo_kwh_faturado_ponta`,
         IF((IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020060'),0) = 0),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020010'),0),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020060'),0)) AS `encargo_valor_kwh_faturado_ponta`,
         IF((IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010061'),0) = 0),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010011'),0),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010061'),0)) AS `encargo_kwh_faturado_fora_ponta`,
         IF((IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020061'),0) = 0),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020011'),0),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020061'),0)) AS `encargo_valor_kwh_faturado_fora_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302040030'),0) AS `mwh_medio`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302040031'),0) AS `mwh_medio_horas`,
         NF.`micro_geracao` AS `micro_geracao`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020994') + `BUSCAVALORITEM`(NF.`itensserv`,'0305020190') +`BUSCAVALORITEM`(NF.`itensserv`,'0306020090') AS `valor reverso`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020993') AS `valor reverso ban`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302022010') AS `reverso ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302022110') AS `reverso ponta bandeira`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302022011') AS `reverso fora de ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302022111') AS `reverso fora de ponta bandeira`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0555020777') AS `dif.rel.rec`,
         IF((NF.`grupo` = 'B'),`BUSCAVALORITEM`(NF.`itensserv`,'0302120009'),`BUSCAVALORITEM`(NF.`itensserv`,'0302120009')) AS `bandeira_vlr_kwh_fat`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302120010'),0) AS `bandeira_valor_kwh_fat_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302120011'),0) AS `bandeira_valor_kwh_fat_fora_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020055'),0) AS `valor_energia_desconto_provisorio`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020056'),0) AS `valor_energia_desconto_realizado`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020057'),0) AS `valor_energia_devolucao_diferenca_ccee`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020058'),0) AS `valor_energia_cobranca_diferenca_ccee`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010010') AS `consumo_ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010011') AS `consumo_fora_ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302012011') AS `reverso_fora_ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302013011') AS `reverso_residuo_fora_ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0303010090') AS `tusd`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010080') AS `consumo_medido`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020996') AS `reverso_lido`,
         NF.`flgst` AS `flgst`,
         NF.`datast` AS `datast`,
         NF.`datapag` AS `data_pagamento`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302022011') AS `reverso_fora_de__ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010190') AS `kwh_faturado__ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010390') AS `kwh_faturado__Intermediaria`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010290') AS `kwh_faturado__fora_ponta`,
         (`BUSCAVALORITEM`(NF.`itensserv`,'0314010090') + `BUSCAVALORITEM`(NF.`itensserv`,'0314010091') + `BUSCAVALORITEM`(NF.`itensserv`,'0314010092') + `BUSCAVALORITEM`(NF.`itensserv`,'0314010093')) AS `Qtd_COMP. TE`,
         (`BUSCAVALORITEM`(NF.`itensserv`,'0313010090') + `BUSCAVALORITEM`(NF.`itensserv`,'0313010091') + `BUSCAVALORITEM`(NF.`itensserv`,'0313010092') + `BUSCAVALORITEM`(NF.`itensserv`,'0313010093')) AS `Qtd_COMP. TUSD`,
         (`BUSCAVALORITEM`(NF.`itensserv`,'0314020090') + `BUSCAVALORITEM`(NF.`itensserv`,'0314020091') + `BUSCAVALORITEM`(NF.`itensserv`,'0314020092') + `BUSCAVALORITEM`(NF.`itensserv`,'0314020093')) AS `Vlr_COMP. TE`,
         (`BUSCAVALORITEM`(NF.`itensserv`,'0313020090') + `BUSCAVALORITEM`(NF.`itensserv`,'0313020091') + `BUSCAVALORITEM`(NF.`itensserv`,'0313020092') + `BUSCAVALORITEM`(NF.`itensserv`,'0313020093')) AS `Vlr_COMP. TUSD`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020123'),0) AS `valor_dif dem contr`   
         FROM NF
         FORCE INDEX FOR JOIN (`keyflgst`, `keymesanoref`, `keymesano`, `keycons`, `keyzona`, `keylivro`, `keyconta`, `keyclasse`, `keydatapag`, `keygrupo`, `idx_numnf`) 
         LEFT JOIN `uc_faturamento` `uca` ON `uca`.`id` = NF.`unid_id` AND NF.`grupo` = 'A' 
         LEFT JOIN `uc_faturamento` `ucb` ON `ucb`.`id` = NF.`unid_id` AND NF.`grupo` = 'B' 
         LEFT JOIN `consum` ON `consum`.`consreg` = NF.`cons_id` 
         LEFT JOIN `classe` ON `classe`.`codigo` = NF.`codclas`; 
--- END

--- BEG # 0002
         DROP VIEW IF EXISTS `vwexporta_nfTransicao_arlindo`;
         CREATE VIEW `vwexporta_nfTransicao_arlindo` AS 
         SELECT NFTRANSICAO.mesano,  
         IF((NFTRANSICAO.`ta` = '01A400'), 'Convencional', 
         IF((NFTRANSICAO.`ta` = '01AA00'), 'Horo Sazonal Azul', 
         IF((NFTRANSICAO.`ta` = '01AV00'), 'Horo Sazonal Verde', 
         IF(((NFTRANSICAO.`ta` = '01AL00')  AND (NFTRANSICAO.`livreespecial` = 1)), 'Consumidor Livre Especial', 
         IF((NFTRANSICAO.`ta` = '01AL00'), 'Consumidor Livre', 
         IF((NFTRANSICAO.`ta` = '01AS00'), 'Suprimento', 
         IF((NFTRANSICAO.`ta` = '01B100'), 'Residencial', 
         IF((NFTRANSICAO.`ta` = '01B200'), 'Rural', 
         IF((NFTRANSICAO.`ta` = '01B400'), 'Iluminação Pública', 
         'Demais Classes'))))))))) AS `grupo_tarifario`, 
         NFTRANSICAO.`grupo` AS `grupo`, 
         NFTRANSICAO.`codclas` AS `codigo_classe`, 
         IF((NFTRANSICAO.`codclas` = 9101),'Residencial Normal', 
         IF((NFTRANSICAO.`codclas` = 9104),'Residencial Baixa Renda (246)', 
         IF((NFTRANSICAO.`codclas` = 9105),'Residencial Baixa Renda (485)', 
         IF((NFTRANSICAO.`codclas` = 9106),'Residencial Baixa Renda (694)', 
         IF((NFTRANSICAO.`codclas` BETWEEN 0 And 3999),'Industrial', 
         IF((NFTRANSICAO.`codclas` BETWEEN 4000 And 4999), 'Comercial', 
         IF((NFTRANSICAO.`codclas` BETWEEN 5000 And 5999), 'Rural', 
         IF((NFTRANSICAO.`codclas` BETWEEN 6100 And 6199), 'Poder Público Municipal',
         IF((NFTRANSICAO.`codclas` BETWEEN 6200 And 6299), 'Poder Público Estadual',
         IF((NFTRANSICAO.`codclas` BETWEEN 6300 And 6399), 'Poder Público Federal',
         IF((NFTRANSICAO.`codclas` BETWEEN 6400 And 6499), 'Iluminação Pública',
         IF((NFTRANSICAO.`codclas` BETWEEN 7200 And 7299), 'Água, Esgosto e Saneamento',
         IF(((NFTRANSICAO.`codclas` BETWEEN 7100 And 7199) AND (NFTRANSICAO.`codclas` BETWEEN 7300 AND 7999)), 'Serviço Público',
         IF((NFTRANSICAO.`codclas` BETWEEN 8000 And 8999), 'Consumo Próprio', 
         'Indefinido')))))))))))))) AS `classe_faturada`,
         `classe`.`descricao` AS `descricao_classe`,
         NFTRANSICAO.`zona` AS `zona`,
         NFTRANSICAO.`livro` AS `livro`,
         NFTRANSICAO.`conta` AS `conta`,
         NFTRANSICAO.`banco_fatur` AS `id_banco`,
         NFTRANSICAO.`banident` AS `banco`,
         `consum`.`matricula` AS `matricula`,
         `consum`.`nome` AS `nome`,
         NFTRANSICAO.`numNF` AS `numero_fatura`,
         NFTRANSICAO.`mesano` AS `mesano_fatura`,
         NFTRANSICAO.`mesano_ref` AS `mesano_referencia`,
         NFTRANSICAO.`anomes_fat` AS `anomes_fat`,
         NFTRANSICAO.`dataem` AS `data_emissao`,
         NFTRANSICAO.`dataapr` AS `data_apresentacao`,
         NFTRANSICAO.`datavenc` AS `data_vencimento`,
         NFTRANSICAO.`dataleit` AS `data_leitura_atual`,
         NFTRANSICAO.`dataleitant` AS `data_leitura_anterior`,
         NFTRANSICAO.`itensserv` AS `itensserv`,
         NFTRANSICAO.`banarrec` AS `banarrec`,
          NFTRANSICAO.`livreespecial`, 
          NFTRANSICAO.`livre_verde`, 
          NFTRANSICAO.`parcialmente_cativo`, 
          NFTRANSICAO.`flgmedfat`, 
          NFTRANSICAO.`fatura_final`, 
          NFTRANSICAO.`irrigacao_aquicultura`, 
          NFTRANSICAO.`micro_geracao_convenio_sefaz`, 
          NFTRANSICAO.`micro_geracao_distribuida`, 
          NFTRANSICAO.`tarifa_branca`, 
          NFTRANSICAO.`unid_id`, 
          NFTRANSICAO.`cons_id`, 
          NFTRANSICAO.`ta`, 
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020012') AS `valor_consumo_reativo`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020031') AS `valor_demanda_reativo`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020013') AS `valor_consumo_reativo_ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020014') AS `valor_consumo_reativo_fora_ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020027') AS `valor_demanda_reativo_ponta`, 
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020028') AS `valor_demanda_reativo_fora_ponta`,
         IF((NFTRANSICAO.`grupo` = 'B'),(((`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'030201009') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010190')) + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010390')) + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010290')),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010009')) AS `kwh_faturado`,
         IF((NFTRANSICAO.`grupo` = 'B'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'030202009'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020009')) AS `vlr_kwh_faturado`,
         IF((IF((NFTRANSICAO.`grupo` = 'A'),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010060'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010010')),0) > 0),IF((NFTRANSICAO.`grupo` = 'A'),(`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010010') - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010060')),0),0) AS `kwh_faturado_ponta`,
         IF((IF((NFTRANSICAO.`grupo` = 'A'),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020060'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020010')),0) > 0),IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020010'),0),0) AS `valor_kwh_faturado_ponta`,
         IF((IF((NFTRANSICAO.`grupo` = 'A'),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010061'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010011')),0) > 0),IF((NFTRANSICAO.`grupo` = 'A'),(`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010011') - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010061')),0),0) AS `kwh_faturado_fora_ponta`,
         IF((IF((NFTRANSICAO.`grupo` = 'A'),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020061'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020011')),0) > 0),IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020011'),0),0) AS `valor_kwh_faturado_fora_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010041'),0) AS `kwh_devolvido_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020041'),0) AS `valor_kwh_devolvido_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010042'),0) AS `kwh_devolvido_fora_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020042'),0) AS `valor_kwh_devolvido_fora_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010043'),0) AS `kwh_ultrapassado_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020043'),0) AS `valor_kwh_ultrapassado_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010044'),0) AS `kwh_ultrapassado_fora_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020044'),0) AS `valor_kwh_ultrapassado_fora_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010017'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010032')) AS `kw_faturado`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020015'),0) AS `valor_kw_faturado`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010021'),0) AS `kw_faturado_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020021'),0) AS `valor_kw_faturado_ponta`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010025'),0) AS `kw_faturado_fora_ponta`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020023'),0) AS `valor_kw_faturado_fora_ponta`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010018'),0) AS `kw_ultrapassado`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020018'),0) AS `valor_kw_ultrapassado`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010022'),0) AS `kw_ultrapassagem_ponta`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020022'),0) AS `valor_kw_ultrapassagem_ponta`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010026'),0) AS `kw_ultrapassado_fora_ponta`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020026'),0) AS `valor_kw_ultrapassado_fora_ponta`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020051'),0) AS `valor_desconto_provisorio`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020052'),0) AS `valor_desconto_realizado`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020053'),0) AS `valor_devolucao_diferenca_ccee`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020054'),0) AS `valor_cobranca_diferenca_ccee`, 
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'903000')   AS `valor_convenios`, 
         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'908000') - ((`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9080000061') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9080000066')) + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9080000067'))) AS `valor_diversos`,
         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'904000') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'905000')) AS `valor_taxados`,
         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0602020001') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0604020001')) AS `Icms_st`,
         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0602020002') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0604020002')) AS `pis_st`,
         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0602020003') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0604020003')) AS `cofins_st`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'907000') AS `valor_devolucao`,
         ((`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9080000061') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9080000066')) + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9080000067')) AS `valor_encargos_financeiros`,
         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'909000') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000088')) AS `valor_retencao`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0301000003') AS `valor_cip`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302050002') AS `valor_juros_ece`,
         0 AS `valor_diferenca_ece`,
         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302070002') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302070005')) AS `valor_ece`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020032') AS `valor_must`,
         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302030009') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302030010')) AS `valor_icms`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020029') AS `valor_pis`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020030') AS `valor_cofins`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020410') AS `valor_subvencao`,
         NFTRANSICAO.`valortot` AS `valor_total`,
         NFTRANSICAO.`valorliq` AS `valor_liquido`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010016') AS `demanda_contratada_convencional`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010020') AS `demanda_contratada_ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010024') AS `demanda_contratada_fora_ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010090') AS `qtde_fx1`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010091') AS `qtde_fx2`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010092') AS `qtde_fx3`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010093') AS `qtde_fx4`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020090') AS `vlr_fx1`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020091') AS `vlr_fx2`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020092') AS `vlr_fx3`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020093') AS `vlr_fx4`,
         IF((NFTRANSICAO.`grupo` = 'B'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'030201008'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010008')) AS `kwh_lido`,
         NFTRANSICAO.`fase` AS `fase`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302030001') AS `base_icms_1`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302030002') AS `aliquota_icms_1`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302030009') AS `valor_icms_1`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302030003') AS `base_icms_2`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302030013') AS `aliquota_icms_2`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302030010') AS `valor_icms_2`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020033') AS `base_pis`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'40PIS00001'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302990303')) AS `aliquota_pis`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020034') AS `base_cofins`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'40COF00001'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302990304')) AS `aliquota_cofins`,
         (((`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'908000') - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9080000098')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9080000092')) - ((((((((((((((((((`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'907000') - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000062')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000079')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000081')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000082')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000087')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000088')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000090')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000091')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000092')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000096')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000030')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000031')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000032')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000033')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000034')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000035')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000036')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000037'))) AS `receitas_financeiras`,
         (((((((`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000030') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000031')) + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000032')) + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000033')) + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000034')) + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000035')) + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000036')) + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000037')) AS `ressarcimento_idqual`,`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9080000098') AS `valor_encargo_uso_sis_trans`,(`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000087') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000091')) AS `desconto_must`,IF((IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010060'),0) = 0),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010010'),0),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010060'),0)) AS `encargo_kwh_faturado_ponta`,
         IF((IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020060'),0) = 0),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020010'),0),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020060'),0)) AS `encargo_valor_kwh_faturado_ponta`,
         IF((IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010061'),0) = 0),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010011'),0),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010061'),0)) AS `encargo_kwh_faturado_fora_ponta`,
         IF((IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020061'),0) = 0),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020011'),0),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020061'),0)) AS `encargo_valor_kwh_faturado_fora_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302040030'),0) AS `mwh_medio`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302040031'),0) AS `mwh_medio_horas`,
         NFTRANSICAO.`micro_geracao` AS `micro_geracao`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020994') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0305020190') +`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0306020090') AS `valor reverso`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020993') AS `valor reverso ban`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302022010') AS `reverso ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302022110') AS `reverso ponta bandeira`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302022011') AS `reverso fora de ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302022111') AS `reverso fora de ponta bandeira`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0555020777') AS `dif.rel.rec`,
         IF((NFTRANSICAO.`grupo` = 'B'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302120009'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302120009')) AS `bandeira_vlr_kwh_fat`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302120010'),0) AS `bandeira_valor_kwh_fat_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302120011'),0) AS `bandeira_valor_kwh_fat_fora_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020055'),0) AS `valor_energia_desconto_provisorio`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020056'),0) AS `valor_energia_desconto_realizado`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020057'),0) AS `valor_energia_devolucao_diferenca_ccee`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020058'),0) AS `valor_energia_cobranca_diferenca_ccee`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010010') AS `consumo_ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010011') AS `consumo_fora_ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302012011') AS `reverso_fora_ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302013011') AS `reverso_residuo_fora_ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0303010090') AS `tusd`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010080') AS `consumo_medido`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020996') AS `reverso_lido`,
          NFTRANSICAO.`flgst` AS `flgst`,
          NFTRANSICAO.`datast` AS `datast`,
          NFTRANSICAO.`datapag` AS `data_pagamento`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302022011') AS `reverso_fora_de__ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010190') AS `kwh_faturado__ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010390') AS `kwh_faturado__Intermediaria`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010290') AS `kwh_faturado__fora_ponta`,
         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0314010090') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0314010091') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0314010092') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0314010093')) AS `Qtd_COMP. TE`,
         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0313010090') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0313010091') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0313010092') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0313010093')) AS `Qtd_COMP. TUSD`,
         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0314020090') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0314020091') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0314020092') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0314020093')) AS `Vlr_COMP. TE`,
         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0313020090') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0313020091') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0313020092') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0313020093')) AS `Vlr_COMP. TUSD`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020123'),0) AS `valor_dif dem contr` 
         FROM NFTRANSICAO
         FORCE INDEX FOR JOIN (`keyflgst`, `keymesanoref`, `keymesano`, `keycons`, `keyzona`, `keylivro`, `keyconta`, `keyclasse`, `keydatapag`, `keygrupo`, `idx_numNf`) 
         LEFT JOIN `uc_faturamento` `uca` ON `uca`.`id` = NFTRANSICAO.`unid_id` AND NFTRANSICAO.`grupo` = 'A' 
         LEFT JOIN `uc_faturamento` `ucb` ON `ucb`.`id` = NFTRANSICAO.`unid_id` AND NFTRANSICAO.`grupo` = 'B' 
         LEFT JOIN `consum` ON `consum`.`consreg` = NFTRANSICAO.`cons_id` 
         LEFT JOIN `classe` ON `classe`.`codigo` = NFTRANSICAO.`codclas`; 
--- END

--- BEG # 0003

        DROP VIEW IF EXISTS `vwexporta_nf_Reserva_arlindo`;
        CREATE VIEW `vwexporta_nf_Reserva_arlindo` AS 
         SELECT NF.mesano,  
         IF((NF.`ta` = '01A400'), 'Convencional', 
         IF((NF.`ta` = '01AA00'), 'Horo Sazonal Azul', 
         IF((NF.`ta` = '01AV00'), 'Horo Sazonal Verde', 
         IF(((NF.`ta` = '01AL00')  AND (NF.`livreespecial` = 1)), 'Consumidor Livre Especial', 
         IF((NF.`ta` = '01AL00'), 'Consumidor Livre', 
         IF((NF.`ta` = '01AS00'), 'Suprimento', 
         IF((NF.`ta` = '01B100'), 'Residencial', 
         IF((NF.`ta` = '01B200'), 'Rural', 
         IF((NF.`ta` = '01B400'), 'Iluminação Pública', 
         'Demais Classes'))))))))) AS `grupo_tarifario`, 
         NF.`grupo` AS `grupo`, 
         NF.`codclas` AS `codigo_classe`, 
         IF((NF.`codclas` = 9101),'Residencial Normal', 
         IF((NF.`codclas` = 9104),'Residencial Baixa Renda (246)', 
         IF((NF.`codclas` = 9105),'Residencial Baixa Renda (485)', 
         IF((NF.`codclas` = 9106),'Residencial Baixa Renda (694)', 
         IF((NF.`codclas` BETWEEN 0 And 3999),'Industrial', 
         IF((NF.`codclas` BETWEEN 4000 And 4999), 'Comercial', 
         IF((NF.`codclas` BETWEEN 5000 And 5999), 'Rural', 
         IF((NF.`codclas` BETWEEN 6100 And 6199), 'Poder Público Municipal',
         IF((NF.`codclas` BETWEEN 6200 And 6299), 'Poder Público Estadual',
         IF((NF.`codclas` BETWEEN 6300 And 6399), 'Poder Público Federal',
         IF((NF.`codclas` BETWEEN 6400 And 6499), 'Iluminação Pública',
         IF((NF.`codclas` BETWEEN 7200 And 7299), 'Água, Esgosto e Saneamento',
         IF(((NF.`codclas` BETWEEN 7100 And 7199) AND (NF.`codclas` BETWEEN 7300 AND 7999)), 'Serviço Público',
         IF((NF.`codclas` BETWEEN 8000 And 8999), 'Consumo Próprio', 
         'Indefinido')))))))))))))) AS `classe_faturada`,
         `classe`.`descricao` AS `descricao_classe`,
         NF.`zona` AS `zona`,
         NF.`livro` AS `livro`,
         NF.`conta` AS `conta`,
         NF.`banco_fatur` AS `id_banco`,
         NF.`banident` AS `banco`,
         `consum`.`matricula` AS `matricula`,
         `consum`.`nome` AS `nome`,
         NF.`numnf` AS `numero_fatura`,
         NF.`mesano` AS `mesano_fatura`,
         NF.`mesano_ref` AS `mesano_referencia`,
         NF.`anomes_fat` AS `anomes_fat`,
         NF.`dataem` AS `data_emissao`,
         NF.`dataapr` AS `data_apresentacao`,
         NF.`datavenc` AS `data_vencimento`,
         NF.`dataleit` AS `data_leitura_atual`,
         NF.`dataleitant` AS `data_leitura_anterior`,
         NF.`itensserv` AS `itensserv`,
         NF.`banarrec` AS `banarrec`,
          NF.`livreespecial`, 
          NF.`livre_verde`, 
          NF.`parcialmente_cativo`, 
          NF.`flgmedfat`, 
          NF.`fatura_final`, 
          NF.`irrigacao_aquicultura`, 
          NF.`micro_geracao_convenio_sefaz`, 
          NF.`micro_geracao_distribuida`, 
          NF.`tarifa_branca`, 
          NF.`unid_id`, 
          NF.`cons_id`, 
          NF.`ta`, 
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020012') AS `valor_consumo_reativo`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020031') AS `valor_demanda_reativo`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020013') AS `valor_consumo_reativo_ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020014') AS `valor_consumo_reativo_fora_ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020027') AS `valor_demanda_reativo_ponta`, 
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020028') AS `valor_demanda_reativo_fora_ponta`,
         IF((NF.`grupo` = 'B'),(((`BUSCAVALORITEM`(NF.`itensserv`,'030201009') + `BUSCAVALORITEM`(NF.`itensserv`,'0302010190')) + `BUSCAVALORITEM`(NF.`itensserv`,'0302010390')) + `BUSCAVALORITEM`(NF.`itensserv`,'0302010290')),`BUSCAVALORITEM`(NF.`itensserv`,'0302010009')) AS `kwh_faturado`,
         IF((NF.`grupo` = 'B'),`BUSCAVALORITEM`(NF.`itensserv`,'030202009'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020009')) AS `vlr_kwh_faturado`,
         IF((IF((NF.`grupo` = 'A'),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010060'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010010')),0) > 0),IF((NF.`grupo` = 'A'),(`BUSCAVALORITEM`(NF.`itensserv`,'0302010010') - `BUSCAVALORITEM`(NF.`itensserv`,'0302010060')),0),0) AS `kwh_faturado_ponta`,
         IF((IF((NF.`grupo` = 'A'),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020060'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020010')),0) > 0),IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020010'),0),0) AS `valor_kwh_faturado_ponta`,
         IF((IF((NF.`grupo` = 'A'),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010061'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010011')),0) > 0),IF((NF.`grupo` = 'A'),(`BUSCAVALORITEM`(NF.`itensserv`,'0302010011') - `BUSCAVALORITEM`(NF.`itensserv`,'0302010061')),0),0) AS `kwh_faturado_fora_ponta`,
         IF((IF((NF.`grupo` = 'A'),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020061'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020011')),0) > 0),IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020011'),0),0) AS `valor_kwh_faturado_fora_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010041'),0) AS `kwh_devolvido_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020041'),0) AS `valor_kwh_devolvido_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010042'),0) AS `kwh_devolvido_fora_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020042'),0) AS `valor_kwh_devolvido_fora_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010043'),0) AS `kwh_ultrapassado_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020043'),0) AS `valor_kwh_ultrapassado_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010044'),0) AS `kwh_ultrapassado_fora_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020044'),0) AS `valor_kwh_ultrapassado_fora_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010017'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010032')) AS `kw_faturado`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020015'),0) AS `valor_kw_faturado`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010021'),0) AS `kw_faturado_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020021'),0) AS `valor_kw_faturado_ponta`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010025'),0) AS `kw_faturado_fora_ponta`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020023'),0) AS `valor_kw_faturado_fora_ponta`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010018'),0) AS `kw_ultrapassado`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020018'),0) AS `valor_kw_ultrapassado`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010022'),0) AS `kw_ultrapassagem_ponta`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020022'),0) AS `valor_kw_ultrapassagem_ponta`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010026'),0) AS `kw_ultrapassado_fora_ponta`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020026'),0) AS `valor_kw_ultrapassado_fora_ponta`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020051'),0) AS `valor_desconto_provisorio`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020052'),0) AS `valor_desconto_realizado`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020053'),0) AS `valor_devolucao_diferenca_ccee`, 
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020054'),0) AS `valor_cobranca_diferenca_ccee`, 
         `BUSCAVALORITEM`(NF.`itensserv`,'903000')   AS `valor_convenios`, 
         (`BUSCAVALORITEM`(NF.`itensserv`,'908000') - ((`BUSCAVALORITEM`(NF.`itensserv`,'9080000061') + `BUSCAVALORITEM`(NF.`itensserv`,'9080000066')) + `BUSCAVALORITEM`(NF.`itensserv`,'9080000067'))) AS `valor_diversos`,
         (`BUSCAVALORITEM`(NF.`itensserv`,'904000') + `BUSCAVALORITEM`(NF.`itensserv`,'905000')) AS `valor_taxados`,
         (`BUSCAVALORITEM`(NF.`itensserv`,'0602020001') + `BUSCAVALORITEM`(NF.`itensserv`,'0604020001')) AS `Icms_st`,
         (`BUSCAVALORITEM`(NF.`itensserv`,'0602020002') + `BUSCAVALORITEM`(NF.`itensserv`,'0604020002')) AS `pis_st`,
         (`BUSCAVALORITEM`(NF.`itensserv`,'0602020003') + `BUSCAVALORITEM`(NF.`itensserv`,'0604020003')) AS `cofins_st`,
         `BUSCAVALORITEM`(NF.`itensserv`,'907000') AS `valor_devolucao`,
         ((`BUSCAVALORITEM`(NF.`itensserv`,'9080000061') + `BUSCAVALORITEM`(NF.`itensserv`,'9080000066')) + `BUSCAVALORITEM`(NF.`itensserv`,'9080000067')) AS `valor_encargos_financeiros`,
         (`BUSCAVALORITEM`(NF.`itensserv`,'909000') + `BUSCAVALORITEM`(NF.`itensserv`,'9070000088')) AS `valor_retencao`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0301000003') AS `valor_cip`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302050002') AS `valor_juros_ece`,
         0 AS `valor_diferenca_ece`,
         (`BUSCAVALORITEM`(NF.`itensserv`,'0302070002') + `BUSCAVALORITEM`(NF.`itensserv`,'0302070005')) AS `valor_ece`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020032') AS `valor_must`,
         (`BUSCAVALORITEM`(NF.`itensserv`,'0302030009') + `BUSCAVALORITEM`(NF.`itensserv`,'0302030010')) AS `valor_icms`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020029') AS `valor_pis`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020030') AS `valor_cofins`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020410') AS `valor_subvencao`,
         NF.`valortot` AS `valor_total`,
         NF.`valorliq` AS `valor_liquido`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010016') AS `demanda_contratada_convencional`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010020') AS `demanda_contratada_ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010024') AS `demanda_contratada_fora_ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010090') AS `qtde_fx1`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010091') AS `qtde_fx2`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010092') AS `qtde_fx3`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010093') AS `qtde_fx4`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020090') AS `vlr_fx1`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020091') AS `vlr_fx2`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020092') AS `vlr_fx3`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020093') AS `vlr_fx4`,
         IF((NF.`grupo` = 'B'),`BUSCAVALORITEM`(NF.`itensserv`,'030201008'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010008')) AS `kwh_lido`,
         NF.`fase` AS `fase`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302030001') AS `base_icms_1`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302030002') AS `aliquota_icms_1`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302030009') AS `valor_icms_1`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302030003') AS `base_icms_2`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302030013') AS `aliquota_icms_2`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302030010') AS `valor_icms_2`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020033') AS `base_pis`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'40PIS00001'),`BUSCAVALORITEM`(NF.`itensserv`,'0302990303')) AS `aliquota_pis`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020034') AS `base_cofins`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'40COF00001'),`BUSCAVALORITEM`(NF.`itensserv`,'0302990304')) AS `aliquota_cofins`,
         (((`BUSCAVALORITEM`(NF.`itensserv`,'908000') - `BUSCAVALORITEM`(NF.`itensserv`,'9080000098')) - `BUSCAVALORITEM`(NF.`itensserv`,'9080000092')) - ((((((((((((((((((`BUSCAVALORITEM`(NF.`itensserv`,'907000') - `BUSCAVALORITEM`(NF.`itensserv`,'9070000062')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000079')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000081')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000082')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000087')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000088')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000090')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000091')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000092')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000096')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000030')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000031')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000032')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000033')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000034')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000035')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000036')) - `BUSCAVALORITEM`(NF.`itensserv`,'9070000037'))) AS `receitas_financeiras`,
         (((((((`BUSCAVALORITEM`(NF.`itensserv`,'9070000030') + `BUSCAVALORITEM`(NF.`itensserv`,'9070000031')) + `BUSCAVALORITEM`(NF.`itensserv`,'9070000032')) + `BUSCAVALORITEM`(NF.`itensserv`,'9070000033')) + `BUSCAVALORITEM`(NF.`itensserv`,'9070000034')) + `BUSCAVALORITEM`(NF.`itensserv`,'9070000035')) + `BUSCAVALORITEM`(NF.`itensserv`,'9070000036')) + `BUSCAVALORITEM`(NF.`itensserv`,'9070000037')) AS `ressarcimento_idqual`,`BUSCAVALORITEM`(NF.`itensserv`,'9080000098') AS `valor_encargo_uso_sis_trans`,(`BUSCAVALORITEM`(NF.`itensserv`,'9070000087') + `BUSCAVALORITEM`(NF.`itensserv`,'9070000091')) AS `desconto_must`,IF((IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010060'),0) = 0),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010010'),0),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010060'),0)) AS `encargo_kwh_faturado_ponta`,
         IF((IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020060'),0) = 0),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020010'),0),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020060'),0)) AS `encargo_valor_kwh_faturado_ponta`,
         IF((IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010061'),0) = 0),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010011'),0),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302010061'),0)) AS `encargo_kwh_faturado_fora_ponta`,
         IF((IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020061'),0) = 0),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020011'),0),IF((NF.`ta` = '01AL00'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020061'),0)) AS `encargo_valor_kwh_faturado_fora_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302040030'),0) AS `mwh_medio`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302040031'),0) AS `mwh_medio_horas`,
         NF.`micro_geracao` AS `micro_geracao`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020994') + `BUSCAVALORITEM`(NF.`itensserv`,'0305020190') +`BUSCAVALORITEM`(NF.`itensserv`,'0306020090') AS `valor reverso`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020993') AS `valor reverso ban`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302022010') AS `reverso ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302022110') AS `reverso ponta bandeira`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302022011') AS `reverso fora de ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302022111') AS `reverso fora de ponta bandeira`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0555020777') AS `dif.rel.rec`,
         IF((NF.`grupo` = 'B'),`BUSCAVALORITEM`(NF.`itensserv`,'0302120009'),`BUSCAVALORITEM`(NF.`itensserv`,'0302120009')) AS `bandeira_vlr_kwh_fat`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302120010'),0) AS `bandeira_valor_kwh_fat_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302120011'),0) AS `bandeira_valor_kwh_fat_fora_ponta`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020055'),0) AS `valor_energia_desconto_provisorio`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020056'),0) AS `valor_energia_desconto_realizado`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020057'),0) AS `valor_energia_devolucao_diferenca_ccee`,
         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020058'),0) AS `valor_energia_cobranca_diferenca_ccee`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010010') AS `consumo_ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010011') AS `consumo_fora_ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302012011') AS `reverso_fora_ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302013011') AS `reverso_residuo_fora_ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0303010090') AS `tusd`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010080') AS `consumo_medido`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302020996') AS `reverso_lido`,
         NF.`flgst` AS `flgst`,
         NF.`datast` AS `datast`,
         NF.`datapag` AS `data_pagamento`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302022011') AS `reverso_fora_de__ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010190') AS `kwh_faturado__ponta`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010390') AS `kwh_faturado__Intermediaria`,
         `BUSCAVALORITEM`(NF.`itensserv`,'0302010290') AS `kwh_faturado__fora_ponta`,

         (`BUSCAVALORITEM`(NF.`itensserv`,'0314010090') + `BUSCAVALORITEM`(NF.`itensserv`,'0314010091') + `BUSCAVALORITEM`(NF.`itensserv`,'0314010092') + `BUSCAVALORITEM`(NF.`itensserv`,'0314010093')) AS `Qtd_COMP. TE`,
         (`BUSCAVALORITEM`(NF.`itensserv`,'0313010090') + `BUSCAVALORITEM`(NF.`itensserv`,'0313010091') + `BUSCAVALORITEM`(NF.`itensserv`,'0313010092') + `BUSCAVALORITEM`(NF.`itensserv`,'0313010093')) AS `Qtd_COMP. TUSD`,

         (`BUSCAVALORITEM`(NF.`itensserv`,'0314020090') + `BUSCAVALORITEM`(NF.`itensserv`,'0314020091') + `BUSCAVALORITEM`(NF.`itensserv`,'0314020092') + `BUSCAVALORITEM`(NF.`itensserv`,'0314020093')) AS `Vlr_COMP. TE`,
         (`BUSCAVALORITEM`(NF.`itensserv`,'0313020090') + `BUSCAVALORITEM`(NF.`itensserv`,'0313020091') + `BUSCAVALORITEM`(NF.`itensserv`,'0313020092') + `BUSCAVALORITEM`(NF.`itensserv`,'0313020093')) AS `Vlr_COMP. TUSD`,

         IF((NF.`grupo` = 'A'),`BUSCAVALORITEM`(NF.`itensserv`,'0302020123'),0) AS `valor_dif dem contr`, 
         `reserva`.`numnf`   caderno, 
         `reserva`.`mesano`  reserva_mesano, 
         `reserva`.`datares` reserva_datares 
       
         FROM NF 
         FORCE INDEX FOR JOIN (`keyflgst`, `keymesanoref`, `keymesano`, `keycons`, `keyzona`, `keylivro`, `keyconta`, `keyclasse`, `keydatapag`, `keygrupo`, `idx_numnf`) 
         LEFT JOIN `uc_faturamento` `uca` ON `uca`.`id` = NF.`unid_id` AND NF.`grupo` = 'A' 
         LEFT JOIN `uc_faturamento` `ucb` ON `ucb`.`id` = NF.`unid_id` AND NF.`grupo` = 'B' 
         LEFT JOIN `consum` ON `consum`.`consreg` = NF.`cons_id` 
         LEFT JOIN `classe` ON `classe`.`codigo` = NF.`codclas` 
         LEFT JOIN `reserva` ON `reserva`.`numnf` = NF.`numnf` AND reserva.`mesano` = NF.`mesano`
        
--- END

--- BEG # 0004

         DROP VIEW IF EXISTS `vwexporta_nfTransicao_Reserva_arlindo`;
         CREATE VIEW `vwexporta_nfTransicao_Reserva_arlindo` AS 
         SELECT NFTRANSICAO.mesano,  
         IF((NFTRANSICAO.`ta` = '01A400'), 'Convencional', 
         IF((NFTRANSICAO.`ta` = '01AA00'), 'Horo Sazonal Azul', 
         IF((NFTRANSICAO.`ta` = '01AV00'), 'Horo Sazonal Verde', 
         IF(((NFTRANSICAO.`ta` = '01AL00')  AND (NFTRANSICAO.`livreespecial` = 1)), 'Consumidor Livre Especial', 
         IF((NFTRANSICAO.`ta` = '01AL00'), 'Consumidor Livre', 
         IF((NFTRANSICAO.`ta` = '01AS00'), 'Suprimento', 
         IF((NFTRANSICAO.`ta` = '01B100'), 'Residencial', 
         IF((NFTRANSICAO.`ta` = '01B200'), 'Rural', 
         IF((NFTRANSICAO.`ta` = '01B400'), 'Iluminação Pública', 
         'Demais Classes'))))))))) AS `grupo_tarifario`, 
         NFTRANSICAO.`grupo` AS `grupo`, 
         NFTRANSICAO.`codclas` AS `codigo_classe`, 
         IF((NFTRANSICAO.`codclas` = 9101),'Residencial Normal', 
         IF((NFTRANSICAO.`codclas` = 9104),'Residencial Baixa Renda (246)', 
         IF((NFTRANSICAO.`codclas` = 9105),'Residencial Baixa Renda (485)', 
         IF((NFTRANSICAO.`codclas` = 9106),'Residencial Baixa Renda (694)', 
         IF((NFTRANSICAO.`codclas` BETWEEN 0 And 3999),'Industrial', 
         IF((NFTRANSICAO.`codclas` BETWEEN 4000 And 4999), 'Comercial', 
         IF((NFTRANSICAO.`codclas` BETWEEN 5000 And 5999), 'Rural', 
         IF((NFTRANSICAO.`codclas` BETWEEN 6100 And 6199), 'Poder Público Municipal',
         IF((NFTRANSICAO.`codclas` BETWEEN 6200 And 6299), 'Poder Público Estadual',
         IF((NFTRANSICAO.`codclas` BETWEEN 6300 And 6399), 'Poder Público Federal',
         IF((NFTRANSICAO.`codclas` BETWEEN 6400 And 6499), 'Iluminação Pública',
         IF((NFTRANSICAO.`codclas` BETWEEN 7200 And 7299), 'Água, Esgosto e Saneamento',
         IF(((NFTRANSICAO.`codclas` BETWEEN 7100 And 7199) AND (NFTRANSICAO.`codclas` BETWEEN 7300 AND 7999)), 'Serviço Público',
         IF((NFTRANSICAO.`codclas` BETWEEN 8000 And 8999), 'Consumo Próprio', 
         'Indefinido')))))))))))))) AS `classe_faturada`,
         `classe`.`descricao` AS `descricao_classe`,
         NFTRANSICAO.`zona` AS `zona`,
         NFTRANSICAO.`livro` AS `livro`,
         NFTRANSICAO.`conta` AS `conta`,
         NFTRANSICAO.`banco_fatur` AS `id_banco`,
         NFTRANSICAO.`banident` AS `banco`,
         `consum`.`matricula` AS `matricula`,
         `consum`.`nome` AS `nome`,
         NFTRANSICAO.`numNF` AS `numero_fatura`,
         NFTRANSICAO.`mesano` AS `mesano_fatura`,
         NFTRANSICAO.`mesano_ref` AS `mesano_referencia`,
         NFTRANSICAO.`anomes_fat` AS `anomes_fat`,
         NFTRANSICAO.`dataem` AS `data_emissao`,
         NFTRANSICAO.`dataapr` AS `data_apresentacao`,
         NFTRANSICAO.`datavenc` AS `data_vencimento`,
         NFTRANSICAO.`dataleit` AS `data_leitura_atual`,
         NFTRANSICAO.`dataleitant` AS `data_leitura_anterior`,
         NFTRANSICAO.`itensserv` AS `itensserv`,
         NFTRANSICAO.`banarrec` AS `banarrec`,
          NFTRANSICAO.`livreespecial`, 
          NFTRANSICAO.`livre_verde`, 
          NFTRANSICAO.`parcialmente_cativo`, 
          NFTRANSICAO.`flgmedfat`, 
          NFTRANSICAO.`fatura_final`, 
          NFTRANSICAO.`irrigacao_aquicultura`, 
          NFTRANSICAO.`micro_geracao_convenio_sefaz`, 
          NFTRANSICAO.`micro_geracao_distribuida`, 
          NFTRANSICAO.`tarifa_branca`, 
          NFTRANSICAO.`unid_id`, 
          NFTRANSICAO.`cons_id`, 
          NFTRANSICAO.`ta`, 
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020012') AS `valor_consumo_reativo`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020031') AS `valor_demanda_reativo`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020013') AS `valor_consumo_reativo_ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020014') AS `valor_consumo_reativo_fora_ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020027') AS `valor_demanda_reativo_ponta`, 
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020028') AS `valor_demanda_reativo_fora_ponta`,
         IF((NFTRANSICAO.`grupo` = 'B'),(((`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'030201009') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010190')) + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010390')) + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010290')),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010009')) AS `kwh_faturado`,
         IF((NFTRANSICAO.`grupo` = 'B'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'030202009'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020009')) AS `vlr_kwh_faturado`,
         IF((IF((NFTRANSICAO.`grupo` = 'A'),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010060'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010010')),0) > 0),IF((NFTRANSICAO.`grupo` = 'A'),(`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010010') - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010060')),0),0) AS `kwh_faturado_ponta`,
         IF((IF((NFTRANSICAO.`grupo` = 'A'),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020060'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020010')),0) > 0),IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020010'),0),0) AS `valor_kwh_faturado_ponta`,
         IF((IF((NFTRANSICAO.`grupo` = 'A'),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010061'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010011')),0) > 0),IF((NFTRANSICAO.`grupo` = 'A'),(`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010011') - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010061')),0),0) AS `kwh_faturado_fora_ponta`,
         IF((IF((NFTRANSICAO.`grupo` = 'A'),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020061'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020011')),0) > 0),IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020011'),0),0) AS `valor_kwh_faturado_fora_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010041'),0) AS `kwh_devolvido_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020041'),0) AS `valor_kwh_devolvido_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010042'),0) AS `kwh_devolvido_fora_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020042'),0) AS `valor_kwh_devolvido_fora_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010043'),0) AS `kwh_ultrapassado_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020043'),0) AS `valor_kwh_ultrapassado_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010044'),0) AS `kwh_ultrapassado_fora_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020044'),0) AS `valor_kwh_ultrapassado_fora_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010017'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010032')) AS `kw_faturado`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020015'),0) AS `valor_kw_faturado`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010021'),0) AS `kw_faturado_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020021'),0) AS `valor_kw_faturado_ponta`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010025'),0) AS `kw_faturado_fora_ponta`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020023'),0) AS `valor_kw_faturado_fora_ponta`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010018'),0) AS `kw_ultrapassado`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020018'),0) AS `valor_kw_ultrapassado`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010022'),0) AS `kw_ultrapassagem_ponta`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020022'),0) AS `valor_kw_ultrapassagem_ponta`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010026'),0) AS `kw_ultrapassado_fora_ponta`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020026'),0) AS `valor_kw_ultrapassado_fora_ponta`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020051'),0) AS `valor_desconto_provisorio`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020052'),0) AS `valor_desconto_realizado`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020053'),0) AS `valor_devolucao_diferenca_ccee`, 
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020054'),0) AS `valor_cobranca_diferenca_ccee`, 
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'903000')   AS `valor_convenios`, 
         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'908000') - ((`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9080000061') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9080000066')) + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9080000067'))) AS `valor_diversos`,
         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'904000') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'905000')) AS `valor_taxados`,
         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0602020001') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0604020001')) AS `Icms_st`,
         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0602020002') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0604020002')) AS `pis_st`,
         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0602020003') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0604020003')) AS `cofins_st`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'907000') AS `valor_devolucao`,
         ((`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9080000061') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9080000066')) + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9080000067')) AS `valor_encargos_financeiros`,
         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'909000') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000088')) AS `valor_retencao`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0301000003') AS `valor_cip`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302050002') AS `valor_juros_ece`,
         0 AS `valor_diferenca_ece`,
         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302070002') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302070005')) AS `valor_ece`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020032') AS `valor_must`,
         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302030009') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302030010')) AS `valor_icms`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020029') AS `valor_pis`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020030') AS `valor_cofins`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020410') AS `valor_subvencao`,
         NFTRANSICAO.`valortot` AS `valor_total`,
         NFTRANSICAO.`valorliq` AS `valor_liquido`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010016') AS `demanda_contratada_convencional`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010020') AS `demanda_contratada_ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010024') AS `demanda_contratada_fora_ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010090') AS `qtde_fx1`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010091') AS `qtde_fx2`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010092') AS `qtde_fx3`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010093') AS `qtde_fx4`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020090') AS `vlr_fx1`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020091') AS `vlr_fx2`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020092') AS `vlr_fx3`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020093') AS `vlr_fx4`,
         IF((NFTRANSICAO.`grupo` = 'B'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'030201008'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010008')) AS `kwh_lido`,
         NFTRANSICAO.`fase` AS `fase`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302030001') AS `base_icms_1`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302030002') AS `aliquota_icms_1`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302030009') AS `valor_icms_1`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302030003') AS `base_icms_2`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302030013') AS `aliquota_icms_2`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302030010') AS `valor_icms_2`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020033') AS `base_pis`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'40PIS00001'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302990303')) AS `aliquota_pis`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020034') AS `base_cofins`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'40COF00001'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302990304')) AS `aliquota_cofins`,
         (((`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'908000') - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9080000098')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9080000092')) - ((((((((((((((((((`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'907000') - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000062')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000079')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000081')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000082')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000087')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000088')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000090')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000091')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000092')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000096')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000030')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000031')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000032')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000033')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000034')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000035')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000036')) - `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000037'))) AS `receitas_financeiras`,
         (((((((`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000030') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000031')) + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000032')) + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000033')) + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000034')) + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000035')) + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000036')) + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000037')) AS `ressarcimento_idqual`,`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9080000098') AS `valor_encargo_uso_sis_trans`,(`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000087') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'9070000091')) AS `desconto_must`,IF((IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010060'),0) = 0),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010010'),0),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010060'),0)) AS `encargo_kwh_faturado_ponta`,
         IF((IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020060'),0) = 0),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020010'),0),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020060'),0)) AS `encargo_valor_kwh_faturado_ponta`,
         IF((IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010061'),0) = 0),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010011'),0),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010061'),0)) AS `encargo_kwh_faturado_fora_ponta`,
         IF((IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020061'),0) = 0),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020011'),0),IF((NFTRANSICAO.`ta` = '01AL00'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020061'),0)) AS `encargo_valor_kwh_faturado_fora_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302040030'),0) AS `mwh_medio`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302040031'),0) AS `mwh_medio_horas`,
         NFTRANSICAO.`micro_geracao` AS `micro_geracao`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020994') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0305020190') +`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0306020090') AS `valor reverso`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020993') AS `valor reverso ban`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302022010') AS `reverso ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302022110') AS `reverso ponta bandeira`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302022011') AS `reverso fora de ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302022111') AS `reverso fora de ponta bandeira`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0555020777') AS `dif.rel.rec`,
         IF((NFTRANSICAO.`grupo` = 'B'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302120009'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302120009')) AS `bandeira_vlr_kwh_fat`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302120010'),0) AS `bandeira_valor_kwh_fat_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302120011'),0) AS `bandeira_valor_kwh_fat_fora_ponta`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020055'),0) AS `valor_energia_desconto_provisorio`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020056'),0) AS `valor_energia_desconto_realizado`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020057'),0) AS `valor_energia_devolucao_diferenca_ccee`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020058'),0) AS `valor_energia_cobranca_diferenca_ccee`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010010') AS `consumo_ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010011') AS `consumo_fora_ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302012011') AS `reverso_fora_ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302013011') AS `reverso_residuo_fora_ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0303010090') AS `tusd`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010080') AS `consumo_medido`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020996') AS `reverso_lido`,
         NFTRANSICAO.`flgst` AS `flgst`,
         NFTRANSICAO.`datast` AS `datast`,
         NFTRANSICAO.`datapag` AS `data_pagamento`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302022011') AS `reverso_fora_de__ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010190') AS `kwh_faturado__ponta`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010390') AS `kwh_faturado__Intermediaria`,
         `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302010290') AS `kwh_faturado__fora_ponta`,
         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0314010090') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0314010091') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0314010092') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0314010093')) AS `Qtd_COMP. TE`,
         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0313010090') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0313010091') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0313010092') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0313010093')) AS `Qtd_COMP. TUSD`,

         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0314020090') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0314020091') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0314020092') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0314020093')) AS `Vlr_COMP. TE`,
         (`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0313020090') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0313020091') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0313020092') + `BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0313020093')) AS `Vlr_COMP. TUSD`,
         IF((NFTRANSICAO.`grupo` = 'A'),`BUSCAVALORITEM`(NFTRANSICAO.`itensserv`,'0302020123'),0) AS `valor_dif dem contr`, 
                   `reserva`.`numnf`   caderno, 
                   `reserva`.`mesano`  reserva_mesano, 
                   `reserva`.`datares` reserva_datares 
   
         FROM NFTRANSICAO 
         FORCE INDEX FOR JOIN (`keyflgst`, `keymesanoref`, `keymesano`, `keycons`, `keyzona`, `keylivro`, `keyconta`, `keyclasse`, `keydatapag`, `keygrupo`, `idx_numNF`) 
         LEFT JOIN `uc_faturamento` `uca` ON `uca`.`id` = NFTRANSICAO.`unid_id` AND NFTRANSICAO.`grupo` = 'A' 
         LEFT JOIN `uc_faturamento` `ucb` ON `ucb`.`id` = NFTRANSICAO.`unid_id` AND NFTRANSICAO.`grupo` = 'B' 
         LEFT JOIN `consum` ON `consum`.`consreg` = NFTRANSICAO.`cons_id` 
         LEFT JOIN `classe` ON `classe`.`codigo` = NFTRANSICAO.`codclas` 
         LEFT JOIN `reserva` ON `reserva`.`numnf` = NFTRANSICAO.`numnf` AND reserva.`mesano` = NFTRANSICAO.`mesano`
        
--- END

