Calling Oracle forms documents form non EBS applications, i.e. OBIEE

Few days ago my colleague while browsing through OBIEE dashboards commented that it would be nice to be able to  click on an invoice on a detailed view and open the actual document.

Well, there is a way to do just that. 

Oracle has a nice document "Integrating Oracle Business Intelligence Applications with Oracle E-Business Suite (Doc ID 555254.1)"

It says:

"In order to generate an Action Link you will first have to do the following:

1. Identify the Oracle E-Business Suite Application page/function that you want to link to. Obtain the function_id of that page and identify the querystring parameters required by that page. This will have to be done by going through Oracle E-Business Suite documentation."

That's good to know, but ... there is no documentation on it.

In the same note they describe function 

FND_RUN_FUNCTION.get_run_function_url(form function id, resp appl id, resp id, sg id, form params)

Which generates URL to a specific document that can be opened from browser window. That link can be added as a hyperlink to a non web based application and clicking it will bring up the  specific document within EBS. This is a gold mine, and a way to improve efficiency of work, but how do I know how to pass the parameters?

The first parameter is the form function id. This can be retrieved by running fnd_function.get_function_id('function name'). 

Example:

Select fnd_function.get_function_id('AR_ARXTWMAI_HEADER') from dual

The function name can be found by going to the specific form and following the steps below:

The second and third parameters, resp appl id, resp id can be found by going to:

Help -> Diagnostics -> Examine

Block: $PROFILES$

Fields: RESP_APPL_ID and RESP_ID

The last parameter, form params, is the hardest one, and without it the form will open but will not query a specific record.  Doc ID 555254.1 says to find the values by "going through Oracle E-Business Suite documentation", but no documentation exists in that regard. When I asked Oracle Support about it they were not aware of any.

Here is how I worked around it.

When you open a specific document, go to File menu and see if you can place it in navigator

Most of the major documents have this feature available.

When you do that the form will appear in the Documents tab of the EBS navigator

By itself it is no very useful, but clicking on the document will open the particular invoice. This means that the navigator passes the proper parameters to the form to open it. 

To capture those parameters enable forms trace by using instructions  from note 438652.1 section 2.2.

Those instruction are very comprehensive but most of the time it is sufficient to find the site value of the profile ICX: Forms Launcher and copy it to user profile appended with "?record=collect"

Example: https://hostname.domain:port/OA_HTML/frmservlet?record=collect

After setting up the profile option exit Oracle and log back it again for it to take effect.

Go to the Document added to the Navigator then double click on it. 

When done, retrieve the debug file located in $FORMS_TRACE_DIR 

and look for string "other_params"

Here is what I found for the AR invoice

AR_ARXTWMAI_HEADER open_flag: Y session_flag: Y other_params: FP_CUSTOMER_TRX_ID=647666 

This not only tells me the form parameters but also the name of the function, so I could have skipped the step above to find the name. 

Here is what it is for Purchase Order

Executing function: PO_POXPOEPO open_flag: Y session_flag: Y other_params: PO_HEADER_ID= 213180 ACCESS_LEVEL_CODE="MODIFY" POXPOEPO_CALLING_FORM="DESKTOP" 

Now I have all the needed parameters I can call my function:

For AR Invoice:

 select FND_RUN_FUNCTION.get_run_function_url(fnd_function.get_function_id('AR_ARXTWMAI_HEADER'), 222, 20678, 0,'FP_CUSTOMER_TRX_ID='||CUSTOMER_TRX_ID) from RA_CUSTOMER_TRX_All

This query creates URL to every single customer invoice. You can copy and paste it into browser and it will open the invoice. If you are not logged in it will prompt you to log in before opening the document. You must have access to the responsibility or the document will produce no access error. 

Here is syntax for other sample documents, make sure to replace the resp_appl_id and resp_id before running it on your system:

-- Purchase Order(Purchasing Superuser)

select FND_RUN_FUNCTION.get_run_function_url(fnd_function.get_function_id('PO_POXPOEPO'), 201, 20707, 0, 'po_header_id='||po_header_id||' ACCESS_LEVEL_CODE=MODIFY POXPOEPO_CALLING_FORM=DESKTOP') from po_headers_all

Note that I am passing all 3 required parameters for PO.  'po_header_id='||po_header_id||' ACCESS_LEVEL_CODE=MODIFY POXPOEPO_CALLING_FORM=DESKTOP'

Passing just the PO_HEADER_ID will not open the record

-- Quick Sales Order (Order Management Superuser)

select FND_RUN_FUNCTION.get_run_function_url(fnd_function.get_function_id('ONT_OEXOETEL'),660, 21623, 0, 'desktop_header_id='||header_id) from oe_order_headers_all 

Note that the correct parameter is "'desktop_header_id" not "header_id" which is an existing and intuitive parameter but using it will not open the form

--AP Invoices (Payables Manager)

 select FND_RUN_FUNCTION.get_run_function_url(fnd_function.get_function_id('AP_APXINWKB'), 200, 20639, 0,'INVOICE_ID='||invoice_id) from

AP_INVOICES_ALL

Even when a particular function cannot be placed on navigator, it may be still possible to get direct link to it, but it is harder to determine the right parameters. They can be determined by opening the form in Forms Developer and reading them from the triggers.  
The Org items and Material Workbench are examples of this scenario.

--Org Items (Inventory)

select FND_RUN_FUNCTION.get_run_function_url(fnd_function.get_function_id('INV_INVIDITM_ORG'), 401,  20634, 0,'chart_of_accounts_id=50348 org_id='||mp.organization_id||' item_id='||inventory_item_id||' org_code='||mp.organization_code)

 from mtl_system_items_b msi , mtl_parameters mp where msi.organization_id=mp.organization_id

--Material Workbench for an item and org (Inventory)

select FND_RUN_FUNCTION.get_run_function_url(fnd_function.get_function_id('INV_INVMATWB'), 401,  20634, 0,
'ORGANIZATION_ID='||organization_id||' INVENTORY_ITEM_ID='||inventory_item_id||' OWNING_QUERY_MODE=1 G_QUERY_FIND=FALSE')
from mtl_onhand_quantities
-- skip the organization_id if you want availability across orgs 

--Material transactions for Sales Order Issue, for specific item that not older than 3 months and not newer than one month (Inventory). This is an interesting example as it involves dates

SELECT FND_RUN_FUNCTION.get_run_function_url (
          fnd_function.get_function_id ('INV_INVTVTXN'),
          401,
          20634,
          0,
             'MODULE=CST FORM_USAGE_MODE=DSLA_DRILLDOWN INVTVTXN_GO_DETAIL=Y ORG_ID=102 INVTVTXN_INVENTORY_ITEM_ID='
          || inventory_item_id
          || ' INVTVTXN_TRXN_TYPE_ID=33 INVTVTXN_FROM_DATE='
          || SUBSTR (fnd_date.date_to_canonical (SYSDATE - 90),
                     1,
                       INSTR (fnd_date.date_to_canonical (SYSDATE - 90),
                              ' ',
                              1,
                              1)
                     - 1)
          || ' INVTVTXN_TO_DATE='
          || SUBSTR (fnd_date.date_to_canonical (SYSDATE - 30),
                     1,
                       INSTR (fnd_date.date_to_canonical (SYSDATE - 30),
                              ' ',
                              1,
                              1)
                     - 1))
  FROM mtl_system_items_b
 WHERE organization_id = 102 AND segment1 = 'XFR0150R240R'

--WiP Jobs

SELECT FND_RUN_FUNCTION.get_run_function_url (
          fnd_function.get_function_id ('WIP_WIPDJMDF_DEFINE'),
          706, 20560,
          0,
             'GID="'
          || wip_entity_id
          || '" ORG_ID="'
          || organization_id
          || '"')

  FROM wip_discrete_jobs where trunc(creation_date)=trunc(sysdate)

Using those URLs you can integrate third party applications with EBS, but you can also use the same parameters to create personalizations to call one Oracle form another from using the "Launch a Function" builtin.

Here is a proof of concept, you can use variables instead of the fixed values but the principle remains the same.

Click on Apply Now and the item form will open

A problem with calling Oracle from from an URL is that if you already have EBS open the URL may hijack the current session and hard close all the documents on the screen while keeping them locked.

To avoid that use this syntax when calling the URL from a third application:

href="http://url_from_above_queries_here"

target="_blank" name="Option" value="-noframemerging"

Opening the URL with the -nonframemerging option in IE will force a new EBS window to open.