[Home] [Help]
PACKAGE BODY: APPS.PER_CAG_BUS
Source
1 Package Body per_cag_bus as
2 /* $Header: pecagrhi.pkb 120.1 2006/10/18 08:42:10 grreddy noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_cag_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be used by the
11 -- return_legislation_code function.
12 --
13 g_collective_agreement_id number default null;
14 g_legislation_code varchar2(150) default null;
15 --
16 -- ----------------------------------------------------------------------------
17 -- |-----------------------< chk_non_updateable_args >------------------------|
18 -- ----------------------------------------------------------------------------
19 -- {Start Of Comments}
20 --
21 -- Description:
22 -- This procedure is used to ensure that non updateable attributes have
23 -- not been updated. If an attribute has been updated an error is generated.
24 --
25 -- Pre Conditions:
26 -- g_old_rec has been populated with details of the values currently in
27 -- the database.
28 --
29 -- In Arguments:
30 -- p_rec has been populated with the updated values the user would like the
31 -- record set to.
32 --
33 -- Post Success:
34 -- Processing continues if all the non updateable attributes have not
35 -- changed.
36 --
37 -- Post Failure:
38 -- An application error is raised if any of the non updatable attributes
39 -- have been altered.
40 --
41 -- {End Of Comments}
42 -- ----------------------------------------------------------------------------
43 Procedure chk_non_updateable_args
44 (p_rec in per_cag_shd.g_rec_type) IS
45 --
46 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
47 l_error EXCEPTION;
48 l_argument varchar2(30);
49 --
50 Begin
51 --
52 -- Only proceed with the validation if a row exists for the current
53 -- record in the HR Schema.
54 --
55 IF NOT per_cag_shd.api_updating
56 (p_collective_agreement_id => p_rec.collective_agreement_id
57 ,p_object_version_number => p_rec.object_version_number
58 ) THEN
59 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
60 fnd_message.set_token('PROCEDURE ', l_proc);
61 fnd_message.set_token('STEP ', '5');
62 fnd_message.raise_error;
63 END IF;
64 --
65 IF nvl(p_rec.start_Date, hr_api.g_date) <>
66 nvl(per_cag_shd.g_old_rec.start_date,hr_api.g_date) THEN
67 --
68 l_argument := 'start_date';
69 RAISE l_error;
70 --
71 END IF;
72
73 EXCEPTION
74 WHEN l_error THEN
75 hr_api.argument_changed_error
76 (p_api_name => l_proc
77 ,p_argument => l_argument);
78 WHEN OTHERS THEN
79 RAISE;
80 End chk_non_updateable_args;
81 --
82 -- ---------------------------------------------------------------------------
83 -- |---------------------------< chk_date_validate >--------------------------|
84 -- ---------------------------------------------------------------------------
85 Procedure chk_date_validate
86 (p_start_date in date,
87 p_end_date in date
88 ) is
89 --
90 l_proc varchar2(72) := g_package||'chk_date_validate';
91 l_temp varchar2(80) := '';
92 --
93 Begin
94 hr_utility.set_location('Entering:'||l_proc, 5);
95 --
96 -- Call all supporting business operations
97 --
98 if (p_start_date > p_end_date) then
99 begin
100 hr_utility.set_message(800,'PER_52833_FR_CAG_INV_START');
101 hr_utility.raise_error;
102 end;
103 end if;
104 --
105 hr_utility.set_location(' Leaving:'||l_proc, 10);
106 End chk_date_validate;
107
108 -- ---------------------------------------------------------------------------
109 -- |---------------------------<chk_status_validate >------------------------|
110 -- ---------------------------------------------------------------------------
111 Procedure chk_status_validate
112 (p_status in varchar2
113 ) is
114
115 cursor csr_status is select 1
116 from hr_lookups
117 where lookup_type = 'CAGR_STATUS'
118 and lookup_code = p_status ;
119
120 --
121 l_proc varchar2(72) := g_package||'chk_status_validate';
122 l_temp varchar2(80) := '';
123 --
124 Begin
125 hr_utility.set_location('Entering:'||l_proc, 5);
126 --
127 -- Call all supporting business operations
128 --
129
130 begin
131 open csr_status;
132 fetch csr_status into l_temp;
133 if csr_status%notfound then
134 begin
135 close csr_status;
136 hr_utility.set_message(800,'PER_289273_CAG_INV_STATUS');
137 hr_utility.raise_error;
138 end;
139 end if;
140 close csr_status;
141 end;
142
143 hr_utility.set_location(' Leaving:'||l_proc, 10);
144 End chk_status_validate;
145 --
146 -- ---------------------------------------------------------------------------
147 -- |---------------------------< chk_mandatory_date >------------------------|
148 -- ---------------------------------------------------------------------------
149 Procedure chk_mandatory_date
150 (p_start_date in date,
151 p_end_date in date
152 ) is
153 --
154 l_proc varchar2(72) := g_package||'chk_mandatory_date';
155 l_temp varchar2(80) := '';
156 --
157 Begin
158 hr_utility.set_location('Entering:'||l_proc, 5);
159 --
160 -- Call all supporting business operations
161 --
162
163 if ((to_char(p_end_date) is NOT NULL) and (to_char(p_start_date) is NULL)) then
164 begin
165 hr_utility.set_message(800,'PER_52834_FR_CAG_MAN_DATE');
166 hr_utility.raise_error;
167 end;
168 end if;
169
170
171 hr_utility.set_location(' Leaving:'||l_proc, 10);
172 End chk_mandatory_date;
173 --
174 -- ---------------------------------------------------------------------------
175 -- |-------------< chk_employer_organization_id >----------------------------|
176 -- ---------------------------------------------------------------------------
177 Procedure chk_employer_organization_id
178 (p_collective_agreement_id in number,
179 p_employer_organization_id in number,
180 p_business_group_id in number
181 )
182 is
183 --
184 cursor csr_employers is select '1'
185 from hr_employers_v hev
186 where hev.organization_id = p_employer_organization_id AND
187 hev.business_group_id = p_business_group_id;
188
189 l_proc varchar2(72) := g_package||'chk_employer_organization_id';
190 l_temp_organization_id number;
191 l_dummy varchar2(1);
192 --
193 --
194 Begin
195
196 hr_utility.set_location('Entering:'||l_proc, 5);
197 --
198 -- Call all supporting business operations
199 --
200
201 /* The validation check for the EMPLOYER_ORGANIZATION_ID will be done (if the field is set) against the HR_FR_EMPLOYERS_V view */
202 /* for each insert or in update only if the employer_organization_id value is different */
203
204 if (p_collective_agreement_id is null and p_employer_organization_id IS NOT NULL) OR
205 (p_collective_agreement_id is NOT null AND
206 per_cag_shd.g_old_rec.employer_organization_id <> p_employer_organization_id) Then
207 begin
208 open csr_employers;
209 fetch csr_employers into l_dummy;
210 if csr_employers%notfound then
211 begin
212 close csr_employers;
213 hr_utility.set_message(800,'PER_52846_CAG_INV_EMP_ORG');
214 hr_utility.raise_error;
215 end;
216 end if;
217 close csr_employers;
218 end;
219 end if;
220
221 hr_utility.set_location(' Leaving:'||l_proc, 10);
222 End chk_employer_organization_id;
223 --
224 --
225 -- ---------------------------------------------------------------------------
226 -- |---------------chk_bargaining_organization_id >----------------------------|
227 -- ---------------------------------------------------------------------------
228 Procedure chk_bargaining_organization_id
229 (p_collective_agreement_id in number,
230 p_bargaining_organization_id in number,
231 p_business_group_id in number
232 )
233 is
234 --
235 cursor csr_bargaining_units is select '1'
236 from hr_bargaining_units_v hbu
237 where hbu.organization_id = p_bargaining_organization_id AND
238 hbu.business_group_id = p_business_group_id;
239
240 l_proc varchar2(72) := g_package||'chk_bargaining_organization_id';
241 l_temp_organization_id number;
242 l_dummy varchar2(1);
243 --
244 --
245 Begin
246
247 hr_utility.set_location('Entering:'||l_proc, 5);
248 --
249 -- Call all supporting business operations
250 --
251
252 /* The validation check for the BARGAINING_ORGANIZATION_ID will be done (if a value is given) against the HR_FR_BARGAINING_UNITS_V view */
253 /* for each insert or in update only if the employer_organization_id value is different */
254 if (p_collective_agreement_id is null and p_bargaining_organization_id IS NOT NULL) OR
255 (p_collective_agreement_id is NOT null AND
256 per_cag_shd.g_old_rec.bargaining_organization_id <> p_bargaining_organization_id) Then
257 begin
258 open csr_bargaining_units;
259 fetch csr_bargaining_units into l_dummy;
260 if csr_bargaining_units%notfound then
261 begin
262 close csr_bargaining_units;
263 hr_utility.set_message(800,'PER_52847_CAG_INV_BARG_ORG');
264 hr_utility.raise_error;
265 end;
266 end if;
267 close csr_bargaining_units;
268 end;
269 end if;
270
271 hr_utility.set_location(' Leaving:'||l_proc, 10);
272 End chk_bargaining_organization_id;
273 --
274 -- -----------------------------------------------------------------------
275 -- |------------------------------< chk_df >-----------------------------|
276 -- -----------------------------------------------------------------------
277 --
278 -- Description:
279 -- Validates the all Descriptive Flexfield values.
280 --
281 -- Pre-conditions:
282 -- All other columns have been validated. Must be called as the
283 -- last step from insert_validate and update_validate.
284 --
285 -- In Arguments:
286 -- p_rec
287 --
288 -- Post Success:
289 -- If the Descriptive Flexfield structure column and data values are
290 -- all valid this procedure will end normally and processing will
291 -- continue.
292 --
293 -- Post Failure:
294 -- If the Descriptive Flexfield structure column value or any of
295 -- the data values are invalid then an application error is raised as
296 -- a PL/SQL exception.
297 --
298 -- Access Status:
299 -- Internal Row Handler Use Only.
300 --
301 procedure chk_df
302 (p_rec in per_cag_shd.g_rec_type) is
303 --
304 l_proc varchar2(72) := g_package||'chk_df';
305 --
306 begin
307 hr_utility.set_location('Entering:'||l_proc, 10);
308 --
309 if ((p_rec.collective_agreement_id is NULL) and (
310 nvl(per_cag_shd.g_old_rec.attribute_category, hr_api.g_varchar2)<>
311 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
312 nvl(per_cag_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
313 nvl(p_rec.attribute1, hr_api.g_varchar2) or
314 nvl(per_cag_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
315 nvl(p_rec.attribute2, hr_api.g_varchar2) or
316 nvl(per_cag_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
317 nvl(p_rec.attribute3, hr_api.g_varchar2) or
318 nvl(per_cag_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
319 nvl(p_rec.attribute4, hr_api.g_varchar2) or
320 nvl(per_cag_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
321 nvl(p_rec.attribute5, hr_api.g_varchar2) or
322 nvl(per_cag_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
323 nvl(p_rec.attribute6, hr_api.g_varchar2) or
324 nvl(per_cag_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
325 nvl(p_rec.attribute7, hr_api.g_varchar2) or
326 nvl(per_cag_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
327 nvl(p_rec.attribute8, hr_api.g_varchar2) or
328 nvl(per_cag_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
329 nvl(p_rec.attribute9, hr_api.g_varchar2) or
330 nvl(per_cag_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
331 nvl(p_rec.attribute10, hr_api.g_varchar2) or
332 nvl(per_cag_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
333 nvl(p_rec.attribute11, hr_api.g_varchar2) or
334 nvl(per_cag_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
335 nvl(p_rec.attribute12, hr_api.g_varchar2) or
336 nvl(per_cag_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
337 nvl(p_rec.attribute13, hr_api.g_varchar2) or
338 nvl(per_cag_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
339 nvl(p_rec.attribute14, hr_api.g_varchar2) or
340 nvl(per_cag_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
341 nvl(p_rec.attribute15, hr_api.g_varchar2) or
342 nvl(per_cag_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
343 nvl(p_rec.attribute16, hr_api.g_varchar2) or
344 nvl(per_cag_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
345 nvl(p_rec.attribute17, hr_api.g_varchar2) or
346 nvl(per_cag_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
347 nvl(p_rec.attribute18, hr_api.g_varchar2) or
348 nvl(per_cag_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
349 nvl(p_rec.attribute19, hr_api.g_varchar2) or
350 nvl(per_cag_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
351 nvl(p_rec.attribute20, hr_api.g_varchar2)))
352 or (p_rec.collective_agreement_id is null) then
353 --
354 -- Only execute the validation if absolutely necessary:
355 -- a) During update, the structure column value or any
356 -- of the attribute values have actually changed.
357 -- b) During insert.
358 --
359 hr_dflex_utility.ins_or_upd_descflex_attribs
360 (p_appl_short_name => 'PER'
361 ,p_descflex_name => 'PER_COLLECTIVE_AGREEMENTS'
362 ,p_attribute_category => p_rec.attribute_category
363 ,p_attribute1_name => 'ATTRIBUTE1'
364 ,p_attribute1_value => p_rec.attribute1
365 ,p_attribute2_name => 'ATTRIBUTE2'
366 ,p_attribute2_value => p_rec.attribute2
367 ,p_attribute3_name => 'ATTRIBUTE3'
368 ,p_attribute3_value => p_rec.attribute3
369 ,p_attribute4_name => 'ATTRIBUTE4'
370 ,p_attribute4_value => p_rec.attribute4
371 ,p_attribute5_name => 'ATTRIBUTE5'
372 ,p_attribute5_value => p_rec.attribute5
373 ,p_attribute6_name => 'ATTRIBUTE6'
374 ,p_attribute6_value => p_rec.attribute6
375 ,p_attribute7_name => 'ATTRIBUTE7'
376 ,p_attribute7_value => p_rec.attribute7
377 ,p_attribute8_name => 'ATTRIBUTE8'
378 ,p_attribute8_value => p_rec.attribute8
379 ,p_attribute9_name => 'ATTRIBUTE9'
380 ,p_attribute9_value => p_rec.attribute9
381 ,p_attribute10_name => 'ATTRIBUTE10'
382 ,p_attribute10_value => p_rec.attribute10
383 ,p_attribute11_name => 'ATTRIBUTE11'
384 ,p_attribute11_value => p_rec.attribute11
385 ,p_attribute12_name => 'ATTRIBUTE12'
386 ,p_attribute12_value => p_rec.attribute12
387 ,p_attribute13_name => 'ATTRIBUTE13'
388 ,p_attribute13_value => p_rec.attribute13
389 ,p_attribute14_name => 'ATTRIBUTE14'
390 ,p_attribute14_value => p_rec.attribute14
391 ,p_attribute15_name => 'ATTRIBUTE15'
392 ,p_attribute15_value => p_rec.attribute15
393 ,p_attribute16_name => 'ATTRIBUTE16'
394 ,p_attribute16_value => p_rec.attribute16
395 ,p_attribute17_name => 'ATTRIBUTE17'
396 ,p_attribute17_value => p_rec.attribute17
397 ,p_attribute18_name => 'ATTRIBUTE18'
398 ,p_attribute18_value => p_rec.attribute18
399 ,p_attribute19_name => 'ATTRIBUTE19'
400 ,p_attribute19_value => p_rec.attribute19
401 ,p_attribute20_name => 'ATTRIBUTE20'
402 ,p_attribute20_value => p_rec.attribute20
403 );
404 end if;
405 --
406 hr_utility.set_location(' Leaving:'||l_proc, 20);
407
408 end chk_df;
409 --
410 -- -----------------------------------------------------------------------
411 -- |------------------------------< chk_ddf >----------------------------|
412 -- -----------------------------------------------------------------------
413 --
414 -- Description:
415 -- Validates the all Developer Descriptive Flexfield values.
416 --
417 -- Pre-conditions:
418 -- All other columns have been validated. Must be called as the
419 -- second last step from insert_validate and update_validate.
420 -- Before any Descriptive Flexfield (chk_df) calls.
421 --
422 -- In Arguments:
423 -- p_rec
424 --
425 -- Post Success:
426 -- If the Developer Descriptive Flexfield structure column and data
427 -- values are all valid this procedure will end normally and
428 -- processing will continue.
429 --
430 -- Post Failure:
431 -- If the DDF structure column value or any of the data values
432 -- are invalid then an application error is raised as
433 -- a PL/SQL exception.
434 --
435 -- Access Status:
436 -- Internal Row Handler Use Only.
437 --
438 -- {End Of Comments}
439 -- ----------------------------------------------------------------------------
440 --
441 procedure chk_ddf
442 (p_rec in per_cag_shd.g_rec_type) is
443 --
444 l_proc varchar2(72) := g_package||'chk_ddf';
445 l_error exception;
446 --
447 Begin
448 hr_utility.set_location('Entering:'||l_proc, 5);
449 --
450 -- Check if the row is being inserted or updated and a
451 -- value has changed
452 --
453 if (p_rec.collective_agreement_id is null)
454 or ((p_rec.collective_agreement_id is not null)
455 and
456 nvl(per_cag_shd.g_old_rec.cag_information_category, hr_api.g_varchar2) <>
457 nvl(p_rec.cag_information_category, hr_api.g_varchar2) or
458 nvl(per_cag_shd.g_old_rec.cag_information1, hr_api.g_varchar2) <>
459 nvl(p_rec.cag_information1, hr_api.g_varchar2) or
460 nvl(per_cag_shd.g_old_rec.cag_information2, hr_api.g_varchar2) <>
461 nvl(p_rec.cag_information2, hr_api.g_varchar2) or
462 nvl(per_cag_shd.g_old_rec.cag_information3, hr_api.g_varchar2) <>
463 nvl(p_rec.cag_information3, hr_api.g_varchar2) or
464 nvl(per_cag_shd.g_old_rec.cag_information4, hr_api.g_varchar2) <>
465 nvl(p_rec.cag_information4, hr_api.g_varchar2) or
466 nvl(per_cag_shd.g_old_rec.cag_information5, hr_api.g_varchar2) <>
467 nvl(p_rec.cag_information5, hr_api.g_varchar2) or
468 nvl(per_cag_shd.g_old_rec.cag_information6, hr_api.g_varchar2) <>
469 nvl(p_rec.cag_information6, hr_api.g_varchar2) or
470 nvl(per_cag_shd.g_old_rec.cag_information7, hr_api.g_varchar2) <>
471 nvl(p_rec.cag_information7, hr_api.g_varchar2) or
472 nvl(per_cag_shd.g_old_rec.cag_information8, hr_api.g_varchar2) <>
473 nvl(p_rec.cag_information8, hr_api.g_varchar2) or
474 nvl(per_cag_shd.g_old_rec.cag_information9, hr_api.g_varchar2) <>
475 nvl(p_rec.cag_information9, hr_api.g_varchar2) or
476 nvl(per_cag_shd.g_old_rec.cag_information10, hr_api.g_varchar2) <>
477 nvl(p_rec.cag_information10, hr_api.g_varchar2) or
478 nvl(per_cag_shd.g_old_rec.cag_information11, hr_api.g_varchar2) <>
479 nvl(p_rec.cag_information11, hr_api.g_varchar2) or
480 nvl(per_cag_shd.g_old_rec.cag_information12, hr_api.g_varchar2) <>
481 nvl(p_rec.cag_information12, hr_api.g_varchar2) or
482 nvl(per_cag_shd.g_old_rec.cag_information13, hr_api.g_varchar2) <>
483 nvl(p_rec.cag_information13, hr_api.g_varchar2) or
484 nvl(per_cag_shd.g_old_rec.cag_information14, hr_api.g_varchar2) <>
485 nvl(p_rec.cag_information14, hr_api.g_varchar2) or
486 nvl(per_cag_shd.g_old_rec.cag_information15, hr_api.g_varchar2) <>
487 nvl(p_rec.cag_information15, hr_api.g_varchar2) or
488 nvl(per_cag_shd.g_old_rec.cag_information16, hr_api.g_varchar2) <>
489 nvl(p_rec.cag_information16, hr_api.g_varchar2) or
490 nvl(per_cag_shd.g_old_rec.cag_information17, hr_api.g_varchar2) <>
491 nvl(p_rec.cag_information17, hr_api.g_varchar2) or
492 nvl(per_cag_shd.g_old_rec.cag_information18, hr_api.g_varchar2) <>
493 nvl(p_rec.cag_information18, hr_api.g_varchar2) or
494 nvl(per_cag_shd.g_old_rec.cag_information19, hr_api.g_varchar2) <>
495 nvl(p_rec.cag_information19, hr_api.g_varchar2) or
496 nvl(per_cag_shd.g_old_rec.cag_information20, hr_api.g_varchar2) <>
497 nvl(p_rec.cag_information20, hr_api.g_varchar2))
498 then
499 --
500 hr_dflex_utility.ins_or_upd_descflex_attribs
501 (p_appl_short_name => 'PER'
502 ,p_descflex_name => 'Coll Agr Developer DF'
503 ,p_attribute_category => p_rec.cag_information_category
504 ,p_attribute1_name => 'CAG_INFORMATION1'
505 ,p_attribute1_value => p_rec.cag_information1
506 ,p_attribute2_name => 'CAG_INFORMATION2'
507 ,p_attribute2_value => p_rec.cag_information2
508 ,p_attribute3_name => 'CAG_INFORMATION3'
509 ,p_attribute3_value => p_rec.cag_information3
510 ,p_attribute4_name => 'CAG_INFORMATION4'
511 ,p_attribute4_value => p_rec.cag_information4
512 ,p_attribute5_name => 'CAG_INFORMATION5'
513 ,p_attribute5_value => p_rec.cag_information5
514 ,p_attribute6_name => 'CAG_INFORMATION6'
515 ,p_attribute6_value => p_rec.cag_information6
516 ,p_attribute7_name => 'CAG_INFORMATION7'
517 ,p_attribute7_value => p_rec.cag_information7
518 ,p_attribute8_name => 'CAG_INFORMATION8'
519 ,p_attribute8_value => p_rec.cag_information8
520 ,p_attribute9_name => 'CAG_INFORMATION9'
521 ,p_attribute9_value => p_rec.cag_information9
522 ,p_attribute10_name => 'CAG_INFORMATION10'
523 ,p_attribute10_value => p_rec.cag_information10
524 ,p_attribute11_name => 'CAG_INFORMATION11'
525 ,p_attribute11_value => p_rec.cag_information11
526 ,p_attribute12_name => 'CAG_INFORMATION12'
527 ,p_attribute12_value => p_rec.cag_information12
528 ,p_attribute13_name => 'CAG_INFORMATION13'
529 ,p_attribute13_value => p_rec.cag_information13
530 ,p_attribute14_name => 'CAG_INFORMATION14'
531 ,p_attribute14_value => p_rec.cag_information14
532 ,p_attribute15_name => 'CAG_INFORMATION15'
533 ,p_attribute15_value => p_rec.cag_information15
534 ,p_attribute16_name => 'CAG_INFORMATION16'
535 ,p_attribute16_value => p_rec.cag_information16
536 ,p_attribute17_name => 'CAG_INFORMATION17'
537 ,p_attribute17_value => p_rec.cag_information17
538 ,p_attribute18_name => 'CAG_INFORMATION18'
539 ,p_attribute18_value => p_rec.cag_information18
540 ,p_attribute19_name => 'CAG_INFORMATION19'
541 ,p_attribute19_value => p_rec.cag_information19
542 ,p_attribute20_name => 'CAG_INFORMATION20'
543 ,p_attribute20_value => p_rec.cag_information20
544 );
545 --
546 end if;
547 --
548 hr_utility.set_location(' Leaving:'||l_proc, 10);
549 end chk_ddf;
550 --
551
552 --
553 -- ----------------------------------------------------------------------------
554 -- |---------------------------< insert_validate >----------------------------|
555 -- ----------------------------------------------------------------------------
556 Procedure insert_validate(p_rec in per_cag_shd.g_rec_type) is
557 --
558 l_proc varchar2(72) := g_package||'insert_validate';
559 --
560 Begin
561 hr_utility.set_location('Entering:'||l_proc, 5);
562 --
563 -- Validate Business Group Id
564 --
565 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
566 --
567 --
568 -- Call all supporting business operations
569 --
570 -- Business Rule Mapping
571 -- =====================
572 -- CHK_MANDATORY_DATE
573 -- Check if dates have to be mandatory
574 chk_mandatory_date (p_rec.start_date, p_rec.end_date);
575
576 --
577 -- Business Rule Mapping
578 -- =====================
579 -- CHK_DATE_VALIDATE
580 -- If dates are provided, check that start_date is lower than end_date
581 chk_date_validate(p_rec.start_date, p_rec.end_date);
582
583 --
584 -- Business Rule Mapping
585 -- =====================
586 -- status_VALIDATE
587 -- Check that status is valid from hr_lookups
588 --
589 chk_status_validate(p_rec.status);
590
591
592
593 --
594 -- Business Rule Mapping (test done against a LOV into the form)
595 -- =====================
596 -- CHK_EMPLOYER_ORGANIZATION_ID
597 -- Check if the combination of employer_organization_id and business_id is valid against the HR_FR_EMPLOYERS_V view
598 chk_employer_organization_id(p_rec.collective_agreement_id,
599 p_rec.employer_organization_id,
600 p_rec.business_group_id);
601
602 --
603 -- Business Rule Mapping (test done against a LOV into the form)
604 -- =====================
605 -- CHK_BARGAINING_ORGANIZATION_ID
606 -- Check if the combination of bargaining_organization_id and business_id is valid against the HR_FR_BARGAINING_UNITS_V view
607 chk_bargaining_organization_id(p_rec.collective_agreement_id,
608 p_rec.bargaining_organization_id,
609 p_rec.business_group_id);
610
611 --
612 -- DDF procedure to validation Developer Descriptive Flexfields
613 -- =============================================================
614 -- CHK_DDF
615 --
616 per_cag_bus.chk_ddf(p_rec => p_rec);
617
618 --
619 -- Descriptive flexfield check
620 -- ===========================
621 --CHK_DF
622 --
623 per_cag_bus.chk_df(p_rec => p_rec);
624 --
625 --
626 hr_utility.set_location(' Leaving:'||l_proc, 10);
627 End insert_validate;
628 --
629 -- ----------------------------------------------------------------------------
630 -- |---------------------------< update_validate >----------------------------|
631 -- ----------------------------------------------------------------------------
632 Procedure update_validate(p_rec in per_cag_shd.g_rec_type) is
633 --
634 l_proc varchar2(72) := g_package||'update_validate';
635 --
636 Begin
637 hr_utility.set_location('Entering:'||l_proc, 5);
638 --
639 -- Call all supporting business operations
640 --
641 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
642 --
643 chk_non_updateable_args
644 (p_rec => p_rec);
645 --
646 --
647 --
648 -- Business Rule Mapping
649 -- =====================
650 -- CHK_MANDATORY_DATE
651 -- Check if dates have to be mandatory
652 chk_mandatory_date (p_rec.start_date, p_rec.end_date);
653
654 --
655 -- Business Rule Mapping
656 -- =====================
657 -- CHK_DATE_VALIDATE
658 -- If dates are provided, check that start_date is lower than end_date
659 chk_date_validate(p_rec.start_date, p_rec.end_date);
660
661 --
662 -- Business Rule Mapping
663 -- =====================
664 -- status_VALIDATE
665 -- Check that status is valid from hr_lookups
666 -- chk_status_validate(p_rec.status);
667
668
669 --
670 -- DDF procedure to validation Developer Descriptive Flexfields
671 -- =============================================================
672 -- CHK_DDF
673 --
674 per_cag_bus.chk_ddf(p_rec => p_rec);
675
676 --
677 -- Descriptive flexfield check
678 -- ===========================
679 --CHK_DF
680 --
681 per_cag_bus.chk_df(p_rec => p_rec);
682
683
684 hr_utility.set_location(' Leaving:'||l_proc, 10);
685 End update_validate;
686 --
687 -- ----------------------------------------------------------------------------
688 -- |---------------------------< delete_validate >----------------------------|
689 -- ----------------------------------------------------------------------------
690 Procedure delete_validate(p_rec in per_cag_shd.g_rec_type) is
691
692 cursor csr_assignment is select '1'
693 from per_all_assignments_f paa
694 where paa.collective_agreement_id = p_rec.collective_agreement_id;
695
696 cursor csr_establishment is select '1'
697 from hr_estab_coll_agrs_v hfe
698 where to_number(substr(hfe.collective_agreement_id,1,30)) = p_rec.collective_agreement_id;
699
700 cursor csr_grade is select '1'
701 from per_cagr_grade_structures pcg
702 where pcg.collective_agreement_id = p_rec.collective_agreement_id;
703
704 CURSOR csr_entitlement IS
705 SELECT 1
706 FROM per_cagr_entitlements pce
707 WHERE pce.collective_agreement_id = p_rec.collective_agreement_id;
708 --
709 l_dummy varchar2(1);
710 --
711 l_proc varchar2(72) := g_package||'delete_validate';
712 --
713 Begin
714
715 hr_utility.set_location('Entering:'||l_proc, 5);
716 --
717 -- If the collective agreements exits in per_all_assignments
718 --
719 open csr_assignment;
720 fetch csr_assignment into l_dummy;
721 if csr_assignment%found then
722 begin
723 close csr_assignment;
724 hr_utility.set_message(800,'PER_52838_CAG_DEL_ASG');
725 hr_utility.raise_error;
726 end;
727 end if;
728 close csr_assignment;
729 --
730 hr_utility.set_location(l_proc, 10);
731 --
732 -- If the collective agreements exits in hr_fr_estab_coll_agrs_v
733 --
734 open csr_establishment;
735 fetch csr_establishment into l_dummy;
736 if csr_establishment%found then
737 begin
738 close csr_establishment;
739 hr_utility.set_message(800,'PER_52839_CAG_DEL_EST');
740 hr_utility.raise_error;
741 end;
742 end if;
743 close csr_establishment;
744 --
745 hr_utility.set_location(l_proc, 20);
746 --
747 -- If the collective agreements exits in per_cagr_grade_structures
748 --
749 open csr_grade;
750 fetch csr_grade into l_dummy;
751 if csr_grade%found then
752 begin
753 close csr_grade;
754 hr_utility.set_message(800,'PER_52840_CAG_DEL_GRADE');
755 hr_utility.raise_error;
756 end;
757 end if;
758 close csr_grade;
759 --
760 hr_utility.set_location(l_proc,30);
761 --
762 OPEN csr_entitlement;
763 FETCH csr_entitlement INTO l_dummy;
764 --
765 IF csr_entitlement%FOUND THEN
766 --
767 CLOSE csr_entitlement;
768 --
769 hr_utility.set_message(800,'HR_289398_ENTITLEMENTS_EXIST');
770 hr_utility.raise_error;
771 --
772 ELSE
773 --
774 CLOSE csr_entitlement;
775 --
776 END IF;
777 --
778 hr_utility.set_location(' Leaving:'||l_proc, 999);
779 --
780 End delete_validate;
781 --
782 --
783 -- ---------------------------------------------------------------------------
784 -- |---------------------< return_legislation_code >-------------------------|
785 -- ---------------------------------------------------------------------------
786 --
787 function return_legislation_code
788 (p_collective_agreement_id in number
789 ) return varchar2 is
790 --
791 -- Declare cursor
792 --
793 cursor csr_leg_code is
794 select pbg.legislation_code
795 from per_business_groups pbg
796 , per_collective_agreements cag
797 where cag.collective_agreement_id = p_collective_agreement_id
798 and pbg.business_group_id = cag.business_group_id;
799 --
800 -- Declare local variables
801 --
802 l_legislation_code varchar2(150);
803 l_proc varchar2(72) := g_package||'return_legislation_code';
804 begin
805 hr_utility.set_location('Entering:'|| l_proc, 10);
806 --
807 -- Ensure that all the mandatory parameter are not null
808 --
809 hr_api.mandatory_arg_error(p_api_name => l_proc,
810 p_argument => 'collective_agreement_id',
811 p_argument_value => p_collective_agreement_id);
812 --
813 if nvl(g_collective_agreement_id, hr_api.g_number) = p_collective_agreement_id then
814 --
815 -- The legislation code has already been found with a previous
816 -- call to this function. Just return the value in the global
817 -- variable.
818 --
819 l_legislation_code := g_legislation_code;
820 hr_utility.set_location(l_proc, 20);
821 else
822 --
823 -- The ID is different to the last call to this function
824 -- or this is the first call to this function.
825 --
826 open csr_leg_code;
827 fetch csr_leg_code into l_legislation_code;
828 if csr_leg_code%notfound then
829 close csr_leg_code;
830 --
831 -- The primary key is invalid therefore we must error
832 --
833 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
834 hr_utility.raise_error;
835 end if;
836 --
837 close csr_leg_code;
838 hr_utility.set_location(' Leaving:'|| l_proc, 20);
839 --
840 g_collective_agreement_id := p_collective_agreement_id;
841 g_legislation_code := l_legislation_code;
842 end if;
843 hr_utility.set_location(' Leaving:'|| l_proc, 25);
844 --
845 return l_legislation_code;
846 end return_legislation_code;
847 --
848 -- ----------------------------------------------------------------------------
849 end per_cag_bus;