Coding Oracle Reports Concurrent Programs

Oracle Reports

You can write Oracle Reports reports, integrate them with Oracle Application Object Library, and run them as concurrent programs from your forms or though Standard Request Submission.

In this chapter, the Oracle Reports executable file is referred to as appsrwrun.sh. The name of your Oracle Reports executable file may vary depending on which version of Oracle Reports you use.

You have the option of using Oracle Application Object Library user exits such as dynamic currency formatting with your Oracle Reports programs.

The concurrent manager can run Oracle Reports either in character mode or in bitmap mode according to your specifications. You control the orientation and page size of your report output.

A troubleshooting guide for your Oracle Reports programs appears at the end of this chapter.

Concurrent Processing with Oracle Reports

Oracle E-Business Suite reports are submitted for concurrent processing by either the Standard Request Submission form or a specific product form such as the Run Depreciation form in Oracle Assets. A concurrent manager begins processing the request by running the appropriate Oracle Reports executable. In a UNIX environment, a concurrent manager inherits environment variable values from the shell from which it was started; as a result, reports processed by the concurrent manager will also be affected by those environment variables.

Oracle Reports Integration

For Oracle Reports programs, the concurrent manager runs the executable appsrwrun.sh on the report description file. This executable includes Oracle E-Business Suite user exits. If your Oracle Reports program produces report output, the concurrent manager can automatically print the report output file after your program completes, provided that your site has the appropriate print drivers defined.

Using PL/SQL Libraries

Immediately before running a report, the concurrent manager dynamically prepends several values onto the environment variable $REPORTS_PATH, as shown below:

REPORTS_PATH = $[PROD]_TOP/$APPLPLS:$[PROD]_TOP/$APPLREP
    :$[PROD]_TOP/$APPLREP/LANGDIR
    :$AU_TOP/$APPLPLS:$REPORTS_PATH

This puts the PL/SQL libraries in the $[PROD]_TOP/$APPLPLS, any other report objects such as external queries, boiler plate text etc. in $[PROD]_TOP/$APPLREP, and sharable libraries in $AU_TOP/$APPLPLS in REPORTS_PATH before the concurrent manager runs a report. $[PROD]_TOP is the application basepath of the application owning the report, and LANGDIR is the directory for a particular language, such as US or FR.

The APPLSYS.env, set at installation, sets REPORTS_PATH to $AU_TOP/$APPLPLS. This may be modified to include customized libraries.

See: Oracle E-Business Suite Concepts

Bitmapped Oracle Reports

If you define a concurrent program with the bitmapped version of Oracle Reports, select PostScript, HTML, or PDF as appropriate from the Output Type poplist in the Define Concurrent Program form.

You can control the orientation of the bitmapped report by passing the ORIENTATION parameter or token. For example, to generate a report with landscape orientation, specify the following option in the Execution Option field:

ORIENTATION=LANDSCAPE

Do not put spaces before or after the execution options values. The parameters should be separated by only a single space. You can also specify an orientation of PORTRAIT.

You can control the dimensions of the generated output with the PAGESIZE parameter. A specified <width>x<height> in the Execution Options field overrides the values specified in the report definition. For example:

ORIENTATION=LANDSCAPE PAGESIZE=8x11.5

The units for your width and height are determined by your Oracle Reports definition. You set the units in your Oracle Reports menu under Report => Global Properties => Unit of Measurement.

If the page size you specify with the PAGESIZE parameter is smaller than what the report was designed for, your report fails with a "REP-1212" error.

Oracle Reports Parameters

Though the concurrent manager passes program arguments to your Oracle Reports program using tokens (so that their order does not matter), you should write your program to receive arguments in the same order that you specify when you call your program and pass arguments for easier maintenance.

Your Oracle Reports program parameters should not expect NULL values. The concurrent manager cannot pass a NULL value to your program.

Important: If you are using a value set for an Oracle Reports parameter that expects a numeric value where precision will be entered as part of the value, you must define this value set with the format type Character with the Numbers Only option selected. Oracle Reports parameters cannot use value sets with format type Number if precision is to be entered as part of the value.

For Oracle Reports programs you have a choice of two implementation methods.

Standard Request Submission

If you choose to make your Oracle Reports program available through Standard Request Submission, you check the Use in SRS check box of the Concurrent Programs form and define your arguments in the Concurrent Program Parameters block. Your program is available for the Submit Request form once you use Oracle System Administration to add your program to the appropriate report security groups.

If you also call your program using FND_REQUEST.SUBMIT_ REQUEST from a form other than the Submit Request form, you supply values for your arguments in the order in which you registered them. The concurrent manager automatically adds the tokens you defined when you registered your arguments to the values you supply when you submit the program from the Submit Request form or from FND_REQUEST. The concurrent manager passes tokenized arguments (token1=parameter1, token2=parameter2, etc.) to your Oracle Reports program. In this case, each parameter value can be up to 240 characters in length, excluding the length of the associated token.

Non-Standard Request Submission

If you do not wish to make your Oracle Reports program available through Standard Request Submission, you pass tokens to your Oracle Reports program in your FND_REQUEST call from a form. In this case you do not check the Use in SRS check box of the Concurrent Programs form. Note that each argument of the form TOKEN=parameter must be a maximum of 240 characters in length, including the token name.

Accessing User Exits and Profile Options

Oracle Application Object Library lets you access user profile information and run user exits from your Oracle Reports program by including the appropriate calls in your program. These Oracle Application Object Library calls also allow your report to access the correct organization (for multiple organizations or "multi-org" installations) automatically.

Call FND SRWINIT and FND SRWEXIT

To access profile values, multiple organizations, or Oracle E-Business Suite user exits, and for your program to be used with concurrent processing at all, you must have the first and last user exits called by your Oracle Reports program be FND SRWINIT and FND SRWEXIT.

FND SRWINIT sets your profile option values and allows Oracle Application Object Library user exits to detect that they have been called by a Oracle Reports program. FND SRWEXIT ensures that all the memory allocated for Oracle Application Object Library user exits has been freed up properly. The steps below ensure that your program correctly calls FND SRWINIT and FND SRWEXIT.

Warning: With future releases of Oracle Application Object Library and Oracle Reports, we may provide a simpler set of steps to access FND SRWINIT and FND SRWEXIT. We reserve the right to discontinue support for these steps. If you use the steps below to integrate your Oracle Reports programs with Oracle Application Object Library, you should plan to convert to a different set of integration steps in the future.

Calling Other Oracle Application Object Library User Exits

These integration steps let you call certain Oracle Application Object Library user exits, in addition to FND SRWINIT and FND SRWEXIT, to access user profile values and perform calculations in your Oracle Reports program:

See: Oracle E-Business Suite Flexfields Guide

Note that you can call many Oracle E-Business Suite PL/SQL routines, such as user profiles routines, from your Oracle Reports programs as well as these user exits. In general, you should use PL/SQL routines instead of user exits where possible.

You can test your Oracle Reports program that calls Oracle E-Business Suite user exits by running appsrwrun.sh from the operating system.

User Exits Used in Oracle Reports

The user exits available in Oracle Reports are:

See: Oracle E-Business Suite Flexfields Guide

FND SRWINIT / FND SRWEXIT

FND SRWINIT sets your profile option values and allows Oracle Application Object Library user exits to detect that they have been called by an Oracle Reports program. FND SRWINIT also allows your report to use the correct organization automatically. FND SRWEXIT ensures that all the memory allocated for Oracle Application Object Library user exits has been freed up properly.

FND FLEXIDVAL / FND FLEXSQL

These user exits allow you to use flexfields in your reports. They are documented in the Oracle E-Business Suite Flexfields Guide.

Using Dynamic Currency in Oracle Reports

Currency formatting support provides a flexible, consistent method to format a numeric value according to its associated currency. The currency value appears with the correct thousands separator and radix character (decimal point) of the user's country. The value appears with positive and negative indicators of the user's choice.

Displayed currency values are never rounded or truncated except when explicitly specified to be scaled. If the formatted value (which includes the thousands separator) does not fit in the output field, then the currency value without the thousands separator is used. If this value is still too large for the output field, then asterisk characters (***) are displayed in the field to alert you of an overflow condition.

You use the same methodology to add support for report regions with one currency type (for example, US dollar amounts) and for report regions with mixed currency types (for example, US dollar and Japanese yen amounts). However, when reporting on mixed currency amounts you include a special argument to indicate that you wish to align all different currency types at a standard point (usually the precision of the currency with the greatest precision). This precision is defined by a profile option or set by the user of the report at execution time.

Currency Formatting Requirements

A report based on a single currency type should display currency amounts aligned along the radix character as illustrated in the following example:

 Currency Value  Code
                 --------------  ----
                     120,300.00  USD
                      -4,201.23  USD
                or
              120,300.00   USD
                     (4,201.23)  USD
                or
                    120,300.00+  USD
                     -4,201.23   USD

If the user chooses a negative or a positive indicator such as parentheses that appears at the right of the currency amount, then values are not flushed with the right margin but are shifted to the left to accommodate the indicator.

A mixed currency report should display currency amounts aligned along the radix character (or implied radix for currencies with no precision like JPY).

   Currency Value  Code
   --------------  ----
                    300.00       USD
                    105.250      DNR
                  1,000          JPY
                -24,000.34       FRF

Call the FND FORMAT_CURRENCY user exit to format the Currency Value column. In this mixed currency report, the minimum precision (specified in the MINIMUM_PRECISION token in the user exit) is set to 3.

FND FORMAT_CURRENCY User Exit

This user exit formats the currency amount dynamically depending upon the precision of the actual currency value, the standard precision, whether the value is in a mixed currency region, the user's positive and negative format profile options, and the location (country) of the site. The location of the site determines the thousands separator and radix to use when displaying currency values. An additional profile determines whether the thousands separator is displayed.

Use the Currencies window to set the standard, extended, and minimum precision of a currency.

You obtain the currency value from the database into an Oracle Reports column. Define another Oracle Reports column, a formula column of type CHAR, which executes the FORMAT_CURRENCY user exit to format the currency value. A displayed field has this formula column as its source so that the formatted value is automatically copied into the field for display.

See: Oracle General Ledger User's Guide

Syntax

  FND FORMAT_CURRENCY
CODE=":column containing currency code"
        DISPLAY_WIDTH="field width for display"
        AMOUNT=":source column name"
        DISPLAY=":display column name"
        [MINIMUM_PRECISION=":P_MIN_PRECISION"]
        [PRECISION="{STANDARD|EXTENDED}"]
        [DISPLAY_SCALING_FACTOR="":P_SCALING_FACTOR"]

Options

Variable Description
CODE Specify the column which contains the currency code for the amount. The type of this column is CHARACTER.
DISPLAY_ WIDTH Specify the width of the field in which you display the formatted amount.
AMOUNT Specify the name of the column which contains the amount retrieved from the database. This amount is of type NUMBER.
DISPLAY Specify the name of the column into which you want to display the formatted values. The type of this column is CHARACTER.
MINIMUM_ PRECISION Specify the precision to which to align all currencies used in this report region. You specify the MINIMUM_PRECISION token in mixed currency report regions to ensure all currency values align at the radix character for easy readability. Your user can adjust the report by setting an input parameter when submitting the report to specifically tailor the report output to a desired minimum precision or accept the default which is determined from the profile option CURRENCY:MIXED_PRECISION (Currency:Mixed Currency Precision). Your report submission must pass the value as a report argument. You use P_MIN_PRECISION as the name of this lexical.
PRECISION If specified as STANDARD, then standard precision is used. If the value is EXTENDED then the extended precision of the currency is used to format the number.
DISPLAY_ SCALING_ FACTOR Optionally, specify the scaling factor to be applied to the amount in that column. If this token is not specified or is negative no scaling is performed. You use P_SCALING_FACTOR as the name of this lexical parameter.

Important: Colon ":" is placed in front of column names and lexical parameters for token values. This indicates that the value of that token is retrieved from the column or lexical parameter. If it is omitted the value within double quotes itself is used. For example, CODE=":C_CODE" indicates that currency code should be retrieved from column CODE while CODE="C_CODE" indicated that currency code is C_CODE.

Mixed Currency Reports

Every report with a mixed currency region should allow the user to override the default setting of the mixed currency precision profile option at submission time. Define a report argument that accepts this value.

The default value of this argument should be the profile option CURRENCY:MIXED_PRECISION (Currency:Mixed Currency Precision) so the user does not always have to set a value explicitly.

Example Output

The following graphic illustrates various input values and the currency amounts displayed using the user exit (negative amounts are surrounded by parentheses) assuming the report was run in the United States.

  Item  Code           Input Number  Output    Field  Notes
        ----  ----      -----------------  ---------------  -----
         01    USD              123456.76     123,456.76
         02    USD                  156.7         156.70
         03    USD                  12345      12,345.00
         04    BHD             123456.764     123,456.764
         05    JPY               12345676  12,345,676
         06    BHD              12345.768      12,345.768
         07    BHD             -12345.768     (12,345.768)
         08    BHD             123456.768     123,456.768
         09    BHD            -123456.768    (123,456.768)
         10    BHD            1234567.768   1,234,567.768
         11    BHD           -1234567.768  (1,234,567.768)
         12    BHD           12345678.768  12,345,678.768
         13    BHD          -12345678.768   (12345678.768)   [1]
         14    BHD          123456789.768  123,456,789.768   [2]
         15    BHD         -123456789.768  (123456789.768)
         16    BHD         1234567890.768  1234567890.768
         17    BHD        -1234567890.768  ***************   [3]
         18    BHD        12345678901.768  12345678901.768  [1,2]
         19    BHD       -12345678901.768  ***************   [3]
         20    BHD       123456789012.768  ***************   [3]         21    USD       123456.765     123,456.765
         22    USD      123456.7654     123,456.7654   [2]
         23    USD     123456.76543    123,456.76543   [2,4]

Code  Name          Precision
        USD   US dollars       2
        BHD   Bahraini dinars  3
        JPY   Japanese yen     0

[1] - Thousands indicators are stripped
[2] - Digit occupies space normally reserved for
                      positive or negative indicator 
[3] - Value cannot fit in the field:  overflow
                condition
[4] - Radix is shifted to the left due to the precision
                of the number exceeding MINIMUM_PRECISION

If the precision of the input number is less than the precision of the currency then the number is padded with 0's to match the precision of the currency.

If the precision of the input number is greater than the precision of the currency then the radix of that number might get misaligned from other currency amounts in the column.

If there is one space allocated on the right for displaying the positive or negative format character (for example the profile option for displaying negative number is set to "()" or "<>") and the current number does not use that space (if the number is positive) then that space is used. If this is not sufficient, then the number is left shifted to display the full precision.

If the formatted number does not fit within the DISPLAY_WIDTH then the user exit strips off all thousands separators so as to fit the number in the allowable DISPLAY_WIDTH. The amount may again be misaligned. If it still does not fit then asterisks are printed in that field to indicate an overflow condition.

Currency values are never rounded or truncated on display by default. However, the values can be scaled by a number if explicitly specified by the DISPLAY_SCALING_FACTOR token.

The tokens MINIMUM_PRECISION=":P_MIN_PRECISION" (where the lexical argument was designated as 3) and DISPLAY_WIDTH="15" apply to all items.

Items 1 through 5 show how various currencies display in a mixed currency region with a MINIMUM_PRECISION of 3. All values align along the radix character.

Items 6 through 20 depict how positive and negative values are displayed as both amounts progressively increase in magnitude (DISPLAY_WIDTH is a constant 15). When the formatted value exceeds DISPLAY_WIDTH the thousands separators are removed as in items 13, 15, 16, and 18. When the unformatted value exceeds DISPLAY_WIDTH asterisks are displayed indicating an overflow as in items 17, 19, and 20. Notice also that the positive value shifts right into the space normally reserved for the negative indicator.

Items 21 through 23 show the effects when displaying a value which exceeds the MINIMUM_PRECISION. Since the negative indicator uses a space on the right, a positive value must exceed MINIMUM_ PRECISION+1 before it shifts to the left.

Example Report Using FND FORMAT_CURRENCY

The following report illustrates how various currencies are formatted using the FND FORMAT_CURRENCY user exit for a report which displays mixed currency values. This document explains how you develop such a report.

Information about the radix character and thousands separator are determined from the location of the user. The special display for negative and positive currency values is specified by two profile options. Hence, a report can appear differently depending upon the location of the user and the profile options values set.

The following reports, one run by a user in United States and the other by a user in Germany, depict this difference. In Germany the radix character and thousand separator are switched from the US counterpart. In these reports, both Manama and Seattle had a loss and the negative numbers display in parentheses () or angle brackets <> depending upon the user's preference.

Sample Report Output

Report 1 Run in The United States

Settings include:

Report 2: Run in Germany

Settings include:

Procedure for Sample Report

  1. First define all the parameters (using the Oracle Reports Parameter Screen). Use these parameters in the user exit calls and SQL statements.

    Name:                P_CONC_REQUEST_ID
Data Data Type: NUMBER
Width:          15
Initial Value:  0

    You always create this lexical parameter. "FND SRWINIT" uses this parameter to retrieve information about this concurrent request.

    Name:                P_MIN_PRECISION
Data Type:      NUMBER
Width:          2
Initial Value:

    You reference this lexical parameter in your FND FORMAT_CURRENCY user exit call.

  2. Call FND SRWINIT

    You always call FND SRWINIT from the Before Report Trigger as follows:

    SRW.USER_EXIT('FND SRWINIT');

    This user exit sets up information for use by profile options and other AOL features.

    You always call FND SRWEXIT from the After Report Trigger as follows:

    SRW.USER_EXIT('FND SRWEXIT');

    This user exit frees all the memory allocation done in other AOL exits.

  3. Create the Currency Code Query

    Create a query which selects the currency code and the currency amount from your table. In this case you might use:

     SELECT OFFICE,
                       SUM(AMOUNT)   C_INCOME,
                       CURRENCY_CODE C_CURRENCY
                FROM OFFICE_INCOME
                WHERE TRANSACTION_DATE = '01/92'
                ORDER BY BY OFFICE

  4. Create a column for the currency call.

    Create one column (C_NET_INCOME) which contains the user exit (FND FORMAT_CURRENCY) call. This is a formula column which formats the number and displays it. The user exit call looks like the following:

    SRW.REFERENCE(:C_CURRENCY); 
SRW.REFERENCE(:C_INCOME);         
SRW.USER_EXIT('FND FORMAT_CURRENCY 
                CODE=":C_CURRENCY"                 
                DISPLAY_WIDTH="15"                 
                AMOUNT=":C_INCOME"                 
                DISPLAY=":C_NET_INCOME"                  
                MINIMUM_PRECISION=":P_MIN_PRECISION"');  
RETURN(:C_NET_INCOME);

    Tip: Always reference any source column/parameter which is used as a source for data retrieval in the user exit. This guarantees that this column/parameter will contain the latest value and is achieved by "SRW.REFERENCE" call as shown above.

    Here the column name containing currency code is "C_CURRENCY" and the field width of the formatted amount field is 15. The source column is "C_INCOME" and the resulting formatted output is placed in "C_NET_INCOME". The minimum precision of all the currencies used for this report is retrieved from the lexical P_MIN_PRECISION (which in this case is set to 3). At the end of the user exit call remember to reference the column "C_NET_INCOME" by RETURN(:C_NET_INCOME), otherwise the column may not contain the current information.

    You do not include the MINIMUM_PRECISION token for single currency reports.

  5. Hide the amount.

    In Default layout, unselect the amount column (C_INCOME) so that it is not displayed in the report. Do not display this amount because it contains the unformatted database column value. In the layout painter update the boiler plate text for each displayed currency field (which in this case are C_CURRENCY and C_NET_INCOME)

    Important: Repeat steps 4 and 5 for each displayed currency field.

  6. Create the title.

    In the layout painter paint the boiler plate text title as follows moving previous fields and boiler plate text as necessary:

     Net Income for January 1992
               ---------------------------

  7. Define your report with Oracle Application Object Library.

    Define your report with Standard Request Submission. Ensure you define an argument P_MIN_PRECISION which defaults to $PROFILE$.MIXED_PRECISION.

    The report is now ready to be run.

Summary

A brief summary of the report specifications:

Lexical Parameters:

Column Names:

Application Object Library User Exits:

Oracle Reports Troubleshooting

This section contains tips for troubleshooting common problems and error messages.

Concurrent Request Logs

The first step of your debugging should be to examine the log of concurrent request for obvious errors.

Running from the Operating System

If you cannot determine the cause of problem from the concurrent request log, run the report from the operating system. Use the Oracle E-Business Suite linked Oracle Reports executable to run the report. Along with the standard Oracle Reports arguments, run the report with the arguments passed by the concurrent manager. The arguments passed by the concurrent manager can be found in the beginning of the concurrent request log under the title "Arguments".

If you can run the report from the command line, that indicates that there is a problem in the environment from which the concurrent manager was started. Ensure that you start the concurrent managers from the same environment in which you are trying to run the report.

Run the Print Environment Variable Values Report

The concurrent manager inherits its environment variables from the shell from which it was started and then runs reports using this environment. This environment could be different from that a user sees logging into the Applications because the concurrent manager may have been started by a different user with different environment settings. Due to this difference, it is sometimes difficult and confusing to determine the cause of errors in running reports.

If you want to examine the value of few variables, you can run "Prints environment variable values" report to print out the variable as seen by the concurrent manager to see if it is correct. Very often problems such as a problem in compilation or the concurrent managers inability to locate a library happen due to an incorrect REPORTS_PATH.

Frequently Asked Questions

Why does my report only fail from the concurrent manager?

This is because the environment from which the concurrent manager launches a report is different from the one you are using when running the report from the command line.

Why does my report show different data?

If your report shows different data when you run it as a standalone report than when you run it from the concurrent manager, you may find that you get different data from the different situations. This is usually due to different (or no) profile option or other values being passed to your report by the concurrent manager. This is especially likely if your report accesses multiple organizations data.

If you have commented out the calls to SRWINIT and SRWEXIT to test your report from a development environment that does not have the Oracle Application Object Library user exits linked in (for example, Microsoft Windows), check that you have re-enabled those calls before trying to run your report from the concurrent manager.

Why do I get the error REP-0713 when I run my report?

Oracle Reports uses a text file called uiprint.txt to hold printer names. If your printer name is not in this file, you can get the REP-0713 error.

My bitmapped report does not print in landscape. Why?

Print styles such as Landscape are associated with printer drivers that send instructions telling the printer how to print text files. However, bitmapped reports are not text files.

Bitmapped reports are output as PostScript files. The PostScript file is a set of instructions telling the printer exactly what to print and how to print it. To get a landscape report, the PostScript file must be generated as landscape.

If you want a landscape bitmapped report, specify this either in the Reports Designer or in the execution options of your concurrent program.

When printing bitmapped reports, a print style is still needed to determine the printer driver used. To avoid confusion, create a special print style for bitmapped reports and make that the required style for all bitmapped reports in the Define Concurrent Programs form.

Why do I get many pages of nonsense when I print my report?

You are looking at the PostScript code. The printer driver you are using caused the printer not to recognize the file as being PostScript. Check your driver. Some initialization strings will cause this problem. Also, do not use the program "enscript" to do the printing.

What does the "REP-0065: Virtual Memory System error" mean?

Unfortunately this is not a very informative error message. This could occur due to various reasons. Try the following to isolate the problem: