[Home] [Help]
PACKAGE BODY: APPS.AMS_RUNTIME_CAMP_PVT
Source
1 PACKAGE BODY AMS_RUNTIME_CAMP_PVT as
2 /* $Header: amsvrcab.pls 120.1 2005/10/04 03:28:29 sikalyan noship $*/
3
4
5 AMS_DEBUG_HIGH_ON constant boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
6 AMS_DEBUG_LOW_ON constant boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
7 AMS_DEBUG_MEDIUM_ON constant boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
8
9
10 PROCEDURE sortRandom
11 (
12 p_input_lst IN JTF_NUMBER_TABLE,
13 p_max_ret_num IN NUMBER := NULL,
14 x_output_lst OUT NOCOPY JTF_Number_Table
15 )
16 IS
17 l_input_lst JTF_NUMBER_TABLE;
18 l_randoms JTF_NUMBER_TABLE;
19 i PLS_INTEGER := 1;
20 j PLS_INTEGER := 1;
21 limit PLS_INTEGER;
22 temp NUMBER;
23 BEGIN
24
25 IF (AMS_DEBUG_HIGH_ON) THEN
26
27
28
29 AMS_UTILITY_PVT.debug_message('random sorting starts');
30
31 END IF;
32 l_input_lst := JTF_NUMBER_TABLE();
33 x_output_lst := JTF_NUMBER_TABLE();
34 for i in 1..p_input_lst.COUNT
35 loop
36 l_input_lst.EXTEND;
37 l_input_lst(i) := p_input_lst(i);
38 end loop;
39
40 l_randoms := JTF_NUMBER_TABLE();
41 IF(p_input_lst.COUNT > 1) THEN
42
43 --first generate all random numbers
44 for i in 1..p_input_lst.COUNT
45 loop
46 l_randoms.EXTEND;
47 l_randoms(i) := dbms_random.value;
48 IF (AMS_DEBUG_HIGH_ON) THEN
49
50 AMS_UTILITY_PVT.debug_message('random value '||to_char(l_randoms(i))||' for '||p_input_lst(i));
51 END IF;
52 end loop;
53
54 -- then , do bubble sort the ids based on random numbers values
55 -- outer loop
56 for i in 1..p_input_lst.COUNT
57 loop
58 --inner loop
59 limit := p_input_lst.COUNT-i+1;
60 for j in 1..limit-1
61 loop
62 --exchange positions if greater
63 IF(l_randoms(j) > l_randoms(j+1)) THEN
64 temp := l_randoms(j);
65 l_randoms(j) := l_randoms(j+1);
66 l_randoms(j+1) := temp;
67
68 temp := l_input_lst(j);
69 l_input_lst(j) := l_input_lst(j+1);
70 l_input_lst(j+1) := temp;
71 END IF;
72 end loop;
73 end loop;
74 ELSE
75 null;
76 END IF;
77
78 --collect max no elements for random prioritization now
79 IF(p_max_ret_num IS NULL) THEN
80 x_output_lst := l_input_lst;
81 ELSE
82 IF(p_max_ret_num < l_input_lst.COUNT) THEN
83 limit := p_max_ret_num;
84 ELSE
85 limit := l_input_lst.COUNT;
86 END IF;
87 for i in 1..limit
88 loop
89 x_output_lst.EXTEND;
90 x_output_lst(i) := l_input_lst(i);
91 end loop;
92 END IF;
93 IF (AMS_DEBUG_HIGH_ON) THEN
94
95 AMS_UTILITY_PVT.debug_message('random sorting ends');
96 END IF;
97 END sortRandom;
98
99
100 PROCEDURE getFilteredOfferIds
101 (p_api_version_number IN NUMBER,
102 p_init_msg_list IN VARCHAR2,
103 p_application_id IN NUMBER,
104 p_party_id IN NUMBER,
105 p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM,
106 p_currency_code IN VARCHAR2 := NULL,
107 p_offer_lst IN JTF_NUMBER_TABLE,
108 p_org_id IN NUMBER,
109 p_max_ret_num IN NUMBER,
110 p_bus_prior IN VARCHAR2,
111 x_offer_qp_lst OUT NOCOPY off_rec_type_tbl,
112 x_return_status OUT NOCOPY VARCHAR2,
113 x_msg_count OUT NOCOPY NUMBER,
114 x_msg_data OUT NOCOPY VARCHAR2
115 )
116
117
118 IS
119
120 CURSOR c_act_offer(l_activity_id NUMBER) IS
121 SELECT aao.activity_offer_id,aao.qp_list_header_id,aao.act_offer_used_by_id
122 FROM ozf_act_offers aao,qp_list_headers_b qlhv,ams_campaign_schedules_b csch,ams_campaigns_all_b camp
123 WHERE aao.arc_act_offer_used_by = 'CSCH' AND aao.qp_list_header_id = qlhv.list_header_id
124 AND aao.act_offer_used_by_id = csch.schedule_id AND camp.campaign_id = csch.campaign_id
125 AND qlhv.active_flag = 'Y' AND TRUNC(sysdate) BETWEEN NVL(TRUNC(qlhv.start_date_active),TRUNC(sysdate)-1) and NVL(TRUNC(qlhv.end_date_active),trunc(sysdate)+1)
126 AND qlhv.ask_for_flag= 'Y' AND csch.status_code= 'ACTIVE' AND csch.activity_id=40 AND aao.activity_offer_id = l_activity_id;
127
128
129 l_api_name CONSTANT VARCHAR2(30) := 'getFilteredOffersIds';
130 l_api_version CONSTANT NUMBER := 1.0;
131 l_off_rec_type AMS_RUNTIME_CAMP_PVT.off_rec_type;
132 l_off_rec_type_tbl AMS_RUNTIME_CAMP_PVT.off_rec_type_tbl;
133 l_counter NUMBER := 0;
134
135
136 BEGIN
137
138 -- Standard call to check for call compatibility.
139 IF NOT FND_API.Compatible_API_Call( l_api_version,
140 p_api_version_number,
141 l_api_name,
142 G_PKG_NAME )
143 THEN
144 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
145 END IF;
146
147 -- Initialize message list if p_init_msg_list is set to TRUE.
148 IF FND_API.to_Boolean( p_init_msg_list ) THEN
149 FND_MSG_PUB.initialize;
150 END IF;
151
152 -- Initialize API return status to success
153 x_return_status := FND_API.G_RET_STS_SUCCESS;
154
155
156 IF (AMS_DEBUG_HIGH_ON) THEN
157 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getFilteredOfferIds starts');
158 END IF;
159
160 FOR i IN 1..p_offer_lst.COUNT
161 LOOP
162 OPEN c_act_offer(p_offer_lst(i));
163 EXIT WHEN c_act_offer%NOTFOUND;
164 FETCH c_act_offer INTO l_off_rec_type;
165 CLOSE c_act_offer;
166 l_counter := l_counter + 1;
167 l_off_rec_type_tbl(l_counter).activity_offer_id := l_off_rec_type.activity_offer_id;
168 l_off_rec_type_tbl(l_counter).qp_list_header_id := l_off_rec_type.qp_list_header_id;
169 l_off_rec_type_tbl(l_counter).camp_schedule_id := l_off_rec_type.camp_schedule_id;
170 IF ((p_bus_prior = 'RANDOM') OR (p_bus_prior = 'OFFER_START_DATE') OR
171 (p_bus_prior = 'OFFER_END_DATE')) THEN
172 null;
173 ELSIF (i >= p_max_ret_num) THEN
174 EXIT;
175 END IF;
176 END LOOP;
177
178 x_offer_qp_lst := l_off_rec_type_tbl;
179
180 IF (AMS_DEBUG_HIGH_ON) THEN
181 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getFilteredOfferIds Ends');
182 END IF;
183
184 EXCEPTION
185
186 WHEN FND_API.G_EXC_ERROR THEN
187 x_return_status := FND_API.G_RET_STS_ERROR;
188 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
189 p_count => x_msg_count,
190 p_data => x_msg_data);
191
192 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
193 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
194 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
195 p_count => x_msg_count,
196 p_data => x_msg_data);
197
198 WHEN OTHERS THEN
199 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
200 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
201 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
202 END IF;
203 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
204 p_count => x_msg_count,
205 p_data => x_msg_data);
206
207 END getFilteredOfferIds;
208
209
210
211 PROCEDURE getFilteredSchedulesFromList
212 (p_api_version_number IN NUMBER,
213 p_init_msg_list IN VARCHAR2,
214 p_application_id IN NUMBER,
215 p_party_id IN NUMBER,
216 p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM,
217 p_sched_lst IN JTF_NUMBER_TABLE,
218 p_org_id IN NUMBER,
219 p_bus_prior IN VARCHAR2 := NULL,
220 p_bus_prior_order IN VARCHAR2 := NULL,
221 p_filter_ref_code IN VARCHAR2 := NULL,
222 p_max_ret_num IN NUMBER,
223 x_sched_lst OUT NOCOPY JTF_Number_Table,
224 x_return_status OUT NOCOPY VARCHAR2,
225 x_msg_count OUT NOCOPY NUMBER,
226 x_msg_data OUT NOCOPY VARCHAR2
227 )
228 IS
229 l_api_name CONSTANT VARCHAR2(30) := 'getFilteredSchedulesFromList';
230 l_api_version CONSTANT NUMBER := 1.0;
231
232 l_sched_stmt_pre VARCHAR2(2000) :=
233 ' SELECT acsb.schedule_id
234 FROM ams_campaign_schedules_b acsb
235 WHERE acsb.status_code = ''ACTIVE''
236 AND acsb.active_flag = ''Y''
237 AND NVL(acsb.start_date_time,SYSDATE) <= SYSDATE
238 AND NVL(acsb.end_date_time,SYSDATE) >= SYSDATE
239 AND acsb.schedule_id IN
240 (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_sched_lst AS JTF_NUMBER_TABLE)) t)';
241
242 --BugFix 3537558 Replaced the IN values
243
244 l_sched_prior_stmt_pre VARCHAR2(2000) :=
245 ' SELECT acsb.schedule_id
246 FROM ams_campaign_schedules_b acsb,
247 ams_lookups LO
248 WHERE acsb.status_code = ''ACTIVE''
249 AND acsb.active_flag = ''Y''
250 AND NVL(acsb.start_date_time,SYSDATE) <= SYSDATE
251 AND NVL(acsb.end_date_time,SYSDATE) >= SYSDATE
252 AND LO.lookup_type = ''AMS_PRIORITY''
253 AND NVL(acsb.priority,''LOW'') = LO.lookup_code
254 AND acsb.schedule_id IN
255 (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_sched_lst AS JTF_NUMBER_TABLE)) t)';
256
257 --BugFix 3537558 Replaced the IN values
258
259 l_sched_stmt_post VARCHAR2(4000) :=
260 ' AND
261 (
262 (
263 NOT EXISTS
264 (
265 select L.list_used_by_id
266 from ams_act_lists L
267 where L.list_used_by_id = acsb.schedule_id
268 AND L.list_used_by = ''CSCH''
269 )
270 AND
271 NOT EXISTS
272 (
273 select S.act_market_segment_used_by_id
274 from ams_act_market_segments S
275 where S.act_market_segment_used_by_id = acsb.schedule_id
276 AND S.arc_act_market_segment_used_by = ''CSCH''
277 )
278 )
279 OR
280 (
281 EXISTS
282 (
283 SELECT L2.list_used_by_id
284 FROM ams_act_lists L2
285 WHERE L2.list_used_by_id = acsb.schedule_id
286 AND L2.list_used_by = ''CSCH''
287 AND L2.list_act_type = ''TARGET''
288 AND NOT EXISTS (
289 SELECT E2.list_header_id
290 FROM ams_list_entries E2
291 WHERE E2.list_header_id = L2.list_header_id
292 )
293 )
294 )
295 OR
296 (
297 EXISTS
298 (
299 SELECT L3.list_used_by_id
300 FROM ams_act_lists L3
301 WHERE L3.list_used_by_id = acsb.schedule_id
302 AND L3.list_used_by = ''CSCH''
303 AND L3.list_act_type = ''TARGET''
304 AND EXISTS (
305 SELECT E3.list_header_id
306 FROM ams_list_entries E3
307 WHERE E3.list_header_id = L3.list_header_id
308 AND E3.party_id = :party_id1
309 )
310 )
311 )
312 OR
313 (
314 EXISTS
315 (
316 SELECT S.act_market_segment_used_by_id
317 FROM ams_act_market_segments S
318 WHERE S.act_market_segment_used_by_id = acsb.schedule_id
319 AND S.arc_act_market_segment_used_by = ''CSCH''
320 AND EXISTS (
321 SELECT P2.market_segment_id
322 FROM ams_party_market_segments P2
323 WHERE S.market_segment_id = P2.market_segment_id
324 AND P2.party_id = :party_id2
325 )
326 )
327 )
328 )';
329
330 l_camp_csr camp_cursor;
331 l_sched_id NUMBER;
332 l_order VARCHAR2(10);
333 l_sched_in_clause1 VARCHAR2(32760);
334 p_index BINARY_INTEGER;
335 i PLS_INTEGER := 1;
336 j PLS_INTEGER := 1;
337 k PLS_INTEGER := 1;
338 pos PLS_INTEGER := 1;
339 l_random NUMBER;
340 l_found BOOLEAN := FALSE;
341 l_found1 BOOLEAN := FALSE;
342 l_sched_lst1 JTF_Number_Table;
343 l_sched_lst2 JTF_Number_Table;
344
345 BEGIN
346 -- Standard call to check for call compatibility.
347 IF NOT FND_API.Compatible_API_Call( l_api_version,
351 THEN
348 p_api_version_number,
349 l_api_name,
350 G_PKG_NAME )
352 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
353 END IF;
354
355 -- Initialize message list if p_init_msg_list is set to TRUE.
356 IF FND_API.to_Boolean( p_init_msg_list ) THEN
357 FND_MSG_PUB.initialize;
358 END IF;
359
360 -- Initialize API return status to success
361 x_return_status := FND_API.G_RET_STS_SUCCESS;
362
363 IF (AMS_DEBUG_HIGH_ON) THEN
364 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getFilteredSchedulesFromList starts '||p_party_id);
365 END IF;
366
367 IF(p_max_ret_num IS NULL) THEN
368 --max return no is null
369 x_return_status := FND_API.G_RET_STS_ERROR;
370 RAISE FND_API.G_EXC_ERROR;
371 END IF;
372
373 -- Initialize the return value table
374 x_sched_lst := JTF_Number_Table();
375 l_sched_lst1 := JTF_Number_Table();
376 l_sched_lst2 := JTF_Number_Table();
377
378 -- BugFix 3537558 Commented the IN clause values
379
380 -- p_Index := p_sched_lst.FIRST;
381 -- FOR pNum IN 1..( p_sched_lst.COUNT - 1 ) LOOP
382 -- l_sched_in_clause1 := l_sched_in_clause1 || TO_CHAR( p_sched_lst( p_Index ) ) || ', ';
383 -- p_Index := p_sched_lst.NEXT( p_Index );
384 -- END LOOP;
385
386 -- p_Index := p_sched_lst.LAST;
387 -- l_sched_in_clause1 := l_sched_in_clause1 || TO_CHAR( p_sched_lst( p_Index ) ) || ')';
388
389 -- IF (AMS_DEBUG_HIGH_ON) THEN
390 -- AMS_UTILITY_PVT.debug_message(l_sched_in_clause1);
391 -- END IF;
392
393 IF(p_bus_prior_order IS NULL OR p_bus_prior_order = 'ASC' OR p_bus_prior_order <> 'DESC') THEN
394 l_order := 'ASC';
395 ELSE
396 l_order := 'DESC';
397 END IF;
398
399 -- BugFix 3537558 Replaced the IN clause values and Included the Bind Values
400
401 IF(p_bus_prior = 'CAMPAIGN_START_DATE') THEN
402 --order by start date
403 OPEN l_camp_csr FOR l_sched_stmt_pre ||
404 l_sched_stmt_post ||
405 ' order by acsb.start_date_time ' ||
406 l_order
407 USING p_sched_lst,p_party_id,p_party_id;
408
409 ELSIF(p_bus_prior = 'CAMPAIGN_END_DATE') THEN
410 --order by end date
411 OPEN l_camp_csr FOR l_sched_stmt_pre ||
412 l_sched_stmt_post ||
413 ' order by acsb.end_date_time ' ||
414 l_order
415 USING p_sched_lst,p_party_id,p_party_id;
416 ELSIF(p_bus_prior = 'RANDOM') THEN
417 OPEN l_camp_csr FOR l_sched_stmt_pre ||
418 l_sched_stmt_post
419 USING p_sched_lst,p_party_id,p_party_id;
420 ELSIF(p_bus_prior = 'CAMPAIGN_PRIORITY') THEN
421 --order by campaign priority
422 IF(l_order = 'ASC') THEN
423 l_order := 'DESC';
424 ELSE
425 l_order := 'ASC';
426 END IF;
427 OPEN l_camp_csr FOR l_sched_prior_stmt_pre ||
428 l_sched_stmt_post ||
429 ' order by LO.tag ' ||
430 l_order
431 USING p_sched_lst,p_party_id,p_party_id;
432 ELSE
433 --no ordering by default
434 OPEN l_camp_csr FOR l_sched_stmt_pre ||
435 l_sched_stmt_post
436 USING p_sched_lst,p_party_id,p_party_id;
437 END IF;
438
439 i := 1;
440 IF(p_bus_prior = 'RANDOM') THEN
441
442 LOOP
443
444 FETCH l_camp_csr INTO l_sched_id;
445 EXIT WHEN l_camp_csr%NOTFOUND;
446
447 l_found := FALSE;
448 for j in 1..l_sched_lst1.COUNT
449 loop
450 IF(l_sched_lst1(j) = l_sched_id) THEN
451 l_found := TRUE;
452 EXIT;
453 END IF;
454 end loop;
455
456 IF(l_found = FALSE) THEN
457 l_random := dbms_random.value;
458
459 --finds the position of the Schedule Id to be inserted
460 pos := l_sched_lst2.COUNT+1;
461
462 for j in 1..l_sched_lst2.COUNT
463 loop
464 IF(l_sched_lst2(j) <= l_random) THEN
465 pos := j;
466 EXIT;
467 END IF;
468 end loop;
469
470 -- extend sizes
471 l_sched_lst1.EXTEND;
472 l_sched_lst2.EXTEND;
473
474 --right shift all other items
475 IF(pos < l_sched_lst2.COUNT) THEN
476 for j in pos+1..l_sched_lst2.COUNT
477 loop
478 l_sched_lst1(l_sched_lst1.COUNT-j+pos+1) := l_sched_lst1(l_sched_lst1.COUNT-j+pos);
479 l_sched_lst2(l_sched_lst2.COUNT-j+pos+1) := l_sched_lst2(l_sched_lst2.COUNT-j+pos);
480 end loop;
481 END IF;
482
483 l_sched_lst1(pos) := l_sched_id;
484 -- put the random number in 2nd array
485 l_sched_lst2(pos) := l_random;
486
487 i := i + 1;
488 END IF;
489
490 END LOOP;
491
495 k := l_sched_lst1.COUNT;
492 IF(l_sched_lst1.COUNT >= p_max_ret_num) THEN
493 k := p_max_ret_num;
494 ELSE
496 END IF;
497 --choose the first m items
498 for i in 1..k
499 loop
500 x_sched_lst.EXTEND;
501 x_sched_lst(i) := l_sched_lst1(i);
502 end loop;
503
504 ELSE
505 LOOP
506 FETCH l_camp_csr INTO l_sched_id;
507 EXIT WHEN l_camp_csr%NOTFOUND;
508
509 l_found := FALSE;
510 for j in 1..x_sched_lst.COUNT
511 loop
512 IF(x_sched_lst(j) = l_sched_id) THEN
513 l_found := TRUE;
514 EXIT;
515 END IF;
516 end loop;
517
518 IF(l_found = FALSE) THEN
519 x_sched_lst.EXTEND;
520 x_sched_lst(i) := l_sched_id;
521 i := i + 1;
522 END IF;
523
524 IF ((i-1) >= p_max_ret_num) THEN
525 EXIT;
526 END IF;
527 END LOOP;
528 END IF;
529
530 CLOSE l_camp_csr;
531
532 -- End of API body.
533
534 IF (AMS_DEBUG_HIGH_ON) THEN
535
536
537
538 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getFilteredSchedulesFromList ends');
539
540 END IF;
541
542 -- Standard call to get message count and if count is 1, get message info.
543 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
544 p_count => x_msg_count ,
545 p_data => x_msg_data );
546
547 EXCEPTION
548 WHEN FND_API.G_EXC_ERROR THEN
549 x_return_status := FND_API.G_RET_STS_ERROR;
550 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
551 p_count => x_msg_count ,
552 p_data => x_msg_data );
553
554 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
556 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
557 p_count => x_msg_count ,
558 p_data => x_msg_data );
559
560
561 WHEN OTHERS THEN
562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
563
564 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
565 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
566 l_api_name );
567 END IF;
568 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
569 p_count => x_msg_count ,
570 p_data => x_msg_data );
571
572 END getFilteredSchedulesFromList;
573
574
575
576 PROCEDURE getRelSchedulesForQuoteAndCust
577 (p_api_version_number IN NUMBER,
578 p_init_msg_list IN VARCHAR2,
579 p_application_id IN NUMBER,
580 p_party_id IN NUMBER,
581 p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM,
582 p_currency_code IN VARCHAR2 := NULL,
583 p_quote_id IN NUMBER,
584 p_msite_id IN NUMBER,
585 p_top_section_id IN NUMBER,
586 p_org_id IN NUMBER,
587 p_rel_type_code IN VARCHAR2,
588 p_bus_prior IN VARCHAR2,
589 p_bus_prior_order IN VARCHAR2,
590 p_filter_ref_code IN VARCHAR2,
591 p_price_list_id IN NUMBER := NULL,
592 p_max_ret_num IN NUMBER := NULL,
593 x_sched_lst OUT NOCOPY JTF_NUMBER_TABLE,
594 x_return_status OUT NOCOPY VARCHAR2,
595 x_msg_count OUT NOCOPY NUMBER,
596 x_msg_data OUT NOCOPY VARCHAR2
597 )
598 IS
599 l_api_name CONSTANT VARCHAR2(30) := 'getRelSchedulesForQuoteAndCust';
600 l_api_version CONSTANT NUMBER := 1.0;
601
602 l_sched_stmt_pre VARCHAR2(2000) :=
603 ' SELECT acsb.schedule_id
604 FROM ams_campaign_schedules_b acsb,
605 ams_iba_cpn_items_denorm D
606 WHERE acsb.schedule_id = D.object_used_by_id
607 AND acsb.status_code = ''ACTIVE''
608 AND acsb.active_flag = ''Y''
609 AND NVL(acsb.start_date_time,SYSDATE) <= SYSDATE
610 AND NVL(acsb.end_date_time,SYSDATE) >= SYSDATE
611 AND D.item_id IN (';
612
613 l_sched_prior_stmt_pre VARCHAR2(2000) :=
614 ' SELECT acsb.schedule_id
615 FROM ams_campaign_schedules_b acsb,
616 ams_iba_cpn_items_denorm D,
617 ams_lookups LO
618 WHERE acsb.schedule_id = D.object_used_by_id
619 AND acsb.status_code = ''ACTIVE''
620 AND acsb.active_flag = ''Y''
621 AND NVL(acsb.start_date_time,SYSDATE) <= SYSDATE
622 AND NVL(acsb.end_date_time,SYSDATE) >= SYSDATE
623 AND LO.lookup_type = ''AMS_PRIORITY''
624 AND NVL(acsb.priority,''LOW'') = LO.lookup_code
625 AND D.item_id IN (';
626
627 l_sched_stmt_post VARCHAR2(4000) :=
628 ' AND
629 (
630 (
631 NOT EXISTS
632 (
636 AND L.list_used_by = ''CSCH''
633 select L.list_used_by_id
634 from ams_act_lists L
635 where L.list_used_by_id = acsb.schedule_id
637 )
638 AND
639 NOT EXISTS
640 (
641 select S.act_market_segment_used_by_id
642 from ams_act_market_segments S
643 where S.act_market_segment_used_by_id = acsb.schedule_id
644 AND S.arc_act_market_segment_used_by = ''CSCH''
645 )
646 )
647 OR
648 (
649 EXISTS
650 (
651 SELECT L2.list_used_by_id
652 FROM ams_act_lists L2
653 WHERE L2.list_used_by_id = acsb.schedule_id
654 AND L2.list_used_by = ''CSCH''
655 AND L2.list_act_type = ''TARGET''
656 AND NOT EXISTS (
657 SELECT E2.list_header_id
658 FROM ams_list_entries E2
659 WHERE E2.list_header_id = L2.list_header_id
660 )
661 )
662 )
663 OR
664 (
665 EXISTS
666 (
667 SELECT L3.list_used_by_id
668 FROM ams_act_lists L3
669 WHERE L3.list_used_by_id = acsb.schedule_id
670 AND L3.list_used_by = ''CSCH''
671 AND L3.list_act_type = ''TARGET''
672 AND EXISTS (
673 SELECT E3.list_header_id
674 FROM ams_list_entries E3
675 WHERE E3.list_header_id = L3.list_header_id
676 AND E3.party_id = :party_id1
677 )
678 )
679 )
680 OR
681 (
682 EXISTS
683 (
684 SELECT S.act_market_segment_used_by_id
685 FROM ams_act_market_segments S
686 WHERE S.act_market_segment_used_by_id = acsb.schedule_id
687 AND S.arc_act_market_segment_used_by = ''CSCH''
688 AND EXISTS (
689 SELECT P2.market_segment_id
690 FROM ams_party_market_segments P2
691 WHERE S.market_segment_id = P2.market_segment_id
692 AND P2.party_id = :party_id2
693 )
694 )
695 )
696 )';
697
698 l_camp_csr camp_cursor;
699 l_quote_prod_lst JTF_NUMBER_TABLE;
700 l_return_status VARCHAR2( 10 );
701 l_msg_count NUMBER;
702 l_msg_data VARCHAR2( 1000 );
703 l_bus_prior VARCHAR2( 30 ) := NULL;
704 l_sched_id NUMBER;
705 l_sched_lst JTF_NUMBER_TABLE;
706 l_order VARCHAR2(10);
707 p_index BINARY_INTEGER;
708 l_items_in_clause1 VARCHAR2(32760);
709 order_items_in_clause1 VARCHAR2(38);
710 i PLS_INTEGER := 1;
711 j PLS_INTEGER := 1;
712 p_Num NUMBER := 1;
713 l_random NUMBER;
714 l_nosched BOOLEAN := TRUE;
715 l_intable BOOLEAN := FALSE;
716 l_found BOOLEAN := FALSE;
717
718
719 BEGIN
720 -- Standard call to check for call compatibility.
721 IF NOT FND_API.Compatible_API_Call( l_api_version,
722 p_api_version_number,
723 l_api_name,
724 G_PKG_NAME )
725 THEN
726 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
727 END IF;
728
729 -- Initialize message list if p_init_msg_list is set to TRUE.
730 IF FND_API.to_Boolean( p_init_msg_list ) THEN
731 FND_MSG_PUB.initialize;
732 END IF;
733
734 -- Initialize API return status to success
735 x_return_status := FND_API.G_RET_STS_SUCCESS;
736
737 IF (AMS_DEBUG_HIGH_ON) THEN
738
739
740
741 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getRelSchedulesForQuoteAndCust starts');
742
743 END IF;
744
745 IF(p_max_ret_num IS NULL) THEN
746 --max return no is null
747 x_return_status := FND_API.G_RET_STS_ERROR;
748 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
749 END IF;
750
751 IF(p_bus_prior <> 'RANDOM') THEN
752 l_bus_prior := p_bus_prior;
753 END IF;
754
755 l_quote_prod_lst := JTF_NUMBER_TABLE();
756
757 IF (p_rel_type_code = 'PROMOTING') THEN
758 select inventory_item_id
759 bulk collect into l_quote_prod_lst
760 from aso_quote_lines_all_v
761 where quote_header_id = p_quote_id;
762
763 ELSE
764
765 AMS_RUNTIME_PROD_PVT.getRelProdsForQuoteAndCust(
766 p_api_version_number,
767 FND_API.G_FALSE,
768 p_application_id,
769 p_party_id,
770 p_cust_account_id,
771 p_currency_code,
772 p_quote_id,
773 p_msite_id,
774 p_top_section_id,
775 p_org_id,
776 p_rel_type_code,
777 p_bus_prior,
778 p_bus_prior_order,
779 p_filter_ref_code,
780 p_price_list_id,
781 NULL,
782 l_quote_prod_lst,
783 x_return_status,
784 x_msg_count,
788 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
785 x_msg_data);
786 END IF;
787
789 IF (AMS_DEBUG_HIGH_ON) THEN
790
791 AMS_UTILITY_PVT.debug_message('Problem in AMS_RUNTIME_PROD_PVT.getRelProdsForQuoteAndCust');
792 END IF;
793 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
794 END IF;
795
796 IF l_quote_prod_lst.COUNT = 0 THEN
797 IF (AMS_DEBUG_HIGH_ON) THEN
798
799 AMS_UTILITY_PVT.debug_message('No Products returned');
800 END IF;
801 IF (AMS_DEBUG_HIGH_ON) THEN
802
803 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getRelSchedulesForQuoteAndCust ends');
804 END IF;
805
806 -- Standard call to get message count and if count is 1, get message info.
807 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
808 p_count => x_msg_count ,
809 p_data => x_msg_data );
810 return;
811 END IF;
812
813 p_Index := l_quote_prod_lst.FIRST;
814 FOR pNum IN 1..( l_quote_prod_lst.COUNT - 1 ) LOOP
815 l_items_in_clause1 := l_items_in_clause1 || TO_CHAR( l_quote_prod_lst( p_Index ) ) || ', ';
816 p_Index := l_quote_prod_lst.NEXT( p_Index );
817 END LOOP;
818
819 p_Index := l_quote_prod_lst.LAST;
820 l_items_in_clause1 := l_items_in_clause1 || TO_CHAR( l_quote_prod_lst( p_Index ) ) || ')';
821
822 IF (AMS_DEBUG_HIGH_ON) THEN
823
824
825
826 AMS_UTILITY_PVT.debug_message(l_items_in_clause1);
827
828 END IF;
829
830 --collect the schedules containing those items
831 IF(p_bus_prior_order IS NULL OR p_bus_prior_order = 'ASC' OR p_bus_prior_order <> 'DESC') THEN
832 l_order := 'ASC';
833 ELSE
834 l_order := 'DESC';
835 END IF;
836
837 IF(p_bus_prior = 'CAMPAIGN_START_DATE') THEN
838 --order by start date
839 OPEN l_camp_csr FOR l_sched_stmt_pre ||
840 l_items_in_clause1 ||
841 l_sched_stmt_post ||
842 ' order by acsb.start_date_time ' ||
843 l_order
844 USING p_party_id,p_party_id;
845 ELSIF(p_bus_prior = 'CAMPAIGN_END_DATE') THEN
846 --order by end date
847 OPEN l_camp_csr FOR l_sched_stmt_pre ||
848 l_items_in_clause1 ||
849 l_sched_stmt_post ||
850 ' order by acsb.end_date_time ' ||
851 l_order
852 USING p_party_id,p_party_id;
853 ELSIF(p_bus_prior = 'RANDOM') THEN
854 OPEN l_camp_csr FOR l_sched_stmt_pre ||
855 l_items_in_clause1 ||
856 l_sched_stmt_post
857 USING p_party_id,p_party_id;
858 ELSIF(p_bus_prior = 'CAMPAIGN_PRIORITY') THEN
859 --order by campaign priority
860 IF(l_order = 'ASC') THEN
861 l_order := 'DESC';
862 ELSE
863 l_order := 'ASC';
864 END IF;
865 OPEN l_camp_csr FOR l_sched_prior_stmt_pre ||
866 l_items_in_clause1 ||
867 l_sched_stmt_post ||
868 ' order by LO.tag ' ||
869 l_order
870 USING p_party_id,p_party_id;
871 ELSE
872 --no ordering by default
873 OPEN l_camp_csr FOR l_sched_stmt_pre ||
874 l_items_in_clause1 ||
875 l_sched_stmt_post
876 USING p_party_id,p_party_id;
877 END IF;
878
879 x_sched_lst := JTF_Number_Table();
880 i := 1;
881 LOOP
882 FETCH l_camp_csr INTO l_sched_id;
883 EXIT WHEN l_camp_csr%NOTFOUND;
884
885 l_found := FALSE;
886 for j in 1..x_sched_lst.COUNT
887 loop
888 IF(x_sched_lst(j) = l_sched_id) THEN
889 l_found := TRUE;
890 EXIT;
891 END IF;
892 end loop;
893
894 IF(l_found = FALSE) THEN
895 x_sched_lst.EXTEND;
896 x_sched_lst(i) := l_sched_id;
897 i := i + 1;
898 END IF;
899
900 IF(p_bus_prior = 'RANDOM') THEN
901 null;
902 ELSIF ((i-1) >= p_max_ret_num) THEN
903 EXIT;
904 END IF;
905 END LOOP;
906
907 close l_camp_csr;
908
909 --do random prioritization now
910 IF(p_bus_prior = 'RANDOM') THEN
911 sortRandom(
912 x_sched_lst,
913 p_max_ret_num,
914 l_sched_lst
915 );
916 x_sched_lst := l_sched_lst;
917 END IF;
918
919 -- End of API body.
920
921 IF (AMS_DEBUG_HIGH_ON) THEN
922
923 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getRelSchedulesForQuoteAndCust ends');
924
925 END IF;
926
927 -- Standard call to get message count and if count is 1, get message info.
928 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
929 p_count => x_msg_count ,
930 p_data => x_msg_data );
931
932 EXCEPTION
936 p_count => x_msg_count ,
933 WHEN FND_API.G_EXC_ERROR THEN
934 x_return_status := FND_API.G_RET_STS_ERROR;
935 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
937 p_data => x_msg_data );
938
939 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
940 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
941 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
942 p_count => x_msg_count ,
943 p_data => x_msg_data );
944
945
946 WHEN OTHERS THEN
947 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
948
949 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
950 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
951 l_api_name );
952 END IF;
953 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
954 p_count => x_msg_count ,
955 p_data => x_msg_data );
956
957 END getRelSchedulesForQuoteAndCust;
958
959
960
961 PROCEDURE getRelSchedulesForProdAndCust
962 (p_api_version_number IN NUMBER,
963 p_init_msg_list IN VARCHAR2,
964 p_application_id IN NUMBER,
965 p_party_id IN NUMBER,
966 p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM,
967 p_currency_code IN VARCHAR2 := NULL,
968 p_prod_lst IN JTF_NUMBER_TABLE,
969 p_msite_id IN NUMBER,
970 p_top_section_id IN NUMBER,
971 p_org_id IN NUMBER,
972 p_rel_type_code IN VARCHAR2,
973 p_bus_prior IN VARCHAR2,
974 p_bus_prior_order IN VARCHAR2,
975 p_filter_ref_code IN VARCHAR2,
976 p_price_list_id IN NUMBER := NULL,
977 p_max_ret_num IN NUMBER := NULL,
978 x_sched_lst OUT NOCOPY JTF_NUMBER_TABLE,
979 x_return_status OUT NOCOPY VARCHAR2,
980 x_msg_count OUT NOCOPY NUMBER,
981 x_msg_data OUT NOCOPY VARCHAR2
982 )
983 IS
984 l_api_name CONSTANT VARCHAR2(30) := 'getRelSchedulesForProdAndCust';
985 l_api_version CONSTANT NUMBER := 1.0;
986
987 l_sched_stmt_pre VARCHAR2(2000) :=
988 ' SELECT acsb.schedule_id
989 FROM ams_campaign_schedules_b acsb,
990 ams_iba_cpn_items_denorm D
991 WHERE acsb.schedule_id = D.object_used_by_id
992 AND D.object_used_by_type = ''CSCH''
993 AND acsb.status_code = ''ACTIVE''
994 AND acsb.active_flag = ''Y''
995 AND NVL(acsb.start_date_time,SYSDATE) <= SYSDATE
996 AND NVL(acsb.end_date_time,SYSDATE) >= SYSDATE
997 AND D.item_id IN (';
998
999 l_sched_prior_stmt_pre VARCHAR2(2000) :=
1000 ' SELECT acsb.schedule_id
1001 FROM ams_campaign_schedules_b acsb,
1002 ams_iba_cpn_items_denorm D,
1003 ams_lookups LO
1004 WHERE acsb.schedule_id = D.object_used_by_id
1005 AND D.object_used_by_type = ''CSCH''
1006 AND acsb.status_code = ''ACTIVE''
1007 AND acsb.active_flag = ''Y''
1008 AND NVL(acsb.start_date_time,SYSDATE) <= SYSDATE
1009 AND NVL(acsb.end_date_time,SYSDATE) >= SYSDATE
1010 AND LO.lookup_type = ''AMS_PRIORITY''
1011 AND NVL(acsb.priority,''LOW'') = LO.lookup_code
1012 AND D.item_id IN (';
1013
1014 l_sched_stmt_post VARCHAR2(4000) :=
1015 ' AND
1016 (
1017 (
1018 NOT EXISTS
1019 (
1020 select L.list_used_by_id
1021 from ams_act_lists L
1022 where L.list_used_by_id = acsb.schedule_id
1023 AND L.list_used_by = ''CSCH''
1024 )
1025 AND
1026 NOT EXISTS
1027 (
1028 select S.act_market_segment_used_by_id
1029 from ams_act_market_segments S
1030 where S.act_market_segment_used_by_id = acsb.schedule_id
1031 AND S.arc_act_market_segment_used_by = ''CSCH''
1032 )
1033 )
1034 OR
1035 (
1036 EXISTS
1037 (
1038 SELECT L2.list_used_by_id
1039 FROM ams_act_lists L2
1040 WHERE L2.list_used_by_id = acsb.schedule_id
1041 AND L2.list_used_by = ''CSCH''
1042 AND L2.list_act_type = ''TARGET''
1043 AND NOT EXISTS (
1044 SELECT E2.list_header_id
1045 FROM ams_list_entries E2
1046 WHERE E2.list_header_id = L2.list_header_id
1047 )
1048 )
1049 )
1050 OR
1051 (
1052 EXISTS
1053 (
1054 SELECT L3.list_used_by_id
1055 FROM ams_act_lists L3
1056 WHERE L3.list_used_by_id = acsb.schedule_id
1057 AND L3.list_used_by = ''CSCH''
1058 AND L3.list_act_type = ''TARGET''
1059 AND EXISTS (
1063 AND E3.party_id = :party_id1
1060 SELECT E3.list_header_id
1061 FROM ams_list_entries E3
1062 WHERE E3.list_header_id = L3.list_header_id
1064 )
1065 )
1066 )
1067 OR
1068 (
1069 EXISTS
1070 (
1071 SELECT S.act_market_segment_used_by_id
1072 FROM ams_act_market_segments S
1073 WHERE S.act_market_segment_used_by_id = acsb.schedule_id
1074 AND S.arc_act_market_segment_used_by = ''CSCH''
1075 AND EXISTS (
1076 SELECT P2.market_segment_id
1077 FROM ams_party_market_segments P2
1078 WHERE S.market_segment_id = P2.market_segment_id
1079 AND P2.party_id = :party_id2
1080 )
1081 )
1082 )
1083 )';
1084
1085 l_camp_csr camp_cursor;
1086 l_prod_lst JTF_NUMBER_TABLE;
1087 l_sched_lst JTF_NUMBER_TABLE;
1088 l_return_status VARCHAR2( 10 );
1089 l_msg_count NUMBER;
1090 l_msg_data VARCHAR2( 1000 );
1091 l_bus_prior VARCHAR2( 30 ) := NULL;
1092 l_sched_id NUMBER;
1093 l_order VARCHAR2(10);
1094 p_index BINARY_INTEGER;
1095 l_items_in_clause1 VARCHAR2(32760);
1096 i PLS_INTEGER := 1;
1097 j PLS_INTEGER := 1;
1098 p_Num NUMBER := 1;
1099 l_random NUMBER;
1100 l_nosched BOOLEAN := TRUE;
1101 l_intable BOOLEAN := FALSE;
1102 l_found BOOLEAN := FALSE;
1103 order_items_in_clause1 VARCHAR2(38);
1104
1105 BEGIN
1106 -- Standard call to check for call compatibility.
1107 IF NOT FND_API.Compatible_API_Call( l_api_version,
1108 p_api_version_number,
1109 l_api_name,
1110 G_PKG_NAME )
1111 THEN
1112 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1113 END IF;
1114
1115 -- Initialize message list if p_init_msg_list is set to TRUE.
1116 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1117 FND_MSG_PUB.initialize;
1118 END IF;
1119
1120 -- Initialize API return status to success
1121 x_return_status := FND_API.G_RET_STS_SUCCESS;
1122
1123 IF (AMS_DEBUG_HIGH_ON) THEN
1124
1125
1126
1127 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getRelSchedulesForProdAndCust starts');
1128
1129 END IF;
1130
1131 IF(p_max_ret_num IS NULL) THEN
1132 --max return no is null
1133 x_return_status := FND_API.G_RET_STS_ERROR;
1134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1135 END IF;
1136
1137 IF(p_bus_prior <> 'RANDOM') THEN
1138 l_bus_prior := p_bus_prior;
1139 END IF;
1140
1141 x_sched_lst := JTF_Number_Table();
1142
1143
1144 IF (p_rel_type_code = 'PROMOTING') THEN
1145 l_prod_lst := p_prod_lst;
1146 ELSE
1147
1148 AMS_RUNTIME_PROD_PVT.getRelProdsForProdAndCust(
1149 p_api_version_number,
1150 FND_API.G_FALSE,
1151 p_application_id,
1152 p_party_id,
1153 p_cust_account_id,
1154 p_currency_code,
1155 p_prod_lst,
1156 p_msite_id,
1157 p_top_section_id,
1158 p_org_id,
1159 p_rel_type_code,
1160 p_bus_prior,
1161 p_bus_prior_order,
1162 p_filter_ref_code,
1163 p_price_list_id,
1164 NULL,
1165 l_prod_lst,
1166 x_return_status,
1167 x_msg_count,
1168 x_msg_data);
1169 END IF;
1170
1171 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1172 IF (AMS_DEBUG_HIGH_ON) THEN
1173
1174 AMS_UTILITY_PVT.debug_message('Problem in AMS_RUNTIME_PROD_PVT.getRelProdsForProdAndCust');
1175 END IF;
1176 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1177 END IF;
1178
1179 IF l_prod_lst.COUNT = 0 THEN
1180 IF (AMS_DEBUG_HIGH_ON) THEN
1181
1182 AMS_UTILITY_PVT.debug_message('No Products returned');
1183 END IF;
1184 IF (AMS_DEBUG_HIGH_ON) THEN
1185
1186 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getRelSchedulesForProdAndCust ends');
1187 END IF;
1188
1189 -- Standard call to get message count and if count is 1, get message info.
1190 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1191 p_count => x_msg_count ,
1192 p_data => x_msg_data );
1193 return;
1194 END IF;
1195
1196 p_Index := l_prod_lst.FIRST;
1197 FOR pNum IN 1..( l_prod_lst.COUNT - 1 ) LOOP
1198 l_items_in_clause1 := l_items_in_clause1 || TO_CHAR( l_prod_lst( p_Index ) ) || ', ';
1199 p_Index := l_prod_lst.NEXT( p_Index );
1200 END LOOP;
1201
1202 p_Index := l_prod_lst.LAST;
1203 l_items_in_clause1 := l_items_in_clause1 || TO_CHAR( l_prod_lst( p_Index ) ) || ')';
1204
1205 IF (AMS_DEBUG_HIGH_ON) THEN
1206
1207 AMS_UTILITY_PVT.debug_message(l_items_in_clause1);
1208
1209 END IF;
1210
1211
1215 ELSE
1212 --collect the schedules containing those items
1213 IF(p_bus_prior_order IS NULL OR p_bus_prior_order = 'ASC' OR p_bus_prior_order <> 'DESC') THEN
1214 l_order := 'ASC';
1216 l_order := 'DESC';
1217 END IF;
1218
1219 IF(p_bus_prior = 'CAMPAIGN_START_DATE') THEN
1220 --order by start date
1221 OPEN l_camp_csr FOR l_sched_stmt_pre ||
1222 l_items_in_clause1 ||
1223 l_sched_stmt_post ||
1224 ' order by acsb.start_date_time ' ||
1225 l_order
1226 USING p_party_id,p_party_id;
1227 ELSIF(p_bus_prior = 'CAMPAIGN_END_DATE') THEN
1228 --order by end date
1229 OPEN l_camp_csr FOR l_sched_stmt_pre ||
1230 l_items_in_clause1 ||
1231 l_sched_stmt_post ||
1232 ' order by acsb.end_date_time ' ||
1233 l_order
1234 USING p_party_id,p_party_id;
1235 ELSIF(p_bus_prior = 'RANDOM') THEN
1236 OPEN l_camp_csr FOR l_sched_stmt_pre ||
1237 l_items_in_clause1 ||
1238 l_sched_stmt_post
1239 USING p_party_id,p_party_id;
1240 ELSIF(p_bus_prior = 'CAMPAIGN_PRIORITY') THEN
1241 --order by campaign priority
1242 IF(l_order = 'ASC') THEN
1243 l_order := 'DESC';
1244 ELSE
1245 l_order := 'ASC';
1246 END IF;
1247 OPEN l_camp_csr FOR l_sched_prior_stmt_pre ||
1248 l_items_in_clause1 ||
1249 l_sched_stmt_post ||
1250 ' order by LO.tag ' ||
1251 l_order
1252 USING p_party_id,p_party_id;
1253 ELSIF (p_bus_prior = 'PROD_LIST_PRICE') THEN
1254 null;
1255 ELSE
1256 --no ordering by default
1257 OPEN l_camp_csr FOR l_sched_stmt_pre ||
1258 l_items_in_clause1 ||
1259 l_sched_stmt_post
1260 USING p_party_id,p_party_id;
1261 END IF;
1262
1263 x_sched_lst := JTF_Number_Table();
1264
1265
1266 --ListPrice Order for ProductRelationship
1267
1268 IF (p_bus_prior = 'PROD_LIST_PRICE' AND l_prod_lst.COUNT > 0) THEN
1269 i := 1;
1270 FOR k IN 1..(l_prod_lst.COUNT)
1271 LOOP
1272 order_items_in_clause1 := TO_CHAR(l_prod_lst(k)) || ')';
1273
1274 IF (l_camp_csr%ISOPEN ) THEN
1275 CLOSE l_camp_csr;
1276 END IF;
1277
1278 OPEN l_camp_csr FOR l_sched_stmt_pre ||
1279 order_items_in_clause1 ||
1280 l_sched_stmt_post
1281 USING p_party_id,p_party_id;
1282
1283
1284 LOOP
1285 FETCH l_camp_csr INTO l_sched_id;
1286 EXIT WHEN l_camp_csr%NOTFOUND;
1287 l_found := FALSE;
1288 for j in 1..x_sched_lst.COUNT
1289 loop
1290 IF(x_sched_lst(j) = l_sched_id) THEN
1291 l_found := TRUE;
1292 EXIT;
1293 END IF;
1294 end loop;
1295 IF(l_found = FALSE) THEN
1296 x_sched_lst.EXTEND;
1297 x_sched_lst(i) := l_sched_id;
1298 i := i + 1;
1299 END IF;
1300 IF ((i-1) >= p_max_ret_num) THEN
1301 EXIT;
1302 END IF;
1303
1304 END LOOP;
1305 close l_camp_csr;
1306 IF ((i-1) >= p_max_ret_num) THEN
1307 EXIT;
1308 END IF;
1309 order_items_in_clause1 := null;
1310 END LOOP;
1311
1312 END IF;
1313
1314 -- End for Product List Price
1315
1316 IF (p_bus_prior <> 'PROD_LIST_PRICE' ) THEN
1317 i := 1;
1318 LOOP
1319 FETCH l_camp_csr INTO l_sched_id;
1320 EXIT WHEN l_camp_csr%NOTFOUND;
1321
1322 l_found := FALSE;
1323 for j in 1..x_sched_lst.COUNT
1324 loop
1325 IF(x_sched_lst(j) = l_sched_id) THEN
1326 l_found := TRUE;
1327 EXIT;
1328 END IF;
1329 end loop;
1330
1331 IF(l_found = FALSE) THEN
1332 x_sched_lst.EXTEND;
1333 x_sched_lst(i) := l_sched_id;
1334 i := i + 1;
1335 END IF;
1336
1337 IF(p_bus_prior = 'RANDOM') THEN
1338 null;
1339 ELSIF ((i-1) >= p_max_ret_num) THEN
1340 EXIT;
1341 END IF;
1342 END LOOP;
1343
1344 close l_camp_csr;
1345
1346 --do random prioritization now
1347 IF(p_bus_prior = 'RANDOM') THEN
1348 sortRandom(
1349 x_sched_lst,
1350 p_max_ret_num,
1351 l_sched_lst
1352 );
1353 x_sched_lst := l_sched_lst;
1354 END IF;
1355
1356 END IF;
1357
1358 -- End of API body.
1359
1360 IF (AMS_DEBUG_HIGH_ON) THEN
1361
1362
1363
1364 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getRelSchedulesForProdAndCust ends');
1365
1366 END IF;
1367
1368 -- Standard call to get message count and if count is 1, get message info.
1369 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1370 p_count => x_msg_count,
1374 WHEN FND_API.G_EXC_ERROR THEN
1371 p_data => x_msg_data);
1372
1373 EXCEPTION
1375 x_return_status := FND_API.G_RET_STS_ERROR;
1376 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1377 p_count => x_msg_count,
1378 p_data => x_msg_data);
1379
1380 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1382 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1383 p_count => x_msg_count,
1384 p_data => x_msg_data);
1385
1386
1387 WHEN OTHERS THEN
1388 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1389
1390 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1391 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1392 END IF;
1393
1394 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1395 p_count => x_msg_count,
1396 p_data => x_msg_data);
1397
1398 END getRelSchedulesForProdAndCust;
1399
1400
1401 PROCEDURE getFilteredOffersFromList
1402 (p_api_version_number IN NUMBER,
1403 p_init_msg_list IN VARCHAR2,
1404 p_application_id IN NUMBER,
1405 p_party_id IN NUMBER,
1406 p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM,
1407 p_currency_code IN VARCHAR2 := NULL,
1408 p_offer_lst IN JTF_NUMBER_TABLE,
1409 p_org_id IN NUMBER,
1410 p_bus_prior IN VARCHAR2 := NULL,
1411 p_bus_prior_order IN VARCHAR2 := NULL,
1412 p_filter_ref_code IN VARCHAR2 := NULL,
1413 p_price_list_id IN NUMBER := NULL,
1414 p_max_ret_num IN NUMBER,
1415 x_offer_lst OUT NOCOPY JTF_Number_Table,
1416 x_return_status OUT NOCOPY VARCHAR2,
1417 x_msg_count OUT NOCOPY NUMBER,
1418 x_msg_data OUT NOCOPY VARCHAR2
1419 )
1420
1421 IS
1422
1423 l_api_name CONSTANT VARCHAR2(30) := 'getFilteredOffersFromList';
1424 l_api_version CONSTANT NUMBER := 1.0;
1425 l_cust_account_id NUMBER;
1426 l_party_id NUMBER;
1427 --l_offers_tbl AMS_OFFR_ELIG_PROD_DENORM_PVT.num_tbl_type;
1428 l_offers_tbl OZF_OFFR_ELIG_PROD_DENORM_PVT.num_tbl_type;
1429 l_offer_lst JTF_NUMBER_TABLE;
1430
1431 l_in_offer_lst JTF_NUMBER_TABLE;
1432
1433 --x_offers_tbl AMS_OFFR_ELIG_PROD_DENORM_PVT.num_tbl_type;
1434 x_offers_tbl OZF_OFFR_ELIG_PROD_DENORM_PVT.num_tbl_type;
1435
1436 --fix for SQL Repository issue 11755480
1437 l_offer_stmt VARCHAR2(2500) := 'Select qlhv.list_header_id
1438 FROM ozf_act_offers aao,qp_list_headers_b qlhv,ams_campaign_schedules_b csch
1439 WHERE aao.arc_act_offer_used_by = ''CSCH'' AND aao.qp_list_header_id = qlhv.list_header_id
1440 and aao.qp_list_header_id in (SELECT COLUMN_VALUE FROM TABLE(CAST(:l_in_offer_lst AS JTF_NUMBER_TABLE)) )
1441 AND aao.act_offer_used_by_id = csch.schedule_id
1442 AND qlhv.active_flag =''Y'' AND TRUNC(sysdate) BETWEEN NVL(TRUNC(qlhv.start_date_active),TRUNC(sysdate)-1)
1443 and NVL(TRUNC(qlhv.end_date_active),trunc(sysdate)+1)
1444 AND qlhv.ask_for_flag = ''Y'' AND csch.status_code = ''ACTIVE'' AND csch.activity_id=40';
1445
1446
1447 --'Select qlhv.list_header_id
1448 --FROM ozf_act_offers aao,qp_list_headers_b qlhv,ams_campaign_schedules_b csch,ams_campaigns_all_b camp
1449 --WHERE aao.arc_act_offer_used_by = ''CSCH'' AND aao.qp_list_header_id = qlhv.list_header_id
1450 --AND aao.act_offer_used_by_id = csch.schedule_id AND camp.campaign_id = csch.campaign_id
1451 --AND qlhv.active_flag =''Y'' AND TRUNC(sysdate) BETWEEN NVL(TRUNC(qlhv.start_date_active),TRUNC(sysdate)-1)
1452 --and NVL(TRUNC(qlhv.end_date_active),trunc(sysdate)+1)
1453 --AND qlhv.ask_for_flag = ''Y'' AND csch.status_code = ''ACTIVE'' AND csch.activity_id=40
1454 --AND qlhv.list_header_id IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:l_in_offer_lst AS JTF_NUMBER_TABLE)) t)';
1455
1456 -- BugFix 3684266 Replaced IN clause
1457
1458 l_offer_csr camp_cursor;
1459 l_offer_id NUMBER;
1460 l_order VARCHAR2(10);
1461 l_offer_in_clause1 VARCHAR2(32760);
1462 p_index BINARY_INTEGER;
1463 i PLS_INTEGER := 1;
1464 j PLS_INTEGER := 1;
1465 k PLS_INTEGER := 1;
1466 pos PLS_INTEGER := 1;
1467 l_random NUMBER;
1468 l_found BOOLEAN := FALSE;
1469 act_offer_lst JTF_Number_Table;
1470 l_offer_lst1 JTF_Number_Table;
1471 l_offer_lst2 JTF_Number_Table;
1472 x_offer_qp_lst AMS_RUNTIME_CAMP_PVT.off_rec_type_tbl;
1473 l_validate_qp_list_header_id NUMBER;
1474 l_qp_rec_type_tbl AMS_RUNTIME_CAMP_PVT.qp_rec_type_tbl;
1475
1476 BEGIN
1477 -- Standard call to check for call compatibility.
1478 IF NOT FND_API.Compatible_API_Call( l_api_version,
1479 p_api_version_number,
1480 l_api_name,
1481 G_PKG_NAME )
1482 THEN
1483 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1484 END IF;
1485
1489 END IF;
1486 -- Initialize message list if p_init_msg_list is set to TRUE.
1487 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1488 FND_MSG_PUB.initialize;
1490
1491 -- Initialize API return status to success
1492 x_return_status := FND_API.G_RET_STS_SUCCESS;
1493
1494
1495 IF (AMS_DEBUG_HIGH_ON) THEN
1496 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getFilteredOffersFromList starts');
1497 END IF;
1498
1499
1500 -- Validation on the required parameters
1501
1502 IF (p_party_id is NULL) THEN
1503 -- party id is null
1504 x_return_status := FND_API.G_RET_STS_ERROR;
1505 RAISE FND_API.G_EXC_ERROR;
1506 ELSE
1507 l_party_id := p_party_id;
1508 END IF;
1509
1510
1511
1512 IF (p_offer_lst.count = 0) THEN
1513 -- Offer ids is 0
1514 x_return_status := FND_API.G_RET_STS_ERROR;
1515 RAISE FND_API.G_EXC_ERROR;
1516 ELSE
1517 getFilteredOfferIds (p_api_version_number,
1518 p_init_msg_list,
1519 p_application_id,
1520 p_party_id,
1521 p_cust_account_id,
1522 p_currency_code,
1523 p_offer_lst,
1524 p_org_id,
1525 p_max_ret_num,
1526 p_bus_prior,
1527 x_offer_qp_lst,
1528 x_return_status,
1529 x_msg_count,
1530 x_msg_data);
1531 END IF;
1532
1533
1534 IF (x_offer_qp_lst.count = 0) THEN
1535 -- Offer ids is 0
1536 x_return_status := FND_API.G_RET_STS_ERROR;
1537 RAISE FND_API.G_EXC_ERROR;
1538 ELSE
1539
1540 -- convert user defined TABLE to num_tbl_type
1541
1542 FOR i in x_offer_qp_lst.first .. x_offer_qp_lst.last
1543 LOOP
1544 l_offers_tbl(i) := x_offer_qp_lst(i).qp_list_header_id;
1545 END LOOP;
1546 END IF;
1547
1548
1549 l_cust_account_id := p_cust_account_id;
1550
1551
1552 IF (AMS_DEBUG_HIGH_ON) THEN
1553 --AMS_UTILITY_PVT.debug_message('AMS_OFFR_ELIG_PROD_DENORM_PVT.find_party_elig starts');
1554 AMS_UTILITY_PVT.debug_message('OZF_OFFR_ELIG_PROD_DENORM_PVT.find_party_elig starts');
1555 END IF;
1556
1557 /*
1558 AMS_OFFR_ELIG_PROD_DENORM_PVT.find_party_elig(
1559 p_offers_tbl => l_offers_tbl,
1560 p_party_id => l_party_id,
1561 p_cust_acct_id => l_cust_account_id,
1562 p_cust_site_id => NULL,
1563 p_api_version => 1.0,
1564 p_init_msg_list => FND_API.G_FALSE,
1565 p_commit => FND_API.G_FALSE,
1566 x_return_status => x_return_status,
1567 x_msg_count => x_msg_count,
1568 x_msg_data => x_msg_data ,
1569 x_offers_tbl => x_offers_tbl
1570 );
1571
1572 */
1573
1574 OZF_OFFR_ELIG_PROD_DENORM_PVT.find_party_elig(
1575 p_offers_tbl => l_offers_tbl,
1576 p_party_id => l_party_id,
1577 p_cust_acct_id => l_cust_account_id,
1578 p_cust_site_id => NULL,
1579 p_api_version => 1.0,
1580 p_init_msg_list => FND_API.G_FALSE,
1581 p_commit => FND_API.G_FALSE,
1582 x_return_status => x_return_status,
1583 x_msg_count => x_msg_count,
1584 x_msg_data => x_msg_data ,
1585 x_offers_tbl => x_offers_tbl
1586 );
1587
1588
1589 -- Standard call to check for call compatibility.
1590
1591 IF NOT FND_API.Compatible_API_Call( l_api_version,
1592 p_api_version_number,
1593 l_api_name,
1594 G_PKG_NAME )
1595 THEN
1596 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1597 END IF;
1598
1599
1600 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1601 IF (AMS_DEBUG_HIGH_ON) THEN
1602
1603 --AMS_UTILITY_PVT.debug_message('Problem in AMS_OFFR_ELIG_PROD_DENORM_PVT.find_party_elig');
1604 AMS_UTILITY_PVT.debug_message('Problem in OZF_OFFR_ELIG_PROD_DENORM_PVT.find_party_elig');
1605 END IF;
1606 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1607 END IF;
1608
1609 x_return_status := FND_API.g_ret_sts_success;
1610
1611 l_offer_lst := JTF_NUMBER_TABLE();
1612
1613 l_in_offer_lst := JTF_NUMBER_TABLE();
1614
1615 IF x_offers_tbl.COUNT = 0 THEN
1616 IF (AMS_DEBUG_HIGH_ON) THEN
1617
1618 AMS_UTILITY_PVT.debug_message('No Offers returned');
1619 END IF;
1620
1621 IF (AMS_DEBUG_HIGH_ON) THEN
1622 --AMS_UTILITY_PVT.debug_message('AMS_OFFR_ELIG_PROD_DENORM_PVT.find_party_elig ends');
1623 AMS_UTILITY_PVT.debug_message('OZF_OFFR_ELIG_PROD_DENORM_PVT.find_party_elig ends');
1624 END IF;
1625
1626 -- Standard call to get message count and if count is 1, get message info.
1627 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1628 p_count => x_msg_count ,
1629 p_data => x_msg_data );
1630
1631 return;
1632 ELSE
1633
1634 -- Convert num_tbl_type to JTF_NUMBER_TABLE
1635
1636 FOR i in x_offers_tbl.first .. x_offers_tbl.last
1637 LOOP
1638 l_offer_lst.EXTEND;
1639 l_offer_lst(i) := x_offers_tbl(i);
1640 END LOOP;
1644
1641 x_offer_lst := l_offer_lst;
1642
1643 END IF;
1645
1646
1647 -- Got the Desired list of Offers Now Display priority
1648
1649 -- Initialize the return value table
1650
1651 act_offer_lst := JTF_Number_Table();
1652 l_offer_lst1 := JTF_Number_Table();
1653 l_offer_lst2 := JTF_Number_Table();
1654
1655 l_in_offer_lst := x_offer_lst;
1656
1657 -- BugFix 3684266 Commented IN clause
1658
1659 p_Index := x_offer_lst.FIRST;
1660 FOR pNum IN 1..( x_offer_lst.COUNT - 1 ) LOOP
1661 l_offer_in_clause1 := l_offer_in_clause1 || TO_CHAR( x_offer_lst( p_Index ) ) || ', ';
1662 p_Index := x_offer_lst.NEXT( p_Index );
1663 END LOOP;
1664 p_Index := x_offer_lst.LAST;
1665 l_offer_in_clause1 := l_offer_in_clause1 || TO_CHAR( x_offer_lst( p_Index ) ) || ')';
1666
1667 IF (AMS_DEBUG_HIGH_ON) THEN
1668 AMS_UTILITY_PVT.debug_message(l_offer_in_clause1);
1669 END IF;
1670
1671 IF(p_bus_prior_order IS NULL OR p_bus_prior_order = 'ASC' OR p_bus_prior_order <> 'DESC') THEN
1672 l_order := 'ASC';
1673 ELSE
1674 l_order := 'DESC';
1675 END IF;
1676
1677 IF(p_bus_prior = 'OFFER_START_DATE') THEN
1678 --order by start date
1679 OPEN l_offer_csr FOR l_offer_stmt ||
1680 ' order by qlhv.start_date_active ' ||
1681 l_order using l_in_offer_lst;
1682 ELSIF(p_bus_prior = 'OFFER_END_DATE') THEN
1683 --order by end date
1684 OPEN l_offer_csr FOR l_offer_stmt ||
1685 ' order by qlhv.end_date_active ' ||
1686 l_order using l_in_offer_lst;
1687
1688 ELSIF(p_bus_prior = 'RANDOM') THEN
1689 --order Randomly
1690 OPEN l_offer_csr FOR l_offer_stmt using l_in_offer_lst;
1691 ELSE
1692 --no ordering by default
1693 OPEN l_offer_csr FOR l_offer_stmt using l_in_offer_lst;
1694 END IF;
1695
1696 i := 1;
1697
1698 IF(p_bus_prior = 'RANDOM') THEN
1699 LOOP
1700 FETCH l_offer_csr INTO l_offer_id;
1701 EXIT WHEN l_offer_csr%NOTFOUND;
1702 l_found := FALSE;
1703 for j in 1..l_offer_lst1.COUNT
1704 loop
1705 IF(l_offer_lst1(j) = l_offer_id) THEN
1706 l_found := TRUE;
1707 EXIT;
1708 END IF;
1709 end loop;
1710 IF(l_found = FALSE) THEN
1711 l_random := dbms_random.value;
1712 --finds the position of the Offer Id to be inserted
1713 pos := l_offer_lst2.COUNT+1;
1714 for j in 1..l_offer_lst2.COUNT
1715 loop
1716 IF(l_offer_lst2(j) <= l_random) THEN
1717 pos := j;
1718 EXIT;
1719 END IF;
1720 end loop;
1721 -- extend sizes
1722 l_offer_lst1.EXTEND;
1723 l_offer_lst2.EXTEND;
1724 --right shift all other items
1725 IF(pos < l_offer_lst2.COUNT) THEN
1726 for j in pos+1..l_offer_lst2.COUNT
1727 loop
1728 l_offer_lst1(l_offer_lst1.COUNT-j+pos+1) := l_offer_lst1(l_offer_lst1.COUNT-j+pos);
1729 l_offer_lst2(l_offer_lst2.COUNT-j+pos+1) := l_offer_lst2(l_offer_lst2.COUNT-j+pos);
1730 end loop;
1731 END IF;
1732 l_offer_lst1(pos) := l_offer_id;
1733 -- put the random number in 2nd array
1734 l_offer_lst2(pos) := l_random;
1735
1736 i := i + 1;
1737 END IF;
1738 END LOOP;
1739
1740 IF(l_offer_lst1.COUNT >= p_max_ret_num) THEN
1741 k := p_max_ret_num;
1742 ELSE
1743 k := l_offer_lst1.COUNT;
1744 END IF;
1745
1746 --choose the first m items
1747 for i in 1..k
1748 loop
1749 act_offer_lst.EXTEND;
1750 act_offer_lst(i) := l_offer_lst1(i);
1751 end loop;
1752
1753 ELSE
1754 LOOP
1755 FETCH l_offer_csr INTO l_offer_id;
1756 EXIT WHEN l_offer_csr%NOTFOUND;
1757 l_found := FALSE;
1758 for j in 1..act_offer_lst.COUNT
1759 loop
1760 IF(act_offer_lst(j) = l_offer_id) THEN
1761 l_found := TRUE;
1762 EXIT;
1763 END IF;
1764 end loop;
1765 IF(l_found = FALSE) THEN
1766 act_offer_lst.EXTEND;
1767 act_offer_lst(i) := l_offer_id;
1768 i := i + 1;
1769 END IF;
1770 IF ((i-1) >= p_max_ret_num) THEN
1771 EXIT;
1772 END IF;
1773 END LOOP;
1774
1775 END IF;
1776
1777 CLOSE l_offer_csr;
1778
1779 x_offer_lst := act_offer_lst;
1780
1781
1782 -- Refine the qp_list_header_ids with activity_offer_id
1783 -- also check for the max to be returned
1784
1785 act_offer_lst := jtf_number_table();
1786 k:=1;
1787
1788 FOR i in 1..x_offer_lst.COUNT
1789 LOOP
1790 l_validate_qp_list_header_id := x_offer_lst(i);
1791 FOR j in 1..x_offer_qp_lst.COUNT
1792 LOOP
1793 IF(x_offer_qp_lst(j).qp_list_header_id = l_validate_qp_list_header_id) THEN
1794 act_offer_lst.EXTEND;
1795 act_offer_lst(k) := l_validate_qp_list_header_id;
1796 k := k + 1;
1797 act_offer_lst.EXTEND;
1798 act_offer_lst(k) := x_offer_qp_lst(j).activity_offer_id;
1799 k := k +1;
1800 END IF;
1801 --IF (i >= p_max_ret_num) THEN
1805 END LOOP;
1802 -- EXIT;
1803 --END IF;
1804 END LOOP;
1806
1807 x_offer_lst := act_offer_lst;
1808
1809
1810 -- End of API body.
1811
1812 IF (AMS_DEBUG_HIGH_ON) THEN
1813 --AMS_UTILITY_PVT.debug_message('AMS_OFFR_ELIG_PROD_DENORM_PVT.find_party_elig ends');
1814 AMS_UTILITY_PVT.debug_message('OZF_OFFR_ELIG_PROD_DENORM_PVT.find_party_elig ends');
1815
1816 END IF;
1817
1818 IF (AMS_DEBUG_HIGH_ON) THEN
1819 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getFilteredOffersFromList ends');
1820 END IF;
1821
1822 -- Standard call to get message count and if count is 1, get message info.
1823
1824 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1825 p_count => x_msg_count,
1826 p_data => x_msg_data);
1827
1828 EXCEPTION
1829
1830 WHEN FND_API.G_EXC_ERROR THEN
1831 x_return_status := FND_API.G_RET_STS_ERROR;
1832 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1833 p_count => x_msg_count,
1834 p_data => x_msg_data);
1835
1836
1837 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1838 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1839 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1840 p_count => x_msg_count,
1841 p_data => x_msg_data);
1842
1843
1844 WHEN OTHERS THEN
1845 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1846
1847 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1848 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
1849 l_api_name);
1850 END IF;
1851 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1852 p_count => x_msg_count,
1853 p_data => x_msg_data);
1854
1855 END getFilteredOffersFromList;
1856
1857
1858
1859 PROCEDURE getRelOffersForQuoteAndCust
1860 (p_api_version_number IN NUMBER,
1861 p_init_msg_list IN VARCHAR2,
1862 p_application_id IN NUMBER,
1863 p_party_id IN NUMBER,
1864 p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM,
1865 p_currency_code IN VARCHAR2 := NULL,
1866 p_quote_id IN NUMBER,
1867 p_msite_id IN NUMBER,
1868 p_top_section_id IN NUMBER,
1869 p_org_id IN NUMBER,
1870 p_rel_type_code IN VARCHAR2,
1871 p_bus_prior IN VARCHAR2,
1872 p_bus_prior_order IN VARCHAR2,
1873 p_filter_ref_code IN VARCHAR2,
1874 p_price_list_id IN NUMBER := NULL,
1875 p_max_ret_num IN NUMBER := NULL,
1876 x_offer_lst OUT NOCOPY JTF_NUMBER_TABLE,
1877 x_return_status OUT NOCOPY VARCHAR2,
1878 x_msg_count OUT NOCOPY NUMBER,
1879 x_msg_data OUT NOCOPY VARCHAR2
1880 )
1881 IS
1882 l_api_name CONSTANT VARCHAR2(30) := 'getRelOffersForQuoteAndCust';
1883 l_api_version CONSTANT NUMBER := 1.0;
1884
1885 l_quote_prod_lst JTF_NUMBER_TABLE;
1886 l_in_offer_lst JTF_NUMBER_TABLE;
1887 --l_prod_tbl AMS_OFFR_ELIG_PROD_DENORM_PVT.num_tbl_type;
1888 --l_offer_tbl AMS_OFFR_ELIG_PROD_DENORM_PVT.num_tbl_type;
1889 l_prod_tbl OZF_OFFR_ELIG_PROD_DENORM_PVT.num_tbl_type;
1890 l_offer_tbl OZF_OFFR_ELIG_PROD_DENORM_PVT.num_tbl_type;
1891 l_bus_prior VARCHAR2(30) := NULL;
1892
1893 --fix for SQL Repository issue 11755493
1894 l_offer_stmt VARCHAR2(2500) := 'Select qlhv.list_header_id
1895 FROM ozf_act_offers aao,qp_list_headers_b qlhv,ams_campaign_schedules_b csch
1896 WHERE aao.arc_act_offer_used_by = ''CSCH'' AND aao.qp_list_header_id = qlhv.list_header_id
1897 and aao.qp_list_header_id in (SELECT COLUMN_VALUE FROM TABLE(CAST(:l_in_offer_lst AS JTF_NUMBER_TABLE)) )
1898 AND aao.act_offer_used_by_id = csch.schedule_id
1899 AND qlhv.active_flag =''Y'' AND TRUNC(sysdate) BETWEEN NVL(TRUNC(qlhv.start_date_active),TRUNC(sysdate)-1)
1900 and NVL(TRUNC(qlhv.end_date_active),trunc(sysdate)+1)
1901 AND qlhv.ask_for_flag = ''Y'' AND csch.status_code = ''ACTIVE'' AND csch.activity_id=40';
1902
1903
1904 --'Select qlhv.list_header_id
1905 --FROM ozf_act_offers aao,qp_list_headers_b qlhv,ams_campaign_schedules_b csch,ams_campaigns_all_b camp
1906 --WHERE aao.arc_act_offer_used_by = ''CSCH'' AND aao.qp_list_header_id = qlhv.list_header_id
1907 --AND aao.act_offer_used_by_id = csch.schedule_id AND camp.campaign_id = csch.campaign_id
1908 --AND qlhv.active_flag = ''Y'' AND TRUNC(sysdate) BETWEEN NVL(TRUNC(qlhv.start_date_active),TRUNC(sysdate)-1) and NVL(TRUNC(qlhv.end_date_active),trunc(sysdate)+1)
1909 --AND qlhv.ask_for_flag = ''Y'' AND csch.status_code = ''ACTIVE'' AND csch.activity_id=40
1910 --ND qlhv.list_header_id IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:l_in_offer_lst AS JTF_NUMBER_TABLE)) t)';
1911
1912 -- BugFix 3684266 Replaced IN clause
1913
1914 CURSOR c_act_qp_offer(l_qp_list_header_id NUMBER) IS
1915 select aao.activity_offer_id,qlhv.list_header_id
1916 FROM ozf_act_offers aao,qp_list_headers_b qlhv,ams_campaign_schedules_b csch,ams_campaigns_all_b camp
1920 AND qlhv.ask_for_flag= 'Y' AND csch.status_code= 'ACTIVE'
1917 WHERE aao.arc_act_offer_used_by = 'CSCH' AND aao.qp_list_header_id = qlhv.list_header_id
1918 AND aao.act_offer_used_by_id = csch.schedule_id AND camp.campaign_id = csch.campaign_id
1919 AND qlhv.active_flag = 'Y' AND TRUNC(sysdate) BETWEEN NVL(TRUNC(qlhv.start_date_active),TRUNC(sysdate)-1) and NVL(TRUNC(qlhv.end_date_active),trunc(sysdate)+1)
1921 AND csch.activity_id=40 AND qlhv.list_header_id = l_qp_list_header_id;
1922
1923 l_offer_csr camp_cursor;
1924 l_offer_id NUMBER;
1925 l_order VARCHAR2(10);
1926 l_offer_in_clause1 VARCHAR2(32760);
1927 p_index BINARY_INTEGER;
1928 i PLS_INTEGER := 1;
1929 j PLS_INTEGER := 1;
1930 k PLS_INTEGER := 1;
1931 pos PLS_INTEGER := 1;
1932 l_random NUMBER;
1933 l_found BOOLEAN := FALSE;
1934 act_offer_lst JTF_Number_Table;
1935 l_offer_lst JTF_Number_Table;
1936 l_offer_lst1 JTF_Number_Table;
1937 l_offer_lst2 JTF_Number_Table;
1938 -- temp_offer_lst JTF_Number_Table;
1939 l_qp_rec_type_tbl AMS_RUNTIME_CAMP_PVT.qp_rec_type_tbl;
1940 l_counter NUMBER := 0;
1941 l_qp_rec_type AMS_RUNTIME_CAMP_PVT.qp_rec_type;
1942
1943 BEGIN
1944 -- Standard call to check for call compatibility.
1945 IF NOT FND_API.Compatible_API_Call( l_api_version,
1946 p_api_version_number,
1947 l_api_name,
1948 G_PKG_NAME )
1949 THEN
1950 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1951 END IF;
1952
1953 -- Initialize message list if p_init_msg_list is set to TRUE.
1954 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1955 FND_MSG_PUB.initialize;
1956 END IF;
1957
1958 -- Initialize API return status to success
1959 x_return_status := FND_API.G_RET_STS_SUCCESS;
1960
1961 IF (AMS_DEBUG_HIGH_ON) THEN
1962
1963 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getRelOffersForQuoteAndCust starts');
1964
1965 END IF;
1966
1967 IF(p_max_ret_num IS NULL) THEN
1968 --max return no is null
1969 x_return_status := FND_API.G_RET_STS_ERROR;
1970 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1971 END IF;
1972
1973 IF(p_bus_prior <> 'RANDOM') THEN
1974 l_bus_prior := p_bus_prior;
1975 END IF;
1976
1977 l_quote_prod_lst := JTF_NUMBER_TABLE();
1978
1979 IF (p_rel_type_code = 'PROMOTING') THEN
1980 select inventory_item_id
1981 bulk collect into l_quote_prod_lst
1982 from aso_quote_lines_all_v
1983 where quote_header_id = p_quote_id;
1984 ELSE
1985 AMS_RUNTIME_PROD_PVT.getRelProdsForQuoteAndCust(
1986 p_api_version_number,
1987 FND_API.G_FALSE,
1988 p_application_id,
1989 p_party_id,
1990 p_cust_account_id,
1991 p_currency_code,
1992 p_quote_id,
1993 p_msite_id,
1994 p_top_section_id,
1995 p_org_id,
1996 p_rel_type_code,
1997 p_bus_prior,
1998 p_bus_prior_order,
1999 p_filter_ref_code,
2000 p_price_list_id,
2001 NULL,
2002 l_quote_prod_lst,
2003 x_return_status,
2004 x_msg_count,
2005 x_msg_data);
2006 END IF;
2007
2008 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2009 IF (AMS_DEBUG_HIGH_ON) THEN
2010 AMS_UTILITY_PVT.debug_message('Problem in AMS_RUNTIME_PROD_PVT.getRelProdsForQuoteAndCust');
2011 END IF;
2012 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2013 END IF;
2014
2015 IF l_quote_prod_lst.COUNT = 0 THEN
2016 IF (AMS_DEBUG_HIGH_ON) THEN
2017 AMS_UTILITY_PVT.debug_message('No Products returned');
2018 END IF;
2019 IF (AMS_DEBUG_HIGH_ON) THEN
2020 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getRelSchedulesForQuoteAndCust ends');
2021 END IF;
2022
2023 -- Standard call to get message count and if count is 1, get message info.
2024 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2025 p_count => x_msg_count,
2026 p_data => x_msg_data);
2027 return;
2028 ELSE
2029 -- We have list of products.
2030 -- Convert _lst to _tbl
2031 FOR i in l_quote_prod_lst.first .. l_quote_prod_lst.last
2032 LOOP
2033 l_prod_tbl(i) := l_quote_prod_lst(i);
2034 END LOOP;
2035
2036 -- Get all the offer for the products - call find_product_elig.
2037 /*
2038 AMS_OFFR_ELIG_PROD_DENORM_PVT.find_product_elig(
2039 l_prod_tbl,
2040 p_party_id,
2041 p_cust_account_id,
2042 NULL, --p_cust_site_id
2043 l_api_version,
2044 FND_API.G_FALSE, --p_init_msg_list
2045 FND_API.G_FALSE, --p_commit
2046 x_return_status,
2047 x_msg_count,
2048 x_msg_data,
2049 l_offer_tbl
2050 );
2051 */
2052 OZF_OFFR_ELIG_PROD_DENORM_PVT.find_product_elig(
2053 l_prod_tbl,
2054 p_party_id,
2055 p_cust_account_id,
2056 NULL, --p_cust_site_id
2057 l_api_version,
2061 x_msg_count,
2058 FND_API.G_FALSE, --p_init_msg_list
2059 FND_API.G_FALSE, --p_commit
2060 x_return_status,
2062 x_msg_data,
2063 l_offer_tbl
2064 );
2065
2066 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2067 IF (AMS_DEBUG_HIGH_ON) THEN
2068 --AMS_UTILITY_PVT.debug_message('Problem in AMS_OFFR_ELIG_PROD_DENORM_PVT.find_product_elig');
2069 AMS_UTILITY_PVT.debug_message('Problem in OZF_OFFR_ELIG_PROD_DENORM_PVT.find_product_elig');
2070 END IF;
2071 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2072 END IF;
2073 x_return_status := FND_API.G_RET_STS_SUCCESS;
2074 IF l_offer_tbl.COUNT = 0 THEN
2075 IF (AMS_DEBUG_HIGH_ON) THEN
2076 AMS_UTILITY_PVT.debug_message('No Offers for the products being viewed');
2077 END IF;
2078 IF (AMS_DEBUG_HIGH_ON) THEN
2079 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getRelOffersForQuotAndCust ends');
2080 END IF;
2081
2082 -- Standard call to get message count and if count is 1, get message info.
2083 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2084 p_count => x_msg_count,
2085 p_data => x_msg_data);
2086 RETURN;
2087
2088 END IF;
2089
2090 -- There are some offers
2091 -- convert l_offer_tbl to x_offer_list
2092 IF (AMS_DEBUG_HIGH_ON) THEN
2093 AMS_UTILITY_PVT.debug_message('# of Offers: '|| l_offer_tbl.COUNT);
2094 END IF;
2095
2096 -- temp_offer_lst := JTF_Number_Table();
2097
2098 l_in_offer_lst := JTF_Number_Table();
2099
2100 -- Convert Table to List
2101 FOR i in l_offer_tbl.first .. l_offer_tbl.last
2102 LOOP
2103 l_in_offer_lst.EXTEND;
2104 l_in_offer_lst(i) := l_offer_tbl(i);
2105 END LOOP;
2106
2107 END IF;
2108
2109 -- Got the Offers Now for the Display Priorities
2110 p_Index := l_in_offer_lst.FIRST;
2111
2112 FOR pNum IN 1..( l_in_offer_lst.COUNT - 1 ) LOOP
2113 l_offer_in_clause1 := l_offer_in_clause1 || TO_CHAR( l_in_offer_lst( p_Index ) ) || ', ';
2114 p_Index := l_in_offer_lst.NEXT( p_Index );
2115 END LOOP;
2116
2117 p_Index := l_in_offer_lst.LAST;
2118 l_offer_in_clause1 := l_offer_in_clause1 || TO_CHAR( l_in_offer_lst( p_Index ) ) || ')';
2119
2120 IF (AMS_DEBUG_HIGH_ON) THEN
2121 AMS_UTILITY_PVT.debug_message(l_offer_in_clause1);
2122 END IF;
2123
2124 IF(p_bus_prior_order IS NULL OR p_bus_prior_order = 'ASC' OR p_bus_prior_order <> 'DESC') THEN
2125 l_order := 'ASC';
2126 ELSE
2127 l_order := 'DESC';
2128 END IF;
2129
2130 IF(p_bus_prior = 'OFFER_START_DATE') THEN
2131 --order by start date
2132 OPEN l_offer_csr FOR l_offer_stmt ||
2133 ' order by qlhv.start_date_active ' ||
2134 l_order using l_in_offer_lst;
2135
2136 ELSIF(p_bus_prior = 'OFFER_END_DATE') THEN
2137 --order by end date
2138 OPEN l_offer_csr FOR l_offer_stmt ||
2139 ' order by qlhv.end_date_active ' ||
2140 l_order using l_in_offer_lst ;
2141
2142 ELSIF(p_bus_prior = 'RANDOM') THEN
2143 --order Randomly
2144 OPEN l_offer_csr FOR l_offer_stmt using l_in_offer_lst ;
2145
2146 ELSE
2147 --no ordering by default
2148 OPEN l_offer_csr FOR l_offer_stmt using l_in_offer_lst;
2149
2150 END IF;
2151
2152 act_offer_lst := JTF_Number_Table();
2153
2154 i := 1;
2155 LOOP
2156 FETCH l_offer_csr INTO l_offer_id;
2157 EXIT WHEN l_offer_csr%NOTFOUND;
2158
2159 l_found := FALSE;
2160 for j in 1..act_offer_lst.COUNT
2161 loop
2162 IF(act_offer_lst(j) = l_offer_id) THEN
2163 l_found := TRUE;
2164 EXIT;
2165 END IF;
2166 end loop;
2167
2168 IF(l_found = FALSE) THEN
2169 act_offer_lst.EXTEND;
2170 act_offer_lst(i) := l_offer_id;
2171 i := i + 1;
2172 END IF;
2173
2174 IF(p_bus_prior = 'RANDOM') THEN
2175 null;
2176 ELSIF ((i-1) >= p_max_ret_num) THEN
2177 EXIT;
2178 END IF;
2179 END LOOP;
2180
2181
2182 close l_offer_csr;
2183
2184 x_offer_lst := act_offer_lst;
2185
2186 --do random prioritization now
2187
2188 IF(p_bus_prior = 'RANDOM') THEN
2189 sortRandom(
2190 act_offer_lst,
2191 p_max_ret_num,
2192 l_offer_lst
2193 );
2194 x_offer_lst := l_offer_lst;
2195 END IF;
2196
2197
2198 -- Refine the qp_list_header_ids with activity_offer_id
2199
2200 FOR i IN 1..x_offer_lst.COUNT
2201 LOOP
2202 OPEN c_act_qp_offer(x_offer_lst(i));
2203 EXIT WHEN c_act_qp_offer%NOTFOUND;
2204 FETCH c_act_qp_offer INTO l_qp_rec_type;
2205 close c_act_qp_offer;
2206 l_counter := l_counter + 1;
2207 l_qp_rec_type_tbl(l_counter).o_activity_offer_id := l_qp_rec_type.o_activity_offer_id;
2208 l_qp_rec_type_tbl(l_counter).o_qp_list_header_id := l_qp_rec_type.o_qp_list_header_id;
2212
2209 END LOOP;
2210
2211 -- convert l_qp_rec_type_tbl to act_offer_list
2213 act_offer_lst := JTF_Number_Table();
2214 l_counter := 0;
2215
2216 -- Convert Table to List
2217
2218 IF (l_qp_rec_type_tbl.COUNT > 0) THEN
2219 FOR i in l_qp_rec_type_tbl.first .. l_qp_rec_type_tbl.last
2220 LOOP
2221 l_counter := l_counter + 1;
2222 act_offer_lst.EXTEND;
2223 act_offer_lst(l_counter) := l_qp_rec_type_tbl(i).o_qp_list_header_id;
2224 l_counter := l_counter + 1;
2225 act_offer_lst.EXTEND;
2226 act_offer_lst(l_counter) := l_qp_rec_type_tbl(i).o_activity_offer_id;
2227 END LOOP;
2228 END IF;
2229
2230 -- End Refine the qp_list_header_ids with activity_offer_id
2231
2232 x_offer_lst := act_offer_lst;
2233
2234 -- End of API body.
2235
2236 IF (AMS_DEBUG_HIGH_ON) THEN
2237
2238 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getRelOffersForQuoteAndCust ends');
2239
2240 END IF;
2241
2242 -- Standard call to get message count and if count is 1, get message info.
2243 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2244 p_count => x_msg_count,
2245 p_data => x_msg_data);
2246
2247 EXCEPTION
2248 WHEN FND_API.G_EXC_ERROR THEN
2249 x_return_status := FND_API.G_RET_STS_ERROR;
2250 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2251 p_count => x_msg_count,
2252 p_data => x_msg_data);
2253
2254 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2256 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2257 p_count => x_msg_count,
2258 p_data => x_msg_data);
2259
2260 WHEN OTHERS THEN
2261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2262
2263 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2264 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
2265 END IF;
2266 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2267 p_count => x_msg_count,
2268 p_data => x_msg_data);
2269
2270 END getRelOffersForQuoteAndCust;
2271
2272
2273 PROCEDURE getRelOffersForProdAndCust
2274 (p_api_version_number IN NUMBER,
2275 p_init_msg_list IN VARCHAR2,
2276 p_application_id IN NUMBER,
2277 p_party_id IN NUMBER,
2278 p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM,
2279 p_currency_code IN VARCHAR2 := NULL,
2280 p_prod_lst IN JTF_NUMBER_TABLE,
2281 p_msite_id IN NUMBER,
2282 p_top_section_id IN NUMBER,
2283 p_org_id IN NUMBER,
2284 p_rel_type_code IN VARCHAR2,
2285 p_bus_prior IN VARCHAR2,
2286 p_bus_prior_order IN VARCHAR2,
2287 p_filter_ref_code IN VARCHAR2,
2288 p_price_list_id IN NUMBER := NULL,
2289 p_max_ret_num IN NUMBER := NULL,
2290 x_offer_lst OUT NOCOPY JTF_NUMBER_TABLE,
2291 x_return_status OUT NOCOPY VARCHAR2,
2292 x_msg_count OUT NOCOPY NUMBER,
2293 x_msg_data OUT NOCOPY VARCHAR2
2294 )
2295 IS
2296
2297 l_api_name CONSTANT VARCHAR2(30) := 'getRelOffersForProdAndCust';
2298 l_api_version CONSTANT NUMBER := 1.0;
2299 l_prod_lst JTF_NUMBER_TABLE;
2300 l_bus_prior VARCHAR2(30) := NULL;
2301 --l_prod_tbl AMS_OFFR_ELIG_PROD_DENORM_PVT.num_tbl_type;
2302 --l_offer_tbl AMS_OFFR_ELIG_PROD_DENORM_PVT.num_tbl_type;
2303 l_prod_tbl OZF_OFFR_ELIG_PROD_DENORM_PVT.num_tbl_type;
2304 l_offer_tbl OZF_OFFR_ELIG_PROD_DENORM_PVT.num_tbl_type;
2305 l_offer_stmt VARCHAR2(2000) := 'SELECT qlhv.list_header_id
2306 FROM ozf_act_offers aao,qp_list_headers_b qlhv,ams_campaign_schedules_b csch,ams_campaigns_all_b camp
2307 WHERE aao.arc_act_offer_used_by = ''CSCH'' AND aao.qp_list_header_id = qlhv.list_header_id
2308 AND aao.act_offer_used_by_id = csch.schedule_id AND camp.campaign_id = csch.campaign_id
2309 AND qlhv.active_flag = ''Y'' AND TRUNC(sysdate) BETWEEN NVL(TRUNC(qlhv.start_date_active),TRUNC(sysdate)-1) and NVL(TRUNC(qlhv.end_date_active),trunc(sysdate)+1)
2310 AND qlhv.ask_for_flag= ''Y'' AND csch.status_code= ''ACTIVE'' AND csch.activity_id=40
2311 AND qlhv.list_header_id IN (';
2312
2313 CURSOR c_act_qp_offer(l_qp_list_header_id NUMBER) IS
2314 SELECT aao.activity_offer_id,qlhv.list_header_id
2315 FROM ozf_act_offers aao,qp_list_headers_b qlhv,ams_campaign_schedules_b csch,ams_campaigns_all_b camp
2316 WHERE aao.arc_act_offer_used_by = 'CSCH' AND aao.qp_list_header_id = qlhv.list_header_id
2317 AND aao.act_offer_used_by_id = csch.schedule_id AND camp.campaign_id = csch.campaign_id
2318 AND qlhv.active_flag = 'Y' AND TRUNC(sysdate) BETWEEN NVL(TRUNC(qlhv.start_date_active),TRUNC(sysdate)-1) and NVL(TRUNC(qlhv.end_date_active),trunc(sysdate)+1)
2319 AND qlhv.ask_for_flag= 'Y' AND qlhv.source_system_code = 'AMS' AND csch.status_code= 'ACTIVE'
2320 AND csch.activity_id=40 AND qlhv.list_header_id = l_qp_list_header_id;
2321
2322 l_offer_csr camp_cursor;
2323 l_offer_id NUMBER;
2327 p_index BINARY_INTEGER;
2324 l_order VARCHAR2(10);
2325 l_offer_in_clause1 VARCHAR2(32760);
2326 order_offer_items_in_clause1 VARCHAR2(32760);
2328 i PLS_INTEGER := 1;
2329 j PLS_INTEGER := 1;
2330 k PLS_INTEGER := 1;
2331 pos PLS_INTEGER := 1;
2332 l_random NUMBER;
2333 l_found BOOLEAN := FALSE;
2334 act_offer_lst JTF_Number_Table;
2335 l_offer_lst JTF_Number_Table;
2336 l_offer_lst1 JTF_Number_Table;
2337 l_offer_lst2 JTF_Number_Table;
2338 l_validate_qp_list_header_id NUMBER;
2339 l_qp_rec_type_tbl AMS_RUNTIME_CAMP_PVT.qp_rec_type_tbl;
2340 l_counter NUMBER := 0;
2341 l_qp_rec_type AMS_RUNTIME_CAMP_PVT.qp_rec_type;
2342
2343
2344
2345 BEGIN
2346
2347 -- Standard call to check for call compatibility.
2348 IF NOT FND_API.Compatible_API_Call( l_api_version,
2349 p_api_version_number,
2350 l_api_name,
2351 G_PKG_NAME )
2352 THEN
2353 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2354 END IF;
2355
2356
2357 -- Initialize message list if p_init_msg_list is set to TRUE.
2358 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2359 FND_MSG_PUB.initialize;
2360 END IF;
2361
2362 -- Initialize API return status to success
2363 x_return_status := FND_API.G_RET_STS_SUCCESS;
2364
2365 IF (AMS_DEBUG_HIGH_ON) THEN
2366
2367
2368
2369 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getRelOffersForProdAndCust starts');
2370
2371 END IF;
2372
2373 IF(p_max_ret_num IS NULL) THEN
2374 --max return no is null
2375 x_return_status := FND_API.G_RET_STS_ERROR;
2376 IF (AMS_DEBUG_HIGH_ON) THEN
2377
2378 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getRelOffersForProdAndCust starts max ret null');
2379 END IF;
2380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2381 END IF;
2382
2383
2384 -- CHANGED THE DATATYPE SAME AS LOCAL
2385
2386 IF(p_bus_prior <> 'RANDOM') THEN
2387 l_bus_prior := p_bus_prior;
2388 END IF;
2389
2390 x_offer_lst := JTF_Number_Table();
2391
2392 IF (p_rel_type_code = 'PROMOTING') THEN
2393 l_prod_lst := p_prod_lst;
2394 ELSE
2395 AMS_RUNTIME_PROD_PVT.getRelProdsForProdAndCust(
2396 l_api_version,
2397 FND_API.G_FALSE,
2398 p_application_id,
2399 p_party_id,
2400 p_cust_account_id,
2401 p_currency_code,
2402 p_prod_lst,
2403 p_msite_id,
2404 p_top_section_id,
2405 p_org_id,
2406 p_rel_type_code,
2407 p_bus_prior,
2408 p_bus_prior_order,
2409 p_filter_ref_code,
2410 p_price_list_id,
2411 -- NULL,
2412 p_max_ret_num,
2413 l_prod_lst,
2414 x_return_status,
2415 x_msg_count,
2416 x_msg_data);
2417 END IF;
2418
2419 -- May have to check if the p_max_ret_num is applied properly
2420
2421 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2422 IF (AMS_DEBUG_HIGH_ON) THEN
2423
2424 AMS_UTILITY_PVT.debug_message('Problem in AMS_RUNTIME_PROD_PVT.getRelProdsForProdAndCust');
2425 END IF;
2426 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2427 END IF;
2428
2429
2430 IF l_prod_lst.COUNT = 0 THEN
2431 IF (AMS_DEBUG_HIGH_ON) THEN
2432
2433 AMS_UTILITY_PVT.debug_message('No Products returned');
2434 END IF;
2435 IF (AMS_DEBUG_HIGH_ON) THEN
2436
2437 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getRelSchedulesForProdAndCust ends');
2438 END IF;
2439
2440 -- Standard call to get message count and if count is 1, get message info.
2441 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2442 p_count => x_msg_count,
2443 p_data => x_msg_data);
2444 RETURN;
2445
2446 ELSE -- some products returned.
2447
2448 -- We have list of products.
2449 -- Convert _lst to _tbl
2450 FOR i in l_prod_lst.first .. l_prod_lst.last
2451 LOOP
2452 l_prod_tbl(i) := l_prod_lst(i);
2453 END LOOP;
2454
2455 IF (AMS_DEBUG_HIGH_ON) THEN
2456
2457
2458
2459 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getRelOffersForProdAndCust We have list of products');
2460
2461 END IF;
2462
2463 IF (AMS_DEBUG_HIGH_ON) THEN
2464
2465
2466
2467 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getRelOffersForProdAndCust Get all the offer for the products call find_product_elig');
2468
2469 END IF;
2470
2471 -- Get all the offer for the products - call find_product_elig.
2472 /* AMS_OFFR_ELIG_PROD_DENORM_PVT.find_product_elig(
2473 l_prod_tbl,
2474 p_party_id,
2475 p_cust_account_id,
2476 NULL, --p_cust_site_id
2477 l_api_version,
2478 FND_API.G_FALSE, --p_init_msg_list
2479 FND_API.G_FALSE, --p_commit
2480 x_return_status,
2481 x_msg_count,
2482 x_msg_data,
2483 l_offer_tbl
2484 );
2485 */
2489 p_party_id,
2486
2487 OZF_OFFR_ELIG_PROD_DENORM_PVT.find_product_elig(
2488 l_prod_tbl,
2490 p_cust_account_id,
2491 NULL, --p_cust_site_id
2492 l_api_version,
2493 FND_API.G_FALSE, --p_init_msg_list
2494 FND_API.G_FALSE, --p_commit
2495 x_return_status,
2496 x_msg_count,
2497 x_msg_data,
2498 l_offer_tbl
2499 );
2500
2501 IF (AMS_DEBUG_HIGH_ON) THEN
2502 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getRelOffersForProdAndCust Get all the offer for the products ends find_product_elig');
2503 END IF;
2504
2505
2506 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2507 IF (AMS_DEBUG_HIGH_ON) THEN
2508
2509 -- AMS_UTILITY_PVT.debug_message('Problem in AMS_OFFR_ELIG_PROD_DENORM_PVT.find_product_elig');
2510 AMS_UTILITY_PVT.debug_message('Problem in OZF_OFFR_ELIG_PROD_DENORM_PVT.find_product_elig');
2511 END IF;
2512 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2513 END IF;
2514
2515 x_return_status := FND_API.G_RET_STS_SUCCESS;
2516
2517 IF l_offer_tbl.COUNT = 0 THEN
2518 IF (AMS_DEBUG_HIGH_ON) THEN
2519
2520 AMS_UTILITY_PVT.debug_message('No Offers for the products being viewed');
2521 END IF;
2522
2523 IF (AMS_DEBUG_HIGH_ON) THEN
2524
2525
2526
2527 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getRelOffersForProdAndCust ends');
2528
2529 END IF;
2530
2531 -- Standard call to get message count and if count is 1, get message info.
2532 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2533 p_count => x_msg_count,
2534 p_data => x_msg_data);
2535 return;
2536
2537 END IF;
2538
2539
2540 IF (AMS_DEBUG_HIGH_ON) THEN
2541
2542
2543
2544
2545
2546 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getRelOffersForProdAndCust count is more than one');
2547
2548
2549 END IF;
2550
2551 -- There are some offers
2552 -- convert l_offer_tbl to x_offer_list
2553 IF (AMS_DEBUG_HIGH_ON) THEN
2554
2555 AMS_UTILITY_PVT.debug_message('# of Offers: '|| l_offer_tbl.COUNT);
2556 END IF;
2557
2558 FOR i in l_offer_tbl.first .. l_offer_tbl.last
2559 LOOP
2560 x_offer_lst.EXTEND;
2561 x_offer_lst(i) := l_offer_tbl(i);
2562 END LOOP;
2563
2564 END IF;
2565
2566 -- Got the Offers Now Display Priorities
2567
2568
2569 -- Initialize the return value table
2570 act_offer_lst := JTF_Number_Table();
2571 l_offer_lst1 := JTF_Number_Table();
2572 l_offer_lst2 := JTF_Number_Table();
2573
2574 p_Index := x_offer_lst.FIRST;
2575
2576 FOR pNum IN 1..( x_offer_lst.COUNT - 1 ) LOOP
2577 l_offer_in_clause1 := l_offer_in_clause1 || TO_CHAR( x_offer_lst( p_Index ) ) || ', ';
2578 p_Index := x_offer_lst.NEXT( p_Index );
2579 END LOOP;
2580
2581 p_Index := x_offer_lst.LAST;
2582 l_offer_in_clause1 := l_offer_in_clause1 || TO_CHAR( x_offer_lst( p_Index ) ) || ')';
2583
2584 IF (AMS_DEBUG_HIGH_ON) THEN
2585
2586 AMS_UTILITY_PVT.debug_message(l_offer_in_clause1);
2587
2588 END IF;
2589
2590 IF(p_bus_prior_order IS NULL OR p_bus_prior_order = 'ASC' OR p_bus_prior_order <> 'DESC') THEN
2591 l_order := 'ASC';
2592 ELSE
2593 l_order := 'DESC';
2594 END IF;
2595
2596 IF(p_bus_prior = 'OFFER_START_DATE') THEN
2597 --order by start date
2598 OPEN l_offer_csr FOR l_offer_stmt ||
2599 l_offer_in_clause1 ||
2600 ' order by qlhv.start_date_active ' ||
2601 l_order ;
2602
2603 ELSIF(p_bus_prior = 'OFFER_END_DATE') THEN
2604 --order by end date
2605 OPEN l_offer_csr FOR l_offer_stmt ||
2606 l_offer_in_clause1 ||
2607 ' order by qlhv.end_date_active ' ||
2608 l_order ;
2609
2610 ELSIF(p_bus_prior = 'RANDOM') THEN
2611 --order Randomly
2612 OPEN l_offer_csr FOR l_offer_stmt ||
2613 l_offer_in_clause1;
2614
2615 ELSIF (p_bus_prior = 'PROD_LIST_PRICE') THEN
2616 null;
2617
2618 ELSE
2619
2620 --no ordering by default
2621 OPEN l_offer_csr FOR l_offer_stmt ||
2622 l_offer_in_clause1 ;
2623 END IF;
2624
2625 act_offer_lst := JTF_Number_Table();
2626
2627
2628 --ListPrice Order for ProductRelationship Offers
2629
2630 IF (p_bus_prior = 'PROD_LIST_PRICE' AND l_prod_lst.COUNT > 0) THEN
2631 i := 1;
2632 FOR k IN 1..(l_offer_tbl.COUNT)
2633 LOOP
2634 order_offer_items_in_clause1 := TO_CHAR(l_offer_tbl(k)) || ')';
2635
2636 IF (l_offer_csr%ISOPEN ) THEN
2637 CLOSE l_offer_csr;
2638 END IF;
2639
2640 OPEN l_offer_csr FOR l_offer_stmt ||
2641 order_offer_items_in_clause1 ;
2642
2643 LOOP
2644 FETCH l_offer_csr INTO l_offer_id;
2645 EXIT WHEN l_offer_csr%NOTFOUND;
2646 l_found := FALSE;
2647 for j in 1..act_offer_lst.COUNT
2648 loop
2649 IF(act_offer_lst(j) = l_offer_id) THEN
2650 l_found := TRUE;
2651 EXIT;
2652 END IF;
2653 end loop;
2654 IF(l_found = FALSE) THEN
2655 act_offer_lst.EXTEND;
2656 act_offer_lst(i) := l_offer_id;
2657 i := i + 1;
2658 END IF;
2659 IF ((i-1) >= p_max_ret_num ) THEN
2660 EXIT;
2661 END IF;
2662 END LOOP;
2663 close l_offer_csr;
2664 IF ((i-1) >= p_max_ret_num) THEN
2665 EXIT;
2666 END IF;
2667 order_offer_items_in_clause1 := null;
2668 END LOOP;
2669
2670 END IF;
2671
2672
2673 -- End for Product List Price
2674
2675 IF (p_bus_prior <> 'PROD_LIST_PRICE') THEN
2676
2677 i := 1;
2678 LOOP
2679 FETCH l_offer_csr INTO l_offer_id;
2680 EXIT WHEN l_offer_csr%NOTFOUND;
2681
2682 l_found := FALSE;
2683 FOR j in 1..act_offer_lst.COUNT
2684 LOOP
2685 IF(act_offer_lst(j) = l_offer_id) THEN
2686 l_found := TRUE;
2687 EXIT;
2688 END IF;
2689 END LOOP;
2690
2691 IF(l_found = FALSE) THEN
2692 act_offer_lst.EXTEND;
2693 act_offer_lst(i) := l_offer_id;
2694 i := i + 1;
2695 END IF;
2696
2697 IF(p_bus_prior = 'RANDOM') THEN
2698 null;
2699 ELSIF ((i-1) >= p_max_ret_num) THEN
2700 EXIT;
2701 END IF;
2702 END LOOP;
2703
2704
2705 close l_offer_csr;
2706
2707 --do random prioritization now
2708
2709 IF(p_bus_prior = 'RANDOM') THEN
2710 sortRandom(
2711 act_offer_lst,
2712 p_max_ret_num,
2713 l_offer_lst
2714 );
2715 act_offer_lst := l_offer_lst;
2716 END IF;
2717
2718 END IF;
2719
2720
2721 x_offer_lst := act_offer_lst;
2722
2723 -- Refine the qp_list_header_ids with activity_offer_id
2724
2725 FOR i IN 1..x_offer_lst.COUNT
2726 LOOP
2727 OPEN c_act_qp_offer(x_offer_lst(i));
2728 EXIT WHEN c_act_qp_offer%NOTFOUND;
2729 FETCH c_act_qp_offer INTO l_qp_rec_type;
2730 close c_act_qp_offer;
2731 l_counter := l_counter + 1;
2732 l_qp_rec_type_tbl(l_counter).o_activity_offer_id := l_qp_rec_type.o_activity_offer_id;
2733 l_qp_rec_type_tbl(l_counter).o_qp_list_header_id := l_qp_rec_type.o_qp_list_header_id;
2734 END LOOP;
2735
2736 -- convert l_qp_rec_type_tbl to act_offer_list
2737
2738 act_offer_lst := JTF_Number_Table();
2739 l_counter := 0;
2740
2741 -- Convert Table to List
2742
2743 IF (l_qp_rec_type_tbl.COUNT > 0) THEN
2744 FOR i in l_qp_rec_type_tbl.first .. l_qp_rec_type_tbl.last
2745 LOOP
2746 l_counter := l_counter + 1;
2747 act_offer_lst.EXTEND;
2748 act_offer_lst(l_counter) := l_qp_rec_type_tbl(i).o_qp_list_header_id;
2749 l_counter := l_counter + 1;
2750 act_offer_lst.EXTEND;
2751 act_offer_lst(l_counter) := l_qp_rec_type_tbl(i).o_activity_offer_id;
2752 END LOOP;
2753 END IF;
2754
2755 -- End Refine the qp_list_header_ids with activity_offer_id
2756
2757 x_offer_lst := act_offer_lst;
2758
2759
2760 -- End of API body.
2761
2762 IF (AMS_DEBUG_HIGH_ON) THEN
2763
2764
2765
2766 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_CAMP_PVT.getRelOffersForProdAndCust ends');
2767
2768 END IF;
2769
2770 -- Standard call to get message count and if count is 1, get message info.
2771 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2772 p_count => x_msg_count,
2773 p_data => x_msg_data);
2774
2775 EXCEPTION
2776
2777 WHEN FND_API.G_EXC_ERROR THEN
2778 x_return_status := FND_API.G_RET_STS_ERROR;
2779 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2780 p_count => x_msg_count,
2781 p_data => x_msg_data);
2782
2783 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2785 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2786 p_count => x_msg_count,
2787 p_data => x_msg_data);
2788
2789 WHEN OTHERS THEN
2790 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2791
2792 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2793 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2794 END IF;
2795 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2796 p_count => x_msg_count,
2797 p_data => x_msg_data);
2798
2799 END getRelOffersForProdAndCust;
2800
2801
2802 END AMS_RUNTIME_CAMP_PVT;