SELECT *  FROM (
            SELECT * FROM(
                        SELECT
                            orcamento.placaveiculo as PLATE,
                            orcamento.kmveiculo as KM,
                            oficinas.supplicer as SUPPLIER_CODE,



                                    CASE WHEN COALESCE(detalhe.event_number,0) = 0
                                            THEN NULL ELSE (
                        CASE WHEN detalhe.tipo_evento = 'M'
                            THEN orcamento.event_number_manutencao ELSE (
                        CASE WHEN detalhe.tipo_evento = 'P'
                            THEN orcamento.event_number_pneu  ELSE (
                        CASE WHEN orcamento.tipomanu = detalhe.tipo_evento
                            THEN orcamento.event_number END )
                                    END  ) END ) END  as EVENT_NUMBER ,







                    CASE WHEN COALESCE(detalhe.event_number,0) = 0  THEN NULL ELSE (
                        CASE WHEN detalhe.tipo_evento = 'M' AND orcamento.tipomanu = detalhe.tipo_evento
                        THEN orcamento.acordomanutencao ELSE (
                    CASE WHEN detalhe.tipo_evento = 'P' AND orcamento.tipomanu = detalhe.tipo_evento
                        THEN orcamento.acordopneu  ELSE (
                    CASE WHEN orcamento.tipomanu = detalhe.tipo_evento
                        THEN orcamento.acordo END )
                                    END  ) END ) END  as AGREEMENT_NUMBER ,



                   CASE WHEN detalhe.jobcode  = '1114' THEN
                orcamento.tipomanu ELSE
                            detalhe.tipo_evento  END as NOE,




                            '' as TYPE,
                            orcamento.datachegada as EVENT_DATE,
                            detalhe.jobcode as JOB_CODE,
                            detalhe.descricao as JOB_CODE_DESCRIPTION,
                            replace(replace(replace(to_char(detalhe.qtd, '999999999990D99'),',','-' ),'.',','),'-','.')  as QTT,
                            replace(replace(replace(to_char(detalhe.vunit, '999999999990D9999'),',','-' ),'.',','),'-','.')  as UNIT_PRICE,
                            'BRL' as CURRENCY,
                            '0,00' as DISCOUNT,
                            replace(replace(replace(to_char((detalhe.qtd * detalhe.vunit), '999999999990D9999'),',','-' ),'.',','),'-','.') as TOTAL_WO_TAX,
                            'PAS DE TAXES' as TAX,
                            '0,00' as TOTAL_W_TAX,
                            orcamento.datachegada as REAL_START_DATE,
                            orcamento.horachegada as REAL_START_HOUR,
                            orcamento.datafim as  PLANNED_END_DATE,
                            orcamento.horafim as PLANNED_END_HOUR,

                            'Observações:             ' || regexp_replace(orcamento.observacao, E'<[^>]+>', '', 'gi') || '   ' ||
                            'Observações da Oficina:  ' || regexp_replace(orcamento.observacaoofi, E'<[^>]+>', '', 'gi') || '   ' ||
                            'Defeitos Apontados:        ' || regexp_replace(orcamento.defeitosapontados, E'<[^>]+>', '', 'gi') || '   ' ||
                            'Avarias:           ' || regexp_replace(orcamento.avarias, E'<[^>]+>', '', 'gi') as COMMENT,



                           CASE WHEN COALESCE(detalhe.event_number,0) = 0   THEN 'Em Curso' ELSE 'Dado Acordo' END  as REASON,
                            veiculos.contrato as CONTRAT,
                                '0' as REINVOICE,
                            orcamento.id as EXTERNAL_ID,


                           CASE WHEN COALESCE(detalhe.event_number,0) > 0  THEN 0 ELSE 1 END  as CREATE_AGREEMENT,


                           detalhe.importado as importado,
               detalhe.id as id_det


                                FROM os.os orcamento
                                INNER JOIN os.detos detalhe ON orcamento.id = detalhe.os_id
                                INNER JOIN cadastro.oficinas oficinas ON orcamento.oficina_id = oficinas.id
                                INNER JOIN veiculo.veiculos veiculos ON orcamento.veiculo_id = veiculos.id

                                WHERE orcamento.status <> 'F'
                                AND detalhe.reprova = 'N'
                                AND detalhe.cancelamento = 'N'





                                ORDER BY orcamento.id



                        ) AS foo


                        WHERE  importado = 0
                      --  AND COALESCE(event_number,0) = 0
                       -- AND COALESCE(AGREEMENT_NUMBER,NULL) IS NULL
                       -- AND CREATE_AGREEMENT = 1


                        ORDER BY create_agreement ASC
                        ) RES


                       WHERE
                     id_det =  (select  det.id from os.detos det
                inner join os.os os on os.id = det.os_id
                where 1=1
                and  det.importado = 0
                AND det.id = RES.id_det
                --AND det.tipo_evento <> os.tipomanu
                AND COALESCE(det.event_number,null) is null)
AND
            external_id IN (select os_id from os.detos where  importado = 0
group by 1)

--and external_id = 581
--select * from os.detos where os_id = 581



                        --  noe  exist (select tipo_evento from os.detos where os_id = RES.external_id)
                  --    COALESCE((select count(event_number) as total from os.detos where os_id = RES.external_id ),0) = 0