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