Custom APEX Help PLSQL

APEX_CUSTOM_ITEM_HELP.sql

CREATE OR REPLACE PACKAGE BODY DEMO.CN_CUSTOM_HELP_PK AS
  
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
FUNCTION get_page_item_help_key
  (p_page_item_id IN apex_application_page_items.item_id%TYPE) RETURN custom_apex_help_text.help_key%TYPE IS

  l_help_key      apex_application_page_items.item_help_text%TYPE;

BEGIN

  -- Fetch the item help text for the page item. This contains the help text
  --   key which we will use to lookup the custom help text e.g. ORDER_NUMBER.
  BEGIN
    SELECT item_help_text INTO l_help_key
    FROM   apex_application_page_items
    WHERE  item_id = p_page_item_id;
  EXCEPTION WHEN NO_DATA_FOUND THEN
    -- Failover to Help Key that will explain that the page item was not found.
    l_help_key := 'PAGE_ITEM_NOT_FOUND';
  END;

  RETURN l_help_key;

END get_page_item_help_key;

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
FUNCTION get_custom_help
  (p_help_key IN custom_apex_help_text.help_key%TYPE) RETURN CLOB IS

  l_current_lang  VARCHAR(10);
  l_help_text     custom_apex_help_text.help_text%TYPE;
  l_app_id        apex_applications.application_id%TYPE := V('APP_ID');

BEGIN

  -- Get the current session language.
  l_current_lang := APEX_UTIL.GET_SESSION_LANG;
  -- If the session language was not set, use the App Primary Language.
  IF l_current_lang IS NULL THEN
    SELECT application_primary_language INTO l_current_lang
    FROM   apex_applications
    WHERE  application_id = l_app_id;
  END IF;

  -- Fetch the custom help text.
  BEGIN
    SELECT help_text INTO l_help_text
    FROM   custom_apex_help_text
    WHERE  help_key      = p_help_key
    AND    language_code = l_current_lang;
  EXCEPTION WHEN NO_DATA_FOUND THEN
    -- If there is no help text, then show a failover message.
    l_help_text := 'Unable to find Help for Help Key ['||p_help_key||']';
  END;
  RETURN l_help_text;
END get_custom_help;

PROCEDURE custom_help IS
  l_help_key     apex_application_page_items.item_help_text%TYPE;
  l_help_text    CLOB;
  l_source       VARCHAR2(10)  := apex_application.g_x01;
BEGIN
  apex_debug.info ('Source [%0], Value [%1]', l_source, apex_application.g_x02);
  IF l_source = 'ITEM' THEN
    -- Get the Help Key for the Page Item.
    l_help_key := cn_custom_help_pk.get_page_item_help_key 
                   (p_page_item_id => apex_application.g_x02);
  ELSIF l_source = 'IG_IR' THEN
    -- For IR and IG Column, Help Key is passed in.
    l_help_key := apex_application.g_x02;
  ELSE
    apex_json.open_object;
    apex_json.write('help_text', 'Unsupported Help Source');
    apex_json.close_all;
    RETURN;
  END IF;

  -- Get the help Text for the Help Key.
  l_help_text := cn_custom_help_pk.get_custom_help
                           (p_help_key => l_help_key);

  -- Create JSON to pass back the help text to the JS Function.
  apex_json.open_object;
  apex_json.write('help_text', l_help_text);
  apex_json.close_all;

EXCEPTION WHEN OTHERS THEN
  apex_json.open_object;
  apex_json.write('help_text', SQLERRM);
  apex_json.close_all;
END custom_help;

END CN_CUSTOM_HELP_PK;