Blitz Report™ Developer Guide

Introduction

Blitz Report is an Oracle Forms based software, fully integrated with Oracle E-Business Suite. It enables your IT team to easily store and edit SQL scripts for reports, and to make them available to your business users. Blitz Report runs as a concurrent process and generates output files in XLSX or text delimited CSV format. Upon completion, reports automatically download and open in Excel.

With Blitz Report, we created the most efficient and easy to use operational reporting solution for Oracle EBS. Optimized for skilled IT professionals to better organize and maintain their reporting queries, and for business users to quickly access EBS data in a format they love without having to learn new skills.

We hope that you will enjoy working with Blitz Report as much as we do, and we welcome your feedback to [email protected]

1 Concepts

This chapter explains the building blocks of Blitz Report before the following chapters describe them in detail. Reading it first makes the rest of the guide much easier to follow.

1.1 How a report executes

A Blitz Report is a SQL query plus a set of parameters. At runtime, the entered parameter values are combined with the SQL: bind parameters are bound directly, optional filter conditions are injected at anchor positions in the WHERE clause, and lexical parameters replace placeholders with SQL fragments. The resulting query runs as a background concurrent request and produces the output file, formatted by the selected template.

1.2 Parameters, lists of values and anchors

Parameters carry a type (Char, Number, Date or LOV), an optional list of values, a default value and dependencies on other parameters. The anchor decides how a parameter reaches the SQL: a bind anchor (:parameter) binds the value where the bind appears, a WHERE anchor (1=1) injects the parameter’s SQL condition only when a value is entered, and a lexical anchor (&name) replaces a placeholder with a SQL fragment. Lists of values are shared components that can be reused by many reports.

1.3 Templates

Templates define how the output looks without changing the SQL: which columns are displayed in which order, formatting, pivot tables, and per-template parameter defaults and exclusions. Every report can have several templates, and templates can be shared at site, responsibility or user level. Templates can also pre-fill and fix column values for uploads, freeze panes, lock parameter default values, and be restricted to specific users or responsibilities.

1.4 Uploads

A Blitz Upload is a report of type Upload: its SQL defines the columns of the Excel template and downloads existing records for updating. Validate and Save in the workbook performs a basic validation only, checking that required columns are populated and running any additional Excel validations defined for the upload. Uploading the file then starts the upload concurrent program, which processes each row through the upload API — a PL/SQL procedure writing the data through standard Oracle APIs — or through interface tables, and reports each row’s outcome in the result file.

1.5 Assignments and categories

Assignments control who can see and run a report: they grant it at site, application, request group, responsibility, user, form or function level, with include and exclude rules. Categories group reports into the folders shown in the run screen’s category list. A report without an assignment is only visible to users with development access.

1.6 Security

Four access levels (User, Administrator, Developer, System) control what a user may do in Blitz Report, and the data a report returns is restricted by the user’s responsibility context, exactly as in Oracle EBS itself. The Security chapter covers access levels, assignments, upload security, data security and licensing in one place.

1.7 Multi-language support

Report names, descriptions, parameter names and column headings are translatable and follow the user’s EBS session language, so one report definition serves all installed languages.

1.8 Architecture

Blitz Report consists of a database tier (the report engine and repository), the EBS Forms and web user interfaces, and webservice endpoints that serve the Excel add-ins. The architecture diagram in the Technical architecture chapter shows how the pieces connect.

2 Tutorial: your first Blitz Report

This tutorial creates a small supplier list report from scratch. It takes about fifteen minutes and touches every concept a report developer needs: SQL, a parameter with an anchor, an assignment and a first run. The following chapters describe each step’s options in full detail, and there is also a training video on creating reports.

1. Open the Blitz Report setup form (development access required) and create a new report named ‘My Supplier List’ in a category of your choice.

2. Enter the report SQL. The literal 1=1 is the anchor where optional parameter filters will be injected:

select
aps.vendor_name supplier,
aps.segment1 supplier_number,
aps.creation_date
from
ap_suppliers aps
where
1=1

3. Add a parameter named ‘Supplier Name like’ with anchor 1=1 and the SQL text below. When the user enters a value, the condition is injected at the anchor; when the parameter is left empty, no filter is applied:

aps.vendor_name like :supplier_name

4. Assign the report so users can see it, for example to your responsibility. Without an assignment, the report is visible to developers only.

5. Run it: open the Blitz Report run screen, select ‘My Supplier List’, enter a name pattern such as A% and click Run. The output opens in Excel.

6. Optionally create a template to reorder columns or add a pivot, and save it as the default layout for the report.

3 Report development

3.1 Anchors and binds

Anchors are ‘placeholders’ in the extraction SQL, which allow the precise placement of additional (optional) parameterized SQL clauses at run-time. These clauses are constructed from user-specified parameters, which are then inserted into the extraction SQL at run-time. There are two types of Anchors:

n=n

WHERE clause SQL anchors, such as ‘1=1’, ‘2=2’ etc. Blitz Report inserts the associated SQL text directly before these anchors, automatically adding the keyword ‘and’ and a line feed, to create valid SQL. This allows quick parameter creation – no need to consider the precise position of the ‘and’ keyword.

A typical example for a SQL text would be ‘column_name=:bind_variable’, where :bind_variable would be bound with the parameter value entered by the user.

&lexical

Lexical parameter references work in the same way as lexical parameters in sqlplus or Oracle reports. Blitz Report replaces these placeholders completely with the parameter SQL text at run-time. To replace a lexical with the user entered parameter value, use the string in the SQL text field. If a parameter value is left blank, the corresponding reference is removed before SQL execution.

However, the ‘n=n’ anchor can only be used for WHERE clauses (since the ‘n=n’ syntax remains in the run-time SQL).

The ‘&lexical’ anchor can be used to add whatever SQL ‘snippets’ are required by the report, featuring (but not limited to):

• WHERE clauses
• Dynamic tables and columns
• ORDER BY, GROUP BY
• HINTS
• Complete subselects or EXISTS clauses

The same anchor may be used multiple times inside a report SQL. Blitz Report inserts the corresponding parameter text for each occurrence.

A lexical parameter SQL text may contain a bind variable, which will be bound with the parameter value entered by the user when running the report.

In case you require the parameter value to show up as lexical text in the SQL, e.g. to purposefully enforce reparsing for different parameter values, you can use placeholder <parameter_value> as shown in the examples table below.

:bind

Similar to other reporting solutions, Blitz Report also supports the use of bind parameters. To avoid performance issues due to due to nvl(:bind_variable, column_name) coding for optional parameters however, it is recommended to use one of the above anchors for dynamic SQL instead.
Some words are reserved by Oracle and can not be used as bind variables. To find a list of such words use the following query:

select keyword from v$reserved_words where reserved='Y' or res_semi='Y' order by keyword asc;

Examples

where
1=1

fu.user_name=:user_name

where
fu.user_name=:user_name and
1=1

where
1=1

furg.user_id in (select fu.user_id from fnd_user fu where fu.user_name=:user_name)

where
furg.user_id in (select fu.user_id from fnd_user fu where fu.user_name=:user_name) and
1=1

where
&account
hp.party_id=hca.party_id

hca.account_number=:account and

where
hca.account_number=:account and
hp.party_id=hca.party_id

group by
&group_by_vendor
pha.currency_code

pv.vendor_id,

group by
pv.vendor_id,
pha.currency_code

select
&columns
frv.responsibility_name

fu.user_name,
fu.email_address,

select
fu.user_name,
fu.email_address,
frv.responsibility_name

select 
xxen_util.dis_user_type(eap.ap_eu_id,'&eul') user_type, 
xxen_util.dis_user(eap.ap_eu_id,'&eul') username, 
eap.* 
from 
&eul.eul5_access_privs eap

select 
xxen_util.dis_user_type(eap.ap_eu_id,'eul_us') user_type, 
xxen_util.dis_user(eap.ap_eu_id,'eul_us') username, 
eap.* 
from 
eul_us.eul5_access_privs eap

select
&flexfield_columns
from
mtl_system_items_vl msiv

select
'msiv.'||lower(fdfcuv.application_column_name)||' "'||fdfcuv.form_left_prompt||'",' column_text
from
fnd_descr_flex_col_usage_vl fdfcuv
where
fdfcuv.application_id=401 and
fdfcuv.descriptive_flexfield_name='MTL_SYSTEM_ITEMS' and
fdfcuv.enabled_flag='Y' and
fdfcuv.display_flag='Y' and
fdfcuv.descriptive_flex_context_code='Global Data Elements'
order by
fdfcuv.column_seq_num

select
msiv.attribute1 "Late Demands Penalty",
msiv.attribute15 "Invoice UOM",
msiv.attribute14 "Graphical Link for Web Reqs",
msiv.attribute2 "Material Over-Capacity Penalty",
from mtl_system_items_vl msiv

where
fu.user_name=:user_name

where
fu.user_name=:user_name

:sheet_name

By default the Excel output’s data sheet name is the same as the report name. A parameter referencing the :sheet_name anchor allows to define a custom sheet name. It can be set up as a standard visible parameter, but also hidden by using a negative display sequence, for example dependent on another parameter. In the example below the Sheet name parameter inherits its value from the Operating unit parameter concatenated with the word ‘suppliers’ by using the following default value: :$flex$.operating_unit||’ suppliers’

3.2 Dynamic SQL example

n=n anchor example

A query on parties and accounts (see below) should allow users to extract all customers’ information or to restrict the data by optional parameters such as customer name or account number. There is a training video available on advanced parameters, dynamic SQL, LOVs and parameter dependencies.

A restriction to customer name would require addition of a WHERE clause:

In Blitz Report, the  parameterized WHERE clauses are set up separately from the report SQL. Blitz Report only inserts individual WHERE clauses into the extraction SQL (at run-time) if the user enters a value for that particular parameter.

In this example, If the user provides a value for the customer name parameter, Blitz Report would add the above WHERE clause at the position of the anchor ‘1=1’ and execute the below SQL for data extraction.

Pivot table in SQL

There is possibility to have dynamic pivot table described in SQL. It is done using &lexical parameter reference.

For example you need to list your balances grouped by ledger and code combination.

We want to introduce additional restriction on a ledger and build pivot table in the output based on the input list of the periods.

Ledger name restriction will be placed after WHERE clause in the place of “1=1” anchor if parameter value is provided.

List of periods columns inserted in SQL statement during execution in the place of lexical &gl_period_pivot reference.

Below you can see how parameters for pivot basis report are described.

In example below lexical reference returns the list of periods according to listed below rules:

• Period year is derived based on value indicated in Period parameter.
• List contains periods of derived year starting from the first till the one indicated in Period parameter.

All period names are enclosed with single quotes and separated by commas.

SQL code during execution looks like that:

3.3 Report header

Name

Report name uniquely identifies reports. Names should be short and descriptive.

Good practice is to prefix report names with the appropriate Oracle EBS module short code.

Description

An optional report description of maximum 4000 characters may be set up to assist users in understanding and using the report.

Search

This Google like search functionality retrieves reports by report name, description or underlying SQL. You can search for example for a table or column name accessed by a SQL, or by parts of the report name or description.

Category

Retrieve reports by category.

Disabled

Defines whether a report is hidden from end users. When the Disabled flag is set, the report is removed from the user report list. This may be useful during the development and testing phase, or to hide specific seeded reports during a phased rollout. Even if a report is disabled, it may still be run by users having User Admin, Developer or System access.

If you require to mass disable reports from a specific category, for example ‘Enginatics’, you can use a direct table update as shown in the following example script.

mass_disable_reports_by_category

Version

Click on a report’s version number to review the change history and previous report SQLs. A new version number is added and stored automatically for each report SQL update. Other report setup modifications such as report name, description or parameter changes are not tracked in the version history.

Type

The Type field determines a report’s purpose and who can run or modify it:

• Standard (Type left blank) — regular reports, available to all users with the appropriate assignments.
• Protected — same as Standard, but editable only by users with the ‘System’ access profile. Use this for reports that should not be modified by developers, such as critical outbound interfaces.
• System (no output) — reports that provide system functionality without producing user output, such as the Supply Chain Hub item search screen. Only users with the ‘System’ access profile can run or modify them.
• Upload — Blitz Upload definitions used to load or update data in Oracle EBS.
• Drilldown — reports used as drilldown targets from the ‘GL Financial Statement and Drilldown (FSG)’ report. They do not appear in the main report list and are launched only via drilldown links from FSG.
• FSG — the Financial Statement Generator report that combines Oracle GL balances with Excel formatting.

BIP Code

BI Publisher data definition code. When populated, Blitz Report executes the dataTrigger section e.g. beforeReportTrigger of the associated data definition XML template. This allows running BI Publisher report SQLs through Blitz Report where the data extracted is based on global temporary tables preprocessed in the before report trigger.

Double-click the BIP Code to download and view the data definition XML template.

Number Format

Format for numeric value display in Excel output files. By setting the number format, you can, for example, change the number of decimals or the display style and color of negative numbers.

The number format can either be set by a profile option, for all columns in a report, or for individual columns as described in the column translations section.

An explanation of how to use Microsoft’s custom number format codes can be found in this blog or on the Microsoft website.

Report Options

Report Options define additional attibutes and processing options for a specific report:

1. The report from which it was copied
2. Before and after report triggers which can be used for data pre- and post processing, e.g. for table data updates before executing a report
3. Email to identify the author of a report
4. Default email to sent the report output after its completion
5. Default report output format
6. Default limitation on the number of rows returned by the report
7. Time limit in minutes after which the report is terminated by the ‘Blitz Report Monitor’ program
8. Custom unix script to be executed after the report completion
9. Excel output file name, downloaded to the client desktop
10. Directory on the application server to save a copy of the report output file
11. Naming convention for the copy written to the server output directory
12. Reqest type for defining concurrent managers specialization rules
13. Standby database name to run report on

DB Package

Package name containing pre and post processing functions, which can be used for example to call Oracle standard PLSQL code or to run additional processes before or after report execution. The functions need to follow the naming convention: afterpform, beforereport, afterreport and return the boolean data type.
An example of how such functions can be used is the FA Depreciation Projection report, which requires running the Oracle standard ‘Depreciation Projection’ concurrent program first, before the report retrieves the data. The Blitz Report references a custom database package XXEN_FASPRJ, which submits and waits for completion of the Depreciation Projection program, before executing the actual report SQL.

Parameter values are passed to the custom package through variables, which appear in the list of Blitz Report parameter anchors.

The runtime values of these parameters can be used inside the package body.

Here are screenshots of the parameter values and logfile showing the values assigned to the package variables before executing function xxen_fasprj.beforereport.

Author email

Email address of the report author.

Additional information can be found on registered authors in our online library.

Email

Default email address for sending output files. If different email address are set up on different levels, the default email on the Blitz Report run window is derived in following order:

1. Default email setup on report level
2. Profile option Blitz Report Default Email Address

It is possible to populate this field in two formats:

1. Comma-separated email list. E.g. [email protected],[email protected]
2. SQL statement returning one column containing email addresses. For example the statement returning the email address of the current EBS user:

select
coalesce(fu.email_address,
(select papf.email_address from per_all_people_f papf where fu.employee_id=papf.person_id
and sysdate between papf.effective_start_date and papf.effective_end_date)) email
from
fnd_user fu
where
fu.user_id=fnd_global.user_id

Output Format

Allows changing the output format from Excel XLSX (Excel) to CSV (comma separated values) or TSV (tab separated values).

Row Limit

Limits the maximum number of lines for report execution.

Time Limit

Maximum run time limit in seconds. The Blitz Report Monitor concurrent program automatically cancels reports exceeding the set time limit. This avoids excessive server load if a user submits a report with insufficient parameter restrictions for example.

A time limit can also be set when running reports or using profile option Blitz Report Time Limit. If there are values set on different levels, the order of precedence is as follows:

1. Run window options time limit
2. Profile option on user level
3. Time limit on report level
4. Profile option on responsibility level
5. Profile option on site level

Custom Postprocess

Provides the same functionality as the Custom Postprocess runtime option to default a postprocess on report level.

Output File Name

Provides the same functionality as the Output File Name runtime option to default the output file name format on report level.

Server Output Directory

Provides the same functionality as the Server Output Directory runtime option to default the server output directory on report level. Saves a copy of the report output file in the specified directory on the application server, which can be any network folder mounted there. Tokens can be used to create a directory path dynamically, for example based on report parameter values. If the resulting directory does not exist on the filesystem, it is created. Please ensure that the apps owner has the required permissions for the directory creation.

Report level defaults are validated in the same way as user entered values. If the Blitz Report Server Output Directory Access profile option restricts a user to the approved output locations, a report defaulting a directory outside that list fails for that user with a corresponding error message. Users with ‘Developer’ or ‘System’ access are not restricted.

Server File Name

Provides the same functionality as the Server File Name runtime option to default the naming convention for the copy written to the server output directory. Tokens can be used to create a file name dynamically, for example based on report parameter values.

Request type

Allows assigning request types defined under System Administrator > Concurrent > Program > Types to specific Blitz Reports. Request types can be used in concurrent managers specialization rules, for example to configure certain slow running Blitz Reports to be processed by a separate concurrent manager.

Target Database

Allows running blitz reports on a standby database. Specify a TNS descriptor defined in $TNS_ADMIN/tnsnames.ora on the apps server.

SQL

The report extraction SQL must start either with the word ‘select’ or ‘with’. Blitz Report does not parse the SQL syntax for validity. SQL entry through the form is limited to 32767 characters. To create a report with a larger SQL, use the Upload Large SQL functionality from the tools menu.

‘Blitz Report Information’ descriptive flexfield

New ‘Blitz Report Information’ descriptive flexfield allows to store additional information, e.g. for change management

3.4 Parameters

Parameter definitions consist of:

• a parameter name, display sequence and optional parameter description and default value
• SQL text to be inserted into the report SQL dynamically at run-time
• an anchor as a reference to a position for the insertion
• a parameter type and optional LOV
• an optional matching value to restrict the SQL text insertion to certain parameter values

Display Sequence

Sequence number that defines the order in which parameters are displayed.

If different WHERE clauses are used for the same parameter name, e.g. to insert a different SQL text at different SQL positions or depending on different parameter matching values, display sequence is populated only for one record and left blank for subsequent lines of that parameter name. Example: To achive better performance, report FND Concurrent Requests uses different WHERE clauses for for parameters Phase and Status, depending on the parameter value entered by the user (Matching Value).

Negative display sequence numbers are used to define hidden parameters. These can be used to populate a &lexical with a SQL text dyamically before report execution. Report AR Transactions and Lines, for example, uses a hidden Ledger parameter to dynamically generate a string for the revenue account columns, depending on the chart of accounts segment setup.

Parameter Name

Parameter identifier. You can use the LOV to copy existing parameter definitions from other reports.

If you need a different SQL text in different SQL positions for one parameter, you can have more than one entries for the same parameter name, but only one of them can have a display sequence, parameter type and list of values setup (example: FND Concurrent Requests).

SQL Text

Parameter specific text added dynamically into the report SQL if a value for the parameter is entered at run-time. Usually, the SQL text forms a where-clause restriction including a bind variable name starting with a colon e.g. ‘:account_number’. Blitz Report automatically detects the variable and binds it with the value entered by the user. A maximum of one bind variable per parameter is allowed. If a parameter’s SQL text contains more than one bind variables, only the first one is bound with the entered parameter value.

Blitz Report also allows the conditional insertion of different SQL text for the same parameter, depending on the parameter value entered by the user, using matching values. If you define a matching value, Blitz Report inserts the corresponding SQL text only if the user enters a parameter value that matches the specified matching value.

Multiple Values

If the SQL text includes a bind variable restriction and the user checks the multiple values checkbox, Blitz Report automatically replaces the restriction with an IN-clause during SQL execution. This replacement works for restrictions using ‘equal’, ‘like’, ‘not equal’ or ‘not like’ operators such as:

You can also use functions, for example:

If the parameter SQL text is too complex or there is a different operator used e.g. column>=:bind_variable, automated IN-clause replacement is not possible and the multiple values checkbox is not available for this parameter. For multiple values, it is required to have the table column on the left and the :bind on the right side of the comparison operator. A SQL text such as :bind_variable=column_name will not work.

Anchor

The position inside the report SQL where the parameter SQL text is inserted. The LOV for this field shows all anchors used in the SQL. See here for detailed explanation of Anchors.

Parameter Type

The parameter type definition controls validation of parameter values at run-time and there are the following types:

• Char: free text / no validation
• Date: a valid date. Even if you have timezone conversions enabled, the entered parameter value will be used as a bind for SQL execution without any conversion (midnight of the date entered).
• DateTime: a valid date including timestamp. If you have timezone conversions enabled, the entered parameter value will be converted to server time before it is used as a bind for SQL execution.
• Number: a valid number
• LOV: If you select this type, the form prompts to pick one of the already existing stored Blitz Report list of values. After selecting the LOV name, you can double click on the LOV Query field to review or modify the SQL query of the LOV (see Tools > LOVs). Note that changes to the LOV affect all parameters referencing it. If you want to modify the LOV for the current parameter only without affecting other parameter validations, switch the parameter type to LOV Custom before modifying the query.
• LOV custom is used to create an ad hoc LOV based on an SQL statement for validation of the current report parameter only. After selecting this type, double click on the LOV Query field to enter the SQL query for validation. The LOV SQL may select an optional ‘id’ column and must include the two columns ‘value’ and ‘description’. If ‘id’ is included, it is used to bind the parameter restriction for report execution, otherwise ‘value’ is used if there is not ‘id’ column selected. If you want to re-use the query for other report parameters, click on the ‘Save as shared LOV’ button to create a shared LOV.
• LOV Oracle allows selecting Oracle standard value sets for parameter validation. Compared with the two Blitz Report specific types, LOV and LOV custom, they have the limitation that you cannot directly edit the LOV query from the Blitz Report form, as the value set would need to be maintained from the Oracle standard form.

LOV Name

Name of a shared LOV or Oracle standard value set.

LOV Query

SQL statement of a list of values. Double click in this field to open the LOV definition window. The LOV query must select the columns ‘value’ and ‘description’, and it may include an optional ‘id’ column. If you include an ‘id’ column, this value is used as the parameter’s :bind value for report execution, otherwise the ‘value’ column is used.

Checkboxes ‘Validate From List’ and ‘Filter Before Display’ control the style of the LOV, see Tools > LOVs.

Matching Value

If the parameter value entered at run-time matches the matching value, then the corresponding SQL text is inserted.

Matching values may contain wildcard characters. If, for the same Anchor, the parameter value entered by the user matches more than one matching value due to use of wildcards, then the SQL text of the best (longest string) match is inserted.

In the example above a value ‘Order’ is provided for the parameter Type, so the following SQL is inserted in the report SQL text: nvl(ooha.transaction_phase_code,’F’)=’F’

............
jtf_rs_salesreps jrs2,
jtf_rs_resource_extns_vl jrrev,
jtf_rs_resource_extns_vl jrrev2,
ra_customer_trx_lines_all rctla,
ra_customer_trx_all rcta
where
haouv.name=:operating_unit and
nvl(ooha.transaction_phase_code,'F')='F' and
ooha.open_flag='Y' and
oola.max_open_flag='Y' and
ooha.cancelled_flag='N' and
oola.cancelled_flag='N' and
1=1 and
............

Default Value

Specifies a default parameter value. If the value starts with the keyword ‘select’, then Blitz Report would execute the SQL to derive the default value dynamically instead of using a fixed value. Example: To get the current date in GL period format, use the following SQL as a default value:

Some functions can be used without selecting from dual making report development easier. Here’s the list of those functions:

abs, add_months, bitand, cast, ceil, chr, coalesce, decode, greatest, initcap, instr, last_day, least, length, lower, lpad, mod, months_between, nvl, nvl2, power, regexp_replace, regexp_substr, replace, round, rtrim, sign, substr, substrb, to_char, to_date, to_number, translate, trim, trunc, upper, userenv

Here is an example of such a function used in a default value

Description

Additional parameter description displayed in the bottom left message area of the Blitz Report run window.

Required

The required flag enforces a parameter value entry by the user, for example to prevent accidental report submission with insufficient parameter restrictions.
Validate_From_List

Locked

Parameter default values stored against a template can be marked as locked. This is mainly used for uploads, for example to fix a template’s Upload Mode so that it can only be used to update existing records. The parameter is then displayed greyed out on the run window and its value is enforced when the report is submitted, including submissions from the Excel add-in and through the API, so that the value cannot be bypassed. A locked value also overrides the user’s own saved parameter defaults.

Advanced required parameters

Using the ‘Required’ button, you can define an advanced definition for required parameters by entering a logical expression based on parameter names.

This allows creating an either-or logic, for example, if at least one, but not all parameters are required.

Here is an example of a logical expression forcing the user to enter a supplier or invoice date range restriction up to two years for specific operating units and responsibilities only:

Parameters are referenced by their names (in the installed base language, usually US), prefixed with a colon and having spaces or other non-word characters replaced with a single underscore. It is is also possible to use functions such as fnd_global.org_name, for example.

Blitz Report evaluates the logical expression at run-time and displays an error message in case the expression is not met. The default message text displayed is stored in FND message XXEN_REPORT_INSUFFICIENT_PARAM and, for expressions enforcing at least one parameter entry, in message XXEN_REPORT_ONE_PARAM_REQUIRED.

To display a specific error message for your logical expression, enter a message text as required.

For reports that have an advanced logical expression set up for their parameter requirements, the required button label shows ‘Advanced’ instead of ‘Required’. One expression can be set up per report.

Dependent Parameters

Similar to Oracle standard’s dependent parameter functionality, you can define parameter dependencies in LOV queries and default values using the syntax:

where parameter_or_lov_name is a reference to either the parameter name in US language or to the LOV name of the parameter, which the query depends on. The match to parameter or LOV name is case insensitive and spaces or other non word characters are replaced with an underscore as in the following examples.

In case you want to use multiple values functionality for a parameter which the query depends on, xxen_util.contains function can help. In the following example AP Supplier LOV will return values depending on multiple values in the Operating Unit parameter.

Dynamic parameter SQL text

Blitz Report provides a possibility to create parameters with dynamic SQL text which depends on a runtime value of a parameter.
In the example below GL Balance (pivot) report contains ‘Show Full Year’ parameter. This parameter produces a list of columns for the resulting SQL statement dynamically depending on its own value and a valuve of the ‘Period’ parameter. Each column represents a month in a year period.

3.5 Assignments

Access to individual Blitz Reports for normal users can be controlled using the following levels:

• Site: all users in the system
• Application: users having a matching application (via their responsibilities)
• Operating Unit: users having access to the assignment operating unit (either via Oracle’s MOAC / security profiles or profile ‘MO: Operating Unit’)
• Request Group: users having a responsibility linked to the assignment request group
• Responsibility: users having the assignment responsibility
• User: inclusion or exclusion by specific user
• Form: make report available from an Oracle standard form through the custom Blitz Report icon
• Function: make an upload available from the Oracle form function that allows updating the same data manually (used for uploads only)

Exclusions take precedence over inclusions. A report included on Site level, but excluded for application ‘Receivables’, for example, would be accessible by all users in the system except from users only having responsibilities linked to the receivables application.

Optionally, an assignment at Application, Request Group, Responsibility, Form or Function level can be limited to group of users through the Assigned Users field.

When one or more users are entered, the report is granted only to those users who also match the assignment level, i.e. the two conditions are combined with AND rather than OR. This is useful, for example, to make an upload available to a specific group of users and only within the correct responsibility or operating unit context. Leaving the Users field empty keeps the assignment available to everyone matching the level, which is the default behavior. Note that this is different from the User level above, which includes or excludes a user across the entire site; the assigned users list instead narrows a single higher-level assignment.

Please note that assignments control report access only for normal users. Users having their access profile set to ‘User Admin’, ‘Developer’ or ‘System’ can access all reports in the system, irrespective of report assignments, and can run them, depending on report type and access profile as described here.

For developers, Blitz Report shows by default all reports, regardless of the current login responsibility. Setting the Blitz Report Filter Reports by Responsibility profile option to ‘Yes’ allows showing only reports from the current login responsibility on the Run window.

Assignments can also be edited via Tools > Assignments

Form assignment

The forms assignment feature allows opening Blitz Reports directly from any Oracle EBS standard form.

To integrate a Blitz Report to a form, first identify the standard form name through the top menu > Help > About Oracle Applications > Current Form > Form Name, as shown in the following example for the GL Enter Journals standard form.

Navigate to the assignment setup of the Blitz Report that you would like to integrate, and select the form name from the list of values. If the Blitz Report should be accessible from a specific navigation block of the standard form only, or if you need to pass parameter values from different item names, depending on the current navigation block, enter the name of that block in the Block Restriction field. If the Blitz Report should be available on all blocks of the assigned form and the passed parameter values are identical, leave the Block Restriction empty.

You can pass default parameter values from the assigned form to the Blitz Report. In this example, the GL Batch and Journal names are passed to the GL Account Analysis report to allow direct drilldown from journals to subledger transactions.

The Blitz Report can then be accessed from the Oracle standard form through the custom icon, with parameters defaulted as defined.

Default assignments

The Blitz Report installation includes seeded reports developed by Enginatics, and their assignments to Oracle standard applications and forms. These default assignments allow business users to start working with the included reports without creating assignments for them individually.

The default assignments are automatically loaded during the first installation of Blitz Report. During upgrades, you can choose which assignment levels are (re)loaded using the corresponding checkboxes on the upgrade screen, each stored in a profile option: Application, Request Group, Form and Function. Application, Request Group and Form are enabled by default, while Function (used for uploads) is optional and disabled by default. You can run report Blitz Report Assignments for a list of all assignments.

If you want to control the seeded assignments in detail, you can disable individual ones via Tools > Assignments. Manually disabled or modified default assignments are preserved during upgrades and are not changed by the automatic load.

If you would like to keep the default assignments, but hide specific Enginatics reports from the users, for example during a phased rollout, you can disable reports either manually, or by a SQL script.

Mass assignments

Assignments can be loaded from Excel with the Blitz Report Assignment Upload.

3.6 Categories

If you have a large number of reports in your system, category assignments will help users to find the reports they require via the category drop-down list on the run window.

To create a new category, navigate to the menu Tools > Categories.

3.7 Multi-language support

Report data

If you have more than one language installed, Blitz Report offers multi-language support via the Oracle EBS translation menu icon for the following data:

• Report name
• Report description
• Parameter name
• Parameter description
• Category
• LOV description
• Column headers

The below screenshot shows an example of setting a parameter translation

User messages

To add translations for user-facing messages, navigate to Application Developer > Application > Messages > query messages starting with XXEN and add translations for a different language as required.

User interface translations

The labels shown on the Blitz Report user interface, e.g. on the run window can be translated via Application Developer > Application > Lookups > Application Object Library, query Lookup XXEN_REPORT_TRANSLATIONS and enter translations for the lookup code descriptions as required.

Blitz Upload is available with Blitz Report which is fully integrated with Oracle E-Business Suite. It enables your IT team to easily create and edit Uploads where they can choose to use an API or directly insert records into an Interface table. Blitz Upload runs as a concurrent process and upon completion generates output file using Blitz Report. The output file automatically downloads and open in Excel.

With Blitz Upload, we created the most efficient and easy to use data upload solution for Oracle EBS. It leverages the reporting capabilities of Blitz Report to efficiently produce outputs while giving you a meaningful interpretation of the upload result.

A Blitz Upload consists of an SQL query defining the column structure of the Excel file for data entry, and three additional tabs that define Excel column validations, API processing and success and error reporting.

To create new Uploads, the Blitz Report Access profile option must be set to ‘System’. With this profile setting, the Blitz Report run window shows an additional ‘Setup’ button, which opens the setup window as shown below. Basic steps to create a new upload are:

1. Enter a report name, an optional description and set the type as Upload.
2. Enter the main extraction SQL. This query is used to export existing data to be updated. For create only scenarios the SQL defines the column names and types for data entry in Excel.
3. Setup Excel column validations. This tab allows setting up column validations through LOVs, defining default values or specifying required and read-only columns.
4. Define the Upload API and mapping from the Excel file columns to the API parameters.
5. Enter Success and Error SQLs for upload result reporting.
6. Optionally define a post procedure and review the mapping between SQL columns post procedure API parameters.
7. Test the upload by clicking the ‘Run’ button.
8. Set up the user access rights.

4.1 Upload types

An upload writes data to the database in one of three ways, depending on how its Upload API is defined. A seeded example upload is installed for each type and can be copied as a starting point for new developments: Blitz Upload Example (API), Blitz Upload Example (API without parameters) and Blitz Upload Example (Interface Table).

4.1.1 API with parameters (row by row)

The upload calls a PL/SQL procedure once for every uploaded row. The Excel columns are mapped to the procedure’s parameters on the Upload Parameters tab, and values of columns whose list of values maps display values to internal ids are passed as the id. The procedure validates the row, writes it through the standard Oracle API, and returns the row’s status and message, which are shown in the result file. Most shipped uploads use this type, for example the AR Customer Upload, which writes customer data through the standard TCA APIs. The main benefit of this type is that the upload framework does the row handling for you, in particular the automatic translation of display values to ids before each call.

4.1.2 API without parameters

If the upload procedure has no parameters, it is called just once per upload run instead of once for every row. The procedure reads the uploaded rows itself from the upload’s data view and updates each row’s status and message back into that view. This type is preferable when you want to loop through the uploaded data in the API package yourself rather than have the framework call you row by row, in particular when rows cannot be processed independently of each other: the ONT Order Upload and the OTL Timecard Upload, for example, process all rows of an order or timecard together. Note that the framework’s automatic value to id translation does not apply here: in the ONT Order Upload’s case, the Oracle standard order API provides such a display value to id translation itself, which is why this type was chosen for it.

4.1.3 Interface table

For this type, the upload object is a table name instead of a PL/SQL procedure: Blitz Report inserts new rows and updates existing rows in that table directly, with the Excel columns mapped to the table’s columns. As no PL/SQL code is required, this is the quickest way to upload spreadsheet data into custom tables. It can also be used to populate Oracle open interface tables, with a post procedure submitting the standard import concurrent program afterwards. The GL Journal Upload, for example, populates the GL interface table (through its API procedure) and then submits the Journal Import concurrent program from its post procedure. An optional delete condition can additionally remove matching existing rows from the table before the uploaded data is inserted, for example to replace previously loaded data.

4.2 Header

Name
Name uniquely identifies uploads. Names should be short and descriptive. Good practice is to prefix names with the appropriate Oracle EBS module short code.
Description
An optional description of maximum 4000 characters may be set up to assist users in understanding and using the upload.
Type
Type should be ‘Upload’.

4.3 SQL requirements

For API based uploads, the SQL query to retrieve the existing records from database, should always contain the columns action_, status_, message_ and modified_columns_ in the first four positions and in the same fashion as below:

null action_,
null status_,
null message_,
null modified_columns_,

For Interface Table based uploads, the SQL query should always contain the columns action_, status, message_, modified_columns_ and row_id_ columns in the first four positions and in the same fashion as below:

null action_,
null status_,
null message_,
null modified_columns_,
"table alias".rowid row_id_,

Most uploads also have an Upload Mode download parameter using the seeded Blitz Upload Mode list of values. It controls whether existing records are downloaded into the template: in Create mode the template is empty for entering new records only, while in Create, Update mode the SQL also downloads the existing records for updating. This is implemented as a WHERE clause condition on the download SQL, which returns no rows unless the selected mode includes updating:

:p_upload_mode like '%'||xxen_upload.action_update

4.4 Upload data view

Blitz Report automatically generates an upload specific database view for every upload, shown in the Data View field on the SQL tab of the setup form. It is a view of the generic xxen_upload_data table, which holds the uploaded Excel data during upload processing, or for longer if the Blitz Upload Data Retention Days profile option is set. The view exposes the uploaded values in the upload’s column structure, together with the framework columns carrying each row’s action, status and message.

The data view is used in several places: API procedures without parameters loop through it to process the uploaded rows, the Error and Success SQLs select from it to build the result file, and during debugging it can be queried directly to inspect the uploaded data of a past run.

4.5 Upload columns

The Upload Columns tab lists all available SQL columns derived from the main report SQL and allows you to define additional validation functionality in Excel, which includes:

• Data type validations.
• List of Values validations through SQL queries.
• Adding Value to Id queries to convert display to id values before passing them to upload API parameters.
• Defaulting column values in the Excel. This includes dynamic defaulting, for example by using an SQL query, dependent on other column values.
• Comments which are shown when hovering over the column name header in the Excel file.
• Specifying required columns.
• Defining read only columns which stops flagging records for update when these columns are updated.
• Enable automatic hiding of columns which are to be used for internal processing of the upload.
• Setting up columns for Group Validation, to reject all records with the same column value in case of validation failures.

Column Name

SQL column names are auto populated in the Column Name.

Type

Type is auto populated based on the data type of the SQL column. Additionally, it can be changed and is used to enforce the data type validation in the excel. This can also be used to define a list of values against the column.

List of values can be used in a similar way as LOVs in Blitz Report

select
msiv.description value,
msiv.description description
from
mtl_system_items_vl msiv,
financials_system_params_all fspa,
hr_all_organization_units_vl haouv
where
haouv.name=:$flex$.operating_unit and
fspa.org_id=haouv.organization_id and
msiv.segment1=:$column$.item and
msiv.organization_id=fspa.inventory_organization_id

Value to Id Query

A Value to Id SQL query can be defined if the LOV query cannot be used for Value to Id conversion for performance reasons. The SQL query is required to have Id and Value columns.

Default Value

An actual value can be provided, or an SQL query can be defined to derive the value which is defaulted against the column in the excel file.

Comments

An actual value can be provided, or an SQL query can be defined to derive the comments to displayed against the column in the excel header record.

Required

The required box can be checked for mandatory columns to ensure records are not marked for processing in the excel file till all the required columns are populated.

Read Only

The read only box can be checked for columns which cannot be updated to make them read-only in the excel file.

Hidden

In case there are internally used columns which need not be shown, the hidden box can be checked to make the columns hidden in the excel file.

Group Validation

Uploaded records are validated on the server using the SQL queries defined in their column LoVs. If a record fails validation, it may be necessary to prevent other related records in the same file from being processed further. In such cases, Group Validation can be enabled on the relevant columns to group records together for validation.

Read Only columns protect downloaded values from being changed in Excel, but they do not prevent entry on new rows, so a column can be immutable on update while still settable on record creation. Hidden columns are not visible in the Excel template and typically carry internal ids of the downloaded records, so the upload procedure can identify the exact record to update even when the visible business key changes.

4.5.1 Descriptive flexfield columns

Uploads can maintain descriptive flexfield attributes alongside the regular columns. The context column and each attribute column are defined as custom lists of values calling xxen_util.dff_attribute_lov, which returns the valid values of the segment for the row’s context, passing the sibling attribute values through :$column$ references so that dependent value sets resolve correctly. Attribute columns of value set segments validate against their value list, while segments without a value set allow free text entry. The download SQL shows the stored values in display form using xxen_util.display_flexfield_value, and the upload procedure translates the display values back to the stored ids by calling xxen_fnd_upload.validate_dff_attributes before passing them to the Oracle API. A dynamic column translation rule renames the generic attribute column headers to the configured segment prompts. The AR Customer Upload is a complete example of this pattern.

4.5.2 Attachment columns

Uploads can also create Oracle attachments on the records they process. The download SQL exposes the reserved attachment columns attachment_category_, attachment_title_, attachment_description_, attachment_type_, attachment_content_ and attachment_file_id_ for data entry, and the Success SQL calls xxen_upload.check_and_add_attachment, which creates the attachment on the target record if it does not exist yet, for example in the GL Journal Upload:

xxen_upload.check_and_add_attachment(
p_function_name=>'GLXJEENT',
p_function_type=>'O',
p_category_name=>xu.attachment_category_,
p_entity_name=>'GL_JE_HEADERS',
p_data_type=>xu.attachment_type_,
p_title=>xu.attachment_title_,
p_description=>xu.attachment_description_,
p_text=>xu.attachment_content_,
p_file_id=>xu.attachment_file_id_,
p_pk1_value=>gjb.je_batch_id,
p_pk2_value=>gjh.je_header_id
) attachment_content_

The function name and entity name determine on which Oracle screen the created attachment is visible. The user side of entering attachment data in the template is described in the User Guide chapter Attachments.

4.5.3 Template column defaults

The Upload Columns tab defines the behaviour of an upload for all of its users. Read-only columns, frozen panes and default values can in addition be defined per template, on the columns of a layout template. This allows the same upload to be offered in several controlled variants without changing the upload definition itself, for example an update-only variant.

• Read-Only – protects the column in the workbook. Together with a single Default Value the column is fixed to that value; together with a semicolon separated list the column’s list of values is reduced to the allowed values.
• Freeze – marks the column at which the panes of the generated Excel file are frozen.
• Default Value – the value entered into the column of the generated workbook. A single value pre-fills the column. A semicolon separated list, for example ‘Create;Update’, defines a set of allowed values of which the first one is used as the default. A value starting with ‘select’ is executed as a SQL statement to derive the default dynamically.

4.6 Upload API

Type

Type determines if the upload will use an API or an Interface table.

Create Only

If the upload does not support updating existing records, then this checkbox needs to be ticked.

Name

Name will list down the procedures or interface tables available in the database based on the Type selected.

action_ in varchar2
status_ out varchar2
message_ out varchar2

Refer this PL SQL package for usage example: XXEN_UPLOAD_EXAMPLE_API.zip

4.6.1 Upload parameters

Parameters are auto populated on selection of the API or the Interface table.

Parameter Name

Parameter Names are auto populated on selection of the API, in case of Interface table the table column names are populated.

In/Out

In/Out is auto populated on selection of API, indicates if the API parameter is of type ‘in’, ’out’ or ‘in/out’.

Type

Type is auto populated with the data type of the API parameter or Interface table column.

Column Name

Report SQL columns are automatically mapped against the API parameters or the Interface table columns and populated in the Column Name on selection of the API or Interface table.

4.6.2 Post procedure

Optionally define a post procedure in case a post process must be executed after the primary upload processing, example: submit standard import interface concurrent request after completion of data upload in the interface table.

Choose between the available PL SQL procedures, and on selection it auto populates the parameters like the upload parameters.

4.6.3 Excel validation

Excel Validation can be used to execute validations for the records directly on the server from the excel when the file is saved. The call to the validation PL SQL function can be entered with the function parameters mapped to the SQL columns.

4.7 Upload results

The Upload Results SQL need to be defined to be able to display the success and error records after upload processing.

The Error and Success SQLs are automatically defaulted and contain placeholders for defining the join conditions. The default Error SQL usually works as is, whereas the Success SQL needs to be reviewed and amended with the correct join conditions by unchecking the Default checkbox: it selects the successfully processed records back from the Oracle tables, so it must join the uploaded rows to these tables by their business key columns.

Data View: This is an auto-generated database view which displays the data uploaded by the user and can be used in the Result SQLs to select the uploaded data. It has the same column structure as the report SQL and also shows the status and message returned by the API for the uploaded records.

4.8 Debugging uploads

By default, the uploaded Excel data is only kept in the xxen_upload_data table while the upload is being processed. To debug an upload, set the Blitz Upload Data Retention Days profile option to a value such as 10 days, so the uploaded data of past runs is retained for inspection.

The upload and result report log files contain an initialization statement, which can be executed in a SQL tool to reproduce the run’s session context:

Init text: begin fnd_global.apps_initialize(1318,50553,101); mo_global.init('S'); xxen_upload.initialize_run(493,608); end;

After executing it, selecting from the upload’s data view returns the uploaded rows of that run, including each row’s status and message. The log file also shows the actual API call performed for each row, and with the Blitz Report Debug profile option set to Yes it includes the full generated processing code.

The Blitz Upload History report lists the upload runs performed on the system, and the Blitz Upload Data report shows the uploaded data of past runs with each row’s status and message.

5 Security

This chapter describes how access to Blitz Report and to the reported data is controlled: the assignments that decide which reports a user sees, the assignment options for uploads, the access levels that decide what a user can do, the data security that decides what data a report returns, and the licensing.

5.1 Report assignments

Assignments decide which reports a user can see and run. A report can be assigned at site, application, request group, responsibility, user, form or function level, with include and exclude rules. Request group assignments reach every responsibility using that request group, and application assignments reach every responsibility owned by the application, which makes them the most common levels for shipped content. Form and function assignments show a report when the user works in the corresponding Oracle form.

5.2 Upload security

Uploads can be assigned at the same levels as reports, for example to specific users only, or to specific users in certain responsibilities only. Because uploads modify data, the default upload assignments provided by Enginatics, which customers can opt to include during upgrades, are restricted to function level: each upload is assigned only to form functions that allow updating the same data manually, so a user who only has query access to an Oracle form does not gain the ability to change that form’s data through an upload.

Two further controls apply per template and are mainly used for uploads. The Restrict flag on a template’s sharing limits the shared user or responsibility to the restricted templates of that upload, and template parameter default values can be Locked so that they cannot be changed on the run window or overridden at submission, for example to fix an upload’s Upload Mode so that it can only be used to update existing records. Both are enforced for business users only: the access levels ‘Developer’ and ‘System’ are exempt, so a developer always sees all templates and can always change parameter values.

5.3 Template security

A template restriction is a control the report developer places on the template’s users, and it is deliberately separate from template ownership. An owner with ‘User’ or ‘User Admin’ access may change what the template contains, but never the restrictions placed on it: only the access levels ‘Developer’ and ‘System’ can set or remove one. Note that the Blitz Report Template Access profile option set to ‘Super User’ grants full access to other users’ templates, but it does not grant the right to change a restriction.

Five template properties are treated as restrictions:

Two further points follow from this. Clearing the template parameter defaults from the Tools menu never clears locked ones, and the menu entry is only offered while there is something a user is allowed to clear. And a template that carries any restriction can only be deleted by a developer, because deleting it would remove the restriction with it.

Scenario 1: the developer keeps ownership

The developer builds the template, restricts the sharing to the intended users or responsibilities, and keeps ownership. The users are not the owner, so they can only run the template exactly as it was built: the columns the developer selected, with the values the developer permitted. Use this where the report or upload has to be identical for everyone, for example an upload that may only ever update one field of existing records.

Scenario 2: the users maintain the template themselves

The developer builds the template, locks the parameter defaults and the read only column values, and then sets the Owner to the user or to their responsibility. The users can now add and remove columns and look after the template themselves, while the locked parameter defaults, the restricted sharing entries, the read only flags and the allowed value lists stay developer territory: they are deactivated in the template window for anyone below ‘Developer’ access. Use this where a team needs to adapt the layout of an upload without being able to widen what it may write.

5.4 Access levels and user profiles

Blitz Report provides the following levels of security:

• The Blitz Report Access profile option controls which users can consume a license, and which users have access to the report setup window.
• Assignments control which business users have access to which reports and uploads.
• Access to data within reports is secured through restricted LOVs (recommended) or by using Oracle’s secured views and synonyms.
• Access to layout templates is controlled by the profile option Blitz Report Template Access, for example to designate specific users as ‘Super User’.
• Templates can be restricted to specific users or responsibilities, and their parameter default values can be locked, so that a report or upload is only run with a controlled layout and with controlled parameter values. See Upload security.
• Access to sensitive data, for example to prevent developers from accessing HR data, can be restricted through additional VPD policies.

The Blitz Report Access profile option is used to control access to Blitz Report functionality, and to distinguish business users with limited access from developers with full access to create new reports and update existing ones.

Developers typically have their access profile option set ‘Developer’ on user level, which allows them to access all reports and the setup window from all login responsibilities. The access to modify reports also depends on the type of report. For reports that require additional protection (such as critical outbound interfaces), you can set the report type to ‘Protected’ to allow only developers with the highest access profile setting ‘System’ to modify them.

The upload functionality has additional security and the Upload button is inactive by default even for developers. To upload data, users or developers require assignments created by someone having the Blitz Report Access profile option set to the highest level ‘System’. Only users users with System access can modify and or run any of the uploads, or create assignments for them. Unlike reports, the Enginatics default assignments for uploads are not loaded automatically: customers can opt to include them during upgrades, or assign the uploads themselves as required.

Access to the different report types ‘Standard’, ‘Protected’ and ‘System’, the upload and other functionality is available according to the following table.

1. Users can only see and run assigned reports and uploads.
Users with access profile set to ‘User Admin’, ‘Developer’ or ‘System’ can access all reports in the system, irrespective of report assignments, and can run them, depending on report type and access profile as shown above. Uploads can only be done if there are assignments for them, or by users with an access profile of ‘System’.
2. Depending on the setup of profile option Blitz Report Template Access, users can create and modify only their own templates or modify other owner’s templates.

5.5 Data access security

For increased flexibility and maintainability, we recommend using “_all” tables in report SQL queries, for example ap_invoices_all, instead of Oracle’s VPD secured synonyms, such as ap_invoices. Security is then applied by adding a required Operating Unit parameter in Blitz Report with an LOV that contains the allowed Operating Units only. This approach allows greater flexibility, e.g. to enable certain users, such as in shared service centers, to see all data in the system, or to test SQL queries through database access tools, without having the application user session context initialized.

Example:

• Report that selects AP invoice information from the unrestricted base table ap_invoices_all.

• “Operating Unit” is introduced as a required parameter. Records of the query are filtered based on the parameter value.

• When submitting a report, the “Operating Unit” LOV is limited to organizations available in the current login user responsibility only.

Where a report should instead follow the standard Oracle EBS access restrictions automatically, the corresponding security predicates can be added to the report SQL directly. For operating unit secured data, the operating units accessible to the user’s responsibility are available in mo_glob_org_access_tmp:

aia.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat)

With this condition in place, an optional Operating Unit parameter can narrow the output further, while leaving it blank shows all operating units the user’s responsibility has access to. For inventory organization secured data, the organizations accessible to the current responsibility are available in org_access_view:

mp.organization_id in
(select oav.organization_id from org_access_view oav
where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)

5.6 Securing sensitive information with Oracle Virtual Private Database

With Blitz Report, you can use Oracle Virtual Private Database (VPD) to control access to sensitive data. VPD policies are set up on database objects to automatically add restrictions before SQL execution, thus preventing visibility of sensitive information. This can be used for example to prevent users and developers from querying sensitive HR data, such as payroll information when running queries through Blitz Report.

Database policies can be set up either on row or on column level and Blitz Report includes the following objects to maintain these:

• Lookup XXEN_REPORT_VPD_POLICY_TABLES to define the tables or columns to be secured
• Concurrent program Blitz Report Update VPD Policies to create or update VPD policies for the tables and columns defined in the lookup
• Concurrent program Blitz Report Remove VPD Policies to completely remove all Blitz Report VPD policies
• Database package XXEN_VPD containing the policy function code
• Profile option Blitz Report VPD Policy Rule to control data access

Perform following steps to secure your data. Note: Using this method secures data access through the Blitz Report concurrent program only, not through other access methods such as direct queries through database access tools or Oracle standard EBS processes.

1 Set up tables or column names in lookup XXEN_REPORT_VPD_POLICY_TABLES

Application Developer > Application > Lookups > Application Object Library: Query lookup type XXEN_REPORT_VPD_POLICY_TABLES and enter one lookup value for each table or table column that need to be secured. Choose a unique lookup code and enter the owner, table and optional column name as the lookup meaning.

2 Run concurrent program ‘Blitz Report Update VPD Policies’

System Administrator > Concurrent > Requests: Run concrrent program ‘Blitz Report Update VPD Policies’. This program first removes all possibly existing Blitz Report VPD policies, creates the policy function package XXEN_VPD and then creates database policies for all tables and columns referenced in lookup XXEN_REPORT_VPD_POLICY_TABLES.

3 Optionally set profile option Blitz Report VPD Policy Rule

System Administrator > Profile > System: Set profile option Blitz Report VPD Policy Rule to ‘Full access’ for responsibilities or users who should have access to run Blitz reports on secured data.

The Virtual Private Database policies are applied and removed with the concurrent programs ‘Blitz Report Update VPD Policies’ and ‘Blitz Report Remove VPD Policies’, see the Administration reference.

5.7 License Key

Blitz Report Setup > Tools > License Key

Enter the company name and license key information.

Double click on the active users count to open a detailed list of active Blitz Report users.

5.8 User license assignment

The access to Blitz Report functionality and licenses is controlled by the Blitz Report Access profile option. With this profile option, licenses can be assigned automatically, or to individual responsibilities or users only. The different access roles and license assignment options are explained in this video.

Automatic license assignment

The recommended way to maintain Blitz Report user licenses is to have them assigned automatically, whenever users run a report. Any user with access to Blitz Report can consume a license by running a report that is assigned to them. This option requires the following setup:

• Set up the Blitz Report menu entry function in all menus of responsibilities that require to run reports or upload data. This assignment can be done manually for individual menus or automatically for all responsibilities by running the Update Menu Entries concurrent program.
• Keep the Blitz Report Access profile option setting to ‘User’ on site level, which is set by default during the first installation.
• Assign reports to users via the Assignments tab inside each report or via Blitz Report Setup > Tools > Assignments.

Manual user license assignment

If you have a large number of EBS users, but a limited number of Blitz Report licenses, you may want to assign licenses to individual users only. In this case, remove the Blitz Report Access profile option value ‘User’ from site level, and set it up for individual users as required.

You can use the Blitz Report Access Upload to maintain the profile option values, for example if you have a larger number of users to maintain, or if you would like to delegate this task to a user, who does not have access to the System Administrator responsibility or System Profile Values form.

If you assign Blitz Report access to a limited number of users, you would typically want to hide the Blitz Report menu entry for other users, who do not have access to the tool. If you remove the Blitz Report Access profile option value from site level, the Blitz Report menu entry is automatically hidden from the Forms navigation menu and only visible for the Blitz Report users. This automated menu entry hiding is done through the CUSTOM.pll, and it can only be done in Forms, but not on the OAF pages. In case you would like to completely hide the Blitz Report menu entry also from the OAF pages, you can can run the Blitz Report Update Menu Entries concurrent program without a menu entry prompt. If you choose this setup, Blitz Report can only be run from the Excel icon in the top menu of the Oracle Forms navigation.

Do not create custom Blitz Report responsibilities

Some companies historically used custom read-only responsibilities for reporting, for example to run Discoverer reports. Blitz Report is designed to work in the existing users’ responsibilities as running Blitz Report in separate custom responsibilities would have the following drawbacks:

1. Additional navigation steps to switch responsibility whenever users need to run reports.
2. More work and complexity to create and maintain additional responsibilities and access.
3. Reports cannot make use of the existing responsibilities’ session security, for example, to restrict access to specific organizations or ledgers. Some reports require a module specific session context, such at the AP Trial Balance.
4. Users cannot use the drill down functionality from Oracle standard forms to Blitz Report.
5. Uploads cannot be linked directly to Oracle standard forms.

6 Tools menu

6.1 LOVs

Name

Unique name for shared LOVs.

Description

Description for shared LOVs. This description is displayed in the bottom left message area of the run window, if parameter description is left blank.

Validate From List

If checked, the parameter validation enforces selection of one record from the LOV and does not allow use of wildcards. If unchecked, the parameter value is not validated against the LOV and use of wildcards is allowed.

Filter Before Display

The Filter Before Display setting is used to avoid performance issues for large LOVs. If unchecked, which is the more user-friendly default, Blitz Report queries all possible parameter values from the LOV in the background when selecting a report on the run window. As this can be slow for large LOVs, checking the ‘Filter Before Display’ setting prompts the user to enter a (partial) value before LOV display, and the form then queries a restricted dataset instead of all records.

Used By

The ‘Used By’ button shows all reports and parameters referencing the LOV.

LOV Query

SQL query for LOVs, selecting the two mandatory columns ‘value’ and ‘description’, and it may include an optional ‘id’ column at the beginning of the select clause.

Version

Double click on a LOV version number to review the change history and previous LOV SQLs. Note that a LOV report version number is added and stored automatically at each update of a LOV’s SQL. Other LOV modifications such as LOV name, description are not stored in the version history.

6.2 Assignments

The Assignment function in the Tools menu allows to mass-assign different reports to one assignment level, or to review existing assignments through the assignment value LOV.

Defines access to reports for users on a particular assignment level.

6.3 Categories

Categories can be defined to help users find reports, or to migrate specific reports between environments though the export and import options.

6.4 Copy Report

Creates a new copy of an existing report. This functionality should be used if user want to do any changes to the existing Blitz report.

Seeded reports should not be modified as all updates will be removed as soon as a new version of Blitz Report is installed.

6.5 Copy LOV

Creates a new copy of an existing LOV. This functionality should be used if user want to do any changes to the existing LOV.

Seeded LOVs should not be modified as all updates will be removed as soon as a new version of Blitz Report is installed.

6.6 Export

The Blitz Report export functionality allows to generate XML files or SQL scripts for automated load of report definitions, LOVs, categories and other Blitz Report related setup for migration purposes.

The following items can be exported:

• Report
• Reports from Category
• Reports except Category
• Reports from Application
• Reports matching a search pattern with the use of wildcards
• LOVs
• Categories
• Column Translations
• Dynamic Column Translation Rules
• Assignments

When choosing one of the Report export options, you can use checkboxes to decide which related object information you would like to include in the exported XML file:

• LOVs: All list of value definitions used by parameters of the exported reports. Use this option if you have new or changed list of value definitions related to the parameters of the exported reports (parameter type LOV, not LOV Custom or LOV Oracle), and you would like to include these in the export.
• Categories: All category definitions of the exported reports. Use this option if you have reports assigned to a category and would like to create that category automatically in the destination environment.
• Assignments: Includes all report assignments in the export.
• Templates: Includes all templates in the export, together with their column default values, read only and freeze settings, their locked parameter default values and their restrictions.
• Columns: Includes optional multi language column translation and number format settings. This option should only be used if you really have translations or column specific number formats to migrate. If you have a large number of reports, this option could slow down the export significantly, as the database needs to parse all exported report SQLs to identify the included column names.

Examples:

To generate an XML file for migration of a single report, choose ‘Report’ and select the report name from the list of values.
To generate an XML file for migration of all reports starting with a specific text, for example FND%, choose ‘Report by Search pattern’ and enter that text in the form.

Blitz Report Library

To download XML files from the Blitz Report library, find the desired report and click on the XML icon in the ‘Download’ column.

To export all reports from a specific category, search for this category

and once the category or several categories are selected, an option to download all reports will appear

Notes:

Export API

To export Blitz Reports programmatically, e.g. for scripted report migrations, you can use the following function in package XXEN_API:

To export a single report, you can use the following function in package XXEN_API:

You can use the dbms_xslprocessor.clob2file procedure to write Blitz Report XML files to the database filesystem, as done in the following example, exporting all reports containing the word “test” in their name into one single XML file. This can be useful to automate migration tasks using shell scripts. Note that ‘OUT_FILE_LOC’ points to a directory name defined in dba_directories.

To export different reports to individual XML files, e.g. in order to store them in a change management system. You can use a similar script calling function xxen_api.export_report_ instead:

Import API

To import Blitz Report XML files programmatically, e.g. for scripted non-interactive deployments, you can use the following import_xml command from Linux:

Or to import an XML as a clob through PLSQL, you can use the following function in package XXEN_API:

The function returns null for success or an error message in case of failure. Note that it does not include a commit, which needs to be executed from the calling code.

6.7 Import

The Import menu option loads Blitz Report definitions from XML files, either generated via export or downloaded from the Blitz Report library, and imports reports developed in other technologies such as BI Publisher or Oracle Discoverer. It is described in detail in chapter 7 Importing and migrating reports.

6.8 Upload Large SQL

Blitz Report Setup > Tools > Upload Large SQL

To upload report SQLs larger than Oracle’s Forms limit of 32767 characters, select the ‘Upload Large SQL’ menu entry to open a browser window and select a SQL file for upload. If the SQL file contains non ANSI characters, it must be uploaded in UTF-8 encoding.

A notification window indicates that an upload is in progress and allows to cancel.

After file upload, the notification window closes and the uploaded SQL is shown on the setup screen. Note that SQLs larger than 32767 characters are greyed out and can be modified via the SQL upload functionality only.

While the form displays the first 32767 characters only, a double click on the SQL downloads the full SQL text as a file.

6.9 Column Translations

Column Translations provide multi-language support for SQL column headers and report parameters and allows specifying number formats for numeric columns/parameters. The number of existing translations is shown in column ‘Count’. If you have a report selected before navigating to Tools > Column Translations, the columns/parameters are shown for that report only. You can query all existing column translations via Ctrl+F11.

6.10 Dynamic Column Translations

Dynamic column translation rules allow dynamic translation of parameters and report header column names based on individual rule SQLs.

The output of a rule SQL should contain two columns, the first for the column or parameter name, and the second for the translation.

This can for example be used to show GL segment names based on a selected ledger parameter. Translations are applicable for both, parameters and header columns in the report.

6.11 Resequence parameters

Blitz Report Setup > Tools > Resequence parameters

Assigns new parameter sequence numbers automatically. Sometimes you can not insert a new parameter because there is no spare sequence number.

Then you can resequence parameters so they have room between sequence numbers again.

6.12 License Key

The License Key menu option opens the license key window to enter the company name and license key information and to review the number of active users. It is described in detail in section 5.7 License Key.

6.13 Mass Change

Blitz Report Setup > Tools > Mass Change

The mass change functionality allows to update SQL text or List of Values for all reports.

• Use the check boxes to decide which data you would like to update.
• For report SQL and LOV query updates, you can add an optional change comment.
• Use the Preview button to see a list of changed objects, before applying the changes.

6.14 Sensitivity Labels

Organizations using Microsoft Purview Information Protection can require every Office document to carry a sensitivity label. Excel shows an ADD SENSITIVITY LABEL banner on any file which does not have one, and users have to pick a label before they can edit it, on every report output they open.

Blitz Report can write your own label into the Excel files it generates, so that the prompt does not appear.

Registering a label

Sensitivity labels are defined in your Microsoft 365 tenant and are not visible to Oracle EBS, so Blitz Report reads a label from a file which already carries one:

• Open Tools > Sensitivity Labels
• Press Scan label from file and upload any Excel file which was labelled in your tenant. It does not have to be a Blitz Report output, any labelled workbook will do.
• The label is registered with its name, its identifier and your tenant identifier
• Tick Enabled to make the label available

A file carries exactly one label, so scan one file for each label you want to offer.

Label names

Microsoft stores only the internal label name in the file, which is not necessarily the name your users see in the Excel label picker. Where the two differ, enter the name they know in the Description field and it is shown in place of the internal name.

Applying a label

Set the Blitz Report Sensitivity Label profile option to label all generated files. Like any profile option it can be set at site, application, responsibility or user level.

An individual report can override it with the Sensitivity Label field on its definition, for example to mark the output of a payroll report more strictly than everything else.

Automatic discovery

Labelled workbooks submitted through Blitz Upload register their label automatically as well. Automatically discovered labels are added disabled, so nothing is applied until you enable them in this window.

Limitations

• Labels which apply encryption are not supported. Blitz Report writes the label information only, it cannot encrypt the generated file, and such a file cannot be scanned either.
• Content marking, the header, footer or watermark text which some labels add, is not written. Only the label itself is stored in the file.
• CSV and TSV output cannot carry a label, as these formats have nowhere to store it.

6.15 Upgrade

Blitz Report can be upgraded directly from the setup window via Tools > Upgrade. This menu is available to users with the Blitz Report Access profile option set to ‘System’.

Two options are available:

• Upgrade to latest version – starts the ‘Blitz Report Upgrade’ concurrent program to automatically download and install the latest available Blitz Report version. Ensure the Email field contains a valid address before starting, as it is required to authorize the download from www.enginatics.com.
• Upload specific version – allows uploading a previously downloaded installation file. Useful for environments without internet access or for upgrading to a specific previously tested version.

The checkboxes select which default report and upload assignments are loaded during the upgrade, at Application, Request Group, Form and Function (uploads) level. Each checkbox corresponds to a profile option, so the selection is remembered for the next upgrade. Function level is disabled by default, since uploads modify data, and manually disabled or modified assignments are preserved and not changed during upgrades.

Both options start the upgrade concurrent program, which automatically disconnects active Blitz Report sessions and sets the Blitz Report Maintenance Mode profile option to prevent access during the upgrade. Close the Blitz Report form immediately after submitting the upgrade, otherwise the disconnection will terminate your Forms session.

For detailed instructions including manual upgrade via terminal session and additional guidelines for multi-node environments, see the Upgrade section of the Installation Guide.

7 Importing and migrating reports

Blitz Report imports existing report definitions from a wide range of sources, so that reporting content built in other tools does not need to be redeveloped by hand. Report definitions themselves are moved between instances as XML files, and ready-made content can be installed from the Blitz Report library.

For the Discoverer migration, the end-to-end process is described in the blog posts Discoverer replacement with Blitz Report and Oracle Discoverer replacement – importing worksheets into Blitz Report.

The Blitz Report ‘Import’ menu option allows import of reports from XML files generated via export or downloaded from the Blitz Report library, or reports developed in other technologies such as BI Publisher, Oracle Discoverer, Enterprise Command Center or other third party tools.

During import, reports are assigned to categories automatically, depending on the originating concurrent program’s application module. This automated category assignment is defined by lookup XXEN_REPORT_APPLICATIONS.

Reports from application modules listed in the lookup code column are assigned to the category listed in the description column. There are two special lookup codes BI_PUBLISHER and CONCURRENT_PROGRAM which can be used to assign all imported reports of a particular type to a specific category.

7.1 XML Upload

Blitz Report Setup > Tools > Import > XML Upload

With XML upload, you can migrate reports exported from other EBS environments or downloaded from the Blitz Report library.
Please see section 2.6 Export for more details on how to get report XML files from other EBS instances or the Blitz Report library.
The screenshot below shows the XML upload option in the Import window.

Once this option is chosen, a separate browser window is opened asking to provide the path to the XML file on the local file system.

At the same time a new form window is opened notifying that upload is in progress and providing an option to cancel the operation.

After successful upload, the Blitz Report setup window is opened and the uploaded reports are shown.

Note: To avoid incompatabilities due to possible file format changes between different Blitz Report versions, please ensure that source and destination environment have the same or latest Bitz Report version installed.

7.2 BI Publisher

Blitz Report imports BI Publisher reports of java executable XDODTEXE (XML Publisher Data Template Executable) by importing the report SQL from the corresponding XML data source. If the originating data source contains more than one SQL query, only the largest one is imported and there would be additional manual work require combine the data from the different SQLs into one larger single query.

Reports imported from BI Publisher show the original source code in field ‘BIP Code’ and Blitz Report uses this value to identify and execute beforereport triggers from the original XML data source. Executing such triggers before running the report SQL is required for reports, which rely on PLSQL code to populate data into global temporary tables for example.

You can double click on the BIP Code to download and review the XML data source.

Import API

BI Publisher reports can be migrated programmatically into Blitz Report through the following PLSQL procedure:

You can use the example script mass_import_bi_publisher_reports.sql to import all custom BI Publisher reports with a datasource starting with XX%.

7.3 Concurrent Program

This option allows import of parameter definitions, LOVs and request group assignments of any concurrent program, and can be used to help migrating other reporting technologies, such as Oracle Reports .rdf files or custom report programs into Blitz Report.

Note that the report SQL can usually not be imported automatically for such technologies, and would need to be transferred manually. If you run the concurrent request to import, e.g. an Oracle standard report, directly before the import however, the Blitz Report code attempts to retrieve the concurrent program’s SQL from the database memory (SGA). While this might not work 100% reliably as it only retrieves the program’s largest SQL, it facilitates the migration process to Blitz Report. You can also use report DBA SGA SQL Performance Summary restricted by module type ‘Concurrent Request’ and the module name of the concurrent program to identify the SQL statements executed by the program to import.

7.4 Discoverer Worksheet

Select ‘Discoverer Worksheet’ to import worksheets available from the selected end user layer. By default, the LOV shows worksheets that ran within the History Days timeframe only. To select all worksheets, including ones that were not executed in the past, clear our the value from the History Days field.

During import, Blitz Report derives parameter types from the Discoverer EUL items and creates LOVs automatically for the item classes used by the workbook parameters.

Prior to importing Discoverer worksheets, you may want to analyze which reports are frequently used and by whom. This will allow you to do a cleanup of the Discoverer reports as part of the import.

To analyze the worksheet usage history and content of your current End User Layer, use the Discoverer analysis reports from the Blitz Report library (starting with DIS %) .

Import API

Mass import of Discoverer Worksheet SQLs can be done via a SQL script using the following PLSQL procedure:

The example script mass_import_discoverer_worksheets.sql imports all Discoverer Worksheet SQLs which have been accessed within the past 180 days into Blitz Report.

In case you need to re-run the import, for example with a different setting for custom view expansion, you can use script mass_delete_discoverer_reports.sql to remove all imported Discoverer reports before re-importing them.

7.5 Discoverer Folders

The ‘Discoverer Folders’ import option allows consolidation of different workbooks during migration to Blitz Report by importing distinct folder or view object combinations only. If you have many different workbooks accessing the same views or folders, this import option can significantly decrease the number of Blitz Reports to migrate and simplify subsequent report maintenance.

The LOV of reports to import shows one record for each distinct folder combination, and the number of different workbooks and sheets using these folders. During import, the different workbooks and their selected columns are converted to Blitz Report templates.

• EUL: Specify the Discoverer End User Layer to import reports from.
• History Days: Number of days in the past to consider worksheet executions for the import. The list of folder combinations is based on the worksheet execution history and only folders used within the given number of days are shown. Leave blank to show all folders from the whole history.
• Expand Views: When set to ‘All’, folders based on views have their view definition SQL statements imported into Blitz Report as a subquery, instead of selecting from the view. This simplifies maintenance, as the SQL can be modified directly in Blitz Report, instead of having to recompile a view in the database. When set to ‘Custom’, not all views, but only the SQLs of views either starting with XX% or with any of the custom application short names, such as ADS_%, are expanded during import.
• Include Columns: Allows to create imported SQLs with either ‘All’ folder columns, such as aia.*, or only the ‘Active’ columns that were previously used by workbooks.

Import API

Mass import of Discoverer folders can be done via a SQL script using the following PLSQL procedure:

The example script mass_import_discoverer_folders.sql imports all Discoverer folder combinations of Worksheets accessed within the past 180 days into Blitz Report.

In case you need to re-run the import (e.g. in case of errors), you can use script mass_delete_discoverer_reports.sql to remove all imported Discoverer reports again.

7.6 Discoverer import prerequisites

Enable statistics collection

Blitz Report’s Discoverer import uses information from table EUL5_QPP_STATS, which is populated by Discoverer Desktop or Discoverer plus with a history of worksheet query execution statistics. The information is written to the table upon exit from above applications. Use File>Exit menu, do not just close browser or application.

When exiting the Discoverer plus choose ‘No’ when prompted to save the changes to a workbook.

Discoverer server configuration file

For Discoverer plus and viewer users, following actions need to be performed which are also outlined in the following Oracle document, section “9.4 How to set default user preferences for all users” and Doc ID 387367.Below instructions will be provided with examples from our own demo Discoverer server installation.
Open pref.txt file under $ORACLE_INSTANCE/config/PreferenceServer/$DISCO_COMP_NAME

Update or add required parameters in the respective sections and save the file:

[Application]

SaveLastUsedParamValue = 1

[Database]

QPPEnable = 1
QPPCreateNewStats = 1

Run script applypreferences.sh or applypreferences.bat under $ORACLE_INSTANCE/Discoverer/$DISCO_COMP_NAME/util

UNIX:

/u01/disco/middleware/asinst_1/Discoverer/Discoverer_asinst_1/util/applypreferences.sh

Windows:

$ORACLE_INSTANCEDiscovererDiscoverer_asinst_1utilapplypreferences.bat

Restart the Discoverer_[instance_name]  ias-component:

Discoverer Desktop Windows registry setting

To ensure that the statistics is written for the Discoverer Desktop, a Windows registry setting for Discoverer parameter QPPEnable and QPPCreateNewStats needs to be added or adjusted, as described in Oracle’s Discoverer Administrator Guide and Doc ID 1340849. Open the Windows registry editor.

Go to the following path (Replace Discoverer 11 with your version):

Computer > HKEY_CURRENT_USER > Software > Oracle > Discoverer 11 > Database

If the setting for key QPPCreateNewStats already exists, ensure that its value is set to ‘1’.

If the key does not exist, create it by using Edit > New > DWORD (32-bit) Value:

Create the QPPEnable key in the same way.

Enable Collect Query Statistics for users

Login to Discoverer Admin and enable ‘Collect Query Statistics’ for all Discoverer users that you want to collect statistics for.

Register eul_trigger$post_save_document function

The Discoverer Worksheet import option of Blitz Report imports Discoverer worksheet SQLs from EBS table AMS_DISCOVERER_SQL. This table is updated with the latest SQL, each time a worksheet is saved in Discoverer Desktop or Discoverer plus. The update is done by a discoverer trigger called eul_trigger$post_save_document, which calls database PLSQL function AMS_DISCOVERER_PVT.EUL_TRIGGER$POST_SAVE_DOCUMENT to perform the update.

If you don’t see any worksheet SQLs in Blitz Report’s Discoverer Worksheet import option, or if you only see very old ones having the execution count and last executed columns blank, or if the EUL you are looking for is not available, then above trigger does not exist or does not work.

To correct the trigger setup, you need to re-register the PL/SQL function. Login to Discoverer Administrator, connecting to the database end user layer, and navigate to Tools > Register PL/SQL Functions > Import, then show all functions from owner APPS. Note that the function LOV for the APPS user is quite large and it took about 4 minutes to bring it up on our PCs. Then select function APPS.AMS_DISCOVERER_PVT.EUL_TRIGGER$POST_SAVE_DOCUMENT.

Note 1: You can start typing the first characters of the function name ‘apps.ams_dis’ to navigate quicker than scrolling through the complete list.
Note 2: If the function doesn’t appear in the list please provide the following grant to the EUL user. Example:

grant execute any procedure to eul_us;

After Import, modify the display name from upper to lowercase (see Oracle’s Discoverer Administrator Guide).

After import, click the validate button to ensure that the function is working. Ensure that the discoverer EUL user has execution and update/insert permissions on the AMS package and table.

With this PL/SQL function in place, every workbook update triggers an update of the included worksheet SQLs to table AMS_DISCOVERER_SQL and makes them available for import into Blitz Report. To automatically trigger recently used workbooks (and to avoid manually saving them to trigger the update), you can enable parameter ‘SaveLastUsedParamValue’, which saves a workbook each time it is used in Discoverer plus or viewer (see ‘Discoverer server configuration file’ further down).

Index creation

For better import performance, we recommend creation of following index on eul5_documents.doc_name.

7.7 Excel4apps Reports Wand

Blitz Report imports custom Excel4apps Reports Wand reports through the import menu option.

Import API

To mass import Exel4apps Reports Wand concurrent programs into Blitz Report via a SQL script, use the following PLSQL procedure:

as shown in the example script mass_import_excel4apps_reports.sql.

7.8 Enterprise Command Center

Blitz Report imports Oracle’s EnterpriseCommand Center dataset queries, allowing users to access ECC data of unlimited size and real-time in Excel. You can import data sets through the menu option

or you can use the following API for mass import.

Import API

The example script mass_import_ecc_reports.sql imports all ECC queries automatically into Blitz Report.

If you upgrade the command centers to a new version, you can use the following script to delete all imported ECC blitz reports, before re-importing Oracle’s latest dataset queries again: mass_delete_ecc_reports.sql.

7.9 Polaris Reporting Workbench

Blitz Report imports Polaris Reporting Workbench reports either through the import menu option or an API.

The import process consolidates different RWB reports, which are based on the same database views, into single Blitz Reports. The RWB report specific column selections are imported as individual Blitz Report templates.
During import, you can set the option ‘Expand Custom Views’ to expand the SQL text from the underlying database views into the imported Blitz Report. This increases flexibility as the SQL text can be maintained through the Blitz Report form instead of compiling a view into the database.

Import API

To mass import Polaris Reporting Workbench reports into Blitz Report via a SQL script, use the following PLSQL procedure:

The example script mass_import_polaris_reporting_workbench.sql imports all RWB reports that were executed within the given number of days automatically into Blitz Report. Parameter ‘p_expand_view_sqls’ defines if the import process expands either all, none or only custom views, which are identified as starting with either XX% or any of the custom application short names. Any view which references Polaris RWB objects starting with XXRX%, such as views using function xxrx_util_pkg.get_parameter_char_value() for example, will be expanded regardless of the setting for parameter p_expand_view_sqls.

In case you need to re-run the import, for example with different parameters, you can use the following script to remove all imported reports again: mass_delete_polaris_reporting_workbench_reports.sql.

7.10 Oracle Reports (RDF)

The ‘Blitz Report RDF Import’ concurrent program converts Oracle Reports files: the report’s data model SQL and its concurrent program parameters, value sets and defaults become a Blitz Report with equivalent parameters.

7.11 SQL Server Reporting Services

The ‘Blitz Report SSRS Import’ concurrent program imports SQL Server Reporting Services report definitions.

7.12 Custom Reports Converter

The Blitz Report Custom Reports Converter gathers a customer’s custom concurrent programs with their execution sources (RDF, BI Publisher, host or SQL) and all custom database dependencies into one package, which is then used to migrate the custom reports to Blitz Reports.

7.13 Oracle FSG and GL Wand

Oracle FSG report definitions and GL Wand / Spreadsheet Server workbooks are converted by the GL Financial Statement and Drilldown (FSG) converter, described in the FSG chapter of the User Guide.

8 APIs and integration

8.1 Export

To make migration tasks easier Blitz Report offers the following Export APIs

• Blitz Report Library Export API

8.2 Import

To make migration tasks easier Blitz Report offers the following Import APIs

• XML Upload Import API
• BI Publisher Import API
• Discoverer Worksheet Import API
• Discoverer Folders Import API
• Excel4apps Reports Wand Import API
• Enterprise Command Center Import API
• Polaris Reporting Workbench Import API

8.3 Submitting Blitz Report from PLSQL

Blitz reports can be started from PLSQL as a background concurrent program, or through an API call returning the Excel output as a blob.

FND concurrent program API

Blitz reports can be submitted through the Oracle standard fnd_request.submit_request api. Arguments1-14 specify the report and template names, and other runtime options, such as the email address or output format. The user entered report parameters start from arguments15 onwards.

Blitz Report concurrent program API

You can also use the API xxen_api.report_submit_concurrent_, which allows referencing the report, parameter and template names by name or id instead of argument position, as shown in the following example.

You can specify the following runtime option names in procedure set_runtime_option:

TEMPLATE_NAME
EMAIL
OUTPUT_FORMAT
ROW_LIMIT
TIME_LIMIT
DISABLE_COLUMN_TRANSLATIONS
EXCLUDE_COLUMN_HEADERS
CUSTOM_POSTPROCESS
OUTPUT_FILE_NAME
SERVER_OUTPUT_DIRECTORY
SERVER_FILE_NAME
ORGANIZATION_ID

PLSQL API returning a BLOB

You can create a blitz report from a PLSQL API as shown in the following example. First create a new run_id then set all parameter and runtime option values and finally call the report creation API. Procedures create_run_id and set_parameter_value can be called either by id or by value.

Opening reports from custom forms

Using the above PLSQL API returning a BLOB, You can open a blitz report directly from a custom form, for example in a when-button-pressed trigger. Blitz Report uses this approach, for example, when pressing the preview button on the on the mass change window.

The file download is done through procedure xxen_utils.download_file, which is included in the XXEN.pll library, so you would need to attach this library to your custom form to use this functionality.

Here is a forms code example:

Submitting a Blitz Upload from PLSQL

An upload file can also be submitted programmatically, for example from a custom integration process: load the Excel file into Oracle’s fnd_lobs table and call xxen_upload.submit_upload_process, which starts the upload concurrent program and returns an error message, or null on success.

declare
l_request_id number;
l_error varchar2(4000);
begin
  l_error:=xxen_upload.submit_upload_process(
  p_report_name=>'GL Journal Upload',
  p_template_name=>null,
  p_file_id=>:file_id, --fnd_lobs file_id of the Excel file
  x_request_id=>l_request_id
  );
end;

For recurring file based integrations without coding, the upload concurrent program can also pick up files from a server directory on a schedule, see Scheduled uploads as an inbound interface in the User Guide.

8.4 Useful DB functions

There is a list of useful function from custom package XXEN_UTIL that can be used during report creation.

XXEN_UTIL.CLIENT_TIME (p_date in date)

This function allows converting date from server’s timezone to client local timezone.
It checks if there is difference in time zones between client and server. If any, it converts time from server timezone to client timezone.

• Input parameter: Date in server timezone
• Result: Date in client timezone

Server timezone derived from profile value ‘Server Timezone’:

Client timezone derived from profile value ‘Client Timezone’:

Example:

Assume we have:

• Server Timezone set to GMT+1
• Client Timezone set to GMT+2
• Server’s current time is 2020.09.03 09:00:00

select 
to_char(xxen_util.client_time(to_date('2020.09.03 09:00:00', 'YYYY.MM.DD HH24:Mi:Ss')), 'YYYY.MM.DD HH24:Mi:Ss')
from dual

2020.09.03 10:00:00

XXEN_UTIL.SERVER_TIME (p_date in date)

This function works in the similar way as one described above. It checks if server timezone differs from client timezone and converts date if needed.

Example:

Assume we have:

• Server Timezone set to GMT+1
• Client Timezone set to GMT+2
• Client current time is 2020.09.03 09:00:00

select 
to_char(xxen_util.server_time(to_date('2020.09.03 09:00:00', 'YYYY.MM.DD HH24:Mi:Ss')), 'YYYY.MM.DD HH24:Mi:Ss')
from dual

2020.09.03 08:00:00

XXEN_UTIL.TIME (p_seconds in number)

This function converts number of seconds to the time in format “67d 14h 45.3s”.

So, it shows the number of days, hours, minutes and seconds. It does not show number of moths or years.

• Input parameter: number of seconds
• Result: string of characters showing how many days, hours, minutes and seconds there are in givens number of seconds

Examples:

select xxen_util.time(3600) from dual

1h 0m 0s

select xxen_util.time(361012) from dual

4d 4h 16m 52s

select xxen_util.time(36101200) from dual;

417d 20h 6m 40s

XXEN_UTIL.USER_NAME (p_user_name in varchar2)

This function converts user name to the full description of the user.
Example: SYSADMIN would be converted to SYSADMIN (System Administrator).

• Input parameter: User Name
• Result: User Description

Example:

select xxen_util.user_name('SYSADMIN') from dual

SYSADMIN (System Administrator)

XXEN_UTIL.USER_NAME (p_user_id in pls_integer)

Similar to the function above returns user name and user description based on user id. Note: User description can be deactivated by profile option Blitz Report Show User Description.

• Input parameter: user id
• Result: user name and user description

Example:

select xxen_util.user_name(0) from dual

SYSADMIN (System Administrator)

XXEN_UTIL.USER_ID (p_user_name in varchar2)

Function returns user id based on user name.

• Input parameter: user name
• Result: user id

Example:

select xxen_util.user_id('SYSADMIN') from dual

0

XXEN_UTIL.DFF_COLUMNS

Returns a SQL text for the descriptive flexfield columns of a specified table, to be used for dynamic &lexical replacement.

select
xxen_util.dff_columns(
p_table_name=>'mtl_system_items_b',
p_table_alias=>'msiv', --Table alias if different than the table name standard, for example msiv instead of msib generates: msiv.attribute15 "Invoice UOM"
p_descr_flex_context_code=>null, --Restrict to a specific flexfield context. Default is to show all dffs from all contexts, starting with 'Global Data Elements' 
p_column_name_prefix=>null, --Prefix for dff column name, for example 'Item: ' creates a text such as: msiv.attribute15 "Item: Invoice UOM"
p_prefix=>null, --Prefix for column name to replace table attribute columns with this text, which can be used in an outer query, for example 'x.' generates: x."Invoice UOM" instead of msiv.attribute15 "Invoice UOM"
p_display_mode=>:dff_display --Display mode bound to the report's DFF Display parameter: null/'V'=value only, 'C'=value with description in one column, 'S'=value and description in separate columns. The default for the DFF Display parameter is read from the Blitz Report DFF Display profile option.
) dff_column_text
from dual

XXEN_UTIL.MEANING (p_lookup_code in varchar2, p_lookup_type in varchar2, p_application_id in varchar2)

Translates a lookup code into the user visible, translated meaning. Use this function instead of joining lookup tables such as fnd_lookup_values directly.

select xxen_util.meaning(ooha.freight_terms_code,'FREIGHT_TERMS',660) freight_terms from oe_order_headers_all ooha

XXEN_UTIL.LOOKUP_CODE (p_meaning in varchar2, p_lookup_type in varchar2, p_application_id in varchar2)

The reverse of xxen_util.meaning: translates a user visible meaning back into the lookup code, for example to compare a parameter value entered as a translated meaning against a code column.

select xxen_util.lookup_code('Yes','YES_NO',0) from dual --returns: Y

XXEN_UTIL.YES (p_lookup_code in varchar2)

Returns the translated meaning ‘Yes’ for lookup code Y and null for anything else. This is the standard way to show flag columns in reports: rows with the flag set show ‘Yes’ and all other rows stay blank instead of showing a wall of ‘No’ values.

select xxen_util.yes(aps.hold_all_payments_flag) hold_all_payments from ap_suppliers aps

XXEN_UTIL.CONTAINS (p_parameter_value in varchar2, p_column_value in varchar2)

Returns Y if the column value is one of the values of a multi-select parameter value list. It is used in dependent lists of values whose parent parameter has multiple values enabled, where a direct equality comparison would not work.

(:$flex$.Ledger is null or xxen_util.contains(:$flex$.Ledger,gl.ledger_id)='Y')

XXEN_UTIL.ROWGEN (p_rows in number)

Table function returning the numbers 1 to p_rows as rows, used as a row generator, for example to split a delimited string into one row per element.

select regexp_substr('A;B;C','[^;]+',1,rowgen.column_value) element from table(xxen_util.rowgen(regexp_count('A;B;C','[^;]+'))) rowgen

XXEN_UTIL.DISPLAY_FLEXFIELD_VALUE (p_application_id in number, p_descriptive_flexfield_name in varchar2, p_context_code in varchar2, p_column_name in varchar2, p_rowid in rowid, p_value in varchar2)

Returns the display value of a single descriptive flexfield segment, translating stored value set ids into their visible values. Always pass the stored column value in p_value so the function does not have to re-query the base table by rowid; the rowid is still needed to resolve dependent value sets.

select xxen_util.display_flexfield_value(140,'FA_ADDITIONS',fab.attribute_category_code,'ATTRIBUTE1',fab.rowid,fab.attribute1) from fa_additions_b fab

9 Administration reference

This chapter is the administrator’s reference for the components that Blitz Report installs into Oracle E-Business Suite. Setup instructions are in the Installation Guide; this chapter lists what exists and what it is for.

9.1 Concurrent programs

Blitz Report ships the following concurrent programs:

9.2 Profile options

All Blitz Report profile options are documented in the Profile options chapter.

10 Profile options

The Blitz Report Access profile option controls access to Blitz Report. The site-level default ‘User’ is set during installation and works for most users. Only set it to a higher level (e.g. ‘Developer’) for users who need to create or modify reports.

All other profile options are optional and work best left blank for most installations. Only set a value when you have a specific requirement — the defaults are designed to work well out of the box.

12 Tips and tricks

12.1 Incremental outbound interface

Integration with Oracle’s concurrent delivery options allows scheduling a Blitz report as an outbound interface or monitoring tool. If you need to transfer incremental data changes only, you can restrict the query to records modified since the previous scheduled request run by a parameter SQL like the following example:

You can find an example of such a parameter restriction in our seeded report FND Concurrent Requests, which uses this logic in parameter ‘Incremental Alert Mode’ to monitor concurrent request activity and send an alert email e.g. only in case of errors that occured since the last scheduled report run. If a scheduled report does not retrieve any data, Blitz Report does not send an empty output file. In case you also want to send empty output files for scheduled reports, set the profile option Blitz Report Suppress Empty File Delivery to ‘No’.

If you want to give your outbound interface report an additional level of protection and allow modifications by users with ‘System’ access profile only, set its type to ‘Protected’.

12.2 Writing report output to a server directory

Blitz Report can write a copy of the report output to a directory on the application server, e.g. by scheduling a report and writing the output file in XLSX or CSV format to a network folder, where it can be picked up as an outbound interface or read by other reporting tools.

The location of these files is controlled by the Server Output Directory runtime option, which can also be defaulted per report in the report definition. Any network folder mounted on the application server can be used as the destination. Which users may set it, and whether they can type a directory freely or have to select one of the approved output locations, is governed by the Blitz Report Server Output Directory Access profile option.

If a scheduled report does not retrieve any data, no file is written to the directory, so a downstream interface never has to deal with empty files. If you do want empty files to be written as well, set the profile option Blitz Report Suppress Empty File Delivery to ‘No’.

The Server File Name runtime option defines the template of the file name, and allows, for example, to overwrite an existing XLSX file with refreshed data by a scheduled Blitz Report concurrent request.

Setting this runtime option to a template containing date format string <report_name>_<DD-Mon-YYYY> and scheduling a report every 30 minutes, for example, would write one separate file per day which gets refreshed with current data every 30 minutes.

Please refer to the Server File Name runtime option description to understand rules for building file name templates using tokens.

This generates a time series of data files, which can then be used as a data warehouse for analysis by other tools such as Microsoft Power BI, Qlik Sense, Tableau or OBIEE.

12.3 MS Excel blocked macros warning

You may face the following warning when using Excel templates with macros after running a report and opening the output:

SECURITY RISK Microsoft has blocked macros from running because the source of this file is untrusted.

To fix it please add the EBS url to the trusted sites or ask your system administrator to do it. The required steps may differ depending on your operating system version. The below screenshots show the steps on Windows 11. To access the required configuration screen please type ‘Internet Options’ in the Windows search box.