Search This Blog

Monday, April 29, 2013

Oracle Diagnostics Tool Responsibility


The Oracle Diagnostic Tool is provided by Oracle to ease the gathering and analyzing of information from your eBusiness suite when diagnosis an existing data issue, transactional problem or setup error through a complete set up responsibility.
purpOracle Support Diagnostics benefits
Problem Avoidance: Identify and solve potential issues before suffering their symptoms
Formatted Output: Conveniently display the information gathered, the finding of the analysis and appropriate action to take if necessary
Ease to use: This tool is easy to use and is designed for both the functional and technical user. Identify and solve existing issues without external assistance
Self Services Resolution: Resolving Problem without the need to contact oracle support
Reduction in resolution time: minimizing the time spent to resolve an issue by increasing support engineer as well as end user efficiency
No Technical skill required: No special SQL*Plus/operating system access required.
Testing Hub: Because of Central location of all tests, it is the hub for all Diagnostic testing.
yel Important Points
  • The diagnostic tool does not alter the data or setup in your system i.e. no updates, inserts, or deletes happen.
  • Sensitive customer information is not collected or displayed.
  • The diagnostic tool are organized into one of the following groups
ora Different type performed at central Place
These are test performed:
  • Setup
Gather information about setup relevant to a particular product / functionality / issue, examine that information, provide feedback, and suggest appropriate actions
  • Activity
Gather information about data and configuration relevant to a particular functionality / issue, examine that information, provide feedback, and suggest appropriate actions
  • Collection
Gather information relevant to a particular product / functionality / issue
  • Functional
Categories typically align with the product menu structure and include both activity and data collection tests.
red How many tests are available?
  • In R11i , there are more than 300+
  • In R12, there are 100 diagnostic tests available
How to get started
The diagnostic tests are delivered in a standard Oracle patch and area applied using adpatch utility.
Support diagnostic patches may be safely applied to any instance without integration and functional testing. As noted above, they do not alter data or configuration
Once required patch is applied then you assign the "Oracle Diagnostic Tool" Responsibility which is seeded responsibility that comes to EBS suite, last year and accessible to all EBS user.
resposniblity
Once you login to the system, you will notice, that is only one menu inside that responsibility as fig 2:
menu
Fig :2 Menu under responsibility
Once you click to the menu, it will call a self-services page that will have all list of test available with respect to application module. The good is that Oracle does provide test for most of the popular modules for diagnostic.
page
Fig :3 List of product
1USAGE DEMONSTRATION : AP Invoice
Let's do a test; here I am starting with Payable. Once you click to application payable, you will find the available test within the Payable module. Each application would have three different set up test as discussed earlier, viz:
  • Setup
  • Activity
  • Data Collection
These activities are listed on the left hand side, where as right hand area will have the parameter form to run the report as figure below:
4

invoicerun

report
once you click to report, we get the entire report which looks like:
testreport
There are three options available for the user, either they can print, save or upload the same to support.
options
If you still use Oracle Diagnostics framework you need to:
1. Click on the 'Print' button in the output
2. When a well formatted html page containing the output is generated save it in your local machine using your browser standard functionality to save a page
3. Upload the saved page (output) to Metalink updating the TAR if required.

2 USAGE DEMONSTRATION :Payable SETUP
setup
Once you passed the correct Responsibility, you will get the report irrespective of its failure or success:
failedtest
Once you click the report, you will find all error and warning message , as reported by executing the
outcomereport
Once you click the report, you will find all error and warning message , as reported by executing the :
The first part of the section will have patch level :
patchleval
Will take some more in another post.Keep watching this space.:)

Thursday, April 25, 2013

XML Bursting


Bursting (Email report output) in XML Publisher reports

XML Bursting is a feature available in Oracle by which a XML is exploded into different parts based on a defined logic and the individual parts are again converted into reports based on a layout and sent via email to designated mailboxes.
This is a very helpful feature especially if you are trying to send the output of a report to an email address or multiple email address
Benefits of XML bursting
  1. The feature is standard for XML Publisher version 5.6.3
  2. No coding is required
  3. Output is sent via email. User does not need to login to Oracle to get the report
  4. The main report template and the bursting templates can be different from each other. Thus there can be one output from the concurrent program and a different layout to send as email.

Demonstration of XML bursting

We created a XML Publisher report with using a data template. The report has been developed to return 4 records. We shall use bursting to send the 4 records as 4 different reports to 4 email addresses. For our example we have used 1 email address across all 4.

XML Data Template
The data template is given below,
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
span class="hiddenSpellError">xml</span> version="1.0" encoding="ISO-8859-1"?>
  
  <dataTemplate name="XXEMPDET" description="Employee Details" version="1.0">
    <parameters></parameters>
    <lexicals></lexicals>
    <dataQuery>
  <sqlStatement name="Q1">
  <![<span class="hiddenSpellError">CDATA</span>[select employee_number, full_name, 'eyaray@etihad.ae' email_address, pp.namefrom per_people_x ppx, per_assignments_x pax, per_positions ppwhere pax.person_id = ppx.person_idand pax.position_id = pp.position_idand ppx.current_employee_flag = 'Y'and rownum < 5]]>
      </sqlStatement>
    </dataQuery>
      <dataStructure>
        <group name="G_EMP" dataType="VARCHAR2" source="Q1">
          <element name="EmpNo" dataType="VARCHAR2" value="employee_number"/>
          <element name="Employee_Name" dataType="VARCHAR2" value="full_name"/>
          <element name="Email" dataType="VARCHAR2" value="email_address"/>
          <element name="Position" dataType="VARCHAR2" value="name"/>
        </group>
      </dataStructure>
    </dataTemplate>

Create the concurrent program

Concurrent program registration

Register the data definition
Responsibility: XML Publisher Administrator
Navigation: Data Definition

Register data definition
Attach the concurrent program to the request group and execute the program to generate the XML output.

Concurrent request
The output shows the following,

Create the layout template

Report layout template

Attach the XML as Preview Data
Responsibility: XML Publisher Administrator
Navigation: Data Definition
Query for Code = XXEMPDET
Attach the XML generated from the program output to the Preview Data section of the data definition.

Attach XML as Preview data

Register the template
Navigation: Templates
Create a template

Register layout template
Check Preview to see if the template matches with the XML.

Execute the report to view the data

XML report output
There are 4 records for 4 employees in the report. We are displaying 2 employees for ease.
Create the bursting template
The most important aspect of XML bursting is the bursting control file. This is a XML file which contains specific tags for bursting the XML output generated earlier.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting">
  <xapi:globalData location="stream">
xapi:globalData >
  <xapi:request select="/XXEMPDET/LIST_G_EMP/G_EMP">
  <xapi:delivery>
  <xapi:email id="123" server="eymail.etihad.local" port="25" from="applprod@eyepdbe1.etihad.local" reply-to ="applprod@eyepdbe1.etihad.local">
  <xapi:message id="123" to="${EMAIL}" attachment="true" content-type="html/text" subject="Employee details for Empno ${EMPNO}">
    Dear ${EMPLOYEE_NAME},
    Please find your details attached.
    Regards,HR team
xapi:message>
  xapi:email>
  xapi:delivery>
  <xapi:document key="${EMPNO}" output="${EMPNO}.pdf" output-type="pdf" delivery="123">
  <xapi:template type="rtf" location="/tmp/XXEMPDET.rtf">
  xapi:template>
  xapi:document>
xapi:request>
xapi:requestset>
Explanation of the bursting template
XML tagExplanation
xapi:request select=This tag in the control file indicates from which tag/node in the concurrent program output XML should separated for bursting
xapi:deliveryThis node contains all email related attributes
xapi:emailThis tag contains the email attributes like,
  • server or the email server IP or name
  • port or the email server port
  • from or the email address from which the server should send the email
  • reply-to or the email address to which the replies should be sent
xapi:messageThis tag contains the attributes about the email like,
  • To or the email address where the email should be sent
  • Subject or the email subject
  • attachment , i.e. if the email should contain an attachment
  • content-type or type of email
  • The mail body will be enclosed within thetags
xapi:documentThis node contains the attachment document and XML template related tags and attributesThe attributes in this tag are related to the email attachment
  • Key or unique identifier of each attachment
  • Output or attachment file name
  • Output-type or attachment file type
xapi:templateThis node contains the template information like,
  • Type or the file type of the template
  • Location or the directory of the template file along with the file name
The following attributes should contain the same values for bursting to function properly,
1.       Id attribute in xapi:email tag
2.       Id attribute in xapi:message tag
3.       Delivery attribute in xapi:document tag
If the 3 values do not match then delivery will not take place as expected.

Attach the bursting file to the Data Definition of the XML Publisher Report
Responsibility: XML Publisher Administrator
Navigation: Data Defintions

Upload the bursting control file
Upload the bursting file in Bursting Control File section.

Change the temporary directory location.
A temporary directory has to be provided for XML Publisher engine to use for bursting.
Responsibility: XML Publisher Administrator
Navigation: Administration tab

Set temporary directory
In Unix, /tmp directory has write permissions for all users. Enter /tmp directory as the temporary directory. This directory can be any directory on which Oracle apps has write permissions.

FTP the template
FTP the layout file, i.e. the XML layout template in the /tmp directory, as we have defined in the bursting control file that the template file location is /tmp

XML layout template in /tmp directory

 

 

XML Bursting execution process

Run the XX Employee detail report to generate the output.

Execute the report
After the report is generated, execute the program named, XML Publisher Report Bursting ProgramThis program is responsible for bursting the XML with the help of the bursting control file attached to the concurrent program whose output will be burst. Ensure that this program is attached to the request group of this responsibility.

XML Publisher Report Bursting Program
Select the request of the previously run report’s request id. We shall select the 2nd request from the top, i.e. Request ID: 19011719

Concurrent request selected
Execute the bursting program

Bursting program executed
When the bursting program ends, the program name in the SRS form changes as shown below

Bursting program name changed in SRS form
Now check the output of the bursting program.

Bursting program output
The bursting program output shows that 4 emails were delivered.
We shall check the mailbox now. Remember that we had set the email address of all the 4 employees to a single email address for this demonstration within the report query itself. You can check the data template for the query at the top.

Emails sent by bursting program
Each mail body looks like the following,

Email body
The email body, attachment name and attachment file type match with the bursting control file above. The attachment will contain the data from the XX Employee detail report,

Attachment
The bursting process is now complete.
Automatic execution of XML Publisher Report Bursting Program
Since the bursting program, XML Publisher Report Bursting Program, has to be run manually this might not be feasible for all users to use. The bursting program can be executed automatically if we use a rdf report to generate the data for the XML publisher report. In a rdf report we can add the following piece of code in the After Report trigger in the report to execute the bursting program as soon as the output has been generated by the report.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
FUNCTION afterreport
   RETURN BOOLEAN
IS
   v_req_id   NUMBER;
BEGIN
      -- Call Bursting Program
      v_req_id :=
  fnd_request.submit_request (application => 'XDO',
  program => 'XDOBURSTREP',
                                     description      => '',
                                     start_time       => '',
                                     sub_request      => FALSE,
  argument1 => 'Y',
  argument2 => :p_conc_request_id,
  argument3 => 'Y'
  --CHR(0),
                                    --''
                                    );
      IF v_req_id = 0
      THEN
         srw.MESSAGE (123, 'Failed to call bursting program.');
      END IF;
  srw.USER_EXIT ('FND SRWEXIT');
   RETURN (TRUE);
END;

Bursting program error
The XML Publisher Bursting program might error out. The log file will talk about checking the Output Post processor logs. I have described how to get the Output Post Processor logs in a previous article.

Bursting program log
After checking the Output Post Processor logs we get the following,
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
[3/22/12 2:19:05 PM] [320381:RT19011722] Completed post-processing actions for request 19011722.
[3/22/12 2:33:10 PM] [OPPServiceThread0] Post-processing request 19011756.
[3/22/12 2:33:10 PM] [320381:RT19011756] Executing post-processing actions for request 19011756.
[3/22/12 2:33:10 PM] [320381:RT19011756] Starting XML Publisher post-processing action.
[3/22/12 2:33:10 PM] [320381:RT19011756]
Template code: BURST_STATUS_REPORT
Template app: XDO
Language:      en
Territory:     US
Output type:   RTF
[3/22/12 2:33:10 PM] [UNEXPECTED] [320381:RT19011756] java.lang.reflect.InvocationTargetException
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at oracle.apps.xdo.common.xml.XSLT10gR1.invokeParse(XSLT10gR1.java:566)
    at oracle.apps.xdo.common.xml.XSLT10gR1.transform(XSLT10gR1.java:231)
    at oracle.apps.xdo.common.xml.XSLTWrapper.transform(XSLTWrapper.java:182)
    at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:1044)
    at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:997)
    at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:212)
    at oracle.apps.xdo.template.FOProcessor.createFO(FOProcessor.java:1665)
    at oracle.apps.xdo.template.FOProcessor.generate(FOProcessor.java:975)
    at oracle.apps.xdo.oa.schema.server.TemplateHelper.runProcessTemplate(TemplateHelper.java:5926)
    at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3458)
    at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3547)
    at oracle.apps.fnd.cp.opp.XMLPublisherProcessor.process(XMLPublisherProcessor.java:290)
    at oracle.apps.fnd.cp.opp.OPPRequestThread.run(OPPRequestThread.java:157)
Caused by: org.xml.sax.SAXParseException: : XML-20108: (Fatal Error) Start of root element expected.
    at oracle.xdo.parser.v2.XMLError.flushErrorHandler(XMLError.java:441)
    at oracle.xdo.parser.v2.XMLError.flushErrors1(XMLError.java:303)
    at oracle.xdo.parser.v2.NonValidatingParser.parseRootElement(NonValidatingParser.java:343)
    at oracle.xdo.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:285)
    at oracle.xdo.parser.v2.XMLParser.parse(XMLParser.java:289)
    ... 17 more
[3/22/12 2:33:10 PM] [320381:RT19011756] Completed post-processing actions for request 19011756.
This error relates to the template in the server. It means that XML processor is unable to find the template. Check the setting in the bursting control file,
  
The location specified as the XML layout template directory should be valid and all permissions must be available on this file as well as the directory.