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 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:
-
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.
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 < 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:
-
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 < 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
-
fixedheader—filters order data based on fixed headers
See "Generating Reports Based on Header Fields" for more information.
-
orderdata—filters order data based on specific values of data elements
See "Generating Reports Based on Order Data" for more information.
-
starttime—the start time for the Order History Report
See "Generating Order History Reports Based on Date" for more information.
-
endtime—the end time for the Order History Report
See "Generating Order History Reports Based on Date" for more information.
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 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. |