eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.CSY_KPI_PKG dependencies on CS_INCIDENTS_AUDIT_B

Line 10: from cs_incidents_audit_b aud_in

6: l_dt in date) return number is
7: l_id number;
8: begin
9: select max(incident_audit_id) into l_id
10: from cs_incidents_audit_b aud_in
11: where aud_in.incident_id = p_inc_id
12: and aud_in.creation_date < p_from_date
13: and
14: nvl(nvl(aud_in.old_inc_responded_by_date,aud_in.old_incident_resolved_date),l_dt)

Line 29: from cs_incidents_audit_b aud_in

25: l_dt in date) return number is
26: l_id number;
27: begin
28: select max(incident_audit_id) into l_id
29: from cs_incidents_audit_b aud_in
30: where aud_in.incident_id = p_inc_id
31: and aud_in.creation_date between p_from_date and p_to_date
32: and nvl(nvl(aud_in.old_inc_responded_by_date,aud_in.old_incident_resolved_date),l_dt)
33: <>

Line 289: from cs_incidents_audit_b aud_out,

285: l_dt date;
286: cursor c_backlog is
287: select nvl(aud_out.incident_owner_id,-1), nvl(aud_out.group_id,-1),
288: nvl(aud_out.incident_severity_id,-1), count(*)
289: from cs_incidents_audit_b aud_out,
290: cs_incidents_all_b sr
291: where aud_out.incident_resolved_date is null
292: and aud_out.incident_id = sr.incident_id
293: and nvl(sr.incident_resolved_date,sysdate+1000) > l_dt

Line 318: from cs_incidents_audit_b aud_in

314: all dates between 11th and 25th may and aut_out.status_flag = 'O'
315: will make it unselected and hence not counted
316: */
317: (select max(incident_audit_id)
318: from cs_incidents_audit_b aud_in
319: where aud_in.incident_id = aud_out.incident_id
320: and aud_in.creation_date < l_dt +1)
321: -- so that full day is taken
322: group by aud_out.incident_owner_id,

Line 424: from cs_incidents_audit_b aud, --this is audit rec for response

420: count(aud.incident_id) resolutions,
421: count(decode(sign(sr.incident_resolved_date-
422: sr.expected_resolution_date),
423: 1,1,null)) resl_sla_missed
424: from cs_incidents_audit_b aud, --this is audit rec for response
425: cs_incidents_all_b sr
426: where sr.incident_id = aud.incident_id
427: and (aud.incident_owner_id is not null or
428: aud.group_id is not null)

Line 431: from cs_incidents_audit_b aud_in

427: and (aud.incident_owner_id is not null or
428: aud.group_id is not null)
429: and aud.incident_audit_id =
430: ( select max(incident_audit_id)
431: from cs_incidents_audit_b aud_in
432: where aud_in.incident_id = aud.incident_id
433: and aud_in.creation_date between p_from_date
434: and p_to_date
435: and nvl(aud_in.old_incident_resolved_date,l_dt) <>

Line 458: from cs_incidents_audit_b curr_resp,

454: count(prev_resp.incident_id) resolutions,
455: count(decode(sign(sr.actual_resolution_date-
456: sr.expected_resolution_date),
457: 1,1,null)) resl_sla_missed
458: from cs_incidents_audit_b curr_resp,
459: --this is audit rec for response in curr run dates
460: cs_incidents_audit_b prev_resp,
461: -- this is response in before curr run dates
462: cs_incidents_all_b sr

Line 460: cs_incidents_audit_b prev_resp,

456: sr.expected_resolution_date),
457: 1,1,null)) resl_sla_missed
458: from cs_incidents_audit_b curr_resp,
459: --this is audit rec for response in curr run dates
460: cs_incidents_audit_b prev_resp,
461: -- this is response in before curr run dates
462: cs_incidents_all_b sr
463: where sr.incident_id = prev_resp.incident_id
464: and curr_resp.incident_audit_id =

Line 466: from cs_incidents_audit_b aud_in

462: cs_incidents_all_b sr
463: where sr.incident_id = prev_resp.incident_id
464: and curr_resp.incident_audit_id =
465: ( select max(incident_audit_id)
466: from cs_incidents_audit_b aud_in
467: where aud_in.incident_id = curr_resp.incident_id
468: and aud_in.creation_date between p_from_date
469: and p_to_date
470: and nvl(aud_in.old_incident_resolved_date,l_dt) <>

Line 481: from cs_incidents_audit_b aud_in1

477: )
478: -- above query will insure that selected response is the last response
479: and prev_resp.incident_id = curr_resp.incident_id
480: and prev_resp.incident_audit_id = ( select max(incident_audit_id)
481: from cs_incidents_audit_b aud_in1
482: where aud_in1.incident_id = curr_resp.incident_id
483: and aud_in1.creation_date < p_from_date
484: and nvl(aud_in1.old_incident_resolved_date,l_dt) <>
485: nvl(aud_in1.incident_resolved_date,l_dt)

Line 548: from cs_incidents_audit_b aud , --this is audit rec for response

544: decode(aud.incident_audit_id,to_dttm.incident_audit_id,
545: aud.incident_resolved_date,to_dttm.creation_date)
546: -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),0))
547: ) * 1440 wait_on_agent
548: from cs_incidents_audit_b aud , --this is audit rec for response
549: cs_incidents_audit_b to_dttm , -- to date time
550: cs_incidents_audit_b from_dttm, -- from date time
551: cs_incident_Statuses_b to_stat
552: /* the pair of from_dttm to to_dttm will give the durating in which an agent

Line 549: cs_incidents_audit_b to_dttm , -- to date time

545: aud.incident_resolved_date,to_dttm.creation_date)
546: -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),0))
547: ) * 1440 wait_on_agent
548: from cs_incidents_audit_b aud , --this is audit rec for response
549: cs_incidents_audit_b to_dttm , -- to date time
550: cs_incidents_audit_b from_dttm, -- from date time
551: cs_incident_Statuses_b to_stat
552: /* the pair of from_dttm to to_dttm will give the durating in which an agent
553: owned a serveice request.

Line 550: cs_incidents_audit_b from_dttm, -- from date time

546: -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),0))
547: ) * 1440 wait_on_agent
548: from cs_incidents_audit_b aud , --this is audit rec for response
549: cs_incidents_audit_b to_dttm , -- to date time
550: cs_incidents_audit_b from_dttm, -- from date time
551: cs_incident_Statuses_b to_stat
552: /* the pair of from_dttm to to_dttm will give the durating in which an agent
553: owned a serveice request.
554: cs_incidents_all_b sr -- only for incident_date */

Line 557: from cs_incidents_audit_b aud_in

553: owned a serveice request.
554: cs_incidents_all_b sr -- only for incident_date */
555: where aud.incident_audit_id =
556: ( select max(incident_audit_id)
557: from cs_incidents_audit_b aud_in
558: where aud_in.incident_id = aud.incident_id
559: and aud_in.creation_date between p_from_date
560: and p_to_date
561: and nvl(aud_in.old_incident_resolved_date,l_dt) <>

Line 589: (select max(incident_audit_id) from cs_incidents_audit_b x

585: or nvl(from_dttm.old_incident_status_id,-1) <>
586: nvl(from_dttm.incident_status_id,-1))
587: */
588: and from_dttm.incident_audit_id =
589: (select max(incident_audit_id) from cs_incidents_audit_b x
590: where x.incident_id = aud.incident_id
591: and ((nvl(x.old_incident_owner_id,-1) <>
592: nvl(x.incident_owner_id,-1) or
593: nvl(x.old_incident_status_id,-1) <>

Line 649: from cs_incidents_audit_b curr_resp,

645: decode(aud.incident_audit_id,to_dttm.incident_audit_id,
646: aud.incident_resolved_date,to_dttm.creation_date)
647: -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),0))
648: ) * 1440 wait_on_agent
649: from cs_incidents_audit_b curr_resp,
650: cs_incidents_audit_b aud , --this is audit rec for prior resolutions
651: cs_incidents_audit_b to_dttm , -- to date time
652: cs_incidents_audit_b from_dttm, -- from date time
653: cs_incident_statuses_b to_Stat

Line 650: cs_incidents_audit_b aud , --this is audit rec for prior resolutions

646: aud.incident_resolved_date,to_dttm.creation_date)
647: -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),0))
648: ) * 1440 wait_on_agent
649: from cs_incidents_audit_b curr_resp,
650: cs_incidents_audit_b aud , --this is audit rec for prior resolutions
651: cs_incidents_audit_b to_dttm , -- to date time
652: cs_incidents_audit_b from_dttm, -- from date time
653: cs_incident_statuses_b to_Stat
654: /* the pair of from_dttm to to_dttm will give the durating in which an agent

Line 651: cs_incidents_audit_b to_dttm , -- to date time

647: -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date),0))
648: ) * 1440 wait_on_agent
649: from cs_incidents_audit_b curr_resp,
650: cs_incidents_audit_b aud , --this is audit rec for prior resolutions
651: cs_incidents_audit_b to_dttm , -- to date time
652: cs_incidents_audit_b from_dttm, -- from date time
653: cs_incident_statuses_b to_Stat
654: /* the pair of from_dttm to to_dttm will give the durating in which an agent
655: owned a serveice request.

Line 652: cs_incidents_audit_b from_dttm, -- from date time

648: ) * 1440 wait_on_agent
649: from cs_incidents_audit_b curr_resp,
650: cs_incidents_audit_b aud , --this is audit rec for prior resolutions
651: cs_incidents_audit_b to_dttm , -- to date time
652: cs_incidents_audit_b from_dttm, -- from date time
653: cs_incident_statuses_b to_Stat
654: /* the pair of from_dttm to to_dttm will give the durating in which an agent
655: owned a serveice request.
656: cs_incidents_all_b sr -- only for incident_date */

Line 659: from cs_incidents_audit_b aud_in

655: owned a serveice request.
656: cs_incidents_all_b sr -- only for incident_date */
657: where aud.incident_audit_id =
658: ( select max(incident_audit_id)
659: from cs_incidents_audit_b aud_in
660: where aud_in.incident_id = curr_resp.incident_id
661: and aud_in.creation_date < p_from_date
662: and nvl(aud_in.old_incident_resolved_date,l_dt) <>
663: nvl(aud_in.incident_resolved_date,l_dt)

Line 672: from cs_incidents_audit_b aud_in

668: -- responded condition.
669: )
670: and curr_resp.incident_audit_id =
671: ( select max(incident_audit_id)
672: from cs_incidents_audit_b aud_in
673: where aud_in.incident_id = curr_resp.incident_id
674: and aud_in.creation_date between p_from_date
675: and p_to_date
676: and nvl(aud_in.old_incident_resolved_date,l_dt) <>

Line 706: (select max(incident_audit_id) from cs_incidents_audit_b x

702: or nvl(from_dttm.old_incident_status_id,-1) <>
703: nvl(from_dttm.incident_status_id,-1))
704: */
705: and from_dttm.incident_audit_id =
706: (select max(incident_audit_id) from cs_incidents_audit_b x
707: where x.incident_id = aud.incident_id
708: and ((nvl(x.old_incident_owner_id,-1) <>
709: nvl(x.incident_owner_id,-1) or
710: nvl(x.old_incident_status_id,-1) <>

Line 1005: from cs_incidents_audit_b first_rslvd,

1001: nvl(first_rslvd.resolution_code ,''-1'') resolution_code ,
1002: count(first_rslvd.incident_id) sr_resolved ,
1003: 0 sr_reopened,
1004: 0 sr_reopened2
1005: from cs_incidents_audit_b first_rslvd,
1006: cs_incidents_all_b sr
1007: where sr.incident_id = first_rslvd.incident_id
1008: and first_rslvd.incident_owner_id is not null
1009: -- so that only those rec are selected where resolution date is

Line 1017: and not exists (select 1 from cs_incidents_audit_b x

1013: and first_rslvd.incident_resolved_date is not null
1014: -- select only resloutions in a given period
1015: and first_rslvd.creation_date between :p_from_date and :p_to_date
1016: -- select a resolution only if it first time resolution
1017: and not exists (select 1 from cs_incidents_audit_b x
1018: where x.incident_resolved_date is not null
1019: and nvl(x.incident_resolved_date, :l_dt) <>
1020: nvl(x.old_incident_resolved_date, :l_dt)
1021: and x.incident_id = first_rslvd.incident_id

Line 1068: from cs_incidents_audit_b last_unrslvd,

1064: decode(count(prev_unrsltns.old_incident_resolved_date),0,0, 1) rework2
1065: /* 1 mean there are atleast two reopen, 1 for last_unrslvd and
1066: 1 from prev_unrsltns
1067: it will return 1 only if there are atleast 2 old close dates */
1068: from cs_incidents_audit_b last_unrslvd,
1069: cs_incidents_audit_b prev_unrsltns,
1070: cs_incidents_all_b sr
1071: where sr.incident_id = last_unrslvd.incident_id
1072: and last_unrslvd.incident_owner_id is not null

Line 1069: cs_incidents_audit_b prev_unrsltns,

1065: /* 1 mean there are atleast two reopen, 1 for last_unrslvd and
1066: 1 from prev_unrsltns
1067: it will return 1 only if there are atleast 2 old close dates */
1068: from cs_incidents_audit_b last_unrslvd,
1069: cs_incidents_audit_b prev_unrsltns,
1070: cs_incidents_all_b sr
1071: where sr.incident_id = last_unrslvd.incident_id
1072: and last_unrslvd.incident_owner_id is not null
1073: and last_unrslvd.old_incident_resolved_date is not null

Line 1078: (select max(incident_audit_id) from cs_incidents_audit_b x

1074: and last_unrslvd.incident_resolved_date is null
1075: /* select only last reopen in a given period */
1076: and last_unrslvd.creation_date between :p_from_date and :p_to_date
1077: and last_unrslvd.incident_audit_id =
1078: (select max(incident_audit_id) from cs_incidents_audit_b x
1079: where x.old_incident_resolved_date is not null
1080: and x.incident_resolved_date is null
1081: and x.incident_id = last_unrslvd.incident_id
1082: and x.incident_owner_id = last_unrslvd.incident_owner_id

Line 1136: from cs_incidents_audit_b curr_unrslvd,

1132: decode(count(prev_unrsltns.old_incident_resolved_date),0,0,1) rework,
1133: /* 1 */
1134: decode(count(prev_unrsltns1.old_incident_resolved_date),0,0, 1)reopen
1135: /** it will return 1 only if there are atleast 2 old close dates */
1136: from cs_incidents_audit_b curr_unrslvd,
1137: cs_incidents_audit_b prev_unrsltns,
1138: /* this indicates if a sr is reworked*/
1139: cs_incidents_audit_b prev_unrsltns1,
1140: /* this table indicates if a sr is reworked more than once*/

Line 1137: cs_incidents_audit_b prev_unrsltns,

1133: /* 1 */
1134: decode(count(prev_unrsltns1.old_incident_resolved_date),0,0, 1)reopen
1135: /** it will return 1 only if there are atleast 2 old close dates */
1136: from cs_incidents_audit_b curr_unrslvd,
1137: cs_incidents_audit_b prev_unrsltns,
1138: /* this indicates if a sr is reworked*/
1139: cs_incidents_audit_b prev_unrsltns1,
1140: /* this table indicates if a sr is reworked more than once*/
1141: cs_incidents_all_b sr

Line 1139: cs_incidents_audit_b prev_unrsltns1,

1135: /** it will return 1 only if there are atleast 2 old close dates */
1136: from cs_incidents_audit_b curr_unrslvd,
1137: cs_incidents_audit_b prev_unrsltns,
1138: /* this indicates if a sr is reworked*/
1139: cs_incidents_audit_b prev_unrsltns1,
1140: /* this table indicates if a sr is reworked more than once*/
1141: cs_incidents_all_b sr
1142: where sr.incident_id = prev_unrsltns.incident_id
1143: and curr_unrslvd.incident_owner_id is not null

Line 1149: (select max(incident_audit_id) from cs_incidents_audit_b x

1145: and curr_unrslvd.incident_resolved_date is null
1146: /* select only rework in a given period */
1147: and curr_unrslvd.creation_date between :p_from_date and :p_to_date
1148: and curr_unrslvd.incident_audit_id =
1149: (select max(incident_audit_id) from cs_incidents_audit_b x
1150: where x.old_incident_resolved_date is not null
1151: and x.incident_resolved_date is null
1152: and x.incident_id = curr_unrslvd.incident_id
1153: and x.incident_owner_id = curr_unrslvd.incident_owner_id

Line 1165: (select max(y.incident_audit_id) from cs_incidents_audit_b y

1161: and prev_unrsltns.incident_resolved_date is null
1162: and prev_unrsltns.old_incident_resolved_date is not null
1163: and prev_unrsltns.incident_owner_id = curr_unrslvd.incident_owner_id
1164: and prev_unrsltns.incident_audit_id =
1165: (select max(y.incident_audit_id) from cs_incidents_audit_b y
1166: where y.incident_id = prev_unrsltns.incident_id
1167: and y.incident_owner_id = prev_unrsltns.incident_owner_id
1168: and y.creation_date < :p_from_date
1169: and y.incident_resolved_date is null

Line 1338: from cs_incidents_audit_b aud

1334: l_sql_in_sel_r := '
1335: select aud.incident_id,
1336: aud.incident_owner_id';
1337: l_sql_in_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.incident_owner_id is not null
1341: and */ aud.creation_date between :p_from_date and :p_to_date
1342: -- same condition are present in subquery too. it existance of these conditions outside the

Line 1346: from cs_incidents_audit_b aud_in

1342: -- same condition are present in subquery too. it existance of these conditions outside the
1343: -- subquery is meaningless. remove it when modifying this query.
1344: and aud.incident_audit_id =
1345: (select max(incident_audit_id)
1346: from cs_incidents_audit_b aud_in
1347: where aud_in.incident_id = aud.incident_id
1348: and aud_in.creation_date between :p_from_date
1349: and :p_to_date
1350: and aud_in.incident_owner_id = aud.incident_owner_id

Line 1376: from cs_incidents_audit_b aud

1372: l_sql_out_sel_r := '
1373: select aud.incident_id,
1374: aud.old_incident_owner_id';
1375: l_sql_out_whr := '
1376: from cs_incidents_audit_b aud
1377: where nvl(aud.incident_owner_id,-1) <> nvl(aud.old_incident_owner_id,-1)
1378: and aud.old_incident_owner_id is not null
1379: and aud.creation_date between :p_from_date and :p_to_date
1380: and aud.incident_audit_id =

Line 1382: from cs_incidents_audit_b aud_in

1378: and aud.old_incident_owner_id is not null
1379: and aud.creation_date between :p_from_date and :p_to_date
1380: and aud.incident_audit_id =
1381: (select max(incident_audit_id)
1382: from cs_incidents_audit_b aud_in
1383: where aud_in.incident_id = aud.incident_id
1384: and aud_in.creation_date between :p_from_date and :p_to_date
1385: and (aud_in.old_incident_owner_id =aud.old_incident_owner_id or
1386: aud_in.incident_owner_id =aud.old_incident_owner_id )

Line 1425: from cs_incidents_audit_b prev_asgn, ( ' || l_sql_in_sel_r ||

1421: prev_asgn.incident_owner_id incident_owner_id,
1422: nvl(incident_severity_id,-1) incident_severity_id,
1423: prev_asgn.incident_id incident_id_in,
1424: to_number(null) incident_id_out
1425: from cs_incidents_audit_b prev_asgn, ( ' || l_sql_in_sel_r ||
1426: l_sql_in_whr ||'
1427: ) cur_asgn
1428: where cur_asgn.incident_id = prev_asgn.incident_id
1429: and cur_asgn.incident_owner_id = prev_asgn.incident_owner_id

Line 1436: from cs_incidents_audit_b aud_in

1432: and prev_asgn.incident_owner_id is not null
1433: and prev_asgn.creation_date < :p_from_date
1434: and prev_asgn.incident_audit_id =
1435: (select max(incident_audit_id)
1436: from cs_incidents_audit_b aud_in
1437: where aud_in.incident_id = prev_asgn.incident_id
1438: and aud_in.incident_owner_id = prev_asgn.incident_owner_id
1439: and (nvl(aud_in.incident_owner_id,-1) <>
1440: nvl(aud_in.old_incident_owner_id,-1) or

Line 1453: from cs_incidents_audit_b prev_asgn, ( ' || l_sql_out_sel_r ||

1449: prev_asgn.old_incident_owner_id incident_owner_id,
1450: nvl(incident_severity_id,-1) incident_severity_id,
1451: null incident_id_in,
1452: prev_asgn.incident_id incident_id_out
1453: from cs_incidents_audit_b prev_asgn, ( ' || l_sql_out_sel_r ||
1454: l_sql_out_whr || '
1455: ) cur_asgn
1456: where cur_asgn.incident_id = prev_asgn.incident_id
1457: and cur_asgn.old_incident_owner_id = prev_asgn.old_incident_owner_id

Line 1464: from cs_incidents_audit_b aud_in

1460: and prev_asgn.old_incident_owner_id is not null
1461: and prev_asgn.creation_date < :p_from_date
1462: and prev_asgn.incident_audit_id =
1463: (select max(incident_audit_id)
1464: from cs_incidents_audit_b aud_in
1465: where aud_in.incident_id = prev_asgn.incident_id
1466: and aud_in.old_incident_owner_id = prev_asgn.old_incident_owner_id
1467: and nvl(prev_asgn.incident_owner_id,-1) <>
1468: nvl(prev_asgn.old_incident_owner_id,-1)

Line 1488: from cs_incidents_audit_b prev_asgn, ( ' || l_sql_in_sel_r ||

1484: prev_asgn.old_incident_owner_id incident_owner_id,
1485: nvl(incident_severity_id,-1) incident_severity_id,
1486: to_number(null) incident_id_in,
1487: prev_asgn.incident_id incident_id_out
1488: from cs_incidents_audit_b prev_asgn, ( ' || l_sql_in_sel_r ||
1489: l_sql_in_whr || '
1490: ) cur_asgn
1491: where cur_asgn.incident_id = prev_asgn.incident_id
1492: and cur_asgn.incident_owner_id = prev_asgn.old_incident_owner_id

Line 1499: from cs_incidents_audit_b aud_in

1495: and prev_asgn.old_incident_owner_id is not null
1496: and prev_asgn.creation_date < :p_from_date
1497: and prev_asgn.incident_audit_id =
1498: (select max(incident_audit_id)
1499: from cs_incidents_audit_b aud_in
1500: where aud_in.incident_id = prev_asgn.incident_id
1501: and aud_in.old_incident_owner_id = prev_asgn.old_incident_owner_id
1502: and nvl(aud_in.incident_owner_id,-1) <>
1503: nvl(aud_in.old_incident_owner_id,-1)

Line 1548: from cs_incidents_audit_b aud

1544: aud.group_id group_id ,
1545: incident_severity_id incident_severity_id,
1546: count(distinct aud.incident_id) sr_in,
1547: 0 sr_out
1548: from cs_incidents_audit_b aud
1549: where nvl(aud.group_id,-1) <> nvl(aud.old_group_id,-1)
1550: and aud.group_id is not null
1551: and aud.creation_date between p_from_date and p_to_date
1552: and aud.incident_audit_id =

Line 1554: from cs_incidents_audit_b aud_in

1550: and aud.group_id is not null
1551: and aud.creation_date between p_from_date and p_to_date
1552: and aud.incident_audit_id =
1553: (select max(incident_audit_id)
1554: from cs_incidents_audit_b aud_in
1555: where aud_in.incident_id = aud.incident_id
1556: and aud_in.creation_date between p_from_date and p_to_date
1557: and aud_in.group_id = aud.group_id
1558: and nvl(aud_in.group_id,-1) <> nvl(aud_in.old_group_id,-1)

Line 1570: from cs_incidents_audit_b aud

1566: aud.old_group_id ,
1567: incident_severity_id,
1568: 0,
1569: count(distinct aud.incident_id) sr_agent_out
1570: from cs_incidents_audit_b aud
1571: where nvl(aud.group_id,-1) <> nvl(aud.old_group_id,-1)
1572: and aud.old_group_id is not null
1573: and aud.creation_date between p_from_date and p_to_date
1574: and aud.incident_audit_id =

Line 1576: from cs_incidents_audit_b aud_in

1572: and aud.old_group_id is not null
1573: and aud.creation_date between p_from_date and p_to_date
1574: and aud.incident_audit_id =
1575: (select max(incident_audit_id)
1576: from cs_incidents_audit_b aud_in
1577: where aud_in.incident_id = aud.incident_id
1578: and aud_in.creation_date between p_from_date and p_to_date
1579: and aud_in.old_group_id =aud.old_group_id
1580: and nvl(aud_in.group_id,-1) <> nvl(aud_in.old_group_id,-1)

Line 1628: from cs_incidents_audit_b aud --this is audit rec for response

1624: count(aud.incident_id) responses,
1625: -- in responded_bydate is greater than obligation date, sla missed
1626: count(decode(sign(nvl(aud.inc_responded_by_date,aud.incident_resolved_date)-aud.obligation_date),
1627: 1,1,null)) resp_sla_missed
1628: from cs_incidents_audit_b aud --this is audit rec for response
1629: where aud.incident_audit_id =
1630: ( select max(incident_audit_id)
1631: from cs_incidents_audit_b aud_in
1632: where aud_in.incident_id = aud.incident_id

Line 1631: from cs_incidents_audit_b aud_in

1627: 1,1,null)) resp_sla_missed
1628: from cs_incidents_audit_b aud --this is audit rec for response
1629: where aud.incident_audit_id =
1630: ( select max(incident_audit_id)
1631: from cs_incidents_audit_b aud_in
1632: where aud_in.incident_id = aud.incident_id
1633: and aud_in.creation_date between p_from_date
1634: and p_to_date
1635: and nvl(nvl(aud_in.old_inc_responded_by_date,aud_in.old_incident_resolved_date),l_dt) <>

Line 1662: from cs_incidents_audit_b curr_resp,

1658: count(distinct prev_resp.incident_id) responses,
1659: -- in responded_bydate is greated than obligation date, sla missed
1660: count(decode(sign(nvl(prev_resp.inc_responded_by_date,prev_resp.incident_resolved_date)-prev_resp.obligation_date),
1661: 1,1,null)) resp_sla_missed
1662: from cs_incidents_audit_b curr_resp,
1663: --this is audit rec for response in curr run dates
1664: cs_incidents_audit_b prev_resp
1665: -- this is response in before curr run dates
1666: where curr_resp.incident_audit_id =

Line 1664: cs_incidents_audit_b prev_resp

1660: count(decode(sign(nvl(prev_resp.inc_responded_by_date,prev_resp.incident_resolved_date)-prev_resp.obligation_date),
1661: 1,1,null)) resp_sla_missed
1662: from cs_incidents_audit_b curr_resp,
1663: --this is audit rec for response in curr run dates
1664: cs_incidents_audit_b prev_resp
1665: -- this is response in before curr run dates
1666: where curr_resp.incident_audit_id =
1667: ( select max(incident_audit_id)
1668: from cs_incidents_audit_b aud_in

Line 1668: from cs_incidents_audit_b aud_in

1664: cs_incidents_audit_b prev_resp
1665: -- this is response in before curr run dates
1666: where curr_resp.incident_audit_id =
1667: ( select max(incident_audit_id)
1668: from cs_incidents_audit_b aud_in
1669: where aud_in.incident_id = curr_resp.incident_id
1670: and aud_in.creation_date between p_from_date
1671: and p_to_date
1672: and nvl(nvl(aud_in.old_inc_responded_by_date,aud_in.old_incident_resolved_date),l_dt) <>

Line 1684: from cs_incidents_audit_b aud_in1

1680: )
1681: -- above query will insure that selected response is the last response
1682: and prev_resp.incident_id = curr_resp.incident_id
1683: and prev_resp.incident_audit_id = ( select max(incident_audit_id)
1684: from cs_incidents_audit_b aud_in1
1685: where aud_in1.incident_id = curr_resp.incident_id
1686: and aud_in1.creation_date < p_from_date
1687: and nvl(nvl(aud_in1.old_inc_responded_by_date,aud_in1.old_incident_resolved_date),l_dt) <>
1688: nvl(nvl(aud_in1.inc_responded_by_date,aud_in1.incident_resolved_date),l_dt)

Line 1727: from cs_incidents_audit_b aud , --this is audit rec for response

1723: decode(aud.incident_audit_id,to_dttm.incident_audit_id,
1724: nvl(aud.inc_responded_by_date,aud.incident_resolved_date),to_dttm.creation_date)
1725: -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date)
1726: )) * 1440 not_waiting_on_me
1727: from cs_incidents_audit_b aud , --this is audit rec for response
1728: cs_incidents_audit_b to_dttm , -- to date time
1729: cs_incidents_audit_b from_dttm -- ,from date time
1730: /* the pair of from_dttm to to_dttm will give the durating in which an agent
1731: owned a serveice request. */

Line 1728: cs_incidents_audit_b to_dttm , -- to date time

1724: nvl(aud.inc_responded_by_date,aud.incident_resolved_date),to_dttm.creation_date)
1725: -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date)
1726: )) * 1440 not_waiting_on_me
1727: from cs_incidents_audit_b aud , --this is audit rec for response
1728: cs_incidents_audit_b to_dttm , -- to date time
1729: cs_incidents_audit_b from_dttm -- ,from date time
1730: /* the pair of from_dttm to to_dttm will give the durating in which an agent
1731: owned a serveice request. */
1732: where aud.incident_audit_id =

Line 1729: cs_incidents_audit_b from_dttm -- ,from date time

1725: -decode(from_dttm.old_incident_date,null,from_dttm.incident_date,from_dttm.creation_date)
1726: )) * 1440 not_waiting_on_me
1727: from cs_incidents_audit_b aud , --this is audit rec for response
1728: cs_incidents_audit_b to_dttm , -- to date time
1729: cs_incidents_audit_b from_dttm -- ,from date time
1730: /* the pair of from_dttm to to_dttm will give the durating in which an agent
1731: owned a serveice request. */
1732: where aud.incident_audit_id =
1733: ( select max(incident_audit_id)

Line 1734: from cs_incidents_audit_b aud_in

1730: /* the pair of from_dttm to to_dttm will give the durating in which an agent
1731: owned a serveice request. */
1732: where aud.incident_audit_id =
1733: ( select max(incident_audit_id)
1734: from cs_incidents_audit_b aud_in
1735: where aud_in.incident_id = aud.incident_id
1736: and aud_in.creation_date between p_from_date
1737: and p_to_date
1738: and nvl(nvl(aud_in.old_inc_responded_by_date,aud_in.old_incident_resolved_date),l_dt) <>

Line 1765: (select max(incident_audit_id) from cs_incidents_audit_b x

1761: nvl(from_dttm.incident_date,trunc(sysdate-300))
1762: )
1763: */
1764: and from_dttm.incident_audit_id =
1765: (select max(incident_audit_id) from cs_incidents_audit_b x
1766: where x.incident_id = aud.incident_id
1767: and ((nvl(x.old_incident_owner_id,-1) <>
1768: nvl(x.incident_owner_id,-1) and
1769: x.creation_date >= x.incident_date) or

Line 1816: from cs_incidents_audit_b curr_resp,

1812: sum(decode(aud.incident_owner_id,to_dttm.old_incident_owner_id,
1813: 0,to_dttm.creation_date-from_dttm.creation_date))
1814: * 1440 not_waiting_on_me
1815: */
1816: from cs_incidents_audit_b curr_resp,
1817: cs_incidents_audit_b aud , --this is audit rec for prior response
1818: cs_incidents_audit_b to_dttm , -- to date time
1819: cs_incidents_audit_b from_dttm -- ,from date time
1820: /* the pair of from_dttm to to_dttm will give the durating in which an agent

Line 1817: cs_incidents_audit_b aud , --this is audit rec for prior response

1813: 0,to_dttm.creation_date-from_dttm.creation_date))
1814: * 1440 not_waiting_on_me
1815: */
1816: from cs_incidents_audit_b curr_resp,
1817: cs_incidents_audit_b aud , --this is audit rec for prior response
1818: cs_incidents_audit_b to_dttm , -- to date time
1819: cs_incidents_audit_b from_dttm -- ,from date time
1820: /* the pair of from_dttm to to_dttm will give the durating in which an agent
1821: owned a serveice request. */

Line 1818: cs_incidents_audit_b to_dttm , -- to date time

1814: * 1440 not_waiting_on_me
1815: */
1816: from cs_incidents_audit_b curr_resp,
1817: cs_incidents_audit_b aud , --this is audit rec for prior response
1818: cs_incidents_audit_b to_dttm , -- to date time
1819: cs_incidents_audit_b from_dttm -- ,from date time
1820: /* the pair of from_dttm to to_dttm will give the durating in which an agent
1821: owned a serveice request. */
1822: where aud.incident_audit_id = getaudit(curr_resp.incident_id,p_from_date,l_dt)

Line 1819: cs_incidents_audit_b from_dttm -- ,from date time

1815: */
1816: from cs_incidents_audit_b curr_resp,
1817: cs_incidents_audit_b aud , --this is audit rec for prior response
1818: cs_incidents_audit_b to_dttm , -- to date time
1819: cs_incidents_audit_b from_dttm -- ,from date time
1820: /* the pair of from_dttm to to_dttm will give the durating in which an agent
1821: owned a serveice request. */
1822: where aud.incident_audit_id = getaudit(curr_resp.incident_id,p_from_date,l_dt)
1823: /* commented out the below sql and replace by function getaudit to fix

Line 1826: from cs_incidents_audit_b aud_in

1822: where aud.incident_audit_id = getaudit(curr_resp.incident_id,p_from_date,l_dt)
1823: /* commented out the below sql and replace by function getaudit to fix
1824: * the performance issue 10630116
1825: ( select max(incident_audit_id)
1826: from cs_incidents_audit_b aud_in
1827: where aud_in.incident_id = curr_resp.incident_id
1828: and aud_in.creation_date < p_from_date
1829: and nvl(nvl(aud_in.old_inc_responded_by_date,aud_in.old_incident_resolved_date),l_dt) <>
1830: nvl(nvl(aud_in.inc_responded_by_date,aud_in.incident_resolved_date),l_dt)

Line 1842: from cs_incidents_audit_b aud_in

1838: and curr_resp.incident_audit_id = getaudit1(curr_resp.incident_id,p_from_date,p_to_date,l_dt)
1839: /* commented out the below sql and replace by function getaudit1 to fix
1840: * the performance issue 10630116
1841: ( select max(incident_audit_id)
1842: from cs_incidents_audit_b aud_in
1843: where aud_in.incident_id = curr_resp.incident_id
1844: and aud_in.creation_date between p_from_date
1845: and p_to_date
1846: and nvl(nvl(aud_in.old_inc_responded_by_date,aud_in.old_incident_resolved_date),l_dt) <>

Line 1875: (select max(incident_audit_id) from cs_incidents_audit_b x

1871: nvl(from_dttm.incident_date,trunc(sysdate-300))
1872: )
1873: */
1874: and from_dttm.incident_audit_id =
1875: (select max(incident_audit_id) from cs_incidents_audit_b x
1876: where x.incident_id = curr_resp.incident_id
1877: and ((nvl(x.old_incident_owner_id,-1) <>
1878: nvl(x.incident_owner_id,-1) and
1879: x.creation_date >= x.incident_date) or

Line 2237: from cs_incidents_audit_b;

2233: begin
2234: /**
2235: select min(creation_date) - 1
2236: into l_min_date
2237: from cs_incidents_audit_b;
2238: */
2239: delete from csy_response_resolutions;
2240: delete from csy_resolution_qlty;
2241: commit;