BIP Report Security -Security data access query

 /*==================================================

Security data access query for BU :-

==================================================*/

 (
 SELECT   hou.name,
          hou.organization_id
 FROM     fun_user_role_data_asgnmnts fu,
          hr_operating_units hou
 WHERE    fu.user_guid = fnd_global.user_guid
 AND      fu.active_flag = 'Y'
 AND      hou.organization_id = fu.org_id
 AND      fu.role_name IN ( 'ORA_PO_PROCUREMENT_MANAGER_JOB',
                           'ORA_PO_BUYER_JOB' )
 GROUP BY -- group by put for each role has same bu access so values getting repeated so group by put
          hou.name,
          hou.organization_id,
 order by 1 )

==================================================

Security Data Access Query for IO :-

==================================================

Inventory Specific roles

(SELECT iop.organization_id

 FROM   fun_user_role_data_asgnmnts fu,
        per_users pu,
        inv_org_parameters iop
 WHERE  1 = 1
        AND iop.organization_id = fu.inv_organization_id
        -- this id will change only
        AND fu.role_name IN ( 'ORA_INV_WAREHOUSE_MANAGER_JOB' )
        AND fu.active_flag = 'Y'
        AND fu.user_guid = pu.user_guid
        AND pu.user_guid = fnd_global.user_guid
 GROUP  BY
-- group by put for each role has same IO access so values getting repeated so group by put
iop.organization_id

) 

==================================================

Security Data Access Query for GL Ledger :-

==================================================

SELECT gl.name,
       gl.ledger_id
FROM   gl_ledgers gl
WHERE  gl.ledger_id IN (SELECT asa.ledger_id
                        FROM   fun_user_role_data_asgnmnts urda,
                               gl_access_sets gs,
                               gl_access_set_assignments asa
                        WHERE  1 = 1
                               AND urda.user_guid = fnd_global.user_guid
                               AND
              urda.role_name IN ( 'ORA_GL_GENERAL_ACCOUNTANT_JOB',
                                  'ORA_GL_GENERAL_ACCOUNTING_MANAGER' )
                               AND urda.active_flag = 'Y'
                               AND urda.access_set_id = gs.access_set_id
                               AND gs.access_set_id = asa.access_set_id
                        GROUP  BY
                       -- group by put as each role has same ledger access so value gets repeated so group by put
                       asa.ledger_id)
ORDER  BY 1 

==================================================

Security Data Access Query for FA Book :-

==================================================

(SELECT bc.book_type_code
 FROM   fun_user_role_data_asgnmnts da,
        fa_book_controls bc
 WHERE  1 = 1
        AND da.user_guid = fnd_global.user_guid
        AND da.role_name IN ( 'ORA_FA_ASSET_ACCOUNTANT_JOB' )
        AND da.active_flag = 'Y'
        AND da.book_id = bc.book_control_id
 GROUP  BY
-- group by put as each role has same asset book so value get repeated so group by put
bc.book_type_code
 ORDER  BY bc.book_type_code)