Check duplicate values in oracle forms multi record block

In Oracle form's multi record block sometimes it is required that user should not enter duplicate values in an item and a alert should warn user that he is trying to add duplicate values for that item before saving the record.

Let us take the following example of the multi record PHONE block in oracle form. PHONE block has 2 items phone_type (select type) and phone_number it is required. User should not be allowed to select the same values in the select list. See the following figure.

Oracle Forms Duplicate check image

Create following triggers in form

When-Validate-item – Create this trigger on that item against which you want to introduced duplicate check. In this case it would be Phone Type as we don’t want user to add same type more than once.

When-new-item-instance – There is an restriction on when-validate-item trigger that we cannot use navigation command on this i.e. go_item, go block, first_record, last_record etc. so we need to create when new item instance trigger ( form level or block level) and use go_item.

• Create control block (LOCAL) and a item CHECK_DUPLICATE
• Create an Alert called ALERT_STOP.


Write the following code on  When-Validate-Item Trigger on Phone type

BEGIN

  /* Setting flag value to Y */

  :LOCAL.CHECK_DUPLICATE := 'Y';

END;

Write following code on When-New-Item-Instance Trigger on form level or block level

DECLARE
 V_CURRENT_RECORD  NUMBER  := 0;
 V_LAST_RECORD     NUMBER  := 0;
 V_DATA            PHONES.PHO_TYPE%type;
 V_ALERT_NO        NUMBER  := 0;
BEGIN
  IF NVL(:LOCAL.CHECK_DUPLICATE,'N') = 'Y' THEN
   /* Setting Flag to N so that it would not get fire again until validated once    
      again */
   :LOCAL.CHECK_DUPLICATE := 'N';
   V_CURRENT_RECORD := TO_NUMBER(GET_BLOCK_PROPERTY('PHONES',CURRENT_RECORD));
   V_DATA           := :PHONES.PHO_TYPE;
   GO_BLOCK('PHONES');
   LAST_RECORD;
   V_LAST_RECORD    := TO_NUMBER(GET_BLOCK_PROPERTY('PHONES',CURRENT_RECORD));
   FIRST_RECORD;
   FOR V_INDEX IN 1..V_LAST_RECORD
   LOOP
    IF V_INDEX != V_CURRENT_RECORD AND
     V_DATA  = :PHONES.PHO_TYPE THEN
     go_record(v_current_record);
     set_alert_property('ALERT_STOP',ALERT_MESSAGE_TEXT,
 'Duplicate Telephone Type.');
   V_ALERT_NO := show_alert('ALERT_STOP');
     raise form_trigger_failure;     
     EXIT;
    END IF;
    NEXT_RECORD;
   END LOOP;   
  END IF;
END;

 

Author

Mantra IT Technical Team - Share and spread knowledge.


Disclaimer

All information and materials provided here are provided "as-is" by Mantra IT technical team based on their vast knowledge and experience. Mantra IT shall not be liable for any damages, including  direct, indirect, incidental, special or consequential damages for loss of profits, revenue, data or data use, incurred by you or any third party in connection with the use of this information or these materials. You may reach Mantra IT technical team by emailing on techhelp@mantrait.com for any further assistance.

replica watches designer replica watches replica watches master replica watches