SQL Query to fetch User Roles in Oracle Cloud ERP

    pp.creation_date    creation_date,
    ppf.first_name      first_name,
    ppf.last_name       last_name,
    hl.location_code    location_code,
    hl.location_name    location_name,
    hl.town_or_city     town,
    hl.country          country,
    pd.name             department,
    pu.username         username,
    pu.active_flag      active_flag,
    au.user_id          user_id,
    au.user_login       user_login,
    r.role_name         role_name,
    r.description       description
    per_persons            pp,
    per_all_people_f       papf,
    per_person_names_f_v   ppf,
    hr_locations_all_f_vl  hl,
    per_departments        pd,
    per_all_assignments_m  paaf,
    per_users              pu,
    ase_user_vl            au,
    ase_user_role_mbr      aurm,
    ase_role_vl            r
        1 = 1
    AND au.user_guid = pu.user_guid (+)
    AND pu.person_id = papf.person_id (+)
    AND papf.person_id = pp.person_id (+)
    AND pp.person_id = ppf.person_id (+)
    AND ppf.person_id = paaf.person_id (+)
    AND paaf.location_id = hl.location_id (+)
    AND paaf.organization_id = pd.organization_id (+)
    AND trunc(sysdate) BETWEEN nvl(ppf.effective_start_date, trunc(sysdate)) AND nvl(ppf.effective_end_date, trunc(sysdate))
    AND trunc(sysdate) BETWEEN nvl(papf.effective_start_date, trunc(sysdate)) AND nvl(papf.effective_end_date, trunc(sysdate))
    AND trunc(sysdate) BETWEEN nvl(paaf.effective_start_date, trunc(sysdate)) AND nvl(paaf.effective_end_date, trunc(sysdate))
    AND trunc(sysdate) BETWEEN nvl(hl.effective_start_date, trunc(sysdate)) AND nvl(hl.effective_end_date, trunc(sysdate))
    AND trunc(sysdate) BETWEEN nvl(pd.effective_start_date, trunc(sysdate)) AND nvl(pd.effective_end_date, trunc(sysdate))
    AND trunc(sysdate) BETWEEN nvl(au.effective_start_date, trunc(sysdate)) AND nvl(au.effective_end_date, trunc(sysdate))
    AND aurm.user_id = au.user_id
    AND r.role_id = aurm.role_id
    AND au.user_login = :p_user_login

Leave a Reply

Your email address will not be published.