Adding a List of Values to the Employees Report

In this exercise, you add a list of values to the Employees page to display employees by department. You also change the Employees report query, edit the column link on the Department report, and turn off pagination.

Topics:

Add a List of Values
Add an Item to Display List of Values
Change Employees Report Query
Change Column Link on Department Report
Turning Off Pagination

Add a List of Values

In this section, you create a list of values that is used to select which department details to view. This is a dynamic list that queries the OEHR_DEPARTMENTS table for a list of all the department names.

To create a list of values:

Click Shared Components icon.
Under User Interface, select Lists of Values.
Click the Create button.
Accept the default, From Scratch, and click Next.
For Name, enter DEPARTMENTS and select Dynamic.
Click Next.

For the Query, enter the following SQL:

SELECT department_name d, department_id v
    FROM oehr_departments
    ORDER BY d

Click Create List of Values.

Add an Item to Display List of Values

Now, you create an item in the Department Details region to display the list of departments.

To add an item:

Navigate to the Page Definition for page 3, Employees.
Under Region, right-click Employees and select Create Page Item.
Select Select List and click Next.
For Create Item:
1. Item Name - Enter P3_DEPARTMENT_ID
2. Sequence - Enter 10
3. Region - Select Employee (1) 10
4. Click Next.
For Item Attributes:
1. Label - Enter Department
2. Accept the remaining defaults and click Next.
For Settings:
1. Value Required - Select No.
2. Page Action when Value Changed - Select Redirect and Set Value.
3. Click Next.
For Identify List of Values, make these changes:
1. Named LOV - Select DEPARTMENTS
2. Display Null Option - Select Yes
3. Null Display Value - Enter:
```
 - No Department Assigned -
```
4. Null Return Value - Enter:
```
-1
```
5. Click Next.
Click Create Item.

Change Employees Report Query

Now, you change the Employees report query to display employees that belong to the selected department.

To change the query:

Under Regions, right-click Employees and click Edit.

Enter the following SQL for Region Source:

SELECT  e.EMPLOYEE_ID,
        e.FIRST_NAME,
        e.LAST_NAME,
        e.HIRE_DATE,
        e.SALARY,
        e.COMMISSION_PCT,
        calc_remuneration(salary, commission_pct) REMUNERATION,
        e.DEPARTMENT_ID,
        d.DEPARTMENT_NAME
  FROM  OEHR_EMPLOYEES e,
        OEHR_DEPARTMENTS d
 WHERE  e.DEPARTMENT_ID = d.DEPARTMENT_ID(+) AND 
        (e.DEPARTMENT_ID = :P3_DEPARTMENT_ID or 
        (e.DEPARTMENT_ID is null and nvl(:P3_DEPARTMENT_ID,'-1') = '-1'))

Click Apply Changes.

Change Column Link on Department Report

Because the Employees query has changed, the Number of Employees column link needs to initialize the P3_DEPARTMENT_ID item to the value of DEPARTMENT_ID.

To change the column link for Number of Employees:

Click the Application ID breadcrumb.
Click 2 - Departments.
Under Regions, right-click Departments and select Edit Report Attributes.
Click the Edit icon for Number of Employees.
Scroll down to Column Link.
For Item 1, select P3_DEPARTMENT_ID.
Click Apply Changes.

Turning Off Pagination

Next, turn off pagination for the Department Details report, because only one record (the department details report) displays at a time. If pagination is on, the region that displays the department details includes the information row 1 - 1.

To turn off pagination:

Click the Application ID breadcrumb.
Click 3 - Employees.
Under Regions, right-click Employees and select Edit Report Attributes.
Scroll down to Pagination.
For Pagination Type, select - No Pagination Selected -.
Click Apply Changes.
Run the page.
From the Department list, select No Department Assigned.

Description of the illustration bldapp_nodptassgn.gif

Employee Kimberely Grant appears. This employee is not currently assigned to a department. Therefore, no department details appear. The 1 - 1 you see on this form is part of the Employees report, not part of the Department Details region you just edited.

Tip:
If no records initially display, reload the page and try again.