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