[Home] [Help]
PACKAGE BODY: APPS.GHR_PA_REMARKS_API
Source
1 Package Body ghr_pa_remarks_api as
2 /* $Header: ghpreapi.pkb 120.1 2006/01/27 12:31:54 vravikan noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'ghr_pa_remarks_api.';
7
8 --
9 -- ----------------------------------------------------------------------------
10 -- |--------------------------< create_pa_remarks> >--------------------------|
11 -- ----------------------------------------------------------------------------
12 --
13
14 --Assumption : Create_pa_remarks is manipulated as create/update based on the existence of the record
15 --only where OVN = 1, else according to this logic a new rec. will be created.
16
17 procedure create_pa_remarks
18 (p_validate in boolean default false
19 ,p_pa_request_id in number
20 ,p_remark_id in number
21 ,p_description in varchar2 default null
22 ,p_remark_code_information1 in varchar2 default null
23 ,p_remark_code_information2 in varchar2 default null
24 ,p_remark_code_information3 in varchar2 default null
25 ,p_remark_code_information4 in varchar2 default null
26 ,p_remark_code_information5 in varchar2 default null
27 ,p_pa_remark_id out nocopy number
28 ,p_object_version_number out nocopy number
29 ) is
30 --
31 -- Declare cursors and local variables
32 --
33 Cursor C_Sel1 is
34 Select pre.pa_remark_id,
35 pre.object_version_number
36 from ghr_pa_remarks pre,
37 ghr_noac_remarks nre
38 where pre.pa_request_id = p_pa_request_id and
39 pre.remark_id = p_remark_id and
40 nre.remark_id = pre.remark_id and
41 (nre.nature_of_action_id =
42 (select par.first_noa_id
43 from ghr_pa_requests par
44 where pa_request_id = p_pa_request_id
45 ) or
46 nre.nature_of_action_id =
47 (select par.second_noa_id
48 from ghr_pa_requests par
49 where pa_request_id = p_pa_request_id
50 )
51 )
52 and
53 nvl(nre.required_flag,hr_api.g_varchar2) = 'Y';
54
55
56 l_proc varchar2(72) := g_package || 'create_pa_remarks';
57 l_exists boolean := false;
58 l_object_version_number ghr_pa_remarks.object_version_number%TYPE;
59 l_pa_remark_id ghr_pa_remarks.pa_remark_id%TYPE;
60
61 begin
62 -- check if required remarks already exists , as it might have been populated,by the create_sf52 bp,
63 -- prior to this call.
64
65 for remark in C_Sel1 loop
66 l_exists := TRUE;
67 l_pa_remark_id := remark.pa_remark_id;
68 l_object_version_number := remark.object_version_number;
69 end loop;
70
71 hr_utility.set_location('Entering:'|| l_proc, 5);
72 --
73 --
74 savepoint create_pa_remarks;
75 --
76 --
77 -- Call Before Process User Hook
78 --
79 begin
80 ghr_pa_remarks_bk1.create_pa_remarks_b (
81 p_pa_request_id => p_pa_request_id
82 ,p_remark_id => p_remark_id
83 ,p_description => p_description
84 ,p_remark_code_information1 => p_remark_code_information1
85 ,p_remark_code_information2 => p_remark_code_information2
86 ,p_remark_code_information3 => p_remark_code_information3
87 ,p_remark_code_information4 => p_remark_code_information4
88 ,p_remark_code_information5 => p_remark_code_information5
89 );
90 exception
91 when hr_api.cannot_find_prog_unit then
92 hr_api.cannot_find_prog_unit_error
93 (p_module_name => 'create_ghr_pa_remarks',
94 p_hook_type => 'BP'
95 );
96 end;
97 --
98 -- End of Before Process User Hook call
99 --
100 -- Process Logic
101 --
102 If l_exists then
103 -- call update procedure
104 l_object_version_number := 1;
105 update_pa_remarks
106 (p_validate => p_validate
107 ,p_pa_remark_id => l_pa_remark_id
108 ,p_description => p_description
109 ,p_remark_code_information1 => p_remark_code_information1
110 ,p_remark_code_information2 => p_remark_code_information2
111 ,p_remark_code_information3 => p_remark_code_information3
112 ,p_remark_code_information4 => p_remark_code_information4
113 ,p_remark_code_information5 => p_remark_code_information5
114 ,p_object_version_number =>l_object_version_number
115 );
116 else
117
118 -- if record does not exist already , call ins procedure to create a new record
119 ghr_pre_ins.ins
120 (p_pa_remark_id => l_pa_remark_id
121 ,p_pa_request_id => p_pa_request_id
122 ,p_remark_id => p_remark_id
123 ,p_description => p_description
124 ,p_remark_code_information1 => p_remark_code_information1
125 ,p_remark_code_information2 => p_remark_code_information2
126 ,p_remark_code_information3 => p_remark_code_information3
127 ,p_remark_code_information4 => p_remark_code_information4
128 ,p_remark_code_information5 => p_remark_code_information5
129 ,p_object_version_number => l_object_version_number
130 ,p_validate => p_validate
131 );
132 end if;
133 --
134 -- When in validation only mode raise the Validate_Enabled exception
135 --
136 --
137 -- Call After Process User Hook
138 --
139 begin
140 ghr_pa_remarks_bk1.create_pa_remarks_a (
141 p_pa_remark_id => l_pa_remark_id
142 ,p_pa_request_id => p_pa_request_id
143 ,p_remark_id => p_remark_id
144 ,p_description => p_description
145 ,p_remark_code_information1 => p_remark_code_information1
146 ,p_remark_code_information2 => p_remark_code_information2
147 ,p_remark_code_information3 => p_remark_code_information3
148 ,p_remark_code_information4 => p_remark_code_information4
149 ,p_remark_code_information5 => p_remark_code_information5
150 ,p_object_version_number => l_object_version_number
151 );
152 exception
153 when hr_api.cannot_find_prog_unit then
154 hr_api.cannot_find_prog_unit_error
155 (p_module_name => 'create_ghr_pa_remarks',
156 p_hook_type => 'AP'
157 );
158 end;
159 --
160 -- End of Before Process User Hook call
161 --
162 if p_validate then
163 raise hr_api.validate_enabled;
164 end if;
165 --
166 -- Set all output arguments
167 --
168 p_pa_remark_id := l_pa_remark_id;
169 p_object_version_number := l_object_version_number;
170
171 --
172 hr_utility.set_location(' Leaving:'||l_proc, 11);
173 exception
174 when hr_api.validate_enabled then
175 --
176 -- As the Validate_Enabled exception has been raised
177 -- we must rollback to the savepoint
178 --
179 ROLLBACK TO create_pa_remarks;
180 --
181 -- Only set output warning arguments
182 -- (Any key or derived arguments must be set to null
183 -- when validation only mode is being used.)
184 --
185 p_pa_remark_id := null;
186 p_object_version_number := null;
187
188 when others then
189 ROLLBACK TO create_pa_remarks;
190 p_pa_remark_id := null;
191 p_object_version_number := null;
192 raise;
193
194 hr_utility.set_location(' Leaving:'||l_proc, 12);
195
196 end create_pa_remarks;
197 --
198
199 -- ----------------------------------------------------------------------------
200 -- |--------------------------< update_pa_remarks> >--------------------------|
201 -- ----------------------------------------------------------------------------
202 --
203 procedure update_pa_remarks
204 (p_validate in boolean default false
205 ,p_pa_remark_id in number
206 ,p_object_version_number in out nocopy number
207 ,p_remark_code_information1 in varchar2 default hr_api.g_varchar2
208 ,p_remark_code_information2 in varchar2 default hr_api.g_varchar2
209 ,p_remark_code_information3 in varchar2 default hr_api.g_varchar2
210 ,p_remark_code_information4 in varchar2 default hr_api.g_varchar2
211 ,p_remark_code_information5 in varchar2 default hr_api.g_varchar2
212 ,p_description in varchar2 default hr_api.g_varchar2
213 )
214 is
215 l_proc varchar2(72) := g_package || 'update_pa_remarks';
216 l_object_version_number ghr_pa_remarks.object_version_number%TYPE;
217 l_init_ovn ghr_pa_remarks.object_version_number%TYPE;
218
219 begin
220 hr_utility.set_location('Entering:'|| l_proc, 5);
221 --
222 l_init_ovn := p_object_version_number;
223 savepoint update_pa_remarks;
224 --
225 -- Call Before Process User Hook
226 --
227 begin
228 ghr_pa_remarks_bk2.update_pa_remarks_b (
229 p_pa_remark_id => p_pa_remark_id
230 ,p_description => p_description
231 ,p_remark_code_information1 => p_remark_code_information1
232 ,p_remark_code_information2 => p_remark_code_information2
233 ,p_remark_code_information3 => p_remark_code_information3
234 ,p_remark_code_information4 => p_remark_code_information4
235 ,p_remark_code_information5 => p_remark_code_information5
236 ,p_object_version_number => p_object_version_number
237 );
238 exception
239 when hr_api.cannot_find_prog_unit then
240 hr_api.cannot_find_prog_unit_error
241 (p_module_name => 'update_ghr_pa_remarks',
242 p_hook_type => 'BP'
243 );
244 end;
245 --
246 -- End of Before Process User Hook call
247 --
248 --
249 -- Store the original ovn in case we rollback when p_validate is true
250 --
251 l_object_version_number := p_object_version_number;
252
253 hr_utility.set_location(l_proc, 6);
254 ghr_pre_upd.upd
255 (p_pa_remark_id => p_pa_remark_id
256 ,p_description => p_description
257 ,p_remark_code_information1 => p_remark_code_information1
258 ,p_remark_code_information2 => p_remark_code_information2
259 ,p_remark_code_information3 => p_remark_code_information3
260 ,p_remark_code_information4 => p_remark_code_information4
261 ,p_remark_code_information5 => p_remark_code_information5
262 ,p_object_version_number => p_object_version_number
263 ,p_validate => p_validate
264 );
265 --
266 --
267 -- Call After Process User Hook
268 --
269 begin
270 ghr_pa_remarks_bk2.update_pa_remarks_a (
271 p_pa_remark_id => p_pa_remark_id
272 ,p_description => p_description
273 ,p_remark_code_information1 => p_remark_code_information1
274 ,p_remark_code_information2 => p_remark_code_information2
275 ,p_remark_code_information3 => p_remark_code_information3
276 ,p_remark_code_information4 => p_remark_code_information4
277 ,p_remark_code_information5 => p_remark_code_information5
278 ,p_object_version_number => l_object_version_number
279 );
280 exception
281 when hr_api.cannot_find_prog_unit then
282 hr_api.cannot_find_prog_unit_error
283 (p_module_name => 'update_ghr_pa_remarks',
284 p_hook_type => 'AP'
285 );
286 end;
287 --
288 -- End of After Process User Hook call
289 --
290 if p_validate then
291 raise hr_api.validate_enabled;
292 end if;
293 --
294 -- Set all output arguments
295 --
296 -- p_object_version_number := l_object_version_number;
297 --
298 hr_utility.set_location(' Leaving:'||l_proc, 11);
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_pa_remarks;
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 := l_object_version_number;
312 When Others then
313 ROLLBACK TO update_pa_remarks;
314 --
315 -- Reset IN OUT parameters and set OUT parameters
316 --
317 p_object_version_number := l_init_ovn;
318
319 raise;
320 hr_utility.set_location(' Leaving:'||l_proc, 12);
321 end update_pa_remarks;
322
323 --
324 --
325 -- ----------------------------------------------------------------------------
326 -- |-----------------------< delete_pa_remarks >------------------------|
327 -- ----------------------------------------------------------------------------
328 --
329 procedure delete_pa_remarks
330 (p_validate in boolean default false
331 ,p_pa_remark_id in number
332 ,p_object_version_number in number
333 ) is
334 --
335 -- Declare cursors and local variables
336 --
337 l_proc varchar2(72) := g_package||'delete_pa_remarks';
338 l_exists boolean := false;
339 --
340 Cursor C_remark_reqd1 is
341 select 1
342 from ghr_noac_remarks nre,
343 ghr_pa_remarks pre,
344 ghr_pa_requests par
345 where pre.pa_remark_id = p_pa_remark_id
346 and par.pa_request_id = pre.pa_request_id
347 and nre.remark_id = pre.remark_id
348 and nre.nature_of_action_id = par.first_noa_id
349 and nre.required_flag = 'Y'
350 and nre.enabled_flag = 'Y'
351 and nvl(par.effective_date,TRUNC(sysdate)) between nre.date_from
352 and nvl(nre.date_to,nvl(par.effective_date,TRUNC(sysdate)));
353 -- and nvl(par.effective_date,sysdate) between
354 -- nvl(start_date_active,nvl(par.effective_date,sysdate))
355 -- and nvl(end_date_active,nvl(par.effective_date,sysdate));
356 -- amended by SUE 3/24/97
357
358
359 Cursor C_remark_reqd2 is
360 select 1
361 from ghr_noac_remarks nre,
362 ghr_pa_remarks pre,
363 ghr_pa_requests par
364 where pre.pa_remark_id = p_pa_remark_id
365 and par.pa_request_id = pre.pa_request_id
366 and nre.remark_id = pre.remark_id
367 and nre.nature_of_action_id = par.second_noa_id
368 and nre.required_flag = 'Y'
369 and nre.enabled_flag = 'Y'
370 and nvl(par.effective_date,TRUNC(sysdate)) between nre.date_from
371 and nvl(nre.date_to,nvl(par.effective_date,TRUNC(sysdate)));
372
373 begin
374 hr_utility.set_location('Entering:'|| l_proc, 5);
375 --
376 --
377 savepoint delete_pa_remarks;
378 --
379 --
380 -- Call Before Process User Hook
381 --
382 begin
386 );
383 ghr_pa_remarks_bk3.delete_pa_remarks_b (
384 p_pa_remark_id => p_pa_remark_id
385 ,p_object_version_number => p_object_version_number
387 exception
388 when hr_api.cannot_find_prog_unit then
389 hr_api.cannot_find_prog_unit_error
390 (p_module_name => 'delete_ghr_pa_remarks',
391 p_hook_type => 'BP'
392 );
393 end;
394 --
395 -- End of Before Process User Hook call
396 --
397 hr_utility.set_location(l_proc, 7);
401 -- for the first_noa_id specified for the pa_request_id
398
399 --
400 -- Process Logic - Delete pa_remarks details if the specific pa_remark_id is not required
402 -- and for the second_noa_id
403
404 for remark_reqd in c_remark_reqd1 loop
405 l_exists := true;
406 exit;
407 end loop;
408 -- if it is a reqd. remark for the 1st Noa, then error
409 if l_exists then
410 hr_utility.set_message(8301,'GHR_38116_REM_REQD');
411 hr_utility.raise_error;
412 else
413 -- if not reqd. for 1st noa, then check if it is reqd. for 2nd noa
414 for remark_reqd in c_remark_reqd2 loop
415 l_exists := true;
416 end loop;
417 End if;
418 --if not reqd, then delete the remark
419 If not l_exists then
420 ghr_pre_del.del
421 (p_pa_remark_id => p_pa_remark_id
422 ,p_object_version_number => p_object_version_number
423 ,p_validate => p_validate
424 );
425 Else
426 -- error to indicate reqd. remark for 2nd noa
427 hr_utility.set_message(8301,'GHR_38128_REM_REQD_2');
428 hr_utility.raise_error;
429 End if;
430 --
431 hr_utility.set_location(l_proc, 8);
432 --
433 --
434 -- Call After Process User Hook
435 --
436 begin
437 ghr_pa_remarks_bk3.delete_pa_remarks_a (
438 p_pa_remark_id => p_pa_remark_id
439 ,p_object_version_number => p_object_version_number
440 );
441 exception
442 when hr_api.cannot_find_prog_unit then
443 hr_api.cannot_find_prog_unit_error
444 (p_module_name => 'delete_ghr_pa_remarks',
445 p_hook_type => 'AP'
446 );
447 end;
448 --
449 -- End of After Process User Hook call
450 --
451 -- When in validation only mode raise the Validate_Enabled exception
452 --
453 if p_validate then
454 raise hr_api.validate_enabled;
455 end if;
456 --
457 hr_utility.set_location(' Leaving:'||l_proc, 11);
458 exception
459 when hr_api.validate_enabled then
460 --
461 -- As the Validate_Enabled exception has been raised
462 -- we must rollback to the savepoint
463 --
464 ROLLBACK TO delete_pa_remarks;
465 --
466 When Others then
467 ROLLBACK TO delete_pa_remarks;
468 raise;
469
470 hr_utility.set_location(' Leaving:'||l_proc, 12);
471 end delete_pa_remarks;
472 --
473 --
474 end ghr_pa_remarks_api;