[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select decode(nvl(evt_eval.eval_mandatory_flag,'N'), 'Y', 'Y',
decode(act_eval.evaluation_id,null,'N',decode(nvl(act_eval.eval_mandatory_flag,'N'),'Y','Y','N'))) flag --bug 7184369
from ota_evaluations evt_eval,
ota_evaluations act_eval,
ota_events evt
where evt_eval.object_id(+) = evt.event_id
and (evt_eval.object_type is null or evt_eval.object_type = 'E')
and act_eval.object_id(+) = evt.activity_version_id
and (act_eval.object_type is null or act_eval.object_type = 'A')
and evt.event_id = p_event_id
and (evt_eval.evaluation_id is not null or act_eval.evaluation_id is not null); --Bug7174996
SELECT hao.business_group_id,
evt.organization_id,
evt.currency_code,
evt.course_start_date,
evt.course_end_date,
evt.Title,
evt.owner_id,
off.activity_version_id,
evt.offering_id,
nvl(evt.price_basis,NULL)
FROM OTA_EVENTS_VL evt,
OTA_OFFERINGS off,
HR_ALL_ORGANIZATION_UNITS hao
WHERE evt.event_id = p_event_id
AND off.offering_id = evt.parent_offering_id
AND evt.organization_id = hao.organization_id (+);
SELECT paf.organization_id
FROM per_all_assignments_f paf
WHERE paf.assignment_id = p_delegate_assignment_id;
SELECT pcak.cost_allocation_keyflex_id
FROM per_all_assignments_f assg,
pay_cost_allocations_f pcaf,
pay_cost_allocation_keyflex pcak
WHERE assg.assignment_id = pcaf.assignment_id
AND assg.assignment_id = p_delegate_assignment_id
AND assg.Primary_flag = 'Y'
AND pcaf.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id
AND pcak.enabled_flag = 'Y'
AND sysdate between nvl(pcaf.effective_start_date,sysdate)
and nvl(pcaf.effective_end_date,sysdate+1)
AND trunc(sysdate) between nvl(assg.effective_start_date,trunc(sysdate))
and nvl(assg.effective_end_date,trunc(sysdate+1));
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 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 = p_event_id
and fr.auto_notification_flag = 'Y';
Select title from ota_events_vl
where event_id = p_event_id;
select decode(nvl(evt_eval.eval_mandatory_flag,'N'), 'Y', 'Y',
decode(act_eval.evaluation_id,null,'N',decode(nvl(act_eval.eval_mandatory_flag,'N'),'Y','Y','N'))) flag --bug 7184369,6935364,7174996
from ota_evaluations evt_eval,
ota_evaluations act_eval,
ota_events evt
where evt_eval.object_id(+) = evt.event_id
and (evt_eval.object_type is null or evt_eval.object_type = 'E')
and act_eval.object_id(+) = evt.activity_version_id
and (act_eval.object_type is null or act_eval.object_type = 'A')
and evt.event_id = p_event_id
and (evt_eval.evaluation_id is not null or act_eval.evaluation_id is not null); --bug 7174996
select currency_code from ota_events
where event_id = p_event_id;
ota_lrng_path_member_util.update_lme_enroll_status_chg(p_event_id => p_event_id,
p_person_id => p_delegate_person_id,
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 => p_delegate_person_id,
p_contact_id => p_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 => p_delegate_person_id,
p_eventid =>p_event_id,
p_learningpath_ids => null,
p_itemkey =>l_item_key);
UPDATE ota_finance_lines SET transfer_status = 'AT'
WHERE finance_line_id = p_finance_line_id;
select decode(nvl(evt_eval.eval_mandatory_flag,'N'), 'Y', 'Y',
decode(act_eval.evaluation_id,null,'N',decode(nvl(act_eval.eval_mandatory_flag,'N'),'Y','Y','N'))) flag --bug 7184369
from ota_evaluations evt_eval,
ota_evaluations act_eval,
ota_events evt
where evt_eval.object_id(+) = evt.event_id
and (evt_eval.object_type is null or evt_eval.object_type = 'E')
and act_eval.object_id(+) = evt.activity_version_id
and (act_eval.object_type is null or act_eval.object_type = 'A')
and evt.event_id = p_event_id
and (evt_eval.evaluation_id is not null or act_eval.evaluation_id is not null); --Bug7174996
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_delegate_booking
(
p_validate in boolean,
p_effective_date in date,
p_booking_id in number,
p_booking_status_type_id in number,
p_delegate_person_id in number,
p_contact_id in number,
p_business_group_id in number,
p_event_id in number,
p_customer_id in number,
p_authorizer_person_id in number,
p_date_booking_placed in date ,
p_corespondent in varchar2,
p_internal_booking_flag in varchar2,
p_number_of_places in number,
p_object_version_number in out nocopy number,
p_administrator in number,
p_booking_priority in varchar2,
p_comments in varchar2,
p_contact_address_id in number,
p_delegate_contact_phone in varchar2,
p_delegate_contact_fax in varchar2,
p_third_party_customer_id in number,
p_third_party_contact_id in number,
p_third_party_address_id in number,
p_third_party_contact_phone in varchar2,
p_third_party_contact_fax in varchar2,
p_date_status_changed in date ,
p_status_change_comments in varchar2,
p_failure_reason in varchar2,
p_attendance_result in varchar2,
p_language_id in number,
p_source_of_booking in varchar2,
p_special_booking_instructions in varchar2,
p_successful_attendance_flag in varchar2,
p_tdb_information_category in varchar2,
p_tdb_information1 in varchar2,
p_tdb_information2 in varchar2,
p_tdb_information3 in varchar2,
p_tdb_information4 in varchar2,
p_tdb_information5 in varchar2,
p_tdb_information6 in varchar2,
p_tdb_information7 in varchar2,
p_tdb_information8 in varchar2,
p_tdb_information9 in varchar2,
p_tdb_information10 in varchar2,
p_tdb_information11 in varchar2,
p_tdb_information12 in varchar2,
p_tdb_information13 in varchar2,
p_tdb_information14 in varchar2,
p_tdb_information15 in varchar2,
p_tdb_information16 in varchar2,
p_tdb_information17 in varchar2,
p_tdb_information18 in varchar2,
p_tdb_information19 in varchar2,
p_tdb_information20 in varchar2,
p_update_finance_line in varchar2,
p_tfl_object_version_number in out nocopy number,
p_finance_header_id in number,
p_finance_line_id in out nocopy number,
p_standard_amount in number,
p_unitary_amount in number,
p_money_amount in number,
p_currency_code in varchar2,
p_booking_deal_type in varchar2,
p_booking_deal_id in number,
p_enrollment_type in varchar2,
p_organization_id in number,
p_sponsor_person_id in number,
p_sponsor_assignment_id in number,
p_person_address_id in number,
p_delegate_assignment_id in number,
p_delegate_contact_id in number,
p_delegate_contact_email in varchar2,
p_third_party_email in varchar2,
p_person_address_type in varchar2,
p_line_id in number,
p_org_id in number,
p_daemon_flag in varchar2,
p_daemon_type in varchar2,
p_old_event_id in number,
p_quote_line_id in number,
p_interface_source in varchar2,
p_total_training_time in varchar2,
p_content_player_status in varchar2,
p_score in number,
p_completed_content in number,
p_total_content in number,
p_booking_justification_id in number,
p_is_history_flag in varchar2
,p_override_prerequisites in varchar2
,p_override_learner_access in varchar2
,p_source_cancel in varchar2
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||' update_delegate_booking ';
SELECT oet.title,oe.course_start_date,oe.course_start_time,oe.owner_id
FROM ota_events_tl oet, ota_events oe
WHERE oet.event_id = oe.event_id
AND oe.event_id = p_old_event_id
AND oet.language = userenv('LANG');
SELECT to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS')
FROM dual;
SELECT user_name
FROM fnd_user
WHERE employee_id = l_owner_id
AND trunc(sysdate) BETWEEN trunc(start_date) AND nvl(trunc(end_date),trunc(sysdate)+1);
SELECT booking_status_type_id
FROM ota_delegate_bookings
WHERE booking_id = p_booking_id;
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;
SELECT cancelled_flag
FROM ota_finance_lines
WHERE finance_line_id = p_finance_line_id;
select evt_eval.evaluation_id evt_eval_id,decode(nvl(evt_eval.eval_mandatory_flag,'N'), 'Y', 'Y',
decode(act_eval.evaluation_id,null,'N',decode(nvl(act_eval.eval_mandatory_flag,'N'),'Y','Y','N'))) flag, --bug 7184369,6935364,7174996
act_eval.evaluation_id act_eval_id
from ota_evaluations evt_eval,
ota_evaluations act_eval,
ota_events evt
where evt_eval.object_id(+) = evt.event_id
and (evt_eval.object_type is null or evt_eval.object_type = 'E')
and act_eval.object_id(+) = evt.activity_version_id
and (act_eval.object_type is null or act_eval.object_type = 'A')
and evt.event_id = p_event_id
and (evt_eval.evaluation_id is not null or act_eval.evaluation_id is not null); --Bug7174996
select 1
from ota_attempts
where event_id = p_event_id
and test_id = l_evt_eval_id
and user_id = l_user_id
and user_type = l_user_type;
select rel.subject_id
from hz_cust_account_roles acct_role,
hz_relationships rel,
hz_cust_accounts role_acct
where acct_role.party_id = rel.party_id
and acct_role.role_type = 'CONTACT'
and acct_role.cust_account_id = role_acct.cust_account_id
and role_acct.party_id = rel.object_id
and rel.subject_table_name = 'HZ_PARTIES'
and rel.object_table_name = 'HZ_PARTIES'
and acct_role.cust_account_role_id = nvl(p_delegate_contact_id,p_contact_id);
savepoint update_delegate_booking ;
ota_delegate_booking_bk2.update_delegate_booking_b
(
p_effective_date => l_effective_date ,
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 => l_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 => l_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 => l_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_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_is_history_flag => p_is_history_flag
);
(p_module_name => 'update_delegate_booking_b'
,p_hook_type => 'BP'
);
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
);
ota_tdb_api_upd2.update_enrollment
(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 => l_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 => l_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_currency_code => p_currency_code ,
p_standard_amount => p_standard_amount ,
p_unitary_amount => p_unitary_amount ,
p_money_amount => p_money_amount ,
p_booking_deal_id => p_booking_deal_id ,
p_booking_deal_type => p_booking_deal_type ,
p_finance_line_id => l_finance_line_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_is_history_flag => p_is_history_flag
,p_override_prerequisites => p_override_prerequisites
,p_override_learner_access => p_override_learner_access
);
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);
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);
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');
IF (p_update_finance_line = 'C') THEN
OTA_LEARNER_ENROLL_SS.cancel_finance(p_booking_id); --Bug#7110214
OTA_delegate_booking_bk2.update_delegate_booking_a
(p_effective_date => l_effective_date ,
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 => l_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 => l_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 => l_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_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_is_history_flag => p_is_history_flag
);
(p_module_name => 'update_delegate_booking_a'
,p_hook_type => 'AP'
);
rollback to update_delegate_booking ;
rollback to update_delegate_booking ;
end update_delegate_booking ;
procedure delete_delegate_booking
(
p_validate in boolean,
p_booking_id in number,
p_object_version_number in number
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||' delete_delegate_booking ';
SELECT oet.title,oe.course_start_date,oe.course_start_time,oe.owner_id
FROM ota_events_tl oet, ota_events oe
WHERE oet.event_id = oe.event_id
AND oe.event_id = p_event_id
AND oet.language = userenv('LANG');
SELECT to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS')
FROM dual;
SELECT user_name
FROM fnd_user
WHERE employee_id = l_owner_id
AND trunc(sysdate) BETWEEN trunc(start_date) AND nvl(trunc(end_date),trunc(sysdate)+1);
SELECT event_id,booking_status_type_id
FROM ota_delegate_bookings
WHERE booking_id = p_booking_id;
SELECT type
FROM ota_booking_status_types
WHERE booking_status_type_id = l_booking_status_type_id;
savepoint delete_delegate_booking ;
OTA_delegate_booking_bk3.delete_delegate_booking_b
(p_booking_id => p_booking_id ,
p_object_version_number => p_object_version_number);
(p_module_name => 'delete_delegate_booking_b'
,p_hook_type => 'BP'
);
OTA_delegate_booking_bk3.delete_delegate_booking_a
(p_booking_id => p_booking_id ,
p_object_version_number => p_object_version_number);
(p_module_name => 'delete_delegate_booking_a'
,p_hook_type => 'AP'
);
rollback to delete_delegate_booking ;
rollback to delete_delegate_booking ;
end delete_delegate_booking;
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;