Skip Headers
Oracle® Database Application Express Advanced Tutorials
Release 3.0

B28842-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

14 How to Build and Deploy an Issue Tracking Application

This tutorial describes how to create and deploy an application that tracks the assignment, status, and progress of issues related to a project. This tutorial walks you through all the steps necessary to create a robust issue tracking application, including planning the project, creating the underlying database objects, loading demonstration data, and building a rich user interface.

Note:

This tutorial takes approximately four to five hours to complete. It is recommended that you read through the entire document first to become familiar with the material before you attempt specific exercises.

Topics in this section include:

Planning and Project Analysis

Effective project management is the key to completing any project on time and within budget. Within every project there are always multiple issues that need to be tracked, prioritized, and managed.

In this business scenario, MRVL Company has several projects that must be completed on time for the company to be profitable. Any missed project deadlines will result in lost revenue. The company's project leads use various methods to track issues, including manually recording statuses in notebooks, organizing issues in text documents, and categorizing issues by using spreadsheets.

By creating a hosted application in Oracle Application Express, project leads can easily record and track issues in one central location. This approach offers each project lead access to just the data they need and makes it easier for management to determine if critical issues are being addressed.

Planning and Project Analysis

Before beginning development on an Oracle Application Express application, you first need to define application requirements. Then, you use the defined requirements to design a database and an outline that describes how the user interface accepts and presents data.

For this business scenario, the project leads establish requirements that define the information that must be tracked, security requirements, data management functions, and how to present data to users.

Topics in this section include:

Gather the Necessary Data

Currently, each project lead tracks information slightly differently. Together, everyone agrees that the application should include the following information:

  • Summary of the issue

  • Detailed description of the issue

  • Who identified the issue

  • The date on which the issue was identified

  • Which project the issue is related to

  • Who the issue is assigned to

  • A current status of the issue

  • Priority of the issue

  • Target resolution date

  • Actual resolution date

  • Progress report

  • Resolution summary

Define Security Requirements

Because the project leads are concerned about everyone having access to all the information, they agree upon the following access rules:

  • Each team member and project lead is only assigned to one project at a time

  • Each team member and project lead must be assigned to a project

  • Managers are never assigned to a specific project

  • Only managers can define and maintain projects and people

  • Everyone can enter new issues

  • Once assigned, only the person assigned or a project lead can change data about the issue

  • Management needs views that summarize the data without access to specific issue details

Select Data Management Functions

Next, the project leads determine how information will be entered into the system. For this project, users must be able to:

  • Create issues

  • Assign issues

  • Edit issues

  • Create projects

  • Maintain projects

  • Create people

  • Maintain people information

  • Maintain project assignments

Select Data Presentation Functions

Once the data is entered into the application, users need to view the data. The team decides that users must be able to view the following:

  • All issues by project

  • Open issues by project

  • Overdue issues, by project and for all

  • Recently opened issues

  • Unassigned issues

  • Summary of issues by project, for managers

  • Resolved issues by month identified

  • Issue resolution dates displayed on a calendar

  • Days to Resolve Issues by person

Define Special Function Requirements

Finally, the project leads determine that the application must support the following special functions:

  • Notify people when an issue is assigned to them

  • Notify the project lead when any issue becomes overdue

Designing the Database Objects

Once you have defined the database requirements, the next step is to turn these requirements into a database design and an outline that describes how the user interface accepts and presents data. In this step you need to think about how information should be organized in the tables in the underlying database. Given the requirements described "Planning and Project Analysis", for this project you need to create three tables:

In addition to the tables, you also need to create additional database objects, such as sequences and triggers, to support the tables. System generated primary keys will be used for all tables so that all the data can be edited without executing a cascade update.

Topics in this section include:

About the Projects Table

Each project must include project name, project start date, target date, and actual end date columns. These date columns help determine if any outstanding issues are jeopardizing the project end date. Table 14-1 describes the columns to be included in the Projects table.

Table 14-1 Project Table Details

Column Name Type Size Not Null? Constraints Description

project_id

integer

n/a

Yes

Primary key

A unique numeric identification for each project.

Populated by a sequence using a trigger.

project_name

varchar2

100

Yes

Unique key

A unique alphanumeric name for the project.

start_date

date

n/a

Yes

None

The project start date.

target_end_date

date

n/a

Yes

None

The targeted project end date.

actual_end_date

date

n/a

No

None

The actual end date.


About the People Table

Each person will have a defined name and role. Project leads and team members will also have an assigned project. To tie the current user to their role within the organization, email addresses will be used for user names.

Table 14-2 describes the columns that will be included in the People table.

Table 14-2 People Table Details

Column Name Type Size Not Null? Constraints Description

person_id

integer

n/a

Yes

Primary key

A numeric ID that identifies each user.

Populated by a sequence using a trigger.

person_name

varchar2

100

Yes

Unique key

A unique name that identifies each user.

person_email

varchar2

100

Yes

None

User email address.

person_role

varchar2

7

Yes

Check constraint

The role assigned to each user.


Note:

For the purposes of this exercise, this application has been simplified. User data is usually much more elaborate and is often pulled from a corporate Human Resource system. Also, users typically work on more than one project at a time. If the roles that are assigned to a user need to be dynamic, you would implement roles as a separate table with a foreign key that relates to the people table.

About the Issues Table

When the project leads defined their application requirements, they decided to track separate issues assigned to each person. Issues will be included in columns along with additional columns to provide an audit trail. The audit trail will track who created the issue, when it was created, as well as who modified the issue last and on what date that modification was made.

Table 14-3 describes the columns to be included in the Issues table.

Table 14-3 Issue Table Details

Column Name Type Size Not Null? Constraints Description

issue_id

integer

n/a

Yes

primary key

A unique numeric ID that identifies an issue.

Populated by a sequence using a trigger.

issue_summary

varchar2

200

Yes

None

A brief summary of the issue.

issue_description

varchar2

2000

No

None

A detailed description of the issue.

identified_by

integer

n/a

Yes

foreign key to People

The user who identifies the issue.

identified_date

date

n/a

Yes

None

The date the issue was identified

related_project

integer

n/a

Yes

foreign key to Projects

Projects related to the issue.

assigned_to

integer

n/a

No

foreign key to People

The person who owns this issue.

status

varchar2

8

Yes

check constraint

The issue status. Automatically set to Open when new and set to Closed when actual resolution date entered.

priority

varchar2

6

No

check constraint

The priority of the issue.

target_resolution_date

date

n/a

No

None

The target resolution date.

progress

varchar2

2000

No

None

The progress of the issue.

actual_resolution_date

date

n/a

No

None

Actual resolution date of the issue.

resolution_summary

varchar2

2000

No

None

Resolution summary.

created_date

date

n/a

Yes

None

Populated by a trigger.

created_by

varchar2

60

Yes

None

User who created this issue.

last_modified_date

date

n/a

No

None

Populated by a trigger.


Note:

A real-world application might need more extensive auditing. For example, you might need to track each change to the data rather than just the last change. Tracking each change to the data would require an additional table, linked to the issues table. If the valid priorities assigned to issues need to be dynamic, you would be required to add a separate table with a foreign key that relates to the issues table.

Implementing Database Objects

This first step in building an application is to create the database objects.

Topics in this section include:

About Building Database Objects

There are several ways to create objects in Oracle Application Express. You can:

  • Create an Object in Object Browser. Use Object Browser to create tables, views, indexes, sequences, types, packages, procedures, functions, triggers database links, materialized views, and synonyms. A wizard walks you through the choices necessary to create the selected database object. To create an object in Object Browser, navigate to SQL Workshop, then Object Browser, and click Create. See "Managing Database Objects with Object Browser" in Oracle Database Application Express User's Guide.

  • Execute SQL Commands. Run SQL Commands by typing or pasting them into the SQL Commands. To access SQL Commands, click the SQL Workshop icon on Workspace home page and then click SQL Commands. See "Using SQL Commands" in Oracle Database Application Express User's Guide.

  • Upload a script. Upload a script to the SQL Script Repository that contains all the necessary create object statements. To upload a script, click SQL Workshop on the Workspace home page, click SQL Scripts and then click Upload. See "Uploading a SQL Script" in Oracle Database Application Express User's Guide.

  • Create script online. Create a script online in the Script Repository. You will use this method to create database objects for this exercise. To create a script online, click the SQL Workshop icon on the Workspace home page, select SQL Scripts and then click Create. See "Creating a SQL Script in the Script Editor" in Oracle Database Application Express User's Guide.

For this exercise, you create and run a script.

Create and Run a Script

To build database objects by creating a script:

  1. Log in to Oracle Application Express.

  2. On the Workspace home page, click SQL Workshop and then SQL Scripts.

  3. Click Create.

  4. In the Script Editor:

    1. For Script Name, enter DDL for Issue Management Application.

    2. Copy the data definition language (DDL) in "Creating Application Database Objects DDL" and paste it into the script.

    3. Click Save.

  5. On the SQL Scripts page, click the DDL for Issue Management Application icon.

    The Script Editor appears.

  6. Click Run.

    A summary page appears.

  7. Click Run again.

    The Manage Script Results page displays a message that the script has been submitted for execution.

View the Created Database Objects

You can view database objects using Object Browser.

To view database objects in Object Browser:

  1. Return to the Workspace home page. Click the Home breadcrumb link.

  2. On the Workspace home page, click SQL Workshop and then Object Browser.

  3. From the Object list on the left side of the page, select Tables.

  4. To view the details of a specific object, select one of the following tables:

    • HT_ISSUES

    • HT_PEOPLE

    • HT_PROJECTS

See Also:

"Managing Database Objects with Object Browser" in Oracle Database Application Express User's Guide.

Loading Demonstration Data

Once you have created all the necessary database objects, the next step is to load data into the tables. You can manually load data using the import functionality available in SQL Scripts. In the following exercise, however, you use SQL Scripts to load demonstration data.

Look at the DDL you copied from "Creating Application Database Objects DDL". Notice that the sequences used for the primary keys start at 40 in order to leave room for the demonstration data. The BEFORE INSERT triggers are coded so that the sequence is only accessed if a primary key value is not provided: they will not need to be disabled in order for you to load data.

Topics in this section include:

Load Projects Data

To import data into the Projects table:

  1. Click the SQL Workshop breadcrumb link.

  2. Click SQL Scripts.

  3. Click Create.

  4. In the Script Editor, specify the following:

    1. Script Name - Enter Load Project Data.

    2. Script - Copy and paste the following:

      INSERT INTO ht_projects
            (project_id, project_name, start_date, target_end_date)   VALUES
            (1, 'Internal Infrastructure', sysdate-150, sysdate-30)
      /
      INSERT INTO ht_projects 
            (project_id, project_name, start_date, target_end_date)   VALUES
            (2, 'New Payroll Rollout', sysdate-150, sysdate+15)
      /
      INSERT INTO ht_projects 
            (project_id, project_name, start_date, target_end_date)   VALUES
            (3, 'Email Integration', sysdate-120, sysdate-60)
      /
      INSERT INTO ht_projects 
            (project_id, project_name, start_date, target_end_date)   VALUES
            (4, 'Public Website Operational', sysdate-60, sysdate+30)
      /
      INSERT INTO ht_projects 
            (project_id, project_name, start_date, target_end_date)   VALUES
            (5, 'Employee Satisfaction Survey', sysdate-30, sysdate+60)
      /
      
      
    3. Click Save.

  5. On the SQL Scripts page, click the Load Project Data icon.

    The Script Editor appears.

  6. Click Run.

    A summary page appears.

  7. Click Run again.

    The Manage Script Results page displays a message that the script has been submitted for execution.

Update Dates to Make the Projects Current

Although you have created the projects, the dates need to be updated to make the projects current. To accomplish this, you run another script.

To update the project dates and make the projects current:

  1. Click the SQL Scripts breadcrumb link.

  2. Click Create.

  3. In the Script Editor:

    1. Script Name - Enter Update Project Dates.

    2. In Script, copy and paste the following:

      UPDATE ht_projects
         SET start_date = sysdate-150,
             target_end_date = sysdate-30
       WHERE project_id = 1
      /
      
      UPDATE ht_projects
         SET start_date = sysdate-150,
             target_end_date = sysdate+15
       WHERE project_id = 2
      /
      UPDATE ht_projects
         SET start_date = sysdate-120,
             target_end_date = sysdate-60
       WHERE project_id = 3
      /
      UPDATE ht_projects
         SET start_date = sysdate-60,
             target_end_date = sysdate+30
       WHERE project_id = 4
      /
      UPDATE ht_projects
         SET start_date = sysdate-30,
             target_end_date = sysdate+60
       WHERE project_id = 5
      /
      
      
    3. Click Save.

  4. On the SQL Scripts page, click the Update Project Dates icon.

    The Script Editor appears.

  5. Click Run.

  6. Click Run again.

    The Manage Script Results page displays a message that the script has been submitted for execution.

Load People Data

After you have loaded data into the Project table, you can load People data. Because of foreign keys in the Projects table, People data must be loaded after Project data. You load data into the People table by creating and running a script in SQL Workshop.

To load data into the People table:

  1. Click the SQL Scripts breadcrumb link.

  2. On the SQL Scripts page, click Create.

  3. In the Script Editor:

    1. Script Name - Enter Load People Data.

    2. Script - Copy and paste the following:

      INSERT INTO ht_people
           (person_id, person_name, person_email, person_role, assigned_project)
        VALUES
           (1, 'Joe Cerno', 'joe.cerno@mrvl-bademail.com', 'CEO', null)
      /
      INSERT INTO ht_people
           (person_id, person_name, person_email, person_role, assigned_project)
        VALUES
           (2, 'Kim Roberts', 'kim.roberts@mrvl-bademail.com', 'Manager', null)
      /
      INSERT INTO ht_people
           (person_id, person_name, person_email, person_role, assigned_project)
        VALUES
           (3, 'Tom Suess', 'tom.suess@mrvl-bademail.com', 'Manager', null)
      /
      INSERT INTO ht_people
           (person_id, person_name, person_email, person_role, assigned_project)
        VALUES
           (4, 'Al Bines', 'al.bines@mrvl-bademail.com', 'Lead', 1)
      /
      INSERT INTO ht_people
           (person_id, person_name, person_email, person_role, assigned_project)
        VALUES
           (5, 'Carla Downing', 'carla.downing@mrvl-bademail.com', 'Lead', 2)
      /
      INSERT INTO ht_people
           (person_id, person_name, person_email, person_role, assigned_project)
        VALUES
           (6, 'Evan Fanner', 'evan.fanner@mrvl-bademail.com', 'Lead', 3)
      /
      INSERT INTO ht_people
           (person_id, person_name, person_email, person_role, assigned_project)
        values
           (7, 'George Hurst', 'george.hurst@mrvl-bademail.com', 'Lead', 4)
      /
      INSERT INTO ht_people
           (person_id, person_name, person_email, person_role, assigned_project)
      VALUES
           (8, 'Irene Jones', 'irene.jones@mrvl-bademail.com', 'Lead', 5)
      /
      INSERT INTO ht_people
           (person_id, person_name, person_email, person_role, assigned_project)
        VALUES
           (9, 'Karen London', 'karen.london@mrvl-bademail.com', 'Member', 1)
      /
      INSERT INTO ht_people
           (person_id, person_name, person_email, person_role, assigned_project)
         values
           (10, 'Mark Nile', 'mark.nile@mrvl-bademail.com', 'Member', 1)
      /
      INSERT INTO ht_people
           (person_id, person_name, person_email, person_role, assigned_project)
        VALUES
           (11, 'Jane Kerry', 'jane.kerry@mrvl-bademail.com', 'Member', 5)
      /
      INSERT INTO ht_people
           (person_id, person_name, person_email, person_role, assigned_project)
        VALUES
           (12, 'Olive Pope', 'olive.pope@mrvl-bademail.com', 'Member', 2)
      /
      INSERT INTO ht_people
           (person_id, person_name, person_email, person_role, assigned_project)
        VALUES
           (13, 'Russ Sanders', 'russ.sanders@mrvl-bademail.com', 'Member', 3)
      /
      INSERT INTO ht_people
           (person_id, person_name, person_email, person_role, assigned_project)
        VALUES
           (14, 'Tucker Uberton', 'tucker.uberton@mrvl-bademail.com', 'Member', 3)
      /
      INSERT INTO ht_people
            (person_id, person_name, person_email, person_role, assigned_project)
        VALUES
            (15, 'Vicky Williams', 'vicky.willaims@mrvl-bademail.com', 'Member', 4)
      /
      INSERT INTO ht_people
            (person_id, person_name, person_email, person_role, assigned_project)
        VALUES
            (16, 'Scott Tiger', 'scott.tiger@mrvl-bademail.com', 'Member', 4)
      /
      INSERT INTO ht_people
            (person_id, person_name, person_email, person_role, assigned_project)
        VALUES
            (17, 'Yvonne Zeiring', 'yvonee.zeiring@mrvl-bademail.com', 'Member', 4)
      /
      
      
    3. Click Save.

  4. On the SQL Scripts page, click the Load People Data icon.

    The Script Editor appears.

  5. Click Run.

  6. Click Run again.

    The Manage Script Results page displays a message that the script has been submitted for execution.

Load Issues Data

The last data you need to load is the Issues data. As with People data, you create and run a script to populate the Issues table.

To load data into the Issues table:

  1. Click the SQL Scripts breadcrumb link.

  2. Click Create.

  3. In the Script Editor:

    1. Script Name - Enter Load Issue Data.

    2. Script - Copy and paste the script in "Creating Issues Script".

    3. Click Save.

  4. On the SQL Scripts page, click the Load Issue Data icon.

    The Script Editor appears.

  5. Click Run.

  6. Click Run again.

    The Manage Script Results page displays a message that the script has been submitted for execution.

Building a Basic User Interface

After you create the objects that support your application and load the demonstration data, the next step is to create a user interface. In this exercise, you use the Create Application Wizard in Application Builder to create an application and then the pages that support the data management and data presentation functions described in "Planning and Project Analysis".

Topics in this section include:

Create the Application

You use the Create Application Wizard to create an application containing pages that enable users to view reports on and create data for the selected tables within a schema. Alternatively, you can create an application first and then add pages to it. As the application requirements include customized overview pages, for this exercise you will use the latter approach.

To create the application:

  1. Click the Home breadcrumb link.

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

  3. Click Create.

  4. For Method, select Create Application and then click Next.

  5. For Name:

    1. Name - Enter Issue Tracker.

    2. Create Application - Select From scratch.

    3. Click Next.

  6. Next, add a blank page. Under Add Page:

    1. Under Select Page Type, select Blank.

    2. Click Add Page.

    3. Click Next.

  7. For Tabs, select No Tabs and then click Next.

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

  9. For Attributes, accept the defaults for Authentication Scheme, Language, and User Language Preference Derived From and click Next.

  10. For User Interface, select Theme 10 and click Next.

  11. Click Create.

To view the application:

  1. Click the Run Application icon on the Applications home page.

  2. When prompted, enter your workspace user name and password and then click Login. See "About Application Authentication".

    This authentication is part of the default security of any newly created application. As shown in Figure 14-1, the home page appears.

    Figure 14-1 Issue Tracking Application Home Page

    Description of Figure 14-1 follows
    Description of "Figure 14-1 Issue Tracking Application Home Page"

    Although the page has no content, notice that the Create Application Wizard has created the following items:

    • Navigation Links - A navigation bar entry displays in the upper right of the page. Logout enables the user to log out of the application.

    • Developer Links - The Developer toolbar appears on the page. These links only display if you are logged in as a developer. Users who only have access to run the application cannot see these links.

  3. Click Application on the Developer toolbar to return to the Application home page.

    Notice that the Create Application Wizard also created a Login page.

Once you have created the basic application structure, the next step is to create individual pages.

Add Pages to Maintain Projects

First, you need to create pages that enable users to view and add data to tables. To accomplish this, you use the Form on a Table with Report Wizard. This wizard creates a report page and maintenance page for each table.

Topics in this section include:

Create Pages for Maintaining Projects

To create pages for maintaining the HT_PROJECTS table:

  1. On the Application home page, click Create Page.

  2. Select Form and then click Next.

  3. Select Form on a Table with Report and click Next.

  4. For Table/View Owner, select the appropriate schema and then click Next.

  5. For Table/View Name, select HT_PROJECTS and click Next.

  6. For Define Report Page:

    1. Page Number: Enter 2.

    2. Page Name and Region Title - Enter Projects.

    3. Accept the remaining defaults and click Next.

  7. For Tab Options, accept the default, Do not use tabs, and then click Next.

  8. For Select Column(s), select all columns except PROJECT_ID and then click Next.

    Note that Project Name is unique and identifies the project. The ID was added to simplify the foreign key and enable cascading updates.

  9. For Edit Link Image, select the fourth option (the word Edit) and then click Next.

  10. For Define Form Page:

    1. Page - Enter 3.

    2. Page Name and Region Title - Enter Create/Edit Project.

    3. Click Next.

  11. For Tab Options, accept the default, Do not use tabs, and click Next.

  12. For Primary Key, accept the default, PROJECT_ID and click Next.

  13. For Source Type, accept the default, Existing Trigger, and click Next.

  14. For Select Column(s), select all columns and click Next.

  15. Under Identify Process Options, accept the defaults for Insert, Update and Delete, and click Next.

  16. Review your selections and click Finish.

  17. Click the Run Page icon.

As shown in Figure 14-2, the newly created report displays the demo data.

Figure 14-2 Projects Page

Description of Figure 14-2 follows
Description of "Figure 14-2 Projects Page"

Click the Edit link to view an existing row or click the Create button to create a new record. If you click Edit to the left of Employee Satisfaction Survey, a form resembling Figure 14-3 appears.

Figure 14-3 Create/Edit Project Form

Description of Figure 14-3 follows
Description of "Figure 14-3 Create/Edit Project Form"

Refine the Appearance of the Projects Report Page

You can change the appearance of the Projects report page by adding a format mask to the dates.

To add a format mask to the dates on the Create/Edit Project page:

  1. Go to the Page Definition for page 2, Projects:

    1. Click Application on the Developer toolbar.

    2. On the Application home page, click 2 - Projects.

  2. Under Regions, click Report next to Projects.

  3. Edit the format for START_DATE:

    1. Click the Edit icon to the left of START_DATE.

      The Column Attributes page appears.

    2. Under Column Formatting, for Number/Date Format, enter DD-MON-YYYY.

  4. Edit the format for the TARGET_END_DATE:

    1. Click the Next button (>) at the top of the page to go to the next Report Item.

      The Column Attributes page appears.

    2. Under Column Formatting, for Number/Date Format, enter DD-MON-YYYY.

  5. Edit the format for the ACTUAL_END_DATE:

    1. Click the Next button (>) at the top of the page to go to the next Report Item.

      The Column Attributes page appears.

    2. Under Column Formatting, for Number/Date Format, enter DD-MON-YYYY.

  6. Click Apply Changes.

    The Report Attributes page appears.

  7. For PROJECT_ID, delete the Heading Edit.

  8. For the START_DATE, TARGET_END_DATE and ACTUAL_END_DATE columns, select center for Column Alignment and Heading Alignment.

    Next, enable users to sort on column headings.

  9. To enable column heading sorting, check Sort for all columns except PROJECT_ID.

  10. For PROJECT_NAME, select 1 for Sort Sequence.

    This selection specifies PROJECT_NAME as the default column to sort on. Note this functionality can be overridden by any user selections.

  11. Scroll down to Sorting. For Ascending and Descending Image, select the light gray arrow.

  12. Scroll down to Messages. In When No Data Found Message, enter the following:

    No Projects found.
    
    
  13. At the top of the page, click Apply Changes.

To view your changes, click the Run Page icon in the upper right of the page.

As shown in Figure 14-4, note the addition of a sort control on the Project Name column and the format of the dates in the Start Date and Target End Date columns.

Figure 14-4 Projects Page with Sort Control

Description of Figure 14-4 follows
Description of "Figure 14-4 Projects Page with Sort Control"

Refine the Create/Edit Project Page

Next, you need to customize the Create/Edit Project page to make the Project Name field larger and the date fields smaller. You also need to change the date picker type, add a format mask for dates, and add validations that check if the target and actual end dates are after the start date.

Edit Fields

To make the Project Name field larger and the date fields smaller:

  1. Go to the Page Definition for Page 3, Create/Edit Project:

    1. From the Developer toolbar, click Application.

    2. Click 3 - Create/Edit Project.

  2. Under Items, click the Edit All icon.

    The Edit All icon resembles a small grid with a pencil on top of it.

  3. Scroll to the right and locate the Width column:

    1. For Project Name, enter 60.

    2. For Start Date, enter 12.

    3. For Target End Date, enter 12.

    4. For Actual End Date, enter 12.

    5. Click Apply Changes.

  4. Return to the Page Definition. Click the Edit Page icon in the upper right corner. The Edit Page icon resembles a small green piece of paper and pencil.

Change the Date Picker Type

To change the date picker type and add a format mask for dates:

  1. Edit the item P3_START_DATE.

    1. Under Items, click P3_START_DATE.

    2. From the Display As list in the Name section, select Date Picker (DD-MON-YYYY).

    3. Click Apply Changes.

  2. Edit the item P3_TARGET_END_DATE.

    1. Under Items, select P3_TARGET_END_DATE.

    2. From the Display As list in the Name section, select Date Picker (DD-MON-YYYY).

    3. Click Apply Changes.

  3. Edit the item P3_ACTUAL_END_DATE.

    1. Under Items, select P3_ACTUAL_END_DATE.

    2. From the Display As list in the Name section, select Date Picker (DD-MON-YYYY).

    3. Click Apply Changes.

Add a Validation

Next, add validations to check if the target and actual end dates are after the start date.

To add validations:

  1. Under Page Processing, Validations, click the Create icon.

  2. For Level, accept the default Item level validation and click Next.

  3. For Item, select Create/Edit Project: 40. P3_TARGET_END_DATE (Target End Date) and click Next.

  4. For Validation Method, select PL/SQL and click Next.

  5. Specify the type of validation you want to create. Accept the default PL/SQL Expression and click Next.

  6. For Validation Name, enter TARGET_AFTER_START and click Next.

  7. For Validation:

    1. Validation - Enter:

      to_date(:P3_ACTUAL_END_DATE,'DD-MON-YYYY') >= to_date(:P3_START_DATE,'DD-MON-YYYY')
      
      
    2. Error Message - Enter:

      Actual End Date must be same or after Start Date.
      
      
    3. Click Next.

  8. For Conditions:

    1. Condition Type - Select Value of Item in Expression 1 Is NOT NULL, or click the shortcut link [item not null].

    2. Expression 1 - Enter:

      P3_ACTUAL_END_DATE.
      
      

      This selection ensures that this validation executes only if the user enters an Actual End Date.

    3. Click Create.

To view your changes, click the Run Page icon in the upper right of the page. (See Figure 14-5.)

Figure 14-5 Modified Create/Edit Project

Description of Figure 14-5 follows
Description of "Figure 14-5 Modified Create/Edit Project "

Add Pages to Track People

Once the initial Projects pages are complete, you create pages for maintaining people.

Topics in this section include:

Create Pages for Maintaining People

To create pages for maintaining the HT_PEOPLE table:

  1. Click Application on the Developer toolbar.

  2. Click Create Page.

  3. Select Form and click Next.

  4. Select Form on a Table with Report and click Next.

  5. For Table/View Owner, select the appropriate schema and click Next.

  6. For Table/View Name, select HT_PEOPLE and click Next.

  7. For Define Report Page:

    1. Page - Enter 4.

    2. Page Name and Region Title - Enter People.

    3. Click Next.

  8. For Tab Options, accept the default, Do not use tabs, and click Next.

  9. For Select Column(s), select all columns except PERSON_ID and click Next.

  10. For Edit Link Image, select the fourth option (the word Edit) and click Next.

  11. For Define Form Page:

    1. Page Number - Enter 5.

    2. Page Name and Region Title - Enter Create/Edit Person Information.

    3. Click Next.

  12. For Tab Options, accept the default, Do not use tabs, and click Next.

  13. For Primary Key, accept the default, PERSON_ID, and click Next.

  14. Specify the source for the primary key columns. Accept the default, Existing Trigger, and click Next.

  15. For Select Column(s), select all the columns and click Next.

  16. For Insert, Update and Delete, accept the defaults and click Next.

  17. Review your selections and then click Finish.

To preview your page, click Run Page. As shown in Figure 14-6, notice the newly created report displays the demo data.

To preview the page for adding or editing people, click the Edit button in the far left column.

Modify the People Report Page

Next, you alter the People Report by changing the query to include a join to the Projects table and modify the headings.

Change the Query to Include a Join

To change the query to include a join to the Projects table:

  1. Go to the Page Definition for page 4 - People:

    1. If you are viewing a running form, click Application on the Developer toolbar.

    2. On the Application home page, click 4 - People.

  2. Under Regions, click People.

  3. Scroll down to Source.

  4. In Region Source, replace the existing query with the following:

    SELECT a."PERSON_ID", 
           a."PERSON_NAME",
           a."PERSON_EMAIL",
           a."PERSON_ROLE",
           b."PROJECT_NAME"
       FROM "#OWNER#"."HT_PEOPLE" a,
            "#OWNER#"."HT_PROJECTS" b
     WHERE a.assigned_project = b.project_id (+)
    
    

    Note that the outer join is necessary because the project assignment is optional.

Modify the Headings

To modify the headings:

  1. Click the Report Attributes tab at the top of the page.

  2. Under Column Attributes, edit the following column headings:

    1. PERSON_ID - Remove the Heading Edit.

    2. PERSON_NAME - Change Heading to Name.

    3. PERSON_EMAIL - Change Heading to Email.

    4. PERSON_ROLE - Change Heading to Role.

    5. PROJECT_NAME - Change Heading to Assigned Project and then select left for Heading Alignment.

      Next, enable column heading sorting.

  3. To enable column heading sorting:

    1. Select Sort for all columns except PERSON_ID.

    2. For PERSON_NAME, select 1 for Sort Sequence.

      This selection specifies PERSON_NAME as the default column to sort on. Note this functionality can be overridden by user selections.

    3. Scroll down to Sorting. For Ascending and Descending Image, select the light gray arrow.

  4. Under Messages, enter the following in When No Data Found Message:

    No people found.
    
    
  5. Click Apply Changes at the top of the page.

Run the Page

To view your changes, click the Run Page icon in the upper right of the page. As shown in Figure 14-7, note the addition of a sort control on the Name column.

Figure 14-7 Revised People Page

Description of Figure 14-7 follows
Description of "Figure 14-7 Revised People Page"

Refine the Create/Edit People Page

Next, you customize the Create/Edit People page by adding lists of values to make it easier for users to select a Role or Assigned Project.

Add a List of Values for Projects

To add a list of values for Projects:

  1. Go to the Page Definition for page 5, Create/Edit Person:

    1. If you are viewing a form, click Application on the Developer toolbar.

    2. On the Application home page, click 5 - Create/Edit Person Information.

  2. Under Shared Components, locate the Lists of Values section and then click the Create icon.

  3. For Source, accept the default, From Scratch, and then click Next.

  4. For Name and Type:

    1. Name - Enter PROJECTS.

    2. Type - Select Dynamic.

    3. Click Next.

  5. In Query, replace the existing statements with the following:

    SELECT project_name d, project_id v
      FROM ht_projects
     ORDER BY d
    
    
  6. Click Create List of Values.

Add a List of Values for Roles

To add a list of values for Roles:

  1. Under Shared Components, locate the Lists of Values section and then click the Create icon.

  2. For Source, accept the default, From Scratch, and then click Next.

  3. For Name and Type:

    1. Name - Enter ROLES.

    2. Type - Select Static

    3. Click Next.

  4. Enter the display value and return value pairs shown in Table 14-4:

    Table 14-4 Display Value and Return Value pairs

    Display Value Return Value

    CEO

    CEO

    Manager

    Manager

    Lead

    Lead

    Member

    Member


  5. Click Create List of Values.

  6. On the Lists of Values page, click the Edit Page icon in the upper right corner.

Edit Display Attributes

To edit display attributes for P5_PERSON_ROLE:

  1. Under Items, click P5_PERSON_ROLE.

  2. From the Display As list in the Name section, select Radiogroup.

  3. Scroll down to Label.

  4. Change Label to Role.

  5. Under Element, enter the following in Form Element Option Attributes:

    class="instructiontext"
    
    

    This specifies that the text associated with each radio group option is the same size as other items on the page.

  6. Scroll down to List of Values.

  7. From the Named LOV list, select ROLES.

  8. Click Apply Changes.

To edit display attributes for P5_ASSIGNED_PROJECT:

  1. Under Items, click P5_ASSIGNED_PROJECT.

  2. From the Display As list in the Name section, select Select List.

  3. Scroll down to List of Values.

  4. Under List of Values:

    1. From the Named LOV list, select PROJECTS.

      Next, specify that the underlying column is not mandatory.

    2. For Null display value, enter:

      - None -
      
      
  5. Click Apply Changes.

To alter the display of fields and field labels:

  1. Under Items, click the Edit All icon.

  2. For P5_PERSON_NAME:

    1. Prompt - Enter Name.

    2. Width - Enter 60.

  3. For P5_PERSON_EMAIL:

    1. Prompt - Enter Email Address.

    2. For Width, enter 60.

  4. Click Apply Changes.

  5. Click the Edit Page icon in the upper right corner to return to the Page Definition for Page 5.

Create a Validation

The Form on a Table with Report Wizard created not null validations for Name, Email, and Role. You must manually create another validation to ensure that Leads and Members have an assigned project while the CEO and Managers do not. As a best practice, it is generally best to use built-in validation types because they are faster. However, for this compound type of validation, you will write a PL/SQL validation.

To add validations to ensure the correct people are assigned projects:

  1. Under Page Processing, Validations, click the Create icon.

  2. For Level, accept the default, Item level validation, and click Next.

  3. For Item, select Create/Edit Person Information: 50. P5_ASSIGNED_PROJECT (Assigned Project) and click Next.

  4. For Validation Method:

    1. Select PL/SQL and click Next.

    2. Accept the default, PL/SQL Expression and click Next.

  5. For Sequence and Name:

    1. Validation Name - Enter PROJECT_MAND_FOR_LEADER_AND_MEMBER.

    2. Accept the remaining defaults and click Next.

  6. For Validation and Error Message:

    1. Validation - Enter:

      (:P5_PERSON_ROLE IN ('CEO','Manager') AND
      :P5_ASSIGNED_PROJECT = '%'||'null%') OR
      (:P5_PERSON_ROLE IN ('Lead','Member') AND
      :P5_ASSIGNED_PROJECT != '%'||'null%')
      
      

      Oracle Application Express passes nulls as %null%. It also replaces %null% with a null when it processes data. Therefore, to keep it in the validation, you need to break the string apart so that it is not recognized and replaced.

    2. Error Message - Enter:

      Leads and Members must have an Assigned Project. CEO and Managers cannot have an Assigned Project.
      
      
    3. Click Next.

  7. Click Create.

Run the Page

To view your changes, click the Run Page icon in the upper right of the page. The revised form appears as shown in Figure 14-8.

Figure 14-8 Revised Create/Edit Person Information Form

Description of Figure 14-8 follows
Description of "Figure 14-8 Revised Create/Edit Person Information Form"

Try entering some records to test the validation. Enter a CEO with a project and then enter a Lead without a project. Both cases should fail and display the error message you defined.

Add Pages to Track Issues

Lastly, you need to create pages for HT_ISSUES. This application needs multiple views on Issues. You can create these views as single reports or as separate reports. For this exercise, you create a complex report that includes an Issues maintenance form. You then link this maintenance form in multiple places. Ultimately, the Issues report will display Issues by the person who identified the issue, project, assigned person, status, or priority.

Topics in this section include:

Create a Report for HT_ISSUES

To create a report for maintaining HT_ISSUES:

  1. Click Application on the Developer toolbar.

  2. Click Create Page.

  3. Select Form and click Next.

  4. Select Form on a Table with Report and click Next.

  5. For Table/View Owner, select the appropriate schema and click Next.

  6. For Table/View Name, select HT_ISSUES and click Next.

  7. On Define Report Page:

    1. Page Number - Enter 6.

    2. Page Name and Region Title - Enter Issues.

    3. Click Next.

  8. For Tab Options, accept the default, Do not use tabs, and click Next.

  9. For Select Column(s):

    1. Press CTRL to select the following columns:

      • ISSUE_SUMMARY

      • IDENTIFIED_BY

      • RELATED_PROJECT

      • ASSIGNED_TO

      • STATUS

      • PRIORITY

      • TARGET_RESOLUTION_DATE

      • ACTUAL_RESOLUTION_DATE

    2. Click Next.

  10. For Edit Link Image, select the fourth option (the word Edit) and click Next.

  11. On Define Form Page:

    1. Page Number - Enter 7.

    2. For Page Name and Region Title, enter Create/Edit Issues.

    3. Click Next.

  12. For Tab Options, accept the default, Do not use tabs, and click Next.

  13. For Primary Key, accept the default, ISSUE_ID, and click Next.

  14. For Define the source for the primary key columns, accept the default, Existing Trigger, and click Next.

  15. For Select Column(s), select all the columns and click Next.

  16. For Insert, Update and Delete, accept the default value, Yes, and click Next.

  17. Review your selections and click Finish.

  18. Click Edit Page.

Refine the Create/Edit Issues Page

You refine the Create/Edit Page for the following reasons:

  • Add lists of values to make it easier for users to select foreign key columns

  • Organize and clean up items

  • Change the display of audit columns

  • Add a button to make data entry faster

Add Lists of Values

Next, you need to add lists of values for Status, Priorities, and People.

To add a list of values for Status:

  1. Go to the Page Definition for page 7, Create/Edit Issues. On the Page Definition, enter 7 in the Page field and click Go.

  2. Under Shared Components, Lists of Values, click the Create icon.

  3. For Create List of Values, accept the default, From Scratch, and click Next.

  4. On Create List of Values:

    1. Name - Enter STATUS.

    2. For Type, select Static.

    3. Click Next.

  5. Enter the Display Value and Return Value pairs shown in Table 14-5:

    Table 14-5 Display Value and Return Value Pairs

    Display Value Return Value

    Open

    Open

    On-Hold

    On-Hold

    Closed

    Closed


  6. Click Create List of Values.

To add a list of values for Priorities:

  1. On the Lists of Values page, click Create.

  2. For Create List of Values, accept the default, From Scratch, and click Next.

  3. On Create List of Values:

    1. Name - Enter PRIORITIES.

    2. For Type, select Static.

    3. Click Next.

  4. Enter the Display Value and Return Value pairs shown in Table 14-6.

    Table 14-6 Display Value and Return Value Pairs

    Display Value Return Value

    High

    High

    Medium

    Medium

    Low

    Low


  5. Click Create List of Values.

To add a list of values for People:

  1. On the Lists of Values page, click Create.

  2. For Create List of Values, accept the default, From Scratch, and click Next.

  3. On Create List of Values:

    1. For Name, enter PEOPLE.

    2. For Type, select Dynamic.

    3. Click Next.

  4. In Query, replace the existing statements with the following:

    SELECT person_name d, person_id v
       FROM ht_people
     ORDER BY 1
    
    
  5. Click Create List of Values.

  6. Go to the Page Definition for page 7.

Edit Specific Items

Next, you edit individual items.

To edit P7_IDENTIFIED_BY:

  1. Under Items on the Page Definition for Page 7, click P7_IDENTIFIED_BY.

  2. From the Display As list in the Name section, select Select List.

  3. Under List of Values:

    1. Named LOV - Select PEOPLE.

    2. Display Null - Select Yes. The base column is mandatory, but you do not want the first name in the list becoming the default value.

    3. Null display value - Enter:

       - Select Person -
      
      
  4. Click the Next button (>) at the top of the page to go to the next item, P7_IDENTIFIED_DATE.

    The Edit Page Item page appears.

To edit P7_IDENTIFIED_DATE:

  1. From the Display As list in the Name section, select Date Picker (DD-MON-YYYY).

  2. Scroll down to Default:

    1. Default value - Enter:

      to_char(sysdate,'DD-MON-YYYY')
      
      
    2. Default Value Type - Select PL/SQL Expression.

  3. Click the Next button (>) at the top of the page to go to the next item, P7_RELATED_PROJECT.

    The Edit Page Item page appears.

To edit P7_RELATED_PROJECT:

  1. From the Display As list in the Name section, select Select List.

  2. Scroll down to List of Values. For List of Values:

    1. Named LOV - Select PROJECTS.

    2. Display Null- Select Yes.

    3. Null display value - Enter:

       - Select Project -
      
      
  3. Click the Next button (>) at the top of the page until you go to P7_STATUS.

To edit P7_STATUS:

  1. From the Display As list in the Name section, select Radiogroup.

  2. Under Label, enter the following in the Label field:

    Status:
    
    
  3. Under Element, enter the following in the Form Element Option Attributes field:

    class="instructiontext"
    
    
  4. Under Default, enter Open in the Default Value field.

  5. Under List of Values:

    1. Named LOV - Select STATUS.

    2. Number of Columns - Enter 3.

      This selection enables the three valid values to display side by side.

  6. Click the Next button (>) at the top of the page to go to P7_PRIORITY.

To edit P7_PRIORITY:

  1. From the Display As list in the Name section, select Radiogroup.

  2. Under Label, enter the following in the Label field:

    Priority:
    
    
  3. Under Element, enter the following in the Form Element Option Attributes field:

    class="instructiontext"
    
    
  4. Under Default, enter Open in Default value.

  5. Under List of Values:

    1. Named LOV - Select PRIORITIES.

    2. Display Null - Select Yes.

    3. Number of Columns - Enter 4.

      This selection reflects the fact there are three valid values plus the null value.

    4. Null display value - Enter the following.

      None-
      
      
  6. Click the Next button (>) at the top of the page to go to P7_TARGET_RESOLUTION_DATE.

To edit P7_TARGET_RESOLUTION_DATE:

  1. From the Display As list in the Name section, select Date Picker (DD-MON-YYYY).

  2. Click the Next button (>) at the top of the page until you go to P7_ACTUAL_RESOLUTION_DATE.

To edit P7_ACTUAL_RESOLUTION_DATE:

  1. From the Display As list in the Name section, select Date Picker (DD-MON-YYYY).

  2. Click Apply Changes.

Create Regions to Group Items

Currently all items are grouped into one large region. Displaying items in logical groups makes data entry easier for users. Therefore, you next create four new regions named Buttons, Progress, Resolution, and Audit Information. You also rename an existing region.

To create new regions to group items:

  1. Under Regions, click the Create icon.

  2. Select Multiple HTML and then click Next.

  3. For the first row:

    • For Sequence, enter 5.

    • For Title, enter Buttons.

    • For Template, select Button Region without Title.

  4. For the second row, in Title enter Progress.

  5. For the third row, in Title enter Resolution.

  6. For the fourth row, in Title enter Audit Information.

  7. Click Create Region(s).

Now that the new regions exist, rename the first region, Create/Edit Issues:

  1. Under Regions, click Create/Edit Issues.

  2. In Title, enter:.

    Issue Identification
    
    
  3. Click Apply Changes.

Move Items to the Appropriate Regions

Next, move each item to the appropriate region. Note that you also need to modify some item widths.

To move items to the appropriate regions:

  1. Under Items, click the Edit All icon.

    The Page Items summary page appears.

  2. Under Region, select Progress for the following items:

    • P7_ASSIGNED_TO

    • P7_STATUS

    • P7_PRIORITY

    • P7_TARGET_RESOLUTION_DATE

    • P7_PROGRESS

  3. Under Region, select Resolution for the following items:

    • P7_ACTUAL_RESOLUTION_DATE

    • P7_RESOLUTION_SUMMARY

  4. Under Region, select Audit Information for the following items:

    • P7_CREATED_DATE

    • P7_CREATED_BY

    • P7_LAST_MODIFIED_DATE

    • P7_LAST_MODIFIED_BY

  5. Under Width, make the following edits:

    1. For P7_ISSUE_SUMMARY, enter 60.

    2. For P7_IDENTIFIED_DATE, enter 12.

    3. For P7_TARGET_RESOLUTION_DATE, enter 12.

    4. For P7_ACTUAL_RESOLUTION_DATE, enter 12.

  6. Click Apply Changes.

  7. Click the Edit Page icon in the upper right to return to the Page Definition of Page 7.

To move buttons to the Button region:

  1. Under Buttons, click the Edit All icon.

  2. Under Region, select Buttons for all buttons.

  3. Click Apply Changes.

  4. Click the Edit Page icon in the upper right to return the Page Definition of Page 7.

Change the Display of Audit Columns

Because the Audit columns should be viewable but not editable, you need to make them display only. In the following exercise, you create a condition for the Audit Information region. As a result, the Audit Information region displays when a user edits an existing issue, but does not appear when a user creates a new issue.

To create a condition for the Audit Information region.

  1. Under Regions, click Audit Information.

  2. Scroll down to Conditional Display.

  3. From Condition Type, select Value of Item in Expression 1 is NOT NULL.

  4. In Expression 1, enter the following:

    P7_ISSUE_ID
    
    
  5. Click Apply Changes.

Next, change the audit columns to display only.

To edit P7_CREATED_DATE:

  1. Under Items, click P7_CREATED_DATE.

  2. From the Display As list in the Name section, select Display as Text (saves state).

  3. Under Label:

    1. For Label, enter:

      Created Date:
      
      
    2. For Template, select Optional Label with Help.

    3. For HTML Table Cell Attributes, enter:

      class="instructiontext"
      
      
  4. Under Source, enter the following in Format Mask:

    DD-MON-YYYY
    
    
  5. Click the Next button (>) at the top of the page to go to the next item, P7_CREATED_BY.

To edit P7_CREATED_BY:

  1. From the Display As list in the Name section, select Display as Text (saves state).

  2. Under Label:

    1. For Label, enter:

      Created By:
      
      
    2. For Template, select Optional Label with Help.

    3. For HTML Table Cell Attributes, enter:

      class="instructiontext"
      
      
  3. Click the Next button (>) at the top of the page to go to the next item, P7_LAST_MODIFIED_DATE.

To edit P7_LAST_MODIFIED_DATE:

  1. From the Display As list in the Name section, select Display as Text (saves state).

  2. Under Label:

    1. For Label, enter:

      Last Modified Date:
      
      
    2. For Template, select Optional Label with Help.

    3. For HTML Table Cell Attributes, enter:

      class="instructiontext"
      
      
  3. Under Source, enter the following in Format Mask:

    DD-MON-YYYY
    
    
  4. Click the Next button (>) at the top of the page to go to the next item, P7_LAST_MODIFIED_BY.

To edit P7_LAST_MODIFIED_BY:

  1. From the Display As list in the Name section, select Display as Text (saves state).

  2. Under Label:

    1. For Label, enter:

      Last Modified By:
      
      
    2. For Template, select Optional Label with Help.

    3. For HTML Table Cell Attributes, enter:

      class="instructiontext"
      
      
  3. Click Apply Changes.

Remove Unnecessary Validations

The Form on a Table with Report Wizard created not null validations for Issue Summary, Identified By, Related Project, Status, Created Date, and Created By. Since the Audit columns are set by a trigger, you need to remove these validations.

To remove not null validations:

  1. Delete P7_CREATED_DATE not null:

    1. Under Page Processing, Validations, click P7_CREATED_DATE not null.

    2. Click Delete.

    3. Click OK to confirm your selection.

  2. Delete P7_CREATED_BY not null:

    1. Under Page Processing, Validations, click P7_CREATED_DATE not null.

    2. Click Delete.

    3. Click OK to confirm your selection.

Return the User to the Calling Page

Because this Create/Edit page will be called from several places, when users finish with the display, they should return to the calling page. To accomplish this, you create an item and change the branch on the Create/Edit page. Every time the Create/Edit page is called, the item must be set with the number of the calling page.

To create a hidden item:

  1. Under Items, click the Create icon.

  2. For Select Item Type, select Hidden and click Next.

  3. For Display Position and Name:

    1. Item Name - Enter:

      P7_PREV_PAGE
      
      
    2. Region - Select Issue Identification.

    3. Click Next.

  4. Click Create Item.

    The Page Definition for page 7 appears.

    Next, edit the Cancel button.

To edit the Cancel button:

  1. Under Buttons, click Cancel.

  2. Scroll down to Optional URL Redirect.

  3. In Page, enter:

    &P7_PREV_PAGE.
    
    

    Note the period at the end.

  4. Click Apply Changes.

    Next, edit the branch.

To edit the branch:

  1. Under Branches, select the After Processing branch, Go to Page.

  2. Under Action, enter the following in Page (be sure to include the period):

    &P7_PREV_PAGE.
    
    
  3. Click Apply Changes.

Add Functionality to Support Adding Multiple Issues Sequentially

Next, you add functionality that enables users to add more than one issue at a time. To accomplish this, you first add a new button and then create a new branch.

To add a new button:

  1. Under Buttons, click the Copy icon.

  2. For Button to copy, click CREATE.

  3. For Target Page, accept the default, 7, and click Next.

  4. For New Button:

    1. Button Name - Enter CREATE_AGAIN.

    2. Label - Enter Create and Create Another.

    3. Click Copy Button.

Functionally, the Copy Button currently works the same as the CREATE button. Next, create a branch that keeps the user on the Create page.

Note that this branch also resets P7_PREV_PAGE because the value of that item will be lost when the cache of the page is cleared. The sequence of this new branch will be 0. Setting the sequence to 0 makes the branch fire before the default branch but only when the Create and Create Another button is used.

To create a branch that keeps the user on the create page:

  1. Under Page Processing, Branches, click the Create icon.

  2. For Point and Type, accept the defaults and click Next.

  3. For Target:

    1. Page - Enter 7.

    2. Clear Cache - Enter 7.

    3. Set these items - Enter the following:

      P7_PREV_PAGE
      
      
    4. With these values - Enter the following (be sure to include the period):

      &P7_PREV_PAGE.
      
      
    5. Click Next.

  4. For Branch Conditions:

    1. Sequence - Enter 0.

    2. When Button Pressed - Select CREATE_AGAIN.

  5. Click Create Branch.

    The Page Definition for page 7 appears.

  6. Under Branches, select the newly created branch, Go to Page, next to 7.

  7. Under Action, select the include process success message check box.

  8. Click Apply Changes.

Run the Page

To see the changes, click the Run Page icon. The new form appears as shown in Figure 14-9.

Figure 14-9 Create/Edit Issues Form

Description of Figure 14-9 follows
Description of "Figure 14-9 Create/Edit Issues Form"

The branch you just created is looking for a value in P7_PREV_PAGE. Since the page was not called from another page, the value has not been set. You need to fix that next.

Refine the Issues Report

Next, you refine the Issues report page to support dynamic modification of the query. To accomplish this, you must:

  • Move the Create button to a new region and edit the label

  • Create new items that enable the user to restrict the query

  • Add a WHERE clause to reference those new items

  • Alter the report column attributes to display each person's name and the project

  • Modify headings

Move Create Button to a New Region

To create a new region for the Create button:

  1. Go to the Page Definition for page 6, Issues.

  2. Under Regions, click the Create icon.

  3. Select HTML and click Next.

  4. Select HTML for region container and click Next.

  5. For Display Attributes:

    1. Title - Enter Buttons.

    2. Region Template - Select Button Region without Title.

    3. Display Point - Select Page Template Body (2. items below region content).

    4. Click Next.

  6. Click Create Region.

To move the Create button to the Buttons region:

  1. Under Buttons, click the Create link.

  2. Under Name, enter the following in the Text Label/Alt field:

    Add a New Issue
    
    
  3. Under Displayed, select Buttons in the Display in Region field.

  4. Under Optional URL Redirect:

    1. Set These Items - Enter:

      P7_PREV_PAGE
      
      
    2. For With These Values, enter 6.

  5. Click Apply Changes.

Change the Query and Display

Next, change the query to display the actual values for people and projects instead of the ID and then clean up the report display.

To edit column attributes for ISSUE_ID:

  1. Under Regions, select Report next to Issues.

    The Report Attributes page appears.

  2. Click the Edit Icon to the left of ISSUE_ID.

  3. Scroll down to Column Link. Under Column Link:

    1. For Item 2, Name, enter:

      P7_PREV_PAGE
      
      
    2. For Item 2, Value, enter 6.

  4. Click Apply Changes.

To edit column attributes for IDENTIFIED_BY, RELATED_PROJECT and ASSIGNED_TO:

  1. Click the Edit Icon to the left of IDENTIFIED_BY.

  2. Scroll down to Tabular Form Element. From Display As, select Display as Text (based on LOV, does not save state).

  3. Scroll down to Lists of Values. From Named LOV, select PEOPLE.

  4. Return to the top of the page and click the Next (>) icon.

    The Column Attributes page for RELATED_PROJECT appears.

  5. Scroll down to Tabular Form Element. From Display As, select Display as Text (based on LOV, does not save state).

  6. Scroll down to Lists of Values. Under List of Values:

    1. Named LOV - Select PROJECTS.

    2. Display Null - Select Yes.

    3. Null Text - Enter a hyphen (-).

  7. Return to the top of the page and click the Next (>) icon.

    The Column Attributes page for ASSIGNED_TO appears.

  8. Scroll down to Tabular Form Element. From Display As, select Display as Text (based on LOV, does not save state).

  9. Scroll down to Lists of Values. Under List of Values:

    1. Named LOV - Select PEOPLE.

    2. Display Null - Select Yes.

    3. Null Text - Enter a hyphen (-).

  10. Click Apply Changes.

    The Report Attributes page appears.

Next, you customize how the report displays by changing report attributes.

To alter the report display:

  1. From Headings Type (the radiogroup row at the top of Column Attributes), accept the default, Custom.

  2. For ISSUE_ID, delete the Heading text.

  3. For ISSUE_SUMMARY, change the Heading to Summary.

  4. For TARGET_RESOLUTION_DATE:

    1. Force the heading to wrap. In Heading, enter:

      Target<br>Resolution<br>Date
      
      
    2. For Column Alignment, select center.

    3. For Heading Alignment, select center.

  5. To sort on ISSUE_ID:

    1. For all columns except ISSUE_ID, check Sort.

    2. For ISSUE_SUMMARY, select 1 for Sort Sequence.

  6. Scroll down to Layout and Pagination. Specify the following:

    1. Show Null Values as - Enter a hyphen (-).

    2. Number of Rows - Enter 5.

  7. Under Sorting, select the light gray arrow for Ascending and Descending Image.

  8. Under Messages, enter the following in When No Data Found Message:

    No issues found.
    
    
  9. Click Apply Changes.

Add Support for Filtering

Although the report now displays nicely, it does not support filtering by the end user. To add this functionality, you first create items that enable the user to set values to query against. You will store these new items in a new region that will display above the report.

To create a new region:

  1. Under Regions, click the Create icon.

  2. Select HTML and then click Next.

  3. Select HTML for region container and click Next.

  4. For Display Attributes:

    1. For Title, enter Issue Report Parameters.

    2. For Region Template, accept the default, Reports Region.

    3. For Sequence, enter 5.

    4. Click Next.

  5. Click Create Region.

Next, create the items.

To create the item for Identified By:

  1. Under Items, click the Create icon.

  2. For Select Item Type, select Select List and click Next.

  3. For Select List Control Type, accept the default selection, Select List, and click Next.

  4. For Display Position and Name:

    1. For Item Name - Enter P6_IDENTIFIED_BY.

    2. For Region, select Issue Report Parameters.

    3. Click Next.

  5. For Identify List of Values:

    1. For Named LOV, select PEOPLE.

    2. For Null Text, enter:

      - All -
      
      
    3. For Null Value, enter:

      -1
      
      
    4. Click Next.

  6. For Item Attributes, accept the defaults and click Next.

  7. For Source, Default, enter:

    -1
    
    
  8. Click Create Item.

To create an item for Assigned To:

  1. Under Items, click the Create icon.

  2. For Select Item Type, select Select List and click Next.

  3. For Select List Control Type, accept the default selection, Select List, and click Next.

  4. For Display Position and Name:

    1. For Item Name, enter P6_ASSIGNED_TO.

    2. For Region, select Issue Report Parameters.

    3. Click Next.

  5. For Identify List of Values:

    1. Named LOV - Select PEOPLE.

    2. For Null Text - Enter:

      - All -
      
      
    3. For Null Value - Enter:

      -1
      
      
    4. Click Next.

  6. For Item Attributes, accept the defaults and click Next.

  7. For Source, Default, enter:

    -1
    
    
  8. Click Create Item.

To create an item for Status:

  1. Under Items, click the Create icon.

  2. For Select Item Type, select Select List and click Next.

  3. For Select List Control Type, accept the default selection, Select List, and then click Next.

  4. For Display Position and Name:

    1. Item Name - Enter P6_STATUS.

    2. For Region - Select Issue Report Parameters.

    3. Click Next.

  5. For Identify List of Values:

    1. For Named LOV - Select STATUS.

    2. For Null Text - Enter:

      - All -
      
      
    3. For Null Value - Enter:

      -1
      
      
    4. Click Next.

  6. For Item Attributes, accept the defaults and click Next.

  7. For Source, Default, enter:

    -1
    
    
  8. Click Create Item.

To create an item for Priority:

  1. Under Items, click the Create icon.

  2. For Select Item Type, select Select List and click Next.

  3. For Select List Control Type, accept the default selection, Select List, and click Next.

  4. For Identify Item Name and Display Position:

    1. For Item Name - Enter P6_PRIORITY.

    2. For Region - Select Issue Report Parameters.

    3. Click Next.

  5. For List of Values:

    1. For Named LOV - Select PRIORITIES.

    2. For Null Text - Enter:

      - All -
      
      
    3. For Null Value - Enter:

      -1
      
      
    4. Click Next.

  6. For Identify Item Attributes, accept the defaults and click Next.

  7. For Source, for Default, enter:

    -1
    
    
  8. Click Create Item.

To create an item for Related Project:

  1. Under Items, click the Create icon.

  2. For Select Item Type, select Select List and click Next.

  3. For Select List Control Type, accept the default selection, Select List and click Next.

  4. For Display Position and Name:

    1. For Item Name - Enter P6_RELATED_PROJECT.

    2. For Region - Select Issue Report Parameters.

    3. Click Next.

  5. For Identify List of Values:

    1. For Named LOV - Select PROJECTS.

    2. For Null Text - Enter:

      - All -
      
      
    3. For Null Value - Enter:

      -1
      
      
    4. Click Next.

  6. For Item Attributes, accept the defaults and click Next.

  7. For Source, for Default, enter:

    -1
    
    
  8. Click Create Item.

Next, create a Go button. This button enables the user to execute the query once they select report parameters. Buttons can be created in region positions or displayed among items.

To create Go button:

  1. Under Buttons, click the Create icon.

  2. For Button Region, select Issue Report Parameters and click Next.

  3. For Button Position, select Create a button displayed among this region's items.

    This selection displays the button to the right of the last report parameter.

  4. Click Next.

  5. For Button Attributes:

    1. Button Name - Enter P6_GO.

    2. Button Style - Select Template Based Button.

    3. Template - Select Button.

  6. Click Create Button.

    The Page Definition for page 6 appears.

Currently the items display stacked on top of one another. To use space more efficiently, change the position of P6_RELATED_PROJECT, P6_STATUS, and P6_PRIORITY so they display next to each other. Place P6_RELATED_PROJECT, P6_STATUS on the first line and P6_PRIORITY on the second line.

To change the position of P6_RELATED_PROJECT, P6_STATUS, and P6_PRIORITY:

  1. Under Items, click the Edit All icon.

  2. For P6_RELATED_PROJECT, P6_STATUS, and P6_PRIORITY, select No for New Line.

  3. Click Apply Changes.

  4. Click the Edit Page icon in the upper right corner to return to the Page Definition for page 6. The Edit Page icon resembles a small green piece of paper and pencil.

Next, you need to modify the report to react to the parameters. To accomplish this, you need to modify the query's WHERE clause as follows:

WHERE (IDENTIFIED_BY = :P6_IDENTIFIED_BY OR 
       :P6_IDENTIFIED_BY = '-1')
   AND (RELATED_PROJECT = :P6_RELATED_PROJECT OR 
       :P6_RELATED_PROJECT = '-1')
   AND (ASSIGNED_TO = :P6_ASSIGNED_TO OR 
       :P6_ASSIGNED_TO = '-1')
   AND (STATUS = :P6_STATUS OR 
       :P6_STATUS = '-1')
   AND (PRIORITY = :P6_PRIORITY OR 
       :P6_PRIORITIY = '-1')

To use the preceding WHERE clause, you must convert the Issues region into a PL/SQL Function Body Returning a SQL Query.

To turn the Issues region into a PL/SQL Function Body Returning a SQL Query:

  1. Under Regions, click Issues.

  2. Under Name, for Type, select SQL Query (PL/SQL function body returning SQL query).

  3. For Region Source, replace the existing statements with the following:

    DECLARE
    
       q VARCHAR2(32767); -- query
       w VARCHAR2(4000) ; -- where clause
       we VARCHAR2(1) := 'N'; -- identifies if where clause exists
    
    BEGIN
    
       q := 'SELECT "ISSUE_ID", '||
            ' "ISSUE_SUMMARY", '||
            ' "IDENTIFIED_BY", '||
            ' "RELATED_PROJECT", '||
            ' "ASSIGNED_TO", '||
            ' "STATUS", '||
            ' "PRIORITY", '||
            ' "TARGET_RESOLUTION_DATE", '||
    
            ' "ACTUAL_RESOLUTION_DATE" '||
            ' FROM "#OWNER#"."HT_ISSUES" '; 
    
       IF :P6_IDENTIFIED_BY != '-1'
          THEN 
          w := ' IDENTIFIED_BY = :P6_IDENTIFIED_BY ';
          we := 'Y';
       END IF;
    
       IF :P6_RELATED_PROJECT != '-1'
          THEN 
          IF we = 'Y'
             THEN 
             w := w || ' AND RELATED_PROJECT = :P6_RELATED_PROJECT ';
          ELSE
             w := ' RELATED_PROJECT = :P6_RELATED_PROJECT ';
             we := 'Y';
          END IF;
       END IF;
    
       IF :P6_ASSIGNED_TO != '-1'
          THEN 
          IF we = 'Y'
             THEN 
             w := w || ' AND ASSIGNED_TO = :P6_ASSIGNED_TO ';
        ELSE
             w := ' ASSIGNED_TO = :P6_ASSIGNED_TO ';
             we := 'Y';
        END IF;
       END IF;
    
       IF :P6_STATUS != '-1'
          THEN 
          IF we = 'Y'
             THEN 
             w := w || ' AND STATUS = :P6_STATUS ';
          ELSE
             w := ' STATUS = :P6_STATUS ';
             we := 'Y';
          END IF;
    END IF;
    
       IF :P6_PRIORITY != '-1'
           THEN 
           IF we = 'Y'
              THEN 
              w := w || ' AND PRIORITY = :P6_PRIORITY ';
           ELSE
              w := ' PRIORITY = :P6_PRIORITY ';
              we := 'Y';
           END IF;
       END IF;
    
       IF we = 'Y'
          THEN q := q || ' WHERE '|| w;
       END IF;
    
    RETURN q;
    
    END;
    
    
  4. Click Apply Changes.

Note that this function first sets the variable q to the original SELECT statement. It then builds a WHERE clause (w) composed of just the variables set by the user. If any variables have been set, it appends the WHERE clause to the original SELECT and passes that new SELECT to the database.

Run the Page

The report is now complete. Click the Run Page icon. The revised report appears and should resemble Figure 14-10.

Figure 14-10 Issues Report

Description of Figure 14-10 follows
Description of "Figure 14-10 Issues Report"

To change the report parameters, make new selections under Issue Report Parameters and then click Go.

Add a Page to Support Assigning Multiple Issues Simultaneously

Currently, you can assign an issue by editing it. Next, you add a new page that enables users to assign multiple issues at once and modify the Related Project, Status, and Priority.

Create a Tabular Form

To add a new page to support assigning multiple issues:

  1. Go to the Application home page.

  2. Click Create Page.

  3. Select Form and click Next.

  4. Select Tabular Form and click Next.

  5. For Table/View Owner:

    1. Table/View Owner- Select the appropriate schema.

      For this exercise, assume that the purpose of this form is to enable users to assign issues, or update existing records, and not create or delete issues.

    2. Allowed Operations - Select Update Only.

    3. Click Next.

  6. For Table/View Name, select HT_ISSUES and click Next.

  7. For Displayed Columns:

    1. Press CTRL and select the following columns:

      • ISSUE_SUMMARY

      • IDENTIFIED_BY

      • IDENTIFIED_DATE

      • RELATED_PROJECT

      • ASSIGNED_TO

      • STATUS

      • PRIORITY

    2. Click Next.

  8. For Primary Key, accept the default, ISSUE_ID, and click Next.

  9. For Primary Key Source, accept the default, Existing trigger, and click Next.

  10. For Updatable Columns:

    1. Press CTRL and select the following columns:

      • RELATED_PROJECT

      • ASSIGNED_TO

      • STATUS

      • PRIORITY

    2. Click Next.

  11. For Page and Region Attributes:

    1. Page - Enter 8.

    2. Page Name - Enter Assign Issues.

    3. Region Title - Enter Assign Issues.

    4. Click Next.

  12. For Tab Options, accept the default, Do not use tabs, and then click Next.

  13. For Button Labels:

    1. For Cancel Button Label, accept the default.

    2. For Submit Button Label, enter Apply Changes.

    3. Click Next.

  14. For Branching, accept the defaults and click Next.

  15. Review your selections and then click Finish.

Add Lists of Values

Once you have created the initial tabular form, you need to add lists of values to make it easier to select issues. Additionally, you need to restrict the query to display only unassigned issues.

To add lists of values:

  1. From the Success page, click Edit Page.

    The Page Definition for page 8, Assign Issues, appears.

  2. Under Regions, click Assign Issues.

  3. Under Source, for Region Source, replace the existing statements with the following:

    SELECT "ISSUE_ID",
           "ISSUE_SUMMARY",
           "IDENTIFIED_BY",
           "IDENTIFIED_DATE",
           "RELATED_PROJECT",
           "ASSIGNED_TO",
           "STATUS",
           "PRIORITY"
      FROM "#OWNER#"."HT_ISSUES"
    WHERE assigned_to IS NULL
    
    

To edit report attributes:

  1. Select the Report Attributes tab at the top of the page.

    Edit the heading for ISSUE_SUMMARY.

  2. For ISSUE_SUMMARY, enter the following in the Heading field:

    Summary
    
    
  3. To sort by ISSUE_ID:

    1. For all columns except ISSUE_ID, select Sort.

    2. For IDENTIFIED_DATE, for Sort Sequence, select 1.

  4. Edit the following attributes for IDENTIFIED_BY:

    1. Click the Edit icon to the left of IDENTIFIED_BY.

    2. Under Tabular Form Element, for Display As, select Display as Text (based on LOV, does not save state).

    3. Scroll down to Lists of Values.

    4. For Named LOV, select PEOPLE.

    5. Click the Next button (>) at the top of the page to go to IDENTIFIED_DATE.

  5. Edit the following attributes for IDENTIFIED_DATE:

    1. Under Column Formatting, for Number/Date Format, enter DD-MON-YYYY.

    2. Click the Next button (>) at the top of the page to go to the RELATED_PROJECT column.

  6. Edit the following attributes for RELATED_PROJECT:

    1. Under Tabular Form Element, for Display As, select Select List (named LOV).

    2. Under List of Values, for Named LOV, select PROJECTS.

    3. Click the Next button (>) at the top of the page to go to the ASSIGNED_TO column.

  7. Edit the following attributes for ASSIGNED_TO:

    1. Under Tabular Form Element, for Display As, select Select List (named LOV).

    2. Under List of Values:

      • Named LOV - Select PEOPLE.

      • Display Null - Select Yes.

      • Null Text - Enter a hyphen (-).

    3. Click the Next button (>) at the top of the page to go to the STATUS column.

  8. Edit the following attributes for STATUS:

    1. Under Tabular Form Element, for Display As, select Select List (named LOV).

    2. Under List of Values, for Named LOV, select STATUS.

    3. Click the Next button (>) at the top of the page to go to the PRIORITY column.

  9. Edit the following attributes for PRIORITY:

    1. Under Tabular Form Element, for Display As, select Select List (named LOV).

    2. Under List of Values:

      • From Named LOV, select PRIORITIES.

      • For Display Null, select Yes.

      • For Null Text, enter a hyphen (-).

    3. Click Apply Changes.

    The Report Attributes page appears.

  10. Scroll down to Sorting. Under Ascending and Descending Image, select the light gray arrow.

  11. Under Messages, enter the following in When No Data Found Message:

    No Unassigned Issues.
    
    
  12. Click Apply Changes.

Delete the Unnecessary Cancel Button

The wizard created an unnecessary Cancel button.

To delete the Cancel button:

  1. On the Page Definition for page 8, click CANCEL in the Buttons section.

  2. Click Delete.

  3. Click OK to confirm your selection.

Run the Page

The tabular form is now complete. To view the new form, click the Run Page icon. The Assign Issues form appears as shown in Figure 14-11.

Figure 14-11 Assign Issues

Description of Figure 14-11 follows
Description of "Figure 14-11 Assign Issues"

To assign an issue, make a selection from the Assigned To list and then click Apply Changes. Notice that once an issue has been assigned, the issue no longer displays.

Create Summary Reports

Lastly, you need to add four summary reports.

Topics in this section include:

Add an Issue Summary by Project Report

The Issue Summary report enable users to select a project and then see a summary of issues related to that project. This report includes the following summary information:

  • Date first issue identified

  • Date last issue closed

  • Total number of issues

  • Number of issues by status

  • Number of open issues by priority

  • Assignments by status

To create this report, you code the information in two SQL statements. The first statement gathers information having a singular result and the second statement gathers information having multiple results.

To add an Issue Summary by Project report:

  1. Go to the Application home page.

  2. Click Create Page.

  3. Select Report and click Next.

  4. Select SQL Report and click Next.

  5. For Page Attributes:

    1. Page - Enter 9.

    2. Page Name - Enter Issue Summary by Project.

    3. Click Next.

  6. For Tab Options, accept the default, Do not use tabs, and click Next.

  7. For SQL Query:

    1. Enter the following SQL SELECT statement:

      SELECT MIN(identified_date) first_identified, 
             MAX(actual_resolution_date) last_closed,
             COUNT(issue_id) total_issues,
             SUM(DECODE(status,'Open',1,0)) open_issues,
             SUM(DECODE(status,'On-Hold',1,0)) onhold_issues,
             SUM(DECODE(status,'Closed',1,0)) closed_issues,
             SUM(DECODE(status,
                        'Open',decode(priority,null,1,0),
                        0))                     open_no_prior,
             SUM(DECODE(status,
                        'Open',decode(priority,'High',1,0),
                        0))                     open_high_prior,
             SUM(DECODE(status,
                        'Open',decode(priority,'Medium',1,0),
                        0))                     open_medium_prior,
             SUM(DECODE(status,
                        'Open',decode(priority,'Low',1,0),
                        0))                     open_low_prior
         FROM ht_issues
      WHERE related_project = :P9_PROJECT
      
      
    2. Click Next.

  8. For Report Attributes:

    1. Report Template - Select default: vertical report, look 1 (include null columns).

    2. For Region Name - Enter Issue Summary by Project.

    3. Accept the remaining defaults and click Next.

  9. Review your selections and click Finish.

Now that you have the first query, you need to edit the headings and create the item to control the related project. First, create a region to display above the report to contain the Project parameter.

Create a New Region

To create a new region to display above the report:

  1. From the Success page, click Edit Page.

    The Page Definition for page 9, Issue Summary by Project, appears.

  2. Under Regions, click the Create icon.

  3. Select HTML and click Next.

  4. Select HTML for region container and click Next.

  5. For Display Attributes:

    1. Title - Enter Issue Summary Report Parameters.

    2. Display Point - Select Page Template Body (2. items below region content).

    3. For Sequence, enter 5.

    4. Accept the remaining defaults and click Next.

  6. Click Create Region.

Create the Project Item

To create the Project item:

  1. Under Items, click the Create icon.

  2. For Select Item Type, select Select List and click Next.

  3. For Select List Control Type, accept the default, Select List, and click Next.

  4. For Display Position and Name:

    1. Item Name - Enter P9_PROJECT.

    2. Region - Select Issue Summary Report Parameters.

    3. Click Next.

  5. For List of Values:

    1. Named LOV - Select PROJECTS.

    2. Null Text - Enter:

      - Select -
      
      
    3. Null Text - Enter:

      - 1
      
      
    4. Click Next.

  6. For Item Attributes, accept the defaults and click Next.

  7. For Source, for Default, enter -1.

  8. Click Create Item.

Create a Go Button

To create a Go button to execute the query:

  1. Under Buttons, click the Create icon.

  2. For Button Region, select Issue Summary Report Parameters and click Next.

  3. For Button Position, select Create a button displayed among this region's items and click Next.

  4. For Button Attributes:

    1. Button Name - Enter P9_GO.

    2. Button Style - Select Template Based Button.

    3. Template - Select Button.

  5. Click Create Button.

Edit Headings and Report Settings

Next, you need to edit the headings and report setting for the report region. You also need to set the report regions to conditionally display when the user has selected a project.

To edit the headings and report settings:

  1. Under Regions, click Report next to Issue Summary by Project.

  2. For Headings Type, select Custom.

  3. Under Column Attributes:

    1. Change the Heading for FIRST_IDENTIFIED to:

      First Issue Identified:
      
      
    2. Change the Heading for LAST_CLOSED to:

      Last Issue Closed:
      
      
    3. Change the Heading for TOTAL_ISSUES to:

      Total Issues:
      
      
    4. Change the Heading for OPEN_ISSUES to:

      Open Issues:
      
      
    5. Change the Heading for ONHOLD_ISSUES to:

      On-Hold Issues:
      
      
    6. Change the Heading for CLOSED_ISSUES to:

      Closed Issues:
      
      
    7. Change the Heading for OPEN_NO_PRIOR to:

      Open Issues with No Priority:
      
      
    8. Change the Heading for OPEN_HIGH_PRIOR:

      Open Issues of High Priority:
      
      
    9. Change the Heading for OPEN_MEDIUM_PRIOR to:

      Open Issues of Medium Priority:
      
      
    10. Change the Heading for OPEN_LOW_PRIOR:

      Open Issues of Low Priority:
      
      
  4. Scroll down to Layout and Pagination. Specify the following:

    1. For Show Null Values as, enter a hyphen (-).

    2. For Pagination Scheme, select - No Pagination Selected -.

  5. Select the Region Definition tab at the top of the page.

    1. Scroll down to Conditional Display.

    2. For Condition Type, select Value of Item in Expression 1 Is NOT Contained within Colon Delimited List in Expression 2.

    3. In Expression 1, enter P9_PROJECT.

    4. For Expression 2, enter -1.

  6. Click Apply Changes.

Create a Query to Retrieve Assignments

To create a query to retrieve assignments by status.

  1. Under Regions, click the Create icon.

  2. Select Report and then click Next.

  3. For Report Implementation, select SQL Report and click Next.

  4. For Display Attributes:

    1. Title - Enter Assignments by Status.

    2. Column - Select 2.

    3. Accept the remaining defaults and click Next.

  5. For Source:

    1. SQL Query or PL/SQL function returning a SQL Query - Enter the following:

      SELECT p.person_name, 
             i.status, 
             COUNT(i.issue_id) issues
        FROM ht_issues i,
             ht_people p
      WHERE i.related_project = :P9_PROJECT
        AND i.assigned_to = p.person_id
      GROUP BY person_name, status
      
      
    2. Rows Per Page - Enter 20.

    3. Break Columns - Select Column 1.

    4. Accept the remaining defaults and click Next.

  6. For Conditional Display:

    1. Condition Type - Select Value of Item in Expression 1 Is NOT Contained within Colon Delimited List in Expression 2.

    2. In Expression 1 enter:

      P9_PROJECT
      
      
    3. For Expression 2 enter -1.

  7. Click Create Region.

To edit headings and report settings:

  1. Under Regions, click Report next to Assignments by Status.

  2. For Headings Type, select Custom.

  3. For PERSON_NAME, change Heading to Assigned To.

  4. Scroll down to Layout and Pagination. From Pagination Scheme, select Row Ranges 1-15 16-30 in select list (with pagination).

  5. Scroll down to Messages. In When No Data Found Message, enter:

    No issues found.
    
    
  6. Click Apply Changes.

Run the Page

To see your newly created report, click the Run Page icon. Note that initially no data displays since no project is selected. Select a project and click Go. Your report should resemble Figure 14-12.

Figure 14-12 Issue Summary by Project Report

Description of Figure 14-12 follows
Description of "Figure 14-12 Issue Summary by Project Report"

Add Resolved by Month Identified Report

The Resolved by Month Identified report is a line chart. This report first calculates the number of days it took to resolve each closed issue, averaged by the month the issue was identified, and finally displayed by the month.

To add a Resolved by Month Identified report:

  1. Go to the Application home page.

  2. Click Create Page.

  3. Select Chart and click Next.

  4. Select SVG Chart and click Next.

  5. Select Line and click Next.

  6. For Page Attributes:

    1. Page Number - Enter 10.

    2. Page Name and Region Name - Enter Resolved by Month Identified.

    3. Accept the remaining defaults and click Next.

  7. For Tab Options, accept the default, Do not use tabs, and click Next.

  8. For Query:

    1. Series Name - Enter Resolved.

    2. SQL - Enter the following:

      SELECT NULL l,
             TO_CHAR(identified_date,'Mon YYYY') month, 
             AVG(actual_resolution_date-identified_date) days
         FROM ht_issues
        WHERE status = 'Closed'
        GROUP BY TO_CHAR(identified_date,'Mon YYYY')
      
      

      Note that this query has no link (that is, the l column). It extracts the month from the identified date so that the data can be grouped by month. Lastly, it calculates the average number of days it took for the issues to be closed that were identified in that month.

    3. For When No Data Found Message, enter:

      No Closed Issues found.
      
      
    4. Click Next.

  9. Review your selections and click Finish.

Edit the Chart

Next, add a correct axis label and turn off the Chart Title and legend.

To edit the chart:

  1. From the Success page, select Edit Page.

    The Page Definition for page 10, Resolved by Month Identified, appears.

  2. Under Regions, click SVG Chart, next to Resolved by Month Identified.

  3. Under Chart Settings:

    1. Chart Height - Enter 300.

    2. Deselect Show Legend.

  4. Scroll to Axes Setting:

    1. For X Axis Title, enter Date Identified.

    2. For Y Axis Title, enter Days to Resolve.

  5. Click Apply Changes.

Run the Page

To view your newly created line chart, click the Run Page icon. Your line chart should resemble Figure 14-13.

Figure 14-13 Resolved by Month Identified Line Chart

Description of Figure 14-13 follows
Description of "Figure 14-13 Resolved by Month Identified Line Chart"

Add a Calendar to Display Issues

The Target Resolution Dates report is a calendar that displays issues that have not yet closed along with the assigned person on the day that corresponds to the issue target resolution date.

Create a Calendar

To create a calendar of target resolution dates:

  1. Go to the Application home page.

  2. Click Create Page.

  3. Select Calendar and click Next.

  4. Select SQL Calendar and click Next.

  5. For Page Attributes:

    1. Page Number - Enter 11.

    2. Page Name and Region Name - Enter Target Resolution Dates.

    3. Click Next.

  6. For Tab Options, accept the default, Do not use tabs, and click Next.

  7. For Table/View Owner:

    1. In Enter SQL Query, enter the following:

      SELECT I.TARGET_RESOLUTION_DATE,
             I.ISSUE_SUMMARY || 
                ' ('||nvl(P.PERSON_NAME,'Unassigned') ||') ' disp,
             I.ISSUE_ID
        FROM HT_ISSUES I, 
             HT_PEOPLE P
        WHERE I.ASSIGNED_TO = P.PERSON_ID (+)
         AND (I.RELATED_PROJECT = :P11_PROJECT OR
              :P11_PROJECT = '-1')
         
      AND I.STATUS != 'Closed'
      
      
    2. Click Next.

    Note that:

    • The target_resolution_date is the date on which the issue displays

    • The issue_summary is concatenated with the person assigned

    • The issue_id does not display, but is used to create a link to enable the user to view and edit the issue

  8. For Date/Display Columns:

    1. Date Column - Select TARGET_RESOLUTION_DATE.

    2. For Display Column - Select DISP.

    3. Click Next.

  9. Review your selections and click Finish.

Add an Item to Support Project Look Up

To enable the user to look up one project or all projects, you need to add an item.

To add an item to support project look up:

  1. From the Success page, select Edit Page.

    The Page Definition for page 11, Target Resolution Dates, appears.

  2. Under Items, click the Create icon.

  3. For Item Type, select Select List and click Next.

  4. For Select List Control Type, select Select List and click Next.

  5. For Display Position and Name:

    1. Item Name - Enter P11_PROJECT.

    2. Accept the remaining defaults and click Next.

  6. For List of Values:

    1. Named LOV - Select PROJECTS.

    2. Null Text - Enter:

      - All Projects -
      
      
    3. For Null Value - Enter:

      -1
      
      
    4. Click Next.

  7. For Item Attributes, accept the defaults and click Next.

  8. For Source, Default, enter:

    -1
    
    
  9. Click Create Item.

Create a Go Button

To create a Go button to execute the query:

  1. Under Buttons, click the Create icon.

  2. For Button Region, select Target Resolution Dates and click Next.

  3. For Button Position, select Create a button displayed among this region's items and click Next.

  4. For Button Attributes:

    1. Button Name - Enter P11_GO.

    2. Button Style - Select Template Based Button.

    3. Template - Select Button.

  5. Click Create Button.

Modify Calendar Attributes

Lastly, you need to modify the Calendar Attributes to add link support for viewing and editing the displayed issues. To accomplish this, you need to call page 7, View/Edit Issues, clear any data from the page and pass in the current issue ID along with the fact that page 11 was the calling page. Then, you need to add a note that displays when the query excludes Closed issues.

To modify the Calendar Attributes:

  1. Under Regions, click Calendar to the right of Target Resolution Dates.

  2. Scroll down to Column Link, enter the following:

    1. Page - Enter 7.

    2. Clear Cache - Enter 7.

    3. Set these items - Enter:

      P7_ISSUE_ID,P7_PREV_PAGE
      
      
    4. For With these values - Enter:

      #ISSUE_ID#,11
      
      
  3. Select the Region Definition tab at the top of the page.

  4. Scroll down to Header and Footer.

  5. In Region Footer, enter the following:

    This excludes Closed issues.
    
    
  6. Click Apply Changes.

Run the Page

To see your newly created calendar, click the Run Page icon. Your report should resemble Figure 14-14. Note that you can click Weekly or Daily to see the corresponding calendar views.

Note that you can also click the text displayed for an issue to display the Edit Issue page. To return to the calendar, click Cancel.

Figure 14-14 Target Resolution Dates Report

Description of Figure 14-14 follows
Description of "Figure 14-14 Target Resolution Dates Report"

Add a Bar Chart that Displays Average Days to Resolve

The Average Days to Resolve report is a bar chart that calculates the number of days it takes to resolve each closed issue and then averages that number by assigned person.

To add the Average Days to Resolve report:

  1. Go to the Application home page.

  2. Click Create Page.

  3. Select Chart and click Next.

  4. Select HTML Chart and click Next.

  5. Select Bar (HTML) and click Next.

  6. For Page Attributes:

    1. Page - Enter 12.

    2. Page Name and Region Name - Enter Average Days to Resolve.

    3. Accept the remaining defaults and click Next.

  7. For Tab Options, accept the default, Do not use tabs, and click Next.

  8. For Chart Definition:

    1. Chart SQL - Replace the existing statements with the following:

      SELECT NULL l,
             NVL(p.person_name,'None Assigned') person, 
             AVG(i.actual_resolution_date-i.identified_date) days   
        FROM ht_issues i,
             ht_people p
       WHERE i.assigned_to = p.person_id (+)
         AND i.status = 'Closed'
       GROUP BY p.person_name
      
      

      In the above SELECT statement:

      • The first item selected is the link. This report does not link to any other page, and so NULL was selected.

      • The second item is the person's name, or None Assigned if assigned_to is NULL.

      • The third item selected is the average number of days it took for that person to resolve all their issues so the issues have a status of closed.

    2. For Include in summary, select only Number of data points. Deselect all other options.

    3. Accept the remaining defaults and click Next.

  9. Review your selections and click Finish.

Run the Page

To view your newly created bar chart, click Run Page. Your report should resemble Figure 14-15.

Figure 14-15 Average Days to Resolve Report

Description of Figure 14-15 follows
Description of "Figure 14-15 Average Days to Resolve Report"

Add Content to the Home Page

Now that you have completed all the detail pages, you next need to add content to the home page and tie all the pages together. In this section, you modify the home page to display the following information:

  • A menu of all available reports

  • Navigation to the maintenance pages

  • A button to Add a New Issue

  • Overdue Issues

  • Recently Opened Issues

  • Open Issues by Project as a chart

  • Unassigned Issues

Topics in this section include:

Add a List

First, you add a menu implemented as a list.

To add a menu:

  1. Go to the Application home page.

  2. Click the Shared Components icon.

  3. Under Navigation, click Lists.

  4. Click Create.

  5. For Name, enter Main Menu.

  6. For List Template, select Vertical Sidebar List.

  7. Click Create.

Create List Entries

Now that the list has been created, you add list items to it. You need to add one list item for each report page.

To add a list item for Assign Issues:

  1. Click the Create List Entry button on the far right of the page.

  2. For List Entry Label, enter Assign Issues.

  3. Under Target, specify the following

    1. Page - Select 8.

    2. Select reset pagination for this page.

  4. Click Create.

    The List Entries page appears.

Now you need to create four more list items, one for each of the reports in your application.

To add four more list items:

  1. On the List Entries page, click Create List Entry.

  2. To define a list item for Issues:

    1. For Sequence, enter 20.

    2. For List Entry Label, enter Issues.

    3. Under Target, specify the following:

      • Page - Select 6.

      • Select reset pagination for this page.

      • Clear Cache - Enter 6.

        This clears any selections for page 6 from the session state.

  3. Click Create and Create Another.

  4. To define a list item for Issue Summary:

    1. For Sequence, enter 30.

    2. For List Entry Label, enter Issue Summary by Project.

    3. Under Target, specify the following:

      • Page - Select 9.

      • Select reset pagination for this page.

      • Clear Cache - Enter 9.

  5. Click Create and Create Another.

  6. To define a list item for Resolved by Month Identified:

    1. For Sequence, enter 40.

    2. For List Entry Label, enter Resolved by Month Identified (chart).

    3. Under Target, select 10 for Page.

  7. Click Create and Create Another.

  8. To define a list item for Target Resolution Dates:

    1. For Sequence, enter 50.

    2. For List Entry Label, enter Target Resolution Dates (calendar).

    3. Under Target, specify the following:

      • Page - Select 11.

      • Select reset pagination for this page.

  9. Click Create and Create Another.

  10. To define a list item for Average Days to Resolve:

    1. For Sequence, enter 60.

    2. For List Entry Label, enter Average Days to Resolve (chart).

    3. Under Target, select 12 for Page.

  11. Click Create.

Include the List on the Home Page

Now that the list is created, you need to include it on the home page. To display the list in the left margin, you need to change the page template to one that supports the appropriate region position.

To change the page template on the home page:

  1. Click the Edit Page icon in the upper right corner.

    The Page Definition for page 12, Average Days to Resolve, appears.

  2. In the Page field, enter 1 and click Go.

  3. Under Page, click the Edit page attributes icon.

    The Page attributes page appears.

  4. Under Display Attributes, for Page Template, select No Tabs with Sidebar.

  5. Click Apply Changes.

Next, create a region to contain your menu.

To create a new region:

  1. Under Regions, click the Create icon.

  2. Select List and click Next.

  3. For Display Attributes:

    1. Title - Enter Menu.

    2. Region Template - Select No Template.

    3. Display Point - Select Page Template Region Position 2 (or select the quick link [Pos. 2]).

    4. Click Next.

  4. For Source, select Main Menu.

  5. Click Create List Region.

Add Maintenance Navigation

Next, you need to add maintenance navigation as a list. This list will display just below the reports in the left margin.

  1. Go to the Application home page.

  2. Click the Shared Components icon.

  3. Under Navigation, click Lists.

  4. Click Create.

  5. For Name, enter Maintenance.

  6. For List Template, select Vertical Sidebar List.

  7. Click Create.

Next, create three list items. The first list item acts as a separator between the two navigation regions. The other two enable users to view people and projects.

To add list items:

  1. Click Create List Entry.

  2. To define the first list item:

    1. For List Entry Label, enter:

      &nbsp;
      
      
    2. Under Target, select 1 for Page.

  3. Click Create and Create Another.

  4. To define a list item for Projects:

    1. For Sequence, enter 20.

    2. For List Entry Label, enter:

      Projects
      
      
    3. Under Target:

      • For Page, select 2.

      • Check reset pagination for this page.

  5. Click Create and Create Another.

  6. To define a list item for People:

    1. For Sequence, enter 30.

    2. For List Entry Label, enter:

      People
      
      
    3. Under Target:

      • For Page, select 4.

      • Check reset pagination for this page.

  7. Click Create.

To create a region to display the new list.

  1. Click the Edit Page icon in the upper right corner.

  2. Under Regions, click the Create icon.

  3. Select List and click Next.

  4. For Display Attributes:

    1. Title - Enter Maintenance.

    2. Region Template - Select No Template.

    3. Display Point - Select Page Template Region Position 2 (or select the quick link [Pos. 2]).

    4. Click Next.

  5. For Source, select Maintenance.

  6. Click Create List Region.

Add a New Issues Button

Next, you create a button to navigate the user to page 7, Create/Edit Issue.

To create a region to contain the button:

  1. Under Regions, click the Create icon.

  2. Select HTML and click Next.

  3. Select HTML for the region container and click Next.

  4. For Display Attributes:

    1. Title - Enter Buttons.

    2. Region Template - Select No Template.

    3. Display Point - Select Page Template Region Position 1 (or select the quick link [Pos. 1]).

    4. Click Next.

  5. Click Create Region.

To add a button:

  1. Under Buttons, click the Create icon.

  2. For Button Region, select Buttons and click Next.

  3. For Button Position, accept the default, Create a button in a region position, and then click Next.

  4. For Button Attributes:

    1. Button Name - Enter ADD.

    2. Label - Enter:

      Add a New Issue
      
      
    3. Action - Select Redirect to URL without submitting page.

    4. Click Next.

  5. For Button Template, select Button and click Next.

  6. For Position, select Top of Region, accept the remaining defaults, and click Next.

    On the Branching page, you need to call the correct page, clear the cache, and specify that the Create and Cancel buttons return the user to the home page.

  7. For Branching, specify the following:

    1. Page - Select 7.

    2. Clear Cache - Enter 7.

    3. Set these items - Enter:

      P7_PREV_PAGE
      
      
    4. With these values - Enter 1.

  8. Click Create Button.

Add An Overdue Issues Report

Next, add some content to the home page. In this exercise, you add a report to display overdue issues. The query for this report retrieves all unclosed issues with a past target resolution date.

Add a Report of Overdue Issues

To add a report to display overdue issues:

  1. Under Regions, click the Create icon.

  2. Select Report and then click Next.

  3. For Report Implementation, select SQL Report and click Next.

  4. For Display Attributes, enter Overdue Issues in the Title field and click Next.

  5. For Source, enter the following in Enter SQL Query:

    SELECT i.issue_id,
           i.priority,
           i.issue_summary, 
           p.person_name assignee,
           i.target_resolution_date, 
           r.project_name
      FROM ht_issues i,
           ht_people p,
           ht_projects r
     WHERE i.assigned_to = p.person_id (+)
       AND i.related_project = r.project_id
       AND i.target_resolution_date < sysdate
       AND i.status != 'Closed'
    
    

    The outer join is necessary because the assignment is optional.

  6. Click Create Region.

Edit the Overdue Issues Report

Now that the region has been created, you need to edit the headings and turn the summary into a link to display the issue details.

To edit the column headings:

  1. Under Regions, click Report next to Overdue Issues.

  2. For Headings Type, select Custom.

  3. For ISSUE_ID, remove the Heading.

  4. For ISSUE_SUMMARY, enter the following for Heading:

    Summary
    
    
  5. For ASSIGNEE, change the Heading to:

    Assigned To
    
    
  6. For TARGET_RESOLUTION_DATE:

    1. For Heading, enter:

      Target<br>Resolution<br>Date
      
      
    2. For Column Alignment, select center.

    3. For Heading Alignment, select center.

  7. For ISSUE_ID, deselect Show.

    This enables the query to pass in the link, but not display it.

  8. To sort by issue ID, select Sort for all columns except ISSUE_ID.

  9. For TARGET_RESOLUTION_DATE, select 1 for Sort Sequence.

  10. For ISSUE_SUMMARY, select 2 for Sort Sequence.

To edit column attributes for ISSUE_SUMMARY:

  1. Click the Edit icon to the left of ISSUE_SUMMARY.

  2. Scroll down to Column Link:

    1. For Link Text, click the quick link of [Icon 3].

    2. For Page, select 7.

    3. For Clear Cache, select 7.

    4. For Item 1, enter the Name:

      P7_ISSUE_ID
      
      
    5. For Item 1, enter the Value:

      #ISSUE_ID#
      
      
    6. For Item 2, enter the Name:

      P7_PREV_PAGE
      
      
    7. For Item 2, enter the Value:

      1
      
      
  3. Click Apply Changes.

To select layout and pagination attributes:

  1. Under Layout and Pagination:

    1. For Pagination Scheme, select Search Engine 1,2,3,4 (set based pagination).

    2. For Number of Rows, enter 5.

  2. Under Sorting, select the light gray arrow for Ascending and Descending Image.

  3. Under Messages, enter the following in When No Data Found Message:

    No Overdue Issues.
    
    
  4. Click Apply Changes.

Add an Unassigned Issues Report

The next report you add displays unassigned, open issues. This report is very similar to Overdue Issues. Rather than creating it manually, you can copy the Overdue Issues report and modify it.

To create a new report by copying an existing report:

  1. Under Regions, click the Copy icon.

  2. In the Name column, click Overdue Issues.

  3. For To Page, accept the default 1, accept the remaining defaults, and click Next.

  4. For Region Name, enter Unassigned Issues.

  5. Click Copy Region.

To modify the query and edit the report region:

  1. Under the Regions section, click Unassigned Issues.

  2. For Region Source, replace the existing statements with the following:

    SELECT i.issue_id,
           i.priority,
           i.issue_summary, 
           i.target_resolution_date, 
           r.project_name,
           p.person_name identifiee
      FROM ht_issues i,
           ht_people p,
           ht_projects r
     WHERE i.assigned_to IS NULL
       AND i.status != 'Closed'
       AND i.related_project = r.project_id
       AND i.identified_by = p.person_id
    
    
  3. Select the Report Attributes tab at the top of the page.

    Note that previously defined columns have retained their modified attributes.

  4. For IDENTIFIEE, enter the following Heading:

    Identified By
    
    
  5. Under Messages, enter the following in When No Data Found Message:

    No Unassigned Issues.
    
    
  6. Click Apply Changes.

Add a Recently Opened Issues Report

Lastly, you add a report of recently opened issues. The underlying query displays the five most recently opened issues.

To create a report of recently opened issues by copying an existing report:

  1. Under Regions, click the Copy icon.

  2. Under Name, select Unassigned Issues.

  3. For To Page, accept the default 1, accept the remaining defaults, and click Next.

  4. For Region Name, enter Recently Opened Issues.

  5. Click Copy Region.

To modify the query and edit the report region:

  1. Under Regions, click Report next to Recently Opened Issues.

  2. For all columns:

    1. Disable sorting by deselecting Sort.

    2. Set Sequence to -.

  3. Select the Region Definition tab at the top of the page.

  4. For Region Source, replace the existing statements with the following:

    SELECT issue_id,
           priority,
           issue_summary, 
           assignee,
           target_resolution_date, 
           project_name,
           identifiee
    FROM 
    (
    SELECT i.issue_id,
           i.priority,
           i.issue_summary, 
           p.person_name assignee,
           i.target_resolution_date, 
           r.project_name,
           p2.person_name identifiee
      FROM ht_issues i,
           ht_people p,
           ht_people p2,
           ht_projects r
     WHERE i.assigned_to = p.person_id (+)
       AND i.related_project = r.project_id
       AND i.identified_by = p2.person_id
       AND i.created_date > (sysdate - 7)
     ORDER BY i.created_date desc
    )
     WHERE rownum < 6
    
    
  5. Select the Report Attributes tab at the top of the page.

  6. For ASSIGNEE, click the up arrow to the right of the Sort Sequence column until ASSIGNEE appears after ISSUE_SUMMARY.

  7. For ASSIGNEE, change Heading to:

    Assigned To
    
    
  8. Scroll down to the Layout and Pagination section. From Pagination Scheme, select - No Pagination Selected -.

  9. Under Messages, enter the following in When No Data Found Message:

    No Recently Opened Issues.
    
    
  10. Click Apply Changes.

Add an Open Issues by Project Pie Chart

Next, add a pie chart displaying Open Issues by Project.

To add a pie chart:

  1. Under Regions, click the Create icon.

  2. Select Chart and click Next.

  3. Select SVG Chart and click Next.

  4. Select Pie and click Next.

  5. For Display Attributes, enter Open Issues by Project in Title and then click Next.

  6. For Source, enter the following in SQL:

    SELECT NULL LINK,
           NVL(r.project_name,'No Project') label,
           COUNT(r.project_name) value
      FROM ht_issues i,
           ht_projects r
     WHERE i.status = 'Open'
       AND i.related_project = r.project_id
    GROUP BY NULL, r.project_name
    ORDER BY r.project_name
    
    

    Note that this query does not include a link, the label is the Project Name, and the value calculated and used for the pie chart is the total number of open issues by project.

  7. Click Create Region.

To edit the chart.

  1. Under Regions, click SVG Chart next to Open Issues by Project.

  2. For Chart Width, enter 500.

  3. For Chart Height, enter 200.

  4. For Chart Title, remove the title.

  5. Under Chart Query, enter the following in When No Data Found Message:

    No Open Issues.
    
    
  6. Under Font Settings, for Legend, select 14 for the Font Size.

  7. Click Apply Changes.

Run the Page

To view the revised page, click the Run Page icon. Your home page should resemble Figure 14-16.

Figure 14-16 Revised Home Page

Description of Figure 14-16 follows
Description of "Figure 14-16 Revised Home Page"

Add a Breadcrumb Menu

In the previous exercise, you created menus on the home page to enable users to navigate to various pages within your application. Next, you need to provide users with a way to navigate to the home page. You can accomplish this by utilizing breadcrumbs. You can add breadcrumbs when you create a new page, or manually after you create your pages.

In the next exercise, you add breadcrumb entries and then include that breadcrumb within a region on page 0. Adding components to page 0 makes them display on all pages with an application.

Topics in this section include:

Navigate to the Breadcrumbs Page

To navigate to the Breadcrumbs page:

  1. Go to the Application home page.

  2. Click Shared Components.

  3. Under Navigation, click Breadcrumbs.

  4. Click the Breadcrumb icon.

    The Breadcrumb Entries page appears.

Add Breadcrumb Entries

Next, you edit the existing breadcrumb entry and add breadcrumb entries for other pages.

Edit Breadcrumb Entry for Page 1

To edit the breadcrumb entry for page 1:

  1. Under Breadcrumb Entries, click Page 1.

  2. Under Breadcrumb, enter 1 in Page.

  3. Under Entry, enter Home for Short Name.

  4. Under Target, enter 1 in Page.

  5. Click Apply Changes.

Create Breadcrumb Entry for Page 2

To add a breadcrumb entry for page 2:

  1. Click Create Breadcrumb Entry.

  2. Under Breadcrumb, enter 2 in Page.

  3. Under Entry:

    1. Parent Entry - Select Home.

    2. Short Name - Enter Projects.

  4. Under Target, enter 2 in Page.

  5. Click Create.

Create Breadcrumb Entry for Page 3

To create a breadcrumb entry for page 3:

  1. Click Create Breadcrumb Entry.

  2. Under Breadcrumb, enter 3 in Page.

  3. Under Entry:

    1. Parent Entry - Select Projects.

    2. For Short Name - Enter Create/Edit Projects.

  4. Under Target, enter 3 in Page.

  5. Click Create.

Create Breadcrumb Entry for Page 4

To create a breadcrumb entry for page 4:

  1. Click Create Breadcrumb Entry.

  2. Under Breadcrumb, enter 4 in Page.

  3. Under Entry:

    1. For Parent Entry - Select Home.

    2. For Short Name - Enter People.

  4. Under Target, enter 4 in Page.

  5. Click Create.

    Note that the People breadcrumb entry is a sibling to Projects.

Create Breadcrumb Entry for Page 5

To create a breadcrumb entry for page 5:

  1. Click Create Breadcrumb Entry.

  2. Under Breadcrumb, enter 5 in Page.

  3. Under Entry:

    1. For Parent Entry - Select People.

    2. For Short Name - Enter Create/Edit Person Information.

  4. Under Target, enter 5 in Page.

  5. Click Create.

Create Breadcrumb Entry for Page 6

To create a breadcrumb entry for page 6:

  1. Click Create Breadcrumb Entry.

  2. Under Breadcrumb, enter 6 in Page.

  3. Under Entry:

    1. For Parent Entry - Select Home.

    2. For Short Name - Enter Issues.

  4. Under Target, enter 6 in Page.

  5. Click Create.

Create Breadcrumb Entry for Page 7

To create a breadcrumb entry for page 7:

  1. Click Create Breadcrumb Entry.

  2. Under Breadcrumb, enter 7 in Page.

  3. Under Entry:

    1. For Parent Entry - Select Home.

    2. For Short Name - Enter Create/Edit Issue.

  4. Under Target, enter 7 in Page.

  5. Click Create.

Create Breadcrumb Entry for Page 8

To create a breadcrumb entry for page 8:

  1. Click Create Breadcrumb Entry.

  2. Under Breadcrumb, enter 8 in Page.

  3. Under Entry:

    1. For Parent Entry - Select Home.

    2. For Short Name - Enter Assign Issues.

  4. Under Target, enter 8 in Page.

  5. Click Create.

Create Breadcrumb Entry for Page 9

To create a breadcrumb entry for page 9:

  1. Click Create Breadcrumb Entry.

  2. Under Breadcrumb, enter 9 in Page.

  3. For Entry:

    1. For Parent Entry - Select Home.

    2. For Short Name - Enter Issue Summary by Project.

  4. Under Target, enter 9 in Page.

  5. Click Create.

Create Breadcrumb Entry for Page 10

To create a breadcrumb entry for page 10:

  1. Click Create Breadcrumb Entry.

  2. Under Breadcrumb, enter 10 in Page.

  3. Under Menu Option:

    1. For Parent Entry- Select Home.

    2. For Short Name - Enter Resolved by Month Identified.

  4. Under Target, enter 10 in Page.

  5. Click Create.

Create Breadcrumb Entry for Page 11

To create a breadcrumb entry option for page 11:

  1. Click Create Breadcrumb Entry.

  2. Under Breadcrumb, enter 11 in Page.

  3. Under Entry:

    1. For Parent Entry - Select Home.

    2. For Short Name - Enter Target Resolution Dates.

  4. Under Target, enter 11 in Page.

  5. Click Create.

Create Breadcrumb Entry for Page 12

To create a breadcrumb entry for page 12:

  1. Click Create Breadcrumb Entry.

  2. Under Breadcrumb, enter 12 in Page.

  3. Under Entry:

    1. For Parent Entry - Select Home.

    2. For Short Name - Enter Average Days to Resolve.

  4. Under Target, enter 12 in Page.

  5. Click Create.

Create a Page 0

Now that the breadcrumb exists, you need to create page 0 and then create a region to contain your Breadcrumb menu. Adding components to page 0 makes them display on all pages with an application.

To create a page 0:

  1. Go to the Application home page.

  2. Click Create Page.

  3. Select Blank Page and click Next.

  4. For Page Attributes, enter 0 for Page Number and click Next.

  5. For Page Name, enter Breadcrumbs for Name and click Next.

  6. On Tabs, accept the default, No, and then click Next.

  7. Review your selections and click Finish.

Create a Region to Contain the Breadcrumb

To create a region to contain your breadcrumb:

  1. From the Success page, select Edit Page.

    The Page Definition for page 0 appears.

  2. Under Regions, click the Create icon.

  3. For Identify the type of region to add to this page, select Breadcrumb and click Next.

  4. For Breadcrumb Container Region:

    1. Region Title - Enter Breadcrumbs.

    2. Region Template - Select No Template.

    3. Display Point - Select Page Template Region Position 1.

      This selection displays the breadcrumb above all other content on the page.

    4. Click Next.

  5. For Breadcrumb, accept the defaults and click Next.

  6. For Breadcrumb Entry, accept the defaults and click Next.

  7. Click Finish.

Return to the home page by clicking Edit Page. When the Page Definition for page 0 appears, click the Next Page button (>). The Page Definition for page 1 appears.

Run the Page

To see your completed home page, click the Run Page icon. Your home page should resemble Figure 14-17.

Figure 14-17 Revised Home Page with Breadcrumb Menu

Description of Figure 14-17 follows
Description of "Figure 14-17 Revised Home Page with Breadcrumb Menu"

Notice the Breadcrumb in the top bar. Click one of the items on the Maintenance menu on the left side of the page. Notice how the breadcrumb changes.

At this stage your application is fully functional, but is missing the security and email notification. Those topics are discussed in the next section.

Adding Advanced Features

Once your application is fully functional you can focus on adding advanced features outlined during the planning and project analysis phase.

Topics in this section include:

Add Support for Email Notification

The planning and project analysis phase produced two email requirements:

  • Notify people when an issue is assigned to them

  • Notify the project lead when any issue becomes overdue

Topics in this section include:

How Email Notification Works

To send mail from within an Oracle Application Express application, you create a PL/SQL process that calls the supplied APEX_MAIL package.

Email is not sent immediately, but is stored in a temporary queue until a DBMS_JOB pushes the queue. The DBMS_JOB utilizes two preferences, SMTP Host Address and SMTP Host Port, to send mail in the queue. By default, these preferences are set to localhost and 25. If Oracle Application Express is not configured for SMTP services, you need to change your Email Environment Settings.

See Also:

"How to Send Email from an Application" in Oracle Database Application Express User's Guide to learn about configuring Email Environment settings.

The following is a description of the SEND procedure of the APEX_MAIL package.

PROCEDURE SEND
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
P_TO                           VARCHAR2                IN
P_FROM                         VARCHAR2                IN
P_BODY                         VARCHAR2                IN
P_BODY_HTML                    VARCHAR2                IN     DEFAULT
P_SUBJ                         VARCHAR2                IN     DEFAULT
P_CC                           VARCHAR2                IN     DEFAULT
P_BCC                          VARCHAR2                IN     DEFAULT

Add Notification of New Assignments

First, you add a notification to a person when the person has a new assignment. An assignment can be made or changed from two different pages: Create/Edit Issue and Assign Issues.

On the Create/Edit Issue page, you can store the initial values and then check them against any changes to see if an assignment has been made or changed. The Assign Issues is a tabular form, so there is no way to check the old values against the new values. For that reason, the best way to implement the notification is with a before insert and update trigger on HT_ISSUES. You can create this trigger programmatically using SQL Workshop.

To create a before insert and update trigger on HT_ISSUES:

  1. On the Workspace home page, click SQL Workshop and then Object Browser.

  2. Click Create.

  3. For Select the type of database object you want to create, click Trigger.

  4. For Table Name, select HT_ISSUES and click Next.

  5. For Define:

    1. For Trigger Name, enter BIU_HT_ISSUES_NOTIFY_ASSIGNEE.

    2. For Firing Point, select AFTER.

    3. For Options, select insert, update.

    4. For Trigger Body, enter the following:

      IF (INSERTING AND 
          :new.assigned_to IS NOT NULL) 
         OR
         (UPDATING AND
          (:old.assigned_to IS NULL OR 
           :new.assigned_to != :old.assigned_to) AND
         :new.assigned_to IS NOT NULL)
         THEN
            FOR c1 IN 
               (SELECT person_name, person_email
                  FROM ht_people
                 WHERE person_id = :new.assigned_to)
            LOOP
      
            IF c1.person_email IS NOT NULL
               THEN
                  FOR c2 IN 
                      (SELECT project_name
                         FROM ht_projects
                        WHERE project_id = :new.related_project)
                  LOOP
      
                 -- APEX_MAIL.SEND(
                 --    p_to => c1.person_email,
                 --    p_from => c1.person_email,
                 --    p_body => 
                 --       'You have been assigned a new issue. 
                 --       'The details are below. ' ||chr(10)||
                 --        chr(10)||
                 --       ' Project: '|| c2.project_name ||chr(10)||
                 --       ' Summary: '||:new.issue_summary ||chr(10)||
                 --       ' Status: '||:new.status ||chr(10)||
                 --       'Priority: '||nvl(:new.priority,'-'),
                 --   p_subj => 'New Issue Assignment');
                  END LOOP;
            END IF;
      
        END LOOP;
      END IF;
      
      
    5. To make this work within your environment, uncomment the APEX_MAIL.SEND and replace the p_to and p_from with your own valid email address.

    6. Click Next.

  6. To review the code, expand the SQL arrow.

  7. Click Finish.

Add Notification for Overdue Issues

The second email notification notifies the project lead whenever an issue becomes overdue. An issue becomes overdue when the target resolution date has passed, but the issue is not yet closed. There is no human interaction to determine if an issue is overdue, so you cannot check for it on a page or in a trigger.

The best way to check for overdue issues is to write a package that queries the HT_ISSUES table. If it finds any overdue issues, the package initiates an email to the Project Lead. This procedure checks for issues by project so that the project lead can receive just one email with all overdue issues rather than an email for each issue. The package will be called once a day by a dbms_job.

You can use the Create Object function as follows:

  • Create the package and package body from within the SQL Workshop

  • Use SQL Command Processor to run the create commands

To create the package:

  1. On the Workspace home page, click SQL Workshop and then SQL Commands.

    SQL Commands appears.

  2. Enter the following in the field provided:

    CREATE OR REPLACE package ht_check_overdue_issues
    AS
       PROCEDURE email_overdue;
    END;
    /
    
    
  3. Click Run.

To create the package body:

  1. On the Workspace home page, click SQL Workshop and then SQL Commands.

    SQL Commands appears.

  2. Enter the following in the field provided:

    CREATE OR REPLACE PACKAGE BODY ht_check_overdue_issues
    AS
    
    PROCEDURE email_overdue
    IS
       l_msg_body varchar2(32000) := null;
       l_count number             := 0;
    BEGIN
    
    FOR c1 IN
       (SELECT pr.project_id,
               pr.project_name,
               pe.person_name,
               pe.person_email
          FROM ht_projects pr,
               ht_people pe
         WHERE pr.project_id = pe.assigned_project
           AND pe.person_role = 'Lead')
    LOOP
       FOR c2 IN
          (SELECT i.target_resolution_date,
                  i.issue_summary,
                  p.person_name,
                  i.status,
                  i.priority
             FROM ht_issues i,
                  ht_people p
            WHERE i.assigned_to = p.person_id (+)
              AND i.related_project = c1.project_id
              AND i.target_resolution_date < SYSDATE
              AND i.status != 'Closed'
         ORDER BY i.target_resolution_date, i.issue_summary)
    LOOP
       IF l_count = 0
          THEN
             l_msg_body := 
               'As of today, the following issues '||
               'are overdue:'||chr(10)||
                chr(10)||
               ' Project: '|| c1.project_name ||chr(10)||
                chr(10)||
              '     Target: '||c2.target_resolution_date ||chr(10)||
              '    Summary: '||c2.issue_summary ||chr(10)||
              ' Status:     '||c2.status ||chr(10)||
              ' Priority:   '||c2.priority ||chr(10)||
              'Assigned to: '||c2.person_name;
     ELSE 
            l_msg_body := l_msg_body ||chr(10)||
                chr(10)||
             '     Target: '||c2.target_resolution_date ||chr(10)||
             '    Summary: '||c2.issue_summary ||chr(10)||
             '     Status: '||c2.status ||chr(10)||
             ' Priority:   '||c2.priority ||chr(10)||
             'Assigned to: '||c2.person_name;
    END IF;
    l_count := l_count + 1;
    END LOOP;
    
     IF l_msg_body IS NOT NULL
       THEN
       -- APEX_MAIL.SEND(
       --    p_to => c1.person_email,
       --    p_from => c1.person_email,
       --    p_body => l_msg_body, 
       --   p_subj => 'Overdue Issues for Project '||
                       c1.project_name);
     END IF;
     l_count := 0;
    
    END LOOP;
    
    END email_overdue;
    
    END ht_check_overdue_issues;
    /
    
    

    To make this work within your environment, uncomment the APEX_MAIL.SEND and replace the p_to and p_from with your own valid email address.

  3. Click Run.

    Next, you want to update the demonstration data to include your employees' valid email addresses.

To update demonstration data to include valid email addresses:

  1. On the Workspace home page, click SQL Workshop and then Object Browser.

  2. From the Object list on the left side of the page, select Tables.

  3. Select the HT_PEOPLE table.

  4. Select the Data tab.

  5. For each person, edit the email address:

    1. Click the Edit icon.

    2. Change Person Email to a valid email address.

    3. Click Apply Changes.

  6. Repeat step 5 for all people within the HT_PEOPLE table.

  7. Return to the Workspace home page by clicking the Home breadcrumb link.

    Next, you want to create a DBMS_JOB that executes your newly created package at a time interval you specify.

To create the DBMS_JOB:

The following is an example of a DBMS_JOB that executes your newly created package. To make this a valid DBMS_JOB, however, you need to set the interval appropriately and execute it using SQL Commands within the SQL Workshop.

DECLARE
   jobno number;
BEGIN
   DBMS_JOB.SUBMIT(
      job => jobno,
     what => 'BEGIN
              ht_check_overdue_issues.email_overdue;
              END;',
next_date => SYSDATE,
 interval => desired_interval 
             );
   COMMIT;
END;
/

For this DBMS_JOB, replace desired_interval with the appropriate interval. For example, to have this job execute once each day, you would replace desired_interval with the following:

'TRUNC(SYSDATE)+(25/24)'

See Also:

Send email from Application Express applications How To on OTN at:
http://www.oracle.com/technology/products/database/application_express/howtos/index.html

Add Application Security

The planning and project analysis phase produced two security requirements:

  • Only the CEO and Managers can define and maintain projects and people

  • Once assigned, only the person assigned or a project lead can change data about the issue

Within Oracle Application Express, you can define authorization schemes. Authorization controls user access to specific controls or components based on user privileges. Once defined, you can associate an authorization scheme with any page, region, or item to restrict access. Each authorization schema is run only when needed and is defined to validate either once for each page view or once for each session.

Topics in this section include:

Restrict Project and People Definition

The first requirement states that only the CEO and Managers may define and maintain projects and people. To address this requirement, you:

  • Create an authorization scheme to check the current user's role

  • Associate the authorization scheme with the items on the Projects and People report that navigate to the Create/Edit pages

  • Associate the authorization scheme with the Create/Edit pages themselves so that a user cannot bypass the security by manually editing the URL to the target page

To reference the current user, use the session variable :APP_USER. This session variable is compared with the person's email address (which is the same as their workspace or workspace name). Whenever coding this type of security, you should always code in a user that can pass all security. You may find this user very useful for development and testing. If you do not take this approach, you may not be able to access the restricted pages unless you define yourself as the CEO or Manager.

Create the Authorization Scheme

To create the authorization scheme:

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

  2. Select the Issue Tracker application.

  3. Click Shared Components.

  4. Under Security, click Authorization Schemes.

  5. Click Create.

  6. For Create Authorization Scheme, accept the default, From Scratch, and click Next.

  7. Under Authorization Scheme, enter the following in Name:

    USER_CEO_OR_MANAGER
    
    
  8. Under Authorization Scheme:

    1. Scheme Type - Select Exists SQL Query.

    2. Expression 1 - Enter:

      SELECT '1'
        FROM ht_people
       WHERE (upper(person_email) = UPPER(:APP_USER) AND
              person_role IN ('CEO','Manager'))
          OR (UPPER(:APP_USER) = 'HOWTO')
      
      
    3. Identify error message displayed when scheme violated - Enter:

      You are not authorized to access this function.
      
      
  9. Scroll down to Evaluation Point. For Validate authorization scheme, select Once per session.

    This selection is sufficient in this instance as the assigned role typically does not change within a given session.

  10. Click Create.

Next, you need to associate the authorization scheme with the appropriate objects.

Associate Objects on the Projects Report

To associate the authorization scheme with the Projects report:

  1. Click the Edit Page icon in the upper right corner. The Edit Page icon resembles a small green piece of paper and pencil.

  2. In Page, enter 2 and then click Go.

    The Page Definition for page 2, Projects, appears.

  3. Under Regions, click Report next to Projects.

  4. Click the Edit icon to the left of PROJECT_ID.

  5. Under Authorization, select the Authorization Scheme USER_CEO_OR_MANAGER.

  6. Click Apply Changes.

  7. Click Cancel.

To associate the authorization scheme with the Create button on the Projects report:

  1. Under Buttons on the Page Definition for page 2, click the Create link (not the icon).

    The Edit Page Buttons page appears.

  2. Under Authorization, select the Authorization Scheme USER_CEO_OR_MANAGER.

  3. Click Apply Changes.

Associate Objects with the Create/Edit Report

To associate the authorization scheme with the Create/Edit Project page:

  1. Go to page 3 by clicking the Next Page (>) button.

    The Page Definition for page 3, Create/Edit Project, appears.

  2. Under Page, click the Edit page attributes icon.

    The Page attributes page appears.

  3. Under Security, select the Authorization Scheme USER_CEO_OR_MANAGER.

  4. Click Apply Changes.

Associate Objects with the People Report

To associate the authorization scheme with the People report.

  1. Go to page 4 by clicking the Next Page (>) button.

    The Page Definition for page 4, People, appears.

  2. Under Regions, click Report next to People.

  3. Click the Edit icon to the left of PERSON_ID.

  4. Under Authorization, select the Authorization Scheme USER_CEO_OR_MANAGER.

  5. Click Apply Changes.

  6. Click Cancel.

To associate the authorization scheme with the Create button on the People report:

  1. Go to page 5 by clicking the Next Page (>) button.

    The Page Definition for page 5 appears.

  2. Under Buttons, click the Create link (not the icon).

    The Edit Page Buttons page appears.

  3. Under Authorization, select the Authorization Scheme USER_CEO_OR_MANAGER.

  4. Click Apply Changes.

To associate the authorization scheme with the Create/Edit Person Information page:

  1. Under Page, click the Edit page attributes icon.

    The Page attributes page appears.

  2. Under Security, select the Authorization Scheme USER_CEO_OR_MANAGER.

  3. Click Apply Changes.

You can test this by creating a user with the user name of HOWTO. The HOWTO user should be able to see the edit link. Then, create another user, HOWTO2. This user should not be able to see the link.

See Also:

"Create Users"

Restrict Issue Modification

The second requirement states that once an issue has been assigned, only the person assigned (or a project lead) can change data about the issue. This requirement is a little trickier since it changes for every issue.

Currently, there are two pages that enable users to modify an issue: the Create/Edit Issue page and the Assign Issues page. On the Assign Issues page, the only issues that are displayed are those that are unassigned. As the issues are unassigned, security is not necessary.

There are many places that a user can navigate to edit an issue:

  • Three regions on the home page display issues or have edit links

  • The Issues report has links to edit each issue

  • The Target Resolution Dates report enables users to select an issue to edit.

Although other users are not allowed to change the data, you do want to enable users to view all the detailed data about an issue so that they can view the progress and resolution. Given this requirement, the best approach is to create an authorization scheme to be evaluated once for each page view.

The authorization scheme will be associated with both the Apply Changes and Delete buttons on the Create/Edit Issue page. This way, unauthorized users can view all the details, but if they do change something, they have no way of saving that change.

For added security, you can also associate the authorization scheme with the process that performs the insert, update and delete on HT_ISSUES. This protects your application against someone changing the URL to call the Apply Changes process. To let users know why they are not able to make changes, you can add an HTML region that displays an explanation when the authorization fails. The SQL for this scheme must be specific to the Create/Edit Issues page because it needs to reference P7_ISSUE_ID. It also needs to retrieve data from the database because at the time it is evaluated, the necessary data will not be available in the session state. The only item that will be available will be P7_ISSUE_ID because it will be passed by the link.

Create the Authorization Scheme

To create the authorization scheme:

  1. Go to the Application home page.

  2. Click Shared Components.

  3. Under Security, click Authorization Schemes.

  4. Click Create.

  5. For Creation Method, accept the default From Scratch and then click Next.

  6. Under Authorization Scheme, enter the following in Name:

    P7_ASSIGNED_OR_PROJECT_LEAD
    
    
  7. Under Authorization Scheme:

    1. For Scheme Type, select PL/SQL Function Returning Boolean.

    2. For Expression 1, enter:

      DECLARE
         l_related_project   integer;
         l_assigned_to       integer;
         l_person_id         integer;
         l_person_role       varchar2(7);
         l_assigned_project  integer;
      BEGIN
      
      -- User is HOWTO or new Issue
      IF :APP_USER = 'HOWTO' or
         :P7_ISSUE_ID IS NULL
         THEN RETURN TRUE;
      END IF;
      
      FOR c1 IN (SELECT related_project,
                        assigned_to
                   FROM ht_issues
                  WHERE issue_id = :P7_ISSUE_ID)
      LOOP
         l_related_project := c1.related_project;
         l_assigned_to     := c1.assigned_to;
      END LOOP;
      
      -- Issue not yet assigned
      IF l_assigned_to IS NULL
      THEN RETURN TRUE;
      END IF;
      
      FOR c2 IN (SELECT person_id,
                 person_role,
                 assigned_project
            FROM ht_people
           WHERE upper(person_email) = upper(:APP_USER))
      LOOP
         l_person_id        := c2.person_id;
         l_person_role      := c2.person_role;
         l_assigned_project := c2.assigned_project;
      END LOOP;
      
      -- User is lead of related project
      IF l_person_role = 'Lead' and 
         l_assigned_project = l_related_project
         THEN RETURN TRUE;
      
      -- User is assigned to issue
      ELSIF l_assigned_to = l_person_id
         THEN RETURN TRUE;
      ELSE
         RETURN FALSE;
      END IF;
      END;
      
      
    3. For Identify error message displayed when scheme violated, enter:

      This issue is not assigned to you, nor are you the Project Lead. Therefore you are not authorized to modify the data.
      
      
  8. Under Evaluation Point, for Validate authorization scheme, select Once per page view.

    This selection is necessary since each issue may have a different result.

  9. Click Create.

Now you need to associate the authorization scheme with the appropriate objects on the Create/Edit Issue page.

Associate Objects with the Create Edit Issues Report

To associate the authorization scheme with buttons and processes:

  1. Go to the Application home page.

  2. Select page 7 - Create/Edit Issues.

  3. Under Buttons, click Delete.

    1. Under Authorization, select the Authorization Scheme P7_ASSIGNED_OR_PROJECT_LEAD.

    2. Click Apply Changes.

  4. Under Buttons, click Apply Changes.

    1. Under Authorization, select the Authorization Scheme P7_ASSIGNED_OR_PROJECT_LEAD.

    2. Click Apply Changes.

  5. Under Buttons, click Create.

    1. Under Authorization, select the Authorization Scheme P7_ASSIGNED_OR_PROJECT_LEAD.

    2. Click Apply Changes.

  6. Under Buttons, click Create and Create Another.

    1. Under Authorization, select the Authorization Scheme P7_ASSIGNED_OR_PROJECT_LEAD.

    2. Click Apply Changes.

  7. Under Page Processing, Processes, select Process Row of HT_ISSUES.

    1. Under Authorization, select the Authorization Scheme P7_ASSIGNED_OR_PROJECT_LEAD.

    2. Click Apply Changes.

Create an HTML Region

Lastly, create a new region to display an explanation when the authorization fails

To create a new region:

  1. Under Regions, click the Create icon.

  2. On Region, accept the default HTML and click Next.

  3. Select HTML for region container and click Next.

  4. For Display Attributes:

    1. For Title, enter Not Authorized.

    2. For Display Point, select Page Template Body (2. items below region content).

    3. Click Next.

  5. For Source, enter the following in Enter HTML Text Region Source and then click Next:

    You are not authorized to modify the data for this issue because<br>you are not the Project Lead nor is the issue assigned to you.
    
    
  6. For Authorization Scheme, select {Not P7_ASSIGNED_OR_PROJECT_LEAD}. This selection makes the region only display when the Authorization Scheme fails.

  7. Click Create Region.

Figure 14-18 displays the Create/Edit Issue page being run by a person for whom the Authorization fails. Notice a new region displays at the top of the page and that the only button being displayed is Cancel.

Figure 14-18 New Region Displaying Authorization Failure

Description of Figure 14-18 follows
Description of "Figure 14-18 New Region Displaying Authorization Failure"

A more elegant solution to this security requirement would be to create a different page for viewing the details of an issue. You would need to have a procedure that would take in the issue_id and current user and pass back a flag for view only or edit. Then you could dynamically build the link for all the reports to call either the View page or the Edit page based upon a call to that procedure. You would still want to protect against someone accessing the edit page without using a link so you would also check permission before firing the insert, update and delete process.

Deploying Your Application

Now that your application is complete, the next step is to deploy it. Typically, developers create applications on one server and deploy it on another. Although this approach is not required, it enables you to resolve bugs without impacting the production instance.

Note:

To deploy an application on another server, you need to install and configure another Oracle Application Express instance.

Topics in this section include:

Move the Application Definition

The definition for your application lives within the Oracle database. The application definition includes everything that makes up the application, including the templates, but it does not include database object definitions or the underlying data. To move an application to another Oracle Application Express instance, you must export the application definition from your development server and import it into your production server.

Topics in this section include:

Export the Application Definition

To export the application definition from your development server:

  1. On the Workspace home page, click the arrow on the Application Builder icon and select the application you just created.

  2. Click the Export/Import icon.

  3. For Export/Import, click Export and then Next.

  4. For Application, make sure the application created in this exercise is selected.

  5. Click Export Application.

  6. When prompted, click to Save the file.

  7. Specify a location on your local hard drive and then click Save.

Create the Required Objects to Support the Application

On your production instance, you need to create the objects necessary to support the application. Log in to the production instance and follow the directions in "About Building Database Objects".

Note:

Although the supporting objects do not need to exist for you to import the application definition, be aware you cannot test the code until they exist.

Import the Application Definition into the Production Instance

Log in to the production instance of the Workspace home page:

  1. On the Workspace home page, click the arrow on the Application Builder icon and select the application you just created.

  2. On the Application home page, click Export/Import.

  3. On the Export/Import page, click Import and then click Next.

  4. For Import File:

    1. Import file - Click the Browse button and then locate your exported file.

    2. File Type - Select Application, Page, or Component Export.

    3. File Character Set - Accept the default and click Next.

    Once the success message appears, the next step is to install the file.

  5. Click Install.

  6. On Application Install:

    1. Parse As Schema - Select the schema on your production server that contains your application objects.

    2. Build Status, - Select Run and Build Application.

      This option enables other users to run the application and enables you to log in and change the code if necessary. Alternatively, you can select Run Application Only. Be aware that if you select this option you will not be able to access the source code for the application.

    3. Install As Application - You can select:

      • Reuse Application ID from Export File - Only select this option if the application ID is not being used on the production instance.

      • Auto Assign New Application ID - Select this option to assign a new application ID.

      • Change Application ID - Select this option to change the existing application ID. If you select this option, you will be prompted to enter a new application ID.

        When you install an application having the same ID as an existing application in the current workspace, the existing application is deleted and then the new application is installed. If you attempt to install an application having the same ID as an existing application in a different workspace, an error message appears.

        If all statements are successful the install commits and becomes permanent. If any errors are encountered, the install is rolled back, resulting in no permanent changes.

    4. Click Install Application.

    If the install is successful, the Post-App Install Utility Options page appears. From here, you can select one of the following:

    • Select Run Application to see the application running

    • Select Application Attributes to view the application definition within Application Builder

Load the Data

The next step in deploying your application is to load the data. At a minimum, you would need to populate the project and people tables.

Note there are various mechanisms you could use to accomplish this task, including:

  • Use the application itself to create data.

  • Use the Data Loader to load data copied from a spreadsheet.

  • Use SQL Scripts and run scripts to create data.

  • If you have data existing already within an Oracle database, use either export/import to move data between machines or use SQL to retrieve and transform existing data and load it into the application tables.

See Also:

"Loading Demonstration Data" and "Importing, Exporting, Loading, and Unloading Data" in Oracle Database Express Edition 2 Day DBA

Alternate Authentication Mechanisms to Consider

When the application login page calls the login API with a user name and password, the Application Express engine calls the credentials verification method specified in the application's current authentication scheme. You have three choices as to how credentials are verified from within the login API:

  • Implement the method yourself as a PL/SQL function returning Boolean and put it in your application's schema.

  • Use the built-in LDAP authentication method, which checks user name and password against the LDAP directory that you specify.

  • Use the built-in Oracle Application Express authentication method, which checks the user name and password against the Oracle Application Express workspace repository.

Your application is currently using the built-in Oracle Application Express authentication method.

See Also:

"Establishing User Identity Through Authentication" in Oracle Database Application Express User's Guide

Create Users

In order for your application to be accessible, you need to create users. If you are still using Oracle Application Express authentication, the simplest way to create users it to access the Manage Users page.

To create a new user:

  1. Go to the Workspace home page.

  2. From the Administration list on the right side of the page, click Manage Application Express Users.

  3. From the Tasks list on the right side of the page, click Create End User.

  4. Under User Identification, enter the required information.

  5. Click Create User or Create and Create Another.

Publish the URL

Now that you have deployed your application, loaded data, and created users, you can publish your production URL.

You can determine the URL to your application by positioning the mouse over the Run icon on the Application home page. The URL appears in the status bar at the bottom of the page.

The Run icon gets its value from the Home link attribute on the Edit Security Attributes page. This link is only referenced by this icon and by applications that do not use the Application Express Login API. Consider the following example:

http://apex.oracle.com/pls/otn/f?p=11563:1:3397731373043366363

Where:

  • apex.oracle.com is the URL of the server

  • pls is the indicator to use the mod_plsql cartridge

  • otn is the data access descriptor (DAD) name

  • f?p= is a prefix used by Oracle Application Express

  • 11563 is the application being called

  • 1 is the page within the application to be displayed

  • 3397731373043366363 is the session number

To run this example application, you would use the URL:

http://apex.oracle.com/pls/otn/f?p=11563:1

When users log in, they receive a unique session number.

As you may recall, you created the Issue Tracker application using the Create Application wizard. This wizard creates a process on the Login page (page 101) that controls authentication. The contents of the process are:

WWV_FLOW_CUSTOM_AUTH_STD.LOGIN(
    P_UNAME => :P101_USERNAME,
    P_PASSWORD => :P101_PASSWORD,
    P_SESSION_ID => :FLOW_SESSION,
    P_FLOW_PAGE => :APP_ID||':1'
    );

Note that the Page is hard coded into this process. Because of this, the page you pass in to the URL is overwritten and does not need to be included. You can access the application by using the following URL:

http://apex.oracle.com/pls/otn/f?p=11563:1

As you can see from the example used, the URL has no meaning and can be rather long. The host name can be changed to make it more symbolic. You can also configure Apache to rewrite your URL so that you can publish an abbreviated format and a URL that would be more intuitive to your users. See your Apache documentation for details.