eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BIX_DM_EMAIL_SUMMARY_PKG dependencies on BIX_DM_EMAIL_SUM

Line 1: PACKAGE BODY BIX_DM_EMAIL_SUMMARY_PKG AS

1: PACKAGE BODY BIX_DM_EMAIL_SUMMARY_PKG AS
2: /*$Header: bixxemcb.plb 120.1 2005/10/12 22:01:57 anasubra noship $ */
3:
4: g_request_id NUMBER;
5: g_program_appl_id NUMBER;

Line 47: ' BIX_DM_EMAIL_SUMMARY_PKG.INSERT_LOG:' ||

43: PROCEDURE INSERT_LOG
44: AS
45: BEGIN
46: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
47: ' BIX_DM_EMAIL_SUMMARY_PKG.INSERT_LOG:' ||
48: 'Start inserting collection status into BIX_DM_COLLECT_LOG table');
49:
50: /* Insert status into log table */
51:

Line 99: ' BIX_DM_EMAIL_SUMMARY_PKG.INSERT_LOG:'||

95: );
96: COMMIT;
97:
98: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
99: ' BIX_DM_EMAIL_SUMMARY_PKG.INSERT_LOG:'||
100: 'Finished inserting collection status into BIX_DM_COLLECT_LOG table');
101: EXCEPTION
102: WHEN OTHERS THEN
103: write_log( TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||

Line 104: 'BIX_DM_EMAIL_SUMMARY_PKG.INSERT_LOG: ' ||

100: 'Finished inserting collection status into BIX_DM_COLLECT_LOG table');
101: EXCEPTION
102: WHEN OTHERS THEN
103: write_log( TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
104: 'BIX_DM_EMAIL_SUMMARY_PKG.INSERT_LOG: ' ||
105: 'Failed to insert rows into BIX_DM_COLLECT_LOG table: '||sqlerrm);
106: RAISE;
107: END INSERT_LOG;
108:

Line 123: DELETE bix_dm_email_sum

119:
120: /* Delete from Email summary table */
121:
122: LOOP
123: DELETE bix_dm_email_sum
124: WHERE last_update_date > g_program_start_date
125: AND rownum <= g_commit_chunk_size ;
126:
127: IF(SQL%ROWCOUNT < g_commit_chunk_size) THEN

Line 137: ' BIX_DM_EMAIL_SUMMARY_PKG.CLEAN_UP: '||

133:
134: END LOOP;
135:
136: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
137: ' BIX_DM_EMAIL_SUMMARY_PKG.CLEAN_UP: '||
138: ' Finished Deleting rows in BIM_DM_EMAIL_SUM table: ' ||
139: 'Row Count:' || l_delete_count);
140:
141: l_delete_count := 0;

Line 160: ' BIX_DM_EMAIL_SUMMARY_PKG.CLEAN_UP: '||

156:
157: END LOOP;
158:
159: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
160: ' BIX_DM_EMAIL_SUMMARY_PKG.CLEAN_UP: '||
161: ' Finished Deleting rows in BIM_DM_EMAIL_AGENT_SUM table: ' ||
162: 'Row Count:' || l_delete_count);
163: l_delete_count := 0;
164:

Line 182: ' BIX_DM_EMAIL_SUMMARY_PKG.CLEAN_UP: '||

178:
179: END LOOP;
180:
181: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
182: ' BIX_DM_EMAIL_SUMMARY_PKG.CLEAN_UP: '||
183: ' Finished Deleting rows in BIM_DM_EMAIL_GROUP_SUM table: ' ||
184: 'Row Count:' || l_delete_count);
185: EXCEPTION
186: WHEN OTHERS THEN

Line 208: ' BIX_DM_EMAIL_SUMMARY_PKG.IS_EMC_INSTALLED: ERROR ' || sqlerrm);

204:
205: EXCEPTION
206: WHEN OTHERS THEN
207: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
208: ' BIX_DM_EMAIL_SUMMARY_PKG.IS_EMC_INSTALLED: ERROR ' || sqlerrm);
209: return 'N';
210: END;
211:
212: /*===========================================================================================+

Line 334: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAIL :'||

330:
331: --dbms_output.put_line('service level emc: '|| l_email_service_level );
332:
333: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
334: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAIL :'||
335: 'Start Deleting rows from Email summary tables');
336:
337: /* Delete from BIX_DM_EMAIL_SUM table where last_update_date
338: between g_rounded_collect_start_date and g_rounded_collect_end_date */

Line 337: /* Delete from BIX_DM_EMAIL_SUM table where last_update_date

333: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
334: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAIL :'||
335: 'Start Deleting rows from Email summary tables');
336:
337: /* Delete from BIX_DM_EMAIL_SUM table where last_update_date
338: between g_rounded_collect_start_date and g_rounded_collect_end_date */
339:
340: l_email_sum_delete_count := 0;
341:

Line 343: DELETE bix_dm_email_sum

339:
340: l_email_sum_delete_count := 0;
341:
342: LOOP
343: DELETE bix_dm_email_sum
344: WHERE period_start_date_time BETWEEN g_rounded_collect_start_date AND
345: g_rounded_collect_end_date
346: AND rownum <= g_commit_chunk_size ;
347:

Line 374: --dbms_output.put_line('BIX_DM_EMAIL_SUM Delete Count : '|| l_email_sum_delete_count);

370: COMMIT;
371: END IF;
372: END LOOP;
373:
374: --dbms_output.put_line('BIX_DM_EMAIL_SUM Delete Count : '|| l_email_sum_delete_count);
375:
376: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
377: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS: '||
378: ' Finished Deleting rows in BIM_DM_EMAIL_SUM table: ' ||

Line 377: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS: '||

373:
374: --dbms_output.put_line('BIX_DM_EMAIL_SUM Delete Count : '|| l_email_sum_delete_count);
375:
376: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
377: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS: '||
378: ' Finished Deleting rows in BIM_DM_EMAIL_SUM table: ' ||
379: 'Row Count:' || l_email_sum_delete_count);
380:
381: --dbms_output.put_line('BIX_DM_EMAIL_AGENT_SUM delete count :'|| l_email_agent_sum_delete_count);

Line 384: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS: '||

380:
381: --dbms_output.put_line('BIX_DM_EMAIL_AGENT_SUM delete count :'|| l_email_agent_sum_delete_count);
382:
383: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
384: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS: '||
385: ' Finished Deleting rows in BIM_DM_EMAIL_AGENT_SUM table: ' ||
386: 'Row Count:' || l_email_agent_sum_delete_count);
387:
388: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||

Line 389: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS: '||

385: ' Finished Deleting rows in BIM_DM_EMAIL_AGENT_SUM table: ' ||
386: 'Row Count:' || l_email_agent_sum_delete_count);
387:
388: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
389: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS: '||
390: ' Start collecting information into Email summary tables');
391:
392: /*
393: dbms_output.put_line('before for loop');

Line 405: /* insert the email summary information into the BIX_DM_EMAIL_SUM table */

401:
402: -- dbms_output.put_line('inside for loop');
403: IF( emails.resource_id IS NULL) THEN
404: --dbms_output.put_line('inside email IF');
405: /* insert the email summary information into the BIX_DM_EMAIL_SUM table */
406: --
407: --This might be a QUEUE rows OR a EMAILS_PROCESSING row or OFFERED.
408: --
409: INSERT INTO bix_dm_email_sum

Line 409: INSERT INTO bix_dm_email_sum

405: /* insert the email summary information into the BIX_DM_EMAIL_SUM table */
406: --
407: --This might be a QUEUE rows OR a EMAILS_PROCESSING row or OFFERED.
408: --
409: INSERT INTO bix_dm_email_sum
410: (
411: email_summary_id,
412: email_account_id,
413: classification_id,

Line 429: bix_dm_email_sum_s.nextval,

425: program_update_date
426: )
427: VALUES
428: (
429: bix_dm_email_sum_s.nextval,
430: emails.email_account_id,
431: emails.classification_id,
432: emails.period_start_date,
433: emails.period_start_time,

Line 515: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS: '||

511:
512: COMMIT;
513:
514: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
515: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS: '||
516: 'Finished Inserting rows into BIM_DM_EMAIL_SUM table ' );
517:
518: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
519: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS: '||

Line 519: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS: '||

515: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS: '||
516: 'Finished Inserting rows into BIM_DM_EMAIL_SUM table ' );
517:
518: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
519: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS: '||
520: 'Finished Inserting rows into BIM_DM_EMAIL_AGENT_SUM table ');
521:
522: select count(*) into l_email_agent_sum_insert_count
523: from bix_dm_email_agent_sum;

Line 661: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_QUEUE_MEASURES: '||

657: BEGIN
658:
659:
660: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
661: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_QUEUE_MEASURES: '||
662: ' Start collecting Queue measures ');
663:
664: FOR q_measures IN queue_measures LOOP
665: -- dbms_output.put_line('FOR LOOP media id:' || q_measures.media_id);

Line 716: UPDATE bix_dm_email_sum

712: END IF;
713:
714: l_counter := l_counter + 1;
715:
716: UPDATE bix_dm_email_sum
717: SET emails_remaining_in_queue = NVL(emails_remaining_in_queue,0) + l_emails_in_queue,
718: queue_time = NVL(queue_time,0) + l_total_queue_time,
719: max_queue_time = DECODE(l_total_queue_time, NULL,max_queue_time,
720: DECODE(max_queue_time,NULL,l_total_queue_time,

Line 736: INSERT INTO bix_dm_email_sum

732: AND email_account_id = q_measures.email_account_id
733: AND classification_id = q_measures.classification_id;
734:
735: IF ( SQL%ROWCOUNT = 0) THEN
736: INSERT INTO bix_dm_email_sum
737: (
738: email_summary_id,
739: email_account_id,
740: classification_id,

Line 760: bix_dm_email_sum_s.nextval,

756: program_update_date
757: )
758: VALUES
759: (
760: bix_dm_email_sum_s.nextval,
761: q_measures.email_account_id,
762: q_measures.classification_id,
763: TRUNC(l_begin_bucket_date),
764: TO_CHAR(l_begin_bucket_date,'HH24:MI'),

Line 794: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_QUEUE_MEASURES: '||

790: COMMIT;
791:
792:
793: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
794: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_QUEUE_MEASURES: '||
795: ' Finished collecting Queue Measures');
796:
797: EXCEPTION
798: WHEN OTHERS THEN

Line 951: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_OPEN_MEASURES: '||

947: BEGIN
948:
949:
950: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
951: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_OPEN_MEASURES: '||
952: ' Start collecting Open Measures ');
953:
954: FOR o_measures IN open_measures LOOP
955: --dbms_output.put_line('FOR LOOP media id:' || o_measures.media_id);

Line 1104: bix_dm_email_sum_s.nextval,

1100: program_update_date
1101: )
1102: VALUES
1103: (
1104: bix_dm_email_sum_s.nextval,
1105: o_measures.email_account_id,
1106: o_measures.classification_id,
1107: NVL(l_resource_id,-999),
1108: TRUNC(l_begin_bucket_date),

Line 1140: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_OPEN_MEASURES: '||

1136: END LOOP; -- End of Cursor for loop
1137: COMMIT;
1138:
1139: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
1140: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_OPEN_MEASURES: '||
1141: 'Finished Collecting Open Measures ');
1142:
1143: EXCEPTION
1144: WHEN OTHERS THEN

Line 1212: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_GROUP_SUMMARY: '||

1208: l_counter NUMBER := 0;
1209:
1210: BEGIN
1211: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
1212: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_GROUP_SUMMARY: '||
1213: ' Start collecting information into Agent Group Email summary table');
1214:
1215: /* Delete the rows from Group summary table for the given date range and re collect the rows from
1216: Agent summary table.

Line 1221: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_GROUP_SUMMARY: '||

1217: */
1218:
1219:
1220: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
1221: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_GROUP_SUMMARY: '||
1222: ' Start Deleting the rows from Agent Group Email summary table ');
1223:
1224: LOOP
1225: DELETE bix_dm_email_group_sum

Line 1241: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_GROUP_SUMMARY: '||

1237: END IF;
1238: END LOOP;
1239:
1240: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
1241: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_GROUP_SUMMARY: '||
1242: ' finished Deleting the rows from Agent Group Email summary table '||
1243: 'Row Count: '|| l_counter);
1244:
1245: l_counter := 0;

Line 1319: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_GROUP_SUMMARY: '||

1315: END LOOP; -- End of cursor for loop
1316: COMMIT;
1317:
1318: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
1319: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_GROUP_SUMMARY: '||
1320: ' finished collecting information into Agent Group Email summary table ' ||
1321: 'Row Count: '|| l_counter );
1322:
1323: EXCEPTION

Line 1333: | dimensions other than resource and update the BIX_DM_EMAIL_SUM table|

1329:
1330:
1331: /*====================================================================+
1332: | In this procedure BIX_DM_EMAIL_AGENT_SUM is summarized to all the |
1333: | dimensions other than resource and update the BIX_DM_EMAIL_SUM table|
1334: ======================================================================*/
1335:
1336: PROCEDURE collect_agent_summary
1337: AS

Line 1373: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_AGENT_SUMMARY: '||

1369: l_counter NUMBER := 0;
1370:
1371: BEGIN
1372: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
1373: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_AGENT_SUMMARY: '||
1374: ' Start updating agent information into Email summary table');
1375:
1376: l_counter := l_counter + 1;
1377:

Line 1381: UPDATE bix_dm_email_sum

1377:
1378: FOR agent_sum IN agent_email_sum LOOP
1379:
1380:
1381: UPDATE bix_dm_email_sum
1382: SET emails_fetched = agent_sum.emails_fetched,
1383: emails_replied = agent_sum.emails_replied,
1384: emails_replied_within_goal = agent_sum.emails_replied_within_goal,
1385: emails_deleted = agent_sum.emails_deleted,

Line 1400: INSERT INTO bix_dm_email_sum

1396: AND period_start_date_time = agent_sum.period_start_date_time;
1397:
1398: IF(SQL%ROWCOUNT = 0) THEN
1399:
1400: INSERT INTO bix_dm_email_sum
1401: (
1402: email_summary_id,
1403: email_account_id,
1404: classification_id,

Line 1431: bix_dm_email_sum_s.nextval,

1427: program_update_date
1428: )
1429: VALUES
1430: (
1431: bix_dm_email_sum_s.nextval,
1432: agent_sum.email_account_id,
1433: agent_sum.classification_id,
1434: agent_sum.period_start_date,
1435: agent_sum.period_start_time,

Line 1468: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_GROUP_SUMMARY: '||

1464: END LOOP; -- End of cursor for loop
1465: COMMIT;
1466:
1467: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
1468: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_GROUP_SUMMARY: '||
1469: ' finished updating agent information into Email summary table ' );
1470:
1471: EXCEPTION
1472: WHEN OTHERS THEN

Line 1509: g_proc_name := 'BIX_DM_EMAIL_SUMMARY_PKG';

1505: g_user_id := FND_GLOBAL.USER_ID();
1506: g_program_start_date := SYSDATE;
1507: g_error_mesg := NULL;
1508: g_status := 'FAILED';
1509: g_proc_name := 'BIX_DM_EMAIL_SUMMARY_PKG';
1510: g_commit_chunk_size := 5;
1511:
1512: g_collect_start_date := TO_DATE(p_start_date,'YYYY/MM/DD HH24:MI:SS');
1513: g_collect_end_date := TO_DATE(p_end_date,'YYYY/MM/DD HH24:MI:SS');

Line 1613: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS_SUMMARY : ' ||

1609: g_status := 'SUCCESS';
1610: insert_log;
1611: ELSE
1612: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
1613: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS_SUMMARY : ' ||
1614: 'Email Application is not installed. ');
1615: END IF;
1616: EXCEPTION
1617: WHEN G_DATE_MISMATCH THEN

Line 1666: g_proc_name := 'BIX_DM_EMAIL_SUMMARY_PKG';

1662: g_user_id := FND_GLOBAL.USER_ID();
1663: g_program_start_date := SYSDATE;
1664: g_error_mesg := NULL;
1665: g_status := 'FAILED';
1666: g_proc_name := 'BIX_DM_EMAIL_SUMMARY_PKG';
1667: g_commit_chunk_size := 100;
1668:
1669: g_collect_start_date := TO_DATE(p_start_date,'YYYY/MM/DD HH24:MI:SS');
1670: g_collect_end_date := TO_DATE(p_end_date,'YYYY/MM/DD HH24:MI:SS');

Line 1771: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS_SUMMARY : ' ||

1767: retcode := NULL;
1768: errbuf := NULL;
1769: ELSE
1770: write_log(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')||
1771: ' BIX_DM_EMAIL_SUMMARY_PKG.COLLECT_EMAILS_SUMMARY : ' ||
1772: 'Email Application is not installed. ');
1773: END IF;
1774:
1775: EXCEPTION

Line 1792: END BIX_DM_EMAIL_SUMMARY_PKG;

1788: RAISE;
1789: END IF;
1790: END COLLECT_EMAILS_SUMMARY;
1791:
1792: END BIX_DM_EMAIL_SUMMARY_PKG;