[Home] [Help]
PACKAGE BODY: APPS.HR_DOCUMENT_EXTRA_INFO_API
Source
1 Package Body hr_document_extra_info_api as
2 /* $Header: hrdeiapi.pkb 120.2 2005/06/08 12:45:07 sbuche noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_document_extra_info_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-------------------------< create_doc_extra_info >---------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_doc_extra_info(
13 p_validate in boolean default false
14 ,p_person_id in number
15 ,p_document_type_id in number
16 ,p_date_from in date
17 ,p_date_to in date
18 ,p_document_number in varchar2 default null
19 ,p_issued_by in varchar2 default null
20 ,p_issued_at in varchar2 default null
21 ,p_issued_date in date default null
22 ,p_issuing_authority in varchar2 default null
23 ,p_verified_by in number default null
24 ,p_verified_date in date default null
25 ,p_related_object_name in varchar2 default null
26 ,p_related_object_id_col in varchar2 default null
27 ,p_related_object_id in number default null
28 ,p_dei_attribute_category in varchar2 default null
29 ,p_dei_attribute1 in varchar2 default null
30 ,p_dei_attribute2 in varchar2 default null
31 ,p_dei_attribute3 in varchar2 default null
32 ,p_dei_attribute4 in varchar2 default null
33 ,p_dei_attribute5 in varchar2 default null
34 ,p_dei_attribute6 in varchar2 default null
35 ,p_dei_attribute7 in varchar2 default null
36 ,p_dei_attribute8 in varchar2 default null
37 ,p_dei_attribute9 in varchar2 default null
38 ,p_dei_attribute10 in varchar2 default null
39 ,p_dei_attribute11 in varchar2 default null
40 ,p_dei_attribute12 in varchar2 default null
41 ,p_dei_attribute13 in varchar2 default null
42 ,p_dei_attribute14 in varchar2 default null
43 ,p_dei_attribute15 in varchar2 default null
44 ,p_dei_attribute16 in varchar2 default null
45 ,p_dei_attribute17 in varchar2 default null
46 ,p_dei_attribute18 in varchar2 default null
47 ,p_dei_attribute19 in varchar2 default null
48 ,p_dei_attribute20 in varchar2 default null
49 ,p_dei_attribute21 in varchar2 default null
50 ,p_dei_attribute22 in varchar2 default null
51 ,p_dei_attribute23 in varchar2 default null
52 ,p_dei_attribute24 in varchar2 default null
53 ,p_dei_attribute25 in varchar2 default null
54 ,p_dei_attribute26 in varchar2 default null
55 ,p_dei_attribute27 in varchar2 default null
56 ,p_dei_attribute28 in varchar2 default null
57 ,p_dei_attribute29 in varchar2 default null
58 ,p_dei_attribute30 in varchar2 default null
59 ,p_dei_information_category in varchar2 default null
60 ,p_dei_information1 in varchar2 default null
61 ,p_dei_information2 in varchar2 default null
62 ,p_dei_information3 in varchar2 default null
63 ,p_dei_information4 in varchar2 default null
64 ,p_dei_information5 in varchar2 default null
65 ,p_dei_information6 in varchar2 default null
66 ,p_dei_information7 in varchar2 default null
67 ,p_dei_information8 in varchar2 default null
68 ,p_dei_information9 in varchar2 default null
69 ,p_dei_information10 in varchar2 default null
70 ,p_dei_information11 in varchar2 default null
71 ,p_dei_information12 in varchar2 default null
72 ,p_dei_information13 in varchar2 default null
73 ,p_dei_information14 in varchar2 default null
74 ,p_dei_information15 in varchar2 default null
75 ,p_dei_information16 in varchar2 default null
76 ,p_dei_information17 in varchar2 default null
77 ,p_dei_information18 in varchar2 default null
78 ,p_dei_information19 in varchar2 default null
79 ,p_dei_information20 in varchar2 default null
80 ,p_dei_information21 in varchar2 default null
81 ,p_dei_information22 in varchar2 default null
82 ,p_dei_information23 in varchar2 default null
83 ,p_dei_information24 in varchar2 default null
84 ,p_dei_information25 in varchar2 default null
85 ,p_dei_information26 in varchar2 default null
86 ,p_dei_information27 in varchar2 default null
87 ,p_dei_information28 in varchar2 default null
88 ,p_dei_information29 in varchar2 default null
89 ,p_dei_information30 in varchar2 default null
90 ,p_request_id in number default null
91 ,p_program_application_id in number default null
92 ,p_program_id in number default null
93 ,p_program_update_date in date default null
94 ,p_document_extra_info_id out nocopy number
95 ,p_object_version_number out nocopy number
96 ) is
97 --
98 -- Declare cursors and local variables
99 --
100
101 l_proc varchar2(72) := g_package||'create_doc_extra_info';
102 l_object_version_number hr_document_extra_info.object_version_number%type;
103 l_document_extra_info_id hr_document_extra_info.document_extra_info_id%type;
104 l_person_id hr_document_extra_info.person_id%type;
105 l_document_type varchar2(50);
106 l_document_type_id hr_document_extra_info.document_type_id%type;
107 l_date_from hr_document_extra_info.date_from%type;
108 l_date_to hr_document_extra_info.date_to%type;
109 l_mul_flag hr_document_types_v.multiple_occurences_flag%type;
110 l_num number;
111 l_authorization_required varchar2(10);
112 l_verified_by number := p_verified_by;
113 l_verified_date date := p_verified_date;
114 dummy varchar2(5);
115
116 cursor csr_get_document_type(p_document_type_id number)
117 is
118 select document_type,authorization_required
119 from hr_document_types_v
120 where document_type_id=p_document_type_id;
121
122 cursor csr_chk_combination(p_document_type_id number,p_person_id number,
123 p_date_from DATE,p_date_to DATE) is
124 select 'X'
125 from hr_document_extra_info
126 where document_type_id = p_document_type_id
127 and person_id = p_person_id
128 and date_from = p_date_from
129 and date_to = p_date_to;
130
131
132 cursor csr_chk_multiple(p_document_type_id number) is
133 select MULTIPLE_OCCURENCES_FLAG
134 from HR_DOCUMENT_TYPES_V
135 where DOCUMENT_TYPE_ID = p_document_type_id;
136
137 cursor csr_chk_doc_allowed(p_document_type_id number,p_person_id number) is
138 select count(*) from hr_document_extra_info
139 where document_type_id = p_document_type_id
140 and person_id = p_person_id;
141 --
142 begin
143 hr_utility.set_location('Entering:'|| l_proc, 5);
144 --
145 -- Issue a savepoint
146 --
147 savepoint create_doc_extra_info;
148 --
149 -- Call Before Process User Hook
150 --
151 begin
152
153 hr_document_extra_info_bk1.create_doc_extra_info_b
154 (p_person_id => p_person_id,
155 p_document_type_id => p_document_type_id,
156 p_date_from => p_date_from,
157 p_date_to => p_date_to,
158 p_document_number => p_document_number,
159 p_issued_by => p_issued_by,
160 p_issued_at => p_issued_at,
161 p_issued_date => p_issued_date,
162 p_issuing_authority => p_issuing_authority,
163 p_verified_by => p_verified_by,
164 p_verified_date => p_verified_date,
165 p_related_object_name => p_related_object_name,
166 p_related_object_id_col => p_related_object_id_col,
167 p_related_object_id => p_related_object_id,
168 p_dei_attribute_category => p_dei_attribute_category,
169 p_dei_attribute1 => p_dei_attribute1,
170 p_dei_attribute2 => p_dei_attribute2,
171 p_dei_attribute3 => p_dei_attribute3,
172 p_dei_attribute4 => p_dei_attribute4,
173 p_dei_attribute5 => p_dei_attribute5,
174 p_dei_attribute6 => p_dei_attribute6,
175 p_dei_attribute7 => p_dei_attribute7,
176 p_dei_attribute8 => p_dei_attribute8,
177 p_dei_attribute9 => p_dei_attribute9,
178 p_dei_attribute10 => p_dei_attribute10,
179 p_dei_attribute11 => p_dei_attribute11,
180 p_dei_attribute12 => p_dei_attribute12,
181 p_dei_attribute13 => p_dei_attribute13,
182 p_dei_attribute14 => p_dei_attribute14,
183 p_dei_attribute15 => p_dei_attribute15,
184 p_dei_attribute16 => p_dei_attribute16,
185 p_dei_attribute17 => p_dei_attribute17,
186 p_dei_attribute18 => p_dei_attribute18,
187 p_dei_attribute19 => p_dei_attribute19,
188 p_dei_attribute20 => p_dei_attribute20,
189 p_dei_attribute21 => p_dei_attribute21,
190 p_dei_attribute22 => p_dei_attribute22,
191 p_dei_attribute23 => p_dei_attribute23,
192 p_dei_attribute24 => p_dei_attribute24,
193 p_dei_attribute25 => p_dei_attribute25,
194 p_dei_attribute26 => p_dei_attribute26,
195 p_dei_attribute27 => p_dei_attribute27,
196 p_dei_attribute28 => p_dei_attribute28,
197 p_dei_attribute29 => p_dei_attribute29,
198 p_dei_attribute30 => p_dei_attribute30,
199 p_dei_information_category => p_dei_information_category,
200 p_dei_information1 => p_dei_information1,
201 p_dei_information2 => p_dei_information2,
202 p_dei_information3 => p_dei_information3,
203 p_dei_information4 => p_dei_information4,
204 p_dei_information5 => p_dei_information5,
205 p_dei_information6 => p_dei_information6,
206 p_dei_information7 => p_dei_information7,
207 p_dei_information8 => p_dei_information8,
208 p_dei_information9 => p_dei_information9,
209 p_dei_information10 => p_dei_information10,
210 p_dei_information11 => p_dei_information11,
211 p_dei_information12 => p_dei_information12,
212 p_dei_information13 => p_dei_information13,
213 p_dei_information14 => p_dei_information14,
214 p_dei_information15 => p_dei_information15,
215 p_dei_information16 => p_dei_information16,
216 p_dei_information17 => p_dei_information17,
217 p_dei_information18 => p_dei_information18,
218 p_dei_information19 => p_dei_information19,
219 p_dei_information20 => p_dei_information20,
220 p_dei_information21 => p_dei_information21,
221 p_dei_information22 => p_dei_information22,
222 p_dei_information23 => p_dei_information23,
223 p_dei_information24 => p_dei_information24,
224 p_dei_information25 => p_dei_information25,
225 p_dei_information26 => p_dei_information26,
226 p_dei_information27 => p_dei_information27,
227 p_dei_information28 => p_dei_information28,
228 p_dei_information29 => p_dei_information29,
229 p_dei_information30 => p_dei_information30,
230 p_request_id => p_request_id,
231 p_program_application_id => p_program_application_id,
232 p_program_id => p_program_id,
233 p_program_update_date => p_program_update_date
234 );
235 exception
236 when hr_api.cannot_find_prog_unit then
237 hr_api.cannot_find_prog_unit_error
238 (p_module_name => 'CREATE_DOC_EXTRA_INFO',
239 p_hook_type => 'BP'
240 );
241 end;
242 --
243 -- End of Before Process User Hook call
244 --
245 hr_utility.set_location(l_proc, 7);
246 open csr_get_document_type(p_document_type_id =>p_document_type_id);
247 fetch csr_get_document_type into l_document_type,l_authorization_required;
248 close csr_get_document_type;
249
250 open csr_chk_multiple(p_document_type_id =>p_document_type_id);
251 fetch csr_chk_multiple into l_mul_flag;
252 if (l_mul_flag = 'N') then
253 open csr_chk_doc_allowed(p_document_type_id =>p_document_type_id,
254 p_person_id=> p_person_id);
255 fetch csr_chk_doc_allowed into l_num;
256 if (l_num > 0) then
257 close csr_chk_doc_allowed;
258
259 hr_utility.set_message(800, 'HR_449709_DOR_MUL_NOT_ALW');
260 hr_utility.set_message_token('TYPE', l_document_type);
261 hr_utility.raise_error;
262 else
263 close csr_chk_doc_allowed;
264 hr_utility.set_location(l_proc, 8);
265 end if;
266 close csr_chk_multiple;
267 else
268 close csr_chk_multiple;
269 end if;
270
271
272 --
273 --
274 open csr_chk_combination(p_document_type_id =>p_document_type_id,
275 p_person_id=>p_person_id ,p_date_from =>p_date_from,p_date_to=>p_date_to );
276
277 fetch csr_chk_combination into dummy;
278 if csr_chk_combination%found then
279 close csr_chk_combination;
280
281 hr_utility.set_message(800, 'HR_449708_DOR_UNQ_PER_DOC');
282 hr_utility.set_message_token('TYPE', l_document_type);
283 hr_utility.set_message_token('DATE_FROM', p_date_from);
284 hr_utility.set_message_token('DATE_TO', p_date_to);
285 hr_utility.raise_error;
286 --
287 else
288 close csr_chk_combination;
289 hr_utility.set_location(l_proc, 9);
290 end if;
291
292 -- Added for checking Authorization Required, If value = 'N'
293 -- pass Default Values of verified by and verified date to 'ins'
294 if(l_authorization_required = 'N') then
295 l_verified_by := nvl(l_verified_by, fnd_global.USER_ID);
296 l_verified_date := nvl(l_verified_date, sysdate);
297 end if;
298
299
300
301 --
302 hr_dei_ins.ins
303 (
304 p_document_extra_info_id => l_document_extra_info_id,
305 p_person_id => p_person_id,
306 p_document_type_id => p_document_type_id,
307 p_date_from => p_date_from,
308 p_date_to => p_date_to,
309 p_document_number => p_document_number,
310 p_issued_by => p_issued_by,
311 p_issued_at => p_issued_at,
312 p_issued_date => p_issued_date,
313 p_issuing_authority => p_issuing_authority,
314 p_verified_by => l_verified_by,
315 p_verified_date => l_verified_date,
316 p_related_object_name => p_related_object_name,
317 p_related_object_id_col => p_related_object_id_col,
318 p_related_object_id => p_related_object_id,
319 p_dei_attribute_category => p_dei_attribute_category,
320 p_dei_attribute1 => p_dei_attribute1,
321 p_dei_attribute2 => p_dei_attribute2,
322 p_dei_attribute3 => p_dei_attribute3,
323 p_dei_attribute4 => p_dei_attribute4,
324 p_dei_attribute5 => p_dei_attribute5,
325 p_dei_attribute6 => p_dei_attribute6,
326 p_dei_attribute7 => p_dei_attribute7,
327 p_dei_attribute8 => p_dei_attribute8,
328 p_dei_attribute9 => p_dei_attribute9,
329 p_dei_attribute10 => p_dei_attribute10,
330 p_dei_attribute11 => p_dei_attribute11,
331 p_dei_attribute12 => p_dei_attribute12,
332 p_dei_attribute13 => p_dei_attribute13,
333 p_dei_attribute14 => p_dei_attribute14,
334 p_dei_attribute15 => p_dei_attribute15,
335 p_dei_attribute16 => p_dei_attribute16,
336 p_dei_attribute17 => p_dei_attribute17,
337 p_dei_attribute18 => p_dei_attribute18,
338 p_dei_attribute19 => p_dei_attribute19,
339 p_dei_attribute20 => p_dei_attribute20,
340 p_dei_attribute21 => p_dei_attribute21,
341 p_dei_attribute22 => p_dei_attribute22,
342 p_dei_attribute23 => p_dei_attribute23,
343 p_dei_attribute24 => p_dei_attribute24,
344 p_dei_attribute25 => p_dei_attribute25,
345 p_dei_attribute26 => p_dei_attribute26,
346 p_dei_attribute27 => p_dei_attribute27,
347 p_dei_attribute28 => p_dei_attribute28,
348 p_dei_attribute29 => p_dei_attribute29,
349 p_dei_attribute30 => p_dei_attribute30,
350 p_dei_information_category => p_dei_information_category,
351 p_dei_information1 => p_dei_information1,
352 p_dei_information2 => p_dei_information2,
353 p_dei_information3 => p_dei_information3,
354 p_dei_information4 => p_dei_information4,
355 p_dei_information5 => p_dei_information5,
356 p_dei_information6 => p_dei_information6,
357 p_dei_information7 => p_dei_information7,
358 p_dei_information8 => p_dei_information8,
359 p_dei_information9 => p_dei_information9,
360 p_dei_information10 => p_dei_information10,
361 p_dei_information11 => p_dei_information11,
362 p_dei_information12 => p_dei_information12,
363 p_dei_information13 => p_dei_information13,
364 p_dei_information14 => p_dei_information14,
365 p_dei_information15 => p_dei_information15,
366 p_dei_information16 => p_dei_information16,
367 p_dei_information17 => p_dei_information17,
368 p_dei_information18 => p_dei_information18,
369 p_dei_information19 => p_dei_information19,
370 p_dei_information20 => p_dei_information20,
371 p_dei_information21 => p_dei_information21,
372 p_dei_information22 => p_dei_information22,
373 p_dei_information23 => p_dei_information23,
374 p_dei_information24 => p_dei_information24,
375 p_dei_information25 => p_dei_information25,
376 p_dei_information26 => p_dei_information26,
377 p_dei_information27 => p_dei_information27,
378 p_dei_information28 => p_dei_information28,
379 p_dei_information29 => p_dei_information29,
380 p_dei_information30 => p_dei_information30,
381 p_request_id => p_request_id,
382 p_program_application_id => p_program_application_id,
383 p_program_id => p_program_id,
384 p_program_update_date => p_program_update_date,
385 p_object_version_number => l_object_version_number
386 );
387
388
389 p_object_version_number := l_object_version_number;
390 p_document_extra_info_id := l_document_extra_info_id;
391 --
392 hr_utility.set_location(l_proc, 8);
393 --
394 -- Call After Process User Hook
395 --
396 begin
397 hr_document_extra_info_bk1.create_doc_extra_info_a
398 (
399 p_document_extra_info_id => l_document_extra_info_id,
400 p_person_id => p_person_id,
401 p_document_type_id => p_document_type_id,
402 p_date_from => p_date_from,
403 p_date_to => p_date_to,
404 p_document_number => p_document_number,
405 p_issued_by => p_issued_by,
406 p_issued_at => p_issued_at,
407 p_issued_date => p_issued_date,
408 p_issuing_authority => p_issuing_authority,
409 p_verified_by => p_verified_by,
410 p_verified_date => p_verified_date,
411 p_related_object_name => p_related_object_name,
412 p_related_object_id_col => p_related_object_id_col,
413 p_related_object_id => p_related_object_id,
414 p_dei_attribute_category => p_dei_attribute_category,
415 p_dei_attribute1 => p_dei_attribute1,
416 p_dei_attribute2 => p_dei_attribute2,
417 p_dei_attribute3 => p_dei_attribute3,
418 p_dei_attribute4 => p_dei_attribute4,
419 p_dei_attribute5 => p_dei_attribute5,
420 p_dei_attribute6 => p_dei_attribute6,
421 p_dei_attribute7 => p_dei_attribute7,
422 p_dei_attribute8 => p_dei_attribute8,
423 p_dei_attribute9 => p_dei_attribute9,
424 p_dei_attribute10 => p_dei_attribute10,
425 p_dei_attribute11 => p_dei_attribute11,
426 p_dei_attribute12 => p_dei_attribute12,
427 p_dei_attribute13 => p_dei_attribute13,
428 p_dei_attribute14 => p_dei_attribute14,
429 p_dei_attribute15 => p_dei_attribute15,
430 p_dei_attribute16 => p_dei_attribute16,
431 p_dei_attribute17 => p_dei_attribute17,
432 p_dei_attribute18 => p_dei_attribute18,
433 p_dei_attribute19 => p_dei_attribute19,
434 p_dei_attribute20 => p_dei_attribute20,
435 p_dei_attribute21 => p_dei_attribute21,
436 p_dei_attribute22 => p_dei_attribute22,
437 p_dei_attribute23 => p_dei_attribute23,
438 p_dei_attribute24 => p_dei_attribute24,
439 p_dei_attribute25 => p_dei_attribute25,
440 p_dei_attribute26 => p_dei_attribute26,
441 p_dei_attribute27 => p_dei_attribute27,
442 p_dei_attribute28 => p_dei_attribute28,
443 p_dei_attribute29 => p_dei_attribute29,
444 p_dei_attribute30 => p_dei_attribute30,
445 p_dei_information_category => p_dei_information_category,
446 p_dei_information1 => p_dei_information1,
447 p_dei_information2 => p_dei_information2,
448 p_dei_information3 => p_dei_information3,
449 p_dei_information4 => p_dei_information4,
450 p_dei_information5 => p_dei_information5,
451 p_dei_information6 => p_dei_information6,
452 p_dei_information7 => p_dei_information7,
453 p_dei_information8 => p_dei_information8,
454 p_dei_information9 => p_dei_information9,
455 p_dei_information10 => p_dei_information10,
456 p_dei_information11 => p_dei_information11,
457 p_dei_information12 => p_dei_information12,
458 p_dei_information13 => p_dei_information13,
459 p_dei_information14 => p_dei_information14,
460 p_dei_information15 => p_dei_information15,
461 p_dei_information16 => p_dei_information16,
462 p_dei_information17 => p_dei_information17,
463 p_dei_information18 => p_dei_information18,
464 p_dei_information19 => p_dei_information19,
465 p_dei_information20 => p_dei_information20,
466 p_dei_information21 => p_dei_information21,
467 p_dei_information22 => p_dei_information22,
468 p_dei_information23 => p_dei_information23,
469 p_dei_information24 => p_dei_information24,
470 p_dei_information25 => p_dei_information25,
471 p_dei_information26 => p_dei_information26,
472 p_dei_information27 => p_dei_information27,
473 p_dei_information28 => p_dei_information28,
474 p_dei_information29 => p_dei_information29,
475 p_dei_information30 => p_dei_information30,
476 p_request_id => p_request_id,
477 p_program_application_id => p_program_application_id,
478 p_program_id => p_program_id,
479 p_program_update_date => p_program_update_date,
480 p_object_version_number => l_object_version_number
481 );
482 exception
483 when hr_api.cannot_find_prog_unit then
484 hr_api.cannot_find_prog_unit_error
485 (p_module_name => 'CREATE_DOC_EXTRA_INFO',
486 p_hook_type => 'AP'
487 );
488 end;
489 --
490 -- End of After Process User Hook call
491 --
492 -- When in validation only mode raise the Validate_Enabled exception
493 --
494 if p_validate then
495 raise hr_api.validate_enabled;
496 end if;
497 --
498 hr_utility.set_location(' Leaving:'||l_proc, 11);
499 exception
500 when hr_api.validate_enabled then
501 --
502 -- As the Validate_Enabled exception has been raised
503 -- we must rollback to the savepoint
504 --
505 ROLLBACK TO create_doc_extra_info;
506 --
507 -- Only set output warning arguments
508 -- (Any key or derived arguments must be set to null
509 -- when validation only mode is being used.)
510 --
511 p_document_extra_info_id := null;
512 p_object_version_number := null;
513 --
514 hr_utility.set_location(' Leaving:'||l_proc, 12);
515 --
516 when others then
517 --
518 -- A validation or unexpected error has occurred
519 --
520 --
521 ROLLBACK TO create_doc_extra_info;
522 --
523 -- set in out parameters and set out parameters
524 --
525 p_document_extra_info_id := null;
526 p_object_version_number := null;
527 --
528 raise;
529 --
530 end create_doc_extra_info;
531 --
532 --
533 -- ----------------------------------------------------------------------------
534 -- |-----------------------< update_doc_extra_info >------------------------|
535 -- ----------------------------------------------------------------------------
536 --
537 procedure update_doc_extra_info
538 ( p_validate in boolean default false
539 ,p_document_extra_info_id in number
540 ,p_person_id in number
541 ,p_document_type_id in number
542 ,p_date_from in date
543 ,p_date_to in date
544 ,p_document_number in varchar2 default hr_api.g_varchar2
545 ,p_issued_by in varchar2 default hr_api.g_varchar2
546 ,p_issued_at in varchar2 default hr_api.g_varchar2
547 ,p_issued_date in date default hr_api.g_date
548 ,p_issuing_authority in varchar2 default hr_api.g_varchar2
549 ,p_verified_by in number default hr_api.g_number
550 ,p_verified_date in date default hr_api.g_date
551 ,p_related_object_name in varchar2 default hr_api.g_varchar2
552 ,p_related_object_id_col in varchar2 default hr_api.g_varchar2
553 ,p_related_object_id in number default hr_api.g_number
554 ,p_dei_attribute_category in varchar2 default hr_api.g_varchar2
555 ,p_dei_attribute1 in varchar2 default hr_api.g_varchar2
556 ,p_dei_attribute2 in varchar2 default hr_api.g_varchar2
557 ,p_dei_attribute3 in varchar2 default hr_api.g_varchar2
558 ,p_dei_attribute4 in varchar2 default hr_api.g_varchar2
559 ,p_dei_attribute5 in varchar2 default hr_api.g_varchar2
560 ,p_dei_attribute6 in varchar2 default hr_api.g_varchar2
561 ,p_dei_attribute7 in varchar2 default hr_api.g_varchar2
562 ,p_dei_attribute8 in varchar2 default hr_api.g_varchar2
563 ,p_dei_attribute9 in varchar2 default hr_api.g_varchar2
564 ,p_dei_attribute10 in varchar2 default hr_api.g_varchar2
565 ,p_dei_attribute11 in varchar2 default hr_api.g_varchar2
566 ,p_dei_attribute12 in varchar2 default hr_api.g_varchar2
567 ,p_dei_attribute13 in varchar2 default hr_api.g_varchar2
568 ,p_dei_attribute14 in varchar2 default hr_api.g_varchar2
569 ,p_dei_attribute15 in varchar2 default hr_api.g_varchar2
570 ,p_dei_attribute16 in varchar2 default hr_api.g_varchar2
571 ,p_dei_attribute17 in varchar2 default hr_api.g_varchar2
572 ,p_dei_attribute18 in varchar2 default hr_api.g_varchar2
573 ,p_dei_attribute19 in varchar2 default hr_api.g_varchar2
574 ,p_dei_attribute20 in varchar2 default hr_api.g_varchar2
575 ,p_dei_attribute21 in varchar2 default hr_api.g_varchar2
576 ,p_dei_attribute22 in varchar2 default hr_api.g_varchar2
577 ,p_dei_attribute23 in varchar2 default hr_api.g_varchar2
578 ,p_dei_attribute24 in varchar2 default hr_api.g_varchar2
579 ,p_dei_attribute25 in varchar2 default hr_api.g_varchar2
580 ,p_dei_attribute26 in varchar2 default hr_api.g_varchar2
581 ,p_dei_attribute27 in varchar2 default hr_api.g_varchar2
582 ,p_dei_attribute28 in varchar2 default hr_api.g_varchar2
583 ,p_dei_attribute29 in varchar2 default hr_api.g_varchar2
584 ,p_dei_attribute30 in varchar2 default hr_api.g_varchar2
585 ,p_dei_information_category in varchar2 default hr_api.g_varchar2
586 ,p_dei_information1 in varchar2 default hr_api.g_varchar2
587 ,p_dei_information2 in varchar2 default hr_api.g_varchar2
588 ,p_dei_information3 in varchar2 default hr_api.g_varchar2
589 ,p_dei_information4 in varchar2 default hr_api.g_varchar2
590 ,p_dei_information5 in varchar2 default hr_api.g_varchar2
591 ,p_dei_information6 in varchar2 default hr_api.g_varchar2
592 ,p_dei_information7 in varchar2 default hr_api.g_varchar2
593 ,p_dei_information8 in varchar2 default hr_api.g_varchar2
594 ,p_dei_information9 in varchar2 default hr_api.g_varchar2
595 ,p_dei_information10 in varchar2 default hr_api.g_varchar2
596 ,p_dei_information11 in varchar2 default hr_api.g_varchar2
597 ,p_dei_information12 in varchar2 default hr_api.g_varchar2
598 ,p_dei_information13 in varchar2 default hr_api.g_varchar2
599 ,p_dei_information14 in varchar2 default hr_api.g_varchar2
600 ,p_dei_information15 in varchar2 default hr_api.g_varchar2
601 ,p_dei_information16 in varchar2 default hr_api.g_varchar2
602 ,p_dei_information17 in varchar2 default hr_api.g_varchar2
603 ,p_dei_information18 in varchar2 default hr_api.g_varchar2
604 ,p_dei_information19 in varchar2 default hr_api.g_varchar2
605 ,p_dei_information20 in varchar2 default hr_api.g_varchar2
606 ,p_dei_information21 in varchar2 default hr_api.g_varchar2
607 ,p_dei_information22 in varchar2 default hr_api.g_varchar2
608 ,p_dei_information23 in varchar2 default hr_api.g_varchar2
609 ,p_dei_information24 in varchar2 default hr_api.g_varchar2
610 ,p_dei_information25 in varchar2 default hr_api.g_varchar2
611 ,p_dei_information26 in varchar2 default hr_api.g_varchar2
612 ,p_dei_information27 in varchar2 default hr_api.g_varchar2
613 ,p_dei_information28 in varchar2 default hr_api.g_varchar2
614 ,p_dei_information29 in varchar2 default hr_api.g_varchar2
615 ,p_dei_information30 in varchar2 default hr_api.g_varchar2
616 ,p_request_id in number default hr_api.g_number
617 ,p_program_application_id in number default hr_api.g_number
618 ,p_program_id in number default hr_api.g_number
619 ,p_program_update_date in date default hr_api.g_date
620 ,p_object_version_number in out nocopy number
621 ) is
622 --
623 -- Declare cursors and local variables
624 --
625 l_proc varchar2(72) := g_package||'update_doc_extra_info';
626 l_object_version_number hr_document_extra_info.object_version_number%TYPE;
627 l_ovn hr_document_extra_info.object_version_number%TYPE := p_object_version_number;
628 l_authorization_required varchar2(10);
629 l_verified_by number := p_verified_by;
630 l_verified_date date := p_verified_date;
631 l_document_type varchar2(50);
632 l_mul_flag hr_document_types_v.multiple_occurences_flag%type;
633 l_num number;
634 dummy varchar2(5);
635
636 cursor csr_get_document_type(p_document_type_id number)
637 is
638 select document_type,authorization_required
639 from hr_document_types_v
640 where document_type_id=p_document_type_id;
641
642 cursor csr_chk_combination(p_document_type_id number,p_person_id number,p_date_from DATE,
643 p_date_to DATE,p_document_extra_info_id number) is
644 select 'X'
645 from hr_document_extra_info
646 where document_type_id = p_document_type_id
647 and person_id = p_person_id
648 and date_from = p_date_from
649 and date_to = p_date_to
650 and document_extra_info_id <> p_document_extra_info_id;
651
652
653 cursor csr_chk_multiple(p_document_type_id number) is
654 select MULTIPLE_OCCURENCES_FLAG
655 from HR_DOCUMENT_TYPES_V
656 where DOCUMENT_TYPE_ID = p_document_type_id;
657
658 cursor csr_chk_doc_allowed(p_document_type_id number,p_person_id number,
659 p_document_extra_info_id number) is
660 select count(*) from hr_document_extra_info
661 where document_type_id = p_document_type_id
662 and person_id = p_person_id
663 and document_extra_info_id <> p_document_extra_info_id;
664 --
665 begin
666 hr_utility.set_location('Entering:'|| l_proc, 5);
667 --
668 -- Issue a savepoint
669 --
670 savepoint update_doc_extra_info;
671 --
672 -- Call Before Process User Hook
673 --
674 begin
675 hr_document_extra_info_bk2.update_doc_extra_info_b
676 (
677 p_document_extra_info_id => p_document_extra_info_id,
678 p_person_id => p_person_id,
679 p_document_type_id => p_document_type_id,
680 p_date_from => p_date_from,
681 p_date_to => p_date_to,
682 p_document_number => p_document_number,
683 p_issued_by => p_issued_by,
684 p_issued_at => p_issued_at,
685 p_issued_date => p_issued_date,
686 p_issuing_authority => p_issuing_authority,
687 p_verified_by => p_verified_by,
688 p_verified_date => p_verified_date,
689 p_related_object_name => p_related_object_name,
690 p_related_object_id_col => p_related_object_id_col,
691 p_related_object_id => p_related_object_id,
692 p_dei_attribute_category => p_dei_attribute_category,
693 p_dei_attribute1 => p_dei_attribute1,
694 p_dei_attribute2 => p_dei_attribute2,
695 p_dei_attribute3 => p_dei_attribute3,
696 p_dei_attribute4 => p_dei_attribute4,
697 p_dei_attribute5 => p_dei_attribute5,
698 p_dei_attribute6 => p_dei_attribute6,
699 p_dei_attribute7 => p_dei_attribute7,
700 p_dei_attribute8 => p_dei_attribute8,
701 p_dei_attribute9 => p_dei_attribute9,
702 p_dei_attribute10 => p_dei_attribute10,
703 p_dei_attribute11 => p_dei_attribute11,
704 p_dei_attribute12 => p_dei_attribute12,
705 p_dei_attribute13 => p_dei_attribute13,
706 p_dei_attribute14 => p_dei_attribute14,
707 p_dei_attribute15 => p_dei_attribute15,
708 p_dei_attribute16 => p_dei_attribute16,
709 p_dei_attribute17 => p_dei_attribute17,
710 p_dei_attribute18 => p_dei_attribute18,
711 p_dei_attribute19 => p_dei_attribute19,
712 p_dei_attribute20 => p_dei_attribute20,
713 p_dei_attribute21 => p_dei_attribute21,
714 p_dei_attribute22 => p_dei_attribute22,
715 p_dei_attribute23 => p_dei_attribute23,
716 p_dei_attribute24 => p_dei_attribute24,
717 p_dei_attribute25 => p_dei_attribute25,
718 p_dei_attribute26 => p_dei_attribute26,
719 p_dei_attribute27 => p_dei_attribute27,
720 p_dei_attribute28 => p_dei_attribute28,
721 p_dei_attribute29 => p_dei_attribute29,
722 p_dei_attribute30 => p_dei_attribute30,
723 p_dei_information_category => p_dei_information_category,
724 p_dei_information1 => p_dei_information1,
725 p_dei_information2 => p_dei_information2,
726 p_dei_information3 => p_dei_information3,
727 p_dei_information4 => p_dei_information4,
728 p_dei_information5 => p_dei_information5,
729 p_dei_information6 => p_dei_information6,
730 p_dei_information7 => p_dei_information7,
731 p_dei_information8 => p_dei_information8,
732 p_dei_information9 => p_dei_information9,
733 p_dei_information10 => p_dei_information10,
734 p_dei_information11 => p_dei_information11,
735 p_dei_information12 => p_dei_information12,
736 p_dei_information13 => p_dei_information13,
737 p_dei_information14 => p_dei_information14,
738 p_dei_information15 => p_dei_information15,
739 p_dei_information16 => p_dei_information16,
740 p_dei_information17 => p_dei_information17,
741 p_dei_information18 => p_dei_information18,
742 p_dei_information19 => p_dei_information19,
743 p_dei_information20 => p_dei_information20,
744 p_dei_information21 => p_dei_information21,
745 p_dei_information22 => p_dei_information22,
746 p_dei_information23 => p_dei_information23,
747 p_dei_information24 => p_dei_information24,
748 p_dei_information25 => p_dei_information25,
749 p_dei_information26 => p_dei_information26,
750 p_dei_information27 => p_dei_information27,
751 p_dei_information28 => p_dei_information28,
752 p_dei_information29 => p_dei_information29,
753 p_dei_information30 => p_dei_information30,
754 p_request_id => p_request_id,
755 p_program_application_id => p_program_application_id,
756 p_program_id => p_program_id,
757 p_program_update_date => p_program_update_date,
758 p_object_version_number => p_object_version_number
759 );
760 exception
761 when hr_api.cannot_find_prog_unit then
762 hr_api.cannot_find_prog_unit_error
763 (p_module_name => 'UPDATE_DOC_EXTRA_INFO',
764 p_hook_type => 'BP'
765 );
766 end;
767 hr_utility.set_location(l_proc, 7);
768 --
769 open csr_get_document_type(p_document_type_id =>p_document_type_id);
770 fetch csr_get_document_type into l_document_type,l_authorization_required;
771 close csr_get_document_type;
772
773 open csr_chk_multiple(p_document_type_id =>p_document_type_id);
774 fetch csr_chk_multiple into l_mul_flag;
775 if (l_mul_flag = 'N') then
776 open csr_chk_doc_allowed(p_document_type_id =>p_document_type_id,p_person_id=>p_person_id,
777 p_document_extra_info_id=>p_document_extra_info_id);
778 fetch csr_chk_doc_allowed into l_num;
779 if (l_num > 0) then
780 close csr_chk_doc_allowed;
781
782 hr_utility.set_message(800, 'HR_449709_DOR_MUL_NOT_ALW');
783 hr_utility.set_message_token('TYPE', l_document_type);
784 hr_utility.raise_error;
785 else
786 close csr_chk_doc_allowed;
787 hr_utility.set_location(l_proc, 8);
788 end if;
789 close csr_chk_multiple;
790 else
791 close csr_chk_multiple;
792 end if;
793
794
795 --
796 --
797 open csr_chk_combination(p_document_type_id =>p_document_type_id,
798 p_person_id=>p_person_id ,p_date_from =>p_date_from,p_date_to=>p_date_to,
799 p_document_extra_info_id=>p_document_extra_info_id );
800
801 fetch csr_chk_combination into dummy;
802 if csr_chk_combination%found then
803 close csr_chk_combination;
804
805 hr_utility.set_message(800, 'HR_449708_DOR_UNQ_PER_DOC');
806 hr_utility.set_message_token('TYPE', l_document_type);
807 hr_utility.set_message_token('DATE_FROM', p_date_from);
808 hr_utility.set_message_token('DATE_TO', p_date_to);
809 hr_utility.raise_error;
810 --
811 else
812 close csr_chk_combination;
813 hr_utility.set_location(l_proc, 9);
814 end if;
815
816 -- Added for checking Authorization Required, If value = 'N'
817 -- pass Default Values of verified by and verified date to 'ins'
818 if(l_authorization_required = 'N') then
819 l_verified_by := nvl(l_verified_by, fnd_global.USER_ID);
820 l_verified_date := nvl(l_verified_date, sysdate);
821 end if;
822
823
824 --
825
826 --
827 -- Store the original ovn in case we rollback when p_validate is true
828 --
829 l_object_version_number := p_object_version_number;
830 --
831 -- Process Logic - Update Doc Extra Info details
832 --
833 hr_dei_upd.upd
834 (p_document_extra_info_id => p_document_extra_info_id,
835 p_document_type_id => p_document_type_id,
836 p_date_from => p_date_from,
837 p_date_to => p_date_to,
838 p_document_number => p_document_number,
839 p_issued_by => p_issued_by,
840 p_issued_at => p_issued_at,
841 p_issued_date => p_issued_date,
842 p_issuing_authority => p_issuing_authority,
843 p_verified_by => l_verified_by,
844 p_verified_date => l_verified_date,
845 p_related_object_name => p_related_object_name,
846 p_related_object_id_col => p_related_object_id_col,
847 p_related_object_id => p_related_object_id,
848 p_dei_attribute_category => p_dei_attribute_category,
849 p_dei_attribute1 => p_dei_attribute1,
850 p_dei_attribute2 => p_dei_attribute2,
851 p_dei_attribute3 => p_dei_attribute3,
852 p_dei_attribute4 => p_dei_attribute4,
853 p_dei_attribute5 => p_dei_attribute5,
854 p_dei_attribute6 => p_dei_attribute6,
855 p_dei_attribute7 => p_dei_attribute7,
856 p_dei_attribute8 => p_dei_attribute8,
857 p_dei_attribute9 => p_dei_attribute9,
858 p_dei_attribute10 => p_dei_attribute10,
859 p_dei_attribute11 => p_dei_attribute11,
860 p_dei_attribute12 => p_dei_attribute12,
861 p_dei_attribute13 => p_dei_attribute13,
862 p_dei_attribute14 => p_dei_attribute14,
863 p_dei_attribute15 => p_dei_attribute15,
864 p_dei_attribute16 => p_dei_attribute16,
865 p_dei_attribute17 => p_dei_attribute17,
866 p_dei_attribute18 => p_dei_attribute18,
867 p_dei_attribute19 => p_dei_attribute19,
868 p_dei_attribute20 => p_dei_attribute20,
869 p_dei_attribute21 => p_dei_attribute21,
870 p_dei_attribute22 => p_dei_attribute22,
871 p_dei_attribute23 => p_dei_attribute23,
872 p_dei_attribute24 => p_dei_attribute24,
873 p_dei_attribute25 => p_dei_attribute25,
874 p_dei_attribute26 => p_dei_attribute26,
875 p_dei_attribute27 => p_dei_attribute27,
876 p_dei_attribute28 => p_dei_attribute28,
877 p_dei_attribute29 => p_dei_attribute29,
878 p_dei_attribute30 => p_dei_attribute30,
879 p_dei_information_category => p_dei_information_category,
880 p_dei_information1 => p_dei_information1,
881 p_dei_information2 => p_dei_information2,
882 p_dei_information3 => p_dei_information3,
883 p_dei_information4 => p_dei_information4,
884 p_dei_information5 => p_dei_information5,
885 p_dei_information6 => p_dei_information6,
886 p_dei_information7 => p_dei_information7,
887 p_dei_information8 => p_dei_information8,
888 p_dei_information9 => p_dei_information9,
889 p_dei_information10 => p_dei_information10,
890 p_dei_information11 => p_dei_information11,
891 p_dei_information12 => p_dei_information12,
892 p_dei_information13 => p_dei_information13,
893 p_dei_information14 => p_dei_information14,
894 p_dei_information15 => p_dei_information15,
895 p_dei_information16 => p_dei_information16,
896 p_dei_information17 => p_dei_information17,
897 p_dei_information18 => p_dei_information18,
898 p_dei_information19 => p_dei_information19,
899 p_dei_information20 => p_dei_information20,
900 p_dei_information21 => p_dei_information21,
901 p_dei_information22 => p_dei_information22,
902 p_dei_information23 => p_dei_information23,
903 p_dei_information24 => p_dei_information24,
904 p_dei_information25 => p_dei_information25,
905 p_dei_information26 => p_dei_information26,
906 p_dei_information27 => p_dei_information27,
907 p_dei_information28 => p_dei_information28,
908 p_dei_information29 => p_dei_information29,
909 p_dei_information30 => p_dei_information30,
910 p_request_id => p_request_id,
911 p_program_application_id => p_program_application_id,
912 p_program_id => p_program_id,
913 p_program_update_date => p_program_update_date,
914 p_object_version_number => p_object_version_number
915 );
916
917
918 --
919 hr_utility.set_location(l_proc, 8);
920 --
921 -- Call After Process User Hook
922 --
923 begin
924 hr_document_extra_info_bk2.update_doc_extra_info_a
925 (
926 p_document_extra_info_id => p_document_extra_info_id,
927 p_person_id => p_person_id,
928 p_document_type_id => p_document_type_id,
929 p_date_from => p_date_from,
930 p_date_to => p_date_to,
931 p_document_number => p_document_number,
932 p_issued_by => p_issued_by,
933 p_issued_at => p_issued_at,
934 p_issued_date => p_issued_date,
935 p_issuing_authority => p_issuing_authority,
936 p_verified_by => p_verified_by,
937 p_verified_date => p_verified_date,
938 p_related_object_name => p_related_object_name,
939 p_related_object_id_col => p_related_object_id_col,
940 p_related_object_id => p_related_object_id,
941 p_dei_attribute_category => p_dei_attribute_category,
942 p_dei_attribute1 => p_dei_attribute1,
943 p_dei_attribute2 => p_dei_attribute2,
944 p_dei_attribute3 => p_dei_attribute3,
945 p_dei_attribute4 => p_dei_attribute4,
946 p_dei_attribute5 => p_dei_attribute5,
947 p_dei_attribute6 => p_dei_attribute6,
948 p_dei_attribute7 => p_dei_attribute7,
949 p_dei_attribute8 => p_dei_attribute8,
950 p_dei_attribute9 => p_dei_attribute9,
951 p_dei_attribute10 => p_dei_attribute10,
952 p_dei_attribute11 => p_dei_attribute11,
953 p_dei_attribute12 => p_dei_attribute12,
954 p_dei_attribute13 => p_dei_attribute13,
955 p_dei_attribute14 => p_dei_attribute14,
956 p_dei_attribute15 => p_dei_attribute15,
957 p_dei_attribute16 => p_dei_attribute16,
958 p_dei_attribute17 => p_dei_attribute17,
959 p_dei_attribute18 => p_dei_attribute18,
960 p_dei_attribute19 => p_dei_attribute19,
961 p_dei_attribute20 => p_dei_attribute20,
962 p_dei_attribute21 => p_dei_attribute21,
963 p_dei_attribute22 => p_dei_attribute22,
964 p_dei_attribute23 => p_dei_attribute23,
965 p_dei_attribute24 => p_dei_attribute24,
966 p_dei_attribute25 => p_dei_attribute25,
967 p_dei_attribute26 => p_dei_attribute26,
968 p_dei_attribute27 => p_dei_attribute27,
969 p_dei_attribute28 => p_dei_attribute28,
970 p_dei_attribute29 => p_dei_attribute29,
971 p_dei_attribute30 => p_dei_attribute30,
972 p_dei_information_category => p_dei_information_category,
973 p_dei_information1 => p_dei_information1,
974 p_dei_information2 => p_dei_information2,
975 p_dei_information3 => p_dei_information3,
976 p_dei_information4 => p_dei_information4,
977 p_dei_information5 => p_dei_information5,
978 p_dei_information6 => p_dei_information6,
979 p_dei_information7 => p_dei_information7,
980 p_dei_information8 => p_dei_information8,
981 p_dei_information9 => p_dei_information9,
982 p_dei_information10 => p_dei_information10,
983 p_dei_information11 => p_dei_information11,
984 p_dei_information12 => p_dei_information12,
985 p_dei_information13 => p_dei_information13,
986 p_dei_information14 => p_dei_information14,
987 p_dei_information15 => p_dei_information15,
988 p_dei_information16 => p_dei_information16,
989 p_dei_information17 => p_dei_information17,
990 p_dei_information18 => p_dei_information18,
991 p_dei_information19 => p_dei_information19,
992 p_dei_information20 => p_dei_information20,
993 p_dei_information21 => p_dei_information21,
994 p_dei_information22 => p_dei_information22,
995 p_dei_information23 => p_dei_information23,
996 p_dei_information24 => p_dei_information24,
997 p_dei_information25 => p_dei_information25,
998 p_dei_information26 => p_dei_information26,
999 p_dei_information27 => p_dei_information27,
1000 p_dei_information28 => p_dei_information28,
1001 p_dei_information29 => p_dei_information29,
1002 p_dei_information30 => p_dei_information30,
1003 p_request_id => p_request_id,
1004 p_program_application_id => p_program_application_id,
1005 p_program_id => p_program_id,
1006 p_program_update_date => p_program_update_date,
1007 p_object_version_number => p_object_version_number
1008 );
1009 exception
1010 when hr_api.cannot_find_prog_unit then
1011 hr_api.cannot_find_prog_unit_error
1012 (p_module_name => 'UPDATE_DOC_EXTRA_INFO',
1013 p_hook_type => 'AP'
1014 );
1015 end;
1016 --
1017 -- End of After Process User Hook call
1018 --
1019 -- When in validation only mode raise the Validate_Enabled exception
1020 --
1021 if p_validate then
1022 raise hr_api.validate_enabled;
1023 end if;
1024 --
1025 hr_utility.set_location(' Leaving:'||l_proc, 11);
1026 exception
1027 when hr_api.validate_enabled then
1028 --
1029 -- As the Validate_Enabled exception has been raised
1030 -- we must rollback to the savepoint
1031 --
1032 ROLLBACK TO update_doc_extra_info;
1033 --
1034 -- Only set output warning arguments
1035 -- (Any key or derived arguments must be set to null
1036 -- when validation only mode is being used.)
1037 --
1038 p_object_version_number := l_object_version_number;
1039 --
1040 hr_utility.set_location(' Leaving:'||l_proc, 12);
1041 --
1042 when others then
1043 --
1044 -- A validation or unexpected error has occurred
1045 --
1046 --
1047 ROLLBACK TO update_doc_extra_info;
1048 --
1049 -- set in out parameters and set out parameters
1050 --
1051 p_object_version_number := l_ovn;
1052 --
1053 raise;
1054 --
1055 end update_doc_extra_info;
1056 --
1057 --
1058 -- ----------------------------------------------------------------------------
1059 -- |-----------------------< delete_person_extra_info >------------------------|
1060 -- ----------------------------------------------------------------------------
1061 --
1062 procedure delete_doc_extra_info
1063 (p_validate in boolean default false
1064 ,p_document_extra_info_id in number
1065 ,p_object_version_number in number
1066 ) is
1067 --
1068 -- Declare cursors and local variables
1069 --
1070 l_proc varchar2(72) := g_package||'delete_doc_extra_info';
1071 --
1072 begin
1073 hr_utility.set_location('Entering:'|| l_proc, 5);
1074 --
1075 -- Issue a savepoint
1076 --
1077 savepoint delete_doc_extra_info;
1078 --
1079 -- Call Before Process User Hook
1080 --
1081 begin
1082 hr_document_extra_info_bk3.delete_doc_extra_info_b
1083 (p_document_extra_info_id => p_document_extra_info_id,
1084 p_object_version_number => p_object_version_number
1085 );
1086 exception
1087 when hr_api.cannot_find_prog_unit then
1088 hr_api.cannot_find_prog_unit_error
1089 (p_module_name => 'DELETE_DOC_EXTRA_INFO',
1090 p_hook_type => 'BP'
1091 );
1092 end;
1093 --
1094 -- End of Before Process User Hook call
1095 --
1096 hr_utility.set_location(l_proc, 7);
1097 --
1098 -- Process Logic - Delete Document Extra Info details
1099 --
1100 hr_dei_del.del
1101 (p_document_extra_info_id => p_document_extra_info_id
1102 ,p_object_version_number => p_object_version_number
1103 );
1104 --
1105 hr_utility.set_location(l_proc, 8);
1106 --
1107 -- Call Before Process User Hook
1108 --
1109 begin
1110 hr_document_extra_info_bk3.delete_doc_extra_info_a
1111 (p_document_extra_info_id => p_document_extra_info_id,
1112 p_object_version_number => p_object_version_number
1113 );
1114 exception
1115 when hr_api.cannot_find_prog_unit then
1116 hr_api.cannot_find_prog_unit_error
1117 (p_module_name => 'DELETE_DOC_EXTRA_INFO',
1118 p_hook_type => 'AP'
1119 );
1120 end;
1121 --
1122 -- End of After Process User Hook call
1123 --
1124 -- When in validation only mode raise the Validate_Enabled exception
1125 --
1126 if p_validate then
1127 raise hr_api.validate_enabled;
1128 end if;
1129 --
1130 hr_utility.set_location(' Leaving:'||l_proc, 11);
1131 exception
1132 when hr_api.validate_enabled then
1133 --
1134 -- As the Validate_Enabled exception has been raised
1135 -- we must rollback to the savepoint
1136 --
1137 ROLLBACK TO delete_doc_extra_info;
1138 --
1139 hr_utility.set_location(' Leaving:'||l_proc, 12);
1140 --
1141 when others then
1142 --
1143 -- A validation or unexpected error has occurred
1144 --
1145 --
1146 ROLLBACK TO delete_doc_extra_info;
1147 --
1148 raise;
1149 --
1150 end delete_doc_extra_info;
1151 --
1152
1153 --
1154 --
1155 -- ----------------------------------------------------------------------------
1156 -- |----------------------------< set_reviewer >------------------------------|
1157 -- ----------------------------------------------------------------------------
1158 --
1159 procedure set_reviewer (itemtype in varchar2,
1160 itemkey in varchar2,
1161 actid in number,
1162 funcmode in varchar2,
1163 resultout out nocopy varchar2)
1164 is
1165 --
1166 cursor c_reviewer (p_person_id varchar2, p_eff_date date) is
1167 select asg.supervisor_id
1168 from per_all_assignments_f asg
1169 where asg.person_id = p_person_id
1170 and p_eff_date between asg.effective_start_date and asg.effective_end_date
1171 and asg.primary_flag = 'Y'
1172 and asg.assignment_type in ('E','C');
1173 --
1174 l_person_id number;
1175 l_eff_date date;
1176 l_supervisor_id number;
1177 l_reviewer_role wf_roles.name%type;
1178 l_display_name wf_roles.display_name%type;
1179 --
1180 begin
1181 --
1182 if ( funcmode = 'RUN' ) then
1183 l_person_id := wf_engine.GetItemAttrText(itemtype => itemtype,
1184 itemkey => itemkey,
1185 aname => 'DOR_PERSON_ID');
1186 l_eff_date := wf_engine.GetItemAttrDate(itemtype => itemtype,
1187 itemkey => itemkey,
1188 aname => 'EFFECTIVE_DATE');
1189 open c_reviewer(l_person_id, l_eff_date);
1190 fetch c_reviewer into l_supervisor_id;
1191 close c_reviewer;
1192 --
1193 if (l_supervisor_id is not null) then
1194 wf_directory.GetRoleName(p_orig_system => 'PER',
1195 p_orig_system_id => l_supervisor_id,
1196 p_name => l_reviewer_role,
1197 p_display_name => l_display_name);
1198 end if;
1199 --
1200 if (l_reviewer_role is not null) then
1201 WF_ENGINE.setItemAttrText(itemtype, itemkey,'REVIEWER', l_reviewer_role);
1202 resultout := 'COMPLETE:' || 'Y';
1203 else
1204 resultout := 'COMPLETE:' || 'N';
1205 end if;
1206
1207 else
1208 resultout := 'ERROR' || 'Y';
1209 end if;
1210 --
1211 exception
1212 when others then
1213 WF_CORE.CONTEXT('HR_DOCUMENT_EXTRA_INFO_API'
1214 ,'SET_REVIEWER'
1215 ,itemtype
1216 ,itemkey
1217 ,to_char(actid)
1218 ,funcmode);
1219 raise;
1220 end set_reviewer;
1221
1222
1223 --
1224 --
1225 -- ----------------------------------------------------------------------------
1226 -- |----------------------------< set_reviewee >------------------------------|
1227 -- ----------------------------------------------------------------------------
1228 --
1229 procedure set_reviewee (itemtype in varchar2,
1230 itemkey in varchar2,
1231 actid in number,
1232 funcmode in varchar2,
1233 resultout out nocopy varchar2)
1234 is
1235
1236 l_reviewee_role wf_roles.name%type;
1237 --
1238 begin
1239 --
1240 if ( funcmode = 'RUN' ) then
1241 l_reviewee_role := wf_engine.GetItemAttrText(itemtype => itemtype,
1242 itemkey => itemkey,
1243 aname => 'REVIEWEE');
1244 if (l_reviewee_role is not null) then
1245 resultout := 'COMPLETE:' || 'Y';
1246 else
1247 resultout := 'COMPLETE:' || 'N';
1248 end if;
1249
1250 else
1251 resultout := 'ERROR' || 'Y';
1252 end if;
1253 --
1254 exception
1255 when others then
1256 WF_CORE.CONTEXT('HR_DOCUMENT_EXTRA_INFO_API'
1257 ,'SET_REVIEWEE'
1258 ,itemtype
1259 ,itemkey
1260 ,to_char(actid)
1261 ,funcmode);
1262 raise;
1263 end set_reviewee;
1264
1265 --
1266 --
1267 -- ----------------------------------------------------------------------------
1268 -- |--------------------< get_view_pg_wf_notif_params >----------------------|
1269 -- ----------------------------------------------------------------------------
1270 --
1271 procedure get_view_pg_wf_notif_params (p_notification_id in number,
1272 p_dor_id out nocopy varchar2,
1273 p_person_id out nocopy varchar2,
1274 p_effective_date out nocopy date)
1275 is
1276 --
1277 cursor c_ntf_attr (p_ntf_id number)
1278 is
1279 select name, text_value, date_value
1280 from wf_notification_attributes
1281 where name IN ('DOR_ID', 'DOR_PERSON_ID', 'EFFECTIVE_DATE')
1282 and notification_id = p_ntf_id;
1283
1284 --
1285 begin
1286 --
1287 FOR ntf_attr_rec in c_ntf_attr(p_notification_id)
1288 LOOP
1289 if (ntf_attr_rec.name = 'DOR_ID' )
1290 then
1291 p_dor_id := ntf_attr_rec.text_value;
1292 --
1293 elsif (ntf_attr_rec.name = 'DOR_PERSON_ID')
1294 then
1295 p_person_id := ntf_attr_rec.text_value;
1296 --
1297 elsif (ntf_attr_rec.name = 'EFFECTIVE_DATE')
1298 then
1299 p_effective_date := ntf_attr_rec.date_value;
1300 end if;
1301 END LOOP;
1302 --
1303 end get_view_pg_wf_notif_params;
1304
1305 end hr_document_extra_info_api;