APPS.BIX_DM_EMAIL_SUMMARY_PKG dependencies on BIX_DM_EMAIL_AGENT_SUM

Line 146: DELETE bix_dm_email_agent_sum

142:
143: /* Delete Email Agent Summary Table */
144:
145: LOOP
146: DELETE bix_dm_email_agent_sum
147: WHERE last_update_date > g_program_start_date
148: AND rownum <= g_commit_chunk_size ;
149:
150: IF(SQL%ROWCOUNT < g_commit_chunk_size) THEN

Line 359: DELETE bix_dm_email_agent_sum

355: END IF;
356: END LOOP;
357:
358: LOOP
359: DELETE bix_dm_email_agent_sum
360: WHERE period_start_date_time BETWEEN g_rounded_collect_start_date AND
361: g_rounded_collect_end_date
362: AND rownum <= g_commit_chunk_size ;
363:

Line 381: --dbms_output.put_line('BIX_DM_EMAIL_AGENT_SUM delete count :'|| l_email_agent_sum_delete_count);

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);
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: ' ||

Line 451: INSERT INTO bix_dm_email_agent_sum

447: --dbms_output.put_line('inside email Agent IF');
448: --
449: --This might be any of the agent events like REPLIED, DELETED etc
450: --
451: INSERT INTO bix_dm_email_agent_sum
452: (
453: email_agent_summary_id,
454: email_account_id,
455: classification_id,

Line 478: bix_dm_email_agent_sum_s.nextval,

474: program_update_date
475: )
476: VALUES
477: (
478: bix_dm_email_agent_sum_s.nextval,
479: emails.email_account_id,
480: emails.classification_id,
481: emails.resource_id,
482: emails.period_start_date,

Line 523: from bix_dm_email_agent_sum;

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;
524: --dbms_output.put_line('Inserted Count : ' || l_email_agent_sum_insert_count);
525:
526: /* If any error occurs delete all the rows inserted in this procedure
527: and raise an exception to outer calling procedure */

Line 1057: UPDATE bix_dm_email_agent_sum

1053: ||l_emails_open||' '||round(l_total_open_age/60)|| ' '||
1054: l_emails_opend || ' '|| l_oldest_open_message);
1055: */
1056:
1057: UPDATE bix_dm_email_agent_sum
1058: SET emails_open = NVL(emails_open,0) + l_emails_open,
1059: open_age = NVL(open_age,0) + l_total_open_age,
1060: emails_open_during_time_period = NVL(emails_open_during_time_period,0) + l_emails_opend,
1061: oldest_open_message = DECODE(l_oldest_open_message,NULL,oldest_open_message,

Line 1079: INSERT INTO bix_dm_email_agent_sum

1075: AND classification_id = o_measures.classification_id
1076: AND resource_id = l_resource_id;
1077:
1078: IF ( SQL%ROWCOUNT = 0) THEN
1079: INSERT INTO bix_dm_email_agent_sum
1080: (
1081: email_agent_summary_id,
1082: email_account_id,
1083: classification_id,

Line 1180: FROM bix_dm_email_agent_sum agt_sum,

1176: SUM(OPEN_AGE) open_age,
1177: SUM(EMAILS_OPEN_DURING_TIME_PERIOD) emails_open_during_time_period,
1178: SUM(EMC_RESPONSE_TIME) emc_response_time,
1179: SUM(COMPLETE_AGENT_RESPONSE_TIME) complete_agent_response_time
1180: FROM bix_dm_email_agent_sum agt_sum,
1181: jtf_rs_group_members groups,
1182: jtf_rs_groups_denorm group_denorm
1183: WHERE agt_sum.period_start_date_time BETWEEN g_rounded_collect_start_date AND g_rounded_collect_end_date
1184: AND agt_sum.resource_id = groups.resource_id

Line 1332: | In this procedure BIX_DM_EMAIL_AGENT_SUM is summarized to all the |

1328: END collect_group_summary;
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

Line 1361: FROM bix_dm_email_agent_sum agt_sum

1357: SUM(OPEN_AGE) open_age,
1358: SUM(EMAILS_OPEN_DURING_TIME_PERIOD) emails_open_during_time_period,
1359: SUM(EMC_RESPONSE_TIME) emc_response_time,
1360: SUM(COMPLETE_AGENT_RESPONSE_TIME) complete_agent_response_time
1361: FROM bix_dm_email_agent_sum agt_sum
1362: WHERE agt_sum.period_start_date_time BETWEEN g_rounded_collect_start_date AND g_rounded_collect_end_date
1363: GROUP BY EMAIL_ACCOUNT_ID,
1364: CLASSIFICATION_ID,
1365: PERIOD_START_DATE,