Skip Headers

Oracle iStore Implementation and Administration Guide
Release 12.1
Part Number E13575-06
Go to Table of Contents
Contents
Go to previous page
Previous
Go to next page
Next

Implementing Reports

This chapter covers the following topics:

Overview of Implementing Reports Chapter

This chapter describes the reports available through Oracle iStore.

Note: In previous releases, the Oracle iStore Site Administration Application included a Reports tab where administrators could access business and operational reports and bins. This tab has been decommissioned. The operational reports previously presented in Oracle iStore are now available in Oracle Discoverer Viewer. Most of the business reports now map to new Oracle Web Analytics and the former Oracle iStore Daily Business Intelligence (now merged with Oracle Web Analytics reports). The bins and related functionality are no longer available.

Overview of Oracle iStore Reports

Oracle iStore supplies a variety of data that feeds the metrics in several e-commerce business intelligence and operational reports. The operational reports are presented in Oracle Discoverer Viewer and the e-commerce intelligence reports in Oracle Web Analytics Reporting module. Together, these reports provide a rich collection of valuable data about your sites, your customers, and their ordering activity.

The Oracle Web Analytics reports present the following data:

For more information, see the Oracle Web Analytics Implementation and Administration Guide.

Operational reports present the following data:

The operational reports are presented within Oracle Discoverer Viewer. For more information, see the operational report descriptions later in this chapter.

Oracle iStore Operational Reports

This section describes the operational reports available for the Oracle iStore product catalog and section hierarchy.

Following are the operational reports:

Descriptions of each report can be found in the sections below.

Product Reports and Oracle Inventory Setup

The following rules or guidelines apply to the product reports' integration with Oracle Inventory:

See the chapter, Implementing Products, for more information about product setup for Oracle iStore.

Unassigned Products Report

This Oracle Discoverer report displays products not assigned to any section and created in a given date range. Users can filter products by combining different selection criteria, such as product number or name, category code/name or published status. This report may be useful to a site manager who wishes to:

This report does not show pricing information. If a product is not priced, the product will still show up in the report.

Assigned Products Report

This Oracle Discoverer report displays products that are already assigned to at least one section. Users can filter products by different criteria, such as the product number or name, or creation date range, category code/description and product publish status.

This report may be useful to a site manager who wishes to:

Note: There may be cases where a product is assigned to a section, but this section is not assigned to any site, and/or has a publish flag set to Unpublished. In that case, the product will still appear in the this report. The Assigned Products Report lists products assigned to sections, but doesn't check if the sections are ultimately published or assigned to a site (however, this can be checked with section reports).

Products by Site Report

This Oracle Discoverer report displays products assigned to sites (product can be either Published or Unpublished). Users can filter products by different criteria, such as the product number or name, creation date range, and site assignment. In addition, users can filter the report on Category Code, Category Description, Product Published Status, and section assignment as well. This report may be useful to a site manager who wishes to run a report listing which products are included in which site(s). The site manager can verify which section(s) each product has been mapped.

Guidelines

Keep the following guidelines in mind:

Products Excluded by Site Report

This Discoverer report displays which products have been explicitly excluded from a specific site. Users can filter products by category, product number, and product name. This report includes only products associated with sections – when a product is not associated to a section, there is no site mapping.

This report may be useful to a site manager who wishes to find out which products of a given category are excluded from a site. This can happen when the same section/product hierarchy is shared across multiple sites.

The primary sorting is always on Site Name. For each sort criterion, you can specify if the sort is ascending or descending.

Sections With Site Assignment Report

This Discoverer report displays all sections mapped to sites. Users can filter sections by various criteria, such as the creation date range, section name, or section code. This report may be useful to a site manager who wishes to find out which newly created sections have not been published.

Note: Even if the report is run for Published sections, it doesn't mean the selected sections will show up in the Customer UI. The parent section of the selected sections can be Unpublished, while the section is Published. This exception case is not taken in account in this report.

Sections Without Site Assignment Report

This Discoverer report displays sections which have no mapped sites. Users can filter sections by creation date range, section name, or section code. This report may be useful to a site manager who wishes to find out which sections are not mapped to any sites.

Sections With Site Exclusion Report

This Discoverer report displays sections which have been explicitly excluded from sites. Each site is mapped to a parent section in the site hierarchy. In this report, only sections within the parent section of the selected site(s) are considered. Users can filter sections by creation date range, section name, or section code.

This report may be useful to a site manager who wishes to find out which sections have been excluded from a specific site. This scenario would occur typically when the same section hierarchy is shared across multiple sites. In this case, some sections may be excluded from a site.

Empty Sections Report

This Discoverer report displays all the sections that have no products or sections published under them, across all sites. Users can filter sections by creation date range, section name, or section code.

A section qualifies as empty if it meets at least one of the following criteria:

Example: Section 1 has one child, Product 1. Product 1 is excluded from Site 1, and Section 1 is included only in Site 1. In this example, Section 1 is considered empty.

This report may be useful to a site manager who wants to know if there are any empty sections in any sites. This situation usually happens when an automated script modifies the product flag to Unpublish. In this case, sections may end up empty.

Note: Even if the report is run for Published sections, it doesn't mean the selected sections will show up in the Customer UI. The parent section of the selected sections can be Unpublished, while the section is Published. This exception case is not taken in account in this report.

Implementing Oracle iStore Reports

Oracle iStore reports leverage the following:

The following are required to successfully set up Oracle iStore Reports:

Step 1 - Set up General Ledger Calendar

You must create a single calendar in Oracle General Ledger (GL) with the periods types: Day, Week, Month, Quarter, and Year, in order for the Reports to work.

Or, if you do not want to add all of these periods to the GL calendar, for reporting purposes you can create a separate calendar for iStore in GL with the needed period types.

See Oracle General Ledger User Guide for information on how to perform these tasks. Also refer to the "Setting up Oracle General Ledger" section in the chapter, Verify Mandatory Dependencies.

Step 2 - Set up Oracle Discoverer

The Oracle iStore operational reports are presented in Oracle Discoverer Viewer; therefore, Oracle Discoverer is a required dependency. This section contains Oracle iStore-specific steps required for Oracle Discoverer implementation.

Follow the steps in this section to set up Oracle Discoverer for Oracle iStore operational reports.

Set up Oracle Discoverer for Oracle Applications

Set up Oracle Discoverer for Oracle Applications. Please follow the steps mentioned in the OracleMetaLink Note 185342.1 for generic setup steps.

Post Installation Steps - Specific to iStore Setup

Grant EUL User Edition privileges to the Oracle iStore Administrator responsibility. Example: Grant privileges to responsibility, iStore Administrator.

  1. Log in to Oracle Discoverer 4i Administration Edition using the database username, password, and database created in the section above, "Set up Oracle Discoverer for Oracle Applications". Example: Log in as EUL4_US/EUL@customDB.

  2. Open the Privilege dialog box using Tools, Privileges.

  3. In the Privileges tab, from the LOV select the application responsibility to which you are granting the privileges. Example: Select iStore Administrator from the LOV.

  4. Select checkboxes for the following privileges:

    User Edition

  5. Select Apply to save changes.

Test iStore Discoverer Reports

Important: Before you begin testing, you will need to bounce the Apache port. This is necessary to reload the site-level profile options.

Testing on Discoverer Viewer

Use the following steps to test the iStore Discoverer Reports on Discoverer Viewer.

  1. Connect to the following URL:

    http://<discoverer server>:<port>/discoverer4i/viewer/

  2. Log in using:

  3. After logging in, select the iStore Administrator responsibility hyperlink.

    Ensure that you are connected to the correct EUL:

    If the EUL is not correct, change it, and select Apply to save changes.

  4. You should see a list of the available iStore Discoverer reports. To view any of the reports, select the appropriate hyperlink.

Step 3 - Set Profile Options

Set the following profile options:

Required Profile Options

Optional Profile Options

See the appendix, Profile Options, for profile option descriptions and setup procedures.

Step 4 - Set Conversion Rates

Oracle iStore can have business transactions from multiple sites, each of which can support multiple currencies. This results in the Oracle iStore functionality compiling business reports from transactions in multiple currencies. These currencies must be resolved to a single currency for Oracle iStore reports.

The currency for the fact tables is determined by the profile options, IBE: Currency Code and IBE: GL Conversion Type, and the daily currency conversion rate in Oracle General Ledger. See the section above, "Set Profile Options", for more information.

To set the daily currency conversion rate in Oracle General Ledger, log in to Oracle Forms with the General Ledger Super User responsibility and navigate to the Daily Rates window.

Guidelines

Keep the following guidelines in mind:

For more information on setting currency conversion rates, see Oracle General Ledger User's Guide.

Step 5 - Run Concurrent Program

The concurrent program, iStore Reports Fact Tables Refresh, populates the Oracle iStore reports' fact tables with data. The program can be run in increment or complete mode; both of these options are discussed below.

To run the program, log in to Oracle Forms with the iStore Concurrent Program Responsibility.

Prerequisite: The calendar in Oracle General Ledger must be set up for iStore before running this concurrent program. For more information, refer to the "Setting up Oracle General Ledger" section in the chapter, Verify Mandatory Dependencies.

Note: It is recommended that you schedule these concurrent program sets to run at regularly scheduled intervals. Otherwise, you will need to rerun at least one of these concurrent program sets every time you want to refresh the Oracle iStore Reports data.

Guidelines for Complete Mode

When run in complete mode, the program refreshes data for the entire time-frame specified and provides completely updated, accurate data; however, it may be very time consuming due to the amount of data refreshed. If you require a less time-consuming refresh, use the increment mode.

Keep the following guidelines in mind:

The beginning date of this program set is controlled by:

The table below summarizes how the system determines the beginning date when running the concurrent program in complete mode.

Complete Mode Begin Date Selection Summary
Profile: IBE:YTD Data Avail. in Bins Profile: IBE: Quarter Begin Data Begin Date Selection
If set to Yes Value irrelevant if YTD Data Availability in Bins is set to Yes System uses the earlier of the user-entered date and the first day of the current Oracle General Ledger year. Defaults to first day of the current year if user does not enter a begin date.
If set to No If set to Yes System uses the earlier of the user-entered date and the first day of the current Oracle General Ledger quarter. Defaults to the first day of the current quarter if user does not enter a begin date.
If set to No If set to No System uses the earlier of the user-entered date and the system date. Defaults to the system date if user does not enter a begin date.

Guidelines for Increment Mode

When run in increment mode, the program adds only data for the time period between the last data refresh and your new end date. It is much faster than complete mode, but may not be accurate if the unrefreshed data has changed since it was last captured.

Keep the following guidelines in mind:

The table below summarizes how the system determines which beginning date to use when running the concurrent program in increment mode.

Increment Mode Begin Date Selection
Condition Begin Date Selection
The previous increment data refresh was successful for the fact tables. End date of the previous increment data refresh
The previous increment data refresh was not successful for the fact tables. Begin date of the previous increment data refresh
You are running the program for the first time. End date of the most recent successful complete data refresh

Step 6 - Verify Discoverer Implementation

Verify the implementation of Oracle Discoverer for Oracle iStore:

  1. If you imported the eex files using adupdeul.sh, go to Oracle Discoverer 4i Admin Edition and log in with apps user ID to validate the folders. Example: If the applications user ID is ibe_admin, then log in to Discoverer Admin edition with this user ID and give the responsibility as iStore Administrator.

  2. After you log in with the apps user ID, open the Business area, then select View, Validate Folders to verify the Business Area E-Commerce Report.

Understanding Oracle iStore Reports Architecture

The iStore Reports Fact Tables Refresh concurrent program pulls data that is within a certain user-specified time frame from Oracle Order Capture and Oracle Order Management data tables into Oracle iStore fact tables, which act as data summaries. This process can be very time consuming, depending on how much data the tables hold and the extent of the time frame you specify. The reports and queries that comprise Oracle iStore reports are run against these fact tables.

The iStore Alert Reports concurrent program triggers delivery of the updated Top 10 Orders Report and Historical Summary Report as e-mail notifications.

The reports and queries that comprise Oracle iStore reports are run against these fact tables. The following table describes the fact tables for Oracle iStore Reports.

Fact Tables for Oracle iStore Reports
Fact Table Name Description
IBE_ECR_MVLOG This table captures the refresh log history, as well as the begin date and end date of the transaction records that are refreshed into the base materialized views.
IBE_ECR_ORDER_HEADERS_FACT This table provides header-level information for orders from Oracle Order Management tables. The orders are placed for sources that are specified in the lookup iStore Order Facts Sources (IBE_ECR_ORDER_SOURCE).
IBE_ECR_ORDERS_FACT This table provides information from Oracle Order Management tables about the sale and quantity of items in orders, including data for Product, Customer, and Contracts.
IBE_ECR_QUOTES_FACT This table provides information from Oracle Order Capture tables about items in quotes and shopping carts.

The basic architecture for Oracle iStore Reports is shown in the following figure.

Oracle iStore Reports Architecture

the picture is described in the document text

Oracle iStore Reports Lookup Types

The Oracle iStore Reports lookup types are iStore Order Facts Sources (IBE_ECR_ORDER_SOURCE) and iStore Reporting Currencies (IBE_ECR_REPORTING_CURRENCY).

The lookup iStore Order Facts Sources contains all reportable order sources. The Oracle iStore Reports use information for the orders that are placed for these sources. The lookup iStore Reporting Currencies contains the currency codes that are available for the Data Out Bins.

See the chapter, Implementing Messages and Prompts, for information about adding lookup codes to these lookup types.