Skip Headers
Oracle® Application Express Advanced Tutorials
Release 3.2

Part Number E13363-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

15 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. Before following the steps outlined in this chapter you must have completed planning the project, creating the underlying database objects, and loading demonstration data, as described in Chapter 14, "How to Design an Issue Tracking Application".

A completed sample Issue Tracker application and supporting scripts are available on the Oracle Technology Network. Go to the following location and navigate to Packaged Applications and then select Issue Tracker:

http://www.oracle.com/technology/products/database/application_express/index.html

Note:

You must complete Chapter 14, "How to Design an Issue Tracking Application" before building the Issue Tracker application. This tutorial takes approximately two to three 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:

Issue Tracker Application Overview

This section provides an overview of the Issue Tracker application designed and implemented in this tutorial. Before building this application it is helpful to understand:

Issue Tracker User Interface

The Issue Tracker user interface is designed to support tracking and maintaining of issues, projects, and users. The application pages are organized as shown in Figure 15-1, "Issue Tracker User Interface".

Figure 15-1 Issue Tracker User Interface

Description of Figure 15-1 follows
Description of "Figure 15-1 Issue Tracker User Interface"

Issue Tracker Architecture

After building this application, you will have 15 pages. The Application Builder home page for the Issue Tracker application will look similar to the one in Figure 15-2, "Issue Tracker Pages".

Figure 15-2 Issue Tracker Pages

Description of Figure 15-2 follows
Description of "Figure 15-2 Issue Tracker Pages"

Each page serves a particular tracking function as described in Table 15-1, "Overview of Each Issue Tracker Page".

Table 15-1 Overview of Each Issue Tracker Page

Name and Number Parent Breadcrumb Entry Purpose Section that describes how to create this page:

1 - Home

 

Application Home page that links to top level pages: Projects, Users, Issues, Reports and Dashboard.

Create the Basic Application

2 - Projects

Home

Project report page used to display and search projects. Links to Project Details page.

Add Pages to Maintain Projects

3 - Project Details

Projects

Project details form used to view details for a specific project and to add, delete, and edit a project.

Add Pages to Maintain Projects

4 - Users

Home

Users report page used to display and search users. Links to User Information page.

Add Pages to Track Users

5 - User Information

Users

User information form used to view information for a specific user and to add, edit and delete a user.

Add Pages to Track Users

6 - Issues

Home

Issues report page used to display and search Issues. Links to Issue Details page.

Add Pages to Track Issues

7 - Issue Details

Issues

Issue Details form used to view information for a specific issue and to add, edit and delete an issue.

Add Pages to Track Issues

8 - Assign Open Issues

Reports

Displays all unassigned issues and allows you to assign to a person, identify a related project, change status and change priority.

Add Pages for Summary Reports

9 - Issue Summary by Project

Reports

Provides a report of a variety of issue parameters per project.

Add Pages for Summary Reports

10 - Resolved by Month Identified

Reports

Shows a visual depiction number of issues solved each month.

Add Pages for Summary Reports

11 - Target Resolution Dates

Reports

Displays a calendar with entries for each target resolution date.

Add Pages for Summary Reports

12 - Average Days to Resolve

Reports

The average number of days it took each person to resolve their issues is shown as a bar chart.

Add Pages for Summary Reports

14 - Reports

Home

This is a landing page for all the summary reports: Assign Open Issues, Issue Summary by Project, Resolved by Month Identified, Target Resolution Dates, Average Days to Resolve.

Add Pages for Summary Reports

18 - Dashboard

Home

A snapshot of Overdue Issue, Unassigned Issues, Recently Opened Issues, and Open Issues by Project is displayed.

Add a Dashboard Page

101 - Login

 

Application login page.

Create the Basic Application


Building a Basic User Interface

After you create the objects that support your application and load the demonstration data, as described in Chapter 14, "How to Design an Issue Tracking Application", 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 create the pages that support the data management and data presentation functions described in "Planning and Project Analysis".

Topics in this section include:

Create the Basic Application

The Create Application Wizard is used 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.

This section includes the following topics:

Create the Application

To create the application:

  1. Log in to Oracle Application Express.

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

  3. Click Create.

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

  5. For Name:

    1. Name - Enter Issue Tracker.

    2. Create Application - Select From scratch.

    3. Schema - Select schema containing loaded IT data objects.

    4. Click Next.

  6. Under Add Page:

    1. Select Page Type - select Blank.

    2. Page Name - enter Home.

    3. Click Add Page.

    4. Click Next.

  7. For Tabs, select One Level of Tabs and click Next.

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

  9. For Attributes:

    1. Date Format - Enter DD-MON-YYYY, or select 12-JAN-2004 from the select list

    2. Click Next.

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

  11. Click Create.

Add a Logo to the Application

To add an Issue Tracker logo to the application:

  1. Click Shared Components.

  2. Under Application, click the Definition link.

  3. On the Definition page, click the Logo tab at the top.

  4. For Logo Type, select Text (requires Application Express 2.2 or greater).

  5. For Logo, enter Issue Tracker 1.0.

  6. For Logo Attributes, select White Text from the drop down list.

  7. Click Apply Changes.

Run the Application

To view the application:

  1. Click the Application home breadcrumb link.

  2. Click the Run Application icon.

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

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

    Figure 15-3 Initial Home Page

    Description of Figure 15-3 follows
    Description of "Figure 15-3 Initial Home Page"

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

    • Logo - The Issue Tracker 1.0 logo is displayed in top left corner.

    • Navigation Links - A navigation bar entry displays in the upper left 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.

  4. 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 add content to the Home page.

Add Navigation Image List to Home Page

Now you need to add a horizontal list of images to help the user navigate to the top level pages of the application.

When you complete this section the Home page will look like Figure 15-4.

Figure 15-4 Home Page with Navigation Image List

Description of Figure 15-4 follows
Description of "Figure 15-4 Home Page with Navigation Image List"

Note:

The navigation links below each image will not work until you create each of the corresponding top level pages. For example, when you complete the Add Pages to Maintain Projects section, the Projects link will display the Projects page.

This section covers the following topics:

Create a List of Images

To create a horizontal list of images to navigate by:

  1. Click the Application home breadcrumb.

  2. Click Shared Components.

  3. Under Navigation, click Lists.

  4. Click Create.

  5. For List make these changes:

    1. Name - Enter Main Menu

    2. List Template - Select Horizontal Images with Label List

  6. Click Create.

To add Dashboard, Project, Issues, Reports, and Users images to the list:

  1. Click Create List Entry >.

  2. To add a Dashboard image, under Entry, make these changes:

    1. Sequence - Enter 5.

    2. Image - Enter menu/dashboard_bx_128x128.png

    3. List Entry Label - Enter Dashboard.

  3. Under Target, for Page, enter 18.

  4. Click Create and Create Another.

  5. To add a Projects image, under Entry, make these changes:

    1. Sequence - Enter 10.

    2. Image - Enter menu/globe_bx_128x128.png

    3. List Entry Label - Enter Projects.

  6. Under Target, for Page, enter 2.

  7. Click Create and Create Another.

  8. To add an Issues image, under Entry, make these changes:

    1. Sequence - Enter 20.

    2. Image - Enter menu/shapes_bx_128x128.png

    3. List Entry Label - Enter Issues.

  9. Under Target, for Page, enter 6.

  10. Click Create and Create Another.

  11. To add a Reports image, under Entry, make these changes:

    1. Sequence - Enter 30.

    2. Image - Enter menu/folder_bx_128x128.png

    3. List Entry Label - Enter Reports.

  12. Under Target, for Page, enter 14.

  13. Click Create and Create Another.

  14. To add a Users image, under Entry, make these changes:

    1. Sequence - Enter 100.

    2. Image - Enter menu/addresses_bx_128x128.png

    3. List Entry Label - Enter Users.

  15. Under Target, for Page, enter 4.

  16. Click Create.

Create a Region to Contain the List of Images

Now create a region on the Home page to display the list of images. You need to remove the default Home region and create another of type list to contain the Main Menu list of images.

To create a region containing the list of images:

  1. Click the Application home breadcrumb.

  2. Click the icon, 1 - Home, for the Home page

  3. Under Regions, select the Home region.

  4. Click Delete.

  5. To confirm, click Delete Region.

  6. Under Regions, select the Create icon.

  7. For region type, select List and click Next.

  8. For Display Attributes, make these changes:

    1. Title - Enter Home.

    2. Region Template - Select No Template.

    3. Sequence - Enter 3.

    4. Click Next.

  9. For Source, select Main Menu for List.

  10. Click Create List Region.

  11. Under Regions, click Home region.

  12. Scroll down to Header and Footer. Enter the following for Region Header:

    <p>Use this application to track issues as they arise for projects within your organization.<p>

  13. Click Apply Changes.

  14. To see the image list on the Home page, click the Run Application icon on the Application's home page. The Home page should look like Figure 15-5, "Home Page with Navigation Image List".

    Figure 15-5 Home Page with Navigation Image List

    Description of Figure 15-5 follows
    Description of "Figure 15-5 Home Page with Navigation Image List"

Add Pages to Maintain Projects

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

Topics in this section include:

Note:

If you are already familiar with this application, skip "Overview of Project Pages" and proceed to "Create a Tab Set for this Application".

Overview of Project Pages

The created report page is the Projects page and the form page is the Project Details page. When you complete this section, your project pages will be similar to the Project page shown in Figure 15-6, "Projects Page", and the Project Details page shown in Figure 15-7, "Project Details Page When Adding a Project" and Figure 15-8, "Project Details Page When Editing a Project".

Figure 15-6 Projects Page

Description of Figure 15-6 follows
Description of "Figure 15-6 Projects Page"

Figure 15-7 Project Details Page When Adding a Project

Description of Figure 15-7 follows
Description of "Figure 15-7 Project Details Page When Adding a Project"

Figure 15-8 Project Details Page When Editing a Project

Description of Figure 15-8 follows
Description of "Figure 15-8 Project Details Page When Editing a Project"

Projects Page (2 - Projects)

This page is a report of all projects in the database. Components included on this page are described as follows:

  • Project Name: The name of the project.

  • Start Date: The date the project was started.

  • Target End Date: The date the project is scheduled to be completed.

  • Actual End Date: The date the project was actually completed.

  • Search region: A filtered report can be obtained by selecting search criteria and clicking the Go button.

  • Edit icon: To edit project information, click on the edit icon to the left of the project name. The Project Details page is displayed showing the selected project's information.

  • Add Project button: A new project can be added to the database by clicking this button. An empty Project Details page is displayed for you to enter specific project information.

Project Details Page (3 - Project Details)

This is a form that allows you to edit project details, and add a new project to the database. When you click the Add Project button or an edit icon on the Projects page this detail page is shown. All fields are empty and the Audit Information is not included when adding a new project. Components included on this page are described as follows:

  • Cancel: Returns you to the Project page without submitting any unapplied changes.

  • Delete: Removes the project from the database after getting an OK response from a delete confirmation message. This button is displayed when a project is being edited and is not displayed when adding a project.

  • Apply Changes: Commits any project changes to the database. This button is displayed when a project is being edited and is not displayed when adding a project.

  • Create: Adds the project to the database. This button is displayed when a project is being added and is not displayed when editing a project.

  • Project Details: This region allows you to enter project details.

  • Audit Information: When this region is expanded, the project audit information is displayed. Audit information cannot be edited. Audit information is automatically updated for this project when the project is created or modified. This region is only included when a project is being edited.

Create a Tab Set for this Application

A default tab set, TS1, was created when the application was created. Next you will change the name of this Tab set to be used when creating each new page.

To rename a Tab Set:

  1. Click the Application home breadcrumb.

  2. Click Shared Components.

  3. Under Navigation, select Tabs.

  4. Click Rename Standard Tab Set link on right panel.

  5. Click TS1.

  6. For New Tab Set Name, enter Issue Tracker and click Next.

  7. To confirm, click Rename Tab Set.

The Issue Tracker tab set is now ready to have tabs added to it as each new page is added by the Create Page Wizard.

Create Pages for Maintaining Projects

To create pages for maintaining the IT_PROJECTS table:

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

  2. Select Form and click Next.

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

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

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

  6. For Define Report Page:

    1. Implementation - Select Interactive.

    2. Page Number - Enter 2

    3. Page Name - Enter Projects.

    4. Region Title - Enter Projects

    5. Region Template - Select No Template.

    6. Breadcrumb - Select Breadcrumb.

      Create Breadcrumb Entry section appears.

    7. Entry Name - Enter Projects.

    8. Select Parent Entry - Select Home link.

    9. Accept the remaining defaults and click Next.

  7. For Tab Options, select Use an existing tab set and create a new tab within the existing tab set.

    The New Tab Label field appears.

  8. For Tab Set, select Issue Tracker(Home).

  9. For New Tab Label, enter Projects and click Next.

  10. For Select Column(s), select PROJECT_ID, PROJECT_NAME, START_DATE, TARGET_END_DATE, ACTUAL_END_DATE, and 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.

  11. For Edit Link Image, select the first option, and click Next.

  12. For Define Form Page under Create Form Page:

    1. Page - Enter 3.

    2. Page Name - Enter Project Details.

    3. Region Title - Enter Project Details.

    4. Region Template - Select Form Region.

  13. For Define Form Page under Create Breadcrumb Entry:

    1. Entry Name - Enter Project Details.

    2. Click Next.

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

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

  16. For Select Column(s), select PROJECT_NAME, START_DATE, TARGET_END_DATE, ACTUAL_END_DATE, and click Next.

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

  18. Review your selections and click Finish.

Refine the Appearance of the Projects Report Page

The fields on the Projects page need to be edited and the name of the Create button needs to be changed to Add Project.

Hide PROJECT_ID Column and Add an Error Message

To edit fields:

  1. Click Application on the Developer toolbar.

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

  3. Under Regions, click Interactive Report next to Projects.

  4. For PROJECT_ID, select Hidden for Display Text As.

  5. Scroll down to Pagination, for When no Data Found Message, enter No projects found.

  6. At the top of the page, click Apply Changes.

Change Create Button to Add Project > Button

To modify button:

  1. On edit page for Page 2, under Buttons select the Create button.

  2. Change Text Label/Alt from Create to Add Project >, select Apply Changes.

  3. Click the Run Page icon.

As shown in Figure 15-9, the newly created report displays the demo data.

Figure 15-9 Refined Projects Page

Description of Figure 15-9 follows
Description of "Figure 15-9 Refined Projects Page"

Refine the Project Details Page

Next, you need to customize the Project Details page to make the Project Name field larger and the date fields smaller. You also need to add an Audit Information region that only displays when a project is being edited and add a validation that checks 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, Project Details:

    1. From the Developer toolbar, click Application.

    2. Click 3 - Project Details.

  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.

Add an Audit Report Region to Display Only When there is Something to Display

To add an Audit Report region at the bottom of the Project Details page:

  1. On edit page for Page 3, under Regions, select Create button.

  2. Select Report, and click Next.

  3. Select SQL Report, and click Next.

  4. For Title, enter Audit Information.

  5. For Region Template, select Hide and Show Region, and click Next.

  6. For Enter SQL Query or PL/SQL function returning SQL Query, enter:

    SELECT  
        CREATED_ON, CREATED_BY, MODIFIED_ON, MODIFIED_BY 
    FROM #OWNER#.IT_PROJECTS
    WHERE PROJECT_ID = :P3_PROJECT_ID
    
  7. Click Create Region.

  8. On edit page for Page 3 under Regions, click on Audit Information.

  9. Scroll down to Conditional Display and select Value of Item in Expression 1 is NOT NULL.

  10. For Expression 1, enter P3_PROJECT_ID.

  11. Click on Report Attributes tab at the top.

  12. Under Layout and Pagination, make these changes:

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

    2. Pagination Scheme - Select - No Pagination Selected -

    3. Enable partial Page Refresh - Select No

  13. Accept all other defaults and click Apply Changes.

Create a Validation

The Form on a Table with Report Wizard created not null validations for Name, Start Date, and End Date. You must manually create another validation to ensure that the Actual End Date is the same or later then the Start Date.

To add a validation for the Actual End Date:

  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 Project Details: 50.P3_ACTUAL_END_DATE (Actual End Date) 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. Sequence - Enter 50.

    2. Validation Name - Enter P3_END_AFTER_START.

    3. Accept the remaining defaults and click Next.

  6. For Validation:

    1. Validation - Enter:

      to_date(:P3_ACTUAL_END_DATE,:APP_DATE_FORMAT) >= to_date
          (:P3_START_DATE,:APP_DATE_FORMAT)
      
    2. Error Message - Enter:

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

  7. For Conditions:

    1. Condition Type - Select Value of Item in Expression 1 is NOT NULL

    2. Expression 1 - Enter P3_ACTUAL_END_DATE

  8. Click Create.

Run Project Page and Project Details Page

To view the new Project page and Project Details page, click the Run Page icon in the upper right of the page.

  1. Click the Application home breadcrumb.

  2. Click the Run Application icon. The Issue Tracker home page appears along with a list of image links, as shown in Figure 15-10.

    Figure 15-10 Issue Tracker Home Page

    Description of Figure 15-10 follows
    Description of "Figure 15-10 Issue Tracker Home Page"

  3. Click the Projects link under the second image. The Project page should look similar to Figure 15-11.

    Figure 15-11 Projects Page

    Description of Figure 15-11 follows
    Description of "Figure 15-11 Projects Page"

  4. Click the edit icon next to the Email Integration project and click the + sign next to Audit Information to show details. The displayed Project Details page should look like Figure 15-12.

    Figure 15-12 Project Details for Email Integration Project

    Description of Figure 15-12 follows
    Description of "Figure 15-12 Project Details for Email Integration Project"

Note:

In Figure 15-12, "Project Details for Email Integration Project", the Audit region is shown because the Project Name field is not empty. The Audit Information region has been expanded.

Add Pages to Track Users

Once the initial projects pages are complete, you create pages for maintaining users. To accomplish this, you use the Form on a Table with Report Wizard. This wizard creates a report page and a form page for the IT_PEOPLE table.

Topics in this section include:

Note:

If you are already familiar with this application, skip Overview of User Pages and proceed to "Create Pages for Maintaining Users".

Overview of User Pages

After completing this section, you will have a Users page and a Users Information page as shown in Figure 15-13, "Users Page" and Figure 15-14, "User Information Page for George Hurst".

You will be able to navigate to the User page by clicking the User link on the Home page. From the User page, you display the User Information page by clicking the Edit icon or the Add User button on the User page.

Figure 15-14 User Information Page for George Hurst

Description of Figure 15-14 follows
Description of "Figure 15-14 User Information Page for George Hurst"

Users Page (4 - Users)

This page is a report of all users with access to the application. Components included on this page are described as follows:

  • Person Name: The name of the user.

  • Person Email: The users email address.

  • Person Role: The role of this person. In later sections, each role is given a different level of access to the application.

  • Username: The person's username to access this application.

  • Assigned Project: The project assigned to this person.

  • Search region: A filtered report can be obtained by selecting search criteria and clicking the Go button.

  • Edit icon: To edit user information, click on the edit icon to the left of the person's name. The user Information page is displayed showing the selected user's information.

  • Add User button: A new user can be provisioned for this application by clicking this button. An empty User Information page is displayed for you to enter specific user details.

User Information Page (5 - User Information)

This is a form that allows you to edit existing user information, and add a new user. When you click the Add user button or an edit icon on the Users page this information page is shown. Components included on this page are described as follows:

  • Cancel: Returns you to the Users page without submitting any unapplied changes.

  • Delete: Removes the user from the database after getting an OK response from a delete confirmation message. This button is displayed when a user is being edited and is not displayed when adding a user.

  • Apply Changes: Commits any user changes to the database. This button is displayed when a user is being edited and is not displayed when adding a user.

  • Create: Adds the user to the database. This button is displayed when a user is being added and is not displayed when editing a user.

  • User Information: This region allows you to enter user details.

  • Audit Report: When this region is expanded, the user audit information is displayed. Audit information cannot be edited. It is automatically updated for this user when the user is added or user information is edited.

Create Pages for Maintaining Users

To create pages for maintaining the IT_PEOPLE table:

  1. Click Application breadcrumb.

  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 IT_PEOPLE and click Next.

  7. For Define Report Page:

    1. Implementation - Select Interactive.

    2. Page Number - Enter 4

    3. Page Name - Enter Users.

    4. Region Title - Enter Users.

    5. Region Template - Select No Template.

    6. Breadcrumb - Select Breadcrumb.

      Create Breadcrumb Entry section appears.

    7. Entry Name - Users.

    8. Parent Entry - Select Home link.

    9. Accept the remaining defaults and click Next.

  8. For Tab Options, select Use an existing tab set and create a new tab within the existing tab set.

  9. For Tab Set, select Issue Tracker.

  10. For New Tab Label, enter Users and click Next.

  11. For Select Column(s), select PERSON_ID, PERSON_NAME, PERSON_EMAIL, PERSON_ROLE, USERNAME, ASSIGNED_PROJECT, and click Next.

  12. For Edit Link Image, select the first option, and click Next.

  13. For Define Form Page under Create Form Page:

    1. Page - Enter 5.

    2. Page Name and Region Title - Enter User Information.

    3. Entry Name - Enter User Information.

  14. For Define Form Page under Create Breadcrumb Entry:

    1. Entry Name - Enter User Information.

    2. Click Next.

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

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

  17. For Select Column(s), select PERSON_NAME, PERSON_EMAIL, PERSON_ROLE, USERNAME, ASSIGNED_PROJECT, and click Next.

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

  19. Review your selections and click Finish.

Run the Page

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

To preview the page for adding or editing users, click the Edit icon in the far left column. The User Information page is displayed as shown in Figure 15-16, "User Information Page".

Figure 15-16 User Information Page

Description of Figure 15-16 follows
Description of "Figure 15-16 User Information Page"

Modify Appearance of the Users Page

Next, you alter the Users Information by following these steps.

  1. Go to the Page Definition for page 4, Users:

    1. Click Application on the Developer toolbar.

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

  2. Under Regions, click Interactive Report next to Users.

  3. For PERSON_ID, select Hidden for Display Text As.

  4. At the top of the page, click Apply Changes.

Modify Name of Create Button

To change Create button to Add User> button:

  1. On edit page for Page 4, under Buttons, select Create button.

  2. Change Text Label/Alt from Create to Add User >, select Apply Changes.

  3. Click the Run Page icon.

    To reorder columns on the Users page:

  4. On the running Users page, click the Action icon as shown in Figure 15-17.

  5. Select Select Columns.

  6. Use the up and down arrows to the side of the Display in Report box to order columns how ever you wish.

  7. Click Apply. The Users page appears with columns reordered according to your changes.

Change the Query to Include Project Name from IT_PROJECTS

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

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

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

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

  2. Under Regions, click Users.

  3. Scroll down to Source.

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

    SELECT "IT_PEOPLE"."PERSON_ID" as "PERSON_ID", 
        "IT_PEOPLE"."PERSON_NAME" as "PERSON_NAME",
        "IT_PEOPLE"."PERSON_EMAIL" as "PERSON_EMAIL",
        "IT_PEOPLE"."PERSON_ROLE" as "PERSON_ROLE",
        "IT_PEOPLE"."USERNAME" as "USERNAME",
        "IT_PROJECTS"."PROJECT_NAME" as "ASSIGNED_PROJECT"
    FROM "#OWNER#"."IT_PEOPLE" "IT_PEOPLE",
        "#OWNER#"."IT_PROJECTS" "IT_PROJECTS"
    WHERE "IT_PEOPLE"."ASSIGNED_PROJECT"="IT_PROJECTS"."PROJECT_ID" (+)
    

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

  5. Click Apply Changes.

  6. For Confirm Interactive Report Region Change, click Apply Changes.

Run the Page

To view your changes, click the Run Page icon in the upper right of the page. As shown in Figure 15-18, you may need to run the application as a developer to show and reorder some of the columns to look like the figure.

Figure 15-18 Revised Users Page

Description of Figure 15-18 follows
Description of "Figure 15-18 Revised Users Page"

Refine the Appearance of the User Information Page

Next, you customize the User Information 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, User Information:

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

    2. On the Application home page, click 5 - User Information.

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

  3. For Source, accept the default, From Scratch, and 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 r
    FROM it_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 click the Create icon.

  2. For Source, accept the default, From Scratch, and 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 15-2:

    Table 15-2 Display Value and Return Value pairs

    Display Value Return Value

    CEO

    CEO

    Manager

    Manager

    Lead

    Lead

    Member

    Member


  5. Click Create List of Values.

Edit Display Attributes

To edit display attributes for P5_PERSON_ROLE:

  1. Click Edit Page icon for page 5.

  2. Under Items, click P5_PERSON_ROLE.

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

  4. Scroll down to Label.

  5. Change Label to Role.

  6. 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.

  7. Scroll down to List of Values.

  8. From the Named LOV list, select ROLES.

  9. 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.

    2. For Display Null, select Yes.

    3. 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. For P5_USERNAME:

    1. Sequence - Enter 35.

    2. Width - Enter 60.

  5. For P5_PERSON_ROLE:

    1. Width - Enter 7.

  6. For P5_ASSIGNED_PROJECT, enter 50 for Sequence.

  7. Click Apply Changes.

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

Add Audit Report

To add an Audit Report region at the bottom of the User Information:

  1. On edit page for Page 5, under Regions, select Create icon.

  2. Select Report and click Next.

  3. Select SQL Report and click Next.

  4. For Create Region, make these changes:

    • Title - Enter Audit Report.

    • Region Template - Select Hide and Show Region.

    • Click Next.

  5. For Enter SQL Query or PL/SQL function returning SQL Query, enter:

    SELECT  CREATED_ON, CREATED_BY, 
        MODIFIED_ON, MODIFIED_BY 
    FROM  IT_PEOPLE 
    WHERE PERSON_ID = :P5_PERSON_ID
    
  6. Click Create Region.

  7. Under Regions, click Report next to Audit Report.

  8. Under Layout and Pagination, make these changes:

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

    • Pagination Scheme - Select - No Pagination Selected -

    • Enable Partial Page Refresh - Select No.

    • Number of Rows - Enter 15.

    • Maximum Row Count - Enter 500.

  9. Click Apply Changes.

Create a Validation

The Form on a Table with Report Wizard created not null validations for Name, Email, Role and Username. 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 User 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. Sequence - Enter 60.

    2. Validation Name - Enter PROJECT_MAND_FOR_LEADER_AND_MEMBER.

    3. Accept the remaining defaults and click Next.

  6. For Validation:

    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 Users Page and User Information Page

To view the new Users page and User Information page, click the Run Page icon in the upper right of the page.

  1. Click on the application home breadcrumb. You should see the pages created so far in this tutorial, including the Users and User Information pages.

  2. Click on the Run Application icon. The Home page contains a list of image links as shown in Figure 15-19.

  3. Click the Users link to the far right. The Users page will be displayed and will look similar to Figure 15-20, "Users Page".

  4. Click the Edit icon to the left of Carla Downing. You will see the User page displayed similar to the one in Figure 15-21.

    Figure 15-21 User Information for Carla Downing

    Description of Figure 15-21 follows
    Description of "Figure 15-21 User Information for Carla Downing"

    Now test the validation process that ensures users with a Lead or Member role have an assigned project.

  5. For Assigned Project, select - None -, and click Apply Changes. The User Information page is displayed with an error message.

    Next, add a new user to the Issue Tracker Application.

  6. Click the Users breadcrumb to go back to Users page.

  7. Click the Add User> button. As shown in Figure 15-22, "Add User Page", an empty User Information form is displayed.

    Figure 15-22 Add User Page

    Description of Figure 15-22 follows
    Description of "Figure 15-22 Add User Page"

  8. Enter the new user's Name, Email Address, Username and select a Role. Do not assign a project unless the role is Lead or Member.

    Note:

    The validation process will make sure a CEO or Manager does not have a project assigned, and will ensure that a Lead or Member does have a project assigned. To test the validation process, violate one of these conditions and click Create. An error message will appear.
  9. Click Create. The Users page is displayed with the newly added user listed.

Add Pages to Track Issues

Now you need to create pages to retrieve issue information from IT_ISSUES.

Topics in this section include:

Overview of Issues Pages

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.

Upon completion of this section, the application will have an Issues page, and Issues Details page as shown in Figure 15-23, "Issues Page" and Figure 15-24, "Issue Details".

Figure 15-24 Issue Details

Description of Figure 15-24 follows
Description of "Figure 15-24 Issue Details"

Issues Page (6 - Issues)

This page reports issues that have been entered into the system along with general issue information. Components included on this page are described as follows:

  • Issue Summary: Short description of the issue.

  • Identified Date: The date the issue occurred.

  • Status: The issue status which can be open, closed or on-hold.

  • Priority: The issue priority which can be high, medium or low.

  • Target Resolution Date: The date this issue should be closed.

  • Progress: A brief description of progress made for this issue.

  • Actual Resolution Date: The date the issue was closed.

  • Identified By: The person who encountered the issue.

  • Project Name: The project this issue falls under. The project is a list of values already specified in the system.

  • Assigned To: The person assigned to resolve this issue. The person must be a user already entered into the system.

  • Search region: A filtered report can be obtained by selecting search criteria and clicking the Go button.

  • Edit icon: To edit user information, click on the edit icon to the left of the person's name. The user Information page is displayed showing the selected user's information.

  • Add Issue button: A new issue can be added by clicking this button. An empty Issue Details page appears for you to enter specific issue details.

Issue Details Page (7 - Issue Details)

This form allows you to edit existing issue information, and add a new issue. When you click the Add Issue button or an edit icon on the Issues page this details page is shown. Components included on this page are described as follows:

  • Cancel: Returns you to the Issues page without submitting any unapplied changes.

  • Delete: Removes the issues from the database after getting an OK response from a delete confirmation message. This button is displayed when an issue is being edited and is not displayed when adding an issue.

  • Apply Changes: Commits any issue changes to the database. This button is displayed when an issue is being edited and is not displayed when adding an issue.

  • Create: Adds the issue to the database. This button is not displayed when an issue is being edited and is displayed when adding an issue.

  • Issue Details: This region allows you to enter specific issue information.

  • Audit Report: When this region is expanded, the issue audit information is displayed. Audit information cannot be edited. It is automatically updated for this issue when the issue is added or issue details are edited.

Create a Report for Issues

To create a report for maintaining IT_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 IT_ISSUES and click Next.

  7. On Define Report Page:

    1. Implementation - Interactive.

    2. Page Number - Enter 6.

    3. Page Name - Enter Issues.

    4. Region Title - Enter Issue.

    5. Region Template - Select No Template.

    6. Breadcrumb - Select Breadcrumb.

      Create Breadcrumb Entry section appears.

    7. Entry Name - Issues.

    8. Select Parent Entry - Select Home link.

    9. Accept the remaining defaults and click Next.

  8. For Define Report Page tab settings, make these changes:

    1. Tab Options - Select Use an existing tab set and create a new tab within the existing tab set.

    2. Tab Set - Select Issue Tracker.

    3. New Tab Label - Enter Issues.

    4. Click Next.

  9. For Tab Set, select Issue Tracker and click Next.

  10. For New Tab Label, enter Issues and click Next.

  11. For Select Column(s), press CTRL and select these columns:

    • ISSUE_ID

    • ISSUE_SUMMARY

    • IDENTIFIED_BY_PERSON_ID

    • IDENTIFIED_DATE

    • RELATED_PROJECT_ID

    • ASSIGNED_TO_PERSON_ID

    • STATUS

    • PRIORITY

    • TARGET_RESOLUTION_DATE

    • PROGRESS

    • ACTUAL_RESOLUTION_DATE

  12. Click Next.

  13. For Edit Link Image, select the first option and click Next.

  14. On Define Form Page:

    1. Page Number - Enter 7.

    2. Page Name - Enter Issue Details.

    3. Region Title - Enter Issue Details.

    4. Region Template - Select Form Region.

    5. Under Create Breadcrumb entry for Entry Name - Enter Issue Details.

    6. Click Next.

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

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

  17. For Select Column(s), select all columns except for CREATED_ON, CREATED_BY, MODIFIED_ON, and MODIFIED_BY and click Next.

    The CREATED_ON, CREATED_BY, MODIFIED_ON, and MODIFIED_BY columns are added to page 7, Issue Details, in subsequent steps when the Audit region is created.

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

  19. Review your selections and click Finish.

  20. Click Edit Page.

Refine the Issue Details

The appearance of this page needs to be modified to look like Figure 15-24, "Issue Details". You need to modify how the columns and fields are displayed, reorganize the page into regions of similar information and add a button to create new issues sequentially.

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, Issue Details.

  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, make these changes:

    • Name - Enter STATUS.

    • For Type, select Static.

    • Click Next.

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

    Table 15-3 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, make these changes:

    1. Name - Enter PRIORITIES.

    2. Type - Select Static.

    3. Click Next.

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

    Table 15-4 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, make these changes:

    1. Name - Enter PEOPLE.

    2. Type - Select Dynamic.

    3. Click Next.

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

    SELECT person_name d, person_id r
       FROM it_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_PERSON_ID:

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

  2. For Name, enter P7_IDENTIFIED_BY.

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

  4. Under Label and for Label, enter Identified By.

  5. Under List of Values, make these changes:

    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 -
      
  6. 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. Scroll down to Default:

    1. Default value - Enter:

      to_char(sysdate,:APP_DATE_FORMAT);
      
    2. Default Value Type - Select PL/SQL Expression.

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

    The Edit Page Item page appears.

To edit P7_RELATED_PROJECT_ID:

  1. For Name, enter P7_RELATED_PROJECT.

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

  3. For Label, enter Related Project.

  4. 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 -
      
  5. Click the Next button (>) at the top of the page until you go to P7_ASSIGNED_TO_PERSON_ID.

To edit P7_ASSIGNED_TO_PERSON_ID:

  1. For Name, enter P7_ASSIGNED_TO.

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

  3. For Label, enter Assigned To.

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

    1. Named LOV - Select PEOPLE.

    2. Display Null- Select Yes.

    3. Null display value - Enter:

      - Select -
      
  5. 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 Default value.

  5. Under List of Values:

    1. Named LOV - Select STATUS.

    2. Number of Columns - Enter 3.

      This selection reflects the fact there are three valid values.

  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 Low in Default value.

  5. Under List of Values:

    1. Named LOV - Select PRIORITIES.

    2. Number of Columns - Enter 3.

      This selection reflects the fact there are three valid values.

  6. Click Apply Changes.

Modify Validations to Match Item Name Changes

To edit validations:

  1. On the edit page for page 7, under Validations, select P7_IDENTIFIED_BY_PERSON_ID not null.

  2. For Name, enter P7_IDENTIFIED_BY not null.

  3. For Validation Expression 1, enter P7_IDENTIFIED_BY.

  4. Scroll down to Error Message.

  5. For Error Message, enter:

    Identified By must have some value.
    
  6. Click Next icon (>) at the top of the page until edit page for P7_RELATED_PROJECT_ID not null is displayed.

  7. For Name, enter P7_RELATED_PROJECT not null.

  8. For Validation Expression 1, enter P7_RELATED_PROJECT.

  9. Scroll down to Error Message.

  10. For Error Message, enter:

    Related Project must have some value.
    
  11. Click Apply Changes.

  12. Click Run icon for page 7. The Issue Details page is displayed and should look similar to Figure 15-25.

    Figure 15-25 Issue Details before Grouping Items

    Description of Figure 15-25 follows
    Description of "Figure 15-25 Issue Details before Grouping Items"

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. Click the Edit Page 7 link at the bottom of the page.

  2. Under Regions, click the Create icon.

  3. Select Multiple HTML and click Next.

  4. For the first row:

    1. For Sequence, enter 15.

    2. For Title, enter Progress.

    3. For Template, select Form Region.

  5. For the second row:

    1. For Sequence, enter 20.

    2. For Title, enter Resolution.

    3. For Template, select Form Region.

  6. Click Create Region(s).

  7. Under Regions, click the Create icon.

  8. Select Report and click Next.

  9. Select SQL Report and click Next.

  10. For Title, enter Audit Information:

    1. For Title, enter Audit Information.

    2. For Region Template, select Hide and Show Region.

    3. For Sequence, enter 40.

    4. Click Next.

  11. For Enter SQL Query or PL/SQL function returning a SQL Query, enter:

    select     "IT_ISSUES"."CREATED_ON" as "CREATED_ON",
             "IT_ISSUES"."CREATED_BY" as "CREATED_BY",
             "IT_ISSUES"."MODIFIED_ON" as "MODIFIED_ON",
             "IT_ISSUES"."MODIFIED_BY" as "MODIFIED_BY" 
     from    "IT_ISSUES" "IT_ISSUES"
     where   ISSUE_ID = :P7_ISSUE_ID
    
  12. Click Create Region.

  13. Click Report next Audit Information region.

  14. Under Layout and Pagination, make these changes:

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

    2. Pagination Scheme - Select - No Pagination Selected -

    3. Enable Partial Page Refresh - Select No.

    4. Number of Rows - Enter 15.

    5. Maximum Row Count - Enter 500.

    6. 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 Width, make the following edits:

    • For P7_ISSUE_SUMMARY, enter 80.

    • For P7_ISSUE_DESCRIPTION, enter 80.

    • For P7_IDENTIFIED_DATE, enter 12.

    • For P7_TARGET_RESOLUTION_DATE, enter 12.

    • For P7_PROGRESS, enter 80.

    • For P7_ACTUAL_RESOLUTION_DATE, enter 12.

    • For P7_RESOLUTION_SUMMARY, enter 80.

  5. Under Sequence, make the following edits:

    • For P7_ISSUE_ID, enter 168.

    • For P7_ISSUE_SUMMARY, enter 169.

    • For P7_ISSUE_DESCRIPTION, enter 170.

    • For P7_IDENTIFIED_BY, enter 173.

    • For P7_IDENTIFIED_DATE, enter 174.

    • For P7_RELATED_PROJECT, enter 172.

    • For P7_ASSIGNED_TO, enter 159.

    • For P7_STATUS, enter 160.

    • For P7_PRIORITY, enter 161.

    • For P7_TARGET_RESOLUTION_DATE, enter 162.

    • For P7_PROGRESS, enter 164.

    • For P7_ACTUAL_RESOLTUTION_DATE, enter 165.

    • For P7_RESOLUTION_SUMMARY, enter 167.

  6. Click Apply Changes.

  7. Click Run icon for page 7. The Issue Details page is displayed and should look similar to Figure 15-26.

    Figure 15-26 Issue Details after Grouping Items

    Description of Figure 15-26 follows
    Description of "Figure 15-26 Issue Details after Grouping Items"

Change the Display of Issues Details, Progress and Resolution Regions

To modify how these columns display:

  1. Click the Edit Page 7 link at the bottom of the page.

  2. Under Items, click P7_ISSUE_SUMMARY.

  3. For Display As, select Textarea.

  4. Under Label, for Horizontal/Vertical Alignment, select Above.

  5. Under Element, make these changes:

    1. Maximum Width - Enter 200

    2. Height - Enter 2.

  6. Select Next icon at top of page to display P7_ISSUE_DESCRIPTION edit page item.

  7. Under Label, for Horizontal/Vertical Alignment, select Above.

  8. Under Element, make these changes:

    1. Maximum Width - Enter 2000.

    2. Height - Enter 4.

  9. Click Apply Changes.

  10. Under Items, click P7_PROGRESS.

  11. Under Label, for Horizontal/Vertical Alignment, select Above.

  12. Under Element, enter 2000 for Maximum Width.

  13. Click Apply Changes.

  14. Under items, click P7_RESOLUTION_SUMMARY.

  15. Under Label, for Horizontal/Vertical Alignment, select Above.

  16. Under Element, enter 2000 for Maximum Width.

  17. Click Apply Changes.

  18. Click Run Page 7 icon at top of page. The Issue Details page shows the Issue Summary, Issue Description and Progress labels above the text area as shown in Figure 15-27.

    Figure 15-27 Issue Details with Labels Above Text Areas

    Description of Figure 15-27 follows
    Description of "Figure 15-27 Issue Details with Labels Above Text Areas"

    Now, add a Stop and Start HTML Table item to each region. This will realign all items in the region to be left justified.

  19. Click the Edit Page 7 link from the developer toolbar.

  20. Under Items, click the Create icon

  21. Select Stop and start table and click Next.

  22. To realign Related Project, Identified By and Identified Date make these changes for Display and Position Name:

    1. Item_Name - Enter P7_3_0.

    2. Sequence - Enter 171.

    3. Region - Select Issues Identification (1) 10.

    4. Click Create Item.

  23. Under Items, click the Create icon.

  24. Select Stop and start table and click Next.

  25. To realign Assigned To, Status, Priority, and Target Resolution Date make these changes for Display and Position Name:

    1. Item_Name - Enter P7_4_0.

    2. Sequence - Enter 163.

    3. Region - Select Progress (1) 15.

    4. Click Create Item.

  26. Under Items, click the Create icon.

  27. Select Stop and start table and click Next.

  28. To realign Actual Resolution Date make these changes for Display and Position Name:

    1. Item_Name - Enter P7_1_0.

    2. Sequence - Enter 166.

    3. Region - Select Resolution (1) 20.

    4. Click Create Item.

  29. Click Run Page 7 icon. the Issues Details page now has all the items aligned properly as shown in

    Figure 15-28 Issue Details After Realignment

    Description of Figure 15-28 follows
    Description of "Figure 15-28 Issue Details After Realignment"

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. Click Edit Page 7 link at bottom of page.

  2. Under Regions, click Audit Information.

  3. Scroll down to Conditional Display.

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

  5. In Expression 1, enter the following:

    P7_ISSUE_ID
    
  6. Click Apply Changes

Return the User to the Calling Page

Because this Issue Details 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 Issue Details page. Every time the Issue Details 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 Hidden Item Type, select Hidden and click Next.

  4. For Display Position and Name:

    1. Item Name - Enter:

      P7_PREV_PAGE
      
    2. Sequence - Enter 175.

    3. Region - Select Issue Details

    4. Click Next.

  5. For Default, enter 1.

  6. Click Create Item.

    The Page Definition for page 7 appears.

Next, edit the Cancel button to return to the page number stored in P7_PREV_PAGE.

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. Select reset pagination for this page.

  5. Click Apply Changes.

To edit the branch:

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

  2. Under Action, make these changes:

    1. Select reset pagination for this page

    2. Deselect include process success message

    3. For Clear Cache - Enter 7

    4. For Set these items - Enter 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 button icon between the Edit All and Create icons at the top right of the Buttons section. The Copy icon looks like Figure 15-29.

    Figure 15-29 Copy Button Icon

    Description of Figure 15-29 follows
    Description of "Figure 15-29 Copy Button 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. Accept remaining defaults and click Copy 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, make these changes:

    1. Page - Enter 7.

    2. Select reset pagination for this page

    3. Clear Cache - Enter 7.

    4. Set these items - Enter the following:

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

      &P7_PREV_PAGE.
      
    6. Click Next.

  4. For Branch Conditions, make these changes:

    1. Sequence - Enter 0.

    2. When Button Pressed - Select CREATE_AGAIN (Create and Create Another).

  5. Click Create Branch.

Run the Page

To see the changes, click the Run Page icon. The new form appears as shown in Figure 15-30, "Refined Issue Details".

Figure 15-30 Refined Issue Details

Description of Figure 15-30 follows
Description of "Figure 15-30 Refined Issue Details"

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:

Modify Add Issue Button

To call Issue Details page from the Add Issue button:

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

  2. Under Button, click the Create button.

  3. For Text Label/Alt, enter Add Issue>.

  4. Under Optional URL Redirect, make these changes:

    1. Set These Items - Enter P7_PREV_PAGE

    2. 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 change the SQL query:

  1. Under Regions, select Issues.

  2. Scroll down to Source.

  3. Replace SQL with the following:

    SELECT "IT_ISSUES"."ISSUE_SUMMARY" as "ISSUE_SUMMARY",
        "IT_PEOPLE"."PERSON_NAME" as "IDENTIFIED_BY",
        "IT_ISSUES"."IDENTIFIED_DATE" as "IDENTIFIED_DATE",
        "IT_PROJECTS"."PROJECT_NAME" as "PROJECT_NAME",
        decode("IT_PEOPLE_1"."PERSON_NAME",NULL,'Unassigned',
            "IT_PEOPLE_1"."PERSON_NAME") 
            as "ASSIGNED_TO",
        "IT_ISSUES"."STATUS" as "STATUS",
        "IT_ISSUES"."PRIORITY" as "PRIORITY",
        "IT_ISSUES"."TARGET_RESOLUTION_DATE" as "TARGET_RESOLUTION_DATE",
        "IT_ISSUES"."PROGRESS" as "PROGRESS",
        "IT_ISSUES"."ACTUAL_RESOLUTION_DATE" as "ACTUAL_RESOLUTION_DATE",
        "IT_ISSUES"."ISSUE_ID" as "ISSUE_ID",
        "IT_ISSUES"."RELATED_PROJECT_ID" as "PROJECT_ID"
    FROM "IT_PEOPLE" "IT_PEOPLE_1",
        "IT_PROJECTS" "IT_PROJECTS",
        "IT_PEOPLE" "IT_PEOPLE",
        "IT_ISSUES" "IT_ISSUES"
    WHERE "IT_ISSUES"."IDENTIFIED_BY_PERSON_ID"="IT_PEOPLE"."PERSON_ID"
    AND "IT_ISSUES"."ASSIGNED_TO_PERSON_ID"="IT_PEOPLE_1"."PERSON_ID"(+)
    AND "IT_ISSUES"."RELATED_PROJECT_ID"="IT_PROJECTS"."PROJECT_ID"
    
  4. Click Apply Changes.

  5. Now to confirm, click Apply Changes.

To edit column attributes:

  1. Under Regions, select Interactive Report.

  2. For ISSUE_ID, Display Text As, select Hidden.

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

  4. For Heading Alignment, select left.

  5. Return to the top of the page and click the Next (>) icon. The Report Attributes page for IDENTIFIED_DATE appears.

  6. For Heading Alignment, select left.

  7. Return to the top of the page and click the Next (>) icon until the Report Attributes page for IDENTIFIED_BY appears.

  8. For Heading Alignment, select left.

  9. Return to the top of the page and click the Next (>) icon. The Report Attributes page for PROJECT_NAME appears.

  10. For Heading Alignment, select left.

  11. Return to the top of the page and click the Next (>) icon. The Column Attributes page for ASSIGNED_TO appears.

  12. For Heading Alignment, select left.

  13. Return to the top of the page and click the Next (>) icon. The Column Attributes page for PROJECT_ID appears.

  14. For Display Text As, select Hidden.

  15. Click Apply Changes.

To add a no data found message and to add details for a link column:

  1. Scroll down to Pagination.

  2. For When No Data Found Message, enter:

    No issues found.
    
  3. Scroll down to Link Column, for Item 2 Name, enter P7_PREV_PAGE.

  4. For Item 2 Value, enter 6.

  5. Click Apply Changes.

Reorder Tabs

Move the Issues tab so it is between the Projects and Users tab.

To change the tab order:

  1. Click on the application home breadcrumb.

  2. Click Shared Components.

  3. Under Navigation, select Tabs.

  4. Click Resequence display order link on the right panel under Standard Tab Tasks.

  5. In the Sequence column, make these changes:

    1. For Projects - Enter 15 for Sequence.

    2. For Users - Enter 30 for Sequence.

    3. For Issues - Enter 20 for Sequence.

  6. Click Apply Changes.

Run Issues Page and Issue Details Page

To view the new Issues page and Issue Details page, click the Run Page icon in the upper right of the page.

  1. Click on the application home breadcrumb. You should see the pages created so far in this tutorial, including the newly added Issues and Issue Details pages.

  2. Click on the Run Application icon. The Home page contains a list of image links as shown in Figure 15-31.

  3. Click the Issues link. The Issues page is displayed. The first time you run this page, because you changed the names of some of the columns, you may not see the Identify By, Project Name and Assigned To columns. If this is the case, you can further customize the reports display by running the page and specifying which columns you want to appear.

    To specify which columns to display:

  4. On the running Issues page, click the Action menu drop down to the right of the Go button as shown in Figure 15-32

  5. Select Select Columns. Your page should look similar to Figure 15-33.

    Figure 15-33 Adding Columns to an Interactive Report

    Description of Figure 15-33 follows
    Description of "Figure 15-33 Adding Columns to an Interactive Report"

  6. Click the Move All icon (>>) between the Do Not Display list and the Display in Report list. The three missing columns will move to the Display in Report list.

  7. Click Apply at the bottom right of the Select Columns area. Your Issues page should now look like Figure 15-34.

    Figure 15-34 Issues Report with all Columns Displayed

    Description of Figure 15-34 follows
    Description of "Figure 15-34 Issues Report with all Columns Displayed"

    Next, you view the Issue Details page for one of the issues.

  8. Click the Edit icon to the left of one of the issues. You will see the Issue Details page displayed similar to the one in Figure 15-35.

    Figure 15-35 Issue Details

    Description of Figure 15-35 follows
    Description of "Figure 15-35 Issue Details"

  9. Click the Issues breadcrumb to go back to the Issues page.

  10. Click the Add Issue> button. An empty Issue Details page should appear.

Add Pages for Summary Reports

Now it's time to add a Summary Reports page that links to the following individual reports:

  • Assign Open Issues Report

  • Issue Summary by Project Report

  • Resolved by Month Identified Report

  • Target Resolution Dates Report

  • Average Days to Resolve Report

Topics in this section include:

Overview of Summary Reports Pages

When you complete this exercise, you will have a Summary Reports page, that resembles Figure 15-36, "Summary Reports Page", and a separate report page for each individual summary report. Each image on the Summary Reports page links to a separate summary report as shown in Figure 15-37, "Issue Summary By Report and Assign Open Issues" and Figure 15-38, "Target Resolution Dates and Average Days to Resolve".

Figure 15-36 Summary Reports Page

Description of Figure 15-36 follows
Description of "Figure 15-36 Summary Reports Page"

Figure 15-37 Issue Summary By Report and Assign Open Issues

Description of Figure 15-37 follows
Description of "Figure 15-37 Issue Summary By Report and Assign Open Issues"

Figure 15-38 Target Resolution Dates and Average Days to Resolve

Description of Figure 15-38 follows
Description of "Figure 15-38 Target Resolution Dates and Average Days to Resolve"

Figure 15-39 Resolved by Month Identified

Description of Figure 15-39 follows
Description of "Figure 15-39 Resolved by Month Identified"

Add a Summary Reports Page

Before creating the Summary Reports Page, you need to create an Image List with an Image Entry for each report linked to from the Summary Reports Page.

Create an Image List

To create the Image List:

  1. Click the Application home breadcrumb link.

  2. Click Shared Components.

  3. Under Navigation, click Lists.

  4. Click Create.

  5. For Name, enter Reports.

  6. For List Template, select Horizontal Images with Label List.

  7. Click Create.

    Now, create a list of images.

  8. Click Create List Entry >.

  9. Under Entry, make these changes:

    1. Sequence - Enter 10.

    2. Image - Enter:

      menu/address_book_bx_128x128.png
      
    3. List Entry Label - Enter:

      Issue Summary<br/>
      by Project
      
  10. Under Target, for Page, enter 9. This is the Issue Summary Report page that will be created later in this section.

  11. Click Create and Create Another.

  12. Under Entry, make these changes:

    1. Sequence - Enter 15.

    2. Image - Enter:

      menu/eba_checklist_bx_128x128.png
      
    3. List Entry Label - Enter:

      Assign Open<br/>Issues
      
  13. Under Target, for Page, enter 8. This is the Assign Open Issues page that will be created later in this section.

  14. Click Create and Create Another.

  15. Under Entry, make these changes:

    1. Sequence - Enter 20.

    2. Image - Enter:

      menu/calendar_bx_128x128.png
      
    3. List Entry Label - Enter:

      Target Resolution<br/>Dates
      
  16. Under Target, for Page, enter 11. This is the Target Resolution Dates page that will be created later in this section.

  17. Click Create and Create Another.

  18. Under Entry, make these changes:

    1. Sequence - Enter 30.

    2. Image - Enter:

      menu/piechart_bx_128x128.png
      
    3. List Entry Label - Enter:

      Average Days<br/>to Resolve
      
  19. Under Target, for Page, enter 12. This is the Average Days to Resolve page that will be created later in this section.

  20. Click Create and Create Another.

  21. Under Entry, make these changes:

    1. Sequence - Enter 40.

    2. Image - Enter:

      menu/generate_bx_128x128.png
      
    3. List Entry Label - Enter:

      Issues Resolved<br/>by Month
      
  22. Under Target, for Page, enter 10. This is the Issues Resolved By Month page that will be created later in this section.

  23. Click Create.

Create the Reports Page

To add the Reports Page that references the Image List:

  1. Click the application home breadcrumb.

  2. Click Create Page.

  3. Select Blank Page and click Next.

  4. For Page Number, enter 14 click Next.

  5. For Name, enter Reports.

  6. For Breadcrumb, select Breadcrumb.

  7. For Parent Entry, select Home.

  8. Click Next.

  9. Under Tabs, select Yes - Use an existing tab set and create a new tab within the existing tab set and click Next.

  10. For Existing Tab Set, select Issue Tracker (Home, Projects, Issues...) and click Next.

  11. For Tab Label, enter Reports and click Next.

  12. Click Finish.

  13. Click application home breadcrumb.

  14. Click Shared Components.

  15. Under Navigation, click Tabs.

  16. Click Reports tab.

  17. Click Edit icon.

  18. For Sequence, enter 25 and click Apply Changes.

  19. Click application home breadcrumb.

  20. Click Reports page.

  21. Under Regions, click Create button.

  22. Select List and click Next.

  23. For Title, enter Reports.

  24. For Region Template, select No Template and click Next.

  25. For List, select Reports.

  26. Click Create List Region.

Run the Reports Page

Run Reports page 14 by clicking the run icon at the top of the edit page. As shown in Figure 15-40, the Reports page displays a list of images.

Figure 15-40 Reports Page After Adding List of Images

Description of Figure 15-40 follows
Description of "Figure 15-40 Reports Page After Adding List of Images"

Note:

Because not all the reports have been created at this point in the tutorial, the report links under each image will not work. As each report is created, these links will navigate to the appropriate report.

Add an Assign Open Issues Report Page

Currently, you can assign an issue by editing it on the Issues Details page. Next, you add a new page named Assign Open Issues, 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.

    2. Allowed Operations - Select Update Only.

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

    3. Click Next.

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

  7. For Displayed Columns:

    1. Press CTRL and select the following columns:

      • ISSUE_SUMMARY

      • IDENTIFIED_BY_PERSON_ID

      • IDENTIFIED_DATE

      • RELATED_PROJECT_ID

      • ASSIGNED_TO_PERSON_ID

      • 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_ID

      • ASSIGNED_TO_PERSON_ID

      • STATUS

      • PRIORITY

    2. Click Next.

  11. For Page and Region Attributes:

    1. Page - Enter 8.

    2. Page Name - Enter Assign Open Issues.

    3. Region Title - Enter Assign Issues.

    4. Region Template - Select No Template.

    5. Breadcrumb - Select Breadcrumb.

    6. Entry Name - Enter Assign Open Issues

    7. For Parent Entry, select Reports.

    8. Click Next.

  12. For Tab Options, select Use an existing tab set and reuse an existing tab within that tab set.

  13. For Tab Set, select Issue Tracker (Home, Dashboard, Projects...).

  14. Click Next.

  15. For Use Tab, select T_REPORTS and click Next.

  16. For Button Labels:

    1. For Cancel Button Label, accept the default.

    2. For Submit Button Label, enter Apply Changes.

    3. Click Next.

  17. For Branching, enter 14 for When Cancel Button Pressed Branch to this Page and click Next.

  18. Review your selections and 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 Open 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_PERSON_ID",
        "IDENTIFIED_DATE",
        "RELATED_PROJECT_ID",
        "ASSIGNED_TO_PERSON_ID",
        "STATUS",
        "PRIORITY"
    FROM "#OWNER#"."IT_ISSUES"
    WHERE assigned_to_person_id IS NULL
    

To edit report attributes:

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

  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. By doing this, issues will be displayed by oldest first.

  4. Edit the following attributes for IDENTIFIED_BY_PERSON_ID:

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

    2. Under Column Definition, for Column Heading, enter Identified By.

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

    4. Scroll down to Lists of Values.

    5. For Named LOV, select PEOPLE.

    6. 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_ID column.

  6. Edit the following attributes for RELATED_PROJECT_ID:

    1. Under Column Definition, for Column Heading, enter Related Project.

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

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

    4. Click the Next button (>) at the top of the page to go to the ASSIGNED_TO_PERSON_ID column.

  7. Edit the following attributes for ASSIGNED_TO_PERSON_ID:

    1. Under Column Definition, for Column Heading, enter Assigned To.

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

    3. Under List of Values:

      • Named LOV - Select PEOPLE.

      • Display Null - Select Yes.

      • Null display value - Enter a hyphen (-).

    4. 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 display value, enter a hyphen (-).

    3. Click Apply Changes.

    The Report Attributes page appears.

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

    No Unassigned Issues.
    
  11. 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 Open Issues form appears as shown in Figure 15-41.

Figure 15-41 Assign Open Issues

Description of Figure 15-41 follows
Description of "Figure 15-41 Assign Open Issues"

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

Add an Issue Summary by Project Report Page

The Issue Summary report enables users to select a project and to 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

Create the Report Page

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. Breadcrumb - Select Breadcrumb.

    4. Parent Entry - Select Reports link.

    5. Click Next.

  6. For Tab Options, select Use an existing tab set and reuse an existing tab within that tab set.

  7. For Tab Set, select Issue Tracker (Home, Dashboard, Projects...) and click Next.

  8. For Use Tab, select T_REPORTS: label="Reports" and click Next.

  9. 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 it_issues
      WHERE related_project_id = :P9_PROJECT
      
    2. Click Next.

  10. For Report Attributes:

    1. Region Template - Select List Region with Icon (Chart).

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

    3. For Region Name - Enter Summary.

    4. Accept the remaining defaults and click Next.

  11. 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 Search Region

To create a new region to display above the report:

  1. From the Success page, click Edit Page 9.

    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. Region Template - Select Report Filter - Single Row.

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

    4. For Sequence, enter 5.

    5. 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. Sequence - Enter 31.

    3. Region - Select Issue Summary Report Parameters (1) 5.

    4. Click Next.

  5. For List of Values:

    1. Named LOV - Select PROJECTS.

    2. Null Text - Enter:

      - Select -
      
    3. Null Value - Enter:

      -1
      
    4. Click Next.

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

  7. For Default, enter -1.

  8. Click Create Item.

Create a Go Button

To create a Go button to perform 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. Sequence - Enter 33.

    3. Label - Enter Go.

    4. Request - Enter Go.

    5. Button Style - Select Template Based Button.

    6. Template - Select Button.

  5. Click Create Button.

Create a Reset Button

Now, create a Reset button and Reset branch to redisplay the default Issue Summary by Report Page.

To create a Reset button to clear 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_RESET.

    2. Sequence - Enter 32.

    3. Label - Enter Reset.

    4. Request - Enter Reset.

    5. Button Style - Select Template Based Button.

    6. Template - Select Button.

  5. Click Create Button.

To create a Reset branch:

  1. Under Branches, select Create icon.

  2. Accept defaults and click Next.

  3. For Target, make these changes:

    1. Page - Enter 9.

    2. Reset Pagination for this page - Select the checkbox.

    3. Clear Cache - Enter 9.

    4. Click Next.

  4. For When Button Pressed, select *P9_RESET.

  5. Click Create Branch.

Create a Assignment by Status Region

To create a new region to display below the report:

  1. Under Regions, click the Create icon.

  2. Select Report and click Next.

  3. Select SQL Report for region container and click Next.

  4. For Display Attributes:

    1. Title - Enter Assignments by Status.

    2. Region Template - Select No Template.

    3. For Sequence, enter 20.

    4. Accept the remaining defaults and click Next.

  5. For Enter SQL Query or PL/SQL function returning a SQL Query, enter:

    SELECT p.person_name,i.status, 
    COUNT(i.issue_id) issues
    FROM it_issues i, it_people p
    WHERE i.related_project_id = :P9_PROJECT
        AND i.assigned_to_person_id = p.person_id
    GROUP BY person_name, status
    
  6. Click Next.

  7. For Rows per Page, enter 20 and click Next.

  8. For Conditional Display:

    1. Condition Type - Select Value of Item in Expression 1 != Expression 2

    2. Expression 1 - Enter P9_PROJECT

    3. Expression 2 - Enter -1

  9. Click Create Region.

Refine Region Appearance

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. For ISSUES, change Heading to Number of Issues.

  5. Click Up Arrow at end of ISSUES to move above STATUS.

  6. For ISSUES, change Column Alignment to right.

  7. For PERSON_NAME, ISSUES and STATUS, select center for Heading Alignment.

  8. For PERSON_NAME, ISSUES and STATUS, uncheck Sort checkbox.

  9. For PERSON_NAME, ISSUES and STATUS, for Sort Sequence, select - .

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

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

    No issues found.
    
  12. Click Apply Changes.

Edit Headings and Report Settings for Summary Report

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 Summary.

  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.

  6. Scroll down to Conditional Display. For Condition Type, make these changes:

    1. Condition Type - Select Value of Item in Expression 1 != Expression 2

    2. Expression 1 - Enter P9_PROJECT

    3. Expression 2 - Enter -1

  7. 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 15-42.

Figure 15-42 Issue Summary By Project

Description of Figure 15-42 follows
Description of "Figure 15-42 Issue Summary By Project"

Add Resolved by Month Identified Report Page

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.

Add a Resolved by Month Identified Report Page

To add this report page:

  1. Go to the Application home page.

  2. Click Create Page.

  3. Select Chart and click Next.

  4. Select FLASH Chart and click Next.

  5. For Page Attributes:

    1. Page Number - Enter 10.

    2. Page Name - Enter Resolved by Month Identified.

    3. Region Template - Select No Template.

    4. Region Name - Enter Resolved by Month Identified.

    5. Breadcrumb - Select Breadcrumb.

    6. Entry Name - Enter Resolved by Month Identified.

    7. Parent Entry - Click Reports link.

    8. Click Next.

  6. For Tab Options, select Use an existing tab set and reuse an existing tab within that tab set.

  7. For Tab Set, select Issue Tracker (Home, Dashboard, Projects...), and click Next.

  8. For Use Tab, select T_REPORTS, and click Next.

  9. For Chart Type, select 3D Column.

  10. For Chart Animation, select Scale.

  11. For Background Type, select Gradient.

  12. For Background Color 1, enter #FFFFCC.

  13. For Background Color 2, enter #FFCC66.

  14. For Gradient Angle, enter 45.

  15. For Color Scheme, select Look 5.

  16. For X Axis Title, enter Month Identified.

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

  18. Accept all other defaults and click Next.

  19. For Query:

    1. SQL - Enter the following:

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

      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.

    2. For When No Data Found Message, enter:

      No Closed Issues found.
      
    3. Click Next.

  20. Review your selections and click Finish.

Edit the Chart

Next, modify month labels along x-axis to display at a 45 degree angle.

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 Flash Chart, next to Resolved by Month Identified.

  3. Under Display Settings, for Labels Rotation, enter 45.

  4. Click Apply Changes.

Run the Page

To view your newly created flash chart, click the Run Page icon. Your flash chart should resemble Figure 15-43.

Figure 15-43 Resolved by Month Identified

Description of Figure 15-43 follows
Description of "Figure 15-43 Resolved by Month Identified"

Add a Calendar to Display Target Resolution Dates

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 - Enter Target Resolution Dates.

    3. Region Template - Select No Template

    4. Region Name - Enter Target Resolution Dates.

    5. Breadcrumb - Select Breadcrumb.

    6. Parent Entry - Select Reports link.

    7. Click Next.

  6. For Tab Options, select Use an existing tab set and reuse an existing tab within that tab set.

  7. For Tab Set, select Issue Tracker (Home, Dashboard, Projects...), and click Next.

  8. For Use Tab, select T_REPORTS and click Next.

  9. 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 IT_ISSUES I, IT_PEOPLE P
      WHERE I.ASSIGNED_TO_PERSON_ID = P.PERSON_ID (+)
          AND (I.RELATED_PROJECT_ID = :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

  10. For Date/Display Columns:

    1. Date Column - Select TARGET_RESOLUTION_DATE.

    2. For Display Column - Select DISP.

    3. Click Next.

  11. Review your selections and click Finish.

Create a Search Region

To create a new region to display above the calendar:

  1. From the Success page, click Edit Page.

    The Page Definition for page 11, 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 Target Resolution Parameters.

    2. Region Template - Select Report Filter - Single Row.

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

    4. For Sequence, enter 5.

    5. Accept the remaining defaults and click Create.

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. Under Items, click the Create icon.

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

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

  4. For Display Position and Name:

    1. Item Name - Enter P11_PROJECT.

    2. Sequence - Enter 30.

    3. Region - Select Target Resolution Parameters (1) 5.

    4. Click Next.

  5. For List of Values:

    1. Named LOV - Select PROJECTS.

    2. Display Null Option - Select Yes.

    3. Null Text - Enter:

      - All -
      
    4. For Null Value - Enter:

      -1
      
    5. Click Next.

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

  7. For Source, 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 Target Resolution Parameters (1) 5 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. Sequence - Enter 40.

    3. Show: - Select Beginning on New Field.

    4. Label - Enter Go.

    5. Request - Enter Go.

    6. Button Style - Select Template Based Button.

    7. Template - Select Button.

  5. Click Create Button.

Create a Reset Button

Now, create a Reset button and Reset branch to redisplay the default Target Resolution Dates page.

To create a Reset button to clear the query:

  1. Under Buttons, click the Create icon.

  2. For Button Region, select Target Resolution Parameters (1) 5 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_RESET.

    2. Sequence - Enter 30.

    3. Show: - Select Beginning on New Field.

    4. Label - Enter Reset.

    5. Request - Enter Reset.

    6. Button Style - Select Template Based Button.

    7. Template - Select Button.

  5. Click Create Button.

To create a Reset branch:

  1. Under Branches, select Create icon.

  2. Accept defaults and click Next.

  3. For Target, make these changes:

    1. Page - Enter 11.

    2. Reset Pagination for this page - Select the checkbox.

    3. Clear Cache - Enter 11.

    4. Click Next.

  4. For When Button Pressed, select *P11_RESET.

  5. Click Create Branch.

Modify Calendar Buttons

To move the Calendar buttons to the Search region and modify:

  1. Under Buttons, click the Edit All icon.

  2. Make the following changes for each button:

    • Region - Select Target Resolution Parameters.

    • Align - Select Right.

    • Position - Select Region Template Position #CREATE#.

  3. Click Apply Changes.

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, Issue Details, to 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. Click Page 11 breadcrumb.

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

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

    1. Target is a - Select URL.

    2. URL Target - Enter:

      f?p=&APP_ID.:7:&SESSION.::&DEBUG.:7:P7_ISSUE_ID,P7_PREV_PAGE:#ISSUE_ID#,11
      
  4. Select the Region Definition tab at the top of the page.

  5. Scroll down to Header and Footer.

  6. In Region Footer, enter the following:

    This excludes Closed issues.
    
  7. Click Apply Changes.

Run the Page

To see your newly created calendar, click the Run Page icon. Note that you can click Weekly or Daily to see the corresponding calendar views. You can run the Issues page to find Target Resolution Dates. Then, run this Target Resolution Dates page and use the <Previous, and Next> buttons to navigate to the month of the target resolution date. You'll see the issue appear similar to the issue shown for Sunday January 13,2008 in Figure 15-44.

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.

Note:

Your data will have different dates because the insert scripts for the data are set relative to your system date; they are not hard coded. The dates will be different each time the script is inserted.

Figure 15-44 Target Resolution Dates

Description of Figure 15-44 follows
Description of "Figure 15-44 Target Resolution Dates"

Add a Bar Chart to Display 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 averages that number by assigned person.

Add a Bar Chart

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 Flash Chart and click Next.

  5. For Page Attributes:

    1. Page - Enter 12.

    2. Page Name - Enter Average Days to Resolve.

    3. Region Template - Select No Template

    4. Region Name - Enter Average Days to Resolve.

    5. Breadcrumb - Select Breadcrumb.

    6. Entry Name - Enter Average Days to Resolve.

    7. Parent Entry - Select Reports link.

    8. Accept the remaining defaults and click Next.

  6. For Tab Options, select Use an existing tab set and reuse an existing tab within that tab set.

  7. For Tab Set, select Issue Tracker (Home, Dashboard, Projects...) and click Next.

  8. For Use Tab, select T_REPORTS and click Next.

  9. For Chart Preview:

    1. Chart Type - Select Horizontal 2D Column.

    2. X Axis - Enter Days.

    3. Click Next.

  10. For Query:

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

      SELECT NULL l,
                          NVL(p.person_name,'None Assigned') person, 
                          AVG(i.actual_resolution_date-i.identified_date) days   
                      FROM it_issues i, it_people p
                      WHERE i.assigned_to_person_id = 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 When No Data Found Message, enter No issues with status 'Closed'.

    3. Accept the remaining defaults and click Next.

  11. Review your selections and click Finish.

Run the Page

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

Figure 15-45 Average Days to Resolve

Description of Figure 15-45 follows
Description of "Figure 15-45 Average Days to Resolve"

Add a Dashboard Page

Now that you have completed all the detail pages, you next need to create the Dashboard, add content to add the Dashboard and tie all the pages together. In this section, you create a Dashboard, as shown in Figure 15-46, "Dashboard", to display the following information:

Topics in this section include:

Overview of Dashboard Page

The Dashboard provides a quick snapshot of important issue tracking statistics. This page is for reporting only; no changes or new entries can be made on this page. Information displayed on the Dashboard includes:

  • Overdue Issues

  • Unassigned Issues

  • Recently Opened Issues

  • Open Issues by Project as a chart

Upon completion of this section, the application will have a Dashboard page similar to the one shown in Figure 15-46, "Dashboard".

Create Dashboard Page

To add the Dashboard page follow these steps:

  1. Go to the Application home page.

  2. Click Create Page.

  3. Select Blank Page and click Next.

  4. For Page Number, enter 18 and click Next.

  5. For Create Page, make these changes:

    1. Name - Enter Dashboard.

    2. Title - Enter Dashboard.

    3. Breadcrumb - Select Breadcrumb.

    4. Entry Name - Enter Dashboard.

    5. Parent Entry - Select Home link.

    6. Click Next.

  6. Select Yes - Use an existing tab set and reuse an existing tab within that tab set and click Next.

  7. For Existing Tab Set, select Issue Tracker (Home, Dashboard, Projects...) and click Next.

  8. For Select tab you would like to designate as "current" for this page:, select T_HOME: label="Home" and click Next.

  9. Review selections and click Finish.

  10. Select application home page breadcrumb.

  11. Select Shared Components.

  12. Under Navigation, select Tabs.

  13. Click Edit icon to the left of the Home tab.

  14. For Tab Name, enter T_DASHBOARD.

  15. For Tab Label, enter Dashboard.

  16. Under Current For Pages, for Tab Page, enter 18.

  17. Click Apply Changes.

Add An Overdue Issues Report

Next, add some content to the Dashboard. 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. Click application home page breadcrumb.

  2. Click 18 - Dashboard.

  3. Under Regions, click the Create icon

  4. Select Report and click Next.

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

  6. For Display Attributes:

    • Title - Enter Overdue Issues.

    • Region Template - Select Reports Region, Alternative 1.

    • Sequence - Enter 5.

    • Click Next.

  7. 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 it_issues i, it_people p, it_projects r
    WHERE i.assigned_to_person_id = p.person_id (+)
        AND i.related_project_id = r.project_id
        AND i.target_resolution_date < sysdate
        AND i.status != 'Closed'
    

    The outer join is necessary because the assignment is optional.

  8. Click Next.

  9. For Report Template, select template: 20. Standard

  10. 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_SUMMARY, enter the following for Heading:

    Summary
    
  4. For ASSIGNEE, change the Heading to:

    Assigned To
    
  5. For TARGET_RESOLUTION_DATE:

    Target
    
  6. Use Up and Down arrows to far right of each column to order columns in the following order: ISSUE_ID, ASSIGNEE, TARGET_RESOLUTION_DATE, PROJECT_NAME, PRIORITY, ISSUE_SUMMARY.

  7. For ISSUE_ID, deselect Show.

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

  8. Select Sort only for ASSIGNEE, TARGET_RESOLUTION_DATE and PROJECT_NAME. Deselect Sort for all others.

  9. Select left for Heading Alignment for TARGET_RESOLUTION_DATE.

  10. Select center for Heading Alignment for all columns except TARGET_RESOLUTION_DATE.

  11. For TARGET_RESOLUTION_DATE, select 1 for Sort Sequence.

  12. For ISSUE_ID, select - 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, enter:

      <table width="200" style=size:9><tr><td>#ISSUE_SUMMARY#</td></tr></table>

    2. For Link Attributes, enter:

      title="Click to edit"

    3. For Page, select 7.

    4. For Clear Cache, select 7.

    5. For Item 1, enter the Name:

      P7_ISSUE_ID
      
    6. For Item 1, enter the Value:

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

      P7_PREV_PAGE
      
    8. For Item 2, enter the Value:

      18
      
  3. Click Apply Changes.

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

    No Overdue Issues.

  5. Click Apply Changes.

Run Application

Run your application by clicking the run page icon. You should see the Dashboard page with the Overdue Issues region added as shown in Figure 15-47.

Figure 15-47 Dashboard with Overdue Issues Region Added

Description of Figure 15-47 follows
Description of "Figure 15-47 Dashboard with Overdue Issues Region Added"

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. Click the Edit Page 18 link in the Developer Toolbar at bottom of the page.

  2. Under Regions, click the Copy icon.

  3. In the Name column, click Overdue Issues.

  4. For To Page, accept the default 18, accept all other defaults and click Next.

  5. For Region Name, enter Unassigned Issues.

  6. For Sequence, enter 10 and 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 it_issues i,
        it_people p,
        it_projects r
    WHERE i.assigned_to_person_id IS NULL
        AND i.status != 'Closed'
        AND i.related_project_id = r.project_id
        AND i.identified_by_person_id = 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. Under Column Attributes for IDENTIFIEE, enter the following Heading:

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

    No Unassigned Issues.
    
  6. Use Up and Down arrows to far right to reorder columns as follows: ISSUES_ID, IDENTIFIEE, TARGET_RESOLUTION_DATE, PROJECT_NAME, PRIORITY, and ISSUE_SUMMARY.

  7. Click Apply Changes.

Run Application

Run your application by clicking the run page icon. The Dashboard appears. Scroll down to the bottom of the page. You should see the Unassigned Issues region as shown in Figure 15-48.

Figure 15-48 Unassigned Issues Region

Description of Figure 15-48 follows
Description of "Figure 15-48 Unassigned Issues Region"

Add a Recently Opened Issues Report

Next, you add a report of recently opened issues. The underlying query displays the five most recently opened issues within the past week.

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

  1. Click the Edit Page 18 link in the Developer Toolbar at the bottom of the page.

  2. Under Regions, click the Copy icon.

  3. Under Name, select Unassigned Issues.

  4. For To Page, accept the default 18, accept the remaining defaults, and click Next.

  5. For Region Name, enter Recently Opened Issues.

  6. For Sequence, enter 5.

  7. For Column, select 2.

  8. Click Copy Region.

To modify the query and edit the report region:

  1. Under the Regions section, click Recently Opened Issues.

  2. 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 it_issues i,
            it_people p,
            it_people p2,
            it_projects r
        WHERE i.assigned_to_person_id = p.person_id (+)
            AND i.related_project_id = r.project_id
            AND i.identified_by_person_id = p2.person_id
            AND i.created_on > (sysdate - 7)
    ORDER BY i.created_on desc)
    WHERE rownum < 6
    
  3. Select the Report Attributes tab at the top of the page.

  4. For all columns:

    1. Disable sorting by deselecting Sort.

    2. Set Sort Sequence to -.

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

  6. For ASSIGNEE, change Heading to:

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

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

    No Recently Opened Issues.
    
  9. Click Apply Changes.

Run Application

Run your application and use the Issues Details page to add an Issue to the database. After adding the new issue, click on the Dashboard tab. At the top right side of the page you should see the Recently Opened Issues region, as shown in Figure 15-49, displaying the newly added issue.

Figure 15-49 Recently Assigned Issues Region

Description of Figure 15-49 follows
Description of "Figure 15-49 Recently Assigned Issues Region"

Add an Open Issues by Project Pie Chart

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

To add a pie chart:

  1. Click the Edit Page 18 link in the Developer Toolbar at the bottom of the page.

  2. Under Regions, click the Create icon.

  3. Select Chart and click Next.

  4. Select Flash Chart and click Next.

  5. For Display Attributes, make these changes:

    • Title - Enter Open Issues by Project.

    • Region Template - Select Chart Region.

    • Sequence - Enter 10.

    • Column - Select 2.

    • Click Next.

  6. For Chart Preview, make these edits:

    • Chart Type - Select 3D Pie.

    • Click Next.

  7. For Source, enter the following in SQL:

    SELECT 'f?p=&APP_ID.:6:&SESSION.:::CIR:IREQ_STATUS,
        IREQ_PROJECT_ID:Open,'||r.project_id LINK,
        NVL(r.project_name,'No Project') label,
        COUNT(r.project_name) value
    FROM it_issues i, it_projects r
    WHERE i.status = 'Open' AND i.related_project_id = r.project_id
        GROUP BY NULL, r.project_name, r.project_id
        ORDER BY r.project_name, r.project_id
    
  8. For When No Data Found Message, enter No open issues.

  9. Click Create Region.

To edit the chart.

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

  2. Under Chart Settings, make these changes: For Chart Width, enter 550.

    1. Chart Height, enter 325.

    2. Chart Margin: Bottom, enter 25.

    3. Chart Margin: Right, enter 50.

  3. Under Display Settings, deselect Show Values.

  4. Click Apply Changes.

Run the Page

To view the revised page, click the Run Page icon. In Figure 15-50, "Final Dashboard with All Regions Added", three overdue issues where closed before going to the Dashboard.

Figure 15-50 Final Dashboard with All Regions Added

Description of Figure 15-50 follows
Description of "Figure 15-50 Final Dashboard with All Regions Added"

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:

For additional examples on this topic, please visit the following Oracle by Examples (OBEs):

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 or CLOB        IN
P_BODY_HTML                    VARCHAR2 or CLOB        IN     DEFAULT
P_SUBJ                         VARCHAR2                IN     DEFAULT
P_CC                           VARCHAR2                IN     DEFAULT
P_BCC                          VARCHAR2                IN     DEFAULT
P_REPLYTO                                      VARCHAR2                IN

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: Issue Details and Assign Open Issues.

On the Issue Details page, you can store the initial values and check them against any changes to see if an assignment has been made or changed. The Assign Open 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 IT_ISSUES. You can create this trigger programmatically using SQL Workshop.

Create a Trigger on IT_ISSUES

To create a before insert and update trigger on IT_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 IT_ISSUES and click Next.

  5. For Define:

    1. For Trigger Name, enter IT_ISSUES_AIU_EMAIL.

    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_person_id IS NOT NULL) 
          OR
          (UPDATING AND (:old.assigned_to_person_id IS NULL OR :new.assigned_to_person_id != :old.assigned_to_person_id) AND :new.assigned_to_person_id IS NOT NULL) THEN
          FOR c1 IN 
              (SELECT person_name, person_email
              FROM it_people
              WHERE person_id = :new.assigned_to_person_id)
          LOOP
              IF c1.person_email IS NOT NULL THEN
                   FOR c2 IN 
                       (SELECT project_name
                       FROM it_projects
                       WHERE project_id = :new.related_project_id)
                   LOOP
       
                   APEX_MAIL.SEND(
                       p_to => c1.person_email,
                       p_from => c1.person_email,
                       p_body => 
                       'You have been assigned a new issue.  ' ||chr(10)||
                       '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. 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.

Test Email Notification

You can test this trigger by:

  • Modifying one of the users to have a valid email address.

  • Assigning an unassigned issue to this user.

  • Checking the valid email inbox for an notification email.

To test your new trigger:

  1. Run your Issue Tracker application.

  2. Click the Users tab.

  3. Click edit icon next to Carla Downing.

  4. For Email Address, enter a valid 'email address'.

  5. Click Apply Changes.

  6. Click Reports tab.

  7. Click Assign Open Issues link.

  8. For Assigned To, select Carla Downing.

  9. Click Apply Changes.

  10. Wait a couple minutes, then check the destination email inbox. You should see an email where the Subject is New Issue Assignment and containing a body similar to Figure 15-51, "Email Notification Body".

    Figure 15-51 Email Notification Body

    Description of Figure 15-51 follows
    Description of "Figure 15-51 Email Notification Body"

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 IT_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 it_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 it_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 it_projects pr,
            it_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 it_issues i,
                it_people p
            WHERE i.assigned_to_person_id = p.person_id (+)
                AND i.related_project_id = 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 it_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 IT_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 IT_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 it_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 users

  • 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 and components, such as validations, processes and branches, based on user privileges. Once defined, you can associate an authorization scheme with any page, region, or item to restrict access. Each authorization scheme 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:

For additional examples on this topic, please visit the following Oracle by Examples (OBEs):

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 Users report that navigate to the Project Details and User Information pages

  • Associate the authorization scheme with the Project Details and User Information 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

Before applying the authorization scheme created in the following steps, create a user with the user name of HOWTO. The HOWTO user will have authorization to see the edit links on the Projects and Issues pages. Then, create another user, HOWTO2. This user should not be able to see the links.

See Also:

"Create Users"

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 it_people
      WHERE (upper(username) = 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 click Go.

    The Page Definition for page 2, Projects, appears.

  3. Under Regions, click Interactive Report next to Projects.

  4. Scroll down to Link Column and for authorization Scheme, select USER_CEO_OR_MANAGER.

  5. Click Apply Changes.

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 Add Project> link.

    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 Project Details Form

To associate the authorization scheme with the Project Details page:

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

    The Page Definition for page 3, Project Details, 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 User Report

To associate the authorization scheme with the Users report.

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

    The Page Definition for page 4, Users, appears.

  2. Under Regions, click Interactive Report next to Users.

  3. Scroll down to Link Column and for Authorization Scheme, select USER_CEO_OR_MANAGER.

  4. Click Apply Changes.

To associate the authorization scheme with the Add User button on the User 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.

Associate Objects with the User Information Form

To associate the authorization scheme with the User 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.

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 Issue Details page and the Assign Open Issues page. On the Assign Open Issues page, the only issues that are displayed are those that are unassigned. Because the issues are unassigned, security is not necessary.

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 Issue Details 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 IT_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 Issues Details 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 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_person_id
          FROM it_issues
          WHERE issue_id = :P7_ISSUE_ID)
      LOOP
          l_related_project := c1.related_project;
          l_assigned_to     := c1.assigned_to_person_id;
      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 it_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
          ELSEIF 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 Issue Details 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 - Issue Details.

  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 IT_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. One Page 7 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. For Sequence, enter 30.

    4. Click Next.

  5. For Source, enter the following in Enter HTML Text Region Source and 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 15-52, "New Region Displaying Authorization Failure" displays the Issue Details 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 15-52 New Region Displaying Authorization Failure

Description of Figure 15-52 follows
Description of "Figure 15-52 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 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 "Designing the 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 click Next.

  4. For Import File:

    1. Import file - Click the Browse button and 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 Next.

  6. On Application Install:

    1. Parsing 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.

    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 Oracle 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

    Instead of hard coding this ID as shown here, you could use the substitution string APP_ALIAS.

  • 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

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.