extracting planning security

So true we can export/import security and import security using utilities exportsecurity or import security utilities respectively.SO at one of my client i ran into issue where these utils turned out be dead animal.So its dead end for me:-((.
So had to take a detour querying planning relational repository and now there is standard list of tables in there.I did some research creating a test app and studied the planning tables combining with some pieces information in few blogs.Posted on oracle forums to gather some more information .After that with my own test application assigned some security to some of the dimensions to make up some data into the tables.

Finally a nd slowly Took some 5+ weeks to explore the planning tables entirely .It wasn't that hard though.

This is a one of the version of sql query i wrote........

SELECT HSP_OBJECT.OBJECT_NAME,

HSP_OBJECT_1.OBJECT_NAME AS MEMBERNAME,

CASE HSP_ACCESS_CONTROL.ACCESS_MODE

WHEN 1 THEN 'READ'

WHEN 3 THEN 'WRITE'

WHEN - 1 THEN 'DENY' END AS ACCESSMODE,

CASE HSP_ACCESS_CONTROL.FLAGS

WHEN 0 THEN 'MEMBER'

WHEN 5 THEN 'CHILDREN'

WHEN 6 THEN 'ICHILDREN'

WHEN 8 THEN 'DESCENDANTS'

WHEN 9 THEN 'IDESCENDANTS' END AS ACCESSLEVEL,

HSP_OBJECT_TYPE.TYPE_NAME, HSP_OBJECT_1.GENERATION,

CASE HSP_OBJECT_1.HAS_CHILDREN

WHEN 0 THEN 'NO'

WHEN 1 THEN 'YES' END AS 'HAS CHILDREN',

HSP_OBJECT_2.OBJECT_NAME AS ALIASNAME

FROM HSP_OBJECT AS HSP_OBJECT_2 INNER JOIN

HSP_ALIAS ON HSP_OBJECT_2.OBJECT_ID = HSP_ALIAS.ALIAS_ID RIGHT OUTER JOIN

HSP_OBJECT INNER JOIN

HSP_GROUP ON HSP_OBJECT.OBJECT_ID = HSP_GROUP.GROUP_ID INNER JOIN

HSP_OBJECT AS HSP_OBJECT_1 INNER JOIN

HSP_ACCESS_CONTROL ON HSP_OBJECT_1.OBJECT_ID = HSP_ACCESS_CONTROL.OBJECT_ID INNER JOIN

HSP_OBJECT_TYPE ON HSP_OBJECT_1.OBJECT_TYPE = HSP_OBJECT_TYPE.OBJECT_TYPE ON

HSP_GROUP.GROUP_ID = HSP_ACCESS_CONTROL.USER_ID ON HSP_ALIAS.MEMBER_ID = HSP_ACCESS_CONTROL.OBJECT_ID

WHERE (HSP_OBJECT_1.OBJECT_TYPE IN (31, 32, 33, 35, 50))

ORDER BY HSP_OBJECT_1.OBJECT_TYPE