[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select lo.learning_object_id, nvl(p.lesson_status, 'N') as lesson_status
from ota_learning_objects lo, ota_performances p
where lo.parent_learning_object_id = p_lo_id and
lo.published_flag = 'Y' and
p.learning_object_id(+) = lo.source_learning_object_id and
p.user_id(+) = p_user_id and
p.user_type(+) = p_user_type and
nvl(p.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1);
select cr.parent_learning_object_id as learning_object_id, cr.assigned_lesson_status as assigned_lesson_status,
null as match_lesson_status, nvl(p.lesson_status, 'N') as user_lesson_status
from ota_performances p, ota_completion_requirements cr
where cr.child_learning_object_id = p_lo_id and
p.learning_object_id(+) = cr.parent_learning_object_id and
p.user_id(+) = p_user_id and
p.user_type(+) = p_user_type and
nvl(p.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1)
order by cr.seq asc;
select acct_role.cust_account_role_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_id = p_party_id and
rel.subject_table_name = 'HZ_PARTIES' and
rel.object_table_name = 'HZ_PARTIES';
procedure update_enrollment(
p_booking_id ota_delegate_bookings.booking_id%type,
p_event_id ota_events.event_id%type,
p_business_group_id ota_delegate_bookings.business_group_id%type,
p_date_booking_placed ota_delegate_bookings.date_booking_placed%type,
p_object_version_number ota_delegate_bookings.object_version_number%type,
p_date_status_changed ota_delegate_bookings.date_status_changed%type,
p_new_status varchar2) is
begin
update_enrollment(p_booking_id,p_event_id,p_business_group_id,p_date_booking_placed,p_object_version_number,p_date_status_changed,p_new_status,null);
end update_enrollment;
procedure update_enrollment(
p_booking_id ota_delegate_bookings.booking_id%type,
p_event_id ota_events.event_id%type,
p_business_group_id ota_delegate_bookings.business_group_id%type,
p_date_booking_placed ota_delegate_bookings.date_booking_placed%type,
p_object_version_number ota_delegate_bookings.object_version_number%type,
p_date_status_changed ota_delegate_bookings.date_status_changed%type,
p_new_status varchar2,
p_failed varchar2) is
v_result_object_version_number ota_finance_lines.object_version_number%type;
ota_tdb_api_upd2.update_enrollment(
p_booking_id => p_booking_id,
p_event_id => p_event_id,
p_failure_reason => l_failure_reason,
p_object_version_number => v_object_version_number,
p_booking_status_type_id => v_booking_status_row.booking_status_type_id,
p_tfl_object_version_number => v_result_object_version_number,
p_finance_line_id => v_finance_line_id,
p_date_status_changed => p_date_status_changed,
p_date_booking_placed => p_date_booking_placed,
p_successful_attendance_flag => l_successful_attendance_flag);
end update_enrollment;
procedure update_enrollment_status(
p_user_id fnd_user.user_id%type,
p_user_type ota_attempts.user_type%type,
p_event_id ota_events.event_id%type) is
-- This cursor finds all events in all offerings which offer the LO and in
-- which the person is enrolled with a status of 'PENDING EVALUATION'.
cursor person_bookings(
p_event_id ota_events.event_id%type,
p_person_id ota_delegate_bookings.delegate_person_id%type) is
select book.booking_id,
ev.event_id,
book.business_group_id,
book.date_booking_placed,
book.object_version_number
from ota_events ev,
ota_delegate_bookings book,
ota_booking_status_types stype
where nvl(ev.course_start_date, sysdate) <= sysdate and
book.event_id = ev.event_id and
book.delegate_person_id = p_person_id and
ev.event_id = p_event_id and
book.booking_status_type_id = stype.booking_status_type_id and
stype.type = 'E';
select book.booking_id,
ev.event_id,
book.business_group_id,
book.date_booking_placed,
book.object_version_number
from ota_events ev,
ota_delegate_bookings book,
ota_booking_status_types stype,
hz_cust_account_roles acct_role,
hz_relationships rel,
hz_cust_accounts role_acct
where nvl(ev.course_start_date, sysdate) <= sysdate and
book.event_id = ev.event_id and
book.booking_status_type_id = stype.booking_status_type_id and
ev.event_id = p_event_id and
stype.type = 'E' and
book.delegate_contact_id = acct_role.cust_account_role_id and
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_id = p_party_id and
rel.subject_table_name = 'HZ_PARTIES' and
rel.object_table_name = 'HZ_PARTIES';
select per.lesson_status,
offe.learning_object_id
from ota_performances per,
ota_offerings offe,
ota_events evt
where evt.parent_offering_id = offe.offering_id and
offe.learning_object_id = per.learning_object_id(+) and
evt.event_id = p_event_id and
(per.user_type is null or per.user_type = p_user_type) and
(per.user_id is null or per.user_id = p_user_id);
l_proc varchar2(72) := g_package||'update_enrollment_status';
update_enrollment(
a_booking.booking_id,
a_booking.event_id,
a_booking.business_group_id,
a_booking.date_booking_placed,
a_booking.object_version_number,
sysdate,
l_status,
l_failed);
update_enrollment(
a_booking.booking_id,
a_booking.event_id,
a_booking.business_group_id,
a_booking.date_booking_placed,
a_booking.object_version_number,
sysdate,
l_status,
l_failed);
end update_enrollment_status;
procedure update_enroll_status_for_lo(
p_lo_id ota_learning_objects.learning_object_id%type,
p_user_id fnd_user.user_id%type,
p_user_type ota_attempts.user_type%type,
p_date date) is
l_proc varchar2(72) := g_package||'update_enroll_status_for_lo';
update_enroll_status_for_lo(p_lo_id,p_user_id,p_user_type,p_date,null);
end update_enroll_status_for_lo;
procedure update_enroll_status_for_lo(
p_lo_id ota_learning_objects.learning_object_id%type,
p_user_id fnd_user.user_id%type,
p_user_type ota_attempts.user_type%type,
p_date date,
p_failed varchar2) is
-- This cursor finds all events in all offerings which offer the LO and in
-- which the person is enrolled with a status of 'PLACED'. Note that we
-- use source_learning_object_id here in case reuse by reference is ever
-- implemented.
cursor person_bookings(
p_lo_id ota_learning_objects.learning_object_id%type,
p_person_id ota_delegate_bookings.delegate_person_id%type) is
select book.booking_id,
ev.event_id,
book.business_group_id,
book.date_booking_placed,
book.object_version_number,
stype.type --Added for 7110517.
from ota_events ev,
ota_offerings offr,
ota_learning_objects lo,
ota_delegate_bookings book,
ota_booking_status_types stype
where lo.source_learning_object_id = p_lo_id and
lo.learning_object_id = offr.learning_object_id and
offr.offering_id = ev.parent_offering_id and
nvl(ev.course_start_date, sysdate) <= sysdate and
book.event_id = ev.event_id and
book.delegate_person_id = p_person_id and
book.booking_status_type_id = stype.booking_status_type_id and
(stype.type = 'P' or
(stype.type = 'A' and --6777581.Added 'A' for
nvl(book.successful_attendance_flag,'N')='N'));--failed candidates.
select book.booking_id,
ev.event_id,
book.business_group_id,
book.date_booking_placed,
book.object_version_number,
stype.type --Added fro 7110517.
from ota_events ev,
ota_offerings offr,
ota_learning_objects lo,
ota_delegate_bookings book,
ota_booking_status_types stype,
hz_cust_account_roles acct_role,
hz_relationships rel,
hz_cust_accounts role_acct
where lo.source_learning_object_id = p_lo_id and
lo.learning_object_id = offr.learning_object_id and
offr.offering_id = ev.parent_offering_id and
nvl(ev.course_start_date, sysdate) <= sysdate and
book.event_id = ev.event_id and
book.booking_status_type_id = stype.booking_status_type_id and
(stype.type = 'P' or
(stype.type = 'A' and --6777581.Added 'A' for
nvl(book.successful_attendance_flag,'N')='N')) and --failed candidates.
book.delegate_contact_id = acct_role.cust_account_role_id and
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_id = p_party_id and
rel.subject_table_name = 'HZ_PARTIES' and
rel.object_table_name = 'HZ_PARTIES';
select attempt_id from ota_attempts where
event_id = l_event_id
and test_id = l_test_id
and user_id = p_user_id
and user_type = p_user_type;
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
,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 = l_event_id
and (evt_eval.evaluation_id is not null or act_eval.evaluation_id is not null); --7172501
l_proc varchar2(72) := g_package||'update_enroll_status_for_lo';
select decode(l_evt_eval_id,l_act_eval_id,null,nvl(l_evt_eval_id,l_act_eval_id))
into l_test_id from dual;
update_enrollment(
a_booking.booking_id,
a_booking.event_id,
a_booking.business_group_id,
a_booking.date_booking_placed,
a_booking.object_version_number,
p_date,
l_status,
p_failed);
select decode(l_evt_eval_id,l_act_eval_id,null,nvl(l_evt_eval_id,l_act_eval_id))
into l_test_id from dual;
update_enrollment(
a_booking.booking_id,
a_booking.event_id,
a_booking.business_group_id,
a_booking.date_booking_placed,
a_booking.object_version_number,
p_date,
l_status,
p_failed);
end update_enroll_status_for_lo;
procedure update_cme_status_for_lo(
p_lo_id ota_learning_objects.learning_object_id%type,
p_date date,
p_cert_prd_enroll_id ota_cert_mbr_enrollments.cert_prd_enrollment_id%type) is
-- This cursor finds all certification member records in the certification
-- period that area associated with this learning object.
cursor cert_member_enrollments(
p_learning_object_id ota_learning_objects.learning_object_id%type,
p_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) is
select distinct(cme.cert_mbr_enrollment_id),
cme.member_status_code,
cme.object_version_number,
cme.cert_member_id,
cme.cert_prd_enrollment_id
from ota_certification_members cm, ota_cert_mbr_enrollments cme,
ota_offerings o, ota_cert_prd_enrollments cpe
where
cme.cert_prd_enrollment_id = p_cert_prd_enrollment_id
and cme.cert_member_id = cm.certification_member_id
and cm.object_id = o.activity_version_id
and o.learning_object_id = p_learning_object_id
-- filter ended offerings
and trunc(sysdate) between trunc(o.start_date) and nvl(trunc(o.end_date), trunc(sysdate))
and cme.cert_prd_enrollment_id = cpe.cert_prd_enrollment_id
and cpe.period_status_code <> 'CANCELLED'
and trunc(sysdate) between trunc(cpe.cert_period_start_date) and trunc(cpe.cert_period_end_date)
and cme.member_status_code <> 'CANCELLED';
ota_cert_mbr_enrollment_api.update_cert_mbr_enrollment
(p_effective_date => sysdate
,p_object_version_number => a_cert_mbr_enrollment.object_version_number
,p_cert_member_id => a_cert_mbr_enrollment.cert_member_id
,p_cert_prd_enrollment_id => p_cert_prd_enroll_id
,p_cert_mbr_enrollment_id => a_cert_mbr_enrollment.cert_mbr_enrollment_id
,p_member_status_code => l_new_mbr_status_code
,p_completion_date => p_date);
ota_cme_util.update_cpe_status(a_cert_mbr_enrollment.cert_mbr_enrollment_id, a_cert_mbr_enrollment.cert_prd_enrollment_id);
end update_cme_status_for_lo;
select parent.learning_object_id as learning_object_id,
nvl(perf.lesson_status, 'N') as lesson_status,
parent.starting_url as starting_url
from ota_learning_objects child, ota_learning_objects parent, ota_performances perf
where child.source_learning_object_id = p_lo_id and
child.parent_learning_object_id = parent.learning_object_id and
parent.learning_object_id = parent.source_learning_object_id and
parent.starting_url is null and
perf.user_id(+) = p_user_id and
perf.user_type(+) = p_user_type and
perf.learning_object_id(+) = parent.learning_object_id and
nvl(perf.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1);
select cr.child_learning_object_id as learning_object_id,
nvl(perf.lesson_status, 'N') as lesson_status,
lo.starting_url as starting_url
from ota_performances perf, ota_completion_requirements cr, ota_learning_objects lo
where cr.parent_learning_object_id = p_lo_id and
cr.child_learning_object_id = lo.learning_object_id and
perf.learning_object_id(+) = cr.child_learning_object_id and
perf.user_id(+) = p_user_id and
perf.user_type(+) = p_user_type and
nvl(perf.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1);
select source_learning_object_id, business_group_id
into v_source_lo_id, v_business_group_id
from ota_learning_objects
where learning_object_id = p_lo_id;
select p.lesson_status, p.completed_date
into v_old_lesson_status, v_completed_date
from ota_performances p
where p.learning_object_id = v_source_lo_id and
p.user_id = p_user_id and
p.user_type = p_user_type and
nvl(p.cert_prd_enrollment_id, -1) = nvl(p_cert_prd_enroll_id, -1);
update_enroll_status_for_lo(v_source_lo_id, p_user_id, p_user_type, p_date);
update_cme_status_for_lo(p_lo_id, p_date, p_cert_prd_enroll_id);
update_enroll_status_for_lo(v_source_lo_id, p_user_id, p_user_type, p_date, 'Y');
update ota_performances
set lesson_status = p_lesson_status,
completed_date = v_completed_date,
last_updated_by = p_user_id,
last_update_date = p_date,
source = v_performance_source,
overridden_by = null,
overridden_date = null
where user_id = p_user_id and
user_type = p_user_type and
learning_object_id = v_source_lo_id and
nvl(cert_prd_enrollment_id, -1) = nvl(p_cert_prd_enroll_id, -1);
update_enroll_status_for_lo(v_source_lo_id, p_user_id, p_user_type, p_date);
update_enroll_status_for_lo(v_source_lo_id, p_user_id, p_user_type, p_date);
update_cme_status_for_lo(v_source_lo_id, p_date, p_cert_prd_enroll_id);
insert into ota_performances
(performance_id, user_id, user_type, learning_object_id,
lesson_status, score, time, completed_date,
created_by, creation_date, last_updated_by, last_update_date,
source, object_version_number, business_group_id, cert_prd_enrollment_id)
values
(ota_performances_s.nextval, p_user_id, p_user_type, v_source_lo_id,
p_lesson_status, -1000, -1001, v_completed_date,
p_user_id, p_date, p_user_id, p_date,
v_performance_source, 0, v_business_group_id, p_cert_prd_enroll_id);
select parent.learning_object_id, parent.starting_url, nvl(perf.time, 0) as time
from ota_learning_objects child, ota_learning_objects parent, ota_performances perf
where child.source_learning_object_id = p_lo_id and
child.parent_learning_object_id = parent.learning_object_id and
parent.learning_object_id = parent.source_learning_object_id and
parent.starting_url is null and
perf.user_id(+) = p_user_id and
perf.user_type(+) = p_user_type and
perf.learning_object_id(+) = parent.learning_object_id and
nvl(perf.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1);
select lo.source_learning_object_id as learning_object_id, nvl(p.time, 0) as time
from ota_performances p,
(select distinct learning_object_id, source_learning_object_id, starting_url
from ota_learning_objects
where learning_object_id <> p_lo_id
start with learning_object_id = p_lo_id
connect by parent_learning_object_id = prior learning_object_id) lo
where lo.starting_url is not null and
p.learning_object_id(+) = lo.source_learning_object_id and
p.user_id(+) = p_user_id and
p.user_type(+) = p_user_type and
nvl(p.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1);
select source_learning_object_id, business_group_id
into v_source_lo_id, v_business_group_id
from ota_learning_objects
where learning_object_id = p_lo_id;
select p.time
into v_old_time
from ota_performances p
where p.learning_object_id = v_source_lo_id and
p.user_id = p_user_id and
p.user_type = p_user_type and
nvl(p.cert_prd_enrollment_id, -1) = nvl(p_cert_prd_enroll_id, -1);
update ota_performances
set time = p_time,
last_updated_by = p_user_id,
last_update_date = p_date
where user_id = p_user_id and
user_type = p_user_type and
learning_object_id = v_source_lo_id and
nvl(cert_prd_enrollment_id, -1) = nvl(p_cert_prd_enroll_id, -1);
insert into ota_performances
(performance_id, user_id, user_type, learning_object_id,
lesson_status, score, time,
created_by, creation_date, last_updated_by, last_update_date,
source, object_version_number, business_group_id, cert_prd_enrollment_id)
values
(ota_performances_s.nextval, p_user_id, p_user_type, v_source_lo_id,
'N', -1000, p_time,
p_user_id, p_date, p_user_id, p_date,
v_performance_source, 0, v_business_group_id, p_cert_prd_enroll_id);
select published_flag,
starting_url,
start_date_active,
end_date_active
into v_published_flag,
v_starting_url,
v_start_date_active,
v_end_date_active
from ota_learning_objects
where learning_object_id = p_lo_id;
select o.learning_object_id
into v_root_lo_id
from ota_events e, ota_offerings o
where e.event_id = p_event_id and
e.parent_offering_id = o.offering_id;
select 'X'
into v_dummy
from ota_learning_objects
where learning_object_id = p_lo_id
start with learning_object_id = v_root_lo_id
connect by parent_learning_object_id = prior learning_object_id;
select 1
from ota_prerequisites preq,
ota_performances perf
where preq.parent_object_id = p_lo_id and
preq.object_id = perf.learning_object_id(+) and
perf.user_id(+) = p_user_id and
perf.user_type(+) = p_user_type and
nvl(perf.lesson_status, 'N') not in ('P', 'C');
SELECT object_id
FROM ota_prerequisites
where parent_object_id = p_lo_id
and parent_type = 'LO';
SELECT 1
FROM ota_performances
WHERE learning_object_id = csr_lo_id
AND user_id = p_user_id
AND user_type = p_user_type
AND lesson_status in ('P', 'C');
select t.max_attempts
into v_max_attempts
from ota_tests t, ota_learning_objects lo
where lo.learning_object_id = p_lo_id and
lo.test_id = t.test_id;
select count(*)
into v_user_attempts
from ota_attempts a, ota_tests t
where a.user_id = p_user_id and
a.user_type = p_user_type and
a.learning_object_id = p_lo_id and
nvl(a.cert_prd_enrollment_id, -1) = nvl(p_cert_prd_enrollment_id, -1) and
a.test_id = t.test_id and
a.initialized_flag = 'Y' and
a.attempt_type <> 'I' and
((a.suspend_data is null and
t.resume_flag = 'Y' and
a.internal_state <> 'A' and
(a.suspend_data is null or a.suspend_data <> 'I')) or
(t.resume_flag = 'N' and (a.suspend_data is null or a.suspend_data <> 'I')));
select nvl(max(a.timestamp), sysdate) + nvl(t.duration_between_attempt, 0)
into v_earliest_attempt_date
from ota_tests t,
ota_learning_objects lo,
ota_attempts a
where t.test_id = a.test_id
and a.initialized_flag = 'Y'
and ((t.resume_flag = 'Y'
and a.internal_state <> 'A'
and (a.suspend_data is null or a.suspend_data <> 'I'))
OR (t.resume_flag = 'N'
and (a.suspend_data is null or a.suspend_data <> 'I')))
and a.learning_object_id = p_lo_id
and a.user_id = p_user_id
and a.user_type = p_user_type
and nvl(p_cert_prd_enrollment_id, -1) = nvl(a.cert_prd_enrollment_id, -1)
group by t.test_id,t.max_attempts, t.duration_between_attempt;
select o.learning_object_id
from ota_offerings o, ota_certification_members cm, ota_cert_mbr_enrollments cme
where o.activity_version_id = cm.object_id
and cm.certification_member_id = cme.cert_member_id
and cme.cert_prd_enrollment_id = p_cert_prd_enroll_id;
select learning_object_id
from ota_learning_objects
where learning_object_id = p_lo_id
start with learning_object_id = p_root_lo_id
connect by parent_learning_object_id = prior learning_object_id;
select course_start_date, course_start_time, course_end_date, course_end_time
into v_course_start_date, v_course_start_time, v_course_end_date, v_course_end_time
from ota_events
where event_id = p_event_id;
select 1
from ota_delegate_bookings book,
ota_booking_status_types stype
where book.delegate_person_id = p_person_id and
book.event_id = p_event_id and
book.booking_status_type_id = stype.booking_status_type_id and
stype.type in ('P', 'A' ,'E');
select 1
from ota_delegate_bookings book,
ota_booking_status_types stype
where book.delegate_contact_id = p_contact_id and
book.event_id = p_event_id and
book.booking_status_type_id = stype.booking_status_type_id and
stype.type in ('P', 'A');
select 1 as dummy
from
ota_suppliable_resources tsr,
ota_resource_bookings trb
where
trb.forum_id is null and
trb.chat_id is null and
tsr.trainer_id = p_user_id and
trb.supplied_resource_id = tsr.supplied_resource_id and
(trb.event_id = p_event_id or
trb.event_id in ( select ses.event_id
from ota_events ses
where ses.parent_event_id = p_event_id
and ses.event_type = 'SESSION')
) and
tsr.resource_type = 'T') loop
p_reason := EVENT_REASON_NO_REASON;
select c.start_date_active, c.end_date_active, cpe.cert_period_start_date, cpe.cert_period_end_date
into v_cert_start_date, v_cert_end_date, v_cert_prd_enroll_start, v_cert_prd_enroll_end
from ota_certifications_b c, ota_cert_prd_enrollments cpe, ota_cert_enrollments ce
where cpe.cert_prd_enrollment_id = p_cert_prd_enroll_id and
cpe.cert_enrollment_id = ce.cert_enrollment_id and
ce.certification_id = c.certification_id;
select ce.unenrollment_date, nvl(ce.person_id, ce.contact_id)
into v_cert_unsubscribe_date, v_cert_enr_user_id
from ota_cert_prd_enrollments cpe, ota_cert_enrollments ce
where cpe.cert_prd_enrollment_id = p_cert_prd_enroll_id and
cpe.cert_enrollment_id = ce.cert_enrollment_id;
select learning_object_id, starting_url
from ota_learning_objects
where parent_learning_object_id = p_lo_id and
published_flag = 'Y'
order by child_seq asc;
select starting_url
into v_starting_url
from ota_learning_objects
where learning_object_id = p_root_lo_id;
select o.learning_object_id
into v_root_lo_id
from ota_events e, ota_offerings o
where e.event_id = p_event_id and
e.parent_offering_id = o.offering_id;
select learning_object_id, starting_url
from ota_learning_objects
where parent_learning_object_id = p_lo_id and
published_flag = 'Y'
order by child_seq asc;
select starting_url
into v_starting_url
from ota_learning_objects
where learning_object_id = p_root_lo_id;
select o.learning_object_id
into v_root_lo_id
from ota_events e, ota_offerings o
where e.event_id = p_event_id and
e.parent_offering_id = o.offering_id;
select a.learning_object_id, max(a.attempt_id) max_attempt
from ota_attempts a,
(select learning_object_id, starting_url
from ota_learning_objects
start with learning_object_id = p_root_lo_id
connect by parent_learning_object_id = prior learning_object_id) lo
where a.user_id = p_user_id and
a.user_type = p_user_type and
a.learning_object_id = lo.learning_object_id and
lo.starting_url is not null and
((p_cert_prd_enroll_id is null and a.cert_prd_enrollment_id is null) OR
(p_cert_prd_enroll_id is not null and a.cert_prd_enrollment_id = p_cert_prd_enroll_id)
)
group by a.learning_object_id
order by max_attempt desc;
select o.learning_object_id
into v_root_lo_id
from ota_events e, ota_offerings o
where e.event_id = p_event_id and
e.parent_offering_id = o.offering_id;
select o.learning_object_id
into v_root_lo_id
from ota_events e, ota_offerings o
where e.event_id = p_event_id and
e.parent_offering_id = o.offering_id;
select nvl(test_type_flag,'LO')
from ota_tests ot,
ota_learning_objects lo
where ot.test_id(+) = lo.test_id
and lo.learning_object_id = p_lo_id;
Select
olo.name Name,
nvl(hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',opf.lesson_status),
hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','N')) Status,
nvl(opf.time,0) Time,
opf.score Score,
decode(opf.lesson_status
,'C','player_status_c.gif'
,'F','player_status_f.gif'
,'I','player_status_i.gif'
,'P','player_status_p.gif'
,'N','player_status_n.gif'
,null,DECODE(OTA_LO_UTILITY.user_can_attempt_lo(olo.learning_object_id,p_user_id,p_user_type),
'N','player_status_no_prereq.gif',
'Y','player_status_n.gif')
,'player_status_n.gif') STATUS_ICON
, tst.grade_flag
, to_char(opf.completed_date)
, to_char(opf.completed_date, 'HH24:MI:SS')
, opf.lesson_status
, ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz
From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,
OTA_TESTS tst
Where
olo.learning_object_id = opf.learning_object_id(+)
AND tst.test_id(+) = olo.test_id
And olo.Learning_object_id = p_lo_id
And opf.User_id(+) = p_user_id
And opf.User_type(+) = p_user_type
AND opf.performance_id(+) = p_performance_id
AND cert_prd_enrollment_id(+) is null;
Select max(per.performance_id)
From ota_performances per
Where per.learning_object_id(+) = p_lo_id
And per.user_id(+) = p_user_id
And per.user_type(+) = p_user_type
AND per.cert_prd_enrollment_id(+) is null;
Select course_start_time, course_end_time, timezone
From ota_events
Where event_id = p_event_id;
select a.rco_id
,e.offering_id
into v_rco_id
,v_offering_id
from ota_activity_versions a, ota_events e
where a.activity_version_id = e.activity_version_id
and e.event_id = p_event_id;
select count(learning_object_id)
,sum(learning_object_id)
,nvl(max(published_flag),'N')
into v_number_of_los
,v_solo_lo_id
,v_published_flag
from ota_learning_objects
start with learning_object_id =
(select learning_object_id
from ota_offerings o
,ota_events e
where o.offering_id = e.parent_offering_id
and event_id = p_event_id)
connect by parent_learning_object_id = prior learning_object_id;
select offr.learning_object_id
into v_lo_id
from ota_offerings offr, ota_events evt
where evt.event_id = p_event_id and
evt.parent_offering_id = offr.offering_id;
Select attempt_id
From ota_attempts
Where event_id = p_event_id
and user_id = p_user_id
and (act_eval_id = l_act_eval_id
or test_id = p_test_id)
and attempt_status = 'C' --bug#7574667
and internal_state = 'F'; --bug#7311115
select eval.eval_mandatory_flag,eval.evaluation_id
from ota_evaluations eval,ota_events evt
where evt.activity_version_id = eval.object_id(+)
and evt.event_id = p_event_id
and eval.evaluation_id is not null --bug 7184369
and (eval.object_type = 'A' or eval.object_type is null);
select type into l_booking_status_type
from ota_booking_status_types
where booking_status_type_id in (p_booking_status_type_id);
Select attempt_id
From ota_attempts
Where event_id = p_event_id
and user_id = p_user_id
and (act_eval_id = l_act_eval_id
or test_id = p_test_id)
and internal_state = 'F';
select eval.eval_mandatory_flag,eval.evaluation_id
from ota_evaluations eval,ota_events evt
where evt.activity_version_id = eval.object_id(+)
and evt.event_id = p_event_id
and eval.evaluation_id is not null --bug 7184369
and (eval.object_type = 'A' or eval.object_type is null);
select type into l_booking_status_type
from ota_booking_status_types
where booking_status_type_id in (p_booking_status_type_id);
select offr.learning_object_id
into v_lo_id
from ota_offerings offr, ota_events evt
where evt.event_id = p_event_id and
evt.parent_offering_id = offr.offering_id;
select
distinct 'found'
from
ota_lo_folders
where
business_group_id = p_business_group_id
and folder_id <> p_folder_id
and parent_folder_id is null;
SELECT ofr.learning_object_id
FROM ota_events oev, ota_offerings ofr
WHERE oev.parent_offering_id = ofr.offering_id
AND oev.event_id = p_event_id;
SELECT lesson_status
FROM ota_performances
WHERE user_id = p_user_id
AND user_type = p_user_type
AND learning_object_id = l_lo_id
AND cert_prd_enrollment_id is null;
SELECT content_player_status
FROM ota_delegate_bookings
WHERE booking_id = p_booking_id;
SELECT offering_id
FROM ota_events
WHERE event_id = p_event_id;
SELECT ofr.learning_object_id
FROM ota_events oev, ota_offerings ofr
WHERE oev.parent_offering_id = ofr.offering_id
AND oev.event_id = p_event_id;
SELECT TO_CHAR(TRUNC(SYSDATE)+(time)/86400, 'HH24:Mi:SS')
FROM ota_performances
WHERE user_id = p_user_id
AND user_type = p_user_type
AND learning_object_id = l_lo_id
AND cert_prd_enrollment_id is null;
SELECT total_training_time
FROM ota_delegate_bookings
WHERE booking_id = p_booking_id;
SELECT offering_id
FROM ota_events
WHERE event_id = p_event_id;
SELECT ofr.learning_object_id
FROM ota_events oev, ota_offerings ofr
WHERE oev.parent_offering_id = ofr.offering_id
AND oev.event_id = p_event_id;
SELECT score
FROM ota_performances
WHERE user_id = p_user_id
AND user_type = p_user_type
AND learning_object_id = l_lo_id
AND cert_prd_enrollment_id is null;
SELECT score
FROM ota_delegate_bookings
WHERE booking_id = p_booking_id;
SELECT offering_id
FROM ota_events
WHERE event_id = p_event_id;
SELECT ocu.online_flag, ofr.learning_object_id,
to_date(to_char(nvl(oev.course_end_date, to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' ' || nvl(oev.course_end_time, '23:59'), 'YYYY/MM/DD HH24:MI'),
ota_timezone_util.convert_date(sysdate, to_char(sysdate,'HH24:MI'), ota_timezone_util.get_server_timezone_code, oev.timezone)
FROM ota_events oev, ota_offerings ofr, ota_category_usages ocu
WHERE oev.parent_offering_id = ofr.offering_id
AND ofr.delivery_mode_id = ocu.category_usage_id
AND oev.event_id = p_event_id;
SELECT BST.type, BST.name
FROM ota_booking_status_types_vl BST
WHERE BST.booking_status_type_id = p_booking_status_type_id;
SELECT lesson_status,
hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',lesson_status)lesson_status_name
FROM ota_performances
WHERE user_id = p_user_id
AND user_type = p_user_type
AND learning_object_id = l_lo_id
AND cert_prd_enrollment_id is null;
SELECT CONTENT_PLAYER_STATUS,
hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',CONTENT_PLAYER_STATUS)lesson_status_name
FROM ota_delegate_bookings
WHERE booking_id = p_booking_id;
SELECT offering_id
FROM ota_events
WHERE event_id = p_event_id;
SELECT decode(prf.lesson_status, 'P', '1',
'C', '2',
'F', '3',
'I', '4',
'B', '5',
'N', '6') decode_lesson_status,
prf.lesson_status lesson_status,
hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS', prf.lesson_status) lesson_status_name
FROM ota_performances prf
WHERE
prf.user_id = p_user_id
and prf.user_type = p_user_type
and prf.learning_object_id = l_lo_id
order by decode_lesson_status;
select delegate_contact_id
from ota_delegate_bookings
where booking_id = p_booking_id;
SELECT offering_id
FROM ota_events
WHERE event_id = p_event_id;
SELECT lesson_status
FROM ota_performances
WHERE learning_object_id = p_lo_id
AND user_id = p_user_id
AND lesson_status IN ('P', 'C');
SELECT type
FROM ota_delegate_bookings odb,
ota_booking_status_types bst
WHERE booking_id = p_booking_id
and odb.booking_status_type_id = bst.booking_status_type_id;
SELECT content_player_status
FROM ota_delegate_bookings
WHERE booking_id = p_booking_id
AND content_player_status in ('P', 'C');
SELECT NLS_LANGUAGE
FROM fnd_languages
WHERE language_code = userenv('LANG');
Select
oev.event_id,
ofr.learning_object_id,
-- to_char(opf.completed_date),
opf.completed_date,
to_char(opf.completed_date, 'HH24:MI:SS'),
opf.lesson_status,
-- Bug#4582683
-- (Select Synchronous_Flag from ota_category_usages where Category_Usage_Id = ofr.Delivery_Mode_Id) Sync_Flag,
-- (Select Online_Flag from ota_category_usages where Category_Usage_Id = ofr.Delivery_Mode_Id) Online_Flag
ocu.Synchronous_Flag Sync_Flag,
ocu.Online_Flag Online_Flag,
ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz
From OTA_EVENTS oev, OTA_OFFERINGS ofr, OTA_PERFORMANCES opf, ota_category_usages ocu
Where
oev.parent_offering_id = ofr.offering_id
And ofr.learning_object_id = opf.learning_object_id(+)
And oev.event_id = p_event_id
And opf.User_id(+) = p_user_id
And opf.User_type(+) = p_user_type
And nvl(opf.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1)
And ocu.Category_Usage_Id = ofr.Delivery_Mode_Id;
Select
oev.event_id,
ofr.learning_object_id,
opf.completed_date,
to_char(opf.completed_date, 'HH24:MI:SS'),
opf.lesson_status,
ocu.Synchronous_Flag Sync_Flag,
ocu.Online_Flag Online_Flag,
ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz,
ota_timezone_util.get_dateDT(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), ocu.Online_Flag, ota_timezone_util.get_server_timezone_code) Comp_Date
From OTA_EVENTS oev, OTA_OFFERINGS ofr, OTA_PERFORMANCES opf, ota_category_usages ocu
Where
oev.parent_offering_id = ofr.offering_id
And ofr.learning_object_id = opf.learning_object_id(+)
And oev.event_id = p_event_id
And opf.User_id(+) = p_user_id
And opf.User_type(+) = p_user_type
And nvl(opf.cert_prd_enrollment_id(+), -1) = nvl(p_cert_prd_enroll_id, -1)
And ocu.Category_Usage_Id = ofr.Delivery_Mode_Id;
SELECT ocu.online_flag, ofr.learning_object_id, nvl(oev.course_end_date, trunc(sysdate))
FROM ota_events oev, ota_offerings ofr, ota_category_usages ocu
WHERE oev.parent_offering_id = ofr.offering_id
AND ofr.delivery_mode_id = ocu.category_usage_id
AND oev.event_id = p_event_id;
SELECT BST.type, BST.name
FROM ota_booking_status_types_vl BST
WHERE BST.booking_status_type_id = p_booking_status_type_id;
SELECT lesson_status,
hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',lesson_status)lesson_status_name
FROM ota_performances
WHERE user_id = p_user_id
AND user_type = p_user_type
AND learning_object_id = l_lo_id
AND cert_prd_enrollment_id = p_cert_prd_enrollment_id;
Select
olo.name Name,
nvl(hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',opf.lesson_status),
hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS','N')) Status,
nvl(opf.time,0) Time,
opf.score Score,
decode(opf.lesson_status
,'C','player_status_c.gif'
,'F','player_status_f.gif'
,'I','player_status_i.gif'
,'P','player_status_p.gif'
,'N','player_status_n.gif'
,null,DECODE(OTA_LO_UTILITY.user_can_attempt_lo(olo.learning_object_id,p_user_id,p_user_type,'', p_cert_prd_enrollment_id),
'N','player_status_no_prereq.gif',
'Y','player_status_n.gif')
,'player_status_n.gif') STATUS_ICON
, tst.grade_flag
, to_char(opf.completed_date)
, to_char(opf.completed_date, 'HH24:MI:SS')
, opf.lesson_status
, ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz
From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,
OTA_TESTS tst
Where
olo.learning_object_id = opf.learning_object_id(+)
AND tst.test_id(+) = olo.test_id
And olo.Learning_object_id = p_lo_id
And opf.User_id(+) = p_user_id
And opf.User_type(+) = p_user_type
AND opf.performance_id(+) = p_performance_id
And opf.cert_prd_enrollment_id(+) = p_cert_prd_enrollment_id;
Select max(per.performance_id)
From ota_performances per
Where per.learning_object_id(+) = p_lo_id
And per.user_id(+) = p_user_id
And per.user_type(+) = p_user_type
And per.cert_prd_enrollment_id(+) = p_cert_prd_enrollment_id;
SELECT cme.cert_mbr_enrollment_id,
cpe.cert_prd_enrollment_id,
cme.object_version_number,
cmb.object_id,
cmb.certification_member_id,
cme.member_status_code
FROM ota_certification_members cmb,
ota_cert_mbr_enrollments cme,
ota_cert_prd_enrollments cpe
WHERE cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
AND cme.cert_member_id = cmb.certification_member_id
AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;
SELECT cre.person_id, cre.contact_id, cpe.cert_period_start_date, cpe.cert_period_end_date
FROM ota_cert_enrollments cre,
ota_cert_prd_enrollments cpe
where cpe.cert_prd_enrollment_id = csr_cert_prd_enrollment_id
and cpe.cert_enrollment_id = cre.cert_enrollment_id;
CURSOR get_enrl_status_on_update(csr_activity_version_id ota_events.activity_version_id%type,
csr_cert_period_start_date in date,
csr_cert_period_end_date in date,
csr_person_id in number,
csr_contact_id in number) IS
SELECT bst.type status,
tdb.DATE_STATUS_CHANGED,
evt.event_id,
evt.event_type,
ocu.synchronous_flag,
ocu.online_flag,
bst.type
FROM ota_events evt,
ota_delegate_bookings tdb,
ota_booking_status_types bst,
ota_offerings ofr,
ota_category_usages ocu
WHERE evt.event_id = tdb.event_id
AND bst.booking_status_type_id = tdb.booking_status_type_id
/* AND (
evt.course_start_date >= csr_cert_period_start_date
AND
(
evt.course_end_date IS NOT NULL
AND evt.course_end_date <= csr_cert_period_end_date
)
OR
(
evt.event_type = 'SELFPACED'
AND csr_cert_period_end_date >= evt.course_start_date
)
)
*/
---
AND ( ( evt.course_start_date >= csr_cert_period_start_date and
nvl(evt.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) <= csr_cert_period_end_date )
/* Bug 4515924
or (evt.event_type ='SELFPACED' and
evt.course_start_date< csr_cert_period_end_date AND
nvl(evt.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) >= csr_cert_period_end_date ))*/
or (evt.event_type = 'SELFPACED' AND
((csr_cert_period_end_date >= evt.course_start_date) AND
((evt.course_end_date is null) or
(evt.course_end_date IS NOT NULL AND evt.course_end_date >= csr_cert_period_start_date))
)))
---
AND evt.activity_version_id = csr_activity_version_id
--AND tdb.delegate_person_id = p_person_id
AND ((csr_person_id IS NOT NULL AND tdb.delegate_person_id = csr_person_id)
OR (csr_contact_id IS NOT NULL AND tdb.delegate_contact_id = csr_contact_id)
)
AND evt.parent_offering_id = ofr.offering_id
AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID
AND tdb.booking_status_type_id = bst.booking_status_type_id
AND bst.type <> 'C';
FOR rec IN get_enrl_status_on_update(rec_cme_info.object_id,
l_cert_period_start_date,
l_cert_period_end_date,
l_person_id,
l_contact_id)
LOOP
if rec.online_flag = 'Y' then
l_online_event_id := rec.event_id;
SELECT
evt.event_id,
evt.event_type,
ocu.synchronous_flag,
ocu.online_flag,
evt.course_start_date,
evt.course_end_date
FROM ota_events evt,
ota_offerings ofr,
ota_category_usages ocu
WHERE evt.event_id = csr_event_id
AND evt.parent_offering_id = ofr.offering_id
AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID;
select cert_prd_enrollment_id
from ota_cert_mbr_enrollments
where cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;
select ofr.player_toolbar_flag
from ota_events evt,
ota_offerings ofr
where evt.parent_offering_id = ofr.offering_id
and evt.event_id = csr_event_id;
SELECT
evt.event_id,
evt.event_type,
ocu.synchronous_flag,
ocu.online_flag,
evt.course_start_date,
evt.course_end_date,
ofr.learning_object_id
FROM ota_events evt,
ota_offerings ofr,
ota_category_usages ocu
WHERE evt.event_id = csr_event_id
AND evt.parent_offering_id = ofr.offering_id
AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID;
select cert_prd_enrollment_id
from ota_cert_mbr_enrollments
where cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;
SELECT lesson_status,
hr_general_utilities.get_lookup_meaning('OTA_CONTENT_PLAYER_STATUS',lesson_status)lesson_status_name
FROM ota_performances
WHERE user_id = p_user_id
AND user_type = p_user_type
AND learning_object_id = l_lo_id
AND cert_prd_enrollment_id = csr_cert_prd_enrollment_id;
select cmb.object_id
from ota_certification_members cmb,
ota_cert_mbr_enrollments cme
where cmb.certification_member_id = cme.cert_member_id
and cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id
and cmb.object_type = 'H';
SELECT prf.user_id,
lo.learning_object_id,
decode(prf.lesson_status, 'P', '1',
'C', '2',
'F', '3',
'I', '4',
'B', '5',
'N', '6') decode_lesson_status,
prf.lesson_status lesson_status,
prf.performance_id
FROM ota_performances prf,
ota_offerings ofr,
ota_learning_objects lo
WHERE
prf.user_id = p_user_id
and prf.user_type = p_user_type
and lo.learning_object_id = prf.learning_object_id
AND prf.cert_prd_enrollment_id = csr_cert_prd_enrollment_id
and ofr.learning_object_id = lo.learning_object_id
and ofr.activity_version_id = p_activity_version_id
order by decode_lesson_status;
SELECT cme.cert_mbr_enrollment_id,
cpe.cert_prd_enrollment_id,
cme.object_version_number,
cmb.object_id,
cmb.certification_member_id,
cme.member_status_code
FROM ota_certification_members cmb,
ota_cert_mbr_enrollments cme,
ota_cert_prd_enrollments cpe
WHERE cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
AND cme.cert_member_id = cmb.certification_member_id
AND cme.cert_mbr_enrollment_id = p_cert_mbr_enrollment_id;
SELECT cre.person_id, cre.contact_id, cpe.cert_period_start_date, cpe.cert_period_end_date
FROM ota_cert_enrollments cre,
ota_cert_prd_enrollments cpe
where cpe.cert_prd_enrollment_id = csr_cert_prd_enrollment_id
and cpe.cert_enrollment_id = cre.cert_enrollment_id;
CURSOR get_enrl_status_on_update(csr_activity_version_id ota_events.activity_version_id%type,
csr_cert_period_start_date in date,
csr_cert_period_end_date in date,
csr_person_id in number,
csr_contact_id in number) IS
SELECT bst.type status,
tdb.DATE_STATUS_CHANGED,
evt.event_id,
evt.event_type,
ocu.synchronous_flag,
ocu.online_flag,
bst.type
FROM ota_events evt,
ota_delegate_bookings tdb,
ota_booking_status_types bst,
ota_offerings ofr,
ota_category_usages ocu
WHERE evt.event_id = tdb.event_id
AND bst.booking_status_type_id = tdb.booking_status_type_id
/* AND (
evt.course_start_date >= csr_cert_period_start_date
AND
(
evt.course_end_date IS NOT NULL
AND evt.course_end_date <= csr_cert_period_end_date
)
OR
(
evt.event_type = 'SELFPACED'
AND csr_cert_period_end_date >= evt.course_start_date
)
)
*/
---
AND ( ( evt.course_start_date >= csr_cert_period_start_date and
nvl(evt.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) <= csr_cert_period_end_date )
/* Bug 4515924 or (evt.event_type ='SELFPACED' and
evt.course_start_date< csr_cert_period_end_date AND
nvl(evt.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) >= csr_cert_period_end_date ))*/
or (evt.event_type = 'SELFPACED' AND
((csr_cert_period_end_date >= evt.course_start_date) AND
((evt.course_end_date is null) or
(evt.course_end_date IS NOT NULL AND evt.course_end_date >= csr_cert_period_start_date))
)))
---
AND evt.activity_version_id = csr_activity_version_id
--AND tdb.delegate_person_id = p_person_id
AND ((csr_person_id IS NOT NULL AND tdb.delegate_person_id = csr_person_id)
OR (csr_contact_id IS NOT NULL AND tdb.delegate_contact_id = csr_contact_id)
)
AND evt.parent_offering_id = ofr.offering_id
AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID
AND tdb.booking_status_type_id = bst.booking_status_type_id
AND bst.type <> 'C';
FOR rec IN get_enrl_status_on_update(rec_cme_info.object_id,
l_cert_period_start_date,
l_cert_period_end_date,
l_person_id,
l_contact_id)
LOOP
if rec.online_flag = 'Y' then
l_online_event_id := rec.event_id;
SELECT lme.lp_member_enrollment_id,
lmb.ACTIVITY_VERSION_ID,
lpe.lp_enrollment_id,
lme.object_version_number,
lmb.learning_path_member_id,
lme.member_status_code,
lpe.person_id,
lpe.contact_id
FROM ota_learning_path_members lmb,
ota_lp_member_enrollments lme,
ota_lp_enrollments lpe
WHERE lpe.lp_enrollment_id = lme.lp_enrollment_id
AND lme.LEARNING_PATH_MEMBER_ID = lmb.LEARNING_PATH_MEMBER_ID
AND lme.lp_member_enrollment_id = p_lp_member_enrollment_id;
SELECT bst.type status,
tdb.DATE_STATUS_CHANGED,
evt.event_id,
evt.event_type,
ocu.synchronous_flag,
ocu.online_flag,
bst.type
FROM ota_events evt,
ota_delegate_bookings tdb,
ota_booking_status_types bst,
ota_offerings ofr,
ota_category_usages ocu
WHERE evt.event_id = tdb.event_id
AND bst.booking_status_type_id = tdb.booking_status_type_id
AND evt.activity_version_id = csr_activity_version_id
--AND tdb.delegate_person_id = p_person_id
AND ((csr_person_id IS NOT NULL AND tdb.delegate_person_id = csr_person_id)
OR (csr_contact_id IS NOT NULL AND tdb.delegate_contact_id = csr_contact_id)
)
AND evt.parent_offering_id = ofr.offering_id
AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID
AND tdb.booking_status_type_id = bst.booking_status_type_id
AND bst.type <> 'C';
SELECT
evt.event_id,
evt.event_type,
ocu.synchronous_flag,
ocu.online_flag,
evt.course_start_date,
evt.course_end_date
FROM ota_events evt,
ota_offerings ofr,
ota_category_usages ocu
WHERE evt.event_id = csr_event_id
AND evt.parent_offering_id = ofr.offering_id
AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID;
select ofr.player_toolbar_flag
from ota_events evt,
ota_offerings ofr
where evt.parent_offering_id = ofr.offering_id
and evt.event_id = csr_event_id;
SELECT
evt.event_id,
evt.event_type,
ocu.synchronous_flag,
ocu.online_flag,
evt.course_start_date,
evt.course_end_date,
ofr.learning_object_id
FROM ota_events evt,
ota_offerings ofr,
ota_category_usages ocu
WHERE evt.event_id = csr_event_id
AND evt.parent_offering_id = ofr.offering_id
AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID;
select lpm.activity_version_id
from ota_learning_path_members lpm,
ota_lp_member_enrollments lme
where lpm.LEARNING_PATH_MEMBER_ID = lme.LEARNING_PATH_MEMBER_ID
and lme.lp_member_enrollment_id = p_lp_member_enrollment_id;
SELECT prf.user_id,
lo.learning_object_id,
decode(prf.lesson_status, 'P', '1',
'C', '2',
'F', '3',
'I', '4',
'B', '5',
'N', '6') decode_lesson_status,
prf.lesson_status lesson_status,
prf.performance_id
FROM ota_performances prf,
ota_offerings ofr,
ota_learning_objects lo
WHERE
prf.user_id = p_user_id
and prf.user_type = p_user_type
and lo.learning_object_id = prf.learning_object_id
and prf.cert_prd_enrollment_id is null
and ofr.learning_object_id = lo.learning_object_id
and ofr.activity_version_id = p_activity_version_id
order by decode_lesson_status;
SELECT lme.lp_member_enrollment_id,
lmb.ACTIVITY_VERSION_ID,
lpe.lp_enrollment_id,
lme.object_version_number,
lmb.learning_path_member_id,
lme.member_status_code,
lpe.person_id,
lpe.contact_id
FROM ota_learning_path_members lmb,
ota_lp_member_enrollments lme,
ota_lp_enrollments lpe
WHERE lpe.lp_enrollment_id = lme.lp_enrollment_id
AND lme.LEARNING_PATH_MEMBER_ID = lmb.LEARNING_PATH_MEMBER_ID
AND lme.lp_member_enrollment_id = p_lp_member_enrollment_id;
SELECT bst.type status,
tdb.DATE_STATUS_CHANGED,
evt.event_id,
evt.event_type,
ocu.synchronous_flag,
ocu.online_flag,
bst.type
FROM ota_events evt,
ota_delegate_bookings tdb,
ota_booking_status_types bst,
ota_offerings ofr,
ota_category_usages ocu
WHERE evt.event_id = tdb.event_id
AND bst.booking_status_type_id = tdb.booking_status_type_id
AND evt.activity_version_id = csr_activity_version_id
--AND tdb.delegate_person_id = p_person_id
AND ((csr_person_id IS NOT NULL AND tdb.delegate_person_id = csr_person_id)
OR (csr_contact_id IS NOT NULL AND tdb.delegate_contact_id = csr_contact_id)
)
AND evt.parent_offering_id = ofr.offering_id
AND OFR.DELIVERY_MODE_ID = ocu.CATEGORY_USAGE_ID
AND tdb.booking_status_type_id = bst.booking_status_type_id
AND bst.type <> 'C';
SELECT cpe.cert_prd_enrollment_id
FROM
OTA_CERTIFICATIONS_B crt,
OTA_CERT_ENROLLMENTS cre,
OTA_CERT_PRD_ENROLLMENTS cpe,
OTA_CERT_MBR_ENROLLMENTS cme,
OTA_CERTIFICATION_MEMBERS cmb,
OTA_EVENTS evt
WHERE crt.CERTIFICATION_ID = cre.CERTIFICATION_ID
AND crt.CERTIFICATION_ID = cmb.CERTIFICATION_ID
AND (cmb.OBJECT_TYPE = 'H' AND cmb.OBJECT_ID = evt.activity_version_id)
AND evt.event_id = p_event_id
AND cme.cert_member_id = cmb.certification_member_id
AND cme.cert_prd_enrollment_id = cpe.CERT_PRD_ENROLLMENT_ID
AND cre.CERT_ENROLLMENT_ID = cpe.CERT_ENROLLMENT_ID
AND cre.BUSINESS_GROUP_ID = OTA_GENERAL.GET_BUSINESS_GROUP_ID
AND ((p_person_id is not null AND cre.PERSON_ID = p_person_id)
OR (p_contact_id is not null AND cre.CONTACT_ID = p_contact_id))
AND (cre.IS_HISTORY_FLAG IS NULL OR cre.IS_HISTORY_FLAG = 'N')
AND (NVL(TRUNC(crt.END_DATE_ACTIVE), TRUNC(SYSDATE)) >= TRUNC(SYSDATE))
AND cre.CERTIFICATION_STATUS_CODE NOT IN ('CANCELLED', 'REJECTED', 'AWAITING_APPROVAL')
AND trunc(sysdate) between trunc(cpe.CERT_PERIOD_START_DATE)
and trunc(cpe.CERT_PERIOD_END_DATE)
AND ( ( evt.course_start_date >= cpe.cert_period_start_date and
nvl(evt.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) <= cpe.cert_period_end_date )
or (evt.event_type = 'SELFPACED' AND
((cpe.cert_period_end_date >= evt.course_start_date) AND
((evt.course_end_date is null) or
(evt.course_end_date IS NOT NULL AND evt.course_end_date >= cpe.cert_period_start_date))
)));
Select null
From ota_learning_objects cld, ota_learning_objects par
Where par.learning_object_id = cld.parent_learning_object_id
And par.starting_url is null
And par.learning_object_id = p_learning_object_id
And rownum = 1;