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 to retrieve the data like a Report, additionally it has a “Upload” tab which enables mapping the SQL query columns to the upload API parameters or the Interface table columns.
To create new Uploads, the profile option ‘Blitz Report Access’ 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:
- Enter a report name, an optional description and set the type as Upload
- Enter the main extraction SQL
- Define the required and optional parameters
- Define the upload and review the mapping between the parameters and the SQL columns
- Optionally define a post procedure and review the mapping between the parameters and the SQL columns
- Review the upload columns and enable the excel validations
- Test the upload by clicking the ‘Run’ button
- Set up the user access rights
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’.
6.2 SQL requirements
For API based uploads, the SQL to retrieve the existing records should always contain action_, status_ and message_ columns in the first three positions and in the same fashion as below:
null action_, null status_, null message_ |
For Interface Table based uploads, the SQL should always contain action_, status, message_ and row_id_ columns in the first four positions and in the same fashion as below:
null action_, null status_, null message_, .rowid |
Additionally, the below lexicals should be added at the end of the SQL:
¬_use_first_block &report_table_select &report_table_name &report_table_where_clause &success_records &processed_run |
6.3 Required parameters
The parameters ‘Processed Run’ and ‘Report Table Name’ are always required to create an upload.
The parameters should be defined like:
| Display Seq | Parameter Name | Anchor | SQL Text | Type | How to add |
|---|---|---|---|---|---|
| -30 | Processed Run | &processed_run | and ‘Y’=’Y’ | Char | This is used by the upload framework to indicate the Blitz Report run after the upload processing completes. Copy as-is, no changes required. |
| Processed Run | ¬_use_first_block | and 1=0 | This is used by the upload framework to avoid running the main SQL after upload processing completes. Copy as-is, no changes required. | ||
| Processed Run | &report_table_select | union all select xxen_upload.action_meaning(xur.action_) action_, xxen_upload.status_meaning(xur.status_) status_, xur.message_, xur.id, xur.name, xur.date_of_birth from |
In the SQL text, write the select clause for the error records SQL statement. Here ‘xur’ is the alias given to the automatically generated reporting table which contains the data used for the upload. The reporting table columns have the same name as the SQL columns. ‘union all’ is required at the beginning of the SQL text and ‘from’ is required at the end. | ||
| Processed Run | &report_table_where_clause | xur where xur.status_=xxen_upload.status_error |
In the SQL text, write the where clause for identification of the error records. Reporting table alias ‘xur’ is required at the beginning of the SQL text. | ||
| Processed Run | &success_records | union all select xxen_upload.action_meaning( xxen_upload.action_processed) action_, xxen_upload.status_meaning( xxen_upload.status_success) action_, xur.message_, xus.id, xus.name, xus.date_of_birth from &report_table_name xur, xxen_upload_sample xus where xus.id=xur.id and xur.status_= xxen_upload.status_success |
In the SQL text, write the SQL statement for success records selection. ‘union all’ is required at the beginning of the SQL text. | ||
| -20 | Report Table Name | &report_table_name | Char | This is used by the upload framework to capture the upload reporting table SQL. Copy as-is, no changes required. |
Note: There are pre-built functions which should be used in the parameters SQL text when working with ‘action_’ and ‘status_’ columns.
- Functions returning action constants: xxen_upload.action_create, xxen_upload.action_update, xxen_upload.action_processed
- Function returning action constant’s meaning: xxen_upload.action_meaning, example: xxen_upload.action_meaning(xxen_upload.action_create)
- Functions returning status constants: xxen_upload.status_new, xxen_upload.status_error, xxen_upload.status_success
- Function returning status constant’s meaning: xxen_upload.status_meaning, example: xxen_upload.status_meaning(xxen_upload.status_new)
Optionally other parameters can be added to restrict the records returned from the main SQL like a Blitz Report.
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.
Note: If using an API then a custom wrapper procedure needs to be created with the below required parameters:
action_ in varchar2 status_ out varchar2 message_ out varchar2 |
| Name | Type | Data Type | How to use> |
|---|---|---|---|
| action_ | in | varchar2 | Input values passed by the upload framework are: xxen_upload.action_create, xxen_upload.action_update |
| status_ | out | varchar2 | This is to be populated in the wrapper procedure based on the processing outcome, for error records populate: xxen_upload.status_error, for success records populate: xxen_upload.status_success |
| message_ | out | varchar2 | This is to be populated in the wrapper procedure based on the processing outcome, this will appear in the excel output generated after upload processing. |
Refer this PL SQL package for usage example: XXEN_UPLOAD_SAMPLE_API.zip
In/Out
In/Out is auto populated on selection of API, indicates if the API parameter is of type ‘in’, ’out’ or ‘in/out’.
Parameter Name
Parameter Names are auto populated on selection of the API, in case of Interface table the table column names are populated.
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.
Upload Query
Upload Query enables converting the column’s value in the excel file to an id value before it is passed to the API parameter or the Interface table column. A SQL query can be defined which is used for the conversion and it should contain id and value columns.
Upload Columns list all the available SQL columns derived from the main report SQL. It enables extra functionalities against these columns in the excel file which include:
- Adding List of Values to the column by defining an LOV SQL query.
- Enforcing data type validation against the column.
- Making columns required in the excel file.
- Enable automatic hiding of columns which are to be used for internal processing of the upload.
- Making columns read only which stops flagging records for update when these columns are updated.
- Defaulting column values in the excel.
- Comments can be enabled which can be seen against the column in the excel header record which can be used to provide additional information about the column.
Number
Number is auto populated with the sequence of the SQL Columns
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
selectmsiv.description value,msiv.description descriptionfrommtl_system_items_vl msiv,financials_system_params_all fspa,hr_all_organization_units_vl haouvwherehaouv.name=:$flex$.operating_unit andfspa.org_id=haouv.organization_id andmsiv.segment1=:$column$.item andmsiv.organization_id=fspa.inventory_organization_id
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.
6.4.3 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.
Procedure Name
Choose between the available PL SQL procedures, and on selection it auto populates the parameters like the upload parameters.
6.4.4 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.