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_EMAILS_SUMMARY_PKG dependencies on BIX_EMAIL_DETAILS_STG

Line 136: /* Truncate_Table('BIX_EMAIL_DETAILS_STG'); */

132:
133: write_log('BIX Schema : ' || g_bix_schema);
134: write_log('Finished procedure init at : ' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
135:
136: /* Truncate_Table('BIX_EMAIL_DETAILS_STG'); */
137:
138: EXCEPTION
139: WHEN OTHERS THEN
140: write_log('Error in init : Error : ' || sqlerrm);

Line 1076: MERGE INTO BIX_EMAIL_DETAILS_STG fact

1072: --to avoid issues with ROLLUP
1073: --
1074:
1075:
1076: MERGE INTO BIX_EMAIL_DETAILS_STG fact
1077: USING
1078: (
1079: SELECT
1080: inv2.email_account_id

Line 1458: write_log('Number of rows updated in table bix_email_details_stg for Emails Fetched and Replied ' || to_char(SQL%ROWCOUNT));

1454: decode(change.emails_rerouted_in_period, 0,to_number(null),change.emails_rerouted_in_period),
1455: decode(change.leads_created_in_period, 0,to_number(null),change.leads_created_in_period)
1456: );
1457:
1458: write_log('Number of rows updated in table bix_email_details_stg for Emails Fetched and Replied ' || to_char(SQL%ROWCOUNT));
1459:
1460:
1461:
1462: MERGE INTO BIX_EMAIL_DETAILS_STG fact

Line 1462: MERGE INTO BIX_EMAIL_DETAILS_STG fact

1458: write_log('Number of rows updated in table bix_email_details_stg for Emails Fetched and Replied ' || to_char(SQL%ROWCOUNT));
1459:
1460:
1461:
1462: MERGE INTO BIX_EMAIL_DETAILS_STG fact
1463: USING
1464: (SELECT
1465: inv2.email_account_id
1466: email_account_id,

Line 1791: write_log('Number of rows updated in table bix_email_details_stg for Emails Offered ' || to_char(SQL%ROWCOUNT));

1787: decode(change.leads_created_in_period, 0,to_number(null),change.leads_created_in_period)
1788: );
1789:
1790:
1791: write_log('Number of rows updated in table bix_email_details_stg for Emails Offered ' || to_char(SQL%ROWCOUNT));
1792:
1793:
1794: MERGE INTO BIX_EMAIL_DETAILS_STG fact
1795: USING

Line 1794: MERGE INTO BIX_EMAIL_DETAILS_STG fact

1790:
1791: write_log('Number of rows updated in table bix_email_details_stg for Emails Offered ' || to_char(SQL%ROWCOUNT));
1792:
1793:
1794: MERGE INTO BIX_EMAIL_DETAILS_STG fact
1795: USING
1796: (SELECT
1797: inv2.email_account_id
1798: email_account_id,

Line 2165: write_log('Number of rows updated in table bix_email_details_stg for SR and leads created ' || to_char(SQL%ROWCOUNT));

2161: decode(change.emails_rerouted_in_period, 0,to_number(null),change.emails_rerouted_in_period),
2162: decode(change.leads_created_in_period, 0,to_number(null),change.leads_created_in_period)
2163: );
2164:
2165: write_log('Number of rows updated in table bix_email_details_stg for SR and leads created ' || to_char(SQL%ROWCOUNT));
2166:
2167:
2168: MERGE INTO BIX_EMAIL_DETAILS_STG fact
2169: USING

Line 2168: MERGE INTO BIX_EMAIL_DETAILS_STG fact

2164:
2165: write_log('Number of rows updated in table bix_email_details_stg for SR and leads created ' || to_char(SQL%ROWCOUNT));
2166:
2167:
2168: MERGE INTO BIX_EMAIL_DETAILS_STG fact
2169: USING
2170: (SELECT
2171: inv2.email_account_id
2172: email_account_id,

Line 2538: write_log('Number of rows updated in table bix_email_details_stg for Emails Resolved and Transferred in period ' || to_char(SQL%ROWCOUNT));

2534: decode(change.emails_rerouted_in_period, 0,to_number(null),change.emails_rerouted_in_period),
2535: decode(change.leads_created_in_period, 0,to_number(null),change.leads_created_in_period)
2536: );
2537:
2538: write_log('Number of rows updated in table bix_email_details_stg for Emails Resolved and Transferred in period ' || to_char(SQL%ROWCOUNT));
2539:
2540:
2541:
2542:

Line 2544: MERGE INTO BIX_EMAIL_DETAILS_STG fact

2540:
2541:
2542:
2543:
2544: MERGE INTO BIX_EMAIL_DETAILS_STG fact
2545: USING
2546: (SELECT
2547: inv2.email_account_id
2548: email_account_id,

Line 2910: write_log('Number of rows updated in table bix_email_details_stg for Emails ORR count ' || to_char(SQL%ROWCOUNT));

2906: decode(change.emails_rerouted_in_period, 0,to_number(null),change.emails_rerouted_in_period),
2907: decode(change.leads_created_in_period, 0,to_number(null),change.leads_created_in_period)
2908: );
2909:
2910: write_log('Number of rows updated in table bix_email_details_stg for Emails ORR count ' || to_char(SQL%ROWCOUNT));
2911:
2912:
2913:
2914: MERGE INTO BIX_EMAIL_DETAILS_STG fact

Line 2914: MERGE INTO BIX_EMAIL_DETAILS_STG fact

2910: write_log('Number of rows updated in table bix_email_details_stg for Emails ORR count ' || to_char(SQL%ROWCOUNT));
2911:
2912:
2913:
2914: MERGE INTO BIX_EMAIL_DETAILS_STG fact
2915: USING
2916: (SELECT
2917: inv2.email_account_id
2918: email_account_id,

Line 3269: write_log('Number of rows updated in table bix_email_details_stg for Emails composed and ORR count ' || to_char(SQL%ROWCOUNT));

3265: decode(change.emails_rerouted_in_period, 0,to_number(null),change.emails_rerouted_in_period),
3266: decode(change.leads_created_in_period, 0,to_number(null),change.leads_created_in_period)
3267: );
3268:
3269: write_log('Number of rows updated in table bix_email_details_stg for Emails composed and ORR count ' || to_char(SQL%ROWCOUNT));
3270:
3271:
3272: MERGE INTO BIX_EMAIL_DETAILS_STG fact
3273: USING

Line 3272: MERGE INTO BIX_EMAIL_DETAILS_STG fact

3268:
3269: write_log('Number of rows updated in table bix_email_details_stg for Emails composed and ORR count ' || to_char(SQL%ROWCOUNT));
3270:
3271:
3272: MERGE INTO BIX_EMAIL_DETAILS_STG fact
3273: USING
3274: (SELECT
3275: inv2.email_account_id
3276: email_account_id,

Line 3753: write_log('Number of rows updated in table bix_email_details_stg for Emails Open ' || to_char(SQL%ROWCOUNT));

3749: decode(change.emails_rerouted_in_period, 0,to_number(null),change.emails_rerouted_in_period),
3750: decode(change.leads_created_in_period, 0,to_number(null),change.leads_created_in_period)
3751: );
3752:
3753: write_log('Number of rows updated in table bix_email_details_stg for Emails Open ' || to_char(SQL%ROWCOUNT));
3754:
3755:
3756:
3757:

Line 3759: MERGE INTO BIX_EMAIL_DETAILS_STG fact

3755:
3756:
3757:
3758:
3759: MERGE INTO BIX_EMAIL_DETAILS_STG fact
3760: USING
3761: (SELECT
3762: inv2.email_account_id
3763: email_account_id,

Line 4226: write_log('Number of rows updated in table bix_email_details_stg for Emails in Queue ' || to_char(SQL%ROWCOUNT));

4222: decode(change.leads_created_in_period, 0,to_number(null),change.leads_created_in_period)
4223: );
4224:
4225:
4226: write_log('Number of rows updated in table bix_email_details_stg for Emails in Queue ' || to_char(SQL%ROWCOUNT));
4227:
4228: COMMIT;
4229:
4230: write_log('Number of rows updated in table bix_email_details_f : ' || to_char(SQL%ROWCOUNT));

Line 4904: MERGE INTO bix_email_details_stg bead

4900:
4901: write_log('Merging day rows to week, month, quarter, year bucket in table bix_email_details_f');
4902:
4903: /* Rollup half hour informations to day, week, month, quarter, year time bucket for table bix_email_details_f */
4904: MERGE INTO bix_email_details_stg bead
4905: USING (
4906: SELECT
4907: rlp.agent_id agent_id,
4908: rlp.email_account_id email_account_id,

Line 5211: FROM bix_email_details_stg bead,

5207: bead.outcome_id, bead.result_id, bead.reason_id,
5208: ftd.ent_year_id
5209: order by to_date(to_char(bead.period_start_date, 'dd/mm/yyyy ') ||
5210: lpad(bead.period_start_time, 5, '0'), 'dd/mm/yyyy hh24:mi') desc) year_acc_emails_week_plus
5211: FROM bix_email_details_stg bead,
5212: fii_time_day ftd
5213: WHERE bead.time_id = ftd.report_date_julian
5214: AND bead.period_type_id = 1
5215: --AND bead.request_id = g_request_id

Line 5519: FROM BIX_EMAIL_DETAILS_STG

5515: nvl(EMAILS_ORR_COUNT_IN_PERIOD,0) EMAILS_ORR_COUNT_IN_PERIOD,
5516: nvl(EMAILS_COMPOSED_IN_PERIOD,0) EMAILS_COMPOSED_IN_PERIOD,
5517: nvl(LEADS_CREATED_IN_PERIOD,0) LEADS_CREATED_IN_PERIOD,
5518: nvl(EMAILS_REROUTED_IN_PERIOD,0) EMAILS_REROUTED_IN_PERIOD
5519: FROM BIX_EMAIL_DETAILS_STG
5520: ) STG
5521: ON
5522: (
5523: summ.AGENT_ID = stg.AGENT_ID

Line 6275: Truncate_Table('BIX_EMAIL_DETAILS_STG');

6271: --
6272: --l_end_date := sysdate - 5/(60*24);
6273: l_end_date := sysdate;
6274:
6275: Truncate_Table('BIX_EMAIL_DETAILS_STG');
6276: -- ?? Need code review for date logic - do we need TRUNC etc
6277: --Check to see if the media or interaction table was updated after the last time the ICI program
6278: --was run. If so, store the date so that we go back and recollect those dates to get the latest
6279: --information. This will happen in case of re-routes where the ACCOUNT/CLASSIFICATION on