[Home] [Help]
PACKAGE BODY: APPS.PER_SSL_SHD
Source
1 Package Body per_ssl_shd as
2 /* $Header: pesslrhi.pkb 120.0.12010000.2 2008/09/09 11:18:51 pchowdav ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_ssl_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< constraint_error >---------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure constraint_error
14 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
15 --
16 l_proc varchar2(72) := g_package||'constraint_error';
17 --
18 Begin
19 hr_utility.set_location('Entering:'||l_proc, 5);
20 --
21 If (p_constraint_name = 'PER_SALARY_SURVEY_LINES_FK1') Then
22 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
23 hr_utility.set_message_token('PROCEDURE', l_proc);
24 hr_utility.set_message_token('STEP','5');
25 hr_utility.raise_error;
26 ElsIf (p_constraint_name = 'PER_SALARY_SURVEY_LINES_PK') Then
27 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
28 hr_utility.set_message_token('PROCEDURE', l_proc);
29 hr_utility.set_message_token('STEP','10');
30 hr_utility.raise_error;
31 ElsIf (p_constraint_name = 'PER_SALARY_SURVEY_LINES_UK1') Then
32 hr_utility.set_message(800, 'PER_50340_PSS_INV_COMB1');
33 hr_utility.raise_error;
34 Else
35 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
36 hr_utility.set_message_token('PROCEDURE', l_proc);
37 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
38 hr_utility.raise_error;
39 End If;
40 --
41 hr_utility.set_location(' Leaving:'||l_proc, 10);
42 End constraint_error;
43 --
44 -- ----------------------------------------------------------------------------
45 -- |-----------------------------< api_updating >-----------------------------|
46 -- ----------------------------------------------------------------------------
47 Function api_updating
48 (
49 p_salary_survey_line_id in number,
53 --
50 p_object_version_number in number
51 ) Return Boolean Is
52 --
54 -- Cursor selects the 'current' row from the HR Schema
55 --
56 Cursor C_Sel1 is
57 select
58 salary_survey_line_id,
59 object_version_number,
60 salary_survey_id,
61 survey_job_name_code,
62 survey_region_code,
63 survey_seniority_code,
64 company_size_code,
65 industry_code,
66 survey_age_code,
67 start_date,
68 end_date,
69 currency_code,
70 differential,
71 minimum_pay,
72 mean_pay,
73 maximum_pay,
74 graduate_pay,
75 starting_pay,
76 percentage_change,
77 job_first_quartile,
78 job_median_quartile,
79 job_third_quartile,
80 job_fourth_quartile,
81 minimum_total_compensation,
82 mean_total_compensation,
83 maximum_total_compensation,
84 compnstn_first_quartile,
85 compnstn_median_quartile,
86 compnstn_third_quartile,
87 compnstn_fourth_quartile,
88 /*Added for Enhancement 4021737 */
89 tenth_percentile,
90 twenty_fifth_percentile,
91 fiftieth_percentile,
92 seventy_fifth_percentile,
93 ninetieth_percentile,
94 minimum_bonus,
95 mean_bonus,
96 maximum_bonus,
97 minimum_salary_increase,
98 mean_salary_increase,
99 maximum_salary_increase,
100 min_variable_compensation,
101 mean_variable_compensation,
102 max_variable_compensation,
103 minimum_stock,
104 mean_stock,
105 maximum_stock,
106 stock_display_type,
107 /* End Enhancement 4021737 */
108 attribute_category,
109 attribute1,
110 attribute2,
111 attribute3,
112 attribute4,
113 attribute5,
114 attribute6,
115 attribute7,
116 attribute8,
117 attribute9,
118 attribute10,
119 attribute11,
120 attribute12,
121 attribute13,
122 attribute14,
123 attribute15,
124 attribute16,
125 attribute17,
126 attribute18,
127 attribute19,
128 attribute20,
129 /*Added for Enhancement 4021737*/
130 attribute21,
131 attribute22,
132 attribute23,
133 attribute24,
134 attribute25,
135 attribute26,
136 attribute27,
137 attribute28,
138 attribute29,
139 attribute30
140 /*Enhancement 4021737 */
141 from per_salary_survey_lines
142 where salary_survey_line_id = p_salary_survey_line_id;
143 --
144 l_proc varchar2(72) := g_package||'api_updating';
145 l_fct_ret boolean;
146 --
147 Begin
148 hr_utility.set_location('Entering:'||l_proc, 5);
149 --
150 If (
151 p_salary_survey_line_id is null and
152 p_object_version_number is null
153 ) Then
154 --
155 -- One of the primary key arguments is null therefore we must
156 -- set the returning function value to false
157 --
158 l_fct_ret := false;
159 Else
160 If (
161 p_salary_survey_line_id = g_old_rec.salary_survey_line_id and
162 p_object_version_number = g_old_rec.object_version_number
163 ) Then
164 hr_utility.set_location(l_proc, 10);
165 --
166 -- The g_old_rec is current therefore we must
167 -- set the returning function to true
168 --
169 l_fct_ret := true;
170 Else
171 --
172 -- Select the current row into g_old_rec
173 --
174 Open C_Sel1;
175 Fetch C_Sel1 Into g_old_rec;
176 If C_Sel1%notfound Then
177 Close C_Sel1;
178 --
179 -- The primary key is invalid therefore we must error
180 --
181 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
182 hr_utility.raise_error;
183 End If;
184 Close C_Sel1;
185 If (p_object_version_number <> g_old_rec.object_version_number) Then
186 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
187 hr_utility.raise_error;
188 End If;
189 hr_utility.set_location(l_proc, 15);
190 l_fct_ret := true;
191 End If;
192 End If;
193 hr_utility.set_location(' Leaving:'||l_proc, 20);
194 Return (l_fct_ret);
195 --
196 End api_updating;
197 --
198 -- ----------------------------------------------------------------------------
199 -- |---------------------------------< lck >----------------------------------|
200 -- ----------------------------------------------------------------------------
201 Procedure lck
202 (
203 p_salary_survey_line_id in number,
204 p_object_version_number in number
205 ) is
206 --
207 -- Cursor selects the 'current' row from the HR Schema
208 --
209 Cursor C_Sel1 is
210 select salary_survey_line_id,
211 object_version_number,
212 salary_survey_id,
213 survey_job_name_code,
214 survey_region_code,
215 survey_seniority_code,
216 company_size_code,
217 industry_code,
218 survey_age_code,
219 start_date,
220 end_date,
221 currency_code,
222 differential,
223 minimum_pay,
224 mean_pay,
225 maximum_pay,
226 graduate_pay,
227 starting_pay,
228 percentage_change,
232 job_fourth_quartile,
229 job_first_quartile,
230 job_median_quartile,
231 job_third_quartile,
233 minimum_total_compensation,
234 mean_total_compensation,
235 maximum_total_compensation,
236 compnstn_first_quartile,
237 compnstn_median_quartile,
238 compnstn_third_quartile,
239 compnstn_fourth_quartile,
240 /*Added for Enhancement 4021737 */
241 tenth_percentile,
242 twenty_fifth_percentile,
243 fiftieth_percentile,
244 seventy_fifth_percentile,
245 ninetieth_percentile,
246 minimum_bonus,
247 mean_bonus,
248 maximum_bonus,
249 minimum_salary_increase,
250 mean_salary_increase,
251 maximum_salary_increase,
252 min_variable_compensation,
253 mean_variable_compensation,
254 max_variable_compensation,
255 minimum_stock,
256 mean_stock,
257 maximum_stock,
258 stock_display_type,
259 /* End Enhancement 4021737 */
260 attribute_category,
261 attribute1,
262 attribute2,
263 attribute3,
264 attribute4,
265 attribute5,
266 attribute6,
267 attribute7,
268 attribute8,
269 attribute9,
270 attribute10,
271 attribute11,
272 attribute12,
273 attribute13,
274 attribute14,
275 attribute15,
276 attribute16,
277 attribute17,
278 attribute18,
279 attribute19,
280 attribute20,
281 /*Added for Enhancement 4021737 */
282 attribute21,
283 attribute22,
284 attribute23,
285 attribute24,
286 attribute25,
287 attribute26,
288 attribute27,
289 attribute28,
290 attribute29,
291 attribute30
292 /*End Enhancement 4021737 */
293 from per_salary_survey_lines
294 where salary_survey_line_id = p_salary_survey_line_id
295 for update nowait;
296 --
297 l_proc varchar2(72) := g_package||'lck';
298 --
299 Begin
300 hr_utility.set_location('Entering:'||l_proc, 5);
301 --
302 -- Add any mandatory argument checking here:
303 -- Example:
304 -- hr_api.mandatory_arg_error
305 -- (p_api_name => l_proc,
306 -- p_argument => 'object_version_number',
307 -- p_argument_value => p_object_version_number);
308 --
309 Open C_Sel1;
310 Fetch C_Sel1 Into g_old_rec;
311 If C_Sel1%notfound Then
312 Close C_Sel1;
313 --
314 -- The primary key is invalid therefore we must error
315 --
316 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
317 hr_utility.raise_error;
318 End If;
319 Close C_Sel1;
320 If (p_object_version_number <> g_old_rec.object_version_number) Then
321 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
322 hr_utility.raise_error;
323 End If;
324 --
325 hr_utility.set_location(' Leaving:'||l_proc, 10);
326 --
327 -- We need to trap the ORA LOCK exception
328 --
329 Exception
330 When HR_Api.Object_Locked Then
331 --
332 -- The object is locked therefore we need to supply a meaningful
333 -- error message.
334 --
335 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
336 hr_utility.set_message_token('TABLE_NAME', 'per_salary_survey_lines');
337 hr_utility.raise_error;
338 End lck;
339 --
340 -- ----------------------------------------------------------------------------
341 -- |-----------------------------< convert_args >-----------------------------|
342 -- ----------------------------------------------------------------------------
343 Function convert_args
344 (
345 p_salary_survey_line_id in number,
346 p_object_version_number in number,
347 p_salary_survey_id in number,
348 p_survey_job_name_code in varchar2,
349 p_survey_region_code in varchar2,
350 p_survey_seniority_code in varchar2,
351 p_company_size_code in varchar2,
352 p_industry_code in varchar2,
353 p_survey_age_code in varchar2,
354 p_start_date in date,
355 p_end_date in date,
356 p_currency_code in varchar2,
357 p_differential in number,
358 p_minimum_pay in number,
359 p_mean_pay in number,
360 p_maximum_pay in number,
361 p_graduate_pay in number,
362 p_starting_pay in number,
363 p_percentage_change in number,
364 p_job_first_quartile in number,
365 p_job_median_quartile in number,
366 p_job_third_quartile in number,
367 p_job_fourth_quartile in number,
368 p_minimum_total_compensation in number,
369 p_mean_total_compensation in number,
370 p_maximum_total_compensation in number,
371 p_compnstn_first_quartile in number,
372 p_compnstn_median_quartile in number,
373 p_compnstn_third_quartile in number,
374 p_compnstn_fourth_quartile in number,
375 /*Added for Enhancement 4021737*/
376 p_tenth_percentile in number,
377 p_twenty_fifth_percentile in number,
378 p_fiftieth_percentile in number,
379 p_seventy_fifth_percentile in number,
383 p_maximum_bonus in number,
380 p_ninetieth_percentile in number,
381 p_minimum_bonus in number,
382 p_mean_bonus in number,
384 p_minimum_salary_increase in number,
385 p_mean_salary_increase in number,
386 p_maximum_salary_increase in number,
387 p_min_variable_compensation in number,
388 p_mean_variable_compensation in number,
389 p_max_variable_compensation in number,
390 p_minimum_stock in number,
391 p_mean_stock in number,
392 p_maximum_stock in number,
393 p_stock_display_type in varchar2,
394 /*End Enhancement 4021737 */
395 p_attribute_category in varchar2,
396 p_attribute1 in varchar2,
397 p_attribute2 in varchar2,
398 p_attribute3 in varchar2,
399 p_attribute4 in varchar2,
400 p_attribute5 in varchar2,
401 p_attribute6 in varchar2,
402 p_attribute7 in varchar2,
403 p_attribute8 in varchar2,
404 p_attribute9 in varchar2,
405 p_attribute10 in varchar2,
406 p_attribute11 in varchar2,
407 p_attribute12 in varchar2,
408 p_attribute13 in varchar2,
409 p_attribute14 in varchar2,
410 p_attribute15 in varchar2,
411 p_attribute16 in varchar2,
412 p_attribute17 in varchar2,
413 p_attribute18 in varchar2,
414 p_attribute19 in varchar2,
415 p_attribute20 in varchar2,
416 /*Added for Enhancement 4021737 */
417 p_attribute21 in varchar2,
418 p_attribute22 in varchar2,
419 p_attribute23 in varchar2,
420 p_attribute24 in varchar2,
421 p_attribute25 in varchar2,
422 p_attribute26 in varchar2,
423 p_attribute27 in varchar2,
424 p_attribute28 in varchar2,
425 p_attribute29 in varchar2,
426 p_attribute30 in varchar2
427 /*End Enhancement 4021737 */
428 )
429 Return g_rec_type is
430 --
431 l_rec g_rec_type;
432 l_proc varchar2(72) := g_package||'convert_args';
433 --
434 Begin
435 --
436 hr_utility.set_location('Entering:'||l_proc, 5);
437 --
438 -- Convert arguments into local l_rec structure.
439 --
440 l_rec.salary_survey_line_id := p_salary_survey_line_id;
441 l_rec.object_version_number := p_object_version_number;
442 l_rec.salary_survey_id := p_salary_survey_id;
443 l_rec.survey_job_name_code := p_survey_job_name_code;
444 l_rec.survey_region_code := p_survey_region_code;
445 l_rec.survey_seniority_code := p_survey_seniority_code;
446 l_rec.company_size_code := p_company_size_code;
447 l_rec.industry_code := p_industry_code;
448 l_rec.survey_age_code := p_survey_age_code;
449 l_rec.start_date := p_start_date;
450 l_rec.end_date := p_end_date;
451 l_rec.currency_code := p_currency_code;
452 l_rec.differential := p_differential;
453 l_rec.minimum_pay := p_minimum_pay;
454 l_rec.mean_pay := p_mean_pay;
455 l_rec.maximum_pay := p_maximum_pay;
456 l_rec.graduate_pay := p_graduate_pay;
457 l_rec.starting_pay := p_starting_pay;
458 l_rec.percentage_change := p_percentage_change;
459 l_rec.job_first_quartile := p_job_first_quartile;
460 l_rec.job_median_quartile := p_job_median_quartile;
461 l_rec.job_third_quartile := p_job_third_quartile;
462 l_rec.job_fourth_quartile := p_job_fourth_quartile;
463 l_rec.minimum_total_compensation := p_minimum_total_compensation;
464 l_rec.mean_total_compensation := p_mean_total_compensation;
465 l_rec.maximum_total_compensation := p_maximum_total_compensation;
466 l_rec.compnstn_first_quartile := p_compnstn_first_quartile;
467 l_rec.compnstn_median_quartile := p_compnstn_median_quartile;
468 l_rec.compnstn_third_quartile := p_compnstn_third_quartile;
469 l_rec.compnstn_fourth_quartile := p_compnstn_fourth_quartile;
470 /*Added for Enhancement 4021737 */
471 l_rec.tenth_percentile := p_tenth_percentile;
472 l_rec.twenty_fifth_percentile := p_twenty_fifth_percentile;
473 l_rec.fiftieth_percentile := p_fiftieth_percentile;
474 l_rec.seventy_fifth_percentile := p_seventy_fifth_percentile;
475 l_rec.ninetieth_percentile := p_ninetieth_percentile;
476 l_rec.minimum_bonus := p_minimum_bonus;
477 l_rec.mean_bonus := p_mean_bonus;
478 l_rec.maximum_bonus := p_maximum_bonus;
479 l_rec.minimum_salary_increase := p_minimum_salary_increase;
480 l_rec.mean_salary_increase := p_mean_salary_increase;
481 l_rec.maximum_salary_increase := p_maximum_salary_increase;
482 l_rec.min_variable_compensation := p_min_variable_compensation;
483 l_rec.mean_variable_compensation := p_mean_variable_compensation;
484 l_rec.max_variable_compensation := p_max_variable_compensation;
485 l_rec.minimum_stock := p_minimum_stock;
486 l_rec.mean_stock := p_mean_stock;
487 l_rec.maximum_stock := p_maximum_stock;
488 l_rec.stock_display_type := p_stock_display_type;
489 /*End Enhancement 4021737 */
490 l_rec.attribute_category := p_attribute_category;
491 l_rec.attribute1 := p_attribute1;
492 l_rec.attribute2 := p_attribute2;
493 l_rec.attribute3 := p_attribute3;
494 l_rec.attribute4 := p_attribute4;
495 l_rec.attribute5 := p_attribute5;
496 l_rec.attribute6 := p_attribute6;
497 l_rec.attribute7 := p_attribute7;
498 l_rec.attribute8 := p_attribute8;
499 l_rec.attribute9 := p_attribute9;
500 l_rec.attribute10 := p_attribute10;
501 l_rec.attribute11 := p_attribute11;
502 l_rec.attribute12 := p_attribute12;
503 l_rec.attribute13 := p_attribute13;
504 l_rec.attribute14 := p_attribute14;
505 l_rec.attribute15 := p_attribute15;
506 l_rec.attribute16 := p_attribute16;
507 l_rec.attribute17 := p_attribute17;
508 l_rec.attribute18 := p_attribute18;
509 l_rec.attribute19 := p_attribute19;
510 l_rec.attribute20 := p_attribute20;
511 /*Added for Enhancement 4021737 */
512 l_rec.attribute21 := p_attribute21;
513 l_rec.attribute22 := p_attribute22;
514 l_rec.attribute23 := p_attribute23;
515 l_rec.attribute24 := p_attribute24;
516 l_rec.attribute25 := p_attribute25;
517 l_rec.attribute26 := p_attribute26;
518 l_rec.attribute27 := p_attribute27;
519 l_rec.attribute28 := p_attribute28;
520 l_rec.attribute29 := p_attribute29;
521 l_rec.attribute30 := p_attribute30;
522 /*End Enhancement 4021737 */
523 --
524 -- Return the plsql record structure.
525 --
526 hr_utility.set_location(' Leaving:'||l_proc, 10);
527 Return(l_rec);
528 --
529 End convert_args;
530 --
531 End per_ssl_shd;