, , , ,

XSL/XPATH for BI Publisher

When you start developing RTF Templates and try to do something a bit advanced most likely you start seeing these words, XSL, XSLT, XPATH, XSL-FO…. And they might have scared or confused you. I’ve met many people use those terms without a correct understanding and everybody uses them in different ways so even I get sometimes confused when I talk with them. 😉 I think the part of the reason is coming from its own history.

What is XSL?

XSL stands for ‘Extensible Stylesheet Language’ and it was designed to describe how to transform and format XML files. But it split into different specifications (or languages) as listed below. So this XSL itself actually is really anything today, it rather be a family name if you will, which contains all the three related languages below.

Here is a list of the three languages that comprise the XSL.

  • XPATH – a language for navigating in XML documents
  • XSLT – a language for transforming XML documents
  • XSL-FO – a language for formatting XML documents


Let’s take at look at each language in more detail and why we need to know them for BI Publisher reports development.

Start from XPath…
XPath is a language for finding information in an XML document. XPath is used to navigate through elements and attributes in an XML document. XPath uses path expressions to navigate in XML documents. It is very similar to what we call ‘Path’ on Unix file system. Basically it acts as a navigation in XML files. Let’s say there is a parent node called ‘Department’ and it has a child node called ‘Employee’. Now you’re processing Employee node and want to get some data from the parent node, Department. This is where the XPath comes in. And it is in fact very simple to do this. You can type something like ‘../Department/BUDGET’. Yes, that’s it and it’s very similar to the path we use at the file system, right?

Path Expression
What makes XPath different from the path is its Path Expression. The Path Expression is very powerful and makes it much easier to access to any vaues in the XML files.
For example you can use ‘//’ (double slashes) to indicate that you want to get any Element and it doesn’t matter if what level the element is.

Here is a list of commonly used Path Expressions.

/ – Selects from the root node

Example:
/Department (Selects the root element Department)
Department/Employee (Selects all Employee elements that are children of Department)

// – Selects nodes in the document from the current node that match the selection no matter where they are

Example:
//Department (Selects all Department elements no matter where they are in the XML)

Department//Employee (Selects all Employee elements that are descendant of the Department element, no matter where they are under the Department element)

. – Selects the current node

.. – Selects the parent of the current node

@ – Selects attributes

Example:
//@type (Selects all attributes that are named type)

Predicates

Also, there are some advanced expressions called ‘Predicates’. Predicates are something you might have seen in your template They are presented with square brackets. They are used to find a specific node and can have specific conditions to specify the node. For example, ‘/Department[1]’ will return the first Department node while ‘/Department[2]’ will return the second Department node. You can also have a condition in the predicates to pick a certain set of nodes only when the condition matches. For example, if you specify ‘/Department[Salary>5000]’ then it will return only the Department nodes that contains Salary element whose values are greater than 5000.

You can also specify relative position. The above example of ‘/Department[1]’ will always return the first Department node in the XML file. But if you have many groups or you’re grouping by a certain value (e.g. Department name) and you might want to pick the first node in the each group. In this case you can use ‘Department[first()]’ or ‘Department[position()=1].

Operators (Functions)
Also, XPath has its own Operators (or functions) that you can use to process or calculate your data in the XML file. For example there is a ‘substring’ function, which you can use to get a part of the data you want from a specified Element values. There are many other useful functions and all of the standard XPath functions can be used in the BI Publisher’s RTF Template. Here is a set of XPATH functions that are useful and we use in many cases with the RTF Template.

List of XPATH functions

  1. substring()
  2. substring-before(’12/10′,’/’)
  3. replace(“Bella Italia”, “l”, “”)
  4. upper-case() /lower-case()
  5. contains()
  6. distinct-values()
  7. false()
  8. sum()


Why this is for BI Publisher?
Now you have gone though the XPath basic and wondering ‘why do I need to know this?’ Here is list of example use cases where we think it is critical and very useful if you understand the XPath appropriately.

  1. With for-each-group you need to specify a parent node and an element node where you want to group by with XPath appropriately
  2. When you want to access to a parent node’s element values when you are processing tis child node.
  3. For IF condition you might want to process data to build some valid conditions. For example if you want to get Employee name ‘Smith’ regardless whether it’s in upper case, lower case or the combination, you can use ‘upper-case()’ so that every type of ‘Smith’ will be matched.
  4. Inside Chart definition you need to specify all the Element names appropriately with XPath. Also you can use the XPath functions to have conditions or process data inside the Chart.


These are just a few examples to list, but there are many other cases you can take advantage of the XPath and make your RTF Template development much easier and more flexible and powerful.

Also, note that using the XPath without an appropriate understanding might cause performance and resource allocation problems. Due to its flexibility you can achieve what you want to do by using many different ways with XPath. However, you might end up typing XPath codes that cause very resource intensive or unnecessary processing. The key is to have a right understanding of XPath and write the code in the most optimized way from maintenance and performance perspective.

/by
, , ,

R12 – Basics of XSLT for BI Publisher

What is XSLT? 
Today, I’ll cover the basic of the XSLT. Basic development of BI Publisher RTF Template doesn’t require the XSLT coding. However, when you start developing advanced reports having a good understanding of the XSLT will help you to develop the RTF Template efficiently and provide richer functionality. I’ll cover the benefit of using XSLT at the end of this posting. 

XSLT stands for XSL Transformations and XSL stands for Extensible Style sheet Language. XSLT is a language for transforming XML documents into another form of documents such as XML, HTML, XHTML, etc. When you use XSLT to transform the original XML documents you will also use XPath to navigate through the documents. So in this process of transforming the documents you use a combination of XSLT and XPath together. 

How to use? 
Here is a list of basic steps to use XSLT. 

1. Declare XSL namespace 
2. Create XSL style sheet 
3. Add a link to the style sheet 

1. Declare XSL namespace 
First you need to declare XSL namespace like follows at the top of the XML document.

<xsl:stylesheet version=”1.0″
xmlns:xsl=”http://www.w3.org/1999/XSL/Transform”>

2. Create XSL style sheet 
Then you can start developing the XSL style sheet. How you can develop the style sheet will be covered later. 

3. Add a link to the style sheet 
Once you have created the style sheet now you can add a link to the style sheet in the original XML document. Then when you process the XML document whatever the standard XSL processor you’re using would transform the XML document following the transformation rules designed in the XSL style sheet is linked in the XML document. 

Example:

<?xml-stylesheet type=”text/xsl” href=”sample.xsl”?> 
XSL Element (or function)

XSL transformation rules are defined with XSL Elements. Here is a set of the most commonly used XSL elements.

  • <xsl:template>
  • <xsl:value-of>
  • <xsl:for-each>
  • <xsl:sort>
  • <xsl:if>
  • <xsl:choose>
  • <xsl:apply-templates>
Since most of the XSLT coding can be also done by the native BI Publisher coding, at each of the following Element example section I’ll also show how to do the same with the BI Publisher coding.

XSL:TEMPLATE
This element is used to specify what part of the XML document should be transformed or be applied with the rules described in the XSL stylesheet. You can use ‘match’ attribute specifying the section of the XML with XPath. For example, if you specify ‘/’ (Root) then the XSL stylesheet will be applied to the entire XML document.

Example: 

<xsl:template match=”/”> 

XSL:VALUE-OF
This element is used to retrieve a value from a specified Element or Attribute. For example, if you used this element specifying ‘/DATA/DEPARTMENT/NAME’ then you can retrieve a value that is presented in the NAME element in the XML document.

Example:

<xsl:value-of select=’/DATA/DEPARTMENT/NAME’/> 
With BI Publisher tags, you can just type the following to do the same.
<?/DATA/DEPARTMENT/NAME?> 

XSL:FOR-EACH
When you just use the <xsl:value-of> element you will get the first value of the specified element. With the above case, you will get the first Department name. But what if you have 5 departments and want to display all the names together?

You can use this ‘for-each’ element to repeat through a specified node. It works the same way as ‘for loop’ in any typical programming language such as C, Java. For example, if you specify ‘/DATA/DEPARTMENT’ as a node and use the ‘for-each’ element to repeat then the XSL processor will repeat through the Department node and display all the department names.

Example:

<xsl:for-each select=’/DATA/DEPARTMENT’>
  <xsl:value-of select=’NAME’/>
</xsl:for-each>
With BI Publisher tags, you can type the following to do the same.
<?for-each:/DATA/DEPARTMENT?>
  <?NAME?>
<?end for-each?>

XSL:SORT
Inside the previous ‘for-each’ loop you might want to sort the data by alphabetically or based on the ID, etc. You can use this ‘sort’ element to do the sorting.

Example: 

<xsl:for-each select=’/DATA/DEPARTMENT’>
<xsl:sort select=’NAME’/>
  <xsl:value-of select=’NAME’/>
</xsl:for-each>
Also you can specify the data type and whether it should be ascending or descending order.

Example: 

  <xsl:sort select=’NAME’ data-type=’text’ order=’descending’/>
With BI Publisher you can do the following to achieve the same. 
  <?sort:NAME;’ascending’;data-type=’text’?>

XSL:IF
You can use this element to have a condition in the XSL transformation logic. This is also pretty much the same as other programming language’s ‘if’ condition. For example, if you want to display manager name only when Department name is ‘Consulting’ you can specify something like the below.

Example: 

<xsl:for-each select=’/DATA/DEPARTMENT’>
  <xsl:if test=”NAME=’Consulting’”>
    <xsl:value-of select=’MANAGER_NAME’/>
  </xsl:if>
</xsl:for-each>
You can do the same with BI Publisher tags as follows.
<?for-each:/DATA/DEPARTMENT?>
  <?if:NAME=’Consulting’?>
    <?NAME?>
  <?end if?>
<?end for-each?>
XSL:CHOOSE
As an alternative or for better reasons you can also use CHOOSE/WHEN elements to do the condition. One thing to note is that XSL doesn’t support IF/ELESE condition as native, so if you have multiple conditions to use together in a form of IF/ELSE then CHOOSE/WHEN/OTHERWISE elements would serve you better.

Example: 

<xsl:for-each select=’/DATA/DEPARTMENT’>
  <xsl:choose>
  <xsl:when ”NAME=’Consulting’”>
    <xsl:value-of select=’MANAGER_NAME’/> 
    <xsl:value-of select=’DEPARTMENT_NAME’/>
  </xsl:when>
  <xsl:otherwise>
    <xsl:value-of select=’DEPARTMENT_NAME’/>
  </xsl:otherwise>
</xsl:for-each>
With BI Publisher you can do the below to have the same condition. 
<?for-each:/DATA/DEPARTMENT?>
  <?choose:?>
  <?when:NAME=’Consulting’>
    <?MANAGER_NAME?>
    <?DEPARTMENT_NAME?>
  <?end when?>
  <?otherwise:?>
    <?DEPARTMENT_NAME?>
  <?end otherwise?>
  <?end choose?>
<?end for-each?>
Why XSLT is needed for BI Publisher?

Though you can type any XSLT coding and BI Publisher can understand it, typical RTF Template development doesn’t require the use of the XSLT. In fact BI Publisher’s tags, which are surrounded by ‘?’ marks, covers the native XSLT functionality for the most so that you don’t need to type the XSLT code. As you have seen, what you can do with XSLT can also be done by the BI Publisher tags and visaversa. When you develop the RTF Template you can use the BI Publisher’s tags then at the run time BI Publisher translate the tags to the XSL codes internally. BI Publisher’s tags are there to make the template development much easier for those who do not have any XML/XSL or programming experience.

However, there are certain situations where you might want to use XSLT over BI Publisher’s tags. Such situation includes the Chart development and variable handling. As you might have known behind the Chart definition is XSLT code. You can insert a chart from the Template Builder (MS Word Add-in) Chart wizard to start with. But once you want to customize the default chart formatting or logic’s to handle the data then you need to modify the XSLT code behind the Chart definition.

Also, sometimes it’s very useful when you want to use variables. There is a BI Publisher’s tag for variable handling but sometimes I find using XSLT’s variable is easier though it depends on the requirements.

Lastly, you can create a set of custom functions in XSL template and call them from your RTF Template as external functions. This is very useful especially when you have common logic’s that contain custom functions or calculations and can be used in many different BI Publisher’s text forms or different templates, yet don’t want to maintain them in each text form or each template. Once you have developed a single XSL template where you create such logics or functions then you can import the XSL template from any of your RTF template and call any of the custom function to do the same process. 

/by
, , , ,

R12 – How to Find XML Publisher Version

Method One: MetaInfo.class

The version of a specific Java Class has a one to one relationship with the currently installed version of Oracle XML Publisher.

/home/applprod >strings $OA_JAVA/oracle/apps/xdo/common/MetaInfo.class|grep -i ‘XML Publisher’

Oracle XML Publisher 5.6.3

/home/applprod >

Version 5.6.3 ships with update pack 12.1.3, which is Patch 8919491

“Patch R12.ATG_PF.B.delta.3: Oracle Applications Technology 12.1.3 Product Family Release Update Pack”

Method Two: SQL Query

SELECT DECODE (bug_number
, ‘3554613’, ‘4.5.0’
, ‘3263588’, ‘XDO.H’
, ‘3822219’, ‘5.0.0’
, ‘4236958’, ‘5.0.1’
, ‘4206181’, ‘5.5.0’
, ‘4561451’, ‘5.6.0’
, ‘4905678’, ‘5.6.1’
, ‘5097966’, ‘5.6.2’
, ‘5472959’, ‘5.6.3’) PATCH, bug_number
FROM ad_bugs
WHERE bug_number IN
(‘3554613′
, ‘3263588’
, ‘3822219’
, ‘4236958’
, ‘4206181’
, ‘4561451’
, ‘4905678’
, ‘5097966’
, ‘5472959’);

Method Three: Output PDF of the report (Document Properties)

doc_properties

 

/by
, , ,

How to use If Else Conditions in XML Publisher RTF Template

XML Publisher supports the common programming construct “if”,”if-then-else”. This is extremely useful when you need to test a condition and conditionally show a result.

We can use if condition directly by writing <?IF?> condition <?END IF?>

Example:
<?if:P_PM_YN=’N’?> Yes <?end if?>
But for If-else we need to use extended function.
 
Method 1:

<?xdofx:if element_condition then result1 else result2 end if?>

Example:

<?xdofx:if INVOICE_AMOUNT > 5000 then ’Higher’
else
if INVOICE_AMOUNT <3000 then ’Lower’
else
’Equal’
end if?>
 
Method 2:
Syntax:

<?xdoxslt:ifelse(condition,true,false)?>

Example: 

<?xdoxslt:ifelse(20=21,’yes 20 and 21 are equal’,’No 20 and 21 are not equal’)?>

Ans: No 20 and 21 are not equal

 

Using OR Condition in XML RTF Template:

Syntax:

<?if:XMLfield=value1 or XMLfield=value2?> display value <?end if?>

Example:

<?if:sum(AVALUE)=0 or sum(BVALUE)=0?>0<?end if?>

You can use whichever is applicable to your requirement.
/by
, , ,

R12 – How to Delete XML Publisher Data Definition and Template

How often do you create a XML publisher definition with a wrong Codes (Template or Data Definition)? Or you want to change the Code so that it is more meaningful?
Due to some typo error or to give some more meaningful name as per the standards, you can’t change those fields later. Also you can’t delete them too. Oracle recommends to disable them by giving an end date. But many developers don’t like to leave the wrong stuff in the system. They better like to delete them and freshly recreate them.

Why Oracle has restricted to Update or Delete?
The reason is concurrent program with XML output matches the Short Name with the template Code to find out which XML Publisher template to use for post processing. If you delete this template, the Post Processor cannot find the template, and then give errors. So it is always better not to give an option to update or delete.

But we have this workaround to update or delete the template or data definitions using scripts. It is highly recommend to run first in development instances.

Before the workaround, let’s look at the main tables that store the information of the Data Definitions and Templates.

1. XDO_DS_DEFINITIONS_B: table for storing data source definition represented by XML Schema Definition (XSD). Each data source has one or more elements, and this information are stored in XDO_DS_ELEMENTS_B.
2. XDO_DS_DEFINITIONS_TL: translation table for XDO_DS_DEFINITIONS_B.
XDO_LOBS: This table is used for storing locale (language and territory) sensitive binary and text files. It is mainly used for storing language layout templates.
3. XDO_CONFIG_VALUES: stores the values of XML Publisher configuration properties entered from the Oracle Applications interface.
4. XDO_TEMPLATES_B: table for template information. Each template has a corresponding data source definition stored in the XDO_DS_DEFINITIONS_B. Each translation of a certain template, not each template, has a corresponding physical template file. The physical template file information is stored in the XDO_LOBS.
5. XDO_TEMPLATES_TL: translation table for XDO_TEMPLATES_B.

–Code Tested in R12.1.3 Instance
SET SERVEROUTPUT ON;
DECLARE
— Change the following two parameters
var_templateCode    VARCHAR2 (100) := ‘ORACLEERPAPPSGUIDE’;     — Template Code
boo_deleteDataDef   BOOLEAN := TRUE;     — delete the associated Data Def.
BEGIN
FOR RS
IN (SELECT T1.APPLICATION_SHORT_NAME TEMPLATE_APP_NAME,
T1.DATA_SOURCE_CODE,
T2.APPLICATION_SHORT_NAME DEF_APP_NAME
FROM XDO_TEMPLATES_B T1, XDO_DS_DEFINITIONS_B T2
WHERE T1.TEMPLATE_CODE = var_templateCode
AND T1.DATA_SOURCE_CODE = T2.DATA_SOURCE_CODE)
LOOP
XDO_TEMPLATES_PKG.DELETE_ROW (RS.TEMPLATE_APP_NAME, var_templateCode);

DELETE FROM XDO_LOBS
WHERE     LOB_CODE = var_templateCode
AND APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
AND LOB_TYPE IN (‘TEMPLATE_SOURCE’, ‘TEMPLATE’);

DELETE FROM XDO_CONFIG_VALUES
WHERE     APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
AND TEMPLATE_CODE = var_templateCode
AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
AND CONFIG_LEVEL = 50;

DBMS_OUTPUT.PUT_LINE (‘Selected template has been ‘ || var_templateCode || ‘ deleted.’);

IF boo_deleteDataDef
THEN
XDO_DS_DEFINITIONS_PKG.DELETE_ROW (RS.DEF_APP_NAME,
RS.DATA_SOURCE_CODE);

DELETE FROM XDO_LOBS
WHERE LOB_CODE = RS.DATA_SOURCE_CODE
AND APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
AND LOB_TYPE IN
(‘XML_SCHEMA’,
‘DATA_TEMPLATE’,
‘XML_SAMPLE’,
‘BURSTING_FILE’);

DELETE FROM XDO_CONFIG_VALUES
WHERE     APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
AND CONFIG_LEVEL = 30;

DBMS_OUTPUT.PUT_LINE (
‘Selected Data Defintion has been ‘ || RS.DATA_SOURCE_CODE || ‘ deleted.’);
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE (
‘Unable to delete XML Publisher Template ‘ || var_templateCode);
DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM, 1, 200));
END;
/

/by