Mining FND data for Access Reporting

1. Mining FND data for Access Reporting Malcolm Speedie Openwave Systems Inc.

2. Business Need • Need to be able to tell auditors what each employees can do in Oracle. • Need to create a method to report user related FND data for the business. • Help staff ensure accounts are created and removed as needed. • Help with locked out accounts.

3. Constraints • Target audience for reports are not technical. • DBA team did not want automated account creation or end dating. • We are using the Hard-To-Guess set up with lockout activated.

4. Assumptions • Employees use responsibilities to access Oracle. • Core users have access to Discoverer. • Site uses custom responsibilities. • If you can show what is in each responsibility, you show what the employee can do.

5. Solutions • Since the data is relatively static (except user data) we used materialized view to speed up queries. • Design views to simplify folders in Discoverer by hiding the complexity of the underlying schema. • Create a batch program to keep the materialized views up to date. Another to populate the menu tree. • Create three alerts: • DBA team of ex-employee accounts to end date. • DBA team that new employees need accounts. • Employees of locked out accounts. • Locked out view

6. Primary Views • XX_FND_USER_RESP_LIST • XX_FND_EX_USER_RESP_LIST • XX_FND_APP_RESP_MENU_TREE • XX_FND_USER_LIST • XX_FND_FAILED_LOGON • XX_FND_ACTIVE_RESP

7. Table and programs • XX_FND_MENU_TREES • XX_FND_MVIEW_REFRESH • XX_FND_POP_MENU_TREES

8. Other areas to explore • Discoverer business area and folders tied to users and responsibilities • Oracle Access auditing • Third Party tools • http://www.mspeedie.com/mining_fnd_data.zipfor all source code and soft copy of presentation