[Home] [Help]
PACKAGE BODY: APPS.OTA_TPM_BUS
Source
1 PACKAGE BODY OTA_TPM_BUS AS
2 /* $Header: ottpmrhi.pkb 120.1 2005/12/14 15:33:09 asud noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_tpm_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) DEFAULT NULL;
14 g_training_plan_member_id number DEFAULT NULL;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 PROCEDURE set_security_group_id
21 (p_training_plan_member_id IN number
22 ,p_associated_column1 IN varchar2
23 ) IS
24 --
25 -- Declare cursor
26 --
27 CURSOR csr_sec_grp IS
28 SELECT inf.org_information14
29 FROM hr_organization_information inf
30 ,ota_training_plan_members tpm
31 WHERE tpm.training_plan_member_id = p_training_plan_member_id
32 AND inf.organization_id = tpm.business_group_id
33 AND inf.org_information_context || '' = 'Business Group Information';
34
35
36
37 --
38 -- Declare local variables
39 --
40 l_security_group_id number;
41 l_proc varchar2(72) := g_package||'set_security_group_id';
42 --
43 BEGIN
44 --
45 hr_utility.set_location('Entering:'|| l_proc, 10);
46 --
47 -- Ensure that all the mandatory parameter are not null
48 --
49 hr_api.mandatory_arg_error
50 (p_api_name => l_proc
51 ,p_argument => 'training_plan_member_id'
52 ,p_argument_value => p_training_plan_member_id
53 );
54 --
55 OPEN csr_sec_grp;
56 FETCH csr_sec_grp INTO l_security_group_id;
57 --
58 IF csr_sec_grp%NOTFOUND THEN
59 --
60 CLOSE csr_sec_grp;
61 --
62 -- The primary key is invalid therefore we must error
63 --
64 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
65 hr_multi_message.add
66 (p_associated_column1 => NVL(p_associated_column1, 'TRAINING_PLAN_MEMBER_ID'));
67 --
68 ELSE
69
70 CLOSE csr_sec_grp;
71 --
72 -- Set the security_group_id in CLIENT_INFO
73 --
74 hr_api.set_security_group_id
75 (p_security_group_id => l_security_group_id
76 );
77
78 END IF;
79 hr_utility.set_location(' Leaving:'|| l_proc, 20);
80 --
81 END set_security_group_id;
82 --
83 -- ---------------------------------------------------------------------------
84 -- |---------------------< return_legislation_code >-------------------------|
85 -- ---------------------------------------------------------------------------
86 --
87 FUNCTION return_legislation_code
88 (p_training_plan_member_id IN number
89 )
90 RETURN Varchar2 IS
91 --
92 -- Declare cursor
93 --
94 CURSOR csr_leg_code IS
95 SELECT pbg.legislation_code
96 FROM per_business_groups pbg
97 , ota_training_plan_members ota
98 WHERE ota.training_plan_member_id = p_training_plan_member_id
99 AND pbg.business_group_id = ota.business_group_id;
100 --
101 -- Declare local variables
102 --
103 l_legislation_code varchar2(150);
104 l_proc varchar2(72) := g_package||'return_legislation_code';
105 --
106 BEGIN
107 --
108 hr_utility.set_location('Entering:'|| l_proc, 10);
109 --
110 -- Ensure that all the mandatory parameter are not null
111 --
112 hr_api.mandatory_arg_error
113 (p_api_name => l_proc
114 ,p_argument => 'training_plan_member_id'
115 ,p_argument_value => p_training_plan_member_id
116 );
117 --
118 IF ( NVL(ota_tpm_bus.g_training_plan_member_id, hr_api.g_number)
119 = p_training_plan_member_id) THEN
120 --
121 -- The legislation code has already been found with a previous
122 -- call to this function. Just return the value in the global
123 -- variable.
124 --
125 l_legislation_code := ota_tpm_bus.g_legislation_code;
126 hr_utility.set_location(l_proc, 20);
127 ELSE
128 --
129 -- The ID is different to the last call to this function
130 -- or this is the first call to this function.
131 --
132 OPEN csr_leg_code;
133 FETCH csr_leg_code INTO l_legislation_code;
134 --
135 IF csr_leg_code%NOTFOUND THEN
136 --
137 -- The primary key is invalid therefore we must error
138 --
139 CLOSE csr_leg_code;
140 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
141 fnd_message.raise_error;
142 END IF;
143 hr_utility.set_location(l_proc,30);
144 --
145 -- Set the global variables so the values are
146 -- available for the next call to this function.
147 --
148 CLOSE csr_leg_code;
149 ota_tpm_bus.g_training_plan_member_id := p_training_plan_member_id;
150 ota_tpm_bus.g_legislation_code := l_legislation_code;
151 END IF;
152 hr_utility.set_location(' Leaving:'|| l_proc, 40);
153 RETURN l_legislation_code;
154 END return_legislation_code;
155 --
156 -- ----------------------------------------------------------------------------
157 -- |------------------------------< chk_df >----------------------------------|
158 -- ----------------------------------------------------------------------------
159 --
160 -- Description:
161 -- Validates all the Descriptive Flexfield values.
162 --
163 -- Prerequisites:
164 -- All other columns have been validated. Must be called as the
165 -- last step from insert_validate and update_validate.
166 --
167 -- In Arguments:
168 -- p_rec
169 --
170 -- Post Success:
171 -- If the Descriptive Flexfield structure column and data values are
172 -- all valid this procedure will end normally and processing will
173 -- continue.
174 --
175 -- Post Failure:
176 -- If the Descriptive Flexfield structure column value or any of
177 -- the data values are invalid then an application error is raised as
178 -- a PL/SQL exception.
179 --
180 -- Access Status:
181 -- Internal Row Handler Use Only.
182 --
183 -- ----------------------------------------------------------------------------
184 PROCEDURE chk_df
185 (p_rec IN ota_tpm_shd.g_rec_type
186 ) IS
187 --
188 l_proc varchar2(72) := g_package || 'chk_df';
189 --
190 BEGIN
191 hr_utility.set_location('Entering:'||l_proc,10);
192 --
193 IF ((p_rec.training_plan_member_id IS NOT NULL) AND (
194 NVL(ota_tpm_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
195 NVL(p_rec.attribute_category, hr_api.g_varchar2) OR
196 NVL(ota_tpm_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
197 NVL(p_rec.attribute1, hr_api.g_varchar2) OR
198 NVL(ota_tpm_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
199 NVL(p_rec.attribute2, hr_api.g_varchar2) OR
200 NVL(ota_tpm_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
201 NVL(p_rec.attribute3, hr_api.g_varchar2) OR
202 NVL(ota_tpm_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
203 NVL(p_rec.attribute4, hr_api.g_varchar2) OR
204 NVL(ota_tpm_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
205 NVL(p_rec.attribute5, hr_api.g_varchar2) OR
206 NVL(ota_tpm_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
207 NVL(p_rec.attribute6, hr_api.g_varchar2) OR
208 NVL(ota_tpm_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
209 NVL(p_rec.attribute7, hr_api.g_varchar2) OR
210 NVL(ota_tpm_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
211 NVL(p_rec.attribute8, hr_api.g_varchar2) OR
212 NVL(ota_tpm_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
213 NVL(p_rec.attribute9, hr_api.g_varchar2) OR
214 NVL(ota_tpm_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
215 NVL(p_rec.attribute10, hr_api.g_varchar2) OR
216 NVL(ota_tpm_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
217 NVL(p_rec.attribute11, hr_api.g_varchar2) OR
218 NVL(ota_tpm_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
219 NVL(p_rec.attribute12, hr_api.g_varchar2) OR
220 NVL(ota_tpm_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
221 NVL(p_rec.attribute13, hr_api.g_varchar2) OR
222 NVL(ota_tpm_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
223 NVL(p_rec.attribute14, hr_api.g_varchar2) OR
224 NVL(ota_tpm_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
225 NVL(p_rec.attribute15, hr_api.g_varchar2) OR
226 NVL(ota_tpm_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
227 NVL(p_rec.attribute16, hr_api.g_varchar2) OR
228 NVL(ota_tpm_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
229 NVL(p_rec.attribute17, hr_api.g_varchar2) OR
230 NVL(ota_tpm_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
231 NVL(p_rec.attribute18, hr_api.g_varchar2) OR
232 NVL(ota_tpm_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
233 NVL(p_rec.attribute19, hr_api.g_varchar2) OR
234 NVL(ota_tpm_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
235 NVL(p_rec.attribute20, hr_api.g_varchar2) OR
236 NVL(ota_tpm_shd.g_old_rec.attribute21, hr_api.g_varchar2) <>
237 NVL(p_rec.attribute21, hr_api.g_varchar2) OR
238 NVL(ota_tpm_shd.g_old_rec.attribute22, hr_api.g_varchar2) <>
239 NVL(p_rec.attribute22, hr_api.g_varchar2) OR
240 NVL(ota_tpm_shd.g_old_rec.attribute23, hr_api.g_varchar2) <>
241 NVL(p_rec.attribute23, hr_api.g_varchar2) OR
242 NVL(ota_tpm_shd.g_old_rec.attribute24, hr_api.g_varchar2) <>
243 NVL(p_rec.attribute24, hr_api.g_varchar2) OR
244 NVL(ota_tpm_shd.g_old_rec.attribute25, hr_api.g_varchar2) <>
245 NVL(p_rec.attribute25, hr_api.g_varchar2) OR
246 NVL(ota_tpm_shd.g_old_rec.attribute26, hr_api.g_varchar2) <>
247 NVL(p_rec.attribute26, hr_api.g_varchar2) OR
248 NVL(ota_tpm_shd.g_old_rec.attribute27, hr_api.g_varchar2) <>
249 NVL(p_rec.attribute27, hr_api.g_varchar2) OR
250 NVL(ota_tpm_shd.g_old_rec.attribute28, hr_api.g_varchar2) <>
251 NVL(p_rec.attribute28, hr_api.g_varchar2) OR
252 NVL(ota_tpm_shd.g_old_rec.attribute29, hr_api.g_varchar2) <>
253 NVL(p_rec.attribute29, hr_api.g_varchar2) OR
254 NVL(ota_tpm_shd.g_old_rec.attribute30, hr_api.g_varchar2) <>
255 NVL(p_rec.attribute30, hr_api.g_varchar2) ) )
256 OR (p_rec.training_plan_member_id IS NULL) THEN
257 --
258 -- Only execute the validation if absolutely necessary:
259 -- a) During update, the structure column value or any
260 -- of the attribute values have actually changed.
261 -- b) During insert.
262 --
263 hr_dflex_utility.ins_or_upd_descflex_attribs
264 (p_appl_short_name => 'OTA'
265 ,p_descflex_name => 'OTA_TRAINING_PLAN_MEMBERS'
266 ,p_attribute_category => p_rec.attribute_category
267 ,p_attribute1_name => 'ATTRIBUTE1'
268 ,p_attribute1_value => p_rec.attribute1
269 ,p_attribute2_name => 'ATTRIBUTE2'
270 ,p_attribute2_value => p_rec.attribute2
271 ,p_attribute3_name => 'ATTRIBUTE3'
272 ,p_attribute3_value => p_rec.attribute3
273 ,p_attribute4_name => 'ATTRIBUTE4'
274 ,p_attribute4_value => p_rec.attribute4
275 ,p_attribute5_name => 'ATTRIBUTE5'
276 ,p_attribute5_value => p_rec.attribute5
277 ,p_attribute6_name => 'ATTRIBUTE6'
278 ,p_attribute6_value => p_rec.attribute6
279 ,p_attribute7_name => 'ATTRIBUTE7'
280 ,p_attribute7_value => p_rec.attribute7
281 ,p_attribute8_name => 'ATTRIBUTE8'
282 ,p_attribute8_value => p_rec.attribute8
283 ,p_attribute9_name => 'ATTRIBUTE9'
284 ,p_attribute9_value => p_rec.attribute9
285 ,p_attribute10_name => 'ATTRIBUTE10'
286 ,p_attribute10_value => p_rec.attribute10
287 ,p_attribute11_name => 'ATTRIBUTE11'
288 ,p_attribute11_value => p_rec.attribute11
289 ,p_attribute12_name => 'ATTRIBUTE12'
290 ,p_attribute12_value => p_rec.attribute12
291 ,p_attribute13_name => 'ATTRIBUTE13'
292 ,p_attribute13_value => p_rec.attribute13
293 ,p_attribute14_name => 'ATTRIBUTE14'
294 ,p_attribute14_value => p_rec.attribute14
295 ,p_attribute15_name => 'ATTRIBUTE15'
296 ,p_attribute15_value => p_rec.attribute15
297 ,p_attribute16_name => 'ATTRIBUTE16'
298 ,p_attribute16_value => p_rec.attribute16
299 ,p_attribute17_name => 'ATTRIBUTE17'
300 ,p_attribute17_value => p_rec.attribute17
301 ,p_attribute18_name => 'ATTRIBUTE18'
302 ,p_attribute18_value => p_rec.attribute18
303 ,p_attribute19_name => 'ATTRIBUTE19'
304 ,p_attribute19_value => p_rec.attribute19
305 ,p_attribute20_name => 'ATTRIBUTE20'
306 ,p_attribute20_value => p_rec.attribute20
307 ,p_attribute21_name => 'ATTRIBUTE21'
308 ,p_attribute21_value => p_rec.attribute21
309 ,p_attribute22_name => 'ATTRIBUTE22'
310 ,p_attribute22_value => p_rec.attribute22
314 ,p_attribute24_value => p_rec.attribute24
311 ,p_attribute23_name => 'ATTRIBUTE23'
312 ,p_attribute23_value => p_rec.attribute23
313 ,p_attribute24_name => 'ATTRIBUTE24'
315 ,p_attribute25_name => 'ATTRIBUTE25'
316 ,p_attribute25_value => p_rec.attribute25
317 ,p_attribute26_name => 'ATTRIBUTE26'
318 ,p_attribute26_value => p_rec.attribute26
319 ,p_attribute27_name => 'ATTRIBUTE27'
320 ,p_attribute27_value => p_rec.attribute27
321 ,p_attribute28_name => 'ATTRIBUTE28'
322 ,p_attribute28_value => p_rec.attribute28
323 ,p_attribute29_name => 'ATTRIBUTE29'
324 ,p_attribute29_value => p_rec.attribute29
325 ,p_attribute30_name => 'ATTRIBUTE30'
326 ,p_attribute30_value => p_rec.attribute30
327 );
328 END IF;
329 --
330 hr_utility.set_location(' Leaving:'||l_proc,20);
331 END chk_df;
332 --
333 -- ----------------------------------------------------------------------------
334 -- |-----------------------< chk_non_updateable_args >------------------------|
335 -- ----------------------------------------------------------------------------
336 -- {Start Of Comments}
337 --
338 -- Description:
339 -- This procedure is used to ensure that non updateable attributes have
340 -- not been updated. If an attribute has been updated an error is generated.
341 --
342 -- Pre Conditions:
343 -- g_old_rec has been populated with details of the values currently in
344 -- the database.
345 --
346 -- In Arguments:
347 -- p_rec has been populated with the updated values the user would like the
348 -- record set to.
349 --
350 -- Post Success:
351 -- Processing continues if all the non updateable attributes have not
352 -- changed.
353 --
354 -- Post Failure:
355 -- An application error is raised if any of the non updatable attributes
356 -- have been altered.
357 --
358 -- {End Of Comments}
359 -- ----------------------------------------------------------------------------
360 PROCEDURE chk_non_updateable_args
361 (p_effective_date IN date
362 ,p_rec IN ota_tpm_shd.g_rec_type
363 ) IS
364 --
365 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
366 l_error EXCEPTION;
367 l_argument varchar2(30);
368 --
369 BEGIN
370 --
371 -- Only proceed with the validation if a row exists for the current
372 -- record in the HR Schema.
373 --
374 IF NOT ota_tpm_shd.api_updating
375 (p_training_plan_member_id => p_rec.training_plan_member_id
376 ,p_object_version_number => p_rec.object_version_number
377 ) THEN
378 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
379 fnd_message.set_token('PROCEDURE ', l_proc);
380 fnd_message.set_token('STEP ', '5');
381 fnd_message.raise_error;
382 END IF;
383 --
384 hr_utility.set_location(' Step:'|| l_proc, 10);
385 IF NVL(p_rec.business_group_id, hr_api.g_number) <>
386 NVL(ota_tpm_shd.g_old_rec.business_group_id, hr_api.g_number) THEN
387 hr_api.argument_changed_error
388 (p_api_name => l_proc
389 ,p_argument => 'BUSINESS_GROUP_ID'
390 ,p_base_table => ota_tpm_shd.g_tab_nam);
391 END IF;
392 --
393 --
394 hr_utility.set_location(' Step:'|| l_proc, 20);
395 IF NVL(p_rec.training_plan_id, hr_api.g_number) <>
396 NVL(ota_tpm_shd.g_old_rec.training_plan_id, hr_api.g_number) THEN
397 hr_api.argument_changed_error
398 (p_api_name => l_proc
399 ,p_argument => 'TRAINING_PLAN_ID'
400 ,p_base_table => ota_tpm_shd.g_tab_nam);
401 END IF;
402 --
403
404 END chk_non_updateable_args;
405 --
406 -- ----------------------------------------------------------------------------
407 -- |---------------------------< insert_validate >----------------------------|
408 -- ----------------------------------------------------------------------------
409 PROCEDURE insert_validate
410 (p_effective_date IN date
411 ,p_rec IN ota_tpm_shd.g_rec_type
412 ) IS
413 --
414 l_proc varchar2(72) := g_package||'insert_validate';
415 l_person_id number;
416 l_personal_trng_plan boolean;
417 --
418 BEGIN
419 hr_utility.set_location('Entering:'||l_proc, 5);
420 --
421 -- VALIDATE IMPORTANT ATTRIBUTES
422 --
423 hr_api.validate_bus_grp_id(p_rec.business_group_id
424 ,p_associated_column1 => ota_tpm_shd.g_tab_nam ||'.BUSINESS_GROUP_ID'); -- Validate Bus Grp
425 --
426 hr_multi_message.end_validation_set;
427 --
428 --
429 hr_utility.set_location(' Step:'|| l_proc, 10);
430 ota_tpm_bus1.chk_training_plan_id(
431 p_training_plan_id => p_rec.training_plan_id
432 ,p_business_group_id => p_rec.business_group_id);
433 --
434
435 hr_utility.set_location(' Step:'|| l_proc, 20);
436 ota_tpm_bus1.chk_member_status_type_id(
437 p_effective_date => p_effective_date
438 ,p_member_status_type_id => p_rec.member_status_type_id
439 ,p_business_group_id => p_rec.business_group_id
443 ,p_activity_version_id => p_rec.activity_version_id
440 ,p_training_plan_member_id => p_rec.training_plan_member_id
441 ,p_object_version_number => p_rec.object_version_number
442 ,p_activity_definition_id => p_rec.activity_definition_id
444 ,p_training_plan_id => p_rec.training_plan_id
445 ,p_target_completion_date => p_rec.target_completion_date);
446 --
447
448 hr_utility.set_location(' Step:'|| l_proc, 30);
449 ota_tpm_bus1.chk_version_definition(
450 p_training_plan_member_id => p_rec.training_plan_member_id
451 ,p_object_version_number => p_rec.object_version_number
452 ,p_activity_definition_id => p_rec.activity_definition_id
453 ,p_activity_version_id => p_rec.activity_version_id
454 ,p_training_plan_id => p_rec.training_plan_id
455 ,p_business_group_id => p_rec.business_group_id);
456
457 --
458 hr_utility.set_location(' Step:'|| l_proc, 40);
459 ota_tpm_bus1.chk_activity_version_id (
460 p_training_plan_member_id => p_rec.training_plan_member_id
461 ,p_object_version_number => p_rec.object_version_number
462 ,p_activity_version_id => p_rec.activity_version_id
463 ,p_training_plan_id => p_rec.training_plan_id
464 ,p_business_group_id => p_rec.business_group_id);
465
466 --
467 -- Modified for Bug#3479186
468 -- l_person_id := ota_trng_plan_util_ss.get_person_id(p_training_plan_id => p_rec.training_plan_id);
469 l_personal_trng_plan := ota_trng_plan_util_ss.is_personal_trng_plan(p_training_plan_id => p_rec.training_plan_id);
470
471
472 -- IF l_person_id = 0 THEN
473 IF NOT l_personal_trng_plan THEN
474
475 hr_utility.set_location(' Step:'|| l_proc, 50);
476 ota_tpm_bus1.chk_activity_definition_id (
477 p_training_plan_member_id => p_rec.training_plan_member_id
478 ,p_object_version_number => p_rec.object_version_number
479 ,p_activity_definition_id => p_rec.activity_definition_id
480 ,p_business_group_id => p_rec.business_group_id);
481
482 hr_utility.set_location(' Step:'|| l_proc, 60);
483 ota_tpm_bus1.chk_unique(
484 p_training_plan_member_id => p_rec.training_plan_member_id
485 ,p_object_version_number => p_rec.object_version_number
486 ,p_activity_definition_id => p_rec.activity_definition_id
487 ,p_activity_version_id => p_rec.activity_version_id
488 ,p_training_plan_id => p_rec.training_plan_id);
489 ELSE
490 hr_utility.set_location(' Step:'|| l_proc, 70);
491 ota_tpm_bus1.chk_source_function(
492 p_training_plan_member_id => p_rec.training_plan_member_id
493 ,p_source_function => p_rec.source_function
494 ,p_effective_date => p_effective_date);
495
496 hr_utility.set_location(' Step:'|| l_proc, 80);
497 ota_tpm_bus1.chk_cancellation_reason(
498 p_training_plan_member_id => p_rec.training_plan_member_id
499 ,p_cancellation_reason => p_rec.cancellation_reason
500 ,p_effective_date => p_effective_date);
501
502 hr_utility.set_location(' Step:'|| l_proc, 90);
503 ota_tpm_bus1.chk_tpc_tp_actver_dates
504 (p_training_plan_id => p_rec.training_plan_id
505 ,p_training_plan_member_id => p_rec.training_plan_member_id
506 ,p_activity_version_id => p_rec.activity_version_id
507 ,p_earliest_start_date => p_rec.earliest_start_date
508 ,p_target_completion_date =>p_rec.target_completion_date
509 ,p_object_version_number =>p_rec.object_version_number);
510
511 hr_utility.set_location(' Step:'|| l_proc, 100);
512 ota_tpm_bus1.chk_unique1(
513 p_training_plan_member_id => p_rec.training_plan_member_id
514 ,p_object_version_number => p_rec.object_version_number
515 ,p_activity_definition_id => p_rec.activity_definition_id
516 ,p_activity_version_id => p_rec.activity_version_id
517 ,p_training_plan_id => p_rec.training_plan_id
518 ,p_target_completion_date => p_rec.target_completion_date);
519 --
520
521 END IF;
522
523
524
525 hr_utility.set_location(' Step:'|| l_proc, 70);
526
527 ota_tpm_bus.chk_df(p_rec);
528 --
529 hr_utility.set_location(' Leaving:'||l_proc, 70);
530 END insert_validate;
531 --
532 -- ----------------------------------------------------------------------------
533 -- |---------------------------< update_validate >----------------------------|
534 -- ----------------------------------------------------------------------------
535 PROCEDURE update_validate
536 (p_effective_date IN date
537 ,p_rec IN ota_tpm_shd.g_rec_type
538 ) IS
539 --
540 l_proc varchar2(72) := g_package||'update_validate';
541 l_person_id number;
542 l_personal_trng_plan BOOLEAN;
543
544 --
545 BEGIN
546 hr_utility.set_location('Entering:'||l_proc, 5);
547 --
548 -- VALIDATE IMPORTANT ATTRIBUTES
549 --
550 hr_api.validate_bus_grp_id(p_rec.business_group_id
551 ,p_associated_column1 => ota_tpm_shd.g_tab_nam ||'.BUSINESS_GROUP_ID'); -- Validate Bus Grp
552 --
556 (p_effective_date => p_effective_date
553 hr_multi_message.end_validation_set;
554
555 chk_non_updateable_args
557 ,p_rec => p_rec
558 );
559 --
560 hr_utility.set_location(' Step:'|| l_proc, 20);
561
562 -- Modified for Bug#3479186
563 -- l_person_id :=ota_trng_plan_util_ss.get_person_id(p_training_plan_id => p_rec.training_plan_id);
564 l_personal_trng_plan := ota_trng_plan_util_ss.is_personal_trng_plan(p_training_plan_id => p_rec.training_plan_id);
565
566
567 hr_utility.set_location(' Step:'|| l_proc, 10);
568 ota_tpm_bus1.chk_activity_definition_id (
569 p_training_plan_member_id => p_rec.training_plan_member_id
570 ,p_object_version_number => p_rec.object_version_number
571 ,p_activity_definition_id => p_rec.activity_definition_id
572 ,p_business_group_id => p_rec.business_group_id);
573 --
574 hr_utility.set_location(' Step:'|| l_proc, 20);
575 ota_tpm_bus1.chk_member_status_type_id(
576 p_effective_date => p_effective_date
577 ,p_member_status_type_id => p_rec.member_status_type_id
578 ,p_business_group_id => p_rec.business_group_id
579 ,p_training_plan_member_id => p_rec.training_plan_member_id
580 ,p_object_version_number => p_rec.object_version_number
581 ,p_activity_definition_id => p_rec.activity_definition_id
582 ,p_activity_version_id => p_rec.activity_version_id
583 ,p_training_plan_id => p_rec.training_plan_id
584 ,p_target_completion_date => p_rec.target_completion_date);
585 --
586 hr_utility.set_location(' Step:'|| l_proc, 30);
587 ota_tpm_bus1.chk_version_definition(
588 p_training_plan_member_id => p_rec.training_plan_member_id
589 ,p_object_version_number => p_rec.object_version_number
590 ,p_activity_definition_id => p_rec.activity_definition_id
591 ,p_activity_version_id => p_rec.activity_version_id
592 ,p_training_plan_id => p_rec.training_plan_id
593 ,p_business_group_id => p_rec.business_group_id);
594 --
595 hr_utility.set_location(' Step:'|| l_proc, 40);
596 ota_tpm_bus1.chk_activity_version_id (
597 p_training_plan_member_id => p_rec.training_plan_member_id
598 ,p_object_version_number => p_rec.object_version_number
599 ,p_activity_version_id => p_rec.activity_version_id
600 ,p_training_plan_id => p_rec.training_plan_id
601 ,p_business_group_id => p_rec.business_group_id);
602 --
603 -- Modified for Bug#3479186
604 -- IF l_person_id <> 0 THEN
605 IF l_personal_trng_plan THEN
606
607 hr_utility.set_location(' Step:'|| l_proc, 50);
608 ota_tpm_bus1.chk_source_function(
609 p_training_plan_member_id => p_rec.training_plan_member_id
610 ,p_source_function => p_rec.source_function
611 ,p_effective_date => p_effective_date);
612
613 hr_utility.set_location(' Step:'|| l_proc, 60);
614 ota_tpm_bus1.chk_cancellation_reason(
615 p_training_plan_member_id => p_rec.training_plan_member_id
616 ,p_cancellation_reason => p_rec.cancellation_reason
617 ,p_effective_date => p_effective_date);
618
619 hr_utility.set_location(' Step:'|| l_proc, 70);
620 ota_tpm_bus1.chk_tpc_tp_actver_dates
621 (p_training_plan_id => p_rec.training_plan_id
622 ,p_training_plan_member_id => p_rec.training_plan_member_id
623 ,p_activity_version_id => p_rec.activity_version_id
624 ,p_earliest_start_date => p_rec.earliest_start_date
625 ,p_target_completion_date =>p_rec.target_completion_date
626 ,p_object_version_number =>p_rec.object_version_number);
627
628 hr_utility.set_location(' Step:'|| l_proc, 80);
629 ota_tpm_bus1.chk_unique1(
630 p_training_plan_member_id => p_rec.training_plan_member_id
631 ,p_object_version_number => p_rec.object_version_number
632 ,p_activity_definition_id => p_rec.activity_definition_id
633 ,p_activity_version_id => p_rec.activity_version_id
634 ,p_training_plan_id => p_rec.training_plan_id
635 ,p_target_completion_date => p_rec.target_completion_date);
636 --
637 -- Modified for Bug#3479186
638 -- ELSIF l_person_id = 0 THEN
639 ELSE
640
641 hr_utility.set_location(' Step:'|| l_proc, 90);
642 ota_tpm_bus1.chk_unique(
643 p_training_plan_member_id => p_rec.training_plan_member_id
644 ,p_object_version_number => p_rec.object_version_number
645 ,p_activity_definition_id => p_rec.activity_definition_id
646 ,p_activity_version_id => p_rec.activity_version_id
647 ,p_training_plan_id => p_rec.training_plan_id);
648 -- Commented by rdola on 6/4/03
649 END IF;
650
651 hr_utility.set_location(' Step:'|| l_proc, 60);
652
653
654 ota_tpm_bus.chk_df(p_rec);
655 --
656 hr_utility.set_location(' Leaving:'||l_proc, 70);
657 END update_validate;
658 --
659 -- ----------------------------------------------------------------------------
660 -- |---------------------------< delete_validate >----------------------------|
661 -- ----------------------------------------------------------------------------
662 PROCEDURE delete_validate
663 (p_rec IN ota_tpm_shd.g_rec_type
664 ) IS
665 --
666 l_proc varchar2(72) := g_package||'delete_validate';
667 --
668 BEGIN
669 hr_utility.set_location('Entering:'||l_proc, 5);
670 --
671 -- Call all supporting business operations
672 --
673 hr_utility.set_location(' tpm is ', ota_tpm_shd.g_old_rec.training_plan_member_id);
674 hr_utility.set_location(' tpm is ', ota_tpm_shd.g_old_rec.training_plan_id);
675 ota_tpm_bus1.chk_delete(
676 p_training_plan_member_id => ota_tpm_shd.g_old_rec.training_plan_member_id
677 ,p_training_plan_id => ota_tpm_shd.g_old_rec.training_plan_id );
678 --
679 hr_utility.set_location(' Leaving:'||l_proc, 10);
680 END delete_validate;
681 --
682 END ota_tpm_bus;