Document-ID:        33342.1
Subject:            SEQUENCING LINES IN A MULTI-LINE BLOCK IN ORACLE FORMS
Author:             FSHAARAN



           Sequencing Lines In A Multi-Line Block In Oracle Forms
           ======================================================

Introduction:
-------------
This bulletin summarizes the code and logic needed to resequence the
lines of a multi-line block when records are deleted from it or added
to it.  This logic can be used in forms that deal with inventory
detail blocks or multi-line text blocks.

Example:
--------
Suppose you have an application that allows the user to create
invoices.  The master block is the invoice header, and the details
block is the invoice detail.  For every invoice header, you sequence
the invoice detail lines in the detail block, starting with the number 1.
You also need to add invoice lines or delete invoice lines that call
for resequencing the existing invoice line numbers.  This bulletin
outlines the process for any situation that calls for sequencing and
resequencing detail multi-line blocks.

Call the procedure seqno_prc from the When-New-Record-Instance trigger
and the Key-DELREC triggers on the block level of the multi-record
block.  This is the level where the delete or the insert of a record
is taking place.

seqno_prc procedure:
--------------------
The procedure seqno_prc takes two parameters:
   1.  Block name of the current multi record block
   2.  Field name that needs to be resequenced in that block

PROCEDURE seqno_prc(p_sourceblock IN VARCHAR2, p_sourcefield IN VARCHAR2) IS
   v_currec     NUMBER(4);
   v_curfld     VARCHAR2(50);
BEGIN
   v_currec := :SYSTEM.CURSOR_RECORD;
   v_curfld := :SYSTEM.CURSOR_FIELD;
   LOOP
      COPY(:SYSTEM.CURSOR_RECORD, p_sourceblock || '.' || p_sourcefield);
      IF :SYSTEM.LAST_RECORD = 'TRUE' THEN
         EXIT;
      END IF;
         NEXT_RECORD;
   END LOOP;
   GO_RECORD(v_currec);
   GO_FIELD(v_curfld);
END;

When-New-Record-Instance trigger:
---------------------------------
Create a When-New-Record-Instance trigger with the following code:

BEGIN
   If :SYSTEM.RECORD_STATUS('NEW') THEN
     seqno_prc('block_name','sequence_item_name');
   END IF;
END;

If insert is allowed, you can add new records to the multi-record
block and resequence the number of lines.

Key-DELREC trigger:
-------------------
Create a Key-DELREC TRIGGER with the following code:

DELETE_RECORD;
IF GET_BLOCK_PROPERTY('block_name',DELETE_ALLOWED) = 'TRUE' THEN
   seqno_prc('block_name','sequence_item_name');
END IF;

If delete is allowed, this trigger deletes a record from the
multi-record block and resequences the number of text lines.

Variations:
-----------
1.  If you put the seqno_prc procedure in a Forms library to be shared
by many forms, use the NAME_IN function on all references to the
SYSTEM.XXX built-ins used in the seqno_prc procedure:

PROCEDURE seqno_prc(p_sourceblock IN VARCHAR2, p_sourcefield IN VARCHAR2) IS
   v_currec    NUMBER(4);
   v_curfld    VARCHAR2(50);
BEGIN
   v_currec := NAME_IN('SYSTEM.CURSOR_RECORD');
   v_curfld := NAME_IN('SYSTEM.CURSOR_FIELD');
   LOOP
      COPY(NAME_IN('SYSTEM.CURSOR_RECORD'), p_sourceblock || '.' ||
          p_sourcefield);
      IF NAME_IN('SYSTEM.LAST_RECORD') = 'TRUE' THEN
          EXIT;
      END IF;
      NEXT_RECORD;
   END LOOP;
   GO_RECORD(v_currec);
   GO_FIELD(v_curfld);
END;

2.  Suppose you have a required field that the cursor first navigates
to when it moves to the block.  Next, on the Pre-Insert trigger, turn
off the required attribute of that item before calling the seqno_prc
procedure.  The seqno_prc procedure must be able to move your cursor
out of the current required item.  After the call to the procedure,
turn on the required attribute of the item.  Below is an example of
the code you need to do this:

SET_ITEM_PROPERTY('required_item', REQUIRED, PROPERTY_OFF);
seqno_prc('block_name', 'sequence_item_name');
SET_ITEM_PROPERTY('required_item', REQUIRED, PROPERTY_ON);