[Home] [Help]
155: l_rows_deleted := 0;
156:
157: loop
158:
159: if upper(table_name) = 'BIX_DM_CALL_SUM'
160: then
161: DELETE BIX_DM_CALL_SUM
162: WHERE period_start_date_time BETWEEN g_min_call_begin_date
163: AND g_max_call_begin_date
157: loop
158:
159: if upper(table_name) = 'BIX_DM_CALL_SUM'
160: then
161: DELETE BIX_DM_CALL_SUM
162: WHERE period_start_date_time BETWEEN g_min_call_begin_date
163: AND g_max_call_begin_date
164: AND rownum <= g_commit_chunk_size;
165: elsif upper(table_name) = 'BIX_DM_AGENT_SUM'
1358: --This is used while calculating the outcome/result/reason values
1359: --This will also be used to find out if we need to fill in the
1360: --call type row in the interface table with the measures.
1361: --If there are with agent segments then these will be added up
1362: --to form the values in BIX_DM_CALL_SUM else these will have to
1363: --be populated manually at the call type row.
1364: --ALso, if there are with agent segments then do not fill in the
1365: --measures at the call level as otherwise it will result in
1366: --double counting.
3575:
3576:
3577:
3578: -- Summarizing the interface table by call and inserting rows into
3579: -- the BIX_DM_CALL_SUM table
3580:
3581: PROCEDURE SUM_CALL AS
3582:
3583: CURSOR call_data IS
3686: END IF;
3687:
3688: -- Delete the rows from summary table for the date range specified
3689:
3690: DELETE_IN_CHUNKS('BIX_DM_CALL_SUM',
3691: 'period_start_date_time BETWEEN '||' to_date('||
3692: ''''||
3693: to_char(g_min_call_begin_date,'YYYY/MM/DDHH24:MI:SS')||
3694: ''''||
3703:
3704: COMMIT;
3705:
3706: IF (g_debug_flag = 'Y') THEN
3707: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'BIX_DM_AGENT_CALL_SUMMARY_PKG.CALL_SUM: '||' Finished Deleting rows in BIX_DM_CALL_SUM table: ' || 'Row Count:' || g_delete_count, g_proc_name);
3708:
3709: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'BIX_DM_AGENT_CALL_SUMMARY_PKG.SUM_CALL: '||' Start inserting rows into BIX_DM_CALL_SUM table: ', g_proc_name);
3710: END IF;
3711:
3705:
3706: IF (g_debug_flag = 'Y') THEN
3707: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'BIX_DM_AGENT_CALL_SUMMARY_PKG.CALL_SUM: '||' Finished Deleting rows in BIX_DM_CALL_SUM table: ' || 'Row Count:' || g_delete_count, g_proc_name);
3708:
3709: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||'BIX_DM_AGENT_CALL_SUMMARY_PKG.SUM_CALL: '||' Start inserting rows into BIX_DM_CALL_SUM table: ', g_proc_name);
3710: END IF;
3711:
3712:
3713: for call_row in call_data LOOP
3712:
3713: for call_row in call_data LOOP
3714:
3715:
3716: SELECT BIX_DM_CALL_SUM_S.NEXTVAL INTO l_bix_call_seq FROM DUAL;
3717:
3718: INSERT INTO BIX_DM_CALL_SUM
3719: (
3720: CALL_SUMMARY_ID,
3714:
3715:
3716: SELECT BIX_DM_CALL_SUM_S.NEXTVAL INTO l_bix_call_seq FROM DUAL;
3717:
3718: INSERT INTO BIX_DM_CALL_SUM
3719: (
3720: CALL_SUMMARY_ID,
3721: LAST_UPDATE_DATE,
3722: LAST_UPDATED_BY,
3890:
3891: EXCEPTION
3892: WHEN OTHERS THEN
3893: IF (g_debug_flag = 'Y') THEN
3894: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||' BIX_DM_AGENT_SUMMARY_PKG.SUM_CALL: '|| ' Failed to Rolled back data in BIX_DM_CALL_SUM table ', g_proc_name);
3895: END IF;
3896:
3897: g_proc_name := 'BIX_DM_AGENT_CALL_SUMMARY_PKG.SUM_CALL';
3898: g_error_mesg := g_proc_name ||' : '|| sqlerrm;
4328: | 1. GET_CALLS : Which populates the BIX_DM_INTERFACE staging table |
4329: | 2. AGENT_SUM : Summarizes agent data and inserts it into the BIX_DM_AGENT_SUM table |
4330: | 3. GROUP_SUM : Summarizes agent group data and inserts it into the BIX_DM_GROUP_SUM table|
4331: | 4. BIX_POP_AO_SUM_PKG.populate: Summarizes adv. outbound data and inserts it into the BIX_DM_AO_SUM tbl |
4332: | 5. CALL_SUM : Summarizes call data and inserts it into the BIX_DM_CALL_SUM table |
4333: | |
4334: | The procedure 1 populates BIX_DM_INTERFACE staging table, the others use the staging table to populate |
4335: | the other tables. After each procedure the insert_log procedure is called to insert the table data into |
4336: | the log table BIX_DM_COLLECT_LOG. |
4473: /* Procedure collects all the calls information for the GROUP data */
4474:
4475: SUM_CALL;
4476: g_status := 'SUCCESS';
4477: g_table_name := 'BIX_DM_CALL_SUM';
4478: insert_log;
4479:
4480: /* Advanced Outbound Data Population */
4481:
4548: | 1. GET_CALLS : Which populates the BIX_DM_INTERFACE staging table |
4549: | 2. AGENT_SUM : Summarizes agent data and inserts it into the BIX_DM_AGENT_SUM table |
4550: | 3. GROUP_SUM : Summarizes agent group data and inserts it into the BIX_DM_GROUP_SUM table|
4551: | 4. BIX_POP_AO_SUM_PKG.populate: Summarizes adv. outbound data and inserts it into the BIX_DM_AO_SUM tbl |
4552: | 5. CALL_SUM : Summarizes call data and inserts it into the BIX_DM_CALL_SUM table |
4553: | |
4554: | The procedure 1 populates BIX_DM_INTERFACE staging table, the others use the staging table to populate |
4555: | the other tables. After each procedure the insert_log procedure is called to insert the table data into |
4556: | the log table BIX_DM_COLLECT_LOG. |
4697: /* Procedure collects all the calls information for the CALL data */
4698:
4699: SUM_CALL;
4700: g_status := 'SUCCESS';
4701: g_table_name := 'BIX_DM_CALL_SUM';
4702: insert_log;
4703:
4704:
4705: /* Advanced Outbound Data Population */