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