The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_entire_qua
(p_validate in boolean
,p_qualification_id in varchar2
,p_pq_object_version_number in varchar2
,p_attendance_id in varchar2
,p_pea_object_version_number in varchar2
,p_qua_subjects in SSHR_QUA_SUBJECT_TAB_TYP
) IS
--Bug#3236273
cursor subjects_for_qua(p_qualification_id in varchar2)is
select subjects_taken_id,object_version_number
from per_subjects_taken per
where per.qualification_id = p_qualification_id;
l_proc varchar2(72) := g_package||'delete_entire_qua';
savepoint delete_entire_qua;
PER_QUALIFICATIONS_API.DELETE_QUALIFICATION
(p_validate => p_validate
,p_qualification_id => p_qualification_id
,p_object_version_number => p_pq_object_version_number);
rollback to delete_entire_qua;
rollback to delete_entire_qua;
END delete_entire_qua;
l_selected_person_id number;
l_selected_person_id := to_number(
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SELECTED_PERSON_ID'));
IF l_qualifications(1).delete_flag = 'Y' THEN
hr_utility.set_location('l_qualifications(1).delete_flag = Y:'||l_proc,10);
delete_entire_qua
(p_validate => p_validate
,p_qualification_id => l_qualifications(1).qualification_id
,p_pq_object_version_number =>
l_qualifications(1).object_version_number
,p_attendance_id => l_qualifications(1).attendance_id
,p_pea_object_version_number =>
l_qua_attendance(1).object_version_number
,p_qua_subjects => l_qua_subjects);
hr_utility.set_location('l_qualifications(1).delete_flag != Y:'||l_proc,15);
,p_selected_person_id => l_selected_person_id
,p_qualifications => l_qualifications
,p_qua_subjects => l_qua_subjects
,p_qua_attendance => l_qua_attendance);
select *
from per_qualifications
where qualification_id=p_qualification_id;
select *
from per_establishment_attendances
where attendance_id = p_attendance_id;
select *
from per_subjects_taken
where subjects_taken_id = p_subject_taken_id;
select name
from per_establishments
where establishment_id = p_establishment_id;
p_qua_subjects(i).delete_flag = 'N' THEN
--a new subject is added
RETURN TRUE;
p_qua_subjects(i).delete_flag = 'Y' THEN
--a subject is deleted
RETURN TRUE;
p_qua_subjects(i).delete_flag = 'N' THEN
OPEN csr_subject(p_qua_subjects(i).subjects_taken_id);
procedure delete_transaction_step
(p_item_type in varchar2
,p_item_key in varchar2
,p_creator_person_id in number) is
cursor c_get_transaction_step_id(p_transaction_id number) is
select transaction_step_id
from hr_api_transaction_steps
where transaction_id = p_transaction_id;
l_proc varchar2(72) := g_package||'delete_transaction_step';
hr_transaction_ss.delete_transaction_step
(l_transaction_step_id,null,p_creator_person_id);
end delete_transaction_step;
SELECT
name
FROM
per_qualification_types
WHERE qualification_type_id = p_qualification_type_id;
,p_selected_person_id in varchar2
,p_mode in varchar2
,p_process_name in varchar2
,p_activity_name in varchar2
,p_qualifications out nocopy SSHR_QUA_TAB_TYP
,p_qua_attendance out nocopy SSHR_QUA_ATTENDANCE_TAB_TYP
,p_transaction_steps out nocopy SSHR_TRN_TAB_TYP
)
IS
l_transaction_step_id hr_util_misc_web.g_varchar2_tab_type;
l_selected_person_id number;
,p_selected_person_id => p_selected_person_id
,p_mode => p_mode
,p_process_name => p_process_name
,p_activity_name => p_activity_name
,p_transaction_step_id => l_transaction_step_id);
,p_selected_person_id in varchar2
,p_mode in varchar2
,p_process_name in varchar2
,p_activity_name in varchar2
,p_qualification_id in number
)
return varchar2 is
l_pending_found varchar2(1);
,p_selected_person_id => p_selected_person_id
,p_mode => p_mode
,p_process_name => p_process_name
,p_activity_name => p_activity_name
,p_transaction_step_id => l_transaction_step_id);
,p_selected_person_id in number
,p_item_type in varchar2
,p_item_key in varchar2
,p_act_id in varchar2
,p_proc_call in varchar2
,p_error_message in out nocopy varchar2
,p_subjects_error_message in out nocopy varchar2
,p_qualifications in SSHR_QUA_TAB_TYP
,p_qua_subjects in SSHR_QUA_SUBJECT_TAB_TYP
,p_qua_attendance in SSHR_QUA_ATTENDANCE_TAB_TYP) is
l_app_exception exception;
if p_qualifications(1).delete_flag is null or
p_qualifications(1).delete_flag = 'N' then
hr_utility.set_location('delete_flag = N:'||l_proc,15);
delete_transaction_step
(p_item_type => p_item_type
,p_item_key => p_item_key
,p_creator_person_id => p_creator_person_id);
if (p_qualifications(1).delete_flag is null or
p_qualifications(1).delete_flag = 'N')
and p_save_mode <> 'SAVE_FOR_LATER'
then
hr_utility.set_location('Delete flag = N and not SFL'||l_proc,35);
if (p_qualifications(1).delete_flag is null or
p_qualifications(1).delete_flag = 'N') and
p_save_mode <> 'SAVE_FOR_LATER' then
hr_utility.set_location('Delete flag = N and not SFL'||l_proc,40);
,p_selected_person_id => p_selected_person_id
,p_qualifications => p_qualifications
,p_qua_subjects => p_qua_subjects
,p_qua_attendance => p_qua_attendance
);
if l_changed = TRUE or p_qualifications(1).delete_flag = 'Y' THEN
hr_utility.set_location('Delete flag = Y or changed=True'||l_proc,40);
,p_selected_person_id => p_selected_person_id
,p_qualifications => p_qualifications
,p_qua_subjects => p_qua_subjects
,p_qua_attendance => p_qua_attendance
,p_proc_call => p_proc_call);
,p_selected_person_id in number
,p_qualifications in SSHR_QUA_TAB_TYP
,p_qua_subjects in SSHR_QUA_SUBJECT_TAB_TYP
,p_qua_attendance in SSHR_QUA_ATTENDANCE_TAB_TYP
,p_proc_call in varchar2 ) IS
l_result VARCHAR2(100);
,p_selected_person_id => p_qualifications(1).person_id
,p_creator_person_id => p_creator_person_id
,result => l_result);
,p_name => 'P_SELECTED_PERSON_ID'
,p_value => p_selected_person_id);
,p_name => 'P_DELETE_FLAG'
,p_value => p_qualifications(1).delete_flag);
,p_name => 'P_PST_DELETE_FLAG'||i
,p_value => p_qua_subjects(i).delete_flag);
,p_selected_person_id in number
,p_qualifications in SSHR_QUA_TAB_TYP
,p_qua_subjects in SSHR_QUA_SUBJECT_TAB_TYP
,p_qua_attendance in SSHR_QUA_ATTENDANCE_TAB_TYP
) IS
CURSOR csr_school_changed(p_establishment in varchar2
,p_attendance_id in number
,p_establishment_id in number)
IS
SELECT null
FROM
per_establishment_attendances
WHERE
attendance_id = p_attendance_id
AND NVL(establishment_id,-1) = NVL(p_establishment_id,-1)
AND NVL(UPPER(establishment),-1) = NVL(UPPER(p_establishment),-1);
,p_selected_person_id in number)
IS
SELECT attendance_id
FROM
per_establishment_attendances
WHERE
PERSON_ID = p_selected_person_id
AND (attended_start_date is null
and p_start_date is null
or attended_start_date = p_start_date)
AND business_group_id = p_business_group_id
AND (p_establishment_id is not null
AND establishment_id = p_establishment_id
OR p_establishment is not null
AND UPPER(establishment) = UPPER(p_establishment));
SELECT
start_date
,end_date
FROM
per_qualifications
WHERE
qualification_id = p_qualification_id;
,p_selected_person_id in number) is
select
null
from
per_establishment_attendances per,
per_qualifications pq
where
per.person_id = p_selected_person_id
and (p_establishment is not null
and UPPER(per.establishment) = UPPER(p_establishment)
or p_school_id is not null and per.establishment_id is not null
and per.establishment_id = p_school_id)
and per.attendance_id <> nvl(p_attendance_id,-1)
and (p_attended_start_date
between per.attended_start_date
and nvl(per.attended_end_date,hr_api.g_eot)
or nvl(p_attended_end_date,hr_api.g_eot)
between per.attended_start_date
and nvl(per.attended_end_date,hr_api.g_eot))
and per.attendance_id = pq.attendance_id
and pq.qualification_type_id = p_qualification_type_id
--and nvl(pq.party_id, -1) = nvl(p_party_id,-1)
and nvl(pq.title,-1) = nvl(p_title,-1);
select establishment_id
from per_establishments
where upper(name) = upper(p_establishment);
select count(*)
from per_qualifications
where attendance_id = p_attendance_id;
,p_selected_person_id => p_selected_person_id);
,p_selected_person_id => p_selected_person_id);
,p_person_id => p_selected_person_id
,p_establishment_id => l_establishment_id
,p_establishment =>
decode_value
(p_expression => l_establishment_id IS NOT null
,p_true => null
,p_false => p_qua_attendance(1).establishment)
,p_attended_start_date => p_qua_attendance(1).attended_start_date
,p_attended_end_date => p_qua_attendance(1).attended_end_date
,p_full_time => NVL(p_qua_attendance(1).full_time,'N')
,p_attribute_category => p_qua_attendance(1).attribute_category
,p_attribute1 => p_qua_attendance(1).attribute1
,p_attribute2 => p_qua_attendance(1).attribute2
,p_attribute3 => p_qua_attendance(1).attribute3
,p_attribute4 => p_qua_attendance(1).attribute4
,p_attribute5 => p_qua_attendance(1).attribute5
,p_attribute6 => p_qua_attendance(1).attribute6
,p_attribute7 => p_qua_attendance(1).attribute7
,p_attribute8 => p_qua_attendance(1).attribute8
,p_attribute9 => p_qua_attendance(1).attribute9
,p_attribute10 => p_qua_attendance(1).attribute10
,p_attribute11 => p_qua_attendance(1).attribute11
,p_attribute12 => p_qua_attendance(1).attribute12
,p_attribute13 => p_qua_attendance(1).attribute13
,p_attribute14 => p_qua_attendance(1).attribute14
,p_attribute15 => p_qua_attendance(1).attribute15
,p_attribute16 => p_qua_attendance(1).attribute16
,p_attribute17 => p_qua_attendance(1).attribute17
,p_attribute18 => p_qua_attendance(1).attribute18
,p_attribute19 => p_qua_attendance(1).attribute19
,p_attribute20 => p_qua_attendance(1).attribute20
,p_object_version_number => l_pea_object_version_number);
end if; --end insert attendance
,p_false => p_selected_person_id)
,p_title => p_qualifications(1).title
,p_status => p_qualifications(1).status
,p_start_date => p_qualifications(1).start_date
,p_end_date => p_qualifications(1).end_date
--,p_party_id => p_qualifications(1).party_id
,p_attendance_id => l_attendance_id
,p_grade_attained => p_qualifications(1).grade_attained
,p_awarded_date => p_qualifications(1).awarded_date
,p_fee => p_qualifications(1).fee
,p_fee_currency => p_qualifications(1).fee_currency
,p_training_completed_amount =>
p_qualifications(1).training_completed_amount
,p_reimbursement_arrangements =>
p_qualifications(1).reimbursement_arrangements
,p_training_completed_units =>
p_qualifications(1).training_completed_units
,p_total_training_amount => p_qualifications(1).total_training_amount
,p_license_number => p_qualifications(1).license_number
,p_expiry_date => p_qualifications(1).expiry_date
,p_license_restrictions => p_qualifications(1).license_restrictions
,p_awarding_body => p_qualifications(1).awarding_body
,p_tuition_method => p_qualifications(1).tuition_method
,p_group_ranking => p_qualifications(1).group_ranking
,p_comments => substr(p_qualifications(1).comments,1,2000)
,p_attribute_category => p_qualifications(1).attribute_category
,p_attribute1 => p_qualifications(1).attribute1
,p_attribute2 => p_qualifications(1).attribute2
,p_attribute3 => p_qualifications(1).attribute3
,p_attribute4 => p_qualifications(1).attribute4
,p_attribute5 => p_qualifications(1).attribute5
,p_attribute6 => p_qualifications(1).attribute6
,p_attribute7 => p_qualifications(1).attribute7
,p_attribute8 => p_qualifications(1).attribute8
,p_attribute9 => p_qualifications(1).attribute9
,p_attribute10 => p_qualifications(1).attribute10
,p_attribute11 => p_qualifications(1).attribute11
,p_attribute12 => p_qualifications(1).attribute12
,p_attribute13 => p_qualifications(1).attribute13
,p_attribute14 => p_qualifications(1).attribute14
,p_attribute15 => p_qualifications(1).attribute15
,p_attribute16 => p_qualifications(1).attribute16
,p_attribute17 => p_qualifications(1).attribute17
,p_attribute18 => p_qualifications(1).attribute18
,p_attribute19 => p_qualifications(1).attribute19
,p_attribute20 => p_qualifications(1).attribute20
,p_qua_information_category => p_qualifications(1).qua_information_category
,p_qua_information1 => p_qualifications(1).qua_information1
,p_qua_information2 => p_qualifications(1).qua_information2
,p_qua_information3 => p_qualifications(1).qua_information3
,p_qua_information4 => p_qualifications(1).qua_information4
,p_qua_information5 => p_qualifications(1).qua_information5
,p_qua_information6 => p_qualifications(1).qua_information6
,p_qua_information7 => p_qualifications(1).qua_information7
,p_qua_information8 => p_qualifications(1).qua_information8
,p_qua_information9 => p_qualifications(1).qua_information9
,p_qua_information10 => p_qualifications(1).qua_information10
,p_qua_information11 => p_qualifications(1).qua_information11
,p_qua_information12 => p_qualifications(1).qua_information12
,p_qua_information13 => p_qualifications(1).qua_information13
,p_qua_information14 => p_qualifications(1).qua_information14
,p_qua_information15 => p_qualifications(1).qua_information15
,p_qua_information16 => p_qualifications(1).qua_information16
,p_qua_information17 => p_qualifications(1).qua_information17
,p_qua_information18 => p_qualifications(1).qua_information18
,p_qua_information19 => p_qualifications(1).qua_information19
,p_qua_information20 => p_qualifications(1).qua_information20
);
if p_qua_subjects(i).delete_flag = 'N' then
per_sub_ins.ins
(p_validate => FALSE
,p_effective_date => sysdate
,p_subjects_taken_id => l_subjects_taken_id
,p_start_date => p_qua_subjects(i).start_date
,p_end_date => p_qua_subjects(i).end_date
,p_major => p_qua_subjects(i).major
,p_subject_status => p_qua_subjects(i).subject_status
,p_subject => p_qua_subjects(i).subject
,p_grade_attained => p_qua_subjects(i).grade_attained
,p_qualification_id => l_qualification_id
,p_object_version_number => l_pst_object_version_number
,p_attribute_category => p_qua_subjects(i).attribute_category
,p_attribute1 => p_qua_subjects(i).attribute1
,p_attribute2 => p_qua_subjects(i).attribute2
,p_attribute3 => p_qua_subjects(i).attribute3
,p_attribute4 => p_qua_subjects(i).attribute4
,p_attribute5 => p_qua_subjects(i).attribute5
,p_attribute6 => p_qua_subjects(i).attribute6
,p_attribute7 => p_qua_subjects(i).attribute7
,p_attribute8 => p_qua_subjects(i).attribute8
,p_attribute9 => p_qua_subjects(i).attribute9
,p_attribute10 => p_qua_subjects(i).attribute10
,p_attribute11 => p_qua_subjects(i).attribute11
,p_attribute12 => p_qua_subjects(i).attribute12
,p_attribute13 => p_qua_subjects(i).attribute13
,p_attribute14 => p_qua_subjects(i).attribute14
,p_attribute15 => p_qua_subjects(i).attribute15
,p_attribute16 => p_qua_subjects(i).attribute16
,p_attribute17 => p_qua_subjects(i).attribute17
,p_attribute18 => p_qua_subjects(i).attribute18
,p_attribute19 => p_qua_subjects(i).attribute19
,p_attribute20 => p_qua_subjects(i).attribute20
,p_sub_information_category => p_qua_subjects(i).sub_information_category
,p_sub_information1 => p_qua_subjects(i).sub_information1
,p_sub_information2 => p_qua_subjects(i).sub_information2
,p_sub_information3 => p_qua_subjects(i).sub_information3
,p_sub_information4 => p_qua_subjects(i).sub_information4
,p_sub_information5 => p_qua_subjects(i).sub_information5
,p_sub_information6 => p_qua_subjects(i).sub_information6
,p_sub_information7 => p_qua_subjects(i).sub_information7
,p_sub_information8 => p_qua_subjects(i).sub_information8
,p_sub_information9 => p_qua_subjects(i).sub_information9
,p_sub_information10 => p_qua_subjects(i).sub_information10
,p_sub_information11 => p_qua_subjects(i).sub_information11
,p_sub_information12 => p_qua_subjects(i).sub_information12
,p_sub_information13 => p_qua_subjects(i).sub_information13
,p_sub_information14 => p_qua_subjects(i).sub_information14
,p_sub_information15 => p_qua_subjects(i).sub_information15
,p_sub_information16 => p_qua_subjects(i).sub_information16
,p_sub_information17 => p_qua_subjects(i).sub_information17
,p_sub_information18 => p_qua_subjects(i).sub_information18
,p_sub_information19 => p_qua_subjects(i).sub_information19
,p_sub_information20 => p_qua_subjects(i).sub_information20
);
else --update an existing qua or award
l_pq_object_version_number := p_qualifications(1).object_version_number;
,p_person_id => p_selected_person_id
,p_establishment_id => l_establishment_id
,p_establishment => decode_value(p_expression =>
l_establishment_id IS NOT null
,p_true => null
,p_false => p_qua_attendance(1).establishment)
,p_attended_start_date => p_qua_attendance(1).attended_start_date
,p_attended_end_date => p_qua_attendance(1).attended_end_date
,p_full_time => NVL(p_qua_attendance(1).full_time,'N')
,p_attribute_category => p_qua_attendance(1).attribute_category
,p_attribute1 => p_qua_attendance(1).attribute1
,p_attribute2 => p_qua_attendance(1).attribute2
,p_attribute3 => p_qua_attendance(1).attribute3
,p_attribute4 => p_qua_attendance(1).attribute4
,p_attribute5 => p_qua_attendance(1).attribute5
,p_attribute6 => p_qua_attendance(1).attribute6
,p_attribute7 => p_qua_attendance(1).attribute7
,p_attribute8 => p_qua_attendance(1).attribute8
,p_attribute9 => p_qua_attendance(1).attribute9
,p_attribute10 => p_qua_attendance(1).attribute10
,p_attribute11 => p_qua_attendance(1).attribute11
,p_attribute12 => p_qua_attendance(1).attribute12
,p_attribute13 => p_qua_attendance(1).attribute13
,p_attribute14 => p_qua_attendance(1).attribute14
,p_attribute15 => p_qua_attendance(1).attribute15
,p_attribute16 => p_qua_attendance(1).attribute16
,p_attribute17 => p_qua_attendance(1).attribute17
,p_attribute18 => p_qua_attendance(1).attribute18
,p_attribute19 => p_qua_attendance(1).attribute19
,p_attribute20 => p_qua_attendance(1).attribute20
,p_object_version_number => l_pea_object_version_number);
end if; --end update attendance
PER_QUALIFICATIONS_API.UPDATE_QUALIFICATION
(p_validate => FALSE
,p_effective_date => sysdate
,p_qualification_id => p_qualifications(1).qualification_id
--,p_business_group_id => p_qualifications(1).business_group_id
,p_object_version_number =>
l_pq_object_version_number
--,p_person_id => decode_value(p_expression =>
-- l_attendance_id IS null
--,p_true => p_qualifications(1).person_id
--,p_false => null)
,p_title => p_qualifications(1).title
,p_status => p_qualifications(1).status
,p_start_date => p_qualifications(1).start_date
,p_end_date => p_qualifications(1).end_date
,p_attendance_id => l_attendance_id
,p_grade_attained => p_qualifications(1).grade_attained
,p_awarded_date => p_qualifications(1).awarded_date
,p_fee => p_qualifications(1).fee
,p_fee_currency => p_qualifications(1).fee_currency
,p_training_completed_amount =>
p_qualifications(1).training_completed_amount
,p_reimbursement_arrangements =>
p_qualifications(1).reimbursement_arrangements
,p_training_completed_units =>
p_qualifications(1).training_completed_units
,p_total_training_amount =>
p_qualifications(1).total_training_amount
,p_license_number => p_qualifications(1).license_number
,p_expiry_date => p_qualifications(1).expiry_date
,p_license_restrictions => p_qualifications(1).license_restrictions
,p_awarding_body => p_qualifications(1).awarding_body
,p_tuition_method => p_qualifications(1).tuition_method
,p_group_ranking => p_qualifications(1).group_ranking
,p_comments => substr(p_qualifications(1).comments,1,2000)
,p_attribute_category => p_qualifications(1).attribute_category
,p_attribute1 => p_qualifications(1).attribute1
,p_attribute2 => p_qualifications(1).attribute2
,p_attribute3 => p_qualifications(1).attribute3
,p_attribute4 => p_qualifications(1).attribute4
,p_attribute5 => p_qualifications(1).attribute5
,p_attribute6 => p_qualifications(1).attribute6
,p_attribute7 => p_qualifications(1).attribute7
,p_attribute8 => p_qualifications(1).attribute8
,p_attribute9 => p_qualifications(1).attribute9
,p_attribute10 => p_qualifications(1).attribute10
,p_attribute11 => p_qualifications(1).attribute11
,p_attribute12 => p_qualifications(1).attribute12
,p_attribute13 => p_qualifications(1).attribute13
,p_attribute14 => p_qualifications(1).attribute14
,p_attribute15 => p_qualifications(1).attribute15
,p_attribute16 => p_qualifications(1).attribute16
,p_attribute17 => p_qualifications(1).attribute17
,p_attribute18 => p_qualifications(1).attribute18
,p_attribute19 => p_qualifications(1).attribute19
,p_attribute20 => p_qualifications(1).attribute20
,p_qua_information_category => p_qualifications(1).qua_information_category
,p_qua_information1 => p_qualifications(1).qua_information1
,p_qua_information2 => p_qualifications(1).qua_information2
,p_qua_information3 => p_qualifications(1).qua_information3
,p_qua_information4 => p_qualifications(1).qua_information4
,p_qua_information5 => p_qualifications(1).qua_information5
,p_qua_information6 => p_qualifications(1).qua_information6
,p_qua_information7 => p_qualifications(1).qua_information7
,p_qua_information8 => p_qualifications(1).qua_information8
,p_qua_information9 => p_qualifications(1).qua_information9
,p_qua_information10 => p_qualifications(1).qua_information10
,p_qua_information11 => p_qualifications(1).qua_information11
,p_qua_information12 => p_qualifications(1).qua_information12
,p_qua_information13 => p_qualifications(1).qua_information13
,p_qua_information14 => p_qualifications(1).qua_information14
,p_qua_information15 => p_qualifications(1).qua_information15
,p_qua_information16 => p_qualifications(1).qua_information16
,p_qua_information17 => p_qualifications(1).qua_information17
,p_qua_information18 => p_qualifications(1).qua_information18
,p_qua_information19 => p_qualifications(1).qua_information19
,p_qua_information20 => p_qualifications(1).qua_information20
);
IF p_qua_subjects(i).delete_flag = 'N' THEN
IF l_subjects_taken_id IS null THEN
--insert subject
per_sub_ins.ins
(p_validate => FALSE
,p_effective_date => sysdate
,p_subjects_taken_id => l_subjects_taken_id
,p_start_date => p_qua_subjects(i).start_date
,p_end_date => p_qua_subjects(i).end_date
,p_major => p_qua_subjects(i).major
,p_subject_status => p_qua_subjects(i).subject_status
,p_subject => p_qua_subjects(i).subject
,p_grade_attained => p_qua_subjects(i).grade_attained
,p_qualification_id => p_qualifications(1).qualification_id
,p_object_version_number => l_pst_object_version_number
,p_attribute_category => p_qua_subjects(i).attribute_category
,p_attribute1 => p_qua_subjects(i).attribute1
,p_attribute2 => p_qua_subjects(i).attribute2
,p_attribute3 => p_qua_subjects(i).attribute3
,p_attribute4 => p_qua_subjects(i).attribute4
,p_attribute5 => p_qua_subjects(i).attribute5
,p_attribute6 => p_qua_subjects(i).attribute6
,p_attribute7 => p_qua_subjects(i).attribute7
,p_attribute8 => p_qua_subjects(i).attribute8
,p_attribute9 => p_qua_subjects(i).attribute9
,p_attribute10 => p_qua_subjects(i).attribute10
,p_attribute11 => p_qua_subjects(i).attribute11
,p_attribute12 => p_qua_subjects(i).attribute12
,p_attribute13 => p_qua_subjects(i).attribute13
,p_attribute14 => p_qua_subjects(i).attribute14
,p_attribute15 => p_qua_subjects(i).attribute15
,p_attribute16 => p_qua_subjects(i).attribute16
,p_attribute17 => p_qua_subjects(i).attribute17
,p_attribute18 => p_qua_subjects(i).attribute18
,p_attribute19 => p_qua_subjects(i).attribute19
,p_attribute20 => p_qua_subjects(i).attribute20
,p_sub_information_category => p_qua_subjects(i).sub_information_category
,p_sub_information1 => p_qua_subjects(i).sub_information1
,p_sub_information2 => p_qua_subjects(i).sub_information2
,p_sub_information3 => p_qua_subjects(i).sub_information3
,p_sub_information4 => p_qua_subjects(i).sub_information4
,p_sub_information5 => p_qua_subjects(i).sub_information5
,p_sub_information6 => p_qua_subjects(i).sub_information6
,p_sub_information7 => p_qua_subjects(i).sub_information7
,p_sub_information8 => p_qua_subjects(i).sub_information8
,p_sub_information9 => p_qua_subjects(i).sub_information9
,p_sub_information10 => p_qua_subjects(i).sub_information10
,p_sub_information11 => p_qua_subjects(i).sub_information11
,p_sub_information12 => p_qua_subjects(i).sub_information12
,p_sub_information13 => p_qua_subjects(i).sub_information13
,p_sub_information14 => p_qua_subjects(i).sub_information14
,p_sub_information15 => p_qua_subjects(i).sub_information15
,p_sub_information16 => p_qua_subjects(i).sub_information16
,p_sub_information17 => p_qua_subjects(i).sub_information17
,p_sub_information18 => p_qua_subjects(i).sub_information18
,p_sub_information19 => p_qua_subjects(i).sub_information19
,p_sub_information20 => p_qua_subjects(i).sub_information20
);
,p_selected_person_id in number
,p_creator_person_id in number
,result out nocopy varchar2) is
-- --------------------------------------------------------------------------
-- declare local variables
-- --------------------------------------------------------------------------
l_transaction_privilege hr_api_transactions.transaction_privilege%type;
select
transaction_step_id
from
hr_api_transaction_steps
where
transaction_id = l_transaction_id;
hr_transaction_ss.delete_transaction_step
(l_transaction_step_id,null,p_creator_person_id);
,p_selected_person_id => p_selected_person_id
,p_login_person_id => p_creator_person_id
,result => result);
select transaction_step_id, activity_id, t.item_key
from hr_api_transactions t, hr_api_transaction_steps ts
,wf_item_activity_statuses s
where t.selected_person_id = p_current_person_id
and t.status = 'Y'
and t.item_type = p_item_type
and t.transaction_id = ts.transaction_id
and ts.api_name = p_api_name
and t.item_type = s.item_type
and t.item_key = s.item_key
and s.activity_result_code = p_result_code;
,p_selected_person_id in varchar2
,p_mode in varchar2
,p_process_name in varchar2
,p_activity_name in varchar2
,p_transaction_step_id out nocopy hr_util_misc_web.g_varchar2_tab_type) IS
l_active_item_keys hr_workflow_service.active_wf_trans_items_list;
,p_current_person_id => p_selected_person_id
,p_result_code => l_result_code
);
select null
from per_qualifications per
where per.qualification_type_id = p_qualification_type_id
--and nvl(per.party_id,-1) = nvl(p_party_id,-1)
and nvl(per.person_id,-1) = nvl(p_person_id,-1)
and nvl(per.attendance_id,-1) = nvl(p_attendance_id,-1)
and per.business_group_id +0 = p_business_group_id
and per.title = p_title
and per.qualification_id <> nvl(p_qualification_id,-1)
and (nvl(per.start_date,hr_api.g_sot)
between nvl(p_start_date,hr_api.g_sot)
and nvl(p_end_date,hr_api.g_eot)
or nvl(per.end_date,nvl(per.start_date,p_start_date))
between nvl(p_start_date,hr_api.g_sot)
and nvl(p_end_date,hr_api.g_eot));
IF p_qua_subjects(i).delete_flag = 'N' THEN
l_error := FALSE;
,p_name => 'P_DELETE_FLAG'),
to_number(hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_BUSINESS_GROUP_ID')),
to_number(hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_OBJECT_VERSION_NUMBER')),
to_number(hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_PERSON_ID')),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_TITLE'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_GRADE_ATTAINED'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_STATUS'),
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_AWARDED_DATE'),
to_number(hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_FEE')),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_FEE_CURRENCY'),
to_number(hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_COMPLETED_AMOUNT')),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_REIMBURSEMENT'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_COMPLETED_UNITS'),
to_number(hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_TOTAL_AMOUNT')),
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_START_DATE'),
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_END_DATE'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_LICENSE_NUMBER'),
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_EXPIRY_DATE'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_LICENSE_RESTRICTIONS'),
null, --PROJECTED_COMPLETION_DATE
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_AWARDING_BODY'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_TUITION_METHOD'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_GROUP_RANKING'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_COMMENTS'),
to_number(hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_QUALIFICATION_TYPE_ID')),
to_number(hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_ATTENDANCE_ID')),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_ATTRIBUTE_CATEGORY'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_ATTRIBUTE1'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_ATTRIBUTE2'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_ATTRIBUTE3'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_ATTRIBUTE4'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_ATTRIBUTE5'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_ATTRIBUTE6'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_ATTRIBUTE7'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_ATTRIBUTE8'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_ATTRIBUTE9'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_ATTRIBUTE10'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_ATTRIBUTE11'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_ATTRIBUTE12'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_ATTRIBUTE13'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_ATTRIBUTE14'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_ATTRIBUTE15'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_ATTRIBUTE16'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_ATTRIBUTE17'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_ATTRIBUTE18'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_ATTRIBUTE19'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_ATTRIBUTE20'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_QUA_INFORMATION_CATEGORY'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_QUA_INFORMATION1'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_QUA_INFORMATION2'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_QUA_INFORMATION3'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_QUA_INFORMATION4'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_QUA_INFORMATION5'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_QUA_INFORMATION6'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_QUA_INFORMATION7'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_QUA_INFORMATION8'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_QUA_INFORMATION9'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_QUA_INFORMATION10'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_QUA_INFORMATION11'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_QUA_INFORMATION12'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_QUA_INFORMATION13'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_QUA_INFORMATION14'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_QUA_INFORMATION15'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_QUA_INFORMATION16'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_QUA_INFORMATION17'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_QUA_INFORMATION18'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_QUA_INFORMATION19'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_QUA_INFORMATION20'),
null,null,null,null,
/*hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_PROFESSIONAL_BODY_NAME'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_MEMBERSHIP_NUMBER'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_MEMBERSHIP_CATEGORY'),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PQ_SUBS_PAYMENT_METHOD'),*/
hr_transaction_api.get_number_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PARTY_ID')
);
,p_name => 'P_PST_DELETE_FLAG'||i),
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PST_START_DATE'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PST_MAJOR'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PST_SUBJECT_STATUS'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PST_SUBJECT'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PST_GRADE_ATTAINED'||i),
hr_transaction_api.get_date_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PST_END_DATE'||i),
hr_transaction_api.get_number_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_QUALIFICATION_ID'),
to_number(hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PST_OBJECT_VERSION_NUMBER'||i)),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_PST_ATTRIBUTE_CATEGORY'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_ATTRIBUTE1_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_ATTRIBUTE2_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_ATTRIBUTE3_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_ATTRIBUTE4_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_ATTRIBUTE5_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_ATTRIBUTE6_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_ATTRIBUTE7_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_ATTRIBUTE8_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_ATTRIBUTE9_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_ATTRIBUTE10_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_ATTRIBUTE11_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_ATTRIBUTE12_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_ATTRIBUTE13_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_ATTRIBUTE14_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_ATTRIBUTE15_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_ATTRIBUTE16_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_ATTRIBUTE17_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_ATTRIBUTE18_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_ATTRIBUTE19_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_ATTRIBUTE20_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SUB_INFORMATION_CATEGORY'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SUB_INFORMATION1_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SUB_INFORMATION2_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SUB_INFORMATION3_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SUB_INFORMATION4_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SUB_INFORMATION5_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SUB_INFORMATION6_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SUB_INFORMATION7_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SUB_INFORMATION8_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SUB_INFORMATION9_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SUB_INFORMATION10_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SUB_INFORMATION11_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SUB_INFORMATION12_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SUB_INFORMATION13_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SUB_INFORMATION14_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SUB_INFORMATION15_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SUB_INFORMATION16_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SUB_INFORMATION17_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SUB_INFORMATION18_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SUB_INFORMATION19_PST'||i),
hr_transaction_api.get_varchar2_value
(p_transaction_step_id => p_transaction_step_id
,p_name => 'P_SUB_INFORMATION20_PST'||i)
);
delete from hr_api_transaction_values where transaction_step_id = p_transaction_step_id;
delete from hr_api_transaction_steps where transaction_step_id = p_transaction_step_id;
This method is used to delete the data from the transaction tables for a given transaction step id
*/
Procedure delete_transaction_step ( p_transaction_step_id in number,
p_creator_person_id in number ) IS
l_proc varchar2(72) := g_package||'delete_transaction_step';
hr_transaction_ss.delete_transaction_step(p_transaction_step_id,null,p_creator_person_id);