[Home] [Help]
PACKAGE BODY: APPS.PER_OSV_BUS
Source
1 Package Body per_osv_bus as
2 /* $Header: peosvrhi.pkb 120.0 2005/05/31 12:37:30 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_osv_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_org_structure_version_id number default null;
15
16
17 --
18 -- ---------------------------------------------------------------------------
19 -- |----------------------< get_business_group_id >--------------------------|
20 -- ---------------------------------------------------------------------------
21 --
22 Procedure get_business_group_id
23 (p_organization_structure_id in number
24 ,p_business_group_id out nocopy number
25 ) is
26 l_proc varchar2(72) := g_package||'get_business_group_id';
27 --
28 begin
29 --
30 hr_utility.set_location('Entering:'|| l_proc, 10);
31 --
32 select business_group_id
33 into p_business_group_id
34 from per_organization_structures
35 where p_organization_structure_id = organization_structure_id;
36
37 --
38 hr_utility.set_location(' Leaving:'|| l_proc, 20);
39 --
40 exception
41 when no_data_found then
42 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
43 fnd_message.raise_error;
44 end get_business_group_id;
45
46
47 --
48 -- ---------------------------------------------------------------------------
49 -- |---------------------------< set_date_to >-------------------------------|
50 -- ---------------------------------------------------------------------------
51 --
52 Procedure set_date_to
53 (p_org_structure_version_id in number,
54 p_organization_structure_id in number,
55 p_date_from in date,
56 p_end_date_closedown_warning out nocopy boolean) is
57 l_proc VARCHAR2(72) := g_package||'set_date_to';
58 begin
59 --
60 -- Close down the open structure versions
61 --
62
63 update per_org_structure_versions osv
64 set osv.date_to = (p_date_from - 1)
65 where osv.organization_structure_id = p_organization_Structure_Id
66 and osv.date_to is null
67 and osv.org_structure_version_id
68 <> nvl(p_org_structure_version_id,-1);
69
70 p_end_date_closedown_warning := (sql%rowcount >0);
71
72
73
74 end;
75
76 -- ---------------------------------------------------------------------------
77 -- |----------------------------< chk_y_or_n>--------------------------------|
78 -- ---------------------------------------------------------------------------
79 --
80 Procedure chk_y_or_n
81 (p_effective_date in date
82 ,p_flag in varchar2
83 ,p_flag_name in varchar2)
84 IS
85 l_proc VARCHAR2(72) := g_package||'chk_sec_profile';
86 begin
87 hr_utility.set_location('Entering:'|| l_proc, 10);
88 --
89 --
90 IF hr_api.not_exists_in_hrstanlookups
91 (p_effective_date => p_effective_date
92 ,p_lookup_type => 'YES_NO'
93 ,p_lookup_code => p_flag
94 ) THEN
95 fnd_message.set_name('801','HR_52970_COL_Y_OR_N');
96 fnd_message.set_token('COLUMN',p_flag_name);
97 fnd_message.raise_error;
98 end if;
99 --
100 hr_utility.set_location('Leaving:'||l_proc, 30);
101 --
102 end chk_y_or_n;
103
104 --
105 -- ---------------------------------------------------------------------------
106 -- |------------------------< chk_version_number >---------------------------|
107 -- ---------------------------------------------------------------------------
108 --
109 Procedure chk_version_number
110 (p_org_structure_version_id in number,
111 p_version_number in number,
112 p_organization_structure_id in number
113 ) is
114 l_proc VARCHAR2(72) := g_package||'chk_version_number';
115 --
116 -- Declare cursor
117 --
118 cursor csr_org_version is
119 select org_structure_version_id,business_group_id
120 from per_org_structure_versions
121 where version_number = p_version_number
122 and nvl(p_org_structure_version_id,-1) <> org_structure_version_id
123 and p_organization_structure_id = organization_structure_id;
124 begin
125 --
126 hr_utility.set_location('Entering:'|| l_proc, 10);
127 --
128 --
129 hr_api.mandatory_arg_error
130 (p_api_name => l_proc
131 ,p_argument => 'p_version_number'
132 ,p_argument_value => p_version_number
133 );
134
135 --
136 --
137 hr_utility.set_location(l_proc, 20);
138 --
139 --
140 for Crec in csr_org_version loop
141 hr_utility.set_message(800, 'PER_7901_SYS_DUPLICATE_RECORDS');
142 hr_utility.raise_error;
143 end loop;
144 --
145 hr_utility.set_location('Leaving:'||l_proc, 30);
146 --
147 end chk_version_number;
148
149 -- ---------------------------------------------------------------------------
150 -- |----------------------< chk_top_node_pos_control >-----------------------|
151 -- ---------------------------------------------------------------------------
152 --
153 Procedure chk_top_node_pos_control
154 (p_topnode_pos_ctrl_enabled_f in varchar2,
155 p_organization_structure_id in varchar2
156 ) is
157 l_proc VARCHAR2(72) := g_package||'chk_top_node_pos_control';
158 --
159 -- Declare cursor
160 --
161 cursor csr_parent is
162 select count(*) as count
163 from per_organization_structures
164 where position_control_structure_flg = 'Y'
165 and p_organization_structure_id = organization_structure_id;
166 begin
167 --
168 hr_utility.set_location('Entering:'|| l_proc, 10);
169 --
170 --
171 --
172 --
173 hr_utility.set_location(l_proc, 20);
174 --
175 --
176 for Crec in csr_parent loop
177 if p_topnode_pos_ctrl_enabled_f = 'Y'
178 and Crec.count = 0 then
179 hr_utility.set_message(800, 'HR_6085_POS_ONE_PRIMARY');
180 hr_utility.raise_error;
181 end if;
182 end loop;
183 --
184 hr_utility.set_location('Leaving:'||l_proc, 30);
185 --
186 end chk_top_node_pos_control;
187
188 -- ---------------------------------------------------------------------------
189 -- |------------------------< chk_org_structure_id>--------------------------|
190 -- ---------------------------------------------------------------------------
191 --
192 Procedure chk_org_structure_id
193 (p_business_group_id in varchar2,
194 p_organization_structure_id in varchar2
195 ) is
196 l_proc VARCHAR2(72) := g_package||'chk_top_node_pos_control';
197 l_count number;
198 --
199 -- Declare cursor
200 --
201 begin
202 --
203 hr_utility.set_location('Entering:'|| l_proc, 10);
204 --
205 --
206
207 --
208 --
209 hr_utility.set_location(l_proc, 20);
210 --
211 --
212 select count(*) as count
213 into l_count
214 from per_organization_structures
215 where p_organization_structure_id = organization_structure_id;
216
217 if l_count =0 then
218 hr_utility.set_message(800, 'HR_51022_HR_INV_PRIMARY_KEY');
219 hr_utility.raise_error;
220 end if;
221 --
222 hr_utility.set_location('Leaving:'||l_proc, 30);
223 --
224 end chk_org_structure_id;
225
226 -- ---------------------------------------------------------------------------
227 -- |-----------------------------< chk_dates >-------------------------------|
228 -- ---------------------------------------------------------------------------
229 --
230 Procedure chk_dates
231 (p_org_structure_version_id in number,
232 p_date_from in date,
233 p_date_to in date,
234 p_organization_structure_id in number,
235 p_gap_warning out nocopy boolean
236 ) is
237 l_dummy VARCHAR2(5);
238 l_max_end_date DATE;
239 l_min_start_date DATE;
240 l_proc VARCHAR2(72) := g_package||'chk_dates';
241 begin
242 --
243 hr_utility.set_location('Entering:'|| l_proc, 10);
244 --
245 --
246 hr_api.mandatory_arg_error
247 (p_api_name => l_proc
248 ,p_argument => 'p_date_from'
249 ,p_argument_value => p_date_from
250 );
251
252 --
253 --
254 hr_utility.set_location(l_proc, 20);
255 --
256 -- Checks that date from is earlier than date to and neither
257 -- are before or after beginning/end of time
258 --
259
260 if p_date_from > p_date_to
261 or p_date_from < hr_api.g_sot
262 or p_date_from > hr_api.g_eot then
263 hr_utility.set_message('801','HR_6021_ALL_START_END_DATE');
264 hr_utility.raise_error;
265 end if;
266
267 -- The code below checks for a gap between different versions
268 --
269 --
270 --
271 p_gap_warning := FALSE;
272 select max(osv.date_to)
273 into l_max_end_date
274 from per_org_structure_versions osv
275 where osv.date_from < p_Date_From
276 and osv.organization_structure_id = p_organization_structure_id
277 and osv.org_structure_Version_id = nvl(p_org_structure_version_id,-1);
278 --
279 if (l_max_end_date is not null and p_Date_from = (l_max_end_date +1)
280 or (l_max_end_date is null)) then
281 select min(osv.date_from)
282 into l_min_start_date
283 from per_org_structure_versions osv
284 where osv.organization_structure_id = p_organization_structure_id
285 and osv.date_from > p_Date_To
286 and osv.org_structure_Version_id = nvl(p_org_structure_version_id,-1);
287 --
288 --
289 if l_min_start_date is not null and (p_Date_To +1) <> l_min_start_date then
290 p_gap_warning :=TRUE;
291 end if;
292 else
293 p_gap_warning := TRUE;
294 end if;
295 begin
296 --
297 -- Test for overlapping rows
298 --
299 select null
300 into l_dummy
301 from dual
302 where exists
303 (select 1
304 from per_org_structure_versions osv
305 where osv.date_from <= nvl(p_Date_To, hr_api.g_eot)
306 and nvl(osv.date_to,hr_api.g_eot) >= p_Date_From
307 and osv.organization_structure_id = p_organization_structure_id
308 and osv.org_structure_version_id
309 <> nvl(p_org_structure_version_id,-1));
310 --
311 hr_utility.set_message('801','HR_6076_PO_POS_OVERLAP');
312 hr_utility.raise_error;
313 --
314 end;
315 exception
316 when no_data_found then
317 null;
318
319 --
320 hr_utility.set_location('Leaving:'||l_proc, 30);
321 --
322 end chk_dates;
323 --
324 -- ---------------------------------------------------------------------------
325 -- |----------------------< set_security_group_id >--------------------------|
326 -- ---------------------------------------------------------------------------
327 --
328 Procedure set_security_group_id
329 (p_org_structure_version_id in number
330 ) is
331 --
332 -- Declare cursor
333 --
334 cursor csr_sec_grp is
335 select pbg.security_group_id
336 from per_business_groups pbg
337 , per_org_structure_versions osv
338 where osv.org_structure_version_id = p_org_structure_version_id
339 and pbg.business_group_id = osv.business_group_id;
340 --
341 -- Declare local variables
342 --
343 l_security_group_id number;
344 l_proc varchar2(72) := g_package||'set_security_group_id';
345 --
346 begin
347 --
348 hr_utility.set_location('Entering:'|| l_proc, 10);
349 --
350 -- Ensure that all the mandatory parameter are not null
351 --
352 hr_api.mandatory_arg_error
353 (p_api_name => l_proc
354 ,p_argument => 'org_structure_version_id'
355 ,p_argument_value => p_org_structure_version_id
356 );
357 --
358 open csr_sec_grp;
359 fetch csr_sec_grp into l_security_group_id;
360 --
361 if csr_sec_grp%notfound then
362 --
363 close csr_sec_grp;
364 --
365 -- The primary key is invalid therefore we must error
366 --
370 end if;
367 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
368 fnd_message.raise_error;
369 --
371 close csr_sec_grp;
372 --
373 -- Set the security_group_id in CLIENT_INFO
374 --
375 hr_api.set_security_group_id
376 (p_security_group_id => l_security_group_id
377 );
378 --
379 hr_utility.set_location(' Leaving:'|| l_proc, 20);
380 --
381 end set_security_group_id;
382 --
383 -- ---------------------------------------------------------------------------
384 -- |---------------------< return_legislation_code >-------------------------|
385 -- ---------------------------------------------------------------------------
386 --
387 Function return_legislation_code
388 (p_org_structure_version_id in number
389 )
390 Return Varchar2 Is
391 --
392 -- Declare cursor
393 --
394 cursor csr_leg_code is
395 select pbg.legislation_code
396 from per_business_groups pbg
397 , per_org_structure_versions osv
398 where osv.org_structure_version_id = p_org_structure_version_id
399 and pbg.business_group_id (+) = osv.business_group_id;
400 --
401 -- Declare local variables
402 --
403 l_legislation_code varchar2(150);
404 l_proc varchar2(72) := g_package||'return_legislation_code';
405 --
406 Begin
407 --
408 hr_utility.set_location('Entering:'|| l_proc, 10);
409 --
410 -- Ensure that all the mandatory parameter are not null
411 --
412 hr_api.mandatory_arg_error
413 (p_api_name => l_proc
414 ,p_argument => 'org_structure_version_id'
415 ,p_argument_value => p_org_structure_version_id
416 );
417 --
418 if ( nvl(per_osv_bus.g_org_structure_version_id, hr_api.g_number)
419 = p_org_structure_version_id) then
420 --
421 -- The legislation code has already been found with a previous
422 -- call to this function. Just return the value in the global
423 -- variable.
424 --
425 l_legislation_code := per_osv_bus.g_legislation_code;
426 hr_utility.set_location(l_proc, 20);
427 else
428 --
429 -- The ID is different to the last call to this function
430 -- or this is the first call to this function.
431 --
432 open csr_leg_code;
433 fetch csr_leg_code into l_legislation_code;
434 --
435 if csr_leg_code%notfound then
436 --
437 -- The primary key is invalid therefore we must error
438 --
439 close csr_leg_code;
440 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
441 fnd_message.raise_error;
442 end if;
443 hr_utility.set_location(l_proc,30);
444 --
445 -- Set the global variables so the values are
446 -- available for the next call to this function.
447 --
448 close csr_leg_code;
449 per_osv_bus.g_org_structure_version_id := p_org_structure_version_id;
450 per_osv_bus.g_legislation_code := l_legislation_code;
451 end if;
452 hr_utility.set_location(' Leaving:'|| l_proc, 40);
453 return l_legislation_code;
454 end return_legislation_code;
455 --
456 -- ----------------------------------------------------------------------------
457 -- |-----------------------< chk_non_updateable_args >------------------------|
458 -- ----------------------------------------------------------------------------
459 -- {Start Of Comments}
460 --
461 -- Description:
462 -- This procedure is used to ensure that non updateable attributes have
463 -- not been updated. If an attribute has been updated an error is generated.
464 --
465 -- Pre Conditions:
466 -- g_old_rec has been populated with details of the values currently in
467 -- the database.
468 --
469 -- In Arguments:
470 -- p_rec has been populated with the updated values the user would like the
471 -- record set to.
472 --
473 -- Post Success:
474 -- Processing continues if all the non updateable attributes have not
475 -- changed.
476 --
477 -- Post Failure:
478 -- An application error is raised if any of the non updatable attributes
479 -- have been altered.
480 --
481 -- {End Of Comments}
482 -- ----------------------------------------------------------------------------
483 Procedure chk_non_updateable_args
484 (p_effective_date in date
485 ,p_rec in per_osv_shd.g_rec_type
486 ) IS
487 --
488 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
489 l_error EXCEPTION;
490 l_argument varchar2(30);
491 --
492 Begin
493 --
494 -- Only proceed with the validation if a row exists for the current
495 -- record in the HR Schema.
496 --
497 IF NOT per_osv_shd.api_updating
498 (p_org_structure_version_id => p_rec.org_structure_version_id
499 ,p_object_version_number => p_rec.object_version_number
500 ) THEN
501 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
502 fnd_message.set_token('PROCEDURE ', l_proc);
503 fnd_message.set_token('STEP ', '5');
504 fnd_message.raise_error;
505 END IF;
506 --
507 -- EDIT_HERE: Add checks to ensure non-updateable args have
508 -- not been updated.
509 --
510 EXCEPTION
511 WHEN l_error THEN
512 hr_api.argument_changed_error
513 (p_api_name => l_proc
514 ,p_argument => l_argument);
515 WHEN OTHERS THEN
516 RAISE;
517 End chk_non_updateable_args;
518 --
519 -- ----------------------------------------------------------------------------
520 -- |---------------------------< insert_validate >----------------------------|
521 -- ----------------------------------------------------------------------------
522 Procedure insert_validate
523 (p_effective_date in date
524 ,p_rec in per_osv_shd.g_rec_type
525 ,p_gap_warning out nocopy boolean
526 ) is
527 --
528 l_proc varchar2(72) := g_package||'insert_validate';
529 --
530 Begin
531 hr_utility.set_location('Entering:'||l_proc, 5);
532 --
533 if p_rec.business_group_id is not null then
534 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
535 end if;
536
537 chk_version_number
538 (p_org_structure_version_id => p_rec.org_structure_version_id,
539 p_version_number => p_rec.version_number,
540 p_organization_structure_id => p_rec.organization_structure_id);
541
542 chk_y_or_n
543 (p_effective_date => p_effective_date
544 ,p_flag => nvl(p_rec.topnode_pos_ctrl_enabled_flag, 'N') --2929528
545 ,p_flag_name => 'topnode_pos_ctrl_enabled_f');
546
547 chk_top_node_pos_control
548 (p_topnode_pos_ctrl_enabled_f => p_rec.topnode_pos_ctrl_enabled_flag,
549 p_organization_structure_id => p_rec.organization_structure_id
550 );
551 chk_org_structure_id
552 (p_business_group_id => p_rec.business_group_id,
553 p_organization_structure_id => p_rec.organization_structure_id);
554
555 chk_dates
556 (p_org_structure_version_id => p_rec.org_structure_version_id,
557 p_date_from => p_rec.date_from,
558 p_date_to => p_rec.date_to,
559 p_organization_structure_id => p_rec.organization_structure_id,
560 p_gap_warning => p_gap_warning);
561
562 --
563 --
564 hr_utility.set_location(' Leaving:'||l_proc, 10);
565 End insert_validate;
566 --
567 -- ----------------------------------------------------------------------------
568 -- |---------------------------< update_validate >----------------------------|
569 -- ----------------------------------------------------------------------------
570 Procedure update_validate
571 (p_effective_date in date
572 ,p_rec in per_osv_shd.g_rec_type
573 ,p_gap_warning out nocopy boolean
574 ) is
575 --
576 l_proc varchar2(72) := g_package||'update_validate';
577 --
578 Begin
579 hr_utility.set_location('Entering:'||l_proc, 5);
580 --
581 if p_rec.business_group_id is not null then
582 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
583 end if;
584 --
585 chk_version_number
586 (p_org_structure_version_id => p_rec.org_structure_version_id,
587 p_version_number => p_rec.version_number,
588 p_organization_structure_id => p_rec.organization_structure_id);
589
590 chk_y_or_n
591 (p_effective_date => p_effective_date
592 ,p_flag => nvl(p_rec.topnode_pos_ctrl_enabled_flag, 'N') --2929528
593 ,p_flag_name => 'topnode_pos_ctrl_enabled_f');
594
595 chk_top_node_pos_control
596 (p_topnode_pos_ctrl_enabled_f => p_rec.topnode_pos_ctrl_enabled_flag,
597 p_organization_structure_id => p_rec.organization_structure_id
598 );
599
600 chk_dates
601 (p_org_structure_version_id => p_rec.org_structure_version_id,
602 p_date_from => p_rec.date_from,
603 p_date_to => p_rec.date_to,
604 p_organization_structure_id => p_rec.organization_structure_id,
605 p_gap_warning => p_gap_warning);
606
607 chk_non_updateable_args
608 (p_effective_date => p_effective_date
609 ,p_rec => p_rec
610 );
611 --
612 --
613 hr_utility.set_location(' Leaving:'||l_proc, 10);
614 End update_validate;
615 --
616 -- ----------------------------------------------------------------------------
617 -- |---------------------------< delete_validate >----------------------------|
618 -- ----------------------------------------------------------------------------
619 Procedure delete_validate
620 (p_rec in per_osv_shd.g_rec_type
621 ) is
622 --
623 l_proc varchar2(72) := g_package||'delete_validate';
624 --
625 Begin
626 hr_utility.set_location('Entering:'||l_proc, 5);
627 --
628 -- Call all supporting business operations
629 --
630 hr_utility.set_location(' Leaving:'||l_proc, 10);
631 End delete_validate;
632 --
633 end per_osv_bus;