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