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