4 Building Your Application

About the Application You Build in This Tutorial

This tutorial guides you through the development of a Human Resources (HR) application for a fictitious company called AnyCo Corp. The application enables users to view and modify employee information stored in the OEHR_DEPARTMENTS and OEHR_EMPLOYEES tables.

When building the application, you perform these steps:

1. Create an application that includes a Home page and a basic Departments report. See "Creating the Initial Application".

2. Expand the report to pull in data from other tables. See "Modifying the Departments Report".

3. Build an Employee form and report based on the OEHR_EMPLOYEES table. See "Adding an Employees Report and Form".

4. Add calculated fields to the Employees report. See "Editing the Employees Report".

5. Edit items on the Employees form to display as select lists (with lists of values). See "Displaying Items as Select Lists".

6. Add a link on the Home page. See "Adding a Link on the Home Page to the Employees Report".

7. Modify the Employees report to enable the user to select a department. See "Linking the Employees and Departments Reports".

8. Add a link on the Departments report that navigates the user to the Employees report. See "Linking a Column Value to Another Page".

9. Add a report as a region that displays department details. See "Adding a Conditional Department Details Report".

10. Add your company name so that it appears on all pages in your application. See "Adding Your Company Name".

11. Switch the theme for your application. See "Switching Themes to Change User Interface".

Creating the Initial Application

This first exercise guides you through creating your initial application. Using the Create Application Wizard, you create two pages as well as specify basic application functionality.

To create your initial application:

1. Log in to Application Express.

   For instructions, see "Logging In to the Hosted Demo Environment" or "Logging In to Your Local Instance".

2. On the Workspace home page, click Application Builder.

3. Click the Create button.

   The Create Application Wizard appears.

   
   Description of the illustration cr_app.gif
   
   

   Note that each page within a wizard displays the title of the wizard (for example, Create Application). Each wizard page also displays a series of blocks on the left, representing the sequence of steps. The highlighted block indicates the step you are performing.

   
   Description of the illustration bldap_wiz_steplst.gif
   
   

   To orient you, each step in these exercises generally begins with the block label. For example, the first step in the Create Application Wizard highlights the word Method. Therefore, the next step orients you by beginning with the words For Method.

4. For Method, accept the default, Create Application, and click Next.

5. For Name:

   1. Name - Enter AnyCo Corp.

   2. Application - accept the default ID. The system provides a unique ID for each application you create in this workspace.

   3. Create Application - Accept the default, From scratch.

   4. Schema - Select the schema that contains the objects for this tutorial (for example, TWODAYPLUS).

   5. Click Next.

   Next, add a page to your application.

6. For Pages, specify the following in the Add Page section:

   1. Select Page Type - Accept the default, Blank.

      Tip:

      Explanatory information for each option appears on the right side of the page. This page-level help appears throughout the application.

   2. Page Name - Enter Home.

   3. Click Add Page.

      The new page is listed in the top area. Also, note that the Add Page area appears so you can continue adding pages to your initial application.

   
   Description of the illustration cr_app_add_pg.gif
   
   

   Next, add a report page on the OEHR_DEPARTMENTS table.

7. In the Add Page section, specify the following:

   1. Select Page Type - Select Report.

   2. Subordinate to Page - Select Home.

      This sets up the hierarchy among the pages in your application.

   3. Page Source - Accept the default, Table.

   4. Table Name - Select OEHR_DEPARTMENTS.

      The list shows all tables and views in the schema associated with the application.

   5. Include Analysis Pages - Do not select.

      If selected, you are prompted for information about additional summary reports and charts you can add to this page.

   6. Click Add Page.

      Notice that the pages listed in the Create Application section reflect the hierarchy of the pages in your application.

      Next, change the default page name, OEHR_DEPARTMENTS, to Departments.

   7. Click the OEHR_DEPARTMENTS link.

   8. Under Page Definition, change the Page Name to Departments.

   9. Click Apply Changes.

   10. In the Create Application section, click Next.

   After adding your initial pages, define some application-level settings.

8. For Tabs, select No Tabs and click Next.

9. For Shared Components, accept the default, No, and click Next.

10. For Attributes, accept all the defaults and click Next.

    Note that Application Express is the default Authentication Scheme. The authentication scheme option enables you to control users' access to the application using Application Express authentication or another type of authentication.

11. For User Interface, select Theme 18 and click Next.

12. For Confirm, verify the information and click Create.

    The two pages you created, Home and Departments, appear as icons by default.

    Note:

    To view the pages in a list instead of as icons, from the View list select Details and click Go. Your view setting is retained from session to session.

    Because these steps use the default setting, switch back to Icons view before continuing.

    Notice that Application Express adds a Login page automatically to your application.

    
    Description of the illustration cr_app_lst_pgs.gif
    
    

Congratulations! You created your first application using Application Express. Next, you will preview the application to see the application the way it would appear to end users.

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.

Running the Page

To see how the page looks to end users, run the page. When you run a page, the Application Express engine dynamically renders and processes the page to display viewable HTML.

To run the page:

• Click the Run Page icon in the upper right corner.

  
  Description of the illustration runpg_iconrow.gif
  
  

The revised Departments report appears.

Description of the illustration bldap_deptexp.gif

Note that the revised report has three new columns: Number of Employees, Manager Name, and Location. Also note that your data might appear in a different order. You can ignore this difference and continue with the steps.

Adding an Employees Report and Form

In this exercise, you add a report to display employee information and a form to create, update, and delete employee information.

To add an employee report and form:

1. Click the Application link on the Developer toolbar (at the bottom of the page).

   The Application home page appears.

2. Click Create Page.

3. For Page:

   1. Select Form and click Next.

   2. Select Form on a Table with Report.

      This option creates two pages, a report and form, based on a single table or view.

   3. Click Next.

4. For Identify Table or View:

   1. For Table/View Owner, accept the default and click Next. This specifies the schema owner.

   2. For Table/View Name, select OEHR_EMPLOYEES and click Next.

5. For Define Report Page, edit the following:

   1. Page Name - Change to Employees.

   2. Region Title - Change to Employees.

   3. Breadcrumb - Select Breadcrumb.

      The Create Breadcrumb Entry section appears.

   4. For Select Parent Entry, click the Home link.

      Home appears in the Parent Entry field.

   5. Click Next.

6. For Define Report Page, accept the default, Do not use tabs, and click Next.

7. For Define Report Page, press the Ctrl key, select the following columns, and click Next:

   • EMPLOYEE_ID

   • FIRST_NAME

   • LAST_NAME

   • HIRE_DATE

   • SALARY

   • COMMISSION_PCT

   The columns you selected will appear on the Report page.

8. For Define Report Page, accept the defaults and click Next.

   Note that the default Edit Link image resembles a small piece of paper and pencil.

   Next, define the page and region information for the form.

9. For Define Form Page, edit the following:

   1. Page Name - Enter Create/Edit Employee.

   2. Region Title - Enter Create/Edit Employee.

   3. Entry Name - Enter Create/Edit Employee.

   4. Click Next.

10. In Tab Options, accept the default, Do not use tabs, and click Next.

11. In Primary Key, accept the defaults and click Next.

12. In Define the source for the primary key columns, accept the default, Existing trigger, and click Next.

    Your table, OEHR_EMPLOYEES, already has a trigger that populates the primary key.

13. In Select Column(s), select all columns and click Next.

    These columns will appear in the Create/Edit Employee form.

14. In Identify Process Options, accept the defaults and click Next.

    These selections enable users to add, update, and create employee records.

15. For Confirm, verify the information and click Finish.

Editing the Employees Report

In this exercise, you run a SQL script to create a function that calculates employees' salaries. Then you add a column, Remuneration, to display that calculation in the Employees report.

Topics in this section include:

• Create a Function

• Add a New Column

• Previewing the Page

• Changing the Column Format

Previewing the Page

To preview the page, click the Run Page icon in the upper right corner. Notice the addition of the new column, Remuneration.

Description of the illustration bldap_empfrmrem.gif

Displaying Items as Select Lists

In this exercise, you edit fields (items) on the Create/Edit Employee page to display as select lists. Select lists are drop-down lists that contain lists of values that users can select to populate a field in the application. Displaying items as select lists consists of two general steps:

1. Create the list of values (LOVs) for each item.

2. Edit the item to display as a select list and reference the appropriate list of values you created.

Topics in this section include:

• Creating Lists of Values

• Editing Items to Display as Select Lists

Adding a Link on the Home Page to the Employees Report

In this exercise, you add a link on the Home page to take users to the Employees report. This link appears in the Navigation region on the left side of the Home page.

To add a link on the Home page to the Employees report:

1. Navigate to the Page Definition for the Home page, page 1:

   1. Click Application in the Developer toolbar (at the bottom of the page).

   2. On the Application home page, click Home.

2. Under Regions, click the List link (next to Navigation).

3. On the List Entries page, click the Create List Entry button on the right side of the page.

4. On the Create/Edit List Entry page, edit the following:

   1. Sequence - Change to 20.

   2. List Entry Label - Enter Employees.

   3. Page - Enter 3.

5. Click Create.

6. Run the page.

   
   Description of the illustration bldap_homeemp.gif
   
   

   Notice that your Home page now includes a link to Employees.

7. Click Employees to test the link.

Linking the Employees and Departments Reports

In this exercise, you link the Employees and Departments reports. To do this, you create a new region and new item on the Employees page to allow the user to select a department. You then modify the Employees report to only display the employees that belong to the selected department.

Topics in this section include:

• Creating a Region

• Creating an Item

• Linking the Item to the Report

• Creating a Branch

Linking a Column Value to Another Page

In this exercise, you add a link on the Departments report that will navigate the user to the Employees report. It will also set the focus to the selected department.

To make this change, you edit report attributes on the Departments report.

To change the column value to a link:

1. Navigate to the Page Definition for the Departments page, page 2.

2. Under Regions, click Report.

   The Region Attributes page appears.

3. Locate the Column Attributes section.

4. From Column Alignment, select right for Number of Employees.

5. Click the Edit icon next to Number of Employees.

6. Scroll down to the Column Link section.

7. Under Column Link:

   1. Link Text - Select #Number of Employees#.

   2. Link Attributes - Enter the following:

      alt="View Employees" title="View Employees"
      

   3. Page - Enter 3.

   4. Select the Reset Pagination check box.

   5. Item 1, Name - Select P3-DEPARTMENT_ID.

   6. Item 1, Value - Select #Department ID#.

      The Column Link section shows all the options you selected.

      
      Description of the illustration bldap_setnumlink.gif
      
      

      In summary, these options enable the following behavior when the end user clicks the link:

      • The end user is navigated to page 3, the Employees report.

      • On the Employees report, the selected department is in focus.

      • The user is returned to the first set of employee records when the user selects another department.

   7. Click Apply Changes.

      Note that in the Column Attributes section, the Number of Employees row contains a check mark in the Link column, reflecting the change you just made.

8. Run the page.

   Note that end users can now drill down on the data in the Number of Employees column.

   
   Description of the illustration bldap_numemplink.gif
   
   

9. In the Number of Employees column, click a number to test the link.

   Only those employees that belong to the department you selected appear in the Employees report.

Adding a Conditional Department Details Report

In this exercise, you add a report as a region that displays department details. You also create a condition so that the report displays department information only if you select a department. If you select No Department Assigned, the Department Details report does not appear.

Topics in this section include:

• Creating the Report and Condition

• Turning Off Pagination

Adding Your Company Name

You can add a graphical logo or text to your application so that it will display on each page. Your page template determines the display location.

In this exercise, you add your company name to your application.

To add your company name to your application:

1. Navigate to the Application home page. Click the Application link on the Developer toolbar (at the bottom of the page).

2. Click Shared Components.

3. Under Application, click Definition.

   The Edit Application Definition page appears.

4. Under Logo:

   1. Logo Type - Select Text.

   2. Logo - Enter AnyCo Corp.

   3. Logo Attributes - Select Black Text.

5. Click Apply Changes.

6. Run the application.

The company name now appears on each page in your application.

Switching Themes to Change User Interface

In this exercise, you switch the current theme to another one for your application. A theme is a collection of templates that defines the application user interface. Each theme contains templates for every type of application component and page control, including individual pages, regions, reports, lists, labels, menus, buttons, and list of values.

Application Express separates presentation (or user interface themes) from the application logic. You can design your application in one theme, change to another supplied theme, or create and use your own custom theme. By separating the application logic (such as queries, processes, and branches) from the HTML rendering, your application can take advantage of new designs and other technological advances without an application rewrite. Each theme provides a complete set of templates that accommodate every user interface pattern that may be needed in an application.

To switch the theme:

1. Click Application on the Developer toolbar (at the bottom of the page).

2. On the Application home page, click Shared Components.

3. Under User Interface, click Themes.

   
   Description of the illustration bldap_sharcomp.gif
   
   

4. On the Themes page, click Create.

   The Create Theme Wizard appears.

5. For Method, accept the default, From the Repository, and click Next.

6. For Identify Theme, select Theme 11 and click Next.

7. For Confirm, click Create.

8. Click Switch Theme.

9. For Identify Theme, accept the default, 11. Round Green, and click Next.

10. For Verify Compatibility, review the information, accept the defaults, and click Next.

    The wizard verifies that corresponding templates exist in the new theme for all templates used in your current application. If a template is missing, it warns you with a message in the Status column. For this tutorial, you can ignore this message because Application Express maps missing region templates to existing ones.

11. For Confirm Switch, click Switch Theme.

12. Run the page.

    
    Description of the illustration bldap_newtheme2.gif
    
    

    The new color scheme and display layouts are changed in all your pages.