/*==================================================
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)
