Get the role name, role start date, role assigned by for a specific user using SQL

SELECT
    ppf.PERSON_NUMBER,
    pu.USERNAME,
    prd.ROLE_COMMON_NAME AS ROLE_NAME,
    TO_CHAR(pur.CREATION_DATE, 'MM/DD/YYYY') AS ROLE_START_DATE,
    pur.CREATED_BY AS ASSIGNED_BY
FROM
    PER_ALL_PEOPLE_F ppf
    JOIN PER_USERS pu ON ppf.PERSON_ID = pu.PERSON_ID
    JOIN PER_USER_ROLES pur ON pu.USER_ID = pur.USER_ID
    JOIN PER_ROLES_DN prd ON pur.ROLE_ID = prd.ROLE_ID
WHERE
    TRUNC(SYSDATE) BETWEEN ppf.EFFECTIVE_START_DATE AND ppf.EFFECTIVE_END_DATE
    AND (
        (:person_number IS NOT NULL AND ppf.PERSON_NUMBER = :person_number)
        OR (:username IS NOT NULL AND UPPER(pu.USERNAME) = UPPER(:username))
    )
ORDER BY
    prd.ROLE_COMMON_NAME ASC

I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.