Skip Navigation
Expand
Milestones and Milestone_Instances tables
Answer ID 7742   |   Last Review Date 11/18/2018

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.

Custom Incident Milestones Report Columns 
Headingg
Definition

Initial Response Status

if(incidents.status_type=2,100,round(((date_diff(sysdate(),
incidents.rel_due))*-1)/60,0))

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
(milestone_instances.due_date,sysdate())))/60,0))


2. Use the following conditional formatting for the Initial Response Status and Resolution Due Status columns.

Conditional Formatting for Status Flags 
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.

Notify Me
The page will refresh upon submission. Any pending input will be lost.