Oracle Apps Interview Question and Answer - D2k Report

1.   What is NOCOPY?

• By default the IN parameter is passed by reference and the OUT and IN OUT parameters are passed by value.
• NOCOPY is a compiles hint that can be used with OUT and IN OUT parameter to request to pass by reference. This improves the performance with OUT and INOUT Parameters.

2.   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.

3.   ANCHOR

·         Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent object. The end of the anchor should be attached to the parent object.

A) Parent Object                                   B) Child Object

• Since the size of the some layout objects may change when the report runs (When the data is actually fetched), anchors need to be defined to make the appearance of the object.
• An Anchor defines the relative position of an object to the object to which if this anchored.
• Symbol is by pressing the shift key Anchor can be moved.

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.

4.   User Exits:

·         A user exit is program that can be written and linked into the report builder executable or user exit DLL files.

·         User exits are build when ever the control needs to be passed from the report builder to a program, which performs some function and then control returns to the Report Builder.

Types of user exits: -

A)   Oracle Pre-Complier user exits.

B)   OCI (Oracle Call Interface user exits)

C)   Non – Oracle user exits.

·         User exits can perform the following tasks.

·         Perform complex data manipulation.

·         Pass data to report builder from OS text files.

·         Support PL/SQL blocks.

·         Control real time devices by printer or robot.

5.   Types of Triggers in Reports:-

1. Report Triggers
2. Data Triggers
3. Layout Triggers

Report Triggers: -

·         After Parameter form Trigger.

·         After Report Trigger

·         Before parameter form Trigger

·         Before Report Trigger

·         Between Pages Trigger

+ Firing Sequence

*   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 are 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.

Data Triggers:-

*   Ref Cursor Query.

-          This uses PL/SQL to fetch data for the report.

-          In this a PL/SQL function need to be specified to return a cursor value from a cursor variable.

*   Group Filter:

This is PL/SQL function that determines which record to be included in a group in the property of PL/SQL.

-          The function must return a BOOLEAN value.

True …….. Includes the current record in the report.

False ……. Excludes the current record from the report.

    

     *   Formula

-          These are Pl/SQL functions that populate formula or place holder columns.

*   Validation Trigger  

-          These are also PL/SQL functions that are executed when parameter values are specified on the command line and when the runtime parameter form is accepted.

-          Are also used to validate the initial value property of the parameter.

Layout Triggers:-

*   Format Trigger.

-          These are PL/SQL functions executed before the object is formatted.

-          Used to dynamically change the formatting attributes of the object.

*   Action Trigger

-          These are Pl/SQL procedures executed when a button is selected in the run time

            Previewer.

-          This can be used to dynamically call another report or execute any other PL/SQL.

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 placeholder column in the following places.

·         Before report trigger if the placeholder is a report level column.

·          Report level formula column, if the placeholder 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)

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.

Frame

·         Surrounds the objects and protect them from being over written or pushed by other objects.

System Parameters in Reports

* Background                         * Copies                           * Currency              

* Decimal                              * Desformat                      * Desname

* Destype                               * Mode                              * Orientation

* Print Job                                      * Thousands.

6.    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.

7.    In which tables FF are stored?

·         FND – ID – FLEXS

·         FND-ID-FLEX-STRUCTURES

8.   How to call a Report in two applications?

*SRW Package is a collection of PL/SQL constructs that contain many functions, procedures and exceptions that can be referenced in Reports.

1 SRW.Break                                   2. SRW.Context_Failure

3 SRW.Do_SQL                                4. SRW.Do_SQL_Failure

5 SRW.Get_Page_Num                     6 SRW.Message

7 SRW.Program_Abort                     8 SRW.Refrence

9 SRW.Run_Report                          10 SRW.Run_report_Failure

11 SRW.Set_Altr                             12 SRW.Integer_Error

13 SRW.Set_Field_char                             14 SRW.Set_Field_Num

15 SRW.Set_Maxrow                        16 SRW.Trace_Add_Option

17 SRW.Trace_End                          18 SRW.Trace_Start

19 SRW.User_Exit                                     20 SRW.User_Exit_Failure

SRW.Run_Report

SRW.Run_Report (command_line, char);

Executes specified R25 RUN Command

SRW.RUN_REPORT_FAILURE;

Stops report execution when failure of SRW.Run_Report occurs.

By using SRW.Run_Report, another report can be called to the screen from a button with in one report.

If this is used from a Report Trigger, BATCH=YES  must be passed.

* DESTYPE can only be FILE, PRINTER or MAIL.

Ex:- Function F1 return Boolean is

          Begin

          SRW.RUN_REPORT (‘Report=Rep_A  P_Param1=20’);

-- calls Report Rep-A and displays to screen

-- Passes a parameter 20 to the param_1

Exception

When SRW.RUN_REPORT_FAILURE Then

SRW. Message (100, ‘Error Calling Report’);

Raise SRW.Program_Abort;

Return True;

End;

* SRW.DO_SQL (sql statement char);

   Executes specified SQL statement

* SRW.DO_SQL_FAILURE;

Stops report execution upon SRW.Do_SQL failure.

* SRW.Message (msg_number number, msg_text char);

Displays a specified  message and message number

* SRW.Program_Abort;

Stops execution of report when raised.

* SRW.Set_Altr

Applies attribute settings, such as font, color to lay out objects.

This procedure applies formatting attributes to the current frame, repeating frame, field or boiler plate object.

* SRW.Set_Altr (object_id number, altr SRW.Generic_Altr);

   object _id is always zero.

Altr is SRW.Altr (that is, the attributes to change)

* SRW.set_Field

The procedures in this package are very useful in format triggers.

They are used to change data that will be displayed ion a particular item based on a specific condition.

SRW.Set_Field_char (Object_id, text char);

SRW.Set_Field_Date (Object_id, date date);

SRW.Set_Field_ Num (Object_id, number number);

9.   Can a Report contain more than one template?

• Templates define common characteristics and objects that can be applied to multiple reports.
• For example template can be defined that include the company logo and sets font colors for selected areas of a report.
• When a report is created through the Report Wizard, there is an option of applying a template  (.tdf file) to the report.
• When a template is chosen, objects in the margin area of a template are imported into the same locations in the current report section, over writing any objects if exists.
• The characteristics of the objects in the body area of the template are applied to objects in the body area of the template are applied to objects in the body area of the current report section.
• Any template properties, parameters, report triggers, programmed units and attached libraries are also applied.
• Different (Multiple) templates can be applied to each section of the report.
• If another template is applied later to a report the existing template objects will be deleted in the current report section.

 

10.   How to add a template to the predefined templates list?

a.    In a text editor open the Preferences File

b.   scroll down to the template  descriptions identified by Reports. Xxx – Template_Reso (who xxx specified a Report style) (Tabular, Break Above)

c.    For each Report style for which the template is defined

i)             to the Reports.xxx – Template_Disc list, add the description that should be appeared on the template page of the report wizard.

ii)           To the corresponding Reports. XXX_template_file list, add the file name of the template in the same position as the addition that is made to the description list.

d.   copy the template file (file name.tdf) to

ORACLE_HOME/REPORT 60/ADMIN/TEMPLATE/US

Preferences file:-

Windows   à ORACLE_HOME\CAUPREFS.ORA (user preferences)

                   ORACLE_HOME\CAGPREFS.ORA (Global Preferences)

Unix à HOME_DIRECOTRY/Prefs.ora (User preferences)

          $ORACLE_HOME/tools/admin/prefs.ora( Global Preferences)

11.   How to pass a parameter in a request set for three concurrent programs which are having same parameter?

a.    For the first  report in the Report set, click on the parameters button and ender the parameters that are to the shared by all reports in the Request (Report) set.

b.   Go to the nest report and click the parameters button and list the same shared parameters.

c.    Do the above step for each and every report in the request set

d.   ’Modify’ check box can be used to allow the users to change the values of the parameters in the ‘lower ’reports at submission time..

e.   ‘Display) check box can be used to allow the users to see the parameter values at submission time.

* Request set wizard can be used to quickly create a new Request set in which all of the request run sequentially or all of the request run in parallel.

                   *sequentially à One after another

                   * parallel à All at once.

- the action can be set whether to continue processing or abort processing. If a request ends  with the statues ‘Error’.

12.    What are Global variables in Reports?

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

                   create_parameter_list (------)

                   add_parameter (----:Global_var);

                   run_product(….);