Saneamento da base do prestador — MedNet
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, cargos, setores e filiais).
Colaboradores
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 |
Esse comando considera colaboradores em situação demitido.
Filiais
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
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
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 |
Importante
Exportar o resultado do comando SQL para planilha XLS ou arquivo CSV. Em seguida, enviar o arquivo exportado ao prestador SST para que avalie se o saneamento da base de dados no sistema MedNet deverá ser feito via importação do arquivo ou através de ajustes específicos (manuais) nos registros já cadastrados na MedNet.
English
Español
