[Home] [Help]
PACKAGE BODY: APPS.PQH_CORPS_EXTRA_INFO_API
Source
1 Package body pqh_corps_extra_info_api as
2 /* $Header: pqceiapi.pkb 115.8 2004/01/02 01:35:54 svorugan noship $ */
3 g_package varchar2(33) := 'pqh_corps_extra_info_api.';
4 ------------------------------------------------------------------------------
5 -- |------------------------< validate_corps_org_info >------------------------|
6 -- ----------------------------------------------------------------------------
7 PROCEDURE validate_corps_org_info(p_effective_date IN DATE
8 ,p_corps_definition_id IN NUMBER
9 ,p_organization_id IN VARCHAR2
10 ,p_corps_extra_info_id IN NUMBER DEFAULT NULL) IS
11 CURSOR csr_chk_valid_org IS
12 SELECT 'Y'
13 FROM DUAL
14 WHERE EXISTS (SELECT 1
15 FROM hr_all_organization_units
16 WHERE organization_id = fnd_number.canonical_to_number(p_organization_id)
17 AND p_effective_date BETWEEN date_from AND NVL(date_to,hr_general.end_of_time));
18 l_valid_org varchar2(10) := 'N';
19 CURSOR csr_dup_org_chk IS
20 SELECT 'Y'
21 FROM DUAL
22 WHERE EXISTS (SELECT 1
23 FROM pqh_corps_extra_info
24 WHERE corps_definition_id = p_corps_definition_id
25 AND information_type = 'ORGANIZATION'
26 AND information3 = p_organization_id
27 AND (p_corps_extra_info_id IS NULL OR
28 corps_extra_info_id <> p_corps_extra_info_id) );
29 l_dup_org varchar2(10) := 'N';
30 l_proc varchar2(72) := g_package||'.validate_corps_org_info';
31 BEGIN
32 hr_utility.set_location('Entering '||l_proc,10);
33 IF p_organization_id IS NOT NULL THEN
34 OPEN csr_chk_valid_org;
35 FETCH csr_chk_valid_org INTO l_valid_org;
36 CLOSE csr_chk_valid_org;
37 IF l_valid_org = 'N' THEN
38 hr_utility.set_message(8302,'PQH_FR_INVALID_ORG_FOR_CORPS');
39 hr_multi_message.add(p_associated_column1 => 'INFORMATION3');
40 END IF;
41 OPEN csr_dup_org_chk;
42 FETCH csr_dup_org_chk INTO l_dup_org;
43 CLOSE csr_dup_org_chk;
44 IF l_dup_org = 'Y' THEN
45 hr_utility.set_message(8302,'PQH_FR_DUP_ORG_FOR_CORPS');
46 hr_multi_message.add(p_associated_column1 => 'INFORMATION3');
47 END IF;
48 END IF;
49 hr_utility.set_location('Leaving '||l_proc,20);
50 END validate_corps_org_info;
51 ------------------------------------------------------------------------------
52 -- |------------------------<validate_corps_prof_field_info>------------------------|
53 -- ----------------------------------------------------------------------------
54 PROCEDURE validate_corps_prof_field_info(p_effective_date IN DATE
55 ,p_corps_definition_id IN NUMBER
56 ,p_field_of_prof_activity_id IN VARCHAR2
57 ,p_corps_extra_info_id IN NUMBER DEFAULT NULL) IS
58 CURSOR csr_chk_valid_prof_field IS
59 SELECT 'Y'
60 FROM dual
61 WHERE EXISTS (SELECT 1
62 FROM per_shared_types
63 WHERE shared_type_id = fnd_number.canonical_to_number(p_field_of_prof_activity_id));
64 l_valid_prof_field varchar2(10) := 'N';
65 l_proc varchar2(72) := g_package||'.validate_corps_prof_field_info';
66 CURSOR csr_dup_prof_field_chk IS
67 SELECT 'Y'
68 FROM dual
69 WHERE EXISTS (SELECT 1
70 FROM pqh_corps_extra_info
71 WHERE corps_definition_id = p_corps_definition_id
72 AND information_type = 'FILERE'
73 AND information3 = p_field_of_prof_activity_id
74 AND (p_corps_extra_info_id IS NULL OR corps_extra_info_id <> p_corps_extra_info_id));
75 l_dup_prof_field varchar2(10) := 'N';
76 BEGIN
77 hr_utility.set_location('Entering '||l_proc,10);
78 IF p_field_of_prof_activity_id IS NOT NULL THEN
79 OPEN csr_chk_valid_prof_field;
80 FETCH csr_chk_valid_prof_field INTO l_valid_prof_field;
81 CLOSE csr_chk_valid_prof_field;
82 IF l_valid_prof_field = 'N' THEN
83 hr_utility.set_message(8302,'PQH_FR_INVALID_FIELD_OF_PROF');
84 hr_multi_message.add(p_associated_column1 => 'INFORMATION3');
85 END IF;
86 OPEN csr_dup_prof_field_chk;
87 FETCH csr_dup_prof_field_chk INTO l_dup_prof_field;
88 CLOSE csr_dup_prof_field_chk;
89 IF l_dup_prof_field = 'Y' THEN
90 hr_utility.set_message(8302,'PQH_FR_DUP_FIELD_OF_PROF');
91 hr_multi_message.add(p_associated_column1 => 'INFORMATION3');
92 END IF;
93 END IF;
94 hr_utility.set_location('Leaving '||l_proc,20);
95 END validate_corps_prof_field_info;
96 ------------------------------------------------------------------------------
97 -- |------------------------<validate_corps_exam_info>------------------------|
98 -- ----------------------------------------------------------------------------
99 PROCEDURE validate_corps_exam_info(p_effective_date IN DATE
100 ,p_corps_definition_id IN NUMBER
101 ,p_examination_type_cd IN VARCHAR2
102 ,p_institution_id IN VARCHAR2
103 ,p_joining_way_cd IN VARCHAR2
104 ,p_mandatory_flag IN VARCHAR2
105 ,p_exam_name IN VARCHAR2
106 ,p_corps_extra_info_id IN NUMBER DEFAULT NULL) IS
107 CURSOR csr_valid_institution IS
108 SELECT 'Y'
109 FROM dual
110 WHERE EXISTS (SELECT 1
111 FROM hr_all_organization_units
112 WHERE organization_id = fnd_number.canonical_to_number(p_institution_id)
113 AND p_effective_date BETWEEN date_from AND nvl(date_to,hr_general.end_of_time));
114 l_valid_institution varchar2(10) :='N';
115 CURSOR csr_chk_dup_exam IS
116 SELECT 'Y'
117 FROM dual
118 WHERE EXISTS (SELECT 1
119 FROM pqh_corps_extra_info
120 WHERE information_type = 'EXAM'
121 AND corps_definition_id = p_corps_definition_id
122 AND nvl(information3,'#@') = nvl(p_examination_type_cd,'#@')
123 AND nvl(information4,'#@') = nvl(p_institution_id,'#@')
124 AND nvl(information5,'#@') = nvl(p_joining_way_cd,'#@')
125 AND nvl(information7,'#@') = nvl(p_exam_name,'#@')
126 AND (p_corps_extra_info_id IS NULL OR corps_extra_info_id <> p_corps_extra_info_id));
127 l_dup_exam varchar2(10) := 'N';
128 l_proc varchar2(72) := g_package||'.validate_corps_exam_info';
129 BEGIN
130 hr_utility.set_location('Entering '||l_proc,10);
131 IF p_examination_type_cd IS NOT NULL THEN
132 IF hr_api.not_exists_in_hr_lookups(p_effective_date,'PQH_CORPS_EXAM_TYPE',p_examination_type_cd) THEN
133 hr_utility.set_message(8302,'PQH_FR_INVALID_EXAM_TYPE');
134 hr_multi_message.add(p_associated_column1 => 'INFORMATION3');
135 END IF;
136 END IF;
137 IF p_institution_id IS NOT NULL THEN
138 OPEN csr_valid_institution;
139 FETCH csr_valid_institution INTO l_valid_institution;
140 CLOSE csr_valid_institution;
141 IF l_valid_institution = 'N' THEN
142 hr_utility.set_message(8302,'PQH_FR_INVALID_INSTITUTION');
143 hr_multi_message.add(p_associated_column1 => 'INFORMATION4');
144 END IF;
145 END IF;
146 IF p_joining_way_cd IS NOT NULL THEN
147 IF hr_api.not_exists_in_hr_lookups(p_effective_date,'PQH_CORPS_WAYS',p_joining_way_cd) THEN
148 hr_utility.set_message(8302,'PQH_FR_INVALID_WAY_FOR_CORPS');
149 hr_multi_message.add(p_associated_column1 => 'INFORMATION5');
150 END IF;
151 END IF;
152 hr_multi_message.end_validation_set;
153 OPEN csr_chk_dup_exam;
154 FETCH csr_chk_dup_exam INTO l_dup_exam;
155 CLOSE csr_chk_dup_exam;
156 IF l_dup_exam = 'Y' THEN
157 hr_utility.set_message(8302,'PQH_FR_DUP_EXAM_FOR_CORPS');
158 hr_multi_message.add;
159 END IF;
160 hr_utility.set_location('Leaving '||l_proc,20);
161 END validate_corps_exam_info;
162
163 ------------------------------------------------------------------------------
164 -- |------------------------<validate_corps_training_info>------------------------|
165 -- ----------------------------------------------------------------------------
166 PROCEDURE validate_corps_training_info(p_effective_date IN DATE
167 ,p_corps_definition_id IN NUMBER
168 ,p_qualification_id IN VARCHAR2
169 ,p_institution_id IN VARCHAR2
170 ,p_training_duration IN VARCHAR2
171 ,p_training_duration_uom IN VARCHAR2
172 ,p_joining_way_cd IN VARCHAR2
173 ,p_mandatory_flag IN VARCHAR2
174 ,p_corps_extra_info_id IN NUMBER DEFAULT NULL) IS
175 CURSOR csr_valid_qualification IS
176 SELECT 'Y'
177 FROM dual
178 WHERE EXISTS (SELECT 1
179 FROM per_qualification_types
180 WHERE qualification_type_id = fnd_number.canonical_to_number(p_qualification_id) );
181 l_valid_qualification varchar2(10) := 'N';
182 CURSOR csr_valid_institution IS
183 SELECT 'Y'
184 FROM dual
185 WHERE EXISTS (SELECT 1
186 FROM hr_all_organization_units
187 WHERE organization_id = fnd_number.canonical_to_number(p_institution_id)
188 AND p_effective_date BETWEEN date_from AND nvl(date_to,hr_general.end_of_time));
189 l_valid_institution varchar2(10) :='N';
190 CURSOR csr_dup_train_dtls IS
191 SELECT 'Y'
192 FROM dual
193 WHERE EXISTS (SELECT 1
194 FROM pqh_corps_extra_info
195 WHERE corps_definition_id = p_corps_definition_id
196 AND information_type = 'TRAINING'
197 AND nvl(information3,'#@') = nvl(p_qualification_id,'#@')
198 AND nvl(information4,'#@') = nvl(p_institution_id,'#@')
199 AND nvl(information5,'#@') = nvl(p_training_duration,'#@')
200 AND nvl(information6,'#@') = nvl(p_training_duration_uom,'#@')
201 AND nvl(information7,'#@') = nvl(p_joining_way_cd,'#@')
202 AND (p_corps_extra_info_id IS NULL OR
203 corps_extra_info_id <> p_corps_extra_info_id) );
204 l_dup_train_dtls varchar2(10) := 'N';
205 l_proc varchar2(72) := g_package||'.validate_corps_training_info';
206 BEGIN
207 hr_utility.set_location('Entering '||l_proc,10);
208 IF p_qualification_id IS NOT NULL THEN
209 OPEN csr_valid_qualification;
210 FETCH csr_valid_qualification INTO l_valid_qualification;
211 CLOSE csr_valid_qualification;
212 IF l_valid_qualification = 'N' THEN
213 hr_utility.set_message(8302,'PQH_FR_INVALID_QUALIFICATION');
214 hr_multi_message.add(p_associated_column1 => 'INFORMATION3');
215 END IF;
216 END IF;
217 IF p_institution_id IS NOT NULL THEN
218 OPEN csr_valid_institution;
219 FETCH csr_valid_institution INTO l_valid_institution;
220 CLOSE csr_valid_institution;
221 IF l_valid_institution = 'N' THEN
222 hr_utility.set_message(8302,'PQH_FR_INVALID_INSTITUTION');
223 hr_multi_message.add(p_associated_column1 => 'INFORMATION4');
224 END IF;
225 END IF;
226 -- Added validation for training duration ( +ve value )
227 IF p_training_duration IS NOT NULL THEN
228 -- Added = for bug fix 3344339
229 if (p_training_duration <= 0) then
230 --
231 hr_utility.set_message(8302,'PQH_FR_INVALID_DURATION');
232 hr_multi_message.add(p_associated_column1 => 'INFORMATION5');
233 --
234 end if;
235 --
236 End if;
237
238 IF p_training_duration_uom IS NOT NULL THEN
239 IF hr_api.not_exists_in_hr_lookups(p_effective_date,'FREQUENCY',p_training_duration_uom) THEN
240 hr_utility.set_message(8302,'PQH_FR_INVALID_DURATION_UNITS');
241 hr_multi_message.add(p_associated_column1 => 'INFORMATION6');
242 END IF;
243 END IF;
244 IF p_joining_way_cd IS NOT NULL THEN
245 IF hr_api.not_exists_in_hr_lookups(p_effective_date,'PQH_CORPS_WAYS',p_joining_way_cd) THEN
246 hr_utility.set_message(8302,'PQH_FR_INVALID_WAY_FOR_CORPS');
247 hr_multi_message.add(p_associated_column1 => 'INFORMATION7');
248 END IF;
249 END IF;
250 hr_multi_message.end_validation_set;
251 OPEN csr_dup_train_dtls ;
252 FETCH csr_dup_train_dtls INTO l_dup_train_dtls ;
253 CLOSE csr_dup_train_dtls ;
254 IF l_dup_train_dtls = 'Y' THEN
255 hr_utility.set_message(8302,'PQH_FR_DUP_TRAINING_FOR_CORPS');
256 hr_multi_message.add;
257 END IF;
258 hr_utility.set_location('Leaving '||l_proc,20);
259 END validate_corps_training_info;
260
261
262 ------------------------------------------------------------------------------
263 -- |------------------------<chk_prim_field_for_corps>------------------------|
264 -- ----------------------------------------------------------------------------
265 PROCEDURE chk_prim_field_for_corps(p_corps_extra_info_id IN NUMBER) IS
266 CURSOR csr_prim_field_for_corps IS
267 SELECT 'Y'
268 FROM dual
269 WHERE EXISTS (SELECT 1
270 FROM pqh_corps_definitions cpd,
271 pqh_corps_extra_info cei
272 WHERE cei.information_type = 'FILERE'
273 AND cei.corps_definition_id = cpd.corps_definition_id
274 AND fnd_number.canonical_to_number(nvl(cei.information4,'-1')) = nvl(primary_prof_field_id,-1)
275 AND corps_extra_info_id = p_corps_extra_info_id);
276 l_primary_flag varchar2(10) := 'N';
277 l_proc varchar2(72) := g_package||'.chk_prim_field_for_corps';
278 BEGIN
279 hr_utility.set_location('Entering '||l_proc,10);
280 OPEN csr_prim_field_for_corps;
281 FETCH csr_prim_field_for_corps INTO l_primary_flag;
282 CLOSE csr_prim_field_for_corps;
283 IF l_primary_flag = 'Y' THEN
284 hr_utility.set_message(8302,'PQH_FR_CANNOT_DEL_PRIM_FIELD');
285 hr_multi_message.add;
286 END IF;
287 hr_utility.set_location('Leaving '||l_proc,20);
288 END chk_prim_field_for_corps;
289
290 --
291 -- ----------------------------------------------------------------------------
292 -- |------------------------< create_corps_extra_info >------------------------|
293 -- ----------------------------------------------------------------------------
294 -- {Start Of Comments}
295 --
296 -- Description:
297 --
298 -- Prerequisites:
299 --
300 --
301 -- In Parameters:
302 -- Name Reqd Type Description
303 -- p_validate YES boolean Commit or Rollback
304 -- p_effective_date YES date
305 -- p_corps_definition_id NO number
306 -- p_information_type NO varchar2
307 -- p_information1 NO varchar2
308 -- p_information2 NO varchar2
309 -- p_information3 NO varchar2
310 -- p_information4 NO varchar2
311 -- p_information5 NO varchar2
312 -- p_information6 NO varchar2
313 -- p_information7 NO varchar2
314 -- p_information8 NO varchar2
315 -- p_information9 NO varchar2
316 -- p_information10 NO varchar2
317 -- p_information11 NO varchar2
318 -- p_information12 NO varchar2
319 -- p_information13 NO varchar2
320 -- p_information14 NO varchar2
321 -- p_information15 NO varchar2
322 -- p_information16 NO varchar2
323 -- p_information17 NO varchar2
324 -- p_information18 NO varchar2
325 -- p_information19 NO varchar2
326 -- p_information20 NO varchar2
327 -- p_information21 NO varchar2
328 -- p_information22 NO varchar2
329 -- p_information23 NO varchar2
330 -- p_information24 NO varchar2
331 -- p_information25 NO varchar2
332 -- p_information26 NO varchar2
333 -- p_information27 NO varchar2
334 -- p_information28 NO varchar2
335 -- p_information29 NO varchar2
336 -- p_information30 NO varchar2
337 -- p_information_category NO varchar2
338 -- p_attribute1 NO varchar2
339 -- p_attribute2 NO varchar2
340 -- p_attribute3 NO varchar2
341 -- p_attribute4 NO varchar2
342 -- p_attribute5 NO varchar2
343 -- p_attribute6 NO varchar2
344 -- p_attribute7 NO varchar2
345 -- p_attribute8 NO varchar2
346 -- p_attribute9 NO varchar2
347 -- p_attribute10 NO varchar2
348 -- p_attribute11 NO varchar2
349 -- p_attribute12 NO varchar2
350 -- p_attribute13 NO varchar2
351 -- p_attribute14 NO varchar2
352 -- p_attribute15 NO varchar2
353 -- p_attribute16 NO varchar2
354 -- p_attribute17 NO varchar2
355 -- p_attribute18 NO varchar2
356 -- p_attribute19 NO varchar2
357 -- p_attribute20 NO varchar2
358 -- p_attribute21 NO varchar2
359 -- p_attribute22 NO varchar2
360 -- p_attribute23 NO varchar2
361 -- p_attribute24 NO varchar2
362 -- p_attribute25 NO varchar2
363 -- p_attribute26 NO varchar2
364 -- p_attribute27 NO varchar2
365 -- p_attribute28 NO varchar2
366 -- p_attribute29 NO varchar2
367 -- p_attribute30 NO varchar2
368 -- p_attribute_category NO varchar2
369
370 -- Post Success:
371 --
372 -- Out Parameters:
373 -- Name Reqd Type Description
374 -- p_object_version_number Yes number OVN of record
375 -- p_corps_extra_info_id Yes number
376 --
377 -- Post Failure:
378 --
379 -- Access Status:
380 -- Public.
381 --
382 -- {End Of Comments}
383 --
384 procedure create_corps_extra_info
385 (
386 p_validate in boolean default false
387 ,p_effective_date in date
388 ,p_corps_extra_info_id out nocopy number
389 ,p_corps_definition_id in number
390 ,p_information_type in varchar2
391 ,p_information1 in varchar2 default null
392 ,p_information2 in varchar2 default null
393 ,p_information3 in varchar2 default null
394 ,p_information4 in varchar2 default null
395 ,p_information5 in varchar2 default null
396 ,p_information6 in varchar2 default null
397 ,p_information7 in varchar2 default null
398 ,p_information8 in varchar2 default null
399 ,p_information9 in varchar2 default null
400 ,p_information10 in varchar2 default null
401 ,p_information11 in varchar2 default null
402 ,p_information12 in varchar2 default null
403 ,p_information13 in varchar2 default null
404 ,p_information14 in varchar2 default null
405 ,p_information15 in varchar2 default null
406 ,p_information16 in varchar2 default null
407 ,p_information17 in varchar2 default null
408 ,p_information18 in varchar2 default null
409 ,p_information19 in varchar2 default null
410 ,p_information20 in varchar2 default null
411 ,p_information21 in varchar2 default null
412 ,p_information22 in varchar2 default null
413 ,p_information23 in varchar2 default null
414 ,p_information24 in varchar2 default null
415 ,p_information25 in varchar2 default null
416 ,p_information26 in varchar2 default null
417 ,p_information27 in varchar2 default null
418 ,p_information28 in varchar2 default null
419 ,p_information29 in varchar2 default null
420 ,p_information30 in varchar2 default null
421 ,p_information_category in varchar2 default null
422 ,p_attribute1 in varchar2 default null
423 ,p_attribute2 in varchar2 default null
424 ,p_attribute3 in varchar2 default null
425 ,p_attribute4 in varchar2 default null
426 ,p_attribute5 in varchar2 default null
427 ,p_attribute6 in varchar2 default null
428 ,p_attribute7 in varchar2 default null
429 ,p_attribute8 in varchar2 default null
430 ,p_attribute9 in varchar2 default null
431 ,p_attribute10 in varchar2 default null
432 ,p_attribute11 in varchar2 default null
433 ,p_attribute12 in varchar2 default null
434 ,p_attribute13 in varchar2 default null
435 ,p_attribute14 in varchar2 default null
436 ,p_attribute15 in varchar2 default null
437 ,p_attribute16 in varchar2 default null
438 ,p_attribute17 in varchar2 default null
439 ,p_attribute18 in varchar2 default null
440 ,p_attribute19 in varchar2 default null
441 ,p_attribute20 in varchar2 default null
442 ,p_attribute21 in varchar2 default null
443 ,p_attribute22 in varchar2 default null
444 ,p_attribute23 in varchar2 default null
445 ,p_attribute24 in varchar2 default null
446 ,p_attribute25 in varchar2 default null
447 ,p_attribute26 in varchar2 default null
448 ,p_attribute27 in varchar2 default null
449 ,p_attribute28 in varchar2 default null
450 ,p_attribute29 in varchar2 default null
451 ,p_attribute30 in varchar2 default null
452 ,p_attribute_category in varchar2 default null
453 ,p_object_version_number out nocopy number
454 ) is
455 --
456 -- Declare cursors and local variables
457 --
458 l_corps_extra_info_id pqh_corps_extra_info.corps_extra_info_id%TYPE;
459 l_proc varchar2(72) := g_package||'create_corps_extra_info';
460 l_object_version_number pqh_corps_extra_info.object_version_number%TYPE;
461 --
462 begin
463 --
464 hr_utility.set_location('Entering:'|| l_proc, 10);
465 --
466 -- Issue a savepoint if operating in validation only mode
467 --
468 savepoint create_corps_extra_info;
469 --
470 hr_utility.set_location(l_proc, 20);
471 --
472 -- Process Logic
473 --
474 begin
475 --
476 -- Start of API User Hook for the before hook of create_corps_extra_info
477 pqh_corps_extra_info_bk1.create_corps_extra_info_b
478 (
479 p_effective_date => trunc(p_effective_date)
480 ,p_corps_definition_id => p_corps_definition_id
481 ,p_information_type => p_information_type
482 ,p_information1 => p_information1
483 ,p_information2 => p_information2
484 ,p_information3 => p_information3
485 ,p_information4 => p_information4
486 ,p_information5 => p_information5
487 ,p_information6 => p_information6
488 ,p_information7 => p_information7
489 ,p_information8 => p_information8
490 ,p_information9 => p_information9
491 ,p_information10 => p_information10
492 ,p_information11 => p_information11
493 ,p_information12 => p_information12
494 ,p_information13 => p_information13
495 ,p_information14 => p_information14
496 ,p_information15 => p_information15
497 ,p_information16 => p_information16
498 ,p_information17 => p_information17
499 ,p_information18 => p_information18
500 ,p_information19 => p_information19
501 ,p_information20 => p_information20
502 ,p_information21 => p_information21
503 ,p_information22 => p_information22
504 ,p_information23 => p_information23
505 ,p_information24 => p_information24
506 ,p_information25 => p_information25
507 ,p_information26 => p_information26
508 ,p_information27 => p_information27
509 ,p_information28 => p_information28
510 ,p_information29 => p_information29
511 ,p_information30 => p_information30
512 ,p_information_category => p_information_category
513 ,p_attribute1 => p_attribute1
514 ,p_attribute2 => p_attribute2
515 ,p_attribute3 => p_attribute3
516 ,p_attribute4 => p_attribute4
517 ,p_attribute5 => p_attribute5
518 ,p_attribute6 => p_attribute6
519 ,p_attribute7 => p_attribute7
520 ,p_attribute8 => p_attribute8
521 ,p_attribute9 => p_attribute9
522 ,p_attribute10 => p_attribute10
523 ,p_attribute11 => p_attribute11
524 ,p_attribute12 => p_attribute12
525 ,p_attribute13 => p_attribute13
526 ,p_attribute14 => p_attribute14
527 ,p_attribute15 => p_attribute15
528 ,p_attribute16 => p_attribute16
529 ,p_attribute17 => p_attribute17
530 ,p_attribute18 => p_attribute18
531 ,p_attribute19 => p_attribute19
532 ,p_attribute20 => p_attribute20
533 ,p_attribute21 => p_attribute21
534 ,p_attribute22 => p_attribute22
535 ,p_attribute23 => p_attribute23
536 ,p_attribute24 => p_attribute24
537 ,p_attribute25 => p_attribute25
538 ,p_attribute26 => p_attribute26
539 ,p_attribute27 => p_attribute27
540 ,p_attribute28 => p_attribute28
541 ,p_attribute29 => p_attribute29
542 ,p_attribute30 => p_attribute30
543 ,p_attribute_category => p_attribute_category
544 );
545 exception
546 when hr_api.cannot_find_prog_unit then
547 hr_api.cannot_find_prog_unit_error
548 (
549 p_module_name => 'create_corps_extra_info'
550 ,p_hook_type => 'BP'
551 );
552 --
553 -- End of API User Hook for the before hook of create_corps_extra_info
554 --
555 end;
556 --
557 -- Validation in addition to Row Handlers
558 --
559 IF p_information_type = 'ORGANIZATION' THEN
560 validate_corps_org_info(p_effective_date => p_effective_date
561 ,p_corps_definition_id => p_corps_definition_id
562 ,p_organization_id => p_information3);
563 ELSIF p_information_type = 'FILERE' THEN
564 validate_corps_prof_field_info(p_effective_date => p_effective_date
565 ,p_corps_definition_id => p_corps_definition_id
566 ,p_field_of_prof_activity_id => p_information3);
567 ELSIF p_information_type = 'EXAM' THEN
568 validate_corps_exam_info(p_effective_date => p_effective_date
569 ,p_corps_definition_id => p_corps_definition_id
570 ,p_examination_type_cd => p_information3
571 ,p_institution_id => p_information4
572 ,p_joining_way_cd => p_information5
573 ,p_mandatory_flag => p_information6
574 ,p_exam_name => p_information7);
575 ELSIF p_information_type = 'TRAINING' THEN
576 validate_corps_training_info(p_effective_date => p_effective_date
577 ,p_corps_definition_id => p_corps_definition_id
578 ,p_qualification_id => p_information3
579 ,p_institution_id => p_information4
580 ,p_training_duration => p_information5
581 ,p_training_duration_uom => p_information6
582 ,p_joining_way_cd => p_information7
583 ,p_mandatory_flag => p_information8);
584 END IF;
585 hr_multi_message.end_validation_set;
586 --
587 --
588 pqh_cei_ins.ins
589 (
590 p_effective_date => trunc(p_effective_date)
591 ,p_corps_extra_info_id => l_corps_extra_info_id
592 ,p_object_version_number => l_object_version_number
593 ,p_corps_definition_id => p_corps_definition_id
594 ,p_information_type => p_information_type
595 ,p_information1 => p_information1
596 ,p_information2 => p_information2
597 ,p_information3 => p_information3
598 ,p_information4 => p_information4
599 ,p_information5 => p_information5
600 ,p_information6 => p_information6
601 ,p_information7 => p_information7
602 ,p_information8 => p_information8
603 ,p_information9 => p_information9
604 ,p_information10 => p_information10
605 ,p_information11 => p_information11
606 ,p_information12 => p_information12
607 ,p_information13 => p_information13
608 ,p_information14 => p_information14
609 ,p_information15 => p_information15
610 ,p_information16 => p_information16
611 ,p_information17 => p_information17
612 ,p_information18 => p_information18
613 ,p_information19 => p_information19
614 ,p_information20 => p_information20
615 ,p_information21 => p_information21
616 ,p_information22 => p_information22
617 ,p_information23 => p_information23
618 ,p_information24 => p_information24
619 ,p_information25 => p_information25
620 ,p_information26 => p_information26
621 ,p_information27 => p_information27
622 ,p_information28 => p_information28
623 ,p_information29 => p_information29
624 ,p_information30 => p_information30
625 ,p_information_category => p_information_category
626 ,p_attribute1 => p_attribute1
627 ,p_attribute2 => p_attribute2
628 ,p_attribute3 => p_attribute3
629 ,p_attribute4 => p_attribute4
630 ,p_attribute5 => p_attribute5
631 ,p_attribute6 => p_attribute6
632 ,p_attribute7 => p_attribute7
633 ,p_attribute8 => p_attribute8
634 ,p_attribute9 => p_attribute9
635 ,p_attribute10 => p_attribute10
636 ,p_attribute11 => p_attribute11
637 ,p_attribute12 => p_attribute12
638 ,p_attribute13 => p_attribute13
639 ,p_attribute14 => p_attribute14
640 ,p_attribute15 => p_attribute15
641 ,p_attribute16 => p_attribute16
642 ,p_attribute17 => p_attribute17
643 ,p_attribute18 => p_attribute18
644 ,p_attribute19 => p_attribute19
645 ,p_attribute20 => p_attribute20
646 ,p_attribute21 => p_attribute21
647 ,p_attribute22 => p_attribute22
648 ,p_attribute23 => p_attribute23
649 ,p_attribute24 => p_attribute24
650 ,p_attribute25 => p_attribute25
651 ,p_attribute26 => p_attribute26
652 ,p_attribute27 => p_attribute27
653 ,p_attribute28 => p_attribute28
654 ,p_attribute29 => p_attribute29
655 ,p_attribute30 => p_attribute30
656 ,p_attribute_category => p_attribute_category
657 );
658
659 begin
660 --
661 -- Start of API User Hook for the afetr hook of create_corps_extra_info
662 pqh_corps_extra_info_bk1.create_corps_extra_info_a
663 (
664 p_effective_date => trunc(p_effective_date)
665 ,p_corps_definition_id => p_corps_definition_id
666 ,p_information_type => p_information_type
667 ,p_information1 => p_information1
668 ,p_information2 => p_information2
669 ,p_information3 => p_information3
670 ,p_information4 => p_information4
671 ,p_information5 => p_information5
672 ,p_information6 => p_information6
673 ,p_information7 => p_information7
674 ,p_information8 => p_information8
675 ,p_information9 => p_information9
676 ,p_information10 => p_information10
677 ,p_information11 => p_information11
678 ,p_information12 => p_information12
679 ,p_information13 => p_information13
680 ,p_information14 => p_information14
681 ,p_information15 => p_information15
682 ,p_information16 => p_information16
683 ,p_information17 => p_information17
684 ,p_information18 => p_information18
685 ,p_information19 => p_information19
686 ,p_information20 => p_information20
687 ,p_information21 => p_information21
688 ,p_information22 => p_information22
689 ,p_information23 => p_information23
690 ,p_information24 => p_information24
691 ,p_information25 => p_information25
692 ,p_information26 => p_information26
693 ,p_information27 => p_information27
694 ,p_information28 => p_information28
695 ,p_information29 => p_information29
696 ,p_information30 => p_information30
697 ,p_information_category => p_information_category
698 ,p_attribute1 => p_attribute1
699 ,p_attribute2 => p_attribute2
700 ,p_attribute3 => p_attribute3
701 ,p_attribute4 => p_attribute4
702 ,p_attribute5 => p_attribute5
703 ,p_attribute6 => p_attribute6
704 ,p_attribute7 => p_attribute7
705 ,p_attribute8 => p_attribute8
706 ,p_attribute9 => p_attribute9
707 ,p_attribute10 => p_attribute10
708 ,p_attribute11 => p_attribute11
709 ,p_attribute12 => p_attribute12
710 ,p_attribute13 => p_attribute13
711 ,p_attribute14 => p_attribute14
712 ,p_attribute15 => p_attribute15
713 ,p_attribute16 => p_attribute16
714 ,p_attribute17 => p_attribute17
715 ,p_attribute18 => p_attribute18
716 ,p_attribute19 => p_attribute19
717 ,p_attribute20 => p_attribute20
718 ,p_attribute21 => p_attribute21
719 ,p_attribute22 => p_attribute22
720 ,p_attribute23 => p_attribute23
721 ,p_attribute24 => p_attribute24
722 ,p_attribute25 => p_attribute25
723 ,p_attribute26 => p_attribute26
724 ,p_attribute27 => p_attribute27
725 ,p_attribute28 => p_attribute28
726 ,p_attribute29 => p_attribute29
727 ,p_attribute30 => p_attribute30
728 ,p_attribute_category => p_attribute_category
729 );
730 exception
731 when hr_api.cannot_find_prog_unit then
732 hr_api.cannot_find_prog_unit_error
733 (
734 p_module_name => 'create_corps_extra_info'
735 ,p_hook_type => 'AP'
736 );
737 --
738 -- End of API User Hook for the after hook of create_corps_extra_info
739 --
740 end;
741
742 --
743 hr_utility.set_location(l_proc, 60);
744 --
745 -- When in validation only mode raise the Validate_Enabled exception
746 --
747 if p_validate then
748 raise hr_api.validate_enabled;
749 end if;
750 --
751 -- Set all output arguments
752 --
753 p_corps_extra_info_id := l_corps_extra_info_id;
754 p_object_version_number := l_object_version_number;
755 --
756 hr_utility.set_location(' Leaving:'||l_proc, 70);
757 --
758 exception
759 --
760 when hr_api.validate_enabled then
761 --
762 -- As the Validate_Enabled exception has been raised
763 -- we must rollback to the savepoint
764 --
765 ROLLBACK TO create_corps_extra_info;
766 --
767 -- Only set output warning arguments
768 -- (Any key or derived arguments must be set to null
769 -- when validation only mode is being used.)
770 --
771 p_corps_extra_info_id := null;
772 p_object_version_number := null;
773 hr_utility.set_location(' Leaving:'||l_proc, 80);
774 --
775 when others then
776 p_corps_extra_info_id := null;
777 p_object_version_number := null;
778 --
779 -- A validation or unexpected error has occured
780 --
781 ROLLBACK TO create_corps_extra_info;
782 raise;
783 --
784 end create_corps_extra_info;
785
786 -- ----------------------------------------------------------------------------
787 -- |------------------------< update_corps_extra_info >------------------------|
788 -- ----------------------------------------------------------------------------
789 -- {Start Of Comments}
790 --
791 -- Description:
792 --
793 -- Prerequisites:
794 --
795 --
796 -- In Parameters:
797 --
798 -- Name Reqd Type Description
799 -- p_validate YES boolean Commit or Rollback
800 -- p_corps_extra_info_id YES number PK of record
801 -- p_effective_date YES date
802 -- p_corps_definition_id NO number
803 -- p_information_type NO varchar2
804 -- p_information1 NO varchar2
805 -- p_information2 NO varchar2
806 -- p_information3 NO varchar2
807 -- p_information4 NO varchar2
808 -- p_information5 NO varchar2
809 -- p_information6 NO varchar2
810 -- p_information7 NO varchar2
811 -- p_information8 NO varchar2
812 -- p_information9 NO varchar2
813 -- p_information10 NO varchar2
814 -- p_information11 NO varchar2
815 -- p_information12 NO varchar2
816 -- p_information13 NO varchar2
817 -- p_information14 NO varchar2
818 -- p_information15 NO varchar2
819 -- p_information16 NO varchar2
820 -- p_information17 NO varchar2
821 -- p_information18 NO varchar2
822 -- p_information19 NO varchar2
823 -- p_information20 NO varchar2
824 -- p_information21 NO varchar2
825 -- p_information22 NO varchar2
826 -- p_information23 NO varchar2
827 -- p_information24 NO varchar2
828 -- p_information25 NO varchar2
829 -- p_information26 NO varchar2
830 -- p_information27 NO varchar2
831 -- p_information28 NO varchar2
832 -- p_information29 NO varchar2
833 -- p_information30 NO varchar2
834 -- p_information_category NO varchar2
835 -- p_attribute1 NO varchar2
836 -- p_attribute2 NO varchar2
837 -- p_attribute3 NO varchar2
838 -- p_attribute4 NO varchar2
839 -- p_attribute5 NO varchar2
840 -- p_attribute6 NO varchar2
841 -- p_attribute7 NO varchar2
842 -- p_attribute8 NO varchar2
843 -- p_attribute9 NO varchar2
844 -- p_attribute10 NO varchar2
845 -- p_attribute11 NO varchar2
846 -- p_attribute12 NO varchar2
847 -- p_attribute13 NO varchar2
848 -- p_attribute14 NO varchar2
849 -- p_attribute15 NO varchar2
850 -- p_attribute16 NO varchar2
851 -- p_attribute17 NO varchar2
852 -- p_attribute18 NO varchar2
853 -- p_attribute19 NO varchar2
854 -- p_attribute20 NO varchar2
855 -- p_attribute21 NO varchar2
856 -- p_attribute22 NO varchar2
857 -- p_attribute23 NO varchar2
858 -- p_attribute24 NO varchar2
859 -- p_attribute25 NO varchar2
860 -- p_attribute26 NO varchar2
861 -- p_attribute27 NO varchar2
862 -- p_attribute28 NO varchar2
863 -- p_attribute29 NO varchar2
864 -- p_attribute30 NO varchar2
865 -- p_attribute_category NO varchar2
866 --
867 -- Post Success:
868 --
869 -- Name Type Description
870 -- p_object_version_number Yes number OVN of record
871 --
872 -- Post Failure:
873 --
874 -- Access Status:
875 -- Public.
876 --
877 -- {End Of Comments}
878 --
879 procedure update_corps_extra_info
880 (
881 p_validate in boolean default false
882 ,p_effective_date in date
883 ,p_corps_extra_info_id in number
884 ,p_corps_definition_id in number default hr_api.g_number
885 ,p_information_type in varchar2 default hr_api.g_varchar2
886 ,p_information1 in varchar2 default hr_api.g_varchar2
887 ,p_information2 in varchar2 default hr_api.g_varchar2
888 ,p_information3 in varchar2 default hr_api.g_varchar2
889 ,p_information4 in varchar2 default hr_api.g_varchar2
890 ,p_information5 in varchar2 default hr_api.g_varchar2
891 ,p_information6 in varchar2 default hr_api.g_varchar2
892 ,p_information7 in varchar2 default hr_api.g_varchar2
893 ,p_information8 in varchar2 default hr_api.g_varchar2
894 ,p_information9 in varchar2 default hr_api.g_varchar2
895 ,p_information10 in varchar2 default hr_api.g_varchar2
896 ,p_information11 in varchar2 default hr_api.g_varchar2
897 ,p_information12 in varchar2 default hr_api.g_varchar2
898 ,p_information13 in varchar2 default hr_api.g_varchar2
899 ,p_information14 in varchar2 default hr_api.g_varchar2
900 ,p_information15 in varchar2 default hr_api.g_varchar2
901 ,p_information16 in varchar2 default hr_api.g_varchar2
902 ,p_information17 in varchar2 default hr_api.g_varchar2
903 ,p_information18 in varchar2 default hr_api.g_varchar2
904 ,p_information19 in varchar2 default hr_api.g_varchar2
905 ,p_information20 in varchar2 default hr_api.g_varchar2
906 ,p_information21 in varchar2 default hr_api.g_varchar2
907 ,p_information22 in varchar2 default hr_api.g_varchar2
908 ,p_information23 in varchar2 default hr_api.g_varchar2
909 ,p_information24 in varchar2 default hr_api.g_varchar2
910 ,p_information25 in varchar2 default hr_api.g_varchar2
911 ,p_information26 in varchar2 default hr_api.g_varchar2
912 ,p_information27 in varchar2 default hr_api.g_varchar2
913 ,p_information28 in varchar2 default hr_api.g_varchar2
914 ,p_information29 in varchar2 default hr_api.g_varchar2
915 ,p_information30 in varchar2 default hr_api.g_varchar2
916 ,p_information_category in varchar2 default hr_api.g_varchar2
917 ,p_attribute1 in varchar2 default hr_api.g_varchar2
918 ,p_attribute2 in varchar2 default hr_api.g_varchar2
919 ,p_attribute3 in varchar2 default hr_api.g_varchar2
920 ,p_attribute4 in varchar2 default hr_api.g_varchar2
921 ,p_attribute5 in varchar2 default hr_api.g_varchar2
922 ,p_attribute6 in varchar2 default hr_api.g_varchar2
923 ,p_attribute7 in varchar2 default hr_api.g_varchar2
924 ,p_attribute8 in varchar2 default hr_api.g_varchar2
925 ,p_attribute9 in varchar2 default hr_api.g_varchar2
926 ,p_attribute10 in varchar2 default hr_api.g_varchar2
927 ,p_attribute11 in varchar2 default hr_api.g_varchar2
928 ,p_attribute12 in varchar2 default hr_api.g_varchar2
929 ,p_attribute13 in varchar2 default hr_api.g_varchar2
930 ,p_attribute14 in varchar2 default hr_api.g_varchar2
931 ,p_attribute15 in varchar2 default hr_api.g_varchar2
932 ,p_attribute16 in varchar2 default hr_api.g_varchar2
933 ,p_attribute17 in varchar2 default hr_api.g_varchar2
934 ,p_attribute18 in varchar2 default hr_api.g_varchar2
935 ,p_attribute19 in varchar2 default hr_api.g_varchar2
936 ,p_attribute20 in varchar2 default hr_api.g_varchar2
937 ,p_attribute21 in varchar2 default hr_api.g_varchar2
938 ,p_attribute22 in varchar2 default hr_api.g_varchar2
939 ,p_attribute23 in varchar2 default hr_api.g_varchar2
940 ,p_attribute24 in varchar2 default hr_api.g_varchar2
941 ,p_attribute25 in varchar2 default hr_api.g_varchar2
942 ,p_attribute26 in varchar2 default hr_api.g_varchar2
943 ,p_attribute27 in varchar2 default hr_api.g_varchar2
944 ,p_attribute28 in varchar2 default hr_api.g_varchar2
945 ,p_attribute29 in varchar2 default hr_api.g_varchar2
946 ,p_attribute30 in varchar2 default hr_api.g_varchar2
947 ,p_attribute_category in varchar2 default hr_api.g_varchar2
948 ,p_object_version_number in out nocopy number
949 ) is
950 --
951 -- Declare cursors and local variables
952 --
953 l_proc varchar2(72) := g_package||'update_corps_extra_info';
954 l_object_version_number pqh_corps_extra_info.object_version_number%TYPE;
955 --
956 begin
957 --
958 hr_utility.set_location('Entering:'|| l_proc, 10);
959 --
960 -- Issue a savepoint if operating in validation only mode
961 --
962 savepoint update_corps_extra_info;
963 --
964 hr_utility.set_location(l_proc, 20);
965 l_object_version_number := p_object_version_number;
966 --
967 -- Process Logic
968 --
969 begin
970 --
971 -- Start of API User Hook for the before hook of update_corps_extra_info
972 pqh_corps_extra_info_bk2.update_corps_extra_info_b
973 (
974 p_effective_date => trunc(p_effective_date)
975 ,p_corps_extra_info_id => p_corps_extra_info_id
976 ,p_corps_definition_id => p_corps_definition_id
977 ,p_object_version_number => p_object_version_number
978 ,p_information_type => p_information_type
979 ,p_information1 => p_information1
980 ,p_information2 => p_information2
981 ,p_information3 => p_information3
982 ,p_information4 => p_information4
983 ,p_information5 => p_information5
984 ,p_information6 => p_information6
985 ,p_information7 => p_information7
986 ,p_information8 => p_information8
987 ,p_information9 => p_information9
988 ,p_information10 => p_information10
989 ,p_information11 => p_information11
990 ,p_information12 => p_information12
991 ,p_information13 => p_information13
992 ,p_information14 => p_information14
993 ,p_information15 => p_information15
994 ,p_information16 => p_information16
995 ,p_information17 => p_information17
996 ,p_information18 => p_information18
997 ,p_information19 => p_information19
998 ,p_information20 => p_information20
999 ,p_information21 => p_information21
1000 ,p_information22 => p_information22
1001 ,p_information23 => p_information23
1002 ,p_information24 => p_information24
1003 ,p_information25 => p_information25
1004 ,p_information26 => p_information26
1005 ,p_information27 => p_information27
1006 ,p_information28 => p_information28
1007 ,p_information29 => p_information29
1008 ,p_information30 => p_information30
1009 ,p_information_category => p_information_category
1010 ,p_attribute1 => p_attribute1
1011 ,p_attribute2 => p_attribute2
1012 ,p_attribute3 => p_attribute3
1013 ,p_attribute4 => p_attribute4
1014 ,p_attribute5 => p_attribute5
1015 ,p_attribute6 => p_attribute6
1016 ,p_attribute7 => p_attribute7
1017 ,p_attribute8 => p_attribute8
1018 ,p_attribute9 => p_attribute9
1019 ,p_attribute10 => p_attribute10
1020 ,p_attribute11 => p_attribute11
1021 ,p_attribute12 => p_attribute12
1022 ,p_attribute13 => p_attribute13
1023 ,p_attribute14 => p_attribute14
1024 ,p_attribute15 => p_attribute15
1025 ,p_attribute16 => p_attribute16
1026 ,p_attribute17 => p_attribute17
1027 ,p_attribute18 => p_attribute18
1028 ,p_attribute19 => p_attribute19
1029 ,p_attribute20 => p_attribute20
1030 ,p_attribute21 => p_attribute21
1031 ,p_attribute22 => p_attribute22
1032 ,p_attribute23 => p_attribute23
1033 ,p_attribute24 => p_attribute24
1034 ,p_attribute25 => p_attribute25
1035 ,p_attribute26 => p_attribute26
1036 ,p_attribute27 => p_attribute27
1037 ,p_attribute28 => p_attribute28
1038 ,p_attribute29 => p_attribute29
1039 ,p_attribute30 => p_attribute30
1040 ,p_attribute_category => p_attribute_category
1041 );
1042 exception
1043 when hr_api.cannot_find_prog_unit then
1044 hr_api.cannot_find_prog_unit_error
1045 (
1046 p_module_name => 'update_corps_extra_info'
1047 ,p_hook_type => 'BP'
1048 );
1049 --
1050 -- End of API User Hook for the before hook of update_corps_extra_info
1051 --
1052 end;
1053 --
1054 -- Validation in addition to Row Handlers
1055 --
1056 IF p_information_type = 'ORGANIZATION' THEN
1057 validate_corps_org_info(p_effective_date => p_effective_date
1058 ,p_corps_definition_id => p_corps_definition_id
1059 ,p_organization_id => p_information3
1060 ,p_corps_extra_info_id => p_corps_extra_info_id);
1061 ELSIF p_information_type = 'FILERE' THEN
1062 validate_corps_prof_field_info(p_effective_date => p_effective_date
1063 ,p_corps_definition_id => p_corps_definition_id
1064 ,p_field_of_prof_activity_id => p_information3
1065 ,p_corps_extra_info_id => p_corps_extra_info_id);
1066 ELSIF p_information_type = 'EXAM' THEN
1067 validate_corps_exam_info(p_effective_date => p_effective_date
1068 ,p_corps_definition_id => p_corps_definition_id
1069 ,p_examination_type_cd => p_information3
1070 ,p_institution_id => p_information4
1071 ,p_joining_way_cd => p_information5
1072 ,p_mandatory_flag => p_information6
1073 ,p_exam_name => p_information7
1074 ,p_corps_extra_info_id => p_corps_extra_info_id);
1075 ELSIF p_information_type = 'TRAINING' THEN
1076 validate_corps_training_info(p_effective_date => p_effective_date
1077 ,p_corps_definition_id => p_corps_definition_id
1078 ,p_qualification_id => p_information3
1079 ,p_institution_id => p_information4
1080 ,p_training_duration => p_information5
1081 ,p_training_duration_uom => p_information6
1082 ,p_joining_way_cd => p_information7
1083 ,p_mandatory_flag => p_information8
1084 ,p_corps_extra_info_id => p_corps_extra_info_id);
1085 END IF;
1086 hr_multi_message.end_validation_set;
1087 --
1088 --
1089 pqh_cei_upd.upd
1090 (
1091 p_effective_date => trunc(p_effective_date)
1092 ,p_corps_extra_info_id => p_corps_extra_info_id
1093 ,p_object_version_number => l_object_version_number
1094 ,p_corps_definition_id => p_corps_definition_id
1095 ,p_information_type => p_information_type
1096 ,p_information1 => p_information1
1097 ,p_information2 => p_information2
1098 ,p_information3 => p_information3
1099 ,p_information4 => p_information4
1100 ,p_information5 => p_information5
1101 ,p_information6 => p_information6
1102 ,p_information7 => p_information7
1103 ,p_information8 => p_information8
1104 ,p_information9 => p_information9
1105 ,p_information10 => p_information10
1106 ,p_information11 => p_information11
1107 ,p_information12 => p_information12
1108 ,p_information13 => p_information13
1109 ,p_information14 => p_information14
1110 ,p_information15 => p_information15
1111 ,p_information16 => p_information16
1112 ,p_information17 => p_information17
1113 ,p_information18 => p_information18
1114 ,p_information19 => p_information19
1115 ,p_information20 => p_information20
1116 ,p_information21 => p_information21
1117 ,p_information22 => p_information22
1118 ,p_information23 => p_information23
1119 ,p_information24 => p_information24
1120 ,p_information25 => p_information25
1121 ,p_information26 => p_information26
1122 ,p_information27 => p_information27
1123 ,p_information28 => p_information28
1124 ,p_information29 => p_information29
1125 ,p_information30 => p_information30
1126 ,p_information_category => p_information_category
1127 ,p_attribute1 => p_attribute1
1128 ,p_attribute2 => p_attribute2
1129 ,p_attribute3 => p_attribute3
1130 ,p_attribute4 => p_attribute4
1131 ,p_attribute5 => p_attribute5
1132 ,p_attribute6 => p_attribute6
1133 ,p_attribute7 => p_attribute7
1134 ,p_attribute8 => p_attribute8
1135 ,p_attribute9 => p_attribute9
1136 ,p_attribute10 => p_attribute10
1137 ,p_attribute11 => p_attribute11
1138 ,p_attribute12 => p_attribute12
1139 ,p_attribute13 => p_attribute13
1140 ,p_attribute14 => p_attribute14
1141 ,p_attribute15 => p_attribute15
1142 ,p_attribute16 => p_attribute16
1143 ,p_attribute17 => p_attribute17
1144 ,p_attribute18 => p_attribute18
1145 ,p_attribute19 => p_attribute19
1146 ,p_attribute20 => p_attribute20
1147 ,p_attribute21 => p_attribute21
1148 ,p_attribute22 => p_attribute22
1149 ,p_attribute23 => p_attribute23
1150 ,p_attribute24 => p_attribute24
1151 ,p_attribute25 => p_attribute25
1152 ,p_attribute26 => p_attribute26
1153 ,p_attribute27 => p_attribute27
1154 ,p_attribute28 => p_attribute28
1155 ,p_attribute29 => p_attribute29
1156 ,p_attribute30 => p_attribute30
1157 ,p_attribute_category => p_attribute_category
1158 );
1159
1160 begin
1161 --
1162 -- Start of API User Hook for the afetr hook of update_corps_extra_info
1163 pqh_corps_extra_info_bk2.update_corps_extra_info_a
1164 (
1165 p_effective_date => trunc(p_effective_date)
1166 ,p_corps_extra_info_id => p_corps_extra_info_id
1167 ,p_corps_definition_id => p_corps_definition_id
1168 ,p_object_version_number => l_object_version_number
1169 ,p_information_type => p_information_type
1170 ,p_information1 => p_information1
1171 ,p_information2 => p_information2
1172 ,p_information3 => p_information3
1173 ,p_information4 => p_information4
1174 ,p_information5 => p_information5
1175 ,p_information6 => p_information6
1176 ,p_information7 => p_information7
1177 ,p_information8 => p_information8
1178 ,p_information9 => p_information9
1179 ,p_information10 => p_information10
1180 ,p_information11 => p_information11
1181 ,p_information12 => p_information12
1182 ,p_information13 => p_information13
1183 ,p_information14 => p_information14
1184 ,p_information15 => p_information15
1185 ,p_information16 => p_information16
1186 ,p_information17 => p_information17
1187 ,p_information18 => p_information18
1188 ,p_information19 => p_information19
1189 ,p_information20 => p_information20
1190 ,p_information21 => p_information21
1191 ,p_information22 => p_information22
1192 ,p_information23 => p_information23
1193 ,p_information24 => p_information24
1194 ,p_information25 => p_information25
1195 ,p_information26 => p_information26
1196 ,p_information27 => p_information27
1197 ,p_information28 => p_information28
1198 ,p_information29 => p_information29
1199 ,p_information30 => p_information30
1200 ,p_information_category => p_information_category
1201 ,p_attribute1 => p_attribute1
1202 ,p_attribute2 => p_attribute2
1203 ,p_attribute3 => p_attribute3
1204 ,p_attribute4 => p_attribute4
1205 ,p_attribute5 => p_attribute5
1206 ,p_attribute6 => p_attribute6
1207 ,p_attribute7 => p_attribute7
1208 ,p_attribute8 => p_attribute8
1209 ,p_attribute9 => p_attribute9
1210 ,p_attribute10 => p_attribute10
1211 ,p_attribute11 => p_attribute11
1212 ,p_attribute12 => p_attribute12
1213 ,p_attribute13 => p_attribute13
1214 ,p_attribute14 => p_attribute14
1215 ,p_attribute15 => p_attribute15
1216 ,p_attribute16 => p_attribute16
1217 ,p_attribute17 => p_attribute17
1218 ,p_attribute18 => p_attribute18
1219 ,p_attribute19 => p_attribute19
1220 ,p_attribute20 => p_attribute20
1221 ,p_attribute21 => p_attribute21
1222 ,p_attribute22 => p_attribute22
1223 ,p_attribute23 => p_attribute23
1224 ,p_attribute24 => p_attribute24
1225 ,p_attribute25 => p_attribute25
1226 ,p_attribute26 => p_attribute26
1227 ,p_attribute27 => p_attribute27
1228 ,p_attribute28 => p_attribute28
1229 ,p_attribute29 => p_attribute29
1230 ,p_attribute30 => p_attribute30
1231 ,p_attribute_category => p_attribute_category
1232 );
1233 exception
1234 when hr_api.cannot_find_prog_unit then
1235 hr_api.cannot_find_prog_unit_error
1236 (
1237 p_module_name => 'update_corps_extra_info'
1238 ,p_hook_type => 'AP'
1239 );
1240 --
1241 -- End of API User Hook for the after hook of update_corps_extra_info
1242 --
1243 end;
1244
1245 --
1246 hr_utility.set_location(l_proc, 60);
1247 --
1248 -- When in validation only mode raise the Validate_Enabled exception
1249 --
1250 if p_validate then
1251 raise hr_api.validate_enabled;
1252 end if;
1253 --
1254 -- Set all output arguments
1255 --
1256 p_object_version_number := l_object_version_number;
1257 --
1258 hr_utility.set_location(' Leaving:'||l_proc, 70);
1259 --
1260 exception
1261 --
1262 when hr_api.validate_enabled then
1263 --
1264 -- As the Validate_Enabled exception has been raised
1265 -- we must rollback to the savepoint
1266 --
1267 ROLLBACK TO update_corps_extra_info;
1268 --
1269 -- Only set output warning arguments
1270 -- (Any key or derived arguments must be set to null
1271 -- when validation only mode is being used.)
1272 --
1273 hr_utility.set_location(' Leaving:'||l_proc, 80);
1274 --
1275 when others then
1276 p_object_version_number := l_object_version_number;
1277 --
1278 -- A validation or unexpected error has occured
1279 --
1280 ROLLBACK TO update_corps_extra_info;
1281 raise;
1282 --
1283 end;
1284 --
1285 -- ----------------------------------------------------------------------------
1286 -- |------------------------< delete_corps_extra_info >------------------------|
1287 -- ----------------------------------------------------------------------------
1288 -- {Start Of Comments}
1289 --
1290 -- Description:
1291 --
1292 -- Prerequisites:
1293 --
1294 --
1295 -- In Parameters:
1296 -- Name Reqd Type Description
1297 --
1298 -- p_validate Yes boolean Commit or Rollback.
1299 -- p_corps_extra_info_id Yes number PK of record
1300 -- p_effective_date Yes date Session Date.
1301 -- p_object_version_number Yes number OVN of record
1302
1303 -- Post Success:
1304 --
1305 -- Name Type Description
1306 --
1307 -- Post Failure:
1308 --
1309 -- Access Status:
1310 -- Public.
1311 --
1312 -- {End Of Comments}
1313 --
1314 procedure delete_corps_extra_info
1315 (
1316 p_validate in boolean default false
1317 ,p_corps_extra_info_id in number
1318 ,p_object_version_number in number
1319 ) is
1320 --
1321 -- Declare cursors and local variables
1322 --
1323 l_proc varchar2(72) := g_package||'delete_corps_extra_info';
1324 l_object_version_number pqh_corps_extra_info.object_version_number%TYPE;
1325 --
1326 begin
1327 --
1328 hr_utility.set_location('Entering:'|| l_proc, 10);
1329 --
1330 -- Issue a savepoint if operating in validation only mode
1331 --
1332 savepoint delete_corps_extra_info;
1333 --
1334 hr_utility.set_location(l_proc, 20);
1335 --
1336 -- Process Logic
1337 --
1338 l_object_version_number := p_object_version_number;
1339 --
1340 --
1341 begin
1342 --
1343 -- Start of API User Hook for the before hook of delete_corps_extra_info
1344 --
1345 pqh_corps_extra_info_bk3.delete_corps_extra_info_b
1346 (
1347 p_corps_extra_info_id => p_corps_extra_info_id
1348 ,p_object_version_number => p_object_version_number
1349 );
1350 exception
1351 when hr_api.cannot_find_prog_unit then
1352 hr_api.cannot_find_prog_unit_error
1353 (p_module_name => 'delete_corps_extra_info'
1354 ,p_hook_type => 'BP'
1355 );
1356 --
1357 -- End of API User Hook for the before hook of delete_corps_extra_info
1358 --
1359 end;
1360 --
1361 --
1362 -- Validation in addition to Row Handlers
1363 --
1364 chk_prim_field_for_corps(p_corps_extra_info_id => p_corps_extra_info_id);
1365 hr_multi_message.end_validation_set;
1366 --
1367 PQH_CEI_del.del
1368 (
1369 p_corps_extra_info_id => p_corps_extra_info_id
1370 ,p_object_version_number => l_object_version_number
1371 );
1372 --
1373 begin
1374 --
1375 -- Start of API User Hook for the after hook of delete_corps_extra_info
1376 --
1377 pqh_corps_extra_info_bk3.delete_corps_extra_info_a
1378 (
1379 p_corps_extra_info_id => p_corps_extra_info_id
1380 ,p_object_version_number => l_object_version_number
1381 );
1382 exception
1383 when hr_api.cannot_find_prog_unit then
1384 hr_api.cannot_find_prog_unit_error
1385 (p_module_name => 'delete_corps_extra_info'
1386 ,p_hook_type => 'AP'
1387 );
1388 --
1389 -- End of API User Hook for the after hook of delete_corps_extra_info
1390 --
1391 end;
1392 --
1393 hr_utility.set_location(l_proc, 60);
1394 --
1395 -- When in validation only mode raise the Validate_Enabled exception
1396 --
1397 if p_validate then
1398 raise hr_api.validate_enabled;
1399 end if;
1400 --
1401 hr_utility.set_location(' Leaving:'||l_proc, 70);
1402 --
1403 exception
1404 --
1405 when hr_api.validate_enabled then
1406 --
1407 -- As the Validate_Enabled exception has been raised
1408 -- we must rollback to the savepoint
1409 --
1410 ROLLBACK TO delete_corps_extra_info;
1411 --
1412 -- Only set output warning arguments
1413 -- (Any key or derived arguments must be set to null
1414 -- when validation only mode is being used.)
1415 --
1416 --
1417 when others then
1418 --
1419 -- A validation or unexpected error has occured
1420 --
1421 ROLLBACK TO delete_corps_extra_info;
1422 raise;
1423 --
1424 end delete_corps_extra_info;
1425
1426 --
1427 --
1428 -- ----------------------------------------------------------------------------
1429 -- |-------------------------------< lck >------------------------------------|
1430 -- ----------------------------------------------------------------------------
1431 --
1432 procedure lck
1433 (
1434 p_corps_extra_info_id in number
1435 ,p_object_version_number in number
1436 ) is
1437 --
1438 --
1439 -- Declare cursors and local variables
1440 --
1441 l_proc varchar2(72) := g_package||'lck';
1442 --
1443 begin
1444 --
1445 hr_utility.set_location('Entering:'|| l_proc, 10);
1446 --
1447 PQH_CEI_shd.lck
1448 (
1449 p_corps_extra_info_id => p_corps_extra_info_id
1450 ,p_object_version_number => p_object_version_number
1451 );
1452 --
1453 hr_utility.set_location(' Leaving:'||l_proc, 70);
1454 --
1455 end lck;
1456 --
1457 end pqh_corps_extra_info_api;