Create instance independent Custom Object SQL queries in Oracle Fusion SaaS

Business Need

Oracle Fusion SaaS provides a very powerful feature of Custom Objects. The feature allows creation of fields, triggers, actions and pages by using the Application Composer.

Every Custom Objects has its own API name allowing performing CRUD operations on its records.

When a new Custom Object is created, the system decides which extension table will be used and will map logical names of fields to specific columns in an extension table. The mapping of table and columns may change if fields are deleted and created again even if they have same API name of the field.

Oracle Fusion SaaS allows writing direct SQL queries against the fusion database (ESS Job parameter sources, Flexfield value Sets, BIP Reports) and if the mapping changes – those queries should be updated.

Instance Independent Solution

The solution is based on the following abilities:

• The actual mapping of Custom Object tables and fields is stored in Import definition tables: MKT_IMP_OBJECT_DETAILS, MKT_IMP_OBJECT_ATTRS
  The following query will fetch all Custom Object fields and their associated tables and physical columns:
  
  SELECT OBJECT_ATTR_NAME AS ATRRIBUTE, TXN_VO_ATTR_TABLE tab_name, TXN_VO_ATTR_COL col_name
FROM FUSION.MKT_IMP_OBJECT_DETAILS D, FUSION.MKT_IMP_OBJECT_ATTRS A
WHERE D.OBJECT_DETAIL_ID=A.OBJECT_DETAIL_ID
AND A.OBJECT_ATTR_COL LIKE '%EXTN_ATTRIBUTE%' AND A.CREATED_BY != 'SEED_DATA_FROM_APPLICATION'
AND D.OBJECT_DETAIL_TABLE != 'MKT_IMP_JOBS'
AND OBJECT_NAME LIKE :p_object || '%'
  
• LISTAGG SQL function to generate dynamic SQL based on mapping
  Create an outer SQL for the statement above to generate a valid SQL statement:
  
  select ' || listagg(col_name || ' ' || ATRRIBUTE, ', ') || ' from ' || max(tab_name) from (<query above>)
  
• dbms_xmlgen.getxmltype – generation of XMLType result based on a query
  Invoke dynamically the query generated in a previous step:
  
  select dbms_xmlgen.getxmltype('select ' || listagg(col_name || ' ' || ATRRIBUTE, ', ') || ' from ' || max(tab_name) || ' where attribute_category = ''' || :p_object || ''' ') dt from (
SELECT OBJECT_ATTR_NAME AS ATRRIBUTE, TXN_VO_ATTR_TABLE tab_name, TXN_VO_ATTR_COL col_name
FROM FUSION.MKT_IMP_OBJECT_DETAILS D, FUSION.MKT_IMP_OBJECT_ATTRS A
WHERE D.OBJECT_DETAIL_ID=A.OBJECT_DETAIL_ID
AND A.OBJECT_ATTR_COL LIKE '%EXTN_ATTRIBUTE%' AND A.CREATED_BY != 'SEED_DATA_FROM_APPLICATION'
AND D.OBJECT_DETAIL_TABLE != 'MKT_IMP_JOBS'
AND D.OBJECT_ATTR_NAME in ('Field Api Name 1', 'Field Api Name 2')
AND OBJECT_NAME LIKE :p_object || '%'
)
• XMLTable function – parsing the XMLType and returning regular SQL columns
  All the steps above are generic and can be used with any deployed Custom Object. The final part depends on actual fields from Custom Object we want to fetch.
  So if we have for example 2 custom fields: Name_c and Description_c the final part will look as below:
  
  XMLTABLE('/ROWSET/ROW'
PASSING q.dt
COLUMNS
"NAME_C" varchar2(50) PATH 'NAME_C',
"DESCRIPTION_C" varchar2(250) PATH 'DESCRIPTION_C'
)
• Now you can move the query and it will not break as long as Custom Object API name and fields API names are in tact !