Select/Unselect all check box functionality in Oracle Apps form

This Oracle form builder tutorial explains the how to achieve select/unselect all checkbox functionality in Oracle Apps.

Select all – when the master checkbox is checked then all child checkboxes on the form should be checked.
Unselect All – when the master checkbox is unchecked then all child checkboxes on the form should be unchecked.

This functionality is helpful to select and process multiple records at a time. In forms, you can use GO_BLOCK, GO_RECORD built-in and for loop to achieve this. Oracle Apps has built-in wrapper APP_RECORD.FOR_ALL_RECORDS on go_block, go_records which you can use to achieve it.

This is how it should work,

  • Uncheck Master Check Box – All child checkbox should get unchecked
  • Check Master Check Box – All child checkbox should get checked
  • Uncheck child checkbox – Master Check box should get unchecked.

select all oracle form working

Pre-requisite

This tutorial assumes basic working knowledge of Oracle Form Builder in Oracle Apps Environment.

Software/Tools/Environment

  • Oracle Developer Suite 10G
  • Oracle Apps 11.5.10 or 12.1.3

APP_RECORD.FOR_ALL_RECORDS

This call executes the specified trigger for every record of the current block or the specified block. If you specify a block, the GO_BLOCK built-in fires. When finished, the cursor returns to the original record and item.

If the trigger fails, FORM_TRIGGER_FAILURE is raised and the cursor is left in the record in which the failure occurred.You can pass arguments to the specified trigger using global variables simply by setting the global variables before calling this routine. APP_RECORD.FOR_ALL_RECORDS fires once when there are no queried records.

Syntax

procedure APP_RECORD.FOR_ALL_RECORDS(block_name varchar2, 
trigger_name varchar2);
procedure APP_RECORD.FOR_ALL_RECORDS(trigger_name varchar2);

Arguments (input)

block_name – The name of the block to navigate to
trigger_name – Name of the trigger to execute

Source: https://docs.oracle.com/cd/B40089_10/current/acrobat/120devg.pdf

Create a custom form

Create a custom form as shown below on any data source. I am using DBA_OBJECTS table to create a form.

Below is the layout of the form.

form layout

Let me show you object navigator view.

object navigator view

Code and Logic

Master Block:

It is a non-database block with a checkbox MASTER_CHECK_BOX.

Name Type Description
MASTER_CHECK_BOX CHECK BOX Non-Database Checkbox with Value Y and N.

 

Add a trigger WHEN-CHECKBOX-CHANGED trigger for MASTER_CHECK_BOX and add below code. This trigger fires when checkbox is checked or uncheck.
APP_RECORD.FOR_ALL_RECORDS(‘CHILD’,’CHILD_CBOX_HANDLE’);

Child Block

It is a database block based on DBA_OBJECTS with the below items.
Item Name Type Description CHILD_CHECK_BOX Check Box Non-Database Checkbox with Value Y and N.

Item Name Type Description
CHILD_CHECK_BOX Check Box Non-Database Checkbox with Value Y and N.
OWNER Text Item Database Item
OBJECT_NAME Text Item Database Item
SUBOBJECT_NAME Text Item Database Item
OBJECT_TYPE Text Item Database Item
STATUS Text Item Database Item

 

Note:- As this procedure works on all the items fetched on forms irrespective of the displayed item. Restrict the number of the item to 10 by setting Maximum Record Fetched to 10 at the block level. This will only fetch 10 records; else it will take a lot of time as DBA_OBJECTS may have millions of records.

Trigger:

CHILD_CBOX_HANDLE

It is a user-defined trigger at CHILD block level.

This code simply code value of MASTER_CHECK_BOX to CHILD_CHECK_BOX i.e if master checkbox is check, check all child box

:CHILD.CHILD_CHECK_BOX := :MASTER.MASTER_CHECK_BOX;

Object: CHILD_CHECK_BOX
Trigger: WHEN-CHECKBOX_CHANGED

IF :CHILD.CHILD_CHECK_BOX ='N' THEN
:MASTER.MASTER_CHECK_BOX := 'N';
END IF;

FTP form to respective custom_top and compile using frmcmp_batch command. Register form with any Responsibility using Application Developer Responsibility.

That’s it and you have designed a master checkbox to control child checkbox


Advertisement

Related Post

Leave a Reply

Your email address will not be published. Required fields are marked *