Fusion SaaS BIP: Utilize lexical feature to improve Custom Object SQL access

General

Custom Objects in Fusion SaaS are very useful feature allowing customers to add their own data and use it together with existing Oracle provided seeded tables.
In the past we have published a solution that allows writing environment independent SQL based BIP reports that can be easily moved between environments.
The method is described here https://cidsolutions.co.il/2023/11/15/improved-method-for-writing-instance-independent-sql-queries-over-custom-objects-in-fusion-saas/ .
The solution above utilizes XML Query feature of Oracle database.
This post will show even more efficient approach using lexical feature of BI Publisher.
The new approach uses native SQL without XMLQuery parsing and much more efficient in performance.
There are still places like bursting that we can not use the new proposed approach and should still use the XML Query feature.

Thanks to @serhii (Serhii Kononov) for contributing to the solution.

New Lexical Solution

1. The solution is based on creating a metadata query returning SQL statements for every custom object or custom fields mapping in standard table..
   The query below contains 3 parts:
   • custom_object WITH section – defines custom objects and standard tables with custom objects to be used
   • codes WITH section is a generic section that can be copied into every new usage
   • pivot section provides a separate lexical variable to for every object to be used later in data access queries
2. Query Example:
   

   with custom_objects as (
           select 'ACSFDCQuotes_c'       custom_object, 'Currency_code_c,Delivery_terms_c,Expiration_date_c' columns, 'ID,RECORD_NAME'      std_fields from dual union all
           select 'SFDCQuoteLines_c'     custom_object, 'ACSFDCQuotes_Id_c,Discount%'                        columns, null                  std_fields from dual union all
           select 'OSS_COVERAGE_HEADERS' custom_object, 'CoverageType_c'                                     columns, 'COVERAGE_HEADER_ID'  std_fields from dual
        ),
        codes as (
           select co.custom_object custom_object,
                  code
           from   custom_objects co,
                  adf_extensible_table_usage tu,
                  lateral(
                    select 'select ' ||
                           any_value(co.std_fields) || case when any_value(co.std_fields) is not null and any_value(co.std_fields) not like '%,' then ', ' end ||
                           listagg(def.column_name || ' ' || def.attribute_name, ',') within group (order by def.attribute_name) ||
                           ' from '  || any_value(def.table_name) ||
                           case when any_value(tu.context_column_value) is not null then ' where attribute_category = ''' || any_value(tu.context_column_value) || '''' end code
                    from (
                       select c.column_name, cu.attribute_name, t.table_name
                       from   adf_extensible_table       t,
                              adf_extension_column       c,
                              adf_extension_column_usage cu
                       where  (t.table_name = co.custom_object or cu.table_usage_id = tu.usage_id)
                              and c.table_id = t.table_id
                              and cu.column_id = c.column_id
                              and t.sandbox_name is null
                              and c.sandbox_name is null
                              and exists (
                                   select 1
                                   from (
                                     select regexp_substr(co.columns, '[^,]+', 1, level) requested_column_mask from dual connect by regexp_substr(co.columns, '[^,]+', 1, level) is not null
                                   ) where  cu.attribute_name like requested_column_mask
                                  )
                    ) def
                   )
           where tu.context_column_value (+)= co.custom_object
       )
   select *
   from (
     select custom_object custom_object_name, code
     from   codes
   )
   pivot (
       any_value(code) for
         custom_object_name in (
           'ACSFDCQuotes_c'       as "ACSFDCQuotes_code",
           'SFDCQuoteLines_c'     as "SFDCQuoteLines_code",
           'OSS_COVERAGE_HEADERS' as "OSS_COVERAGE_HEADERS_code"
         )
   )
   

3. The query above should be first query and connected to the DATA query by a group link
4. In order to continue with the model development – run the model and copy generated values for every custom object field generated in the previous step (in our example – 3 SQL statements)
   
5. You can create now SQL DATA query dataset and use every custom object as if it was a regular view/table like this (&ACSFDCQuotes_code) or (&OSS_COVERAGE_HEADERS_code).
   Upon saving the query BIP Model editor will prompt you to add the default value for lexical variables – paste the values saved in the previous step – it will generate the columns properly