Saneamento da base do prestador — GSO

SELECT
      R034FUN.CodFil "Cod.Unid",
      R030FIL.NomFil "Nome Unidade",
      R030ORG.TabOrg || '/' || R016HIE.CodLoc "Cod.Setor",
      R016ORN.NomLoc "Nome Setor",
      R030CAR.EstCar || '/' || R034FUN.CodCar "Cod.Cargo",
      R024CAR.TitRed "Nome Cargo",
      R034FUN.TipCol || '/' || R034FUN.NumCad "Cod. Ident. Prestador SST", '' "Cod Funcionário",
      R034FUN.NomFun "Nome Funcionário",
      DECODE(TO_CHAR(R034FUN.DatNas, 'DD/MM/YYYY'), '31/12/1900', null, TO_CHAR(R034FUN.DatNas, 'DD/MM/YYYY')) "Dt.Nascimento",
      R034FUN.TipSex "Sexo",
      R010SIT.DesSit "Situação",
      DECODE(TO_CHAR(R034FUN.DatAdm, 'DD/MM/YYYY'), '31/12/1900', null, TO_CHAR(R034FUN.DatAdm, 'DD/MM/YYYY')) "Dt.Admissão",
      DECODE(TO_CHAR(R038AFA.DATAFA, 'DD/MM/YYYY'), '31/12/1900', null, TO_CHAR(R038AFA.DATAFA, 'DD/MM/YYYY')) "Dt.Demissão",
      CASE R034FUN.EstCiv
            WHEN 1 THEN 'Solteiro'
            WHEN 2 THEN 'Casado'
            WHEN 3 THEN 'Divorciado'
            WHEN 4 THEN 'Viúvo'
            WHEN 5 THEN 'Concubinato'
            WHEN 6 THEN 'Separado'
            WHEN 7 THEN 'União Estável'
            WHEN 9 THEN 'Outros'
            ELSE 'Solteiro'
      END "Estado Civil",
      R034FUN.NumPis "Pis/Pasep",
      CASE R034FUN.tipcon
            WHEN 1 THEN 'Empregado'
            WHEN 2 THEN 'Diretor'
            WHEN 3 THEN 'Trabalhador Rural'
            WHEN 4 THEN 'Aposentado'
            WHEN 5 THEN 'Estágiario'
            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 'Cooperador'
            WHEN 12 THEN 'Trabalhador Doméstico'
            WHEN 13 THEN 'Professor Prazo Determinado'
            ELSE 'Empregado'
      END "Contratação",
      R033PES.NumCid "Rg",
      R033PES.EstCid "UF-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"
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(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(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 R034FUN.TipCol = R038AFA.tipcol AND R034FUN.NumCad = R038AFA.numcad AND r038afa.sitafa IN (SELECT codsit FROM r010sit WHERE r010sit.tipsit = 7))
      WHERE R034FUN.TipCol IN (1,2)
ORDER BY 1,3,4

SELECT DISTINCT
      R024CAR.EstCar 'Estrutura cargo',
      R024CAR.CodCar 'Código cargo',
      R024CAR.TitRed 'Título cargo',
      CAST(R024CAR.ESTCAR AS VARCHAR(10))+'/'+ CAST(R024CAR.CODCAR AS VARCHAR(100)) 'Cod. Ident. Cargo Prestador SST'
FROM R024CAR, R034FUN, R010SIT
WHERE (R024CAR.DatExt >= getdate() OR CAST(R024CAR.DatExt AS DATE) = '1900-12-31')
      AND R024CAR.ESTCAR = R034FUN.ESTCAR
      AND R024CAR.CODCAR = R034FUN.CODCAR
      AND R034FUN.SITAFA = R010SIT.CODSIT
      AND R010SIT.TIPSIT <> 7
ORDER BY 1,3

SELECT DISTINCT
      R024CAR.EstCar "Estrutura cargo",
      R024CAR.CodCar "Código cargo",
      R024CAR.TitRed "Título cargo",
      R024CAR.EstCar || '/' || R024CAR.CodCar "Cod. Ident. Cargo Prestador SST"
FROM R024CAR, R034FUN, R010SIT
WHERE (R024CAR.DatExt >= sysdate OR R024CAR.DatExt = TO_DATE('1900-12-31', 'YYYY-MM-DD'))
      AND R024CAR.ESTCAR = R034FUN.ESTCAR
      AND R024CAR.CODCAR = R034FUN.CODCAR
      AND R034FUN.SITAFA = R010SIT.CODSIT
      AND R010SIT.TIPSIT <> 7
ORDER BY 1,3									

SELECT DISTINCT
      R016HIE.TabOrg 'Tabela organograma',
      R016HIE.CodLoc 'Código setor',
      R016ORN.NomLoc 'Nome setor',
      CAST(R016HIE.TABORG AS VARCHAR(200)) +'/'+ R016HIE.CODLOC 'Cod. Ident. Setor Prestador SST'
FROM R016ORN, R016HIE, R034FUN, R010SIT
WHERE 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')
      AND R016ORN.taborg = R034FUN.taborg
      AND R016HIE.numloc = R034FUN.numloc
      AND R034FUN.SITAFA = R010SIT.CODSIT
      AND R010SIT.TIPSIT <> 7
ORDER BY 1,2,3

SELECT DISTINCT
      R016HIE.TabOrg "Tabela organograma",
      R016HIE.CodLoc "Código setor",
      R016ORN.NomLoc "Nome setor",
      R016HIE.TabOrg || '/' || R016HIE.CodLoc "Cod. Ident. Setor Prestador SST"
FROM R016ORN, R016HIE, R034FUN, R010SIT
WHERE 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'))
      AND R016ORN.taborg = R034FUN.taborg
      AND R016HIE.numloc = R034FUN.numloc
      AND R034FUN.SITAFA = R010SIT.CODSIT
      AND R010SIT.TIPSIT <> 7
ORDER BY 1,2,3

SELECT
      R030FIL.NumEmp 'Empresa',
      R030EMP.NomEmp 'Nome empresa',
      R030FIL.CodFil 'Código filial',
      R030FIL.NomFil 'Nome da Filial',
      R030FIL.RazSoc 'Razão social da filial',
      R030FIL.CodFil 'Código Identificação Filial Prestador SST',
      CASE R030FIL.TipIns
            WHEN 1 THEN 'CNPJ'
            WHEN 2 THEN 'CEI'
            WHEN 3 THEN 'CPF'
            ELSE null
      END 'Tipo de inscrição',
      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 'Número de inscrição',
            CASE
                        WHEN (R030FIL.InsCei = 0) THEN null
                        ELSE R030FIL.InsCei
            END 'Número de inscrição CEI',
            CASE
                        WHEN (R030FIL.NumCno = 0) THEN null
                        ELSE R030FIL.NumCno
            END 'Número de inscrição no CNO',
            CASE
                        WHEN (R030FIL.NCaepf = 0) THEN null
                        ELSE R030FIL.NCaepf
            END 'Número de inscrição no CAEPF'
FROM R030FIL, R030EMP
WHERE
      R030Emp.NumEmp = R030FIL.NumEmp
ORDER BY 1,3,4									

SELECT
      R030FIL.NumEmp "Empresa",
      R030EMP.NomEmp "Nome empresa",
      R030FIL.CodFil "Código filial",
      R030FIL.NomFil "Nome da Filial",
      R030FIL.RazSoc "Razão social da filial",
      R030FIL.CodFil "Código Identificação Filial Prestador SST",
      CASE R030FIL.TipIns
      WHEN 1 THEN
            'CNPJ'
      WHEN 2 THEN
            'CEI'
      WHEN 3 THEN
            'CPF'
      ELSE NULL
      END "Tipo de inscrição",
      CASE R030FIL.TipIns
      WHEN 1 THEN
            DECODE(REPLACE(R030FIL.NumCGC, '.', ''), null, null,
            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))
      WHEN 2 THEN
            DECODE(REPLACE(R030FIL.NumCGC, '.', ''),null, null,
            SUBSTR(REPLACE(R030FIL.NumCGC, '.', ''),1,2) || '.' ||
            SUBSTR(REPLACE(R030FIL.NumCGC, '.', ''),3,3) || '.' ||
            SUBSTR(REPLACE(R030FIL.NumCGC, '.', ''),6,5) || '/' ||
            SUBSTR(REPLACE(R030FIL.NumCGC, '.', ''),11,2))
      WHEN 3 THEN
            DECODE(REPLACE(R030FIL.NumCGC, '.', ''), 0, ' ',
            SUBSTR(REPLACE(R030FIL.NumCGC, '.', ''),1,3) ||'.'||
            SUBSTR(REPLACE(R030FIL.NumCGC, '.', ''),4,3) ||'.'||
            SUBSTR(REPLACE(R030FIL.NumCGC, '.', ''),7,3) ||'-'||
            SUBSTR(REPLACE(R030FIL.NumCGC, '.', ''),10,2))
      ELSE null
      END "Número de inscrição",
            DECODE(R030FIL.InsCei, 0, null, R030FIL.InsCei) "Número de inscrição CEI",
            DECODE(R030FIL.NumCno, 0, null, R030FIL.NumCno) "Número de inscrição CNO",
            DECODE(R030FIL.NCaepf, 0, null, R030FIL.NCaepf) "Número de inscrição CAEPF"
      FROM R030FIL, R030EMP
      WHERE
      R030Emp.NumEmp = R030FIL.NumEmp
ORDER BY 1,3,4