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