10 How to Build and Deploy an Issue Tracking Application

Add Lists of Values

To add a list of values for Projects:

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

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

   2. When the Application home page appears select 5 - Create/Edit Person.

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. For Name, enter PROJECTS.

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

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. For Name, enter ROLES.

   2. For Type:, select Static

   3. Click Next.

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

   Table 10-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. Return to the Page Definition for Page 5. Click the Edit Page icon in the upper right corner.

Edit Display Attributes

To edit display attributes for P5_PERSON_ROLE:

1. Under Items, select P5_PERSON_ROLE.

2. Under Name, select Radiogroup from the Display As list.

3. Scroll down to Label.

4. Change Label to Role.

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

   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, select P5_ASSIGNED_PROJECT.

2. Under Name, select Select List from the Display As 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 the Page Rendering section, select the Items heading.

2. For P5_PERSON_NAME:

   1. For Prompt, enter Name.

   2. For Width, enter 60.

3. For P5_PERSON_EMAIL:

   1. For 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 the Validations section, click the Create icon.

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

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

4. On Validation Method:

   1. Select PL/SQL and click Next.

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

5. For Validation Name, enter PROJECT_MAND_FOR_LEADER_AND_MEMBER and click Next.

6. For Validation and Error Message:

   1. For 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 so to keep it in the validation, you need to break the string apart so that it is not recognized and replaced.

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

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

Figure 10-8 Revised Create/Edit Person Information Form

Description of "Figure 10-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. Navigate to the Page Definition for page 7.

2. Under the Lists of Values section, click the Create icon.

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

4. On Create List of Values (LOV):

   1. For Name, enter STATUS.

   2. For Type, select Static.

   3. Click Next.

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

   Table 10-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. Return to the Page Definition for Page 7. Click the Edit Page icon in the upper right corner.

2. Under the Lists of Values section and click the Create icon.

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

4. On Create List of Values (LOV):

   1. For Name, enter PRIORITIES.

   2. For Type, select Static.

   3. Click Next.

5. Enter the Display Value and Return Value pairs shown in Table 10-6.

   Table 10-6 Display Value and Return Value Pairs

   Display Value	Return Value
   High	High
   Medium	Medium
   Low	Low

   
   

6. Click Create List of Values.

To add a list of values for People:

1. Return to the Page Definition for Page 7. Click the Edit Page icon in the upper right corner.

2. Under the Lists of Values section and click the Create icon.

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

4. On Create List of Values (LOV):

   1. For Name, enter PEOPLE.

   2. For Type, select Dynamic.

   3. Click Next.

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

   SELECT person_name d, person_id v
      FROM ht_people
    ORDER BY 1
   
   

6. Click Created List of Values.

Edit Specific Items

Next, you edit individual items.

To edit P7_IDENTIFIED_BY:

1. Return to the Page Definition for Page 7 by clicking the Edit Page icon in the upper right corner.

2. Under Items, select P7_IDENTIFIED_BY.

3. Under Name, select Select List from the Display As list.

4. Scroll down to List of Values:

   1. For Named LOV, select PEOPLE.

   2. For 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. For Null display value, enter:

      - Select Person -
      
      

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

To edit P7_IDENTIFIED_DATE:

1. Navigate to P7_IDENTIFIED_DATE.

2. Under Name, select Date Picker (DD-MON-YYYY) from the Display As list.

3. Scroll down to Default:

   1. For Default value, enter:

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

   2. For Default Value Type, select PL/SQL Expression.

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

To edit P7_RELATED_PROJECT:

1. Navigate to P7_RELATED_PROJECT.

2. Under Name, select Select List from the Display As list.

3. Scroll down to List of Values:

   1. For Named LOV, select PROJECTS.

   2. For Display Null, select Yes.

   3. For Null display value, enter:

      - Select Project -
      
      

4. Click the Next button (>) at the top of the page to navigate to P7_STATUS.

To edit P7_STATUS:

1. Navigate to P7_STATUS.

2. Under Name, select Radiogroup from the Display As list.

3. In Label, enter:

   Status:
   
   

4. Scroll down to Element. Enter the following in the Form Element Option Attributes:

   class="instructiontext"
   
   

5. Scroll down to Default. In Default Value, enter Open.

6. Scroll down to List of Values:

   1. For Named LOV, select STATUS.

   2. For Columns, enter 3.

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

7. Click the Next button (>) at the top of the page to navigate to P7_PRIORITY.

To edit P7_PRIORITY:

1. Navigate to P7_PRIORITY.

2. Under Name, select Radiogroup from the Display As list.

3. In Label, enter:

   Priority:
   
   

4. Scroll down to Element. Enter the following in the Form Element Option Attributes:

   class="instructiontext"
   
   

5. Scroll down to Default. In Default value, enter Open.

6. Scroll down to List of Values:

   1. For Named LOV, select PRIORITIES.

   2. For Display Null, select Yes.

   3. For Columns, enter 4.

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

   4. For Null display value, enter -None-.

7. Click the Next button (>) at the top of the page to navigate to the next item.

To edit P7_TARGET_RESOLUTION_DATE:

1. Navigate to P7_TARGET_RESOLUTION_DATE.

2. Under Name, select Date Picker (DD-MON-YYYY) from the Display As list.

3. Click the Next button (>) at the top of the page to navigate to P7_ACTUAL_RESOLUTION_DATE.

To edit P7_ACTUAL_RESOLUTION_DATE:

1. Navigate to P7_ACTUAL_RESOLUTION_DATE.

2. Under Name, select Date Picker (DD-MON-YYYY) from the Display As list.

3. 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. Next, you create four new regions named Buttons, Identification, Progress, Resolution, and Auditing.

To create new regions to group items:

1. Under Regions, click the Create icon.

2. Select Multiple HTML.

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, select Create/Edit Issue.

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 the Page Rendering section, select the Items heading.

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. For P7_ISSUE_SUMMARY, enter 60 for Width.

6. For P7_IDENTIFIED_DATE, enter 12 for Width.

7. For P7_TARGET_RESOLUTION_DATE, enter 12 for Width.

8. For P7_ACTUAL_RESOLUTION_DATE, enter 12 for Width.

9. Click Apply Changes.

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

To move buttons to the Button region:

1. Return to the Page Definition for Page 7.

2. Under the Page Rendering section, select the Buttons heading.

3. Under Region for all buttons, select Buttons.

4. Click Apply Changes.

5. 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. On the Page Definition for Page 7, under Regions, select 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 P7_ISSUE_ID.

5. Click Apply Changes.

Next, change the audit columns to display only.

To edit P7_CREATED_DATE:

1. Under Items, select P7_CREATED_DATE.

2. Under Name, select Display as Text (saves state) from the Display As list.

3. Scroll down to 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. Scroll down to Source and enter the following in Format Mask:

   DD-MON-YYYY
   
   

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

To edit P7_CREATED_BY:

1. Under Name, select Display as Text (saves state) from the Display As list.

2. Scroll down to 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 navigate to the next item.

To edit P7_LAST_MODIFIED_DATE:

1. Under Name, select Display as Text (saves state) from the Display As list.

2. Scroll down to 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. Scroll down to Source and enter the following in Format Mask:

   DD-MON-YYYY
   
   

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

To edit P7_LAST_MODIFIED_BY:

1. Under Name, select Display as Text (saves state) from the Display As list.

2. Scroll down to 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. Under Validations, select P7_CREATED_DATE not null.

2. Click Delete.

   A dialog box displays prompting you to confirm the deletion.

3. Click OK to confirm your selection.

4. Under Validations, select P7_CREATED_BY not null.

5. Click Delete.

   A dialog box displays prompting you to confirm the deletion.

6. 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. For Item Name, enter:

      P7_PREV_PAGE
      
      

   2. For 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, select 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 Action, enter the following in Page (be sure to include the period):

   &P7_PREV_PAGE.
   
   

2. 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 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. Under Name, select CREATE.

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

4. For Button Name, enter CREATE_AGAIN.

5. For Label, enter Create and Create Another.

6. 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 Branches, click the Create icon.

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

3. For Target:

   1. For Page, enter 7.

   2. For Clear Cache, enter 7.

   3. For Set these items, enter the following:

      P7_PREV_PAGE
      
      

   4. For With these values, enter (be sure to include the period):

      &P7_PREV_PAGE.
      
      

   5. Click Next.

4. For Branch Conditions:

   1. For Sequence, enter 0.

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

7. Under Action, select include process success message.

8. Click Apply Changes.

   To see the changes, click the Run Page icon. (See Figure 10-9.)

   Figure 10-9 Create/Edit Issues Form

   
   Description of "Figure 10-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 of the Create button:

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

2. Under Regions, click the Create icon.

3. Select HTML and click Next.

4. Specify the type of HTML region container you want to create. Select HTML and click Next.

5. For Display Attributes:

   1. For Title, enter Buttons.

   2. For Region Template, select Button Region without Title.

   3. For 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, select the CREATE button.

2. In Text Label, enter:

   Add a New Issue
   
   

3. From Display in Region, select Buttons.

4. Scroll down to Optional URL Redirect:

   1. For Set These Items, enter:

      P7_PREV_PAGE
      
      

   2. For With These Values, enter 6.

5. Click Apply Changes.

Alter the Query and Display

Next, alter 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 the Regions section, select Report adjacent to Issues.

   The Report Attribute page appears.

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

3. Scroll down to Column Link.

   1. For Item 2, for Name enter:

      P7_PREV_PAGE
      
      

   2. For Item 2, for 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 the Display As list, select Display as Text (based on LOV, does not save state).

3. Scroll down to List of Values. For 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 the Display As list, select Display as Text (based on LOV, does not save state).

6. Scroll down to List of Values:

   1. For Named LOV, select PROJECTS.

   2. For Display Null, select Yes.

   3. In 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 the Display As list, select Display as Text (based on LOV, does not save state).

9. Scroll down to List of Values:

   1. For Named LOV, select PEOPLE.

   2. For Display Null, select Yes.

   3. In 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, select Custom.

2. For ISSUE_ID, delete the current entry under Heading.

3. For ISSUE_SUMMARY, change 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 Align., select center.

   3. For Heading Align. select center.

5. For all columns except ISSUE_ID, check Sort.

6. For ISSUE_SUMMARY, select 1 for Sort Sequence.

7. Scroll down to Layout and Pagination:

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

   2. For Number of Rows, enter 5.

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

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

   No issues found.
   
   

10. Click Apply Changes.

Add Support of 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 which will display above the report.

To create a new region:

1. Under Regions, click the Create icon.

2. Select HTML and click Next.

3. Select the type of HTML region container you want to create. Select HTML and click Next.

4. For Display Attributes:

   1. For Title, enter Issue Report Parameters.

   2. For Region Template, select accept the default of 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. On Display Position and Name:

   1. For Item Name, enter P6_IDENTIFIED_BY.

   2. For Region, select Issue Report Parameters.

   3. Click Next.

5. On 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. On Source, for 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. On 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. On Source, for 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 click Next.

4. On Display Position and Name:

   1. For Item Name, enter P6_STATUS.

   2. For Region, select Issue Report Parameters.

   3. Click Next.

5. On 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. On Source, for 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. On Identify Item Name and Display Position:

   1. For Item Name, enter P6_PRIORITY.

   2. For Region, select Issue Report Parameters.

   3. Click Next.

5. On 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. On 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. On Identify Item Name and Display Position:

   1. For Item Name, enter P6_RELATED_PROJECT.

   2. For Region, select Issue Report Parameters.

   3. Click Next.

5. On Identify 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. On 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. For Button Name, enter P6_GO.

   2. For Button Style, select Template Based Button.

   3. For 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 6_RELATED_PROJECT, P6_STATUS, and P6_PRIORITY:

1. 1. Under the Page Rendering section, select the Items heading.

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.

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, select Issues.

2. For Type, select SQL Query (PL/SQLfunction 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.

The report is now complete. Click the Run Page icon. (See Figure 10-10).

Figure 10-10 Issues Report

Description of "Figure 10-10 Issues Report"

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

Create a Tabular Form

To add a new page to support assigning multiple issues:

1. Navigate to the Application home page.