How to enable Record History for a custom form

How to enable Record History for a custom form

 In this article I shall illustrate the process of enabling record history on a custom form. By checking record history of a record on a form we can identify which user created and modified the record. We also get to know the underlying database table/view where the record is being pulled from.

Open a custom form. Click on Help > Record History

You get a popup message saying that Record History is not available here. This is because the WHO columns are not set on the custom form.

Now we shall follow the steps to add the WHO columns into the table and modify the custom form accordingly

   
Step 1: Describe the custom table

Check the table on which the form is built by describing the table, XX_BLACKLIST_SUPPLIER.

The script is the following.

CREATE TABLE xx_supplier_blacklist

(

   blacklist_id           NUMBER NOT NULL PRIMARY KEY,

   supp_number            VARCHAR2 (30),

   supp_name              VARCHAR2 (240),

   address                VARCHAR2 (1000),

   LOCATION               VARCHAR2 (2),

   supp_lob               VARCHAR2 (240),

   blklist_flag           VARCHAR2 (3),

   reason_blklist         VARCHAR2 (240),

   reason_details         VARCHAR2 (1000),

   date_blklist           DATE,

   date_remove            DATE,

   reason_blk_remove      VARCHAR2 (240),

   detail_reason_remove   VARCHAR2 (1000),

   linkage                VARCHAR2 (3),

   attribute_category     VARCHAR2 (150),

   attribute1             VARCHAR2 (240),

   attribute2             VARCHAR2 (240),

   attribute3             VARCHAR2 (240),

   attribute4             VARCHAR2 (240),

   attribute5             VARCHAR2 (240)

)

The script is the following  in this table. You need to add the columns

     

Column Name	Data Type
CREATION_DATE	DATE
CREATED_BY	NUMBER(15)
LAST_UPDATE_DATE	DATE
LAST_UPDATED_BY	NUMBER(15)
LAST_UPDATE_LOGIN	NUMBER(15)

  
Step 2: Alter the custom table to add the WHO columns

ALTER TABLE xx_supplier_blacklist ADD creation_date DATE;

ALTER TABLE xx_supplier_blacklist ADD created_by NUMBER (15);

ALTER TABLE xx_supplier_blacklist ADD last_update_date DATE;

ALTER TABLE xx_supplier_blacklist ADD last_updated_by NUMBER(15);

ALTER TABLE xx_supplier_blacklist ADD last_update_login NUMBER (15);

Step 3: Open the custom form

The custom form file name is XX_NEW_FORM.fmb. 

We shall open the form in Forms Builder.

This form was developed earlier and you can find the development steps in this blog.

Step 4: Add the newly added WHO columns on the data block

You can manually add the WHO columns on the data block or you can use the Data Block wizard to do so. I have used the wizard for this purpose as it is fast.

Right click on the data block and select Data Block Wizard.

Click on Next button.

Click on Refresh button to get the newly added WHO columns on the list.

Click on the select all columns button (>>) to add the columns into the data block.

Click on Next

Click on Finish button. Now the WHO columns have been added into the data block but not on the canvas. So these fields will not be visible to the user and this is the required functionality in any Oracle Apps form.

   
Step 5: Add a PRE-INSERT trigger on the data blocks

Check the block XX_SUPPLIER_BLACKLIST as this is the main data block.

This block does not have a PRE-INSERT trigger. Let us add it.

In the same way add the PRE-UPDATE trigger.

After the triggers are added the data block will look like this,

In both the triggers add the following line,

1	FND_STANDARD.SET_WHO;

This seeded package will add the values for the WHO columns.

Important:
When you invoke the Data block Wizard, add the columns and let the wizard update the data block, some of the field properties might be reset as per the default values. For instance, if a form item refers to a primary field and it is set as non updatable or non insertable in the block, then after invoking the wizard will cause the form builder to reset these properties to updatable and insertable for this item.

  
Step 6: Compile the form

Compile the form(read in my Blogger)

   
Test the form

Open the form

Now create a new record or update an existing record. For our test we shall update an existing record.

First let us query the form for an existing record.

Add a value to the fields

Now save the form. Once the form is saved click on Help > Record History

Now you get to see the record history for the current record on the form.