[Home] [Help]
PACKAGE BODY: APPS.PAY_PSD_SHD
Source
1 Package Body pay_psd_shd as
2 /* $Header: pypsdrhi.pkb 120.1 2005/12/08 05:08 ssekhar noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_psd_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_PL_SII_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_sii_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 sii_details_id
49 ,effective_start_date
50 ,effective_end_date
51 ,per_or_asg_id
52 ,business_group_id
53 ,contract_category
54 ,object_version_number
55 ,emp_social_security_info
56 ,old_age_contribution
57 ,pension_contribution
58 ,sickness_contribution
59 ,work_injury_contribution
60 ,labor_contribution
61 ,health_contribution
62 ,unemployment_contribution
63 ,old_age_cont_end_reason
64 ,pension_cont_end_reason
65 ,sickness_cont_end_reason
66 ,work_injury_cont_end_reason
67 ,labor_fund_cont_end_reason
68 ,health_cont_end_reason
69 ,unemployment_cont_end_reason
70 ,program_id
71 ,program_login_id
72 ,program_application_id
73 ,request_id
74 from pay_pl_sii_details_f
75 where sii_details_id = p_sii_details_id
76 and p_effective_date
77 between effective_start_date and effective_end_date;
78 --
79 l_fct_ret boolean;
80 --
81 Begin
82 --
83 If (p_effective_date is null or
84 p_sii_details_id is null or
85 p_object_version_number is null) Then
86 --
87 -- One of the primary key arguments is null therefore we must
88 -- set the returning function value to false
89 --
90 l_fct_ret := false;
91 Else
92 If (p_sii_details_id =
93 pay_psd_shd.g_old_rec.sii_details_id and
94 p_object_version_number =
95 pay_psd_shd.g_old_rec.object_version_number
96 ) Then
97 --
98 -- The g_old_rec is current therefore we must
99 -- set the returning function to true
100 --
101 l_fct_ret := true;
102 Else
103 --
104 -- Select the current row
105 --
106 Open C_Sel1;
107 Fetch C_Sel1 Into pay_psd_shd.g_old_rec;
108 If C_Sel1%notfound Then
109 Close C_Sel1;
110 --
111 -- The primary key is invalid therefore we must error
112 --
113 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
114 fnd_message.raise_error;
115 End If;
116 Close C_Sel1;
117 If (p_object_version_number
118 <> pay_psd_shd.g_old_rec.object_version_number) Then
119 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
120 fnd_message.raise_error;
121 End If;
122 l_fct_ret := true;
123 End If;
124 End If;
125 Return (l_fct_ret);
126 --
127 End api_updating;
128 --
129 -- ----------------------------------------------------------------------------
130 -- |---------------------------< find_dt_upd_modes >--------------------------|
131 -- ----------------------------------------------------------------------------
132 Procedure find_dt_upd_modes
133 (p_effective_date in date
134 ,p_base_key_value in number
135 ,p_correction out nocopy boolean
136 ,p_update out nocopy boolean
137 ,p_update_override out nocopy boolean
138 ,p_update_change_insert out nocopy boolean
139 ,p_correction_start_date OUT NOCOPY DATE
140 ,p_correction_end_date OUT NOCOPY DATE
141 ,p_update_start_date OUT NOCOPY DATE
142 ,p_update_end_date OUT NOCOPY DATE
143 ,p_override_start_date OUT NOCOPY DATE
144 ,p_override_end_date OUT NOCOPY DATE
145 ,p_upd_chg_start_date OUT NOCOPY DATE
146 ,p_upd_chg_end_date OUT NOCOPY DATE
147 ) is
148 --
149 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
150 --
151 Begin
152 hr_utility.set_location('Entering:'||l_proc, 5);
153 --
154 -- Call the corresponding datetrack api
155 --
156 dt_api.find_dt_upd_modes_and_dates
157 (p_effective_date => p_effective_date
158 ,p_base_table_name => 'pay_pl_sii_details_f'
159 ,p_base_key_column => 'sii_details_id'
160 ,p_base_key_value => p_base_key_value
161 ,p_correction => p_correction
162 ,p_update => p_update
163 ,p_update_override => p_update_override
164 ,p_update_change_insert => p_update_change_insert
165 ,p_correction_start_date => p_correction_start_date
166 ,p_correction_end_date => p_correction_end_date
167 ,p_update_start_date => p_update_start_date
168 ,p_update_end_date => p_update_end_date
169 ,p_override_start_date => p_override_start_date
170 ,p_override_end_date => p_override_end_date
171 ,p_upd_chg_start_date => p_upd_chg_start_date
172 ,p_upd_chg_end_date => p_upd_chg_end_date
173 );
174 --
175 hr_utility.set_location(' Leaving:'||l_proc, 10);
176 End find_dt_upd_modes;
177 --
178 -- ----------------------------------------------------------------------------
179 -- |---------------------------< find_dt_del_modes >--------------------------|
180 -- ----------------------------------------------------------------------------
181 Procedure find_dt_del_modes
182 (p_effective_date in date
183 ,p_base_key_value in number
184 ,p_zap out nocopy boolean
185 ,p_delete out nocopy boolean
186 ,p_future_change out nocopy boolean
187 ,p_delete_next_change out nocopy boolean
188 ) is
189 --
190 l_proc varchar2(72) := g_package||'find_dt_del_modes';
191 --
192 --
193 Begin
194 hr_utility.set_location('Entering:'||l_proc, 5);
195 --
196 -- Call the corresponding datetrack api
197 --
198 dt_api.find_dt_del_modes
199 (p_effective_date => p_effective_date
200 ,p_base_table_name => 'pay_pl_sii_details_f'
201 ,p_base_key_column => 'sii_details_id'
202 ,p_base_key_value => p_base_key_value
203 ,p_zap => p_zap
204 ,p_delete => p_delete
205 ,p_future_change => p_future_change
206 ,p_delete_next_change => p_delete_next_change
207 );
208 --
209 hr_utility.set_location(' Leaving:'||l_proc, 10);
210 End find_dt_del_modes;
211 --
212 -- ----------------------------------------------------------------------------
213 -- |-----------------------< upd_effective_end_date >-------------------------|
214 -- ----------------------------------------------------------------------------
215 Procedure upd_effective_end_date
216 (p_effective_date in date
217 ,p_base_key_value in number
218 ,p_new_effective_end_date in date
219 ,p_validation_start_date in date
220 ,p_validation_end_date in date
221 ,p_object_version_number out nocopy number
222 ) is
223 --
224 l_proc varchar2(72) := g_package||'upd_effective_end_date';
225 l_object_version_number number;
226 --
227 Begin
228 hr_utility.set_location('Entering:'||l_proc, 5);
229 --
230 -- Because we are updating a row we must get the next object
231 -- version number.
232 --
233 l_object_version_number :=
234 dt_api.get_object_version_number
235 (p_base_table_name => 'pay_pl_sii_details_f'
236 ,p_base_key_column => 'sii_details_id'
237 ,p_base_key_value => p_base_key_value
238 );
239 --
240 hr_utility.set_location(l_proc, 10);
241 --
242 --
243 -- Update the specified datetrack row setting the effective
244 -- end date to the specified new effective end date.
245 --
246 update pay_pl_sii_details_f t
247 set t.effective_end_date = p_new_effective_end_date
248 , t.object_version_number = l_object_version_number
249 where t.sii_details_id = p_base_key_value
250 and p_effective_date
251 between t.effective_start_date and t.effective_end_date;
252 --
253 --
254 p_object_version_number := l_object_version_number;
255 hr_utility.set_location(' Leaving:'||l_proc, 15);
256 --
257 End upd_effective_end_date;
258 --
259 -- ----------------------------------------------------------------------------
260 -- |---------------------------------< lck >----------------------------------|
261 -- ----------------------------------------------------------------------------
262 Procedure lck
263 (p_effective_date in date
264 ,p_datetrack_mode in varchar2
265 ,p_sii_details_id in number
266 ,p_object_version_number in number
267 ,p_validation_start_date out nocopy date
268 ,p_validation_end_date out nocopy date
269 ) is
270 --
271 l_proc varchar2(72) := g_package||'lck';
272 l_validation_start_date date;
273 l_validation_end_date date;
274 l_argument varchar2(30);
275 --
276 -- Cursor C_Sel1 selects the current locked row as of session date
277 -- ensuring that the object version numbers match.
278 --
279 Cursor C_Sel1 is
280 select
281 sii_details_id
282 ,effective_start_date
283 ,effective_end_date
284 ,per_or_asg_id
285 ,business_group_id
286 ,contract_category
287 ,object_version_number
288 ,emp_social_security_info
289 ,old_age_contribution
290 ,pension_contribution
291 ,sickness_contribution
292 ,work_injury_contribution
293 ,labor_contribution
294 ,health_contribution
295 ,unemployment_contribution
296 ,old_age_cont_end_reason
297 ,pension_cont_end_reason
298 ,sickness_cont_end_reason
299 ,work_injury_cont_end_reason
300 ,labor_fund_cont_end_reason
301 ,health_cont_end_reason
302 ,unemployment_cont_end_reason
303 ,program_id
304 ,program_login_id
305 ,program_application_id
306 ,request_id
307 from pay_pl_sii_details_f
308 where sii_details_id = p_sii_details_id
309 and p_effective_date
310 between effective_start_date and effective_end_date
311 for update nowait;
312 --
313 --
314 --
315 Begin
316 hr_utility.set_location('Entering:'||l_proc, 5);
317 --
318 -- Ensure that all the mandatory arguments are not null
319 --
320 hr_api.mandatory_arg_error(p_api_name => l_proc
321 ,p_argument => 'effective_date'
322 ,p_argument_value => p_effective_date
323 );
324 --
325 hr_api.mandatory_arg_error(p_api_name => l_proc
326 ,p_argument => 'datetrack_mode'
327 ,p_argument_value => p_datetrack_mode
328 );
329 --
330 hr_api.mandatory_arg_error(p_api_name => l_proc
331 ,p_argument => 'sii_details_id'
332 ,p_argument_value => p_sii_details_id
333 );
334 --
335 hr_api.mandatory_arg_error(p_api_name => l_proc
336 ,p_argument => 'object_version_number'
337 ,p_argument_value => p_object_version_number
338 );
339 --
340 -- Check to ensure the datetrack mode is not INSERT.
341 --
342 If (p_datetrack_mode <> hr_api.g_insert) then
343 --
344 -- We must select and lock the current row.
345 --
346 Open C_Sel1;
347 Fetch C_Sel1 Into pay_psd_shd.g_old_rec;
348 If C_Sel1%notfound then
349 Close C_Sel1;
350 --
351 -- The primary key is invalid therefore we must error
352 --
353 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
354 fnd_message.raise_error;
355 End If;
356 Close C_Sel1;
357 If (p_object_version_number
358 <> pay_psd_shd.g_old_rec.object_version_number) Then
359 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
360 fnd_message.raise_error;
361 End If;
362 --
363 --
367
364 -- Validate the datetrack mode mode getting the validation start
365 -- and end dates for the specified datetrack operation.
366 --
368 if g_old_rec.contract_category in ('CIVIL','TERM_NORMAL','LUMP','F_LUMP') then
369
370 -- Here we are locking the corresponding Assignment record
371 -- a) if the Contract category is "CIVIL" since the SII record for Civil Contracts are stored at the Assignment level
372 -- b) If the Contract category is 'Normal' and the Assignment is terminated. This is cos for Normal Terminated assignments
373 -- the SII record is stored at the Assignment level.
374 -- c) if the Contract category is "LUMP" or "F_LUMP"
375
376 dt_api.validate_dt_mode
377 (p_effective_date => p_effective_date
378 ,p_datetrack_mode => p_datetrack_mode
379 ,p_base_table_name => 'pay_pl_sii_details_f'
380 ,p_base_key_column => 'sii_details_id'
381 ,p_base_key_value => p_sii_details_id
382 ,p_parent_table_name1 => 'per_all_assignments_f'
383 ,p_parent_key_column1 => 'assignment_id'
384 ,p_parent_key_value1 => g_old_rec.per_or_asg_id
385 ,p_enforce_foreign_locking => true
386 ,p_validation_start_date => l_validation_start_date
387 ,p_validation_end_date => l_validation_end_date
388 );
389
390 elsif g_old_rec.contract_category = 'NORMAL' then
391
392 -- Here we are locking the corresponding Person record if the Contract category is
393 -- "NORMAL" since the SII record for Normal Contracts are stored at the Person level
394
395 dt_api.validate_dt_mode
396 (p_effective_date => p_effective_date
397 ,p_datetrack_mode => p_datetrack_mode
398 ,p_base_table_name => 'pay_pl_sii_details_f'
399 ,p_base_key_column => 'sii_details_id'
400 ,p_base_key_value => p_sii_details_id
401 ,p_parent_table_name1 => 'per_people_f'
402 ,p_parent_key_column1 => 'person_id'
403 ,p_parent_key_value1 => g_old_rec.per_or_asg_id
404 ,p_enforce_foreign_locking => true
405 ,p_validation_start_date => l_validation_start_date
406 ,p_validation_end_date => l_validation_end_date
407 );
408
409 end if;
410
411
412 Else
413 --
414 -- We are doing a datetrack 'INSERT' which is illegal within this
415 -- procedure therefore we must error (note: to lck on insert the
416 -- private procedure ins_lck should be called).
417 --
418 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
419 fnd_message.set_token('PROCEDURE', l_proc);
420 fnd_message.set_token('STEP','20');
421 fnd_message.raise_error;
422 End If;
423 --
424 -- Set the validation start and end date OUT arguments
425 --
426 p_validation_start_date := l_validation_start_date;
427 p_validation_end_date := l_validation_end_date;
428 --
429 hr_utility.set_location(' Leaving:'||l_proc, 30);
430 --
431 -- We need to trap the ORA LOCK exception
432 --
433 Exception
434 When HR_Api.Object_Locked then
435 --
436 -- The object is locked therefore we need to supply a meaningful
437 -- error message.
438 --
439 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
440 fnd_message.set_token('TABLE_NAME', 'pay_pl_sii_details_f');
441 fnd_message.raise_error;
442 End lck;
443 --
444 -- ----------------------------------------------------------------------------
445 -- |-----------------------------< convert_args >-----------------------------|
446 -- ----------------------------------------------------------------------------
447 Function convert_args
448 (p_sii_details_id in number
449 ,p_effective_start_date in date
450 ,p_effective_end_date in date
451 ,p_per_or_asg_id in number
452 ,p_business_group_id in number
453 ,p_contract_category in varchar2
454 ,p_object_version_number in number
455 ,p_emp_social_security_info in varchar2
456 ,p_old_age_contribution in varchar2
457 ,p_pension_contribution in varchar2
458 ,p_sickness_contribution in varchar2
459 ,p_work_injury_contribution in varchar2
460 ,p_labor_contribution in varchar2
461 ,p_health_contribution in varchar2
462 ,p_unemployment_contribution in varchar2
463 ,p_old_age_cont_end_reason in varchar2
464 ,p_pension_cont_end_reason in varchar2
465 ,p_sickness_cont_end_reason in varchar2
466 ,p_work_injury_cont_end_reason in varchar2
467 ,p_labor_fund_cont_end_reason in varchar2
468 ,p_health_cont_end_reason in varchar2
469 ,p_unemployment_cont_end_reason in varchar2
470 ,p_program_id in number
471 ,p_program_login_id in number
472 ,p_program_application_id in number
473 ,p_request_id in number
474 )
475 Return g_rec_type is
476 --
477 l_rec g_rec_type;
478 --
479 Begin
480 --
481 -- Convert arguments into local l_rec structure.
482 --
483 l_rec.sii_details_id := p_sii_details_id;
484 l_rec.effective_start_date := p_effective_start_date;
485 l_rec.effective_end_date := p_effective_end_date;
486 l_rec.per_or_asg_id := p_per_or_asg_id;
487 l_rec.business_group_id := p_business_group_id;
488 l_rec.contract_category := p_contract_category;
489 l_rec.object_version_number := p_object_version_number;
490 l_rec.emp_social_security_info := p_emp_social_security_info;
491 l_rec.old_age_contribution := p_old_age_contribution;
495 l_rec.labor_contribution := p_labor_contribution;
492 l_rec.pension_contribution := p_pension_contribution;
493 l_rec.sickness_contribution := p_sickness_contribution;
494 l_rec.work_injury_contribution := p_work_injury_contribution;
496 l_rec.health_contribution := p_health_contribution;
497 l_rec.unemployment_contribution := p_unemployment_contribution;
498 l_rec.old_age_cont_end_reason := p_old_age_cont_end_reason;
499 l_rec.pension_cont_end_reason := p_pension_cont_end_reason;
500 l_rec.sickness_cont_end_reason := p_sickness_cont_end_reason;
501 l_rec.work_injury_cont_end_reason := p_work_injury_cont_end_reason;
502 l_rec.labor_fund_cont_end_reason := p_labor_fund_cont_end_reason;
503 l_rec.health_cont_end_reason := p_health_cont_end_reason;
504 l_rec.unemployment_cont_end_reason := p_unemployment_cont_end_reason;
505 l_rec.program_id := p_program_id;
506 l_rec.program_login_id := p_program_login_id;
507 l_rec.program_application_id := p_program_application_id;
508 l_rec.request_id := p_request_id;
509 --
510 -- Return the plsql record structure.
511 --
512 Return(l_rec);
513 --
514 End convert_args;
515 --
516 end pay_psd_shd;