[Home] [Help]
138: --Exception occured: Delete all data which was inserted in this run
139: --
140: LOOP
141:
142: DELETE FROM bix_dm_uwq_agent_sum
143: WHERE last_update_date >= v_sysdate
144: AND rownum <= v_delete_size;
145:
146: IF SQL%ROWCOUNT >= v_delete_size --this means we need to loop again
181:
182: write_log(v_proc_name, v_message);
183:
184: --Create error log table entry for agent summary
185: v_table_name := 'BIX_DM_UWQ_AGENT_SUM';
186: v_insert_count := 0;
187: v_delete_count := 0;
188: insert_log_table;
189:
203: p_end_date IN DATE
204: )
205:
206: --
207: --This procedure populates the BIX_DM_UWQ_AGENT_SUM table.
208: --
209: IS
210:
211: v_insert_date DATE;
268: v_insert_date := p_start_date;
269: v_insert_count := 0;
270: v_delete_count := 0;
271: v_proc_name := 'POPULATE_AGENTS';
272: v_table_name := 'BIX_DM_UWQ_AGENT_SUM';
273: v_run_start_date := sysdate;
274:
275: --
276: --Delete data between these dates and re-compute
277: --
278:
279: LOOP
280:
281: DELETE FROM bix_dm_uwq_agent_sum
282: WHERE day BETWEEN p_start_date AND p_end_date
283: AND rownum <= v_delete_size;
284:
285: IF SQL%ROWCOUNT >= v_delete_size --this means we need to loop again
325: FOR rec_days IN c_days(v_insert_date)
326: LOOP
327:
328:
329: INSERT INTO BIX_DM_UWQ_AGENT_SUM
330: (
331: RESOURCE_ID, DAY, DAY_LOGIN, DAY_DURATION, DAY1_LOGIN,
332: DAY1_DURATION, DAY2_LOGIN, DAY2_DURATION, DAY3_LOGIN, DAY3_DURATION,
333: DAY4_LOGIN, DAY4_DURATION, DAY5_LOGIN, DAY5_DURATION, DAY6_LOGIN,
359: --Select information for last week from summary table. If it doesnt exist, re-compute.
360: --
361: SELECT current_week_login, current_week_duration
362: INTO v_prior_week_login, v_prior_week_duration
363: FROM bix_dm_uwq_agent_sum
364: WHERE resource_id = rec_days.resource_id
365: AND day = v_week_start_date-1;
366:
367: EXCEPTION
401: BEGIN
402:
403: SELECT (rec_days.day_login+current_week_login), (rec_days.day_duration+current_week_duration)
404: INTO v_current_week_login, v_current_week_duration
405: FROM bix_dm_uwq_agent_sum
406: WHERE resource_id = rec_days.resource_id
407: AND day = trunc(rec_days.day)-1;
408:
409: EXCEPTION
458: --If data exists for the last day of the previous month in the summary table, use that
459: --
460: SELECT current_month_login, current_month_duration
461: INTO v_prior_month_login, v_prior_month_duration
462: FROM bix_dm_uwq_agent_sum
463: WHERE resource_id = rec_days.resource_id
464: AND day = last_day(add_months(v_insert_date,-1));
465:
466: EXCEPTION
505: BEGIN
506:
507: SELECT rec_days.day_login+current_month_login, rec_days.day_duration+current_month_duration
508: INTO v_current_month_login, v_current_month_duration
509: FROM bix_dm_uwq_agent_sum
510: WHERE resource_id = rec_days.resource_id
511: AND day = v_insert_date-1;
512:
513: EXCEPTION
557: --
558: --Update week and month information
559: --
560:
561: UPDATE bix_dm_uwq_agent_sum
562: SET prior_week_login = v_prior_week_login,
563: prior_week_duration = v_prior_week_duration,
564: current_week_login = v_current_week_login,
565: current_week_duration = v_current_week_duration,
590: END LOOP; -- for date
591:
592: v_run_end_date := sysdate;
593: v_status := 'SUCCESS';
594: v_message := 'Successfully populated bix_dm_uwq_agent_sum';
595: write_log(v_proc_name, v_message);
596: insert_log_table;
597:
598: COMMIT; --commit after all rows are inserted in bix_dm_uwq_agent_sum
594: v_message := 'Successfully populated bix_dm_uwq_agent_sum';
595: write_log(v_proc_name, v_message);
596: insert_log_table;
597:
598: COMMIT; --commit after all rows are inserted in bix_dm_uwq_agent_sum
599:
600: EXCEPTION
601: WHEN OTHERS
602: THEN
604: --Exception occurred.
605: --Raise exception and pass ccontrol to the calling procedure where we will perform the deletes.
606: --
607: v_status := 'FAILURE';
608: v_message := 'Failed while populating bix_dm_uwq_agent_sum '|| sqlerrm;
609: v_run_end_date := sysdate;
610: write_log(v_proc_name, v_message);
611: RAISE;
612:
620:
621: --
622: --This procedure populates the BIX_DM_UWQ_GROUP_SUM table.
623: --There is no need to go after the OLTP tables. Instead, the
624: --calculations are based off the BIX_DM_UWQ_AGENT_SUM table.
625: --
626: IS
627:
628: v_insert_date DATE;
656: CURSOR c_all_groups
657: IS
658: select DISTINCT denorm.parent_group_id group_id
659: from jtf_rs_group_members mem, jtf_rs_groups_denorm denorm,
660: bix_dm_uwq_agent_sum summ
661: where mem.group_id = denorm.group_id
662: and summ.resource_id = mem.resource_id;
663:
664: BEGIN
724:
725: v_insert_count := v_insert_count + sql%rowcount;
726:
727: --
728: --Compute information using BIX_DM_UWQ_AGENT_SUM and store in variables.
729: --
730: SELECT sum(day_login), sum(day_duration), sum(day1_login), sum(day1_duration),
731: sum(day2_login), sum(day2_duration), sum(day3_login), sum(day3_duration),
732: sum(day4_login), sum(day4_duration), sum(day5_login), sum(day5_duration),
742: v_prior_week_login, v_prior_week_duration,
743: v_current_week_login, v_current_week_duration,
744: v_prior_month_login, v_prior_month_duration,
745: v_current_month_login, v_current_month_duration
746: FROM bix_dm_uwq_agent_sum agent
747: WHERE agent.resource_id IN (
748: select grp.resource_id
749: from jtf_rs_groups_denorm denorm, jtf_rs_group_members grp
750: where denorm.parent_group_id = rec_groups.group_id
811: --Raise the exception to the calling procedure where we will perform the deletes.
812: --
813:
814: v_status := 'FAILURE';
815: v_message := 'Failed while populating bix_dm_uwq_agent_sum '|| sqlerrm;
816: v_run_end_date := sysdate;
817: write_log(v_proc_name, v_message);
818: RAISE;
819: