Creating and Running an Application Using Oracle Application Express 3.2

<Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a browser>

Purpose

This tutorial shows you how to rapidly create an application using Oracle Application Express.

Prerequisites

Before you perform this tutorial, you should:

.	Perform the Manipulating Database Objects Using Application Express 3.2 tutorial.

Creating an Application

To create the application framework and default a few initial pages, perform the following steps:

.	From the Oracle Application Express home page, click the down arrow next to Application Builder > Create Application.
.	Select Create Application and click Next.
.	For Name, enter Project Tasks Application. Keep the default value of From Scratch for the Create Application, and click Next >.
.	Enter Home for Page Name and click Add Page.
.	You see the Master Detail page type. Master Detail builds a two page report and form combination. Select Master Detail for Page Type. Select Home(1) for Subordinate to Page, OBE_PROJECTS for Master Table Name, and OBE_TASKS for Detail Table Name. Click Add Page.
.	The Master Detail pages were created, click Next.
.	Accept the default and click Next.
.	Accept the default and click Next.
.	You can define a date format that will be used throughout your application. This date format will be used to alter the NLS_DATE_FORMAT database session setting prior to showing or submitting any page within the application. This format is used by all reports showing dates and is also picked up by form items of type 'Date Picker (use Application Date Format)'. This can be set using: Application Builder > Application Builder Defaults (under Tasks): These are specific to a Workspace, not a developer, and will be used as the default when using the create application wizard. Application Builder > Create Application Wizard > Attributes page Application Builder > Your Application > Shared Components > Edit Globalization Attributes. Select an Application Date Format for your Application and click Next.
.	Select Theme 20 and click Next.
.	Click Create.
.	Your application was created successfully. Click Run Application.
.	Because you chose the default of Application Express Authentication, you need to login to the new application using the same login you use to develop. Enter obe for the User Name and oracle for the Password. Then click Login.
.	Select OBE Projects.
.	A report containing the list of projects is automatically created. Select the Edit ( )icon next to one of the projects to view the Master detail Form.
.	The Master Detail Form is shown. This page allows a user to edit the details of the Project as well as the details of the Tasks associated with the Project. It also allows users to add new tasks to the current project. In the detail region, you see an Add Row button. This button saves any pending changes and then adds another row so that a new task can be added to the project. You should also have a Delete Checked button. This button is used in conjunction with the check boxes to the left of each Task. When you click the Delete Checked button, any tasks that have been "checked" are deleted. If you use this feature, you notice that a deletion confirmation has been built in. This asks you to confirm your deletion before proceeding. Change the name of the project and click Apply Changes.

Although the Master-Detail form is fully functional, the presentation can be improved. In this topic you change the names of the Page Title, Region Title and change the format an item on the Master Report. Additionally, you print the Projects report in the PDF format. Lastly, you change the sequence of items in the Projects and Tasks page using the Drag and Drop feature. Perform the following steps:

.	Select Edit Page 2.
.	Select the Report link under Regions.
.	Click the Edit () icon next to PROJECT_DEADLINE.
.	Under Column Formatting, enter MM/DD/YYYY for the Number/Date Format field and click Apply Changes.
.	Click Apply Changes.
.	Click the Edit Page Attributes () icon for the Page.
.	In the Name section, enter Projects for the Name. In the Display Attributes section, enter Projects for the Title. Then click Apply Changes.
.	Click Run.
.	Notice that the format of the PROJECT_DEADLINE has changed. You notice that the Region title is still set to OBE_Projects. Click Show Edit Links in the Developer links area.
.	Click the Edit () icon next to the Obe Projects Region title.
.	Change the title to Projects and click Apply Changes.
.	Click Cancel to close the Page Region window.
.	Refresh your browser to show the change you just made. Click Hide Edit Links in the Developer Links.
.	Click the Edit Page 2 link in the developer tool bar.
.	Click the Report link next to the Projects region.
.	Click the Print Attributes tab.
.	Select Yes in the Enable Report Printing drop-down menu and select Portrait in the Orientation drop-down menu under Page Attributes. Then click the Page Header tab.
.	Enter Project Report for Page Header and and set the Alignment to center. Click Apply Changes.
.	Notice that now Print appears to the right of the Report link since you have enabled Report Printing. Click Run.
.	Notice the Print link at the bottom of the report. Click the Print link.
.	Click the Open with radio button and click OK to view the report in PDF format. Note: Accept the license agreement. In order to use print services functionality, BI Publisher needs to be running. You see an error if the BI Publisher is not running. If you have not started BI Publisher as mentioned in the Manipulating Database Objects Using Oracle Application Express 3.2 OBE, open a terminal and enter the following command: /u01/app/oracle/product/bipub/oc4j_bi/bin/oc4j -start
.	The Projects report is displayed in the PDF format.
.	Click the Edit Page 2 link in the developer tool bar.
.	Click the > arrow to navigate to the page definition for the Projects and Tasks page (Page 3).
.	The page definition for Page 3 is displayed. To reorder the sequencing of items in this page, click the Items tab.
.	Click the Drag and drop icon in the Items section.
.	The Drag and Drop layout is displayed. You can use this intuitive graphical layout to edit, create, or delete items in this page.
.	In order for the Project Priority item to appear above Project Deadline item in Page 3, drag and drop the P3_PROJECT_ PRIORITY item above the P3_PROJECT_ DEADLINE item and click Next>.
.	Click Apply Changes.
.	Click Run.
.	Note how the Project Priority field now appears above the Project Deadline field. Click the Application <n> link in the developer tool bar.

Creating Lists of Values (LOVs)

Another way to improve the presentation of the application is to create a List of Values. In this topic, you create the following LOVs:

EMPLOYEES	A Dynamic LOV to show the list of Employees. This LOV is then associated with the Assigned To column in the List of Tasks.
STATUSES	A Static LOV to show the list of Statuses. This LOV is then associated with the Status column in the List of Tasks.
PRIORITIES	A Static LOV to show the list of Priorities. This LOV is then associated with the Priority column in the Projects Master area.

Perform the following steps:

Click the 3 - Master Detail page.

In the Shared Components column, under List of Values, click the Create ( ) icon.

For Create List of Values, keep the default value From Scratch, and click Next >.

For Name, enter EMPLOYEES. For Type, change the type to Dynamic. Then click Next >.

For a dynamic LOV, you need to enter the SQL you want to execute when the page is rendered. To find out what the names of your columns are, you can select the item finder () icon.

Click the Tables tab.

Select the OBE_EMPLOYEES table link.

Note the names of the columns. You could select the SQL statement shown and copy this to the clipboard. Exit the Item Finder window.

Change the SQL statement to the following and then click Create List of Values. The first column in a dynamic list of values is the column displayed to the user. The second column is the corresponding value to be stored in the database. They can both be the same value when appropriate.

select FIRST_NAME||', '||LAST_NAME d,
    EMPLOYEE_ID r
from OBE_EMPLOYEES
order by 1

Your LOV was created. Note that the LOV is not listed in the List of Values area because you haven't yet associated it with this page. You want to create 2 more static LOVs. Click the Create ( ) icon again.

For Create List of Values, keep the default value From Scratch, and click Next >.

Enter STATUSES for the Name and make sure Static is selected for the type and click Next >.

For Display Value and Return Value, enter the following:

Display Value	Return Value
Closed	closed
Open	open
On Hold	on-hold

Click Create List of Values.

The STATUSES LOV was created. There is one more LOV to create for the Master portion of the form, Priorities. Click Create.

For Create List of Values, keep the default value From Scratch, and click Next >.

Enter PRIORITIES for the Name and make sure Static is selected for the type and click Next >.

For Display Value and Return Value, enter the following:

Display Value	Return Value
High	1
Medium	2
Low	3

Click Create List of Values.

The PRIORITIES LOV was created. Now you can associate the LOVs with the page. Click the Edit Page 3 () icon.

Click the Show All icon to reveal all Page Components.

In the Regions area, click the Report link.

Click the Edit () icon in front of the ASSIGNED_TO column.

Click the Tabular Form Element section button.

Change the Display As to Select List (named LOV) and click the List of Values section button.

Select EMPLOYEES for the Named LOV, select YES for Display Null and enter - None Assigned - for the Null display value. Then click Apply Changes.

Click the Edit () icon in front of the STATUS column.

The List of Values section button should be already selected. Select STATUSES for the Named LOV, Yes for Display Null and enter - No Status - for the Null display value. Then click the Tabular Form Element section button.

Select Select List (named LOV) for the Display As and click Apply Changes. Click Apply Changes.

Now you can run the page again to see the results. Click Run Page 3 () icon.

Select the dropdown list for Assigned To. You see that the dynamic LOV executed and the list of Employees is shown.

Select the dropdown list for Status. You see that the static LOV values are shown.

Next, you need to assign the the PRIORITIES LOV to the Priority item in the Master area of the page. Click Edit Page 3 .

Under Page Rendering, click the Items () icon.

Select P3_PROJECT_PRIORITY.

Select the Radio link under the Display As field and click the LOV section button.

Select PRIORITIES for the Named LOV, enter 4 for Number of Columns, select Yes for Display Nulls and enter None for the Null display value. Then click Apply Changes.

Click Run.

The Priority field is now a Radiogroup populated with the LOV values. Click Edit Page 3 in the Developer links.

Adding an Item Validation

You don't want a user entering a date prior to today's date. This type of check can be done through an item validation. Perform the following steps:

.	Click the Create () icon in the Validations section for Page 3.
.	Accept the default to create an Item level validation and click Next >.
.	Select the item P3_PROJECT_DEADLINE and click Next >.
.	Select SQL for the Validation Method and click Next >.
.	Because you want to specify the condition when a PROJECT_DEADLINE is incorrect, click SQL Expression and click Next >.
.	Enter P3_PROJECT_DEADLINE as Validation Name and click Next >.
.	Enter TO_DATE(:P3_PROJECT_DEADLINE,'MM/DD/YYYY') >= SYSDATE for the Validation and Date needs to be greater than today for the Error Message and click Next >.
.	Select Request Is Contained within Expression1 for the Condition Type and enter SAVE, CREATE for Expression1. Then click Create.
.	The validation has been created successfully. Click Run.
.	Change the date to something prior to today's date and click Apply Changes.
.	Notice that you receive an item (or field) error message as well as a page notification message. This is because you specified that you wanted both when you created the validation.
.	Click the Application <n> link in the developer tool bar.