Dynamic View and Drop Down Menu

Your rating: None Average: 3.7 (9 votes)

This was kind of interesting to figure out. What I was going after is the following. I had a drop down box on a page that shows some Business Units. The values however need to be dynamic and change depending on the user viewing/accessing the page.

For example, user1 should only see 52024, 12456 but user2 should be allowed to see 52024, 12456, 45648, and 10235.

To accomplish this I've created a table to store values for each user (lets call it TABLE_1). TABLE_1 has 2 fields (oprid, and business_unit). So now, we have 2 users on the table along with their associated business units.

The 2nd step is to create the dynamic view to pull business units specific to each user. The view will only have one field and that is the business_unit field.

record view

On the Record Type tab choose Dynamic View and click "Click to open SQL Editor" to add your SQL view.

dynamic view

Your SQL will look something like this:

SELECT business_unit
  ,oprid
FROM ( SELECT business_unit
             ,oprid
          FROM TABLE_1)
WHERE %oprclause

The %oprclause is what drives the dynamic view. At run time if the OPRID is in the view, %oprclause expands to OPRID='current operator' and therefore the view will only show values that are specific to the user viewing the page!

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Guest's picture
Guest (not verified)
Alternate way

Hey!Informative blog man.
We implemented the same using an array. Instead of having another table created, we just had an SQLExec to fetch the values for an employee and stored the same in an array and later populated the DropDown with the values from the array.

Guest's picture
Guest (not verified)
Re: Dynamic View and Drop Down Menu

Lepa,
Can you explain how you expanded %oprclause in peoplecode ?

Lizy's picture
Lizy (not verified)
Calling Dynamic View from Dinamic View

hi,
thank you for your example. Help me a lot, but my case is a little bit different.
I need to get the security row level base on the login user so I am using a dinamic view with the following code

SELECT rowsecclass
, oprid
FROM (
SELECT rowsecclass
, oprid
FROM SYSADM.psoprdefn )
WHERE %oprclause

With the RowsecClass I need to get the groups and display them in the LookPrompt so I am using a second dynamic view

SELECT group_id
FROM PS_TL_GRP_SECURITY
WHERE rowsecclass in (
SELECT rowsecclass
FROM p_row_sec_vw) <-- CALLING FIRST DINAMIC VIEW

when I run the page I have error "
SQL Error. #:650 Error position 95 Return: 942-ORA-00942 table or view does not exist"

I am not sure how I can accomplish this.. Any suggestion!! Please HELP ME!!

Thanks in advance

Lepa's picture
User offline. Last seen 48 weeks 1 day ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Dynamic View and Drop Down Menu

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

KC Koh's picture
KC Koh (not verified)
Re: Dynamic View and Drop Down Menu

Hi,

I appreciate the tip on using dynamic views, along with %EDITTABLE as an edit prompt table to control the content of a field.

I was attempting to perform a join in the SQL statement text for the dynamic view, and I was able to pull up the prompt values on the page. But I keep getting an 'ORA-00918:Column Ambigiously defined' error on the page after I selected a prompt value. Is it possible to perform a SQL join on a dynamic view's SQL text?

Thanks,
KC