APEX

Hi all,

I am struggling to implement an eqivalent of a post-query trigger in Oracle Forms. Please bear with me as I am trying to explain in detail what the problem is.

Here is my situation.

I have three tables EMP, DEPT and LOCATION. I created a Master/Table pages on EMP and DEPT.

Basic relationships.


Each LOCATION has one or more DEPTs; Each DEPT is at one and only one LOCATION.
Each DEPT has one ore more EMPs; Each EMP is assigned to only and only DEPT.

Not getting too complicated, here are the table layouts:

LOCATION:
-----------------
ID number(10,0) not null,
NAME varchar2(20) not null


DEPT
--------------
ID number(10,0) not null,
NAME varchar2(20) not null,
LOC_ID number (10,0) not null foreign key from LOCATION


EMP
--------------
ID number(10,0) not null,
NAME varchar2(20) not null,
DEPT_ID number (10,0) not null foreign key from DEPT


Assume I have all the constraints, BIU triggers, sequences defined properly.


I am using the APEX page wizard to create a Master/Detail Form on DEPT and EMP. The first page is the Master Report on DEPT. I want to display the Location Name on this page also. Because the Master Report allows
you to change the query, I was able to add the Location Name as part of the
query. This was very simple.

Select a.name "Department_Name",
b.name "Location"
from dept a, location b
where a.loc_id = b.id


Moving on, In the Master Detail Form, I would like to get the Location Name as part of the first Region (Dept Region).

In this region, I would like to include the Location Name also. So my first region on the Master Detail form includes:

Dept Id: ________
Dept Name: _______
Location Name: __________

As Region are automatically populated using using a Fetch Row from EMP table (Automated Row Fetch) on an After Header process point, I don't have a way of including the Location as part of query. So I created a Region level Item called Location Name, made it Display only.

In the old SQL*Forms, or Oracle Forms days, I used to use a Post-Query trigger, or Post-Change trigger to fire on the Loc_Id column to populate the Location Name. Simple fetch like:

Select name
into :P80_location_name
from location
where loc_id = :P80_loc_id


However, I am struggling to implement some thing simple like this in APEX. Tried creating a processes, computatations etc, but nothing is working.

I have seen some previous responses to fetching values from a foreign table
using a button or AJAX script, but this should be very basic. What am I
missing here?

Appreciate any insights.

Thanks.

John