[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure deleteForumNotification(l_event_id in number
,l_person_id in number
,l_contact_id in number) is
--
-- Set up local variables
--
cursor c_get_forum_id is
select fns.forum_id,fns.object_version_number
from ota_frm_obj_inclusions foi,ota_frm_notif_subscribers fns
where foi.object_id = l_event_id
and foi.object_Type = 'E'
and foi.forum_id = fns.forum_id
and (fns.person_id = l_person_id or fns.contact_id = l_contact_id);
l_proc := g_package||'deleteForumNotification';
end deleteForumNotification;
Select fr.forum_id, fr.business_group_id from
ota_forums_b fr,
ota_frm_obj_inclusions foi
where fr.forum_id = foi.forum_id
and foi.object_type = 'E'
and foi.object_id = l_event_id
and fr.auto_notification_flag = 'Y';
select Type into l_type from ota_booking_status_types where booking_status_type_id=l_booking_status_type_id;
SELECT evt.course_start_time
,evt.course_start_date
,evt.event_id
FROM ota_events evt,
ota_delegate_bookings tdb
WHERE tdb.event_id = evt.event_id
AND tdb.booking_id = p_booking_id;
SELECT 1
FROM ota_delegate_bookings tdb
,ota_booking_status_types bst
WHERE tdb.booking_status_type_id = bst.booking_status_type_id
AND bst.type = 'W'
AND tdb.event_id = p_event_id;
procedure Update_Enrollment
(
p_booking_id in number,
p_booking_status_type_id in number default hr_api.g_number,
p_delegate_person_id in number default hr_api.g_number,
p_contact_id in number default hr_api.g_number,
p_business_group_id in number default hr_api.g_number,
p_event_id in number default hr_api.g_number,
p_customer_id in number default hr_api.g_number,
p_authorizer_person_id in number default hr_api.g_number,
p_date_booking_placed in date default hr_api.g_date,
p_corespondent in varchar2 default hr_api.g_varchar2,
p_internal_booking_flag in varchar2 default hr_api.g_varchar2,
p_number_of_places in number default hr_api.g_number,
p_object_version_number in out nocopy number,
p_administrator in number default hr_api.g_number,
p_booking_priority in varchar2 default hr_api.g_varchar2,
p_comments in varchar2 default hr_api.g_varchar2,
p_contact_address_id in number default hr_api.g_number,
p_delegate_contact_phone in varchar2 default hr_api.g_varchar2,
p_delegate_contact_fax in varchar2 default hr_api.g_varchar2,
p_third_party_customer_id in number default hr_api.g_number,
p_third_party_contact_id in number default hr_api.g_number,
p_third_party_address_id in number default hr_api.g_number,
p_third_party_contact_phone in varchar2 default hr_api.g_varchar2,
p_third_party_contact_fax in varchar2 default hr_api.g_varchar2,
p_date_status_changed in date default hr_api.g_date,
p_status_change_comments in varchar2 default hr_api.g_varchar2,
p_failure_reason in varchar2 default hr_api.g_varchar2,
p_attendance_result in varchar2 default hr_api.g_varchar2,
p_language_id in number default hr_api.g_number,
p_source_of_booking in varchar2 default hr_api.g_varchar2,
p_special_booking_instructions in varchar2 default hr_api.g_varchar2,
p_successful_attendance_flag in varchar2 default hr_api.g_varchar2,
p_tdb_information_category in varchar2 default hr_api.g_varchar2,
p_tdb_information1 in varchar2 default hr_api.g_varchar2,
p_tdb_information2 in varchar2 default hr_api.g_varchar2,
p_tdb_information3 in varchar2 default hr_api.g_varchar2,
p_tdb_information4 in varchar2 default hr_api.g_varchar2,
p_tdb_information5 in varchar2 default hr_api.g_varchar2,
p_tdb_information6 in varchar2 default hr_api.g_varchar2,
p_tdb_information7 in varchar2 default hr_api.g_varchar2,
p_tdb_information8 in varchar2 default hr_api.g_varchar2,
p_tdb_information9 in varchar2 default hr_api.g_varchar2,
p_tdb_information10 in varchar2 default hr_api.g_varchar2,
p_tdb_information11 in varchar2 default hr_api.g_varchar2,
p_tdb_information12 in varchar2 default hr_api.g_varchar2,
p_tdb_information13 in varchar2 default hr_api.g_varchar2,
p_tdb_information14 in varchar2 default hr_api.g_varchar2,
p_tdb_information15 in varchar2 default hr_api.g_varchar2,
p_tdb_information16 in varchar2 default hr_api.g_varchar2,
p_tdb_information17 in varchar2 default hr_api.g_varchar2,
p_tdb_information18 in varchar2 default hr_api.g_varchar2,
p_tdb_information19 in varchar2 default hr_api.g_varchar2,
p_tdb_information20 in varchar2 default hr_api.g_varchar2,
p_update_finance_line in varchar2 default 'N',
p_tfl_object_version_number in out nocopy number,
p_finance_header_id in number default hr_api.g_number,
p_finance_line_id in out nocopy number,
p_standard_amount in number default hr_api.g_number,
p_unitary_amount in number default hr_api.g_number,
p_money_amount in number default hr_api.g_number,
p_currency_code in varchar2 default hr_api.g_varchar2,
p_booking_deal_type in varchar2 default hr_api.g_varchar2,
p_booking_deal_id in number default hr_api.g_number,
p_enrollment_type in varchar2 default hr_api.g_varchar2,
p_validate in boolean default false,
p_organization_id in number default hr_api.g_number,
p_sponsor_person_id in number default hr_api.g_number,
p_sponsor_assignment_id in number default hr_api.g_number,
p_person_address_id in number default hr_api.g_number,
p_delegate_assignment_id in number default hr_api.g_number,
p_delegate_contact_id in number default hr_api.g_number,
p_delegate_contact_email in varchar2 default hr_api.g_varchar2,
p_third_party_email in varchar2 default hr_api.g_varchar2,
p_person_address_type in varchar2 default hr_api.g_varchar2,
p_line_id in number default hr_api.g_number,
p_org_id in number default hr_api.g_number,
p_daemon_flag in varchar2 default hr_api.g_varchar2,
p_daemon_type in varchar2 default hr_api.g_varchar2,
p_old_event_id in number default hr_api.g_number,
p_quote_line_id in number default hr_api.g_number,
p_interface_source in varchar2 default hr_api.g_varchar2,
p_total_training_time in varchar2 default hr_api.g_varchar2,
p_content_player_status in varchar2 default hr_api.g_varchar2,
p_score in number default hr_api.g_number,
p_completed_content in number default hr_api.g_number,
p_total_content in number default hr_api.g_number,
p_booking_justification_id in number default hr_api.g_number,
p_source_cancel in varchar2,
p_override_prerequisites in varchar2 default 'N',
p_is_history_flag in varchar2 default hr_api.g_varchar2
,p_override_learner_access in varchar2 default 'N',
p_sign_eval_status in varchar2 default null)
is
l_proc varchar2(72) := g_package || ' ' || 'create_enrollment';
Select contact_id,delegate_contact_id from
Ota_delegate_bookings
where booking_id= p_booking_id;
select ocu.online_flag , off.Learning_object_id from ota_category_usages ocu,
ota_offerings off , ota_events oev
where ocu.category_usage_id = off.delivery_mode_id
and off.offering_id = oev.parent_offering_id
and oev.event_id = p_event_id;
select bst.Type
from ota_booking_status_types bst, ota_delegate_bookings tdb
where bst.booking_status_type_id= tdb.booking_status_type_id
and tdb.booking_id = p_booking_id;
SELECT event_id, customer_id, organization_id,
delegate_person_id, delegate_assignment_id,
delegate_contact_id
FROM ota_delegate_bookings
WHERE booking_id = p_booking_id;
ota_delegate_booking_api.update_delegate_booking(
p_effective_date => trunc(sysdate)
, p_booking_id => p_booking_id
, p_booking_status_type_id => p_booking_status_type_id
, p_delegate_person_id => p_delegate_person_id
, p_contact_id => p_contact_id
, p_business_group_id => p_business_group_id
, p_event_id => p_event_id
, p_customer_id => p_customer_id
, p_authorizer_person_id => p_authorizer_person_id
, p_date_booking_placed => p_date_booking_placed
, p_corespondent => p_corespondent
, p_internal_booking_flag => p_internal_booking_flag
, p_number_of_places => p_number_of_places
, p_object_version_number => p_object_version_number
, p_administrator => p_administrator
, p_booking_priority => p_booking_priority
, p_comments => p_comments
, p_contact_address_id => p_contact_address_id
, p_delegate_contact_phone => p_delegate_contact_phone
, p_delegate_contact_fax => p_delegate_contact_fax
, p_third_party_customer_id => p_third_party_customer_id
, p_third_party_contact_id => p_third_party_contact_id
, p_third_party_address_id => p_third_party_address_id
, p_third_party_contact_phone => p_third_party_contact_phone
, p_third_party_contact_fax => p_third_party_contact_fax
, p_date_status_changed => p_date_status_changed
, p_status_change_comments => p_status_change_comments
, p_failure_reason => p_failure_reason
, p_attendance_result => p_attendance_result
, p_language_id => p_language_id
, p_source_of_booking => p_source_of_booking
, p_special_booking_instructions => p_special_booking_instructions
, p_successful_attendance_flag => p_successful_attendance_flag
, p_tdb_information_category => p_tdb_information_category
, p_tdb_information1 => p_tdb_information1
, p_tdb_information2 => p_tdb_information2
, p_tdb_information3 => p_tdb_information3
, p_tdb_information4 => p_tdb_information4
, p_tdb_information5 => p_tdb_information5
, p_tdb_information6 => p_tdb_information6
, p_tdb_information7 => p_tdb_information7
, p_tdb_information8 => p_tdb_information8
, p_tdb_information9 => p_tdb_information9
, p_tdb_information10 => p_tdb_information10
, p_tdb_information11 => p_tdb_information11
, p_tdb_information12 => p_tdb_information12
, p_tdb_information13 => p_tdb_information13
, p_tdb_information14 => p_tdb_information14
, p_tdb_information15 => p_tdb_information15
, p_tdb_information16 => p_tdb_information16
, p_tdb_information17 => p_tdb_information17
, p_tdb_information18 => p_tdb_information18
, p_tdb_information19 => p_tdb_information19
, p_tdb_information20 => p_tdb_information20
, p_update_finance_line => p_update_finance_line
, p_tfl_object_version_number => p_tfl_object_version_number
, p_finance_header_id => p_finance_header_id
, p_finance_line_id => p_finance_line_id
, p_standard_amount => p_standard_amount
, p_unitary_amount => p_unitary_amount
, p_money_amount => p_money_amount
, p_currency_code => p_currency_code
, p_booking_deal_type => p_booking_deal_type
, p_booking_deal_id => p_booking_deal_id
, p_enrollment_type => p_enrollment_type
, p_validate => p_validate
, p_organization_id => p_organization_id
, p_sponsor_person_id => p_sponsor_person_id
, p_sponsor_assignment_id => p_sponsor_assignment_id
, p_person_address_id => p_person_address_id
, p_delegate_assignment_id => p_delegate_assignment_id
, p_delegate_contact_id => p_delegate_contact_id
, p_delegate_contact_email => p_delegate_contact_email
, p_third_party_email => p_third_party_email
, p_person_address_type => p_person_address_type
, p_line_id => p_line_id
, p_org_id => p_org_id
, p_daemon_flag => p_daemon_flag
, p_daemon_type => p_daemon_type
, p_old_event_id => p_old_event_id
, p_quote_line_id => p_quote_line_id
, p_interface_source => p_interface_source
, p_total_training_time => p_total_training_time
, p_content_player_status => p_content_player_status
, p_score => p_score
, p_completed_content => p_completed_content
, p_total_content => p_total_content
, p_booking_justification_id => p_booking_justification_id
, p_source_cancel => p_source_cancel
, p_override_prerequisites => p_override_prerequisites
, p_is_history_flag => p_is_history_flag
, p_override_learner_access => p_override_learner_access
, p_sign_eval_status => p_sign_eval_status
);
l_proc := g_package||'update_enrollment';
savepoint update_enrollment;
p_update_finance_line,
p_tfl_object_version_number,
p_finance_header_id,
p_finance_line_id,
p_standard_amount,
p_unitary_amount,
p_money_amount,
p_currency_code,
p_booking_deal_type,
p_booking_deal_id,
p_enrollment_type,
p_validate,
p_organization_id,
p_sponsor_person_id,
p_sponsor_assignment_id,
p_person_address_id,
p_delegate_assignment_id,
p_delegate_contact_id,
p_delegate_contact_email,
p_third_party_email,
p_person_address_type,
p_line_id,
p_org_id,
-- Modified for bug#4654530
l_daemon_flag,
l_daemon_type,
-- p_daemon_flag,
-- p_daemon_type,
p_old_event_id,
p_quote_line_id,
p_interface_source,
p_total_training_time,
p_content_player_status,
p_score,
p_completed_content,
p_total_content,
p_booking_justification_id,
p_is_history_flag
);
select delegate_person_id into l_person_id from ota_delegate_bookings
where booking_id = p_booking_id;
ota_trng_plan_comp_ss.update_tpc_enroll_status_chg(p_event_id => p_event_id,
p_person_id => l_person_id,
-- Added for Bug#3479186
p_contact_id => NULL,
p_learning_path_ids => l_learning_path_ids);
ota_trng_plan_comp_ss.update_tpc_enroll_status_chg(p_event_id => p_event_id,
p_person_id => NULL,
p_contact_id => l_delegate_contact_id,
p_learning_path_ids => l_learning_path_ids);
ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => p_event_id,
p_person_id => l_person_id,
p_contact_id => null,
p_lp_enrollment_ids => l_lp_enrollment_ids);
-- update any associated cert member enrollment statuses
ota_cme_util.update_cme_status(p_event_id => p_event_id,
p_person_id => l_person_id,
p_contact_id => null,
p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => p_event_id,
p_person_id => null,
p_contact_id => l_delegate_contact_id,
p_lp_enrollment_ids => l_lp_enrollment_ids);
-- update any associated cert member enrollment statuses
ota_cme_util.update_cme_status(p_event_id => p_event_id,
p_person_id => null,
p_contact_id => l_delegate_contact_id,
p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
select Type into l_type from ota_booking_status_types where booking_status_type_id=p_booking_status_type_id;
ota_competence_ss.create_wf_process(p_process =>'OTA_COMPETENCE_UPDATE_JSP_PRC',
p_itemtype =>'HRSSA',
p_person_id => l_person_id,
p_eventid =>p_event_id,
p_learningpath_ids => null,
p_itemkey =>l_item_key);
deleteForumNotification(p_event_id,l_person_id, l_delegate_contact_id);
When the class name is changed for an enrollment, the lme update must be called
twice, once for the old class and once for the new class.
When the learner name is changed for an enrollment, the lme update must be called
twice, once for the old learner and once for the new learner.
If both the learner aswell as class associated with an enrollment are changed,
update lme must be called once for old class, old learner and once for new class and new learner
**/
/*
IF l_event_id_changed AND NOT l_person_id_changed AND NOT l_contact_id_changed THEN
IF l_person_id IS NOT NULL THEN
ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
p_person_id => l_person_id,
p_contact_id => null,
p_lp_enrollment_ids => l_lp_enrollment_ids);
-- update any associated cert member enrollment statuses
ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
p_person_id => l_person_id,
p_contact_id => null,
p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id =>p_event_id,
p_person_id => l_person_id,
p_contact_id => null,
p_lp_enrollment_ids => l_lp_enrollment_ids);
-- update any associated cert member enrollment statuses
ota_cme_util.update_cme_status(p_event_id => p_event_id,
p_person_id => l_person_id,
p_contact_id => null,
p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
--If the event has changed, the forum notification record should be deleted and created for the new event
-- FRM Notification should be created only for Placed or Attended status. Not for 'C','W' or 'R'.
deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, l_person_id, null);
ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
p_person_id => null,
p_contact_id => l_delegate_contact_id,
p_lp_enrollment_ids => l_lp_enrollment_ids);
-- update any associated cert member enrollment statuses
ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
p_person_id => null,
p_contact_id => l_delegate_contact_id,
p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => p_event_id,
p_person_id => null,
p_contact_id => l_delegate_contact_id,
p_lp_enrollment_ids => l_lp_enrollment_ids);
-- update any associated cert member enrollment statuses
ota_cme_util.update_cme_status(p_event_id => p_event_id,
p_person_id => null,
p_contact_id => l_delegate_contact_id,
p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, null, l_delegate_contact_id);
ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
p_person_id =>ota_tdb_shd.g_old_rec.delegate_person_id,
p_contact_id => null,
p_lp_enrollment_ids => l_lp_enrollment_ids);
ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
p_person_id => ota_tdb_shd.g_old_rec.delegate_person_id,
p_contact_id => null,
p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => p_event_id,
p_person_id => l_person_id,
p_contact_id => null,
p_lp_enrollment_ids => l_lp_enrollment_ids);
ota_cme_util.update_cme_status(p_event_id => p_event_id,
p_person_id => l_person_id,
p_contact_id => null,
p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, ota_tdb_shd.g_old_rec.delegate_person_id, null);
ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
p_person_id => null,
p_contact_id =>ota_tdb_shd.g_old_rec.delegate_contact_id,
p_lp_enrollment_ids => l_lp_enrollment_ids);
ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
p_person_id => null,
p_contact_id => ota_tdb_shd.g_old_rec.delegate_contact_id,
p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => p_event_id,
p_person_id => null,
p_contact_id => p_delegate_contact_id,
p_lp_enrollment_ids => l_lp_enrollment_ids);
ota_cme_util.update_cme_status(p_event_id => p_event_id,
p_person_id => null,
p_contact_id => p_delegate_contact_id,
p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, null, ota_tdb_shd.g_old_rec.delegate_contact_id);
ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
p_person_id =>ota_tdb_shd.g_old_rec.delegate_person_id,
p_contact_id => null,
p_lp_enrollment_ids => l_lp_enrollment_ids);
ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
p_person_id => ota_tdb_shd.g_old_rec.delegate_person_id,
p_contact_id => null,
p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
p_person_id => l_person_id,
p_contact_id => null,
p_lp_enrollment_ids => l_lp_enrollment_ids);
ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
p_person_id => l_person_id,
p_contact_id => null,
p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, ota_tdb_shd.g_old_rec.delegate_person_id, null);
ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
p_person_id => null,
p_contact_id =>ota_tdb_shd.g_old_rec.delegate_contact_id,
p_lp_enrollment_ids => l_lp_enrollment_ids);
ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
p_person_id => null,
p_contact_id => ota_tdb_shd.g_old_rec.delegate_contact_id,
p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => ota_tdb_shd.g_old_rec.event_id,
p_person_id => null,
p_contact_id => p_delegate_contact_id,
p_lp_enrollment_ids => l_lp_enrollment_ids);
ota_cme_util.update_cme_status(p_event_id => ota_tdb_shd.g_old_rec.event_id,
p_person_id => null,
p_contact_id => p_delegate_contact_id,
p_cert_prd_enrollment_ids => l_cert_prd_enrollment_ids);
deleteForumNotification(ota_tdb_shd.g_old_rec.event_id, null, ota_tdb_shd.g_old_rec.delegate_contact_id);
if p_update_finance_line in ('C','Y') then
--
l_cancel_finance_line := (p_update_finance_line = 'C');
ROLLBACK TO update_enrollment;
end Update_Enrollment;
Procedure Update_Waitlisted (p_booking_id in number
,p_object_version_number in out nocopy number
,p_event_id in number
,p_booking_status_type_id in number
,p_date_status_changed in date
,p_status_change_comments in varchar2
,p_number_of_places in number
,p_finance_line_id in out nocopy number
,p_tfl_object_version_number in out nocopy number
,p_administrator in number
,p_validate in boolean
) is
--
--
l_status_change_comments varchar2(1000);
l_proc := g_package||'Update_Waitlisted';
ota_tdb_api_upd2.update_enrollment
(p_booking_id => p_booking_id
,p_object_version_number => p_object_version_number
,p_event_id => p_event_id
,p_booking_status_type_id => p_booking_status_type_id
,p_date_status_changed => p_date_status_changed
,p_status_change_comments => p_status_change_comments
,p_number_of_places => p_number_of_places
,p_update_finance_line => 'N'
,p_finance_line_id => p_finance_line_id
,p_tfl_object_version_number => p_tfl_object_version_number
,p_administrator => p_administrator
,p_validate => p_validate
);
end Update_Waitlisted;