1. Reporting Interface Guide
  2. Generating OSM Reports

3 Generating OSM Reports

This chapter describes how to generate reports by using the Oracle Communications Order and Service Management (OSM) Reporting Interface.

Guidelines for Running Reports

Note these guidelines for running reports:

  • Columns that report on process duration times are structured to display in days. If the actual duration of a task is sufficiently short (seconds or minutes), the duration time may be reported incorrectly by misplacing the decimal point, which makes the duration appear longer than it really is.

  • For the Orders report and the Pending Order At Tasks report, you must create a reporting view if you want the report to return order data. The number of nodes in this view is limited by the size of the database variable that contains the data (32760 bytes). Various factors can play a role in how quickly the view exceeds this limit, including the number of elements and structures in the view, having multiple instances of elements or structure in the view, or having elements and structures with long mnemonics. Given these factors, the view should not contain a large number of value nodes. For example, you might test between 6 to 90 value nodes. You can also split the reporting views into smaller chunks and collate the data at a later time. This applies to nodes with or without data.

    Note:

    Pending Order At Task reports support multi-instance nodes. Cardinality determines the number of nodes contained in it. For example, if the reporting view has two nodes with cardinality 1 then this will be equal to one multi instance node with cardinality 2.

  • To reduce the impact of reporting on system performance, use filters to limit the scope of the report. For example, configure your report to return orders in a reduced, specific date range, or related to a specific cartridge. See "Using Report Parameters to Filter Results" for more information.

Generating Reports from Stored Procedures

You can generate the following reports:

  • Orders (OMS_REPORT_ORDERS)

  • Order History (OMS_REPORT_ORDERS_HISTORY)

  • Pending Order At Task (OMS_REPORT_PENDING_AT_TASKS)

Refer to the documentation of the your reporting application for instructions on generating reports from stored procedures. Typically, you will need to choose a report, a stored procedure, a database connection, and the configuration of any parameters to customize the report output.

Using Report Parameters to Filter Results

When generating reports from stored procedures, you can filter results by providing values for the following parameters:

Generating Reports Based on OSM Views

The a_view_mnemonic parameter is an OSM view (query task) mnemonic that contains a list of nodes that the report returns. Only value nodes are included in the report.

When you use the a_view_mnemonic parameter, the search is performed on all cartridges unless you use the a_fixed_header_filter parameter to restrict the search to certain cartridges. See "Generating Reports Based on Header Fields" for more information.

Note:

If different cartridges have identical views, both with the same mnemonic, the views are amalgamated and subsequently the fields appear duplicated in the report.

Generating Reports Based on Header Fields

The a_fixed_header_filter parameter is a filter condition that you can apply to the search using the fixed header fields, such as order ID, task, and process status. See Table 3-1 for more information.

You can restrict the report to return data from a specific cartridge. If the cartridge contains the view, the corresponding values are returned, otherwise the report returns empty values. All order data is returned if the cartridge value is null.

You can combine fields using logical operators OR and AND, and use parenthesis to group information. You can also apply relational operators such as =, !=, <, <=, > and >= . However, the following fields can use only = and !=:

  • Process

  • Order type

  • Order source

  • Process status

  • Cartridge

You can also query on the following columns using between as an operator to specify a range of values:

  • Order ID

  • Order creation date

  • Order start date

  • Expected order completion date

  • Order completion date

Examples

Completed orders only: 

completion_date &lt; sysdate

Orders completed in the last 30 days: 

completion_date between sysdate-30 and sysdate

Pending orders: 

completion_date > sysdate

Orders that were created between specified dates: 

order_creation_date between to_date(2008.11.01:00:00:00) and to_date(2008.11.30:23:59:59)

Orders created in the last week:

completion_date between sysdate -7 and sysdate

Orders from multiple specified cartridges:

Cartridge=(dsl, *) OR cartridge=(default, 1.0) OR cartridge=(default, 2.0)

Table 3-1 Filter Values for the a_fixed_header_filter Parameter

Field Name Description Orders/Order History Reports Pending Order At Tasks Report

order_id

The order sequence ID number.

Y

Y

ref_num

The reference number.

Y

Y

process

The process mnemonic.

Y

Y

order_type

The order type mnemonic.

Y

Y

order_source

The order source mnemonic.

Y

Y

task

The task mnemonic.

N

Y

state

The state mnemonic.

N

Y

process_status

The process status mnemonic.

Note: If N/A appears in the process_status, there is no process status for the order.

Y

Y

user

The user name.

N

Y

order_creation_date

Order creation date. The format can be either sysdate, sysdate - offset or to_date.(2011.12.12.11:22:33). The keyword sysdate represents the current date or time. When you specify a fixed date, the input should follow the format as follows: yyyy.mm.dd.hi24:mi:ss, in which yyyy = the year (4 digits), mm = the month (01, 02…12), dd = the day of the month (01, 02…31), hi24 = the hour (in 24 hour format—00, 02…23), mi = the minute (00, 01, 02, 59), ss = the second (00, 01, 02 … 59).

Y

Y

order_start_date

Order start time: when an order enters the main process. The format can be either sysdate, sysdate - offset or to_date (2011.12.12.11:22:33).

Y

Y

task_start_date

Task start date. The format can be either sysdate, sysdate - offset or to_date (2011.12.12.11:22:33).

N

Y

expected_ completion_date

Expected order completion date. The format can be either sysdate, sysdate +/- offset or to_date(2011.12.12.11:22:33).

Y

Y

completion_date

Actual date when the order is completed in the system. The format can be either sysdate, sysdate - offset or to_date(2011.12.12.11:22:33).

Y

N

task_expected_

completion_date

Expected task completion date. The format can be either sysdate, sysdate +/- offset or to_date (2011.12.12.11:22:33).

N

Y

cartridge

Cartridge = (namespace_mnemonic, version). For example, cartridge = (default, 1.0). If it is not version aware, specify * as the value, example, cartridge = (default, *).

Y

Y
Generating Reports Based on Order Data

The a_order_data_filter parameter is a filter condition that you can apply to the search using one or more order data elements that match specific values. This enables orders to be matched based on their contents. You can combine these elements using the same logical and relational operators as a_fixed_header_filter.

When using a_order_data_filter, you must provide the full mnemonic path of the node to which you are referring.

In the following example, textfield1 is a node in the group1 group: 

/group1/textfield1=some text

Multi-instance nodes can also be specified using the following notation:

/group1/multiinstancenode[2]
/multiinstancegroup[3]/textfield2

In this example, the number in the square brackets indicates the instance number to which you are referring. Instances are counted in the same order as they are added to the order (on screen from left to right and from top to bottom).

Note:

The following conditions apply when filtering order data:

  • Text values cannot contain apostrophes or quotation marks

  • Dates must follow the format yyyy.mm.dd.hh.mi.ss

  • Phone numbers cannot contain parenthesis or dashes (if stored using the Phone data type). For example, use 5556744004 instead of (555)674-4004.

Generating Order History Reports Based on Date

The a_history_start_time and a_history_end_time parameters specify a time interval that you can apply to the search to return order data history for the time specified. These parameters are applicable only to the Order History Report. You can specify either a start time or an end time, or both.

The date format is dependent upon the third-party reporting application you are using.

Generating Reports from Views

You generate the following reports from views:

  • Processes Report

  • Tasks Report

  • Notifications Report

Managing Performance When Generating Reports from Views

To reduce the impact of reporting on system performance, use filters to limit the scope of the report.

Filter Condition Examples for Processes Report

Use these filter conditions for the Processes report.

  • Pending processes:

    completion_time is NULL

  • Completed processes: 

    completion_time is not NULL

  • Processes that were completed between certain dates: 

    completion_time between 
to_date('2011.11.01.00:00:00','yyyy.mm.dd.hh24:mi:ss') and to_date('2011.11.30.23:59:59','yyyy.mm.dd.hh24:mi:ss')
Filter Conditions Examples for Tasks Report

Use these filter conditions for the Tasks report.

  • Pending tasks: 

    timestamp_out is NULL

  • Completed tasks: 

    timestamp_out is not NULL

  • Tasks that were completed between certain dates: 

    timestamp_out between to_date('2011.11.01.00:00:00','yyyy.mm.dd.hh24:mi:ss') and to_date '2011.11.30.23:59:59','yyyy.mm.dd.hh24:mi:ss')
Filter Conditions Examples for Notifications Report

Use these filter conditions for the Notifications report.

  • Notifications that were sent between certain dates: 

    datetime_sent between 
to_date('2011.11.01.00:00:00','yyyy.mm.dd.hh24:mi:ss') and to_date('2011.11.30.23:59:59','yyyy.mm.dd.hh24:mi:ss')

  • Notifications that were acknowledged between certain dates: 

    datetime_acknowledged between 
to_date('2011.11.01.00:00:00','yyyy.mm.dd.hh24:mi:ss') and to_date('2011.11.30.23:59:59','yyyy.mm.dd.hh24:mi:ss')

Generating Reports from the Command Line

The command-line utility is implemented as an Ant task that calls a reporting procedure or accesses a view, and then exports the resulting data to a comma-separated values (CSV) file. The data can also be saved directly as an XML file.

To assist you in configuring the Ant build file, use the provReport.xml file in the SDK/Reporting/ant directory.

To generate reports from the command line:

  1. Edit the provReport.xml file in the SDK/Reporting/ant directory using the parameters described in Table 3-2.

    Note:

    Normal XML character restrictions apply to the contents of the provReport.xml file. For example, rather than using

    completion_date < sysdate

    to indicate completed orders, in the XML file this would be written

    completion_date &lt; sysdate

    to conform with XML formatting rules.

    Table 3-2 Reporting Interface Ant Command-Line Parameters

    Parameter Description Usage

    report

    The legal name of the report. Options are:

    • Orders

    • Pending_At_Tasks

    • Orders_History

    • Tasks

    • Notifications

    • Processes

    For example:

    report="PROCESSES"

    Names are not case sensitive.

    Mandatory

    view

    The mnemonic for the OSM view (query task) that is the basis for the order data returned.

    For example:

    view="Amend_A_Task"

    Optional, applicable only to reports implemented as stored procedures:

    • Orders

    • Pending_At_Tasks

    • Orders_History

    destfile

    The destination file for the output data.

    For example:

    destfile="/myosm73/SDK/Reporting/processesfile"

    If a full path is not provided, the report is created in SDK/Reporting/ant.

    Mandatory

    outputtype

    The format of the output. Valid values are CSV or XML.

    For example:

    outputtype="CSV"

    Mandatory

    whereclause

    Provides the option of adding a where clause to the search in reports implemented as database views. Follows standard SQL; however, omit the initial where.

    For example:

    whereclause="process_mnemonic = 'BillingSI'"

    Optional, applicable only to reports implemented as database views:

    • Tasks

    • Notifications

    • Processes

    database

    The database connection, specified by:

    • datasource—a standard Oracle URL, for example:

      jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=localhost))(CONNECT_DATA=(SID=orcl)))

    • user—the Reporting Interface schema user name

    • password—the Reporting Interface schema user name password. You must use SDK/Reporting/ant/EncryptPasswords.sh in step 2 to encrypt the password.

    For example:

    database datasource="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=
(PROTOCOL=tcp)(PORT=1521)(HOST=localhost))(CONNECT_
DATA=(SID=oracle11)))" user="ordermgmtb1148_reports"
password="password"

    Mandatory

    reportfilter

    For example:

    reportfilter fixedHeader="datetime_sent between to_
date('2011.11.01.00:00:00','yyyy.mm.dd.hh24:mi:ss') 
and to_ 
date('2011.11.30.23:59:59','yyyy.mm.dd.hh24:mi:ss')" 
orderdata="" starttime="" endtime=""

    Optional

    Date format must be:

    yyyy.mm.dd.hh:mi:ss

    hh uses a 24 hour clock

  2. Type the following command to encrypt the OSM Reporting Interface schema password:

    EncryptPasswords.sh

    The Enter user name for database connection prompt appears.

  3. Enter the user name for the OSM Reporting Interface schema.

    The Enter password for database connection prompt appears.

  4. Enter the password for the OSM Reporting Interface schema.

    Note:

    The prompt does not display any characters as you type.

    The Enter Path to ant xml file prompt appears.

  5. Enter the full path including the provReport.xml file. For example:

    /myosm73folder/SDK/Reporting/ant/provReport.xml

  6. At the command line, go to directory SDK/reporting/ant and type the following command: 

    ant -f provReport.xml

    The system displays a successful build message and generates the specified report in the SDK/reporting/ant directory.

Error Messages

Table 3-3 describes some of the errors you may receive when running reports.

Table 3-3 Report Error Message

Error Message Description

SQL error ORA-01789 query block has incorrect number of result columns.

The views have different node structures.

SQL error ORA-01790 expression must have same data type as corresponding expression:

The view has the same node type and structure but in a different position.

SQL error ORA-20901 cannot find view:

View mnemonic supplied cannot be found in any cartridge.

SQL error ORA-20902 errors in executing the reports, please check your search condition.

Search conditions are not valid.

SQL error ORA-20903 cannot find metadata condition supplied:

Search condition contains metadata, for example, process = customer_contact, however process customer_contact cannot be found in metadata.

SQL error ORA-20904 search condition contains syntax errors:

The search condition supplied is not valid, for example, it is missing a closing bracket.