Difference Between Delete and Truncate and Drop.

DELETE

• DELETE is a DML statement.
• DELETE removes some rows if WHERE clause is used
• DELETE keeps records in buffers(temporary storage) till the first ROLLBACK or COMMIT. 
• Can be rolled back
• Can be used with or without WHERE clause
• Does not reset identity of the table
• Triggers will be fired.

When DELETE operation is performed, all the data get copied into Rollback Tablespace first,
and then delete operation get performed. Hence we can get back the data by ROLLBACK command.

SYNTAX:
To delete a particular row

DELETE FROM table_name
WHERE column_name = column_value

To delete all rows

DELETE FROM table_name
Or
DELETE * FROM table_name

DROP

1. DROP is a DDL statement.
2. Removes a table from the database. Table structures, indexes, privileges, constraints will
also be removed.
3. Cannot be rolled back
4. No Triggers will be fired.

SYNTAX:
DROP TABLE table_name

TRUNCATE
1. TRUNCATE is a DDL Statement.
2. Removes all rows from a table, but the table structures and its columns, constraints, indexes
remains.
3. Cannot be rolled back
4. Resets the identity of the table
5. Truncate is faster and uses fewer system and transaction log than delete.
6. Cannot use TRUNCATE on a table referenced by a FOREIGN KEY constraint.
7. No Triggers will be fired.
8. Cannot use WHERE conditions.
9.TRUCATE is faster and doesn't use as much undo space as a DELETE.

SYNTAX:
TRUNCATE TABLE table_name

AIM Documentation details for Technical Resource.

AIM (Application Implementation Methodology) Documents.

Oracle A.I.M. Methodology encompasses a project management methodology with documentation templates that support the life cycle of an implementation. The life cycle methodology and documentation templates allows A.I.M. to be a very useful tool for managing implementation projects successfully.

MD50, MD70 and MD120 are part of AIM documents.

MD50:

This document specifies various functional specification required for Techies to understand the functional requirement of Form or Report Customization and design.. This is prepared by Functional consultant to clarify the spec to Techies. For any component like Report, Interface etc we have to first get the requirements as to what is the source , destinations, what data should be moved and what functional validations would be required etc. All this would go into the MD050. Generally written by a non-technical people like Business Analysts or Functional Consultants.

MD70:

This is technical specification prepared by the programmer based on MD50. This provides tech specs, PLSQL code for the Report and Form and helps to understand and modify if required in future. MD070 is the technical document that is written to fulfill the functional requirement specified in MD050. It includes the approaches you take, pseudo code, validations, Data Sources, SQL Statements etc.

MD120:

MD120 is installation steps Prepared by programmer for the code migration. MD120 mainly consists of the below steps
1. Pre-Installation Steps
2. Installation Steps
3. Verification Checklist

Introduction to Oracle Forms.

Oracle Forms (part of the Oracle Developer Suite - formerly called Developer 2000 or D2K) is a Rapid Application Development (RAD) environment for developing database applications. The language used inside is PL/SQL.

The fundamental concepts in Forms are:

A canvas is an area for “painting” the various items and labels that provide the user interface.
A block is often based on, and corresponds to, a database table. Forms is most useful when used to provide operations that conform to the fundamental ‘SELECT, INSERT, UPDATE, DELETE’ concepts.
Coding generally hangs off triggers based around these events, or around user events (such as pressing a button or moving to a different field).

Components of Oracle Workflow.

Workflow Builder
Workflow Engine
Workflow Definitions Loader
Notification Systems
Workflow Monitor
Notification systems
Business Event System's
Workflow XML Loader
Workflow Directory Service's.

Workflow Builder :Work Flow builder is a Graphical interface to create and modify a business process with simple drag and drop operations.
It has two components -
* Navigator Window
* Process Window
A workflow builder would be used by a person to design and modify a workflow. It is more of a designer’s tool rather than an end-user’s tool.

Workflow Engine : The Workflow Engine embedded in the Oracle8 server monitors workflow states and coordinates the routing of activities for a process.Changes in workflow state, such as the completion of workflow activities, are signaled to the engine via a PL/SQL API or a Java API. Based on flexibly–defined workflow rules, the engine determines which activities are eligible to run, and then runs them. The Workflow Engine supports sophisticated workflow rules, including looping, branching,parallel flows, and sub-flows.

Workflow Definitions Loader :The Workflow Definitions Loader is a utility program that moves workflow definitions between database and corresponding flat file representations.It allows opening and saving workflow definitions in both a database and file.

Notification System : Each notification includes a message that contains all the information a user needs to make a decision.

Workflow Monitor : The Workflow Monitor displays an annotated view of the process diagram for a particular instance of a workflow process.Users can get a graphical depiction of their work item status. It also displays a separate status summary for the work item, the process, and each activity in the process.

Overview of Discoverer.

Oracle Discoverer is a business intelligence tool to support organizational decisions and data will show in the form of excel format.

Components of discoverer:-

1.Discoverer Adminstration Edition

2. Discoverer Desktop Edition

Architecture Of Discoverer Administartion Edition:-

i.End User Layer

ii.Business Area

iii.Business Folders

What is the End User Layer(EUL?

The End User Layer (EUL) is the meta data (i.e. data about the actual data in a database) that is simple and easy for users to understand. You use Discoverer Administration Edition to create, customize, and maintain this view for your users so they can easily access data in Discoverer Plus. You must have access to at least one EUL in order to use Discoverer Administration Edition or Discoverer Plus. 

What is  Business Area?

A business area is a collection of related information in the database.

A business area is a set of related information with a common business purpose

For example, information about Sales may be stored in one business area, while
information about Cops is stored in another business area.

In simple words it can be termed as collections of objects in a particular module

 What is Business Folders:-

-Simple Folders – Folders that are based on a database table (e.g.: ITEM)

-Custom Folders – Folders that contain a custom SQL query.

-Complex Folders – Folders that are based on multiple simple folders.

Oracle Reports Basics.

Oracle Reports Basics.

Step1: Start Connecting.

• Open the Oracle Reports Builder, go to File Menu and press Connect or execute the command Ctrl+J.
• On the second step, you need to enter the user name, password and the database name.

Step2: Create Report Wizard in Oracle Reports.

Click on Reports and the press the button Create. An alert "Create a new report" with two option will appear, and here you choose: Use the Report Wizard.

Step3: Choose The Type of Layout and Style Reports in Oracle 11g.

• Select the type of layout you would like to generate:

We have three options here.

1.  Create both Web and Paper Layout,
2.  Create Web Layout only, 
3.  Create paper Layout only.

• Next step is  to  choose the report style and give a title name for your report. 

         The report style option are: Tabular, Group Left, Group Above, Matrix, Matrix with Group.

Step 4: Selecting a Data Source and Select Sql Query Builder for Reports in Oracle.

• In this step you have to choose a Data Source for your report. The Data Source can be from JDBC Query, SQL Query, Text Query, XML Query.
• In the next window you can write the query manually or else you can use Query Builder or Import Builder.

Example Query with Explanation:

SELECT * FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1 =:P_SEGMENT1

In the above query: p_segment1 is called as bind parameter/variable.

Note: There are two types of parameters that we use in reports. Lexical Parameter and Bind Parameter

Bind Parameter: This is used to pass the values dynamically at run time.

Examples: 1) If you want to see only one item from your inventory and you want to select that item at the time of running report. 2) If you want select only particular department employees and you want to select that department at runtime.

Lexical Parameter: This parameter is used to build the query dynamically.

Examples: If you have two users A, B will be running the report and if use A want to see only columns 1, 2, 3 where as User B want to see columns 2, 3, 4 in that case we build the query dynamically using lexical parameters

• Select the fields that you would like to display in your report. You can move one item or all items to target from Available Fields in Displayed Fields.
• Select the fields that you would like to calculate totals in your report. Also you can choose to calculate: sum, count, minimum, maximum, average.
• In the Next screen here you can modify the labels of the fields and also the width for each field.
• Next Step is to select the template for your report. You have multiple options: Predefined Template, Template File or No Template

Now you are done with describing report.

Step 5:Run Module Report Editor Paper Design.

• Select the template for your report. You have multiple options: Predefined Template, Template File or No Template.

Basic Concepts of R12 Oracle Project Accounting.

Project: A project is a primary unit of work that can be broken down into one or more tasks.

Task: It is the small unit of work created under the project against which transaction can be created.

Note: we cannot create transaction/expenditure items directly on the project, hence it is mandatory for each project to have at least one task.

Events in Project Accounting:

Tasks

Customer&Contacts

Agreement&Funding.

Events(Performance Bonus,Wrtie-On)

Budget

Expendeture.

Key Members.

Trasanction Control

Oracle Projects consists of the following products:

Oracle Project Costing

Oracle Project Billing

Oracle Project Resource Management

Oracle Project Management

Oracle Project Collaboration

Oracle Daily Business Intelligence for Projects

Oracle Project Portfolio Analysis.

Note:Oracle Project Costing&Oracle Project Billing comes by default with software and other products are licence based and charged extra.

SQL Command Categories

Below are the SQL commands which are grouped into four major categories depending on their functionality. They are as follows:

Data Definition Language (DDL)

These SQL commands are used for creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.

Data Manipulation Language (DML)

These SQL commands are used for storing, retrieving, modifying, and deleting data. These commands are SELECT, INSERT, UPDATE, and DELETE.

Transaction Control Language (TCL)

These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.

Data Control Language (DCL)

These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE.

Basics of Oracle Pl/Sql.

PL/SQL stands for Procedural Language extension of SQL.
PL/SQL is a combination of SQL along with the procedural features of programming languages.

It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.
Oracle uses a PL/SQL engine to processes the PL/SQL statements.A PL/SQL language code can be stored in the client system (client-side) or in the database (server-side).

PL/SQL Block consists of three sections:

• The Declaration section (optional).
• The Execution section (mandatory).
• The Exception Handling (or Error) section (optional).

Declaration Section:

The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE. This section is optional and is used to declare any placeholders like variables, constants, records and cursors, which are used to manipulate data in the execution section. Placeholders may be any of Variables, Constants and Records, which stores data temporarily. Cursors are also declared in this section.

Execution Section:

The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END. This is a mandatory section and is the section where the program logic is written to perform any task. The programmatic constructs like loops, conditional statement and SQL statements form the part of execution section.

Exception Section:

The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION. This section is optional. Any errors in the program can be handled in this section, so that the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions that cannot be handled, the Block terminates abruptly with errors.

Every statement in the above three sections must end with a semicolon ; . PL/SQL blocks can be nested within other PL/SQL blocks. Comments can be used to document code.

Sample PL/SQL Block:

DECLARE 

     Variable declaration

BEGIN 

     Program Execution 

EXCEPTION 

     Exception handling

END;

Most Useful Shortcut Keys in Toad.

Toad Shortcut Keys.

Editor:

CTRL+B    Comment block
CTRL+E    Execute Explain Plan on the current statement.
ALT+UP    Display previous statement
ALT+DOWN  Display next statement (after ALT+UP)
CTRL+M    Make code statement.

CTRL+T              Display pick list drop-down
CTRL+F9            Verify statement without execution (parse) in the Editor(Query Describe)
CTRL+F12           Pass the SQL or Editor contents to the specified external editor.
CTRL+ENTER    Execute current SQL (same as SHIFT+F9)
F2         Toggle full screen Editor
F5         Execute as script.
F6         Toggle between Editor and Results panel
F7         Clear all text, trace into the Editor
F8         Recall previous SQL statement in the Editor
F9         Execute statement in the Editor
CTRL+F     Find text
CTRL+G     Go to line number
CTRL+R     Find and replace.

General:

F4        Immediately describe object in popup window.
F10       Display right-click menu
CTRL+TAB  Cycle through a collection of "child windows" or tabs in a window.

Workflow Basics.

Components of Oracle Workflow

1. Workflow Builder

2. Workflow Engine

3. Workflow Definitions Loader

4. Notification Systems

5. Workflow Monitor

About Workflow Builder:

Work Flow builder is a Graphical interface to create and modify a business process with simple drag and drop operations. It has two components -
1 Navigator Window
2 Process Window
A workflow builder would be used by a person to design and modify a workflow.

About Workflow Engine:

The Workflow Engine embedded in the Oracle server monitors workflow states and coordinates the routing of activities for a process. Changes in workflow state, such as the completion of workflow activities, are signaled to the engine via a PL/SQL API or a Java API. Based on flexibly–defined workflow rules, the engine determines which activities are eligible to run, and then runs them. The Workflow Engine supports sophisticated workflow rules, including looping, branching, parallel flows, and sub-flows.

About Definitions Loader:

The Workflow Definitions Loader is a utility program that moves workflow definitions between database and corresponding flat file representations.
The workflow can be downloaded/uploded using the concurrent program “Workflow Definitions Loader” or from Unix box using the WFLOAD command.

What is the difference between Interface and Conversion?

What is the difference between Interface and Conversion?

What is the Difference between Interfaces and API.

Interface Vs. Application Program Interface (API):

-->Interfaces are used to transfer the data from legacy system to Oracle Application system .

-->API is used to convert the data from one form to another form with in the Oracle Application Module

An Introduction to Basics of Interfaces in Oracle Apps.

What is Oracle Apps Conversion?

Conversion is one of the program which will be used to upload the data from legacy system in to oracle applications, base tables and it is one time data transfer.

What is Oracle Apps Interface?


Oracle provides flexible and flexible tools in the form of Interface programs to import the master and transaction data like Customers, Invoices, and Sales Orders etc from external systems into Oracle Applications.

IMPORTING SALES ORDERS AND/OR RETURNS.

What we can import?
New sales orders,Changed sales orders,Completed sales orders,Returns sales orders..etc.

From Where we can import?
From legacy applications...

1. Through EDI Transactions processed through the Gateway.
2. Internal orders created from Oracle Purchasing’s internal requisitions, or returns.

What are Interfaces?

·         Interfaces are used in Oracle Applications to integrate external systems and Data Conversion.
·         The interfaces are mainly used to either transfer data from Oracle Applications to a flat file or data from legacy system to Oracle Applications.
·         Used extensively at the time of Data Conversion from legacy/ old systems to a fresh implementation of Oracle Applications.
·         Used also at regular intervals when data transfer is from other live systems if the systems are not defined in Oracle Applications implementation.
·         Oracle provides flexible and flexible tools in the form of Interface programs to import the master and transactional data like Customers, Invoices, and Sales Orders etc from external systems into Oracle Applications.

Types of Interfaces

There are two major types of Interfaces:

·         Inbound Interface : These interfaces are used to transfer data from external systems to Oracle Applications.

·         Outbound Interface :  These interfaces are used to transfer data from Oracle Applications to external systems.

Two other distinctions of Interfaces:

·         Open Interface: If the interface logic is provided by Oracle Applications, it is called an Open Interface.

·         Custom Interface: If the interface logic needs to be developed by the implementation team, it is called a Custom Interface.

Interface Components

Open Interface Logic

·         First the data from the source application is loaded into a database table (called Interface table).

·         Then the provided validation program logic validates the records whether they are correct or not .

·         If the validation fails, the errors are transferred into another table (called Error Table).

·         If the validation succeeds, the correct records are transferred through a process into the destination application table.

Components of an Interface

a] Source Application:
You obtain data from a source application to pass on to a destination application for further processing and/or storage.
b] Source Data Issues:
Type of file, Size, Frequency of upload, Record Length (Variable or fixed), Delimiter, Datatype for each field, Any unwanted data, Naming convention and uniqueness of file, Location of the file, Access on the file.
c] Destination Application:
You send data to a destination application so that the application can perform further processing and/or storage.
d] Interface Table:
For inbound interfaces, the interface table is the intermediary table where the data from your source application temporarily resides until it is validated and processed into the destination application.
e] Identifier columns: 
Uniquely identify rows in the interface table provide foreign key reference to both the source and destination applications.
f] Control Columns:

·         Control columns track the status of each row in the interface table, as it is inserted, validated, rejected, processed, and ultimately deleted.

·         WHO columns are also control columns.

g] Data Columns:

·         Stores the data that is being converted.

·         Required columns store the minimum information needed by the destination application to successfully process the interface row.

h] Derived Columns:
Derived columns are created by the destination application from information in the required columns.
i] Optional Columns:
Optional columns are not necessarily required by the destination application, but can be used by the destination application for additional value-added functionality beyond the basics.
j] Error Table:

·         For inbound interfaces, the errors table stores all errors found by the validation and processing functions.

·         In some cases, the errors table is a child of the interface table. This allows each row in the interface table to have many errors, so that you can easily manage multiple errors at once.

·         In other cases, the errors are stored in a column within the interface table, which requires you to fix each error independently.

Developing an Interface

1] Identification:
Find out if there exists an Open Interface to carry out the functionality.
2] Creation of Pre-Interface table ( staging Table):
A table in the format of the data file which can be pruned to load as clean a data into the Interface table.
3] Load data into Pre-Interface table:
SQL*LOADER can be used to load the flat file into the pre-interface table.
4] Validate data in the Pre-Interface table:
Basic validation of the data loaded into the Pre-Interface table can be carried out like:

·         For checking NULL values in required columns

·         Checking for Foreign Key and Quick Code values.

·         Duplication Validation

·         Business Rule validation

5] Mapping the values:
Generated fields in Oracle Applications can be mapped in this step to either default values or sequences.
6] Load data into Interface table:

·     Once the data is as clean as you can get it, the data can be inserted into the Interface table.

·     At such a time, certain columns, which are necessary in Applications but not found in legacy system, need to be populated accordingly like WHO columns.     

•         Run the interface program

•         Check for Errors

•         Report on the Interface

ABOUT INTERFACES:

In Oracle Apps Interfaces are generally tables, which act as a medium to transfer the data from one module to another module or to transfer the data from legacy system into Oracle Applications. There are 352 tables provided by the Oracle Package. Each module has its own Interface Tables.

A typical path to transfer the data from Legacy System to Oracle Apps:

What is Interfacing?

It is the process of converting the records from one format to another format. The main components of this interfacing are
• Transfer Program
• Interface Table and
• Import Program

A] Transfer Program:
If the source modules data are implemented in Oracle Applications then the Transfer Programs are integrated with the Package. If the source modules are implemented in external system (i.e. other than Oracle Applications) then we have to develop our own Transfer Programs. Generally these Transfer Programs are developed using PL/SQL, JAVA or SQL Loader.
What they do?

·         It maps the columns of source table with the columns of Interface Tables.

·         It performs Row Level and Column Level validations.

·         It transfers the data from Source to the Interface Table.

B] Interface Tables:
The Interface tables basically have 4 types of columns.

1.     Mandatory Columns.

2.     Conditionally Required Columns.

3.     Optional Columns.

4.     Internal Processing Columns.

Mandatory Columns:
These are the main columns which are required in the destination tables (i.e. Oracle Application Module Tables). With the help of mandatory columns only the Import Program will converts the records from source to destination.

Conditionally Required Columns:
The values for these columns are based on the values of Mandatory columns. For Example: If you are converting foreign currency transactions to INR then it as compulsory to provide conditionally required columns like Currency conversion rate, Conversion Time and Conversion Date.

Optional Columns:
These are used when a client wanted to transfer some additional information from source to destination. These are based on client’s requirement.

Internal Processing Columns:
Status and Error Message columns are called Internal Processing Columns. These are specific only to Interface Table. These columns are going to be used by the Import Program to update the status and error message, if the record fails its validation while importing from Interface Table to the Destination Table.

C] Import Program:
For all Interface Tables, Oracle Application Package is going to provide Import Programs. These are generally registered with destination modules. These Import Programs are designed using PL/SQL, JAVA, C, C++, etc.

What they do?

·         It maps the columns of the Interface Table with one or more columns in the destination table.

·         It performs row level and column level validation.

·         It imports the data from Interface Table to the Destination tables, if the records validated successfully.

·         It deletes all the successfully validated records from Interface Table.

·         If the record fails its validation then the Import Program will update the status and error message columns of Interface Table.

Oracle Reports FAQs

1. What are the different file extensions that are created by oracle reports?
Rep file and Rdf file.

2. From which designation is it preferred to send the output to the printed?
Previewer.

3. Is it possible to disable the parameter from while running the report?
Yes

4. What is lexical reference?How can it be created?
Lexical reference is place_holder for text that can be embedded in a sql statements.A lexical reference can be created using & before the column or parameter name.

5. What is bind reference and how can it carate?
Bind reference are used to replace the single value in sql,pl/sql statements a bind reference can be careated using a (:) before a column or a parameter name.

6. What use of command line parameter cmd file?
It is a command line argument that allows you to specify a file that contain a set of arguments for r20run.

7. Where is a procedure return in an external pl/sql library executed at the client or at the server?
At the client.

8. Where is the external query executed at the client or the server?
At the server.

9. What are the default parameter that appear at run time in the parameter screen?
Destype and Desname.

10. Which parameter can be used to set read level consistency across multiple queries?
Read only.

11. What is term?
  The term is terminal definition file that describes the terminal form which you are using r20run.

12. What is use of term?
  The term file which key is correspond to which oracle report functions.

13. Is it possible to insert comments into sql statements return in the data model editor?
  Yes.

14. If the maximum record retrieved property of the query is set to 10 then a summary value will be calculated?
  Only for 10 records.

15. What are the sql clauses supported in the link property sheet?
Where startwith having.

16. To execute row from being displayed that still use column in the row which property can be used?
Format trigger.

17. Is it possible to set a filter condition in a cross product group in matrix reports?
No.

18. If a break order is set on a column would it effect columns which are under the column?
No.

19. With which function of summary item is the compute at options required?
percentage of total functions.

20. What is the purpose of the product order option in the column property sheet?
To specify the order of individual group evaluation in a cross products.

21. Can a formula column be obtained through a select statement?
Yes.

22. Can a formula column refered to columns in higher group?
Yes.

23. How can a break order be created on a column in an existing group?
  By dragging the column outside the group.

24. What are the types of calculated columns available?
  Summary, Formula, Placeholder column.

25. What is the use of place holder column?
A placeholder column is used to hold a calculated values at a specified place rather than allowing is to appear in the actual row where it has to appeared.

26. What is the use of hidden column?
A hidden column is used to when a column has to embedded into boilerplate text.

27. What is the use of break group?
A break group is used to display one record for one group ones.While multiple related records in other group can be displayed.

28.. If two groups are not linked in the data model editor, what is the hierarchy between them?
Two group that is above are the left most rank higher than the group that is to right or below it.

29. The join defined by the default data link is an outer join yes or no?
Yes.

30. How can a text file be attached to a report while creating in the report writer?
By using the link file property in the layout boiler plate property sheet.

31. Can a repeating frame be careated without a data group as a base?
No.

32. Can a field be used in a report wihtout it appearing in any data group?
Yes.

33. For a field in a repeating frame, can the source come from the column which does not exist in the data group which forms the base for the frame?
Yes.

34. Is it possible to center an object horizontally in a repeating frame that has a variable horizontal size?
Yes.

35. If yes,how?
By the use anchors.
36. What are the two repeating frame always associated with matrix object?
One down repeating frame below one across repeating frame.

37. Is it possible to split the printpreviewer into more than one region?
Yes.

38. Does a grouping done for objects in the layout editor affect the grouping done in the datamodel editor?
No.

39. How can a square be drawn in the layout editor of the report writer?
By using the rectangle tool while pressing the (Constraint) key.

40. To display the page no. for each page on a report what would be the source & logical page no. or & of physical page no.?
& physical page no.

41. What does the term panel refer to with regard to pages?
A panel is the no. of physical pages needed to print one logical page.

42. What is an anchoring object & what is its use?
An anchoring object is a print condition object which used to explicitly or implicitly anchor other objects to itself.

43. What is a physical page? & what is a logical page?
A physical page is a size of a page. That is output by the printer. The logical page is the size of one page of the actual report as seen in the Previewer.

44. What is the frame & repeating frame?
A frame is a holder for a group of fields. A repeating frame is used to display a set of records when the no. of records that are to displayed is not known before.

XML Publisher FAQs

What is BI Publisher?
       A. It is a reporting tool for generating the reports. More than tool it is an engine that can be
            integrated with systems supporting the business.

    Is BI Publisher integrated with Oracle Apps?
        Yes, it is tightly integrated with Oracle Apps for reporting needs. In 11.5.10 instances xml publisher was used, in R12 we can it BI Publisher

    What is the difference between xml publisher and BI Publisher?
        Name is the difference, initially it was released on the name of xml publisher( the initial patchset), later on they have added more features and called it Business Intelligence Publisher. In BI by default we have integration with Datadefinitions in R12 instance. Both these names can be used interchangeably

    What are the various components required for developing a BI publisher report?
        Data Template, Layout template and the integration with Concurrent Manager.

    How does the concurrent program submitted by the user knows about the datatemplate or layout template it should be using for generating the output?
        The concurrent program ‘shortname’ will be mapped to the ‘code’ of the Datatemplate. Layout template is attached to the datatemplate, this forms the mapping between all the three.

    What is a datatemplate?
        Datatemplate is an xml structure which contains the queries to be run against the database so that desired output in xml format is generated, this generated xml output is then applied on to the layout template for the final output

    What is a layout template?
        Layout template defines how the user views the output, basically it can be developed using Microsoft word document in rft (rich text format) or Adobe pdf format. The data output in xml format (from Data template) will be loaded in layout template at run time and the required final output file is generated.

    What are the output formats supported by layout template?
        xls, html, pdf, eText etc are supported based on the business need.

    Do you need to write multiple layout templates for each output type like html/pdf?
        No, only layout template will be created, BI Publisher generates desired output format when the request is run

    What is the default output format of the report?
        The default output format defined during the layout template creation will be used to generate the output, the same can be modified during the request submission and it will overwrite the one defined at layout template

    Can you have multiple layout templates for a singe data template?
        Yes, multiple layouts can be defined, user has a choice here to use one among them at run time during conc request submission

    Where do you register data and layout templates?
        Layout template will be registered under xml publisher administrator responsibility>Templates tab.
        Data template will be registered under xml publisher admininstrator responsibility> Data Definitions

    I want to create a report output in 10 languages, do I have to create 10 layout templates?
        No, BI Publisher provides the required translation for your templates, based on the number of languages installed in your oracle apps environment requires outputs are provided

    What is the required installation for using BI Pub report?
        BI Publisher deskop tool has be installed. Using this tool you can preview or test the report before deploying the same on to the instance.

    How do you move your layout or data template across instances?
        xdoloader is the utility that will be used.

    What is the tool to map required data output and layout templates so that they can be tested in local machine?
        Template viewer will be used for the same.

    Which component is responsible for generating the output in xml format before applying it to layout template?
        DataEngine will take DataTemplate as the input and the output will be generated in xml format which will then be applied on layout template

    Can BI publisher reports be used in OAF pages?
        XDO template utility helper java classes are provided for the same.

    Name some business use cases for BI  reports?
        Bank EFT, customer documents, shipping documents, internal analysis documents or any transactional documents

    How do you pass parameters to your report?
        Concurrent program parameters should be passed, ensure that the parameter name/token are same as in the conc prog defn and the data template

    What are the various sections in the data template?
        Parameter section
        Trigger Section
        Sql stmt section
        Data Structure section
        Lexical Section

    What does lexical section contain?
        The required lexical clause of Key Flex field or Descriptive FF are created under this section

    What triggers are supported in Data template?
        Before report and After report are supported

    Where is the trigger code written?
        The code is written in the plsql package which is given under ‘defaultpackage’ tag of data template.

    What is the file supporting the translation for a layout template?
A. xliff is the file that supports the translation, you can modify the same as required.

  Q. How do you display the company logo on the report output?
A. Copy and paste the logo (.gif. or any format) on the header section of .rtf file . Ensure you resize per the company standards.

 How to Debug – XML Publisher Report – Log?

A.The Concurrent Request ends with Phase ‘Completed’ and Status ‘Warning’ which indicates that the Output Post Processor (OPP) failed to generate an output file. In such cases the request log file shows a generic error message indicating the the post-processing action has failed'.

 How to Modify Existing Seeded Templates Using XML Publisher?
A.Seeded templates cannot be updated or deleted. The Update and Delete icons for these templates are disabled. If you wish to modify a seeded template, duplicate it, then modify the template file of the duplicated entry. You can then End Date the seeded template if you do not wish it to be available to your users.

'

Creating Header In XML Template.

Whenever we want to display anything in header section of rtf template,we are putting the static text inside the header section of the rtf file during the rtf template design.But if we want to display the dynamic content or field value inside the header section,the ms-word will not allow us to do so.For that we have to follow following steps:

Step 1: Design the header section of the report in the report body itself under the syntax

header contents

Step 2: Call this section to the header section of the report by the syntax given below:

For your reference one screen-shot is given below