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