BI Publisher RTF Template Column Span

BI Publisher RTF Template, Merge Columns Dynamically

We shall see how to merge cells dynamically in Excel output with RTF template.

Use below code for defining the column span

<xsl:attribute xdofo:ctx="block" name="number-columns-spanned">        

       <xsl:value-of select="//CELL_TO_BE_MERGED"/>

</xsl:attribute>

In effect our RTF template will look as mentioned below:-

 Sample XML for testing is below:- 

<?xml version="1.0" encoding="UTF-8"?>

<CELLMERGE>

                <CELL_TO_BE_MERGED>3</CELL_TO_BE_MERGED>

                <CONTENT> THIS IS MERGED CELL </CONTENT>

</CELLMERGE>

Report output is shown below:-

Cells A1,B1,C1 are merged and content spans across 3 cells :-

Multi Value Parameter in Concurrent Program

Multi Value Parameter in Concurrent Program

In this article, we shall see a common use-case scenario i.e. Passing multiple values in a single Parameter to concurrent program.

This code can be used as a generic component and can be plugged-in to any concurrent program which requires single parameter which can accept multiple values.

This plug-in includes following:-

1.       Valueset which accepts multiple values separated by comma and enclosed by single quote for concurrent program parameter.

2.       xxxx_rprt_param_util_pkg contains pl/sql stored procedure to parse the comma separated parameter list

This procedure actually tokenizes each value and builds the query dynamically, this will be useful in situations for ex:- in reports where query results depend on values selected and query has to be constructed dynamically. This uses regular expression to validate the parameter to ensure that each value is enclosed with single quote and values separated by comma. For ex:- 'X','Y' is valid whereas 'X','K is not valid.

3.       xxxx_rprt_param_test_pkg contains pl/sql stored procedure which is invoked from concurrent program

STEP1:

We shall first define value set which will accept multiple values enclosed with single quote and separated by comma.

Below are the screenshots of value set definition:-

 

When you click on “edit information”, select event as “Validate” and paste code for function(download the code from link provided at the end of article)

  

STEP2:

Now define Concurrent program executable and concurrent program to test this value set.

Concurrent program excecutable screenshot is:- 

 

Concurrent program definition:-

  

STEP3:

After adding the CP to request group, test your CP.

Report output is

Lets closely see the code executed by CP, as you can see that input parameter is tokenized and final query is formed using xxxx_rprt_param_util_pkg.

Please download the required objects from here.

BI Publisher Different Page Sizes in Single RTF Template

BI Publisher Different Page Sizes in Single RTF Template

In this article we shall see how to create single RTF template and print output in different page sizes based on user parmeter “page_display”.

Design your template with needed content and "if condition" based on which corresponding content will be displayed in different page sizes. This "If" condition will not be simple IF, but it has to be like “<?if@section:$page_display=’L’?>” and defined inside BI Publisher field. My sample template will hence look like

Define page size to Legal Size by selecting from MS Word Menu Layout->Size->Legal for legal text and place pointer on second IF and now again go to Layout->Size

In this pop window select as shown below:-

To confirm that the page break is happening click on “P” in Home

Load dummy sample.xml(it just contains one root node) to test the report.

When page_display is initialized to “L”, PDF output page properties(in Acrobat File->Properties…) is shown below

When page_display is initialized to “S”, PDF output page properties(in Acrobat File->Properties…) is shown below:-

You can download RTF file along with dummy XML Download Here

OAF - Dynamically adding view attribute, Dynamically creating view object

In this article, we shall see code snippets in OAF, on

1.       How to dynamically add view attribute to existing VO

2.       How to create view object dynamically and get values by executing the query

How to dynamically add view attribute to existing VO

Following code can be used for dynamically adding new view attribute to existing standard VO

OAViewObject stdVO = (OAViewObject)am.findViewObject("OracleStdVO");

        if (stdVO != null) {

            try {

                String transientAttr =

                    stdVO.findAttributeDef("AdditionalInfo").toString();

            } catch (Exception e) {

                 stdVO.addDynamicAttribute("AdditionalInfo");

  pageContext.writeDiagnostics(this,"<<<Added dynamic transient  attribute>>>",OAFwkConstants.STATEMENT);

            }

               }

How to dynamically create query based view object

String requisitionLineId = 123;              // Need to pull up dynamically

String requisitionHeaderId = 234;           // Need to pull dynamically

       

// Create view object(only if doesnot exist) for getting ATTRIBUTE11 from PO_REQUISITION_LINES_ALL table

oracle.jbo.server.ViewObjectImpl customVO = (ViewObjectImpl)am.findViewObject("POReqVO");

if(customVO==null){

                pageContext.writeDiagnostics(this,"<<<Inside customVO is null>>>",OAFwkConstants.STATEMENT);

                // Define query.

                 String retnSqlStr = "select ATTRIBUTE11 from  PO_REQUISITION_LINES_ALL WHERE REQUISITION_LINE_ID =:1 AND REQUISITION_HEADER_ID = :2";

                customVO = (ViewObjectImpl)am.createViewObjectFromQueryStmt("POReqVO", retnSqlStr);

}

String attribute11  = null;

pageContext.writeDiagnostics(this,"<<<Created customVO>>>"+customVO,OAFwkConstants.STATEMENT);

customVO.setWhereClause(null);

customVO.setWhereClauseParams(null);

// Execute Query for this Requisition line

customVO.setWhereClauseParam(0,requisitionLineId);

customVO.setWhereClauseParam(1,requisitionHeaderId);

customVO.executeQuery();

int rowCnt = customVO.getRowCount();

pageContext.writeDiagnostics(this,"<<<Executed Retention Query RowCount = >>>"+rowCnt,OAFwkConstants.STATEMENT);

customVO.setRangeSize(rowCnt);

Row retnRows[] = customVO.getAllRowsInRange(); //there will be only one row but will loop

// Get  value after query execution

for(Row retnRow:retnRows){

                attribute11  = (String)retnRow.getAttribute(0);

                pageContext.writeDiagnostics(this,"<<<Inside retention query row loop retention = >>>"+retention,OAFwkConstants.STATEMENT);

}

Logging into Oracle.com SSO Site (OAM) Programmatically

Logging into Oracle.com SSO Site (OAM) Programmatically

In most of the real time projects, we usually have a scenario where we need to login to third party SSO site (which implements OAM) from custom page. In this article, we shall see how to write Custom Login page which directly logins into oracle.com website and opens the oracle welcome page after logging in.

You can download the files from below location:-

Download Here

Custom Page

1.       Oracle.com, login page submits the form to https://login.oracle.com/oam/server/auth_cred_submit which needs additional details like username, password and OAM_REQ (a transient cookie).

2.       So, in this use case we shall get OAM_REQ by connecting to below URL using HttpURLConnection http://www.oracle.com/webapps/redirect/signon?nexturl=https://support.oracle.com/cloud/faces/index . “nexturl” parameter value will be stored in OAM_REQ transient cookie which will tell where the user should be redirected after logging in.

3.       http://www.oracle.com/webapps/redirect/signon URL again redirects to https://login.oracle.com/pls/orasso/orasso.wwsso_app_admin.ls_login?Site2pstoreToken= with Site2pstoreToken generated. HTML code of login page has OAM_REQ cookie value as hidden parameter which we grab and redirect from JSP to “https://login.oracle.com/oam/server/auth_cred_submit?username="+userName+"&password="+pwd+"&OAM_REQ="+oam_req” which finally creates a user session and redirects to the URL mentioned in “nexturl” in Step-1

Code Components

1.       URLUtility.class which uses HttpURLConnection and gets valid OAM_REQ transient cookie value. Snapshot of code is pasted:-

    public static String getOAM_REQ() {

        try {

            String url =                "http://www.oracle.com/webapps/redirect/signon?nexturl=https://support.oracle.com/cloud/faces/index";

            // First set the default cookie manager.

            java.net

                .CookieHandler

                .setDefault(new java.net.CookieManager(null, java.net

                                                                 .CookiePolicy

                                                                 .ACCEPT_ALL));

            java.net.URL obj = new java.net.URL(url);

            java.net.HttpURLConnection conn = (java.net.HttpURLConnection) obj.openConnection();

            conn.addRequestProperty("User-Agent", "Mozilla");

            System.out.println("Request URL ... " + url);

            boolean redirect = false;

            // normally, 3xx is redirect

            int status = conn.getResponseCode();

            if (status != HttpURLConnection.HTTP_OK) {

                if (status == HttpURLConnection.HTTP_MOVED_TEMP || status == HttpURLConnection.HTTP_MOVED_PERM ||

                    status == HttpURLConnection.HTTP_SEE_OTHER)

                    redirect = true;

            }

            System.out.println("Response Code ... " + status);

            java.net.HttpURLConnection conn1 = null;

            if (redirect) {

                // get redirect url from "location" header field

                String newUrl = conn.getHeaderField("Location");

                System.out.println("Redirect to URL : " + newUrl);

                // get the cookie if need, for login

                String cookies = conn.getHeaderField("Set-Cookie");

                System.out.println("Redirect to URL : " + newUrl);

                java.net.URL newUrlObj = new java.net.URL(newUrl); //,new sun.net.www.protocol.https.Handler());

                // open the new connnection again

                conn = (java.net.HttpURLConnection) newUrlObj.openConnection();

                conn.setRequestProperty("Cookie", cookies);

                conn.addRequestProperty("User-Agent", "Mozilla");

                System.out.println("Response Code ... " + conn.getResponseCode());

            }

            BufferedReader in = new BufferedReader(new InputStreamReader(conn.getInputStream()));

            String inputLine;

            StringBuffer html = new StringBuffer();

            while ((inputLine = in.readLine()) != null) {

                html.append(inputLine);

            }

            in.close();

            System.out.println("URL Content... \n" + html.toString());

            String htmlStr = html.toString();

            System.out.println("Done");

            int beginIndex = htmlStr.indexOf("OAM_REQ\" value=\"") + "OAM_REQ\" value=\"".length();

            int endIndex = htmlStr.indexOf("\"><", beginIndex);

            System.out.println("beginIndex=" + beginIndex);

            System.out.println("endIndex=" + endIndex);

            String OAM_REQ = htmlStr.substring(beginIndex, endIndex);

            return OAM_REQ;

        } catch (Exception e) {

            e.printStackTrace();

        }

        return null;

    }

2.       customLoginDirectly.jsp calls URLUtility class to get OAM_REQ and redirects to https://login.oracle.com/oam/server/auth_cred_submit with valid parameters i.e. username,password and OAM_REQ.

Code Snapshot pasted below:-

<!DOCTYPE html>

<%@ page contentType="text/html;charset=UTF-8"%>

<%

String userName = "ENTER_VALID_USER_NAME";

String pwd = " ENTER_VALID_PASSWORD ";

String oam_req = foo.URLUtility.getOAM_REQ();

String finalUrl = "https://login.oracle.com/oam/server/auth_cred_submit?username="+userName+"&password="+pwd+"&OAM_REQ="+oam_req;

System.out.println(finalUrl);

response.sendRedirect(finalUrl);

%>

<html>

    <head>

        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>

    </head>

    <body>

    </body>

</html>

3.       When customLoginDirectly.jsp is ran, you will find logged into the session and will open up https://support.oracle.com/cloud/faces/index

Brief flow when we login to oracle.com

Reference: http://docs.oracle.com/cd/E27559_01/admin.1112/e27239/sso.htm#autoId0

SSO Log-in with Embedded Credential Collector and OAM Agents

Process overview: SSO Login Processing with Embedded Credential Collector and OAM Agents

1. The user requests a resource.
2. Webgate forwards the request to Access Manager for policy evaluation.
3. Access Manager:
1. Checks for the existence of an SSO cookie.
2. Checks policies to determine if the resource protected and if so, how?
4. Access Manager Server logs and returns decisions.
5. Webgate responds as follows:

1. Unprotected Resource: Resource is served to the user.
2. Protected Resource:

Request is redirected to the credential collector.The login form is served based on the authentication policy. Authentication processing begins

6. User sends credentials.
7. Access Manager verifies credentials.
8. Access Manager starts the session and creates the following host-based cookies:

·         One per Agent: OAMAuthnCookie set by 11g Webgates (ObSSOCookie set by 10g Webgate) using the authentication token received from the OAM Server after successful authentication.

Note: A valid cookie is required for a session.

·         One for OAM Server: OAM_ID

9. Access Manager logs Success or Failure.
10. Credential collector redirects to Webgate and authorization processing begins.
11. Webgate prompts Access Manager to look up policies, compare the user's identity, and determine the user's level of authorization.
12. Access Manager logs policy decision and checks the session cookie.
13. OAM Server evaluates authorization policies and cache the result.
14. OAM Server logs and returns decisions
15. Webgate responds as follows:
• If the authorization policy allows access, the desired content or applications are served to the user.
• If the authorization policy denies access, the user is redirected to another URL determined by the Administrator.