How to Restrict or avoid Duplicate value in line level at run time in oracle apps custom form EBS R12.

Restrict or avoid Duplicate value in line level at run time in oracle apps custom form.

  

Step : 1

I have uses two calculated items, one in the data bock and another in a control block.

Step : 2

1^st Data Block : XXBEX_BLKLST_LINES

Added an non database ITEM as : MATCH_FOUND with following property

 

  

2^st Data Block : CTRL

Added an non database ITEM as : CHARSAVE  with following property

 

Added an non database ITEM as : MATCH_FOUND  with following property

Step 3 :

Create a function in the program unit stored in the Forms module as COMPARAISON.

  

--*************************--

Function COMPARAISON (val1 varchar2, val2 varchar2)

Return number is

                answer number := 0;

Begin

                 if val1 = val2 then

                                 answer := 1;

                 end if;

                 return(answer);

END;

--*************************--

Then call the function  in the 1^st Data Block : XXBEX_BLKLST_LINES  and Item MATCH_FOUND with following property formula. See the above picture.

Step 4 :  Then Restrict the code at Form level trigger : WHEN-VALIDATE-RECORD

And write the code:

--******************--

:CTRL.CHARSAVE := :XXBEX_BLKLST_LINES.FC_CURRENCY || :XXBEX_BLKLST_LINES.EXCHANGE_RATE;

If :CTRL.MATCH_FOUND > 1 then

   FND_MESSAGE.SET_STRING('This record is duplicated for currency '||:XXBEX_BLKLST_LINES.FC_CURRENCY);

   fnd_message.error;

   --FND_MESSAGE.SHOW;

   Raise form_trigger_failure ;

End if;

--******************--

The two values are compared to each other, then the function returns 1 (a value greatest than 0) if both the values are identical.

The first value (:ctrl.charsave) contains the bakup value of the current record.

The XXBEX_BLKLST_LINES  block must have the following properties setting:

Query all records

YES

 The CTRL block must have the following properties setting:

Query all records	YES
Single record	YES
Database data block	NO

The second calculated item (:CTRL.MATCH_FOUND) is added to the CTRL block.

It summarize the values contained in all the rows of the DEPT block (dept.match_found).

If the total is greater than 1, we have two duplicated data.