Extending EBS with Apex: A Critical Warning (Update 1)

First (and most critical), the password issue does not start in a clean install. In the environment with the password issue, Apex had been installed as an app previously (the original extension method) although never used. Also, the environment had been upgraded from 11.5.10.2 and the passwords migrated to use hashing. I suspect one of these is the cause of the issues. Please let me know if you encounter the problem and if either of these apply to your environment.

Second, the Apex schema migration sets the READ_ONLY_FLAG to ‘K’. When you register an Apex function in E-Business Suite via Functional Administrator, the dropdowns to select the workspace, application, and page are populated based on the user with READ_ONLY_FLAG=’K’ in FND_ORACLE_USERID. You will either need to update the table and reset the flag to ‘K’ when registering functions (and set it back to ‘A’ otherwise-Strongly recommend that you keep the column set to ‘A’ during normal operation if you have this password issue.

--Enable Lookups but may have password issue
UPDATE FND_ORACLE_USERID
   SET READ_ONLY_FLAG='K'
WHERE ORACLE_USERNAME LIKE '%APEX%';

--Avoids Password issue but changing oracle passwords will change 
--Apex passwords and function lookup will fail
UPDATE FND_ORACLE_USERID
   SET READ_ONLY_FLAG='A'
WHERE ORACLE_USERNAME LIKE '%APEX%';

Alternatively, you can use the API to register the function, fnd_form_functions_pkg.load_row, which avoids having to change the value in the READ_ONLY_FLAG column for your Apex schemas. Here is a sample from apex_ebs_env.sql:

  fnd_form_functions_pkg.load_row(
    X_FUNCTION_NAME            => 'XX_APEX_DEMO_1',
    X_APPLICATION_SHORT_NAME   => null,
    X_FORM_NAME                => null,
    X_PARAMETERS               => null,
    X_TYPE                     => 'APEX',
    X_WEB_HOST_NAME            => null,
    X_WEB_AGENT_NAME           => null,
    X_WEB_HTML_CALL            => 'GWY.jsp?targetAppType=APEX&apexLayout=EMBEDDED&p=^APEX_APPID:^APEX_PAGE_NR',
    X_WEB_ENCRYPT_PARAMETERS   => 'N',
    X_WEB_SECURED              => 'N',
    X_WEB_ICON                 => null,
    X_OBJECT_NAME              => null,
    X_REGION_APPLICATION_NAME  => null,
    X_REGION_CODE              => null,
    X_USER_FUNCTION_NAME       => 'Update User Email (Without Responsibility)',
    X_DESCRIPTION              => 'Update the current user email without checking the user responsibility.',
    X_OWNER                    => null,
    X_CUSTOM_MODE              => 'FORCE',
    X_LAST_UPDATE_DATE         => to_char(sysdate, 'YYYY/MM/DD'),
    X_MAINTENANCE_MODE_SUPPORT => 'NONE',
    X_CONTEXT_DEPENDENCE       => 'RESP',
    X_JRAD_REF_PATH            => null
  );

  fnd_form_functions_pkg.load_row(
    X_FUNCTION_NAME            => 'XX_APEX_DEMO_2',
    X_APPLICATION_SHORT_NAME   => null,
    X_FORM_NAME                => null,
    X_PARAMETERS               => null,
    X_TYPE                     => 'APEX',
    X_WEB_HOST_NAME            => null,
    X_WEB_AGENT_NAME           => null,
    X_WEB_HTML_CALL            => 'GWY.jsp?targetAppType=APEX&apexLayout=EMBEDDED&p=^APEX_APPID:^APEX_PAGE_R:::::EBS_RESP_ID,EBS_APP_ID,EBS_SEC_GROUP:[RESPONSIBILITY_ID],[RESP_APPL_ID],[SECURITY_GROUP_ID]',
    X_WEB_ENCRYPT_PARAMETERS   => 'N',
    X_WEB_SECURED              => 'N',
    X_WEB_ICON                 => null,
    X_OBJECT_NAME              => null,
    X_REGION_APPLICATION_NAME  => null,
    X_REGION_CODE              => null,
    X_USER_FUNCTION_NAME       => 'Update User Email (Using Responsibilities)',
    X_DESCRIPTION              => 'Update the current user email after checking the user responsibility. This option is more secure!',
    X_OWNER                    => null,
    X_CUSTOM_MODE              => 'FORCE',
    X_LAST_UPDATE_DATE         => to_char(sysdate, 'YYYY/MM/DD'),
    X_MAINTENANCE_MODE_SUPPORT => 'NONE',
    X_CONTEXT_DEPENDENCE       => 'RESP',
    X_JRAD_REF_PATH            => null
  );