[Home] [Help]
148: p_ending_backlog number ,
149: p_sr_assigned number ,
150: p_sr_reassigned_to_others number ) is
151: begin
152: update csy_response_resolutions
153: set
154: total_response_time = nvl(total_response_time,0)+
155: nvl(p_response_time,0),
156: total_requests_responded = nvl(total_requests_responded,0)+
187: and owner_group_id = p_owner_group_id
188: and owner_type = p_owner_type
189: and incident_severity_id = p_incident_severity_id;
190: if (sql%notfound) then
191: insert into csy_response_resolutions
192: (summary_date,
193: incident_owner_id,
194: incident_severity_id,
195: owner_group_id,
335: --dbms_output.put_line('To date:'||to_char(p_to_date,'dd-mon-yyyy hh24:mi:ss'));
336: --dbms_output.put_line('End date:'||to_char(l_end_date,'dd-mon-yyyy hh24:mi:ss'));
337: -- These 2 updaes are needed because if backlog is reduced to 0 then c_backlog
338: -- cursor will not return any row and existing value will not be overwritten
339: update csy_response_resolutions
340: set ending_backlog = 0
341: where summary_date between trunc(p_from_date) and trunc(p_to_date);
342: update csy_response_resolutions
343: set beginning_backlog = 0
338: -- cursor will not return any row and existing value will not be overwritten
339: update csy_response_resolutions
340: set ending_backlog = 0
341: where summary_date between trunc(p_from_date) and trunc(p_to_date);
342: update csy_response_resolutions
343: set beginning_backlog = 0
344: where summary_date between trunc(p_from_date+1) and trunc(p_to_date+1);
345: loop
346: if (l_dt > l_end_date ) then exit; end if;
1260: l_incident_owner_id := l_owner_id;
1261: else
1262: l_owner_group_id := l_owner_id;
1263: end if;
1264: update csy_response_resolutions
1265: set total_sr_assigned = nvl(total_sr_assigned,0)+l_sr_in,
1266: total_sr_reassigned_to_others = nvl(total_sr_reassigned_to_others,0)+
1267: l_sr_out
1268: where summary_date = l_dt
1270: and owner_group_id = l_owner_group_id
1271: and owner_type = p_owner_type
1272: and incident_severity_id = l_sev_id;
1273: if (sql%notfound) then
1274: insert into csy_response_resolutions
1275: (summary_date,
1276: incident_owner_id,
1277: incident_severity_id,
1278: total_sr_assigned,
1538: p_to_date in date) is
1539: begin
1540: null;
1541: /* 10/13/2003 Not used
1542: merge into csy_response_resolutions a using (
1543: select trunc(aud.creation_date) summary_date,
1544: aud.group_id group_id ,
1545: incident_severity_id incident_severity_id,
1546: count(distinct aud.incident_id) sr_in,
2069: sum(total_resp_sla_missed ) resp_sla,
2070: sum(total_resl_sla_missed ) resl_sla,
2071: sum(beginning_backlog ) begblog,
2072: sum(ending_backlog ) endblog
2073: from csy_response_resolutions
2074: where summary_date between p_from_date and p_to_date
2075: and owner_type = 'A'
2076: group by owner_group_id,
2077: summary_date,
2077: summary_date,
2078: incident_severity_id;
2079: begin
2080: for l_rec in c_group_data loop
2081: update csy_response_resolutions
2082: set total_response_time = l_rec.resp_time,
2083: total_requests_responded = l_rec.req_resp,
2084: total_wait_on_agent_resp = l_rec.wait_on_agent_resp,
2085: total_wait_on_others_resp = l_rec.wait_on_others_resp,
2099: and owner_type = 'G'
2100: and incident_owner_id = -1
2101: and incident_severity_id = l_rec.incident_severity_id;
2102: if (sql%notfound) then
2103: insert into csy_response_resolutions
2104: (summary_date,
2105: incident_owner_id,
2106: incident_severity_id,
2107: owner_group_id,
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;
2242: l_min_date := trunc(sysdate) - 370;
2243: l_ret_val := fnd_profile.save('CS_CSY_LAST_PROGRAM_RUN_DATE',
2305: SUM(decode(resp.summary_date, per.end_date, ending_backlog, 0))
2306: ending_backlog,
2307: SUM(total_sr_assigned) total_sr_assigned,
2308: SUM(total_sr_reassigned_to_others) total_sr_reassigned_to_others
2309: FROM csy_response_resolutions resp,
2310: csy_periods_v per
2311: WHERE resp.summary_date BETWEEN per.start_date
2312: AND per.end_date
2313: AND owner_type = 'A'
2360: SUM(decode(resp.summary_date, per.end_date, ending_backlog, 0))
2361: ending_backlog,
2362: SUM(total_sr_assigned) total_sr_assigned,
2363: SUM(total_sr_reassigned_to_others) total_sr_reassigned_to_others
2364: FROM csy_response_resolutions resp,
2365: csy_periods_v per
2366: WHERE resp.summary_date BETWEEN per.start_date
2367: AND per.end_date
2368: AND owner_type = 'G'