Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Wednesday, September 21, 2011

11/23 FORMS - Trigger (PRE-QUERY), Set dynamically properties

Introduction

A forms trigger is a block of PL/SQL code that adds functionality to your application. Triggers are attached to objects in your application. When a trigger is fired, it executes the code it contains. Each trigger’s name defines what event will fire it; for instance, a WHEN-BUTTON-PRESSED trigger executes its code each time you click on the button to which the trigger is attached. Or, we can say, a forms trigger is a set of PL/SQL actions that happen each time an event such as when-checkbox-changed, when-button-pressed, or when-new-record-instance occurs. You can attach several triggers to a data query. The most popular of them are the PRE-QUERY and POST-QUERY.

PRE-QUERY trigger

The PRE-QUERY trigger fires before the select statement is finalized. The POST-QUERY trigger fires before selected records are presented to the user. It fires after records are retrieved but before they are displayed. So, you can use it to enhance a query’s records in a number of ways. Your Post-Query trigger can contain code to calculate or populate control items.

Change an object property dynamically

You can also change an object property dynamically. The “SET_objectname_PROPERTY” built-in subprogram will change an object property dynamically.

Hands-On

On the "Orders" tab, your client wants to see the most recent or last customer orders. They ask you to sort the customer orders list based on their order’s date. They like to have two radio buttons to allow them to manipulate the order of displayed records by order date.

You have been assigned to create two radio buttons, Labeled "Ascending" and

"Descending" to sort displayed record order by "order date."

See Figure 11.

Your tasks are:

1- Create a Radio Group.

2- Create two Radio Buttons associated with step 1 (Radio Group).

3- Label the first Radio Button “Ascending.”

4- Label the second Radio Button “Descending.”

5- Run and test all user functional requirements.

You will learn how to: create and use PRE-QUERY trigger, set dynamically block property on a table's data block and execute the query using trigger, use tab canvas, use “object navigator,” use “Data Blocks,” use “Layout Editor,” use “Property Palette,” use “Run Form,” and “Execute Query.”

Figure 11

Open a Module

In the ‘Object Navigator’ window, highlight Forms. Go to the Main menu and choose “File,” select “Open” to open an existing form (customer_orders_V08)

from the “iself” folder.

Save a Module

Click on the “CUSTOMER_ORDERS_V08” form. The color will change to blue. Now, you should be able to change the name and then save the Form name as version 09 (customer_orders_v09). This way the original form is untouched.

Open Layout Editor

In the Main menu, choose the ‘Tools’ sub-menu and select the ‘Layout Editor’ option. In the Layout Editor window, make sure that the Canvas box contains your tab canvas. If not, click on the down arrow next to its box and select it and also make sure in the Layout Editor window, the BLOCK box contains the ORD block.

Create a Control Data Block

In the Object Navigator window, highlight the Data Blocks item and click on the green ‘+’ sign to create a data block. In the ‘New Data Block’ window, select the ‘Build a new data block manually’ option and then click ‘OK.’

Change a property palette sheets

Now an object was created (BLOCKnn). Right click on the object and open its Property Palette. Change "name" to sort_block. And press the enter key. Change "Database Data Block" to "no." Close the window.

Create a Radio Group and Radio Button

In the Layout Editor, on the “BLOCK” box, select the sort_block item and click on the Radio Button icon to create a radio button. Drag the ‘+’ sign on the canvas and click where you wish to have your Radio Button. In the ‘Radio Group’ window, click “new” to create a new “Radio Group” for sorting.

Notice on the "Object Navigator," a radio group and radio button were created.

In the Object Navigator, right click on the new RADIO_GROUPnn item and open its Property Palette.

In its Property Palette, change the "name" property to SORT_SELECT or any name you like and then press the enter key. Change "initial Value" to "orderdate asc." Then press the enter key and click “OK.” Change "Database Item" to "no." Close the window.

Right click on the RADIO_BUTTONnn item and open its property palette. Change the "name" property to RB_SORT_ASC. The RB stands for Radio Button. Change "radio button value" to "orderdate asc." Change "Label" to "Ascending." Close the window.

Copy and Paste a Radio Button

Copy and paste the Radio Button item. Make sure to place it in the “sort_select” Radio Group. Then click “OK.”

Move an object

Move the new "radio button" item similar to user requirement with above figure.

Right click on it and open its Property Palette.

Change a property palette sheets

In the Property Palette window, change the NAME to RB_SORT_DESC. Change the "label’ to Descending. Change "Radio Button Value" to "orderdate desc." Then close the window.

Change an appearance of an object

Click on the "Text" icon; and type 'Sort Order Date.' Then click outside of the text box.

In the Layout Editor, you can change the appearance of the text by clicking on the B, I, U icons on the top horizontal toolbar.

Create a Trigger

In the Object Navigator, highlight the Trigger of the ‘SORT_SELECT’ radio group and click on the green ‘+’ sign to create a trigger.

WHEN-RADIO-CHANGED’ trigger

In the Triggers window, type a letter ‘W’ then ‘R.’ Select the ‘WHEN-RADIO-CHANGED’ trigger and click ‘OK.’

PL/SQL Editor

In the PL/SQL Editor, write a PL/SQL procedure to go to the order block (ORD) and then execute the query statement.

(PL/SQL Editor)

GO_BLOCK(ord);

EXECUTE_QUERY;

Compile a trigger

Compile the trigger and close the window.

Create a trigger

In the Object Navigator, highlight the Trigger of the ‘ORD’ data blocks and click on the green ‘+’ sign to create a trigger

PRE-QUERY trigger

In the Triggers window, type a letter ‘P’ then ‘Q.’ Select the ‘PRE-QUERY’ trigger and click ‘OK.’

PL/SQL Editor

In the PL/SQL Editor, write a PL/SQL procedure to change the “order_by” property of the “ORD” data block. Notice that the “sort_select” value is changed based on the radio button you select.

(PL/SQL Editor)

SET_BLOCK_PROPERTY (‘ord’, ORDER_BY, :sort_block.sort_select);

Compile Trigger

Compile the trigger. Close the window.

Run the Form

Run the application.

Execute Query

Click “Execute query.”

Navigate

Navigate through the application and check the sort radio button.

After the testing is done, close the window and save the changes.

Questions:

Q: Describe the PRE-QUERY trigger.

Q: How do you set a block property dynamically?

Q: Can you attach several triggers to an object in the Form Module?

Q: On the "Orders" tab, your client wants to see the most recent or last customer orders. They ask you to sort the customer orders list based on their order’s date. They like to have two radio buttons to allow them to manipulate the order of displayed records by order date.

You have been assigned to create two radio buttons, Labeled "Ascending" and

"Descending" to sort displayed record order by "order date."

See Figure 11.

Your tasks are:

1- Create a Radio Group.

2- Create two Radio Buttons associated with step 1 (Radio Group).

3- Label the first Radio Button “Ascending.”

4- Label the second Radio Button “Descending.”

5- Run and test all user functional requirements.

No comments:

Post a Comment

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect