[ERP] uses the built-in Object Record Group for repetitive Verification

Background description

In the process of developing Oracle forms, Uniqueness verification and repetitive verification are one of the most basic form verification functions. Generally, the uniqueness verification can meet the requirements of daily development by creating a unique index for the base table. However, there may inevitably be some special behavior and the method for requiring a unique index is still not fixed. For example, the values of the two fields in a record are taken from the same data source, however, they cannot repeat each other or between records.

Fortunately, developers also have many solutions to cope with special situations, such as using the built-in arrays of PL/SQL to store the values to be verified for each input, during verification, the values in all arrays are iterated for repeated verification. You can even use a string to access a single set of values to do the same thing, although I personally do not recommend this method ;).

To sum up, there are several possible types:

• Use PL/SQL arrays (Disadvantage: poor combination with form)
• Use strings (complex operations, not suitable for use, especially when multiple columns are verified)
• Use FIRST_RECORD, LOOP, and record on the cyclic interface (the speed is slow, the efficiency is low, and the user's visual experience is poor because the cursor is always moving ...)

;)

Record Group)

In our development practices, we found that a built-in array-like object Record Group has been provided in Oracle forms. I 'd like to mention it to everyone ~ Yes, what are the surprises? Isn't it often used when we create a LOV... However, Oracle also provides a series of built-in APIs to help us dynamically create, delete, and operate record groups and elements in record groups, and store multi-row data in two-dimensional form. Therefore, this record group object can be used programmatically.

Compared with other methods, I think the record group method has the following advantages:

1. Built-in Form support, which can be combined with Oracle forms
2. Provides concise and clear APIs for ease of use
3. The row number operation mechanism of the record group is exactly the same as that of the record number on the data block, facilitating the synchronization of records on the Interface
4. It is faster and more efficient than other built-in methods.

;)

API Overview

The following is a brief introduction of the API function definitions that can be used for verification. For detailed descriptions and documents, see the help document of Oracle Forms Builder.

--FUNCTION
CREATE_GROUP ();
--Create a non-query record group in memory based on the given name. The function returns the handle of the created record group. (The handle type is RecordGroup)

-- PROCEDURE
DELETE_GROUP ();
-- Delete A created record group from the memory.

--FUNCTION
ADD_GROUP_COLUMN ();
--Create columns of the specified type for the given record group (similar to the field attribute initialized when the database table is created). There are three types of columns: CHAR_COLUMN, DATE_COLUMN, and NUMBER_COLUMN.

-- PROCEDURE
ADD_GROUP_ROW ();
DELETE_GROUP_ROW ();
-- Creates/deletes a record row for the specified record group.

-- FUNCTION/PROCEDURE
SET_GROUP _ < XXX > _ CELL ();
GET_GROUP _ < XXX > _ CELL ();
-- Sets/obtains a field value on a specified record row in a specified record group. <XXX> there are three types: CHAR, DATE, and NUMBER.

-- FUNCTION
GET_GROUP_ROW_COUNT ();
-- Returns the number of rows in a given record group.

--FUNCTION
GET_GROUP_RECORD_NUMBER ();
--Returns the first row number in the record row that contains a specific value. If this value is not found in any record row, 0 is returned.

A simple example

The following is a simple example to illustrate how to use these Apis. I will not be able to use the image. By default, you are already familiar with form development ,:)

Assume that the form contains a multi-row data block REPLIST. Now we need to perform interface uniqueness verification on the REPORT_ID field of the data block.

1. Add the following code to the FORM-level trigger WHEN-NEW-FORM-INSTANCE:

DECLARE
Rg_nameVARCHAR2(30):= 'RG_REPORT_ID';
Rg_id RecordGroup;
Rc_id GroupColumn;
BEGIN
    --Check whether a record group with the same name has been created in the memory
Rg_id:=Find_Group (rg_name );

    --If yes, delete from memory
    IF NOTId_null (rg_id)THEN
Delete_Group (rg_name );
    END IF;

    --Create record group
Rg_id:=Create_Group ('RG_REPORT_ID');
    --Add columns to record groups
Rc_id:=Add_Group_Column (rg_id,'REPORT_ID', CHAR_COLUMN,100);
END;

--Switch to target data block
GO_BLOCK ('REPLIST');
--Execute Query
EXECUTE_QUERY;

2. Add the block-Level Trigger PRE-QUERY to the data block REPLIST with the following code:

DECLARE
Rg_nameVARCHAR2(30):=  'RG_REPORT_ID';
BEGIN
    --Delete all rows in the record group
Delete_Group_Row (rg_name, ALL_ROWS );
END;

3. Add the block-level trigger WHEN-REMOVE-RECORD to the data block REPLIST. The Code is as follows:

DECLARE
Rg_nameVARCHAR2(30):=  'RG_REPORT_ID';
BEGIN
    --When a record in a data block is deleted, the record in the corresponding record group also needs to be deleted.
Delete_Group_Row (rg_name,: system. trigger_record );
END

4. Add the block-level trigger WHEN-CREATE-RECORD to the data block REPLIST. The Code is as follows:

DECLARE
Rg_nameVARCHAR2(30):= 'RG_REPORT_ID';
BEGIN
    --When creating a record, you also want to add a corresponding row to the record group.
    --Note: This trigger will be called twice at the initial stage of form creation. Obviously, at that time
    --It is not when we create a record, so we use: system. block_status! = 'New' is used to filter out the two triggers.
    IF: System. block_status! = 'NEW' THEN
Add_Group_Row (rg_name,: system. trigger_record );
    END IF;
END;

5. Add the block-level trigger POST-QUERY to the data block REPLIST. The Code is as follows:

DECLARE
Rg_nameVARCHAR2(30):= 'RG_REPORT_ID';
BEGIN
    --Create a new line
Add_Group_Row (rg_name,: system. trigger_record );
    --Update Field Values for this row
Set_Group_Char_Cell (rg_name| '. REPORT_ID',: System. trigger_record,: replist. report_id );
END;

6. Add the ITEM-level trigger WHEN-VALIDATE-ITEM to the REPORT_ID ITEM of the data block REPLIST. The Code is as follows:

DECLARE
Rg_nameVARCHAR2(30):=  'RG_REPORT_ID';
N_row_number pls_integer;
BEGIN
    IF(: REPLIST. report_idIS NOT NULL)THEN

        --Check whether the current value is repeated. If yes, return the row number of the first record found.
N_row_number:=Get_Group_Record_Number (rg_name| '. REPORT_ID',: Replist. report_id );
        --If there are duplicates, but not itself, prompt information and terminate the current operation
        IFN_row_number> 0 ANDN_row_number! =: System. trigger_recordTHEN
        
FND_MESSAGE.DEBUG ('Duplicated!');
RAISE form_trigger_failure;
                
        ELSE                
            --If no duplicate is found, the value is updated in the record group.
Set_Group_Char_Cell (rg_name| '. REPORT_ID',: System. triiger_record,: replist. report_id );

        END IF;
                
    END IF;
END;

OK. The verification part on the interface has come to an end. In addition, we also need to perform full verification at the interface and database layers, because not all records are displayed on the interface.

 

Package zz_record_group

Although the built-in APIs are simple, some operations need to be done before each API call to simplify the API and verify, which is very troublesome, therefore, I simply wrote a general package ZZ_RECORD_GROUP that is easy to use for students (including myself). This package has the following features:

1. Encapsulate the creation and deletion of record rows to simplify operations
2. Provides more convenient VALUE_EXISTS functions for repeated value search, which are reloaded three times for the character type, number type, and date type.
3. Change the cell modification operation to set_value for ease of use.
4. Released in the form of library. attach directly to the form on the local machine for use.

I have released it to github. If you are interested, you can check it out ;)

Transport: https://github.com/eliu/zz_record_group

Thanks for the following articles, Thanks!

Http://sites.google.com/site/craigsoraclestuff/oracle-forms---how-to-s/oracle-forms

Enjoy!