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