PeopleSoft :: Prompt function in Peoplesoft-Dynamic Views.

Dynamic Views can prove to be powerful prompting mechanism when you come across a requirement where the values fetched by the prompt table needs to be changed based on some system variables such as %OperatorId or %EmployeeId. Take a situation where you want to filter the data present in the prompt table based on the logged in user. In this case you need to use dynamic views as a prompt table.
Take a situation where you have an address id as a field on the page. Your requirement will be to bring up the address id’s for the particular logged in user alone. But if a system administrator logs in, then you should display address id’s corresponding to all the users. In this case you have to use up dynamic views. The step should be followed will be as follows.
1. Create a record (say PROMPT_DVW) and add necessary fields that need to be prompted.
2. Set up the key structure in the way you would like the prompt page to be appeared.
3. Save the record as dynamic view type. No need to build or write sql for these kinds of records.
4. On the record field property of the address id field, set the dynamic view as the prompt table.
5. Now on the appropriate event (RowInit, FieldChange etc) write the below code.

/* Note: ADDRESS_ID is the field which requires prompt. We are not writing anything on the dynamic view people code events. */
If %OperatorId = “Admin” Then
RECORD.ADDRESS_ID.SqlText = “select ADDRESS_ID, DESCR from PS_BASE_TABLE”;
Else
RECORD.ADDRESS_ID.SqlText = “select ADDRESS_ID, DESCR from PS_BASE_TABLE where EMPLOYEE_ID = ‘”|%EmployeeId|”’”;
End-If;
The SqlText property will dynamically act as a view sql and bring up the corresponding result in the prompt page.