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