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