Modifying the Departments Report

When you run the Create Application Wizard, the wizard creates forms and reports based on the selections you make. Behind the scenes, Application Express writes a query for you to extract the appropriate data from the database.

In this exercise, you modify the query for the Departments report to include information from the OEHR_EMPLOYEES table.

To modify the Departments report:

1. Click the Edit Page 2 link on the Developer toolbar (at the bottom of the page).

   The Page Definition for Page 2 appears. A page is the basic building block of an application. Pages contain user interface elements such as tabs, lists, buttons, items, and regions. To see the definition of each page belonging to your application, you use the Page Definition page. For more information, see "What Is Application Builder?".

2. Under Page Rendering, locate the Regions section.

   
   Description of the illustration reg_sect.gif
   
   

3. Click Departments.

4. Scroll down to Source, and replace the default Region Source script with the following:

   SELECT d.department_id           "Department ID",
       d.department_name            "Department Name",
       count(e2.employee_id)        "Number of Employees",
       substr(e.first_name,1,1)||'. '|| e.last_name "Manager Name", 
       c.country_name               "Location"
   FROM oehr_departments d,
       oehr_employees e,
       oehr_locations l, 
       oehr_countries c,
       oehr_employees e2
   WHERE d.manager_id    = e.employee_id 
       AND d.location_id   = l.location_id
       AND d.department_id = e2.department_id
       AND l.country_id    = c.country_id
       AND instr(upper(d.department_name),upper(nvl(:P2_REPORT_SEARCH,d.department_name))) > 0
       GROUP BY d.department_id,
       d.department_name,
       substr(e.first_name,1,1)||'. '||e.last_name,  c.country_name
   

5. Click Apply Changes.