Re: Need advice on Oracle Forms

Dave Williams wrote:

> All,
>
> I am looking for the best way to code/design a form around several tables
> that I have. I will go into detail as to what I am trying to do.
>
> I have 3 tables: Users, Depts and Roles.
>
> A user belongs to a dept and has a role. Other information in the user
> table is the country he/she resides along with Oracle apps login name and
> e-mail.
>
> I want this form to be a muti-purpose form that allows an admin type person
> to add users and to add new roles and depts. Countries come from
> FND_TERRITORIES_TL.
>
> I envision the form having 3 tabs. One for users, 1 for depts and 1 for
> roles.
>
> On the tab for user maintenance I obviously want to validate entry for roles
> and depts (and countries) to be valid against the appropriate tables. The
> user table has dept_id, role_id and terriotry_short_name as references to
> the other tables.
>
> So I need to create additional fields in the user tab to allow for dept
> name, role name and country name. These extra fields will have LOV's on
> them that validate the data entry against the other tables.
>
> This works well when creating new users. When querying back existing users
> I need to execute a post-query trigger to go get the dept name, role name
> and country name based upon the id that comes back from the user table.
>
> Again, this is all good except that if I try and close the form after the
> query then I get asked if I want to save the data. There is no real update
> to the data but the form thinks there is because the post-query trigger is
> populating some text items for dept name, role name and country name.
>
> So, what is the best way to do this ? I could set the record status to
> QUERY at the end of the post-query trigger. But, is there a nicer way of
> doing this ?
>
> Please note, the problem is in the USER tab part of the form since it
> references data from other tables. The DEPT and ROLE tabs are just fine
> because it is all based upon a single table.
>
> Any help/advice would be much appreciated.
>
> TIA.
>
> Dave.

Forget the post-query trigger.

Build a function on the form that queries the information based on a formulae attached to a database column. Something like this:

The formula:
get_org(:training.organization_id)

The function:

FUNCTION get_org (orgidin VARCHAR2) RETURN VARCHAR2 IS

x VARCHAR2(92);

BEGIN
  IF orgidin IS NOT NULL THEN
    SELECT org_name_1 || ' ' || org_name_2 || ' ' || org_name_3     INTO x
    FROM organization
    WHERE organization_id = orgidin;

    RETURN x;
  END IF;   RETURN NULL;
EXCEPTION
  WHEN OTHERS THEN
   NULL; END; It is far simpler to write, test, and debug.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)