[Home] [Help]
PACKAGE BODY: APPS.PER_PGS_SHD
Source
1 Package Body per_pgs_shd as
2 /* $Header: pepgsrhi.pkb 120.0 2005/05/31 14:12:49 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_pgs_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 = 'PER_GRADE_SPINES_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 = 'PER_GRADE_SPINES_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 = 'PER_GRADE_SPINES_F_FK3') 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 = 'PER_GRADE_SPINES_F_PK') 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_spine_id in number
67 ,p_object_version_number in number
68 ) Return Boolean Is
72 Cursor C_Sel1 is
69 --
70 -- Cursor selects the 'current' row from the HR Schema
71 --
73 select
74 grade_spine_id
75 ,effective_start_date
76 ,effective_end_date
77 ,business_group_id
78 ,parent_spine_id
79 ,grade_id
80 ,ceiling_step_id
81 ,starting_step
82 ,request_id
83 ,program_application_id
84 ,program_id
85 ,program_update_date
86 ,object_version_number
87 from per_grade_spines_f
88 where grade_spine_id = p_grade_spine_id
89 and p_effective_date
90 between effective_start_date and effective_end_date;
91 --
92 l_fct_ret boolean;
93 --
94 Begin
95 --
96 If (p_effective_date is null or
97 p_grade_spine_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_spine_id =
106 per_pgs_shd.g_old_rec.grade_spine_id and
107 p_object_version_number =
108 per_pgs_shd.g_old_rec.object_version_number
109 ) Then
110 --
111 -- The g_old_rec is current therefore we must
112 -- set the returning function to true
113 --
114 l_fct_ret := true;
115 Else
116 --
117 -- Select the current row
118 --
119 Open C_Sel1;
120 Fetch C_Sel1 Into per_pgs_shd.g_old_rec;
121 If C_Sel1%notfound Then
122 Close C_Sel1;
123 --
124 -- The primary key is invalid therefore we must error
125 --
126 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
127 fnd_message.raise_error;
128 End If;
129 Close C_Sel1;
130 If (p_object_version_number
131 <> per_pgs_shd.g_old_rec.object_version_number) Then
132 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
133 fnd_message.raise_error;
134 End If;
135 l_fct_ret := true;
136 End If;
137 End If;
138 Return (l_fct_ret);
139 --
140 End api_updating;
141 --
142 -- ----------------------------------------------------------------------------
143 -- |---------------------------< find_dt_upd_modes >--------------------------|
144 -- ----------------------------------------------------------------------------
145 Procedure find_dt_upd_modes
146 (p_effective_date in date
147 ,p_base_key_value in number
148 ,p_correction out nocopy boolean
149 ,p_update out nocopy boolean
150 ,p_update_override out nocopy boolean
151 ,p_update_change_insert out nocopy boolean
152 ) is
153 --
154 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
155 --
156 Begin
157 hr_utility.set_location('Entering:'||l_proc, 5);
158 --
159 -- Call the corresponding datetrack api
160 --
161 dt_api.find_dt_upd_modes
162 (p_effective_date => p_effective_date
163 ,p_base_table_name => 'per_grade_spines_f'
164 ,p_base_key_column => 'grade_spine_id'
165 ,p_base_key_value => p_base_key_value
166 ,p_correction => p_correction
167 ,p_update => p_update
168 ,p_update_override => p_update_override
169 ,p_update_change_insert => p_update_change_insert
170 );
171 --
172 hr_utility.set_location(' Leaving:'||l_proc, 10);
173 End find_dt_upd_modes;
174 --
175 -- ----------------------------------------------------------------------------
176 -- |---------------------------< find_dt_del_modes >--------------------------|
177 -- ----------------------------------------------------------------------------
178 Procedure find_dt_del_modes
179 (p_effective_date in date
180 ,p_base_key_value in number
181 ,p_zap out nocopy boolean
182 ,p_delete out nocopy boolean
183 ,p_future_change out nocopy boolean
184 ,p_delete_next_change out nocopy boolean
185 ) is
186 --
187 l_proc varchar2(72) := g_package||'find_dt_del_modes';
188 --
189 --
190 Begin
191 hr_utility.set_location('Entering:'||l_proc, 5);
192 --
193 -- Call the corresponding datetrack api
194 --
195 dt_api.find_dt_del_modes
196 (p_effective_date => p_effective_date
197 ,p_base_table_name => 'per_grade_spines_f'
198 ,p_base_key_column => 'grade_spine_id'
199 ,p_base_key_value => p_base_key_value
200 ,p_zap => p_zap
201 ,p_delete => p_delete
202 ,p_future_change => p_future_change
203 ,p_delete_next_change => p_delete_next_change
204 );
205 --
206 hr_utility.set_location(' Leaving:'||l_proc, 10);
207 End find_dt_del_modes;
208 --
209 -- ----------------------------------------------------------------------------
210 -- |-----------------------< upd_effective_end_date >-------------------------|
211 -- ----------------------------------------------------------------------------
212 Procedure upd_effective_end_date
213 (p_effective_date in date
214 ,p_base_key_value in number
215 ,p_new_effective_end_date in date
216 ,p_validation_start_date in date
217 ,p_validation_end_date in date
221 l_proc varchar2(72) := g_package||'upd_effective_end_date';
218 ,p_object_version_number out nocopy number
219 ) is
220 --
222 l_object_version_number number;
223 --
224 Begin
225 hr_utility.set_location('Entering:'||l_proc, 5);
226 --
227 -- Because we are updating a row we must get the next object
228 -- version number.
229 --
230 l_object_version_number :=
231 dt_api.get_object_version_number
232 (p_base_table_name => 'per_grade_spines_f'
233 ,p_base_key_column => 'grade_spine_id'
234 ,p_base_key_value => p_base_key_value
235 );
236 --
237 hr_utility.set_location(l_proc, 10);
238 per_pgs_shd.g_api_dml := true; -- Set the api dml status
239 --
240 -- Update the specified datetrack row setting the effective
241 -- end date to the specified new effective end date.
242 --
243 update per_grade_spines_f t
244 set t.effective_end_date = p_new_effective_end_date
245 , t.object_version_number = l_object_version_number
246 where t.grade_spine_id = p_base_key_value
247 and p_effective_date
248 between t.effective_start_date and t.effective_end_date;
249 --
250 per_pgs_shd.g_api_dml := false; -- Unset the api dml status
251 p_object_version_number := l_object_version_number;
252 hr_utility.set_location(' Leaving:'||l_proc, 15);
253 --
254 Exception
255 When Others Then
256 per_pgs_shd.g_api_dml := false; -- Unset the api dml status
257 Raise;
258 --
259 End upd_effective_end_date;
260 --
261 -- ----------------------------------------------------------------------------
262 -- |---------------------------------< lck >----------------------------------|
263 -- ----------------------------------------------------------------------------
264 Procedure lck
265 (p_effective_date in date
266 ,p_datetrack_mode in varchar2
267 ,p_grade_spine_id in number
268 ,p_object_version_number in number
269 ,p_validation_start_date out nocopy date
270 ,p_validation_end_date out nocopy date
271 ) is
272 --
273 l_proc varchar2(72) := g_package||'lck';
274 l_validation_start_date date;
275 l_validation_end_date date;
276 l_argument varchar2(30);
277 --
278 -- Cursor C_Sel1 selects the current locked row as of session date
279 -- ensuring that the object version numbers match.
280 --
281 Cursor C_Sel1 is
282 select
283 grade_spine_id
284 ,effective_start_date
285 ,effective_end_date
286 ,business_group_id
287 ,parent_spine_id
288 ,grade_id
289 ,ceiling_step_id
290 ,starting_step
291 ,request_id
292 ,program_application_id
293 ,program_id
294 ,program_update_date
295 ,object_version_number
296 from per_grade_spines_f
297 where grade_spine_id = p_grade_spine_id
298 and p_effective_date
299 between effective_start_date and effective_end_date
300 for update nowait;
301 --
302 --
303 --
304 Begin
305 hr_utility.set_location('Entering:'||l_proc, 5);
306 --
307 -- Ensure that all the mandatory arguments are not null
308 --
309 hr_api.mandatory_arg_error(p_api_name => l_proc
310 ,p_argument => 'effective_date'
311 ,p_argument_value => p_effective_date
312 );
313 --
314 hr_api.mandatory_arg_error(p_api_name => l_proc
315 ,p_argument => 'datetrack_mode'
316 ,p_argument_value => p_datetrack_mode
317 );
318 --
319 hr_api.mandatory_arg_error(p_api_name => l_proc
320 ,p_argument => 'grade_spine_id'
321 ,p_argument_value => p_grade_spine_id
322 );
323 --
324 hr_api.mandatory_arg_error(p_api_name => l_proc
325 ,p_argument => 'object_version_number'
326 ,p_argument_value => p_object_version_number
327 );
328 --
329 -- Check to ensure the datetrack mode is not INSERT.
330 --
331 If (p_datetrack_mode <> hr_api.g_insert) then
332 --
333 -- We must select and lock the current row.
334 --
335 Open C_Sel1;
336 Fetch C_Sel1 Into per_pgs_shd.g_old_rec;
337 If C_Sel1%notfound then
338 Close C_Sel1;
339 --
340 -- The primary key is invalid therefore we must error
341 --
342 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
343 fnd_message.raise_error;
344 End If;
345 Close C_Sel1;
346 If (p_object_version_number
347 <> per_pgs_shd.g_old_rec.object_version_number) Then
348 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
349 fnd_message.raise_error;
350 End If;
351 --
352 --
353 -- Validate the datetrack mode mode getting the validation start
354 -- and end dates for the specified datetrack operation.
355 --
356 dt_api.validate_dt_mode
357 (p_effective_date => p_effective_date
358 ,p_datetrack_mode => p_datetrack_mode
359 ,p_base_table_name => 'per_grade_spines_f'
360 ,p_base_key_column => 'grade_spine_id'
361 ,p_base_key_value => p_grade_spine_id
362 ,p_child_table_name1 => 'per_spinal_point_steps_f'
363 ,p_child_key_column1 => 'step_id'
364 ,p_child_table_name2 => 'per_cagr_entitlement_lines_f'
365 ,p_child_key_column2 => 'cagr_entitlement_line_id'
366 ,p_enforce_foreign_locking => true
367 ,p_validation_start_date => l_validation_start_date
368 ,p_validation_end_date => l_validation_end_date
369 );
370 Else
371 --
372 -- We are doing a datetrack 'INSERT' which is illegal within this
373 -- procedure therefore we must error (note: to lck on insert the
374 -- private procedure ins_lck should be called).
375 --
376 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
377 fnd_message.set_token('PROCEDURE', l_proc);
378 fnd_message.set_token('STEP','20');
379 fnd_message.raise_error;
380 End If;
381 --
382 -- Set the validation start and end date OUT arguments
383 --
384 p_validation_start_date := l_validation_start_date;
385 p_validation_end_date := l_validation_end_date;
386 --
387 hr_utility.set_location(' Leaving:'||l_proc, 30);
388 --
389 -- We need to trap the ORA LOCK exception
390 --
391 Exception
392 When HR_Api.Object_Locked then
393 --
394 -- The object is locked therefore we need to supply a meaningful
395 -- error message.
396 --
397 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
398 fnd_message.set_token('TABLE_NAME', 'per_grade_spines_f');
399 fnd_message.raise_error;
400 End lck;
401 --
402 -- ----------------------------------------------------------------------------
403 -- |-----------------------------< convert_args >-----------------------------|
404 -- ----------------------------------------------------------------------------
405 Function convert_args
406 (p_grade_spine_id in number
407 ,p_effective_start_date in date
408 ,p_effective_end_date in date
409 ,p_business_group_id in number
410 ,p_parent_spine_id in number
411 ,p_grade_id in number
412 ,p_ceiling_step_id in number
413 ,p_starting_step in number
414 ,p_request_id in number
415 ,p_program_application_id in number
416 ,p_program_id in number
417 ,p_program_update_date in date
418 ,p_object_version_number in number
419 )
420 Return g_rec_type is
421 --
422 l_rec g_rec_type;
423 --
424 Begin
425 --
426 -- Convert arguments into local l_rec structure.
427 --
428 l_rec.grade_spine_id := p_grade_spine_id;
429 l_rec.effective_start_date := p_effective_start_date;
430 l_rec.effective_end_date := p_effective_end_date;
431 l_rec.business_group_id := p_business_group_id;
432 l_rec.parent_spine_id := p_parent_spine_id;
433 l_rec.grade_id := p_grade_id;
434 l_rec.ceiling_step_id := p_ceiling_step_id;
435 l_rec.starting_step := p_starting_step;
436 l_rec.request_id := p_request_id;
437 l_rec.program_application_id := p_program_application_id;
438 l_rec.program_id := p_program_id;
439 l_rec.program_update_date := p_program_update_date;
440 l_rec.object_version_number := p_object_version_number;
441 --
442 -- Return the plsql record structure.
443 --
444 Return(l_rec);
445 --
446 End convert_args;
447 --
448 end per_pgs_shd;