Pre-generation editing

After reading this article, we will understand our project a lot better. Also, to a certain level, we will be able control the way our application will be generated. Generation is often performed more than once as you refine the definitions and settings between iterations.

In this article we will learn a lot of ways to edit the project in order to generate optimally. But we must understand that we will not cover all the exceptions in the generation process. If we want to do a real Forms to APEX conversion project, it will be very wise to carefully read the help texts in the Migration Documentation provided by Oracle in every APEX instance—especially the appendix called Oracle Forms Generation Capabilities and Workarounds, which will help you to understand the choices that can be made in the generation process. The information in these migration help texts tells us how the different components in Oracle Forms will be converted in APEX and how to implement business logic in the APEX application. For example, when we take a look at the Block to Page Region Mappings, we learn how APEX converts certain blocks to APEX regions during conversion.

Investigating

When we take a look at our conversion project, we must understand what will be generated. In case of generation, the most important parts are the blocks on our Forms modules. These are, quite literally, the building blocks our pages in APEX will be based upon. Of course, we have our program units, triggers, and much more; but the pages that are defined in the APEX application (which we put in production after the project is finished) will be based on Blocks, Reports, and Menus. This is why we need to adjust them before we generate anything. This might seem like a small part of the project as we look at the count of all the components in our project page, but that doesn’t make it less important.

We can’t adjust reports as they are defined by the query that they are built upon, but we can alter the blocks. That’s why we focus on those components first.

Data blocks

The building blocks of our APEX pages are the blocks and, of course, the reports. The blocks we can generate in our project are the ones that are based on database block. Non-database blocks such as those that hold menus and buttons are not generated by default, as they will be generated as blank pages. In the block overview page, we get the basic information about the blocks in our project. The way the blocks will be generated is determined by APEX based on the contents, the number of items on the block, and, most importantly, the number of records displayed. For further details on the generation rules, refer to the Migration Guide—Appendix A: Forms Generation Capabilities and Workarounds.

In the Blocks overview page in our conversion project, we notice that not all the blocks are included. In other words, they aren’t checked to be included in the project. This is because they are not oriented from a database block. To include or exclude a block during generation, we need to check or uncheck the specific block. Don’t confuse this with the applicability of a block. We also might notice that some of the blocks are already set to complete. In our example we see that the S_CUSTOMER1 and S_CUSTOMER blocks are set to complete. If we take a look inside these components and check the annotations, they are indeed set to complete. There’s also a note set for us. As we see in the following screenshot, it states Incorporating Enhanced Query:

The Enhanced Query is something that we will use later in this article. But beware of the statement that a component is Complete as we will see that we might want to alter the query on which the customer’s block is based.

If we look at a block that is not yet set to complete in the overview page (such as the Orders block) and we look at the Application Express Page Query region in the details screen, we see that only Original Query is present. This is the query that is in the original Forms XML file we uploaded earlier.

Although we have the Original Query present in our page, we can also alter it and customize the query on which this block is based. But this will be done later in the article. In this way, we have a better control over the way we will generate our application. We can’t alter this query as it is to be implemented as a Master-Detail Form.

Block items

Each block contains a number of items. These items define the fields in our application and are derived from our Forms XML files. In the block details pages, we can find the details of the items on the particular block as well. Here we can see the most basic information about the items, namely their Type, Prompt, Column Name, and the Triggers on that particular item. We can also see the Name of the item if it is a Database Item and if the item is complete or not, and whether or not it is Applicable. When a block is set to complete, it is assumed that we have all the information required about the items, as we see in the example shown here:

But there are also cases where we don’t get all the information about the items we want. In our case, we might want to customize the query the block is based on or define the items further. We will cover this later in the article.

In the above screenshot we notice that for all the items the Column Name is not known. This is an indication that the items will not be generated properly and we need to take a further look into the query and, maybe, some of the triggers.

When we want to alter the completeness and applicability of the items in our block, there’s a great functionality available on the upper-right of the Blocks Details page. In the Block Tasks section, we find a link that states: Set All Block Items Completeness and Applicability. This function is used to make bulk changes in the items in the block we are in. It can be useful to change the completeness of all items when we are not sure what more needs to be done.

To set the completeness or the applicability with a bulk change on all the items, we click on the link in the Block Tasks region and this takes us to the following screen:

In the Set Block Item & Trigger Status page we can select the Attribute (Items, Block Triggers, or Item Triggers), the Set Tracking Attribute (Complete or Applicable), and the Set Value (Yes or No). To make changes, set the correct attribute, tracking attribute, and value, and then click on Apply Changes.

Original versus Enhanced Query

As mentioned earlier, we can encounter both Original and Enhanced Queries in the blocks of our Forms. The Original Query is taken from the XML file directly as it is stated in the source of the block we are looking at. So where does the Enhanced Query originate from? This is one of the automatically generated parts of the Forms Conversion tool in APEX. If a block contains a POST QUERY trigger, the Forms Conversion tool generates an Enhanced Query for us.

In the following screenshot, we see both the Enhanced Query and the Original Query in the S_CUSTOMER block. We can clearly notice the additional lines at the bottom of the Enhanced Query.

The query in the Enhanced Query section still looks a lot like the one in the Original Query section, but is slightly altered. The code is generated automatically by taking the code from both the Original Query and POST QUERY triggers on this block. Please note that the query is automatically generated by APEX by adding a WHERE clause to the SQL query. This means that we will still need to check it and, probably, optimize it to work properly.

The following screenshot shows us the POST QUERY trigger. Notice that it’s set to both applicable and complete. This is because the code is now embedded in the enhanced query and so the trigger is taken care of for our project.

Triggers

Besides items, even blocks contain triggers. These define the actions in our blocks and are, therefore, equally important. Most of the triggers are very Forms-specific, but it’s nice to be the judge of that ourselves.

In the Orders Block, we have the Block Triggers region that contains the triggers in our orders block. The region tells us the name, applicability, and completeness. It gives us a snippet of the code inside the trigger and tells us the level it is set to (ITEM or BLOCK).

A lot of the triggers in our project need to be implemented post-generation, which will be discussed later in this article. But as mentioned above, there is one trigger that we need in the pre-generation stage of our project. This is the POST-QUERY trigger.

In this example, the applicability in the orders block is set to No. This is also the reason why we have no Enhanced Query to choose from in this block. The reasons behind setting the trigger to not applicable can be many, and you can learn more about the reasons if you read the migration help texts carefully.

We probably want to change the applicability of the trigger ourselves because the POST-QUERY trigger contains some necessary information on how we need to define our block. If we click on the edit link (the pencil icon) for the POST-QUERY trigger, we can alter the applicability.

Set the value for Applicable to Yes and click on Apply Changes. This will take us back to the Block Details screen. In the Triggers region, we can see that the applicability of the POST QUERY trigger is now set to Yes.

Now if we scroll up to the Application Express Page Query region, we can also see that the Enhanced Query is now in place. As shown in the following screenshot, we can see that we automatically generated an extended version of the Original Query, embedding the logic in the Post Query trigger. For the developers among us, we can see that the query produced by the conversion tool in APEX doesn’t make the query very optimal. We can rewrite the query in the Custom Query section, which we will describe later in this article.

We are able to set the values for our triggers in the same way we used to set the applicability and completeness of the items in our blocks.

In the upper-right corner of our Block Details screen, we find the Block Tasks region. Here we find the link to the tasks for items as well as triggers.

Click on the Set All Block Triggers Completeness and Applicability to navigate to the screen where we can set the values. In the Attribute section, we can choose from both the block level triggers as well as the item level triggers. We can’t adjust them all at once, so we may need to adjust them twice.