Gestión del Capital Humano - senior X - Manual del Usuario > Integrador SST > Saneamiento de la base del prestador > Software SGG

Saneamiento de la base de proveedores —software SGG

Estos son los comandos paraSELECTeso puede serejecutar en el sistema de Senior, para facilitar la extracción de datos. Los comandos se dividen por tipo de información (empleados, cargos, sectores y sucursales).

Se for necessário extrair informações diferentes dos exemplos, você pode adaptar os comandos para buscar as informações que não constam nos exemplos.

Colaboradores

Ligação dos registros

La vinculación de los registros la realiza elregistro de empleados:

Registro en el sistema de Senior Registro con el proveedor de SST Ejemplo

R034FUN.NumEmp
R034FUN.CodFil
R034FUN.TipCol
R034FUN.NumCad

Campo: Código RH do COLABORADOR

Formato:

NumEmp-CodFil-TipCol-NumCad

SISTEMA DA SENIOR				SISTEMA DO PRESTADOR
Cadastro da empresa (`NumEmp`)	Código da filial (`CodFil`)	Tipo de colaborador (`TipCol`)	Cadastro do colaborador (`NumCad`)	Código RR.HH. COLABORADOR
1	dos	1	100	1-2-1-100

Nota

Los valores que componen el código HR del empleado deben estar separados porguiones (-), como se muestra en el ejemplo anterior.

Comandos do banco de dados para saneamento da base

servidor SQL

  SELECT R030FIL.NumEmp 'Código Empresa Senior', R030EMP.NomEmp 'Nome empresa Senior', R030FIL.CodFil 'Código filial Senior', R030FIL.NomFil 'Nome da Filial Senior', 'COD_RH_' + CAST(R034FUN.NumEmp AS VARCHAR(4)) + '-' + CAST(R034FUN.CodFil AS VARCHAR(4)) + '-' + CAST(R034FUN.TIPCOL AS VARCHAR(1)) + '-' + CAST(R034FUN.NUMCAD AS VARCHAR(15)) 'Funcionario SGG', 'COD_RH_' + CAST(R034FUN.NumEmp AS VARCHAR(4)) + '-' + CAST(R034FUN.CodFil AS VARCHAR(4)) 'Empresa SGG', CAST(R034FUN.NumEmp AS VARCHAR(4)) + '-' + CAST(R034FUN.CodFil AS VARCHAR(4)) + '-' + CAST(R034FUN.TIPCOL AS VARCHAR(1)) + '-' + CAST(R034FUN.NUMCAD AS VARCHAR(15)) 'Código RH', '' 'Matrícula RH', R033PES.NumCid 'RG', 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.NomFun 'Nome Funcionário', CASE WHEN (convert(varchar, R034FUN.DatAdm, 103) = '31/12/1900') THEN '' ELSE (convert(varchar, R034FUN.DatAdm, 103)) END 'Data Admissão', CASE WHEN (convert(varchar, R034FUN.DatNas, 103) = '31/12/1900') THEN '' ELSE (convert(varchar, R034FUN.DatNas, 103)) END 'Data Nascimento', R034FUN.TipSex 'Sexo', 'COD_RH_' + CAST(R034FUN.NumEmp AS VARCHAR(4)) + '-' + CAST(R034FUN.CodFil AS VARCHAR(4)) + '-' + CAST(R030ORG.TabOrg AS VARCHAR(200)) + '-' + CAST(R016HIE.CodLoc AS VARCHAR(200)) + '-' + CAST(R030CAR.EstCar AS VARCHAR(200)) + '-' + CAST(R034FUN.CodCar AS VARCHAR(200)) 'Id do Cargo', R024CAR.CodCb2 'CBO', '' 'Função', 'COD_RH_' + CAST(R034FUN.NumEmp AS VARCHAR(4)) + '-' + CAST(R034FUN.CodFil AS VARCHAR(4)) + '-' + CAST(R030ORG.TabOrg AS VARCHAR(200)) + '-' + CAST(R016HIE.CodLoc AS VARCHAR(200)) 'Id do Setor', R016ORN.NomLoc 'Setor', '' 'NIT', '' 'CTPS', '' 'Vínculo', '' 'Matrícula', '' 'Categoria', CASE WHEN (convert(varchar, R038AFA.DatAfa, 103) = '31/12/1900') or R038AFA.DatAfa is null THEN 'Ativo' ELSE 'Demitido' END 'Situação', CASE WHEN (convert(varchar, R038AFA.DatAfa, 103) = '31/12/1900') or R038AFA.DatAfa is null THEN '' ELSE (convert(varchar, R038AFA.DatAfa, 103)) END 'Data de Demissão', '' 'Descrição das atividades', '' 'Requisitos da Função', '' 'Recomendações da Função', '' 'Procedimentos em caso de acidentes', '' 'Responsabilidades do Empregado', '' 'Observações', '' 'Informações Adicionais', '' 'Fone-Comercial', '' 'Fone-Celular', '' 'Email', '' 'CEP', '' 'Logradouro', '' 'Número', '' 'Bairro', '' 'Cidade', '' 'Estado', '' 'Modelo OS', '' 'Campo 1', '' 'Campo 2', '' 'Campo 3', '' 'Campo 4', '' 'Campo 5', '' 'Campo 6', '' 'Campo 7', '' 'Campo 8', '' 'Campo 9', '' 'Campo 10', '' 'Candidato' 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,3,7

Observación

El comando anteriorconsidera también los empleados que sondespedido. Además, sólo considera los tiposColaboradores yLa tercera.

Oráculo

  SELECT R030FIL.NumEmp "Código Empresa Senior", R030EMP.NomEmp "Nome empresa Senior", R030FIL.CodFil "Código filial Senior", R030FIL.NomFil "Nome da Filial Senior", 'COD_RH_' || R034FUN.NumEmp || '-' || R034FUN.CodFil || '-' || R034FUN.TIPCOL || '-' || R034FUN.NUMCAD "Funcionario SGG", 'COD_RH_' || R034FUN.NumEmp || '-' || R034FUN.CodFil "Empresa SGG", R034FUN.NumEmp || '-' || R034FUN.CodFil || '-' || R034FUN.TIPCOL || '-' || R034FUN.NUMCAD "Código RH", '' "Matrícula RH", R033PES.NumCid "RG", DECODE(R034FUN.NumCPF, 0, ' ', 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)) "CPF", R034FUN.NomFun "Nome Funcionário", DECODE(TO_CHAR(R034FUN.DatAdm, 'DD/MM/YYYY'), '31/12/1900', null, TO_CHAR(R034FUN.DatAdm, 'DD/MM/YYYY')) "Data Admissão", DECODE(TO_CHAR(R034FUN.DatNas, 'DD/MM/YYYY'), '31/12/1900', null, TO_CHAR(R034FUN.DatNas, 'DD/MM/YYYY')) "Data Nascimento", R034FUN.TipSex "Sexo", 'COD_RH_' || R034FUN.NumEmp || '-' || R034FUN.CodFil || '-' || R030ORG.TabOrg || '-' || R016HIE.CodLoc || '-' || R030CAR.EstCar || '-' || R034FUN.CodCar "Id do Cargo", R024CAR.CodCb2 "CBO", '' "Função", 'COD_RH_' || R034FUN.NumEmp || '-' || R034FUN.CodFil || '-' || R030ORG.TabOrg || '-' || R016HIE.CodLoc "Id do Setor", R016ORN.NomLoc "Setor", '' "NIT", '' "CTPS", '' "Vínculo", '' "Matrícula", '' "Categoria", CASE WHEN (R038AFA.DATAFA = TO_DATE('1900-12-31', 'YYYY-MM-DD') or R038AFA.DatAfa is null) THEN 'Ativo' ELSE 'Demitido' END "Situação", DECODE(TO_CHAR(R038AFA.DATAFA, 'DD/MM/YYYY'), '31/12/1900', null, TO_CHAR(R038AFA.DATAFA, 'DD/MM/YYYY')) "Data de Demissão", '' "Descrição das atividades", '' "Requisitos da Função", '' "Recomendações da Função", '' "Procedimentos em acidentes", '' "Responsabilidades do Empregado", '' "Observações", '' "Informações Adicionais", '' "Fone-Comercial", '' "Fone-Celular", '' "Email", '' "CEP", '' "Logradouro", '' "Número", '' "Bairro", '' "Cidade", '' "Estado", '' "Modelo OS", '' "Campo 1", '' "Campo 2", '' "Campo 3", '' "Campo 4", '' "Campo 5", '' "Campo 6", '' "Campo 7", '' "Campo 8", '' "Campo 9", '' "Campo 10", '' "Candidato" 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 <= SYSDATE)) 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 <= 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 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,3,7

Observación

El comando anteriorconsidera también los empleados que sondespedido. Además, sólo considera los tiposColaboradores yLa tercera.

Posiciones

Ligação dos registros

La vinculación de los registros la realiza elcódigo de recursos humanos del trabajo:

Registro en el sistema de Senior Registro con el proveedor de SST Ejemplo

R030FIL.NumEmp
R030FIL.CodFil
R030ORG.TabOrg
R016HIE.CodLoc
R030CAR.EstCar
R034FUN.CodCar

Campo: Código RH do CARGO

Formato:

NumEmp-CodFil-TabOrg-CodLoc-Estcar-CodCar

SISTEMA DA SENIOR							SISTEMA DO PRESTADOR
Código da empresa (`NumEmp`)	Código da filial (`CodFil`)	Tabela de organograma (`TabOrg`)	Código do local (`CodLoc`)	Estrutura de cargos (`EstCar`)	Código do cargo (`CodCar`)	título profesional	POSICIÓN código HR
1	dos	10	1.01.02	5	30	ABOGADO	1-2-10-1.01.02-5-30

Nota

Los valores que componen el código HR del puesto deben estar separados porguiones (-), como se muestra en el ejemplo anterior.

Comandos do banco de dados para saneamento da base

servidor SQL

  SELECT DISTINCT R030FIL.NumEmp 'Código Empresa Senior', R030EMP.NomEmp 'Nome empresa Senior', R030FIL.CodFil 'Código filial Senior', R030FIL.NomFil 'Nome da Filial Senior', R030ORG.TabOrg 'Tabela de organograma Senior', R016HIE.CodLoc 'Código do setor Senior', R030CAR.EstCar 'Estrutura de cargo Senior', R034FUN.CodCar 'Código do cargo Senior', 'COD_RH_' + CAST(R034FUN.NumEmp AS VARCHAR(4)) + '-' + CAST(R034FUN.CodFil AS VARCHAR(4)) + '-' + CAST(R030ORG.TabOrg AS VARCHAR(200)) + '-' + CAST(R016HIE.CodLoc AS VARCHAR(200)) + '-' + CAST(R030CAR.EstCar AS VARCHAR(200)) + '-' + CAST(R034FUN.CodCar AS VARCHAR(200)) 'Cargo SGG', 'COD_RH_' + CAST(R034FUN.NumEmp AS VARCHAR(4)) + '-' + CAST(R034FUN.CodFil AS VARCHAR(4)) 'Empresa SGG', CAST(R034FUN.NumEmp AS VARCHAR(4)) + '-' + CAST(R034FUN.CodFil AS VARCHAR(4)) + '-' + CAST(R030ORG.TabOrg AS VARCHAR(200)) + '-' + CAST(R016HIE.CodLoc AS VARCHAR(200)) + '-' + CAST(R030CAR.EstCar AS VARCHAR(200)) + '-' + CAST(R034FUN.CodCar AS VARCHAR(200)) 'Código RH', '' 'Código eSocial', R024CAR.CodCb2 'CBO', R024CAR.TitRed 'Cargo', '' 'Função', '' 'Descrição das Atividades', '' 'Requisitos da Função', '' 'Recomendações da Função', '' 'Procedimentos em caso de acidentes', '' 'Responsabilidades do Empregado', '' 'Observações', '' 'Id do Setor', R016ORN.NomLoc 'Setor', '' 'Modelo OS', '' 'Campo 1', '' 'Campo 2', '' 'Campo 3', '' 'Campo 4', '' 'Campo 5', '' 'Campo 6', '' 'Campo 7', '' 'Campo 8', '' 'Campo 9', '' 'Campo 10' 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) AND (R024CAR.DatExt >= getdate() OR CAST(R024CAR.DatExt AS DATE) = '1900-12-31') ORDER BY 1,3,11

Observación

El comando anteriorno considera posiciones extintas. Además,considera solo puestos que tienen un empleado vinculado.

Oráculo

  SELECT DISTINCT R030FIL.NumEmp "Código Empresa Senior", R030EMP.NomEmp "Nome empresa Senior", R030FIL.CodFil "Código filial Senior", R030FIL.NomFil "Nome da Filial Senior", R030ORG.TabOrg "Tabela de organograma Senior", R016HIE.CodLoc "Código do setor Senior", R030CAR.EstCar "Estrutura de cargo Senior", R034FUN.CodCar "Código do cargo Senior", 'COD_RH_' || R034FUN.NumEmp || '-' || R034FUN.CodFil || '-' || R030ORG.TabOrg || '-' || R016HIE.CodLoc || '-' || R030CAR.EstCar || '-' || R034FUN.CodCar "Cargo SGG", 'COD_RH_' || R034FUN.NumEmp || '-' || R034FUN.CodFil "Empresa SGG", R034FUN.NumEmp || '-' || R034FUN.CodFil || '-' || R030ORG.TabOrg || '-' || R016HIE.CodLoc || '-' || R030CAR.EstCar || '-' || R034FUN.CodCar "Código RH", '' "Código eSocial", R024CAR.CodCb2 "CBO", R024CAR.TitRed "Cargo", '' "Função", '' "Descrição das Atividades", '' "Requisitos da Função", '' "Recomendações da Função", '' "Procedimentos em acidentes", '' "Responsabilidades do Empregado", '' "Observações", '' "Id do Setor", R016ORN.NomLoc "Setor", '' "Modelo OS", '' "Campo 1", '' "Campo 2", '' "Campo 3", '' "Campo 4", '' "Campo 5", '' "Campo 6", '' "Campo 7", '' "Campo 8", '' "Campo 9", '' "Campo 10" 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 <= SYSDATE)) 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 <= 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 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) AND (R024CAR.DatExt >= SYSDATE OR R024CAR.DatExt = TO_DATE('1900-12-31', 'YYYY-MM-DD')) ORDER BY 1,3,11

Observación

El comando anteriorno considera posiciones extintas. Además,considera solo puestos que tienen un empleado vinculado.

Sectores (Ubicaciones)

Ligação dos registros

La vinculación de los registros la realiza elcódigo de recursos humanos de la industria:

Registro en el sistema de Senior Registro con el proveedor de SST Ejemplo

R030FIL.NumEmp
R030FIL.CodFil
R030ORG.TabOrg
R016HIE.CodLoc

Campo: Código RH do SETOR

Formato:

NumEmp-CodFil-TabOrg-CodLoc

SISTEMA DA SENIOR					SISTEMA DO PRESTADOR
Código de empresa (NumEmp)	Código de sucursal (CodFil)	Tabla de organigrama (TabOrg)	Código de sector (CodLoc)	nombre del sector	SECTOR Código RRHH
1	dos	10	1.01.02	Auditoría	1-2-10-1.01.02

Nota

Los valores que componen el código HR del sector deben estar separados porguiones (-), como se muestra en el ejemplo anterior.

Comandos do banco de dados para saneamento da base

servidor SQL

  SELECT DISTINCT R030FIL.NumEmp 'Código Empresa Senior', R030EMP.NomEmp 'Nome empresa Senior', R030FIL.CodFil 'Código filial Senior', R030FIL.NomFil 'Nome da Filial Senior', R030ORG.TabOrg 'Tabela de organograma Senior', R016HIE.CodLoc 'Código do setor Senior', 'COD_RH_' + CAST(R034FUN.NumEmp AS VARCHAR(4)) + '-' + CAST(R034FUN.CodFil AS VARCHAR(4)) + '-' + CAST(R030ORG.TabOrg AS VARCHAR(200)) + '-' + CAST(R016HIE.CodLoc AS VARCHAR(200)) 'Setor SGG', 'COD_RH_' + CAST(R034FUN.NumEmp AS VARCHAR(4)) + '-' + CAST(R034FUN.CodFil AS VARCHAR(4)) 'Empresa SGG', CAST(R034FUN.NumEmp AS VARCHAR(4)) + '-' + CAST(R034FUN.CodFil AS VARCHAR(4)) + '-' + CAST(R030ORG.TabOrg AS VARCHAR(200)) + '-' + CAST(R016HIE.CodLoc AS VARCHAR(200)) 'Código RH', R016ORN.NomLoc 'Nome do setor', '' 'Local Ambiente', '' 'Tipo Pessoa', '' 'CNPJ', '' 'CPF', '' 'Tipo específico', '' 'Valor Tipo Específico', '' 'Razão Social/Estabel.   Terceiro', '' 'Nome Fantasia/Apelido Terceiro', '' 'Descrição do Ambiente', '' 'CNAE', '' 'Atividade Principal', '' 'Grau de Risco', '' 'CEP', '' 'Logradouro', '' 'Número', '' 'Bairro', '' 'Cidade', '' 'Estado' 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) AND 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') ORDER BY 1,3,9

Observación

El comando anteriorno considera sectores del organigrama vigente, que no estén extintos y que tengan un empleado vinculado yconsidera solo puestos que tienen un empleado vinculado.

Oráculo

  SELECT DISTINCT R030FIL.NumEmp "Código Empresa Senior", R030EMP.NomEmp "Nome empresa Senior", R030FIL.CodFil "Código filial Senior", R030FIL.NomFil "Nome da Filial Senior", R030ORG.TabOrg "Tabela de organograma Senior", R016HIE.CodLoc "Código do setor Senior", 'COD_RH_' || R034FUN.NumEmp || '-' || R034FUN.CodFil || '-' || R030ORG.TabOrg || '-' || R016HIE.CodLoc "Setor SGG", 'COD_RH_' || R034FUN.NumEmp || '-' || R034FUN.CodFil "Empresa SGG", R034FUN.NumEmp || '-' || R034FUN.CodFil || '-' || R030ORG.TabOrg || '-' || R016HIE.CodLoc "Código RH", R016ORN.NomLoc "Nome do setor", '' "Local Ambiente", '' "Tipo Pessoa", '' "CNPJ", '' "CPF", '' "Tipo específico", '' "Valor Tipo Específico", '' "Razão Social/Estabel.   Terceiro", '' "Nome Fantasia/Apelido Terceiro", '' "Descrição do Ambiente", '' "CNAE", '' "Atividade Principal", '' "Grau de Risco", '' "CEP", '' "Logradouro", '' "Número", '' "Bairro", '' "Cidade", '' "Estado" 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 <= SYSDATE)) 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 <= 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 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) AND 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 <= SYSDATE) AND (R016ORN.DatExt >= SYSDATE OR R016ORN.DatExt = TO_DATE('1900-12-31', 'YYYY-MM-DD')) ORDER BY 1,3,9

Observación

El comando anteriorno considera sectores del organigrama vigente, que no estén extintos y que tengan un empleado vinculado yconsidera solo puestos que tienen un empleado vinculado.

sucursales

Ligação dos registros

La vinculación de los registros la realiza elcódigo HR sucursal:

Registro en el sistema de Senior Registro con el proveedor de SST Ejemplo

R030FIL.NumEmp
R030FIL.CodFil

Campo: Código RH da FILIAL

Formato:

NumEmp-CodFil

SISTEMA DA SENIOR			SISTEMA DO PRESTADOR
Código da empresa (`NumEmp`)	Código da filial (`CodFil`)	nombre de la sucursal	Código HR SUCURSAL
1	1001	Sucursal Demostrativa S/A	1-1001

Nota

Los valores que componen el código HR de la sucursal deben estar separados porguiones (-), como se muestra en el ejemplo anterior.

Comandos do banco de dados para saneamento da base

servidor SQL

  SELECT DISTINCT R030FIL.NumEmp 'Código Empresa Senior', R030EMP.NomEmp 'Nome empresa Senior', R030FIL.CodFil 'Código filial Senior', R030FIL.NomFil 'Nome da Filial Senior', 'COD_RH_' + CAST(R034FUN.NumEmp AS VARCHAR(4)) + '-' + CAST(R034FUN.CodFil AS VARCHAR(4)) 'Empresa SGG', CAST(R034FUN.NumEmp AS VARCHAR(4)) + '-' + CAST(R034FUN.CodFil AS VARCHAR(4)) 'Código RH', R030FIL.RazSoc 'Nome/Razão Social', R030FIL.NomFil 'Fantasia/Apelido', '' 'Natureza Jurídica', 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 ELSE '' END 'CNPJ', CASE R030FIL.TipIns 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 '' END 'CPF', CASE WHEN (R030FIL.InsCei <> 0) THEN 'CEI' WHEN (R030FIL.NumCno <> 0) THEN 'CNO' WHEN (R030FIL.NCaepf <> 0) THEN 'CAEPF' ELSE '' END 'Tipo Específico', CASE WHEN (R030FIL.InsCei <> 0) THEN R030FIL.InsCei WHEN (R030FIL.NumCno <> 0) THEN R030FIL.NumCno WHEN (R030FIL.NCaepf <> 0) THEN R030FIL.NCaepf ELSE '' END 'Valor Tipo Específico', '' 'IE', '' 'IM', '' 'CNAE', '' 'Atividade Principal', '' 'Grau de Risco', '' 'Fone-Comercial', '' 'Fone-Celular', '' 'Email', '' 'CEP', '' 'Logradouro', '' 'Número', '' 'Bairro', '' 'Cidade', '' 'Estado', '' 'Região', '' 'Informações Adicionais', '' 'Empresa Matriz' 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,3,6

Oráculo

  SELECT DISTINCT R030FIL.NumEmp "Código Empresa Senior", R030EMP.NomEmp "Nome empresa Senior", R030FIL.CodFil "Código filial Senior", R030FIL.NomFil "Nome da Filial Senior", 'COD_RH_' || R034FUN.NumEmp || '-' || R034FUN.CodFil "Empresa SGG", R034FUN.NumEmp || '-' || R034FUN.CodFil "Código RH", R030FIL.RazSoc "Nome/Razão Social", R030FIL.NomFil "Fantasia/Apelido", '' "Natureza Jurídica", CASE R030FIL.TipIns WHEN 1 THEN R030FIL.NumCGC ELSE 0 END "CNPJ", CASE R030FIL.TipIns WHEN 3 THEN R030FIL.NumCGC ELSE 0 END "CPF", CASE WHEN (R030FIL.InsCei <> 0) THEN 'CEI' WHEN (R030FIL.NumCno <> 0) THEN 'CNO' WHEN (R030FIL.NCaepf <> 0) THEN 'CAEPF' ELSE '' END "Tipo Específico", CASE WHEN (R030FIL.InsCei <> 0) THEN R030FIL.InsCei WHEN (R030FIL.NumCno <> 0) THEN R030FIL.NumCno WHEN (R030FIL.NCaepf <> 0) THEN R030FIL.NCaepf ELSE 0 END "Valor Tipo Específico", '' "IE", '' "IM", '' "CNAE", '' "Atividade Principal", '' "Grau de Risco", '' "Fone-Comercial", '' "Fone-Celular", '' "Email", '' "CEP", '' "Logradouro", '' "Número", '' "Bairro", '' "Cidade", '' "Estado", '' "Região", '' "Informações Adicionais", '' "Empresa Matriz" 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 <= SYSDATE)) 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 <= 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 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,3,6