Performance Tip-- How to cache oracle apps valuesets in PLSQL

Problem: In consulting world, most of the business uses value sets to define name value pair values. Usually programmers pass the value set name and value to check or derive the values in loop for each record. How do we avoid re-quering the valueset table again and again.

Solution:  Please see the below example which will allow them to cache it locally in the collection table, which can be used anytime during the processing without going back to db.

set serveroutput on;
declare
      CURSOR c_get_value_set_info IS
        SELECT fv.flex_value,      
               fv.description flex_value_meaning
        FROM fnd_flex_values_vl fv, fnd_flex_value_sets fvs
          WHERE     fv.enabled_flag = 'Y'
            AND fvs.flex_value_set_id = fv.flex_value_set_id
            AND SYSDATE BETWEEN NVL (fv.start_date_active, SYSDATE - 1) AND NVL (fv.end_date_active, SYSDATE + 1)
            AND fvs.flex_value_set_name = 'XX_VALUE_SET_NAME';

   TYPE xx_valuest_rec IS RECORD
   (
     flex_value         VARCHAR2(250),
     flex_meaning       VARCHAR2(2000)
   );
 
   TYPE xx_valuest_rec_tbl IS TABLE OF xx_valuest_rec INDEX BY PLS_INTEGER;
 
     l_value_set_array xx_valuest_rec_tbl;
     l_status_message VARCHAR2(2000);
begin
     OPEN c_get_value_set_info;
     FETCH c_get_value_set_info BULK COLLECT into  l_value_set_array;
     CLOSE c_get_value_set_info;
   
       IF l_value_set_array.count > 0  THEN
         dbms_output.put_line('success:');

          -- to print all the elements
          for i in 1..l_value_set_array.count loop
            dbms_output.put_line('value:'||l_value_set_array(i).flex_value ||'    Meaning:'||l_value_set_array(i).flex_meaning);
          end loop;
       else
         dbms_output.put_line('failure: valueset not found');
        end if;

end;