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. 

When you want to view the status diagram of workflow process on status monitor page from the workflow administrator responsibility, maybe you get the error page like below:
The solution for the above issue is set the Server Timezone profile option on the Site level.
After that you can able to see the status diagram in workflow status monitor.
 
Sometimes we will face below issues in R12.

Issue 1:
You have insufficient privileges for the current operation. Please contact your System Administrator.

You cannot run a page which is not Self Secured when the MAC fails.

Resolution:
 
Set the below 3 profiles to None
1) FND Function Validation Level
2) FND Validation Level
3) Framework Validation Level
JDev-2
Issue 2:
Unexpected URL parameters have been detected and will be ignored Solution:
 
Resolution:

In JDeveloper, go to menu Tools–>Embedded OC4J Server Preferences–>Global–>Startup–>Select Default Local IP Address.
JDev-3
 
 
Autoconfig log file:
Apps:
$INST_TOP/appl/$CONTEXT_NAME/admin/log/$MMDDHHMM/adconfig.log

Db:
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/adconfig.log $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/NetServiceHandler.log

Startup/Shutdown Log files:

$INST_TOP/logs/appl/admin/log

Apache, OC4J and OPMN:

$LOG_HOME/ora/10.1.3/Apache 
$LOG_HOME/ora/10.1.3/j2ee 
$LOG_HOME/ora/10.1.3/opmn

Patch log:

$APPL_TOP/admin/$SID/log/
Workflow Mailer log:
$APPLCSF/$APPLLOG/FNDCPGSC*.txt

Concurrent log:

$INST_TOP/apps/$CONTEXT_NAME/logs/appl/conc/log
OAM (Oracle Application Manager) log:
$APPLRGF/oam/

Clone log:

Preclone log files in source instance

Apps:
$INST_TOP/apps/$CONTEXT_NAME/admin/log/ (StageAppsTier_MMDDHHMM.log)

Db:
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/(StageDBTier_MMDDHHMM.log)

Clone log files in target instance:

Apps :
$INST_TOP/apps/$CONTEXT_NAME/admin/log/ApplyAppsTier_.log

Db:
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDBTier_.log

– Alert Log File:
$ORACLE_HOME/admin/$CONTEXT_NAME/bdump/alert_$SID.log

Concurrent OUT Directory:
$APPLCSF/$APPLOUT  or $APPLCSF/out
APPL_TOP – this is the top level directory for the Applications
  • $APPLCSF – the top level directory where the concurrent manager stores the log and out files of concurrent requests
  • $APPLCSF/$APPLLOG – concurrent request log files( As describe above)
  • $APPLCSF/$APPLOUT – concurrent request out files( As describe above)
  • $APPLTMP – Applications temporary files
  • $APPLPTMP – PL/SQL temporary files   or /usr/tmp (default tmp directory)
Startup and shutdown messages:
–Startup/Shutdown error message related to tech stack (10.1.2, 10.1.3 forms/reports/web)
$INST_TOP/logs/ora/ (10.1.2 & 10.1.3)
$INST_TOP/logs/ora/10.1.3/Apache/error_log[timestamp]
$INST_TOP/logs/ora/10.1.3/opmn/ (OC4J~…, oa*, opmn.log)$INST_TOP/apps/$CONTEXT_NAME/logs/ora/10.1.2/network/ (listener log)
$INST_TOP/logs/appl/conc/log (CM log files)
Other log files in R12

1) Database Tier
1.1) Relink Log files :
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME /MMDDHHMM/ make_$MMDDHHMM.log

1.2) Alert Log Files :
$ORACLE_HOME/admin/$CONTEXT_NAME/bdump/alert_$SID.log

1.3) Network Logs :
$ORACLE_HOME/network/admin/$SID.log

1.4) OUI Logs :
OUI Inventory Logs :
$ORACLE_HOME/admin/oui/$CONTEXT_NAME/oraInventory/logs

2) Application Tier

$ORACLE_HOME/j2ee/DevSuite/log
$ORACLE_HOME/opmn/logs
$ORACLE_HOME/network/logs

Tech Stack Patch 10.1.3 (Web/HTTP Server)
$IAS_ORACLE_HOME/j2ee/forms/log
$IAS_ORACLE_HOME/j2ee/oafm/log
$IAS_ORACLE_HOME/j2ee/oacore/log
$IAS_ORACLE_HOME/opmn/logs
$IAS_ORACLE_HOME/network/log
$INST_TOP/logs/ora/10.1.2
$INST_TOP/logs/ora/10.1.3
$INST_TOP/logs/appl/conc/log
$INST_TOP/logs/appl/admin/log

Patching related log files in R12 :
Application Tier– adpatch log – $APPL_TOP/admin//log/
Developer (Developer/Forms & Reports 10.1.2) Patch – $ORACLE_HOME/.patch_storage
Web Server (Apache) patch – $IAS_ORACLE_HOME/.patch_storage
Database Tier opatch log – $ORACLE_HOME/.patch_storage
Error:Delete/Purge COMPLETED concurrent requests

Concurrent request history should not exceed 50K rows, or else it will start impacting on performance

SQL> select count(*) from FND_CONCURRENT_PROCESSES;

COUNT(*)
———-
552

SQL> select count(*) from FND_CONCURRENT_REQUESTS;

COUNT(*)
———-
85009 ==>>> Should always be below 50K, Else run “Purge Concurrent Request”
SQL>

SQL> select count(*) from FND_CONCURRENT_REQUESTS

2 where request_date > sysdate-7;

COUNT(*)
———-
19757

SQL> col “Min request|Date” for a20;

SQL> col “Max request|Date” for a20;

SQL> select min(request_date) “Min request|Date”,max(request_date) “Max request|Date” from FND_CONCURRENT_REQUESTS;

Min request|Date Max request|Date
——————– ——————–
18-NOV-09 01-NOV-11

SQL>

To clean old history of “COMPLETED” requests, follow the below note, to purge the queue

Concurrent Processing Tables and Purge Concurrent Request and/or Manager Data Program (FNDCPPUR) [ID 104282.1]

System Administrator -> Requests -> Run

Select “Single Request”

Mode = 14 days – delete all older than 14 days. Don’t make this window to small.

To view the above submitted request, do the following

Enter the request ID and “Find”

While Running:

Status on successful completion is shown below “Completed”, “Normal”


Extract from the Log File:

FNDCPPUR module: Purge Concurrent Request and/or Manager Data
+—————————————————————————+
Current system time is 01-NOV-2011 12:57:03
+—————————————————————————+
Purged 46616 entrie(s) from FND_CONCURRENT_REQUESTS :-01-NOV-2011 13:07:22

Purged 0 entrie(s) from FND_FILE_TEMP :-01-NOV-2011 13:07:22

Purged 7082 entrie(s) from FND_CRM_HISTORY :-01-NOV-2011 13:07:25

Purged 0 entrie(s) from FND_TM_EVENTS :-01-NOV-2011 13:07:25

Purged 346 entrie(s) from FND_TEMP_FILES :-01-NOV-2011 13:07:25

Purged 0 entrie(s) from FND_ENV_CONTEXT :-01-NOV-2011 13:07:46

Purged 1 entrie(s) from FND_DUAL :-01-NOV-2011 13:07:46

Purged 2 entrie(s) from FND_CONFLICTS_DOMAIN :-01-NOV-2011 13:07:46

+—————————————————————————+
Start of log messages from FND_FILE
+—————————————————————————+