Follow us: Connect on YouTube Connect on YouTube Connect on YouTube


Thursday, 7 November 2019

EIS eXpress Reports Development in Oracle Apps R12

Profile Values Developer Setup:

To be able to develop reports the user will need the following profile options set.

EIS: Calculation Columns
EIS: Copy Reports
EIS: Create Reports
EIS: Create/Edit Objects
EIS: Free Text Filters
EIS: Modify Report
EIS: Report Requests View
EIS: Restrict DML manipulations
EIS: Triggers
EIS: Restrict select clause in calculation column

Responsibility:     XXEIS eXpress Administrator

Path:           Security => Profiles => Profile Option Name

Note: Set profiles at user level.

XXEIS eXpress Profile Option Name

Module Security:

To be able to create a report against a particular module (Oracle Application) the developer must be assigned to the module, this is the same for users trying to run a report.

Responsibility:     XXEIS eXpress Administrator
Path:           Security => Module Security => Select

XXEIS eXpress Module Security

This form gets a bit messy if there are many users so the best way to check if a user has module access is the try and add them.

JGUY has access

XXEIS eXpress Module Security1

So when you try to grant access

EIS eXpress Grands

The user will not be selectable

EIS eXpress User will not select
However, WYOUNG did not have access so name is selectable.

Save the change

WYOUNG now has access to General Ledger

View Creation

Create Database view as shown below example

SELECT   msi.segment1                                      item_num
,        msi.description
,        msi.primary_unit_of_measure
,        msi.primary_uom_code
,        msi.inventory_item_status_code
,        msi.purchasing_item_flag
,        msi.purchasing_enabled_flag
,        msi.enabled_flag                                  item_enabled_flag
,        msi.attribute1                                    item_attribute1
,        msi.attribute2                                    item_attribute2
,        msi.attribute3                                    item_attribute3
,        msi.attribute4                                    item_attribute4
,        msi.attribute5                                    item_attribute5
,        msi.attribute6                                    item_attribute6
,        msi.attribute7                                    item_attribute7
,        msi.attribute8                                    item_attribute8
,        msi.attribute9                                    item_attribute9
,        msi.attribute10                                   item_attribute10
,        msi.attribute11                                   item_attribute11
,        msi.attribute12                                   item_attribute12
,        msi.attribute13                                   item_attribute13
,        msi.attribute14                                   item_attribute14
,        msi.attribute15                                   item_attribute15
,        msit.long_description
,        mc.segment3||'.'||mc.segment1||'.'||mc.segment2   oracle_category
,        mc.enabled_flag                                   category_enabled_flag
,        mc.summary_flag                                   category_summary_flag
,        mc.start_date_active                              category_start_Date
,        mc.end_date_active                                category_end_Date
,        mc.attribute1                                     category_attribute1
,        mc.attribute2                                     category_attribute2
,        mc.attribute3                                     category_attribute3
,        mc.attribute4                                     category_attribute4
,        mc.attribute5                                     category_attribute5
,        mc.attribute6                                     category_attribute6
,        mc.attribute7                                     category_attribute7
,        mc.attribute8                                     category_attribute8
,        mc.attribute9                                     category_attribute9
,        mc.attribute10                                    category_attribute10
,        mc.attribute11                                    category_attribute11
,        mc.attribute12                                    category_attribute12
,        mc.attribute13                                    category_attribute13
,        mc.attribute14                                    category_attribute14
,        mc.attribute15                                    category_attribute15
FROM     mtl_system_items_b       msi
,        mtl_system_items_tl      msit
,        mtl_item_categories      mic
,        mtl_categories_b         mc 
WHERE    msi.organization_id   = FND_PROFILE.VALUE('ORG_ID')
AND      msi.organization_id   = mic.organization_id
AND      msi.inventory_item_id = mic.inventory_item_id
AND      mic.category_id       = mc.category_id
AND      msi.organization_id   = msit.organization_id
AND      msi.inventory_item_id = msit.inventory_item_id
AND      msit.language         = userenv('LANG');

If you included more fields than this in the actual view; remember “Power User” can copy the report and add columns to it as long as they are present in the view.

It should also be noted that I have not aliased the attributes with the report column name this is because attributes can have different meanings/uses across organizations and the column name can be set in the report format section, covered later in this document.

Create the view in the relevant environment for import into EiS eXpress Reports

View Import:

One you have created the view it must be imported into EiS before it can be used.
Responsibility:     XXEIS eXpress Developer
Path:           Setup => Data Sources => Import Object

Select the apps schema, all bespoke views should be created in the apps schema.
Select the object (view) that you want to import into EiS

Select the module (Oracle Application) you want to import the view into.

Note: If you are using the view for a Purchasing and General Ledger report you will need to import it into both Modules.
If the module is not in your list see Module Security in the “Developer Setup” section.
Note: The module should be the most likely Oracle Application that the user would expect to find the report in i.e. a Purchase Order report would most likely be access by Purchasing Users and Purchasing would be the logical EiS module to put it in.

Make sure you click the “Import Objects” button before navigating away from this screen.

View Amendments:

If you amend a view once it has been imported, i.e. add, rename or remove a column from the view you must update the view definition in EiS before the report will reflect the changes.
Responsibility:     XXEIS eXpress Developer
Path:           Setup => Data Sources => Search by

Adding Columns

If you have added columns to the view you will need to call the view back in the “Data Sources” screen and use the “Show Object Changes” button to review columns that can be added to the view.

The top section of the screen will now show you columns that can be imported into the Object (view), select all of the columns and click the import button.

The new columns will now be available for selection in the report.

Removing Columns:

If you have removed or renamed columns in the view it is equally as import to delete them from the view definition in EiS as well. Call the view back in the “Data Sources” screen as per above and use the “Show Object Changes” button to review columns that have been removed from the view.

The second section of the screen will show all of the columns that no longer exist in the Oracle view and allow their deletion. Select them all and click “Delete”

Note: Deleting a column here will remove it from the report, if you have a column selected for use in a report that no longer exists in the underlying Oracle view the report will error with “invalid identifier” message. Failure to remove columns from EiS after removing them from the Oracle view could result in “Power Users” thinking they are valid and getting the aforementioned issue when they run the report.

Report Creation:

Responsibility:     XXEIS eXpress Developer
Path:           Setup => Create Report

New Report

The first screen you encounter holds the general information for the report.

Module:     This should be the module into which the view was imported and the most likely Oracle Application for the users to look for the report.
Report Name:      Name of the report appended with (c) to show it is a custom report.
Category:   This should be a logic place for the report to sit i.e. a report that returns information about Purchase Orders should be in the Purchasing module and use the Purchase Order Category.
Users can search in the report list by module and category so try to make them as intuitive as possible.

Description:         This should be a description of what the report does; this can be taken from the MD050 business requirement. It will appear in the report submission screen under the parameters.
Report Instructions: Usually left blank
Report Source:    Always use Object as DHL Policy prohibits the use of Paste SQL.
Language: American English
Object Name:      The name of the view that the report is to be built on i.e. the name you have given the view in “Object Creation” above.
Output Types:     The four main output types will each be saved on the server or into Oracle tables so to avoid using more space than absolutely necessary only choose ones that will actual be used in live, also Pivot Excel has a data sheet so there is no point in allowing Excel and Pivot Excel as it is a duplication of data.

Template Based Output Types: These types are memory intensive so only use templates where necessary.
Override Output Options: Tick this box to ensure that the only output types allowed for the report are the ones you have specified.
Remember to save the changes

Colums Selection
Select the columns from the view that are required in the report output.
Choose the edit button next to the view

Select all of the required columns

Save the changes

Column Order

You can change the order that the columns appear in the output of the report using the “Column Order“ tab, use the up/down buttons to move the columns.

Column Order

The “Add Distinct Clause” seen above will get the query to return distinct rows i.e. the equivalent of SELECT DISTINCT…

Add Distinct Clause in EIS Reports

Example Move the description up

Remember to save the changes

Save Changes


The “Make at least one parameter mandatory” field shown above will force the users to enter at least one parameter, however more complex parameter combinations can be enforced using a trigger, covered later in this document.

Add a Parameter

Parameter Name: The name shown in the parameter box
Description:  Information to show in the help box, if no help box information leave blank

Parameters in EIS reports
Parameter Hint in EIS reports

Data Type: Expected data type for the parameter i.e. VARCHAR, DATE or Number
LOV:   List of values for the parameter pick list, see LOVs section.
Date List: Unknown
Default Type:      SQL: A SQL Statement used in the “Default Value”
                             Constant: Free text used in the “Default Value”
                             Current Date: Sysdate used as the “Default Value”
Default Value: This is the default value for the parameter when the report is run.

If Default Type is set to “SQL” and default value contains “SELECT FND_PROFILE.VALUE('USERNAME') FROM dual” then the parameter will be populated with the users name at run time.

Required: Check if the parameter is mandatory
Order: The order the parameter appears at run time
Condition: EiS automatically creates a condition for this parameter if checked, if you are tying the parameter to a field in the report then then check this box and populate the next two fields. If you are passing the parameter to the view via a trigger (covered in Triggers) then leave unchecked
Operator:   The operator for the parameter.
Database Column:The column in the view that the parameter is tied to
Multiple Entries: If the user is allowed to select multiple items from the pick list and you are using an “IN” operator then leave this checked, if you are using an equals or other single input operator i.e. greater than, then uncheck this box.

Parameter Data type Define
Enabled: Disabling the parameter will leave the information in the report definition but stop its use.
Display: Uncheck to hide from the end user, this can be used for hidden parameters
Is Dependant: Information to follow
Dependant On: Information to follow

Remember to save the changes

Conditions act as the where clause for the report and are created in two ways:
Automatically by EiS when the condition check box is ticked on a parameter.

Simple Condition
This is the type automatically created by EiS when the condition check box is ticked during parameter creation and just add an “AND…” to the report logic.
For example if the users only want to see active items in their report you could hard code the view with “AND enabled_flag = ‘Y’” but what if there is another requirement to show similar fields in a new report utilising the same view but they want all items. This is when you would place a condition on one of the reports to show enabled items only.

Note:  Currently there is a bug in EiS whereby you cannot type into the value field, you have to type into a text editor and copy the text then right click in the Value field and paste.

Remember to save the changes

Advanced Condition
An advanced condition allows multiple simple conditions to be link, this is how an OR statement can be achieved.

Remember to save the changes

Free Text Condition
This type of condition allows free text SQL entry so the logic can be as complex as required, parameter can be used in this logic as a bind variable based on the parameter name i.e. :IPN for our example report.
Free text condition in eis report

Free Text SQL: AND ( xpimv.item_enabled_flag = 'Y' OR imv.item_enabled_flag = 'N' )

Remember to save the changes

Ordering by output columns can be added to the report, but when the output is an Excel spreadsheet it is better left to the users.

Sort data in eis reports
Remember to save the changes

User Column Name

Formate user columns in eis reports
In the format tab you can change the name of the report output columns, it is here you will change the view attribute columns to have more meaningful names.
Formate user columns

Remember to save the changes


Triggers can be used for a variety of reason, checking parameters, enforcing parameters, calling packages and passing parameter directly into a view.

Calling Packages to Populate Custom Tables

The following section has been taken from the EiS eXpress Reporting Create Trigger Based Report document version 8 and adapted to remove references to the eis_rs_common_outputs table (common table). It should be noted that some of the EiS reports developed by DHL use the common table, but its use is being phased out because it could lead to performance issues. There are two differences between the instructions below and the use of the common table. (1) The creation of a view that points to the common table instead of a custom table creation script (2) when the common table is used the delete in the after report trigger is not required.

Step One
Create a custom table to hold the report data.
Note: The first column in the table is used to hold the EiS process id.
   (  PROCESS_ID              NUMBER
    , PO_HEADER_ID            NUMBER
    , PO_NUMBER               VARCHAR2(30)
    , etc.

Note: Think about using the process id in indexes for the holding table to improve performance.

Step Two
Create a package that contains a procedures to populate and delete from the custom table.
Note: The first parameter of the procedures is used to pass the EiS process id.
                        , other parameters…
   ,      PO_HEADER_ID
   ,      SEGEMENT1     PO_NUMBER
   ,      etc.


Step Three

Use the before report trigger to pass the process id and report parameters to the custom procedure that populates the custom table.
Note: :SYSTEM.PROCESS_ID is used to pass the EiS process id to the procedure.

Before Report in EIS report

Step Four

Use the after report trigger to pass the process id to the procedure that deletes from the custom table.

Before Report Trigger
Step Five
Add a condition to your report to restrict the records returned to its own process id

Before report

Passing Mandatory Parameters to a view

This is done with a package that contains a global variable and a function that can be called from the view.

Package Specification

In the package specification declare the global variables and function to be called from the view.
   G_DATE_FROM       ap_invoices_all.creation_date%TYPE;
   G_DATE_TO         ap_invoices_all.creation_date%TYPE;
   FUNCTION GetFromDate

In the package body use the function to return the global variable.

   FUNCTION GetFromDate
   END GetFromDate;
   END GetToDate;

Define the parameter in the report; do not link it to a data column.


Populate the global variable using the report trigger

Call the function from the view to get the parameters from the report.

FROM    …
WHERE   ou.organization_id = i.org_id
AND     i.creation_date BETWEEN xxeis_ap_vendor_spend_pkg.GetFromDate
                            AND xxeis_ap_vendor_spend_pkg.GetToDate
AND     i.invoice_type_lookup_code != 'PREPAYMENT'
AND          …

For non-mandatory parameters and paramaters that need to be manipulated first see the “Manipulating Parameters before passing to a view” section.
Report Security

Report security can be set at three levels, User, Responsibility and Request Group. DHL Supply Chain currently set report security at Request Group level. This is the Oracle Request Group assigned to the Oracle Responsibility used by the user when accessing the Oracle Applications i.e. in the case of a GB Payables Manager it would be “GB001 Payables Manager” and not the XXEIS responsibility.
To assign the “Global Invoice Status Report (c)” to a user with the “GB001 Payables Manager” responsibility first determine which request group the responsibility users.
Responsibility:      System Administrator (or equivalent)
Path:           Security => Responsibility => Define

Concurrent program

Then use the security tab in EiS to assign the report to the request group.
Responsibility:      XXEIS eXpress Developer
Path:           Security => Report Security

Call back the report

Check the existing security

Note: When assigning a report you cannot see which request groups it is already assigned to so you have to check up front.

Note: You may need to use the “Next” button to see all of the assignments.
If the report is not already assigned, re-query it and use the security button to assign it.

Select the Request Group radial button

This will make the “Add Request Group” button active

Use this to assign the report to the request Group

Remember to click the Grant Access button to save the changes, you can check this by repeating the steps at the beginning of this section on checking the security.

Exporting EIS report 

To move EiS reports between environments you can export the report and run the resulting script from a SQL session in the new environment.
Responsibility:     XXEIS eXpress Developer
Path:           Setup

Click the export button

Tick the following selections

Export Object Source is unchecked because it embeds a view creation script in the exported report creation script and we handle view creation with a separate SQL script file.
Export Report Security is usually left uncheck because it will copy the security from a development environment into a live one and often security is added in a development environment to allow testing and may not actually be required in Live.

Click the export button

Try to make the name of the export / import script meaningful for the report, where possible I use the view name and substitute _V with _RPT.

Share this article :


Post a Comment