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


Sunday, 1 December 2019

Types Of Triggers In Oracle Reports

What types of triggers are there in oracle report ?

      There are eight Triggers available, among them five triggers consider as global Oracle Report triggers and these trigger are used initializing parameter values, Validate Parameter values and Dynamic query execution

Oracle Reporting Triggers

Following Order of Oracle Triggers Firing sequence

1.Before Parameter Form
2.After Parameter Form
3.Before Report
4.Between Pages
5.After Report

1Before Parameter Form: Fires before the Runtime Parameter Form is displayed. From this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns. If the Runtime Parameter Form is suppressed, this trigger still fires. Consequently, you can use this trigger for validation of command line parameters.

2. After Parameter Form: Fires after the Runtime Parameter Form is displayed. From this trigger, you can access parameters and check their values. This trigger can also be used to change parameter values or, if an error occurs, return to the Runtime Parameter Form. Columns from the data model are not accessible from this trigger. If the Runtime Parameter Form is suppressed, the After Parameter Form trigger still fires. Consequently, you can use this trigger for validation of command line parameters or other data.

3Before Report: Fires before the report is executed but after queries are parsed 

4Between Pages: Fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting. In the Previewer, this trigger only fires the first time that you go to page. If you subsequently return to the page, the trigger does not fire again.

5. After Report: Fires after you exit the Previewed, or after report output is sent to a specified destination, such as a file, a printer, or an Oracle Office userid. This trigger can be used to clean up any initial processing that was done, such as deleting tables. Note, however, that this trigger always fires, whether or not your report completed successfully.

Following three general triggers not consider as main triggers:

1.Validation Triggers
2.Format Triggers
3.Action Triggers

1. Validation Triggers: Validation trigger are PL/SQL functions that are executed when parameter values are specified on the command line and when you accept the Runtime Parameter Form. (Notice that this means each validation trigger may fire twice when you execute the report) Validation trigger are also used to validate the Initial Value property of the parameter. The function must return a boolean value.

2. Format Triggers: This trigger are PL/SQL functions executed before the object is formatted. The trigger can be used to dynamically change the formatting attributes of the object. The function must return a Boolean value (TRUE or FALSE). Depending on whether the function returns TRUE or FALSE, the current instance of the object is included or excluded from the report output. You can access format triggers from the Object Navigator, the Property Palette, or the PL/SQL Editor.

Definition Level: layout object
On Failure:  Excludes the current instance of the object from the output.
Format trigger example (highlighting a value)
/* suppose that you are building a banking report and would like it to indicate if a customer is overdrawn. To do so, you give the repeating frame around the customer information a format trigger that causes it to have a border only if a customer's account balance is less than 0 (or the required minimum balance). */
function my_formtrig return BOOLEAN is begin
  if: bal < 0 then
    srw.attr.mask := SRW.BORDERWIDTH_ATTR;
    srw.attr.borderwidth := 1;
    srw.set_attr (0, srw.attr);
  end if;
    return (true);

3. Action Triggers: Action triggers are PL/SQL procedures executed when a button is selected in the Runtime Previewer. The trigger can be used to dynamically call another report (drill down) or execute any other PL/SQL. You can access action triggers from the Object Navigator, the Property Palette (PL/SQL Trigger property), or the PL/SQL Editor.

Definition Level: button

Continue reading >>

Friday, 29 November 2019

View Modification In EIS eXpress Reports


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 To Eis Reports

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 To Eis Reports:

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.
Continue reading >>

How To Create View In EIS eXpress Reports


View Creation in eis eXpress Reports

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 in eis:

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.

Continue reading >>

Profile Values Developer Setup in EIS eXpress Reports


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

Continue reading >>