Create Wizard-Based Order Processing Module in Oracle Application Express
Part I of IV
In chapter 6 of the book “Create Rapid Web Applications Using Oracle Application Express”, you are guided on how to create a wizard-based order placement module. This post is aimed at revealing how the order process flows. Each order passes through a sequence of steps called Order Wizard. This wizard is based on a Shared Component list created in Chapter 2 section 2.5.2 (c). The steps in the Order Wizard list are:
- Identify Customer
- Create Customer (if one doesn’t exist in the database)
- Select Items
- Show Order Summary
Order placement process is invoked through the Orders tab created in chapter 2, section 2.5.1. To remind you, tabs list available options in an Oracle APEX application and are an effective way to navigate between pages of an application. While creating the Orders tab, you set the value of Tab Current for Page to 4. This is the ID of the page to which the user navigates to when selecting this tab. In our scenario, we instructed Oracle APEX to call Page 4 - Orders Master when a user clicks this tab. The Orders Master page provides a list of all placed orders. A user can create and modify orders through this page. Rather than starting with this initial page, we are going to take the Order Wizard route to explain this chapter where a new order is generated first and modified later on.
Section 6.6 – Page 11
In section 6.6 and 6.8, two pages (Page 11 and Page 18) are created to either select an existing customer or create a new one for the order.
Section 6.6.1 – Create a blank page (Page 11). Select a page template with one level tab and a left sidebar to display application menu and to hold Order Wizard list respectively.
Section 6.6.2 – Create an HTML region named Identify Customer to hold the two items - Radio buttons and a Popup List of Values as shown in the following figure. The radio buttons offer two options to the application user to choose from. These are (a) Existing Customer and (b) New Customer. Upon selecting the Existing Customer option, the popup list item becomes active and allows user to select a customer from the provided list. If the customer is new, control of wizard transfers to Create Customer – Page 18, where user creates record for the new customer before placing order.
Section 6.6.3 – Create a List region named Order Progress (shown under) to hold Order Wizard list to the left side of the page. This region is created on every page where we need to show the Order Wizard list.
Use the Display Point attribute of the region to position it at a desirable location on the web page. This region’s Display Point is set to Page Template Region Position 2. There are 13 display points available for a region which are:
The following illustration shows various region display positions in a page template.
Section 6.6.4 – Create a button named CANCEL to close this page without executing validation. Buttons can be placed in predefined region template positions or among items in a form. When you create a button in a region position, the positions you define will appear in a select list. Use the following positions for the placement of buttons in a region:
#EDIT#, #CLOSE#, #CREATE#, #CREATE2#, #EXPAND#, #HELP#, #DELETE#, #COPY#, #NEXT#, and #PREVIOUS#
You should use different available options of an attribute to check the behaviour. It's necessary if you want to learn how things work in Oracle APEX.
Section 6.6.5 – Create a button named NEXT to step forward in the wizard based on the radio button selection. Both CANCEL and NEXT buttons will be displayed in Identify Customer region (6.6.2).
Section 6.6.6 – Create the following two items on this page:
Using the radio group item in Oracle APEX you can offer multiple choices to the end user to select a single option. You can enter either a SQL query or a static definition for a list of values. The following are some examples for populating radio group list:
Example 1: List of values based on SQL query
SELECT Customer_Name, Customer_ID FROM Customers ORDER by 1
Example 2: List of values with same display and return value
SELECT Customer_Name a, Customer_Name z FROM Customers ORDER BY 1
When selecting the same column for both the display and return values use a column alias.
Example 3: Static list examples
STATIC:Cow,Dog,Cat,Lion (will be sorted alphabetically)
STATIC2:10,15,20,25,50,100,200,500,1000,10000 (sorted in order of creation)
STATIC:Yes;Y,No;N (show Yes / No, return Y and N)
In our scenario, we used the last approach by creating a static list along with return values. STATIC2:Existing customer;EXISTING,New customer;NEW
In point 6 of this section, you set Source Value to EXISTING. The source of an item is based on a user preference. If the item source is null the default value is used that you set in point 7.
The Label Template, Required with help, references an asterisk image to indicate to the user that the field is required.
If Value Required is set to Yes and the page item is visible, Application Express will automatically perform a NOT NULL validation when the page is submitted. By defining a message called APEX.PAGE_ITEM_IS_REQUIRED in Shared Components/Text Messages, the predefined error text can be replaced by an application specific error text. The display location of the message is defined by the application level setting Default error display location.
We set Display Null Value to No which means that the list of values used for this item should not display a NULL value.
Section 6.6.7 – Create a PL/SQL page process under Page Rendering section to create and truncate a collection named ORDER. See Display Current Order in the post “Using HTML in PL/SQL” for details on APEX_COLLECTION.
Section 6.6.8 – Create a Dynamic Action under Page Rendering section to hide/show the LOV (P11_CUSTOMER_ID), created in step 6.6.6, based on the selection of P11_CUSTOMER_OPTIONS radio item.
Points 4 and 5 say: if the default Existing Customer option is selected from the radio group, SHOW the LOV item P11_CUSTOMER_ID. In the final step of the wizard, you select P11_CUSTOMER_ID to indicate that this is the item which will be shown or hidden depending on the selected option. You can select the name of 1 or more page items at this stage that will be affected by the action. The reverse FALSE dynamic action is created automatically by Oracle APEX which triggers when the option New Customer is selected.
Section 6.6.9 – Create an item level validation rule for P11_CUSTOMER_ID LOV to check that the item value is not null if an existing customer is to be selected i.e. the P11_CUSTOMER_OPTIONS is set to EXISTING.
Section 6.6.10 – Create three branches to move ahead or step back accordingly when the NEXT or CANCEL buttons are clicked. In the first branch, enter 12 in page value to call this page only when an existing customer is selected and the NEXT button is clicked. In the second branch, set page value to 18 to first create a new customer record and then move ahead in the sequence to place order. In the final branch, set the page value to 4 to move back to Order Master page when the CANCEL button is clicked.
Section 6.8 – Page 18
This exercise creates record for a customer which doesn’t exist in the database. In chapter 4 of this book, you’ve already done this exercise. So, to preserve time and to learn how existing pages are re-used in Oracle APEX, you are taught how a new page is created as a copy of an existing one in this section.
Section 6.8.1 – Create a new page based on Page 7 (Customer Details). Set the new page number to 18. Select a page template with one level tab to display application menu along with two sidebars to hold Order Wizard list to the left side and an HTML region to the right to display some static help text about the page.
Section 6.8.2 – Set template and display point for Customer Details region. Use the flashlight icon to see the page layout and select a position.
Section 6.8.3 – Delete the unwanted region Orders for this customer, which displays existing orders for the selected customer. Because this information is not available for new customers, therefore, we removed it.
Section 6.8.4 – Create a List region named Order Progress to hold Order Wizard list similar to 6.6.3.
Section 6.8.5 – Move back to Page 4 – Order Master when CANCEL button is clicked.
Section 6.8.6 – Remove the customer deletion process and the corresponding DELETE button because you only want to create a new customer record.
Section 6.8.7 – Create two new buttons - PREVIOUS and INSERT. The PREVIOUS button would take you back to Page 11 when you click the CANCEL button while clicking the INSERT button (labeled Next >) first inserts the new customer’s record into the database using SQL Insert action in Database Action under Action When Button Clicked, and then passes the flow to the next step to select order items.
Section 6.8.8 – Modify the two default processes created by Oracle APEX under Page Processing section. The first one, Process Row of DEMO_CUSTOMERS is an automatic row processing (DML) process which after performing a SQL INSERT statement, takes the first (or only) column of the primary key and returns it into the selected item i.e. P18_CUSTOMER_ID.
The process, Row of DEMO_CUSTOMERS offers three functions. First, you are not required to provide any SQL coding. Second, Oracle Application Express performs DML processing for you. Third, this process automatically performs lost update detection. Lost update detection ensures data integrity in applications where data can be accessed concurrently.
The Reset Page process clears cache for all items on the page when the PREVIOUS button is clicked.
The NEVER Condition Type is used to temporarily prevent controls or components from being rendered on a page, or to prevent processes, computations and validations from running.
Section 6.8.9 – A branch is an instruction to go to a specific page, procedure, or URL. Here, you’re instructing to go to Page 12 – Enter New Order when the NEXT button is clicked.