extracao_par3_script.sql

SELECT
	distinct
						pro.proid,
						CASE WHEN iu.muncod IS NULL THEN est.estuf ELSE mun.estuf END AS "UF",
						mun.mundescricao as "Entidade",
						to_char(pro.pronumeroprocesso::bigint, 'FM00000"."000000"/"0000"-"00') as "Processo",
						dot.dotnumero||'-'||dot.intoid as "Documento", 
						to_char(dot.dotdatafimvigencia, 'DD/MM/YYYY') as "Vigência",
						(case
                            when mdo.mdoex_oficio = 'S' then 'Validado'
                            when (select dotsq1.dotcancelado from par3.documentotermo as dotsq1 where dotsq1.proid = pro.proid and dotsq1.dotcancelado = 'S' limit 1) = 'S' then 'Cancelado'
                        else
                            CASE WHEN mdo.mdoqtdvalidacao <= (SELECT COUNT(dv.dtvid) FROM par3.documentotermovalidacao dv WHERE dv.dotid = dot.dotid AND dv.dtvstatus = 'A')
                                THEN 'Validado'
                                ELSE 'Não Validado' 
                            end
                        end) as "Validação",
						ito.intodsc AS "Tipo",
						ini.iniid as "Código da Iniciativa",
                      	ind.inddsc AS "Iniciativa",
                      	ipic.ipiano AS "Ano",
                      	--sum(vip.vlriniciativa::NUMERIC(20,2)) AS "Valor da Iniciativa",
                      	--COALESCE(REPLACE(TO_CHAR(sum(vip.vlriniciativa), 'FM999999990.00'), '.', ','), '0,00') AS "Valor da Iniciativa",
                      	TO_CHAR(sum(vip.vlriniciativa), 'FM9999999D99')  AS "Valor da Iniciativa",
                      	--sum(vip.vlriniciativa) AS "Valor da Iniciativa",
                      	COALESCE(REPLACE(TO_CHAR(vlrempenho, 'FM999999990.00'), '.', ','), '0,00') as "Valor Empenhado",
						COALESCE(REPLACE(TO_CHAR(vlr_pg_efetivado, 'FM999999990.00'), '.', ','), '0,00') as "Valor Pago",
						--null as "Situação Pagamento",
						CASE WHEN est.estuf IS NULL THEN 'Municipal' ELSE 'Estadual' END as "Esfera",
						--null as "Subação de Emenda",
						CASE 
						    WHEN ini.inistatus = 'A' THEN 'Ativo'
						    WHEN ini.inistatus = 'I' THEN 'Inativo'
						    ELSE 'Cópia'
						END AS "Status da Iniciativa",
						(select sum(ipiquantidade)
							from par3.iniciativa_planejamento_item_composicao ipic1 
								inner join par3.iniciativa_planejamento ip1 on ipic1.inpid = ip1.inpid
									where ip1.inpid = ip.inpid 
										and ipic1.ipiano = ipic.ipiano) as "Quantidade de Itens Planejados",
						(select sum(ipiquantidade)
							from par3.iniciativa_planejamento_item_composicao ipic1 
								inner join par3.iniciativa_planejamento ip1 on ipic1.inpid = ip1.inpid
									where ip1.inpid = ip.inpid 
										and ipic1.ipiano = ipic.ipiano) as "Quantidade de Itens Validados",
						CASE 
						    WHEN EXISTS (
						        SELECT 1
						        FROM par3.vm_relatorio_quantitativo_pendencias vrqp 
						        WHERE vrqp.inuid = pro.inuid
						          AND habilitacao = TRUE
						    ) 
						    THEN 'Sim' 
						    ELSE 'Não' 
						END AS "Habilitação",
						CASE 
						    WHEN EXISTS (
						        SELECT 1
						        FROM par3.vm_relatorio_quantitativo_pendencias vrqp 
						        WHERE vrqp.inuid = pro.inuid
						          AND (
						              habilitacao = TRUE OR 
						              cae = TRUE OR 
						              contas = TRUE OR 
						              monitoramento_par = TRUE OR 
						              obras_par = TRUE OR 
						              cacs = TRUE OR 
						              pne = TRUE OR 
						              siope = TRUE
						          )
						    ) 
						    THEN 'Sim'
						    ELSE 'Não' 
						END AS "Pendência",
						case
							when
								(SELECT count(1)
				                    FROM par3.processo p
				                    INNER JOIN par3.execucao_contrato ec on ec.proid = p.proid AND ec.ecostatus = 'A' and p.prostatus = 'A'
				       				where p.proid = pro.proid 
				       				limit 1) > 0
				             THEN 'Sim'   
						     else 'Não'
						end
						as "Contrato Inserido",
						case
							when
								(SELECT count(1)
				                    FROM par3.processo p
				                    INNER JOIN par3.execucao_notafiscal en on en.proid = p.proid AND en.ntfstatus = 'A' and p.prostatus = 'A'
				       				where p.proid = pro.proid
				       				limit 1) > 0
				             THEN 'Sim'   
							 else 'Não'
						end
						as "Nota Fiscal Inserida",
						(select count(1)
							from par3.iniciativa_planejamento_item_composicao ipic1 
								inner join par3.iniciativa_planejamento ip1 on ipic1.inpid = ip1.inpid
									where ip1.inpid = ip.inpid 
										and ipic1.ipiano = ipic.ipiano) as "Quantidade de Escolas Atendidas",
						( SELECT (case when epc.epcstatus = 'A' then to_char(epc.epcdatavalidacao, 'DD/MM/YYYY')  ELSE ''
                        END) FROM par3.execucao_prestacao_contas epc where epc.proid = pro.proid and epc.epcstatus = 'A'  ORDER BY epc.epcid desc LIMIT 1
							) as "Data Envio PC",
							COALESCE((
							  SELECT 
							    CASE 
							      WHEN epc2.epcstatus = 'A' THEN 'Enviado'
							      ELSE 'Não Enviado'
							    END
							  FROM par3.execucao_prestacao_contas epc2
								  WHERE epc2.epcid = (
								    SELECT epc3.epcid 
								    	FROM par3.execucao_prestacao_contas epc3
								    		WHERE epc3.proid = pro.proid
								    			ORDER BY epc3.epcid DESC
								    			LIMIT 1
							  )
							), 'Não Enviado') AS "Situação Envio PC",
					    --null  as "Fase Análise PC",
					    CASE 
						    WHEN ed.esddsc is not NULL 
						    THEN ed.esddsc
						    ELSE 'Não Iniciado'
						END  as "Situação Análise PC",
						--null as "Conclusão PC",
						--null as "Data Conclusão Análise PC",
						(par.retornasaldoprocesso(pro.pronumeroprocesso)) as "Saldo CC",
						CASE 
							WHEN EXISTS (
						        SELECT 1
						        FROM par3.vm_relatorio_quantitativo_pendencias vrqp 
						        WHERE vrqp.inuid = pro.inuid
						          AND cacs = TRUE
						    )
						    THEN 'Sim' 
					        ELSE 'Não' 
						END AS "Manifestação CACS"
                    FROM par3.processo pro 
                    	JOIN(
    					    SELECT pp.proid, sum(ai.aicqtdaprovado::numeric * ai.aicvaloraprovado) AS vlriniciativa, a.inpid, 'P' AS tipo_processo
                            FROM par3.processoparcomposicao pp
                                JOIN par3.analise_itemcomposicao ai ON pp.anaid = ai.anaid
                                JOIN par3.analise a ON ai.anaid = a.anaid and a.anastatus = 'A'
                            WHERE pp.ppcstatus = 'A' AND ai.aicstatus = 'A'
                			GROUP BY pp.proid, a.inpid
    	     				UNION ALL /*bem aqui tem um union all*/
							SELECT po.proid, sum(o.obrvalor) AS vlriniciativa, o.inpid, 'O' as tipo_processo
							FROM par3.obra o
								INNER JOIN par3.processoobracomposicao po ON po.obrid = o.obrid AND po.pocstatus = 'A'
							WHERE o.obrstatus = 'A' GROUP BY po.proid, o.inpid
    				    ) AS vip ON vip.proid = pro.proid
                    	left join par3.vm_relatorio_quantitativo_pendencias vmrqp on pro.inuid = vmrqp.inuid
                      INNER JOIN par3.documentotermo dot ON pro.proid = dot.proid AND dot.dotstatus = 'A'
                      Left join workflow.documento d on d.docid = dot.docid --and d.esdid = 2447
                      left JOIN workflow.estadodocumento AS ed ON d.esdid = ed.esdid
                      INNER JOIN par3.modelodocumento mdo ON dot.mdoid = mdo.mdoid                      
                      INNER JOIN par3.iniciativa_tipos_objeto ito ON ito.intoid = dot.intoid
                      LEFT JOIN par3.processoobracomposicao poc ON poc.proid = dot.proid and poc.pocstatus = 'A'
                      LEFT JOIN obras2.obras o ON o.obrid_par3 = poc.obrid
                      LEFT JOIN LATERAL (SELECT obrid, obrid_par3, COUNT(obrid) as totalobrid FROM obras2.obras obr WHERE obr.obrid_par3 = poc.obrid and obr.obridpai is null and obrstatus = 'A' GROUP BY obrid) v on true
                      LEFT JOIN LATERAL (SELECT sum(vlrempenho) as vlrempenho, sum(vlr_pg_efetivado) as vlr_pg_efetivado
                                         FROM par3.v_saldo_empenho_do_processo vsaldo
                                         WHERE vsaldo.proid = dot.proid ) v2 on true
                      JOIN par3.instrumentounidade iu on iu.inuid = pro.inuid and iu.inustatus = 'A'
                      LEFT JOIN territorios.municipio mun on mun.muncod = iu.muncod
                     LEFT JOIN territorios.estado est on est.estuf = iu.estuf
                     Join par3.processoparcomposicao pp ON pp.proid = pro.proid
                     JOIN par3.iniciativa_planejamento ip on ip.inpid = pp.inpid
                     inner join par3.iniciativa_planejamento_item_composicao ipic on ipic.inpid = ip.inpid
                     JOIN par3.iniciativa ini on ini.iniid = ip.iniid --AND ini.inistatus = 'A'
                     INNER JOIN par3.iniciativa_descricao ind on ind.indid = ini.indid

                    WHERE dotstatus = 'A'
                      AND  dot.dotid NOT IN ( SELECT coalesce(par3.retornar_termo_pendente_validacao(dot.dotid)::integer,0) )
                      and pro.prostatus = 'A'
                      --AND pro.pronumeroprocesso = '23400000017201978'

                    GROUP BY
                        pro.pronumeroprocesso,  
								mdo.mdonome,
								ito.intodsc,                      
                      dot.dotnumero,
							 dot.intoid,                      
							  est.estuf, 
							  mun.estuf,							  
                        iu.muncod,
								 est.estdescricao,
								 mun.mundescricao,
								dot.dotdatafimvigencia,
								dotdatafimvigencia,                         
								dot.dotvalortermo,
								vlrempenho,
								vlr_pg_efetivado,
                      	ind.inddsc,
                      	ed.esddsc,
                      	ini.iniid,
                      	ip.inpid,
                      	ipic.ipiano,
                      	mdo.mdoex_oficio,
                      	mdo.mdoqtdvalidacao,
                      	dot.dotid,
                      			pro.proid							
								--limit 10;