[Home] [Help]
PACKAGE BODY: APPS.PAY_PGR_SHD
Source
4 -- ----------------------------------------------------------------------------
1 Package Body pay_pgr_shd as
2 /* $Header: pypgrrhi.pkb 120.6.12020000.2 2013/01/17 09:39:34 sclakkar ship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_pgr_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------< return_api_dml_status >-------------------------|
12 -- ----------------------------------------------------------------------------
13 Function return_api_dml_status Return Boolean Is
14 --
15 Begin
16 --
17 Return (nvl(g_api_dml, false));
18 --
19 End return_api_dml_status;
20 --
21 -- ----------------------------------------------------------------------------
22 -- |---------------------------< constraint_error >---------------------------|
23 -- ----------------------------------------------------------------------------
24 Procedure constraint_error
25 (p_constraint_name in all_constraints.constraint_name%TYPE
26 ) Is
27 --
28 l_proc varchar2(72) := g_package||'constraint_error';
29 --
30 Begin
31 --
32 If (p_constraint_name = 'PAY_GRADE_RULES_F_FK1') Then
33 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
34 fnd_message.set_token('PROCEDURE', l_proc);
35 fnd_message.set_token('STEP','5');
36 fnd_message.raise_error;
37 ElsIf (p_constraint_name = 'PAY_GRADE_RULES_F_FK2') Then
38 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
39 fnd_message.set_token('PROCEDURE', l_proc);
40 fnd_message.set_token('STEP','10');
41 fnd_message.raise_error;
42 ElsIf (p_constraint_name = 'PAY_GRADE_RULES_F_PK') Then
43 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
44 fnd_message.set_token('PROCEDURE', l_proc);
45 fnd_message.set_token('STEP','15');
46 fnd_message.raise_error;
47 ElsIf (p_constraint_name = 'PAY_GRL_RATE_TYPE_CHK') Then
48 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
49 fnd_message.set_token('PROCEDURE', l_proc);
50 fnd_message.set_token('STEP','20');
51 fnd_message.raise_error;
52 Else
53 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
54 fnd_message.set_token('PROCEDURE', l_proc);
55 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
56 fnd_message.raise_error;
57 End If;
58 --
59 End constraint_error;
60 --
61 -- ----------------------------------------------------------------------------
62 -- |-----------------------------< api_updating >-----------------------------|
63 -- ----------------------------------------------------------------------------
64 Function api_updating
65 (p_effective_date in date
66 ,p_grade_rule_id in number
67 ,p_object_version_number in number
68 ) Return Boolean Is
69 --
70 -- Cursor selects the 'current' row from the HR Schema
71 --
72 Cursor C_Sel1 is
73 select
74 grade_rule_id
75 ,effective_start_date
76 ,effective_end_date
77 ,business_group_id
78 ,rate_id
79 ,grade_or_spinal_point_id
80 ,rate_type
81 ,maximum
82 ,mid_value
83 ,minimum
84 ,sequence
85 ,value
86 ,request_id
87 ,program_application_id
88 ,program_id
89 ,program_update_date
90 ,object_version_number
91 ,currency_code
92 from pay_grade_rules_f
93 where grade_rule_id = p_grade_rule_id
94 and p_effective_date
95 between effective_start_date and effective_end_date;
96 --
97 l_fct_ret boolean;
98 --
99 Begin
103 p_object_version_number is null) Then
100 --
101 If (p_effective_date is null or
102 p_grade_rule_id is null or
104 --
105 -- One of the primary key arguments is null therefore we must
106 -- set the returning function value to false
107 --
108 l_fct_ret := false;
109 Else
110 If (p_grade_rule_id =
111 pay_pgr_shd.g_old_rec.grade_rule_id and
112 p_object_version_number =
113 pay_pgr_shd.g_old_rec.object_version_number
114 ) Then
115 --
116 -- The g_old_rec is current therefore we must
117 -- set the returning function to true
118 --
119 l_fct_ret := true;
120 Else
121 --
122 -- Select the current row
123 --
124 Open C_Sel1;
125 Fetch C_Sel1 Into pay_pgr_shd.g_old_rec;
126 If C_Sel1%notfound Then
127 Close C_Sel1;
128 --
129 -- The primary key is invalid therefore we must error
130 --
131 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
132 fnd_message.raise_error;
133 End If;
134 Close C_Sel1;
135 If (p_object_version_number
136 <> pay_pgr_shd.g_old_rec.object_version_number) Then
137 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
138 fnd_message.raise_error;
139 End If;
140 l_fct_ret := true;
141 End If;
142 End If;
143 Return (l_fct_ret);
144 --
145 End api_updating;
146 --
147 -- ----------------------------------------------------------------------------
148 -- |---------------------------< find_dt_upd_modes >--------------------------|
149 -- ----------------------------------------------------------------------------
150 Procedure find_dt_upd_modes
151 (p_effective_date in date
152 ,p_base_key_value in number
153 ,p_correction out nocopy boolean
154 ,p_update out nocopy boolean
155 ,p_update_override out nocopy boolean
156 ,p_update_change_insert out nocopy boolean
157 ) is
158 --
159 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
160 --
161 Begin
162 hr_utility.set_location('Entering:'||l_proc, 5);
163 --
164 -- Call the corresponding datetrack api
165 --
166 dt_api.find_dt_upd_modes
167 (p_effective_date => p_effective_date
168 ,p_base_table_name => 'pay_grade_rules_f'
169 ,p_base_key_column => 'grade_rule_id'
170 ,p_base_key_value => p_base_key_value
171 ,p_correction => p_correction
172 ,p_update => p_update
173 ,p_update_override => p_update_override
174 ,p_update_change_insert => p_update_change_insert
175 );
176 --
177 hr_utility.set_location(' Leaving:'||l_proc, 10);
178 End find_dt_upd_modes;
179 --
180 -- ----------------------------------------------------------------------------
181 -- |---------------------------< find_dt_del_modes >--------------------------|
182 -- ----------------------------------------------------------------------------
183 Procedure find_dt_del_modes
184 (p_effective_date in date
185 ,p_base_key_value in number
186 ,p_zap out nocopy boolean
187 ,p_delete out nocopy boolean
188 ,p_future_change out nocopy boolean
189 ,p_delete_next_change out nocopy boolean
190 ) is
191 --
192 l_proc varchar2(72) := g_package||'find_dt_del_modes';
193 --
194 --
195 Begin
196 hr_utility.set_location('Entering:'||l_proc, 5);
197 --
198 -- Call the corresponding datetrack api
199 --
200 dt_api.find_dt_del_modes
201 (p_effective_date => p_effective_date
202 ,p_base_table_name => 'pay_grade_rules_f'
203 ,p_base_key_column => 'grade_rule_id'
204 ,p_base_key_value => p_base_key_value
205 ,p_zap => p_zap
206 ,p_delete => p_delete
207 ,p_future_change => p_future_change
208 ,p_delete_next_change => p_delete_next_change
209 );
210 --
211 hr_utility.set_location(' Leaving:'||l_proc, 10);
212 End find_dt_del_modes;
213 --
214 -- ----------------------------------------------------------------------------
215 -- |-----------------------< upd_effective_end_date >-------------------------|
216 -- ----------------------------------------------------------------------------
217 Procedure upd_effective_end_date
218 (p_effective_date in date
219 ,p_base_key_value in number
220 ,p_new_effective_end_date in date
221 ,p_validation_start_date in date
222 ,p_validation_end_date in date
223 ,p_object_version_number out nocopy number
224 ) is
225 --
226 l_proc varchar2(72) := g_package||'upd_effective_end_date';
227 l_object_version_number number;
228 --
229 Begin
230 hr_utility.set_location('Entering:'||l_proc, 5);
231 --
232 -- Because we are updating a row we must get the next object
233 -- version number.
234 --
235 l_object_version_number :=
236 dt_api.get_object_version_number
237 (p_base_table_name => 'pay_grade_rules_f'
238 ,p_base_key_column => 'grade_rule_id'
239 ,p_base_key_value => p_base_key_value
240 );
241 --
242 hr_utility.set_location(l_proc, 10);
243 pay_pgr_shd.g_api_dml := true; -- Set the api dml status
247 --
244 --
245 -- Update the specified datetrack row setting the effective
246 -- end date to the specified new effective end date.
248 update pay_grade_rules_f t
249 set t.effective_end_date = p_new_effective_end_date
250 , t.object_version_number = l_object_version_number
251 where t.grade_rule_id = p_base_key_value
252 and p_effective_date
253 between t.effective_start_date and t.effective_end_date;
254 --
255 pay_pgr_shd.g_api_dml := false; -- Unset the api dml status
256 p_object_version_number := l_object_version_number;
257 hr_utility.set_location(' Leaving:'||l_proc, 15);
258 --
259 Exception
260 When Others Then
261 pay_pgr_shd.g_api_dml := false; -- Unset the api dml status
262 Raise;
263 --
264 End upd_effective_end_date;
265 --
266 -- ----------------------------------------------------------------------------
267 -- |---------------------------------< lck >----------------------------------|
268 -- ----------------------------------------------------------------------------
269 Procedure lck
270 (p_effective_date in date
271 ,p_datetrack_mode in varchar2
272 ,p_grade_rule_id in number
273 ,p_object_version_number in number
274 ,p_validation_start_date out nocopy date
275 ,p_validation_end_date out nocopy date
276 ) is
277 --
278 l_proc varchar2(72) := g_package||'lck';
279 l_validation_start_date date;
280 l_validation_end_date date;
281 l_argument varchar2(30);
282 --
283 -- Cursor C_Sel1 selects the current locked row as of session date
284 -- ensuring that the object version numbers match.
285 --
286 Cursor C_Sel1 is
287 select
288 grade_rule_id
289 ,effective_start_date
290 ,effective_end_date
291 ,business_group_id
292 ,rate_id
293 ,grade_or_spinal_point_id
294 ,rate_type
295 ,maximum
296 ,mid_value
297 ,minimum
298 ,sequence
299 ,value
300 ,request_id
301 ,program_application_id
302 ,program_id
303 ,program_update_date
304 ,object_version_number
305 ,currency_code
306 from pay_grade_rules_f
307 where grade_rule_id = p_grade_rule_id
308 and p_effective_date
309 between effective_start_date and effective_end_date
310 for update nowait;
311 --
312 --
313 --
314 Begin
315 hr_utility.set_location('Entering:'||l_proc, 5);
316 --
317 -- Ensure that all the mandatory arguments are not null
318 --
319 hr_api.mandatory_arg_error(p_api_name => l_proc
320 ,p_argument => 'effective_date'
321 ,p_argument_value => p_effective_date
322 );
323 --
324 hr_api.mandatory_arg_error(p_api_name => l_proc
325 ,p_argument => 'datetrack_mode'
326 ,p_argument_value => p_datetrack_mode
327 );
328 --
329 hr_api.mandatory_arg_error(p_api_name => l_proc
330 ,p_argument => 'grade_rule_id'
331 ,p_argument_value => p_grade_rule_id
332 );
333 --
334 hr_api.mandatory_arg_error(p_api_name => l_proc
335 ,p_argument => 'object_version_number'
336 ,p_argument_value => p_object_version_number
337 );
338
339 hr_utility.set_location(l_proc, 10);
340 --
341 -- Check to ensure the datetrack mode is not INSERT.
342 --
343 If (p_datetrack_mode <> hr_api.g_insert) then
344 --
345 -- We must select and lock the current row.
346 --
347 Open C_Sel1;
348 Fetch C_Sel1 Into pay_pgr_shd.g_old_rec;
349 If C_Sel1%notfound then
350 Close C_Sel1;
351 --
352 -- The primary key is invalid therefore we must error
353 --
354 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
355 fnd_message.raise_error;
356 End If;
357 Close C_Sel1;
358 If (p_object_version_number
359 <> pay_pgr_shd.g_old_rec.object_version_number) Then
360 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
361 fnd_message.raise_error;
362 End If;
363 --
364 --
365 -- Validate the datetrack mode mode getting the validation start
366 -- and end dates for the specified datetrack operation.
367 --
368 IF pay_pgr_shd.g_old_rec.rate_type = 'A' THEN
369 --
370 hr_utility.set_location(l_proc, 20);
371 --
372 dt_api.validate_dt_mode
373 (p_effective_date => p_effective_date
374 ,p_datetrack_mode => p_datetrack_mode
375 ,p_base_table_name => 'pay_grade_rules_f'
376 ,p_base_key_column => 'grade_rule_id'
377 ,p_base_key_value => p_grade_rule_id
378 ,p_parent_table_name1 => 'per_all_assignments_f'
379 ,p_parent_key_column1 => 'assignment_id'
380 ,p_parent_key_value1 => pay_pgr_shd.g_old_rec.grade_or_spinal_point_id
381 ,p_enforce_foreign_locking => true
382 ,p_validation_start_date => l_validation_start_date
383 ,p_validation_end_date => l_validation_end_date);
384 --
385 else
386 --
387 hr_utility.set_location(l_proc, 30);
388 --
389 dt_api.validate_dt_mode
390 (p_effective_date => p_effective_date
391 ,p_datetrack_mode => p_datetrack_mode
392 ,p_base_table_name => 'pay_grade_rules_f'
393 ,p_base_key_column => 'grade_rule_id'
394 ,p_base_key_value => p_grade_rule_id
395 ,p_enforce_foreign_locking => true
396 ,p_validation_start_date => l_validation_start_date
397 ,p_validation_end_date => l_validation_end_date);
398 --
399
400 END IF;
401 --
402 Else
403 --
404 -- We are doing a datetrack 'INSERT' which is illegal within this
405 -- procedure therefore we must error (note: to lck on insert the
406 -- private procedure ins_lck should be called).
407 --
408 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
409 fnd_message.set_token('PROCEDURE', l_proc);
410 fnd_message.set_token('STEP','20');
411 fnd_message.raise_error;
412 End If;
413 --
414 -- Set the validation start and end date OUT arguments
415 --
416 p_validation_start_date := l_validation_start_date;
417 p_validation_end_date := l_validation_end_date;
418 --
419 hr_utility.set_location(' Leaving:'||l_proc, 30);
420 --
421 -- We need to trap the ORA LOCK exception
422 --
423 Exception
424 When HR_Api.Object_Locked then
425 --
426 -- The object is locked therefore we need to supply a meaningful
427 -- error message.
428 --
429 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
430 fnd_message.set_token('TABLE_NAME', 'pay_grade_rules_f');
431 fnd_message.raise_error;
432 End lck;
433 --
434 -- ----------------------------------------------------------------------------
435 -- |-----------------------------< convert_args >-----------------------------|
436 -- ----------------------------------------------------------------------------
437 Function convert_args
438 (p_grade_rule_id in number
439 ,p_effective_start_date in date
440 ,p_effective_end_date in date
441 ,p_business_group_id in number
442 ,p_rate_id in number
443 ,p_grade_or_spinal_point_id in number
444 ,p_rate_type in varchar2
445 ,p_maximum in varchar2
446 ,p_mid_value in varchar2
447 ,p_minimum in varchar2
448 ,p_sequence in number
449 ,p_value in varchar2
450 ,p_request_id in number
451 ,p_program_application_id in number
452 ,p_program_id in number
453 ,p_program_update_date in date
454 ,p_object_version_number in number
455 ,p_currency_code in varchar2
456 )
457 Return g_rec_type is
458 --
459 l_rec g_rec_type;
460 --
461 Begin
462 --
463 -- Convert arguments into local l_rec structure.
464 --
465 l_rec.grade_rule_id := p_grade_rule_id;
466 l_rec.effective_start_date := p_effective_start_date;
467 l_rec.effective_end_date := p_effective_end_date;
468 l_rec.business_group_id := p_business_group_id;
469 l_rec.rate_id := p_rate_id;
470 l_rec.grade_or_spinal_point_id := p_grade_or_spinal_point_id;
471 l_rec.rate_type := p_rate_type;
472 l_rec.maximum := p_maximum;
473 l_rec.mid_value := p_mid_value;
474 l_rec.minimum := p_minimum;
475 l_rec.sequence := p_sequence;
476 l_rec.value := p_value;
477 l_rec.request_id := p_request_id;
478 l_rec.program_application_id := p_program_application_id;
479 l_rec.program_id := p_program_id;
480 l_rec.program_update_date := p_program_update_date;
481 l_rec.object_version_number := p_object_version_number;
482 l_rec.currency_code := p_currency_code;
483 --
484 -- Return the plsql record structure.
485 --
486 Return(l_rec);
487 --
488 End convert_args;
489 --
490 end pay_pgr_shd;