[Home] [Help]
PACKAGE BODY: APPS.PER_SSM_BUS
Source
1 Package Body per_ssm_bus as
2 /* $Header: pessmrhi.pkb 120.0 2005/05/31 21:50:48 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
11 --
8 g_package varchar2(33) := ' per_ssm_bus.'; -- Global package name
9 g_salary_survey_mapping_id number default null;
10 g_legislation_code varchar2(150) default null;
12 --
13 --
14 -- ----------------------------------------------------------------------------
15 -- |------------------------< return_legislation_code >-----------------------|
16 -- ----------------------------------------------------------------------------
17 Function return_legislation_code( p_salary_survey_mapping_id in number )
18 return varchar2 is
19 --
20 --
21 -- Cursor to find legislation code
22 --
23 cursor csr_leg_code is
24 select pbg.legislation_code
25 from per_business_groups pbg,
26 per_salary_survey_mappings ssm
27 where ssm.salary_survey_mapping_id = p_salary_survey_mapping_id
28 and pbg.business_group_id = ssm.business_group_id;
29 --
30 -- Declare local variables
31 --
32 l_legislation_code varchar2(150);
33 l_proc varchar2(72) := g_package||'return_legislation_code';
34 Begin
35 --
36 hr_utility.set_location('Entering:'||l_proc, 5);
37 hr_api.mandatory_arg_error(p_api_name => l_proc
38 ,p_argument => 'salary_survey_mapping_id'
39 ,p_argument_value => p_salary_survey_mapping_id
40 );
41 --
42 if nvl( g_salary_survey_mapping_id, hr_api.g_number ) = p_salary_survey_mapping_id
43 then
44 --
45 -- The legislation has already been found with a previous call to this
46 -- function. Just return the value in the global variable.
47 --
48 l_legislation_code := g_legislation_code;
49 hr_utility.set_location('Entering:'||l_proc, 6);
50 else
51 open csr_leg_code;
52 fetch csr_leg_code into l_legislation_code;
53 if csr_leg_code%notfound then
54 close csr_leg_code;
55 fnd_message.set_name('PER','PER_52479_SSM_INVL_SSM_ID');
56 fnd_message.raise_error;
57 end if;
58 hr_utility.set_location('Entering:'||l_proc, 7);
59 close csr_leg_code;
60 g_salary_survey_mapping_id := p_salary_survey_mapping_id;
61 g_legislation_code := l_legislation_code;
62 end if;
63 hr_utility.set_location(' Leaving:'||l_proc, 10);
64 --
65 return l_legislation_code;
66 End return_legislation_code;
67 --
68 --
69 -- ----------------------------------------------------------------------------
70 -- |----------------------< chk_non_updateable_args >-----------------------|
71 -- ----------------------------------------------------------------------------
72 --
73 Procedure chk_non_updateable_args(p_rec in per_ssm_shd.g_rec_type) is
74 --
75 l_proc varchar2(72) := g_package||'chk_non_updateable_args';
76 l_error exception;
77 l_argument varchar2(30);
78 --
79 Begin
80 hr_utility.set_location('Entering:'||l_proc, 5);
81 --
82 -- Only proceed with validation if a row exists for
83 -- the current record in the HR Schema
84 --
85 if not per_ssm_shd.api_updating
86 (p_salary_survey_mapping_id => p_rec.salary_survey_mapping_id
87 ,p_object_version_number => p_rec.object_version_number
88 ) then
89 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
90 hr_utility.set_message_token('PROCEDURE', l_proc);
91 hr_utility.set_message_token('STEP', '5');
92 end if;
93 --
94 hr_utility.set_location(l_proc, 6);
95 --
96 if nvl(p_rec.business_group_id, hr_api.g_number) <>
97 nvl(per_ssm_shd.g_old_rec.business_group_id, hr_api.g_number) then
98 l_argument := 'business_group_id';
99 raise l_error;
100 elsif nvl(p_rec.salary_survey_line_id, hr_api.g_number) <>
101 nvl(per_ssm_shd.g_old_rec.salary_survey_line_id, hr_api.g_number) then
102 l_argument := 'salary_survey_line_id';
103 raise l_error;
104 elsif nvl(p_rec.parent_id, hr_api.g_number) <>
105 nvl(per_ssm_shd.g_old_rec.parent_id, hr_api.g_number) then
106 l_argument := 'parent_id';
107 raise l_error;
108 elsif nvl(p_rec.parent_table_name, hr_api.g_varchar2) <>
109 nvl(per_ssm_shd.g_old_rec.parent_table_name, hr_api.g_varchar2) then
110 l_argument := 'parent_table_name';
111 raise l_error;
112 end if;
113 hr_utility.set_location(l_proc, 11);
114 --
115 exception
116 when l_error then
117 hr_api.argument_changed_error
118 (p_api_name => l_proc
119 ,p_argument => l_argument);
120 when others then
121 raise;
122 hr_utility.set_location(' Leaving:'||l_proc, 12);
123 end chk_non_updateable_args;
124 --
125 --
126 -- ----------------------------------------------------------------------------
127 -- |--------------------< get_salary_survey_line_start >----------------------|
128 -- ----------------------------------------------------------------------------
129 --
130 -- Description
131 -- This Procedure returns the start date of the salary survey line referenced.
132 --
133 -- Pre-Conditions
134 -- None.
135 --
136 -- In Parameters
137 -- p_salary_survey_line_id ID of FK column
138 --
139 -- Post Success
140 -- Processing continues If the foreign key exists in the referenced table.
141 --
142 -- Post Failure
146 -- Access Status
143 -- Processing stops and an error is raised If the foreign key does not
144 -- exist in the referenced table.
145 --
147 -- Internal row handler use only.
148 --
149 Function get_salary_survey_line_start
150 (p_salary_survey_line_id in number)
151 return date is
152 --
153 l_proc varchar2(72) := g_package||'get_salary_survey_line_start';
154 l_start_date date;
155 --
156 cursor csr_get_dates is
157 select start_date
158 from per_salary_survey_lines ssl
159 where ssl.salary_survey_line_id = p_salary_survey_line_id;
160 --
161 Begin
162 --
163 hr_utility.set_location('Entering:'||l_proc,5);
164 --
165 open csr_get_dates;
166 --
167 fetch csr_get_dates into l_start_date;
168 --
169 return l_start_date;
170 --
171 --
172 hr_utility.set_location('Leaving:'||l_proc,10);
173 --
174 End get_salary_survey_line_start;
175 --
176 -- ----------------------------------------------------------------------------
177 -- |---------------------< get_salary_survey_line_end >-----------------------|
178 -- ----------------------------------------------------------------------------
179 --
180 -- Description
181 -- This Procedure returns the end date of the salary survey line referenced.
182 --
183 -- Pre-Conditions
184 -- None.
185 --
186 -- In Parameters
187 -- p_salary_survey_line_id ID of FK column
188 --
189 -- Post Success
190 -- Processing continues If the foreign key exists in the referenced table.
191 --
192 -- Post Failure
193 -- Processing stops and an error is raised If the foreign key does not
194 -- exist in the referenced table.
195 --
196 -- Access Status
197 -- Internal row handler use only.
198 --
199 -- Processing stops and an error is raised If the foreign key does not
200 -- exist in the referenced table.
201 --
202 -- Access Status
203 -- Internal row handler use only.
204 --
205 Function get_salary_survey_line_end
206 (p_salary_survey_line_id in number)
207 return date is
208 --
209 l_proc varchar2(72) := g_package||'get_salary_survey_line_end';
210 l_end_date date;
211 --
212 cursor csr_get_dates is
213 select nvl(end_date,hr_api.g_eot)
214 from per_salary_survey_lines ssl
215 where ssl.salary_survey_line_id = p_salary_survey_line_id;
216 --
217 Begin
218 --
219 hr_utility.set_location('Entering:'||l_proc,5);
220 --
221 open csr_get_dates;
222 --
223 fetch csr_get_dates into l_end_date;
224 --
225 return l_end_date;
226 --
227 --
228 hr_utility.set_location('Leaving:'||l_proc,10);
229 --
230 End get_salary_survey_line_end;
231 --
232 -- ----------------------------------------------------------------------------
233 -- |---------------------< chk_salary_survey_line_id >------------------------|
234 -- ----------------------------------------------------------------------------
235 --
236 -- Description
237 -- This Procedure checks that a referenced foreign key actually exists
238 -- in the referenced table.
239 --
240 -- Pre-Conditions
241 -- None.
242 --
243 -- In Parameters
244 -- p_salary_survey_mapping_id PK
245 -- p_salary_survey_line_id ID of FK column
246 -- p_object_version_number object version number
247 --
248 -- Post Success
249 -- Processing continues If the foreign key exists in the referenced table.
250 --
251 -- Post Failure
252 -- Processing stops and an error is raised If the foreign key does not
253 -- exist in the referenced table.
254 --
255 -- Access Status
256 -- Internal row handler use only.
257 --
258 Procedure chk_salary_survey_line_id
259 (p_salary_survey_mapping_id in number,
260 p_salary_survey_line_id in number,
261 p_object_version_number in number) is
262 --
263 l_proc varchar2(72) := g_package||'chk_salary_survey_line_id';
264 l_api_updating boolean;
265 l_exists varchar2(1);
266 --
267 cursor csr_chk_survey_line_exists is
268 select 'Y'
269 from per_salary_survey_lines ssl
270 where ssl.salary_survey_line_id = p_salary_survey_line_id;
271 --
272 Begin
273 --
274 hr_utility.set_location('Entering:'||l_proc,5);
275 --
276 -- Check mandatory parameters have been set
277 --
278 --hr_api.mandatory_arg_error
279 -- (p_api_name => l_proc
280 -- ,p_argument => 'salary_survey_line_id'
281 -- ,p_argument_value => p_salary_survey_line_id
282 -- );
283 --
284 hr_utility.set_location(l_proc,6);
285 --
286 l_api_updating := per_ssm_shd.api_updating
287 (p_salary_survey_mapping_id => p_salary_survey_mapping_id,
288 p_object_version_number => p_object_version_number);
289 --
290 If not l_api_updating and p_salary_survey_line_id is null then
291 -- Mandatory column is null
292 --
293 fnd_message.set_name('PER','PER_52478_SSM_LINE_ID_NULL');
294 fnd_message.raise_error;
295 --
296 Elsif not l_api_updating and p_salary_survey_line_id is not null then
297 --
298 -- check If salary_survey_line_id value exists in
299 -- per_salary_survey_lines table
300 --
301 open csr_chk_survey_line_exists;
302 --
303 fetch csr_chk_survey_line_exists into l_exists;
304 --
305 If csr_chk_survey_line_exists%notfound Then
306 --
307 close csr_chk_survey_line_exists;
308 --
309 -- raise error as FK does not relate to PK in per_salary_survey_lines
310 -- table.
311 --
312 per_ssm_shd.constraint_error('PER_SALARY_SURVEY_MAPPINGS_FK1');
313 --
314 End If;
315 --
316 close csr_chk_survey_line_exists;
317 --
318 End If;
319 --
320 hr_utility.set_location('Leaving:'||l_proc,10);
321 --
322 End chk_salary_survey_line_id;
323 --
324
325 -- ----------------------------------------------------------------------------
326 -- |-----------------------------< chk_parent >-------------------------------|
327 -- ----------------------------------------------------------------------------
328 --
329 -- Description
330 -- This Procedure checks that a referenced foreign key actually exists
331 -- in the referenced table.
332 --
333 -- Pre-Conditions
334 -- None.
335 --
336 -- In Parameters
337 -- p_salary_survey_mapping_id PK
338 -- p_parent_id ID of FK column in table p_parent_table_name
339 -- p_parent_table_name is the name of the table for which parent_id is the PK
340 -- p_business_group_id is the business group ID of the salary survey mapping
341 -- p_object_version_number object version number
342 --
343 -- Post Success
344 -- Processing continues If the foreign key exists in the referenced table.
345 --
346 -- Post Failure
347 -- Processing stops and an error is raised If the foreign key does not
348 -- exist in the referenced table.
349 --
350 -- Access Status
351 -- Internal row handler use only.
352 --
353 Procedure chk_parent
354 (p_salary_survey_mapping_id in number,
355 p_parent_id in number,
356 p_parent_table_name in varchar2,
357 p_business_group_id in number,
358 p_object_version_number in number,
359 p_ssl_start_date in date,
360 p_ssl_end_date in date) is
361 --
362 l_proc varchar2(72) := g_package||'chk_parent';
363 l_api_updating boolean;
364 l_business_group_id per_business_groups.business_group_id%TYPE;
365 l_parent_start_date date;
366 l_parent_end_date date;
367 --
368 cursor csr_chk_job_id_exists is
369 select j.business_group_id, date_from, nvl(date_to,hr_api.g_eot)
370 from per_jobs j
371 where j.job_id = p_parent_id;
372 --
373 -- Changed 13-Oct-99 SCNair (per_positions to hr_positions_f) Date tracked position req
374 --
375 cursor csr_chk_position_id_exists is
376 select p.business_group_id,date_effective,
377 nvl(hr_general.get_position_date_end(p.position_id),hr_general.end_of_time)
378 from hr_positions_f p
379 where p.position_id = p_parent_id
380 and effective_end_date = hr_general.end_of_time;
381 --
382 -- Added as part of enhancement 4021737.
383 cursor csr_chk_assignment_id_exists is
384 select p.business_group_id,effective_start_date,
385 effective_end_date
386 from per_all_assignments_f p
387 where p.assignment_id = p_parent_id
388 and effective_end_date = hr_general.end_of_time;
389 --
390 Begin
391 --
392 hr_utility.set_location('Entering:'||l_proc,5);
393 --
394 -- Check mandatory parameters have been set
395 --
396 -- hr_api.mandatory_arg_error
397 -- (p_api_name => l_proc
398 -- ,p_argument => 'parent_id'
399 -- ,p_argument_value => p_parent_id
400 -- );
401 -- hr_api.mandatory_arg_error
402 -- (p_api_name => l_proc
403 -- ,p_argument => 'parent_table_name'
404 -- ,p_argument_value => p_parent_table_name
405 -- );
406 --
407 hr_utility.set_location(l_proc,6);
408 --
409 l_api_updating := per_ssm_shd.api_updating
410 (p_salary_survey_mapping_id => p_salary_survey_mapping_id,
411 p_object_version_number => p_object_version_number);
412 --
413 hr_utility.set_location(l_proc,7);
414 --
415 If l_api_updating and
416 (nvl(p_parent_id,hr_api.g_number)
417 <> nvl(per_ssm_shd.g_old_rec.parent_id,hr_api.g_number)
418 or nvl(p_parent_table_name,hr_api.g_varchar2)
419 <> nvl(per_ssm_shd.g_old_rec.parent_table_name,hr_api.g_varchar2)) then
420 --
421 hr_utility.set_location(l_proc,8);
422 --
423 -- Trying to update parent_id or parent_table_name.
424 fnd_message.set_name('PER','PER_52480_SSM_NON_UPD_FIELD');
425 fnd_message.raise_error;
426 Elsif not l_api_updating then
430 If p_parent_id is null then
427 --
428 hr_utility.set_location(l_proc,9);
429 --
431 --
432 hr_utility.set_location(l_proc,15);
433 fnd_message.set_name('PER','PER_52481_SSM_NO_PARENT_ID');
434 fnd_message.raise_error;
435 Elsif p_parent_table_name is null then
436 --
437 hr_utility.set_location(l_proc,20);
438 --
439 fnd_message.set_name('PER','PER_52482_SSM_NO_PRNT_TBL_NAME');
440 fnd_message.raise_error;
441 Else
442 --
443 hr_utility.set_location(l_proc,25);
444 --
445 If p_parent_table_name = 'PER_JOBS' then
446 --
447 hr_utility.set_location(l_proc,30);
448 --
449 open csr_chk_job_id_exists;
450 fetch csr_chk_job_id_exists into l_business_group_id,l_parent_start_date,l_parent_end_date;
451 If csr_chk_job_id_exists%notfound Then
452 --
453 hr_utility.set_location(l_proc,35);
454 --
455 close csr_chk_job_id_exists;
456 --
457 -- raise error as FK does not relate to PK in per_jobs
458 -- table.
459 --
460 fnd_message.set_name('PER','PER_52483_SSM_INVL_JOB_ID');
461 fnd_message.raise_error;
462 Elsif l_business_group_id <> p_business_group_id then
463 --
464 hr_utility.set_location(l_proc,40);
465 close csr_chk_job_id_exists;
466 fnd_message.set_name('PER','PER_52484_SSM_INVL_JOB_BG');
467 fnd_message.raise_error;
468 --
469 Elsif (p_ssl_start_date > l_parent_end_date) or
470 (p_ssl_end_date < l_parent_start_date) then
471 --
472 hr_utility.set_location(l_proc,42);
473 close csr_chk_job_id_exists;
474 fnd_message.set_name('PER','PER_52485_SSM_JOB_DATE_INVL');
475 fnd_message.raise_error;
476 Else
477 --
478 hr_utility.set_location(l_proc,45);
479 --
480 close csr_chk_job_id_exists;
481 End if;
482 --
483 Elsif p_parent_table_name = 'PER_POSITIONS' then
484 --
485 hr_utility.set_location(l_proc,50);
486 --
487 open csr_chk_position_id_exists;
488 --
489 fetch csr_chk_position_id_exists into l_business_group_id,l_parent_start_date,l_parent_end_date;
490 If csr_chk_position_id_exists%notfound Then
491 --
492 hr_utility.set_location(l_proc,55);
493 --
494 close csr_chk_position_id_exists;
495 --
496 -- raise error as FK does not relate to PK in per_jobs
497 -- table.
498 --
499 fnd_message.set_name('PER','PER_52486_SSM_INVL_POS_ID');
500 fnd_message.raise_error;
501 Elsif l_business_group_id <> p_business_group_id then
502 --
503 hr_utility.set_location(l_proc,60);
504 --
505 close csr_chk_position_id_exists;
506 fnd_message.set_name('PER','PER_52487_SSM_INVL_POS_BG');
507 fnd_message.raise_error;
508 --
509 Elsif (p_ssl_start_date > nvl(l_parent_end_date,hr_api.g_eot)) or
510 (nvl(p_ssl_end_date,hr_api.g_eot)< l_parent_start_date) then
511
512 --
513 hr_utility.set_location(l_proc,62);
514 hr_utility.set_location('SSL start '||to_char(p_ssl_start_date,'DD-MON-YYYY'),62);
515 hr_utility.set_location('SSL end '||to_char(p_ssl_end_date,'DD-MON-YYYY'),62);
516 hr_utility.set_location('parent start '||to_char(l_parent_start_date,'DD-MON-YYYY'),62);
517 hr_utility.set_location('parent end '||to_char(l_parent_end_date,'DD-MON-YYYY'),62);
518 close csr_chk_position_id_exists;
519 fnd_message.set_name('PER','PER_52488_SSM_POS_DATE_INVL');
520 fnd_message.raise_error;
521 Else
522 --
523 hr_utility.set_location(l_proc,65);
524 --
525 close csr_chk_position_id_exists;
526 End if;
527 Elsif p_parent_table_name = 'PER_ASSIGNMENTS' then
528 --
529 hr_utility.set_location(l_proc,50);
530 --
531 open csr_chk_assignment_id_exists;
532 --
533 fetch csr_chk_assignment_id_exists into l_business_group_id,l_parent_start_date,l_parent_end_date;
534 If csr_chk_assignment_id_exists%notfound Then
535 --
536 hr_utility.set_location(l_proc,55);
537 --
538 close csr_chk_assignment_id_exists;
539 --
540 -- raise error as FK does not relate to PK in per_jobs
541 -- table.
542 --
543 fnd_message.set_name('PER','PER_SSM_INVL_ASG_ID');
544 fnd_message.raise_error;
545 Elsif l_business_group_id <> p_business_group_id then
546 --
547 hr_utility.set_location(l_proc,60);
548 --
549 close csr_chk_assignment_id_exists;
550 fnd_message.set_name('PER','PER_SSM_INVL_ASG_BG');
551 fnd_message.raise_error;
552 --
553 Elsif (p_ssl_start_date > nvl(l_parent_end_date,hr_api.g_eot)) or
554 (nvl(p_ssl_end_date,hr_api.g_eot)< l_parent_start_date) then
555
556 --
557 hr_utility.set_location(l_proc,62);
558 hr_utility.set_location('SSL start '||to_char(p_ssl_start_date,'DD-MON-YYYY'),62);
559 hr_utility.set_location('SSL end '||to_char(p_ssl_end_date,'DD-MON-YYYY'),62);
560 hr_utility.set_location('parent start '||to_char(l_parent_start_date,'DD-MON-YYYY'),62);
561 hr_utility.set_location('parent end '||to_char(l_parent_end_date,'DD-MON-YYYY'),62);
562 close csr_chk_position_id_exists;
563 fnd_message.set_name('PER','PER_SSM_ASG_DATE_INVL');
564 fnd_message.raise_error;
565 Else
566 --
567 hr_utility.set_location(l_proc,65);
568 --
569 close csr_chk_assignment_id_exists;
570 End if;
571 Else
572
573 --
574 hr_utility.set_location(l_proc,70);
575 --
576 -- Invalid parent_table_name.
577 fnd_message.set_name('PER','PER_52489_SSM_INVL_TBL_NAME');
578 fnd_message.raise_error;
579 End If;
580 End If;
581 End If;
582 --
583 --
584 hr_utility.set_location('Leaving:'||l_proc,150);
585 --
586 End chk_parent;
587 --
588 --
589 --
590 -- ----------------------------------------------------------------------------
591 -- |---------------------------< chk_unique_key >-----------------------------|
592 -- ----------------------------------------------------------------------------
593 --
594 -- Description
595 -- This Procedure is used to check that salary_survey_line_id,
596 -- parent_table_name and the parent_table_id are in a unique combination
597 -- compared to other rows in the table per_salary_survey_mappings.
598 --
599 --
600 -- Pre-Conditions
601 -- None.
602 --
603 -- In Parameters
604 -- p_salary_survey_mapping_id PK
605 -- p_parent_id ID of FK column in table p_parent_table_name
606 -- p_parent_table_name is the name of the table for which parent_id is the PK
607 -- p_salary_survey_line_id is the salary survey line ID of the salary survey
608 -- mapping
609 -- p_object_version_number object version number
610 --
611 -- Post Success
612 -- Processing continues If the salary_survey_line_id, parent_table_name
613 -- and the parent_table_id are in a unique combination compared to
614 -- other rows in the table per_salary_survey_mappings.
615 --
616 -- Post Failure
617 -- Processing stops and an error is raised if the unique key validation
618 -- is breeched.
619 --
620 -- Access Status
621 -- Internal row handler use only.
622 --
623 Procedure chk_unique_key
624 (p_salary_survey_mapping_id in number,
625 p_parent_id in number,
626 p_parent_table_name in varchar2,
627 p_salary_survey_line_id in number,
628 p_grade_id in number,
629 p_location_id in number,
630 p_company_organization_id in number,
631 p_company_age_code in varchar2,
632 p_object_version_number in number) is
633 --
634 l_proc varchar2(72) := g_package||'chk_unique_key';
635 l_api_updating boolean;
636 l_exists varchar2(1);
637 --
638 cursor csr_chk_unique_key is
639 select 'Y'
640 from per_salary_survey_mappings ssm
641 where ssm.parent_id = p_parent_id
642 and ssm.parent_table_name = p_parent_table_name
643 and ssm.salary_survey_line_id = p_salary_survey_line_id
644 and ssm.grade_id = p_grade_id
645 and ssm.location_id = p_location_id
646 and ssm.company_organization_id = p_company_organization_id
647 and ssm.company_age_code = p_company_age_code;
648 --
649 --
650 Begin
651 --
652 hr_utility.set_location('Entering:'||l_proc,5);
653 --
654 -- Check mandatory parameters have been set
655 --
656 hr_utility.set_location(l_proc,6);
657 --
658 l_api_updating := per_ssm_shd.api_updating
659 (p_salary_survey_mapping_id => p_salary_survey_mapping_id,
660 p_object_version_number => p_object_version_number);
661 --
662 hr_utility.set_location(l_proc,8);
663 --
664 If not l_api_updating then
665 open csr_chk_unique_key;
666 fetch csr_chk_unique_key into l_exists;
667 If csr_chk_unique_key%found Then
668 close csr_chk_unique_key;
669 per_ssm_shd.constraint_error('PER_SALARY_SURVEY_MAPPINGS_UK');
670 Else
671 close csr_chk_unique_key;
672 End If;
673 End If;
674 --
675 --
676 hr_utility.set_location('Leaving:'||l_proc,10);
677 --
678 End chk_unique_key;
679 --
680 --
681 -- ----------------------------------------------------------------------------
682 -- |--------------------------< chk_location_id >-----------------------------|
683 -- ----------------------------------------------------------------------------
684 --
685 -- Description
686 -- This Procedure checks that a referenced foreign key actually exists
687 -- in the referenced table.
688 --
689 -- Pre-Conditions
690 -- None.
691 --
692 -- In Parameters
693 -- p_salary_survey_mapping_id PK
694 -- p_location_id ID of FK column
695 -- p_object_version_number object version number
696 --
697 -- Post Success
698 -- Processing continues If the foreign key exists in the referenced table.
699 --
700 -- Post Failure
701 -- Processing stops and an error is raised If the foreign key does not
702 -- exist in the referenced table.
703 --
704 -- Access Status
705 -- Internal row handler use only.
706 --
707 Procedure chk_location_id
708 (p_salary_survey_mapping_id in number,
709 p_location_id in number,
710 p_object_version_number in number,
711 p_ssl_start_date in date) is
712 --
713 l_proc varchar2(72) := g_package||'chk_location_id';
714 l_api_updating boolean;
715 l_loc_date_to date;
716 --
717 cursor csr_chk_location_exists is
718 select inactive_date
719 from hr_locations loc
720 where loc.location_id = p_location_id and loc.location_use = 'HR'
721 ;
722 --
723 Begin
724 --
725 hr_utility.set_location('Entering:'||l_proc,5);
726 --
727 -- Check mandatory parameters have been set
728 --
729 --
730 --
731 If p_location_id is not null then
732 open csr_chk_location_exists;
733 fetch csr_chk_location_exists into l_loc_date_to;
734 If csr_chk_location_exists%notfound then
735 --
736 hr_utility.set_location('Entering:'||l_proc,6);
737 --
738 close csr_chk_location_exists;
739 --
740 -- raise error as FK does not relate to PK in hr_locations
741 -- table.
742 --
743 per_ssm_shd.constraint_error('PER_SALARY_SURVEY_MAPPINGS_FK2');
744 --
745 Elsif l_loc_date_to < p_ssl_start_date then
746 --
747 close csr_chk_location_exists;
748 --
749 fnd_message.set_name('PER','PER_52490_SSM_LOC_DATE_INVL');
750 fnd_message.raise_error;
751 --
752 Else
753 --
754 hr_utility.set_location('Entering:'||l_proc,7);
755 --
756 close csr_chk_location_exists;
757 End If;
758 End if;
759 hr_utility.set_location('Leaving:'||l_proc,10);
760 --
761 End chk_location_id;
762 --
763 --
764 --
765 -- ----------------------------------------------------------------------------
766 -- |----------------------------< chk_grade_id >------------------------------|
767 -- ----------------------------------------------------------------------------
768 --
769 -- Description
770 -- This Procedure checks that a referenced foreign key actually exists
771 -- in the referenced table.
772 --
773 -- Pre-Conditions
774 -- None.
775 --
776 -- In Parameters
777 -- p_salary_survey_mapping_id PK
778 -- p_grade_id ID of FK column
779 -- p_business_group_id the business group ID of the salary survey mapping
780 -- p_object_version_number object version number
781 --
782 -- Post Success
783 -- Processing continues If the foreign key exists in the referenced table.
784 --
785 -- Post Failure
786 -- Processing stops and an error is raised If the foreign key does not
787 -- exist in the referenced table.
788 --
789 -- Access Status
790 -- Internal row handler use only.
791 --
792 Procedure chk_grade_id
793 (p_salary_survey_mapping_id in number,
794 p_grade_id in number,
795 p_business_group_id in number,
796 p_object_version_number in number,
797 p_ssl_start_date in date,
798 p_ssl_end_date in date) is
799 --
800 l_proc varchar2(72) := g_package||'chk_grade_id';
801 l_api_updating boolean;
802 l_exists varchar2(1);
803 l_grade_id number(15);
804 l_business_group_id number(15);
805 l_grade_date_from date;
806 l_grade_date_to date;
807 l_bg number(15);
808 --
809 cursor csr_chk_grade_exists is
810 select g.business_group_id, g.date_from, nvl(g.date_to,hr_api.g_eot)
811 from per_grades g
812 where g.grade_id = p_grade_id;
813 --
814 cursor csr_get_bg is
815 select business_group_id
816 from per_salary_survey_mappings
817 where salary_survey_mapping_id = p_salary_survey_mapping_id;
818 --
819 Begin
820 --
821 hr_utility.set_location('Entering:'||l_proc,5);
822 --
823 l_api_updating := per_ssm_shd.api_updating
824 (p_salary_survey_mapping_id => p_salary_survey_mapping_id
825 ,p_object_version_number => p_object_version_number
826 );
827 --
828 If l_api_updating then
829 open csr_get_bg;
830 fetch csr_get_bg into l_bg;
831 close csr_get_bg;
832 Else
833 l_bg := p_business_group_id;
834 End If;
835 If p_grade_id is not null then
836 open csr_chk_grade_exists;
837 fetch csr_chk_grade_exists into l_business_group_id, l_grade_date_from, l_grade_date_to;
838 If csr_chk_grade_exists%notfound then
839 close csr_chk_grade_exists;
840 --
841 -- raise error as FK does not relate to PK in per_grades
842 -- table.
843 --
844 per_ssm_shd.constraint_error('PER_SALARY_SURVEY_MAPPINGS_FK3');
845 --
846 -- Elsif l_business_group_id <> p_business_group_id then
847 Elsif l_business_group_id <> l_bg then
848 close csr_chk_grade_exists;
849 --
850 fnd_message.set_name('PER','PER_52491_SSM_INVL_GRD_BG');
851 fnd_message.raise_error;
852 Elsif (p_ssl_start_date > nvl(l_grade_date_to,hr_api.g_eot)
853 or nvl(p_ssl_end_date,hr_api.g_eot) < l_grade_date_from) then
854
855 close csr_chk_grade_exists;
856 --
857 fnd_message.set_name('PER','PER_52492_SSM_GRD_DATE_INVL');
858 fnd_message.raise_error;
859 --
860 Else
861 close csr_chk_grade_exists;
862 End If;
863 End If;
864 hr_utility.set_location('Leaving:'||l_proc,10);
865 --
866 End chk_grade_id;
867 --
868 --
869 --
870 -- ----------------------------------------------------------------------------
871 -- |--------------------< chk_company_organization_id >-----------------------|
872 -- ----------------------------------------------------------------------------
873 --
874 -- Description
875 -- This Procedure checks that a referenced foreign key actually exists
876 -- in the referenced table.
877 --
878 -- Pre-Conditions
879 -- None.
880 --
881 -- In Parameters
882 -- p_salary_survey_mapping_id PK
883 -- p_company_organization_id ID of FK column
884 -- p_business_group_id the business group ID of the salary survey mapping
885 -- p_object_version_number object version number
886 --
887 -- Post Success
888 -- Processing continues If the foreign key exists in the referenced table.
889 --
890 -- Post Failure
891 -- Processing stops and an error is raised If the foreign key does not
892 -- exist in the referenced table.
893 --
894 -- Access Status
895 -- Internal row handler use only.
896 --
897 Procedure chk_company_organization_id
898 (p_salary_survey_mapping_id in number,
899 p_company_organization_id in number,
900 p_business_group_id in number,
901 p_object_version_number in number,
902 p_ssl_start_date in date,
903 p_ssl_end_date in date) is
904 --
905 l_proc varchar2(72) := g_package||'chk_company_organization_id';
906 l_api_updating boolean;
907 l_business_group_id number;
908 l_org_date_from date;
909 l_org_date_to date;
910 l_bg number;
911 --
912 cursor csr_chk_company_org is
913 select o.business_group_id, o.date_from, nvl(o.date_to,hr_api.g_eot)
914 from hr_all_organization_units o
915 where o.organization_id = p_company_organization_id;
916 --
917 cursor csr_get_bg is
918 select business_group_id
919 from per_salary_survey_mappings
920 where salary_survey_mapping_id = p_salary_survey_mapping_id;
921 --
922 Begin
923 --
924 hr_utility.set_location('Entering:'||l_proc,5);
925 --
926 l_api_updating := per_ssm_shd.api_updating
927 (p_salary_survey_mapping_id => p_salary_survey_mapping_id
928 ,p_object_version_number => p_object_version_number
929 );
930 --
931 If l_api_updating then
932 open csr_get_bg;
933 fetch csr_get_bg into l_bg;
934 close csr_get_bg;
935 Else
936 l_bg := p_business_group_id;
937 End If;
938 --
939 If p_company_organization_id is not null then
940 hr_utility.set_location(l_proc,10);
941 open csr_chk_company_org;
942 fetch csr_chk_company_org into l_business_group_id, l_org_date_from, l_org_date_to;
943 If csr_chk_company_org%notfound then
944 hr_utility.set_location(l_proc,25);
945 close csr_chk_company_org;
946 --
947 -- raise error as FK does not relate to PK in hr_all_organization_units
948 -- table.
949 --
950 per_ssm_shd.constraint_error('PER_SALARY_SURVEY_MAPPINGS_FK5');
951 --
952 -- Elsif l_business_group_id <> p_business_group_id then
953 Elsif l_business_group_id <> l_bg then
954 hr_utility.set_location(l_proc,30);
955 close csr_chk_company_org;
956 --
957 fnd_message.set_name('PER','PER_52493_SSM_INVL_ORG_BG');
958 fnd_message.raise_error;
959 Elsif p_ssl_end_date < l_org_date_from
960 or p_ssl_start_date > l_org_date_to then
961 hr_utility.set_location(l_proc,32);
962 close csr_chk_company_org;
966 --
963 --
964 fnd_message.set_name('PER','PER_52494_SSM_ORG_DATE_INVL');
965 fnd_message.raise_error;
967 Else
968 hr_utility.set_location(l_proc,35);
969 close csr_chk_company_org;
970 End If;
971 End If;
972 hr_utility.set_location('Leaving:'||l_proc,50);
973 --
974 End chk_company_organization_id;
975 --
976 --
977 --
978 --
979 -- ----------------------------------------------------------------------------
980 -- |------------------------< chk_company_age_code >--------------------------|
981 -- ----------------------------------------------------------------------------
982 --
983 -- Description
984 -- This Procedure checks that a referenced foreign key actually exists
985 -- in the referenced table.
986 --
987 -- Pre-Conditions
988 -- None.
989 --
990 -- In Parameters
991 -- p_salary_survey_mapping_id PK
992 -- p_company_age_code code for lookup in hr_lookups
993 -- p_object_version_number object version number
994 --
995 -- Post Success
996 -- Processing continues If the lookup exists in the lookup table.
997 --
998 -- Post Failure
999 -- Processing stops and an error is raised If the lookup does not
1000 -- exist in the lookup table.
1001 --
1002 -- Access Status
1003 -- Internal row handler use only.
1004 --
1005 Procedure chk_company_age_code
1006 (p_salary_survey_mapping_id in number,
1007 p_company_age_code in varchar2,
1008 p_effective_date in date,
1009 p_object_version_number in number) is
1010 --
1011 l_proc varchar2(72) := g_package||'chk_company_age_code';
1012 l_api_updating boolean;
1013 l_exists varchar2(1);
1014 --
1015 cursor csr_chk_company_age_exists is
1016 select 'Y'
1017 from hr_lookups l
1018 where l.lookup_code = p_company_age_code
1019 and l.lookup_type = 'COMPANY_AGE'
1020 and l.enabled_flag = 'Y';
1021 --
1022 Begin
1023 --
1024 hr_utility.set_location('Entering:'||l_proc,5);
1025 --
1026 If p_company_age_code is not null then
1027 open csr_chk_company_age_exists;
1028 fetch csr_chk_company_age_exists into l_exists;
1029 If csr_chk_company_age_exists%notfound then
1030 close csr_chk_company_age_exists;
1031 --
1032 -- raise error as FK does not relate to PK in hr_all_organization_units
1033 -- table.
1034 --
1035 -- per_ssm_shd.constraint_error('PER_SALARY_SURVEY_MAPPINGS_FK');
1036 fnd_message.set_name('PER','PER_52495_SSM_AGE_DATE_INVL');
1037 fnd_message.raise_error;
1038 --
1039 Else
1040 close csr_chk_company_age_exists;
1041 End If;
1042 End If;
1043 hr_utility.set_location('Leaving:'||l_proc,10);
1044 --
1045 End chk_company_age_code;
1046 --
1047 --
1048 -- ----------------------------------------------------------------------------
1049 -- |------------------------< chk_effective_date >----------------------------|
1050 -- ----------------------------------------------------------------------------
1051 --
1052 -- Description
1053 -- This Procedure checks that the effective date is not null and is valid
1054 --
1055 -- Pre-Conditions
1056 -- None.
1057 --
1058 -- In Parameters
1059 -- p_salary_survey_mapping_id PK
1060 -- p_effective_date
1061 -- p_object_version_number object version number
1062 --
1063 -- Post Success
1064 -- Processing continues If the effective_date is valid
1065 --
1066 -- Post Failure
1067 -- Processing stops and an error is raised.
1068 --
1069 -- Access Status
1070 -- Internal row handler use only.
1071 --
1072 Procedure chk_effective_date
1073 (p_effective_date in date) is
1074 --
1075 l_proc varchar2(72) := g_package||'chk_effective_date';
1076 l_api_updating boolean;
1077 l_exists varchar2(1);
1078 l_effective_date date;
1079 --
1080 --
1081 Begin
1082 --
1083 hr_utility.set_location('Entering:'||l_proc,5);
1084 --
1085 l_effective_date := p_effective_date;
1086 --
1087 If l_effective_date is not null then
1088 hr_api.mandatory_arg_error
1089 (p_api_name => l_proc
1090 ,p_argument => 'effective_date'
1091 ,p_argument_value => l_effective_date
1092 );
1093 Else
1094 fnd_message.set_name('PER','PER_52496_SSM_EFF_DATE_NULL');
1095 fnd_message.raise_error;
1096 End If;
1097 --
1098 hr_utility.set_location('Leaving:'||l_proc,10);
1099 --
1100 End chk_effective_date;
1101 --
1102 --
1103 --
1104 -- -----------------------------------------------------------------------
1105 -- |------------------------------< chk_df >-----------------------------|
1106 -- -----------------------------------------------------------------------
1107 --
1108 -- Description:
1109 -- Validates the all Descriptive Flexfield values.
1110 --
1111 -- Pre-conditions:
1112 -- All other columns have been validated. Must be called as the
1113 -- last step from insert_validate and update_validate.
1114 --
1115 -- In Arguments:
1116 -- p_rec
1117 --
1118 -- Post Success:
1119 -- If the Descriptive Flexfield structure column and data values are
1120 -- all valid this Procedure will End normally and processing will
1121 -- continue.
1122 --
1123 -- Post Failure:
1124 -- If the Descriptive Flexfield structure column value or any of
1125 -- the data values are invalid Then an application error is raised as
1129 -- Internal Row Handler Use Only.
1126 -- a PL/SQL exception.
1127 --
1128 -- Access Status:
1130 --
1131 Procedure chk_df
1132 (p_rec in per_ssm_shd.g_rec_type) is
1133 --
1134 l_proc varchar2(72) := g_package||'chk_df';
1135 --
1136 Begin
1137 hr_utility.set_location('Entering:'||l_proc, 10);
1138 --
1139 If ((p_rec.salary_survey_mapping_id is not null) and (
1140 nvl(per_ssm_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
1141 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
1142 nvl(per_ssm_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
1143 nvl(p_rec.attribute1, hr_api.g_varchar2) or
1144 nvl(per_ssm_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
1145 nvl(p_rec.attribute2, hr_api.g_varchar2) or
1146 nvl(per_ssm_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
1147 nvl(p_rec.attribute3, hr_api.g_varchar2) or
1148 nvl(per_ssm_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
1149 nvl(p_rec.attribute4, hr_api.g_varchar2) or
1150 nvl(per_ssm_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
1151 nvl(p_rec.attribute5, hr_api.g_varchar2) or
1152 nvl(per_ssm_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
1153 nvl(p_rec.attribute6, hr_api.g_varchar2) or
1154 nvl(per_ssm_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
1155 nvl(p_rec.attribute7, hr_api.g_varchar2) or
1156 nvl(per_ssm_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
1157 nvl(p_rec.attribute8, hr_api.g_varchar2) or
1158 nvl(per_ssm_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
1159 nvl(p_rec.attribute9, hr_api.g_varchar2) or
1160 nvl(per_ssm_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
1161 nvl(p_rec.attribute10, hr_api.g_varchar2) or
1162 nvl(per_ssm_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
1163 nvl(p_rec.attribute11, hr_api.g_varchar2) or
1164 nvl(per_ssm_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
1165 nvl(p_rec.attribute12, hr_api.g_varchar2) or
1166 nvl(per_ssm_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
1167 nvl(p_rec.attribute13, hr_api.g_varchar2) or
1168 nvl(per_ssm_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
1169 nvl(p_rec.attribute14, hr_api.g_varchar2) or
1170 nvl(per_ssm_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
1171 nvl(p_rec.attribute15, hr_api.g_varchar2) or
1172 nvl(per_ssm_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
1173 nvl(p_rec.attribute16, hr_api.g_varchar2) or
1174 nvl(per_ssm_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
1175 nvl(p_rec.attribute17, hr_api.g_varchar2) or
1176 nvl(per_ssm_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
1177 nvl(p_rec.attribute18, hr_api.g_varchar2) or
1178 nvl(per_ssm_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
1179 nvl(p_rec.attribute19, hr_api.g_varchar2) or
1180 nvl(per_ssm_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
1181 nvl(p_rec.attribute20, hr_api.g_varchar2)))
1182 or
1183 (p_rec.salary_survey_mapping_id is null) Then
1184 --
1185 -- Only execute the validation If absolutely necessary:
1186 -- a) During update, the structure column value or any
1187 -- of the attribute values have actually changed.
1188 -- b) During insert.
1189 --
1190 hr_dflex_utility.ins_or_upd_descflex_attribs
1191 (p_appl_short_name => 'PER'
1192 ,p_descflex_name => 'PER_SALARY_SURVEY_MAPPINGS'
1193 ,p_attribute_category => p_rec.attribute_category
1194 ,p_attribute1_name => 'ATTRIBUTE1'
1195 ,p_attribute1_value => p_rec.attribute1
1196 ,p_attribute2_name => 'ATTRIBUTE2'
1197 ,p_attribute2_value => p_rec.attribute2
1198 ,p_attribute3_name => 'ATTRIBUTE3'
1199 ,p_attribute3_value => p_rec.attribute3
1200 ,p_attribute4_name => 'ATTRIBUTE4'
1201 ,p_attribute4_value => p_rec.attribute4
1202 ,p_attribute5_name => 'ATTRIBUTE5'
1203 ,p_attribute5_value => p_rec.attribute5
1204 ,p_attribute6_name => 'ATTRIBUTE6'
1205 ,p_attribute6_value => p_rec.attribute6
1206 ,p_attribute7_name => 'ATTRIBUTE7'
1207 ,p_attribute7_value => p_rec.attribute7
1208 ,p_attribute8_name => 'ATTRIBUTE8'
1209 ,p_attribute8_value => p_rec.attribute8
1210 ,p_attribute9_name => 'ATTRIBUTE9'
1211 ,p_attribute9_value => p_rec.attribute9
1212 ,p_attribute10_name => 'ATTRIBUTE10'
1213 ,p_attribute10_value => p_rec.attribute10
1214 ,p_attribute11_name => 'ATTRIBUTE11'
1215 ,p_attribute11_value => p_rec.attribute11
1216 ,p_attribute12_name => 'ATTRIBUTE12'
1217 ,p_attribute12_value => p_rec.attribute12
1218 ,p_attribute13_name => 'ATTRIBUTE13'
1219 ,p_attribute13_value => p_rec.attribute13
1220 ,p_attribute14_name => 'ATTRIBUTE14'
1221 ,p_attribute14_value => p_rec.attribute14
1222 ,p_attribute15_name => 'ATTRIBUTE15'
1223 ,p_attribute15_value => p_rec.attribute15
1224 ,p_attribute16_name => 'ATTRIBUTE16'
1225 ,p_attribute16_value => p_rec.attribute16
1226 ,p_attribute17_name => 'ATTRIBUTE17'
1227 ,p_attribute17_value => p_rec.attribute17
1228 ,p_attribute18_name => 'ATTRIBUTE18'
1229 ,p_attribute18_value => p_rec.attribute18
1230 ,p_attribute19_name => 'ATTRIBUTE19'
1231 ,p_attribute19_value => p_rec.attribute19
1232 ,p_attribute20_name => 'ATTRIBUTE20'
1233 ,p_attribute20_value => p_rec.attribute20
1234 );
1235 End If;
1236 --
1237 hr_utility.set_location(' Leaving:'||l_proc, 20);
1238 End chk_df;
1239 --
1240 --
1241 --
1242 -- ----------------------------------------------------------------------------
1243 -- |---------------------------< insert_validate >----------------------------|
1244 -- ----------------------------------------------------------------------------
1245 Procedure insert_validate(p_rec in per_ssm_shd.g_rec_type
1246 ,p_effective_date in date) is
1247 --
1248 l_proc varchar2(72) := g_package||'insert_validate';
1249 l_ssl_start_date date;
1250 l_ssl_end_date date;
1251 --
1252 Begin
1253 hr_utility.set_location('Entering:'||l_proc, 5);
1254 --
1255 -- Call all supporting business operations
1256 --
1257 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1258 --
1259 hr_utility.set_location(l_proc, 6);
1260 --
1261 chk_unique_key( p_salary_survey_mapping_id => p_rec.salary_survey_mapping_id
1262 , p_parent_id => p_rec.parent_id
1263 , p_parent_table_name => p_rec.parent_table_name
1264 , p_salary_survey_line_id => p_rec.salary_survey_line_id
1265 , p_grade_id => p_rec.grade_id
1266 , p_location_id => p_rec.location_id
1267 , p_company_organization_id => p_rec.company_organization_id
1268 , p_company_age_code => p_rec.company_age_code
1269 , p_object_version_number => p_rec.object_version_number
1270 );
1271 --
1272 hr_utility.set_location(l_proc, 7);
1273 --
1274 chk_salary_survey_line_id( p_salary_survey_mapping_id => p_rec.salary_survey_mapping_id
1275 , p_salary_survey_line_id => p_rec.salary_survey_line_id
1276 , p_object_version_number => p_rec.object_version_number
1277 );
1278 --
1279 l_ssl_start_date := get_salary_survey_line_start( p_salary_survey_line_id => p_rec.salary_survey_line_id);
1280 --
1281 l_ssl_end_date := get_salary_survey_line_end( p_salary_survey_line_id => p_rec.salary_survey_line_id);
1282 hr_utility.set_location(l_proc, 8);
1283 --
1284 chk_parent( p_salary_survey_mapping_id => p_rec.salary_survey_mapping_id
1285 , p_parent_id => p_rec.parent_id
1286 , p_parent_table_name => p_rec.parent_table_name
1287 , p_business_group_id => p_rec.business_group_id
1288 , p_object_version_number => p_rec.object_version_number
1289 , p_ssl_start_date => l_ssl_start_date
1290 , p_ssl_end_date => l_ssl_end_date
1291 );
1292 --
1293 hr_utility.set_location(l_proc, 9);
1294 --
1295 chk_location_id( p_salary_survey_mapping_id => p_rec.salary_survey_mapping_id
1296 , p_location_id => p_rec.location_id
1297 , p_object_version_number => p_rec.object_version_number
1298 , p_ssl_start_date => l_ssl_start_date
1299 );
1300 --
1301 hr_utility.set_location(l_proc, 10);
1302 --
1303 chk_grade_id( p_salary_survey_mapping_id => p_rec.salary_survey_mapping_id
1304 , p_grade_id => p_rec.grade_id
1305 , p_business_group_id => p_rec.business_group_id
1306 , p_object_version_number => p_rec.object_version_number
1307 , p_ssl_start_date => l_ssl_start_date
1308 , p_ssl_end_date => l_ssl_end_date
1309 );
1310 --
1311 hr_utility.set_location(l_proc, 11);
1312 --
1313 chk_company_organization_id( p_salary_survey_mapping_id => p_rec.salary_survey_mapping_id
1314 , p_company_organization_id => p_rec.company_organization_id
1315 , p_business_group_id => p_rec.business_group_id
1316 , p_object_version_number => p_rec.object_version_number
1317 , p_ssl_start_date => l_ssl_start_date
1318 , p_ssl_end_date => l_ssl_end_date
1319 );
1320 --
1321 hr_utility.set_location(l_proc, 12);
1322 --
1323 chk_company_age_code
1324 ( p_salary_survey_mapping_id => p_rec.salary_survey_mapping_id
1325 , p_company_age_code => p_rec.company_age_code
1326 , p_effective_date => p_effective_date
1327 , p_object_version_number => p_rec.object_version_number
1328 );
1329 --
1330 hr_utility.set_location(l_proc, 13);
1331 --
1332 chk_effective_date(p_effective_date => p_effective_date);
1333 --
1334 -- chk_df(p_rec);
1335 --
1336 hr_utility.set_location(' Leaving:'||l_proc, 15);
1337 End insert_validate;
1338 --
1339 --
1340 -- ----------------------------------------------------------------------------
1341 -- |---------------------------< update_validate >----------------------------|
1342 -- ----------------------------------------------------------------------------
1343 Procedure update_validate(p_rec in per_ssm_shd.g_rec_type
1344 ,p_effective_date in date) is
1345 --
1346 l_proc varchar2(72) := g_package||'update_validate';
1347 l_ssl_start_date date;
1348 l_ssl_end_date date;
1349 --
1350 Begin
1351 hr_utility.set_location('Entering:'||l_proc, 5);
1352 --
1353 -- Call all supporting business operations
1354 --
1355 -- Check that non updateable arguments have not bee updated.
1356 --
1357 chk_non_updateable_args(p_rec);
1358 --
1359 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1360 --
1361 --
1362 hr_utility.set_location(l_proc, 6);
1363 --
1364 chk_unique_key( p_salary_survey_mapping_id => p_rec.salary_survey_mapping_id
1365 , p_parent_id => p_rec.parent_id
1366 , p_parent_table_name => p_rec.parent_table_name
1367 , p_salary_survey_line_id => p_rec.salary_survey_line_id
1368 , p_grade_id => p_rec.grade_id
1369 , p_location_id => p_rec.location_id
1370 , p_company_organization_id => p_rec.company_organization_id
1371 , p_company_age_code => p_rec.company_age_code
1372 , p_object_version_number => p_rec.object_version_number
1373 );
1374 --
1375 hr_utility.set_location(l_proc, 7);
1376 --
1377 chk_salary_survey_line_id( p_salary_survey_mapping_id => p_rec.salary_survey_mapping_id
1378 , p_salary_survey_line_id => p_rec.salary_survey_line_id
1379 , p_object_version_number => p_rec.object_version_number
1380 );
1381 --
1382 l_ssl_start_date := get_salary_survey_line_start( p_salary_survey_line_id => p_rec.salary_survey_line_id);
1383 --
1384 l_ssl_end_date := get_salary_survey_line_end( p_salary_survey_line_id => p_rec.salary_survey_line_id);
1385 --
1386 hr_utility.set_location(l_proc, 8);
1387 --
1388 chk_parent( p_salary_survey_mapping_id => p_rec.salary_survey_mapping_id
1389 , p_parent_id => p_rec.parent_id
1390 , p_parent_table_name => p_rec.parent_table_name
1391 , p_business_group_id => p_rec.business_group_id
1392 , p_object_version_number => p_rec.object_version_number
1393 , p_ssl_start_date => l_ssl_start_date
1394 , p_ssl_end_date => l_ssl_end_date
1395 );
1396 --
1397 hr_utility.set_location(l_proc, 9);
1398 --
1399 chk_location_id( p_salary_survey_mapping_id => p_rec.salary_survey_mapping_id
1400 , p_location_id => p_rec.location_id
1401 , p_object_version_number => p_rec.object_version_number
1402 , p_ssl_start_date => l_ssl_start_date
1403 );
1404 --
1405 hr_utility.set_location(l_proc, 10);
1406 --
1407 chk_grade_id( p_salary_survey_mapping_id => p_rec.salary_survey_mapping_id
1408 , p_grade_id => p_rec.grade_id
1409 , p_business_group_id => p_rec.business_group_id
1410 , p_object_version_number => p_rec.object_version_number
1411 , p_ssl_start_date => l_ssl_start_date
1412 , p_ssl_end_date => l_ssl_end_date
1413 );
1414 --
1415 hr_utility.set_location(l_proc, 11);
1416 --
1417 chk_company_organization_id
1418 ( p_salary_survey_mapping_id => p_rec.salary_survey_mapping_id
1419 , p_company_organization_id => p_rec.company_organization_id
1420 , p_business_group_id => p_rec.business_group_id
1421 , p_object_version_number => p_rec.object_version_number
1422 , p_ssl_start_date => l_ssl_start_date
1423 , p_ssl_end_date => l_ssl_end_date
1424 );
1425 --
1426 hr_utility.set_location(l_proc, 12);
1427 --
1428 chk_company_age_code
1429 ( p_salary_survey_mapping_id => p_rec.salary_survey_mapping_id
1430 , p_company_age_code => p_rec.company_age_code
1431 , p_effective_date => p_effective_date
1432 , p_object_version_number => p_rec.object_version_number
1433 );
1434 --
1435 hr_utility.set_location(l_proc, 13);
1436 --
1437 chk_effective_date(p_effective_date => p_effective_date);
1438 --
1439 -- chk_df(p_rec);
1440 --
1441 hr_utility.set_location(' Leaving:'||l_proc, 15);
1442 End update_validate;
1443 --
1444 --
1445 --
1446 -- ----------------------------------------------------------------------------
1447 -- |---------------------------< delete_validate >----------------------------|
1448 -- ----------------------------------------------------------------------------
1449 Procedure delete_validate(p_rec in per_ssm_shd.g_rec_type) is
1450 --
1451 l_proc varchar2(72) := g_package||'delete_validate';
1452 --
1453 Begin
1454 hr_utility.set_location('Entering:'||l_proc, 5);
1455 --
1456 -- Call all supporting business operations
1457 --
1458 hr_utility.set_location(' Leaving:'||l_proc, 10);
1459 End delete_validate;
1460 --
1461 --
1462 end per_ssm_bus;