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

Saneamento da base do prestador — NexoCS

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. Os comandos estão divididos por tipo de informação (colaboradores, filiais, setores, cargos e postos de trabalho).

Instruções:

Use os comandos desta página para consultar os registros no banco de dados.
Exporte os resultados da consulta para um arquivo no formato .XLS ou .CSV.
Envie o arquivo exportado para o prestador SST.

Ao receber o arquivo exportado, o prestador SST deverá fazer o saneamento da base de dados no sistema NexoCS. Para fazer o saneamento, o prestador pode optar entre:

importar o arquivo; ou
ajustar manualmente os registros existentes no sistema.

Colaboradores

Comandos do banco de dados para saneamento da base (SQL Server)

SELECT

R034FUN.NomFun 'Nome funcionário',

CASE

WHEN R034FUN.TipCol = 1 THEN '1-Empregado'

WHEN R034FUN.TipCol = 2 THEN '2-Terceiro'

WHEN R034FUN.TipCol = 3 THEN '3-Parceiro'

ELSE 'Valor inválido'

END 'Tipo',

R034FUN.NumCad 'Código cadastro (matrícula)',

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',

R034FUN.NumEmp 'Código empresa',

R030EMP.NomEmp 'Razão social empresa',

R034FUN.CodFil 'Código filial',

R030FIL.NomFil 'Nome filial',

R030ORG.TabOrg 'Tabela organograma setor',

R016HIE.CodLoc 'Código setor',

R016ORN.NomLoc 'Nome setor',

R030CAR.EstCar 'Estrutura cargos',

R034FUN.CodCar 'Código cargo',

R024CAR.TitRed 'Nome cargo',

R010SIT.DesSit 'Situação',

CASE

WHEN (convert(varchar, R034FUN.DatAdm, 103) = '31/12/1900') THEN null

ELSE (convert(varchar, R034FUN.DatAdm, 103))

END 'Data admissão',

CASE

WHEN (convert(varchar, R038AFA.DatAfa, 103) = '31/12/1900') THEN null

ELSE (convert(varchar, R038AFA.DatAfa, 103))

END 'Data demissão'

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 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 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 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,2,3,4

Observação

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

Filiais

Comandos do banco de dados para saneamento da base (SQL Server)

SELECT

R030FIL.NumEmp 'Código empresa',

R030EMP.NomEmp 'Nome empresa',

R030FIL.CodFil 'Código filial',

R030FIL.NomFil 'Nome filial',

R030FIL.RazSoc 'Razão social filial',

CASE R030FIL.TipIns

WHEN 1 THEN 'CNPJ'

WHEN 2 THEN 'CEI'

WHEN 3 THEN 'CPF'

ELSE null

END 'Tipo inscrição',

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 'Número inscrição',

CASE

WHEN (R030FIL.InsCei = 0) THEN null

ELSE R030FIL.InsCei

END 'Número inscrição CEI',

CASE

WHEN (R030FIL.NumCno = 0) THEN null

ELSE R030FIL.NumCno

END 'Número inscrição CNO',

CASE

WHEN (R030FIL.NCaepf = 0) THEN null

ELSE R030FIL.NCaepf

END 'Número inscrição CAEPF'

FROM R030FIL, R030EMP

WHERE

R030Emp.NumEmp = R030FIL.NumEmp

ORDER BY 1,3,4

Setores (Locais)

Comandos do banco de dados para saneamento da base (SQL Server)

SELECT DISTINCT

R016HIE.TabOrg 'Tabela organograma',

R016HIE.CodLoc 'Código setor',

R016ORN.NomLoc 'Nome setor'

FROM R016ORN, R016HIE, R034FUN, R010SIT

WHERE R016ORN.TabOrg = R016HIE.TabOrg

AND R016ORN.NumLoc = R016HIE.NumLoc

AND R016HIE.DatIni = (SELECT MAX(DATINI) FROM R016HIE H

WHERE R016ORN.TabOrg = H.TabOrg

AND R016ORN.NumLoc = H.NumLoc

AND H.DatIni <= GETDATE())

AND (R016ORN.DatExt >= getdate() OR CAST(R016ORN.DatExt AS DATE) = '1900-12-31')

AND R016ORN.taborg = R034FUN.taborg

AND R016HIE.numloc = R034FUN.numloc

AND R034FUN.SITAFA = R010SIT.CODSIT

AND R010SIT.TIPSIT <> 7

ORDER BY 1,2,3

Cargos

Comandos do banco de dados para saneamento da base (SQL Server)

SELECT DISTINCT

R024CAR.EstCar 'Estrutura cargo',

R024CAR.CodCar 'Código cargo',

R024CAR.TitRed 'Título cargo'

FROM R024CAR, R034FUN, R010SIT

WHERE (R024CAR.DatExt >= getdate() OR CAST(R024CAR.DatExt AS DATE) = '1900-12-31')

AND R024CAR.ESTCAR = R034FUN.ESTCAR

AND R024CAR.CODCAR = R034FUN.CODCAR

AND R034FUN.SITAFA = R010SIT.CODSIT

AND R010SIT.TIPSIT <> 7

ORDER BY 1,2,3

Postos de trabalho

Comandos do banco de dados para saneamento da base (SQL Server)

SELECT DISTINCT

R017POS.EstPos 'Estrutura posto',

R017POS.PosTra 'Código posto',

R017POS.DesRed 'Descrição posto'

FROM R017POS, R034FUN, R010SIT

WHERE (R017POS.DatExt >= getdate() OR CAST(R017POS.DatExt AS DATE) = '1900-12-31')

AND R017POS.ESTPOS = R034FUN.ESTPOS

AND R017POS.POSTRA = R034FUN.POSTRA

AND R034FUN.SITAFA = R010SIT.CODSIT

AND R010SIT.TIPSIT <> 7

ORDER BY 1,2,3

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