[Home] [Help]
PACKAGE BODY: APPS.OTA_CATALOG_UTIL
Source
1 PACKAGE BODY ota_catalog_util as
2 /* $Header: otctgutl.pkb 120.0 2006/03/21 03:08 pgupta noship $ */
3
4 g_package varchar2(33) := 'ota_catalog_util.'; -- Global package name
5
6 --
7 -- ---------------------------------------------------------------------------
8 -- |--------------------< Get_Forum_Topic_Count >----------------------------|
9 -- ---------------------------------------------------------------------------
10 --
11 Function Get_Forum_Topic_Count
12 (p_forum_id IN Number
13 ,p_person_id IN Number
14 )
15 Return Number Is
16 --
17 -- Declare cursor
18 --
19 Cursor cur_forum_topics is
20 select count(thread_id)
21 from
22 ( select fth.forum_thread_id thread_id
23 from ota_forum_threads fth
24 where fth.private_thread_flag = 'N'
25 and fth.forum_id = p_forum_id
26 UNION ALL
27 select fth.forum_thread_id
28 from ota_forum_threads fth,
29 ota_pvt_frm_thread_users users
30 where fth.forum_thread_id = users.forum_thread_id
31 and fth.private_thread_flag = 'Y'
32 and users.person_id = p_person_id
33 and fth.forum_id = p_forum_id);
34 --
35 -- Declare local variables
36 --
37 l_thread_count Number(9);
38 l_proc Varchar2(72) := g_package||'Get_Forum_Topic_Count';
39 --
40 Begin
41 --
42 hr_utility.set_location('Entering:'|| l_proc, 10);
43 --
44 Open cur_forum_topics;
45 Fetch cur_forum_topics into l_thread_count;
46 Close cur_forum_topics;
47 --
48 hr_utility.set_location('Returning:'|| l_proc, 20);
49 --
50 return l_thread_count;
51 --
52 End Get_Forum_Topic_Count;
53
54 --
55 -- ---------------------------------------------------------------------------
56 -- |-------------------< Get_Forum_Message_Count >---------------------------|
57 -- ---------------------------------------------------------------------------
58 --
59 Function Get_Forum_Message_Count
60 (p_forum_id IN Number
61 ,p_person_id IN Number
62 )
63 Return Number Is
64 --
65 -- Declare cursor
66 --
67 Cursor cur_forum_messages is
68 select count(message_id)
69 from
70 (
71 select message.forum_message_id message_id
72 from ota_forum_messages message, ota_forum_threads threads
73 where message.forum_thread_id = threads.forum_thread_id
74 and threads.private_thread_flag = 'N'
75 and message.forum_id = p_forum_id
76 UNION ALL
77 select message.forum_message_id message_id
78 from ota_forum_messages message, ota_forum_threads threads,
79 ota_pvt_frm_thread_users pvtusers
80 where message.forum_thread_id = threads.forum_thread_id
81 and threads.forum_id = p_forum_id
82 and threads.private_thread_flag = 'Y'
83 and message.forum_thread_id = pvtusers.forum_thread_id
84 and pvtusers.person_id = p_person_id
85 and (message.message_scope = 'T'
86 or (message.message_scope = 'U'
87 and (message.person_id = p_person_id
88 or message.target_person_id = p_person_id
89 )
90 )
91 )
92 );
93 --
94 -- Declare local variables
95 --
96 l_message_count Number(9);
97 l_proc Varchar2(72) := g_package||'Get_Forum_Message_Count';
98 --
99 Begin
100 --
101 hr_utility.set_location('Entering:'|| l_proc, 10);
102 --
103 Open cur_forum_messages;
104 Fetch cur_forum_messages into l_message_count;
105 Close cur_forum_messages;
106 --
107 hr_utility.set_location('Returning:'|| l_proc, 20);
108 --
109 Return l_message_count;
110 --
111 End Get_Forum_Message_Count;
112 --
113
114 --
115 -- ---------------------------------------------------------------------------
116 -- |------------------< Get_Forum_Last_Post_Date >---------------------------|
117 -- ---------------------------------------------------------------------------
118 --
119 Function Get_Forum_Last_Post_Date
120 (p_forum_id IN Number
121 )
122 Return Date Is
123 --
124 -- Declare cursor
125 --
126 Cursor cur_forum_last_post_date is
127 select max(message.creation_date)
128 from ota_forum_messages message, ota_forum_threads thread
129 where message.forum_thread_id = thread.forum_thread_id
130 and message.forum_id = p_forum_id;
131 --
132 -- Declare local variables
133 --
134 l_message_last_post_date Date;
135 l_proc Varchar2(72) := g_package||'Get_Forum_Last_Post_Date';
136 --
137 Begin
138 --
139 hr_utility.set_location('Entering:'|| l_proc, 10);
140 --
141 Open cur_forum_last_post_date;
142 Fetch cur_forum_last_post_date into l_message_last_post_date;
143 Close cur_forum_last_post_date;
144 --
145 hr_utility.set_location('Returning:'|| l_proc, 20);
146 --
147 Return l_message_last_post_date;
148 --
149 End Get_Forum_Last_Post_Date;
150 --
151 end ota_catalog_util;
152