Interactive Grid - Aggregate Validations

In Oracle APEX, validating Interactive Grid (IG) data at row level, is easy and straight forward. You just need to select "Editable Region" for the validation and then you can use IG column values using :COLUMN_NAME syntax in SQL and PL/SQL code. However, if you want to create a validation at table level or validation which uses multiple rows data, then it becomes tricky.

Let's consider a simple example of budget planning, where you can allocate percentage for each category, like 10% for healthcare, 10% for education etc. Here, we need to implement a simple table level validation to ensure total allocation % does not exceed 100.

Please refer Interactive Grid - Client Side Aggregate Validations for implementing this validation entirely on client side. You can use technique explained below for more complex validations that can't be done on client side (checking data from multiple tables, number of rows in IG are more than 50)

Before jumping into the implementation, let's review few points about IGs

1. For IGs, when page is submitted or when you click default "Save" button, then only newly added rows and modified rows are submitted to server.
2. Selecting default check-box (Column APEX$ROW_SELECTOR) will not change behavior of which rows are submitted to server. This check box selection is mainly for performing row actions enabled for IG (Column APEX$ROW_ACTION)
3. When you use default "Save" button to update IG data into DB, then APEX won't submit entire page. APEX will make AJAX call and partially submits the page and processes new/modified rows of IG. However, all validations defined for the IG will still be executed. But, page level validations will not be executed (because whole page is not submitted).

Aggregate Validations: 

To perform above discussed validation, we need data added/modified by user in front-end UI + data which is already existing in DB, if any. 

Table Design: 

To understand code better, please assume table BLOG_BUDGET_PLANNING with below columns

PLAN_ID - Primary Key Column (GENERATED ALWAYS AS IDENTITY)

PLAN_CATEGORY - Budget Planning Category

PLAN_PERCENTAGE - Number column to store allocation percentage

+ Audit (Who) columns. 

Implementation: 

To start with, create IG region on table BLOG_BUDGET_PLANNING and enable edit mode. Next, create two hidden page items. One item, P11_SUM_PLAN_PERCENTAGE, to hold sum of "Allocation %" entered by user in front-end and another item, P11_PLAN_IDS, to hold list of primary key values for the rows modified by user. Please disable "Value Protected" option for both these page items. When "Value Protected" option is enabled, and if you modify item value using JS and submit page, then you will get an error. 

Create after submit page process "Aggregate P11_SUM_PLAN_PERCENTAGE" to calculate SUM of "Allocation %" entered by user for added/modified rows and to capture primary key values for rows modified. Selected "Budget Planning" IG as "Editable Region" for the page process.

Page Process Code:

-- aggregate plan percentage value for added/modified rows
:P11_SUM_PLAN_PERCENTAGE := COALESCE(:P11_SUM_PLAN_PERCENTAGE,0) + :PLAN_PERCENTAGE;

-- for rows being modified, save plan_ids (primary key values)
IF :PLAN_ID IS NOT NULL THEN
  :P11_PLAN_IDS := :P11_PLAN_IDS||':'||:PLAN_ID;
END IF;

Next, create a page level validation which checks "Allocation %" already there in DB, excluding rows that are being modified by user (variable l_db_plan_percentage in below code) and add it to "Allocation %" entered by user which was calculated in above page process. Check if the sum of these two is more than 100. If yes, then raise validation error.

Validation Type: PL/SQL Function Body (returning Boolean)

Editable Region: Null

Validation Code:

DECLARE 
  l_db_plan_percentage NUMBER;
BEGIN
  -- take sum of existing rows
  SELECT SUM(PLAN_PERCENTAGE)
  INTO l_db_plan_percentage
  FROM blog_budget_planning
  -- consider rows that are NOT being updated
  WHERE INSTR(':'||:P11_PLAN_IDS||':',':'||TO_CHAR(plan_id)||':') = 0
  ;
  
  -- Check if total percentage is > 100, then raise error
  IF :P11_SUM_PLAN_PERCENTAGE + COALESCE(l_db_plan_percentage,0) > 100 THEN
    RETURN FALSE;
  END IF;
  
  RETURN TRUE;
END;

Next, create button 'SAVE" which will trigger dynamic action and execute below JS code. Here, we need to create separate SAVE button to ensure page is submitted and page validation defined above is executed. Because, above validation is not linked with IG, so IG default "Save" button will not trigger this validation.

SAVE button D.A. JS code:

apex.page.submit( {
    request: "SAVE"
    ,set: {
       "P11_SUM_PLAN_PERCENTAGE": 0
        ,"P11_PLAN_IDS": ""
    }
    ,showWait: true,
} );

Please note, while submitting the page we are also clearing/initializing the hidden items values. This is required to clear values from previous page submit and validation cycle. 

That's it. We are good to go. link to demo

What do you think about this approach? Do you have any other ideas to solve this problem? I am eager to hear your feedback. 

Thank you.