How to check Oracle EBS Table Detail

You can check the table detail using Oracle etrm utility

eTRM is a pl/sql utility that reads design information in an Oracle database and displays its output in html format.

It shows database design and dependency information for the Oracle eBusiness Suite.

Steps to get EBS table Detail

Step 1: Open Link

https://etrm.oracle.com/


Enter the name of a database object


(example: per_people_f) or a search condition (example: contract compliance) then press ‘Search’ to search for information.

Alternatively you can browse the FND Model or Oracle Data Dictionary from the FND or DBA tabs


Click Table name


You can see the description


Like this you can check all table

Now search PO_vendors Table in Oracle etrm


Creating BI Publisher Report in Oracle EBS

Prerequisites


To complete this tutorial, you should have:

1. The E-Business Suite 11.5.10 instance or higher. This tutorial is based on the E-Business Suite R12 instance.

You must have the following responsibilities:
– XML Publisher Administrator
– System Administrator

2. Java Runtime Environment (JRE) 1.4. and Microsoft .Net 2.0 on your Windows PC. 3. Microsoft Word 2000 or Microsoft Word 2003 on your Windows

Scenario:

Let’s say if you want to create simple report which display Employee information based on parameter provided by user.

Creating XML schema.

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
<?xml version="1.0"?>
<dataTemplate description="EMPLOYEEDETAILS" name="EMPLOYEEDATA">
<parameters>
<parameter name="EMP_NAME" dataType="number"/>
</parameters>
<dataQuery>
<sqlStatement name="TMP"> 
select papf.full_name FULL_NAME, papf.employee_number,papf1.full_name supervisor_name
from per_all_people_F papf
, per_all_assignments_F paaf
, per_all_people_f papf1
where papf.person_id = paaf.person_id
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_Date
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and paaf.supervisor_id = papf1.person_id
and trunc(sysdate) between papf1.effective_start_date and papf1.effective_end_Date
and (papf.employee_number=:EMP_NAME or :EMP_NAME is null ) 
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="G_EMPLOYEE" source="TMP">
<element name="FULL_NAME" value="FULL_NAME"/>
<element name="EMPLOYEE_NUMBER" value="EMPLOYEE_NUMBER"/>
<element name="SUPERVISOR_NAME" value="SUPERVISOR_NAME"/>
</group>
</dataStructure>
</dataTemplate>

Step 1: Login to EBS instance

C:\Users\bhavesh1.lakhani\Desktop\EBS Report\1.png

Step 2 Navigate to Application Developer- Application-Validate

C:\Users\bhavesh1.lakhani\Desktop\EBS Report\2.png

Step 3:

C:\Users\bhavesh1.lakhani\Desktop\EBS Report\3.png

Step 4: Click on program

C:\Users\bhavesh1.lakhani\Desktop\EBS Report\4.png

Step 5: Define concurrent program

C:\Users\bhavesh1.lakhani\Desktop\EBS Report\5.png

Step 6: Define parameter

C:\Users\bhavesh1.lakhani\Desktop\EBS Report\6.png

Step 7: Navigate to XML publisher Administrator

C:\Users\bhavesh1.lakhani\Desktop\EBS Report\7.png

Step 8: Click on Create data definition

C:\Users\bhavesh1.lakhani\Desktop\EBS tut\data definition-creating.png

Step 9: define name and Code for it and Select Application

C:\Users\bhavesh1.lakhani\Desktop\EBS tut\data definition.png

Step 10: Upload txt file in which you have created.(XML schema)

C:\Users\bhavesh1.lakhani\Desktop\EBS Report\9.png

Step 11:navigate to Template and Click on Create template

C:\Users\bhavesh1.lakhani\Desktop\EBS tut\Creating Template.png

Step 12 : Define name and code, Code should be same as your data definition and Select application and data definition.

C:\Users\bhavesh1.lakhani\Desktop\EBS tut\Template.png

Step 13: Define Responsibility or use Existing responsibility

C:\Users\bhavesh1.lakhani\Desktop\EBS Report\11.png

Step 14: I am using System Administrator Responsibilty and Copy the Request group name

C:\Users\bhavesh1.lakhani\Desktop\EBS Report\12.png

Step 15: Select Report name and your Application name

C:\Users\bhavesh1.lakhani\Desktop\EBS Report\15.png

Step 16: Click on submit

C:\Users\bhavesh1.lakhani\Desktop\EBS Report\16.png

Step 17: To run Report go to view and Click on request

C:\Users\bhavesh1.lakhani\Desktop\EBS Report\17.png

Step 18: Click on submit a new request

C:\Users\bhavesh1.lakhani\Desktop\EBS Report\18.png

Step 19: Select single request

C:\Users\bhavesh1.lakhani\Desktop\EBS Report\19.png

Step 20: enter Report name and pass Parameter and Click on submit

C:\Users\bhavesh1.lakhani\Desktop\EBS Report\20.png

Step 21:

C:\Users\bhavesh1.lakhani\Desktop\EBS Report\21.png

Step 22: To see the output Click on View output

C:\Users\bhavesh1.lakhani\Desktop\EBS Report\22.png

Step 23: Output

C:\Users\bhavesh1.lakhani\Desktop\EBS Report\23.png

In this lesson, you learned how to:

http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/xmlp_ebiz/images/bullet.gif Install Oracle XML Publisher Desktop / BI Publisher Desktop
http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/xmlp_ebiz/images/bullet.gif Set an Oracle Reports Report Output to XML
http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/xmlp_ebiz/images/bullet.gif Use Concurrent Manager to Create a Sample XML File
http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/xmlp_ebiz/images/bullet.gif Create an RTF Template
http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/xmlp_ebiz/images/bullet.gif Create a Data Definition for the Report
http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/xmlp_ebiz/images/bullet.gif Use the Template Manager to Upload the Template
http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/xmlp_ebiz/images/bullet.gif Run the Report with the Template

Reference:

http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/xmlp_ebiz/index.html