Thursday, 14 June 2012

Web Development Using Wizard Part-4

Create Wizard-Based Order Processing Module in Oracle Application Express

Part IV of IV

Section 6.2 – Page 4 and 29

While creating a new page, Oracle APEX offers an option to create Master Detail Form. A master detail form reflects a one-to-many relationship between two tables in a database. Typically, a master detail form displays a master row and multiple detail rows within a single HTML form. With this form, users can insert, update, and delete values from two tables or views. When running a master detail form, data is rendered as a report using various form elements.

In section 6.2 you create a Master Detail Form to show records from two tables – Orders and Order Items.

Section 6.3 – Page 4

Section 6.3.1 – During the Master Detail form creation process (section 6.2), you specify page numbers for Master and Detail pages. Oracle APEX creates these two pages (in this scenario - Page 4 and Page 29) with some default functionalities. In order to provide some more professional touch to your application, you replace some of these default functionalities with that of yours like section 6.3.1 where a default region, Orders, is deleted and replaced with a new region in 6.3.2 to fetch data from multiple tables in an Oracle APEX Master Detail form and to provide interactive functionality.

Section 6.3.2 – In this section an Interactive Report is created based on a SQL SELECT statement which fetches data from three tables i.e. Orders, Customers, and Users. Interactive reports enable end users to customize reports. Users can alter the layout of report data by choosing the columns they are interested in, applying filters, highlighting, and sorting. They can also define breaks, aggregations, different type of charts, group bys, and add their own computations.

Users can also set up a subscription so that an HTML version of the report will be emailed to them at designated interval. Users can create multiple variations of a report and save them as named reports, for either public or private viewing.

On Order Master page, an attribute called Link Column is set to Link to Custom Target which is used to link to a specific page. You set this value to link to Page 29 - Order Details. Item 1 is set to page item P29_ORDER_ID with value #ORDER_ID# from Orders table to set session state for the item specified. 

Section 6.3.3 – This section demonstrates how to remove columns from an interactive report and how to sort the columns.

Section 6.3.4 – You're guided how to create an Alternative Report in Oracle APEX. Alternative report enables developers to create multiple report layouts. Only developers can save, rename, or delete an Alternative Report. Here, you create different views of the alternative report such as; Report, Chart and Group By and learn the following techniques:

  • How to save an existing Oracle APEX Interactive report as an Alternative report under a different name.
  • How to create break reports in Oracle APEX. You apply control breaks to display report by month.
  • How to use Aggregate and corresponding functions (Sum, Average, Maximum, Minimum etc.) in Oracle APEX. Aggregate enables you to perform mathematical computations against a column. Aggregates are displayed after each control break and at the end of the report within the column in which they are defined. In this exercise, you apply Average function to evaluate average orders in each month.
  • How to highlight rows having specific values.
  • Conditional highlighting of column values.
  • Sort report on a specified column.
  • How to add chart to Oracle APEX Interactive reports.
  • How to group columns. Group By enables users to group the result set by one or more columns and perform mathematical computations against the columns.           
Once you define a report, you can switch between the report, chart, and group by views using the View Icons on the Search bar.

Report View:

Chart View:

Group By View:

Section 6.3.5 – Similar to the previous section, this one creates the three views of a Public Report. In Oracle APEX, a public report is a type of report that can be saved, renamed, or deleted by the end user who created it. Other users can view and save the layout under a different name.

Section 6.3.6 – During Master Detail from creation, Oracle APEX creates a button, named CREATE, to add new records. In this section, you modify some attributes of this default button by providing a meaningful name, label, position, and action when clicked.

Section 6.3.7 – Deleted a computation that is not required in our process.

Section 6.3.8 – Create a branch to move to Page 11 when the button Enter New Order is clicked to start order wizard.

Section 6.4 – Page 29
Page 29 – Order Details is the second page of the master detail form that is created by Oracle APEX page creation wizard. This form is used to modify or delete an order and is called when you click the Edit icon in the Order Master page.

Section 6.4.1 – Section 6.4 is devoted to modify Order Details page (Page 29). Initially, you modify the attributes of the page itself. Each Oracle APEX page has a security section where you apply an Authorization Scheme. Authorization schemes are defined at the application level and can be applied to many elements within the application. In this section, you apply Verify Order Ownership, a scheme created under Shared Components in chapter 2, section 2.5.6 (c). The purpose of this authorization scheme is to allow order manipulation only to the user who created the order.

Section 6.4.2 – You learn how to display the value of a page item as the page title. In this case, you set the page title to Order #&P29_ORDER_ID where Order# is a string and &P29_ORDER_ID is a substitution string referencing a page item holding the Order ID.

Besides, you create a custom SQL statement to show desired columns in specific format. An important segment in the SQL statement is worth mentioning and that is the APEX_UTIL package. This package provides utilities you can use when programming in the Oracle Application Express environment. You can use the APEX_UTIL package to get and set session state, get files, check authorizations for users, reset different states for users, get and purge cache information and also to get and set preferences for users. A function from this package, GET_BLOB_FILE_SRC, is used to more specifically format the display of the product images with height, width, and alt tags.

Section 6.4.3 – Modified the Add Row button by setting its Action value to Submit Page when it is clicked.

Section 6.4.4 – Created a new Display Only item named P29_CUSTOMER_INFO on the page and associated a SQL query with it to show customer information.

To prevent Cross-Site Scripting (XSS) attacks Escape Special Characters attribute should always be set to Yes! Only if you want that HTML tags which are stored in the page item or in the entries of a list of value are actually rendered, you should set this flag to No.

An onchange="htmldb_item_change(this)" event is declared in HTML Form Element Attribute for P29_ORDER_TIMESTAMP  and P29_ORDER_TOTAL. The onChange specifies script code to run when the data in the input field changes. The onChange happens whenever anything changes the value of the field, not just when the user enters a value. The this object, is a reference to the two page items. Since the two items are marked as Display Only, that makes the values unchangeable on the form, therefore, the above event could be ignored. Try by removing the event from both items and observe any change in the application behavior.

Section 6.4.5 – After processing an order, a user might want to add few more items to it at a later stage. To provide this functionality create a Data Manipulation process under Page Processing section to allow adding more items to the order and associate it with Apply Changes button on the page.

This ends my effort to expose facts in Chapter 6 of my book “Create Rapid Web Applications Using Oracle Application Express”. I hope that you have grabbed the knowledge and would apply the techniques presented in this multi-part post to your own projects. Please do provide your valuable comments and let me know of any overlooked area.

No comments:

Post a Comment