[Home] [Help]
PACKAGE BODY: APPS.PQH_FR_EMP_STAT_SITUATION_API
Source
1 PACKAGE BODY PQH_FR_EMP_STAT_SITUATION_API as
2 /* $Header: pqpsuapi.pkb 120.0 2005/05/29 02:19:33 appldev noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' PQH_FR_EMP_STAT_SITUATION_API.';
7 --
8 --
9 --
10 PROCEDURE update_assignments(p_person_id IN NUMBER
11 ,p_emp_stat_situation_id IN NUMBER DEFAULT NULL
12 ,p_statutory_situation_id IN NUMBER
13 ,p_start_date IN DATE
14 ,p_end_date IN DATE DEFAULT NULL)
15 IS
16 --
17 --Cursor Declaration
18 --Cursor to fetch Statutory Situation Details.
19 CURSOR csr_situation_details(p_statutory_situation_id IN NUMBER) IS
20 SELECT situation_type,
21 sub_type,
22 NVL(reserve_position,'N') reserve_position,
23 NVL(remuneration_paid,'N') remuneration_paid,
24 NVL(renewable_allowed,'N') renewable_allowed,
25 NVL(default_flag,'N') default_flag,
26 NVL(allow_progression_flag,'N') allow_progression_flag,
27 NVL(extend_probation_period_flag,'N') extend_probation_period_flag,
28 NVL(remunerate_assign_status_id,-1) remunerate_assign_status_id
29 FROM pqh_fr_stat_situations
30 WHERE statutory_situation_id = p_statutory_situation_id;
31 --
32 --Cursor to fetch Assignment Details.
33 CURSOR csr_career_dtls(p_person_id IN NUMBER,p_eff_date IN DATE) IS
34 SELECT asg.assignment_id,
35 asg.assignment_type,
36 asg.effective_start_date,
37 asg.effective_end_date,
38 asg.assignment_status_type_id,
39 ast.per_system_status,
40 ast.pay_system_status,
41 asg.object_version_number
42 FROM per_all_assignments_f asg,
43 per_assignment_status_types ast
44 WHERE asg.person_id = p_person_id
45 AND p_eff_date BETWEEN asg.effective_start_date AND asg.effective_end_date
46 AND asg.primary_flag = 'Y'
47 AND asg.assignment_status_type_id = ast.assignment_status_type_id;
48 --
49 --Cursor to fetch Affectation details.
50 CURSOR csr_affectations(p_career_id IN NUMBER,p_per_status IN VARCHAR2,p_aff_st_date IN DATE,p_aff_end_date IN DATE) IS
51 SELECT assignment_id,
52 effective_start_date,
53 effective_end_date,
54 assignment_type,
55 asg.object_version_number
56 FROM per_all_assignments_f asg,
57 hr_soft_coding_keyflex scl,
58 per_assignment_status_types ast
59 WHERE asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
60 AND scl.segment26 = p_career_id
61 AND asg.assignment_status_type_id = ast.assignment_status_type_id
62 AND ast.per_system_status = p_per_status
63 AND(p_aff_st_date BETWEEN asg.effective_start_date AND asg.effective_end_date
64 OR asg.effective_start_date BETWEEN p_aff_st_date AND NVL(p_aff_end_date,p_aff_st_date))
65 ORDER BY effective_start_date;
66 --
67 --Cursor to check if Affectation Assignment is Terminated in Future.
68 CURSOR csr_invalid_term_assign(p_affect_asg_id IN NUMBER,
69 p_term_st_dt IN DATE) IS
70 SELECT 'Y'
71 FROM per_all_assignments_f asg,
72 per_assignment_status_types ast
73 where asg.assignment_id = p_affect_asg_id
74 and asg.effective_end_date >= p_term_st_dt
75 and ast.assignment_status_type_id = asg.assignment_status_type_id
76 and ast.per_system_status = 'TERM_ASSIGN';
77 --
78 --Variable Declarations
79 lr_career_rec csr_career_dtls%ROWTYPE;
80 lr_sit_dtls csr_situation_details%ROWTYPE;
81 l_dt_mode VARCHAR2(100);
82 l_career_ovn NUMBER(9);
83 l_career_esd DATE;
84 l_career_eed DATE;
85 l_affect_ovn NUMBER(9);
86 l_affect_asg_id NUMBER(15);
87 l_affect_esd DATE;
88 l_affect_eed DATE;
89 l_warn_future_changes BOOLEAN;
90 l_warn_entries_changed VARCHAR2(1000);
91 l_warn_pay_proposal BOOLEAN;
92 l_eff_dt DATE;
93 l_term_exists VARCHAR2(1);
94 l_proc VARCHAR2(72) := g_package||'update_assignments';
95 --
96 BEGIN
97 --
98 HR_UTILITY.set_location('Entering:'|| l_proc, 10);
99 OPEN csr_situation_details(p_statutory_situation_id);
100 FETCH csr_situation_details INTO lr_sit_dtls.situation_type,
101 lr_sit_dtls.sub_type,
102 lr_sit_dtls.reserve_position,
103 lr_sit_dtls.remuneration_paid,
104 lr_sit_dtls.renewable_allowed,
105 lr_sit_dtls.default_flag,
106 lr_sit_dtls.allow_progression_flag,
107 lr_sit_dtls.extend_probation_period_flag,
108 lr_sit_dtls.remunerate_assign_status_id;
109 IF csr_situation_details%NOTFOUND THEN
110 CLOSE csr_situation_details;
111 FND_MESSAGE.set_name('PQH','FR_PQH_STAT_SIT_INVALID_SIT');
112 FND_MESSAGE.raise_error;
113 END IF;
114 CLOSE csr_situation_details;
115 --
116 OPEN csr_career_dtls(p_person_id,p_start_date);
117 FETCH csr_career_dtls INTO lr_career_rec.assignment_id,
118 lr_career_rec.assignment_type,
119 lr_career_rec.effective_start_date,
120 lr_career_rec.effective_end_date,
121 lr_career_rec.assignment_status_type_id,
122 lr_career_rec.per_system_status,
123 lr_career_rec.pay_system_status,
124 lr_career_rec.object_version_number;
125 CLOSE csr_career_dtls;
126 --
127 l_career_ovn := lr_career_rec.object_version_number;
128 --
129 --Check if Situation is a Reinstate situation.
130 IF lr_sit_dtls.situation_type = 'IA' AND lr_sit_dtls.sub_type = 'IA_N' THEN
131 --
132 --Activate all affectations that were suspended by earlier situation.
133 FOR lr_affectations IN csr_affectations(lr_career_rec.assignment_id,'SUSP_ASSIGN',p_start_date,p_end_date)
134 LOOP
135 --
136 IF lr_affectations.effective_start_date > p_start_date THEN
137 l_eff_dt := lr_affectations.effective_start_date;
138 ELSE
139 l_eff_dt := p_start_date;
140 END IF;
141 l_affect_asg_id := lr_affectations.assignment_id;
142 l_affect_ovn := lr_affectations.object_version_number;
143 l_dt_mode := pqh_fr_utility.get_datetrack_mode(p_effective_date => l_eff_dt
144 ,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
145 ,p_base_key_column => 'ASSIGNMENT_ID'
146 ,p_base_key_value => l_affect_asg_id);
147 --
148 IF lr_affectations.assignment_type = 'E' THEN
149 --
150 hr_assignment_api.activate_emp_asg(p_effective_date => l_eff_dt
151 ,p_datetrack_update_mode => l_dt_mode
152 ,p_assignment_id => l_affect_asg_id
153 ,p_object_version_number => l_affect_ovn
154 ,p_effective_start_date => l_affect_esd
155 ,p_effective_end_date => l_affect_eed);
156 --
157 ELSIF lr_affectations.assignment_type = 'C' THEN
158 --
159 hr_assignment_api.activate_cwk_asg(p_effective_date => l_eff_dt
160 ,p_datetrack_update_mode => l_dt_mode
161 ,p_assignment_id => l_affect_asg_id
162 ,p_object_version_number => l_affect_ovn
163 ,p_effective_start_date => l_affect_esd
164 ,p_effective_end_date => l_affect_eed);
165 --
166 END IF;
167 --
168 END LOOP;
169 --
170 l_dt_mode := pqh_fr_utility.get_datetrack_mode(p_effective_date => p_start_date
171 ,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
172 ,p_base_key_column => 'ASSIGNMENT_ID'
173 ,p_base_key_value => lr_career_rec.assignment_id);
174 --
175 IF lr_career_rec.assignment_type = 'C' THEN
176 --
177 hr_assignment_api.activate_cwk_asg(p_effective_date => p_start_date
178 ,p_datetrack_update_mode => l_dt_mode
179 ,p_assignment_id => lr_career_rec.assignment_id
180 ,p_object_version_number => l_career_ovn
181 ,p_effective_start_date => l_career_esd
182 ,p_effective_end_date => l_career_eed);
183 --
184 ELSIF lr_career_rec.assignment_type = 'E' THEN
185 --
186 hr_assignment_api.activate_emp_asg(p_effective_date => p_start_date
187 ,p_datetrack_update_mode => l_dt_mode
188 ,p_assignment_id => lr_career_rec.assignment_id
189 ,p_object_version_number => l_career_ovn
190 ,p_effective_start_date => l_career_esd
191 ,p_effective_end_date => l_career_eed);
192 --
193 END IF;
194 --
195 ELSE --Terminate or Suspend assignment if Situation is not In Activity Normal.
196 --
197 FOR lr_affectations IN csr_affectations(lr_career_rec.assignment_id,'ACTIVE_ASSIGN',p_start_date,p_end_date)
198 LOOP
199 --
200 --Added below condition because assignment status cannot be changed if it is effective for one day only.
201 IF TRUNC(lr_affectations.effective_start_date) <> TRUNC(lr_affectations.effective_end_date) THEN
202 --Added below condition because if Affectation starts after the Primary Assignment Start Dt
203 --then we want to Terminate or Suspend Assignment from its Effective Start Date.
204 IF lr_affectations.effective_start_date > p_start_date THEN
205 l_eff_dt := lr_affectations.effective_start_date;
206 ELSE
207 l_eff_dt := p_start_date;
208 END IF;
209 l_affect_asg_id := lr_affectations.assignment_id;
210 l_affect_ovn := lr_affectations.object_version_number;
211 l_dt_mode := pqh_fr_utility.get_datetrack_mode(p_effective_date => l_eff_dt
212 ,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
213 ,p_base_key_column => 'ASSIGNMENT_ID'
214 ,p_base_key_value => lr_affectations.assignment_id);
215 --
216 IF lr_affectations.assignment_type = 'E' THEN
217 --
218 IF lr_sit_dtls.reserve_position = 'Y' THEN
219 --
220 hr_assignment_api.suspend_emp_asg(p_effective_date => l_eff_dt
221 ,p_datetrack_update_mode => l_dt_mode
222 ,p_assignment_id => l_affect_asg_id
223 ,p_object_version_number => l_affect_ovn
224 ,p_effective_start_date => l_affect_esd
225 ,p_effective_end_date => l_affect_eed);
226 --
227 ELSIF lr_sit_dtls.reserve_position = 'N' THEN
228 --
229 --Added below condition because API terminates Assignment one day after requested effective date.
230 IF l_eff_dt = p_start_date THEN
231 l_eff_dt := TRUNC(p_start_date-1);
232 END IF;
233 --
234 --Check whether the Affectation being terminated is not already terminated in future.
235 OPEN csr_invalid_term_assign(l_affect_asg_id,l_eff_dt);
236 FETCH csr_invalid_term_assign into l_term_exists;
237 IF csr_invalid_term_assign%FOUND THEN
238 CLOSE csr_invalid_term_assign;
239 FND_MESSAGE.set_name('PQH','FR_PQH_AFFECT_TERM_IN_FUTURE');
240 FND_MESSAGE.raise_error;
241 ELSE
242 CLOSE csr_invalid_term_assign;
243 END IF;
244 --
245 hr_assignment_api.actual_termination_emp_asg
246 (p_assignment_id => l_affect_asg_id
247 ,p_object_version_number => l_affect_ovn
248 ,p_actual_termination_date => TRUNC(l_eff_dt)
249 ,p_effective_start_date => l_affect_esd
250 ,p_effective_end_date => l_affect_eed
251 ,p_asg_future_changes_warning => l_warn_future_changes
252 ,p_entries_changed_warning => l_warn_entries_changed
253 ,p_pay_proposal_warning => l_warn_pay_proposal);
254 --
255 END IF;
256 --
257 ELSIF lr_affectations.assignment_type = 'C' THEN
258 --
259 IF lr_sit_dtls.reserve_position = 'Y' THEN
260 --
261 hr_assignment_api.suspend_cwk_asg(p_effective_date => l_eff_dt
262 ,p_datetrack_update_mode => l_dt_mode
263 ,p_assignment_id => l_affect_asg_id
264 ,p_object_version_number => l_affect_ovn
265 ,p_effective_start_date => l_affect_esd
266 ,p_effective_end_date => l_affect_eed);
267 --
268 ELSIF lr_sit_dtls.reserve_position = 'N' THEN
269 --
270 --Added below condition because API terminates Assignment one day after requested effective date.
271 IF l_eff_dt = p_start_date THEN
272 l_eff_dt := TRUNC(p_start_date-1);
273 END IF;
274 hr_assignment_api.actual_termination_emp_asg(p_assignment_id => l_affect_asg_id
275 ,p_object_version_number => l_affect_ovn
276 ,p_actual_termination_date => TRUNC(l_eff_dt)
277 ,p_effective_start_date => l_affect_esd
278 ,p_effective_end_date => l_affect_eed
279 ,p_asg_future_changes_warning => l_warn_future_changes
280 ,p_entries_changed_warning => l_warn_entries_changed
281 ,p_pay_proposal_warning => l_warn_pay_proposal);
282 --
283 END IF;
284 --
285 END IF;
286 --
287 END IF;
288 --
289 END LOOP;
290 --
291 l_dt_mode := pqh_fr_utility.get_datetrack_mode(p_effective_date => p_start_date
292 ,p_base_table_name => 'PER_ALL_ASSIGNMENTS_F'
293 ,p_base_key_column => 'ASSIGNMENT_ID'
294 ,p_base_key_value => lr_career_rec.assignment_id);
295 --
296 IF lr_sit_dtls.remuneration_paid = 'Y' AND lr_sit_dtls.remunerate_assign_status_id = -1 THEN
297 FND_MESSAGE.set_name('PQH','FR_PQH_NO_REMU_ASG_STAT');
298 FND_MESSAGE.raise_error;
299 ELSIF lr_sit_dtls.remuneration_paid = 'Y' AND lr_sit_dtls.remunerate_assign_status_id <> -1 THEN
300 --
301 IF lr_career_rec.assignment_type = 'C' THEN
302 --
303 hr_assignment_api.suspend_cwk_asg
304 (p_effective_date => p_start_date
305 ,p_datetrack_update_mode => l_dt_mode
306 ,p_assignment_id => lr_career_rec.assignment_id
307 ,p_object_version_number => l_career_ovn
311 --
308 ,p_assignment_status_type_id => lr_sit_dtls.remunerate_assign_status_id
309 ,p_effective_start_date => l_career_esd
310 ,p_effective_end_date => l_career_eed);
312 ELSIF lr_career_rec.assignment_type = 'E' THEN
313 --
314 hr_assignment_api.suspend_emp_asg
315 (p_effective_date => p_start_date
316 ,p_datetrack_update_mode => l_dt_mode
317 ,p_assignment_id => lr_career_rec.assignment_id
318 ,p_object_version_number => l_career_ovn
319 ,p_assignment_status_type_id => lr_sit_dtls.remunerate_assign_status_id
320 ,p_effective_start_date => l_career_esd
321 ,p_effective_end_date => l_career_eed);
322 --
323 END IF;
324 --
325 ELSE
326 --
327 IF lr_career_rec.assignment_type = 'C' THEN
328 --
329 hr_assignment_api.suspend_cwk_asg
330 (p_effective_date => p_start_date
331 ,p_datetrack_update_mode => l_dt_mode
332 ,p_assignment_id => lr_career_rec.assignment_id
333 ,p_object_version_number => l_career_ovn
334 ,p_effective_start_date => l_career_esd
335 ,p_effective_end_date => l_career_eed);
336 --
337 ELSIF lr_career_rec.assignment_type = 'E' THEN
338 --
339 hr_assignment_api.suspend_emp_asg
340 (p_effective_date => p_start_date
341 ,p_datetrack_update_mode => l_dt_mode
342 ,p_assignment_id => lr_career_rec.assignment_id
343 ,p_object_version_number => l_career_ovn
344 ,p_effective_start_date => l_career_esd
345 ,p_effective_end_date => l_career_eed);
346 --
347 END IF;
348 --
349 END IF;
350 --
351 END IF;
352 --
353 END update_assignments;
354 --
355 --
356 -- ----------------------------------------------------------------------------
357 -- |------------------------< CREATE_EMP_STAT_SITUATION >----------------------|
358 -- ----------------------------------------------------------------------------
359 --
360 procedure CREATE_EMP_STAT_SITUATION
361 (p_validate IN boolean default false
362 ,p_effective_date IN date
363 ,P_STATUTORY_SITUATION_ID IN NUMBER
364 ,P_PERSON_ID IN NUMBER
365 ,P_PROVISIONAL_START_DATE IN DATE
366 ,P_PROVISIONAL_END_DATE IN DATE
367 ,P_ACTUAL_START_DATE IN DATE default null
368 ,P_ACTUAL_END_DATE IN DATE default null
369 ,P_APPROVAL_FLAG IN VARCHAR2 default null
370 ,P_COMMENTS IN VARCHAR2 default null
371 ,P_CONTACT_PERSON_ID IN NUMBER default null
372 ,P_CONTACT_RELATIONSHIP IN VARCHAR2 default null
373 ,P_EXTERNAL_ORGANIZATION_ID IN NUMBER default null
374 ,P_RENEWAL_FLAG IN VARCHAR2 default null
375 ,P_RENEW_STAT_SITUATION_ID IN NUMBER default null
376 ,P_SECONDED_CAREER_ID IN NUMBER default null
377 ,P_ATTRIBUTE_CATEGORY IN VARCHAR2 default null
378 ,P_ATTRIBUTE1 IN VARCHAR2 default null
379 ,P_ATTRIBUTE2 IN VARCHAR2 default null
380 ,P_ATTRIBUTE3 IN VARCHAR2 default null
381 ,P_ATTRIBUTE4 IN VARCHAR2 default null
382 ,P_ATTRIBUTE5 IN VARCHAR2 default null
383 ,P_ATTRIBUTE6 IN VARCHAR2 default null
384 ,P_ATTRIBUTE7 IN VARCHAR2 default null
385 ,P_ATTRIBUTE8 IN VARCHAR2 default null
386 ,P_ATTRIBUTE9 IN VARCHAR2 default null
387 ,P_ATTRIBUTE10 IN VARCHAR2 default null
388 ,P_ATTRIBUTE11 IN VARCHAR2 default null
389 ,P_ATTRIBUTE12 IN VARCHAR2 default null
390 ,P_ATTRIBUTE13 IN VARCHAR2 default null
391 ,P_ATTRIBUTE14 IN VARCHAR2 default null
392 ,P_ATTRIBUTE15 IN VARCHAR2 default null
393 ,P_ATTRIBUTE16 IN VARCHAR2 default null
394 ,P_ATTRIBUTE17 IN VARCHAR2 default null
395 ,P_ATTRIBUTE18 IN VARCHAR2 default null
396 ,P_ATTRIBUTE19 IN VARCHAR2 default null
397 ,P_ATTRIBUTE20 IN VARCHAR2 default null
398 ,P_ATTRIBUTE21 IN VARCHAR2 default null
399 ,P_ATTRIBUTE22 IN VARCHAR2 default null
400 ,P_ATTRIBUTE23 IN VARCHAR2 default null
401 ,P_ATTRIBUTE24 IN VARCHAR2 default null
402 ,P_ATTRIBUTE25 IN VARCHAR2 default null
403 ,P_ATTRIBUTE26 IN VARCHAR2 default null
404 ,P_ATTRIBUTE27 IN VARCHAR2 default null
405 ,P_ATTRIBUTE28 IN VARCHAR2 default null
406 ,P_ATTRIBUTE29 IN VARCHAR2 default null
407 ,P_ATTRIBUTE30 IN VARCHAR2 default null
408 ,P_EMP_STAT_SITUATION_ID OUT NOCOPY NUMBER
409 ,P_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER
410 ) is
411 --
412 -- Declare cursors and local variables
413 --
417 l_proc varchar2(72) := g_package||'create_emp_stat_situation';
414 l_emp_stat_situation_id NUMBER(15);
415 l_object_version_number NUMBER(9);
416 l_actual_start_date DATE;
418 begin
419 hr_utility.set_location('Entering:'|| l_proc, 10);
420 savepoint create_emp_stat_situation;
421 --
422 -- Validation in addition to Row Handlers
423 --
424 If (P_APPROVAL_FLAG = 'Y') Then
425 l_actual_start_date := p_provisional_start_date;
426 Else
427 l_actual_start_date := P_ACTUAL_START_DATE;
428 End if;
429 pqh_psu_ins.ins(
430 P_EFFECTIVE_DATE => p_effective_date
431 ,P_STATUTORY_SITUATION_ID => p_statutory_situation_id
432 ,P_PERSON_ID => p_person_id
433 ,P_PROVISIONAL_START_DATE => p_provisional_start_date
434 ,P_PROVISIONAL_END_DATE => p_provisional_end_date
435 ,P_ACTUAL_START_DATE => l_actual_start_date
436 ,P_ACTUAL_END_DATE => p_actual_end_date
437 ,P_APPROVAL_FLAG => p_approval_flag
438 ,P_COMMENTS => p_comments
439 ,P_CONTACT_PERSON_ID => p_contact_person_id
440 ,P_CONTACT_RELATIONSHIP => p_contact_relationship
441 ,P_EXTERNAL_ORGANIZATION_ID => p_external_organization_id
442 ,P_RENEWAL_FLAG => p_renewal_flag
443 ,P_RENEW_STAT_SITUATION_ID => p_renew_stat_situation_id
444 ,P_SECONDED_CAREER_ID => p_seconded_career_id
445 ,P_ATTRIBUTE_CATEGORY => p_attribute_category
446 ,P_ATTRIBUTE1 => p_attribute1
447 ,P_ATTRIBUTE2 => p_attribute2
448 ,P_ATTRIBUTE3 => p_attribute3
449 ,P_ATTRIBUTE4 => p_attribute4
450 ,P_ATTRIBUTE5 => p_attribute5
451 ,P_ATTRIBUTE6 => p_attribute6
452 ,P_ATTRIBUTE7 => p_attribute7
453 ,P_ATTRIBUTE8 => p_attribute8
454 ,P_ATTRIBUTE9 => p_attribute9
455 ,P_ATTRIBUTE10 => p_attribute10
456 ,P_ATTRIBUTE11 => p_attribute11
457 ,P_ATTRIBUTE12 => p_attribute12
458 ,P_ATTRIBUTE13 => p_attribute13
459 ,P_ATTRIBUTE14 => p_attribute14
460 ,P_ATTRIBUTE15 => p_attribute15
461 ,P_ATTRIBUTE16 => p_attribute16
462 ,P_ATTRIBUTE17 => p_attribute17
463 ,P_ATTRIBUTE18 => p_attribute18
464 ,P_ATTRIBUTE19 => p_attribute19
465 ,P_ATTRIBUTE20 => p_attribute20
466 ,P_ATTRIBUTE21 => p_attribute21
467 ,P_ATTRIBUTE22 => p_attribute22
468 ,P_ATTRIBUTE23 => p_attribute23
469 ,P_ATTRIBUTE24 => p_attribute24
470 ,P_ATTRIBUTE25 => p_attribute25
471 ,P_ATTRIBUTE26 => p_attribute26
472 ,P_ATTRIBUTE27 => p_attribute27
473 ,P_ATTRIBUTE28 => p_attribute28
474 ,P_ATTRIBUTE29 => p_attribute29
475 ,P_ATTRIBUTE30 => p_attribute30
476 ,P_EMP_STAT_SITUATION_ID => l_emp_stat_situation_id
477 ,P_OBJECT_VERSION_NUMBER => l_object_version_number );
478 --
479 --
480 -- Processing Logic
481 /* Commented by deenath.
482 IF p_approval_flag = 'Y' THEN
483 update_assignments(p_person_id => p_person_id
484 ,p_statutory_situation_id => p_statutory_situation_id
485 ,p_start_date => NVL(p_actual_start_date,p_provisional_start_date)
486 ,p_end_date => NVL(p_actual_end_date,p_provisional_end_date) );
487 END IF;
488 */
489 --
490 -- Set all output arguments
491 --
492 IF p_validate = TRUE THEN
493 raise hr_api.validate_enabled;
494 END IF;
495 p_emp_stat_situation_id := l_emp_stat_situation_id;
496 p_object_version_number := l_object_version_number;
497 hr_utility.set_location(' Leaving:'||l_proc, 40);
498 EXCEPTION
499 when hr_api.validate_enabled then
500 --
501 -- As the Validate_Enabled exception has been raised
502 -- we must rollback to the savepoint
503 --
504 ROLLBACK TO create_emp_stat_situation;
505 p_emp_stat_situation_id := null;
506 p_object_version_number := null;
507 hr_utility.set_location(' Leaving:'||l_proc, 41);
508 When Others THEN
509 Rollback to create_emp_stat_situation;
510 hr_utility.set_location(' Leaving:'||l_proc, 42);
511 fnd_message.raise_error;
512 end CREATE_EMP_STAT_SITUATION;
513 procedure UPDATE_EMP_STAT_SITUATION
514 (p_validate IN boolean default false
515 ,p_effective_date IN date
516 ,P_EMP_STAT_SITUATION_ID IN NUMBER
517 ,P_STATUTORY_SITUATION_ID IN NUMBER default hr_api.g_number
518 ,P_PERSON_ID IN NUMBER default hr_api.g_number
519 ,P_PROVISIONAL_START_DATE IN DATE default hr_api.g_date
520 ,P_PROVISIONAL_END_DATE IN DATE default hr_api.g_date
521 ,P_ACTUAL_START_DATE IN DATE default hr_api.g_date
522 ,P_ACTUAL_END_DATE IN DATE default hr_api.g_date
523 ,P_APPROVAL_FLAG IN VARCHAR2 default hr_api.g_varchar2
524 ,P_COMMENTS IN VARCHAR2 default hr_api.g_varchar2
525 ,P_CONTACT_PERSON_ID IN NUMBER default hr_api.g_number
526 ,P_CONTACT_RELATIONSHIP IN VARCHAR2 default hr_api.g_varchar2
530 ,P_SECONDED_CAREER_ID IN NUMBER default hr_api.g_number
527 ,P_EXTERNAL_ORGANIZATION_ID IN NUMBER default hr_api.g_number
528 ,P_RENEWAL_FLAG IN VARCHAR2 default hr_api.g_varchar2
529 ,P_RENEW_STAT_SITUATION_ID IN NUMBER default hr_api.g_number
531 ,P_ATTRIBUTE_CATEGORY IN VARCHAR2 default hr_api.g_varchar2
532 ,P_ATTRIBUTE1 IN VARCHAR2 default hr_api.g_varchar2
533 ,P_ATTRIBUTE2 IN VARCHAR2 default hr_api.g_varchar2
534 ,P_ATTRIBUTE3 IN VARCHAR2 default hr_api.g_varchar2
535 ,P_ATTRIBUTE4 IN VARCHAR2 default hr_api.g_varchar2
536 ,P_ATTRIBUTE5 IN VARCHAR2 default hr_api.g_varchar2
537 ,P_ATTRIBUTE6 IN VARCHAR2 default hr_api.g_varchar2
538 ,P_ATTRIBUTE7 IN VARCHAR2 default hr_api.g_varchar2
539 ,P_ATTRIBUTE8 IN VARCHAR2 default hr_api.g_varchar2
540 ,P_ATTRIBUTE9 IN VARCHAR2 default hr_api.g_varchar2
541 ,P_ATTRIBUTE10 IN VARCHAR2 default hr_api.g_varchar2
542 ,P_ATTRIBUTE11 IN VARCHAR2 default hr_api.g_varchar2
543 ,P_ATTRIBUTE12 IN VARCHAR2 default hr_api.g_varchar2
544 ,P_ATTRIBUTE13 IN VARCHAR2 default hr_api.g_varchar2
545 ,P_ATTRIBUTE14 IN VARCHAR2 default hr_api.g_varchar2
546 ,P_ATTRIBUTE15 IN VARCHAR2 default hr_api.g_varchar2
547 ,P_ATTRIBUTE16 IN VARCHAR2 default hr_api.g_varchar2
548 ,P_ATTRIBUTE17 IN VARCHAR2 default hr_api.g_varchar2
549 ,P_ATTRIBUTE18 IN VARCHAR2 default hr_api.g_varchar2
550 ,P_ATTRIBUTE19 IN VARCHAR2 default hr_api.g_varchar2
551 ,P_ATTRIBUTE20 IN VARCHAR2 default hr_api.g_varchar2
552 ,P_ATTRIBUTE21 IN VARCHAR2 default hr_api.g_varchar2
553 ,P_ATTRIBUTE22 IN VARCHAR2 default hr_api.g_varchar2
554 ,P_ATTRIBUTE23 IN VARCHAR2 default hr_api.g_varchar2
555 ,P_ATTRIBUTE24 IN VARCHAR2 default hr_api.g_varchar2
556 ,P_ATTRIBUTE25 IN VARCHAR2 default hr_api.g_varchar2
557 ,P_ATTRIBUTE26 IN VARCHAR2 default hr_api.g_varchar2
558 ,P_ATTRIBUTE27 IN VARCHAR2 default hr_api.g_varchar2
559 ,P_ATTRIBUTE28 IN VARCHAR2 default hr_api.g_varchar2
560 ,P_ATTRIBUTE29 IN VARCHAR2 default hr_api.g_varchar2
561 ,P_ATTRIBUTE30 IN VARCHAR2 default hr_api.g_varchar2
562 ,P_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER
563 ) IS
564 l_emp_stat_situation_id NUMBER(15);
565 l_object_version_number NUMBER(9);
566 l_orig_object_version_number NUMBER(9);
567 CURSOR csr_situation_approved(p_emp_stat_situation_id IN NUMBER) IS
568 SELECT NVL(approval_flag, 'N')
569 FROM pqh_fr_emp_stat_situations
570 WHERE emp_stat_situation_id = p_emp_stat_situation_id;
571 l_sit_approval varchar2(10) ;
572 l_proc varchar2(72) := g_package||'update_emp_stat_situation';
573 l_actual_start_date DATE;
574 begin
575 hr_utility.set_location('Entering:'|| l_proc, 10);
576 l_emp_stat_situation_id := p_emp_stat_situation_id;
577 l_object_version_number := p_object_version_number;
578 l_orig_object_version_number := p_object_version_number;
579 savepoint update_emp_stat_situation;
580 --
581 -- Validation in addition to Row Handlers
582 --
583 If (P_APPROVAL_FLAG = 'Y') Then
584 l_actual_start_date := p_provisional_start_date;
585 Else
586 l_actual_start_date := P_ACTUAL_START_DATE;
587 End if;
588 -- Processing Logic
589 OPEN csr_situation_approved(p_emp_stat_situation_id);
590 FETCH csr_situation_approved INTO l_sit_approval;
591 CLOSE csr_situation_approved;
592 pqh_psu_upd.upd(
593 P_EFFECTIVE_DATE => p_effective_date
594 ,P_STATUTORY_SITUATION_ID => p_statutory_situation_id
595 ,P_PERSON_ID => p_person_id
596 ,P_PROVISIONAL_START_DATE => p_provisional_start_date
597 ,P_PROVISIONAL_END_DATE => p_provisional_end_date
598 ,P_ACTUAL_START_DATE => l_actual_start_date
599 ,P_ACTUAL_END_DATE => p_actual_end_date
600 ,P_APPROVAL_FLAG => p_approval_flag
601 ,P_COMMENTS => p_comments
602 ,P_CONTACT_PERSON_ID => p_contact_person_id
603 ,P_CONTACT_RELATIONSHIP => p_contact_relationship
604 ,P_EXTERNAL_ORGANIZATION_ID => p_external_organization_id
605 ,P_RENEWAL_FLAG => p_renewal_flag
606 ,P_RENEW_STAT_SITUATION_ID => p_renew_stat_situation_id
607 ,P_SECONDED_CAREER_ID => p_seconded_career_id
608 ,P_ATTRIBUTE_CATEGORY => p_attribute_category
609 ,P_ATTRIBUTE1 => p_attribute1
610 ,P_ATTRIBUTE2 => p_attribute2
611 ,P_ATTRIBUTE3 => p_attribute3
612 ,P_ATTRIBUTE4 => p_attribute4
613 ,P_ATTRIBUTE5 => p_attribute5
614 ,P_ATTRIBUTE6 => p_attribute6
615 ,P_ATTRIBUTE7 => p_attribute7
616 ,P_ATTRIBUTE8 => p_attribute8
617 ,P_ATTRIBUTE9 => p_attribute9
618 ,P_ATTRIBUTE10 => p_attribute10
619 ,P_ATTRIBUTE11 => p_attribute11
620 ,P_ATTRIBUTE12 => p_attribute12
621 ,P_ATTRIBUTE13 => p_attribute13
622 ,P_ATTRIBUTE14 => p_attribute14
623 ,P_ATTRIBUTE15 => p_attribute15
627 ,P_ATTRIBUTE19 => p_attribute19
624 ,P_ATTRIBUTE16 => p_attribute16
625 ,P_ATTRIBUTE17 => p_attribute17
626 ,P_ATTRIBUTE18 => p_attribute18
628 ,P_ATTRIBUTE20 => p_attribute20
629 ,P_ATTRIBUTE21 => p_attribute21
630 ,P_ATTRIBUTE22 => p_attribute22
631 ,P_ATTRIBUTE23 => p_attribute23
632 ,P_ATTRIBUTE24 => p_attribute24
633 ,P_ATTRIBUTE25 => p_attribute25
634 ,P_ATTRIBUTE26 => p_attribute26
635 ,P_ATTRIBUTE27 => p_attribute27
636 ,P_ATTRIBUTE28 => p_attribute28
637 ,P_ATTRIBUTE29 => p_attribute29
638 ,P_ATTRIBUTE30 => p_attribute30
639 ,P_EMP_STAT_SITUATION_ID => l_emp_stat_situation_id
640 ,P_OBJECT_VERSION_NUMBER => l_object_version_number );
641 --
642 --
643 /* commented by deenath
644 IF p_approval_flag = 'Y' and NVL(l_sit_approval,'N') = 'N' THEN
645 update_assignments(p_person_id => p_person_id
646 ,p_statutory_situation_id => p_statutory_situation_id
647 ,p_start_date => NVL(p_actual_start_date,p_provisional_start_date)
648 ,p_end_date => NVL(p_actual_end_date,p_provisional_end_date) );
649 END IF;
650 */
651 --
652 -- Set all output arguments
653 --
654 IF p_validate = TRUE THEN
655 raise hr_api.validate_enabled;
656 END IF;
657 p_object_version_number := l_object_version_number;
658 hr_utility.set_location(' Leaving:'||l_proc, 40);
659 EXCEPTION
660 when hr_api.validate_enabled then
661 --
662 -- As the Validate_Enabled exception has been raised
663 -- we must rollback to the savepoint
664 --
665 hr_utility.set_location(' Leaving:'||l_proc, 41);
666 ROLLBACK TO update_emp_stat_situation;
667 p_object_version_number := l_orig_object_version_number;
668 When Others THEN
669 Rollback to update_emp_stat_situation;
670 p_object_version_number := l_orig_object_version_number;
671 hr_utility.set_location(' Leaving:'||l_proc, 42 );
672 fnd_message.raise_error;
673 END UPDATE_EMP_STAT_SITUATION;
674 Procedure DELETE_EMP_STAT_SITUATION
675 ( P_VALIDATE IN BOOLEAN DEFAULT FALSE
676 ,P_EMP_STAT_SITUATION_ID IN NUMBER
677 ,P_OBJECT_VERSION_NUMBER IN NUMBER) IS
678 l_proc varchar2(72) := g_package||'delete_emp_stat_situation';
679 BEGIN
680 hr_utility.set_location('Entering:'|| l_proc, 10);
681 savepoint delete_emp_stat_situation;
682 pqh_psu_del.del(p_emp_stat_situation_id => p_emp_stat_situation_id,
683 p_object_version_number => p_object_version_number);
684 IF p_validate = TRUE THEN
685 raise hr_api.validate_enabled;
686 END IF;
687 hr_utility.set_location(' Leaving:'||l_proc, 40);
688 EXCEPTION
689 when hr_api.validate_enabled then
690 --
691 -- As the Validate_Enabled exception has been raised
692 -- we must rollback to the savepoint
693 --
694 hr_utility.set_location(' Leaving:'||l_proc, 41);
695 ROLLBACK TO delete_emp_stat_situation;
696 When Others THEN
697 Rollback to delete_emp_stat_situation;
698 hr_utility.set_location(' Leaving:'||l_proc, 42);
699 fnd_message.raise_error;
700 END DELETE_EMP_STAT_SITUATION;
701 --
702 --
703 procedure renew_emp_stat_situation
704 ( P_VALIDATE IN BOOLEAN DEFAULT FALSE
705 ,P_EMP_STAT_SITUATION_ID IN OUT NOCOPY NUMBER
706 ,P_RENEW_STAT_SITUATION_ID IN NUMBER
707 ,P_RENEWAL_DURATION IN NUMBER
708 ,P_DURATION_UNITS IN VARCHAR2
709 ,P_APPROVAL_FLAG IN VARCHAR2
710 ,P_COMMENTS IN VARCHAR2
711 ,P_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER) IS
712 l_proc varchar2(72) := g_package||'renew_emp_stat_situation';
713 CURSOR Csr_current_situation_dtls (p_emp_stat_situation_id IN NUMBER) IS
714 SELECT *
715 FROM pqh_fr_emp_stat_situations
716 WHERE emp_stat_situation_id = p_emp_stat_situation_id;
717 lr_currec csr_current_situation_dtls%ROWTYPE;
718 l_new_prov_end_date DATE;
719 l_upd_sit_ovn NUMBER(9);
720 l_new_sit_id NUMBER(15);
721 l_new_sit_ovn NUMBER(9);
722 BEGIN
723 hr_utility.set_location('Entering:'|| l_proc, 10);
724 savepoint renew_emp_stat_situation;
725 OPEN csr_current_situation_dtls(p_emp_stat_situation_id);
726 FETCH csr_current_situation_dtls INTO lr_currec;
727 CLOSE csr_current_situation_dtls;
728 IF p_duration_units = 'D' THEN
729 l_new_prov_end_date := lr_currec.provisional_end_date+p_renewal_duration;
730 ELSIF p_duration_units = 'W' THEN
731 l_new_prov_end_date := lr_currec.provisional_end_date+p_renewal_duration*7;
732 ELSIF p_duration_units = 'M' THEN
733 l_new_prov_end_date := add_months(lr_currec.provisional_end_date,p_renewal_duration);
734 ELSIF p_duration_units = 'Y' THEN
735 l_new_prov_end_date := add_months(lr_currec.provisional_end_date,p_renewal_duration*12);
736 ELSE
737 fnd_message.set_name('PQH','FR_PQH_STS_INVALID_UNITS');
738 hr_multi_message.add(p_associated_column1=> 'P_DURATION_UNITS');
739 END IF;
740 l_upd_sit_ovn := lr_currec.object_version_number;
741 --Extend the civil servant situation by the said duration and create a new renewal situation record
742 pqh_fr_emp_stat_situation_api.update_emp_stat_situation(p_emp_stat_situation_id => p_emp_stat_situation_id
743 ,p_effective_date => lr_currec.provisional_end_date
744 ,p_object_version_number => l_upd_sit_ovn
748 ,p_statutory_situation_id => lr_currec.statutory_situation_id
745 ,p_provisional_end_date => l_new_prov_end_date);
746 pqh_fr_emp_stat_situation_api.create_emp_stat_situation( p_effective_date => trunc(lr_currec.provisional_end_date+1)
747 ,p_emp_stat_situation_id => l_new_sit_id
749 ,p_person_id => lr_currec.person_id
750 ,p_provisional_start_date => trunc(lr_currec.provisional_end_date+1)
751 ,p_provisional_end_date => l_new_prov_end_date
752 ,p_approval_flag => p_approval_flag
753 ,p_comments => p_comments
754 ,p_renewal_flag => 'Y'
755 ,p_renew_stat_situation_id => lr_currec.emp_stat_situation_id
756 ,p_object_version_number => l_new_sit_ovn);
757 IF p_validate = TRUE THEN
758 raise hr_api.validate_enabled;
759 END IF;
760 hr_utility.set_location(' Leaving:'||l_proc, 40);
761 EXCEPTION
762 when hr_api.validate_enabled then
763 --
764 -- As the Validate_Enabled exception has been raised
765 -- we must rollback to the savepoint
766 --
767 hr_utility.set_location(' Leaving:'||l_proc, 41);
768 ROLLBACK TO renew_emp_stat_situation;
769 When Others THEN
770 Rollback to renew_emp_stat_situation;
771 hr_utility.set_location(' Leaving:'||l_proc, 42);
772 fnd_message.raise_error;
773 end renew_emp_stat_situation;
774 procedure reinstate_emp_stat_situation
775 ( P_VALIDATE IN BOOLEAN DEFAULT FALSE
776 ,P_PERSON_ID IN NUMBER
777 ,P_EMP_STAT_SITUATION_ID IN NUMBER
778 ,P_REINSTATE_DATE IN DATE
779 ,P_COMMENTS IN VARCHAR2
780 ,P_NEW_EMP_STAT_SITUATION_ID OUT NOCOPY NUMBER) IS
781 l_proc varchar2(72) := g_package||'reinstate_emp_stat_situation';
782 l_reinstate_sit_id NUMBER(15);
783 l_new_sit_id NUMBER(15);
784 l_new_sit_ovn NUMBER(9);
785 l_upd_sit_ovn NUMBER(9);
786 CURSOR Csr_current_situation_dtls (p_emp_stat_situation_id IN NUMBER) IS
787 SELECT *
788 FROM pqh_fr_emp_stat_situations
789 WHERE emp_stat_situation_id = p_emp_stat_situation_id;
790 lr_currec csr_current_situation_dtls%ROWTYPE;
791 CURSOR Csr_prov_end_date (p_emp_stat_situation_id IN NUMBER, p_person_id IN NUMBER, p_eff_date IN DATE) IS
792 SELECT max(NVL(actual_start_date,provisional_start_date))
793 FROM pqh_fr_emp_stat_situations
794 WHERE emp_stat_situation_id <> p_emp_stat_situation_id
795 AND person_Id = p_person_id
796 AND p_eff_date BETWEEN provisional_start_date and provisional_end_date;
797 l_next_sit_end_dt DATE;
798 BEGIN
799 hr_utility.set_location('Entering:'|| l_proc, 10);
800 savepoint reinstate_emp_stat_situation;
801 --Modified by deenath. Changed parameters 'IN' and 'IN_N' to 'IA' and 'IA_N' resp.
802 l_reinstate_sit_id:= pqh_fr_stat_sit_util.get_dflt_situation(p_business_group_id => hr_general.get_business_group_id,
803 p_effective_date => p_reinstate_date,
804 p_situation_type => 'IA',
805 p_sub_type => 'IA_N');
806 IF l_reinstate_sit_id = -1 THEN
807 fnd_message.set_name('PQH','FR_PQH_STS_NO_DFLT_SIT');
808 fnd_message.raise_error;
809 END IF;
810 OPEN csr_current_situation_dtls(p_emp_stat_situation_id);
811 FETCH csr_current_situation_dtls INTO lr_currec;
812 IF csr_current_situation_dtls%NOTFOUND THEN
813 CLOSE csr_current_situation_dtls;
814 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
815 fnd_message.raise_error;
816 END IF;
817 CLOSE csr_current_situation_dtls;
818 l_upd_sit_ovn := lr_currec.object_version_number;
819 --End Date the current situation and create a new situation with default situation for the employee.
820 pqh_fr_emp_stat_situation_api.update_emp_stat_situation(p_emp_stat_situation_id => p_emp_stat_situation_id
821 ,p_effective_date => trunc(p_reinstate_date)-1
822 ,p_object_version_number => l_upd_sit_ovn
823 ,p_actual_end_date => trunc(p_reinstate_date)-1);
824 OPEN csr_prov_end_date(p_emp_stat_situation_id,p_person_id,p_reinstate_date);
825 FETCH csr_prov_end_date INTO l_next_sit_end_dt;
826 CLOSE csr_prov_end_date;
827 pqh_fr_emp_stat_situation_api.create_emp_stat_situation( p_effective_date => trunc(p_reinstate_date)
828 ,p_emp_stat_situation_id => l_new_sit_id
829 ,p_statutory_situation_id => l_reinstate_sit_id
830 ,p_person_id => lr_currec.person_id
831 ,p_provisional_start_date => trunc(p_reinstate_date)
832 ,p_provisional_end_date => NVL(l_next_sit_end_dt,hr_general.end_of_time)
833 ,p_actual_start_date => trunc(p_reinstate_date)
834 ,p_approval_flag => 'Y'
835 ,p_comments => p_comments
836 ,p_object_version_number => l_new_sit_ovn);
837 IF p_validate = TRUE THEN
838 raise hr_api.validate_enabled;
839 END IF;
840 hr_utility.set_location(' Leaving:'||l_proc, 40);
841 EXCEPTION
842 when hr_api.validate_enabled then
843 --
844 -- As the Validate_Enabled exception has been raised
845 -- we must rollback to the savepoint
846 --
847 hr_utility.set_location(' Leaving:'||l_proc, 41);
848 ROLLBACK TO reinstate_emp_stat_situation;
849 When Others THEN
850 Rollback to reinstate_emp_stat_situation;
851 hr_utility.set_location(' Leaving:'||l_proc, 42);
852 fnd_message.raise_error;
853 END REINSTATE_EMP_STAT_SITUATION;
854 --
855 end PQH_FR_EMP_STAT_SITUATION_API;