SELECT
CAST(PCEMPR.NOME AS VARCHAR(80)) AS NAME,
CAST(PCEMPR.MATRICULA AS VARCHAR(25)) AS REGISTRATION_NUM,
NVL(CAST(PCEMPR.CNH AS VARCHAR(50)),'') AS HABILITATION_NUM,
NVL(CAST(TO_CHAR(PCEMPR.DTVALIDADECNH, 'YYYY-MM-DD') AS VARCHAR(10)),'') AS HABILITATION_DUE_DATE,
NVL(CAST(PCEMPR.ORGAOEMISSORCNH AS VARCHAR(25)),'') AS HABILITATION_ISSUER,
NVL(CAST(PCEMPR.CATEGORIACNH AS VARCHAR(25)),'') AS HABILITATION_CATEGORY,
CAST(DECODE(NVL(PCEMPR.DT_EXCLUSAO,''),'',DECODE(NVL(PCSETOR.USAMYFROTA,'N'),'S','true','false'),'false') AS VARCHAR(5)) AS ACTIVE,
NVL(CAST(PCEMPR.CPF AS VARCHAR(20)),'') AS CPF,
NVL(CAST(PCEMPR.RG AS VARCHAR(20)),'') AS RG,
NVL(CAST(PCEMPR.CNH AS VARCHAR(50)),'') AS CNH,
NVL(CAST(TO_CHAR(PCEMPR.DTNASC, 'YYYY-MM-DD') AS VARCHAR(10)),'') AS BIRTHDATE,
CAST(DECODE(PCEMPR.TIPOMOTORISTA, 'F', 'CLT', 'T', 'Terceirizado', 'CLT') AS VARCHAR(25)) AS TYPE,
NVL(CAST(TO_CHAR(PCEMPR.ADMISSAO, 'YYYY-MM-DD') AS VARCHAR(10)),'') AS ADMISSION_DATE,
TO_CHAR(NVL((SELECT MAX(D.DATA)
FROM PCLOGALTERACAODADOS D
WHERE D.DATA BETWEEN TO_DATE(TO_CHAR(SYSDATE,'dd/mm/rrrr')||' 00:00:00','dd/mm/rrrr hh24:mi:ss') AND
TO_DATE(TO_CHAR(SYSDATE,'dd/mm/rrrr')||' 23:59:59','dd/mm/rrrr hh24:mi:ss')
AND D.TABELA = 'PCEMPR'
AND D.COLUNA = 'CODFILIAL'
AND D.CODIGO = PCEMPR.MATRICULA), PCEMPR.ADMISSAO), 'YYYY-MM-DD HH24:MI:SS') AS SUBSIDIARY_CHANGE_DATE,
NVL(CAST(PCEMPR.CELULAR AS VARCHAR(20)),'') AS PHONES,
NVL(CAST(PCEMPR.ENDERECO AS VARCHAR(60)),'') AS ADDRESS,
NVL(CAST(PCEMPR.ESTADO AS VARCHAR(2)),'') AS ADDRESS_STATE_ID,
NVL(CAST(PCEMPR.CIDADE AS VARCHAR(50)),'') AS ADDRESS_CITY_ID,
CAST('BR' AS VARCHAR(2)) AS ADDRESS_COUNTRY,
NVL(CAST(PCEMPR.CEP AS VARCHAR(20)),'') AS POSTAL_CODE,
CAST('Motorista' AS VARCHAR(25)) AS EMPLOYEE_ROLE_ID,
CAST(PCFILIAL.RAZAOSOCIAL AS VARCHAR(60)) AS SUBSIDIARY_ID,
NVL(CAST(PCEMPR.ORGAOEMISSORRG AS VARCHAR(20)),'') AS RG_ISSUER,
CAST(PCEMPR.IDINTEGRACAOMYFROTA AS VARCHAR(50)) AS EXTERNAL_ID,
PCSETOR.DESCRICAO AS DEPARTMENT,
PCEMPR.EMAIL AS EMAIL,
PCEMPR.BAIRRO AS ADRESS_NEIGHBORHOOD
FROM PCEMPR, PCSETOR, PCFILIAL
WHERE PCEMPR.CODSETOR = PCSETOR.CODSETOR
AND PCEMPR.CODFILIAL = PCFILIAL.CODIGO
AND PCSETOR.USAMYFROTA = 'S'
AND
(IDSOFITVIEW IS NULL
OR ((TO_DATE(TO_CHAR(NVL(DT_EXCLUSAO,TO_DATE('01/01/1900','DD/MM/YYYY HH24:MI:SS')),'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS')
- TO_DATE(TO_CHAR(NVL(DTEXCLUSAOSOFITVIEW,TO_DATE('01/01/1900','DD/MM/YYYY HH24:MI:SS')),'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS')) > 0
AND IDSOFITVIEW IS NOT NULL AND CAST(IDSOFITVIEW AS INT) > 0)
OR (DTEXCLUSAOSOFITVIEW IS NOT NULL AND DT_EXCLUSAO IS NULL)
OR ((TO_DATE(TO_CHAR(NVL(PCSETOR.DTULTALTER,TO_DATE('01/01/1900','DD/MM/YYYY HH24:MI:SS')),'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS')
- TO_DATE(TO_CHAR(NVL(DTULTALTERSOFITVIEW,TO_DATE('01/01/1900','DD/MM/YYYY HH24:MI:SS')),'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS')) > 0
AND DTULTALTERSOFITVIEW IS NOT NULL)
OR ((TO_DATE(TO_CHAR(NVL(PCEMPR.DTULTALTER,TO_DATE('01/01/1900','DD/MM/YYYY HH24:MI:SS')),'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS')
- TO_DATE(TO_CHAR(NVL(DTULTALTERSOFITVIEW,TO_DATE('01/01/1900','DD/MM/YYYY HH24:MI:SS')),'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS')) > 0
AND DTULTALTERSOFITVIEW IS NOT NULL)
)
ORDER BY PCEMPR.MATRICULA DESC |
