How does the milestones and milestone_instances tables work?
Environment:
February 2015 Console
Resolution:
If you recently upgraded to the February 2015 version, you will be able to view canned milestones "Resolution Due" for an incident after a new site is created or upgraded.
For SLAs that have response times defined, you can have resolution due dates and times calculated automatically. Times are calculated when an SLA instance is added to an incident.
To create the incident milestones report you can follow the below steps:
1. Create a new report with the following column definitions.
|
Headingg
|
Definition
|
|
Initial Response Status |
if(incidents.status_type=2,100,round(((date_diff(sysdate(), |
|
Initial Response Due |
incidents.rel_due |
|
Time Remaining |
if(incidents.status_type=2,0,round(((date_diff(incidents.rel_due, sysdate())))/60,0)) |
|
Resolution Due Status |
round(((date_diff(sysdate(), milestone_instances.due_date))*-1)/60,0) |
|
Resolution Due |
milestone_instances.due_date |
|
Time Remaining |
if(incidents.status_type=2,0,round(((date_diff |
2. Use the following conditional formatting for the Initial Response Status and Resolution Due Status columns.
|
Condition
|
Display
|
Image
|
Color
|
|
Greater than 15 |
Image Only |
Flag |
Green |
|
Less than or equals 0 |
Image Only |
Flag |
Red |
|
Less than or equals 15 |
Image Only |
Flag |
Yellow |
3. Use the Minutes (Days, Hours, Minutes) format for both Time Remaining columns.
4. Set the following report options to refresh the data regularly:
- Refresh report when data shown is changed in the editor
- Automatically refresh report at regular interval > Auto-Refresh enabled by default > Interval 55 Seconds
5. Clear all of the check boxes in the Customize Record Commands window so that no commands will be available at runtime.
6. Click the Save and Close button to save the custom report and exit the report design center.
Technical Architecture:
Table ‘milestones’
milestone_id – Primary key for this table. There would be one canned milestone currently, which would be ‘Initial Resolution Due’.
Name – The name of the milestone. This would be unique. In this release only one canned milestone would be created, i.e. ‘Initial Resolution Due’. When we support custom milestones a new canned milestone ‘Initial Response Due’ would be also added. These two milestones would be applicable to all SLAs.
Table 'milestone_instances'
i_id – The incident ID this record is associated too. Foreign key refers i_id from incidents table. This is a composite primary key with milestone_id.
milestone_id - The milestone ID for this record. This is a foreign key referring milestone_id in milestones table. One incident can have only one type of milestone associated. This is a composite primary key with i_id.
due_date - This would store the due_date for the milestone. In this release it would only store resolution due date time value for the incident. It would be updated based on algorithm currently used for initial_response_due field on table ‘incidents’. This would be a read-only field for clients (CX/MAPI/CP).
For more information, refer to the Configuring resolution due calculations section in online documentation for the version your site is currently running. See also Create the incident milestones report. To access Oracle B2C Service manuals and documentation online, refer to the Documentation for Oracle B2C Service Products.
