[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT emp_stat_situation_id, statutory_situation_id, object_version_number,
actual_start_date, provisional_start_date,
actual_end_date, NVL(provisional_end_date,HR_GENERAL.end_of_time)
FROM pqh_fr_emp_stat_situations
WHERE person_id = p_person_id
AND statutory_situation_id IN (SELECT statutory_situation_id
FROM pqh_fr_stat_situations_v sit
,per_shared_types_vl sh
WHERE sh.shared_type_id = type_of_ps
AND sh.system_type_cd = NVL(PQH_FR_UTILITY.get_bg_type_of_ps,sh.system_type_cd)
AND sit.business_group_id = HR_GENERAL.get_business_group_id
AND sit.default_flag = 'Y'
AND sit.situation_type = 'IA'
AND sit.sub_type = 'IA_N'
AND TRUNC(NVL(p_actual_start,p_provisional_start)) BETWEEN
sit.date_from AND NVL(sit.date_to,HR_GENERAL.end_of_time))
AND(TRUNC(NVL(actual_start_date,provisional_start_date)) <= TRUNC(NVL(p_actual_start,p_provisional_start))
AND TRUNC(NVL(actual_end_date,NVL(provisional_end_date,HR_GENERAL.end_of_time)))
>= TRUNC(NVL(p_actual_end,p_provisional_end)));
SELECT 'x'
FROM DUAL
WHERE EXISTS(SELECT 'x'
FROM pqh_fr_emp_stat_situations
WHERE person_id = p_person_id
-- AND emp_stat_situation_id <> NVL(p_emp_stat_situation_id,-1)
AND statutory_situation_id <> p_iand_stat_sit_id
AND(NVL(p_actual_start,p_provisional_start)
BETWEEN NVL(actual_start_date,provisional_start_date)
AND NVL(actual_end_date,NVL(provisional_end_date,HR_GENERAL.end_of_time))
OR NVL(p_actual_end,p_provisional_end)
BETWEEN NVL(actual_start_date,provisional_start_date)
AND NVL(actual_end_date,NVL(provisional_end_date,HR_GENERAL.end_of_time))));
pqh_fr_emp_stat_situation_api.update_emp_stat_situation
(p_effective_date => p_effective_date
,p_emp_stat_situation_id => l_iand_emp_stat_sit_id --Update this IAND rec
,p_statutory_situation_id => l_iand_stat_sit_id
,p_provisional_end_date => TRUNC(NVL(p_actual_start_date,p_provisional_start_date)-1)
,p_actual_end_date => TRUNC(NVL(p_actual_start_date,p_provisional_start_date)-1)
,p_approval_flag => 'Y'
,p_object_version_number => l_iand_ovn); --OVN for the IAND rec
PROCEDURE update_emp_stat_situation
(p_validate IN NUMBER DEFAULT HR_API.g_false_num
,p_effective_date IN DATE
,p_emp_stat_situation_id IN NUMBER
,p_statutory_situation_id IN NUMBER DEFAULT HR_API.g_number
,p_person_id IN NUMBER DEFAULT HR_API.g_number
,p_provisional_start_date IN DATE DEFAULT HR_API.g_date
,p_provisional_end_date IN DATE DEFAULT HR_API.g_date
,p_actual_start_date IN DATE DEFAULT HR_API.g_date
,p_actual_end_date IN DATE DEFAULT HR_API.g_date
,p_approval_flag IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_comments IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_contact_person_id IN NUMBER DEFAULT HR_API.g_number
,p_contact_relationship IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_external_organization_id IN NUMBER DEFAULT HR_API.g_number
,p_renewal_flag IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_renew_stat_situation_id IN NUMBER DEFAULT HR_API.g_number
,p_seconded_career_id IN NUMBER DEFAULT HR_API.g_number
,p_attribute_category IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute1 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute2 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute3 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute4 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute5 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute6 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute7 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute8 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute9 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute10 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute11 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute12 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute13 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute14 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute15 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute16 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute17 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute18 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute19 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute20 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute21 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute22 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute23 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute24 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute25 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute26 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute27 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute28 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute29 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_attribute30 IN VARCHAR2 DEFAULT HR_API.g_varchar2
,p_object_version_number IN OUT nocopy NUMBER
,p_return_status OUT nocopy VARCHAR2
)
IS
--
--Cursor to get current Situation details
CURSOR csr_current_situation_dtls(p_emp_stat_situation_id IN NUMBER) IS
SELECT person_id, statutory_situation_id, actual_start_date, provisional_start_date, actual_end_date, provisional_end_date
FROM pqh_fr_emp_stat_situations
WHERE emp_stat_situation_id = p_emp_stat_situation_id;
SELECT emp_stat_situation_id, statutory_situation_id, object_version_number,
provisional_start_date, actual_start_date, provisional_end_date, actual_end_date
FROM pqh_fr_emp_stat_situations
WHERE person_id = p_person_id
AND TRUNC(NVL(actual_end_date,provisional_end_date)) = TRUNC(p_date-1);
SELECT emp_stat_situation_id, statutory_situation_id, object_version_number,
provisional_start_date, actual_start_date, provisional_end_date, actual_end_date
FROM pqh_fr_emp_stat_situations
WHERE person_id = p_person_id
AND TRUNC(NVL(actual_start_date,provisional_start_date)) = TRUNC(p_date+1);
SELECT 'x'
FROM DUAL
WHERE EXISTS(SELECT 'x'
FROM pqh_fr_emp_stat_situations
WHERE person_id = p_person_id
AND emp_stat_situation_id <> NVL(p_emp_stat_situation_id,-1)
AND statutory_situation_id <> p_iand_stat_sit_id
AND(p_provisional_start BETWEEN NVL(actual_start_date,provisional_start_date)
AND NVL(actual_end_date,NVL(provisional_end_date,HR_GENERAL.end_of_time))
OR p_provisional_end BETWEEN NVL(actual_start_date,provisional_start_date)
AND NVL(actual_end_date,NVL(provisional_end_date,HR_GENERAL.end_of_time))));
l_proc VARCHAR2(72) := g_package||'update_emp_stat_situation';
FND_MESSAGE.set_name('PQH','FR_PQH_PRIOR_SIT_NOT_FND'); --Cannot Update/Delete because no situation exists prior to this situation.
FND_MESSAGE.set_name('PQH','FR_PQH_NEXT_SIT_NOT_FND'); --Cannot Update/Delete because no situation exists after this situation.
FND_MESSAGE.set_name('PQH','FR_PQH_STAT_SIT_RENEW'); --Cannot update because this is a Renewal Situation.
pqh_fr_emp_stat_situation_api.update_emp_stat_situation
(p_effective_date => TRUNC(NVL(l_prior_act_st_date,l_prior_prov_st_date))
,p_emp_stat_situation_id => l_prior_emp_stat_sit_id --Update this IAND rec
,p_statutory_situation_id => l_prior_sit_id
,p_provisional_end_date => TRUNC(p_provisional_start_date-1)
,p_actual_end_date => TRUNC(p_provisional_start_date-1) --in case it is not approved.
,p_approval_flag => 'Y'
,p_object_version_number => l_prior_ovn);
pqh_fr_emp_stat_situation_api.update_emp_stat_situation
(p_validate => l_validate
,p_effective_date => p_effective_date
,p_emp_stat_situation_id => p_emp_stat_situation_id
,p_statutory_situation_id => p_statutory_situation_id
,p_person_id => p_person_id
,p_provisional_start_date => p_provisional_start_date
,p_provisional_end_date => p_provisional_end_date
,p_approval_flag => p_approval_flag
,p_comments => p_comments
,p_contact_person_id => p_contact_person_id
,p_contact_relationship => p_contact_relationship
,p_external_organization_id => p_external_organization_id
,p_renewal_flag => p_renewal_flag
,p_renew_stat_situation_id => p_renew_stat_situation_id
,p_seconded_career_id => p_seconded_career_id
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_attribute21 => p_attribute21
,p_attribute22 => p_attribute22
,p_attribute23 => p_attribute23
,p_attribute24 => p_attribute24
,p_attribute25 => p_attribute25
,p_attribute26 => p_attribute26
,p_attribute27 => p_attribute27
,p_attribute28 => p_attribute28
,p_attribute29 => p_attribute29
,p_attribute30 => p_attribute30
,p_object_version_number => p_object_version_number);
pqh_fr_emp_stat_situation_api.update_emp_stat_situation
(p_effective_date => TRUNC(NVL(l_next_act_st_date,l_next_prov_st_date))
,p_emp_stat_situation_id => l_next_emp_stat_sit_id --Update this IAND rec
,p_statutory_situation_id => l_next_sit_id
,p_provisional_start_date => TRUNC(p_provisional_end_date+1)
,p_actual_start_date => TRUNC(p_provisional_end_date+1)
,p_approval_flag => 'Y'
,p_object_version_number => l_next_ovn);
END update_emp_stat_situation;
SELECT *
FROM pqh_fr_emp_stat_situations
WHERE emp_stat_situation_id = p_emp_stat_situation_id;
SELECT emp_stat_situation_id, provisional_end_date, actual_end_date, object_version_number
FROM pqh_fr_emp_stat_situations
WHERE person_id = p_person_id
AND statutory_situation_id = p_iand_stat_sit_id
AND TRUNC(NVL(actual_start_date,provisional_start_date)) = TRUNC(p_start_date+1);
pqh_fr_emp_stat_situation_api.update_emp_stat_situation
(p_emp_stat_situation_id => p_emp_stat_situation_id
,p_effective_date => TRUNC(p_reinstate_date-1)
,p_object_version_number => l_upd_sit_ovn
,p_actual_end_date => TRUNC(p_reinstate_date-1));
pqh_fr_emp_stat_situation_api.update_emp_stat_situation
(p_effective_date => TRUNC(p_reinstate_date)
,p_emp_stat_situation_id => l_iand_emp_stat_sit_id --Update this IAND rec
,p_statutory_situation_id => l_reinstate_sit_id
,p_provisional_start_date => TRUNC(p_reinstate_date)
,p_actual_start_date => TRUNC(p_reinstate_date)
,p_approval_flag => 'Y'
,p_comments => p_comments
,p_object_version_number => l_iand_ovn);
SELECT emp_stat_situation_id, person_id, statutory_situation_id,approval_flag,
actual_start_date,provisional_start_date, actual_end_date, provisional_end_date,
renewal_flag, renew_stat_situation_id, object_version_number
FROM pqh_fr_emp_stat_situations
WHERE emp_stat_situation_id = p_emp_stat_situation_id;
SELECT emp_stat_situation_id, person_id, statutory_situation_id, approval_flag,
actual_start_date, provisional_start_date,
actual_end_date, provisional_end_date, object_version_number
FROM pqh_fr_emp_stat_situations
WHERE renew_stat_situation_id = p_renew_emp_stat_sit_id
AND renewal_flag = 'Y'
AND NVL(actual_start_date,provisional_start_date)
= (SELECT MAX(NVL(actual_start_date,provisional_start_date))
FROM pqh_fr_emp_stat_situations
WHERE renew_stat_situation_id = p_renew_emp_stat_sit_id
AND renewal_flag = 'Y');
SELECT emp_stat_situation_id, statutory_situation_id, object_version_number,
provisional_end_date, actual_end_date
FROM pqh_fr_emp_stat_situations
WHERE person_id = p_person_id
AND statutory_situation_id IN(SELECT statutory_situation_id
FROM pqh_fr_stat_situations_v sit
,per_shared_types_vl sh
WHERE sh.shared_type_id = type_of_ps
AND sh.system_type_cd = NVL(PQH_FR_UTILITY.get_bg_type_of_ps,sh.system_type_cd)
AND sit.business_group_id = HR_GENERAL.get_business_group_id
AND sit.default_flag = 'Y'
AND sit.situation_type = 'IA'
AND sit.sub_type = 'IA_N'
AND TRUNC(p_end_date+1) BETWEEN
sit.date_from AND NVL(sit.date_to,HR_GENERAL.end_of_time))
AND TRUNC(NVL(actual_start_date,provisional_start_date)) = TRUNC(p_end_date+1);
pqh_fr_emp_stat_situation_api.update_emp_stat_situation
(p_effective_date => l_renew_start_date
,p_emp_stat_situation_id => l_rn_emp_stat_sit_id
,p_actual_end_date => TRUNC(l_renew_start_date)
,p_object_version_number => l_rn_ovn);
pqh_fr_emp_stat_situation_api.update_emp_stat_situation
(p_effective_date => TRUNC(l_renew_end_date+1)
,p_emp_stat_situation_id => l_iand_emp_stat_sit_id --Update this IAND rec
,p_statutory_situation_id => l_iand_stat_sit_id
,p_provisional_start_date => TRUNC(l_renew_end_date+1)
,p_actual_start_date => TRUNC(l_renew_end_date+1)
,p_approval_flag => 'Y'
,p_comments => p_comments
,p_object_version_number => l_iand_ovn);
pqh_fr_emp_stat_situation_api.update_assignments
(p_person_id => l_rn_person_id
,p_statutory_situation_id => l_iand_stat_sit_id
,p_start_date => TRUNC(l_renew_end_date+1)
,p_end_date => TRUNC(NVL(l_iand_act_end_date,NVL(l_iand_prov_end_date,HR_GENERAL.end_of_time))));
PROCEDURE delete_emp_stat_situation
(p_validate IN NUMBER DEFAULT HR_API.g_false_num
,p_emp_stat_situation_id IN NUMBER
,p_object_version_number IN NUMBER
,p_return_status OUT nocopy VARCHAR2
)
IS
--
--Cursor to get current Situation details
CURSOR csr_current_situation_dtls(p_emp_stat_situation_id IN NUMBER) IS
SELECT person_id, actual_start_date, provisional_start_date, actual_end_date, provisional_end_date
FROM pqh_fr_emp_stat_situations
WHERE emp_stat_situation_id = p_emp_stat_situation_id;
SELECT emp_stat_situation_id, statutory_situation_id, object_version_number,
provisional_start_date, actual_start_date, provisional_end_date, actual_end_date
FROM pqh_fr_emp_stat_situations
WHERE person_id = p_person_id
AND TRUNC(NVL(actual_end_date,provisional_end_date)) = TRUNC(p_date-1);
SELECT emp_stat_situation_id, statutory_situation_id, object_version_number,
provisional_end_date, actual_end_date
FROM pqh_fr_emp_stat_situations
WHERE person_id = p_person_id
AND TRUNC(NVL(actual_start_date,provisional_start_date)) = TRUNC(p_date+1);
l_proc VARCHAR2(72) := g_package||'delete_emp_stat_situation';
FND_MESSAGE.set_name('PQH','FR_PQH_PRIOR_SIT_NOT_FND'); --Cannot Update/Delete because no situation exists prior to this situation.
FND_MESSAGE.set_name('PQH','FR_PQH_NEXT_SIT_NOT_FND'); --Cannot Update/Delete because no situation exists after this situation.
pqh_fr_emp_stat_situation_api.delete_emp_stat_situation
(p_validate => l_validate
,p_emp_stat_situation_id => p_emp_stat_situation_id
,p_object_version_number => p_object_version_number);
pqh_fr_emp_stat_situation_api.delete_emp_stat_situation
(p_validate => l_validate
,p_emp_stat_situation_id => l_next_emp_stat_sit_id
,p_object_version_number => l_next_ovn);
pqh_fr_emp_stat_situation_api.update_emp_stat_situation
(p_effective_date => TRUNC(NVL(l_act_st_dt,l_prov_st_dt))
,p_emp_stat_situation_id => l_prior_emp_stat_sit_id --Update this IAND rec
,p_statutory_situation_id => l_prior_sit_id
,p_provisional_end_date => TRUNC(l_next_prov_end_date)
,p_actual_end_date => TRUNC(l_next_act_end_date)
,p_approval_flag => 'Y'
,p_object_version_number => l_prior_ovn);
pqh_fr_emp_stat_situation_api.update_emp_stat_situation
(p_effective_date => TRUNC(NVL(l_act_st_dt,l_prov_st_dt))
,p_emp_stat_situation_id => l_prior_emp_stat_sit_id --Update this IAND rec
,p_statutory_situation_id => l_prior_sit_id
,p_actual_end_date => TRUNC(l_act_end_dt)
,p_approval_flag => 'Y'
,p_object_version_number => l_prior_ovn);
pqh_fr_emp_stat_situation_api.update_emp_stat_situation
(p_effective_date => TRUNC(NVL(l_act_st_dt,l_prov_st_dt))
,p_emp_stat_situation_id => l_next_emp_stat_sit_id --Update this IAND rec
,p_statutory_situation_id => l_next_sit_id
,p_provisional_start_date => TRUNC(NVL(l_act_st_dt,l_prov_st_dt))
,p_actual_start_date => TRUNC(NVL(l_act_st_dt,l_prov_st_dt))
,p_approval_flag => 'Y'
,p_object_version_number => l_next_ovn);
pqh_fr_emp_stat_situation_api.update_emp_stat_situation
(p_effective_date => TRUNC(NVL(l_act_st_dt,l_prov_st_dt))
,p_emp_stat_situation_id => l_prior_emp_stat_sit_id --Update this IAND rec
,p_statutory_situation_id => l_prior_sit_id
,p_provisional_end_date => TRUNC(NVL(l_act_end_dt,l_prov_end_dt))
,p_actual_end_date => TRUNC(NVL(l_act_end_dt,l_prov_end_dt))
,p_approval_flag => 'Y'
,p_object_version_number => l_prior_ovn);
END delete_emp_stat_situation;
SELECT asg.assignment_id, asg.effective_start_date, asg.effective_end_date, ast.per_system_status
FROM per_all_assignments_f asg,
per_assignment_status_types ast
WHERE asg.person_id = p_person_id
AND(TRUNC(asg.effective_start_date) BETWEEN TRUNC(p_start_date) AND TRUNC(p_end_date)
OR TRUNC(asg.effective_end_date) BETWEEN TRUNC(p_start_date) AND TRUNC(p_end_date))
AND asg.primary_flag = 'Y'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
ORDER BY asg.effective_start_date;
pqh_fr_emp_stat_situation_api.update_assignments
(p_person_id => p_person_id
,p_statutory_situation_id => p_iand_stat_sit_id
,p_start_date => TRUNC(l_start_date)
,p_end_date => TRUNC(l_end_date));
pqh_fr_emp_stat_situation_api.update_assignments
(p_person_id => p_person_id
,p_statutory_situation_id => p_statutory_situation_id
,p_start_date => TRUNC(l_start_date)
,p_end_date => TRUNC(l_end_date));
pqh_fr_emp_stat_situation_api.update_assignments
(p_person_id => p_person_id
,p_statutory_situation_id => p_iand_stat_sit_id
,p_start_date => TRUNC(p_end_date+1)
,p_end_date => TRUNC(l_end_date));
pqh_fr_emp_stat_situation_api.update_assignments
(p_person_id => p_person_id
,p_statutory_situation_id => p_statutory_situation_id
,p_start_date => TRUNC(p_start_date)
,p_end_date => TRUNC(p_end_date));
pqh_fr_emp_stat_situation_api.update_assignments
(p_person_id => p_person_id
,p_statutory_situation_id => p_iand_stat_sit_id
,p_start_date => TRUNC(p_end_date+1));
SELECT statutory_situation_id
FROM pqh_fr_emp_stat_situations
WHERE person_id = p_person_id
AND p_effective_date BETWEEN NVL(actual_start_date,provisional_start_date)
AND NVL(actual_end_date,NVL(provisional_end_date,HR_GENERAL.end_of_time));