14 How to Build and Deploy an Issue Tracking Application

Edit Fields

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

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

   1. From the Developer toolbar, click Application.

   2. Click 3 - Create/Edit Project.

2. Under Items, click the Edit All icon.

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

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

   1. For Project Name, enter 60.

   2. For Start Date, enter 12.

   3. For Target End Date, enter 12.

   4. For Actual End Date, enter 12.

   5. Click Apply Changes.

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

Change the Date Picker Type

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

1. Edit the item P3_START_DATE.

   1. Under Items, click P3_START_DATE.

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

   3. Click Apply Changes.

2. Edit the item P3_TARGET_END_DATE.

   1. Under Items, select P3_TARGET_END_DATE.

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

   3. Click Apply Changes.

3. Edit the item P3_ACTUAL_END_DATE.

   1. Under Items, select P3_ACTUAL_END_DATE.

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

   3. Click Apply Changes.

Add a Validation

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

To add validations:

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

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

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

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

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

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

7. For Validation:

   1. Validation - Enter:

      to_date(:P3_ACTUAL_END_DATE,'DD-MON-YYYY') >= to_date(:P3_START_DATE,'DD-MON-YYYY')
      
      

   2. Error Message - Enter:

      Actual End Date must be same or after Start Date.
      
      

   3. Click Next.

8. For Conditions:

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

   2. Expression 1 - Enter:

      P3_ACTUAL_END_DATE.
      
      

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

   3. Click Create.

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

Figure 14-5 Modified Create/Edit Project

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

Change the Query to Include a Join

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

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

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

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

2. Under Regions, click People.

3. Scroll down to Source.

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

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

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

Modify the Headings

To modify the headings:

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

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

   1. PERSON_ID - Remove the Heading Edit.

   2. PERSON_NAME - Change Heading to Name.

   3. PERSON_EMAIL - Change Heading to Email.

   4. PERSON_ROLE - Change Heading to Role.

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

      Next, enable column heading sorting.

3. To enable column heading sorting:

   1. Select Sort for all columns except PERSON_ID.

   2. For PERSON_NAME, select 1 for Sort Sequence.

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

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

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

   No people found.
   
   

5. Click Apply Changes at the top of the page.

Run the Page

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

Figure 14-7 Revised People Page

Description of "Figure 14-7 Revised People Page"

Add a List of Values for Projects

To add a list of values for Projects:

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

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

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

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

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

4. For Name and Type:

   1. Name - Enter PROJECTS.

   2. Type - Select Dynamic.

   3. Click Next.

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

   SELECT project_name d, project_id v
     FROM ht_projects
    ORDER BY d
   
   

6. Click Create List of Values.

Add a List of Values for Roles

To add a list of values for Roles:

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

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

3. For Name and Type:

   1. Name - Enter ROLES.

   2. Type - Select Static

   3. Click Next.

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

   Table 14-4 Display Value and Return Value pairs

   Display Value	Return Value
   CEO	CEO
   Manager	Manager
   Lead	Lead
   Member	Member

   
   

5. Click Create List of Values.

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

Edit Display Attributes

To edit display attributes for P5_PERSON_ROLE:

1. Under Items, click P5_PERSON_ROLE.

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

3. Scroll down to Label.

4. Change Label to Role.

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

   class="instructiontext"
   
   

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

6. Scroll down to List of Values.

7. From the Named LOV list, select ROLES.

8. Click Apply Changes.

To edit display attributes for P5_ASSIGNED_PROJECT:

1. Under Items, click P5_ASSIGNED_PROJECT.

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

3. Scroll down to List of Values.

4. Under List of Values:

   1. From the Named LOV list, select PROJECTS.

      Next, specify that the underlying column is not mandatory.

   2. For Null display value, enter:

      - None -
      
      

5. Click Apply Changes.

To alter the display of fields and field labels:

1. Under Items, click the Edit All icon.

2. For P5_PERSON_NAME:

   1. Prompt - Enter Name.

   2. Width - Enter 60.

3. For P5_PERSON_EMAIL:

   1. Prompt - Enter Email Address.

   2. For Width, enter 60.

4. Click Apply Changes.

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

Create a Validation

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

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

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

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

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

4. For Validation Method:

   1. Select PL/SQL and click Next.

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

5. For Sequence and Name:

   1. Validation Name - Enter PROJECT_MAND_FOR_LEADER_AND_MEMBER.

   2. Accept the remaining defaults and click Next.

6. For Validation and Error Message:

   1. Validation - Enter:

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

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

   2. Error Message - Enter:

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

   3. Click Next.

7. Click Create.

Run the Page

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

Figure 14-8 Revised Create/Edit Person Information Form

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

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

Add Lists of Values

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

To add a list of values for Status:

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

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

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

4. On Create List of Values:

   1. Name - Enter STATUS.

   2. For Type, select Static.

   3. Click Next.

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

   Table 14-5 Display Value and Return Value Pairs

   Display Value	Return Value
   Open	Open
   On-Hold	On-Hold
   Closed	Closed

   
   

6. Click Create List of Values.

To add a list of values for Priorities:

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

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

3. On Create List of Values:

   1. Name - Enter PRIORITIES.

   2. For Type, select Static.

   3. Click Next.

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

   Table 14-6 Display Value and Return Value Pairs

   Display Value	Return Value
   High	High
   Medium	Medium
   Low	Low

   
   

5. Click Create List of Values.

To add a list of values for People:

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

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

3. On Create List of Values:

   1. For Name, enter PEOPLE.

   2. For Type, select Dynamic.

   3. Click Next.

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

   SELECT person_name d, person_id v
      FROM ht_people
    ORDER BY 1
   
   

5. Click Create List of Values.

6. Go to the Page Definition for page 7.

Edit Specific Items

Next, you edit individual items.

To edit P7_IDENTIFIED_BY:

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

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

3. Under List of Values:

   1. Named LOV - Select PEOPLE.

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

   3. Null display value - Enter:

      - Select Person -
      
      

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

   The Edit Page Item page appears.

To edit P7_IDENTIFIED_DATE:

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

2. Scroll down to Default:

   1. Default value - Enter:

      to_char(sysdate,'DD-MON-YYYY')
      
      

   2. Default Value Type - Select PL/SQL Expression.

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

   The Edit Page Item page appears.

To edit P7_RELATED_PROJECT:

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

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

   1. Named LOV - Select PROJECTS.

   2. Display Null- Select Yes.

   3. Null display value - Enter:

      - Select Project -
      
      

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

To edit P7_STATUS:

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

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

   Status:
   
   

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

   class="instructiontext"
   
   

4. Under Default, enter Open in the Default Value field.

5. Under List of Values:

   1. Named LOV - Select STATUS.

   2. Number of Columns - Enter 3.

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

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

To edit P7_PRIORITY:

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

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

   Priority:
   
   

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

   class="instructiontext"
   
   

4. Under Default, enter Open in Default value.

5. Under List of Values:

   1. Named LOV - Select PRIORITIES.

   2. Display Null - Select Yes.

   3. Number of Columns - Enter 4.

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

   4. Null display value - Enter the following.

      None-
      
      

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

To edit P7_TARGET_RESOLUTION_DATE:

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

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

To edit P7_ACTUAL_RESOLUTION_DATE:

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

2. Click Apply Changes.

Create Regions to Group Items

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

To create new regions to group items:

1. Under Regions, click the Create icon.

2. Select Multiple HTML and then click Next.

3. For the first row:

   • For Sequence, enter 5.

   • For Title, enter Buttons.

   • For Template, select Button Region without Title.

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

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

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

7. Click Create Region(s).

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

1. Under Regions, click Create/Edit Issues.

2. In Title, enter:.

   Issue Identification
   
   

3. Click Apply Changes.

Move Items to the Appropriate Regions

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

To move items to the appropriate regions:

1. Under Items, click the Edit All icon.

   The Page Items summary page appears.

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

   • P7_ASSIGNED_TO

   • P7_STATUS

   • P7_PRIORITY

   • P7_TARGET_RESOLUTION_DATE

   • P7_PROGRESS

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

   • P7_ACTUAL_RESOLUTION_DATE

   • P7_RESOLUTION_SUMMARY

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

   • P7_CREATED_DATE

   • P7_CREATED_BY

   • P7_LAST_MODIFIED_DATE

   • P7_LAST_MODIFIED_BY

5. Under Width, make the following edits:

   1. For P7_ISSUE_SUMMARY, enter 60.

   2. For P7_IDENTIFIED_DATE, enter 12.

   3. For P7_TARGET_RESOLUTION_DATE, enter 12.

   4. For P7_ACTUAL_RESOLUTION_DATE, enter 12.

6. Click Apply Changes.

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

To move buttons to the Button region:

1. Under Buttons, click the Edit All icon.

2. Under Region, select Buttons for all buttons.

3. Click Apply Changes.

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

Change the Display of Audit Columns

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

To create a condition for the Audit Information region.

1. Under Regions, click Audit Information.

2. Scroll down to Conditional Display.

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

4. In Expression 1, enter the following:

   P7_ISSUE_ID
   
   

5. Click Apply Changes.

Next, change the audit columns to display only.

To edit P7_CREATED_DATE:

1. Under Items, click P7_CREATED_DATE.

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

3. Under Label:

   1. For Label, enter:

      Created Date:
      
      

   2. For Template, select Optional Label with Help.

   3. For HTML Table Cell Attributes, enter:

      class="instructiontext"
      
      

4. Under Source, enter the following in Format Mask:

   DD-MON-YYYY
   
   

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

To edit P7_CREATED_BY:

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

2. Under Label:

   1. For Label, enter:

      Created By:
      
      

   2. For Template, select Optional Label with Help.

   3. For HTML Table Cell Attributes, enter:

      class="instructiontext"
      
      

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

To edit P7_LAST_MODIFIED_DATE:

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

2. Under Label:

   1. For Label, enter:

      Last Modified Date:
      
      

   2. For Template, select Optional Label with Help.

   3. For HTML Table Cell Attributes, enter:

      class="instructiontext"
      
      

3. Under Source, enter the following in Format Mask:

   DD-MON-YYYY
   
   

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

To edit P7_LAST_MODIFIED_BY:

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

2. Under Label:

   1. For Label, enter:

      Last Modified By:
      
      

   2. For Template, select Optional Label with Help.

   3. For HTML Table Cell Attributes, enter:

      class="instructiontext"
      
      

3. Click Apply Changes.

Remove Unnecessary Validations

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

To remove not null validations:

1. Delete P7_CREATED_DATE not null:

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

   2. Click Delete.

   3. Click OK to confirm your selection.

2. Delete P7_CREATED_BY not null:

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

   2. Click Delete.

   3. Click OK to confirm your selection.

Return the User to the Calling Page

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

To create a hidden item:

1. Under Items, click the Create icon.

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

3. For Display Position and Name:

   1. Item Name - Enter:

      P7_PREV_PAGE
      
      

   2. Region - Select Issue Identification.

   3. Click Next.

4. Click Create Item.

   The Page Definition for page 7 appears.

   Next, edit the Cancel button.

To edit the Cancel button:

1. Under Buttons, click Cancel.

2. Scroll down to Optional URL Redirect.

3. In Page, enter:

   &P7_PREV_PAGE.
   
   

   Note the period at the end.

4. Click Apply Changes.

   Next, edit the branch.

To edit the branch:

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

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

   &P7_PREV_PAGE.
   
   

3. Click Apply Changes.

Add Functionality to Support Adding Multiple Issues Sequentially

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

To add a new button:

1. Under Buttons, click the Copy icon.

2. For Button to copy, click CREATE.

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

4. For New Button:

   1. Button Name - Enter CREATE_AGAIN.

   2. Label - Enter Create and Create Another.

   3. Click Copy Button.

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

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

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

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

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

3. For Target:

   1. Page - Enter 7.

   2. Clear Cache - Enter 7.

   3. Set these items - Enter the following:

      P7_PREV_PAGE
      
      

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

      &P7_PREV_PAGE.
      
      

   5. Click Next.

4. For Branch Conditions:

   1. Sequence - Enter 0.

   2. When Button Pressed - Select CREATE_AGAIN.

5. Click Create Branch.

   The Page Definition for page 7 appears.

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

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

8. Click Apply Changes.

Run the Page

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

Figure 14-9 Create/Edit Issues Form

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

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

Move Create Button to a New Region

To create a new region for the Create button:

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

2. Under Regions, click the Create icon.

3. Select HTML and click Next.

4. Select HTML for region container and click Next.

5. For Display Attributes:

   1. Title - Enter Buttons.

   2. Region Template - Select Button Region without Title.

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

   4. Click Next.

6. Click Create Region.

To move the Create button to the Buttons region:

1. Under Buttons, click the Create link.

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

   Add a New Issue
   
   

3. Under Displayed, select Buttons in the Display in Region field.

4. Under Optional URL Redirect:

   1. Set These Items - Enter:

      P7_PREV_PAGE
      
      

   2. For With These Values, enter 6.

5. Click Apply Changes.

Change the Query and Display

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

To edit column attributes for ISSUE_ID:

1. Under Regions, select Report next to Issues.

   The Report Attributes page appears.

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

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

   1. For Item 2, Name, enter:

      P7_PREV_PAGE
      
      

   2. For Item 2, Value, enter 6.

4. Click Apply Changes.

To edit column attributes for IDENTIFIED_BY, RELATED_PROJECT and ASSIGNED_TO:

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

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

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

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

   The Column Attributes page for RELATED_PROJECT appears.

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

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

   1. Named LOV - Select PROJECTS.

   2. Display Null - Select Yes.

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

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

   The Column Attributes page for ASSIGNED_TO appears.

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

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

   1. Named LOV - Select PEOPLE.

   2. Display Null - Select Yes.

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

10. Click Apply Changes.

    The Report Attributes page appears.

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

To alter the report display:

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

2. For ISSUE_ID, delete the Heading text.

3. For ISSUE_SUMMARY, change the Heading to Summary.

4. For TARGET_RESOLUTION_DATE:

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

      Target<br>Resolution<br>Date
      
      

   2. For Column Alignment, select center.

   3. For Heading Alignment, select center.

5. To sort on ISSUE_ID:

   1. For all columns except ISSUE_ID, check Sort.

   2. For ISSUE_SUMMARY, select 1 for Sort Sequence.

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

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

   2. Number of Rows - Enter 5.

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

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

   No issues found.
   
   

9. Click Apply Changes.

Add Support for Filtering

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

To create a new region:

1. Under Regions, click the Create icon.

2. Select HTML and then click Next.

3. Select HTML for region container and click Next.

4. For Display Attributes:

   1. For Title, enter Issue Report Parameters.

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

   3. For Sequence, enter 5.

   4. Click Next.

5. Click Create Region.

Next, create the items.

To create the item for Identified By:

1. Under Items, click the Create icon.

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

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

4. For Display Position and Name:

   1. For Item Name - Enter P6_IDENTIFIED_BY.

   2. For Region, select Issue Report Parameters.

   3. Click Next.

5. For Identify List of Values:

   1. For Named LOV, select PEOPLE.

   2. For Null Text, enter:

      - All -
      
      

   3. For Null Value, enter:

      -1
      
      

   4. Click Next.

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

7. For Source, Default, enter:

   -1
   
   

8. Click Create Item.

To create an item for Assigned To:

1. Under Items, click the Create icon.

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

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

4. For Display Position and Name:

   1. For Item Name, enter P6_ASSIGNED_TO.

   2. For Region, select Issue Report Parameters.

   3. Click Next.

5. For Identify List of Values:

   1. Named LOV - Select PEOPLE.

   2. For Null Text - Enter:

      - All -
      
      

   3. For Null Value - Enter:

      -1
      
      

   4. Click Next.

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

7. For Source, Default, enter:

   -1
   
   

8. Click Create Item.

To create an item for Status:

1. Under Items, click the Create icon.

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

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

4. For Display Position and Name:

   1. Item Name - Enter P6_STATUS.

   2. For Region - Select Issue Report Parameters.

   3. Click Next.

5. For Identify List of Values:

   1. For Named LOV - Select STATUS.

   2. For Null Text - Enter:

      - All -
      
      

   3. For Null Value - Enter:

      -1
      
      

   4. Click Next.

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

7. For Source, Default, enter:

   -1
   
   

8. Click Create Item.

To create an item for Priority:

1. Under Items, click the Create icon.

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

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

4. For Identify Item Name and Display Position:

   1. For Item Name - Enter P6_PRIORITY.

   2. For Region - Select Issue Report Parameters.

   3. Click Next.

5. For List of Values:

   1. For Named LOV - Select PRIORITIES.

   2. For Null Text - Enter:

      - All -
      
      

   3. For Null Value - Enter:

      -1
      
      

   4. Click Next.

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

7. For Source, for Default, enter:

   -1
   
   

8. Click Create Item.

To create an item for Related Project:

1. Under Items, click the Create icon.

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

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

4. For Display Position and Name:

   1. For Item Name - Enter P6_RELATED_PROJECT.

   2. For Region - Select Issue Report Parameters.

   3. Click Next.

5. For Identify List of Values:

   1. For Named LOV - Select PROJECTS.

   2. For Null Text - Enter:

      - All -
      
      

   3. For Null Value - Enter:

      -1
      
      

   4. Click Next.

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

7. For Source, for Default, enter:

   -1
   
   

8. Click Create Item.

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

To create Go button:

1. Under Buttons, click the Create icon.

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

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

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

4. Click Next.

5. For Button Attributes:

   1. Button Name - Enter P6_GO.

   2. Button Style - Select Template Based Button.

   3. Template - Select Button.

6. Click Create Button.

   The Page Definition for page 6 appears.

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

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

1. Under Items, click the Edit All icon.

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

3. Click Apply Changes.

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

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

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

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

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

1. Under Regions, click Issues.

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

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

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

4. Click Apply Changes.

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

Run the Page

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

Figure 14-10 Issues Report

Description of "Figure 14-10 Issues Report"

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

Add Lists of Values

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

To add lists of values:

1. From the Success page, click Edit Page.

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

2. Under Regions, click Assign Issues.

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

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

To edit report attributes:

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

   Edit the heading for ISSUE_SUMMARY.

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

   Summary
   
   

3. To sort by ISSUE_ID:

   1. For all columns except ISSUE_ID, select Sort.

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

4. Edit the following attributes for IDENTIFIED_BY:

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

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

   3. Scroll down to Lists of Values.

   4. For Named LOV, select PEOPLE.

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

5. Edit the following attributes for IDENTIFIED_DATE:

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

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

6. Edit the following attributes for RELATED_PROJECT:

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

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

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

7. Edit the following attributes for ASSIGNED_TO:

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

   2. Under List of Values:

      • Named LOV - Select PEOPLE.

      • Display Null - Select Yes.

      • Null Text - Enter a hyphen (-).

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

8. Edit the following attributes for STATUS:

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

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

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

9. Edit the following attributes for PRIORITY:

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

   2. Under List of Values:

      • From Named LOV, select PRIORITIES.

      • For Display Null, select Yes.

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

   3. Click Apply Changes.

   The Report Attributes page appears.

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

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

    No Unassigned Issues.
    
    

12. Click Apply Changes.

Delete the Unnecessary Cancel Button

The wizard created an unnecessary Cancel button.

To delete the Cancel button:

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

2. Click Delete.

3. Click OK to confirm your selection.

Run the Page

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

Figure 14-11 Assign Issues

Description of "Figure 14-11 Assign Issues"

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

Create a New Region

To create a new region to display above the report:

1. From the Success page, click Edit Page.

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

2. Under Regions, click the Create icon.

3. Select HTML and click Next.

4. Select HTML for region container and click Next.

5. For Display Attributes:

   1. Title - Enter Issue Summary Report Parameters.

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

   3. For Sequence, enter 5.

   4. Accept the remaining defaults and click Next.

6. Click Create Region.

Create the Project Item

To create the Project item:

1. Under Items, click the Create icon.

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

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

4. For Display Position and Name:

   1. Item Name - Enter P9_PROJECT.

   2. Region - Select Issue Summary Report Parameters.

   3. Click Next.

5. For List of Values:

   1. Named LOV - Select PROJECTS.

   2. Null Text - Enter:

      - Select -
      
      

   3. Null Text - Enter:

      - 1
      
      

   4. Click Next.

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

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

8. Click Create Item.

Create a Go Button

To create a Go button to execute the query:

1. Under Buttons, click the Create icon.

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

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

4. For Button Attributes:

   1. Button Name - Enter P9_GO.

   2. Button Style - Select Template Based Button.

   3. Template - Select Button.

5. Click Create Button.

Edit Headings and Report Settings

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

To edit the headings and report settings:

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

2. For Headings Type, select Custom.

3. Under Column Attributes:

   1. Change the Heading for FIRST_IDENTIFIED to:

      First Issue Identified:
      
      

   2. Change the Heading for LAST_CLOSED to:

      Last Issue Closed:
      
      

   3. Change the Heading for TOTAL_ISSUES to:

      Total Issues:
      
      

   4. Change the Heading for OPEN_ISSUES to:

      Open Issues:
      
      

   5. Change the Heading for ONHOLD_ISSUES to:

      On-Hold Issues:
      
      

   6. Change the Heading for CLOSED_ISSUES to:

      Closed Issues:
      
      

   7. Change the Heading for OPEN_NO_PRIOR to:

      Open Issues with No Priority:
      
      

   8. Change the Heading for OPEN_HIGH_PRIOR:

      Open Issues of High Priority:
      
      

   9. Change the Heading for OPEN_MEDIUM_PRIOR to:

      Open Issues of Medium Priority:
      
      

   10. Change the Heading for OPEN_LOW_PRIOR:

       Open Issues of Low Priority:
       
       

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

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

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

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

   1. Scroll down to Conditional Display.

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

   3. In Expression 1, enter P9_PROJECT.

   4. For Expression 2, enter -1.

6. Click Apply Changes.

Create a Query to Retrieve Assignments

To create a query to retrieve assignments by status.

1. Under Regions, click the Create icon.

2. Select Report and then click Next.

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

4. For Display Attributes:

   1. Title - Enter Assignments by Status.

   2. Column - Select 2.

   3. Accept the remaining defaults and click Next.

5. For Source:

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

      SELECT p.person_name, 
             i.status, 
             COUNT(i.issue_id) issues
        FROM ht_issues i,
             ht_people p
      WHERE i.related_project = :P9_PROJECT
        AND i.assigned_to = p.person_id
      GROUP BY person_name, status
      
      

   2. Rows Per Page - Enter 20.

   3. Break Columns - Select Column 1.

   4. Accept the remaining defaults and click Next.

6. For Conditional Display:

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

   2. In Expression 1 enter:

      P9_PROJECT
      
      

   3. For Expression 2 enter -1.

7. Click Create Region.

To edit headings and report settings:

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

2. For Headings Type, select Custom.

3. For PERSON_NAME, change Heading to Assigned To.

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

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

   No issues found.
   
   

6. Click Apply Changes.

Run the Page

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

Figure 14-12 Issue Summary by Project Report

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

Edit the Chart

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

To edit the chart:

1. From the Success page, select Edit Page.

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

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

3. Under Chart Settings:

   1. Chart Height - Enter 300.

   2. Deselect Show Legend.

4. Scroll to Axes Setting:

   1. For X Axis Title, enter Date Identified.

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

5. Click Apply Changes.

Run the Page

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

Figure 14-13 Resolved by Month Identified Line Chart

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

Create a Calendar

To create a calendar of target resolution dates:

1. Go to the Application home page.

2. Click Create Page.

3. Select Calendar and click Next.

4. Select SQL Calendar and click Next.

5. For Page Attributes:

   1. Page Number - Enter 11.

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

   3. Click Next.

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

7. For Table/View Owner:

   1. In Enter SQL Query, enter the following:

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

   2. Click Next.

   Note that:

   • The target_resolution_date is the date on which the issue displays

   • The issue_summary is concatenated with the person assigned

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

8. For Date/Display Columns:

   1. Date Column - Select TARGET_RESOLUTION_DATE.

   2. For Display Column - Select DISP.

   3. Click Next.

9. Review your selections and click Finish.

Add an Item to Support Project Look Up

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

To add an item to support project look up:

1. From the Success page, select Edit Page.

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

2. Under Items, click the Create icon.

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

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

5. For Display Position and Name:

   1. Item Name - Enter P11_PROJECT.

   2. Accept the remaining defaults and click Next.

6. For List of Values:

   1. Named LOV - Select PROJECTS.

   2. Null Text - Enter:

      - All Projects -
      
      

   3. For Null Value - Enter:

      -1
      
      

   4. Click Next.

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

8. For Source, Default, enter:

   -1
   
   

9. Click Create Item.

Create a Go Button

To create a Go button to execute the query:

1. Under Buttons, click the Create icon.

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

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

4. For Button Attributes:

   1. Button Name - Enter P11_GO.

   2. Button Style - Select Template Based Button.

   3. Template - Select Button.

5. Click Create Button.

Modify Calendar Attributes

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

To modify the Calendar Attributes:

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

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

   1. Page - Enter 7.

   2. Clear Cache - Enter 7.

   3. Set these items - Enter:

      P7_ISSUE_ID,P7_PREV_PAGE
      
      

   4. For With these values - Enter:

      #ISSUE_ID#,11
      
      

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

4. Scroll down to Header and Footer.

5. In Region Footer, enter the following:

   This excludes Closed issues.
   
   

6. Click Apply Changes.

Run the Page

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

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

Figure 14-14 Target Resolution Dates Report

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

Run the Page

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

Figure 14-15 Average Days to Resolve Report

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

Create List Entries

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

To add a list item for Assign Issues:

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

2. For List Entry Label, enter Assign Issues.

3. Under Target, specify the following

   1. Page - Select 8.

   2. Select reset pagination for this page.

4. Click Create.

   The List Entries page appears.

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

To add four more list items:

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

2. To define a list item for Issues:

   1. For Sequence, enter 20.

   2. For List Entry Label, enter Issues.

   3. Under Target, specify the following:

      • Page - Select 6.

      • Select reset pagination for this page.

      • Clear Cache - Enter 6.

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

3. Click Create and Create Another.

4. To define a list item for Issue Summary:

   1. For Sequence, enter 30.

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

   3. Under Target, specify the following:

      • Page - Select 9.

      • Select reset pagination for this page.

      • Clear Cache - Enter 9.

5. Click Create and Create Another.

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

   1. For Sequence, enter 40.

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

   3. Under Target, select 10 for Page.

7. Click Create and Create Another.

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

   1. For Sequence, enter 50.

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

   3. Under Target, specify the following:

      • Page - Select 11.

      • Select reset pagination for this page.

9. Click Create and Create Another.

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

    1. For Sequence, enter 60.

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

    3. Under Target, select 12 for Page.

11. Click Create.

Include the List on the Home Page

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

To change the page template on the home page:

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

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

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

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

   The Page attributes page appears.

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

5. Click Apply Changes.

Next, create a region to contain your menu.

To create a new region:

1. Under Regions, click the Create icon.

2. Select List and click Next.

3. For Display Attributes:

   1. Title - Enter Menu.

   2. Region Template - Select No Template.

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

   4. Click Next.

4. For Source, select Main Menu.

5. Click Create List Region.

Add a Report of Overdue Issues

To add a report to display overdue issues:

1. Under Regions, click the Create icon.

2. Select Report and then click Next.

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

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

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

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

   The outer join is necessary because the assignment is optional.

6. Click Create Region.

Edit the Overdue Issues Report

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

To edit the column headings:

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

2. For Headings Type, select Custom.

3. For ISSUE_ID, remove the Heading.

4. For ISSUE_SUMMARY, enter the following for Heading:

   Summary
   
   

5. For ASSIGNEE, change the Heading to:

   Assigned To
   
   

6. For TARGET_RESOLUTION_DATE:

   1. For Heading, enter:

      Target<br>Resolution<br>Date
      
      

   2. For Column Alignment, select center.

   3. For Heading Alignment, select center.

7. For ISSUE_ID, deselect Show.

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

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

9. For TARGET_RESOLUTION_DATE, select 1 for Sort Sequence.

10. For ISSUE_SUMMARY, select 2 for Sort Sequence.

To edit column attributes for ISSUE_SUMMARY:

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

2. Scroll down to Column Link:

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

   2. For Page, select 7.

   3. For Clear Cache, select 7.

   4. For Item 1, enter the Name:

      P7_ISSUE_ID
      
      

   5. For Item 1, enter the Value:

      #ISSUE_ID#
      
      

   6. For Item 2, enter the Name:

      P7_PREV_PAGE
      
      

   7. For Item 2, enter the Value:

      1
      
      

3. Click Apply Changes.

To select layout and pagination attributes:

1. Under Layout and Pagination:

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

   2. For Number of Rows, enter 5.

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

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

   No Overdue Issues.
   
   

4. Click Apply Changes.

Run the Page

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

Figure 14-16 Revised Home Page

Description of "Figure 14-16 Revised Home Page"

Edit Breadcrumb Entry for Page 1

To edit the breadcrumb entry for page 1:

1. Under Breadcrumb Entries, click Page 1.

2. Under Breadcrumb, enter 1 in Page.

3. Under Entry, enter Home for Short Name.

4. Under Target, enter 1 in Page.

5. Click Apply Changes.

Create Breadcrumb Entry for Page 2

To add a breadcrumb entry for page 2:

1. Click Create Breadcrumb Entry.

2. Under Breadcrumb, enter 2 in Page.

3. Under Entry:

   1. Parent Entry - Select Home.

   2. Short Name - Enter Projects.

4. Under Target, enter 2 in Page.

5. Click Create.

Create Breadcrumb Entry for Page 3

To create a breadcrumb entry for page 3:

1. Click Create Breadcrumb Entry.

2. Under Breadcrumb, enter 3 in Page.

3. Under Entry:

   1. Parent Entry - Select Projects.

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

4. Under Target, enter 3 in Page.

5. Click Create.

Create Breadcrumb Entry for Page 4

To create a breadcrumb entry for page 4:

1. Click Create Breadcrumb Entry.

2. Under Breadcrumb, enter 4 in Page.

3. Under Entry:

   1. For Parent Entry - Select Home.

   2. For Short Name - Enter People.

4. Under Target, enter 4 in Page.

5. Click Create.

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

Create Breadcrumb Entry for Page 5

To create a breadcrumb entry for page 5:

1. Click Create Breadcrumb Entry.

2. Under Breadcrumb, enter 5 in Page.

3. Under Entry:

   1. For Parent Entry - Select People.

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

4. Under Target, enter 5 in Page.

5. Click Create.

Create Breadcrumb Entry for Page 6

To create a breadcrumb entry for page 6:

1. Click Create Breadcrumb Entry.

2. Under Breadcrumb, enter 6 in Page.

3. Under Entry:

   1. For Parent Entry - Select Home.

   2. For Short Name - Enter Issues.

4. Under Target, enter 6 in Page.

5. Click Create.

Create Breadcrumb Entry for Page 7

To create a breadcrumb entry for page 7:

1. Click Create Breadcrumb Entry.

2. Under Breadcrumb, enter 7 in Page.

3. Under Entry:

   1. For Parent Entry - Select Home.

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

4. Under Target, enter 7 in Page.

5. Click Create.

Create Breadcrumb Entry for Page 8

To create a breadcrumb entry for page 8:

1. Click Create Breadcrumb Entry.

2. Under Breadcrumb, enter 8 in Page.

3. Under Entry:

   1. For Parent Entry - Select Home.

   2. For Short Name - Enter Assign Issues.

4. Under Target, enter 8 in Page.

5. Click Create.

Create Breadcrumb Entry for Page 9

To create a breadcrumb entry for page 9:

1. Click Create Breadcrumb Entry.

2. Under Breadcrumb, enter 9 in Page.

3. For Entry:

   1. For Parent Entry - Select Home.

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

4. Under Target, enter 9 in Page.

5. Click Create.

Create Breadcrumb Entry for Page 10

To create a breadcrumb entry for page 10:

1. Click Create Breadcrumb Entry.

2. Under Breadcrumb, enter 10 in Page.

3. Under Menu Option:

   1. For Parent Entry- Select Home.

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

4. Under Target, enter 10 in Page.

5. Click Create.

Create Breadcrumb Entry for Page 11

To create a breadcrumb entry option for page 11:

1. Click Create Breadcrumb Entry.

2. Under Breadcrumb, enter 11 in Page.

3. Under Entry:

   1. For Parent Entry - Select Home.

   2. For Short Name - Enter Target Resolution Dates.

4. Under Target, enter 11 in Page.

5. Click Create.

Create Breadcrumb Entry for Page 12

To create a breadcrumb entry for page 12:

1. Click Create Breadcrumb Entry.

2. Under Breadcrumb, enter 12 in Page.

3. Under Entry:

   1. For Parent Entry - Select Home.

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

4. Under Target, enter 12 in Page.

5. Click Create.

Run the Page

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

Figure 14-17 Revised Home Page with Breadcrumb Menu

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

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

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