Improved method for writing instance independent SQL queries over custom objects in Fusion SaaS

Business Need

In previous post https://cidsolutions.co.il/2023/07/18/create-instance-independent-custom-object-sql-queries-in-oracle-fusion-saas/ we described the method to overcome the issue of different mapping between logical custom field name to its mapped table and underlying column.

The solution described in the previous post was based on Import Objects setup and had the following drawbacks:

1. Not all objects were covered (Installed Base Assets for example)
2. The Import Object setup is available only after publishing the custom object changes and field object/field creations are not reversible once published.

Modified Solution

The new approach solves both issues above. it covers all extensible standard objects and all custom objects and its setup is available immediately after save, even inside the sandbox.

select cu.sandbox_name,
       tu.context_column_value object_name,
       t.table_name, 
       t.context_column_name,
       attribute_name,  
       c.column_name
from   adf_extension_column_usage cu, 
       adf_extension_column c, 
       adf_extensible_table t, 
       adf_extensible_table_usage tu
where  cu.column_id = c.column_id
       and c.table_id = t.table_id
       and cu.table_usage_id = tu.usage_id
       and tu.context_column_value like :p_object_name

The example below shows the usage of modified version to fetch custom object information for every sales order line.
We use LATERAL operator to ensure proper performance:

select l.line_id, m.*
from   doo_lines_all l,
       lateral(
        select r.* 
        from   (
                select dbms_xmlgen.getxmltype(
                        'select id,' || listagg(column_name || ' ' || attribute_name, ', ') ||
                        ' from ' || max(table_name) ||
                        ' where ' || max(context_column_name) || ' = ''' || max(object_name) || ''' and ' || max(order_line_id) || ' = ' || max(l.line_id)) dt 
                from 
                 (
                  select cu.sandbox_name,
                         tu.context_column_value object_name,
                         t.table_name, 
                         t.context_column_name,
                         attribute_name,  
                         c.column_name,
                         case when attribute_name = 'OrderLineId_c' then c.column_name end order_line_id
                  from   adf_extension_column_usage cu, 
                         adf_extension_column c, 
                         adf_extensible_table t, 
                         adf_extensible_table_usage tu
                  where  cu.column_id = c.column_id
                         and c.table_id = t.table_id
                         and cu.table_usage_id = tu.usage_id
                         and tu.context_column_value like 'OSSProductReferences_c'
                 )
               ) q,
               xmltable(
                 '/ROWSET/ROW' passing q.dt
                    columns 
                       "RECORD_ID"        varchar2(250) path 'ID',
                       "OSSPUID_C"        varchar2(250) path 'OSSPUID_C',
                       "ORDERLINEID_C"    number        path 'ORDERLINEID_C',
                       "AMENDEDFLAG_C"    varchar2(1)   path 'AMENDEDFLAG_C'
              ) r
        where 1=1     
       ) (+) m
where 1=1