This section discusses

• Duplicating Records

• Renumbering All Records in a Window

Why Duplicate Record is Disabled by Default

By default, duplicate record is disabled at the form level. There are several reasons for this:

• The special column ROW_ID is duplicated and must be manually exempted if it exists

• The record is marked as valid even through the items may contain time-sensitive data that is no longer valid

• Defaults are overridden

• In many blocks, Duplicate Record makes no sense (modal dialogs, find blocks, etc.)

For any block where you want to enable Duplicate Record, you must write code. You must process unique keys, possibly reapply defaults, and confirm that copied data is still valid. None of this is done by default, and this can lead to errors or data corruption.

In general, duplicate all item values, even if the item value must be unique. The user may wish to create a unique value very similar to the previous value.

Do not override a default if

• The item cannot be modified by the user

• The item must contain a specific value for a new record

• The item is a sequential number and the default is the correct value most of the time

Example

A block order has items order_number and order_date which are defaulted from the sequence order_S and from SYSDATE respectively, and which cannot be modified by the user. The item status should contain "Open" for a new order, but the user can change the Status to "Book" at any time to book the order.

1. Create your event handler procedures as follows:

   PACKAGE BODY order IS
      PROCEDURE KEY_DUPREC IS
      CURSOR new_order_number IS SELECT order_S.nextval
                                  FROM sys.dual;
     BEGIN
       DUPLICATE_RECORD;
       open new_order_number;
       fetch new_order_number into :order.order_number;
       close new_order_number;
       :order.status : = 'Open';
       :order.order_date := FND_STANDARD.SYSTEM_DATE;
       :order.row_id := null;
     END KEY_DUPREC;
   END order;
   

2. Call your event handler procedures in:

   Trigger: KEY-DUPREC on order:

   order.KEY_DUPREC;
   

To renumber an item sequentially for all records on the block, create a user-named trigger to increment the sequence variable and set the sequence item. Use the procedure APP_RECORD.FOR_ALL_ RECORDS to fire the trigger once for each record.

To number an item sequentially as records are created, create a variable or item to contain the sequence number. Create a WHEN-CREATE- RECORD trigger to increment the sequence variable and default the sequence item. However, if you want to renumber all the records in a window, use the procedure APP_RECORD.FOR_ALL_RECORDS.

If you are renumbering your items after a query or commit, you may wish to reset the record status so that the record is not marked as changed.

Example

A block lines has item line_number. When a record is deleted, line_number must be renumbered.

1. Create your item handler procedures as follows:

    PACKAGE BODY lines IS
        line_number_seq number := 0;
        PROCEDURE delete_row IS
        BEGIN
          line_number_seq := 0;
          APP_RECORD.FOR_ALL_RECORDS('reseq_line_number');
        END delete_row;
      END lines;
   

2. Create a user-defined trigger RESEQ_LINE_NUMBER as follows:

   lines.line_number_seq := lines.line_number_seq + 1;
   :lines.line_number := lines.line_number_seq;
   

3. Call your item handler procedures in:

   Trigger: KEY-DELETE:

   lines.line_number('KEY-DELETE');

   Warning: Be aware of the consequences of this type of processing. Specifically, consider the following points:

   If a very large number of records are queried in a block, looping through them one at a time can be very slow.

   Not all the records that could be in the block may be in the current query set if you allow the user to enter the query.

   If you are changing a value that is part of a unique key, you may get errors at commit time because the record currently being committed may conflict with another already in the database, even though that record has also been changed in the block.