X

The Oracle APEX blog is your source for APEX news, technical tips and strategic direction

[Forms to APEX] Customizing the APEX Application - Orders Form

Monica Godoy
Principal Product Manager

In the last blog post in this series, I focused on customizing the Customers Form. I tracked the objects in Migration Project, reviewed the business logic and created additional enhancements for this form.  It's the moment to focus on the Orders Form which I created four different options:

  • Option 1
    • Master Detail - Stacked
    • Item Form
  • Option 2
    • Master Detail - Side by Side
    • Item Form
  • Option 3
    • Master Detail - Drill Down
    • Item Form
  • Option 4
    • Faceted Search + Classic Report
    • Form Page + Collections

Option 1, 2 and 3 allows the creation of order information first and then end-users can add items to that order. However, creating incomplete with any related items can be risky for some companies. This a good topic to see how APEX can temporarily save the data until the end-user is finally ready to submit the Order.

Having said, in this blog post, I'm going to focus on option 4 by doing:

  • Creating the Needed List of Values.
  • Creating the Orders Form.
    • Creating Validations.
    • Creating Process.
  • Creating the Items Form.
    • Creating Process
  • Enhancing the Faceted Search page.
  • Put together the new forms with the Faceted Seach page.

I'm not going to create the Items Form using the wizard, I'm going to temporarily manage the items using the APEX_COLLECTION API.  You can use collections to store rows and columns currently in session state so they can be accessed, manipulated, or processed during a user's specific session. Essentially, I'm going to use the following functions/procedures to manage the items:

  • COLLECTION_EXISTS: Use this function to determine if a collection exists. A TRUE is returned if the specified collection exists for the current user in the current session for the current Application ID, otherwise FALSE is returned.
  • CREATE_COLLECTION: Use this procedure to create an empty collection that does not already exist. If a collection exists with the same name for the current user in the same session for the current Application ID, an application error is raised.
  • ADD_MEMBER: Use this procedure to add a new member to an existing collection. An error is raised if the specified collection does not exist for the current user in the same session for the current Application ID. Gaps are not used when adding a new member, so an existing collection with members of sequence IDs (1,2,5,8) adds the new member with a sequence ID of 9.
  • DELETE_MEMBER: Use this procedure to delete a specified member from a given named collection. If the named collection does not exist for the same user in the current session for the current Application ID, an application error is raised.
  • DELETE_COLLECTION: Use this procedure to delete a named collection. All members that belong to the collection are removed and the named collection is dropped. If the named collection does not exist for the same user in the current session for the current Application ID, an application error is raised.

Creating the Needed List of Values

There are four lists of values that can be useful in Faceted Search Page and Orders Page, following the details:

  1. SALES_REP_LOV: A dynamic list of values based on the S_EMP table. This LOV is already created and customized.
  2. S_CUSTOMER.NAME: A dynamic list of values based on the S_CUSTOMER table.

3. S_ORD.PAYMENT_TYPE: A static list of values using the following values.

4. S_PRODUCT.NAME: A dynamic list of values based on the S_PRODUCT table.

Creating the Orders Form

Following the steps to create the Orders Form using the wizard:

  1. Click App Builder and click on your application.
  2. Click Create Page.
  3. Select Form and click Next.
  4. Select Form and click Next.
    For Page Number, as default.
    For Name, enter Order - Details.
    For Page Mode, select Normal.
    For Branch Here on Submit, select Orders Faceted Search Page.
    For Cancel and Go To Page, select Orders Faceted Search Page.
    For Breadcrumb, select Breadcrumb.
  5. Click Next.
  6. For navigation preference, select Do not associate this page with a navigation menu entry.
  7. For Data Source, enter local.
    For Source Type, select Table.
    For Table/View Owner, select the proper owner.
    For Table/View Name, select S_ORD.
  8. Click Next.
  9. Select Columns to be displayed in the form, as default.
    For Primary Key Type, Select Primary Key Column(s)
    Primary Key Column, select ID(Number).
  10. Click Create.

Using this form, end-users can create, update or delete an order. Before creating/updating an order it's important to validate the entered information. Let's track the triggers in the Migration Project and add the required validations.

Creating Validations

WHEN-VALIDATE-RECORD

To create this validation in the Orders Form:

  1. In the first pane, go to the Processing tab.
  2. Right-click Validations and select Create Validation.
  3. For the Name of the validation, enter Validate Order Dates.
    For Type, select SQL Expression.
    For SQL Expression, enter :PX_DATE_SHIPPED > :PX_DATE_ORDERED.
    For Error Message, enter Ship date is before order date!
    For Associated Item, enter PX_DATE_SHIPPED.
  4. Click Save.

WHEN-RADIO-CHANGED

For this validation, you can create a procedure to validate the credit rating of the customer (preferred) or include the code in the validation.
To call a validation procedure, select the validation type: PL/SQL Function (returning Error Text) and for PL/SQL Function Body Returning Error Text enter:

Declare
    l_message varchar2(200);
Begin
    PRD_VALIDATE_PAYMENT(P_CUSTOMER_ID  => :PX_CUSTOMER_ID,
                         P_PAYMENT_TYPE => :PX_PAYMENT_TYPE,
                         P_MESSAGE      => l_message);
    return l_message;                         
End;


Creating Procedures

Once the order is ready to be created, the items that were created temporarily in the APEX_COLLECTION need to be inserted in the real table S_ITEM. Let's see how to create a process in the form:

  1. In the first pane, go to the Processing tab.

  2. Right-click Processes and select Create Process.
  3. For Name, enter Create Items.
    For PL/SQL Code, enter PRD_CREATE_ITEM  (P_ORD_ID => :PX_ID);.
  4. Since you're going to need the Order ID to create the items, make sure this process is created after the Process form Order (This process was created automatically by the wizard).
  5. Click Save.

Following the PRD_CREATE_ITEM procedure which inserts the rows in the table, receiving as parameter the Order ID. 

create or replace procedure PRD_CREATE_ITEM  (P_ORD_ID           IN NUMBER)
is
begin
    If APEX_COLLECTION.COLLECTION_EXISTS (p_collection_name => 'ITEMS') then

        INSERT INTO S_ITEM 
        (Ord_id, Item_id, Product_id, Price, Quantity, Quantity_Shipped)
        SELECT P_ORD_ID, n001 Item, n002 Product, n003 Price, n004 Quantity, 
               n005 Quantity_Shipped
           FROM APEX_COLLECTIONS
         WHERE COLLECTION_NAME = 'ITEMS';
         
         APEX_COLLECTION.DELETE_COLLECTION(p_collection_name => 'ITEMS');

    End if;

end PRD_CREATE_ITEM;

 

Creating the Items Form

Following the steps to create a simple page. Please remember that this page will be created from scratch, not using the help of the wizard.

  1. Click App Builder and click your application.
  2. Click Create Page.
  3. Select Blank Page and click Next.
  4. For Page Number, as default.
    For Name, enter Item - Details.
    For Page Mode, select Modal Dialog.
    For Breadcrumb, select Breadcrumb.
  5. Click Next.
  6. For navigation preference, select Do not associate this page with a navigation menu entry.
  7. Click Finish.

Once the page is created, let's create the Region which contents the items:

Create a Region

  1. Find the Gallery Region at the center bottom of the page.
  2. Drag a Static Content region and drop it in the Content Body section.
  3. For Region Name, enter Items.
  4. Apply the following Template options and click Save.

Create Items

  1. Find the Items Gallery at the center bottom of the page.
  2. Drag five items and drop them in the Item Region. Ensure the following settings for each item and click Save.
Name Label Type List Of Values
PX_ITEM_ID Item Id Number Field  
PX_PRODUCT_ID Product Id Popup LOV S_PRODUCT.NAME
PX_PRICE Price Number Field  
PX_QUANTITY Quantity  Number Field  
PX_QUANTITY_SHIPPED Quantity Shipped Number Field  
PX_SEQ_ID   Hidden  


PX_SEQ_ID is going to be used to manage the items in the collection when the end-user needs to edit or delete an item.

Create Buttons

  1. Find the Buttons Gallery at the bottom center of the page.
  2. Drag three buttons and drop them in the Item Region. Following the settings for each button:
Name Label Action Server-side Condition
DELETE Delete Submit Page PX_SEQ_ID is NOT NULL
SAVE Save Submit Page PX_SEQ_ID is NOT NULL
CREATE Create Submit Page PX_SEQ_ID is NULL


At the end of these steps, the Items Page will look like this: one region containing five visible items and with three buttons:

Creating Validations

There are six required columns in the S_ITEM table, which are: ID, ORD_ID, ITEM_ID, PRODUCT_ID, PRICE, and QUANTITY. So, I need to make sure that end-users entered the data for the columns they can control. An easy way to validate that is to set two attributes for each item, following these steps:

  1. In the first pane, find the Items Region and expand the items.
  2. While holding down the Ctrl key, click the items: PX_ITEM_ID, PX_PRODUCT_ID, PX_PRICE, and PX_QUANTITY.
  3. Find the Appearance group and for Template, set them to Required - Floating.
  4. Find the Validation group and for Value Required, set them to On.
  5. Click Save.

Creating Process

The last steps in this form are creating the PL/SQL procedures to manage temporarily the items in the collection, and then just create a process on the Items Form to call the corresponding procedure depending on the button that the end-user has clicked. 

             

 

Enhancing the Faceted Search Page

Remember that the Faceted Search Page was created previously in Creating the APEX Application and the wizard created automatically five facets: 

  • Sales Representative
  • Customer
  • Payment Type
  • Order Filled
  • Total

Since order filled is not a key facet search, let's get rid of it by following the steps:

  1. Click App Builder and click your application.
  2. Go to the Faceted Search Page.
  3. In the first pane, find the Search Region.
  4. Right-click on Order Filled and select Delete.
  5. Click Save.

In order to manage the past orders, let's create a link to the new Orders Form. To do so follow these steps:

  1. In the first pane, find the Ord Region.
  2. Expand the columns.
  3. Click ID Column and for type, select Link.
  4. Find the Link group and click the target attribute. Add the following:
    Page - enter the page number or page alias to navigate to. 
    Set Items: Name - enter or select page items (PX_ID), application items (LAST_PAGE) or interactive report filters (IR_EMPNO) to be set into session state.
    Set Items: Value - enter or select a report column (#ID#), enter an application or page item (&P2_EMPNO.), or enter a static value (2).
    Clear Cache - enter a comma-delimited list of page numbers (2,3), items (PX_EMPNO,LAST_PAGE), and report settings to be cleared. To reset pagination for a report enter RP. To clear or reset interactive report settings enter CIR or RIR.
  5. Click Save.

And let's create a button to create a new Order, following the steps:

  1. Find the Buttons Gallery at the center bottom of the page.
  2. Drag one hot button and drop it in the Ord Region. 
  3. For name, enter: CREATE.
    For label, enter: Create.
    For Action, enter: Redirect to Page in this Application.
  4. Click Target.
    For Page, select the Orders Page.
    For Clear Cache, enter the number of the Orders Page.
  5. Click Save.

In this blog post, I showed you that you can create pages from scratch on your own, not only following the wizard. You can also customize your page, add validations and processes as you need.
In the next and last blog post, I will show how to organize the navigation menu, define the authentication scheme and enhance the UI of the application.

Note: Please remember that the value "X" in "PX_ITEM" depends on the APEX page you're working on.

Previous                                                                                               Next