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