[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select cpe.cert_prd_enrollment_id,
cpe.cert_period_start_date,
cpe.cert_period_end_date,
cre.certification_id
from
ota_activity_versions tav,
ota_cert_enrollments cre,
ota_cert_prd_enrollments cpe,
ota_cert_mbr_enrollments cme,
ota_certification_members cmb,
ota_events evt
where evt.event_id = p_event_id
and evt.activity_version_id = tav.activity_version_id
AND cmb.object_id = tav.activity_version_id
AND cmb.object_type = 'H'
AND cme.cert_member_id = cmb.certification_member_id
AND cme.member_status_code <> 'CANCELLED'
and cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
and cpe.period_status_code = 'COMPLETED'
and cpe.cert_enrollment_id = cre.cert_enrollment_id
and cre.person_id = p_person_id
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
(event_type ='SELFPACED' and cpe.cert_period_end_date >= 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)) );
ota_competence_ss.create_wf_process(p_process =>'OTA_COMPETENCE_UPDATE_JSP_PRC',
p_itemtype =>'HRSSA',
p_person_id => p_person_id,
p_eventid =>null,
p_learningpath_ids => null,
p_certification_id => rec.certification_id ,
p_itemkey =>l_item_key);
Select
tav.activity_version_id
FROM
ota_activity_versions tav,
-- ota_cert_enrollments cre,
ota_cert_prd_enrollments cpe,
ota_cert_mbr_enrollments cme,
ota_certification_members cmb
WHERE
cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
-- AND cre.cert_enrollment_id = cpe.cert_enrollment_id
AND cme.cert_member_id = cmb.certification_member_id
AND cme.member_status_code <> 'CANCELLED'
AND cmb.object_id = tav.activity_version_id
AND cmb.object_type = 'H'
-- AND tav.activity_version_id= evt.activity_version_id
AND cpe.cert_prd_enrollment_id =p_cert_prd_enrollment_id; -- 640, 350, 349
Select tdb.successful_attendance_flag
From ota_events evt,ota_delegate_bookings tdb,ota_booking_status_types bst
Where
evt.activity_version_id = p_act_version_id
and tdb.event_id = evt.event_id
and tdb.booking_status_type_id = bst.booking_status_type_id
and bst.type = 'A'
AND tdb.delegate_person_id = p_person_id
--and tdb.event_id =p_event_id
AND ((evt.course_start_date >= p_cert_period_start_date
and nvl(evt.course_end_date,to_date('4712/12/31', 'YYYY/MM/DD')) <= p_cert_period_end_date )
OR
(event_type ='SELFPACED' and p_cert_period_end_date >= course_start_date)
AND (evt.course_end_date is null or (evt.course_end_date IS NOT NULL AND
evt.course_end_date >= p_cert_period_start_date)) )
Order by Nvl(tdb.Successful_attendance_flag,'N') desc ;
Select cme.cert_mbr_enrollment_id
from ota_cert_mbr_enrollments cme
where member_status_code <> 'CANCELLED'
and member_status_code <> 'COMPLETED'
and cert_prd_enrollment_id = p_cert_prd_enrollment_id and rownum=1;
Select cme.cert_mbr_enrollment_id
from ota_cert_mbr_enrollments cme
where member_status_code = 'COMPLETED'
and cert_prd_enrollment_id = p_cert_prd_enrollment_id and rownum=1;
procedure update_cpe_status(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type
,p_certification_status_code OUT NOCOPY VARCHAR2
,p_enroll_from in varchar2 default null
,p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type default null
,p_child_update_flag in varchar2 default 'Y'
,p_completion_date in date default sysdate)
IS
Cursor csr_mbr_enrl is
Select cme.cert_mbr_enrollment_id
from ota_cert_mbr_enrollments cme
where member_status_code <> 'CANCELLED'
and cert_prd_enrollment_id = p_cert_prd_enrollment_id;
Select cme.cert_mbr_enrollment_id
from ota_cert_mbr_enrollments cme
where member_status_code in ('ACTIVE', 'PENDING', 'COMPLETED') --10164130
and cert_prd_enrollment_id = p_cert_prd_enrollment_id and rownum=1;
select cre.certification_status_code, cre.object_version_number, cre.completion_date,
cre.certification_id,
cre.person_id,
cre.expiration_date, cre.unenrollment_date
FROM ota_cert_enrollments cre,
ota_cert_prd_enrollments cpe
where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
and cpe.cert_enrollment_id = cre.cert_enrollment_id;
select period_status_code, object_version_number, completion_date, cert_enrollment_id, cert_period_end_date
FROM ota_cert_prd_enrollments
where cert_prd_enrollment_id = p_cert_prd_enrollment_id;
Select min(cme.completion_date)
from ota_cert_mbr_enrollments cme
where member_status_code = 'COMPLETED'
and cert_prd_enrollment_id = p_cert_prd_enrollment_id;
l_proc varchar2(72) := g_package || ' update_cpe_status';
if p_child_update_flag = 'Y' then
for rec_mbr_enrl in csr_mbr_enrl
loop
ota_cme_util.update_cme_status(rec_mbr_enrl.cert_mbr_enrollment_id);
--update cpe and cre recs
if n_period_status_code = 'ACTIVE' THEN
l_certification_status_code := 'ENROLLED';
ota_cert_prd_enrollment_api.update_cert_prd_enrollment
(p_effective_date => trunc(sysdate)
,p_cert_enrollment_id => l_prd_enrl_rec.cert_enrollment_id
,p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
,p_object_version_number => l_prd_enrl_rec.object_version_number
,p_period_status_code => n_period_status_code
,p_completion_date => l_completion_date);
ota_cert_enrollment_api.update_cert_enrollment
(p_effective_date => trunc(sysdate)
,p_cert_enrollment_id => l_prd_enrl_rec.cert_enrollment_id
,p_certification_id => l_cert_enrl_rec.certification_id
,p_object_version_number => l_cert_enrl_rec.object_version_number
,p_certification_status_code => l_certification_status_code
,p_expiration_date => l_expiration_date
,p_completion_date => l_completion_date
,p_unenrollment_date => l_unenrollment_date);
ota_cert_prd_enrollment_api.update_cert_prd_enrollment
(p_effective_date => trunc(sysdate)
,p_cert_enrollment_id => l_prd_enrl_rec.cert_enrollment_id
,p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
,p_object_version_number => l_prd_enrl_rec.object_version_number
,p_period_status_code => l_prd_enrl_rec.period_status_code
,p_cert_period_end_date => p_cert_period_end_date);
end update_cpe_status;
select
b.certification_id certification_id
, b.RENEWABLE_FLAG
, b.INITIAL_COMPLETION_DURATION
, cre.expiration_date
from ota_certifications_b b,
ota_cert_enrollments cre
where cre.certification_id = b.certification_id
and cre.cert_enrollment_id = p_cert_enrollment_id;
select
max(cpe.expiration_date)
from ota_cert_prd_enrollments cpe,
ota_cert_enrollments cre
where cpe.cert_enrollment_id = cre.cert_enrollment_id
and cre.cert_enrollment_id = p_cert_enrollment_id;
select 'Y' as dupl_prd_exists
from ota_cert_enrollments
where cert_enrollment_id = p_cert_enrollment_id
and expiration_date = csr_expiration_date;
select
b.certification_id certification_id
, b.INITIAL_COMPLETION_DATE
, b.INITIAL_COMPLETION_DURATION
, b.INITIAL_COMPL_DURATION_UNITS
, b.RENEWAL_DURATION
, b.RENEWAL_DURATION_UNITS
, b.NOTIFY_DAYS_BEFORE_EXPIRE
, b.VALIDITY_DURATION
, b.VALIDITY_DURATION_UNITS
, b.RENEWABLE_FLAG
, b.VALIDITY_START_TYPE
, b.PUBLIC_FLAG
, b.START_DATE_ACTIVE
, b.END_DATE_ACTIVE
from ota_certifications_b b,
ota_cert_enrollments cre
where cre.certification_id = b.certification_id
and cre.cert_enrollment_id = p_cert_enrollment_id;
--popl the values initially and update this with compl date on the day learner completes
l_next_earliest_enroll_date := p_expiration_date;
select
b.certification_id certification_id
, b.INITIAL_COMPLETION_DATE
, b.INITIAL_COMPLETION_DURATION
, b.INITIAL_COMPL_DURATION_UNITS
, b.RENEWAL_DURATION
, b.RENEWAL_DURATION_UNITS
, b.NOTIFY_DAYS_BEFORE_EXPIRE
, b.VALIDITY_DURATION
, b.VALIDITY_DURATION_UNITS
, b.RENEWABLE_FLAG
, b.VALIDITY_START_TYPE
, b.PUBLIC_FLAG
, b.START_DATE_ACTIVE
, b.END_DATE_ACTIVE
from ota_certifications_b b
where certification_id = p_certification_id;
select
trunc(cre.earliest_enroll_date) earliest_enroll_date --bug#8775942
, trunc(cre.expiration_date) expiration_date
from ota_certifications_b b,
ota_cert_enrollments cre
where cre.certification_id = b.certification_id
and cre.cert_enrollment_id = p_cert_enrollment_id;
select
max(cpe.expiration_date)
from ota_cert_prd_enrollments cpe,
ota_cert_enrollments cre
where cpe.cert_enrollment_id = cre.cert_enrollment_id
and cre.cert_enrollment_id = p_cert_enrollment_id;
select
b.certification_id certification_id
, b.INITIAL_COMPLETION_DATE
, b.INITIAL_COMPLETION_DURATION
, b.INITIAL_COMPL_DURATION_UNITS
, b.validity_duration
, b.START_DATE_ACTIVE
, b.END_DATE_ACTIVE
, cre.expiration_date
, b.renewable_flag --Bug 4545407
from ota_certifications_b b,
ota_cert_enrollments cre
where cre.certification_id = b.certification_id
and cre.cert_enrollment_id = p_cert_enrollment_id;
select
max(cpe.expiration_date)
from ota_cert_prd_enrollments cpe,
ota_cert_enrollments cre
where cpe.cert_enrollment_id = cre.cert_enrollment_id
and cre.cert_enrollment_id = p_cert_enrollment_id;
Select member_status_code
,ota_utility.get_lookup_meaning('OTA_CERT_MBR_ENROLL_STATUS', member_status_code, 810) member_status
From ota_cert_mbr_enrollments
where
cert_member_id = p_cert_mbr_id
and cert_prd_enrollment_id = p_cert_prd_enrollment_id;
Select object_id, object_type
From ota_certification_members
Where certification_member_id = p_cert_mbr_id;
Select version_name
From ota_activity_versions_vl
Where activity_version_id = p_act_ver_id;
Select cre.certification_status_code,
cre.completion_date,
crt.validity_duration,
ota_utility.get_lookup_meaning('OTA_CERT_ENROLL_STATUS', cre.certification_status_code, 810) cre_status,
crt.renewable_flag,
cre.expiration_date,
nvl(crt.end_date_active,to_date('4712/12/31','YYYY/MM/DD')) end_date_active
From ota_cert_enrollments cre,
ota_certifications_b crt
where cre.cert_enrollment_id = p_cert_enrollment_id
and crt.certification_id = cre.certification_id;
Select
get_cre_status(cre.cert_enrollment_id, 'c') certification_status_code
From ota_cert_enrollments cre,
ota_cert_prd_enrollments cpe
where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
and cre.cert_enrollment_id = cpe.cert_enrollment_id;
p_mass_update_flag in varchar2 default 'N') return varchar2 IS
CURSOR csr_cert_enrl IS
select cre.certification_status_code,
cre.completion_date,
cre.certification_id,
cpe.expiration_date,
cpe.cert_period_end_date,
cpe.period_status_code,
crt.renewable_flag,
crt.initial_completion_duration,
crt.validity_duration,
crt.validity_start_type
FROM ota_cert_enrollments cre,
ota_cert_prd_enrollments cpe,
ota_certifications_b crt
where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
and cre.certification_id = crt.certification_id
and cpe.cert_enrollment_id = cre.cert_enrollment_id;
if p_mass_update_flag = 'N' then
hr_multi_message.enable_message_list;
if p_mass_update_flag = 'N' then
fnd_message.set_name('OTA','OTA_443893_CPE_PRD_END_DT_ERR');
if p_mass_update_flag = 'N' then
l_return_status := hr_multi_message.get_return_status_disable;
if p_mass_update_flag = 'N' then
if hr_multi_message.exception_add(p_associated_column1 => 'OTA_CERT_PRD_ENROLLMENTS.CERT_PERIOD_END_DATE') then
return hr_multi_message.get_return_status_disable;
if p_mass_update_flag = 'N' then
l_return_status := hr_multi_message.get_return_status_disable;
select
b.certification_id certification_id
, b.INITIAL_COMPLETION_DATE
, b.INITIAL_COMPLETION_DURATION
, b.INITIAL_COMPL_DURATION_UNITS
, b.RENEWAL_DURATION
, b.RENEWAL_DURATION_UNITS
, b.NOTIFY_DAYS_BEFORE_EXPIRE
, b.VALIDITY_DURATION
, b.VALIDITY_DURATION_UNITS
, b.RENEWABLE_FLAG
, b.VALIDITY_START_TYPE
, b.PUBLIC_FLAG
, b.START_DATE_ACTIVE
, b.END_DATE_ACTIVE
from ota_certifications_b b
where b.certification_id = p_certification_id;
select
cmb.CERTIFICATION_MEMBER_ID
, cmb.CERTIFICATION_ID
, cmb.OBJECT_ID
, cmb.OBJECT_TYPE
, cmb.MEMBER_SEQUENCE
, cmb.START_DATE_ACTIVE
, cmb.END_DATE_ACTIVE
from ota_certification_members cmb
where cmb.certification_id = p_certification_id
and trunc(sysdate) between trunc(cmb.START_DATE_ACTIVE) and nvl(trunc(cmb.end_date_active), to_date('4712/12/31', 'YYYY/MM/DD'));
select certification_id, cert_enrollment_id, business_group_id, certification_status_code,
object_version_number, completion_date, expiration_date
FROM ota_cert_enrollments
where cert_enrollment_id = p_cert_enrollment_id;
select period_status_code, object_version_number, completion_date
FROM ota_cert_prd_enrollments
where cert_prd_enrollment_id = csr_cert_prd_enrollment_id;
Select cme.cert_mbr_enrollment_id
from ota_cert_mbr_enrollments cme
where member_status_code in ('ACTIVE', 'PENDING', 'COMPLETED') --10164130
and cert_prd_enrollment_id = l_cert_prd_enrollment_id and rownum=1;
select count(cert_prd_enrollment_id)
FROM ota_cert_prd_enrollments
where cert_enrollment_id = p_cert_enrollment_id;
Select min(cme.completion_date)
from ota_cert_mbr_enrollments cme
where member_status_code = 'COMPLETED'
and cert_prd_enrollment_id = l_cert_prd_enrollment_id;
ota_cert_prd_enrollment_api.update_cert_prd_enrollment
(p_effective_date => trunc(sysdate)
,p_cert_enrollment_id => p_cert_enrollment_id
,p_cert_prd_enrollment_id => l_cert_prd_enrollment_id
,p_object_version_number => l_prd_enrl_rec.object_version_number
,p_period_status_code => n_period_status_code
,p_completion_date => l_prd_completion_date);
ota_cert_enrollment_api.update_cert_enrollment
(p_effective_date => trunc(sysdate)
,p_cert_enrollment_id => p_cert_enrollment_id
,p_certification_id => l_cert_enrl_rec.certification_id
,p_object_version_number => l_cert_enrl_rec.object_version_number
,p_certification_status_code => l_certification_status_code
,p_is_history_flag => 'N'
,p_completion_date => l_completion_date
,p_expiration_date => l_expiration_date);
PROCEDURE delete_prd_cascade(p_cert_prd_enrollment_id IN ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
p_return_code OUT NOCOPY varchar2) IS
CURSOR csr_attempt IS
SELECT attempt_id
FROM ota_attempts
WHERE cert_prd_enrollment_id = p_cert_prd_enrollment_id
FOR UPDATE;
SELECT objective_id
FROM OTA_SCORM_OBJ_ATTEMPTS
WHERE attempt_id = p_attempt_id
FOR UPDATE;
SELECT USER_TEST_QUESTION_ID
FROM OTA_UTEST_QUESTIONS
WHERE attempt_id = p_attempt_id
FOR UPDATE;
DELETE FROM OTA_SCORM_OBJ_PERFS
WHERE objective_id = scorm_attempt_rec.objective_id;
DELETE FROM OTA_SCORM_OBJ_ATTEMPTS
WHERE CURRENT OF csr_scorm_attempt;
--Delete attempt specific test data, OTA_UTEST_QUESTIONS
--OTA_UTEST_RESPONSES
FOR utest_ques_rec IN csr_utest_ques(attempt_rec.attempt_id)
LOOP
--for each utest ques delete utest responses
DELETE FROM OTA_UTEST_RESPONSES
WHERE USER_TEST_QUESTION_ID = utest_ques_rec.USER_TEST_QUESTION_ID;
--Delete the fetched utest ques record
DELETE FROM OTA_UTEST_QUESTIONS
WHERE CURRENT OF csr_utest_ques;
--Delete the fetched attempt record
DELETE FROM OTA_ATTEMPTS
WHERE CURRENT OF csr_attempt;
--delete cert prd performance
DELETE FROM ota_performances
WHERE cert_prd_enrollment_id = p_cert_prd_enrollment_id;
--delete cert mbr enrollments
DELETE FROM ota_cert_mbr_enrollments where cert_prd_enrollment_id = p_cert_prd_enrollment_id;
DELETE FROM ota_cert_prd_enrollments where cert_prd_enrollment_id = p_cert_prd_enrollment_id;
END delete_prd_cascade;
procedure update_admin_changes(p_cert_enrollment_id in ota_cert_prd_enrollments.cert_enrollment_id%type,
p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
p_certification_status_code in ota_cert_enrollments.certification_status_code%type,
p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type default null,
p_return_status out NOCOPY VARCHAR2,
p_cert_period_completion_date in ota_cert_prd_enrollments.completion_date%type default trunc(sysdate),
p_cert_upd_comp in varchar2 default 'N') is
CURSOR csr_cert_enrl IS
select cre.certification_status_code, cre.object_version_number, cre.completion_date,
cre.certification_id,
cre.person_id,
cre.expiration_date
FROM ota_cert_enrollments cre
where cre.cert_enrollment_id = p_cert_enrollment_id;
select period_status_code, object_version_number, completion_date, cert_enrollment_id, cert_period_start_date, cert_period_end_date
FROM ota_cert_prd_enrollments
where cert_prd_enrollment_id = p_cert_prd_enrollment_id;
l_proc varchar2(72) := g_package || ' update_admin_changes';
l_is_period_update boolean := false;
l_is_period_update := true;
l_is_period_update := false;
ota_cert_enrollment_api.update_cert_enrollment
(p_effective_date => trunc(sysdate)
,p_cert_enrollment_id => p_cert_enrollment_id
,p_certification_id => l_cert_enrl_rec.certification_id
,p_object_version_number => l_cert_enrl_rec.object_version_number
,p_certification_status_code => l_new_cre_status_code);
if l_is_period_update then
OPEN csr_prd_enrl;
ota_cert_prd_enrollment_api.update_cert_prd_enrollment
(p_effective_date => trunc(sysdate)
,p_cert_enrollment_id => p_cert_enrollment_id
,p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
,p_object_version_number => l_prd_enrl_rec.object_version_number
,p_period_status_code => l_new_cpe_status_code
,p_cert_period_end_date => l_cert_period_end_date);
ota_cert_enrollment_api.update_cert_enrollment
(p_effective_date => trunc(sysdate)
,p_cert_enrollment_id => p_cert_enrollment_id
,p_certification_id => l_cert_enrl_rec.certification_id
,p_object_version_number => l_cert_enrl_rec.object_version_number
,p_certification_status_code => l_new_cre_status_code
,p_completion_date => l_cert_period_completion_date);
if l_is_period_update then
OPEN csr_prd_enrl;
ota_cert_prd_enrollment_api.update_cert_prd_enrollment
(p_effective_date => trunc(sysdate)
,p_cert_enrollment_id => p_cert_enrollment_id
,p_cert_prd_enrollment_id => p_cert_prd_enrollment_id
,p_object_version_number => l_prd_enrl_rec.object_version_number
,p_period_status_code => l_new_cpe_status_code
,p_cert_period_end_date => l_cert_period_end_date
,p_completion_date => l_cert_period_completion_date);
ota_competence_ss.create_wf_process(p_process =>'OTA_COMPETENCE_UPDATE_JSP_PRC',
p_itemtype =>'HRSSA',
p_person_id => l_cert_enrl_rec.person_id,
p_eventid =>null,
p_learningpath_ids => null,
p_certification_id => l_cert_enrl_rec.certification_id ,
p_itemkey =>l_item_key);
if l_is_period_update then
update_cpe_status(p_cert_prd_enrollment_id, l_certification_status_code, null, p_cert_period_end_date);
ota_cert_enrollment_api.update_cert_enrollment
(p_effective_date => trunc(sysdate)
,p_cert_enrollment_id => p_cert_enrollment_id
,p_certification_id => l_cert_enrl_rec.certification_id
,p_object_version_number => l_cert_enrl_rec.object_version_number
,p_certification_status_code => l_new_cre_status_code
,p_unenrollment_date => null);
elsif (p_certification_status_code is null and l_is_period_update) then
--mass update scrn with just due date chg
update_cpe_status(p_cert_prd_enrollment_id, l_certification_status_code, null, p_cert_period_end_date);
End update_admin_changes;
Procedure update_cert_status_to_expired(
ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2) is
l_proc varchar2(72) := g_package || ' update_cert_status_to_expired';
Select ceb.certification_id
,ceb.name
,enr.cert_enrollment_id
,enr.person_id
,enr.contact_id
,prd.cert_prd_enrollment_id
,prd.period_status_code
,prd.cert_period_end_date
,prd.object_version_number
From ota_certifications_vl ceb
,ota_cert_enrollments enr
,ota_cert_prd_enrollments prd
Where
ceb.certification_id = enr.certification_id
and enr.cert_enrollment_id = prd.cert_enrollment_id
and trunc(sysdate) between nvl(trunc(ceb.start_date_active), trunc(sysdate)) and
nvl(trunc(ceb.end_date_active), trunc(sysdate))
and prd.period_status_code in ('ACTIVE', 'ENROLLED', 'INCOMPLETE');
Select ceb.certification_id
,ceb.name
,enr.cert_enrollment_id
,enr.person_id
,enr.contact_id
,prd.cert_prd_enrollment_id
,enr.certification_status_code
,enr.expiration_date
,prd.period_status_code
,prd.cert_period_end_date
,ceb.initial_completion_date
,enr.object_version_number
From ota_certifications_vl ceb
,ota_cert_enrollments enr
,ota_cert_prd_enrollments prd
Where
ceb.certification_id = enr.certification_id
and enr.cert_enrollment_id = prd.cert_enrollment_id
and trunc(sysdate) between nvl(trunc(ceb.start_date_active), trunc(sysdate))
and nvl(trunc(ceb.end_date_active), trunc(sysdate))
and enr.certification_status_code in ('ENROLLED', 'CERTIFIED', 'RENEWING') -- Bug#7303995
--check if this is max/latest prd then update CRE accordingly
and prd.cert_prd_enrollment_id = (select max(cpex.cert_prd_enrollment_id)
from ota_cert_prd_enrollments cpex
where cpex.cert_enrollment_id = enr.cert_enrollment_id
and trunc(cpex.cert_period_end_date) < trunc(sysdate)
)
and prd.cert_prd_enrollment_id = p_cert_prd_enrollment_id;
savepoint update_status_to_expired;
--Update Period CPE status to Expired
ota_cert_prd_enrollment_api.update_cert_prd_enrollment(
p_effective_date => trunc(sysdate),
p_cert_prd_enrollment_id => exp_sts_upd.cert_prd_enrollment_id,
p_object_version_number => exp_sts_upd.object_version_number,
p_cert_enrollment_id => exp_sts_upd.cert_enrollment_id,
p_period_status_code => 'EXPIRED');
--Update Cert CRE status to Expired
for exp_records in get_data_for_expired_records(exp_sts_upd.cert_prd_enrollment_id)
Loop
--if condition to consider bug 4642943
if ( ( exp_records.expiration_date is not null and
trunc(sysdate) > trunc(exp_records.expiration_date) )
or exp_records.initial_completion_date is not null) then
--Update CRE status to Expired
ota_cert_enrollment_api.update_cert_enrollment(
p_effective_date => trunc(sysdate),
p_cert_enrollment_id => exp_records.cert_enrollment_id,
p_certification_id => exp_records.certification_id,
p_object_version_number => exp_records.object_version_number,
p_certification_status_code => 'EXPIRED');
|| 'Update to cpe cre calls ,' || 'Cert_Prd_Enrollment_Id=' || to_char(exp_sts_upd.cert_prd_enrollment_id)
||',' || 'Cert_Enrollment_Id=' || to_char(exp_sts_upd.cert_enrollment_id)
|| ',' || SUBSTR(SQLERRM, 1, 500));
ROLLBACK TO update_status_to_expired;
end update_cert_status_to_expired;
select cpe.cert_prd_enrollment_id,
cpe.period_status_code,
cpe.cert_period_start_date
from ota_cert_prd_enrollments cpe,
ota_cert_enrollments cre
where cpe.period_status_code not in ('COMPLETED', 'CANCELLED') and
cpe.cert_enrollment_id = cre.cert_enrollment_id --bug#6338987
order by cre.certification_id;
select cme.cert_mbr_enrollment_id,
cmb.object_id
from ota_cert_mbr_enrollments cme,
ota_certification_members cmb
where cme.cert_prd_enrollment_id = p_cert_prd_enrollment_id
and cme.cert_member_id = cmb.certification_member_id
and object_type = 'H';
do_update boolean := false;
ota_cme_util.get_enrl_status_on_update(rec_acty_info.object_id,
rec_cpe_status.cert_prd_enrollment_id,
l_booking_status_type,
l_date_status_changed);
-- if l_booking_status_type is 'A' then bypass update for EXPIRED periods for COMPLETED child status
if l_booking_status_type = 'A' then
if rec_cpe_status.period_status_code <> 'EXPIRED' then
if trunc(l_date_status_changed) = trunc(rec_cpe_status.cert_period_start_date) then
--scenarios 1 and 2
-- for 'Attended' class enroll status perform status rollup for CURRENT CPE
hr_utility.set_location('Step:'||l_proc, 10.2);
do_update := true;
do_update := true;
if do_update then
ota_cme_util.update_cme_status(rec_acty_info.cert_mbr_enrollment_id);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updated the Certification member status for the cert_mbr_enrollment_id = ' || rec_acty_info.cert_mbr_enrollment_id);
ota_cme_util.update_cpe_status(rec_acty_info.cert_mbr_enrollment_id, l_cert_prd_enrollment_id, trunc(l_date_status_changed));
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updated the Certification period status for the cert_prd_enrollment_id = ' || l_cert_prd_enrollment_id);
do_update := false;
Select cpe.period_status_code Period_Status_Code,
ota_utility.get_lookup_meaning('OTA_CERT_PRD_ENROLL_STATUS', cpe.period_status_code, 810) Period_Status_Meaning,
cpe.cert_prd_enrollment_id
From ota_cert_prd_enrollments cpe
where cpe.cert_enrollment_id = p_cert_enrollment_id
-- and rownum = 1 --Bug#6356854
order by cpe.cert_prd_enrollment_id desc;
select
b.certification_id certification_id
, b.INITIAL_COMPLETION_DATE
, b.VALIDITY_DURATION
, b.VALIDITY_DURATION_UNITS
, b.renewable_flag
from ota_certifications_b b
where b.certification_id = p_certification_id;
select csu.online_flag,
csu.synchronous_flag,
ofr.offering_id
from ota_category_usages csu,
ota_offerings ofr,
ota_events evt
where evt.event_id = p_event_id
and evt.parent_offering_id = ofr.offering_id
and ofr.delivery_mode_id = csu.category_usage_id;
select act.activity_version_id
from ota_activity_versions act,
ota_offerings ofr
where ofr.offering_id = csr_offering_id
and ofr.activity_version_id = act.activity_version_id;
SELECT null
FROM ota_certification_members cmb,
ota_cert_mbr_enrollments cme,
ota_cert_prd_enrollments cpe,
ota_cert_enrollments cre
WHERE
cre.cert_enrollment_id = cpe.cert_enrollment_id
AND cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
AND cme.cert_member_id = cmb.certification_member_id
AND cmb.object_id = l_activity_version_id
--pull only curr periods
AND trunc(sysdate) between trunc(cpe.cert_period_start_date) and trunc(cpe.cert_period_end_date)
-- don't consider expired or canc prds
AND cpe.period_status_code not in ('COMPLETED','EXPIRED','CANCELLED');
6)update the new calculated dates
NOTE:This procedure will correct late subscription or late renewal
only for internal employees. Contacts will not be cosidered
*/
procedure sync_late_subsc_to_class IS
l_proc varchar2(72) := g_package || ' sync_late_subsc_to_class';
SELECT ocb.certification_id,
ocb.name,
ocb.start_date_active,
nvl(ocb.end_date_active, to_date('4712/12/31','YYYY/MM/DD')) end_date_active,
oce.enrollment_date,
nvl(ocb.initial_completion_duration, (ocb.initial_completion_date - oce.enrollment_date)) initial_completion_duration,
ocb.renewable_flag,
ocb.validity_duration,
oce.cert_enrollment_id,
oce.person_id,
oce.contact_id,
ocpe.cert_prd_enrollment_id,
ocpe.cert_period_start_date,
ocpe.cert_period_end_date,
ocpe.expiration_date
FROM ota_certifications_vl ocb, ota_cert_enrollments oce, ota_cert_prd_enrollments ocpe
WHERE ocb.certification_id = oce.certification_id
AND oce.cert_enrollment_id = ocpe.cert_enrollment_id
AND trunc(SYSDATE) BETWEEN trunc(ocb.start_date_active) AND trunc(nvl(ocb.end_date_active, SYSDATE))
AND ocb.renewable_flag = 'Y'
AND oce.person_id is not null
AND oce.certification_status_code <> 'CERTIFIED'
AND ocpe.period_status_code not in ('COMPLETED', 'CANCELLED')
AND ocpe.cert_prd_enrollment_id = (SELECT MAX(cert_prd_enrollment_id) FROM ota_cert_prd_enrollments ocpe1
WHERE oce.cert_enrollment_id = ocpe1.cert_enrollment_id);
SELECT cme.cert_mbr_enrollment_id,
cmb.object_id
FROM ota_cert_mbr_enrollments cme,
ota_certification_members cmb
WHERE cme.cert_prd_enrollment_id = p_cert_prd_enrollment_id
AND cme.cert_member_id = cmb.certification_member_id
AND object_type = 'H'
AND member_status_code = 'PLANNED';
SELECT DECODE(bst.type,'C','Z',bst.type) status,
evt.event_type,
tdb.DATE_STATUS_CHANGED,
evt.COURSE_START_DATE,
evt.COURSE_END_DATE
FROM ota_events evt,
ota_delegate_bookings tdb,
ota_booking_status_types bst
WHERE evt.event_id = tdb.event_id
AND bst.booking_status_type_id = tdb.booking_status_type_id
AND (
--sync sched, online(conf) or offline(ILT)
--sync always have an end date
( evt.event_type = 'SCHEDULED' and
evt.course_start_date >= csr_cert_period_start_date and
evt.course_end_date <= csr_cert_period_end_date )
or
--async selfpaced, online(selfp) or offline(CBT)
--async have opt end date
(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 ((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)
)
order by status;
SELECT certification_id,
cert_enrollment_id,
business_group_id,
certification_status_code,
object_version_number,
completion_date,
earliest_enroll_date
FROM ota_cert_enrollments
WHERE cert_enrollment_id = p_cert_enrollment_id;
select cert_enrollment_id, cert_prd_enrollment_id, period_status_code, object_version_number, completion_date
FROM ota_cert_prd_enrollments
where cert_prd_enrollment_id = csr_cert_prd_enrollment_id;
ota_cert_enrollment_api.update_cert_enrollment
(p_effective_date => sysdate
,p_cert_enrollment_id => l_cert_enrl_rec.cert_enrollment_id
,p_certification_id => l_cert_enrl_rec.certification_id
,p_object_version_number => l_cert_enrl_rec.object_version_number
,p_certification_status_code => l_cert_enrl_rec.certification_status_code
,p_is_history_flag => 'N'
,p_earliest_enroll_date => l_earliest_enroll_date
,p_completion_date => l_cert_completion_date
);
--UPDATE cert period enrollment dates
OPEN csr_prd_enrl(rec_cert_details.cert_prd_enrollment_id);
ota_cert_prd_enrollment_api.update_cert_prd_enrollment
(p_effective_date => trunc(sysdate)
,p_cert_enrollment_id => l_prd_enrl_rec.cert_enrollment_id
,p_cert_prd_enrollment_id => l_prd_enrl_rec.cert_prd_enrollment_id
,p_object_version_number => l_prd_enrl_rec.object_version_number
,p_period_status_code => l_prd_enrl_rec.period_status_code
,p_completion_date => l_cert_prd_completion_date
,p_cert_period_start_date => l_cert_period_start_date
,p_cert_period_end_date => l_cert_period_end_date
,p_expiration_date => l_expiration_date
);