[Home] [Help]
PACKAGE BODY: APPS.AMS_ACTMETRICS_SEED_PVT
Source
1 PACKAGE BODY AMS_ACTMETRICS_SEED_PVT AS
2 /* $Header: amsvamsb.pls 120.20.12010000.2 2008/08/12 08:11:47 amlal ship $ */
3 --------------------------------------------------------------------------------
4 --
5 -- NAME
6 -- AMS_ACTMETRICS_SEED_PVT 12.0
7 --
8 -- HISTORY
9 -- 30-Aug-2001 dmvincen Created
10 -- 05-Sep-2001 dmvincen Checking for changed values only.
11 -- Using union all for performance.
12 -- Fixed setting dirty flags.
13 -- 24-Oct-2001 dmvincen Fixed update for existing values.
14 -- 04-Jan-2002 dmvincen New columns for materialized views.
15 -- 27-Nov-2002 dmvincen Sunil decoupled with BIM.
16 -- 29-Nov-2002 sunkumar added index hints for performance reasons
17 -- 06-Dec-2002 dmvincen Fixed cartesian product joins.
18 -- Synced with BIM fact queries.
19 -- 06-Feb-2003 sunkumar added the missing group by expression
20 -- for order amount, for all objtypes. bug#2782228
21 -- 06-Mar-2003 dmvincen BUG2837271: Fixed response count for distinct
22 -- party_id.
23 -- 06-Mar-2003 dmvincen Update last_update_date for history recording.
24 -- 06-Mar-2003 dmvincen Removed magic strings.
25 -- 13-Jun-2003 sunkumar bug# 2948298 added registrants, attendees and
26 -- cancellations for campaign schedule of type event.
27 -- 31-Jul-2003 sunkumar BUG 3058065: Added Booked and Invoiced Revenue
28 -- 05-Jul-2003 sunkumar Added 'CALCULATE_TARGET_GROUP'
29 -- and 'CALCULATE_SEEDED_LIST_METRICS'
30 -- and modified 'CALCULATE_SEEDED_METRICS'
31 -- to add seeded metrics for campaign workbench (R10)
32 -- 06-Nov-2003 choang 1) updated all types related to profile options
33 -- to use profile option
34 -- column 2) added bulk update callouts to new
35 -- seeded metric procedures.
36 -- 13-Nov-2003 choang Fixed lead seeded metrics.
37 -- 14-Nov-2003 dmvincen Leads to check subcategory is null.
38 -- 14-Nov-2003 dmvincen Wrong syntax for decode...G_EONE.
39 -- 16-Jan-2004 dmvincen BUG3370252: Orders count not populating correctly.
40 -- Removed linkage through leads/opps for orders
41 -- and quotes, and revenue.
42 -- 26-Jan-2004 choang bug 3396140: changed target group queries to use
43 -- list_act_type TARGET instead of LIST.
44 -- 26-Jan-2004 dmvincen BUG3396063: Indicate refresh for formulas.
45 -- 04-Feb-2004 dmvincen Object level query amount missing entry join.
46 -- 06-Feb-2004 dmvincen Removed object tables for more generic queries.
47 -- 06-Feb-2004 dmvincen Added like to function name for any schema match.
48 -- 11-Feb-2004 dmvincen Added currency conversion during bulk update.
49 -- 20-feb-2004 sunkumar modified list queries. bug#3370252
50 -- 23-feb-2004 sunkumar modified leads/opp. query for list for org. contact
51 -- 26-feb-2004 sunkumar used HZ tables to get data for list queries
52 -- 09-Mar-2004 dmvincen Moved hz_cust_accounts into exists clause.
53 -- 11-May-2004 sunkumar bug#3611891:LIST EFFECTIVENESS SHOWS WRONG NO OF LEADS
54 -- 25-May-2004 sunkumar bug#3578292:INVOICE REV COL GETS POPULATED W/O INVOICING IN LIST EFF
55 -- 08-Jun-2004 dmvincen BUG#3488796: PERF: Invoiced revenue to use exists clause
56 -- 23-Jun-2004 dmvincen BUG3704598: PERF: List queries.
57 -- 06-Jan-2005 dmvincen BUG4116414: Merge forward: List query performance.
58 -- 16-Jun-2005 dmvincen BUG4438486: Refresh function variable metrics.
59 -- 11-Aug-2005 dmvincen Added Inferred Calculations.
60 -- 21-Oct-2005 dmvincen BUG4650767: Inferred B2B Orders requires Roles.
61 -- 21-Dec-2005 dmvincen BUG4878626: Performance if inferred queries.
62 -- 05-Jan-2006 dmvincen BUG4924982: SQL Repos: Quotes performance.
63 -- 19-Jan-2006 dmvincen BUG4963536: Inferred response is a rogue.
64 -- 03-Feb-2006 dmvincen BUG5002890: Inferred Leads has high buffer gets.
65 -- 03-Feb-2006 dmvincen BUG4970454: Inferred Orders has high buffer gets.
66 --------------------------------------------------------------------------------
67 --
68 --
69 -- Global variables and constants.
70 --
71 -- Name of the current package.
72 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMS_ACTMETRICS_SEED_PVT';
73
74 G_FUNCTION_NAME CONSTANT VARCHAR2(80) :='AMS_ACTMETRICS_SEED_PVT.CALCULATE_SEEDED_METRICS';
75
76 -- sunkumar Added 11.5.10: 05-August-2003
77 G_TARGET_FUNCTION_NAME CONSTANT VARCHAR2(80) :='AMS_ACTMETRICS_SEED_PVT.CALCULATE_TARGET_GROUP';
78 G_LIST_FUNCTION_NAME CONSTANT VARCHAR2(80) := 'AMS_ACTMETRICS_SEED_PVT.CALCULATE_SEEDED_LIST_METRICS';
79
80 G_DEAD_LEAD_STATUS fnd_profile_option_values.profile_option_value%TYPE := fnd_profile.value('AS_DEAD_LEAD_STATUS');
81 G_LEAD_LINK_STATUS fnd_profile_option_values.profile_option_value%TYPE := fnd_profile.value('AS_LEAD_LINK_STATUS');
82 -- sunkumar end additions
83
84 G_FUNC_CURRENCY fnd_profile_option_values.profile_option_value%TYPE := fnd_profile.value('AMS_DEFAULT_CURR_CODE');
85
86
87 -- sunkumar Added 11.5.10: 05-August-2003
88 G_ACCOUNTING_INFO VARCHAR2(30) := 'Accounting Information';
89 G_ALIST CONSTANT VARCHAR2(30) := 'ALIST';
90 -- sunkumar end additions
91
92 G_CSCH CONSTANT VARCHAR2(30) := 'CSCH';
93 G_EVEO CONSTANT VARCHAR2(30) := 'EVEO';
94 G_EONE CONSTANT VARCHAR2(30) := 'EONE';
95 G_FUNCTION CONSTANT VARCHAR2(30) := 'FUNCTION';
96 G_LEAD_ID CONSTANT NUMBER := 906;
97 G_OPPORTUNITY_ID CONSTANT NUMBER := 907;
98 G_ORDER_AMOUNT_ID CONSTANT NUMBER := 908;
99 G_RESPONSE_ID CONSTANT NUMBER := 903;
100 G_ATTENDEE_ID CONSTANT NUMBER := 910;
101 G_ORDER_COUNT_ID CONSTANT NUMBER := 912;
102 G_REGISTRANTS_ID CONSTANT NUMBER := 909;
103 G_CANCELLATION_ID CONSTANT NUMBER := 911;
104 G_INVOICED_ID CONSTANT NUMBER := 921;
105 G_BOOKED_ID CONSTANT NUMBER := 922;
106 G_REVENUE_ID CONSTANT NUMBER := 902;
107 G_BOOKED CONSTANT VARCHAR2(30) := 'BOOKED';
108 G_HEADER CONSTANT VARCHAR2(30) := 'HEADER';
109 G_OPP_QUOTE CONSTANT VARCHAR2(30) := 'OPP_QUOTE';
110 G_REGISTERED CONSTANT VARCHAR2(30) := 'REGISTERED';
111 G_CANCELLED CONSTANT VARCHAR2(30) := 'CANCELLED';
112 G_POSITIVE_RESPONSE CONSTANT VARCHAR2(1) := 'Y';
113 G_IS_DIRTY CONSTANT VARCHAR2(1) := 'Y';
114 G_IS_LEAD CONSTANT VARCHAR2(1) := 'Y';
115 G_IS_ENABLED CONSTANT VARCHAR2(1) := 'Y';
116 G_IS_DELETED CONSTANT VARCHAR2(1) := 'Y';
117 G_NOT_DELETED CONSTANT VARCHAR2(1) := 'N';
118 G_IS_ATTENDED CONSTANT VARCHAR2(1) := 'Y';
119
120 --sunkumar - start: 11510 additions 04-August-2003
121
122 G_BOOKED_FLAG CONSTANT VARCHAR2(1) := 'Y';
123 G_IS_ACCEPTED CONSTANT VARCHAR2(1) := 'Y';
124 G_NOT_ACCEPTED CONSTANT VARCHAR2(1) := 'N';
125
126 G_LEAD_TYPE CONSTANT VARCHAR2(1) := 'A';
127 G_LEAD_RANK CONSTANT VARCHAR2(30) := 'RANK';
128
129 G_CONTACT_GROUP_ID CONSTANT NUMBER := 770;
130 G_CONTROL_GROUP_ID CONSTANT NUMBER := 771;
131 G_QUOTE_COUNT_ID CONSTANT NUMBER := 914;
132 G_QUOTE_AMOUNT_ID CONSTANT NUMBER := 915;
133 G_TARGET_GROUP_ID CONSTANT NUMBER := 913;
134 G_DEAD_LEAD_ID CONSTANT NUMBER := 780;
135 G_OPP_CONVERSION_ID CONSTANT NUMBER := 781;
136 G_ACCEPTED_LEAD_ID CONSTANT NUMBER := 782;
137 G_TOP_LEAD_ID CONSTANT NUMBER := 783;
138 --G_BOOKED_ORDER_ID CONSTANT NUMBER := 918;
139 G_OPP_AMOUNT CONSTANT NUMBER := 916;
140
141 G_LIST_TYPE CONSTANT VARCHAR2(30) := 'LIST';
142 G_TARGET_TYPE CONSTANT VARCHAR2(30) := 'TARGET';
143 G_LIST_SEL_ACTION CONSTANT VARCHAR2(30) := 'LIST';
144
145 G_FORMULA CONSTANT VARCHAR2(30) := 'FORMULA';
146 G_METRIC CONSTANT VARCHAR2(30) := 'METRIC';
147 G_CATEGORY CONSTANT VARCHAR2(30) := 'CATEGORY';
148 G_VARIABLE CONSTANT VARCHAR2(30) := 'VARIABLE';
149
150 G_B2B_LIST_CATEGORY CONSTANT VARCHAR2(30) := 'B2B_TCA_PROFILE';
151
152 --sunkumar end: 11510 additions
153
154 --sunkumar 03-mar-2004 additions for list metric_id's
155
156 G_LIST_LEAD_ID CONSTANT NUMBER := 326;
157 G_LIST_OPP_ID CONSTANT NUMBER := 331;
158
159 --sunkumar - 18-Jun-2003 - bug #2948298
160 G_ACTIVITY_EVENTS CONSTANT VARCHAR2(30) := 'EVENTS';
161
162 -- BUG 4281906: Calculation lag in days.
163 G_CALC_LAG_DAYS CONSTANT NUMBER := fnd_profile.value('AMS_METR_SEEDED_CALC_EXP');
164
165 AMS_DEBUG_HIGH_ON BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
166 AMS_DEBUG_LOW_ON BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
167 AMS_DEBUG_MEDIUM_ON BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
168
169 PROCEDURE Write_Log(name varchar2, message1 varchar2, message2 varchar2 := null)
170 IS
171 msg varchar2(2000);
172 begin
173 msg := TO_CHAR(DBMS_UTILITY.get_time)||': '
174 ||G_PKG_NAME||'.'||name||': '||message1;
175 if message2 is not null then
176 msg := msg || ': '||message2;
177 end if;
178 Ams_Utility_Pvt.Write_Conc_Log(msg);
179 END Write_Log;
180
181 PROCEDURE Show_Values(
182 p_actmetric_id_table IN num_table_type,
183 p_actual_value_table IN num_table_type)
184 IS
185 l_index NUMBER;
186 l_last number;
187 l_limit NUMBER := 5;
188 BEGIN
189 write_log('Show_Values','COUNT='||p_actmetric_id_table.COUNT);
190 IF p_actmetric_id_table.COUNT > 0 THEN
191 IF l_limit is not null then
192 l_last := p_actmetric_id_table.first + l_limit - 1;
193 IF l_last > p_actmetric_id_table.last THEN
194 l_last := p_actmetric_id_table.last;
195 end if;
196 else
197 l_last := p_actmetric_id_table.last;
198 end if;
199
200 FOR l_index IN p_actmetric_id_table.FIRST .. l_last
201 LOOP
202 EXIT WHEN l_index IS NULL;
203 write_log('Show_Values',
204 'actmetid='||p_actmetric_id_table(l_index),
205 'actual_value='||p_actual_value_table(l_index));
206 END LOOP;
207 END IF;
208 END Show_Values;
209
210 -- NAME
211 -- convert_currency
212 --
213 -- PURPOSE
214 -- Change currency to the default.
215 --
216 -- NOTES
217 --
218 -- HISTORY
219 -- 30-Aug-2001 dmvincen Created.
220 --
221 FUNCTION convert_currency(
222 p_from_currency VARCHAR2
223 ,p_from_amount NUMBER) RETURN NUMBER
224 IS
225 l_conversion_type_profile CONSTANT VARCHAR2(30) := 'AMS_CURR_CONVERSION_TYPE';
226 l_conversion_type fnd_profile_option_values.profile_option_value%TYPE; -- Curr conversion type; see API doc for details.
227 l_to_amount NUMBER;
228 x_return_status VARCHAR2(1);
229 BEGIN
230
231 IF p_from_currency IS NULL OR
232 G_FUNC_CURRENCY = p_from_currency OR
233 nvl(p_from_amount,0) = 0 THEN
234 RETURN p_from_amount;
235 END IF;
236
237 -- condition added to pass conversion types
238 l_conversion_type := fnd_profile.VALUE(l_conversion_type_profile);
239
240 -- Conversion type cannot be null in profile
241 IF l_conversion_type IS NULL THEN
242 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
243 fnd_message.set_name('AMS', 'AMS_NO_EXCHANGE_TYPE');
244 fnd_msg_pub.ADD;
245 END IF;
246 RETURN 0;
247 END IF;
248
249 -- Call the proper AMS_UTILITY_API API to convert the amount.
250
251 ams_utility_pvt.Convert_Currency (
252 x_return_status ,
253 p_from_currency,
254 G_FUNC_CURRENCY,
255 sysdate,
256 p_from_amount,
257 l_to_amount);
258
259 RETURN (l_to_amount);
260
261 EXCEPTION
262 WHEN gl_currency_api.no_rate THEN
263 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
264 fnd_message.set_name('AMS', 'AMS_NO_RATE');
265 fnd_msg_pub.ADD;
266 END IF;
267 WHEN gl_currency_api.invalid_currency THEN
268 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
269 fnd_message.set_name('AMS', 'AMS_INVALID_CURR');
270 fnd_msg_pub.ADD;
271 END IF;
272 WHEN OTHERS THEN
273 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
274 fnd_msg_pub.add_exc_msg('AMS_UTLITY_PVT', 'Convert_curency');
275 END IF;
276 END convert_currency;
277
278 -- NAME
279 -- convert_to_trans_currency
280 --
281 -- PURPOSE
282 -- Change the currency from default to transactional.
283 --
284 -- NOTES
285 --
286 -- HISTORY
287 -- 08-Jun-2004 dmvincen Created.
288 --
289 FUNCTION convert_to_trans_currency(
290 p_trans_currency VARCHAR2
291 ,p_from_amount NUMBER) RETURN NUMBER
292 IS
293 l_conversion_type_profile CONSTANT VARCHAR2(30) := 'AMS_CURR_CONVERSION_TYPE';
294 l_conversion_type fnd_profile_option_values.profile_option_value%TYPE; -- Curr conversion type; see API doc for details.
295 l_to_amount NUMBER;
296 x_return_status VARCHAR2(1);
297 BEGIN
298
299 IF p_trans_currency IS NULL OR
300 G_FUNC_CURRENCY = p_trans_currency OR
301 nvl(p_from_amount,0) = 0 THEN
302 RETURN p_from_amount;
303 END IF;
304
305 -- condition added to pass conversion types
306 l_conversion_type := fnd_profile.VALUE(l_conversion_type_profile);
307
308 -- Conversion type cannot be null in profile
309 IF l_conversion_type IS NULL THEN
310 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
311 fnd_message.set_name('AMS', 'AMS_NO_EXCHANGE_TYPE');
312 fnd_msg_pub.ADD;
313 END IF;
314 RETURN 0;
315 END IF;
316
317 -- Call the proper AMS_UTILITY_API API to convert the amount.
318
319 ams_utility_pvt.Convert_Currency (
320 x_return_status ,
321 G_FUNC_CURRENCY,
322 p_trans_currency,
323 sysdate,
324 p_from_amount,
325 l_to_amount);
326
327 RETURN (l_to_amount);
328
329 EXCEPTION
330 WHEN gl_currency_api.no_rate THEN
331 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
332 fnd_message.set_name('AMS', 'AMS_NO_RATE');
333 fnd_msg_pub.ADD;
334 END IF;
335 WHEN gl_currency_api.invalid_currency THEN
336 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
337 fnd_message.set_name('AMS', 'AMS_INVALID_CURR');
338 fnd_msg_pub.ADD;
339 END IF;
340 WHEN OTHERS THEN
341 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
342 fnd_msg_pub.add_exc_msg('AMS_UTLITY_PVT', 'Convert_curency');
343 END IF;
344 END convert_to_trans_currency;
345
346 -- NAME
347 -- Update_Actmetrics_Bulk
348 --
349 -- PURPOSE
350 -- Bulk update the activity metrics for given ids and actual values.
351 -- Set ancester nodes to dirty.
352 --
353 -- NOTES
354 -- To Do: The Costs and Revenue value must be converted.
355 --
356 -- HISTORY
357 -- 30-Aug-2001 dmvincen Created.
358 -- 16-Jun-2005 dmvincen BUG4438486: Refresh function variable metrics
359 -- by setting dirty flag.
360 --
361 PROCEDURE Update_Actmetrics_Bulk(
362 p_actmetric_id_table IN num_table_type,
363 p_actual_value_table IN num_table_type)
364 IS
365 l_today DATE := SYSDATE;
366 BEGIN
367 IF AMS_DEBUG_HIGH_ON THEN
368 Write_Log('Update_Actmetrics_Bulk',
369 'BULK UPDATING COUNT='||p_actmetric_id_table.COUNT);
370 END IF;
371 IF AMS_DEBUG_MEDIUM_ON THEN
372 Show_Values(p_actmetric_id_table, p_actual_value_table);
373 END IF;
374 IF p_actmetric_id_table.COUNT > 0 THEN
375 -- Bulk update all the actual values.
376 FORALL l_index IN p_actmetric_id_table.FIRST .. p_actmetric_id_table.LAST
377 UPDATE ams_act_metrics_all
378 SET days_since_last_refresh = l_today - last_calculated_date,
382 dirty_flag = 'Y',
379 last_calculated_date = l_today,
380 last_update_date = l_today,
381 object_version_number = object_version_number+1,
383 difference_since_last_calc =
384 p_actual_value_table(l_index) - func_actual_value,
385 trans_actual_value = convert_to_trans_currency(transaction_currency_code,p_actual_value_table(l_index)),
386 func_actual_value = p_actual_value_table(l_index)
387 WHERE activity_metric_id = p_actmetric_id_table(l_index);
388
389 -- Bulk update ancesters to be dirty.
390 FORALL l_index IN p_actmetric_id_table.first..p_actmetric_id_table.last
391 UPDATE ams_act_metrics_all a
392 SET dirty_flag = G_IS_DIRTY
393 WHERE activity_metric_id IN
394 (SELECT activity_metric_id FROM
395 (SELECT activity_metric_id, dirty_flag FROM ams_act_metrics_all
396 START WITH activity_metric_id = p_actmetric_id_table(l_index)
397 CONNECT BY activity_metric_id = PRIOR summarize_to_metric
398 UNION ALL
399 SELECT activity_metric_id, dirty_flag FROM ams_act_metrics_all
400 START WITH activity_metric_id = p_actmetric_id_table(l_index)
401 CONNECT BY activity_metric_id = PRIOR rollup_to_metric
402 UNION ALL
403 SELECT a.activity_metric_id, a.dirty_flag
404 FROM ams_act_metrics_all a, ams_metrics_all_b m,
405 ams_metric_formulas f,
406 ams_act_metrics_all b, ams_metrics_all_b c
407 WHERE a.metric_id = m.metric_id
408 AND m.metric_id = f.metric_id
409 AND b.metric_id = c.metric_id
410 AND a.arc_act_metric_used_by = b.arc_act_metric_used_by
411 AND a.act_metric_used_by_id = b.act_metric_used_by_id
412 AND m.metric_calculation_type = G_FORMULA
413 AND a.last_update_date > b.last_update_date
414 AND ((b.metric_id = f.source_id
415 AND f.source_type = G_METRIC)
416 OR (c.metric_category = f.source_id
417 AND f.source_type = G_CATEGORY))
418 AND b.activity_metric_id = p_actmetric_id_table(l_index)
419 AND a.dirty_flag <> G_IS_DIRTY
420 UNION ALL
421 SELECT a.activity_metric_id, a.dirty_flag
422 FROM ams_act_metrics_all a, ams_metrics_all_b b,
423 ams_act_metrics_all c
424 WHERE a.metric_id = b.metric_id
425 AND b.accrual_type = G_VARIABLE
426 AND a.arc_act_metric_used_by = c.arc_act_metric_used_by
427 AND a.act_metric_used_by_id = c.act_metric_used_by_id
428 AND c.activity_metric_id = p_actmetric_id_table(l_index)
429 AND TO_NUMBER(NVL(b.compute_using_function,'-1')) = c.metric_id
430 AND a.dirty_flag <> G_IS_DIRTY)
431 WHERE dirty_flag <> G_IS_DIRTY);
432
433 END IF;
434 END update_actmetrics_bulk;
435
436 -- NAME
437 -- Calculate_Quotes
438 --
439 -- PURPOSE
440 -- Bulk collect the quotes from source tables.
441 --
442 -- HISTORY
443 -- 05-Jan-2005 dmvincen Created from calculate_seeded_metrics.
444 -- 05-Jan-2005 dmvincen Added enabled metric checks.
445 --
446 PROCEDURE Calculate_Quotes -- ALL
447 IS
448 l_actual_values_table num_table_type;
449 l_activity_metric_id_table num_table_type;
450 l_today DATE := sysdate;
451
452 cursor c_has_quotes_enabled IS
453 SELECT count(distinct b.metric_id) metric_count,
454 count(a.activity_metric_id) activity_count
455 FROM ams_metrics_all_b b, ams_act_metrics_all a
456 WHERE b.metric_id in (244,245,246,247,248,254,255,256,257,258)
457 AND enabled_flag = G_IS_ENABLED
458 AND a.metric_id = b.metric_id
459 AND nvl(a.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
460
461 l_has_enabled NUMBER;
462 l_activity_count NUMBER;
463 BEGIN
464 OPEN c_has_quotes_enabled;
465 FETCH c_has_quotes_enabled INTO l_has_enabled, l_activity_count;
466 CLOSE c_has_quotes_enabled;
467
468 IF AMS_DEBUG_HIGH_ON THEN
472
469 Write_Log('Calculate_Quotes','Enabled Count='||l_has_enabled,
470 'Activity Metrics='||l_activity_count);
471 END IF;
473 IF l_has_enabled > 0 THEN
474
475 SELECT NVL(actual_value, 0), activity_metric_id
476 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
477 FROM
478 (SELECT decode(metric_category,914,quote_count,
479 915,quote_amount,0) actual_value, activity_metric_id, func_actual_value
480 FROM(
481 SELECT count(G.quote_header_id) quote_count,
482 sum(convert_currency(nvl(currency_code,G_FUNC_CURRENCY),
483 total_list_price + total_adjusted_amount)) quote_amount,
484 marketing_source_code_id
485 FROM aso_quote_headers_all G
486 WHERE G.marketing_source_code_id in (select c.source_code_id
487 from ams_act_metrics_all AL, ams_metrics_all_b ALB,
488 ams_source_codes c
489 where AL.ARC_ACT_METRIC_USED_BY = C.arc_source_code_for
490 AND AL.act_metric_used_by_id = C.source_code_for_id
491 AND AL.metric_id = ALB.metric_id
492 AND ALB.metric_id in (244,245,246,247,248,254,255,256,257,258)
493 AND ALB.enabled_flag = 'Y'
494 AND nvl(AL.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
495 )
496 and quote_version = (select max(quote_version) from aso_quote_headers_all
497 where quote_number = g.quote_number)
498 GROUP BY marketing_source_code_id
499 ) quotes,
500 ams_act_metrics_all AL, ams_metrics_all_b ALB,
501 ams_source_codes c
502 where quotes.marketing_source_code_id(+) = c.source_code_id
503 AND AL.ARC_ACT_METRIC_USED_BY = C.arc_source_code_for
504 AND AL.act_metric_used_by_id = C.source_code_for_id
505 AND AL.metric_id = ALB.metric_id
506 AND ALB.metric_id in (244,245,246,247,248,254,255,256,257,258)
507 AND ALB.enabled_flag = 'Y'
508 AND nvl(AL.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
509 ) C
510 WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
511 ;
512
513 -- Save all calculation upto this point.
514 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
515 l_activity_metric_id_table.DELETE;
516 l_actual_values_table.DELETE;
517
518 END IF;
519
520 END;
521
522 -- NAME
523 -- Calculate_Leads_Opps
524 --
525 -- PURPOSE
526 -- Bulk collect the leads and opportunities from source tables.
527 --
528 -- HISTORY
529 -- 05-Jan-2005 dmvincen Created from calculate_seeded_metrics.
530 -- 05-Jan-2005 dmvincen Added enabled metric checks.
531 --
532 PROCEDURE Calculate_Leads_Opps -- ALL
533 IS
534 l_actual_values_table num_table_type;
535 l_activity_metric_id_table num_table_type;
536 l_today DATE := sysdate;
537
538 cursor c_has_leads_quotes_enabled IS
539 SELECT count(distinct b.metric_id) metric_count,
540 count(activity_metric_id) activity_count
541 FROM ams_metrics_all_b b, ams_act_metrics_all a
542 WHERE b.metric_id in (264,265,266,267,268, -- Dead leads
543 83,84,85,88,89, -- Leads
544 274,275,276,277,278, -- Leads Accepted
545 284,285,286,287,288, -- Leads to Opportunities
546 294,295,296,297,298, -- Top Leads
547 93,94,95,98,99) -- Opportunities
548 AND enabled_flag = G_IS_ENABLED
549 AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
550 and a.metric_id = b.metric_id;
551
552 cursor c_get_profiles is
553 select max(max_score),
554 fnd_profile.value('AS_LEAD_LINK_STATUS'),
555 fnd_profile.value('AS_DEAD_LEAD_STATUS')
556 from as_sales_lead_ranks_b
557 where enabled_flag = 'Y';
558
559 l_max_score number;
560 l_link_status varchar2(150);
561 l_dead_status varchar2(150);
562 l_has_enabled NUMBER;
563 l_activity_count number;
564 BEGIN
565 OPEN c_has_leads_quotes_enabled;
566 FETCH c_has_leads_quotes_enabled INTO l_has_enabled, l_activity_count;
567 CLOSE c_has_leads_quotes_enabled;
568
569 IF AMS_DEBUG_HIGH_ON THEN
570 Write_Log('Calculate_Leads_Opps','Enabled Count='||l_has_enabled,
571 'Activity Count='||l_activity_count);
572 END IF;
573
574 IF l_has_enabled > 0 THEN
575
576 OPEN c_get_profiles;
577 fetch c_get_profiles into l_max_score, l_link_status, l_dead_status;
578 close c_get_profiles;
579
580 SELECT NVL(actual_value, 0), activity_metric_id
581 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
582 FROM
583 (SELECT COUNT(decode(metric_sub_category,null,sales_lead_id,
584 G_TOP_LEAD_ID,decode(lead_rank_score,l_max_score,1,null),
585 G_ACCEPTED_LEAD_ID,decode(accepted_flag,G_IS_ACCEPTED,1,null),
586 G_OPP_CONVERSION_ID,decode(x.status_code,l_link_status,1,null),
587 G_DEAD_LEAD_ID,decode(x.status_code,l_dead_status,1,null))) actual_value,
588 AL.activity_metric_id, AL.func_actual_value
589 FROM
590 (SELECT c.arc_source_code_for, c.source_code_for_id,
594 FROM as_sales_leads X, as_statuses_b Y,
591 x.sales_lead_id, x.lead_rank_score,
592 NVL(X.ACCEPT_FLAG,G_NOT_ACCEPTED) accepted_flag,
593 X.status_code
595 (select distinct c.source_code_id,
596 c.arc_source_code_for, c.source_code_for_id
597 FROM ams_source_codes C,
598 ams_metrics_all_b b, ams_act_metrics_all a
599 where a.metric_id = b.metric_id
600 AND c.arc_source_code_for = a.arc_act_metric_used_by
601 and c.source_code_for_id = a.act_metric_used_by_id
602 and b.enabled_flag = G_IS_ENABLED
603 and b.metric_id in (83,84,85,88,89, -- Leads
604 294,295,296,297,298, -- Top Leads
605 274,275,276,277,278, -- Leads Accepted
606 284,285,286,287,288, -- Leads to Opps
607 264,265,266,267,268) -- Dead Leads
608 and a.last_calculated_date > l_today - G_CALC_LAG_DAYS) c
609 WHERE X.status_code = Y.status_code
610 AND Y.lead_flag = G_IS_LEAD
611 AND Y.enabled_flag = G_IS_ENABLED
612 AND NVL(X.DELETED_FLAG,G_NOT_DELETED) <> G_IS_DELETED
613 AND source_promotion_id = c.source_code_id
614 ) X,
615 ams_act_metrics_all AL, ams_metrics_all_b ALB
616 WHERE AL.arc_act_metric_used_by = X.arc_source_code_for(+)
617 AND AL.act_metric_used_by_id = X.source_code_for_id(+)
618 AND AL.metric_id = ALB.metric_id
619 AND ALB.metric_id in (83,84,85,88,89, -- Leads
620 294,295,296,297,298, -- Top Leads
621 274,275,276,277,278, -- Leads Accepted
622 284,285,286,287,288, -- Leads to Opps
623 264,265,266,267,268) -- Dead Leads
624 and nvl(al.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
625 AND ALB.enabled_flag = G_IS_ENABLED
626 GROUP BY AL.activity_metric_id, AL.func_actual_value
627 UNION ALL
628 --R9 Campaign Schedule/Opportunities
629 SELECT
630 COUNT(X.lead_id) actual_value, AL.activity_metric_id,
631 AL.func_actual_value
632 FROM
633 ams_source_codes C,
634 as_leads_all X,
635 ams_act_metrics_all AL, ams_metrics_all_b ALB
636 WHERE X.source_promotion_id(+) = C.source_code_id
637 AND AL.ARC_ACT_METRIC_USED_BY = C.arc_source_code_for
638 AND AL.metric_id = ALB.metric_id
639 AND AL.act_metric_used_by_id = C.source_code_for_id
640 AND ALB.metric_id in (93,94,95,98,99) -- Opportunities
641 AND ALB.enabled_flag = G_IS_ENABLED
642 and nvl(al.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
643 GROUP BY AL.activity_metric_id, AL.func_actual_value
644 )
645 WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
646 ;
647
648 -- Save all calculation upto this point.
649 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
650 l_activity_metric_id_table.DELETE;
651 l_actual_values_table.DELETE;
652
653 END IF;
654
655 END;
656
657 -- NAME
658 -- Calculate_Orders
659 --
660 -- PURPOSE
661 -- Bulk collect the orders from source tables.
662 --
663 -- HISTORY
664 -- 05-Jan-2005 dmvincen Created from calculate_seeded_metrics.
665 -- 05-Jan-2005 dmvincen Added enabled metric checks.
666 --
667 PROCEDURE Calculate_Orders
668 IS
669 l_actual_values_table num_table_type;
670 l_activity_metric_id_table num_table_type;
671 l_today date := sysdate;
672 cursor c_has_orders_enabled IS
673 SELECT count(distinct a.metric_id) metric_count,
674 count(activity_metric_id) activity_count
675 FROM ams_metrics_all_b b, ams_act_metrics_all a
676 WHERE b.metric_id in (155,156,157,158,159, -- Orders
677 103,104,105,108,109, -- Orders amount
678 233,234,235,236,237, -- Booked Revenue
679 220,221,222,224,225) -- Invoiced Revenue
680 AND enabled_flag = G_IS_ENABLED
681 and a.metric_id = b.metric_id
682 -- and a.last_calculated_date > l_today - G_CALC_LAG_DAYS
683 and NVL(A.LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS ;
684
685 l_has_enabled NUMBER;
686 l_activity_count number;
687 BEGIN
688 OPEN c_has_orders_enabled;
689 FETCH c_has_orders_enabled INTO l_has_enabled, l_activity_count;
690 CLOSE c_has_orders_enabled;
691
692 IF AMS_DEBUG_HIGH_ON THEN
693 Write_Log('Calculate_Orders','Enabled Count='||l_has_enabled,
694 'Activity Count='||l_activity_count);
695 END IF;
696
697 IF l_has_enabled > 0 THEN
698
699 SELECT NVL(actual_value, 0), activity_metric_id
700 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
701 FROM
702 (SELECT
703 DECODE(DECODE(METRIC_CATEGORY,G_REVENUE_ID,
704 METRIC_SUB_CATEGORY,METRIC_CATEGORY)
705 , G_ORDER_COUNT_ID , ORDER_COUNT
706 , G_ORDER_AMOUNT_ID , BOOKED_REVENUE
707 , G_BOOKED_ID , BOOKED_REVENUE
708 , G_INVOICED_ID , INVOICED_REVENUE
709 , 0 ) ACTUAL_VALUE, ACTIVITY_METRIC_ID, FUNC_ACTUAL_VALUE
710 FROM
714 SUM(AMS_ACTMETRICS_SEED_PVT.CONVERT_CURRENCY(CURRENCY_CODE, INVOICED_REVENUE)) INVOICED_REVENUE
711 (SELECT ARC_SOURCE_CODE_FOR, SOURCE_CODE_FOR_ID,
712 COUNT(DISTINCT H.HEADER_ID) ORDER_COUNT,
713 SUM(AMS_ACTMETRICS_SEED_PVT.CONVERT_CURRENCY(CURRENCY_CODE, BOOKED_REVENUE)) BOOKED_REVENUE,
715 FROM
716 (SELECT H.ARC_SOURCE_CODE_FOR, H.SOURCE_CODE_FOR_ID, H.HEADER_ID,
717 SUM(NVL(H.UNIT_SELLING_PRICE * H.ORDERED_QUANTITY,0)) BOOKED_REVENUE,
718 SUM(NVL(H.UNIT_SELLING_PRICE * ABS(H.INVOICED_QUANTITY),0)) INVOICED_REVENUE,
719 H.CURRENCY_CODE
720 FROM
721 (SELECT C.ARC_SOURCE_CODE_FOR, C.SOURCE_CODE_FOR_ID,
722 I.LINE_ID, I.UNIT_SELLING_PRICE,
723 DECODE(H.FLOW_STATUS_CODE, G_BOOKED, H.HEADER_ID, NULL) HEADER_ID,
724 DECODE(H.FLOW_STATUS_CODE, G_BOOKED, I.ORDERED_QUANTITY, 0) ORDERED_QUANTITY,
725 I.INVOICED_QUANTITY,
726 NVL(H.TRANSACTIONAL_CURR_CODE,G_FUNC_CURRENCY) CURRENCY_CODE
727 FROM OE_ORDER_HEADERS_ALL H, OE_ORDER_LINES_ALL I,
728 (SELECT DISTINCT ARC_SOURCE_CODE_FOR, SOURCE_CODE_FOR_ID, SOURCE_CODE_ID
729 FROM AMS_SOURCE_CODES C, AMS_ACT_METRICS_ALL A, AMS_METRICS_ALL_B B
730 WHERE A.ARC_ACT_METRIC_USED_BY = C.ARC_SOURCE_CODE_FOR
731 AND A.ACT_METRIC_USED_BY_ID = C.SOURCE_CODE_FOR_ID
732 AND A.METRIC_ID = B.METRIC_ID
733 AND DECODE(B.METRIC_CATEGORY,G_REVENUE_ID,
734 B.METRIC_SUB_CATEGORY,B.METRIC_CATEGORY) IN
735 (G_ORDER_COUNT_ID,G_ORDER_AMOUNT_ID,G_BOOKED_ID, G_INVOICED_ID)
736 AND B.FUNCTION_NAME LIKE '%'|| G_FUNCTION_NAME
737 AND B.METRIC_CALCULATION_TYPE = G_FUNCTION
738 AND B.ENABLED_FLAG = G_IS_ENABLED
739 AND NVL(A.LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS
740 ) C
741 WHERE H.HEADER_ID = I.HEADER_ID(+)
742 AND H.BOOKED_FLAG = G_BOOKED_FLAG
743 AND H.BOOKED_DATE IS NOT NULL
744 AND H.MARKETING_SOURCE_CODE_ID = C.SOURCE_CODE_ID
745 AND EXISTS (
746 SELECT 1 FROM DUAL WHERE H.FLOW_STATUS_CODE = G_BOOKED
747 UNION ALL
748 SELECT /*+ FIRST_ROWS */ 1
749 FROM OE_SYSTEM_PARAMETERS_ALL OSPA, MTL_SYSTEM_ITEMS_B ITEM
750 WHERE H.ORG_ID = OSPA.ORG_ID
751 AND I.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
752 AND NVL(I.SHIP_FROM_ORG_ID, OSPA.MASTER_ORGANIZATION_ID) = ITEM.ORGANIZATION_ID
753 and rownum = 1
754 )
755 ) H
756 GROUP BY H.ARC_SOURCE_CODE_FOR, H.SOURCE_CODE_FOR_ID,
757 H.HEADER_ID,H.CURRENCY_CODE
758 ) H
759 GROUP BY ARC_SOURCE_CODE_FOR, SOURCE_CODE_FOR_ID) T,
760 AMS_ACT_METRICS_ALL AL, AMS_METRICS_ALL_B ALB
761 WHERE AL.ARC_ACT_METRIC_USED_BY = T.ARC_SOURCE_CODE_FOR(+)
762 AND AL.ACT_METRIC_USED_BY_ID = T.SOURCE_CODE_FOR_ID(+)
763 AND AL.METRIC_ID = ALB.METRIC_ID
764 AND DECODE(METRIC_CATEGORY,G_REVENUE_ID,
765 METRIC_SUB_CATEGORY,METRIC_CATEGORY) IN
766 (G_ORDER_COUNT_ID,G_ORDER_AMOUNT_ID,G_BOOKED_ID, G_INVOICED_ID)
767 AND ALB.FUNCTION_NAME LIKE '%'|| G_FUNCTION_NAME
768 AND ALB.METRIC_CALCULATION_TYPE = G_FUNCTION
769 AND ALB.ENABLED_FLAG = G_IS_ENABLED
770 AND NVL(AL.LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS
771 )
772 WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
773 ;
774
775 -- Save all calculation upto this point.
776 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
777 l_activity_metric_id_table.DELETE;
778 l_actual_values_table.DELETE;
779
780 END IF;
781
782 END;
783
784 -- NAME
785 -- Calculate_Responses
786 --
787 -- PURPOSE
788 -- Bulk collect the responses from source tables.
789 --
790 -- HISTORY
791 -- 05-Jan-2005 dmvincen Created from calculate_seeded_metrics.
792 -- 05-Jan-2005 dmvincen Added enabled metric checks.
793 --
794 PROCEDURE Calculate_Responses
795 IS
796
797 l_actual_values_table num_table_type;
798 l_activity_metric_id_table num_table_type;
799 l_today DATE := sysdate;
800 cursor c_has_responses_enabled IS
801 SELECT count(distinct b.metric_id) metric_count,
802 count(activity_metric_id) activity_count
803 FROM ams_metrics_all_b b, ams_act_metrics_all a
804 WHERE b.metric_id in (165,166,167,168,169)
805 AND enabled_flag = G_IS_ENABLED
806 AND a.metric_id = b.metric_id
807 AND NVL(A.LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS;
808
809 l_has_enabled NUMBER;
810 l_activity_count NUMBER;
811 BEGIN
812 OPEN c_has_responses_enabled;
813 FETCH c_has_responses_enabled INTO l_has_enabled, l_activity_count;
814 CLOSE c_has_responses_enabled;
815
816 IF AMS_DEBUG_HIGH_ON THEN
817 Write_Log('Calculate_Responses','Enabled Count='||l_has_enabled,
818 'Activity Count='||l_activity_count);
819 END IF;
820
821 IF l_has_enabled > 0 THEN
822
823 SELECT NVL(actual_value, 0), activity_metric_id
827 -- R9 Campaigns/Response Count
824 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
825 FROM
826 (
828 SELECT COUNT(distinct Z.party_id) actual_value,
829 AL.activity_metric_id, AL.func_actual_value
830 FROM
831 (select arc_source_code_for, source_code_for_id, party_id
832 from jtf_ih_interactions Z, ams_source_codes C,
833 ams_act_metrics_all a, ams_metrics_all_b b
834 where c.arc_source_code_for = a.arc_act_metric_used_by
835 AND c.source_code_for_id = a.act_metric_used_by_id
836 AND a.metric_id = b.metric_id
837 and b.metric_id in (168,169,165,166,167)
838 AND NVL(a.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
839 AND b.enabled_flag = G_IS_ENABLED
840 and z.source_code_id = c.source_code_id
841 and exists (select 1 from jtf_ih_results_b Y
842 where y.result_id = z.result_id
843 and rownum = 1
844 and positive_response_flag = G_POSITIVE_RESPONSE)
845 ) Z,
846 ams_act_metrics_all AL, ams_metrics_all_b ALB
847 WHERE AL.act_metric_used_by_id = Z.source_code_for_id(+)
848 AND AL.ARC_ACT_METRIC_USED_BY = Z.arc_source_code_for(+)
849 AND AL.metric_id = ALB.metric_id
850 and ALB.metric_id in (168,169,165,166,167)
851 AND NVL(AL.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
852 AND ALB.enabled_flag = G_IS_ENABLED
853 GROUP BY AL.activity_metric_id, AL.func_actual_value
854 )
855 WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
856 ;
857
858 -- Save all calculation upto this point.
859 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
860 l_activity_metric_id_table.DELETE;
861 l_actual_values_table.DELETE;
862
863 END IF;
864 END;
865
866 -- NAME
867 -- Calculate_Registrants
868 --
869 -- PURPOSE
870 -- Bulk collect the registrants from source tables.
871 --
872 -- HISTORY
873 -- 05-Jan-2005 dmvincen Created from calculate_seeded_metrics.
874 -- 05-Jan-2005 dmvincen Added enabled metric checks.
875 --
876 PROCEDURE Calculate_Registrants
877 IS
878 l_actual_values_table num_table_type;
879 l_activity_metric_id_table num_table_type;
880 l_today DATE := sysdate;
881
882 cursor c_has_registrations_enabled IS
883 SELECT count(distinct b.metric_id) metric_count,
884 count(activity_metric_id) activity_count
885 FROM ams_metrics_all_b b, ams_act_metrics_all a
886 WHERE b.metric_id in (123,124,127, -- Registrants
887 143,144,147, -- Cancellations
888 133,134,137) -- Attendees
889 AND enabled_flag = G_IS_ENABLED
890 AND a.metric_id = b.metric_id
891 AND NVL(A.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
892
893 l_has_enabled NUMBER;
894 l_activity_count NUMBER;
895 BEGIN
896 OPEN c_has_registrations_enabled;
897 FETCH c_has_registrations_enabled INTO l_has_enabled, l_activity_count;
898 CLOSE c_has_registrations_enabled;
899
900 IF AMS_DEBUG_HIGH_ON THEN
901 Write_Log('Calculate_Registrants','Enabled Count='||l_has_enabled,
902 'Activity Count='||l_activity_count);
903 END IF;
904
905 IF l_has_enabled > 0 THEN
906
907 SELECT NVL(actual_value, 0), activity_metric_id
908 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
909 FROM
910 (
911 --Campaign schedule of type events registrants, attendees, cancellations.
912 --And Event schedule and One-off event registrants, attendees, cancellations.
913 select SUM(decode(metric_category,909,registered,
914 910,attendee,911,cancelled,0)) actual_value,
915 activity_metric_id, func_actual_value
916 from (
917 select sum(decode(system_status_code,'REGISTERED',1,0)) registered,
918 sum(decode(system_status_code||':'||attended_flag,'REGISTERED:Y',1,0)) attendee,
919 sum(decode(system_status_code,'CANCELLED',1,0)) cancelled,
920 object_id, object_type
921 from ams_event_registrations r,
922 (select distinct act_metric_used_by_id event_offer_id,
923 act_metric_used_by_id object_id,
924 arc_act_metric_used_by object_type
925 from ams_act_metrics_all a, ams_metrics_all_b b
926 where a.metric_id = b.metric_id
927 AND b.metric_id in (123,124,133,134,143,144)
928 AND b.enabled_flag = G_IS_ENABLED
929 AND NVL(A.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
930 UNION ALL
931 select distinct related_event_id, act_metric_used_by_id object_id,
932 arc_act_metric_used_by object_type
933 from ams_act_metrics_all a, ams_metrics_all_b b,
934 ams_campaign_schedules_b c
935 where a.metric_id = b.metric_id
936 AND b.metric_id in (127,137,147)
937 AND b.enabled_flag = G_IS_ENABLED
938 AND NVL(A.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
939 AND c.schedule_id = a.act_metric_used_by_id
940 AND c.activity_type_code = G_ACTIVITY_EVENTS
941 ) A
945 AMS_ACT_METRICS_ALL AL, AMS_METRICS_ALL_B ALB
942 where r.event_offer_id = a.event_offer_id
943 group by object_id, object_type
944 ) A,
946 where AL.arc_act_metric_used_by = a.object_type(+)
947 AND AL.act_metric_used_by_id = a.object_id(+)
948 and al.metric_id = alb.metric_id
949 AND ALB.metric_id in (127,137,147,123,124,133,134,143,144)
950 AND ALB.enabled_flag = G_IS_ENABLED
951 AND NVL(AL.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
952 group by activity_metric_id, func_actual_value
953 )
954 WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
955 ;
956
957 -- Save all calculation upto this point.
958 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
959 l_activity_metric_id_table.DELETE;
960 l_actual_values_table.DELETE;
961
962 END IF;
963
964 END;
965
966 -- NAME
967 -- Calculate_Quotes
968 --
969 -- PURPOSE
970 -- Bulk collect the quotes from source tables.
971 --
972 -- HISTORY
973 -- 05-Jan-2005 dmvincen Created from calculate_seeded_metrics.
974 -- 05-Jan-2005 dmvincen Added enabled metric checks.
975 -- 05-Jan-2006 dmvincen Seperated getting source code id.
976 --
977 PROCEDURE Calculate_Quotes(
978 p_arc_act_metric_used_by VARCHAR2,
979 p_act_metric_used_by_id NUMBER)
980 IS
981 l_actual_values_table num_table_type;
982 l_activity_metric_id_table num_table_type;
983 cursor c_has_quotes_enabled(p_object_type VARCHAR2,
984 p_object_id NUMBER) IS
985 SELECT count(distinct b.metric_id) metric_count,
986 count(a.activity_metric_id) activity_count
987 FROM ams_metrics_all_b b, ams_act_metrics_all a
988 WHERE b.metric_id in (244,245,246,247,248,254,255,256,257,258)
989 AND enabled_flag = G_IS_ENABLED
990 AND arc_act_metric_used_by = p_object_type
991 AND act_metric_used_by_id = p_object_id
992 AND a.metric_id = b.metric_id;
993
994 -- BUG4924982: Get source code id in a seperate step.
995 CURSOR c_get_source_code_id (p_object_type VARCHAR2,
996 p_object_id NUMBER) IS
997 select c.source_code_id
998 from ams_act_metrics_all AL, ams_metrics_all_b ALB,
999 ams_source_codes c
1000 where AL.ARC_ACT_METRIC_USED_BY = C.arc_source_code_for
1001 AND AL.act_metric_used_by_id = C.source_code_for_id
1002 AND AL.metric_id = ALB.metric_id
1003 AND ALB.metric_id in (244,245,246,247,248,254,255,256,257,258)
1004 AND ALB.enabled_flag = 'Y'
1005 AND AL.ACT_METRIC_USED_BY_ID = p_object_id
1006 AND AL.arc_act_metric_used_by = p_object_type
1007 AND rownum = 1
1008 ;
1009
1010 l_has_enabled NUMBER;
1011 l_activity_count NUMBER;
1012 l_source_code_id NUMBER;
1013 BEGIN
1014 OPEN c_has_quotes_enabled(p_arc_act_metric_used_by,
1015 p_act_metric_used_by_id);
1016 FETCH c_has_quotes_enabled INTO l_has_enabled, l_activity_count;
1017 CLOSE c_has_quotes_enabled;
1018
1019 IF AMS_DEBUG_HIGH_ON THEN
1020 Write_Log('Calculate_Quotes','Enabled Count='||l_has_enabled,
1021 'Activity Count='||l_activity_count);
1022 END IF;
1023
1024 IF l_has_enabled > 0 THEN
1025
1026 OPEN c_get_source_code_id(p_arc_act_metric_used_by,
1027 p_act_metric_used_by_id);
1028 FETCH c_get_source_code_id INTO l_source_code_id;
1029 CLOSE c_get_source_code_id;
1030
1031 SELECT NVL(actual_value, 0), activity_metric_id
1032 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
1033 FROM
1034 (SELECT decode(metric_category,914,quote_count,
1035 915,quote_amount,0) actual_value,
1036 activity_metric_id, func_actual_value
1037 FROM(
1038 SELECT count(G.quote_header_id) quote_count,
1039 sum(convert_currency(nvl(currency_code,G_FUNC_CURRENCY),
1040 total_list_price + total_adjusted_amount)) quote_amount,
1041 marketing_source_code_id
1042 FROM aso_quote_headers_all G
1043 WHERE G.marketing_source_code_id = l_source_code_id
1044 and quote_version = (select max(quote_version)
1045 from aso_quote_headers_all
1046 where quote_number = g.quote_number)
1047 GROUP BY marketing_source_code_id
1048 ) quotes,
1049 ams_act_metrics_all AL, ams_metrics_all_b ALB,
1050 ams_source_codes c
1051 where quotes.marketing_source_code_id(+) = c.source_code_id
1052 AND AL.ARC_ACT_METRIC_USED_BY = C.arc_source_code_for
1053 AND AL.act_metric_used_by_id = C.source_code_for_id
1054 AND AL.metric_id = ALB.metric_id
1055 AND ALB.metric_id in (244,245,246,247,248,254,255,256,257,258)
1056 AND ALB.enabled_flag = 'Y'
1057 AND AL.ACT_METRIC_USED_BY_ID = p_act_metric_used_by_id
1058 AND AL.arc_act_metric_used_by = P_ARC_ACT_METRIC_USED_BY
1059 ) C
1060 WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
1061 ;
1062
1063 -- Save all calculation upto this point.
1067
1064 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
1065 l_activity_metric_id_table.DELETE;
1066 l_actual_values_table.DELETE;
1068 END IF;
1069
1070 END;
1071
1072 -- NAME
1073 -- Calculate_Leads_Opps
1074 --
1075 -- PURPOSE
1076 -- Bulk collect the leads and opportunities from source tables.
1077 --
1078 -- HISTORY
1079 -- 05-Jan-2005 dmvincen Created from calculate_seeded_metrics.
1080 -- 05-Jan-2005 dmvincen Added enabled metric checks.
1081 --
1082 PROCEDURE Calculate_Leads_Opps(
1083 p_arc_act_metric_used_by VARCHAR2,
1084 p_act_metric_used_by_id NUMBER)
1085 IS
1086 l_actual_values_table num_table_type;
1087 l_activity_metric_id_table num_table_type;
1088 cursor c_has_leads_opps_enabled(p_object_type VARCHAR2,
1089 p_object_id NUMBER) IS
1090 SELECT count(distinct b.metric_id) metric_count,
1091 count(activity_metric_id) activity_count
1092 FROM ams_metrics_all_b b, ams_act_metrics_all a
1093 WHERE b.metric_id in (264,265,266,267,268, -- Dead leads
1094 83,84,85,88,89, -- Leads
1095 274,275,276,277,278, -- Leads Accepted
1096 284,285,286,287,288, -- Leads to Opportunities
1097 294,295,296,297,298, -- Top Leads
1098 93,94,95,98,99) -- Opportunities
1099 AND enabled_flag = G_IS_ENABLED
1100 AND arc_act_metric_used_by = p_object_type
1101 AND act_metric_used_by_id = p_object_id
1102 AND a.metric_id = b.metric_id;
1103
1104 cursor c_get_profiles is
1105 select max(max_score),
1106 fnd_profile.value('AS_LEAD_LINK_STATUS'),
1107 fnd_profile.value('AS_DEAD_LEAD_STATUS')
1108 from as_sales_lead_ranks_b
1109 where enabled_flag = 'Y';
1110
1111 l_max_score number;
1112 l_link_status varchar2(150);
1113 l_dead_status varchar2(150);
1114 l_has_enabled NUMBER;
1115 l_activity_count number;
1116 BEGIN
1117 OPEN c_has_leads_opps_enabled(p_arc_act_metric_used_by,
1118 p_act_metric_used_by_id);
1119 FETCH c_has_leads_opps_enabled INTO l_has_enabled, l_activity_count;
1120 CLOSE c_has_leads_opps_enabled;
1121
1122 IF AMS_DEBUG_HIGH_ON THEN
1123 Write_Log('Calculate_Leads_Opps','Enabled Count='||l_has_enabled,
1124 'Activity Count='||l_activity_count);
1125 END IF;
1126
1127 IF l_has_enabled > 0 THEN
1128
1129 OPEN c_get_profiles;
1130 FETCH c_get_profiles INTO l_max_score, l_link_status, l_dead_status;
1131 close c_get_profiles;
1132
1133 SELECT NVL(actual_value, 0), activity_metric_id
1134 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
1135 FROM (
1136 SELECT COUNT(decode(metric_sub_category,null,sales_lead_id,
1137 G_TOP_LEAD_ID,decode(lead_rank_score,l_max_score,1,null),
1138 G_ACCEPTED_LEAD_ID,decode(accepted_flag,G_IS_ACCEPTED,1,null),
1139 G_OPP_CONVERSION_ID,decode(x.status_code,l_link_status,1,null),
1140 G_DEAD_LEAD_ID,decode(x.status_code,l_dead_status,1,null))) actual_value,
1141 AL.activity_metric_id, AL.func_actual_value
1142 FROM
1143 (SELECT c.arc_source_code_for, c.source_code_for_id,
1144 x.sales_lead_id, x.lead_rank_score,
1145 NVL(X.ACCEPT_FLAG,G_NOT_ACCEPTED) accepted_flag,
1146 X.status_code
1147 FROM as_sales_leads X, as_statuses_b Y,
1148 (select distinct c.source_code_id,
1149 c.arc_source_code_for, c.source_code_for_id
1150 FROM ams_source_codes C,
1151 ams_metrics_all_b b, ams_act_metrics_all a
1152 where a.metric_id = b.metric_id
1153 AND c.arc_source_code_for = a.arc_act_metric_used_by
1154 and c.source_code_for_id = a.act_metric_used_by_id
1155 and b.enabled_flag = G_IS_ENABLED
1156 and b.metric_id in (83,84,85,88,89, -- Leads
1157 294,295,296,297,298, -- Top Leads
1158 274,275,276,277,278, -- Leads Accepted
1159 284,285,286,287,288, -- Leads to Opps
1160 264,265,266,267,268) -- Dead Leads
1161 AND C.arc_source_code_for = p_arc_act_metric_used_by
1162 AND C.source_code_for_id = p_act_metric_used_by_id
1163 ) c
1164 WHERE X.status_code = Y.status_code
1165 AND Y.lead_flag = G_IS_LEAD
1166 AND Y.enabled_flag = G_IS_ENABLED
1167 AND NVL(X.DELETED_FLAG,G_NOT_DELETED) <> G_IS_DELETED
1168 AND X.source_promotion_id = c.source_code_id
1169 ) X,
1170 ams_act_metrics_all AL, ams_metrics_all_b ALB
1171 WHERE AL.arc_act_metric_used_by = X.arc_source_code_for(+)
1172 AND AL.act_metric_used_by_id = X.source_code_for_id(+)
1173 AND AL.metric_id = ALB.metric_id
1174 AND ALB.metric_id in (83,84,85,88,89, -- Leads
1175 294,295,296,297,298, -- Top Leads
1176 274,275,276,277,278, -- Leads Accepted
1177 284,285,286,287,288, -- Leads to Opps
1178 264,265,266,267,268) -- Dead Leads
1179 AND AL.ACT_METRIC_USED_BY_ID = p_act_metric_used_by_id
1180 AND AL.ARC_ACT_METRIC_USED_BY = p_arc_act_metric_used_by
1184 --R9 Campaign Schedule/Opportunities
1181 AND ALB.enabled_flag = G_IS_ENABLED
1182 GROUP BY AL.activity_metric_id, AL.func_actual_value
1183 UNION ALL
1185 SELECT
1186 COUNT(X.lead_id) actual_value, AL.activity_metric_id,
1187 AL.func_actual_value
1188 FROM
1189 ams_source_codes C,
1190 as_leads_all X,
1191 ams_act_metrics_all AL, ams_metrics_all_b ALB
1192 WHERE X.source_promotion_id(+) = C.source_code_id
1193 AND AL.ARC_ACT_METRIC_USED_BY = C.arc_source_code_for
1194 AND AL.metric_id = ALB.metric_id
1195 AND AL.act_metric_used_by_id = C.source_code_for_id
1196 AND ALB.metric_id in (93,94,95,98,99) -- Opportunities
1197 AND ALB.enabled_flag = G_IS_ENABLED
1198 AND AL.ACT_METRIC_USED_BY_ID = p_act_metric_used_by_id
1199 AND AL.ARC_ACT_METRIC_USED_BY = p_arc_act_metric_used_by
1200 GROUP BY AL.activity_metric_id, AL.func_actual_value
1201 )
1202 WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
1203 ;
1204
1205 -- Save all calculation upto this point.
1206 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
1207 l_activity_metric_id_table.DELETE;
1208 l_actual_values_table.DELETE;
1209
1210 END IF;
1211 END;
1212
1213 -- NAME
1214 -- Calculate_Orders
1215 --
1216 -- PURPOSE
1217 -- Bulk collect the orders from source tables.
1218 --
1219 -- HISTORY
1220 -- 05-Jan-2005 dmvincen Created from calculate_seeded_metrics.
1221 -- 05-Jan-2005 dmvincen Added enabled metric checks.
1222 --
1223 PROCEDURE Calculate_Orders(
1224 p_arc_act_metric_used_by VARCHAR2,
1225 p_act_metric_used_by_id NUMBER)
1226 IS
1227 l_actual_values_table num_table_type;
1228 l_activity_metric_id_table num_table_type;
1229 cursor c_has_orders_enabled(p_object_type VARCHAR2,
1230 p_object_id NUMBER) IS
1231 SELECT count(distinct b.metric_id) metric_count,
1232 count(activity_metric_id) activity_count
1233 FROM ams_metrics_all_b b, ams_act_metrics_all a
1234 WHERE b.metric_id in (155,156,157,158,159, -- Orders
1235 103,104,105,108,109, -- Orders amount
1236 233,234,235,236,237, -- Booked Revenue
1237 220,221,222,224,225) -- Invoiced Revenue
1238 AND enabled_flag = G_IS_ENABLED
1239 AND arc_act_metric_used_by = p_object_type
1240 AND act_metric_used_by_id = p_object_id
1241 AND a.metric_id = b.metric_id;
1242
1243 l_has_enabled NUMBER;
1244 l_activity_count NUMBER;
1245 BEGIN
1246 OPEN c_has_orders_enabled(p_arc_act_metric_used_by,
1247 p_act_metric_used_by_id);
1248 FETCH c_has_orders_enabled INTO l_has_enabled, l_activity_count;
1249 CLOSE c_has_orders_enabled;
1250
1251 IF AMS_DEBUG_HIGH_ON THEN
1252 Write_Log('Calculate_Orders','Enabled Count='||l_has_enabled,
1253 'Activity Count='||l_activity_count);
1254 END IF;
1255
1256 IF l_has_enabled > 0 THEN
1257
1258 SELECT NVL(actual_value, 0), activity_metric_id
1259 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
1260 FROM
1261 (
1262 SELECT -- metrics_name,
1263 decode(decode(metric_category,G_REVENUE_ID,
1264 metric_sub_category,metric_category)
1265 , G_ORDER_COUNT_ID , order_count
1266 , G_ORDER_AMOUNT_ID , booked_revenue
1267 , G_BOOKED_ID , booked_revenue
1268 , G_INVOICED_ID , invoiced_revenue
1269 , 0 ) actual_value, activity_metric_id, func_actual_value
1270 FROM
1271 (SELECT
1272 arc_source_code_for, source_code_for_id,
1273 count(DISTINCT h.header_id) order_count,
1274 sum(convert_currency(currency_code, booked_revenue)) booked_revenue,
1275 sum(convert_currency(currency_code, invoiced_revenue)) invoiced_revenue
1276 FROM
1277 (SELECT H.arc_source_code_for, H.source_code_for_id,
1278 h.header_id,
1279 sum(nvl(H.unit_selling_price * H.ordered_quantity,0)) booked_revenue,
1280 sum(nvl(H.unit_selling_price * abs(H.invoiced_quantity),0)) invoiced_revenue,
1281 h.currency_code
1282 FROM
1283 (SELECT C.arc_source_code_for, C.source_code_for_id,
1284 i.line_id, I.unit_selling_price,
1285 decode(H.flow_status_code, G_BOOKED, H.header_id, NULL) header_id,
1286 decode(H.flow_status_code, G_BOOKED, I.ordered_quantity, 0) ordered_quantity,
1287 I.invoiced_quantity,
1288 nvl(H.transactional_curr_code,G_FUNC_CURRENCY) currency_code
1289 FROM oe_order_headers_all H, oe_order_lines_all I,
1290 ams_source_codes C
1291 WHERE H.header_id = I.header_id(+)
1292 AND H.booked_flag = G_BOOKED_FLAG
1293 AND H.booked_date IS NOT NULL
1294 AND H.marketing_source_code_id = C.source_code_id
1295 AND EXISTS (
1296 SELECT 1 FROM dual WHERE H.flow_status_code = G_BOOKED
1297 and rownum = 1
1298 UNION ALL
1299 SELECT 1 FROM OE_SYSTEM_PARAMETERS_ALL ospa, MTL_SYSTEM_ITEMS_B item
1300 WHERE H.org_id = ospa.org_id
1304 )
1301 AND I.inventory_item_id = item.inventory_item_id
1302 AND nvl(I.ship_from_org_id, ospa.master_organization_id) = item.organization_id
1303 and rownum = 1
1305 AND C.arc_source_code_for = p_arc_act_metric_used_by
1306 AND C.source_code_for_id = p_act_metric_used_by_id) H
1307 GROUP BY H.arc_source_code_for, H.source_code_for_id,
1308 h.header_id,H.currency_code
1309 ) H
1310 GROUP BY arc_source_code_for, source_code_for_id) T,
1311 ams_act_metrics_all AL, ams_metrics_all_b ALB
1312 WHERE AL.ARC_ACT_METRIC_USED_BY = T.arc_source_code_for(+)
1313 AND AL.act_metric_used_by_id = T.source_code_for_id(+)
1314 AND AL.metric_id = ALB.metric_id
1315 AND ALB.metric_id in (155,156,157,158,159, -- Orders
1316 103,104,105,108,109, -- Orders amount
1317 233,234,235,236,237, -- Booked Revenue
1318 220,221,222,224,225) -- Invoiced Revenue
1319 AND ALB.enabled_flag = G_IS_ENABLED
1320 AND AL.arc_act_metric_used_by = p_arc_act_metric_used_by
1321 AND AL.act_metric_used_by_id = p_act_metric_used_by_id
1322 )
1323 WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
1324 ;
1325 -- Save all calculation upto this point.
1326 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
1327 l_activity_metric_id_table.DELETE;
1328 l_actual_values_table.DELETE;
1329
1330 END IF;
1331 END;
1332
1333 -- NAME
1334 -- Calculate_Responses
1335 --
1336 -- PURPOSE
1337 -- Bulk collect the responses from source tables.
1338 --
1339 -- HISTORY
1340 -- 05-Jan-2005 dmvincen Created from calculate_seeded_metrics.
1341 -- 05-Jan-2005 dmvincen Added enabled metric checks.
1342 --
1343 PROCEDURE Calculate_Responses(
1344 p_arc_act_metric_used_by VARCHAR2,
1345 p_act_metric_used_by_id NUMBER)
1346 IS
1347 l_actual_values_table num_table_type;
1348 l_activity_metric_id_table num_table_type;
1349 cursor c_has_responses_enabled(p_object_type VARCHAR2,
1350 p_object_id NUMBER) IS
1351 SELECT count(distinct b.metric_id) metric_count,
1352 count(activity_metric_id) activity_count
1353 FROM ams_metrics_all_b b, ams_act_metrics_all a
1354 WHERE b.metric_id in (165,166,167,168,169)
1355 AND enabled_flag = G_IS_ENABLED
1356 AND arc_act_metric_used_by = p_object_type
1357 AND act_metric_used_by_id = p_object_id
1358 AND a.metric_id = b.metric_id;
1359
1360 l_has_enabled NUMBER;
1361 l_activity_count NUMBER;
1362 BEGIN
1363 OPEN c_has_responses_enabled(p_arc_act_metric_used_by,
1364 p_act_metric_used_by_id);
1365 FETCH c_has_responses_enabled INTO l_has_enabled, l_activity_count;
1366 CLOSE c_has_responses_enabled;
1367
1368 IF AMS_DEBUG_HIGH_ON THEN
1369 Write_Log('Calculate_Responses','Enabled Count='||l_has_enabled,
1370 'Activity Count='||l_activity_count);
1371 END IF;
1372
1373 IF l_has_enabled > 0 THEN
1374
1375 SELECT NVL(actual_value, 0), activity_metric_id
1376 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
1377 FROM (
1378 SELECT actual_value, activity_metric_id
1379 FROM(
1380 -- R9 Campaign Schedules/Response Count
1381 SELECT COUNT(DISTINCT C.party_id) actual_value,
1382 AL.activity_metric_id, AL.func_actual_value
1383 FROM
1384 (SELECT arc_source_code_for, source_code_for_id, party_id
1385 FROM jtf_ih_interactions Z, ams_source_codes C
1386 WHERE exists (select 1 from jtf_ih_results_b Y
1387 where z.result_id = y.result_id
1388 AND y.positive_response_flag = G_POSITIVE_RESPONSE
1389 and rownum = 1)
1390 AND Z.source_code_id = C.source_code_id
1391 AND C.arc_source_code_for = p_arc_act_metric_used_by
1392 AND C.source_code_for_id = p_act_metric_used_by_id) C,
1393 ams_act_metrics_all AL, ams_metrics_all_b ALB
1394 WHERE AL.act_metric_used_by_id = C.source_code_for_id(+)
1395 AND AL.ARC_ACT_METRIC_USED_BY = C.arc_source_code_for(+)
1396 AND AL.metric_id = ALB.metric_id
1397 AND ALB.metric_id in (165,166,167,168,169)
1398 AND ALB.enabled_flag = G_IS_ENABLED
1399 AND AL.ACT_METRIC_USED_BY_ID = p_act_metric_used_by_id
1400 AND AL.ARC_ACT_METRIC_USED_BY = p_arc_act_metric_used_by
1401 GROUP BY AL.activity_metric_id, AL.func_actual_value
1402
1403 )
1404 WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
1405 );
1406
1407 -- Save all calculation upto this point.
1408 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
1409 l_activity_metric_id_table.DELETE;
1410 l_actual_values_table.DELETE;
1411
1412 END IF;
1413 END;
1414
1415 -- NAME
1416 -- Calculate_Registrants
1417 --
1418 -- PURPOSE
1419 -- Bulk collect the registrants from source tables.
1420 --
1421 -- HISTORY
1422 -- 05-Jan-2005 dmvincen Created from calculate_seeded_metrics.
1426 p_arc_act_metric_used_by VARCHAR2,
1423 -- 05-Jan-2005 dmvincen Added enabled metric checks.
1424 --
1425 PROCEDURE Calculate_Registrants(
1427 p_act_metric_used_by_id NUMBER)
1428 IS
1429 l_actual_values_table num_table_type;
1430 l_activity_metric_id_table num_table_type;
1431 cursor c_has_registrations_enabled(p_object_type VARCHAR2,
1432 p_object_id NUMBER) IS
1433 SELECT count(distinct b.metric_id) metric_count,
1434 count(activity_metric_id) activity_count
1435 FROM ams_metrics_all_b b, ams_act_metrics_all a
1436 WHERE b.metric_id in (123,124,127, -- Registrants
1437 143,144,147, -- Cancellations
1438 133,134,137) -- Attendees
1439 AND enabled_flag = G_IS_ENABLED
1440 AND arc_act_metric_used_by = p_object_type
1441 AND act_metric_used_by_id = p_object_id
1442 AND a.metric_id = b.metric_id;
1443
1444 l_has_enabled NUMBER;
1445 l_activity_count NUMBER;
1446 BEGIN
1447 OPEN c_has_registrations_enabled(p_arc_act_metric_used_by,
1448 p_act_metric_used_by_id);
1449 FETCH c_has_registrations_enabled INTO l_has_enabled, l_activity_count;
1450 CLOSE c_has_registrations_enabled;
1451
1452 IF AMS_DEBUG_HIGH_ON THEN
1453 Write_Log('Calculate_Registrants','Enabled Count='||l_has_enabled,
1454 'Activity Count='||l_activity_count);
1455 END IF;
1456
1457 IF l_has_enabled > 0 THEN
1458
1459 -- Do campaign schedule specific calculations here.
1460 IF NVL(p_arc_act_metric_used_by,'NULL') = G_CSCH THEN
1461
1462 SELECT NVL(actual_value, 0), activity_metric_id
1463 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
1464 FROM
1465 (
1466 --Campaign schedule of type events registrants.
1467 select SUM(decode(metric_category,909,registered,
1468 910,attendee,911,cancelled,0)) actual_value,
1469 activity_metric_id, func_actual_value
1470 from (
1471 select sum(decode(system_status_code,'REGISTERED',1,0)) registered,
1472 sum(decode(system_status_code||':'||attended_flag,'REGISTERED:Y',1,0)) attendee,
1473 sum(decode(system_status_code,'CANCELLED',1,0)) cancelled,
1474 object_id, object_type
1475 from ams_event_registrations r,
1476 (select distinct related_event_id event_offer_id,
1477 act_metric_used_by_id object_id,
1478 arc_act_metric_used_by object_type
1479 from ams_act_metrics_all a, ams_metrics_all_b b, ams_campaign_schedules_b c
1480 where a.metric_id = b.metric_id
1481 AND b.metric_id in (127,137,147)
1482 AND a.ARC_ACT_METRIC_USED_BY = G_CSCH
1483 AND b.enabled_flag = G_IS_ENABLED
1484 AND c.schedule_id = a.act_metric_used_by_id
1485 AND c.activity_type_code = G_ACTIVITY_EVENTS
1486 AND a.act_metric_used_by_id = p_act_metric_used_by_id
1487 ) A
1488 where r.event_offer_id = a.event_offer_id
1489 group by object_id, object_type
1490 ) A,
1491 AMS_ACT_METRICS_ALL AL, AMS_METRICS_ALL_B ALB
1492 where AL.arc_act_metric_used_by = a.object_type(+)
1493 AND AL.act_metric_used_by_id = a.object_id(+)
1494 AND al.metric_id = alb.metric_id
1495 AND ALB.metric_id in (127,137,147)
1496 AND ALB.enabled_flag = G_IS_ENABLED
1497 AND AL.act_metric_used_by_id = p_act_metric_used_by_id
1498 group by activity_metric_id, func_actual_value
1499 )
1500 WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
1501 ;
1502
1503 -- Save all calculation upto this point.
1504 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
1505 l_activity_metric_id_table.DELETE;
1506 l_actual_values_table.DELETE;
1507
1508 END IF; -- CSCH only
1509
1510 IF NVL(p_arc_act_metric_used_by,'NULL') IN (G_EVEO, G_EONE) THEN
1511 SELECT NVL(actual_value, 0), activity_metric_id
1512 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
1513 FROM (
1514 --R9 (Event Schedule/One-off Event)/Registrations
1515 select SUM(decode(metric_category,909,registered,
1516 910,attendee,911,cancelled,0)) actual_value,
1517 activity_metric_id, func_actual_value
1518 from (
1519 select sum(decode(system_status_code,'REGISTERED',1,0)) registered,
1520 sum(decode(system_status_code||':'||attended_flag,'REGISTERED:Y',1,0)) attendee,
1521 sum(decode(system_status_code,'CANCELLED',1,0)) cancelled,
1522 object_id, object_type
1523 from ams_event_registrations r,
1524 (select distinct act_metric_used_by_id event_offer_id,
1525 act_metric_used_by_id object_id,
1526 arc_act_metric_used_by object_type
1527 from ams_act_metrics_all a, ams_metrics_all_b b
1528 where a.metric_id = b.metric_id
1529 AND b.metric_id in (123,124,133,134,143,144)
1530 AND a.ARC_ACT_METRIC_USED_BY = p_arc_act_metric_used_by
1531 AND b.enabled_flag = G_IS_ENABLED
1532 AND a.act_metric_used_by_id = p_act_metric_used_by_id
1533 ) A
1534 where r.event_offer_id = a.event_offer_id
1535 group by object_id, object_type
1536 ) A,
1540 and al.metric_id = alb.metric_id
1537 AMS_ACT_METRICS_ALL AL, AMS_METRICS_ALL_B ALB
1538 where AL.arc_act_metric_used_by = a.object_type(+)
1539 AND AL.act_metric_used_by_id = a.object_id(+)
1541 AND ALB.metric_id in (123,124,133,134,143,144)
1542 AND ALB.enabled_flag = G_IS_ENABLED
1543 AND AL.act_metric_used_by_id = p_act_metric_used_by_id
1544 AND AL.ARC_ACT_METRIC_USED_BY = p_arc_act_metric_used_by
1545 group by activity_metric_id, func_actual_value
1546 )
1547 WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
1548 ;
1549
1550 -- Save all calculation upto this point.
1551 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
1552 l_activity_metric_id_table.DELETE;
1553 l_actual_values_table.DELETE;
1554
1555 END IF; -- EVEO EONE only
1556 END IF;
1557 END;
1558
1559 -- NAME
1560 -- Calculate_Seeded_Metrics
1561 --
1562 -- PURPOSE
1563 -- Bulk collect the metrics from source tables.
1564 --
1565 -- NOTES
1566 -- Only one bulk collect should be executed.
1567 -- Number of orders for events is currently unavailable.
1568 --
1569 -- HISTORY
1570 -- 30-Aug-2001 dmvincen Created.
1571 -- 24-Oct-2001 dmvincen Fixed update for existing values.
1572 -- 05-Jan-2005 dmvincen Broke out queries into smaller procedures.
1573 --
1574
1575 PROCEDURE Calculate_Seeded_Metrics(
1576 p_arc_act_metric_used_by VARCHAR2 := NULL,
1577 p_act_metric_used_by_id NUMBER := NULL)
1578 IS
1579 --l_actual_values_table num_table_type;
1580 --l_activity_metric_id_table num_table_type;
1581 BEGIN
1582 IF AMS_DEBUG_HIGH_ON THEN
1583 Write_Log('Calculate_Seeded_Metrics','BEGIN');
1584 END IF;
1585 -- Get all object data to update.
1586 IF p_arc_act_metric_used_by IS NULL THEN
1587
1588 Calculate_Quotes;
1589
1590 Calculate_Leads_Opps;
1591
1592 Calculate_Orders;
1593
1594 Calculate_Responses;
1595
1596 Calculate_Registrants;
1597
1598 ELSE -- if object type is set.
1599
1600 Calculate_Quotes(p_arc_act_metric_used_by, p_act_metric_used_by_id);
1601
1602 Calculate_Leads_Opps(p_arc_act_metric_used_by, p_act_metric_used_by_id);
1603
1604 Calculate_Orders(p_arc_act_metric_used_by, p_act_metric_used_by_id);
1605
1606 Calculate_Responses(p_arc_act_metric_used_by, p_act_metric_used_by_id);
1607
1608 END IF;
1609
1610 IF NVL(p_arc_act_metric_used_by,'NULL') IN (G_CSCH, G_EONE, G_EVEO) THEN
1611
1612 Calculate_Registrants(p_arc_act_metric_used_by, p_act_metric_used_by_id);
1613
1614 END IF; -- CSCH EVEO EONE only
1615
1616 IF AMS_DEBUG_HIGH_ON THEN
1617 Write_Log('Calculate_Seeded_Metrics','END');
1618 END IF;
1619
1620 END Calculate_Seeded_Metrics;
1621
1622 -- NAME
1623 -- Calculate_Target_Group
1624 --
1625 -- PURPOSE
1626 -- Bulk collect the target group counts into metrics.
1627 --
1628 -- HISTORY
1629 -- 05-Aug-2003 sunkumar created.
1630 -- 05-Jan-2005 dmvincen Checking for Enabled metrics.
1631 --
1632 --
1633
1634 PROCEDURE Calculate_Target_Group(
1635 p_arc_act_metric_used_by VARCHAR2 := NULL,
1636 p_act_metric_used_by_id NUMBER := NULL)
1637 IS
1638 l_actual_values_table num_table_type;
1639 l_activity_metric_id_table num_table_type;
1640 l_today DATE := sysdate;
1641 cursor c_has_traget_enabled IS
1642 SELECT count(distinct a.metric_id) metric_count,
1643 count(activity_metric_id) activity_count
1644 FROM ams_metrics_all_b b, ams_act_metrics_all a
1645 WHERE b.metric_id in (304,305,306,307,308, -- Contact Group
1646 314,315,316,317,318) -- Control Group
1647 AND enabled_flag = G_IS_ENABLED
1648 AND a.metric_id = b.metric_id
1649 AND NVL(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
1650
1651 cursor c_has_traget_enabled_obj(p_object_type VARCHAR2,
1652 p_object_id NUMBER) IS
1653 SELECT count(distinct a.metric_id) metric_count,
1654 count(activity_metric_id) activity_count
1655 FROM ams_metrics_all_b b, ams_act_metrics_all a
1656 WHERE b.metric_id in (304,305,306,307,308, -- Contact Group
1657 314,315,316,317,318) -- Control Group
1658 AND enabled_flag = G_IS_ENABLED
1659 AND arc_act_metric_used_by = p_object_type
1660 AND act_metric_used_by_id = p_object_id
1661 AND a.metric_id = b.metric_id;
1662
1663 l_has_enabled NUMBER;
1664 l_activity_count NUMBER;
1665 BEGIN
1666 -- Get all object data to update.
1667 IF p_arc_act_metric_used_by IS NULL THEN
1668
1669 OPEN c_has_traget_enabled;
1670 FETCH c_has_traget_enabled INTO l_has_enabled, l_activity_count;
1671 CLOSE c_has_traget_enabled;
1672
1673 IF AMS_DEBUG_HIGH_ON THEN
1674 Write_Log('Calculate_Target_Group','Enabled Count='||l_has_enabled,
1675 'Activity Count='||l_activity_count);
1676 END IF;
1677
1678 IF l_has_enabled > 0 THEN
1679
1680 SELECT NVL(actual_value, 0) actual_value, activity_metric_id
1681 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
1685 SELECT SUM(NVL (decode(ALB.metric_sub_category,
1682 FROM
1683 (
1684 --R10 contact/control group count
1686 G_CONTACT_GROUP_ID, ACT.no_of_rows_active,
1687 G_CONTROL_GROUP_ID, ACT.no_of_rows_in_ctrl_group,0),0)) actual_value,
1688 AL.activity_metric_id,AL.func_actual_value
1689 FROM
1690 (SELECT al.activity_metric_id,
1691 alh.no_of_rows_active, alh.no_of_rows_in_ctrl_group
1692 FROM ams_list_headers_all ALH, ams_act_metrics_all al,
1693 ams_metrics_all_b alb
1694 WHERE ALH.arc_list_used_by = al.arc_act_metric_used_by
1695 AND ALH.list_used_by_id = al.act_metric_used_by_id
1696 AND al.metric_id = alb.metric_id
1697 and alb.metric_id in (304,305,306,307,308, -- Contact Group
1698 314,315,316,317,318) -- Control Group
1699 and alb.enabled_flag = 'Y'
1700 AND NVL(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
1701 ) ACT,
1702 ams_act_metrics_all AL, ams_metrics_all_b ALB
1703 WHERE ACT.activity_metric_id(+) = AL.activity_metric_id
1704 AND AL.metric_id=ALB.METRIC_ID
1705 AND ALB.metric_id in (304,305,306,307,308, -- Contact Group
1706 314,315,316,317,318) -- Control Group
1707 AND ALB.metric_category = G_TARGET_GROUP_ID
1708 AND ALB.metric_sub_category in
1709 (G_CONTACT_GROUP_ID,G_CONTROL_GROUP_ID)
1710 AND ALB.function_name LIKE '%'|| G_TARGET_FUNCTION_NAME
1711 AND ALB.metric_calculation_type = G_FUNCTION
1712 AND ALB.enabled_flag = G_IS_ENABLED
1713 AND NVL(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
1714 GROUP BY AL.activity_metric_id, AL.func_actual_value
1715 )
1716 WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
1717 ;
1718
1719 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
1720 l_activity_metric_id_table.DELETE;
1721 l_actual_values_table.DELETE;
1722
1723 END IF;
1724 -- ELSIF p_arc_act_metric_used_by = G_CSCH THEN
1725 ELSE -- IF object is specified, all object types supported.
1726
1727 OPEN c_has_traget_enabled_obj(p_arc_act_metric_used_by,
1728 p_act_metric_used_by_id);
1729 FETCH c_has_traget_enabled_obj INTO l_has_enabled, l_activity_count;
1730 CLOSE c_has_traget_enabled_obj;
1731
1732 IF AMS_DEBUG_HIGH_ON THEN
1733 Write_Log('Calculate_Target_Group','Enabled Count='||l_has_enabled,
1734 'Activity Count='||l_activity_count);
1735 END IF;
1736
1737 IF l_has_enabled > 0 THEN
1738
1739 SELECT NVL(actual_value, 0) actual_value, activity_metric_id
1740 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
1741 FROM
1742 (
1743 --R10 contact/control group count
1744 SELECT SUM(NVL (decode(ALB.metric_sub_category,
1745 G_CONTACT_GROUP_ID, ACT.no_of_rows_active,
1746 G_CONTROL_GROUP_ID, ACT.no_of_rows_in_ctrl_group,0),0)) actual_value,
1747 AL.activity_metric_id,AL.func_actual_value
1748 FROM
1749 (SELECT al.activity_metric_id,
1750 alh.no_of_rows_active, alh.no_of_rows_in_ctrl_group
1751 FROM ams_list_headers_all ALH, ams_act_metrics_all al,
1752 ams_metrics_all_b alb
1753 WHERE ALH.arc_list_used_by = al.arc_act_metric_used_by
1754 AND ALH.list_used_by_id = al.act_metric_used_by_id
1755 AND al.metric_id = alb.metric_id
1756 and alb.metric_id in (304,305,306,307,308, -- Contact Group
1757 314,315,316,317,318) -- Control Group
1758 and alb.enabled_flag = 'Y') ACT,
1759 ams_act_metrics_all AL, ams_metrics_all_b ALB
1760 WHERE ACT.activity_metric_id(+) = AL.activity_metric_id
1761 AND AL.metric_id=ALB.METRIC_ID
1762 AND ALB.metric_id in (304,305,306,307,308, -- Contact Group
1763 314,315,316,317,318) -- Control Group
1764 AND ALB.metric_category = G_TARGET_GROUP_ID
1765 AND ALB.metric_sub_category in
1766 (G_CONTACT_GROUP_ID,G_CONTROL_GROUP_ID)
1767 AND ALB.function_name LIKE '%'|| G_TARGET_FUNCTION_NAME
1768 AND ALB.metric_calculation_type = G_FUNCTION
1769 AND ALB.enabled_flag = G_IS_ENABLED
1770 AND AL.ACT_METRIC_USED_BY_ID = p_act_metric_used_by_id
1771 AND AL.ARC_ACT_METRIC_USED_BY = p_arc_act_metric_used_by
1772 GROUP BY AL.activity_metric_id, AL.func_actual_value
1773 )
1774 WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
1775 ;
1776
1777 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
1778 l_activity_metric_id_table.DELETE;
1779 l_actual_values_table.DELETE;
1780
1781 END IF;
1782
1783 END IF;
1784
1785 END Calculate_Target_Group;
1786
1787 -- NAME
1788 -- Calculate_List_Target
1789 --
1790 -- PURPOSE
1791 -- Bulk collect the list data from sources comparing against the list
1792 -- entries.
1793 --
1794 -- HISTORY
1798 IS
1795 -- 05-Jan-2005 dmvincen Derived from Calculate_Seeded_list_metrics.
1796 --
1797 PROCEDURE Calculate_List_Target
1799 l_actual_values_table num_table_type;
1800 l_activity_metric_id_table num_table_type;
1801 l_today DATE := sysdate;
1802 cursor c_has_traget_enabled IS
1803 SELECT count(distinct b.metric_id) metric_count,
1804 count(activity_metric_id) activity_count
1805 FROM ams_metrics_all_b b, ams_act_metrics_all a
1806 WHERE b.metric_id IN (347, 348)
1807 AND enabled_flag = G_IS_ENABLED
1808 AND a.metric_id = b.metric_id
1809 AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
1810
1811 l_has_enabled NUMBER;
1812 l_activity_count NUMBER;
1813 BEGIN
1814 OPEN c_has_traget_enabled;
1815 FETCH c_has_traget_enabled INTO l_has_enabled, l_activity_count;
1816 CLOSE c_has_traget_enabled;
1817
1818 IF AMS_DEBUG_HIGH_ON THEN
1819 Write_Log('Calculate_List_Target','Enabled Count='||l_has_enabled,
1820 'Activity Count='||l_activity_count);
1821 END IF;
1822
1823 IF l_has_enabled > 0 THEN
1824
1825 SELECT NVL(actual_value, 0), activity_metric_id
1826 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
1827 FROM (
1828 --------------------------------------------------------
1829 -- choang - 03-mar-2004 - outer-joins needed to pick up
1830 -- objects which no longer exist; deleted act
1831 -- list, for example;
1832 -- hard-code the metric id's for performance
1833 --------------------------------------------------------
1834 --contact group count at list level
1835 SELECT COUNT(contacts.list_entry_id) actual_value
1836 , actmet.activity_metric_id
1837 , actmet.func_actual_value
1838 FROM
1839 (
1840 SELECT LIST.list_entry_id, 'ALIST' object_type,
1841 actlist.act_list_header_id
1842 FROM ams_act_lists actlist
1843 , ams_list_entries LIST
1844 , ams_act_metrics_all a
1845 , ams_metrics_all_b b
1846 WHERE LIST.list_header_id = actlist.list_header_id
1847 AND actlist.list_act_type = 'LIST'
1848 AND EXISTS (SELECT 1
1849 FROM ams_list_entries target, ams_act_lists acttarget
1850 WHERE acttarget.list_used_by = actlist.list_used_by
1851 AND acttarget.list_used_by_id = actlist.list_used_by_id
1852 AND acttarget.list_act_type = 'TARGET'
1853 AND target.list_header_id = acttarget.list_header_id
1854 AND target.list_entry_source_system_id =
1855 LIST.list_entry_source_system_id
1856 AND target.list_entry_source_system_type =
1857 LIST.list_entry_source_system_type
1858 AND target.enabled_flag = 'Y'
1859 AND target.part_of_control_group_flag = 'N'
1860 AND rownum = 1
1861 )
1862 AND LIST.enabled_flag = 'Y'
1863 AND LIST.part_of_control_group_flag = 'N'
1864 AND a.metric_id = b.metric_id
1865 AND actlist.act_list_header_id = a.act_metric_used_by_id
1866 AND a.arc_act_metric_used_by = 'ALIST'
1867 AND NVL(a.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
1868 AND b.metric_id = 347
1869 AND b.enabled_flag = G_IS_ENABLED
1870 ) contacts
1871 , ams_act_metrics_all actmet, ams_metrics_all_b ALB
1872 WHERE ALB.metric_id = 347
1873 AND actmet.metric_id = ALB.metric_id
1874 AND ALB.enabled_flag = G_IS_ENABLED
1875 AND contacts.act_list_header_id (+) = actmet.act_metric_used_by_id
1876 AND contacts.object_type(+) = actmet.arc_act_metric_used_by
1877 AND NVL(actmet.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
1878 GROUP BY actmet.activity_metric_id, actmet.func_actual_value
1879
1880 UNION ALL
1881
1882 --R10 Control Group Count at list level
1883 SELECT COUNT(controls.list_entry_id) actual_value
1884 , actmet.activity_metric_id
1885 , actmet.func_actual_value
1886 FROM
1887 (
1888 SELECT LIST.list_entry_id, 'ALIST' object_type,
1889 actlist.act_list_header_id
1890 FROM ams_act_lists actlist
1891 , ams_list_entries LIST
1892 , ams_act_metrics_all a
1893 , ams_metrics_all_b b
1894 WHERE LIST.list_header_id = actlist.list_header_id
1895 AND actlist.list_act_type = 'LIST'
1896 AND EXISTS (SELECT 1
1897 FROM ams_list_entries target, ams_act_lists acttarget
1898 WHERE acttarget.list_used_by = actlist.list_used_by
1899 AND acttarget.list_used_by_id = actlist.list_used_by_id
1900 AND acttarget.list_act_type = 'TARGET'
1901 AND target.list_header_id = acttarget.list_header_id
1902 AND target.list_entry_source_system_id =
1903 LIST.list_entry_source_system_id
1904 AND target.list_entry_source_system_type =
1905 LIST.list_entry_source_system_type
1906 AND target.enabled_flag = 'N'
1910 AND LIST.enabled_flag = 'N'
1907 AND target.part_of_control_group_flag = 'Y'
1908 AND rownum = 1
1909 )
1911 AND LIST.part_of_control_group_flag = 'Y'
1912 AND a.metric_id = b.metric_id
1913 AND actlist.act_list_header_id = a.act_metric_used_by_id
1914 AND a.arc_act_metric_used_by = 'ALIST'
1915 AND NVL(a.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
1916 AND b.metric_id = 348
1917 AND b.enabled_flag = 'Y'
1918 ) controls
1919 , ams_act_metrics_all actmet, ams_metrics_all_b ALB
1920 WHERE ALB.metric_id = 348
1921 AND actmet.metric_id = ALB.metric_id
1922 AND ALB.enabled_flag = 'Y'
1923 AND controls.act_list_header_id (+) = actmet.act_metric_used_by_id
1924 AND controls.object_type(+) = actmet.arc_act_metric_used_by
1925 AND NVL(actmet.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
1926 GROUP BY actmet.activity_metric_id, actmet.func_actual_value
1927 )
1928 WHERE NVL(actual_value, 0) <> NVL(func_actual_value, -1);
1929
1930 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
1931 l_activity_metric_id_table.DELETE;
1932 l_actual_values_table.DELETE;
1933
1934 END IF;
1935
1936 END;
1937
1938 -- NAME
1939 -- Calculate_List_Resp_Lead_Opp
1940 --
1941 -- PURPOSE
1942 -- Bulk collect the list data from sources comparing against the list
1943 -- entries.
1944 --
1945 -- HISTORY
1946 -- 05-Jan-2005 dmvincen Derived from Calculate_Seeded_list_metrics.
1947 -- 03-Mar-2004 choang Re-wrote all queries to take org contacts into consideration
1948 -- 04-Mar-2004 choang Removed org contact validation for control and contact
1949 -- queries; validation is only required for the entries
1950 -- in the list to exist in the target group.
1951 -- 05-Jan-2005 dmvincen Separated the queries into procedures for better
1952 -- management.
1953 --
1954 PROCEDURE Calculate_List_Resp_Lead_Opp
1955 IS
1956 l_actual_values_table num_table_type;
1957 l_activity_metric_id_table num_table_type;
1958 l_today DATE := sysdate;
1959 cursor c_has_metrics_enabled IS
1960 SELECT count(decode(b.metric_id,321,1,null)) response_count,
1961 count(decode(b.metric_id,326,1,null)) leads_count,
1962 count(decode(b.metric_id,331,1,null)) opportunities_count
1963 FROM ams_metrics_all_b b, ams_act_metrics_all a
1964 WHERE b.metric_id IN (321, 326, 331)
1965 AND enabled_flag = G_IS_ENABLED
1966 AND a.metric_id = b.metric_id
1967 AND NVL(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
1968 ;
1969
1970 l_response_count NUMBER;
1971 l_leads_count NUMBER;
1972 l_opps_count NUMBER;
1973 BEGIN
1974 OPEN c_has_metrics_enabled;
1975 FETCH c_has_metrics_enabled
1976 INTO l_response_count, l_leads_count, l_opps_count;
1977 CLOSE c_has_metrics_enabled;
1978
1979 IF AMS_DEBUG_HIGH_ON THEN
1980 Write_Log('Calculate_List_Resp_Lead_Opp','Response Count='||l_response_count);
1981 END IF;
1982
1983 IF l_response_count > 0 THEN
1984
1985 SELECT NVL(actual_value, 0), activity_metric_id
1986 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
1987 FROM (
1988 /* BUG 4070346: Performance issue: This new query performs a full
1989 * table scan on the source codes table. This is much smaller
1990 * than using the list entries or relationships tables. Each
1991 * sub query returns a distinct list of party IDs for each activity
1992 * list. Then each set of party IDs is checked against the
1993 * interactions to see if it has a positive response. The final list
1994 * of party ids is unique per activity list and thus does not require
1995 * a distinct count.
1996 */
1997 --R10: Responses Count
1998 SELECT PARTIES ACTUAL_VALUE
1999 , ACTMET.ACTIVITY_METRIC_ID
2000 , ACTMET.FUNC_ACTUAL_VALUE
2001 FROM (
2002 SELECT COUNT(PARTY_ID) PARTIES, OBJECT_TYPE, OBJECT_ID
2003 FROM (
2004 -- SELECT DISTINCT LIST OF B2C AND B2B PARTIES
2005 SELECT distinct ACTLIST.ACT_LIST_HEADER_ID OBJECT_ID,
2006 'ALIST' OBJECT_TYPE,
2007 decode(src.source_category,
2008 'B2C',ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID,
2009 'B2B',REL.OBJECT_ID,null) PARTY_ID,
2010 SOURCE.SOURCE_CODE_ID, src.source_category
2011 FROM AMS_ACT_LISTS ACTLIST,
2012 AMS_LIST_ENTRIES ENTRY,
2013 AMS_SOURCE_CODES SOURCE,
2014 AMS_LIST_SRC_TYPES SRC,
2015 HZ_RELATIONSHIPS REL,
2016 AMS_METRICS_ALL_B B,
2017 AMS_ACT_METRICS_ALL A
2018 WHERE ENTRY.LIST_HEADER_ID = ACTLIST.LIST_HEADER_ID
2019 AND SOURCE.SOURCE_CODE_FOR_ID = ACTLIST.LIST_USED_BY_ID
2020 AND SOURCE.ARC_SOURCE_CODE_FOR = ACTLIST.LIST_USED_BY
2024 AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
2021 AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE =
2022 SRC.SOURCE_TYPE_CODE
2023 AND SRC.LIST_SOURCE_TYPE = 'TARGET'
2025 AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
2026 AND REL.DIRECTIONAL_FLAG(+) = 'F'
2027 AND A.METRIC_ID = B.METRIC_ID
2028 AND B.ENABLED_FLAG = G_IS_ENABLED
2029 AND nvl(LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS
2030 AND B.METRIC_ID = 321
2031 AND a.arc_act_metric_used_by = 'ALIST'
2032 AND a.act_metric_used_by_id = ACTLIST.ACT_LIST_HEADER_ID
2033 AND actlist.list_act_type = 'LIST'
2034 ) LIST_PARTIES
2035 -- CHECK EACH PARTY FOR A POSITIVE RESPONSE WITHIN THE SOURCE CODE.
2036 WHERE EXISTS (SELECT 1
2037 FROM JTF_IH_INTERACTIONS INTER, JTF_IH_RESULTS_B RESULT
2038 WHERE RESULT.POSITIVE_RESPONSE_FLAG = 'Y'
2039 AND RESULT.RESULT_ID = INTER.RESULT_ID
2040 AND INTER.SOURCE_CODE_ID = LIST_PARTIES.SOURCE_CODE_ID
2041 AND INTER.PARTY_ID = LIST_PARTIES.PARTY_ID
2042 AND ROWNUM = 1)
2043 GROUP BY OBJECT_TYPE, OBJECT_ID
2044 ) RESP
2045 , AMS_ACT_METRICS_ALL ACTMET, AMS_METRICS_ALL_B ALB
2046 WHERE ALB.METRIC_ID = 321
2047 AND ACTMET.METRIC_ID = ALB.METRIC_ID
2048 AND ALB.ENABLED_FLAG = G_IS_ENABLED
2049 AND RESP.OBJECT_ID (+) = ACTMET.ACT_METRIC_USED_BY_ID
2050 AND RESP.OBJECT_TYPE(+) = ACTMET.ARC_ACT_METRIC_USED_BY
2051 AND nvl(LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS
2052
2053 /******* BUG 4070346: End of new query *******/
2054
2055 )
2056 WHERE NVL(actual_value, 0) <> NVL(func_actual_value, -1);
2057
2058 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
2059 l_activity_metric_id_table.DELETE;
2060 l_actual_values_table.DELETE;
2061
2062 END IF;
2063
2064 IF AMS_DEBUG_HIGH_ON THEN
2065 Write_Log('Calculate_List_Resp_Lead_Opp','Leads Count='||l_leads_count);
2066 END IF;
2067
2068 IF l_leads_count > 0 THEN
2069
2070 SELECT NVL(actual_value, 0), activity_metric_id
2071 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
2072 FROM (
2073 --R10: Leads Count
2074 --sunkumar - 28-Jan-04 modified to include the join between party id's
2075 SELECT COUNT( leads.sales_lead_id) actual_value
2076 , actmet.activity_metric_id
2077 , actmet.func_actual_value
2078 FROM
2079 (
2080 SELECT lead.sales_lead_id,
2081 LIST_PARTIES.activity_metric_id
2082 FROM as_sales_leads lead
2083 , as_statuses_b lead_status
2084 , (
2085 -- SELECT DISTINCT LIST OF B2C AND B2B PARTIES
2086 SELECT distinct A.activity_metric_id,
2087 decode(src.source_category,
2088 'B2C',ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID,
2089 'B2B',REL.OBJECT_ID,null) PARTY_ID,
2090 SOURCE.SOURCE_CODE_ID, src.source_category
2091 FROM AMS_ACT_LISTS ACTLIST,
2092 AMS_LIST_ENTRIES ENTRY,
2093 AMS_SOURCE_CODES SOURCE,
2094 AMS_LIST_SRC_TYPES SRC,
2095 HZ_RELATIONSHIPS REL,
2096 AMS_METRICS_ALL_B B,
2097 AMS_ACT_METRICS_ALL A
2098 WHERE ENTRY.LIST_HEADER_ID = ACTLIST.LIST_HEADER_ID
2099 AND SOURCE.SOURCE_CODE_FOR_ID = ACTLIST.LIST_USED_BY_ID
2100 AND SOURCE.ARC_SOURCE_CODE_FOR = ACTLIST.LIST_USED_BY
2101 AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE =
2102 SRC.SOURCE_TYPE_CODE
2103 AND SRC.LIST_SOURCE_TYPE = 'TARGET'
2104 AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
2105 AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
2106 AND REL.DIRECTIONAL_FLAG(+) = 'F'
2107 AND A.METRIC_ID = B.METRIC_ID
2108 AND B.ENABLED_FLAG = G_IS_ENABLED
2109 AND nvl(LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS
2110 AND B.METRIC_ID = 326
2111 AND a.arc_act_metric_used_by = 'ALIST'
2112 AND a.act_metric_used_by_id = ACTLIST.ACT_LIST_HEADER_ID
2113 AND actlist.list_act_type = 'LIST'
2114 ) LIST_PARTIES
2115 WHERE lead.status_code = lead_status.status_code
2116 AND lead_status.lead_flag = 'Y'
2117 AND lead_status.enabled_flag = 'Y'
2118 AND lead.source_promotion_id = LIST_PARTIES.source_code_id
2119 AND NVL(lead.deleted_flag, 'N') <> 'Y'
2120 AND lead.customer_id = list_parties.party_id
2121 ) leads
2122 , ams_act_metrics_all actmet, ams_metrics_all_b ALB
2123 WHERE ALB.metric_id = 326 -- metric id for leads
2124 AND actmet.metric_id = ALB.metric_id
2125 AND ALB.enabled_flag = G_IS_ENABLED
2126 AND leads.activity_metric_id = actmet.activity_metric_id
2127 AND nvl(LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS
2131
2128 GROUP BY actmet.activity_metric_id, actmet.func_actual_value
2129 )
2130 WHERE NVL(actual_value, 0) <> NVL(func_actual_value, -1);
2132 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
2133 l_activity_metric_id_table.DELETE;
2134 l_actual_values_table.DELETE;
2135
2136 END IF;
2137
2138 IF AMS_DEBUG_HIGH_ON THEN
2139 Write_Log('Calculate_List_Resp_Lead_Opp','Opportunities Count='||l_opps_count);
2140 END IF;
2141
2142 IF l_opps_count > 0 THEN
2143
2144 SELECT NVL(actual_value, 0), activity_metric_id
2145 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
2146 FROM (
2147 --R10: Opportunities
2148 --sunkumar - 28-Jan-04 modified to include the join between party id's
2149 --sunkumar - 02-mar-2004 removed cartesian join
2150 SELECT COUNT( opps.lead_id) actual_value
2151 , actmet.activity_metric_id
2152 , actmet.func_actual_value
2153 FROM
2154 (
2155 SELECT lead.lead_id, list_parties.activity_metric_id
2156 FROM as_leads_all lead
2157 , (
2158 -- SELECT DISTINCT LIST OF B2C AND B2B PARTIES
2159 SELECT distinct A.activity_metric_id,
2160 decode(src.source_category,
2161 'B2C',ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID,
2162 'B2B',REL.OBJECT_ID,null) PARTY_ID,
2163 SOURCE.SOURCE_CODE_ID, src.source_category
2164 FROM AMS_ACT_LISTS ACTLIST,
2165 AMS_LIST_ENTRIES ENTRY,
2166 AMS_SOURCE_CODES SOURCE,
2167 AMS_LIST_SRC_TYPES SRC,
2168 HZ_RELATIONSHIPS REL,
2169 AMS_METRICS_ALL_B B,
2170 AMS_ACT_METRICS_ALL A
2171 WHERE ENTRY.LIST_HEADER_ID = ACTLIST.LIST_HEADER_ID
2172 AND SOURCE.SOURCE_CODE_FOR_ID = ACTLIST.LIST_USED_BY_ID
2173 AND SOURCE.ARC_SOURCE_CODE_FOR = ACTLIST.LIST_USED_BY
2174 AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE =
2175 SRC.SOURCE_TYPE_CODE
2176 AND SRC.LIST_SOURCE_TYPE = 'TARGET'
2177 AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
2178 AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
2179 AND REL.DIRECTIONAL_FLAG(+) = 'F'
2180 AND A.METRIC_ID = B.METRIC_ID
2181 AND B.ENABLED_FLAG = G_IS_ENABLED
2182 AND nvl(LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS
2183 AND B.METRIC_ID = 331
2184 AND a.arc_act_metric_used_by = 'ALIST'
2185 AND a.act_metric_used_by_id = ACTLIST.ACT_LIST_HEADER_ID
2186 AND actlist.list_act_type = 'LIST'
2187 ) LIST_PARTIES
2188 WHERE lead.source_promotion_id = LIST_PARTIES.source_code_id
2189 AND lead.customer_id = list_parties.party_id
2190 ) opps
2191 , ams_act_metrics_all actmet, ams_metrics_all_b ALB
2192 WHERE ALB.metric_id = 331 -- metric id for opportunities
2193 AND actmet.metric_id = ALB.metric_id
2194 AND ALB.enabled_flag = G_IS_ENABLED
2195 AND opps.activity_metric_id (+) = actmet.activity_metric_id
2196 AND nvl(LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS
2197 GROUP BY actmet.activity_metric_id, actmet.func_actual_value
2198 )
2199 WHERE NVL(actual_value, 0) <> NVL(func_actual_value, -1);
2200
2201 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
2202 l_activity_metric_id_table.DELETE;
2203 l_actual_values_table.DELETE;
2204
2205 END IF;
2206
2207 END;
2208
2209 -- NAME
2210 -- Calculate_List_Orders
2211 --
2212 -- PURPOSE
2213 -- Bulk collect the seed values from order entries and compare against
2214 -- the list entries.
2215 --
2216 -- HISTORY
2217 -- 05-Jan-2005 dmvincen Created from Calculated_Seeded_list_metrics
2218 -- 17-Dec-2003 choang Fixed SQL for contact group, control group and responses
2219 -- 03-Mar-2004 choang Re-wrote all queries to take org contacts into consideration
2220 --
2221 --
2222 PROCEDURE Calculate_List_Orders
2223 IS
2224 l_actual_values_table num_table_type;
2225 l_activity_metric_id_table num_table_type;
2226 l_today DATE := sysdate;
2227 cursor c_has_orders_enabled IS
2228 SELECT count(distinct b.metric_id) metric_count,
2229 count(a.activity_metric_id) activity_count
2230 FROM ams_metrics_all_b b, ams_act_metrics_all a
2231 WHERE b.metric_id IN (336, 341, 346)
2232 AND enabled_flag = G_IS_ENABLED
2233 AND a.metric_id = b.metric_id
2234 AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
2235
2236 l_has_enabled NUMBER;
2237 l_activity_count NUMBER;
2238 BEGIN
2239 OPEN c_has_orders_enabled;
2240 FETCH c_has_orders_enabled INTO l_has_enabled, l_activity_count;
2241 CLOSE c_has_orders_enabled;
2242
2243 IF AMS_DEBUG_HIGH_ON THEN
2244 Write_Log('Calculate_List_Orders','Enabled Count='||l_has_enabled,
2245 'Activity Count='||l_activity_count);
2246 END IF;
2247
2248 IF l_has_enabled > 0 THEN
2249
2250 SELECT NVL(actual_value, 0), activity_metric_id
2254 /***** BUG 4070346: Improved performance for list orderes.
2251 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
2252 FROM (
2253 --R10: Bookedorders
2255 ****** 12/21/2004 New Order/booked revenue/invoiced revenue list query.
2256 ****** Combines the tree queries to lookup all the order information at
2257 ****** once and dividing the results amount the appropriate metrics
2258 ******/
2259 SELECT -- metrics_name,
2260 decode(AL.metric_id
2261 , 336 , order_count
2262 , 341 , booked_revenue
2263 , 346 , invoiced_revenue
2264 , 0 ) actual_value, activity_metric_id, func_actual_value
2265 FROM
2266 (SELECT
2267 object_type, object_id,
2268 count(DISTINCT h.header_id) order_count,
2269 sum(ams_actmetrics_seed_pvt.convert_currency(currency_code, booked_revenue)) booked_revenue,
2270 sum(ams_actmetrics_seed_pvt.convert_currency(currency_code, invoiced_revenue)) invoiced_revenue
2271 FROM
2272 (SELECT H.object_type, H.object_id, H.header_id,
2273 sum(nvl(H.unit_selling_price * H.ordered_quantity,0)) booked_revenue,
2274 sum(nvl(H.unit_selling_price * abs(H.invoiced_quantity),0)) invoiced_revenue,
2275 H.currency_code
2276 FROM
2277 (SELECT /*+ first_rows */
2278 H.object_type, H.object_id, I.line_id, I.unit_selling_price,
2279 decode(H.flow_status_code, 'BOOKED', H.header_id, NULL) header_id,
2280 decode(H.flow_status_code, 'BOOKED', I.ordered_quantity, 0) ordered_quantity,
2281 I.invoiced_quantity, H.currency_code
2282 FROM oe_order_lines_all I,
2283 (SELECT H.header_id, H.flow_status_code, H.org_id,
2284 H.transactional_curr_code currency_code,
2285 list_parties.object_type,
2286 list_parties.object_id
2287 FROM oe_order_headers_all H, hz_cust_accounts A
2288 , (
2289 -- SELECT DISTINCT LIST OF B2C AND B2B PARTIES
2290 SELECT distinct actlist_source.source_code_for_id,
2291 actlist_source.arc_source_code_for,
2292 decode(src.source_category,
2293 'B2C',ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID,
2294 'B2B',REL.OBJECT_ID,null) PARTY_ID,
2295 actlist_SOURCE.SOURCE_CODE_ID, src.source_category,
2296 actlist_source.object_type, actlist_source.object_id
2297 FROM AMS_LIST_ENTRIES ENTRY,
2298 AMS_LIST_SRC_TYPES SRC,
2299 HZ_RELATIONSHIPS REL,
2300 (select distinct source_code_for_id, arc_source_code_for,
2301 source_code_id, 'ALIST' object_type,
2302 ACTLIST.ACT_LIST_HEADER_ID object_id,
2303 ACTLIST.LIST_HEADER_ID
2304 from ams_source_codes source, AMS_ACT_LISTS ACTLIST,
2305 ams_metrics_all_b b, ams_act_metrics_all a
2306 where a.metric_id = b.metric_id
2307 AND b.metric_id in (336,341,346)
2308 AND b.enabled_flag = G_IS_ENABLED
2309 AND nvl(LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS
2310 AND ACTLIST.LIST_USED_BY = source.arc_source_code_for
2311 AND ACTLIST.LIST_USED_BY_ID = source.source_code_for_id
2312 AND a.arc_act_metric_used_by = 'ALIST'
2313 AND a.act_metric_used_by_id = ACTLIST.ACT_LIST_HEADER_ID
2314 AND actlist.list_act_type = 'LIST'
2315 ) actlist_source
2316 WHERE ENTRY.LIST_HEADER_ID = actlist_source.LIST_HEADER_ID
2317 AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE =
2318 SRC.SOURCE_TYPE_CODE
2319 AND SRC.LIST_SOURCE_TYPE = 'TARGET'
2320 AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
2321 AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
2322 AND REL.DIRECTIONAL_FLAG(+) = 'F'
2323 ) LIST_PARTIES
2324 WHERE H.booked_flag = 'Y'
2325 AND H.booked_date IS NOT NULL
2326 AND H.marketing_source_code_id = LIST_PARTIES.source_code_id
2327 AND H.sold_to_org_id = A.cust_account_id
2328 AND A.party_id = list_parties.party_id
2329 ) H
2330 WHERE H.header_id = I.header_id(+)
2331 AND EXISTS (
2332 SELECT 1 FROM dual WHERE H.flow_status_code = 'BOOKED'
2333 UNION ALL
2334 SELECT 1
2335 FROM OE_SYSTEM_PARAMETERS_ALL ospa, MTL_SYSTEM_ITEMS_B item
2336 WHERE H.org_id = ospa.org_id
2337 AND I.inventory_item_id = item.inventory_item_id
2338 AND nvl(I.ship_from_org_id, ospa.master_organization_id) = item.organization_id
2339 and rownum = 1
2340 )
2341 ) H
2342 GROUP BY H.object_type, H.object_id, H.header_id,H.currency_code
2343 ) H
2344 GROUP BY object_type, object_id) T,
2345 ams_act_metrics_all AL, ams_metrics_all_b ALB
2346 WHERE AL.ARC_ACT_METRIC_USED_BY = T.object_type(+)
2347 AND AL.act_metric_used_by_id = T.object_id(+)
2348 AND ALB.metric_id IN (336,341,346)
2352 )
2349 AND AL.metric_id = ALB.metric_id
2350 AND ALB.enabled_flag = G_IS_ENABLED
2351 AND nvl(LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS
2353 WHERE NVL(actual_value, 0) <> NVL(func_actual_value, -1);
2354
2355 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
2356 l_activity_metric_id_table.DELETE;
2357 l_actual_values_table.DELETE;
2358
2359 END IF;
2360
2361 END;
2362
2363 -- NAME
2364 -- Calculate_Seeded_List_Metrics
2365 --
2366 -- PURPOSE
2367 -- Bulk collect the list data from sources comparing against the list
2368 -- entries.
2369 --
2370 -- HISTORY
2371 -- 05-Aug-2003 sunkumar created.
2372 -- 17-Dec-2003 choang Fixed SQL for contact group, control group and responses
2373 -- 02-Mar-2004 sunkumar code cleanup
2374 -- 03-Mar-2004 choang Re-wrote all queries to take org contacts into consideration
2375 -- 04-Mar-2004 choang Removed org contact validation for control and contact
2376 -- queries; validation is only required for the entries
2377 -- in the list to exist in the target group.
2378 -- 05-Jan-2005 dmvincen Separated the queries into procedures for better
2379 -- management.
2380 --
2381
2382 PROCEDURE Calculate_Seeded_List_Metrics(
2383 p_arc_act_metric_used_by VARCHAR2 := NULL,
2384 p_act_metric_used_by_id NUMBER := NULL)
2385 IS
2386 l_actual_values_table num_table_type;
2387 l_activity_metric_id_table num_table_type;
2388 BEGIN
2389 IF AMS_DEBUG_HIGH_ON THEN
2390 Write_Log('Calculate_Seeded_List_Metrics','BEGIN');
2391 END IF;
2392 -- Get all object data to update.
2393 IF p_arc_act_metric_used_by IS NULL THEN
2394
2395 Calculate_List_Target;
2396
2397 Calculate_List_Resp_Lead_Opp;
2398
2399 Calculate_List_Orders;
2400
2401 END IF;
2402
2403 IF AMS_DEBUG_HIGH_ON THEN
2404 Write_Log('Calculate_Seeded_List_Metrics','END');
2405 END IF;
2406 END Calculate_Seeded_List_Metrics;
2407
2408 -- NAME
2409 -- Calculate_Inferred_Responses
2410 --
2411 -- PURPOSE
2412 -- Calculate inferred Responses for a single object.
2413 --
2414 -- HISTORY
2415 -- 11-Aug-2005 dmvincen created.
2416 -- 19-Jan-2006 dmvincen Fixed actmet and metric join, lost in 120.13
2417 --
2418 PROCEDURE Calculate_Inferred_Responses(
2419 p_arc_act_metric_used_by VARCHAR2,
2420 p_act_metric_used_by_id NUMBER)
2421 is
2422 l_actual_values_table num_table_type;
2423 l_activity_metric_id_table num_table_type;
2424 cursor c_has_inferred_enabled(object_type varchar2, object_id number) IS
2425 SELECT count(1)
2426 FROM ams_metrics_all_b b, ams_act_metrics_all a
2427 WHERE a.metric_id IN (361,362)
2428 AND enabled_flag = G_IS_ENABLED
2429 and a.metric_id = b.metric_id
2430 and a.arc_act_metric_used_by = object_type
2431 and a.act_metric_used_by_id = object_id;
2432
2433 l_has_enabled NUMBER;
2434 l_today date := sysdate;
2435 l_inferred_period number := fnd_profile.value('AMS_METR_INFERRED_PERIOD');
2436 begin
2437 open c_has_inferred_enabled(p_arc_act_metric_used_by, p_act_metric_used_by_id);
2438 fetch c_has_inferred_enabled into l_has_enabled;
2439 close c_has_inferred_enabled;
2440
2441 if l_has_enabled > 0 then
2442 SELECT NVL(actual_value, 0), activity_metric_id
2443 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
2444 from (select actual_value, actmet.activity_metric_id, func_actual_value
2445 FROM (
2446 select count(1) actual_value, ACTIVITY_METRIC_ID
2447 from (
2448 SELECT decode(SRC.SOURCE_CATEGORY, 'B2C', ENTRY.PARTY_ID,
2449 'B2B', REL.OBJECT_ID, null) PRIMARY_PARTY_ID,
2450 decode(SRC.SOURCE_CATEGORY, 'B2C', NULL,
2451 'B2B', ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID, null)
2452 CONTACT_REL_PARTY_ID,
2453 CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID,
2454 SRC.SOURCE_CATEGORY, CS.SCHEDULE_ID OBJECT_ID
2455 FROM AMS_CAMPAIGN_SCHEDULES_B CS ,
2456 AMS_LIST_HEADERS_ALL LHA ,
2457 AMS_LIST_ENTRIES ENTRY ,
2458 AMS_ACT_METRICS_ALL AM ,
2459 AMS_METRICS_ALL_B MB,
2460 HZ_RELATIONSHIPS REL,
2461 AMS_LIST_SRC_TYPES SRC
2462 WHERE AM.METRIC_ID = MB.METRIC_ID
2463 AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
2464 AND AM.ACT_METRIC_USED_BY_ID = CS.SCHEDULE_ID
2465 AND MB.ENABLED_FLAG = 'Y'
2466 AND MB.METRIC_ID IN (361,362)
2467 AND am.act_metric_used_by_id = p_act_metric_used_by_id
2468 AND 'Y' = DECODE(MB.METRIC_ID, 362, ENTRY.ENABLED_FLAG,
2469 361, ENTRY.PART_OF_CONTROL_GROUP_FLAG,'N')
2470 AND ENTRY.LIST_HEADER_ID = LHA.LIST_HEADER_ID
2471 AND CS.SCHEDULE_ID = LHA.LIST_USED_BY_ID
2472 AND 'CSCH' = LHA.ARC_LIST_USED_BY
2473 AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE = SRC.SOURCE_TYPE_CODE
2474 AND SRC.LIST_SOURCE_TYPE = 'TARGET'
2475 AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
2476 AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
2477 AND REL.DIRECTIONAL_FLAG(+) = 'F'
2478 ) csch_parties
2479 -- Check each party for a positive response within active period.
2480 where exists (select /*+ use_concat */ 1
2484 and ((source_category = 'B2B'
2481 from jtf_ih_interactions inter, jtf_ih_results_b result
2482 WHERE result.positive_response_flag = 'Y'
2483 AND result.result_id = inter.result_id
2485 and inter.contact_rel_party_id = csch_parties.contact_rel_party_id
2486 and inter.primary_party_id = csch_parties.primary_party_id)
2487 OR
2488 (source_category = 'B2C'
2489 and inter.party_id = csch_parties.primary_party_id))
2490 and inter.creation_date between csch_parties.last_activation_date
2491 and csch_parties.last_activation_date + l_inferred_period
2492 and rownum = 1)
2493 group by ACTIVITY_METRIC_ID
2494 ) resp
2495 , ams_act_metrics_all actmet, ams_metrics_all_b ALB
2496 WHERE ALB.metric_id in (361,362)
2497 AND actmet.metric_id = alb.metric_id
2498 AND ALB.enabled_flag = 'Y'
2499 and actmet.arc_act_metric_used_by = 'CSCH'
2500 and actmet.act_metric_used_by_id = p_act_metric_used_by_id
2501 AND actmet.ACTIVITY_METRIC_ID = resp.ACTIVITY_METRIC_ID(+)
2502 )
2503 where NVL(actual_value, 0) <> NVL(func_actual_value, -1);
2504
2505 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
2506 l_activity_metric_id_table.DELETE;
2507 l_actual_values_table.DELETE;
2508
2509 end if;
2510 end;
2511
2512 -- NAME
2513 -- Calculate_Inferred_Responses
2514 --
2515 -- PURPOSE
2516 -- Calculate inferred responses for a all objects.
2517 --
2518 -- HISTORY
2519 -- 11-Aug-2005 dmvincen created.
2520 --
2521 PROCEDURE Calculate_Inferred_Responses
2522 is
2523 l_actual_values_table num_table_type;
2524 l_activity_metric_id_table num_table_type;
2525 l_today DATE := sysdate;
2526 cursor c_has_inferred_enabled IS
2527 SELECT count(distinct b.metric_id) metric_count,
2528 count(activity_metric_id) activity_count
2529 FROM ams_metrics_all_b b, ams_act_metrics_all a
2530 WHERE b.metric_id IN (361,362)
2531 AND enabled_flag = G_IS_ENABLED
2532 AND a.metric_id = b.metric_id
2533 AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
2534
2535 l_has_enabled NUMBER;
2536 l_activity_count NUMBER;
2537 l_inferred_period number := fnd_profile.value('AMS_METR_INFERRED_PERIOD');
2538 begin
2539 open c_has_inferred_enabled;
2540 fetch c_has_inferred_enabled into l_has_enabled, l_activity_count;
2541 close c_has_inferred_enabled;
2542
2543 IF AMS_DEBUG_HIGH_ON THEN
2544 Write_Log('Calculate_Inferred_Responses','Enabled Count='||l_has_enabled,
2545 'Activity Count='||l_activity_count);
2546 END IF;
2547
2548 if l_has_enabled > 0 then
2549 SELECT NVL(actual_value, 0), activity_metric_id
2550 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
2551 from (select actual_value, actmet.activity_metric_id, func_actual_value
2552 FROM (
2553 select count(1) actual_value, ACTIVITY_METRIC_ID
2554 from (
2555 SELECT decode(SRC.SOURCE_CATEGORY, 'B2C', ENTRY.PARTY_ID,
2556 'B2B', REL.OBJECT_ID, null) PRIMARY_PARTY_ID,
2557 decode(SRC.SOURCE_CATEGORY, 'B2C', NULL,
2558 'B2B', ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID, null)
2559 CONTACT_REL_PARTY_ID,
2560 CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID,
2561 SRC.SOURCE_CATEGORY, CS.SCHEDULE_ID OBJECT_ID
2562 FROM AMS_CAMPAIGN_SCHEDULES_B CS ,
2563 AMS_LIST_HEADERS_ALL LHA ,
2564 AMS_LIST_ENTRIES ENTRY ,
2565 AMS_ACT_METRICS_ALL AM ,
2566 AMS_METRICS_ALL_B MB,
2567 HZ_RELATIONSHIPS REL,
2568 AMS_LIST_SRC_TYPES SRC
2569 WHERE AM.METRIC_ID = MB.METRIC_ID
2570 AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
2571 AND AM.ACT_METRIC_USED_BY_ID = CS.SCHEDULE_ID
2572 AND MB.ENABLED_FLAG = 'Y'
2573 AND MB.METRIC_ID IN (361,362)
2574 AND NVL(AM.LAST_CALCULATED_DATE,l_today ) > l_today - G_CALC_LAG_DAYS
2575 AND 'Y' = DECODE(MB.METRIC_ID, 362, ENTRY.ENABLED_FLAG,
2576 361, ENTRY.PART_OF_CONTROL_GROUP_FLAG,'N')
2577 AND ENTRY.LIST_HEADER_ID = LHA.LIST_HEADER_ID
2578 AND CS.SCHEDULE_ID = LHA.LIST_USED_BY_ID
2579 AND 'CSCH' = LHA.ARC_LIST_USED_BY
2580 AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE = SRC.SOURCE_TYPE_CODE
2581 AND SRC.LIST_SOURCE_TYPE = 'TARGET'
2582 AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
2583 AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
2584 AND REL.DIRECTIONAL_FLAG(+) = 'F'
2585 ) csch_parties
2586 -- Check each party for a positive response within active period.
2587 where exists (select /*+ use_concat */ 1
2588 from jtf_ih_interactions inter, jtf_ih_results_b result
2589 WHERE result.positive_response_flag = 'Y'
2590 AND result.result_id = inter.result_id
2591 and ((source_category = 'B2B'
2592 and inter.contact_rel_party_id = csch_parties.contact_rel_party_id
2593 and inter.primary_party_id = csch_parties.primary_party_id)
2594 OR
2595 (source_category = 'B2C'
2596 and inter.party_id = csch_parties.primary_party_id))
2597 and inter.creation_date between csch_parties.last_activation_date
2598 and csch_parties.last_activation_date + l_inferred_period
2599 and rownum = 1)
2600 group by ACTIVITY_METRIC_ID
2604 AND actmet.metric_id = ALB.metric_id
2601 ) resp
2602 , ams_act_metrics_all actmet, ams_metrics_all_b ALB
2603 WHERE ALB.metric_id in (361,362)
2605 AND ALB.enabled_flag = 'Y'
2606 and l_today - G_CALC_LAG_DAYS < nvl(actmet.last_calculated_date,l_today)
2607 AND actmet.ACTIVITY_METRIC_ID = resp.ACTIVITY_METRIC_ID(+)
2608 )
2609 where NVL(actual_value, 0) <> NVL(func_actual_value, -1);
2610
2611 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
2612 l_activity_metric_id_table.DELETE;
2613 l_actual_values_table.DELETE;
2614
2615 end if;
2616
2617 end;
2618
2619 -- NAME
2620 -- Calculate_Inferred_Leads
2621 --
2622 -- PURPOSE
2623 -- Calculate inferred leads for an object.
2624 --
2625 -- HISTORY
2626 -- 11-Aug-2005 dmvincen created.
2627 --
2628 PROCEDURE Calculate_Inferred_Leads(
2629 p_arc_act_metric_used_by VARCHAR2,
2630 p_act_metric_used_by_id NUMBER)
2631 is
2632 l_actual_values_table num_table_type;
2633 l_activity_metric_id_table num_table_type;
2634 cursor c_has_inferred_enabled(object_type varchar2, object_id number) IS
2635 SELECT count(1)
2636 FROM ams_metrics_all_b b, ams_act_metrics_all a
2637 WHERE a.metric_id IN (371,372)
2638 AND enabled_flag = G_IS_ENABLED
2639 and a.metric_id = b.metric_id
2640 and a.arc_act_metric_used_by = object_type
2641 and a.act_metric_used_by_id = object_id;
2642
2643 l_has_enabled NUMBER;
2644 l_today date := sysdate;
2645 l_inferred_period number := fnd_profile.value('AMS_METR_INFERRED_PERIOD');
2646 begin
2647 open c_has_inferred_enabled(p_arc_act_metric_used_by, p_act_metric_used_by_id);
2648 fetch c_has_inferred_enabled into l_has_enabled;
2649 close c_has_inferred_enabled;
2650
2651 if l_has_enabled > 0 then
2652 SELECT NVL(actual_value, 0), activity_metric_id
2653 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
2654 FROM (select actual_value, am.activity_metric_id, am.func_actual_value
2655 from (
2656 select count(1) actual_value, activity_metric_id
2657 from (
2658 SELECT decode(SRC.SOURCE_CATEGORY, 'B2C', ENTRY.PARTY_ID,
2659 'B2B', REL.OBJECT_ID, null) PRIMARY_PARTY_ID,
2660 decode(SRC.SOURCE_CATEGORY, 'B2C', NULL,
2661 'B2B', ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID, null)
2662 CONTACT_REL_PARTY_ID,
2663 CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID,
2664 SRC.SOURCE_CATEGORY, CS.SCHEDULE_ID OBJECT_ID
2665 FROM AMS_CAMPAIGN_SCHEDULES_B CS ,
2666 AMS_LIST_HEADERS_ALL LHA ,
2667 AMS_LIST_ENTRIES ENTRY ,
2668 AMS_ACT_METRICS_ALL AM ,
2669 AMS_METRICS_ALL_B MB,
2670 HZ_RELATIONSHIPS REL,
2671 AMS_LIST_SRC_TYPES SRC
2672 WHERE AM.METRIC_ID = MB.METRIC_ID
2673 AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
2674 AND AM.ACT_METRIC_USED_BY_ID = CS.SCHEDULE_ID
2675 AND MB.ENABLED_FLAG = 'Y'
2676 AND MB.METRIC_ID IN (371,372)
2677 AND cs.schedule_id = p_act_metric_used_by_id
2678 AND 'Y' = DECODE(MB.METRIC_ID, 372, ENTRY.ENABLED_FLAG,
2679 371, ENTRY.PART_OF_CONTROL_GROUP_FLAG,'N')
2680 AND ENTRY.LIST_HEADER_ID = LHA.LIST_HEADER_ID
2681 AND CS.SCHEDULE_ID = LHA.LIST_USED_BY_ID
2682 AND 'CSCH' = LHA.ARC_LIST_USED_BY
2683 AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE = SRC.SOURCE_TYPE_CODE
2684 AND SRC.LIST_SOURCE_TYPE = 'TARGET'
2685 AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
2686 AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
2687 AND REL.DIRECTIONAL_FLAG(+) = 'F'
2688 ) csch_parties
2689 where exists (select /*+ use_concat */1
2690 from as_sales_leads lead, as_statuses_b lead_status
2691 where lead.status_code = lead_status.status_code
2692 AND lead_status.lead_flag = 'Y'
2693 AND lead_status.enabled_flag = 'Y'
2694 AND NVL(lead.deleted_flag, 'N') <> 'Y'
2695 and ((source_category = 'B2C' AND lead.customer_id = csch_parties.primary_party_id)
2696 OR
2697 (source_category = 'B2B' and lead.customer_id = csch_parties.primary_party_id
2698 and lead.primary_contact_party_id = csch_parties.contact_rel_party_id))
2699 and lead.creation_date between csch_parties.last_activation_date
2700 and csch_parties.last_activation_date + l_inferred_period
2701 AND exists (select 1
2702 from as_sales_lead_lines LL, ams_act_products actprod
2703 where ll.inventory_item_id = actprod.INVENTORY_ITEM_ID
2704 and ll.organization_id = actprod.organization_id
2705 and ll.sales_lead_id = lead.sales_lead_id
2706 and actprod.arc_act_product_used_by = 'CSCH'
2707 and actprod.act_product_used_by_id = csch_parties.object_id
2708 and actprod.level_type_code = 'PRODUCT'
2709 and rownum = 1
2710 )
2711 and rownum = 1
2712 )
2713 group by activity_metric_id) leads,
2714 ams_act_metrics_all am, ams_metrics_all_b mb
2715 WHERE am.metric_id = mb.metric_id
2716 and am.arc_act_metric_used_by = 'CSCH'
2717 and mb.enabled_flag = 'Y'
2718 and mb.metric_id in (371,372)
2719 and am.act_metric_used_by_id = p_act_metric_used_by_id
2720 and leads.activity_metric_id(+) = am.activity_metric_id)
2724 l_activity_metric_id_table.DELETE;
2721 where NVL(actual_value, 0) <> NVL(func_actual_value, -1);
2722
2723 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
2725 l_actual_values_table.DELETE;
2726
2727 end if;
2728 end;
2729
2730 -- NAME
2731 -- Calculate_Inferred_Leads
2732 --
2733 -- PURPOSE
2734 -- Calculate inferred leads for a all objects.
2735 --
2736 -- HISTORY
2737 -- 11-Aug-2005 dmvincen created.
2738 --
2739 PROCEDURE Calculate_Inferred_Leads
2740 is
2741 l_actual_values_table num_table_type;
2742 l_activity_metric_id_table num_table_type;
2743 l_today DATE := sysdate;
2744 cursor c_has_inferred_enabled IS
2745 SELECT count(distinct b.metric_id) metric_count,
2746 count(activity_metric_id) activity_count
2747 FROM ams_metrics_all_b b, ams_act_metrics_all a
2748 WHERE b.metric_id IN (371,372)
2749 AND enabled_flag = G_IS_ENABLED
2750 AND a.metric_id = b.metric_id
2751 AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
2752
2753 l_has_enabled NUMBER;
2754 l_activity_count NUMBER;
2755 l_inferred_period number := fnd_profile.value('AMS_METR_INFERRED_PERIOD');
2756 begin
2757 open c_has_inferred_enabled;
2758 fetch c_has_inferred_enabled into l_has_enabled,l_activity_count;
2759 close c_has_inferred_enabled;
2760
2761 IF AMS_DEBUG_HIGH_ON THEN
2762 Write_Log('Calculate_Inferred_Leads','Enabled Count='||l_has_enabled,
2763 'Activity Count='||l_activity_count);
2764 END IF;
2765
2766 if l_has_enabled > 0 then
2767 SELECT NVL(actual_value, 0), activity_metric_id
2768 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
2769 FROM (select actual_value, am.activity_metric_id, am.func_actual_value
2770 from (
2771 select count(1) actual_value, activity_metric_id
2772 from (
2773 SELECT decode(SRC.SOURCE_CATEGORY, 'B2C', ENTRY.PARTY_ID,
2774 'B2B', REL.OBJECT_ID, null) PRIMARY_PARTY_ID,
2775 decode(SRC.SOURCE_CATEGORY, 'B2C', NULL,
2776 'B2B', ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID, null)
2777 CONTACT_REL_PARTY_ID,
2778 CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID,
2779 SRC.SOURCE_CATEGORY, CS.SCHEDULE_ID OBJECT_ID
2780 FROM AMS_CAMPAIGN_SCHEDULES_B CS ,
2781 AMS_LIST_HEADERS_ALL LHA ,
2782 AMS_LIST_ENTRIES ENTRY ,
2783 AMS_ACT_METRICS_ALL AM ,
2784 AMS_METRICS_ALL_B MB,
2785 HZ_RELATIONSHIPS REL,
2786 AMS_LIST_SRC_TYPES SRC
2787 WHERE AM.METRIC_ID = MB.METRIC_ID
2788 AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
2789 AND AM.ACT_METRIC_USED_BY_ID = CS.SCHEDULE_ID
2790 AND MB.ENABLED_FLAG = 'Y'
2791 AND MB.METRIC_ID IN (371,372)
2792 AND nvl(am.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
2793 AND 'Y' = DECODE(MB.METRIC_ID, 372, ENTRY.ENABLED_FLAG,
2794 371, ENTRY.PART_OF_CONTROL_GROUP_FLAG,'N')
2795 AND ENTRY.LIST_HEADER_ID = LHA.LIST_HEADER_ID
2796 AND CS.SCHEDULE_ID = LHA.LIST_USED_BY_ID
2797 AND 'CSCH' = LHA.ARC_LIST_USED_BY
2798 AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE = SRC.SOURCE_TYPE_CODE
2799 AND SRC.LIST_SOURCE_TYPE = 'TARGET'
2800 AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
2801 AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
2802 AND REL.DIRECTIONAL_FLAG(+) = 'F'
2803 ) csch_parties
2804 where exists (select /*+ use_concat */ 1
2805 from as_sales_leads lead, as_statuses_b lead_status
2806 where lead.status_code = lead_status.status_code
2807 AND lead_status.lead_flag = 'Y'
2808 AND lead_status.enabled_flag = 'Y'
2809 AND NVL(lead.deleted_flag, 'N') <> 'Y'
2810 and ((source_category = 'B2C' AND lead.customer_id = csch_parties.primary_party_id)
2811 OR
2812 (source_category = 'B2B' and lead.customer_id = csch_parties.primary_party_id
2813 and lead.primary_contact_party_id = csch_parties.contact_rel_party_id))
2814 and lead.creation_date between csch_parties.last_activation_date
2815 and csch_parties.last_activation_date + l_inferred_period
2816 AND exists (select 1
2817 from as_sales_lead_lines LL, ams_act_products actprod
2818 where ll.inventory_item_id = actprod.INVENTORY_ITEM_ID
2819 and ll.organization_id = actprod.organization_id
2820 and ll.sales_lead_id = lead.sales_lead_id
2821 and actprod.arc_act_product_used_by = 'CSCH'
2822 and actprod.act_product_used_by_id = csch_parties.object_id
2823 and actprod.level_type_code = 'PRODUCT'
2824 and rownum = 1
2825 )
2826 and rownum = 1
2827 )
2828 group by activity_metric_id) leads,
2829 ams_act_metrics_all am, ams_metrics_all_b mb
2830 WHERE am.metric_id = mb.metric_id
2831 and am.arc_act_metric_used_by = 'CSCH'
2832 and mb.enabled_flag = 'Y'
2833 and mb.metric_id in (371,372)
2834 AND nvl(am.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
2835 and leads.activity_metric_id(+) = am.activity_metric_id)
2836 where NVL(actual_value, 0) <> NVL(func_actual_value, -1);
2837
2838 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
2839 l_activity_metric_id_table.DELETE;
2840 l_actual_values_table.DELETE;
2841
2842 end if;
2843
2847 -- Calculate_Inferred_Orders
2844 end;
2845
2846 -- NAME
2848 --
2849 -- PURPOSE
2850 -- Calculate inferred orders for an object.
2851 --
2852 -- HISTORY
2853 -- 11-Aug-2005 dmvincen created.
2854 --
2855 PROCEDURE Calculate_Inferred_Orders(
2856 p_arc_act_metric_used_by VARCHAR2,
2857 p_act_metric_used_by_id NUMBER)
2858 is
2859 l_actual_values_table num_table_type;
2860 l_activity_metric_id_table num_table_type;
2861 cursor c_has_inferred_enabled(object_type varchar2, object_id number) IS
2862 SELECT count(1)
2863 FROM ams_metrics_all_b b, ams_act_metrics_all a
2864 WHERE a.metric_id IN (381,382,391,392)
2865 AND enabled_flag = G_IS_ENABLED
2866 and a.metric_id = b.metric_id
2867 and a.arc_act_metric_used_by = object_type
2868 and a.act_metric_used_by_id = object_id;
2869
2870 l_has_enabled NUMBER;
2871 l_today date := sysdate;
2872 l_inferred_period number := fnd_profile.value('AMS_METR_INFERRED_PERIOD');
2873 begin
2874 open c_has_inferred_enabled(p_arc_act_metric_used_by, p_act_metric_used_by_id);
2875 fetch c_has_inferred_enabled into l_has_enabled;
2876 close c_has_inferred_enabled;
2877
2878 if l_has_enabled > 0 then
2879 SELECT NVL(actual_value, 0), activity_metric_id
2880 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
2881 FROM (
2882 SELECT
2883 decode(al.metric_id
2884 , 381, order_count, 382, order_count
2885 , 391, booked_revenue, 392, booked_revenue
2886 , 0 ) actual_value, al.activity_metric_id, func_actual_value,
2887 al.metric_id, al.act_metric_used_by_id
2888 FROM
2889 (SELECT
2890 ACTIVITY_METRIC_ID,
2891 count(distinct nvl(contact_rel_party_id, primary_party_id)) targets,
2892 count(DISTINCT header_id) order_count,
2893 sum(ams_actmetrics_seed_pvt.convert_currency(currency_code, booked_revenue)) booked_revenue
2894 FROM (
2895 SELECT /*+ ordered */ H.header_id, H.transactional_curr_code currency_code,
2896 sum(nvl(I.ordered_quantity * I.unit_selling_price,0)) booked_revenue,
2897 csch_parties.ACTIVITY_METRIC_ID,
2898 primary_party_id, contact_rel_party_id
2899 from (
2900 SELECT decode(SRC.SOURCE_CATEGORY, 'B2C', ENTRY.PARTY_ID,
2901 'B2B', REL.OBJECT_ID, null) PRIMARY_PARTY_ID,
2902 decode(SRC.SOURCE_CATEGORY, 'B2C', NULL,
2903 'B2B', ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID, null)
2904 CONTACT_REL_PARTY_ID,
2905 CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID,
2906 SRC.SOURCE_CATEGORY, CS.SCHEDULE_ID OBJECT_ID
2907 FROM AMS_CAMPAIGN_SCHEDULES_B CS ,
2908 AMS_LIST_HEADERS_ALL LHA ,
2909 AMS_LIST_ENTRIES ENTRY ,
2910 AMS_ACT_METRICS_ALL AM ,
2911 AMS_METRICS_ALL_B MB,
2912 HZ_RELATIONSHIPS REL,
2913 AMS_LIST_SRC_TYPES SRC
2914 WHERE AM.METRIC_ID = MB.METRIC_ID
2915 AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
2916 AND AM.ACT_METRIC_USED_BY_ID = CS.SCHEDULE_ID
2917 AND MB.ENABLED_FLAG = 'Y'
2918 AND MB.METRIC_ID IN (391,381,392,382)
2919 and am.act_metric_used_by_id = p_act_metric_used_by_id
2920 AND 'Y' = DECODE(MB.METRIC_ID,
2921 392, ENTRY.ENABLED_FLAG,
2922 382, ENTRY.ENABLED_FLAG,
2923 391, ENTRY.PART_OF_CONTROL_GROUP_FLAG,
2924 381, ENTRY.PART_OF_CONTROL_GROUP_FLAG,'N')
2925 AND ENTRY.LIST_HEADER_ID = LHA.LIST_HEADER_ID
2926 AND CS.SCHEDULE_ID = LHA.LIST_USED_BY_ID
2927 AND 'CSCH' = LHA.ARC_LIST_USED_BY
2928 AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE = SRC.SOURCE_TYPE_CODE
2929 AND SRC.LIST_SOURCE_TYPE = 'TARGET'
2930 AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
2931 AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
2932 AND REL.DIRECTIONAL_FLAG(+) = 'F'
2933 ) csch_parties,
2934 hz_cust_accounts account,
2935 oe_order_headers_all H,
2936 oe_order_lines_all I,
2937 ams_act_products aprod
2938 where csch_parties.primary_party_id = account.party_id
2939 AND H.sold_to_org_id = account.cust_account_id
2940 AND ((source_category = 'B2B'
2941 AND exists (select 1 from hz_cust_account_roles roles
2942 where H.sold_to_contact_id = roles.cust_account_role_id
2943 AND roles.cust_account_id = account.cust_account_id
2944 AND roles.party_id = csch_parties.contact_rel_party_id
2945 AND rownum = 1))
2946 OR
2947 (source_category = 'B2C'))
2948 and H.booked_flag = 'Y'
2949 AND H.booked_date IS NOT NULL
2950 AND H.flow_status_code = 'BOOKED'
2951 and aprod.arc_act_product_used_by = 'CSCH'
2952 and aprod.act_product_used_by_id = csch_parties.object_id
2953 -- Commenting out this line since sold_from_org_id doesn't get
2954 -- populated. Bug#5139222
2955 -- and aprod.organization_id = i.sold_from_org_id
2956 and aprod.INVENTORY_ITEM_ID = i.ordered_item_id
2957 and aprod.level_type_code = 'PRODUCT'
2958 AND H.header_id = I.header_id
2959 -- AND H.creation_date
2960 AND H.ordered_date
2961 between csch_parties.last_activation_date
2962 and csch_parties.last_activation_date + l_inferred_period
2963 group by H.header_id, H.transactional_curr_code ,
2967 GROUP BY csch_orders.activity_metric_id
2964 csch_parties.object_id, csch_parties.activity_metric_id,
2965 primary_party_id, contact_rel_party_id
2966 ) csch_orders
2968 ) T,
2969 ams_act_metrics_all AL, ams_metrics_all_b ALB
2970 WHERE ALB.metric_id IN (391,381,392,382)
2971 AND AL.metric_id = ALB.metric_id
2972 AND ALB.enabled_flag = 'Y'
2973 AND AL.activity_metric_id = t.activity_metric_id(+)
2974 AND AL.arc_act_metric_used_by = 'CSCH'
2975 and al.act_metric_used_by_id = p_act_metric_used_by_id
2976 )
2977 WHERE NVL(actual_value, 0) <> NVL(func_actual_value, -1);
2978
2979 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
2980 l_activity_metric_id_table.DELETE;
2981 l_actual_values_table.DELETE;
2982
2983 end if;
2984
2985 end;
2986
2987 -- NAME
2988 -- Calculate_Inferred_Orders
2989 --
2990 -- PURPOSE
2991 -- Calculate inferred orders for a all objects.
2992 --
2993 -- HISTORY
2994 -- 11-Aug-2005 dmvincen created.
2995 --
2996 PROCEDURE Calculate_Inferred_Orders
2997 is
2998 l_actual_values_table num_table_type;
2999 l_activity_metric_id_table num_table_type;
3000 l_today DATE := sysdate;
3001 cursor c_has_inferred_enabled IS
3002 SELECT count(distinct b.metric_id) metric_count,
3003 count(activity_metric_id) activity_count
3004 FROM ams_metrics_all_b b, ams_act_metrics_all a
3005 WHERE b.metric_id IN (381,382,391,392)
3006 AND enabled_flag = G_IS_ENABLED
3007 AND a.metric_id = b.metric_id
3008 AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
3009
3010 l_has_enabled NUMBER;
3011 l_activity_count NUMBER;
3012 l_inferred_period number := fnd_profile.value('AMS_METR_INFERRED_PERIOD');
3013 begin
3014 open c_has_inferred_enabled;
3015 fetch c_has_inferred_enabled into l_has_enabled,l_activity_count;
3016 close c_has_inferred_enabled;
3017
3018 IF AMS_DEBUG_HIGH_ON THEN
3019 Write_Log('Calculate_Inferred_Orders','Enabled Count='||l_has_enabled,
3020 'Activity Count='||l_activity_count);
3021 END IF;
3022
3023 if l_has_enabled > 0 then
3024 SELECT NVL(actual_value, 0), activity_metric_id
3025 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
3026 FROM (
3027 SELECT
3028 decode(al.metric_id
3029 , 381, order_count, 382, order_count
3030 , 391, booked_revenue, 392, booked_revenue
3031 , 0 ) actual_value, al.activity_metric_id, func_actual_value,
3032 al.metric_id, al.act_metric_used_by_id
3033 FROM
3034 (SELECT
3035 ACTIVITY_METRIC_ID,
3036 count(distinct nvl(contact_rel_party_id, primary_party_id)) targets,
3037 count(DISTINCT header_id) order_count,
3038 sum(ams_actmetrics_seed_pvt.convert_currency(currency_code, booked_revenue)) booked_revenue
3039 FROM (
3040 SELECT /*+ ordered */ H.header_id, H.transactional_curr_code currency_code,
3041 sum(nvl(I.ordered_quantity * I.unit_selling_price,0)) booked_revenue,
3042 csch_parties.ACTIVITY_METRIC_ID,
3043 primary_party_id, contact_rel_party_id
3044 from (
3045 SELECT decode(SRC.SOURCE_CATEGORY, 'B2C', ENTRY.PARTY_ID,
3046 'B2B', REL.OBJECT_ID, null) PRIMARY_PARTY_ID,
3047 decode(SRC.SOURCE_CATEGORY, 'B2C', NULL,
3048 'B2B', ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID, null)
3049 CONTACT_REL_PARTY_ID,
3050 CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID,
3051 SRC.SOURCE_CATEGORY, CS.SCHEDULE_ID OBJECT_ID
3052 FROM AMS_METRICS_ALL_B MB,
3053 AMS_ACT_METRICS_ALL AM ,
3054 AMS_CAMPAIGN_SCHEDULES_B CS ,
3055 AMS_LIST_HEADERS_ALL LHA ,
3056 AMS_LIST_ENTRIES ENTRY ,
3057 HZ_RELATIONSHIPS REL,
3058 AMS_LIST_SRC_TYPES SRC
3059 WHERE AM.METRIC_ID = MB.METRIC_ID
3060 AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
3061 AND AM.ACT_METRIC_USED_BY_ID = CS.SCHEDULE_ID
3062 AND MB.ENABLED_FLAG = 'Y'
3063 AND MB.METRIC_ID IN (391,381,392,382)
3064 AND NVL(AM.LAST_CALCULATED_DATE,l_today ) > l_today - G_CALC_LAG_DAYS
3065 AND 'Y' = DECODE(MB.METRIC_ID,
3066 392, ENTRY.ENABLED_FLAG,
3067 382, ENTRY.ENABLED_FLAG,
3068 391, ENTRY.PART_OF_CONTROL_GROUP_FLAG,
3069 381, ENTRY.PART_OF_CONTROL_GROUP_FLAG,'N')
3070 AND ENTRY.LIST_HEADER_ID = LHA.LIST_HEADER_ID
3071 AND CS.SCHEDULE_ID = LHA.LIST_USED_BY_ID
3072 AND 'CSCH' = LHA.ARC_LIST_USED_BY
3073 AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE = SRC.SOURCE_TYPE_CODE
3074 AND SRC.LIST_SOURCE_TYPE = 'TARGET'
3075 AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
3076 AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
3077 AND REL.DIRECTIONAL_FLAG(+) = 'F'
3078 ) csch_parties,
3079 hz_cust_accounts account,
3080 oe_order_headers_all H,
3081 oe_order_lines_all I,
3082 ams_act_products aprod
3083 where csch_parties.primary_party_id = account.party_id
3084 AND H.sold_to_org_id = account.cust_account_id
3085 AND ((source_category = 'B2B'
3086 AND exists (select 1 from hz_cust_account_roles roles
3090 AND rownum = 1))
3087 where H.sold_to_contact_id = roles.cust_account_role_id
3088 AND roles.cust_account_id = account.cust_account_id
3089 AND roles.party_id = csch_parties.contact_rel_party_id
3091 OR
3092 (source_category = 'B2C'))
3093 and H.booked_flag = 'Y'
3094 AND H.booked_date IS NOT NULL
3095 AND H.flow_status_code = 'BOOKED'
3096 and aprod.arc_act_product_used_by = 'CSCH'
3097 and aprod.act_product_used_by_id = csch_parties.object_id
3098 -- Commenting out this line since sold_from_org_id doesn't get
3099 -- populated. Bug#5139222
3100 -- and aprod.organization_id = i.sold_from_org_id
3101 and aprod.INVENTORY_ITEM_ID = i.ordered_item_id
3102 and aprod.level_type_code = 'PRODUCT'
3103 AND H.header_id = I.header_id
3104 -- AND H.creation_date
3105 AND H.ordered_date
3106 between csch_parties.last_activation_date
3107 and csch_parties.last_activation_date + l_inferred_period
3108 group by H.header_id, H.transactional_curr_code ,
3109 csch_parties.object_id, csch_parties.activity_metric_id,
3110 primary_party_id, contact_rel_party_id
3111 ) csch_orders
3112 GROUP BY csch_orders.activity_metric_id
3113 ) T,
3114 ams_act_metrics_all AL, ams_metrics_all_b ALB
3115 WHERE ALB.metric_id IN (391,381,392,382)
3116 AND AL.metric_id = ALB.metric_id
3117 AND ALB.enabled_flag = 'Y'
3118 and l_today - G_CALC_LAG_DAYS < nvl(AL.last_calculated_date,l_today)
3119 AND AL.activity_metric_id = t.activity_metric_id(+)
3120 )
3121 WHERE NVL(actual_value, 0) <> NVL(func_actual_value, -1);
3122
3123 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
3124 l_activity_metric_id_table.DELETE;
3125 l_actual_values_table.DELETE;
3126
3127 end if;
3128
3129 end;
3130
3131 -- NAME
3132 -- Calculate_Inferred_Metrics
3133 --
3134 -- PURPOSE
3135 -- Calculate inferred activity for an object.
3136 --
3137 -- HISTORY
3138 -- 11-Aug-2005 dmvincen created.
3139 --
3140 PROCEDURE Calculate_Inferred_Metrics(
3141 p_arc_act_metric_used_by VARCHAR2,
3142 p_act_metric_used_by_id NUMBER)
3143 is
3144 begin
3145 IF AMS_DEBUG_HIGH_ON THEN
3146 Write_Log('Calculate_Inferred_Metrics',
3147 'object type='||p_arc_act_metric_used_by,
3148 'object id='||p_act_metric_used_by_id);
3149 END IF;
3150 Calculate_Inferred_Responses(p_arc_act_metric_used_by, p_act_metric_used_by_id);
3151
3152 Calculate_Inferred_Leads(p_arc_act_metric_used_by, p_act_metric_used_by_id);
3153
3154 Calculate_Inferred_Orders(p_arc_act_metric_used_by, p_act_metric_used_by_id);
3155 IF AMS_DEBUG_HIGH_ON THEN
3156 Write_Log('Calculate_Inferred_Metrics','END');
3157 END IF;
3158
3159 end;
3160 --------------------------------------------------------------------
3161 -- Following API are added by rrajesh on 06/06 for fixing Performance issues; each SQL has a buffer get < 1 M
3162 --------------------------------------------------------------------
3163 -- NAME
3164 -- Calc_Inf_Resp_new
3165 --
3166 -- PURPOSE
3167 -- Calculate inferred responses for all objects.
3168 -- Storing the targted parties in global temp table to improve performance
3169 -- HISTORY
3170 -- 15-Jun-2006 rrajesh created.
3171 --
3172 --------------------------------------------------------------------
3173 PROCEDURE Calc_Inf_Resp_new
3174 is
3175 l_actual_values_table num_table_type;
3176 l_activity_metric_id_table num_table_type;
3177 l_today DATE := sysdate;
3178 cursor c_has_inferred_enabled IS
3179 SELECT count(distinct b.metric_id) metric_count,
3180 count(activity_metric_id) activity_count
3181 FROM ams_metrics_all_b b, ams_act_metrics_all a
3182 WHERE b.metric_id IN (361,362)
3183 AND enabled_flag = G_IS_ENABLED
3184 AND a.metric_id = b.metric_id
3185 AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
3186
3187 l_has_enabled NUMBER;
3188 l_activity_count NUMBER;
3189 l_inferred_period number := fnd_profile.value('AMS_METR_INFERRED_PERIOD');
3190
3191
3192
3193 begin
3194 open c_has_inferred_enabled;
3195 fetch c_has_inferred_enabled into l_has_enabled, l_activity_count;
3196 close c_has_inferred_enabled;
3197
3198 IF AMS_DEBUG_HIGH_ON THEN
3199 Write_Log('Calculate_Inferred_Responses','Enabled Count='||l_has_enabled,
3200 'Activity Count='||l_activity_count);
3201 END IF;
3202
3203
3204 IF l_has_enabled > 0 THEN
3205
3206 INSERT INTO AMS_INFMET_RESP_GT(PRIMARY_PARTY_ID, CONTACT_REL_PARTY_ID,
3207 LAST_ACTIVATION_DATE, ACTIVITY_METRIC_ID, SOURCE_CATEGORY)
3208 SELECT decode(SRC.SOURCE_CATEGORY, 'B2C', ENTRY.PARTY_ID,
3209 'B2B', REL.OBJECT_ID, null) PRIMARY_PARTY_ID,
3210 decode(SRC.SOURCE_CATEGORY,'B2C', NULL,
3211 'B2B', ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID, null)
3212 CONTACT_REL_PARTY_ID,
3213 CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID,
3214 SRC.SOURCE_CATEGORY
3215 FROM AMS_CAMPAIGN_SCHEDULES_B CS ,
3216 AMS_LIST_HEADERS_ALL LHA , AMS_LIST_ENTRIES ENTRY ,
3220 AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
3217 AMS_ACT_METRICS_ALL AM , AMS_METRICS_ALL_B MB, HZ_RELATIONSHIPS REL,
3218 AMS_LIST_SRC_TYPES SRC
3219 WHERE AM.METRIC_ID = MB.METRIC_ID
3221 AND AM.ACT_METRIC_USED_BY_ID = CS.SCHEDULE_ID
3222 AND MB.ENABLED_FLAG = 'Y'
3223 AND MB.METRIC_ID IN (361,362)
3224 AND NVL(AM.LAST_CALCULATED_DATE,l_today ) > l_today - G_CALC_LAG_DAYS
3225 -- AND NVL(AM.LAST_CALCULATED_DATE,sysdate) > sysdate - 90
3226 AND 'Y' = DECODE(MB.METRIC_ID, 362, ENTRY.ENABLED_FLAG,
3227 361, ENTRY.PART_OF_CONTROL_GROUP_FLAG,'N')
3228 AND ENTRY.LIST_HEADER_ID = LHA.LIST_HEADER_ID
3229 AND CS.SCHEDULE_ID = LHA.LIST_USED_BY_ID
3230 AND 'CSCH' = LHA.ARC_LIST_USED_BY
3231 AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE = SRC.SOURCE_TYPE_CODE
3232 AND SRC.LIST_SOURCE_TYPE = 'TARGET'
3233 AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
3234 AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
3235 AND REL.DIRECTIONAL_FLAG(+) ='F';
3236
3237 SELECT NVL(actual_value, 0), activity_metric_id
3238 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
3239 from (select actual_value, actmet.activity_metric_id, func_actual_value
3240 FROM (
3241 select count(1) actual_value, ACTIVITY_METRIC_ID
3242 from AMS_INFMET_RESP_GT csch_parties
3243 where exists (select /*+ use_concat */ 1
3244 from jtf_ih_interactions inter, jtf_ih_results_b result
3245 WHERE result.positive_response_flag = 'Y'
3246 AND result.result_id = inter.result_id
3247 and ((source_category = 'B2B'
3248 and inter.contact_rel_party_id = csch_parties.contact_rel_party_id
3249 and inter.primary_party_id = csch_parties.primary_party_id)
3250 OR
3251 (source_category = 'B2C'
3252 and inter.party_id = csch_parties.primary_party_id))
3253 and inter.creation_date between csch_parties.last_activation_date
3254 and csch_parties.last_activation_date + l_inferred_period
3255 -- and csch_parties.last_activation_date + 90
3256 and rownum = 1)
3257 group by ACTIVITY_METRIC_ID
3258 ) resp
3259 , ams_act_metrics_all actmet, ams_metrics_all_b ALB
3260 WHERE ALB.metric_id in (361,362)
3261 AND actmet.metric_id = ALB.metric_id
3262 AND ALB.enabled_flag = 'Y'
3263 and l_today - G_CALC_LAG_DAYS < nvl(actmet.last_calculated_date,l_today)
3264 -- and sysdate - 90 < nvl(actmet.last_calculated_date,sysdate)
3265 AND actmet.ACTIVITY_METRIC_ID = resp.ACTIVITY_METRIC_ID(+)
3266 )
3267 where NVL(actual_value, 0) <> NVL(func_actual_value, -1);
3268
3269 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
3270 l_activity_metric_id_table.DELETE;
3271 l_actual_values_table.DELETE;
3272
3273 end if;
3274
3275 end;
3276 ------------------------------------------------------------------------------
3277 -- NAME
3278 -- Calc_Inf_Order_new
3279 --
3280 -- PURPOSE
3281 -- Calculate inferred orders for all objects.
3282 -- Storing the targted parties in global temp table to improve performance
3283 -- HISTORY
3284 -- 16-Jun-2006 rrajesh created.
3285 --
3286 ------------------------------------------------------------------------------
3287 PROCEDURE Calc_Inf_Order_new
3288 is
3289 l_actual_values_table num_table_type;
3290 l_activity_metric_id_table num_table_type;
3291 l_today DATE := sysdate;
3292 cursor c_has_inferred_enabled IS
3293 SELECT count(distinct b.metric_id) metric_count,
3294 count(activity_metric_id) activity_count
3295 FROM ams_metrics_all_b b, ams_act_metrics_all a
3296 WHERE b.metric_id IN (381,382,391,392)
3297 AND enabled_flag = G_IS_ENABLED
3298 AND a.metric_id = b.metric_id
3299 AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
3300
3301 l_has_enabled NUMBER;
3302 l_activity_count NUMBER;
3303 l_inferred_period number := fnd_profile.value('AMS_METR_INFERRED_PERIOD');
3304 l_sql_stmt VARCHAR2(2000);
3305
3306 begin
3307 open c_has_inferred_enabled;
3308 fetch c_has_inferred_enabled into l_has_enabled,l_activity_count;
3309 close c_has_inferred_enabled;
3310
3311 IF AMS_DEBUG_HIGH_ON THEN
3312 Write_Log('Calculate_Inferred_Orders','Enabled Count='||l_has_enabled,
3313 'Activity Count='||l_activity_count);
3314 END IF;
3315
3316 if l_has_enabled > 0 then
3317 /* INSERT INTO AMS_INFMET_ORDER_GT(PRIMARY_PARTY_ID, CONTACT_REL_PARTY_ID,
3318 LAST_ACTIVATION_DATE, ACTIVITY_METRIC_ID, SOURCE_CATEGORY, OBJECT_ID)
3319 SELECT decode(SRC.SOURCE_CATEGORY, 'B2C', ENTRY.PARTY_ID,
3320 'B2B', REL.OBJECT_ID, null) PRIMARY_PARTY_ID,
3321 decode(SRC.SOURCE_CATEGORY, 'B2C', NULL,
3322 'B2B', ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID, null) CONTACT_REL_PARTY_ID,
3323 CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID, SRC.SOURCE_CATEGORY, CS.SCHEDULE_ID OBJECT_ID
3324 FROM AMS_METRICS_ALL_B MB,
3325 AMS_ACT_METRICS_ALL AM ,
3326 AMS_CAMPAIGN_SCHEDULES_B CS ,
3327 AMS_LIST_HEADERS_ALL LHA ,
3328 AMS_LIST_ENTRIES ENTRY ,
3329 HZ_RELATIONSHIPS REL,
3330 AMS_LIST_SRC_TYPES SRC
3331 WHERE AM.METRIC_ID = MB.METRIC_ID
3335 AND MB.METRIC_ID IN (391,381,392,382)
3332 AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
3333 AND AM.ACT_METRIC_USED_BY_ID = CS.SCHEDULE_ID
3334 AND MB.ENABLED_FLAG = 'Y'
3336 AND NVL(AM.LAST_CALCULATED_DATE,l_today ) > l_today - G_CALC_LAG_DAYS
3337 -- AND NVL(AM.LAST_CALCULATED_DATE,sysdate ) > sysdate - 90
3338 AND 'Y' = DECODE(MB.METRIC_ID,
3339 392, ENTRY.ENABLED_FLAG,
3340 382, ENTRY.ENABLED_FLAG,
3341 391, ENTRY.PART_OF_CONTROL_GROUP_FLAG,
3342 381, ENTRY.PART_OF_CONTROL_GROUP_FLAG,'N')
3343 AND ENTRY.LIST_HEADER_ID = LHA.LIST_HEADER_ID
3344 AND CS.SCHEDULE_ID = LHA.LIST_USED_BY_ID
3345 AND 'CSCH' = LHA.ARC_LIST_USED_BY
3346 AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE = SRC.SOURCE_TYPE_CODE
3347 AND SRC.LIST_SOURCE_TYPE = 'TARGET'
3348 AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
3349 AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
3350 AND REL.DIRECTIONAL_FLAG(+) = 'F'; */
3351
3352 -- Splitting the SQL between order number metrices and revenue metrices
3353 l_sql_stmt := 'INSERT INTO AMS_INFMET_ORDER_GT(PRIMARY_PARTY_ID, CONTACT_REL_PARTY_ID,
3354 LAST_ACTIVATION_DATE, ACTIVITY_METRIC_ID, SOURCE_CATEGORY, OBJECT_ID)
3355 SELECT decode(SRC.SOURCE_CATEGORY, ''B2C'', ENTRY.PARTY_ID,
3356 ''B2B'', REL.OBJECT_ID, null) PRIMARY_PARTY_ID,
3357 decode(SRC.SOURCE_CATEGORY, ''B2C'', NULL,
3358 ''B2B'', ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID, null) CONTACT_REL_PARTY_ID,
3359 CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID, SRC.SOURCE_CATEGORY, CS.SCHEDULE_ID OBJECT_ID
3360 FROM AMS_METRICS_ALL_B MB,
3361 AMS_ACT_METRICS_ALL AM ,
3362 AMS_CAMPAIGN_SCHEDULES_B CS ,
3363 AMS_LIST_HEADERS_ALL LHA ,
3364 AMS_LIST_ENTRIES ENTRY ,
3365 HZ_RELATIONSHIPS REL,
3366 AMS_LIST_SRC_TYPES SRC
3367 WHERE AM.METRIC_ID = MB.METRIC_ID
3368 AND AM.ARC_ACT_METRIC_USED_BY = ''CSCH''
3369 AND AM.ACT_METRIC_USED_BY_ID = CS.SCHEDULE_ID
3370 AND MB.ENABLED_FLAG = ''Y''
3371 AND MB.METRIC_ID IN (:1, :2)
3372 AND NVL(AM.LAST_CALCULATED_DATE,l_today ) > l_today - G_CALC_LAG_DAYS
3373 -- AND NVL(AM.LAST_CALCULATED_DATE,sysdate ) > sysdate - 90
3374 AND ''Y'' = DECODE(MB.METRIC_ID,
3375 :3, ENTRY.PART_OF_CONTROL_GROUP_FLAG,
3376 :4, ENTRY.ENABLED_FLAG,''N'')
3377 AND ENTRY.LIST_HEADER_ID = LHA.LIST_HEADER_ID
3378 AND CS.SCHEDULE_ID = LHA.LIST_USED_BY_ID
3379 AND ''CSCH'' = LHA.ARC_LIST_USED_BY
3380 AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE = SRC.SOURCE_TYPE_CODE
3381 AND SRC.LIST_SOURCE_TYPE = ''TARGET''
3382 AND SRC.SOURCE_CATEGORY in (''B2C'',''B2B'')
3383 AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
3384 AND REL.DIRECTIONAL_FLAG(+) = ''F'' ';
3385
3386 EXECUTE IMMEDIATE l_sql_stmt USING '381', '382', '381', '382';
3387 EXECUTE IMMEDIATE l_sql_stmt USING '391', '392', '391', '392';
3388
3389 -- Using ordered and NL hint so that the ams_act_product gets the first hit.
3390 -- This change brought down the buffer get from 4 M to 550269.
3391
3392 SELECT NVL(actual_value, 0), activity_metric_id
3393 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
3394 FROM (
3395 SELECT
3396 decode(al.metric_id
3397 , 381, order_count, 382, order_count
3398 , 391, booked_revenue, 392, booked_revenue
3399 , 0 ) actual_value, al.activity_metric_id, func_actual_value,
3400 al.metric_id, al.act_metric_used_by_id
3401 FROM
3402 (SELECT
3403 ACTIVITY_METRIC_ID,
3404 count(distinct nvl(contact_rel_party_id, primary_party_id)) targets,
3405 count(DISTINCT header_id) order_count,
3406 sum(ams_actmetrics_seed_pvt.convert_currency(currency_code, booked_revenue)) booked_revenue
3407 FROM (
3408 SELECT /*+ ordered USE_NL(ACCOUNT H) */ H.header_id, H.transactional_curr_code currency_code,
3409 sum(nvl(I.ordered_quantity * I.unit_selling_price,0)) booked_revenue,
3410 csch_parties.ACTIVITY_METRIC_ID,
3411 primary_party_id, contact_rel_party_id
3412 from AMS_INFMET_ORDER_GT csch_parties,
3413 ams_act_products aprod,
3414 hz_cust_accounts account,
3415 oe_order_headers_all H,
3416 oe_order_lines_all I
3417 where csch_parties.primary_party_id = account.party_id
3418 AND H.sold_to_org_id = account.cust_account_id
3419 AND ((source_category = 'B2B'
3420 AND exists (select 1 from hz_cust_account_roles roles
3421 where H.sold_to_contact_id = roles.cust_account_role_id
3422 AND roles.cust_account_id = account.cust_account_id
3423 AND roles.party_id = csch_parties.contact_rel_party_id
3424 AND rownum = 1))
3425 OR
3426 (source_category = 'B2C'))
3427 and H.booked_flag = 'Y'
3428 AND H.booked_date IS NOT NULL
3429 AND H.flow_status_code = 'BOOKED'
3430 and aprod.arc_act_product_used_by = 'CSCH'
3431 and aprod.act_product_used_by_id = csch_parties.object_id
3432 and aprod.INVENTORY_ITEM_ID = i.ordered_item_id
3433 and aprod.level_type_code = 'PRODUCT'
3434 AND H.header_id = I.header_id
3435 AND H.ordered_date
3436 between csch_parties.last_activation_date
3437 and csch_parties.last_activation_date + l_inferred_period
3441 primary_party_id, contact_rel_party_id
3438 -- and csch_parties.last_activation_date + 90
3439 group by H.header_id, H.transactional_curr_code ,
3440 csch_parties.object_id, csch_parties.activity_metric_id,
3442 ) csch_orders
3443 GROUP BY csch_orders.activity_metric_id
3444 ) T,
3445 ams_act_metrics_all AL, ams_metrics_all_b ALB
3446 WHERE ALB.metric_id IN (391,381,392,382)
3447 AND AL.metric_id = ALB.metric_id
3448 AND ALB.enabled_flag = 'Y'
3449 -- and sysdate - 90 < nvl(AL.last_calculated_date,sysdate)
3450 and l_today - G_CALC_LAG_DAYS < nvl(AL.last_calculated_date,l_today)
3451 AND AL.activity_metric_id = t.activity_metric_id(+)
3452 )
3453 WHERE NVL(actual_value, 0) <> NVL(func_actual_value, -1);
3454
3455 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
3456 l_activity_metric_id_table.DELETE;
3457 l_actual_values_table.DELETE;
3458
3459 end if;
3460
3461 end;
3462 ----------------------------------------------------
3463 -- NAME
3464 -- Calc_Inf_Lead_new
3465 --
3466 -- PURPOSE
3467 -- Calculate inferred leads for all objects.
3468 -- Storing the targted parties in global temp table to improve performance
3469 -- HISTORY
3470 -- 16-Jun-2006 rrajesh created.
3471 --
3472 ------------------------------------------------------------------------------
3473 PROCEDURE Calc_Inf_Lead_new
3474 is
3475 l_actual_values_table num_table_type;
3476 l_activity_metric_id_table num_table_type;
3477 l_today DATE := sysdate;
3478 cursor c_has_inferred_enabled IS
3479 SELECT count(distinct b.metric_id) metric_count,
3480 count(activity_metric_id) activity_count
3481 FROM ams_metrics_all_b b, ams_act_metrics_all a
3482 WHERE b.metric_id IN (371,372)
3483 AND enabled_flag = G_IS_ENABLED
3484 AND a.metric_id = b.metric_id
3485 AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
3486
3487 l_has_enabled NUMBER;
3488 l_activity_count NUMBER;
3489 l_inferred_period number := fnd_profile.value('AMS_METR_INFERRED_PERIOD');
3490 begin
3491 open c_has_inferred_enabled;
3492 fetch c_has_inferred_enabled into l_has_enabled,l_activity_count;
3493 close c_has_inferred_enabled;
3494
3495 IF AMS_DEBUG_HIGH_ON THEN
3496 Write_Log('Calculate_Inferred_Leads','Enabled Count='||l_has_enabled,
3497 'Activity Count='||l_activity_count);
3498 END IF;
3499
3500 if l_has_enabled > 0 then
3501
3502 INSERT INTO AMS_INFMET_LEAD_GT(PRIMARY_PARTY_ID, CONTACT_REL_PARTY_ID,
3503 LAST_ACTIVATION_DATE, ACTIVITY_METRIC_ID, SOURCE_CATEGORY, OBJECT_ID)
3504 SELECT decode(SRC.SOURCE_CATEGORY, 'B2C', ENTRY.PARTY_ID,
3505 'B2B', REL.OBJECT_ID, null) PRIMARY_PARTY_ID,
3506 decode(SRC.SOURCE_CATEGORY, 'B2C', NULL,
3507 'B2B', ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID, null)
3508 CONTACT_REL_PARTY_ID,
3509 CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID,
3510 SRC.SOURCE_CATEGORY, CS.SCHEDULE_ID OBJECT_ID
3511 FROM AMS_CAMPAIGN_SCHEDULES_B CS ,
3512 AMS_LIST_HEADERS_ALL LHA ,
3513 AMS_LIST_ENTRIES ENTRY ,
3514 AMS_ACT_METRICS_ALL AM ,
3515 AMS_METRICS_ALL_B MB,
3516 HZ_RELATIONSHIPS REL,
3517 AMS_LIST_SRC_TYPES SRC
3518 WHERE AM.METRIC_ID = MB.METRIC_ID
3519 AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
3520 AND AM.ACT_METRIC_USED_BY_ID = CS.SCHEDULE_ID
3521 AND MB.ENABLED_FLAG = 'Y'
3522 AND MB.METRIC_ID IN (371,372)
3523 AND nvl(am.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
3524 -- AND nvl(am.last_calculated_date,sysdate) > sysdate - 90
3525 AND 'Y' = DECODE(MB.METRIC_ID, 372, ENTRY.ENABLED_FLAG,
3526 371, ENTRY.PART_OF_CONTROL_GROUP_FLAG,'N')
3527 AND ENTRY.LIST_HEADER_ID = LHA.LIST_HEADER_ID
3528 AND CS.SCHEDULE_ID = LHA.LIST_USED_BY_ID
3529 AND 'CSCH' = LHA.ARC_LIST_USED_BY
3530 AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE = SRC.SOURCE_TYPE_CODE
3531 AND SRC.LIST_SOURCE_TYPE = 'TARGET'
3532 AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
3533 AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
3534 AND REL.DIRECTIONAL_FLAG(+) = 'F' ;
3535
3536 SELECT NVL(actual_value, 0), activity_metric_id
3537 BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
3538 FROM (select actual_value, am.activity_metric_id, am.func_actual_value
3539 from (
3540 select count(1) actual_value, activity_metric_id
3541 from AMS_INFMET_LEAD_GT csch_parties
3542 where exists (select 1
3543 from as_sales_leads lead, as_statuses_b lead_status
3544 where lead.status_code = lead_status.status_code
3545 AND lead_status.lead_flag = 'Y'
3546 AND lead_status.enabled_flag = 'Y'
3547 AND NVL(lead.deleted_flag, 'N') <> 'Y'
3548 and ((source_category = 'B2C' AND lead.customer_id = csch_parties.primary_party_id)
3549 OR
3550 (source_category = 'B2B' and lead.customer_id = csch_parties.primary_party_id
3551 and lead.primary_contact_party_id = csch_parties.contact_rel_party_id))
3552 and lead.creation_date between csch_parties.last_activation_date
3553 and csch_parties.last_activation_date + l_inferred_period
3554 -- and csch_parties.last_activation_date + 90
3555 AND exists (select 1
3556 from as_sales_lead_lines LL, ams_act_products actprod
3557 where ll.inventory_item_id = actprod.INVENTORY_ITEM_ID
3558 and ll.organization_id = actprod.organization_id
3559 and ll.sales_lead_id = lead.sales_lead_id
3560 and actprod.arc_act_product_used_by = 'CSCH'
3561 and actprod.act_product_used_by_id = csch_parties.object_id
3562 and actprod.level_type_code = 'PRODUCT'
3563 and rownum = 1
3564 )
3565 and rownum = 1
3566 )
3567 group by activity_metric_id) leads,
3568 ams_act_metrics_all am, ams_metrics_all_b mb
3569 WHERE am.metric_id = mb.metric_id
3570 and am.arc_act_metric_used_by = 'CSCH'
3571 and mb.enabled_flag = 'Y'
3572 and mb.metric_id in (371,372)
3573 AND nvl(am.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
3574 -- AND nvl(am.last_calculated_date,sysdate) > sysdate - 90
3575 and leads.activity_metric_id(+) = am.activity_metric_id)
3576 where NVL(actual_value, 0) <> NVL(func_actual_value, -1);
3577
3578 update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
3579 l_activity_metric_id_table.DELETE;
3580 l_actual_values_table.DELETE;
3581
3582 end if;
3583
3584 end;
3585 ------------------------------------------------------------------------------
3586 -- NAME
3587 -- Calculate_Inferred_Metrics
3588 --
3589 -- PURPOSE
3590 -- Calculate inferred activity for a all objects.
3591 --
3592 -- HISTORY
3593 -- 11-Aug-2005 dmvincen created.
3594 --
3595 ------------------------------------------------------------------------------
3596 PROCEDURE Calculate_Inferred_Metrics
3597 is
3598 begin
3599 IF AMS_DEBUG_HIGH_ON THEN
3600 Write_Log('Calculate_Inferred_Metrics','BEGIN, ALL');
3601 END IF;
3602
3603 -- Replacing the inferred metrics APIs with new ones using Global temporary tables
3604
3605 -- Calculate_Inferred_Responses;
3606 Calc_Inf_Resp_new;
3607
3608 -- Calculate_Inferred_Leads;
3609 Calc_Inf_Lead_new;
3610
3611 -- Calculate_Inferred_Orders;
3612 Calc_Inf_Order_new;
3613
3614 IF AMS_DEBUG_HIGH_ON THEN
3615 Write_Log('Calculate_Inferred_Metrics','END');
3616 END IF;
3617
3618 end;
3619
3620 END Ams_Actmetrics_Seed_Pvt;