Dynamically Changing the VO query in OAF

Generally while creating the View Objects we define a query to it. While rendering the OAF page these view objects get executed (either by framework or by the java code).
In some scenarios we might have a requirement to change the query of the VO, this query can be modified dynamically.

Example: Lets say a VO(EMPVO) has the query as
SELECT empno,ename,sal
FROM EMP

Now I want to change the query as below

SELECT empno,ename,sal+nvl(comm,0)
FROM EMP

Sample code:

String query = “SELECT empno,ename,sal+nvl(comm,0) FROM EMP”;
try
{
OAApplicationModule oam = oapagecontext.getApplicationModule(oawebbean);
// get the handle for your view object
EMPVOImpl voimpl = (EMPVOImpl)oam.findViewObject(“EMPVO”);   
voimpl.setFullSqlMode(voimpl.FULLSQL_MODE_AUGMENTATION);
voimpl.setQuery(query);
// setQuery only sets the new query to the View Object, in order to effect the changes of the query we need to execute the equery using below statement.
voimpl.executeQuery();
}
catch (Exception e)
{
}


Always need to call setFullSqlMode(voimpl.FULLSQL_MODE_AUGMENTATION) before executing the query, If you won’t call this OA Framework will not append the where clause and Order by clauses correctly.
The above code is written in controller, you can write the code in either processRequest method or in processFormRequest based on your requirement or in AM.

API to Create Lookup Values - "FND_LOOKUP_VALUES"

Note: 

Before you start running the Below code, Kindly note we have only inserted records for Language as "US". If one has dual language, write a nested Loop to insert the same.

DECLARE

   CURSOR get_lookup_details

   IS

      SELECT   ltype.application_id,

               ltype.customization_level,

               ltype.creation_date,

               ltype.created_by,

               ltype.last_update_date,

               ltype.last_updated_by,

               ltype.last_update_login,

               tl.lookup_type,

               tl.security_group_id,

               tl.view_application_id,

               tl.description,

               tl.meaning

        FROM   fnd_lookup_types_tl tl, fnd_lookup_types ltype

       WHERE       ltype.lookup_type = 'XX_COUNTRY'

               AND ltype.lookup_type = tl.lookup_type

               AND language = 'US';

   CURSOR get_country

   IS

      SELECT   UPPER (country_name) country FROM xx_country;

   l_rowid   VARCHAR2 (100) := 0;

BEGIN

   FOR i IN get_lookup_details

   LOOP

      FOR j IN get_country

      LOOP

         l_rowid := NULL;

         BEGIN

            fnd_lookup_values_pkg.insert_row (

               x_rowid                 => l_rowid,

               x_lookup_type           => i.lookup_type,

               x_security_group_id     => i.security_group_id,

               x_view_application_id   => i.view_application_id,

               x_lookup_code           => j.country,

               x_tag                   => NULL,

               x_attribute_category    => NULL,

               x_attribute1            => NULL,

               x_attribute2            => NULL,

               x_attribute3            => NULL,

               x_attribute4            => NULL,

               x_enabled_flag          => 'Y',

               x_start_date_active     => TO_DATE ('01-JAN-1950',

                                                   'DD-MON-YYYY'),

               x_end_date_active       => NULL,

               x_territory_code        => NULL,

               x_attribute5            => NULL,

               x_attribute6            => NULL,

               x_attribute7            => NULL,

               x_attribute8            => NULL,

               x_attribute9            => NULL,

               x_attribute10           => NULL,

               x_attribute11           => NULL,

               x_attribute12           => NULL,

               x_attribute13           => NULL,

               x_attribute14           => NULL,

               x_attribute15           => NULL,

               x_meaning               => j.country,

               x_description           => NULL,

               x_creation_date         => SYSDATE,

               x_created_by            => i.created_by,

               x_last_update_date      => i.last_update_date,

               x_last_updated_by       => i.last_updated_by,

               x_last_update_login     => i.last_update_login

            );

            COMMIT;

            DBMS_OUTPUT.put_line (j.country || ' has been loaded');

         EXCEPTION

            WHEN OTHERS

            THEN

               DBMS_OUTPUT.put_line ('Inner Exception: ' || SQLERRM);

         END;

      END LOOP;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);

END;