Oracle Apps: Setting NULL value for DFF segment on FND_LOOKUP

This blog is about setting NULL value for DFF segment on FND_LOOKUP from lookup form or using API.

Assume we have a DFF enabled on FND Lookup and the segment already have a value. Now we need to clear the value of that segment. What we normally do is open the Lookup and open the DFF values and delete the value from the Form for that segment and save the record. This will not clear the value, if you open the lookup again, you can still see the old value.

The reason for this is because, the underlying API uses a decode statement in the update procedure, which overides the NULL value with old value.

code snippet from package spec FND_LOOKUP_VALUES_PKG

 null_char varchar2(8) := '*NULL*';  

code snippet from package body FND_LOOKUP_VALUES_PKG

 l_null varchar2(20) := fnd_lookup_values_pkg.null_char;  

 update FND_LOOKUP_VALUES A  
   set  
   A.TAG = X_TAG,  
   A.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,  
   A.ATTRIBUTE1 = decode(x_attribute1,l_null,null, null,A.attribute1, x_attribute1) ,  
   A.ATTRIBUTE2 = decode(x_attribute2,l_null,null, null,A.attribute2, x_attribute2) ,  
   A.ATTRIBUTE3 = decode(x_attribute3,l_null,null, null,A.attribute3, x_attribute3) ,  
   A.ATTRIBUTE4 = decode(x_attribute4,l_null,null, null,A.attribute4, x_attribute4) ,  
   A.ATTRIBUTE5 = decode(x_attribute5,l_null,null, null,A.attribute5, x_attribute5) ,  
   A.ATTRIBUTE6 = decode(x_attribute6,l_null,null, null,A.attribute6, x_attribute6) ,  
   A.ATTRIBUTE7 = decode(x_attribute7,l_null,null, null,A.attribute7, x_attribute7) ,  
   A.ATTRIBUTE8 = decode(x_attribute8,l_null,null, null,A.attribute8, x_attribute8) ,  
   A.ATTRIBUTE9 = decode(x_attribute9,l_null,null, null,A.attribute9, x_attribute9) ,  
   A.ATTRIBUTE10 = decode(x_attribute10,l_null,null, null,A.attribute10, x_attribute10) ,  
   A.ATTRIBUTE11 = decode(x_attribute11,l_null,null, null,A.attribute11, x_attribute11) ,  
   A.ATTRIBUTE12 = decode(x_attribute12,l_null,null, null,A.attribute12, x_attribute12) ,  
   A.ATTRIBUTE13 = decode(x_attribute13,l_null,null, null,A.attribute13, x_attribute13) ,  
   A.ATTRIBUTE14 = decode(x_attribute14,l_null,null, null,A.attribute14, x_attribute14) ,  
   A.ATTRIBUTE15 = decode(x_attribute15,l_null,null, null,A.attribute15, x_attribute15) ,  
   A.ENABLED_FLAG = X_ENABLED_FLAG,  
   A.START_DATE_ACTIVE = X_START_DATE_ACTIVE,  
   A.END_DATE_ACTIVE = X_END_DATE_ACTIVE,  
   A.TERRITORY_CODE = X_TERRITORY_CODE,  
   A.LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,  
   A.LAST_UPDATED_BY = X_LAST_UPDATED_BY,  
   A.LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN  
  where A.LOOKUP_TYPE = X_LOOKUP_TYPE  
  and A.SECURITY_GROUP_ID = sgid  
  and A.VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID  
  and A.LOOKUP_CODE = X_LOOKUP_CODE;  

Explanation:

A.ATTRIBUTE1 = decode(x_attribute1,l_null,null, null,A.attribute1, x_attribute1)

If the new value is *NULL*, then set the value to null
If the new value is null, then set the value to old value
Else set the new value

Solution :

So to clear the DFF segment value on FND Lookup from the form, use the string *NULL*, instead of deleting the value.

Feel free to point out if anything is missing/wrong in this blog.