CR XI / Oracle
What I want to do is that when the report runs, the dynamic cascading prompt would only show the list of organizations or personnel that the user has access to. For example if User "A" logs on, he will be able to see only the organizations or personnel that’s link to his user_id. The user_id is stored in an Oracle database table. The user will run the report via a user interface and that interface will pass the user_id but I don’t know how to get the user_id into my Business View to filter the LOV with the list of organization or personnel that the user is only allowed to see. Has anyone ever done this?
My data foundation in BV for my LOV is:
select distinct pa.person_assignment_id, sp.user_id, o.org_structure_long, p.last_name || ' ,' || p.first_name user_name
from persons p,
person_assignments pa,
orgs o,
session_privileges sp,
users u
where pa.person_assignment_id = p.checked_in_reporting_id
and p.person_id = pa.person_id
and pa.org_organization_id = o.org_id
and pa.org_organization_id = sp.organization_id
and sp.user_id = u.user_id
and u.user_id = '1055624'
I hardcoded the '1055624' for the user_id to test my Command but I need to pass a value to {?user_id}prompt parameter.
What I want to do is that when the report runs, the dynamic cascading prompt would only show the list of organizations or personnel that the user has access to. For example if User "A" logs on, he will be able to see only the organizations or personnel that’s link to his user_id. The user_id is stored in an Oracle database table. The user will run the report via a user interface and that interface will pass the user_id but I don’t know how to get the user_id into my Business View to filter the LOV with the list of organization or personnel that the user is only allowed to see. Has anyone ever done this?
My data foundation in BV for my LOV is:
select distinct pa.person_assignment_id, sp.user_id, o.org_structure_long, p.last_name || ' ,' || p.first_name user_name
from persons p,
person_assignments pa,
orgs o,
session_privileges sp,
users u
where pa.person_assignment_id = p.checked_in_reporting_id
and p.person_id = pa.person_id
and pa.org_organization_id = o.org_id
and pa.org_organization_id = sp.organization_id
and sp.user_id = u.user_id
and u.user_id = '1055624'
I hardcoded the '1055624' for the user_id to test my Command but I need to pass a value to {?user_id}prompt parameter.
