[Home] [Help]
PACKAGE BODY: APPS.IRC_ASSIGNMENT_DETAILS_API
Source
4 -- Package Variables
1 Package Body irc_assignment_details_api as
2 /* $Header: iriadapi.pkb 120.3.12010000.3 2010/05/18 14:44:54 vmummidi ship $ */
3 --
5 --
6 g_package varchar2(33) := ' irc_assignment_details_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-----------------------< create_assignment_details >----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_assignment_details
13 (p_validate in boolean default false
14 ,p_effective_date in date
15 ,p_assignment_id in number
16 ,p_attempt_id in number default null
17 ,p_qualified in varchar2 default null
18 ,p_considered in varchar2 default null
19 ,p_assignment_details_id out nocopy number
20 ,p_details_version out nocopy number
21 ,p_effective_start_date out nocopy date
22 ,p_effective_end_date out nocopy date
23 ,p_object_version_number out nocopy number
24 ) is
25 --
26 -- Declare cursors and local variables
27 --
28 l_latest_details irc_assignment_details_f.latest_details%TYPE;
29 -- out variables
30 l_assignment_details_id irc_assignment_details_f.assignment_details_id%TYPE;
31 l_details_version irc_assignment_details_f.details_version%TYPE;
32 l_effective_start_date irc_assignment_details_f.effective_start_date%TYPE;
33 l_effective_end_date irc_assignment_details_f.effective_end_date%TYPE;
34 l_object_version_number irc_assignment_details_f.object_version_number%TYPE;
35
36 l_effective_date date;
37 l_proc varchar2(72) := g_package||'create_assignment_details';
38 l_considered varchar2(30);
39 begin
40 hr_utility.set_location('Entering:'|| l_proc, 10);
41 --
42 -- Issue a savepoint
43 --
44 savepoint create_assignment_details;
45 --
46 -- Truncate the time portion from all IN date parameters
47 --
48 l_effective_date := trunc(p_effective_date);
49
50 hr_utility.set_location(l_proc, 20);
51 --
52 -- set the value of considered, if qualified is not null.
53 --
54 if ( p_qualified <> null ) then
55 l_considered := 'Y';
56 else
57 l_considered := p_considered;
58 end if;
59 --
60 -- Call Before Process User Hook
61 --
62 begin
63 irc_assignment_details_bk1.create_assignment_details_b
64 (p_effective_date => l_effective_date
65 ,p_assignment_id => p_assignment_id
66 ,p_attempt_id => p_attempt_id
67 );
68 exception
69 when hr_api.cannot_find_prog_unit then
70 hr_api.cannot_find_prog_unit_error
71 (p_module_name => 'create_assignment_details'
72 ,p_hook_type => 'BP'
73 );
74 end;
75 hr_utility.set_location(l_proc, 30);
76 --
77 -- Validation in addition to Row Handlers
78 --
79
80 --
81 -- Process Logic
82 --
83 irc_iad_ins.ins
84 (p_effective_date => l_effective_date
85 ,p_assignment_id => p_assignment_id
89 ,p_assignment_details_id => l_assignment_details_id
86 ,p_attempt_id => p_attempt_id
87 ,p_qualified => p_qualified
88 ,p_considered => l_considered
90 ,p_details_version => l_details_version
91 ,p_latest_details => l_latest_details
92 ,p_effective_start_date => l_effective_start_date
93 ,p_effective_end_date => l_effective_end_date
94 ,p_object_version_number => l_object_version_number
95 );
96 hr_utility.set_location(' Leaving:'||l_proc, 50);
97 --
98 -- Call After Process User Hook
99 --
100 begin
101 irc_assignment_details_bk1.create_assignment_details_a
102 (p_effective_date => l_effective_date
103 ,p_assignment_id => p_assignment_id
104 ,p_attempt_id => p_attempt_id
105 ,p_qualified => p_qualified
106 ,p_considered => l_considered
107 ,p_assignment_details_id => l_assignment_details_id
108 ,p_details_version => l_details_version
109 ,p_latest_details => l_latest_details
110 ,p_effective_start_date => l_effective_start_date
111 ,p_effective_end_date => l_effective_end_date
112 ,p_object_version_number => l_object_version_number
113 );
114 exception
115 when hr_api.cannot_find_prog_unit then
116 hr_api.cannot_find_prog_unit_error
117 (p_module_name => 'create_assignment_details'
118 ,p_hook_type => 'AP'
119 );
120 end;
121 --
122 -- When in validation only mode raise the Validate_Enabled exception
123 --
124 if p_validate then
125 raise hr_api.validate_enabled;
126 end if;
127 --
128 -- Set all IN OUT and OUT parameters with out values
129 --
130 p_assignment_details_id := l_assignment_details_id;
131 p_details_version := l_details_version;
132 p_effective_start_date := l_effective_start_date;
133 p_effective_end_date := l_effective_end_date;
134 p_object_version_number := l_object_version_number;
135 --
136 hr_utility.set_location(' Leaving:'||l_proc, 70);
137 exception
138 when hr_api.validate_enabled then
139 --
140 -- As the Validate_Enabled exception has been raised
141 -- we must rollback to the savepoint
142 --
143 rollback to create_assignment_details;
144 --
145 -- Reset IN OUT parameters and set OUT parameters
146 -- (Any key or derived arguments must be set to null
147 -- when validation only mode is being used.)
148 --
149 p_assignment_details_id := null;
150 p_details_version := null;
151 p_effective_start_date := null;
152 p_effective_end_date := null;
153 p_object_version_number := null;
154 --
155 hr_utility.set_location(' Leaving:'||l_proc, 80);
156 when others then
157 --
158 -- A validation or unexpected error has occured
159 --
160 rollback to create_assignment_details;
161 --
162 -- Reset IN OUT parameters and set all
163 -- OUT parameters, including warnings, to null
164 --
165 p_assignment_details_id := null;
166 p_details_version := null;
167 p_effective_start_date := null;
168 p_effective_end_date := null;
169 p_object_version_number := null;
170 --
171 hr_utility.set_location(' Leaving:'||l_proc, 90);
172 raise;
173 end create_assignment_details;
174 --
175 -- ----------------------------------------------------------------------------
176 -- |-----------------------< update_assignment_details >----------------------|
177 -- ----------------------------------------------------------------------------
178 --
179 procedure update_assignment_details
180 (p_validate in boolean default false
181 ,p_effective_date in date
182 ,p_datetrack_update_mode in varchar2
183 ,p_assignment_id in number default hr_api.g_number
184 ,p_attempt_id in number default hr_api.g_number
185 ,p_qualified in varchar2 default hr_api.g_varchar2
186 ,p_considered in varchar2 default hr_api.g_varchar2
187 ,p_assignment_details_id in out nocopy number
188 ,p_object_version_number in out nocopy number
189 ,p_details_version out nocopy number
190 ,p_effective_start_date out nocopy date
191 ,p_effective_end_date out nocopy date
192 ) is
193 --
194 -- Declare cursors and local variables
195 --
196 l_latest_details irc_assignment_details_f.latest_details%TYPE;
197 -- out variables
198 l_assignment_details_id irc_assignment_details_f.assignment_details_id%TYPE;
199 l_details_version irc_assignment_details_f.details_version%TYPE;
200 l_effective_start_date irc_assignment_details_f.effective_start_date%TYPE;
201 l_effective_end_date irc_assignment_details_f.effective_end_date%TYPE;
202 l_object_version_number irc_assignment_details_f.object_version_number%TYPE;
203
204 l_effective_date date;
205 l_proc varchar2(72) := g_package||'update_assignment_details';
206 l_considered varchar2(30);
207 begin
208 hr_utility.set_location('Entering:'|| l_proc, 10);
209 --
210 -- Issue a savepoint
211 --
212 savepoint update_assignment_details;
213 --
214 -- Truncate the time portion from all IN date parameters
215 --
216 l_effective_date := trunc(p_effective_date);
217 --
221 hr_utility.set_location(l_proc, 20);
218 l_assignment_details_id := p_assignment_details_id;
219 l_object_version_number := p_object_version_number;
220
222 --
223 -- set the value of considered, if qualified is not null.
224 --
225 if ( p_qualified <> hr_api.g_varchar2 ) then
226 l_considered := 'Y';
227 else
228 l_considered := p_considered;
229 end if;
230 --
231 -- Call Before Process User Hook
232 --
233 begin
234 irc_assignment_details_bk2.update_assignment_details_b
235 (p_effective_date => l_effective_date
236 ,p_datetrack_update_mode => p_datetrack_update_mode
237 ,p_assignment_id => p_assignment_id
238 ,p_attempt_id => p_attempt_id
239 ,p_assignment_details_id => l_assignment_details_id
240 ,p_object_version_number => l_object_version_number
241 ,p_qualified => p_qualified
242 ,p_considered => l_considered
243 );
244 exception
245 when hr_api.cannot_find_prog_unit then
246 hr_api.cannot_find_prog_unit_error
247 (p_module_name => 'update_assignment_details'
248 ,p_hook_type => 'BP'
249 );
250 end;
251 hr_utility.set_location(l_proc, 30);
252 --
253 -- Validation in addition to Row Handlers
254 --
255
256 --
257 -- Process Logic
258 --
259 irc_iad_upd.upd
260 (p_effective_date => l_effective_date
261 ,p_datetrack_mode => p_datetrack_update_mode
262 ,p_assignment_id => p_assignment_id
263 ,p_attempt_id => p_attempt_id
264 ,p_qualified => p_qualified
265 ,p_considered => l_considered
266 ,p_assignment_details_id => l_assignment_details_id
267 ,p_details_version => l_details_version
268 ,p_latest_details => l_latest_details
269 ,p_effective_start_date => l_effective_start_date
270 ,p_effective_end_date => l_effective_end_date
271 ,p_object_version_number => l_object_version_number
272 );
273 hr_utility.set_location(' Leaving:'||l_proc, 50);
274 --
275 -- Call After Process User Hook
276 --
277 begin
278 irc_assignment_details_bk2.update_assignment_details_a
279 (p_effective_date => l_effective_date
280 ,p_datetrack_update_mode => p_datetrack_update_mode
281 ,p_assignment_details_id => l_assignment_details_id
282 ,p_assignment_id => p_assignment_id
283 ,p_attempt_id => p_attempt_id
284 ,p_details_version => l_details_version
285 ,p_latest_details => l_latest_details
286 ,p_effective_start_date => l_effective_start_date
287 ,p_effective_end_date => l_effective_end_date
288 ,p_object_version_number => l_object_version_number
289 ,p_qualified => p_qualified
290 ,p_considered => l_considered
291 );
292 exception
293 when hr_api.cannot_find_prog_unit then
294 hr_api.cannot_find_prog_unit_error
295 (p_module_name => 'update_assignment_details'
296 ,p_hook_type => 'AP'
297 );
298 end;
299 --
300 -- When in validation only mode raise the Validate_Enabled exception
301 --
302 if p_validate then
303 raise hr_api.validate_enabled;
304 end if;
305 --
306 -- Set all IN OUT and OUT parameters with out values
307 --
308 p_assignment_details_id := l_assignment_details_id;
309 p_object_version_number := l_object_version_number;
310 p_details_version := l_details_version;
311 p_effective_start_date := l_effective_start_date;
312 p_effective_end_date := l_effective_end_date;
313 --
314 hr_utility.set_location(' Leaving:'||l_proc, 70);
315 exception
316 when hr_api.validate_enabled then
317 --
318 -- As the Validate_Enabled exception has been raised
319 -- we must rollback to the savepoint
320 --
321 rollback to update_assignment_details;
322 --
323 -- Reset IN OUT parameters and set OUT parameters
324 -- (Any key or derived arguments must be set to null
325 -- when validation only mode is being used.)
326 --
327 p_assignment_details_id := p_assignment_details_id;
328 p_object_version_number := p_object_version_number;
329 p_details_version := null;
330 p_effective_start_date := null;
331 p_effective_end_date := null;
332 --
333 hr_utility.set_location(' Leaving:'||l_proc, 80);
334 when others then
335 --
336 -- A validation or unexpected error has occured
337 --
338 rollback to update_assignment_details;
339 --
340 -- Reset IN OUT parameters and set all
341 -- OUT parameters, including warnings, to null
342 --
343 p_assignment_details_id := p_assignment_details_id;
344 p_object_version_number := p_object_version_number;
345 p_details_version := null;
346 p_effective_start_date := null;
347 p_effective_end_date := null;
348 --
349 hr_utility.set_location(' Leaving:'||l_proc, 90);
350 raise;
351 end update_assignment_details;
352 --
353 --
354 -- ----------------------------------------------------------------------------
355 -- |-------------------------< copy_assignment_details >----------------------|
356 -- ----------------------------------------------------------------------------
357 --
358 procedure copy_assignment_details
359 (p_source_assignment_id in number
360 ,p_target_assignment_id in number
361 ) is
362 --
363 -- Declare cursors and local variables
364 --
365 l_proc varchar2(72) := g_package||'copy_assignment_details';
366 --
367 l_assignment_details_id irc_assignment_details_f.assignment_details_id%type;
368 --
369 cursor csr_assignment_details is
370 select *
371 from irc_assignment_details_f
372 where assignment_id = p_source_assignment_id;
373 --
374 Cursor C_Sel1 is select irc_assignment_details_s.nextval from sys.dual;
375 --
376 begin
377 hr_utility.set_location('Entering:'|| l_proc, 10);
378 --
379 -- Issue a savepoint
380 --
381 savepoint copy_assignment_details;
382 --
383 --
384 -- Process Logic
385 --
386 FOR l_asg_det_rec in csr_assignment_details
387 LOOP
388 --
389 --
390 Open C_Sel1;
391 Fetch C_Sel1 Into l_assignment_details_id;
392 Close C_Sel1;
393 --
394 insert into irc_assignment_details_f
395 (assignment_details_id
396 ,assignment_id
397 ,effective_start_date
398 ,effective_end_date
399 ,details_version
400 ,latest_details
401 ,attempt_id
402 ,qualified
403 ,considered
404 ,object_version_number
405 )
406 Values
407 (l_assignment_details_id
408 ,p_target_assignment_id
409 ,l_asg_det_rec.effective_start_date
410 ,l_asg_det_rec.effective_end_date
411 ,l_asg_det_rec.details_version
412 ,l_asg_det_rec.latest_details
413 ,l_asg_det_rec.attempt_id
414 ,l_asg_det_rec.qualified
415 ,l_asg_det_rec.considered
416 ,l_asg_det_rec.object_version_number
417 );
418 --
419 END LOOP;
420 --
421 --
422 hr_utility.set_location(' Leaving:'||l_proc, 20);
423 exception
424 when hr_api.validate_enabled then
425 --
426 -- As the Validate_Enabled exception has been raised
427 -- we must rollback to the savepoint
428 --
429 rollback to copy_assignment_details;
430 --
431 hr_utility.set_location(' Leaving:'||l_proc, 30);
432 when others then
433 --
434 -- A validation or unexpected error has occured
435 --
436 rollback to copy_assignment_details;
437 --
438 hr_utility.set_location(' Leaving:'||l_proc, 40);
439 raise;
440 end copy_assignment_details;
441 --
442 end irc_assignment_details_api;