How to Auto-Fill a Field in Oracle APEX Interactive Grid Based on LOV Selection - By PL/SQL Function

 

✅ লক্ষ্য:

• TEST_CODE একটি LOV ফিল্ড

• TEST_DESCRIPTION একটি Display-Only ফিল্ড

• TEST_CODE নির্বাচন করলে TEST_DESCRIPTION অটোফিল হবে

• Backend query ব্যবহার করে description fetch হবে

---

🔷 Step-by-Step Guide

---

🟠 Step 1: Create a PL/SQL Function

Oracle DB তে নিচের function টি তৈরি করুন:

plsql
CREATE OR REPLACE FUNCTION get_item_description (
    p_inventory_item_id IN NUMBER
) RETURN VARCHAR2 IS
    l_description VARCHAR2(1000);
BEGIN
  SELECT description INTO l_description
  FROM (
    SELECT 
      msib.inventory_item_id,
      msib.description
    FROM 
      mtl_system_items_b msib
    JOIN 
      mtl_item_catalog_groups micg 
      ON micg.item_catalog_group_id = msib.item_catalog_group_id
    WHERE 
      micg.description = 'Textile Testing Service (TTSL)' 
      AND msib.organization_id = xxpwc.pwc_organization_name('Item Master Organization')
  )
  WHERE inventory_item_id = p_inventory_item_id;

  RETURN l_description;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;

---

🟠 Step 2: Go to APEX Page → Select Interactive Grid

1. Go to Page Designer

2. Click on your Interactive Grid (IG) region

---

🟠 Step 3: Configure TEST_CODE Column

1. Select TEST_CODE column

2. Type: Select List

3. List of Values:

   sql
   SELECT 
     msib.segment1 || ' - ' || msib.description AS display_value,
     msib.inventory_item_id AS return_value
   FROM 
     mtl_system_items_b msib
   JOIN 
     mtl_item_catalog_groups micg 
     ON micg.item_catalog_group_id = msib.item_catalog_group_id
   WHERE 
     micg.description = 'Textile Testing Service (TTSL)' 
     AND msib.organization_id = xxpwc.pwc_organization_name('Item Master Organization')
   ORDER BY msib.segment1
   

---

🟠 Step 4: Create Dynamic Action

1. Select the Interactive Grid region

2. In the right pane, go to Dynamic Actions

3. Click Create → Choose:

   • Event: Change

   • Selection Type: Column

   • Column: TEST_CODE

4. Name the DA: Set Test Description

---

🟠 Step 5: Add True Action → PL/SQL Code

1. Under the Dynamic Action, add a True Action

2. Action Type: Execute PL/SQL Code

3. Code:

plsql

:P_TEST_DESCRIPTION := get_item_description(:P_TEST_CODE);

⚠️ Replace :P_TEST_CODE with the selected value of TEST_CODE column. Since this is Interactive Grid, we need to use Row context.

4. In Items to Submit: add the column TEST_CODE

5. In Page Items to Return: add the column TEST_DESCRIPTION

---

🟠 Step 6: Assign Returned Value to TEST_DESCRIPTION

Still under the same Dynamic Action:

1. Add another True Action → Choose Set Value

2. Set type to: PL/SQL Function Body

3. Code:

plsql

return get_item_description(:TEST_CODE);

4. Set Affected Elements → Selection Type: Column → Column: TEST_DESCRIPTION

---

🟠 Step 7: Set TEST_DESCRIPTION Column

1. Select TEST_DESCRIPTION column in grid

2. Type: Display Only

3. Optionally set Read-Only, Escape special characters = Off

---

✅ Done! Test It:

• Run the page

• Select a value in TEST_CODE

• TEST_DESCRIPTION will auto-populate using your function