Hyperlink in EXCEL BI Publisher Reports

The user has to search for the PO number online in the PO Form to view the related information. Similarly, the data related to PO like Item Information, Sales Order Information, Sourcing Rule Information and Supplier Information are present in different forms and responsibilities. To avoid user to switch responsibilities, open the form and query the item or supplier or sourcing rule information, a link to the PO/Item/Supplier/SO will be incorporated in the report.
Note: This blog is purely for Hyperlink to Organization Items form. You can refer this article to create hyperlink to other forms

To create a hyperlink, we can use the following query(Replace p_organization_id with Organization ID):

SELECT REPLACE(fnd_run_function.get_run_function_url(p_function_id      => (SELECT function_id
FROM fnd_form_functions
WHERE function_name =
‘INV_INVIDITM_ORG’
AND TYPE =
‘FORM’),
p_resp_appl_id     => fnd_profile.value(‘RESP_APPL_ID’),
p_resp_id          => fnd_profile.value(‘RESP_ID’),
p_security_group_id => fnd_profile.value(‘SECURITY_GROUP_ID’),
p_parameters       => ‘G_QUERY_FIND=TRUE ITEM_ID=’ ||
prl.item_id ||
‘ ORG_ID =’ ||
p_organization_id ||
‘ ORG_CODE=’ ||
(SELECT organization_code
FROM org_organization_definitions
WHERE organization_id =
p_organization_id) ||
‘ CHART_OF_ACCOUNTS_ID=’ ||
(SELECT chart_of_accounts_id
FROM org_organization_definitions
WHERE organization_id =
p_organization_id),

p_org_id => fnd_profile.value(‘ORG_ID’)),
‘&’,
‘%26’) item_url
FROM dual;

The above query will give you a Hyperlink to the Item form at Organization Level.

There is a bug in excel in case of OAF or Oracle Forms.
Please refer the following link for more information:
https://support.microsoft.com/en-gb/kb/899927

The hyperlink in excel output will ask for login each time when the user clicks on it and if we keep a direct link to OAF page or Oracle Forms even though the browser has the logged-in session.

To remove the drawback, our team developed the innovative solution. We created a JSP program which will catch the browser session and will direct the user to the form. The user will not have to login each time it clicks on Hyperlink.



Code(RedirectPage.jsp):

<!– import attributes start here –>
<%@ page import = “java.io.*” %>
<%@ page import = “java.util.*” %>
<%@ page import = “oracle.apps.fnd.common.WebAppsContext” %>
<%@ page import = “oracle.apps.fnd.common.AppsContext” %>
<%@ page import = “oracle.apps.fnd.common.WebRequestUtil” %>
<%@ page import = “javax.servlet.ServletOutputStream” %>
<%@ page import = “oracle.apps.fnd.sso.SessionMgr” %>
<%@ page import = “oracle.apps.fnd.sso.Utils” %>
<%@ page import = “javax.servlet.*” %>
<%@ page import = “oracle.apps.fnd.common.Message” %>
<!– import attributes ends here –>
<%
WebAppsContext wctx = null;
try {
try
{
wctx = getWebAppsContext(request,response);
String linkString = request.getParameter(“page”);
response.setHeader(“Refresh”, “0;url=”+””+linkString+””);
}
catch(Exception e)
{
response.setContentType(“text/plain”);
printError(response,”Error in forwarding the URL.”);
e.printStackTrace(System.err);
}
finally
{
if (wctx != null) {
/*just to be on safer side if the connection code is added in future,
*the WebAppsContext needs to be released else monitors will
*raise alarm of a leak*/
try{ wctx.freeWebAppsContext(); }
catch (Throwable _ignoreit){}
wctx=null;
}
}
} catch (Exception e ) {
e.printStackTrace(System.err);
} finally {
if (wctx!=null)
{
try{ wctx.freeWebAppsContext(); }
catch (Throwable _ignoreit){}
}
}
%>
<%!
/*
* Return a AppsContext instance.
* The validateContext method checks whether the session associated with
* the request and response streams is a valid one –
* if so it returns a properly validated context.
* If the session is invalid or expired, this will take care of
* displaying a login page to the user and the method will return null.
*/
private WebAppsContext getWebAppsContext(HttpServletRequest request,
HttpServletResponse response)
{
WebAppsContext ctx = null;
try
{
ctx = WebRequestUtil.validateContext(request, response);
return ctx;
}
catch(Exception e)
{
response.setContentType(“text/plain”);
printError(response,e);
return ctx;
}
}
/*
* Display an error message on the page.
*/
private void printError(HttpServletResponse response, String message)
{
printLine(response,”RedirectPage.jsp error : ” + message);
}
/*
* Display an error message on the page.
*/
private void printError(HttpServletResponse response, Exception exception)
{
printError(response,”Java Exception”);
getWriter(response).println(“<pre>”);
exception.printStackTrace(getWriter(response));
getWriter(response).println(“</pre>”);
}
/**
* Display a message on the page.
*/
private void printLine(HttpServletResponse response, String message)
{
getWriter(response).println(message);
}
private PrintWriter getWriter(HttpServletResponse response) {
try {
return (new PrintWriter(response.getOutputStream(), true));
}
catch (IOException ioe) {
throw new RuntimeException(“Failed to obtain writer 1.”);
}
catch (IllegalStateException ise) {
/* if getWriter has already been called on this response
* stream, the getOutputStream call above will fail. In that case,
* use the getWriter call instead and wrap a new PrintWriter
* object around it.  */
try {
return (response.getWriter());
}
catch (IOException ioe) {
throw new RuntimeException(“Failed to obtain writer 2.”);
}
}
}
%>
Place the file either in the Custom TOP bin folder($XXCUST_TOP/bin/):
cp $XXCUST_TOP/bin/RedirectPage.jsp $OA_HTML/RedirectPage.jsp
or directly under $OA_HTML folder:
cd $OA_HTML
Compile the file using the following command:
$FND_TOP/patch/115/bin/ojspCompile.pl –compile -s ‘RedirectPage.jsp’ -log err.log –flush
After the JSP file is compiled, use the query given below in Data Definition(Replace p_organization_id with Organization ID) :
SELECT fnd_profile.value(‘APPS_SERVLET_AGENT’) || ‘/RedirectPage.jsp?page=’ ||
REPLACE(fnd_run_function.get_run_function_url(p_function_id      => (SELECT function_id
FROM fnd_form_functions
WHERE function_name =
‘INV_INVIDITM_ORG’
AND TYPE =
‘FORM’),
p_resp_appl_id     => fnd_profile.value(‘RESP_APPL_ID’),
p_resp_id          => fnd_profile.value(‘RESP_ID’),
p_security_group_id => fnd_profile.value(‘SECURITY_GROUP_ID’),
p_parameters       => ‘G_QUERY_FIND=TRUE ITEM_ID=’ ||
prl.item_id ||
‘ ORG_ID =’ ||
p_organization_id ||
‘ ORG_CODE=’ ||
(SELECT organization_code
FROM org_organization_definitions
WHERE organization_id =
p_organization_id) ||
‘ CHART_OF_ACCOUNTS_ID=’ ||
(SELECT chart_of_accounts_id
FROM org_organization_definitions
WHERE organization_id =
p_organization_id),

p_org_id => fnd_profile.value(‘ORG_ID’)),
‘&’,
‘%26’) item_url
FROM dual



Note: To get the values of profile options, you need to initialize apps using following procedure in PL/SQL:
fnd_global.apps_initialize(user_id => USER_ID , resp_id => RESP_ID, resp_appl_id => RESP_APPL_ID);

In addition to this, Organization Items form need to be personalized. Please refer the screenshots below:

 

In RTF Template, Add Hyperlink to the Tag and write {ELEMENT_NAME}

Where ELEMENT_NAME = name of the XML Element item_url from the above query.

1 comments On Hyperlink in EXCEL BI Publisher Reports

Leave a reply:

Your email address will not be published.

Sliding Sidebar