APPS.CSY_KPI_PKG dependencies on CS_INCIDENTS_ALL_B

Line 290: cs_incidents_all_b sr

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
294: and incident_audit_id =

Line 425: cs_incidents_all_b sr

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)
429: and aud.incident_audit_id =

Line 462: cs_incidents_all_b sr

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 =
465: ( select max(incident_audit_id)
466: from cs_incidents_audit_b aud_in

Line 554: cs_incidents_all_b sr -- only for incident_date */

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 */
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

Line 656: cs_incidents_all_b sr -- only for incident_date */

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 */
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

Line 721: l_owner_id cs_incidents_all_b.incident_owner_id % type;

717: aud.group_id,
718: aud.incident_severity_id,
719: trunc(aud.incident_resolved_date)
720: ;
721: l_owner_id cs_incidents_all_b.incident_owner_id % type;
722: l_sev_id cs_incidents_all_b.incident_severity_id % type;
723: l_group_id cs_incidents_all_b.owner_group_id % type;
724: l_summ_dt cs_incidents_all_b.close_date % type;
725: l_resl_time number;

Line 722: l_sev_id cs_incidents_all_b.incident_severity_id % type;

718: aud.incident_severity_id,
719: trunc(aud.incident_resolved_date)
720: ;
721: l_owner_id cs_incidents_all_b.incident_owner_id % type;
722: l_sev_id cs_incidents_all_b.incident_severity_id % type;
723: l_group_id cs_incidents_all_b.owner_group_id % type;
724: l_summ_dt cs_incidents_all_b.close_date % type;
725: l_resl_time number;
726: l_no_of_resp number;

Line 723: l_group_id cs_incidents_all_b.owner_group_id % type;

719: trunc(aud.incident_resolved_date)
720: ;
721: l_owner_id cs_incidents_all_b.incident_owner_id % type;
722: l_sev_id cs_incidents_all_b.incident_severity_id % type;
723: l_group_id cs_incidents_all_b.owner_group_id % type;
724: l_summ_dt cs_incidents_all_b.close_date % type;
725: l_resl_time number;
726: l_no_of_resp number;
727: l_wait_on_support number;

Line 724: l_summ_dt cs_incidents_all_b.close_date % type;

720: ;
721: l_owner_id cs_incidents_all_b.incident_owner_id % type;
722: l_sev_id cs_incidents_all_b.incident_severity_id % type;
723: l_group_id cs_incidents_all_b.owner_group_id % type;
724: l_summ_dt cs_incidents_all_b.close_date % type;
725: l_resl_time number;
726: l_no_of_resp number;
727: l_wait_on_support number;
728: l_wait_on_customer number;

Line 884: l_owner_id cs_incidents_all_b.incident_owner_id % type;

880: ) as
881: Type Resolution_cursor_type is ref cursor;
882: c_first_resolutions Resolution_cursor_type;
883: l_dt date ;
884: l_owner_id cs_incidents_all_b.incident_owner_id % type;
885: l_sev_id cs_incidents_all_b.incident_severity_id % type;
886: l_resol_dt cs_incidents_all_b.incident_date % type;
887: l_inv_item_id cs_incidents_all_b.inventory_item_id % type;
888: l_inv_org_id cs_incidents_all_b.inv_organization_id % type;

Line 885: l_sev_id cs_incidents_all_b.incident_severity_id % type;

881: Type Resolution_cursor_type is ref cursor;
882: c_first_resolutions Resolution_cursor_type;
883: l_dt date ;
884: l_owner_id cs_incidents_all_b.incident_owner_id % type;
885: l_sev_id cs_incidents_all_b.incident_severity_id % type;
886: l_resol_dt cs_incidents_all_b.incident_date % type;
887: l_inv_item_id cs_incidents_all_b.inventory_item_id % type;
888: l_inv_org_id cs_incidents_all_b.inv_organization_id % type;
889: l_prob_code cs_incidents_all_b.problem_code % type;

Line 886: l_resol_dt cs_incidents_all_b.incident_date % type;

882: c_first_resolutions Resolution_cursor_type;
883: l_dt date ;
884: l_owner_id cs_incidents_all_b.incident_owner_id % type;
885: l_sev_id cs_incidents_all_b.incident_severity_id % type;
886: l_resol_dt cs_incidents_all_b.incident_date % type;
887: l_inv_item_id cs_incidents_all_b.inventory_item_id % type;
888: l_inv_org_id cs_incidents_all_b.inv_organization_id % type;
889: l_prob_code cs_incidents_all_b.problem_code % type;
890: l_resol_code cs_incidents_all_b.resolution_code % type;

Line 887: l_inv_item_id cs_incidents_all_b.inventory_item_id % type;

883: l_dt date ;
884: l_owner_id cs_incidents_all_b.incident_owner_id % type;
885: l_sev_id cs_incidents_all_b.incident_severity_id % type;
886: l_resol_dt cs_incidents_all_b.incident_date % type;
887: l_inv_item_id cs_incidents_all_b.inventory_item_id % type;
888: l_inv_org_id cs_incidents_all_b.inv_organization_id % type;
889: l_prob_code cs_incidents_all_b.problem_code % type;
890: l_resol_code cs_incidents_all_b.resolution_code % type;
891: l_sr_resolved number;

Line 888: l_inv_org_id cs_incidents_all_b.inv_organization_id % type;

884: l_owner_id cs_incidents_all_b.incident_owner_id % type;
885: l_sev_id cs_incidents_all_b.incident_severity_id % type;
886: l_resol_dt cs_incidents_all_b.incident_date % type;
887: l_inv_item_id cs_incidents_all_b.inventory_item_id % type;
888: l_inv_org_id cs_incidents_all_b.inv_organization_id % type;
889: l_prob_code cs_incidents_all_b.problem_code % type;
890: l_resol_code cs_incidents_all_b.resolution_code % type;
891: l_sr_resolved number;
892: l_sr_reopen number;

Line 889: l_prob_code cs_incidents_all_b.problem_code % type;

885: l_sev_id cs_incidents_all_b.incident_severity_id % type;
886: l_resol_dt cs_incidents_all_b.incident_date % type;
887: l_inv_item_id cs_incidents_all_b.inventory_item_id % type;
888: l_inv_org_id cs_incidents_all_b.inv_organization_id % type;
889: l_prob_code cs_incidents_all_b.problem_code % type;
890: l_resol_code cs_incidents_all_b.resolution_code % type;
891: l_sr_resolved number;
892: l_sr_reopen number;
893: l_sr_reopen2 number;

Line 890: l_resol_code cs_incidents_all_b.resolution_code % type;

886: l_resol_dt cs_incidents_all_b.incident_date % type;
887: l_inv_item_id cs_incidents_all_b.inventory_item_id % type;
888: l_inv_org_id cs_incidents_all_b.inv_organization_id % type;
889: l_prob_code cs_incidents_all_b.problem_code % type;
890: l_resol_code cs_incidents_all_b.resolution_code % type;
891: l_sr_resolved number;
892: l_sr_reopen number;
893: l_sr_reopen2 number;
894: begin

Line 1006: cs_incidents_all_b sr

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
1010: -- set from null to NOT NULL

Line 1070: cs_incidents_all_b sr

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
1074: and last_unrslvd.incident_resolved_date is null

Line 1141: cs_incidents_all_b sr

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
1144: and curr_unrslvd.old_incident_resolved_date is not null
1145: and curr_unrslvd.incident_resolved_date is null

Line 1232: l_incident_owner_id cs_incidents_all_b.incident_owner_id % type;

1228: l_sr_in number;
1229: l_sr_out number;
1230: Type AssignmentCursorType is ref cursor;
1231: c_agent_assignment AssignmentCursorType;
1232: l_incident_owner_id cs_incidents_all_b.incident_owner_id % type;
1233: l_owner_group_id cs_incidents_all_b.owner_group_id % type;
1234: begin
1235: if (p_load_type = 'ADD' ) then
1236: open c_agent_assignment for p_sql using

Line 1233: l_owner_group_id cs_incidents_all_b.owner_group_id % type;

1229: l_sr_out number;
1230: Type AssignmentCursorType is ref cursor;
1231: c_agent_assignment AssignmentCursorType;
1232: l_incident_owner_id cs_incidents_all_b.incident_owner_id % type;
1233: l_owner_group_id cs_incidents_all_b.owner_group_id % type;
1234: begin
1235: if (p_load_type = 'ADD' ) then
1236: open c_agent_assignment for p_sql using
1237: p_from_date, p_to_date,

Line 1890: l_owner_id cs_incidents_all_b.incident_owner_id % type;

1886: aud.group_id,
1887: aud.incident_severity_id,
1888: trunc(nvl(aud.inc_responded_by_date,aud.incident_resolved_date))
1889: ;
1890: l_owner_id cs_incidents_all_b.incident_owner_id % type;
1891: l_sev_id cs_incidents_all_b.incident_severity_id % type;
1892: l_group_id cs_incidents_all_b.owner_group_id % type;
1893: l_summ_dt cs_incidents_all_b.close_date % type;
1894: l_resp_time number;

Line 1891: l_sev_id cs_incidents_all_b.incident_severity_id % type;

1887: aud.incident_severity_id,
1888: trunc(nvl(aud.inc_responded_by_date,aud.incident_resolved_date))
1889: ;
1890: l_owner_id cs_incidents_all_b.incident_owner_id % type;
1891: l_sev_id cs_incidents_all_b.incident_severity_id % type;
1892: l_group_id cs_incidents_all_b.owner_group_id % type;
1893: l_summ_dt cs_incidents_all_b.close_date % type;
1894: l_resp_time number;
1895: l_no_of_resp number;

Line 1892: l_group_id cs_incidents_all_b.owner_group_id % type;

1888: trunc(nvl(aud.inc_responded_by_date,aud.incident_resolved_date))
1889: ;
1890: l_owner_id cs_incidents_all_b.incident_owner_id % type;
1891: l_sev_id cs_incidents_all_b.incident_severity_id % type;
1892: l_group_id cs_incidents_all_b.owner_group_id % type;
1893: l_summ_dt cs_incidents_all_b.close_date % type;
1894: l_resp_time number;
1895: l_no_of_resp number;
1896: l_wait_on_me number;

Line 1893: l_summ_dt cs_incidents_all_b.close_date % type;

1889: ;
1890: l_owner_id cs_incidents_all_b.incident_owner_id % type;
1891: l_sev_id cs_incidents_all_b.incident_severity_id % type;
1892: l_group_id cs_incidents_all_b.owner_group_id % type;
1893: l_summ_dt cs_incidents_all_b.close_date % type;
1894: l_resp_time number;
1895: l_no_of_resp number;
1896: l_wait_on_me number;
1897: l_wait_on_others number;