[Home] [Help]
PACKAGE BODY: APPS.PAY_RFI_SHD
Source
1 Package Body pay_rfi_shd as
2 /* $Header: pyrfirhi.pkb 120.0 2005/05/29 08:19 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_rfi_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_REPORT_FORMAT_ITEMS_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_report_format_item_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 report_type
60 ,report_qualifier
61 ,report_category
62 ,user_entity_id
63 ,effective_start_date
64 ,effective_end_date
65 ,archive_type
66 ,updatable_flag
67 ,display_sequence
68 ,object_version_number
69 ,report_format_item_id
70 ,null
71 from pay_report_format_items_f
72 where report_format_item_id = p_report_format_item_id
73 and p_effective_date
74 between effective_start_date and effective_end_date;
75 --
76 l_fct_ret boolean;
77 --
78 Begin
79 --
80 If (p_effective_date is null or
81 p_report_format_item_id is null or
82 p_object_version_number is null) Then
83 --
84 -- One of the primary key arguments is null therefore we must
85 -- set the returning function value to false
86 --
87 l_fct_ret := false;
88 Else
89 If (p_report_format_item_id =
90 pay_rfi_shd.g_old_rec.report_format_item_id and
91 p_object_version_number =
92 pay_rfi_shd.g_old_rec.object_version_number
93 ) Then
94 --
95 -- The g_old_rec is current therefore we must
96 -- set the returning function to true
97 --
98 l_fct_ret := true;
99 Else
100 --
101 -- Select the current row
102 --
103 Open C_Sel1;
104 Fetch C_Sel1 Into pay_rfi_shd.g_old_rec;
105 If C_Sel1%notfound Then
106 Close C_Sel1;
107 --
108 -- The primary key is invalid therefore we must error
109 --
110 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
111 fnd_message.raise_error;
112 End If;
113 Close C_Sel1;
114 If (p_object_version_number
115 <> pay_rfi_shd.g_old_rec.object_version_number) Then
116 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
117 fnd_message.raise_error;
118 End If;
119
120 pay_rfi_shd.g_old_rec.report_format_mapping_id
121 := pay_rfm_shd.get_report_format_mapping_id
122 ( p_report_type => pay_rfi_shd.g_old_rec.report_type
123 ,p_report_qualifier => pay_rfi_shd.g_old_rec.report_qualifier
124 ,p_report_category => pay_rfi_shd.g_old_rec.report_category );
125
126 l_fct_ret := true;
127 End If;
128 End If;
129 Return (l_fct_ret);
130 --
131 End api_updating;
132 --
133 -- ----------------------------------------------------------------------------
134 -- |---------------------------< find_dt_upd_modes >--------------------------|
135 -- ----------------------------------------------------------------------------
136 Procedure find_dt_upd_modes
137 (p_effective_date in date
138 ,p_base_key_value in number
139 ,p_correction out nocopy boolean
140 ,p_update out nocopy boolean
141 ,p_update_override out nocopy boolean
142 ,p_update_change_insert out nocopy boolean
143 ) is
144 --
145 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
146 --
147 Begin
148 hr_utility.set_location('Entering:'||l_proc, 5);
149 --
150 -- Call the corresponding datetrack api
151 --
152 dt_api.find_dt_upd_modes
153 (p_effective_date => p_effective_date
154 ,p_base_table_name => 'pay_report_format_items_f'
155 ,p_base_key_column => 'report_format_item_id'
156 ,p_base_key_value => p_base_key_value
157 ,p_correction => p_correction
158 ,p_update => p_update
159 ,p_update_override => p_update_override
160 ,p_update_change_insert => p_update_change_insert
161 );
162 --
163 hr_utility.set_location(' Leaving:'||l_proc, 10);
164 End find_dt_upd_modes;
165 --
166 -- ----------------------------------------------------------------------------
167 -- |---------------------------< find_dt_del_modes >--------------------------|
168 -- ----------------------------------------------------------------------------
169 Procedure find_dt_del_modes
170 (p_effective_date in date
171 ,p_base_key_value in number
172 ,p_zap out nocopy boolean
173 ,p_delete out nocopy boolean
174 ,p_future_change out nocopy boolean
175 ,p_delete_next_change out nocopy boolean
176 ) is
177 --
178 l_proc varchar2(72) := g_package||'find_dt_del_modes';
179 --
180 l_parent_key_value1 number;
181 --
182 Cursor C_Sel1 Is
183 select
184 m.report_format_mapping_id
185 from pay_report_format_items_f t
186 ,pay_report_format_mappings_f m
187 where t.report_format_item_id = p_base_key_value
188 and p_effective_date
189 between t.effective_start_date and t.effective_end_date
190 and t.report_type = m.report_type
191 and t.report_qualifier = m.report_qualifier
192 and t.report_category = m.report_category
193 and p_effective_date
194 between m.effective_start_date and m.effective_end_date;
195 --
196 Begin
197 hr_utility.set_location('Entering:'||l_proc, 5);
198 Open C_sel1;
199 Fetch C_Sel1 Into
200 l_parent_key_value1;
201 If C_Sel1%NOTFOUND then
202 Close C_Sel1;
203 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
204 fnd_message.set_token('PROCEDURE',l_proc);
205 fnd_message.set_token('STEP','10');
206 fnd_message.raise_error;
207 End If;
208 Close C_Sel1;
209 --
210 -- Call the corresponding datetrack api
211 --
212 dt_api.find_dt_del_modes
213 (p_effective_date => p_effective_date
214 ,p_base_table_name => 'pay_report_format_items_f'
215 ,p_base_key_column => 'report_format_item_id'
216 ,p_base_key_value => p_base_key_value
217 ,p_parent_table_name1 => 'pay_report_format_mappings_f'
218 ,p_parent_key_column1 => 'report_format_mapping_id'
219 ,p_parent_key_value1 => l_parent_key_value1
220 ,p_zap => p_zap
221 ,p_delete => p_delete
222 ,p_future_change => p_future_change
223 ,p_delete_next_change => p_delete_next_change
224 );
225 --
226 hr_utility.set_location(' Leaving:'||l_proc, 10);
227 End find_dt_del_modes;
228 --
229 -- ----------------------------------------------------------------------------
230 -- |-----------------------< upd_effective_end_date >-------------------------|
231 -- ----------------------------------------------------------------------------
232 Procedure upd_effective_end_date
233 (p_effective_date in date
234 ,p_base_key_value in number
235 ,p_new_effective_end_date in date
236 ,p_validation_start_date in date
237 ,p_validation_end_date in date
238 ,p_object_version_number out nocopy number
239 ) is
240 --
241 l_proc varchar2(72) := g_package||'upd_effective_end_date';
242 l_object_version_number number;
243 --
244 Begin
245 hr_utility.set_location('Entering:'||l_proc, 5);
246 --
247 -- Because we are updating a row we must get the next object
248 -- version number.
249 --
250 l_object_version_number :=
251 dt_api.get_object_version_number
252 (p_base_table_name => 'pay_report_format_items_f'
253 ,p_base_key_column => 'report_format_item_id'
254 ,p_base_key_value => p_base_key_value
255 );
256 --
257 hr_utility.set_location(l_proc, 10);
258 pay_rfi_shd.g_api_dml := true; -- Set the api dml status
259 --
260 -- Update the specified datetrack row setting the effective
261 -- end date to the specified new effective end date.
262 --
263 update pay_report_format_items_f t
264 set t.effective_end_date = p_new_effective_end_date
265 , t.object_version_number = l_object_version_number
266 where t.report_format_item_id = p_base_key_value
267 and p_effective_date
268 between t.effective_start_date and t.effective_end_date;
269 --
270 pay_rfi_shd.g_api_dml := false; -- Unset the api dml status
271 p_object_version_number := l_object_version_number;
272 hr_utility.set_location(' Leaving:'||l_proc, 15);
273 --
274 Exception
275 When Others Then
276 pay_rfi_shd.g_api_dml := false; -- Unset the api dml status
277 Raise;
278 --
279 End upd_effective_end_date;
280 --
281 -- ----------------------------------------------------------------------------
282 -- |---------------------------------< lck >----------------------------------|
283 -- ----------------------------------------------------------------------------
284 Procedure lck
285 (p_effective_date in date
286 ,p_datetrack_mode in varchar2
287 ,p_report_format_item_id in number
288 ,p_object_version_number in number
289 ,p_validation_start_date out nocopy date
290 ,p_validation_end_date out nocopy date
291 ) is
292 --
293 l_proc varchar2(72) := g_package||'lck';
294 l_validation_start_date date;
295 l_validation_end_date date;
296 l_argument varchar2(30);
297 --
298 -- Cursor C_Sel1 selects the current locked row as of session date
299 -- ensuring that the object version numbers match.
300 --
301 Cursor C_Sel1 is
302 select
303 report_type
304 ,report_qualifier
305 ,report_category
306 ,user_entity_id
307 ,effective_start_date
308 ,effective_end_date
309 ,archive_type
310 ,updatable_flag
311 ,display_sequence
312 ,object_version_number
313 ,report_format_item_id
314 ,null
315 from pay_report_format_items_f
316 where report_format_item_id = p_report_format_item_id
317 and p_effective_date
318 between effective_start_date and effective_end_date
319 for update nowait;
320 --
321 --
322 --
323 Begin
324 hr_utility.set_location('Entering:'||l_proc, 5);
325 --
326 -- Ensure that all the mandatory arguments are not null
327 --
328 hr_api.mandatory_arg_error(p_api_name => l_proc
329 ,p_argument => 'effective_date'
330 ,p_argument_value => p_effective_date
331 );
332 --
333 hr_api.mandatory_arg_error(p_api_name => l_proc
334 ,p_argument => 'datetrack_mode'
335 ,p_argument_value => p_datetrack_mode
336 );
337 --
338 hr_api.mandatory_arg_error(p_api_name => l_proc
339 ,p_argument => 'report_format_item_id'
340 ,p_argument_value => p_report_format_item_id
341 );
342 --
343 hr_api.mandatory_arg_error(p_api_name => l_proc
344 ,p_argument => 'object_version_number'
345 ,p_argument_value => p_object_version_number
346 );
347 --
348 -- Check to ensure the datetrack mode is not INSERT.
349 --
350 If (p_datetrack_mode <> hr_api.g_insert) then
351 --
352 -- We must select and lock the current row.
353 --
354 Open C_Sel1;
355 Fetch C_Sel1 Into pay_rfi_shd.g_old_rec;
356 If C_Sel1%notfound then
357 Close C_Sel1;
358 --
359 -- The primary key is invalid therefore we must error
360 --
361 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
362 fnd_message.raise_error;
363 End If;
364 Close C_Sel1;
365 If (p_object_version_number
366 <> pay_rfi_shd.g_old_rec.object_version_number) Then
367 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
368 fnd_message.raise_error;
369 End If;
370
371 pay_rfi_shd.g_old_rec.report_format_mapping_id
372 := pay_rfm_shd.get_report_format_mapping_id
373 ( p_report_type => pay_rfi_shd.g_old_rec.report_type
374 ,p_report_qualifier => pay_rfi_shd.g_old_rec.report_qualifier
375 ,p_report_category => pay_rfi_shd.g_old_rec.report_category );
376
377
378 --
379 --
380 -- Validate the datetrack mode mode getting the validation start
381 -- and end dates for the specified datetrack operation.
382 --
383 dt_api.validate_dt_mode
384 (p_effective_date => p_effective_date
385 ,p_datetrack_mode => p_datetrack_mode
386 ,p_base_table_name => 'pay_report_format_items_f'
387 ,p_base_key_column => 'report_format_item_id'
391 ,p_parent_key_value1 => pay_rfi_shd.g_old_rec.report_format_mapping_id
388 ,p_base_key_value => p_report_format_item_id
389 ,p_parent_table_name1 => 'pay_report_format_mappings_f'
390 ,p_parent_key_column1 => 'report_format_mapping_id'
392 ,p_enforce_foreign_locking => true
393 ,p_validation_start_date => l_validation_start_date
394 ,p_validation_end_date => l_validation_end_date
395 );
396 Else
397 --
398 -- We are doing a datetrack 'INSERT' which is illegal within this
399 -- procedure therefore we must error (note: to lck on insert the
400 -- private procedure ins_lck should be called).
401 --
402 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
403 fnd_message.set_token('PROCEDURE', l_proc);
404 fnd_message.set_token('STEP','20');
405 fnd_message.raise_error;
406 End If;
407 --
408 -- Set the validation start and end date OUT arguments
409 --
410 p_validation_start_date := l_validation_start_date;
411 p_validation_end_date := l_validation_end_date;
412 --
413 hr_utility.set_location(' Leaving:'||l_proc, 30);
414 --
415 -- We need to trap the ORA LOCK exception
416 --
417 Exception
418 When HR_Api.Object_Locked then
419 --
420 -- The object is locked therefore we need to supply a meaningful
421 -- error message.
422 --
423 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
424 fnd_message.set_token('TABLE_NAME', 'pay_report_format_items_f');
425 fnd_message.raise_error;
426 End lck;
427 --
428 -- ----------------------------------------------------------------------------
429 -- |-----------------------------< convert_args >-----------------------------|
430 -- ----------------------------------------------------------------------------
431 Function convert_args
432 (p_report_type in varchar2
433 ,p_report_qualifier in varchar2
434 ,p_report_category in varchar2
435 ,p_user_entity_id in number
436 ,p_effective_start_date in date
437 ,p_effective_end_date in date
438 ,p_archive_type in varchar2
439 ,p_updatable_flag in varchar2
440 ,p_display_sequence in number
441 ,p_object_version_number in number
442 ,p_report_format_item_id in number
443 ,p_report_format_mapping_id in number
444 )
445 Return g_rec_type is
446 --
447 l_rec g_rec_type;
448 --
449 Begin
450 --
451 -- Convert arguments into local l_rec structure.
452 --
453 l_rec.report_type := p_report_type;
454 l_rec.report_qualifier := p_report_qualifier;
455 l_rec.report_category := p_report_category;
456 l_rec.user_entity_id := p_user_entity_id;
457 l_rec.effective_start_date := p_effective_start_date;
458 l_rec.effective_end_date := p_effective_end_date;
459 l_rec.archive_type := p_archive_type;
460 l_rec.updatable_flag := p_updatable_flag;
461 l_rec.display_sequence := p_display_sequence;
462 l_rec.object_version_number := p_object_version_number;
463 l_rec.report_format_item_id := p_report_format_item_id;
464 l_rec.report_format_mapping_id := p_report_format_mapping_id;
465 --
466 -- Return the plsql record structure.
467 --
468 Return(l_rec);
469 --
470 End convert_args;
471 --
472 -- ----------------------------------------------------------------------------
473 -- |---------------------< get_report_format_item_id >------------------------|
474 -- ----------------------------------------------------------------------------
475 Function get_report_format_item_id
476 (p_report_type in varchar2
477 ,p_report_qualifier in varchar2
478 ,p_report_category in varchar2
479 ,p_user_entity_id in number
480 )
481 Return number is
482 --
483 cursor csr_format_item_id is
484 select distinct report_format_item_id
485 from pay_report_format_items_f
486 where report_type = p_report_type
487 and report_qualifier = p_report_qualifier
488 and report_category = p_report_category
489 and user_entity_id = p_user_entity_id ;
490 --
491 l_proc varchar2(72) := g_package||'get_report_format_item_id';
492 l_report_format_item_id PAY_REPORT_FORMAT_ITEMS_F.REPORT_FORMAT_ITEM_ID%TYPE;
493 --
494 Begin
495 --
496 hr_utility.set_location('Entering:'||l_proc, 5);
497
498 if p_report_type is null or p_report_qualifier is null or
499 p_report_category is null or p_user_entity_id is null then
500
501 return null;
502
503 end if;
504
505 open csr_format_item_id;
506 fetch csr_format_item_id into l_report_format_item_id;
507
508 if csr_format_item_id%ROWCOUNT > 1 then
509
510 close csr_format_item_id;
511
512 fnd_message.set_name( 'PAY' , 'PAY_33255_INV_SKEY' );
513 fnd_message.set_token( 'SURROGATE_ID' , 'REPORT_FORMAT_ITEM_ID' );
514 fnd_message.set_token( 'ENTITY' , 'REPORT FORMAT ITEM' );
515 fnd_message.raise_error ;
516
517 end if;
518
519 if csr_format_item_id%found and l_report_format_item_id is null then
520
521 close csr_format_item_id;
522
523 fnd_message.set_name( 'PAY' , 'PAY_33255_INV_SKEY' );
524 fnd_message.set_token( 'SURROGATE_ID' , 'REPORT_FORMAT_ITEM_ID' );
525 fnd_message.set_token( 'ENTITY' , 'REPORT FORMAT ITEM' );
526 fnd_message.raise_error ;
527
528 end if;
529
530 close csr_format_item_id;
531
532 hr_utility.set_location(' Leaving:'||l_proc, 10);
533
534 return l_report_format_item_id;
535 --
536 End get_report_format_item_id;
537 --
538 end pay_rfi_shd;