[Home] [Help]
PACKAGE BODY: APPS.PON_THREAD_DISC_PKG
Source
1 package body PON_THREAD_DISC_PKG as
2 /* $Header: PONTHDIB.pls 120.8.12010000.2 2009/02/11 15:56:59 ankusriv ship $ */
3
4 g_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.PON_THREAD_DISC_PKG.';
5
6 function insert_pon_discussions(
7 p_entity_name IN VARCHAR2,
8 p_entity_pk1 IN VARCHAR2,
9 p_entity_pk2 IN VARCHAR2,
10 p_entity_pk3 IN VARCHAR2,
11 p_entity_pk4 IN VARCHAR2,
12 p_entity_pk5 IN VARCHAR2,
13 p_subject IN VARCHAR2,
14 p_language_code IN VARCHAR2,
15 p_party_id IN NUMBER,
16 p_validation_class IN VARCHAR2)
17 return NUMBER
18 IS
19
20 l_discussion_id NUMBER(15);
21
22 BEGIN
23 -- bug 8224577
24 BEGIN
25 SELECT discussion_id
26 INTO l_discussion_id
27 FROM pon_discussions
28 WHERE ENTITY_NAME = p_entity_name
29 AND pk1_value = p_entity_pk1;
30
31 EXCEPTION
32 WHEN No_Data_Found THEN
33
34 SELECT pon_discussions_s.nextval
35 INTO l_discussion_id
36 FROM dual;
37
38 INSERT INTO pon_discussions(
39 DISCUSSION_ID,
40 ENTITY_NAME,
41 PK1_VALUE,
42 SUBJECT,
43 LAST_UPDATE_DATE,
44 VALIDATION_CLASS,
45 PK2_VALUE,
46 PK3_VALUE,
47 PK4_VALUE,
48 PK5_VALUE,
49 LANGUAGE_CODE,
50 OWNER_PARTY_ID
51 )VALUES(
52 l_discussion_id,
53 p_entity_name,
54 p_entity_pk1,
55 nvl(p_subject,p_entity_pk1),
56 sysdate,
57 p_validation_class,
58 p_entity_pk2,
59 p_entity_pk3,
60 p_entity_pk4,
61 p_entity_pk5,
62 p_language_code,
63 p_party_id);
64 END;
65 return l_discussion_id;
66
67 END insert_pon_discussions;
68
69 function insert_pon_threads(
70 p_discussion_id IN NUMBER,
71 p_subject IN VARCHAR2,
72 p_language_code IN VARCHAR2,
73 p_party_id IN NUMBER)
74 return NUMBER
75 IS
76
77 l_prev_thread_number NUMBER(15);
78 l_now_date DATE;
79 l_lang_code VARCHAR2(4);
80
81 BEGIN
82
83 l_now_date := sysdate;
84
85 SELECT nvl(max(thread_number), -1)
86 INTO l_prev_thread_number
87 FROM pon_threads
88 WHERE discussion_id = p_discussion_id;
89
90 SELECT nvl(p_language_code, language_code)
91 INTO l_lang_code
92 FROM pon_discussions
93 WHERE discussion_id = p_discussion_id;
94
95
96 INSERT INTO pon_threads(
97 THREAD_NUMBER,
98 OWNER_PARTY_ID,
99 DISCUSSION_ID,
100 SUBJECT,
101 LANGUAGE_CODE,
102 LAST_UPDATE_DATE
103 )VALUES(
104 l_prev_thread_number + 1,
105 p_party_id,
106 p_discussion_id,
107 p_subject,
108 l_lang_code,
109 l_now_date);
110
111 UPDATE pon_discussions
112 SET last_update_date = l_now_date
113 WHERE discussion_id = p_discussion_id;
114
115 return l_prev_thread_number + 1;
116
117 END insert_pon_threads;
118
119
120 function insert_thread_entry(
121 p_from_id in NUMBER,
122 p_from_first_name in VARCHAR2,
123 p_from_last_name in VARCHAR2,
124 p_subject in VARCHAR2,
125 p_discussion_id in VARCHAR2,
126 p_thread_id in NUMBER,
127 p_broadcast_flag in VARCHAR2,
128 p_parent_id in NUMBER)
129 return NUMBER
130 IS
131
132 l_entry_id NUMBER(15);
133 l_now_date DATE;
134
135 BEGIN
136
137 SELECT pon_thread_entries_s.nextval
138 INTO l_entry_id FROM dual;
139
140 l_now_date := sysdate;
141
142 INSERT INTO pon_thread_entries(
143 ENTRY_ID,
144 PARENT_ENTRY_ID,
145 FROM_ID,
146 FROM_FIRST_NAME,
147 FROM_LAST_NAME,
148 POSTED_DATE,
149 SUBJECT,
150 THREAD_NUMBER,
151 DISCUSSION_ID,
152 BROADCAST_FLAG,
153 CONTENT
154 )VALUES(
155 l_entry_id,
156 p_parent_id,
157 p_from_id,
158 p_from_first_name,
159 p_from_last_name,
160 l_now_date,
161 p_subject,
162 p_thread_id,
163 p_discussion_id,
164 p_broadcast_flag,
165 empty_clob());
166
167 UPDATE pon_threads
168 SET last_update_date = l_now_date
169 WHERE discussion_id = p_discussion_id
170 AND thread_number = p_thread_id;
171
172 UPDATE pon_discussions
173 SET last_update_date = l_now_date
174 WHERE discussion_id = p_discussion_id;
175
176 RETURN l_entry_id;
177
178 END insert_thread_entry;
179
180 /*=======================================================================+
181 -- API Name: insert_or_update_recipient
182 --
183 -- Type : Public
184 --
185 -- Pre-reqs: None
186 --
187 -- Function: This API is called by the Online Discussion code.
188 -- It inserts a record to pon_te_recipients if it
189 -- does not exists for given p_to_id and p_entry_id.
190 -- Else it will update the record with appropriate
191 -- read or replied flag.
192 --
193 -- Parameters:
194 --
195 -- p_entry_id in NUMBER
196 -- p_recipient_id in NUMBER
197 -- p_read_flag in VARCHAR2
198 -- p_replied_flag in VARCHAR2
199 --
200 -- Following paraemters are added as part of R12 : Online Discussion Enhancement Project.
201 -- p_to_company_id in NUMBER
202 -- p_to_first_name in VARCHAR2
203 -- p_to_last_name in VARCHAR2
204 -- p_to_company_name in VARCHAR2
205 --
206 *=======================================================================*/
207
208 procedure insert_or_update_recipient(
209 p_entry_id in NUMBER,
210 p_recipient_id in NUMBER,
211 p_read_flag in VARCHAR2,
212 p_replied_flag in VARCHAR2,
213 p_to_company_id in NUMBER,
214 p_to_first_name in VARCHAR2,
215 p_to_last_name in VARCHAR2,
216 p_to_company_name in VARCHAR2)
217 IS
218 l_row_exists NUMBER;
219 l_module_name VARCHAR2(40) := 'INSERT_OR_UPDATE_RECIPIENT';
220
221 BEGIN
222
223 SELECT COUNT(1) INTO l_row_exists
224 FROM pon_te_recipients
225 WHERE to_id = p_recipient_id
226 AND entry_id = p_entry_id;
227
228
229 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
230 FND_LOG.string (log_level => FND_LOG.level_procedure,
231 module => g_module_prefix || l_module_name,
232 message => 'Entering PON_THREAD_DISC_PKG.INSERT_OR_UPDATE_RECIPIENT'
233 || ', l_row_exists = ' || l_row_exists
234 || ', p_recipient_id = ' || p_recipient_id
235 || ', p_entry_id = ' || p_entry_id
236 || ', p_read_flag = ' || p_read_flag
237 || ', p_replied_flag = ' || p_replied_flag
238 || ', p_to_company_id = ' || p_to_company_id
239 || ', p_to_first_name = ' || p_to_first_name
240 || ', p_to_last_name = ' || p_to_last_name
241 || ', p_to_company_name = '|| p_to_company_name);
242 END IF;
243
244 IF (l_row_exists > 0) THEN
245
246 UPDATE pon_te_recipients
247 SET read_flag = p_read_flag,
248 replied_flag = p_replied_flag
249 WHERE to_id = p_recipient_id
250 AND entry_id = p_entry_id;
251
252 ELSE
253
254 INSERT INTO pon_te_recipients(
255 TO_ID,
256 READ_FLAG,
257 ENTRY_ID,
258 REPLIED_FLAG,
259 TO_COMPANY_ID,
260 TO_FIRST_NAME,
261 TO_LAST_NAME,
262 TO_COMPANY_NAME
263 )VALUES(
264 p_recipient_id,
265 p_read_flag,
266 p_entry_id,
267 p_replied_flag,
268 p_to_company_id,
269 p_to_first_name,
270 p_to_last_name ,
271 p_to_company_name);
272 END IF;
273
274 END insert_or_update_recipient;
275
276 /*=======================================================================+
277 -- API Name: update_recipient_to_read
278 --
279 -- Type : Public
280 --
281 -- Pre-reqs: None
282 --
283 -- Function: This API is called by the Online Discussion code.
284 -- It checks if any record exist for given p_entry_id
285 -- and p_recipient_id and calls insert_or_update_recipient
286 -- with appropriate value for Read and Replied flag.
287 -- Parameters:
288 --
289 -- p_entry_id in NUMBER
290 -- p_recipient_id in NUMBER
291 --
292 -- Following paraemters are added as part of R12 : Online Discussion Enhancement Project.
293 -- p_to_company_id in NUMBER
294 -- p_to_first_name in VARCHAR2
295 -- p_to_last_name in VARCHAR2
296 -- p_to_company_name in VARCHAR2
297 --
298 *=======================================================================*/
299
300 PROCEDURE update_recipient_to_read(
301 p_entry_id in NUMBER,
302 p_recipient_id in NUMBER,
303 p_to_company_id in NUMBER,
304 p_to_first_name in VARCHAR2,
305 p_to_last_name in VARCHAR2,
306 p_to_company_name in VARCHAR2)
307 IS
308 l_num_entries NUMBER(1);
309 l_replied_state VARCHAR(1);
310 l_module_name VARCHAR2(40) := 'UPDATE_RECIPIENT_TO_READ';
311 BEGIN
312 SELECT count(1) INTO l_num_entries
313 FROM pon_te_recipients
314 WHERE entry_id = p_entry_id
315 AND to_id = p_recipient_id;
316
317 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
318 FND_LOG.string (log_level => FND_LOG.level_procedure,
319 module => g_module_prefix || l_module_name,
320 message => 'Entering PON_THREAD_DISC_PKG.UPDATE_RECIPIENT_TO_READ'
321 || ', l_num_entries = ' || l_num_entries
322 || ', p_recipient_id = ' || p_recipient_id
323 || ', p_entry_id = ' || p_entry_id
324 || ', p_to_company_id = ' || p_to_company_id
325 || ', p_to_first_name = ' || p_to_first_name
326 || ', p_to_last_name = ' || p_to_last_name
327 || ', p_to_company_name = '|| p_to_company_name);
328 END IF;
329
330 IF (l_num_entries = 0) THEN
331 insert_or_update_recipient( p_entry_id, p_recipient_id, 'Y', 'N', p_to_company_id, p_to_first_name, p_to_last_name, p_to_company_name);
332 ELSE
333 SELECT replied_flag INTO l_replied_state
334 FROM pon_te_recipients
335 WHERE entry_id = p_entry_id
336 AND to_id = p_recipient_id;
337
338 insert_or_update_recipient( p_entry_id, p_recipient_id, 'Y', l_replied_state, null, null, null, null);
339 END IF;
340 END update_recipient_to_read;
341
342
343
344
345 PROCEDURE record_read(
346 p_reader in NUMBER,
347 p_entry_id in NUMBER)
348 IS
349 BEGIN
350
351 INSERT INTO pon_te_view_audit(
352 VIEW_DATE,
353 ENTRY_ID,
354 VIEWER_PARTY_ID
355 )VALUES(
356 sysdate,
357 p_entry_id,
358 p_reader
359 );
360
361 END record_read;
362
363 /*=======================================================================+
364 -- API Name: GET_REPLIED_BY_LIST
365 --
366 -- Type : Public
367 --
368 -- Pre-reqs: None
369 --
370 -- Function: This API is called by the Online Discussion code.
371 -- It returns the list of Buyer's who has already replied to
372 -- the message for given entry id.
373 -- This function will retrieve list of buyer's only,it will not
374 -- include supplier's name. Also, it will consider EXTERNAL messages
375 -- only.
376 --
377 -- Parameters:
378 --
379 -- p_to_id IN NUMBER
380 -- p_entry_id IN NUMBER
381 -- p_auctioneer_tp_id IN NUMBER
382 -- p_message_type IN VARCHAR2
383 --
384 *=======================================================================*/
385
386 function GET_REPLIED_BY_LIST (p_to_id IN NUMBER,
387 p_entry_id IN NUMBER,
388 p_auctioneer_tp_id IN NUMBER,
389 p_message_type IN VARCHAR2)
390 RETURN VARCHAR2
391 IS
392 v_display_name VARCHAR2(240);
393 v_member_list VARCHAR2(2500);
394 l_module_name VARCHAR2(40) := 'GET_REPLIED_BY_LIST';
395
396 CURSOR memberlist(x_entry_id NUMBER,x_auctioneer_tp_id NUMBER, x_to_id NUMBER, x_message_type VARCHAR2) IS
397 SELECT PON_LOCALE_PKG.get_party_display_name(PTR.TO_ID, PON_LOCALE_PKG.DEFAULT_NAME_DISPLAY_PATTERN, userenv('LANG')) as name
398 FROM PON_TE_RECIPIENTS PTR
399 WHERE PTR.REPLIED_FLAG='Y'
400 AND PTR.ENTRY_ID = x_entry_id
401 AND PTR.TO_COMPANY_ID = x_auctioneer_tp_id -- auctioneer's trading partner id
402 AND PTR.TO_ID <> x_to_id -- Replied by Others
403 AND 'EXTERNAL'=x_message_type; -- Replied to External Messages
404 BEGIN
405
406 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
407 FND_LOG.string (log_level => FND_LOG.level_procedure,
408 module => g_module_prefix || l_module_name,
409 message => 'Entering PON_THREAD_DISC_PKG.GET_REPLIED_BY_LIST'
410 || ', p_to_id = ' || p_to_id
411 || ', p_entry_id = ' || p_entry_id
412 || ', p_auctioneer_tp_id = ' || p_auctioneer_tp_id
413 || ', p_message_type = ' || p_message_type);
414 END IF;
415
416
417 FOR teammember IN memberlist(p_entry_id,p_auctioneer_tp_id, p_to_id, p_message_type) LOOP
418 IF (v_member_list is not null) THEN
419 v_member_list := v_member_list || '; '|| teammember.name;
420 ELSE
421 v_member_list := teammember.name;
422 END IF;
423 END LOOP;
424
425 RETURN v_member_list;
426
427 END GET_REPLIED_BY_LIST;
428
429 /*=======================================================================+
430 -- API Name: GET_RECIPIENTS_LIST
431 --
432 -- Type : Public
433 --
434 -- Pre-reqs: None
435 --
436 -- Function: This API is called by the Online Discussion code.
437 -- It returns the list of message recipients for
438 -- the message for given entry id.
439 --
440 -- Parameters:
441 --
442 -- p_from_id IN NUMBER
443 -- p_entry_id IN NUMBER
444 -- p_message_type IN VARCHAR2
445 --
446 *=======================================================================*/
447
448 function GET_RECIPIENTS_LIST (p_from_id IN NUMBER,
449 p_entry_id IN NUMBER,
450 p_message_type IN VARCHAR2)
451 RETURN VARCHAR2
452 IS
453 v_display_name VARCHAR2(240);
454 v_member_list VARCHAR2(2500);
455 l_module_name VARCHAR2(40) := 'GET_RECIPIENTS_LIST';
456 l_from_company_id NUMBER;
457
458 CURSOR memberlist(x_entry_id NUMBER, x_from_id NUMBER, x_message_type VARCHAR2, x_from_company_id NUMBER) IS
459 SELECT PON_LOCALE_PKG.get_party_display_name(PTR.TO_ID, PON_LOCALE_PKG.DEFAULT_NAME_DISPLAY_PATTERN, userenv('LANG')) || DECODE(x_message_type,'EXTERNAL',' - '|| PTR.TO_COMPANY_NAME) as name
460 FROM PON_TE_RECIPIENTS PTR
461 WHERE PTR.ENTRY_ID = x_entry_id
462 AND PTR.TO_ID <> x_from_id
463 AND ((x_message_type='EXTERNAL' AND PTR.TO_COMPANY_ID <> x_from_company_id)
464 OR x_message_type <> 'EXTERNAL');
465
466
467 BEGIN
468
469 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
470 FND_LOG.string (log_level => FND_LOG.level_procedure,
471 module => g_module_prefix || l_module_name,
472 message => 'Entering PON_THREAD_DISC_PKG.GET_RECIPIENTS_LIST'
473 || ', p_from_id = ' || p_from_id
474 || ', p_entry_id = ' || p_entry_id
475 || ', p_message_type = ' || p_message_type);
476 END IF;
477
478 /* Bug 5253337. Buyers viewing EXTERNAL Group messages should not be
479 shown in the 'To' field while viewing the message.*/
480 SELECT from_company_id
481 INTO l_from_company_id
482 FROM PON_THREAD_ENTRIES
483 WHERE entry_id = p_entry_id;
484
485
486 FOR teammember IN memberlist(p_entry_id, p_from_id, p_message_type, l_from_company_id ) LOOP
487 IF (v_member_list is not null) THEN
488 v_member_list := v_member_list || '; '|| teammember.name;
489 ELSE
490 v_member_list := teammember.name;
491 END IF;
492 END LOOP;
493
494 RETURN v_member_list;
495
496 END GET_RECIPIENTS_LIST;
497
498 /*=======================================================================+
499 -- API Name: GET_MESSAGE_STATUS_DISP
500 --
501 -- Type : Public
502 --
503 -- Pre-reqs: None
504 --
505 -- Function: This API is called by the Print Discussion code.
506 -- It returns read, unread or replied status of
507 -- the message for given entry id, depending on viewer.
508 --
509 -- Parameters:
510 --
511 -- p_viewer_id IN NUMBER
512 -- p_entry_id IN NUMBER
513 --
514 *=======================================================================*/
515 function GET_MESSAGE_STATUS_DISP (p_viewer_id IN NUMBER,
516 p_entry_id IN NUMBER)
517 RETURN VARCHAR2
518 AS
519 l_msg_read fnd_new_messages.message_text%TYPE;
520 l_msg_unread fnd_new_messages.message_text%TYPE;
521 l_msg_replied fnd_new_messages.message_text%TYPE;
522 l_message_status fnd_new_messages.message_text%TYPE;
523 l_module_name VARCHAR2(40) := 'GET_MESSAGE_STATUS_DISP';
524
525 BEGIN
526 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
527 FND_LOG.string (log_level => FND_LOG.level_procedure,
528 module => g_module_prefix || l_module_name,
529 message => 'Entering PON_THREAD_DISC_PKG.GET_MESSAGE_STATUS_DISP'
530 || ', p_viewer_id = ' || p_viewer_id
531 || ', p_entry_id = ' || p_entry_id);
532 END IF;
533
534 -- first get the translated messages for the three statuses
535 l_msg_read := fnd_message.get_string('PON', 'PON_TD_READ');
536 l_msg_unread := fnd_message.get_string('PON', 'PON_TD_UNREAD');
537 l_msg_replied := fnd_message.get_string('PON', 'PON_TD_REPLIED');
538
539 -- then we see if this is a sender or recipient with a record
540 SELECT decode(replied_flag, 'Y', l_msg_replied, decode(read_flag, 'Y', l_msg_read, l_msg_unread))
541 INTO l_message_status
542 FROM pon_thread_entries pte, pon_te_recipients ptr
543 WHERE pte.entry_id = p_entry_id
544 AND ptr.entry_id = pte.entry_id
545 AND ((pte.from_id = p_viewer_id AND pte.from_id = ptr.to_id)
546 OR (ptr.to_id = p_viewer_id AND pte.from_id <> ptr.to_id));
547
548 IF SQL%NOTFOUND THEN -- {
549 -- message is unread, no record exists
550 l_message_status := l_msg_unread;
551 END IF; -- }
552
553 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
554 FND_LOG.string (log_level => FND_LOG.level_procedure,
555 module => g_module_prefix || l_module_name,
556 message => 'Exiting PON_THREAD_DISC_PKG.GET_MESSAGE_STATUS_DISP'
557 || ', l_message_status = ' || l_message_status);
558 END IF;
559
560 RETURN l_message_status;
561
562 EXCEPTION
563 WHEN OTHERS THEN
564 IF (FND_LOG.level_exception >= FND_LOG.g_current_runtime_level) THEN
565 FND_LOG.string (log_level => FND_LOG.level_exception,
566 module => g_module_prefix || l_module_name,
567 message => 'Exception in PON_THREAD_DISC_PKG.GET_MESSAGE_STATUS_DISP'
568 || ', returning l_msg_unread = ' || l_msg_unread);
569 END IF;
570 RETURN l_msg_unread;
571
572 END GET_MESSAGE_STATUS_DISP;
573
574 end PON_THREAD_DISC_PKG;
575