[Home] [Help]
PACKAGE BODY: APPS.PQH_FR_EMP_STAT_SIT_UTILITY
Source
1 PACKAGE BODY PQH_FR_EMP_STAT_SIT_UTILITY AS
2 /* $Header: pqfresut.pkb 120.0 2005/05/29 01:53:03 appldev noship $ */
3 --
4 --Package variables
5 g_package VARCHAR2(33) := 'PQH_FR_EMP_STAT_SIT_UTILITY.';
6 --
7 -- ---------------------------------------------------------------------------
8 -- ----------------------< create_emp_stat_situation >------------------------
9 -- ---------------------------------------------------------------------------
10 PROCEDURE create_emp_stat_situation
11 (p_validate IN NUMBER DEFAULT HR_API.g_false_num
12 ,p_effective_date IN DATE
13 ,p_statutory_situation_id IN NUMBER
14 ,p_person_id IN NUMBER
15 ,p_provisional_start_date IN DATE
16 ,p_provisional_end_date IN DATE
17 ,p_actual_start_date IN DATE DEFAULT NULL
18 ,p_actual_end_date IN DATE DEFAULT NULL
19 ,p_approval_flag IN VARCHAR2 DEFAULT NULL
20 ,p_comments IN VARCHAR2 DEFAULT NULL
21 ,p_contact_person_id IN NUMBER DEFAULT NULL
22 ,p_contact_relationship IN VARCHAR2 DEFAULT NULL
23 ,p_external_organization_id IN NUMBER DEFAULT NULL
24 ,p_renewal_flag IN VARCHAR2 DEFAULT NULL
25 ,p_renew_stat_situation_id IN NUMBER DEFAULT NULL
26 ,p_seconded_career_id IN NUMBER DEFAULT NULL
27 ,p_attribute_category IN VARCHAR2 DEFAULT NULL
28 ,p_attribute1 IN VARCHAR2 DEFAULT NULL
29 ,p_attribute2 IN VARCHAR2 DEFAULT NULL
30 ,p_attribute3 IN VARCHAR2 DEFAULT NULL
31 ,p_attribute4 IN VARCHAR2 DEFAULT NULL
32 ,p_attribute5 IN VARCHAR2 DEFAULT NULL
33 ,p_attribute6 IN VARCHAR2 DEFAULT NULL
34 ,p_attribute7 IN VARCHAR2 DEFAULT NULL
35 ,p_attribute8 IN VARCHAR2 DEFAULT NULL
36 ,p_attribute9 IN VARCHAR2 DEFAULT NULL
37 ,p_attribute10 IN VARCHAR2 DEFAULT NULL
38 ,p_attribute11 IN VARCHAR2 DEFAULT NULL
39 ,p_attribute12 IN VARCHAR2 DEFAULT NULL
40 ,p_attribute13 IN VARCHAR2 DEFAULT NULL
41 ,p_attribute14 IN VARCHAR2 DEFAULT NULL
42 ,p_attribute15 IN VARCHAR2 DEFAULT NULL
43 ,p_attribute16 IN VARCHAR2 DEFAULT NULL
44 ,p_attribute17 IN VARCHAR2 DEFAULT NULL
45 ,p_attribute18 IN VARCHAR2 DEFAULT NULL
46 ,p_attribute19 IN VARCHAR2 DEFAULT NULL
47 ,p_attribute20 IN VARCHAR2 DEFAULT NULL
48 ,p_attribute21 IN VARCHAR2 DEFAULT NULL
49 ,p_attribute22 IN VARCHAR2 DEFAULT NULL
50 ,p_attribute23 IN VARCHAR2 DEFAULT NULL
51 ,p_attribute24 IN VARCHAR2 DEFAULT NULL
52 ,p_attribute25 IN VARCHAR2 DEFAULT NULL
53 ,p_attribute26 IN VARCHAR2 DEFAULT NULL
54 ,p_attribute27 IN VARCHAR2 DEFAULT NULL
55 ,p_attribute28 IN VARCHAR2 DEFAULT NULL
56 ,p_attribute29 IN VARCHAR2 DEFAULT NULL
57 ,p_attribute30 IN VARCHAR2 DEFAULT NULL
58 ,p_emp_stat_situation_id OUT nocopy NUMBER
59 ,p_object_version_number OUT nocopy NUMBER
60 ,p_return_status OUT nocopy VARCHAR2
61 )
62 IS
63 --
64 --Cursor for Overlapping In Activity Normal Default Situation
65 CURSOR csr_get_inactivity(p_provisional_start DATE,
66 p_provisional_end DATE,
67 p_actual_start DATE,
68 p_actual_end DATE,
69 p_person_id NUMBER) IS
70 SELECT emp_stat_situation_id, statutory_situation_id, object_version_number,
71 actual_start_date, provisional_start_date,
72 actual_end_date, NVL(provisional_end_date,HR_GENERAL.end_of_time)
73 FROM pqh_fr_emp_stat_situations
74 WHERE person_id = p_person_id
75 AND statutory_situation_id IN (SELECT statutory_situation_id
76 FROM pqh_fr_stat_situations_v sit
77 ,per_shared_types_vl sh
78 WHERE sh.shared_type_id = type_of_ps
79 AND sh.system_type_cd = NVL(PQH_FR_UTILITY.get_bg_type_of_ps,sh.system_type_cd)
80 AND sit.business_group_id = HR_GENERAL.get_business_group_id
81 AND sit.default_flag = 'Y'
82 AND sit.situation_type = 'IA'
83 AND sit.sub_type = 'IA_N'
84 AND TRUNC(NVL(p_actual_start,p_provisional_start)) BETWEEN
85 sit.date_from AND NVL(sit.date_to,HR_GENERAL.end_of_time))
86 AND(TRUNC(NVL(actual_start_date,provisional_start_date)) <= TRUNC(NVL(p_actual_start,p_provisional_start))
87 AND TRUNC(NVL(actual_end_date,NVL(provisional_end_date,HR_GENERAL.end_of_time)))
88 >= TRUNC(NVL(p_actual_end,p_provisional_end)));
89 --
90 --Cursor for Overlapping non In Activity Normal Default Situations
91 CURSOR csr_overlaps(p_provisional_start DATE,
92 p_provisional_end DATE,
93 p_actual_start DATE,
94 p_actual_end DATE,
95 p_person_id NUMBER,
96 p_emp_stat_situation_id NUMBER,
97 p_iand_stat_sit_id NUMBER) IS
98 SELECT 'x'
99 FROM DUAL
100 WHERE EXISTS(SELECT 'x'
101 FROM pqh_fr_emp_stat_situations
102 WHERE person_id = p_person_id
103 -- AND emp_stat_situation_id <> NVL(p_emp_stat_situation_id,-1)
104 AND statutory_situation_id <> p_iand_stat_sit_id
105 AND(NVL(p_actual_start,p_provisional_start)
106 BETWEEN NVL(actual_start_date,provisional_start_date)
107 AND NVL(actual_end_date,NVL(provisional_end_date,HR_GENERAL.end_of_time))
108 OR NVL(p_actual_end,p_provisional_end)
109 BETWEEN NVL(actual_start_date,provisional_start_date)
110 AND NVL(actual_end_date,NVL(provisional_end_date,HR_GENERAL.end_of_time))));
111 --
112 --Variable Declaration
113 l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
114 l_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
115 l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
116 l_iand_act_start_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
117 l_iand_prv_start_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
118 l_iand_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
119 l_iand_prv_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
120 l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
121 l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
122 l_max_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
123 l_max_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
124 l_max_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
125 l_max_iand_act_start_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
126 l_actual_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
127 l_approval_flag PQH_FR_EMP_STAT_SITUATIONS.approval_flag%TYPE;
128 l_valid VARCHAR2(01) := NULL;
129 l_proc VARCHAR2(72) := g_package||'create_emp_stat_situation';
130 l_validate BOOLEAN;
131 l_rul_sit_return_st varchar2(2) ;
132 --
133 BEGIN
134 --
135 --Log entry
136 HR_UTILITY.set_location(' Entering: '||l_proc, 10);
137 --
138 --Issue savepoint
139 SAVEPOINT pre_state;
140 --
141 --Initialise Multiple Message Detection
142 HR_MULTI_MESSAGE.enable_message_list;
143 --Convert constant values to their corresponding boolean value
144 l_validate := HR_API.constant_to_boolean(p_constant_value => p_validate);
145 --
146 --Check whether In Activity Normal Default Situation exists during the timeframe.
147 OPEN csr_get_inactivity(p_provisional_start_date,p_provisional_end_date,
148 p_actual_start_date,p_actual_end_date,p_person_id);
149 FETCH csr_get_inactivity INTO l_iand_emp_stat_sit_id,l_iand_stat_sit_id,l_iand_ovn,
150 l_iand_act_start_dt,l_iand_prv_start_dt,
151 l_iand_act_end_dt,l_iand_prv_end_dt;
152 IF csr_get_inactivity%NOTFOUND THEN
153 CLOSE csr_get_inactivity;
154 FND_MESSAGE.set_name('PQH','FR_PQH_STS_NO_IAND_SIT'); --In Activity Normal Default sitution does not exist for the specified duration. A Situation can only be created when an In Activity Normal Default situation exists in the duration.
155 HR_MULTI_MESSAGE.ADD;
156 RAISE HR_MULTI_MESSAGE.error_message_exist;
157 END IF;
158 IF csr_get_inactivity%ISOPEN THEN
159 CLOSE csr_get_inactivity;
160 END IF;
161 --
162 --Check for Overlaps against Non In Activity Normal Default Situations.
163 OPEN csr_overlaps(p_provisional_start_date,p_provisional_end_date,
164 p_actual_start_date,p_actual_end_date,
165 p_person_id,p_emp_stat_situation_id,l_iand_stat_sit_id);
166 FETCH csr_overlaps INTO l_valid;
167 IF csr_overlaps%FOUND THEN
168 CLOSE csr_overlaps;
169 FND_MESSAGE.set_name('PQH','FR_PQH_STAT_SIT_OVERLAP_DATES'); --The Start and/or End Date for this situation overlaps with other situation. Please enter non overlapping dates.
170 HR_MULTI_MESSAGE.ADD;
171 RAISE HR_MULTI_MESSAGE.error_message_exist;
172 END IF;
173 IF csr_overlaps%ISOPEN THEN
174 CLOSE csr_overlaps;
175 END IF;
176 --
177 -- Checking whether user satisfies the eligibility criteria for going to a situation
178 HR_UTILITY.set_location('Checking the eligibility criteria ', 40);
179 l_rul_sit_return_st := pqh_sit_engine.is_situation_valid(p_person_id,p_provisional_start_date,p_statutory_situation_id);
180 HR_UTILITY.set_location('Return status for the eligibility criteria '||l_rul_sit_return_st, 40);
181 IF l_rul_sit_return_st = 'N' then
182 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
183 RAISE HR_MULTI_MESSAGE.error_message_exist;
184 END IF;
185 END IF;
186
187 --
188 --End date IAND situation (IAND = In Activity Normal Default)
189 pqh_fr_emp_stat_situation_api.update_emp_stat_situation
190 (p_effective_date => p_effective_date
191 ,p_emp_stat_situation_id => l_iand_emp_stat_sit_id --Update this IAND rec
192 ,p_statutory_situation_id => l_iand_stat_sit_id
193 ,p_provisional_end_date => TRUNC(NVL(p_actual_start_date,p_provisional_start_date)-1)
194 ,p_actual_end_date => TRUNC(NVL(p_actual_start_date,p_provisional_start_date)-1)
195 ,p_approval_flag => 'Y'
196 ,p_object_version_number => l_iand_ovn); --OVN for the IAND rec
197 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
198 RAISE HR_MULTI_MESSAGE.error_message_exist;
199 END IF;
200 --
201 pqh_psu_ins.set_base_key_value(p_emp_stat_situation_id => p_emp_stat_situation_id);
202 l_actual_end_date := p_actual_end_date;
203 --
204 --If Situation is Past Situation then set its actual end date to prov end date.
205 IF NVL(p_actual_end_date,p_provisional_end_date) < TRUNC(SYSDATE) THEN
206 IF p_actual_end_date IS NULL THEN
207 l_actual_end_date := p_provisional_end_date;
208 END IF;
209 END IF;
210 --
211 --Create the new Situation
212 pqh_fr_emp_stat_situation_api.create_emp_stat_situation
213 (p_validate => l_validate
214 ,p_effective_date => p_effective_date
215 ,p_statutory_situation_id => p_statutory_situation_id
216 ,p_person_id => p_person_id
217 ,p_provisional_start_date => p_provisional_start_date
218 ,p_provisional_end_date => p_provisional_end_date
219 ,p_actual_start_date => p_actual_start_date
220 ,p_actual_end_date => l_actual_end_date
221 ,p_approval_flag => p_approval_flag
222 ,p_comments => p_comments
223 ,p_contact_person_id => p_contact_person_id
224 ,p_contact_relationship => p_contact_relationship
225 ,p_external_organization_id => p_external_organization_id
226 ,p_renewal_flag => p_renewal_flag
227 ,p_renew_stat_situation_id => p_renew_stat_situation_id
228 ,p_seconded_career_id => p_seconded_career_id
229 ,p_attribute_category => p_attribute_category
230 ,p_attribute1 => p_attribute1
231 ,p_attribute2 => p_attribute2
232 ,p_attribute3 => p_attribute3
233 ,p_attribute4 => p_attribute4
234 ,p_attribute5 => p_attribute5
235 ,p_attribute6 => p_attribute6
236 ,p_attribute7 => p_attribute7
237 ,p_attribute8 => p_attribute8
238 ,p_attribute9 => p_attribute9
239 ,p_attribute10 => p_attribute10
240 ,p_attribute11 => p_attribute11
241 ,p_attribute12 => p_attribute12
242 ,p_attribute13 => p_attribute13
243 ,p_attribute14 => p_attribute14
244 ,p_attribute15 => p_attribute15
245 ,p_attribute16 => p_attribute16
246 ,p_attribute17 => p_attribute17
247 ,p_attribute18 => p_attribute18
248 ,p_attribute19 => p_attribute19
249 ,p_attribute20 => p_attribute20
250 ,p_attribute21 => p_attribute21
251 ,p_attribute22 => p_attribute22
252 ,p_attribute23 => p_attribute23
253 ,p_attribute24 => p_attribute24
254 ,p_attribute25 => p_attribute25
255 ,p_attribute26 => p_attribute26
256 ,p_attribute27 => p_attribute27
257 ,p_attribute28 => p_attribute28
258 ,p_attribute29 => p_attribute29
259 ,p_attribute30 => p_attribute30
260 ,p_emp_stat_situation_id => p_emp_stat_situation_id
261 ,p_object_version_number => p_object_version_number);
262 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
263 RAISE HR_MULTI_MESSAGE.error_message_exist;
264 END IF;
265 --
266 --Create IAND with start date as (end date+1) of new Situation and end date as current IAND end date
267 pqh_fr_emp_stat_situation_api.create_emp_stat_situation
268 (p_effective_date => p_effective_date
269 ,p_emp_stat_situation_id => l_new_iand_emp_stat_sit_id
270 ,p_statutory_situation_id => l_iand_stat_sit_id
271 ,p_person_id => p_person_id
272 ,p_provisional_start_date => TRUNC(NVL(p_actual_end_date,p_provisional_end_date)+1)
273 ,p_provisional_end_date => NVL(l_iand_prv_end_dt,hr_general.end_of_time)
274 ,p_actual_start_date => TRUNC(NVL(p_actual_end_date,p_provisional_end_date)+1)
275 ,p_actual_end_date => NVL(l_iand_act_end_dt,hr_general.end_of_time)
276 ,p_approval_flag => 'Y'
277 ,p_comments => p_comments
278 ,p_object_version_number => l_new_sit_ovn);
279 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
280 RAISE HR_MULTI_MESSAGE.error_message_exist;
281 END IF;
282 --
283 --Update Assignment to reflect the Create.
284 updt_assign(p_person_id
285 ,p_statutory_situation_id
286 ,l_iand_stat_sit_id
287 ,TRUNC(p_provisional_start_date)
288 ,TRUNC(p_provisional_end_date));
289 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
290 RAISE HR_MULTI_MESSAGE.error_message_exist;
291 END IF;
292 --
293 p_return_status := HR_MULTI_MESSAGE.get_return_status_disable;
294 HR_UTILITY.set_location(' Leaving: '||l_proc, 20);
295 --
296 EXCEPTION
297 WHEN HR_MULTI_MESSAGE.error_message_exist THEN
298 --Catch Multiple Message List exception
299 ROLLBACK TO pre_state;
300 --Reset IN OUT parameters and set OUT parameters
301 p_emp_stat_situation_id := NULL;
302 p_object_version_number := NULL;
303 p_return_status := HR_MULTI_MESSAGE.get_return_status_disable;
304 HR_UTILITY.set_location(' Leaving: '||l_proc, 30);
305 WHEN others THEN
306 --When Multiple Message Detection is enabled catch any Application specific or other unexpected exceptions.
307 --Adding appropriate details to Multiple Message List. Otherwise re-raise the error.
308 ROLLBACK TO pre_state;
309 IF HR_MULTI_MESSAGE.unexpected_error_add(l_proc) THEN
310 HR_UTILITY.set_location(' Leaving: '||l_proc, 40);
311 RAISE;
312 END IF;
313 --Reset IN OUT and set OUT parameters
314 p_emp_stat_situation_id := NULL;
315 p_object_version_number := NULL;
316 p_return_status := HR_MULTI_MESSAGE.get_return_status_disable;
317 HR_UTILITY.set_location(' Leaving: '||l_proc, 50);
318 END create_emp_stat_situation;
319 --
320 -- ---------------------------------------------------------------------------
321 -- ----------------------< update_emp_stat_situation >------------------------
322 -- ---------------------------------------------------------------------------
323 PROCEDURE update_emp_stat_situation
324 (p_validate IN NUMBER DEFAULT HR_API.g_false_num
325 ,p_effective_date IN DATE
326 ,p_emp_stat_situation_id IN NUMBER
327 ,p_statutory_situation_id IN NUMBER DEFAULT HR_API.g_number
328 ,p_person_id IN NUMBER DEFAULT HR_API.g_number
329 ,p_provisional_start_date IN DATE DEFAULT HR_API.g_date
330 ,p_provisional_end_date IN DATE DEFAULT HR_API.g_date
331 ,p_actual_start_date IN DATE DEFAULT HR_API.g_date
332 ,p_actual_end_date IN DATE DEFAULT HR_API.g_date
333 ,p_approval_flag IN VARCHAR2 DEFAULT HR_API.g_varchar2
334 ,p_comments IN VARCHAR2 DEFAULT HR_API.g_varchar2
335 ,p_contact_person_id IN NUMBER DEFAULT HR_API.g_number
336 ,p_contact_relationship IN VARCHAR2 DEFAULT HR_API.g_varchar2
337 ,p_external_organization_id IN NUMBER DEFAULT HR_API.g_number
338 ,p_renewal_flag IN VARCHAR2 DEFAULT HR_API.g_varchar2
339 ,p_renew_stat_situation_id IN NUMBER DEFAULT HR_API.g_number
340 ,p_seconded_career_id IN NUMBER DEFAULT HR_API.g_number
341 ,p_attribute_category IN VARCHAR2 DEFAULT HR_API.g_varchar2
342 ,p_attribute1 IN VARCHAR2 DEFAULT HR_API.g_varchar2
343 ,p_attribute2 IN VARCHAR2 DEFAULT HR_API.g_varchar2
344 ,p_attribute3 IN VARCHAR2 DEFAULT HR_API.g_varchar2
345 ,p_attribute4 IN VARCHAR2 DEFAULT HR_API.g_varchar2
346 ,p_attribute5 IN VARCHAR2 DEFAULT HR_API.g_varchar2
347 ,p_attribute6 IN VARCHAR2 DEFAULT HR_API.g_varchar2
348 ,p_attribute7 IN VARCHAR2 DEFAULT HR_API.g_varchar2
349 ,p_attribute8 IN VARCHAR2 DEFAULT HR_API.g_varchar2
350 ,p_attribute9 IN VARCHAR2 DEFAULT HR_API.g_varchar2
351 ,p_attribute10 IN VARCHAR2 DEFAULT HR_API.g_varchar2
352 ,p_attribute11 IN VARCHAR2 DEFAULT HR_API.g_varchar2
353 ,p_attribute12 IN VARCHAR2 DEFAULT HR_API.g_varchar2
354 ,p_attribute13 IN VARCHAR2 DEFAULT HR_API.g_varchar2
355 ,p_attribute14 IN VARCHAR2 DEFAULT HR_API.g_varchar2
356 ,p_attribute15 IN VARCHAR2 DEFAULT HR_API.g_varchar2
357 ,p_attribute16 IN VARCHAR2 DEFAULT HR_API.g_varchar2
358 ,p_attribute17 IN VARCHAR2 DEFAULT HR_API.g_varchar2
359 ,p_attribute18 IN VARCHAR2 DEFAULT HR_API.g_varchar2
360 ,p_attribute19 IN VARCHAR2 DEFAULT HR_API.g_varchar2
361 ,p_attribute20 IN VARCHAR2 DEFAULT HR_API.g_varchar2
362 ,p_attribute21 IN VARCHAR2 DEFAULT HR_API.g_varchar2
363 ,p_attribute22 IN VARCHAR2 DEFAULT HR_API.g_varchar2
364 ,p_attribute23 IN VARCHAR2 DEFAULT HR_API.g_varchar2
365 ,p_attribute24 IN VARCHAR2 DEFAULT HR_API.g_varchar2
366 ,p_attribute25 IN VARCHAR2 DEFAULT HR_API.g_varchar2
367 ,p_attribute26 IN VARCHAR2 DEFAULT HR_API.g_varchar2
368 ,p_attribute27 IN VARCHAR2 DEFAULT HR_API.g_varchar2
369 ,p_attribute28 IN VARCHAR2 DEFAULT HR_API.g_varchar2
370 ,p_attribute29 IN VARCHAR2 DEFAULT HR_API.g_varchar2
371 ,p_attribute30 IN VARCHAR2 DEFAULT HR_API.g_varchar2
372 ,p_object_version_number IN OUT nocopy NUMBER
373 ,p_return_status OUT nocopy VARCHAR2
374 )
375 IS
376 --
377 --Cursor to get current Situation details
378 CURSOR csr_current_situation_dtls(p_emp_stat_situation_id IN NUMBER) IS
379 SELECT person_id, statutory_situation_id, actual_start_date, provisional_start_date, actual_end_date, provisional_end_date
380 FROM pqh_fr_emp_stat_situations
381 WHERE emp_stat_situation_id = p_emp_stat_situation_id;
382 --
383 --Cursor to fetch prior Situation falling before the Situation to be deleted
384 CURSOR csr_get_prior_sit_dtls(p_person_id IN NUMBER,
385 p_date IN DATE) IS
386 SELECT emp_stat_situation_id, statutory_situation_id, object_version_number,
387 provisional_start_date, actual_start_date, provisional_end_date, actual_end_date
388 FROM pqh_fr_emp_stat_situations
389 WHERE person_id = p_person_id
390 AND TRUNC(NVL(actual_end_date,provisional_end_date)) = TRUNC(p_date-1);
391 --
392 --Cursor to fetch future Situation falling after the Situation to be deleted
393 CURSOR csr_get_next_sit_dtls(p_person_id IN NUMBER,
394 p_date IN DATE) IS
395 SELECT emp_stat_situation_id, statutory_situation_id, object_version_number,
396 provisional_start_date, actual_start_date, provisional_end_date, actual_end_date
397 FROM pqh_fr_emp_stat_situations
398 WHERE person_id = p_person_id
399 AND TRUNC(NVL(actual_start_date,provisional_start_date)) = TRUNC(p_date+1);
400 --
401 --Cursor for Overlapping non In Activity Normal Default Situations
402 CURSOR csr_overlaps(p_provisional_start DATE,
403 p_provisional_end DATE,
404 p_person_id NUMBER,
405 p_emp_stat_situation_id NUMBER,
406 p_iand_stat_sit_id NUMBER) IS
407 SELECT 'x'
408 FROM DUAL
409 WHERE EXISTS(SELECT 'x'
410 FROM pqh_fr_emp_stat_situations
411 WHERE person_id = p_person_id
412 AND emp_stat_situation_id <> NVL(p_emp_stat_situation_id,-1)
413 AND statutory_situation_id <> p_iand_stat_sit_id
414 AND(p_provisional_start BETWEEN NVL(actual_start_date,provisional_start_date)
415 AND NVL(actual_end_date,NVL(provisional_end_date,HR_GENERAL.end_of_time))
416 OR p_provisional_end BETWEEN NVL(actual_start_date,provisional_start_date)
417 AND NVL(actual_end_date,NVL(provisional_end_date,HR_GENERAL.end_of_time))));
418 --
419 --Variable Declaration
420 l_proc VARCHAR2(72) := g_package||'update_emp_stat_situation';
421 l_validate BOOLEAN;
422 l_valid VARCHAR2(01) := NULL;
423 l_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;
424 l_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
425 l_act_st_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
426 l_prov_st_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
427 l_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
428 l_prov_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
429 l_prior_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
430 l_prior_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
431 l_prior_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
432 l_prior_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
433 l_prior_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
434 l_prior_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
435 l_prior_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
436 l_iand_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
437 l_next_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
438 l_next_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
439 l_next_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
440 l_next_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
441 l_next_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
442 l_next_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
443 l_next_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
444 l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
445 l_new_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
446 l_object_version_number PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
447 --
448 BEGIN
449 --
450 --Log entry
451 HR_UTILITY.set_location(' Entering: '||l_proc, 10);
452 --
453 --Issue savepoint
454 SAVEPOINT pre_state;
455 --
456 --Initialise Multiple Message Detection
457 HR_MULTI_MESSAGE.enable_message_list;
458 --
459 --Assign OVN for Update Record to IN OUT variable
460 l_object_version_number := p_object_version_number;
461 --
462 --Convert constant values to their corresponding boolean value
463 l_validate := HR_API.constant_to_boolean(p_constant_value => p_validate);
464 --
465 --Fetch details of Situation to be updated.
466 OPEN csr_current_situation_dtls(p_emp_stat_situation_id);
467 FETCH csr_current_situation_dtls INTO l_person_id,l_sit_id,l_act_st_dt,l_prov_st_dt,l_act_end_dt,l_prov_end_dt;
468 IF csr_current_situation_dtls%NOTFOUND THEN
469 CLOSE csr_current_situation_dtls;
470 FND_MESSAGE.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
471 HR_MULTI_MESSAGE.ADD;
472 RAISE HR_MULTI_MESSAGE.error_message_exist;
473 END IF;
474 CLOSE csr_current_situation_dtls;
475 --
476 --Fetch details for prior Situation ending on current situations start date - 1
477 OPEN csr_get_prior_sit_dtls(l_person_id,TRUNC(NVL(l_act_st_dt,l_prov_st_dt)));
478 FETCH csr_get_prior_sit_dtls INTO l_prior_emp_stat_sit_id, l_prior_sit_id, l_prior_ovn,
479 l_prior_prov_st_date, l_prior_act_st_date,
480 l_prior_prov_end_date, l_prior_act_end_date;
481 IF csr_get_prior_sit_dtls%NOTFOUND THEN
482 CLOSE csr_get_prior_sit_dtls;
483 FND_MESSAGE.set_name('PQH','FR_PQH_PRIOR_SIT_NOT_FND'); --Cannot Update/Delete because no situation exists prior to this situation.
484 HR_MULTI_MESSAGE.ADD;
485 RAISE HR_MULTI_MESSAGE.error_message_exist;
486 END IF;
487 CLOSE csr_get_prior_sit_dtls;
488 --
489 --Fetch details of future Situation starting from current situations end date + 1
490 OPEN csr_get_next_sit_dtls(l_person_id,TRUNC(NVL(l_act_end_dt,l_prov_end_dt)));
491 FETCH csr_get_next_sit_dtls INTO l_next_emp_stat_sit_id, l_next_sit_id, l_next_ovn,
492 l_next_act_st_date,l_next_prov_st_date,
493 l_next_prov_end_date, l_next_act_end_date;
494 IF csr_get_next_sit_dtls%NOTFOUND THEN
495 CLOSE csr_get_next_sit_dtls;
496 FND_MESSAGE.set_name('PQH','FR_PQH_NEXT_SIT_NOT_FND'); --Cannot Update/Delete because no situation exists after this situation.
497 HR_MULTI_MESSAGE.ADD;
498 RAISE HR_MULTI_MESSAGE.error_message_exist;
499 END IF;
500 CLOSE csr_get_next_sit_dtls;
501 --
502 --Get In Activity Normal Default Situation Id
503 l_iand_sit_id:= PQH_FR_STAT_SIT_UTIL.get_dflt_situation
504 (p_business_group_id => HR_GENERAL.get_business_group_id
505 ,p_effective_date => TRUNC(NVL(l_act_st_dt,l_prov_st_dt))
506 ,p_situation_type => 'IA'
507 ,p_sub_type => 'IA_N');
508 IF l_iand_sit_id = -1 THEN
509 FND_MESSAGE.set_name('PQH','FR_PQH_STS_NO_DFLT_SIT'); --In Activity Normal Default Situation does not exist.
510 HR_MULTI_MESSAGE.ADD;
511 RAISE HR_MULTI_MESSAGE.error_message_exist;
512 END IF;
513 --
514 --Check for Overlaps against Non In Activity Normal Default Situations.
515 OPEN csr_overlaps(p_provisional_start_date,p_provisional_end_date,
516 p_person_id,p_emp_stat_situation_id,l_iand_sit_id);
517 FETCH csr_overlaps INTO l_valid;
518 IF csr_overlaps%FOUND THEN
519 CLOSE csr_overlaps;
520 FND_MESSAGE.set_name('PQH','FR_PQH_STAT_SIT_OVERLAP_DATES'); --The Start and/or End Date for this situation overlaps with other situation. Please enter non overlapping dates.
521 HR_MULTI_MESSAGE.ADD;
522 RAISE HR_MULTI_MESSAGE.error_message_exist;
523 END IF;
524 IF csr_overlaps%ISOPEN THEN
525 CLOSE csr_overlaps;
526 END IF;
527 --
528 --If prior Sit is IAND and next Sit is IAND then
529 IF l_prior_sit_id <> l_iand_sit_id OR l_next_sit_id <> l_iand_sit_id THEN
530 FND_MESSAGE.set_name('PQH','FR_PQH_STAT_SIT_RENEW'); --Cannot update because this is a Renewal Situation.
531 HR_MULTI_MESSAGE.ADD;
532 RAISE HR_MULTI_MESSAGE.error_message_exist;
533 END IF;
534 --
535 IF TRUNC(p_provisional_start_date) <> TRUNC(NVL(l_act_st_dt,l_prov_st_dt)) THEN
536 --Call API to update Prior IAND Situations End Date to current situations updated Start Date - 1.
537 pqh_fr_emp_stat_situation_api.update_emp_stat_situation
538 (p_effective_date => TRUNC(NVL(l_prior_act_st_date,l_prior_prov_st_date))
539 ,p_emp_stat_situation_id => l_prior_emp_stat_sit_id --Update this IAND rec
540 ,p_statutory_situation_id => l_prior_sit_id
541 ,p_provisional_end_date => TRUNC(p_provisional_start_date-1)
542 ,p_actual_end_date => TRUNC(p_provisional_start_date-1) --in case it is not approved.
543 ,p_approval_flag => 'Y'
544 ,p_object_version_number => l_prior_ovn);
545 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
546 RAISE HR_MULTI_MESSAGE.error_message_exist;
547 END IF;
548 END IF;
549 --
550 --Call API to update current Situation
551 pqh_fr_emp_stat_situation_api.update_emp_stat_situation
552 (p_validate => l_validate
553 ,p_effective_date => p_effective_date
554 ,p_emp_stat_situation_id => p_emp_stat_situation_id
555 ,p_statutory_situation_id => p_statutory_situation_id
556 ,p_person_id => p_person_id
557 ,p_provisional_start_date => p_provisional_start_date
558 ,p_provisional_end_date => p_provisional_end_date
559 ,p_approval_flag => p_approval_flag
560 ,p_comments => p_comments
561 ,p_contact_person_id => p_contact_person_id
562 ,p_contact_relationship => p_contact_relationship
563 ,p_external_organization_id => p_external_organization_id
564 ,p_renewal_flag => p_renewal_flag
565 ,p_renew_stat_situation_id => p_renew_stat_situation_id
566 ,p_seconded_career_id => p_seconded_career_id
567 ,p_attribute_category => p_attribute_category
568 ,p_attribute1 => p_attribute1
569 ,p_attribute2 => p_attribute2
570 ,p_attribute3 => p_attribute3
571 ,p_attribute4 => p_attribute4
572 ,p_attribute5 => p_attribute5
573 ,p_attribute6 => p_attribute6
574 ,p_attribute7 => p_attribute7
575 ,p_attribute8 => p_attribute8
576 ,p_attribute9 => p_attribute9
577 ,p_attribute10 => p_attribute10
578 ,p_attribute11 => p_attribute11
579 ,p_attribute12 => p_attribute12
580 ,p_attribute13 => p_attribute13
581 ,p_attribute14 => p_attribute14
582 ,p_attribute15 => p_attribute15
583 ,p_attribute16 => p_attribute16
584 ,p_attribute17 => p_attribute17
585 ,p_attribute18 => p_attribute18
586 ,p_attribute19 => p_attribute19
587 ,p_attribute20 => p_attribute20
588 ,p_attribute21 => p_attribute21
589 ,p_attribute22 => p_attribute22
590 ,p_attribute23 => p_attribute23
591 ,p_attribute24 => p_attribute24
592 ,p_attribute25 => p_attribute25
593 ,p_attribute26 => p_attribute26
594 ,p_attribute27 => p_attribute27
595 ,p_attribute28 => p_attribute28
596 ,p_attribute29 => p_attribute29
597 ,p_attribute30 => p_attribute30
598 ,p_object_version_number => p_object_version_number);
599 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
600 RAISE HR_MULTI_MESSAGE.error_message_exist;
601 END IF;
602 --
603 IF TRUNC(p_provisional_end_date) <> TRUNC(NVL(l_act_end_dt,NVL(l_prov_end_dt,HR_GENERAL.end_of_time))) THEN
604 --Call API to update next IAND Situations Start Date to current situations updated End Date + 1.
605 pqh_fr_emp_stat_situation_api.update_emp_stat_situation
606 (p_effective_date => TRUNC(NVL(l_next_act_st_date,l_next_prov_st_date))
607 ,p_emp_stat_situation_id => l_next_emp_stat_sit_id --Update this IAND rec
608 ,p_statutory_situation_id => l_next_sit_id
609 ,p_provisional_start_date => TRUNC(p_provisional_end_date+1)
610 ,p_actual_start_date => TRUNC(p_provisional_end_date+1)
611 ,p_approval_flag => 'Y'
612 ,p_object_version_number => l_next_ovn);
613 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
614 RAISE HR_MULTI_MESSAGE.error_message_exist;
615 END IF;
616 END IF;
617 --
618 --Update Assignment to reflect the Update.
619 updt_assign(l_person_id
620 ,l_sit_id
621 ,l_iand_sit_id
622 ,TRUNC(p_provisional_start_date)
623 ,TRUNC(p_provisional_end_date));
624 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
625 RAISE HR_MULTI_MESSAGE.error_message_exist;
626 END IF;
627 --
628 IF TRUNC(p_provisional_start_date) > TRUNC(NVL(l_act_st_dt,l_prov_st_dt)) THEN
629 --Update Assignment to from original start date till updated start date.
630 updt_assign(l_person_id
631 ,l_iand_sit_id
632 ,l_iand_sit_id
633 ,TRUNC(NVL(l_act_st_dt,l_prov_st_dt))
634 ,TRUNC(p_provisional_start_date-1));
635 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
636 RAISE HR_MULTI_MESSAGE.error_message_exist;
637 END IF;
638 END IF;
639 --
640 IF TRUNC(p_provisional_end_date) < TRUNC(NVL(l_act_end_dt,NVL(l_prov_end_dt,HR_GENERAL.end_of_time))) THEN
641 --Update Assignment to from updated end date till original end date.
642 updt_assign(l_person_id
643 ,l_iand_sit_id
644 ,l_iand_sit_id
645 ,TRUNC(p_provisional_end_date+1)
646 ,TRUNC(NVL(l_act_end_dt,NVL(l_prov_end_dt,HR_GENERAL.end_of_time))));
647 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
648 RAISE HR_MULTI_MESSAGE.error_message_exist;
649 END IF;
650 END IF;
651 --
652 p_return_status := HR_MULTI_MESSAGE.get_return_status_disable;
653 HR_UTILITY.set_location(' Leaving: '||l_proc, 20);
654 --
655 EXCEPTION
656 WHEN HR_MULTI_MESSAGE.error_message_exist THEN
657 --Catch Multiple Message List exception
658 ROLLBACK TO pre_state;
659 --Reset IN OUT parameters and set OUT parameters
660 p_object_version_number := l_object_version_number;
661 p_return_status := HR_MULTI_MESSAGE.get_return_status_disable;
662 HR_UTILITY.set_location(' Leaving: '||l_proc, 30);
663 WHEN others THEN
664 --When Multiple Message Detection is enabled catch any Application specific or other unexpected exceptions.
665 --Adding appropriate details to Multiple Message List. Otherwise re-raise the error.
666 ROLLBACK TO pre_state;
667 IF HR_MULTI_MESSAGE.unexpected_error_add(l_proc) THEN
668 HR_UTILITY.set_location(' Leaving: '||l_proc, 40);
669 RAISE;
670 END IF;
671 --Reset IN OUT and set OUT parameters
672 p_object_version_number := l_object_version_number;
673 p_return_status := HR_MULTI_MESSAGE.get_return_status_disable;
674 HR_UTILITY.set_location(' Leaving: '||l_proc, 50);
675 END update_emp_stat_situation;
676 --
677 -- ---------------------------------------------------------------------------
678 -- --------------------< reinstate_emp_stat_situation >-----------------------
679 -- ---------------------------------------------------------------------------
680 PROCEDURE reinstate_emp_stat_situation
681 (p_validate IN NUMBER DEFAULT HR_API.g_false_num
682 ,p_person_id IN NUMBER
683 ,p_emp_stat_situation_id IN NUMBER
684 ,p_reinstate_date IN DATE
685 ,p_comments IN VARCHAR2
686 ,p_new_emp_stat_situation_id OUT nocopy NUMBER
687 ,p_return_status OUT nocopy VARCHAR2
688 )
689 IS
690 --
691 --Cursor to fetch the current Situation Details.
692 CURSOR csr_current_situation_dtls(p_emp_stat_situation_id IN NUMBER) IS
693 SELECT *
694 FROM pqh_fr_emp_stat_situations
695 WHERE emp_stat_situation_id = p_emp_stat_situation_id;
696 --
697 --Cursor to fetch IAND record falling after the Situation to be reinstated
698 CURSOR csr_get_iand_dtls(p_iand_stat_sit_id IN NUMBER,
699 p_start_date IN DATE) IS
700 SELECT emp_stat_situation_id, provisional_end_date, actual_end_date, object_version_number
701 FROM pqh_fr_emp_stat_situations
702 WHERE person_id = p_person_id
703 AND statutory_situation_id = p_iand_stat_sit_id
704 AND TRUNC(NVL(actual_start_date,provisional_start_date)) = TRUNC(p_start_date+1);
705 --
706 --Variable Declaration
707 l_proc VARCHAR2(72) := g_package||'reinstate_emp_stat_situation';
708 l_validate BOOLEAN;
709 l_object_version_number PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
710 lr_currec csr_current_situation_dtls%ROWTYPE;
711 l_reinstate_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
712 l_upd_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
713 l_new_emp_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
714 l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
715 l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
716 l_iand_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
717 l_iand_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
718 l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
719 --
720 BEGIN
721 --
722 --Log entry
723 HR_UTILITY.set_location(' Entering: '||l_proc, 10);
724 --
725 --Issue savepoint
726 SAVEPOINT pre_state;
727 --
728 --Initialise Multiple Message Detection
729 HR_MULTI_MESSAGE.enable_message_list;
730 --
731 --Convert constant values to their corresponding boolean value
732 l_validate := HR_API.constant_to_boolean(p_constant_value => p_validate);
733 --
734 --Get In Activity Normal Default Situation Id
735 l_reinstate_sit_id:= PQH_FR_STAT_SIT_UTIL.get_dflt_situation
736 (p_business_group_id => HR_GENERAL.get_business_group_id
737 ,p_effective_date => p_reinstate_date
738 ,p_situation_type => 'IA'
739 ,p_sub_type => 'IA_N');
740 IF l_reinstate_sit_id = -1 THEN
741 FND_MESSAGE.set_name('PQH','FR_PQH_STS_NO_DFLT_SIT'); --In Activity Normal Default Situation does not exist.
742 HR_MULTI_MESSAGE.ADD;
743 RAISE HR_MULTI_MESSAGE.error_message_exist;
744 END IF;
745 --
746 --Get details for the situation being reinstated.
747 OPEN csr_current_situation_dtls(p_emp_stat_situation_id);
748 FETCH csr_current_situation_dtls INTO lr_currec;
749 IF csr_current_situation_dtls%NOTFOUND THEN
750 CLOSE csr_current_situation_dtls;
751 FND_MESSAGE.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
752 HR_MULTI_MESSAGE.ADD;
753 RAISE HR_MULTI_MESSAGE.error_message_exist;
754 END IF;
755 CLOSE csr_current_situation_dtls;
756 --
757 --Check Reinstate Date is valid.
758 IF(TRUNC(p_reinstate_date) <= TRUNC(NVL(lr_currec.actual_start_date,lr_currec.provisional_start_date)) OR
759 TRUNC(p_reinstate_date) > TRUNC(NVL(lr_currec.actual_end_date,lr_currec.provisional_end_date)+1)) THEN
760 FND_MESSAGE.set_name('PQH','FR_PQH_INVLD_REINSTATE_DT'); --Invalid Reinstate Date entered. It must be between Start Date and End Date.
761 HR_MULTI_MESSAGE.ADD;
762 RAISE HR_MULTI_MESSAGE.error_message_exist;
763 END IF;
764 --
765 --Check if In Activity Normal Default situation exists.
766 OPEN csr_get_iand_dtls(l_reinstate_sit_id,NVL(lr_currec.actual_end_date,lr_currec.provisional_end_date));
767 FETCH csr_get_iand_dtls INTO l_iand_emp_stat_sit_id,l_iand_prov_end_date,l_iand_act_end_date,l_iand_ovn;
768 IF csr_get_iand_dtls%NOTFOUND THEN
769 CLOSE csr_get_iand_dtls;
770 FND_MESSAGE.set_name('PQH','FR_PQH_IAND_NOT_FND'); --Cannot Renew/Reinstate because this Situation has already been Renewed or Reinstated.
771 HR_MULTI_MESSAGE.ADD;
772 RAISE HR_MULTI_MESSAGE.error_message_exist;
773 END IF;
774 CLOSE csr_get_iand_dtls;
775 --
776 --Assign the current Situation OVN number. Required for update.
777 l_upd_sit_ovn := lr_currec.object_version_number;
778 --
779 --Update current Situation to end on (Reinstate Date - 1)
780 pqh_fr_emp_stat_situation_api.update_emp_stat_situation
781 (p_emp_stat_situation_id => p_emp_stat_situation_id
782 ,p_effective_date => TRUNC(p_reinstate_date-1)
783 ,p_object_version_number => l_upd_sit_ovn
784 ,p_actual_end_date => TRUNC(p_reinstate_date-1));
785 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
786 RAISE HR_MULTI_MESSAGE.error_message_exist;
787 END IF;
788 --
789 --Update Start Date of IAND situation (IAND = In Activity Normal Default)
790 pqh_fr_emp_stat_situation_api.update_emp_stat_situation
791 (p_effective_date => TRUNC(p_reinstate_date)
792 ,p_emp_stat_situation_id => l_iand_emp_stat_sit_id --Update this IAND rec
793 ,p_statutory_situation_id => l_reinstate_sit_id
794 ,p_provisional_start_date => TRUNC(p_reinstate_date)
795 ,p_actual_start_date => TRUNC(p_reinstate_date)
796 ,p_approval_flag => 'Y'
797 ,p_comments => p_comments
798 ,p_object_version_number => l_iand_ovn);
799 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
800 RAISE HR_MULTI_MESSAGE.error_message_exist;
801 END IF;
802 --
803 --Update Assignment to reflect the REINSTATEMENT.
804 updt_assign(lr_currec.person_id
805 ,l_reinstate_sit_id
806 ,l_reinstate_sit_id
807 ,TRUNC(p_reinstate_date)
808 ,TRUNC(NVL(l_iand_act_end_date,l_iand_prov_end_date)));
809 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
810 RAISE HR_MULTI_MESSAGE.error_message_exist;
811 END IF;
812 --
813 p_new_emp_stat_situation_id := l_iand_emp_stat_sit_id;
814 p_return_status := HR_MULTI_MESSAGE.get_return_status_disable;
815 HR_UTILITY.set_location(' Leaving: '||l_proc, 20);
816 --
817 EXCEPTION
818 WHEN HR_MULTI_MESSAGE.error_message_exist THEN
819 --Catch Multiple Message List exception
820 ROLLBACK TO pre_state;
821 --Reset IN OUT parameters and set OUT parameters
822 p_new_emp_stat_situation_id := NULL;
823 p_return_status := HR_MULTI_MESSAGE.get_return_status_disable;
824 HR_UTILITY.set_location(' Leaving: '||l_proc, 30);
825 WHEN others THEN
826 --When Multiple Message Detection is enabled catch any Application specific or other unexpected exceptions.
827 --Adding appropriate details to Multiple Message List. Otherwise re-raise the error.
828 ROLLBACK TO pre_state;
829 IF HR_MULTI_MESSAGE.unexpected_error_add(l_proc) THEN
830 HR_UTILITY.set_location(' Leaving: '||l_proc, 40);
831 RAISE;
832 END IF;
833 --Reset IN OUT and set OUT parameters
834 p_new_emp_stat_situation_id := NULL;
835 p_return_status := HR_MULTI_MESSAGE.get_return_status_disable;
836 HR_UTILITY.set_location(' Leaving: '||l_proc, 50);
837 END reinstate_emp_stat_situation;
838 --
839 -- ---------------------------------------------------------------------------
840 -- |-----------------------< renew_emp_stat_situation >-----------------------
841 -- ---------------------------------------------------------------------------
842 PROCEDURE renew_emp_stat_situation
843 (p_validate IN NUMBER DEFAULT HR_API.g_false_num
844 ,p_emp_stat_situation_id IN OUT nocopy NUMBER
845 ,p_renew_stat_situation_id IN NUMBER
846 ,p_renewal_duration IN NUMBER
847 ,p_duration_units IN VARCHAR2
848 ,p_approval_flag IN VARCHAR2
849 ,p_comments IN VARCHAR2
850 ,p_object_version_number IN OUT nocopy NUMBER
851 ,p_return_status OUT nocopy VARCHAR2
852 )
853 IS
854 --
855 --Cursor to get current Situation details
856 CURSOR csr_current_situation_dtls(p_emp_stat_situation_id IN NUMBER) IS
857 SELECT emp_stat_situation_id, person_id, statutory_situation_id,approval_flag,
858 actual_start_date,provisional_start_date, actual_end_date, provisional_end_date,
859 renewal_flag, renew_stat_situation_id, object_version_number
860 FROM pqh_fr_emp_stat_situations
861 WHERE emp_stat_situation_id = p_emp_stat_situation_id;
862 --
863 --Cursor to get most recent Renewal Situation details
864 CURSOR csr_renew_situation_dtls(p_renew_emp_stat_sit_id IN NUMBER) IS
865 SELECT emp_stat_situation_id, person_id, statutory_situation_id, approval_flag,
866 actual_start_date, provisional_start_date,
867 actual_end_date, provisional_end_date, object_version_number
868 FROM pqh_fr_emp_stat_situations
869 WHERE renew_stat_situation_id = p_renew_emp_stat_sit_id
870 AND renewal_flag = 'Y'
871 AND NVL(actual_start_date,provisional_start_date)
872 = (SELECT MAX(NVL(actual_start_date,provisional_start_date))
873 FROM pqh_fr_emp_stat_situations
874 WHERE renew_stat_situation_id = p_renew_emp_stat_sit_id
875 AND renewal_flag = 'Y');
876 --
877 --Cursor for fetching In Activity Normal Default Situation existing after the Situation being renewed.
878 CURSOR csr_get_iand_dtls(p_person_id NUMBER,
879 p_end_date DATE) IS
880 SELECT emp_stat_situation_id, statutory_situation_id, object_version_number,
881 provisional_end_date, actual_end_date
882 FROM pqh_fr_emp_stat_situations
883 WHERE person_id = p_person_id
884 AND statutory_situation_id IN(SELECT statutory_situation_id
885 FROM pqh_fr_stat_situations_v sit
886 ,per_shared_types_vl sh
887 WHERE sh.shared_type_id = type_of_ps
888 AND sh.system_type_cd = NVL(PQH_FR_UTILITY.get_bg_type_of_ps,sh.system_type_cd)
889 AND sit.business_group_id = HR_GENERAL.get_business_group_id
890 AND sit.default_flag = 'Y'
891 AND sit.situation_type = 'IA'
892 AND sit.sub_type = 'IA_N'
893 AND TRUNC(p_end_date+1) BETWEEN
894 sit.date_from AND NVL(sit.date_to,HR_GENERAL.end_of_time))
895 AND TRUNC(NVL(actual_start_date,provisional_start_date)) = TRUNC(p_end_date+1);
896 --
897 --Variable Declaration
898 l_proc VARCHAR2(72) := g_package||'renew_emp_stat_situation';
899 l_validate BOOLEAN;
900 l_emp_stat_situation_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
901 l_object_version_number PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
902 l_renew_start_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
903 l_renew_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
904 l_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
905 l_iand_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
906 l_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
907 l_iand_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
908 l_iand_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
909 l_new_emp_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
910 l_new_sit_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
911 l_rn_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
912 l_rn_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;
913 l_rn_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
914 l_rn_approval_flag PQH_FR_EMP_STAT_SITUATIONS.approval_flag%TYPE;
915 l_rn_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
916 l_rn_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
917 l_rn_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
918 l_rn_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
919 l_rn_renewal_flag PQH_FR_EMP_STAT_SITUATIONS.renewal_flag%TYPE;
920 l_rn_renew_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.renew_stat_situation_id%TYPE;
921 l_rn_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
922 --
923 BEGIN
924 --
925 --Log entry
926 HR_UTILITY.set_location(' Entering: '||l_proc, 10);
927 --
928 --Issue savepoint
929 SAVEPOINT pre_state;
930 --
931 --Initialise Multiple Message Detection
932 HR_MULTI_MESSAGE.enable_message_list;
933 --
934 --Convert constant values to their corresponding boolean value
935 l_validate := HR_API.constant_to_boolean(p_constant_value => p_validate);
936 --
937 -- Remember IN OUT parameter IN values
938 l_emp_stat_situation_id := p_emp_stat_situation_id;
939 l_object_version_number := p_object_version_number;
940 --
941 --Get current situation details.
942 OPEN csr_current_situation_dtls(p_emp_stat_situation_id);
943 FETCH csr_current_situation_dtls INTO l_rn_emp_stat_sit_id,l_rn_person_id,
944 l_rn_stat_sit_id,l_rn_approval_flag,
945 l_rn_act_st_date,l_rn_prov_st_date,
946 l_rn_act_end_date,l_rn_prov_end_date,
947 l_rn_renewal_flag,l_rn_renew_stat_sit_id,l_rn_ovn;
948 IF csr_current_situation_dtls%NOTFOUND THEN
949 CLOSE csr_current_situation_dtls;
950 FND_MESSAGE.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
951 HR_MULTI_MESSAGE.ADD;
952 RAISE HR_MULTI_MESSAGE.error_message_exist;
953 END IF;
954 CLOSE csr_current_situation_dtls;
955 --
956 --If Situation has been renewed earlier then get latest renewal record.
957 IF l_rn_renewal_flag = 'Y' AND l_rn_renew_stat_sit_id IS NOT NULL THEN
958 OPEN csr_renew_situation_dtls(l_rn_renew_stat_sit_id);
959 FETCH csr_renew_situation_dtls INTO l_rn_emp_stat_sit_id,l_rn_person_id,l_rn_stat_sit_id,
960 l_rn_approval_flag,l_rn_act_st_date,l_rn_prov_st_date,
961 l_rn_act_end_date,l_rn_prov_end_date,l_rn_ovn;
962 IF csr_renew_situation_dtls%NOTFOUND THEN
963 CLOSE csr_renew_situation_dtls;
964 FND_MESSAGE.set_name('PQH', 'FR_PQH_STS_RENEW_NOT_FND'); --Cannot find latest renewal for the Situation.
965 HR_MULTI_MESSAGE.ADD;
966 RAISE HR_MULTI_MESSAGE.error_message_exist;
967 END IF;
968 CLOSE csr_renew_situation_dtls;
969 END IF;
970 --
971 --Calculate duration
972 l_renew_start_date := NVL(l_rn_act_end_date,l_rn_prov_end_date);
973 IF p_duration_units = 'D' THEN
974 l_renew_end_date := l_renew_start_date + p_renewal_duration;
975 ELSIF p_duration_units = 'W' THEN
976 l_renew_end_date := l_renew_start_date + p_renewal_duration*7;
977 ELSIF p_duration_units = 'M' THEN
978 l_renew_end_date := ADD_MONTHS(l_renew_start_date,p_renewal_duration);
979 ELSIF p_duration_units = 'Y' THEN
980 l_renew_end_date := ADD_MONTHS(l_renew_start_date,p_renewal_duration*12);
981 ELSE
982 FND_MESSAGE.set_name('PQH','FR_PQH_STS_INVALID_UNITS');
983 HR_MULTI_MESSAGE.add;
984 RAISE HR_MULTI_MESSAGE.error_message_exist;
985 END IF;
986 --
987 --Check if In Activity Normal Default situation exists.
988 OPEN csr_get_iand_dtls(l_rn_person_id,l_renew_start_date);
989 FETCH csr_get_iand_dtls INTO l_iand_emp_stat_sit_id,l_iand_stat_sit_id,l_iand_ovn,
990 l_iand_prov_end_date,l_iand_act_end_date;
991 IF csr_get_iand_dtls%NOTFOUND THEN
992 CLOSE csr_get_iand_dtls;
993 FND_MESSAGE.set_name('PQH','FR_PQH_IAND_NOT_FND'); --Cannot Renew/Reinstate because this Situation has already been Renewed or Reinstated.
994 HR_MULTI_MESSAGE.ADD;
995 RAISE HR_MULTI_MESSAGE.error_message_exist;
996 END IF;
997 CLOSE csr_get_iand_dtls;
998 --
999 --Check whether IAND exists for entire duration of the Renewal.
1000 IF l_renew_end_date > NVL(l_iand_act_end_date,NVL(l_iand_prov_end_date,HR_GENERAL.end_of_time)) THEN
1001 FND_MESSAGE.set_name('PQH','FR_PQH_IAND_NOT_FND'); --Cannot Renew/Reinstate because this Situation has already been Renewed or Reinstated.
1002 HR_MULTI_MESSAGE.ADD;
1003 RAISE HR_MULTI_MESSAGE.error_message_exist;
1004 END IF;
1005 --
1006 --Update Actual End Date of current/most recent renewal situation.
1007 pqh_fr_emp_stat_situation_api.update_emp_stat_situation
1008 (p_effective_date => l_renew_start_date
1009 ,p_emp_stat_situation_id => l_rn_emp_stat_sit_id
1010 ,p_actual_end_date => TRUNC(l_renew_start_date)
1011 ,p_object_version_number => l_rn_ovn);
1012 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
1013 RAISE HR_MULTI_MESSAGE.error_message_exist;
1014 END IF;
1015 --
1016 --Create a new Renewal Situation record for the Renew duration.
1017 pqh_fr_emp_stat_situation_api.create_emp_stat_situation
1018 (p_effective_date => TRUNC(l_renew_start_date+1)
1019 ,p_emp_stat_situation_id => l_new_emp_sit_id
1020 ,p_statutory_situation_id => l_rn_stat_sit_id
1021 ,p_person_id => l_rn_person_id
1022 ,p_provisional_start_date => TRUNC(l_renew_start_date+1)
1023 ,p_provisional_end_date => TRUNC(l_renew_end_date)
1024 ,p_approval_flag => p_approval_flag
1025 ,p_comments => p_comments
1026 ,p_renewal_flag => 'Y'
1027 ,p_renew_stat_situation_id => l_emp_stat_situation_id
1028 ,p_object_version_number => l_new_sit_ovn);
1029 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
1030 RAISE HR_MULTI_MESSAGE.error_message_exist;
1031 END IF;
1032 --
1033 --Update Start Date of IAND situation (IAND = In Activity Normal Default)
1034 pqh_fr_emp_stat_situation_api.update_emp_stat_situation
1035 (p_effective_date => TRUNC(l_renew_end_date+1)
1036 ,p_emp_stat_situation_id => l_iand_emp_stat_sit_id --Update this IAND rec
1037 ,p_statutory_situation_id => l_iand_stat_sit_id
1038 ,p_provisional_start_date => TRUNC(l_renew_end_date+1)
1039 ,p_actual_start_date => TRUNC(l_renew_end_date+1)
1040 ,p_approval_flag => 'Y'
1041 ,p_comments => p_comments
1042 ,p_object_version_number => l_iand_ovn);
1043 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
1044 RAISE HR_MULTI_MESSAGE.error_message_exist;
1045 END IF;
1046 --
1047 --Update Assignment to reflect the Renewal.
1048 updt_assign(l_rn_person_id
1049 ,l_rn_stat_sit_id
1050 ,l_iand_stat_sit_id
1051 ,TRUNC(l_renew_start_date+1)
1052 ,TRUNC(l_renew_end_date));
1053 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
1054 RAISE HR_MULTI_MESSAGE.error_message_exist;
1055 END IF;
1056 --
1057 /*
1058 --Update_assignment to reflect the Renewal.
1059 pqh_fr_emp_stat_situation_api.update_assignments
1060 (p_person_id => l_rn_person_id
1061 ,p_statutory_situation_id => l_iand_stat_sit_id
1062 ,p_start_date => TRUNC(l_renew_end_date+1)
1063 ,p_end_date => TRUNC(NVL(l_iand_act_end_date,NVL(l_iand_prov_end_date,HR_GENERAL.end_of_time))));
1064 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
1065 RAISE HR_MULTI_MESSAGE.error_message_exist;
1066 END IF;
1067 --
1068 */
1069 p_return_status := HR_MULTI_MESSAGE.get_return_status_disable;
1070 HR_UTILITY.set_location(' Leaving: '||l_proc, 20);
1071 --
1072 EXCEPTION
1073 WHEN HR_MULTI_MESSAGE.error_message_exist THEN
1074 --Catch Multiple Message List exception
1075 ROLLBACK TO pre_state;
1076 --Reset IN OUT parameters and set OUT parameters
1077 p_emp_stat_situation_id := l_emp_stat_situation_id;
1078 p_object_version_number := l_object_version_number;
1079 p_return_status := HR_MULTI_MESSAGE.get_return_status_disable;
1080 HR_UTILITY.set_location(' Leaving:' || l_proc, 30);
1081 WHEN others THEN
1082 --When Multiple Message Detection is enabled catch any Application specific or other unexpected exceptions.
1083 --Adding appropriate details to Multiple Message List. Otherwise re-raise the error.
1084 ROLLBACK TO pre_state;
1085 IF HR_MULTI_MESSAGE.unexpected_error_add(l_proc) THEN
1086 HR_UTILITY.set_location(' Leaving: '||l_proc, 40);
1087 RAISE;
1088 END IF;
1089 --Reset IN OUT and set OUT parameters
1090 p_emp_stat_situation_id := l_emp_stat_situation_id;
1091 p_object_version_number := l_object_version_number;
1092 p_return_status := HR_MULTI_MESSAGE.get_return_status_disable;
1093 HR_UTILITY.set_location(' Leaving: '||l_proc, 50);
1094 END renew_emp_stat_situation;
1095 --
1096 -- ---------------------------------------------------------------------------
1097 -- |-----------------------< delete_emp_stat_situation >----------------------
1098 -- ---------------------------------------------------------------------------
1099 PROCEDURE delete_emp_stat_situation
1100 (p_validate IN NUMBER DEFAULT HR_API.g_false_num
1101 ,p_emp_stat_situation_id IN NUMBER
1102 ,p_object_version_number IN NUMBER
1103 ,p_return_status OUT nocopy VARCHAR2
1104 )
1105 IS
1106 --
1107 --Cursor to get current Situation details
1108 CURSOR csr_current_situation_dtls(p_emp_stat_situation_id IN NUMBER) IS
1109 SELECT person_id, actual_start_date, provisional_start_date, actual_end_date, provisional_end_date
1110 FROM pqh_fr_emp_stat_situations
1111 WHERE emp_stat_situation_id = p_emp_stat_situation_id;
1112 --
1113 --Cursor to fetch prior Situation falling before the Situation to be deleted
1114 CURSOR csr_get_prior_sit_dtls(p_person_id IN NUMBER,
1115 p_date IN DATE) IS
1116 SELECT emp_stat_situation_id, statutory_situation_id, object_version_number,
1117 provisional_start_date, actual_start_date, provisional_end_date, actual_end_date
1118 FROM pqh_fr_emp_stat_situations
1119 WHERE person_id = p_person_id
1120 AND TRUNC(NVL(actual_end_date,provisional_end_date)) = TRUNC(p_date-1);
1121 --
1122 --Cursor to fetch future Situation falling after the Situation to be deleted
1123 CURSOR csr_get_next_sit_dtls(p_person_id IN NUMBER,
1124 p_date IN DATE) IS
1125 SELECT emp_stat_situation_id, statutory_situation_id, object_version_number,
1126 provisional_end_date, actual_end_date
1127 FROM pqh_fr_emp_stat_situations
1128 WHERE person_id = p_person_id
1129 AND TRUNC(NVL(actual_start_date,provisional_start_date)) = TRUNC(p_date+1);
1130 --
1131 -- Variable Declaration
1132 l_proc VARCHAR2(72) := g_package||'delete_emp_stat_situation';
1133 l_validate BOOLEAN;
1134 l_person_id PQH_FR_EMP_STAT_SITUATIONS.person_id%TYPE;
1135 l_act_st_dt PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
1136 l_prov_st_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1137 l_act_end_dt PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1138 l_prov_end_dt PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
1139 l_prior_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1140 l_prior_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1141 l_prior_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
1142 l_prior_prov_st_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1143 l_prior_act_st_date PQH_FR_EMP_STAT_SITUATIONS.actual_start_date%TYPE;
1144 l_prior_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_start_date%TYPE;
1145 l_prior_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1146 l_next_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1147 l_next_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1148 l_next_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
1149 l_next_prov_end_date PQH_FR_EMP_STAT_SITUATIONS.provisional_end_date%TYPE;
1150 l_next_act_end_date PQH_FR_EMP_STAT_SITUATIONS.actual_end_date%TYPE;
1151 l_iand_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1152 l_new_iand_emp_stat_sit_id PQH_FR_EMP_STAT_SITUATIONS.emp_stat_situation_id%TYPE;
1153 l_new_iand_ovn PQH_FR_EMP_STAT_SITUATIONS.object_version_number%TYPE;
1154 --
1155 BEGIN
1156 --
1157 --Log entry
1158 HR_UTILITY.set_location(' Entering: '||l_proc, 10);
1159 --
1160 --Issue savepoint
1161 SAVEPOINT pre_state;
1162 --
1163 --Initialise Multiple Message Detection
1164 HR_MULTI_MESSAGE.enable_message_list;
1165 --
1166 --Convert constant values to their corresponding boolean value
1167 l_validate := HR_API.constant_to_boolean(p_constant_value => p_validate);
1168 --
1169 --Fetch details of Situation to be deleted.
1170 OPEN csr_current_situation_dtls(p_emp_stat_situation_id);
1171 FETCH csr_current_situation_dtls INTO l_person_id, l_act_st_dt, l_prov_st_dt,
1172 l_act_end_dt, l_prov_end_dt;
1173 IF csr_current_situation_dtls%NOTFOUND THEN
1174 CLOSE csr_current_situation_dtls;
1175 FND_MESSAGE.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
1176 HR_MULTI_MESSAGE.ADD;
1177 RAISE HR_MULTI_MESSAGE.error_message_exist;
1178 END IF;
1179 CLOSE csr_current_situation_dtls;
1180 --
1181 --Fetch details for prior Situation ending on current situations start date - 1
1182 OPEN csr_get_prior_sit_dtls(l_person_id,TRUNC(NVL(l_act_st_dt,l_prov_st_dt)));
1183 FETCH csr_get_prior_sit_dtls INTO l_prior_emp_stat_sit_id, l_prior_sit_id, l_prior_ovn,
1184 l_prior_prov_st_date, l_prior_act_st_date,
1185 l_prior_prov_end_date, l_prior_act_end_date;
1186 IF csr_get_prior_sit_dtls%NOTFOUND THEN
1187 CLOSE csr_get_prior_sit_dtls;
1188 FND_MESSAGE.set_name('PQH','FR_PQH_PRIOR_SIT_NOT_FND'); --Cannot Update/Delete because no situation exists prior to this situation.
1189 HR_MULTI_MESSAGE.ADD;
1190 RAISE HR_MULTI_MESSAGE.error_message_exist;
1191 END IF;
1192 CLOSE csr_get_prior_sit_dtls;
1193 --
1194 --Fetch details of future Situation starting from current situations end date + 1
1195 OPEN csr_get_next_sit_dtls(l_person_id,TRUNC(NVL(l_act_end_dt,l_prov_end_dt)));
1196 FETCH csr_get_next_sit_dtls INTO l_next_emp_stat_sit_id, l_next_sit_id, l_next_ovn,
1197 l_next_prov_end_date, l_next_act_end_date;
1198 IF csr_get_next_sit_dtls%NOTFOUND THEN
1199 CLOSE csr_get_next_sit_dtls;
1200 FND_MESSAGE.set_name('PQH','FR_PQH_NEXT_SIT_NOT_FND'); --Cannot Update/Delete because no situation exists after this situation.
1201 HR_MULTI_MESSAGE.ADD;
1202 RAISE HR_MULTI_MESSAGE.error_message_exist;
1203 END IF;
1204 CLOSE csr_get_next_sit_dtls;
1205 --
1206 --Get In Activity Normal Default Situation Id
1207 l_iand_sit_id:= PQH_FR_STAT_SIT_UTIL.get_dflt_situation
1208 (p_business_group_id => HR_GENERAL.get_business_group_id
1209 ,p_effective_date => TRUNC(NVL(l_act_st_dt,l_prov_st_dt))
1210 ,p_situation_type => 'IA'
1211 ,p_sub_type => 'IA_N');
1212 IF l_iand_sit_id = -1 THEN
1213 FND_MESSAGE.set_name('PQH','FR_PQH_STS_NO_DFLT_SIT'); --In Activity Normal Default Situation does not exist.
1214 HR_MULTI_MESSAGE.ADD;
1215 RAISE HR_MULTI_MESSAGE.error_message_exist;
1216 END IF;
1217 --
1218 --Call API to delete the current Situation
1219 pqh_fr_emp_stat_situation_api.delete_emp_stat_situation
1220 (p_validate => l_validate
1221 ,p_emp_stat_situation_id => p_emp_stat_situation_id
1222 ,p_object_version_number => p_object_version_number);
1223 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
1224 RAISE HR_MULTI_MESSAGE.error_message_exist;
1225 END IF;
1226 --
1227 --Update Assignment to reflect the delete.
1228 updt_assign(l_person_id
1229 ,l_iand_sit_id
1230 ,l_iand_sit_id
1231 ,TRUNC(NVL(l_act_st_dt,l_prov_st_dt))
1232 ,TRUNC(NVL(l_act_end_dt,l_prov_end_dt)));
1233 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
1234 RAISE HR_MULTI_MESSAGE.error_message_exist;
1235 END IF;
1236 --
1237 --If previous Situation is a IAND situation and future Situation is a IAND situation.
1238 IF l_prior_sit_id = l_iand_sit_id AND l_next_sit_id = l_iand_sit_id THEN
1239 --
1240 --Call API to delete the next IAND situation.
1241 pqh_fr_emp_stat_situation_api.delete_emp_stat_situation
1242 (p_validate => l_validate
1243 ,p_emp_stat_situation_id => l_next_emp_stat_sit_id
1244 ,p_object_version_number => l_next_ovn);
1245 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
1246 RAISE HR_MULTI_MESSAGE.error_message_exist;
1247 END IF;
1248 --
1249 --Update prior situations end date to next IAND's end date.
1250 pqh_fr_emp_stat_situation_api.update_emp_stat_situation
1251 (p_effective_date => TRUNC(NVL(l_act_st_dt,l_prov_st_dt))
1252 ,p_emp_stat_situation_id => l_prior_emp_stat_sit_id --Update this IAND rec
1253 ,p_statutory_situation_id => l_prior_sit_id
1254 ,p_provisional_end_date => TRUNC(l_next_prov_end_date)
1255 ,p_actual_end_date => TRUNC(l_next_act_end_date)
1256 ,p_approval_flag => 'Y'
1257 ,p_object_version_number => l_prior_ovn);
1258 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
1259 RAISE HR_MULTI_MESSAGE.error_message_exist;
1260 END IF;
1261 --
1262 --Else if prior Situation is not a IAND Situation and future Situation is a IAND Situation.
1263 ELSIF l_prior_sit_id <> l_iand_sit_id AND l_next_sit_id = l_iand_sit_id THEN
1264 --
1265 --Update prior sits actual end date to end of time, if deleted sits actual end date is end of time.
1266 IF TRUNC(NVL(l_act_end_dt,HR_GENERAL.end_of_time)) = TRUNC(HR_GENERAL.end_of_time) THEN
1267 pqh_fr_emp_stat_situation_api.update_emp_stat_situation
1268 (p_effective_date => TRUNC(NVL(l_act_st_dt,l_prov_st_dt))
1269 ,p_emp_stat_situation_id => l_prior_emp_stat_sit_id --Update this IAND rec
1270 ,p_statutory_situation_id => l_prior_sit_id
1271 ,p_actual_end_date => TRUNC(l_act_end_dt)
1272 ,p_approval_flag => 'Y'
1273 ,p_object_version_number => l_prior_ovn);
1274 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
1275 RAISE HR_MULTI_MESSAGE.error_message_exist;
1276 END IF;
1277 END IF;
1278 --
1279 --Update the Next IAND Situation's start date to deleted Situations start date
1280 pqh_fr_emp_stat_situation_api.update_emp_stat_situation
1281 (p_effective_date => TRUNC(NVL(l_act_st_dt,l_prov_st_dt))
1282 ,p_emp_stat_situation_id => l_next_emp_stat_sit_id --Update this IAND rec
1283 ,p_statutory_situation_id => l_next_sit_id
1284 ,p_provisional_start_date => TRUNC(NVL(l_act_st_dt,l_prov_st_dt))
1285 ,p_actual_start_date => TRUNC(NVL(l_act_st_dt,l_prov_st_dt))
1286 ,p_approval_flag => 'Y'
1287 ,p_object_version_number => l_next_ovn);
1288 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
1289 RAISE HR_MULTI_MESSAGE.error_message_exist;
1290 END IF;
1291 --
1292 --Else if prior Situation is not a IAND Situation and future Situation is not a IAND Situation.
1293 ELSIF l_prior_sit_id <> l_iand_sit_id AND l_next_sit_id <> l_iand_sit_id THEN
1294 --
1295 --Create a IAND Situation for the duration of Delete Siutation.
1296 pqh_fr_emp_stat_situation_api.create_emp_stat_situation
1297 (p_effective_date => TRUNC(NVL(l_act_st_dt,l_prov_st_dt))
1298 ,p_emp_stat_situation_id => l_new_iand_emp_stat_sit_id
1299 ,p_statutory_situation_id => l_iand_sit_id
1300 ,p_person_id => l_person_id
1301 ,p_provisional_start_date => TRUNC(NVL(l_act_st_dt,l_prov_st_dt))
1302 ,p_provisional_end_date => TRUNC(NVL(l_act_end_dt,l_prov_end_dt))
1303 ,p_actual_start_date => TRUNC(NVL(l_act_st_dt,l_prov_st_dt))
1304 ,p_actual_end_date => TRUNC(l_act_end_dt)
1305 ,p_approval_flag => 'Y'
1306 ,p_object_version_number => l_new_iand_ovn);
1307 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
1308 RAISE HR_MULTI_MESSAGE.error_message_exist;
1309 END IF;
1310 --
1311 --Else if prior Situation is a IAND Situation and future Situation is not a IAND Situation.
1312 ELSIF l_prior_sit_id = l_iand_sit_id AND l_next_sit_id <> l_iand_sit_id THEN
1313 --
1314 --Update the prior IAND Situation's end date to deleted Situations end date
1315 pqh_fr_emp_stat_situation_api.update_emp_stat_situation
1316 (p_effective_date => TRUNC(NVL(l_act_st_dt,l_prov_st_dt))
1317 ,p_emp_stat_situation_id => l_prior_emp_stat_sit_id --Update this IAND rec
1318 ,p_statutory_situation_id => l_prior_sit_id
1319 ,p_provisional_end_date => TRUNC(NVL(l_act_end_dt,l_prov_end_dt))
1320 ,p_actual_end_date => TRUNC(NVL(l_act_end_dt,l_prov_end_dt))
1321 ,p_approval_flag => 'Y'
1322 ,p_object_version_number => l_prior_ovn);
1323 IF HR_MULTI_MESSAGE.get_return_status = 'E' THEN
1324 RAISE HR_MULTI_MESSAGE.error_message_exist;
1325 END IF;
1326 --
1327 END IF;
1328 --
1329 p_return_status := HR_MULTI_MESSAGE.get_return_status_disable;
1330 HR_UTILITY.set_location(' Leaving: '||l_proc, 20);
1331 --
1332 EXCEPTION
1333 WHEN HR_MULTI_MESSAGE.error_message_exist THEN
1334 --Catch Multiple Message List exception
1335 ROLLBACK TO pre_state;
1336 --Reset IN OUT parameters and set OUT parameters
1337 p_return_status := HR_MULTI_MESSAGE.get_return_status_disable;
1338 HR_UTILITY.set_location(' Leaving:' || l_proc, 30);
1339 WHEN others THEN
1340 --When Multiple Message Detection is enabled catch any Application specific or other unexpected exceptions.
1341 --Adding appropriate details to Multiple Message List. Otherwise re-raise the error.
1342 ROLLBACK TO pre_state;
1343 IF HR_MULTI_MESSAGE.unexpected_error_add(l_proc) THEN
1344 HR_UTILITY.set_location(' Leaving: '||l_proc, 40);
1345 RAISE;
1346 END IF;
1347 --Reset IN OUT and set OUT parameters
1348 p_return_status := HR_MULTI_MESSAGE.get_return_status_disable;
1349 HR_UTILITY.set_location(' Leaving: '||l_proc, 50);
1350 END delete_emp_stat_situation;
1351 --
1352 --
1353 -- ---------------------------------------------------------------------------
1354 -- |---------------------------< update_assignments >-------------------------
1355 -- ---------------------------------------------------------------------------
1356 PROCEDURE updt_assign(p_person_id IN NUMBER
1357 ,p_statutory_situation_id IN NUMBER
1358 ,p_iand_stat_sit_id IN NUMBER DEFAULT NULL
1359 ,p_start_date IN DATE
1360 ,p_end_date IN DATE)
1361 IS
1362 --
1363 --Cursor to get current Situation details
1364 CURSOR csr_asg_dtls IS
1365 SELECT asg.assignment_id, asg.effective_start_date, asg.effective_end_date, ast.per_system_status
1366 FROM per_all_assignments_f asg,
1367 per_assignment_status_types ast
1368 WHERE asg.person_id = p_person_id
1369 AND(TRUNC(asg.effective_start_date) BETWEEN TRUNC(p_start_date) AND TRUNC(p_end_date)
1370 OR TRUNC(asg.effective_end_date) BETWEEN TRUNC(p_start_date) AND TRUNC(p_end_date))
1371 AND asg.primary_flag = 'Y'
1372 AND asg.assignment_status_type_id = ast.assignment_status_type_id
1373 ORDER BY asg.effective_start_date;
1374 --
1375 --Varialbe Declarations
1376 l_start_date DATE;
1377 l_end_date DATE;
1378 l_count NUMBER;
1379 --
1380 BEGIN
1381 --
1382 --Fetch Assignments that are affected.
1383 l_count := 0;
1384 FOR lr_asg IN csr_asg_dtls
1385 LOOP
1386 --
1387 l_count := l_count+1;
1388 l_start_date := lr_asg.effective_start_date;
1389 l_end_date := lr_asg.effective_end_date;
1390 --
1391 /*
1392 --Check whether Assignment starts before the Situation.
1393 IF lr_asg.per_system_status <> 'ACTIVE_ASSIGN' AND TRUNC(p_start_date) > TRUNC(l_start_date) THEN
1394 --
1395 --Set prior Assignment to Active on its effective date date.
1396 pqh_fr_emp_stat_situation_api.update_assignments
1397 (p_person_id => p_person_id
1398 ,p_statutory_situation_id => p_iand_stat_sit_id
1399 ,p_start_date => TRUNC(l_start_date)
1400 ,p_end_date => TRUNC(l_end_date));
1401 --
1402 END IF;
1403 --
1404 */
1405 IF l_count = 1 THEN
1406 --For first loop, we want to Update Assignment on Situations Start Date.
1407 l_start_date := p_start_date;
1408 END IF;
1409 --
1410 --Update all Assignments withing the Situation Duration to reflect Situation Status.
1411 pqh_fr_emp_stat_situation_api.update_assignments
1412 (p_person_id => p_person_id
1413 ,p_statutory_situation_id => p_statutory_situation_id
1414 ,p_start_date => TRUNC(l_start_date)
1415 ,p_end_date => TRUNC(l_end_date));
1416 --
1417 IF TRUNC(p_end_date) < TRUNC(l_end_date) THEN
1418 --
1419 pqh_fr_emp_stat_situation_api.update_assignments
1420 (p_person_id => p_person_id
1421 ,p_statutory_situation_id => p_iand_stat_sit_id
1422 ,p_start_date => TRUNC(p_end_date+1)
1423 ,p_end_date => TRUNC(l_end_date));
1424 --
1425 END IF;
1426 --
1427 END LOOP;
1428 --
1429 --If no Assignment are within Situation duration then Update Assignment just once for Situation.
1430 IF l_count = 0 THEN
1431 pqh_fr_emp_stat_situation_api.update_assignments
1432 (p_person_id => p_person_id
1433 ,p_statutory_situation_id => p_statutory_situation_id
1434 ,p_start_date => TRUNC(p_start_date)
1435 ,p_end_date => TRUNC(p_end_date));
1436 --
1437 pqh_fr_emp_stat_situation_api.update_assignments
1438 (p_person_id => p_person_id
1439 ,p_statutory_situation_id => p_iand_stat_sit_id
1440 ,p_start_date => TRUNC(p_end_date+1));
1441 END IF;
1442 --
1443 END updt_assign;
1444 --
1445 --
1446 -- ---------------------------------------------------------------------------
1447 -- --------------------------< is_person_active >-----------------------------
1448 -- ---------------------------------------------------------------------------
1449 FUNCTION is_person_active
1450 (p_person_id IN NUMBER,
1451 p_effective_date IN DATE) RETURN VARCHAR2
1452 IS
1453 --
1454 --Cursor to fetch Situation as on effective date.
1455 CURSOR csr_emp_sit_dtls IS
1456 SELECT statutory_situation_id
1457 FROM pqh_fr_emp_stat_situations
1458 WHERE person_id = p_person_id
1459 AND p_effective_date BETWEEN NVL(actual_start_date,provisional_start_date)
1460 AND NVL(actual_end_date,NVL(provisional_end_date,HR_GENERAL.end_of_time));
1461 --
1462 --Varialbe Declarations.
1463 l_default_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1464 l_sit_id PQH_FR_EMP_STAT_SITUATIONS.statutory_situation_id%TYPE;
1465 l_is_active VARCHAR2(10) := NULL;
1466 --
1467 BEGIN
1468 --
1469 --Get Situation Id as on effective date.
1470 OPEN csr_emp_sit_dtls;
1471 FETCH csr_emp_sit_dtls INTO l_sit_id;
1472 IF csr_emp_sit_dtls%NOTFOUND THEN
1473 CLOSE csr_emp_sit_dtls;
1474 l_is_active := 'E';
1475 RETURN l_is_active;
1476 END IF;
1477 IF csr_emp_sit_dtls%ISOPEN THEN
1478 CLOSE csr_emp_sit_dtls;
1479 END IF;
1480 --
1481 --Get In Activity Normal Default Situation Id.
1482 l_default_sit_id:= PQH_FR_STAT_SIT_UTIL.get_dflt_situation
1483 (p_business_group_id => HR_GENERAL.get_business_group_id
1484 ,p_effective_date => p_effective_date
1485 ,p_situation_type => 'IA'
1486 ,p_sub_type => 'IA_N');
1487 --If Default Situation does not exist then return error.
1488 IF l_default_sit_id = -1 THEN
1489 l_is_active := 'E';
1490 ELSE
1491 --If Person Situation is In Activity Default Normalthen return 'Y' else 'N'.
1492 IF l_sit_id = l_default_sit_id THEN
1493 l_is_active := 'Y';
1494 ELSE
1495 l_is_active := 'N';
1496 END IF;
1497 END IF;
1498 --
1499 RETURN l_is_active;
1500 --
1501 END is_person_active;
1502 --
1503 END PQH_FR_EMP_STAT_SIT_UTILITY;