Skip Headers

Oracle E-Business Suite Developer's Guide
Release 12.1
Part Number E12897-04
Go to Table of Contents
Contents
Go to previous page
Previous
Go to next page
Next

Coding Item Behavior

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 E-Business Suite, 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:

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:

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

Important: 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.

  1. 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;
    
  2. 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');
    
  3. 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');
    

    Important: 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.

    Important: 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."

  1. 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;
    
  2. 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."

  1. 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;
    
  2. 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.

  1. 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;
    
  2. 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.

The list of valid contacts depends on the current site.

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

  1. 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;
    
  2. 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:

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.

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

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

  1. 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;
    
  2. 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.

  1. 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;
    
  2. 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.

  1. 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;
    
  2. 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).

  1. 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;
    
  2. 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');
    

Defaults

Defaults on a New Record

To default values when the user first creates a new record, use the Default values property in the Oracle Forms Designer. For more complex defaulting behavior, follow the example below.

  1. Create your event handler procedure as follows:

    PACKAGE block IS
       
       PROCEDURE WHEN_CREATE_RECORD IS
       BEGIN
          :block.item1 := default_value1;
          :block.item2 := default_value2;
           ...
       END WHEN_CREATE_RECORD;
    
    END block;
    
  2. Call your event handler procedures in:

    Trigger: WHEN-CREATE-RECORD: 
    
    block.WHEN_CREATE_RECORD;
    

Applying Defaults While Entering a Record

When you want to set a default for an item whose validation depends on another item (for example, to apply the default when the master value changes), set the default values in the dependent item's INIT event.

Integrity Checking

This section discusses how to handle:

Uniqueness Check

To do a uniqueness check for a key, use a select statement that is invoked by the WHEN-VALIDATE-ITEM event.

Note that a uniqueness check done in WHEN-VALIDATE-ITEM does not catch duplicates residing in uncommitted rows (for instance, a user enters uncommitted, duplicate rows in a detail block). The database constraints will catch this situation, as well as the situation where an identical key is committed by someone else between the time that the WHEN-VALIDATE-ITEM fired and your record is committed. For this reason, you do not need to write a uniqueness check in PRE-UPDATE or PRE-INSERT.

Example:

 PROCEDURE CHECK_UNIQUE(X_ROWID VARCHAR2,
          pkey1 type1, pkey2 type2, ...) IS
  DUMMY NUMBER;
BEGIN
   SELECT COUNT(1)
      INTO DUMMY
      FROM table
   WHERE pkeycol1 = pkey1
    AND pkeycol2 = pkey2
      ...
    AND ((X_ROWID IS NULL) OR (ROWID != X_ROWID));
   IF (DUMMY >= 1) then
       FND_MESSAGE.SET_NAME('prod', 'message_name');
      APP_EXCEPTION.RAISE_EXCEPTION;
 
   END IF;
END CHECK_UNIQUE;

Create your item handler procedure as follows:

 PACKAGE BODY block IS
 PROCEDURE item(EVENT VARCHAR2) IS
   BEGIN
      IF (EVENT = 'WHEN-VALIDATE-ITEM') THEN
        table_PKG.CHECK_UNIQUE(:block.row_id,
                  :block.pkey1, :block.pkey2, ...);
      ELSE
         message('Invalid event in block.item');
      END IF
   END item;
 END block;

Referential Integrity Check

When deleting a record, you must be concerned about the existence of other records that may be referencing that record. For example, if an item has already been placed on a Purchase Order, what should occur when you attempt to delete the item? Three possible answers are:

Most of the time, the first solution is both the most practical and sensible. To do this, create a procedure that detects these referenced cases, and raise an exception.

Giving Warning Before Deleting Details

To give a warning when detail records will be deleted, create CHECK_REFERENCES as a function which returns FALSE if detail records exist (CHECK_REFERENCES should still raise an exception if deleting the row would cause a referential integrity error).

If a table contains subtypes, you must decide whether you need one CHECK_REFERENCES procedure or one CHECK_REFERENCES procedure per subtype.

If the subtypes share most of the foreign key references with some subtype-specific foreign key references, then create just one CHECK_REFERENCES procedure with the first parameter a subtype discriminator.

If the subtypes are orthogonal, then create a CHECK_subtype_REFERENCES procedure for each subtype.

Example Referential Integrity Check

  1. Create your table handler procedures as follows:

     CREATE OR REPLACE PACKAGE BODY table_PKG AS
         PROCEDURE CHECK_REFERENCES(pkey1 type1, pkey2 type2, ...) IS
           MESSAGE_NAME VARCHAR2(80);
           DUMMY        credit;
         BEGIN
           MESSAGE_NAME := 'message_name1';
           SELECT 1 INTO DUMMY FROM DUAL WHERE NOT EXISTS
             (SELECT 1 FROM referencing_table1
              WHERE ref_key1 = pkey1
                AND ref_key2 = pkey2
                ...                
             );
           MESSAGE_NAME := 'message_name2';
           SELECT 1 INTO DUMMY FROM DUAL WHERE NOT EXISTS
             (SELECT 1 FROM referencing_table2
              WHERE ref_key1 = pkey1
                AND ref_key2 = pkey2
                ...                
             );
           ...
         EXCEPTION
           WHEN NO_DATA_FOUND THEN
             FND_MESSAGE.SET_NAME('prod', MESSAGE_NAME);
             APP_EXCEPTION.RAISE_EXCEPTION;
         END CHECK_REFERENCES;
       END table_PKG;
    
  2. Create your event handler procedures as follows:

     PACKAGE BODY block IS
        PROCEDURE key_delete IS
        BEGIN
          --
          -- First make sure its possible to delete this record.
          -- An exception will be raised if its not.
          --
          table_PKG.CHECK_REFRENCES(pkey1, pkey2, ...);
          --
          -- Since it is possible to delete the row, ask the
          -- user if they really want to, 
          -- and delete it if they respond with 'OK'.
          --
          app_record.delete_row;
        END key_delete;
      END block;
    
  3. Call the event handler:

    Trigger: KEY-DELETE:
    
    block.dey_delete;
    

    Tip: You should do similar steps again with the ON-DELETE trigger. It is possible that between the time a user requested the delete, and actually saved the transaction, a record was entered elsewhere that will cause referential integrity problems. Remember that KEY-DELETE fires in response to the user initiating a delete, but it does not actually perform the delete; it just flags the record to be deleted and clears it from the screen. The ON-DELETE trigger fires at commit time and actually performs the delete.

The Calendar

The Calendar is a standard object that allows selection of date and time values from a Calendar. It also allows the developer to specify validation rules ensuring that only valid dates can be selected. Both the List and Edit functions should invoke the Calendar on any date field.

For each date field within a form, you should provide the code necessary for the user to call the Calendar feature. However, the calendar is not a replacement for validating the data in the field.

The Calendar is automatically included in the TEMPLATE form.

For more information on the user interface standards for the Calendar, see the Oracle E-Business Suite User Interface Standards for Forms-Based Products.

LOV for Date and Datetime Fields

Date and datetime fields should enable the List lamp. When the user invokes List on these fields, the form opens the Calendar window.

Date fields should use the ENABLE_LIST_LAMP LOV, which is included in the TEMPLATE form. This setting enables the menu and Toolbar List of Values entries for your date fields. Set '"Validate from List" to No on fields that use this LOV. If you leave "Validate from List" set to Yes, you will see an LOV that has no columns.

Required Calls

Each date field within a form needs to have the following code:

Trigger: KEY-LISTVAL:
calendar.show([first_date]);

By default, the Calendar shows the month of the value in the date field (if a value exists) upon first opening. If no specific date is supplied, the Calendar shows the current month.

Do not pass the current field into CALENDAR.SHOW as a parameter, as this forces validation of the field. The current field is used as a default. Generally, the code in KEY-LISTVAL should be:

calendar.show;

Important: Never pass the value of the current date field as the argument to CALENDAR.SHOW. Because the calendar actually disables all Oracle Forms validation momentarily, if the user has entered an invalid date then immediately invokes the calendar, a PL/SQL error occurs. SHOW automatically handles this case when no argument is passed to it.

The KEY-LISTVAL trigger must have Execution Hierarchy "Override," and should not fire in enter-query mode.

See: CALENDAR: Calendar Package.

Display Only Mode

The entire calendar can be run in a display-only mode, where it is used to show one or more dates as Selected, rather than allowing the user to select a particular date. For example, it can be used to show all dates on which an employee was absent.

In this mode, characteristics of the field the cursor is on are ignored. All the user can do is change the month and year shown, and press 'OK' to close the window (no value is ever written back to the form).

To invoke this mode, the following calls are required in addition to those listed above:

Trigger: KEY-LISTVAL:
calendar.setup('DISPLAY');
calendar.setup('TITLE', null, null,
               '<translated text for window title>');

Additional CALENDAR.SETUP calls are required after these two calls to establish those dates that should be shown as selected.

Advanced Calendar Options

You can incorporate optional features into your Calendar call. If you use any of the optional calls, they must be placed before the mandatory calendar.show call.

The following examples customize the Calendar to show or disable specific dates.

Disable Weekends in the Calendar Window

To disable weekends (where the weekend is defined as Saturday and Sunday):

calendar.setup('WEEKEND');

Disable Specific Date Ranges

To disable specific date ranges where the dates are either hard-coded or references to other fields on the form:

calendar.setup(<30 char identifying name>, <low_date>,
  <high_date>);

This call can be repeated as many times as needed. A null LOW_DATE is treated as the beginning of time; a null HIGH_DATE is treated as the end of time.

Disable Specific Date Ranges From a Table

To disable specific date ranges where the dates are contained in a table:

calendar.setup(<30 char identifying name>, null, null, <SQL>);

This call may be made only once per field, but may return multiple rows. A null LOW_DATE is treated as the beginning of time; a null HIGH_DATE is treated as the end of time. Use NVL in your SQL statement if this is not the desired behavior.

Restrictions from several tables can be performed by using UNION SQL statements. The selected columns must be aliased to LOW_DATE and HIGH_DATE.

Tip: Ordering on the LOW_DATE column may improve performance. Restricting the dates returned to a small range near the anticipated selected value also improves performance.

Calling the Calendar from non-DATE fields

If you need to be able to activate the Calendar from a field that is not explicitly declared as a DATE or DATETIME field (such as a CHAR text item that serves multiple purposes depending on context), write the Calendar calls as normal. The Calendar acts as if invoked from a DATE field, and when the user selects a value the date is written back to the field in the format "DD-MON-YYYY."

Then user-named trigger CALENDAR_WROTE_DATE fires. Create that trigger at the item level, and add any code you need to process the value (typically you need to apply a mask to it).

Calendar Examples

Example - Weekdays Only

In this example, you want to open the Calendar to show either the date currently displayed in the DATE item, or the current month if no date is displayed. Additionally, you want to disable weekends (Saturdays and Sundays).

Trigger: KEY-LISTVAL:

calendar.setup('WEEKEND');
calendar.show;

Example - Only Include Workdays

In a form with the field SHIP_BY_DATE, you want to open the Calendar and customize it to:

The code to implement this is:

Trigger: KEY-LISTVAL:

calendar.setup('WEEKEND');
calendar.setup('Manufacturing Holidays', null, null, 
                 'select action_date LOW_DATE,
                 action_date HIGH_DATE '||
                 'from org_holidays where 
                 date_type = ''HOLIDAY''');
calendar.show(:lines.need_by_date);

Example - Range of Days Enabled

In a form with a field NEED_BY_DATE, you want the Calendar to show the month corresponding to the date in the field LINES.CREATED_DATE + 30 days. You also want to disable all dates before and including: LINES.CREATED_DATE.

The code to implement this is:

Trigger: KEY-LISTVAL:

calendar.setup('After created date', null,
                 lines.created_date);
calendar.show(:lines.need_by_date + 30);

Example - Display Only Calendar

A form uses a button called "Holidays" to show all Manufacturing holidays. The current month displays initially, and the calendar finds the selected dates in the ORG_DATES table.

The code to implement this is:

Trigger: WHEN-BUTTON-PRESSED on HOLIDAYS:

calendar.setup('TITLE', null, null,
          '<translated text for "Manufacturing Holidays">');
calendar.setup('Manufacturing Holidays', null, null, 
     'select action_date LOW_DATE, action_date HIGH_DATE '||
     'from  org_dates where date_type = ''HOLIDAY''');
calendar.show;

CALENDAR: Calendar Package

For standards and examples of coding calendars into your forms, see: The Calendar.

CALENDAR.SHOW

Summary

PROCEDURE show (first_date date default null);

Description

This call shows the calendar. Do not pass the current field value into show; this value is used by default.

CALENDAR.SETUP

Summary

PROCEDURE setup (new_type varchar2,
                 low_date date DEFAULT null,
                 high_date date DEFAULT null,
                 sql_string varchar2 DEFAULT null);

Important: The WEEKEND argument is hardcoded to mean Saturday and Sunday, so it is not applicable to all countries (such as countries where the weekend is defined as Friday and Saturday).

CALENDAR.EVENT

Summary

PROCEDURE event (event varchar2);