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