Case Study: Emailing OBIEE dashboards with ad-hoc agent that dynamically passes dashboard prompt values based on user request -- Part 2

From the previous time, I have finalized the DB table design, now let's check out what I did in the rpd.

Below are the session initialization blocks. 'Security' and 'GROUPS' where created for the existing data level and object level security implemented before.
Now I am adding a new one 'Security_Agent':

In Security Agent, I have created session variables for each columns of the table. These session variables will be passed on to the dashboard later.

The initialization string is the following query:

Select USER_NAME, START_MONTH, END_MONTH, MFG_HIERARCHY, MAKE, PAY_TYPE, AVG_COST,EMAIL, USER_SEQUENCE_NUM, USERS 

from DW_OBIEE_USER_AUTH

where 

USERS in (select USERNAME     

from table(Ret_User_Details(substr(':USER', 1,  instr(':USER' , '_' , 1) -1 ) ))  ) 

AND USER_NAME = ':USER'

and MFG_HIERARCHY in (select company_name     

from table(Ret_User_Details(substr(':USER', 1,  instr(':USER' , '_' , 1) -1 ) ))  ) 

UNION

Select USER_NAME, START_MONTH, END_MONTH, MFG_HIERARCHY, MAKE, PAY_TYPE, AVG_COST,EMAIL, USER_SEQUENCE_NUM, USERS 

from DW_OBIEE_USER_AUTH

where 

USERS in (select USERNAME     

from table(Ret_User_Details(substr(':USER', 1,  instr(':USER' , '_' , 1) -1 ) ))  ) 

AND USER_NAME = ':USER'

AND USER_TYPE in (select SCOPE_NAME FROM table(Ret_User_Details(substr(':USER', 1,  instr(':USER' , '_' , 1) -1 ) ))  )

The blue part of the query takes the input of (:user), which is going to be in the form of 'user_1', strip everything after '_' in order to match in the security user table that the client company already has, then it returns the values of the same user in my new DB table with corresponding dashboard parameters. The user in my table must exist in the security user table, not only that, but the corresponding manufacturer of that user must also match the same in the security table. This takes care of the error handling of man made mistakes. 

The red part of the query is identical to the blue part. However, this part is only for users who are company's internal users. If you followed my previous implementation of data and row level security, you will realize that for company user, there is no corresponding company name of that user in the security table. It is only flagged by 'HAS_GLOBAL_APP_SCOPE' = 1 in that table. So the blue part of the query takes care of external users, but it will leave out all of the company's internal user, which is not good. Therefore, the blue and the red are both needed and it will be unioned. 

All variables are set with default values:

This is all I need for the rpd.

Next time, the most tedious work will be begin, which is modifying dashboards to receive these session variables.

Stay tuned.

Thanks