Problema: Usuários demitidos no Senior não estavam inativando na Joinin, mesmo com a situação “Demitido” (7) no Senior.
Ajuste: → Anteriormente estava com a situação 0, foi ajustado para situação 7, em configuração da conta
→ Query não continha a situação 7 no banco de dados, foi incluído a situação para inativar.
Configuração da conta > Desativar marcações na situação 7
Antes:
Depois:
Ajuste Query importação usuários:
--------------------------------------------------------------------------------------
Antes:
-- Documento (CPF ou RG)
SELECT
RIGHT(CONCAT('00000000000', fun.numcpf), 11) AS document,
-- Tipo de Uso (NOVO)
fun.TipUso AS usage_type_code,
-- (Opcional) Descrição do Tipo de Uso
CASE fun.TipUso
WHEN 1 THEN 'Terceiro'
WHEN 2 THEN 'Consultor'
WHEN 3 THEN 'Fornecedor'
ELSE 'Não informado'
END AS usage_type_name,
-- Dados básicos
fun.tipcol AS user_type,
fun.numemp AS company_code,
fun.codfil AS headquarter_code,
NULLIF(fun.codpes, 0) AS person_code,
fun.datadm AS admission_date,
fun.nomfun AS name,
fun.numcad AS identification_number,
FORMAT(fun.datnas, 'dd-MM-yyyy') AS birthday,
fun.tipsex AS gender,
--Controle de Ponto
fun.conrho AS ControledePonto,
fun.ConRho_Desc AS DescricaoPonto,
fun.registra_ponto AS RegistraPonto,
-- Local de trabalho / departamento
fun.numloc AS workplace_code,
fun.nomloc AS workplace_name,
fun.numloc AS department_code,
fun.nomloc AS department_name,
-- Centro de custo
fun.codccu AS cost_center_code,
fun.nomccu AS cost_center_name,
-- Cargo
CAST(fun.estcar AS VARCHAR(10)) + '-' + LTRIM(CAST(fun.codcar AS VARCHAR(10))) AS position_code,
fun.titred AS position_name,
fun.codhie AS job_level_code,
fun.deshie AS job_level_name,
-- Situação
fun.sitafa AS situation_code,
fun.dessit AS situation_name,
NULLIF(FORMAT(fun.datafa, 'dd-MM-yyyy'), '31-12-1900') AS situation_date,
-- E-mail principal (Profissional, se nulo assume o Particular)
COALESCE(NULLIF(LTRIM(RTRIM(fun.emacom)), ''), NULLIF(LTRIM(RTRIM(fun.emapar)), '')) AS email,
-- Endereço e contato
RIGHT(CONCAT('00000000', fun.endcep), 8) AS zipcode,
LTRIM(RTRIM(fun.endcpl)) AS address_complement,
fun.endrua AS address,
fun.endnum AS address_number,
LTRIM(RTRIM(fun.numram)) AS phone_extension,
NULLIF(LTRIM(RTRIM(fun.emapar)), '') AS personal_email,
fun.dddtel AS ddd_tel,
LTRIM(RTRIM(fun.numtel)) AS num_tel,
-- Informações adicionais
fun.mother_name,
fun.father_name,
fun.fun_mateso AS esocial_identification_number,
fun.fun_nompai AS country_name,
fun.codest AS state_uf,
fun.codcid AS city_code,
fun.codbai AS district_code,
fun.nombai AS district_name,
fun.fun_codpai_inter AS country_code,
fun.numcnh,
fun.catcnh,
fun.vencnh,
-- Jornada de trabalho
fun.codesc AS workschedule_code,
fun.nomesc AS workschedule_name,
fun.turesc AS shift_code,
fun.Turno_Nome AS shift_name,
CASE WHEN fun.EscNot = 'S' THEN 1 ELSE 0 END AS workschedule_night_shift,
-- Nome social e contrato
fun.nomsoc AS nome_social,
fun.tipcon AS contract_type_code,
CASE fun.tipcon
WHEN 1 THEN 'Empregado'
WHEN 2 THEN 'Diretor'
WHEN 3 THEN 'Trabalhador Rural'
WHEN 4 THEN 'Aposentado'
WHEN 5 THEN 'Estagiário'
WHEN 6 THEN 'Aprendiz'
WHEN 7 THEN 'Prazo Determinado - Lei 9.601/98'
WHEN 8 THEN 'Diretor Aposentado'
WHEN 9 THEN 'Agente Público'
WHEN 10 THEN 'Professor'
WHEN 11 THEN 'Cooperado'
WHEN 12 THEN 'Trabalhador Doméstico'
ELSE 'Não definido'
END AS contract_type_name
FROM vw_joinin_funcionario fun
WHERE fun.sitafa <> 0
ORDER BY
document,
CASE WHEN fun.sitafa = 1 THEN 0 ELSE 1 END
----------------------------------------------------------------------------------------------------
Depois:
-- Documento (CPF ou RG)
SELECT
RIGHT(CONCAT('00000000000', fun.numcpf), 11) AS document,
-- Tipo de Uso (NOVO)
fun.TipUso AS usage_type_code,
-- (Opcional) Descrição do Tipo de Uso
CASE fun.TipUso
WHEN 1 THEN 'Terceiro'
WHEN 2 THEN 'Consultor'
WHEN 3 THEN 'Fornecedor'
ELSE 'Não informado'
END AS usage_type_name,
-- Dados básicos
fun.tipcol AS user_type,
fun.numemp AS company_code,
fun.codfil AS headquarter_code,
NULLIF(fun.codpes, 0) AS person_code,
fun.datadm AS admission_date,
fun.nomfun AS name,
fun.numcad AS identification_number,
FORMAT(fun.datnas, 'dd-MM-yyyy') AS birthday,
fun.tipsex AS gender,
--Controle de Ponto
fun.conrho AS ControledePonto,
fun.ConRho_Desc AS DescricaoPonto,
fun.registra_ponto AS RegistraPonto,
-- Local de trabalho / departamento
fun.numloc AS workplace_code,
fun.nomloc AS workplace_name,
fun.numloc AS department_code,
fun.nomloc AS department_name,
-- Centro de custo
fun.codccu AS cost_center_code,
fun.nomccu AS cost_center_name,
-- Cargo
CAST(fun.estcar AS VARCHAR(10)) + '-' + LTRIM(CAST(fun.codcar AS VARCHAR(10))) AS position_code,
fun.titred AS position_name,
fun.codhie AS job_level_code,
fun.deshie AS job_level_name,
-- Situação
fun.sitafa AS situation_code,
fun.dessit AS situation_name,
NULLIF(FORMAT(fun.datafa, 'dd-MM-yyyy'), '31-12-1900') AS situation_date,
-- E-mail principal (Profissional, se nulo assume o Particular)
COALESCE(NULLIF(LTRIM(RTRIM(fun.emacom)), ''), NULLIF(LTRIM(RTRIM(fun.emapar)), '')) AS email,
-- Endereço e contato
RIGHT(CONCAT('00000000', fun.endcep), 8) AS zipcode,
LTRIM(RTRIM(fun.endcpl)) AS address_complement,
fun.endrua AS address,
fun.endnum AS address_number,
LTRIM(RTRIM(fun.numram)) AS phone_extension,
NULLIF(LTRIM(RTRIM(fun.emapar)), '') AS personal_email,
fun.dddtel AS ddd_tel,
LTRIM(RTRIM(fun.numtel)) AS num_tel,
-- Informações adicionais
fun.mother_name,
fun.father_name,
fun.fun_mateso AS esocial_identification_number,
fun.fun_nompai AS country_name,
fun.codest AS state_uf,
fun.codcid AS city_code,
fun.codbai AS district_code,
fun.nombai AS district_name,
fun.fun_codpai_inter AS country_code,
fun.numcnh,
fun.catcnh,
fun.vencnh,
-- Jornada de trabalho
fun.codesc AS workschedule_code,
fun.nomesc AS workschedule_name,
fun.turesc AS shift_code,
fun.Turno_Nome AS shift_name,
CASE WHEN fun.EscNot = 'S' THEN 1 ELSE 0 END AS workschedule_night_shift,
-- Nome social e contrato
fun.nomsoc AS nome_social,
fun.tipcon AS contract_type_code,
CASE fun.tipcon
WHEN 1 THEN 'Empregado'
WHEN 2 THEN 'Diretor'
WHEN 3 THEN 'Trabalhador Rural'
WHEN 4 THEN 'Aposentado'
WHEN 5 THEN 'Estagiário'
WHEN 6 THEN 'Aprendiz'
WHEN 7 THEN 'Prazo Determinado - Lei 9.601/98'
WHEN 8 THEN 'Diretor Aposentado'
WHEN 9 THEN 'Agente Público'
WHEN 10 THEN 'Professor'
WHEN 11 THEN 'Cooperado'
WHEN 12 THEN 'Trabalhador Doméstico'
ELSE 'Não definido'
END AS contract_type_name
FROM vw_joinin_funcionario fun
WHERE fun.sitafa <> 7
ORDER BY
document,
CASE WHEN fun.sitafa = 1 THEN 0 ELSE 1 END