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)