Integrating mod_plsql reports with apps so I can synch users. Is there a way?

Hi,
I have successfully crossed all hurdles on my current projects except this last one.
The scenario:
Create materialized views in a schema outside of apps, for a mini data warehouse. Check.
Create reports using mod_plsql referencing the materialized views that users will be able to access. Check.
Create a VPD or FGAC package to ensure that users can only see what they are supposed to see by using system context to track the user. Check (sort of, read on).
Add links to the Oracle apps menu so that a user will be able to click a link in their menu after they've logged on that will take them to the reports. Check (sort of, read on).

Where I'm stuck.

The user wants to be able to login to apps, click on the link in their menu and go to the reports. The reports should know who the user is and correctly constrain their access to just the data they should be able to see.

I COULD do this, passing a session_id (established on login to Oracle Applications for that user) to the mod_plsql report that would then establish the context for that session for that user in the schema that the report lives in. However, as far as I can tell, and this is where you may come in, I'm not able to establish a dynamic parameter value to be part of the html string in the function definition when you set things up in Oracle apps menu.

Currently I don't see how I can pass a user specific session_id from the Oracle Apps menu to my mod_plsql report that resides in a different schema. The alternate solution would be to somehow retrieve the user_id from the mod_plsql side of the equation, but I don't see how to do that either. I did note that the mod_plsql reports appear to be wrapped in a login jsp that times out, requiring user re-login after a certain amount of time which is exactly what I would want, but how do I get the mod_plsql report to convince the jsp page to cough up the user?

My deadline is looming. Any suggestions would be much appreciated. Thanks.
Kurt Received on Thu May 18 2006 - 13:00:59 CDT