SQL Query to Retrieve All Security Profiles Assigned to Roles in Oracle HCM

SELECT r_code.*, 
       "Role Code" || ' - ' || "Role Name" || ' - ' || "Job Role Name" || ' - ' || 
       "Organization Security Profile" || ' - ' || "Position Security Profile" || ' - ' || 
       "Country Security Profile" || ' - ' || "LDG Security Profile" || ' - ' || 
       "Person Security Profile" || ' - ' || "Public Person Security Profile" || ' - ' || 
       "Document Type Security Profile" || ' - ' || "Payroll Security Profile" || ' - ' || 
       "Payroll Flow Security Profile" || ' - ' || "Person Security Profile" AS Concat_Val_Validation
FROM (
  -- Subquery fetching data roles and respective security profile mappings
  SELECT 
    gen.data_role_name AS "Role Code",
    (SELECT role_name FROM fusion.ase_role_vl WHERE code = gen.data_role_name AND ROWNUM = 1) AS "Role Name",
    (SELECT role_name FROM per_roles_dn_tl WHERE gen.base_role_id = role_id AND language = 'US') AS "Job Role Name",

    -- Each subquery checks for the existence of a security profile for each HR securing object
    -- If the profile is not found, it shows a "Missing <Profile Type>" message
    ...
    -- (Rest of subqueries as per original SQL for all security profile types)
    
  FROM fusion.per_generated_data_roles gen
  WHERE gen.data_role_name IN (
    SELECT code
    FROM fusion.ase_role_vl
    WHERE 1=1
      AND (
        role_name IN (:ROLE_NAME_PARAM)
        OR 'All' IN (:ROLE_NAME_PARAM || 'All')
      )
  )
) r_code

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