PL/SQL Function Body Returning Query
DECLARE
l_admin_role number(20);
l_main_role number(20);
l_policy_role number(20);
l_public_role number(20);
q varchar2(4000);
BEGIN
select role_id into l_admin_role
from APEX_APPL_ACL_ROLES
where role_name = 'ADMIN';
select role_id into l_main_role
from APEX_APPL_ACL_ROLES
where role_name = 'MAIN';
select role_id into l_policy_role
from APEX_APPL_ACL_ROLES
where role_name = 'POLICY';
select role_id into l_public_role
from APEX_APPL_ACL_ROLES
where role_name = 'PUBLIC';
q := 'SELECT ID as user_avatar,';
q := q || 'username as user_name,';
q := q || '''u-color-31-bg'' user_color,';
q := q || 'date_created as event_date,';
q := q || '''fa fa-user'' as event_icon,';
q := q || 'case role ';
q := q || 'when '|| l_admin_role ||' then ''ADMIN''';
q := q || 'when '|| l_main_role ||' then ''MAIN''';
q := q || 'when '|| l_policy_role ||' then ''POLICY''';
q := q || 'when '|| l_public_role ||' then ''PUBLIC''';
q := q || 'end as event_type,';
q := q || 'case role ';
q := q || 'when '|| l_admin_role ||' then ''u-color-1-bg''';
q := q || 'when '|| l_main_role ||' then ''u-color-4-bg''';
q := q || 'when '|| l_policy_role ||' then ''u-color-7-bg''';
q := q || 'when '|| l_public_role ||' then ''u-color-25-bg''';
q := q || 'end as event_status,';
q := q || 'firstname ||'' ''||lastname as event_title,';
q := q || 'case status ';
q := q || ' when 0 then ''Inactive''';
q := q || ' when 1 then ''Active''';
q := q || ' end as event_desc';
q := q || ' from APP_USER';
return q;
END;