Tuesday, 9 August 2016

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


  

Step : 1
I have uses two calculated items, one in the data bock and another in a control block.
Step : 2
1st Data Block : XXBEX_BLKLST_LINES
Added an non database ITEM as : MATCH_FOUND with following property

 


  

2st 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 1st 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.
 


No comments:

Post a Comment

How to change Employee Number Generation from Manual to Automatic in Oracle HRMS (When attempting to apply for a job in iRecruitment)

When attempting to apply for a job in iRecruitment, the following error occurs: ERROR: You must enter an Application Number. Solution: How t...