[Home] [Help]
PACKAGE BODY: APPS.BEN_PRY_SHD
Source
1 Package Body ben_pry_shd as
2 /* $Header: bepryrhi.pkb 120.5.12010000.3 2008/08/05 15:23:35 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_pry_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 = 'BEN_PRTT_RMT_APRVD_FR_PYMT_PK') 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 Else
38 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
39 fnd_message.set_token('PROCEDURE', l_proc);
40 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
41 fnd_message.raise_error;
42 End If;
43 --
44 End constraint_error;
45 --
46 -- ----------------------------------------------------------------------------
47 -- |-----------------------------< api_updating >-----------------------------|
48 -- ----------------------------------------------------------------------------
49 Function api_updating
50 (p_effective_date in date
51 ,p_prtt_rmt_aprvd_fr_pymt_id in number
52 ,p_object_version_number in number
53 ) Return Boolean Is
54 --
55 -- Cursor selects the 'current' row from the HR Schema
56 --
57 Cursor C_Sel1 is
58 select
59 prtt_rmt_aprvd_fr_pymt_id
60 ,prtt_reimbmt_rqst_id
61 ,effective_start_date
62 ,effective_end_date
63 ,apprvd_fr_pymt_num
64 ,adjmt_flag
65 ,aprvd_fr_pymt_amt
66 ,pymt_stat_cd
67 ,pymt_stat_rsn_cd
68 ,pymt_stat_ovrdn_rsn_cd
69 ,pymt_stat_prr_to_ovrd_cd
70 ,business_group_id
71 ,element_entry_value_id
72 ,pry_attribute_category
73 ,pry_attribute1
74 ,pry_attribute2
75 ,pry_attribute3
76 ,pry_attribute4
77 ,pry_attribute5
78 ,pry_attribute6
79 ,pry_attribute7
80 ,pry_attribute8
81 ,pry_attribute9
82 ,pry_attribute10
83 ,pry_attribute11
84 ,pry_attribute12
85 ,pry_attribute13
86 ,pry_attribute14
87 ,pry_attribute15
88 ,pry_attribute16
89 ,pry_attribute17
90 ,pry_attribute18
91 ,pry_attribute19
92 ,pry_attribute20
93 ,pry_attribute21
94 ,pry_attribute22
95 ,pry_attribute23
96 ,pry_attribute24
97 ,pry_attribute25
98 ,pry_attribute26
99 ,pry_attribute27
100 ,pry_attribute28
101 ,pry_attribute29
102 ,pry_attribute30
103 ,object_version_number
104 from ben_prtt_rmt_aprvd_fr_pymt_f
105 where prtt_rmt_aprvd_fr_pymt_id = p_prtt_rmt_aprvd_fr_pymt_id
106 and p_effective_date
107 between effective_start_date and effective_end_date;
108 --
109 l_fct_ret boolean;
110 --
111 Begin
112 --
113 If (p_effective_date is null or
114 p_prtt_rmt_aprvd_fr_pymt_id is null or
115 p_object_version_number is null) Then
116 --
117 -- One of the primary key arguments is null therefore we must
118 -- set the returning function value to false
119 --
120 l_fct_ret := false;
121 Else
122 If (p_prtt_rmt_aprvd_fr_pymt_id =
123 ben_pry_shd.g_old_rec.prtt_rmt_aprvd_fr_pymt_id and
124 p_object_version_number =
125 ben_pry_shd.g_old_rec.object_version_number
126 ) Then
127 --
128 -- The g_old_rec is current therefore we must
129 -- set the returning function to true
130 --
131 l_fct_ret := true;
132 Else
133 --
134 -- Select the current row
135 --
136 Open C_Sel1;
137 Fetch C_Sel1 Into ben_pry_shd.g_old_rec;
138 If C_Sel1%notfound Then
139 Close C_Sel1;
140 --
141 -- The primary key is invalid therefore we must error
142 --
143 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
144 fnd_message.raise_error;
145 End If;
146 Close C_Sel1;
147 If (p_object_version_number
148 <> ben_pry_shd.g_old_rec.object_version_number) Then
149 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
150 fnd_message.raise_error;
151 End If;
152 l_fct_ret := true;
153 End If;
154 End If;
155 Return (l_fct_ret);
156 --
157 End api_updating;
158 --
159 -- ----------------------------------------------------------------------------
160 -- |---------------------------< find_dt_upd_modes >--------------------------|
161 -- ----------------------------------------------------------------------------
162 Procedure find_dt_upd_modes
163 (p_effective_date in date
164 ,p_base_key_value in number
165 ,p_correction out nocopy boolean
166 ,p_update out nocopy boolean
167 ,p_update_override out nocopy boolean
168 ,p_update_change_insert out nocopy boolean
169 ) is
170 --
171 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
172 --
173 Begin
174 hr_utility.set_location('Entering:'||l_proc, 5);
175 --
176 -- Call the corresponding datetrack api
177 --
178 dt_api.find_dt_upd_modes
179 (p_effective_date => p_effective_date
180 ,p_base_table_name => 'ben_prtt_rmt_aprvd_fr_pymt_f'
181 ,p_base_key_column => 'prtt_rmt_aprvd_fr_pymt_id'
182 ,p_base_key_value => p_base_key_value
183 ,p_correction => p_correction
184 ,p_update => p_update
185 ,p_update_override => p_update_override
186 ,p_update_change_insert => p_update_change_insert
187 );
188
189 --Bug 5558175 : Datetrack functionality on the Reimbursement Payment block (PRY) should not be
190 --present and hence hard-coded datetrack_mode in update, delete to correction and zap respectively.
191
192 hr_utility.set_location('Forcing Correction Mode for block (PRY)', 5);
193 p_update := FALSE;
194 p_update_override := FALSE;
195 p_update_change_insert := FALSE;
196
197 --Bug 5558175
198
199 hr_utility.set_location(' Leaving:'||l_proc, 10);
200 End find_dt_upd_modes;
201 --
202 -- ----------------------------------------------------------------------------
203 -- |---------------------------< find_dt_del_modes >--------------------------|
204 -- ----------------------------------------------------------------------------
205 Procedure find_dt_del_modes
206 (p_effective_date in date
207 ,p_base_key_value in number
208 ,p_zap out nocopy boolean
209 ,p_delete out nocopy boolean
210 ,p_future_change out nocopy boolean
211 ,p_delete_next_change out nocopy boolean
212 ) is
213 --
214 l_proc varchar2(72) := g_package||'find_dt_del_modes';
215 --
216 l_parent_key_value1 number;
217 --
218 Cursor C_Sel1 Is
219 select
220 t.prtt_reimbmt_rqst_id
221 from ben_prtt_rmt_aprvd_fr_pymt_f t
222 where t.prtt_rmt_aprvd_fr_pymt_id = p_base_key_value
223 and p_effective_date
224 between t.effective_start_date and t.effective_end_date;
225 --
226 Begin
227 hr_utility.set_location('Entering:'||l_proc, 5);
228 Open C_sel1;
229 Fetch C_Sel1 Into
230 l_parent_key_value1;
231 If C_Sel1%NOTFOUND then
232 Close C_Sel1;
233 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
234 fnd_message.set_token('PROCEDURE',l_proc);
235 fnd_message.set_token('STEP','10');
236 fnd_message.raise_error;
237 End If;
238 Close C_Sel1;
239 --
240 -- Call the corresponding datetrack api
241 --
242 dt_api.find_dt_del_modes
243 (p_effective_date => p_effective_date
244 ,p_base_table_name => 'ben_prtt_rmt_aprvd_fr_pymt_f'
245 ,p_base_key_column => 'prtt_rmt_aprvd_fr_pymt_id'
246 ,p_base_key_value => p_base_key_value
247 ,p_parent_table_name1 => 'ben_prtt_reimbmt_rqst_f'
248 ,p_parent_key_column1 => 'prtt_reimbmt_rqst_id'
249 ,p_parent_key_value1 => l_parent_key_value1
250 ,p_zap => p_zap
251 ,p_delete => p_delete
252 ,p_future_change => p_future_change
253 ,p_delete_next_change => p_delete_next_change
254 );
255
256
257 --Bug 5558175 : Datetrack functionality on the Reimbursement Payment block (PRY) should not be
258 --present and hence hard-coded datetrack_mode in update, delete to correction and zap respectively.
259
260 hr_utility.set_location('Forcing Zap Mode for block (PRY)', 5);
261
262 p_delete := FALSE;
263 p_future_change := FALSE;
264 p_delete_next_change := FALSE;
265
266 --End Bug 5558175
267
268
269 --
270 hr_utility.set_location(' Leaving:'||l_proc, 10);
271 End find_dt_del_modes;
272 --
273 -- ----------------------------------------------------------------------------
274 -- |-----------------------< upd_effective_end_date >-------------------------|
275 -- ----------------------------------------------------------------------------
276 Procedure upd_effective_end_date
277 (p_effective_date in date
278 ,p_base_key_value in number
279 ,p_new_effective_end_date in date
280 ,p_validation_start_date in date
281 ,p_validation_end_date in date
282 ,p_object_version_number out nocopy number
283 ) is
284 --
285 l_proc varchar2(72) := g_package||'upd_effective_end_date';
286 l_object_version_number number;
287 --
288 Begin
289 hr_utility.set_location('Entering:'||l_proc, 5);
290 --
291 -- Because we are updating a row we must get the next object
292 -- version number.
293 --
294 l_object_version_number :=
295 dt_api.get_object_version_number
296 (p_base_table_name => 'ben_prtt_rmt_aprvd_fr_pymt_f'
297 ,p_base_key_column => 'prtt_rmt_aprvd_fr_pymt_id'
298 ,p_base_key_value => p_base_key_value
299 );
300 --
301 hr_utility.set_location(l_proc, 10);
302 ben_pry_shd.g_api_dml := true; -- Set the api dml status
303 --
304 -- Update the specified datetrack row setting the effective
305 -- end date to the specified new effective end date.
306 --
307 update ben_prtt_rmt_aprvd_fr_pymt_f t
308 set t.effective_end_date = p_new_effective_end_date
309 , t.object_version_number = l_object_version_number
310 where t.prtt_rmt_aprvd_fr_pymt_id = p_base_key_value
311 and p_effective_date
312 between t.effective_start_date and t.effective_end_date;
313 --
314 ben_pry_shd.g_api_dml := false; -- Unset the api dml status
315 p_object_version_number := l_object_version_number;
316 hr_utility.set_location(' Leaving:'||l_proc, 15);
317 --
318 Exception
319 When Others Then
320 ben_pry_shd.g_api_dml := false; -- Unset the api dml status
321 Raise;
322 --
323 End upd_effective_end_date;
324 --
325 -- ----------------------------------------------------------------------------
326 -- |---------------------------------< lck >----------------------------------|
327 -- ----------------------------------------------------------------------------
328 Procedure lck
329 (p_effective_date in date
330 ,p_datetrack_mode in varchar2
331 ,p_prtt_rmt_aprvd_fr_pymt_id in number
332 ,p_object_version_number in number
333 ,p_validation_start_date out nocopy date
334 ,p_validation_end_date out nocopy date
335 ) is
336 --
337 l_proc varchar2(72) := g_package||'lck';
338 l_validation_start_date date;
339 l_validation_end_date date;
340 l_argument varchar2(30);
341 --
342 -- Cursor C_Sel1 selects the current locked row as of session date
343 -- ensuring that the object version numbers match.
344 --
345 Cursor C_Sel1 is
346 select
347 prtt_rmt_aprvd_fr_pymt_id
348 ,prtt_reimbmt_rqst_id
349 ,effective_start_date
350 ,effective_end_date
351 ,apprvd_fr_pymt_num
352 ,adjmt_flag
353 ,aprvd_fr_pymt_amt
354 ,pymt_stat_cd
355 ,pymt_stat_rsn_cd
356 ,pymt_stat_ovrdn_rsn_cd
357 ,pymt_stat_prr_to_ovrd_cd
358 ,business_group_id
359 ,element_entry_value_id
360 ,pry_attribute_category
361 ,pry_attribute1
362 ,pry_attribute2
363 ,pry_attribute3
364 ,pry_attribute4
365 ,pry_attribute5
366 ,pry_attribute6
367 ,pry_attribute7
368 ,pry_attribute8
369 ,pry_attribute9
370 ,pry_attribute10
371 ,pry_attribute11
372 ,pry_attribute12
373 ,pry_attribute13
374 ,pry_attribute14
375 ,pry_attribute15
376 ,pry_attribute16
377 ,pry_attribute17
378 ,pry_attribute18
379 ,pry_attribute19
380 ,pry_attribute20
381 ,pry_attribute21
382 ,pry_attribute22
383 ,pry_attribute23
384 ,pry_attribute24
385 ,pry_attribute25
386 ,pry_attribute26
387 ,pry_attribute27
388 ,pry_attribute28
389 ,pry_attribute29
390 ,pry_attribute30
391 ,object_version_number
392 from ben_prtt_rmt_aprvd_fr_pymt_f
393 where prtt_rmt_aprvd_fr_pymt_id = p_prtt_rmt_aprvd_fr_pymt_id
394 and p_effective_date
395 between effective_start_date and effective_end_date
396 for update nowait;
397 --
398 --
399 --
400 Begin
401 hr_utility.set_location('Entering:'||l_proc, 5);
402 --
403 -- Ensure that all the mandatory arguments are not null
404 --
405 hr_api.mandatory_arg_error(p_api_name => l_proc
406 ,p_argument => 'effective_date'
407 ,p_argument_value => p_effective_date
408 );
412 ,p_argument_value => p_datetrack_mode
409 --
410 hr_api.mandatory_arg_error(p_api_name => l_proc
411 ,p_argument => 'datetrack_mode'
413 );
414 --
415 hr_api.mandatory_arg_error(p_api_name => l_proc
416 ,p_argument => 'prtt_rmt_aprvd_fr_pymt_id'
417 ,p_argument_value => p_prtt_rmt_aprvd_fr_pymt_id
418 );
419 --
420 hr_api.mandatory_arg_error(p_api_name => l_proc
421 ,p_argument => 'object_version_number'
422 ,p_argument_value => p_object_version_number
423 );
424 --
425 -- Check to ensure the datetrack mode is not INSERT.
426 --
427 If (p_datetrack_mode <> hr_api.g_insert) then
428 --
429 -- We must select and lock the current row.
430 --
431 Open C_Sel1;
432 Fetch C_Sel1 Into ben_pry_shd.g_old_rec;
433 If C_Sel1%notfound then
434 Close C_Sel1;
435 --
436 -- The primary key is invalid therefore we must error
437 --
438 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
439 fnd_message.raise_error;
440 End If;
441 Close C_Sel1;
442 If (p_object_version_number
443 <> ben_pry_shd.g_old_rec.object_version_number) Then
444 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
445 fnd_message.raise_error;
446 End If;
447 --
448 --
449 -- Validate the datetrack mode mode getting the validation start
450 -- and end dates for the specified datetrack operation.
451 --
452 dt_api.validate_dt_mode
453 (p_effective_date => p_effective_date
454 ,p_datetrack_mode => p_datetrack_mode
455 ,p_base_table_name => 'ben_prtt_rmt_aprvd_fr_pymt_f'
456 ,p_base_key_column => 'prtt_rmt_aprvd_fr_pymt_id'
457 ,p_base_key_value => p_prtt_rmt_aprvd_fr_pymt_id
458 ,p_parent_table_name1 => 'ben_prtt_reimbmt_rqst_f'
459 ,p_parent_key_column1 => 'prtt_reimbmt_rqst_id'
460 ,p_parent_key_value1 => ben_pry_shd.g_old_rec.prtt_reimbmt_rqst_id
461 ,p_enforce_foreign_locking => false --true
462 ,p_validation_start_date => l_validation_start_date
463 ,p_validation_end_date => l_validation_end_date
464 );
465 Else
466 --
467 -- We are doing a datetrack 'INSERT' which is illegal within this
468 -- procedure therefore we must error (note: to lck on insert the
469 -- private procedure ins_lck should be called).
470 --
471 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
472 fnd_message.set_token('PROCEDURE', l_proc);
473 fnd_message.set_token('STEP','20');
474 fnd_message.raise_error;
475 End If;
476 --
477 -- Set the validation start and end date OUT arguments
478 --
479 p_validation_start_date := l_validation_start_date;
480 p_validation_end_date := l_validation_end_date;
481 --
482 hr_utility.set_location(' Leaving:'||l_proc, 30);
483 --
484 -- We need to trap the ORA LOCK exception
485 --
486 Exception
487 When HR_Api.Object_Locked then
488 --
489 -- The object is locked therefore we need to supply a meaningful
490 -- error message.
491 --
492 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
493 fnd_message.set_token('TABLE_NAME', 'ben_prtt_rmt_aprvd_fr_pymt_f');
494 fnd_message.raise_error;
495 End lck;
496 --
497 -- ----------------------------------------------------------------------------
498 -- |-----------------------------< convert_args >-----------------------------|
499 -- ----------------------------------------------------------------------------
500 Function convert_args
501 (p_prtt_rmt_aprvd_fr_pymt_id in number
502 ,p_prtt_reimbmt_rqst_id in number
503 ,p_effective_start_date in date
504 ,p_effective_end_date in date
505 ,p_apprvd_fr_pymt_num in number
506 ,p_adjmt_flag in varchar2
507 ,p_aprvd_fr_pymt_amt in number
508 ,p_pymt_stat_cd in varchar2
509 ,p_pymt_stat_rsn_cd in varchar2
510 ,p_pymt_stat_ovrdn_rsn_cd in varchar2
511 ,p_pymt_stat_prr_to_ovrd_cd in varchar2
512 ,p_business_group_id in number
513 ,p_element_entry_value_id in number
514 ,p_pry_attribute_category in varchar2
515 ,p_pry_attribute1 in varchar2
516 ,p_pry_attribute2 in varchar2
517 ,p_pry_attribute3 in varchar2
518 ,p_pry_attribute4 in varchar2
519 ,p_pry_attribute5 in varchar2
520 ,p_pry_attribute6 in varchar2
521 ,p_pry_attribute7 in varchar2
522 ,p_pry_attribute8 in varchar2
523 ,p_pry_attribute9 in varchar2
524 ,p_pry_attribute10 in varchar2
525 ,p_pry_attribute11 in varchar2
526 ,p_pry_attribute12 in varchar2
527 ,p_pry_attribute13 in varchar2
528 ,p_pry_attribute14 in varchar2
529 ,p_pry_attribute15 in varchar2
533 ,p_pry_attribute19 in varchar2
530 ,p_pry_attribute16 in varchar2
531 ,p_pry_attribute17 in varchar2
532 ,p_pry_attribute18 in varchar2
534 ,p_pry_attribute20 in varchar2
535 ,p_pry_attribute21 in varchar2
536 ,p_pry_attribute22 in varchar2
537 ,p_pry_attribute23 in varchar2
538 ,p_pry_attribute24 in varchar2
539 ,p_pry_attribute25 in varchar2
540 ,p_pry_attribute26 in varchar2
541 ,p_pry_attribute27 in varchar2
542 ,p_pry_attribute28 in varchar2
543 ,p_pry_attribute29 in varchar2
544 ,p_pry_attribute30 in varchar2
545 ,p_object_version_number in number
546 )
547 Return g_rec_type is
548 --
549 l_rec g_rec_type;
550 --
551 Begin
552 --
553 -- Convert arguments into local l_rec structure.
554 --
555 l_rec.prtt_rmt_aprvd_fr_pymt_id := p_prtt_rmt_aprvd_fr_pymt_id;
556 l_rec.prtt_reimbmt_rqst_id := p_prtt_reimbmt_rqst_id;
557 l_rec.effective_start_date := p_effective_start_date;
558 l_rec.effective_end_date := p_effective_end_date;
559 l_rec.apprvd_fr_pymt_num := p_apprvd_fr_pymt_num;
560 l_rec.adjmt_flag := p_adjmt_flag;
561 l_rec.aprvd_fr_pymt_amt := p_aprvd_fr_pymt_amt;
562 l_rec.pymt_stat_cd := p_pymt_stat_cd;
563 l_rec.pymt_stat_rsn_cd := p_pymt_stat_rsn_cd;
564 l_rec.pymt_stat_ovrdn_rsn_cd := p_pymt_stat_ovrdn_rsn_cd;
565 l_rec.pymt_stat_prr_to_ovrd_cd := p_pymt_stat_prr_to_ovrd_cd;
566 l_rec.business_group_id := p_business_group_id;
567 l_rec.element_entry_value_id := p_element_entry_value_id;
568 l_rec.pry_attribute_category := p_pry_attribute_category;
569 l_rec.pry_attribute1 := p_pry_attribute1;
570 l_rec.pry_attribute2 := p_pry_attribute2;
571 l_rec.pry_attribute3 := p_pry_attribute3;
572 l_rec.pry_attribute4 := p_pry_attribute4;
573 l_rec.pry_attribute5 := p_pry_attribute5;
574 l_rec.pry_attribute6 := p_pry_attribute6;
575 l_rec.pry_attribute7 := p_pry_attribute7;
576 l_rec.pry_attribute8 := p_pry_attribute8;
577 l_rec.pry_attribute9 := p_pry_attribute9;
578 l_rec.pry_attribute10 := p_pry_attribute10;
579 l_rec.pry_attribute11 := p_pry_attribute11;
580 l_rec.pry_attribute12 := p_pry_attribute12;
581 l_rec.pry_attribute13 := p_pry_attribute13;
582 l_rec.pry_attribute14 := p_pry_attribute14;
583 l_rec.pry_attribute15 := p_pry_attribute15;
584 l_rec.pry_attribute16 := p_pry_attribute16;
585 l_rec.pry_attribute17 := p_pry_attribute17;
586 l_rec.pry_attribute18 := p_pry_attribute18;
587 l_rec.pry_attribute19 := p_pry_attribute19;
588 l_rec.pry_attribute20 := p_pry_attribute20;
589 l_rec.pry_attribute21 := p_pry_attribute21;
590 l_rec.pry_attribute22 := p_pry_attribute22;
591 l_rec.pry_attribute23 := p_pry_attribute23;
592 l_rec.pry_attribute24 := p_pry_attribute24;
593 l_rec.pry_attribute25 := p_pry_attribute25;
594 l_rec.pry_attribute26 := p_pry_attribute26;
595 l_rec.pry_attribute27 := p_pry_attribute27;
596 l_rec.pry_attribute28 := p_pry_attribute28;
597 l_rec.pry_attribute29 := p_pry_attribute29;
598 l_rec.pry_attribute30 := p_pry_attribute30;
599 l_rec.object_version_number := p_object_version_number;
600 --
601 -- Return the plsql record structure.
602 --
603 Return(l_rec);
604 --
605 End convert_args;
606 --
607 end ben_pry_shd;