In the last post in this series, I created the APEX application and showed you various options for the UI in the Customers and Orders Forms.
In this blog post, I'm going to focus on over the logic from the Customers Form and then I’ll make some additional enhancements to the application.
Let’s start by reviewing the following components in the Customers Form to see what actually needs to be migrated (applicable) vs. what was specific to Oracle Forms (not applicable):
After you review the objects, remember to set them as Complete.
There are five blocks but only two are database blocks.
S_CUSTOMER1 and S_CUSTOMER are using the S_CUSTOMER table. The first block is query-only so it's not applicable. On the other hand, the second block allows us to insert, update and delete rows and definitely it's applicable. The region form was created by the wizard in the APEX Application.
CONTROL, NAV_CONTROL and NAVIGATOR blocks were used to control the tree region and prompt the title of the columns, so they aren't applicable to the APEX Form.
There are 30 items listed distributed by the blocks:
The only applicable block is S_CUSTOMER, which has 12 items corresponding to the columns in the S_CUSTOMER table. Since the form was created by the wizard, those items were included in the form.
There are 23 triggers in total but only two triggers associated with the S_CUSTOMER block.
One of these triggers is POST-QUERY, this trigger logic is not applicable since the APEX form automatically creates the Form - Initialization Process. This process initializes form region items. Initialization can either be fetching data from the region source, using the primary key value(s) or simple initialization of the form region items.
The other trigger is WHEN-MOUSE-DOUBLECLICK, used to execute the procedure EDIT_TEXTITEM. This procedure invokes a pop-up window editor for the COMMENTS column. This procedure can be omitted in the APEX Form.
The only list of values created in the original form is SALES_REP_LOV:
Since the form was created by the wizard, this list of values was also created but the name is different and the query is incorrect. So let's fix that in the APEX Application:
Keep in mind that you can define additional display columns for item types that support multiple display columns.
There are two alert messages: DELETE_ALERT and CONFIRM_REVERT.
DELETE_ALERT message was automatically created when you create the form using the wizard, but the message is quite different. Follow these steps to create a customized message:
The message is created and now you need to update the shortcut in the APEX Form.
As to CONFIRM_REVERT, every APEX page has the Warn on Unsaved Changes property that can warn users when they try to navigate away from the page that has unsaved changes. Just make sure to enable this property.
There are seven program units, some to control the tree region and some others to control the relation between the S_CUSTOMER1 and S_CUSTOMER blocks. None of them are applicable to the APEX Form.
The following objects were applicable and included in the APEX Form:
Now that the APEX Form contains the business logic from the original form, it's a good time to enhance the application by making the following changes:
The table S_CUSTOMER has a column named CREDIT_RATING with a check constraint. The column can only contain the following values: EXCELLENT, GOOD, POOR.
It's important to create a list of values to prevent end-users from entering invalid options.
To create the list of values:
To learn more about creating a List of Values, please read Beginners Guide to List of Values in Oracle APEX by David Peake.
When you create a form using the wizard, it automatically creates an item for each column in the table. Depending on the column type, it creates a Text Field, Textarea, Date Picker or Number Field item.
Once the page is created, you can go to the page and make the changes you need. Select a different type of element, edit the label and more.
Plus, you can organize items by dragging and drop them.
To use the list of values previously created,
You can define which facets are going to be used to filter the data, some are defined automatically, but you can create or delete them as needed. Also, you can drag and drop facets to change their display order.
To learn more about Faceted Search, please read APEX 19.2 - Faceted Search by Carsten Czarski.
Define how end-users are going to read the report. Explore all the options by clicking the Actions button when you run the Interactive Report.
Keep in mind that you can run the Report as a:
To define the primary report:
To save the primary report:
In the following video, you can see in detail all the customizations made in the Customers Form:
In this blog post, I showed you how to migrate the business logic from the original form to the APEX Application, but also how to enhance it by adding some customizations to the options created by the wizard.
Note: Please remember that the value "X" in "PX_ITEM" depends on the APEX page you're working on.