[Home] [Help]
247: l_dt date;
248: cursor c_backlog is
249: select nvl(aud_out.incident_owner_id,-1), nvl(aud_out.group_id,-1),
250: nvl(aud_out.incident_severity_id,-1), count(*)
251: from cs_incidents_audit_b aud_out,
252: cs_incidents_all_b sr
253: where aud_out.incident_resolved_date is null
254: and aud_out.incident_id = sr.incident_id
255: and nvl(sr.incident_resolved_date,sysdate+1000) > l_dt
276: all dates between 11th and 25th may and aut_out.status_flag = 'O'
277: will make it unselected and hence not counted
278: */
279: (select max(incident_audit_id)
280: from cs_incidents_audit_b aud_in
281: where aud_in.incident_id = aud_out.incident_id
282: and aud_in.creation_date < l_dt +1)
283: -- so that full day is taken
284: group by aud_out.incident_owner_id,
382: count(aud.incident_id) resolutions,
383: count(decode(sign(sr.incident_resolved_date-
384: sr.expected_resolution_date),
385: 1,1,null)) resl_sla_missed
386: from cs_incidents_audit_b aud, --this is audit rec for response
387: cs_incidents_all_b sr
388: where sr.incident_id = aud.incident_id
389: and (aud.incident_owner_id is not null or
390: aud.group_id is not null)
389: and (aud.incident_owner_id is not null or
390: aud.group_id is not null)
391: and aud.incident_audit_id =
392: ( select max(incident_audit_id)
393: from cs_incidents_audit_b aud_in
394: where aud_in.incident_id = aud.incident_id
395: and aud_in.creation_date between p_from_date
396: and p_to_date
397: and nvl(aud_in.old_incident_resolved_date,l_dt) <>
416: count(prev_resp.incident_id) resolutions,
417: count(decode(sign(sr.actual_resolution_date-
418: sr.expected_resolution_date),
419: 1,1,null)) resl_sla_missed
420: from cs_incidents_audit_b curr_resp,
421: --this is audit rec for response in curr run dates
422: cs_incidents_audit_b prev_resp,
423: -- this is response in before curr run dates
424: cs_incidents_all_b sr
418: sr.expected_resolution_date),
419: 1,1,null)) resl_sla_missed
420: from cs_incidents_audit_b curr_resp,
421: --this is audit rec for response in curr run dates
422: cs_incidents_audit_b prev_resp,
423: -- this is response in before curr run dates
424: cs_incidents_all_b sr
425: where sr.incident_id = prev_resp.incident_id
426: and curr_resp.incident_audit_id =
424: cs_incidents_all_b sr
425: where sr.incident_id = prev_resp.incident_id
426: and curr_resp.incident_audit_id =
427: ( select max(incident_audit_id)
428: from cs_incidents_audit_b aud_in
429: where aud_in.incident_id = curr_resp.incident_id
430: and aud_in.creation_date between p_from_date
431: and p_to_date
432: and nvl(aud_in.old_incident_resolved_date,l_dt) <>
439: )
440: -- above query will insure that selected response is the last response
441: and prev_resp.incident_id = curr_resp.incident_id
442: and prev_resp.incident_audit_id = ( select max(incident_audit_id)
443: from cs_incidents_audit_b aud_in1
444: where aud_in1.incident_id = curr_resp.incident_id
445: and aud_in1.creation_date < p_from_date
446: and nvl(aud_in1.old_incident_resolved_date,l_dt) <>
447: nvl(aud_in1.incident_resolved_date,l_dt)
506: decode(aud.incident_audit_id,to_dttm.incident_audit_id,
507: aud.incident_resolved_date,to_dttm.creation_date)
508: -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),0))
509: ) * 1440 wait_on_agent
510: from cs_incidents_audit_b aud , --this is audit rec for response
511: cs_incidents_audit_b to_dttm , -- to date time
512: cs_incidents_audit_b from_dttm, -- from date time
513: cs_incident_Statuses_b to_stat
514: /* the pair of from_dttm to to_dttm will give the durating in which an agent
507: aud.incident_resolved_date,to_dttm.creation_date)
508: -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),0))
509: ) * 1440 wait_on_agent
510: from cs_incidents_audit_b aud , --this is audit rec for response
511: cs_incidents_audit_b to_dttm , -- to date time
512: cs_incidents_audit_b from_dttm, -- from date time
513: cs_incident_Statuses_b to_stat
514: /* the pair of from_dttm to to_dttm will give the durating in which an agent
515: owned a serveice request.
508: -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),0))
509: ) * 1440 wait_on_agent
510: from cs_incidents_audit_b aud , --this is audit rec for response
511: cs_incidents_audit_b to_dttm , -- to date time
512: cs_incidents_audit_b from_dttm, -- from date time
513: cs_incident_Statuses_b to_stat
514: /* the pair of from_dttm to to_dttm will give the durating in which an agent
515: owned a serveice request.
516: cs_incidents_all_b sr -- only for incident_date */
515: owned a serveice request.
516: cs_incidents_all_b sr -- only for incident_date */
517: where aud.incident_audit_id =
518: ( select max(incident_audit_id)
519: from cs_incidents_audit_b aud_in
520: where aud_in.incident_id = aud.incident_id
521: and aud_in.creation_date between p_from_date
522: and p_to_date
523: and nvl(aud_in.old_incident_resolved_date,l_dt) <>
547: or nvl(from_dttm.old_incident_status_id,-1) <>
548: nvl(from_dttm.incident_status_id,-1))
549: */
550: and from_dttm.incident_audit_id =
551: (select max(incident_audit_id) from cs_incidents_audit_b x
552: where x.incident_id = aud.incident_id
553: and ((nvl(x.old_incident_owner_id,-1) <>
554: nvl(x.incident_owner_id,-1) or
555: nvl(x.old_incident_status_id,-1) <>
607: decode(aud.incident_audit_id,to_dttm.incident_audit_id,
608: aud.incident_resolved_date,to_dttm.creation_date)
609: -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),0))
610: ) * 1440 wait_on_agent
611: from cs_incidents_audit_b curr_resp,
612: cs_incidents_audit_b aud , --this is audit rec for prior resolutions
613: cs_incidents_audit_b to_dttm , -- to date time
614: cs_incidents_audit_b from_dttm, -- from date time
615: cs_incident_statuses_b to_Stat
608: aud.incident_resolved_date,to_dttm.creation_date)
609: -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),0))
610: ) * 1440 wait_on_agent
611: from cs_incidents_audit_b curr_resp,
612: cs_incidents_audit_b aud , --this is audit rec for prior resolutions
613: cs_incidents_audit_b to_dttm , -- to date time
614: cs_incidents_audit_b from_dttm, -- from date time
615: cs_incident_statuses_b to_Stat
616: /* the pair of from_dttm to to_dttm will give the durating in which an agent
609: -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),0))
610: ) * 1440 wait_on_agent
611: from cs_incidents_audit_b curr_resp,
612: cs_incidents_audit_b aud , --this is audit rec for prior resolutions
613: cs_incidents_audit_b to_dttm , -- to date time
614: cs_incidents_audit_b from_dttm, -- from date time
615: cs_incident_statuses_b to_Stat
616: /* the pair of from_dttm to to_dttm will give the durating in which an agent
617: owned a serveice request.
610: ) * 1440 wait_on_agent
611: from cs_incidents_audit_b curr_resp,
612: cs_incidents_audit_b aud , --this is audit rec for prior resolutions
613: cs_incidents_audit_b to_dttm , -- to date time
614: cs_incidents_audit_b from_dttm, -- from date time
615: cs_incident_statuses_b to_Stat
616: /* the pair of from_dttm to to_dttm will give the durating in which an agent
617: owned a serveice request.
618: cs_incidents_all_b sr -- only for incident_date */
617: owned a serveice request.
618: cs_incidents_all_b sr -- only for incident_date */
619: where aud.incident_audit_id =
620: ( select max(incident_audit_id)
621: from cs_incidents_audit_b aud_in
622: where aud_in.incident_id = curr_resp.incident_id
623: and aud_in.creation_date < p_from_date
624: and nvl(aud_in.old_incident_resolved_date,l_dt) <>
625: nvl(aud_in.incident_resolved_date,l_dt)
630: -- responded condition.
631: )
632: and curr_resp.incident_audit_id =
633: ( select max(incident_audit_id)
634: from cs_incidents_audit_b aud_in
635: where aud_in.incident_id = curr_resp.incident_id
636: and aud_in.creation_date between p_from_date
637: and p_to_date
638: and nvl(aud_in.old_incident_resolved_date,l_dt) <>
664: or nvl(from_dttm.old_incident_status_id,-1) <>
665: nvl(from_dttm.incident_status_id,-1))
666: */
667: and from_dttm.incident_audit_id =
668: (select max(incident_audit_id) from cs_incidents_audit_b x
669: where x.incident_id = aud.incident_id
670: and ((nvl(x.old_incident_owner_id,-1) <>
671: nvl(x.incident_owner_id,-1) or
672: nvl(x.old_incident_status_id,-1) <>
963: nvl(first_rslvd.resolution_code ,''-1'') resolution_code ,
964: count(first_rslvd.incident_id) sr_resolved ,
965: 0 sr_reopened,
966: 0 sr_reopened2
967: from cs_incidents_audit_b first_rslvd,
968: cs_incidents_all_b sr
969: where sr.incident_id = first_rslvd.incident_id
970: and first_rslvd.incident_owner_id is not null
971: -- so that only those rec are selected where resolution date is
975: and first_rslvd.incident_resolved_date is not null
976: -- select only resloutions in a given period
977: and first_rslvd.creation_date between :p_from_date and :p_to_date
978: -- select a resolution only if it first time resolution
979: and not exists (select 1 from cs_incidents_audit_b x
980: where x.incident_resolved_date is not null
981: and nvl(x.incident_resolved_date, :l_dt) <>
982: nvl(x.old_incident_resolved_date, :l_dt)
983: and x.incident_id = first_rslvd.incident_id
1026: decode(count(prev_unrsltns.old_incident_resolved_date),0,0, 1) rework2
1027: /* 1 mean there are atleast two reopen, 1 for last_unrslvd and
1028: 1 from prev_unrsltns
1029: it will return 1 only if there are atleast 2 old close dates */
1030: from cs_incidents_audit_b last_unrslvd,
1031: cs_incidents_audit_b prev_unrsltns,
1032: cs_incidents_all_b sr
1033: where sr.incident_id = last_unrslvd.incident_id
1034: and last_unrslvd.incident_owner_id is not null
1027: /* 1 mean there are atleast two reopen, 1 for last_unrslvd and
1028: 1 from prev_unrsltns
1029: it will return 1 only if there are atleast 2 old close dates */
1030: from cs_incidents_audit_b last_unrslvd,
1031: cs_incidents_audit_b prev_unrsltns,
1032: cs_incidents_all_b sr
1033: where sr.incident_id = last_unrslvd.incident_id
1034: and last_unrslvd.incident_owner_id is not null
1035: and last_unrslvd.old_incident_resolved_date is not null
1036: and last_unrslvd.incident_resolved_date is null
1037: /* select only last reopen in a given period */
1038: and last_unrslvd.creation_date between :p_from_date and :p_to_date
1039: and last_unrslvd.incident_audit_id =
1040: (select max(incident_audit_id) from cs_incidents_audit_b x
1041: where x.old_incident_resolved_date is not null
1042: and x.incident_resolved_date is null
1043: and x.incident_id = last_unrslvd.incident_id
1044: and x.incident_owner_id = last_unrslvd.incident_owner_id
1094: decode(count(prev_unrsltns.old_incident_resolved_date),0,0,1) rework,
1095: /* 1 */
1096: decode(count(prev_unrsltns1.old_incident_resolved_date),0,0, 1)reopen
1097: /** it will return 1 only if there are atleast 2 old close dates */
1098: from cs_incidents_audit_b curr_unrslvd,
1099: cs_incidents_audit_b prev_unrsltns,
1100: /* this indicates if a sr is reworked*/
1101: cs_incidents_audit_b prev_unrsltns1,
1102: /* this table indicates if a sr is reworked more than once*/
1095: /* 1 */
1096: decode(count(prev_unrsltns1.old_incident_resolved_date),0,0, 1)reopen
1097: /** it will return 1 only if there are atleast 2 old close dates */
1098: from cs_incidents_audit_b curr_unrslvd,
1099: cs_incidents_audit_b prev_unrsltns,
1100: /* this indicates if a sr is reworked*/
1101: cs_incidents_audit_b prev_unrsltns1,
1102: /* this table indicates if a sr is reworked more than once*/
1103: cs_incidents_all_b sr
1097: /** it will return 1 only if there are atleast 2 old close dates */
1098: from cs_incidents_audit_b curr_unrslvd,
1099: cs_incidents_audit_b prev_unrsltns,
1100: /* this indicates if a sr is reworked*/
1101: cs_incidents_audit_b prev_unrsltns1,
1102: /* this table indicates if a sr is reworked more than once*/
1103: cs_incidents_all_b sr
1104: where sr.incident_id = prev_unrsltns.incident_id
1105: and curr_unrslvd.incident_owner_id is not null
1107: and curr_unrslvd.incident_resolved_date is null
1108: /* select only rework in a given period */
1109: and curr_unrslvd.creation_date between :p_from_date and :p_to_date
1110: and curr_unrslvd.incident_audit_id =
1111: (select max(incident_audit_id) from cs_incidents_audit_b x
1112: where x.old_incident_resolved_date is not null
1113: and x.incident_resolved_date is null
1114: and x.incident_id = curr_unrslvd.incident_id
1115: and x.incident_owner_id = curr_unrslvd.incident_owner_id
1123: and prev_unrsltns.incident_resolved_date is null
1124: and prev_unrsltns.old_incident_resolved_date is not null
1125: and prev_unrsltns.incident_owner_id = curr_unrslvd.incident_owner_id
1126: and prev_unrsltns.incident_audit_id =
1127: (select max(y.incident_audit_id) from cs_incidents_audit_b y
1128: where y.incident_id = prev_unrsltns.incident_id
1129: and y.incident_owner_id = prev_unrsltns.incident_owner_id
1130: and y.creation_date < :p_from_date
1131: and y.incident_resolved_date is null
1296: l_sql_in_sel_r := '
1297: select aud.incident_id,
1298: aud.incident_owner_id';
1299: l_sql_in_whr := '
1300: from cs_incidents_audit_b aud
1301: where /*nvl(aud.incident_owner_id,-1) <> nvl(aud.old_incident_owner_id,-1)
1302: and aud.incident_owner_id is not null
1303: and */ aud.creation_date between :p_from_date and :p_to_date
1304: -- same condition are present in subquery too. it existance of these conditions outside the
1304: -- same condition are present in subquery too. it existance of these conditions outside the
1305: -- subquery is meaningless. remove it when modifying this query.
1306: and aud.incident_audit_id =
1307: (select max(incident_audit_id)
1308: from cs_incidents_audit_b aud_in
1309: where aud_in.incident_id = aud.incident_id
1310: and aud_in.creation_date between :p_from_date
1311: and :p_to_date
1312: and aud_in.incident_owner_id = aud.incident_owner_id
1334: l_sql_out_sel_r := '
1335: select aud.incident_id,
1336: aud.old_incident_owner_id';
1337: l_sql_out_whr := '
1338: from cs_incidents_audit_b aud
1339: where nvl(aud.incident_owner_id,-1) <> nvl(aud.old_incident_owner_id,-1)
1340: and aud.old_incident_owner_id is not null
1341: and aud.creation_date between :p_from_date and :p_to_date
1342: and aud.incident_audit_id =
1340: and aud.old_incident_owner_id is not null
1341: and aud.creation_date between :p_from_date and :p_to_date
1342: and aud.incident_audit_id =
1343: (select max(incident_audit_id)
1344: from cs_incidents_audit_b aud_in
1345: where aud_in.incident_id = aud.incident_id
1346: and aud_in.creation_date between :p_from_date and :p_to_date
1347: and (aud_in.old_incident_owner_id =aud.old_incident_owner_id or
1348: aud_in.incident_owner_id =aud.old_incident_owner_id )
1383: prev_asgn.incident_owner_id incident_owner_id,
1384: nvl(incident_severity_id,-1) incident_severity_id,
1385: prev_asgn.incident_id incident_id_in,
1386: to_number(null) incident_id_out
1387: from cs_incidents_audit_b prev_asgn, ( ' || l_sql_in_sel_r ||
1388: l_sql_in_whr ||'
1389: ) cur_asgn
1390: where cur_asgn.incident_id = prev_asgn.incident_id
1391: and cur_asgn.incident_owner_id = prev_asgn.incident_owner_id
1394: and prev_asgn.incident_owner_id is not null
1395: and prev_asgn.creation_date < :p_from_date
1396: and prev_asgn.incident_audit_id =
1397: (select max(incident_audit_id)
1398: from cs_incidents_audit_b aud_in
1399: where aud_in.incident_id = prev_asgn.incident_id
1400: and aud_in.incident_owner_id = prev_asgn.incident_owner_id
1401: and (nvl(aud_in.incident_owner_id,-1) <>
1402: nvl(aud_in.old_incident_owner_id,-1) or
1411: prev_asgn.old_incident_owner_id incident_owner_id,
1412: nvl(incident_severity_id,-1) incident_severity_id,
1413: null incident_id_in,
1414: prev_asgn.incident_id incident_id_out
1415: from cs_incidents_audit_b prev_asgn, ( ' || l_sql_out_sel_r ||
1416: l_sql_out_whr || '
1417: ) cur_asgn
1418: where cur_asgn.incident_id = prev_asgn.incident_id
1419: and cur_asgn.old_incident_owner_id = prev_asgn.old_incident_owner_id
1422: and prev_asgn.old_incident_owner_id is not null
1423: and prev_asgn.creation_date < :p_from_date
1424: and prev_asgn.incident_audit_id =
1425: (select max(incident_audit_id)
1426: from cs_incidents_audit_b aud_in
1427: where aud_in.incident_id = prev_asgn.incident_id
1428: and aud_in.old_incident_owner_id = prev_asgn.old_incident_owner_id
1429: and nvl(prev_asgn.incident_owner_id,-1) <>
1430: nvl(prev_asgn.old_incident_owner_id,-1)
1446: prev_asgn.old_incident_owner_id incident_owner_id,
1447: nvl(incident_severity_id,-1) incident_severity_id,
1448: to_number(null) incident_id_in,
1449: prev_asgn.incident_id incident_id_out
1450: from cs_incidents_audit_b prev_asgn, ( ' || l_sql_in_sel_r ||
1451: l_sql_in_whr || '
1452: ) cur_asgn
1453: where cur_asgn.incident_id = prev_asgn.incident_id
1454: and cur_asgn.incident_owner_id = prev_asgn.old_incident_owner_id
1457: and prev_asgn.old_incident_owner_id is not null
1458: and prev_asgn.creation_date < :p_from_date
1459: and prev_asgn.incident_audit_id =
1460: (select max(incident_audit_id)
1461: from cs_incidents_audit_b aud_in
1462: where aud_in.incident_id = prev_asgn.incident_id
1463: and aud_in.old_incident_owner_id = prev_asgn.old_incident_owner_id
1464: and nvl(aud_in.incident_owner_id,-1) <>
1465: nvl(aud_in.old_incident_owner_id,-1)
1506: aud.group_id group_id ,
1507: incident_severity_id incident_severity_id,
1508: count(distinct aud.incident_id) sr_in,
1509: 0 sr_out
1510: from cs_incidents_audit_b aud
1511: where nvl(aud.group_id,-1) <> nvl(aud.old_group_id,-1)
1512: and aud.group_id is not null
1513: and aud.creation_date between p_from_date and p_to_date
1514: and aud.incident_audit_id =
1512: and aud.group_id is not null
1513: and aud.creation_date between p_from_date and p_to_date
1514: and aud.incident_audit_id =
1515: (select max(incident_audit_id)
1516: from cs_incidents_audit_b aud_in
1517: where aud_in.incident_id = aud.incident_id
1518: and aud_in.creation_date between p_from_date and p_to_date
1519: and aud_in.group_id = aud.group_id
1520: and nvl(aud_in.group_id,-1) <> nvl(aud_in.old_group_id,-1)
1528: aud.old_group_id ,
1529: incident_severity_id,
1530: 0,
1531: count(distinct aud.incident_id) sr_agent_out
1532: from cs_incidents_audit_b aud
1533: where nvl(aud.group_id,-1) <> nvl(aud.old_group_id,-1)
1534: and aud.old_group_id is not null
1535: and aud.creation_date between p_from_date and p_to_date
1536: and aud.incident_audit_id =
1534: and aud.old_group_id is not null
1535: and aud.creation_date between p_from_date and p_to_date
1536: and aud.incident_audit_id =
1537: (select max(incident_audit_id)
1538: from cs_incidents_audit_b aud_in
1539: where aud_in.incident_id = aud.incident_id
1540: and aud_in.creation_date between p_from_date and p_to_date
1541: and aud_in.old_group_id =aud.old_group_id
1542: and nvl(aud_in.group_id,-1) <> nvl(aud_in.old_group_id,-1)
1586: count(aud.incident_id) responses,
1587: -- in responded_bydate is greater than obligation date, sla missed
1588: count(decode(sign(nvl(aud.inc_responded_by_date,aud.incident_resolved_date)-aud.obligation_date),
1589: 1,1,null)) resp_sla_missed
1590: from cs_incidents_audit_b aud --this is audit rec for response
1591: where aud.incident_audit_id =
1592: ( select max(incident_audit_id)
1593: from cs_incidents_audit_b aud_in
1594: where aud_in.incident_id = aud.incident_id
1589: 1,1,null)) resp_sla_missed
1590: from cs_incidents_audit_b aud --this is audit rec for response
1591: where aud.incident_audit_id =
1592: ( select max(incident_audit_id)
1593: from cs_incidents_audit_b aud_in
1594: where aud_in.incident_id = aud.incident_id
1595: and aud_in.creation_date between p_from_date
1596: and p_to_date
1597: and nvl(nvl(aud_in.old_inc_responded_by_date,aud_in.old_incident_resolved_date),l_dt) <>
1620: count(distinct prev_resp.incident_id) responses,
1621: -- in responded_bydate is greated than obligation date, sla missed
1622: count(decode(sign(nvl(prev_resp.inc_responded_by_date,prev_resp.incident_resolved_date)-prev_resp.obligation_date),
1623: 1,1,null)) resp_sla_missed
1624: from cs_incidents_audit_b curr_resp,
1625: --this is audit rec for response in curr run dates
1626: cs_incidents_audit_b prev_resp
1627: -- this is response in before curr run dates
1628: where curr_resp.incident_audit_id =
1622: count(decode(sign(nvl(prev_resp.inc_responded_by_date,prev_resp.incident_resolved_date)-prev_resp.obligation_date),
1623: 1,1,null)) resp_sla_missed
1624: from cs_incidents_audit_b curr_resp,
1625: --this is audit rec for response in curr run dates
1626: cs_incidents_audit_b prev_resp
1627: -- this is response in before curr run dates
1628: where curr_resp.incident_audit_id =
1629: ( select max(incident_audit_id)
1630: from cs_incidents_audit_b aud_in
1626: cs_incidents_audit_b prev_resp
1627: -- this is response in before curr run dates
1628: where curr_resp.incident_audit_id =
1629: ( select max(incident_audit_id)
1630: from cs_incidents_audit_b aud_in
1631: where aud_in.incident_id = curr_resp.incident_id
1632: and aud_in.creation_date between p_from_date
1633: and p_to_date
1634: and nvl(nvl(aud_in.old_inc_responded_by_date,aud_in.old_incident_resolved_date),l_dt) <>
1642: )
1643: -- above query will insure that selected response is the last response
1644: and prev_resp.incident_id = curr_resp.incident_id
1645: and prev_resp.incident_audit_id = ( select max(incident_audit_id)
1646: from cs_incidents_audit_b aud_in1
1647: where aud_in1.incident_id = curr_resp.incident_id
1648: and aud_in1.creation_date < p_from_date
1649: and nvl(nvl(aud_in1.old_inc_responded_by_date,aud_in1.old_incident_resolved_date),l_dt) <>
1650: nvl(nvl(aud_in1.inc_responded_by_date,aud_in1.incident_resolved_date),l_dt)
1685: decode(aud.incident_audit_id,to_dttm.incident_audit_id,
1686: nvl(aud.inc_responded_by_date,aud.incident_resolved_date),to_dttm.creation_date)
1687: -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date)
1688: )) * 1440 not_waiting_on_me
1689: from cs_incidents_audit_b aud , --this is audit rec for response
1690: cs_incidents_audit_b to_dttm , -- to date time
1691: cs_incidents_audit_b from_dttm -- ,from date time
1692: /* the pair of from_dttm to to_dttm will give the durating in which an agent
1693: owned a serveice request. */
1686: nvl(aud.inc_responded_by_date,aud.incident_resolved_date),to_dttm.creation_date)
1687: -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date)
1688: )) * 1440 not_waiting_on_me
1689: from cs_incidents_audit_b aud , --this is audit rec for response
1690: cs_incidents_audit_b to_dttm , -- to date time
1691: cs_incidents_audit_b from_dttm -- ,from date time
1692: /* the pair of from_dttm to to_dttm will give the durating in which an agent
1693: owned a serveice request. */
1694: where aud.incident_audit_id =
1687: -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date)
1688: )) * 1440 not_waiting_on_me
1689: from cs_incidents_audit_b aud , --this is audit rec for response
1690: cs_incidents_audit_b to_dttm , -- to date time
1691: cs_incidents_audit_b from_dttm -- ,from date time
1692: /* the pair of from_dttm to to_dttm will give the durating in which an agent
1693: owned a serveice request. */
1694: where aud.incident_audit_id =
1695: ( select max(incident_audit_id)
1692: /* the pair of from_dttm to to_dttm will give the durating in which an agent
1693: owned a serveice request. */
1694: where aud.incident_audit_id =
1695: ( select max(incident_audit_id)
1696: from cs_incidents_audit_b aud_in
1697: where aud_in.incident_id = aud.incident_id
1698: and aud_in.creation_date between p_from_date
1699: and p_to_date
1700: and nvl(nvl(aud_in.old_inc_responded_by_date,aud_in.old_incident_resolved_date),l_dt) <>
1723: nvl(from_dttm.incident_date,trunc(sysdate-300))
1724: )
1725: */
1726: and from_dttm.incident_audit_id =
1727: (select max(incident_audit_id) from cs_incidents_audit_b x
1728: where x.incident_id = aud.incident_id
1729: and ((nvl(x.old_incident_owner_id,-1) <>
1730: nvl(x.incident_owner_id,-1) and
1731: x.creation_date >= x.incident_date) or
1774: sum(decode(aud.incident_owner_id,to_dttm.old_incident_owner_id,
1775: 0,to_dttm.creation_date-from_dttm.creation_date))
1776: * 1440 not_waiting_on_me
1777: */
1778: from cs_incidents_audit_b curr_resp,
1779: cs_incidents_audit_b aud , --this is audit rec for prior response
1780: cs_incidents_audit_b to_dttm , -- to date time
1781: cs_incidents_audit_b from_dttm -- ,from date time
1782: /* the pair of from_dttm to to_dttm will give the durating in which an agent
1775: 0,to_dttm.creation_date-from_dttm.creation_date))
1776: * 1440 not_waiting_on_me
1777: */
1778: from cs_incidents_audit_b curr_resp,
1779: cs_incidents_audit_b aud , --this is audit rec for prior response
1780: cs_incidents_audit_b to_dttm , -- to date time
1781: cs_incidents_audit_b from_dttm -- ,from date time
1782: /* the pair of from_dttm to to_dttm will give the durating in which an agent
1783: owned a serveice request. */
1776: * 1440 not_waiting_on_me
1777: */
1778: from cs_incidents_audit_b curr_resp,
1779: cs_incidents_audit_b aud , --this is audit rec for prior response
1780: cs_incidents_audit_b to_dttm , -- to date time
1781: cs_incidents_audit_b from_dttm -- ,from date time
1782: /* the pair of from_dttm to to_dttm will give the durating in which an agent
1783: owned a serveice request. */
1784: where aud.incident_audit_id =
1777: */
1778: from cs_incidents_audit_b curr_resp,
1779: cs_incidents_audit_b aud , --this is audit rec for prior response
1780: cs_incidents_audit_b to_dttm , -- to date time
1781: cs_incidents_audit_b from_dttm -- ,from date time
1782: /* the pair of from_dttm to to_dttm will give the durating in which an agent
1783: owned a serveice request. */
1784: where aud.incident_audit_id =
1785: ( select max(incident_audit_id)
1782: /* the pair of from_dttm to to_dttm will give the durating in which an agent
1783: owned a serveice request. */
1784: where aud.incident_audit_id =
1785: ( select max(incident_audit_id)
1786: from cs_incidents_audit_b aud_in
1787: where aud_in.incident_id = curr_resp.incident_id
1788: and aud_in.creation_date < p_from_date
1789: and nvl(nvl(aud_in.old_inc_responded_by_date,aud_in.old_incident_resolved_date),l_dt) <>
1790: nvl(nvl(aud_in.inc_responded_by_date,aud_in.incident_resolved_date),l_dt)
1796: -- responded condition.
1797: )
1798: and curr_resp.incident_audit_id =
1799: ( select max(incident_audit_id)
1800: from cs_incidents_audit_b aud_in
1801: where aud_in.incident_id = curr_resp.incident_id
1802: and aud_in.creation_date between p_from_date
1803: and p_to_date
1804: and nvl(nvl(aud_in.old_inc_responded_by_date,aud_in.old_incident_resolved_date),l_dt) <>
1829: nvl(from_dttm.incident_date,trunc(sysdate-300))
1830: )
1831: */
1832: and from_dttm.incident_audit_id =
1833: (select max(incident_audit_id) from cs_incidents_audit_b x
1834: where x.incident_id = curr_resp.incident_id
1835: and ((nvl(x.old_incident_owner_id,-1) <>
1836: nvl(x.incident_owner_id,-1) and
1837: x.creation_date >= x.incident_date) or
2191: begin
2192: /**
2193: select min(creation_date) - 1
2194: into l_min_date
2195: from cs_incidents_audit_b;
2196: */
2197: delete from csy_response_resolutions;
2198: delete from csy_resolution_qlty;
2199: commit;