15 How to Build and Deploy an Issue Tracking Application

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.

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 Refined Projects Page"

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.

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.

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.

Figure 15-15 Users Page

Description of "Figure 15-15 Users Page"

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 User Information Page"

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.

   Figure 15-17 Action Icon

   
   Description of "Figure 15-17 Action Icon"
   
   

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 Revised Users 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, 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.

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.

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

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.

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 Reports Page After Adding List of Images"

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

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 Issue Summary By Project"

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 Resolved by Month Identified"

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.

Figure 15-44 Target Resolution Dates

Description of "Figure 15-44 Target Resolution Dates"

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 Average Days to Resolve"

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 Dashboard with Overdue Issues Region Added"

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 Unassigned Issues Region"

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 Recently Assigned Issues Region"

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 Final Dashboard with All Regions Added"

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 Email Notification Body"