Item Relations (Oracle Applications Help)

Item Relations

There are many behaviors in complex forms that must be enforced dynamically at runtime, either to adhere to the field-level validation model of Oracle Applications, or to enforce specific business rules.

You should model your form's item and event handlers after these examples.

Disabled Items and WHEN-VALIDATE-ITEM Trigger

In most of the item relations you are dynamically disabling and enabling items. For your disabled items, note these Oracle Forms coding issues:

WHEN-VALIDATE-ITEM always fires the first time a user Tabs through each field on a brand new record, even if they do not make a change. Internally Oracle Forms notes that the value changes from unknown to null, therefore it fires WHEN-VALIDATE-ITEM. Also, WHEN-VALIDATE-ITEM fires when a user changes a field from a non-null value to null.

Furthermore, a user can leave a required field null at any time; it is only trapped at record level. Therefore, all WHEN- VALIDATE-ITEM triggers must account for the value of the field being null, and act accordingly. Since you cannot distinguish between the user changing the value to null, or Oracle Forms setting the value to null the first time, both must behave as if the user changed the value.
Most of the time, a disabled item has a null value. Since you account for nulls because of the previous issue, this is not a problem. In those rare cases that a disabled field has a value, and that value got set while it was disabled and the field has not been validated yet, you may need to add logic to WHEN-VALIDATE-ITEM to do nothing.

Dependent Items

To create a text item, check box, or poplist that is enabled only when a master item is populated, use the procedure APP_FIELD.SET_ DEPENDENT_FIELD. This routine enforces the following behaviors:

The dependent item is either cleared or made invalid when the master item changes.
If the master item is NULL or the condition is FALSE, the dependent item is disabled.

Create the item handler procedures as shown below and then call the procedures from the specified triggers.

Attention: These routines do not apply to display-only text items. To conditionally grey out display-only text items, use the routine APP_ITEM_PROPERTY.SET_VISUAL_ ATTRIBUTE.

See: APP_ITEM_PROPERTY: Property Utilities

In the following example, a block order has items item_type and item_name. Item_name is dependent on item_type, thus item_name is enabled only when item_type is NOT NULL.

Create your item handler procedures as follows:

 PACKAGE BODY ORDER IS
     PROCEDURE ITEM_TYPE(EVENT VARCHAR2) IS
     BEGIN
       IF (EVENT = 'WHEN-VALIDATE-ITEM') THEN
         --- Any validation logic goes here.
         ITEM_NAME('INIT');
       ELSE
          fnd_message.debug('Invalid event passed to
              ORDER.ITEM_TYPE: ' || EVENT);
       END IF;
     END ITEM_TYPE;

 PROCEDURE ITEM_NAME(EVENT VARCHAR2) IS
     BEGIN
       IF ((EVENT = 'PRE-RECORD') OR
           (EVENT = 'INIT')) THEN
         APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
                                       'ORDER.ITEM_TYPE',
                                       'ORDER.ITEM_NAME');
       ELSE
          fnd_message.debug('Invalid event passed to
              ORDER.ITEM_NAME: ' || EVENT);
       END IF;
     END ITEM_NAME;
   END ORDER;

Call your item handler procedures in:

Trigger: WHEN-VALIDATE-ITEM on item_type:

order.item_type('WHEN-VALIDATE-ITEM');

Trigger: PRE-RECORD on order (Fire in Enter-Query Mode: No):

order.item_name('PRE-RECORD');

If your master and dependent items are in a multi-row block, or if they are items in a single-row block that is a detail of a master block, you must call SET_DEPENDENT_FIELD for the POST-QUERY event as well.
```
PROCEDURE ITEM_NAME(EVENT VARCHAR2) IS
     BEGIN
       IF ((EVENT = 'PRE-RECORD') OR
           (EVENT = 'INIT') OR
           (EVENT = 'POST-QUERY')) THEN
          APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
                                       'ORDER.ITEM_TYPE',
                                       'ORDER.ITEM_NAME');
       ELSE
          fnd_message.debug('Invalid event passed to
              ORDER.ITEM_NAME: ' || EVENT);
       END IF;
     END ITEM_NAME;
```
Add another call to your item handler procedure in:
```
Trigger: POST-QUERY
```
```
ORDER.ITEM_NAME('POST-QUERY');
```
Attention: In a multi-record block, if the dependent item is the last item in the record, the cursor navigates to the next record when tabbing from the master. To work around this behavior, code a KEY-NEXT-ITEM trigger that does a VALIDATE(Item_scope) and then a NEXT_ITEM.

Attention: If the dependent item is a required list or option group, set the "invalidate" parameter in the call to APP_FIELD.SET_DEPENDENT_FIELD to TRUE. When this flag is TRUE, the dependent item is marked as invalid rather than cleared.

Conditionally Dependent Item

A conditionally dependent item is enabled or disabled depending on the particular value of the master item. In this example, the block order has items item_type and item_size. Item_size is enabled only when item_type is "SHOES."

Create your item handler procedures as follows. Note that this item handler is very similar to the simple master/dependent situation, but you specify the condition instead of the name of the master item.

PACKAGE BODY order IS
 PROCEDURE ITEM_TYPE(EVENT VARCHAR2) IS
 BEGIN
   IF (EVENT = 'WHEN-VALIDATE-ITEM') THEN
     size('INIT');
   ELSE 
      fnd_message.debug('Invalid event passed to
        ORDER.ITEM_TYPE: ' || EVENT); 
   END IF;
 END item_type;
 PROCEDURE size(EVENT VARCHAR2) IS
 BEGIN
   IF ((EVENT = 'PRE-RECORD') OR
       (EVENT = 'INIT')) THEN
     APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
                                  (:order.item_type = 'SHOES'),
                                   'ORDER.SIZE');
   ELSE 
      fnd_message.debug('Invalid event passed to
        ORDER.SIZE: ' || EVENT); 
   END IF;
 END size;
END order;

Call your item handler procedures in:

Trigger: PRE-RECORD on order (Fire in Enter-Query Mode: No):

order.item_size('PRE-RECORD');

Trigger: WHEN-VALIDATE-ITEM on item_type:

order.item_type('WHEN-VALIDATE-ITEM');

Multiple Dependent Items

There are cases where multiple items are dependent on a single master item. For example, only certain item_types can specify a color and size. Therefore, the color and size fields are dependent on the master field item_type, and they are enabled only when item_type is "RAINCOAT."

Create your item handler procedures as follows:

PACKAGE BODY order IS
 PROCEDURE item_type(EVENT VARCHAR2) IS
 BEGIN
   IF (EVENT = 'WHEN-VALIDATE-ITEM') THEN
    color('INIT');
    size('INIT');
   ELSE
      fnd_message.debug('Invalid event passed to
        ORDER.ITEM_TYPE: ' || EVENT);
   END IF;
 END item_type;
 PROCEDURE color(EVENT VARCHAR2) IS
 BEGIN
   IF (EVENT = 'PRE-RECORD') OR
      (EVENT = 'INIT') THEN
       APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
                          (:order.item_type = 'RAINCOAT'),
                          'ORDER.COLOR');
   ELSE
      fnd_message.debug('Invalid event passed to
        ORDER.COLOR: ' || EVENT);
   END IF;
 END color;
 PROCEDURE size(EVENT VARCHAR2) IS
 BEGIN
   IF (EVENT = 'PRE-RECORD') OR
      (EVENT = 'INIT') THEN
     APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
                          (:order.item_type = 'RAINCOAT'),
                          'ORDER.SIZE');
   ELSE
      fnd_message.debug('Invalid event passed to
        ORDER.SIZE: ' || EVENT);
   END IF;
 END size;
END order;

Call your item handler procedures in:

Trigger: WHEN-VALIDATE-ITEM on order.item_type:

order.item_type('WHEN-VALIDATE-ITEM');

Trigger: PRE-RECORD (Fire in Enter-Query Mode: No):

order.color('PRE-RECORD');
order.size('PRE-RECORD');

Two Master Items and One Dependent Item

There may also be cases where an item is dependent on two master items. Suppose that different sizes of sweaters come in different colors. You cannot fill in the color of the sweater until you have filled in both item_type and size. The validation of block.dependent is controlled by the content of both master_1 and master_2.

Create your item handler procedures as follows:

PACKAGE BODY order IS
     PROCEDURE item_type(EVENT VARCHAR2) IS
     BEGIN
       IF (EVENT = 'WHEN-VALIDATE-ITEM') THEN
         color('INIT'):
   ELSE
      fnd_message.debug('Invalid event passed to
        ORDER.ITEM_TYPE: ' || EVENT);
       END IF;
     END item_type;
     PROCEDURE size(EVENT VARCHAR2) IS
     BEGIN
       IF (EVENT = 'WHEN-VALIDATE-ITEM') THEN
         color('INIT');
   ELSE
      fnd_message.debug('Invalid event passed to
        ORDER.SIZE: ' || EVENT);
       END IF;
     END size;
     PROCEDURE color(EVENT VARCHAR2) IS
     BEGIN
       IF (EVENT = 'PRE-RECORD') OR
          (EVENT = 'INIT') THEN
          APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
                ((:order.item_type IS NOT NULL) AND
                 (:order.size IS NOT NULL)),
                  'ORDER.COLOR');
       ELSE
        fnd_message.debug('Invalid event passed to
        ORDER.COLOR: ' || EVENT);
       END IF;
     END color;

END order;

Call your item handler procedures in:

Trigger: WHEN-VALIDATE-ITEM on order.item_type:

order.item_type('WHEN-VALIDATE-ITEM');

Trigger: WHEN-VALIDATE-ITEM on order.size:

order.size('WHEN-VALIDATE-ITEM');

Trigger: PRE-RECORD (Fire in Enter-Query Mode: No):

order.color('PRE-RECORD');

Cascading Dependence

With cascading dependence, item_3 depends on item_2, which in turn depends on item_1. Usually all items are in the same block.

For example, the block order contains the items vendor, site, and contact.

The list of valid sites depends on the current vendor.

Whenever vendor is changed, site is cleared.
Whenever vendor is null, site is disabled.

The list of valid contacts depends on the current site.

Whenever site is changed, contact is cleared.
Whenever site is null, contact is disabled.

To code the correct behavior for these dependent items, follow these steps.

Create your item handler procedures as follows:

PACKAGE BODY order IS
PROCEDURE vendor(EVENT VARCHAR2) IS
BEGIN
   IF (EVENT = 'WHEN-VALIDATE-ITEM') THEN
      SITE('INIT');
   ELSE
      fnd_message.debug('Invalid event passed to
        ORDER.VENDOR: ' || EVENT);
   END IF;
END VENDOR;
PROCEDURE SITE(EVENT VARCHAR2) IS
BEGIN
   IF (EVENT = 'WHEN-VALIDATE-ITEM') THEN
      CONTACT('INIT');
   ELSIF (EVENT = 'PRE-RECORD') OR
         (EVENT = 'INIT') THEN
      APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
                                    'ORDER.VENDOR',
                                    'ORDER.SITE');
      CONTACT(EVENT);
   ELSE
      fnd_message.debug('Invalid event passed to
        ORDER.SITE: ' || EVENT);
   END IF;
END SITE;
PROCEDURE CONTACT(EVENT VARCHAR2) IS
BEGIN
   IF (EVENT = 'PRE-RECORD') OR
      (EVENT = 'INIT') THEN
      APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
                                   'ORDER.SITE',
                                   'ORDER.CONTACT');
   ELSE
      fnd_message.debug('Invalid event passed to
        ORDER.CONTACT: ' || EVENT);
   END IF;
END CONTACT;
END ORDER;

Call your item handler procedures in:
```
Trigger: WHEN-VALIDATE-ITEM on vendor:
```
```
order.vendor('WHEN-VALIDATE-ITEM');
```
```
Trigger: WHEN-VALIDATE-ITEM on site:
```
```
order.site('WHEN-VALIDATE-ITEM');
```
```
Trigger: PRE-RECORD on order (Fire in Enter-Query Mode: No):
```
```
order.site('PRE-RECORD');
order.contact('PRE-RECORD');
```
Remember that the following chain of events occurs whenever the VENDOR field is validated:
- VENDOR is validated, which calls SITE ('INIT').
- SITE ('INIT') causes the state of SITE to change and calls CONTACT ('INIT').
- CONTACT ('INIT') causes the state of CONTACT to change.

Mutually Exclusive Items

Use the procedure APP_FIELD.SET_EXCLUSIVE_FIELD to code two items where only one item is valid at a time.

The key to coding an item handler procedure for mutually exclusive items is to realize that mutually exclusive items are logically one item. Whenever one of a pair of mutually exclusive items is dependent on or depended upon by another item, they both are. Their relationship to other items is always identical. Therefore, code a single item handler procedure for the single logical item.

If both mutually exclusive items are NULL, then both items are navigable. If one item is populated, then the other item is unnavigable (you can still click there), and any value in that item is cleared.

If one item must be not null, set the REQUIRED property of both items to be Yes in the Oracle Forms Developer. If both items may be null, set the REQUIRED property of both items to be No. APP_FIELD.SET_ EXCLUSIVE_FIELD reads the initial REQUIRED property and dynamically manages the REQUIRED properties of both items.

You can also use the procedure APP_FIELD.SET_EXCLUSIVE_FIELD for a set of three mutually exclusive items. For more than three items, you must write your own custom logic.

Attention: Mutually exclusive check boxes and required lists require mouse operations.

For example, a block lines has mutually exclusive items credit and debit.

Call your item handler procedures in:

 PACKAGE BODY lines IS
   PROCEDURE credit_debit(EVENT VARCHAR2) IS
   BEGIN
      IF ((EVENT = 'WHEN-VALIDATE-ITEM') OR
          (EVENT = 'PRE-RECORD')) THEN
         APP_FIELD.SET_EXCLUSIVE_FIELD(EVENT,
                                       'LINES.CREDIT',
                                       'LINES.DEBIT');
      ELSIF (EVENT = 'WHEN-CREATE-RECORD') THEN
         SET_ITEM_PROPERTY('lines.credit', ITEM_IS_VALID,
                            PROPERTY_TRUE);
         SET_ITEM_PROPERTY('lines.debit', ITEM_IS_VALID,
                            PROPERTY_TRUE);
      ELSE
        fnd_message.debug('Invalid event passed to
        Lines.credit_debit: ' || EVENT);
      END IF;
   END credit_debit;
END lines;

Create your item handler procedures as follows:

Trigger: WHEN-VALIDATE-ITEM on credit:

lines.credit_debit('WHEN-VALIDATE-ITEM');

Trigger: WHEN-VALIDATE-ITEM on debit:

lines.credit_debit('WHEN-VALIDATE-ITEM');

Trigger: PRE-RECORD on lines (Fire in Enter-Query Mode: No):

lines.credit_debit('PRE-RECORD');

Trigger: WHEN-CREATE-RECORD on lines:

lines.credit_debit('WHEN-CREATE-RECORD');

You only need the WHEN-CREATE-RECORD trigger if the resulting one of your mutually-exclusive fields is required. This trigger initially sets all the mutually-exclusive fields of the set to be required. The fields are then reset appropriately once a user enters a value in one of them.

Mutually Inclusive Items

Use APP_FIELD.SET_INCLUSIVE_FIELD to code a set of items where, if any of the items is not null, all of the items are required.

The item values may be entered in any order. If all of the items are null, then the items are optional.

You can use the procedure APP_FIELD.SET_INCLUSIVE_FIELD for up to five mutually inclusive items. For more than five items, you must write your own custom logic.

This example shows a block payment_info with mutually inclusive items payment_type and amount.

Create your item handler procedures as follows:

 PACKAGE BODY payment_info IS
   PROCEDURE payment_type_amount(EVENT VARCHAR2) IS
   BEGIN
      IF ((EVENT = 'WHEN-VALIDATE-ITEM') OR
          (EVENT = 'PRE-RECORD')) THEN
         APP_FIELD.SET_INCLUSIVE_FIELD(EVENT,
                                 'PAYMENT_INFO.PAYMENT_TYPE',
                                 'PAYMENT_INFO.AMOUNT');
      ELSE
         fnd_message.debug('Invalid event to
           payment_info.payment_type_ amount: ' || EVENT);
      END IF;
   END payment_type_amount;
END payment_info;

Call your item handler procedures in:

Trigger: WHEN-VALIDATE-ITEM on payment_info.payment_type:

payment_info.payment_type_amount('WHEN-VALIDATE-ITEM');

Trigger: WHEN-VALIDATE-ITEM on payment_info.amount:

payment_info.payment_type_amount('WHEN-VALIDATE-ITEM');

Trigger: PRE-RECORD on payment_info (Fire in Enter-Query Mode: No):

payment_info.payment_type_amount('PRE-RECORD');

Mutually Inclusive Items with Dependent Items

There are cases where items are dependent on master items, where the master items are mutually inclusive.

See: Item Relations.

This example shows a block payment_info with mutually inclusive items payment_type and amount, just as in the previous example. The block also contains two regions, one for check information and one for credit card information. Check Information has a single item, check_number. Credit Card Information has five items: credit_type, card_holder, number, expiration_date, and approval_code.

Payment Type can be Cash, Check, or Credit.

When Payment Type is Check, the Check Information region is enabled.
When Payment Type is Credit, the Credit Card Information region is enabled.

Create your item handler procedures as follows:

PACKAGE BODY payment_info IS
PROCEDURE payment_type_amount(EVENT VARCHAR2) IS
BEGIN
   IF (EVENT = 'WHEN-VALIDATE-ITEM') THEN
      APP_FIELD.SET_INCLUSIVE_FIELD(EVENT,
 
                                   'PAYMENT_INFO.PAYMENT_TYPE',
                                   'PAYMENT_INFO.AMOUNT');
      IF (:SYSTEM.CURSOR_ITEM =
          'payment_info.payment_type') THEN
         check_info('INIT');
         credit_info('INIT');
      END IF;
   ELSIF (EVENT = 'PRE-RECORD') THEN
      APP_FIELD.SET_INCLUSIVE_FIELD(EVENT,
                              'PAYMENT_INFO.PAYMENT_TYPE',
                              'PAYMENT_INFO.AMOUNT');
   ELSE
      fnd_message.debug('Invalid event in
        payment_info.payment_type_amount: ' || EVENT);
   END IF;
END payment_type_amount;

 PROCEDURE check_info IS
BEGIN
   IF ((EVENT = 'PRE-RECORD') OR
       (EVENT = 'INIT')) THEN
      APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
        (:payment_info.payment_type = 'Check'),
        'PAYMENT_INFO.CHECK_NUMBER');
   ELSE
      fnd_message.debug('Invalid event in
         payment_info.check_info: ' || EVENT);
   END IF;
END check_info;
PROCEDURE credit_info IS
   CONDITION BOOLEAN;
BEGIN
   IF ((EVENT = 'PRE-RECORD') OR
       (EVENT = 'INIT')) THEN
      CONDITION := (:payment_info.payment_type = 'Credit');
      APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
                                CONDITION,
                               'PAYMENT_INFO.CREDIT_TYPE');
      APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
                                CONDITION,
                                'PAYMENT_INFO.NUMBER');
      APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
                                CONDITION,
                                'PAYMENT_INFO.CARD_HOLDER');
      APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
                                CONDITION,
                               'PAYMENT_INFO.EXPIRATION_DATE');
      APP_FIELD.SET_DEPENDENT_FIELD(EVENT,
                               CONDITION,
                               'PAYMENT_INFO.APPROVAL_CODE');
   ELSE
     fnd_message.debug('Invalid event in
         payment_info.credit_info: ' || EVENT);
   END IF;
END credit_info;
END payment_info;

Call your item handler procedures in:

Trigger: WHEN-VALIDATE-ITEM on payment_info.payment_type:

payment_info.payment_type_amount('WHEN-VALIDATE-ITEM');

Trigger: WHEN-VALIDATE-ITEM on payment_info.amount:

payment_info.payment_type_amount('WHEN-VALIDATE-ITEM');

Trigger: PRE-RECORD on payment_info (Fire in Enter-Query Mode: No):

payment_info.payment_type_amount('PRE-RECORD');
payment_info.check_info('PRE-RECORD');
payment_info.credit_info('PRE-RECORD');

Conditionally Mandatory Items

Use the procedure APP_FIELD.SET_REQUIRED_FIELD to code an item that is only mandatory when a certain condition is met. If the condition is FALSE, the dependent item is optional. Any value in the dependent item is not cleared. If an item is both conditionally required and dependent, call APP_FIELD.SET_DEPENDENT_FIELD before calling APP_FIELD.SET_REQUIRED_FIELD.

An example demonstrates using APP_FIELD.SET_REQUIRED_FIELD.

A block purchase_order has items total and vp_approval. Vp_approval is required when total is more than $10,000. (Note: quantity * unit_price = total).

Create your item handler procedures as follows:

PACKAGE BODY purchase_order IS
PROCEDURE vp_approval(EVENT VARCHAR2) IS
BEGIN
   IF ((EVENT = 'PRE-RECORD') OR
       (EVENT = 'INIT')) THEN
      APP_FIELD.SET_REQUIRED_FIELD(EVENT,
                             (:purchase_order.total > 10000),
                              'PURCHASE_ORDER.VP_APPROVAL');
   ELSE
      fnd_message.debug('Invalid event in
        purchase_order.vp_approval: ' || EVENT);
   END IF;
END vp_approval;
PROCEDURE total(EVENT VARCHAR2) IS
BEGIN
   IF (EVENT = 'INIT') THEN
      :purchase_order.total := :purchase_order.quantity *
                               :purchase_order.unit_price;
      vp_approval('INIT');
   ELSE
      fnd_message.debug('Invalid event in purchase_order.total: ' || EVENT);
END total;
PROCEDURE quantity(EVENT VARCHAR2) IS
BEGIN
   IF (EVENT = 'WHEN-VALIDATE-ITEM') THEN
      total('INIT');
   ELSE
      fnd_message.debug('Invalid event in
        purchase_order.quantity: ' || EVENT);
   END IF;
END quantity;
PROCEDURE unit_price(EVENT VARCHAR2) IS
BEGIN
   IF (EVENT = 'WHEN-VALIDATE-ITEM') THEN
      total('INIT');
   ELSE
      fnd_message.debug('Invalid event in
        purchase_order.unit_price: ' || EVENT);
   END IF;
END unit_price;
END purchase_order;

Call your item handler procedures in:

Trigger: PRE-RECORD on purchase_order (Fire in Enter-Query Mode: No):

purchase_order.vp_approval('PRE-RECORD');

Trigger: WHEN-VALIDATE-ITEM on quantity:

purchase_order.quantity('WHEN-VALIDATE-ITEM');

Trigger: WHEN-VALIDATE-ITEM on unit_price:

purchase_order.unit_price('WHEN-VALIDATE-ITEM');