[Home] [Help]
PACKAGE BODY: APPS.AMV_MATCH_PVT
Source
1 PACKAGE BODY amv_match_pvt AS
2 /* $Header: amvvmatb.pls 120.1 2005/06/21 16:47:20 appldev ship $ */
3 -- Start of Comments
4 --
5 -- NAME
6 -- AMV_MATCH_PVT
7 --
8 -- HISTORY
9 -- 09/30/1999 PWU created
10 --
11 -- 05/10/2000 SVATSA UPDATED
12 -- Updated the Start_MatchingEngine API to work only for a finite loop count
13 -- instead of an endless loop.
14 --
15 -- 06/23/2000 SVATSA UPDATED
16 -- (Shitij Vatsa)
17 -- Updated the following API for the territory functionality :
18 -- 1. Remove_ItemChannelMatch
19 --
20 -- Overloaded the the following API
21 -- 1. Do_ItemChannelMatch
22 --
23 -- Added two new APIs
24 -- 1. Get_UserTerritory
25 -- 2. Get_PublishedTerritories
26 --
27 --
28 -- End of Comments
29 --
30 --
31 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMV_MATCH_PVT';
32 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amvvmatb.pls';
33 G_NORMAL_PRIORITY CONSTANT NUMBER := 8;
34 G_STOP_PRIORITY CONSTANT NUMBER := 3;
35
36 --
37 -- Debug mode
38 G_DEBUG boolean := FALSE;
39 --
40 TYPE CursorType IS REF CURSOR;
41 --
42 --------------------------------------------------------------------------------
43 --------------------------------------------------------------------------------
44 PROCEDURE Request_ItemMatch
45 (
46 p_api_version IN NUMBER,
47 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
48 p_commit IN VARCHAR2 := FND_API.G_FALSE,
49 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
50 x_return_status OUT NOCOPY VARCHAR2,
51 x_msg_count OUT NOCOPY NUMBER,
52 x_msg_data OUT NOCOPY VARCHAR2,
53 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
54 p_item_id IN NUMBER
55 ) IS
56 l_api_name CONSTANT VARCHAR2(30) := 'Request_ItemMatch';
57 l_api_version CONSTANT NUMBER := 1.0;
58 l_message_obj SYSTEM.AMV_AQ_MSG_OBJECT_TYPE;
59 --
60 BEGIN
61 SAVEPOINT Request_ItemMatch_Pvt;
62 -- Standard call to check for call compatibility.
63 IF NOT FND_API.Compatible_API_Call (
64 l_api_version,
65 p_api_version,
66 l_api_name,
67 G_PKG_NAME) THEN
68 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
69 END IF;
70 --Initialize message list if p_init_msg_list is TRUE.
71 IF FND_API.To_Boolean (p_init_msg_list) THEN
72 FND_MSG_PUB.initialize;
73 END IF;
74 -- Check if item id is valid.
75 -- The rest of the checking is done on Enqueue_Message().
76 IF (AMV_UTILITY_PVT.Is_ItemIdValid(p_item_id) <> TRUE) THEN
77 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
78 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
79 FND_MESSAGE.Set_Token('RECORD', 'AMV_ITEM_TK', TRUE);
80 FND_MESSAGE.Set_Token('ID', to_char(nvl(p_item_id, -1)));
81 FND_MSG_PUB.Add;
82 END IF;
83 RAISE FND_API.G_EXC_ERROR;
84 END IF;
85 --
86 l_message_obj := SYSTEM.AMV_AQ_MSG_OBJECT_TYPE
87 (
88 G_AMV_APP_ID,
89 p_item_id,
90 'ITEM',
91 G_NORMAL_PRIORITY,
92 'Match the item'
93 );
94 -- call Enqueue_Message to put the request into AQ queue.
95 amv_aq_utility_pvt.Enqueue_Message
96 (
97 p_api_version => p_api_version,
98 p_init_msg_list => p_init_msg_list,
99 p_commit => p_commit,
100 p_validation_level => p_validation_level,
101 x_return_status => x_return_status,
102 x_msg_count => x_msg_count,
103 x_msg_data => x_msg_data,
104 p_check_login_user => p_check_login_user,
105 p_message_obj => l_message_obj
106 );
107 --
108 EXCEPTION
109 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
110 ROLLBACK TO Request_ItemMatch_Pvt;
111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
112 -- Standard call to get message count and if count=1, get the message
113 FND_MSG_PUB.Count_And_Get (
114 p_encoded => FND_API.G_FALSE,
115 p_count => x_msg_count,
116 p_data => x_msg_data
117 );
118 WHEN FND_API.G_EXC_ERROR THEN
119 ROLLBACK TO Request_ItemMatch_Pvt;
120 x_return_status := FND_API.G_RET_STS_ERROR;
121 -- Standard call to get message count and if count=1, get the message
122 FND_MSG_PUB.Count_And_Get (
123 p_encoded => FND_API.G_FALSE,
124 p_count => x_msg_count,
125 p_data => x_msg_data
126 );
127 WHEN OTHERS THEN
128 ROLLBACK TO Request_ItemMatch_Pvt;
129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
130 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
131 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
132 END IF;
133 -- Standard call to get message count and if count=1, get the message
134 FND_MSG_PUB.Count_And_Get (
135 p_encoded => FND_API.G_FALSE,
136 p_count => x_msg_count,
137 p_data => x_msg_data
138 );
139 END Request_ItemMatch;
140 --
141 --------------------------------------------------------------------------------
142 PROCEDURE Request_ChannelMatch
143 (
144 p_api_version IN NUMBER,
145 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
146 p_commit IN VARCHAR2 := FND_API.G_FALSE,
147 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
148 x_return_status OUT NOCOPY VARCHAR2,
149 x_msg_count OUT NOCOPY NUMBER,
150 x_msg_data OUT NOCOPY VARCHAR2,
151 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
152 p_channel_id IN NUMBER
153 ) IS
154 l_api_name CONSTANT VARCHAR2(30) := 'Request_ChannelMatch';
155 l_api_version CONSTANT NUMBER := 1.0;
156 l_message_obj SYSTEM.AMV_AQ_MSG_OBJECT_TYPE;
157 --
158 BEGIN
159 SAVEPOINT Request_ChannelMatch_Pvt;
160 -- Standard call to check for call compatibility.
161 IF NOT FND_API.Compatible_API_Call (
162 l_api_version,
163 p_api_version,
164 l_api_name,
165 G_PKG_NAME) THEN
166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
167 END IF;
168 --Initialize message list if p_init_msg_list is TRUE.
169 IF FND_API.To_Boolean (p_init_msg_list) THEN
170 FND_MSG_PUB.initialize;
171 END IF;
172 -- Check if channel id is valid.
173 -- The rest of the checking is done on Enqueue_Message().
174 IF (AMV_UTILITY_PVT.Is_ChannelIdValid(p_channel_id) <> TRUE) THEN
175 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
176 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
177 FND_MESSAGE.Set_Token('RECORD', 'AMV_CHANNEL_TK', TRUE);
178 FND_MESSAGE.Set_Token('ID', to_char(nvl(p_channel_id, -1)));
179 FND_MSG_PUB.Add;
180 END IF;
181 RAISE FND_API.G_EXC_ERROR;
182 END IF;
183 --
184 l_message_obj := SYSTEM.AMV_AQ_MSG_OBJECT_TYPE
185 (
186 G_AMV_APP_ID,
187 p_channel_id,
188 'CHANNEL',
189 G_NORMAL_PRIORITY,
190 'Match the channel'
191 );
192 -- call Enqueue_Message to put the request into AQ queue.
193 amv_aq_utility_pvt.Enqueue_Message
194 (
195 p_api_version => p_api_version,
196 p_init_msg_list => p_init_msg_list,
197 p_commit => p_commit,
198 p_validation_level => p_validation_level,
199 x_return_status => x_return_status,
200 x_msg_count => x_msg_count,
201 x_msg_data => x_msg_data,
202 p_check_login_user => p_check_login_user,
203 p_message_obj => l_message_obj
204 );
205 --
206 EXCEPTION
207 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
208 ROLLBACK TO Request_ChannelMatch_Pvt;
209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
210 -- Standard call to get message count and if count=1, get the message
211 FND_MSG_PUB.Count_And_Get (
212 p_encoded => FND_API.G_FALSE,
213 p_count => x_msg_count,
214 p_data => x_msg_data
215 );
216 WHEN FND_API.G_EXC_ERROR THEN
217 ROLLBACK TO Request_ChannelMatch_Pvt;
218 x_return_status := FND_API.G_RET_STS_ERROR;
219 -- Standard call to get message count and if count=1, get the message
220 FND_MSG_PUB.Count_And_Get (
221 p_encoded => FND_API.G_FALSE,
222 p_count => x_msg_count,
223 p_data => x_msg_data
224 );
225 WHEN OTHERS THEN
226 ROLLBACK TO Request_ChannelMatch_Pvt;
227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
228 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
229 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
230 END IF;
231 -- Standard call to get message count and if count=1, get the message
232 FND_MSG_PUB.Count_And_Get (
233 p_encoded => FND_API.G_FALSE,
234 p_count => x_msg_count,
235 p_data => x_msg_data
236 );
237 --
238 END Request_ChannelMatch;
239 --
240 --------------------------------------------------------------------------------
241 PROCEDURE Stop_MatchingEngine
242 (
243 p_api_version IN NUMBER,
244 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
245 p_commit IN VARCHAR2 := FND_API.G_FALSE,
246 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
247 x_return_status OUT NOCOPY VARCHAR2,
248 x_msg_count OUT NOCOPY NUMBER,
249 x_msg_data OUT NOCOPY VARCHAR2,
250 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE
251 ) IS
252 l_api_name CONSTANT VARCHAR2(30) := 'Stop_MatchingEngine';
253 l_api_version CONSTANT NUMBER := 1.0;
254 l_message_obj SYSTEM.AMV_AQ_MSG_OBJECT_TYPE;
255 --
256 BEGIN
257 SAVEPOINT Stop_MatchingEngine_Pvt;
258 -- Standard call to check for call compatibility.
259 IF NOT FND_API.Compatible_API_Call (
260 l_api_version,
261 p_api_version,
262 l_api_name,
263 G_PKG_NAME) THEN
264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
265 END IF;
266 --Initialize message list if p_init_msg_list is TRUE.
267 IF FND_API.To_Boolean (p_init_msg_list) THEN
268 FND_MSG_PUB.initialize;
269 END IF;
270 l_message_obj := SYSTEM.AMV_AQ_MSG_OBJECT_TYPE
271 (
272 G_AMV_APP_ID,
273 911,
274 'STOP',
275 G_STOP_PRIORITY,
276 'Stop the matching engine.'
277 );
278 -- call Enqueue_Message to put the request into AQ queue.
279 amv_aq_utility_pvt.Enqueue_Message
280 (
281 p_api_version => p_api_version,
282 p_init_msg_list => p_init_msg_list,
283 p_commit => p_commit,
284 p_validation_level => p_validation_level,
285 x_return_status => x_return_status,
286 x_msg_count => x_msg_count,
287 x_msg_data => x_msg_data,
288 p_check_login_user => p_check_login_user,
289 p_message_obj => l_message_obj
290 );
291 --
292 EXCEPTION
293 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
294 ROLLBACK TO Stop_MatchingEngine_Pvt;
295 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
296 -- Standard call to get message count and if count=1, get the message
297 FND_MSG_PUB.Count_And_Get (
298 p_encoded => FND_API.G_FALSE,
299 p_count => x_msg_count,
300 p_data => x_msg_data
301 );
302 WHEN FND_API.G_EXC_ERROR THEN
303 ROLLBACK TO Stop_MatchingEngine_Pvt;
304 x_return_status := FND_API.G_RET_STS_ERROR;
305 -- Standard call to get message count and if count=1, get the message
306 FND_MSG_PUB.Count_And_Get (
307 p_encoded => FND_API.G_FALSE,
308 p_count => x_msg_count,
309 p_data => x_msg_data
310 );
311 WHEN OTHERS THEN
312 ROLLBACK TO Stop_MatchingEngine_Pvt;
313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
314 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
315 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
316 END IF;
317 -- Standard call to get message count and if count=1, get the message
318 FND_MSG_PUB.Count_And_Get (
319 p_encoded => FND_API.G_FALSE,
320 p_count => x_msg_count,
321 p_data => x_msg_data
322 );
323 END Stop_MatchingEngine;
324 --
325 --------------------------------------------------------------------------------
326 PROCEDURE Start_MatchingEngine
327 (
328 errbuf OUT NOCOPY VARCHAR2,
329 retcode OUT NOCOPY NUMBER
330 ) IS
331 l_api_name CONSTANT VARCHAR2(30) := 'Start_MatchingEngine';
332 l_api_version CONSTANT NUMBER := 1.0;
333 l_resource_id NUMBER := -1;
334 l_current_user_id NUMBER := -1;
335 l_current_login_id NUMBER := -1;
336 l_current_user_status VARCHAR2(80);
337 --
338 l_admin_flag VARCHAR2(1);
339 l_message_obj SYSTEM.AMV_AQ_MSG_OBJECT_TYPE;
340 l_return_status VARCHAR2(1);
341 l_msg_count NUMBER;
342 l_msg_data VARCHAR2(2000);
343
344 -- Cursor to get the message count in the queue table
345 CURSOR c_get_msg_count IS
346 SELECT count(*)
347 FROM amv_matching_queue_tbl;
348 --WHERE q_name = 'AMV_MATCHING_QUEUE';
349
350 l_aq_count NUMBER := 0;
351 --
352
353 --
354 BEGIN
355 SAVEPOINT Start_MatchingEngine_Pvt;
356 --Initialize message list
357 FND_MSG_PUB.initialize;
358 -- Initialize API return status to success
359 errbuf := '';
360 retcode := 0;
361
362 -- Get the count of messages in the queue
363 OPEN c_get_msg_count;
364 FETCH c_get_msg_count INTO l_aq_count;
365 IF c_get_msg_count%NOTFOUND THEN
366 CLOSE c_get_msg_count;
367 END IF;
368 CLOSE c_get_msg_count;
369
370 -- Do not enter the processing loop if the message count is 0 or null
371 IF NVL(l_aq_count,0) = 0 THEN
372 RETURN;
373 END IF;
374
375 -- Instead of an endless loop, call a finite for loop
376 FOR i IN 1 .. l_aq_count LOOP
377 l_message_obj := null;
378 -- Get a message from the queue. If the queue is empty,
379 -- the program is put to sleep (via the AQ queue).
380 amv_aq_utility_pvt.Dequeue_Message
381 (
382 p_api_version => l_api_version,
383 p_init_msg_list => FND_API.G_TRUE,
384 p_commit => FND_API.G_TRUE,
385 x_return_status => l_return_status,
386 x_msg_count => l_msg_count,
387 x_msg_data => l_msg_data,
388 p_check_login_user => FND_API.G_FALSE,
389 x_message_obj => l_message_obj
390 );
391 IF (l_message_obj IS NOT NULL) THEN
392 IF (G_DEBUG = TRUE) THEN
393 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
394 '**********Start_MatchingEngine************');
395 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
396 'Time= '||to_char(sysdate,'DD-MON: HH:MI:SS'));
397 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
398 'object_id = ' || l_message_obj.object_id ||
399 ' object_type = ' || l_message_obj.object_type);
400 END IF;
401 -- The engine is told to quit.
402 IF (l_message_obj.OBJECT_TYPE = 'STOP') THEN
403 EXIT;
404 ELSIF (l_message_obj.OBJECT_TYPE = 'ITEM') THEN
405 -- process the item
406 Match_ItemWithChannels
407 (
408 p_api_version => l_api_version,
409 p_init_msg_list => FND_API.G_TRUE,
410 p_commit => FND_API.G_TRUE,
411 x_return_status => l_return_status,
412 x_msg_count => l_msg_count,
413 x_msg_data => l_msg_data,
414 p_check_login_user => FND_API.G_FALSE,
415 p_item_id => l_message_obj.object_id
416 );
417 ELSIF (l_message_obj.OBJECT_TYPE = 'CHANNEL') THEN
418 -- process the channel
419 Match_ChannelWithItems
420 (
421 p_api_version => l_api_version,
422 p_init_msg_list => FND_API.G_TRUE,
423 p_commit => FND_API.G_TRUE,
424 x_return_status => l_return_status,
425 x_msg_count => l_msg_count,
426 x_msg_data => l_msg_data,
427 p_check_login_user => FND_API.G_FALSE,
428 p_channel_id => l_message_obj.object_id
429 );
430 ELSE
431 -- unknown message type
432 -- We ignore unknown type messages.
433 null; -- maybe do something?
434 END IF;
435 END IF;
436 END LOOP;
437 --Standard call to get message count and if count=1, get the message
438 FND_MSG_PUB.Count_And_Get (
439 p_encoded => FND_API.G_FALSE,
440 p_count => l_msg_count,
441 p_data => l_msg_data
442 );
443 errbuf := l_msg_data;
444 EXCEPTION
445 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
446 ROLLBACK TO Start_MatchingEngine_Pvt;
447 -- Standard call to get message count and if count=1, get the message
448 FND_MSG_PUB.Count_And_Get (
449 p_encoded => FND_API.G_FALSE,
450 p_count => l_msg_count,
451 p_data => l_msg_data
452 );
453 errbuf := l_msg_data;
454 retcode := 2;
455 WHEN FND_API.G_EXC_ERROR THEN
456 ROLLBACK TO Start_MatchingEngine_Pvt;
457 -- Standard call to get message count and if count=1, get the message
458 FND_MSG_PUB.Count_And_Get (
459 p_encoded => FND_API.G_FALSE,
460 p_count => l_msg_count,
461 p_data => l_msg_data
462 );
463 errbuf := l_msg_data;
464 retcode := 2;
465 WHEN OTHERS THEN
466 ROLLBACK TO Start_MatchingEngine_Pvt;
467 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
468 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
469 END IF;
470 -- Standard call to get message count and if count=1, get the message
471 FND_MSG_PUB.Count_And_Get (
472 p_encoded => FND_API.G_FALSE,
473 p_count => l_msg_count,
474 p_data => l_msg_data
475 );
476 errbuf := l_msg_data;
477 retcode := 2;
478 END Start_MatchingEngine;
479 --
480 --------------------------------------------------------------------------------
481 PROCEDURE Match_ItemWithChannels
482 (
483 p_api_version IN NUMBER,
484 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
485 p_commit IN VARCHAR2 := FND_API.G_FALSE,
486 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
487 x_return_status OUT NOCOPY VARCHAR2,
488 x_msg_count OUT NOCOPY NUMBER,
489 x_msg_data OUT NOCOPY VARCHAR2,
490 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
491 p_item_id IN NUMBER
492 ) IS
493 l_api_name CONSTANT VARCHAR2(30) := 'Match_ItemWithChannels';
494 l_api_version CONSTANT NUMBER := 1.0;
495 l_resource_id NUMBER := -1;
496 l_current_user_id NUMBER := -1;
497 l_current_login_id NUMBER := -1;
498 l_current_user_status VARCHAR2(80);
499 --
500 l_return_status VARCHAR2(1);
501 l_msg_count NUMBER;
502 l_msg_data VARCHAR2(2000);
503 --
504 l_cursor CursorType;
505 l_sql_statement VARCHAR2(2000);
506 l_where_clause VARCHAR2(2000);
507 l_channel_id NUMBER;
508 l_match_on_author_flag VARCHAR2(1);
509 l_match_on_keyword_flag VARCHAR2(1);
510 l_match_on_perspective_flag VARCHAR2(1);
511 l_match_on_content_type_flag VARCHAR2(1);
512 l_match_on_item_type_flag VARCHAR2(1);
513 l_match_flag VARCHAR2(1);
514 l_tmp_number NUMBER;
515 l_content_type_id NUMBER;
516 l_application_id NUMBER;
517 l_status VARCHAR2(30);
518 l_expiration_date DATE;
519 --
520 CURSOR Get_ItemInfo_csr IS
521 Select
522 content_type_id,
523 application_id,
524 status_code status,
525 expiration_date
526 From jtf_amv_items_b
527 Where item_id = p_item_id;
528 --
529 CURSOR Check_empty_author_csr IS
530 Select 1
531 From jtf_amv_item_authors
532 Where item_id = p_item_id;
533 --
534 CURSOR Check_empty_keyword_csr IS
535 Select 1
536 From jtf_amv_item_keywords
537 Where item_id = p_item_id;
538 --
539 CURSOR Check_empty_persp_csr IS
540 Select 1
541 From amv_i_item_perspectives
542 Where item_id = p_item_id;
543 --
544 BEGIN
545 SAVEPOINT Match_ItemWithChannels_Pvt;
546 IF NOT FND_API.Compatible_API_Call (
547 l_api_version,
548 p_api_version,
549 l_api_name,
550 G_PKG_NAME) THEN
551 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
552 END IF;
553 --Initialize message list if p_init_msg_list is TRUE.
554 IF FND_API.To_Boolean (p_init_msg_list) THEN
555 FND_MSG_PUB.initialize;
556 END IF;
557 -- Initialize API return status to success
558 x_return_status := FND_API.G_RET_STS_SUCCESS;
559 -- Get the current (login) user id.
560 AMV_UTILITY_PVT.Get_UserInfo(
561 x_resource_id => l_resource_id,
562 x_user_id => l_current_user_id,
563 x_login_id => l_current_login_id,
564 x_user_status => l_current_user_status
565 );
566 IF (p_check_login_user = FND_API.G_TRUE) THEN
567 -- Check if user is login and has the required privilege.
568 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
569 -- User is not login.
570 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
571 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
572 FND_MSG_PUB.Add;
573 END IF;
574 RAISE FND_API.G_EXC_ERROR;
575 END IF;
576 END IF;
577 IF (G_DEBUG = TRUE) THEN
578 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
579 '**********Match_ItemWithChannels************');
580 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
581 'Time= '||to_char(sysdate,'DD-MON: HH:MI:SS'));
582 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE('p_item_id = '||p_item_id);
583 END IF;
584 -- Get some basic information of the item
585 -- (and thus check if the item id is valid)
586 OPEN Get_ItemInfo_csr;
587 FETCH Get_ItemInfo_csr
588 INTO l_content_type_id, l_application_id, l_status, l_expiration_date;
589 IF (Get_ItemInfo_csr%NOTFOUND) THEN
590 CLOSE Get_ItemInfo_csr;
591 -- The item id is invalid.
592 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
593 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
594 FND_MESSAGE.Set_Token('RECORD', 'AMV_ITEM_TK', TRUE);
595 FND_MESSAGE.Set_Token('ID', to_char(nvl(p_item_id, -1)));
596 FND_MSG_PUB.Add;
597 END IF;
598 RAISE FND_API.G_EXC_ERROR;
599 END IF;
600 CLOSE Get_ItemInfo_csr;
601 IF (G_DEBUG = TRUE) THEN
602 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
603 'The item (id=' ||p_item_id|| ') info: '||
604 ' status=' || l_status ||
605 ', appl id = ' || l_application_id ||
606 ', expiration = ' || l_expiration_date );
607 END IF;
608 IF (--l_application_id <> G_AMV_APP_ID OR
609 l_status <> 'ACTIVE' OR
610 nvl(l_expiration_date, sysdate+1) < sysdate) THEN
611 -- For such item, don't do the match.
612 IF (G_DEBUG = TRUE) THEN
613 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
614 'The item (id='||p_item_id|| ') does not need to be matched.');
615 END IF;
616 RAISE FND_API.G_EXC_ERROR;
617 END IF;
618 -- changed G_AMV_APP_ID to l_application_id
619 --
620 l_sql_statement :=
621 'Select ' ||
622 'c.channel_id, ' ||
623 'c.match_on_author_flag, ' ||
624 'c.match_on_keyword_flag, ' ||
625 'c.match_on_perspective_flag, ' ||
626 'c.match_on_content_type_flag, ' ||
627 'c.match_on_item_type_flag ' ||
628 'From amv_c_channels_b c ';
629 l_where_clause :=
630 'Where c.application_id = ' || l_application_id || ' ' ||
631 'And c.match_on_all_criteria_flag = ''' || FND_API.G_TRUE || ''' ' ||
632 'And c.access_level_type in (''PUBLIC'', ''PROTECT'') ' ||
633 'And c.channel_type = ''CONTENT'' ' ||
634 'And c.status = ''ACTIVE'' ' ||
635 'And nvl(c.expiration_date, sysdate+1) > sysdate ' ||
636 'And not exists ( ' ||
637 'select 1 ' ||
638 'from amv_c_chl_item_match match ' ||
639 'where match.channel_id = c.channel_id ' ||
640 'and match.item_id = :item_id ' ||
641 'and match.table_name_code = '''||G_MATCH_ITEM_TABLE || ''') ';
642 IF (l_content_type_id IS NULL OR
643 l_content_type_id = FND_API.G_MISS_NUM) THEN
644 l_where_clause := l_where_clause ||
645 'And c.match_on_content_type_flag = ''' || FND_API.G_FALSE || ''' ';
646 END IF;
647 OPEN Check_empty_author_csr;
648 FETCH Check_empty_author_csr INTO l_tmp_number;
649 IF (Check_empty_author_csr%NOTFOUND) THEN
650 l_where_clause := l_where_clause ||
651 'And c.match_on_author_flag = ''' || FND_API.G_FALSE || ''' ';
652 END IF;
653 CLOSE Check_empty_author_csr;
654 --
655 OPEN Check_empty_keyword_csr;
656 FETCH Check_empty_keyword_csr INTO l_tmp_number;
657 IF (Check_empty_keyword_csr%NOTFOUND) THEN
658 l_where_clause := l_where_clause ||
659 'And c.match_on_keyword_flag = ''' || FND_API.G_FALSE || ''' ';
660 END IF;
661 CLOSE Check_empty_keyword_csr;
662 --
663 OPEN Check_empty_persp_csr;
664 FETCH Check_empty_persp_csr INTO l_tmp_number;
665 IF (Check_empty_persp_csr%NOTFOUND) THEN
666 l_where_clause := l_where_clause ||
667 'And c.match_on_perspective_flag = ''' || FND_API.G_FALSE || ''' ';
668 END IF;
669 CLOSE Check_empty_persp_csr;
670 --
671 l_sql_statement := l_sql_statement || l_where_clause;
672 --
673 IF (G_DEBUG = TRUE) THEN
674 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE( '****SQL Statement****');
675 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE( l_sql_statement );
676 END IF;
677 --
678 OPEN l_cursor FOR l_sql_statement USING p_item_id;
679 LOOP
680 FETCH l_cursor INTO
681 l_channel_id,
682 l_match_on_author_flag,
683 l_match_on_keyword_flag,
684 l_match_on_perspective_flag,
685 l_match_on_content_type_flag,
686 l_match_on_item_type_flag;
687 EXIT WHEN l_cursor%NOTFOUND;
688
689 IF (G_DEBUG = TRUE) THEN
690 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE('channel_id =' ||
691 l_channel_id);
692 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE('match on authors =' ||
693 l_match_on_author_flag );
694 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE('match on keyword =' ||
695 l_match_on_keyword_flag );
696 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE('match_on_persp. =' ||
697 l_match_on_perspective_flag);
698 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE('match on C_type ='||
699 l_match_on_content_type_flag);
700 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE('match on I_type ='||
701 l_match_on_item_type_flag);
702 END IF;
703 IF (l_match_on_author_flag = FND_API.G_FALSE AND
704 l_match_on_keyword_flag = FND_API.G_FALSE AND
705 l_match_on_perspective_flag = FND_API.G_FALSE AND
706 l_match_on_content_type_flag = FND_API.G_FALSE AND
707 l_match_on_item_type_flag = FND_API.G_FALSE) THEN
708 IF (G_DEBUG = TRUE) THEN
709 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
710 'Invalid channel data on matching flags(id='||l_channel_id
711 || '): all flags are ''F'' (T F F F F F)' );
712 END IF;
713 ELSE
714 -- Check completely if the channel really match with the item
715 Check_MatchingCondition
716 (
717 p_api_version => p_api_version,
718 p_init_msg_list => FND_API.G_FALSE,
719 p_validation_level => p_validation_level,
720 x_return_status => l_return_status,
721 x_msg_count => l_msg_count,
722 x_msg_data => l_msg_data,
723 p_check_login_user => FND_API.G_FALSE,
724 p_item_id => p_item_id,
725 p_channel_id => l_channel_id,
726 p_match_on_author_flag => l_match_on_author_flag,
727 p_match_on_keyword_flag => l_match_on_keyword_flag,
728 p_match_on_perspective_flag => l_match_on_perspective_flag,
729 p_match_on_content_type_flag => l_match_on_content_type_flag,
730 p_match_on_item_type_flag => l_match_on_item_type_flag,
731 x_match_flag => l_match_flag
732 );
733 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
734 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
735 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR AND
736 x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
737 x_return_status := FND_API.G_RET_STS_ERROR;
738 END IF;
739 IF (G_DEBUG = TRUE) THEN
740 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
741 'MATCH REUSLT = '||l_match_flag);
742 END IF;
743 IF (l_match_flag = FND_API.G_TRUE) THEN
744 -- match the channel with the content item
745 Do_ItemChannelMatch
746 (
747 p_api_version => l_api_version,
748 p_validation_level => p_validation_level,
749 p_commit => FND_API.G_TRUE,
750 x_return_status => l_return_status,
751 x_msg_count => l_msg_count,
752 x_msg_data => l_msg_data,
753 p_check_login_user => FND_API.G_FALSE,
754 p_channel_id => l_channel_id,
755 p_item_id => p_item_id,
756 p_table_name_code => G_MATCH_ITEM_TABLE,
757 p_match_type => AMV_UTILITY_PVT.G_MATCH
758 );
759 IF (G_DEBUG = TRUE) THEN
760 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
761 'Do_ItemChannelMatch = '||l_return_status);
762 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
763 'count = '||l_msg_count ||
764 ' msg = ' || l_msg_data);
765 END IF;
766 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
767 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
768 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR AND
769 x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
770 x_return_status := FND_API.G_RET_STS_ERROR;
771 END IF;
772 END IF;
773 END IF;
774 END LOOP;
775 CLOSE l_cursor;
776 --
777 IF (G_DEBUG = TRUE) THEN
778 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE('Match_ItemWithChannels: End time '
779 || to_char(sysdate, 'DD-MON-YYYY: HH:MI:SS') );
780 END IF;
781 --Standard check of commit
782 IF FND_API.To_Boolean ( p_commit ) THEN
783 COMMIT WORK;
784 END IF;
785 --Standard call to get message count and if count=1, get the message
786 FND_MSG_PUB.Count_And_Get (
787 p_count => x_msg_count,
788 p_data => x_msg_data
789 );
790 EXCEPTION
791 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
792 ROLLBACK TO Match_ItemWithChannels_Pvt;
793 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
794 -- Standard call to get message count and if count=1, get the message
795 FND_MSG_PUB.Count_And_Get (
796 p_encoded => FND_API.G_FALSE,
797 p_count => x_msg_count,
798 p_data => x_msg_data
799 );
800 WHEN FND_API.G_EXC_ERROR THEN
801 ROLLBACK TO Match_ItemWithChannels_Pvt;
802 x_return_status := FND_API.G_RET_STS_ERROR;
803 -- Standard call to get message count and if count=1, get the message
804 FND_MSG_PUB.Count_And_Get (
805 p_encoded => FND_API.G_FALSE,
806 p_count => x_msg_count,
807 p_data => x_msg_data
808 );
809 WHEN OTHERS THEN
810 ROLLBACK TO Match_ItemWithChannels_Pvt;
811 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
812 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
813 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
814 END IF;
815 -- Standard call to get message count and if count=1, get the message
816 FND_MSG_PUB.Count_And_Get (
817 p_encoded => FND_API.G_FALSE,
818 p_count => x_msg_count,
819 p_data => x_msg_data
820 );
821 END Match_ItemWithChannels;
822 --
823 --------------------------------------------------------------------------------
824 PROCEDURE Match_ChannelWithItems
825 (
826 p_api_version IN NUMBER,
827 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
828 p_commit IN VARCHAR2 := FND_API.G_FALSE,
829 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
830 x_return_status OUT NOCOPY VARCHAR2,
831 x_msg_count OUT NOCOPY NUMBER,
832 x_msg_data OUT NOCOPY VARCHAR2,
833 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
834 p_channel_id IN NUMBER
835 ) IS
836 l_api_name CONSTANT VARCHAR2(30) := 'Match_ChannelWithItems';
837 l_api_version CONSTANT NUMBER := 1.0;
838 l_resource_id NUMBER := -1;
839 l_current_user_id NUMBER := -1;
840 l_current_login_id NUMBER := -1;
841 l_current_user_status VARCHAR2(80);
842 --
843 l_cursor CursorType;
844 l_sql_statement VARCHAR2(2000);
845 l_where_clause VARCHAR2(2000);
846 l_need_to_match_flag VARCHAR2(1);
847 l_match_on_author_flag VARCHAR2(1);
848 l_match_on_keyword_flag VARCHAR2(1);
849 l_match_on_perspective_flag VARCHAR2(1);
850 l_match_on_content_type_flag VARCHAR2(1);
851 l_match_on_item_type_flag VARCHAR2(1);
852 l_match_flag VARCHAR2(1);
853 l_application_id NUMBER;
854 l_access_level_type VARCHAR2(30);
855 l_channel_type VARCHAR2(30);
856 l_status VARCHAR2(30);
857 l_expiration_date DATE;
858 l_item_id NUMBER;
859 l_return_status VARCHAR2(1);
860 l_msg_count NUMBER;
861 l_msg_data VARCHAR2(2000);
862 --
863 --
864 CURSOR Get_ChannelInfo_csr IS
865 Select
866 c.match_on_all_criteria_flag,
867 c.match_on_author_flag,
868 c.match_on_keyword_flag,
869 c.match_on_perspective_flag,
870 c.match_on_content_type_flag,
871 c.match_on_item_type_flag,
872 c.application_id,
873 c.access_level_type,
874 c.channel_type,
875 c.status,
876 c.expiration_date
877 From amv_c_channels_b c
878 Where c.channel_id = p_channel_id;
879 --
880 BEGIN
881 SAVEPOINT Match_ChannelWithItems_Pvt;
882 IF NOT FND_API.Compatible_API_Call (
883 l_api_version,
884 p_api_version,
885 l_api_name,
886 G_PKG_NAME) THEN
887 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
888 END IF;
889 --Initialize message list if p_init_msg_list is TRUE.
890 IF FND_API.To_Boolean (p_init_msg_list) THEN
891 FND_MSG_PUB.initialize;
892 END IF;
893 -- Initialize API return status to success
894 x_return_status := FND_API.G_RET_STS_SUCCESS;
895 -- Get the current (login) user id.
896 AMV_UTILITY_PVT.Get_UserInfo(
897 x_resource_id => l_resource_id,
898 x_user_id => l_current_user_id,
899 x_login_id => l_current_login_id,
900 x_user_status => l_current_user_status
901 );
902 IF (p_check_login_user = FND_API.G_TRUE) THEN
903 -- Check if user is login and has the required privilege.
904 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
905 -- User is not login.
906 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
907 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
908 FND_MSG_PUB.Add;
909 END IF;
910 RAISE FND_API.G_EXC_ERROR;
911 END IF;
912 END IF;
913 --
914 IF (G_DEBUG = TRUE) THEN
915 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
916 '**********Match_ChannelWithItems************');
917 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
918 'Time= '||to_char(sysdate,'DD-MON: HH:MI:SS'));
919 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE('p_channel_id = '|| p_channel_id);
920 END IF;
921 -- Get all the matching flags.
922 OPEN Get_ChannelInfo_csr;
923 FETCH Get_ChannelInfo_csr
924 INTO l_need_to_match_flag, l_match_on_author_flag,
925 l_match_on_keyword_flag, l_match_on_perspective_flag,
926 l_match_on_content_type_flag, l_match_on_item_type_flag,
927 l_application_id, l_access_level_type,
928 l_channel_type, l_status,
929 l_expiration_date;
930 IF (Get_ChannelInfo_csr%NOTFOUND) THEN
931 CLOSE Get_ChannelInfo_csr;
932 -- The channel id is NOT valid.
933 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
934 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
935 FND_MESSAGE.Set_Token('RECORD', 'AMV_CHANNEL_TK', TRUE);
936 FND_MESSAGE.Set_Token('ID', to_char(nvl(p_channel_id, -1)));
937 FND_MSG_PUB.Add;
938 END IF;
939 RAISE FND_API.G_EXC_ERROR;
940 END IF;
941 CLOSE Get_ChannelInfo_csr;
942 IF (G_DEBUG = TRUE) THEN
943 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
944 'The channel id='||p_channel_id);
945 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
946 'Need Match flag = ' || l_need_to_match_flag);
947 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
948 'Match on author flag = ' || l_match_on_author_flag);
949 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
950 'Match on keyword flag = ' || l_match_on_keyword_flag);
951 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
952 'Match on persp flag = ' || l_match_on_perspective_flag);
953 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
954 'Match on C type flag = ' || l_match_on_content_type_flag);
955 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
956 'Match on I type flag = ' || l_match_on_item_type_flag);
957 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
958 'appl id = ' || l_application_id);
959 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
960 'channel_type=' || l_channel_type);
961 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
962 'status=' || l_status);
963 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
964 'expiration = ' || l_expiration_date);
965 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
966 'access_level_type = ' || l_access_level_type);
967 END IF;
968 -- For such channel, don't do the match.
969 -- Make sure the channel is good for rule matching.
970 IF (l_need_to_match_flag = FND_API.G_FALSE OR
971 --l_application_id <> G_AMV_APP_ID OR
972 l_channel_type <> 'CONTENT' OR
973 l_status <> 'ACTIVE' OR
974 nvl(l_expiration_date, sysdate+1) < sysdate OR
975 l_access_level_type = 'PRIVATE') THEN
976 -- For such channel, don't do the match.
977 RAISE FND_API.G_EXC_ERROR;
978 END IF;
979 -- Make sure the data is fine.
980 IF (l_match_on_author_flag = FND_API.G_FALSE AND
981 l_match_on_keyword_flag = FND_API.G_FALSE AND
982 l_match_on_perspective_flag = FND_API.G_FALSE AND
983 l_match_on_content_type_flag = FND_API.G_FALSE AND
984 l_match_on_item_type_flag = FND_API.G_FALSE) THEN
985 -- We should not get here: data error.
986 IF (G_DEBUG = TRUE) THEN
987 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
988 'Invalid channel data on matching flags(id='||p_channel_id
989 || '): all flags are ''F'' (T F F F F F)' );
990 END IF;
991 -- For such channel, don't do the match.
992 RAISE FND_API.G_EXC_ERROR;
993 END IF;
994 -- changed G_AMV_APP_ID to l_application_id
995 --
996 l_sql_statement :=
997 'Select ' ||
998 'item.item_id ' ||
999 'From jtf_amv_items_b item, amv_c_channels_b chan ';
1000 l_where_clause := 'Where chan.channel_id = :channel_id ' ||
1001 'And item.application_id = ' || l_application_id || ' ' ||
1002 'And item.status_code = ''ACTIVE'' ' ||
1003 'And item.item_type != ''MESSAGE_ITEM'' ' ||
1004 'And nvl(item.expiration_date,sysdate+1)>sysdate ' ||
1005 'And not exists ( ' ||
1006 'select 1 ' ||
1007 'from amv_c_chl_item_match match ' ||
1008 'where match.channel_id = chan.channel_id ' ||
1009 'and match.item_id = item.item_id ' ||
1010 'and table_name_code = ''' || G_MATCH_ITEM_TABLE
1011 || ''' ) ';
1012 --
1013 IF (l_match_on_author_flag = FND_API.G_TRUE) THEN
1014 l_where_clause := l_where_clause ||
1015 'And exists (select 1 from amv_c_authors ca, jtf_amv_item_authors ia '||
1016 'where ca.channel_id = chan.channel_id ' ||
1017 'and ia.item_id = item.item_id ' ||
1018 'and ca.author = ia.author) ';
1019 END IF;
1020 IF (l_match_on_keyword_flag = FND_API.G_TRUE) THEN
1021 l_where_clause := l_where_clause ||
1022 'And exists (select 1 from amv_c_keywords ck, ' ||
1023 ' jtf_amv_item_keywords ik ' ||
1024 'where ck.channel_id = chan.channel_id ' ||
1025 'and ik.item_id = item.item_id ' ||
1026 'and ck.keyword = ik.keyword) ';
1027 END IF;
1028 IF (l_match_on_perspective_flag = FND_API.G_TRUE) THEN
1029 l_where_clause := l_where_clause ||
1030 'And exists (select 1 from amv_c_chl_perspectives cp, ' ||
1031 'amv_i_item_perspectives ip ' ||
1032 'where cp.channel_id = chan.channel_id ' ||
1033 'and ip.item_id = item.item_id ' ||
1034 'and cp.perspective_id = ip.perspective_id) ';
1035 END IF;
1036 IF (l_match_on_content_type_flag = FND_API.G_TRUE) THEN
1037 l_where_clause := l_where_clause ||
1038 'And exists (select 1 from amv_c_content_types cc ' ||
1039 'where cc.channel_id = chan.channel_id ' ||
1040 'and cc.content_type_id = item.content_type_id) ';
1041 END IF;
1042 IF (l_match_on_item_type_flag = FND_API.G_TRUE) THEN
1043 l_where_clause := l_where_clause ||
1044 'And exists (select 1 from amv_c_item_types ci ' ||
1045 'where ci.channel_id = chan.channel_id ' ||
1046 'and ci.item_type = item.item_type) ';
1047 END IF;
1048 l_sql_statement := l_sql_statement || l_where_clause;
1049 IF (G_DEBUG = TRUE) THEN
1050 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
1051 '*************SQL Statement*************');
1052 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(l_sql_statement);
1053 END IF;
1054 -- Now do the execution.
1055 OPEN l_cursor FOR l_sql_statement USING p_channel_id;
1056 LOOP
1057 FETCH l_cursor INTO l_item_id;
1058 EXIT WHEN l_cursor%NOTFOUND;
1059 IF (G_DEBUG = TRUE) THEN
1060 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
1061 ' Matching item (id = ' || l_item_id || ') ');
1062 END IF;
1063 -- match the content item with the channel
1064 Do_ItemChannelMatch
1065 (
1066 p_api_version => l_api_version,
1067 p_commit => FND_API.G_TRUE,
1068 p_validation_level => p_validation_level,
1069 x_return_status => l_return_status,
1070 x_msg_count => l_msg_count,
1071 x_msg_data => l_msg_data,
1072 p_check_login_user => FND_API.G_FALSE,
1073 p_channel_id => p_channel_id,
1074 p_item_id => l_item_id,
1075 p_table_name_code => G_MATCH_ITEM_TABLE,
1076 p_match_type => AMV_UTILITY_PVT.G_MATCH
1077 );
1078 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1079 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1080 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR AND
1081 x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1082 x_return_status := FND_API.G_RET_STS_ERROR;
1083 END IF;
1084 END LOOP;
1085 --
1086 IF (G_DEBUG = TRUE) THEN
1087 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE('Match_ChannelWithItems: End time '
1088 || to_char(sysdate, 'DD-MON: HH:MI:SS') );
1089 END IF;
1090 --Standard check of commit
1091 IF FND_API.To_Boolean ( p_commit ) THEN
1092 COMMIT WORK;
1093 END IF;
1094 --Standard call to get message count and if count=1, get the message
1095 FND_MSG_PUB.Count_And_Get (
1096 p_count => x_msg_count,
1097 p_data => x_msg_data
1098 );
1099 EXCEPTION
1100 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1101 ROLLBACK TO Match_ChannelWithItems_Pvt;
1102 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1103 -- Standard call to get message count and if count=1, get the message
1104 FND_MSG_PUB.Count_And_Get (
1105 p_encoded => FND_API.G_FALSE,
1106 p_count => x_msg_count,
1107 p_data => x_msg_data
1108 );
1109 WHEN FND_API.G_EXC_ERROR THEN
1110 ROLLBACK TO Match_ChannelWithItems_Pvt;
1111 x_return_status := FND_API.G_RET_STS_ERROR;
1112 -- Standard call to get message count and if count=1, get the message
1113 FND_MSG_PUB.Count_And_Get (
1114 p_encoded => FND_API.G_FALSE,
1115 p_count => x_msg_count,
1116 p_data => x_msg_data
1117 );
1118 WHEN OTHERS THEN
1119 ROLLBACK TO Match_ChannelWithItems_Pvt;
1120 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1121 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1122 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1123 END IF;
1124 -- Standard call to get message count and if count=1, get the message
1125 FND_MSG_PUB.Count_And_Get (
1126 p_encoded => FND_API.G_FALSE,
1127 p_count => x_msg_count,
1128 p_data => x_msg_data
1129 );
1130 END Match_ChannelWithItems;
1131 --------------------------------------------------------------------------------
1132 PROCEDURE Check_ExistItemChlMatch
1133 (
1134 p_api_version IN NUMBER,
1135 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1136 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1137 x_return_status OUT NOCOPY VARCHAR2,
1138 x_msg_count OUT NOCOPY NUMBER,
1139 x_msg_data OUT NOCOPY VARCHAR2,
1140 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1141 p_channel_id IN NUMBER,
1142 p_item_id IN NUMBER,
1143 x_match_exist_flag OUT NOCOPY VARCHAR2,
1144 x_approval_status OUT NOCOPY VARCHAR2
1145 ) IS
1146 l_api_name CONSTANT VARCHAR2(30) := 'Check_ExistItemChlMatch';
1147 l_api_version CONSTANT NUMBER := 1.0;
1148 l_resource_id NUMBER := -1;
1149 l_current_user_id NUMBER := -1;
1150 l_current_login_id NUMBER := -1;
1151 l_current_user_status VARCHAR2(80);
1152 --
1153 CURSOR Get_ChlItemApprovalStatus_csr IS
1154 Select
1155 approval_status_type
1156 From amv_c_chl_item_match
1157 Where item_id = p_item_id
1158 And channel_id = p_channel_id
1159 And table_name_code = G_MATCH_ITEM_TABLE;
1160 --
1161 --
1162 BEGIN
1163 IF NOT FND_API.Compatible_API_Call (
1164 l_api_version,
1165 p_api_version,
1166 l_api_name,
1167 G_PKG_NAME) THEN
1168 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1169 END IF;
1170 --Initialize message list if p_init_msg_list is TRUE.
1171 IF FND_API.To_Boolean (p_init_msg_list) THEN
1172 FND_MSG_PUB.initialize;
1173 END IF;
1174 -- Initialize API return status to success
1175 x_return_status := FND_API.G_RET_STS_SUCCESS;
1176 -- Get the current (login) user id.
1177 AMV_UTILITY_PVT.Get_UserInfo(
1178 x_resource_id => l_resource_id,
1179 x_user_id => l_current_user_id,
1180 x_login_id => l_current_login_id,
1181 x_user_status => l_current_user_status
1182 );
1183 IF (p_check_login_user = FND_API.G_TRUE) THEN
1184 -- Check if user is login and has the required privilege.
1185 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
1186 -- User is not login.
1187 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1188 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1189 FND_MSG_PUB.Add;
1190 END IF;
1191 RAISE FND_API.G_EXC_ERROR;
1192 END IF;
1193 END IF;
1194 -- This is to make database happy
1195 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
1196 l_current_login_id := -1;
1197 l_current_user_id := -1;
1198 END IF;
1199 --
1200 OPEN Get_ChlItemApprovalStatus_csr;
1201 FETCH Get_ChlItemApprovalStatus_csr INTO x_approval_status;
1202 IF Get_ChlItemApprovalStatus_csr%FOUND THEN
1203 CLOSE Get_ChlItemApprovalStatus_csr;
1204 x_match_exist_flag := FND_API.G_TRUE;
1205 ELSE
1206 CLOSE Get_ChlItemApprovalStatus_csr;
1207 x_match_exist_flag := FND_API.G_FALSE;
1208 x_approval_status := FND_API.G_MISS_CHAR;
1209 END IF;
1210 --Standard call to get message count and if count=1, get the message
1211 FND_MSG_PUB.Count_And_Get (
1212 p_count => x_msg_count,
1213 p_data => x_msg_data
1214 );
1215 EXCEPTION
1216 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1217 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1218 -- Standard call to get message count and if count=1, get the message
1219 FND_MSG_PUB.Count_And_Get (
1220 p_encoded => FND_API.G_FALSE,
1221 p_count => x_msg_count,
1222 p_data => x_msg_data
1223 );
1224 WHEN FND_API.G_EXC_ERROR THEN
1225 x_return_status := FND_API.G_RET_STS_ERROR;
1226 -- Standard call to get message count and if count=1, get the message
1227 FND_MSG_PUB.Count_And_Get (
1228 p_encoded => FND_API.G_FALSE,
1229 p_count => x_msg_count,
1230 p_data => x_msg_data
1231 );
1232 WHEN OTHERS THEN
1233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1234 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1235 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1236 END IF;
1237 -- Standard call to get message count and if count=1, get the message
1238 FND_MSG_PUB.Count_And_Get (
1239 p_encoded => FND_API.G_FALSE,
1240 p_count => x_msg_count,
1241 p_data => x_msg_data
1242 );
1243 END Check_ExistItemChlMatch;
1244 --
1245 --------------------------------------------------------------------------------
1246 PROCEDURE Check_MatchingCondition
1247 (
1248 p_api_version IN NUMBER,
1249 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1250 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1251 x_return_status OUT NOCOPY VARCHAR2,
1252 x_msg_count OUT NOCOPY NUMBER,
1253 x_msg_data OUT NOCOPY VARCHAR2,
1254 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1255 p_item_id IN NUMBER,
1256 p_channel_id IN NUMBER,
1257 p_match_on_author_flag IN VARCHAR2,
1258 p_match_on_keyword_flag IN VARCHAR2,
1259 p_match_on_perspective_flag IN VARCHAR2,
1260 p_match_on_content_type_flag IN VARCHAR2,
1261 p_match_on_item_type_flag IN VARCHAR2,
1262 x_match_flag OUT NOCOPY VARCHAR2
1263 ) IS
1264 --NOTE: This procedure has lots of space for performance turning.
1265 l_api_name CONSTANT VARCHAR2(30) := 'Check_MatchingCondition';
1266 l_api_version CONSTANT NUMBER := 1.0;
1267 l_resource_id NUMBER := -1;
1268 l_current_user_id NUMBER := -1;
1269 l_current_login_id NUMBER := -1;
1270 l_current_user_status VARCHAR2(80);
1271 --
1272 l_content_type_id NUMBER := null;
1273 l_item_type VARCHAR2(30) := null;
1274 l_match_flag VARCHAR2(1) := FND_API.G_TRUE;
1275 l_tmp_number NUMBER;
1276 --
1277 CURSOR Get_ItemAndContentTypes_csr IS
1278 Select
1279 content_type_id,
1280 item_type
1281 From jtf_amv_items_b
1282 where item_id = p_item_id;
1283 --
1284 CURSOR Check_ContentType_Match_csr(p_content_type_id IN NUMBER) IS
1285 Select object_version_number
1286 From amv_c_content_types
1287 Where channel_id = p_channel_id
1288 And content_type_id = p_content_type_id;
1289 --
1290 CURSOR Check_ItemType_Match_csr(p_item_type IN VARCHAR2) IS
1291 Select object_version_number
1292 From amv_c_item_types
1293 Where channel_id = p_channel_id
1294 And item_type = l_item_type;
1295 --
1296 CURSOR Check_Author_Match_csr IS
1297 Select c.object_version_number
1298 From amv_c_authors c, jtf_amv_item_authors i
1299 Where c.channel_id = p_channel_id
1300 And i.item_id = p_item_id
1301 And i.author = c.author;
1302 --
1303 CURSOR Check_Keyword_Match_csr IS
1304 Select c.object_version_number
1305 From amv_c_keywords c, jtf_amv_item_keywords i
1306 Where c.channel_id = p_channel_id
1307 And i.item_id = p_item_id
1308 And i.keyword = c.keyword;
1309 --
1310 CURSOR Check_Perspective_Match_csr IS
1311 Select c.object_version_number
1312 From amv_c_chl_perspectives c, amv_i_item_perspectives i
1313 Where c.channel_id = p_channel_id
1314 And i.item_id = p_item_id
1315 And i.perspective_id = c.perspective_id;
1316 --
1317 BEGIN
1318 IF NOT FND_API.Compatible_API_Call (
1319 l_api_version,
1320 p_api_version,
1321 l_api_name,
1322 G_PKG_NAME) THEN
1323 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1324 END IF;
1325 --Initialize message list if p_init_msg_list is TRUE.
1326 IF FND_API.To_Boolean (p_init_msg_list) THEN
1327 FND_MSG_PUB.initialize;
1328 END IF;
1329 -- Initialize API return status to success
1330 x_return_status := FND_API.G_RET_STS_SUCCESS;
1331 IF (p_match_on_author_flag = FND_API.G_FALSE AND
1332 p_match_on_keyword_flag = FND_API.G_FALSE AND
1333 p_match_on_perspective_flag = FND_API.G_FALSE AND
1334 p_match_on_content_type_flag = FND_API.G_FALSE AND
1335 p_match_on_item_type_flag = FND_API.G_FALSE ) THEN
1336 x_match_flag := FND_API.G_FALSE;
1337 ELSE
1338 x_match_flag := FND_API.G_TRUE;
1339 END IF;
1340 -- Get the current (login) user id.
1341 AMV_UTILITY_PVT.Get_UserInfo(
1342 x_resource_id => l_resource_id,
1343 x_user_id => l_current_user_id,
1344 x_login_id => l_current_login_id,
1345 x_user_status => l_current_user_status
1346 );
1347 IF (p_check_login_user = FND_API.G_TRUE) THEN
1348 -- Check if user is login and has the required privilege.
1349 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
1350 -- User is not login.
1351 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1352 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1353 FND_MSG_PUB.Add;
1354 END IF;
1355 RAISE FND_API.G_EXC_ERROR;
1356 END IF;
1357 END IF;
1358 --
1359 IF (p_match_on_author_flag = FND_API.G_TRUE AND
1360 x_match_flag = FND_API.G_TRUE) THEN
1361 OPEN Check_Author_Match_csr;
1362 FETCH Check_Author_Match_csr INTO l_tmp_number;
1363 IF (Check_Author_Match_csr%NOTFOUND) THEN
1364 x_match_flag := FND_API.G_FALSE;
1365 END IF;
1366 CLOSE Check_Author_Match_csr;
1367 END IF;
1368 IF (p_match_on_keyword_flag = FND_API.G_TRUE AND
1369 x_match_flag = FND_API.G_TRUE) THEN
1370 OPEN Check_Keyword_Match_csr;
1371 FETCH Check_Keyword_Match_csr INTO l_tmp_number;
1372 IF (Check_Keyword_Match_csr%NOTFOUND) THEN
1373 x_match_flag := FND_API.G_FALSE;
1374 END IF;
1375 CLOSE Check_Keyword_Match_csr;
1376 END IF;
1377 IF (p_match_on_perspective_flag = FND_API.G_TRUE AND
1378 x_match_flag = FND_API.G_TRUE) THEN
1379 OPEN Check_Perspective_Match_csr;
1380 FETCH Check_Perspective_Match_csr INTO l_tmp_number;
1381 IF (Check_Perspective_Match_csr%NOTFOUND) THEN
1382 x_match_flag := FND_API.G_FALSE;
1383 END IF;
1384 CLOSE Check_Perspective_Match_csr;
1385 END IF;
1386 IF (x_match_flag = FND_API.G_TRUE AND
1387 (p_match_on_content_type_flag = FND_API.G_TRUE OR
1388 p_match_on_item_type_flag = FND_API.G_TRUE) ) THEN
1389 OPEN Get_ItemAndContentTypes_csr;
1390 FETCH Get_ItemAndContentTypes_csr
1391 INTO l_content_type_id, l_item_type;
1392 IF (Get_ItemAndContentTypes_csr%NOTFOUND) THEN
1393 x_match_flag := FND_API.G_FALSE;
1394 END IF;
1395 CLOSE Get_ItemAndContentTypes_csr;
1396 IF (x_match_flag = FND_API.G_TRUE AND
1397 p_match_on_content_type_flag = FND_API.G_TRUE) THEN
1398 OPEN Check_ContentType_Match_csr(l_content_type_id);
1399 FETCH Check_ContentType_Match_csr INTO l_tmp_number;
1400 IF (Check_ContentType_Match_csr%NOTFOUND) THEN
1401 x_match_flag := FND_API.G_FALSE;
1402 END IF;
1403 CLOSE Check_ContentType_Match_csr;
1404 END IF;
1405 IF (x_match_flag = FND_API.G_TRUE AND
1406 p_match_on_item_type_flag = FND_API.G_TRUE) THEN
1407 OPEN Check_ItemType_Match_csr(l_item_type);
1408 FETCH Check_ItemType_Match_csr INTO l_tmp_number;
1409 IF (Check_ItemType_Match_csr%NOTFOUND) THEN
1410 x_match_flag := FND_API.G_FALSE;
1411 END IF;
1412 CLOSE Check_ItemType_Match_csr;
1413 END IF;
1414 END IF;
1415 --Standard call to get message count and if count=1, get the message
1416 FND_MSG_PUB.Count_And_Get (
1417 p_encoded => FND_API.G_FALSE,
1418 p_count => x_msg_count,
1419 p_data => x_msg_data
1420 );
1421 EXCEPTION
1422 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1423 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1424 x_match_flag := FND_API.G_FALSE;
1425 -- Standard call to get message count and if count=1, get the message
1426 FND_MSG_PUB.Count_And_Get (
1427 p_encoded => FND_API.G_FALSE,
1428 p_count => x_msg_count,
1429 p_data => x_msg_data
1430 );
1431 WHEN FND_API.G_EXC_ERROR THEN
1432 x_return_status := FND_API.G_RET_STS_ERROR;
1433 x_match_flag := FND_API.G_FALSE;
1434 -- Standard call to get message count and if count=1, get the message
1435 FND_MSG_PUB.Count_And_Get (
1436 p_encoded => FND_API.G_FALSE,
1437 p_count => x_msg_count,
1438 p_data => x_msg_data
1439 );
1440 WHEN OTHERS THEN
1441 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1442 x_match_flag := FND_API.G_FALSE;
1443 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1444 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1445 END IF;
1446 -- Standard call to get message count and if count=1, get the message
1447 FND_MSG_PUB.Count_And_Get (
1448 p_encoded => FND_API.G_FALSE,
1449 p_count => x_msg_count,
1450 p_data => x_msg_data
1451 );
1452 END Check_MatchingCondition;
1453 --
1454 --------------------------------------------------------------------------------
1455 PROCEDURE Check_MatchingCondition2
1456 (
1457 p_api_version IN NUMBER,
1458 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1459 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1460 x_return_status OUT NOCOPY VARCHAR2,
1461 x_msg_count OUT NOCOPY NUMBER,
1462 x_msg_data OUT NOCOPY VARCHAR2,
1463 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1464 p_item_id IN NUMBER,
1465 p_channel_id IN NUMBER,
1466 p_match_on_author_flag IN VARCHAR2,
1467 p_match_on_keyword_flag IN VARCHAR2,
1468 p_match_on_perspective_flag IN VARCHAR2,
1469 p_match_on_content_type_flag IN VARCHAR2,
1470 p_match_on_item_type_flag IN VARCHAR2,
1471 x_match_flag OUT NOCOPY VARCHAR2
1472 ) IS
1473 l_api_name CONSTANT VARCHAR2(30) := 'Check_MatchingCondition';
1474 l_api_version CONSTANT NUMBER := 1.0;
1475 l_resource_id NUMBER := -1;
1476 l_current_user_id NUMBER := -1;
1477 l_current_login_id NUMBER := -1;
1478 l_current_user_status VARCHAR2(80);
1479 --
1480 l_cursor CursorType;
1481 l_sql_statement VARCHAR2(2000);
1482 l_where_clause VARCHAR2(2000);
1483 l_tmp_number NUMBER;
1484 --
1485 BEGIN
1486 IF NOT FND_API.Compatible_API_Call (
1487 l_api_version,
1488 p_api_version,
1489 l_api_name,
1490 G_PKG_NAME) THEN
1491 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1492 END IF;
1493 --Initialize message list if p_init_msg_list is TRUE.
1494 IF FND_API.To_Boolean (p_init_msg_list) THEN
1495 FND_MSG_PUB.initialize;
1496 END IF;
1497 -- Initialize API return status to success
1498 x_return_status := FND_API.G_RET_STS_SUCCESS;
1499 IF (p_match_on_author_flag = FND_API.G_FALSE AND
1500 p_match_on_keyword_flag = FND_API.G_FALSE AND
1501 p_match_on_perspective_flag = FND_API.G_FALSE AND
1502 p_match_on_content_type_flag = FND_API.G_FALSE AND
1503 p_match_on_item_type_flag = FND_API.G_FALSE ) THEN
1504 x_match_flag := FND_API.G_FALSE;
1505 RAISE FND_API.G_EXC_ERROR;
1506 ELSE
1507 x_match_flag := FND_API.G_TRUE;
1508 END IF;
1509 -- Get the current (login) user id.
1510 AMV_UTILITY_PVT.Get_UserInfo(
1511 x_resource_id => l_resource_id,
1512 x_user_id => l_current_user_id,
1513 x_login_id => l_current_login_id,
1514 x_user_status => l_current_user_status
1515 );
1516 IF (p_check_login_user = FND_API.G_TRUE) THEN
1517 -- Check if user is login and has the required privilege.
1518 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
1519 -- User is not login.
1520 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1521 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1522 FND_MSG_PUB.Add;
1523 END IF;
1524 x_match_flag := FND_API.G_FALSE;
1525 RAISE FND_API.G_EXC_ERROR;
1526 END IF;
1527 END IF;
1528 --
1529 l_sql_statement := 'Select 1 ' ||
1530 'From jtf_amv_items_b item, amv_c_channels_b chan ';
1531 l_where_clause := 'Where item.item_id = :item_id ' ||
1532 'And chan.channel_id = :channel_id ';
1533 IF (p_match_on_author_flag = FND_API.G_TRUE) THEN
1534 l_where_clause := l_where_clause ||
1535 'And exists (select 1 from amv_c_authors ca, ' ||
1536 ' jtf_amv_item_authors ia ' ||
1537 'where ca.channel_id = chan.channel_id ' ||
1538 'and ia.item_id = item.item_id ' ||
1539 'and ca.author = ia.author) ';
1540 END IF;
1541 IF (p_match_on_keyword_flag = FND_API.G_TRUE) THEN
1542 l_where_clause := l_where_clause ||
1543 'And exists (select 1 from amv_c_keywords ck, ' ||
1544 ' jtf_amv_item_keywords ik ' ||
1545 'where ck.channel_id = chan.channel_id ' ||
1546 'and ik.item_id = item.item_id ' ||
1547 'and ck.keyword = ik.keyword) ';
1548 END IF;
1549 IF (p_match_on_perspective_flag = FND_API.G_TRUE) THEN
1550 l_where_clause := l_where_clause ||
1551 'And exists (select 1 from amv_c_chl_perspectives cp, ' ||
1552 'amv_i_item_perspectives ip ' ||
1553 'where cp.channel_id = chan.channel_id ' ||
1554 'and ip.item_id = item.item_id ' ||
1555 'and cp.perspective_id = ip.perspective_id) ';
1556 END IF;
1557 IF (p_match_on_content_type_flag = FND_API.G_TRUE) THEN
1558 l_where_clause := l_where_clause ||
1559 'And exists (select 1 from amv_c_content_types cc ' ||
1560 'where cc.channel_id = chan.channel_id ' ||
1561 'and cc.content_type_id = item.content_type_id) ';
1562 END IF;
1563 IF (p_match_on_item_type_flag = FND_API.G_TRUE) THEN
1564 l_where_clause := l_where_clause ||
1565 'And exists (select 1 from amv_c_item_types ci ' ||
1566 'where ci.channel_id = chan.channel_id ' ||
1567 'and ci.item_type = item.item_type) ';
1568 END IF;
1569 l_sql_statement := l_sql_statement || l_where_clause;
1570 IF (G_DEBUG = TRUE) THEN
1571 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(
1572 '*************SQL Statement*************');
1573 AMV_UTILITY_PVT.PRINT_DEBUG_MESSAGE(l_sql_statement);
1574 END IF;
1575 -- Now do the execution.
1576 OPEN l_cursor FOR l_sql_statement USING p_item_id, p_channel_id;
1577 FETCH l_cursor INTO l_tmp_number;
1578 IF (l_cursor%FOUND) THEN
1579 x_match_flag := FND_API.G_TRUE;
1580 ELSE
1581 x_match_flag := FND_API.G_FALSE;
1582 END IF;
1583 CLOSE l_cursor;
1584 --Standard call to get message count and if count=1, get the message
1585 FND_MSG_PUB.Count_And_Get (
1586 p_encoded => FND_API.G_FALSE,
1587 p_count => x_msg_count,
1588 p_data => x_msg_data
1589 );
1590 EXCEPTION
1591 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1592 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1593 x_match_flag := FND_API.G_FALSE;
1594 -- Standard call to get message count and if count=1, get the message
1595 FND_MSG_PUB.Count_And_Get (
1596 p_encoded => FND_API.G_FALSE,
1597 p_count => x_msg_count,
1598 p_data => x_msg_data
1599 );
1600 WHEN FND_API.G_EXC_ERROR THEN
1601 x_return_status := FND_API.G_RET_STS_ERROR;
1602 x_match_flag := FND_API.G_FALSE;
1603 -- Standard call to get message count and if count=1, get the message
1604 FND_MSG_PUB.Count_And_Get (
1605 p_encoded => FND_API.G_FALSE,
1606 p_count => x_msg_count,
1607 p_data => x_msg_data
1608 );
1609 WHEN OTHERS THEN
1610 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1611 x_match_flag := FND_API.G_FALSE;
1612 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1613 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1614 END IF;
1615 -- Standard call to get message count and if count=1, get the message
1616 FND_MSG_PUB.Count_And_Get (
1617 p_encoded => FND_API.G_FALSE,
1618 p_count => x_msg_count,
1619 p_data => x_msg_data
1620 );
1621 END Check_MatchingCondition2;
1622 --
1623 --------------------------------------------------------------------------------
1624 PROCEDURE Do_ItemChannelMatch
1625 (
1626 p_api_version IN NUMBER,
1627 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1628 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1629 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1630 x_return_status OUT NOCOPY VARCHAR2,
1631 x_msg_count OUT NOCOPY NUMBER,
1632 x_msg_data OUT NOCOPY VARCHAR2,
1633 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1634 p_application_id IN NUMBER := G_AMV_APP_ID,
1635 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
1636 p_channel_id IN NUMBER := FND_API.G_MISS_NUM,
1637 p_item_id IN NUMBER,
1638 p_table_name_code IN VARCHAR2,
1639 p_match_type IN VARCHAR2
1640 ) IS
1641 l_api_name CONSTANT VARCHAR2(30) := 'Do_ItemChannelMatch';
1642 l_api_version CONSTANT NUMBER := 1.0;
1643 l_resource_id NUMBER := -1;
1644 l_current_user_id NUMBER := -1;
1645 l_current_login_id NUMBER := -1;
1646 l_current_user_status VARCHAR2(80);
1647 --
1648 l_pub_need_approval_f VARCHAR2(1);
1649 l_channel_category_id NUMBER;
1650 l_match_id NUMBER;
1651 l_current_date DATE;
1652 l_approval_flag VARCHAR2(30);
1653 l_owner_name VARCHAR2(100);
1654 l_owner_id NUMBER;
1655 l_temp_id NUMBER := FND_API.G_MISS_NUM;
1656 --
1657 CURSOR Check_ExistMatch IS
1658 Select CHANNEL_ITEM_ID
1659 From AMV_C_CHL_ITEM_MATCH
1660 Where TABLE_NAME_CODE = p_table_name_code
1661 And ITEM_ID = p_item_id
1662 And CHANNEL_ID = p_channel_id
1663 ;
1664 --Updated to use current resource_id and not channel_owner
1665 CURSOR Get_OwnerIDAndName_csr (res_id IN NUMBER)IS
1666 select u.user_name,
1667 r.resource_id
1668 From amv_rs_all_res_extns_vl r
1669 , fnd_user u
1670 where r.resource_id = res_id
1671 and u.user_id = r.user_id;
1672 --
1673 CURSOR Get_ChannelInfo_csr IS
1674 select
1675 pub_need_approval_flag,
1676 channel_category_id,
1677 AMV_C_CHL_ITEM_MATCH_S.nextval,
1678 sysdate
1679 from amv_c_channels_b
1680 Where channel_id = p_channel_id;
1681 --
1682 CURSOR Get_IdAndDate_csr IS
1683 select
1684 AMV_C_CHL_ITEM_MATCH_S.nextval,
1685 sysdate
1686 from dual;
1687 --
1688 CURSOR Get_Resourceid_csr IS
1689 select owner_id
1690 from jtf_amv_items_b
1691 where item_id = p_item_id;
1692 --
1693 BEGIN
1694 SAVEPOINT Do_ItemChannelMatch_Pvt;
1695 IF NOT FND_API.Compatible_API_Call (
1696 l_api_version,
1697 p_api_version,
1698 l_api_name,
1699 G_PKG_NAME) THEN
1700 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1701 END IF;
1702 --Initialize message list if p_init_msg_list is TRUE.
1703 IF FND_API.To_Boolean (p_init_msg_list) THEN
1704 FND_MSG_PUB.initialize;
1705 END IF;
1706 -- Initialize API return status to success
1707 x_return_status := FND_API.G_RET_STS_SUCCESS;
1708 -- Get the current (login) user id.
1709 AMV_UTILITY_PVT.Get_UserInfo(
1710 x_resource_id => l_resource_id,
1711 x_user_id => l_current_user_id,
1712 x_login_id => l_current_login_id,
1713 x_user_status => l_current_user_status
1714 );
1715
1716 -- Added by Matching Engine
1717 -- Matching would not have Resouce
1718 -- Picking up from Item id.
1719 IF (l_current_user_status = 'NORESOURCE') THEN
1720 OPEN Get_Resourceid_csr;
1721 FETCH Get_Resourceid_csr INTO l_resource_id;
1722 IF (Get_Resourceid_csr%NOTFOUND) THEN
1723 RAISE FND_API.G_EXC_ERROR;
1724 END IF;
1725 CLOSE Get_Resourceid_csr;
1726 END IF;
1727
1728 IF (p_check_login_user = FND_API.G_TRUE) THEN
1729 -- Check if user is login and has the required privilege.
1730 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
1731 -- User is not login.
1732 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1733 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
1734 FND_MSG_PUB.Add;
1735 END IF;
1736 RAISE FND_API.G_EXC_ERROR;
1737 END IF;
1738 END IF;
1739 -- This is to make database happy
1740 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
1741 l_current_login_id := -1;
1742 l_current_user_id := -1;
1743 END IF;
1744 IF (p_category_id = FND_API.G_MISS_NUM) THEN -- push item to channel
1745 OPEN Check_ExistMatch;
1746 FETCH Check_ExistMatch INTO l_temp_id;
1747 IF (Check_ExistMatch%NOTFOUND) THEN
1748 l_temp_id := FND_API.G_MISS_NUM;
1749 END IF;
1750 CLOSE Check_ExistMatch;
1751 IF (l_temp_id = FND_API.G_MISS_NUM) THEN
1752 --Get channel information (and thus check if the channel id is valid).
1753 OPEN Get_ChannelInfo_csr;
1754 FETCH Get_ChannelInfo_csr Into
1755 l_pub_need_approval_f,
1756 l_channel_category_id,
1757 l_match_id,
1758 l_current_date;
1759 IF (Get_ChannelInfo_csr%NOTFOUND) THEN
1760 CLOSE Get_ChannelInfo_csr;
1761 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1762 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
1763 FND_MESSAGE.Set_Token('RECORD', 'AMV_CHANNEL_TK', TRUE);
1764 FND_MESSAGE.Set_Token('ID', to_char(nvl(p_channel_id,-1)));
1765 FND_MSG_PUB.Add;
1766 END IF;
1767 RAISE FND_API.G_EXC_ERROR;
1768 END IF;
1769 CLOSE Get_ChannelInfo_csr;
1770 --
1771 IF (l_pub_need_approval_f = FND_API.G_TRUE) THEN
1772 l_approval_flag := AMV_UTILITY_PVT.G_NEED_APPROVAL;
1773 ELSE
1774 l_approval_flag := AMV_UTILITY_PVT.G_APPROVED;
1775 END IF;
1776 --
1777 --l_owner_name := 'TEST'; l_owner_id := 1;
1778 OPEN Get_OwnerIDAndName_csr (l_resource_id);
1779 FETCH Get_OwnerIDAndName_csr INTO l_owner_name, l_owner_id;
1780 IF (Get_OwnerIDAndName_csr%NOTFOUND) THEN
1781 CLOSE Get_OwnerIDAndName_csr;
1782 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1783 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
1784 FND_MESSAGE.Set_Token('RECORD', 'AMV_CHANNEL_TK', TRUE);
1785 FND_MESSAGE.Set_Token('ID', to_char(nvl(l_resource_id,-1)));
1786 FND_MSG_PUB.Add;
1787 END IF;
1788 RAISE FND_API.G_EXC_ERROR;
1789 END IF;
1790 CLOSE Get_OwnerIDAndName_csr;
1791 --
1792 Insert Into AMV_C_CHL_ITEM_MATCH
1793 (
1794 CHANNEL_ITEM_ID,
1795 OBJECT_VERSION_NUMBER,
1796 LAST_UPDATE_DATE,
1797 LAST_UPDATED_BY,
1798 CREATION_DATE,
1799 CREATED_BY,
1800 LAST_UPDATE_LOGIN,
1801 CHANNEL_ID,
1802 ITEM_ID,
1803 TABLE_NAME_CODE,
1804 CHANNEL_CATEGORY_ID,
1805 APPLICATION_ID,
1806 APPROVAL_STATUS_TYPE,
1807 APPROVAL_DATE,
1808 AVAILABLE_DUE_TO_TYPE,
1809 AVAILABLE_FOR_CHANNEL_DATE
1810 )
1811 VALUES
1812 (
1813 l_match_id,
1814 1,
1815 l_current_date,
1816 l_current_user_id,
1817 l_current_date,
1818 l_current_user_id,
1819 l_current_login_id,
1820 p_channel_id,
1821 p_item_id,
1822 p_table_name_code,
1823 l_channel_category_id,
1824 p_application_id,
1825 l_approval_flag,
1826 l_current_date,
1827 p_match_type,
1828 l_current_date
1829 );
1830 -- start SLKRISHN's workflow approval process
1831 amv_wfapproval_pvt.StartProcess
1832 (
1833 RequestorId => l_resource_id,
1834 ItemId => p_item_id,
1835 ChannelId => p_channel_id,
1836 ProcessOwner => l_owner_name,
1837 Workflowprocess => 'AMV_CONTENT_APPROVAL'
1838 );
1839 END IF;
1840 ELSE -- add the item to the category.
1841 --check category id
1842 IF (AMV_UTILITY_PVT.Is_CategoryIdValid(p_category_id) <> TRUE) THEN
1843 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1844 FND_MESSAGE.Set_name('AMV','AMV_INVALID_CATEGORY_ID');
1845 FND_MESSAGE.Set_Token('ID', to_char(nvl(p_category_id, -1)));
1846 FND_MSG_PUB.Add;
1847 END IF;
1848 RAISE FND_API.G_EXC_ERROR;
1849 END IF;
1850 --
1851 OPEN Get_IdAndDate_csr;
1852 FETCH Get_IdAndDate_csr Into l_match_id, l_current_date;
1853 CLOSE Get_IdAndDate_csr;
1854 Insert Into AMV_C_CHL_ITEM_MATCH
1855 (
1856 CHANNEL_ITEM_ID,
1857 OBJECT_VERSION_NUMBER,
1858 LAST_UPDATE_DATE,
1859 LAST_UPDATED_BY,
1860 CREATION_DATE,
1861 CREATED_BY,
1862 LAST_UPDATE_LOGIN,
1863 CHANNEL_ID,
1864 ITEM_ID,
1865 TABLE_NAME_CODE,
1866 CHANNEL_CATEGORY_ID,
1867 APPLICATION_ID,
1868 APPROVAL_STATUS_TYPE,
1869 AVAILABLE_DUE_TO_TYPE,
1870 AVAILABLE_FOR_CHANNEL_DATE
1871 )
1872 VALUES
1873 (
1874 l_match_id,
1875 1,
1876 l_current_date,
1877 l_current_user_id,
1878 l_current_date,
1879 l_current_user_id,
1880 l_current_login_id,
1881 null,
1882 p_item_id,
1883 p_table_name_code,
1884 p_category_id,
1885 p_application_id,
1886 AMV_UTILITY_PVT.G_APPROVED,
1887 p_match_type,
1888 l_current_date
1889 );
1890 END IF;
1891 --Standard check of commit
1892 IF FND_API.To_Boolean ( p_commit ) THEN
1893 COMMIT WORK;
1894 END IF;
1895 --Standard call to get message count and if count=1, get the message
1896 FND_MSG_PUB.Count_And_Get (
1897 p_encoded => FND_API.G_FALSE,
1898 p_count => x_msg_count,
1899 p_data => x_msg_data
1900 );
1901 EXCEPTION
1902 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1903 ROLLBACK TO Do_ItemChannelMatch_Pvt;
1904 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1905 -- Standard call to get message count and if count=1, get the message
1906 FND_MSG_PUB.Count_And_Get (
1907 p_encoded => FND_API.G_FALSE,
1908 p_count => x_msg_count,
1909 p_data => x_msg_data
1910 );
1911 WHEN FND_API.G_EXC_ERROR THEN
1912 ROLLBACK TO Do_ItemChannelMatch_Pvt;
1913 x_return_status := FND_API.G_RET_STS_ERROR;
1914 -- Standard call to get message count and if count=1, get the message
1915 FND_MSG_PUB.Count_And_Get (
1916 p_encoded => FND_API.G_FALSE,
1917 p_count => x_msg_count,
1918 p_data => x_msg_data
1919 );
1920 WHEN OTHERS THEN
1921 ROLLBACK TO Do_ItemChannelMatch_Pvt;
1922 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1923 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1924 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1925 END IF;
1926 -- Standard call to get message count and if count=1, get the message
1927 FND_MSG_PUB.Count_And_Get (
1928 p_encoded => FND_API.G_FALSE,
1929 p_count => x_msg_count,
1930 p_data => x_msg_data
1931 );
1932 END Do_ItemChannelMatch;
1933 --
1934 --------------------------------------------------------------------------------
1935 -- Do_ItemChannelMatch(Overloaded) --
1936 --
1937 PROCEDURE Do_ItemChannelMatch
1938 (
1939 p_api_version IN NUMBER,
1940 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1941 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1942 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1943 x_return_status OUT NOCOPY VARCHAR2,
1944 x_msg_count OUT NOCOPY NUMBER,
1945 x_msg_data OUT NOCOPY VARCHAR2,
1946 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
1947 p_application_id IN NUMBER := G_AMV_APP_ID,
1948 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
1949 p_channel_id IN NUMBER := FND_API.G_MISS_NUM,
1950 p_item_id IN NUMBER,
1951 p_table_name_code IN VARCHAR2,
1952 p_match_type IN VARCHAR2,
1953 p_territory_tbl IN terr_id_tbl_type
1954 ) IS
1955 l_api_name CONSTANT VARCHAR2(30) := 'Do_ItemChannelMatch';
1956 l_api_version CONSTANT NUMBER := 1.0;
1957 l_resource_id NUMBER := -1;
1958 l_current_user_id NUMBER := -1;
1959 l_current_login_id NUMBER := -1;
1960 l_current_user_status VARCHAR2(80);
1961 --
1962 l_pub_need_approval_f VARCHAR2(1);
1963 l_channel_category_id NUMBER;
1964 l_match_id NUMBER;
1965 l_current_date DATE;
1966 l_approval_flag VARCHAR2(30);
1967 l_owner_name VARCHAR2(100);
1968 l_owner_id NUMBER;
1969 l_temp_id NUMBER := FND_API.G_MISS_NUM;
1970 l_item_id NUMBER;
1971 l_match_item_terr VARCHAR2(1);
1972 l_match_item_catg VARCHAR2(1);
1973 l_rec_count NUMBER;
1974 --
1975
1976 -- Cursor to check for existence for item to territory match
1977 CURSOR c_exist_item_terr_match (cv_table_name_code VARCHAR2
1978 ,cv_item_id NUMBER
1979 ,cv_territory_id NUMBER
1980 ) IS
1981 SELECT item_id
1982 FROM amv_c_chl_item_match
1983 WHERE table_name_code = cv_table_name_code
1984 AND item_id = cv_item_id
1985 AND territory_id = cv_territory_id
1986 AND channel_id IS NULL
1987 AND channel_category_id IS NULL;
1988
1989 -- Cursor to check for existence for item to category match
1990 CURSOR c_exist_item_catg_match (cv_table_name_code VARCHAR2
1991 ,cv_item_id NUMBER
1992 ,cv_category_id NUMBER
1993 ) IS
1994 SELECT item_id
1995 FROM amv_c_chl_item_match
1996 WHERE table_name_code = cv_table_name_code
1997 AND item_id = cv_item_id
1998 AND channel_category_id = cv_category_id
1999 AND channel_id IS NULL;
2000
2001
2002 CURSOR Check_ExistMatch IS
2003 Select CHANNEL_ITEM_ID
2004 From AMV_C_CHL_ITEM_MATCH
2005 Where TABLE_NAME_CODE = p_table_name_code
2006 And ITEM_ID = p_item_id
2007 And CHANNEL_ID = p_channel_id
2008 ;
2009 --Updated to use current resource_id and not channel_owner
2010 CURSOR Get_OwnerIDAndName_csr (res_id IN NUMBER)IS
2011 select u.user_name,
2012 r.resource_id
2013 From amv_rs_all_res_extns_vl r
2014 , fnd_user u
2015 where r.resource_id = res_id
2016 and u.user_id = r.user_id;
2017 --
2018 CURSOR Get_ChannelInfo_csr IS
2019 select
2020 pub_need_approval_flag,
2021 channel_category_id,
2022 AMV_C_CHL_ITEM_MATCH_S.nextval,
2023 sysdate
2024 from amv_c_channels_b
2025 Where channel_id = p_channel_id;
2026 --
2027 CURSOR Get_IdAndDate_csr IS
2028 select
2029 AMV_C_CHL_ITEM_MATCH_S.nextval,
2030 sysdate
2031 from dual;
2032 --
2033 BEGIN
2034 SAVEPOINT Do_ItemChannelMatch_Pvt;
2035 IF NOT FND_API.Compatible_API_Call (
2036 l_api_version,
2037 p_api_version,
2038 l_api_name,
2039 G_PKG_NAME) THEN
2040 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2041 END IF;
2042 --Initialize message list if p_init_msg_list is TRUE.
2043 IF FND_API.To_Boolean (p_init_msg_list) THEN
2044 FND_MSG_PUB.initialize;
2045 END IF;
2046 -- Initialize API return status to success
2047 x_return_status := FND_API.G_RET_STS_SUCCESS;
2048 -- Get the current (login) user id.
2049 AMV_UTILITY_PVT.Get_UserInfo(
2050 x_resource_id => l_resource_id,
2051 x_user_id => l_current_user_id,
2052 x_login_id => l_current_login_id,
2053 x_user_status => l_current_user_status
2054 );
2055 IF (p_check_login_user = FND_API.G_TRUE) THEN
2056 -- Check if user is login and has the required privilege.
2057 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
2058 -- User is not login.
2059 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2060 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2061 FND_MSG_PUB.Add;
2062 END IF;
2063 RAISE FND_API.G_EXC_ERROR;
2064 END IF;
2065 END IF;
2066 -- This is to make database happy
2067 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
2068 l_current_login_id := -1;
2069 l_current_user_id := -1;
2070 END IF;
2071
2072 --*******
2073 -- Conditional match for item with the territory
2074 IF p_territory_tbl.COUNT <> 0 THEN
2075 l_rec_count := 1;
2076 LOOP
2077 -- Open cursor c_exist_item_terr_match to see if the match exists
2078 OPEN c_exist_item_terr_match(p_table_name_code
2079 ,p_item_id
2080 ,p_territory_tbl(l_rec_count));
2081 FETCH c_exist_item_terr_match INTO l_item_id;
2082 IF c_exist_item_terr_match%NOTFOUND THEN
2083 l_match_item_terr := 'Y';
2084 ELSE
2085 l_match_item_terr := 'N';
2086 END IF;
2087 CLOSE c_exist_item_terr_match;
2088
2089 IF l_match_item_terr = 'Y' THEN
2090 -- Open the curosr to get the record id and the 'WHO' date column value
2091 --DBMS_OUTPUT.PUT_LINE('l_match_item_terr = '||l_match_item_terr);
2092 OPEN Get_IdAndDate_csr;
2093 FETCH Get_IdAndDate_csr Into l_match_id, l_current_date;
2094 CLOSE Get_IdAndDate_csr;
2095 --DBMS_OUTPUT.PUT_LINE('l_match_id = '||to_char(l_match_id));
2096 --DBMS_OUTPUT.PUT_LINE('l_current_date = '||l_current_date);
2097 --DBMS_OUTPUT.PUT_LINE('Hello');
2098 --DBMS_OUTPUT.PUT_LINE('p_channel_id = '||NVL(p_channel_id,0));
2099 --DBMS_OUTPUT.PUT_LINE('p_item_id = '||to_char(p_item_id));
2100 -- Create the match record
2101 INSERT INTO AMV_C_CHL_ITEM_MATCH
2102 (CHANNEL_ITEM_ID
2103 ,OBJECT_VERSION_NUMBER
2104 ,LAST_UPDATE_DATE
2105 ,LAST_UPDATED_BY
2106 ,CREATION_DATE
2107 ,CREATED_BY
2108 ,LAST_UPDATE_LOGIN
2109 ,CHANNEL_ID
2110 ,ITEM_ID
2111 ,TABLE_NAME_CODE
2112 ,CHANNEL_CATEGORY_ID
2113 ,APPLICATION_ID
2114 ,APPROVAL_STATUS_TYPE
2115 ,AVAILABLE_DUE_TO_TYPE
2116 ,AVAILABLE_FOR_CHANNEL_DATE
2117 ,TERRITORY_ID
2118 )
2119 VALUES
2120 (l_match_id -- CHANNEL_ITEM_ID
2121 ,1 -- OBJECT_VERSION_NUMBER
2122 ,l_current_date -- LAST_UPDATE_DATE
2123 ,l_current_user_id -- LAST_UPDATED_BY
2124 ,l_current_date -- CREATION_DATE
2125 ,l_current_user_id -- CREATION_BY
2126 ,l_current_login_id -- LAST_UPDATE_LOGIN
2127 ,NULL -- CHANNEL_ID
2128 ,p_item_id -- ITEM_ID
2129 ,p_table_name_code -- TABLE_NAME_CODE
2130 ,NULL -- CHANNEL_CATEGORY_ID
2131 ,p_application_id -- APPLICATION_ID
2132 ,AMV_UTILITY_PVT.G_APPROVED -- APPROVAL_STATUS_TYPE
2133 ,p_match_type -- AVAILABLE_DUE_TO_TYPE
2134 ,l_current_date -- AVAILABLE_FOR_CHANNEL_DATE
2135 ,p_territory_tbl(l_rec_count) -- TERRITORY_ID
2136 );
2137 END IF; -- l_match_item_terr,Territory Logic
2138 EXIT WHEN l_rec_count = p_territory_tbl.COUNT;
2139 l_rec_count := l_rec_count + 1;
2140 END LOOP;
2141 ELSE -- Proceed with the regular logic
2142 --*******
2143
2144 IF (p_category_id = FND_API.G_MISS_NUM) THEN -- push item to channel
2145 OPEN Check_ExistMatch;
2146 FETCH Check_ExistMatch INTO l_temp_id;
2147 IF (Check_ExistMatch%NOTFOUND) THEN
2148 l_temp_id := FND_API.G_MISS_NUM;
2149 END IF;
2150 CLOSE Check_ExistMatch;
2151 IF (l_temp_id = FND_API.G_MISS_NUM) THEN
2152 --Get channel information (and thus check if the channel id is valid).
2153 OPEN Get_ChannelInfo_csr;
2154 FETCH Get_ChannelInfo_csr Into
2155 l_pub_need_approval_f,
2156 l_channel_category_id,
2157 l_match_id,
2158 l_current_date;
2159 IF (Get_ChannelInfo_csr%NOTFOUND) THEN
2160 CLOSE Get_ChannelInfo_csr;
2161 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2162 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
2163 FND_MESSAGE.Set_Token('RECORD', 'AMV_CHANNEL_TK', TRUE);
2164 FND_MESSAGE.Set_Token('ID', to_char(nvl(p_channel_id,-1)));
2165 FND_MSG_PUB.Add;
2166 END IF;
2167 RAISE FND_API.G_EXC_ERROR;
2168 END IF;
2169 CLOSE Get_ChannelInfo_csr;
2170 --
2171 IF (l_pub_need_approval_f = FND_API.G_TRUE) THEN
2172 l_approval_flag := AMV_UTILITY_PVT.G_NEED_APPROVAL;
2173 ELSE
2174 l_approval_flag := AMV_UTILITY_PVT.G_APPROVED;
2175 END IF;
2176 --
2177 --l_owner_name := 'TEST'; l_owner_id := 1;
2178 OPEN Get_OwnerIDAndName_csr (l_resource_id);
2179 FETCH Get_OwnerIDAndName_csr INTO l_owner_name, l_owner_id;
2180 IF (Get_OwnerIDAndName_csr%NOTFOUND) THEN
2181 CLOSE Get_OwnerIDAndName_csr;
2182 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2183 FND_MESSAGE.Set_name('AMV','AMV_RECORD_ID_MISSING');
2184 FND_MESSAGE.Set_Token('RECORD', 'AMV_CHANNEL_TK', TRUE);
2185 FND_MESSAGE.Set_Token('ID', to_char(nvl(l_resource_id,-1)));
2186 FND_MSG_PUB.Add;
2187 END IF;
2188 RAISE FND_API.G_EXC_ERROR;
2189 END IF;
2190 CLOSE Get_OwnerIDAndName_csr;
2191 --
2192 Insert Into AMV_C_CHL_ITEM_MATCH
2193 (
2194 CHANNEL_ITEM_ID,
2195 OBJECT_VERSION_NUMBER,
2196 LAST_UPDATE_DATE,
2197 LAST_UPDATED_BY,
2198 CREATION_DATE,
2199 CREATED_BY,
2200 LAST_UPDATE_LOGIN,
2201 CHANNEL_ID,
2202 ITEM_ID,
2203 TABLE_NAME_CODE,
2204 CHANNEL_CATEGORY_ID,
2205 APPLICATION_ID,
2206 APPROVAL_STATUS_TYPE,
2207 APPROVAL_DATE,
2208 AVAILABLE_DUE_TO_TYPE,
2209 AVAILABLE_FOR_CHANNEL_DATE
2210 )
2211 VALUES
2212 (
2213 l_match_id,
2214 1,
2215 l_current_date,
2216 l_current_user_id,
2217 l_current_date,
2218 l_current_user_id,
2219 l_current_login_id,
2220 p_channel_id,
2221 p_item_id,
2222 p_table_name_code,
2223 l_channel_category_id,
2224 p_application_id,
2225 l_approval_flag,
2226 l_current_date,
2227 p_match_type,
2228 l_current_date
2229 );
2230 -- start SLKRISHN's workflow approval process
2231 amv_wfapproval_pvt.StartProcess
2232 (
2233 RequestorId => l_resource_id,
2234 ItemId => p_item_id,
2235 ChannelId => p_channel_id,
2236 ProcessOwner => l_owner_name,
2237 Workflowprocess => 'AMV_CONTENT_APPROVAL'
2238 );
2239 END IF;
2240 ELSE -- add the item to the category.
2241 -- Open cursor c_exist_item_catg_match to see if the match exists
2242 OPEN c_exist_item_catg_match(p_table_name_code
2243 ,p_item_id
2244 ,p_category_id);
2245 FETCH c_exist_item_catg_match INTO l_item_id;
2246 IF c_exist_item_catg_match%NOTFOUND THEN
2247 l_match_item_catg := 'Y';
2248 ELSE
2249 l_match_item_catg := 'N';
2250 END IF;
2251 CLOSE c_exist_item_catg_match;
2252
2253 IF l_match_item_catg = 'Y' THEN
2254 --check category id
2255 IF (AMV_UTILITY_PVT.Is_CategoryIdValid(p_category_id) <> TRUE) THEN
2256 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2257 FND_MESSAGE.Set_name('AMV','AMV_INVALID_CATEGORY_ID');
2258 FND_MESSAGE.Set_Token('ID', to_char(nvl(p_category_id, -1)));
2259 FND_MSG_PUB.Add;
2260 END IF;
2261 RAISE FND_API.G_EXC_ERROR;
2262 END IF;
2263 --
2264 OPEN Get_IdAndDate_csr;
2265 FETCH Get_IdAndDate_csr Into l_match_id, l_current_date;
2266 CLOSE Get_IdAndDate_csr;
2267 Insert Into AMV_C_CHL_ITEM_MATCH
2268 (
2269 CHANNEL_ITEM_ID,
2270 OBJECT_VERSION_NUMBER,
2271 LAST_UPDATE_DATE,
2272 LAST_UPDATED_BY,
2273 CREATION_DATE,
2274 CREATED_BY,
2275 LAST_UPDATE_LOGIN,
2276 CHANNEL_ID,
2277 ITEM_ID,
2278 TABLE_NAME_CODE,
2279 CHANNEL_CATEGORY_ID,
2280 APPLICATION_ID,
2281 APPROVAL_STATUS_TYPE,
2282 AVAILABLE_DUE_TO_TYPE,
2283 AVAILABLE_FOR_CHANNEL_DATE
2284 )
2285 VALUES
2286 (
2287 l_match_id,
2288 1,
2289 l_current_date,
2290 l_current_user_id,
2291 l_current_date,
2292 l_current_user_id,
2293 l_current_login_id,
2294 null,
2295 p_item_id,
2296 p_table_name_code,
2297 p_category_id,
2298 p_application_id,
2299 AMV_UTILITY_PVT.G_APPROVED,
2300 p_match_type,
2301 l_current_date
2302 );
2303 END IF;
2304 END IF;
2305 END IF;
2306 --Standard check of commit
2307 IF FND_API.To_Boolean ( p_commit ) THEN
2308 COMMIT WORK;
2309 END IF;
2310 --Standard call to get message count and if count=1, get the message
2311 FND_MSG_PUB.Count_And_Get (
2312 p_encoded => FND_API.G_FALSE,
2313 p_count => x_msg_count,
2314 p_data => x_msg_data
2315 );
2316 EXCEPTION
2317 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2318 ROLLBACK TO Do_ItemChannelMatch_Pvt;
2319 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2320 -- Standard call to get message count and if count=1, get the message
2321 FND_MSG_PUB.Count_And_Get (
2322 p_encoded => FND_API.G_FALSE,
2323 p_count => x_msg_count,
2324 p_data => x_msg_data
2325 );
2326 WHEN FND_API.G_EXC_ERROR THEN
2327 ROLLBACK TO Do_ItemChannelMatch_Pvt;
2328 x_return_status := FND_API.G_RET_STS_ERROR;
2329 -- Standard call to get message count and if count=1, get the message
2330 FND_MSG_PUB.Count_And_Get (
2331 p_encoded => FND_API.G_FALSE,
2332 p_count => x_msg_count,
2333 p_data => x_msg_data
2334 );
2335 WHEN OTHERS THEN
2336 ROLLBACK TO Do_ItemChannelMatch_Pvt;
2337 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2338 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2339 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2340 END IF;
2341 -- Standard call to get message count and if count=1, get the message
2342 FND_MSG_PUB.Count_And_Get (
2343 p_encoded => FND_API.G_FALSE,
2344 p_count => x_msg_count,
2345 p_data => x_msg_data
2346 );
2347 END Do_ItemChannelMatch;
2348 --
2349 --------------------------------------------------------------------------------
2350 --------------------------------------------------------------------------------
2351 PROCEDURE Remove_ItemChannelMatch
2352 (
2353 p_api_version IN NUMBER,
2354 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2355 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2356 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2357 x_return_status OUT NOCOPY VARCHAR2,
2358 x_msg_count OUT NOCOPY NUMBER,
2359 x_msg_data OUT NOCOPY VARCHAR2,
2360 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2361 p_application_id IN NUMBER := G_AMV_APP_ID,
2362 p_category_id IN NUMBER := FND_API.G_MISS_NUM,
2363 p_channel_id IN NUMBER := FND_API.G_MISS_NUM,
2364 p_item_id IN NUMBER,
2365 p_table_name_code IN VARCHAR2,
2366 p_territory_id IN NUMBER := FND_API.G_MISS_NUM
2367 ) IS
2368 l_api_name CONSTANT VARCHAR2(30) := 'Remove_ItemChannelMatch';
2369 l_api_version CONSTANT NUMBER := 1.0;
2370 l_resource_id NUMBER := -1;
2371 l_current_user_id NUMBER := -1;
2372 l_current_login_id NUMBER := -1;
2373 l_current_user_status VARCHAR2(80);
2374 --
2375 l_pub_need_approval_f VARCHAR2(1);
2376 l_channel_category_id NUMBER;
2377 l_match_id NUMBER;
2378 l_current_date DATE;
2379 l_approval_flag VARCHAR2(30);
2380 l_owner_name VARCHAR2(100);
2381 l_owner_id NUMBER;
2382 --
2383 CURSOR Get_OwnerIDAndName_csr (p_id IN NUMBER)IS
2384 select
2385 u.user_name,
2386 c.owner_user_id
2387 From fnd_user u, jtf_rs_resource_extns r, amv_c_channels_b c
2388 Where c.channel_id = p_id
2389 And r.resource_id = c.owner_user_id
2390 And r.user_id = u.user_id
2391 ;
2392 --
2393 CURSOR Get_ChannelInfo_csr IS
2394 select
2395 pub_need_approval_flag,
2396 channel_category_id,
2397 AMV_C_CHL_ITEM_MATCH_S.nextval,
2398 sysdate
2399 from amv_c_channels_b
2400 Where channel_id = p_channel_id;
2401 --
2402 CURSOR Get_IdAndDate_csr IS
2403 select
2404 AMV_C_CHL_ITEM_MATCH_S.nextval,
2405 sysdate
2406 from dual;
2407 --
2408 BEGIN
2409 SAVEPOINT Remove_ItemChannelMatch_PVT;
2410 IF NOT FND_API.Compatible_API_Call (
2411 l_api_version,
2412 p_api_version,
2413 l_api_name,
2414 G_PKG_NAME) THEN
2415 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2416 END IF;
2417 --Initialize message list if p_init_msg_list is TRUE.
2418 IF FND_API.To_Boolean (p_init_msg_list) THEN
2419 FND_MSG_PUB.initialize;
2420 END IF;
2421 -- Initialize API return status to success
2422 x_return_status := FND_API.G_RET_STS_SUCCESS;
2423 -- Get the current (login) user id.
2424 AMV_UTILITY_PVT.Get_UserInfo(
2425 x_resource_id => l_resource_id,
2426 x_user_id => l_current_user_id,
2427 x_login_id => l_current_login_id,
2428 x_user_status => l_current_user_status
2429 );
2430 IF (p_check_login_user = FND_API.G_TRUE) THEN
2431 -- Check if user is login and has the required privilege.
2432 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
2433 -- User is not login.
2434 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2435 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2436 FND_MSG_PUB.Add;
2437 END IF;
2438 RAISE FND_API.G_EXC_ERROR;
2439 END IF;
2440 END IF;
2441 -- This is to make database happy
2442 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
2443 l_current_login_id := -1;
2444 l_current_user_id := -1;
2445 END IF;
2446
2447
2448 IF p_territory_id <> FND_API.G_MISS_NUM
2449 AND p_territory_id IS NOT NULL THEN
2450
2451 DELETE FROM amv_c_chl_item_match
2452 WHERE territory_id = p_territory_id
2453 AND item_id = p_item_id
2454 AND table_name_code = p_table_name_code
2455 AND channel_id IS NULL
2456 AND channel_category_id IS NULL;
2457 ELSE
2458 -- Proceed with the regular logic
2459 IF (p_category_id = FND_API.G_MISS_NUM) THEN
2460 --
2461 -- delete from channel
2462 DELETE FROM amv_c_chl_item_match
2463 WHERE channel_id = p_channel_id
2464 AND item_id = p_item_id
2465 AND table_name_code = p_table_name_code;
2466 --
2467 ELSE -- delete the item to category match.
2468 --check category id
2469 IF (AMV_UTILITY_PVT.Is_CategoryIdValid(p_category_id) <> TRUE) THEN
2470 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2471 FND_MESSAGE.Set_name('AMV','AMV_INVALID_CATEGORY_ID');
2472 FND_MESSAGE.Set_Token('ID', to_char(nvl(p_category_id, -1)));
2473 FND_MSG_PUB.Add;
2474 END IF;
2475 RAISE FND_API.G_EXC_ERROR;
2476 END IF;
2477 --
2478 DELETE FROM amv_c_chl_item_match
2479 WHERE channel_category_id = p_category_id
2480 AND item_id = p_item_id
2481 AND table_name_code = p_table_name_code
2482 AND channel_id is null;
2483 --
2484 END IF;
2485 END IF;
2486 --Standard check of commit
2487 IF FND_API.To_Boolean ( p_commit ) THEN
2488 COMMIT WORK;
2489 END IF;
2490 --Standard call to get message count and if count=1, get the message
2491 FND_MSG_PUB.Count_And_Get (
2492 p_encoded => FND_API.G_FALSE,
2493 p_count => x_msg_count,
2494 p_data => x_msg_data
2495 );
2496 EXCEPTION
2497 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2498 ROLLBACK TO Remove_ItemChannelMatch_PVT;
2499 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2500 -- Standard call to get message count and if count=1, get the message
2501 FND_MSG_PUB.Count_And_Get (
2502 p_encoded => FND_API.G_FALSE,
2503 p_count => x_msg_count,
2504 p_data => x_msg_data
2505 );
2506 WHEN FND_API.G_EXC_ERROR THEN
2507 ROLLBACK TO Remove_ItemChannelMatch_PVT;
2508 x_return_status := FND_API.G_RET_STS_ERROR;
2509 -- Standard call to get message count and if count=1, get the message
2510 FND_MSG_PUB.Count_And_Get (
2511 p_encoded => FND_API.G_FALSE,
2512 p_count => x_msg_count,
2513 p_data => x_msg_data
2514 );
2515 WHEN OTHERS THEN
2516 ROLLBACK TO Remove_ItemChannelMatch_PVT;
2517 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2518 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2519 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2520 END IF;
2521 -- Standard call to get message count and if count=1, get the message
2522 FND_MSG_PUB.Count_And_Get (
2523 p_encoded => FND_API.G_FALSE,
2524 p_count => x_msg_count,
2525 p_data => x_msg_data
2526 );
2527 END Remove_ItemChannelMatch;
2528 --
2529 --------------------------------------------------------------------------------
2530 --
2531 --
2532 --------------------------------------------------------------------------------
2533 --------------------------------------------------------------------------------
2534 PROCEDURE Get_UserTerritory
2535 (
2536 p_api_version IN NUMBER,
2537 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2538 x_return_status OUT NOCOPY VARCHAR2,
2539 x_msg_count OUT NOCOPY NUMBER,
2540 x_msg_data OUT NOCOPY VARCHAR2,
2541 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2542 p_resource_id IN NUMBER,
2543 p_resource_type IN VARCHAR2 := 'RS_EMPLOYEE',
2544 x_terr_id_tbl OUT NOCOPY terr_id_tbl_type,
2545 x_terr_name_tbl OUT NOCOPY terr_name_tbl_type
2546 ) AS
2547 l_api_name CONSTANT VARCHAR2(30) := 'Get_UserTerritory';
2548 l_api_version CONSTANT NUMBER := 1.0;
2549 l_resource_id NUMBER := -1;
2550 l_current_user_id NUMBER := -1;
2551 l_current_login_id NUMBER := -1;
2552 l_current_user_status VARCHAR2(80);
2553 --
2554 l_rec_count NUMBER := 0;
2555 --
2556 CURSOR c_territory_details IS
2557 SELECT DISTINCT
2558 RSC.terr_id
2559 ,TERR.name
2560 FROM jtf_terr_rsc_all RSC
2561 ,jtf_terr_srch_adv_gen_v TERR
2562 WHERE RSC.terr_id = TERR.terr_id
2563 AND RSC.resource_id = p_resource_id
2564 AND RSC.resource_type = p_resource_type;
2565 --
2566 BEGIN
2567 --DBMS_OUTPUT.PUT_LINE('ENTER : Get_UserTerritory');
2568 -- Standard call to check for call compatibility.
2569 IF NOT FND_API.Compatible_API_Call (
2570 l_api_version,
2571 p_api_version,
2572 l_api_name,
2573 G_PKG_NAME) THEN
2574 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2575 END IF;
2576 --Initialize message list if p_init_msg_list is TRUE.
2577 IF FND_API.To_Boolean (p_init_msg_list) THEN
2578 FND_MSG_PUB.initialize;
2579 END IF;
2580 -- Initialize API return status to success
2581 x_return_status := FND_API.G_RET_STS_SUCCESS;
2582 -- Get the current (login) user id.
2583 AMV_UTILITY_PVT.Get_UserInfo(
2584 x_resource_id => l_resource_id,
2585 x_user_id => l_current_user_id,
2586 x_login_id => l_current_login_id,
2587 x_user_status => l_current_user_status
2588 );
2589 IF (p_check_login_user = FND_API.G_TRUE) THEN
2590 -- Check if user is login and has the required privilege.
2591 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
2592 -- User is not login.
2593 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2594 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2595 FND_MSG_PUB.ADD;
2596 END IF;
2597 RAISE FND_API.G_EXC_ERROR;
2598 END IF;
2599 END IF;
2600
2601
2602 l_rec_count := 1;
2603 -- Open the territory cursor to get the user territories
2604 --DBMS_OUTPUT.PUT_LINE('Calling cursor');
2605 FOR territory_rec IN c_territory_details LOOP
2606 x_terr_id_tbl(l_rec_count) := territory_rec.terr_id;
2607 --DBMS_OUTPUT.PUT_LINE('x_terr_id_tbl(l_rec_count) : '||to_char(x_terr_id_tbl(l_rec_count)));
2608 x_terr_name_tbl(l_rec_count):= territory_rec.name;
2609 --DBMS_OUTPUT.PUT_LINE('x_terr_name_tbl(l_rec_count) : '||x_terr_name_tbl(l_rec_count));
2610 l_rec_count := l_rec_count + 1;
2611 END LOOP;
2612 --TYPE territory_tbl_type IS TABLE OF NUMBER;
2613 --TYPE terr_name_tbl_type IS TABLE OF VARCHAR2(4000);
2614 --Standard call to get message count and if count=1, get the message
2615 FND_MSG_PUB.Count_And_Get (
2616 p_encoded => FND_API.G_FALSE,
2617 p_count => x_msg_count,
2618 p_data => x_msg_data
2619 );
2620 EXCEPTION
2621 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2622 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2623 -- Standard call to get message count and if count=1, get the message
2624 FND_MSG_PUB.Count_And_Get (
2625 p_encoded => FND_API.G_FALSE,
2626 p_count => x_msg_count,
2627 p_data => x_msg_data
2628 );
2629 WHEN FND_API.G_EXC_ERROR THEN
2630 x_return_status := FND_API.G_RET_STS_ERROR;
2631 -- Standard call to get message count and if count=1, get the message
2632 FND_MSG_PUB.Count_And_Get (
2633 p_encoded => FND_API.G_FALSE,
2634 p_count => x_msg_count,
2635 p_data => x_msg_data
2636 );
2637 WHEN OTHERS THEN
2638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2639 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2640 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2641 END IF;
2642 -- Standard call to get message count and if count=1, get the message
2643 FND_MSG_PUB.Count_And_Get (
2644 p_encoded => FND_API.G_FALSE,
2645 p_count => x_msg_count,
2646 p_data => x_msg_data
2647 );
2648 END Get_UserTerritory;
2649 --
2650 --------------------------------------------------------------------------------
2651 --------------------------------------------------------------------------------
2652 PROCEDURE Get_PublishedTerritories
2653 (
2654 p_api_version IN NUMBER,
2655 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2656 x_return_status OUT NOCOPY VARCHAR2,
2657 x_msg_count OUT NOCOPY NUMBER,
2658 x_msg_data OUT NOCOPY VARCHAR2,
2659 p_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
2660 p_terr_id IN NUMBER,
2661 p_table_name_code IN VARCHAR2,
2662 x_item_id_tbl OUT NOCOPY terr_id_tbl_type
2663 ) AS
2664 l_api_name CONSTANT VARCHAR2(30) := 'Get_PublishedTerritories';
2665 l_api_version CONSTANT NUMBER := 1.0;
2666 l_resource_id NUMBER := -1;
2667 l_current_user_id NUMBER := -1;
2668 l_current_login_id NUMBER := -1;
2669 l_current_user_status VARCHAR2(80);
2670 --
2671 l_rec_count NUMBER := 0;
2672 --
2673 CURSOR c_territory_item IS
2674 SELECT DISTINCT
2675 item_id
2676 FROM amv_c_chl_item_match
2677 WHERE territory_id = p_terr_id
2678 AND channel_id IS NULL
2679 AND channel_category_id IS NULL
2680 AND table_name_code = p_table_name_code;
2681 --
2682 BEGIN
2683 -- Standard call to check for call compatibility.
2684 IF NOT FND_API.Compatible_API_Call (
2685 l_api_version,
2686 p_api_version,
2687 l_api_name,
2688 G_PKG_NAME) THEN
2689 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2690 END IF;
2691 --Initialize message list if p_init_msg_list is TRUE.
2692 IF FND_API.To_Boolean (p_init_msg_list) THEN
2693 FND_MSG_PUB.initialize;
2694 END IF;
2695 -- Initialize API return status to success
2696 x_return_status := FND_API.G_RET_STS_SUCCESS;
2697 -- Get the current (login) user id.
2698 AMV_UTILITY_PVT.Get_UserInfo(
2699 x_resource_id => l_resource_id,
2700 x_user_id => l_current_user_id,
2701 x_login_id => l_current_login_id,
2702 x_user_status => l_current_user_status
2703 );
2704 IF (p_check_login_user = FND_API.G_TRUE) THEN
2705 -- Check if user is login and has the required privilege.
2706 IF (l_current_login_id = FND_API.G_MISS_NUM) THEN
2707 -- User is not login.
2708 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2709 FND_MESSAGE.Set_name('AMV','AMV_USER_NOT_LOGIN');
2710 FND_MSG_PUB.ADD;
2711 END IF;
2712 RAISE FND_API.G_EXC_ERROR;
2713 END IF;
2714 END IF;
2715
2716 l_rec_count := 1;
2717 -- Open the cursor to get the items for the territory
2718 FOR cur IN c_territory_item LOOP
2719 x_item_id_tbl(l_rec_count) := cur.item_id;
2720 l_rec_count := l_rec_count + 1;
2721 END LOOP;
2722 --Standard call to get message count and if count=1, get the message
2723 FND_MSG_PUB.Count_And_Get (
2724 p_encoded => FND_API.G_FALSE,
2725 p_count => x_msg_count,
2726 p_data => x_msg_data
2727 );
2728 EXCEPTION
2729 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2731 -- Standard call to get message count and if count=1, get the message
2732 FND_MSG_PUB.Count_And_Get (
2733 p_encoded => FND_API.G_FALSE,
2734 p_count => x_msg_count,
2735 p_data => x_msg_data
2736 );
2737 WHEN FND_API.G_EXC_ERROR THEN
2738 x_return_status := FND_API.G_RET_STS_ERROR;
2739 -- Standard call to get message count and if count=1, get the message
2740 FND_MSG_PUB.Count_And_Get (
2741 p_encoded => FND_API.G_FALSE,
2742 p_count => x_msg_count,
2743 p_data => x_msg_data
2744 );
2745 WHEN OTHERS THEN
2746 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2747 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2748 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2749 END IF;
2750 -- Standard call to get message count and if count=1, get the message
2751 FND_MSG_PUB.Count_And_Get (
2752 p_encoded => FND_API.G_FALSE,
2753 p_count => x_msg_count,
2754 p_data => x_msg_data
2755 );
2756 END Get_PublishedTerritories;
2757 --------------------------------------------------------------------------------
2758 --------------------------------------------------------------------------------
2759 --
2760 END amv_match_pvt;