Create a block corresponding to the S_CUSTOMER table. It will be a master block and the detail block will be associated with the S_ORDER table since the S_ORDER table has a foreign key that references S_CUSTOMER.

Before we proceed: You should now have three data blocks and two relations items in your forms file. Please save the file and proceed. The Object Navigator looks something like this at this stage.

Saving data in a table

Next, we will create  a button which will allow us to save the data entered in the form in the respective table (S_ORD). The resulting form will have minimal functionality. We can further enhance the form by customizing it.

Placing a button on the canvas. Click the "Push Button" icon on the canvas toolbar (shown below) and then click once on the canvas.
 
 A new push button with default properties will be created. Forms builder will give it a default name like "PUSH_BUTTON14". You can drag and place the button any where on the canvas.
Note: The Push Button is automatically placed in the same data block (S_ORD) as the data block on the current canvas.

On the canvas double click on the Push button to open its PROPERTY PALETTE. In the value for the "LABEL" field enter "SUBMIT" or "SAVE" to indicate the function of the button.

6(a).3 Try running the form. Click here for instructions for running a form. Try clicking on the Push Button - nothing happens. This is because we still have not told Forms builder what to do once the button is clicked. This is done entering PL/SQL code in a trigger. Please refer to the lab manual for more information on triggers. At this moment it is enough for you to know that triggers are blocks of code that are executed when a particular event occurs. In this case we need certain code to be executed when the "Submit" button is pressed. So we use what is called a WHEN-BUTTON-PRESSED trigger ! Here is how the form would look:

Note: The green back ground is the default color for the form. This can be easily changed using the canvas toolbar, though at the moment we will not bother ourselves with such cosmetic details.

Create a WHEN-PRESSED-BUTTON Trigger

On the ORDER_CANVAS right click on the "Submit" Button. Select "PL/ SQL Editor" from the menu. This will bring up a list of triggers that can be associated with the push button. Select "When-Button-Pressed" from this list.

Once you select the trigger forms builder will open a PL/SQL Editor window. This is where you input PL/SQL code that has to be executed when the "Submit" button is pressed. For saving information through a form we use the following line of code:
commit_form;

Compile the PL/SQL code by clicking on the first button on the toolbar for the PL/SQL Editor.
 
Note: You should get a "Not Modified - Successfully Compiled" message as shown in the picture above. Any errors generated during the compilation process will be shown in the bottom part of the editor window.

Close the PL/SQL Editor window and run the form.

Since CUSTOMER_ID and SALE_REP_ID must refer to actual customers and sales representatives, we will enter these values after looking up values in the databases from S_CUSTOMER and S_EMP tables.

Enter data as shown in the picture below and click on the "Submit" button.

Note: You will get the following message at the bottom of the applet window:

Create a Control Block and a Non Base Table Item

So far we selected a database table to create a data block by selecting a particular table in the database schema and then selected the columns from that table to be ITEMS in the data block. Later using the LAYOUT WIZARD we placed these items on the ORDER_CANVAS. These items are called "Database Table Items".
We might need to have other items on the canvas to make the form more user friendly. Suppose we wanted to display customers and sale representatives information in an order form and these items are not included in S_ORD. They are called "NON Database Table Items". Forms developer considers all new items to be "Database Table Items" (default). This can be changed by opening the PROPERTY PALETTE of a item and changing the "Database Item" field to "NO".
Instead of changing the property of every item added to the form you can simply create a "dummy data block" or CONTROL BLOCK and add these items like text boxes, buttons etc. to this data block.

Select "Data Block" in the OBJECT NAVIGATOR and press the "+" button to add a new data block.

Select "Build Data Block Manually" in the next dialogue box and click "OK". You can now see a new data block with no items. Rename the Data block to "CTRL_S_ORD" - indicating that it is a control block for the S_ORD data block.

To add a new item to the CTRL_S_ORD block select ITEMS node and press the "+" sign. This will create a new text item with a default name. Rename the item as "CUST_NAME" - we will use this text item in the next stage of the tutorial (Dynamic Lists : LOV's).

Repeat to create two more text items : CUST_PHONE and SR_NAME (Sales Rep Name).

Repeat to create two more text items : SR_BUTTON and CUST_BUTTON. Open the PROPERTY PALETTE for these two items and change the value of the "Item Type" field to "Push Button". Also change the labels of these items to "Select Sales Rep" and "Select Customer".
The OBJECT NAVIGATOR looks like this:

For the next part of the tutorial we need to place these items on the ORDER_CANVAS. This can be done by simply opening the PROPERTY PALETTE for each of these items and setting the value of the "CANVAS" field to "ORDER_CANVAS".

Rearrange the items on the canvas as shown :

Create Dynamic Lists: List of Values (LOVs)

There are two types of lists we can use : Static Lists and Dynamic lists. Static Lists are lists of item that do not change frequently - these are implemented using simple drop down boxes (ask your GA to show you how to create a Static List). Typically a static list would be a list of all States in the US, or list of semesters for the next couple of years etc. Dynamic lists on the other hand are based on data in the database tables. The data is frequently changing and the most up-to-date data has to be presented to the user every time he / she asks to see the dynamic list. For e.g. a list of items in an warehouse that has gone below its re-order level. These dynamic lists are based on SQL queries and show the results of the query every time the user calls the dynamic list. We shall start with creating a dynamic list (called LOV / List of Values) for all the customers in the database - this data is frequently changing as new customers are added and old customers are deleted.

Stage 1 Create LOV Using SELECT Statement

Select "LOVs" in the OBJECT NAVIGATOR and click on the "+". Choose to build a LOV using the LOV wizard.

Select "New Record Group based on a query" and click "NEXT". The next screen will prompt you to enter the query on which this LOV is based.

Enter the query shown in the picture below and press "Check Syntax" to ensure that the query is correct. Click "OK" and then "Next".

select id, name, phone from s_customer order by name

Note: Though all SQL statements end with a semi-colon (;) the SQL statements entered in the LOV wizard should NOT be terminated with a semi-colon (;).

In the next step Click on the ">>" to select all items as LOV items. Click "Next" and you will see the following screen.

Click on the "Return Value" column for the ID field and press the "Look up return item" button you will see the following dialogue box.

Select S_ORD.CUSTOMER_ID as the return item. Repeat step 6(d).5 to specify return items for NAME and PHONE as CTRL_BLK.CUST_NAME and CTRL_BLK.CUST_PHONE respectively. Click "NEXT" when done.
Note: Specifying Return Values for LOV items is a very important step. Ask your GA to explain what a return value does. Try the exercise below to find what Return Values do.

After we are done with this part of the tutorial (creating dynamic lists) open the LOV wizard in re-entrant mode by right clicking the LOV and selecting "LOV Wizard". Delete the return item specified for CTRL_BLK.CUST_PHONE. Click finish and run the form - observe what happens to the customer phone field.

Answer: Repeat the entire procedure to create a LOV to select Sales Representative Name and display his / her ID and name on the form. Hint : Use the following SQL code to create the LOV:  select id, last_name from s_emp order by last_name

Specify a appropriate title for the LOV: "Select Customer name". This appears on top of the LOV. Retain other default values - these can be changed later.

Click "Next" till you reach the end of the wizard. We will retain all default values. Click "Finish" on the last screen - a new LOV with a default name will be created in the OBJECT NAVIGATOR. Rename the LOV as "CUST_LIST".
Note: Creating a LOV is the first stage of creating a dynamic lists. Next we have to insert code to call the LOV.

Stage 2 Call LOV Using WHEN-BUTTON-PRESSED Trigger

Open ORDER_CANVAS and right click on the "Select Customer" button. Choose "PL/SQL Editor" from the resulting menu.

Choose WHEN-BUTTON-PRESSED trigger from the list and insert the following code in the PL/SQL editor:
declare
lov_return boolean;
begin
lov_return := show_lov('cust_list');
end;
NOTE: cust_list is the name of the LOV.

Compile the code and close the PL/SQL editor window. Run the form to see the LOV at work.
Note: You may need to open the LOV wizard again to modify the visual attributes of the LOV.

Before we proceed: We frequently need to refer to data in a particular text item. The syntax for referring to a particular item is as follows:
:block_name.item_name
Thus the item "TOTAL" under the S_ORD block is shall be referred to as:
:s_ord.total

Auto Generate Primary Key

Stage 1 Create a Sequence

Open the SQL*Plus window and enter the login and account information.

Once you have logged in enter the following command:
create sequence ord_pk start with 1 increment by 1;

Stage 2 Call a Sequence

Note: We need to use a few lines of code in order to call the sequence and tell it to insert the new sequence value. Before we do that we have to decide when this code should be executed? We want to make sure that a new primary key is generated only when the customer has submitted the final order. This can be done either by incorporating the code into the WHEN-BUTTON-PRESSED trigger of the "Submit" button or by using a BLOCK-LEVEL-TRIGGER called PRE-INSERT trigger. This trigger is fired just before forms developer saves data to the table. Since we have used the WHEN-BUTTON-PRESSED trigger before lets experiment with the PRE-INSERT trigger.

In the OBJECT NAVIGATOR expand the S_ORD data block. You will find the "Triggers" node right under the data block name. Select the node and click "+" to add a trigger.

Select PRE-INSERT from the pop-up list and click OK. The PL/SQL editor for the trigger will open up. Use the following code :
select ord_pk.nextval into :s_ord.id from dual;
The OBJECT NAVIGATOR for the S_ORD block looks something like this:

Note:  Dual is a dummy table used to complete the "From" part of a SQL statement. The required property for the id text field must be changed to "No." If this field is not changed the form will force the user to enter a value for the id field and the field will not populate.

Compile the code and close the editor window. Run the form to see how auto-generated primary key works.
Note: In order for the id field to populate correctly an existing customer id and sales rep id must be entered in their respective fields.

The value in Shipping cost will be added to the total cost of the order and the sum will be displayed in the GrandTotal Field. Create the Shipping and GrandTotal text fields as we have done before, place it on ORDER_CANVAS and proceed to Step 1.

Calculated Values

We use program units when we want to execute pieces of PL/SQL code. S_ORD has an field TOTAL. We refer its as :              :S_ORD.TOTAL

We will need to add two text items called: SHIPPING and GRANDTOTAL to the control block CTRL_BLK. The value of SHIPPING will be entered by a user and GRANDTOTAL will be calculated.

Stage 1 Create a Program Unit

Open the OBJECT NAVIGATOR and select the PROGRAM UNIT node and click "+" to add a program unit. You will see the following dialogue box. Enter the name shown in the picture and click OK:

A PL/SQL Window will automatically open up with the following code in it :
PROCEDURE calc_order_details IS
BEGIN

END;

Enter the code given below.
PROCEDURE calc_order_details IS
BEGIN
 :ctrl_s_ord.grandtotal := :ctrl_s_ord.shipping + :s_ord.total;
END;

Compile the code and close the PL/SQL Editor Window.

Stage 2 Call the Program Unit

Right click on the SHIPPING text item from the ctrl_s_ord block (referred to as :ctrl_s_ord.total) and click on the PL/SQL editor to bring up the list of possible triggers.

Select a WHEN-VALIDATE-ITEM trigger and enter the following code :
calc_order_details;
This calls the program unit and executes the PL/SQL code in it.

What is validation ? When is the WHEN-VALIDATE-ITEM trigger fired?

Answer: Validation is a process through which forms builder checks the whether the data being entered in a text box matches with the data type defined for that item. Every time the validation process takes place the WHEN-VALIDATE-ITEM trigger is fired.

You may want to disable the total cost (:s_ord.total), and Grand Total (:s_ord.grandtotal) so that the user cannot modify the values in these fields. This can be done by opening the PROPERTY PALETTE of the items and turning the value of the ENABLED field to NO.

Create Alert

Alerts are simple mechanisms to require user confirm the requested action. Alerts are used to avoid mistakes made by the user. For e.g. when you ask for a document to be deleted, the windows operating system asks if you are sure - this is a alert that requires the user to confirm his action. We shall see how alerts can be incorporated into forms. Let us add a alert to the form that asks the user to confirm his request to submit an order. (A alert has to show up after the user has pressed the "Submit" button.)

Stage 1 Create an Alert

Select "Alerts" in the OBJECT NAVIGATOR and click on the "+". A new alert with a default name will be created. Change the default name to "SAVE_ALERT".

Double click on the icon to open the PROPERTY PALETTE for the alert. Change the field values as shown in the picture below. 

Note: Your alert is now ready to be used. The next stage is to call the alert using a PL/SQL code.

Stage 2 Call an Alert

Since we want the alert to be called when the user presses the "Submit" button we have to include code for the Alert in the WHEN-BUTTON-PRESSED trigger.

Open the PL/SQL editor for the WHEN-BUTTON-PRESSED trigger associated with the Submit button. This can be done by opening ORDER_CANVAS and right clicking on the Submit button. Select "PL/SQL Editor" from the menu.

We already have the following code in this trigger:
commit_form;

We will modify the code in this trigger as shown below:
declare
alert_id alert;
choice number;
BEGIN
alert_id := Find_Alert ('SAVE_ALERT');
choice := Show_ALert(alert_id);
IF choice = ALERT_BUTTON1 THEN
commit_form;
ELSE null;
END IF;
END;

Note: Please note the the commit_form; code has now been placed INSIDE the If-THEN-ELSE LOOP.

Compile the code and close the PL/SQL editor window. Your Alert has now ready to be used. Run the form and see how the alert works. Click here for instructions for running a form. This is how a alert looks after the Submit button has been pressed.

Before we proceed: We currently have just one Data Block (S_ORD) on the ORDER_CANVAS. The form as a whole is not yet complete , we will add S_ITEM to the same canvas.

Create Multiple Data Blocks on the Same Canvas

In the OBJECT NAVIGATOR right click on the S_ITEM data block and click on LAYOUT WIZARD. This will bring up the Welcome Screen. Click NEXT to proceed

Since we want the data block on the same canvas (ORDER_CANVAS) select that canvas from the Drop down list and click NEXT.

You will see all the items in the data block S_ITEM under the "Available Items" column. Select all items EXCEPT QUANTITY_SHIPPED to the "Displayed Items" column. Click NEXT once the dialogue box looks like this :
 
Note:

We are not including QUANTITY_SHIPPED as it is currently out of the scope of the tutorial. It involves working with the S_INVENTORY table which we will not be using for the purpose of this tutorial.

Select "TABULAR" as the layout style and Click NEXT
Note: It would be interesting to note that the tabular layout allows a better organization of multiple items. This is in line with the ER-Diagram where we have a One-Many relationship between S_ORD and S_ITEM tables.

Input "Items for the order" in the FRAME TITLE field. Also choose to display 7 items with a distance of 3 between each record. Click NEXT when the dialogue box looks like this :
 

Expand the S_ORD data block in the object navigator. Expand the TRIGGERS node under the S_ORD data block and double click on the icon for the PRE-INSERT Trigger. This opens the PL/SQL editor with a line of code we have enter previously:
select ord_pk.nextval into :s_ord.id from dual

Add the following line AFTER the above line : :s_item.ord_id := :s_ord.id;
Note: The second line of code (STEP 1.9) will copy the auto generated key in :s_ord.id to :s_item.ord.id.

Run the form to see the results of your work. Click here for instructions for running a form. Enter the data shown in the picture below and submit the order:
 

Start with TUT4.fmb 

• Disable user data entry into specific fields, e.g., GRANDTOTAL in CTRL_BLK and ID in the S_ORD block.
• Customize the Sales Rep LOV. Create an item COMMISION_PCT in CTRL_BLK and add it to the canvas. The user will enter a value here. Modify the LOV query that selects values from the S_EMP table to only select sales representatives whose commission is below the user specified input. You will also need to add this item COMMISSION_PCT (from the S_EMP table) to LOV Columns so that it is displayed when you run the LOV.
• Create a new canvas “Customer Maintenance” to display and modify Customer information. Create a new canvas and use the layout wizard to attach the S_CUSTOMER block to the new canvas.  Use the GO_BLOCK command to switch from one canvas to the other. The canvas will not be populated unless you use the EXECUTE_QUERY macro. You will need to create a PRE_QUERY trigger in the S_CUSTOMER block that copies the customer id from the S_ORD block (:S_ORD.CUSTOMER_ID) to the ID field in S_CUSTOMER block (:S_CUSTOMER.ID). You may need to delete the Master Detail relationship between the S_ORD and S_CUSTOMER blocks to get this to work correctly.
• Display items belonging to an order.  Recall that there is a master detail relationship between the S_ORD and S_ITEM block.  Display the S_ITEM block on the same canvas as the S_ORD block. As you scroll through each order, the items in the order will be displayed.
• Create a new item for a new order.  To make sure that the order id from S_ORD is entered into the ORD_ID field in the S_ITEM block you can use the property palette “Copy Value from Item” function or you can use a PRE_INSERT or PRE_QUERY trigger. The user has to manually enter a new item 1, 2, 3, etc. or you can use a program unit to calculate this value. Please note that the TOTAL is not calculated automatically!

Triggers

Triggers are PL/SQL program units that are executed when a certain event occurs. Oracle recognizes the occurrence of an event, typically INSERT, DELETE, BUTTON PRESSED, CURSOR IN AN TEXT ITEM etc., and executes the program unit.

Oracle classifies triggers in the following categories (HELP gives a lot of details! ): ):

• Block-processing triggers
  Triggers that fire in response to record management events at block level. 
• Interface event triggers
  Triggers that fire in response to events that occur in the form interface 
• Master-detail triggers
  Triggers that enforce co-ordination between detail block and master block
• Message-handling triggers
  Triggers that fire in response to error / informational messages generated by Form builder.
• Navigational triggers
  Triggers that fire when the user moves between forms / items on a canvas. 
• Query-time triggers
  Triggers that fire when a query is executed in a block
  
• Transactional triggers
  Triggers that fire when the form interacts with the database. 
• Validation triggers
  Validation is a process where Oracle ensures that the data / record being inputted is in conformance with the details in DDL statements. (Such as validating whether an input for a text item is an INTEGER) Validation Triggers are triggers that are fired during the validation process for item or record.

Someome< of the triggers used in sample.fmb (and ones that are used frequently) are discussed below. Typically these are the triggers where you will provide the PL/SQL code:
1.     ON-CLEAR-DETAILS: S:
Definition Level  form, block
Type: Master – Detail Trigger
Action: Used for coordination between Master / Block when form builder tries to delete a record from a block which is a DETAIL block in a master-detail relationship.
Action in Sample.fmb: Fires stored procedure (procedure provided by Oracle) called CLEAR_ALL_MASTER_DETAILS.