This chapter describes how to generate reports by using the Oracle Communications Order and Service Management (OSM) Reporting Interface.
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.
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.
When generating reports from stored procedures, you can filter results by providing values for the following parameters:
Use the a_view_mnemonic parameter to generate reports based on OSM views. See "Generating Reports Based on OSM Views" for more information.
Use the a_fixed_header_filter parameter to generate reports based on order data. See "Generating Reports Based on Header Fields" for more information.
Use the a_order_data_filter parameter to generate reports based on data values. See "Generating Reports Based on Order Data" for more information.
Use the a_history_start_time and a_history_end_time parameters to generate reports based on their start and end times. Use this parameter only for the Order History report. See "Generating Order History Reports Based on Date" for more information.
The a_view_mnemonic parameter is an Oracle Communications Order and Service Management (OSM) view 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.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
Completed orders only:
completion_date < 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 |
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.
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.
You generate the following reports from views:
Processes Report
Tasks Report
Notifications Report
To reduce the impact of reporting on system performance, use filters to limit the scope of the 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')
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')
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')
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 OSM_home/SDK/Reporting/ant directory (where OSM_home is the directory in which the OSM software is installed).
To generate reports from the command line:
Edit the provReport.xml file in the OSM_home/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 usingcompletion_date < sysdate
to indicate completed orders, in the XML file this would be written
completion_date < 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:
For example: report="PROCESSES" Names are not case sensitive. |
Mandatory |
view |
The mnemonic for the OSM view (not Oracle view) that is the basis for the order data returned. For example: view="Amend_A_Task" |
Optional, applicable only to reports implemented as stored procedures:
|
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 OSM_home/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:
|
database |
The database connection, specified by:
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 |
Type the following command to encrypt the OSM Reporting Interface schema password:
EncryptPasswords.sh
The Enter user name for database connection prompt appears.
Enter the user name for the OSM Reporting Interface schema.
The Enter password for database connection prompt appears.
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.
Enter the full path including the provReport.xml file. For example:
/myosm73folder/SDK/Reporting/ant/provReport.xml
At the command line, go to directory OSM_home/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 OSM_home/SDK/reporting/ant directory.
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. |