select first 30 distinct EE.REG,

                         (select first 1 Z.ATTRS1
                          from ZXBI Z
                          inner join SILANEXA ZL on ZL.ID = Z.ENTITY_ID
                          where Z."ACTIVE" = 1 and
                                ZL.ID_SICADATE = EE.ID
                          order by 1 desc) STATUS,

                         (select first 1 cast(SUBSTR(TT.DATA_HORA, 12, 13) || ':' || SUBSTR(TT.DATA_HORA, 15, 16) as char(5)) as HORAS
                          from TBSENHAATEND T
                          inner join TBSENHAATEND_SIPACAGD B on T.ID = B.ID_TBSENHAATEND
                          inner join ATCABECATEND_TBSENHAATEND C on C.ID_TBSENHAATEND = T.ID
                          inner join ATCABECATEND D on D.ID = C.ID_ATCABECATEND
                          inner join TBFLUXOATEND TT on TT.ID_TBSENHAATEND = T.ID

                          where EE.ID_ATCABECATEND = D.ID and
                                TT.ID_TBLOCAL = 64 and
                                TT.ACAO = 'A'
                          order by T.DATA_HORA) HORA_SENHA,

                         iif(EE.ATEND = 'E', U.ALIAS,

                         iif(EE.ATEND = 'I',
                         (select BLOCO
                          from RICADINT I
                          where I.PRONT = EE.PRONT and
                                EE.REG = I.REG), 1)) LOCAL,

                         (select first 1 cast(SUBSTR(L.DATA_HORA_INCLUSAO, 12, 13) || ':' || SUBSTR(L.DATA_HORA_INCLUSAO, 15, 16) as char(5)) as HORAS
                          from SILANEXA L
                          inner join SITABPRO P on P.ATO = L.ATO and P.CODALF = L.EXAME
                          where L.ID_SICADATE = EE.ID
                          order by L.DATA_HORA_INCLUSAO) HORA_SOL,

                         cast(SUBSTR(A.DATA_HORA_ENTRADA, 12, 13) || ':' || SUBSTR(A.DATA_HORA_ENTRADA, 15, 16) as char(5)) as FICHA,
                         PP.NOME PACIENTE,
                         --DECODE(position(' ', PP.NOME), 0, PP.NOME, substring(PP.NOME from 1 for position(' ', PP.NOME))) || '  ' || iif(position(' ', PP.NOME) + 1 > 1, substring(PP.NOME from position(' ', PP.NOME) + 1 for 1), '') /segunda/ || iif(position(' ', PP.NOME, position(' ', PP.NOME) + 1) + 1 > 2, substring(PP.NOME from position(' ', PP.NOME, position(' ', PP.NOME) + 1) + 1 for 1), '')
                         --/terceira/ || iif(position(' ', PP.NOME, position(' ', PP.NOME, position(' ', PP.NOME) + 1) + 1) + 1 > 3, substring(PP.NOME from position(' ', PP.NOME, position(' ', PP.NOME, position(' ', PP.NOME) + 1) + 1) + 1 for 1), '') /quarta/ PACIENTE,

                         (select first 1 cast(SUBSTR(L.DATA_HORA_INCLUSAO, 12, 13) || ':' || SUBSTR(L.DATA_HORA_INCLUSAO, 15, 16) as char(5)) as HORAS
                          from SILANEXA L
                          inner join SITABPRO P on P.ATO = L.ATO and P.CODALF = L.EXAME
                          inner join SICADATE E on E.ID = L.ID_SICADATE
                          inner join RICADPAC N on N.PRONT = E.PRONT
                          where L.ID_SICADATE = E.ID and
                                E.ID = EE.ID and
                                L.PEND_SADT = 'F' and
                                P.GRUPO2 = 1
                                   and
                                not exists(select *
                              from ZXBI Z
                              --      inner join SILANEXA ZL on ZL.ID = Z.ENTITY_ID
                              where Z."ACTIVE" = 1 and
                                    L.ID = Z.ENTITY_ID and
                                    Z.ATTRS1 = 'Exame Concluído')

                                ) RX,

                         (select first 1 cast(SUBSTR(F.HORA, 1, 2) || ':' || SUBSTR(F.HORA, 3, 4) as char(5))
                          from SIPACAGD F
                          where F.DATA = current_date and
                                F.PRONT = EE.PRONT and
                                F.NOME containing 'MAMOGRAFIA'
                          order by F.DATA desc) MAMO,

                         iif(EE.CDC <> 12000,
                         (select first 1 cast(SUBSTR(L.DATA_HORA_INCLUSAO, 12, 13) || ':' || SUBSTR(L.DATA_HORA_INCLUSAO, 15, 16) as char(5)) as HORAS
                          from SILANEXA L
                          inner join SITABPRO P on P.ATO = L.ATO and P.CODALF = L.EXAME
                          where L.ID_SICADATE = EE.ID and
                                L.PEND_SADT = 'F' and
                                P.GRUPO2 = 4),
                         (select first 1 cast(SUBSTR(F.HORA, 1, 2) || ':' || SUBSTR(F.HORA, 3, 4) as char(5))
                          from SIPACAGD F
                          inner join sicadate s on f.registro=s.reg
                          inner join silanexa l on l.id_sicadate=s.id
                          where F.DATA = current_date and
                                F.PRONT = EE.PRONT and
                                F.NOME containing 'ULTRASSOM GERAL'
                                    and
                                not exists(select *
                              from ZXBI Z
                              --      inner join SILANEXA ZL on ZL.ID = Z.ENTITY_ID
                              where Z."ACTIVE" = 1 and
                                    L.ID = Z.ENTITY_ID and
                                    Z.ATTRS1 = 'Exame Concluído')



                          order by F.DATA desc)) ULTRASSOM,

                         iif(EE.CDC <> 12000,
                         (select first 1 cast(SUBSTR(L.DATA_HORA_INCLUSAO, 12, 13) || ':' || SUBSTR(L.DATA_HORA_INCLUSAO, 15, 16) as char(5)) as HORAS
                          from SILANEXA L
                          inner join SITABPRO P on P.ATO = L.ATO and P.CODALF = L.EXAME
                          where L.ID_SICADATE = EE.ID and
                                L.PEND_SADT = 'F' and
                                P.GRUPO2 = 5),
                         (select first 1 cast(SUBSTR(F.HORA, 1, 2) || ':' || SUBSTR(F.HORA, 3, 4) as char(5))
                          from SIPACAGD F
                          where F.DATA = current_date and
                                F.PRONT = EE.PRONT and
                                F.NOME containing 'TOMOGRAFIA'
                          order by F.DATA desc)) TOMOGRAFIA,

                         (select first 1 cast(SUBSTR(F.HORA, 1, 2) || ':' || SUBSTR(F.HORA, 3, 4) as char(5))
                          from SIPACAGD F
                          where F.DATA = current_date and
                                F.PRONT = EE.PRONT and
                                F.NOME containing 'RESSONANCIA'
                          order by F.DATA desc) RM,

                         (select first 1 cast(SUBSTR(L.DATA_HORA_INCLUSAO, 12, 13) || ':' || SUBSTR(L.DATA_HORA_INCLUSAO, 15, 16) as char(5)) as HORAS
                          from SILANEXA L
                          inner join SITABPRO P on P.ATO = L.ATO and P.CODALF = L.EXAME
                          inner join SICADATE E on E.ID = L.ID_SICADATE
                          inner join RICADPAC N on N.PRONT = E.PRONT
                          where L.ID_SICADATE = E.ID and
                                E.ID = EE.ID and
                                L.PEND_SADT = 'F' and
                                P.GRUPO2 = 8 and
                                L.CDC = 12000) ECG,

                         (select first 1 cast(SUBSTR(L.DATA_HORA_INCLUSAO, 12, 13) || ':' || SUBSTR(L.DATA_HORA_INCLUSAO, 15, 16) as char(5)) as HORAS
                          from SILANEXA L
                          inner join SITABPRO P on P.ATO = L.ATO and P.CODALF = L.EXAME
                          inner join SICADATE E on E.ID = L.ID_SICADATE
                          inner join RICADPAC N on N.PRONT = E.PRONT
                          where L.ID_SICADATE = E.ID and
                                E.ID = EE.ID and
                                L.PEND_SADT = 'F' and
                                P.GRUPO2 = 9) CARD,

                         (select first 1 cast(SUBSTR(L.DATA_HORA_INCLUSAO, 12, 13) || ':' || SUBSTR(L.DATA_HORA_INCLUSAO, 15, 16) as char(5)) as HORAS
                          from SILANEXA L
                          inner join SITABPRO P on P.ATO = L.ATO and P.CODALF = L.EXAME
                          inner join SICADATE E on E.ID = L.ID_SICADATE
                          inner join RICADPAC N on N.PRONT = E.PRONT
                          where L.ID_SICADATE = E.ID and
                                E.ID = EE.ID and
                                L.PEND_SADT = 'F' and
                                P.GRUPO2 = 10) ENDO,

                         (select first 1 cast(SUBSTR(L.DATA_HORA_INCLUSAO, 12, 13) || ':' || SUBSTR(L.DATA_HORA_INCLUSAO, 15, 16) as char(5)) as HORAS
                          from SILANEXA L
                          inner join SITABPRO P on P.ATO = L.ATO and P.CODALF = L.EXAME
                          inner join SICADATE E on E.ID = L.ID_SICADATE
                          inner join RICADPAC N on N.PRONT = E.PRONT
                          where L.ID_SICADATE = E.ID and
                                E.ID = EE.ID and
                                L.PEND_SADT = 'F' and
                                P.GRUPO2 = 12) VASC

from SICADATE EE

inner join RICADPAC PP on PP.PRONT = EE.PRONT
inner join ATCABECATEND A on A.ID = EE.ID_ATCABECATEND
--inner join SITABPRO P on P.ATO = L.ATO and P.CODALF = L.EXAME
inner join TBCENCUS U on U.COD = EE.CDC
where EE.DATA = current_date and
      exists(select *
             from SILANEXA LL
             inner join SITABPRO P on P.ATO = LL.ATO and P.CODALF = LL.EXAME
             where LL.ID_SICADATE = EE.ID and
                   LL.PEND_SADT = 'F' and
                   P.GRUPO2 in (4, 5, 6) and
                   not exists(select *
                              from ZXBI Z
                              --      inner join SILANEXA ZL on ZL.ID = Z.ENTITY_ID
                              where Z."ACTIVE" = 1 and
                                    LL.ID = Z.ENTITY_ID and
                                    Z.ATTRS1 = 'Exame Concluído'))

order by 5