[Home] [Help]
PACKAGE BODY: APPS.HR_SALARY_SURVEY_LINE_API
Source
1 Package Body hr_salary_survey_line_api as
2 /* $Header: pesslapi.pkb 120.0 2005/05/31 21:44:17 appldev noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hr_salary_survey_line_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------------< create_salary_survey_line>-------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_salary_survey_line
13 (p_validate in boolean default false
14 ,p_salary_survey_id in number
15 ,p_survey_job_name_code in varchar2
16 ,p_start_date in date
17 ,p_currency_code in varchar2
18 ,p_survey_region_code in varchar2 default null
19 ,p_survey_seniority_code in varchar2 default null
20 ,p_company_size_code in varchar2 default null
21 ,p_industry_code in varchar2 default null
22 ,p_survey_age_code in varchar2 default null
23 ,p_end_date in date default null
24 ,p_differential in number default null
25 ,p_minimum_pay in number default null
26 ,p_mean_pay in number default null
27 ,p_maximum_pay in number default null
28 ,p_graduate_pay in number default null
29 ,p_starting_pay in number default null
30 ,p_percentage_change in number default null
31 ,p_job_first_quartile in number default null
32 ,p_job_median_quartile in number default null
33 ,p_job_third_quartile in number default null
34 ,p_job_fourth_quartile in number default null
35 ,p_minimum_total_compensation in number default null
36 ,p_mean_total_compensation in number default null
37 ,p_maximum_total_compensation in number default null
38 ,p_compnstn_first_quartile in number default null
39 ,p_compnstn_median_quartile in number default null
40 ,p_compnstn_third_quartile in number default null
41 ,p_compnstn_fourth_quartile in number default null
42 /*Added for Enhancement 4021737 */
43 ,p_tenth_percentile in number default null
44 ,p_twenty_fifth_percentile in number default null
45 ,p_fiftieth_percentile in number default null
46 ,p_seventy_fifth_percentile in number default null
47 ,p_ninetieth_percentile in number default null
48 ,p_minimum_bonus in number default null
49 ,p_mean_bonus in number default null
50 ,p_maximum_bonus in number default null
51 ,p_minimum_salary_increase in number default null
52 ,p_mean_salary_increase in number default null
53 ,p_maximum_salary_increase in number default null
54 ,p_min_variable_compensation in number default null
55 ,p_mean_variable_compensation in number default null
56 ,p_max_variable_compensation in number default null
57 ,p_minimum_stock in number default null
58 ,p_mean_stock in number default null
59 ,p_maximum_stock in number default null
60 ,p_stock_display_type in varchar2 default null
61 /*End Enhancement 4021737 */
62 ,p_effective_date in date default null
63 ,p_attribute_category in varchar2 default null
64 ,p_attribute1 in varchar2 default null
65 ,p_attribute2 in varchar2 default null
66 ,p_attribute3 in varchar2 default null
67 ,p_attribute4 in varchar2 default null
68 ,p_attribute5 in varchar2 default null
69 ,p_attribute6 in varchar2 default null
70 ,p_attribute7 in varchar2 default null
71 ,p_attribute8 in varchar2 default null
72 ,p_attribute9 in varchar2 default null
73 ,p_attribute10 in varchar2 default null
74 ,p_attribute11 in varchar2 default null
75 ,p_attribute12 in varchar2 default null
76 ,p_attribute13 in varchar2 default null
77 ,p_attribute14 in varchar2 default null
78 ,p_attribute15 in varchar2 default null
79 ,p_attribute16 in varchar2 default null
80 ,p_attribute17 in varchar2 default null
81 ,p_attribute18 in varchar2 default null
82 ,p_attribute19 in varchar2 default null
83 ,p_attribute20 in varchar2 default null
84 /*Added for Enhancement 4021737 */
85 ,p_attribute21 in varchar2 default null
86 ,p_attribute22 in varchar2 default null
87 ,p_attribute23 in varchar2 default null
88 ,p_attribute24 in varchar2 default null
89 ,p_attribute25 in varchar2 default null
90 ,p_attribute26 in varchar2 default null
91 ,p_attribute27 in varchar2 default null
92 ,p_attribute28 in varchar2 default null
93 ,p_attribute29 in varchar2 default null
94 ,p_attribute30 in varchar2 default null
95 /* End Enhancement 4021737*/
96 ,p_salary_survey_line_id out nocopy number
97 ,p_ssl_object_version_number out nocopy number
98 ,p_overlap_warning out nocopy boolean
99 ) is
100 --
101 -- Declare cursors and local variables
102 --
103 l_proc varchar2(72) := g_package||'create_salary_survey_line';
104 --
105 l_salary_survey_line_id per_salary_survey_lines.salary_survey_line_id%TYPE;
106 l_ssl_object_version_number per_salary_survey_lines.object_version_number%TYPE;
107 --
108 l_eot date := hr_general.End_of_time;
109 --
110 l_start_date date;
111 l_end_date date;
112 --
113 l_overlap_warning boolean := false;
114 --
115 c_salary_survey_line_id per_salary_survey_lines.salary_survey_line_id%TYPE;
116 --
117 cursor csr_date_overlap is
118 select salary_survey_line_id
119 from per_salary_survey_lines
120 where l_start_date > start_date
121 and end_date is null
122 and survey_job_name_code = p_survey_job_name_code
123 and nvl(survey_region_code,hr_api.g_varchar2)
124 = nvl(p_survey_region_code,hr_api.g_varchar2)
125 and nvl(survey_seniority_code,hr_api.g_varchar2)
126 = nvl(p_survey_seniority_code,hr_api.g_varchar2)
127 and nvl(company_size_code,hr_api.g_varchar2)
128 = nvl(p_company_size_code,hr_api.g_varchar2)
129 and nvl(industry_code,hr_api.g_varchar2)
130 = nvl(p_industry_code,hr_api.g_varchar2)
131 and nvl(survey_age_code,hr_api.g_varchar2)
132 = nvl(p_survey_age_code,hr_api.g_varchar2)
133 and salary_survey_line_id <> nvl(p_salary_survey_line_id,hr_api.g_number)
134 and salary_survey_id = nvl(p_salary_survey_id,hr_api.g_number);
135 --
136 begin
137 --
138 hr_utility.set_location('Entering:'|| l_proc, 10);
139 --
140 -- Issue a savepoint
141 --
142 savepoint create_salary_survey_line;
143 --
144 hr_utility.set_location(l_proc, 20);
145 --
146 -- Truncate the time portion from IN date parameters to be stored in the database.
147 --
148 l_start_date := trunc(p_start_date);
149 l_end_date := trunc(p_end_date);
150 --
151 -- Call Before Process User Hook
152 --
153 begin
154 per_salary_survey_line_bk1.create_salary_survey_line_b
155 (p_salary_survey_id => p_salary_survey_id,
156 p_survey_job_name_code => p_survey_job_name_code,
157 p_survey_region_code => p_survey_region_code,
158 p_survey_seniority_code => p_survey_seniority_code,
159 p_company_size_code => p_company_size_code,
160 p_industry_code => p_industry_code,
161 p_survey_age_code => p_survey_age_code,
162 p_start_date => l_start_date,
163 p_end_date => l_end_date,
164 p_currency_code => p_currency_code,
165 p_differential => p_differential,
166 p_minimum_pay => p_minimum_pay,
167 p_mean_pay => p_mean_pay,
168 p_maximum_pay => p_maximum_pay,
169 p_graduate_pay => p_graduate_pay,
170 p_starting_pay => p_starting_pay,
171 p_percentage_change => p_percentage_change,
172 p_job_first_quartile => p_job_first_quartile,
173 p_job_median_quartile => p_job_median_quartile,
174 p_job_third_quartile => p_job_third_quartile,
175 p_job_fourth_quartile => p_job_fourth_quartile,
176 p_minimum_total_compensation => p_minimum_total_compensation,
177 p_mean_total_compensation => p_mean_total_compensation,
178 p_maximum_total_compensation => p_maximum_total_compensation,
179 p_compnstn_first_quartile => p_compnstn_first_quartile,
180 p_compnstn_median_quartile => p_compnstn_median_quartile,
181 p_compnstn_third_quartile => p_compnstn_third_quartile,
182 p_compnstn_fourth_quartile => p_compnstn_fourth_quartile,
183 /*Added for Enhancement 4021737 */
184 p_tenth_percentile => p_tenth_percentile,
185 p_twenty_fifth_percentile => p_twenty_fifth_percentile,
186 p_fiftieth_percentile => p_fiftieth_percentile,
187 p_seventy_fifth_percentile => p_seventy_fifth_percentile,
188 p_ninetieth_percentile => p_ninetieth_percentile,
189 p_minimum_bonus => p_minimum_bonus,
190 p_mean_bonus => p_mean_bonus,
191 p_maximum_bonus => p_maximum_bonus,
192 p_minimum_salary_increase => p_minimum_salary_increase,
193 p_mean_salary_increase => p_mean_salary_increase,
194 p_maximum_salary_increase => p_maximum_salary_increase,
195 p_min_variable_compensation => p_min_variable_compensation,
196 p_mean_variable_compensation => p_mean_variable_compensation,
197 p_max_variable_compensation => p_max_variable_compensation,
198 p_minimum_stock => p_minimum_stock,
199 p_mean_stock => p_mean_stock,
200 p_maximum_stock => p_maximum_stock,
201 p_stock_display_type => p_stock_display_type,
202 /* End Enhancement 4021737 */
203 p_effective_date => p_effective_date,
204 p_attribute_category => p_attribute_category,
205 p_attribute1 => p_attribute1,
206 p_attribute2 => p_attribute2,
207 p_attribute3 => p_attribute3,
208 p_attribute4 => p_attribute4,
209 p_attribute5 => p_attribute5,
210 p_attribute6 => p_attribute6,
211 p_attribute7 => p_attribute7,
212 p_attribute8 => p_attribute8,
213 p_attribute9 => p_attribute9,
214 p_attribute10 => p_attribute10,
215 p_attribute11 => p_attribute11,
216 p_attribute12 => p_attribute12,
217 p_attribute13 => p_attribute13,
218 p_attribute14 => p_attribute14,
219 p_attribute15 => p_attribute15,
220 p_attribute16 => p_attribute16,
221 p_attribute17 => p_attribute17,
222 p_attribute18 => p_attribute18,
223 p_attribute19 => p_attribute19,
224 p_attribute20 => p_attribute20,
225 /*Added for Enhancement 4021737 */
226 p_attribute21 => p_attribute21,
227 p_attribute22 => p_attribute22,
228 p_attribute23 => p_attribute23,
229 p_attribute24 => p_attribute24,
230 p_attribute25 => p_attribute25,
231 p_attribute26 => p_attribute26,
232 p_attribute27 => p_attribute27,
233 p_attribute28 => p_attribute28,
234 p_attribute29 => p_attribute29,
235 p_attribute30 => p_attribute30
236 /* End Enhancement 4021737 */
237 );
238 exception
239 when hr_api.cannot_find_prog_unit then
240 hr_api.cannot_find_prog_unit_error
241 (p_module_name => 'create_salary_survey_line'
242 ,p_hook_type => 'BP'
243 );
244 end;
245 --
246 hr_utility.set_location(l_proc, 30);
247 --
248 -- Validation in addition to Row Handlers
249 --
250 -- If we have a date overlap and the end_date of the overlapping
251 -- row found in the database is null then set the end date of the
252 -- overlapping row in the database to a day before the start_date
253 -- of the new row and then set the warning parameter to true.
254 --
255 open csr_date_overlap;
256 --
257 c_salary_survey_line_id := null;
258 --
259 fetch csr_date_overlap into c_salary_survey_line_id;
260 --
261 --
262 --
263 if c_salary_survey_line_id is not null then
264 --
265 --
266 --
267 update per_salary_survey_lines
268 set end_date = (l_start_date - 1)
269 where salary_survey_line_id = c_salary_survey_line_id;
270 --
271 l_overlap_warning := true;
272 --
273 end if;
274 --
275 close csr_date_overlap;
276 --
277 hr_utility.set_location(l_proc, 40);
278 --
279 -- Process Logic
280 --
281 per_ssl_ins.ins(p_salary_survey_line_id => l_salary_survey_line_id,
282 p_object_version_number => l_ssl_object_version_number,
283 p_salary_survey_id => p_salary_survey_id,
284 p_survey_job_name_code => p_survey_job_name_code,
285 p_survey_region_code => p_survey_region_code,
286 p_survey_seniority_code => p_survey_seniority_code,
287 p_company_size_code => p_company_size_code,
288 p_industry_code => p_industry_code,
289 p_survey_age_code => p_survey_age_code,
290 p_start_date => l_start_date,
291 p_end_date => l_end_date,
292 p_currency_code => p_currency_code,
293 p_differential => p_differential,
294 p_minimum_pay => p_minimum_pay,
295 p_mean_pay => p_mean_pay,
296 p_maximum_pay => p_maximum_pay,
297 p_graduate_pay => p_graduate_pay,
298 p_starting_pay => p_starting_pay,
299 p_percentage_change => p_percentage_change,
300 p_job_first_quartile => p_job_first_quartile,
301 p_job_median_quartile => p_job_median_quartile,
302 p_job_third_quartile => p_job_third_quartile,
303 p_job_fourth_quartile => p_job_fourth_quartile,
304 p_minimum_total_compensation => p_minimum_total_compensation,
305 p_mean_total_compensation => p_mean_total_compensation,
306 p_maximum_total_compensation => p_maximum_total_compensation,
307 p_compnstn_first_quartile => p_compnstn_first_quartile,
308 p_compnstn_median_quartile => p_compnstn_median_quartile,
309 p_compnstn_third_quartile => p_compnstn_third_quartile,
310 p_compnstn_fourth_quartile => p_compnstn_fourth_quartile,
311 /*Added for Enhancement 4021737 */
312 p_tenth_percentile => p_tenth_percentile,
313 p_twenty_fifth_percentile => p_twenty_fifth_percentile,
314 p_fiftieth_percentile => p_fiftieth_percentile,
315 p_seventy_fifth_percentile => p_seventy_fifth_percentile,
316 p_ninetieth_percentile => p_ninetieth_percentile,
317 p_minimum_bonus => p_minimum_bonus,
318 p_mean_bonus => p_mean_bonus,
319 p_maximum_bonus => p_maximum_bonus,
320 p_minimum_salary_increase => p_minimum_salary_increase,
321 p_mean_salary_increase => p_mean_salary_increase,
322 p_maximum_salary_increase => p_maximum_salary_increase,
323 p_min_variable_compensation => p_min_variable_compensation,
324 p_mean_variable_compensation => p_mean_variable_compensation,
325 p_max_variable_compensation => p_max_variable_compensation,
326 p_minimum_stock => p_minimum_stock,
327 p_mean_stock => p_mean_stock,
328 p_maximum_stock => p_maximum_stock,
329 p_stock_display_type => p_stock_display_type,
330 /* End Enhancement 4021737 */
331 p_effective_date => p_effective_date,
332 p_attribute_category => p_attribute_category,
333 p_attribute1 => p_attribute1,
334 p_attribute2 => p_attribute2,
335 p_attribute3 => p_attribute3,
336 p_attribute4 => p_attribute4,
337 p_attribute5 => p_attribute5,
338 p_attribute6 => p_attribute6,
339 p_attribute7 => p_attribute7,
340 p_attribute8 => p_attribute8,
341 p_attribute9 => p_attribute9,
342 p_attribute10 => p_attribute10,
343 p_attribute11 => p_attribute11,
344 p_attribute12 => p_attribute12,
345 p_attribute13 => p_attribute13,
346 p_attribute14 => p_attribute14,
347 p_attribute15 => p_attribute15,
348 p_attribute16 => p_attribute16,
349 p_attribute17 => p_attribute17,
350 p_attribute18 => p_attribute18,
351 p_attribute19 => p_attribute19,
352 p_attribute20 => p_attribute20,
353 /* Added for Enhancement 4021737 */
354 p_attribute21 => p_attribute21,
355 p_attribute22 => p_attribute22,
356 p_attribute23 => p_attribute23,
357 p_attribute24 => p_attribute24,
358 p_attribute25 => p_attribute25,
359 p_attribute26 => p_attribute26,
360 p_attribute27 => p_attribute27,
361 p_attribute28 => p_attribute28,
362 p_attribute29 => p_attribute29,
363 p_attribute30 => p_attribute30
364 /* End Enhancement 4021737 */
365 );
366 hr_utility.set_location(l_proc, 50);
367 --
368 -- Call After Process User Hook
369 --
370 begin
371 per_salary_survey_line_bk1.create_salary_survey_line_a
372 (p_salary_survey_id => p_salary_survey_id,
373 p_survey_job_name_code => p_survey_job_name_code,
374 p_survey_region_code => p_survey_region_code,
375 p_survey_seniority_code => p_survey_seniority_code,
376 p_company_size_code => p_company_size_code,
377 p_industry_code => p_industry_code,
378 p_survey_age_code => p_survey_age_code,
379 p_start_date => l_start_date,
380 p_end_date => l_end_date,
381 p_currency_code => p_currency_code,
382 p_differential => p_differential,
383 p_minimum_pay => p_minimum_pay,
384 p_mean_pay => p_mean_pay,
385 p_maximum_pay => p_maximum_pay,
386 p_graduate_pay => p_graduate_pay,
387 p_starting_pay => p_starting_pay,
388 p_percentage_change => p_percentage_change,
389 p_job_first_quartile => p_job_first_quartile,
390 p_job_median_quartile => p_job_median_quartile,
391 p_job_third_quartile => p_job_third_quartile,
392 p_job_fourth_quartile => p_job_fourth_quartile,
393 p_minimum_total_compensation => p_minimum_total_compensation,
394 p_mean_total_compensation => p_mean_total_compensation,
395 p_maximum_total_compensation => p_maximum_total_compensation,
396 p_compnstn_first_quartile => p_compnstn_first_quartile,
397 p_compnstn_median_quartile => p_compnstn_median_quartile,
398 p_compnstn_third_quartile => p_compnstn_third_quartile,
399 p_compnstn_fourth_quartile => p_compnstn_fourth_quartile,
400 /*Added for Enhancement 4021737 */
401 p_tenth_percentile => p_tenth_percentile,
402 p_twenty_fifth_percentile => p_twenty_fifth_percentile,
403 p_fiftieth_percentile => p_fiftieth_percentile,
404 p_seventy_fifth_percentile => p_seventy_fifth_percentile,
405 p_ninetieth_percentile => p_ninetieth_percentile,
406 p_minimum_bonus => p_minimum_bonus,
407 p_mean_bonus => p_mean_bonus,
408 p_maximum_bonus => p_maximum_bonus,
409 p_minimum_salary_increase => p_minimum_salary_increase,
410 p_mean_salary_increase => p_mean_salary_increase,
411 p_maximum_salary_increase => p_maximum_salary_increase,
412 p_min_variable_compensation => p_min_variable_compensation,
413 p_mean_variable_compensation => p_mean_variable_compensation,
414 p_max_variable_compensation => p_max_variable_compensation,
415 p_minimum_stock => p_minimum_stock,
416 p_mean_stock => p_mean_stock,
417 p_maximum_stock => p_maximum_stock,
418 p_stock_display_type => p_stock_display_type,
419 /* End Enhancement 4021737 */
420 p_effective_date => p_effective_date,
421 p_attribute_category => p_attribute_category,
422 p_attribute1 => p_attribute1,
423 p_attribute2 => p_attribute2,
424 p_attribute3 => p_attribute3,
425 p_attribute4 => p_attribute4,
426 p_attribute5 => p_attribute5,
427 p_attribute6 => p_attribute6,
428 p_attribute7 => p_attribute7,
429 p_attribute8 => p_attribute8,
430 p_attribute9 => p_attribute9,
431 p_attribute10 => p_attribute10,
432 p_attribute11 => p_attribute11,
433 p_attribute12 => p_attribute12,
434 p_attribute13 => p_attribute13,
435 p_attribute14 => p_attribute14,
436 p_attribute15 => p_attribute15,
437 p_attribute16 => p_attribute16,
438 p_attribute17 => p_attribute17,
439 p_attribute18 => p_attribute18,
440 p_attribute19 => p_attribute19,
441 p_attribute20 => p_attribute20,
442 /*Added for Enhancement 4021737 */
443 p_attribute21 => p_attribute21,
444 p_attribute22 => p_attribute22,
445 p_attribute23 => p_attribute23,
446 p_attribute24 => p_attribute24,
447 p_attribute25 => p_attribute25,
448 p_attribute26 => p_attribute26,
449 p_attribute27 => p_attribute27,
450 p_attribute28 => p_attribute28,
451 p_attribute29 => p_attribute29,
452 p_attribute30 => p_attribute30,
453 /* End Enhancement 4021737 */
454 p_salary_survey_line_id => l_salary_survey_line_id,
455 p_ssl_object_version_number => l_ssl_object_version_number,
456 p_overlap_warning => l_overlap_warning
457 );
458 exception
459 when hr_api.cannot_find_prog_unit then
460 hr_api.cannot_find_prog_unit_error
461 (p_module_name => 'create_salary_survey_line'
462 ,p_hook_type => 'AP'
463 );
464 end;
465 hr_utility.set_location(l_proc, 60);
466 --
467 -- When in validation only mode raise the Validate_Enabled exception
468 --
469 if p_validate then
470 --
471 raise hr_api.validate_enabled;
472 --
473 end if;
474 --
475 -- Set all output arguments
476 --
477 p_salary_survey_line_id := l_salary_survey_line_id;
478 p_ssl_object_version_number := l_ssl_object_version_number;
479 p_overlap_warning := l_overlap_warning;
480 --
481 hr_utility.set_location(' Leaving:'||l_proc, 70);
482 --
483 exception
484 when hr_api.validate_enabled then
485 --
486 -- As the Validate_Enabled exception has been raised
487 -- we must rollback to the savepoint
488 --
489 rollback to create_salary_survey_line;
490 --
491 -- Only set output warning arguments
492 -- (Any key or derived arguments must be set to null
493 -- when validation only mode is being used.)
494 --
495 p_salary_survey_line_id := null;
496 p_ssl_object_version_number := null;
497 p_overlap_warning := l_overlap_warning;
498 --
499 hr_utility.set_location(' Leaving:'||l_proc, 80);
500 --
501 when others then
502 --
503 -- A validation or unexpected error has occured
504 --
505 p_salary_survey_line_id := null;
506 p_ssl_object_version_number := null;
507 p_overlap_warning := null;
508 rollback to create_salary_survey_line;
509 --
510 hr_utility.set_location(' Leaving:'||l_proc, 90);
511 --
512 raise;
513 --
514 end create_salary_survey_line;
515 --
516 --
517 -- ----------------------------------------------------------------------------
518 -- |------------------------< update_salary_survey_line >---------------------------|
519 -- ----------------------------------------------------------------------------
520 --
521 procedure update_salary_survey_line
522 (p_validate in boolean default false
523 ,p_survey_job_name_code in varchar2
524 ,p_start_date in date
525 ,p_currency_code in varchar2 default hr_api.g_varchar2
526 ,p_survey_region_code in varchar2 default hr_api.g_varchar2
527 ,p_survey_seniority_code in varchar2 default hr_api.g_varchar2
528 ,p_company_size_code in varchar2 default hr_api.g_varchar2
529 ,p_industry_code in varchar2 default hr_api.g_varchar2
530 ,p_survey_age_code in varchar2 default hr_api.g_varchar2
531 ,p_end_date in date default hr_api.g_date
532 ,p_differential in number default hr_api.g_number
533 ,p_minimum_pay in number default hr_api.g_number
534 ,p_mean_pay in number default hr_api.g_number
535 ,p_maximum_pay in number default hr_api.g_number
536 ,p_graduate_pay in number default hr_api.g_number
537 ,p_starting_pay in number default hr_api.g_number
538 ,p_percentage_change in number default hr_api.g_number
539 ,p_job_first_quartile in number default hr_api.g_number
540 ,p_job_median_quartile in number default hr_api.g_number
541 ,p_job_third_quartile in number default hr_api.g_number
542 ,p_job_fourth_quartile in number default hr_api.g_number
543 ,p_minimum_total_compensation in number default hr_api.g_number
544 ,p_mean_total_compensation in number default hr_api.g_number
545 ,p_maximum_total_compensation in number default hr_api.g_number
546 ,p_compnstn_first_quartile in number default hr_api.g_number
547 ,p_compnstn_median_quartile in number default hr_api.g_number
548 ,p_compnstn_third_quartile in number default hr_api.g_number
549 ,p_compnstn_fourth_quartile in number default hr_api.g_number
550 /*Added for Enhancement 4021737 */
551 ,p_tenth_percentile in number default hr_api.g_number
552 ,p_twenty_fifth_percentile in number default hr_api.g_number
553 ,p_fiftieth_percentile in number default hr_api.g_number
554 ,p_seventy_fifth_percentile in number default hr_api.g_number
555 ,p_ninetieth_percentile in number default hr_api.g_number
556 ,p_minimum_bonus in number default hr_api.g_number
557 ,p_mean_bonus in number default hr_api.g_number
558 ,p_maximum_bonus in number default hr_api.g_number
559 ,p_minimum_salary_increase in number default hr_api.g_number
560 ,p_mean_salary_increase in number default hr_api.g_number
561 ,p_maximum_salary_increase in number default hr_api.g_number
562 ,p_min_variable_compensation in number default hr_api.g_number
563 ,p_mean_variable_compensation in number default hr_api.g_number
564 ,p_max_variable_compensation in number default hr_api.g_number
565 ,p_minimum_stock in number default hr_api.g_number
566 ,p_mean_stock in number default hr_api.g_number
567 ,p_maximum_stock in number default hr_api.g_number
568 ,p_stock_display_type in varchar2 default hr_api.g_varchar2
569 /* End Enhancement 4021737 */
570 ,p_effective_date in date default hr_api.g_date
571 ,p_attribute_category in varchar2 default hr_api.g_varchar2
572 ,p_attribute1 in varchar2 default hr_api.g_varchar2
573 ,p_attribute2 in varchar2 default hr_api.g_varchar2
574 ,p_attribute3 in varchar2 default hr_api.g_varchar2
575 ,p_attribute4 in varchar2 default hr_api.g_varchar2
576 ,p_attribute5 in varchar2 default hr_api.g_varchar2
577 ,p_attribute6 in varchar2 default hr_api.g_varchar2
578 ,p_attribute7 in varchar2 default hr_api.g_varchar2
579 ,p_attribute8 in varchar2 default hr_api.g_varchar2
580 ,p_attribute9 in varchar2 default hr_api.g_varchar2
581 ,p_attribute10 in varchar2 default hr_api.g_varchar2
582 ,p_attribute11 in varchar2 default hr_api.g_varchar2
583 ,p_attribute12 in varchar2 default hr_api.g_varchar2
584 ,p_attribute13 in varchar2 default hr_api.g_varchar2
585 ,p_attribute14 in varchar2 default hr_api.g_varchar2
586 ,p_attribute15 in varchar2 default hr_api.g_varchar2
587 ,p_attribute16 in varchar2 default hr_api.g_varchar2
588 ,p_attribute17 in varchar2 default hr_api.g_varchar2
589 ,p_attribute18 in varchar2 default hr_api.g_varchar2
590 ,p_attribute19 in varchar2 default hr_api.g_varchar2
591 ,p_attribute20 in varchar2 default hr_api.g_varchar2
592 /*Added for Enhancement 4021737 */
593 ,p_attribute21 in varchar2 default hr_api.g_varchar2
594 ,p_attribute22 in varchar2 default hr_api.g_varchar2
595 ,p_attribute23 in varchar2 default hr_api.g_varchar2
596 ,p_attribute24 in varchar2 default hr_api.g_varchar2
597 ,p_attribute25 in varchar2 default hr_api.g_varchar2
598 ,p_attribute26 in varchar2 default hr_api.g_varchar2
599 ,p_attribute27 in varchar2 default hr_api.g_varchar2
600 ,p_attribute28 in varchar2 default hr_api.g_varchar2
601 ,p_attribute29 in varchar2 default hr_api.g_varchar2
602 ,p_attribute30 in varchar2 default hr_api.g_varchar2
603 /* End Enhancement 4021737 */
604 ,p_salary_survey_line_id in number
605 ,p_ssl_object_version_number in out nocopy number
606 ) is
607 --
608 -- Declare cursors and local variables
609 --
610 l_proc varchar2(72) := g_package||'update_salary_survey_line';
611 --
612 l_start_date date;
613 l_end_date date;
614 --
615 l_salary_survey_line_id per_salary_survey_lines.salary_survey_line_id%TYPE;
616 l_ssl_object_version_number per_salary_survey_lines.object_version_number%TYPE;
617 --
618 begin
619 --
620 hr_utility.set_location('Entering:'|| l_proc, 10);
621 --
622 -- Issue a savepoint
623 --
624 savepoint update_salary_survey_line;
625 --
626 hr_utility.set_location(l_proc, 20);
627 --
628 -- Truncate tine portion from all IN date parameters being stored in
629 -- the database.
630 l_start_date := trunc(p_start_date);
631 l_end_date := trunc(p_end_date);
632 --
633 -- Call Before Process User Hook
634 --
635 l_salary_survey_line_id := p_salary_survey_line_id;
636 l_ssl_object_version_number := p_ssl_object_version_number;
637 --
638 begin
639 per_salary_survey_line_bk2.update_salary_survey_line_b
640 (p_survey_job_name_code => p_survey_job_name_code,
641 p_survey_region_code => p_survey_region_code,
642 p_survey_seniority_code => p_survey_seniority_code,
643 p_company_size_code => p_company_size_code,
644 p_industry_code => p_industry_code,
645 p_survey_age_code => p_survey_age_code,
646 p_start_date => l_start_date,
647 p_end_date => l_end_date,
648 p_currency_code => p_currency_code,
649 p_differential => p_differential,
650 p_minimum_pay => p_minimum_pay,
651 p_mean_pay => p_mean_pay,
652 p_maximum_pay => p_maximum_pay,
653 p_graduate_pay => p_graduate_pay,
654 p_starting_pay => p_starting_pay,
655 p_percentage_change => p_percentage_change,
656 p_job_first_quartile => p_job_first_quartile,
657 p_job_median_quartile => p_job_median_quartile,
658 p_job_third_quartile => p_job_third_quartile,
659 p_job_fourth_quartile => p_job_fourth_quartile,
660 p_minimum_total_compensation => p_minimum_total_compensation,
661 p_mean_total_compensation => p_mean_total_compensation,
662 p_maximum_total_compensation => p_maximum_total_compensation,
663 p_compnstn_first_quartile => p_compnstn_first_quartile,
664 p_compnstn_median_quartile => p_compnstn_median_quartile,
665 p_compnstn_third_quartile => p_compnstn_third_quartile,
666 p_compnstn_fourth_quartile => p_compnstn_fourth_quartile,
667 /*Added for Enhancement 4021737 */
668 p_tenth_percentile => p_tenth_percentile,
669 p_twenty_fifth_percentile => p_twenty_fifth_percentile,
670 p_fiftieth_percentile => p_fiftieth_percentile,
671 p_seventy_fifth_percentile => p_seventy_fifth_percentile,
672 p_ninetieth_percentile => p_ninetieth_percentile,
673 p_minimum_bonus => p_minimum_bonus,
674 p_mean_bonus => p_mean_bonus,
675 p_maximum_bonus => p_maximum_bonus,
676 p_minimum_salary_increase => p_minimum_salary_increase,
677 p_mean_salary_increase => p_mean_salary_increase,
678 p_maximum_salary_increase => p_maximum_salary_increase,
679 p_min_variable_compensation => p_min_variable_compensation,
680 p_mean_variable_compensation => p_mean_variable_compensation,
681 p_max_variable_compensation => p_max_variable_compensation,
682 p_minimum_stock => p_minimum_stock,
683 p_mean_stock => p_mean_stock,
684 p_maximum_stock => p_maximum_stock,
685 p_stock_display_type => p_stock_display_type,
686 /* End Enhancement 4021737 */
687 p_effective_date => p_effective_date,
688 p_attribute_category => p_attribute_category,
689 p_attribute1 => p_attribute1,
690 p_attribute2 => p_attribute2,
691 p_attribute3 => p_attribute3,
692 p_attribute4 => p_attribute4,
693 p_attribute5 => p_attribute5,
694 p_attribute6 => p_attribute6,
695 p_attribute7 => p_attribute7,
696 p_attribute8 => p_attribute8,
697 p_attribute9 => p_attribute9,
698 p_attribute10 => p_attribute10,
699 p_attribute11 => p_attribute11,
700 p_attribute12 => p_attribute12,
701 p_attribute13 => p_attribute13,
702 p_attribute14 => p_attribute14,
703 p_attribute15 => p_attribute15,
704 p_attribute16 => p_attribute16,
705 p_attribute17 => p_attribute17,
706 p_attribute18 => p_attribute18,
707 p_attribute19 => p_attribute19,
708 p_attribute20 => p_attribute20,
709 p_attribute21 => p_attribute21,
710 p_attribute22 => p_attribute22,
711 p_attribute23 => p_attribute23,
712 p_attribute24 => p_attribute24,
713 p_attribute25 => p_attribute25,
714 p_attribute26 => p_attribute26,
715 p_attribute27 => p_attribute27,
716 p_attribute28 => p_attribute28,
717 p_attribute29 => p_attribute29,
718 p_attribute30 => p_attribute30,
719 p_ssl_object_version_number => l_ssl_object_version_number
720 );
721 exception
722 when hr_api.cannot_find_prog_unit then
723 hr_api.cannot_find_prog_unit_error
724 (p_module_name => 'update_salary_survey_line'
725 ,p_hook_type => 'BP'
726 );
727 end;
728 hr_utility.set_location(l_proc, 30);
729 --
730 -- Process Logic
731 --
732 per_ssl_upd.upd(p_salary_survey_line_id => l_salary_survey_line_id,
733 p_object_version_number => l_ssl_object_version_number,
734 p_survey_job_name_code => p_survey_job_name_code,
735 p_survey_region_code => p_survey_region_code,
736 p_survey_seniority_code => p_survey_seniority_code,
737 p_company_size_code => p_company_size_code,
738 p_industry_code => p_industry_code,
739 p_survey_age_code => p_survey_age_code,
740 p_start_date => l_start_date,
741 p_end_date => l_end_date,
742 p_currency_code => p_currency_code,
743 p_differential => p_differential,
744 p_minimum_pay => p_minimum_pay,
745 p_mean_pay => p_mean_pay,
746 p_maximum_pay => p_maximum_pay,
747 p_graduate_pay => p_graduate_pay,
748 p_starting_pay => p_starting_pay,
749 p_percentage_change => p_percentage_change,
750 p_job_first_quartile => p_job_first_quartile,
751 p_job_median_quartile => p_job_median_quartile,
752 p_job_third_quartile => p_job_third_quartile,
753 p_job_fourth_quartile => p_job_fourth_quartile,
754 p_minimum_total_compensation => p_minimum_total_compensation,
755 p_mean_total_compensation => p_mean_total_compensation,
756 p_maximum_total_compensation => p_maximum_total_compensation,
757 p_compnstn_first_quartile => p_compnstn_first_quartile,
758 p_compnstn_median_quartile => p_compnstn_median_quartile,
759 p_compnstn_third_quartile => p_compnstn_third_quartile,
760 p_compnstn_fourth_quartile => p_compnstn_fourth_quartile,
761 /*Added for Enhancement 4021737 */
762 p_tenth_percentile => p_tenth_percentile,
763 p_twenty_fifth_percentile => p_twenty_fifth_percentile,
764 p_fiftieth_percentile => p_fiftieth_percentile,
765 p_seventy_fifth_percentile => p_seventy_fifth_percentile,
766 p_ninetieth_percentile => p_ninetieth_percentile,
767 p_minimum_bonus => p_minimum_bonus,
768 p_mean_bonus => p_mean_bonus,
769 p_maximum_bonus => p_maximum_bonus,
770 p_minimum_salary_increase => p_minimum_salary_increase,
771 p_mean_salary_increase => p_mean_salary_increase,
772 p_maximum_salary_increase => p_maximum_salary_increase,
773 p_min_variable_compensation => p_min_variable_compensation,
774 p_mean_variable_compensation => p_mean_variable_compensation,
775 p_max_variable_compensation => p_max_variable_compensation,
776 p_minimum_stock => p_minimum_stock,
777 p_mean_stock => p_mean_stock,
778 p_maximum_stock => p_maximum_stock,
779 p_stock_display_type => p_stock_display_type,
780 /* End Enhancement 4021737 */
781 p_effective_date => p_effective_date,
782 p_attribute_category => p_attribute_category,
783 p_attribute1 => p_attribute1,
784 p_attribute2 => p_attribute2,
785 p_attribute3 => p_attribute3,
786 p_attribute4 => p_attribute4,
787 p_attribute5 => p_attribute5,
788 p_attribute6 => p_attribute6,
789 p_attribute7 => p_attribute7,
790 p_attribute8 => p_attribute8,
791 p_attribute9 => p_attribute9,
792 p_attribute10 => p_attribute10,
793 p_attribute11 => p_attribute11,
794 p_attribute12 => p_attribute12,
795 p_attribute13 => p_attribute13,
796 p_attribute14 => p_attribute14,
797 p_attribute15 => p_attribute15,
798 p_attribute16 => p_attribute16,
799 p_attribute17 => p_attribute17,
800 p_attribute18 => p_attribute18,
801 p_attribute19 => p_attribute19,
802 p_attribute20 => p_attribute20,
803 p_attribute21 => p_attribute21,
804 p_attribute22 => p_attribute22,
805 p_attribute23 => p_attribute23,
806 p_attribute24 => p_attribute24,
807 p_attribute25 => p_attribute25,
808 p_attribute26 => p_attribute26,
809 p_attribute27 => p_attribute27,
810 p_attribute28 => p_attribute28,
811 p_attribute29 => p_attribute29,
812 p_attribute30 => p_attribute30
813 );
814 hr_utility.set_location(l_proc, 50);
815 --
816 -- Call After Process User Hook
817 --
818 begin
819 per_salary_survey_line_bk2.update_salary_survey_line_a
820 (p_survey_job_name_code => p_survey_job_name_code,
821 p_survey_region_code => p_survey_region_code,
822 p_survey_seniority_code => p_survey_seniority_code,
823 p_company_size_code => p_company_size_code,
824 p_industry_code => p_industry_code,
825 p_survey_age_code => p_survey_age_code,
826 p_start_date => l_start_date,
827 p_end_date => l_end_date,
828 p_currency_code => p_currency_code,
829 p_differential => p_differential,
830 p_minimum_pay => p_minimum_pay,
831 p_mean_pay => p_mean_pay,
832 p_maximum_pay => p_maximum_pay,
833 p_graduate_pay => p_graduate_pay,
834 p_starting_pay => p_starting_pay,
835 p_percentage_change => p_percentage_change,
836 p_job_first_quartile => p_job_first_quartile,
837 p_job_median_quartile => p_job_median_quartile,
838 p_job_third_quartile => p_job_third_quartile,
839 p_job_fourth_quartile => p_job_fourth_quartile,
840 p_minimum_total_compensation => p_minimum_total_compensation,
841 p_mean_total_compensation => p_mean_total_compensation,
842 p_maximum_total_compensation => p_maximum_total_compensation,
843 p_compnstn_first_quartile => p_compnstn_first_quartile,
844 p_compnstn_median_quartile => p_compnstn_median_quartile,
845 p_compnstn_third_quartile => p_compnstn_third_quartile,
846 p_compnstn_fourth_quartile => p_compnstn_fourth_quartile,
847 /*Added for Enhancement 4021737 */
848 p_tenth_percentile => p_tenth_percentile,
849 p_twenty_fifth_percentile => p_twenty_fifth_percentile,
850 p_fiftieth_percentile => p_fiftieth_percentile,
851 p_seventy_fifth_percentile => p_seventy_fifth_percentile,
852 p_ninetieth_percentile => p_ninetieth_percentile,
853 p_minimum_bonus => p_minimum_bonus,
854 p_mean_bonus => p_mean_bonus,
855 p_maximum_bonus => p_maximum_bonus,
856 p_minimum_salary_increase => p_minimum_salary_increase,
857 p_mean_salary_increase => p_mean_salary_increase,
858 p_maximum_salary_increase => p_maximum_salary_increase,
859 p_min_variable_compensation => p_min_variable_compensation,
860 p_mean_variable_compensation => p_mean_variable_compensation,
861 p_max_variable_compensation => p_max_variable_compensation,
862 p_minimum_stock => p_minimum_stock,
863 p_mean_stock => p_mean_stock,
864 p_maximum_stock => p_maximum_stock,
865 p_stock_display_type => p_stock_display_type,
866 /* End Enhancement 4021737 */
867 p_effective_date => p_effective_date,
868 p_attribute_category => p_attribute_category,
869 p_attribute1 => p_attribute1,
870 p_attribute2 => p_attribute2,
871 p_attribute3 => p_attribute3,
872 p_attribute4 => p_attribute4,
873 p_attribute5 => p_attribute5,
874 p_attribute6 => p_attribute6,
875 p_attribute7 => p_attribute7,
876 p_attribute8 => p_attribute8,
877 p_attribute9 => p_attribute9,
878 p_attribute10 => p_attribute10,
879 p_attribute11 => p_attribute11,
880 p_attribute12 => p_attribute12,
881 p_attribute13 => p_attribute13,
882 p_attribute14 => p_attribute14,
883 p_attribute15 => p_attribute15,
884 p_attribute16 => p_attribute16,
885 p_attribute17 => p_attribute17,
886 p_attribute18 => p_attribute18,
887 p_attribute19 => p_attribute19,
888 p_attribute20 => p_attribute20,
889 p_attribute21 => p_attribute21,
890 p_attribute22 => p_attribute22,
891 p_attribute23 => p_attribute23,
892 p_attribute24 => p_attribute24,
893 p_attribute25 => p_attribute25,
894 p_attribute26 => p_attribute26,
895 p_attribute27 => p_attribute27,
896 p_attribute28 => p_attribute28,
897 p_attribute29 => p_attribute29,
898 p_attribute30 => p_attribute30,
899 p_salary_survey_line_id => l_salary_survey_line_id,
900 p_ssl_object_version_number => l_ssl_object_version_number
901 );
902 exception
903 when hr_api.cannot_find_prog_unit then
904 hr_api.cannot_find_prog_unit_error
905 (p_module_name => 'update_salary_survey_line'
906 ,p_hook_type => 'AP'
907 );
908 end;
909 --
910 hr_utility.set_location(l_proc, 60);
911 --
912 -- When in validation only mode raise the Validate_Enabled exception
913 --
914 if p_validate then
915 --
916 raise hr_api.validate_enabled;
917 --
918 end if;
919 --
920 -- Set all output arguments
921 --
922 p_ssl_object_version_number := l_ssl_object_version_number;
923 --
924 hr_utility.set_location(' Leaving:'||l_proc, 70);
925 --
926 exception
927 when hr_api.validate_enabled then
928 --
929 -- As the Validate_Enabled exception has been raised
930 -- we must rollback to the savepoint
931 --
932 rollback to update_salary_survey_line;
933 --
934 -- Only set output warning arguments
935 -- (Any key or derived arguments must be set to null
936 -- when validation only mode is being used.)
937 --
938 p_ssl_object_version_number := null;
939 --
940 hr_utility.set_location(' Leaving:'||l_proc, 80);
941 --
942 when others then
943 --
944 -- A validation or unexpected error has occured
945 --
946 p_ssl_object_version_number := l_ssl_object_version_number;
947 rollback to update_salary_survey_line;
948 --
949 hr_utility.set_location(' Leaving:'||l_proc, 90);
950 --
951 raise;
952 --
953 end update_salary_survey_line;
954
955 --
956 --
957 -- ----------------------------------------------------------------------------
958 -- |------------------------< delete_salary_survey_line >---------------------------|
959 -- ----------------------------------------------------------------------------
960 --
961 procedure delete_salary_survey_line
962 (p_validate in boolean default false
963 ,p_salary_survey_line_id in number
964 ,p_ssl_object_version_number in number
965 ) is
966 --
967 -- Declare cursors and local variables
968 --
969 l_proc varchar2(72) := g_package||'delete_salary_survey_line';
970 --
971 l_salary_survey_line_id per_salary_survey_lines.salary_survey_line_id%TYPE;
972 l_ssl_object_version_number per_salary_survey_lines.object_version_number%TYPE;
973 --
974 begin
975 hr_utility.set_location('Entering:'|| l_proc, 10);
976 --
977 -- Issue a savepoint
978 --
979 savepoint delete_salary_survey_line;
980 --
981 hr_utility.set_location(l_proc, 20);
982 --
983 -- Call Before Process User Hook
984 --
985 l_salary_survey_line_id := p_salary_survey_line_id;
986 l_ssl_object_version_number := p_ssl_object_version_number;
987 --
988 begin
989 per_salary_survey_line_bk3.delete_salary_survey_line_b
990 (p_salary_survey_line_id => l_salary_survey_line_id
991 ,p_ssl_object_version_number => l_ssl_object_version_number
992 );
993 exception
994 when hr_api.cannot_find_prog_unit then
995 hr_api.cannot_find_prog_unit_error
996 (p_module_name => 'delete_salary_survey_line'
997 ,p_hook_type => 'BP'
998 );
999 end;
1000 --
1001 hr_utility.set_location(l_proc, 30);
1002 --
1003 -- Process Logic
1004 --
1005 per_ssl_del.del(p_salary_survey_line_id => l_salary_survey_line_id
1006 ,p_object_version_number => l_ssl_object_version_number
1007 );
1008 --
1009 hr_utility.set_location(l_proc, 50);
1010 --
1011 -- Call After Process User Hook
1012 --
1013 begin
1014 per_salary_survey_line_bk3.delete_salary_survey_line_a
1015 (p_salary_survey_line_id => l_salary_survey_line_id
1016 ,p_ssl_object_version_number => l_ssl_object_version_number
1017 );
1018 exception
1019 when hr_api.cannot_find_prog_unit then
1020 hr_api.cannot_find_prog_unit_error
1021 (p_module_name => 'delete_salary_survey_line'
1022 ,p_hook_type => 'AP'
1023 );
1024 end;
1025 hr_utility.set_location(l_proc, 60);
1026 --
1027 -- When in validation only mode raise the Validate_Enabled exception
1028 --
1029 if p_validate then
1030 --
1031 raise hr_api.validate_enabled;
1032 --
1033 end if;
1034 --
1035 hr_utility.set_location(' Leaving:'||l_proc, 70);
1036 --
1037 exception
1038 when hr_api.validate_enabled then
1039 --
1040 -- As the Validate_Enabled exception has been raised
1041 -- we must rollback to the savepoint
1042 --
1043 rollback to delete_salary_survey_line;
1044 --
1045 hr_utility.set_location(' Leaving:'||l_proc, 80);
1046 --
1047 when others then
1048 --
1049 -- A validation or unexpected error has occured
1050 --
1051 rollback to delete_salary_survey_line;
1052 --
1053 hr_utility.set_location(' Leaving:'||l_proc, 90);
1054 --
1055 raise;
1056 --
1057 end delete_salary_survey_line;
1058 --
1059 end hr_salary_survey_line_api;