a Search Region with Page Items and an Interactive Grid (IG) for the results and dynamic refresh

To build this robust, process-oriented page in Oracle APEX, we will utilize a Search Region with Page Items and an Interactive Grid (IG) for the results. The Interactive Grid is the best choice here because it natively supports row selection, bulk actions, and dynamic refreshing.

Create Tables as below-

Run this in SQL Workshop > SQL Commands to create the tables and some sample data. This includes the STATUS and REJECTION_REASON columns required for your logic.

SQL
-- Create Departments Table
CREATE TABLE apex_departments (
    dept_id      NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    dept_code    VARCHAR2(10) UNIQUE,
    dept_name    VARCHAR2(100) NOT NULL
);

-- Create Employees Table
CREATE TABLE apex_employees (
    emp_id            NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    first_name        VARCHAR2(50),
    last_name         VARCHAR2(50),
    emp_code          VARCHAR2(20) UNIQUE,
    salary            NUMBER(10,2),
    joining_date      DATE,
    dept_id           NUMBER REFERENCES apex_departments(dept_id),
    status            VARCHAR2(20) DEFAULT 'PENDING',
    rejection_reason  VARCHAR2(4000)
);

-- Insert Sample Data
INSERT INTO apex_departments (dept_code, dept_name) VALUES ('HR', 'Human Resources');
INSERT INTO apex_departments (dept_code, dept_name) VALUES ('IT', 'Information Technology');

INSERT INTO apex_employees (first_name, last_name, emp_code, salary, joining_date, dept_id) 
VALUES ('John', 'Doe', 'E001', 5000, TO_DATE('2023-01-15','YYYY-MM-DD'), 1);
COMMIT;

Step 1: Create the Search Region

Create a Static Content region at the top of your page titled Search Filters.
Add the following Page Items:
- P1_LAST_NAME, P1_FIRST_NAME, P1_EMP_CODE (Text Fields).
- P1_DEPT_ID (Select List / LOV based on Departments).
- 1. Navigate to Shared Components > List of Values.
  2. Click Create > From Scratch.
  3. Name: LOV_DEPARTMENTS.
  4. Type: Dynamic.
  5. SQL Query:
    SQL
    SELECT dept_name as display_value, dept_id as return_value FROM apex_departments ORDER BY dept_name
  Step 2: Configure the Search Page Item (P1_DEPT_ID)
  1. Go back to your page in Page Designer.
  2. Select the item P1_DEPT_ID.
  3. Identification > Type: Select List.
  4. List of Values > Type: Shared Component.
  5. List of Values > List of Values: LOV_DEPARTMENTS.
  6. Display Extra Values: No.
  7. Display Null Value: Yes.
  8. Null Display Value: - Select Department -.
  Step 3: Configure the Grid Column (DEPT_ID)
  To ensure the grid doesn't just show an ID number, but shows the name instead:
  1. In the Employee Grid, expand the Columns node.
  2. Select the DEPT_ID column.
  3. Identification > Type: Select List.
  4. List of Values > Type: Shared Component.
  5. List of Values > List of Values: LOV_DEPARTMENTS.
  6. Heading: Department.
- P1_DATE_FROM, P1_DATE_TO (Date Pickers).
Add a Button titled SEARCH in the "Region Body" or "Right of Title".

Step 2: Create the Results Region (Interactive Grid)

Create an Interactive Grid region below the search region.

SQL Query:

SQL
SELECT 
    APEX_ITEM.CHECKBOX2(p_idx => 1, p_value => emp_id) as select_row, -- Row Selector
    emp_id,
    first_name,
    last_name,
    emp_code,
    salary,
    joining_date,
    dept_code,
    dept_name,
    'PENDING' as status, -- Approve/Reject field
    rejection_reason
FROM employees_v
WHERE 
-- Filter by First Name (Case-insensitive)
    (upper(first_name) LIKE '%' || upper(:P1_FIRST_NAME) || '%' OR :P1_FIRST_NAME IS NULL)
    AND
    -- Filter by Last Name
    (upper(last_name) LIKE '%' || upper(:P1_LAST_NAME) || '%' OR :P1_LAST_NAME IS NULL)
    AND
    -- Filter by Employee Code
    (emp_code = :P1_EMP_CODE OR :P1_EMP_CODE IS NULL)
    AND
    -- Filter by Department (LOV)
    (dept_id = :P1_DEPT_ID OR :P1_DEPT_ID IS NULL)
    AND
    -- Date Range Filter (Joining Date)
    (joining_date >= :P1_DATE_FROM OR :P1_DATE_FROM IS NULL)
    AND
    (joining_date <= :P1_DATE_TO OR :P1_DATE_TO IS NULL)

IG Settings: Go to Attributes and ensure Edit > Enabled is On.
Set the STATUS column to Select List with static values: STATIC:Approve;APPROVED,Reject;REJECTED.

Step 3: Requirement Logic & JavaScript

4. Validate Search (Empty Filter Check)

Create a Dynamic Action on the SEARCH button:

Action: Execute JavaScript Code.

Code:

JavaScript
if (!$v('P1_LAST_NAME') && !$v('P1_FIRST_NAME') && !$v('P1_DEPT_ID') && !$v('P1_DATE_FROM')) {
    apex.message.alert('Please select at least one filter before searching.');
} else {
    apex.region("results_ig").refresh();
}

5, 6, & 7. Header Checkbox & Bulk Action

In an Interactive Grid, the header checkbox is built-in. To sync the "Status" dropdown across all selected rows:

Create a Select List in the Search region called P1_BULK_ACTION (Approve/Reject).
Create a Dynamic Action on P1_BULK_ACTION (Change):

Action: Execute JavaScript Code.

Code:

JavaScript
var ig$ = apex.region("results_ig").widget();
var model = ig$.interactiveGrid("getViews", "grid").model;
var selectedRecords = ig$.interactiveGrid("getViews", "grid").getSelectedRecords();
var bulkVal = $v('P1_BULK_ACTION');

selectedRecords.forEach(function(record) {
    model.setValue(record, 'STATUS', bulkVal);
});

8 & 9. Rejection Reason Popup

Create a Hidden Page Item P1_TEMP_ID and a Modal Dialog Page (Page 2) with a Text Area for P2_REASON.
Create a Dynamic Action on the IG column STATUS (Change).
Client-side Condition: Item = Value (REJECTED).

Action: Execute JavaScript to open the popup:

JavaScript
// Store current record ID and open reason modal
var grid = apex.region("results_ig").widget().interactiveGrid("getViews", "grid");
var record = grid.getContextRecord(this.triggeringElement);
$s('P1_TEMP_ID', grid.model.getValue(record, 'EMP_ID'));

apex.navigation.dialog(apex.util.makeApplicationUrl({pageId: 2}), {title:'Rejection Reason'});

Step 4: Final Processing

To save the changes to the database:

Use the Interactive Grid - Automatic Row Processing (DML).
APEX will automatically map the STATUS and REJECTION_REASON values back to your table based on the primary key.

To ensure the rejection reason from the Modal Dialog (Page 2) is passed back to the main Employee Grid (Page 1) and displayed correctly in the "Rejection Reason" column, follow this PL/SQL and JavaScript configuration as below

1. The Modal Dialog "Save" Button (Page 2)

On your Modal Page (where the user types the reason), create a button SAVE_REASON.

Action: Execute Server-Side Code (PL/SQL).

PL/SQL Code:

SQL

-- This simply ensures the value is in session state
apex_util.set_session_state('P2_REASON', :P2_REASON);

Next Action: Close Dialog.
- Settings > Items to Return: P2_REASON

2. The Main Page "Dialog Closed" Event (Page 1)

Now, back on Page 1 (the Employee Grid page), create a Dynamic Action to catch that reason and inject it into the specific row.

Event: Dialog Closed.
Selection Type: Region.
Region: Employee Results Grid.
Action: Execute JavaScript Code.

Code:

JavaScript
// 1. Get the reason returned from the modal
var returnedReason = this.data.P2_REASON;

// 2. Access the Interactive Grid model
var ig$ = apex.region("results_ig").widget();
var grid = ig$.interactiveGrid("getViews", "grid");
var model = grid.model;

// 3. Identify the record we were editing (using the Temp ID we set earlier)
var record = model.getRecord($v('P1_TEMP_ID'));

// 4. Update the 'REJECTION_REASON' column in the grid
if (returnedReason) {
    model.setValue(record, 'REJECTION_REASON', returnedReason);
}

5. Handling Individual vs. Bulk Selection

Since your requirement allows for individual record updates via a dropdown, we need to ensure the "Approve" logic also works instantly without a popup.

Individual Record Toggle

Create a Dynamic Action on the STATUS column of the Interactive Grid:

Event: Change.
Selection Type: Column(s).
Column: STATUS.
Client-side Condition: Item = Value (Value: APPROVED).
Action: Execute JavaScript Code.

Code:

JavaScript
var grid = apex.region("results_ig").widget().interactiveGrid("getViews", "grid");
var record = grid.getContextRecord(this.triggeringElement);

// Clear the rejection reason if they switch back to Approved
grid.model.setValue(record, 'REJECTION_REASON', '');

SAVE ALL for the entire page

Step 5: Configure the Global Save Process
Go to the Processing Tab (the third icon in the left pane of Page Designer).
Right-click "Processes" and select Create Process.
Set the following properties:
Name: Process_Employee_Actions
Type: Interactive Grid - Automatic Row Processing (DML)
Settings > Target Type: Region
Settings > Region: Employee Management (Your IG region)
Important Execution Settings:
Settings > Prevent Loss of Data: Yes
Execution > Sequence: Ensure this is the first process that runs.
Step 6: Add Success/Error Messaging
To give the user clear feedback after they click the "Save All" button:
Success Message: &APP_USER., changes saved successfully.
Error Message: Unable to save changes. Please check the validation errors.
Step 7: (Optional) Custom PL/SQL Logic
If your database requires more than a simple UPDATE (for example, if you need to send an email notification when a record is rejected), change the Target Type from "Region" to PL/SQL Code and use the following:
SQL
BEGIN
    CASE :APEX$ROW_STATUS
        WHEN 'U' THEN -- Update
            UPDATE employees
               SET status = :STATUS,
                   rejection_reason = :REJECTION_REASON,
                   salary = :SALARY
             WHERE emp_id = :EMP_ID;
             
            -- Trigger notification if rejected
            IF :STATUS = 'REJECTED' THEN
                -- Your custom notification procedure here
                -- send_rejection_email(:EMP_ID, :REJECTION_REASON);
                NULL;
            END IF;
            
        WHEN 'C' THEN -- Create (if allowed)
            INSERT INTO employees (first_name, last_name, salary, status)
            VALUES (:FIRST_NAME, :LAST_NAME, :SALARY, :STATUS);
            
        WHEN 'D' THEN -- Delete (if allowed)
            DELETE FROM employees WHERE emp_id = :EMP_ID;
    END CASE;
END;

Technical Knowledge

Search This Blog