Implementing Reports

This chapter covers the following topics:

• Overview of Implementing Reports Chapter
• Overview of Oracle iStore Reports
• Oracle iStore Operational Reports
• Implementing Oracle iStore Reports
• Understanding Oracle iStore Reports Architecture
• Oracle iStore Reports Lookup Types

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:

• Web visitor trends, including such metrics as visit duration, opt-outs, and page views

• Web visitors converted to customers

• Web product interest, including number of product and product category views

• Web customer acquisitions and activity, including top ordering customers and customer activity by customer classification

• Web campaign analyses, featuring direct integration with Oracle Marketing and Oracle Interaction History

• Web page interest reporting on Oracle iStore and external site page views, page view duration and the like

• Top selling Web products

• Number of carts created over time, and the number and amounts of carts converted to orders

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

Operational reports present the following data:

• Product assignment and published/unpublished status reports

• Section assignment and published/unpublished status reports

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:

• Unassigned Products Report

• Assigned Products Report

• Products by Site Report

• Products Excluded by Site Report

• Sections With Site Assignment Report

• Sections Without Site Assignment Report

• Sections With Site Exclusion Report

• Empty Sections Report

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:

• For all product reports, the Inventory Organization is a parameter, as defined by the profile option, IBE: Item Validation Organization.

• Oracle iStore only supports products having the Web Status (item Inventory flag) set to Published or Unpublished. Therefore, product reports always include only products having the web status set to those values; the reports will not include items having the Web Status set to Disabled or null.

• The product reports make no distinction for items based on their Orderable on the Web status.

• For configurable products (Assemble to Order Configuration), during the order processing, a new item is created during the order execution. Example: AT68331*109 – Envoy Assemble to Order. This inventory item is a configuration that will be never exposed in the Site Administration UI product functionality. Therefore, this type of item will not be exposed in the product reports. However, the base model may still appear in the products reports.

• The profile option, IBE: Category Set, determines which Inventory category set's products are available for reporting purposes.

• Product field names in Oracle iStore are not an exact match with the Oracle Inventory forms field names.

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:

• Find newly created products which have not yet been assigned to any section

• Find all unpublished or published products that are unassigned to a section

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:

• Find all products assigned to a section but in unpublished status

• List products from a specific categories assigned to at least one section (either published or unpublished products

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:

• To be included in a site, a product has to be associated with one or more sections. This means that only products already assigned to sections will be displayed in this report. If a product is not included in any site, the product is never displayed in that report.

• The report displays --- for each product and by site --- which sections products are assigned to and which products are included in sites. The section publish flag is also displayed. But even if the section flag is Published and the Product publish flag is Published, it doesn't mean the product will show up in the Customer UI. The parent section of the section can have an Unpublished status. The report doesn't take in account this exception case. Also, a product can be assigned to one more many sections, but the product is excluded from all the sites where those sections are included. In that case, the product doesn't appear in the report, since the product is not going to appear under any section in the Site Administration UI.

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

• The user is able to roll up this report at the product level (to display products without any section mapping column).

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:

• The section has no children (sections or products); OR

• All the children of the section are unpublished; OR

• All the children have a start and end date which are not current; OR

• All the children of the section are excluded from all the sites in which the section is included.

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:

• Oracle General Ledger Calendar

• Oracle Order Capture and Oracle Order Management data tables

• Oracle iStore concurrent programs

• Oracle iStore Order Facts Sources lookup types

• Oracle Discoverer Viewer

• Oracle Workflow

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

• Step 1 - Set up Calendar in General Ledger

• Step 2 - Set up Oracle Discoverer

• Step 3 - Set Profile Options

• Step 4 - Set Conversion Rates

• Step 5 - Run Concurrent Program

• Step 6 - Verify Discoverer Implementation

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

   • Create/Edit Query

   • Collect Query Statistics

   • Item Drill

   • Drill Out

   • Grant Workbook

   • Save workbooks to database

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:

   • User Name: ibe_admin: (Note the ":"at the end of the apps username.)

   • Password: <enter the appropriate password>

   • Database: <database> For example: CustomDB

   • Language: <select from language list>

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

   Ensure that you are connected to the correct EUL:

   • Select the Options menu.

   • Check the current EUL from the End User Layer Access section.

   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

• IBE: Enable Parallel Data Extraction for Reporting - Set to No at the iStore application level

  Caution: Never set the profile option, IBE: Enable Parallel Data Extraction for Reporting, to Yes.

• IBE: Currency Code - Set as indicated in "Profile Options" appendix

• IBE: GL Conversion Type - Set as indicated in "Profile Options" appendix

• IBE: GL Period Set Name - Set as indicated in "Profile Options" appendix

Optional Profile Options

• IBE: Enable Force Refresh

• IBE: Quarter Begin Data

• IBE: Truncate Records

• IBE: YTD Data Availability in Bins

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:

• You can set the profile options, IBE: Currency Code and IBE: GL Conversion Type, before or after you set the daily currency conversion rate, to complete currency setup for the Oracle iStore Reports fact tables.

• There should be a line for every currency conversion that you need, for every date for which the Oracle iStore Reports will request data.

• The From fields should have the currencies in which your sites provide transactions.

• The To fields should contain the value for the currency that you want the fact tables to use. This value is the same as the setting of the profile option, IBE: Currency Code.

• The values in the Conversion Type fields should equal the value of the profile option IBE: GL Conversion Type.

• You can enter the currency conversions one line at a time, or select Enter by Date Range. . . to enter the currency conversions for a date range at one time.

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:

• Prerequisites: Perform the previously listed implementation steps: "Set up Oracle Discoverer", "Set Profile Options", and "Set Conversion Rates"

• Set the parameters as follows:

  • Refresh Mode: Complete

  • Begin Date: The start date for the time period for which data should be pulled from the Oracle Order Capture and Oracle Order Management tables.

  • End Date: The end date for the time period. If you leave this blank, it defaults to the current system date.

  • Conversion Rate Check: If this is set to Yes, the concurrent program will abort whenever the currency rates are not maintained. The details of the missing rates would be logged in the concurrent program log files (up to a maximum of 25 missing rates). If this is set to No, the program will execute with "default" values for the missing conversions. By default, this parameter is set to No.

The beginning date of this program set is controlled by:

• User-entered date

• Profile option: IBE: Quarter Begin Data

• Profile option: IBE: YTD Data Availability in Bins

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:

• Prerequisite: You have run the program in complete mode at least once.

• Set the parameters as follows:

  • Refresh Mode: Increment

  • End Date: The ending date for the time period for which data should be pulled from the Oracle Order Capture and Oracle Order Management tables. If you leave this blank, it defaults to the current system date.

  • Conversion Rate Check: If this is set to Yes, the concurrent program will abort whenever the currency rates are not maintained. The details of the missing rates would be logged in the concurrent program log files (up to a maximum of 25 missing rates). If this is set to No, the program will execute with "default" values for the missing conversions. By default, this parameter is set to No.

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

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.