[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_delete_count NUMBER := 0;
g_insert_count NUMBER := 0;
| INSERT_LOG procedure inserts collection concurrent program status into BIX_DM_COLLECT_LOG table |
| It inserts a row with the following details : |
| |
| COLLECT_STATUS column equals to FAILURE if the program failed otherwise SUCCESS |
| COLLECT_EXCEP_MESG as error message if the program failed otherwise NULL |
| RUN_START_DATE equals to start date time when the collection program started runnning |
| RUN_END_DATE equals end date time of the collection program finished |
| COLLECT_START_DATE Collection start date specified by the user in the cuncurrent program parameter |
| COLLECT_END_DATE Collection end date specified by the user in the cuncurrent program parameter |
====================================================================================================+*/
PROCEDURE INSERT_LOG
AS
BEGIN
fnd_file.put_line(fnd_file.log,TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
' BIX_POP_AO_SUM_PKG.INSERT_LOG:' ||
'Start inserting collection status into BIX_DM_COLLECT_LOG table');
/* Insert status into log table */
INSERT INTO BIX_DM_COLLECT_LOG
(
COLLECT_ID,
COLLECT_CONCUR_ID,
OBJECT_NAME,
OBJECT_TYPE,
RUN_START_DATE,
RUN_END_DATE,
COLLECT_START_DATE,
COLLECT_END_DATE,
COLLECT_STATUS,
COLLECT_EXCEP_MESG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ROWS_INSERTED,
ROWS_DELETED,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
VALUES
(
BIX_DM_COLLECT_LOG_S.NEXTVAL,
NULL,
'BIX_DM_ADVANCED_OUTBOUND_SUM',
'TABLE',
g_program_start_date,
SYSDATE,
g_collect_start_date,
g_collect_end_date,
g_status,
g_error_mesg,
SYSDATE,
g_user_id,
SYSDATE,
g_user_id,
g_user_id,
g_insert_count,
g_delete_count,
g_request_id,
g_program_appl_id,
g_program_id,
SYSDATE
);
' BIX_POP_AO_SUM_PKG.INSERT_LOG:'||
'Finished inserting collection status into BIX_DM_COLLECT_LOG table');
'BIX_POP_AO_SUM_PKG.INSERT_LOG: ' ||
' Failed to insert rows into BIX_DM_COLLECT_LOG table: '|| sqlerrm);
END INSERT_LOG;
| summary tables and also calls INSERT_LOG procedure to log error messge in BIX_DM_COLLECT_LOG table |
+===================================================================================================*/
PROCEDURE clean_up IS
BEGIN
fnd_file.put_line(fnd_file.log,TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||g_error_mesg);
/* Delete from AO summary table */
LOOP
DELETE bix_dm_advanced_outbound_sum
WHERE last_update_date > g_program_start_date
AND rownum <= g_commit_chunk_size ;
INSERT_LOG;
SELECT
campaign_schedule_id,
source_list_id ,
sublist_id,
period_start_date,
period_start_time,
period_start_date_time,
COUNT(DISTINCT resource_id) agent_work_on,
SUM(DECODE(row_type,'C',1,0)) DIALED,
SUM(DECODE(outcome_id,7,1,0)) connected,
SUM(DECODE(outcome_id,11,1,0)) abandoned,
SUM(DECODE(outcome_id,2,1,0)) busy,
SUM(DECODE(outcome_id,1,1,0)) ring_no_ansewr,
SUM(DECODE(outcome_id,6,1,0)) answering_machine,
SUM(DECODE(outcome_id,22,1,23,1,24,1,25,1,0)) sit,
SUM(out_talk_time) talk_time,
SUM(out_wrap_time) wrap_time,
SUM(leads_created) leads_created,
SUM(LEADS_AMOUNT) leads_amount,
SUM(LEADS_AMOUNT_TXN) leads_amount_txn,
SUM(OPPORTUNITIES_WON + OPPORTUNITIES_CROSS_SOLD + OPPORTUNITIES_UP_SOLD) OPPORTUNITIES_WON,
SUM(OPPORTUNITIES_WON_AMOUNT) OPPORTUNITIES_WON_AMOUNT,
SUM(OPPORTUNITIES_WON_AMOUNT_TXN) OPPORTUNITIES_WON_AMOUNT_TXN
FROM BIX_DM_INTERFACE
WHERE direction = 1 --OUTBOUND
AND period_start_date_time BETWEEN p_start_date_time AND p_end_date_time
GROUP BY
campaign_schedule_id,
source_list_id ,
sublist_id,
period_start_date,
period_start_time,
period_start_date_time;
IF (FND_PROFILE.DEFINED('BIX_DM_DELETE_SIZE')) THEN
g_commit_chunk_size := TO_NUMBER(FND_PROFILE.VALUE('BIX_DM_DELETE_SIZE'));
DELETE bix_dm_advanced_outbound_sum
WHERE PERIOD_START_DATE_TIME >= p_start_date_time
AND PERIOD_START_DATE_TIME <= p_end_date_time
AND rownum <= g_commit_chunk_size ;
g_delete_count := g_delete_count + SQL%ROWCOUNT;
g_delete_count := g_delete_count + SQL%ROWCOUNT;
'Row count: ' || g_delete_count );
' Start inserting rows in BIM_DM_AO_SUM table: ' );
INSERT INTO BIX_DM_ADVANCED_OUTBOUND_SUM
(
ADVANCED_OUTBOUND_ID,
CAMPAIGN_SCHEDULE_ID,
SOURCE_LIST_ID,
SUBLIST_ID,
PERIOD_START_DATE,
PERIOD_START_TIME,
PERIOD_START_DATE_TIME,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
AGENT_WORK_ON,
DIALED_COUNT,
CONNECTED_COUNT,
ABANDONED_COUNT,
BUSY_COUNT,
RING_NO_ANSWER_COUNT,
ANSWERING_MACHINE_COUNT,
SIT_COUNT,
TALK_TIME,
WRAP_TIME,
LEADS_CREATED,
LEADS_AMOUNT,
LEADS_AMOUNT_TXN,
OPPORTUNITIES_WON,
OPPORTUNITIES_WON_AMOUNT,
OPPORTUNITIES_WON_AMOUNT_TXN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
VALUES (
bix_dm_advanced_outbound_sum_s.NEXTVAL,
l_campaign_schedule_id(i),
l_source_list_id(i),
l_sublist_id(i),
l_period_start_date(i),
l_period_start_time(i),
l_period_start_date_time(i),
SYSDATE,
g_user_id ,
SYSDATE,
g_user_id,
l_agent_work_on(i),
l_dialed(i),
l_connected(i),
l_abandoned(i),
l_busy(i),
l_ring_no_answer(i),
l_answering_machine(i),
l_sit(i),
l_talk_time(i),
l_wrap_time(i),
l_leads_created(i),
l_leads_amount(i),
l_leads_amount_txn(i),
l_opportunities_won(i),
l_opportunities_won_amount(i),
l_opportunities_won_amount_txn(i),
g_request_id,
g_program_appl_id,
g_program_id,
SYSDATE
);
g_insert_count := g_insert_count + l_campaign_schedule_id.COUNT;
' finished inserting rows in BIM_DM_AO_SUM table: ' );
insert_log;