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

2. Under Shared Components, locate 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 PROJECTS.

   2. For Type:, select Dynamic and click Next.

5. In Query, enter:

   SELECT project_name d, project_id v
     FROM ht_projects
    ORDER BY d
   
   

6. Click Create Named LOV.

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 Create List of Values, accept the default From Scratch and click Next.

3. On Create List of Values (LOV):

   1. For Name, enter ROLES.

   2. For Type:, select Static and 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 Static LOV.

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 Identification, select Radiogroup from the Display As list.

3. Under Label, in Label enter Role.

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

5. Under List of Values, select ROLES from the Named LOV list.

6. Click Apply Changes.

To edit display attributes for P5_ASSIGNED_PROJECT:

1. Under Items, select P5_ASSIGNED_PROJECT.

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

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

4. Click Apply Changes.

To alter the display of fields and field labels:

1. Click the heading Items.

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 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 since 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 Identify Validation Type, accept the default Item level validation and click Next.

3. For Item, select P5_ASSIGNED_PROJECT and click Next.

4. For Select a validation method, select PL/SQL and click Next.

5. For Pick the type of PL/SQL validation you wish to create, accept the default PL/SQL Expression and click Next.

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

7. On Identify 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 HTML DB 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.

8. Click Create.

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

Try entering some records to test the validation. Try to enter a CEO with a project and then try to enter a Lead without a project. Both cases will 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 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 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 Static LOV.

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 and 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 Static LOV.

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

5. In Query enter:

   SELECT person_name d, person_id v
      FROM ht_people
    ORDER BY 1
   
   

6. Click Created Named LOV.

Edit Specific Items

Next, you edit individual items.

To edit P7_IDENTIFIED_BY:

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

2. Under Items, select P7_IDENTIFIED_BY.

3. Under Identification, 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 Identification, for select Date Picker (DD-MON-YYYY) from the Display As list.

3. Scroll down to Default Value:

   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 Identification, for select Select List from the Display As list.

3. Scroll down to List of Values:

   1. For Named LOV, select PEOPLE.

   2. For Display Null, select Yes.

   3. For Null display value, enter:

      - Select Person -
      
      

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 Identification, for 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 Attribute:

   class="instructiontext"
   
   

5. Under Default Value, enter Open in Default value.

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 the next item.

To edit P7_PRIORITY:

1. Navigate to P7_PRIORITY.

2. Under Identification, for 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 attribute:

   class="instructiontext"
   
   

5. Under Default Value, enter Open in Default value.

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 Identification, for select Date Picker (DD-MON-YYYY) from the Display As list.

3. Click Apply Changes.

To edit P7_ACTUAL_RESOLUTION_DATE:

1. Navigate to P7_ACTUAL_RESOLUTION_DATE.

2. Under Identification, for 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 will make data entry easier for users. Next, you will 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. From the Tasks list, select Create Multiple HTML regions.

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 will also need to modify some item widths.

To move items to the appropriate regions:

1. Under Items, 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 Buttons section, click 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 will display when a user edits an existing issue, but will not appear when a user creates a new issue.

To create a condition for the Audit Information region.

1. On the Page Definition of Page 7, select the Audit Information region.

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 Identification, select Display as Text (saves state) from the Display As list.

3. For Label, enter:

   Created Date:
   
   

4. For Template, select Optional Label with Help.

5. For HTML Table Cell Attributes, enter:

   class="instructiontext"
   
   

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

   DD-MON-YYYY
   
   

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

To edit P7_CREATED_BY:

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

2. For Label, enter:

   Created By:
   
   

3. For Template, select Optional Label with Help.

4. For HTML Table Cell Attributes, enter:

   class="instructiontext"
   
   

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

To edit P7_LAST_MODIFIED_DATE:

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

2. For Label, enter:

   Last Modified Date:
   
   

3. For Template, select Optional Label with Help.

4. For HTML Table Cell Attributes, enter:

   class="instructiontext"
   
   

5. Under Source, for Format Mask, enter:

   DD-MON-YYYY
   
   

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

To edit P7_LAST_MODIFIED_BY:

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

2. For Label, enter:

   Last Modified By:
   
   

3. For Template, select Optional Label with Help.

4. For HTML Table Cell Attributes, enter:

   class="instructiontext"
   
   

5. Click Apply Changes.

Remove Unnecessary Validations

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

3. Click OK to confirm your selection.

4. Under Validations, select P7_CREATED_BY not null.

5. Click Delete.

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

3. On Identify Item Name and Display Position:

   1. For Item Name, enter:

      P7_PREV_PAGE
      
      

   2. For Region, select Issue Identification.

   3. Click Next.

4. Click Create Item.

   Next, edit the Cancel button.

5. Under Buttons, select Cancel.

6. Scroll down to Optional URL Redirect.

7. In Page, enter:

   &P7_PREV_PAGE.
   
   

   Note the period at the end.

8. Click Apply Changes.

   Next, edit the branch.

9. Under Branching, select the After Processing branch.

10. Under Branch Action, enter the following in Page:

    &P7_PREV_PAGE.
    
    

11. Click Apply Changes.

Add Functionality to Support Adding Multiple Issues Sequentially

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

To add a new button:

1. Under the Buttons section, 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 will also reset 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. That will make it 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 Branching, click the Create icon.

2. On Identify Branch Point and Type, accept the defaults and click Next.

3. On Identify Target:

   1. For Page, enter 7.

   2. For Clear Cache, enter 7.

   3. For Set these items, enter:

      P7_PREV_PAGE
      
      

   4. For With these values, enter:

      &P7_PREV_PAGE.
      
      

   5. Click Next.

4. On Identify Branch Conditions:

   1. For Sequence, enter 0.

   2. For When Button Pressed, select CREATE_AGAIN.

5. Click Create Branch.

6. Under Branching, select the newly created branch.

7. Under Branch 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 the illustration iss_edit1.gif
   
   

   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 will 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 the Regions section, click the Create icon.

3. For Select a common region type, accept the default of HTML and click Next.

4. For Region Type, select HTML and click Next.

5. On Identify Region 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. Under 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 Q to the left of Issues.

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. Under the List of Values section, 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. Under 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. Under 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.

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

To alter the report display:

1. For ISSUE_ID, delete the Heading Edit.

2. For ISSUE_SUMMARY, change Heading to Summary.

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

4. For all columns except ISSUE_ID, check Sort.

5. For ISSUE_SUMMARY, select 1 for Sort Sequence.

6. Scroll down to Layout and Pagination:

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

   2. For Number of Rows, enter 5.

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

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

   No issues found.
   
   

9. Click Apply Changes.

Add Support of Filtering

Although the report now displays nicely, it does not support filtering by the end user. To add this functionality, you will first create items that will 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 the Regions section, click the Create icon.

2. For Select a common region type, accept the default of HTML and click Next.

3. For Region Type, select HTML and click Next.

4. On Identify Region 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 Choose Item Type, select Select List and click Next.

3. For Pick 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_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 Identify Item Attributes, accept the defaults and click Next.

7. For Default, enter:

   -1
   
   

8. Click Create Item.

To create an item for Assigned To.

1. Under Items, click the Create icon.

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

3. For Pick 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_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 Identify Item Attributes, accept the defaults and click Next.

7. For Default, enter:

   -1
   
   

8. Click Create Item.

To create an item for Status.

1. Under Items, click the Create icon.

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

3. For Pick 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_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 Identify Item Attributes, accept the defaults and click Next.

7. For Default, enter:

   -1
   
   

8. Click Create Item.

To create an item for Priority.

1. Under Items, click the Create icon.

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

3. For Pick 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 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. For Default, enter:

   -1
   
   

8. Click Create Item.

To create an item for Related Project.

1. Under Items, click the Create icon.

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

3. For Pick 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. For Default, enter:

   -1
   
   

8. Click Create Item.

Next, create a Go button. This button will enable the user to execute the query once they select report parameters. Buttons can be created in region positions or displayed among items. For this exercise, the Go button will display just to the right of the last report parameter so you will create it among the region's items.

To create Go button:

1. Under Buttons, click the Create icon.

2. For Select a region for the button, select Issue Report Parameters and click Next.

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

4. For Button Name, enter P6_GO.

5. For Button Style, select Template Based Button.

6. For Template, select Button.

7. Click Create Button.

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. Under the Items section, click the heading Items.

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 WHERE clause of the query. One approach would be add the following WHERE clause. For example:

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')

Although this is a valid approach, the query will execute faster if you generate it with the WHERE clause that needs it. To accomplish this, turn 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 the Regions section, select Issues.

2. For Type, select SQL Query (Pl/Sql Function Body Returning Sql Query).

3. For Region Source, enter 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 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).

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 Builder home page.

2. Click Create Page.

3. Select Page with Component and click Next.

4. Select Form and click Next.

5. Select Tabular Form and click Next.

6. For Table/View Owner, select the appropriate schema.

   Since the purpose of this form is to enable users to assign issues, it is assumed they will only update existing records, not create or delete issues.

7. To enforce this assumption, select Update Only from the Allowed Operations list and click Next.

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

9. For Select Column(s), select the following columns and click Next:

   • ISSUE_SUMMARY

   • IDENTIFIED_BY

   • IDENTIFIED_DATE

   • RELATED_PROJECT

   • ASSIGNED_TO

   • STATUS

   • PRIORITY

10. For Primary Key Column 1, accept the default ISSUE_ID and click Next.

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

12. For Updatable Column(s), select the following and click Next:

    • RELATED_PROJECT

    • ASSIGNED_TO

    • STATUS

    • PRIORITY

13. On Identify Page and Region Attributes:

    1. For Page, enter 8.

    2. For Page Name, enter Assign Issues.

    3. For Region Title, enter Assign Issues.

    4. Click Next.

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

15. On Button Labels:

    1. For Cancel Button Label, accept the default.

    2. For Submit Button Label, enter Apply Changes.

    3. Click Next.

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

17. Review your selections and click Finish.

Add Lists of Values

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

To add lists of values:

1. From the Success page, select Edit Page.

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

2. Under Regions, select Assign Issues.

3. In Region Source, enter the following:

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

To edit report attributes:

1. Select the Report Attributes tab.

2. Under Report Column Attributes:

   1. For ISSUE_SUMMARY, edit the existing Heading to read:

      Summary
      
      

   2. For all columns except ISSUE_ID, select Sort.

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

3. Click the Edit icon to the left of IDENTIFIED_BY and edit the following attributes:

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

   2. Under List of Values, select PEOPLE from the Named LOV list.

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

4. Edit the following attributes for IDENTIFIED_DATE:

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

   2. Click the Next button (>) at the top of the page to navigate to the next column.

5. Edit the following attributes for RELATED_PROJECT:

   1. Scroll down to Tabular Form Element. From Display As, select Select List (Named LOV).

   2. Under List of Values, select PROJECTS from the Named LOV list.

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

6. Edit the following attributes for ASSIGNED_TO:

   1. Scroll down to Tabular Form Element. From Display As, select Select List (Named LOV).

   2. Under List of Values:

      • From the Named LOV list, select PEOPLE.

      • For Display Null, select Yes.

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

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

7. Edit the following attributes for STATUS:

   1. Scroll down to Tabular Form Element. From Display As, select Select List (Named LOV).

   2. Under List of Values select STATUS from the Named LOV list.

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

8. Edit the following attributes for PRIORITY:

   1. Scroll down to Tabular Form Element. From Display As, select Select List (Named LOV).

   2. Under List of Values:

      • From Named LOV, select PRIORITIES.

      • For Display Null, select Yes.

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

   3. Click Apply Changes.

   The Report Attributes page appears.

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

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

    No Unassigned Issues.
    
    

11. Click Apply Changes.

The wizard created an unnecessary Cancel button.

To delete the Cancel button:

1. On the Page Definition for page 8, select the CANCEL button.

2. Click Delete.

3. Click OK to confirm your selection.

The tabular form is now complete. To view the new form, click the Run Page icon. (See Figure 10-11.)

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 a New Region

To create a new region to display above the report:

1. From the Success page, click Edit Page.

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

2. Under the Regions section, click the Create icon.

3. For Select a common region type, accept the default of HTML and click Next.

4. On Region Type, select HTML and click Next.

5. On Identify Region Attributes:

   1. For Title, enter Issue Summary Report Parameters.

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

   3. For Sequence, enter 5.

   4. Click Next.

6. Click Create Region.

Create the Project Item

To create the Project item:

1. Under Items, click the Create icon.

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

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

4. On Identify Item Name and Display Position:

   1. For Item Name, enter P9_PROJECT.

   2. For Region, select Issue Summary Report Parameters.

   3. Click Next.

5. On Identify List of Values:

   1. For Named LOV, select PROJECTS.

   2. For Null Text, enter:

      - Select -
      
      

   3. For Null Value, enter -1.

   4. Click Next.

6. On Identify 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 execute the query:

1. Under Buttons, click the Create icon.

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

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

4. On Identify Button within Items Attributes:

   1. For Button Name, enter P9_GO.

   2. For Button Style, select Template Based Button.

   3. For Template, select Button.

5. Click Create Button.

Edit Headings and Report Settings

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

To edit the headings and report settings:

1. Under Regions, select the Q adjacent to Issue Summary by Project.

2. For Headings Type, select Custom.

3. Under Report 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. Under the Layout and Pagination section:

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

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

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

   1. Scroll down to Conditional Display.

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

   3. In Expression 1, enter P9_PROJECT.

   4. For Expression 2, enter -1.

6. Click Apply Changes.

Create a Query to Retrieve Assignments

To create a query to retrieve assignments by status.

1. Under the Regions section, click the Create icon.

2. For Select a common region type, select Report and click Next.

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

4. On Identify Region Attributes:

   1. For Title, enter Assignments by Status.

   2. For Column, select 2.

   3. Click Next.

5. On Page Region Source:

   1. In Enter SQL Query, enter:

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

   2. For Rows Per Page, enter 20.

   3. For Break Columns, select Column 1.

   4. Click Next.

6. On Identify Display Conditions:

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

   2. In Expression 1 enter:

      P9_PROJECT
      
      

   3. For Expression 2 enter -1.

7. Click Create Region.

To edit headings and report settings:

1. Under Regions, select Q adjacent to Assignments by Status.

2. For Headings Type, select Custom.

3. For PERSON_NAME, change Heading to Assigned To.

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

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

   No Issues found.
   
   

6. Click Apply Changes.

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. Your report should resemble Figure 10-12.

Create a Calendar

To create a calendar of target resolution dates:

1. Navigate to the Application Builder home page.

2. Click Create Page.

3. Select Page with Component and click Next.

4. Select Calendar and click Next.

5. Select SQL Calendar and click Next.

6. On Identify Page Attributes:

   1. For Page, enter 11.

   2. For Page Name and Region Name, enter Target Resolution Dates.

   3. Click Next.

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

8. For Enter SQL Query, enter the following and click Next:

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

   Note that:

   • The target_resolution_date is the date on which the issue will display

   • The issue_summary is concatenated with the person assigned

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

9. On Identify Calendar Columns:

   1. For Date Column, select TARGET_RESOLUTION_DATE.

   2. For Display Column, select DISP.

   3. Click Next.

10. Review your selections and click Finish.

Add an Item to Support Project Look Up

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

To add an item to support project look up:

1. From the Success page, select Edit Page.

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

2. Under Items, click the Create icon.

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

4. For Pick Select List Control Type, accept the default of Select List and click Next.

5. For Item Name, enter P11_PROJECT and click Next.

6. On Identify List of Values:

   1. For Named LOV, select PROJECTS.

   2. For Null Text, enter:

      - All Projects -
      
      

   3. For Null Value, enter:

      -1
      
      

   4. Click Next.

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

8. For Default, enter:

   -1
   
   

9. Click Create Item.

Create a Go Button

To create a Go button to execute the query:

1. Under Buttons, click the Create icon.

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

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

4. On Identify Button within Items Attributes:

   1. For Button Name, enter P11_GO.

   2. For Button Style, select Template Based Button.

   3. For Template, select Button.

5. Click Create Button.

Modify Calendar Attributes

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

To modify the Calendar Attributes:

1. Under Regions, select CAL to the left of Target Resolution Dates.

2. Scroll down to Column Link:

   1. For Page, enter 7.

   2. For Clear Cache, enter 7.

   3. For Set these items, enter:

      P7_ISSUE_ID,P7_PREV_PAGE
      
      

   4. For With these values, enter:

      #ISSUE_ID#,11
      
      
      

3. Select the Region Definition tab.

4. Scroll down to Header and Footer Text.

5. Enter the following in Region Footer:

   This excludes Closed issues.
   
   

6. Click Apply Changes.

To see your newly created calendar, click the Run Page icon. Your report should resemble Figure 10-14. Note that you can click the text displayed for an issue to display the Edit Issue page. To return to the calendar, click Cancel.

Create List Entries

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

To add a list item for Assign Issues:

1. Click Create List Entry.

2. For List, select Main Menu.

3. For List Entry Label, enter Assign Issues.

4. Under the Target section:

   1. For Page, select 8.

   2. Select reset pagination for this page.

5. Click Create.

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

To add list items for each of the other reports in your application:

1. Click Create List Entry.

2. To define list item attributes for Issues:

   1. For List, select Main Menu.

   2. For Sequence, enter 20.

   3. For List Entry Label, enter Issues.

   4. Under the Target section:

      • For Page, select 6.

      • Select reset pagination for this page.

      • For Clear Cache, enter 6.

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

3. Click Create and Create Another.

4. To define list item attributes for Issue Summary:

   1. For List, select Main Menu.

   2. For Sequence, enter 30.

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

   4. Under the Target section:

      • For Page, select 9.

      • Select reset pagination for this page.

      • For Clear Cache, enter 9.

5. Click Create and Create Another.

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

   1. For List, select Main Menu.

   2. For Sequence, enter 40.

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

7. Click Create and Create Another.

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

   1. For List, select Main Menu.

   2. For Sequence, enter 50.

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

   4. Under the Target section:

      • For Page, select 11.

      • Select reset pagination for this page.

9. Click Create and Create Another.

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

    1. For List, select Main Menu.

    2. For Sequence, enter 60.

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

    4. Under Target, select 12 for Page.

11. Click Create.

Include the List on the Home Page

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

To change the page template on the home page:

1. Click the Edit Page icon.

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

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

3. Click Edit Attributes.

   Page Attributes for page 1 appears.

4. Locate Primary Display Attributes.

5. From the Page Template list, select 10. No Tabs with Sidebar.

6. Click Apply Changes.

Next, create a region to contain your menu.

To create a a new region:

1. Under Regions, click the Create icon.

2. For Select a common region type, select List and click Next.

3. In Identify Region Attributes:

   1. For Title, enter Menu.

   2. For Region Template, select No Template.

   3. For Display Point, select Page Template Region Position 2 (or select the quick link [Pos. 2].

   4. Click Next.

4. For List, select Main Menu.

5. Click Create List Region.

Create the Authorization Scheme

To create the authorization scheme:

1. Navigate to the Oracle HTML DB home page.

2. Click Application Builder.

3. Click Shared Components.

4. Under Security, select Authorization.

5. Click Create.

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

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

   USER_CEO_OR_MANAGER
   
   

8. Under Authorization Scheme:

   1. For Scheme Type, select Exists SQL Query.

   2. In Expression 1, enter:

      SELECT '1'
        FROM ht_people
       WHERE (upper(person_email) = UPPER(:APP_USER) AND
              person_role IN ('CEO','Manager'))
          OR (UPPER(:APP_USER) = 'HOWTO')
      
      

   3. For Identify error message displayed when scheme violated, enter:

      You are not authorized to access this function.
      
      

9. Scroll down to Evaluation Point. For Validate authorization scheme, select Once per session.

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

10. Click Create.

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

Associate Objects on the Projects Report

To associate the authorization scheme with the Projects report:

1. Click the Edit Page icon.

2. In Page, enter 2 and click Go.

   The Page Definition for page 2, Projects, appears.

3. Under Regions, select Q to the left of Projects.

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

5. Under Authorization, select the Authorization Scheme USER_CEO_OR_MANAGER.

6. Click Apply Changes.

7. Click Cancel.

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

1. On the Page Definition for page 2, select the CREATE button

2. Under Authorization, select the Authorization Scheme USER_CEO_OR_MANAGER.

3. Click Apply Changes.

Associate Objects with the Create/Edit Report

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

1. Navigate to page 3 by clicking the Next Page (>) icon.

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

2. Click Edit Attributes.

3. Under Security, select the Authorization Scheme USER_CEO_OR_MANAGER.

4. Click Apply Changes.

Associate Objects with the People Report

To associate the authorization scheme with the People report.

1. Navigate to page 4 by clicking the Next Page (>) icon.

   The Page Definition for page 4, People, appears.

2. Under Regions, select Q to the left of People.

3. Click the edit link to the left of PERSON_ID.

4. Under Authorization, select the Authorization Scheme USER_CEO_OR_MANAGER.

5. Click Apply Changes.

6. Click Cancel.

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

1. On the Page Definition for page 5, select the CREATE button

2. Under Authorization, select the Authorization Scheme USER_CEO_OR_MANAGER.

3. Click Apply Changes.

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

1. Navigate to page 5.

   The Page Definition for page 5, Create/Edit Person Information, appears.

2. Click Edit Attributes.

3. Under Security, select the Authorization Scheme USER_CEO_OR_MANAGER.

4. Click Apply Changes.

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

Create the Authorization Scheme

To create the authorization scheme:

1. Navigate to the Application Builder home page.

2. Select Shared Components.

3. Under Security, select Authorization.

4. Click Create.

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

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

   P7_ASSIGNED_OR_PROJECT_LEAD
   
   

7. Under Authorization Scheme:

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

   2. For Expression 1, enter:

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

   3. For Identify error message displayed when scheme violated, enter:

      This issue is not assigned to you, nor are you the Project Lead. Therefore you are not authorized to modify the data.
      
      

8. Scroll down to Evaluation Point. For Validate authorization scheme, select Once per page view.

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

9. Click Create.

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

Associate Objects with the Create Edit Issues Report

To associate the authorization scheme with buttons and processes:

1. Click the Edit Page icon.

2. Navigate to page 7. Enter 7 in the Page field and click Go.

3. Under Buttons, select DELETE.

   1. Under Authorization, select the Authorization Scheme P7_ASSIGNED_OR_PROJECT_LEAD.

   2. Click Apply Changes.

4. Under Buttons, select SAVE.

   1. Under Authorization, select the Authorization Scheme P7_ASSIGNED_OR_PROJECT_LEAD.

   2. Click Apply Changes.

5. Under Processes, select Process Row of HT_ISSUES.

   1. Under Authorization, select the Authorization Scheme P7_ASSIGNED_OR_PROJECT_LEAD.

   2. Click Apply Changes.

Create an HTML Region

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

To create a new region:

1. Under Regions, click the Create icon.

2. For Select a common region type, accept the default HTML and click Next.

3. On Region Type, select HTML and click Next.

4. For Identify Region Attributes:

   1. For Title, enter Not Authorized.

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

   3. Click Next.

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

   You are not authorized to modify the data for this issue because<br>you are not the Project Lead nor is the issue assigned to you.
   
   

6. For Authorization Scheme, select {Not}P7_ASSIGNED_OR_PROJECT_LEAD. This will make the region only display when the Authorization Scheme fails.

7. Click Create Region.

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

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