CREATE OR REPLACE VIEW public.view_dashboard_impostos
 AS
SELECT filial,
    ano_mes,
    tipo,
    sum(icms) AS icms,
    sum(ipi) AS ipi,
    sum(iss) AS iss,
    sum(pis) AS pis,
    sum(cofins) AS cofins,
    sum(icms_subst) AS icms_subst,
    sum(importacao) AS importacao,
    sum(difal) AS difal
   FROM ( SELECT bas_filial.codigo AS filial,
            tabaux.ano_mes::integer AS ano_mes,
            '1 - Saida'::text AS tipo,
            COALESCE(sum(nfs_nota_fiscal.vlr_icms * (-1)), 0.000000) AS icms,
            COALESCE(sum(nfs_nota_fiscal.vlr_ipi * (-1)), 0.000000) AS ipi,
            COALESCE(sum(nfs_nota_fiscal.vlr_iss * (-1)), 0.000000) AS iss,
            COALESCE(sum(nfs_nota_fiscal.vlr_pis * (-1)), 0.000000) AS pis,
            COALESCE(sum(nfs_nota_fiscal.valor_cofins * (-1)), 0.000000) AS cofins,
            COALESCE(sum(nfs_nota_fiscal.vlr_icms_substituicao * (-1)), 0.000000) AS icms_subst,
            COALESCE(sum(nfs_nota_fiscal.total_imp_import * (-1)), 0.000000) AS importacao,
            COALESCE(sum((nfs_nota_fiscal.valor_total_icms_destino + nfs_nota_fiscal.valor_total_icms_origem) * (-1)), 0.000000) AS difal
			
           FROM ( SELECT to_char(CURRENT_DATE - '1 mon'::interval month * generate_series(0, 11)::double precision, 'YYYYmm'::text) AS ano_mes) tabaux
             CROSS JOIN bas_filial
             LEFT JOIN nfs_nota_fiscal ON to_char(nfs_nota_fiscal.data_emissao::timestamp with time zone, 'YYYYmm'::text)::integer = tabaux.ano_mes::integer AND nfs_nota_fiscal.filial_nota = bas_filial.codigo AND nfs_nota_fiscal.cancelada::text = 'N'::text
             LEFT JOIN nfs_op_fiscal ON nfs_op_fiscal.cfop = nfs_nota_fiscal.cfop
		   WHERE (nfs_op_fiscal.entrada_saida = 'S' OR nfs_op_fiscal.entrada_saida IS NULL)
          GROUP BY bas_filial.codigo, tabaux.ano_mes
        UNION ALL
         SELECT bas_filial.codigo AS filial,
            tabaux.ano_mes::integer AS ano_mes,
            '1 - Saida'::text AS tipo,
            COALESCE(sum(ent_nota_fiscal.icm_informado * (-1)), 0.000000) AS icms,
            COALESCE(sum(ent_nota_fiscal.total_ipi * (-1)), 0.000000) AS ipi,
            COALESCE(sum(ent_nota_fiscal.vlr_iss * (-1)), 0.000000) AS iss,
            COALESCE(sum(ent_nota_fiscal.vlr_pis * (-1)), 0.000000) AS pis,
            COALESCE(sum(ent_nota_fiscal.valor_cofins * (-1)), 0.000000) AS cofins,
            COALESCE(sum(ent_nota_fiscal.vlr_icm_subst * (-1)), 0.000000) AS icms_subst,
            COALESCE(sum(ent_nota_fiscal.total_imp_import * (-1)), 0.000000) AS importacao,
            COALESCE(sum(0 * (-1)), 0.000000) AS difal
           FROM ( SELECT to_char(CURRENT_DATE - '1 mon'::interval month * generate_series(0, 11)::double precision, 'YYYYmm'::text) AS ano_mes) tabaux
             CROSS JOIN bas_filial
             LEFT JOIN ent_nota_fiscal ON to_char(ent_nota_fiscal.data_entrega::timestamp with time zone, 'YYYYmm'::text)::integer = tabaux.ano_mes::integer AND ent_nota_fiscal.tipo_duplicata = 1 AND ent_nota_fiscal.modelo::text <> '0'::text AND ent_nota_fiscal.filial_nota = bas_filial.codigo
             LEFT JOIN nfs_op_fiscal ON nfs_op_fiscal.cfop = ent_nota_fiscal.cfop
		   WHERE (nfs_op_fiscal.entrada_saida = 'S' OR nfs_op_fiscal.entrada_saida IS NULL)
          GROUP BY bas_filial.codigo, tabaux.ano_mes
        UNION ALL
         SELECT bas_filial.codigo AS filial,
            tabaux.ano_mes::integer AS ano_mes,
            '2 - Entrada'::text AS tipo,
            COALESCE(sum(ent_nota_fiscal.icm_informado), 0.000000) AS icms,
            COALESCE(sum(ent_nota_fiscal.total_ipi), 0.000000) AS ipi,
            COALESCE(sum(ent_nota_fiscal.vlr_iss), 0.000000) AS iss,
            COALESCE(sum(ent_nota_fiscal.vlr_pis), 0.000000) AS pis,
            COALESCE(sum(ent_nota_fiscal.valor_cofins), 0.000000) AS cofins,
            COALESCE(sum(ent_nota_fiscal.vlr_icm_subst), 0.000000) AS icms_subst,
            COALESCE(sum(ent_nota_fiscal.total_imp_import), 0.000000) AS importacao,
            COALESCE(sum(0), 0.000000) AS difal
           FROM ( SELECT to_char(CURRENT_DATE - '1 mon'::interval month * generate_series(0, 11)::double precision, 'YYYYmm'::text) AS ano_mes) tabaux
             CROSS JOIN bas_filial
             LEFT JOIN ent_nota_fiscal ON to_char(ent_nota_fiscal.data_entrega::timestamp with time zone, 'YYYYmm'::text)::integer = tabaux.ano_mes::integer AND ent_nota_fiscal.tipo_duplicata = 1 AND ent_nota_fiscal.modelo::text <> '0'::text AND ent_nota_fiscal.filial_nota = bas_filial.codigo
             LEFT JOIN nfs_op_fiscal ON nfs_op_fiscal.cfop = ent_nota_fiscal.cfop
		   WHERE (nfs_op_fiscal.entrada_saida = 'E' OR nfs_op_fiscal.entrada_saida IS NULL)
          GROUP BY bas_filial.codigo, tabaux.ano_mes
		UNION ALL
		 SELECT bas_filial.codigo AS filial,
            tabaux.ano_mes::integer AS ano_mes,
            '2 - Entrada'::text AS tipo,
            COALESCE(sum(nfs_nota_fiscal.vlr_icms), 0.000000) AS icms,
            COALESCE(sum(nfs_nota_fiscal.vlr_ipi), 0.000000) AS ipi,
            COALESCE(sum(nfs_nota_fiscal.vlr_iss), 0.000000) AS iss,
            COALESCE(sum(nfs_nota_fiscal.vlr_pis), 0.000000) AS pis,
            COALESCE(sum(nfs_nota_fiscal.valor_cofins), 0.000000) AS cofins,
            COALESCE(sum(nfs_nota_fiscal.vlr_icms_substituicao), 0.000000) AS icms_subst,
            COALESCE(sum(nfs_nota_fiscal.total_imp_import), 0.000000) AS importacao,
            COALESCE(sum(nfs_nota_fiscal.valor_total_icms_destino + nfs_nota_fiscal.valor_total_icms_origem), 0.000000) AS difal
			
           FROM ( SELECT to_char(CURRENT_DATE - '1 mon'::interval month * generate_series(0, 11)::double precision, 'YYYYmm'::text) AS ano_mes) tabaux
             CROSS JOIN bas_filial
             LEFT JOIN nfs_nota_fiscal ON to_char(nfs_nota_fiscal.data_emissao::timestamp with time zone, 'YYYYmm'::text)::integer = tabaux.ano_mes::integer AND nfs_nota_fiscal.filial_nota = bas_filial.codigo AND nfs_nota_fiscal.cancelada::text = 'N'::text
             LEFT JOIN nfs_op_fiscal ON nfs_op_fiscal.cfop = nfs_nota_fiscal.cfop
		   WHERE (nfs_op_fiscal.entrada_saida = 'E' OR nfs_op_fiscal.entrada_saida IS NULL)
          GROUP BY bas_filial.codigo, tabaux.ano_mes
        UNION ALL
         SELECT bas_filial.codigo AS filial,
            tabaux.ano_mes::integer AS ano_mes,
            '0 - Saldo Mês Anterior'::text AS tipo,
            sum(COALESCE(dashboard_lancamento_tributos.icms, 0.000000)) AS icms,
            sum(COALESCE(dashboard_lancamento_tributos.ipi, 0.000000)) AS ipi,
            sum(COALESCE(dashboard_lancamento_tributos.iss, 0.000000)) AS iss,
            sum(COALESCE(dashboard_lancamento_tributos.pis, 0.000000)) AS pis,
            sum(COALESCE(dashboard_lancamento_tributos.cofins, 0.000000)) AS cofins,
            sum(COALESCE(dashboard_lancamento_tributos.icmsst, 0.000000)) AS icms_subst,
            sum(COALESCE(dashboard_lancamento_tributos.importacao, 0.000000)) AS importacao,
            sum(COALESCE(dashboard_lancamento_tributos.difal, 0.000000)) AS difal
           FROM ( SELECT to_char(CURRENT_DATE - '1 mon'::interval month * generate_series(0, 11)::double precision, 'YYYYmm'::text) AS ano_mes) tabaux
             CROSS JOIN bas_filial
             LEFT JOIN dashboard_lancamento_tributos ON (lpad(dashboard_lancamento_tributos.ano::text, 4, '0'::text) || lpad(dashboard_lancamento_tributos.mes::text, 2, '0'::text)) = tabaux.ano_mes AND dashboard_lancamento_tributos.filial = bas_filial.codigo

GROUP BY bas_filial.codigo, tabaux.ano_mes) tab_final

GROUP BY filial, ano_mes, tipo
  ORDER BY filial, ano_mes DESC, tipo;