[Home] [Help]
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.
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;
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;
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;
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;
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:
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: --
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);
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;
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
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
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.
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;
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);
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
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;
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.
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
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: -- ---------------------------------------------------------------------------
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: --
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: --
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
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: --
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
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: --
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
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.
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,
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;
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
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.
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: --
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
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
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.
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: --
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.
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
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.
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: --
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
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: --
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;
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
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;
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.
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
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: -- ---------------------------------------------------------------------------
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: --
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.
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: --
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.
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.
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: --
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.
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)
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
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.
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;
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;
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
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;
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.
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
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: -- ---------------------------------------------------------------------------
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: --
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: --
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.
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;
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: --
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.
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);
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: --
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.
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.
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.
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
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)
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
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.
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: /*
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.
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: */
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;
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
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;
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.
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
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: -- ---------------------------------------------------------------------------
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: --
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: --
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
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: --
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
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: --
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
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
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
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.
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.
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
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.
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
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.
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
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: --
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
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.
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
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.
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
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;
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: --
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
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;
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.
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
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: --