Edit D:\app\Administrator\product\11.2.0\dbhome_1\apex\images\doc\tutorial\issue_track.htm
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <head> <!-- Run date = February 23, 2005 16:58:38 --> <meta name="Generator" content=" Oracle DARB XHTML converter - Version 4.1.3 Build 116" /> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <meta http-equiv="Content-Script-Type" content="text/javascript" /> <title> How to Build and Deploy an Issue Tracking Application </title> <link rel="stylesheet" type="text/css" href="blafdoc.css" title="default" /> <link rel="stylesheet" type="text/css" href="darbbook.css" title="default" /> <link rel="prev" href="javascript.htm" title="Previous" /> <link rel="next" href="scripts.htm" title="Next" /> <link rel="up" href="toc.htm" title="Up" /> <link rel="contents" href="toc.htm" title="Contents" /> <link rel="copyright" href="./support/html/cpyr.htm" title="Copyright" /> <link rel="index" href="index.htm" title="Index" /> </head> <body> <div class="header"> <a id="top" name="top"></a> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> <tr> <td align="left" valign="top"> <b>Oracle® HTML DB Guide to Building Applications </b> <br /> <b>Release 2.0 </b> </td> <td valign="bottom" align="right"> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="225"> <tr><td> </td> <td align="center" valign="top"><a href="toc.htm"><img src="./support/gifs/toc.gif" alt="Go To Table Of Contents" border="0" /><br /><span class="icon">Contents</span></a></td> </table> </td> </tr> </table> <hr /> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100"> <tr> <td align="center"> <a href="javascript.htm"> <img src="./support/gifs/leftnav.gif" alt="Previous" border="0" /><br /><span class="icon">Previous</span></a> </td> <td align="center"> <a href="scripts.htm"> <img src="./support/gifs/rightnav.gif" alt="Next" border="0" /><br /><span class="icon">Next</span></a> </td> <td> </td> </tr> </table> </div><!-- class="header" --> <div class="IND"> <!-- End Header --><a id="BJFJFCBF" name="BJFJFCBF"></a><a id="sthref197" name="sthref197"></a> <h1><a name="HTMAD010"></a>How to Build and Deploy an Issue Tracking Application </h1> <p>Storing information in an Oracle database organizes it into tables that group similar information together and removes redundancies. Using the Oracle HTML DB development environment, you can quickly build an application that enables a user to view and update information stored in an Oracle Database. </p> <p>This tutorial describes how to use Oracle HTML DB to create and deploy an application that tracks the assignment, status, and progress of issues related to a project. </p> <div align="center"> <div class="inftblNote"> <br /><table class = "Note oac_no_warn" summary="This is a layout table to format a note" title="This is a layout table to format a note" dir="ltr" border="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <tbody> <tr> <td align="left"> <p class="notep1">Note:</p>This tutorial takes approximately four to five hours to complete. It is recommended that you read through the entire document first to become familiar with the material before you attempt specific exercises. </td> </tr> </tbody> </table><br /> </div><!-- class="inftblNote --> </div> <p class="subhead2">Topics:</p> <ul> <li type="disc"> <p><a href="#BJFFJCAE">Business Scenario</a> </p> </li> <li type="disc"> <p><a href="#BJFHFJBI">Designing the Database Objects</a> </p> </li> <li type="disc"> <p><a href="#BJFBEAEI">Implementing Database Objects</a> </p> </li> <li type="disc"> <p><a href="#BABBJHGB">Loading Demonstration Data</a> </p> </li> <li type="disc"> <p><a href="#BABHAFFD">Building a Basic User Interface</a> </p> </li> <li type="disc"> <p><a href="#BABDAACH">Adding Advanced Features</a> </p> </li> <li type="disc"> <p><a href="#BABBGDAA">Deploying Your Application</a> </p> </li> </ul><a id="BJFFJCAE" name="BJFFJCAE"></a> <div class="sect1"> <!-- infolevel=all infotype=General --><a id="sthref198" name="sthref198"></a> <h2>Business Scenario </h2> <p>Effective project management is the key to completing any project on time and within budget. Within every project there are always multiple issues that need to be tracked, prioritized, and dealt with. </p> <p>The MRVL Company has several projects that must be completed on time for the company to be profitable. Missing deadlines for any of the projects will cause the company to lose money. The project leads are tracking issues in several different ways. Some individuals are manually tracking issues in notebooks, others are tracking issues in text documents, and other managers are using spreadsheets. </p> <p>By creating a hosted application in Oracle HTML DB, each project lead can easily record and track issues in one central location. Not only will everyone have access to just the data they need, but having the data stored in one location, will make it easier for management to determine if any critical issues are not being addressed. </p><a id="BJFFCCGC" name="BJFFCCGC"></a> <div class="sect2"> <!-- infolevel=all infotype=General --><a id="sthref199" name="sthref199"></a> <h3>Planning and Project Analysis </h3> <p>Before any beginning development on an application, you first need to define requirements that describe what the application does. Once defined, you can turn these requirements into a database design and an outline that describes how the user interface accepts and presents data. </p> <p>For this business scenario, the project leads establish requirements that define what information needs to be tracked, how security will work, what data management functions will be included, and how data will be presented to users. </p> <p class="subhead2">Topics:</p> <ul> <li type="disc"> <p><a href="#BABHEHEF">Necessary Data</a> </p> </li> <li type="disc"> <p><a href="#BABBGBJG">Requested Security</a> </p> </li> <li type="disc"> <p><a href="#BABEDCHD">Data Management Functions</a> </p> </li> <li type="disc"> <p><a href="#BABIACJB">Data Presentation Functions</a> </p> </li> <li type="disc"> <p><a href="#BABJAEGC">Special Functions</a> </p> </li> </ul><a id="BABHEHEF" name="BABHEHEF"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref200" name="sthref200"></a> <h4>Necessary Data </h4> <p>Currently, each project lead tracks information slightly differently. Together, everyone agrees that the application should include the following information: </p> <ul> <li type="disc"> <p>Summary of the issue </p> </li> <li type="disc"> <p>Detailed description of the issue </p> </li> <li type="disc"> <p>Who identified the issue </p> </li> <li type="disc"> <p>The date the issue was identified </p> </li> <li type="disc"> <p>Which project the issue is related to </p> </li> <li type="disc"> <p>Who the issue is assigned to </p> </li> <li type="disc"> <p>A current status of the issue </p> </li> <li type="disc"> <p>Priority of the issue </p> </li> <li type="disc"> <p>Target resolution date </p> </li> <li type="disc"> <p>Actual resolution date </p> </li> <li type="disc"> <p>Progress report </p> </li> <li type="disc"> <p>Resolution summary </p> </li> </ul> </div><!-- class="sect3" --> <a id="BABBGBJG" name="BABBGBJG"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref201" name="sthref201"></a> <h4>Requested Security </h4> <p>Because the project leads were concerned about everyone having access to all the information, they agree upon the following access rules: </p> <ul> <li type="disc"> <p>Each team member and project lead is only assigned to one project at a time </p> </li> <li type="disc"> <p>Each team member and project lead must be assigned to a project </p> </li> <li type="disc"> <p>Managers are never assigned to a specific project </p> </li> <li type="disc"> <p>Only managers can define and maintain projects and people </p> </li> <li type="disc"> <p>Everyone can enter new issues </p> </li> <li type="disc"> <p>Once assigned, only the person assigned or a project lead can change data about the issue </p> </li> <li type="disc"> <p>Management needs views that summarize the data without access to specific issue details </p> </li> </ul> </div><!-- class="sect3" --> <a id="BABEDCHD" name="BABEDCHD"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref202" name="sthref202"></a> <h4>Data Management Functions </h4> <p>Next, the project leads determine how information will be entered into the system. In this case, users must be able to: </p> <ul> <li type="disc"> <p>Create issues </p> </li> <li type="disc"> <p>Assign issues </p> </li> <li type="disc"> <p>Edit issues </p> </li> <li type="disc"> <p>Create projects </p> </li> <li type="disc"> <p>Maintain projects </p> </li> <li type="disc"> <p>Create people </p> </li> <li type="disc"> <p>Maintain people information </p> </li> <li type="disc"> <p>Maintain project assignments </p> </li> </ul> </div><!-- class="sect3" --> <a id="BABIACJB" name="BABIACJB"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref203" name="sthref203"></a> <h4>Data Presentation Functions </h4> <p>Once the data is entered into the application, users will need to view the data. The team decides they initially need to be able to view the following: </p> <ul> <li type="disc"> <p>All issues by project </p> </li> <li type="disc"> <p>Open issues by project </p> </li> <li type="disc"> <p>Overdue issues, by project and for all </p> </li> <li type="disc"> <p>Recently opened issues </p> </li> <li type="disc"> <p>Unassigned issues </p> </li> <li type="disc"> <p>Summary of issues by project, for managers </p> </li> <li type="disc"> <p>Resolved issues by month identified </p> </li> <li type="disc"> <p>Issue resolution dates displayed on a calendar </p> </li> <li type="disc"> <p>Days to Resolve Issues by person </p> </li> </ul> </div><!-- class="sect3" --> <a id="BABJAEGC" name="BABJAEGC"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref204" name="sthref204"></a> <h4>Special Functions </h4> <p>Finally, the project leads determine the application must support the following special functions: </p> <ul> <li type="disc"> <p>Notify people when an issue is assigned to them </p> </li> <li type="disc"> <p>Notify the project lead when any issue becomes overdue </p> </li> </ul> </div><!-- class="sect3" --> </div><!-- class="sect2" --> </div><!-- class="sect1" --> <a id="BJFHFJBI" name="BJFHFJBI"></a> <div class="sect1"> <!-- infolevel=all infotype=General --><a id="sthref205" name="sthref205"></a> <h2>Designing the Database Objects </h2> <p>Once you have defined the database object requirements, the next step is to turn these requirements in a database design and an outline that describes how the user interface accepts and presents data. In this step you need to think about how information should be organized in the tables in the underlying database. Given the requirements described <a href="#BJFFCCGC">"Planning and Project Analysis"</a>, for this project you need to create three tables: </p> <ul> <li type="disc"> <p><code>Projects</code> tracks all current projects </p> </li> <li type="disc"> <p><code>People</code> contains information about who can be assigned to handle issues </p> </li> <li type="disc"> <p><code>Issues</code> tracks all the information about an issue, including the project to which it is related and the person assigned to rectify the issue </p> </li> </ul> <p>Be aware that you will also need create additional database objects, such as sequences and triggers, to support the tables. System generated primary keys will be used for all tables so that all the data can be edited without needing to implement a cascade update. </p> <p class="subhead2">Topics:</p> <ul> <li type="disc"> <p><a href="#BABIIBBF">About the Projects Table</a> </p> </li> <li type="disc"> <p><a href="#BABBHFAF">About the People Table</a> </p> </li> <li type="disc"> <p><a href="#BABBBHHB">About the Issues Table</a> </p> </li> </ul><a id="BABIIBBF" name="BABIIBBF"></a> <div class="sect2"> <!-- infolevel=all infotype=General --><a id="sthref206" name="sthref206"></a> <h3>About the Projects Table </h3> <p>Each project must have a name and include a project start date as well as the projected end date. These dates will be used to help determine if any outstanding issues are jeopardizing the end date. <a href="#BJFJDJIE">Table 10-1</a> describes the columns to be included in the Projects table. </p> <div class="tblFormalWide"> <a id="sthref207" name="sthref207"></a><a id="BJFJDJIE" name="BJFJDJIE"></a> <p class="titleintable">Table 10-1 Project Table Details</p> <table class="FormalWide" title="Project Table Details" summary="This table describes the columns that to be included in the Projects table." dir="ltr" border="1" width="100%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <thead> <tr align="left" valign="top"> <th align="left" valign="bottom" id="r1c1-t3">Column Name </th> <th align="left" valign="bottom" id="r1c2-t3">Type </th> <th align="left" valign="bottom" id="r1c3-t3">Size </th> <th align="left" valign="bottom" id="r1c4-t3">Not Null? </th> <th align="left" valign="bottom" id="r1c5-t3">Constraints </th> <th align="left" valign="bottom" id="r1c6-t3">Description </th> </tr> </thead> <tbody> <tr align="left" valign="top"> <td align="left" id="r2c1-t3" headers="r1c1-t3">project_id </td> <td align="left" headers="r2c1-t3 r1c2-t3">integer </td> <td align="left" headers="r2c1-t3 r1c3-t3">n/a </td> <td align="left" headers="r2c1-t3 r1c4-t3">Yes </td> <td align="left" headers="r2c1-t3 r1c5-t3">Primary key </td> <td align="left" headers="r2c1-t3 r1c6-t3">A unique numeric identification number for each project. <p>Populated by a sequence using a trigger. </p> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r3c1-t3" headers="r1c1-t3">project_name </td> <td align="left" headers="r3c1-t3 r1c2-t3">varchar2 </td> <td align="left" headers="r3c1-t3 r1c3-t3">100 </td> <td align="left" headers="r3c1-t3 r1c4-t3">Yes </td> <td align="left" headers="r3c1-t3 r1c5-t3">Unique key </td> <td align="left" headers="r3c1-t3 r1c6-t3">A unique alphanumeric name for the project. </td> </tr> <tr align="left" valign="top"> <td align="left" id="r4c1-t3" headers="r1c1-t3">start_date </td> <td align="left" headers="r4c1-t3 r1c2-t3">date </td> <td align="left" headers="r4c1-t3 r1c3-t3">n/a </td> <td align="left" headers="r4c1-t3 r1c4-t3">Yes </td> <td align="left" headers="r4c1-t3 r1c5-t3">None </td> <td align="left" headers="r4c1-t3 r1c6-t3">The project start date. </td> </tr> <tr align="left" valign="top"> <td align="left" id="r5c1-t3" headers="r1c1-t3">target_end_date </td> <td align="left" headers="r5c1-t3 r1c2-t3">date </td> <td align="left" headers="r5c1-t3 r1c3-t3">n/a </td> <td align="left" headers="r5c1-t3 r1c4-t3">Yes </td> <td align="left" headers="r5c1-t3 r1c5-t3">None </td> <td align="left" headers="r5c1-t3 r1c6-t3">The targeted project end date. </td> </tr> <tr align="left" valign="top"> <td align="left" id="r6c1-t3" headers="r1c1-t3">actual_end_date </td> <td align="left" headers="r6c1-t3 r1c2-t3">date </td> <td align="left" headers="r6c1-t3 r1c3-t3">n/a </td> <td align="left" headers="r6c1-t3 r1c4-t3">No </td> <td align="left" headers="r6c1-t3 r1c5-t3">None </td> <td align="left" headers="r6c1-t3 r1c6-t3">The actual end date. </td> </tr> </tbody> </table><br /> </div><!-- class="tblFormalWide" --> </div><!-- class="sect2" --> <a id="BABBHFAF" name="BABBHFAF"></a> <div class="sect2"> <!-- infolevel=all infotype=General --><a id="sthref208" name="sthref208"></a> <h3>About the People Table </h3> <p>Each person will have a defined name and role. Project leads and team members will also have an assigned project. To tie the current user to their role within the organization, e-mail addresses will be used for user names. </p> <p><a href="#BJFCGIBC">Table 10-2</a> describes the columns that will be included in the People table. </p> <div class="tblFormalWide"> <a id="sthref209" name="sthref209"></a><a id="BJFCGIBC" name="BJFCGIBC"></a> <p class="titleintable">Table 10-2 People Table Details</p> <table class="FormalWide" title="People Table Details" summary="This table describes the columns that will be included in the People table." dir="ltr" border="1" width="100%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <thead> <tr align="left" valign="top"> <th align="left" valign="bottom" id="r1c1-t4">Column Name </th> <th align="left" valign="bottom" id="r1c2-t4">Type </th> <th align="left" valign="bottom" id="r1c3-t4">Size </th> <th align="left" valign="bottom" id="r1c4-t4">Not Null? </th> <th align="left" valign="bottom" id="r1c5-t4">Constraints </th> <th align="left" valign="bottom" id="r1c6-t4">Description </th> </tr> </thead> <tbody> <tr align="left" valign="top"> <td align="left" id="r2c1-t4" headers="r1c1-t4">person_id </td> <td align="left" headers="r2c1-t4 r1c2-t4">integer </td> <td align="left" headers="r2c1-t4 r1c3-t4">n/a </td> <td align="left" headers="r2c1-t4 r1c4-t4">Yes </td> <td align="left" headers="r2c1-t4 r1c5-t4">Primary key </td> <td align="left" headers="r2c1-t4 r1c6-t4">A numeric ID that identifies each user. <p>Populated by a sequence using a trigger. </p> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r3c1-t4" headers="r1c1-t4">person_name </td> <td align="left" headers="r3c1-t4 r1c2-t4">varchar2 </td> <td align="left" headers="r3c1-t4 r1c3-t4">100 </td> <td align="left" headers="r3c1-t4 r1c4-t4">Yes </td> <td align="left" headers="r3c1-t4 r1c5-t4">Unique key </td> <td align="left" headers="r3c1-t4 r1c6-t4">A unique name that identifies each user. </td> </tr> <tr align="left" valign="top"> <td align="left" id="r4c1-t4" headers="r1c1-t4">person_email </td> <td align="left" headers="r4c1-t4 r1c2-t4">varchar2 </td> <td align="left" headers="r4c1-t4 r1c3-t4">100 </td> <td align="left" headers="r4c1-t4 r1c4-t4">Yes </td> <td align="left" headers="r4c1-t4 r1c5-t4">None </td> <td align="left" headers="r4c1-t4 r1c6-t4">User e-mail address. </td> </tr> <tr align="left" valign="top"> <td align="left" id="r5c1-t4" headers="r1c1-t4">person_role </td> <td align="left" headers="r5c1-t4 r1c2-t4">varchar2 </td> <td align="left" headers="r5c1-t4 r1c3-t4">7 </td> <td align="left" headers="r5c1-t4 r1c4-t4">Yes </td> <td align="left" headers="r5c1-t4 r1c5-t4">Check constraint </td> <td align="left" headers="r5c1-t4 r1c6-t4">The role assigned to each user. </td> </tr> </tbody> </table><br /> </div><!-- class="tblFormalWide" --> <div align="center"> <div class="inftblNote"> <br /><table class = "Note oac_no_warn" summary="This is a layout table to format a note" title="This is a layout table to format a note" dir="ltr" border="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <tbody> <tr> <td align="left"> <p class="notep1">Note:</p>For the purposes of this exercise, this application has been simplified. People data is usually much more elaborate and is often pulled from a corporate Human Resource system. Also, people typically work on more than one project at a time. If the roles that are assigned to a person need to be dynamic, you would implement roles as a separate table with a foreign key back to the people table. </td> </tr> </tbody> </table><br /> </div><!-- class="inftblNote --> </div> </div><!-- class="sect2" --> <a id="BABBBHHB" name="BABBBHHB"></a> <div class="sect2"> <!-- infolevel=all infotype=General --><a id="sthref210" name="sthref210"></a> <h3>About the Issues Table </h3> <p>When the project leads defined their application requirements, they determined they needed to track specific issues assigned to a specific person. Issues will be included as a columns along with additional columns to provide an audit trail. The audit trail will track who created the issue, when it was created, as well as who last modified the issue and on what date that modification was made. </p> <p><a href="#BJFDGDBB">Table 10-3</a> describes the columns to be included in the Issues table. </p> <div class="tblFormalWide"> <a id="sthref211" name="sthref211"></a><a id="BJFDGDBB" name="BJFDGDBB"></a> <p class="titleintable">Table 10-3 Issue Table Details</p> <table class="FormalWide" title="Issue Table Details" summary="This table describes the columns to be included in the Issues table." dir="ltr" border="1" width="100%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <thead> <tr align="left" valign="top"> <th align="left" valign="bottom" id="r1c1-t6">Column Name </th> <th align="left" valign="bottom" id="r1c2-t6">Type </th> <th align="left" valign="bottom" id="r1c3-t6">Size </th> <th align="left" valign="bottom" id="r1c4-t6">Not Null? </th> <th align="left" valign="bottom" id="r1c5-t6">Constraints </th> <th align="left" valign="bottom" id="r1c6-t6">Description </th> </tr> </thead> <tbody> <tr align="left" valign="top"> <td align="left" id="r2c1-t6" headers="r1c1-t6">issue_id </td> <td align="left" headers="r2c1-t6 r1c2-t6">integer </td> <td align="left" headers="r2c1-t6 r1c3-t6">n/a </td> <td align="left" headers="r2c1-t6 r1c4-t6">Yes </td> <td align="left" headers="r2c1-t6 r1c5-t6">primary key </td> <td align="left" headers="r2c1-t6 r1c6-t6">A unique numeric ID that identifies an issue. <p>Populated by a sequence using a trigger </p> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r3c1-t6" headers="r1c1-t6">issue_summary </td> <td align="left" headers="r3c1-t6 r1c2-t6">varchar2 </td> <td align="left" headers="r3c1-t6 r1c3-t6">200 </td> <td align="left" headers="r3c1-t6 r1c4-t6">Yes </td> <td align="left" headers="r3c1-t6 r1c5-t6">None </td> <td align="left" headers="r3c1-t6 r1c6-t6">A brief summary of the issue. </td> </tr> <tr align="left" valign="top"> <td align="left" id="r4c1-t6" headers="r1c1-t6">issue_description </td> <td align="left" headers="r4c1-t6 r1c2-t6">varchar2 </td> <td align="left" headers="r4c1-t6 r1c3-t6">2000 </td> <td align="left" headers="r4c1-t6 r1c4-t6">No </td> <td align="left" headers="r4c1-t6 r1c5-t6">None </td> <td align="left" headers="r4c1-t6 r1c6-t6">A detailed description of the issue. </td> </tr> <tr align="left" valign="top"> <td align="left" id="r5c1-t6" headers="r1c1-t6">identified_by </td> <td align="left" headers="r5c1-t6 r1c2-t6">integer </td> <td align="left" headers="r5c1-t6 r1c3-t6">n/a </td> <td align="left" headers="r5c1-t6 r1c4-t6">Yes </td> <td align="left" headers="r5c1-t6 r1c5-t6">foreign key to People </td> <td align="left" headers="r5c1-t6 r1c6-t6">The user who identifies the issue. </td> </tr> <tr align="left" valign="top"> <td align="left" id="r6c1-t6" headers="r1c1-t6">identified_date </td> <td align="left" headers="r6c1-t6 r1c2-t6">date </td> <td align="left" headers="r6c1-t6 r1c3-t6">n/a </td> <td align="left" headers="r6c1-t6 r1c4-t6">Yes </td> <td align="left" headers="r6c1-t6 r1c5-t6">None </td> <td align="left" headers="r6c1-t6 r1c6-t6">The date the issue was identified </td> </tr> <tr align="left" valign="top"> <td align="left" id="r7c1-t6" headers="r1c1-t6">related_project </td> <td align="left" headers="r7c1-t6 r1c2-t6">integer </td> <td align="left" headers="r7c1-t6 r1c3-t6">n/a </td> <td align="left" headers="r7c1-t6 r1c4-t6">Yes </td> <td align="left" headers="r7c1-t6 r1c5-t6">foreign key to Projects </td> <td align="left" headers="r7c1-t6 r1c6-t6">Projects related to the issue. </td> </tr> <tr align="left" valign="top"> <td align="left" id="r8c1-t6" headers="r1c1-t6">assigned_to </td> <td align="left" headers="r8c1-t6 r1c2-t6">integer </td> <td align="left" headers="r8c1-t6 r1c3-t6">n/a </td> <td align="left" headers="r8c1-t6 r1c4-t6">No </td> <td align="left" headers="r8c1-t6 r1c5-t6">foreign key to People </td> <td align="left" headers="r8c1-t6 r1c6-t6">The person who owns this issue. </td> </tr> <tr align="left" valign="top"> <td align="left" id="r9c1-t6" headers="r1c1-t6">status </td> <td align="left" headers="r9c1-t6 r1c2-t6">varchar2 </td> <td align="left" headers="r9c1-t6 r1c3-t6">8 </td> <td align="left" headers="r9c1-t6 r1c4-t6">Yes </td> <td align="left" headers="r9c1-t6 r1c5-t6">check constraint </td> <td align="left" headers="r9c1-t6 r1c6-t6">The issue status. Automatically set to <code>Open</code> when new and set to Closed when actual resolution date entered </td> </tr> <tr align="left" valign="top"> <td align="left" id="r10c1-t6" headers="r1c1-t6">priority </td> <td align="left" headers="r10c1-t6 r1c2-t6">varchar2 </td> <td align="left" headers="r10c1-t6 r1c3-t6">6 </td> <td align="left" headers="r10c1-t6 r1c4-t6">No </td> <td align="left" headers="r10c1-t6 r1c5-t6">check constraint </td> <td align="left" headers="r10c1-t6 r1c6-t6">The priority of the issue. </td> </tr> <tr align="left" valign="top"> <td align="left" id="r11c1-t6" headers="r1c1-t6">target_resolution_date </td> <td align="left" headers="r11c1-t6 r1c2-t6">date </td> <td align="left" headers="r11c1-t6 r1c3-t6">n/a </td> <td align="left" headers="r11c1-t6 r1c4-t6">No </td> <td align="left" headers="r11c1-t6 r1c5-t6">None </td> <td align="left" headers="r11c1-t6 r1c6-t6">The target resolution date. </td> </tr> <tr align="left" valign="top"> <td align="left" id="r12c1-t6" headers="r1c1-t6">progress </td> <td align="left" headers="r12c1-t6 r1c2-t6">varchar2 </td> <td align="left" headers="r12c1-t6 r1c3-t6">2000 </td> <td align="left" headers="r12c1-t6 r1c4-t6">No </td> <td align="left" headers="r12c1-t6 r1c5-t6">None </td> <td align="left" headers="r12c1-t6 r1c6-t6">The progress of the issue. </td> </tr> <tr align="left" valign="top"> <td align="left" id="r13c1-t6" headers="r1c1-t6">actual_resolution_date </td> <td align="left" headers="r13c1-t6 r1c2-t6">date </td> <td align="left" headers="r13c1-t6 r1c3-t6">n/a </td> <td align="left" headers="r13c1-t6 r1c4-t6">No </td> <td align="left" headers="r13c1-t6 r1c5-t6">None </td> <td align="left" headers="r13c1-t6 r1c6-t6">Actual resolution date of the issue. </td> </tr> <tr align="left" valign="top"> <td align="left" id="r14c1-t6" headers="r1c1-t6">resolution_summary </td> <td align="left" headers="r14c1-t6 r1c2-t6">varchar2 </td> <td align="left" headers="r14c1-t6 r1c3-t6">2000 </td> <td align="left" headers="r14c1-t6 r1c4-t6">No </td> <td align="left" headers="r14c1-t6 r1c5-t6">None </td> <td align="left" headers="r14c1-t6 r1c6-t6">Resolution summary. </td> </tr> <tr align="left" valign="top"> <td align="left" id="r15c1-t6" headers="r1c1-t6">created_date </td> <td align="left" headers="r15c1-t6 r1c2-t6">date </td> <td align="left" headers="r15c1-t6 r1c3-t6">n/a </td> <td align="left" headers="r15c1-t6 r1c4-t6">Yes </td> <td align="left" headers="r15c1-t6 r1c5-t6">None </td> <td align="left" headers="r15c1-t6 r1c6-t6">Populated by a trigger. </td> </tr> <tr align="left" valign="top"> <td align="left" id="r16c1-t6" headers="r1c1-t6">created_by </td> <td align="left" headers="r16c1-t6 r1c2-t6">varchar2 </td> <td align="left" headers="r16c1-t6 r1c3-t6">60 </td> <td align="left" headers="r16c1-t6 r1c4-t6">Yes </td> <td align="left" headers="r16c1-t6 r1c5-t6">None </td> <td align="left" headers="r16c1-t6 r1c6-t6">User who create this issue. </td> </tr> <tr align="left" valign="top"> <td align="left" id="r17c1-t6" headers="r1c1-t6">last_modified_date </td> <td align="left" headers="r17c1-t6 r1c2-t6">date </td> <td align="left" headers="r17c1-t6 r1c3-t6">n/a </td> <td align="left" headers="r17c1-t6 r1c4-t6">No </td> <td align="left" headers="r17c1-t6 r1c5-t6">None </td> <td align="left" headers="r17c1-t6 r1c6-t6">Populated by a trigger. </td> </tr> </tbody> </table><br /> </div><!-- class="tblFormalWide" --> <div align="center"> <div class="inftblNote"> <br /><table class = "Note oac_no_warn" summary="This is a layout table to format a note" title="This is a layout table to format a note" dir="ltr" border="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <tbody> <tr> <td align="left"> <p class="notep1">Note:</p>A real-world application might need more extensive auditing. For example, you might need to track each change to the data rather than just the last change. Tracking each change to the data would require an additional table, linked to the issues table. If the valid priorities assigned to issues needed to be dynamic, you would need to add a separate table with a foreign key back to the issues table. </td> </tr> </tbody> </table><br /> </div><!-- class="inftblNote --> </div> </div><!-- class="sect2" --> </div><!-- class="sect1" --> <a id="BJFBEAEI" name="BJFBEAEI"></a> <div class="sect1"> <!-- infolevel=all infotype=General --><a id="sthref212" name="sthref212"></a> <h2>Implementing Database Objects </h2> <p>This first step in building an application is to create the database objects in the underlying database. You create database objects within a user account in the Oracle database. To create this application, you need to request a new workspace within the Oracle HTML DB development environment. An administrator creates (or provisions) a workspace within an initial schema that will house the application objects. </p> <p class="subhead2">Topics:</p> <ul> <li type="disc"> <p><a href="#BABHIADA">Request a New Workspace</a> </p> </li> <li type="disc"> <p><a href="#BABEHAEH">Build the Database Objects</a> </p> </li> </ul><a id="BABHIADA" name="BABHIADA"></a> <div class="sect2"> <!-- infolevel=all infotype=General --><a id="sthref213" name="sthref213"></a> <h3>Request a New Workspace </h3> <div align="center"> <div class="inftblNote"> <br /><table class = "Note oac_no_warn" summary="This is a layout table to format a note" title="This is a layout table to format a note" dir="ltr" border="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <tbody> <tr> <td align="left"> <p class="notep1">Note:</p>If you have a local installation of Oracle HTML DB, you may skip this section and use and existing workspace. </td> </tr> </tbody> </table><br /> </div><!-- class="inftblNote --> </div> <p>To request a workspace: </p> <ol type="1" start="1"> <li> <p>In a Web browser, navigate to the Oracle HTML DB Login page. To log in to the Oracle supported site of Oracle HTML DB development, go to: </p> <pre xml:space="preserve" class="oac_no_warn"><code><a href="http://htmldb.oracle.com/">http://htmldb.oracle.com/</a></code> </pre> <p>The Login page appears. </p> </li> <li> <p>Under Tasks, click <span class="bold">Request a Workspace</span>. </p> <p>The Request Service Wizard appears. </p> </li> <li> <p>Click <span class="bold">Continue</span> and follow the on-screen instructions. </p> <p>You will be notified through e-mail when your request has been approved and your workspace has been provisioned. </p> </li> </ol> </div><!-- class="sect2" --> <a id="BABEHAEH" name="BABEHAEH"></a> <div class="sect2"> <!-- infolevel=all infotype=General --><a id="sthref214" name="sthref214"></a> <h3>Build the Database Objects </h3> <p>There are several ways to create objects in Oracle HTML DB. You can: </p> <ul> <li type="disc"> <p><span class="bold">Run the Create Object Wizard.</span> This wizard walks you through the choices necessary to create the selected database object. To access the Create Object Wizard, click <span class="bold">Create Object</span> on the SQL Workshop home page. </p> </li> <li type="disc"> <p><span class="bold">Execute commands in SQL Command Processor.</span> Run SQL commands by typing or pasting them into the SQL Command Processor. To access SQL Command Processor, click SQL Command Processor on the SQL Workshop home page. </p> </li> <li type="disc"> <p><span class="bold">Upload a script.</span> Upload and script that contains all the necessary create object statements to the Script Repository. To access the Script Repository, click <span class="bold">Upload</span> on the Script Repository page. </p> </li> <li type="disc"> <p><span class="bold">Create script online.</span> Create a script online in the Script Repository. This is the method you will use to create the database objects for this exercise. </p> </li> </ul> <p>To build database objects by creating a script: </p> <ol type="1" start="1"> <li> <p>Log in to Oracle HTML DB. </p> </li> <li> <p>Click <span class="bold">SQL Workshop</span> on the Workspace home page. </p> </li> <li> <p>Under SQL Scripts, select <span class="bold">Scripts</span>. </p> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> <li> <p>For Script Name, enter <code>DDL for Issue Management Application</code> and click <span class="bold">Next</span>. </p> </li> <li> <p>In Script, copy and paste the DDL (data definition language) in <a href="scripts.htm#CBHCEIGH">"Create Application Database Objects DDL"</a>. </p> </li> <li> <p>Click <span class="bold">Create Script</span>. </p> </li> </ol> <p>To run the DDL for Issue Management Application script: </p> <ol type="1" start="1"> <li> <p>In the Scripts list, click <span class="bold">Run</span> in the Actions column adjacent to <code>DDL for Issue Management Application</code> script. </p> </li> <li> <p>From Parse As, select the schema owner that was created with your workspace or the existing schema owner you wish to use for this exercise. </p> </li> <li> <p>Click <span class="bold">Run Script</span>. </p> <p>The Summary Statistics page appears. No error should display for all commands. </p> </li> </ol> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref215" name="sthref215"></a> <h4>Viewing Created Database Objects </h4> <p>You can view database objects using the SQL Workshop Browse feature. </p> <p>To view database objects in SQL Workshop: </p> <ol type="1" start="1"> <li> <p>Click the <span class="bold">SQL Workshop</span> tab. </p> </li> <li> <p>Under Database Browser, click <span class="bold">Tables</span>. </p> </li> <li> <p>To view the details of a specific object (<code>HT_ISSUES</code>, <code>HT_PEOPLE</code>, or <code>HT_PROJECTS</code>) click the <span class="bold">View</span> icon (the magnifying glass) icon to the left of the object name. </p> </li> </ol> </div><!-- class="sect3" --> </div><!-- class="sect2" --> </div><!-- class="sect1" --> <a id="BABBJHGB" name="BABBJHGB"></a> <div class="sect1"> <!-- infolevel=all infotype=General --><a id="sthref216" name="sthref216"></a> <h2>Loading Demonstration Data </h2> <p>Once you have created all the necessary database objects, the next step is to load data into the tables. You can manually load data using the import functionality available in Data Workshop or SQL Workshop. In the following exercise, you will use SQL Workshop to load demonstration data. </p> <p>Look at the DDL you copied from <a href="scripts.htm#CBHCEIGH">"Create Application Database Objects DDL"</a>. Notice that the sequences used for the primary keys start at 40 in order to leave room for the demonstration data. Because the before insert triggers are coded so that the sequence is only accessed if a primary key value is not provided, they will not needed to be disabled in order for you to load data. </p> <p class="subhead2">Topics:</p> <ul> <li type="disc"> <p><a href="#BABBCCDE">Load Projects Data</a> </p> </li> <li type="disc"> <p><a href="#BABIBDCH">Load People Data</a> </p> </li> <li type="disc"> <p><a href="#BABEAEGJ">Load Issues Data</a> </p> </li> </ul><a id="BABBCCDE" name="BABBCCDE"></a> <div class="sect2"> <!-- infolevel=all infotype=General --><a id="sthref217" name="sthref217"></a> <h3>Load Projects Data </h3> <p>To import data into the Projects table: </p> <ol type="1" start="1"> <li> <p>Select the <span class="bold">SQL Workshop</span> tab. </p> </li> <li> <p>Under SQL Scripts, select <span class="bold">Scripts</span>. </p> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> <li> <p>For Script Name, enter <code>Load Project Data</code> and click <span class="bold">Next</span>. </p> </li> <li> <p>In Script, copy and paste the following: </p> <pre xml:space="preserve" class="oac_no_warn">INSERT INTO ht_projects (project_id, project_name, start_date, target_end_date) values (1, 'Internal Infrastructure', sysdate-150, sysdate-30) / INSERT INTO ht_projects (project_id, project_name, start_date, target_end_date) values (2, 'New Payroll Rollout', sysdate-150, sysdate+15) / INSERT INTO ht_projects (project_id, project_name, start_date, target_end_date) values (3, 'Email Integration', sysdate-120, sysdate-60) / INSERT INTO ht_projects (project_id, project_name, start_date, target_end_date) values (4, 'Public Website Operational', sysdate-60, sysdate+30) / insert into ht_projects (project_id, project_name, start_date, target_end_date) values (5, 'Employee Satisfaction Survey', sysdate-30, sysdate+60) / </pre> </li> <li> <p>Click <span class="bold">Create Script</span>. </p> </li> <li> <p>In the Scripts list, click <span class="bold">Run</span> in the Actions column adjacent to the <code>Load Project Data</code> script. </p> </li> <li> <p>From Parse As, select the appropriate schema owner. </p> </li> <li> <p>Click <span class="bold">Run Script</span>. </p> <p>The Summary Statistics page appears. No error should display for all commands. </p> </li> </ol> <p>Although you have created the Projects, the dates need to be updated to make the projects current. To accomplish this, you run another script. </p> <p>To update the project dates and make the projects current: </p> <ol type="1" start="1"> <li> <p>Return to the SQL Workshop home page. </p> </li> <li> <p>Under SQL Scripts, select <span class="bold">Scripts</span>. </p> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> <li> <p>For Script Name, enter <code>Update Project Dates</code> and click <span class="bold">Next</span>. </p> </li> <li> <p>In Script, copy and paste the following: </p> <pre xml:space="preserve" class="oac_no_warn">UPDATE ht_projects SET start_date = sysdate-150, target_end_date = sysdate-30 WHERE project_id = 1 / UPDATE ht_projects SET start_date = sysdate-150, target_end_date = sysdate+15 WHERE project_id = 2 / UPDATE ht_projects SET start_date = sysdate-120, target_end_date = sysdate-60 WHERE project_id = 3 / UPDATE ht_projects SET start_date = sysdate-60, target_end_date = sysdate+30 WHERE project_id = 4 / UPDATE ht_projects SET start_date = sysdate-30, target_end_date = sysdate+60 WHERE project_id = 5 / </pre> </li> <li> <p>Click <span class="bold">Create Script</span>. </p> </li> <li> <p>In the Scripts list, click <span class="bold">Run</span> in the Actions column adjacent to the <code>Update Project Dates</code> script. </p> </li> <li> <p>From Parse As, select the appropriate schema owner. </p> </li> <li> <p>Click <span class="bold">Run Script</span>. </p> <p>The Summary Statistics page appears. No error should display for all commands. </p> </li> <li> <p>Click <span class="bold">Cancel</span> to return the Scripts Repository. </p> </li> </ol> </div><!-- class="sect2" --> <a id="BABIBDCH" name="BABIBDCH"></a> <div class="sect2"> <!-- infolevel=all infotype=General --><a id="sthref218" name="sthref218"></a> <h3>Load People Data </h3> <p>Once you have loaded data into the Project table, you can load People data. Because of foreign keys in the Projects table, People data must be loaded after Project data. You will load data into the People table by creating and running a script in SQL Workshop. </p> <p>To load data into the People table: </p> <ol type="1" start="1"> <li> <p>Return to the SQL Workshop home page. </p> </li> <li> <p>Under SQL Scripts, select <span class="bold">Scripts</span>. </p> </li> <li> <p>In the Scripts Repository, click <span class="bold">Create</span>. </p> </li> <li> <p>For Script Name, enter <code>Load People Data</code> and click <span class="bold">Next</span>. </p> </li> <li> <p>In Script, copy and paste the following: </p> <pre xml:space="preserve" class="oac_no_warn">INSERT INTO ht_people (person_id, person_name, person_email, person_role, assigned_project) VALUES (1, 'Joe Cerno', 'joe.cerno@mrvl-bademail.com', 'CEO', null) / INSERT INTO ht_people (person_id, person_name, person_email, person_role, assigned_project) VALUES (2, 'Kim Roberts', 'kim.roberts@mrvl-bademail.com', 'Manager', null) / INSERT INTO ht_people (person_id, person_name, person_email, person_role, assigned_project) VALUES (3, 'Tom Suess', 'tom.suess@mrvl-bademail.com', 'Manager', null) / INSERT INTO ht_people (person_id, person_name, person_email, person_role, assigned_project) VALUES (4, 'Al Bines', 'al.bines@mrvl-bademail.com', 'Lead', 1) / INSERT INTO ht_people (person_id, person_name, person_email, person_role, assigned_project) VALUES (5, 'Carla Downing', 'carla.downing@mrvl-bademail.com', 'Lead', 2) / INSERT INTO ht_people (person_id, person_name, person_email, person_role, assigned_project) VALUES (6, 'Evan Fanner', 'evan.fanner@mrvl-bademail.com', 'Lead', 3) / INSERT INTO ht_people (person_id, person_name, person_email, person_role, assigned_project) values (7, 'George Hurst', 'george.hurst@mrvl-bademail.com', 'Lead', 4) / INSERT INTO ht_people (person_id, person_name, person_email, person_role, assigned_project) VALUES (8, 'Irene Jones', 'irene.jones@mrvl-bademail.com', 'Lead', 5) / INSERT INTO ht_people (person_id, person_name, person_email, person_role, assigned_project) VALUES (9, 'Karen London', 'karen.london@mrvl-bademail.com', 'Member', 1) / INSERT INTO ht_people (person_id, person_name, person_email, person_role, assigned_project) values (10, 'Mark Nile', 'mark.nile@mrvl-bademail.com', 'Member', 1) / INSERT INTO ht_people (person_id, person_name, person_email, person_role, assigned_project) VALUES (11, 'Jane Kerry', 'jane.kerry@mrvl-bademail.com', 'Member', 5) / INSERT INTO ht_people (person_id, person_name, person_email, person_role, assigned_project) VALUES (12, 'Olive Pope', 'olive.pope@mrvl-bademail.com', 'Member', 2) / INSERT INTO ht_people (person_id, person_name, person_email, person_role, assigned_project) VALUES (13, 'Russ Sanders', 'russ.sanders@mrvl-bademail.com', 'Member', 3) / INSERT INTO ht_people (person_id, person_name, person_email, person_role, assigned_project) VALUES (14, 'Tucker Uberton', 'tucker.uberton@mrvl-bademail.com', 'Member', 3) / INSERT INTO ht_people (person_id, person_name, person_email, person_role, assigned_project) VALUES (15, 'Vicky Williams', 'vicky.willaims@mrvl-bademail.com', 'Member', 4) / INSERT INTO ht_people (person_id, person_name, person_email, person_role, assigned_project) VALUES (16, 'Scott Tiger', 'scott.tiger@mrvl-bademail.com', 'Member', 4) / INSERT INTO ht_people (person_id, person_name, person_email, person_role, assigned_project) VALUES (17, 'Yvonne Zeiring', 'yvonee.zeiring@mrvl-bademail.com', 'Member', 4) / </pre> </li> <li> <p>Click <span class="bold">Create Script</span>. </p> </li> <li> <p>In the Scripts list, click <span class="bold">Run</span> in the Actions column adjacent to the <code>Load People Data</code> script. </p> </li> <li> <p>From Parse As, select the appropriate schema owner. </p> </li> <li> <p>Click <span class="bold">Run Script</span>. </p> <p>The Summary Statistics page appears. No error should display for all commands. </p> </li> </ol> </div><!-- class="sect2" --> <a id="BABEAEGJ" name="BABEAEGJ"></a> <div class="sect2"> <!-- infolevel=all infotype=General --><a id="sthref219" name="sthref219"></a> <h3>Load Issues Data </h3> <p>The last data you need to load is the Issues data. As with People data, you will create and run a script to populate the <code>Issues</code> table. </p> <p>To load data into the <code>Issues</code> table: </p> <ol type="1" start="1"> <li> <p>Return to the SQL Workshop home page. </p> </li> <li> <p>Under SQL Scripts, select <span class="bold">Scripts</span>. </p> </li> <li> <p>In the Scripts Repository, click <span class="bold">Create</span>. </p> </li> <li> <p>For Script Name, enter <code>Load Issues Data</code> and click <span class="bold">Next</span>. </p> </li> <li> <p>In Script, copy and paste the script in <a href="scripts.htm#CBHCCHFG">"Create Issues Script"</a>. </p> </li> <li> <p>Click <span class="bold">Create Script</span>. </p> </li> <li> <p>In the Scripts list, click <span class="bold">Run</span> in the Actions column adjacent to the Load People Data script. </p> </li> <li> <p>From Parse As, select the appropriate schema owner. </p> </li> <li> <p>Click <span class="bold">Run Script</span>. </p> <p>The Summary Statistics page appears. No error should display for all commands. </p> </li> </ol> </div><!-- class="sect2" --> </div><!-- class="sect1" --> <a id="BABHAFFD" name="BABHAFFD"></a> <div class="sect1"> <!-- infolevel=all infotype=General --><a id="sthref220" name="sthref220"></a> <h2>Building a Basic User Interface </h2> <p>Once you have created the objects that support your application and have loaded the demonstration data, the next step is to create a user interface. In this exercise, you use the Create Application Wizard in Application Builder to create an application and then the pages that support the data management and data presentation functions described in <a href="#BJFFCCGC">"Planning and Project Analysis"</a>. </p> <p class="subhead2">Topics:</p> <p>Topics in this section include: </p> <ul> <li type="disc"> <p><a href="#BJFBJFEI">Create the Application</a> </p> </li> <li type="disc"> <p><a href="#BJFBACGE">Add Pages to Maintain Projects</a> </p> </li> <li type="disc"> <p><a href="#BJFGAFCG">Add Pages to Track People</a> </p> </li> <li type="disc"> <p><a href="#BJFDECAB">Add Pages to Track Issues</a> </p> </li> <li type="disc"> <p><a href="#BJFIBIBD">Create Summary Reports</a> </p> </li> <li type="disc"> <p><a href="#BJFGAFIJ">Add Content to the Home Page</a> </p> </li> <li type="disc"> <p><a href="#BJFFEJID">Add a Breadcrumb Menu</a> </p> </li> </ul><a id="BJFBJFEI" name="BJFBJFEI"></a> <div class="sect2"> <!-- infolevel=all infotype=General --><a id="sthref221" name="sthref221"></a> <h3>Create the Application </h3> <p>You can use the Create Application Wizard to create an application that contains pages that enables users to view a report on and create data for selected tables within a schema. Alternatively, you can create an application first and then add pages to it. Since the application requirements include customized overview pages, for this exercise you will use latter approach. </p> <p>To create the application manually: </p> <ol type="1" start="1"> <li> <p>From the Workspace home page, click <span class="bold">Create Application</span>. </p> </li> <li> <p>On Select Creation Method, select <span class="bold">From Scratch</span> </p> </li> <li> <p>For Name and Authentication: </p> <ol type="a"> <li> <p>For Name, enter <code>Issue Tracker</code>. </p> </li> <li> <p>Set Pages to <span class="bold">1</span>. </p> </li> <li> <p>Accept the remaining defaults and click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>For <span class="bold">Tabs</span>, select <span class="bold">No Tabs</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Page Name, enter <code>Home</code> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Theme, select <span class="bold">Theme 10</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>Review your selections and click <span class="bold">Create Application</span>. </p> </li> </ol> <p>To view the application: </p> <ol type="1" start="1"> <li> <p>Click the <span class="bold">Run</span> icon on the Applications home page. </p> </li> <li> <p>When prompted enter your workspace username and password. </p> <p>This authentication is part of the default security of any newly created application. As shown in <a href="#BABCHDDC">Figure 10-1</a>, the home page appears. </p> <div class="figure"> <a id="BABCHDDC" name="BABCHDDC"></a><a id="sthref222" name="sthref222"></a> <p class="titleinfigure">Figure 10-1 Issue Tracking Application Home Page</p> <img src="./img/iss_home1.gif" alt="Description of iss_home1.gif follows" title="Description of iss_home1.gif follows"/> <br /><a id="sthref223" name="sthref223" href="./img_text/iss_home1.htm">Description of the illustration iss_home1.gif</a> <br /><br /> </div><!-- class="figure" --> <p>Although the page has no content, notice that the Create Application Wizard created the following items: </p> <ul> <li type="disc"> <p><span class="bold">Navigation Links</span> - Two navigation bar entries appear in the upper right of the page. Print enables the user to print the current page. Logout enables the user to log out of the application. </p> </li> <li type="disc"> <p><span class="bold">Developer Links</span> - The Developer Toolbar appears at the bottom of the page. These links only display if you are logged in as a developer. Regular users, those who only have access to run the application, will not see these links. From left to right, the Developer Toolbar contains the following links: </p> <ul> <li type="circle"> <p><span class="bold">Edit Application</span> - Edit the application by linking to the Application Builder home page. </p> </li> <li type="circle"> <p><span class="bold">Edit Page 1</span> - Edit the current running page. This link takes you to Page Definition for the current page. </p> </li> <li type="circle"> <p><span class="bold">New</span> - Add a new component to the current page. </p> </li> <li type="circle"> <p><span class="bold">Session</span> - Open a new page containing session details for the current page. </p> </li> <li type="circle"> <p><span class="bold">Debug</span> - Display the current page in debug mode. </p> </li> <li type="circle"> <p><span class="bold">Show Edit Links</span> - Displays edit links next to each object on the page that can be edited. Each edit link resembles two colons (<code>::</code>) and appears to the right of navigation bar items, tabs, region titles, buttons, and items. Clicking a edit link displays another window in which to edit the object. </p> </li> </ul> </li> </ul> </li> <li> <p>Click <span class="bold">Edit Application</span> on the Developer Toolbar to return to Application Builder home page. Notice that the Create Application Wizard also created a Login page. </p> </li> </ol> <p>Once you have created the basic application structure, the next step is to create individual pages. </p> </div><!-- class="sect2" --> <a id="BJFBACGE" name="BJFBACGE"></a> <div class="sect2"> <!-- infolevel=all infotype=General --><a id="sthref224" name="sthref224"></a> <h3>Add Pages to Maintain Projects </h3> <p>First, you need to create pages that enable users to view and add data to tables. To accomplish this, you will use the Form on a Table with Report, 2 Pages Wizard. This wizard creates a report page and maintenance page for each table the start with <code>HT_PROJECTS</code>. </p> <p class="subhead2">Topics:</p> <p>Topics in this section include: </p> <ul> <li type="disc"> <p><a href="#BJFCHJEI">Create Pages for Maintaining Projects</a> </p> </li> <li type="disc"> <p><a href="#BJFDJJEG">Refine the Appearance of the Projects Report Page</a> </p> </li> <li type="disc"> <p><a href="#BJFHJHIA">Refine the Create/Edit Project Page</a> </p> </li> </ul><a id="BJFCHJEI" name="BJFCHJEI"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref225" name="sthref225"></a> <h4>Create Pages for Maintaining Projects </h4> <p>To create pages for maintaining <code>HT_PROJECTS </code>table: </p> <ol type="1" start="1"> <li> <p>On the Application Builder home page, click <span class="bold">Create Page</span>. </p> </li> <li> <p>Select <span class="bold">Page with Component</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>Select <span class="bold">Form</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>Select <span class="bold">Form on a Table with Report, 2 Pages</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Table/View Owner, select the appropriate schema and click <span class="bold">Next</span>. </p> </li> <li> <p>For Table/View Name, select <code>HT_PROJECTS</code> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Report Attributes: </p> <ol type="a"> <li> <p>For Page, enter <code>2</code>. </p> </li> <li> <p>For Page Name and Region Title, enter <code>Projects</code>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>For Tab Options, accept the default selection <span class="bold">Do not use tabs</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Select Column(s), select every column except <code>PROJECT_ID</code> and click <span class="bold">Next</span>. </p> <p>Note that Project Name is unique and identifies the project. The ID was added to simplify the foreign key and enable cascading updates. </p> </li> <li> <p>For <span class="bold">Edit Link Image</span>, select the third option (the word Edit in blue with a white background) and click <span class="bold">Next</span>. </p> <p>The next attributes define the Form Page. </p> </li> <li> <p>On Identify Page and Region Attributes for Form: </p> <ol type="a"> <li> <p>For Page, enter <code>3</code>. </p> </li> <li> <p>For Page Name and Region Title, enter <code>Create/Edit Project</code>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>For Tab Options, accept the default selection <span class="bold">Do not use tabs</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Primary Key, accept the default <code>PROJECT_ID</code> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Source Type, accept the default <code>Existing Trigger</code> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Select Column(s), selected all columns and click <span class="bold">Next</span>. </p> </li> <li> <p>Under Identify Process Options, accept the defaults for <span class="bold">Insert</span>, <span class="bold">Update</span> and <span class="bold">Delete</span>, and click <span class="bold">Next</span>. </p> </li> <li> <p>Review your selections and click <span class="bold">Finish</span>. </p> </li> </ol> <p>To preview your page, select Run Page. As shown in <a href="#BJFCGIBC">Figure 10-0</a>, the newly created report displays the demo data. </p> <div class="figure"> <a id="sthref226" name="sthref226"></a> <p class="titleinfigure">Figure 10-2 Projects Page</p> <img src="./img/iss_project1.gif" alt="Description of iss_project1.gif follows" title="Description of iss_project1.gif follows"/> <br /><a id="sthref227" name="sthref227" href="./img_text/iss_project1.htm">Description of the illustration iss_project1.gif</a> <br /><br /> </div><!-- class="figure" --> <p>Click the <span class="bold">Edit</span> icon to view an existing row or click the <span class="bold">Create</span> button to create a new record. If you click the <span class="bold">Edit</span> icon to the left of Employee Satisfaction Survey, a form resembling <a href="#BABGIIAF">Figure 10-3</a> appears. </p> <div class="figure"> <a id="BABGIIAF" name="BABGIIAF"></a><a id="sthref228" name="sthref228"></a> <p class="titleinfigure">Figure 10-3 Create/Edit Project Form</p> <img src="./img/iss_projedit1.gif" alt="Description of iss_projedit1.gif follows" title="Description of iss_projedit1.gif follows"/> <br /><a id="sthref229" name="sthref229" href="./img_text/iss_projedit1.htm">Description of the illustration iss_projedit1.gif</a> <br /><br /> </div><!-- class="figure" --> </div><!-- class="sect3" --> <a id="BJFDJJEG" name="BJFDJJEG"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref230" name="sthref230"></a> <h4>Refine the Appearance of the Projects Report Page </h4> <p>You can change the appearance of the Projects report page by adding a format mask to the dates. </p> <p>To add a format mask to the dates on the Create/Edit Project page: </p> <ol type="1" start="1"> <li> <p>Navigate to the Page Definition for page 2, Projects: </p> <ol type="a"> <li> <p>From the Developer toolbar, select <span class="bold">Edit Application</span>. </p> </li> <li> <p>From the Pages list, select <span class="bold">Projects</span>. </p> </li> </ol> </li> <li> <p>Under the Regions section, select <span class="bold">Q</span> to the left of Projects. </p> </li> <li> <p>Edit the format for the START_DATE: </p> <ol type="a"> <li> <p>Click the <span class="bold">Edit</span> icon the left of <code>START_DATE</code>. </p> <p>The Column Attributes page appears. </p> </li> <li> <p>For Number/Date Format, enter <code>DD-MON-YYYY.</code> </p> </li> </ol> </li> <li> <p>Edit the format for the TARGET_END_DATE: </p> <ol type="a"> <li> <p>Click the Next button (>) at the top of the page to navigate to the next Report Item. </p> <p>The Column Attributes page appears. </p> </li> <li> <p>For Number/Date Format, enter <code>DD-MON-YYYY.</code> </p> </li> </ol> </li> <li> <p>Edit the format for the ACTUAL_END_DATE: </p> <ol type="a"> <li> <p>Click the Next button (>) at the top of the page to navigate to the next Report Item. </p> <p>The Column Attributes page appears. </p> </li> <li> <p>For Number/Date Format, enter <code>DD-MON-YYYY.</code> </p> </li> </ol> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> <p>The Report Attributes page appears. </p> </li> <li> <p>For PROJECT_ID, delete the Heading <span class="bold">Edit</span>. </p> </li> <li> <p>For the START_DATE, TARGET_END_DATE and ACTUAL_END_DATE columns, select <span class="bold">center</span> for Column Alignment and Heading Alignment. </p> </li> <li> <p>To enable column heading sorting, check <span class="bold">Sort</span> for all columns except PROJECT_ID. </p> </li> <li> <p>For PROJECT_NAME, select <span class="bold">1</span> for Sort Sequence. </p> <p>This selection specifies PROJECT_NAME as the default column to sort on. Note this functionality can overridden by any user selections. </p> </li> <li> <p>Scroll down to Sorting. For Ascending and Descending Image, select the light gray arrow. </p> </li> <li> <p>Under Messages, enter the following in When No Data Found Message: </p> <pre xml:space="preserve" class="oac_no_warn">No Projects found. </pre> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> <p>To view your changes, click the run icon in the upper right of the page. As shown in <a href="#BABFAABJ">Figure 10-4</a>, note the addition of a sort control on the Project Name column and the format of the dates in the Start Date and Target End Date columns. </p> <div class="figure"> <a id="BABFAABJ" name="BABFAABJ"></a><a id="sthref231" name="sthref231"></a> <p class="titleinfigure">Figure 10-4 Projects Page with Sort Control</p> <img src="./img/iss_project2.gif" alt="Description of iss_project2.gif follows" title="Description of iss_project2.gif follows"/> <br /><a id="sthref232" name="sthref232" href="./img_text/iss_project2.htm">Description of the illustration iss_project2.gif</a> <br /><br /> </div><!-- class="figure" --> </div><!-- class="sect3" --> <a id="BJFHJHIA" name="BJFHJHIA"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref233" name="sthref233"></a> <h4>Refine the Create/Edit Project Page </h4> <p>Next, you will customize the Create/Edit Project page to make the Project Name field larger, the date fields smaller, change the date picker type and add a format mask for dates, and add validations that check if the target and actual end dates are after the start date. </p> <p>To make the Project Name field larger and the date fields smaller: </p> <ol type="1" start="1"> <li> <p>Navigate to the Page Definition for Page 3, Create/Edit Project. </p> <ol type="a"> <li> <p>From the Developer toolbar, select <span class="bold">Edit Application</span>. </p> </li> <li> <p>From the Pages list, select <span class="bold">Create/Edit Projects</span>. </p> </li> </ol> </li> <li> <p>Under Items, select the heading <span class="bold">Items</span> to edit selected attributes of all items. </p> </li> <li> <p>Scroll to the right and locate the <span class="bold">Width</span> column: </p> <ol type="a"> <li> <p>For Project Name, enter <code>60</code>. </p> </li> <li> <p>For Start Date, enter <code>12</code>. </p> </li> <li> <p>For Target End Date, enter <code>12</code>. </p> </li> <li> <p>For Actual End Date, enter <code>12</code>. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> </li> <li> <p>Click the <span class="bold">Edit Page</span> icon in the upper right corner of the page to return to the Page Definition. </p> </li> </ol> <p>To change the date picker type and add a format mask for dates: </p> <ol type="1" start="1"> <li> <p>Edit the item P3_START_DATE. </p> <ol type="a"> <li> <p>Under Items, select <span class="bold">P3_START_DATE</span>. </p> </li> <li> <p>Under Identification, for Display As select <span class="bold">Date Picker (DD-MON-YYYY)</span>. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> <p>Edit the item P3_TARGET_END_DATE. </p> <ol type="a"> <li> <p>Under Items, select <span class="bold">P3_TARGET_END_DATE</span>. </p> </li> <li> <p>Under Identification, for Display As select <span class="bold">Date Picker (DD-MON-YYYY)</span>. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> <p>Edit the item P3_ACTUAL_END_DATE. </p> <ol type="a"> <li> <p>Under Items, select <span class="bold">P3_ACTUAL_END_DATE</span>. </p> </li> <li> <p>Under Identification, for Display As select <span class="bold">Date Picker (DD-MON-YYYY)</span>. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> </li> </ol> <p>To add validations to check if the target and actual end dates are after the start date: </p> <ol type="1" start="1"> <li> <p>Under the Validations section, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Identify the validation level, accept the default <span class="bold">Item level validation</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Item, select <span class="bold">P3_TARGET_END_DATE</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Select a validation method, select <span class="bold">PL/SQL</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Pick PL/SQL Validation Type, accept the default <span class="bold">PL/SQL Expression</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Validation Name, enter <code>TARGET_AFTER_START</code> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Validation and Error Message: </p> <ol type="a"> <li> <p>For Validation, enter: </p> <pre xml:space="preserve" class="oac_no_warn">to_date(:P3_ACTUAL_END_DATE,'DD-MON-YYYY') >= to_date(:P3_START_DATE,'DD-MON-YYYY') </pre> </li> <li> <p>For Error Message, enter: </p> <pre xml:space="preserve" class="oac_no_warn">Actual End Date must be same or after Start Date. </pre> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>On Identify Validation Conditions: </p> <ol type="a"> <li> <p>For <span class="bold">Condition Type</span>, select the shortcut link <span class="bold">[item not null]</span>. </p> <p>Selecting this shortcut link selects <span class="bold">Value of Item in Expression 1 is NOT NULL</span> from the Condition Type list. </p> </li> <li> <p>For Expression 1, enter: </p> <pre xml:space="preserve" class="oac_no_warn">P3_ACTUAL_END_DATE. </pre> <p>This selection ensures that this validation will only execute if the user enters an Actual End Date. </p> </li> <li> <p>Click <span class="bold">Create.</span> </p> </li> </ol> </li> </ol> <p>To view your changes, click the <span class="bold">Run Page</span> icon in the upper right of the page. (See <a href="#BABHAGIE">Figure 10-5</a>.) </p> <div class="figure"> <a id="BABHAGIE" name="BABHAGIE"></a><a id="sthref234" name="sthref234"></a> <p class="titleinfigure">Figure 10-5 Modified Create/Edit Project </p> <img src="./img/iss_projedit2.gif" alt="Description of iss_projedit2.gif follows" title="Description of iss_projedit2.gif follows"/> <br /><a id="sthref235" name="sthref235" href="./img_text/iss_projedit2.htm">Description of the illustration iss_projedit2.gif</a> <br /><br /> </div><!-- class="figure" --> </div><!-- class="sect3" --> </div><!-- class="sect2" --> <a id="BJFGAFCG" name="BJFGAFCG"></a> <div class="sect2"> <!-- infolevel=all infotype=General --><a id="sthref236" name="sthref236"></a> <h3>Add Pages to Track People </h3> <p>Once the initial Projects pages are complete, you create pages for maintaining people. </p> <p class="subhead2">Topics:</p> <p>Topics in this section include: </p> <ul> <li type="disc"> <p><a href="#BJFEBBEG">Create Pages for Maintaining People</a> </p> </li> <li type="disc"> <p><a href="#BJFDFGFF">Modify of the People Report Page</a> </p> </li> <li type="disc"> <p><a href="#BJFFGDAC">Refine the Create/Edit People Page</a> </p> </li> </ul><a id="BJFEBBEG" name="BJFEBBEG"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref237" name="sthref237"></a> <h4>Create Pages for Maintaining People </h4> <p>To create pages for maintaining the <code>HT_PEOPLE</code> table: </p> <ol type="1" start="1"> <li> <p>Return to the Application Builder home page. </p> </li> <li> <p>Click <span class="bold">Create Page</span>. </p> </li> <li> <p>Select <span class="bold">Page with Component</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>Select <span class="bold">Form</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>Select <span class="bold">Form on a Table with Report, 2 Pages</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Table/View Owner, select the appropriate schema and click <span class="bold">Next</span>. </p> </li> <li> <p>For Table/View Name, select <span class="bold">HT_PEOPLE</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Report Attributes: </p> <ol type="a"> <li> <p>For Page, enter <code>4</code>. </p> </li> <li> <p>For Page Name and Region Title, enter <code>People</code>. </p> </li> <li> <p>Click <span class="bold">Next.</span> </p> </li> </ol> </li> <li> <p>For <span class="bold">Tab Options</span>, accept the default <span class="bold">Do not use tabs</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For <span class="bold">Select Column(s)</span>, select all columns except <code>PERSON_ID</code> and click <span class="bold">Next</span>. </p> </li> <li> <p>For <span class="bold">Edit Link Image</span>, select the third option (the word Edit in blue with a white background) and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Page and Region Attributes for Form: </p> <ol type="a"> <li> <p>For Page, enter <code>5</code>. </p> </li> <li> <p>For Page Name and Region Title, enter <code>Create/Edit Person Information</code>. </p> </li> <li> <p>Click <span class="bold">Next.</span> </p> </li> </ol> </li> <li> <p>For Tab Options, accept the default <span class="bold">Do not use tabs</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Primary Key, accept the default <span class="bold">PERSON_ID</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Source Type, accept the default <span class="bold">Existing Trigger</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Select Column(s), select all the columns and click <span class="bold">Next</span>. </p> </li> <li> <p>For Insert, Update and Delete, accept the defaults and click <span class="bold">Next</span>.<span class="bold"></span> </p> </li> <li> <p>Review your selections and click <span class="bold">Finish</span>. </p> </li> </ol> <p>To preview your page, select Run Page. As shown in <a href="#BABDAJHF">Figure 10-6</a>, notice the newly created report displays the demo data. </p> <div class="figure"> <a id="BABDAJHF" name="BABDAJHF"></a><a id="sthref238" name="sthref238"></a> <p class="titleinfigure">Figure 10-6 People Page</p> <img src="./img/iss_people1.gif" alt="Description of iss_people1.gif follows" title="Description of iss_people1.gif follows"/> <br /><a id="sthref239" name="sthref239" href="./img_text/iss_people1.htm">Description of the illustration iss_people1.gif</a> <br /><br /> </div><!-- class="figure" --> <p>To preview the page for adding or editing people, click the <span class="bold">Edit</span> button in the far left column. </p> </div><!-- class="sect3" --> <a id="BJFDFGFF" name="BJFDFGFF"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref240" name="sthref240"></a> <h4>Modify of the People Report Page </h4> <p>Next, you will alter the People Report by changing the query to include a join to the Projects table and modify the headings. </p> <p>To change the query to include a join to the Projects table: </p> <ol type="1" start="1"> <li> <p>Navigate to the Page Definition for page 4, People. </p> </li> <li> <p>Under the Regions section, select <span class="bold">People</span>. </p> </li> <li> <p>Scroll down to Region Source. </p> </li> <li> <p>In Region Source, enter: </p> <pre xml:space="preserve" class="oac_no_warn">SELECT a."PERSON_ID", a."PERSON_NAME", a."PERSON_EMAIL", a."PERSON_ROLE", b."PROJECT_NAME" FROM "#OWNER#"."HT_PEOPLE" a, "#OWNER#"."HT_PROJECTS" b WHERE a.assigned_project = b.project_id (+) </pre> <p>Note that the outer join is necessary because the project assignment is optional. </p> </li> <li> <p>Select the Report Attributes tab. </p> <ol type="a"> <li> <p>For PERSON_ID, remove the Heading <span class="bold">Edit</span>. </p> </li> <li> <p>For PERSON_NAME, change Heading to <code>Name</code>. </p> </li> <li> <p>For PERSON_EMAIL, change Heading to <code>Email</code>. </p> </li> <li> <p>For PERSON_ROLE, change Heading to <code>Role</code>. </p> </li> <li> <p>For PROJECT_NAME, change Heading to <code>Assigned Project</code> and select <span class="bold">left</span> for Heading Align. </p> </li> </ol> </li> <li> <p>Enable column heading sorting by selecting <span class="bold">Sort</span> for all columns except PERSON_ID. </p> </li> <li> <p>For PERSON_NAME, select 1 for Sort Sequence. </p> <p>This selection specifies PERSON_NAME as the default column to sort on. Note this functionality can overridden by any user selections. </p> </li> <li> <p>Scroll down to Sorting. For Ascending and Descending Image, select the light gray arrow. </p> </li> <li> <p>Under Messages, enter the following in When No Data Found Message: </p> <pre xml:space="preserve" class="oac_no_warn">No people found. </pre> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> <p>To view your changes, click the <span class="bold">Run Page</span> icon in the upper right of the page. As shown in <a href="#BABCBIBI">Figure 10-7</a>, note the addition of a sort control on the Name column. </p> <div class="figure"> <a id="BABCBIBI" name="BABCBIBI"></a><a id="sthref241" name="sthref241"></a> <p class="titleinfigure">Figure 10-7 Revised People Page</p> <img src="./img/iss_people2.gif" alt="Description of iss_people2.gif follows" title="Description of iss_people2.gif follows"/> <br /><a id="sthref242" name="sthref242" href="./img_text/iss_people2.htm">Description of the illustration iss_people2.gif</a> <br /><br /> </div><!-- class="figure" --> </div><!-- class="sect3" --> <a id="BJFFGDAC" name="BJFFGDAC"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref243" name="sthref243"></a> <h4>Refine the Create/Edit People Page </h4> <p>Next, you will customize the Create/Edit People page by adding lists of values to make it easier for users to select a Role or Assigned Project. </p> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref244" name="sthref244"></a> <h5>Add Lists of Values </h5> <p>To add a list of values for Projects: </p> <ol type="1" start="1"> <li> <p>Navigate to the Page Definition for page 5, Create/Edit Person Information. </p> </li> <li> <p>Under Shared Components, locate the Lists of Values section and click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Create List of Values, accept the default <span class="bold">From Scratch</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Create List of Values (LOV): </p> <ol type="a"> <li> <p>For Name, enter <code>PROJECTS</code>. </p> </li> <li> <p>For Type:, select <span class="bold">Dynamic</span> and click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>In Query, enter: </p> <pre xml:space="preserve" class="oac_no_warn">SELECT project_name d, project_id v FROM ht_projects ORDER BY d </pre> </li> <li> <p>Click <span class="bold">Create Named LOV</span>. </p> </li> </ol> <p>To add a list of values for Roles: </p> <ol type="1" start="1"> <li> <p>Under Shared Components, locate the Lists of Values section and click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Create List of Values, accept the default From Scratch and click <span class="bold">Next</span>. </p> </li> <li> <p>On Create List of Values (LOV): </p> <ol type="a"> <li> <p>For Name, enter <code>ROLES</code>. </p> </li> <li> <p>For Type:, select <span class="bold">Static</span> and click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>Enter the display value and return value pairs shown in <a href="#BJFDAJDD">Table 10-4</a>: </p> <div class="tblFormal"> <a id="sthref245" name="sthref245"></a><a id="BJFDAJDD" name="BJFDAJDD"></a> <p class="titleintable">Table 10-4 Display Value and Return Value pairs</p> <table class="Formal" title="Display Value and Return Value pairs" summary="This table contains a list of Display Value and Return Value pairs you must enter to complete this exercise." dir="ltr" border="1" width="100%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <thead> <tr align="left" valign="top"> <th align="left" valign="bottom" id="r1c1-t9">Display Value </th> <th align="left" valign="bottom" id="r1c2-t9">Return Value </th> </tr> </thead> <tbody> <tr align="left" valign="top"> <td align="left" id="r2c1-t9" headers="r1c1-t9">CEO </td> <td align="left" headers="r2c1-t9 r1c2-t9">CEO </td> </tr> <tr align="left" valign="top"> <td align="left" id="r3c1-t9" headers="r1c1-t9">Manager </td> <td align="left" headers="r3c1-t9 r1c2-t9">Manager </td> </tr> <tr align="left" valign="top"> <td align="left" id="r4c1-t9" headers="r1c1-t9">Lead </td> <td align="left" headers="r4c1-t9 r1c2-t9">Lead </td> </tr> <tr align="left" valign="top"> <td align="left" id="r5c1-t9" headers="r1c1-t9">Member </td> <td align="left" headers="r5c1-t9 r1c2-t9">Member </td> </tr> </tbody> </table><br /> </div><!-- class="tblFormal" --> </li> <li> <p>Click <span class="bold">Create Static LOV</span>. </p> </li> <li> <p>Return to the Page Definition for Page 5. Click the <span class="bold">Edit Page</span> icon in the upper right corner. </p> </li> </ol> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref246" name="sthref246"></a> <h5>Edit Display Attributes </h5> <p>To edit display attributes for P5_PERSON_ROLE: </p> <ol type="1" start="1"> <li> <p>Under Items, select <span class="bold">P5_PERSON_ROLE</span>. </p> </li> <li> <p>Under Identification, select <span class="bold">Radiogroup</span> from the Display As list. </p> </li> <li> <p>Under Label, in Label enter <code>Role</code>. </p> </li> <li> <p>Under Element, enter the following in Form Element Option Attribute: </p> <pre xml:space="preserve" class="oac_no_warn">class="instructiontext" </pre> <p>This specifies that the text associated with each radio group option is the same size as other items on the page. </p> </li> <li> <p>Under List of Values, select <span class="bold">ROLES</span> from the Named LOV list. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> <p>To edit display attributes for P5_ASSIGNED_PROJECT: </p> <ol type="1" start="1"> <li> <p>Under Items, select <span class="bold">P5_ASSIGNED_PROJECT</span>. </p> </li> <li> <p>Under Identification, select <span class="bold">Select List</span> from the Display As list. </p> </li> <li> <p>Under List of Values: </p> <ol type="a"> <li> <p>From the Named LOV list, select <span class="bold">PROJECTS</span>. </p> <p>Next, specify that the underlying column is not mandatory. </p> </li> <li> <p>For Null display value, enter: </p> <pre xml:space="preserve" class="oac_no_warn">- None - </pre> </li> </ol> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> <p>To alter the display of fields and field labels: </p> <ol type="1" start="1"> <li> <p>Click the heading <span class="bold">Items</span>. </p> </li> <li> <p>For P5_PERSON_NAME: </p> <ol type="a"> <li> <p>For Prompt, enter <code>Name</code>. </p> </li> <li> <p>For Width, enter <code>60</code>. </p> </li> </ol> </li> <li> <p>For P5_PERSON_EMAIL: </p> <ol type="a"> <li> <p>For Prompt, enter <code>Email Address</code>. </p> </li> <li> <p>For Width, enter <code>60</code>. </p> </li> </ol> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> <li> <p>Click the <span class="bold">Edit Page</span> icon in the upper right corner to return to the Page Definition for Page 5. </p> </li> </ol> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref247" name="sthref247"></a> <h5>Create a Validation </h5> <p>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. </p> <p>To add validations to ensure the correct people are assigned projects: </p> <ol type="1" start="1"> <li> <p>Under the Validations section, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>On Identify Validation Type, accept the default <span class="bold">Item level validation</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Item, select <span class="bold">P5_ASSIGNED_PROJECT</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Select a validation method, select <span class="bold">PL/SQL</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Pick the type of PL/SQL validation you wish to create, accept the default <span class="bold">PL/SQL Expression</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Validation Name, enter <code>PROJECT_MAND_FOR_LEADER_AND_MEMBER</code> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Validation and Error Message: </p> <ol type="a"> <li> <p>For Validation, enter: </p> <pre xml:space="preserve" class="oac_no_warn">(:P5_PERSON_ROLE IN ('CEO','Manager') AND :P5_ASSIGNED_PROJECT = '%'||'null%') OR (:P5_PERSON_ROLE IN ('Lead','Member') AND :P5_ASSIGNED_PROJECT != '%'||'null%') </pre> <p>Oracle HTML DB passes nulls as <code>%null%</code>. It also replaces <code>%null%</code> 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. </p> </li> <li> <p>For Error Message, enter: </p> <pre xml:space="preserve" class="oac_no_warn">Leads and Members must have an Assigned Project, CEO and Managers cannot have an Assigned Project. </pre> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> </ol> <p>To view your changes, click the <span class="bold">Run Page</span> icon in the upper right of the page. (See <a href="#BABCIJGD">Figure 10-8</a>.) </p> <div class="figure"> <a id="BABCIJGD" name="BABCIJGD"></a><a id="sthref248" name="sthref248"></a> <p class="titleinfigure">Figure 10-8 Revised Create/Edit Person Information Form</p> <img src="./img/iss_peoedit1.gif" alt="Description of iss_peoedit1.gif follows" title="Description of iss_peoedit1.gif follows"/> <br /><a id="sthref249" name="sthref249" href="./img_text/iss_peoedit1.htm">Description of the illustration iss_peoedit1.gif</a> <br /><br /> </div><!-- class="figure" --> <p>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. </p> </div><!-- class="sect4" --> </div><!-- class="sect3" --> </div><!-- class="sect2" --> <a id="BJFDECAB" name="BJFDECAB"></a> <div class="sect2"> <!-- infolevel=all infotype=General --><a id="sthref250" name="sthref250"></a> <h3>Add Pages to Track Issues </h3> <p>Lastly, you need to create pages for <code>HT_ISSUES</code>. This application needs multiple views on Issues. You can create these views as one, more complex report or as separate reports. For this exercise you will be creating the complex report. This report will have an Issues maintenance form. You will then link this maintenance form in multiple places. Ultimately, the Issues report will display Issues by the person who identified the issue, project, assigned person, status, or priority. </p> <p class="subhead2">Topics:</p> <p>Topics in this section include: </p> <ul> <li type="disc"> <p><a href="#BJFEBDGA">Create a Report for HT_ISSUES</a> </p> </li> <li type="disc"> <p><a href="#BJFJJFAH">Refine the Create/Edit Issues Page</a> </p> </li> <li type="disc"> <p><a href="#BJFFDHAE">Refine the Issues Report</a> </p> </li> <li type="disc"> <p><a href="#BJFBHJEF">Add a Page to Support Assigning Multiple Issues Simultaneously</a> </p> </li> </ul><a id="BJFEBDGA" name="BJFEBDGA"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref251" name="sthref251"></a> <h4>Create a Report for HT_ISSUES </h4> <p>To create a report for maintaining <code>HT_ISSUES</code>: </p> <ol type="1" start="1"> <li> <p>On the Application Builder home page, click <span class="bold">Create Page</span>. </p> </li> <li> <p>Select <span class="bold">Page with Component</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>Select <span class="bold">Form</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>Select <span class="bold">Form on a Table with Report, 2 Pages</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Table/View Owner, select the appropriate schema and click <span class="bold">Next</span>. </p> </li> <li> <p>For Table/View Name, select <span class="bold">HT_ISSUES</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Report Attribute: </p> <ol type="a"> <li> <p>For Page, enter <code>6</code>. </p> </li> <li> <p>For Page Name and Region Title, enter <code>Issues</code> and click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>For <span class="bold">Tab Options</span>, accept the default <span class="bold">Do not use tabs</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For <span class="bold">Select Column(s)</span>, select the following and click <span class="bold">Next</span>: </p> <ol type="a"> <li> <p>ISSUE_SUMMARY </p> </li> <li> <p>IDENTIFIED_BY </p> </li> <li> <p>RELATED_PROJECT </p> </li> <li> <p>ASSIGNED_TO </p> </li> <li> <p>STATUS </p> </li> <li> <p>PRIORITY </p> </li> <li> <p>TARGET_RESOLUTION_DATE </p> </li> <li> <p>ACTUAL_RESOLUTION_DATE </p> </li> </ol> </li> <li> <p>For <span class="bold">Edit Link Image</span>, select the third option (the word Edit in blue with a white background) and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Page and Region Attributes for Form: </p> <ol type="a"> <li> <p>For Page, enter <code>7</code>. </p> </li> <li> <p>For Page Name and Region Title, enter <code>Create/Edit Issues</code>. </p> </li> <li> <p>Click <span class="bold">Next.</span> </p> </li> </ol> </li> <li> <p>For Tab Options, accept the default <span class="bold">Do not use tabs</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Primary Key, accept the default <span class="bold">ISSUE_ID</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Source Type, accept the default <span class="bold">Existing Trigger</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Select Column(s), select all the columns and click <span class="bold">Next</span>. </p> </li> <li> <p>For Insert, Update and Delete, accept the default value <span class="bold">Yes</span> and click <span class="bold">Next</span>.<span class="bold"></span> </p> </li> <li> <p>Review your selections and click <span class="bold">Finish</span>. </p> </li> </ol> </div><!-- class="sect3" --> <a id="BJFJJFAH" name="BJFJJFAH"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref252" name="sthref252"></a> <h4>Refine the Create/Edit Issues Page </h4> <p>When you refine the Create/Edit Page you will: </p> <ul> <li type="disc"> <p>Add lists of values to make it easier for users to select foreign key columns </p> </li> <li type="disc"> <p>Organize and clean up items </p> </li> <li type="disc"> <p>Change the display of audit columns </p> </li> <li type="disc"> <p>Add a button to make data entry faster </p> </li> </ul> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref253" name="sthref253"></a> <h5>Add Lists of Values </h5> <p>Next, you need to add lists of values for Status, Priorities, and People. </p> <p>To add a list of values for Status: </p> <ol type="1" start="1"> <li> <p>Navigate to the Page Definition for page 7: </p> </li> <li> <p>Under the Lists of Values section and click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Create List of Values, accept the default <span class="bold">From Scratch</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Create List of Values (LOV): </p> <ol type="a"> <li> <p>For Name, enter <code>STATUS</code>. </p> </li> <li> <p>For Type, select <span class="bold">Static</span>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>Enter the Display Value and Return Value pairs shown in <a href="#BJFHAJCF">Table 10-5</a>: </p> <div class="tblFormal"> <a id="sthref254" name="sthref254"></a><a id="BJFHAJCF" name="BJFHAJCF"></a> <p class="titleintable">Table 10-5 Display Value and Return Value Pairs</p> <table class="Formal" title="Display Value and Return Value Pairs" summary="This table contains a list of Display Value and Return Value pairs you must enter to complete this exercise." dir="ltr" border="1" width="100%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <thead> <tr align="left" valign="top"> <th align="left" valign="bottom" id="r1c1-t10">Display Value </th> <th align="left" valign="bottom" id="r1c2-t10">Return Value </th> </tr> </thead> <tbody> <tr align="left" valign="top"> <td align="left" id="r2c1-t10" headers="r1c1-t10">Open </td> <td align="left" headers="r2c1-t10 r1c2-t10">Open </td> </tr> <tr align="left" valign="top"> <td align="left" id="r3c1-t10" headers="r1c1-t10">On-Hold </td> <td align="left" headers="r3c1-t10 r1c2-t10">On-Hold </td> </tr> <tr align="left" valign="top"> <td align="left" id="r4c1-t10" headers="r1c1-t10">Closed </td> <td align="left" headers="r4c1-t10 r1c2-t10">Closed </td> </tr> </tbody> </table><br /> </div><!-- class="tblFormal" --> </li> <li> <p>Click <span class="bold">Create Static LOV</span>. </p> </li> </ol> <p>To add a list of values for Priorities: </p> <ol type="1" start="1"> <li> <p>Return to the Page Definition for Page 7. Click the <span class="bold">Edit Page</span> icon in the upper right corner. </p> </li> <li> <p>Under the Lists of Values section and click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Create List of Values, accept the default <span class="bold">From Scratch</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Create List of Values (LOV): </p> <ol type="a"> <li> <p>For Name, enter <code>PRIORITIES</code>. </p> </li> <li> <p>For Type, select <span class="bold">Static</span> and click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>Enter the Display Value and Return Value pairs shown in <a href="#BJFBEHDA">Table 10-6</a>. </p> <div class="tblFormal"> <a id="sthref255" name="sthref255"></a><a id="BJFBEHDA" name="BJFBEHDA"></a> <p class="titleintable">Table 10-6 Display Value and Return Value Pairs</p> <table class="Formal" title="Display Value and Return Value Pairs" summary="This table contains a list of Display Value and Return Value pairs you must enter to complete this exercise." dir="ltr" border="1" width="100%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <thead> <tr align="left" valign="top"> <th align="left" valign="bottom" id="r1c1-t11">Display Value </th> <th align="left" valign="bottom" id="r1c2-t11">Return Value </th> </tr> </thead> <tbody> <tr align="left" valign="top"> <td align="left" id="r2c1-t11" headers="r1c1-t11">High </td> <td align="left" headers="r2c1-t11 r1c2-t11">High </td> </tr> <tr align="left" valign="top"> <td align="left" id="r3c1-t11" headers="r1c1-t11">Medium </td> <td align="left" headers="r3c1-t11 r1c2-t11">Medium </td> </tr> <tr align="left" valign="top"> <td align="left" id="r4c1-t11" headers="r1c1-t11">Low </td> <td align="left" headers="r4c1-t11 r1c2-t11">Low </td> </tr> </tbody> </table><br /> </div><!-- class="tblFormal" --> </li> <li> <p>Click <span class="bold">Create Static LOV</span>. </p> </li> </ol> <p>To add a list of values for People: </p> <ol type="1" start="1"> <li> <p>Return to the Page Definition for Page 7. Click the <span class="bold">Edit Page</span> icon in the upper right corner. </p> </li> <li> <p>Under the Lists of Values section and click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Create List of Values, accept the default <span class="bold">From Scratch</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Create List of Values (LOV): </p> <ol type="a"> <li> <p>For Name, enter <code>PEOPLE</code>. </p> </li> <li> <p>For Type, select <span class="bold">Dynamic</span> and click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>In Query enter: </p> <pre xml:space="preserve" class="oac_no_warn">SELECT person_name d, person_id v FROM ht_people ORDER BY 1 </pre> </li> <li> <p>Click <span class="bold">Created Named LOV</span>. </p> </li> </ol> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref256" name="sthref256"></a> <h5>Edit Specific Items </h5> <p>Next, you edit individual items. </p> <p>To edit P7_IDENTIFIED_BY: </p> <ol type="1" start="1"> <li> <p>Return to the Page Definition for Page 7. Click the <span class="bold">Edit Page</span> icon in the upper right corner. </p> </li> <li> <p>Under Items, select <span class="bold">P7_IDENTIFIED_BY</span>. </p> </li> <li> <p>Under Identification, select <span class="bold">Select List</span> from the Display As list. </p> </li> <li> <p>Scroll down to List of Values: </p> <ol type="a"> <li> <p>For Named LOV, select <span class="bold">PEOPLE</span>. </p> </li> <li> <p>For Display Null, select <span class="bold">Yes</span>. The base column is mandatory but you do not want the first name in the list becoming the default value. </p> </li> <li> <p>For Null display value, enter: </p> <pre xml:space="preserve" class="oac_no_warn"> - Select Person - </pre> </li> </ol> </li> <li> <p>Click the Next button (>) at the top of the page to navigate to the next item. </p> </li> </ol> <p>To edit P7_IDENTIFIED_DATE: </p> <ol type="1" start="1"> <li> <p>Navigate to P7_IDENTIFIED_DATE. </p> </li> <li> <p>Under Identification, for select <span class="bold">Date Picker (DD-MON-YYYY)</span> from the Display As list. </p> </li> <li> <p>Scroll down to Default Value: </p> <ol type="a"> <li> <p>For Default Value, enter: </p> <pre xml:space="preserve" class="oac_no_warn">to_char(sysdate,'DD-MON-YYYY') </pre> </li> <li> <p>For Default Value Type, select <span class="bold">PL/SQL Expression</span>. </p> </li> </ol> </li> <li> <p>Click the Next button (>) at the top of the page to navigate to the next item. </p> </li> </ol> <p>To edit P7_RELATED_PROJECT: </p> <ol type="1" start="1"> <li> <p>Navigate to P7_RELATED_PROJECT. </p> </li> <li> <p>Under Identification, for select <span class="bold">Select List</span> from the Display As list. </p> </li> <li> <p>Scroll down to List of Values: </p> <ol type="a"> <li> <p>For Named LOV, select <span class="bold">PEOPLE</span>. </p> </li> <li> <p>For Display Null, select <span class="bold">Yes</span>. </p> </li> <li> <p>For Null display value, enter: </p> <pre xml:space="preserve" class="oac_no_warn"> - Select Person - </pre> </li> </ol> </li> <li> <p>Click the Next button (>) at the top of the page to navigate to P7_STATUS. </p> </li> </ol> <p>To edit P7_STATUS: </p> <ol type="1" start="1"> <li> <p>Navigate to P7_STATUS. </p> </li> <li> <p>Under Identification, for select <span class="bold">Radiogroup</span> from the Display As list. </p> </li> <li> <p>In Label, enter: </p> <pre xml:space="preserve" class="oac_no_warn">Status: </pre> </li> <li> <p>Scroll down to Element. Enter the following in the Form Element Option Attribute: </p> <pre xml:space="preserve" class="oac_no_warn">class="instructiontext" </pre> </li> <li> <p>Under Default Value, enter <code>Open in Default value</code>. </p> </li> <li> <p>Scroll down to List of Values: </p> <ol type="a"> <li> <p>For Named LOV, select <span class="bold">STATUS</span>. </p> </li> <li> <p>For Columns, enter 3. </p> <p>This selection enables the three valid values to display side by side. </p> </li> </ol> </li> <li> <p>Click the Next button (>) at the top of the page to navigate to the next item. </p> </li> </ol> <p>To edit P7_PRIORITY: </p> <ol type="1" start="1"> <li> <p>Navigate to P7_PRIORITY. </p> </li> <li> <p>Under Identification, for select <span class="bold">Radiogroup</span> from the Display As list. </p> </li> <li> <p>In Label, enter: </p> <pre xml:space="preserve" class="oac_no_warn">Priority: </pre> </li> <li> <p>Scroll down to Element. Enter the following in the Form Element Option attribute: </p> <pre xml:space="preserve" class="oac_no_warn">class="instructiontext" </pre> </li> <li> <p>Under Default Value, enter <code>Open in Default value</code>. </p> </li> <li> <p>Scroll down to List of Values: </p> <ol type="a"> <li> <p>For Named LOV, select <span class="bold">PRIORITIES</span>. </p> </li> <li> <p>For Display Null, select <span class="bold">Yes</span>. </p> </li> <li> <p>For Columns, enter 4. </p> <p>This selection reflects that fact there are three valid values plus the null value. </p> </li> <li> <p>For Null display value, enter <code>None</code>. </p> </li> </ol> </li> <li> <p>Click the Next button (>) at the top of the page to navigate to the next item. </p> </li> </ol> <p>To edit P7_TARGET_RESOLUTION_DATE: </p> <ol type="1" start="1"> <li> <p>Navigate to P7_TARGET_RESOLUTION_DATE. </p> </li> <li> <p>Under Identification, for select <span class="bold">Date Picker (DD-MON-YYYY) </span>from the Display As list. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> <p>To edit <code>P7_ACTUAL_RESOLUTION_DATE</code>: </p> <ol type="1" start="1"> <li> <p>Navigate to <code>P7_ACTUAL_RESOLUTION_DATE</code>. </p> </li> <li> <p>Under Identification, for select <span class="bold">Date Picker (DD-MON-YYYY) </span>from the Display As list. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref257" name="sthref257"></a> <h5>Create Regions to Group Items </h5> <p>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. </p> <p>To create new regions to group items: </p> <ol type="1" start="1"> <li> <p>Under Regions, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>From the Tasks list, select <span class="bold">Create Multiple HTML regions</span>. </p> </li> <li> <p>For the first row: </p> <ul> <li type="disc"> <p>For Sequence, enter <code>5</code>. </p> </li> <li type="disc"> <p>For Title, enter <code>Buttons</code>. </p> </li> <li type="disc"> <p>For Template, select <span class="bold">Button Region without Title</span>. </p> </li> </ul> </li> <li> <p>For the second row, in Title enter <code>Progress</code>. </p> </li> <li> <p>For the third row, in Title enter <code>Resolution</code>. </p> </li> <li> <p>For the fourth row, in Title enter <code>Audit Information</code>. </p> </li> <li> <p>Click <span class="bold">Create Region(s</span>). </p> </li> </ol> <p>Now that the new regions exist, rename the first region, Create/Edit Issues: </p> <ol type="1" start="1"> <li> <p>Under Regions, select <span class="bold">Create/Edit Issue</span>. </p> </li> <li> <p>In <span class="bold">Title</span>, enter:. </p> <pre xml:space="preserve" class="oac_no_warn">Issue Identification </pre> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref258" name="sthref258"></a> <h5>Move Items to the Appropriate Regions </h5> <p>Next, move each item to the appropriate region. Note that you will also need to modify some item widths. </p> <p>To move items to the appropriate regions: </p> <ol type="1" start="1"> <li> <p>Under Items, select the <span class="bold">Items</span> heading. </p> </li> <li> <p>Under Region, select <span class="bold">Progress</span> for the following items: </p> <ul> <li type="disc"> <p>P7_ASSIGNED_TO </p> </li> <li type="disc"> <p>P7_STATUS </p> </li> <li type="disc"> <p>P7_PRIORITY </p> </li> <li type="disc"> <p>P7_TARGET_RESOLUTION_DATE </p> </li> <li type="disc"> <p>P7_PROGRESS </p> </li> </ul> </li> <li> <p>Under Region, select <span class="bold">Resolution</span> for the following items: </p> <ul> <li type="disc"> <p>P7_ACTUAL_RESOLUTION_DATE </p> </li> <li type="disc"> <p>P7_RESOLUTION_SUMMARY </p> </li> </ul> </li> <li> <p>Under Region, select <span class="bold">Audit Information</span> for the following items: </p> <ul> <li type="disc"> <p>P7_CREATED_DATE </p> </li> <li type="disc"> <p>P7_CREATED_BY </p> </li> <li type="disc"> <p>P7_LAST_MODIFIED_DATE </p> </li> <li type="disc"> <p>P7_LAST_MODIFIED_BY </p> </li> </ul> </li> <li> <p>For P7_ISSUE_SUMMARY, enter <code>60</code> for Width. </p> </li> <li> <p>For P7_IDENTIFIED_DATE, enter <code>12</code> for Width. </p> </li> <li> <p>For P7_TARGET_RESOLUTION_DATE, enter <code>12</code> for Width. </p> </li> <li> <p>For P7_ACTUAL_RESOLUTION_DATE, enter <code>12</code> for Width. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> <li> <p>Click the <span class="bold">Edit Page</span> icon in the upper right to return the Page Definition of Page 7. </p> </li> </ol> <p>To move buttons to the Button region: </p> <ol type="1" start="1"> <li> <p>Return to the Page Definition for Page 7. </p> </li> <li> <p>Under the Buttons section, click the <span class="bold">Buttons</span> heading. </p> </li> <li> <p>Under Region for all buttons, select <span class="bold">Buttons</span>. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> <li> <p>Click the <span class="bold">Edit Page</span> icon in the upper right to return the Page Definition of Page 7. </p> </li> </ol> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref259" name="sthref259"></a> <h5>Change the Display of Audit Columns </h5> <p>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. </p> <p>To create a condition for the Audit Information region. </p> <ol type="1" start="1"> <li> <p>On the Page Definition of Page 7, select the <span class="bold">Audit Information</span> region. </p> </li> <li> <p>Scroll down to Conditional Display. </p> </li> <li> <p>From Condition Type, select <span class="bold">Value of Item in Expression 1 is NOT NULL</span>. </p> </li> <li> <p>In Expression 1, enter <code>P7_ISSUE_ID</code>. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> <p>Next, change the audit columns to display only. </p> <p>To edit P7_CREATED_DATE: </p> <ol type="1" start="1"> <li> <p>Under Items, select <span class="bold">P7_CREATED_DATE</span>. </p> </li> <li> <p>Under Identification, select <span class="bold">Display as Text (saves state)</span> from the Display As list. </p> </li> <li> <p>For Label, enter: </p> <pre xml:space="preserve" class="oac_no_warn">Created Date: </pre> </li> <li> <p>For Template, select <span class="bold">Optional Label with Help</span>. </p> </li> <li> <p>For HTML Table Cell Attributes, enter: </p> <pre xml:space="preserve" class="oac_no_warn">class="instructiontext" </pre> </li> <li> <p>Under Source, enter the following in Format Mask: </p> <pre xml:space="preserve" class="oac_no_warn">DD-MON-YYYY </pre> </li> <li> <p>Click the Next button (>) at the top of the page to navigate to the next item. </p> </li> </ol> <p>To edit P7_CREATED_BY: </p> <ol type="1" start="1"> <li> <p>Under Identification, select <span class="bold">Display as Text (saves state)</span> from the Display As list. </p> </li> <li> <p>For Label, enter: </p> <pre xml:space="preserve" class="oac_no_warn">Created By: </pre> </li> <li> <p>For Template, select <span class="bold">Optional Label with Help</span>. </p> </li> <li> <p>For HTML Table Cell Attributes, enter: </p> <pre xml:space="preserve" class="oac_no_warn">class="instructiontext" </pre> </li> <li> <p>Click the Next button (>) at the top of the page to navigate to the next item. </p> </li> </ol> <p>To edit P7_LAST_MODIFIED_DATE: </p> <ol type="1" start="1"> <li> <p>Under Identification, select <span class="bold">Display as Text (saves state)</span> from the Display As list. </p> </li> <li> <p>For Label, enter: </p> <pre xml:space="preserve" class="oac_no_warn">Last Modified Date: </pre> </li> <li> <p>For Template, select <span class="bold">Optional Label with Help</span>. </p> </li> <li> <p>For HTML Table Cell Attributes, enter: </p> <pre xml:space="preserve" class="oac_no_warn">class="instructiontext" </pre> </li> <li> <p>Under Source, for Format Mask, enter: </p> <pre xml:space="preserve" class="oac_no_warn">DD-MON-YYYY </pre> </li> <li> <p>Click the Next button (>) at the top of the page to navigate to the next item. </p> </li> </ol> <p>To edit P7_LAST_MODIFIED_BY: </p> <ol type="1" start="1"> <li> <p>Under Identification, select <span class="bold">Display as Text (saves state)</span> from the Display As list. </p> </li> <li> <p>For Label, enter: </p> <pre xml:space="preserve" class="oac_no_warn">Last Modified By: </pre> </li> <li> <p>For Template, select <span class="bold">Optional Label with Help</span>. </p> </li> <li> <p>For HTML Table Cell Attributes, enter: </p> <pre xml:space="preserve" class="oac_no_warn">class="instructiontext" </pre> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref260" name="sthref260"></a> <h5>Remove Unnecessary Validations </h5> <p>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. </p> <p>To remove not null validations: </p> <ol type="1" start="1"> <li> <p>Under Validations, select <span class="bold">P7_CREATED_DATE not null</span>. </p> </li> <li> <p>Click <span class="bold">Delete</span>. </p> </li> <li> <p>Click <span class="bold">OK</span> to confirm your selection. </p> </li> <li> <p>Under Validations, select <span class="bold">P7_CREATED_BY not null</span>. </p> </li> <li> <p>Click <span class="bold">Delete</span>. </p> </li> <li> <p>Click <span class="bold">OK</span> to confirm your selection. </p> </li> </ol> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref261" name="sthref261"></a> <h5>Return the User to the Calling Page </h5> <p>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. </p> <p>To create a hidden item: </p> <ol type="1" start="1"> <li> <p>Under Items, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Choose Item Type, select <span class="bold">Hidden</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Item Name and Display Position: </p> <ol type="a"> <li> <p>For Item Name, enter: </p> <pre xml:space="preserve" class="oac_no_warn">P7_PREV_PAGE </pre> </li> <li> <p>For Region, select <span class="bold">Issue Identification</span>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>Click <span class="bold">Create Item</span>. </p> <p>Next, edit the Cancel button. </p> </li> <li> <p>Under Buttons, select <span class="bold">Cancel</span>. </p> </li> <li> <p>Scroll down to Optional URL Redirect. </p> </li> <li> <p>In Page, enter: </p> <pre xml:space="preserve" class="oac_no_warn">&P7_PREV_PAGE. </pre> <p>Note the period at the end. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> <p>Next, edit the branch. </p> </li> <li> <p>Under Branching, select the <span class="bold">After Processing</span> branch. </p> </li> <li> <p>Under Branch Action, enter the following in Page: </p> <pre xml:space="preserve" class="oac_no_warn">&P7_PREV_PAGE. </pre> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref262" name="sthref262"></a> <h5>Add Functionality to Support Adding Multiple Issues Sequentially </h5> <p>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. </p> <p>To add a new button: </p> <ol type="1" start="1"> <li> <p>Under the Buttons section, click the <span class="bold">Copy</span> icon. </p> </li> <li> <p>Under Name, select <span class="bold">CREATE</span>. </p> </li> <li> <p>For Target Page, accept the default <span class="bold">7</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Button Name, enter <code>CREATE_AGAIN</code>. </p> </li> <li> <p>For Label, enter <code>Create and Create Another</code>. </p> </li> <li> <p>Click <span class="bold">Copy Button</span>. </p> </li> </ol> <p>Functionally, the Copy Button currently works the same as the CREATE button. Next, create a branch that keeps the user on the create page. </p> <p>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. </p> <p>To create a branch that keeps the user on the create page: </p> <ol type="1" start="1"> <li> <p>Under Branching, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>On Identify Branch Point and Type, accept the defaults and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Target: </p> <ol type="a"> <li> <p>For Page, enter <code>7</code>. </p> </li> <li> <p>For Clear Cache, enter <code>7</code>. </p> </li> <li> <p>For Set these items, enter: </p> <pre xml:space="preserve" class="oac_no_warn">P7_PREV_PAGE </pre> </li> <li> <p>For With these values, enter: </p> <pre xml:space="preserve" class="oac_no_warn">&P7_PREV_PAGE. </pre> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>On Identify Branch Conditions: </p> <ol type="a"> <li> <p>For Sequence, enter 0. </p> </li> <li> <p>For When Button Pressed, select <span class="bold">CREATE_AGAIN</span>. </p> </li> </ol> </li> <li> <p>Click <span class="bold">Create Branch</span>. </p> </li> <li> <p>Under Branching, select the newly created branch. </p> </li> <li> <p>Under Branch Action, select <span class="bold">include process success message</span>. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> <p>To see the changes, click the <span class="bold">Run</span> <span class="bold">Page</span> icon. (See <a href="#BABCIHFH">Figure 10-9</a>.) </p> <div class="figure"> <a id="BABCIHFH" name="BABCIHFH"></a><a id="sthref263" name="sthref263"></a> <p class="titleinfigure">Figure 10-9 Create/Edit Issues Form</p> <img src="./img/iss_edit1.gif" alt="Description of iss_edit1.gif follows" title="Description of iss_edit1.gif follows"/> <br /><a id="sthref264" name="sthref264" href="./img_text/iss_edit1.htm">Description of the illustration iss_edit1.gif</a> <br /><br /> </div><!-- class="figure" --> <p>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. </p> </li> </ol> </div><!-- class="sect4" --> </div><!-- class="sect3" --> <a id="BJFFDHAE" name="BJFFDHAE"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref265" name="sthref265"></a> <h4>Refine the Issues Report </h4> <p>Next, you will refine the Issues report page to support dynamic modification of the query. To accomplish this, you must: </p> <ul> <li type="disc"> <p>Move the Create button to a new region and edit the label </p> </li> <li type="disc"> <p>Create new items that will enable the user to restrict the query </p> </li> <li type="disc"> <p>Add a <code>WHERE</code> clause to reference those new items </p> </li> <li type="disc"> <p>Alter the report column attributes to display each person's name and the project </p> </li> <li type="disc"> <p>Modify headings </p> </li> </ul> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref266" name="sthref266"></a> <h5>Move Create Button to a New Region </h5> <p>To create a new region of the Create button: </p> <ol type="1" start="1"> <li> <p>Navigate to the Page Definition for page 6, Issues. </p> </li> <li> <p>Under the Regions section, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Select a common region type, accept the default of <span class="bold">HTML</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Region Type, select <span class="bold">HTML</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Region Attributes: </p> <ol type="a"> <li> <p>For Title, enter <code>Buttons</code>. </p> </li> <li> <p>For Region Template, select <span class="bold">Button Region without Title</span>. </p> </li> <li> <p>For Display Point, select <span class="bold">Page Template Body (2. items below region content)</span>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>Click <span class="bold">Create Region</span>. </p> </li> </ol> <p>To move the Create button to the Buttons region: </p> <ol type="1" start="1"> <li> <p>Under Buttons, select the <span class="bold">CREATE</span> button. </p> </li> <li> <p>In Text Label, enter: </p> <pre xml:space="preserve" class="oac_no_warn">Add a New Issue </pre> </li> <li> <p>From Display in Region, select <span class="bold">Buttons</span>. </p> </li> <li> <p>Under Optional URL Redirect: </p> <ol type="a"> <li> <p>For Set These Items, enter: </p> <pre xml:space="preserve" class="oac_no_warn">P7_PREV_PAGE </pre> </li> <li> <p>For With These Values, enter <code>6</code>. </p> </li> </ol> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref267" name="sthref267"></a> <h5>Alter the Query and Display </h5> <p>Next, alter the query to display the actual values for people and projects instead of the ID and then clean up the report display. </p> <p>To edit column attributes for ISSUE_ID: </p> <ol type="1" start="1"> <li> <p>Under the Regions section, select <span class="bold">Q</span> to the left of Issues. </p> </li> <li> <p>Click the <span class="bold">Edit Icon</span> to the left of ISSUE_ID. </p> </li> <li> <p>Scroll down to Column Link. </p> <ol type="a"> <li> <p>For Item 2, for Name enter: </p> <pre xml:space="preserve" class="oac_no_warn">P7_PREV_PAGE </pre> </li> <li> <p>For Item 2, for Value enter 6. </p> </li> </ol> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> <p>To edit column attributes for IDENTIFIED_BY, RELATED_PROJECT, and ASSIGNED_TO: </p> <ol type="1" start="1"> <li> <p>Click the <span class="bold">Edit Icon</span> to the left of IDENTIFIED_BY. </p> </li> <li> <p>Scroll down to Tabular Form Element. From the Display As list, select <span class="bold">Display as Text (based on LOV, does not save state).</span> </p> </li> <li> <p>Under the List of Values section, for <span class="bold">Named LOV</span>, select <span class="bold">PEOPLE</span>. </p> </li> <li> <p>Return to the top of the page and click the <span class="bold">Next</span> (>) icon. </p> <p>The Column Attributes page for RELATED_PROJECT appears. </p> </li> <li> <p>Scroll down to Tabular Form Element. From the Display As list, select <span class="bold">Display as Text (based on LOV, does not save state).</span> </p> </li> <li> <p>Under List of Values: </p> <ol type="a"> <li> <p>For Named LOV, select <span class="bold">PROJECTS</span>. </p> </li> <li> <p>For Display Null, select <span class="bold">Yes</span>. </p> </li> <li> <p>In Null Text, enter a hyphen (-). </p> </li> </ol> </li> <li> <p>Return to the top of the page and click the <span class="bold">Next</span> (>) icon. </p> <p>The Column Attributes page for ASSIGNED_TO appears. </p> </li> <li> <p>Scroll down to Tabular Form Element. From the Display As list, select <span class="bold">Display as Text (based on LOV, does not save state).</span> </p> </li> <li> <p>Under List of Values: </p> <ol type="a"> <li> <p>For Named LOV, select <span class="bold">PEOPLE</span>. </p> </li> <li> <p>For Display Null, select <span class="bold">Yes</span>. </p> </li> <li> <p>In Null Text, enter a hyphen (-). </p> </li> </ol> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> <p>Next, you will customize how the report displays by changing report attributes. </p> <p>To alter the report display: </p> <ol type="1" start="1"> <li> <p>For ISSUE_ID, delete the Heading <span class="bold">Edit</span>. </p> </li> <li> <p>For ISSUE_SUMMARY, change Heading to <code>Summary</code>. </p> </li> <li> <p>For TARGET_RESOLUTION_DATE: </p> <ol type="a"> <li> <p>Force the heading to wrap. In Heading, enter: </p> <pre xml:space="preserve" class="oac_no_warn">Target<br>Resolution<br>Date </pre> </li> <li> <p>For Column Align., select <span class="bold">center</span>. </p> </li> <li> <p>For Heading Align. select <span class="bold">center</span>. </p> </li> </ol> </li> <li> <p>For all columns except ISSUE_ID, check <span class="bold">Sort</span>. </p> </li> <li> <p>For ISSUE_SUMMARY, select <span class="bold">1</span> for Sort Sequence. </p> </li> <li> <p>Scroll down to Layout and Pagination: </p> <ol type="a"> <li> <p>For Show Null Values as, enter a hyphen (-). </p> </li> <li> <p>For Number of Rows, enter <code>5</code>. </p> </li> </ol> </li> <li> <p>Under Sorting, select the light gray arrow for Ascending and Descending Image. </p> </li> <li> <p>Under Messages, enter the following in When No Data Found Message: </p> <pre xml:space="preserve" class="oac_no_warn">No issues found. </pre> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref268" name="sthref268"></a> <h5>Add Support of Filtering </h5> <p>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. </p> <p>To create a new region: </p> <ol type="1" start="1"> <li> <p>Under the Regions section, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Select a common region type, accept the default of <span class="bold">HTML</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Region Type, select <span class="bold">HTML</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Region Attributes: </p> <ol type="a"> <li> <p>For Title, enter <code>Issue Report Parameters</code>. </p> </li> <li> <p>For Region Template, select accept the default of <span class="bold">Reports Region</span>. </p> </li> <li> <p>For Sequence, enter <code>5</code>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>Click <span class="bold">Create Region</span>. </p> </li> </ol> <p>Next, create the items. </p> <p>To create the item for Identified By: </p> <ol type="1" start="1"> <li> <p>Under Items, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Choose Item Type, select <span class="bold">Select List</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Pick Select List Control Type, accept the default selection <span class="bold">Select List</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Item Name and Display Position: </p> <ol type="a"> <li> <p>For Item Name, enter <code>P6_IDENTIFIED_BY</code>. </p> </li> <li> <p>For Region, select <span class="bold">Issue Report Parameters</span>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>On Identify List of Values: </p> <ol type="a"> <li> <p>For Named LOV, select <span class="bold">PEOPLE</span>. </p> </li> <li> <p>For Null Text, enter: </p> <pre xml:space="preserve" class="oac_no_warn">- All - </pre> </li> <li> <p>For Null Value, enter: </p> <pre xml:space="preserve" class="oac_no_warn">-1 </pre> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>For Identify Item Attributes, accept the defaults and click <span class="bold">Next</span>. </p> </li> <li> <p>For Default, enter: </p> <pre xml:space="preserve" class="oac_no_warn">-1 </pre> </li> <li> <p>Click <span class="bold">Create Item</span>. </p> </li> </ol> <p>To create an item for Assigned To. </p> <ol type="1" start="1"> <li> <p>Under Items, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Choose Item Type, select <span class="bold">Select List</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Pick Select List Control Type, accept the default selection <span class="bold">Select List</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Item Name and Display Position: </p> <ol type="a"> <li> <p>For Item Name, enter <code>P6_ASSIGNED_TO</code>. </p> </li> <li> <p>For Region, select <span class="bold">Issue Report Parameters</span>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>On Identify List of Values: </p> <ol type="a"> <li> <p>For Named LOV, select <span class="bold">PEOPLE</span>. </p> </li> <li> <p>For Null Text, enter: </p> <pre xml:space="preserve" class="oac_no_warn">- All - </pre> </li> <li> <p>For Null Value, enter: </p> <pre xml:space="preserve" class="oac_no_warn">-1 </pre> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>For Identify Item Attributes, accept the defaults and click <span class="bold">Next</span>. </p> </li> <li> <p>For Default, enter: </p> <pre xml:space="preserve" class="oac_no_warn">-1 </pre> </li> <li> <p>Click <span class="bold">Create Item</span>. </p> </li> </ol> <p>To create an item for Status. </p> <ol type="1" start="1"> <li> <p>Under Items, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Choose Item Type, select <span class="bold">Select List</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Pick Select List Control Type, accept the default selection <span class="bold">Select List</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Item Name and Display Position: </p> <ol type="a"> <li> <p>For Item Name, enter <code>P6_STATUS</code>. </p> </li> <li> <p>For Region, select <span class="bold">Issue Report Parameters</span>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>On Identify List of Values: </p> <ol type="a"> <li> <p>For Named LOV, select <span class="bold">STATUS</span>. </p> </li> <li> <p>For Null Text, enter: </p> <pre xml:space="preserve" class="oac_no_warn">- All - </pre> </li> <li> <p>For Null Value, enter: </p> <pre xml:space="preserve" class="oac_no_warn">-1 </pre> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>For Identify Item Attributes, accept the defaults and click <span class="bold">Next</span>. </p> </li> <li> <p>For Default, enter: </p> <pre xml:space="preserve" class="oac_no_warn">-1 </pre> </li> <li> <p>Click <span class="bold">Create Item</span>. </p> </li> </ol> <p>To create an item for Priority. </p> <ol type="1" start="1"> <li> <p>Under Items, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Choose Item Type, select <span class="bold">Select List</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Pick Select List Control Type, accept the default selection <span class="bold">Select List</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Item Name and Display Position: </p> <ol type="a"> <li> <p>For Item Name, enter <code>P6_PRIORITY</code>. </p> </li> <li> <p>For Region, select <span class="bold">Issue Report Parameters</span>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>On Identify List of Values: </p> <ol type="a"> <li> <p>For Named LOV, select <span class="bold">PRIORITIES</span>. </p> </li> <li> <p>For Null Text, enter: </p> <pre xml:space="preserve" class="oac_no_warn">- All - </pre> </li> <li> <p>For Null Value, enter: </p> <pre xml:space="preserve" class="oac_no_warn">-1 </pre> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>For Identify Item Attributes, accept the defaults and click <span class="bold">Next</span>. </p> </li> <li> <p>For Default, enter: </p> <pre xml:space="preserve" class="oac_no_warn">-1 </pre> </li> <li> <p>Click <span class="bold">Create Item</span>. </p> </li> </ol> <p>To create an item for Related Project. </p> <ol type="1" start="1"> <li> <p>Under Items, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Choose Item Type, select <span class="bold">Select List</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Pick Select List Control Type, accept the default selection <span class="bold">Select List</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Item Name and Display Position: </p> <ol type="a"> <li> <p>For Item Name, enter <code>P6_RELATED_PROJECT</code>. </p> </li> <li> <p>For Region, select <span class="bold">Issue Report Parameters</span>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>On Identify List of Values: </p> <ol type="a"> <li> <p>For Named LOV, select <span class="bold">PRIORITIES</span>. </p> </li> <li> <p>For Null Text, enter: </p> <pre xml:space="preserve" class="oac_no_warn">- All - </pre> </li> <li> <p>For Null Value, enter: </p> <pre xml:space="preserve" class="oac_no_warn">-1 </pre> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>For Identify Item Attributes, accept the defaults and click <span class="bold">Next</span>. </p> </li> <li> <p>For Default, enter: </p> <pre xml:space="preserve" class="oac_no_warn">-1 </pre> </li> <li> <p>Click <span class="bold">Create Item</span>. </p> </li> </ol> <p>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. </p> <p>To create Go button: </p> <ol type="1" start="1"> <li> <p>Under Buttons, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Select a region for the button, select <span class="bold">Issue Report Parameters</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Task, select <span class="bold">Create a button displayed among this region's items</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Button Name, enter <code>P6_GO</code>. </p> </li> <li> <p>For Button Style, select <span class="bold">Template Based Button</span>. </p> </li> <li> <p>For <span class="bold">Template</span>, select <span class="bold">Button</span>. </p> </li> <li> <p>Click <span class="bold">Create Button</span>. </p> </li> </ol> <p>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. </p> <p>To change the position of 6_RELATED_PROJECT, P6_STATUS, and P6_PRIORITY: </p> <ol type="1" start="1"> <li> <p>Under the Items section, click the heading <span class="bold">Items</span>. </p> </li> <li> <p>For P6_RELATED_PROJECT, P6_STATUS, and P6_PRIORITY, select <span class="bold">No</span> for New Line. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> <li> <p>Click the Edit Page icon in the upper right corner to return to the Page Definition for page 6. </p> </li> </ol> <p>Next, you need to modify the report to react to the parameters. To accomplish this, you need to modify the <code>WHERE</code> clause of the query. One approach would be add the following <code>WHERE</code> clause. For example: </p> <pre xml:space="preserve" class="oac_no_warn">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') </pre> <p>Although this is a valid approach, the query will execute faster if you generate it with the <code>WHERE</code> clause that needs it. To accomplish this, turn the Issues region into a PL/SQL Function Body Returning a SQL Query. </p> <p>To turn the Issues region into a PL/SQL Function Body Returning a SQL Query: </p> <ol type="1" start="1"> <li> <p>Under the Regions section, select <span class="bold">Issues</span>. </p> </li> <li> <p>For Type, select <span class="bold">SQL Query (Pl/Sql Function Body Returning Sql Query)</span>. </p> </li> <li> <p>For Region Source, enter the following: </p> <pre xml:space="preserve" class="oac_no_warn">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; </pre> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> <p>Note that this function first sets the variable <code>q</code> to the original <code>SELECT</code> statement. It then builds <code>WHERE</code> clause (<code>w</code>) composed of just the variables set by the user. If any variables have been set, it appends the WHERE clause to the original <code>SELECT</code> and passes that new SELECT to the database. </p> <p>The report is now complete. Click the <span class="bold">Run Page</span> icon. (See <a href="#BABHGDCE">Figure 10-10</a>). </p> <div class="figure"> <a id="BABHGDCE" name="BABHGDCE"></a><a id="sthref269" name="sthref269"></a> <p class="titleinfigure">Figure 10-10 Issues Report</p> <img src="./img/iss_add_iss.gif" alt="Description of iss_add_iss.gif follows" title="Description of iss_add_iss.gif follows"/> <br /><a id="sthref270" name="sthref270" href="./img_text/iss_add_iss.htm">Description of the illustration iss_add_iss.gif</a> <br /><br /> </div><!-- class="figure" --> <p>To change the report parameters, make new selections under Issue Report Parameters and click <span class="bold">Go</span>. </p> </div><!-- class="sect4" --> </div><!-- class="sect3" --> <a id="BJFBHJEF" name="BJFBHJEF"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref271" name="sthref271"></a> <h4>Add a Page to Support Assigning Multiple Issues Simultaneously </h4> <p>Currently, you can assign an issue by editing it. Next, you will add a new page that will enable users to assign multiple issues at once and modify the Related Project, Status and Priority. </p> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref272" name="sthref272"></a> <h5>Create a Tabular Form </h5> <p>To add a new page to support assigning multiple issues: </p> <ol type="1" start="1"> <li> <p>Navigate to the Application Builder home page. </p> </li> <li> <p>Click <span class="bold">Create Page</span>. </p> </li> <li> <p>Select <span class="bold">Page with Component</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>Select <span class="bold">Form</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>Select <span class="bold">Tabular Form</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Table/View Owner, select the appropriate schema. </p> <p>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. </p> </li> <li> <p>To enforce this assumption, select <span class="bold">Update Only</span> from the Allowed Operations list and click <span class="bold">Next</span>. </p> </li> <li> <p>For Table/View Name, select <span class="bold">HT_ISSUES</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Select Column(s), select the following columns and click <span class="bold">Next</span>: </p> <ul> <li type="disc"> <p>ISSUE_SUMMARY </p> </li> <li type="disc"> <p>IDENTIFIED_BY </p> </li> <li type="disc"> <p>IDENTIFIED_DATE </p> </li> <li type="disc"> <p>RELATED_PROJECT </p> </li> <li type="disc"> <p>ASSIGNED_TO </p> </li> <li type="disc"> <p>STATUS </p> </li> <li type="disc"> <p>PRIORITY </p> </li> </ul> </li> <li> <p>For Primary Key Column 1, accept the default <span class="bold">ISSUE_ID</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Primary Key Source Type, accept the default <span class="bold">Existing trigger</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Updatable Column(s), select the following and click <span class="bold">Next</span>: </p> <ul> <li type="disc"> <p>RELATED_PROJECT </p> </li> <li type="disc"> <p>ASSIGNED_TO </p> </li> <li type="disc"> <p>STATUS </p> </li> <li type="disc"> <p>PRIORITY </p> </li> </ul> </li> <li> <p>On Identify Page and Region Attributes: </p> <ol type="a"> <li> <p>For Page, enter <code>8</code>. </p> </li> <li> <p>For Page Name, enter <code>Assign Issues</code>. </p> </li> <li> <p>For Region Title, enter <code>Assign Issues</code>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>For <span class="bold">Tab Options</span>, accept the default <span class="bold">Do not use tabs</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Button Labels: </p> <ol type="a"> <li> <p>For Cancel Button Label, accept the default. </p> </li> <li> <p>For Submit Button Label, enter <code>Apply Changes</code>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>For Branching, accept the defaults and click <span class="bold">Next</span>. </p> </li> <li> <p>Review your selections and click <span class="bold">Finish</span>. </p> </li> </ol> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref273" name="sthref273"></a> <h5>Add Lists of Values </h5> <p>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. </p> <p>To add lists of values: </p> <ol type="1" start="1"> <li> <p>From the Success page, select <span class="bold">Edit Page</span>. </p> <p>The Page Definition for page 8, Assign Issues, appears. </p> </li> <li> <p>Under Regions, select <span class="bold">Assign Issues</span>. </p> </li> <li> <p>In <span class="bold">Region Source</span>, enter the following: </p> </li> </ol> <pre xml:space="preserve" class="oac_no_warn">SELECT "ISSUE_ID", "ISSUE_SUMMARY", "IDENTIFIED_BY", "IDENTIFIED_DATE", "RELATED_PROJECT", "ASSIGNED_TO", "STATUS", "PRIORITY" FROM "#OWNER#"."HT_ISSUES" WHERE assigned_to IS NULL </pre> <p>To edit report attributes: </p> <ol type="1" start="1"> <li> <p>Select the <span class="bold">Report Attributes</span> tab. </p> </li> <li> <p>Under Report Column Attributes: </p> <ol type="a"> <li> <p>For ISSUE_SUMMARY, edit the existing Heading to read: </p> <pre xml:space="preserve" class="oac_no_warn">Summary </pre> </li> <li> <p>For all columns except ISSUE_ID, select <span class="bold">Sort</span>. </p> </li> <li> <p>For IDENTIFIED_DATE, for <span class="bold">Sort Sequence</span>, select <span class="bold">1</span>. </p> </li> </ol> </li> <li> <p>Click the <span class="bold">Edit</span> icon to the left of IDENTIFIED_BY and edit the following attributes: </p> <ol type="a"> <li> <p>Scroll down to Tabular Form Element. From the Display As list, select <span class="bold">Display as Text (based on LOV, does not save state)</span>. </p> </li> <li> <p>Under List of Values, select <span class="bold">PEOPLE</span> from the Named LOV list. </p> </li> <li> <p>Click the Next button (>) at the top of the page to navigate to the next column. </p> </li> </ol> </li> <li> <p>Edit the following attributes for IDENTIFIED_DATE: </p> <ol type="a"> <li> <p>Under Column Formatting, enter <code>DD-MON-YYYY</code> in Number/Date Format. </p> </li> <li> <p>Click the Next button (>) at the top of the page to navigate to the next column. </p> </li> </ol> </li> <li> <p>Edit the following attributes for RELATED_PROJECT: </p> <ol type="a"> <li> <p>Scroll down to Tabular Form Element. From Display As, select <span class="bold">Select List (Named LOV)</span>. </p> </li> <li> <p>Under List of Values, select <span class="bold">PROJECTS</span> from the Named LOV list. </p> </li> <li> <p>Click the Next button (>) at the top of the page to navigate to the next column. </p> </li> </ol> </li> <li> <p>Edit the following attributes for ASSIGNED_TO: </p> <ol type="a"> <li> <p>Scroll down to Tabular Form Element. From Display As, select <span class="bold">Select List (Named LOV)</span>. </p> </li> <li> <p>Under List of Values: </p> <ul> <li type="circle"> <p>From the Named LOV list, select <span class="bold">PEOPLE</span>. </p> </li> <li type="circle"> <p>For Display Null, select <span class="bold">Yes</span>. </p> </li> <li type="circle"> <p>For Null Text, enter a hyphen (-). </p> </li> </ul> </li> <li> <p>Click the Next button (>) at the top of the page to navigate to the next column. </p> </li> </ol> </li> <li> <p>Edit the following attributes for STATUS: </p> <ol type="a"> <li> <p>Scroll down to Tabular Form Element. From Display As, select <span class="bold">Select List (Named LOV)</span>. </p> </li> <li> <p>Under List of Values select <span class="bold">STATUS</span> from the Named LOV list. </p> </li> <li> <p>Click the Next button (>) at the top of the page to navigate to the next column. </p> </li> </ol> </li> <li> <p>Edit the following attributes for PRIORITY: </p> <ol type="a"> <li> <p>Scroll down to Tabular Form Element. From Display As, select <span class="bold">Select List (Named LOV)</span>. </p> </li> <li> <p>Under List of Values: </p> <ul> <li type="circle"> <p>From Named LOV, select <span class="bold">PRIORITIES</span>. </p> </li> <li type="circle"> <p>For Display Null, select <span class="bold">Yes</span>. </p> </li> <li type="circle"> <p>For Null Text, enter a hyphen (-). </p> </li> </ul> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> <p>The Report Attributes page appears. </p> </li> <li> <p>Scroll down to Sorting. Under Ascending and Descending Image, select the light gray arrow. </p> </li> <li> <p>Under Messages, enter the following in When No Data Found Message: </p> <pre xml:space="preserve" class="oac_no_warn">No Unassigned Issues. </pre> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> <p>The wizard created an unnecessary Cancel button. </p> <p>To delete the Cancel button: </p> <ol type="1" start="1"> <li> <p>On the Page Definition for page 8, select the <span class="bold">CANCEL</span> button. </p> </li> <li> <p>Click <span class="bold">Delete</span>. </p> </li> <li> <p>Click <span class="bold">OK</span> to confirm your selection. </p> </li> </ol> <p>The tabular form is now complete. To view the new form, click the <span class="bold">Run Page</span> icon. (See <a href="#BABJEACH">Figure 10-11</a>.) </p> <div class="figure"> <a id="BABJEACH" name="BABJEACH"></a><a id="sthref274" name="sthref274"></a> <p class="titleinfigure">Figure 10-11 Assign Issues</p> <img src="./img/iss_assiss.gif" alt="Description of iss_assiss.gif follows" title="Description of iss_assiss.gif follows"/> <br /><a id="sthref275" name="sthref275" href="./img_text/iss_assiss.htm">Description of the illustration iss_assiss.gif</a> <br /><br /> </div><!-- class="figure" --> <p>To assign an issue, make a selection from the Assigned To list and click <span class="bold">Apply Changes</span>. Notice that once an issue has been assigned, the issue no longer displays. </p> </div><!-- class="sect4" --> </div><!-- class="sect3" --> </div><!-- class="sect2" --> <a id="BJFIBIBD" name="BJFIBIBD"></a> <div class="sect2"> <!-- infolevel=all infotype=General --><a id="sthref276" name="sthref276"></a> <h3>Create Summary Reports </h3> <p>Lastly, you will add four summary reports. </p> <p class="subhead2">Topics:</p> <p>Topics in this section include: </p> <ul> <li type="disc"> <p><a href="#BJFGHGAH">Add a Issue Summary by Project Report</a> </p> </li> <li type="disc"> <p><a href="#BJFGFEJI">Add Resolved by Month Identified</a> </p> </li> <li type="disc"> <p><a href="#BJFGBJIC">Add Target Resolution Dates</a> </p> </li> <li type="disc"> <p><a href="#BJFGJBBI">Add Average Days to Resolve</a> </p> </li> </ul><a id="BJFGHGAH" name="BJFGHGAH"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref277" name="sthref277"></a> <h4>Add a Issue Summary by Project Report </h4> <p>The Issue Summary report enable users to select a project and then see a summary of issues related to that project. This report includes the following summary information: </p> <ul> <li type="disc"> <p>Date first issue identified </p> </li> <li type="disc"> <p>Date last issue closed </p> </li> <li type="disc"> <p>Total number of issues </p> </li> <li type="disc"> <p>Number of issues by status </p> </li> <li type="disc"> <p>Number of open issues by priority </p> </li> <li type="disc"> <p>Assignments by status </p> </li> </ul> <p>To create this report, you will code the information in two SQL statements. The first statement gathers information having a a singular result and the second statement gathers information having multiple results. </p> <p>To add an Issue Summary by Project report: </p> <ol type="1" start="1"> <li> <p>Navigate to the Application Builder home page. </p> </li> <li> <p>Click <span class="bold">Create Page</span>. </p> </li> <li> <p>Select <span class="bold">Page with Component</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Select Component Type, select <span class="bold">Report</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>Select <span class="bold">SQL Report</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Page Attributes: </p> <ol type="a"> <li> <p>For Page, enter <code>9</code>. </p> </li> <li> <p>For Page Name, enter <code>Issue Summary by Project</code>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>For Tab Options, accept the default <span class="bold">Do not use tabs</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>Enter the following <span class="bold">SQL SELECT</span> statement and click <span class="bold">Next</span>: </p> <pre xml:space="preserve" class="oac_no_warn">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 ht_issues WHERE related_project = :P9_PROJECT </pre> </li> <li> <p>On Report Attributes: </p> <ol type="a"> <li> <p>For Region Name, enter <code>Issue Summary by Project</code>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>Review your selections and click <span class="bold">Finish</span>. </p> </li> </ol> <p>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 and that will contain the Project parameter. </p> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref278" name="sthref278"></a> <h5>Create a New Region </h5> <p>To create a new region to display above the report: </p> <ol type="1" start="1"> <li> <p>From the Success page, click <span class="bold">Edit Page</span>. </p> <p>The Page Definition for page 9, Issue Summary by Project appears. </p> </li> <li> <p>Under the Regions section, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Select a common region type, accept the default of <span class="bold">HTML</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Region Type, select <span class="bold">HTML</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Region Attributes: </p> <ol type="a"> <li> <p>For Title, enter <code>Issue Summary Report Parameters</code>. </p> </li> <li> <p>For Display Point, select <span class="bold">Page Template Body (2. items below region content)</span>. </p> </li> <li> <p>For Sequence, enter <code>5</code>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>Click <span class="bold">Create Region</span>. </p> </li> </ol> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref279" name="sthref279"></a> <h5>Create the Project Item </h5> <p>To create the Project item: </p> <ol type="1" start="1"> <li> <p>Under Items, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Choose Item Type, select <span class="bold">Select List</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Pick Select List Control Type, accept the default <span class="bold">Select List</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Item Name and Display Position: </p> <ol type="a"> <li> <p>For Item Name, enter <code>P9_PROJECT</code>. </p> </li> <li> <p>For Region, select <span class="bold">Issue Summary Report Parameters</span>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>On Identify List of Values: </p> <ol type="a"> <li> <p>For Named LOV, select <span class="bold">PROJECTS</span>. </p> </li> <li> <p>For Null Text, enter: </p> <pre xml:space="preserve" class="oac_no_warn">- Select - </pre> </li> <li> <p>For <span class="bold">Null Value</span>, enter <code>-1</code>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>On Identify Item Attributes, accept the defaults and click <span class="bold">Next</span>. </p> </li> <li> <p>For Default, enter <code>-1</code>. </p> </li> <li> <p>Click <span class="bold">Create Item</span>. </p> </li> </ol> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref280" name="sthref280"></a> <h5>Create a Go Button </h5> <p>To create a Go button to execute the query: </p> <ol type="1" start="1"> <li> <p>Under Buttons, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Select a Region, select <span class="bold">Issue Summary Report Parameters</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Task, select <span class="bold">Create a button displayed among this region's items</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Button within Items Attributes: </p> <ol type="a"> <li> <p>For Button Name, enter <code>P9_GO</code>. </p> </li> <li> <p>For Button Style, select <span class="bold">Template Based Button</span>. </p> </li> <li> <p>For Template, select <span class="bold">Button</span>. </p> </li> </ol> </li> <li> <p>Click <span class="bold">Create Button</span>. </p> </li> </ol> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref281" name="sthref281"></a> <h5>Edit Headings and Report Settings </h5> <p> 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. </p> <p>To edit the headings and report settings: </p> <ol type="1" start="1"> <li> <p>Under Regions, select the <span class="bold">Q</span> adjacent to <span class="bold">Issue Summary by Project</span>. </p> </li> <li> <p>For <span class="bold">Headings Type</span>, select <span class="bold">Custom</span>. </p> </li> <li> <p>Under Report Column Attributes: </p> <ol type="a"> <li> <p>Change the Heading for FIRST_IDENTIFIED to: </p> <pre xml:space="preserve" class="oac_no_warn">First Issue Identified: </pre> </li> <li> <p>Change the Heading for LAST_CLOSED to: </p> <pre xml:space="preserve" class="oac_no_warn">Last Issue Closed: </pre> </li> <li> <p>Change the Heading for TOTAL_ISSUES to:<span class="bold"></span> </p> <pre xml:space="preserve" class="oac_no_warn">Total Issues: </pre> </li> <li> <p>Change the Heading for OPEN_ISSUES to: </p> <pre xml:space="preserve" class="oac_no_warn">Open Issues: </pre> </li> <li> <p>Change the Heading for ONHOLD_ISSUES to: </p> <pre xml:space="preserve" class="oac_no_warn">On-Hold Issues: </pre> </li> <li> <p>Change the Heading for CLOSED_ISSUES to: </p> <pre xml:space="preserve" class="oac_no_warn">Closed Issues: </pre> </li> <li> <p>Change the Heading for OPEN_NO_PRIOR to: </p> <pre xml:space="preserve" class="oac_no_warn">Open Issues with No Priority: </pre> </li> <li> <p>Change the Heading for OPEN_HIGH_PRIOR: </p> <pre xml:space="preserve" class="oac_no_warn">Open Issues of High Priority: </pre> </li> <li> <p>Change the Heading for OPEN_MEDIUM_PRIOR to: </p> <pre xml:space="preserve" class="oac_no_warn">Open Issues of Medium Priority: </pre> </li> <li> <p>Change the Heading for OPEN_LOW_PRIOR: </p> <pre xml:space="preserve" class="oac_no_warn">Open Issues of Low Priority: </pre> </li> </ol> </li> <li> <p>Under the Layout and Pagination section: </p> <ol type="a"> <li> <p>For Show Null Values as, enter a hyphen (-). </p> </li> <li> <p>For Pagination Scheme, select <span class="bold">- No Pagination Selected -</span>. </p> </li> </ol> </li> <li> <p>Select the <span class="bold">Region Definition</span> tab at the top of the page. </p> <ol type="a"> <li> <p>Scroll down to Conditional Display. </p> </li> <li> <p>For Condition Type, select <span class="bold">Value of Item in Expression 1 Is NOT Contained within Colon Delimited List in Expression 2.</span> </p> </li> <li> <p>In Expression 1, enter <code>P9_PROJECT</code>. </p> </li> <li> <p>For Expression 2, enter <code>-1</code>. </p> </li> </ol> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref282" name="sthref282"></a> <h5>Create a Query to Retrieve Assignments </h5> <p>To create a query to retrieve assignments by status. </p> <ol type="1" start="1"> <li> <p>Under the Regions section, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Select a common region type, select <span class="bold">Report</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Report Implementation, select <span class="bold">SQL Report</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Region Attributes: </p> <ol type="a"> <li> <p>For Title, enter <code>Assignments by Status</code>. </p> </li> <li> <p>For Column, select <span class="bold">2</span>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>On Page Region Source: </p> <ol type="a"> <li> <p>In <span class="bold">Enter SQL Query</span>, enter: </p> <pre xml:space="preserve" class="oac_no_warn">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<span class="italic"></span> </pre> </li> <li> <p>For Rows Per Page, enter <code>20</code>. </p> </li> <li> <p>For Break Columns, select <span class="bold">Column 1</span>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>On Identify Display Conditions: </p> <ol type="a"> <li> <p>From Condition Type, select <span class="bold">Value of Item in Expression 1 Is NOT Contained within Colon Delimited List in Expression 2</span>. </p> </li> <li> <p>In Expression 1 enter: </p> <pre xml:space="preserve" class="oac_no_warn">P9_PROJECT </pre> </li> <li> <p>For Expression 2 enter -1. </p> </li> </ol> </li> <li> <p>Click <span class="bold">Create Region</span>. </p> </li> </ol> <p>To edit headings and report settings: </p> <ol type="1" start="1"> <li> <p>Under Regions, select <span class="bold">Q</span> adjacent to <span class="bold">Assignments by Status</span>. </p> </li> <li> <p>For Headings Type, select <span class="bold">Custom</span>. </p> </li> <li> <p>For PERSON_NAME, change Heading to <code>Assigned To</code>. </p> </li> <li> <p>Scroll down to Layout and Pagination. From Pagination Scheme, select <span class="bold">Row Ranges 1-15 16-30 in select list</span>. </p> </li> <li> <p>Under Messages, enter the following in When No Data Found Message: </p> <pre xml:space="preserve" class="oac_no_warn">No Issues found. </pre> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> <p>To see your newly created report, click the <span class="bold">Run Page</span> icon. Note that initially no data displays since no project is selected. Select a project. Your report should resemble <a href="#BABBJCFE">Figure 10-12</a>. </p> <div class="figure"> <a id="BABBJCFE" name="BABBJCFE"></a><a id="sthref283" name="sthref283"></a> <p class="titleinfigure">Figure 10-12 Issue Summary by Project Report</p> <img src="./img/iss_sumrpt.gif" alt="Description of iss_sumrpt.gif follows" title="Description of iss_sumrpt.gif follows"/> <br /><a id="sthref284" name="sthref284" href="./img_text/iss_sumrpt.htm">Description of the illustration iss_sumrpt.gif</a> <br /><br /> </div><!-- class="figure" --> </div><!-- class="sect4" --> </div><!-- class="sect3" --> <a id="BJFGFEJI" name="BJFGFEJI"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref285" name="sthref285"></a> <h4>Add Resolved by Month Identified </h4> <p>The Resolved by Month Identified report is a line chart. This report first calculates the number of days it took to resolve each closed issue, averaged by the month the issue was identified, and finally displayed by the month. </p> <p>To add a Resolved by Month Identified report: </p> <ol type="1" start="1"> <li> <p>Navigate to the Application Builder home page. </p> </li> <li> <p>Click <span class="bold">Create Page</span>. </p> </li> <li> <p>Select <span class="bold">Page with Component</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>Select <span class="bold">Chart</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>Select <span class="bold">Line</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Page Attributes: </p> <ol type="a"> <li> <p>For Page, enter <code>10</code>. </p> </li> <li> <p>For Page Name and Region Name, enter <code>Resolved by Month Identified</code>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>For Tab Options, accept the default <span class="bold">Do not use tabs</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Series Query: </p> <ol type="a"> <li> <p>For Series Name, enter <code>Resolved</code>. </p> </li> <li> <p>In <span class="bold">SQL,</span> enter: </p> <pre xml:space="preserve" class="oac_no_warn">SELECT NULL l, TO_CHAR(identified_date,'Mon YYYY') month, AVG(actual_resolution_date-identified_date) days FROM ht_issues WHERE status = 'Closed' GROUP BY TO_CHAR(identified_date,'Mon YYYY') </pre> <p>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. </p> </li> <li> <p>For <span class="bold">When No Data Found Message</span>, enter: </p> <pre xml:space="preserve" class="oac_no_warn">No Closed Issues found. </pre> </li> </ol> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> <li> <p>Review your selections and click <span class="bold">Finish</span>. </p> </li> </ol> <p>Next, add a correct axis label and turn off the Chart Title and legend. </p> <p>To edit the chart: </p> <ol type="1" start="1"> <li> <p>From the Success page, select <span class="bold">Edit Page</span>. </p> <p>The Page Definition for page 10, Resolved by Month Identified, appears. </p> </li> <li> <p>Under Regions, select <span class="bold">Chart</span> to the left of Resolved by Month Identified. </p> </li> <li> <p>Under Chart Settings: </p> <ol type="a"> <li> <p>For Chart Height, enter <code>300</code>. </p> </li> <li> <p>Disable <span class="bold">Show Legend</span>. </p> </li> </ol> </li> <li> <p>Under the Axes Setting section: </p> <ol type="a"> <li> <p>For X Axis Title, enter <code>Date Identified</code>. </p> </li> <li> <p>For Y Axis Title, enter <code>Days to Resolve</code>. </p> </li> </ol> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> <p>To view your newly created line chart, click the <span class="bold">Run Page</span> icon. Your line chart should resemble <a href="#BABBJEBD">Figure 10-13</a>. </p> <div class="figure"> <a id="BABBJEBD" name="BABBJEBD"></a><a id="sthref286" name="sthref286"></a> <p class="titleinfigure">Figure 10-13 Resolved by Month Identified Line Chart</p> <img src="./img/iss_line.gif" alt="Description of iss_line.gif follows" title="Description of iss_line.gif follows"/> <br /><a id="sthref287" name="sthref287" href="./img_text/iss_line.htm">Description of the illustration iss_line.gif</a> <br /><br /> </div><!-- class="figure" --> </div><!-- class="sect3" --> <a id="BJFGBJIC" name="BJFGBJIC"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref288" name="sthref288"></a> <h4>Add Target Resolution Dates </h4> <p>The Target Resolution Dates report is a calendar which displays issues that have not yet closed and the assigned person on the day that corresponds to the issue target resolution date. </p> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref289" name="sthref289"></a> <h5>Create a Calendar </h5> <p>To create a calendar of target resolution dates: </p> <ol type="1" start="1"> <li> <p>Navigate to the Application Builder home page. </p> </li> <li> <p>Click <span class="bold">Create Page</span>. </p> </li> <li> <p>Select <span class="bold">Page with Component</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>Select <span class="bold">Calendar</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>Select <span class="bold">SQL Calendar</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Page Attributes: </p> <ol type="a"> <li> <p>For Page, enter <code>11</code>. </p> </li> <li> <p>For Page Name and Region Name, enter <code>Target Resolution Dates</code>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>For Tab Options, accept the default <span class="bold">Do not use tabs</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For <span class="bold">Enter SQL Query</span>, enter the following and click <span class="bold">Next</span>: </p> <pre xml:space="preserve" class="oac_no_warn">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' </pre> <p>Note that: </p> <ul> <li type="disc"> <p>The <code>target_resolution_date</code> is the date on which the issue will display </p> </li> <li type="disc"> <p>The <code>issue_summary</code> is concatenated with the person assigned </p> </li> <li type="disc"> <p>The <code>issue_id</code> will not display, but will be used to create a link to enable the user to view and edit the issue </p> </li> </ul> </li> <li> <p>On Identify Calendar Columns: </p> <ol type="a"> <li> <p>For Date Column, select <span class="bold">TARGET_RESOLUTION_DATE</span>. </p> </li> <li> <p>For Display Column, select <span class="bold">DISP</span>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>Review your selections and click <span class="bold">Finish</span>. </p> </li> </ol> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref290" name="sthref290"></a> <h5>Add an Item to Support Project Look Up </h5> <p>To enable the user to look up one project or all projects, you need to add an item. </p> <p>To add an item to support project look up: </p> <ol type="1" start="1"> <li> <p>From the Success page, select <span class="bold">Edit Page</span>. </p> <p>The Page Definition for page 11, Target Resolution Dates, appears. </p> </li> <li> <p>Under Items, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Choose Item Type, select <span class="bold">Select List</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Pick Select List Control Type, accept the default of <span class="bold">Select List</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Item Name, enter <code>P11_PROJECT</code> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify List of Values: </p> <ol type="a"> <li> <p>For Named LOV, select <span class="bold">PROJECTS</span>. </p> </li> <li> <p>For Null Text, enter: </p> <pre xml:space="preserve" class="oac_no_warn">- All Projects - </pre> </li> <li> <p>For Null Value, enter: </p> <pre xml:space="preserve" class="oac_no_warn">-1 </pre> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>For Item Attributes, accept the defaults and click <span class="bold">Next</span>. </p> </li> <li> <p>For <span class="bold">Default</span>, enter: </p> <pre xml:space="preserve" class="oac_no_warn">-1 </pre> </li> <li> <p>Click <span class="bold">Create Item</span>. </p> </li> </ol> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref291" name="sthref291"></a> <h5>Create a Go Button </h5> <p>To create a Go button to execute the query: </p> <ol type="1" start="1"> <li> <p> Under Buttons, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For the <span class="bold">Region</span>, select <span class="bold">Target Resolution Dates</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For <span class="bold">Task</span>, select <span class="bold">Create a button displayed among this region's items</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Button within Items Attributes: </p> <ol type="a"> <li> <p>For Button Name, enter <code>P11_GO</code>. </p> </li> <li> <p>For Button Style, select <span class="bold">Template Based Button</span>. </p> </li> <li> <p>For Template, select <span class="bold">Button</span>. </p> </li> </ol> </li> <li> <p>Click <span class="bold">Create Button</span>. </p> </li> </ol> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref292" name="sthref292"></a> <h5>Modify Calendar Attributes </h5> <p>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. </p> <p>To modify the Calendar Attributes: </p> <ol type="1" start="1"> <li> <p>Under Regions, select <span class="bold">CAL</span> to the left of Target Resolution Dates. </p> </li> <li> <p>Scroll down to Column Link: </p> <ol type="a"> <li> <p>For Page, enter <code>7</code>. </p> </li> <li> <p>For Clear Cache, enter <code>7</code>. </p> </li> <li> <p>For Set these items, enter: </p> <pre xml:space="preserve" class="oac_no_warn">P7_ISSUE_ID,P7_PREV_PAGE </pre> </li> <li> <p>For With these values, enter: </p> <pre xml:space="preserve" class="oac_no_warn">#ISSUE_ID#,11 </pre> </li> </ol> </li> <li> <p>Select the <span class="bold">Region Definition</span> tab. </p> </li> <li> <p>Scroll down to Header and Footer Text. </p> </li> <li> <p>Enter the following in Region Footer: </p> <pre xml:space="preserve" class="oac_no_warn">This excludes Closed issues. </pre> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> <p>To see your newly created calendar, click the <span class="bold">Run Page</span> icon. Your report should resemble <a href="#BABHEAEI">Figure 10-14</a>. Note that you can click the text displayed for an issue to display the Edit Issue page. To return to the calendar, click <span class="bold">Cancel</span>. </p> <div class="figure"> <a id="BABHEAEI" name="BABHEAEI"></a><a id="sthref293" name="sthref293"></a> <p class="titleinfigure">Figure 10-14 Target Resolution Dates Report</p> <img src="./img/iss_cal.gif" alt="Description of iss_cal.gif follows" title="Description of iss_cal.gif follows"/> <br /><a id="sthref294" name="sthref294" href="./img_text/iss_cal.htm">Description of the illustration iss_cal.gif</a> <br /><br /> </div><!-- class="figure" --> </div><!-- class="sect4" --> </div><!-- class="sect3" --> <a id="BJFGJBBI" name="BJFGJBBI"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref295" name="sthref295"></a> <h4>Add Average Days to Resolve </h4> <p>The Average Days to Resolve report is a bar chart that calculates the number of days it takes to resolve each closed issue and then averages that number that by assigned person. </p> <p>To add the Average Days to Resolve report: </p> <ol type="1" start="1"> <li> <p>Navigate to the Application Builder home page. </p> </li> <li> <p>Click <span class="bold">Create Page</span>. </p> </li> <li> <p>Select <span class="bold">Page with Component</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>Select <span class="bold">Chart</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>Select <span class="bold">Bar (HTML)</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Page Attributes: </p> <ol type="a"> <li> <p>For Page, enter <code>12</code>. </p> </li> <li> <p>For Page Name, enter <code>Average Days to Resolve</code>. </p> </li> <li> <p>For Region Name, enter <code>Average Days to Resolve</code>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>For Tab Options, accept the default <span class="bold">Do not use tabs</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Chart Attributes: </p> <ol type="a"> <li> <p>In <span class="bold">Chart SQL,</span> enter: </p> <pre xml:space="preserve" class="oac_no_warn">SELECT NULL l, NVL(p.person_name,'None Assigned') person, AVG(i.actual_resolution_date-i.identified_date) days FROM ht_issues i, ht_people p WHERE i.assigned_to = p.person_id (+) AND i.status = 'Closed' GROUP BY p.person_name </pre> <p>In the above SELECT statement: </p> <ul> <li type="circle"> <p>The first item selected is the link. Because this report will not link to any other page, <code>NULL</code> was selected. </p> </li> <li type="circle"> <p>The second item is the person's name, or <code>None Assigned</code> if <code>assigned_to</code> is <code>NULL</code>. </p> </li> <li type="circle"> <p>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. </p> </li> </ul> </li> <li> <p>For Include in summary, select only <span class="bold">Number of data points</span>. Deselect all other options. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>Review your selections and click <span class="bold">Finish</span>. </p> </li> </ol> <p>To view your newly created bar chart, select <span class="bold">Run Page</span>. Your report should resemble <a href="#BABBDCCE">Figure 10-15</a>. </p> <div class="figure"> <a id="BABBDCCE" name="BABBDCCE"></a><a id="sthref296" name="sthref296"></a> <p class="titleinfigure">Figure 10-15 Average Days to Resolve Report</p> <img src="./img/iss_bar.gif" alt="Description of iss_bar.gif follows" title="Description of iss_bar.gif follows"/> <br /><a id="sthref297" name="sthref297" href="./img_text/iss_bar.htm">Description of the illustration iss_bar.gif</a> <br /><br /> </div><!-- class="figure" --> </div><!-- class="sect3" --> </div><!-- class="sect2" --> <a id="BJFGAFIJ" name="BJFGAFIJ"></a> <div class="sect2"> <!-- infolevel=all infotype=General --><a id="sthref298" name="sthref298"></a> <h3>Add Content to the Home Page </h3> <p>Now that you have completed all the detail pages, next you need to add content to the home page and tie all the pages together. In this section, you modify the home page to display the following information: </p> <ul> <li type="disc"> <p>A menu of all available reports </p> </li> <li type="disc"> <p>Navigation to the maintenance pages </p> </li> <li type="disc"> <p>A button to <span class="bold">Add a New Issue</span> </p> </li> <li type="disc"> <p>Overdue Issues </p> </li> <li type="disc"> <p>Recently Opened Issues </p> </li> <li type="disc"> <p>Open Issues by Project as a chart </p> </li> <li type="disc"> <p>Unassigned Issues </p> </li> </ul> <p class="subhead2">Topics:</p> <p>Topics in this section include: </p> <ul> <li type="disc"> <p><a href="#BJFBHAHF">Add a Reports Menu</a> </p> </li> <li type="disc"> <p><a href="#BJFCJHCI">Add Maintenance Navigation</a> </p> </li> <li type="disc"> <p><a href="#BJFCHGDB">Add a New Issues Button</a> </p> </li> <li type="disc"> <p><a href="#BJFFAJAF">Add Overdue Issues Report</a> </p> </li> <li type="disc"> <p><a href="#BJFIJDBG">Add Unassigned Issues Report</a> </p> </li> <li type="disc"> <p><a href="#BJFHFEGF">Add Recently Opened Issues Report</a> </p> </li> <li type="disc"> <p><a href="#BJFIICDA">Add Open Issues by Project</a> </p> </li> </ul><a id="BJFBHAHF" name="BJFBHAHF"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref299" name="sthref299"></a> <h4>Add a Reports Menu </h4> <p>First, you add a menu implemented as a list. </p> <p>To add a menu: </p> <ol type="1" start="1"> <li> <p>Navigate to the Application Builder home page. </p> </li> <li> <p>Select <span class="bold">Shared Components</span>. </p> </li> <li> <p>Under the Navigation section, select <span class="bold">Lists</span>. </p> </li> <li> <p>Click <span class="bold">Create List</span>. </p> </li> <li> <p>For Name, enter <code>Main Menu</code>. </p> </li> <li> <p>For <span class="bold">List Template</span>, select <span class="bold">Vertical Sidebar List</span>. </p> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> </ol> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref300" name="sthref300"></a> <h5>Create List Entries </h5> <p>Now that the list has been created, you add list items to it. You need to add one list item for each report page. </p> <p>To add a list item for Assign Issues: </p> <ol type="1" start="1"> <li> <p>Click <span class="bold">Create List Entry</span>. </p> </li> <li> <p>For List, select <span class="bold">Main Menu</span>. </p> </li> <li> <p>For List Entry Label, enter <code>Assign Issues</code>. </p> </li> <li> <p>Under the Target section: </p> <ol type="a"> <li> <p>For Page, select <span class="bold">8</span>. </p> </li> <li> <p>Select <span class="bold">reset pagination for this page</span>. </p> </li> </ol> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> </ol> <p>Now you will create four more list items, one for each of the other reports in your application. </p> <p>To add list items for each of the other reports in your application: </p> <ol type="1" start="1"> <li> <p>Click <span class="bold">Create List Entry</span>. </p> </li> <li> <p>To define list item attributes for Issues: </p> <ol type="a"> <li> <p>For List, select <span class="bold">Main Menu</span>. </p> </li> <li> <p>For Sequence, enter <code>20</code>. </p> </li> <li> <p>For List Entry Label, enter <code>Issues</code>. </p> </li> <li> <p>Under the Target section: </p> <ul> <li type="circle"> <p>For Page, select <code>6</code>. </p> </li> <li type="circle"> <p>Select <span class="bold">reset pagination for this page</span>. </p> </li> <li type="circle"> <p>For Clear Cache, enter <code>6</code>. </p> <p>This clears any selections for page 6 from the session state. </p> </li> </ul> </li> </ol> </li> <li> <p>Click <span class="bold">Create and Create Another</span>. </p> </li> <li> <p>To define list item attributes for Issue Summary: </p> <ol type="a"> <li> <p>For List, select <span class="bold">Main Menu</span>. </p> </li> <li> <p>For Sequence, enter <code>30</code>. </p> </li> <li> <p>For List Entry Label, enter <code>Issue Summary by Project</code>. </p> </li> <li> <p>Under the Target section: </p> <ul> <li type="circle"> <p>For Page, select <span class="bold">9</span>. </p> </li> <li type="circle"> <p>Select <span class="bold">reset pagination for this page</span>. </p> </li> <li type="circle"> <p>For <span class="bold">Clear Cache</span>, enter <code>9</code>. </p> </li> </ul> </li> </ol> </li> <li> <p>Click <span class="bold">Create and Create Another</span>. </p> </li> <li> <p>To define list item attributes for Resolved by Month Identified: </p> <ol type="a"> <li> <p>For List, select <span class="bold">Main Menu</span>. </p> </li> <li> <p>For Sequence, enter <code>40</code>. </p> </li> <li> <p>For List Entry Label, enter <code>Resolved by Month Identified (chart)</code>. </p> </li> </ol> </li> <li> <p>Click <span class="bold">Create and Create Another</span>. </p> </li> <li> <p>To define list item attributes for Target Resolution Dates: </p> <ol type="a"> <li> <p>For List, select <span class="bold">Main Menu</span>. </p> </li> <li> <p>For Sequence, enter <code>50</code>. </p> </li> <li> <p>For List Entry Label, enter <code>Target Resolution Dates (calendar)</code>. </p> </li> <li> <p>Under the Target section: </p> <ul> <li type="circle"> <p>For Page, select <span class="bold">11</span>. </p> </li> <li type="circle"> <p>Select <span class="bold">reset pagination for this page</span>. </p> </li> </ul> </li> </ol> </li> <li> <p>Click <span class="bold">Create and Create Another</span>. </p> </li> <li> <p>To define list item attributes for Average Days to Resolve: </p> <ol type="a"> <li> <p>For List, select <span class="bold">Main Menu</span>. </p> </li> <li> <p>For Sequence, enter <code>60</code>. </p> </li> <li> <p>For List Entry Label, enter <code>Average Days to Resolve (chart)</code>. </p> </li> <li> <p>Under Target, select <span class="bold">12</span> for Page. </p> </li> </ol> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> </ol> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref301" name="sthref301"></a> <h5>Include the List on the Home Page </h5> <p>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. </p> <p>To change the page template on the home page: </p> <ol type="1" start="1"> <li> <p>Click the <span class="bold">Edit Page</span> icon. </p> <p>The Page Definition for page 12, Average Days to Resolve, appears. </p> </li> <li> <p>In the Page field, enter <code>1</code> and click <span class="bold">Go</span>. </p> </li> <li> <p>Click <span class="bold">Edit Attributes</span>. </p> <p>Page Attributes for page 1 appears. </p> </li> <li> <p>Locate Primary Display Attributes. </p> </li> <li> <p>From the Page Template list, select <span class="bold">10. No Tabs with Sidebar</span>. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> <p>Next, create a region to contain your menu. </p> <p>To create a a new region: </p> <ol type="1" start="1"> <li> <p>Under Regions, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Select a common region type, select <span class="bold">List</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>In Identify Region Attributes: </p> <ol type="a"> <li> <p>For Title, enter <code>Menu</code>. </p> </li> <li> <p>For Region Template, select <span class="bold">No Template</span>. </p> </li> <li> <p>For Display Point, select <span class="bold">Page Template Region Position 2</span> (or select the quick link <span class="bold">[Pos. 2]</span>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>For List, select <span class="bold">Main Menu</span>. </p> </li> <li> <p>Click <span class="bold">Create List Region</span>. </p> </li> </ol> </div><!-- class="sect4" --> </div><!-- class="sect3" --> <a id="BJFCJHCI" name="BJFCJHCI"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref302" name="sthref302"></a> <h4>Add Maintenance Navigation </h4> <p>Next, you will add maintenance navigation as a list. This list will display just below the reports in the left margin. </p> <ol type="1" start="1"> <li> <p>Navigate to the Application Builder home page. </p> </li> <li> <p>Select <span class="bold">Shared Components</span>. </p> </li> <li> <p>Under the Navigation section, select <span class="bold">Lists</span>. </p> </li> <li> <p>Click <span class="bold">Create List</span>. </p> </li> <li> <p>For Name, enter <code>Maintenance</code>. </p> </li> <li> <p>For <span class="bold">List Template</span>, select <span class="bold">Vertical Sidebar List</span>. </p> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> </ol> <p>Next, create three list items. The first list item acts as a separator between the two navigation regions. The other two enable users to view people and projects. </p> <p>To add list items: </p> <ol type="1" start="1"> <li> <p>Click <span class="bold">Create List Entry</span>. </p> </li> <li> <p>To define list item attributes for the first list item: </p> <ol type="a"> <li> <p>For List, select <span class="bold">Maintenance</span>. </p> </li> <li> <p>For List Entry Label, enter: </p> <pre xml:space="preserve" class="oac_no_warn">&nbsp; </pre> </li> <li> <p>Under Target, select <span class="bold">1</span> for Page. </p> </li> </ol> </li> <li> <p>Click <span class="bold">Create and Create Another</span>. </p> </li> <li> <p>To define list item attributes for Projects: </p> <ol type="a"> <li> <p>For List, select <span class="bold">Maintenance</span>. </p> </li> <li> <p>For Sequence, enter <code>20</code>. </p> </li> <li> <p>For <span class="bold">List Entry Label</span>, enter: </p> <pre xml:space="preserve" class="oac_no_warn">Projects </pre> </li> <li> <p>Under the Target section: </p> <ul> <li type="circle"> <p>For Page, select <span class="bold">2</span>. </p> </li> <li type="circle"> <p>Check <span class="bold">reset pagination for this page</span>. </p> </li> </ul> </li> </ol> </li> <li> <p>Click <span class="bold">Create and Create Another</span>. </p> </li> <li> <p>To define list item attributes for People: </p> <ol type="a"> <li> <p>For List, select <span class="bold">Maintenance</span>. </p> </li> <li> <p>For Sequence, enter <code>30</code>. </p> </li> <li> <p>For <span class="bold">List Entry Label</span>, enter: </p> <pre xml:space="preserve" class="oac_no_warn">People </pre> </li> <li> <p>Under the Target section: </p> <ul> <li type="circle"> <p>For Page, select <span class="bold">4</span>. </p> </li> <li type="circle"> <p>Check <span class="bold">reset pagination for this page</span>. </p> </li> </ul> </li> </ol> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> </ol> <p>To create a region to display the new list. </p> <ol type="1" start="1"> <li> <p>Click the Edit Page icon. </p> </li> <li> <p>Under Regions, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Select a common region type, select <span class="bold">List</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Region Attributes: </p> <ol type="a"> <li> <p>For Title, enter <code>Maintenance</code>. </p> </li> <li> <p>For Region Template, select <span class="bold">No Template</span>. </p> </li> <li> <p>For Display Point, select <span class="bold">Page Template Region Position 2 </span>(or select the quick link <span class="bold">[Pos. 2]</span>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>For List, select <span class="bold">Maintenance</span>. </p> </li> <li> <p>Click <span class="bold">Create List Region</span>. </p> </li> </ol> </div><!-- class="sect3" --> <a id="BJFCHGDB" name="BJFCHGDB"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref303" name="sthref303"></a> <h4>Add a New Issues Button </h4> <p>Next, you create a button to navigate the user to page 7, Create/Edit Issue. </p> <p>To create a region to contain the button: </p> <ol type="1" start="1"> <li> <p>Under Regions, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Select a common region type, accept the default of <span class="bold">HTML</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Region Attributes: </p> <ol type="a"> <li> <p>For Title, enter <code>Buttons</code>. </p> </li> <li> <p>For Region Template, select <span class="bold">No Template</span>. </p> </li> <li> <p>For Display Point, select <span class="bold">Page Template Region Position 1</span> (or select the quick link <span class="bold">[Pos. 1]</span>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>Click <span class="bold">Create Region</span>. </p> </li> </ol> <p>To add a button: </p> <ol type="1" start="1"> <li> <p>Under the Buttons section, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Region, select <span class="bold">Buttons</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Task, accept the default <span class="bold">Create a button in a region position</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Button Name and Label: </p> <ol type="a"> <li> <p>For Button Name, enter <code>ADD</code>. </p> </li> <li> <p>For Label, enter: </p> <pre xml:space="preserve" class="oac_no_warn">Add a New Issue </pre> </li> <li> <p>For Action, select <span class="bold">Redirect to URL without submitting page</span>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>For Button Template, select <span class="bold">Button</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Position, select <span class="bold">Top of Region</span> and click <span class="bold">Next</span>. </p> <p>On the Branching page, you need to call the correct page, clear the cache, and specify that the Create and Cancel buttons returns the user to the home page. </p> </li> <li> <p>On Branching: </p> <ol type="a"> <li> <p>For Page, select <span class="bold">7</span>. </p> </li> <li> <p>For Clear Cache, enter <code>7</code>. </p> </li> <li> <p>For Set these items, enter: </p> <pre xml:space="preserve" class="oac_no_warn">P7_PREV_PAGE </pre> </li> <li> <p>For With these values, enter <code>1</code>. </p> </li> </ol> </li> <li> <p>Click <span class="bold">Create Button</span>. </p> </li> </ol> </div><!-- class="sect3" --> <a id="BJFFAJAF" name="BJFFAJAF"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref304" name="sthref304"></a> <h4>Add Overdue Issues Report </h4> <p>Next, add some content to the home page. In this exercise you add a report to display overdue issues. The query for this report retrieves all unclosed issues with a past target resolution date. </p> <p>To add a report to display overdue issues: </p> <ol type="1" start="1"> <li> <p>Under the Regions section, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Select a common region type, select <span class="bold">Report</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Report Implementation, select <span class="bold">SQL Report</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Region Attributes, enter <code>Overdue Issues</code> for Title and click <span class="bold">Next</span>. </p> </li> <li> <p>For Enter SQL Query, enter: </p> <pre xml:space="preserve" class="oac_no_warn">SELECT i.issue_id, i.priority, i.issue_summary, p.person_name assignee, i.target_resolution_date, r.project_name FROM ht_issues i, ht_people p, ht_projects r WHERE i.assigned_to = p.person_id (+) AND i.related_project = r.project_id AND i.target_resolution_date < sysdate AND i.status != 'Closed' </pre> <p>The outer join is necessary because the assignment is optional. </p> </li> <li> <p>Click <span class="bold">Create Region</span>. </p> </li> </ol> <p>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. </p> <p>To edit the column headings: </p> <ol type="1" start="1"> <li> <p>Under Regions, select <span class="bold">Q</span> to the left of Overdue Issues. </p> </li> <li> <p>For Headings Type, select <span class="bold">Custom</span>. </p> </li> <li> <p>For ISSUE_ID, remove the Heading. </p> </li> <li> <p>For ISSUE_SUMMARY, edit the Heading to read: </p> <pre xml:space="preserve" class="oac_no_warn">Summary </pre> </li> <li> <p>For ASSIGNEE, change the Heading to: </p> <pre xml:space="preserve" class="oac_no_warn">Assigned To </pre> </li> <li> <p>For TARGET_RESOLUTION_DATE: </p> <ol type="a"> <li> <p>For <span class="bold"></span>Heading, enter: </p> <pre xml:space="preserve" class="oac_no_warn">Target<br>Resolution<br>Date </pre> </li> <li> <p>For Column Align, select <span class="bold">center</span>. </p> </li> <li> <p>For Heading Align, select <span class="bold">center</span>. </p> </li> </ol> </li> <li> <p>For <span class="bold">ISSUE_ID</span>, deselest <span class="bold">Show</span>. </p> <p>This enables the query to pass in the link, but not display it. </p> </li> <li> <p>Select <span class="bold">Sort</span> for all columns except ISSUE_ID. </p> </li> <li> <p>For TARGET_RESOLUTION_DATE, select <span class="bold">1</span> for Sort Sequence. </p> </li> <li> <p>For ISSUE_SUMMARY, select 2 for Sort Sequence. </p> </li> </ol> <p>To edit column attributes for ISSUE_SUMMARY: </p> <ol type="1" start="1"> <li> <p>Click the <span class="bold">Edit</span> icon to the left of ISSUE_SUMMARY. </p> </li> <li> <p>Scroll down to Column Link: </p> <ol type="a"> <li> <p>For Link Text, use the quick link of <span class="bold">[Icon 3]</span>. </p> </li> <li> <p>For Page, select <span class="bold">7</span>. </p> </li> <li> <p>For Clear Cache, select <code>7</code>. </p> </li> <li> <p>For Item 1, enter the Name: </p> <pre xml:space="preserve" class="oac_no_warn">P7_ISSUE_ID </pre> </li> <li> <p>For Item 1, enter the Value: </p> <pre xml:space="preserve" class="oac_no_warn">#ISSUE_ID# </pre> </li> <li> <p>For Item 2, enter the Name: </p> <pre xml:space="preserve" class="oac_no_warn">P7_PREV_PAGE </pre> </li> <li> <p>For Item 2, enter the Value: </p> <pre xml:space="preserve" class="oac_no_warn">1 </pre> </li> </ol> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> <p>To select layout and pagination attributes: </p> <ol type="1" start="1"> <li> <p>Scroll down to Layout and Pagination: </p> <ol type="a"> <li> <p>For Pagination Scheme, select <span class="bold">Search Engine 1,2,3,4 (set based pagination)</span>. </p> </li> <li> <p>For Number of Rows, enter <code>5</code>. </p> </li> </ol> </li> <li> <p>Under Sorting, select the light gray arrow for Ascending and Descending Image. </p> </li> <li> <p>Under the Messages section, enter the following in When No Data Found Message: </p> <pre xml:space="preserve" class="oac_no_warn">No Overdue Issues. </pre> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> </div><!-- class="sect3" --> <a id="BJFIJDBG" name="BJFIJDBG"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref305" name="sthref305"></a> <h4>Add Unassigned Issues Report </h4> <p>The next report you add displays unassigned, open issues. This report is very similar to Overdue Issues. Rather than creating it manually, you will copy the Overdue Issues report and modify it. </p> <p>To create the Unassigned Issues report by copying an existing report: </p> <ol type="1" start="1"> <li> <p>Under <span class="bold">Regions</span>, click the Copy icon. </p> </li> <li> <p>Under Name, select <span class="bold">Overdue Issues</span>. </p> </li> <li> <p>For To Page, accept the default <span class="bold">1</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Region Name, enter <code>Unassigned Issues</code>. </p> </li> <li> <p>Click <span class="bold">Copy Region</span>. </p> </li> </ol> <p>To modify the query and edit the report region: </p> <ol type="1" start="1"> <li> <p>Under the Regions section, select <span class="bold">Unassigned Issues</span>. </p> </li> <li> <p>For <span class="bold">Region Source</span>, enter: </p> <pre xml:space="preserve" class="oac_no_warn">SELECT i.issue_id, i.priority, i.issue_summary, i.target_resolution_date, r.project_name, p.person_name identifiee FROM ht_issues i, ht_people p, ht_projects r WHERE i.assigned_to IS NULL AND i.status != 'Closed' AND i.related_project = r.project_id AND i.identified_by = p.person_id </pre> </li> <li> <p>Select the <span class="bold">Report Attributes</span> tab. </p> <p>Note that previously defined columns have retained their modified attributes. </p> </li> <li> <p>For IDENTIFIEE, enter the following Heading: </p> <pre xml:space="preserve" class="oac_no_warn">Identified By </pre> </li> <li> <p>Under Messages, enter the following in When No Data Found Message: </p> <pre xml:space="preserve" class="oac_no_warn">No Unassigned Issues. </pre> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> </div><!-- class="sect3" --> <a id="BJFHFEGF" name="BJFHFEGF"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref306" name="sthref306"></a> <h4>Add Recently Opened Issues Report </h4> <p>Lastly, you will add a report of recently opened issues. The underlying query displays the five most recently opened issues. As in the existing exercise, you will copy an existing report and modify it. </p> <p>To create a report of recently opened issues by copying an existing report: </p> <ol type="1" start="1"> <li> <p>Under Regions, click the <span class="bold">Copy</span> icon. </p> </li> <li> <p>Under Name, select <span class="bold">Unassigned Issues</span>. </p> </li> <li> <p>For To Page, accept the default <span class="bold">1</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Region Name, enter <code>Recently Opened Issues</code>. </p> </li> <li> <p>Click <span class="bold">Copy Region</span>. </p> </li> </ol> <p>To modify the query and edit the report region: </p> <ol type="1" start="1"> <li> <p>Under Regions, click the <span class="bold">Q</span> to the left of Recently Opened Issues. </p> </li> <li> <p>For all columns: </p> <ol type="a"> <li> <p>Disable sorting by deselecting <span class="bold">Sort</span>. </p> </li> <li> <p>Set Sequence to <span class="bold">-</span>. </p> </li> </ol> </li> <li> <p>Select the <span class="bold">Region Definition</span> tab. </p> </li> <li> <p>For <span class="bold">Region Source</span>, enter: </p> <pre xml:space="preserve" class="oac_no_warn">SELECT issue_id, priority, issue_summary, assignee, target_resolution_date, project_name, identifiee FROM ( SELECT i.issue_id, i.priority, i.issue_summary, p.person_name assignee, i.target_resolution_date, r.project_name, p2.person_name identifiee FROM ht_issues i, ht_people p, ht_people p2, ht_projects r WHERE i.assigned_to = p.person_id (+) AND i.related_project = r.project_id AND i.identified_by = p2.person_id AND i.created_date > (sysdate - 7) ORDER BY i.created_date desc ) WHERE rownum < 6 </pre> </li> <li> <p>Select the <span class="bold">Report Attributes</span> tab. </p> </li> <li> <p>For ASSIGNEE, click the gray up arrow to the right of the Edit column until ASSIGNEE is just after ISSUE_SUMMARY. </p> </li> <li> <p>For ASSIGNEE, change Heading to: </p> <pre xml:space="preserve" class="oac_no_warn">Assigned To </pre> </li> <li> <p>Scroll down to Layout and Pagination section. From the Pagination Scheme list, select <span class="bold">- No Pagination Selected -</span>. </p> </li> <li> <p>Under the Messages section, enter the following in When No Data Found Message: </p> <pre xml:space="preserve" class="oac_no_warn">No Recently Opened Issues. </pre> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> </div><!-- class="sect3" --> <a id="BJFIICDA" name="BJFIICDA"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref307" name="sthref307"></a> <h4>Add Open Issues by Project </h4> <p>Next, add a pie chart displaying Open Issues by Project. </p> <p>To add a pie chart: </p> <ol type="1" start="1"> <li> <p>Under Regions, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Select a common region type, select <span class="bold">Chart</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>Select <span class="bold">Pie</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Title, enter <code>Open Issues by Project</code> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Enter SVG Chart SQL Query, enter: </p> <pre xml:space="preserve" class="oac_no_warn">SELECT NULL LINK, NVL(r.project_name,'No Project') label, COUNT(r.project_name) value FROM ht_issues i, ht_projects r WHERE i.status = 'Open' AND i.related_project = r.project_id GROUP BY NULL, r.project_name ORDER BY r.project_name </pre> <p>Note that this query does not include a link, the label is the <code>Project Name</code>, and the value calculated and used for the pie chart is the total number of open issues by project. </p> </li> <li> <p>Click <span class="bold">Create Region</span>. </p> </li> </ol> <p>To edit the chart. </p> <ol type="1" start="1"> <li> <p>Under Regions, select <span class="bold">Chart</span> to the left of Open Issues by Project. </p> </li> <li> <p>For Chart Width, enter <code>500</code>. </p> </li> <li> <p>For Chart Height, enter <code>200</code>. </p> </li> <li> <p>For Chart Title, remove the title. </p> </li> <li> <p>Under Chart Query, enter the following in When No Data Found Message: </p> <pre xml:space="preserve" class="oac_no_warn">No Open Issues. </pre> </li> <li> <p>Under Font Settings, for Legend select <span class="bold">14</span> for the Font Size. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> <p>To view the revised page, click the <span class="bold">Run Page</span> icon. Your home page should resemble <a href="#BABJEIDB">Figure 10-16</a>. </p> <div class="figure"> <a id="BABJEIDB" name="BABJEIDB"></a><a id="sthref308" name="sthref308"></a> <p class="titleinfigure">Figure 10-16 Revised Home Page</p> <img src="./img/iss_home2.gif" alt="Description of iss_home2.gif follows" title="Description of iss_home2.gif follows"/> <br /><a id="sthref309" name="sthref309" href="./img_text/iss_home2.htm">Description of the illustration iss_home2.gif</a> <br /><br /> </div><!-- class="figure" --> </div><!-- class="sect3" --> </div><!-- class="sect2" --> <a id="BJFFEJID" name="BJFFEJID"></a> <div class="sect2"> <!-- infolevel=all infotype=General --><a id="sthref310" name="sthref310"></a> <h3>Add a Breadcrumb Menu </h3> <p>In the previous exercise, you created menus on the home page to enable users to navigate to various pages within your application. Next, you need to provide users with a way to navigate to the home page. You can accomplish this by adding a breadcrumb menu. </p> <p>In the next exercise, you create a menu, add menu options to it and then include that menu within a region on page 0. Adding components to page 0 makes them display on all pages with an application. </p> <p class="subhead2">Topics:</p> <p>Topics in this section include: </p> <ul> <li type="disc"> <p><a href="#BJFJHIAF">Create a Breadcrumb Menu</a> </p> </li> <li type="disc"> <p><a href="#BJFGACAG">Add Menu Options</a> </p> </li> <li type="disc"> <p><a href="#BJFBDJJD">Create a Page 0</a> </p> </li> <li type="disc"> <p><a href="#BJFJHEJF">Create a Region to Contain the Menu</a> </p> </li> </ul><a id="BJFJHIAF" name="BJFJHIAF"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref311" name="sthref311"></a> <h4>Create a Breadcrumb Menu </h4> <p>To create a breadcumb menu: </p> <ol type="1" start="1"> <li> <p>Navigate to the Application Builder home page. </p> </li> <li> <p>Click <span class="bold">Shared Components</span>. </p> </li> <li> <p>Under Navigation, select <span class="bold">Menus</span>. </p> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> <li> <p>For Name, enter <code>Breadcrumb</code>. </p> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> </ol> </div><!-- class="sect3" --> <a id="BJFGACAG" name="BJFGACAG"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref312" name="sthref312"></a> <h4>Add Menu Options </h4> <p>Next, add menu options. </p> <p>To create a menu option for page 1: </p> <ol type="1" start="1"> <li> <p>Click <span class="bold">Create Menu Option</span>. </p> </li> <li> <p>Under Menu Identification: </p> <ol type="a"> <li> <p>For Menu, select <span class="bold">Breadcrumb</span>. </p> </li> <li> <p>For Page, enter <code>1</code>. </p> </li> </ol> </li> <li> <p>For Short Name, enter <code>Home</code>. </p> </li> <li> <p>Under Menu Target, enter <code>1</code> in Page. </p> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> </ol> <p>To create a menu option for page 2: </p> <ol type="1" start="1"> <li> <p>Click <span class="bold">Create Menu Option</span>. </p> </li> <li> <p>Under Menu Identification: </p> <ol type="a"> <li> <p>For Menu, select <span class="bold">Breadcrumb</span>. </p> </li> <li> <p>For Page, enter <code>2</code>. </p> </li> </ol> </li> <li> <p>Under Menu Option: </p> <ol type="a"> <li> <p>For Parent Menu Option, select <span class="bold">Home</span>. </p> </li> <li> <p>For Short Name, enter <code>Projects</code>. </p> </li> </ol> </li> <li> <p>Under Menu Target, enter <code>2</code> in Page. </p> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> </ol> <p>To create a menu option for page 3: </p> <ol type="1" start="1"> <li> <p>Click <span class="bold">Create Menu Option</span>. </p> </li> <li> <p>Under Menu Identification: </p> <ol type="a"> <li> <p>For Menu, select <span class="bold">Breadcrumb</span>. </p> </li> <li> <p>For Page, enter <code>3</code>. </p> </li> </ol> </li> <li> <p>Under Menu Option: </p> <ol type="a"> <li> <p>For Parent Menu Option, select <span class="bold">Projects</span>. </p> </li> <li> <p>For Short Name, enter <code>Create/Edit Projects</code>. </p> </li> </ol> </li> <li> <p>Under Menu Target, enter <code>3</code> in Page. </p> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> </ol> <p>To create a menu option for page 4: </p> <ol type="1" start="1"> <li> <p>Click <span class="bold">Create Menu Option</span>. </p> </li> <li> <p>Under Menu Identification: </p> <ol type="a"> <li> <p>For Menu, select <span class="bold">Breadcrumb</span>. </p> </li> <li> <p>For Page, enter <code>4</code>. </p> </li> </ol> </li> <li> <p>Under Menu Option: </p> <ol type="a"> <li> <p>For Parent Menu Option, select <span class="bold">Home</span>. </p> </li> <li> <p>For Short Name, enter <code>People</code>. </p> </li> </ol> </li> <li> <p>Under Menu Target, enter 4 in Page. </p> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> </ol> <p>To create a menu option for page 5: </p> <ol type="1" start="1"> <li> <p>Click <span class="bold">Create Menu Option</span>. </p> </li> <li> <p>Under Menu Identification: </p> <ol type="a"> <li> <p>For Menu, select <span class="bold">Breadcrumb</span>. </p> </li> <li> <p>For Page, enter <code>5</code>. </p> </li> </ol> </li> <li> <p>Under Menu Option: </p> <ol type="a"> <li> <p>For Parent Menu Option, select <span class="bold">People</span>. </p> </li> <li> <p>For Short Name, enter <code>Create/Edit Person Information</code>. </p> </li> </ol> </li> <li> <p>Under Menu Target, enter 5 in Page. </p> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> </ol> <p>To create a menu option for page 6: </p> <ol type="1" start="1"> <li> <p>Click <span class="bold">Create Menu Option</span>. </p> </li> <li> <p>Under Menu Identification: </p> <ol type="a"> <li> <p>For Menu, select <span class="bold">Breadcrumb</span>. </p> </li> <li> <p>For Page, enter 6. </p> </li> </ol> </li> <li> <p>Under Menu Option: </p> <ol type="a"> <li> <p>For Parent Menu Option, select <span class="bold">Home</span>. </p> </li> <li> <p>For Short Name, enter <code>Issues</code>. </p> </li> </ol> </li> <li> <p>Under Menu Target, enter 6 in Page. </p> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> </ol> <p>To create a menu option for page 7: </p> <ol type="1" start="1"> <li> <p>Click <span class="bold">Create Menu Option</span>. </p> </li> <li> <p>Under Menu Identification: </p> <ol type="a"> <li> <p>For Menu, select <span class="bold">Breadcrumb</span>. </p> </li> <li> <p>For Page, enter 7. </p> </li> </ol> </li> <li> <p>Under Menu Option: </p> <ol type="a"> <li> <p>For Parent Menu Option, select <span class="bold">Home</span>. </p> </li> <li> <p>For Short Name, enter <code>Create/Edit Issue</code>. </p> </li> </ol> </li> <li> <p>Under Menu Target, enter 7 in Page. </p> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> </ol> <p>To create a menu option for page 8: </p> <ol type="1" start="1"> <li> <p>Click <span class="bold">Create Menu Option</span>. </p> </li> <li> <p>Under Menu Identification: </p> <ol type="a"> <li> <p>For Menu, select <span class="bold">Breadcrumb</span>. </p> </li> <li> <p>For Page, enter <code>8</code>. </p> </li> </ol> </li> <li> <p>Under Menu Option: </p> <ol type="a"> <li> <p>For Parent Menu Option, select <span class="bold">Home</span>. </p> </li> <li> <p>For Short Name, enter <code>Assign Issues</code>. </p> </li> </ol> </li> <li> <p>Under Menu Target, enter <code>8</code> in Page. </p> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> </ol> <p>To create a menu option for page 9: </p> <ol type="1" start="1"> <li> <p>Click <span class="bold">Create Menu Option</span>. </p> </li> <li> <p>Under Menu Identification: </p> <ol type="a"> <li> <p>For Menu, select <span class="bold">Breadcrumb</span>. </p> </li> <li> <p>For Page, enter <code>9</code>. </p> </li> </ol> </li> <li> <p>Under Menu Option: </p> <ol type="a"> <li> <p>For Parent Menu Option, select <span class="bold">Home</span>. </p> </li> <li> <p>For Short Name, enter <code>Issue Summary by Project</code>. </p> </li> </ol> </li> <li> <p>Under Menu Target, enter <code>9</code> in Page. </p> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> </ol> <p>To create a menu option for page 10: </p> <ol type="1" start="1"> <li> <p>Click <span class="bold">Create Menu Option</span>. </p> </li> <li> <p>Under Menu Identification: </p> <ol type="a"> <li> <p>For Menu, select <span class="bold">Breadcrumb</span>. </p> </li> <li> <p>For Page, enter <code>10</code>. </p> </li> </ol> </li> <li> <p>Under Menu Option: </p> <ol type="a"> <li> <p>For Parent Menu Option, select <span class="bold">Home</span>. </p> </li> <li> <p>For Short Name, enter <code>Resolved by Month Identified</code>. </p> </li> </ol> </li> <li> <p>Under Menu Target, enter <code>10</code> in Page. </p> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> </ol> <p>To create a menu option for page 11: </p> <ol type="1" start="1"> <li> <p>Click <span class="bold">Create Menu Option</span>. </p> </li> <li> <p>Under Menu Identification: </p> <ol type="a"> <li> <p>For Menu, select <span class="bold">Breadcrumb</span>. </p> </li> <li> <p>For Page, enter <code>11</code>. </p> </li> </ol> </li> <li> <p>Under Menu Option: </p> <ol type="a"> <li> <p>For Parent Menu Option, select <span class="bold">Home</span>. </p> </li> <li> <p>For Short Name, enter <code>Target Resolution Dates</code>. </p> </li> </ol> </li> <li> <p>Under Menu Target, enter <code>11</code> in Page. </p> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> </ol> <p>To create a menu option for page 12: </p> <ol type="1" start="1"> <li> <p>Click <span class="bold">Create Menu Option</span>. </p> </li> <li> <p>Under Menu Identification: </p> <ol type="a"> <li> <p>For Menu, select <span class="bold">Breadcrumb</span>. </p> </li> <li> <p>For Page, enter <code>12</code>. </p> </li> </ol> </li> <li> <p>Under Menu Option: </p> <ol type="a"> <li> <p>For Parent Menu Option, select <span class="bold">Home</span>. </p> </li> <li> <p>For Short Name, enter <code>Average Days to Resolve</code>. </p> </li> </ol> </li> <li> <p>Under Menu Target, enter <code>12</code> in Page. </p> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> </ol> </div><!-- class="sect3" --> <a id="BJFBDJJD" name="BJFBDJJD"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref313" name="sthref313"></a> <h4>Create a Page 0 </h4> <p>Now that the menu exists, you need to create page 0 and then create a region to contain your Breadcrumb menu. </p> <p>To create page 0: </p> <ol type="1" start="1"> <li> <p>Navigate to the Application Builder home page. </p> </li> <li> <p>Click <span class="bold">Create Page</span>. </p> </li> <li> <p>Select <span class="bold">Blank Page</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Page, enter <code>0</code> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Name, enter <span class="bold">Breadcrumbs</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Identify Tabs, accept the default <span class="bold">No</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>Review your selections and click <span class="bold">Finish</span>. </p> </li> </ol> </div><!-- class="sect3" --> <a id="BJFJHEJF" name="BJFJHEJF"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref314" name="sthref314"></a> <h4>Create a Region to Contain the Menu </h4> <p>To create a region to contain your Breadcrumb menu: </p> <ol type="1" start="1"> <li> <p>From the Success page, select <span class="bold">Edit Page</span>. </p> <p>The Page Definition for page 0 appears. </p> </li> <li> <p>Under Regions, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Select a common region type, select <span class="bold">Menu</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Identify Region Attributes: </p> <ol type="a"> <li> <p>For Title, enter <span class="bold">Breadcrumbs</span>. </p> </li> <li> <p>For Region Template, select <span class="bold">No Template</span>. </p> </li> <li> <p>For Display Point, select <span class="bold">Page Template Region Position 1</span>. </p> <p>This selection displays the menu above all other content on the page. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>For Identify Menu: </p> <ol type="a"> <li> <p>For Menu, select <span class="bold">Breadcrumbs</span>. </p> </li> <li> <p>For Menu Template, select <span class="bold">Breadcrumb Menu</span>. </p> </li> </ol> </li> <li> <p>Click <span class="bold">Create Menu Region</span>. </p> </li> </ol> <p>Return to the home page by clicking the Next Page icon (>). The Page Definition for page 1 appears. To see your completed home page, click the <span class="bold">Run Page</span> icon. Your home page should resemble <a href="#BABBBCCB">Figure 10-17</a>. </p> <div class="figure"> <a id="BABBBCCB" name="BABBBCCB"></a><a id="sthref315" name="sthref315"></a> <p class="titleinfigure">Figure 10-17 Revised Home Page with Breadcrumb Menu</p> <img src="./img/iss_home3.gif" alt="Description of iss_home3.gif follows" title="Description of iss_home3.gif follows"/> <br /><a id="sthref316" name="sthref316" href="./img_text/iss_home3.htm">Description of the illustration iss_home3.gif</a> <br /><br /> </div><!-- class="figure" --> <p>Notice the Breadcrumb in the top bar. Click one of the items on the Maintenance menu on the left side of the page. Notice how the breadcrumb menu changes.and watch the breadcrumb change. </p> <p>At this stage your application is fully functional, but is missing the security and email notification. Those topics will be discussed in the next section. </p> </div><!-- class="sect3" --> </div><!-- class="sect2" --> </div><!-- class="sect1" --> <a id="BABDAACH" name="BABDAACH"></a> <div class="sect1"> <!-- infolevel=all infotype=General --><a id="sthref317" name="sthref317"></a> <h2>Adding Advanced Features </h2> <p>Once your application is fully functional you can focus on adding advanced features outlined during the planning and project analysis phase. </p> <p class="subhead2">Topics:</p> <ul> <li type="disc"> <p><a href="#BJFJIEGI">Add Support for E-mail Notification</a> </p> </li> <li type="disc"> <p><a href="#BJFJAJGH">Add Application Security</a> </p> </li> </ul><a id="BJFJIEGI" name="BJFJIEGI"></a> <div class="sect2"> <!-- infolevel=all infotype=General --><a id="sthref318" name="sthref318"></a> <h3>Add Support for E-mail Notification </h3> <p>The planning and project analysis phase produced two e-mail requirements: </p> <ul> <li type="disc"> <p>Notify people when an issue is assigned to them </p> </li> <li type="disc"> <p>Notify the project lead when any issue becomes overdue </p> </li> </ul> <p class="subhead2">Topics:</p> <ul> <li type="disc"> <p><a href="#BJFCDHJH">How E-mail Notification Works</a> </p> </li> <li type="disc"> <p><a href="#BJFCCECG">Add Notification of New Assignments</a> </p> </li> <li type="disc"> <p><a href="#BJFDECJH">Add a Notification for Overdue Issues</a> </p> </li> </ul><a id="BJFCDHJH" name="BJFCDHJH"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref319" name="sthref319"></a> <h4>How E-mail Notification Works </h4> <p>To send mail from within an Oracle HTML DB application, you create a PL/SQL process that calls the supplied <code>HTMLDB_MAIL</code> package. </p> <p>E-mail is not sent immediately from Oracle HTML DB, but is stored in a temporary queue until a <code>DBMS_JOB</code> pushes the queue. The <code>DBMS_JOB</code> utilizes two preferences named <code>SMTP_HOST_ADDRESS</code> and <code>SMTP_HOST_PORT</code> to send mail in the queue. By default, these preferences are set to <code>localhost</code> and <code>25</code>. </p> <p>If the server where Oracle HTML DB is installed is not configured for SMTP services, you will need to change the <code>SMTP_HOST_ADDRESS</code> preference. Check with your Oracle HTML DB administrator to make sure the instance you are using is properly configured to send e-mail. </p> <p>The following is a description of the SEND procedure of the <code>HTMLDB_MAIL</code> package. </p> <pre xml:space="preserve" class="oac_no_warn">PROCEDURE SEND Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- P_TO VARCHAR2 IN P_FROM VARCHAR2 IN P_BODY VARCHAR2 IN P_BODY_HTML VARCHAR2 IN DEFAULT P_SUBJ VARCHAR2 IN DEFAULT P_CC VARCHAR2 IN DEFAULT P_BCC VARCHAR2 IN DEFAULT </pre> </div><!-- class="sect3" --> <a id="BJFCCECG" name="BJFCCECG"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref320" name="sthref320"></a> <h4>Add Notification of New Assignments </h4> <p>First, you will add a notification to a person when he or she has a new assignment. An assignment can be made or changed from two different pages </p> <ul> <li type="disc"> <p>Create/Edit Issue </p> </li> <li type="disc"> <p>Assign Issues. </p> </li> </ul> <p>On the Create/Edit Issue page, you can store the initial values and then check them against any changes to see if an assignment has been made or changed. Because Assign Issues is a tabular form, there is no way to check the old values against the new values. For that reason, the best way to implement the notification is with a before insert and update trigger on <code>HT_ISSUES</code>. You can create this trigger programmatically using SQL Workshop. </p> <p>To create a before insert and update trigger on <code>HT_ISSUES</code>: </p> <ol type="1" start="1"> <li> <p>Navigate to the Oracle HTML DB home page. </p> </li> <li> <p>Click <span class="bold">SQL Workshop</span>. </p> </li> <li> <p>Click <span class="bold">Create Object</span>. </p> </li> <li> <p>For Select the type of database object you wish to create, select <span class="bold">Trigger</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Schema, select the appropriate schema and click <span class="bold">Next</span>. </p> </li> <li> <p>For Table Name, select <span class="bold">HT_ISSUES</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Action, keep the default of <span class="bold">Create Trigger</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Trigger Name, enter <code>BIU_HT_ISSUES_NOTIFY_ASSIGNEE</code>. </p> <ol type="a"> <li> <p>For <span class="bold">Firing Point</span>, select <span class="bold">AFTER</span>. </p> </li> <li> <p>For <span class="bold">Options</span>, select <span class="bold">insert, update</span>. </p> </li> <li> <p>For <span class="bold">Trigger Body</span>, enter: </p> <pre xml:space="preserve" class="oac_no_warn">IF (INSERTING AND :new.assigned_to IS NOT NULL) OR (UPDATING AND (:old.assigned_to IS NULL OR :new.assigned_to != :old.assigned_to) AND :new.assigned_to IS NOT NULL) THEN FOR c1 IN (SELECT person_name, person_email FROM ht_people WHERE person_id = :new.assigned_to) LOOP IF c1.person_email IS NOT NULL THEN FOR c2 IN (SELECT project_name FROM ht_projects WHERE project_id = :new.related_project) LOOP HTMLDB_MAIL.SEND( p_to => c1.person_email, p_from => c1.person_email, p_body => 'You have been assigned a new issue. '|| '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; </pre> </li> </ol> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> <li> <p>To review the code, click the plus icon to the left of <span class="bold">SQL</span>. </p> </li> <li> <p>Click <span class="bold">Finish</span>. </p> </li> <li> <p>When the Success page appears, select <span class="bold">SQL Workshop Home</span>. </p> </li> </ol> <div align="center"> <div class="inftblNote"> <br /><table class = "Note oac_no_warn" summary="This is a layout table to format a note" title="This is a layout table to format a note" dir="ltr" border="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <tbody> <tr> <td align="left"> <p class="notep1">Note:</p>If you plan on using this application, you should either disable this trigger or change the <code>p_to</code> and <code>p_from</code> to your own e-mail address so that you do not create e-mails with invalid addresses each time you assign or reassign an issue. </td> </tr> </tbody> </table><br /> </div><!-- class="inftblNote --> </div> </div><!-- class="sect3" --> <a id="BJFDECJH" name="BJFDECJH"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref321" name="sthref321"></a> <h4>Add a Notification for Overdue Issues </h4> <p>The second email notification notifies the project lead whenever an issue becomes overdue. An issue becomes overdue when the target resolution date has passed, but the issue is not yet closed. Because there is no human interaction to determine if an issues overdue, you cannot check for it on a page or in a trigger. </p> <p>The best way to check for overdue issues is to write a package that queries the <code>HT_ISSUES</code> table. If it finds any overdue issues, the package will initiate an email to the Project Lead. This procedure will check for issues by project so that the project lead will receive just one email with all overdue issues rather than an email for each issue. The package will be called once a day by a <code>dbms_job</code>. </p> <p>You can use the Create Object function as follows: </p> <ul> <li type="disc"> <p>Create the package and package body from within the SQL Workshop </p> </li> <li type="disc"> <p>Use SQL Command Processor to run the create commands </p> </li> </ul> <p>For this exercise, you will use SQL Command Processor. </p> <p>To create the package: </p> <ol type="1" start="1"> <li> <p>Navigate to the SQL Workshop home page. </p> </li> <li> <p>Select <span class="bold">SQL Command Processor</span>. </p> </li> <li> <p>For <span class="bold">Schema</span>, select the appropriate schema. </p> </li> <li> <p>For Enter a SQL or PL/SQL Statement enter: </p> <pre xml:space="preserve" class="oac_no_warn">CREATE OR REPLACE package ht_check_overdue_issues AS PROCEDURE email_overdue; END; / </pre> </li> <li> <p>Click <span class="bold">Run SQL</span>. </p> </li> </ol> <p>To create the package body: </p> <ol type="1" start="1"> <li> <p>Navigate to the SQL Workshop home page. </p> </li> <li> <p>Select <span class="bold">SQL Command Processor</span>. </p> </li> <li> <p>For <span class="bold">Schema</span>, select the appropriate schema. </p> </li> <li> <p>In Enter a SQL or PL/SQL Statement, enter the following: </p> <pre xml:space="preserve" class="oac_no_warn">CREATE OR REPLACE PACKAGE BODY ht_check_overdue_issues AS PROCEDURE email_overdue IS l_msg_body varchar2(32000) := null; l_count number := 0; BEGIN FOR c1 IN (SELECT pr.project_id, pr.project_name, pe.person_name, pe.person_email FROM ht_projects pr, ht_people pe WHERE pr.project_id = pe.assigned_project AND pe.person_role = 'Lead') LOOP FOR c2 IN (SELECT i.target_resolution_date, i.issue_summary, p.person_name, i.status, i.priority FROM ht_issues i, ht_people p WHERE i.assigned_to = p.person_id (+) AND i.related_project = c1.project_id AND i.target_resolution_date < SYSDATE AND i.status != 'Closed' ORDER BY i.target_resolution_date, i.issue_summary) LOOP IF l_count = 0 THEN l_msg_body := 'As of today, the following issues '|| 'are overdue:'||chr(10)|| chr(10)|| ' Project: '|| c1.project_name ||chr(10)|| chr(10)|| ' Target: '||c2.target_resolution_date ||chr(10)|| ' Summary: '||c2.issue_summary ||chr(10)|| ' Status: '||c2.status ||chr(10)|| ' Priority: '||c2.priority ||chr(10)|| 'Assigned to: '||c2.person_name; ELSE l_msg_body := l_msg_body ||chr(10)|| chr(10)|| ' Target: '||c2.target_resolution_date ||chr(10)|| ' Summary: '||c2.issue_summary ||chr(10)|| ' Status: '||c2.status ||chr(10)|| ' Priority: '||c2.priority ||chr(10)|| 'Assigned to: '||c2.person_name; END IF; l_count := l_count + 1; END LOOP; IF l_msg_body IS NOT NULL THEN HTMLDB_MAIL.SEND( p_to => c1.person_email, p_from => c1.person_email, p_body => l_msg_body, p_subj => 'Overdue Issues for Project '|| c1.project_name); END IF; l_count := 0; END LOOP; END email_overdue; END ht_check_overdue_issues; / </pre> </li> <li> <p>Click <span class="bold">Run SQL</span>. </p> </li> </ol> <p>To create the <code>DBMS_JOB</code>: </p> <ol type="1" start="1"> <li> <p>Navigate to the SQL Workshop home page. </p> </li> <li> <p>Select <span class="bold">SQL Command Processor</span>. </p> </li> <li> <p>For <span class="bold">Schema</span>, select the appropriate schema. </p> </li> <li> <p>In Enter a SQL or PL/SQL Statement, enter the following: </p> <pre xml:space="preserve" class="oac_no_warn">DECLARE jobno number; BEGIN DBMS_JOB.SUBMIT( job => jobno, what => 'BEGIN ht_check_overdue_issues.email_overdue; END;', next_date => SYSDATE, interval => 'TRUNC(SYSDATE)+(25/24)' ); COMMIT; END; / </pre> </li> <li> <p>Click <span class="bold">Run SQL</span>. </p> </li> </ol> <p>This dbms_job executes just after midnight each day. </p> <div align="center"> <div class="inftblNote"> <br /><table class = "Note oac_no_warn" summary="This is a layout table to format a note" title="This is a layout table to format a note" dir="ltr" border="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <tbody> <tr> <td align="left"> <p class="notep1">Note:</p>If you plan on using this application, change either the <code>p_to</code> and <code>p_from</code> in the <code>ht_check_overdue_issues</code> package body to your own email address, or do not create the <code>dbms_job</code>. Running the code as written results in the creation of email with invalid addresses that will sit in mail queue. </td> </tr> </tbody> </table><br /> </div><!-- class="inftblNote --> </div> <div align="center"> <div class="inftblNoteAlso"> <br /><table class = "NoteAlso oac_no_warn" summary="This is a layout table to format a tip" title="This is a layout table to format a tip" dir="ltr" border="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <tbody> <tr> <td align="left"> <p class="notep1">See Also:</p><span class="italic">Send email from HTML DB applications</span> How To on OTN at: <pre xml:space="preserve" class="oac_no_warn"><code><a href="http://www.oracle.com/technology/products/database/htmldb/howtos/index.html">http://www.oracle.com/technology/products/database/htmldb/howtos/index.html</a></code> </pre> </td> </tr> </tbody> </table><br /> </div><!-- class="inftblNoteAlso --> </div> </div><!-- class="sect3" --> </div><!-- class="sect2" --> <a id="BJFJAJGH" name="BJFJAJGH"></a> <div class="sect2"> <!-- infolevel=all infotype=General --><a id="sthref322" name="sthref322"></a> <h3>Add Application Security </h3> <p>The planning and project analysis phase produced two security requirements: </p> <ul> <li type="disc"> <p>Only the CEO and Managers can define and maintain projects and people </p> </li> <li type="disc"> <p>Once assigned, only the person assigned or a project lead can change data about the issue </p> </li> </ul> <p>Within Oracle HTML DB, you can define authorization schemes. Authorization controls user access to specific controls or components based on user privileges. Once defined, you can associate an authorization scheme with any page, region or item to restrict access. Each authorization schema is run only when needed and is defined to validate either once for each page view or once for each session. </p> <p class="subhead2">Topics:</p> <ul> <li type="disc"> <p><a href="#BJFJGDCJ">Restrict Project and People Definition</a> </p> </li> <li type="disc"> <p><a href="#BJFBAECG">Restrict Issue Modification</a> </p> </li> </ul><a id="BJFJGDCJ" name="BJFJGDCJ"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref323" name="sthref323"></a> <h4>Restrict Project and People Definition </h4> <p>The first requirement states only the CEO and Managers may define and maintain projects and people. To address this requirement you will: </p> <ul> <li type="disc"> <p>Create an authorization scheme to check the current user's role </p> </li> <li type="disc"> <p>Associate the authorization scheme with the items on the Projects and People report that navigate to the Create/Edit pages </p> </li> <li type="disc"> <p>Associate the authorization scheme with the Create/Edit pages themselves so that a user cannot bypass the security by manually editing the URL to the target page. </p> </li> </ul> <p>To reference the current user, use the session variable <code>:APP_USER</code>. This will be compared with the person's email address (which is the same as their Oracle HTML DB user name). Whenever coding this type of security, you should always code in a user that can pass all security. You will find this user very useful for development and testing. If you do not take this approach, you will not be able to access the restricted pages unless you define yourself as the CEO or Manager. </p> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref324" name="sthref324"></a> <h5>Create the Authorization Scheme </h5> <p>To create the authorization scheme: </p> <ol type="1" start="1"> <li> <p>Navigate to the Oracle HTML DB home page. </p> </li> <li> <p>Click <span class="bold">Application Builder</span>. </p> </li> <li> <p>Click <span class="bold">Shared Components</span>. </p> </li> <li> <p>Under Security, select <span class="bold">Authorization</span>. </p> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> <li> <p>For Create Authorization Scheme, accept the default <span class="bold">From Scratch</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>Under Authorization Scheme Identification, enter the following in Name: </p> <pre xml:space="preserve" class="oac_no_warn">USER_CEO_OR_MANAGER </pre> </li> <li> <p>Under Authorization Scheme: </p> <ol type="a"> <li> <p>For Scheme Type, select <span class="bold">Exists SQL Query</span>. </p> </li> <li> <p>In Expression 1, enter: </p> <pre xml:space="preserve" class="oac_no_warn">SELECT '1' FROM ht_people WHERE (upper(person_email) = UPPER(:APP_USER) AND person_role IN ('CEO','Manager')) OR (UPPER(:APP_USER) = 'HOWTO') </pre> </li> <li> <p>For Identify error message displayed when scheme violated, enter: </p> <pre xml:space="preserve" class="oac_no_warn">You are not authorized to access this function. </pre> </li> </ol> </li> <li> <p>Scroll down to Evaluation Point. For Validate authorization scheme, select <span class="bold">Once per session</span>. </p> <p>This selection is sufficient in this instance since the assigned role will not typically change within a given session. </p> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> </ol> <p>Next, you need to associate the authorization scheme with the appropriate objects. </p> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref325" name="sthref325"></a> <h5>Associate Objects on the Projects Report </h5> <p>To associate the authorization scheme with the Projects report: </p> <ol type="1" start="1"> <li> <p>Click the Edit Page icon. </p> </li> <li> <p>In Page, enter <code>2</code> and click <span class="bold">Go</span>. </p> <p>The Page Definition for page 2, Projects, appears. </p> </li> <li> <p>Under Regions, select <span class="bold">Q</span> to the left of Projects. </p> </li> <li> <p>Click the <span class="bold">Edit</span> icon to the left of <code>PROJECT_ID</code>. </p> </li> <li> <p>Under Authorization, select the Authorization Scheme <span class="bold">USER_CEO_OR_MANAGER</span>. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> <li> <p>Click <span class="bold">Cancel</span>. </p> </li> </ol> <p>To associate the authorization scheme with the Create button on the Projects report: </p> <ol type="1" start="1"> <li> <p>On the Page Definition for page 2, select the <span class="bold">CREATE</span> button </p> </li> <li> <p>Under Authorization, select the Authorization Scheme <span class="bold">USER_CEO_OR_MANAGER</span>. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref326" name="sthref326"></a> <h5>Associate Objects with the Create/Edit Report </h5> <p>To associate the authorization scheme with the Create/Edit Project page: </p> <ol type="1" start="1"> <li> <p>Navigate to page 3 by clicking the Next Page (>) icon. </p> <p>The Page Definition for page 3, Create/Edit Project, appears. </p> </li> <li> <p>Click <span class="bold">Edit Attributes</span>. </p> </li> <li> <p>Under Security, select the Authorization Scheme <span class="bold">USER_CEO_OR_MANAGER</span>. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref327" name="sthref327"></a> <h5>Associate Objects with the People Report </h5> <p>To associate the authorization scheme with the People report. </p> <ol type="1" start="1"> <li> <p>Navigate to page 4 by clicking the Next Page (>) icon. </p> <p>The Page Definition for page 4, People, appears. </p> </li> <li> <p>Under Regions, select <span class="bold">Q</span> to the left of People. </p> </li> <li> <p>Click the edit link to the left of PERSON_ID. </p> </li> <li> <p>Under Authorization, select the Authorization Scheme <span class="bold">USER_CEO_OR_MANAGER</span>. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> <li> <p>Click <span class="bold">Cancel</span>. </p> </li> </ol> <p>To associate the authorization scheme with the Create button on the People report: </p> <ol type="1" start="1"> <li> <p>On the Page Definition for page 5, select the <span class="bold">CREATE</span> button </p> </li> <li> <p>Under Authorization, select the Authorization Scheme <span class="bold">USER_CEO_OR_MANAGER</span>. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> <p>To associate the authorization scheme with the Create/Edit Person Information page: </p> <ol type="1" start="1"> <li> <p>Navigate to page 5. </p> <p>The Page Definition for page 5, Create/Edit Person Information, appears. </p> </li> <li> <p>Click <span class="bold">Edit Attributes</span>. </p> </li> <li> <p>Under Security, select the Authorization Scheme <span class="bold">USER_CEO_OR_MANAGER</span>. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> <p>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. </p> <div align="center"> <div class="inftblNoteAlso"> <br /><table class = "NoteAlso oac_no_warn" summary="This is a layout table to format a tip" title="This is a layout table to format a tip" dir="ltr" border="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <tbody> <tr> <td align="left"> <p class="notep1">See Also:</p><a href="#BJFDFDIE">"Create Users"</a> </td> </tr> </tbody> </table><br /> </div><!-- class="inftblNoteAlso --> </div> </div><!-- class="sect4" --> </div><!-- class="sect3" --> <a id="BJFBAECG" name="BJFBAECG"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref328" name="sthref328"></a> <h4>Restrict Issue Modification </h4> <p>The second requirement states that once an issue has been assigned, only the person assigned (or a project lead) can change data about the issue. This requirement is a little trickier since it changes for every issue. </p> <p>Currently, there are two pages that enable users to modify an issue, the Create/Edit Issue page and the Assign Issues page. On the Assign Issues page, the only issues that display are those that are unassigned. Since the issues are unassigned, security is not necessary. </p> <p>There are many places that a user can navigate to edit an issue: </p> <ul> <li type="disc"> <p>Three regions on the home page display issues or have edit links </p> </li> <li type="disc"> <p>The Issues report has links to edit each issue </p> </li> <li type="disc"> <p>The Target Resolution Dates report enables users to select an issue to edit. </p> </li> </ul> <p>Although other users are not allowed to change the data, you do want to enable users to view all the detailed data about an issue so that they can view the progress and resolution. Given this requirement, the best approach would be to create an authorization scheme that will be evaluated once for each page view. </p> <p>The authorization scheme will be associated with both the Apply Changes and Delete buttons on the Create/Edit Issue page. This way, unauthorized users can view all the details, but if they do change something, they have no way of saving that change. </p> <p>For added security, you will also associate the authorization scheme with the process that performs the insert, update and delete on <code>HT_ISSUES</code>. This protects your application against someone changing the URL to call the Apply Changes process. To let users know why they are not able to make changes, you will add an HTML region which will display an explanation when the authorization fails. The SQL for this scheme will be specific to the Create/Edit Issues page because it needs to reference P7_ISSUE_ID. It will need to retrieve data from the database because at the time it will be evaluated, the necessary data will not be available in the session state. The only item that will be available will be P7_ISSUE_ID because it will be passed by the link. </p> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref329" name="sthref329"></a> <h5>Create the Authorization Scheme </h5> <p>To create the authorization scheme: </p> <ol type="1" start="1"> <li> <p>Navigate to the Application Builder home page. </p> </li> <li> <p>Select <span class="bold">Shared Components</span>. </p> </li> <li> <p>Under Security, select <span class="bold">Authorization</span>. </p> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> <li> <p>For Create Authorization Scheme, accept the default <span class="bold">From Scratch</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>Under Authorization Scheme Identification, enter the following in Name: </p> <pre xml:space="preserve" class="oac_no_warn">P7_ASSIGNED_OR_PROJECT_LEAD </pre> </li> <li> <p>Under Authorization Scheme: </p> <ol type="a"> <li> <p>For Scheme Type, select <span class="bold">PL/SQL Function Returning Boolean</span>. </p> </li> <li> <p>For Expression 1, enter: </p> <pre xml:space="preserve" class="oac_no_warn">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; </pre> </li> <li> <p>For Identify error message displayed when scheme violated, enter: </p> <pre xml:space="preserve" class="oac_no_warn">This issue is not assigned to you, nor are you the Project Lead. Therefore you are not authorized to modify the data. </pre> </li> </ol> </li> <li> <p>Scroll down to Evaluation Point. For Validate authorization scheme, select <span class="bold">Once per page view</span>. </p> <p>This selection is necessary since each issue may have a different result. </p> </li> <li> <p>Click <span class="bold">Create</span>. </p> </li> </ol> <p>Now you need to associate the authorization scheme with the appropriate objects on the Create/Edit Issue page. </p> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref330" name="sthref330"></a> <h5>Associate Objects with the Create Edit Issues Report </h5> <p>To associate the authorization scheme with buttons and processes: </p> <ol type="1" start="1"> <li> <p>Click the <span class="bold">Edit Page</span> icon. </p> </li> <li> <p>Navigate to page 7. Enter <code>7</code> in the Page field and click <span class="bold">Go</span>. </p> </li> <li> <p>Under Buttons, select <span class="bold">DELETE</span>. </p> <ol type="a"> <li> <p>Under Authorization, select the Authorization Scheme <span class="bold">P7_ASSIGNED_OR_PROJECT_LEAD</span>. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> </li> <li> <p>Under Buttons, select <span class="bold">SAVE</span>. </p> <ol type="a"> <li> <p>Under Authorization, select the Authorization Scheme <span class="bold">P7_ASSIGNED_OR_PROJECT_LEAD</span>. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> </li> <li> <p>Under Processes, select <span class="bold">Process Row of HT_ISSUES</span>. </p> <ol type="a"> <li> <p>Under Authorization, select the Authorization Scheme <span class="bold">P7_ASSIGNED_OR_PROJECT_LEAD</span>. </p> </li> <li> <p>Click <span class="bold">Apply Changes</span>. </p> </li> </ol> </li> </ol> </div><!-- class="sect4" --> <div class="sect4"> <!-- infolevel=all infotype=General --><a id="sthref331" name="sthref331"></a> <h5>Create an HTML Region </h5> <p>Lastly, create a new region to display an explanation when the authorization fails </p> <p>To create a new region: </p> <ol type="1" start="1"> <li> <p>Under Regions, click the <span class="bold">Create</span> icon. </p> </li> <li> <p>For Select a common region type, accept the default <span class="bold">HTML</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Region Type, select <span class="bold">HTML</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For Identify Region Attributes: </p> <ol type="a"> <li> <p>For Title, enter <code>Not Authorized</code>. </p> </li> <li> <p>For Display Point, select <span class="bold">Page Template Body (2. items below region content)</span>. </p> </li> <li> <p>Click <span class="bold">Next</span>. </p> </li> </ol> </li> <li> <p>For <span class="bold">Enter HTML Text Region Source</span>, enter the following and click <span class="bold">Next</span>: </p> <pre xml:space="preserve" class="oac_no_warn">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. </pre> </li> <li> <p>For Authorization Scheme, select <span class="bold">{Not}P7_ASSIGNED_OR_PROJECT_LEAD</span>. This will make the region only display when the Authorization Scheme fails. </p> </li> <li> <p>Click <span class="bold">Create Region</span>. </p> </li> </ol> <p><a href="#BABCDDFB">Figure 10-18</a> 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. </p> <div class="figure"> <a id="BABCDDFB" name="BABCDDFB"></a><a id="sthref332" name="sthref332"></a> <p class="titleinfigure">Figure 10-18 New Region Displaying Authorization Failure</p> <img src="./img/iss_auth.gif" alt="Description of iss_auth.gif follows" title="Description of iss_auth.gif follows"/> <br /><a id="sthref333" name="sthref333" href="./img_text/iss_auth.htm">Description of the illustration iss_auth.gif</a> <br /><br /> </div><!-- class="figure" --> <p>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. </p> </div><!-- class="sect4" --> </div><!-- class="sect3" --> </div><!-- class="sect2" --> </div><!-- class="sect1" --> <a id="BABBGDAA" name="BABBGDAA"></a> <div class="sect1"> <!-- infolevel=all infotype=General --><a id="sthref334" name="sthref334"></a> <h2>Deploying Your Application </h2> <p>Now that your application is complete, the next step is to deploy it. Typically, developers create applications on one server and deploy it on another. Although this approach is not required, it enables you to resolve bugs without impacting the production instance. </p> <div align="center"> <div class="inftblNote"> <br /><table class = "Note oac_no_warn" summary="This is a layout table to format a note" title="This is a layout table to format a note" dir="ltr" border="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <tbody> <tr> <td align="left"> <p class="notep1">Note:</p>To deploy an application on another server, you need to install and configure another Oracle HTML DB instance. </td> </tr> </tbody> </table><br /> </div><!-- class="inftblNote --> </div> <p class="subhead2">Topics:</p> <ul> <li type="disc"> <p><a href="#BJFHGECI">Move the Application Definition</a> </p> </li> <li type="disc"> <p><a href="#BJFIIFBB">Alternate Authentication Mechanisms to Consider</a> </p> </li> <li type="disc"> <p><a href="#BJFDFDIE">Create Users</a> </p> </li> <li type="disc"> <p><a href="#BJFDIJEB">Publish the URL</a> </p> </li> </ul><a id="BJFHGECI" name="BJFHGECI"></a> <div class="sect2"> <!-- infolevel=all infotype=General --><a id="sthref335" name="sthref335"></a> <h3>Move the Application Definition </h3> <p>The definition for your application lives within the Oracle database. The application definition includes everything that makes up the application, including the templates, but it does not include database object definitions or the underlying data. To move an application to another Oracle HTML DB instance, you must export the application definition from your development server and import it into your production server. </p> <p class="subhead2">Topics:</p> <ul> <li type="disc"> <p><a href="#BJFBFDGJ">Export the Application Definition</a> </p> </li> <li type="disc"> <p><a href="#BJFFGHIC">Create the Required Objects to Support the Application</a> </p> </li> <li type="disc"> <p><a href="#BJFEIDCH">Import the Application Definition into the Production Instance</a> </p> </li> <li type="disc"> <p><a href="#BJFBECDE">Load the Data</a> </p> </li> </ul><a id="BJFBFDGJ" name="BJFBFDGJ"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref336" name="sthref336"></a> <h4>Export the Application Definition </h4> <p>To export the application definition from your development server: </p> <ol type="1" start="1"> <li> <p>Navigate to the Application Builder home page. </p> </li> <li> <p>Select <span class="bold">Export / Import</span>. </p> </li> <li> <p>For Select Task, accept the default <span class="bold">Export</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>For <span class="bold">Application</span>, select the application created in this exercise. </p> </li> <li> <p>Click <span class="bold">Export Application</span>. </p> </li> <li> <p>When prompted, click to <span class="bold">Save</span> the file. </p> </li> <li> <p>Specify a location on your local hard drive and click <span class="bold">Save</span>. </p> </li> </ol> </div><!-- class="sect3" --> <a id="BJFFGHIC" name="BJFFGHIC"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref337" name="sthref337"></a> <h4>Create the Required Objects to Support the Application </h4> <p>On your production instance, you need to create the objects necessary to support the application. Log in to the production instance and follow the directions in <a href="#BABEHAEH">"Build the Database Objects"</a>. </p> <div align="center"> <div class="inftblNote"> <br /><table class = "Note oac_no_warn" summary="This is a layout table to format a note" title="This is a layout table to format a note" dir="ltr" border="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <tbody> <tr> <td align="left"> <p class="notep1">Note:</p>Although the supporting objects do not need to exist for you to import the application definition, be aware you cannot test the code until they exist. </td> </tr> </tbody> </table><br /> </div><!-- class="inftblNote --> </div> </div><!-- class="sect3" --> <a id="BJFEIDCH" name="BJFEIDCH"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref338" name="sthref338"></a> <h4>Import the Application Definition into the Production Instance </h4> <p>Login to the production instance of Oracle HTML DB: </p> <ol type="1" start="1"> <li> <p>Navigate to the Application Builder home page. </p> </li> <li> <p>Select <span class="bold">Export / Import</span>. </p> </li> <li> <p>For <span class="bold">Select Task</span>, select <span class="bold">Import</span> and click <span class="bold">Next</span>. </p> </li> <li> <p>On Import File: </p> <ol type="a"> <li> <p>For Import File, click the <span class="bold">Browse</span> button and locate your exported file. </p> </li> <li> <p>For File Type, select <span class="bold">Application/Page Export</span>. </p> </li> <li> <p>For File Character Set, accept the default and click <span class="bold">Next</span>. </p> </li> </ol> <p>Once the success message appears, the next step is to install the file. </p> </li> <li> <p>Click <span class="bold">Install</span>. </p> </li> <li> <p>On Application Install: </p> <ol type="a"> <li> <p>For Parse As Schema, select the schema on your production server that contains your application objects. </p> </li> <li> <p>For Build Status, you select <span class="bold">Run and Build Application</span>. </p> <p>This option enables other users to run the application and enables you to log in and change the code if necessary. Alternatively, you can select <span class="bold">Run Application Only</span>. Be aware that if you select this option you will not be able to access the source code for the application. </p> </li> <li> <p>For Install As Application, you can select: </p> <ul> <li type="circle"> <p><span class="bold">Reuse Application ID</span> - Only select this option if the application ID is not being used on the production instance. </p> </li> <li type="circle"> <p><span class="bold">Auto Assign New Application ID</span> - Select this option to assign a new application ID. </p> </li> <li type="circle"> <p><span class="bold">Change Application ID</span> - Select this option to change the existing application ID. If you select this option, you will be prompted to enter a new application ID. </p> <p>When you install an application having the same ID as an existing application in the current workspace, the existing application is deleted and then the new application is installed. If you attempt to install an application having the same ID as an existing application in a different workspace, an error message appears. </p> <p>If all statements are successful the install commits and becomes permanent. If any errors are encountered, the install is rolled back, resulting in no permanent changes. </p> </li> </ul> </li> <li> <p>Click <span class="bold">Install Application</span>. </p> </li> </ol> <p>If the install is successful, the Post-App Install Utility Options page appears. From here, you can select one of the following: </p> <ul> <li type="disc"> <p>Select <span class="bold">Run Application</span> to see application running </p> </li> <li type="disc"> <p>Select <span class="bold">Edit Application Attributes</span> to view the application definition within Oracle HTML DB </p> </li> </ul> </li> </ol> </div><!-- class="sect3" --> <a id="BJFBECDE" name="BJFBECDE"></a> <div class="sect3"> <!-- infolevel=all infotype=General --><a id="sthref339" name="sthref339"></a> <h4>Load the Data </h4> <p>The next step in deploying your application would be to load the data. At a minimum, you would need to populate the <code>project</code> and <code>people</code> tables. </p> <p> Note there are various mechanisms you could use to accomplish this task, including: </p> <ul> <li type="disc"> <p>Use the application itself to create data. </p> </li> <li type="disc"> <p>Use the Data Loader to load data copied from a spreadsheet. </p> </li> <li type="disc"> <p>Use the SQL Workshop and run scripts to create data. </p> </li> <li type="disc"> <p>If you have data existing already within an Oracle database, use either export/import to move data between machines or use SQL to retrieve and transform existing data and load it into the application tables. </p> </li> </ul> <div align="center"> <div class="inftblNoteAlso"> <br /><table class = "NoteAlso oac_no_warn" summary="This is a layout table to format a tip" title="This is a layout table to format a tip" dir="ltr" border="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <tbody> <tr> <td align="left"> <p class="notep1">See Also:</p><a href="#BABBJHGB">"Loading Demonstration Data"</a> </td> </tr> </tbody> </table><br /> </div><!-- class="inftblNoteAlso --> </div> </div><!-- class="sect3" --> </div><!-- class="sect2" --> <a id="BJFIIFBB" name="BJFIIFBB"></a> <div class="sect2"> <!-- infolevel=all infotype=General --><a id="sthref340" name="sthref340"></a> <h3>Alternate Authentication Mechanisms to Consider </h3> <p>When the application login page calls the Oracle HTML DB login API with a username and password, the HTML DB engine calls the credentials verification method specified in the application's current authentication scheme. You have three choices as to how credentials are verified from within the login API: </p> <ul> <li type="disc"> <p>Implement the method yourself as a PL/SQL function returning boolean and put it in your application's schema. </p> </li> <li type="disc"> <p>Use the built-in LDAP authentication method, which checks username and password against the LDAP directory that you specify. </p> </li> <li type="disc"> <p>Use the built-in Oracle HTML DB authentication method, which checks the username and password against the Oracle HTML DB account repository. </p> </li> </ul> <p>Your application is currently using the built-in Oracle HTML DB. It is also possible to use an external authentication service such Oracle Application Server Single Sign-On. Be aware, however, that Oracle HTML DB redirects to these services instead of showing a login page. </p> <div align="center"> <div class="inftblNoteAlso"> <br /><table class = "NoteAlso oac_no_warn" summary="This is a layout table to format a tip" title="This is a layout table to format a tip" dir="ltr" border="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <tbody> <tr> <td align="left"> <p class="notep1">See Also:</p><span class="italic">Using Oracle AS Single Sign-On with HTML DB Applications</span> and <span class="italic">Changing Authentication Methods</span> How To documents on OTN: <pre xml:space="preserve" class="oac_no_warn"><code><a href="http://www.oracle.com/technology/products/database/htmldb/howtos/index.html">http://www.oracle.com/technology/products/database/htmldb/howtos/index.html</a></code> </pre> </td> </tr> </tbody> </table><br /> </div><!-- class="inftblNoteAlso --> </div> </div><!-- class="sect2" --> <a id="BJFDFDIE" name="BJFDFDIE"></a> <div class="sect2"> <!-- infolevel=all infotype=General --><a id="sthref341" name="sthref341"></a> <h3>Create Users </h3> <p>In order for your application to be accessible, you need to create users. If you are still using Oracle HTML DB authentication, the simplest way to create users it to access the Manage Users page within the workspace that owns the application. Note that you will need to have been granted Administration rights for the workspace in order to access this page. </p> <p>To create a new user: </p> <ol type="1" start="1"> <li> <p>Navigate to the HTML DB home page. </p> </li> <li> <p>From the Workspace Administration list on the right side of the page, select <span class="bold">Manage Users</span>. </p> </li> <li> <p>Select <span class="bold">Create New User</span>. </p> </li> <li> <p>Under User Identification, enter the required information. If you are using Oracle HTML DB authentication, make User Name and Email Address identical. </p> </li> <li> <p>Under Developer Privileges: </p> <ol type="a"> <li> <p>For User is a developer, select <span class="bold">No</span>. </p> </li> <li> <p>For User is an administrator, select <span class="bold">No</span>. </p> <p>These settings enable the user to run the application but not access the Application Builder. </p> </li> </ol> </li> <li> <p>Click <span class="bold">Create</span> or <span class="bold">Create and Create Another</span>. </p> </li> </ol> </div><!-- class="sect2" --> <a id="BJFDIJEB" name="BJFDIJEB"></a> <div class="sect2"> <!-- infolevel=all infotype=General --><a id="sthref342" name="sthref342"></a> <h3>Publish the URL </h3> <p>Now that you have deployed your application, loaded data, and created users, you can publish your production URL. </p> <p>You can determine the URL to your application by positioning the mouse over the <span class="bold">Run</span> icon on the Application Builder home page. The URL displays in the status bar at the bottom of the page. </p> <p>The Run icon gets its value from the Home link found under the Session Management section of the Application Attributes page. This link is only referenced by this icon and by applications that do not use the Oracle HTML DB Login API. For example, the Issue Tracker on the public Oracle HTML DB development environment, the URL might resemble this: </p> <pre xml:space="preserve" class="oac_no_warn">http://htmldb.oracle.com/pls/otn/f?p=11563:1:3397731373043366363 </pre> <p>Where: </p> <ul> <li type="disc"> <p><code>htmldb.oracle.com</code> is the URL of the server </p> </li> <li type="disc"> <p><code>pls</code> is the indicator to use the <code>mod_plsql</code> cartridge </p> </li> <li type="disc"> <p><code>otn</code> is the DAD name </p> </li> <li type="disc"> <p><code>f?p= </code>is a prefix used by Oracle HTML DB </p> </li> <li type="disc"> <p><code>11563</code> is application being called </p> </li> <li type="disc"> <p><code>1</code> is the page within the application to be displayed </p> </li> <li type="disc"> <p><code>3397731373043366363</code> is the session number </p> </li> </ul> <p>To run this example application, you would use the URL: </p> <pre xml:space="preserve" class="oac_no_warn">http://htmldb.oracle.com/pls/otn/f?p=11563:1 </pre> <p>When each user logs in, he or she will receive an unique session number. </p> <p>As you may recall, you created the Issue Tracker application using the Create Application wizard. This wizard creates a process on the Login page (page 101) that controls authentication. The contents of the process are: </p> <pre xml:space="preserve" class="oac_no_warn">WWV_FLOW_CUSTOM_AUTH_STD.LOGIN( P_UNAME => :P101_USERNAME, P_PASSWORD => :P101_PASSWORD, P_SESSION_ID => :FLOW_SESSION, P_FLOW_PAGE => :APP_ID||':1' ); </pre> <p>Note that the Page is hard coded into this process. Because of this, the page you pass in the URL is overwritten and does not need to be included. You can access the application by using the following URL: </p> <pre xml:space="preserve" class="oac_no_warn">http://htmldb.oracle.com/pls/otn/f?p=11563:1 </pre> <p>As you can see from the example used, the URL has no meaning and can be rather long. The host name can be changed to make it more symbolic. You can also configure Apache to rewrite your URL so that you can publish an abbreviated format and a URL that would be more intuitive to your users. See your Apache documentation for details. </p> </div><!-- class="sect2" --> </div><!-- class="sect1" --> </div> <!-- Start Footer --> <div class="footer"> <hr /> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> <tr> <td valign="bottom" width="33%"> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100"> <tr><td> </td> <td align="center"> <a href="javascript.htm"> <img src="./support/gifs/leftnav.gif" alt="Previous" border="0" /><br /><span class="icon">Previous</span></a> </td> <td align="center"> <a href="scripts.htm"> <img src="./support/gifs/rightnav.gif" alt="Next" border="0" /><br /><span class="icon">Next</span></a> </td> </tr> </table> </td> <td class="copyrightlogo"> <a href="./support/html/cpyr.htm"> <span class="copyrightlogo"> Copyright © 2005, Oracle. All rights reserved. </span> </a> </td> <td valign="bottom" align="right" width="33%"> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="225"> <tr><td> </td> <td align="center" valign="top"><a href="toc.htm"><img src="./support/gifs/toc.gif" alt="Go To Table Of Contents" border="0" /><br /><span class="icon">Contents</span></a></td> </table> </td> </tr> </table> </div><!-- class="footer" --> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de