Query SDR BSBoard - Versao Oracle

SELECT P.DOCUMENT,
P.ENROLLMENT,
P.WORKCODE,
P.WORKNAME,
C.ID,
C.CONTRACTDATE,
C.CONTRACTTYPE,
CASE
    WHEN C.FACERECOGNITIONVALIDATION = 1 THEN 'SIM'
    ELSE 'NÃO'
END facerecognitionvalidation
, CASE
    WHEN C.TERMSACCEPTED = 1 THEN 'SIM'
    ELSE 'NÃO'
END termsaccepted
, C.FINANCIALINSTITUTIONID
, F.BANKNUMBER
, B.BANKNAME
, CASE
    WHEN CL.LOANTYPE = 'NEW' THEN 'Novo'
    WHEN CL.LOANTYPE = 'REFIN' THEN 'Refinanciamento'
    WHEN CL.LOANTYPE = 'PORTABILITY' THEN 'Portabilidade'
    ELSE 'N/A'
  END AS loantype
, CASE
    WHEN CL.LOANSTATUS = 'CANCELLED_BY_SYSTEM' THEN 'Cancelado pelo Sistema'
        WHEN CL.LOANSTATUS = 'CANCELLED_BY_USER' THEN 'Cancelado pelo Usuário'
    WHEN CL.LOANSTATUS = 'CCBSIGNED' THEN 'Assinado'
    WHEN CL.LOANSTATUS = 'PAID' THEN 'Pago'
    WHEN CL.LOANSTATUS = 'INITIAL' THEN 'Iniciado'
    WHEN CL.LOANSTATUS = 'SIGNED' THEN 'Assinado'
    WHEN CL.LOANSTATUS = 'CANCELLED' THEN 'Cancelado'
    ELSE 'N/A'
  END AS loanstatus
, CL.VALUEFORDEPOSIT
, CL.INSTALLMENTVALUE
, CL.NUMBEROFINSTALLMENTS
, ( CL.NOMINALRATEPERMONTH * 100 ) AS nominalratepermonth
FROM TUDOPROD.PERSON P
INNER JOIN TUDOPROD.CONTRACT C ON C.PERSONID = P.ID
INNER JOIN TUDOPROD.CONTRACTLOAN CL ON CL.CONTRACTID = C.ID
INNER JOIN TUDOPROD.FINANCIALINSTITUTION F ON F.ID = C.FINANCIALINSTITUTIONID
INNER JOIN TUDOPROD.BANK B ON B.BANKNUMBER = F.BANKNUMBER
ORDER BY C.ID;