Gestão de Pessoas - senior X - Manual do Usuário > Integrador SST > Saneamento da base do prestador > RSData

Saneamento da base do prestador — RSData

Extrair informações usando comandos no banco de dados

Estes são os comandos de SELECT que podem ser executados no sistema da Senior, para facilitar a extração dos dados. Esses comandos estão compatíveis com todas as entidades da integração (colaboradores, filiais, setores e cargos).

Comandos do banco de dados para saneamento da base

SQL Server

SELECT

1 'TIPO', ';' ,

null 'Unidade Marítima', ';' ,

CASE R030FIL.TipIns

WHEN 1 then

CASE

WHEN (REPLACE(R030FIL.NumCGC, '.', '') = null) THEN null

ELSE (SUBSTRING(CONVERT(CHAR, REPLACE(R030FIL.NumCGC, '.', '')),1,2) + '.' +

SUBSTRING(CONVERT(CHAR, REPLACE(R030FIL.NumCGC, '.', '')),3,3) + '.' +

SUBSTRING(CONVERT(CHAR, REPLACE(R030FIL.NumCGC, '.', '')),6,3) + '/' +

SUBSTRING(CONVERT(CHAR, REPLACE(R030FIL.NumCGC, '.', '')),9,4) + '-' +

SUBSTRING(CONVERT(CHAR, REPLACE(R030FIL.NumCGC, '.', '')),13,2))

END

WHEN 2 then

CASE

WHEN (REPLACE(R030FIL.NumCGC, '.', '') = null) THEN null

ELSE (SUBSTRING(CONVERT(CHAR, REPLACE(R030FIL.NumCGC, '.', '')),1,2) + '.' +

SUBSTRING(CONVERT(CHAR, REPLACE(R030FIL.NumCGC, '.', '')),3,3) + '.' +

SUBSTRING(CONVERT(CHAR, REPLACE(R030FIL.NumCGC, '.', '')),6,5) + '/' +

SUBSTRING(CONVERT(CHAR, REPLACE(R030FIL.NumCGC, '.', '')),11,2))

END

WHEN 3 then

CASE

WHEN (REPLACE(R030FIL.NumCGC, '.', '') = null) THEN null

ELSE (SUBSTRING(CONVERT(CHAR, REPLACE(R030FIL.NumCGC, '.', '')),1,3) + '.' +

SUBSTRING(CONVERT(CHAR, REPLACE(R030FIL.NumCGC, '.', '')),4,3) + '.' +

SUBSTRING(CONVERT(CHAR, REPLACE(R030FIL.NumCGC, '.', '')),7,3) + '-' +

SUBSTRING(CONVERT(CHAR, REPLACE(R030FIL.NumCGC, '.', '')),10,2))

END

ELSE null

END 'CNPJ/CPF/CEI/CAEPF', ';' ,

R034FUN.NomFun 'Nome', ';' ,

CASE WHEN R023NAC.PaiRfb = 105 THEN 'BRA' ELSE 'ES' END 'Nacionalidade', ';' ,

Convert(varchar(10), R034FUN.DatNas,103) 'Nascimento', ';' ,

R034FUN.TipSex 'Sexo', ';' ,

CASE WHEN CAST(R034FUN.NumCtp as VARCHAR(10)) = '0' THEN null ELSE CAST(R034FUN.NumCtp as VARCHAR(10)) END 'CTPS', ';' ,

CASE WHEN CAST(R034FUN.SerCtp as VARCHAR(10)) = '0' THEN null ELSE CAST(R034FUN.SerCtp as VARCHAR(10)) END 'Série', ';' ,

CASE WHEN Convert(varchar(10), R034FUN.DexCtp, 103) = '31/12/1900' THEN null ELSE Convert(varchar(10), R034FUN.DexCtp, 103) END 'Data de emissão', ';' ,

R034FUN.EstCtp 'UF emissão', ';' ,

R033PES.numcid 'Identidade', ';' ,

R033PES.EmiCid 'Orgão Exp.', ';' ,

CASE WHEN Convert(varchar(10), R033PES.DexCid, 103) = '31/12/1900' THEN null ELSE Convert(varchar(10), R033PES.DexCid, 103) END 'DT emissão', ';' ,

R033PES.EstCid 'UF emissão', ';' ,

CAST(R033PES.NumPis as VARCHAR(15)) 'NIT (PIS/PASEP)', ';' ,

CASE

WHEN R034FUN.NumCPF = 0 THEN ' '

WHEN R034FUN.NumCPF IS NULL THEN ' '

WHEN ((R034FUN.NumCPF <> 0) AND (LEN(R034FUN.NumCPF)= 1)) THEN (SUBSTRING('0000000000' + CONVERT(CHAR, R034FUN.NumCPF),1,3) + '.' + SUBSTRING('0000000000' + CONVERT(CHAR, R034FUN.NumCPF),4,3) + '.' + SUBSTRING('0000000000' + CONVERT(CHAR, R034FUN.NumCPF),7,3) + '-' + SUBSTRING('0000000000' + CONVERT(CHAR, R034FUN.NumCPF),10,2))

WHEN ((R034FUN.NumCPF <> 0) AND (LEN(R034FUN.NumCPF)= 2)) THEN (SUBSTRING('000000000' + CONVERT(CHAR, R034FUN.NumCPF),1,3) + '.' + SUBSTRING('000000000' + CONVERT(CHAR, R034FUN.NumCPF),4,3) + '.' + SUBSTRING('000000000' + CONVERT(CHAR, R034FUN.NumCPF),7,3) + '-' + SUBSTRING('000000000' + CONVERT(CHAR, R034FUN.NumCPF),10,2))

WHEN ((R034FUN.NumCPF <> 0) AND (LEN(R034FUN.NumCPF)= 3)) THEN (SUBSTRING('00000000' + CONVERT(CHAR, R034FUN.NumCPF),1,3) + '.' + SUBSTRING('00000000' + CONVERT(CHAR, R034FUN.NumCPF),4,3) + '.' + SUBSTRING('00000000' + CONVERT(CHAR, R034FUN.NumCPF),7,3) + '-' + SUBSTRING('00000000' + CONVERT(CHAR, R034FUN.NumCPF),10,2))

WHEN ((R034FUN.NumCPF <> 0) AND (LEN(R034FUN.NumCPF)= 4)) THEN (SUBSTRING('0000000' + CONVERT(CHAR, R034FUN.NumCPF),1,3) + '.' + SUBSTRING('0000000' + CONVERT(CHAR, R034FUN.NumCPF),4,3) + '.' + SUBSTRING('0000000' + CONVERT(CHAR, R034FUN.NumCPF),7,3) + '-' + SUBSTRING('0000000' + CONVERT(CHAR, R034FUN.NumCPF),10,2))

WHEN ((R034FUN.NumCPF <> 0) AND (LEN(R034FUN.NumCPF)= 5)) THEN (SUBSTRING('000000' + CONVERT(CHAR, R034FUN.NumCPF),1,3) + '.' + SUBSTRING('000000' + CONVERT(CHAR, R034FUN.NumCPF),4,3) + '.' + SUBSTRING('000000' + CONVERT(CHAR, R034FUN.NumCPF),7,3) + '-' + SUBSTRING('000000' + CONVERT(CHAR, R034FUN.NumCPF),10,2))

WHEN ((R034FUN.NumCPF <> 0) AND (LEN(R034FUN.NumCPF)= 6)) THEN (SUBSTRING('00000' + CONVERT(CHAR, R034FUN.NumCPF),1,3) + '.' + SUBSTRING('00000' + CONVERT(CHAR, R034FUN.NumCPF),4,3) + '.' + SUBSTRING('00000' + CONVERT(CHAR, R034FUN.NumCPF),7,3) + '-' + SUBSTRING('00000' + CONVERT(CHAR, R034FUN.NumCPF),10,2))

WHEN ((R034FUN.NumCPF <> 0) AND (LEN(R034FUN.NumCPF)= 7)) THEN (SUBSTRING('0000' + CONVERT(CHAR, R034FUN.NumCPF),1,3) + '.' + SUBSTRING('0000' + CONVERT(CHAR, R034FUN.NumCPF),4,3) + '.' + SUBSTRING('0000' + CONVERT(CHAR, R034FUN.NumCPF),7,3) + '-' + SUBSTRING('0000' + CONVERT(CHAR, R034FUN.NumCPF),10,2))

WHEN ((R034FUN.NumCPF <> 0) AND (LEN(R034FUN.NumCPF)= 8)) THEN (SUBSTRING('000' + CONVERT(CHAR, R034FUN.NumCPF),1,3) + '.' + SUBSTRING('000' + CONVERT(CHAR, R034FUN.NumCPF),4,3) + '.' + SUBSTRING('000' + CONVERT(CHAR, R034FUN.NumCPF),7,3) + '-' + SUBSTRING('000' + CONVERT(CHAR, R034FUN.NumCPF),10,2))

WHEN ((R034FUN.NumCPF <> 0) AND (LEN(R034FUN.NumCPF)= 9)) THEN (SUBSTRING('00' + CONVERT(CHAR, R034FUN.NumCPF),1,3) + '.' + SUBSTRING('00' + CONVERT(CHAR, R034FUN.NumCPF),4,3) + '.' + SUBSTRING('00' + CONVERT(CHAR, R034FUN.NumCPF),7,3) + '-' + SUBSTRING('00' + CONVERT(CHAR, R034FUN.NumCPF),10,2))

WHEN ((R034FUN.NumCPF <> 0) AND (LEN(R034FUN.NumCPF)= 10)) THEN (SUBSTRING('0' + CONVERT(CHAR, R034FUN.NumCPF),1,3) + '.' + SUBSTRING('0' + CONVERT(CHAR, R034FUN.NumCPF),4,3) + '.' + SUBSTRING('0' + CONVERT(CHAR, R034FUN.NumCPF),7,3) + '-' + SUBSTRING('0' + CONVERT(CHAR, R034FUN.NumCPF),10,2))

WHEN ((R034FUN.NumCPF <> 0) AND (LEN(R034FUN.NumCPF)= 11)) THEN (SUBSTRING(CONVERT(CHAR, R034FUN.NumCPF),1,3) + '.' + SUBSTRING(CONVERT(CHAR, R034FUN.NumCPF),4,3) + '.' + SUBSTRING(CONVERT(CHAR, R034FUN.NumCPF),7,3) + '-' + SUBSTRING(CONVERT(CHAR, R034FUN.NumCPF),10,2))

ELSE ''

END 'CPF', ';' ,

NULL 'RNE', ';' ,

CAST(R034FUN.NUMCAD AS VARCHAR(200)) 'Matrícula', ';' ,

CAST(R034MAT.MATESO AS VARCHAR(200)) 'Matrícula do RH', ';' ,

NULL 'Vínculo', ';' ,

NULL 'BR/PDH', ';' ,

NULL 'Reg. Revez', ';' ,

CASE WHEN Convert(varchar(10), R034FUN.DatAdm, 103) = '31/12/1900' THEN null ELSE Convert(varchar(10), R034FUN.DatAdm, 103) END 'Admissão', ';' ,

NULL 'Demissão', ';' ,

NULL 'Observação', ';' ,

R033PES.EmaPar 'Email', ';' ,

null 'Endereço', ';' ,

null 'Cidade', ';' ,

null 'Bairro', ';' ,

null 'Estado', ';' ,

null 'País', ';' ,

null 'CEP', ';' ,

null 'Telefone', ';' ,

null 'Remuneração Mensal', ';' ,

null 'Nome da mãe', ';' ,

null 'Filiação à Previdência Social', ';' ,

case

WHEN R034FUN.estciv in (1, 2) then R034FUN.estciv

WHEN R034FUN.estciv = 3 then 4

when R034FUN.estciv = 4 then 3

ELSE 5

END 'Estado Civil', ';' ,

NULL 'Aposentado', ';' ,

R033PES.NumEle 'Título de eleitor', ';' ,

R033PES.NumCnh 'CNH', ';' ,

CASE WHEN Convert(varchar(10), R033PES.VenCnh, 103) = '31/12/1900' THEN null ELSE Convert(varchar(10), R033PES.VenCnh, 103) END 'Validade CNH', ';' ,

CASE WHEN Convert(varchar(10), R038HCA.DatAlt, 103) = '31/12/1900' THEN null ELSE Convert(varchar(10), R038HCA.DatAlt, 103) END 'Inicio', ';' ,

'' 'Fim', ';' ,

R016HIE.CodLoc 'Código setor', ';' ,

R016ORN.NomLoc 'Nome setor', ';' ,

null 'Cod Setor Desenvolvido', ';' ,

null 'Setor Desenvolvido', ';' ,

null 'Local GHE', ';' ,

null 'Código GHE', ';' ,

null 'GHE', ';' ,

R034FUN.CodCar 'Código cargo', ';' ,

R024CAR.TitRed 'Cargo', ';' ,

null 'Cod Cargo Desenvolvido', ';' ,

null 'Cargo Deselvolvido', ';' ,

null 'CBO', ';' ,

null 'Descrição Sumária do Cargo', ';' ,

null 'Descrição Detalhada do Cargo', ';' ,

null 'Código Posição Trabalho', ';' ,

null 'Posição Trabalho', ';' ,

null 'Código RFID', ';' ,

null 'Código de BARRAS', ';' ,

null 'Turno', ';' ,

null 'Turno Jornada', ';' ,

null 'Grupo Sanguíneo', ';' ,

null 'Deficiência', ';' ,

CAST(R034FUN.NUMEMP AS VARCHAR(200)) + '/' + CAST(R034FUN.CODFIL AS VARCHAR(200)) 'Código Integração Empresa', ';' ,

null 'Descrição Ambiente', ';' ,

CAST(R034FUN.NUMEMP AS VARCHAR(200)) + '/' + CAST(R034FUN.CODFIL AS VARCHAR(200)) + '/' + CAST(R034FUN.TIPCOL AS VARCHAR(200)) + '/' + CAST(R034FUN.NUMCAD AS VARCHAR(200)) 'Código Integração Empregado'

FROM R034FUN

inner join R030EMP on (R030EMP.NumEmp = R034FUN.NumEmp)

inner join R030CAR on (R030CAR.NumEmp = R034FUN.NumEmp AND R030CAR.EstCar = R034FUN.EstCar AND R030CAR.DatAlt = (SELECT MAX(C.DATALT) FROM R030CAR C WHERE R030EMP.NumEmp = C.NumEmp AND C.DatAlt <= GETDATE()))

inner join R024CAR on (R024CAR.EstCar = R034FUN.EstCar AND R024CAR.CodCar = R034FUN.CodCar)

inner join R038HCA on (R038HCA.NumEmp = R034FUN.NumEmp AND R038HCA.TipCol = R034FUN.TipCol AND R038HCA.NumCad = R034FUN.NumCad AND R038HCA.DatAlt = (SELECT MAX(H.DATALT) FROM R038HCA H WHERE R034FUN.NumEmp = H.NumEmp AND R034FUN.TipCol = H.TipCol AND R034FUN.NumCad = H.NumCad AND H.DatAlt <= GETDATE()))

inner join R030ORG on (R030ORG.NumEmp = R034FUN.NumEmp AND R030ORG.TabOrg = R034FUN.TabOrg AND R030ORG.DatAlt = (SELECT MAX(O.DATALT) FROM R030ORG O WHERE R030EMP.NumEmp = O.NumEmp AND O.DatAlt <= GETDATE()))

inner join R016ORN on (R016ORN.TabOrg = R034FUN.TabOrg AND R016ORN.NumLoc = R034FUN.NumLoc)

inner join R016HIE on (R016HIE.TabOrg = R034FUN.TabOrg AND R016HIE.NumLoc = R034FUN.NumLoc AND R016HIE.DatIni = (SELECT MAX(H.DATINI) FROM R016HIE H WHERE R034FUN.TabOrg = H.TabOrg AND R034FUN.NumLoc = H.NumLoc AND H.DatIni <= GETDATE()))

inner join R038HLO on (R038HLO.NumEmp = R034FUN.NumEmp AND R038HLO.TipCol = R034FUN.TipCol AND R038HLO.NumCad = R034FUN.NumCad AND R038HLO.DatAlt = (SELECT MAX(H.DATALT) FROM R038HLO H WHERE R034FUN.NumEmp = H.NumEmp AND R034FUN.TipCol = H.TipCol AND R034FUN.NumCad = H.NumCad AND H.DatAlt <= GETDATE()))

inner join R030FIL on (R030FIL.NumEmp = R034FUN.NumEmp AND R030FIL.CodFil = R034FUN.CodFil)

inner join R010SIT on (R010SIT.CodSit = R034FUN.SitAfa)

left join R033PES on (R033PES.CodPES = R034FUN.CodPes)

left join R034MAT on (R034MAT.NumEmp = R034FUN.NumEmp AND R034MAT.TipCol = R034FUN.TipCol AND R034MAT.NumCad = R034FUN.TipCol)

left join R023NAC on (R023NAC.CodNac = R034FUN.CodNac)

left join R038AFA on (R038AFA.numemp = R034FUN.NumEmp AND R038AFA.TipCol = R034FUN.tipcol AND R038AFA.NumCad = R034FUN.numcad AND r038afa.sitafa in (SELECT S.codsit FROM r010sit S WHERE S.tipsit = 7))

WHERE

R034FUN.TipCol IN (1,2)

ORDER BY 1,3,4

Observação

O comando acima considera também os colaboradores que estão demitidos.

Oracle

SELECT
    1 AS "TIPO", ';' AS ";",
    NULL AS "Unidade Marítima", ';' AS ";",
    CASE R030FIL.TipIns
        WHEN 1 THEN
            CASE
                WHEN (REPLACE(R030FIL.NumCGC, '.', '') IS NULL) THEN NULL
                ELSE (SUBSTR(REPLACE(R030FIL.NumCGC, '.', ''), 1, 2) || '.' ||
                      SUBSTR(REPLACE(R030FIL.NumCGC, '.', ''), 3, 3) || '.' ||
                      SUBSTR(REPLACE(R030FIL.NumCGC, '.', ''), 6, 3) || '/' ||
                      SUBSTR(REPLACE(R030FIL.NumCGC, '.', ''), 9, 4) || '-' ||
                      SUBSTR(REPLACE(R030FIL.NumCGC, '.', ''), 13, 2))
            END
        WHEN 2 THEN
            CASE
                WHEN (REPLACE(R030FIL.NumCGC, '.', '') IS NULL) THEN NULL
                ELSE (SUBSTR(REPLACE(R030FIL.NumCGC, '.', ''), 1, 2) || '.' ||
                      SUBSTR(REPLACE(R030FIL.NumCGC, '.', ''), 3, 3) || '.' ||
                      SUBSTR(REPLACE(R030FIL.NumCGC, '.', ''), 6, 5) || '/' ||
                      SUBSTR(REPLACE(R030FIL.NumCGC, '.', ''), 11, 2))
            END
        WHEN 3 THEN
            CASE
                WHEN (REPLACE(R030FIL.NumCGC, '.', '') IS NULL) THEN NULL
                ELSE (SUBSTR(REPLACE(R030FIL.NumCGC, '.', ''), 1, 3) || '.' ||
                      SUBSTR(REPLACE(R030FIL.NumCGC, '.', ''), 4, 3) || '.' ||
                      SUBSTR(REPLACE(R030FIL.NumCGC, '.', ''), 7, 3) || '-' ||
                      SUBSTR(REPLACE(R030FIL.NumCGC, '.', ''), 10, 2))
            END
        ELSE NULL
    END AS "CNPJ/CPF/CEI/CAEPF", ';' AS ";",
    R034FUN.NomFun AS "Nome", ';' AS ";",
    CASE
        WHEN R023NAC.PaiRfb = 105 THEN 'BRA'
        ELSE 'ES'
    END AS "Nacionalidade", ';' AS ";",
    TO_CHAR(R034FUN.DatNas, 'DD/MM/YYYY') AS "Nascimento", ';' AS ";",
    R034FUN.TipSex AS "Sexo", ';' AS ";",
    CASE
        WHEN TO_CHAR(R034FUN.NumCtp) = '0' THEN NULL
        ELSE TO_CHAR(R034FUN.NumCtp)
    END AS "CTPS", ';' AS ";",
    CASE
        WHEN TO_CHAR(R034FUN.SerCtp) = '0' THEN NULL
        ELSE TO_CHAR(R034FUN.SerCtp)
    END AS "Série", ';' AS ";",
    CASE
        WHEN TO_CHAR(R034FUN.DexCtp, 'DD/MM/YYYY') = '31/12/1900' THEN NULL
        ELSE TO_CHAR(R034FUN.DexCtp, 'DD/MM/YYYY')
    END AS "Data de emissão", ';' AS ";",
    R034FUN.EstCtp AS "UF emissão", ';' AS ";",
    R033PES.numcid AS "Identidade", ';' AS ";",
    R033PES.EmiCid AS "Orgão Exp.", ';' AS ";",
    CASE
        WHEN TO_CHAR(R033PES.DexCid, 'DD/MM/YYYY') = '31/12/1900' THEN NULL
        ELSE TO_CHAR(R033PES.DexCid, 'DD/MM/YYYY')
    END AS "DT emissão", ';' AS ";",
    R033PES.EstCid AS "UF emissão", ';' AS ";",
    TO_CHAR(R033PES.NumPis) AS "NIT (PIS/PASEP)", ';' AS ";",
    CASE
        WHEN R034FUN.NumCPF = 0 THEN ' '
        WHEN R034FUN.NumCPF IS NULL THEN ' '
        ELSE
            CASE LENGTH(R034FUN.NumCPF)
                WHEN 1 THEN SUBSTR('0000000000' || TO_CHAR(R034FUN.NumCPF), 1, 3) || '.' ||
                           SUBSTR('0000000000' || TO_CHAR(R034FUN.NumCPF), 4, 3) || '.' ||
                           SUBSTR('0000000000' || TO_CHAR(R034FUN.NumCPF), 7, 3) || '-' ||
                           SUBSTR('0000000000' || TO_CHAR(R034FUN.NumCPF), 10, 2)
                WHEN 2 THEN SUBSTR('000000000' || TO_CHAR(R034FUN.NumCPF), 1, 3) || '.' ||
                           SUBSTR('000000000' || TO_CHAR(R034FUN.NumCPF), 4, 3) || '.' ||
                           SUBSTR('000000000' || TO_CHAR(R034FUN.NumCPF), 7, 3) || '-' ||
                           SUBSTR('000000000' || TO_CHAR(R034FUN.NumCPF), 10, 2)
                WHEN 3 THEN SUBSTR('00000000' || TO_CHAR(R034FUN.NumCPF), 1, 3) || '.' ||
                           SUBSTR('00000000' || TO_CHAR(R034FUN.NumCPF), 4, 3) || '.' ||
                           SUBSTR('00000000' || TO_CHAR(R034FUN.NumCPF), 7, 3) || '-' ||
                           SUBSTR('00000000' || TO_CHAR(R034FUN.NumCPF), 10, 2)
                WHEN 4 THEN SUBSTR('0000000' || TO_CHAR(R034FUN.NumCPF), 1, 3) || '.' ||
                           SUBSTR('0000000' || TO_CHAR(R034FUN.NumCPF), 4, 3) || '.' ||
                           SUBSTR('0000000' || TO_CHAR(R034FUN.NumCPF), 7, 3) || '-' ||
                           SUBSTR('0000000' || TO_CHAR(R034FUN.NumCPF), 10, 2)
                WHEN 5 THEN SUBSTR('000000' || TO_CHAR(R034FUN.NumCPF), 1, 3) || '.' ||
                           SUBSTR('000000' || TO_CHAR(R034FUN.NumCPF), 4, 3) || '.' ||
                           SUBSTR('000000' || TO_CHAR(R034FUN.NumCPF), 7, 3) || '-' ||
                           SUBSTR('000000' || TO_CHAR(R034FUN.NumCPF), 10, 2)
                WHEN 6 THEN SUBSTR('00000' || TO_CHAR(R034FUN.NumCPF), 1, 3) || '.' ||
                           SUBSTR('00000' || TO_CHAR(R034FUN.NumCPF), 4, 3) || '.' ||
                           SUBSTR('00000' || TO_CHAR(R034FUN.NumCPF), 7, 3) || '-' ||
                           SUBSTR('00000' || TO_CHAR(R034FUN.NumCPF), 10, 2)
                WHEN 7 THEN SUBSTR('0000' || TO_CHAR(R034FUN.NumCPF), 1, 3) || '.' ||
                           SUBSTR('0000' || TO_CHAR(R034FUN.NumCPF), 4, 3) || '.' ||
                           SUBSTR('0000' || TO_CHAR(R034FUN.NumCPF), 7, 3) || '-' ||
                           SUBSTR('0000' || TO_CHAR(R034FUN.NumCPF), 10, 2)
                WHEN 8 THEN SUBSTR('000' || TO_CHAR(R034FUN.NumCPF), 1, 3) || '.' ||
                           SUBSTR('000' || TO_CHAR(R034FUN.NumCPF), 4, 3) || '.' ||
                           SUBSTR('000' || TO_CHAR(R034FUN.NumCPF), 7, 3) || '-' ||
                           SUBSTR('000' || TO_CHAR(R034FUN.NumCPF), 10, 2)
                WHEN 9 THEN SUBSTR('00' || TO_CHAR(R034FUN.NumCPF), 1, 3) || '.' ||
                           SUBSTR('00' || TO_CHAR(R034FUN.NumCPF), 4, 3) || '.' ||
                           SUBSTR('00' || TO_CHAR(R034FUN.NumCPF), 7, 3) || '-' ||
                           SUBSTR('00' || TO_CHAR(R034FUN.NumCPF), 10, 2)
                WHEN 10 THEN SUBSTR('0' || TO_CHAR(R034FUN.NumCPF), 1, 3) || '.' ||
                            SUBSTR('0' || TO_CHAR(R034FUN.NumCPF), 4, 3) || '.' ||
                            SUBSTR('0' || TO_CHAR(R034FUN.NumCPF), 7, 3) || '-' ||
                            SUBSTR('0' || TO_CHAR(R034FUN.NumCPF), 10, 2)
                WHEN 11 THEN SUBSTR(TO_CHAR(R034FUN.NumCPF), 1, 3) || '.' ||
                            SUBSTR(TO_CHAR(R034FUN.NumCPF), 4, 3) || '.' ||
                            SUBSTR(TO_CHAR(R034FUN.NumCPF), 7, 3) || '-' ||
                            SUBSTR(TO_CHAR(R034FUN.NumCPF), 10, 2)
                ELSE ' '
            END
    END AS "CPF", ';' AS ";",
	NULL AS "RNE", ';' ,
	TO_CHAR(R034FUN.NUMCAD) AS "Matrícula", ';' ,
	TO_CHAR(R034MAT.MATESO) AS "Matrícula do RH", ';' ,
	NULL AS "Vínculo", ';' ,
	NULL AS "BR/PDH", ';' ,
	NULL AS "Reg. Revez", ';' ,
	CASE 
		WHEN TO_CHAR(R034FUN.DatAdm, 'DD/MM/YYYY') = '31/12/1900' THEN NULL 
		ELSE TO_CHAR(R034FUN.DatAdm, 'DD/MM/YYYY') 
	END AS "Admissão", ';' ,
	NULL AS "Demissão", ';' ,
	NULL AS "Observação", ';' ,
	R033PES.EmaPar AS "Email", ';' ,
	NULL AS "Endereço", ';' ,
	NULL AS "Cidade", ';' ,
	NULL AS "Bairro", ';' ,
	NULL AS "Estado", ';' ,
	NULL AS "País", ';' ,
	NULL AS "CEP", ';' ,
	NULL AS "Telefone", ';' ,
	NULL AS "Remuneração Mensal", ';' ,
	NULL AS "Nome da mãe", ';' ,
	NULL AS "Filiação à Previdência Social", ';' ,
	CASE 
		WHEN R034FUN.estciv IN (1, 2) THEN R034FUN.estciv
		WHEN R034FUN.estciv = 3 THEN 4
		WHEN R034FUN.estciv = 4 THEN 3
		ELSE 5
	END AS "Estado Civil", ';' ,
	NULL AS "Aposentado", ';' ,
	R033PES.NumEle AS "Título de eleitor", ';' ,
	R033PES.NumCnh AS "CNH", ';' ,
	CASE 
		WHEN TO_CHAR(R033PES.VenCnh, 'DD/MM/YYYY') = '31/12/1900' THEN NULL 
		ELSE TO_CHAR(R033PES.VenCnh, 'DD/MM/YYYY') 
	END AS "Validade CNH", ';' ,
	CASE 
		WHEN TO_CHAR(R038HCA.DatAlt, 'DD/MM/YYYY') = '31/12/1900' THEN NULL 
		ELSE TO_CHAR(R038HCA.DatAlt, 'DD/MM/YYYY') 
	END AS "Inicio", ';' ,
	'' AS "Fim", ';' ,
	R016HIE.CodLoc AS "Código setor", ';' ,
	R016ORN.NomLoc AS "Nome setor", ';' ,
	NULL AS "Cod Setor Desenvolvido", ';' ,
	NULL AS "Setor Desenvolvido", ';' ,
	NULL AS "Local GHE", ';' ,
	NULL AS "Código GHE", ';' ,
	NULL AS "GHE", ';' ,
	R034FUN.CodCar AS "Código cargo", ';' ,
	R024CAR.TitRed AS "Cargo", ';' ,
	NULL AS "Cod Cargo Desenvolvido", ';' ,
	NULL AS "Cargo Deselvolvido", ';' ,
	NULL AS "CBO", ';' ,
	NULL AS "Descrição Sumária do Cargo", ';' ,
	NULL AS "Descrição Detalhada do Cargo", ';' ,
	NULL AS "Código Posição Trabalho", ';' ,
	NULL AS "Posição Trabalho", ';' ,
	NULL AS "Código RFID", ';' ,
	NULL AS "Código de BARRAS", ';' ,
	NULL AS "Turno", ';' ,
	NULL AS "Turno Jornada", ';' ,
	NULL AS "Grupo Sanguíneo", ';' ,
	NULL AS "Deficiência", ';' ,
	TO_CHAR(R034FUN.NUMEMP) || '/' || TO_CHAR(R034FUN.CODFIL) AS "Código Integração Empresa", ';' ,
	NULL AS "Descrição Ambiente", ';' ,
	TO_CHAR(R034FUN.NUMEMP) || '/' || TO_CHAR(R034FUN.CODFIL) || '/' || TO_CHAR(R034FUN.TIPCOL) || '/' || TO_CHAR(R034FUN.NUMCAD) AS "Código Integração Empregado"			
	FROM R034FUN
		inner join R030EMP on (R030EMP.NumEmp = R034FUN.NumEmp)
		inner join R030CAR on (R030CAR.NumEmp = R030EMP.NumEmp AND R030CAR.EstCar = R034FUN.EstCar AND R030CAR.DatAlt = (SELECT MAX(C.DATALT) FROM R030CAR C WHERE R030EMP.NumEmp = C.NumEmp AND C.DatAlt <= SYSDATE))
		inner join R024CAR on (R024CAR.EstCar = R034FUN.EstCar AND R024CAR.CodCar = R034FUN.CodCar)
		inner join R038HCA on (R038HCA.NumEmp = R034FUN.NumEmp AND R038HCA.TipCol = R034FUN.TipCol AND R038HCA.NumCad = R034FUN.NumCad AND R038HCA.DatAlt = (SELECT MAX(H.DATALT) FROM R038HCA H WHERE R034FUN.NumEmp = H.NumEmp AND R034FUN.TipCol = H.TipCol AND R034FUN.NumCad = H.NumCad AND H.DatAlt <= SYSDATE))
		inner join R030ORG on (R030ORG.NumEmp = R034FUN.NumEmp AND R030ORG.TabOrg = R034FUN.TabOrg AND R030ORG.DatAlt = (SELECT MAX(O.DATALT) FROM R030ORG O WHERE R030EMP.NumEmp = O.NumEmp AND O.DatAlt <= SYSDATE))
		inner join R016ORN on (R016ORN.TabOrg = R034FUN.TabOrg AND R016ORN.NumLoc = R034FUN.NumLoc)
		inner join R016HIE on (R016HIE.TabOrg = R034FUN.TabOrg AND R016HIE.NumLoc = R034FUN.NumLoc AND R016HIE.DatIni = (SELECT MAX(H.DATINI) FROM R016HIE H WHERE R034FUN.TabOrg = H.TabOrg AND R034FUN.NumLoc = H.NumLoc AND H.DatIni <= SYSDATE))
		inner join R038HLO on (R038HLO.NumEmp = R034FUN.NumEmp AND R038HLO.TipCol = R034FUN.TipCol AND R038HLO.NumCad = R034FUN.NumCad AND R038HLO.DatAlt = (SELECT MAX(H.DATALT) FROM R038HLO H WHERE R034FUN.NumEmp = H.NumEmp AND R034FUN.TipCol = H.TipCol AND R034FUN.NumCad = H.NumCad AND H.DatAlt <= SYSDATE))
		inner join R030FIL on (R030FIL.NumEmp = R034FUN.NumEmp AND R030FIL.CodFil = R034FUN.CodFil)
		inner join R010SIT on (R010SIT.CodSit = R034FUN.SitAfa)
		left join R033PES on (R033PES.CodPES = R034FUN.CodPes)
		left join R034MAT on (R034MAT.NumEmp = R034FUN.NumEmp AND R034MAT.TipCol = R034FUN.TipCol AND R034MAT.NumCad = R034FUN.TipCol)
		left join R023NAC on (R023NAC.CodNac = R034FUN.CodNac)
		left join R038AFA on (R038AFA.numemp = R034FUN.NumEmp AND R034FUN.TipCol = R038AFA.tipcol AND R034FUN.NumCad = R038AFA.numcad AND r038afa.sitafa IN (SELECT S.codsit FROM r010sit S WHERE S.tipsit = 7))
	WHERE
		R034FUN.TipCol IN (1,2)
	ORDER BY 1,3,4

Observação

O comando acima considera também os colaboradores que estão demitidos.

PORTAL DE DOCUMENTAÇÃO
Gestão de Pessoas \| HCM - senior X