2020.08.17 Oracle Application Express 20.1.0.00.13 Copyright © 1999, 2020

1. Intro, content

I. BASICS Top.....2.1 Apexws_cloud.....2.2 App. builder.....2.3 Page
.....2.5 Environm .....2.7 URL

II. APP .....2.9 Sales web app - DDL, add data

III. SHARED COMPOMENTS .....3.2 SHARED C. (lists) 3.2.1 Menu......3.2.2 Rep.List.....3.2.3 Ord.WizList
.....3.3 Top. right Navig.....3.4 LOVs....3.5 IMGs

IV. APP PAGES .....4. Home p......4.4 Buttons.....4.5 PgStyles.....5. Cust.....6. Product.....7. Order
.....8. Graph. & Mobile.....9. Adv. Rep
.....10. Authoriz.....11. Search--Style--Calendar.....12. Deploy

This txt on my Github : https://github.com/slavkoss/fwphp/blob/master/fwphp/glomodul/mkd/01/001_db/oracle_apex.txt (in Markdown format) is based on https://drive.google.com/drive/my-drive 2020_Riaz_Oracle_APEX_20_For_Beginner.pdf : , URL to Download Book Code (no DDL) https://tinyurl.com/oracleapex20

MD to HTML converters on inet

Own Workspace to execute exercises online on Ora. servers (free)

Top.....Apexws_cloud.....App. builder.....Page.....Environm.....URL.....Sales.....SHARED C. ......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs.....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy

Sales APEX app in Oracle Cloud

1. Cloud Dev APEX : https://apex.oracle.com/pls/apex/f?p=4500:1000 is opened (APEX add :722441254522763:::::)
   or dev URL https://apex.oracle.com/pls/apex/f?p=4550
   To sign in Cloud Dev APEX : https://apex.oracle.com/en/ -> "Sign in" button which opens Login page
   After "p=" is app alias 4500 (appID) means eg sales-web-app4, possys is workspace. 1000 is pageID.

2. To leave APEX dev. envir, click your name (appearing at top-right) and select Sign Out.

3. Cloud run Cloud APEX Sales app : https://apex.oracle.com/pls/apex/possys/r/sales-web-app4/home (APEX add ?session=710994255959550)

4. https://apex.oracle.com/en/ Oracle APEX site

5. click "Get Started for Free" button

6. click "Request a Free Workspace" button
   On the Identification wizard screen, enter : Request a Workspace
   First Name Slavko Last Name Srakocic
   Email slavkoss22@gmail.com
   Workspace possys Country Croatia Usage Personal
   Confirm. email : Workspace Request Approved
   Environment: https://apex.oracle.com/pls/apex/

   Click "Create Workspace" button to complete the approval process and set your psw.
   Workspace Successfully Created Your request for an account has been approved.
   Button "Continue to Sign in screen" psw=MYLONGPSW opens page :

   Cloud Dev APEX : https://apex.oracle.com/pls/apex/f?p=4500:1000 (+ :722441254522763:::::) is opened

App builder

1. https://apex.oracle.com/pls/apex/ (+ f?p=4550:1:117485610537637:::::)

2. button "Sign in" opens same URL, page "App Builder"

   Click on APEX opens page "APEX" contans "Top menus" of page "App Builder" as icons, instead "Dashboard" is "Team development".

2. Oracle APEX Concepts

2.1 Exercises online on Ora. servers (free) Own APEX Workspace on Oracle cloud

2.2-2.8

2.9 Start Building Sales app. 80858

Top.....Apexws_cloud.....App. builder.....Page.....Environm.....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy

https://apex.oracle.com/pls/apex/possys/r/sales-web-app4/home (+ eg ?session=706440515653484)

1. https://apex.oracle.com/pls/apex/ + eg f?p=4550
2. Sign In form : Username (your e-mail address) and Password (you provided in Section 2.3.1)
3. Click App Builder icon ->click some app -> "App home page" appears. Use App home page to run, edit, import, export, copy, or delete apps
4. On App Builder page, click "Create" icon
5. Select first "New App" option - Create an App page is displayed
6. In Appearance section, click "Set Appearance" icon
7. Enter Sales Web Application in Name box (A)
8. On Appearance page, in Navigation section, select Mega Menu option (or Shared Components | Edit App Definition | User Interface | Navigation Menu). Click Choose New Icon button (E), and select an icon (F) and its color (G). By default, App Builder process creates Home page along with a couple of pages (Login and Global). Since you will create other pages for your app in subsequent chapters, you do not need to add any page at the moment.
9. In Features section, click Check All link (K). In Settings section, accept all default values. Here, Application ID = 80858 (K) .
   In default APEX Accounts authentication scheme (M) users are managed and maintained in Oracle APEX repository.
10. Click Create App button (N), app will be created with default pages, including Page 1 Home, Page 9999 Login Page and Page 0 Global Page (A).
    Using buttons (View Icons and View Report - B), you can get different views of this interface.
    To modify properties of your app (for example, app name or menu position), click Edit Application Properties button (D).
    See Delete this App link (E). See Copy this App link (F) - makes an exact copy of app under different ID.
11. Click Run App (H) - type same username (your e-mail ID) and password you entered earlier to access development environment.
    Home page bottom is Developer Toolbar :
    1. Application option (D) takes you to the App Builder page, where you can select a different page to work on.
    2. Edit Page option (E) in this toolbar takes you to Page Designer to edit the current page
    3. Session option brings up a page (see Figure 2-6) that displays current state of app so that you can verify its behavior
    4. Sign Out option (F top page right) exits app.
    5. Administration menu option (G) - Administration page that lists all the features you selected in step 9. Features section.

2.10 Create DB Objects interactively (no SQL) for Sales app

Five tables : DEMO_STATES, DEMO_CUSTOMERS, DEMO_PRODUCT_INFO, DEMO_ORDER, and DEMO_ORDER_ITEMS.
in folder J:\awww\www\fwphp\glomodul\mkd\01\001_db

app 4500, pg 3002 is SQL workshop : https://apex.oracle.com/pls/apex/f?p=4500:3002

app 4500, pg 1001 is SQL workshop Object browser : https://apex.oracle.com/pls/apex/f?p=4500:1001

See at end this txt DB Objects

2.11 Add Data to tbl, see oracleapex....csv files

1. From main APEX menu, select SQL Workshop | Utilities | Data Workshop
2. On Get Started page, click Load Data button
3. On next screen, click Choose File button. In Open dialog box, select DEMO_CUSTOMERS csv
4. Load Data page will appear on your screen. Select options on this screen. By selecting Existing Table option you are informing that you want to upload the csv file data to eg existing DEMO_CUSTOMERS table. Once you select the database table, APEX will automatically map columns. Click Load Data button on this page. A message " Data in table DEMO_CUSTOMERS appended with 7 new rows! " should appear on your screen. Click the View Table button to browse data.

See at end this txt DB Objects data

Next

In chapter 3, we create building blocks (shared components) of app.

Shared Components wizards allow us to define components we can re-use in pages (better name "modules" like Ora. Forms) throughout our app. (common, global on app level).

Work in detail on Home page in Chapter 4 to convert it into dashboard. Chapter 5 - Customers. Chapter 6 - Products. Chapter 7 -Orders.

3. Create (shared, common, appglobal) App Components

You can see a list of all Shared Components utilized on some app page by accessing its "Shared Components" tab in the Page Designer.

See 3.1 About Shared Components

3.2 Create Lists - collections of links rendered using template

Top.....Apexws_cloud.....App. builder.....Page.....Environm.....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy

Shared Components you will use (create) for Sales Web app

1. Lists
2. List of Values (LOV)
3. app Logo

To access Shared Components page :

1. Select Database apps from the App Builder menu.
2. Click Edit icon (A) under Sales Web app.
3. On the next screen, use either of the two Shared Components icons (pyramid of 3).

For each list entry, you specify display text, a target URL, and other properties to control when and how the list entry displays. You control the display of the list and the appearance of all list entries by linking the list to a template.

List 1 Main top menu (Desktop Navigation Menu List)

APEX App Builder wizard created default Home and Administration mnuitems.

Main top menu is hierarchical list of navigation, which appears either as a responsive side bar or at top of window. Based on available space, navigation bar either displays a full menu or collapses to a narrow icon bar.

Modify Desktop Navigation Menu List list to add app submenu level1 entries Setup, Orders, Reports :

1. In Shared Components -> Navigation section -> click "Navigation Menu" option

2. On Lists page, click "Desktop Navigation Menu" option, which carries two entries Home and Administration

3. On List Details page, click Create Entry button (A) to create new menu item named Setup. This menu entry will have sub-entries that will allow you to access Products and Customers modules.

   Fill in values for Setup menu submenu level1 entry :

   1. Do not select anything in the first attribute (Parent List Entry), because initially you will create level one entries
   2. Click pop-up LOV icon representing Image/Class attribute.
      1. From Show list, select Font APEX, and from Category, select Web app
      2. Click Go button to refresh the view
      3. Scroll down to the middle of the icons list and select fa-database icon. This image will be displayed for Setup submenu at run time. Note that you can select any image from list or input its name directly in the Image/Class attribute.
   3. Type Setup in List Entry Label field. This label will appear in app menu.
   4. In Target Type attribute you specify a page in current app or any valid URL. Target Type and Page properties inform Oracle APEX where to land when a menu item is clicked, eg Orders entry will take you to page 4.

4. Using button labeled Create and Create Another, create two more level-1 entries to form the main menu of our app because we set No Parent List Item for all three entries.

   Parent List Entry	Image/Class	List Entry Label	Target Type	PgID	Help
   No Parent List Item	eg fa-database	Setup	No Target		not associated to any app page
   No Parent List Item	eg fa-send-o	Orders	Page in this app	4
   No Parent List Item	eg fa-table-arrow-up	Reports	Page in this app	1

   After adding last entry (Reports), click Create List Entry button.

5. Using same process create submenus level-2 menu entries. First two entries will come under the main Setup menu item, while Reports menu will contain two child entries (Graphical Reports and Advance Reports).

   Parent List Entry	Image/Class	List Entry Label	Target Type	PageID	Help
   Setup	eg ...	Manage Customers	Page in this app	2
   Setup	eg ...	Manage Products	Page in this app	3
   Reports	eg ..	Graphical Reports	Page in this app	1
   Reports	eg ...	Advance Reports	Page in this app	1

   TIP: If you make a mistake while creating these menu entries, you can rectify it. After creating the last entry, click Create List Entry button on Create/Edit page to move back to the List Details page. On this page, click menu entry you want to modify (under Name column) to call its definition in Create/Edit page. Rectify the error and click Apply Changes button.

6. Create llevel 3 entries. First 5 entries will appear as submenu choices under Graphical Reports menu. Similarly, Monthly Review Report and Customer Invoice will be placed under Advance Reports. All the settings will set up a hierarchical navigation for your app.

   Parent List Entry	Image/Class	List Entry Label	Target Type	PageID	Help
   Graphical Reports	eg ...	Customer Orders	Page in this app	1
   Graphical Reports	eg ...	Sales By Category/Products	Page in this app	1
   Graphical Reports	eg ...	Sales By Category/Month	Page in this app	1
   Graphical Reports	eg ...	Order Calendar	Page in this app	1
   Graphical Reports	eg ...	Product Order Tree	Page in this app	1
   Advance Reports	eg ...	Monthly Review Report	Page in this app	0	set to zero, because it will be invoked through a print request that will be configured in Chapter 9.
   Advance Reports	eg ...	Customer Invoice	Page in this app	1

   TIP: After making any modification in your app you can test it immediately. For example, after creating the navigation menu, hit Run Page button (at top-right) to see the app menu.

List 2 Reports List

Top.....Apexws_cloud.....App. builder.....Page.....Environm.....URL.....Sales.....SHARED C......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p.

List "Reports List" will have several links that will lead to different reports in your app. Note that you created the same links in the navigation menu in the previous section to call some of these reports from app menu. Reports List being created here will be used on a dedicated report page to call respective reports - see Chapter 8 section 8.2.

1. Go to App 80858 -> Sales Web App -> Shared Components -> Navigation -> Lists. There are 7 default (APEX created) lists.
   Click Create button to create a new list.

2. Select From Scratch on the Source wizard screen and click Next.
   On the next screen, enter Reports List for Name, select Static as the list Type, and click Next.
   When you create a static list you define a list entry label and a target (either a page or URL).

3. Enter the following values in Query or Static Values screen.
   Initially, the wizard allows you to create five entries. The remaining entries and Image/Class properties are created and set after saving the first five.

   List Entry Label	Target PageID	Image/Class
   Customer Orders	17
   Sales by Category and Product	16
   Sales by Category / Month	5
   Order Calendar	10
   Product Order Tree	19
   Gantt Chart	20
   Box Plot	21
   Pyramid Chart	22
   List View (Mobile)	23
   Column Toggle Report (Mobile)	24
   Reflow Report (Mobile)	25
   Monthly Review Report	0
   Customer Invoice	50

4. After entering the first five list entries

   1. click Next, accept default values in next screen, and click Create List button.
   2. You will be taken back to the Lists page, where you will see new list "Reports List"
   3. Modify list by clicking Reports List link in the Name column
   4. Click Create Entry button to add the sixth entry. Enter Product Order Tree in List Entry Label. Set Target Type to Page in this app and enter 19 in the Page attribute.
   5. Click Create and Create Another button to add the remaining entries, as shown in the table above
   6. Modify each entry by clicking its name in the List Details interface and add image references. Click the Apply Changes button after adding the image reference

List 3 "Order Wizard" List (wizard steps to create an order)

Top.....Apexws_cloud.....App. builder.....Page.....Environm.....URL.....Sales.....SHARED C......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p.

List 3 is another utilization of lists. Rather than associating list items to pages in the app, you will use it for visual representation. It will be used while creating orders in Chapter 7. In our app, we will create an order using a set of wizard steps in the following sequence:

1. Identify Customer
2. Select Items
3. Order Summary

So :

1. Go to Shared Components | Navigation | Lists and click Create button

2. Select the first From Scratch option and click Next

3. Type Order Wizard in the Name box, set Type to Static, and click Next

4. On the Query or Static Values screen, enter the following values and click Next

   List Entry Label	Target Page ID or custom URL	Help
   Identify Customer	11
   Select Items	12
   Order Summary	14

5. Click Create List button on the Confirm screen.

6. Modify the newly created Order Wizard list.

7. Edit each list item, set Target Type attribute to No Target for all 3 list items.

   1. The No Target value is set because this list is intended to display the current order wizard step where the user is within the order processing module, and not to call a page in the app.
   2. In Current List Entry section, set "List Entry Current for Pages Type" to Comma Delimited Page List for all 3 list items, and set "List Entry Current for Condition" attribute to 11, 12, 14.
   3. Click Apply Changes button to save the modifications.

List Entry Current for Pages Type attribute specifies when this list entry should be current. Based on the value of this attribute, you define a condition to evaluate. When this condition is true then the list item becomes current. The template associated with list item gives users a visual indication about the active list item. The following figure illustrates the use of Order Wizard list. Being the first step in the order wizard, the Identify Customer list item is marked as current (when Page 11 is called to enter a new order), while the remaining two are displayed as non-current. After selecting a customer, when you move on to the next step to select ordered items, the Select Items entry becomes current and the first and last entries become inactive.

                     -------------------------------
                     |                             |
                     |                             |
        Non current  -------------------------------

        -------------------------------
        |                             |
        |                             |
Current -------------------------------

3.3 Desktop Navigation Bar eg top right

Top.....Apexws_cloud.....App. builder.....Page.....Environm.....URL.....Sales.....SHARED C......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p.

Also used to link various pages within app eg Help pages, Sign Out. Location eg top right of navigation bar depends upon associated page template. When you create a navigation bar, you specify an image name, label, display sequence, and target location (a URL or a page). Navigation bar used in our app will show :

feedback page icon, Page Help entry, About Page entry, id of logged in user and a Sign Out link.

All these entries are created automatically when you create new app.

3.4 List of Values (LOV)

Top.....Apexws_cloud.....App. builder.....Page.....Environm.....URL.....Sales.....SHARED C......Rep.List.....Ord.WizList.....Top. Navig.....LOVs.....Home p.

Used to control input values and limit users selection. You can define two types of lists:

1. static list of values is based on predefined display and return values
2. dynamic LOV is based on a SQL query and it is executed at runtime.

3.4.1 LOV CATEGORIES (U, T) - STATIC

See 6.4.2 Attach Categories LOV.

3.4.2 LOV PRODUCTS WITH PRICE - DYNAMIC

You will use this LOV in Chapter 7 section 7.4.2.

1. Shared Components -> Other Components section -> Lists of Values APEX page -> Click Create button
2. Select From Scratch and click Next.
3. Enter Products with Price in the Name box. Select Dynamic Type and click Next.
4. On the List of Values Source screen, select SQL Query for the Source Type, and enter the following query in the Enter a SQL SELECT statement box.

   select apex_escape.html(product_name) || ' [$' || list_price || ']' d
       , product_id r
   from demo_product_info
   where product_avail = 'Y'
   order by 1

5. On the final Column Mappings screen, select R for Return Column, D for Display Column and click the Create button to finish the wizard.

APEX_ESCAPE.HTML function is used to protect against XSS (Cross Site Scripting) attacks. It replaces characters that have special meaning in HTML with their escape sequence. It converts occurrence of :

```
& to &
“ to "
< to <
> to >
```

3.4.3 LOV STATES (države) - DYNAMIC (used in crUD form Customers)

See 5.4.2 Change cust. column "P7_CUST_STATE" to hold predefined LOV! States list.

3.4.4 LOV NEW OR EXISTING CUSTOMER - STATIC

Will be incorporated in initial Order Wizard step (Chapter 7 Section 7.5.4) to select an existing customer for a new order or to create new one.

1. Shared Components -> Other Components section -> Lists of Values APEX page -> Click Create button
2. Select From Scratch and click Next.
3. Enter NEW OR EXISTING CUSTOMER in Name box, select Static as its Type and click Next

4. Fill in display and return values as shown in following tbl and click Create LOV button.

   Sequence	Display value	Rerturn value
   1	Existing customer	EXISTING
   2	New customer	NEW

3.5 Images

Top.....Apexws_cloud.....App. builder.....Page Environm.....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy

You can reference images within your app by uploading them to Images Repository. When you upload an image, you can specify whether it is available to all apps or a specific app. Images uploaded as shared components can be referenced throughout an app. They may

1. include images for application menus or buttons
2. or may represent icons that, when clicked, allow users to modify or delete data.

Images uploaded to the images repository should not be directly related to the app data such as images of products and employees. Such images must be stored in the apps schema alongside the data to which the image is related. You will follow this approach in Chapter 6 to save each products image along with other information in a DB table.

APEX images are divided into two categories:

1. Workspace images are available to all applications for a given workspace
2. App images are available for only one app

Add your app logo to images repository.

Logo appears at every page top in app.

1. Shared Components -> Files section -> click Static Application Files
2. Click the Upload File button.
3. Click the Choose Files button and select logo.ico file, available in the book code
4. Click the upload button. After uploading the image, you need to tell Oracle APEX to use this file as your app logo :
5. Shared Components -> User Interface section -> User Interface Attributes
6. Logo section -> Image and Text for Logo
7. Enter #APP_IMAGES#oracle_apex.ico in Image URL box,
   and enter Sales Web App in the Text box. An application logo can be an image, text, image and text, or based on custom markup.

When you select a type for your app logo, additional attributes appear depending upon your selection. With this selection, your app logo and app name both will be displayed on each app page. The built-in substitution string (APP_IMAGES) is used to reference uploaded images, JavaScript, and cascading style sheets that are specific to a given app and are not shared over many apps. You must use this substitution string if you upload a file and make it specific to an app. Note that you must use the correct case for the image file name and extension, else the logo will not be displayed at runtime. Click the Apply Changes button.

Run the app.

4. Page Home (App Dashboard, default page)

with 6 stacked canvases (blocks) Top.....Apexws_cloud.....App. builder.....Page   Environm.....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs....Home page.....Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy

4.1 About Home Page
Represents objective of app (module). Is created as a blank slate and needs to be populated with content relevant to your app (module) - stuff related to sales is :
6 regions to present different views of sales data (see 2.6 and 2.9 Page Designer interface and how to access your workspace).

4.2 Modify Home Page - 2 properties that are usually enough to set for the main page

1. Sign in to your workspace -> in main menu click App Builder option -> click Edit icon under Sales Web App - see Figure 3-3 in chapter 3.

2. Click Home page icon (if you’re browsing the page in Icon view). This action will open definitions of Home page in Page Designer interface.
   Modify Page Attributes Home page Name and Title properties with meaningful labels.
   Rendering tab to your left -> click root node to refresh Property Editor (on the right side) with the main page properties. Set the properties mentioned in the following table and click the Save button (at the top-right corner).

   Property	Value	Note
   Name	Sales Web App
   Title	Sales Web App	APEX engine uses title in place of #TITLE# substitution string used in page template inserted between the HTML tags

Use help : Click a property in the Property Editor and then click the Help tab (in the Central pane).

4.3 Create Regions (stacked canvases - blocks) in Home Page

You put page items (Text Field, Select List, Radio Group, report, chart, static HTML content, buttons...) on a page under a specific region (page area, section that serves as a container for content ee to group page elements). Each region can have its own template, which controls its appearance.

Some of our regions will use Oracle JET Charts. Oracle JET Charts (charting library) is a component of JET. These charts work on any modern browser regardless of platform, screen size, or features. JET is integrated Oracle JavaScript and CSS3 and HTML5 Extension, open source Toolkit

To remove a component (such as a region or an item) from a page, right-click the desired component in the Rendering section, and select Delete from the context menu. If you just created the component, click Undo (A) on the Toolbar to remove it from the page.

Home Page region 1 : "Top Orders by Date"

Top.....Home reg.1.....Home reg.2.....Home reg.3.....Home reg.4.....Home reg.5.....Home reg.6.....Home buttons.....Home styles

Displays top five orders by date from DB using a bar chart - horizontal bars=months and sumsales on x axe - SQL (or PL/SQL function) populated - summarized sales figures (bars) for each date from Orders table.

!!!!! Rendering tab -> right-click Regions -> select Create Region to place new region = </> New under Content Body.

Click "</> New node" (future "Top Orders by Date" stacked canvas) and set common region properties in Property Editor.

Property Editor is declarative (ee functional programming) above some prog.language procedural code.

Set common region properties (attributes)**

Set region-specific properties

Click Attributes node under new region and set the following region-specific properties :

Set Type of this chart region to horizontal bar :

Click the New sub-node under Series and set the following properties:

Click No Link Defined under Target and set props in Link Builder dialog box:

Define links on charts is done in properties 13-15. Links let you call another app page for browsing details.

To test your work from time to time eg after completing this region you can save and run page (by clicking the Save and Run Page button at the top-right corner) to check how the region appears on it. At this stage, your Home page will show just one region (Top Orders by Date). If you click any bar in the chart, app tries to open Page 4 (Orders) and throws an error, because Orders page does not exist. After completing Page 4 (Orders) in Chapter 7, when you run the Home page and click any of these links, Page 4 will be rendered carrying a list of orders.

Home Page region 2 : "Sales For This Month"

Top.....Home reg.1.....Home reg.2.....Home reg.3.....Home reg.4.....Home reg.5.....Home reg.6....Home buttons.....Home styles

Page region 2 uses a Badge List - 2 circles "Tot.sales and Tot.orders". List is dynamically rendered based on a SQL Statement (or PL/SQL function) each time the page is viewed. Later on, we will transform it to present fetched data in graphical format.

Create another region as previous region.

Set common region properties (attributes)**

Rendering tab to your left App builder -> Home icon (App 80858) -> Page Designer -> right-click Regions -> select Create Region from context menu. New region will be created under previous one. Set following properties for this region in Property Editor.
TIP: If a region is not created in the desired location, drag and drop it to the appropriate location in the rendering tree.

Create hidden page item to store first day of currmonth for behind processing

right-click Sales for this Month region and select Create Page Item from the context menu. A new node named Items will be created with a new item named P1_NEW. Hidden items can be seen in the Page Designer, but they do not appear on the page at run time.

Click the new item and set the following properties:

Select TOTAL_CUSTOMERS column and set the Type attribute of this column to Hidden Column (invisible at run-time).

TOTAL_SALES to transform it into a link

How to refer to a page item in links - present it as a substitution string : &P1_THIS_MONTH. - terminated with a period !! - see Value property in serial 6 in the following table.

Rendering section -> expand Columns node under the Sales for This Month region -> click TOTAL_SALES column - Figure 4-4. Set following props :

Define link : Click No Link Defined under Target -> Link Builder dialog is opened -> set following properties. To call another app page, it is suffice to transform a column into a link by setting three values : Link, Page in this application, and Page Number. Recall that in previous region you formed a similar kind of link.

Click OK to close the Link Builder - Target dialog box

In the current scenario, we used one name/value pair to filter interactive report on the target page. However, this section allows you to set as many filters as you want. Each time you provide a value, another row is appended, thus allowing you to enter another pair of name/value. You can use this section to also specify target pages items in the Name column and can set their values using the Value box.

For example, to set a customers credit limit items value on the target page, enter the name of that item :
(P7_CREDIT_LIMIT) in the Name box and type the corresponding value (5000) in the Value box. This way, when you call the target page, the value (5000) appears in the credit limit item.

Note that column names are enclosed in # symbol when you specify them in Link Text attribute. This is a mandatory attribute whose value can be selected using the Quick Pick button appearing next to it.

At run-time the link is formed like this (if the application's Friendly URL attribute is turned off):
f?p=145615:4:8824748217892::NO:RP,RIR,4:IRGTE_ORDER_DATE:01012018
using the following syntax:
f?p=&APPID.:Page:Sessionid::NO:RP,RIR,4:IRGTE\(itemname):itemvalue (stored in &P1_THIS_MONTH item). Table URL parameters :

Set region-specific properties

"Sales for This Month" region -> Click Attributes node -> Switch its Template from "Standard" to "Badge List" -> click Template Options -> set Badge Size to 128px, Layout to Span Horizontally -> click OK.

By setting these region properties, the derived one row summarized report will be presented as a badge list, spanned horizontally.

Also set Pagination Type to No Pagination (Show All Rows).

Click Save and Run Page button to see this region with two badges (circles) on it displaying 1. current month's sales and 2. number of orders placed.

First badge - Current month's sales - acts as a link and leads you to Page 4 to display details of the summarized data. Since Page 4 will be created in Chapter 7, you will get Page Not Found message if you click this badge.

Home Page region 3 : "Sales by Product region" - pie chart

Top.....Home reg.1.....Home reg.2.....Home reg.3.....Home reg.4.....Home reg.5.....Home reg.6....Home buttons.....Home styles

Page region 3 shows sale figures (sums as pie slices) visible when you move the mouse pointer over pie slices of pie chart for individual products.

Create another region as previous region.

Set common region properties (attributes)**

Set region-specific properties

Click the Attributes sub-node under new region and set following properties.

Click the New sub-node under Series and set the following properties:

Home Page region 4 : "Sales by Category" region

Top.....Home reg.1.....Home reg.2.....Home reg.3.....Home reg.4.....Home reg.5.....Home reg.6....Home buttons.....Home styles

Page region 4 will present sale figures - bars for each product category : Men, Women, and Accessories.

This time, we will add a region using the drag and drop APEX feature. Drag Chart icon from Regions gallery and drop it under "Top Orders by Date region. Chart region will appear. Place chart region at its proper location, if needed.

Set common region properties (attributes)**

Set region-specific properties

Click Attributes sub-node under new region and set following properties.

Click the New sub-node under Series and set the following properties:

Home Page region 5 : "Top Customers" region

Top.....Home reg.1.....Home reg.2.....Home reg.3.....Home reg.4.....Home reg.5.....Home reg.6....Home buttons.....Home styles

Top six customers with highest orders and will present info in text format.

Create a new region by dragging Classic Report icon from gallery and dropping it under "Sales by Category" region. Source of a Classic Report is SQL query. Each time page is rendered, APEX evaluates query and displays result within the region. Once you specify row and column properties using following table, region will appear next to the Sales by Category region.

Set common region properties (attributes)**

Rendering tab -> expand Columns node under "Top Customers" region, and click CUSTOMER_NAME column. Set following properties to transform this column into a link to provide drill-down capability. APEX specifies CUSTOMER_NAME column in Link Text attribute.

Click No Link Defined under Target and set the following properties:

When we run this page, each customer's name appears as a hyperlink, clicking which calls customer's profile page (Page 7). We set Page attribute to 7, which is the page we want to navigate to. We also forwarded the customer’s ID (#ID#) to Page 7.

Click ORDER_TOTAL column and set Format Mask to $5,234.10 = FML999G999G999G999G990D00.

Select ID column and set Type property (under Identification) to Hidden Column to hide this column at run-time.

Set region-specific properties

Click the Attributes sub-node under new region and set following properties.

Pagination is suppressed since we want to see only six records in the region. We also set Heading Type to None to suppress column headings.

Click Save and Run Page button to test the progress.

Home Page region 6 : "Top Products" region

Top.....Home reg.1.....Home reg.2.....Home reg.3.....Home reg.4.....Home reg.5.....Home reg.6....Home buttons.....Home styles

Similar to Top Customers region - create this region by copying the Top Customers region. Displays six top selling products.

Right-click Top Customers region -> Duplicate from context menu. Copy of source region will be appended just under it.

Set common region properties (attributes)**

Expand the Columns node and click PRODUCT column to set the following properties:

Click No Link Defined under Target and set the following properties:

Click SALES column and set its Format Mask to $5,234.10. Select PRODUCT_ID column and set its Type property to Hidden Column.

Set region-specific properties

Click the Attributes sub-node under new region and set following properties.

Click Save and Run Page button to see how all six regions appear on Home page.

END creating all regions.

4.4 Create Buttons on top of each region

Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy

These buttons provide drill-down functionality and take user to relevant pages to dig further details for summarized information. Eg buttons add, view... - click "Add Order" btn in "Top Order by Date" region -> redirect to page 11 to add new order.

4.4.1 Button View Orders

To view a list of all customer orders.

Right-click Top Orders by Date region -> select Create Button from context menu. This way, button will be created in selected region. A new node "Region Buttons" will be added with a button.

If we click new button ">" : Sorry, this page isn't available Application "80858" Page "4" not found.

4.4.2 Button Add Order

Calls Order Wizard (to be created in Chapter 7) to place a new order.

Right-click Region Buttons under the Top Orders by Date region -> select Create Button. A new button will be added under previous one.

4.4.3 Button View Orders For This Month

Drill-down into current month's order details.

1. drag "Icon" button from Buttons gallery
2. drop it under Sales for this Month region in EDIT position

A new button will be added to this region.

Link properties set here are similar to those set earlier in section 4.3.2.Figure 4-7

4.4.4 Button View Customers on Page 2 of app.

You’ll place two buttons in the Top Customers region. Create these buttons using either of the two methods applied above.

4.4.5 Button Add Customer call Page 7 "Customers" blank form

Target page will appear on top of the Home page (as a modal dialog).

Right-click VIEW_CUSTOMERS button -> select Create Button. Set following properties for new button.

4.4.6 Button View Products leads to main products page 3 - list of all products

Two buttons in the Top Products region (same as in Top Customers region).

4.4.7 Add Product Button calls Page 6 to add a new product

At this stage, all the seven buttons are placed at their proper locations with expected functionalities and are ready for partial test.

These buttons will be productive after creating pages indicated in their Target properties.

4.5 Styling Page Elements - 6 regions light gray

Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy

CSS provides way to control style of a web page without changing its structure. When used properly, a CSS separates visual properties such as color, margins, and fonts from the structure of the HTML document. APEX includes themes containing templates to reference their own CSS. The style rules defined in each CSS for a particular theme also determine the way reports and regions display.

CSS can be added to APEX apps inline, as CSS file(s) or through ThemeRoller.

Depending on your requirements, you can add CSS to your app at the:

1. Page Level
2. Page Template Level
3. Theme Style Level
4. Theme Level
5. User Interface Level - In this exercise
   Here you’ll upload a custom CSS file carrying just one rule to style all six regions of the home page. The file named AppCss.css available in the source code contains following rule, which creates a rounded border and places inset shadow around regions. For more details on CSS, see Chapter 7 section 7.6.1.

.region {background:white;border-radius:10px 10px 10px 10px;box-shadow: inset 0px 0px 30px #dfdbdf}

To apply CSS at user interface level:

1. Shared Components page -> Files section -> Static App Files

2. Click Upload File button

3. click Choose Files, select AppCss.css file from the source code and click Upload. The css file will be added to the static app files listing. Copy Reference URL entry #APP_IMAGES#AppCss.css appearing on this page to your clipboard.

   APP_IMAGES substitution string is used to reference uploaded images, JS, and CSS specific to given app and are not shared over many apps. Recall that you used this substitution string earlier in chapter 3 to reference app logo.

4. Add the CSS file to User Interface : Shared Components User Interface section -> User Interface Attributes.

5. On User Interface tab, click Cascading Style Sheets sub-tab and press Ctrl+V to append the reference text in File URLs box under existing URL :

   #APP_IMAGES#app-icon.css?version=#APP_VERSION#
   #APP_IMAGES#AppCss.css

6. Click Apply Changes.

7. Finally, you have to apply the CSS rule to your region. Open home page (Page1) of your app and select first region - Top Orders by Date. In the properties pane, scroll down to the Appearance section and enter region (a class defined in the AppCss.css file) in CSS Classes attribute.

   CSS allows you to specify your own selectors called "id" and "class".

   1. id selector is used to specify style for single, unique element. It uses id attribute of HTML element, and is defined with "#" identifier.
   2. class selector is used to specify a style for a group of elements. This means you can set a particular style for many HTML elements with the same class. It uses HTML class attribute, and is defined with "." identifier.

   Add the region class to the CSS Classes property of the remaining five regions.

Test Your Work

Click the Save and Run Page button to see the Home page, which should now look similar to the one illustrated in Figure 4-1 at the beginning of this chapter.

Summary of chapter 4 "Home page" declarative (functional) development

We added contents to a blank page, modified properties to customize look and feel of this page. This is uniqueness and beauty of Oracle APEX that allows you to create pages rapidly without writing tons of code. Oracle APEX features you learned :

1. Region - We added six regions (stacked canvases) to display different types of contents : different types of charts, badge list, and classic reports to populate these regions via simple SQL statements.

2. 12 columns grid Layout - to arrange multiple regions on a page.

3. URL & Links - link app pages together by setting some properties. How APEX formulates URL and passes values to target page using some link properties.

4. Buttons can also be used to link application pages. You created a few buttons to access different application pages.

5. Apply Styles - You learned how to add custom styles to page elements through user interface level.

In the next chapter, you will learn about Interactive Grid and how to create web forms to receive user input.

05. Module Customers 2 pages

tbl ID=2 CRud & form ID=7 crUD - profile page 133/419

Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy

5.1 CRUD DEMO_CUSTOMERS tbl

1. R Browse and search customer records
2. U Modify customers profiles
3. C Add record of a new customer to DB
4. D Remove a customer from the database

Customers info is used in other app segments (app modules) eg customer orders and invoices. You can evaluate how much business you have done with your customers either by location or by product, as you did in previous chapter where you created Customers region on page ID=1 Sales web app.

5.2 Page Customers tbl ID=2 - CRud - main page of Cus. module - IG (interactive grid)

Page ID=1 Sales web app - was created by App Builder wizard at the time when app was created. Other pages in this app will be created manually with help of wizards and copy utility.

Customers who have some existing orders cannot be Deleted - see chapter 2 section 2.10 step 11.

Each customers name appears as link in interactive grid -> form page profile of selected customer.

Create two module pages via built-in wizard:

1. Main App Builder interface -> click "Sales Web Application's Edit icon" (A) -> click "Create Page" button (B).

   Create tbl IG page ID=2, Editing Enabled=Off (for "On" see Sample Interactive Grids)

   TIP: To delete page, open page in Page Designer by clicking its name -> select Delete from top-right Utils menu.

2. On first wizard screen, select "Report" option -> report of customers in an interactive grid.

3. On next wizard screen, click Interactive Grid. This screen presents sub-categories of reports and requires a single selection the report will base on. The option you selected here means an interactive grid will act as a report to display all customers from DB.

Up to version 5.0 APEX used IR (Interactive Report) feature to present data tbl. Since version 5.1 - new feature IG (Interactive Grid) similar to IR + clicking on a cell and editing its value (Clipper Dbedit !). IG introduces fixed headers, frozen columns, scroll pagination, multiple filters, sorting, aggregates, computations... Supports all item types and item type plug-ins. You can create master-detail relationships to any number of levels deep and across. See section 5.6.

4. On the next wizard screen, set following

   Properties of IG page

   	Property	Value	Help
   1	Page Number	2	Main page of this module (form page to be created next will have number 7)
   2	Page Name	Customers
   3	Page Mode	Normal	How you want to see page. It has two options: 1. Normal Dialog. New pages default to Normal. When you call a normal page, it replaces an existing page appearing in your browser. 2. Modal Dialog is stand-alone page, which appears on top of its calling page and doesn't allow users to do anything else unless it is closed. A modal page can be displayed only on top of another page.
   4	Breadcrumb	Breadcrumb	Breadcrumb shared component was created by the App Builder when you created this app earlier (see Shared Components -> Navigation > Breadcrumbs). In this step, you selected the same breadcrumb component and added an entry name (Customers) to it. Take a look at Figure 5-1 and see where the provided entry name appears in the breadcrumb region. We use breadcrumbs as a second level of navigation at the top of each page - hierarchical list of links indicates where the user is within the app from a hierarchical perspective to switch to any level.
   5	Parent Entry	Home (Page 1)	To create hierarchy in this app, you selected Home menu entry as Parent Entry for this page.
   6	Entry Name	Customers

5. On Navigation Menu wizard screen

   Set menu item to call this page

   1. set Navigation Preference = Identify an existing navigation menu entry for this page
   2. set Existing Navigation Menu Entry = Setup
   3. click Next.

   This step will make Setup entry active in the main navigation menu (created in Chapter 3, section 3.2.1) when this page is accessed.

6. On Report Source screen, set following properties.

   	Property	Value	Help
   1	Editing Enabled	Off	form pg ID=7 edits cust records. Examples of editing rec. in interactive grid see later in this ch.
   2	Source Type	Table	Tbl data to populate this interactive grid
   2	Table/View Owner		accept displayed value of Oracle schema (POSSYS) to which you are connected.
   2	Table/View Name	DEMO_CUSTOMERS (table)	Once you select a schema, all tables within that schema are populated in Table/View Name drop-down list from where you select a table - DEMO_CUSTOMERS in the current scenario whose data will be displayed in the interactive grid. Note that in the current scenario you can select only one table from the provided list.

   If not visible, click the arrow icon next to the Column section to see the table columns. When you choose a table, all the columns from that table are selected (moved to the right pane in the Columns section). For this exercise, leave the following columns in the right pane and exclude others by moving them to the left pane using Ctrl+click and the left arrow icon. Here are the columns we want to show in the interactive grid. Cust_First_Name, Cust_Last_Name, Cust_Street_Address1, Cust_Street_Address2, Cust_City, Cust_State, and Cust_Postal_Code.

7. Click Create button to finish report page creation process.

Page Customers tbl ID=2 is created and its structure is presented in Page Designer. Only significant aspect of this page is "Customers" Interactive Grid region under :

Rendering -> Regions -> Content Body node to your left. Wizard created this region with all columns you specified in step 6 - see SQL Query box in Page Designer. All these columns appear under Columns node.

Properties in the Interactive grid's Attributes node control how an interactive grid works. For example, developers use these properties to determine if end-users can edit the underlying data, configure report pagination, create error messages, configure toolbar, use download options, control if and how users can save an interactive grid, and add Icon and Detail Views to the toolbar. You will go through these properties later in this chapter. For now, walk around the Page Designer to observe page components and relevant properties.

Click Application eg 145615(=appID) breadcrumb at top-left to leave Page Designer interface.

Create Page ID=7 to carry form "CrUD Customers"

1. Click Create Page button -> select "Form" option -> click another Form option on next wizard screen -> creates form page based on DB table.

   	Property	Value	Help
   1	Page Number	7	Page to carry form to CrUD Customers
   2	Page Name	Customer Details
   3	Page Mode	Modal Dialog	stand-alone page, which appears on top of the calling page. Oracle APEX page can be created as dialog, which supports for all the functionality of a normal page, including computations, validations, processes, and branches.
   4	Breadcrumb
   5	Parent Entry	Customers (Page 2)	called from Page 2
   6	Entry Name	Customers Details

2. On the Navigation Menu screen, set "Navigation Preference" = Identify an existing navigation menu entry for this page, set "Existing Navigation Menu Entry" = Setup, and click Next.

3. On the Source screen, set the following properties and click Next.

   	Property	Value	Help
   1	Data Source	Local DB
   2	Source Type	Table
   2	Table/View Owner		accept displayed value of Oracle schema (POSSYS) to which you are connected.
   2	Table/View Name	DEMO_CUSTOMERS (table)

4. This time, select all columns from DEMO_CUSTOMERS tbl to display all of them in the input form (Page 7) to populate backend DB tbl. For "Primary Key Type", select second option Select Primary Key Column(s). Then, set first "Primary Key Column" attribute to CUSTOMER_ID. Click "Create" button to complete form page creation process.

   In this step, you specified PK (primary key) column col or set of cols that uniquely identify record in tbl. Note that in current scenario PK col for cust tbl will be populated using DEMO_CUSTOMERS_SEQ Sequence object through BI_DEMO_CUSTOMERS trigger. Trigger fires when you insert a new customer. To browse this trigger, select SQL Workshop -> Object Browser -> Tables -> click on DEMO_CUSTOMERS table and then click the SQL tab. Sequence is a database object that automatically generates PK values for every new cust row. Rows are identified using either PK defined on tbl, or ROWID pseudo column, which uniquely identifies row in a tbl. Forms support up to 2 columns in PK. For tables using primary keys with more than two columns, ROWID option should be used. For further details, see Chapter 2.

5. Access main App Builder interface by clicking application ID breadcrumb to see two new pages (Customers and Customer Details) with their respective page numbers. Click Customer Details (Page 7) to open its definitions in Page Designer.

   Expand Pre-Rendering node and rename process Initialize form Customer Details as "Initialize Customer Details".

   Click Processing tab and rename process Process form Customer Details to "Process Customer Data". NOTE: If you see a different process name, then there is nothing to worry about as it sometimes happens due to change in APEX version.

APEX is a low-code application development platform. Two pages you just created have everything you need to view and manipulate data. Customers Page 2 contains Interactive Grid in which you can view all customers data.

Click Customer Details Page 7 to open it in Page Designer. On Rendering tab, expand Pre-Rendering node. Here, you will see an auto-generated process named Initialize Customer Details of Form Initialization type. This Process is responsible to initialize form region items. Initialization can either be fetching data from region source, using PK value(s) or simple initialization of form region items. Process fetches and displays data in page items when you select customer by clicking corresponding edit icon on the reports page and it initializes page items when you create a new customer record.

Customer Details region is a Form type region, which connects to local DB and fetches data from DEMO_CUSTOMERS table into relevant page items listed under Items node. Same page items are used to receive user input when new customer record is created.

In Buttons section, you will see a bunch of auto-generated buttons (Cancel, Delete, Save, and Create). DB Action property of these buttons specify function each button performs. When you click button (eg CREATE), corresponding DB action is submitted to process named Process Customer Data, which resides under the Processing tab. This process is of Automatic Row Processing (DML) type and performs CrUD action on form region - Customer Details region in current scenario.

5.3 Modify Tbl Customers Page ID=2

Main page of this module (Page 2) holds interactive grid generated by wizard with some default data source values.

In next steps we change default data source values and produce custom output using SQL query.

5.3.1 Modify Customers Region Prop.

Cols hdr, order, filter and cust name as link

1. In App Builder, click Customers page (Page 2) to open it in the Page Designer for modification.

   Cols hdr, cols order, cols filter in tbl Customers

2. Click Customers region under Content Body node. Standard method to modify properties of page component is to click corresponding node. This action refreshes Properties section (located to your right) with properties of selected page component for alteration.

3. Change Type (under Source section) = SQL Query to see default query generated for interactive grid.

   select ROWID,        CUSTOMER_ID,        CUST_FIRST_NAME,        CUST_LAST_NAME,        CUST_STREET_ADDRESS1,
      CUST_STREET_ADDRESS2,        CUST_CITY,        CUST_STATE,        CUST_POSTAL_CODE,        CUST_EMAIL,
      PHONE_NUMBER1,        PHONE_NUMBER2,        URL,        CREDIT_LIMIT,        TAGS
   from DEMO_CUSTOMERS

   Enter following SQL statement in SQL Query text area, replacing existing one:

   SELECT customer_id, cust_last_name || ', ' || cust_first_name customer_name
     , CUST_STREET_ADDRESS1||decode(CUST_STREET_ADDRESS2, null, null, ', '||CUST_STREET_ADDRESS2) customer_address
     , cust_city, cust_state, cust_postal_code
   FROM demo_customers

   Decode Syntax: decode( expression , search , result [, search , result]... [, default] )
   If default is omitted, Oracle returns null.

4. Expand Customers region -> expand Columns node. Click column eg CUSTOMER_NAME and change its heading (under Heading section in Properties pane) to Name. Change headings of other cols : Address, City, State, and Postal Code

5. In Columns node, click the CUSTOMER_ID column, and change it Type property from Number Field to Hidden to hide col at runtime. PK cols are added to DB tbls to enforce data integrity and are not displayed in apps. This is why such column's Type property is set to hidden to make them invisible at runtime.

6. Run page. Click Actions menu (A) - select Columns. When you click column in left pane, right pane (D) shows its name and width. You can input a numeric value to change width of col. Using arrow icons (E), arrange selected columns in following order: Name, Address, City, State, and Postal Code

7. Click Save button in the Columns window to apply the changes.

8. After you modify interactive grid in runtime save it : Click Actions menu -> Save from Report option, otherwise you’ll lose the applied settings when you access it later.

9. Click Edit Page2 (F) in the Developer Toolbar at the bottom of page to access Page Designer.

   Cust name as link in tbl Customers

10. Click CUSTOMER_NAME column to set properties: transforming cust name col into link that will lead to Page 7 cust. form.

    When you click customer's name in interactive grid report at runtime:

    1. ID of that cust is stored in substitution string (&CUSTOMER_ID.) (G)
    2. and is forwarded to corresponding page item (P7_CUSTOMER_ID) (H) on Page 7, which displays cust profile using this ID.

    You created similar kind of link in Chapter 4 for region "Sales for this Month".

    Scroll down to Link section and click "No Link Defined" under Target to bring up Link Builder dialog box -> set link properties :

    	Property	Value	Help
    1	Type	Page in this app
    2	Page	7
    3	Name	P7_CUSTOMER_ID	This value refers to an item on Page 7 that will be populated with the value held in #ID# . It is forwarded to Page 7 from the Home page to display selected customer profile.
    4	Value	&CUSTOMER_ID.	references third column in above SELECT query. Standard procedure in APEX to refer to a column value is to enclose it between # symbols. To refer page item we use substitution strings.
    5	Clear Cache	7

    Use LOVs (I) in Set Items section to select item name (3) and value (4).

11. Close Link Builder dialog box using OK button.

12. Save and run page. Cust Name col will now appear as link. Click any customer name to see details on Page 7, which pops up on top of Page 2.

5.3.2 Button "Create" in tbl Customers

To call Page 7 with a blank form from Page 2 - Customers :

1. Rendering tab to your left click Customers interactive grid region -> set its Template property to Standard (was Interactiv). Selected template will place title and border for interactive grid region.

   Right-click Customers region and from context menu select Create Button. Button named New will be added. Set properties for new button :

   	Property	Value	Help
   1	Button Name	CREATE
   2	Label	Create Customer	appears as tooltip when you move over button at run-time
   3	Button Position	Copy	over dozen values, try other options as well to observe different positions
   4	Hot	On	renders the button in dark color
   5	Action	Redirect to Page in this App	(under Behavior) - Creates link to call Page 7.
   6	Target	Type = Page in this app Page = 7 Clear Cache =7	Creates link to call Page 7. Clear Cache prop. makes all items on target pg 7 blank

2. Save and run Page 2, which should look similar to Figure 5-1.

3. Click Create Customer button to call Customer Details Page 7 on top of calling page as a modal dialog.

5.3.3 Button "Call PHP page" to Call PHP script - report from APEX

https://www.youtube.com/watch?v=WLE9_1I_nA0

Like 5.3.2, properties are :

Open in new tab : ?

5.4 Modify properties of crUD form Customer Details - Page 7

With Page 7 cust. form being displayed in browser, click Edit Page 7 in Developer Toolbar at pge bottom to call this pge in PageDesigner.

5.4.1 Modify Page Items Properties

Like region placement in 12 cols grid layout, done for six Home pge regions, pge items can also be placed using APEX's grid layout, as follows.
Width property sets items width on page.
If Value Required is set to Yes and page item is visible, APEX automatically performs NOT NULL validation when page is submitted and you are asked to input a value for field. If you set Value Required to No, no validation is performed and a NULL value is accepted. Value Required attribute works in conjunction with Template = Required to signify (označi) mandatory items visually.

Page Id=7 Customer Details (profile) items properties and values

Page flds are in 2 columns, only "Tags" fld spans both cols. Save your changes and call this Page 7 by clicking any customer's name on Page 2. It should come up with selected customer profile (Figure 5-6 Customer Details Page). Click each item under Items node and apply following properties.

1. P7_CUST_FIRST_NAME Label=First Name.....Sequence=20.....Start New Row=On.....Column=Automatic.....Column Span=Automatic
   .....Template=Required.....Label Column Span=2 (becomes visible in Layout section only after setting Template property) .....Width=50.....Value Required=On
2. P7_CUST_LAST_NAME Label=Last Name.....Sequence=30.....Start New Row=Off.....Column=Automatic .....New Column=On.....Column Span=Automatic.....Template=Required.....Label Column Span=2 .....Width=50.....Value Required=On
3. P7_CUST_STREET_ADDRESS1 Label=Street Address.....Sequence=40.....Start New Row=On.....Column=Automatic .....Column Span=Automatic.....Template=Optional.....Label Column Span=2 .....Width=50.....Value Required=Off
4. P7_CUST_STREET_ADDRESS2 Label=Line 2.....Sequence=50.....Start New Row=Off.....Column=Automatic .....New Column=On.....Column Span=Automatic.....Template=Optional.....Label Column Span=2 .....Width=50.....Value Required=Off
5. P7_CUST_CITY Label=City.....Sequence=60.....Start New Row=On.....Column=Automatic .....Column Span=6.....Template=Optional.....Label Column Span=2 .....Width=50.....Value Required=Off
6. See 5.4.2 Change cust. column "P7_CUST_STATE" to hold predefined LOV! States list
7. P7_CUST_POSTAL_CODE Label=Zip Code.....Sequence=80.....Start New Row=On.....Column=Automatic .....Column Span=6.....Template=Required.....Label Column Span=2 .....Width=8.....Value Required=On
8. P7_CREDIT_LIMIT Label=Credit Limit.....Sequence=90.....Start New Row=Off.....Column=Automatic .....New Column=On.....Column Span=Automatic.....Template=Required.....Label Column Span=2 .....Width=8.....Value Required=On
9. P7_PHONE_NUMBER1 Label=Phone Number.....Sequence=100.....Start New Row=On.....Column=Automatic.....Column Span=Automatic .....Template=Optional.....Label Column Span=2.....Width=12.....Value Required=Off
10. P7_PHONE_NUMBER2 Label=Alternate No.....Sequence=110.....Start New Row=Off.....Column=Automatic .....New Column=On.....Column Span=Automatic.....Template=Optional.....Label Column Span=2 .....Width=12.....Value Required=Off
11. P7_CUST_EMAIL Label=Email.....Sequence=120.....Start New Row=On.....Column=Automatic .....Column Span=Automatic.....Template=Required.....Label Column Span=2 .....Width=50.....Value Required=On
12. P7_URL Type=Text Field.....Label=URL.....Sequence=130.....Start New Row=Off.....Column=Automatic .....New Column=On.....Column Span=Automatic.....Template=Optional.....Label Column Span=2 .....Width=50.....Value Required=Off
13. P7_TAGS Type=Textarea.....Label=Tags.....Sequence=140.....Start New Row=On.....Column=Automatic .....Column Span=Automatic.....Template=Optional.....Label Column Span=2 .....Width=100.....Value Required=Off

5.4.2 Customer's page column "P7_CUST_STATE"

to hold app-shared dynamic LOV! States list. app-shared means defined in app shared componebts in one place and reusable in all app modules pages.

1. In Shared Components to fetch State names from DEMO_STATES table

1. Shared Components -> Other Components section -> "Lists of Values" APEX page -> Click Create button
2. Select From Scratch option and click Next
3. Enter States in Name box, select Dynamic for its type, and click Next
4. On List of Values Source screen, select SQL Query for Source Type, and enter following query in Enter SQL SELECT statement box. Click Next

   both VARCHAR2    30 :
   select state_name display_value, st return_value from demo_states order by 1
   --select state_name display_value, state_id return_value from demo_states order by 1

5. On final Column Mappings screen, select RETURN_VALUE for Return Column, DISPLAY_VALUE for Display Column and click Create button to create the LOV.

2. P7_CUST_STATE prop.

Label=State.....Sequence=70.....Start New Row=Off.....Column=Automatic .....New Column=On.....Column Span=Automatic.....Template=Required.....Label Column Span=2 .....Width=make it null (this item will be transformed into a select list).....Value Required=On

3. Type Text Field change to -> Select List and Attach STATES LOV! to it (was created in 3.4.3)

1. In Page Designer interface, click P7_CUST_STATE item.
2. Change its Type property from Text Field to Select List.
3. Set Type (under List of Values) to Shared Components and select STATES for LOV. This step attaches States LOV to page item.
4. Turn off "Display Extra Values" property. An item may have a session state value, which does not occur in its LOV definition. Select whether this LOV should display this extra session state value. If you choose not to display this extra session state value and there is no matching value in list of values definition, first value will be selected value. Eg while creating new customer record you will see - Choose a State - as the first value in the list. This value is added to the list in following steps.
5. Turn on Display Null Value property, which is default. Display Null Value property makes it possible for a user to choose a null value instead of one of the list items. If you set this property to Yes, additional properties appear on the screen for you to specify display value for this new entry. Eg "Choose State".
6. Enter - Choose State - in Null Display Value. This step, along with the previous one, generates a placeholder that appears on top of the LOV asking for a selection whenever you call this page to create a new customer record.
7. Save your work.

5.4.3 Apply Input Mask to Items

Modify the two phone number items in cust. form pgID=7 and set their Value Placeholder property (under Appearance) to 999-999-9999. When a new customer record is added, this placeholder is shown in the two phone number items to receive input in the specified format. As you type in values, the placeholders will be replaced by the numbers entered.

5.4.4 Create Validation 1 (logic control of user input) - Check Customer field "P7_CREDIT_LIMIT"

Field "Credit Limit" is used to assign a credit cap to each customer with a figure of $5,000. If you enter a value more than the assigned cap, you'll be prevented by presenting an appropriate message. NOTE: Validation error messages display when :

1. validation fails the equality test
2. or evaluates to FALSE
3. or non-empty text string is returned.

In left pane of Page 7 cust.form : click Processing icon (was tab) -> right-click Validating node -> Create Validation from context menu (Figure 5-7) -> Set following properties for this new validation :

5.4.5 Create Validation 2 - "Can't Delete Customer with Orders"

This check is performed to retain DB integrity from front-end. Validation is performed using custom PL/SQL function, which returns either a true or false (deletion process is aborted) value. Validation is associated to DELETE button in last attribute in following table, which means that validation will be performed only when Delete button is pressed.

In left pane of Page 7 cust.form : click Processing icon (was tab) -> right-click Validating node -> Create Validation from context menu (Figure 5-7) -> Set following properties for this new validation :

Setting a condition type involves selecting a condition from list that must be met in order for a validation to be processed.

| 3 | PL/SQL Fn Body :

begin
for c1 in (
   select 'x' from demo_orders
   where customer_id = :P7_CUSTOMER_ID
) loop
   RETURN FALSE;
end loop;
RETURN TRUE;
end;

wbp cre_row

"Customer Details" - Page 7 - observe auto-generated buttons (Cancel, Delete, Save, and Create) with default functionalities. Eg when you fill in form with new customer's record and click Create button, record is added to DB table using a built-in process - discussed in a while.

In Pre-Rendering (pre_form crUD costomer) we read_by_id

Expand Pre-Rendering node (under root node - Page 7: Customer Details). Here, you will see process of Form Initialization type created by wizard - purpose of which is to fetch row from DB by key, and put row values into pge items Px_.... Eg when you click customer name in Interactive Grid on Page ID=2, ID of that customer is used by this process to fetch and display details of the selected customer on page ID=7.

Px_... flds are F[orm] flds

Wizard also created individual input items (under Customer Details region) for each tbl column.

1. Source Type property of these cols is set to DB Column
2. DB Column property is set to column name in table. Eg two properties set for P7_CUST_FIRST_NAME page item tells the ARF process to set item with value retrieved from CUST_FIRST_NAME table column.

Shared Component confirmation dialog box before deleting

Click root node (Page 7: Customer Details) and scroll down to Function and Global Variable Declaration section in Property Editor, you'll see a global variable defined as var htmldb_delete_message. This variable was generated automatically along with a corresponding shortcut named DELETE CONFIRM MSG (in Shared Components > Other Components > Shortcuts) to control record deletion process by presenting a confirmation dialog box before deleting a customer’s record (other app pages will also use it).

crUD process of form items with source of type DB Column

Note that Delete button was created by wizard with SQL DELETE DB action. Similarly, INSERT and UPDATE DB actions were set automatically for Create and Save buttons, respectively - see DB Action attribute under Behavior. When clicked, these buttons perform selected SQL operations to trigger specified DB action within built-in Automatic Row Processing (DML) type process, also created automatically by wizard on Processing tab. This process is located under Processing > Processes node and it is responsible to CrUD rows into backend database table. This process is used to process form items with source of type DB Column. This process has three advantages :

1. you are not required to provide any SQL coding
2. APEX performs DML processing for you
3. this process automatically performs lost update detection which ensures data integrity in apps where data can be accessed concurrently

In addition, wizard created Dynamic Action (Cancel Dialog) to close this form when Cancel button is clicked. These are some of the beauties of declarative development that not only generates basic functionalities of an app, but on the same time doesn't limit our abilities to manually enter specific and tailored code (demonstrated in subsequent chapters), both on the client and server sides to answer our specific needs.

Test Your Work

Save and run app. Access this module by clicking "Manage Customers" menu item (under "Setup"). You’ll see Page 2 - Customers (Figure 5-1), carrying an interactive grid. Grid has :

1. search bar (search string in report ) comprising :
2. magnifying glass - drop down list to limit your search to a specific column
3. text area
4. Go button - Type eg albert in text area and click Go button - shows rows. Click remove filter icon to reinstate the grid to its previous state. Alternatively, you can click Reset button appearing on top-right of the grid.

Actions menu in runtime

See Chapter 7. Couple of save options under Actions -> Report in runtime :

1. First one Save is used when you customize report by applying filters or moving columns. Otherwise you'll lose applied settings when you subsequently view same report.
2. Clicking second option Save As presents a window with Type drop-down list and Name text box. Developers can save four types of reports Primary, Alternative, Private, and Public :
   1. Primary report is initial interactive grid report rendered in your browser. Default Primary report (you are looking at) is created by app developer. It cannot be renamed or deleted. Primary report is displayed on toolbar under heading Default.
   2. Alternative report enables developers to create multiple report layouts. Only developers can save, rename, or delete an Alternative Report. An alternative report is based on default primary report and is rendered in a different layout (see Section 7.3.3 in Chapter 7).
   3. Private report can be viewed, saved, renamed, or deleted by user who created it.
   4. In contrast, when you save a report as public, all users can view it. By default, end-users cannot save Public reports. To enable support for Public reports, developers edit the report attribute and enables users to save it as public report - see step 7 Section 7.3.1 in Chapter 7. After saving, all these reports display on the Saved Reports list on toolbar.

Customer name column appears as a link in Page 2 -click it to modify row (Figure 5-6).Check credit limit validation by entering more than 5000 in Credit Limit box.

NOTE: You might encounter a primary key violation message while creating first customer record. This is because the Sequence object for this table is created with an initial value of 1. When you try to save the first customer record, 1 is assigned as its primary key, which already exists in the table. To cope with this situation, developers drop and re-create auto-generated sequence objects with a higher START WITH value. To keep things simple, I'd suggest beginners to click the Create button on the form page several times. After few clicks row will be saved.

5.5 When_window_closed - refresh Viewtbl after crUD Viewfrm is closed

Add Dynamic Action to refresh interactive grid region (Customers, Page 2) when modal dialog page (Page 7) is closed. When_window_deactivated is when mouse clicks on other window.

After C customer row or U existing one, interactive grid doesn't reflect CrUD, page doesn't get refreshed. Manually refresh your browser window eg F5. More professional approach is to refresh page automatically using dynamic action.

1. Open Page 2 (Customers) in Page Designer and click Dynamic Actions icon (was tab) appearing in left pane.
2. Right-click Dialog Closed node and select Create Dynamic Action from context menu. Figure 5-8. Set following properties for this dynamic action.
   Property Value
   1. Name Refresh Interactive Grid
   2. Selection Type Region
   3. Region Customers
3. Click Refresh sub-node and set Region to Customers. Refresh is an action which executes when the condition evaluates to true - ee, when modal dialog page is closed.

Save page and run it. Now you will see immediate reflection of your modifications in interactive grid.

5.6 IG : Learn IG - install app "Sample Interactive Grids" from App Gallery

06. Page Set Up Products Catalog

page 177/419

Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy

6.1 About Products Setup

Main Products Page 3, CRud product - Figure 6-1 Products Interactive Report Page - will have 3 different views: Icon, Report, and Details. Initially, wizard will create the Report View version that you'll modify with a custom SQL statement. Remaining two views (Detail and Icon) are placed on the page by enabling respective properties found under main Products region. Once you enable Detail and Icon views, their icons appear on main Search bar. Using these icons you can switch among different views to browse products information.

Product Details Page 6, CrUD product .

To create these two pages follow same approach as you did for Customers.

New stuff added to this module

1. technique to incorporate style sheet in APEX page
2. image handling and styling. This module is based on DEMO_PRODUCT_INFO table. Among conventional columns exists the following four special columns to handle images in DB. Normally, specialized processing is required to handle images in DB. APEX environment has eliminated need of specialized processing with these additional columns to properly process images in BLOB column.

Image handling and styling columns :

1. PRODUCT_IMAGE: This column uses BLOB data type. BLOB (Binary Large Object) is Oracle data type that can hold up to 4 GB data. BLOBs we use for storing digitized information eg images, audio, video, document files : PDF, MS Word, MS Excel, MS PowerPoint, CSV....

2. MIMETYPE: Multipurpose Internet Mail Extension (MIME) type identifies file format. MIME type enables apps (Internet browsers, e-mail apps...) to read (handle) file. Eg e-mail app can use MIME type to detect what type is file attached to e-mail. MIME types are composed of a top-level media type followed by a subtype identifier, separated by a forward slash character (/) eg image/jpeg. The top-level media type is a general categorization about content of file, while subtype identifier specifically identifies format of file.

You can view via Object Browser in SQL Workshop after uploading such file types to BLOB column in your table :

3. FILENAME: A case-sensitive column name used to store filename of BLOB, such as bag.jpg or CV.pdf.

4. IMAGE_LAST_UPDATE: A case-sensitive column name used to store the last update date of BLOB.

6.2 Create 2 Pgs for Products - tbl CRud report ID=3 & crUD form ID=6

Same approach as for Customers.

1. Main App Builder interface -> click "Sales Web Application's Edit icon" -> click "Create Page" button

   Create tbl IG page ID=3, Editing Enabled=Off (for "On" see Sample Interactive Grids)

   TIP: To delete page, open page in Page Designer by clicking its name -> select Delete from top-right Utils menu.

2. On first wizard screen, select "Report" means report of products in an interactive grid

3. On next wizard screen, click Interactive Grid. This screen presents sub-categories of reports and requires a single selection the report will base on. The option you selected here means an interactive grid will act as a report to display all customers from DB.

4. On the next wizard screen, set following

   Properties of IG tbl page 3

   	Property	Value	Help
   1	Page Number	3	Main page of this module (form page to be created next will have number 6)
   2	Page Name	Products
   3	Page Mode	Normal	How you want to see page. Or Modal = Dialog is stand-alone page, appears only on top of its calling page and doesn't allow users to do anything else unless it is closed.
   4	Breadcrumb	Breadcrumb	= hierarchical list of links. Breadcrumb shared component was created by App Builder when you created this app (see Shared Components -> Navigation > Breadcrumbs). In this step, you selected same breadcrumb component and added an entry name (Customers) to it.
   5	Parent Entry	Home (Page 1)
   6	Entry Name	Products

5. On Navigation Menu wizard screen

   Set menu item to call this page

   1. set Navigation Preference = Identify an existing navigation menu entry for this page
   2. set Existing Navigation Menu Entry = Setup - This step will make Setup entry active in the main navigation menu (created in Chapter 3, section 3.2.1) when this page is accessed.
   3. click Next.

6. On Report Source screen, set following properties.

   	Property	Value	Help
   1	Editing Enabled	Off	form pg ID=6 edits cust records. Examples of editing rec. in interactive grid see later in this text
   2	Source Type	Table	Tbl data to populate this interactive grid
   2	Table/View Owner		accept displayed value of Oracle schema (POSSYS) to which you are connected.
   2	Table/View Name drop-down list (from selected schema)	DEMO_PRODUCT_INFO (table)	whose data will be displayed in IG. You can select only one table from provided list.

   Click arrow icon next to the Column section to see table columns - all columns from that table are selected (moved to the right pane in the Columns section). Columns we want to show in IG : ...

   Click Create button to finish report page creation process.

   Click Application eg 145615(=appID) breadcrumb at top-left to leave Page Designer interface

Create Page ID=6 to carry form "CrUD Products"

1. Click Create Page button -> select "Form" option -> click another Form option on next wizard screen -> creates form page based on DB table.

   	Property	Value	Help
   1	Page Number	6	Page to carry form to CrUD Products
   2	Page Name	Product Details
   3	Page Mode	Modal Dialog	stand-alone page, appears on top of calling page
   4	Breadcrumb	Breadcrumb
   5	Parent Entry	Products (Page 3)	called from Page 3
   6	Entry Name	Product Details

2. On Navigation Menu screen, set "Navigation Preference" = Identify an existing navigation menu entry for this page, set "Existing Navigation Menu Entry" = Setup, and click Next.

3. On Source screen, set following properties and click Next.

   	Property	Value	Help
   1	Data Source	Local DB
   2	Source Type	Table
   2	Table/View Owner		accept displayed value of Oracle schema (POSSYS) to which you are connected.
   2	Table/View Name	DEMO_PRODUCT_INFO (table)

4. This time, select all columns from DEMO_PRODUCT_INFO tbl to display all of them in the input form (Page 6) to populate backend DB tbl. For "Primary Key Type", select second option Select Primary Key Column(s). Then, set first "Primary Key Column" attribute to PRODUCT_ID. Click "Create" button to complete form page creation process.

   Forms support up to 2 cols in PK. For tbls using PK with more than two columns, ROWID option should be used. For further details, see Chapter 2.

5. Access main App Builder interface by clicking application ID breadcrumb to see two new pages (Customers and Customer Details) with their respective page numbers. Click Products Details (Page 6) to open its definitions in Page Designer.

   Expand Pre-Rendering node and rename process Initialize form Products Details as "Initialize Products Details".

   Click Processing tab and rename process Process form Products Details to "Process Products Data". NOTE: If you see a different process name, then there is nothing to worry about as it sometimes happens due to change in APEX version.

   APEX is a low-code app dev. platform. Two pages you just created have everything you need to view and manipulate data. Products Page 2 contains IG in which you can view all customers data.

Click Product Details Page 6 to open it in Page Designer. On Rendering tab, expand Pre-Rendering node. Here, you will see an auto-generated process named Initialize Products Details of Form Initialization type. This Process is responsible to initialize form region items. Initialization can either be fetching data from region source, using PK value(s) or simple initialization of form region items. Process fetches and displays data in page items when you select product by clicking corresponding edit icon on the reports page and it initializes page items when you create a new product record.

Product Details region is a Form type region, which connects to local DB and fetches data from DEMO_PRODUCT_INFO table into relevant page items listed under Items node. Same page items are used to receive user input when new customer record is created.

In Buttons section, you will see a bunch of auto-generated buttons (Cancel, Delete, Save, and Create). DB Action property of these buttons specify function each button performs. When you click button (eg CREATE), corresponding DB action is submitted to process named Process Customer Data, which resides under Processing tab. This process is of Automatic Row Processing (DML) type and performs CrUD action on form region - Product Details region in current scenario.

6.3 Modify Main page of module Products Page ID=3 tbl

Holds interactive grid generated by wizard with some default data source values.

6.3.1 Modify region Products Properties

1. click region Products of page 3 -> change Type "Table / View" to SQL Query

2. In SQL Query, replace existing SELECT statement with following to select (same in pge 6 crUD form) :

   select PRODUCT_ID,  PRODUCT_NAME ,  PRODUCT_DESCRIPTION,  CATEGORY,  PRODUCT_AVAIL
     , LIST_PRICE,  UNITS,         SALES,                CUSTOMERS, LAST_DATE_SOLD
     , IMG,         ICON_LINK,     DETAIL_IMG,  DETAIL_IMG_NO_STYLE
   from (
     select p.product_id, p.product_name, p.product_description, p.category
     , decode(p.product_avail, 'Y','Yes','N','No') product_avail
     , p.list_price
     , (select sum(quantity) from demo_order_items where product_id = p.product_id) units
     , (
         select sum(quantity * p.list_price) 
         from demo_order_items where product_id = p.product_id
     ) sales
     , (
         select count(o.customer_id) from demo_orders o, demo_order_items t 
         where o.order_id = t.order_id and t.product_id = p.product_id group by p.product_id
     ) customers
     , (
         select max(o.order_timestamp) od 
         from demo_orders o, demo_order_items i 
         where o.order_id = i.order_id and i.product_id = p.product_id
     ) last_date_sold
     , p.product_id img
     , apex_util.prepare_url(p_url=>'f?p='||:app_id||':6:'||:app_session||'::::P6_PRODUCT_ID:'||p.product_id) icon_link
     , decode( nvl(dbms_lob.getlength(p.product_image),0)
         ,0,null,
         '<img alt="'||p.product_name||'" title="'||p.product_name
         ||'" style="border: 4px solid #CCC; -moz-border-radius: 4px; -webkit-border-radius: 4px;" '
         ||'src="'||apex_util.get_blob_file_src('P6_PRODUCT_IMAGE',p.product_id)||'" height="75" width="75" />'
     ) detail_img
     , decode( nvl(dbms_lob.getlength(p.product_image),0)
        ,0,null,
        apex_util.get_blob_file_src('P6_PRODUCT_IMAGE',p.product_id)
     ) detail_img_no_style
     from  demo_product_info p
   )
   

   p.product_id is img column in query above

   "icon_link" to Product Details Page 6 (read_byprodID)

   is formed using PREPARE_URL function which is :

   1. used to form links
   2. is part of APEX_UTIL package
   3. returns f?p=URL. p_url is VARCHAR2 parameter passed on to this fn
      apex_util.prepare_url(p_url=>'f?p='||:app_id||':6:'||:app_session||'::::P6_PRODUCT_ID:'||p.product_id) icon_link

   "detail_img" (and detail_img_no_style)

   holds products img. HTML img tag is used to display images of products in conjunction with built-in APEX function APEX_UTIL.GET_BLOB_FILE_SRC - which enables us to format img display with height and width properties. Image is styled using CSS inline styling method. getlength fn of dbms_lob package (dbms_lob.getlength) is used to estimate BLOB column size in table to facilitate inclusion of download link in report. If BLOB column size length is 0, BLOB is NULL and no download link is displayed.

3. Products region -> Content Body -> Expand Columns node and set meaningful column headings as follows:
   Product, Description, Category, Available, Price, Units, Sales, Customers, Last Sold, Image, Icon Link, Image Detail, and Detail Image No Style

4. Modify following columns. We can use Ctrl+click or Shift+click to select multiple cols to change Type properties at once.

   Column : Property = Value...Help

   1. PRODUCT_ID : Type = Hidden Column...hidden to make it invisible at runtime
   2. IMG : Type = Hidden Column
   3. ICON_LINK : Type = Hidden Column
   4. DETAIL_IMG : Escape special characters (under Security) = Off (otherwise image will not appear)...Each report column IN IR (not in IG) !! has property Escape special characters by default set to Yes - prevents Cross-Site Scripting (XSS) attacks and selecting No renders HTML tags stored in page item or in entries of list of value.
   5. DETAIL_IMG_NO_STYLE : Type = Hidden Column

5. By selecting Product Name column in Link Text attribute you specify this report column to appear as a link. You created a similar kind of link to call Customer Details page.

   Click PRODUCT_NAME column to transform it into a link

   1. In Interactive Reports, you forward a value to target page using special substitution strings (enclosed in # symbols)
   2. as compared to &Item. notation (for example, &CUSTOMER_ID.), which you use in the Interactive Grid - see Chapter 5 Section 5.3.1 Step 10.

   Property Value

   1. Click Link -> "Target" attribute
   2. In opened dialog : Type = Page In this application, Page = 6, Name = P6_PRODUCT_ID, Value = #PRODUCT_ID#
   3. Link Text = #PRODUCT_NAME# not &PRODUCT_NAME.

6. If you save and run report page at this stage, you will see an EDIT column (represented with a pencil icon), which leads to details page. Since we have already created a link (on the Product Name column), we will eliminate this column so :

   Under Products region, click its Attributes node, and set in Properties pane "Link" Column = Exclude Link Column. Figure 6-3

7. In same Attributes node, scroll down to Icon View section and set following properties.

   By default, most interactive reports display as a report. You can optionally display columns as icons. When configured, an icon (View Icons) appears on Search bar. To use this view, you must specify columns to identify icon, label, and target (that is link). As a best practice Type attribute of these columns is set to hidden (as you did in step 4), because they are typically not useful for end users. Image Attributes property will style height and width of images.

   Property Value

   1. Show = On
   2. Columns Per Row = 5 (to display 5 images on a single row in View Icons interface)
   3. Link Column = ICON_LINK
   4. Image Source Column = DETAIL_IMG_NO_STYLE
   5. Label Column = PRODUCT_NAME
   6. Image Attributes = width="75" height="75"

8. Section "Detail View" under Icon View section -> turn on Show property. When configured, View Details icon appears on Search bar.

9. In Before Rows attribute of Detail View you enter HTML code to be displayed before report rows. Eg you can use TABLE element to put DB content in row/column format. Besides adding HTML code, styling information can also be incorporated using this attribute. Code below uses custom CSS rules to override default Oracle APEX Interactive Report (apexir) styles.

   <style>
   table.apexir_WORKSHEET_CUSTOM { 
      border: none !important; 
      box-shadow: none; 
      -moz-box-shadow: none; 
      -webkit-box-shadow: none;}
   
   .apexir_WORKSHEET_DATA td {
    border-bottom: none !important;}
   
   table.reportDetail td {
      padding: 2px 4px !important;
      border: none !important;
      font: 11px/16px Arial, sans-serif;}
   
   table.reportDetail td.separator {
      background: #F0F0F0 !important;
      padding: 0 !important;
      height: 1px !important;
      padding: 0;
      line-height: 2px !important;
      overflow: hidden;}
   
   table.reportDetail td h1 {margin: 0 !important}
   
   table.reportDetail td img {
      margin-top: 8px; 
      border: 4px solid #CCC; 
      -moz-border-radius: 4px; 
      -webkit-border-radius: 4px;}
   </style>
   <table class="reportDetail">

   NOTE: Remember that all APEX pages are HTML pages controlled by HTML properties and cascading style sheet (CSS) settings. When you create an interactive report, Oracle APEX renders it based on CSS classes associated with current theme. Each APEX interactive report component has a CSS style definition that may be changed by applying standard CSS techniques to override defaults. CSS changes may be applied to :

   1. single interactive report
   2. page template to effect changes across several interactive reports
   3. all page templates of a theme to enforce a common look and feel for all reports in an application

In the current step, you are changing report appearance by overriding built-in styles for table and subordinate elements.

10. In "For Each Row" (post_query), enter following code applied to each record. In every td element you are referencing interactive report columns and labels with substitution string (#) and are styling each record using inline CSS method.

    You used substitution string to reference table column names and labels of page items as #PRODUCT_NAME# and #CATEGORY_LABEL#.

    <tr><td rowspan="5" valign="top">
       <img width="75" height="75" src="#DETAIL_IMG_NO_STYLE#"></td>
       <td colspan="6"><h1><a href="#ICON_LINK#"><strong>#PRODUCT_NAME#</strong></a></h1></td>
    </tr>
    
    <tr>
       <td><strong>#CATEGORY_LABEL#:</strong></td><td>#CATEGORY#</td>
       <td><strong>#PRODUCT_AVAIL_LABEL#:</strong></td><td>#PRODUCT_AVAIL#   </td>
       <td><strong>#LAST_DATE_SOLD_LABEL#:</strong></td><td>#LAST_DATE_SOLD#</td>
    </tr>
    
    <tr>
       <td align="left"><strong>#PRODUCT_DESCRIPTION_LABEL#:</strong></td>
       <td colspan="5">#PRODUCT_DESCRIPTION#</td>
    </tr>
    
    <tr>
       <td style="padding-bottom: 0px;"><strong>#LIST_PRICE_LABEL#</strong></td>
       <td style="padding-bottom: 0px;"><strong>#UNITS_LABEL#</strong></td>
       <td style="padding-bottom: 0px;"><strong>#SALES_LABEL#</strong></td>
       <td style="padding-bottom: 0px;"><strong>#CUSTOMERS_LABEL#</strong></td>
    </tr>
    
    <tr>
       <td style="padding-top: 0px;">#LIST_PRICE#</td>
       <td style="padding-top: 0px;">#UNITS#</td>
       <td style="padding-top: 0px;">#SALES#</td>
       <td style="padding-top: 0px;">#CUSTOMERS#</td>
    </tr>
    
    <tr>
       <td colspan="7" class="separator"></td>
    </tr>

11. In After Rows, enter </table> to complete HTML code = HTML to be displayed after report rows. It is closing table tag to end HTML table.

12. Save and run page 3 products tbl from Manage Products option in Setup menu

    1. Click View Reports icon (1st left to Actions list)
    2. Note that Image Detail column is blank at the moment, because we do not have any product image in tbl. This is column which will hold images of products.

    Add (upload) image of product

    1. Click some link in Product name column to add image of this product.
    2. On Product Details page, ???click "folder icon" representing Product Img field at page bottom. This will bring up Open dialog box.
    3. Go to BookCode\Chapter6 folder and select 1_AirMax2090.png file, and click Open
    4. image name will be displayed in Product Image field.
    5. Click Apply Changes button on Product Details form to save image. The image will appear on the interactive report page. Repeat this step to add images of remaining products. Click View Icons (3rd left from "Actions list") and View Details (1st left from "Actions list") options on the interactive report toolbar and see output (Fig. 6-4).

13. Filter and order cols : Click View Reports icon. Click Actions menu in interactive report and select Columns. Make sure all columns (except Description and Last Sold) appear in Display in Report section. You can use arrow icons to arrange columns in a desired order. Click Apply button. Only columns you selected will appear in interactive report.

14. Click Actions menu again and select Save Report (under Report). From Save drop-down list, select As Default Report Settings. Set Default Report Type to Primary and click Apply. After modifying an interactive report you must save it using this procedure, otherwise you’ll lose the applied settings when you subsequently view this report. Developers can save two types of default interactive report: primary and alternative. Both reports display on Report list on Search bar. Primary default report (you just saved) cannot be renamed or deleted.

In next steps we change default data source values and produce custom output using SQL query.

6.3.1 Modify Products Region Prop.

Cols hdr, order, filter and cust name as link

1. In App Builder, click Customers page (Page 2) to open it in the Page Designer for modification.

   Cols hdr, cols order, cols filter in tbl Products

2. Click Customers region under Content Body node. Standard method to modify properties of page component is to click corresponding node. This action refreshes Properties section (located to your right) with properties of selected page component for alteration.

3. Change Type (under Source section) = SQL Query to see default query generated for interactive grid.

   select ROWID,    CUSTOMER_ID,    CUST_FIRST_NAME,    CUST_LAST_NAME,    CUST_STREET_ADDRESS1,
   CUST_STREET_ADDRESS2,    CUST_CITY,    CUST_STATE,    CUST_POSTAL_CODE,    CUST_EMAIL,
   PHONE_NUMBER1,    PHONE_NUMBER2,    URL,    CREDIT_LIMIT,    TAGS
   from DEMO_CUSTOMERS

   Enter following SQL statement in SQL Query text area, replacing existing one:

   SELECT customer_id, cust_last_name || ', ' || cust_first_name customer_name
   , CUST_STREET_ADDRESS1||decode(CUST_STREET_ADDRESS2, null, null, ', '||CUST_STREET_ADDRESS2) customer_address
   , cust_city, cust_state, cust_postal_code
   FROM demo_customers

   Decode Syntax: decode( expression , search , result [, search , result]... [, default] ) If default is omitted, Oracle returns null.

4. Expand Customers region -> expand Columns node. Click column eg CUSTOMER_NAME and change its heading (under Heading section in Properties pane) to Name. Change headings of other cols : Address, City, State, and Postal Code

5. In Columns node, click the CUSTOMER_ID column, and change it Type property from Number Field to Hidden to hide col at runtime. PK cols are added to DB tbls to enforce data integrity and are not displayed in apps. This is why such column's Type property is set to hidden to make them invisible at runtime.

6. Run page. Click Actions menu (A) - select Columns. When you click column in left pane, right pane (D) shows its name and width. You can input a numeric value to change width of col. Using arrow icons (E), arrange selected columns in following order: Name, Address, City, State, and Postal Code

7. Click Save button in the Columns window to apply the changes.

8. After you modify interactive grid in runtime save it : Click Actions menu -> Save from Report option, otherwise you’ll lose the applied settings when you access it later.

9. Click Edit Page2 (F) in the Developer Toolbar at the bottom of page to access Page Designer.

   Cust name as link in tbl Customers

10. Click CUSTOMER_NAME column to set properties: transforming cust name col into link that will lead to Page 7 cust. form.

    When you click customer's name in interactive grid report at runtime:

    1. ID of that cust is stored in substitution string (&CUSTOMER_ID.) (G)
    2. and is forwarded to corresponding page item (P7_CUSTOMER_ID) (H) on Page 7, which displays cust profile using this ID.

    You created similar kind of link in Chapter 4 for region "Sales for this Month".

    Scroll down to Link section and click "No Link Defined" under Target to bring up Link Builder dialog box -> set link properties :

    	Property	Value	Help
    1	Type	Page in this app
    2	Page	7
    3	Name	P7_CUSTOMER_ID	This value refers to an item on Page 7 that will be populated with the value held in #ID# . It is forwarded to Page 7 from the Home page to display selected customer profile.
    4	Value	&CUSTOMER_ID.	references third column in above SELECT query. Standard procedure in APEX to refer to a column value is to enclose it between # symbols. To refer page item we use substitution strings.
    5	Clear Cache	7

    Use LOVs (I) in Set Items section to select item name (3) and value (4).

11. Close Link Builder dialog box using OK button.

12. Save and run page. Cust Name col will now appear as link. Click any customer name to see details on Page 7, which pops up on top of Page 2.

5.3.2 Button "Create" in tbl Customers

To call Page 7 with a blank form from Page 2 - Customers :

1. Rendering tab to your left click Customers interactive grid region -> set its Template property to Standard (was Interactiv). Selected template will place title and border for interactive grid region.

   Right-click Customers region and from context menu select Create Button. Button named New will be added. Set properties for new button :

   	Property	Value	Help
   1	Button Name	CREATE
   2	Label	Create Customer	appears as tooltip when you move over button at run-time
   3	Button Position	Copy	over dozen values, try other options as well to observe different positions
   4	Hot	On	renders the button in dark color
   5	Action	Redirect to Page in this App	(under Behavior) - Creates link to call Page 7.
   6	Target	Type = Page in this app Page = 7 Clear Cache =7	Creates link to call Page 7. Clear Cache prop. makes all items on target pg 7 blank

2. Save and run Page 2, which should look similar to Figure 5-1.

3. Click Create Customer button to call Customer Details Page 7 on top of calling page as a modal dialog.

5.3.3 Button "Call PHP page" to Call PHP script - report from APEX

https://www.youtube.com/watch?v=WLE9_1I_nA0

Like 5.3.2, properties are :

Open in new tab : ?

5.4 Modify properties of crUD form Customer Details - Page 7

With Page 7 cust. form being displayed in browser, click Edit Page 7 in Developer Toolbar at pge bottom to call this pge in PageDesigner.

5.4.1 Modify Page Items Properties

Like region placement in 12 cols grid layout, done for six Home pge regions, pge items can also be placed using APEX's grid layout, as follows. Width property sets items width on page. If Value Required is set to Yes and page item is visible, APEX automatically performs NOT NULL validation when page is submitted and you are asked to input a value for field. If you set Value Required to No, no validation is performed and a NULL value is accepted. Value Required attribute works in conjunction with Template = Required to signify (označi) mandatory items visually.

Page Id=7 Customer Details (profile) items properties and values

Page flds are in 2 columns, only "Tags" fld spans both cols. Save your changes and call this Page 7 by clicking any customer's name on Page 2. It should come up with selected customer profile (Figure 5-6 Customer Details Page). Click each item under Items node and apply following properties.

1. P7_CUST_FIRST_NAME Label=First Name.....Sequence=20.....Start New Row=On.....Column=Automatic.....Column Span=Automatic .....Template=Required.....Label Column Span=2 (becomes visible in Layout section only after setting Template property) .....Width=50.....Value Required=On
2. P7_CUST_LAST_NAME Label=Last Name.....Sequence=30.....Start New Row=Off.....Column=Automatic .....New Column=On.....Column Span=Automatic.....Template=Required.....Label Column Span=2 .....Width=50.....Value Required=On
3. P7_CUST_STREET_ADDRESS1 Label=Street Address.....Sequence=40.....Start New Row=On.....Column=Automatic .....Column Span=Automatic.....Template=Optional.....Label Column Span=2 .....Width=50.....Value Required=Off
4. P7_CUST_STREET_ADDRESS2 Label=Line 2.....Sequence=50.....Start New Row=Off.....Column=Automatic .....New Column=On.....Column Span=Automatic.....Template=Optional.....Label Column Span=2 .....Width=50.....Value Required=Off
5. P7_CUST_CITY Label=City.....Sequence=60.....Start New Row=On.....Column=Automatic .....Column Span=6.....Template=Optional.....Label Column Span=2 .....Width=50.....Value Required=Off
6. See 5.4.2 Change cust. column "P7_CUST_STATE" to hold predefined LOV! States list
7. P7_CUST_POSTAL_CODE Label=Zip Code.....Sequence=80.....Start New Row=On.....Column=Automatic .....Column Span=6.....Template=Required.....Label Column Span=2 .....Width=8.....Value Required=On
8. P7_CREDIT_LIMIT Label=Credit Limit.....Sequence=90.....Start New Row=Off.....Column=Automatic .....New Column=On.....Column Span=Automatic.....Template=Required.....Label Column Span=2 .....Width=8.....Value Required=On
9. P7_PHONE_NUMBER1 Label=Phone Number.....Sequence=100.....Start New Row=On.....Column=Automatic.....Column Span=Automatic .....Template=Optional.....Label Column Span=2.....Width=12.....Value Required=Off
10. P7_PHONE_NUMBER2 Label=Alternate No.....Sequence=110.....Start New Row=Off.....Column=Automatic .....New Column=On.....Column Span=Automatic.....Template=Optional.....Label Column Span=2 .....Width=12.....Value Required=Off
11. P7_CUST_EMAIL Label=Email.....Sequence=120.....Start New Row=On.....Column=Automatic .....Column Span=Automatic.....Template=Required.....Label Column Span=2 .....Width=50.....Value Required=On
12. P7_URL Type=Text Field.....Label=URL.....Sequence=130.....Start New Row=Off.....Column=Automatic .....New Column=On.....Column Span=Automatic.....Template=Optional.....Label Column Span=2 .....Width=50.....Value Required=Off
13. P7_TAGS Type=Textarea.....Label=Tags.....Sequence=140.....Start New Row=On.....Column=Automatic .....Column Span=Automatic.....Template=Optional.....Label Column Span=2 .....Width=100.....Value Required=Off

5.4.2 Customer's page column "P7_CUST_STATE"

to hold app-shared dynamic LOV! States list. app-shared means defined in app shared componebts in one place and reusable in all app modules pages.

1. In Shared Components to fetch State names from DEMO_STATES table

1. Shared Components -> Other Components section -> "Lists of Values" APEX page -> Click Create button
2. Select From Scratch option and click Next
3. Enter States in Name box, select Dynamic for its type, and click Next
4. On List of Values Source screen, select SQL Query for Source Type, and enter following query in Enter SQL SELECT statement box. Click Next

   both VARCHAR2 30 :
   select state_name display_value, st return_value from demo_states order by 1
   --select state_name display_value, state_id return_value from demo_states order by 1

5. On final Column Mappings screen, select RETURN_VALUE for Return Column, DISPLAY_VALUE for Display Column and click Create button to create the LOV.

2. P7_CUST_STATE prop.

Label=State.....Sequence=70.....Start New Row=Off.....Column=Automatic .....New Column=On.....Column Span=Automatic.....Template=Required.....Label Column Span=2 .....Width=make it null (this item will be transformed into a select list).....Value Required=On

3. Type Text Field change to -> Select List and Attach STATES LOV! to it (was created in 3.4.3)

1. In Page Designer interface, click P7_CUST_STATE item.
2. Change its Type property from Text Field to Select List.
3. Set Type (under List of Values) to Shared Components and select STATES for LOV. This step attaches States LOV to page item.
4. Turn off "Display Extra Values" property. An item may have a session state value, which does not occur in its LOV definition. Select whether this LOV should display this extra session state value. If you choose not to display this extra session state value and there is no matching value in list of values definition, first value will be selected value. Eg while creating new customer record you will see - Choose a State - as the first value in the list. This value is added to the list in following steps.
5. Turn on Display Null Value property, which is default. Display Null Value property makes it possible for a user to choose a null value instead of one of the list items. If you set this property to Yes, additional properties appear on the screen for you to specify display value for this new entry. Eg "Choose State".
6. Enter - Choose State - in Null Display Value. This step, along with the previous one, generates a placeholder that appears on top of the LOV asking for a selection whenever you call this page to create a new customer record.
7. Save your work.

5.4.3 Apply Input Mask to Items

Modify the two phone number items in cust. form pgID=7 and set their Value Placeholder property (under Appearance) to 999-999-9999. When a new customer record is added, this placeholder is shown in the two phone number items to receive input in the specified format. As you type in values, the placeholders will be replaced by the numbers entered.

5.4.4 Create Validation 1 (logic control of user input) - Check Customer field "P7_CREDIT_LIMIT"

Field "Credit Limit" is used to assign a credit cap to each customer with a figure of $5,000. If you enter a value more than the assigned cap, you'll be prevented by presenting an appropriate message. NOTE: Validation error messages display when :

1. validation fails the equality test
2. or evaluates to FALSE
3. or non-empty text string is returned.

In left pane of Page 7 cust.form : click Processing icon (was tab) -> right-click Validating node -> Create Validation from context menu (Figure 5-7) -> Set following properties for this new validation :

IR (Interactive report) (see 2.3.3) to display a list of products instead of an interactive grid.

2. 1. Click Create Page button in App Builder interface -> Sales web App 80858

3. Click Form option, followed by Report with Form option. These two selections will create a report page (Figure 6-1) to display all product records from the table (selected in step 5) and a form page (Figure 6-6) to add, modify, and delete products.

4. On the Page Attributes wizard screen, set the following properties and click Next. The form page (Page 6) is named Product Details and it will be linked to the report page (Products - Page 3).

   Property Value

   1. Report Type = Interactive Report

   2. Report Page Number = 3

   3. Report Page Name = Products

   4. Form Page Number = 6

   5. Form Page Name = Product Details

   6. Form Page Mode = Modal Dialog

   7. Breadcrumb = Breadcrumb

   8. Parent Entry = Home (Page 1)

   9. Entry Name = Products

5. On Navigation Menu screen, set Navigation Preference to "Identify an existing navigation menu entry for this page", set "Existing Navigation Menu Entry" to Setup (šifrarnici), and click Next. This step will highlight Setup entry in main navigation menu when you access products setup.

6. On Data Source screen, select Table for Source Type, accept default schema in Table/View Owner, and select "DEMO_PRODUCT_INFO (table)" for Table/View Name. Columns from the selected table to be shown in the interactive report will appear. Accept all table columns and click Next.

7. On Form Page screen, add all columns (A) from the DEMO_PRODUCT_INFO table to Page 6, except : MIMETYPE, FILENAME, and IMAGE_LAST_UPDATE (B). These three columns are used in background to handle images of products.

   For Primary Key Type, choose Select "Primary Key Column(s)" (C). Set "PK Column 1" attribute to PRODUCT_ID (Number) (D). PRODUCT_ID is populated behind the scene using DB sequence object (DEMO_PRODUCT_INFO_SEQ) via BI_DEMO_PRODUCT_INFO trigger when you add a new product - see the two objects by accessing Object Browser.

Click Create button to complete wizard.This time, wizard creates two pages (3 and 6) as an initial structure for this module.

6.3 Modify Products tbl Page 3

6.3.1 Modify Region Properties

1. Click region named Report 1 and set its Title to Products

2. In SQL Query, replace existing SELECT statement with following to select :
   Was :

   select PRODUCT_ID,        PRODUCT_NAME,        PRODUCT_DESCRIPTION,        CATEGORY,        PRODUCT_AVAIL,
        LIST_PRICE,    PRODUCT_IMAGE,        MIMETYPE,        FILENAME,        IMAGE_LAST_UPDATE
   from DEMO_PRODUCT_INFO

   Demo app :

   PRODUCT_ID   PRODUCT_NAME    PRODUCT_DESCRIPTION CATEGORY    PRODUCT_AVAIL   
   LIST_PRICE   UNITS   SALES   CUSTOMERS   LAST_DATE_SOLD
   IMG  ICON_LINK   DETAIL_IMG  DETAIL_IMG_NO_STYLE TAGS
   
   select p.product_id,
      p.product_name, 
      p.product_description, 
      p.category, 
      decode(p.product_avail, 'Y','Yes','N','No') product_avail,
      p.list_price,
      (select sum(quantity) from demo_order_items where product_id = p.product_id) units,
      (select sum(quantity * p.list_price) from demo_order_items where product_id = p.product_id) sales,       
      (select count(o.customer_id) from demo_orders o, demo_order_items t where o.order_id = t.order_id and t.product_id = p.product_id group by p.product_id) customers,
      (select max(o.order_timestamp) od from demo_orders o, demo_order_items i where o.order_id = i.order_id and i.product_id = p.product_id) last_date_sold,
      p.product_id img,
      apex_util.prepare_url(p_url=>'f?p='||:app_id||':6:'||:app_session||'::::P6_PRODUCT_ID,P6_BRANCH:'||p.product_id||','||3,p_dialog=> 'null') icon_link,
      decode(nvl(dbms_lob.getlength(p.product_image),0),0,null,
      '<img alt="'||apex_escape.html_attribute(p.product_name)||'" title="'||apex_escape.html_attribute(p.product_name)
             ||'" style="border: 4px solid #CCC; -moz-border-radius: 4px; -webkit-border-radius: 4px;" '
             ||'src="'||apex_util.get_blob_file_src('P6_PRODUCT_IMAGE',p.product_id)||'" height="75" width="75" />') detail_img,
      decode(nvl(dbms_lob.getlength(p.product_image),0),0,null,
      apex_util.get_blob_file_src('P6_PRODUCT_IMAGE',p.product_id))
      detail_img_no_style,
      tags
   from demo_product_info p

   select PRODUCT_ID,  PRODUCT_NAME ,  PRODUCT_DESCRIPTION,  CATEGORY,  PRODUCT_AVAIL
     , LIST_PRICE,  UNITS,         SALES,                CUSTOMERS, LAST_DATE_SOLD
     , IMG,         ICON_LINK,     DETAIL_IMG,  DETAIL_IMG_NO_STYLE
   from (
     select p.product_id, p.product_name, p.product_description, p.category
     , decode(p.product_avail, 'Y','Yes','N','No') product_avail
     , p.list_price
     , (select sum(quantity) from demo_order_items where product_id = p.product_id) units
     , (
         select sum(quantity * p.list_price) 
         from demo_order_items where product_id = p.product_id
     ) sales
     , (
         select count(o.customer_id) from demo_orders o, demo_order_items t 
         where o.order_id = t.order_id and t.product_id = p.product_id group by p.product_id
     ) customers
     , (
         select max(o.order_timestamp) od 
         from demo_orders o, demo_order_items i 
         where o.order_id = i.order_id and i.product_id = p.product_id
     ) last_date_sold
     , p.product_id img
     , apex_util.prepare_url(p_url=>'f?p='||:app_id||':6:'||:app_session||'::::P6_PRODUCT_ID:'||p.product_id) icon_link
     , decode( nvl(dbms_lob.getlength(p.product_image),0)
         ,0,null,
         '<img alt="'||p.product_name||'" title="'||p.product_name
         ||'" style="border: 4px solid #CCC; -moz-border-radius: 4px; -webkit-border-radius: 4px;" '
         ||'src="'||apex_util.get_blob_file_src('P6_PRODUCT_IMAGE',p.product_id)||'" height="75" width="75" />'
     ) detail_img
     , decode( nvl(dbms_lob.getlength(p.product_image),0)
        ,0,null,
        apex_util.get_blob_file_src('P6_PRODUCT_IMAGE',p.product_id)
     ) detail_img_no_style
     from  demo_product_info p
   )
   

   p.product_id is img column in query above

   "icon_link" to Product Details Page 6 (read_byprodID)

   is formed using PREPARE_URL function which is :

   1. used to form links
   2. is part of APEX_UTIL package
   3. returns f?p=URL. p_url is VARCHAR2 parameter passed on to this fn
      apex_util.prepare_url(p_url=>'f?p='||:app_id||':6:'||:app_session||'::::P6_PRODUCT_ID:'||p.product_id) icon_link

   "detail_img" (and detail_img_no_style)

   holds products img. HTML img tag is used to display images of products in conjunction with built-in APEX function APEX_UTIL.GET_BLOB_FILE_SRC - which enables us to format img display with height and width properties. Image is styled using CSS inline styling method. getlength fn of dbms_lob package (dbms_lob.getlength) is used to estimate BLOB column size in table to facilitate inclusion of download link in report. If BLOB column size length is 0, BLOB is NULL and no download link is displayed.

3. Expand Columns node (under Content Body | Products region) and set meaningful column headings as follows:
   Product, Description, Category, Available, Price, Units, Sales, Customers, Last Sold, Image, Icon Link, Image Detail, and Detail Image No Style

4. Modify following columns using specified properties.

   We can use Ctrl+click or Shift+click to select multiple cols to change Type properties at once.

   Column : Property = Value...Help

   1. PRODUCT_ID : Type = Hidden Column...hidden to make it invisible at runtime
   2. IMG : Type = Hidden Column
   3. ICON_LINK : Type = Hidden Column
   4. DETAIL_IMG : Escape special characters (under Security) = Off (otherwise image will not appear)...Each report column has property Escape special characters by default set to Yes - prevents Cross-Site Scripting (XSS) attacks and selecting No renders HTML tags stored in page item or in entries of list of value.
   5. DETAIL_IMG_NO_STYLE : Type = Hidden Column

5. Click PRODUCT_NAME column to transform it into a link. By selecting Product Name column in Link Text attribute you specify this report column to appear as a link. You created a similar kind of link in previous chapter to call Customer Details page.

   1. In Interactive Reports, you forward a value to target page using special substitution strings (enclosed in # symbols)
   2. as compared to &Item. notation (for example, &CUSTOMER_ID.), which you use in the Interactive Grid - see Chapter 5 Section 5.3.1 Step 10.

   Property Value

   1. Type = Link
   2. Target (under Link) : Type = Page In this application, Page = 6, Name = P6_PRODUCT_ID, Value = #PRODUCT_ID#
   3. Link Text = #PRODUCT_NAME#

6. If you save and run report page at this stage, you will see an EDIT column (represented with a pencil icon), which leads to details page. Since we have already created a link (on the Product Name column), we will eliminate this column so :

   Under Products region, click its Attributes node, and set in Properties pane "Link" Column = Exclude Link Column. Figure 6-3

7. In same Attributes node, scroll down to Icon View section and set following properties.

   By default, most interactive reports display as a report. You can optionally display columns as icons. When configured, an icon (View Icons) appears on Search bar. To use this view, you must specify columns to identify icon, label, and target (that is link). As a best practice Type attribute of these columns is set to hidden (as you did in step 4), because they are typically not useful for end users. Image Attributes property will style height and width of images.

   Property Value

   1. Show = On
   2. Columns Per Row = 5 (to display 5 images on a single row in View Icons interface)
   3. Link Column = ICON_LINK
   4. Image Source Column = DETAIL_IMG_NO_STYLE
   5. Label Column = PRODUCT_NAME
   6. Image Attributes = width="75" height="75"

8. Section "Detail View" under Icon View section -> turn on Show property. When configured, View Details icon appears on Search bar.

9. In Before Rows attribute of Detail View you enter HTML code to be displayed before report rows. Eg you can use TABLE element to put DB content in row/column format. Besides adding HTML code, styling information can also be incorporated using this attribute. Code below uses custom CSS rules to override default Oracle APEX Interactive Report (apexir) styles.

   <style>
   table.apexir_WORKSHEET_CUSTOM { 
      border: none !important; 
      box-shadow: none; 
      -moz-box-shadow: none; 
      -webkit-box-shadow: none;}
   
   .apexir_WORKSHEET_DATA td {
    border-bottom: none !important;}
   
   table.reportDetail td {
      padding: 2px 4px !important;
      border: none !important;
      font: 11px/16px Arial, sans-serif;}
   
   table.reportDetail td.separator {
      background: #F0F0F0 !important;
      padding: 0 !important;
      height: 1px !important;
      padding: 0;
      line-height: 2px !important;
      overflow: hidden;}
   
   table.reportDetail td h1 {margin: 0 !important}
   
   table.reportDetail td img {
      margin-top: 8px; 
      border: 4px solid #CCC; 
      -moz-border-radius: 4px; 
      -webkit-border-radius: 4px;}
   </style>
   <table class="reportDetail">

   NOTE: Remember that all APEX pages are HTML pages controlled by HTML properties and cascading style sheet (CSS) settings. When you create an interactive report, Oracle APEX renders it based on CSS classes associated with current theme. Each APEX interactive report component has a CSS style definition that may be changed by applying standard CSS techniques to override defaults. CSS changes may be applied to :

   1. single interactive report
   2. page template to effect changes across several interactive reports
   3. all page templates of a theme to enforce a common look and feel for all reports in an application

In the current step, you are changing report appearance by overriding built-in styles for table and subordinate elements.

10. In "For Each Row" (post_query), enter following code applied to each record. In every td element you are referencing interactive report columns and labels with substitution string (#) and are styling each record using inline CSS method.

    You used substitution string to reference table column names and labels of page items as #PRODUCT_NAME# and #CATEGORY_LABEL#.

    <tr><td rowspan="5" valign="top">
       <img width="75" height="75" src="#DETAIL_IMG_NO_STYLE#"></td>
       <td colspan="6"><h1><a href="#ICON_LINK#"><strong>#PRODUCT_NAME#</strong></a></h1></td>
    </tr>
    
    <tr>
       <td><strong>#CATEGORY_LABEL#:</strong></td><td>#CATEGORY#</td>
       <td><strong>#PRODUCT_AVAIL_LABEL#:</strong></td><td>#PRODUCT_AVAIL#   </td>
       <td><strong>#LAST_DATE_SOLD_LABEL#:</strong></td><td>#LAST_DATE_SOLD#</td>
    </tr>
    
    <tr>
       <td align="left"><strong>#PRODUCT_DESCRIPTION_LABEL#:</strong></td>
       <td colspan="5">#PRODUCT_DESCRIPTION#</td>
    </tr>
    
    <tr>
       <td style="padding-bottom: 0px;"><strong>#LIST_PRICE_LABEL#</strong></td>
       <td style="padding-bottom: 0px;"><strong>#UNITS_LABEL#</strong></td>
       <td style="padding-bottom: 0px;"><strong>#SALES_LABEL#</strong></td>
       <td style="padding-bottom: 0px;"><strong>#CUSTOMERS_LABEL#</strong></td>
    </tr>
    
    <tr>
       <td style="padding-top: 0px;">#LIST_PRICE#</td>
       <td style="padding-top: 0px;">#UNITS#</td>
       <td style="padding-top: 0px;">#SALES#</td>
       <td style="padding-top: 0px;">#CUSTOMERS#</td>
    </tr>
    
    <tr>
       <td colspan="7" class="separator"></td>
    </tr>

11. In After Rows, enter </table> to complete HTML code = HTML to be displayed after report rows. It is closing table tag to end HTML table.

12. Save and run page 3 products tbl from Manage Products option in Setup menu

    1. Click View Reports icon (1st left to Actions list)
    2. Note that Image Detail column is blank at the moment, because we do not have any product image in tbl. This is column which will hold images of products.

    Add (upload) image of product

    1. Click some link in Product name column to add image of this product.
    2. On Product Details page, ???click "folder icon" representing Product Img field at page bottom. This will bring up Open dialog box.
    3. Go to BookCode\Chapter6 folder and select 1_AirMax2090.png file, and click Open
    4. image name will be displayed in Product Image field.
    5. Click Apply Changes button on Product Details form to save image. The image will appear on the interactive report page. Repeat this step to add images of remaining products. Click View Icons (3rd left from "Actions list") and View Details (1st left from "Actions list") options on the interactive report toolbar and see output (Fig. 6-4).

13. Filter and order cols : Click View Reports icon. Click Actions menu in interactive report and select Columns. Make sure all columns (except Description and Last Sold) appear in Display in Report section. You can use arrow icons to arrange columns in a desired order. Click Apply button. Only columns you selected will appear in interactive report.

14. Click Actions menu again and select Save Report (under Report). From Save drop-down list, select As Default Report Settings. Set Default Report Type to Primary and click Apply. After modifying an interactive report you must save it using this procedure, otherwise you’ll lose the applied settings when you subsequently view this report. Developers can save two types of default interactive report: primary and alternative. Both reports display on Report list on Search bar. Primary default report (you just saved) cannot be renamed or deleted.

6.4 Modify Product Details (form) Page 6

Adjust dimension of Product Details page

Page Designer toolbar carries a section called Page Selector - fld : enter page number and click Go. Navigate to Next/prev Page (up/down arrow) - Figure 6-5. Using Page Selector call Page 6 in Page Designer. Click root node and set following properties.

Property Value

1. Title = Product Details
2. Width (under Dialog) = 900
3. Height = 620
4. Maximum Width = 1000
5. Img rule in inline CSS property (under CSS) to make products images responsive. ::before selector (used in second rule) will insert word 'Nike' before images of products - see Figure 6-6.

   img {
   width: 100%;
   max-width: 100%;
   height: auto;
   }
   .imgItem::before
   {
   content: 'Nike';
   position: absolute;
   top: 30px;
   left: 20px;color: #000;
   opacity: 0.1;
   font-size: 8em;
   font-weight: 800;
   }

6.4.1 Making Page Item Mandatory

P6_PRODUCT_NAME, P6_CATEGORY, P6_PRODUCT_AVAIL, and P6_LIST_PRICE

1. Click on P6_PRODUCT_NAME item, press and hold the Ctrl key , and then click the other items to select them all

   Property Value

   1. Template (under Appearance) Required
   2. Value Required (under Validation) On

2. Set Template property of P6_PRODUCT_DESCRIPTION and P6_PRODUCT_IMAGE page items to Optional.

6.4.2 Attach Categories LOV! to page item P6_CATEGORY

1. In Shared Components 3.4.1 LOV CATEGORIES (U, T) - STATIC to fetch Product categories from list

1. Shared Components (pyramid of 3) -> Other Components section -> Lists of Values APEX page

2. Click Create button (9 LOVs are created by APEX).

3. Select From Scratch option and click Next.

4. LOV Name = Categories, select LOV type = Static, click Next.

5. Fill in values as shown in following table. At runtime these entries will display in order they are entered. Return Value does not display, but is the value that is returned to APEX engine as user selection (user event).

   Sequence	Display val	Rerturn val (user event)	Help
   1	Mens	Mens	Men ?
   2	Womens	Womens	Women ?
   3	Accessories	Accessories	Kids ?

6. Click Create LOV button

2. P6_CATEGORY prop.

Label=Category.....Sequence=70.....Start New Row=On.....Column=Automatic .....New Column=On.....Column Span=Automatic.....Template=Required.....Label Column Span=2 .....Width=make it null (this item will be transformed into a select list).....Value Required=On

3. Type Text Field change to -> Select List and Attach Categories LOV! to it

Here we're going to use LOV Categories to which page item P6_CATEGORY will bound, to display predefined values of categories in Select List - see Manage Customers module to display STATES LOV.

1. Page Designer interface -> Items node under Product Details region click P6_CATEGORY item.
2. In Property Editor (right pane) change its Type property (under Identification) from Text Field to Select List.
3. Set Type (under List of Values) to Shared Components and select CATEGORIES for LOV. This step attaches Categories LOV to page item P6_CATEGORY.
4. Turn off "Display Extra Values" property
5. Turn off Display Null Value property
6. Save your work.

6.4.3 Attach LOV to Product Available Column

Next, you will change the Product Available field to a Switch comprising two options: On and Off. Just like the previous steps, here as well, you’re changing the item type from Text to Switch. At runtime, this item will show two options to specify whether the selected product is available or not. If you ignore this exercise and leave the item to its default type, users can enter whatever value they like, resulting in compromising application’s integrity. This is a good example to restrict users to select valid values. Select the P6_PRODUCT_AVAIL item and set the following properties. Note that the last two properties in the table sets Y (which stands for On) as the default value for this item.

Property Value

1. Type (under Identification) = Switch
2. Label = Product Available
3. Type (under Default) = Static
4. Static Value = Y

6.4.4 Handling Image (Handle Image Exercise A)

Modify following properties (in Settings section) for the P6_PRODUCT_IMAGE item to map table columns. This mapping is necessary to display product images on details form.

Property Value

1. MIME Type Column = MIMETYPE
2. Filename Column = FILENAME
3. BLOB Last Update Column = IMAGE_LAST_UPDATE

In the Settings section, the Storage Type attribute is set to BLOB column specified in item Source attribute by default. The Storage Type attribute specifies where the uploaded file should be stored at. It has two values: BLOB column specified in item source attribute. Stores the uploaded file in the table used by the "Automatic Row Processing (DML)" process and the column specified in the item sourceattribute. The column has to be of data type BLOB. Table APEX_APPLICATION_TEMP_FILE. Stores the uploaded file in a table named APEX_APPLICATION_TEMP_FILE.

6.4.5 Create Region - Product Image (Handle Image Exercise B)

To show images of selected products on Product Details page, we will create a Static Content sub-region. Note that this section will only create a blank region to hold an image. The image will be added to this region in a subsequent section. Right-click the Regions node and select Create Region from the context menu. Select the new region and modify the following properties. The region will have a blue background and it will be displayed only when there exists an image for a product and this evaluation is made using a condition based on a PL/SQL function.

Property Value

1. Title = Product Image
2. Type = Static Content
3. Sequence = 5 (to place this region before the Product Details region)
4. Custom Attributes = style="background: #006bdc;"
   5. Type (under Server-side Condition) = PL/SQL Function Body
5. PL/SQL Function Body

   declare
   begin
   if :P6_PRODUCT_ID is not null then
     for c1 in (
        select nvl(dbms_lob.getlength(product_image),0) A
        from demo_product_info
       where product_id = :P6_PRODUCT_ID
     ) loop
         if c1.A > 0 then return true; end if;
      end loop;
   end if;
   return false;
   end;

   Click the Product Details region and turn off the Start New Row property to place this region beside the Product Image region you just added.

NOTE: Page items are referenced in a PL/SQL block using bind variables inwhich a colon(:) is prefixed to the item name – :P6_PRODUCT_ID, for example.

Code Explained

In Oracle APEX you make use of conditions to control the appearance of page components. The ability to dynamically show or hide a page component is referred to as conditional rendering. You define conditional rendering for regions, items, and buttons. These page components have a Condition section in the property editor, where you select a condition type from a list. In the current scenario, you set a condition based on a PL/SQL function, which returns a single Boolean value: True or False. If the code returns True, the region is displayed carrying the image of the selected product.

After selecting a condition type, you inform Oracle APEX to execute the defined PL/SQL code. The code first executes an IF condition (line 3) to check whether the product ID is not null by evaluating the value of the page item P6_PRODUCT_ID. If the value is null, the flow of the code is transferred to line 13, where a false value is returned and the function is terminated. If there exists a value for the product ID, then line 4 is executed, which creates a FOR loop to loop through all records in the DEMO_PRODUCT_INFO table to find the record (and consequently the image) of the selected product (line 4-11). On line 8, another IF condition is used to assess whether the image exists. If so, a true value is returned on line 9 and the function is terminated.

6.4.6 Create Item (Handle Image Exercise C)

In this section, you will create a new item named P6_IMAGE to display the product image in the Product Image region. Right-click the Product Image region and select Create Page Item from the context menu. Set the following properties for the new item. The code defined in the PL/SQL Function Body fetches image of the selected product using a function (apex_util.get_blob_file_src). By setting the Rows Returned condition and using a SQL query we ensured the existence of an image in the table. The imgItem class defined for this page item was referenced in section 6.4 to show some content before product images.

Property Value

1. Name = P6_IMAGE
2. Type = Display Only
3. Label = Clear the Label box to make it empty
4. Region = Product Image
5. Template = No template (Set it to -Select- placeholder)
6. CSS Classes = imgItem
7. Type (under Source) = PL/SQL Function Body
8. PL/SQL Function Body =

   return '<img src="'||apex_util.get_blob_file_src('P6_PRODUCT_IMAGE',:P6_PRODUCT_ID)||'" />';

9. Type (under Server- side Condition) = Rows Returned
10. SQL Query

    SELECT  mimetype from demo_product_info
    WHERE product_id = :P6_PRODUCT_ID AND mimetype like 'image%'

    10 Escape special characters = Off (Otherwise the image won’t appear)

6.4.7 Create Button to Remove Image (Handle Image Exercise D)

An image can be removed from the Product Details page and consequently from the underlying table by clicking this button. It is attached to a process (Delete Image) defined in the next section. Right-click the Product Image region and select Create Button. Set the following properties for the new button. The button will appear on top of the region. The Target value calls a confirmation box. This call is made using an Oracle APEX function (apex.confirm) by passing a message and the name of the Delete button. If you click Yes in the confirmation box, the process associated with the Delete button removes image references from the products table.

Property Value

1. Name DELETE_IMAGE
2. Label Remove Image
3. Region Product Image
4. Button Position Copy
5. Button Template Icon
6. Hot On
7. Icon fa-image
8. Action (under Behavior)
9. Redirect to URL
10. URL (under Target)

    javascript:apex.confirm('Are you sure you want to delete this image? It will no longer be available for others to see if you continue.','DELETE_IMAGE');

6.5 Create a Process Under Processing to Delete Image (Handle Image Exercise E)

This is the process I mentioned in the previous section. It is associated with the Delete button to remove a product image. To remove an image stored in a database table, you are required to just replace the content of the relevant columns with a null. Click the Processing tab and then right-click the Processing node. From the context menu select Create Process. Set the following properties for the new process:

Property Value

1. Name Delete Image
2. Type PL/SQL Code
3. PL/SQL Code

   UPDATE demo_product_info
   SET product_image=null, mimetype=null, filename=null, image_last_update=null
   WHERE product_id = :P6_PRODUCT_ID;

4. Sequence 15 (to place it before the Close Dialog process)
5. Success Message Product image deleted
6. When Button Pressed DELETE_IMAGE

NOTE: The Processing node contains two processes (Process form Product Details and Close Dialog) that were created by the page creation wizard. The first one is created to handle DML operations, while the second one closes Page 6 when you click Create, Save, or Delete button. The values of these buttons are mentioned in Server-side Condition of the process, which specifies that the dialog is to be closed only when any of the three buttons are clicked. Clicking the DELETE_IMAGE button won't close the page, because the name of this button is not in the Value list. Similarly, the Delete Image process will only be executed when the DELETE_IMAGE button is pressed.

Test Your Work

Save your work and run the application. From the main navigation menu, select Manage Products from the Setup menu. On the main interactive report page (Figure 6-1), click the three report icons individually to see different views of products information. Clicking View Icons will present small icons of products. Each product is presented as a linked icon. If you click any icon, you'll be taken to the form page (Page 6 - Figure 6-6) where you'll see details of the selected product. Click the Report View icon. TheReport View presents data in a table. Here, you can access the details page by clicking products’ names. Click the Detail View icon. This View presents products information from a different perspective. You can access details of a product by clicking its name. This is the view that was styled in section 6.3.1 steps 8-11.

Click any product's name to call its details page (as illustrated in Figure 6-6). The form region (Product Details) was created by the wizard incorporating all relevant fields. The Product Image region was created in section 6.4.5. Also, note that the Remove Image button (you created in section 6.4.7) appears within this region. Figure 6-6 Product Details Page

Create a new product record using the Add Product button on the Products report page. Click the Browse button and select any small image file to test image upload. You can use an existing product image by right-clicking the image and selecting Save Image As from the context menu. Or, use the Download link provided on the Product Details form page to get one for testing.Once you have an image in place, fill in all the fields except List Price. Try to save this record by clicking the Create button. A message “Please fill out this field” will appear informing you to provide some value for the List Price. Now, provide some alpha-numeric value like abc123 in the List Price. Again, a message will come up reminding you to put a numeric value. Finally, input a numeric value in the List Price field and save the record. You'll see the new product appears on the Products page among others with the image you uploaded. Edit this record and see the image. Change the category of this product, switch availability to the other option and apply changes. Call the product again and observe the changes you just made to it. Click the Remove Image button and see what happens. Click the Delete button followed by OK in the confirmation box. The product will vanish from the list.

NOTE: You might encounter a primary key violation message (ORA-00001: unique constraint (DEMO_PRODUCT_INFO_PK) violated) while creating first product record. This is because the Sequence object for this table is created with an initial value of 1. When you try to save the first product record, 1 is assigned as the first primary key value, which already exists in the table. To cope with this situation, just click the Create button on the form page several times. After ten clicks the record will be save.

6.6 Uploading and Viewing PDF and Other Types of Files

As mentioned earlier in this chapter, you can store different types of files (upto 4 GB), such as images, audio, video, PDF, CSV, XLSX, DOC and more, in the BLOB column of your table. In this exercise, I’ll demonstrate how to save and view a PDF. As far as the uploading is concerned, you are not required to perform any special steps to handle PDF or any other file type. You have already set the stage in the previous sections where you handled JPEG images. Here, you will just create two new pages that will be apart from your application.

1. Click the Create Page button in the App Builder interface.
2. Click the Report option.
3. Select Interactive Report.4. On Page Attributes wizard screen, enter 303 for Page Number and Products Catalog for Page Name. Click Next.
4. On the Navigation Menu screen, set Navigation Preference to Do not associate this page with a navigation menu entry and click Next.
5. On Report Source screen, select SQL Query for Source Type and enter the following statement in the Enter a SQL SELECT statement area.

   select p.product_id, p.product_name,
   dbms_lob.getlength(p.product_image) document
   from  demo_product_info p

6. Click the Create button to complete the page creation process.

6.6.1 Modify BLOB Column

Execute the following steps to modify the BLOB column attributes.

In the Page Designer, expand the Columns node (under the Products Catalog interactive report region), click the Document column and set the following attributes for this column. In the Download Text property you set a string used for the download link. If nothing is provided, Download is used. The Content Disposition specifies how the browser handles the content when downloading. If a MIME type is provided and the file is a type that can be displayed, the file is displayed. If MIME type is not provided, or the file cannot be displayed inline, the user is prompted to download.

Property Value

1. Type = Download BLOB
2. Table Name (under BLOB Attributes) = DEMO_PRODUCT_INFO
3. BLOB Column = PRODUCT_IMAGE
4. Primary Key Column 1 = PRODUCT_ID
5. Mime Type Column = MIMETYPE
6. Filename Column = FILENAME
7. Last Updated Column = IMAGE_LAST_UPDATE
8. Download Text (under Appearance) = View
9. Content Disposition = Inline

6.6.2 Upload and View PDF

As just mentioned the product setup module created earlier in this chapter is ready to upload any type of file, so to save some precious time we are going to use that module to upload a PDF.

1. Run the application and select Setup | Manage Products.
2. Click the Create button on the main Products interactive report page.
3. Fill in the mandatory fields. Click the Choose File button, select product_catalog.pdf file, which is available in the source code, and click Create. The pdf will be uploaded to the DEMO_PRODUCT_INFO table. Take some time to verify the upload from SQL Workshop | Object Browser.
4. Switch back to Page Designer. With Page 303 appearing on your screen click the Save and Run page button. The Product Catalog page will appear displaying data from the corresponding table. Click the View link for the Product Catalog PDF document. The PDF will be opened in your browser.Figure 6-7

Play around with this module by tweaking the saved properties to see resulting effects on two pages. You can always restore properties to their original values by referencing exercises provided in the chapter.

07. Taking Orders

page 201/419

Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy

7.1 About Sale Orders

This chapter will teach you how to create professional looking order forms. Orders from customers will be taken through a sequence of wizard steps. The first wizard step will allow you to select an existing customer or create a new one. In the second step, you will select ordered products. After placing the order, the last step will show summary of the placed order. Once an order is created, you can view, modify, or delete it through Order Details page using a link in orders main page. The list presented below displays the application pages you will create in this chapter: Page No. Page Name Purpose 4 Orders The main page to display all existing orders 29 Order Details Display a complete order with details for modification 11 Identify Customer (Wizard Step 1) Select an existing customer or create a new one 12 Select Order Items (Wizard Step 2) Add products to an order 14 Order Summary (Wizard Step 3) Show summary of the placed order You’ll build this module sequentially in the sequence specified above. The first two pages (Page 4 and 29) will be created initially using a new wizard option: Master Detail. Both these pages are not part of the Order Wizard and will be utilized for order modification and deletion after recording an order. Page 4 is similar to the pages you created in Customer and Product modules and lists all placed orders, while Page 29 will be used to manipulate order details. For example, you can call an order in the usual way using the provided link in the master page. The called order will appear in the details page where you can: Add/Remove products to and from an order Delete the order itself The purpose of each chapter in this book is to teach you some new features. Here as well, you’ll get some new stuff. This chapter will walk you through to get detailed practical exposure to the techniques this module contains. After completing the two main pages, you will work on actual order wizardsteps to create other pages of the module. Recall that in the previous chapter you modified the main interactive report (Page 3) to create a couple of views (Icon and Detail) and used the Actions menu to select and sort table columns. In this chapter, many other utilities provided under the Actions menu will be exposed. But first, let’s create the two main pages using the conventional route.

7.2 Create Order Master and Order Detail Pages

1. Click the Create Page button in the App Builder.
2. On the first wizard screen, click the Master Detail option. A master detail page reflects a one-to-many relationship between two tables in a database. Typically, a master detail page 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. On the Master Detail page, the master record displays as a standard form and the detail records appear in an interactive grid region under the master section.
3. On the next wizard screen, select the Drill Down option, which opens input form in a separate page.
4. Fill in the next screen (Page Attributes) as illustrated below, and click Next.Figure 7-1
5. On the Navigation Menu screen, set Navigation Preference to Identify an existing navigation menu entry for this page, set Existing Navigation Menu Entry to Orders, and click Next.
6. Select the values in the Master Source screen (as shown in the following figure), and click Next. In this step, you select the parent table, which contains the master information for each order. You also specify the primary key column, which will be populated automatically behind the scene using a trigger named BI_DEMO_ORDERS via a sequence named DEMO_ORDERS_SEQ. You can view both these database objects from SQL Workshop > Object Browser interface. The ORDER_ID column selected in the Form Navigation Order list is the navigation order column used by the previous and next buttons on the Order Details page to navigate to a different master record.Figure 7-2
7. Set properties on the Detail Source screen as illustrated below and click Create to finish the wizard. On this screen, you specify the relational child table, which carries line item information for each order. The primary key column of this table will be populated automatically via a trigger named BI_DEMO_ORDER_ITEMS, which gets the next primary key values from a sequence named DEMO_ORDER_ITEMS_SEQ. In the Master Detail Foreign Key list you select the sole auto-generated foreign key, which creates a relationship between the master and detail tables.Figure 7-3 Before running these pages, let’s see what the wizard has done for us. The master page (Page 4) is created with an Interactive Report to display a list of all order from the Orders Mater table. The details page (Page 29), on the other hand, has many things to reveal. The following table lists all those components created automatically by the wizard with complete functionalities to manage this module. Component: Pre-Rendering Process Name: Initialize form Form on DEMO_ORDERS Description: Fetches master row from DEMO_ORDERS table. This process was briefed in Chapter 5 Section 5.4.5. If you see a different process name, then there is nothing to worry about as it sometimes happens due to change in APEX version. Component: Region Name: Form on DEMO_ORDERSDescription: The page has two regions. The Form on DEMO_ORDERS region, which is a Static Content region, displays master information like customer ID, order date, and so on. The lower region shows product details along with quantity and price in an Interactive Grid. Component: Buttons Names: GET_PREVIOUS_ORDER_ID and GET_NEXT_ORDER_ID Description: These buttons are added to the master region to fetch previous and next orders, respectively. For example, when you click the Next button , the page is submitted to get the next order record from the server by triggering the Initialize form Form on DEMO_ORDERS process using the value set for Next Primary Key Item(s) property in this process. The Next Primary Key Item(s) and Previous Primary Key Item(s) properties in this process are associated with respective hidden page items to fetch next and previous order ids. Based on the currently fetched order number, which is held in the page item P29_ORDER_ID, the process dynamically obtains the next and previous order numbers and stores them in two hidden page items: P29_ORDER_ID_NEXT and P29_ORDER_ID_PREV. The visibility of the Next and Previous buttons is controlled by a Server-side Condition (Item is NOT NULL), which says that these buttons will be visible only when their corresponding hidden items have some values. If you make any modification to an order on Page 29 and navigate to another order record using any of these buttons, the changes are saved to the two database tables. This is because the Action property of the two buttons is set to Submit Page. When the page is submitted, two processes (Process form Form on DEMO_ORDERS and Order Details - Save Interactive Grid Data defined later in this section) are executed to make the changes permanent. Component: Button Name: Cancel Description: The Cancel button closes Page 29 and takes you back to Page 4 without saving an order. For this, a redirect action is generated in the Behavior section with Page 4 set as the target. Component: ButtonName: Delete Description: The Delete button removes a complete order. When this button is clicked, a confirmation dialog pops up using its Target property, which is set to: javascript:apex.confirm(htmldb_delete_message,'DELETE'); When you confirm the deletion, a SQL DELETE action (specified in Database Action property for this button) is executed within the built-in Automatic Row Processing (DML) processes–Process form Form on DEMO_ORDERS and Order Details - Save Interactive Grid Data. Component: Button Name: Save Description: The Save button records updates to an existing order in the corresponding database table. This button is visible when you call an order for modification, in other words, P29_ORDER_ID is NOT NULL. The process behind this button is controlled by a SQL UPDATE action within the two built-in Automatic Row Processing (DML) processes. Component: Button Name: Create Description: The Create button is used for new orders to handle the INSERT operation. This button is visible when you are creating a new order – that is, the page item P29_ORDER_ID is NULL. It uses the SQL INSERT action within the two built-in Automatic Row Processing (DML) processes. Component: Region Name: Order Details Description: This is an Interactive Grid region, which is generated to view, add, modify, and delete line items using the parameters set in step 7. The information you provided in this interactive grid is saved to the DEMO_ORDER_ITEMS table through a process named Order Details - Save Interactive Grid Data – discussed next. Component: Process Name: Process form Form on DEMO_ORDERSDescription: This Automatic Row Processing (DML) type process is generated by the wizard to handle DML operations performed on the master row of an order, which gets into the DEMO_ORDERS table. It comes into action when you click Delete, Save, or Create buttons. The three buttons and their associated actions are depicted in the following figure. Component: Process Name: Order Details – Save Interactive Grid Data Description: The Save Interactive Grid Data process is responsible to handle DML operations on the details table (DEMO_ORDER_ITEMS). This process is associated with the details section (Interactive Grid) to insert, update, or delete Interactive Grid rows. Component: Branches Name: Go To Page 29, Go To Page 29, and Go To Page 4 When you submit a page, the Oracle APEX server receives a submit request and performs the processes and validations associated with that request. After this, it evaluates where to land in the application via these branches. By default, it selects the current page as the target page. For example, when you click the Next or Previous buttons on Page 29, you stay on the same page. If you want to land users to some other page, youcan do this as well by creating branches. In the current scenario, you are moved back to Page 4 when you click any other button on Page 29. A branch has two important properties: Behavior and Server-side Condition. In the Behavior section you specify the page (or URL) to redirect to, and in Condition you specify when the branch is to be fired. Here, the first two branches are created to keep you on Page 29. These branches are associated with Next and Previous buttons–see When Button Pressed properties of these branches. The third one takes you back to Page 4 when you click any other button on this page – see the Behavior section that specifies the redirect. Run this module from the Orders navigation menu entry. The first page (Page 4) you see is an interactive report. It is similar to the one you created in Chapter 6. It has a Create button, which is used to create a new order. Click the edit link (represented with a pencil icon) in front of any record to call the Order Details page (Page 29). The Order Details page has two regions. The upper region, which is called the master region, displays information from the DEMO_ORDERS table, while the lower interactive grid region shows relevant line item information from the DEMO_ORDER_ITEMS table. Besides usual buttons, the master region has two navigational buttons at the top. These buttons help you move forward and backward to browse orders. The Order Timestamp field is supplemented with a Date Picker control. You can add more products to the details section by clicking the Add Row button. From a professional viewpoint this page is not user friendly. If you try to add a new product, you have to enter its ID manually. Moreover, if you try to create a new order, you won’t see the interactive grid. By default, this grid is visible only when you modify an existing order and it hides when you try to create a new order. This behavior is controlled by a server side condition (Item is NOT NULL) set for the Interactive Grid region (Order Details). With this condition set, the region is rendered only when the page item P29_ORDER_ID has some value. Choosing the – Select – placeholder for the Server-side Condition Type property removes this condition and makes the interactive grid visible every time you access Page 29. Even after this adjustment, you will face some constraint issues related to a backend table. To avoid all such problems, execute the instructions provided in subsequentsections to make the module user-friendly.

7.3 Modify Orders Page - Page 4

Execute the instructions provided in the following sub-sections to modify the Orders page. 7.3.1 Modify the Orders Interactive Report Region on Page 4 The Orders interactive report region on Page 4 fetches orders information from the DEMO_ORDERS table. Let’s replace the existing auto-generated data fetching mechanism with a custom SQL query, which incorporates customers information from the DEMO_CUSTOMERS table.

1. Open Page 4 in the Page Designer, and click the Orders region. Modify the region using the values set in the following table: Property Value Location Local Database Type SQL Query SQL Query select lpad(to_char(o.order_id),4,'0000') order_number, o.order_id, to_char(o.order_timestamp,'Month YYYY') order_month, trunc(o.order_timestamp) order_date, o.user_name sales_rep, o.order_total, c.cust_last_name||', '||c.cust_first_name customer_name, (select count(*) from demo_order_items oi where oi.order_id = o.order_id and oi.quantity != 0) order_i o.tags tags from demo_orders o, demo_customers c where o.customer_id = c.customer_id
2. Expand the Columns node under the Orders region and set the Type property for the ORDER_ID column to Hidden Column.
3. Set meaningful headings for all interactive report columns as follows. Order #, Order Month, Order Date, Customer, Sales Rep, Order Items, Order Total, and Tags
4. Edit the ORDER_TOTAL column and select the value $5,234.10 for its Format Mask property.5. Select the Order Number column (not Order ID) and turn it into a link using the following properties: Property Value Type Link Target (in Link section) Type = Page in this application Page = 29 Name = P29_ORDER_ID Value = #ORDER_ID# Clear Cache = 29 Link Text #ORDER_NUMBER#
5. Click the Attributes node under the Orders interactive report region. Select Exclude Link Column for Link Column property in the Property Editor. This action will exclude the default link column (denoted with a pencil icon) from the report as we have a custom link created in the previous step.
6. In the Attribute node, scroll down to the Actions Menu section, and turn on the Save Public Report option, to include this option in the Actions menu at runtime. By enabling this option you can create a public report – see section 7.3.4.
7. Click the Create button and set the following properties for this button. New customer orders in this module will be recorded via some wizards steps, and Page 11 (to be created in a subsequent section) will be the first order wizard step. Property Value Label Enter New Order Target (in Behavior section) Type = Page in this application Page = 11 Clear Cache = 11
8. Save your modifications. 7.3.2 Modify Interactive Report Perform the following steps to change the look and feel of the default interactive report. After performing these steps, the interactive report will besaved as the Default Primary Report, which cannot be renamed or deleted. Note that these modifications are made using the Actions menu at runtime.
9. Click the Save and Run Page button to run Page 4.
10. Click the Actions menu, select the Columns option, arrange the report columns as depicted in the following screen shot, and click Apply. This action will arrange the report columns in the specified order. Figure 7-4
11. Click the Actions menu again, and select Data followed by the Sort option.
12. In the Sort grid, select the Order # column in the first row, set the corresponding Direction to Descending, and click Apply. This action will display most current orders on top.Figure 7-5
13. Click Actions | Report | Save Report. In the Save Report dialog box, select As Default Report Settings from the Save list, select Primary for Default Report Type, and click Apply. NOTE: Always save a report via the Actions menu whenever you make changes to it; otherwise, your modifications will not be reflected the next time you log in to the application. In Interactive Reports, you can apply a number of filters, highlights, and other customizations. Rather than having to re-enter these customizations each time you run the report, you tell Oracle APEX to remember them so that they are applied automatically on every next run. The application users can save multiple reports based on the default primary report, as discussed in the next couple of sections. 7.3.3 Create Alternative Report Alternative report enables developers to create multiple report layouts. Only developers can save, rename, or delete an Alternative Report. This report (named Monthly Review) is based on the default primary report and will be rendered in a different layout using the Control Break utility on Order Month column. Execute the following steps on the primary interactive report on Page 4 to create three different views of the report. A. Report View
14. From the Actions menu, select Save Report (under Report). In the Save Report dialog box, select As Default Report Settings from the Save list. This time, select the Alternative option for Default Report Type, enter Monthly Review in the Name box, and click Apply. You will see a drop down list between the Search bar andthe Actions menu carrying two reports: Primary Report and Monthly Review.
15. From the list, select the Monthly Review alternative report.
16. Click Actions | Format | Control Break. Under Column, select Order Month in the first row (A), set Status to Enabled (B), and click Apply. The Control Break feature enables grouping to be added to your report on one or more columns. The Column attribute defines which column to group on and the Status attribute determines whether the control break is active. When you click the Apply button, you will see the report results are grouped by the Order Month column and the Control Break column rule (C) is listed under the toolbar. A checkbox (D) is displayed next to the Control Break column and it is used to turn the control break rule on or off. The control break can be deleted from the report by clicking the small cross icon (E).
17. Click Actions | Format | Highlight. Type Display Orders >$1000 (A) in the Name box, set Highlight Type to Cell (B), select green (C) for Background Color, and click red (D) for Text Color. In the Highlight Condition section, set Column to Order Total (E), Operator to > greater than (F), Expression to 1000 (G), and click Apply. To distinguish important data from the rest, Oracle APEX provides you with conditional highlighting feature in interactive reports. The highlight feature in the Actions menu enables users to display data in different colors based on a condition. You can define multiple highlight conditions for a report. In this step, you're instructing to highlight the Order Total column in the report with green background and red text color where the value of this column is greater than 1000. Since you set the Highlight Type to Cell, the condition will apply only to the Order Total column. To modify an existing highlight rule, click its entry under the interactive report toolbar.
18. Click Actions | Format | Highlight. Type Display Orders <= $999 in the Name field, set Highlight Type to Row, click yellow for Background Color, click Red for Text Color, in HighlightCondition set Column to Order Total, Operator to <= (less than or equal to), Expression to 999 and click Apply. This step is similar to the previous one with different parameters. In contrast to the previous action, where only a single cell was highlighted, this one highlights a complete row with yellow background and red text color and applies it to all rows in the report that have Order Total equaling $999 or less. The resulting output should resemble the following figure. Figure 7-8 Monthly Order Review ReportB. Chart View You can generate charts in Interactive Reports based on the results of a report. You can specify the type of chart together with the data in the report you want to chart. In the following exercise, you will create a horizontal bar chart to present monthly sales figures using the Order Month column for the chart labels and a sum of the Order Total column for the chart values. Figure 7-9
19. Click Actions | Chart.
20. Select the first option (Bar) for the Chart Type.
21. Select Order Month for Label.
22. Enter Month in Axis Title for Label.
23. Select Order Total for Value.
24. Enter Sales in Axis Title for Value.7. Select Sum for Function.
25. Set Orientation to Horizontal.
26. Select Label-Ascending for Sort.
27. Click Apply. The chart should resemble the following figure. Note that the toolbar now has two icons: View Report and View Chart. If the chart doesn’t appear, click the View Chart icon in the toolbar. Move your mouse over each bar to see total amount for the month. Figure 7-10 Chart View C. Group By View Group By enables users to group the result set by one or more columns and perform mathematical computations against the columns. Once users define the group by, a corresponding icon is placed in the toolbar, which they can use to switch among the three report views.1. Click the View Report icon in the interactive report toolbar to switch back to the report view interface.
28. Click Actions | Group By.
29. Set the properties as show in the following figure and click Apply. Use the Add Function button to add the second function (Count). The first function calculates the monthly average of orders, while the second function counts the number of orders placed in each month. Figure 7-11
30. Click Actions | Report | Save Report. Select As Default Report Setting from the Save list. Select Alternative for the Default Report Type. The Name box should display Monthly Review. Click Apply. The output of this view is illustrated in the following figure. Note that a third icon (View Group By) is also added to the toolbar.Figure 7-12 Group By View 7.3.4 Create Public Report This type of report can be saved, renamed, or deleted by end users who created it. Other users can view and save the layout as another report. Execute the following instructions to create the three views Report, Chart, and Group by of a public report. The Alternative report created in the previous section focused on orders, while this one is created from customers perspective. A. Report View
31. Select the default 1. Primary Report from the Reports drop-down list in the toolbar.
32. From the Actions menu, select Save Report (under Report).
33. From the Save drop-down list select As Named Report. For report Name, enter Customer Review, put a check on Public and click the Apply button. A new report group (Public) will be added to the reports list in the toolbar, carrying a new report named Customer Review. Users can create multiple variations of a report and save them as named reports for either public or private viewing. When you click the Apply button, the report is displayed on your screen.
34. With the Customer Review report being displayed on your screen, click Actions | Format | Control Break. Select Customer in the first row under Column, set Status to Enabled, and click Apply to see the following output.Figure 7-13 B. Chart ViewFigure 7-14
35. Click Actions | Chart.
36. Set parameters for the chart as illustrated in the figure 7-14.
37. Click the Apply button. The output is illustrated in figure 7-15. NOTE: The chart uses the Average function (as compared to the Sum function used in the previous exercise). William Hartsfield has placed two orders amounting to $2,370. The average for this customer comes to $1,185 (2,370/2) and this is what you see when you move your mouse over the bar representing this customer.Figure 7-15 Chart View C. Group By View
38. Click the View Report icon to switch back.
39. Click Actions | Group By.
40. Set parameters for this view as show in the following illustration. Turn on the Sum switch for all three functions to display grand totals.Figure 7-16
41. Click Apply.
42. Save your work using the Actions menu. Select As Named Report from the Save list. The Name box should be displaying Customer Review. Click Apply. Select Customer Review from the report list in the toolbar, and click the View Group By icon. The following figure displays the output for the selections you just made. In this view, you utilized Sum and Count functions on two columns: Order Total and Order Items. This view displays total amount of orders placed by each customer with number of orders and the total number of items ordered.Figure 7-17 Group By View D. Pivot View The Pivot option is the Actions menu is used to create a cross tab view based on the data in the report. Let's see an instance of this option as well.
43. Click the View Report icon to switch back.
44. Click Actions | Pivot.
45. Set parameters as show in the following illustration. Don’t forget to turn on the Sum switch to produce grand totals on the page.Figure 7-18
46. Click Apply.
47. Save your work using the Actions menu. The following figure illustrates the output of these actions. Figure 7-19 Pivot ViewIn the previous few sections you used some options from the Actions menu to customize the interactive report. However, the menu contains a few more, as listed below: Filter focuses the report by adding or modifying the WHERE clause on the query. Rows Per Page determines how many rows display in the current report. Data contains the following submenu: Sort - Changes the columns to sort on and determines whether to sort in ascending or descending order. Compute - Enables users to add computed columns to a report. Flashback enables you to view the data as it existed at a previous point in time by specifying number of minutes. To use this option, the Oracle database FLASHBACK feature must be turned on. Reset is used to reorganize the report back to the default report settings. Help provides descriptions of how to customize interactive reports. Download enables users to download a report. Available download formats depend upon your installation and report definition. To see these formats, click a region's Attribute node and check the Download section in the Property Editor.

7.4 Modify Order Details Page - Page 29

Execute the instructions provided in the following sub-sections to modify the Order Details Page. 7.4.1 Modify Master Region Properties Page 29 contains two regions. The master region (Form on DEMO_ORDERS) is of Form type and carries order header information, while the second region (Order Details) is an interactive grid, which containsline item details. Modify the master region using the following steps:

1. Open Page 29 in the Page Designer, click the root node (Page 29: Order Details) and set the Page Mode property to Modal Dialog to open it on top of Page 4. Set Width, Height, and Maximum Width properties to 900, 700, and 1200, respectively. Also, set Dialog Template (in the Appearance section) to Wizard Modal Dialog. Dialog templates are defined in the application theme. When a dialog page is created, the template is automatically set to Theme Default, which will render the page using the default page template defined in the current theme. The Wizard Modal Dialog provides a streamlined user interface suitable for input forms. When you switch to this template, the name of Content Body changes to Wizard Body and a new node named Wizard Buttons is added. We will use this node to place all our page buttons to make them visible all the time.
2. Click the Form on DEMO_ORDERS region and enter Order

   &P29_ORDER_ID. (including the terminating period) for its

   Title. The expression consists of two parts. The first one (Order #) is a string concatenated to a page item (P29_ORDER_ID), which carries the order number. The string, when combined, would be presented as: Order # 1. Make sure that region’s Template attribute (under Appearance) is set to Standard to show this title.

3. Create a new page item in the Items node under the master region and set the following properties. This item will present customer information on each order as display-only text. Display Only items are shown as non-enterable text item. Note that you may get an error message (ORA-20999) when you enter the SQL query specified in the table below. Save the page by clicking the Save button to get rid of this message. Moreover, if you keep the default value (On) for Escape Special Characters property, the customer information appears on a single line with
   tags Property Value Name P29_CUSTOMER_INFO Type Display OnlyLabel Customer Template Optional Type (under Source) SQL Query (return single value) SQL Query select apex_escape.html(cust_first_name) || ' ' || apex_escape.html(cust_last_name) || '
   ' || apex_escape.html(cust_street_address1) || decode(cust_street_address2, null, null, '
   ' || apex_escape.html(cust_street_address2)) || '
   ' || apex_escape.html(cust_city) || ', ' || apex_escape.html(cust_state) || ' ' || apex_escape.html(cust_postal_code) from demo_customers where customer_id = :P29_CUSTOMER_ID Escape Special Characters Off NOTE: If you see an error message after providing the SQL query, ignore it and click the Save button to save the page. The error will vanish.
4. Using drag and drop arrange page items in the master region as illustrated in the following screenshot. Figure 7-20
5. Edit the following items individually and set the corresponding properties shown under each item.a. P29_ORDER_TIMESTAMP Property Value Type Display Only Label Order Date Template Optional Format Mask DD-MON-YYYY HH:MIPM b. P29_ORDER_TOTAL Property Value Type Display Only Template Optional Format Mask $5,234.10 c. P29_USER_NAME Property Value Type Select List Label Sales Rep Template Optional Type (List of Values) SQL Query SQL Query select distinct user_name d, user_name r from demo_orders union select upper(:APP_USER) d, upper(:APP_US from dual order by 1 Display Extra Values Off Display Null Value Off Help Text Use this list to change the Sales Rep associate In the Help Text attribute you specify help text for an item. The help text may be used to provide field level, context sensitive help. At run-time you will see a small help icon in-front of this item. When you click this icon, a window pops up to show the help text.d. P29_TAGS Property Value Template Optional e. P29_CUSTOMER_ID Property Value Type Hidden Value Protected Off f. P29_ORDER_ID Property Value Type Hidden Value Protected Off
6. In the Region Buttons node, set Button Position property to Edit for GET_PREVIOUS_ORDER_ID and GET_NEXT_ORDER_ID buttons to place them on top of the region. 7.4.2 Modify Details Region’s Properties After setting the master region, let’s modify the details region to give it a desirable look.
7. Click the Order Details interactive grid region and set its Title to Items for Order #&P29_ORDER_ID. – including the terminating period.
8. Replace the auto-generated source attributes of the region with the followings: Property Value Location Location Database Type SQL Query SQL Query select oi.order_item_id, oi.order_id, oi.product_id, oi.uni oi.quantity, (oi.unit_price * oi.quantity) extended_price from DEMO_ORDER_ITEMS oi, DEMO_PRODUCT_INFO piwhere oi.ORDER_ID = :P29_ORDER_ID and oi.product_id = pi.product_id (+)
9. Save the page.
10. Under the Columns node, edit the following columns using the specified properties. Column Property Value ORDER_ITEM_ID Type Value Protected Primary Key Hidden On On ORDER_ID Type Value Protected Hidden On PRODUCT_ID Type Heading Alignment Type (LOV) List of Values Display Null Value Select List Product Select the left icon Shared Components Products With Price Off UNIT_PRICE Alignment Column Alignment Format Mask Select the right icon Select the right icon $5,234.10 QUANTITY Width (under Appearance) Type (under Default) PL/SQL Expression 5 PL/SQL Expression 1 (sets 1 as the default quan EXTENDED_PRICE Type Heading Alignment Column Alignment Format Mask Query Only ( under Source ) Display Only Price Select the right icon Select the right icon $5,234.10 On After modifying an interactive grid query you must specify a primary column, which is required for editing and to specify master detail relationship. If not defined, you will encounter " Interactive Grid doesn't have a primary key column defined which is required for editing or in a master detail relationship " message . By setting the ORDER_ITEM_ID column as the primary key you eliminate this error. The Alignment property sets the heading alignment, while the ColumnAlignment specifies the column display alignment. For product ID column, we changed two properties. First, we set its Type property to Select List. Secondly, we associated an LOV (Products with Price) to it. This LOV was created in Chapter 3 section 3.4.2 to display a list of products along with respective prices. The Query Only property (under Source section) set for the Extended Price column specifies whether to exclude the column from DML operations. If set to On, Application Express will not utilize the column when executing the Interactive Grid - Automatic Row Processing (DML) process. In the current scenario, you excluded the Extended Price column, because it is not a physical table column and is calculated in the SELECT query stated above. If you keep the default value of this property for the Extended Price column, you will get “Virtual column not allowed here” error message when you try to save an existing order. All columns whose definitions include concatenations, inner selects, functions call, or a column in an updateable view that is based on an expression should be excluded. All columns that need to be included in any INSERT or UPDATE statements must have this option set to Off. Note that columns of type Display Only are also included in the Automatic Row Processing unless this option is turned on.
11. Using drag and drop arrange the five visible columns in the following order: PRODUCT_ID, UNIT_PRICE, QUANTITY, and EXTENDED_PRICE
12. Right-click the Wizard Buttons node and select Create Region. Set Title of the new region to Buttons and Template to Buttons Container. In the Region Buttons node, click the Cancel button, and set its Region property to Buttons. Set the same Buttons region for Delete, Save, and Create buttons, too. This action will place the four buttons under the Buttons region in the interactive grid.
13. On the Processing tab, make sure that the process “Process form Form on DEMO_ORDERS” sits before the Order Details – Save Interactive Grid Data process. If not, drag and place it before the Order Details – Save Interactive Grid Data process or set itsSequence property to a number lower than that of the Save Interactive Grid Data process. Note that this process must precede the Save Interactive Grid Data process; otherwise, you will get the error “Current version of data in database has changed since user initiated update process” when you try to manipulate data in the interactive grid.
14. Save the changes. NOTE: If you see a different process name, then there is nothing to worry about as it sometimes happens due to change in APEX version. Test Your Work Run the application and click the Orders option in the main navigation menu. The page that comes up should look like Figure 7-21. Click any order number to call the Order Details page (Figure 7-22). Try to navigate forward and backward using the Next and Previous buttons. At the moment, you can only use these two pages to manipulate existing orders. In the next sections, you will create some more pages to enter new orders. Call order number 0002 and click the Add Row button appearing in the Interactive Grid's toolbar. A new row will be added to the grid just under the first row with the Product column appearing as a list of values carrying all products with their respective prices. Select Air Max 2090 (A) from this list, enter 1500 (B) in the Unit Price column, and put some value in the Quantity column (or accept the default quantity 1). Now, remove the checkmark appearing in the first column of the new record and put a check on the previous Air Max 2090 record (C). From the Row Actions menu (D), select Delete Row (E). The previous record will be marked as deleted (F). Click the Apply Changes button (G). Call the order again. The new record will be added to the table with the correct price of the product and the previous record will be removed.Figure 7-21 – Orders Interactive Report Page Figure 7-22 – Order Details

Page7.5 Create a Page to Enter a New Order - Page 11

As mentioned earlier, you will go through a series of steps to enter a new order. You identified and created these steps in Order Wizard list in Chapter 3 section 3.2.3. The top section (A) in Figure 7-23 reflects these steps. Each step will be associated to an application page. The rest of this chapter will guide you to create the three pages individually. In this exercise, you will create Page 11 - Enter New Order. The order recording process initiates when you click the button Enter New Order on the Orders page (Page 4). The button calls Page 11, where you select a customer who placed the order. Besides selecting an existing customer, you can also create record of a new customer on this page. The Customer LOV button (B) calls a list of existing customers from which you can select one for the order. If you select the New Customer option (C), a region (New Customer Details) will be shown under the existing region. By default, this region is hidden and becomes visible when you click the New Customer option. This functionality is controlled by a dynamic action (Hide / Show Customer), which will also be created for this page. In addition to various techniques taught in this part, you’ll create this page from an existing page - Customer Details (Page 7) - to generate a new customer record. Here, you’ll make a copy of that page and will tweak it for the current scenario. Let’s see how it is done. Figure 7-23 Identify Customer Page1. In the App Builder interface, click the Customer Details - Page 7 to open its definitions in Page Designer.

2. Click the Create menu at top-right in the toolbar and select Page as copy.
3. On the first wizard screen, select the option Page in this application for Create a page as a copy of and click Next.
4. Fill in the following values on Page To Copy screen and click Next. Figure 7-24
5. On the Navigation Menu screen select Identify an existing navigation menu entry for this page, select Orders for Existing Navigation Menu, and click Next.
6. Accept the names of existing page buttons and items on the New Names screen and click the Copy button to finish the wizard. Look at the Page Designer. All the elements from Page 7 appear on the new page, especially the items section, which carries all input elements (with P11 prefix) to create a new customer record. This is the section we needed on our new page to spare some time. 7.5.1 Modify Page Properties
7. In Page 11, click the root node (Page 11: Identify Customer). In the Properties pane, set Dialog Template (under Appearance) toWizard Modal Dialog. The template creates a region (Wizard Progress Bar) to hold the order progress list (A), as shown in Figure 7-23, and alters the name of the main region from Content Body to Wizard Body.
8. Set Width and Height properties to 700 and 500, respectively.
9. Remove htmldb_delete_message variable from Function and Global Variable Declaration property. Save the page after removing the variable. This is an auto-generated variable associated with the customer record deletion process handled transparently by Oracle APEX. It is removed because the customer record deletion process is not required here.
10. Change Maintain Session State property (in Source section) of P11_CUST_FIRST_NAME, P11_CUST_LAST_NAME, P11_CUST_STREET_ADDRESS1, P11_CUST_STREET_ADDRESS2, P11_CUST_CITY, and P11_CUST_STATE,P11_CUST_POSTAL_CODE, P11_CREDIT_LIMIT, P11_PHONE_NUMBER1, P11_PHONE_NUMBER2, P11_CUST_EMAIL, P11_URL, and P11_TAGS page items to Per Session (Disk). Switching to this value maintains the item value to access it across requests. See PL/SQL code line 22-30 in section 7.6.3 and Place Order process in section 7.6.8 later in this chapter where these items are referenced. If you keep the default Per Request (Memory Only) value for this property, none of the page item values can be referenced on other module pages and will not be inserted in the database table. 7.5.2 Create Region – Order Progress Right-click the Wizard Progress Bar node (under Regions) and select Create Region. Set the following properties for the new region. The Order Wizard list used here was created in Chapter 3 - section 3.2.3. The Wizard Progress value specified for the List Template property displays a progress train based on the list items and is well suited for wizards and multi-step flows. Property ValueTitle Order Progress Type List List Order Wizard Template Blank with Attributes List Template (under Attributes node) Wizard Progress Label Display ( under Attributes |Template Options ) All Steps ( displays labels of all wizard steps ) 7.5.3 Create Region – Identify Customer Right-click the Wizard Body node and select Create Region. Drag the new region and place it above the Customer Details region. Set the following properties for it. This region is created to act as a main container to hold a radio group item and a couple of sub-regions. Property Value Title Identify Customer Type Static Content Template Standard 7.5.4 Create Item Right-click the new Identify Customer region and select Create Page Item. Set the following properties for the new item, which is a Radio Group. The list of values attached to this radio group item (NEW OR EXISTING CUSTOMER) was created in Chapter 3 - section 3.4.4 with two static values to create a new customer or select an existing one for a new order. The value set for the Number of Columns property displays these values in two separate columns. The first Type and Static Value properties (under Source) specify the source type the value of this item will based on when you access this page, whereas the second pair sets the EXISTING value as the default choice.

Property Value

Name P11_CUSTOMER_OPTIONS Type Radio Group Label Create Order for: Number of Columns 2 Template Required Label Column Span 3 Type (under List of Values) Shared ComponentList of Values NEW OR EXISTING CUSTOMER Display Null Value Off Type (under Source) Static Value Static Value (under Source) EXISTING Type (under Default) Static Static Value (under Default) EXISTING 7.5.5 Create a Sub Region – Existing Customer Right-click the Identify Customer region and select Create Sub Region. This will add a sub region under the page item P11_CUSTOMER_OPTIONS. Set the following properties from the sub region. Property Value Title Existing Customer Type Static Content Template Blank with Attributes 7.5.6 Modify Item – P11_CUSTOMER_ID In the Items section, click P11_CUSTOMER_ID. Set the Name property of this hidden item to P11_CUSTOMER_ID_XYZ. Set Server-side Condition Type to Never (last in the list). This item is renamed and suppressed from being rendered because a new item (of Popup LOV type) with the same name is created in the next section to display a list of customers, instead. By selecting the Never value for the Server-side Condition Type property, you permanently disable a page component. That is, the component is never rendered. 7.5.7 Add LOV Right-click the Existing Customer sub-region and select Create Page Item. Set the following properties for this item. The Type value (under Source) is set to Null, because the IDs and names of customers are retreived using a SQL query and displayed in a Popup LOV. Property Value Name P11_CUSTOMER_ID Type Popup LOV Label Customer Template Required Width 70Value Required Off Type (under List of Values) SQL Query SQL Query select cust_last_name || ', ' || cust_first_name d, customer_id r from demo_customers order by cust_last_name Display Extra Values Off Display Null Value Off Type (under Source) Null Help Text Choose a customer using the pop-up selector, or to create a new customer, select the New customer option. 7.5.8 Modify Customer Details Region Click the Customer Details region and set the following properties for this region. When you specify a parent region you make a region child of a parent region. Property Value Title New Customer Details Parent Region Identify Customer 7.5.9 Delete Validation, Processes, and Buttons

1. On the Processing tab, right-click the entry Can’t Delete Customer with Orders under Validations, and select Delete from the context menu. Similarly, delete the process Process Customer Data.
2. Also, remove Delete, Save, and Create buttons from the Buttons region on the Rendering tab. 7.5.10 Delete Process On the Rendering tab, expand the Pre-Rendering | Before Header | Processes node and delete the process named Initialize Customer Details. This is a default process created in the Customers module and is not required in the current scenario. 7.5.11 Create Button Create a new button in the Buttons region and set the following properties for it. After identifying a customer, you click this button to advance to the secondorder wizard step. This button will appear under the Cancel button in the Page Designer. When this button is clicked, the Action property submits the page and a branch (created in section 7.5.18) takes control of the application flow and moves you on to the next wizard step. Property Value Button Name NEXT Label Next Button Position Next Button Template Text with Icon Hot On Icon fa-chevron-right Action Submit Page (default) 7.5.12 Create Process - Create or Truncate Order Collection When developing web applications in Oracle APEX, you often need a mechanism to store an unknown number of items in a temporary location. The most common example of this is an online shopping cart where a user can add a large number of items. To cope with this situation in Oracle APEX, you use Collections to store variable information. Before using a collection, it is necessary to initialize it in the context of the current application session. After clicking the Enter New Order button, you’re brought to this page (Page 11) and this is where your collection (named ORDER) is initialized using a PL/SQL process that fires Before Header when the user enters into the interface of Page 11. See sections 7.6.7 and 7.6.8 for relevant details on collections. On the Rendering tab, expand the Pre-Rendering node. Right-click the Before Header node and select Create Process. Set the following properties for the new process. Property Value Name Create or Truncate ORDER Collection Type PL/SQL Code PL/SQL Code apex_collection.create_or_truncate_collection (p_collection_name => 'ORDER');Figure 7-25 7.5.13 Create Dynamic Action (Hide / Show Customer) Click the Dynamic Actions tab. Right-click the Change node and select Create Dynamic Action. Click the New node and set the following properties. The following settings inform Oracle APEX to fire the dynamic action when user changes (Event) the radio group item (Selection Type) P11_CUSTOMER_OPTIONS (Item) from New Customer to Existing. Property Value Name Hide / Show Customer Event Change Selection Type Item(s) Item(s) P11_CUSTOMER_OPTIONS Type (under Client-side Condition) Item = value Item (under Client-side Condition) P11_CUSTOMER_OPTIONS Value EXISTING Click the Show node to set the following properties. The values for these properties are set to show the Existing Customer region when the EXISTING option is selected from the radio group. The On value set for the Fire on Initialization property specifies to fire the action when the page loads. Property Value Action Show Selection Type Region Region ..Existing Customer Fire When Event Result is True Fire on Initialization OnRight-click the Show node and select Create Action. Another Show node will be added just under the previous one. Set the following properties for it. This action is also assoicated with the previous two and is added to hide New Customer Details region when the EXISTING option is selected. Property Value Action Hide Selection Type Region Region ..New Customer Details Fire When Event Result is True Fire on Initialization On Right-click the Show node again and select Create Opposite Action. This will add an opposite Hide action under the False node (with all properties set) to hide the Existing Customer region. Right-click the Hide node under the True node and select Create Opposite Action. This will add a Show action under the False node to show the New Customer Details region. If you run the page at this stage (by clicking the Enter New Order button on Page 4), you'll see the P11_CUSTOMER_ID item (in the Existing Customer region) is shown on the page. Now, select the New Customer option. The item P11_CUSTOMER_ID disappears from the page and the New Customer Details region becomes visible. Select the Existing Customer option again, the item becomes visible and the New Customer Details region hides. 7.5.14 Modify Validation – Check Credit Limit On the Processing tab, click the Check Credit Limit validation. Set its Sequence to 100 and save the change to place this validation in a proper sequence after the following validations. Note that the Sequence property determines the order of evaluation. 7.5.15 Create Validation – Customer ID Not Null Right-click the Validations node and select Create Validation. Set the following properties for the new validation. You can control when a validation is performed by configuring its Server-side Condition property. Select a condition type from the list that must meet in order for a validation to process. In the current scenario, the condition (item=value) is formed likethis: P11_CUSTOMER_OPTIONS = EXISTING . The validation fires when you select the Existing Customer option on the application page, and do not select a customer from the provided list. In case of an error at runtime, the #LABEL# substitution string specified in the Error Message property is replaced with the label of the associated item P11_CUSTOMER_ID – that is, Customer. Property Value Name Customer ID Not Null Sequence 10 Type (Validation) Item is NOT NULL Item P11_CUSTOMER_ID Error Message Select a #LABEL# from the provided list. Associated Item P11_CUSTOMER_ID Type (Server-side Condition) Item = Value Item P11_CUSTOMER_OPTIONS Value EXISTING 7.5.16 Create Validation – First Name Not Null Create another validation. This validation will check whether the first name of a new customer is provided. It is fired only when the New Customer option is selected. Property Value Name First Name is Not Null Sequence 20 Type (Validation) Item is NOT NULL Item P11_CUST_FIRST_NAME Error Message #LABEL# must have some value. Associated Item P11_CUST_FIRST_NAME Type (Server-side Condition) Item = Value Item P11_CUSTOMER_OPTIONS Value NEW Using the previous table, create NOT NULL validations for Last Name, State, Postal Code, and Credit Limit items. 7.5.17 Create Validation – Phone Number Create the following validation to check input of proper phone numbers.Regular Expressions enable you to search for patterns in string data by using standardized syntax conventions, rather than just a straight character comparisons. The validation passes if the phone numbers matches the regular expression attribute and fails if the item value does not match the regular expression. The last three properties inform Oracle APEX to execute the validation only when a new customer is created. Property Value Name Phone Number Format Type (Validation) Item matches Regular Expression Item P11_PHONE_NUMBER1 Regular Expression ^(?[[:digit:]]{3})?[-. ][[:digit:]]{3}[-. ][[:digit:]]{4}$ Error Message Phone number format not recognized Associated Item P11_PHONE_NUMBER1 Type (Server-side Condition) Item = Value Item P11_CUSTOMER_OPTIONS Value NEW Create a similar validation for P11_PHONE_NUMBER2 item. Next, you have to turn off the Value Required attribute for P11_CUSTOMER_ID (in Existing Customer region) , and P11_CUST_FIRST_NAME, P11_CUST_LAST_NAME, P11_CUST_STATE, P11_CUST_POSTAL_CODE, P11_CREDIT_LIMIT and P11_CUST_EMAIL (in New Customer Details region). The Value Required properties for these items were inherited from Page 7 where they were set to On, to mark them as mandatory. In the previous two sections, you used an alternate method to manually control the validation process for these items. If you don’t reverse the Value Required status, then the application will throw NOT NULL errors for these items, even if you select an existing customer. 7.5.18 Create Branch When the Next button is clicked, the defined button action (Submit Page) triggers after performing all validations. The submit page process executes instructions specified in this branch and moves the user to the next order wizard step. On the Processing tab, right-click the After Processing node and select Create Branch. Set the following properties for the new branch. Property ValueName Go To Page 12 Type (under Behavior) Page or URL (Redirect) Target Type = Page in this Application Page = 12 Clear Cache = 12 When Button Pressed NEXT Test Your Work From the main menu, select Orders and click the Enter New Order button. Your page should look like Figure 7-23. Select Existing Customer and click the LOV button to call list of customers. Click the name of a customer from the list. The name of the selected customer appears in the Customer box. This is how an existing customer is selected for an order. Now, click the New Customer option, the Dynamic Action created in section 7.5.13 invokes and performs two actions. First, it hides the Customer box and the LOV. Second, it shows a form similar to the one you created in Chapter 5 to add a new customer record. Click the Next button without putting any value in the provided form. An inline message box will appear with six errors. This is the procedure you handled in the validation sections. After correcting all the form errors if you click Next, the message “Sorry, this page isn't available“ pops up indicating that Page 12 doesn’t exist. Your next task is to create Page 12 where you’ll select products for an order.

7.6 Create Select Items Page - Pages 12

Having identified the customer, the second step in the order wizard is to add products to the order. In this exercise, you will create Page 12 of the application to select ordered items and input the required quantities.

1. Click the Create Page button in the App Builder interface.
2. This time, select the Blank Page option. This option is selected to create an application page from scratch. Using this option you can create and customize a page according to your own specific needs.
3. Complete the first Page Attributes screen as show in the following figure and click Next.Figure 7-26
4. On the Navigation Menu screen, set Navigation Preference to Identify an existing navigation menu entry for this page, Existing Navigation Menu Entry to Orders, and click Next.
5. Click Finish to end the wizard.

   7.6.1 Modify Page Properties

   You styled the Detail View of an interactive report in the previous chapter to customize its look. Here as well, you will apply some styling rules to give the page a professional touch. Previously, you added rules to a single page element: HTML table. In the following exercise you’ll apply rules to the whole page. Before getting your feet wet, go through the following topic to understand Cascading Style Sheets (CSS). Cascading Style Sheets A cascading style sheet (CSS) provides a way to control the style of a web page without changing its structure. When used properly, a CSS separates visual properties such as color, margins, and fonts from the structure of the HTML document. In this chapter, you will use CSS to style Page 12 (Select Items - Figure 7- 27). On this page you will add class properties to PL/SQL code and will reference them in CSS in the HTML Head section. Before moving on to understand the actual functionality, let’s first take a look at a simple example on how to use class attribute in an HTML document. The class attribute is mostly used to point to a class in a style sheet. The syntax is

   <element class="classname">.
   <html><head>
   <style type="text/css">
   h1.header {color:blue;}
   p.styledpara {color:red;}
   </style>
   </head>
   <body>
   <h1 class="header">Class Referenced in CSS</h1>
   <p>A normal paragraph.</p>
   <p class="styledpara">Note that this is an important paragraph.</p>
   </body>
   </html>
   The body of this web page contains three sections:
   <h1 class="header">Class Referenced in CSS</h1>

   The text “Class Referenced in CSS” is enclosed in h1 html tag. It is called level 1 heading and is the most important heading in a document. It is usually used to indicate the title of the document. The text is preceded by a class named “header”. Considering the class syntax, h1 is the element and header is the classname. This class is referenced in the style section using a CSS rule – h1.header {color:blue;} – to present the heading in blue color. A CSS rule has two main parts: a selector and one or more declarations. The selector is normally the HTML element you want to style. Each declaration consists of a property and a value. The property is the style attribute you want to change. Each property has a value. In the h1.header {color:blue;} rule, h1 is the selector, header is the classname, and {color:blue;} is the declaration.

   A normal paragraph.

   – It is a plain paragraph without any style applied to it. HTML documents are divided into paragraphs and paragraphs are defined with the

   tag. The

   tag is called the start tag or opening tag, while

   is called the end or closing tag.

   Note that this is an importantparagraph.

   – It is a paragraph with a class named “styledpara”. In the style section, the selector “p” followed by the classname “styledpara” with the declaration{color:red;} is referencing this section to present the paragraph text in red color. Now that you have understood how CSS is used in web pages, let’s figure out how it is used in Oracle APEX.
6. Click the root node – Page 12: Order Items.
7. Set Dialog Template to Wizard Modal Dialog.
8. Set Width and Height to 500 and 600, respectively.
9. Enter the following code for inline property under CSS section and

save your work. You can find this code in BookCode\Chapter7\7.6.1.txt file. CSS rules entered in this box will be applied to all the referenced elements on the current page, as illustrated in Figure 7-27.

div.CustomerInfo{margin: 10px 10px 0;}
div.CustomerInfo strong{font:bold 12px/16px Arial,sans-serif;display:block;width:120px;}
div.CustomerInfo p{display:block;margin:0; font: normal 12px/16px Arial, sans-serif;}
div.Products{clear:both;margin:16px 0 0 0;padding:0 8px 0 0;}
div.Products table{border:1px solid #CCC;border-bottom:none;}
div.Products table th{background-color:#DDD;color:#000;font:bold 12px/16px Arial,sans-serif;padding:4px 10px;text-align:right;border-bottom:1px solid #CCC;}
div.Products table td{border-bottom:1px solid #CCC;font:normal 12px/16px Arial,sans-serif; padding:4px 10px;text-align:right;}
div.Products table td a{color:#000;}
div.Products .left{text-align:left;}
div.CartItem{padding:8px 8px 0 8px;font:normal 11px/14px Arial,sans-serif;} 
div.CartItem a{color:#000;} 
div.CartItem span{display:block;text-align:right;padding:8px 0 0 0;} 
div.CartItem span.subtotal{font-weight:bold;} 
div.CartTotal{margin-top:8px;padding:8px;border-top:1px dotted #AAA;} 
div.CartTotal span{display:block;text-align:right;font:normal 11px/14px Arial,sans-serif;padding:0 0 4px 0;} 
div.CartTotal p{padding:0;margin:0;font:normal 11px/14px Arial,sans-serif;position:relative;} 
div.CartTotal p.CartTotal{font:bold 12px/14px Arial,sans-serif;padding:8px 0 0 0;} 
div.CartTotal p.CartTotal span{font:bold 12px/14px Arial,sans-serif;padding:8px 0 0 0;} 
div.CartTotal p span{padding:0;position:absolute;right:0;top:0;} 

7.6.2 Create Region – Order Progress

Right-click the Wizard Progress Bar node and select Create Region. Set the following properties for the new region. A similar region was added previously to Page 11 to display the Order Progress bar. Property ValueTitle Order Progress Type List List Order Wizard Template Blank with Attributes List Template (under Attributes node) Wizard Progress

7.6.3 Create Region – Select Items

The region being created in this section is based on a custom PL/SQL code. The code references CSS rules (defined in the previous section) to design the Select Items page, as illustrated in Figure 7-27.

What is PL/SQL?

PL/SQL stands for Procedural Language/Structured Query Language. It is a programming language that uses detailed sequential instructions to process data. A PL/SQL program combines SQL command (such as Select and Update) with procedural commands for tasks, such as manipulating variable values, evaluating IF/THEN logic structure, and creating loop structures that repeat instructions multiple times until the condition satisfies the defined criteria. PL/SQL was expressly designed for this purpose. The structure of a PL/SQL program block is:

Declare
   Variable declaration
Begin
   Program statements
Exception
   Error-handling statements
End;

PL/SQL program variables are declared in the program’s declaration section. The beginning of the declaration section is marked with the reserved word DECLARE . You can declare multiple variables in the declaration section. The body of a PL/SQL block consists of program statements, which can be assigned statements, conditional statements, loop statements, and so on. The body lies between the BEGIN and EXCEPTION statements. The exception section contains program statements for error handling. Finally, PL/SQL programs end with the END; statement. Comments in PL/SQL code are added by prefixing them with double hyphens.In a PL/SQL program block, the DECLARE and EXCEPTION sections are optional. If there are no variables to declare, you can omit the DECLARE section and start the program with the BEGIN command.

1. Right-click the Order Progress region and select Create Sub Region from the context menu.
2. Enter Select Items for its Title and set its Type to PL/SQL Dynamic Content to display the page content using PL/SQL code. PL/SQL Dynamic Content displays the HTML output from the PL/SQL code.
3. Add the code defined in the PL/SQL Code column (Table 7-1) in the PL/SQL Code property (in the Source section). You can find this code in BookCode\Chapter7\7.6.3.txt file. The first column (CSS Rule) in the following table references the rules defined in the previous section. These rules are applied to the injected HTML elements in the PL/SQL code. The second table column is populated with a serial number assigned to each PL/SQL code. These numbers are referenced in the explanation section underneath.
4. Do not set any option for the Template property (in other words, change it from the default Standard value to the -Select- placeholder).

declare
  l_customer_id varchar2(30) := :P11_CUSTOMER_ID;
begin
--
-- display customer information
--
sys.htp.p('<div class="CustomerInfo">');
if :P11_CUSTOMER_OPTIONS = 'EXISTING' then
  for x in (select * from demo_customers where customer_id = l_customer_id) loop
    sys.htp.p('<div class="CustomerInfo">');
    sys.htp.p('<strong>Customer:</strong>');  
    sys.htp.p('<p>');
    sys.htp.p(sys.htf.escape_sc(x.cust_first_name) || ' ' ||
    sys.htf.escape_sc(x.cust_last_name) || '<br />');
    sys.htp.p(sys.htf.escape_sc(x.cust_street_address1) || '<br />');
    if x.cust_street_address2 is not null then
      sys.htp.p(sys.htf.escape_sc(x.cust_street_address2) || '<br />');        
    end if;
    sys.htp.p(sys.htf.escape_sc(x.cust_city) || ', ' ||
    sys.htf.escape_sc(x.cust_state) || '  ' ||
    sys.htf.escape_sc(x.cust_postal_code));
    sys.htp.p('</p>');
  end loop;
else
  sys.htp.p('<strong>Customer:</strong>');  
  sys.htp.p('<p>');
  sys.htp.p(sys.htf.escape_sc(:P11_CUST_FIRST_NAME) || ' ' ||
                sys.htf.escape_sc(:P11_CUST_LAST_NAME) || '<br />');
  sys.htp.p(sys.htf.escape_sc(:P11_CUST_STREET_ADDRESS1) || '<br />');
  if :P11_CUST_STREET_ADDRESS2 is not null then
    sys.htp.p(sys.htf.escape_sc(:P11_CUST_STREET_ADDRESS2) || '<br />');    
  end if;
  sys.htp.p(sys.htf.escape_sc(:P11_CUST_CITY) || ', ' ||
  sys.htf.escape_sc(:P11_CUST_STATE) || '  ' ||
  sys.htf.escape_sc(:P11_CUST_POSTAL_CODE));
  sys.htp.p('</p>');
end if;
sys.htp.p('</div>');

-- display products
--
sys.htp.p('<div class="Products" >');
sys.htp.p('<table width="100%" cellspacing="0" cellpadding="0" border="0">
<thead>
<tr><th class="left">Product</th><th>Price</th><th></th></tr>
</thead>
<tbody>');
for c1 in (select product_id, product_name,  list_price, 'Add to Cart' add_to_order
from demo_product_info
where product_avail = 'Y'
order by product_name) loop
sys.htp.p('<tr><td class="left">'||sys.htf.escape_sc(c1.product_name)||'</td>
<td>'||trim(to_char(c1.list_price,'999G999G990D00')) || '</td>
<td><a href="'||apex_util.prepare_url('f?p=&APP_ID.:12:'||:app_session||':ADD:::P12_PRODUCT_ID:'|| c1.product_id)||'" class="t-Button t-Button--simple t-Button--hot"><span>Add<i class="iR"></i></span></a></td>
</tr>');
end loop;
sys.htp.p('</tbody></table>');
sys.htp.p('</div>');

--
-- display current order
--
sys.htp.p('<div class="Products" >');
sys.htp.p('<table width="100%" cellspacing="0" cellpadding="0" border="0">
<thead>
<tr><th class="left">Current Order</th></tr>
</thead>
</table>
<table width="100%" cellspacing="0" cellpadding="0" border="0">
<tbody>');
declare
    c number := 0; t number := 0;
begin
-- loop over cart values
for c1 in (select c001 pid, c002 i, to_number(c003) p, count(c002) q, sum(c003) ep,  'Remove' remove
from apex_collections
where collection_name = 'ORDER'
group by c001, c002, c003
order by c002)
loop
sys.htp.p('<div class="CartItem">
<a href="'||apex_util.prepare_url('f?p=&APP_ID.:12:&SESSION.:REMOVE:::P12_PRODUCT_ID:'||sys.htf.escape_sc(c1.pid))||'"><img src="#IMAGE_PREFIX#delete.gif" alt="Remove from cart" title="Remove from cart" /></a>  
'||sys.htf.escape_sc(c1.i)||'
<span>'||trim(to_char(c1.p,'$999G999G999D00'))||'</span>
<span>Quantity: '||c1.q||'</span>
<span class="subtotal">Subtotal: '||trim(to_char(c1.ep,'$999G999G999D00'))||'</span>
</div>');
c := c + 1;
t := t + c1.ep;
end loop;
sys.htp.p('</tbody></table>');
if c > 0 then
    sys.htp.p('<div class="CartTotal">
    <p>Items: <span>'||c||'</span></p>
    <p class="CartTotal">Total: <span>'||trim(to_char(t,'$999G999G999D00'))||'</span></p>
    </div>');
else
   sys.htp.p('<div class="alertMessage info" style="margin-top: 8px;">');
     sys.htp.p('<img src="#IMAGE_PREFIX#f_spacer.gif">');
     sys.htp.p('<div class="innerMessage">');
       sys.htp.p('<h3>Note</h3>');
       sys.htp.p('<p>You have no items in your current order.</p>');
     sys.htp.p('</div>');
   sys.htp.p('</div>');
end if;
end;
sys.htp.p('</div>');
end;

NOTE: The ELSE block (lines 70-76) executes when the user tries to move on without selecting a product in the current order. The block uses a built-in class (alertMessage info) that carries an image (f_spacer.gif) followed by the message specified on lines 73-74. In this PL/SQL code you merged some HTML elements to deliver the page in your browser. Before getting into the code details, let’s first acquaint ourselves with some specific terms and objects used in the PL/SQL code. Using HTML in PL/SQL Code Oracle APEX installs with your Oracle database and is comprised of data in tables and PL/SQL code. Whether you are running the Oracle APEX development environment or an application you built using Oracle APEX, the process is the same. Your browser sends a URL request, which is translated into an appropriate Oracle APEX PL/SQL call. After the database processes the PL/SQL, the results are relayed back to your browser as HTML. This cycle happens each time you either request or submit a page. Specific HTML content not handled by Oracle APEX (forms, reports, and charts) are generated using the PL/SQL region type. You can use PL/SQL to have more control over dynamically generated HTML within a region, as you do here. Let’s see how these two core technologies are used together.

htp and htf Packages:

htp (hypertext procedures) and htf (hypertext functions) are part of PL/SQL Web Toolkit package to generate HTML tags. These packages translate PL/SQL into HTML understood by a web browser. For instance, the htp.anchor procedure generates the HTML anchor tag a. The following PL/SQL block generate a simple HTML document:

CREATE OR REPLACE PROCEDURE hello AS
BEGIN
htp.htmlopen;              -- generates <HTML>
htp.headopen;             -- generates <HEAD>
htp.title('Hello');          -- generates <TITLE>Hello</TITLE>
htp.headclose;             -- generates </HEAD>
htp.bodyopen;             -- generates <BODY>
htp.header(1, 'Hello'); -- generates <H1>Hello</H1>
htp.bodyclose;             -- generates </BODY>
htp.htmlclose;              -- generates </HTML>
END;

Oracle provided the htp.p tag to allow you to override any PL/SQL-HTML procedure or even a tag that did not exist. If a developer wishes to use a new HTML tag or simply is unaware of the PL/SQL analog to the html tag, s/he can use the htp.p procedure. For every htp procedure that generates HTML tags, there is a corresponding htf function with identical parameters. The function versions do not directly generate output in your web page. Instead, they pass their output as return values to the statements that invoked them.

htp.p / htp.print: Generates the specified parameter as a string

htp.p(‘<p>’):
Indicates that the text coming after the tag is to be formatted as a paragraph
<strong>Customer:</strong>:
Renders the text they surround in bold
htf.escape_sc:
Escape_sc is a function, which replaces characters that have special meaning
in HTML with their escape sequence.
converts occurrence of & to &
converts occurrence of “ to "
converts occurrence of < to <
converts occurrence of > to >

To prevent XSS (Cross Site Scripting) attacks, you must call SYS.HTF.ESCAPE_SC to prevent embedded JavaScript code from being executed when you inject the string into an HTML page. The SYS prefix isused to signify Oracle’s SYS schema. The HTP and HTF packages normally exist in the SYS schema and Oracle APEX relies on them.

Cursor FOR LOOP Statement

The cursor FOR LOOP statement implicitly declares its loop index as a record variable of the row type that a specified cursor returns and then opens a cursor. With each iteration, the cursor FOR LOOP statement fetches a row from the result set into the record. When there are no more rows to fetch, the cursor FOR LOOP statement closes the cursor. The cursor also closes if a statement inside the loop transfers control outside the loop or raises an exception.

The cursor FOR LOOP statement lets you run a SELECT statement and then immediately loop through the rows of the result set. This statement can use either an implicit or explicit cursor. If you use the SELECT statement only in the cursor FOR LOOP statement, then specify the SELECT statement inside the cursor FOR LOOP statement, as in Example A. This form of the cursor FOR LOOP statement uses an implicit cursor and is called an implicit cursor FOR LOOP statement.

Because the implicit cursor is internal to the statement, you cannot reference it with the name SQL.

Example A - Implicit Cursor FOR LOOP Statement

BEGIN
FOR item IN (
SELECT last_name, job_id
FROM employees
WHERE job_id LIKE '%CLERK%' AND manager_id > 120
ORDER BY last_name
)
LOOP
DBMS_OUTPUT.PUT_LINE ('Name = ' || item.last_name || ', Job = ' || item.job_id);
END LOOP;
END;
/

If you use the SELECT statement multiple times in the same PL/SQL unit, define an explicit cursor for it and specify that cursor in the cursor FOR LOOP statement, as shown in Example B. This form of the cursor FOR LOOP statement is called an explicit cursor FOR LOOP statement. You can use the same explicit cursor elsewhere in the same PL/SQL unit.

Example B - Explicit Cursor FOR LOOP Statement

DECLARE
CURSOR c1 IS
SELECT last_name, job_id FROM employees
WHERE job_id LIKE '%CLERK%' AND manager_id > 120
ORDER BY last_name;
BEGIN
FOR item IN c1
LOOP
DBMS_OUTPUT.PUT_LINE ('Name = ' || item.last_name || ', Job = ' || item.job_id);
END LOOP;
END;
/

TABLE 7-1 PL/SQL CODE EXPLAINED

Display Customer Information (Lines 7-32)

This procedure fetches information of the selected customer and presents it in a desirable format (as shown in Figure 7-27) using the CSS rules defined under the class CustomerInfo.

Declare (Line: 1)

This is the parent PL/SQL block. A nested block is also used under the Display Current Order section on line:48.l_customer_id varchar2(30) := :P11_CUSTOMER_ID; (Line: 2) Assigns customer ID, which is retrieved from the previous order wizard step (Page 11), to the variable l_customer_id. This variable is used in a SQL statement (on Line No. 9) to fetch details of the selected customer. In PL/SQL, the symbol := is called the assignment operator. The variable, which is being assigned the new value, is placed on the left side of the assignment operator and the value is placed on the right side of the operator.

:P11_CUSTOMER_ID is called a bind variable. Bind variables are substituion variables that are used in place of literals. You can use bind variables syntax anywhere in Oracle APEX where you are using SQL or PL/SQL to reference session state of a specified item. For example:

SELECT * FROM employees WHERE last_name like '%' || :P99_SEARCH_STRING || '%' 

In this example, the search string is a page item. If the region type is defined as SQL Query, then you can reference the value using standard SQL bind variable syntax. Using bind variables ensures that parsed representations of SQL queries are reused by the database, optimizing memory usage by the server.

The use of bind variables is encouraged in Oracle APEX. Bind variables help you protect your Oracle APEX application from SQL injection attacks. Bind variables work in much the same way as passing data to a stored procedure. Bind variables automatically treat all input data as “flat” data and never mistake it for SQL code. Besides the prevention of SQL injection attacks, there are other performance-related benefits to its use.

You declare a page item as a bind variable by prefixing a colon character (:) like this:

:P11_CUSTOMER_OPTIONS.

When using bind variable syntax, remember the following rules:

1. Bind variable names must correspond to an item name
2. Bind variable names are not case-sensitive
3. Bind variable names cannot be longer than 30 characters

Although page item and application item names can be up to 255 characters, if you intend to use an application item within SQL using bind variablesyntax, the item name must be 30 characters or less.

Begin (Line: 3) Read What is PL/SQL at the beginning of this section.

The code block from line number 7 to 32 creates the first section on the page (marked as A in Figure 7-27) using div HTML element and styles it using Rule 1 and 2. The code between lines 9-20 is executed when the user selects an existing customer from the previous wizard step.

sys.htp.p('<div class="CustomerInfo">'); (Line: 7)
The <div> tag defines a division or a section in an HTML document. This is
the  opening  tag,  which  references  the  CustomerInfo  class  in  CSS  rules  to
format the following elements. The ending tag is defined on Line 32.
for  x  in  (select  *  from  demo_customers  where  customer_id  =
l_customer_id) loop (Line: 9)
Initiates  the  FOR  loop  to  locate  and  fetch  record  of  the  selected  customer
from the demo_customers table.
sys.htp.p('<strong>Customer:</strong>'); (Line: 11)
Displays the label “Customer:” in bold.
sys.htp.p('<p>'); (Line: 12)
The paragraph opening tag. It ends on Line 19.
sys.htp.p(sys.htf.escape_sc(x.cust_first_name) || ' '
||sys.htf.escape_sc(x.cust_last_name) || '<br />'); (Line: 13)
Concatenates  customer’s  first  and  last  names  using  the  concatenation
characters (||). The <br /> tag inserts a single line break.
sys.htp.p(sys.htf.escape_sc(x.cust_street_address1) || '<br />'); (Line: 14)
Show customer’s first address on a new line.
if x.cust_street_address2 is not null then  (Lines: 15-17)
sys.htp.p(sys.htf.escape_sc(x.cust_street_address2) || '<br />');
end if;
It’s a condition to check whether the customer’s second address is not null. If
it’s not, print it on a new line.
sys.htp.p(sys.htf.escape_sc(x.cust_city)  ||  ',  '  ||
sys.htf.escapte_sc(x.cust_state)  ||  '    '  ||sys.htf.escape_sc(x.cust_postal_code)); (Line: 18)
Displays  city,  state,  and  postal  code  data  on  the  same  row  separating  each
other with a comma and a blank space.
sys.htp.p('</p>'); (Line: 19)
The paragraph end tag.
end loop; (Line: 20)
The loop terminates here after fetching details of an existing customer from
the database table.
sys.htp.p('</div>'); (Line: 32)
The div tag terminates here. The output of this section is illustrated in Figure
7-27:  A  -  CustomerInfo.  The  ELSE  block  (line  22-30)  is  executed  when  a
new  customer  is  added  to  the  database  from  the  order  interface.  In  that
situation, all values on the current page are fetched from the previous wizard
step (Page 11).
Display Products (Lines: 36-42)
Here you create a section on your web page to display all products along with
their prices and include an option, which allows users to add products to their
cart.
sys.htp.p('<div class="Products" >'); (Line: 36)
Creates a division based on the Products class. HTML elements under this
division are styled using rules 4-9.
sys.htp.p('<table  width="100%"  cellspacing="0"  cellpadding="0"
border="0"> (Line: 37)
Here you are initiating to draw an HTML table. The <table> tag defines an
HTML  table.  An  HTML  table  consists  of  the  <table>  element  and  one  or
more <tr>, <th>, and <td> elements. The <tr> element defines a table row,
the <th> element defines a table header, and the <td> element defines a table
cell. The Width attribute specifies the width of the table. Setting 100% width
instructs  the  browser  to  consume  the  full  screen  width  to  display  the  table
element.
<thead> (Line: 37)
<tr><th class="left">Product</th><th>Price</th><th></th></tr>
</thead>The  <thead>  tag  is  used  to  group  header  content  in  an  HTML  table.  The
<thead>  element  is  used  in  conjunction  with  the  <tbody>  and  <tfoot>
elements to specify each part of a table (header, body, footer). The <tr> tag
creates a row for column heading. The three <th> tags specify the headings.
The first two columns are labeled Product and Price, respectively. The third
column heading is left blank. A specific declaration (class=”left”) is included
that points toward the CSS rule (9) div.Products .left{text-align:left;} to align
the title of the first column (Product) to the left. The second column (Price) is
styled using a general rule (6).
<tbody>’); (Line: 37)
The <tbody> tag is used to group the body content in an HTML table. This
section spans up to line 41 and is marked as B in Figure 7-27.
for  c1  in  (select  product_id,  product_name,    list_price,  'Add  to  Cart'
add_to_order
from demo_product_info
where product_avail = 'Y'
order by product_name) loop  (Line: 38)
The  FOR  loop  fetches  Product  ID,  Product  Name,  and  List  Price  columns
from the products table. To display a  button (Add) in the table, we appended
a column aliased add_to_order and populated all rows with a constant value
'Add to Cart'. For further information on FOR LOOP, see the Cursor  FOR
LOOP Statement section earlier in this section.
sys.htp.p('<tr><td  class="left">'
||sys.htf.escape_sc(c1.product_name)||'</td>
<td>'||trim(to_char(c1.list_price,'999G999G990D00')
|| '</td>
<td><a  href="  '||apex_util.prepare_url('f?
p=&APP_ID.:12:'||:app_session||'
:ADD:::P12_PRODUCT_ID:'||
c1.product_id)||' "
class="t-Button  t-Button--simple
t-Button--hot">
<span>Add<i  class="iR"></i>
</span></a></td>
</tr>'); (Line: 39)

This line displays product names with respective prices in two separate columns. The product column is styled using Rule 9, while the price column is styled using Rule 6. There is an Add button in the third column of the table, which is presented as a link using the HTML anchor tag \ and is styled #using a built-in class (t-Button). An anchor can be used in two ways:

It is usually referred to as a link or a hyperlink. The most important attribute of \ element is the href attribute, which specifies the URL of the page to which the link goes. When this button is clicked, the product it represents is moved to the Current Order section with the help of a process (Add Product to the Order Collection) defined in section 7.6.7.

SELECT value FROM v$nls_parameters
WHERE parameter='NLS_NUMERIC_CHARACTERS';
The code,
<a href="'||apex_util.prepare_url('f?p=&APP_ID.:12:'||:app_session||':ADD:::P12_PRODUCT_ID:'||
c1.product_id)||'" class="t-Button t-Button--simple t-Button--hot"> <span>Add<iclass="iR"></i>
</span></a>,

for x in (select p.rowid, p.* from demo_product_info p where product_id=:P12_PRODUCT_ID)
loop
select count(*)
into l_count
from wwv_flow_collections
where collection_name = 'ORDER'
and c001 =  x.product_id;
if l_count >= 10 then
exit;
end if;
apex_collection.add_member(p_collection_name => 'ORDER',
p_c001 => x.product_id,
p_c002 => x.product_name,
p_c003 => x.list_price,
p_c004 => 1,
p_c010 => x.rowid);
end loop;

During the process creation, you’ll select Request=Value in Condition Type and will enter ADD for Value. The ADD request in the \ tag is referencing the same expression. When a user clicks the ADD button on the web page, the URL sends the ADD request to the process along with the selected product ID using a hidden item named P12_PRODUCT_ID to be created in section 7.6.4. In turn, the process adds the product to the Current Order section. The URL generated from this code looks something like this at runtime:

f?p=18132:12:13238397476902:ADD:::P12_PRODUCT_ID:10end loop; (Line: 40)
End of FOR loop.
sys.htp.p('</tbody></table>'); (Line: 41)
Table and body closing tags.
sys.htp.p('</div>'); (Line: 42)
The closing div tag.

Display Current Order (Lines: 46-79)
This  section  acts  as  a  shopping  cart.  The  products  selected  by  a  user  are
placed in this section.
sys.htp.p('<div class="Products" >'); (Line: 46)
Defines the <div> tag and utilizes the Products class referenced in rules 4-9.
sys.htp.p('<table  width="100%"  cellspacing="0"  cellpadding="0"
border="0">
<thead>
<tr><th class="left">Current Order</th></tr>
</thead>
</table> (Line: 47)

Displays section heading as follows in the first row of a separate table.
Declare (Line: 48)
This is a nested or child block. To nest a block means to embed one or more
PL/SQL  block  inside  another  PL/SQL  block  to  have  better  control  over
program’s execution.
c number := 0; t number := 0; (Line: 49)
Declared two numeric counter variables and initialized them with zero. The
variable c is used to evaluate whether any product is selected in the current
order, while the variable t stores total value for the order.
Begin (Line: 50)
for  c1  in  (select  c001  pid,  c002  i,  to_number(c003)  p,  count(c002)  q,
sum(c003) ep,  'Remove' remove
from apex_collections
where collection_name = 'ORDER'group by c001, c002, c003
order by c001)
loop (Line: 52)

COLLECTION_NAME    NOT NULL VARCHAR2(255)
SEQ_ID             NOT NULL NUMBER
C001               VARCHAR2(4000)
C002               VARCHAR2(4000)
C003               VARCHAR2(4000)  
C004               VARCHAR2(4000)  
C005               VARCHAR2(4000)
...
C050               VARCHAR2(4000)
N001               NUMBER
N002               NUMBER
N003               NUMBER
N004               NUMBER
N005               NUMBER    
CLOB001            CLOB
BLOB001            BLOB
XMLTYPE001         XMLTYPEMD5_ORIGINAL       VARCHAR2(4000)

SELECT c001, c002, c003, n001, clob001  
FROM APEX_collections
WHERE collection_name = 'DEPARTMENTS'

apex_collection.create_or_truncate_collection (p_collection_name => 'ORDER');

Column Corresponds To
C001 – pid Product ID (9)
C002 – i Product Name (Men Shoes)
C003 – p List Price (110)
C002 - q Quantity (1)
C003 - ep Extended Price (110) This value will increase with each Add button click to accumulate total cost of a product.

sys.htp.p('<div class="CartItem"> (Line: 53)
This line references another class (CartItem) to style the actual Current Order
section.
<a href="'||apex_util.prepare_url('f?
p=&APP_ID.:12:&SESSION.:REMOVE:::P12_PRODUCT_ID:'||sys.htf.
<img src="#IMAGE_PREFIX#delete.gif" alt="Remove from cart"
title="Remove from cart" />
</a>   (Line: 54)

\ tag creates a link with a REMOVE request. This time, it usesproduct ID from the collection. In section 7.6.7 (B), there is a process named

Remove product from the Order Collection (as shown below) where the request expression is set to REMOVE.

for x in
(select seq_id, c001 from apex_collections
where collection_name = 'ORDER' and c001 = :P12_PRODUCT_ID)
loop
apex_collection.delete_member(p_collection_name => 'ORDER', p_seq => x.seq_id);
end loop;

In HTML, images are defined with the \ tag. The \ tag has no closing tag. To display an image on a page, you need to use the src attribute.

Src stands for "source". The value of the src attribute is the URL of the image you want to display.

Syntax for defining an image:

The URL points to the location where the image is stored. The value of IMAGE_PREFIX determines the virtual path the web server uses to point to the images directory distributed with Oracle APEX. We used “delete.gif” that is displayed in front of the product name. The required alt attribute specifies an alternate text for an image, if the image cannot be displayed. When a user clicks the remove link [X] in the Current Order section, the URL sends a REMOVE request to the process along with the product ID. The DELETE_MEMBER procedure deletes a specified member from a given named collection using the p_seq => x.seq_id parameter, which is the sequence ID of the collection member to be deleted.

'||sys.htf.escape_sc(c1.i)||' (Line: 55)
Displays name of the selected product in the Current Order section.  
<span>'||trim(to_char(c1.p,'$999G999G999D00'))||'</span> (Line: 56)
<span>Quantity: '||c1.q||'</span> (Line: 57)
<span class="subtotal">Subtotal:
'||trim(to_char(c1.ep,'$999G999G999D00'))||'</span> (Line: 58)
The three lines display price, quantity, and sub-total of the selected product in
the Current Order section, as shown below:</div>'); (Line: 59)
The ending div tag.
c := c + 1; (Line: 60)
This counter increments the value of c with 1 at the end of each loop. The
variable c is used to calculate number of items selected in the current order.
t := t + c1.ep; (Line: 61)
Similar  to  the  variable  c,  t  is  also  incremented  to  sum  up  extended  price
(c1.ep) to calculate total order value.

if c > 0 then
sys.htp.p('<div class="CartTotal">
<p>Items: <span>'||c||'</span></p>
<p class="CartTotal">Total:
<span>'||trim(to_char(t,'$999G999G999D00'))||'</span></p>
</div>');
else
   sys.htp.p('<div class="alertMessage
info" style="margin-top: 8px;">');
sys.htp.p('<img src="#IMAGE_PREFIX#f_spacer.gif">');
       sys.htp.p('<div class="innerMessage">');
sys.htp.p('<h3>Note</h3>');
sys.htp.p('<p>You have no items in your current order.</p>');
       sys.htp.p('</div>');
   sys.htp.p('</div>');
end if;

(Line: 64-77) The condition (IF c > 0) evaluates whether a product is selected in the current order. A value other than zero in this variable indicates addition of product(s). If the current order has some items added, the label Total: along with the value is displayed, which is stored in the variable t. If no items are selected, the message defined in the else block is shown using a couple of built-in classes.

7.6.4 Create Hidden Item

Create a hidden item in the Select Items region. When you click the Add button on Page 12 to add a product to an order, the ID of that product is stored in this hidden item using a URL specified in the PL/SQL code on line 39.

Property Value

1. Name P12_PRODUCT_ID
2. Type Hidden

7.6.5 Create Region to hold Buttons

Right-click the Wizard Buttons node and select Create Region. Enter Buttons for the Title of this region and set its Template to Buttons Container. The region will hold three buttons: Cancel, Previous, and Next. These buttons are created in the next section.

7.6.6 Create Buttons

All the three buttons created in this section have one thing in common, the Action property, which is set to Submit Page. When you click any of these three buttons, the page is submitted and a corresponding branch (to be created in section 7.6.9) is fired to take you to the specified location. For example, if you click the Cancel button, the corresponding branch takes you back to the main Orders page (Page 4). Right-click the new Buttons region and select Create Button. Set the following properties for the new button:

Property Value

1. Button Name CANCEL
2. Label Cancel
3. Button Position Close
4. Action Submit Page

Create another button under the Cancel button and set the following properties:

Property Value

1. Button Name PREVIOUS
2. Label Previous
3. Button Position Previous
4. Button Template Icon
5. Icon fa-chevron-left
6. Action Submit Page

Create the final button under the Previous button and set the following properties:

Property Value

1. Button Name NEXT
2. Label Place Order
3. Button Position Next
4. Button Template Text with Icon
5. Hot On
6. Icon fa-chevron-right
7. Action Submit Page

7.6.7 Create Processes

The two processes created in this section handle the routine to either add a product to the Current Order section or remove one from it. The add_member function references the collection (ORDER created in section 7.5.12) to populate the collection with a new product. In Table 7-1, the link defined on line 39 in the PL/SQL code forwards an ADD request, which is entertained here after evaluating the request in step 4 below.

A. Add Product to the Order Collection

1. On Page 12, expand the Pre-Rendering node (on the Rendering tab) and create a process under Before Header node.
2. Enter Add Product to the ORDER Collection for the name of this new process and set its Type to PL/SQL Code.Figure 7-28
3. Enter the following code in the PL/SQL Code box. Locate this code under BookCode\Chapter7\7.6.7A.txt file.

   declare
   l_count number := 0;
   begin
   for x in (select p.rowid, p.* from demo_product_info p where product_id = :P12_PRODUCT_ID)
   loop
   select count(*) 
   into l_count
   from wwv_flow_collections
   where collection_name = 'ORDER'
   and c001 =  x.product_id;
   if l_count >= 10 then
   exit;
   end if;
   apex_collection.add_member(p_collection_name => 'ORDER', 
   p_c001 => x.product_id, 
   p_c002 => x.product_name,
   p_c003 => x.list_price,
   p_c004 => 1,
   p_c010 => x.rowid);
   end loop;
   end;

4. In Server-side Condition section, set Type to Request=Value, and enter ADD in the Value property box.

B. Remove Product from the Order Collection

The delete_member function is just opposite to the add_member function. It is called by a link (Table 7-1 line 54), which carries a REMOVE request. Therequest is evaluated by a condition set in Step 3 below. If the request matches, the selected product is deleted from the ORDER collection.

1. Create another process under the previous one. Name it Remove Product from the ORDER Collection and set its Type to PL/SQL Code.
2. Enter the following code in the PL/SQL Code property box. Get this code from BookCode\Chapter7\7.6.7B.txt file.

   for x in 
   (select seq_id, c001 from apex_collections 
   where collection_name = 'ORDER' and c001 = :P12_PRODUCT_ID)
   loop
   apex_collection.delete_member(p_collection_name => 'ORDER', p_seq => x.seq_id);
   end loop;

3. In Server-side Condition section, set Type to Request=Value, and enter REMOVE in the Value property box.

7.6.8 Create Process - Place Order

After selecting products for an order, you click the Next button. The process defined in this section is associated with this button. The PL/SQL code specified in this process adds new customer and order information in relevant database tables using a few SQL INSERT statements. After committing the DML statement, the process truncates the ORDER collection.

1. On the Processing tab, create a new process under the Processing node.Figure 7-29

2. Enter Place Order for the name of this new process and set its Type to PL/SQL Code. Enter the following code in the PL/SQL Code box. Also, select NEXT for When Button Pressed property. The code is stored under BookCode\Chapter7\7.6.8.txt file.

   declare
   l_order_id    number;
   l_customer_id varchar2(30) := :P11_CUSTOMER_ID;
   begin
   
   -- Create New Customer
   if :P11_CUSTOMER_OPTIONS = 'NEW' then
       insert into DEMO_CUSTOMERS (
           CUST_FIRST_NAME, CUST_LAST_NAME, CUST_STREET_ADDRESS1,
           CUST_STREET_ADDRESS2, CUST_CITY, CUST_STATE, CUST_POSTAL_CODE,
           CUST_EMAIL, PHONE_NUMBER1, PHONE_NUMBER2, URL, CREDIT_LIMIT, TAGS)
       values (
           :P11_CUST_FIRST_NAME, :P11_CUST_LAST_NAME, :P11_CUST_STREET_ADDRESS1,
           :P11_CUST_STREET_ADDRESS2, :P11_CUST_CITY, :P11_CUST_STATE,
           :P11_CUST_POSTAL_CODE, :P11_CUST_EMAIL, :P11_PHONE_NUMBER1,
           :P11_PHONE_NUMBER2, :P11_URL, :P11_CREDIT_LIMIT, :P11_TAGS)
       returning customer_id into l_customer_id;
       :P11_CUSTOMER_ID := l_customer_id;
   end if;
   
   -- Insert a row into the Order Header table
   insert into demo_orders(customer_id, order_total, order_timestamp, user_name) 
   values  (l_customer_id, null, systimestamp, upper(:APP_USER)) 
   returning order_id into l_order_id;
   commit;
   
   -- Loop through the ORDER collection and insert rows into the Order Line Item table
   for x in (select c001, c003, sum(c004) c004 from apex_collections 
              where collection_name = 'ORDER' group by c001, c003) loop
      insert into demo_order_items(order_item_id, order_id, product_id, unit_price, quantity)
      values (null, l_order_id, to_number(x.c001), to_number(x.c003),to_number(x.c004));
   end loop;
   commit;
   
   -- Set the item P14_ORDER_ID to the order which was just placed
   :P14_ORDER_ID := l_order_id;
   
   -- Truncate the collection after the order has been placed
   apex_collection.truncate_collection(p_collection_name => 'ORDER');
   end;

7.6.9 Create Branches

Create the following three branches under the After Processing node on the Processing tab. The buttons referenced in these branches were created in section 7.6.6.

Property Value

1. Name Go To Page 14
2. Type (under Behavior) Page or URL (Redirect)
3. Target Type = Page in this Application Page = 14
4. When Button Pressed NEXT

Property Value

1. Name Go To Page 4
2. Type (under Behavior) Page or URL (Redirect)
3. Target Type = Page in this Application Page = 4
4. When Button Pressed CANCEL

Property Value

1. Name Go To Page 11
2. Type (under Behavior) Page or URL (Redirect)
3. Target Type = Page in this Application Page = 11
4. When Button Pressed PREVIOUS

Test Your Work

Navigate to the Orders page using the main menu route and click the Enter New Order button. Select a customer using the Existing Customer option and click Next. Click the Add button next to Air Jordan 6 shoes to add this product to the Current Order pane. Click the Add button again for this product and see increase in Quantity and Total. Add some more products and observe the change in the Current Order section. Click the cross sign to remove a product from the Current Order section. Click Cancel to return to Page 4 without saving the order.

7.7 Create Order Summary Page - Page 14

After adding products to the Order form, you click the Place Order button. The next page, Order Summary, comes up to show details of the placed order. In this section, you will create this page. It is the last step in the order creation wizard.

1. Create one more Blank Page.
2. Complete the first wizard step as show in the following figure and click Next.
3. On the Navigation Menu screen, set Navigation Preference to Identify an existing navigation menu entry for this page, and set Existing Navigation Menu Entry to Orders. Click Next.
4. Click Finish to end the wizard.
5. Click the root node (Page 14: Order Summary) and set Dialog Template to Wizard Modal Dialog.

7.7.1 Create Region – Order Progress

Right-click the Wizard Progress Bar node and select Create Region. Set following properties for the new region.

Property Value

1. Title Order Progress
2. Type List
3. List Order Wizard
4. Template Blank with Attributes
5. List Template (under Attributes node) Wizard Progress

7.7.2 Create Region – Order Header

Right-click the Wizard Body node and select Create Region. Set the following properties for this region. Just like section 7.6.3, you define the region as PL/SQL Dynamic Content, which is based on PL/SQL that enables you to render any HTML or text.

Property Value

1. Title Order Header
2. Type PL/SQL Dynamic Content

3. PL/SQL Code

   begin
   for x in (
    select c.cust_first_name, c.cust_last_name, cust_street_address1,
        cust_street_address2, cust_city, cust_state, cust_postal_code from demo_customers c, demo_orders o
    where c.customer_id = o.customer_id and o.order_id = :P14_ORDER_ID
   ) 
   loop
   htp.p('<span style="font-size:16px;font-weight:bold;">ORDER #' ||sys.htf.escape_sc(:P14_ORDER_ID) || '</span><br />');
   htp.p(sys.htf.escape_sc(x.cust_first_name) || ' ' ||sys.htf.escape_sc(x.cust_last_name) || '<br />');
   htp.p(sys.htf.escape_sc(x.cust_street_address1) || '<br />');
   
   if x.cust_street_address2 is not null then
     htp.p(sys.htf.escape_sc(x.cust_street_address2) || '<br />');
   end if;
   htp.p(sys.htf.escape_sc(x.cust_city) || ', ' || sys.htf.escape_sc(x.cust_state) || '  ' ||sys.htf.escape_sc(x.cust_postal_code) || '<br /><br />');
   end loop;
   end;

7.7.3 Create Region – Order Lines

Add another region under the Wizard Body node and set the following properties for this region. After creating this region expand its Columns node and set suitable heading for each column. This region will carry line item information.

Property Value

1. Title Order LinesType Classic Report
2. Location Local Database
3. Type SQL Query
4. SQL Query

   select p.product_name, oi.unit_price, oi.quantity, (oi.unit_price * oi.quantity) extended_price  
   from demo_order_items oi, demo_product_info p
   where oi.product_id = p.product_id and oi.order_id = :P14_ORDER_ID

7.7.4 Create Item

Right-click the Order Lines region and select Create Page Item. Set the following properties for the new item. The value for this item was set in the PL/SQL code defined in section 7.6.8 and was utilized in the codes defined in section 7.7.2 and in section 7.7.3 to fetch order information. Property Value Name P14_ORDER_ID Type Hidden 7.7.5 Create Region – Buttons Right-click the Wizard Buttons node and select Create Region. Enter Buttons for its Name and set its Template to Buttons Container. The region will hold the following button. 7.7.6 Create Button Right-click the new Buttons region node and select Create Button. Set the following properties for the new button: Property Value Button Name BACK Label Back To Orders Button Position Next Hot On Action Redirect to Page in this Application Target Type = Page in this application Page = 4 7.7.7 Create Trigger As the final step of this module, add the following trigger to your schema. The trigger will fire to write order total to the DEMO_ORDERS table when any order item is changed.1. From the main Oracle APEX menu, select SQL Workshop | SQL Commands. Figure 7-31

2. In the SQL Commands interface, enter the code for the new trigger named DEMO_ORDER_ITEMS_AIUD_TOTAL, as illustrated in the following figure, and hit the Run button. The trigger will be created and you will see a confirmation on the Results tab. The code for this trigger is available in BookCode\Chapter7\7.7.7.txt file.Figure 7-32 Complete Testing Congratulation! You have completed the most tiresome but interesting chapter of the book in which you learned numerous techniques. Now you are in a position to test the whole work you performed in this chapter.
3. Select Orders from the main navigation menu and then click the Enter New Order button.
4. Select New Customer.
5. Fill in the New Customer form using your own name, address, and so on. Click Next to proceed.
6. On the Select Items page add some products to the Current Order pane.
7. Click the Place Order button to see the Order Summary page, as illustrated in figure 7-33.Figure 7-33 Order Summary Page NOTE: You might encounter a primary key violation message (ORA-00001: unique constraint (DEMO_ORDERS_PK) violated) while creating first product record. This is because the Sequence object for this table is created with an initial value of 1. Keep clicking the Place Order button unless the record is saved.
8. Click the Back To Orders button in the Order Summary page to return to the orders main page. The newly created order will appear in the orders list.
9. Click the number of the new order to modify it in Order Details page (Page 29). Try to add or remove products on this page and save your modifications.8. Also, try the delete operation by deleting this new order.

7.8 Sending Email from Oracle APEX Application

You can use the APEX_MAIL package to send an email from an Oracle Application Express application. This package is built on top of the Oracle supplied UTL_SMTP package. Because of this dependence, the UTL_SMTP package must be installed and functioning to use APEX_MAIL. Since we are using the online APEX version, this package is already configured and we can give it a test run. In this section we are going to send an order confirmation email to customers, whose emails exists in the DEMO_CUSTOMERS table. The email will contain a link to access the placed order.

1. Open Page 12 (Order Items) and create a new process under the Place Order process. Set the attributes for this new process as shown on the next page. The PL/SQL code for this process is provided in BookCode\Chapter7\7.8.txt file. The PL/SQL code starts with the declaration of some variables. You can use VARCHAR type for Vbody and Vbody_html variables. Passing values to these variables yield a multi-part message that includes both plain text and HTML content. The settings and capabilities of the recipient's email client determine what displays. Although most modern email clients can read an HTML formatted email, remember that some users disable this functionality to address security issues. On line 7 we fetch email address of the ordering customer, and on line 8 we store customer name in a variable. The CSS code defined on line 10 formats different parts of the email. Line 11 creates the greeting line, while line 12 forms a paragraph containing a link to the customer order. The order is displayed on Page 30 of the application. Remember, you must include a carriage return or line feed (CRLF) every 1000 characters because the SMTP/MIME specification dictates that no single line shall exceed 1000 characters. We used utl_tcp.crlf for the same purpose. Finally, the APEX_MAIL.SEND procedure sends an outbound email message. The following table describes the parameters used in this SEND procedure. Parameter Descriptionp_to (required) Valid email address to which the email is sent. For multiple email addresses, use a comma-separated list. p_from (required) Email address from which the email is sent. This email address must be a valid address. Otherwise, the message is not sent. p_body (required) Body of the email in plain text. If a value is passed to p_body_html, then this is the only text the recipient sees. If a value is not passed to p_body_html, then this text only displays for email clients that do not support HTML or have HTML disabled. A carriage return or line feed (CRLF) must be included every 1000 characters. p_body_html Body of the email in HTML format. p_subj Subject of the email. Property Value Name Send Confirmation Email Type PL/SQL Code PL/SQL Code

   DECLARE
   Vbody CLOB;
   Vbody_html CLOB;
   Vcust_email varchar2(100);
   Vcust_name varchar2(100);
   BEGIN
   select cust_email into Vcust_email from DEMO_CUSTOMERS 
   where customer_id = :P11_CUSTOMER_ID;
   select CUST_FIRST_NAME into Vcust_name from DEMO_CUSTOMERS 
   where customer_id = :P11_CUSTOMER_ID;
   Vbody := 'To view the content of this message, please use an HTML enabled mail client.'||utl_tcp.crlf;
   
   Vbody_html := '<html>
       <head>
           <style type="text/css">
               body{font-family: Arial, Helvetica, sans-serif; font-size:10pt;margin:30px;
                    background-color:#ffffff;}
               span.sig{font-size: 20px; font-weight:bold; color:#811919;}
            </style>
        </head>
        <body>'||utl_tcp.crlf;
   
   Vbody_html := Vbody_html || 'Hi '|| Vcust_name ||','||utl_tcp.crlf||utl_tcp.crlf;
   Vbody_html := Vbody_html ||'<p> Your order has been confirmed which you can access by clicking <a href="'||APEX_UTIL.HOST_URL('SCRIPT')||'f?p='||:APP_ID|| ':30'||':0::::P30_ORDER_ID:'||:P14_ORDER_ID ||'"> here. </a></p>' ||utl_tcp.crlf;
   
   Vbody_html := Vbody_html ||'<p>  Regards,</p>'||utl_tcp.crlf;
   Vbody_html := Vbody_html ||'  <span class="sig">Sales Team</span><br />'||utl_tcp.crlf;
   apex_mail.send(
   p_to   => Vcust_email,   
   p_from => 'sales@abc.com', 
   p_body      => Vbody,
   p_body_html => Vbody_html,
   p_subj      => 'Order Confirmation');
   END;

   Success Message Order confirmation email sent to customer Error Message There was some problem in sending email When Button Pressed NEXT When a customer clicks the link in the email, he is routed to Page 30 (after providing his credentials on the sign in page) to see his order. This page will be created by making a copy of Page 29. There are a couple of default security setting on this page that we need to change as well to allow access to the order.

2. Open Page 29 in Page Designer. From the Create menu, select Page as Copy – see section 7.5. Enter 30 for New Page Number and Customer Order for New Page Name. On the Navigation Menu screen, select Identify an existing navigation menu entry for this page, and select Orders for Existing Navigation Menu Entry.
3. Open Page 29 (Order Details) and click the root node. Scroll down to the Security section, and set Page Access Protection attribute to Unrestricted. This value is set for a page that is requested using a URL, with or without session state arguments, and having nochecksum.
4. Delete all six buttons and their regions from Page 30.
5. Next, click the P30_ORDER_ID page item located under Wizard Body | Order #&P30_ORDER_ID. region. Set Session State Protection under Security to Unrestricted. By setting this value the item's value can be set by passing the item in a URL or in a form and no checksum is required in the URL. All is set. Modify a customer record by entering your email account. Create a new order for this customer. After clicking the Place Order button on the second wizard screen, you will see the message “ Order confirmation email sent to customer. ” Log out from the application. After a while, you will receive an order confirmation email in your email account. Click the “ here ” link in the email that will take you to the application login page. Immediately after providing your credentials, the copied order details page (Page 30) will appear on your screen displaying the order you just entered. NOTE: If you get “Your session has expired. Please close this dialog and press your browser's reload button to obtain a new session.” message, then open Page 30, click its root node, and set Page Mode to Normal.

7.9 A More Simple Approach

I know as a beginner you might be confused with the stuff described in section 7.5 onward. I added this stuff purposely to present something that would be helpful to you in your future endeavors. However, in this section I’ll demonstrate a simpler approach to add, modify, and delete orders using just one interface.

1. Execute all the steps mentioned in section 7.2 to create the two master and details pages. In step 4, set number of the Master Page to 404, and number of the Details Page to 429. NOTE: Make the Interactive Grid visible on the Order Details page (Page 429), as instructed at the end of section 7.2.
2. Open Page 404 and execute the instructions provided in section 7.3.1. In step 5 of section 7.3.1, set Page and Clear Cacheproperties to 429 to point to the correct page number and set the Name property to P429_ORDER_ID. Skip the optional report sections (spanning from 7.3.2 to 7.3.4) at this stage to preserve some time.
3. Set the following attributes for the CREATE button. Note that previously this buttons was used to initiate the order wizard by calling Page 11. Here, we are calling Page 429 to directly enter a new order. Property Value Button Name CREATE Label Enter New Order Button Template Text with Icon Hot On Icon fa-chevron-right Action Redirect to Page in this Application Target Type = Page in this Application Page = 429 Clear Cache = 429
4. Save Page 404.
5. In the Page Finder box, enter 429 and press the Enter key to call Page 429 in the Page Designer.
6. Click the root node (Page 429: Order Details) and set the Page Mode property to Modal Dialog. Set Width, Height, and Maximum Width properties to 900, 800, and 1200, respectively. Also, set Dialog Template (in the Appearance section) to Wizard Modal Dialog.
7. Edit the following items individually and set the corresponding properties shown under each item. The customer ID item, which was displayed as Display Only item in the previous method, will now be rendered as a Select List carrying the names of all customers. The SQL query defined for the Select List automatically shows the correct customer name when you navigate from oneorder to another. P429_CUSTOMER_ID Property Value Type Select List Label Customer Type (List of Values) SQL Query SQL Query select cust_first_name ||' '|| cust_last_name d, customer_id r from demo_customers P429_USER_NAME Property Value Type Select List Label Sales Rep Type (List of Values) SQL Query SQL Query select distinct user_name d, user_name r from demo_orders union select upper(:APP_USER) d, upper(:APP_USER) r from dual order by 1 Display Extra Values Off Display Null Value Off Help Text Use this list to change the Sales Rep associated with the order.
8. In the Region Buttons node, set Button Position property to Edit for GET_PREVIOUS_ORDER_ID and GET_NEXT_ORDER_ID buttons to place them on top of the region.
9. Click the Order Details interactive grid region. Set its Source Type to SQL Query, and replace the default query with the one that follows: select oi.order_item_id, oi.order_id, oi.product_id, pi.product_name, oi.unit_price, oi.quantity, (oi.unit_price * oi.quantity) extended_price from DEMO_ORDER_ITEMS oi, DEMO_PRODUCT_INFO piwhere oi.ORDER_ID = :P429_ORDER_ID and oi.product_id = pi.product_id (+)
10. Under the Columns node, edit the following columns using the specified properties and values. Column Property Value PRODUCT_ID Type Heading Alignment Type (LOV) List of Values Display Null Value Select List Product left Shared Components Products With Price Off PRODUCT_NAME Type Hidden QUANTITY Width (Appearance) Type (Default) PL/SQL Expression 5 PL/SQL Expression 1 (sets 1 as the default q EXTENDED_PRICE Type Heading Alignment Column Alignment Format Mask Query Only ( Source ) Display Only Price right right $5,234.10 On
11. Right-click the Wizard Buttons node and select Create Region. Set Title of the new region to Buttons and Template to Buttons Container. In Regions Buttons node, click the Cancel button and set its Region property (under Layout) to Buttons. Set this region for Delete, Save, and Create buttons, too. This action will place all the four buttons under the Buttons region.
12. Open Page 429 in the Page Designer. On the Processing tab make sure that the Process form Form on DEMO_ORDERS sits before the Order Details - Save Interactive Grid Data process.
13. Click the Save Interactive Grid Data process and switch its Type from Interactive Grid - Automatic Row Processing (DML) to PL/SQL Code. Enter the following code in the PL/SQL Code box. In this code, you specify SQL Insert, Update, and Delete statements to manually handle the three operations for the Interactive Griddata. The :APEX$ROW_STATUS is a built-in substitution string, which is used to refer to the row status in an Interactive Grid. This placeholder returns the status of C if created, U if updated, or D if deleted for the currently processed interactive grid row. Enter "The DML operation performed successfully" in the Success Message box. Similarly, enter "Could not perform the DML operation" in the Error Message box, and save your work. begin case :APEX$ROW_STATUS when 'C' then
    insert into DEMO_ORDER_ITEMS (order_item_id, order_id, product_id, unit_price, quantity) values (null, :P429_ORDER_ID, :PRODUCT_ID, :UNIT_PRICE, :QUANTITY); when 'U' then update DEMO_ORDER_ITEMS set product_id = :PRODUCT_ID, unit_price = :UNIT_PRICE, quantity = :QUANTITY where order_item_id = :ORDER_ITEM_ID and order_id = :ORDER_ID; when 'D' then delete DEMO_ORDER_ITEMS where order_item_id = :ORDER_ITEM_ID and order_id = :P429_ORDER_ID; end case; end; NOTE: All four input items in the Order Master section on Page 429 are rendered as floating elements (see Template property under Appearance section) in which the label is displayed inside of the input item, and it automatically shrinks once the input field has a value. Test Your Work Click the Enter New Order button (A) on Page 404. Select a customer (B) and pick an order date (C). Click the Edit button (D) in the Order Details region. With a product appearing in the first row (E) along with its default quantity (G), enter some value in the Unit Price column (F), and click the Create button (H). The order will be saved and you will see the success message. On the Order Master page, click the order number you just saved, and then click Add Row (I) to add some more products. Just select aproduct, enter some value in the Quantity column, and click Save. The modified order will be saved as well. Try to remove a product from this order using the Delete Rows option in the Row Actions menu. Finally, click the Delete button on the Order Details page to test order deletion. You’re done! Figure 7-34 Order Master and Detail Pages

7.10 Looping Through Interactive Grid

If you are an absolute beginner, I would recommend you to skip this section for the time being. Once you get a firm grip on APEX, revert to this section to learn some beyond stuff. In this section, you learn how to loop through each record in an interactive grid to perform some kind of validation. For example, here you will prevent addition of duplicate products in a single order. Of course, you can add a composite unique key constraint on the corresponding table to prevent duplication. But, there are some scenarios where this solution doesn’t fit. For example, if you provide some free samples of a product in an invoice, you need to create two line item entries in your order screen for the same product – one with a price tag and another free. Execute the following steps to prevent product duplication in an order.1. Open Page 429 in page designer. Click the Form on DEMO_ORDERS static content region (under Wizard Body) and set it Title to Order Master.

2. Click the Order Details interactive grid region and enter ORDER for its Static ID attribute (under Advanced). The ORDER static id will be used as the ID for the interactive grid region, which is useful in developing custom JavaScript behavior for the region, as you will see later in this exercise.
3. Right-click the Items node under the Order Master static content region (under Wizard Body) and select Create Page Item. Set the following attributes for this new item. It is a hidden item that will store 0 (as default) or 1 behind the scene. The value 1 in this item means that there are some duplicate products in the order. This evaluation will be done by a validation – Check Duplicate Product. Property Value Name P429_PRODDUP Type Hidden Value Protected Off Type (under Source) Null Type (under Default) Static Static Value 0
4. Expand the Columns node (under the Order Details region), and set the following attributes for PRODUCT_NAME column: Property Value Type Text Field Heading Product Name
5. Switch to the Dynamic Actions tab. Right-click the main Events node and select Create Dynamic Action. Set the following attributes for this dynamic action. The dynamic action will execute a JavaScript code that will be fired before submitting the page. The JavaScript code is defined as a custom function – chkDUP() in step7. Property Value Name Check Duplicate Product Event Before Page Submit Click the Show node (under True) to set the following attributes: Action Execute JavaScript Code Code chkDUP()
6. Create another dynamic action. This time right-click the Change node and select Create Dynamic Action from the context menu. Set the following attributes for this dynamic action, which is being created to fetch product name when a user selects a different product in the Order Details interactive grid. Property Value Name Fetch Product Name Event Change Selection Type Column(s) Region Order Details Column PRODUCT_ID Click the Show node (under True) to set the following attributes: Action Execute PL/SQL Code PL/SQL Code select product_name into :PRODUCT_NA from DEMO_PRODUCT_INFO where product_id = :PRODUCT_ID; Items to submit PRODUCT_ID Items to Return PRODUCT_NAME
7. On the Rendering tab, click the root node - Page 429: Order Details. Scroll down to the Function and Global Variable Declaration section and append the following JavaScript function after the existing code: function chkDUP() { var record; var prodDUP=0;//Identify the particular interactive grid var ig$ = apex.region("ORDER").widget(); var grid = ig$.interactiveGrid("getViews","grid"); //Fetch the model for the interactive grid var model = grid.model; //Select all rows ig$.interactiveGrid("getViews").grid.view$.grid("selectAll"); //Fetch selected records var selectedRecords = grid.view$.grid("getSelectedRecords"); for (idx1=0; idx1 < selectedRecords.length; idx1++) { record = model.getRecord(selectedRecords[idx1][0]); prodcode1 = model.getValue(record,"PRODUCT_NAME"); for (idx2=0; idx2 < selectedRecords.length; idx2++) { record = model.getRecord(selectedRecords[idx2][0]); prodcode2 = model.getValue(record,"PRODUCT_NAME"); if (prodcode1 == prodcode2 && idx1 != idx2) { prodDUP=1; break; } } if (prodDUP == 1) { break; }
   } $s("P429_PRODDUP",prodDUP);
   if (prodDUP == 1) { alert("Duplication of product occurred - "+prodcode2);
   }
   } The function is called from the Check Duplicate Product dynamic action before the page is submitted. Initially the function identifies the Order Details interactive grid through its static ID. Then, after fetching the interactive grid's model, all rows in the interactive grid are selected. The function then initiates a FOR loop, which loops through every record in the interactive grid. In every loop, value from the Product Name column is stored (in prodcode1 variable) and then compared with another variable in an inner FOR loop. If a duplicate is found, the duplicate switch is turned on – prodDUP=1. If the switch is turned on, you see the client-side message specified in the alert function.
8. The JavaScript function in the previous step alerts you of duplicateproducts. After the alert, the page is submitted and the order is saved with duplication. A server-side validation must also be created to prevent this situation. On the Processing tab, right-click the Validations node and select Create Validation. Set the following attributes for the new validation, which evaluates the value of P429_PRODDUP hidden page item when either Save or Create buttons are clicked. If the value of this item is zero, the order is processed. If it is set as 1 by the chkDUP function, an error message is fired. Note that if a validation passes the equality test, or evaluates to TRUE, then the validation error message does not display. Validation error messages display when the validation fails the equality test, or evaluates to FALSE, or a non-empty text string is returned. Subsequent processes and branches are not executed if one or more validations fail evaluation. Property Value Name Check Duplicate Products Type (under Validation) Item = Value Item P429_PRODDUP Value 0 Error Message Duplicated product found – cannot proceed further Display Location Inline in Notification Server-side Condition section Type Request is contained in Value Value SAVE,CREATE Save and run the module. Create a new order. Initially the Product column in the interactive grid defaults to Air Jordan 6. Select a different product to fire the dynamic action and fetch the product name in the Product Name column. Add another row and select the same product on the new row. Input unit price in both rows and click Create. First, you will get the client-side product duplication message from the JavaScript function followed by the error message defined in the validation.

7.11 Interactive Grid Native PDF Printing

Interactive Grid Downloads includes native PDF Printing which allows youto print PDF files directly from Interactive Grids. This feature produces a PDF file which retains Grid formatting such as highlighting, column grouping, and column breaks. Let’s go through a simple demonstration to explore this feature.

1. Open the Orders Interactive Report page (Page 4) in Page Designer.
2. Right-click the Orders interactive report region, and select Duplicate from the context menu. A copy of this region will be created. Figure 7-35
3. Click the new Orders region, and change its Type property in the Identification section from Interactive Report to Interactive Grid.
4. Click the Attributes node under the new Orders region. In the properties pane, scroll down to Download section and ensure that PDF option (under Download | Formats) is checked. The checkeddownload formats can be utilized by users to download the currently displayed columns in the interactive grid.
5. Save and run the page. The page should now have two regions. Scroll down a bit to see the interactive grid region. From the interactive grid’s Actions menu, select Format | Control Break.
6. On the Control Break dialog, select Order Month from the Column list and click Save. Figure 7-36
7. Next, select Actions | Format | Highlight. Set the following parameters in the Highlight dialog. Once you hit Save in the Highlight dialog, rows with 1000 or greater amount in the Order Total column will be highlighted.Figure 7-37
8. Click the Actions menu again and select Download. In the Download dialog, select PDF and other options as illustrated in the following figure and click the Download button. The output of the interactive grid will be downloaded as a PDF to your device.Figure 7-38 The following figure illustrates the downloaded PDF. As you can see both highlight and control break formattings are preserved in the PDF.Figure 7-39 Summary Here are the highlights of this chapter: Master Detail – You learned how to implement Master Detail page feature to handle data in two relational tables and went through theauto-generated page components added by the wizard to transparently manage the order processing module. Interactive Report – Created an interactive report and learned how to alter the report layout by applying highlighting, sorting, and using aggregate functions. You also applied Control Breaks to group related data and added Chart and Group By Views. Primary, Public, and Alternative Interactive Report – You created three variants of the interactive report and went through the concepts behind these variants. Wizard Steps – Learned how to create wizard-like interfaces to perform related tasks in a sequence. Copy Page Utility – The chapter provided a shortcut to utilize an existing page with all functionalities using a different number and for a different scenario. Oracle APEX Collection – You learned how to use collections to store and retrieve rows and columns information in session state. Custom Processes and Dynamic Actions – In addition to the auto- generated components and processes, you learned how to manually add your own processes and other components. Using HTML in PL/SQL Code – You used PL/SQL to have more control over dynamically generated HTML within a region. Using CSS in Oracle APEX Pages – You applied styling rules to give the page a more professional look. Simple Approach – Besides the advance techniques, you also learned how to create this module using a simple approach. Looping through Interactive Grid – In the final section of this chapter you learned how to loop through interactive grid records. You usually execute this procedure when you need to perform some sort of validation on the data in an interactive grid prior to storing it in your database.

08. Graphical Reports & Mobile Integration

page 304/419

Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy

09. Produce Advance Reports

Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy

10. Authorization - Managing Users & App Access

Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy

11.1 Faceted Search

Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz....Search.....Style Theme r......Style buttons.....Calendar..... Deploy

11.2 Theme Roller – Style Your Application

Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search, style, calendar.....Style Theme r......Style buttons.....Calendar..... Deploy

11.3 Styling Buttons

Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz....Search, style, calendar.....Style Theme r......Style buttons.....Calendar..... Deploy

11.4 Manage Events via Calendar component

Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz....Search, style, calendar.....Style Theme r......Style buttons.....Calendar..... Deploy

12. Deploy

Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal......Deploy

12.1 About App Deployment dev to production environment

Consists of two steps : Export desired components to a script file and import script file into your production environment. Decide where and how app will run.

1. No Deployment: dev environment becomes prod environment and nothing is moved to another computer. In this option users are provided with URL to access app.
2. App: if target computer is already running production Ora DB with all objects. You export app and import it into target DB.
3. App and Table Structures: you create two scripts, one for your app and another for DB tbl structures using Generate DDL utility in SQL Workshop.
4. App and DB obj. with Data: you deploy your app along with all DB objects using data pump utility
5. Individual Components: With dev phase going on, you can supplement your deployment plan by exporting only selected components.

12.2 Export App

https://docs.oracle.com/en/database/oracle/application-express/20.1//htmdb/managing-application-backups.html

For simplicity, we will deploy app in the same workspace. Same technique is applicable to new workspace or prod env.

1. Sign in to APEX and edit (click) Sales Web Application.
2. Click Export/Import icon. On the ensuing page, click Export icon.
3. In "Choose App" section, set App to Sales Web App, and click Export button.
4. A file something like f145615.sql will be saved in Download folder under My Doc or in another folder specified in your browser.

12.3 Data pump utils DB 20.1c expdp/impdp (since 10g)

Up to 40x faster than Exp/Imp Data.

Very high-speed movement of data and metadata from one DB to another for a complete DB or subsets of a DB. In addition to basic import and export functionality data pump provides a PL/SQL API and support for external tables.

See oracle_DB18c_devsuite10g_F6i_to_apex.txt.

12.4 Import App

Import exported app f145615.sql into existing workspace you are connected to with a different WS ID.

1. Go to App Builder interface and click Import icon.
2. On the Import screen, click the Choose File button and select exported file (f145615.sql). For File Type, select DB App, Page or Component Export and click Next.
3. After a while a message The export file has been imported successfully will appear. The status bar at the bottom of your screen will show progress during the upload process. Click Next to move on.
4. On the Install screen, select default value for Parsing Schema. Set Build Status to Run and Build App, Install As Application to Auto Assign New Application ID, and click the Install Application button. After a short while, the application will be installed with a new ID for you to give it a test-run.
5. On the Install page, click the Run Application button. You will encounter an error saying “ You are not authorized to view this application, either because you have not been granted access, or your account has been locked. Please contact the application administrator. ” Application users are not exported as part of your application. When you deploy your application you will need to manually manage your user to role assignments. Roles are exported as part of an application export and imported with application imports. Execute the following step to cope with this error.
6. On the Install page, click the Edit Application button. Go to Shared Components, and click Application Access Control in the Security section. Using the Add User Role Assignment button (A) add the three users as shown in the following figure. In Shared Components, click Security Attributes. Click the Authorization tab, and set Authorization Scheme to No application authorization required (A). Apply the change. Now you will be able to access the application.

12.5 Prevent App Modification and Remove Developers Toolbar

The Developers Toolbar is used to access the application source. In this exercise, we are going to prevent users from modifying the application by suppressing the toolbar.

1. Open the new application you just imported in the designer interface.
2. Click Shared Components.
3. Click the Globalization Attributes link (under Globalization).
4. Click the Definition tab.
5. Click the Availability tab, set Build Status to Run Application Only, and click Apply Changes.
6. Go to the App Builder interface and see that the new application doesnt have the Edit link. Click the Run button and provide yoursign in credentials. Note that the Developer Toolbar has disappeared as well.
7. To make the application editable again, select App Builder | Workspace Utilities | All Workspace Utilities (A), as illustrated in the following figure. On the Workspace Utilities page, select Build and App Status (B) from the right sidebar. On Build Status and Application Status page, click the application ID in the first report column, change the Build Status to Run and Build Application, and apply the change.

Thats it. You have successfully deployed your application in the same workspace. You can apply the same procedure to deploy the application to another environment.

Conclusion

Oracle APEX has come a long way from its simple beginning. Sky is the limit, you are limited by your imagination.

Top.....Back

2.2 Applications, App. builder

Top.....Apexws_cloud.....App. builder.....Page.....Environm.....URL .....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy

1. https://apex.oracle.com/pls/apex/

   (+ f?p=4550:1:117485610537637:::::)

2. button "Sign in" opens same URL, page "App Builder" :

Page "App Builder" : Create and manage my apps and their pages :

1. "Create" icon functionality :
   1. New App based on tables you select or by providing a valid SQL
   2. New App from File :
      Load Data Wizard appears to load definitions & data in : CSV, XLSX, XML, TXT or JSON
      or Copy and Paste column delimited data.
      After loading data into DB tbl, wizard creates some app. pages based on new tbl
   3. "App Gallery" -> "Productivity Apps" eg proj. mngmnt, surveys, shared calendars, and tracking apps, can be installed, run, and removed.
2. "Import" icon
3. "Dashboard" icon
4. "Workspace" Utilities icon

2.3 Page eg App. builder -> click my "Sales Web app" icon

Top.....Apexws_cloud.....App. builder.....Page.....Environm.....URL .....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy

Page creation wizards - option to create a blank page and add components manually, Page Designer interface to add more controls after page creation.

When you run app requested with a URL, APEX engine relies on two processes:

1. Show Page is page rendering process, runs when you request page using a URL. It assembles all the page attributes including regions (stacked canvases), items, and buttons into a viewable HTML page. When you request a page using a URL, the engine is running Show Page.
2. Accept Page performs page processing when you submit page. It performs computations, validations, processes, and branching.
   1. APEX engine is running Accept Page then performing page processing during which it
   2. saves submitted values in session cache
   3. and then performs any computations, validations, or processes.

You can create following types of pages for your app :

1. Blank Page Creates a page without any built-in functionality
2. Report Used to present a SQL query in a formatted style
   1. Interactive Report
   2. Interactive Grid
   3. Classic Report
3. Form
   1. Editable Interactive Grid
   2. Report with Form
4. Master Detail
   1. Stacked
   2. Side by Side
   3. Drill Down
   4. Plug-ins
   5. Chart
   6. Tree
   7. Calendar
   8. Data Loading

2.4 Region, Items, Buttons

1. Region (stacked canvas) serves as container for content

Region template controls region look, size, determines whether there is border or background color, and what type of fonts to display. Also determines standard placement for any buttons placed in region positions.

You can use regions to group page elements (such as items or buttons).

APEX supports many different region types eg Static Content, Classic Report, Interactive Report, Interactive Grid, Chart, and more.

2. Items

Text Field, Textarea, Password, Select List, Checkbox... Item properties : where a label displays, how large an item is, and if item displays next to or below previous item. Page item name is eg P7_CUSTOMER_ID = custID item on page 7 (preceded P followed by number pageID).

3. Buttons to submit page or to take users to another page

Redirect :

1. within the same site where a user submits a page, the Oracle APEX engine executes some processes associated with a particular button and uploads the page's item values to the server - see Figure 1-3 in chapter 1
2. or to a different site

In case of a redirect : nothing is uploaded to the server. If you change some items values on a page and press a button created with a redirect action, those changes will be lost.

Button options are: Icon, Text, and Text with Icon. You can place buttons either in predefined region positions or with other items in a form - see Figures 1-1, 1-3, and 1-7 in chapter 1.

Buttons control flow of DB apps, are created by right-clicking region -> select "Create Button" from context menu. By placing buttons (such as Create, Delete, Cancel, Next, Previous , and more) on your web page, you can post or process by customer provided information or you can direct user to another app pg or to another URL.

Buttons are used to:

1. Submit a page eg to save user input in a DB tbl. When a button on a page is clicked, pg is submitted with REQUEST value that carries btnname. You can reference value of REQUEST from within PL/SQL using bind variable " :REQUEST.". By using this bind variable, you can conditionally process, validate, or branch based on which button the user clicks. You can create processes that execute when the clicks a button. And you can use a more complex condition as demonstrated in the following examples:

   If :REQUEST in ('EDIT','DELETE') then ...
   If :REQUEST != 'DELETE' then ...

   These examples assume existence of buttons named EDIT and DELETE. You can also use this syntax in PL/SQL Expression conditions. If you name a button LOGIN, then request looking for Login fails - btnname is case sensitive.

   2. Take user to another page within the same app with optional additional properties for resetting pagination, setting request value, clearing cache, and setting item values on the target page.

2. Redirect to another URL.

3. Do nothing eg if buttons behavior is defined in a Dynamic Action.

4. Download Printable Report Query. This creates a Submit Page button and also a corresponding branch. When the button is clicked, the output is downloaded from Report Query.

2.5 APEX Web-based app Development Environment to build web apps

Top.....Apexws_cloud.....App. builder.....Page.....Environment.....URL Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy

You are not required to install any client software to develop, deploy, or run Oracle APEX applications.

Primary APEX tools :

1. App Builder - to create dynamic database driven web appls. Here you create and modify your applications and pages. It comprises following :

   1. Create: new apps. See section 2.3.2
   2. Import: entire Oracle APEX apps developed somewhere else, along with related files.
   3. Dashboard: metrics about apps in your workspace eg: Developer Activity, Page Events, Page Count by App, Most Active Pages...
   4. Workspace Utilities: Most significant is Export app and component metadata to SQL script file format that you can import on the same or another compatible instance of APEX.

2. SQL Workshop - to browse your DB objects, to run ad-hoc SQL queries, allow App Developers to maintain DB objects eg tables, packages, functions, views... It is beneficial in hosted environments like apex.oracle.com where direct access to underlying schemas is not provided. It has five basic components:

   1. Object Browser: to review and maintain database objects (tables, views, functions, triggers...).
   2. SQL Commands: to run SQL queries.
   3. SQL Scripts: to upload and execute script files.
   4. Utilities: eg Query Builder, Data Workshop, Generate DDL, Schema Comparison...
   5. RESTful Services: to define Web Services using SQL and PL/SQL against DB

3. Team Development - allows development teams to better manage their Oracle APEX projects by defining milestones, features, to-dos, and bugs. Features, to-dos, and bugs can be associated with specific apps and pages as necessary. Developers can readily configure feedback to allow their end-users to provide comments on app. The feedback also captures relevant session state details and can be readily converted to a feature, to-do or bug.

4. App Gallery - Productivity apps are a suite of business productivity applications, easily installed with only a few clicks. These solutions can be readily used as production applications to improve business processes and are fully supported by Oracle.

Oracle APEX environment has two broad categories:

1. Development Environment: Here you have complete control to build and test your applications.
2. Runtime Environment: After completing development and testing phase, you implement your apps in a production environment where users can only run these applications and do not have the right to modify them.

2.6 Page Designer : App Builder -> Sales app -> some page

1. page top, right

   2.6.1 Page Designer toolbar (A)

   It comprises various tools to find a page, lock/unlock a page, undo/redo actions, Create menu, Utilities menu, shared components, save and run page (see tooltips).

   Utilities menu has an option that lets you delete the page being displayed in your browser.

   Lock icon (padlock - lokot) indicates whether a page is currently locked. Is also displayed in Page Designer as well as on App home page - Figure 2-5. This feature enables you to prevent conflicts during app development. By locking a page you prevent other developers from editing it.

2. page top, left

   2.6.2 Tree Pane contains four icons (were tabs) and tree nodes

   Tree nodes : regions, items, buttons, app logic (eg computations, processes, validations), dynamic actions, branches, shared components.

   4 icons :

   1. Rendering icon (B) - contains as nodes in a tree : regions, page items, page buttons, and app logic. Components defined in this section appear when a page is rendered. These components can be viewed as a tree, organized either by processing order (the default) or by component type. The first two buttons to the right of the Rendering label can be used to toggle between rendering trees. Rendering is divided in three stages :

      1. pre-Rendering stage preliminary computations are performed
      2. main rendering stage comprises regions and its components
      3. Post-Rendering stage also carries computations that occur after rendering a page.

   2. Dynamic Actions icon (C) - Displays dynamic actions defined on this page. By creating a dynamic action, you can define complex client-side behavior declaratively without the need for JavaScript. Refer to Dynamic Actions entry in the book’s index to see its utilization in the project application.

   3. Processing icon (D) - Use this icon to specify app logic eg :

      1. Computations are APEX's declarative way of setting an item's values on page. These are units of logic used to assign session state to items and are executed at the time page is processed.
      2. Validation is server-side mechanism designed to check and validate quality, accuracy, and consistency of page submitted data, prior to saving it into DB. If a validation fails, further processing is aborted by the server and existing page is redisplayed with all inline validation errors.
      3. Processing are logic controls used to execute DML (Data Manipulation Language) or PL/SQL. Processes are executed after page is submitted - typically when user clicks button.
      4. Branches enable you to create logic controls that determine how user navigates through app.

   The left iconic button to the right of the Processing label displays the components under this tab according to the servers processing order. The middle button organizes these components according to their type, while the third one provides a menu to create a new component in the selected folder.

   4. Page Shared Components icon (E) - Displays shared components associated with this page. The list on this tab gets populated automatically when you use shared components on a page.

3. 2.6.3 Central Pane has two sections

   Upper section contains three tabs: Layout, Page Search, Help. Lower pane is called Gallery and it is associated with Layout tab.

   1. Layout (F) is a visual representation of the regions, items, and buttons that define a page. You can add new regions, items and buttons to a page by selecting them from Gallery at the bottom of the page, drag, drop.
   2. Page Search (G) - to search all page metadata including regions, items, buttons, dynamic actions, columns, and so on.
   3. Help (H) displays help text for properties (purpose) in Property Editor (right pane). Click a property in the Property Editor and then click Help tab (in the Central pane). As you move from one property to next in property editor, Help tab displays it's help text.

4. right pane

   2.6.4 Property Editor (prop, value) for current component in either Tree View or Layout tab

   As you select component Property Editor updates to reflect current selection. Properties are organized into functional groups (Identification, Source, Layout, Appearance, and more) that describe their purpose. When you modify or add a value to a property, a colored vertical bar appears as a visual modification indicator before the property name.

2.7 APEX URL Syntax

Top.....Apexws_cloud.....App. builder.....Page Environm.....URL .....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy

f?p URL Syntax

is a legacy syntax that creates a unique URL structure that identifies address of APEX, app ID, page number, and session ID.

https://apex.oracle.com/pls/apex/f?p=4500:1000:706440515653484

In code: https://apex.oracle.com/pls/apex/f?p=&APP_ID.:1000:&APP_SESSION.:::::

https:// URL of the server / pls is indicator to use the mod_plsql cartridge

/ apex is (default) DAD (database access descriptor) name. DAD describes how HTTP server connects to DB server to fulfill HTTP request.

/ f?p= is a prefix used by Oracle APEX to route the request to the correct engine process

4500 is appID being called : 1000 is page within app to be displayed

: 440323506685863558 is session number to keep track of user’s session state

Developer Toolbar among other tools for developers contains a Session option, which shows you the current session state. Clicking it opens a window (called Session Page, shown in Figure 2-6) carrying all items and their current session values. It is useful for developers to debug pages. When you change some item value on a page and submit it, the value in the session window for that item changes to reflect the current state. Use Page, Find, and View parameters to view session state for the page. The drop-down View menu comprises Page Items, Application Items, Session State, Collections, and All options. Select an option from this list and click the Set button to refresh the Session State report.

Friendly URL Syntax (APEX release >= 20)

creates a URL structure that identifies the address of APEX, app, page, and uses a standard URL hierarchy and passes parameters in a similar fashion. You can change existing apps to use Friendly URLs by editing Friendly URLs attribute in app definition :

Shared Components | Application Definition Attributes | Properties.

https://apex.oracle.com/pls/apex/POSSYS/r/4500/home?session=702685162362252

Friendly URL Syntax creates a URL with the following directory hierarchy and syntax:

https:// apex.oracle.com / pls /apex / myws/ r / 4500 / home?session=16167973992554

Where:

1. myws is the path_prefix which is URI path prefix used to access RESTful Services. When you create a workspace, this value defaults to workspace name. You can customize the URI path prefix by editing the Path Prefix attribute in :
   Administration | Manage Service | Set Workspace Preferences | SQL Workshop

2. r is the router shortcut. This value is a constant and should never be changed

3. 4500 is the application id

4. home is the alias of the page being displayed. If no alias is defined, the page number is displayed instead.

5. ?session=16167973992554 identifies the session ID.

2.8 Substitution Strings (&varname.) and Bind Variables (:item_name)

&varname. means prefix ampersand to item name and append period at its end to reference item.

:item_name means precede item name with colon.

To make your app more portable, APEX provides many features. On top of the list are the Substitution Strings that help you avoid hard-coded references in your app. Every app has its own unique ID used to identify app and it's metadata within APEX repository. When you move app from your dev. envir. to prod. envir, and if you've hard-coded app references, eg you hard-coded the application ID (101) like this: f?p=101:1:&APP_SESSION and prod. environ. already has an app ID=101, you will be forced to use a different ID, which will point all your links within app to the wrong ID.

To avoid such situations, you should always use substitution strings. You can avoid hard-coding appID by using APP_ID substitution string, which identifies ID of the currently executing app. With the substitution string, the URL looks like: f?p=&APP_ID.:1:&APP_SESSION.. Supported syntax for referencing APP_ID :

There are two ways to get a page to access value of a session state variable:

1. Use bind variable to reference the variable from within SQL or PL/SQL code,

2. Use substitution string from within an HTML expression so :

   Using Substitution Strings

   1. Include a substitution string within a template to reference component values
      Special substitution strings available within a template are denoted by the number symbol (#). For example: #PRODUCT_ID# - see Chapter 4 Section 4.3.2, 4.3.5, and 4.3.6.

   2. Reference page or app items using &ITEM. syntax - all capital letters !
      For example, you would refer to a page item named P7_CUSTOMER_ID in a region, a region title, an item label, or in any of numerous other contexts in which static text is used, like this: "&CUSTOMER_ID." Notice required trailing period. When the page is rendered, APEX engine replaces the value of the substitution string &CUSTOMER_ID. with the value of item P7_CUSTOMER_ID

   3. Use one of many APEX built-in substitution strings to achieve specific types of functionality. Eg APP_ID, APP_IMAGES, APP_PAGE_ID, APP_SESSION, APP_USER, LOGIN_URL, LOGOUT_URL.

Top.....Back

3.1 Shared (common, appglobal) Components across all app pages eg menu options

"Page Shared Components" tab top left icon (pyramid of 3) in Page Designer (App Builder -> App) displays a list of APPLEVEL common elements applied to that particular page. Shared components are only displayed in this section after you add them to a page.

Shared comp. list 1. APP LOGIC SECTION :

3.1.1 App Definition Attributes

Link to Edit App Definition page where you can modify your app attributes, including its name, version, and availability options. You can turn on new Friendly URLs option.

3.1.2 Application Processes

Run PL/SQL logic at a specific point from multiple pages (reports) of an app. You can apply conditions to control when process executes. Currently there are 8 different types of process that you can include in your app.

One significant is On Demand App Process. It is a special type of app process which executes when called from :

1. page-level On Demand process

2. or from an Ajax call from the browser.

   On Demand processes are useful eg assessing customer's outstanding balance and using that figure (value) on reports eg : customer invoice, age analysis report, customer balances report and so on.

Shared comp. list 2. SECURITY SECTION

3.1.3 Authentication Schemes

(access=prijava, functionalities=prava)

These schemes enable us to declaratively define security for our apps quickly and easily.

Authentication is the process of establishing identity of every your app user . Most common auth. process : usrname and psw. These credentials are then evaluated either through

1. built-in APEX Auth. scheme
2. or using a custom scheme with more control.

Authentication could involve use of digital certificates or a secure key, too. If credentials pass, user is allowed to access app. Once a user has been identified, APEX engine keeps track of each user by setting the value of built-in substitution string APP_USER.

As you create your app, you must determine whether to include auth. You can:

1. Choose to not require authentication. Oracle APEX does not check any user credentials. All pages of your app are accessible to all users eg public informational app website.

2. Select a built-in authentication scheme. Create an auth. method based on available preconfigured authentication schemes. Each scheme follows a standard behavior for authentication and session management.

3. APEX Accounts. These are user accounts created within and managed in Oracle APEX user repository. When you use this method, your app is authenticated against these accounts.

4. DB Account Credentials authentication scheme

   It utilizes DB schema accounts. This authscheme requires that DB user (schema) exist in local DB. When using this method, username and password of the DB account is used to authenticate user. Choose DB Account Credentials if having one DB account for each named user of your app is feasible (viable, practicable, executable, workable) and account maintenance using DB tools meets your needs.

   5. HTTP Header Variable. It supports the use of header variables to identify a user and to create an Application Express user session. Use this authentication scheme if your company employs a centralized web authentication solution like Oracle Access Manager, which provides single sign-on across apps and technologies.

   6. LDAP Directory Verification. You can configure any authentication scheme that uses a login page to use Lightweight Directory Access Protocol (LDAP) to verify the username and password submitted on the login page. App Builder includes wizards and pages that explain how to configure this option. These wizards assume that an LDAP directory accessible to your application for this purpose already exists and that it can respond to a SIMPLE_BIND_S call for credentials verification.

   7. Application Server Single Sign-On Server. This one delegates authentication to the Oracle AS Single Sign-On (SSO) Server. To use this authentication scheme, your site must have been registered as a partner application with the SSO server.

   8. Create custom authentication scheme.

      Using this method you can have complete control over the authentication interface. To implement this approach you must provide a PL/SQL function the Application Express engine executes before processing each page request. The Boolean return value of this function determines whether the Application Express engine processes the page normally or displays a failure page. This is the best approach for apps when any of the following is true:

      1. Database authentication or other methods are not adequate
      2. You want to develop your own login form and associated methods
      3. You want to control security aspects of session management
      4. You want to record or audit activity at the user or session level
      5. You want to enforce session activity or expiry limits
      6. Your app consists of multiple apps that operate seamlessly (eg, more than one app ID)

3.1.4 Authorization Schemes

To control users access to specific components of your app. An auth. scheme can be specified for an entire app, page, or specific page components such as a region, button, or item. For instance, you can apply an auth. scheme to determine which menu options a user can see, or whether he is allowed to create a new order (using Create button).

Shared comp. list 3. OTHER COMPONENTS

3.1.5 LOVs (Lists of Values) are defined by running LOV wizard - static and dynamic

Once created, LOVs are stored in LOVs repository and are utilized by page items Px_...

1. static LOV displays and returns predefined values such as Yes and No
2. dynamic list is populated using SQL query

After creating LOV you associate it to page items such as select list, radio group, checkbox.... LOVs at app-level can be added to any page within app, and is easy to locate and update (since all LOV definitions are stored in one location).

3.1.6 Plug-Ins framework - was introduced in Oracle APEX 4.0

Allows developers to create their own plug-ins to add additional functionality in a supported and declarative way. Usually, a tool like Ajax is used to add custom functionality. The con of this approach is to place the code in different locations such as within the database, in external JavaScript files, and so on. On the other hand, turning that code into a plug-in is more convenient to use and manage because the code resides in one object. With the help of open source jQuery components you can create plug-ins without generating huge amount of code manually.

Plug-ins are shared component objects that allow you to extend the functionality of item types, region types, dynamic actions, and process types. The plug-in architecture includes a declarative development environment that lets you create custom versions of these built-in objects. For example, you can create your own star rating item that allows your user to provide feedback using a one-to-five star graphic. This new item type can then be used across all your apps. The main part of a plug-in consists of PL/SQL code and can be supplemented with JavaScript and CSS code. A plug-in consists of one or more PL/SQL functions. These functions can either reside in the database (in a package or a set of functions) or be included within the plug-in.

NOTE: Plug-in OTN page (https://www.oracle.com/tools/technologies/apex-plug-ins.html) has several different plug-ins developed by APEX community.

3.1.7 Shortcuts to avoid repetitive coding of HTML or PL/SQL functions

You can use a shortcut to define a page control such as a button, HTML text, or a PL/SQL procedure. Once defined, you can invoke a shortcut using specific syntax unique to the location in which the shortcut is used. Shortcuts can be referenced many times, thus reducing code redundancy. When you create a shortcut, you must specify the type of shortcut you want to create. Oracle APEX supports the following shortcut types:

1. PL/SQL Function Body
2. HTML Text
3. HTML Text with Escaped Special Characters
4. Image
5. Text with JavaScript Escaped Single Quotes
6. Message
7. Message with JavaScript Escaped Special Quotes

Shared comp. list 4. NAVIGATION

3.1.8 Lists - collections of links

For each list entry, you specify display text, a target URL, and other attributes to control when and how the list entry displays. Once created, you can add a list to any number of pages within an app by creating a region and specifying the region type as List. You control the display of the list and the appearance of all list entries by linking list to a template. Lists are of two types:

Static Lists - When you create a static list you define a list entry label and a target (either a page or a URL). You can add list entries when you create the list (from scratch), by copying existing entries or by adding the list entries. You can control when list entries display by defining display conditions.

Dynamic Lists - are based on a SQL query or a PL/SQL function executed at runtime.

3.1.9 Navigation Menu

You might have seen a horizontal bar at the top of a website. The options provided on this bar help you navigate to different pages within that site. app Express provides you with a similar component called Navigation Menu. It is an effective way to navigate users between pages of an app. A navigation menu is basically a list with hierarchical entries.

When you create an app, the Create app Wizard automatically creates a navigation menu for you and populates it with one or more list entries. Types of navigation menus include Side Me

Navig. Top Menu, or Mega Menu
By default, the navigation menu is displayed as a left sidebar. Users can expand or collapse the Side Navigation Menu by clicking on the menu icon from the header. This navigation menu renders the navigation items using a tree component that enables users to expand or collapse sub items. A Top Navigation Menu displays at the top of the app. You can change how and where a navigation menu displays by editing the app User Interface Details. The Top Navigation Mega Menu template renders your app navigation in a pop-up panel that can be opened or closed from the header menu button. Users can expand or collapse a Mega Menu by clicking on the menu icon from the header. Mega menus are especially useful when you want to display all navigation items at once to your user.

3.1.10 Breadcrumb

A breadcrumb (A) is a hierarchical list of links rendered using a template. For example, you can display breadcrumbs as a list of links or as a breadcrumb path. A breadcrumb trail indicates where you are within the app from a hierarchical perspective. In addition, you can click a specific breadcrumb link to instantly view the page. For example, in the screen shot below you can access the app home page by clicking its breadcrumb entry (B). You use breadcrumbs as a second level of navigation at the top of each page, complementing other navigation options such as Navigation Menu and Navigation Bar.

3.1.11 Navigation Bar List

Just like menus, lists, and breadcrumb, a navigation bar is also created to link users to various pages within an app. Typically, a navigation bar carries links such as user id, logout, feedback, help, and so on. It appears on top-right of every app page. While creating a navigation bar, you can specify an image name, label, display sequence, and target location.

Shared comp. list 5. USER INTERFACE

3.1.12 User Interface Attributes

The app user interface determines default characteristics of app and optimizes the display for the target environment. This is place where you define your app logo.

3.1.13 Themes and Templates

Instead of telling App Builder how to design and style your pages using HTML, CSS, and JavaScript code that you may not be familiar with, you only apply theme and templates you want to use and the Oracle APEX engine does the rest of the job for you.

A theme is a named collection of templates that defines the look and feel of app user interface. Each theme contains templates for every type of app component and page control, including individual pages, regions,reports, lists, labels, menus, buttons, and list of values.

APEX engine constructs the appearance of each page in a app using Templates . Templates define how pages, page controls, and page components display. Templates control the look and feel of the pages in your app using snippets of HTML, CSS, JavaScript and image icons. As you create your app, you specify templates for pages, regions, reports, lists, labels, menus, buttons, and pop-up lists of values.

Groups of templates are organized into named collections called themes.

App Builder also allows you to access themes and template mechanism so you can create new ones according to your own requirements or amend (improve) existing ones. Oracle APEX ships with an extensive theme repository. Administrators can add themes to the theme repository, as follows:

1. Workspace administrators can create themes that are available to all developers within the workspace. Such themes are called workspace themes.
2. Instance administrators can create public themes by adding them to the Oracle APEX Administration Services. Once added, these public themes are available to all developers across all workspaces in an instance.
3. Apps you create with the Create app Wizard use Universal Theme. Universal Theme - 42 features a responsive design, versatile UI components and enables developers to create web apps without extensive knowledge of HTML, CSS, or JavaScript. Responsive design enables you to design web pages so that the layout fits the available space regardless of the device on which page displays (eg desktop computer, laptop computer, tablet, or smartphone).

Shared comp. list 6. FILES

3.1.14 Static App and Workspace resource Files (img, CSS, JS)

Use these two links to upload, edit, and delete static files including images, custom style sheets, JavaScript files.

Static App file can be referenced from a specific app only, whereas workspace file can be accessed by any app in workspace. Here we use Static app Files option to upload your app logo.

Shared comp. list 7. DATA SOURCES

Shared comp. list 8. REPORTS

3.1.15 Report Queries

A report query is a printable document, which can be integrated with an app using buttons, list items, branches, or any other navigational components that allow for using URLs as targets. A report query is based on a standard SQL query. It can be downloaded as a PDF document, a Word document (RTF based), an Excel Spreadsheet (HTML based), or as an HTML file. The layout of a report query is customizable using RTF templates.

3.1.16 Report Layouts

Use Report Layouts in conjunction with a report region or report query to render data in a printer-friendly format, such as PDF, Word, or Excel. A report layout can be designed using the Template Builder Word plug-in and uploaded as a file of type RTF or XSL-FO. Report regions use a generic XSL-FO layout, which is customizable.

Shared comp. list 9. GLOBALIZATION

3.1.17 Globalization Attributes

If you want to develop apps that can run concurrently in different languages, then app Express is the right platform for this. In the Globalization interface, you can specify options such as the app Primary Language, Date/Time format, and some other options related to app globalization.

3.1.18 Translate app

A single Oracle DB and APEX instance can support an app in multiple languages. Translating an app involves multiple steps. To translate an app developed in App Builder, you must :

1. map the primary and target language
2. seed and export text to a translation file
3. translate the text
4. apply the translation file
5. publish the translated app.

Top.....Back

App Gallery "Sample Database Application

Is finished this tutorial.

Is an application that highlights common design concepts. It includes dedicated pages for customers, products, and orders as well as demonstrates the use of reports, charts, calendar, map, and tree.

5.6 IG : Learn IG - install "Sample Interactive Grids" app

Interactive Grid is a page component, which is used to display data in row/column matrix. In appearance, it looks similar to an Interactive Report (used in the next chapter) and delivers all features of Interactive Report, but it also allows you to manipulate data by clicking on a cell and editing its value (like Clipper Dbedit !), which is not available in Interactive Reports. In many ways this grid looks and acts like an Interactive Report. Here are some new features and differences:

1. Rows are fixed height and columns have a specific width that can be adjusted by dragging the border between column headers (G) or with Ctrl+Left/Right keys when the column header has keyboard focus.
2. Columns can be reordered with drag and drop (dragging the handle (E) at the start of a column heading) or with Shift+Left/Right keys when the column header has keyboard focus.
3. Columns can be sorted using the buttons (F) in the column heading or by using Alt+Up/Down key combination. Use the Shift key to add additional sort columns.
4. Columns can be frozen using the Freeze button (D) in the column heading pop-up menu. For example, to freeze the customers’ name column (on Page 2), click the Name column heading. A pop-up menu will appear with four options: Hide (A), Control Break (B), Aggregate (C), and Freeze (D). Select Freeze. Drag the border between the Name and Address columns (F) toward right to expand the Name column.
5. By default the toolbar and column headings stick to the top of the page and the footer sticks to the bottom when scrolling.
6. By default pagination uses a "Load More" button.The grid is keyboard navigable with a focused cell and current selected row (single selection by default).
7. Toolbar includes a Reset button by default, which restores all the report settings to their defaults.

IG : Install Sample Interactive Grids app

https://apex.oracle.com/pls/apex/f?p=4000:1:708714070734932::NO:RP:FB_FLOW_ID,F4000_P1_FLOW,P0_FLOWPAGE,RECENT_PAGES:19693,19693,19693 https://apex.oracle.com/pls/apex/possys/r/19693/home (+ ?session=104411212612767) Install sample application to get required tables.

1. App Gallery menu -> Sample Apps.Figure 5-10

2. Click icon Sample Interactive Grids app - functionality of new APEX Interactive Grid. Declarative features :

   1. Reporting capabilities
   2. Pagination options
   3. Editing capabilities
   4. Advanced techniques

3. On App Details page, click Install App button Minimum Version APEX 20.1, Oracle DB 11.2.0.4 (newer than XE 11.2.0.2.0 !!) Released 10-MAR-2020

4. On Install App wizard screen, accept the default Authentication scheme (APEX Accounts) and click Next.

5. On the next wizard screen, click Install App button. After a while, you will see the message Application installed.

6. SQL Workshop main menu -> Click Object Browser and see two required tables (EBA_DEMO_IG_EMP and EBA_DEMO_IG_PEOPLE) in the left pane under Tables category.

5.6.1 IG : Column Groups in IG (Interactive Grid)

Groups are used to associate columns together in the grid and Single Row View. Groups are added by expanding the Attributes node within Rendering tree, and right-clicking on Column Groups. Let's try this feature by executing the following steps:

1. Create a new page in your Sales app by clicking the Create Page button. Select the Report option in the first wizard screen, followed by the Interactive Grid option on the next screen.
2. Enter 100 for Page Number, Column Groups for Page Name, set Page Mode to Normal, Breadcrumb to Breadcrumb, Parent Entry to No Parent Entry, Entry Name to Column Groups, and click Next.
3. Select the default Navigation Preference Do not associate this page with a navigation menu entry, because this page is not associated with our sales app. Click Next.
4. On Report Source screen, keep default Off value of Editing Enabled, set Source Type to SQL Query, and enter the following SQL Statement in Enter a SQL SELECT Statement text area.

   SELECT  empno,  ename,  job,  mgr,  hiredate,  sal,  comm,  deptno,  onleave,  notes,  flex4  as tags
   FROM EBA_DEMO_IG_EMP

5. Click Create button to complete the page creation process.
6. In Page Designer, under Column Groups region (in the Rendering tree), right-click the Attributes node, and select Create Column Group (A) from the context menu. In the Properties pane, set the Heading attribute for this new group to Identity (cols emp name till dep).
7. Repeat step 6 to create two more groups. Enter Compensation (cols salary, commission) and Notes for their headings. The three column groups should look like (B).
8. Under the Columns Group region, expand the Columns node. Click the EMPNO column and set its Type to Hidden.
9. Set appropriate column headings, as shown in Figure 5-11.
10. Use the following table to associate each column with a group created in steps 6 & 7. To establish this association, click any column (ENAME, for example), scroll down to the Layout section, and set the Group property as follows:

Column Group Property

1. ENAME Identity

   1. JOB Identity
   2. MGR Identity
   3. HIREDATE Identity

2. SAL Compensation

   1. COMM Compensation
      DEPTNO Identity

3. ONLEAVE Notes

   1. NOTES Notes
   2. TAGS Notes

4. Save your work and run the page. Column group headings can be used to reorder columns just like column headings. Play around with column reordering (using drag and drop - see E in Figure 5-9) to see how group headings are split and joined.

5.6.2 IG : Editing Data in IG (Interactive Grid)

Interactive Grid allows you to manipulate data by clicking on a cell. When you add an Interactive Grid to a page, you specify (on Report Source wizard screen) whether it is editable–see step 4 in the previous section. If you initially turn this attribute off, you can always reverse it to make the Interactive Grid editable. Here are some points to know about editing:

1. Normally the grid is in navigation mode where arrow keys move from cell to cell. To enter edit mode, press the Edit button (A).
2. Alternatively, double-click a cell or press either the Enter key or F2 key in a cell.
3. To exit edit mode, press the Edit button (A) again or press the Escape key in a cell.
4. Use the Delete key on your keyboard to delete the selected rows.
5. Use the Insert key to add a row.
6. Second column (B) is a menu. It allows you to perform actions on the selected row such as Delete or Duplicate. Use the Revert Changes option from this menu to revert a record marked for deletion.
7. Editing is also supported in Single Row View. All edits are stored locally until you press the Save button (C). If you try to leave the page while there are unsaved changes you will be notified.
8. Any action that causes refreshing the data such as changing a filter or sorting will warn if there are unsaved changes. Pagination does not affect changes.

Execute following steps to enable editing in the Interactive Grid you added to Page 100 in the previous section.

1. Click the Attributes node (A) under the Column Group region and turn on the Enabled attribute (B). Make sure all three operations (C) are also enabled. Figure 5-14
2. Scroll down to the Toolbar section to ensure that the Show property is turned on and the two toolbar buttons (Reset and Save) are also enabled. Reset removes any customizations, such as filters, column width, ordering, and so forth, and reloads the report definition from the server. Save will only save changes made to this interactive grid, without needing to save the whole page. The save button will be displayed only when the interactive grid is editable and the end user has the authorization to add, update, or delete records.3. After making these changes, save and run the page. Notice that the row selector (D) and the Selection Actions menu (B) columns (in Figure 5-13) are added automatically. A process named Save Interactive Grid Data is also added to the Processing tab with an Interactive Grid - Automatic Row Processing (DML) type process to perform DML processing for you without writing any SQL code. This process is added by default when an Interactive Grid is made editable. Play around with the interactive grid by adding, modifying, and deleting rows.

5.6.3 IG : Changing Column Type

By default, the type of a column in an Interactive Grid is inherited from the base table. For example, the names of employees are displayed in a Text Field column type, while their salaries are shown in Number Field column type. In this exercise, you will change the default types of some columns to some other types, as follows:
A. The Job column will be presented as a Radio Group to select one from a list of distinct jobs
B. The value for the Manager column will be selected from a pop-up LOVC. The Hire Date will use a Date Picker that opens on focus
D. Display Yes/No in On Leave column
E. The Tags column will use a Shuttle type to select multiple values

1. With Page 100 being displayed in the Page Designer, expand the Columns node and click the JOB column. Set its Type attribute to Radio Group. When you select the radio group type, you are asked to associate a list of values to populate the item. For the List of Values Type attribute, select SQL Query and enter

   SELECT DISTINCT job a, job b FROM EBA_DEMO_IG_EMP  

   in SQL Query box. Also turn off set Display Extra Values and Display Null Values properties – see section 5.4.2 for details. The SQL Query fetches distinct job IDs from the table and shows them in the JOB column using the radio group type.

2. Next, click the MGR column and change its Type to Popup LOV. Then, select SQL Query for List of Values Type and enter the following statement in SQL Query box.

   SELECT ENAME as d, EMPNO as r
   FROM EBA_DEMO_IG_EMP
   WHERE JOB = 'MANAGER' or JOB = 'PRESIDENT' order by 1

3. Click the HIREDATE column. The Type attribute of this column is already set to Date Picker and this is what we want. The only attribute of this column that needs to be changed is Show (under Settings). Change it from on icon click to On focus to display the date picker when the focus is on this column.

4. Click the ONLEAVE column and set its Type to Switch. This will display either On or Off state for this column.

5. Finally, click the TAGS column. Change its Type from Textarea to Shuttle. Set List of Values Type to Static Values and enter static values (by clicking Display1, Display2 text next to the Static Values property) as shown in the following screenshot. Recall that you created a Static LOV through Shared Components interface inChapter 3 - Section 3.4.1. There you specified a pair of static Display and Return values. Here, you didn’t use the Return value, because the Return value is optional. If a Return value is not included, the return value equals the display value. Click the OK button to close the Static Values screen. Figure 5-16

6. Save and run the page. Click different cells under the JOB column and press F2 to see values in a radio group (A). Similarly, press F2 in the Manager column. This will display a drop down list (B) in the selected cell carrying names of president and managers. Double click the column prior to the Hire Date column, and press the Tab key. The cursor’s focus will move on to the Hire Date column and immediately the Date Picker window (C) will pop up. Keep pressing the Tab key to access the On Leave column, which will show a Yes and No switch (D). Access the Tags column, which should come up with a shuttle (E) carrying the five values defined in step 5. Using the arrow key in the shuttle, move all these values to the right pane and click the cross icon to close the shuttle. Click the Save button to write your changes to the database.

5.6.4 IG : Protecting Rows in Interactive Grid

In this example, you will see how to protect rows in an Interactive Grid. For this purpose, you need to add a column named CTRL to implement a simple rule that Managers and Presidents cannot be edited or deleted. This column is then selected in the Allowed Row Operations Column property in the Attributes node.

1. With Page 100 being displayed in the Page Designer, click the Column Groups region and amend the SELECT statement as follows (the amendment is shown in bold):
   SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno,onleave,notes, flex4 as tags,
   case when JOB = 'MANAGER' or JOB = 'PRESIDENT' then ''
   else 'UD' end as CTRL
   FROM EBA_DEMO_IG_EMP
2. After amending the SQL query you will see the CTRL column under the Columns node. Click this column and set its Type to Hidden. Also turn on the Query Only property (under Source). For explanation, see Chapter 7 section 7.4.2.
3. Click the Attributes node under the Column Groups region and set Allowed Row Operations Column (in the Edit section) to the CTRL column.4. Save the page and run it. Click the Edit button. Rows that cannot be edited or deleted are grayed out (A) in the edit mode.

NOTE: If you encounter the error Interactive Grid 'Column Groups' doesn't have a primary key column defined which is required for editing or in a master detail relationship, then click the EMPNO column in the Page Designer, and turn on the Primary Key property in the Source section. Save and run the page.

5.6.5 IG : Scroll Paging

Another exciting feature of Interactive Grids is scroll paging (also known as infinite scrolling or virtual paging). It is enabled by setting Pagination attribute to Scroll. After enabling this attribute, the region appears to carry the entire result set but rows are rendered on demand as you scroll. When you scroll down in the Interactive Grid, the model fetches data from the server as it is needed by the view. You can even drag the scroll bar handle all the way to the bottom and then scroll up. You need a database table with lots of records to assess this feature. In this exercise, you will use EBA_DEMO_IG_PEOPLE table, which carries over 4000 records.

1. Create a new page using the instructions mentioned earlier in Section 5.6.1. Set Page Number to 111, set Page Name to Scroll Paging, and enter the following SELECT statement. Rest of the page properties will remain the same. SELECT name, country, rating FROM EBA_DEMO_IG_PEOPLE
2. In the Page Designer, click the Attributes node under the Scroll Paging region. Set Type under the Pagination section to Scroll and turn on the Show Total Row Count.
3. Save and run the page and scroll down using your mouse wheel to test this amazing feature. You will see total number of records at the bottom of the Interactive Grid.

5.6.6 IG : Master Detail. And Detail. And Detail...

Interactive Grid makes it effortless to create master-detail relationships and go any number of levels deep and across. You can create all types of master-detail-detail screens with ease. In this section, I’ll demonstrate this feature.

1. From the SQL Workshop menu, select SQL Scripts and click the Upload button. In the Upload Script screen, click the Choose File button. In the Open dialog box, select master_detail_detail.sql file from Chapter 5 folder in the book’s source code and click Upload. In the SQL Scripts interface click the Run button appearing in the last column. On the Run Script screen, click the Run Now button. The script will execute to create four tables (MD_continent, MD_country, MD_city, and MD_population) along with relevant data to demonstrate the master detail detail feature. You can view these tables from the SQL Workshop > Object Browser interface.

2. Create a new page by clicking the Create Page button in the App Builder interface. This time, select the first Blank Page option and click Next. Set Page Number to 112, Name to Master Detail Detail, Page Mode to Normal, and click Next. On the Navigation Menu screen, select the first Navigation Preference to not associate this page with any sales app navigation menu entry. On the final wizard screen, click Finish.

3. In the Page Designer, right-click the Regions node on the Rendering tab and select Create Region. Set the following properties for the new region. This region will display data from the MD_continent table.

   Property Value

   1. Title Continents
   2. Type (under Identification) Interactive Grid
   3. Type (under Source) SQL Query
   4. SQL Query SELECT * FROM MD_continent After entering the SQL query, click anywhere outside the query box. Expand the Columns node under this region. Click the CONTINENT_ID column. Set its Type to Hidden and turn on the Primary Key property (under Source). You must define a primary key column for an interactive grid region, which is required to establish a master detail relationship.

4. Create another region under the Continents region by right-clicking the main Regions node. This region will act as the detail for the Continents region. At run-time when you select a continent, this region will display a list of countries in the selected continent. Set the following properties for this new region.

   Property Value

   1. Title Countries
   2. Type (under Identification) Interactive Grid
   3. Type (under Source) SQL Query
   4. SQL Query SELECT * FROM MD_country Expand the Columns node under the Countries region. Click the COUNTRY_ID column. Set its Type to Hidden and turn on the Primary Key property (under Source). You set the Primary Key property to Yes, because this region will act as a master for the Cities region created in the next step. Now, associate this detail region to its master (Continents). Click the Countries region and set the Master Region property (under Master Detail) to Continents. This should be set when this region is the detail region in a master-detail relationship with another region on the page. For the master-detail relationship to work correctly, you must also select the column(s) in the detail region, which are foreign keys to the master region, by setting the Master Column property. Click the CONTINENT_ID column (a foreign key) in the Countries region. Set its Type property to Hidden and Master Column (under Master Detail) to CONTINENT_ID, which references the same column in the master region.
   5. Create another region and place it under the Countries region. This region will show a list of cities when you select a country from its master region. Set the following properties for this region:

   Property Value

   1. Title Cities
   2. Type (under Identification) Interactive Grid
   3. Type (under Source) SQL Query
   4. SQL Query SELECT * FROM MD_cityMaster Region Countries Expand the Columns node under the Cities region. Click the CITY_ID column. Set its Type to Hidden and turn on the Primary Key property (under Source). Click the COUNTRY_ID column in this region. Set the Type of this column to Hidden and Master Column to COUNTRY_ID to point to the same column in the Countries region.
   5. Create the last region to display population of a city.

   Property Value

   1. Title Population
   2. Type (under Identification) Interactive Grid
   3. Type (under Source) SQL Query
   4. SQL Query SELECT * FROM MD_population
   5. Master Region Cities Expand the Columns node under the Population region. Click the POPULATION_ID column. Set its Type to Hidden. Since this is the last region, you do not need to specify this column as a primary key. However, you have to set a couple of properties for the CITY_ID column in this region to associate it with its master. Click the CITY_ID column, set its Type property to Hidden and Master Column to CITY_ID. That’s it!

Save and run the page. Click the row representing Europe (A) in the first region. As you click this row, the second region will display countries in the Europe continent. Click Germany (B) in the second region. This will refresh the third region with a list of cities in Germany. Click the Berlin city (C) to see its population (D) in the forth region.Figure 5-18

Summary of 5.6 IG :

Techniques in this chapter:

1. Declaratively created report and form pages and linked them together
2. Placed form input items using 12 columns grid layout
3. Changed default type of an item and used LOV
4. Created validations to prevent customer record deletion with existing orders and to check customers credit limits.
5. Used a dynamic action to automatically refresh page
6. Used various features of new Interactive Grid
7. Learned how to change types of columns
8. Got hands-on exposure to Master Detail Detail feature.

Top.....Back drop table DEMO_ORDER_ITEMS;
drop table DEMO_ORDERS;
drop table DEMO_ORDERS_ERR$;

drop table DEMO_STATES;
drop table DEMO_CUSTOMERS;
drop table DEMO_CUSTOMERS_ERR$;
drop table DEMO_PRODUCT_INFO;

drop sequence

drop sequence DEMO_ORDER_ITEMS_SEQ;
drop sequence DEMO_ORDERS_SEQ;

drop sequence DEMO_STATES_SEQ;
drop sequence DEMO_CUSTOMERS_SEQ_SEQ;
drop sequence DEMO_PRODUCT_INFO_SEQ;

prompt 1. DEMO_ CUSTOMERS

--update DEMO_CUSTOMERS set CUST_STREET_ADDRESS2 ='aaaa bbbbb' where CUSTOMER_ID=10 ;
--SELECT CUST_STREET_ADDRESS2, CUSTOMER_ID from DEMO_CUSTOMERS order by CUST_LAST_NAME desc ; 

CREATE table "DEMO_CUSTOMERS" (
    "CUSTOMER_ID"          NUMBER NOT NULL,
    "CUST_FIRST_NAME"      VARCHAR2(20) NOT NULL,
    "CUST_LAST_NAME"       VARCHAR2(20) NOT NULL,
    "CUST_STREET_ADDRESS1" VARCHAR2(60),
    "CUST_STREET_ADDRESS2" VARCHAR2(60),
    "CUST_CITY"            VARCHAR2(30),
    "CUST_STATE"           VARCHAR2(2),
    "CUST_POSTAL_CODE"     VARCHAR2(10),
    "CUST_EMAIL"           VARCHAR2(30),
    "PHONE_NUMBER1"        VARCHAR2(25),
    "PHONE_NUMBER2"        VARCHAR2(25),
    "URL"                  VARCHAR2(100),
    "CREDIT_LIMIT"         VARCHAR2(2),
    "TAGS"                 VARCHAR2(4000),
    constraint  "DEMO_CUSTOMERS_PK" primary key ("CUSTOMER_ID")
)
/

    -- CONSTRAINT "DEMO_CUST_CREDIT_LIMIT_MAX" CHECK (credit_limit <= 5000) ENABLE, 
     --CONSTRAINT "DEMO_CUSTOMERS_PK" PRIMARY KEY ("CUSTOMER_ID") USING INDEX  ENABLE, 
     --CONSTRAINT "DEMO_CUSTOMERS_UK" UNIQUE ("CUST_FIRST_NAME", "CUST_LAST_NAME") USING INDEX  ENABLE

alter table "DEMO_CUSTOMERS" modify (
  "CREDIT_LIMIT" NUMBER(9,2)
)
/   

CREATE sequence "DEMO_CUSTOMERS_SEQ" 
/

CREATE trigger "BI_DEMO_CUSTOMERS"  
  before insert on "DEMO_CUSTOMERS"              
  for each row 
begin  
  if :NEW."CUSTOMER_ID" is null then
    select "DEMO_CUSTOMERS_SEQ".nextval into :NEW."CUSTOMER_ID" from sys.dual;
  end if;
end;
/   

prompt 2. DEMO PRODUCT INFO

--update DEMO_PRODUCT_INFO set CATEGORY ='aaaa' where PRODUCT_ID=8 ;
--SELECT PRODUCT_NAME, CATEGORY, PRODUCT_ID from DEMO_PRODUCT_INFO order by PRODUCT_NAME  ; 

CREATE table "DEMO_PRODUCT_INFO" (
    "PRODUCT_ID"          NUMBER NOT NULL,
    "PRODUCT_NAME"        VARCHAR2(50),
    "PRODUCT_DESCRIPTION" VARCHAR2(2000),
    "CATEGORY"            VARCHAR2(30),
    "PRODUCT_AVAIL"       VARCHAR2(1),
    "LIST_PRICE"          NUMBER(8,2),
    "PRODUCT_IMAGE"       BLOB,
    "MIMETYPE"            VARCHAR2(255),
    "FILENAME"            VARCHAR2(400),
    "IMAGE_LAST_UPDATE"   TIMESTAMP(6) WITH LOCAL TIME ZONE,
    "TAGS" VARCHAR2(4000), 
    constraint  "DEMO_PRODUCT_INFO_PK" primary key ("PRODUCT_ID")
)
/

--alter table "DEMO_PRODUCT_INFO" add column TAGS VARCHAR2(4000) ;

CREATE sequence "DEMO_PRODUCT_INFO_SEQ" 
/

CREATE trigger "BI_DEMO_PRODUCT_INFO"  
  before insert on "DEMO_PRODUCT_INFO"              
  for each row 
begin  
  if :NEW."PRODUCT_ID" is null then
    select "DEMO_PRODUCT_INFO_SEQ".nextval into :NEW."PRODUCT_ID" from sys.dual;
  end if;
end;
/   

prompt 3. DEMO_ STATES (no PK)

CREATE table "DEMO_STATES" (
    --"STATE_ID"   VARCHAR2(2),
    "ST"   VARCHAR2(30),
    "STATE_NAME" VARCHAR2(30)
)
/

prompt 4. DEMO_ ORDERS

update DEMO_ORDERS set ORDER_TIMESTAMP = ORDER_TIMESTAMP + 3600 * 48

Formatting of the TIMESTAMP datatype with fractional seconds:  
SELECT TO_CHAR(ORDER_TIMESTAMP,'DD.MM.YYYY HH24:MI:SS.FF3') ORDER_TIMESTAMP, ORDER_ID from DEMO_ORDERS order by ORDER_ID desc ; 
prompt   ORDER_TIMESTAMP=30.08.2020 20:18:21:000       ORDER_ID=10

update DEMO_ORDERS set ORDER_TIMESTAMP = TO_TIMESTAMP('01.09.2020 11:10:55.888','DD.MM.RRRR HH24:MI:SS.FF3') where ORDER_ID=10 ;
SELECT TO_CHAR(ORDER_TIMESTAMP,'DD.MM.YYYY HH24:MI:SS.FF3') ORDER_TIMESTAMP, ORDER_ID from DEMO_ORDERS order by ORDER_ID desc ; 
rem Commit statement not applicable. All statements are **automatically committed**. 

CREATE table "DEMO_ORDERS" (
    "ORDER_ID"        NUMBER NOT NULL,
    "CUSTOMER_ID"     NUMBER NOT NULL,
    "ORDER_TOTAL"     NUMBER(8,2),
    "ORDER_TIMESTAMP" TIMESTAMP(6) WITH LOCAL TIME ZONE,
    "USER_NAME"       VARCHAR2(100),
    "TAGS"            VARCHAR2(4000),
    constraint  "DEMO_ORDERS_PK" primary key ("ORDER_ID")
)
/

CREATE sequence "DEMO_ORDERS_SEQ" 
/

CREATE trigger "BI_DEMO_ORDERS"  
  before insert on "DEMO_ORDERS"              
  for each row 
begin  
  if :NEW."ORDER_ID" is null then
    select "DEMO_ORDERS_SEQ".nextval into :NEW."ORDER_ID" from sys.dual;
  end if;
end;
/   

--ALTER TABLE "DEMO_ORDERS" DROP CONSTRAINT "DEMO_ORDERS_FK" 

ALTER TABLE "DEMO_ORDERS" ADD CONSTRAINT "DEMO_ORDERS_FK" 
FOREIGN KEY ("CUSTOMER_ID")
REFERENCES "DEMO_CUSTOMERS" ("CUSTOMER_ID")
/
--delete DEMO_ORDERS

prompt 5. DEMO ORDER ITEMS

CREATE table "DEMO_ORDER_ITEMS" (
    "ORDER_ITEM_ID" NUMBER NOT NULL,
    "ORDER_ID"      NUMBER NOT NULL,
    "PRODUCT_ID"    NUMBER NOT NULL,
    "UNIT_PRICE"    NUMBER(8,2) NOT NULL,
    "QUANTITY"      NUMBER(8,0) NOT NULL,
    constraint  "DEMO_ORDER_ITEMS_PK" primary key ("ORDER_ITEM_ID")
)
/

CREATE sequence "DEMO_ORDER_ITEMS_SEQ" 
/

CREATE trigger "BI_DEMO_ORDER_ITEMS"  
  before insert on "DEMO_ORDER_ITEMS"              
  for each row 
begin  
  if :NEW."ORDER_ITEM_ID" is null then
    select "DEMO_ORDER_ITEMS_SEQ".nextval into :NEW."ORDER_ITEM_ID" from sys.dual;
  end if;
end;
/   

ALTER TABLE "DEMO_ORDER_ITEMS" ADD CONSTRAINT "DEMO_ORDER_ITEMS_FK" 
FOREIGN KEY ("ORDER_ID")
REFERENCES "DEMO_ORDERS" ("ORDER_ID")
ON DELETE CASCADE
/

ALTER TABLE "DEMO_ORDER_ITEMS" ADD CONSTRAINT "DEMO_ORDER_ITEMS_PRODUCT_ID_FK" 
FOREIGN KEY ("PRODUCT_ID")
REFERENCES "DEMO_PRODUCT_INFO" ("PRODUCT_ID")
/

Top.....Back

tbl01 DEMO_CUSTOMERS

1. Click the SQL Workshop menu (A).

2. Select the Object Browser option (B) from the menu, which is used to review and maintain database objects (such as, tables, sequences, views, functions, triggers, and so on).

3. In Object Browser page, select the Tables option (C) from select list. This action will show a list of existing tables in the left pane, if there are any.

4. Click Create menu select list (D) , and select Table (E) from the menu list to create a new table. This will invoke a wizard named Create Table

5. DEMO_CUSTOMERS for table name (A), CUSTOMER_ID in the first Column Name (B), NUMBER (C) Type, Not Null (D)
   CUST_FIRST_NAME, CUST_LAST_NAME, CUST_STREET_ADDRESS1, CUST_STREET_ADDRESS2, CUST_CITY, CUST_STATE,
   CUST_POSTAL_CODE, CUST_EMAIL, PHONE_NUMBER1, PHONE_NUMBER2, URL, CREDIT_LIMIT, TAGS

6. Scale column specify the number of characters each column will hold

7. Primary Key -> select Populated from a new sequence (A) -> Accept DEMO_CUSTOMERS_PK (B) for the Primary Key Constraint Name -> select CUSTOMER_ID (C) -> default Sequence Name or enter any other

8. Skipp Foreign Key and Constraints wizard screens. On the final Confirm screen, click the Create Table button. SQL button shows auto-generated SQL see above.

"Column Name","Data Type","Nullable","Default","Primary Key"
"CUSTOMER_ID","NUMBER","No"," ","1"
"CUST_FIRST_NAME","VARCHAR2(20)","No"," "," "
"CUST_LAST_NAME","VARCHAR2(20)","No"," "," "
"CUST_STREET_ADDRESS1","VARCHAR2(60)","Yes"," "," "
"CUST_STREET_ADDRESS2","VARCHAR2(60)","Yes"," "," "
"CUST_CITY","VARCHAR2(30)","Yes"," "," "
"CUST_STATE","VARCHAR2(2)","Yes"," "," "
"CUST_POSTAL_CODE","VARCHAR2(10)","Yes"," "," "
"CUST_EMAIL","VARCHAR2(30)","Yes"," "," "
"PHONE_NUMBER1","VARCHAR2(25)","Yes"," "," "
"PHONE_NUMBER2","VARCHAR2(25)","Yes"," "," "
"URL","VARCHAR2(100)","Yes"," "," "
"CREDIT_LIMIT","VARCHAR2(2)","Yes"," "," "
"TAGS","VARCHAR2(4000)","Yes"," "," "

-- data : see oracle_apex_....csv files 

CUSTOMER_ID,CUST_FIRST_NAME,CUST_LAST_NAME,CUST_STREET_ADDRESS1,CUST_STREET_ADDRESS2,CUST_CITY,CUST_STATE,CUST_POSTAL_CODE,CUST_EMAIL,PHONE_NUMBER1,PHONE_NUMBER2,URL,CREDIT_LIMIT,TAGS
1,John,Dulles,45020 Aviation Drive, ,Sterling,VA,20166,techies81@gmail.com,703-555-2143,703-555-8967,http://www.johndulles.com,1000, 
2,William,Hartsfield,6000 North Terminal Parkway, ,Atlanta,GA,30320, ,404-555-3285, , ,1000,REPEAT CUSTOMER
3,Edward,Logan,1 Harborside Drive, ,East Boston,MA,2128, ,617-555-3295, , ,1000,REPEAT CUSTOMER
4,Frank,OHare,10000 West OHare, ,Chicago,IL,60666, ,773-555-7693, , ,1000, 
5,Fiorello,LaGuardia,Hangar Center,Third Floor,Flushing,NY,11371, ,212-555-3923, , ,1000, 
6,Albert,Lambert,10701 Lambert International Blvd., ,St. Louis,MO,63145,techies81@gmail.com,314-555-4022, , ,1000, 
7,Eugene,Bradley,Schoephoester Road, ,Windsor Locks,CT,6096,techies81@gmail.com,860-555-1835, , ,1000,REPEAT CUSTOMER

tbl02 DEMO_PRODUCT_INFO

Contains BLOB (Binary Large Objects) type (B), which is an Oracle data type that can hold up to 4 GB of data. BLOBs are handy for storing digitized information, such as images, audio, and video. This type can also be used to store document files like PDF, MS Word, MS Excel, MS PowerPoint and CSV. We are also using a TIMESTAMP type (C) to store the date when a product image is updated.

Select the Populated from a new sequence

Skip the Foreign Key and Constraints wizard screens.

PRODUCT_ID,PRODUCT_NAME,PRODUCT_DESCRIPTION,CATEGORY,PRODUCT_AVAIL,LIST_PRICE,PRODUCT_IMAGE,MIMETYPE,FILENAME,IMAGE_LAST_UPDATE
1,Air Max 2090,"Bring the past into the future with the Nike Air Max 2090, a bold look inspired by the DNA of the iconic Air Max 90.",Men,Y,1500,,image/png,1_AirMax2090.png,21-JUN-20 06.45.19.434901 AM
2,LeBron Soldier 13 Red,"LeBron works hard in the offseason, getting stronger and fine-tuning his already devastating skills. ",Men,Y,200,,image/png,2_LeBron Soldier 13 Red.png,21-JUN-20 06.45.19.733931 AM
3,LeBron Soldier 13 White,"LeBron works hard in the offseason, getting stronger and fine-tuning his already devastating skills. ",Men,Y,150,,image/png,3_LeBron Soldier 13 White.png,21-JUN-20 06.45.19.738065 AM
4,Air Max 720,"The Nike Air Max 720 goes bigger than ever before with Nike’s tallest Air unit yet, which offers more air underfoot for unimaginable, all-day comfort.",Women,Y,60,,image/png,4_Air Max 720.png,21-JUN-20 06.45.19.741109 AM
5,Air Max 270,"Nike's first lifestyle Air Max takes a leap forward with the softest, smoothest, most resilient foam yet in the Nike Air Max 270 React.",Women,Y,80,,image/png,5_Air Max 270.png,21-JUN-20 06.45.19.743820 AM
6,Air Jordan 6,Inspired by the original AJ6 - released the year MJ won his first professional championship - the Air Jordan 6 Retro maintains the iconic look with updated materials.,Women,Y,120,,image/png,6_Air Jordan 6.png,21-JUN-20 06.45.19.746745 AM
7,LeBron 17 BG,The LeBron 17 harnesses LeBrons strength and speed with containment and support for all-court domination.,Women,Y,30,,image/png,7_LeBron 17 BG.png,21-JUN-20 06.45.19.749888 AM
8,Air Max 270 Gradient,"By pulling inspiration from two icons the Air Max 180 and Air Max 93, the Nike Air Max 270 has the tallest Air unit to date.",Men,Y,125,,image/png,8_Air Max 270 Gradient.png,21-JUN-20 06.45.19.752623 AM
9,Air Max 180 Trainer,"Hi-Tech meets high impact in these durable, performance-based Nike training shoes.",Men,Y,110,,image/png,9_Air Max 180 Trainer.png,21-JUN-20 06.45.19.755284 AM
10,Jr Phantom Vision,"Wenn du auf der Suche nach Multinockenschuhen für Kinder bist, ist der kicker-Onlineshop genau das Richtige - 10112892",Kids,Y,50,,image/png,10_Jr Phantom Vision.png,21-JUN-20 06.45.19.757715 AM

tbl03 DEMO_STATES no PK (51 row)

Skip the Foreign Key and Constraints wizard screens.

STATE_ID,STATE_NAME
AK,ALASKA
AL,ALABAMA
AR,ARKANSAS
AZ,ARIZONA
CA,CALIFORNIA
CO,COLORADO
CT,CONNECTICUT
DC,DISTRICT OF COLUMBIA
DE,DELAWARE
FL,FLORIDA
GA,GEORGIA
HI,HAWAII
IA,IOWA
ID,IDAHO
IL,ILLINOIS
IN,INDIANA
KS,KANSAS
KY,KENTUCKY
LA,LOUISIANA
MA,MASSACHUSETTS
MD,MARYLAND
ME,MAINE
MI,MICHIGAN
MN,MINNESOTA
MO,MISSOURI
MS,MISSISSIPPI
MT,MONTANA
NC,NORTH CAROLINA
ND,NORTH DAKOTA
NE,NEBRASKA
NH,NEW HAMPSHIRE
NJ,NEW JERSEY
NM,NEW MEXICO
NV,NEVADA
NY,NEW YORK
OH,OHIO
OK,OKLAHOMA
OR,OREGON
PA,PENNSYLVANIA
RI,RHODE ISLAND
SC,SOUTH CAROLINA
SD,SOUTH DAKOTA
TN,TENNESSEE
TX,TEXAS
UT,UTAH
VA,VIRGINIA
VT,VERMONT
WA,WASHINGTON
WI,WISCONSIN
WV,WEST VIRGINIA
WY,WYOMING

tbl04 DEMO_ORDERS

Select Populated from a new sequence.

Foreign Key wizard screen :
Create a relationship between DEMO_CUSTOMERS and DEMO_ORDERS table.
Default Disallow Delete option (B) will block deletion of rows from the customers table when they are utilized in the orders master table.

Skip Constraints wizard screen.

ORDER_ID,CUSTOMER_ID,ORDER_TOTAL,ORDER_TIMESTAMP,USER_NAME,TAGS
1,7,1890,17-APR-20 06.45.19.000000 AM,DEMO, 
2,1,2380,01-MAY-20 06.45.19.000000 AM,DEMO,LARGE ORDER
3,2,1640,12-MAY-20 06.45.19.000000 AM,DEMO, 
4,5,1090,14-MAY-20 06.45.19.000000 AM,DEMO, 
5,6,950,24-MAY-20 06.45.19.000000 AM,DEMO, 
6,3,1515,29-MAY-20 06.45.19.000000 AM,DEMO, 
7,3,905,03-JUN-20 06.45.19.000000 AM,DEMO, 
8,4,1060,11-JUN-20 06.45.19.000000 AM,DEMO, 
9,2,730,17-JUN-20 06.45.19.000000 AM,DEMO, 
10,7,870,20-JUN-20 06.45.19.000000 AM,DEMO, 
192,7,180,21-JUN-20 07.03.03.717283 AM,DEMO, 
191,1,800,21-JUN-20 06.52.48.105288 AM,DEMO, 
211,7,100,21-JUN-20 06.57.10.978243 AM,DEMO, 
212,6,210,21-JUN-20 06.59.13.144771 AM,DEMO, 
213,6,500,21-JUN-20 07.00.17.533966 AM,DEMO, 
251,7,155,02-DEC-20 06.29.39.463839 AM,DEMO, 
231,7,1421,06-JUL-20 09.17.31.833485 AM,DEMO, 

tbl05 DEMO_ORDER_ITEMS

Has two foreign key references : ORDER_ID and PRODUCT_ID.

FK to DEMO_ORDERS : For this relationship you selected the Cascade Delete option (A), which simultaneously removes both parent and child records from the two tables when you delete an order.

DEMO_ORDER_ITEMS_PRODUCT_ID_FK : Select Disallow Delete for delete option.

Skip Constraint wizard screen.

"ORDER_ITEM_ID","ORDER_ID","PRODUCT_ID","UNIT_PRICE","QUANTITY"
"500","1","1","50","10"
"501","1","2","80","8"
"502","1","3","150","5"
"503","2","1","50","3"
"504","2","2","80","3"
"505","2","3","150","3"
"506","2","4","60","3"
"507","2","5","80","3"
"508","2","6","120","2"
"509","2","7","30","2"
"510","2","8","125","4"
"511","2","9","110","2"
"512","2","10","50","2"
"513","3","4","60","4"
"514","3","5","80","4"
"515","3","6","120","4"
"516","3","8","125","4"
"517","3","10","50","2"
"518","4","6","120","2"
"519","4","7","30","6"
"520","4","8","125","2"
"521","4","9","110","2"
"522","4","10","50","4"
"523","5","1","50","3"
"524","5","2","80","2"
"525","5","3","150","2"
"526","5","4","60","3"
"527","5","5","80","2"
"528","6","3","150","3"
"529","6","6","120","3"
"530","6","8","125","3"
"531","6","9","110","3"
"532","7","1","50","2"
"533","7","2","80","2"
"534","7","4","60","2"
"535","7","5","80","2"
"536","7","7","30","3"
"537","7","8","125","1"
"538","7","10","50","3"
"539","8","2","80","2"
"540","8","3","150","3"
"541","8","6","120","1"
"542","8","9","110","3"
"543","9","4","60","4"
"544","9","5","80","3"
"545","9","8","125","2"
"546","10","1","50","5"
"547","10","2","80","4"
"548","10","3","150","2"
"561","192","7","30","6"
"620","231","8","125","10"
"560","191","2","80","10"
"549","211","10","50","2"
"550","212","7","30","7"
"551","213","1","50","10"
"640","251","7","30","1"
"641","251","8","125","1"
"580","231","8","111","1"
"581","231","7","30","2"

Why would I want to build a Single Page Application in APEX?

https://blog.foex.at/an-introduction-to-single-page-application-development-in-oracle-apex/

APEX has many unique characteristics that you won’t find in any other framework. 6 characteristics sets it apart from every other development framework :

1. it runs within the Oracle DB, it's at the same level of your data negating need for a middle tier (Java need not apply). It's perfect for data centric apps. You have full access to all DB capability, and as of 18c Oracle DB packs a very long comprehensive set of features.

2. it's meta data driven. Using SQL you can query everything that is designed on your page, or in your app, and you can do this at the point of page rendering or in every AJAX callback. You can even generate apps/pages/regions etc. using the same API's the APEX builder uses to create your apps. The power and solution capability this gives you as a developer is not widely publicised and in our opinion is really underrated.

3. is serverside generation, you can programatically output HTML, CSS and Javascript on the fly. This can be on page render or for AJAX callbacks. The benefits of server generated Javascript is not widely publicised either. Again it can reduce your client side foot print drastically as well as reducing client side coding complexity.

4. we can build visual appealing applications really fast with APEX, and they are super easy to deploy and maintain.Thanks to the universal theme and Theme Roller you can achieve a modern look and feel whilst adhering to corporate colour conventions. APEX has an extensive and mature PLSQL API, and also built-in REST support thanks to ORDS (Oracle REST Data Services).

5. low code design makes it easy for beginners to become productive, whilst it provides full control for experts.

6. licensing cost, if you don't have a data foot print above 12GB you can use 18c XE, ORDS, and APEX for free. You can also deploy this on cloud providers that can charge as little as $5 a month for hosting. So for people not using Oracle yet there is no entry barrier for adopting APEX. You could even use REST to access data in remote databases if your data requirement exceeds 12G. There are solutions to keeping costs to a minimum for small businesses.

Lastly there's also productivity apps that come with it like Quick SQL which is a markdown editor that can quickly build your data model and also generate a dummy dataset. You can also quickly build prototypes using APEX, comparable in time to using a wireframe tool. The difference being you have a functional application to preview, making the choice to use APEX that much easier.

Currently the majority of APEX plugin developers are using it for single functionality use cases like nested reporting, multiple file upload, new LOV items... but you can take this a step further and plug-in an existing SPA Javascript framework and provide a reusable declarative set of regions, items, processes, and dynamic actions that are designed to work together to form a single page application design approach to building your application. This is what is FOEX Plugin Framework for Oracle APEX.

Help region

for c1 in 
(
    select page_title, help_text 
    from apex_application_pages
    where page_id = :P10061_PAGE_ID 
       and application_id = :APP_ID
)
loop
    if c1.help_text is null then
        sys.htp.p('No help is available for this page.');
    else
        if substr(c1.help_text, 1, 3) != '<p>' then
            sys.htp.p('<p>');
        end if;

        sys.htp.p(apex_application.do_substitutions(c1.help_text));

        if substr(trim(c1.help_text), -4) != '</p>' then
            sys.htp.p('</p>');
        end if;
    end if;
end loop;

Top

MD to HTML converters on inet

1. https://www.tutorialspoint.com/online_markdown_editor.php or https://markdowntohtml.com/
2. or https://www.browserling.com/tools/markdown-to-html (many converters)
3. or files convert : https://products.aspose.app/pdf/conversion/md-to-html to many formats
4. Links not working : http://demo.showdownjs.com/ (no HTML source)
   NOT WORKING : https://daringfireball.net/projects/markdown/dingus or https://pandoc.org/try/

https://www.w3schools.com/html/html_symbols.asp

https://www.degraeve.com/reference/specialcharacters.php

Top.....Apexws_cloud.....App. builder.....Page.....Environm .....URL.....Sales.....SHARED C.......Rep.List.....Ord.WizList.....Top. Navig.....LOVs....IMGs .....Home p......Buttons.....PgStyles.....Cust.....Prod.....Order.....Graph. & Mobile.....Adv. Rep.....Authoriz.....Search Style Cal...... Deploy