[Home] [Help]
PACKAGE BODY: APPS.OTA_FORUM_THREAD_API
Source
1 PACKAGE BODY ota_forum_thread_api as
2 /* $Header: otftsapi.pkb 120.2 2005/08/10 16:47 asud noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' OTA_FORUM_THREAD_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-------------------------< CREATE_FORUM_THREAD >-------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_forum_thread(
13 p_validate in boolean default false
14 ,p_effective_date in date
15 ,p_forum_id in number
16 ,p_business_group_id in number
17 ,p_subject in varchar2
18 ,p_private_thread_flag in varchar2
19 ,p_last_post_date in date default null
20 ,p_reply_count in number default null
21 ,p_forum_thread_id out nocopy number
22 ,p_object_version_number out nocopy number) is
23 --
24 -- Declare cursors and local variables
25 --
26 l_proc varchar2(72) := g_package||' Create_forum_thread';
27 l_forum_thread_id number;
28 l_object_version_number number;
29 l_effective_date date;
30 l_end_date date;
31 begin
32 hr_utility.set_location('Entering:'|| l_proc, 10);
33 --
34 -- Issue a savepoint
35 --
36 savepoint CREATE_FORUM_THREAD;
37 --
38 -- Truncate the time portion from all IN date parameters
39 --
40 l_effective_date := trunc(p_effective_date);
41 --
42 -- Call Before Process User Hook
43 --
44 begin
45 ota_forum_thread_bk1.create_forum_thread_b
46 (p_effective_date => p_effective_date
47 ,p_forum_id => p_forum_id
48 ,p_business_group_id => p_business_group_id
49 ,p_subject => p_subject
50 ,p_private_thread_flag => p_private_thread_flag
51 ,p_last_post_date => p_last_post_date
52 ,p_reply_count => p_reply_count
53 ,p_object_version_number => p_object_version_number
54 );
55 exception
56 when hr_api.cannot_find_prog_unit then
57 hr_api.cannot_find_prog_unit_error
58 (p_module_name => 'CREATE_FORUM_THREAD'
59 ,p_hook_type => 'BP'
60 );
61 end;
62
63
64 -- Validation in addition to Row Handlers
65 --
66 --
67 -- Process Logic
68 --
69
70 ota_fts_ins.ins
71 ( p_effective_date => l_effective_date
72 ,p_forum_id => p_forum_id
73 ,p_business_group_id => p_business_group_id
74 ,p_subject => p_subject
75 ,p_private_thread_flag => p_private_thread_flag
76 ,p_last_post_date => p_last_post_date
77 ,p_reply_count => p_reply_count
78 ,p_forum_thread_id => l_forum_thread_id
79 ,p_object_version_number => l_object_version_number
80 );
81
82
83 --
84 -- Call After Process User Hook
85 begin
86 ota_forum_thread_bk1.create_forum_thread_a
87 (p_effective_date => p_effective_date
88 ,p_forum_id => p_forum_id
89 ,p_business_group_id => p_business_group_id
90 ,p_subject => p_subject
91 ,p_private_thread_flag => p_private_thread_flag
92 ,p_last_post_date => p_last_post_date
93 ,p_reply_count => p_reply_count
94 ,p_forum_thread_id => p_forum_thread_id
95 ,p_object_version_number => p_object_version_number
96 );
97 exception
98 when hr_api.cannot_find_prog_unit then
99 hr_api.cannot_find_prog_unit_error
100 (p_module_name => 'CREATE_FORUM_THREAD'
101 ,p_hook_type => 'AP'
102 );
103 end;
104 --
105 -- When in validation only mode raise the Validate_Enabled exception
106 --
107 if p_validate then
108 raise hr_api.validate_enabled;
109 end if;
110 --
111 -- Set all output arguments
112 --
113 p_forum_thread_id := l_forum_thread_id;
114 p_object_version_number := l_object_version_number;
115
116
117 hr_utility.set_location(' Leaving:'||l_proc, 70);
118 exception
119 when hr_api.validate_enabled then
120 --
121 -- As the Validate_Enabled exception has been raised
122 -- we must rollback to the savepoint
123 --
124 rollback to CREATE_FORUM_THREAD;
125 --
126 -- Only set output warning arguments
127 -- (Any key or derived arguments must be set to null
128 -- when validation only mode is being used.)
129 --
130 p_forum_thread_id := null;
131 p_object_version_number := null;
132 hr_utility.set_location(' Leaving:'||l_proc, 80);
133 when others then
134 --
135 -- A validation or unexpected error has occured
136 --
137 rollback to CREATE_FORUM_THREAD;
138 p_forum_thread_id := null;
139 p_object_version_number := null;
140 hr_utility.set_location(' Leaving:'||l_proc, 90);
141 raise;
142 end create_forum_thread;
143 --
144 --
145 -- ----------------------------------------------------------------------------
146 -- |-------------------------< UPDATE_FORUM_THREAD >-------------------------|
147 -- ----------------------------------------------------------------------------
148 --
149 procedure update_forum_thread(
150 p_validate in boolean default false
151 ,p_effective_date in date
152 ,p_forum_id in number
153 ,p_business_group_id in number
154 ,p_subject in varchar2
155 ,p_private_thread_flag in varchar2
156 ,p_last_post_date in date default null
157 ,p_reply_count in number default null
158 ,p_forum_thread_id in number
159 ,p_object_version_number in out nocopy number) is
160 --
161 -- Declare cursors and local variables
162 --
163 l_proc varchar2(72) := g_package||' Update_forum_thread';
164 l_object_version_number number := p_object_version_number;
165 l_effective_date date;
166 l_end_date date;
167 begin
168 hr_utility.set_location('Entering:'|| l_proc, 10);
169 --
170 -- Issue a savepoint
171 --
172 savepoint UPDATE_FORUM_THREAD;
173 --
174 -- Truncate the time portion from all IN date parameters
175 --
176 l_effective_date := trunc(p_effective_date);
177 --
178 -- Call Before Process User Hook
179 --
180 begin
181 ota_forum_thread_bk2.update_forum_thread_b
182 (p_effective_date => l_effective_date
183 ,p_forum_id => p_forum_id
184 ,p_business_group_id => p_business_group_id
185 ,p_subject => p_subject
186 ,p_private_thread_flag => p_private_thread_flag
187 ,p_last_post_date => p_last_post_date
188 ,p_reply_count => p_reply_count
189 ,p_forum_thread_id => p_forum_thread_id
190 ,p_object_version_number => l_object_version_number
191 );
192 exception
193 when hr_api.cannot_find_prog_unit then
194 hr_api.cannot_find_prog_unit_error
195 (p_module_name => 'UPDATE_FORUM_THREAD'
196 ,p_hook_type => 'BP'
197 );
198 end;
199
200
201 -- Validation in addition to Row Handlers
202 --
203 --
204 -- Process Logic
205 --
206
207 ota_fts_upd.upd
208 ( p_effective_date => l_effective_date
209 ,p_forum_id => p_forum_id
210 ,p_business_group_id => p_business_group_id
211 ,p_subject => p_subject
212 ,p_private_thread_flag => p_private_thread_flag
213 ,p_last_post_date => p_last_post_date
214 ,p_reply_count => p_reply_count
215 ,p_forum_thread_id => p_forum_thread_id
216 ,p_object_version_number => l_object_version_number
217 );
218
219
220 --
221 -- Call After Process User Hook
222 begin
223 ota_forum_thread_bk2.update_forum_thread_a
224 (p_effective_date => p_effective_date
225 ,p_forum_id => p_forum_id
226 ,p_business_group_id => p_business_group_id
227 ,p_subject => p_subject
228 ,p_private_thread_flag => p_private_thread_flag
229 ,p_last_post_date => p_last_post_date
230 ,p_reply_count => p_reply_count
231 ,p_forum_thread_id => p_forum_thread_id
232 ,p_object_version_number => l_object_version_number
233 );
234 exception
235 when hr_api.cannot_find_prog_unit then
236 hr_api.cannot_find_prog_unit_error
237 (p_module_name => 'UPDATE_FORUM_THREAD'
238 ,p_hook_type => 'AP'
239 );
240 end;
241 --
242 -- When in validation only mode raise the Validate_Enabled exception
243 --
244 if p_validate then
245 raise hr_api.validate_enabled;
246 end if;
247 --
248 -- Set all output arguments
249 --
250 p_object_version_number := l_object_version_number;
251
252
253 hr_utility.set_location(' Leaving:'||l_proc, 70);
254 exception
255 when hr_api.validate_enabled then
256 --
257 -- As the Validate_Enabled exception has been raised
258 -- we must rollback to the savepoint
259 --
260 rollback to UPDATE_FORUM_THREAD;
261 --
262 -- Only set output warning arguments
263 -- (Any key or derived arguments must be set to null
264 -- when validation only mode is being used.)
265 --
266 p_object_version_number := null;
267 hr_utility.set_location(' Leaving:'||l_proc, 80);
268 when others then
269 --
270 -- A validation or unexpected error has occured
271 --
272 rollback to UPDATE_FORUM_THREAD;
273 p_object_version_number := null;
274 hr_utility.set_location(' Leaving:'||l_proc, 90);
275 raise;
276 end update_forum_thread;
277 -- ----------------------------------------------------------------------------
278 -- |-------------------------< DELETE_FORUM_THREAD > --------------------------|
279 -- ----------------------------------------------------------------------------
280 --
281 procedure delete_forum_thread
282 (p_validate in boolean default false
283 ,p_forum_thread_id in number
284 ,p_object_version_number in number
285 ) is
286 --
287 -- Declare cursors and local variables
288 --
289 CURSOR csr_child_message is
290 SELECT
291 forum_message_id,
292 object_version_number
293 FROM ota_forum_messages
294 where forum_thread_id = p_forum_thread_id;
295
296 --
297 l_proc varchar2(72) := g_package||' Delete_forum_thread';
298 l_object_version_id number;
299 v_forum_message_id number;
300 v_object_version_number number;
301 --
302 --
303 begin
304 hr_utility.set_location('Entering:'|| l_proc, 10);
305 --
306 -- Issue a savepoint
307 --
308 savepoint DELETE_FORUM_THREAD;
309 --
310 -- Truncate the time portion from all IN date parameters
311 --
312 --
313
314 -- Call Before Process User Hook
315 begin
316 ota_forum_thread_bk3.delete_forum_thread_b
317 (p_forum_thread_id => p_forum_thread_id
318 ,p_object_version_number => p_object_version_number
319 );
320 exception
321 when hr_api.cannot_find_prog_unit then
322 hr_api.cannot_find_prog_unit_error
323 (p_module_name => 'DELETE_FORUM_THREAD'
324 ,p_hook_type => 'BP'
325 );
326 end;
327
328 --
329 -- Validation in addition to Row Handlers
330 --
331 --
332 -- Process Logic
333 --
334 --delete all the messages first
335 OPEN csr_child_message;
336 FETCH csr_child_message into v_forum_message_id, v_object_version_number;
337
338 LOOP
339 Exit When csr_child_message%notfound OR csr_child_message%notfound is null;
340
341 --ota_forum_message_api.delete_forum_message(p_validate,v_forum_message_id,v_object_version_number);
342 ota_fms_del.del
343 (p_forum_message_id => v_forum_message_id
344 ,p_object_version_number => v_object_version_number
345 );
346
347 FETCH csr_child_message into v_forum_message_id, v_object_version_number;
348 End Loop;
349 Close csr_child_message;
350
351 --delete the given forum thread
352 ota_fts_del.del
353 (p_forum_thread_id => p_forum_thread_id
354 ,p_object_version_number => p_object_version_number
355 );
356
357
358 --
359 -- Call After Process User Hook
360 begin
361 ota_forum_thread_bk3.delete_forum_thread_a
362 (p_forum_thread_id => p_forum_thread_id
363 ,p_object_version_number => p_object_version_number
364 );
365 exception
366 when hr_api.cannot_find_prog_unit then
367 hr_api.cannot_find_prog_unit_error
368 (p_module_name => 'DELETE_FORUM_THREAD'
369 ,p_hook_type => 'AP'
370 );
371 end;
372 --
373 -- When in validation only mode raise the Validate_Enabled exception
374 --
375 if p_validate then
376 raise hr_api.validate_enabled;
377 end if;
378 --
379 -- Set all output arguments
380 --
381 --
382 hr_utility.set_location(' Leaving:'||l_proc, 170);
383 exception
384 when hr_api.validate_enabled then
385 --
389 rollback to DELETE_FORUM_THREAD;
386 -- As the Validate_Enabled exception has been raised
387 -- we must rollback to the savepoint
388 --
390 --
391 -- Only set output warning arguments
392 -- (Any key or derived arguments must be set to null
393 -- when validation only mode is being used.)
394 --
395 hr_utility.set_location(' Leaving:'||l_proc, 180);
396 when others then
397 --
398 -- A validation or unexpected error has occured
399 --
400 rollback to DELETE_FORUM_THREAD;
401 hr_utility.set_location(' Leaving:'||l_proc, 190);
402 raise;
403 end delete_forum_thread;
404 --
405 end ota_forum_thread_api;