BI/XML Publisher interview questions

http://docs.oracle.com/cd/E10091_01/doc/bip.1013/b40017/T421739T481157.htm#do_regroup



---->  how to get the outer group values into inner group
ans-use XPath expressions in there.
 if you are inside the invoice loop you can still access the fields in the outer loop by modifying the form field and prefixing with ../

e.g. <?VENDOR_NAME?> becomes <?../VENDOR_NAME?>

Page Breaks <?split-by-page-break:?>

To create a page break after the occurrence of a specific element use the "split-by-page-break" alias. This will cause the report output to insert a hard page break between every instance of a specific element.

To insert a page break between each occurrence of a group, insert the "split-by-page-break" form field within the group immediately before the <?end for-each?> tag that closes the group. In the Help Text of this form field enter the syntax:

<?split-by-page-break:?>

For the following XML, assume you want to create a page break for each new supplier:

<SUPPLIER>
 <NAME>My Supplier</NAME>
 <INVOICES>
  <INVOICE>
   <INVNUM>10001-1</INVNUM>
   <INVDATE>1-Jan-2005</INVDATE>
   <INVAMT>100</INVOICEAMT>
  </INVOICE>
  <INVOICE>
   <INVNUM>10001-2</INVNUM>
   <INVDATE>10-Jan-2005</INVDATE>
   <INVAMT>200</INVOICEAMT>
  </INVOICE>
 </INVOICES>
</SUPPLIER>
<SUPPLIER>
 <NAME>My Second Supplier</NAME>
 <INVOICES>
  <INVOICE>
   <INVNUM>10001-1</INVNUM>
   <INVDATE>11-Jan-2005</INVDATE>
   <INVAMT>150</INVOICEAMT>
  </INVOICE>
…

In the template sample shown in the following figure, the field called PageBreak contains the split-by-page-break syntax:

Place the PageBreak field with the <?split-by-page-break:?> syntax immediately before the <?end for-each?> field. The PageBreak field sits inside the end of the SUPPLIER loop. This will ensure a page break is inserted before the occurrence of each new supplier. This method avoids the ejection of an extra page at the end of the group when using the native Microsoft Word page break after the group.

1.      What is a template Builder?     The Template Builder is an extension to Microsoft Word that simplifies the development of RTF templates. The Template Builder is tightly integrated with Microsoft Word and allows you to perform the following functions:•        Insert data fields•        Insert data-driven tables•        Insert data-driven forms•        Insert data-driven charts•        Preview your template with sample XML data•        Browse and update the content of form fields•        Extract boilerplate text into an XLIFF translation file and test translationsValid template file types are:eText - Inbound, eText - Outbound, PDF, RTF, XSL-FO, XSL-HTML, XSL-TEXT, and XSL-XML.

2.      To handle parameterized layoutIf the requirement is to display a different layout based on a user parameter value or a List of Value selection, then the parameter can be passed to RTF layout and conditionally display the layout.On the Report Editor you first need to define a parameter with name DeptName. If this parameter has to be associated with a List of Value (LOV), then create a LOV on the Report Editor page.Next, in the parameter definition select parameter type as “Menu” and then select the LOV from the selection.In the RTF template define a parameter using syntax - <?param@begin:DeptName?>Now when user selects a department name from the List of Value on the report viewer page, the value gets passed to RTF layer into the DeptName parameter. To display the layout based on this user selection, you can use an IF statement or a CHOOSE statement to evaluate the parameter value and call the associated sub template.Use CHOOSE statement when too many conditional tests are to be done and a default action is expected for rest of the values, for example, for each department here we have a different sub template and if the user parameter has a department name with no associated sub template, then a default sub template can be called in otherwise section.<?choose:?><?when:$DeptName=’Accounting’?><?call:tAcc?><?end when?><?when:$DeptName=’Sales’?><?call:tSales?><?end when?><?when:$DeptName=’Marketing’?><?call:tMark?><?end when?><?otherwise:?><?call:tDefault?><end otherwise><?end choose?>3.  Adding Page/Brought Forward/Carried Forward Total
Placed before the column nameà PT
<?add-page-total:pt;'TRANS_AMOUNT_REMAINING'?> 4.Display Page Total in Footer
<xdofo:show-page-total name="pt" format="99G999G999D00"/>5.Display Brought Forward Total in Header<xdofo:show-brought-forward name="pt" format="99G999G999D00"/>The values for the form fields in the template are shown in the following table:

Form Field	Syntax	Description
RtotalVar	<?xdoxslt:set_variable($_XDOCTX, 'RTotalVar', 0)?>	Declares the "RTotalVar" variable and initializes it to 0.
FE	<?for-each:INVOICE?>	Starts the Invoice group.
10001-1	<?INVNUM?>	Invoice Number tag
1-Jan-2005	<?INVDATE?>	Invoice Date tag
100.00	<?xdoxslt:set_variable($_XDOCTX, 'RTotalVar', xdoxslt:get_variable($_XDOCTX, 'RTotalVar') + INVAMT)?> xdoxslt:get_variable($_XDOCTX, 'RTotalVar')?>	Sets the value of RTotalVar to the current value plus the new Invoice Amount. Retrieves the RTotalVar value for display.
EFE	<?end for-each?>	Ends the INVOICE group.

3.PAGE BREAKIn order to have different set of page number for each customer’s document you can use a section break.Double click on the first ‘for-each’ for Customer Name text form.Change the ‘for-each’ loop to ‘for-each@section’Example:Before<?for-each:G_CUSTOMER?>After<?for-each@section:G_CUSTOMER?>Click OK.  CONDITIONAL 4.FORMATTING
The XSL code you need is:<?if:TRANS_AMOUNT_REMAINING >1000?><xsl:attribute xdofo:ctx="block" name="background-color">red</xsl:attribute><?end if?>.

5.  BARCODE
Select File à Properties à Custom Tab
In the Name field enter: xdo-font.Free 3 of 9.normal.normal – notice the name of the font must match the name of the font in the MSWord font drop downIn the Value field enter: truetype.c:\windows\fonts     \FREE3OF9.ttf  

6.SORTING:
<?sort:TRANS_AMOUNT;'ascending';data-type='text'?> 

7. Bursting
What is a bursting file ?Its an XML file which tells bursting enginea)      to split the XML File created.
b) to deliver the Documents.
b) to deliver the Documents.b) to deliver the Documents.b) to deliver the Documents.b) to deliver the Documents.Advantages of XMLP burstinga. No Additional Coding, just need to create XML bursting File. ( Isn't it exciting , otherwise we would have to write java program to split the XML File and then use XMLP delivery manager API to send emails .b. Process of Creating XML data Definition and Template remains AS – IS .

8.BLOB & CLOB

• Can’t generate xml data for CLOB more than 40 kb in report builder.

• BLOB Images are supported in XML Publisher 5.6.2 or later.

• Encode BLOB to CLOB use this following function:

• CREATE FUNCTION getbase64( p_source BLOB )

• RETURN CLOB

• IS

• v_result CLOB;

• BEGIN

• DBMS_LOB.createtemporary(lob_loc => v_result, CACHE => FALSE, dur => 0);

• Wf_Mail_Util.EncodeBLOB ( p_source, v_result);

• RETURN ( v_result );

• END getbase64;

• To show BLOB Images use the following code:

• <fo:instream-foreign-object content-type=”image/jpg”><xsl:value-of select=”.//IMAGE_”/></fo:instream-foreign-object>

• XML Publisher supports in general right now only JPG, GIF and PNG.

XML Publisher Report Output

Where do I get the XML file or request output file for my BI Publisher Report / Concurrent Request?

A quick post ... it's been a frenetic few months, so blog posts a bit thin lately but plenty of good stuff in the pipeline!
Generally when I'm designing a BI Publisher/XML Publisher report I'll create a concurrent program that generates XML, I'll follow the usual steps:

1. Setup the concurrent program to output Text
2. Run it to ensure its working
3. Switch the concurrent program to output XML
4. Run the concurrent program to output XML
5. SFTP/copy the o*.out XML file from $APPLCSF/$APPLOUT
6. Load it into Word via the BI Publisher Desktop plugin and design the layout "on the run".
7. Setup the BI Publisher Data Definition/Template
8. Perform system/unit tests

Is there an easier way to get the XML file from a concurrent request? Sure is - nice and easy through the front end.

Get the XML File from a BI Publisher based concurrent request/report:

1. View->Requests
2. Find the request you're interested in
3. Click on Diagnostics button
4. Click the View XML
5. Save the file to your PC by doing File->Save As, *.xml

But where does the XML file live?
Well, by default it is $APPLCSF/$APPLOUT/o{REQUEST_ID}.out.
But if that's the XML file, then where does the actual output file reside as the location of the XML file is where normal request output resides?

Get the Request Output File for a BI Publisher based concurrent request/report:

The output is $APPLCSF/$APPLOUT/{REPORTNAME}_{REQUEST_ID}_{COUNT}.PDF|RTF|EXCEL|HTML
Where

• REPORTNAME is the concurrent program short name,
• REQUEST_ID is the concurrent request ID
• COUNT is a counter based on the number of times a request has been re-published.
• One of PDF, RTF, EXCEL, HTML is the file extension/type dependent on the output formats chosen.

An example is: XXXX_FNDSCURS_2803880_1.EXCEL

What is BI Publisher?

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?

Responsibility: xml publisher administrator responsibility --> Home --> Data Definitions

Responsibility: xml publisher administrator responsibility --> Home -->Templates

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 transaction 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?

xliff is the file that supports the translation, you can modify the same as required.

How do you display the company logo on the report output?

Copy and paste the logo (.gif. or any format) on the header section of .rtf file . Ensure you resize per the company standards.

Oracle Reports Interview Questions

Oracle Reports Interview Questions:

1.       What is a Lexical Parameter?

Lexical parameters are used to substitute multiple values at runtime and are identified by a preceding ‘&’. Lexicals can consist of as little a one line where clause to an entire select statement

 Lexical Parameters are used to execute query dynamically.

Example: An example of a lexical parameter usage in a select statement is as follows

               Select * from emp, deptno 
               &where. 

In the properties of the 'where' user parameter, make sure that the data type of the 'where' user parameter is set as character.  If you know the maximum length that your where clause is going be, you can set the width of the where parameter to be slightly greater than that number.  Otherwise, set it to some number like 100. 

If your lexical parameter ('where') width is not enough to hold the where condition assigned to it, you will receive one of the following errors depending on your Reports version. 
   REP-0450 - Unhandled exception, 
  and ORA-6502- PL/SQL numeric or value error. 
   or 
   REP-1401 - Fatal PL/SQL error in afterptrigger  
  and ORA-6502-PL/SQL numeric or value error. 

2.       What is a Bind Variable?

Bind parameters are used to substitute single value at runtime for evaluation and are identified by a preceding ‘:’. An example of a bind parameter in a select statement is provided below, where :P_EMP is the bind parameter reference.

                               Select ename,empno
                                From emp 
                               Where empno= :P_EMP

These are used as tokens while registering concurrent program.

3.                                                 Difference between lexical and bind variable?

Bind references are used to replace a single value in SQL or PL/SQL.  Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING,CONNECT BY, and START WITH clauses of queries.  Binds may not be referenced in the FROM clause.  An example is: 

SELECT ORDID,TOTAL

FROM ORD

WHERE CUSTID = :CUST

Lexical references are placeholders for text that you embed in a SELECT statement.  You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY , ORDER BY , HAVING, CONNECT BY, and START WITH.  You cannot make lexical references in PL/SQL.  Before you reference a lexical parameter in a query you must have predefined the parameter and given it an initial value.  An example is: 

SELECT ORDID, TOTAL

FROM &ATABLE

4.       How many types of Triggers are there and what are they? Tell their sequence of execution.

Report triggers execute PL/SQL functions at specific times during the execution and formatting of your report.  Using the conditional processing capabilities of PL/SQL for these triggers, you can do things such as customize the formatting of your report, perform initialization tasks, and access the database.  To create or modify a report trigger, use Report Triggers in the Object Navigator.  Report triggers must explicitly return TRUE or FALSE.  Report Builder has five global report triggers (you cannot create new global report triggers):

Before Parameter Form trigger

After Parameter Form trigger

Before Report trigger

Between Pages trigger

After Report trigger

Before Report trigger and After Report trigger should be declared compulsory. In the Before Report trigger we declare thesrw.user_exit(‘ fnd srwinit’) user exist and in the After Report trigger srw.user_exit (‘fnd srwexit’)

The sequence/order of events when a report is executed is as follows:

Before Parameter Form trigger is fired.
1 Runtime Parameter Form appears (if not suppressed).

2 After Parameter Form trigger is fired (unless the user cancels from the Runtime Parameter
Form).

3 Report is "compiled."

4 Queries are parsed.

5 Before Report trigger is fired.

6 SET TRANSACTION READONLY is executed (if specified via the READONLY argument
or setting).

7 The report is executed and the Between Pages trigger fires for each page except the last one.
(Note that data can be fetched at any time while the report is being formatted.) COMMITs
can occur during this time due to any of the following--user exit with DDL, SRW.DO_SQL
with DDL, or if ONFAILURE=COMMIT, and the report fails.

8 COMMIT is executed (if READONLY is specified) to end the transaction.

9 After Report trigger is fired.

10 COMMIT/ROLLBACK/NOACTION is executed based on what was specified via the
ONSUCCESS argument or setting.

Cautions
=========
1. In steps 4 through 9, avoid DDL statements that would modify the tables on which the
report is based. Step 3 takes a snapshot of the tables and the snapshot must remain valid
throughout the execution of the report. In steps 7 through 9, avoid DML statements that
would modify the contents of the tables on which the report is based. Queries may be
executed in any order, which makes DML statements unreliable (unless performed on tables
not used by the report).

2. If you specify READONLY, you should avoid DDL altogether. When you execute a DDL
statement (e.g., via SRW.DO_SQL or a user exit), a COMMIT is automatically issued. If you
are using READONLY, this will prematurely end the transaction begun by SET
TRANSACTION READONLY.


Report trigger restrictions
=============================
1. If you are sending your report output to the Runtime Previewer or Live Previewer, you

should note that some or all of the report triggers may be fired before you see the report
output. For example, suppose that you use SRW.MESSAGE to issue a message in the
Between Pages trigger when a condition is met. If there are forward references in the report
(e.g., a total number of pages displayed before the last page), Report Builder may have to
format ahead to compute the forward references. Hence, even though you have not yet seen
a page, it may already have been formatted and the trigger fired.

2. In report triggers, you can use the values of report-level columns and parameters. For
example, you might need to use the value of a parameter called COUNT1 in a condition
(e.g., IF :COUNT1 = 10). Note, though, that you cannot reference any page-dependent columns (i.e., a column with a Reset At of Page) or columns that rely on page-dependent columns.

3. In the Before and After Parameter Form, and Before and After Report triggers, you can set
the values of parameters (e.g., give them a value in an assignment statement, :COUNT1 =
15). In the Before and After Report triggers, you can also set the values of report-level,
placeholder columns.

4. In the Between Pages trigger, you cannot set the values of any data model objects. Note also
that the use of PL/SQL global variables to indirectly set the values of columns or parameters
is not recommended. If you do this, you may get unpredictable results.

5. If you run a report from Report Builder Runtime (i.e., not the command line or
SRW.RUN_REPORT), you should commit database changes you make in the Before
Parameter Form, After Parameter Form, and Validation triggers before the report runs.
When running in this way, these triggers will share the parent process’ database connection.
When the report is actually executed, however, it will establish its own database connection.

6. A lexical reference cannot be used to create additional bind variables after the After
Parameter Form trigger fires. For example, suppose you have a query like the following
(note that the WHERE clause is replaced by a lexical reference):
SELECT ENAME, SAL FROM EMP
&where_clause
If the value of the WHERE_CLAUSE parameter contains a reference to a bind variable, you
must specify the value in the After Parameter Form trigger or earlier. You would get an
error if you supplied the following value for the parameter in the Before Report trigger. If
you supplied this same value in the After Parameter Form trigger, the report would run.
WHERE SAL = :new_bind

5.       What is a Format Trigger?

Format triggers are PL/SQL functions executed before the object is formatted.  The trigger can be used to dynamically change the formatting attributes of the object.  The function must return a Boolean value (TRUE or FALSE).  Depending on whether the function returns TRUE or FALSE, the current instance of the object is included or excluded from the report output.  You can access format triggers from the Object Navigator, the Property Palette, or the PL/SQL Editor.

A format trigger is a PL/SQL function executed before an object is formatted.  A trigger can be used to dynamically change the formatting attributes of the object.

6.       What is Anchoring?

It is a feature thru which we can control the position of the boiler plate or data fields in layout.
Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent.  The end of the anchor with a symbol is attached to the parent object. 
When you create a default layout, Reports will create some of its own implicit anchors.  These are not visible. There may be occasions when you want to create your own explicit anchors to force objects to be positioned together or to conditionally specify when the object prints. 
 You create an explicit anchor as follows: 
 1.  Select the Anchor tool in the Layout Tool Palette. 
 2.  Click on an edge of the Child object. 
 3.  Move the cursor to the edge of the Parent object and double click to fix the anchor. 
 You can position the anchor at any distance down the edge of the object.  The distance is a percentage of the total length of the edge.  You can adjust this position in the anchor property sheet. 

7.       What is Frame and Repeating Frame?

Frames are used to surround other objects and protect them from being overwritten or pushed by other objects.  For example, a frame might be used to surround all objects owned by a group, to surround column headings, or to surround summaries. 

Repeating frames are place holders for records.  Repeating frames print once for each record of a group and control record-level formatting.  Reports will generate one repeating frame for each group when you create a default layout. 
Reports will place containers of columns inside of the frames.  Each repeating frame retrieves only one row in its fetch cycle for any one repetition.  Until it is constrained by another frame, it will repeat itself until the while loop condition can no longer be satisfied. 
 We give group in data model as source to repeating frame.

8.       What are Confined Mode and Flex Mode?

Confined mode allows objects to be locked into the place in the layout. Objects are maintained within their containers.

CONFINE mode is not for a specific object, but applies to all objects on the layout when it is enabled (locked).When it is turned off (unlocked), you are allowed to move an object outside its surrounding frame. When it is turned on (locked), you are unable to move an object outside its surrounding frame.  This is to prevent unnecessary 'Frequency Errors'.

 Flex mode preserves the layout structure while allowing expanding and shrinking of the layout.

FLEX mode, when enabled, allows surrounding frames to grow as an object is resized or moved.  Only one object at a time can be moved either vertically or horizontally, not diagonally.  

9.       What are User Exits?

You build user exits when you want to pass control from Report Builder to a program you have written, which performs some function, and then returns control to Report Builder. 
 You can write the following types of user exits:  
 *             ORACLE Precompiler user exits 
 *             OCI (ORACLE Call Interface) user exits 
 *             Non-ORACLE user exits.
User exits can perform the following tasks:  
 *             Perform complex data manipulation  
 *             Pass data to Report Builder from operating system text files  
 *             Manipulate LONG RAW data  
 *             Support PL/SQL blocks  
 *             Control real time devices, such as a printer or a robot  
 You can use user exits for other tasks, such as mathematical processing.   
However, it is recommended that you perform such tasks with PL/SQL within Report Builder itself.  
Ex: FNDSRWINIT, FNDSRWEXIT.

10.   How do I Register a Custom Report?

               Step 1: Register a concurrent program executable  

         Navigate to the Define Executable form (AOL Reference manual pg 9-84) 
         This determines the type of program being run,ie an Oracle Report. Fill in the executable name, application and execution method. For the Execution File, fill in just the filename. The concurrent manager will look in the appropriate directory under the application's top directory. 
         For spawned programs, the file must be in the bin directory, for Oracle Reports the rdf file must be in the srw directory. 
         For PLSQL concurrent programs, put the name of the stored procedure. 
           Step 2: Define the concurrent program 
         Navigate to the Define Concurrent Program form (AOL Reference manual pg 9-87) 
         This form links a concurrent program to the executable you just defined, as well as defines the programs parameters, incompatibilities, and other options. 
Enter the concurrent program name, application, short name and description. Check Standard Submission if you want to be able to submit this program from the Standard Report Submission form. 
Enter the name of the executable you defined and any report information if necessary. Also define any parameters your program needs here and any incompatibilities. 
         Step 3: Add the concurrent program to a Report Group 
         First you will need to find the name of the Report Group to use. 
         Go to Security->Responsibility and query the responsibility you want to run the program with. 
         It should show a Report Group name. Query this name in Security->Responsibility->Report 

         Add your new program to the list of available programs. Now when you go to submit a request with this responsibility, you will be able to submit your custom program.

11.   What is a Token?

Token is used to attach a bindvariable to a report parameter while registering the report as concurrent program.

12.   What is the use of ‘Send to Back’ and ‘Bring to Front’?

To change the order in which objects are layered on top of each other.

Send to Back to move the object behind all other objects.

Bring to Front to move the object in front of all other objects.

13.   If 2^nd parameter value is based on 1^st parameter then how do u declare it?

Let v2 be the value set definition of 2^nd parameter and v1 be the value set definition for the first parameter then

In the value set definition of v2 = value $FLEX$.v1

14.   What are Summary Column, Place holder Column, and Formula Column?

A summary column performs a computation on another column's data.  Using the Report Wizard or Data Wizard, you can create the following summaries:  sum, average, count, minimum, maximum, % total.  You can also create a summary column manually in the Data Model view, and use the Property Palette to create the following additional summaries:  first, last, standard deviation, variance.

A placeholder is a column for which you set the data type and value in PL/SQL that you define.  You can set the value of a placeholder column in the following places. A place holder column stores a value which we can refer in the layout.

A formula column performs a user-defined computation on another column(s) data, including placeholder columns. Formula columns should not be used to set values for parameters.

15.   How do u hide fields in a Report?

    Ans: Using the Format Trigger we can hide the fields.

/* Suppose that you are building a master/detail report

** and, if no detail records are retrieved for a master

** record, you do not want the boilerplate labels to

** appear.  To do this, you first create a summary

** column called MYCOUNT with a Function of Count in

** the source group of the master repeating frame. 

** In the format trigger for the group frame that

** surrounds the detail repeating frame and its labels,

** you enter the following:

*/

function my_formtrig return BOOLEAN is

begin

if :mycount = 0 then

   return (false);

else

   return (true);

end if;

end;

16.   How many types of Report formats we have?

Custom Reports and Standard reports

17.   What is the minimum number of groups required for a Matrix type report?

To create a matrix report, you need at least four groups: one group must be a cross-product group, two of the groups must be within the cross-product group to furnish the "labels," and at least one group must provide the information to fill the cells.  The groups can belong to a single query or to multiple queries. 

 A matrix (cross tab) report contains one row of labels, one column of labels, and information in a grid format that is related to the row and column labels.  A distinguishing feature of matrix reports is that the number of columns is not known until the data is fetched from the database. 

View the video report builder help

18.   What is the difference between Bitmap and Character based reports? Explain in detail.

Bitmap vs. Character-Mode Report Design    
   Here is an example to help explain how Oracle Reports are designed and    printed in both the bitmap and character-mode environments.     
   Assume you wish to print "Cc" where "C" is a different font and a larger point size than "c" and is in boldface type (where "c" is not).     
   In Oracle Reports Designer, bitmap mode, you can make "C" bold and in a     different font and point size than "c".  This is because you are generating postscript output.  Postscript is a universal printer language and any postscript printer is able to interpret your different design instructions.   
    In Oracle Reports Designer, character mode, the APPLICATIONS STANDARDS EQUIRE the report to be designed in ONE FONT/ ONE CHARACTER SIZE.  Character mode reports generate ASCII output.  In ASCII you cannot dynamically change the font and character size. The standard is in effect so a report prints as identically as possible from both conventional and postscript printers.      
   Bitmap vs. Character-Mode Report Printing 
   These sequences contrast the two printing environments. In postscript, "C" can be in a different font and point size than "c".  Both or either could also be bold, for example.     

19.   What Printer Styles are used for? Did you develop any printer styles?

Srw.driver

20.   How do you fix a performance problem in a Report?

Check Report main query and fine tune it.

Create indexes on columns used in where condition (eliminate full table scan)

Enable Trace (set trace on in before report and set trace off in after report)

Before Report:

    srw.do_sql('alter session set sql_trace=true');

After Report:

srw.do_sql('alter session set sql_trace=false');

Trace file will be generated at location:

select value from v$parameter

 where name = 'user_dump_dest';

To better see execution plans in a trace file, you need to format the 
generated trace file with tkprof statement.

21.   What is the significance of  p_conc_request_id?

P_conc_request_id is declared as the user parameter for reports which will get org specific data.  P_conc_request_id datatype is character and length is 15.

22.   How to call a stored procedure in the report? What is the use of that?

Package.prcedure

23.   How do you set ORG_ID in a SQL*Plus session?

Call the Below Anonymous pl/sql block.

BEGIN

                        fnd_client_info.set_org_context(‘204');

            END;

           

            Or

                       

            exec dbms_application_info.set_client_info(‘org_id’);

24.   While registering a report and a pl/sql block we pass some parameters, for any pl/sql block we pass two additional parameters. Can u list them?

p_errorcode and p_errorbuffer as out parameters in main procedure.

It requires 2 IN parameters for a PL/SQL procedure that's registered as a concurrent program in Apps.  They are

1. errcode  IN  VARCHAR2

2. errbuff    IN  VARCHAR2

25.   How we can call from form to form, form to report?

Calling a Form from another Form:  FND_EXECUTE(…);

NOTE: The calling and called Forms must be registered with Applications.

Calling a Report from a Form: FND_REQUEST.SUBMIT_REQUEST(…);

NOTE: This method can be used to call any concurrent program.

26.   What are logical page and physical page?

In the Runtime Previewer, you can scroll though a single page of report output, page through the entire report, and split the screen to view different sections of the same report concurrently.

A physical page (or panel) is the size of a page that will be output by your printer.  A logical page is the size of one page of your actual report (it can be any number of physical pages wide or long).  The Runtime Previewer displays the logical pages of your report output, one at a time.

27.   Why ref cursor is is used in the reports?

Dynamic cursor

28.   When we create a report we use the tables, there is some difference when we use the multi-org tables and ordinary tables, can u tell the difference?

Set p_conc_request_id for org specific tables.

29.   We have 2 different databases, and each system has 2 tables. Know there is a link provided between them. The client want a report to be developed based on the 4 tables that r there in the 2 different databases. The solution must be efficient?

Use database links