We will add a new report under the SLA tab called TAS SLA report, which will include the following columns:

• Request ID
• Assignee(s) of the CONFIRM_SOLUTION task (multiple if applicable)
• Service type (e.g., MPLS, FBB, SIP etc…)
• Global SLA (in days)
• Total SLA (in days)
• Task Name

Each record will show the duration from the activation date of the CONFIRM_SOLUTION task to the completion of the SOLUTION_ACCEPTANCE task.
The time between these tasks represents the total SLA for TAS, displayed in days (e.g., 2.4 days).
For sure the Total SLA will take into consideration the working hours and weekend / Holidays as configured in SRD.

Note: We will modify the current workflow for fixed services to make CONFIRM_SOLUTION the default task after SELECT_SOLUTION. If SSE wishes to bypass this task, they can select the ‘Skip TAS Confirmation’ option in the Service Resource panel.

WL : 5 mday

starting date: 16-12-2024
actual WL: 6.5 days.

Dears,

This CR is implemented.
Patch required (3.7.0.1238).

Kindly find the attached process to be uploaded.

Dear @integration,

Kindly apply the following scripts on staging schema. Note that they also exist in patch 3.7.0.1238.

Ensure to restart SRD only after applying the queries.

Query 1:

CREATE TABLE SRD_CUSTOM_SLA (
ID NUMBER(19) NOT NULL,
REQUEST_ID VARCHAR2(100),
TASK_NAME VARCHAR2(200),
TEAM VARCHAR2(100),
ASSIGNEE VARCHAR2(100),
SLA_DAY NUMBER(10),
DURATION_DAY FLOAT(126),
INSERTDATE DATE,
CONSTRAINT PK_New_Table_Name PRIMARY KEY (ID)
);

Query 2:

alter table srd_task add ASSIGNEE_HISTORY VARCHAR2(255);

Query 3:

alter table srd_task add VISIBLE NUMBER DEFAULT 1;

Regards,
Sara

SLA Tasks Report is the new SLA report.

The customer is requesting to add the below 2 columns to the new SLA Task Report:

the start date when we received this request and end date when we submitted the final solution after going through the workflows.

Dears,

The requested is implemented.
“Request Type”, “Start Task Time”, “End Task Time” are added as shown below.

Patch Required (3.7.0.1244).

Dear @integration,

Kindly apply the following script on staging schema after applying the patch. Note that it also exists in patch 3.7.0.1244.

Query:

ALTER TABLE SRD_CUSTOM_SLA
ADD ("START_TASK_TIME" DATE,
"END_TASK_TIME" VARCHAR2(200 BYTE),
"REQUEST_TYPE" VARCHAR2(100 BYTE));

Note: In the new columns of time (Start/End Task Time), T: Is A literal character that separates the date (2024-12-12) from the time (12:54:24). It simply indicates that the part after T is the time.

+ check the calculation of the total duration after configuring both working days and hours

L3 WL : 4 md

Dear @Salem Dannawi
Please make sure that the attached processes are uploaded on staging and prod (SRD).

Dear @integration,

Kindly find below the queries to be applied by order on the two schemas (staging - prod):

Query 1:

CREATE SEQUENCE SEQ_SRD_TASK
START WITH -1
INCREMENT BY -1
NOMAXVALUE CACHE 100 ORDER;

Query 2:

CREATE SEQUENCE SEQ_SRD_REQUEST_EVENT
START WITH -1
INCREMENT BY -1
NOMAXVALUE CACHE 100 ORDER;

Query 3:

INSERT INTO SRD_TASK (ID, REQUEST_ID, TASK_NAME, TASK_STATUS, TEAM, VISIBLE, INSERTDATE, CHANGEDATE, COMPLETION_DATE)
SELECT
SEQ_SRD_TASK.NEXTVAL,
req.request_id,
'CUSTOM_TAS',
'ACTIVE',
'TAS',
0,
tas_confirm.completion_date,
tas_solution.completion_date,
tas_solution.completion_date
FROM SRD_SERVICE_REQUEST req
JOIN SRD_TASK tas_confirm
ON req.request_id = tas_confirm.request_id
AND tas_confirm.TASK_NAME = 'CONFIRM_SOLUTION'
AND tas_confirm.TASK_STATUS = 'COMPLETED'
JOIN SRD_TASK tas_solution
ON req.request_id = tas_solution.request_id
AND tas_solution.TASK_NAME = 'SOLUTION_ACCEPTANCE'
AND tas_solution.TASK_STATUS = 'COMPLETED'

WHERE tas_confirm.ID IS NOT NULL
AND tas_solution.ID IS NOT NULL
and NOT EXISTS (
SELECT *
FROM SRD_TASK tas
WHERE req.request_id = tas.request_id and tas.TASK_NAME = 'CUSTOM_TAS'
);

Query 4:

INSERT INTO SRD_TASK (ID, REQUEST_ID, TASK_NAME, TASK_STATUS, TEAM, VISIBLE, INSERTDATE, CHANGEDATE, COMPLETION_DATE)
SELECT
SEQ_SRD_TASK.NEXTVAL,
req.request_id,
'CUSTOM_TAS',
'ACTIVE',
'TAS',
0,
tas_confirm.completion_date,
tas_solution.completion_date,
tas_solution.completion_date
FROM SRD_SERVICE_REQUEST req
JOIN SRD_TASK tas_confirm
ON req.request_id = tas_confirm.request_id
AND tas_confirm.TASK_NAME = 'CONFIRM_SOLUTION'
AND tas_confirm.TASK_STATUS = 'COMPLETED'
JOIN SRD_TASK tas_solution
ON req.request_id = tas_solution.request_id
AND tas_solution.TASK_NAME = 'SOLUTION_ACCEPTANCE'
AND tas_solution.TASK_STATUS <> 'COMPLETED'
WHERE
tas_confirm.ID IS NOT NULL
AND
tas_solution.ID IS NOT NULL
and NOT EXISTS (
SELECT *
FROM SRD_TASK tas
WHERE req.request_id = tas.request_id and tas.TASK_NAME = 'CUSTOM_TAS'
);

Query 5:

INSERT INTO SRD_TASK (ID, REQUEST_ID, TASK_NAME, TASK_STATUS, TEAM, VISIBLE, INSERTDATE, CHANGEDATE, COMPLETION_DATE)
SELECT
SEQ_SRD_TASK.NEXTVAL,
req.request_id,
'CUSTOM_TAS',
'ACTIVE',
'TAS',
0,
tas_confirm.completion_date,
tas_confirm.completion_date,
tas_confirm.completion_date
FROM SRD_SERVICE_REQUEST req
JOIN SRD_TASK tas_confirm
ON req.request_id = tas_confirm.request_id
AND tas_confirm.TASK_NAME = 'CONFIRM_SOLUTION'
AND tas_confirm.TASK_STATUS = 'COMPLETED'
WHERE
tas_confirm.ID IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM SRD_TASK tas
WHERE req.request_id = tas.request_id
AND tas.TASK_NAME = 'SOLUTION_ACCEPTANCE'
)
and NOT EXISTS (
SELECT *
FROM SRD_TASK tas
WHERE req.request_id = tas.request_id and tas.TASK_NAME = 'CUSTOM_TAS'
);

Query 6:

INSERT INTO SRD_TASK (ID, REQUEST_ID, TASK_NAME, TASK_STATUS, TEAM, VISIBLE, INSERTDATE, CHANGEDATE, COMPLETION_DATE)
SELECT
SEQ_SRD_TASK.NEXTVAL,
req.request_id,
'CUSTOM_TAS',
'ACTIVE',
'TAS',
0,
tas_confirm.completion_date,
tas_confirm.completion_date,
tas_confirm.completion_date
FROM SRD_SERVICE_REQUEST req
JOIN SRD_TASK tas_confirm
ON req.request_id = tas_confirm.request_id
AND tas_confirm.TASK_NAME = 'CONFIRM_SOLUTION'
AND tas_confirm.TASK_STATUS <> 'COMPLETED'
WHERE
tas_confirm.ID IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM SRD_TASK tas
WHERE req.request_id = tas.request_id
AND tas.TASK_NAME = 'SOLUTION_ACCEPTANCE'
)
and NOT EXISTS (
SELECT *
FROM SRD_TASK tas
WHERE req.request_id = tas.request_id and tas.TASK_NAME = 'CUSTOM_TAS'
);

Query 7:

INSERT INTO SRD_REQUEST_EVENT (
ID, RECORD_TYPE, REQUEST_ID, REQUEST_TYPE, TASK_NAME, TEAM,
START_TIME, END_TIME, START_EVENT, END_EVENT, PROCESS_ID, ACTIVE
)
SELECT
SEQ_SRD_REQUEST_EVENT.NEXTVAL,
'TASK' AS RECORD_TYPE,
tas.REQUEST_ID,
evt.REQUEST_TYPE,
tas.TASK_NAME,
tas.TEAM,
tas.INSERTDATE,
tas.COMPLETION_DATE,
'TASK_CREATED' AS START_EVENT,
'TASK_COMPLETED' AS END_EVENT,
evt.PROCESS_ID,
1 AS ACTIVE
FROM SRD_TASK tas
LEFT JOIN (
SELECT *
FROM SRD_REQUEST_EVENT
WHERE RECORD_TYPE = 'REQUEST'
) evt
ON tas.REQUEST_ID = evt.REQUEST_ID
WHERE tas.TASK_NAME = 'CUSTOM_TAS'
and not exists (
SELECT *
FROM SRD_TASK tas
WHERE evt.request_id = tas.request_id and evt.TASK_NAME = tas.TASK_NAME)
;
Commit;

1.5 wd

After the deployment of patch 3.7.0.1268, the assignee column of the CUSTOM_TASK_FIXED tasks will be filled.

Kindly note that the old data will not change. This feature will apply only to new data.

@salem , plz validate /confirm below 2 bugs are solved and update the ticket status accordingly

• Task SLA report should cover open and closed requests
• Task SLA report custom task should reflect the users who participated in the request from TAS team

we will need to update the old data as well with new custom_task_fixed name via script

fixed as per internal testing , awaiting customer final feedback , to be followed by with other SLA ticket