[Home] [Help]
PACKAGE BODY: APPS.OZF_OFFR_ELIG_PROD_DENORM_PVT
Source
1 PACKAGE BODY OZF_OFFR_ELIG_PROD_DENORM_PVT AS
2 /* $Header: ozfvodeb.pls 120.23.12010000.2 2008/07/31 11:28:10 kpatro ship $ */
3
4
5 g_pkg_name CONSTANT VARCHAR2(30):='OZF_OFFR_ELIG_PROD_DENORM_PVT';
6 OZF_DEBUG_LOW CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
7 --a utl_file.file_type;
8 --l_out_dir varchar2(100);
9 --out_dir varchar2(100) := '/sqlcom/crmeco/ozfd1r10/temp';
10 --l_out_file varchar2(100) := 'debug_denorm.txt';
11
12 PROCEDURE write_conc_log
13 (
14 p_text IN VARCHAR2
15 ) IS
16 BEGIN
17 --if OZF_DEBUG_LOW then
18 ozf_utility_pvt.write_conc_log(p_text);
19 --end if;
20 --dbms_output.put_line(p_text);
21 --null;
22 END write_conc_log;
23
24 /*
25 PROCEDURE write_log
26 (
27 p_text IN VARCHAR2
28 ) IS
29 BEGIN
30 utl_file.put(a,p_text );
31 utl_file.new_line(a,1);
32 utl_file.fflush(a);
33
34 END write_log;
35 */
36 ---------------------------------------------------------------------
37 -- FUNCTION
38 -- get_sql
39 --
40 -- PURPOSE
41 -- Retrieves SQL statment for given context, attribute.
42 --
43 -- PARAMETERS
44 -- p_context: product or qualifier context
45 -- p_attribute: context attribute
46 -- p_attr_value: context attribute value
47 -- p_type: PROD for product; ELIG for eligibity
48 --
49 -- NOTES
50 -- This functions returns SQL statement for the given context, attribute and attribute value.
51 ---------------------------------------------------------------------
52 FUNCTION get_sql(
53 p_context IN VARCHAR2,
54 p_attribute IN VARCHAR2,
55 p_attr_value_from IN VARCHAR2,
56 p_attr_value_to IN VARCHAR2,
57 p_comparison IN VARCHAR2,
58 p_type IN VARCHAR2,
59 p_qualifier_id IN NUMBER := NULL,
60 p_qualifier_group IN NUMBER := NULL
61 )
62 RETURN VARCHAR2
63 IS
64 CURSOR c_get_sql IS
65 SELECT sql_validation_1,
66 sql_validation_2,
67 sql_validation_3,
68 sql_validation_4,
69 sql_validation_5,
70 sql_validation_6,
71 sql_validation_7,
72 sql_validation_8,
73 condition_name_column,
74 condition_id_column
75 FROM ozf_denorm_queries
76 WHERE context = p_context
77 AND attribute = p_attribute
78 AND query_for = p_type
79 AND active_flag = 'Y'
80 AND LAST_UPDATE_DATE = (
81 SELECT MAX(LAST_UPDATE_DATE)
82 FROM ozf_denorm_queries
83 WHERE context = p_context
84 AND attribute = p_attribute
85 AND query_for = p_type
86 AND active_flag = 'Y');
87
88 l_stmt VARCHAR2(32000) := NULL;
89 l_stmt_1 VARCHAR2(4000) := NULL;
90 l_stmt_2 VARCHAR2(4000) := NULL;
91 l_stmt_3 VARCHAR2(4000) := NULL;
92 l_stmt_4 VARCHAR2(4000) := NULL;
93 l_stmt_5 VARCHAR2(4000) := NULL;
94 l_stmt_6 VARCHAR2(4000) := NULL;
95 l_stmt_7 VARCHAR2(4000) := NULL;
96 l_stmt_8 VARCHAR2(4000) := NULL;
97 l_cond_name VARCHAR2(40);
98 l_cond_id VARCHAR2(40);
99 l_category number := null;
100 n_attr_value_from NUMBER;
101 l_start_position NUMBER ;
102 l_distinct_position NUMBER;
103 l_qualifier_id NUMBER := NULL;
104 l_qualifier_group NUMBER := NULL;
105
106 BEGIN
107 --FND_DSQL.init;
108
109 if p_qualifier_id is null then
110 l_qualifier_id := -1;
111 else
112 l_qualifier_id := p_qualifier_id;
113 end if;
114
115 if p_qualifier_group is null then
116 l_qualifier_group := -1;
117 else
118 l_qualifier_group := p_qualifier_group;
119 end if;
120
121 OPEN c_get_sql;
122 FETCH c_get_sql INTO l_stmt_1, l_stmt_2,l_stmt_3, l_stmt_4, l_stmt_5, l_stmt_6, l_stmt_7, l_stmt_8, l_cond_name, l_cond_id;
123 CLOSE c_get_sql;
124 if p_context = 'ITEM' AND p_attribute = 'PRICING_ATTRIBUTE2' then
125 l_category := p_attr_value_from;
126 else
127 l_category := null;
128 end if;
129 --ozf_utility_pvt.write_conc_log ('In getSQL : p_attribute is ' || p_attribute);
130 --ozf_utility_pvt.write_conc_log ('In getSQL : l_category is ' || l_category);
131 -- special case, for item only.
132 IF l_stmt_1 is not null then
133 IF INSTR(l_stmt_1, '*') > 0 OR INSTR(l_stmt_1, '?') > 0 THEN
134 FND_DSQL.add_text('SELECT null items_category, TO_NUMBER(');
135 FND_DSQL.add_bind(p_attr_value_from);
136 --FND_DSQL.add_text(') product_id FROM DUAL');
137 FND_DSQL.add_text(') product_id,''PRICING_ATTRIBUTE1'' product_type FROM DUAL');
138 ELSE
139 IF p_context = 'ITEM' then
140 IF l_category is null then
141 --ozf_utility_pvt.write_conc_log ('In getSQL : l_category is null condition ');
142 l_stmt_1 := 'select null items_category, ' || substr(l_stmt_1,7);
143 else
144 --ozf_utility_pvt.write_conc_log ('In getSQL : l_category is not null and is equal to:' || l_category ||':');
145 l_stmt_1 := 'select '|| l_category || ' items_category, ' || substr(l_stmt_1,7);
146 end if;
147 ELSIF l_qualifier_id is not null then
148 l_distinct_position := INSTR(l_stmt_1,' distinct ');
149 if l_distinct_position > 0 then
150 l_start_position := l_distinct_position+9;
151 else
152 l_start_position := 7;
153 end if;
154 l_stmt_1 := 'select '||
155 l_qualifier_id ||
156 ' qp_qualifier_id, ' ||
157 l_qualifier_group||
158 ' qp_qualifier_group, ' ||
159 substr(l_stmt_1,l_start_position);
160 END IF;
161 FND_DSQL.add_text(' '||l_stmt_1);
162 END IF;
163
164 FND_DSQL.add_text(l_stmt_2);
165 FND_DSQL.add_text(l_stmt_3);
166 FND_DSQL.add_text(l_stmt_4);
167 FND_DSQL.add_text(l_stmt_5);
168 FND_DSQL.add_text(l_stmt_6);
169 FND_DSQL.add_text(l_stmt_7);
170 FND_DSQL.add_text(l_stmt_8);
171
172
173 IF l_cond_name IS NOT NULL OR l_cond_id IS NOT NULL THEN
174 IF INSTR(UPPER(l_stmt_1),'WHERE') > 0 OR INSTR(UPPER(l_stmt_2),'WHERE') > 0
175 OR INSTR(UPPER(l_stmt_3),'WHERE') > 0 OR INSTR(UPPER(l_stmt_4),'WHERE') > 0
176 OR INSTR(UPPER(l_stmt_5),'WHERE') > 0 OR INSTR(UPPER(l_stmt_6),'WHERE') > 0
177 OR INSTR(UPPER(l_stmt_7),'WHERE') > 0 OR INSTR(UPPER(l_stmt_8),'WHERE') > 0 THEN
178 IF p_type = 'PROD' THEN
179 FND_DSQL.add_text(' AND ');
180 FND_DSQL.add_text(l_cond_id);
181 FND_DSQL.add_text(' = ');
182 FND_DSQL.add_bind(p_attr_value_from);
183 ELSE
184 IF p_comparison = 'BETWEEN' THEN
185 FND_DSQL.add_text(' AND ');
186 FND_DSQL.add_text(l_cond_name);
187 FND_DSQL.add_text(' BETWEEN ');
188 FND_DSQL.add_bind(p_attr_value_from);
189 FND_DSQL.add_text(' AND ');
190 FND_DSQL.add_bind(p_attr_value_to);
191 ELSIF p_comparison = '=' THEN
192 FND_DSQL.add_text(' AND ');
193 FND_DSQL.add_text(l_cond_id);
194 FND_DSQL.add_text(' = ');
195 FND_DSQL.add_bind(p_attr_value_from);
196 ELSIF p_comparison = 'NOT =' THEN
197 FND_DSQL.add_text(' AND ');
198 FND_DSQL.add_text(l_cond_id);
199 FND_DSQL.add_text(' <> ');
200 FND_DSQL.add_bind(p_attr_value_from);
201 END IF;
202 END IF;
203 ELSE -- no WHERE clause, need to add WHERE
204 IF p_type = 'PROD' THEN
205 FND_DSQL.add_text(' WHERE ');
206 FND_DSQL.add_text(l_cond_id);
207 FND_DSQL.add_text(' = ');
208 FND_DSQL.add_bind(p_attr_value_from);
209 ELSE
210 IF p_comparison = 'BETWEEN' THEN
211 FND_DSQL.add_text(' WHERE ');
212 FND_DSQL.add_text(l_cond_name);
213 FND_DSQL.add_text(' BETWEEN ');
214 FND_DSQL.add_bind(p_attr_value_from);
215 FND_DSQL.add_text(' AND ');
216 FND_DSQL.add_bind(p_attr_value_to);
217 ELSIF p_comparison = '=' THEN
218 FND_DSQL.add_text(' WHERE ');
219 FND_DSQL.add_text(l_cond_id);
220 FND_DSQL.add_text(' = ');
221 FND_DSQL.add_bind(p_attr_value_from);
222 ELSIF p_comparison = 'NOT =' THEN
223 FND_DSQL.add_text(' WHERE ');
224 FND_DSQL.add_text(l_cond_id);
225 FND_DSQL.add_text(' <> ');
226 FND_DSQL.add_bind(p_attr_value_from);
227 END IF;
228 END IF;
229 END IF;
230 END IF;
231 l_stmt := FND_DSQL.get_text(FALSE);
232 else
233 l_stmt := NULL;
234 end if;
235
236 RETURN l_stmt;
237
238 END get_sql;
239
240
241 PROCEDURE insert_excl_prod(
242 p_api_version IN NUMBER,
243 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
244 p_commit IN VARCHAR2 := FND_API.g_false,
245 p_context IN VARCHAR2,
246 p_attribute IN VARCHAR2,
247 p_attr_value IN VARCHAR2,
248 x_return_status OUT NOCOPY VARCHAR2,
249 x_msg_count OUT NOCOPY NUMBER,
250 x_msg_data OUT NOCOPY VARCHAR2
251 )
252 IS
253 CURSOR c_prod_stmt IS
254 SELECT sql_validation_1 || sql_validation_2 || sql_validation_3 || sql_validation_4 || sql_validation_5 || sql_validation_6 || sql_validation_7 || sql_validation_8, condition_id_column
255 FROM ozf_denorm_queries
256 WHERE context = p_context
257 AND attribute = p_attribute
258 AND query_for = 'PROD'
259 AND active_flag = 'Y'
260 AND LAST_UPDATE_DATE = (
261 SELECT MAX(LAST_UPDATE_DATE)
262 FROM ozf_denorm_queries
263 WHERE context = p_context
264 AND attribute = p_attribute
265 AND query_for = 'PROD'
266 AND active_flag = 'Y');
267
268 l_api_version CONSTANT NUMBER := 1.0;
269 l_api_name CONSTANT VARCHAR2(30) := 'insert_excl_prod';
270 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
271 l_prod_stmt VARCHAR2(32000) := NULL;
272 l_cond_id VARCHAR2(40);
273 BEGIN
274 IF FND_API.to_boolean(p_init_msg_list) THEN
275 FND_MSG_PUB.initialize;
276 END IF;
277
278 IF NOT FND_API.compatible_api_call(l_api_version,
279 p_api_version,
280 l_api_name,
281 g_pkg_name)
282 THEN
283 RAISE FND_API.g_exc_unexpected_error;
284 END IF;
285
286 x_return_status := FND_API.g_ret_sts_success;
287
288 IF p_attribute = 'PRICING_ATTRIBUTE1' THEN
289 INSERT INTO ozf_search_selections_t(attribute_value, attribute_id) VALUES(p_attr_value, p_attribute);
290 ELSE
291 OPEN c_prod_stmt;
292 FETCH c_prod_stmt INTO l_prod_stmt, l_cond_id;
293 CLOSE c_prod_stmt;
294
295 l_prod_stmt := 'INSERT INTO ozf_search_selections_t(attribute_value, attribute_id) ' || l_prod_stmt;
296
297 IF l_cond_id IS NOT NULL THEN
298 IF INSTR(UPPER(l_prod_stmt),'WHERE') > 0 THEN
299 l_prod_stmt := l_prod_stmt || ' AND ' || l_cond_id || ' = :1 ';
300 ELSE -- no WHERE clause, need to add WHERE
301 l_prod_stmt := l_prod_stmt || ' WHERE ' || l_cond_id || ' = :1 ';
302 END IF;
303 END IF;
304
305 EXECUTE IMMEDIATE l_prod_stmt USING p_attr_value;
306 END IF;
307
308 EXCEPTION
309 WHEN OTHERS THEN
310 write_conc_log('-- insert_excl_prod failed - '|| SQLERRM || ' ' );
311 x_return_status := FND_API.g_ret_sts_unexp_error;
312
313 write_conc_log('1:' || SUBSTR(l_prod_stmt, 1, 250));
314 write_conc_log('2:' || SUBSTR(l_prod_stmt, 251, 250));
315 write_conc_log('3:' || SUBSTR(l_prod_stmt, 501, 250));
316 write_conc_log('4:' || SUBSTR(l_prod_stmt, 751, 250));
317
318 FND_MESSAGE.set_name('OZF', 'OZF_OFFER_PRODUCT_STMT_FAILED');
319 FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
320 FND_MSG_PUB.add;
321
322 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
323 THEN
324 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
325 END IF;
326
327 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
328 p_count => x_msg_count,
329 p_data => x_msg_data);
330
331 END insert_excl_prod;
332
333
334 ---------------------------------------------------------------------
335 -- FUNCTION
336 -- get_sql_new
337 --
338 -- PURPOSE
339 -- Retrieves SQL statment for given context, attribute.
340 --
341 -- PARAMETERS
342 -- p_context: product or qualifier context
343 -- p_attribute: context attribute
344 -- p_attr_value: context attribute value
345 -- p_type: PROD for product; ELIG for eligibity
346 --
347 -- NOTES
348 -- This functions returns SQL statement for the given context, attribute and attribute value.
349 ---------------------------------------------------------------------
350 FUNCTION get_sql_new(
351 p_context IN VARCHAR2,
352 p_attribute IN VARCHAR2,
353 p_attr_value_from IN VARCHAR2,
354 p_attr_value_to IN VARCHAR2,
355 p_comparison IN VARCHAR2,
356 p_type IN VARCHAR2,
357 p_qualifier_id IN NUMBER,
358 p_qualifier_group IN NUMBER,
359 p_discount_prod_id IN NUMBER,
360 p_discount_line_id IN NUMBER,
361 p_apply_discount IN VARCHAR2,
362 p_include_volume IN VARCHAR2
363 )
364 RETURN VARCHAR2
365 IS
366
367 CURSOR c_get_sql IS
368 SELECT sql_validation_1,
369 sql_validation_2,
370 sql_validation_3,
371 sql_validation_4,
372 sql_validation_5,
373 sql_validation_6,
374 sql_validation_7,
375 sql_validation_8,
376 condition_name_column,
377 condition_id_column
378 FROM ozf_denorm_queries
379 WHERE context = p_context
380 AND attribute = p_attribute
381 AND query_for = p_type
382 AND active_flag = 'Y'
383 AND LAST_UPDATE_DATE = (
384 SELECT MAX(LAST_UPDATE_DATE)
385 FROM ozf_denorm_queries
386 WHERE context = p_context
387 AND attribute = p_attribute
388 AND query_for = p_type
389 AND active_flag = 'Y');
390
391 l_stmt VARCHAR2(32000) := NULL;
392 l_stmt_1 VARCHAR2(4000) := NULL;
393 l_stmt_2 VARCHAR2(4000) := NULL;
394 l_stmt_3 VARCHAR2(4000) := NULL;
395 l_stmt_4 VARCHAR2(4000) := NULL;
396 l_stmt_5 VARCHAR2(4000) := NULL;
397 l_stmt_6 VARCHAR2(4000) := NULL;
398 l_stmt_7 VARCHAR2(4000) := NULL;
399 l_stmt_8 VARCHAR2(4000) := NULL;
400 l_cond_name VARCHAR2(40);
401 l_cond_id VARCHAR2(40);
402 l_category NUMBER := null;
403 n_attr_value_from NUMBER;
404
405
406 BEGIN
407 --FND_DSQL.init;
408
409 OPEN c_get_sql;
410 FETCH c_get_sql INTO l_stmt_1, l_stmt_2,l_stmt_3, l_stmt_4, l_stmt_5, l_stmt_6, l_stmt_7, l_stmt_8, l_cond_name, l_cond_id;
411 CLOSE c_get_sql;
412 -- special case, for item only.
413 if p_context = 'ITEM' AND p_attribute = 'PRICING_ATTRIBUTE2' then
414 l_category := p_attr_value_from;
415 else
416 l_category := null;
417 end if;
418
419 IF l_stmt_1 is not null then
420
421 IF INSTR(l_stmt_1, '*') > 0 OR INSTR(l_stmt_1, '?') > 0 THEN
422 FND_DSQL.add_text('SELECT TO_NUMBER(');
423 FND_DSQL.add_bind(p_attr_value_from);
424 --FND_DSQL.add_text(') product_id FROM DUAL');
425 FND_DSQL.add_text(') product_id,''PRICING_ATTRIBUTE1'' product_type FROM DUAL');
426 ELSE
427 IF p_qualifier_id is not null then
428 l_stmt_1 := 'select '||
429 p_qualifier_id ||
430 ' qp_qualifier_id ' ||
431 p_qualifier_group||
432 ' qp_qualifier_group ' ||
433 substr(l_stmt_1,7);
434 ELSIF p_discount_line_id is not null then
435 l_stmt_1 := 'select '||
436 p_discount_line_id ||
437 ' discount_line_id ' ||
438 p_apply_discount||
439 ' apply_discount ' ||
440 p_include_volume||
441 ' include_volume ' ||
442 l_category || 'items_category' ||
443 substr(l_stmt_1,7);
444 END IF;
445 FND_DSQL.add_text(' '||l_stmt_1);
446 END IF;
447
448 FND_DSQL.add_text(l_stmt_2);
449 FND_DSQL.add_text(l_stmt_3);
450 FND_DSQL.add_text(l_stmt_4);
451 FND_DSQL.add_text(l_stmt_5);
452 FND_DSQL.add_text(l_stmt_6);
453 FND_DSQL.add_text(l_stmt_7);
454 FND_DSQL.add_text(l_stmt_8);
455
456 IF l_cond_name IS NOT NULL OR l_cond_id IS NOT NULL THEN
457 IF INSTR(UPPER(l_stmt_1),'WHERE') > 0 OR INSTR(UPPER(l_stmt_2),'WHERE') > 0
458 OR INSTR(UPPER(l_stmt_3),'WHERE') > 0 OR INSTR(UPPER(l_stmt_4),'WHERE') > 0
459 OR INSTR(UPPER(l_stmt_5),'WHERE') > 0 OR INSTR(UPPER(l_stmt_6),'WHERE') > 0
460 OR INSTR(UPPER(l_stmt_7),'WHERE') > 0 OR INSTR(UPPER(l_stmt_8),'WHERE') > 0 THEN
461 IF p_type = 'PROD' THEN
462 FND_DSQL.add_text(' AND ');
463 FND_DSQL.add_text(l_cond_id);
464 FND_DSQL.add_text(' = ');
465 FND_DSQL.add_bind(p_attr_value_from);
466 ELSE
467 IF p_comparison = 'BETWEEN' THEN
468 FND_DSQL.add_text(' AND ');
469 FND_DSQL.add_text(l_cond_name);
470 FND_DSQL.add_text(' BETWEEN ');
471 FND_DSQL.add_bind(p_attr_value_from);
472 FND_DSQL.add_text(' AND ');
473 FND_DSQL.add_bind(p_attr_value_to);
474 ELSIF p_comparison = '=' THEN
475 FND_DSQL.add_text(' AND ');
476 FND_DSQL.add_text(l_cond_id);
477 FND_DSQL.add_text(' = ');
478 FND_DSQL.add_bind(p_attr_value_from);
479 ELSIF p_comparison = 'NOT =' THEN
480 FND_DSQL.add_text(' AND ');
481 FND_DSQL.add_text(l_cond_id);
482 FND_DSQL.add_text(' <> ');
483 FND_DSQL.add_bind(p_attr_value_from);
484 END IF;
485 END IF;
486 ELSE -- no WHERE clause, need to add WHERE
487 IF p_type = 'PROD' THEN
488 FND_DSQL.add_text(' WHERE ');
489 FND_DSQL.add_text(l_cond_id);
490 FND_DSQL.add_text(' = ');
491 FND_DSQL.add_bind(p_attr_value_from);
492 ELSE
493 IF p_comparison = 'BETWEEN' THEN
494 FND_DSQL.add_text(' WHERE ');
495 FND_DSQL.add_text(l_cond_name);
496 FND_DSQL.add_text(' BETWEEN ');
497 FND_DSQL.add_bind(p_attr_value_from);
498 FND_DSQL.add_text(' AND ');
499 FND_DSQL.add_bind(p_attr_value_to);
500 ELSIF p_comparison = '=' THEN
501 FND_DSQL.add_text(' WHERE ');
502 FND_DSQL.add_text(l_cond_id);
503 FND_DSQL.add_text(' = ');
504 FND_DSQL.add_bind(p_attr_value_from);
505 ELSIF p_comparison = 'NOT =' THEN
506 FND_DSQL.add_text(' WHERE ');
507 FND_DSQL.add_text(l_cond_id);
508 FND_DSQL.add_text(' <> ');
509 FND_DSQL.add_bind(p_attr_value_from);
510 END IF;
511 END IF;
512 END IF;
513 END IF;
514 l_stmt := FND_DSQL.get_text(FALSE);
515 else
516 l_stmt := NULL;
517 end if;
518
519 RETURN l_stmt;
520
521 END get_sql_new;
522
523
524 ---------------------------------------------------------------------
525 -- PROCEDURE
526 -- refresh_netaccrual_parties
527 --
528 -- PURPOSE
529 -- Refreshes offer and party denorm table ozf_activity_customers.
530 --
531 -- PARAMETERS
532 -- p_list_header_id: qp_list_header_id of the offer
533 --
534 -- DESCRIPTION
535 -- This procedure calls get_sql, builds SQL statment for parties and refresh ozf_activity_customers
536 ---------------------------------------------------------------------
537 PROCEDURE refresh_netaccrual_parties(
538 p_api_version IN NUMBER,
539 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
540 p_commit IN VARCHAR2 := FND_API.g_false,
541
542 p_list_header_id IN NUMBER,
543 p_calling_from_den IN VARCHAR2,
544
545 x_return_status OUT NOCOPY VARCHAR2,
546 x_msg_count OUT NOCOPY NUMBER,
547 x_msg_data OUT NOCOPY VARCHAR2,
548
549 x_party_stmt OUT NOCOPY VARCHAR2,
550 p_qnum IN NUMBER :=NULL
551 )
552 IS
553
554 CURSOR c_offer_id IS
555 SELECT offer_id
556 FROM ozf_offers
557 WHERE qp_list_header_id = p_list_header_id;
558
559 CURSOR c_no_groups(l_offer_id NUMBER) IS
560 SELECT COUNT(*)
561 FROM ozf_offer_qualifiers
562 WHERE offer_id = l_offer_id
563 AND qualifier_id = nvl(p_qnum,qualifier_id)
564 AND active_flag = 'Y';
565
566 CURSOR c_groups(l_offer_id NUMBER) IS
567 SELECT qualifier_id
568 FROM ozf_offer_qualifiers
569 WHERE offer_id = l_offer_id
570 AND qualifier_id = nvl(p_qnum,qualifier_id)
571 AND active_flag = 'Y';
572
573
574 CURSOR c_qualifiers(p_qualifier_id NUMBER) IS
575 SELECT NVL(qualifier_context,
576 DECODE(qualifier_attribute,
577 'BUYER', 'CUSTOMER_GROUP',
578 'CUSTOMER_BILL_TO', 'CUSTOMER',
579 'CUSTOMER', 'CUSTOMER',
580 'LIST', 'CUSTOMER_GROUP',
581 'SEGMENT', 'CUSTOMER_GROUP',
582 'TERRITORY', 'TERRITORY',
583 'SHIP_TO', 'CUSTOMER')) qualifier_context,
584 DECODE(qualifier_attribute,
585 'BUYER', 'QUALIFIER_ATTRIBUTE3',
586 'CUSTOMER_BILL_TO', 'QUALIFIER_ATTRIBUTE14',
587 'CUSTOMER', 'QUALIFIER_ATTRIBUTE2',
588 'LIST', 'QUALIFIER_ATTRIBUTE1',
589 'SEGMENT', 'QUALIFIER_ATTRIBUTE2',
590 'TERRITORY', 'QUALIFIER_ATTRIBUTE1',
591 'SHIP_TO', 'QUALIFIER_ATTRIBUTE11',
592 qualifier_attribute) qualifier_attribute,
593 qualifier_attr_value,
594 '=' comparison_operator_code
595 FROM ozf_offer_qualifiers
596 WHERE qualifier_id = p_qualifier_id;
597
598 l_api_name CONSTANT VARCHAR2(30) := 'refresh_netaccrual_parties';
599 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
600
601 l_stmt_temp VARCHAR2(32000) := NULL;
602 l_no_query_flag VARCHAR2(1) := 'N';
603 l_no_groups NUMBER;
604 l_no_lines NUMBER;
605 l_group_index NUMBER;
606 l_line_index NUMBER;
607 l_offer_id NUMBER;
608
609 BEGIN
610
611 ozf_utility_pvt.write_conc_log(l_full_name ||
612 ': Start refresh netaccrual parties' ||
613 '-'||to_char(sysdate,'dd-mon-yyyy-hh:mi:ss'));
614
615 x_return_status := FND_API.g_ret_sts_success;
616
617 OPEN c_offer_id;
618 FETCH c_offer_id INTO l_offer_id;
619 CLOSE c_offer_id;
620
621 OPEN c_no_groups(l_offer_id);
622 FETCH c_no_groups INTO l_no_groups;
623 CLOSE c_no_groups;
624
625 ozf_utility_pvt.write_conc_log('l_elig_exists:'||l_no_groups);
626 IF l_no_groups > 0 THEN
627 l_group_index := 1;
628
629 FOR i IN c_groups(l_offer_id) LOOP
630 l_line_index := 1;
631 l_no_lines := 1; -- currently NA qualifier does not support grouping, each group has only 1 line
632
633 FND_DSQL.add_text('(');
634 FOR j IN c_qualifiers(i.qualifier_id) LOOP
635 l_stmt_temp := NULL;
636
637 l_stmt_temp := get_sql(p_context => j.qualifier_context,
638 p_attribute => j.qualifier_attribute,
639 p_attr_value_from => j.qualifier_attr_value,
640 p_attr_value_to => NULL,--j.qualifier_attr_value_to,
641 p_comparison => j.comparison_operator_code,
642 p_type => 'ELIG',
643 p_qualifier_id => i.qualifier_id,
644 p_qualifier_group => NULL
645 );
646
647 IF l_stmt_temp IS NULL THEN
648 l_no_query_flag := 'Y';
649 EXIT;
650 ELSE
651 IF l_line_index < l_no_lines THEN
652 FND_DSQL.add_text(' INTERSECT ');
653 l_line_index := l_line_index + 1;
654 END IF;
655 END IF;
656 END LOOP;
657 FND_DSQL.add_text(')');
658
659 IF l_group_index < l_no_groups THEN
660 FND_DSQL.add_text(' UNION ');
661 l_group_index := l_group_index + 1;
662 END IF;
663 END LOOP;
664 ELSE
665 FND_DSQL.add_text('(SELECT -1 qp_qualifier_id, -1 qp_qualifier_group, -1 party_id,-1 cust_account_id, -1 cust_acct_site_id, -1 site_use_id,'' '' site_use_code FROM DUAL)');
666 END IF;
667
668 IF p_calling_from_den = 'N' OR l_no_query_flag = 'N' THEN
669 x_party_stmt := FND_DSQL.get_text(FALSE);
670 ELSE
671 x_party_stmt := NULL;
672 END IF;
673 --ozf_utility_pvt.write_conc_log('1:'||substr(x_party_stmt,945,250));
674 --ozf_utility_pvt.write_conc_log('2:'||substr(x_party_stmt,1195,250));
675 --ozf_utility_pvt.write_conc_log('3:'||substr(x_party_stmt,1445,250));
676
677 EXCEPTION
678 WHEN OTHERS THEN
679 x_return_status := FND_API.g_ret_sts_unexp_error;
680
681 FND_MESSAGE.set_name('OZF', 'OZF_OFFER_PARTY_STMT_FAILED');
682 FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
683 FND_MSG_PUB.add;
684
685 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
686 THEN
687 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
688 END IF;
689
690 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
691 p_count => x_msg_count,
692 p_data => x_msg_data);
693
694 END refresh_netaccrual_parties;
695
696 --------------------------------------------------------------------
697 -- PROCEDURE
698 -- refresh_netaccrual_products
699 --
700 -- PURPOSE
701 -- Refreshes offer and product denorm table ozf_activity_products
702 -- for NETACCRUAL offers.
703 --
704 -- PARAMETERS
705 -- p_list_header_id: qp_list_header_id of the offer
706 -- DESCRIPTION
707 -- This procedure calls get_sql, builds SQL statment for product and refresh ozf_activity_products
708 ----------------------------------------------------------------------
709 PROCEDURE refresh_netaccrual_products(
710 p_api_version IN NUMBER,
711 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
712 p_commit IN VARCHAR2 := FND_API.g_false,
713
714 p_list_header_id IN NUMBER,
715 p_calling_from_den IN VARCHAR2,
716
717 x_return_status OUT NOCOPY VARCHAR2,
718 x_msg_count OUT NOCOPY NUMBER,
719 x_msg_data OUT NOCOPY VARCHAR2,
720
721 x_product_stmt OUT NOCOPY VARCHAR2,
722 p_lline_id IN NUMBER := NULL
723 )
724 IS
725
726 CURSOR c_products(l_used_by_id NUMBER) IS
727 SELECT off_discount_product_id,
728 product_id,
729 product_level
730 FROM ozf_offer_discount_products
731 WHERE offer_id = l_used_by_id
732 AND off_discount_product_id = nvl(p_lline_id, off_discount_product_id)
733 AND excluder_flag = 'N';
734
735 CURSOR c_excluded_products(l_used_by_id NUMBER,l_product_id NUMBER) IS
736 SELECT off_discount_product_id,
737 product_id,
738 product_level
739 FROM ozf_offer_discount_products
740 WHERE offer_id = l_used_by_id
741 AND parent_off_disc_prod_id = l_product_id
742 AND excluder_flag = 'Y';
743
744 CURSOR c_no_products(l_used_by_id NUMBER) IS
745 SELECT COUNT(*)
746 FROM ozf_offer_discount_products
747 WHERE offer_id = l_used_by_id
748 AND off_discount_product_id = nvl(p_lline_id, off_discount_product_id)
749 AND excluder_flag = 'N';
750
751 CURSOR c_no_excl_products(l_used_by_id NUMBER,l_product_id NUMBER) IS
752 SELECT COUNT(*)
753 FROM ozf_offer_discount_products
754 WHERE offer_id = l_used_by_id
755 AND parent_off_disc_prod_id = l_product_id
756 AND excluder_flag = 'Y';
757
758 CURSOR c_offer_id IS
759 SELECT offer_id
760 FROM ozf_offers
761 WHERE qp_list_header_id = p_list_header_id;
762
763 l_api_version CONSTANT NUMBER := 1.0;
764 l_api_name CONSTANT VARCHAR2(30) := 'refresh_netaccrual_products';
765 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
766 l_no_query_flag VARCHAR2(1) := 'N';
767 l_org_id NUMBER ;
768 l_context VARCHAR2(30);
769 l_context_attr VARCHAR2(30);
770 l_prod_attr_val VARCHAR2(240);
771
772 l_stmt_temp VARCHAR2(32000) := NULL;
773 l_stmt_product1 VARCHAR2(32000) := NULL;
774 l_stmt_product2 VARCHAR2(32000) := NULL;
775 l_stmt_product VARCHAR2(32000) := NULL;
776
777 l_no_products NUMBER;
778 l_no_excl_products NUMBER;
779 l_prod_index NUMBER;
780 l_excl_index NUMBER;
781 l_offer_id NUMBER;
782
783 BEGIN
784
785 ozf_utility_pvt.write_conc_log(l_full_name || ': start refresh_products_netaccrual');
786
787 IF FND_API.to_boolean(p_init_msg_list) THEN
788 FND_MSG_PUB.initialize;
789 END IF;
790
791 IF NOT FND_API.compatible_api_call(l_api_version,
792 p_api_version,
793 l_api_name,
794 g_pkg_name)
795 THEN
796 RAISE FND_API.g_exc_unexpected_error;
797 END IF;
798
799 x_return_status := FND_API.g_ret_sts_success;
800 l_org_id := FND_PROFILE.VALUE('QP_ORGANIZATION_ID');
801 l_context := 'ITEM';
802
803 OPEN c_offer_id;
804 FETCH c_offer_id INTO l_offer_id;
805 CLOSE c_offer_id;
806
807
808 OPEN c_no_products(l_offer_id);
809 FETCH c_no_products INTO l_no_products;
810 CLOSE c_no_products;
811
812 l_prod_index := 1;
813
814 IF l_no_products > 0 THEN
815 FOR i IN c_products(l_offer_id)
816 LOOP
817 IF i.product_level = 'FAMILY' THEN
818 OPEN c_no_excl_products(l_offer_id,i.off_discount_product_id);
819 FETCH c_no_excl_products INTO l_no_excl_products;
820 CLOSE c_no_excl_products;
821 END IF;
822
823 l_excl_index := 1;
824 l_stmt_temp := null;
825
826 IF i.product_level = 'PRODUCT' THEN
827 l_context_attr := 'PRICING_ATTRIBUTE1';
828 l_prod_attr_val := i.product_id;
829 ELSIF i.product_level = 'FAMILY' THEN
830 l_context_attr := 'PRICING_ATTRIBUTE2';
831 l_prod_attr_val := i.product_id;
832 END IF;
833
834 FND_DSQL.add_text('(');
835 l_stmt_temp := get_sql(p_context => l_context,
836 p_attribute => l_context_attr,
837 p_attr_value_from => l_prod_attr_val,
838 p_attr_value_to => NULL,
839 p_comparison => '=',
840 p_type => 'PROD'
841 );
842
843 IF l_stmt_temp IS NULL THEN
844 l_no_query_flag := 'Y';
845 ELSE
846 IF l_no_excl_products > 0 THEN
847 EXECUTE IMMEDIATE 'TRUNCATE TABLE ozf_search_selections_t';
848 FOR j IN c_excluded_products(l_offer_id,i.off_discount_product_id)
849 LOOP
850 IF j.product_level = 'PRODUCT' THEN
851 l_context_attr := 'PRICING_ATTRIBUTE1';
852 l_prod_attr_val := j.product_id;
853 ELSIF j.product_level = 'FAMILY' THEN
854 l_context_attr := 'PRICING_ATTRIBUTE2';
855 l_prod_attr_val := j.product_id;
856 END IF;
857
858 insert_excl_prod(p_api_version => p_api_version,
859 p_init_msg_list => p_init_msg_list,
860 p_commit => p_commit,
861 p_context => l_context,
862 p_attribute => l_context_attr,
863 p_attr_value => l_prod_attr_val,
864 x_return_status => x_return_status,
865 x_msg_count => x_msg_count,
866 x_msg_data => x_msg_data);
867 END LOOP;
868 FND_DSQL.add_text(' MINUS select attribute_value product_id, attribute_id product_type from ozf_search_selections_t ');
869 END IF;
870 END IF;
871
872 FND_DSQL.add_text(')');
873
874 IF l_prod_index < l_no_products THEN
875 FND_DSQL.add_text(' UNION ');
876 l_prod_index := l_prod_index + 1;
877 END IF;
878 END LOOP;
879 ELSE
880 l_no_query_flag := 'Y';
881 END IF;
882
883 IF p_calling_from_den = 'N' OR l_no_query_flag = 'N' THEN
884 x_product_stmt := FND_DSQL.get_text(FALSE);
885 ELSE
886 x_product_stmt := NULL;
887 END IF;
888
889 EXCEPTION
890
891 WHEN OTHERS THEN
892 x_return_status := FND_API.g_ret_sts_unexp_error;
893
894 FND_MESSAGE.set_name('AMS', 'AMS_OFFER_PRODUCT_STMT_FAILED');
895 FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
896 FND_MSG_PUB.add;
897
898 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
899 THEN
900 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
901 END IF;
902
903 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
904 p_count => x_msg_count,
905 p_data => x_msg_data);
906
907 END refresh_netaccrual_products;
908
909 --------------------------------------------------------------------
910 -- PROCEDURE
911 -- refresh_volume_products
912 --
913 -- PURPOSE
914 -- Refreshes offer and product denorm table ozf_activity_products
915 -- for NETACCRUAL offers.
916 --
917 -- PARAMETERS
918 -- p_list_header_id: qp_list_header_id of the offer
919 -- DESCRIPTION
920 -- This procedure calls get_sql, builds SQL statment for product and refresh ozf_activity_products
921 ----------------------------------------------------------------------
922 PROCEDURE refresh_volume_products(
923 p_api_version IN NUMBER,
924 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
925 p_commit IN VARCHAR2 := FND_API.g_false,
926
927 p_list_header_id IN NUMBER,
928 p_calling_from_den IN VARCHAR2,
929
930 x_return_status OUT NOCOPY VARCHAR2,
931 x_msg_count OUT NOCOPY NUMBER,
932 x_msg_data OUT NOCOPY VARCHAR2,
933
934 x_product_stmt OUT NOCOPY VARCHAR2,
935 p_lline_id IN NUMBER := NULL
936 )
937 IS
938
939 -- The assumption is that all the product in a discount table(tier) would have the same discount_line_id
940 -- ie the line belonging to the PBH header line
941
942 CURSOR c_lines(l_offer_id NUMBER) IS
943 SELECT offer_discount_line_id
944 FROM ozf_offer_discount_lines
945 WHERE offer_id = l_offer_id
946 AND offer_discount_line_id = NVL(p_lline_id, offer_discount_line_id)
947 AND tier_type = 'PBH';
948
949 CURSOR c_no_lines(l_offer_id NUMBER) IS
950 SELECT COUNT(*)
951 FROM ozf_offer_discount_lines
952 WHERE offer_id = l_offer_id
953 AND offer_discount_line_id = NVL(p_lline_id, offer_discount_line_id)
954 AND tier_type = 'PBH';
955
956 CURSOR c_products(l_offer_id NUMBER,l_offer_discount_line_id NUMBER) IS
957 SELECT off_discount_product_id,
958 product_context,
959 product_attribute,
960 product_attr_value,
961 offer_discount_line_id
962 FROM ozf_offer_discount_products
963 WHERE offer_id = l_offer_id
964 AND offer_discount_line_id = l_offer_discount_line_id
965 AND excluder_flag = 'N';
966
967 CURSOR c_excluded_products(l_offer_id NUMBER,l_offer_discount_line_id NUMBER) IS
968 SELECT off_discount_product_id,
969 product_context,
970 product_attribute,
971 product_attr_value,
972 offer_discount_line_id
973 FROM ozf_offer_discount_products
974 WHERE offer_id = l_offer_id
975 AND offer_discount_line_id = l_offer_discount_line_id
976 AND excluder_flag = 'Y';
977
978 CURSOR c_no_products(l_offer_id NUMBER,l_offer_discount_line_id NUMBER) IS
979 SELECT COUNT(*)
980 FROM ozf_offer_discount_products
981 WHERE offer_id = l_offer_id
982 AND offer_discount_line_id = l_offer_discount_line_id
983 AND excluder_flag = 'N';
984
985 CURSOR c_no_excl_products(l_offer_id NUMBER,l_offer_discount_line_id NUMBER) IS
986 SELECT COUNT(*)
987 FROM ozf_offer_discount_products
988 WHERE offer_id = l_offer_id
989 AND offer_discount_line_id = l_offer_discount_line_id
990 AND excluder_flag = 'Y';
991
992 CURSOR c_offer_id(l_list_header_id NUMBER) IS
993 SELECT offer_id
994 FROM ozf_offers
995 WHERE qp_list_header_id = l_list_header_id;
996
997
998 l_api_version CONSTANT NUMBER := 1.0;
999 l_api_name CONSTANT VARCHAR2(30) := 'refresh_volume_products';
1000 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1001 l_no_query_flag VARCHAR2(1) := 'N';
1002 l_org_id NUMBER ;
1003 l_context VARCHAR2(30);
1004 l_context_attr VARCHAR2(30);
1005 l_prod_attr_val VARCHAR2(240);
1006
1007 l_stmt_temp VARCHAR2(32000) := NULL;
1008 l_stmt_product1 VARCHAR2(32000) := NULL;
1009 l_stmt_product2 VARCHAR2(32000) := NULL;
1010 l_stmt_product VARCHAR2(32000) := NULL;
1011
1012 l_no_products NUMBER;
1013 l_no_excl_products NUMBER;
1014 l_prod_index NUMBER;
1015 l_excl_index NUMBER;
1016 l_no_lines NUMBER;
1017 l_line_index NUMBER;
1018 l_offer_id NUMBER;
1019
1020 BEGIN
1021 ozf_utility_pvt.write_conc_log(l_full_name || ': start refresh_products_volume');
1022
1023 IF FND_API.to_boolean(p_init_msg_list) THEN
1024 FND_MSG_PUB.initialize;
1025 END IF;
1026
1027 IF NOT FND_API.compatible_api_call(l_api_version,
1028 p_api_version,
1029 l_api_name,
1030 g_pkg_name)
1031 THEN
1032 RAISE FND_API.g_exc_unexpected_error;
1033 END IF;
1034
1035 x_return_status := FND_API.g_ret_sts_success;
1036 l_org_id := FND_PROFILE.VALUE('QP_ORGANIZATION_ID');
1037 l_context := 'ITEM';
1038
1039 OPEN c_offer_id(p_list_header_id);
1040 FETCH c_offer_id INTO l_offer_id;
1041 CLOSE c_offer_id;
1042
1043 OPEN c_no_lines(l_offer_id);
1044 FETCH c_no_lines INTO l_no_lines;
1045 CLOSE c_no_lines;
1046
1047 l_line_index := 1;
1048
1049 IF l_no_lines > 0 THEN
1050 FOR i in c_lines(l_offer_id)
1051 LOOP
1052 OPEN c_no_products(l_offer_id,i.offer_discount_line_id);
1053 FETCH c_no_products INTO l_no_products;
1054 CLOSE c_no_products;
1055 l_prod_index := 1;
1056
1057 OPEN c_no_excl_products(l_offer_id,i.offer_discount_line_id);
1058 FETCH c_no_excl_products INTO l_no_excl_products;
1059 CLOSE c_no_excl_products;
1060
1061 FND_DSQL.add_text('(');
1062
1063 IF l_no_products > 0 THEN
1064 FOR j in c_products(l_offer_id,i.offer_discount_line_id)
1065 LOOP
1066 -- IF j.product_context = 'PRICING_ATTRIBUTE2' THEN
1067 -- END IF;
1068
1069 l_excl_index := 1;
1070 l_stmt_temp := null;
1071
1072 l_stmt_temp := get_sql(p_context => j.product_context,
1073 p_attribute => j.product_attribute,
1074 p_attr_value_from => j.product_attr_value,
1075 p_attr_value_to => NULL,
1076 p_comparison => '=',
1077 p_type => 'PROD'
1078 );
1079 /*
1080 l_stmt_temp := get_sql(p_context => j.context,
1081 p_attribute => j.attribute,
1082 p_attr_value_from => j.attribute_value,
1083 p_attr_value_to => NULL,
1084 p_comparison => '=',
1085 p_type => 'PROD',
1086 p_qualifier_id => NULL,
1087 p_qualifier_group => NULL,
1088 p_discount_line_id => j.offer_discount_line_id,
1089 p_apply_discount => j.apply_discount,
1090 p_include_volume => j.include_volume
1091 );
1092 */
1093 IF l_stmt_temp IS NULL THEN
1094 l_no_query_flag := 'Y';
1095 EXIT;
1096 END IF;
1097
1098 IF l_prod_index < l_no_products THEN
1099 FND_DSQL.add_text(' UNION ');
1100 l_prod_index := l_prod_index + 1;
1101 END IF;
1102 END LOOP; -- j loop
1103
1104 IF l_no_excl_products > 0 THEN
1105 EXECUTE IMMEDIATE 'TRUNCATE TABLE ozf_search_selections_t';
1106 FOR k IN c_excluded_products(l_offer_id,i.offer_discount_line_id) LOOP
1107 insert_excl_prod(p_api_version => p_api_version,
1108 p_init_msg_list => p_init_msg_list,
1109 p_commit => p_commit,
1110 p_context => k.product_context,
1111 p_attribute => k.product_attribute,
1112 p_attr_value => k.product_attr_value,
1113 x_return_status => x_return_status,
1114 x_msg_count => x_msg_count,
1115 x_msg_data => x_msg_data);
1116 END LOOP; -- k loop
1117 FND_DSQL.add_text(' MINUS select attribute_value product_id, attribute_id product_type from ozf_search_selections_t ');
1118 END IF;
1119
1120 l_no_query_flag := 'N';
1121 ELSE
1122 l_no_query_flag := 'Y';
1123 END IF;
1124
1125 FND_DSQL.add_text(')');
1126
1127 IF l_line_index < l_no_lines THEN
1128 FND_DSQL.add_text(' UNION ');
1129 l_line_index := l_line_index + 1;
1130 END IF;
1131
1132 END LOOP; -- i loop
1133 ELSE
1134 l_no_query_flag := 'Y';
1135 END IF;
1136
1137 IF p_calling_from_den = 'N' OR l_no_query_flag = 'N' THEN
1138 x_product_stmt := FND_DSQL.get_text(FALSE);
1139 ELSE
1140 x_product_stmt := NULL;
1141 END IF;
1142
1143 EXCEPTION
1144 WHEN OTHERS THEN
1145 x_return_status := FND_API.g_ret_sts_unexp_error;
1146 FND_MESSAGE.set_name('AMS', 'AMS_OFFER_PRODUCT_STMT_FAILED');
1147 FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
1148 FND_MSG_PUB.add;
1149
1150 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1151 THEN
1152 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1153 END IF;
1154
1155 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
1156 p_count => x_msg_count,
1157 p_data => x_msg_data);
1158
1159 END refresh_volume_products;
1160
1161 PROCEDURE refresh_lumpsum_parties(
1162 p_api_version IN NUMBER,
1163 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1164 p_commit IN VARCHAR2 := FND_API.g_false,
1165
1166 p_list_header_id IN NUMBER,
1167 p_calling_from_den IN VARCHAR2,
1168
1169 x_return_status OUT NOCOPY VARCHAR2,
1170 x_msg_count OUT NOCOPY NUMBER,
1171 x_msg_data OUT NOCOPY VARCHAR2,
1172
1173 x_party_stmt OUT NOCOPY VARCHAR2
1174 )
1175 IS
1176 CURSOR c_qualifier_exists IS
1177 SELECT 'Y'
1178 FROM ozf_offers
1179 WHERE qualifier_type IS NOT NULL
1180 AND qualifier_id IS NOT NULL
1181 AND qp_list_header_id = p_list_header_id;
1182
1183 CURSOR c_qualifiers IS
1184 SELECT DECODE(qualifier_type, 'BUYER', 'CUSTOMER_GROUP',
1185 'CUSTOMER', 'CUSTOMER',
1186 'CUSTOMER_BILL_TO', 'CUSTOMER',
1187 'SHIP_TO', 'CUSTOMER') qualifier_context,
1188 DECODE(qualifier_type, 'BUYER', 'QUALIFIER_ATTRIBUTE3',
1189 'CUSTOMER', 'QUALIFIER_ATTRIBUTE2',
1190 'CUSTOMER_BILL_TO', 'QUALIFIER_ATTRIBUTE14',
1191 'SHIP_TO', 'QUALIFIER_ATTRIBUTE11') qualifier_attribute,
1192 qualifier_id qualifier_attr_value
1193 FROM ozf_offers
1194 WHERE qp_list_header_id = p_list_header_id;
1195
1196 l_api_name CONSTANT VARCHAR2(30) := 'refresh_lumpsum_parties';
1197 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1198
1199 l_stmt_temp VARCHAR2(32000) := NULL;
1200 l_no_query_flag VARCHAR2(1) := 'N';
1201 l_qualifier_context VARCHAR2(30);
1202 l_qualifier_attribute VARCHAR2(30);
1203 l_qualifier_attr_value NUMBER;
1204 l_qualifier_exists VARCHAR2(1) := NULL;
1205
1206 BEGIN
1207
1208 ozf_utility_pvt.write_conc_log(l_full_name ||
1209 ': Start refresh lumpsum parties' ||
1210 '-'||to_char(sysdate,'dd-mon-yyyy-hh:mi:ss'));
1211
1212 x_return_status := FND_API.g_ret_sts_success;
1213
1214 OPEN c_qualifier_exists;
1215 FETCH c_qualifier_exists INTO l_qualifier_exists;
1216 CLOSE c_qualifier_exists;
1217
1218 IF l_qualifier_exists = 'Y' THEN
1219 OPEN c_qualifiers;
1220 FETCH c_qualifiers INTO l_qualifier_context, l_qualifier_attribute, l_qualifier_attr_value;
1221 CLOSE c_qualifiers;
1222
1223 FND_DSQL.add_text('(');
1224 l_stmt_temp := NULL;
1225 l_stmt_temp := get_sql(p_context => l_qualifier_context,
1226 p_attribute => l_qualifier_attribute,
1227 p_attr_value_from => l_qualifier_attr_value,
1228 p_attr_value_to => NULL,
1229 p_comparison => '=',
1230 p_type => 'ELIG',
1231 p_qualifier_id => NULL,
1232 p_qualifier_group => NULL);
1233 /*
1234 l_stmt_temp := get_sql(p_context => l_qualifier_context,
1235 p_attribute => l_qualifier_attribute,
1236 p_attr_value_from => l_qualifier_attr_value,
1237 p_attr_value_to => NULL,
1238 p_comparison => '=',
1239 p_type => 'ELIG',
1240 p_qualifier_id => NULL,
1241 p_qualifier_group => NULL,
1242 p_discount_prod_id => NULL,
1243 p_discount_line_id => NULL,
1244 p_apply_discount => NULL,
1245 p_include_volume => NULL);
1246 */
1247 IF l_stmt_temp IS NULL THEN
1248 l_no_query_flag := 'Y';
1249 END IF;
1250
1251 FND_DSQL.add_text(')');
1252 ELSE
1253 FND_DSQL.add_text('(SELECT -1 qp_qualifier_id, -1 qp_qualifier_group, -1 party_id,-1 cust_account_id, -1 cust_acct_site_id, -1 site_use_id,'' '' site_use_code FROM DUAL)');
1254 END IF;
1255
1256 IF p_calling_from_den = 'N' OR l_no_query_flag = 'N' THEN
1257 x_party_stmt := FND_DSQL.get_text(FALSE);
1258 ELSE
1259 x_party_stmt := NULL;
1260 END IF;
1261 --ozf_utility_pvt.write_conc_log('1:'||substr(x_party_stmt,945,250));
1262 --ozf_utility_pvt.write_conc_log('2:'||substr(x_party_stmt,1195,250));
1263 --ozf_utility_pvt.write_conc_log('3:'||substr(x_party_stmt,1445,250));
1264
1265 EXCEPTION
1266 WHEN OTHERS THEN
1267 x_return_status := FND_API.g_ret_sts_unexp_error;
1268
1269 FND_MESSAGE.set_name('OZF', 'OZF_OFFER_PARTY_STMT_FAILED');
1270 FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
1271 FND_MSG_PUB.add;
1272
1273 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1274 THEN
1275 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1276 END IF;
1277
1278 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
1279 p_count => x_msg_count,
1280 p_data => x_msg_data);
1281
1282 END refresh_lumpsum_parties;
1283
1284 ---------------------------------------------------------------------
1285 -- PROCEDURE
1286 -- refresh_volume_parties
1287 --
1288 -- PURPOSE
1289 -- Refreshes offer and party denorm table ozf_activity_customers.
1290 --
1291 -- PARAMETERS
1292 -- p_list_header_id: qp_list_header_id of the offer
1293 --
1294 -- DESCRIPTION
1295 -- This procedure calls get_sql, builds SQL statment for parties and refresh ozf_activity_customers
1296 ---------------------------------------------------------------------
1297 PROCEDURE refresh_volume_parties(
1298 p_api_version IN NUMBER,
1299 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1300 p_commit IN VARCHAR2 := FND_API.g_false,
1301
1302 p_list_header_id IN NUMBER,
1303 p_calling_from_den IN VARCHAR2,
1304
1305 x_return_status OUT NOCOPY VARCHAR2,
1306 x_msg_count OUT NOCOPY NUMBER,
1307 x_msg_data OUT NOCOPY VARCHAR2,
1308 x_party_stmt OUT NOCOPY VARCHAR2,
1309 p_qnum IN NUMBER := NULL
1310 )
1311 IS
1312
1313 -- fix for bug no. 6058819
1314 CURSOR c_no_groups IS
1315 SELECT COUNT(*)
1316 FROM qp_qualifiers a,
1317 ozf_denorm_queries b
1318 WHERE a.list_header_id = p_list_header_id
1319 --AND a.active_flag = 'Y'
1320 AND a.qualifier_grouping_no = NVL(p_qnum, a.qualifier_grouping_no)
1321 AND a.qualifier_context = b.context
1322 AND a.qualifier_attribute = b.attribute
1323 AND b.query_for = 'ELIG';
1324
1325
1326 CURSOR c_groups IS
1327 SELECT a.qualifier_id,
1328 a.qualifier_grouping_no
1329 FROM qp_qualifiers a,
1330 ozf_denorm_queries b
1331 WHERE a.list_header_id = p_list_header_id
1332 --AND a.active_flag = 'Y'
1333 AND a.qualifier_grouping_no = NVL(p_qnum, a.qualifier_grouping_no)
1334 AND a.qualifier_context = b.context
1335 AND a.qualifier_attribute = b.attribute
1336 AND b.query_for = 'ELIG';
1337
1338 CURSOR c_qualifiers(l_grouping_no NUMBER) IS
1339 SELECT a.qualifier_context,
1340 a.qualifier_attribute,
1341 a.qualifier_attr_value,
1342 a.comparison_operator_code,
1343 a.qualifier_id
1344 FROM qp_qualifiers a,
1345 ozf_denorm_queries b
1346 WHERE a.list_header_id = p_list_header_id
1347 AND a.qualifier_grouping_no = l_grouping_no
1348 AND a.qualifier_context = b.context
1349 AND a.qualifier_attribute = b.attribute
1350 AND a.qualifier_context <> 'SOLD_BY'
1351 AND b.query_for = 'ELIG';
1352
1353 CURSOR c_soldby_qualifiers(l_grouping_no NUMBER) IS
1354 SELECT a.qualifier_context,
1355 a.qualifier_attribute,
1356 a.qualifier_attr_value,
1357 a.comparison_operator_code,
1358 a.qualifier_id
1359 FROM qp_qualifiers a,
1360 ozf_denorm_queries b
1361 WHERE a.list_header_id = p_list_header_id
1362 AND a.qualifier_grouping_no = l_grouping_no
1363 AND a.qualifier_context = b.context
1364 AND a.qualifier_attribute = b.attribute
1365 AND a.qualifier_context = 'SOLD_BY'
1366 AND a.qualifier_attribute <> 'QUALIFIER_ATTRIBUTE1'
1367 AND b.query_for = 'ELIG';
1368
1369 CURSOR c_no_lines(l_grouping_no NUMBER) IS
1370 SELECT COUNT(*)
1371 FROM qp_qualifiers a, ozf_denorm_queries b
1372 WHERE a.list_header_id = p_list_header_id
1373 AND a.qualifier_context <> 'SOLD_BY'
1374 AND a.qualifier_grouping_no = l_grouping_no
1375 AND a.qualifier_context = b.context
1376 AND a.qualifier_attribute = b.attribute
1377 AND b.query_for = 'ELIG';
1378
1379 CURSOR c_no_soldby_lines(l_grouping_no NUMBER) IS
1380 SELECT COUNT(*)
1381 FROM qp_qualifiers a, ozf_denorm_queries b
1382 WHERE a.list_header_id = p_list_header_id
1383 AND a.qualifier_context = 'SOLD_BY'
1384 AND a.qualifier_grouping_no = l_grouping_no
1385 AND a.qualifier_context = b.context
1386 AND a.qualifier_attribute = b.attribute
1387 AND a.qualifier_attribute <> 'QUALIFIER_ATTRIBUTE1'
1388 AND b.query_for = 'ELIG';
1389
1390
1391 l_api_name CONSTANT VARCHAR2(30) := 'refresh_volume_parties';
1392 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1393
1394 l_stmt_temp VARCHAR2(32000) := NULL;
1395 l_no_query_flag VARCHAR2(1) := 'N';
1396 l_no_groups NUMBER;
1397 l_no_lines NUMBER;
1398 l_group_index NUMBER;
1399 l_line_index NUMBER;
1400 l_offer_id NUMBER;
1401 l_qual_line_ct NUMBER;
1402 l_direct_indirect_flag VARCHAR2(1) := 'N';
1403 BEGIN
1404
1405 ozf_utility_pvt.write_conc_log(l_full_name ||
1406 ': Start refresh volume parties' ||
1407 '-'||to_char(sysdate,'dd-mon-yyyy-hh:mi:ss'));
1408
1409 x_return_status := FND_API.g_ret_sts_success;
1410
1411 OPEN c_no_groups;
1412 FETCH c_no_groups INTO l_no_groups;
1413 CLOSE c_no_groups;
1414
1415 ozf_utility_pvt.write_conc_log('l_elig_exists:'||l_no_groups);
1416
1417 IF l_no_groups > 0 THEN
1418 l_group_index := 1;
1419
1420 FOR i IN c_groups LOOP
1421 l_line_index := 1;
1422 l_no_lines := 1;
1423
1424 OPEN c_no_lines(i.qualifier_grouping_no);
1425 FETCH c_no_lines INTO l_no_lines;
1426 CLOSE c_no_lines;
1427
1428 l_qual_line_ct := 0;
1429 FND_DSQL.add_text('(');
1430 FOR j IN c_qualifiers(i.qualifier_grouping_no) LOOP
1431 l_stmt_temp := NULL;
1432
1433 l_stmt_temp := get_sql(p_context => j.qualifier_context,
1434 p_attribute => j.qualifier_attribute,
1435 p_attr_value_from => j.qualifier_attr_value,
1436 p_attr_value_to => NULL,--j.qualifier_attr_value_to,
1437 p_comparison => j.comparison_operator_code,
1438 p_type => 'ELIG',
1439 p_qualifier_id => i.qualifier_id,
1440 p_qualifier_group => i.qualifier_grouping_no);
1441 IF l_stmt_temp IS NULL THEN
1442 l_no_query_flag := 'Y';
1443 EXIT;
1444 ELSE
1445 IF l_line_index < l_no_lines THEN
1446 FND_DSQL.add_text(' INTERSECT ');
1447 l_line_index := l_line_index + 1;
1448 END IF;
1449 END IF;
1450 l_qual_line_ct := l_qual_line_ct + 1;
1451 END LOOP;
1452 FND_DSQL.add_text(')');
1453
1454 /*
1455 IF l_qual_line_ct > 0 THEN
1456 FND_DSQL.add_text(' UNION');
1457 END IF;
1458 */
1459 l_line_index := 1;
1460 l_no_lines := 1;
1461
1462 OPEN c_no_soldby_lines(i.qualifier_grouping_no);
1463 FETCH c_no_soldby_lines INTO l_no_lines;
1464 CLOSE c_no_soldby_lines;
1465
1466 IF l_no_lines > 0 then
1467 IF l_qual_line_ct > 0 THEN
1468 FND_DSQL.add_text(' UNION');
1469 END IF;
1470 FND_DSQL.add_text('(');
1471
1472 FOR j IN c_soldby_qualifiers(i.qualifier_grouping_no) LOOP
1473 l_stmt_temp := NULL;
1474
1475 /* No need for this check as the conditions can be put in the cursor */
1476 /*
1477 IF l_qual_line_ct = 0 AND
1478 j.qualifier_context = 'SOLD_BY' AND
1479 j.qualifier_attribute = 'QUALIFIER_ATTRIBUTE1' THEN
1480 l_direct_indirect_flag := 'Y';
1481 END IF;
1482 */
1483 IF l_direct_indirect_flag = 'N' THEN
1484 l_stmt_temp := get_sql(p_context => j.qualifier_context,
1485 p_attribute => j.qualifier_attribute,
1486 p_attr_value_from => j.qualifier_attr_value,
1487 p_attr_value_to => NULL,--j.qualifier_attr_value_to,
1488 p_comparison => j.comparison_operator_code,
1489 p_type => 'ELIG',
1490 p_qualifier_id => i.qualifier_id,
1491 p_qualifier_group => i.qualifier_grouping_no
1492 );
1493 END IF;
1494
1495 IF l_stmt_temp IS NULL THEN
1496 l_no_query_flag := 'Y';
1497 EXIT;
1498 ELSE
1499 IF l_line_index < l_no_lines THEN
1500 FND_DSQL.add_text(' INTERSECT');
1501 l_line_index := l_line_index + 1;
1502 END IF;
1503 END IF;
1504 END LOOP;
1505 FND_DSQL.add_text(')');
1506 END IF;
1507 IF l_group_index < l_no_groups THEN
1508 FND_DSQL.add_text(' UNION ');
1509 l_group_index := l_group_index + 1;
1510 END IF;
1511 END LOOP;
1512 ELSE
1513 FND_DSQL.add_text('(SELECT -1 qp_qualifier_id, -1 qp_qualifier_group, -1 party_id,-1 cust_account_id, -1 cust_acct_site_id, -1 site_use_id,'' '' site_use_code FROM DUAL)');
1514 END IF;
1515
1516 IF p_calling_from_den = 'N' OR l_no_query_flag = 'N' THEN
1517 x_party_stmt := FND_DSQL.get_text(FALSE);
1518 ELSE
1519 x_party_stmt := NULL;
1520 END IF;
1521 --ozf_utility_pvt.write_conc_log('1:'||substr(x_party_stmt,945,250));
1522 --ozf_utility_pvt.write_conc_log('2:'||substr(x_party_stmt,1195,250));
1523 --ozf_utility_pvt.write_conc_log('3:'||substr(x_party_stmt,1445,250));
1524
1525 EXCEPTION
1526 WHEN OTHERS THEN
1527 x_return_status := FND_API.g_ret_sts_unexp_error;
1528
1529 FND_MESSAGE.set_name('OZF', 'OZF_OFFER_PARTY_STMT_FAILED');
1530 FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
1531 FND_MSG_PUB.add;
1532
1533 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1534 THEN
1535 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1536 END IF;
1537
1538 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
1539 p_count => x_msg_count,
1540 p_data => x_msg_data);
1541
1542 END refresh_volume_parties;
1543
1544 ---------------------------------------------------------------------
1545 -- PROCEDURE
1546 -- refresh_parties
1547 --
1548 -- PURPOSE
1549 -- Refreshes offer and party denorm table ozf_activity_customers.
1550 --
1551 -- PARAMETERS
1552 -- p_list_header_id: qp_list_header_id of the offer
1553 --
1554 -- DESCRIPTION
1555 -- This procedure calls get_sql, builds SQL statment for parties and refresh ozf_activity_customers
1556 ---------------------------------------------------------------------
1557 PROCEDURE refresh_parties(
1558 p_api_version IN NUMBER,
1559 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1560 p_commit IN VARCHAR2 := FND_API.g_false,
1561
1562 p_list_header_id IN NUMBER,
1563 p_calling_from_den IN VARCHAR2,
1564
1565 x_return_status OUT NOCOPY VARCHAR2,
1566 x_msg_count OUT NOCOPY NUMBER,
1567 x_msg_data OUT NOCOPY VARCHAR2,
1568 x_party_stmt OUT NOCOPY VARCHAR2,
1569 p_qnum IN NUMBER := NULL
1570 )
1571 IS
1572
1573
1574 /* Changing to join with denorm_queries as only those would be denormed */
1575 CURSOR c_groups IS
1576 SELECT DISTINCT a.qualifier_grouping_no
1577 FROM qp_qualifiers a, ozf_denorm_queries b
1578 WHERE a.list_header_id = p_list_header_id
1579 AND a.qualifier_grouping_no = NVL(p_qnum, a.qualifier_grouping_no)
1580 AND a.list_line_id = -1
1581 AND a.qualifier_context = b.context
1582 AND a.qualifier_attribute = b.attribute
1583 AND b.query_for = 'ELIG';
1584
1585 CURSOR c_qualifiers(l_grouping_no NUMBER) IS
1586 SELECT a.qualifier_context,
1587 a.qualifier_attribute,
1588 a.qualifier_attr_value,
1589 a.qualifier_attr_value_to,
1590 a.comparison_operator_code,
1591 a.qualifier_id
1592 FROM qp_qualifiers a,ozf_denorm_queries b
1593 WHERE a.list_header_id = p_list_header_id
1594 AND a.qualifier_grouping_no = l_grouping_no
1595 AND a.list_line_id = -1 -- dont pick up line level qualifier
1596 AND a.qualifier_context = b.context
1597 AND a.qualifier_attribute = b.attribute
1598 AND b.query_for = 'ELIG';
1599
1600 CURSOR c_offer_type IS
1601 SELECT offer_type
1602 FROM ozf_offers
1603 WHERE qp_list_header_id = p_list_header_id;
1604
1605
1606 CURSOR c_elig_exists IS
1607 SELECT 'Y'
1608 FROM DUAL
1609 WHERE EXISTS (SELECT 1
1610 FROM qp_qualifiers
1611 WHERE list_header_id = p_list_header_id
1612 AND list_line_id = -1
1613 AND (qualifier_context,qualifier_attribute) IN
1614 (SELECT DISTINCT context,attribute
1615 FROM ozf_denorm_queries
1616 WHERE query_for = 'ELIG'
1617 AND active_flag = 'Y'));
1618
1619
1620 CURSOR c_no_groups IS
1621 SELECT COUNT(DISTINCT a.qualifier_grouping_no)
1622 FROM qp_qualifiers a, ozf_denorm_queries b
1623 WHERE a.list_header_id = p_list_header_id
1624 AND a.qualifier_grouping_no = NVL(p_qnum,a.qualifier_grouping_no)
1625 AND a.list_line_id = -1
1626 AND a.qualifier_context = b.context
1627 AND a.qualifier_attribute = b.attribute
1628 AND b.query_for = 'ELIG';
1629
1630
1631 /* Removed the date restriction */
1632 CURSOR c_no_lines(l_grouping_no NUMBER) IS
1633 SELECT COUNT(*)
1634 FROM qp_qualifiers a, ozf_denorm_queries b
1635 WHERE list_header_id = p_list_header_id
1636 AND qualifier_grouping_no = l_grouping_no
1637 AND list_line_id = -1
1638 AND a.qualifier_context = b.context
1639 AND a.qualifier_attribute = b.attribute
1640 AND b.query_for = 'ELIG';
1641
1642
1643 l_api_version CONSTANT NUMBER := 1.0;
1644 l_api_name CONSTANT VARCHAR2(30) := 'refresh_parties';
1645 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1646 l_denormed VARCHAR2(1);
1647
1648 l_stmt_temp VARCHAR2(32000) := NULL;
1649 l_stmt_group VARCHAR2(32000) := NULL;
1650 l_stmt_offer VARCHAR2(32000) := NULL;
1651 l_no_query_flag VARCHAR2(1) := 'N';
1652 l_elig_exists VARCHAR2(1);
1653 l_no_groups NUMBER;
1654 l_no_lines NUMBER;
1655 l_group_index NUMBER;
1656 l_line_index NUMBER;
1657 l_offer_type VARCHAR2(100);
1658
1659
1660 BEGIN
1661
1662 ozf_utility_pvt.write_conc_log(l_full_name || ': Start refresh parties' || '-'||to_char(sysdate,'dd-mon-yyyy-hh:mi:ss'));
1663 --write_log(l_full_name || ': Start refresh parties');
1664
1665 IF FND_API.to_boolean(p_init_msg_list) THEN
1666 FND_MSG_PUB.initialize;
1667 END IF;
1668
1669 IF NOT FND_API.compatible_api_call(l_api_version,
1670 p_api_version,
1671 l_api_name,
1672 g_pkg_name)
1673 THEN
1674 RAISE FND_API.g_exc_unexpected_error;
1675 END IF;
1676
1677 x_return_status := FND_API.g_ret_sts_success;
1678
1679
1680 OPEN c_offer_type;
1681 FETCH c_offer_type INTO l_offer_type;
1682 CLOSE c_offer_type;
1683 ozf_utility_pvt.write_conc_log(' -- Offer Type : '|| l_offer_type );
1684 ozf_utility_pvt.write_conc_log(' -- Offer Id : '|| p_list_header_id );
1685
1686 IF l_offer_type = 'LUMPSUM' OR l_offer_type = 'SCAN_DATA' THEN
1687 refresh_lumpsum_parties(
1688 p_api_version => p_api_version,
1689 p_init_msg_list => p_init_msg_list,
1690 p_commit => p_commit,
1691 p_list_header_id => p_list_header_id,
1692 p_calling_from_den => p_calling_from_den,
1693 x_return_status => x_return_status,
1694 x_msg_count => x_msg_count,
1695 x_msg_data => x_msg_data,
1696 x_party_stmt => x_party_stmt
1697 );
1698 ELSIF l_offer_type = 'NET_ACCRUAL' THEN
1699 refresh_netaccrual_parties(
1700 p_api_version => p_api_version,
1701 p_init_msg_list => p_init_msg_list,
1702 p_commit => p_commit,
1703 p_list_header_id => p_list_header_id,
1704 p_calling_from_den => p_calling_from_den,
1705 x_return_status => x_return_status,
1706 x_msg_count => x_msg_count,
1707 x_msg_data => x_msg_data,
1708 x_party_stmt => x_party_stmt,
1709 p_qnum => p_qnum
1710 );
1711 ELSIF l_offer_type = 'VOLUME_OFFER' THEN
1712 refresh_volume_parties(
1713 p_api_version => p_api_version,
1714 p_init_msg_list => p_init_msg_list,
1715 p_commit => p_commit,
1716 p_list_header_id => p_list_header_id,
1717 p_calling_from_den => p_calling_from_den,
1718 x_return_status => x_return_status,
1719 x_msg_count => x_msg_count,
1720 x_msg_data => x_msg_data,
1721 x_party_stmt => x_party_stmt,
1722 p_qnum => p_qnum
1723 );
1724 ELSE
1725 OPEN c_elig_exists;
1726 FETCH c_elig_exists INTO l_elig_exists;
1727 CLOSE c_elig_exists;
1728
1729 IF l_elig_exists = 'Y' THEN
1730
1731 OPEN c_no_groups;
1732 FETCH c_no_groups INTO l_no_groups;
1733 CLOSE c_no_groups;
1734
1735 l_group_index := 1;
1736
1737 FOR i IN c_groups
1738 LOOP
1739 l_stmt_group := NULL;
1740 l_line_index := 1;
1741
1742 OPEN c_no_lines(i.qualifier_grouping_no);
1743 FETCH c_no_lines INTO l_no_lines;
1744 CLOSE c_no_lines;
1745
1746 FND_DSQL.add_text('(');
1747 FOR j IN c_qualifiers(i.qualifier_grouping_no)
1748 LOOP
1749 l_stmt_temp := NULL;
1750 l_stmt_temp := get_sql(p_context => j.qualifier_context,
1751 p_attribute => j.qualifier_attribute,
1752 p_attr_value_from => j.qualifier_attr_value,
1753 p_attr_value_to => j.qualifier_attr_value_to,
1754 p_comparison => j.comparison_operator_code,
1755 p_type => 'ELIG',
1756 p_qualifier_id => j.qualifier_id,
1757 p_qualifier_group => i.qualifier_grouping_no
1758 );
1759
1760 IF l_stmt_temp IS NULL THEN
1761 l_no_query_flag := 'Y';
1762 EXIT;
1763 ELSE
1764 IF l_line_index < l_no_lines THEN
1765 FND_DSQL.add_text(' INTERSECT ');
1766 l_line_index := l_line_index + 1;
1767 END IF;
1768 END IF;
1769 END LOOP;
1770 FND_DSQL.add_text(')');
1771 /*
1772 IF l_group_index < l_no_groups THEN
1773 FND_DSQL.add_text(' UNION ');
1774 l_group_index := l_group_index + 1;
1775 END IF;
1776 */
1777 END LOOP;
1778 ELSE
1779 FND_DSQL.add_text('(SELECT -1 qp_qualifier_id, -1 qp_qualifier_group, -1 party_id,-1 cust_account_id, -1 cust_acct_site_id, -1 site_use_id,'' '' site_use_code FROM DUAL)');
1780 END IF;
1781 END IF;
1782 IF p_calling_from_den = 'N' OR l_no_query_flag = 'N' THEN
1783 x_party_stmt := FND_DSQL.get_text(FALSE);
1784 ELSE
1785 x_party_stmt := NULL;
1786 END IF;
1787
1788 EXCEPTION
1789 WHEN OTHERS THEN
1790 write_conc_log ('-- Others - ' || SQLERRM || ' ' || x_party_stmt);
1791 x_return_status := FND_API.g_ret_sts_unexp_error;
1792
1793 FND_MESSAGE.set_name('AMS', 'AMS_OFFER_PARTY_STMT_FAILED');
1794 FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
1795 FND_MSG_PUB.add;
1796
1797 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1798 THEN
1799 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1800 END IF;
1801
1802 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
1803 p_count => x_msg_count,
1804 p_data => x_msg_data);
1805
1806 END refresh_parties;
1807
1808
1809
1810 --------------------------------------------------------------------
1811 -- PROCEDURE
1812 -- refresh_lumpsum_products
1813 --
1814 -- PURPOSE
1815 -- Refreshes offer and product denorm table ozf_activity_products
1816 -- for LUMPSUM and SCAN_DATA offers.
1817 --
1818 -- PARAMETERS
1819 -- p_list_header_id: qp_list_header_id of the offer
1820 -- DESCRIPTION
1821 -- This procedure calls get_sql, builds SQL statment for product and refresh ozf_activity_products
1822 ----------------------------------------------------------------------
1823 PROCEDURE refresh_lumpsum_products(
1824 p_api_version IN NUMBER,
1825 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1826 p_commit IN VARCHAR2 := FND_API.g_false,
1827
1828 p_list_header_id IN NUMBER,
1829 p_calling_from_den IN VARCHAR2,
1830
1831 x_return_status OUT NOCOPY VARCHAR2,
1832 x_msg_count OUT NOCOPY NUMBER,
1833 x_msg_data OUT NOCOPY VARCHAR2,
1834
1835 x_product_stmt OUT NOCOPY VARCHAR2,
1836 p_lline_id IN NUMBER := NULL
1837 )
1838 IS
1839
1840 CURSOR c_products(l_used_by_id NUMBER, l_org_id NUMBER) IS
1841 SELECT activity_product_id,
1842 inventory_item_id,
1843 category_id,
1844 level_type_code
1845 FROM ams_act_products
1846 WHERE act_product_used_by_id = l_used_by_id
1847 AND activity_product_id = NVL(p_lline_id, activity_product_id)
1848 AND arc_act_product_used_by = 'OFFR'
1849 AND excluded_flag = 'N'
1850 AND organization_id = l_org_id;
1851
1852 CURSOR c_excluded_products(l_used_by_id NUMBER, l_org_id NUMBER) IS
1853 SELECT inventory_item_id,
1854 category_id,
1855 level_type_code
1856 FROM ams_act_products
1857 WHERE act_product_used_by_id = l_used_by_id
1858 AND arc_act_product_used_by = 'PROD'
1859 AND excluded_flag = 'Y'
1860 AND organization_id = l_org_id;
1861
1862 CURSOR c_no_products(l_used_by_id NUMBER, l_org_id NUMBER) IS
1863 SELECT COUNT(*)
1864 FROM ams_act_products
1865 WHERE act_product_used_by_id = l_used_by_id
1866 AND activity_product_id = NVL(p_lline_id, activity_product_id)
1867 AND arc_act_product_used_by = 'OFFR'
1868 AND excluded_flag = 'N'
1869 AND organization_id = l_org_id;
1870
1871 CURSOR c_no_excl_products(l_used_by_id NUMBER, l_org_id NUMBER) IS
1872 SELECT COUNT(*)
1873 FROM ams_act_products
1874 WHERE act_product_used_by_id = l_used_by_id
1875 AND arc_act_product_used_by = 'PROD'
1876 AND excluded_flag = 'Y'
1877 AND organization_id = l_org_id;
1878
1879 l_api_version CONSTANT NUMBER := 1.0;
1880 l_api_name CONSTANT VARCHAR2(30) := 'refresh_lumpsum_products';
1881 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1882 l_no_query_flag VARCHAR2(1) := 'N';
1883 l_org_id NUMBER ;
1884 l_context VARCHAR2(30);
1885 l_context_attr VARCHAR2(30);
1886 l_prod_attr_val VARCHAR2(240);
1887
1888 l_stmt_temp VARCHAR2(32000) := NULL;
1889 l_stmt_product1 VARCHAR2(32000) := NULL;
1890 l_stmt_product2 VARCHAR2(32000) := NULL;
1891 l_stmt_product VARCHAR2(32000) := NULL;
1892
1893 l_no_products NUMBER;
1894 l_no_excl_products NUMBER;
1895 l_prod_index NUMBER;
1896 l_excl_index NUMBER;
1897
1898 BEGIN
1899
1900 write_conc_log(l_full_name || ': start refresh_products_lumpsum');
1901
1902 IF FND_API.to_boolean(p_init_msg_list) THEN
1903 FND_MSG_PUB.initialize;
1904 END IF;
1905
1906 IF NOT FND_API.compatible_api_call(l_api_version,
1907 p_api_version,
1908 l_api_name,
1909 g_pkg_name)
1910 THEN
1911 RAISE FND_API.g_exc_unexpected_error;
1912 END IF;
1913
1914 x_return_status := FND_API.g_ret_sts_success;
1915 l_org_id := FND_PROFILE.VALUE('QP_ORGANIZATION_ID');
1916 l_context := 'ITEM';
1917
1918 OPEN c_no_products(p_list_header_id, l_org_id);
1919 FETCH c_no_products INTO l_no_products;
1920 CLOSE c_no_products;
1921
1922 l_prod_index := 1;
1923
1924 IF l_no_products > 0 THEN
1925 FOR i IN c_products(p_list_header_id, l_org_id) LOOP
1926 OPEN c_no_excl_products(i.activity_product_id, l_org_id);
1927 FETCH c_no_excl_products INTO l_no_excl_products;
1928 CLOSE c_no_excl_products;
1929
1930 l_excl_index := 1;
1931 l_stmt_temp := null;
1932
1933 IF i.level_type_code = 'PRODUCT' THEN
1934 l_context_attr := 'PRICING_ATTRIBUTE1';
1935 l_prod_attr_val := i.inventory_item_id;
1936 ELSIF i.level_type_code = 'FAMILY' THEN
1937 l_context_attr := 'PRICING_ATTRIBUTE2';
1938 l_prod_attr_val := i.category_id;
1939 END IF;
1940
1941 FND_DSQL.add_text('(');
1942 l_stmt_temp := get_sql(p_context => l_context,
1943 p_attribute => l_context_attr,
1944 p_attr_value_from => l_prod_attr_val,
1945 p_attr_value_to => NULL,
1946 p_comparison => '=',
1947 p_type => 'PROD'
1948 );
1949
1950 IF l_stmt_temp IS NULL THEN
1951 l_no_query_flag := 'Y';
1952 ELSE
1953 IF l_no_excl_products > 0 THEN
1954 EXECUTE IMMEDIATE 'TRUNCATE TABLE ozf_search_selections_t';
1955 FOR j IN c_excluded_products(i.activity_product_id, l_org_id) LOOP
1956 IF j.level_type_code = 'PRODUCT' THEN
1957 l_context_attr := 'PRICING_ATTRIBUTE1';
1958 l_prod_attr_val := j.inventory_item_id;
1959 ELSIF j.level_type_code = 'FAMILY' THEN
1960 l_context_attr := 'PRICING_ATTRIBUTE2';
1961 l_prod_attr_val := j.category_id;
1962 END IF;
1963
1964 insert_excl_prod(p_api_version => p_api_version,
1965 p_init_msg_list => p_init_msg_list,
1966 p_commit => p_commit,
1967 p_context => l_context,
1968 p_attribute => l_context_attr,
1969 p_attr_value => l_prod_attr_val,
1970 x_return_status => x_return_status,
1971 x_msg_count => x_msg_count,
1972 x_msg_data => x_msg_data);
1973 END LOOP;
1974 FND_DSQL.add_text(' MINUS select attribute_value product_id, attribute_id product_type from ozf_search_selections_t ');
1975 END IF;
1976 END IF;
1977
1978 FND_DSQL.add_text(')');
1979
1980 IF l_prod_index < l_no_products THEN
1981 FND_DSQL.add_text(' UNION ');
1982 l_prod_index := l_prod_index + 1;
1983 END IF;
1984 END LOOP;
1985 ELSE
1986 l_no_query_flag := 'Y';
1987 END IF;
1988
1989 IF p_calling_from_den = 'N' OR l_no_query_flag = 'N' THEN
1990 x_product_stmt := FND_DSQL.get_text(FALSE);
1991 ELSE
1992 x_product_stmt := NULL;
1993 END IF;
1994
1995 EXCEPTION
1996
1997 WHEN OTHERS THEN
1998 x_return_status := FND_API.g_ret_sts_unexp_error;
1999
2000 FND_MESSAGE.set_name('AMS', 'AMS_OFFER_PRODUCT_STMT_FAILED');
2001 FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
2002 FND_MSG_PUB.add;
2003
2004 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
2005 THEN
2006 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2007 END IF;
2008
2009 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
2010 p_count => x_msg_count,
2011 p_data => x_msg_data);
2012
2013 END refresh_lumpsum_products;
2014
2015 --------------------------------------------------------------------
2016 -- PROCEDURE
2017 -- refresh_products
2018 --
2019 -- PURPOSE
2020 -- Refreshes offer and product denorm table ozf_activity_products.
2021 --
2022 -- PARAMETERS
2023 -- p_list_header_id: qp_list_header_id of the offer
2024 -- DESCRIPTION
2025 -- This procedure calls get_sql, builds SQL statment for product and refresh ozf_activity_products
2026 ----------------------------------------------------------------------
2027 PROCEDURE refresh_products(
2028 p_api_version IN NUMBER,
2029 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2030 p_commit IN VARCHAR2 := FND_API.g_false,
2031
2032 p_list_header_id IN NUMBER,
2033 p_calling_from_den IN VARCHAR2,
2034
2035 x_return_status OUT NOCOPY VARCHAR2,
2036 x_msg_count OUT NOCOPY NUMBER,
2037 x_msg_data OUT NOCOPY VARCHAR2,
2038
2039 x_product_stmt OUT NOCOPY VARCHAR2,
2040 p_lline_id IN NUMBER := NULL
2041 )
2042 IS
2043
2044 CURSOR c_list_lines IS
2045 SELECT DISTINCT list_line_id
2046 FROM qp_modifier_summary_v
2047 WHERE list_header_id = p_list_header_id
2048 AND list_line_id = NVL(p_lline_id , list_line_id)
2049 AND (end_date_active IS NULL
2050 OR end_date_active >= SYSDATE);
2051
2052 CURSOR c_products(l_list_line_id NUMBER, l_excluder_flag VARCHAR2) IS
2053 SELECT product_attribute_context,
2054 product_attribute,
2055 product_attr_value
2056 FROM qp_pricing_attributes
2057 WHERE list_header_id = p_list_header_id
2058 AND list_line_id = l_list_line_id
2059 AND excluder_flag = l_excluder_flag;
2060
2061
2062 CURSOR c_offer_type IS
2063 SELECT offer_type
2064 FROM ozf_offers
2065 WHERE qp_list_header_id = p_list_header_id;
2066
2067 CURSOR c_na_offer_type IS
2068 SELECT offer_type
2069 FROM ozf_offers
2070 WHERE offer_id = p_list_header_id;
2071
2072 CURSOR c_no_products IS
2073 SELECT COUNT(DISTINCT list_line_id)
2074 FROM qp_modifier_summary_v
2075 WHERE list_header_id = p_list_header_id
2076 and list_line_id = nvl(p_lline_id, list_line_id)
2077 AND (end_date_active IS NULL
2078 OR end_date_active >= SYSDATE);
2079
2080 CURSOR c_no_excl_products(l_list_line_id NUMBER) IS
2081 SELECT COUNT(*)
2082 FROM qp_pricing_attributes
2083 WHERE list_header_id = p_list_header_id
2084 AND list_line_id = l_list_line_id
2085 AND excluder_flag = 'Y';
2086
2087 l_api_version CONSTANT NUMBER := 1.0;
2088 l_api_name CONSTANT VARCHAR2(30) := 'refresh_products';
2089 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2090 l_no_query_flag VARCHAR2(1) := 'N';
2091 l_offer_type VARCHAR2(30);
2092 l_product_attribute_context VARCHAR2(30);
2093 l_product_attribute VARCHAR2(30);
2094 l_product_attr_value VARCHAR2(240);
2095
2096 l_stmt_temp VARCHAR2(32000) := NULL;
2097 l_stmt_product1 VARCHAR2(32000) := NULL;
2098 l_stmt_product2 VARCHAR2(32000) := NULL;
2099 l_stmt_product VARCHAR2(32000) := NULL;
2100
2101 l_no_products NUMBER;
2102 l_no_excl_products NUMBER;
2103 l_prod_index NUMBER;
2104 l_excl_index NUMBER;
2105
2106 BEGIN
2107
2108 ozf_utility_pvt.write_conc_log(l_full_name || ': Start refresh products' || '-'||to_char(sysdate,'dd-mon-yyyy-hh:mi:ss'));
2109 --write_log(l_full_name || ': Start refresh products');
2110
2111 IF FND_API.to_boolean(p_init_msg_list) THEN
2112 FND_MSG_PUB.initialize;
2113 END IF;
2114
2115 IF NOT FND_API.compatible_api_call(l_api_version,
2116 p_api_version,
2117 l_api_name,
2118 g_pkg_name)
2119 THEN
2120 RAISE FND_API.g_exc_unexpected_error;
2121 END IF;
2122
2123 x_return_status := FND_API.g_ret_sts_success;
2124
2125 OPEN c_offer_type;
2126 FETCH c_offer_type INTO l_offer_type;
2127 CLOSE c_offer_type;
2128
2129 ozf_utility_pvt.write_conc_log(' -- Offer Type : '|| l_offer_type );
2130 ozf_utility_pvt.write_conc_log(' -- Offer Id : '|| p_list_header_id );
2131
2132 IF l_offer_type = 'LUMPSUM' OR l_offer_type = 'SCAN_DATA' THEN
2133 refresh_lumpsum_products(
2134 p_api_version => p_api_version,
2135 p_init_msg_list => p_init_msg_list,
2136 p_commit => p_commit,
2137 p_list_header_id => p_list_header_id,
2138 p_calling_from_den => p_calling_from_den,
2139 x_return_status => x_return_status,
2140 x_msg_count => x_msg_count,
2141 x_msg_data => x_msg_data,
2142 x_product_stmt => x_product_stmt,
2143 p_lline_id => p_lline_id
2144 );
2145 ELSIF l_offer_type = 'NET_ACCRUAL' THEN
2146 refresh_netaccrual_products(
2147 p_api_version => p_api_version,
2148 p_init_msg_list => p_init_msg_list,
2149 p_commit => p_commit,
2150 p_list_header_id => p_list_header_id,
2151 p_calling_from_den => p_calling_from_den,
2152 x_return_status => x_return_status,
2153 x_msg_count => x_msg_count,
2154 x_msg_data => x_msg_data,
2155 x_product_stmt => x_product_stmt,
2156 p_lline_id => p_lline_id
2157 );
2158 ELSIF l_offer_type = 'VOLUME_OFFER' THEN
2159 refresh_volume_products(
2160 p_api_version => p_api_version,
2161 p_init_msg_list => p_init_msg_list,
2162 p_commit => p_commit,
2163 p_list_header_id => p_list_header_id,
2164 p_calling_from_den => p_calling_from_den,
2165 x_return_status => x_return_status,
2166 x_msg_count => x_msg_count,
2167 x_msg_data => x_msg_data,
2168 x_product_stmt => x_product_stmt,
2169 p_lline_id => p_lline_id
2170 );
2171 ELSE
2172 OPEN c_no_products;
2173 FETCH c_no_products INTO l_no_products;
2174 CLOSE c_no_products;
2175
2176 l_prod_index := 1;
2177
2178 ozf_utility_pvt.write_conc_log(' -- Number of Products in this offer : '|| l_no_products );
2179
2180 IF l_no_products > 0 THEN
2181 FOR i IN c_list_lines LOOP
2182 l_stmt_temp := null;
2183
2184 OPEN c_no_excl_products(i.list_line_id);
2185 FETCH c_no_excl_products INTO l_no_excl_products;
2186 CLOSE c_no_excl_products;
2187
2188 l_excl_index := 1;
2189
2190 FND_DSQL.add_text('(');
2191 OPEN c_products(i.list_line_id, 'N');
2192 FETCH c_products INTO l_product_attribute_context, l_product_attribute, l_product_attr_value;
2193 CLOSE c_products;
2194 l_stmt_temp := get_sql(p_context => l_product_attribute_context,
2195 p_attribute => l_product_attribute,
2196 p_attr_value_from => l_product_attr_value,
2197 p_attr_value_to => NULL,
2198 p_comparison => NULL,
2199 p_type => 'PROD'
2200 );
2201
2202 ozf_utility_pvt.write_conc_log(' -- Geting Statement for : '|| l_product_attribute || ':' || l_product_attr_value );
2203 --write_log(' -- Geting Statement for : '|| l_product_attribute || ':' || l_product_attr_value );
2204
2205 IF l_stmt_temp IS NULL THEN
2206 l_no_query_flag := 'Y';
2207 ELSE
2208 IF l_no_excl_products > 0 THEN
2209 EXECUTE IMMEDIATE 'TRUNCATE TABLE ozf_search_selections_t';
2210 FOR j IN c_products(i.list_line_id, 'Y') LOOP
2211 insert_excl_prod(p_api_version => p_api_version,
2212 p_init_msg_list => p_init_msg_list,
2213 p_commit => p_commit,
2214 p_context => j.product_attribute_context,
2215 p_attribute => j.product_attribute,
2216 p_attr_value => j.product_attr_value,
2217 x_return_status => x_return_status,
2218 x_msg_count => x_msg_count,
2219 x_msg_data => x_msg_data);
2220 END LOOP;
2221 FND_DSQL.add_text(' MINUS select attribute_value product_id, attribute_id product_type from ozf_search_selections_t ');
2222 END IF;
2223 END IF;
2224
2225 FND_DSQL.add_text(')');
2226
2227 IF l_prod_index < l_no_products THEN
2228 FND_DSQL.add_text(' UNION ');
2229 l_prod_index := l_prod_index + 1;
2230 END IF;
2231 END LOOP;
2232 ELSE
2233 l_no_query_flag := 'Y';
2234 END IF;
2235
2236 IF p_calling_from_den = 'N' OR l_no_query_flag = 'N' THEN
2237 x_product_stmt := FND_DSQL.get_text(FALSE);
2238 ELSE
2239 x_product_stmt := NULL;
2240 END IF;
2241 END IF;
2242 ozf_utility_pvt.write_conc_log(' -- End refresh products --' );
2243 --write_log(' -- End refresh products --' );
2244
2245
2246 EXCEPTION
2247
2248 WHEN OTHERS THEN
2249 ozf_utility_pvt.write_conc_log('-- Others - ' || SQLERRM || ' ' || x_product_stmt);
2250
2251 x_return_status := FND_API.g_ret_sts_unexp_error;
2252
2253 FND_MESSAGE.set_name('AMS', 'AMS_OFFER_PRODUCT_STMT_FAILED');
2254 FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
2255 FND_MSG_PUB.add;
2256
2257 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
2258 THEN
2259 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2260 END IF;
2261
2262 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
2263 p_count => x_msg_count,
2264 p_data => x_msg_data);
2265
2266 END refresh_products;
2267
2268
2269 PROCEDURE refresh_offers(
2270 ERRBUF OUT NOCOPY VARCHAR2,
2271 RETCODE OUT NOCOPY VARCHAR2,
2272 x_return_status OUT NOCOPY VARCHAR2,
2273 p_increment_flag IN VARCHAR2 := 'N',
2274 p_latest_comp_date IN DATE,
2275 p_offer_id IN NUMBER
2276 )
2277 IS
2278
2279 CURSOR c_party_denormed(l_date DATE, l_id NUMBER) IS
2280 SELECT 'Y'
2281 FROM DUAL
2282 WHERE EXISTS (
2283 SELECT 1
2284 FROM ozf_activity_customers
2285 WHERE last_update_date > l_date
2286 AND object_id = l_id and object_class = 'OFFR'
2287 );
2288
2289 CURSOR c_product_denormed(l_date DATE, l_id NUMBER) IS
2290 SELECT 'Y'
2291 FROM DUAL
2292 WHERE EXISTS (
2293 SELECT 1
2294 FROM ozf_activity_products
2295 WHERE last_update_date > l_date
2296 AND object_id = l_id and object_class = 'OFFR'
2297 );
2298
2299 CURSOR c_refreshed_offers(l_date DATE) IS
2300 SELECT distinct object_id offer_id, af.forecast_uom_code, oap.currency_code curr_code
2301 from ozf_activity_products oap,
2302 ozf_act_forecasts_all af
2303 where oap.creation_date > l_date
2304 and oap.object_class = 'OFFR'
2305 and af.act_fcast_used_by_id(+) = oap.object_id
2306 and af.arc_act_fcast_used_by(+) = oap.object_class
2307 and af.freeze_flag(+) = 'Y';
2308
2309 /* All Offers are captured here. Not just active and request_only offers as before */
2310 /* The parent object is captured only for non-reusable offers. The parent would always be a campaign */
2311
2312 CURSOR c_all_offers IS
2313 SELECT o.qp_list_header_id object_id,
2314 o.offer_type object_type,
2315 o.status_code object_status,
2316 'OFFR' object_class,
2317 l.description object_desc,
2318 ao.act_offer_used_by_id parent_id,
2319 ao.arc_act_offer_used_by parent_class,
2320 ct.campaign_name parent_desc,
2321 l.ask_for_flag,
2322 DECODE(o.status_code, 'ACTIVE', 'Y', 'N') active_flag,--l.active_flag,
2323 o.offer_code source_code,
2324 o.activity_media_id,
2325 l.start_date_active start_date,
2326 l.end_date_active end_date,
2327 o.confidential_flag,
2328 o.custom_setup_id,
2329 af.forecast_uom_code,
2330 o.fund_request_curr_code curr_code
2331 FROM ozf_offers o,
2332 qp_list_headers l,
2333 ozf_act_offers ao,
2334 ams_campaigns_vl ct,
2335 ozf_act_forecasts_all af
2336 WHERE o.qp_list_header_id = NVL(p_offer_id,o.qp_list_header_id)
2337 and o.qp_list_header_id = l.list_header_id
2338 and ao.qp_list_header_id(+) = decode(o.reusable,'N', o.qp_list_header_id)
2339 and ao.arc_act_offer_used_by(+) = 'CAMP'
2340 and ao.act_offer_used_by_id = ct.campaign_id(+)
2341 and af.act_fcast_used_by_id(+) = l.list_header_id
2342 and af.arc_act_fcast_used_by(+) = 'OFFR'
2343 and af.freeze_flag(+) = 'Y';
2344
2345 CURSOR c_offer_changed(l_list_header_id NUMBER, l_date DATE) IS
2346 SELECT 'Y'
2347 FROM ozf_offers
2348 WHERE qp_list_header_id = l_list_header_id
2349 AND (last_update_date > l_date OR qualifier_deleted = 'Y');
2350
2351 CURSOR c_qualifier_changed(l_list_header_id NUMBER, l_date DATE) IS
2352 SELECT 'Y'
2353 FROM DUAL
2354 WHERE EXISTS (
2355 SELECT 1
2356 FROM qp_qualifiers
2357 WHERE list_header_id = l_list_header_id
2358 AND (
2359 last_update_date > l_date -- changed qualifiers
2360 OR ( -- changed lists
2361 qualifier_context = 'CUSTOMER_GROUP'
2362 AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE1'
2363 AND qualifier_attr_value IN (
2364 SELECT list_header_id
2365 FROM ams_list_entries
2366 WHERE last_update_date > l_date
2367 )
2368 )
2369 OR ( -- changed segments
2370 qualifier_context = 'CUSTOMER_GROUP'
2371 AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE2'
2372 AND qualifier_attr_value IN (
2373 SELECT ams_party_market_segment_id
2374 FROM ams_party_market_segments
2375 WHERE last_update_date > l_date
2376 )
2377 )
2378 )
2379 );
2380
2381 CURSOR c_product_changed(l_list_header_id NUMBER, l_date DATE) IS
2382 SELECT 'Y'
2383 FROM DUAL
2384 WHERE EXISTS (
2385 SELECT 1
2386 FROM qp_pricing_attributes
2387 WHERE last_update_date > l_date
2388 AND list_header_id = l_list_header_id
2389 );
2390
2391 /*
2392 CURSOR c_incremental_actual_values(l_date DATE)
2393 IS
2394 select distinct adj.list_header_id offer_id,
2395 af.forecast_uom_code
2396 from oe_price_adjustments adj,
2397 oe_order_lines line,
2398 ozf_act_forecasts_all af
2399 where adj.line_id = line.line_id
2400 and line.open_flag = 'N'
2401 and line.cancelled_flag = 'N'
2402 and line.actual_shipment_date > l_date
2403 and af.act_fcast_used_by_id(+) = adj.list_header_id
2404 and af.arc_act_fcast_used_by(+) = 'OFFR'
2405 and af.freeze_flag(+) = 'Y';
2406 */
2407 CURSOR c_incremental_forecast_values(l_date DATE)
2408 IS
2409 select act_fcast_used_by_id offer_id,
2410 forecast_uom_code
2411 from ozf_act_forecasts_all
2412 where last_update_date > l_date
2413 and arc_act_fcast_used_by = 'OFFR'
2414 and freeze_flag(+) = 'Y';
2415
2416 CURSOR c_primary_uom (l_inventory_item_id NUMBER,l_org_id NUMBER)
2417 IS
2418 select primary_uom_code
2419 from MTL_SYSTEM_ITEMS_B
2420 where inventory_item_id = l_inventory_item_id
2421 and organization_id = l_org_id
2422 and enabled_flag = 'Y';
2423
2424 /*
2425 CURSOR c_actual_values ( l_offer_id NUMBER, l_org_id NUMBER)
2426 IS
2427 SELECT 'ITEM' product_attribute_context,
2428 'PRICING_ATTRIBUTE1' product_attribute,
2429 line.inventory_item_id product_attr_value,
2430 sum(NVL(line.shipped_quantity, line.ordered_quantity)) actual_units,
2431 sum(( NVL(line.shipped_quantity, line.ordered_quantity))
2432 * line.unit_list_price) actual_amount,
2433 adj.arithmetic_operator,
2434 adj.operand,
2435 CST_COST_API.get_item_cost(1, line.inventory_item_id, l_org_id, NULL,NULL) cost,
2436 line.order_quantity_uom,
2437 head.transactional_curr_code order_currency,
2438 NVL(line.actual_shipment_date, line.request_date) trans_date
2439 FROM oe_price_adjustments adj,
2440 oe_order_lines_all line,
2441 oe_order_headers_all head
2442 WHERE adj.list_header_id = l_offer_id
2443 AND adj.line_id = line.line_id
2444 AND line.open_flag = 'N'
2445 AND line.cancelled_flag = 'N'
2446 AND line.header_id = head.header_id
2447 group by line.inventory_item_id,
2448 adj.arithmetic_operator,
2449 adj.operand,
2450 CST_COST_API.get_item_cost(1, line.inventory_item_id, l_org_id, NULL,NULL),
2451 line.order_quantity_uom,
2452 head.transactional_curr_code,
2453 NVL(line.actual_shipment_date, line.request_date);
2454 */
2455
2456 CURSOR c_actual_values ( l_offer_id NUMBER, l_org_id NUMBER)
2457 IS
2458 SELECT 'ITEM' product_attribute_context,
2459 'PRICING_ATTRIBUTE1' product_attribute,
2460 line.inventory_item_id product_attr_value,
2461 sum(NVL(line.shipped_quantity, line.ordered_quantity)) actual_units,
2462 sum(( NVL(line.shipped_quantity, line.ordered_quantity))
2463 * line.unit_list_price) actual_amount,
2464 adj.arithmetic_operator,
2465 adj.operand,
2466 --CST_COST_API.get_item_cost(1, line.inventory_item_id, l_org_id, NULL,NULL) cost,
2467 line.order_quantity_uom,
2468 head.transactional_curr_code order_currency,
2469 NVL(line.actual_shipment_date, line.request_date) trans_date
2470 FROM oe_price_adjustments adj,
2471 oe_order_lines_all line,
2472 oe_order_headers_all head
2473 WHERE adj.list_header_id = l_offer_id
2474 AND adj.line_id = line.line_id
2475 AND line.open_flag = 'N'
2476 AND line.cancelled_flag = 'N'
2477 AND line.header_id = head.header_id
2478 group by line.inventory_item_id,
2479 adj.arithmetic_operator,
2480 adj.operand,
2481 --CST_COST_API.get_item_cost(1, line.inventory_item_id, l_org_id, NULL,NULL),
2482 line.order_quantity_uom,
2483 head.transactional_curr_code,
2484 NVL(line.actual_shipment_date, line.request_date);
2485
2486
2487 CURSOR c_forecast_values(l_offer_id NUMBER,l_org_id NUMBER) IS
2488 SELECT fc.price_list_id,
2489 fm.fact_value forecast_units,
2490 fp.product_attribute_context,
2491 fp.product_attribute,
2492 fp.product_attr_value,
2493 fc.forecast_uom_code uom,
2494 CST_COST_API.get_item_cost(1, fp.product_attr_value, l_org_id, NULL,NULL) cost,
2495 ql.arithmetic_operator,
2496 ql.operand,
2497 ao.transaction_currency_code,
2498 ao.fund_request_curr_code transaction_currency_code
2499 FROM
2500 ozf_act_forecasts_all fc,
2501 ozf_act_metric_facts_all fm,
2502 ozf_forecast_dimentions fp,
2503 qp_pricing_attributes qa,
2504 qp_list_lines ql,
2505 ozf_offers ao
2506 WHERE fp.obj_id = l_offer_id
2507 and fp.obj_type = 'OFFR'
2508 and fc.act_fcast_used_by_id = fp.obj_id
2509 and fc.last_scenario_id = (select max(last_scenario_id)
2510 from ozf_act_forecasts_all
2511 where act_fcast_used_by_id = l_offer_id
2512 and freeze_flag = 'Y')
2513 and fm.act_metric_used_by_id = fc.forecast_id
2514 and fm.arc_act_metric_used_by = 'FCST'
2515 and fm.fact_type = 'PRODUCT'
2516 and fm.fact_reference = fp.forecast_dimention_id
2517 and qa.list_header_id = fp.obj_id
2518 and qa.product_attribute_context = fp.product_attribute_context
2519 and qa.product_attribute = fp.product_attribute
2520 and qa.product_attr_value = fp.product_attr_value
2521 and ql.list_line_id = qa.list_line_id
2522 and ql.list_header_id = qa.list_header_id
2523 and ao.qp_list_header_id = fp.obj_id;
2524
2525 CURSOR c_item_cost(l_inv_item_id NUMBER, l_org_id NUMBER)
2526 IS
2527 SELECT CQL.item_cost cost
2528 FROM cst_quantity_layers CQL,
2529 mtl_parameters MP
2530 WHERE CQL.inventory_item_id = l_inv_item_id AND
2531 CQL.organization_id = l_org_id AND
2532 CQL.cost_group_id = MP.default_cost_group_id AND
2533 MP.organization_id = CQL.organization_id;
2534
2535
2536 CURSOR c_list_lines(ll_list_header_id NUMBER) IS
2537 SELECT DISTINCT list_line_id lline_id
2538 FROM qp_modifier_summary_v a, ozf_offers b
2539 WHERE a.list_header_id = ll_list_header_id
2540 AND b.qp_list_header_id = a.list_header_id
2541 AND b.offer_type <> 'VOLUME_OFFER'
2542 AND (a.end_date_active IS NULL
2543 OR a.end_date_active >= SYSDATE)
2544 UNION
2545 SELECT off_discount_product_id lline_id
2546 FROM ozf_offer_discount_products a, ozf_offers b
2547 WHERE b.qp_list_header_id = ll_list_header_id
2548 AND a.offer_id = b.offer_id
2549 and b.offer_type = 'NET_ACCRUAL'
2550 AND a.excluder_flag = 'N'
2551 AND (a.end_date_active IS NULL
2552 OR a.end_date_active >= SYSDATE)
2553 UNION
2554 SELECT activity_product_id lline_id
2555 FROM ams_act_products
2556 WHERE act_product_used_by_id = ll_list_header_id
2557 AND arc_act_product_used_by = 'OFFR'
2558 AND excluded_flag = 'N'
2559 UNION
2560 SELECT distinct offer_discount_line_id lline_id
2561 FROM ozf_offer_discount_lines a, ozf_offers b
2562 WHERE b.qp_list_header_id = ll_list_header_id
2563 AND a.offer_id = b.offer_id
2564 AND b.offer_type = 'VOLUME_OFFER'
2565 and a.tier_type = 'PBH'
2566 AND (a.end_date_active IS NULL
2567 OR a.end_date_active >= SYSDATE)
2568 ;
2569
2570 CURSOR c_no_products(ll_list_header_id NUMBER) IS
2571 SELECT COUNT(DISTINCT list_line_id)
2572 FROM qp_modifier_summary_v
2573 WHERE list_header_id = ll_list_header_id
2574 AND (end_date_active IS NULL
2575 OR end_date_active >= SYSDATE);
2576
2577 CURSOR c_groups(ll_list_header_id NUMBER) IS
2578 select qnum from
2579 (
2580 SELECT DISTINCT a.qualifier_grouping_no qnum
2581 FROM qp_qualifiers a, ozf_denorm_queries b
2582 WHERE a.list_header_id = ll_list_header_id
2583 AND a.list_line_id = -1
2584 AND a.qualifier_context = b.context
2585 AND a.qualifier_attribute = b.attribute
2586 AND b.query_for = 'ELIG'
2587 UNION
2588 SELECT a.qualifier_id qnum
2589 FROM ozf_offer_qualifiers a, ozf_offers b
2590 WHERE b.qp_list_header_id = ll_list_header_id
2591 AND a.offer_id = b.offer_id
2592 AND a.active_flag = 'Y'
2593 UNION
2594 SELECT qualifier_id qnum
2595 FROM ozf_offers
2596 WHERE qp_list_header_id = ll_list_header_id
2597 AND offer_type in ('SCAN_DATA', 'LUMPSUM')
2598 UNION
2599 select -99 qnum
2600 FROM dual
2601 ) order by qnum desc;
2602
2603
2604 CURSOR c_no_groups(ll_list_header_id NUMBER) IS
2605 SELECT COUNT(DISTINCT a.qualifier_grouping_no)
2606 FROM qp_qualifiers a, ozf_denorm_queries b
2607 WHERE a.list_header_id = ll_list_header_id
2608 AND a.list_line_id = -1
2609 AND a.qualifier_context = b.context
2610 AND a.qualifier_attribute = b.attribute
2611 AND b.query_for = 'ELIG'
2612 UNION
2613 SELECT count(a.qualifier_id)
2614 FROM ozf_offer_qualifiers a, ozf_offers b
2615 WHERE b.qp_list_header_id = ll_list_header_id
2616 AND a.offer_id = b.offer_id
2617 AND a.active_flag = 'Y';
2618
2619
2620 CURSOR c_elig_exists(ll_list_header_id NUMBER) IS
2621 SELECT 'Y'
2622 FROM DUAL
2623 WHERE EXISTS (SELECT 1
2624 FROM qp_qualifiers
2625 WHERE list_header_id = ll_list_header_id
2626 AND list_line_id = -1
2627 AND (qualifier_context,qualifier_attribute) IN
2628 (SELECT DISTINCT context,attribute
2629 FROM ozf_denorm_queries
2630 WHERE query_for = 'ELIG'
2631 AND active_flag = 'Y'));
2632
2633
2634 l_api_version CONSTANT NUMBER := 1.0;
2635 l_api_name CONSTANT VARCHAR2(30) := 'refresh_denorm';
2636 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2637 l_return_status VARCHAR2(1);
2638 l_msg_count NUMBER;
2639 l_msg_data VARCHAR2(2000);
2640
2641 l_conc_program_id NUMBER;
2642 l_app_id NUMBER;
2643 l_latest_comp_date DATE;
2644 l_offer_changed VARCHAR2(1);
2645 l_qualifier_changed VARCHAR2(1);
2646 l_product_changed VARCHAR2(1);
2647 l_dummy VARCHAR2(1);
2648 l_index_tablespace VARCHAR2(100);
2649 l_increment_flag VARCHAR2(1);
2650
2651 l_stmt_denorm VARCHAR2(32000) := NULL;
2652 l_stmt_offer VARCHAR2(32000) := NULL;
2653 l_stmt_product VARCHAR2(32000) := NULL;
2654 l_stmt_temp VARCHAR2(254) :=NULL;
2655 l_denorm_csr NUMBER;
2656 l_ignore NUMBER;
2657 l_org_id NUMBER;
2658 l_common_uom VARCHAR2(3);
2659 l_uom_code VARCHAR2(3);
2660 l_primary_uom VARCHAR2(3);
2661
2662 s_forecast_revenue NUMBER;
2663 s_forecast_costs NUMBER;
2664 s_actual_costs NUMBER;
2665 s_actual_revenue NUMBER;
2666 s_actual_units NUMBER;
2667 s_forecast_units NUMBER;
2668 s_actual_ROI NUMBER;
2669 s_forecast_ROI NUMBER;
2670 l_dis_as_exp VARCHAR(1);
2671 sy_actual_revenue NUMBER;
2672 sy_actual_costs NUMBER;
2673 sy_forecast_revenue NUMBER;
2674 sy_forecast_costs NUMBER;
2675 l_qual_group_no NUMBER;
2676 product_denormed VARCHAR2(1) := NULL;
2677 l_group_count NUMBER;
2678 l_conv_actual_revenue NUMBER;
2679 y_conv_actual_revenue NUMBER;
2680 l_conv_actual_costs NUMBER;
2681 y_conv_actual_costs NUMBER;
2682
2683
2684
2685 BEGIN
2686 SAVEPOINT refresh_denorm;
2687 --a := utl_file.fopen( out_dir ,l_out_file,'w' );
2688 ozf_utility_pvt.write_conc_log(l_full_name || ': Start Offer refresh denorm');
2689 --write_log(l_full_name || ': Start Offer refresh denorm');
2690
2691 ERRBUF := NULL;
2692 RETCODE := '0';
2693
2694 -- The following means the default is, incremental refresh.
2695 IF p_increment_flag = 'N' THEN
2696 l_increment_flag := 'N' ;
2697 ELSE
2698 l_increment_flag := 'Y';
2699 END IF;
2700 l_org_id := FND_PROFILE.VALUE('QP_ORGANIZATION_ID');
2701 l_common_uom := FND_PROFILE.VALUE('OZF_TP_COMMON_UOM');
2702 l_dis_as_exp := FND_PROFILE.VALUE('OZF_TREAT_DISCOUNT_AS_EXPENSE');
2703
2704 ozf_utility_pvt.write_conc_log('-- l_increment_flag is : '|| l_increment_flag );
2705 --write_log('-- l_increment_flag is : '|| l_increment_flag );
2706 ozf_utility_pvt.write_conc_log('-- l_org_id is : '|| l_org_id );
2707 ozf_utility_pvt.write_conc_log('-- l_latest_comp_date is : '|| p_latest_comp_date );
2708
2709 IF NOT FND_API.compatible_api_call(l_api_version,
2710 l_api_version,
2711 l_api_name,
2712 g_pkg_name)
2713 THEN
2714 RAISE FND_API.g_exc_unexpected_error;
2715 END IF;
2716
2717 l_latest_comp_date := NVL(p_latest_comp_date, TO_DATE('01/01/1952','MM/DD/YYYY'));
2718 ozf_utility_pvt.write_conc_log('-- Full Refersh Start --');
2719 --write_log('-- Full Refersh Start --');
2720
2721 IF l_increment_flag = 'N' OR l_latest_comp_date IS NULL
2722 THEN
2723 -- first time execution or fresh denorm
2724 -- denorm parties
2725
2726 write_conc_log('-- Deleting Temp and Denorm Tables --');
2727
2728 if p_offer_id is not null then
2729 DELETE FROM ozf_activity_customers
2730 WHERE object_class = 'OFFR'
2731 and object_id = p_offer_id;
2732
2733 DELETE FROM ozf_activity_products
2734 WHERE object_class = 'OFFR'
2735 and object_id = p_offer_id;
2736
2737 DELETE FROM ozf_activity_customers_temp
2738 WHERE object_class = 'OFFR'
2739 and object_id = p_offer_id;
2740
2741 DELETE FROM ozf_activity_products_temp
2742 WHERE object_class = 'OFFR'
2743 and object_id = p_offer_id;
2744 else
2745 DELETE FROM ozf_activity_customers
2746 WHERE object_class = 'OFFR';
2747
2748 DELETE FROM ozf_activity_products
2749 WHERE object_class = 'OFFR';
2750
2751 DELETE FROM ozf_activity_customers_temp
2752 WHERE object_class = 'OFFR';
2753
2754 DELETE FROM ozf_activity_products_temp
2755 WHERE object_class = 'OFFR';
2756 end if;
2757
2758 end if;
2759
2760 FOR i IN c_all_offers
2761 LOOP
2762 ozf_utility_pvt.write_conc_log('-- Processing Offer_id : '||i.object_id);
2763 --write_log('-- Processing Offer_id : '||i.object_id);
2764
2765 IF l_increment_flag = 'N' OR l_latest_comp_date IS NULL
2766 THEN
2767 -- first time execution or fresh denorm
2768 -- denorm parties
2769
2770 ozf_utility_pvt.write_conc_log('-- Deleting Temp and Denorm Tables --');
2771 /*
2772 DELETE FROM ozf_activity_customers_temp
2773 WHERE object_class = 'OFFR'
2774 AND object_id = i.object_id ;
2775
2776 DELETE FROM ozf_activity_products_temp
2777 WHERE object_class = 'OFFR'
2778 AND object_id = i.object_id ;
2779
2780 DELETE FROM ozf_activity_customers
2781 WHERE object_class = 'OFFR'
2782 AND object_id = i.object_id;
2783
2784 DELETE FROM ozf_activity_products
2785 WHERE object_class = 'OFFR'
2786 AND object_id = i.object_id ;
2787 */
2788 IF i.object_status IN ('CANCELLED', 'TERMINATED', 'CLOSED') THEN
2789 GOTO END_INSERT;
2790 END IF;
2791 --=========================================================================--
2792 --============================ denorm parties =============================--
2793 --=========================================================================--
2794 l_group_count :=0;
2795 FOR z IN c_groups(i.object_id)
2796 LOOP
2797 ozf_utility_pvt.write_conc_log('Checking Group:' || z.qnum);
2798
2799 if z.qnum = -99 AND l_group_count > 0 then
2800 goto GROUP_END1;
2801 end if;
2802 FND_DSQL.init;
2803 FND_DSQL.add_text('INSERT INTO ozf_activity_customers_temp(');
2804 FND_DSQL.add_text('creation_date,created_by,last_update_date,last_updated_by,last_update_login,');
2805 FND_DSQL.add_text('confidential_flag,custom_setup_id,');
2806 FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,');
2807 FND_DSQL.add_text('parent_id,parent_class,parent_desc,');
2808 FND_DSQL.add_text('ask_for_flag,active_flag,source_code,currency_code,marketing_medium_id,start_date,end_date,');
2809 -- FND_DSQL.add_text('qp_qualifier_id,qp_qualifier_group,party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code,');
2810 FND_DSQL.add_text('qp_qualifier_id,qp_qualifier_group,party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code,');
2811 FND_DSQL.add_text('qualifier_attribute,qualifier_context) ');
2812
2813 FND_DSQL.add_text('SELECT SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
2814 FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,');
2815 FND_DSQL.add_bind(i.confidential_flag);
2816 FND_DSQL.add_text(',');
2817 FND_DSQL.add_bind(i.custom_setup_id);
2818 FND_DSQL.add_text(',');
2819 FND_DSQL.add_bind(i.object_id);
2820 FND_DSQL.add_text(',');
2821 FND_DSQL.add_bind(i.object_type);
2822 FND_DSQL.add_text(',');
2823 FND_DSQL.add_bind(i.object_status);
2824 FND_DSQL.add_text(',');
2825 FND_DSQL.add_bind(i.object_class);
2826 FND_DSQL.add_text(',');
2827 FND_DSQL.add_bind(i.object_desc);
2828 FND_DSQL.add_text(',');
2829 FND_DSQL.add_bind(i.parent_id);
2830 FND_DSQL.add_text(',');
2831 FND_DSQL.add_bind(i.parent_class);
2832 FND_DSQL.add_text(',');
2833 FND_DSQL.add_bind(i.parent_desc);
2834 FND_DSQL.add_text(',');
2835 FND_DSQL.add_bind(i.ask_for_flag);
2836 FND_DSQL.add_text(',');
2837 FND_DSQL.add_bind(i.active_flag);
2838 FND_DSQL.add_text(',');
2839 FND_DSQL.add_bind(i.source_code);
2840 FND_DSQL.add_text(',');
2841 FND_DSQL.add_bind(i.curr_code);
2842 FND_DSQL.add_text(',');
2843 FND_DSQL.add_bind(i.activity_media_id);
2844 FND_DSQL.add_text(',');
2845 FND_DSQL.add_bind(i.start_date);
2846 FND_DSQL.add_text(',');
2847 FND_DSQL.add_bind(i.end_date);
2848 -- FND_DSQL.add_text(',qp_qualifier_id,qp_qualifier_group,party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code, ');
2849 FND_DSQL.add_text(',qp_qualifier_id,qp_qualifier_group,party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code, ');
2850 FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''QUALIFIER_ATTRIBUTE14'',''SHIP_TO'',''QUALIFIER_ATTRIBUTE11'',substr(site_use_code,INSTR(site_use_code,'':'')+1)) qualifier_attribute,');
2851 FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''CUSTOMER'',''SHIP_TO'',''CUSTOMER'',substr(site_use_code,0,INSTR(site_use_code,'':'')-1)) qualifier_context');
2852 FND_DSQL.add_text(' FROM (');
2853
2854 --ozf_utility_pvt.write_conc_log('Before refresh parties');
2855
2856 /* refresh parties would get all the parties for the list_header_id and add to FND_DSQL*/
2857 refresh_parties(p_api_version => l_api_version,
2858 p_init_msg_list => FND_API.g_false,
2859 p_commit => FND_API.g_false,
2860 p_list_header_id => i.object_id,
2861 p_calling_from_den => 'Y',
2862 x_return_status => l_return_status,
2863 x_msg_count => l_msg_count,
2864 x_msg_data => l_msg_data,
2865 x_party_stmt => l_stmt_offer,
2866 p_qnum => z.qnum);
2867
2868 --write_conc_log('1:' || SUBSTR(l_stmt_offer, 1, 250));
2869 --write_conc_log('2:' || SUBSTR(l_stmt_offer, 251, 250));
2870 --write_conc_log('3:' || SUBSTR(l_stmt_offer, 501, 250));
2871 --write_conc_log('4:' || SUBSTR(l_stmt_offer, 751, 250));
2872 --write_conc_log('5:' || SUBSTR(l_stmt_offer, 1001, 250));
2873 --write_conc_log('6:' || SUBSTR(l_stmt_offer, 1251, 250));
2874 --write_conc_log('7:' || SUBSTR(l_stmt_offer, 1501, 250));
2875
2876 --ozf_utility_pvt.write_conc_log('After refresh parties');
2877 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2878 RAISE FND_API.g_exc_unexpected_error;
2879 END IF;
2880
2881 IF l_stmt_offer IS NOT NULL THEN
2882 FND_DSQL.add_text(' UNION select -1 qp_qualifier_id, -1 qp_qualifier_group,-1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');
2883 -- FND_DSQL.add_text(' UNION select -1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');
2884 FND_DSQL.add_text(' to_number(qualifier_attr_value) site_use_id, ');
2885 FND_DSQL.add_text(' qualifier_context||'':''||qualifier_attribute site_use_code ');
2886 FND_DSQL.add_text(' FROM qp_qualifiers WHERE list_header_id = ');
2887 FND_DSQL.add_bind(i.object_id);
2888 FND_DSQL.add_text(' AND qualifier_grouping_no = ');
2889 FND_DSQL.add_bind(z.qnum);
2890 FND_DSQL.add_text(' and qualifier_context||'':''||qualifier_attribute not in ');
2891 FND_DSQL.add_text(' (''CUSTOMER:QUALIFIER_ATTRIBUTE11'',''CUSTOMER:QUALIFIER_ATTRIBUTE14'')');
2892 FND_DSQL.add_text(' and qualifier_context not in (''MODLIST'',''ORDER'') ');
2893 FND_DSQL.add_text(' and qualifier_attribute < ''A'' ');
2894 FND_DSQL.add_text(')');
2895
2896 l_denorm_csr := DBMS_SQL.open_cursor;
2897 FND_DSQL.set_cursor(l_denorm_csr);
2898 l_stmt_denorm := FND_DSQL.get_text(FALSE);
2899 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
2900 FND_DSQL.do_binds;
2901 l_ignore := DBMS_SQL.execute(l_denorm_csr);
2902 dbms_sql.close_cursor(l_denorm_csr);
2903
2904 UPDATE ozf_offers
2905 SET qualifier_deleted = 'N'
2906 WHERE qp_list_header_id = i.object_id;
2907 END IF;
2908 l_group_count := l_group_count + 1;
2909 << GROUP_END1 >>
2910 write_conc_log('end insert party fresh denorm: ' || z.qnum);
2911
2912 END LOOP;
2913
2914 --=========================================================================================--
2915 --=========================================================================================--
2916 --write_conc_log('Before insert into activity_products_temp');
2917 ---===================== denorm products================================---
2918 --=========================================================================================--
2919 --=========================================================================================--
2920 product_denormed := 'N';
2921 FOR x IN c_list_lines(i.object_id) LOOP
2922 FND_DSQL.init;
2923 FND_DSQL.add_text('INSERT INTO ozf_activity_products_temp(');
2924 FND_DSQL.add_text('creation_date,created_by,last_update_date,last_updated_by,');
2925 FND_DSQL.add_text('last_update_login,confidential_flag,custom_setup_id,');
2926 FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
2927 FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,currency_code,marketing_medium_id,start_date,end_date,');
2928 -- FND_DSQL.add_text('discount_line_id,apply_discount,include_volume,item,item_type) ');
2929 FND_DSQL.add_text('items_category,item,item_type) ');
2930 FND_DSQL.add_text('SELECT SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
2931 FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,');
2932 FND_DSQL.add_bind(i.confidential_flag);
2933 FND_DSQL.add_text(',');
2934 FND_DSQL.add_bind(i.custom_setup_id);
2935 FND_DSQL.add_text(',');
2936 FND_DSQL.add_bind(i.object_id);
2937 FND_DSQL.add_text(',');
2938 FND_DSQL.add_bind(i.object_type);
2939 FND_DSQL.add_text(',');
2940 FND_DSQL.add_bind(i.object_status);
2941 FND_DSQL.add_text(',');
2942 FND_DSQL.add_bind(i.object_class);
2943 FND_DSQL.add_text(',');
2944 FND_DSQL.add_bind(i.object_desc);
2945 FND_DSQL.add_text(',');
2946 FND_DSQL.add_bind(i.parent_id);
2947 FND_DSQL.add_text(',');
2948 FND_DSQL.add_bind(i.parent_class);
2949 FND_DSQL.add_text(',');
2950 FND_DSQL.add_bind(i.parent_desc);
2951 FND_DSQL.add_text(',');
2952 FND_DSQL.add_bind(i.ask_for_flag);
2953 FND_DSQL.add_text(',');
2954 FND_DSQL.add_bind(i.active_flag);
2955 FND_DSQL.add_text(',');
2956 FND_DSQL.add_bind(i.source_code);
2957 FND_DSQL.add_text(',');
2958 FND_DSQL.add_bind(i.curr_code);
2959 FND_DSQL.add_text(',');
2960 FND_DSQL.add_bind(i.activity_media_id);
2961 FND_DSQL.add_text(',');
2962 FND_DSQL.add_bind(i.start_date);
2963 FND_DSQL.add_text(',');
2964 FND_DSQL.add_bind(i.end_date);
2965 -- FND_DSQL.add_text(',discount_line_id,apply_discount,include_volume, product_id, product_type FROM (');
2966 FND_DSQL.add_text(',items_category, product_id, product_type FROM (');
2967
2968 refresh_products(p_api_version => l_api_version,
2969 p_init_msg_list => FND_API.g_false,
2970 p_commit => FND_API.g_false,
2971 p_list_header_id => i.object_id,
2972 p_calling_from_den => 'Y',
2973 x_return_status => l_return_status,
2974 x_msg_count => l_msg_count,
2975 x_msg_data => l_msg_data,
2976 x_product_stmt => l_stmt_product,
2977 p_lline_id => x.lline_id);
2978
2979 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2980 RAISE FND_API.g_exc_unexpected_error;
2981 END IF;
2982
2983 ozf_utility_pvt.write_conc_log ('-- 1 --');
2984
2985 /* the following has to modified to read from ozf_offer_discount_products to handle volume offers */
2986 IF l_stmt_product IS NOT NULL THEN
2987 -- FND_DSQL.add_text(' UNION SELECT distinct discount_line_id,apply_discount,include_volume, to_number(decode(product_attr_value,''ALL'',''-9999'',product_attr_value)) product_id, ');
2988 FND_DSQL.add_text(' UNION ALL SELECT distinct null items_category, to_number(decode(product_attr_value,''ALL'',''-9999'',product_attr_value)) product_id, ');
2989 FND_DSQL.add_text(' product_attribute product_type FROM qp_pricing_attributes ');
2990 FND_DSQL.add_text(' WHERE list_header_id = ');
2991 FND_DSQL.add_bind(i.object_id);
2992 FND_DSQL.add_text(' AND list_line_id = ');
2993 FND_DSQL.add_bind(x.lline_id);
2994 FND_DSQL.add_text(' and product_attribute <> ''PRICING_ATTRIBUTE1'' AND excluder_flag = ''N'')');
2995
2996 l_denorm_csr := DBMS_SQL.open_cursor;
2997 FND_DSQL.set_cursor(l_denorm_csr);
2998 l_stmt_denorm := FND_DSQL.get_text(FALSE);
2999
3000 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
3001 FND_DSQL.do_binds;
3002 l_ignore := DBMS_SQL.execute(l_denorm_csr);
3003 dbms_sql.close_cursor(l_denorm_csr);
3004 END IF;
3005 END LOOP; -- one list line processed
3006
3007 -- Maintain a counter for offers processed and check if any products were denormed, if so, have an if condition to do the forecast.
3008
3009 IF product_denormed = 'Y' then
3010
3011 /*---------- Start forecast update ----------------*/
3012 --ozf_utility_pvt.write_conc_log(' Start forecast');
3013 s_actual_units := 0;
3014 s_actual_revenue := 0;
3015 s_actual_costs := 0;
3016 sy_actual_revenue := 0;
3017 sy_actual_costs := 0;
3018
3019 s_forecast_units := 0;
3020 s_forecast_revenue := 0;
3021 s_forecast_costs := 0;
3022 sy_forecast_revenue := 0;
3023 sy_forecast_costs := 0;
3024
3025 if i.forecast_uom_code is not NULL then
3026 l_uom_code := i.forecast_uom_code;
3027 else
3028 l_uom_code := l_common_uom;
3029 end if;
3030
3031 -- get actual values
3032 --Replace#2
3033 get_actual_values(
3034 l_uom_code,
3035 i.object_id,
3036 l_org_id,
3037 l_dis_as_exp,
3038 i.curr_code,
3039 s_actual_units,
3040 s_actual_revenue,
3041 s_actual_costs,
3042 sy_actual_revenue,
3043 sy_actual_costs,
3044 l_return_status,
3045 l_msg_count,
3046 l_msg_data);
3047
3048 s_actual_roi := 0;
3049 if (l_dis_as_exp = 'N') then
3050 if s_actual_costs <> 0 then
3051 s_actual_roi := (s_actual_revenue - s_actual_costs)/s_actual_costs;
3052 end if;
3053 else
3054 if sy_actual_costs <> 0 then
3055 s_actual_roi := (sy_actual_revenue - sy_actual_costs)/sy_actual_costs;
3056 end if;
3057 end if;
3058
3059
3060 --ozf_utility_pvt.write_conc_log(' Before forecast values loop');
3061 -- Get Forecast Value
3062
3063 --ReplaceForecast#2
3064
3065 get_forecast_values (
3066 i.forecast_uom_code,
3067 i.object_id,
3068 l_org_id,
3069 l_dis_as_exp,
3070 s_forecast_units,
3071 s_forecast_revenue,
3072 s_forecast_costs,
3073 sy_forecast_revenue,
3074 sy_forecast_costs,
3075 l_return_status,
3076 l_msg_count,
3077 l_msg_data);
3078
3079 s_forecast_roi := 0;
3080 if (l_dis_as_exp = 'N') then
3081 if s_forecast_costs <> 0 then
3082 s_forecast_roi := (s_forecast_revenue - s_forecast_costs)/s_forecast_costs;
3083 end if;
3084 else
3085 if sy_forecast_costs <> 0 then
3086 s_forecast_roi := (sy_forecast_revenue - sy_forecast_costs)/sy_forecast_costs;
3087 end if;
3088 end if;
3089
3090 -- update offer forecast and offer values.
3091 update ozf_activity_products_temp
3092 set forecast_units = s_forecast_units,
3093 forecast_revenue = s_forecast_revenue,
3094 forecast_costs = s_forecast_costs,
3095 forecast_roi = s_forecast_roi,
3096 forecast_uom = i.forecast_uom_code,
3097 actual_units = s_actual_units,
3098 actual_revenue = s_actual_revenue,
3099 actual_costs = s_actual_costs,
3100 actual_roi = s_actual_roi,
3101 actual_uom = l_uom_code
3102 where object_id = i.object_id
3103 and object_class = 'OFFR';
3104
3105 -- update offer forecast and offer values.
3106 update ozf_activity_customers_temp
3107 set forecast_units = s_forecast_units,
3108 forecast_revenue = s_forecast_revenue,
3109 forecast_costs = s_forecast_costs,
3110 forecast_roi = s_forecast_roi,
3111 forecast_uom = i.forecast_uom_code,
3112 actual_units = s_actual_units,
3113 actual_revenue = s_actual_revenue,
3114 actual_costs = s_actual_costs,
3115 actual_roi = s_actual_roi,
3116 actual_uom = l_uom_code
3117 where object_id = i.object_id
3118 and object_class = 'OFFR';
3119
3120
3121 /*---------- End forecast update ----------------*/
3122
3123 END IF;
3124 << END_INSERT >>
3125 ozf_utility_pvt.write_conc_log('-- Done for Offer Id : '|| i.object_id );
3126 ELSE --=================== incremental denorm======================
3127
3128 ozf_utility_pvt.write_conc_log('-- Incremental Denorm -- ' || '-'||to_char(sysdate,'dd-mon-yyyy-hh:mi:ss'));
3129 --write_log('-- Incremental Denorm -- ');
3130
3131 -- initialize offer_changed flag
3132 l_offer_changed := NULL;
3133
3134 OPEN c_offer_changed(i.object_id, l_latest_comp_date);
3135 FETCH c_offer_changed INTO l_offer_changed;
3136 CLOSE c_offer_changed;
3137
3138 OPEN c_qualifier_changed(i.object_id, l_latest_comp_date);
3139 FETCH c_qualifier_changed INTO l_qualifier_changed;
3140 CLOSE c_qualifier_changed;
3141
3142 OPEN c_product_changed(i.object_id, l_latest_comp_date);
3143 FETCH c_product_changed INTO l_product_changed;
3144 CLOSE c_product_changed;
3145
3146 ozf_utility_pvt.write_conc_log('-- After Change Check -- ' || '-'||to_char(sysdate,'dd-mon-yyyy-hh:mi:ss'));
3147
3148 IF l_offer_changed IS NOT NULL THEN -- offer changed
3149 --write_log('OCHAN-Offer Id: '|| i.object_id || ' has changed.');
3150 -- parties have to be denormed as associated offers are changed
3151 DELETE FROM ozf_activity_customers -- delete rows that will be refreshed
3152 WHERE object_id = i.object_id and object_class = 'OFFR';
3153
3154 l_group_count := 0;
3155 FOR z IN c_groups(i.object_id)
3156 LOOP
3157 if z.qnum = -99 AND l_group_count > 0 then
3158 goto GROUP_END2;
3159 end if;
3160
3161 FND_DSQL.init;
3162 FND_DSQL.add_text('INSERT INTO ozf_activity_customers(');
3163 FND_DSQL.add_text('activity_customer_id,creation_date,created_by,last_update_date,last_updated_by,');
3164 FND_DSQL.add_text('last_update_login,confidential_flag,custom_setup_id,');
3165 FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
3166 FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,currency_code,marketing_medium_id,start_date,end_date,');
3167 FND_DSQL.add_text('qp_qualifier_id,qp_qualifier_group,party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code,');
3168 -- FND_DSQL.add_text('party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code,');
3169 FND_DSQL.add_text('qualifier_attribute,qualifier_context) ');
3170
3171 FND_DSQL.add_text('SELECT ozf_activity_customers_s.nextval,SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
3172 FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,');
3173 FND_DSQL.add_bind(i.confidential_flag);
3174 FND_DSQL.add_text(',');
3175 FND_DSQL.add_bind(i.custom_setup_id);
3176 FND_DSQL.add_text(',');
3177 FND_DSQL.add_bind(i.object_id);
3178 FND_DSQL.add_text(',');
3179 FND_DSQL.add_bind(i.object_type);
3180 FND_DSQL.add_text(',');
3181 FND_DSQL.add_bind(i.object_status);
3182 FND_DSQL.add_text(',');
3183 FND_DSQL.add_bind(i.object_class);
3184 FND_DSQL.add_text(',');
3185 FND_DSQL.add_bind(i.object_desc);
3186 FND_DSQL.add_text(',');
3187 FND_DSQL.add_bind(i.parent_id);
3188 FND_DSQL.add_text(',');
3189 FND_DSQL.add_bind(i.parent_class);
3190 FND_DSQL.add_text(',');
3191 FND_DSQL.add_bind(i.parent_desc);
3192 FND_DSQL.add_text(',');
3193 FND_DSQL.add_bind(i.ask_for_flag);
3194 FND_DSQL.add_text(',');
3195 FND_DSQL.add_bind(i.active_flag);
3196 FND_DSQL.add_text(',');
3197 FND_DSQL.add_bind(i.source_code);
3198 FND_DSQL.add_text(',');
3199 FND_DSQL.add_bind(i.curr_code);
3200 FND_DSQL.add_text(',');
3201 FND_DSQL.add_bind(i.activity_media_id);
3202 FND_DSQL.add_text(',');
3203 FND_DSQL.add_bind(i.start_date);
3204 FND_DSQL.add_text(',');
3205 FND_DSQL.add_bind(i.end_date);
3206 FND_DSQL.add_text(',qp_qualifier_id,qp_qualifier_group,party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code, ');
3207 -- FND_DSQL.add_text(',party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code, ');
3208 FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''QUALIFIER_ATTRIBUTE14'',''SHIP_TO'',''QUALIFIER_ATTRIBUTE11'',substr(site_use_code,INSTR(site_use_code,'':'')+1)) qualifier_attribute,');
3209 FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''CUSTOMER'',''SHIP_TO'',''CUSTOMER'',substr(site_use_code,0,INSTR(site_use_code,'':'')-1)) qualifier_context');
3210 FND_DSQL.add_text(' FROM (');
3211
3212 refresh_parties(p_api_version => l_api_version,
3213 p_init_msg_list => FND_API.g_false,
3214 p_commit => FND_API.g_false,
3215 p_list_header_id => i.object_id,
3216 p_calling_from_den => 'Y',
3217 x_return_status => l_return_status,
3218 x_msg_count => l_msg_count,
3219 x_msg_data => l_msg_data,
3220 x_party_stmt => l_stmt_offer,
3221 p_qnum => z.qnum);
3222
3223 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3224 RAISE FND_API.g_exc_unexpected_error;
3225 END IF;
3226
3227 ozf_utility_pvt.write_conc_log('l_stmt_offer '||l_stmt_offer);
3228
3229 IF l_stmt_offer IS NOT NULL THEN
3230 --FND_DSQL.add_text(')');
3231 FND_DSQL.add_text(' UNION select -1 qp_qualifier_id,-1 qp_qualifier_group,-1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');
3232 -- FND_DSQL.add_text(' UNION select -1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');
3233 FND_DSQL.add_text(' to_number(qualifier_attr_value) site_use_id, ');
3234 FND_DSQL.add_text(' qualifier_context||'':''||qualifier_attribute site_use_code ');
3235 FND_DSQL.add_text(' FROM qp_qualifiers WHERE list_header_id = ');
3236 FND_DSQL.add_bind(i.object_id);
3237 FND_DSQL.add_text(' AND qualifier_grouping_no = ');
3238 FND_DSQL.add_bind(z.qnum);
3239 FND_DSQL.add_text(' and qualifier_context||'':''||qualifier_attribute not in ');
3240 FND_DSQL.add_text(' (''CUSTOMER:PRICING_ATTRIBUTE11'',''CUSTOMER:QUALIFIER_ATTRIBUTE14'')');
3241 FND_DSQL.add_text(' and qualifier_context not in (''MODLIST'',''ORDER'') ');
3242 FND_DSQL.add_text(' and qualifier_attribute < ''A'' ');
3243 FND_DSQL.add_text(')');
3244
3245 /*
3246 DELETE FROM ozf_activity_customers -- delete rows that will be refreshed
3247 WHERE object_id = i.object_id and object_class = 'OFFR';
3248 */
3249 l_denorm_csr := DBMS_SQL.open_cursor;
3250 FND_DSQL.set_cursor(l_denorm_csr);
3251 l_stmt_denorm := FND_DSQL.get_text(FALSE);
3252 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
3253 FND_DSQL.do_binds;
3254 l_ignore := DBMS_SQL.execute(l_denorm_csr);
3255 dbms_sql.close_cursor(l_denorm_csr);
3256
3257 UPDATE ozf_offers
3258 SET qualifier_deleted = 'N'
3259 WHERE qp_list_header_id = i.object_id;
3260 END IF;
3261 l_group_count := l_group_count + 1;
3262 << GROUP_END2 >>
3263 write_conc_log('end insert party incremental: ' || z.qnum);
3264 END LOOP;
3265 -- products have to be denormed as associated offers are changed
3266 DELETE FROM ozf_activity_products
3267 WHERE object_id = i.object_id and object_class = 'OFFR';
3268
3269 FOR x IN c_list_lines(i.object_id) LOOP
3270 FND_DSQL.init;
3271 FND_DSQL.add_text('INSERT INTO ozf_activity_products(');
3272 FND_DSQL.add_text('activity_product_id,creation_date,created_by,last_update_date,last_updated_by,');
3273 FND_DSQL.add_text('last_update_login,confidential_flag,custom_setup_id,');
3274 FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
3275 FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,currency_code,marketing_medium_id,start_date,end_date,');
3276 -- FND_DSQL.add_text('discount_line_id,apply_discount,include_volume,item,item_type) ');
3277 FND_DSQL.add_text('items_category,item,item_type) ');
3278 FND_DSQL.add_text('SELECT ozf_activity_products_s.nextval,SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
3279 FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,');
3280 FND_DSQL.add_bind(i.confidential_flag);
3281 FND_DSQL.add_text(',');
3282 FND_DSQL.add_bind(i.custom_setup_id);
3283 FND_DSQL.add_text(',');
3284 FND_DSQL.add_bind(i.object_id);
3285 FND_DSQL.add_text(',');
3286 FND_DSQL.add_bind(i.object_type);
3287 FND_DSQL.add_text(',');
3288 FND_DSQL.add_bind(i.object_status);
3289 FND_DSQL.add_text(',');
3290 FND_DSQL.add_bind(i.object_class);
3291 FND_DSQL.add_text(',');
3292 FND_DSQL.add_bind(i.object_desc);
3293 FND_DSQL.add_text(',');
3294 FND_DSQL.add_bind(i.parent_id);
3295 FND_DSQL.add_text(',');
3296 FND_DSQL.add_bind(i.parent_class);
3297 FND_DSQL.add_text(',');
3298 FND_DSQL.add_bind(i.parent_desc);
3299 FND_DSQL.add_text(',');
3300 FND_DSQL.add_bind(i.ask_for_flag);
3301 FND_DSQL.add_text(',');
3302 FND_DSQL.add_bind(i.active_flag);
3303 FND_DSQL.add_text(',');
3304 FND_DSQL.add_bind(i.source_code);
3305 FND_DSQL.add_text(',');
3306 FND_DSQL.add_bind(i.curr_code);
3307 FND_DSQL.add_text(',');
3308 FND_DSQL.add_bind(i.activity_media_id);
3309 FND_DSQL.add_text(',');
3310 FND_DSQL.add_bind(i.start_date);
3311 FND_DSQL.add_text(',');
3312 FND_DSQL.add_bind(i.end_date);
3313 -- FND_DSQL.add_text(',discount_line_id,apply_discount,include_volume, product_id, product_type FROM (');
3314 FND_DSQL.add_text(',items_category, product_id, product_type FROM (');
3315
3316
3317 refresh_products(p_api_version => l_api_version,
3318 p_init_msg_list => FND_API.g_false,
3319 p_commit => FND_API.g_false,
3320 p_list_header_id => i.object_id,
3321 p_calling_from_den => 'Y',
3322 x_return_status => l_return_status,
3323 x_msg_count => l_msg_count,
3324 x_msg_data => l_msg_data,
3325 x_product_stmt => l_stmt_product,
3326 p_lline_id => x.lline_id);
3327
3328 --write_log('After Refresh products- offer has changed');
3329
3330 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3331 RAISE FND_API.g_exc_unexpected_error;
3332 END IF;
3333
3334 --write_log('one');
3335 IF l_stmt_product IS NOT NULL THEN
3336 --FND_DSQL.add_text(')');
3337 --FND_DSQL.add_text(' UNION SELECT distinct discount_line_id,apply_discount,include_volume, to_number(decode(product_attr_value,''ALL'',''-9999'',product_attr_value)) product_id, ');
3338 FND_DSQL.add_text(' UNION ALL SELECT distinct null items_category, to_number(decode(product_attr_value,''ALL'',''-9999'',product_attr_value)) product_id, ');
3339 FND_DSQL.add_text(' product_attribute product_type FROM qp_pricing_attributes ');
3340 FND_DSQL.add_text(' WHERE list_header_id = ');
3341 FND_DSQL.add_bind(x.lline_id);
3342 FND_DSQL.add_text(' and product_attribute <> ''PRICING_ATTRIBUTE1'' AND excluder_flag = ''N'')');
3343
3344 /*
3345 DELETE FROM ozf_activity_products -- delete rows that will be refreshed
3346 WHERE object_id = i.object_id and object_class = 'OFFR';
3347 */
3348
3349 l_denorm_csr := DBMS_SQL.open_cursor;
3350 FND_DSQL.set_cursor(l_denorm_csr);
3351 l_stmt_denorm := FND_DSQL.get_text(FALSE);
3352 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
3353 FND_DSQL.do_binds;
3354 l_ignore := DBMS_SQL.execute(l_denorm_csr);
3355 --write_log('four');
3356 dbms_sql.close_cursor(l_denorm_csr);
3357 END IF;
3358 END LOOP;
3359 ELSE -- offer not changed, denorm party and/or product
3360 --write_log('Offer Id: '|| i.object_id || ' has not changed.');
3361 IF l_qualifier_changed IS NOT NULL THEN
3362 OPEN c_party_denormed(l_latest_comp_date,i.object_id);
3363 FETCH c_party_denormed INTO l_dummy;
3364 CLOSE c_party_denormed;
3365
3366 IF l_dummy IS NULL THEN
3367
3368 DELETE FROM ozf_activity_customers -- delete rows that will be refreshed
3369 WHERE object_id = i.object_id and object_class = 'OFFR';
3370
3371 l_group_count := 0;
3372 FOR z IN c_groups(i.object_id)
3373 LOOP
3374 if z.qnum = -99 AND l_group_count > 0 then
3375 goto GROUP_END3;
3376 end if;
3377 FND_DSQL.init;
3378 FND_DSQL.add_text('INSERT INTO ozf_activity_customers(');
3379 FND_DSQL.add_text('activity_customer_id,creation_date,created_by,last_update_date,last_updated_by,');
3380 FND_DSQL.add_text('last_update_login,confidential_flag,custom_setup_id,');
3381 FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
3382 FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,currency_code,marketing_medium_id,start_date,end_date,');
3383 FND_DSQL.add_text('qp_qualifier_id, qp_qualifier_group,party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code,');
3384 -- FND_DSQL.add_text('party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code,');
3385 FND_DSQL.add_text('qualifier_attribute,qualifier_context) ');
3386 FND_DSQL.add_text('SELECT ozf_activity_customers_s.nextval,SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
3387 FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,');
3388 FND_DSQL.add_bind(i.confidential_flag);
3389 FND_DSQL.add_text(',');
3390 FND_DSQL.add_bind(i.custom_setup_id);
3391 FND_DSQL.add_text(',');
3392 FND_DSQL.add_bind(i.object_id);
3393 FND_DSQL.add_text(',');
3394 FND_DSQL.add_bind(i.object_type);
3395 FND_DSQL.add_text(',');
3396 FND_DSQL.add_bind(i.object_status);
3397 FND_DSQL.add_text(',');
3398 FND_DSQL.add_bind(i.object_class);
3399 FND_DSQL.add_text(',');
3400 FND_DSQL.add_bind(i.object_desc);
3401 FND_DSQL.add_text(',');
3402 FND_DSQL.add_bind(i.parent_id);
3403 FND_DSQL.add_text(',');
3404 FND_DSQL.add_bind(i.parent_class);
3405 FND_DSQL.add_text(',');
3406 FND_DSQL.add_bind(i.parent_desc);
3407 FND_DSQL.add_text(',');
3408 FND_DSQL.add_bind(i.ask_for_flag);
3409 FND_DSQL.add_text(',');
3410 FND_DSQL.add_bind(i.active_flag);
3411 FND_DSQL.add_text(',');
3412 FND_DSQL.add_bind(i.source_code);
3413 FND_DSQL.add_text(',');
3414 FND_DSQL.add_bind(i.curr_code);
3415 FND_DSQL.add_text(',');
3416 FND_DSQL.add_bind(i.activity_media_id);
3417 FND_DSQL.add_text(',');
3418 FND_DSQL.add_bind(i.start_date);
3419 FND_DSQL.add_text(',');
3420 FND_DSQL.add_bind(i.end_date);
3421 FND_DSQL.add_text(',qp_qualifier_id, qp_qualifier_group,party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code, ');
3422 -- FND_DSQL.add_text(',party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code, ');
3423 FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''QUALIFIER_ATTRIBUTE14'',''SHIP_TO'',''QUALIFIER_ATTRIBUTE11'',substr(site_use_code,INSTR(site_use_code,'':'')+1)) qualifier_attribute,');
3424 FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''CUSTOMER'',''SHIP_TO'',''CUSTOMER'',substr(site_use_code,0,INSTR(site_use_code,'':'')-1)) qualifier_context');
3425 FND_DSQL.add_text(' FROM (');
3426 refresh_parties(p_api_version => l_api_version,
3427 p_init_msg_list => FND_API.g_false,
3428 p_commit => FND_API.g_false,
3429 p_list_header_id => i.object_id,
3430 p_calling_from_den => 'Y',
3431 x_return_status => l_return_status,
3432 x_msg_count => l_msg_count,
3433 x_msg_data => l_msg_data,
3434 x_party_stmt => l_stmt_offer,
3435 p_qnum => z.qnum);
3436
3437 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3438 RAISE FND_API.g_exc_unexpected_error;
3439 END IF;
3440
3441 IF l_stmt_offer IS NOT NULL THEN
3442 --FND_DSQL.add_text(')');
3443 FND_DSQL.add_text(' UNION select -1 qp_qualifier_id, -1 qp_qualifier_group,-1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');
3444 -- FND_DSQL.add_text(' UNION select -1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');
3445 FND_DSQL.add_text(' to_number(qualifier_attr_value) site_use_id, ');
3446 FND_DSQL.add_text('qualifier_context||'':''||qualifier_attribute site_use_code ');
3447 FND_DSQL.add_text(' FROM qp_qualifiers WHERE list_header_id = ');
3448 FND_DSQL.add_bind(i.object_id);
3449 FND_DSQL.add_text(' AND qualifier_grouping_no = ');
3450 FND_DSQL.add_bind(z.qnum);
3451 FND_DSQL.add_text(' and qualifier_context||'':''||qualifier_attribute not in ');
3452 FND_DSQL.add_text(' (''CUSTOMER:PRICING_ATTRIBUTE11'',''CUSTOMER:QUALIFIER_ATTRIBUTE14'')');
3453 FND_DSQL.add_text(' and qualifier_context not in (''MODLIST'',''ORDER'') ');
3454 FND_DSQL.add_text(' and qualifier_attribute < ''A'' ');
3455 FND_DSQL.add_text(')');
3456 /*
3457 DELETE FROM ozf_activity_customers -- delete rows that will be refreshed
3458 WHERE object_id = i.object_id and object_class = 'OFFR';
3459 */
3460 l_denorm_csr := DBMS_SQL.open_cursor;
3461 FND_DSQL.set_cursor(l_denorm_csr);
3462 l_stmt_denorm := FND_DSQL.get_text(FALSE);
3463 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
3464 FND_DSQL.do_binds;
3465 l_ignore := DBMS_SQL.execute(l_denorm_csr);
3466 dbms_sql.close_cursor(l_denorm_csr);
3467 END IF;
3468 l_group_count := l_group_count + 1;
3469 << GROUP_END3 >>
3470 write_conc_log('end insert party changed qualifier: ' || z.qnum);
3471 END LOOP;
3472 END IF; -- l_dummy <> 'Y', party not denormed yet
3473 END IF; -- qualifier changed
3474
3475 IF l_product_changed IS NOT NULL THEN
3476 OPEN c_product_denormed(l_latest_comp_date,i.object_id);
3477 FETCH c_product_denormed INTO l_dummy;
3478 CLOSE c_product_denormed;
3479
3480 IF l_dummy IS NULL THEN
3481 DELETE FROM ozf_activity_products -- delete rows that will be refreshed
3482 WHERE object_id = i.object_id and object_class = 'OFFR';
3483
3484 FOR x IN c_list_lines(i.object_id) LOOP
3485
3486 FND_DSQL.init;
3487 FND_DSQL.add_text('INSERT INTO ozf_activity_products(');
3488 FND_DSQL.add_text('activity_product_id,creation_date,created_by,last_update_date,last_updated_by,');
3489 FND_DSQL.add_text('last_update_login,confidential_flag,custom_setup_id,');
3490 FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
3491 FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,currency_code,marketing_medium_id,start_date,end_date,');
3492 -- FND_DSQL.add_text('discount_line_id,apply_discount,include_volume,item,item_type) ');
3493 FND_DSQL.add_text('items_category,item,item_type) ');
3494 FND_DSQL.add_text('SELECT ozf_activity_products_s.nextval,SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
3495 FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,');
3496 FND_DSQL.add_bind(i.confidential_flag);
3497 FND_DSQL.add_text(',');
3498 FND_DSQL.add_bind(i.custom_setup_id);
3499 FND_DSQL.add_text(',');
3500 FND_DSQL.add_bind(i.object_id);
3501 FND_DSQL.add_text(',');
3502 FND_DSQL.add_bind(i.object_type);
3503 FND_DSQL.add_text(',');
3504 FND_DSQL.add_bind(i.object_status);
3505 FND_DSQL.add_text(',');
3506 FND_DSQL.add_bind(i.object_class);
3507 FND_DSQL.add_text(',');
3508 FND_DSQL.add_bind(i.object_desc);
3509 FND_DSQL.add_text(',');
3510 FND_DSQL.add_bind(i.parent_id);
3511 FND_DSQL.add_text(',');
3512 FND_DSQL.add_bind(i.parent_class);
3513 FND_DSQL.add_text(',');
3514 FND_DSQL.add_bind(i.parent_desc);
3515 FND_DSQL.add_text(',');
3516 FND_DSQL.add_bind(i.ask_for_flag);
3517 FND_DSQL.add_text(',');
3518 FND_DSQL.add_bind(i.active_flag);
3519 FND_DSQL.add_text(',');
3520 FND_DSQL.add_bind(i.source_code);
3521 FND_DSQL.add_text(',');
3522 FND_DSQL.add_bind(i.curr_code);
3523 FND_DSQL.add_text(',');
3524 FND_DSQL.add_bind(i.activity_media_id);
3525 FND_DSQL.add_text(',');
3526 FND_DSQL.add_bind(i.start_date);
3527 FND_DSQL.add_text(',');
3528 FND_DSQL.add_bind(i.end_date);
3529 -- FND_DSQL.add_text(',discount_line_id,apply_discount,include_volume, product_id, product_type FROM (');
3530 FND_DSQL.add_text(',items_category, product_id, product_type FROM (');
3531
3532 refresh_products(p_api_version => l_api_version,
3533 p_init_msg_list => FND_API.g_false,
3534 p_commit => FND_API.g_false,
3535 p_list_header_id => i.object_id,
3536 p_calling_from_den => 'Y',
3537 x_return_status => l_return_status,
3538 x_msg_count => l_msg_count,
3539 x_msg_data => l_msg_data,
3540 x_product_stmt => l_stmt_product,
3541 p_lline_id => x.lline_id);
3542
3543 --write_log('After Refresh products- offer has not changed');
3544
3545 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3546 RAISE FND_API.g_exc_unexpected_error;
3547 END IF;
3548
3549 IF l_stmt_product IS NOT NULL THEN
3550 --FND_DSQL.add_text(')');
3551 -- FND_DSQL.add_text(' UNION SELECT distinct discount_line_id,apply_discount,include_volume,to_number(decode(product_attr_value,''ALL'',''-9999'',product_attr_value)) product_id, ');
3552 FND_DSQL.add_text(' UNION ALL SELECT distinct null items_category, to_number(decode(product_attr_value,''ALL'',''-9999'',product_attr_value)) product_id, ');
3553 FND_DSQL.add_text(' product_attribute product_type FROM qp_pricing_attributes ');
3554 FND_DSQL.add_text(' WHERE list_header_id = ');
3555 FND_DSQL.add_bind(i.object_id);
3556 FND_DSQL.add_text(' AND list_line_id = ');
3557 FND_DSQL.add_bind(x.lline_id);
3558 FND_DSQL.add_text(' and product_attribute <> ''PRICING_ATTRIBUTE1'' AND excluder_flag = ''N'')');
3559
3560
3561 DELETE FROM ozf_activity_products -- delete rows that will be refreshed
3562 WHERE object_id = i.object_id and object_class = 'OFFR';
3563
3564 l_denorm_csr := DBMS_SQL.open_cursor;
3565 FND_DSQL.set_cursor(l_denorm_csr);
3566 l_stmt_denorm := FND_DSQL.get_text(FALSE);
3567 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
3568 FND_DSQL.do_binds;
3569 l_ignore := DBMS_SQL.execute(l_denorm_csr);
3570 dbms_sql.close_cursor(l_denorm_csr);
3571 END IF;
3572 END LOOP;
3573 END IF; -- l_dummy <> 'Y', product not denormed yet
3574 END IF; -- product changed
3575 END IF; -- offer changed or qualifier/product changed
3576 --write_log('After Offer Change if condition');
3577 END IF; -- full fresh or incremental denorm
3578 --write_log('After full or incremental denorm condition');
3579 END LOOP; -- all active and request_only offers
3580 --write_log('After looping thru all_offers' );
3581
3582 IF l_increment_flag = 'Y' then
3583 --write_log('In increment equals Y for forecasts' );
3584 --add forecast and actuals for the re-added offer rows.
3585 --write_log('Before c_refreshed_offers loop' );
3586 FOR i IN c_refreshed_offers(l_latest_comp_date) LOOP
3587 s_actual_units := 0;
3588 s_actual_revenue := 0;
3589 s_actual_costs := 0;
3590 sy_actual_revenue := 0;
3591 sy_actual_costs := 0;
3592
3593 s_forecast_units := 0;
3594 s_forecast_revenue := 0;
3595 s_forecast_costs := 0;
3596 sy_forecast_revenue := 0;
3597 sy_forecast_costs := 0;
3598
3599 if i.forecast_uom_code is not null then
3600 l_uom_code := i.forecast_uom_code;
3601 else
3602 l_uom_code := l_common_uom;
3603 end if;
3604
3605 --Replace#1
3606 get_actual_values(
3607 l_uom_code,
3608 i.offer_id,
3609 l_org_id,
3610 l_dis_as_exp,
3611 i.curr_code,
3612 s_actual_units,
3613 s_actual_revenue,
3614 s_actual_costs,
3615 sy_actual_revenue,
3616 sy_actual_costs,
3617 l_return_status,
3618 l_msg_count,
3619 l_msg_data);
3620
3621 s_actual_roi := 0;
3622 if (l_dis_as_exp = 'N') then
3623 if s_actual_costs <> 0 then
3624 s_actual_roi := (s_actual_revenue - s_actual_costs)/s_actual_costs;
3625 end if;
3626 else
3627 if sy_actual_costs <> 0 then
3628 s_actual_roi := (sy_actual_revenue - sy_actual_costs)/sy_actual_costs;
3629 end if;
3630 end if;
3631
3632 --ReplaceForecast#1
3633 get_forecast_values (
3634 i.forecast_uom_code,
3635 i.offer_id,
3636 l_org_id,
3637 l_dis_as_exp,
3638 s_forecast_units,
3639 s_forecast_revenue,
3640 s_forecast_costs,
3641 sy_forecast_revenue,
3642 sy_forecast_costs,
3643 l_return_status,
3644 l_msg_count,
3645 l_msg_data);
3646
3647 s_forecast_roi := 0;
3648 if (l_dis_as_exp = 'N') then
3649 if s_forecast_costs <> 0 then
3650 s_forecast_roi := (s_forecast_revenue - s_forecast_costs)/s_forecast_costs;
3651 end if;
3652 else
3653 if sy_forecast_costs <> 0 then
3654 s_forecast_roi := (sy_forecast_revenue - sy_forecast_costs)/sy_forecast_costs;
3655 end if;
3656 end if;
3657
3658 update ozf_activity_products
3659 set forecast_units = s_forecast_units,
3660 forecast_revenue = s_forecast_revenue,
3661 forecast_costs = s_forecast_costs,
3662 forecast_roi = s_forecast_roi,
3663 forecast_uom = i.forecast_uom_code,
3664 actual_units = s_actual_units,
3665 actual_revenue = s_actual_revenue,
3666 actual_costs = s_actual_costs,
3667 actual_roi = s_actual_roi,
3668 actual_uom = l_uom_code
3669 where object_id = i.offer_id
3670 and object_class = 'OFFR';
3671
3672 update ozf_activity_customers
3673 set forecast_units = s_forecast_units,
3674 forecast_revenue = s_forecast_revenue,
3675 forecast_costs = s_forecast_costs,
3676 forecast_roi = s_forecast_roi,
3677 forecast_uom = i.forecast_uom_code,
3678 actual_units = s_actual_units,
3679 actual_revenue = s_actual_revenue,
3680 actual_costs = s_actual_costs,
3681 actual_roi = s_actual_roi,
3682 actual_uom = l_uom_code
3683 where object_id = i.offer_id
3684 and object_class = 'OFFR';
3685 END LOOP;
3686
3687 --write_log('After c_refreshed_offers loop' );
3688 --write_log('Before c_incremental_forecast_values loop' );
3689 -- update forecast and actuals for those offers not already updated by the earlier line.
3690 FOR i in c_incremental_forecast_values(l_latest_comp_date) LOOP
3691 s_actual_units := 0;
3692 s_actual_revenue := 0;
3693 s_actual_costs := 0;
3694 sy_actual_revenue := 0;
3695 sy_actual_costs := 0;
3696
3697 s_forecast_units := 0;
3698 s_forecast_revenue := 0;
3699 s_forecast_costs := 0;
3700 sy_forecast_revenue := 0;
3701 sy_forecast_costs := 0;
3702
3703
3704 -- ReplaceForecast#3
3705 get_forecast_values (
3706 i.forecast_uom_code,
3707 i.offer_id,
3708 l_org_id,
3709 l_dis_as_exp,
3710 s_forecast_units,
3711 s_forecast_revenue,
3712 s_forecast_costs,
3713 sy_forecast_revenue,
3714 sy_forecast_costs,
3715 l_return_status,
3716 l_msg_count,
3717 l_msg_data);
3718
3719 s_forecast_roi :=0;
3720 if (l_dis_as_exp = 'N') then
3721 if s_forecast_costs <> 0 then
3722 s_forecast_roi := (s_forecast_revenue - s_forecast_costs)/s_forecast_costs;
3723 end if;
3724 else
3725 if sy_forecast_costs <> 0 then
3726 s_forecast_roi := (sy_forecast_revenue - sy_forecast_costs)/sy_forecast_costs;
3727 end if;
3728 end if;
3729
3730 update ozf_activity_products
3731 set forecast_units = s_forecast_units,
3732 forecast_revenue = s_forecast_revenue,
3733 forecast_costs = s_forecast_costs,
3734 forecast_roi = s_forecast_roi,
3735 forecast_uom = i.forecast_uom_code
3736 where object_id = i.offer_id
3737 and object_class = 'OFFR';
3738
3739 update ozf_activity_customers
3740 set forecast_units = s_forecast_units,
3741 forecast_revenue = s_forecast_revenue,
3742 forecast_costs = s_forecast_costs,
3743 forecast_roi = s_forecast_roi,
3744 forecast_uom = i.forecast_uom_code
3745 where object_id = i.offer_id
3746 and object_class = 'OFFR';
3747
3748 END LOOP;
3749
3750 --write_log('After c_incremental_forecast_values loop' );
3751 --write_log('Before c_incremental_actual_values loop' );
3752 /*
3753 FOR i in c_incremental_actual_values(l_latest_comp_date)
3754 LOOP
3755 if i.forecast_uom_code is not null then
3756 l_uom_code := i.forecast_uom_code;
3757 else
3758 l_uom_code := l_common_uom;
3759 end if;
3760
3761 --Replace#3
3762 get_actual_values(
3763 l_uom_code,
3764 i.offer_id,
3765 l_org_id,
3766 l_dis_as_exp,
3767 i.curr_code,
3768 s_actual_units,
3769 s_actual_revenue,
3770 s_actual_costs,
3771 sy_actual_revenue,
3772 sy_actual_costs,
3773 l_return_status,
3774 l_msg_count,
3775 l_msg_data);
3776
3777 s_actual_roi := 0;
3778 if (l_dis_as_exp = 'N') then
3779 if s_actual_costs <> 0 then
3780 s_actual_roi := (s_actual_revenue - s_actual_costs)/s_actual_costs;
3781 end if;
3782 else
3783 if sy_actual_costs <> 0 then
3784 s_actual_roi := (sy_actual_revenue - sy_actual_costs)/sy_actual_costs;
3785 end if;
3786 end if;
3787
3788 update ozf_activity_products
3789 set actual_units = s_actual_units,
3790 actual_revenue = s_actual_revenue,
3791 actual_costs = s_actual_costs,
3792 actual_roi = s_actual_roi,
3793 actual_uom = l_uom_code
3794 where object_id = i.offer_id
3795 and object_class = 'OFFR';
3796
3797 update ozf_activity_customers
3798 set forecast_units = s_forecast_units,
3799 forecast_revenue = s_forecast_revenue,
3800 forecast_costs = s_forecast_costs,
3801 forecast_roi = s_forecast_roi,
3802 forecast_uom = i.forecast_uom_code
3803 where object_id = i.offer_id
3804 and object_class = 'OFFR';
3805
3806 END LOOP;
3807 */
3808 --write_log('After c_incremental_actual_values loop' );
3809 END IF;
3810
3811 /*
3812 DELETE FROM ozf_activity_customers
3813 WHERE object_id IN (
3814 SELECT l.list_header_id
3815 FROM ozf_offers o, qp_list_headers l
3816 WHERE o.status_code IN ('CANCELLED', 'TERMINATED', 'CLOSED')
3817 AND o.qp_list_header_id = l.list_header_id
3818 )
3819 AND object_class = 'OFFR';
3820
3821 DELETE FROM ozf_activity_products
3822 WHERE object_id IN (
3823 SELECT l.list_header_id
3824 FROM ozf_offers o, qp_list_headers l
3825 WHERE o.status_code IN ('CANCELLED', 'TERMINATED', 'CLOSED')
3826 AND o.qp_list_header_id = l.list_header_id
3827 )
3828 AND object_class = 'OFFR';
3829 */
3830
3831 DELETE FROM OZF_ACTIVITY_CUSTOMERS b
3832 WHERE
3833 exists ( SELECT L.LIST_HEADER_ID
3834 FROM OZF_OFFERS O, QP_LIST_HEADERS L
3835 WHERE O.STATUS_CODE IN ('CANCELLED', 'TERMINATED', 'CLOSED') AND
3836 O.QP_LIST_HEADER_ID = L.LIST_HEADER_ID and
3837 b.object_id = l.list_header_id ) AND OBJECT_CLASS = 'OFFR';
3838
3839 DELETE FROM OZF_ACTIVITY_PRODUCTS b
3840 WHERE
3841 exists ( SELECT L.LIST_HEADER_ID
3842 FROM OZF_OFFERS O, QP_LIST_HEADERS L
3843 WHERE O.STATUS_CODE IN ('CANCELLED', 'TERMINATED', 'CLOSED') AND
3844 O.QP_LIST_HEADER_ID = L.LIST_HEADER_ID and
3845 b.object_id = l.list_header_id ) AND OBJECT_CLASS = 'OFFR';
3846
3847
3848 IF l_increment_flag = 'N' THEN
3849 --full denorm, need to truncate table and populate from _temp tables and re-create index
3850 --ozf_utility_pvt.write_conc_log('Before the actual Insert into denorm tables for Full Refresh');
3851 /*
3852 SELECT i.index_tablespace INTO l_index_tablespace
3853 FROM fnd_product_installations i, fnd_application a
3854 WHERE a.application_short_name = 'AMS'
3855 AND a.application_id = i.application_id;
3856 */
3857
3858 ozf_utility_pvt.write_conc_log('-- Populating ozf_activity_customers -- ');
3859
3860 INSERT INTO ozf_activity_customers
3861 (activity_customer_id,OBJECT_ID,
3862 OBJECT_TYPE,
3863 OBJECT_STATUS,
3864 OBJECT_CLASS,
3865 PARENT_ID,
3866 PARENT_CLASS,
3867 PARENT_DESC,
3868 ASK_FOR_FLAG,
3869 ACTIVE_FLAG,
3870 SOURCE_CODE,
3871 CURRENCY_CODE,
3872 MARKETING_MEDIUM_ID,
3873 START_DATE,
3874 END_DATE,
3875 PARTY_ID,
3876 CUST_ACCOUNT_ID,
3877 CUST_ACCT_SITE_ID,
3878 SITE_USE_CODE,
3879 SITE_USE_ID,
3880 QUALIFIER_CONTEXT,
3881 QUALIFIER_ATTRIBUTE,
3882 FORECAST_UNITS,
3883 FORECAST_REVENUE,
3884 FORECAST_COSTS,
3885 FORECAST_ROI,
3886 ACTUAL_UNITS,
3887 ACTUAL_REVENUE,
3888 ACTUAL_COSTS,
3889 ACTUAL_ROI,
3890 CREATION_DATE,
3891 CREATED_BY,
3892 LAST_UPDATED_BY,
3893 LAST_UPDATE_DATE,
3894 LAST_UPDATE_LOGIN,
3895 CONFIDENTIAL_FLAG,
3896 CUSTOM_SETUP_ID,
3897 QP_QUALIFIER_ID,
3898 QP_QUALIFIER_GROUP)
3899 SELECT ozf_activity_customers_s.nextval,OBJECT_ID,
3900 OBJECT_TYPE,
3901 OBJECT_STATUS,
3902 OBJECT_CLASS,
3903 PARENT_ID,
3904 PARENT_CLASS,
3905 PARENT_DESC,
3906 ASK_FOR_FLAG,
3907 ACTIVE_FLAG,
3908 SOURCE_CODE,
3909 CURRENCY_CODE,
3910 MARKETING_MEDIUM_ID,
3911 START_DATE,
3912 END_DATE,
3913 PARTY_ID,
3914 CUST_ACCOUNT_ID,
3915 CUST_ACCT_SITE_ID,
3916 SITE_USE_CODE,
3917 SITE_USE_ID,
3918 QUALIFIER_CONTEXT,
3919 QUALIFIER_ATTRIBUTE,
3920 FORECAST_UNITS,
3921 FORECAST_REVENUE,
3922 FORECAST_COSTS,
3923 FORECAST_ROI,
3924 ACTUAL_UNITS,
3925 ACTUAL_REVENUE,
3926 ACTUAL_COSTS,
3927 ACTUAL_ROI,
3928 CREATION_DATE,
3929 CREATED_BY,
3930 LAST_UPDATED_BY,
3931 LAST_UPDATE_DATE,
3932 LAST_UPDATE_LOGIN,
3933 CONFIDENTIAL_FLAG,
3934 CUSTOM_SETUP_ID,
3935 QP_QUALIFIER_ID,
3936 QP_QUALIFIER_GROUP
3937 FROM ozf_activity_customers_temp;
3938
3939 ozf_utility_pvt.write_conc_log('-- Populating ozf_activity_products -- ');
3940
3941 INSERT INTO ozf_activity_products
3942 (activity_product_id,
3943 OBJECT_ID,
3944 OBJECT_TYPE,
3945 OBJECT_STATUS,
3946 OBJECT_CLASS,
3947 PARENT_ID,
3948 PARENT_CLASS,
3949 PARENT_DESC,
3950 ASK_FOR_FLAG,
3951 ACTIVE_FLAG,
3952 SOURCE_CODE,
3953 CURRENCY_CODE,
3954 MARKETING_MEDIUM_ID,
3955 START_DATE,
3956 END_DATE,
3957 ITEM,
3958 ITEM_TYPE,
3959 FORECAST_UNITS,
3960 FORECAST_REVENUE,
3961 FORECAST_COSTS,
3962 FORECAST_ROI,
3963 ACTUAL_UNITS,
3964 ACTUAL_REVENUE,
3965 ACTUAL_COSTS,
3966 ACTUAL_ROI,
3967 FORECAST_PRODUCT_UNITS,
3968 FORECAST_PRODUCT_REVENUE,
3969 FORECAST_PRODUCT_COSTS,
3970 FORECAST_PRODUCT_ROI,
3971 ACTUAL_PRODUCT_UNITS,
3972 ACTUAL_PRODUCT_REVENUE,
3973 ACTUAL_PRODUCT_COSTS,
3974 ACTUAL_PRODUCT_ROI,
3975 CREATION_DATE,
3976 CREATED_BY,
3977 LAST_UPDATED_BY,
3978 LAST_UPDATE_DATE,
3979 LAST_UPDATE_LOGIN,
3980 CONFIDENTIAL_FLAG,
3981 CUSTOM_SETUP_ID,
3982 FORECAST_UOM,
3983 ACTUAL_UOM,
3984 LIST_PRICE,
3985 DISCOUNT,
3986 ITEMS_CATEGORY)
3987 SELECT ozf_activity_products_s.nextval,
3988 OBJECT_ID,
3989 OBJECT_TYPE,
3990 OBJECT_STATUS,
3991 OBJECT_CLASS,
3992 PARENT_ID,
3993 PARENT_CLASS,
3994 PARENT_DESC,
3995 ASK_FOR_FLAG,
3996 ACTIVE_FLAG,
3997 SOURCE_CODE,
3998 CURRENCY_CODE,
3999 MARKETING_MEDIUM_ID,
4000 START_DATE,
4001 END_DATE,
4002 ITEM,
4003 ITEM_TYPE,
4004 FORECAST_UNITS,
4005 FORECAST_REVENUE,
4006 FORECAST_COSTS,
4007 FORECAST_ROI,
4008 ACTUAL_UNITS,
4009 ACTUAL_REVENUE,
4010 ACTUAL_COSTS,
4011 ACTUAL_ROI,
4012 FORECAST_PRODUCT_UNITS,
4013 FORECAST_PRODUCT_REVENUE,
4014 FORECAST_PRODUCT_COSTS,
4015 FORECAST_PRODUCT_ROI,
4016 ACTUAL_PRODUCT_UNITS,
4017 ACTUAL_PRODUCT_REVENUE,
4018 ACTUAL_PRODUCT_COSTS,
4019 ACTUAL_PRODUCT_ROI,
4020 CREATION_DATE,
4021 CREATED_BY,
4022 LAST_UPDATED_BY,
4023 LAST_UPDATE_DATE,
4024 LAST_UPDATE_LOGIN,
4025 CONFIDENTIAL_FLAG,
4026 CUSTOM_SETUP_ID,
4027 FORECAST_UOM,
4028 ACTUAL_UOM,
4029 LIST_PRICE,
4030 DISCOUNT,
4031 ITEMS_CATEGORY
4032 FROM ozf_activity_products_temp;
4033
4034 END IF;
4035 --utl_file.fflush( a );
4036 --utl_file.fclose( a );
4037
4038 EXCEPTION
4039 WHEN FND_API.G_EXC_ERROR THEN
4040 ozf_utility_pvt.write_conc_log('-- Expected Error - '|| SQLERRM || ' ' || l_stmt_denorm);
4041 x_return_status := FND_API.g_ret_sts_error ;
4042 ERRBUF := l_msg_data;
4043 RETCODE := 2;
4044
4045 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4046 ozf_utility_pvt.write_conc_log('-- Unexpected Error - '|| SQLERRM || ' ' || l_stmt_denorm);
4047 x_return_status := FND_API.g_ret_sts_unexp_error ;
4048 ERRBUF := l_msg_data;
4049 RETCODE := 2;
4050
4051 WHEN OTHERS THEN
4052 ROLLBACK TO refresh_denorm;
4053 ozf_utility_pvt.write_conc_log('-- Others - '|| SQLERRM || ' ' || l_stmt_denorm);
4054 x_return_status := FND_API.g_ret_sts_unexp_error ;
4055 ERRBUF := SQLERRM || ' ' || l_stmt_denorm;
4056 RETCODE := sqlcode;
4057
4058
4059 END refresh_offers;
4060
4061
4062 PROCEDURE get_actual_values(
4063 p_uom_code IN VARCHAR2,
4064 p_offer_id IN NUMBER,
4065 p_org_id IN NUMBER,
4066 p_dis_as_exp IN VARCHAR2,
4067 p_curr_code IN VARCHAR2,
4068 x_actual_units OUT NOCOPY NUMBER,
4069 x_actual_revenue OUT NOCOPY NUMBER,
4070 x_actual_costs OUT NOCOPY NUMBER,
4071 xy_actual_revenue OUT NOCOPY NUMBER,
4072 xy_actual_costs OUT NOCOPY NUMBER,
4073 x_return_status OUT NOCOPY VARCHAR2,
4074 x_msg_count OUT NOCOPY NUMBER,
4075 x_msg_data OUT NOCOPY VARCHAR2
4076 )
4077 IS
4078 CURSOR c_actual_values ( l_offer_id NUMBER, l_org_id NUMBER)
4079 IS
4080 SELECT 'ITEM' product_attribute_context,
4081 'PRICING_ATTRIBUTE1' product_attribute,
4082 line.inventory_item_id product_attr_value,
4083 sum(NVL(line.shipped_quantity, line.ordered_quantity)) actual_units,
4084 sum(( NVL(line.shipped_quantity, line.ordered_quantity))* line.unit_list_price) actual_amount,
4085 adj.arithmetic_operator,
4086 adj.operand,
4087 CST_COST_API.get_item_cost(1, line.inventory_item_id, l_org_id, NULL,NULL) cost,
4088 line.order_quantity_uom,
4089 head.transactional_curr_code order_currency,
4090 NVL(line.actual_shipment_date,line.request_date) trans_date
4091 FROM oe_price_adjustments adj,
4092 oe_order_lines_all line,
4093 oe_order_headers_all head
4094 WHERE adj.list_header_id = l_offer_id
4095 AND adj.line_id = line.line_id
4096 AND line.open_flag = 'N'
4097 AND line.cancelled_flag = 'N'
4098 AND line.header_id = head.header_id
4099 GROUP BY line.inventory_item_id,
4100 adj.arithmetic_operator,
4101 adj.operand,
4102 CST_COST_API.get_item_cost(1, line.inventory_item_id, l_org_id, NULL,NULL),
4103 line.order_quantity_uom,
4104 head.transactional_curr_code,
4105 NVL(line.actual_shipment_date,line.request_date);
4106
4107 CURSOR c_primary_uom (l_inventory_item_id NUMBER,l_org_id NUMBER)
4108 IS
4109 select primary_uom_code
4110 from MTL_SYSTEM_ITEMS_B
4111 where inventory_item_id = l_inventory_item_id
4112 and organization_id = l_org_id
4113 and enabled_flag = 'Y';
4114
4115
4116 CURSOR c_item_cost(l_inv_item_id NUMBER, l_org_id NUMBER)
4117 IS
4118 SELECT CQL.item_cost cost
4119 FROM cst_quantity_layers CQL,
4120 mtl_parameters MP
4121 WHERE CQL.inventory_item_id = l_inv_item_id AND
4122 CQL.organization_id = l_org_id AND
4123 CQL.cost_group_id = MP.default_cost_group_id AND
4124 MP.organization_id = CQL.organization_id;
4125
4126 l_primary_uom VARCHAR2(3);
4127 l_fc_conv_factor NUMBER;
4128 l_comm_conv_factor NUMBER;
4129 t_actual_units NUMBER;
4130 t_conv_actual_units NUMBER;
4131 t_actual_amount NUMBER;
4132 t_operand NUMBER;
4133 t_cost NUMBER;
4134 t_conv_cost NUMBER;
4135 t_arithmetic_operator VARCHAR2(30);
4136 l_discount NUMBER;
4137 l_actual_revenue NUMBER;
4138 l_actual_costs NUMBER;
4139 l_actual_roi NUMBER;
4140 y_actual_revenue NUMBER;
4141 y_actual_costs NUMBER;
4142 l_return_status VARCHAR2(1);
4143 l_conv_actual_revenue NUMBER;
4144 y_conv_actual_revenue NUMBER;
4145 l_conv_actual_costs NUMBER;
4146 y_conv_actual_costs NUMBER;
4147
4148 BEGIN
4149 /* Initializing the offer level value */
4150 x_actual_units := 0;
4151 x_actual_revenue := 0;
4152 x_actual_costs := 0;
4153 xy_actual_revenue := 0;
4154 xy_actual_costs := 0;
4155
4156 FOR j IN c_actual_values(p_offer_id,p_org_id)
4157 LOOP
4158 l_fc_conv_factor := inv_convert.inv_um_convert(j.product_attr_value,
4159 NULL,
4160 1,
4161 j.order_quantity_uom,
4162 p_uom_code,
4163 NULL,
4164 NULL);
4165
4166 OPEN c_primary_uom(j.product_attr_value, p_org_id);
4167 FETCH c_primary_uom INTO l_primary_uom;
4168 CLOSE c_primary_uom;
4169
4170 l_comm_conv_factor := inv_convert.inv_um_convert(j.product_attr_value,
4171 NULL,
4172 1,
4173 j.order_quantity_uom,
4174 l_primary_uom,
4175 NULL,
4176 NULL);
4177
4178
4179 t_actual_units := nvl(j.actual_units,0);
4180 t_conv_actual_units := nvl(j.actual_units,0)*l_fc_conv_factor;
4181 t_actual_amount := nvl(j.actual_amount,0);
4182 t_operand := nvl(j.operand,0);
4183 t_arithmetic_operator := j.arithmetic_operator;
4184 --t_cost := nvl(j.cost,0);
4185
4186 OPEN c_item_cost(j.product_attr_value, p_org_id);
4187 FETCH c_item_cost into t_cost;
4188 CLOSE c_item_cost;
4189
4190 if t_cost is not null then
4191 if l_comm_conv_factor < 0 then
4192 t_conv_cost := t_cost;
4193 else
4194 t_conv_cost := t_cost/l_comm_conv_factor;
4195 end if;
4196 else
4197 t_conv_cost := 0;
4198 end if;
4199
4200 If t_arithmetic_operator is not NULL then
4201 If t_arithmetic_operator = 'AMT' then
4202 l_discount := t_actual_units * t_operand;
4203 elsif t_arithmetic_operator = '%' then
4204 l_discount := (t_actual_amount * t_operand)/100;
4205 end if;
4206 end if;
4207
4208 l_actual_revenue := t_actual_amount - l_discount;
4209 l_actual_costs := t_actual_units * t_conv_cost;
4210
4211 if (p_dis_as_exp = 'N') then
4212 if l_actual_costs <> 0 then
4213 l_actual_roi := (l_actual_revenue - l_actual_costs)/l_actual_costs;
4214 end if;
4215 else
4216 y_actual_revenue := t_actual_amount;
4217 y_actual_costs := t_actual_units * (t_conv_cost + l_discount);
4218 if (y_actual_costs <> 0) then
4219 l_actual_roi := (y_actual_revenue - y_actual_costs)/y_actual_costs;
4220 end if;
4221 end if;
4222
4223
4224
4225 if p_curr_code <> j.order_currency then
4226 ozf_utility_pvt.convert_currency(x_return_status => l_return_status
4227 ,p_from_currency => j.order_currency
4228 ,p_to_currency => p_curr_code
4229 ,p_conv_date => j.trans_date
4230 ,p_from_amount => l_actual_revenue
4231 ,x_to_amount => l_conv_actual_revenue);
4232
4233 ozf_utility_pvt.convert_currency(x_return_status => l_return_status
4234 ,p_from_currency => j.order_currency
4235 ,p_to_currency => p_curr_code
4236 ,p_conv_date => j.trans_date
4237 ,p_from_amount => y_actual_revenue
4238 ,x_to_amount => y_conv_actual_revenue);
4239
4240 ozf_utility_pvt.convert_currency(x_return_status => l_return_status
4241 ,p_from_currency => j.order_currency
4242 ,p_to_currency => p_curr_code
4243 ,p_conv_date => j.trans_date
4244 ,p_from_amount => l_actual_costs
4245 ,x_to_amount => l_conv_actual_costs);
4246
4247 ozf_utility_pvt.convert_currency(x_return_status => l_return_status
4248 ,p_from_currency => j.order_currency
4249 ,p_to_currency => p_curr_code
4250 ,p_conv_date => j.trans_date
4251 ,p_from_amount => y_actual_costs
4252 ,x_to_amount => y_conv_actual_costs);
4253 if l_conv_actual_revenue is not null then
4254 l_actual_revenue := l_conv_actual_revenue;
4255 end if;
4256
4257 if y_conv_actual_revenue is not null then
4258 y_actual_revenue := y_conv_actual_revenue;
4259 end if;
4260
4261 if l_conv_actual_costs is not null then
4262 l_actual_costs := l_conv_actual_costs;
4263 end if;
4264
4265 if y_conv_actual_costs is not null then
4266 y_actual_costs := y_conv_actual_costs;
4267 end if;
4268
4269 end if;
4270
4271
4272 x_actual_units := x_actual_units + t_conv_actual_units;
4273 x_actual_revenue := x_actual_revenue + l_actual_revenue;
4274 x_actual_costs := x_actual_costs + l_actual_costs;
4275 xy_actual_revenue := xy_actual_revenue + y_actual_revenue;
4276 xy_actual_costs := xy_actual_costs + y_actual_costs;
4277
4278 -- update the actual values for the offer/product
4279 update ozf_activity_products_temp
4280 set actual_product_units = t_conv_actual_units,
4281 actual_product_revenue = l_actual_revenue,
4282 actual_product_costs = l_actual_costs,
4283 actual_product_roi = l_actual_roi,
4284 actual_uom = p_uom_code,
4285 discount = l_discount
4286 where object_id = p_offer_id
4287 and object_class = 'OFFR'
4288 and item = j.product_attr_value
4289 and item_type = j.product_attribute;
4290 END LOOP;
4291
4292 END;
4293
4294 PROCEDURE get_forecast_values (
4295 p_forecast_uom_code IN VARCHAR2,
4296 p_offer_id IN NUMBER,
4297 p_org_id IN NUMBER,
4298 p_dis_as_exp IN VARCHAR2,
4299 x_forecast_units OUT NOCOPY NUMBER,
4300 x_forecast_revenue OUT NOCOPY NUMBER,
4301 x_forecast_costs OUT NOCOPY NUMBER,
4302 xy_forecast_revenue OUT NOCOPY NUMBER,
4303 xy_forecast_costs OUT NOCOPY NUMBER,
4304 x_return_status OUT NOCOPY VARCHAR2,
4305 x_msg_count OUT NOCOPY NUMBER,
4306 x_msg_data OUT NOCOPY VARCHAR2
4307 )
4308 IS
4309
4310 CURSOR c_forecast_values(l_offer_id NUMBER,l_org_id NUMBER) IS
4311 SELECT fc.price_list_id,
4312 fm.fact_value forecast_units,
4313 fp.product_attribute_context,
4314 fp.product_attribute,
4315 fp.product_attr_value,
4316 fc.forecast_uom_code uom,
4317 CST_COST_API.get_item_cost(1, fp.product_attr_value, l_org_id, NULL,NULL) cost,
4318 ql.arithmetic_operator,
4319 ql.operand,
4320 ao.transaction_currency_code,
4321 fc.forecast_id
4322 FROM
4323 ozf_act_forecasts_all fc,
4324 ozf_act_metric_facts_all fm,
4325 ozf_forecast_dimentions fp,
4326 qp_pricing_attributes qa,
4327 qp_list_lines ql,
4328 ozf_offers ao
4329 WHERE fp.obj_id = l_offer_id
4330 and fp.obj_type = 'OFFR'
4331 and fc.act_fcast_used_by_id = fp.obj_id
4332 and fc.last_scenario_id = (select max(last_scenario_id)
4333 from ozf_act_forecasts_all
4334 where act_fcast_used_by_id = l_offer_id
4335 and freeze_flag = 'Y')
4336 and fm.act_metric_used_by_id = fc.forecast_id
4337 and fm.arc_act_metric_used_by = 'FCST'
4338 and fm.fact_type = 'PRODUCT'
4339 and fm.fact_reference = fp.forecast_dimention_id
4340 and qa.list_header_id = fp.obj_id
4341 and qa.product_attribute_context = fp.product_attribute_context
4342 and qa.product_attribute = fp.product_attribute
4343 and qa.product_attr_value = fp.product_attr_value
4344 and ql.list_line_id = qa.list_line_id
4345 and ql.list_header_id = qa.list_header_id
4346 and ao.qp_list_header_id = fp.obj_id;
4347
4348 CURSOR c_primary_uom (l_inventory_item_id NUMBER,l_org_id NUMBER)
4349 IS
4350 select primary_uom_code
4351 from MTL_SYSTEM_ITEMS_B
4352 where inventory_item_id = l_inventory_item_id
4353 and organization_id = l_org_id
4354 and enabled_flag = 'Y';
4355
4356 l_list_price NUMBER;
4357 l_discount NUMBER;
4358 l_primary_uom VARCHAR2(3);
4359 l_comm_conv_factor NUMBER;
4360 l_selling_price NUMBER;
4361 l_forecast_revenue NUMBER;
4362 l_forecast_costs NUMBER;
4363 l_forecast_roi NUMBER;
4364 y_discount NUMBER;
4365 y_forecast_revenue NUMBER;
4366 y_forecast_costs NUMBER;
4367
4368 t_forecast_units NUMBER;
4369 t_operand NUMBER;
4370 t_cost NUMBER;
4371 t_conv_cost NUMBER;
4372 t_arithmetic_operator VARCHAR2(30);
4373 l_return_status VARCHAR2(1);
4374 l_msg_count NUMBER;
4375 l_msg_data VARCHAR2(2000);
4376 l_api_version CONSTANT NUMBER := 1.0;
4377 BEGIN
4378 /* Initializing the offer level value */
4379 x_forecast_units := 0;
4380 x_forecast_revenue := 0;
4381 x_forecast_costs := 0;
4382 xy_forecast_revenue := 0;
4383 xy_forecast_costs := 0;
4384
4385 FOR j IN c_forecast_values(p_offer_id,p_org_id)
4386 LOOP
4387 OPEN c_primary_uom(j.product_attr_value, p_org_id);
4388 FETCH c_primary_uom INTO l_primary_uom;
4389 CLOSE c_primary_uom;
4390
4391 l_comm_conv_factor := inv_convert.inv_um_convert(j.product_attr_value,
4392 NULL,
4393 1,
4394 j.uom,
4395 l_primary_uom,
4396 NULL,
4397 NULL);
4398
4399 t_forecast_units := nvl(j.forecast_units,0);
4400 t_operand := nvl(j.operand,0);
4401 t_cost := nvl(j.cost,0);
4402 t_arithmetic_operator := j.arithmetic_operator;
4403 l_list_price := 0;
4404 l_discount := 0;
4405 if j.cost is not null then
4406 if l_comm_conv_factor < 0 then
4407 t_conv_cost := j.cost;
4408 else
4409 t_conv_cost := j.cost/l_comm_conv_factor;
4410 end if;
4411 else
4412 t_conv_cost := 0;
4413 end if;
4414
4415 ozf_forecast_util_pvt.get_list_price(
4416 p_api_version => l_api_version,
4417 p_init_msg_list => FND_API.g_false,
4418 p_commit => FND_API.g_false,
4419 p_obj_type => 'OFFR',
4420 p_obj_id => p_offer_id,
4421 p_forecast_id => j.forecast_id,
4422 p_product_attribute => j.product_attribute,
4423 p_product_attr_value => j.product_attr_value,
4424 p_fcst_uom => j.uom,
4425 p_currency_code => j.transaction_currency_code,
4426 p_price_list_id => j.price_list_id,
4427 x_list_price => l_list_price,
4428 x_return_status => l_return_status,
4429 x_msg_count => l_msg_count,
4430 x_msg_data => l_msg_data
4431 );
4432 If t_arithmetic_operator is not NULL then
4433 if t_arithmetic_operator = '%' then
4434 l_selling_price := (l_list_price - (l_list_price*t_operand)/100);
4435 l_discount := (l_list_price*t_operand)/100;
4436 elsif t_arithmetic_operator = 'AMT' then
4437 l_selling_price := l_list_price - t_operand;
4438 l_discount := t_operand;
4439 end if;
4440 end if;
4441
4442 l_forecast_revenue := t_forecast_units * l_selling_price;
4443 l_forecast_costs := t_forecast_units * t_conv_cost;
4444 if (p_dis_as_exp = 'N') then
4445 if l_forecast_costs <> 0 then
4446 l_forecast_roi := (l_forecast_revenue - l_forecast_costs)/l_forecast_costs;
4447 end if;
4448 else
4449 If t_arithmetic_operator is not NULL then
4450 if t_arithmetic_operator = '%' then
4451 y_discount := (l_list_price*t_operand)/100;
4452 elsif t_arithmetic_operator = 'AMT' then
4453 y_discount := t_operand;
4454 end if;
4455 end if;
4456 y_forecast_revenue := t_forecast_units * l_list_price;
4457 y_forecast_costs := t_forecast_units * (t_conv_cost+y_discount);
4458 if y_forecast_costs <> 0 then
4459 l_forecast_roi := (y_forecast_revenue - y_forecast_costs)/y_forecast_costs;
4460 end if;
4461 end if;
4462
4463 x_forecast_units := x_forecast_units + t_forecast_units;
4464 x_forecast_revenue := x_forecast_revenue + l_forecast_revenue;
4465 x_forecast_costs := x_forecast_costs + l_forecast_costs;
4466 xy_forecast_revenue := xy_forecast_revenue + y_forecast_revenue;
4467 xy_forecast_costs := xy_forecast_costs + y_forecast_costs;
4468
4469 -- update the forecast values for the offer/product
4470 update ozf_activity_products_temp
4471 set forecast_product_units = t_forecast_units,
4472 forecast_product_revenue = l_forecast_revenue,
4473 forecast_product_costs = l_forecast_costs,
4474 forecast_product_roi = l_forecast_roi,
4475 forecast_uom = p_forecast_uom_code,
4476 list_price = l_list_price,
4477 discount = l_discount
4478 where object_id = p_offer_id
4479 and object_class = 'OFFR'
4480 and item = j.product_attr_value
4481 and item_type = j.product_attribute;
4482 END LOOP;
4483 --ReplaceForecast#2
4484
4485 END;
4486
4487 -------------------------------------------------------------------
4488 -- PROCEDURE
4489 -- find_party_elig
4490 --
4491 -- PURPOSE
4492 -- Find eligible offer for given party and offers.
4493 -- This would return only active and request_only offers
4494 --
4495 -- PARAMETERS
4496 -- p_offers_tbl: Input, table of qp_list_header_id of offers
4497 -- p_party_id: Input, party id
4498 -- x_offers_tbl: Output, table of qp_list_header_id of offers
4499 -- NOTES
4500 --
4501 --------------------------------------------------------------------
4502 PROCEDURE find_party_elig(
4503 p_offers_tbl IN num_tbl_type,
4504 p_party_id IN NUMBER,
4505 p_cust_acct_id IN NUMBER := NULL,
4506 p_cust_site_id IN NUMBER := NULL,
4507
4508 p_api_version IN NUMBER,
4509 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
4510 p_commit IN VARCHAR2 := FND_API.g_false,
4511
4512 x_return_status OUT NOCOPY VARCHAR2,
4513 x_msg_count OUT NOCOPY NUMBER,
4514 x_msg_data OUT NOCOPY VARCHAR2,
4515
4516 x_offers_tbl OUT NOCOPY num_tbl_type
4517 )
4518 IS
4519
4520 CURSOR c_offer(l_party NUMBER, l_offer NUMBER) IS
4521 SELECT distinct(1)
4522 FROM ozf_activity_customers
4523 WHERE (party_id = l_party
4524 OR party_id = -1)
4525 AND object_id = l_offer
4526 AND object_class = 'OFFR'
4527 AND active_flag = 'Y'
4528 AND ask_for_flag = 'Y'
4529 AND (start_date <= TRUNC(SYSDATE)
4530 OR start_date IS NULL)
4531 AND (end_date >= TRUNC(SYSDATE)
4532 OR end_date IS NULL);
4533
4534 l_dummy NUMBER;
4535 l_counter NUMBER := 0;
4536
4537 BEGIN
4538
4539 FOR i IN 1..p_offers_tbl.COUNT LOOP
4540 l_dummy := NULL;
4541 OPEN c_offer(p_party_id, p_offers_tbl(i));
4542 FETCH c_offer INTO l_dummy;
4543 CLOSE c_offer;
4544
4545 IF l_dummy = 1 THEN
4546 l_counter := l_counter + 1;
4547 x_offers_tbl(l_counter) := p_offers_tbl(i);
4548 END IF;
4549 END LOOP;
4550 END find_party_elig;
4551
4552
4553 -------------------------------------------------------------------
4554 -- PROCEDURE
4555 -- find_products_elig
4556 --
4557 -- PURPOSE
4558 -- Find eligible offer for given party and products.
4559 --
4560 -- PARAMETERS
4561 -- p_products_tbl: Input, table of product_id of products
4562 -- p_party_id: Input, party id
4563 -- x_offers_tbl: Output, table of qp_list_header_id of offers
4564 --
4565 -- NOTES
4566 --
4567 --------------------------------------------------------------------
4568 PROCEDURE find_product_elig(
4569 p_products_tbl IN num_tbl_type,
4570 p_party_id IN NUMBER,
4571 p_cust_acct_id IN NUMBER := NULL,
4572 p_cust_site_id IN NUMBER := NULL,
4573
4574 p_api_version IN NUMBER,
4575 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
4576 p_commit IN VARCHAR2 := FND_API.g_false,
4577
4578 x_return_status OUT NOCOPY VARCHAR2,
4579 x_msg_count OUT NOCOPY NUMBER,
4580 x_msg_data OUT NOCOPY VARCHAR2,
4581
4582 x_offers_tbl OUT NOCOPY num_tbl_type
4583 )
4584 IS
4585
4586 CURSOR c_offer(l_party NUMBER, l_product NUMBER) IS
4587 SELECT object_id
4588 FROM (SELECT distinct object_id
4589 FROM ozf_activity_customers
4590 WHERE (party_id = l_party OR party_id = -1)
4591 AND active_flag = 'Y'
4592 AND ask_for_flag = 'Y'
4593 AND object_class = 'OFFR'
4594 INTERSECT
4595 SELECT object_id
4596 FROM ozf_activity_products
4597 WHERE item = l_product
4598 --AND item_type = 'PRODUCT' --fixed bug 7289857
4599 AND object_class = 'OFFR'
4600 AND active_flag = 'Y'
4601 AND ask_for_flag = 'Y');
4602
4603 l_counter NUMBER := 0;
4604
4605 BEGIN
4606
4607 FOR i IN 1..p_products_tbl.COUNT LOOP
4608 FOR j IN c_offer(p_party_id, p_products_tbl(i)) LOOP
4609 l_counter := l_counter + 1;
4610 x_offers_tbl(l_counter) := j.object_id;
4611 END LOOP;
4612 END LOOP;
4613
4614 END find_product_elig;
4615
4616
4617 --------------------------------------------------------------------
4618 -- PROCEDURE
4619 -- get_party_product_stmt
4620 --
4621 -- PURPOSE
4622 -- Generates denorm statement for budget validation.
4623 --
4624 -- PARAMETERS
4625 -- p_list_header_id: list_header_id of the offer
4626 -- x_party_stmt: party statement for the offer
4627 -- x_product_stmt: product statement for the offer
4628 -- DESCRIPTION
4629 -- This procedure calls get_sql, builds SQL statment for product and refresh ozf_activity_products
4630 ----------------------------------------------------------------------
4631 PROCEDURE get_party_product_stmt(
4632 p_api_version IN NUMBER,
4633 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
4634 p_commit IN VARCHAR2 := FND_API.g_false,
4635
4636 p_list_header_id IN NUMBER,
4637
4638 x_return_status OUT NOCOPY VARCHAR2,
4639 x_msg_count OUT NOCOPY NUMBER,
4640 x_msg_data OUT NOCOPY VARCHAR2,
4641
4642 x_party_stmt OUT NOCOPY VARCHAR2,
4643 x_product_stmt OUT NOCOPY VARCHAR2
4644 )
4645 IS
4646
4647 BEGIN
4648
4649 refresh_parties(p_api_version => p_api_version,
4650 p_init_msg_list => p_init_msg_list,
4651 p_commit => p_commit,
4652 p_list_header_id => p_list_header_id,
4653 p_calling_from_den => 'N',
4654 x_return_status => x_return_status,
4655 x_msg_count => x_msg_count,
4656 x_msg_data => x_msg_count,
4657 x_party_stmt => x_party_stmt);
4658
4659 x_party_stmt := 'select distinct(party_id) from ('||x_party_stmt||' )';
4660
4661 refresh_products(p_api_version => p_api_version,
4662 p_init_msg_list => p_init_msg_list,
4663 p_commit => p_commit,
4664 p_list_header_id => p_list_header_id,
4665 p_calling_from_den => 'N',
4666 x_return_status => x_return_status,
4667 x_msg_count => x_msg_count,
4668 x_msg_data => x_msg_count,
4669 x_product_stmt => x_product_stmt);
4670
4671 x_product_stmt := 'select distinct(product_id) from ('||x_product_stmt||' )';
4672
4673 EXCEPTION
4674 WHEN OTHERS THEN
4675 NULL;
4676
4677 END get_party_product_stmt;
4678
4679
4680 END OZF_OFFR_ELIG_PROD_DENORM_PVT;