[Home] [Help]
6498: x_msg_data := l_msg_data;
6499:
6500: --bms_output.put_line('deleting all entries');
6501: IF AMS_DEBUG_HIGH_ON THEN
6502: write_msg('populate_all','Deleting all entris from ams_act_metrics_all_denorm');
6503: END IF;
6504:
6505:
6506: delete
6503: END IF;
6504:
6505:
6506: delete
6507: from ams_act_metrics_all_denorm;
6508:
6509: ----Ams_Utility_Pvt.Write_Conc_log('Inserting entries ams_act_metrics_all_denorm') ;
6510: l_metric_col_date := SYSDATE;
6511: --bms_output.put_line('inserting all entries with metric collection date : '||TO_CHAR(l_metric_col_date,'DD-MON-YYYY HH:MI:SS'));
6505:
6506: delete
6507: from ams_act_metrics_all_denorm;
6508:
6509: ----Ams_Utility_Pvt.Write_Conc_log('Inserting entries ams_act_metrics_all_denorm') ;
6510: l_metric_col_date := SYSDATE;
6511: --bms_output.put_line('inserting all entries with metric collection date : '||TO_CHAR(l_metric_col_date,'DD-MON-YYYY HH:MI:SS'));
6512: IF AMS_DEBUG_HIGH_ON THEN
6513: write_msg('populate_all','inserting all entries with metric collection date : '||TO_CHAR(l_metric_col_date,'DD-MON-YYYY HH:MI:SS'));
6513: write_msg('populate_all','inserting all entries with metric collection date : '||TO_CHAR(l_metric_col_date,'DD-MON-YYYY HH:MI:SS'));
6514: END IF;
6515:
6516:
6517: insert into ams_act_metrics_all_denorm (creation_date,created_by,last_update_date,last_updated_by,last_update_login,act_metrics_denorm_id,metric_collection_date,object_type
6518: ,object_id,leads,top_leads,top_leads_ratio,leads_accepted,lead_acceptance_rate,dead_leads,dead_leads_ratio,opportunities
6519: ,leads_to_opportunity_rate,quotes,quotes_amount,orders,orders_amount,booked_revenue,invoiced_revenue
6520: ,booked_revenue_per_lead,invoiced_revenue_per_lead,cost,cost_per_lead,booked_roi,invoiced_roi
6521: ,responses,contact_group_size,target_group_size
6525: ,booked_revenue_forecast,invoiced_revenue_forecast,booked_rev_per_lead_forecast,invoiced_rev_per_lead_forecast
6526: ,cost_forecast,cost_per_lead_forecast,booked_roi_forecast,
6527: invoiced_roi_forecast,contact_group_size_forecast,target_group_size_forecast,responses_forecast
6528: )
6529: select sysdate,Fnd_Global.User_Id,sysdate,Fnd_Global.User_Id,Fnd_Global.Conc_Login_Id,AMS_ACT_METRICS_ALL_DENORM_S.NEXTVAL,
6530: sysdate,object_type,object_id
6531: ,leads,top_leads,top_leads_ratio,leads_accepted,lead_acceptance_rate
6532: ,dead_leads,dead_leads_ratio,opportunities,leads_to_opportunity_rate,quotes,quotes_amount,orders,orders_amount
6533: ,booked_revenue,invoiced_revenue,booked_revenue_per_lead,invoiced_revenue_per_lead,cost,cost_per_lead,booked_roi,
6619: group by h1.arc_act_metric_used_by ,h1.act_metric_used_by_id
6620: );
6621:
6622: IF AMS_DEBUG_HIGH_ON THEN
6623: write_msg('populate_all','Done Inserting entries ams_act_metrics_all_denorm');
6624: END IF;
6625:
6626: --bms_output.put_line('Done Inserting entries ');
6627:
6662: BEGIN
6663:
6664: x_return_status := FND_API.G_RET_STS_SUCCESS;
6665:
6666: ----Ams_Utility_Pvt.Write_Conc_log('Inserting entries ams_act_metrics_all_denorm') ;
6667: l_metric_col_date := SYSDATE;
6668:
6669: IF AMS_DEBUG_HIGH_ON THEN
6670: write_msg('populate_incremental','max last run date : '||TO_CHAR(p_last_run_date,'DD-MON-YYYY HH:MI:SS'));
6673:
6674: --bms_output.put_line('last run date : '||TO_CHAR(p_last_run_date,'DD-MON-YYYY HH:MI:SS'));
6675: --bms_output.put_line('metric collection date : '||TO_CHAR(l_metric_col_date,'DD-MON-YYYY HH:MI:SS'));
6676:
6677: --first update those rows in ams_act_metrics_all_denorm whose values have been changed in
6678: --ams_act_metrics_all since last metrics collection date
6679: update ams_act_metrics_all_denorm a
6680: set
6681: (
6675: --bms_output.put_line('metric collection date : '||TO_CHAR(l_metric_col_date,'DD-MON-YYYY HH:MI:SS'));
6676:
6677: --first update those rows in ams_act_metrics_all_denorm whose values have been changed in
6678: --ams_act_metrics_all since last metrics collection date
6679: update ams_act_metrics_all_denorm a
6680: set
6681: (
6682: a.last_updated_by
6683: ,a.last_update_date
6787: )
6788: ;
6789:
6790:
6791: insert into ams_act_metrics_all_denorm (creation_date,created_by,last_update_date,last_updated_by,last_update_login
6792: ,act_metrics_denorm_id,metric_collection_date,object_type
6793: ,object_id,leads,top_leads,top_leads_ratio,leads_accepted,lead_acceptance_rate,dead_leads,dead_leads_ratio,opportunities
6794: ,leads_to_opportunity_rate,quotes,quotes_amount,orders,orders_amount,booked_revenue,invoiced_revenue
6795: ,booked_revenue_per_lead,invoiced_revenue_per_lead,cost,cost_per_lead,booked_roi,invoiced_roi
6800: ,booked_revenue_forecast,invoiced_revenue_forecast,booked_rev_per_lead_forecast,invoiced_rev_per_lead_forecast
6801: ,cost_forecast,cost_per_lead_forecast,booked_roi_forecast,
6802: invoiced_roi_forecast,contact_group_size_forecast,target_group_size_forecast,responses_forecast
6803: )
6804: select sysdate,Fnd_Global.User_Id,sysdate,Fnd_Global.User_Id,Fnd_Global.Conc_Login_Id,AMS_ACT_METRICS_ALL_DENORM_S.NEXTVAL,
6805: sysdate,object_type,object_id
6806: ,leads,top_leads,top_leads_ratio,leads_accepted,lead_acceptance_rate
6807: ,dead_leads,dead_leads_ratio,opportunities,leads_to_opportunity_rate,quotes,quotes_amount,orders,orders_amount
6808: ,booked_revenue,invoiced_revenue,booked_revenue_per_lead,invoiced_revenue_per_lead,cost,cost_per_lead,booked_roi,
6893: ,'METRIC_24')
6894: and (h1.arc_act_metric_used_by,h1.act_metric_used_by_id) not in
6895: (
6896: select distinct object_type, object_id
6897: from ams_act_metrics_all_denorm
6898: )
6899: group by h1.arc_act_metric_used_by ,h1.act_metric_used_by_id
6900: );
6901:
6933: l_last_run_date DATE := NULL;
6934:
6935: CURSOR c_last_run_date_csr is
6936: select max(metric_collection_date)
6937: from ams_act_metrics_all_denorm;
6938:
6939: BEGIN
6940: FND_MSG_PUB.initialize;
6941: