Determine a build option's value dynamically without calling the apex_util.get_build_option_status() API

Sometimes you want to return a different value from or filter the results of a report or plug-in query based on an application's current build option value. Usually developers just call the apex_util.get_build_option_status() API to get the build option's current value. I'm here to tell you there is a better way.

Let's say your application has a build option named, "Show Computed Amounts" and this build option (when enabled) displays the sum of the cost of several items/products belonging to any given customer. When the build option is disabled, end-users can only assign an overall "estimated value" to the customer and the items/products assigned to that customer no longer display their individual costs in reports or forms. Typically an APEX developer would handle this in a query like this:

    select
        case when apex_util.get_build_option_status(
                      p_application_id    => :APP_ID,
                      p_build_option_name => 'Show Computed Amounts'
                  ) = 'INCLUDE' then
            (select to_char(sum(product_price),'999G999G999G999G999G999G990')
               from cust_products cp
              where cp.cust_id = c.id)
        else
            to_char(nvl(c.estimated_value, 0), '999G999G999G999G999G999G990')
        end amount
    from
        customers c
    where
        c.id = :P100_CUSTOMER_ID;

The query above will return the expected results but it is not efficient because the apex_util.get_build_option_status() API call is issuing another query to get the build option's current value.

A more efficient query would be:

    select
        case when :SHOW_COMP_AMTS_BO = 'Include' then
            (select to_char(sum(product_price),'999G999G999G999G999G999G990')
               from cust_products cp
              where cp.cust_id = c.id)
        else
            to_char(nvl(c.estimated_value, 0), '999G999G999G999G999G999G990')
        end amount
    from
        customers c
    where
        c.id = :P100_CUSTOMER_ID;

Here are the steps to make the more efficient query a reality.

1. Create an Application Item called, "SHOW_COMP_AMTS_BO"
   
   its "Session State Protection" should be set to, "Restricted - May not be set from browser" and there should be no build option value set on this item.
   
   
2. Create another Application Item called, "SHOW_COMP_AMTS_BO_NAME"
   
   its "Session State Protection" should also be set to, "Restricted - May not be set from browser" and there should be no build option value set on this item.
   
   
3. Create an Application Computation for the "SHOW_COMP_AMTS_BO_NAME" Application Item
   
   its "Computation Point" should be set to, "On New Instance (new session)", its "Computation Type" should be set to "Static Assignment", its "Computation" should be set to, "Show Computed Amounts" and there should be no build option value set on this computation.
   
   
4. Create an Application Computation for the "SHOW_COMP_AMTS_BO" Application Item
   
   its "Computation Point" should be set to, "On New Instance (new session)", its "Computation Type" should be set to "Static Assignment", its "Computation" should be set to, "Include" and its build option value should be set to "Show Computed Amounts".
   
   
5. Create another Application Computation for the "SHOW_COMP_AMTS_BO" Application Item
   
   its "Computation Point" should be set to, "On New Instance (new session)", its "Computation Type" should be set to "Static Assignment", its "Computation" should be set to, "Exclude" and its build option value should be set to "{Not Show Computed Amounts}".

The last piece of the puzzle is to ensure your application's administrative Build Options page will update the new "SHOW_COMP_AMTS_BO" Application Item's value accordingly (if an app administrator decides to change it). To do this, change the PL/SQL in the update process of your application's administrative Build Options page from this:

    for i in 1..apex_application.g_f01.count loop

        for c1 in ( select application_id, build_option_name, build_option_status

                    from apex_application_build_options

                    where apex_application.g_f01(i) = build_option_id

                       and application_id = :APP_ID)

        loop

            if c1.build_option_status != apex_application.g_f03(i) then

                apex_util.set_build_option_status(  p_application_id => :APP_ID,

                                                    p_id => apex_application.g_f01(i),

                                                    p_build_status => upper(apex_application.g_f03(i)) );

            end if;

        end loop;

    end loop;

to this:

    for i in 1..apex_application.g_f01.count loop

        for c1 in ( select application_id, build_option_name, build_option_status

                    from apex_application_build_options

                    where apex_application.g_f01(i) = build_option_id

                       and application_id = :APP_ID)

        loop

            if c1.build_option_status != apex_application.g_f03(i) then

                apex_util.set_build_option_status(  p_application_id => :APP_ID,

                                                    p_id => apex_application.g_f01(i),

                                                    p_build_status => upper(apex_application.g_f03(i)) );

                if c1.build_option_name = :SHOW_COMP_AMTS_BO_NAME then

                    :SHOW_COMP_AMTS_BO := apex_application.g_f03(i);

                end if;

            end if;

        end loop;

    end loop;

That's it. Now your application can dynamically determine the "Show Computed Amounts" current build option value in any report or plug-in query without the overhead of re-querying the apex_application_build_options view via an apex_util.get_build_option_status() API call.