SET statement_timeout=0;
EXPLAIN (ANALYZE, BUFFERS)
	    SELECT cd_unidade, cd_centro_custo, cd_pessoa, dt_mes, cd_pessoa_matriz, cd_pessoa_filial, vl_corretor, 
	           SUM(vl_supervisor + vl_gerente + vl_agenciador + vl_manifestador + vl_corretor  - vl_corretor ) AS vl_comissao
	
	      FROM (
	          SELECT acc.cd_unidade, 
	                 acc.cd_centro_custo,
	                 ccp.cd_pessoa, 
	                 (SUBSTR(ct.dt_emissao::TEXT, 1, 8) || '01') AS dt_mes,
	                 ct.cd_pessoa_matriz,
	                 ct.cd_pessoa_filial,
	  
	                 SUM(ccp.vl_supervisor) AS vl_supervisor,
	                 SUM(0)                 AS vl_gerente,
	                 SUM(0)                 AS vl_agenciador,
	                 SUM(0)                 AS vl_manifestador,
	                 SUM(0)                 AS vl_corretor
	            
	            FROM ctrc ct
	            JOIN ctrc_comissao_pessoa ccp ON ccp.cd_ctrc = ct.cd_ctrc
	            JOIN ctrc_comissao cc ON cc.cd_ctrc = ccp.cd_ctrc
	            JOIN pessoa p ON p.cd_pessoa = ccp.cd_pessoa 
	            JOIN salario s ON s.cd_pessoa = ccp.cd_pessoa
	            LEFT OUTER JOIN funcionario f ON f.cd_pessoa = s.cd_pessoa
	  
	            JOIN agencia_centro_custo acc ON acc.cd_agencia = cc.cd_agencia_supervisor
	            
	           WHERE ct.dt_cancelamento IS NULL
	             AND ct.id_operacao IN (3, 1)
	             AND ct.dt_emissao >= (SELECT (p.dt_ultimo_fechamento_salario + INTERVAL '1 month')
	                     FROM pessoa p_
	                    WHERE p_.cd_pessoa = p.cd_pessoa)::DATE
	             AND ct.dt_emissao <= '2014-02-28'
	             AND acc.dt_vigencia = (SELECT acc2.dt_vigencia
	                                      FROM agencia_centro_custo acc2
	                                     WHERE acc2.cd_agencia = acc.cd_agencia
	                                       AND acc2.dt_vigencia <= ct.dt_emissao
	                                     ORDER BY acc2.dt_vigencia DESC
	                                     LIMIT 1)
	             AND s.id_pagamento >= 0
	             AND s.id_pagamento != 1
	             AND s.dt_vigencia = (SELECT s2.dt_vigencia
	                                    FROM salario s2
	                                   WHERE s2.cd_pessoa = s.cd_pessoa
	                                     AND s2.dt_vigencia <= '2014-02-28'
	                                   ORDER BY s2.dt_vigencia DESC
	                                   LIMIT 1)
	             AND (f.dt_admissao <= '2014-02-28' OR f.dt_admissao IS NULL)
	             AND (f.dt_demissao > '2014-02-28' OR 
	                  f.dt_demissao IS NULL OR 
	                  f.dt_demissao >= ct.dt_emissao)
	  
	            AND ccp.vl_supervisor != 0
	            GROUP BY 1, 2, 3, 4, 5, 6  
	 
	           UNION ALL
	 
	            
	          SELECT acc.cd_unidade, 
	                 acc.cd_centro_custo,
	                 ccp.cd_pessoa, 
	                 (SUBSTR(ct.dt_emissao::TEXT, 1, 8) || '01') AS dt_mes,
	                 ct.cd_pessoa_matriz,
	                 ct.cd_pessoa_filial,
	  
	                 SUM(0),
	                 SUM(ccp.vl_gerente),
	                 SUM(0),
	                 SUM(0),
	                 SUM(0)
	            
	            FROM ctrc ct
	            JOIN ctrc_comissao_pessoa ccp ON ccp.cd_ctrc = ct.cd_ctrc
	            JOIN ctrc_comissao cc ON cc.cd_ctrc = ccp.cd_ctrc
	            JOIN pessoa p ON p.cd_pessoa = ccp.cd_pessoa 
	            JOIN salario s ON s.cd_pessoa = ccp.cd_pessoa
	            LEFT OUTER JOIN funcionario f ON f.cd_pessoa = s.cd_pessoa
	  
	            JOIN agencia_centro_custo acc ON acc.cd_agencia = cc.cd_agencia_gerente
	            
	           WHERE ct.dt_cancelamento IS NULL
	             AND ct.id_operacao IN (3, 1)
	             AND ct.dt_emissao >= (SELECT (p.dt_ultimo_fechamento_salario + INTERVAL '1 month')
	                     FROM pessoa p_
	                    WHERE p_.cd_pessoa = p.cd_pessoa)::DATE
	             AND ct.dt_emissao <= '2014-02-28'
	             AND acc.dt_vigencia = (SELECT acc2.dt_vigencia
	                                      FROM agencia_centro_custo acc2
	                                     WHERE acc2.cd_agencia = acc.cd_agencia
	                                       AND acc2.dt_vigencia <= ct.dt_emissao
	                                     ORDER BY acc2.dt_vigencia DESC
	                                     LIMIT 1)
	             AND s.id_pagamento >= 0
	             AND s.id_pagamento != 1
	             AND s.dt_vigencia = (SELECT s2.dt_vigencia
	                                    FROM salario s2
	                                   WHERE s2.cd_pessoa = s.cd_pessoa
	                                     AND s2.dt_vigencia <= '2014-02-28'
	                                   ORDER BY s2.dt_vigencia DESC
	                                   LIMIT 1)
	             AND (f.dt_admissao <= '2014-02-28' OR f.dt_admissao IS NULL)
	             AND (f.dt_demissao > '2014-02-28' OR 
	                  f.dt_demissao IS NULL OR 
	                  f.dt_demissao >= ct.dt_emissao)
	  
	            AND ccp.vl_gerente != 0
	            GROUP BY 1, 2, 3, 4, 5, 6  
	 
	           UNION ALL
	 
	            
	          SELECT acc.cd_unidade, 
	                 acc.cd_centro_custo,
	                 ccp.cd_pessoa, 
	                 (SUBSTR(ct.dt_emissao::TEXT, 1, 8) || '01') AS dt_mes,
	                 ct.cd_pessoa_matriz,
	                 ct.cd_pessoa_filial,
	  
	                 SUM(0),
	                 SUM(0),
	                 SUM(ccp.vl_agenciador),
	                 SUM(0),
	                 SUM(0)
	            
	            FROM ctrc ct
	            JOIN ctrc_comissao_pessoa ccp ON ccp.cd_ctrc = ct.cd_ctrc
	            JOIN ctrc_comissao cc ON cc.cd_ctrc = ccp.cd_ctrc
	            JOIN pessoa p ON p.cd_pessoa = ccp.cd_pessoa 
	            JOIN salario s ON s.cd_pessoa = ccp.cd_pessoa
	            LEFT OUTER JOIN funcionario f ON f.cd_pessoa = s.cd_pessoa
	  
	            JOIN agencia_centro_custo acc ON acc.cd_agencia = cc.cd_agencia_agenciador
	            
	           WHERE ct.dt_cancelamento IS NULL
	             AND ct.id_operacao IN (3, 1)
	             AND ct.dt_emissao >= (SELECT (p.dt_ultimo_fechamento_salario + INTERVAL '1 month')
	                     FROM pessoa p_
	                    WHERE p_.cd_pessoa = p.cd_pessoa)::DATE
	             AND ct.dt_emissao <= '2014-02-28'
	             AND acc.dt_vigencia = (SELECT acc2.dt_vigencia
	                                      FROM agencia_centro_custo acc2
	                                     WHERE acc2.cd_agencia = acc.cd_agencia
	                                       AND acc2.dt_vigencia <= ct.dt_emissao
	                                     ORDER BY acc2.dt_vigencia DESC
	                                     LIMIT 1)
	             AND s.id_pagamento >= 0
	             AND s.id_pagamento != 1
	             AND s.dt_vigencia = (SELECT s2.dt_vigencia
	                                    FROM salario s2
	                                   WHERE s2.cd_pessoa = s.cd_pessoa
	                                     AND s2.dt_vigencia <= '2014-02-28'
	                                   ORDER BY s2.dt_vigencia DESC
	                                   LIMIT 1)
	             AND (f.dt_admissao <= '2014-02-28' OR f.dt_admissao IS NULL)
	             AND (f.dt_demissao > '2014-02-28' OR 
	                  f.dt_demissao IS NULL OR 
	                  f.dt_demissao >= ct.dt_emissao)
	  
	            AND ccp.vl_agenciador != 0
	            GROUP BY 1, 2, 3, 4, 5, 6  
	 
	           UNION ALL
	 
	            
	          SELECT acc.cd_unidade, 
	                 acc.cd_centro_custo,
	                 ccp.cd_pessoa, 
	                 (SUBSTR(ct.dt_emissao::TEXT, 1, 8) || '01') AS dt_mes,
	                 ct.cd_pessoa_matriz,
	                 ct.cd_pessoa_filial,
	  
	                 SUM(0),
	                 SUM(0),
	                 SUM(0),
	                 SUM(ccp.vl_manifestador),
	                 SUM(0)
	            
	            FROM ctrc ct
	            JOIN ctrc_comissao_pessoa ccp ON ccp.cd_ctrc = ct.cd_ctrc
	            JOIN ctrc_comissao cc ON cc.cd_ctrc = ccp.cd_ctrc
	            JOIN pessoa p ON p.cd_pessoa = ccp.cd_pessoa 
	            JOIN salario s ON s.cd_pessoa = ccp.cd_pessoa
	            LEFT OUTER JOIN funcionario f ON f.cd_pessoa = s.cd_pessoa
	  
	            JOIN agencia_centro_custo acc ON acc.cd_agencia = cc.cd_agencia_manifestador
	            
	           WHERE ct.dt_cancelamento IS NULL
	             AND ct.id_operacao IN (3, 1)
	             AND ct.dt_emissao >= (SELECT (p.dt_ultimo_fechamento_salario + INTERVAL '1 month')
	                     FROM pessoa p_
	                    WHERE p_.cd_pessoa = p.cd_pessoa)::DATE
	             AND ct.dt_emissao <= '2014-02-28'
	             AND acc.dt_vigencia = (SELECT acc2.dt_vigencia
	                                      FROM agencia_centro_custo acc2
	                                     WHERE acc2.cd_agencia = acc.cd_agencia
	                                       AND acc2.dt_vigencia <= ct.dt_emissao
	                                     ORDER BY acc2.dt_vigencia DESC
	                                     LIMIT 1)
	             AND s.id_pagamento >= 0
	             AND s.id_pagamento != 1
	             AND s.dt_vigencia = (SELECT s2.dt_vigencia
	                                    FROM salario s2
	                                   WHERE s2.cd_pessoa = s.cd_pessoa
	                                     AND s2.dt_vigencia <= '2014-02-28'
	                                   ORDER BY s2.dt_vigencia DESC
	                                   LIMIT 1)
	             AND (f.dt_admissao <= '2014-02-28' OR f.dt_admissao IS NULL)
	             AND (f.dt_demissao > '2014-02-28' OR 
	                  f.dt_demissao IS NULL OR 
	                  f.dt_demissao >= ct.dt_emissao)
	  
	            AND ccp.vl_manifestador != 0
	            GROUP BY 1, 2, 3, 4, 5, 6 
	            
	           UNION ALL
	
	            
	          SELECT acc.cd_unidade, 
	                 acc.cd_centro_custo,
	                 ccor.cd_pessoa_corretor,
	                 (SUBSTR(ct.dt_emissao::TEXT, 1, 8) || '01') AS dt_mes,
	                 ct.cd_pessoa_matriz,
	                 ct.cd_pessoa_filial,
	                 SUM(0),
	                 SUM(0),
	                 SUM(0),
	                 SUM(0),
	                 SUM(ccor.vl_total_corretor)
	            FROM ctrc ct
	            JOIN ctrc_corretor ccor ON ccor.cd_ctrc = ct.cd_ctrc
	            JOIN agencia_centro_custo acc ON acc.cd_agencia = ct.cd_agencia
	            JOIN pessoa p ON p.cd_pessoa = ccor.cd_pessoa_corretor
	            JOIN corretor c ON c.cd_pessoa = p.cd_pessoa
	                           AND c.id_atualiza_rh = 1
	            LEFT OUTER JOIN carta_frete cf ON cf.cd_ctrc = ct.cd_ctrc
	            LEFT OUTER JOIN ctrc_icms ci ON ci.cd_ctrc = ct.cd_ctrc
	           WHERE ct.dt_cancelamento IS NULL
	             AND ct.id_operacao IN (3, 1)
	             AND ct.dt_emissao >= (SELECT (p.dt_ultimo_fechamento_salario + INTERVAL '1 month')
	                     FROM pessoa p_
	                    WHERE p_.cd_pessoa = p.cd_pessoa)::DATE
	             AND ct.dt_emissao <= '2014-02-28'
	             AND acc.dt_vigencia = (SELECT acc2.dt_vigencia
	                                      FROM agencia_centro_custo acc2
	                                     WHERE acc2.cd_agencia = acc.cd_agencia
	                                       AND acc2.dt_vigencia <= ct.dt_emissao
	                                     ORDER BY acc2.dt_vigencia DESC
	                                     LIMIT 1)
	           GROUP BY 1, 2, 3, 4, 5, 6
	  ) AS tmp_comissao 
	      GROUP BY 1, 2, 3, 4, 5, 6 , 7 
	     ORDER BY 1, 2, 3, 4

