BIP – Restricting succession plans access to logged in person in BIP reports
Succession Plans data is stored in HRM_PLANS table. A succession plan can a PUBLIC or PRIVATE plan. By default if any user accesses HRM_PLANS table, user will be able to see all plans data irrespective of plan type (public or private). The requirement here is that a person who is querying data should be able to see all PUBLIC succession plans and only those PRIVATE plans which are defined by the user.
This can be achieved using below SQL:
select * from HRM_PLANS hp where hp.ACCESS_TYPE_CODE = 'PUBLIC' UNION select * from HRM_PLANS hp where hp.ACCESS_TYPE_CODE = 'PRIVATE' and exists (select 1 from HRM_PLAN_OWNERS hpo where person_id = hrc_session_util.get_user_personid and hpo.plan_id = hp.plan_id)