[Home] [Help]
PACKAGE BODY: APPS.HR_SALARY_SURVEY_MAPPING_API
Source
1 Package Body hr_salary_survey_mapping_api as
2 /* $Header: pessmapi.pkb 115.6 2003/12/24 12:04:23 hsajja ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_salary_survey_mapping_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |----------------------------< create_mapping >----------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_mapping
13 (p_validate in boolean default false
14 ,p_effective_date in date
15 ,p_business_group_id in number
16 ,p_parent_id in number
17 ,p_parent_table_name in varchar2
18 ,p_salary_survey_line_id in number
19 ,p_location_id in number default null
20 ,p_grade_id in number default null
21 ,p_company_organization_id in number default null
22 ,p_company_age_code in varchar2 default null
23 ,p_attribute_category in varchar2 default null
24 ,p_attribute1 in varchar2 default null
25 ,p_attribute2 in varchar2 default null
26 ,p_attribute3 in varchar2 default null
27 ,p_attribute4 in varchar2 default null
28 ,p_attribute5 in varchar2 default null
29 ,p_attribute6 in varchar2 default null
30 ,p_attribute7 in varchar2 default null
31 ,p_attribute8 in varchar2 default null
32 ,p_attribute9 in varchar2 default null
33 ,p_attribute10 in varchar2 default null
34 ,p_attribute11 in varchar2 default null
35 ,p_attribute12 in varchar2 default null
36 ,p_attribute13 in varchar2 default null
37 ,p_attribute14 in varchar2 default null
38 ,p_attribute15 in varchar2 default null
39 ,p_attribute16 in varchar2 default null
40 ,p_attribute17 in varchar2 default null
41 ,p_attribute18 in varchar2 default null
42 ,p_attribute19 in varchar2 default null
43 ,p_attribute20 in varchar2 default null
44 ,p_salary_survey_mapping_id out nocopy number
45 ,p_object_version_number out nocopy number
46 ) is
47
48 --
49 -- Declare cursors and local variables
50 --
51
52 l_proc varchar2(72) := g_package||'create_mapping';
53 l_effective_date date;
54 l_salary_survey_mapping_id number;
55 l_object_version_number number;
56 begin
57 hr_utility.set_location('Entering:'|| l_proc, 10);
58 --
59 -- Issue a savepoint
60 --
61 savepoint create_mapping;
62 hr_utility.set_location(l_proc, 20);
63 --
64 -- Truncate the time portion from all IN date parameters
65 --
66 l_effective_date := trunc(p_effective_date);
67 --
68 -- Call Before Process User Hook
69 --
70 /*
71 begin
72 per_ssm_api_bk1.create_mapping_b
73 (p_effective_date => p_effective_date
74 ,p_business_group_id => p_business_group_id
75 ,p_parent_id => p_parent_id
76 ,p_parent_table_name => p_parent_table_name
77 ,p_salary_survey_line_id => p_salary_survey_line_id
78 ,p_location_id => p_location_id
79 ,p_grade_id => p_grade_id
80 ,p_company_organization_id => p_company_organization_id
81 ,p_company_age_code => p_company_age_code
82 ,p_attribute_category => p_attribute_category
83 ,p_attribute1 => p_attribute1
84 ,p_attribute2 => p_attribute2
85 ,p_attribute3 => p_attribute3
86 ,p_attribute4 => p_attribute4
87 ,p_attribute5 => p_attribute5
88 ,p_attribute6 => p_attribute6
89 ,p_attribute7 => p_attribute7
90 ,p_attribute8 => p_attribute8
91 ,p_attribute9 => p_attribute9
92 ,p_attribute10 => p_attribute10
93 ,p_attribute11 => p_attribute11
94 ,p_attribute12 => p_attribute12
95 ,p_attribute13 => p_attribute13
96 ,p_attribute14 => p_attribute14
97 ,p_attribute15 => p_attribute15
98 ,p_attribute16 => p_attribute16
99 ,p_attribute17 => p_attribute17
100 ,p_attribute18 => p_attribute18
101 ,p_attribute19 => p_attribute19
102 ,p_attribute20 => p_attribute20
103 );
104 exception
105 when hr_api.cannot_find_prog_unit then
106 hr_api.cannot_find_prog_unit_error
107 (p_module_name => 'CREATE_MAPPING'
108 ,p_hook_type => 'BP'
109 );
110 end;
111 */
112 hr_utility.set_location(l_proc, 30);
113 --
114 -- Validation in addition to Row Handlers
115 --
116
117
118
119 hr_utility.set_location(l_proc, 40);
120 --
121 -- Process Logic
122 --
123 per_ssm_ins.ins(
124 p_object_version_number => l_object_version_number
125 ,p_salary_survey_mapping_id => l_salary_survey_mapping_id
126 ,p_parent_id => p_parent_id
127 ,p_parent_table_name => p_parent_table_name
128 ,p_salary_survey_line_id => p_salary_survey_line_id
129 ,p_business_group_id => p_business_group_id
130 ,p_location_id => p_location_id
131 ,p_grade_id => p_grade_id
132 ,p_company_organization_id => p_company_organization_id
133 ,p_company_age_code => p_company_age_code
134 ,p_attribute_category => p_attribute_category
135 ,p_attribute1 => p_attribute1
136 ,p_attribute2 => p_attribute2
137 ,p_attribute3 => p_attribute3
138 ,p_attribute4 => p_attribute4
139 ,p_attribute5 => p_attribute5
140 ,p_attribute6 => p_attribute6
141 ,p_attribute7 => p_attribute7
142 ,p_attribute8 => p_attribute8
143 ,p_attribute9 => p_attribute9
144 ,p_attribute10 => p_attribute10
145 ,p_attribute11 => p_attribute11
146 ,p_attribute12 => p_attribute12
147 ,p_attribute13 => p_attribute13
148 ,p_attribute14 => p_attribute14
149 ,p_attribute15 => p_attribute15
150 ,p_attribute16 => p_attribute16
151 ,p_attribute17 => p_attribute17
152 ,p_attribute18 => p_attribute18
153 ,p_attribute19 => p_attribute19
154 ,p_attribute20 => p_attribute20
155 ,p_effective_date => l_effective_date
156 );
157
158
159 hr_utility.set_location(l_proc, 50);
160 --
161 -- Call After Process User Hook
162 --
163 /*
164 begin
165 per_ssm_api_bk1.create_mapping_a
166 (p_effective_date => p_effective_date
167 ,p_business_group_id => p_business_group_id
168 ,p_parent_id => p_parent_id
169 ,p_parent_table_name => p_parent_table_name
170 ,p_salary_survey_line_id => p_salary_survey_line_id
171 ,p_location_id => p_location_id
172 ,p_grade_id => p_grade_id
173 ,p_company_organization_id => p_company_organization_id
174 ,p_company_age_code => p_company_age_code
175 ,p_attribute_category => p_attribute_category
176 ,p_attribute1 => p_attribute1
177 ,p_attribute2 => p_attribute2
178 ,p_attribute3 => p_attribute3
179 ,p_attribute4 => p_attribute4
180 ,p_attribute5 => p_attribute5
181 ,p_attribute6 => p_attribute6
182 ,p_attribute7 => p_attribute7
183 ,p_attribute8 => p_attribute8
184 ,p_attribute9 => p_attribute9
185 ,p_attribute10 => p_attribute10
186 ,p_attribute11 => p_attribute11
187 ,p_attribute12 => p_attribute12
188 ,p_attribute13 => p_attribute13
189 ,p_attribute14 => p_attribute14
190 ,p_attribute15 => p_attribute15
191 ,p_attribute16 => p_attribute16
192 ,p_attribute17 => p_attribute17
193 ,p_attribute18 => p_attribute18
194 ,p_attribute19 => p_attribute19
195 ,p_attribute20 => p_attribute20
196 ,p_salary_survey_mapping_id => p_salary_survey_mapping_id
197 ,p_object_version_number => p_object_version_number
198 );
199 exception
200 when hr_api.cannot_find_prog_unit then
201 hr_api.cannot_find_prog_unit_error
202 (p_module_name => 'CREATE_MAPPING'
203 ,p_hook_type => 'AP'
204 );
205 end;
206 */
207 hr_utility.set_location(l_proc, 60);
208 --
209 -- When in validation only mode raise the Validate_Enabled exception
210 --
211 if p_validate then
212 raise hr_api.validate_enabled;
213 end if;
214 --
215 -- Set all output arguments
216 --
217 p_salary_survey_mapping_id := l_salary_survey_mapping_id;
218 p_object_version_number := l_object_version_number;
219 --
220 hr_utility.set_location(' Leaving:'||l_proc, 70);
221 exception
222 when hr_api.validate_enabled then
223 --
224 -- As the Validate_Enabled exception has been raised
225 -- we must rollback to the savepoint
226 --
227 rollback to create_mapping;
228 --
229 -- Only set output warning arguments
230 -- (Any key or derived arguments must be set to null
231 -- when validation only mode is being used.)
232 --
233 p_salary_survey_mapping_id := null;
234 p_object_version_number := null;
235 hr_utility.set_location(' Leaving:'||l_proc, 80);
236 when others then
237 --
238 -- A validation or unexpected error has occured
239 --
240 p_salary_survey_mapping_id := null;
241 p_object_version_number := null;
242 rollback to create_mapping;
243 hr_utility.set_location(' Leaving:'||l_proc, 90);
244 raise;
245 end create_mapping;
246 --
247 -- ----------------------------------------------------------------------------
248 -- |----------------------------< update_mapping >----------------------------|
249 -- ----------------------------------------------------------------------------
250 --
251 procedure update_mapping
252 (p_validate in boolean default false
253 ,p_effective_date in date
254 ,p_location_id in number
255 ,p_grade_id in number
256 ,p_company_organization_id in number
257 ,p_company_age_code in varchar2
258 ,p_attribute_category in varchar2
259 ,p_attribute1 in varchar2
260 ,p_attribute2 in varchar2
261 ,p_attribute3 in varchar2
262 ,p_attribute4 in varchar2
263 ,p_attribute5 in varchar2
264 ,p_attribute6 in varchar2
265 ,p_attribute7 in varchar2
266 ,p_attribute8 in varchar2
267 ,p_attribute9 in varchar2
268 ,p_attribute10 in varchar2
269 ,p_attribute11 in varchar2
270 ,p_attribute12 in varchar2
271 ,p_attribute13 in varchar2
272 ,p_attribute14 in varchar2
273 ,p_attribute15 in varchar2
274 ,p_attribute16 in varchar2
275 ,p_attribute17 in varchar2
276 ,p_attribute18 in varchar2
277 ,p_attribute19 in varchar2
278 ,p_attribute20 in varchar2
279 ,p_salary_survey_mapping_id in number
280 ,p_object_version_number in out nocopy number
281 ) is
282 --
283 -- Declare cursors and local variables
284 --
285
286 l_proc varchar2(72) := g_package||'update_mapping';
287 l_salary_survey_mapping_id number;
288 l_object_version_number number;
289 l_effective_date date;
290 begin
291 hr_utility.set_location('Entering:'|| l_proc, 10);
292 --
293 -- Issue a savepoint
294 --
295 savepoint update_mapping;
296 hr_utility.set_location(l_proc, 20);
297 --
298 -- Truncate the time portion from all IN date parameters
299 --
300 l_effective_date := trunc(p_effective_date);
301 l_object_version_number := p_object_version_number;
302 --
303 -- Call Before Process User Hook
304 --
305 /*
306 begin
307 per_ssm_api_bk2.update_mapping_b
308 (p_effective_date => p_effective_date
309 ,p_location_id => p_location_id
310 ,p_grade_id => p_grade_id
311 ,p_company_organization_id => p_company_organization_id
312 ,p_company_age_code => p_company_age_code
313 ,p_attribute_category => p_attribute_category
314 ,p_attribute1 => p_attribute1
315 ,p_attribute2 => p_attribute2
316 ,p_attribute3 => p_attribute3
317 ,p_attribute4 => p_attribute4
318 ,p_attribute5 => p_attribute5
319 ,p_attribute6 => p_attribute6
320 ,p_attribute7 => p_attribute7
321 ,p_attribute8 => p_attribute8
322 ,p_attribute9 => p_attribute9
323 ,p_attribute10 => p_attribute10
324 ,p_attribute11 => p_attribute11
325 ,p_attribute12 => p_attribute12
326 ,p_attribute13 => p_attribute13
330 ,p_attribute17 => p_attribute17
327 ,p_attribute14 => p_attribute14
328 ,p_attribute15 => p_attribute15
329 ,p_attribute16 => p_attribute16
331 ,p_attribute18 => p_attribute18
332 ,p_attribute19 => p_attribute19
333 ,p_attribute20 => p_attribute20
334 ,p_salary_survey_mapping_id => p_salary_survey_mapping_id
335 ,p_object_version_number => l_object_version_number
336 );
337 exception
338 when hr_api.cannot_find_prog_unit then
339 hr_api.cannot_find_prog_unit_error
340 (p_module_name => 'update_mapping'
341 ,p_hook_type => 'BP'
342 );
343 end;
344 */
345 hr_utility.set_location(l_proc, 30);
346 --
347 -- Validation in addition to Row Handlers
348 --
349
350
351
352 hr_utility.set_location(l_proc, 40);
353 --
354 -- Process Logic
355 --
356 per_ssm_upd.upd
357 (p_object_version_number => l_object_version_number
358 ,p_salary_survey_mapping_id => p_salary_survey_mapping_id
359 ,p_location_id => p_location_id
360 ,p_grade_id => p_grade_id
361 ,p_company_organization_id => p_company_organization_id
362 ,p_company_age_code => p_company_age_code
363 ,p_attribute_category => p_attribute_category
364 ,p_attribute1 => p_attribute1
365 ,p_attribute2 => p_attribute2
366 ,p_attribute3 => p_attribute3
367 ,p_attribute4 => p_attribute4
368 ,p_attribute5 => p_attribute5
369 ,p_attribute6 => p_attribute6
370 ,p_attribute7 => p_attribute7
371 ,p_attribute8 => p_attribute8
372 ,p_attribute9 => p_attribute9
373 ,p_attribute10 => p_attribute10
374 ,p_attribute11 => p_attribute11
375 ,p_attribute12 => p_attribute12
376 ,p_attribute13 => p_attribute13
377 ,p_attribute14 => p_attribute14
378 ,p_attribute15 => p_attribute15
379 ,p_attribute16 => p_attribute16
380 ,p_attribute17 => p_attribute17
381 ,p_attribute18 => p_attribute18
382 ,p_attribute19 => p_attribute19
383 ,p_attribute20 => p_attribute20
384 ,p_effective_date => l_effective_date
385 );
386
387 p_object_version_number := l_object_version_number;
388 hr_utility.set_location(l_proc||' SSM OVN: '||l_object_version_number,49);
389
390
391 hr_utility.set_location(l_proc, 50);
392 /*
393 --
394 -- Call After Process User Hook
395 --
396 begin
397 per_ssm_api_bk2.update_mapping_a
398 (p_effective_date => p_effective_date
399 ,p_location_id => p_location_id
400 ,p_grade_id => p_grade_id
401 ,p_company_organization_id => p_company_organization_id
402 ,p_company_age_code => p_company_age_code
403 ,p_attribute_category => p_attribute_category
404 ,p_attribute1 => p_attribute1
405 ,p_attribute2 => p_attribute2
406 ,p_attribute3 => p_attribute3
407 ,p_attribute4 => p_attribute4
408 ,p_attribute5 => p_attribute5
409 ,p_attribute6 => p_attribute6
410 ,p_attribute7 => p_attribute7
411 ,p_attribute8 => p_attribute8
412 ,p_attribute9 => p_attribute9
413 ,p_attribute10 => p_attribute10
414 ,p_attribute11 => p_attribute11
415 ,p_attribute12 => p_attribute12
416 ,p_attribute13 => p_attribute13
417 ,p_attribute14 => p_attribute14
418 ,p_attribute15 => p_attribute15
419 ,p_attribute16 => p_attribute16
420 ,p_attribute17 => p_attribute17
421 ,p_attribute18 => p_attribute18
422 ,p_attribute19 => p_attribute19
423 ,p_attribute20 => p_attribute20
424 ,p_salary_survey_mapping_id => p_salary_survey_mapping_id
425 ,p_object_version_number => l_object_version_number
426 );
427 exception
428 when hr_api.cannot_find_prog_unit then
429 hr_api.cannot_find_prog_unit_error
430 (p_module_name => 'update_mapping'
431 ,p_hook_type => 'AP'
432 );
433 end;
434 */
435 hr_utility.set_location(l_proc, 60);
436 --
437 -- When in validation only mode raise the Validate_Enabled exception
438 --
439 if p_validate then
440 raise hr_api.validate_enabled;
441 end if;
442 --
443 -- Set all output arguments
444 --
445 p_object_version_number := l_object_version_number;
446 --
447 hr_utility.set_location(' Leaving:'||l_proc, 70);
448 exception
452 -- we must rollback to the savepoint
449 when hr_api.validate_enabled then
450 --
451 -- As the Validate_Enabled exception has been raised
453 --
454 rollback to update_mapping;
455 --
456 -- Only set output warning arguments
457 -- (Any key or derived arguments must be set to null
458 -- when validation only mode is being used.)
459 --
460 p_object_version_number := null;
461 hr_utility.set_location(' Leaving:'||l_proc, 80);
462 when others then
463 --
464 -- A validation or unexpected error has occured
465 --
466 p_object_version_number := l_object_version_number;
467 rollback to update_mapping;
468 hr_utility.set_location(' Leaving:'||l_proc, 90);
469 raise;
470 end update_mapping;
471 --
472 -- ----------------------------------------------------------------------------
473 -- |----------------------------< delete_mapping >----------------------------|
474 -- ----------------------------------------------------------------------------
475 --
476 procedure delete_mapping
477 (p_validate in boolean default false
478 ,p_salary_survey_mapping_id in number
479 ,p_object_version_number in number
480 ) is
481 --
482 -- Declare cursors and local variables
483 --
484
485 l_proc varchar2(72) := g_package||'delete_mapping';
486 begin
487 hr_utility.set_location('Entering:'|| l_proc, 10);
488 --
489 -- Issue a savepoint
490 --
491 savepoint delete_mapping;
492 hr_utility.set_location(l_proc, 20);
493 --
494 -- Truncate the time portion from all IN date parameters
495 --
496
497 --
498 -- Call Before Process User Hook
499 --
500 begin
501 per_ssm_api_bk3.delete_mapping_b
502 (p_salary_survey_mapping_id => p_salary_survey_mapping_id
503 ,p_object_version_number => p_object_version_number
504 );
505 exception
506 when hr_api.cannot_find_prog_unit then
507 hr_api.cannot_find_prog_unit_error
508 (p_module_name => 'DELETE_MAPPING'
509 ,p_hook_type => 'BP'
510 );
511 end;
512 hr_utility.set_location(l_proc, 30);
513 --
514 -- Validation in addition to Row Handlers
515 --
516
517
518
519 hr_utility.set_location(l_proc, 40);
520 --
521 -- Process Logic
522 --
523 per_ssm_del.del
524 (p_salary_survey_mapping_id => p_salary_survey_mapping_id
525 ,p_object_version_number => p_object_version_number
526 );
527 --
528 hr_utility.set_location(l_proc, 50);
529 --
530 -- Call After Process User Hook
531 --
532 begin
533 per_ssm_api_bk3.delete_mapping_a
534 (p_salary_survey_mapping_id => p_salary_survey_mapping_id
535 ,p_object_version_number => p_object_version_number
536 );
537 exception
538 when hr_api.cannot_find_prog_unit then
539 hr_api.cannot_find_prog_unit_error
540 (p_module_name => 'DELETE_MAPPING'
541 ,p_hook_type => 'AP'
542 );
543 end;
544 hr_utility.set_location(l_proc, 60);
545 --
546 -- When in validation only mode raise the Validate_Enabled exception
547 --
548 if p_validate then
549 raise hr_api.validate_enabled;
550 end if;
551 --
552 hr_utility.set_location(' Leaving:'||l_proc, 70);
553 exception
554 when hr_api.validate_enabled then
555 --
556 -- As the Validate_Enabled exception has been raised
557 -- we must rollback to the savepoint
558 --
559 rollback to delete_mapping;
560 --
561 when others then
562 --
563 -- A validation or unexpected error has occured
564 --
565 rollback to delete_mapping;
566 hr_utility.set_location(' Leaving:'||l_proc, 90);
567 raise;
568 end delete_mapping;
569 --
570 -- ----------------------------------------------------------------------------
571 -- |------------------------------< mass_update >-----------------------------|
572 -- ----------------------------------------------------------------------------
573 --
574 procedure mass_update
575 (p_validate in boolean default false
576 ,p_effective_date in date
577 ,p_business_group_id in number
578 ,p_job_id in number default null
579 ,p_position_id in number default null
580 ) is
581 --
582 -- Declare cursors and local variables
583 --
584 l_proc varchar2(72) := g_package||'mass_update';
585 l_effective_date date;
586 l_salary_survey_mapping_id number;
587 l_object_version_number number;
588 l_job_id number;
589 l_business_group_id number;
590 l_date_from date;
591 l_date_to date;
592 l_parent_date_from date;
593 l_parent_date_to date;
594 l_max_date_from date;
595 l_min_date_to date;
596 l_parent_table_name varchar2(30);
597 l_company_organization_id number;
598 l_grade_id number;
599 --
600 cursor csr_get_job_dates is
604 --
601 select j.date_from, nvl(j.date_to, hr_api.g_eot)
602 from per_jobs j
603 where j.job_id = p_job_id;
605 -- Changes 13-Oct-99 SCNair (per_positions to hr_positions_f)Date tracked position req
606 --
607 cursor csr_get_position_dates is
608 select p.date_effective, nvl(hr_general.get_position_date_end(p.position_id), hr_general.end_of_time)
609 from hr_positions_f p
610 where p.position_id = p_position_id
611 and p.effective_end_date = hr_general.end_of_time;
612 --
613 cursor csr_get_mappings(c_table_name per_salary_survey_mappings.parent_table_name%TYPE) is
614 select ssm.location_id location_id,
615 ssm.grade_id grade_id,
616 ssm.company_organization_id company_organization_id,
617 ssm.company_age_code company_age_code,
618 ssl.survey_job_name_code survey_job_name_code,
619 ssl.survey_region_code survey_region_code,
620 ssl.survey_seniority_code survey_seniority_code,
621 ssl.company_size_code company_size_code,
622 ssl.industry_code industry_code,
623 max(ssl.end_date) max_end_date
624 from per_salary_survey_mappings ssm,
625 per_salary_survey_lines ssl
626 where nvl(p_job_id,p_position_id) = ssm.parent_id
627 and ssl.salary_survey_line_id = ssm.salary_survey_line_id
628 and parent_table_name = c_table_name
629 group by ssm.location_id,
630 ssm.grade_id,
631 ssm.company_organization_id,
632 ssm.company_age_code,
633 ssl.survey_job_name_code,
634 ssl.survey_region_code,
635 ssl.survey_seniority_code,
636 ssl.company_size_code,
637 ssl.industry_code;
638
639 --
640 cursor csr_get_grade_dates(c_grade_id number) is
641 select g.date_from, nvl(g.date_to,hr_api.g_eot)
642 from per_grades g
643 where grade_id = c_grade_id;
644 --
645 cursor csr_get_org_dates(c_org_id number) is
646 select o.date_from, nvl(o.date_to,hr_api.g_eot)
647 from hr_all_organization_units o
648 where o.organization_id = c_org_id;
649 --
650 cursor csr_get_ssl_ids(c_survey_job_name_code varchar2
651 ,c_survey_region_code varchar2
652 ,c_survey_seniority_code varchar2
653 ,c_company_size_code varchar2
654 ,c_industry_code varchar2
655 ,c_date_from date
656 ,c_date_to date) is
657 select salary_survey_line_id
658 from per_salary_survey_lines ssl
659 where ssl.start_date between c_date_from and c_date_to
660 and nvl(ssl.survey_job_name_code,hr_api.g_varchar2) = nvl(c_survey_job_name_code,hr_api.g_varchar2)
661 and nvl(ssl.survey_region_code,hr_api.g_varchar2) = nvl(c_survey_region_code,hr_api.g_varchar2)
662 and nvl(ssl.survey_seniority_code,hr_api.g_varchar2) = nvl(c_survey_seniority_code,hr_api.g_varchar2)
663 and nvl(ssl.company_size_code,hr_api.g_varchar2) = nvl(c_company_size_code,hr_api.g_varchar2)
664 and nvl(ssl.industry_code,hr_api.g_varchar2) = nvl(c_industry_code,hr_api.g_varchar2);
665
666
667 begin
668 hr_utility.set_location('Entering:'|| l_proc, 10);
669 --
670 -- Issue a savepoint
671 --
672 savepoint mass_update;
673 hr_utility.set_location(l_proc, 20);
674 --
675 -- Truncate the time portion from all IN date parameters
676 --
677 l_effective_date := trunc(p_effective_date);
678 --
679 -- Call Before Process User Hook
680 --
681 /*
682 begin
683 per_ssm_api_bk4.mass_update_b
684 (p_effective_date => p_effective_date
685 ,p_business_group_id => p_business_group_id
686 ,p_job_id => p_job_id
687 ,p_position_id => p_position_id
688 );
689 exception
690 when hr_api.cannot_find_prog_unit then
691 hr_api.cannot_find_prog_unit_error
692 (p_module_name => 'MASS_UPDATE'
693 ,p_hook_type => 'BP'
694 );
695 end;
696 */
697 hr_utility.set_location(l_proc, 30);
698 --
699 -- Validation in addition to Row Handlers
700 --
701 hr_utility.set_location(l_proc, 40);
702 --
703 -- Process Logic
704 --
705 If p_job_id is null and p_position_id is null then
706 hr_utility.set_location(l_proc, 50);
707 fnd_message.set_name('PER','PER_52497_SSM_JOB_POS_NULL');
708 fnd_message.raise_error;
709 Elsif p_job_id is not null and p_position_id is not null then
710 hr_utility.set_location(l_proc, 60);
711 fnd_message.set_name('PER','PER_52498_SSM_JOB_POS_CONT_VAL');
712 fnd_message.raise_error;
713 Elsif p_job_id is not null then
714 hr_utility.set_location(l_proc, 70);
715 l_parent_table_name := 'PER_JOBS';
716 open csr_get_job_dates;
717 fetch csr_get_job_dates into l_parent_date_from, l_parent_date_to;
718 If csr_get_job_dates%NOTFOUND then
719 hr_utility.set_location(l_proc, 80);
720 close csr_get_job_dates;
721 fnd_message.set_name('PER','PER_52483_SSM_INVL_JOB_ID');
722 fnd_message.raise_error;
723 Else
724 hr_utility.set_location(l_proc, 90);
725 close csr_get_job_dates;
726 l_max_date_from := l_parent_date_from;
727 --
728 l_min_date_to := l_parent_date_to;
729 --
730 For csr_get_mappings_rec in csr_get_mappings(l_parent_table_name) loop
734 If csr_get_mappings_rec.max_end_date > l_max_date_from then
731 hr_utility.set_location(l_proc, 100);
732 l_max_date_from := l_parent_date_from;
733 l_min_date_to := l_parent_date_to;
735 hr_utility.set_location(l_proc, 115);
736 l_max_date_from := csr_get_mappings_rec.max_end_date;
737 End If;
738 If csr_get_mappings_rec.grade_id is not null then
739 hr_utility.set_location(l_proc, 120);
740 open csr_get_grade_dates(csr_get_mappings_rec.grade_id);
741 fetch csr_get_grade_dates into l_date_from, l_date_to;
742 close csr_get_grade_dates;
743 hr_utility.set_location(l_proc, 130);
744 If l_date_from > l_max_date_from then
745 hr_utility.set_location(l_proc, 140);
746 l_max_date_from := l_date_from;
747 End If;
748 If l_date_to < l_min_date_to then
749 hr_utility.set_location(l_proc, 150);
750 l_min_date_to := l_date_to;
751 End If;
752 End If;
753 --
754 If csr_get_mappings_rec.company_organization_id is not null then
755 hr_utility.set_location(l_proc, 160);
756 open csr_get_org_dates(csr_get_mappings_rec.company_organization_id);
757 fetch csr_get_org_dates into l_date_from, l_date_to;
758 close csr_get_org_dates;
759 hr_utility.set_location(l_proc, 170);
760 If l_date_from > l_max_date_from then
761 hr_utility.set_location(l_proc, 180);
762 l_max_date_from := l_date_from;
763 End If;
764 If l_date_to < l_min_date_to then
765 hr_utility.set_location(l_proc, 190);
766 l_min_date_to := l_date_to;
767 End If;
768 hr_utility.set_location(l_proc, 192);
769 End If;
770 --
771 hr_utility.set_location(l_proc||' Location ID: '||csr_get_mappings_rec.location_id,195);
772 hr_utility.set_location(l_proc||' Grade ID: '||csr_get_mappings_rec.grade_id,196);
773 hr_utility.set_location(l_proc||' Org ID: '||csr_get_mappings_rec.company_organization_id,197);
774 hr_utility.set_location(l_proc||' Comp Age Code: '||csr_get_mappings_rec.company_age_code,198);
775 --
776 For csr_get_ssl_ids_rec in csr_get_ssl_ids(csr_get_mappings_rec.survey_job_name_code
777 ,csr_get_mappings_rec.survey_region_code
778 ,csr_get_mappings_rec.survey_seniority_code
779 ,csr_get_mappings_rec.company_size_code
780 ,csr_get_mappings_rec.industry_code
781 ,l_max_date_from
782 ,l_min_date_to) Loop
783 hr_utility.set_location(l_proc||' SSL ID: '||csr_get_ssl_ids_rec.salary_survey_line_id,199);
784 --
785 hr_utility.set_location(l_proc, 200);
786 create_mapping
787 (p_validate => FALSE
788 ,p_effective_date => l_effective_date
789 ,p_business_group_id => p_business_group_id
790 ,p_parent_id => p_job_id
791 ,p_parent_table_name => l_parent_table_name
792 ,p_salary_survey_line_id => csr_get_ssl_ids_rec.salary_survey_line_id
793 ,p_location_id => csr_get_mappings_rec.location_id
794 ,p_grade_id => csr_get_mappings_rec.grade_id
795 ,p_company_organization_id => csr_get_mappings_rec.company_organization_id
796 ,p_company_age_code => csr_get_mappings_rec.company_age_code
797 ,p_salary_survey_mapping_id => l_salary_survey_mapping_id
798 ,p_object_version_number => l_object_version_number
799 );
800 hr_utility.set_location(l_proc, 210);
801 End loop;
802 End Loop;
803 End If;
804 Elsif p_position_id is not null then
805 l_parent_table_name := 'PER_POSITIONS';
806 open csr_get_position_dates;
807 fetch csr_get_position_dates into l_parent_date_from, l_parent_date_to;
808 If csr_get_position_dates%NOTFOUND then
809 close csr_get_position_dates;
810 fnd_message.set_name('PER','PER_52486_SSM_INVL_POS_ID');
811 fnd_message.raise_error;
812 Else
813 close csr_get_position_dates;
814 l_max_date_from := l_parent_date_from;
815 --
816 l_min_date_to := l_parent_date_to;
817 --
818 For csr_get_mappings_rec in csr_get_mappings(l_parent_table_name) loop
819 hr_utility.set_location(l_proc, 300);
820 l_max_date_from := l_parent_date_from;
821 l_min_date_to := l_parent_date_to;
822 If csr_get_mappings_rec.max_end_date > l_max_date_from then
823 hr_utility.set_location(l_proc, 310);
824 l_max_date_from := csr_get_mappings_rec.max_end_date;
825 End If;
826 If csr_get_mappings_rec.grade_id is not null then
827 hr_utility.set_location(l_proc, 320);
828 open csr_get_grade_dates(csr_get_mappings_rec.grade_id);
829 fetch csr_get_grade_dates into l_date_from, l_date_to;
830 close csr_get_grade_dates;
831 hr_utility.set_location(l_proc, 330);
832 If l_date_from > l_max_date_from then
833 hr_utility.set_location(l_proc, 340);
834 l_max_date_from := l_date_from;
835 End If;
836 If l_date_to < l_min_date_to then
837 hr_utility.set_location(l_proc, 350);
838 l_min_date_to := l_date_to;
839 End If;
840 End If;
841 --
842 If csr_get_mappings_rec.company_organization_id is not null then
843 hr_utility.set_location(l_proc, 360);
844 open csr_get_org_dates(csr_get_mappings_rec.company_organization_id);
848 If l_date_from > l_max_date_from then
845 fetch csr_get_org_dates into l_date_from, l_date_to;
846 close csr_get_org_dates;
847 hr_utility.set_location(l_proc, 370);
849 hr_utility.set_location(l_proc, 380);
850 l_max_date_from := l_date_from;
851 End If;
852 If l_date_to < l_min_date_to then
853 hr_utility.set_location(l_proc, 390);
854 l_min_date_to := l_date_to;
855 End If;
856 hr_utility.set_location(l_proc, 392);
857 End If;
858 --
859 hr_utility.set_location(l_proc||' Location ID: '||csr_get_mappings_rec.location_id,395);
860 hr_utility.set_location(l_proc||' Grade ID: '||csr_get_mappings_rec.grade_id,396);
861 hr_utility.set_location(l_proc||' Org ID: '||csr_get_mappings_rec.company_organization_id,397);
862 hr_utility.set_location(l_proc||' Comp Age Code: '||csr_get_mappings_rec.company_age_code,398);
863 --
864 For csr_get_ssl_ids_rec in csr_get_ssl_ids(csr_get_mappings_rec.survey_job_name_code
865 ,csr_get_mappings_rec.survey_region_code
866 ,csr_get_mappings_rec.survey_seniority_code
867 ,csr_get_mappings_rec.company_size_code
868 ,csr_get_mappings_rec.industry_code
869 ,l_max_date_from
870 ,l_min_date_to) Loop
871 hr_utility.set_location(l_proc||' SSL ID: '||csr_get_ssl_ids_rec.salary_survey_line_id,399);
872 --
873 hr_utility.set_location(l_proc, 400);
874 create_mapping
875 (p_validate => FALSE
876 ,p_effective_date => l_effective_date
877 ,p_business_group_id => p_business_group_id
878 ,p_parent_id => p_position_id
879 ,p_parent_table_name => l_parent_table_name
880 ,p_salary_survey_line_id => csr_get_ssl_ids_rec.salary_survey_line_id
881 ,p_location_id => csr_get_mappings_rec.location_id
882 ,p_grade_id => csr_get_mappings_rec.grade_id
883 ,p_company_organization_id => csr_get_mappings_rec.company_organization_id
884 ,p_company_age_code => csr_get_mappings_rec.company_age_code
885 ,p_salary_survey_mapping_id => l_salary_survey_mapping_id
886 ,p_object_version_number => l_object_version_number
887 );
888 hr_utility.set_location(l_proc, 410);
889 End loop;
890 End Loop;
891 End If;
892 End If;
893 hr_utility.set_location(l_proc, 500);
894 --
895 -- Call After Process User Hook
896 --
897 /*
898 begin
899 per_ssm_api_bk4.mass_update_a
900 (p_effective_date => p_effective_date
901 ,p_business_group_id => p_business_group_id
902 ,p_job_id => p_job_id
903 ,p_position_id => p_position_id
904 );
905 exception
906 when hr_api.cannot_find_prog_unit then
907 hr_api.cannot_find_prog_unit_error
908 (p_module_name => 'MASS_UPDATE'
909 ,p_hook_type => 'AP'
910 );
911 end;
912 */
913 hr_utility.set_location(l_proc, 510);
914 --
915 -- When in validation only mode raise the Validate_Enabled exception
916 --
917 if p_validate then
918 raise hr_api.validate_enabled;
919 end if;
920 --
921 -- Set all output arguments
922 --
923 hr_utility.set_location(' Leaving:'||l_proc, 520);
924 exception
925 when hr_api.validate_enabled then
926 --
927 -- As the Validate_Enabled exception has been raised
928 -- we must rollback to the savepoint
929 --
930 rollback to mass_update;
931 --
932 hr_utility.set_location(' Leaving:'||l_proc, 530);
933 when others then
934 --
935 -- A validation or unexpected error has occured
936 --
937 rollback to mass_update;
938 hr_utility.set_location(' Leaving:'||l_proc, 540);
939 raise;
940 end mass_update;
941 --
942 end hr_salary_survey_mapping_api;