Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

Category Archives: Form Personalization

Enabling access to Oracle Forms-based Applications Diagnostics Menu

The difference between the two screenshots below are that in one of them the the diagnostics menu and submenus are available while in the other they are not.

The diagnostics menu allows users to personalize Forms, generate trace file, examine items etc.  Access to the diagnostics menu and submenu are controlled by the profile option ‘Hide Diagnostics Menu Entry’ about which I had written in an earlier post. However, sometimes even after setting the profile option, users may encounter the message “Function not available to this responsibility. Change responsibilities or contact your System Administrator.” This is because, beginning with Release 12.1.3, access to the diagnostics submenu items can be controlled by the profile option ‘Utilities:Diagnostics’ or by security functions using Role-Based Access Control (RBAC). Whether or not a submenu item is available is checked on an as-needed basis by the system when the user selects the submenu item.

The following table lists the seeded securing functions and their corresponding diagnostics menu items. If the user wants to access the diagnostics menu items from a specific responsibility, adding the functions below to the menu attached to their responsibility would allow them to do so.

Securing Function Name Securing Function User-Friendly Name Internal Menu Name Runtime Menu Name
FND_DIAGNOSTICS_EXAMINE FND Diagnostics Menu Examine DIAGNOSTICS

  • EXAMINE
Diagnostics

  • Examine
FND_DIAGNOSTICS_EXAMINE_RO FND Diagnostics Menu Examine Read Only DIAGNOSTICS

  • EXAMINE
Diagnostics

  • Examine
FND_DIAGNOSTICS_TRACE FND Diagnostics Trace TRACE

  • NO_TRACE
  • REGULAR
  • BINDS
  • WAITS
  • BINDS_AND_WAITS
  • PLSQL_PROFILING
Diagnostics

  • No Trace
  • Regular Trace
  • Trace with Binds
  • Trace with Waits
  • Trace with Binds and Waits
  • PL/SQL Profiling
FND_DIAGNOSTICS_VALUES FND Diagnostics Values PROPERTIES_MENU

  • ITEM
  • FOLDER
Diagnostics – Properties

  • Item
  • Folder
FND_DIAGNOSTICS_VALUES_RO FND Diagnostics Values Read Only PROPERTIES_MENU

  • ITEM
  • FOLDER
Diagnostics – Properties

  • Item
  • Folder
FND_DIAGNOSTICS_CUSTOM FND Diagnostics Custom CUSTOM_CODE_MENU

  • NORMAL
  • OFF
  • CORE
  • SHOW_EVENTS
Diagnostics – Custom Code

  • Normal
  • Off
  • Core Code Only
  • Show Custom Events
FND_DIAGNOSTICS_PERSONALIZE FND Diagnostics Personalize CUSTOM_CODE_MENU

  • CUSTOMIZE
Diagnostics – Custom Code

  • Personalize
FND_DIAGNOSTICS_PERSONALIZE_RO FND Diagnostics Personalize Read Only CUSTOM_CODE_MENU

  • CUSTOMIZE
Diagnostics – Custom Code

  • Personalize

Adding the function ‘FND Diagnostics Personalize Read Only’ to the appropriate menu by navigating to System Administrator>Application>Menu, for example, will allow users to access a read-only version of the Form Personalization screen.

While adding the function ‘FND Diagnostics Personalize’ will allow users to access the normal Form Personalization screen.

In addition to securing functions, access to the diagnostics menu items can also be controlled by assigning permission sets to roles and then assigning the roles to users. The following table lists seeded permission sets.

Permission Set Name Permission Set Code Permissions Assigned
FND Diagnostics Examine Menu FND_DIAGNOSTICS_EXAMINE_PS FND Diagnostics Menu Examine
FND Diagnostics Examine Read Only FND_DIAGNOSTICS_EXAMINE_RO_PS FND Diagnostics Menu Examine Read Only
FND Diagnostics Custom Menu FND_DIAGNOSTICS_CUSTOM_PS FND Diagnostics Custom
FND Diagnostics Personalizations Menu FND_DIAGNOSTICS_FORMS_PERS_PS FND Diagnostics Personalize
FND Diagnostics Personalizations Menu Read Only FND_DIAGNOSTICS_FRM_PERS_RO_PS FND Diagnostics Personalize Read Only
FND Diagnostics Properties Menu FND_DIAGNOSTICS_PROPERTIES_PS FND Diagnostics Values
FND Diagnostics Properties Menu Read Only FND_DIAGNOSTICS_PROP_RO_PS FND Diagnostics Values Read Only
FND Diagnostics Trace Menu FND_DIAGNOSTICS_TRACE_PS FND Diagnostics Trace
FND Diagnostics Menu Developer FND_DIAGNOSTICS_DEVELOPER_PS
  • FND Diagnostics Examine
  • FND Diagnostics Personalize
  • FND Diagnostics Trace
  • FND Diagnostics Values
  • FND Diagnostics Custom
FND Diagnostics Menu Support FND_DIAGNOSTICS_SUPPORT_PS
  • FND Diagnostics Examine Read Only
  • FND Diagnostics Personalize Read Only
  • FND Diagnostics Trace
  • FND Diagnostics Values Read Only
  • FND Diagnostics Custom

Source/further reading: Controlling Access to the Oracle Forms-based Applications Diagnostics Menu(Oracle E-Business Suite System Administrator’s Guide – Configuration)

Handy Notes-II

1. Use of <PROPERTY_NAME>(APPLICATIONS COVER) in Form Personalization

You are personalizing a Form and you want the DISPLAYED property to be set to FALSE for an item. While you are at it you will notice that there are two versions in the Property Name field- one is the normal ‘DISPLAYED’ and the other is ‘DISPLAYED (APPLICATIONS COVER)’. Ever wondered what is the difference between these two? Well, the difference is explained in the  Developer’s Guide.

Oracle E-Business Suite provides a cover routine to the Oracle Forms built-in routine SET_ITEM_PROPERTY. This cover routine, APP_ITEM_PROPERTY.SET_PROPERTY, modifies or augments the native Oracle Forms behaviors for specific properties. Using APP_ITEM_PROPERTY.SET_PROPERTY helps your forms adhere to the Oracle E-Business Suite user interface standards and helps simplify coding. Using this routine also helps to protect your form from future changes in the native Oracle Forms SET_ITEM_PROPERTY built-in routine.

In short, ‘DISPLAYED (APPLICATIONS COVER)’ makes use of the EBS cover routine APP_ITEM_PROPERTY.SET_PROPERTY while ‘DISPLAYED’ uses the Forms built-in SET_ITEM_PROPERTY. For more details you can check the ‘Setting Item Properties’ section of the Developer’s Guide.

2. Changes to profile options not taking effect

Sometimes it is observed that changes made to profile options do not take effect until Apache server is bounced. The culprit in such a scenario might be Java Caching. Java caching provides a powerful, flexible, easy to use mechanism for storing database results and other Java objects in-memory for repeated usage. This minimizes expensive object initializations and database round-trips, thereby improving application performance. The profile option issue can be solved by clearing the cache by following the following steps:

1. Log into the application with the the Functional Administrator responsibility

2. Choose the Core Services Tab > Caching Framework subtab

3. Click on ‘Total Cache Components’ hyperlink

4. Search for code : PROFILE%OPTION%

5. Select check boxes for PROFILE_OPTION_CACHE & PROFILE_OPTION_VALUE_CACHE

6. Click on ‘Clear Cache’ button

Further information on Java Caching:

1. Metalink Note#275879.1 -Oracle Applications Java Caching Framework Developer’s Guide Release 11i
2. Metalink Note#395439.1 -Oracle Applications Java Caching Framework Developer’s Guide Release 12
3. Metalink Note#455194.1 -Diagnosing database invalidation issues with Java Cache for eBusiness Suite

3. Controlling access to the Diagnostics menu and submenu items

Utilities: Diagnostics – Utilities: Diagnostics determines whether a user can automatically use the following Diagnostics submenu items: Examine, Trace, Debug, Properties, and Custom Code. If Utilities:Diagnostics is set to Yes, then users can automatically use these features. If Utilities:Diagnostics is set to No, then users must enter the password for the APPS schema to use these Diagnostics features.

Hide Diagnostics Menu Entry – If this profile is set to Yes, the Diagnostics menu is hidden from the user.

Invoking a Web Service from seeded Oracle EBS form through PL/SQL

The scenario : Invoking a Web Service hosted in a SAP PI server from a seeded Oracle E-Business Suite form

The solution : Writing a PL/SQL function which invokes the Web Service and calling the function from the seeded form. The PL/SQL function will use the UTL_HTTP package to invoke the Web Service through a HTTP POST request.

The Steps : Generate the expected SOAP Request and Response XML documents for the Web Service. There are several tools which you can use to get the Request and Response XML documents. I found soapUI to be the best(read easiest) of the lot. All you need to do is to create a new project in soapUI based on the WSDL and generate a MockService. soapUI will generate the Request XML for you which you can then run to get the Response XML.

Next we will create the PL/SQL function which will invoke the Web Service.

create or replace function xxx_sample_ws(p_em_amount number)
  return varchar2 is
  soap_request varchar2(30000);
  soap_respond varchar2(30000);
  http_req     utl_http.req;
  http_resp    utl_http.resp;
  resp         XMLType;
  exp_resp     XMLType;
  endpoint_url varchar2(1000);
  username CONSTANT varchar2(10) := 'user_100';
  password CONSTANT varchar2(10) := 'pass_100';
  pname varchar2(100);
  outs  varchar2(10000) := null;

begin

  --The SOAP Request XML document obtained from soapUI
  --the parameters are embedded into the request
  soap_request := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://rapdrp.com/updateemd/transactional/model/1.0/" xmlns:ns1="http://rapdrp.com/common/bodcomponents/transactional/model/1.0/">
   <soapenv:Header/>
   <soapenv:Body>
      <ns:UpdateEMDReq>
         <ns:ApplicationArea>
            <!--Optional:-->
            <ns1:Sender>
               <!--Optional:-->
               <ns1:Id>?</ns1:Id>
               <!--Optional:-->
               <ns1:Properties>
                  <!--Optional:-->
                  <ns1:Property>?</ns1:Property>
                  <!--Optional:-->
                  <ns1:Value>?</ns1:Value>
               </ns1:Properties>
            </ns1:Sender>
            <!--Optional:-->
            <ns1:Receiver>
               <!--Optional:-->
               <ns1:Id>?</ns1:Id>
               <!--Optional:-->
               <ns1:CompanyName>?</ns1:CompanyName>
               <!--Optional:-->
               <ns1:CompanyId>?</ns1:CompanyId>
               <!--Optional:-->
               <ns1:Properties>
                  <!--Optional:-->
                  <ns1:Property>?</ns1:Property>
                  <!--Optional:-->
                  <ns1:Value>?</ns1:Value>
               </ns1:Properties>
            </ns1:Receiver>
            <!--Optional:-->
            <ns1:Reference>
               <!--Optional:-->
               <ns1:BODCreationDateTime>?</ns1:BODCreationDateTime>
               <!--Optional:-->
               <ns1:BusinessProcessId>?</ns1:BusinessProcessId>
               <!--Optional:-->
               <ns1:MessageId>?</ns1:MessageId>
               <!--Optional:-->
               <ns1:CorrelationId>?</ns1:CorrelationId>
               <!--Zero or more repetitions:-->
               <ns1:Properties>
                  <!--Optional:-->
                  <ns1:Property>?</ns1:Property>
                  <!--Optional:-->
                  <ns1:Value>?</ns1:Value>
               </ns1:Properties>
               <!--Optional:-->
               <ns1:Username>?</ns1:Username>
               <!--Optional:-->
               <ns1:Token>?</ns1:Token>
            </ns1:Reference>
         </ns:ApplicationArea>
         <ns:DataArea>
            <!--Optional:-->
            <ns:ApplicationNo>?</ns:ApplicationNo>
            <!--Optional:-->
            <ns:ApplicationDate>?</ns:ApplicationDate>
            <!--Optional:-->
            <ns:ErnestMoneyAmt>'||p_em_amount||'</ns:ErnestMoneyAmt>
            <!--Optional:-->
            <ns:ApplicantName>?</ns:ApplicantName>
         </ns:DataArea>
      </ns:UpdateEMDReq>
   </soapenv:Body>
</soapenv:Envelope>';

  --the endpoint URL of the Web Service
  endpoint_url := 'http://10.95.244.75:8000/sap/xi/engine?type=entry' || '&' ||
                  'sap-user=user_100' || '&' || 'sap-password=pass_100' || '&' ||
                  'version=3.0' || '&' ||
                  'Sender.Service=CB_SZPDRP_DEV_ORACRM' || '&' ||
                  'Interface=urn%3Awb.szpdrp.oracrm.updateemd%5EUpdateEMD_OB';

  -- if proxy server is involved, use the following for authentication
  -- where 'username' and 'passwd' are the credentials required to access the proxy server
  -- with IP address 'ip_addredd' at port number 'port'
  -- utl_http.set_proxy('http://username:passwd@ip_address:port');

  --begin HTTP request
  http_req := utl_http.begin_request(endpoint_url, 'POST', 'HTTP/1.1');

  --set basic HTTP authentication
  --not required for this case as the endpoint URL contains the username/password
  --utl_http.set_authentication(http_req, username, password);
  utl_http.set_header(http_req, 'Content-Type', 'text/xml');
  utl_http.set_header(http_req, 'Content-Length', length(soap_request));

  --The SOAP action for the Web Service can also be obtained from soapUI
  utl_http.set_header(http_req,
                      'SOAPAction',
                      'http://sap.com/xi/WebService/soap1.1');
  utl_http.write_text(http_req, soap_request);
  http_resp := utl_http.get_response(http_req);
  utl_http.read_text(http_resp, soap_respond);
  utl_http.end_response(http_resp);
  resp := XMLType.createXML(soap_respond);

  --extract the value of the relevant element from the SOAP Response XML
  --Google 'Oracle XMLType' for more information
  begin
    exp_resp := resp.extract('//ns1:Status/text()',
                             'xmlns:ns1="http://rapdrp.com/updateemd/transactional/model/1.0/"');
    outs     := exp_resp.getStringVal();

  exception
    when others then
      outs := SQLERRM;
  end;

  --return the extracted value or the error details
  return outs;

  --in case of exceptions return the error details
exception
  when others then
    outs := SQLERRM;
    return outs;
end;

You can now call the function from the seeded Form through Form Personalization to invoke the Web Service and display an appropriate message to the user if the invocation was successful.

Nice friendly message for the user!