All Oracle Apps

Frequently Asked Interview Questions

Oracle Reports

16) How to implement Flex fields in a Report?

      You use a two-step method to report on flexfield values. The first step creates the appropriate SQL statement dynamically based upon the user’s flexfield. The output of the first step is used as input to the second step. The second step formats this raw data for display.

Step 1 (Construction):

      The first step requires you to include one or more lexical parameters (Oracle Reports variables that can be changed at runtime) in your SQL statement. You call the user exit FND FLEXSQL with different arguments to specify that part of the query you would like to build. The user exit retrieves the appropriate column names (SQL fragment) and inserts it into the lexical parameter at runtime before the SQL query is executed. The query then returns site– and runtime–specific flexfield information. For example, suppose you have the following query:

      SELECT &LEXICAL1 alias, column
      FROM table
      WHERE &LEXICAL2

      The preliminary calls to FND FLEXSQL replace values of LEXICAL1and LEXICAL2 at execution time with the SQL fragments. For example, LEXICAL1 becomes ”SEGMENT1||’\n’||SEGMENT2” and LEXICAL2 becomes ”SEGMENT1 less than 2” (assuming the user’s flexfield is made up of two segments and the user requested that the segment value of SEGMENT1 be less than 2). The actual executed SQL query might be:

      SELECT SEGMENT1||’\n’||SEGMENT2 alias, column
      FROM table
      WHERE SEGMENT1 less than 2

      The SQL statement for a user with a different flexfield structure might be:
      SELECT SEGMENT5||’\n’||SEGMENT3||’\n’||SEGMENT8 alias, column
      FROM table
      WHERE SEGMENT3 less than 2

      With this step you can alter the SELECT, ORDER BY, GROUP BY, or WHERE clause. You use this step to retrieve all the concatenated flexfield segment values to use as input to the user exit FND FLEXIDVAL in step 2 (described below).

      You call this user exit once for each lexical parameter you use, and you always call it at least once to get all segments. This raw flexfield information is in an internal format and should never be displayed (especially if the segment uses a ”hidden ID” value set).

Step 2 (Display):

      The second step requires you to call another user exit, FND FLEXIDVAL, on a ”post–record” basis. You create a new formula column to contain the flexfield information and include the user exit call in this column. This user exit determines the exact information required for display and populates the column appropriately. By using the flexfield routines the user exit can access any flexfield information. Use this step for getting descriptions, prompts, or values. This step derives the flexfield information from the already selected concatenated values and populates the formula column on a row-by-row basis.

      You call FND FLEXIDVAL once for each record of flexfield segments.

      The flexfield user exits for Oracle Reports are similar to their Oracle Application Object Library (using SQL*Forms) counterparts LOADID(R) or LOADDESC and POPID(R) or POPDESC; one to construct or load the values (FLEXSQL), the other to display them (FLEXIDVAL). The token names and meanings are similar.

Basic Implementation Steps

Step 1: Call FND SRWINIT from your Before Report Trigger

      You call the user exit FND SRWINIT from your Before Report Trigger. FND SRWINIT fetches concurrent request information and sets up profile options. You must include this step if you use any Oracle Application Object Library features in your report (such as concurrent processing).

Step 2: Call FND SRWEXIT from your After Report Trigger

      You call the user exit FND SRWEXIT from your After Report Trigger. FND SRWEXIT frees all the memory allocation done in other Oracle Applications user exits. You must include this step if you use any Oracle Application Object Library features in your report (such as concurrent processing).

Step 3: Call FND FLEXSQL from the Before Report Trigger

      You 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 descriptions and values from switched value sets as needed. You get this information by calling the AOL user exit FND FLEXSQL from the Before Report Trigger. This user exit populates the lexical parameter that you specify with the appropriate column names/SQL fragment at run time. You include this lexical parameter in the SELECT clause of your report query. This enables the report itself to retrieve the concatenated flexfield segment values. You call this user exit once for each lexical to be set. You do not display this column in your report. You use this ”hidden field” as input to the FND FLEXIDVAL user exit call. This user exit can also handle multi–structure flexfield reporting by generating a decode on the structure column. If your report query uses table joins, this user exit can prepend your code combination table name alias to the column names it returns.

      SELECT &LEXICAL alias, column

      Becomes, for example,
      SELECT SEGMENT1||’\n’||SEGMENT2 alias, column

      Note: Oracle Reports needs the column alias to keep the name of column fixed for the lexicals in SELECT clauses. Without the alias, Oracle Reports assigns the name of the column as the initial value of the lexical and a discrepancy occurs when the value of the lexical changes at run time.

Step 4: Restrict report data based upon flexfield values

      You call the user exit FND FLEXSQL with MODE=”WHERE” from the Before Report Trigger. 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 your report query. You call this user exit once for each lexical to be changed. If your report query uses table joins, you can have this user exit prepend your code combination table name alias to the column names it returns.

      WHERE tax_flag = ’Y’ and &LEXICAL less than &reportinput

      Becomes, for example,
      WHERE tax_flag = ’Y’ and T1.segment3 less than 200
      The same procedure can be applied for a HAVING clause.

Step 5: Order by flexfield columns

      You call the user exit FND FLEXSQL with MODE=”ORDER BY” from the Before Report Trigger. This user exit populates the lexical parameter that you specify with the appropriate SQL fragment at run time. You include this lexical parameter in the ORDER BY clause of your report query. You call this user exit once for each lexical to be changed. If your report query uses table joins, you can have this user exit prepend your code combination table name alias to the column names it returns.

      ORDER BY column1, &LEXICAL

      Becomes, for example,
      ORDER BY column1, segment1, segment3

Step 6:Display flex field segment values, descriptions, and prompts

      Create a Formula Column (an Oracle Reports 6.0 data construct that enables you to call a user exit). 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 you do not have to use complicated table joins to the flexfield tables. Then you create a new field (an Oracle Reports 6.0 construct used to format and display Columns), assign the Formula Column as its source, and add this field to your report using the screen painter. You need to include this field on the same Repeating Frame (an Oracle Reports 6.0 construct found in the screen painter that defines the frequency of data retrieved) as the rest of your data, where data could be actual report data, boilerplate, column headings, etc. The user exit is called and flexfield information retrieved at the frequency of the Repeating Frame that contains your field. In the report data case, the user exit is called and flexfield information retrieved once for every row retrieved with your query. All flexfield segment values and descriptions are displayed left justified. Segment values are not truncated, that is, the Display Size defined in Define Key Segments screen is ignored. Segment value descriptions are truncated to the description size (if one is displayed) or the concatenated description size (for concatenated segments) defined in the form.

17) How do you display Flexfiled Segment vales, Description and Prompts on the report?

      Create a Formula Column. Call the User Exit FND FLEXIDVAL as the formula in this column, This User exits automatically fetches more complicated information such as description and prompts so that one does not has tot use complicated table joins to the flex field tables.

18) What is a Break Group and how do you create one?

      A break group is used to create a report in which an identical values in sequential rows is printed only once for its related records.(i.e. A break group divides a query’s columns into sets).

19) What is the difference in using a ‘Group Filter’ Vs ‘Maximum Row’?

      Maximum Rows in the Query Property sheet restricts the actual number of records fetched by the query. A Group Filter determines which records can be included or exclude, after all the records have been fetched by the query.

20) Which built-in is used to call the required Report?

      RUN_PRODUCT

21) Can you pass runtime parameters from reports to a Graphical display?

      Yes you can pass

22) Do you use bind reference to replace reserved words or clauses?

      Yes

23) Which property handles a situation where an expected parameter is not passed runtime?

      Initial Value

24) Do you use System Parameter DESNAME to specify the destination type for output?

      NO, use DESTTYPE

25) Do you reference parts of SRW package in layout format triggers or report level triggers?

      Yes, you do.

26) Do you think that you can call contents of SRW Package form within any of Developer Tools?

      No, you do not.

27) Which SRW package is used to apply specified formatting attribute to the current layout object?

      SRW.SET_ATTR

28) In a Character Mode environment action with the host computer is continuous or not?

      It is Continuous.

29) In a Bit Map Mode environment action with the host computer is continuous or not?

      It is Continuous.

30) Do you think that reports stored in the database generally executes faster than those stored in the file system?

      No, Reports stored the file system generally executes faster than the report stored in the database.

                                                                                                                       Previous    Next