Saneamento da base do prestador — RSData

SELECT

1 ‘TIPO’, ’;’ ,

null ‘Unidade Marítima’, ’;’ ,

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 ‘CNPJ/CPF/CEI/CAEPF’, ’;’ ,

R034FUN.NomFun ‘Nome’, ’;’ ,

CASE WHEN R023NAC.PaiRfb = 105 THEN ‘BRA’ ELSE ‘ES’ END ‘Nacionalidade’, ’;’ ,

Convert(varchar(10), R034FUN.DatNas,103) ‘Nascimento’, ’;’ ,

R034FUN.TipSex ‘Sexo’, ’;’ ,

CASE WHEN CAST(R034FUN.NumCtp as VARCHAR(10)) = ‘0’ THEN null ELSE CAST(R034FUN.NumCtp as VARCHAR(10)) END ‘CTPS’, ’;’ ,

CASE WHEN CAST(R034FUN.SerCtp as VARCHAR(10)) = ‘0’ THEN null ELSE CAST(R034FUN.SerCtp as VARCHAR(10)) END ‘Série’, ’;’ ,

CASE WHEN Convert(varchar(10), R034FUN.DexCtp, 103) = ‘31/12/1900’ THEN null ELSE Convert(varchar(10), R034FUN.DexCtp, 103) END ‘Data de emissão’, ’;’ ,

R034FUN.EstCtp ‘UF emissão’, ’;’ ,

R033PES.numcid ‘Identidade’, ’;’ ,

R033PES.EmiCid ‘Orgão Exp.’, ’;’ ,

CASE WHEN Convert(varchar(10), R033PES.DexCid, 103) = ‘31/12/1900’ THEN null ELSE Convert(varchar(10), R033PES.DexCid, 103) END ‘DT emissão’, ’;’ ,

R033PES.EstCid ‘UF emissão’, ’;’ ,

CAST(R033PES.NumPis as VARCHAR(15)) ‘NIT (PIS/PASEP)’, ’;’ ,

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’, ’;’ ,

NULL ‘RNE’, ’;’ ,

CAST(R034FUN.NUMCAD AS VARCHAR(200)) ‘Matrícula’, ’;’ ,

CAST(R034MAT.MATESO AS VARCHAR(200)) ‘Matrícula do RH’, ’;’ ,

NULL ‘Vínculo’, ’;’ ,

NULL ‘BR/PDH’, ’;’ ,

NULL ‘Reg. Revez’, ’;’ ,

CASE WHEN Convert(varchar(10), R034FUN.DatAdm, 103) = ‘31/12/1900’ THEN null ELSE Convert(varchar(10), R034FUN.DatAdm, 103) END ‘Admissão’, ’;’ ,

NULL ‘Demissão’, ’;’ ,

NULL ‘Observação’, ’;’ ,

R033PES.EmaPar ‘Email’, ’;’ ,

null ‘Endereço’, ’;’ ,

null ‘Cidade’, ’;’ ,

null ‘Bairro’, ’;’ ,

null ‘Estado’, ’;’ ,

null ‘País’, ’;’ ,

null ‘CEP’, ’;’ ,

null ‘Telefone’, ’;’ ,

null ‘Remuneração Mensal’, ’;’ ,

null ‘Nome da mãe’, ’;’ ,

null ‘Filiação à Previdência Social’, ’;’ ,

case

WHEN R034FUN.estciv in (1, 2) then R034FUN.estciv

WHEN R034FUN.estciv = 3 then 4

when R034FUN.estciv = 4 then 3

ELSE 5

END ‘Estado Civil’, ’;’ ,

NULL ‘Aposentado’, ’;’ ,

R033PES.NumEle ‘Título de eleitor’, ’;’ ,

R033PES.NumCnh ‘CNH’, ’;’ ,

CASE WHEN Convert(varchar(10), R033PES.VenCnh, 103) = ‘31/12/1900’ THEN null ELSE Convert(varchar(10), R033PES.VenCnh, 103) END ‘Validade CNH’, ’;’ ,

CASE WHEN Convert(varchar(10), R038HCA.DatAlt, 103) = ‘31/12/1900’ THEN null ELSE Convert(varchar(10), R038HCA.DatAlt, 103) END ‘Inicio’, ’;’ ,

” ‘Fim’, ’;’ ,

R016HIE.CodLoc ‘Código setor’, ’;’ ,

R016ORN.NomLoc ‘Nome setor’, ’;’ ,

null ‘Cod Setor Desenvolvido’, ’;’ ,

null ‘Setor Desenvolvido’, ’;’ ,

null ‘Local GHE’, ’;’ ,

null ‘Código GHE’, ’;’ ,

null ‘GHE’, ’;’ ,

R034FUN.CodCar ‘Código cargo’, ’;’ ,

R024CAR.TitRed ‘Cargo’, ’;’ ,

null ‘Cod Cargo Desenvolvido’, ’;’ ,

null ‘Cargo Deselvolvido’, ’;’ ,

null ‘CBO’, ’;’ ,

null ‘Descrição Sumária do Cargo’, ’;’ ,

null ‘Descrição Detalhada do Cargo’, ’;’ ,

null ‘Código Posição Trabalho’, ’;’ ,

null ‘Posição Trabalho’, ’;’ ,

null ‘Código RFID’, ’;’ ,

null ‘Código de BARRAS’, ’;’ ,

null ‘Turno’, ’;’ ,

null ‘Turno Jornada’, ’;’ ,

null ‘Grupo Sanguíneo’, ’;’ ,

null ‘Deficiência’, ’;’ ,

CAST(R034FUN.NUMEMP AS VARCHAR(200)) + ’/’ + CAST(R034FUN.CODFIL AS VARCHAR(200)) ‘Código Integração Empresa’, ’;’ ,

null ‘Descrição Ambiente’, ’;’ ,

CAST(R034FUN.NUMEMP AS VARCHAR(200)) + ’/’ + CAST(R034FUN.CODFIL AS VARCHAR(200)) + ’/’ + CAST(R034FUN.TIPCOL AS VARCHAR(200)) + ’/’ + CAST(R034FUN.NUMCAD AS VARCHAR(200)) ‘Código Integração Empregado’

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 R038HCA on (R038HCA.NumEmp = R034FUN.NumEmp AND R038HCA.TipCol = R034FUN.TipCol AND R038HCA.NumCad = R034FUN.NumCad AND R038HCA.DatAlt = (SELECT MAX(H.DATALT) FROM R038HCA H WHERE R034FUN.NumEmp = H.NumEmp AND R034FUN.TipCol = H.TipCol AND R034FUN.NumCad = H.NumCad AND H.DatAlt <= GETDATE()))

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 R038HLO on (R038HLO.NumEmp = R034FUN.NumEmp AND R038HLO.TipCol = R034FUN.TipCol AND R038HLO.NumCad = R034FUN.NumCad AND R038HLO.DatAlt = (SELECT MAX(H.DATALT) FROM R038HLO H WHERE R034FUN.NumEmp = H.NumEmp AND R034FUN.TipCol = H.TipCol AND R034FUN.NumCad = H.NumCad AND H.DatAlt <= 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 R034MAT on (R034MAT.NumEmp = R034FUN.NumEmp AND R034MAT.TipCol = R034FUN.TipCol AND R034MAT.NumCad = R034FUN.TipCol)

left join R023NAC on (R023NAC.CodNac = R034FUN.CodNac)

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

SELECT
1 AS “TIPO”, ’;’ AS ”;”,
NULL AS “Unidade Marítima”, ’;’ AS ”;”,
CASE R030FIL.TipIns
WHEN 1 THEN
CASE
WHEN (REPLACE(R030FIL.NumCGC, ’.’, ”) IS NULL) THEN NULL
ELSE (SUBSTR(REPLACE(R030FIL.NumCGC, ’.’, ”), 1, 2) || ’.’ ||
SUBSTR(REPLACE(R030FIL.NumCGC, ’.’, ”), 3, 3) || ’.’ ||
SUBSTR(REPLACE(R030FIL.NumCGC, ’.’, ”), 6, 3) || ’/’ ||
SUBSTR(REPLACE(R030FIL.NumCGC, ’.’, ”), 9, 4) || ’-’ ||
SUBSTR(REPLACE(R030FIL.NumCGC, ’.’, ”), 13, 2))
END
WHEN 2 THEN
CASE
WHEN (REPLACE(R030FIL.NumCGC, ’.’, ”) IS NULL) THEN NULL
ELSE (SUBSTR(REPLACE(R030FIL.NumCGC, ’.’, ”), 1, 2) || ’.’ ||
SUBSTR(REPLACE(R030FIL.NumCGC, ’.’, ”), 3, 3) || ’.’ ||
SUBSTR(REPLACE(R030FIL.NumCGC, ’.’, ”), 6, 5) || ’/’ ||
SUBSTR(REPLACE(R030FIL.NumCGC, ’.’, ”), 11, 2))
END
WHEN 3 THEN
CASE
WHEN (REPLACE(R030FIL.NumCGC, ’.’, ”) IS NULL) THEN NULL
ELSE (SUBSTR(REPLACE(R030FIL.NumCGC, ’.’, ”), 1, 3) || ’.’ ||
SUBSTR(REPLACE(R030FIL.NumCGC, ’.’, ”), 4, 3) || ’.’ ||
SUBSTR(REPLACE(R030FIL.NumCGC, ’.’, ”), 7, 3) || ’-’ ||
SUBSTR(REPLACE(R030FIL.NumCGC, ’.’, ”), 10, 2))
END
ELSE NULL
END AS “CNPJ/CPF/CEI/CAEPF”, ’;’ AS ”;”,
R034FUN.NomFun AS “Nome”, ’;’ AS ”;”,
CASE
WHEN R023NAC.PaiRfb = 105 THEN ‘BRA’
ELSE ‘ES’
END AS “Nacionalidade”, ’;’ AS ”;”,
TO_CHAR(R034FUN.DatNas, ‘DD/MM/YYYY’) AS “Nascimento”, ’;’ AS ”;”,
R034FUN.TipSex AS “Sexo”, ’;’ AS ”;”,
CASE
WHEN TO_CHAR(R034FUN.NumCtp) = ‘0’ THEN NULL
ELSE TO_CHAR(R034FUN.NumCtp)
END AS “CTPS”, ’;’ AS ”;”,
CASE
WHEN TO_CHAR(R034FUN.SerCtp) = ‘0’ THEN NULL
ELSE TO_CHAR(R034FUN.SerCtp)
END AS “Série”, ’;’ AS ”;”,
CASE
WHEN TO_CHAR(R034FUN.DexCtp, ‘DD/MM/YYYY’) = ‘31/12/1900’ THEN NULL
ELSE TO_CHAR(R034FUN.DexCtp, ‘DD/MM/YYYY’)
END AS “Data de emissão”, ’;’ AS ”;”,
R034FUN.EstCtp AS “UF emissão”, ’;’ AS ”;”,
R033PES.numcid AS “Identidade”, ’;’ AS ”;”,
R033PES.EmiCid AS “Orgão Exp.”, ’;’ AS ”;”,
CASE
WHEN TO_CHAR(R033PES.DexCid, ‘DD/MM/YYYY’) = ‘31/12/1900’ THEN NULL
ELSE TO_CHAR(R033PES.DexCid, ‘DD/MM/YYYY’)
END AS “DT emissão”, ’;’ AS ”;”,
R033PES.EstCid AS “UF emissão”, ’;’ AS ”;”,
TO_CHAR(R033PES.NumPis) AS “NIT (PIS/PASEP)”, ’;’ AS ”;”,
CASE
WHEN R034FUN.NumCPF = 0 THEN ’ ’
WHEN R034FUN.NumCPF IS NULL THEN ’ ’
ELSE
CASE LENGTH(R034FUN.NumCPF)
WHEN 1 THEN SUBSTR(‘0000000000’ || TO_CHAR(R034FUN.NumCPF), 1, 3) || ’.’ ||
SUBSTR(‘0000000000’ || TO_CHAR(R034FUN.NumCPF), 4, 3) || ’.’ ||
SUBSTR(‘0000000000’ || TO_CHAR(R034FUN.NumCPF), 7, 3) || ’-’ ||
SUBSTR(‘0000000000’ || TO_CHAR(R034FUN.NumCPF), 10, 2)
WHEN 2 THEN SUBSTR(‘000000000’ || TO_CHAR(R034FUN.NumCPF), 1, 3) || ’.’ ||
SUBSTR(‘000000000’ || TO_CHAR(R034FUN.NumCPF), 4, 3) || ’.’ ||
SUBSTR(‘000000000’ || TO_CHAR(R034FUN.NumCPF), 7, 3) || ’-’ ||
SUBSTR(‘000000000’ || TO_CHAR(R034FUN.NumCPF), 10, 2)
WHEN 3 THEN SUBSTR(‘00000000’ || TO_CHAR(R034FUN.NumCPF), 1, 3) || ’.’ ||
SUBSTR(‘00000000’ || TO_CHAR(R034FUN.NumCPF), 4, 3) || ’.’ ||
SUBSTR(‘00000000’ || TO_CHAR(R034FUN.NumCPF), 7, 3) || ’-’ ||
SUBSTR(‘00000000’ || TO_CHAR(R034FUN.NumCPF), 10, 2)
WHEN 4 THEN SUBSTR(‘0000000’ || TO_CHAR(R034FUN.NumCPF), 1, 3) || ’.’ ||
SUBSTR(‘0000000’ || TO_CHAR(R034FUN.NumCPF), 4, 3) || ’.’ ||
SUBSTR(‘0000000’ || TO_CHAR(R034FUN.NumCPF), 7, 3) || ’-’ ||
SUBSTR(‘0000000’ || TO_CHAR(R034FUN.NumCPF), 10, 2)
WHEN 5 THEN SUBSTR(‘000000’ || TO_CHAR(R034FUN.NumCPF), 1, 3) || ’.’ ||
SUBSTR(‘000000’ || TO_CHAR(R034FUN.NumCPF), 4, 3) || ’.’ ||
SUBSTR(‘000000’ || TO_CHAR(R034FUN.NumCPF), 7, 3) || ’-’ ||
SUBSTR(‘000000’ || TO_CHAR(R034FUN.NumCPF), 10, 2)
WHEN 6 THEN SUBSTR(‘00000’ || TO_CHAR(R034FUN.NumCPF), 1, 3) || ’.’ ||
SUBSTR(‘00000’ || TO_CHAR(R034FUN.NumCPF), 4, 3) || ’.’ ||
SUBSTR(‘00000’ || TO_CHAR(R034FUN.NumCPF), 7, 3) || ’-’ ||
SUBSTR(‘00000’ || TO_CHAR(R034FUN.NumCPF), 10, 2)
WHEN 7 THEN SUBSTR(‘0000’ || TO_CHAR(R034FUN.NumCPF), 1, 3) || ’.’ ||
SUBSTR(‘0000’ || TO_CHAR(R034FUN.NumCPF), 4, 3) || ’.’ ||
SUBSTR(‘0000’ || TO_CHAR(R034FUN.NumCPF), 7, 3) || ’-’ ||
SUBSTR(‘0000’ || TO_CHAR(R034FUN.NumCPF), 10, 2)
WHEN 8 THEN SUBSTR(‘000’ || TO_CHAR(R034FUN.NumCPF), 1, 3) || ’.’ ||
SUBSTR(‘000’ || TO_CHAR(R034FUN.NumCPF), 4, 3) || ’.’ ||
SUBSTR(‘000’ || TO_CHAR(R034FUN.NumCPF), 7, 3) || ’-’ ||
SUBSTR(‘000’ || TO_CHAR(R034FUN.NumCPF), 10, 2)
WHEN 9 THEN SUBSTR(‘00’ || TO_CHAR(R034FUN.NumCPF), 1, 3) || ’.’ ||
SUBSTR(‘00’ || TO_CHAR(R034FUN.NumCPF), 4, 3) || ’.’ ||
SUBSTR(‘00’ || TO_CHAR(R034FUN.NumCPF), 7, 3) || ’-’ ||
SUBSTR(‘00’ || TO_CHAR(R034FUN.NumCPF), 10, 2)
WHEN 10 THEN SUBSTR(‘0’ || TO_CHAR(R034FUN.NumCPF), 1, 3) || ’.’ ||
SUBSTR(‘0’ || TO_CHAR(R034FUN.NumCPF), 4, 3) || ’.’ ||
SUBSTR(‘0’ || TO_CHAR(R034FUN.NumCPF), 7, 3) || ’-’ ||
SUBSTR(‘0’ || TO_CHAR(R034FUN.NumCPF), 10, 2)
WHEN 11 THEN 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)
ELSE ’ ’
END
END AS “CPF”, ’;’ AS ”;”,
NULL AS “RNE”, ’;’ ,
TO_CHAR(R034FUN.NUMCAD) AS “Matrícula”, ’;’ ,
TO_CHAR(R034MAT.MATESO) AS “Matrícula do RH”, ’;’ ,
NULL AS “Vínculo”, ’;’ ,
NULL AS “BR/PDH”, ’;’ ,
NULL AS “Reg. Revez”, ’;’ ,
CASE
WHEN TO_CHAR(R034FUN.DatAdm, ‘DD/MM/YYYY’) = ‘31/12/1900’ THEN NULL
ELSE TO_CHAR(R034FUN.DatAdm, ‘DD/MM/YYYY’)
END AS “Admissão”, ’;’ ,
NULL AS “Demissão”, ’;’ ,
NULL AS “Observação”, ’;’ ,
R033PES.EmaPar AS “Email”, ’;’ ,
NULL AS “Endereço”, ’;’ ,
NULL AS “Cidade”, ’;’ ,
NULL AS “Bairro”, ’;’ ,
NULL AS “Estado”, ’;’ ,
NULL AS “País”, ’;’ ,
NULL AS “CEP”, ’;’ ,
NULL AS “Telefone”, ’;’ ,
NULL AS “Remuneração Mensal”, ’;’ ,
NULL AS “Nome da mãe”, ’;’ ,
NULL AS “Filiação à Previdência Social”, ’;’ ,
CASE
WHEN R034FUN.estciv IN (1, 2) THEN R034FUN.estciv
WHEN R034FUN.estciv = 3 THEN 4
WHEN R034FUN.estciv = 4 THEN 3
ELSE 5
END AS “Estado Civil”, ’;’ ,
NULL AS “Aposentado”, ’;’ ,
R033PES.NumEle AS “Título de eleitor”, ’;’ ,
R033PES.NumCnh AS “CNH”, ’;’ ,
CASE
WHEN TO_CHAR(R033PES.VenCnh, ‘DD/MM/YYYY’) = ‘31/12/1900’ THEN NULL
ELSE TO_CHAR(R033PES.VenCnh, ‘DD/MM/YYYY’)
END AS “Validade CNH”, ’;’ ,
CASE
WHEN TO_CHAR(R038HCA.DatAlt, ‘DD/MM/YYYY’) = ‘31/12/1900’ THEN NULL
ELSE TO_CHAR(R038HCA.DatAlt, ‘DD/MM/YYYY’)
END AS “Inicio”, ’;’ ,
” AS “Fim”, ’;’ ,
R016HIE.CodLoc AS “Código setor”, ’;’ ,
R016ORN.NomLoc AS “Nome setor”, ’;’ ,
NULL AS “Cod Setor Desenvolvido”, ’;’ ,
NULL AS “Setor Desenvolvido”, ’;’ ,
NULL AS “Local GHE”, ’;’ ,
NULL AS “Código GHE”, ’;’ ,
NULL AS “GHE”, ’;’ ,
R034FUN.CodCar AS “Código cargo”, ’;’ ,
R024CAR.TitRed AS “Cargo”, ’;’ ,
NULL AS “Cod Cargo Desenvolvido”, ’;’ ,
NULL AS “Cargo Deselvolvido”, ’;’ ,
NULL AS “CBO”, ’;’ ,
NULL AS “Descrição Sumária do Cargo”, ’;’ ,
NULL AS “Descrição Detalhada do Cargo”, ’;’ ,
NULL AS “Código Posição Trabalho”, ’;’ ,
NULL AS “Posição Trabalho”, ’;’ ,
NULL AS “Código RFID”, ’;’ ,
NULL AS “Código de BARRAS”, ’;’ ,
NULL AS “Turno”, ’;’ ,
NULL AS “Turno Jornada”, ’;’ ,
NULL AS “Grupo Sanguíneo”, ’;’ ,
NULL AS “Deficiência”, ’;’ ,
TO_CHAR(R034FUN.NUMEMP) || ’/’ || TO_CHAR(R034FUN.CODFIL) AS “Código Integração Empresa”, ’;’ ,
NULL AS “Descrição Ambiente”, ’;’ ,
TO_CHAR(R034FUN.NUMEMP) || ’/’ || TO_CHAR(R034FUN.CODFIL) || ’/’ || TO_CHAR(R034FUN.TIPCOL) || ’/’ || TO_CHAR(R034FUN.NUMCAD) AS “Código Integração Empregado”
FROM R034FUN
inner join R030EMP on (R030EMP.NumEmp = R034FUN.NumEmp)
inner join R030CAR on (R030CAR.NumEmp = R030EMP.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 R038HCA on (R038HCA.NumEmp = R034FUN.NumEmp AND R038HCA.TipCol = R034FUN.TipCol AND R038HCA.NumCad = R034FUN.NumCad AND R038HCA.DatAlt = (SELECT MAX(H.DATALT) FROM R038HCA H WHERE R034FUN.NumEmp = H.NumEmp AND R034FUN.TipCol = H.TipCol AND R034FUN.NumCad = H.NumCad AND H.DatAlt <= SYSDATE))
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 R038HLO on (R038HLO.NumEmp = R034FUN.NumEmp AND R038HLO.TipCol = R034FUN.TipCol AND R038HLO.NumCad = R034FUN.NumCad AND R038HLO.DatAlt = (SELECT MAX(H.DATALT) FROM R038HLO H WHERE R034FUN.NumEmp = H.NumEmp AND R034FUN.TipCol = H.TipCol AND R034FUN.NumCad = H.NumCad AND H.DatAlt <= 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 R034MAT on (R034MAT.NumEmp = R034FUN.NumEmp AND R034MAT.TipCol = R034FUN.TipCol AND R034MAT.NumCad = R034FUN.TipCol)
left join R023NAC on (R023NAC.CodNac = R034FUN.CodNac)
left join R038AFA on (R038AFA.numemp = R034FUN.NumEmp AND R034FUN.TipCol = R038AFA.tipcol AND R034FUN.NumCad = R038AFA.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,4