Saneamento da base do prestador — Software SGG

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
											

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
											

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
												

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

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

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

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