SELECT R034FUN.NUMEMP,
(SELECT c.NOMFIL FROM vetorh.R030FIL c WHERE c.numemp = R034FUN.NUMEMP AND c.codfil = R034FUN.CODFIL) empresa,
(SELECT
                     MAX(INITCAP(bb.nomnat) )
                 FROM
                     vetorh_0302.r038hna aa,
                     vetorh_0302.r048nat bb
                 WHERE
                     aa.natdes = bb.natdes
                     AND aa.numemp = R034FUN.numemp
                     AND aa.numcad = R034FUN.numcad
             ) ds_natgasto,
R034FUN.NUMCAD , --2 matricula
R034FUN.CODESC,
R034FUN.NOMFUN , --3 nome funcionario
R034FUN.DATADM , --4 data adminissao
R010SIT.DESSIT ,
R010SIT.CODSIT ,
R034FUN.CODCAR , -- 5 cargo
(SELECT c.TITCAR  FROM vetorh_0302.R024CAR c WHERE c.codcar = R034FUN.CODCAR) ds_cargo,
R034CPL.REGCON , -- número conselho
NVL (R034FUN.COTDEF, 'N') , -- se é deficiente
R016ORN.NOMLOC ,
R034FUN.CODCCU , --9 centro
R018CCU.NOMCCU ,
(SELECT MAX(valsal) FROM vetorh_0302.R038HSA a WHERE a.numcad = R034FUN.NUMCAD AND a.numemp = r034fun.numemp) salario, --10 salario
vetorh_obter_peri_insa(R034FUN.NUMEMP,R034FUN.NUMCAD, R034FUN.TIPCOL, :dt_ref , 'P'  ) perc_peri,
vetorh_obter_peri_insa(R034FUN.NUMEMP,R034FUN.NUMCAD, R034FUN.TIPCOL, :dt_ref , 'I'  ) insa_peri,
R034FUN.DATNAS , -- 15 data nascimento
R034FUN.NUMCPF , --16 cpf
R034CPL.NUMCID , -- 17 rg
vetorh_obter_afastamento(R034FUN.NUMEMP,R034FUN.NUMCAD, R034FUN.TIPCOL, :dt_ref , 'CD'  ) situ_afa,
vetorh_obter_afastamento(R034FUN.NUMEMP,R034FUN.NUMCAD, R034FUN.TIPCOL, :dt_ref , 'DI'  ) inicio_afa,
vetorh_obter_afastamento(R034FUN.NUMEMP,R034FUN.NUMCAD, R034FUN.TIPCOL, :dt_ref, 'DF'  ) final_afa,
vetorh_obter_afastamento(R034FUN.NUMEMP,R034FUN.NUMCAD, R034FUN.TIPCOL, :dt_ref , 'DS'  ) DS_AFA-- descricao situacao afastamento
FROM
vetorh_0302.R034FUN,
vetorh_0302.R022VIN,
vetorh_0302.R010SIT,
vetorh_0302.R038HFI,
vetorh_0302.R038HLO,
vetorh_0302.R038HVI,
vetorh_0302.R034CPL,
vetorh_0302.R016ORN,
vetorh_0302.R018CCU
WHERE
((R034FUN.TIPCOL = 1)) AND
--R034FUN.SITAFA <> 7 and
R018CCU.CODCCU = R034FUN.CODCCU AND R018CCU.NUMEMP  =R034FUN.NUMEMP AND
R022VIN.CALFOL = 'S'     AND R034FUN.TIPCOL = 1
AND NOT EXISTS (SELECT 1
    FROM vetorh_0302.R038AFA b
    left join vetorh_0302.R010SIT c ON c.codsit = b.sitafa
    WHERE
    (R034FUN.NUMEMP = b.NUMEMP   ) AND
    (R034FUN.NUMCAD =b.NUMCAD   ) AND b.datafa < :dt_ref AND b.sitafa = 7  AND R010SIT.CODSIT <> 1)
 AND
(R038HVI.CODVIN = R022VIN.CODVIN) AND
(R034FUN.SITAFA = R010SIT.CODSIT) AND
(R038HFI.NUMEMP = R034FUN.NUMEMP) AND
(R038HFI.TIPCOL = R034FUN.TIPCOL) AND
(R038HFI.NUMCAD = R034FUN.NUMCAD) AND
(R038HLO.NUMEMP = R034FUN.NUMEMP) AND
(R038HLO.TIPCOL = R034FUN.TIPCOL) AND
(R038HLO.NUMCAD = R034FUN.NUMCAD) AND
(((R038HLO.TABORG = R016ORN.TABORG) AND
(R038HLO.NUMLOC = R016ORN.NUMLOC))) AND
(R038HVI.NUMEMP = R034FUN.NUMEMP) AND
(R038HVI.TIPCOL = R034FUN.TIPCOL) AND
(R038HVI.NUMCAD = R034FUN.NUMCAD) AND
(R034CPL.NUMEMP = R034FUN.NUMEMP) AND
(R034CPL.TIPCOL = R034FUN.TIPCOL) AND
(R034CPL.NUMCAD = R034FUN.NUMCAD) AND
((R038HVI.DATALT = (SELECT MAX (DATALT) FROM vetorh_0302.R038HVI TABELA001 WHERE
(TABELA001.NUMEMP = R038HVI.NUMEMP) AND
(TABELA001.TIPCOL = R038HVI.TIPCOL) AND
(TABELA001.NUMCAD = R038HVI.NUMCAD) AND
(TABELA001.DATALT <= :dt_ref)))
 AND    R034FUN.DATADM <= :dt_ref AND EXISTS(SELECT 1 FROM vetorh_0302.R038HFI R038HFI WHERE                         R038HFI.NUMEMP = R034FUN.NUMEMP                     AND R038HFI.TIPCOL = R034FUN.TIPCOL                     AND R038HFI.NUMCAD = R034FUN.NUMCAD                     AND R038HFI.DATALT <= :dt_ref )
 AND  R038HFI.DATALT = (SELECT MAX (DATALT) FROM vetorh_0302.R038HFI TAB2 WHERE            (TAB2.NUMEMP = R038HFI.NUMEMP) AND            (TAB2.TIPCOL = R038HFI.TIPCOL) AND            (TAB2.NUMCAD = R038HFI.NUMCAD) AND            (TAB2.NUMEMP = TAB2.EMPATU) AND            (TAB2.NUMCAD = TAB2.CADATU) AND            (TAB2.DATALT <= :dt_ref))
 AND
(R038HLO.DATALT = (SELECT MAX (DATALT) FROM vetorh_0302.R038HLO TABELA002 WHERE
(TABELA002.NUMEMP = R038HLO.NUMEMP) AND
(TABELA002.TIPCOL = R038HLO.TIPCOL) AND
(TABELA002.NUMCAD = R038HLO.NUMCAD) AND
(TABELA002.DATALT <= :dt_ref))))