Oracle Reports FAQS

1. REPORT TYPES

A) Tabular Report B) Group Left Report

C) Group Above Report D) Form like report , Form letter

E) Matrix Report, with group F) Multi Media Report

G) Mailing Label Report H) OLE Report.

2. How do you register a report?

*Design your report.

· Generate the executable file of the report.

· Move the executable as well as source files to the appropriate product’s folder.

· Register the report as concurrent executable.

· Define the concurrent program for the executable registered.

· Add the concurrent program to the request group of the responsibility.

3. What are the different triggers in report?

*There are five report trigger:

· Before Report

· After Report

· Before Parameter Form

· After Parameter Form

· Between Pages

4. The Firing sequence for report triggers is

* Before Parameter form Trigger-Fires before the runtime parameter form are displayed.

The parameter values can be accessed and changed.

* After Parameter form Trigger.-Fires after the runtime parameter form are displayed.

The parameters can be accessed and their values can be checked.

*Before Report Trigger

Fires before the report is executed but after the queries is passed and data is fetched.

*Between Pages Trigger.-Fires between each page of the report are formatted, except the very first pages. This is used for customized page formatting.

* After Report Trigger-Fires after exiting from the run time premier or after report output is sent to a specified destination. (File, Printer, Mai lid etc….)This is used to clean up any initial processing that was done such as deleting the tables. This Trigger always fires irrespective of success or failure of the report.

Validation Triggers: Validation Triggers are PL/SQL functions that are executed when parameter values are specified on the command line and when you accept the Runtime Parameter Form. (Notice that this means each Validation Trigger may fire twice when you execute the report). Validation Triggers are also used to validate the Initial Value of the parameter in the Parameter property sheet.

Format Triggers: 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.

Action Triggers: Action Triggers are PL/SQL procedures executed when a button is selected in the Previewer. The trigger can be used to dynamically call another report (drill down) or execute any other PL/SQL.

5. Frame: Surrounds the objects and protect them from being over written 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

6. Repeating Frame: Repeating frame surrounds all of the fields that are created for group’s columns. Repeating frame prints once for each record of the group. For frames and repeating frames, the property elasticity defines whether the size of the frame or repeating frame should with the objects inside of it at runtime.

7. Data Link: Data links relate the results of multiple queries. A data link (Parent – Child Relation Ship) causes the child query to be executed once for each instance of its parent group.

8. How u defines reports parameter in oracle application?

*Token in Concurrent Program creation: For a parameter in an Oracle Reports program, the keyword or parameter appears here. The value is case insensitive. For other types of programs, you can skip this field.

9.Parameters: A parameter is a variable whose value can be set at runtime (from the run time parameter of the command line).User parameters are created by the user and system parameters are created by Report Builder. System parameters cannot be renamed or deleted.

10.Bind Parameters (Variables):Bind references (or Variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number or date. Bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY and START WITH clauses of queries. Bind references cannot be referenced in FROM clauses. Bind variables can be referenced by entering a colon (:) followed immediately by the column or parameter name. If the parameter / column are not created before making a bind reference, report builder will create a parameter.

11. Lexical Parameters (Variables): Lexical references are place holders for text that is embedded in a SELECT statement. Lexical Variables can replace the clauses appearing after SLECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH. You can’t make lexical reference in a pl/sql statements.

12. What is Flex mode and Confine mode?

Confine mode (lock option)

On: child objects cannot be moved outside their enclosing parent objects.

Off: child objects can be moved outside their enclosing parent objects.

Flex mode:

Flex is the property of moving the related fields together by setting the flex property on

On: parent borders "stretch" when child objects are moved against them.

Off: parent borders remain fixed when child objects are moved against them.

13. What are the different columns in reports?

Formula Column: It performs a user-defined computation on another columns data, including Place-holder columns. Formulas are PL/SQL functions that populate formula or place holder columns. Cannot be used to populate parameter values.

Summary Column: Performs a computation on another columns data like sum, average, count, minimum, maximum, %, total.

For group reports, the report wizard and data wizard create ‘n’ summary fields in the data model for each summary column that is defined.

One at each group level above the column being summarized.

One at the report level.

Place Holder Column

A Place holder column is a column for which, the data type and value can be set dynamically (Programmatically)

*The value can be set for a place holder column in the following places.

Before report trigger if the place holder is a report level column.

Report level formula column, if the place holder is a report level column.

A formula in the place holders group below it (The value is set once for each record of the group)

14. What are user exists in reports? What are user exist available in apps?

A user exit is a program written and then linked in to the report builder executable user exist are written when content need to be passed from report builder to that pgm, which performs some function and then returns control to report builder.

(OR)

This one of built-in program in report 6i, it will be used to transfer the report builder control to some other 3rd generation language get some information complete the remaining execution process.

FND_SRWINIT: - This is a User Exit which sets your profile option values and allows Oracle AOL user exits to detect that they have been called by oracle repots.

Can be used in BEFORE-REPORT Trigger.

FND_SRWEXIT:- This user exit ensures that all the memory allocated for

AOL user exits have been freed up properly. Can be used in AFTER- REPORT Trigger

FND FORMAT_CURRENCY: -To format the currency amount dynamically

Depending upon the precision of the actual currency value, standard precision, users profile values and location (country) of the site.

FND FLEXSQL - This user exits allows you to use Flex fields in Reports

FND FLEXIDVAL - This user exits allows you to use Flex fields in Reports

15. Format trigger where u uses this trigger and how?

*Format triggers are executed before an object is formatted. A format trigger can be used to dynamically change the formatting attributes of the object.

Validation triggers are PL/SQL functions that are executed when parameter values are specified on the command line and when you accept the Runtime Parameter Form.

Are also used to validate the Initial Value property of the parameter. Depending on whether the function returns TRUE or FALSE, the user is returned to the Runtime Parameter Form.

16. What are the type Models in the system parameters of the report?

1) Bit map 2) Character mode

17. What are Global variables in Reports?

*Global variables are the variables that ca n be assigned to parameters in reports and those parameters can be used in reports

18. How can you change the select statement of a query at run time?

Using lexical parameter

19. How can you call a report from Form?

Using Run_product

20. How u will debug Ur reports?

Using srw_massage

21.How do you display a message in reports ?

* SRW.Message

22. Suppose I have 5 pages report. On which page between pages trigger will not fire?

*hat between page trigger will fire after first page nothing but last page. (It will not fire for last page)

23. What is the difference between static and dynamic lov?

The static lov contains the predetermined values while the dynamic lov contains values that come at run time.

24. What is the minimum number of groups required for a matrix report

The minimum of groups required for a matrix report are 4

25. What are the User PARAMETERS in the Reports?

P_CONC_REQUEST_ID

P_FLEX_VALUE

26. What is SRW Package? (Sql Report Writer)

The Report builder Built in package known as SRW Package This package extends reports Control

Report execution, output message at runtime, Initialize layout fields, Perform DDL statements used

To create or Drop temporary table, Call User Exist, to format width of the columns, to page break

The column, to set the colors

Ex: SRW.DO_SQL, It’s like DDL command, we can create table, views , etc.,

SRW.SET_FIELD_NUM

SRW. SET_FILED_CHAR

SRW. SET FILED _DATE

27. How to display Request ID in the reports?

* By using the parameter P_CONC_REQUEST_ID which needs to be defined always in the reports.

28. Can u have more than one layout in report?

*It is possible to have more than one layout in a report by using the additional layout option in the layout editor. Yes it is possible to run the report without parameter form by setting the PARAM value to Null

29. Name some of the procedures in the SRW package ?

* SRW.Message, SRW.User_Exit, SRW.Do_Sql, SRW.Run_Report

30. Matrix Report: Simple, Group above, Nested

*Simple Matrix Report: 4 groups

1. Cross Product Group

2. Row and Column Group

3. Cell Group

4. Cell column is the source of a cross product summary that becomes the cell content.

Frames: 1.Repeating frame for rows (down direction)

2. Repeating frame for columns (Across)

3. Matrix object the intersection of the two repeating frames

31) What is difference between d2k report and oracle apps report

*In general D2k reports we won't use the user exit’s we use user exit's in oracle apps reports

2)in general D2k reports we won't use the multiOrg we use multiorg in oracle apps reports

32. What are ad-hoc reports?

*Ad-hoc Report is made to meet one-time reporting needs. Concerned with or formed for a

particular purpose. For example, ad hoc tax codes or an ad hoc database query

create_parameter_list (------)

add_parameter (----:Global_var);

run_product(….);

33. What are Physical and Logical pages in Reports ?

*A report page can have any length and any width. Because printer pages may be smaller or larger than your report's "page," the concept of physical and logical pages is used.

Physical Page: A physical page (or panel) is the size of a page that will be output by your printer.

Logical Page: A logical page is the size of one page of your actual report; one logical page may be made up of multiple physical pages. The Previewer displays the logical pages of your report output, one at a time.

34. What are the various page layout sections in Oracle Reports?

*A report has three sections: the report header pages, report body/margin pages, and report trailer pages.

35. Can you create a group without any break columns?

*No.

36. Types of Matrix report ?

*1. Single query 2. Multi query 3. Nested Query 4. Matrix Break

37. In Reports, how can you print one record per page in the output ?

*Set the Maximum records per page property of the Repeating frame to 1.

38.What is Matrix Report how many groups r there and how many queries u can use?

*The minimum of groups required for a matrix report are 4, but queries are may be one query or more than one query. A matrix (cross-product) report

Is a cross-tabulation of four groups of data:

One group of data is displayed across the page.

One group of data is displayed down the page.

One group of data is the cross product, which determines all possible locations where the across and down data relate and places a cell in those locations.

One group of data is displayed as the “filler” of the cells.

39.How many different layouts are in Reports?

*A tabular report:Is the most basic type of report. Each column corresponds to a column selected from the database.

A group above report

Contains multiple groups in its data model. It is a "master/detail" report, where there may be a lot of information in the master group. For every master group, the related values of the detail group(s) are fetched from the database and are displayed below the master information.

A group left report

Also contains multiple groups in its data model, dividing the rows of a table based on a common value in one of the columns. Use this type of report to restrict a column from repeating the same value several times while values of related columns change.

A form-like report

Displays one record per page, displaying field values to the right of field labels.

A matrix (cross-product) report

Is a cross-tabulation of four groups of data:

One group of data is displayed across the page.

One group of data is displayed down the page.

One group of data is the cross product, which determines all possible locations where the across and down data relate and places a cell in those locations.

One group of data is displayed as the”filler” of the cells.

40.What is Financial Statement Generator (FSG) and what is its use?

*FSG is a powerful and flexible tool you can use to build your own custom reports without programming. FSG is only available with GL.

41. How can you use DDL comment in report?

*Using SRW.DO_SQL

42. What are the various types of links?

*The Data Link tool draws a link between a parent group and a child query. Creating a link is a drag and drop operation. Clicking and dragging from one column to another creates a link between those two columns (column to column link). Clicking and dragging from one query to another creates all possible links between columns selected by the queries based on database constraints (query to query link). Clicking and dragging between two groups creates a group-to-group link (i.e., a link with no columns).

43. What are Anchors?

*An anchor defines the relative position of an object to the object to which it is anchored. Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent. Since the size of some layout objects may change when the report runs (and data is actually fetched), you need anchors to define where you want objects to appear relative to one another.

44. What are the various types of anchors in Reports?

*There are two types of anchors in Oracle Reports:

Implicit (anchors that Oracle Reports creates when a report is run)

Explicit (anchors you create)

Implicit Anchors: At runtime, Oracle Reports generates an implicit anchor for each layout object that does not already have an explicit anchor. It determines for each layout object which objects, if any, can overwrite it, then creates an anchor from the layout object to the closest object that can overwrite it. This prevents the object from being overwritten. The implicit anchor functionality saves you from having to define the positioning of each object. Implicit anchors are not visible in the Layout editor. However, you can specify that the Object Navigator display anchoring information using the Object Navigator Options dialog.

EXPLICIT ANCHORS: Create an anchor in the Layout editor by clicking on the Anchor tool, dragging from one edge of the child to the one of the parent's edges, then specifying the anchor's properties in its property sheet. Any anchor you create for an object will override its implicit anchoring. Explicit anchors are always visible in the Layout editor unless you specify otherwise via the Layout Options dialog

45. Anchor Properties:

A) Child Edge percent on child edge type B) Child Object Name

C) Collapse Horizontally D) Collapse vertically

E) Comments F) Name

* An Anchor can relate two child objects to one parent object.

46. In which tables FF are stored?

*FND – ID – FLEXS

FND-ID-FLEX-STRUCTURES

47. How do u customize the Reports?

a. Identify the Short name of the standard report in which module we have to customize

Ex: - if u want to customize in the AR module path is

Appl top\ar\11.5.0\reports\US\ .rdf

b. Open the .rdf file in Report builder and change the name of the module.

c. Open the data module and modify the query (what is client requirements) assign the columns to the attributes.

d. Go to report wizard and select, what r the newly created columns.

e. Then Compile it. Then u will get a .rep file in the specified module. If it is not in the specified directory then we have to put in the server directory.

f. Then Register in the AOL Concurrent àexecutable.

Concurrent à program.

g. go to system administrator SecurityàResponsibilityàrequest

h) Add and assign a concurrent program to a request group

48. Why do we call FND SRWEXIT from After Report Trigger

*FND SRWEXIT frees all the memory allocations done in other Oracle Applications user exits. It must be included if one is using any ORACLE APPLICATION OBJECT LIBRARY features in his report (such as concurrent processing)

49. Why do we call FND FLEXSQL from the Before Report Trigger?

*One need to pass the concatenated segment values from the underlying code combinations table to the user exit so that it can display appropriate data and derive any description and values from switched value sets as needed. One gets this information by calling the AOL user exit FND FLEXSQL from the before report Trigger.

50. Why do we call FND SRWINIT from Before Report Trigger

*FND SRWINIT fetches concurrent request information and sets up the profile options. It must be included if one is using any ORACLE APPLICATION OBJECT LIBRARY features in his report (such as concurrent processing)

51. If u call the user exit FND FLEXSQL with MODE = “WHERE” from the Before Report Trigger. What will it do?

*This user exit populates a lexical parameter that you specify with the appropriate SQL fragment at run time. You include this lexical parameter in the WHERE clause of the report query. This user exit is called once for each lexical to be changed.

52. If u call the user exit FND FLEXSQL with MODE = “ORDER BY” from the Before Report Trigger. What will it do?

*This user Exit populates the lexical parameter that one specifies with the appropriate SQL fragment at run time. One includes this lexical parameter in the ORDER BY clause of the report query. This user exit is called once for each lexical to be changed.

53. How can we display flexfield segment values, descriptions, and prompts on the report?

*Create a formula Column. Call the user exit FND FLEXIDVAL as the formula for this column. This user exit automatically fetches more complicated information such as descriptions and prompts so that one does not has to use complicated table joins to the flex field tables.

54. Name some options of the FND FLEXSQL user exit

*CODE, APP_SHORT_NAME, OUTPUT, MODE, DISPLAY, SHOWDEPSEG, NUM or MULTINUM, TABLEALIAS, OPERATOR, OPERAND1, OPERAND2.

55. Describe CODE option of the FND FLEXSQL user exit

*Specify the flex field code for the report (for example, GL#, MCAT).

56. Describe the APP_SHORT_NAME option of the FND FLEXSQL user exit

*Specifies the short name of the application that owns the flex field (for example: SQLGL, INV)

57. Describe the OUTPUT option of the FND FLEXSQL user exit

*Specify the name of the lexical parameter to store the SQl fragment. One uses this lexical later in the report when defining the SQL statement that selects the flexfield values. The datatype of this parameter should be character.

58. Describe the MODE option of the FND FLEXSQL user exit

*Specify the mode to use to generate the SQL fragment. Valid mode are:

SELECT: Retrieves all segments values in an internal (non- displayable format).

WHERE: Restrict the query by specifying constraints on flexfield columns.

The fragment returned includes the correct decode statement if one specifies MULTINUM. One must also specify an OPERATOR and OPERANDS.

HAVING: Same calling procedures and functionality as WHERE.

ORDER BY: Order required information by flexfield columns. The fragment Orders your flexfield columns and separates them with a comma. The fragment returned includes the correct decode statement, one specifies in MULTINUM.

59. Describe the DISPLAY option of the FND FLEXSQL user exit

*One uses the DISPLAY token with the MODE token . the DISPLAY parameter allows you to specify segments that represent specified flexfield qualifiers or specified segments numbers , where the segment numbers are the order in that the segments appear in the flexfield window, not the segment number specified in the Define Key Segments form.

Eg. If your MODE is SELECT and you specify DISPLAY = “ALL” then the SELECT statement includes all the segments of the flexfield. . Similarly, if your MODE is WHERE and you specify DISPLAY = “ALL”, then your WHERE clause includes all segments.

60.Describe the SHOWDEPSEG option of the FND FLEXSQL user exit

*SHOWDEPSEG = “N”disables automatic addition of depended upon segments to the order criteria.The default is “Y”.This token is valid only for MODE = “ODER BY” In FLEXSQL.

61. Describe the NUM option of the FND FLEXSQL user exit

*Specify the name or lexical or source column that contains the flexfield structure information. If the flexfield uses just one structure, specify NUM only and use a lexical parameter to hold the value. If the flexfield uses multiple structures, specify MULTINUM only and use a source column to hold the value. The default value is 101.

62. Describe the TABLE ALIAS option of the FND FLEXSQL user exit

*You use TABLE ALIAS if your SELECT joins to other flexfield tables or uses a self– join.

63. Describe the OPERATOR option of the FND FLEXSQL user exit

*Specify an operator to use in the WHERE clause.

64. Describe the OPERAND1 option of the FND FLEXSQL user exit

*Specify an operand to use in the WHERE clause,

65. Describe the OPERAND2 option of the FND FLEXSQL user exit

Specify a second operand to use with OPERATOR = “BETWEEN”

66. Where is FND FLEXIDVAL user exit used

*Call this user exit to populate fields for display. You pass the key flex fields data retrieved by the query into this user exit from the formula column. With this exit you can display values, descriptions and prompts by passing appropriate token (any one of VALUE, DECRIPTION<APROMPT or LPROMPT).

67. What are the User PARAMETERS in the Reports?

*P_CONC_REQUEST_ID

P_FLEX_VALUE

FND USER EXITS:-FND SRWINIT sets your profile option values, multiple organizations and allows Oracle Application ObjectLibrary user exits to detect that they have been called by an Oracle Reports program.FND SRWEXIT ensures that all the memory allocated for AOL user exits have been freed up properly.FND FLEXIDVAL are used to display flex field information like prompt, value etcFND FLEXSQL these user exits allow you to use flex fields in your reportsFND FORMAT_CURRENCY is used to print currency in various formats by using formula column

68. What is the difference between Master-Detail Report and report created by breaks

* Master/detail data models are very similar to break report data models. However, a master/detail data model is created using two queries, each of which owns at least one group, and a data link. A break report data model is created using one query and at least two groups. While reports based on a single query are usually more efficient than reports based on multiple queries, sometimes the structure of your data tables may require you to link multiple tables.

69. What are widow lines ?

* Widow lines are the minimum number of lines of the boilerplate text or field that should appear on the logical page where the text starts to print. If the number of lines specified for this property cannot fit on the logical page, then all lines of the boilerplate are moved to the next page.

70. What are widow records ?

* Widow records are the minimum number of instances (records) that should appear on the logical page where the repeating frame starts to print. If the number of instances specified for this property cannot fit on the logical page where the repeating frame is initially triggered to print, then the repeating frame will start formatting on the next page.

71. What is 'page protect' property for objects ?

* Page protect property for an object indicates whether to try to keep the entire object and its contents on the same logical page. Checking Page Protect means that if the contents of the object cannot fit on the current logical page, the object and all of its contents will be moved to the next logical page.

72. What is the horizontal of vertical sizing property of objects?

* Horizontal of vertical sizing property specifies how the horizontal or vertical size of the object may change at runtime to accommodate the objects or data within it.

73. What are the various values of the horizontal of vertical sizing property?

* The various values are:

Contract: Contract means the vertical size of the object decreases, if the formatted objects or data within it are short enough, but it cannot increase to a height greater than that shown in the editor. Note : Truncation of data may occur. (You can think of this option as meaning "only contract, do not expand.")

Expand: Expand means the vertical size of the object increases, if the formatted objects or data within it are tall enough, but it cannot decrease to a height less than that shown in the editor. (You can think of this option as meaning "only expand, do not contract.")

Fixed: Fixed means the height of the object is the same on each logical page, regardless of the size of the objects or data within it. Note : Truncation of data may occur. The height of the object is defined to be its height in the editor.

Variable: Variable means the object may expand or contract vertically to accommodate the objects or data within it (with no extra space), which means the height shown in the editor has no effect on the object's height at runtime.

74)What are the Major differences bteween key flexfields ,descriptive flexfields key

*flexfields: capture the key information in code language each code have specific meaning

Descriptive flexfields: capture the extra information

75) what is the use of Xml reports over reports10g?

*Multiple Language purpose we are going to develop XML Reports& Once we developed XML Report we can Display indifferent format like word,excel

77) How can we get odd numbered pages in output of a report u need to write stuff in before report trigger?

*Suppose u fetch pagenumber value into field or variable pagenum

now in trigger u h to write

if ((:pagenum/2=1) or :pagenum=1) then

return(true);

else

return(false);

end if;

78)Is it possible calling from one report to another?if possible means tell me ?

*Using srw.run_report('reportname'..........);

we can call a report from another report.

79)what are the different types of parameters avialable in reports?

*By using system parameter we can send the data to out put divises.where as user parameter are used to pass parameter values.default parameters are system parameters user parameters are created by user.

80) Do I need to declare bind parameter before using in the query?

*No need of declaring bind parameters directly you can pass the value.

81) What is meant by anchoring?

*Anchoring determine horizontally and vertically positioning between parent object to his child object; It means whenever parent object is moving then child object will also move...

82) What is meant by boilerplates?

*Boilerplate consists of text (label of the column) and graphics that appear in a report each time it is run.

83)I have 100 pages output. I want to print 10 records per page in Template?

*Set “maximum no of records per page” to 10 in property of repeating frame so that it will display 10 records on each page.

84) I want to print output in portrait style? Where I have to change the settings?

*Go to printer properties finishing tab and Choose portrait option button in orientation frame. Navigation: Print->properties->finishing->portrait

Click ok to print in portrait style.

85) In xml report, how to print the headings in every page?

*Define it as header section.it will be printed in all pages.

86) How to debug the report?

Set the profile option Concurrent: Debug Level to 5.Run the report and get the debug log.

87) How will you get the concurrent program output as email?