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