[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT event_id,
object_version_number,
business_group_id,
title,
course_start_date,
course_start_time,
course_end_date,
course_end_time,
duration,
duration_units,
enrolment_start_date,
enrolment_end_date,
resource_booking_flag,
public_event_flag,
minimum_attendees,
maximum_attendees,
maximum_internal_attendees,
standard_price,
parent_event_id,
book_independent_flag,
actual_cost,
budget_cost,
budget_currency_code,
created_by,
creation_date,
last_updated_by,
last_update_login,
last_update_date,
comments,
evt_information_category,
evt_information1,
evt_information2,
evt_information3,
evt_information4,
evt_information5,
evt_information6,
evt_information7,
evt_information8,
evt_information9,
evt_information10,
evt_information11,
evt_information12,
evt_information13,
evt_information14,
evt_information15,
evt_information16,
evt_information17,
evt_information18,
evt_information19,
evt_information20,
secure_event_flag,
organization_id,
organization_name,
centre,
centre_meaning,
currency_code,
development_event_type,
development_event_type_meaning,
language_code,
language_description,
price_basis,
programme_code,
programme_code_meaning,
event_status,
event_status_meaning,
activity_name,
activity_version_id,
activity_version_name,
event_type,
event_type_meaning,
invoiced_amount,
user_status,
user_status_meaning,
vendor_id,
vendor_name,
project_id,
project_name,
project_number,
line_id,
org_id,
owner_id,
training_center_id,
location_id,
offering_id,
timezone,
inventory_item_id,
parent_offering_id,
data_source
FROM OTA_EVENTS_V
WHERE ((p_event_id IS NULL) OR (p_event_id IS NOT NULL AND event_id = p_event_id))
AND ((p_activity_version_id IS NULL) OR (p_activity_version_id IS NOT NULL AND activity_version_id = p_activity_version_id));
SELECT ctl.name cert_name,
cre.certification_id certification_id,
cre.certification_status_code certification_status_code,
ota_cpe_util.get_cre_status(cre.cert_enrollment_id) cert_status_meaning,
cpe.period_status_code period_status_code,
cpe_lkp.meaning period_status_meaning,
cpe.cert_period_start_date cert_period_start_date,
decode(cre.certification_status_code,'CERTIFIED', decode(crt.renewable_flag,'Y',cre.expiration_date,null), cpe.cert_period_end_date) cert_period_end_date,
cpe.completion_date cre_completion_date,
cre.person_id person_id,
cre.contact_id contact_id,
cre.cert_enrollment_id,
cpe.cert_prd_enrollment_id,
cre.is_history_flag,
crt.renewable_flag,
ota_cpe_util.is_period_renewable(cre.cert_enrollment_id) Is_Period_Renewable,
cre.earliest_enroll_date,
cpe.expiration_date,
crt.start_date_active,
crt.end_date_active
FROM ota_certifications_b crt
,ota_certifications_tl ctl
,ota_cert_enrollments cre
,ota_cert_prd_enrollments cpe
,hr_lookups cpe_lkp
WHERE cre.person_id BETWEEN nvl(p_start_person_id, cre.person_id)
AND nvl(p_end_person_id, cre.person_id)
AND crt.certification_id = cre.certification_id
AND cre.cert_enrollment_id = cpe.cert_enrollment_id(+)
AND crt.certification_id = ctl.certification_id
AND ctl.language = USERENV('LANG')
AND cpe_lkp.lookup_code(+) = cpe.period_status_code
AND cpe_lkp.lookup_type(+) = 'OTA_CERT_PRD_ENROLL_STATUS'
AND cre.certification_status_code = 'CERTIFIED';
select ctl.name cert_name,
cre.certification_id certification_id,
cre.certification_status_code certification_status_code,
ota_cpe_util.get_cre_status(cre.cert_enrollment_id) cert_status_meaning,
cpe.period_status_code period_status_code,
cpe_lkp.meaning period_status_meaning,
cpe.cert_period_start_date cert_period_start_date,
decode(cre.certification_status_code,'CERTIFIED', decode(crt.renewable_flag,'Y',cre.expiration_date,null), cpe.cert_period_end_date) cert_period_end_date,
cpe.completion_date cre_completion_date,
cre.person_id person_id,
cre.contact_id contact_id,
cre.cert_enrollment_id,
cpe.cert_prd_enrollment_id,
cre.is_history_flag,
crt.renewable_flag,
ota_cpe_util.is_period_renewable(cre.cert_enrollment_id) Is_Period_Renewable,
cre.earliest_enroll_date,
cpe.expiration_date,
crt.start_date_active,
crt.end_date_active
FROM ota_certifications_b crt
,ota_certifications_tl ctl
,ota_cert_enrollments cre
,ota_cert_prd_enrollments cpe
,hr_lookups cpe_lkp
WHERE
nvl(p_person_id, cre.person_id) = cre.person_id
AND cre.person_id BETWEEN nvl(p_start_person_id, cre.person_id)
AND nvl(p_end_person_id, cre.person_id)
AND crt.certification_id = cre.certification_id
AND cre.cert_enrollment_id = cpe.cert_enrollment_id(+)
AND crt.certification_id = ctl.certification_id
AND ctl.language = USERENV('LANG')
AND cpe_lkp.lookup_code(+) = cpe.period_status_code
AND cpe_lkp.lookup_type(+) = 'OTA_CERT_PRD_ENROLL_STATUS'
AND ((p_is_history_flag = 'Y' and
((cre.is_history_flag ='Y'
OR (CERTIFICATION_STATUS_CODE IN ('CANCELLED','EXPIRED'))
OR (crt.renewable_flag ='Y' AND PERIOD_STATUS_CODE NOT IN ('ACTIVE','ENROLLED'))
OR (NVL(TRUNC(crt.end_date_active), TRUNC(SYSDATE)) < TRUNC(SYSDATE))) OR (NVL(TRUNC(cpe.cert_period_end_date), TRUNC(SYSDATE)) < TRUNC(SYSDATE))))
OR (p_is_history_flag = 'N' 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 CERTIFICATION_STATUS_CODE NOT IN ('CANCELLED','REJECTED','AWAITING_APPROVAL')
AND ((cpe.cert_prd_enrollment_id IS NULL)
OR (cpe.cert_prd_enrollment_id IS NOT NULL
AND cpe.cert_prd_enrollment_id =
(SELECT MAX(cpe2.cert_prd_enrollment_id)
FROM ota_cert_prd_enrollments cpe2
WHERE cpe2.cert_enrollment_id(+) = cre.cert_enrollment_id))))));
select ctl.name cert_name,
cre.certification_id certification_id,
cre.certification_status_code certification_status_code,
ota_cpe_util.get_cre_status(cre.cert_enrollment_id) cert_status_meaning,
cpe.period_status_code period_status_code,
cpe_lkp.meaning period_status_meaning,
cpe.cert_period_start_date cert_period_start_date,
decode(cre.certification_status_code,'CERTIFIED', decode(crt.renewable_flag,'Y',cre.expiration_date,null), cpe.cert_period_end_date) cert_period_end_date,
cpe.completion_date cre_completion_date,
cre.person_id person_id,
cre.contact_id contact_id,
cre.cert_enrollment_id,
cpe.cert_prd_enrollment_id,
cre.is_history_flag,
crt.renewable_flag,
ota_cpe_util.is_period_renewable(cre.cert_enrollment_id) Is_Period_Renewable,
cre.earliest_enroll_date,
cpe.expiration_date,
crt.start_date_active,
crt.end_date_active
FROM ota_certifications_b crt
,ota_certifications_tl ctl
,ota_cert_enrollments cre
,ota_cert_prd_enrollments cpe
,hr_lookups cpe_lkp
WHERE ((p_person_id IS NULL) OR (p_person_id IS NOT NULL AND cre.person_id = p_person_id))
AND ((p_certification_id IS NULL) OR (cre.certification_id = p_certification_id))
AND crt.certification_id = cre.certification_id
AND cre.cert_enrollment_id = cpe.cert_enrollment_id(+)
AND crt.certification_id = ctl.certification_id
AND ctl.language = USERENV('LANG')
AND cpe_lkp.lookup_code(+) = cpe.period_status_code
AND cpe_lkp.lookup_type(+) = 'OTA_CERT_PRD_ENROLL_STATUS';
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 RENEWABLE_FLAG_CODE
, ota_utility.get_lookup_meaning('YES_NO',b.renewable_flag, '810') renewable_flag_meaning
, b.start_date_active
, B.END_DATE_ACTIVE
, tl.name Name
, tl.description Description
, tl.objectives Objectives
, tl.purpose Purpose
, tl.keywords Keywords
, INITIAL_PERIOD_COMMENTS
, tl.RENEWAL_PERIOD_COMMENTS
from ota_certifications_b b,
ota_certifications_tl tl
where
b.certification_id = tl.certification_id
--and b.business_group_id = ota_general.get_business_group_id
and tl.language = USERENV ('LANG')
and b.certification_id = p_certification_id;
SELECT cre.certification_status_code certification_status_code
, crt_lkp.meaning cert_status_meaning
, cpe.period_status_code period_status_code
, cpe_lkp.meaning period_status_meaning
, cre.expiration_date
, cre.earliest_enroll_date
, cpe.cert_period_start_date cert_period_start_date
, cpe.cert_period_end_date cert_period_end_date
, cre.cert_enrollment_id cert_enrollment_id
, cpe.cert_prd_enrollment_id cert_prd_enrollment_id
, cre.completion_date cre_completion_date
FROM ota_cert_enrollments cre
, ota_cert_prd_enrollments cpe
, hr_lookups crt_lkp
, hr_lookups cpe_lkp
WHERE cre.cert_enrollment_id = cpe.cert_enrollment_id (+)
AND crt_lkp.lookup_code = cre.certification_status_code
AND crt_lkp.lookup_type = 'OTA_CERT_ENROLL_STATUS'
AND cpe_lkp.lookup_code (+) = cpe.period_status_code
AND cpe_lkp.lookup_type (+) = 'OTA_CERT_PRD_ENROLL_STATUS'
AND cre.cert_enrollment_id = p_cert_enrollment_id
AND cpe.cert_prd_enrollment_id (+) = p_cert_prd_enrollment_id;
SELECT comp.competence_id Competence_Id,
cpn.name Competence_Name,
comp.proficiency_level_id Proficiency_Level_Id,
ratl1.step_value || DECODE(ratl1.name,'','', ' - ' || ratl1.name) Proficiency_Level_Name,
comp.effective_date_from Effective_Date_From,
comp.effective_date_to Effective_Date_To,
comp.object_id object_id,
comp.business_group_id Business_Group_Id
FROM per_competence_elements comp,
per_competences_tl cpn, per_rating_levels ratl1
WHERE comp.object_id = p_certification_id
AND comp.type = 'OTA_CERTIFICATION'
AND cpn.competence_id = comp.competence_id
AND comp.proficiency_level_id = ratl1.rating_level_id(+)
AND cpn.language = USERENV('LANG')
ORDER BY COMPETENCE_NAME;
select cme.cert_mbr_enrollment_id cert_mbr_enrollment_id,
tav.activity_version_id Activity_version_Id,
cme.member_status_code member_status_code,
tav.version_name Course_Name,
cme.completion_date completion_date,
lkp.meaning Member_Status_Meaning,
decode( ota_cme_util.chk_active_cme_enrl(cme.cert_mbr_enrollment_id),
'F', 'DetailsIconDisabled',
decode( cme.member_status_code,
'ACTIVE','DetailsIconEnabled',
'CANCELLED','DetailsIconDisabled',
'PLANNED','DetailsIconDisabled',
'AWAITING_APPROVAL','DetailsIconDisabled',
'COMPLETED','DetailsIconEnabled')
) Enrollment_Details_Icon,
tav.Version_Code Version_Code,
tav.Version_Name Activity_Version_Name,
tav.Start_Date Start_Date,
tav.End_Date End_Date,
cmb.certification_member_id certification_member_id,
cmb.MEMBER_SEQUENCE MEMBER_SEQUENCE,
OTA_LO_UTILITY.get_cme_online_event_id(NVL(cre.person_id, cre.contact_id),
DECODE(cre.person_id, NULL, 'C', 'E'),
cme.cert_mbr_enrollment_id) as Event_Id,
OTA_LO_UTILITY.get_cert_lo_status(NVL(cre.person_id, cre.contact_id),
DECODE(cre.person_id, NULL, 'C', 'E'),
cme.cert_mbr_enrollment_id) as Perf_Status, cme.Cert_Prd_Enrollment_Id,
cre.Cert_Enrollment_Id,
cre.Certification_Id,
fnd_profile.value('OTA_ILEARNING_SITE_ADDRESS') AS SITE_ADDRESS ,
fnd_profile.value('OTA_ILEARNING_SITE_ID') AS SITE_SHORT_NAME ,
fnd_profile.value('USERNAME') AS FND_USER_NAME ,
WFA_HTML.CONV_SPECIAL_URL_CHARS(fnd_profile.value('OTA_ILEARNING_SITE_ADDRESS')) Encoded_Site_Address ,
(select e.offering_id
from ota_events e
where e.event_id = OTA_LO_UTILITY.get_cme_online_event_id(NVL(cre.person_id, cre.contact_id),
DECODE(cre.person_id, NULL, 'C', 'E'), cme.cert_mbr_enrollment_id))AS CLASSROOM_ID
from ota_cert_enrollments cre,
ota_cert_prd_enrollments cpe,
ota_cert_mbr_enrollments cme,
ota_certification_members cmb,
ota_activity_versions_vl tav,
hr_lookups lkp
where tav.activity_version_id = cmb.object_id
and cmb.object_type = 'H'
and cmb.certification_member_id = cme.cert_member_id
and lkp.lookup_code = cme.member_status_code
and lkp.lookup_type = 'OTA_CERT_MBR_ENROLL_STATUS'
AND trunc(sysdate) BETWEEN NVL(lkp.start_date_active,trunc(sysdate))
AND NVL (lkp.end_date_active, trunc(sysdate))
AND lkp.enabled_flag ='Y'
and cpe.cert_enrollment_id = cre.cert_enrollment_id
and cme.cert_prd_enrollment_id = cpe.cert_prd_enrollment_id
and cme.cert_prd_enrollment_id = p_cert_prd_enrollment_id
order by MEMBER_SEQUENCE asc;
SELECT oce.person_id,
oce.certification_id
FROM ota_cert_enrollments oce
WHERE oce.cert_enrollment_id = p_cert_enrollment_id;
SELECT *
FROM (
SELECT cpe.cert_prd_enrollment_id,
cre.cert_enrollment_id,
cre.certification_id,
to_char(b.booking_id) Enrollment_Number,
cme.cert_mbr_enrollment_id,
cre.person_id,
cpe.cert_period_start_date,
cpe.cert_period_end_date,
e.course_end_date Course_End,
e.course_start_date event_start_date,
e.event_type event_type
FROM ota_events e,
ota_events_tl et,
hr_all_organization_units o,
hr_all_organization_units_tl haotl,
ota_activity_versions a,
ota_delegate_bookings b,
ota_booking_status_types_VL s,
ota_cert_enrollments cre,
ota_cert_prd_enrollments cpe,
ota_cert_mbr_enrollments cme,
ota_certification_members cmb,
ota_offerings ofr,
ota_category_usages c
WHERE e.event_id = b.event_id
AND cre.cert_enrollment_id = cpe.cert_enrollment_id
AND cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
AND e.event_id= et.event_id
AND s.type <> 'C'
AND et.language = USERENV('LANG')
AND e.training_center_id = o.organization_id(+)
And haotl.organization_id(+) = o.organization_id
AND cme.cert_member_id = cmb.certification_member_id
AND cmb.object_id = a.activity_version_id
AND cmb.object_type = 'H'
AND e.parent_offering_id = ofr.offering_id
And haotl.language(+) = USERENV ('LANG')
AND e.activity_version_id = a.activity_version_id
AND b.booking_status_type_id = s.booking_status_type_id
AND ((cre.person_id IS NOT NULL AND b.delegate_person_id = cre.person_id)
OR
(cre.CONTACT_ID IS NOT NULL AND b.delegate_contact_id = cre.contact_id))
AND E.PARENT_OFFERING_ID=OFR.OFFERING_ID
AND OFR.DELIVERY_MODE_ID = C.CATEGORY_USAGE_ID
) QRSLT
WHERE (enrollment_number = p_booking_id
AND (
( event_start_date >= cert_period_start_date
and nvl(course_end,to_date('4712/12/31', 'YYYY/MM/DD')) <= cert_period_end_date )
or ( event_type ='SELFPACED'
and ((cert_period_end_date >= event_start_date) AND ((course_end is null) or (course_end IS NOT NULL AND course_end >= cert_period_start_date)) ))));
SELECT distinct
a.activity_version_id Activity_Version_Id,
a.version_name Activity_Version_Name,
a.description Activity_Description,
a.objectives Activity_Objectives,
a.intended_audience Activity_Audience,
a.keywords Activity_Keywords,
a.tav_information_category ,
a.tav_information1,
a.tav_information2,
a.tav_information3,
a.tav_information4,
a.tav_information5,
a.tav_information6,
a.tav_information7,
a.tav_information8,
a.tav_information9,
a.tav_information10,
a.tav_information11,
a.tav_information12,
a.tav_information13,
a.tav_information14,
a.tav_information15,
a.tav_information16,
a.tav_information17,
a.tav_information18,
a.tav_information19,
a.tav_information20,
a.Version_Code Activity_Version_Code,
hr_general_utilities.get_lookup_meaning('ACTIVITY_SUCCESS_CRITERIA', a.success_criteria) Success_Criteria,
a.professional_credits,
hr_general.decode_lookup('PROFESSIONAL_CREDIT_TYPE', a.professional_credit_type) Professional_Credit_Meaning,
a.Controlling_Person_Id Controlling_Person_Id,
ST.NAME status,
DECODE(C.ONLINE_FLAG ,'Y',OTA_LO_UTILITY.get_enroll_lo_status(NVL(D.delegate_person_id, D.contact_id), DECODE(D.delegate_person_id, NULL, 'C', 'E') , E.EVENT_ID,D.BOOKING_STATUS_TYPE_ID,D.BOOKING_ID,null,'N'), null) player_status
,D.BOOKING_ID
,D.DELEGATE_PERSON_ID
,D.IS_HISTORY_FLAG
,D.DATE_STATUS_CHANGED
,D.SUCCESSFUL_ATTENDANCE_FLAG
, nvl(D.IS_MANDATORY_ENROLLMENT,'N') is_mandatory_enrollment
,E.EVENT_ID
FROM ota_activity_versions_vl a ,
OTA_EVENTS E,
OTA_EVENTS_TL ET,
OTA_DELEGATE_BOOKINGS D,
OTA_BOOKING_STATUS_TYPES S,
OTA_BOOKING_STATUS_TYPES_TL ST,
OTA_OFFERINGS O,
OTA_OFFERINGS_TL OT,
OTA_CATEGORY_USAGES C,
OTA_CATEGORY_USAGES_TL CT,
OTA_ACTIVITY_VERSIONS_TL OAV,
OTA_EVALUATIONS EVAL
WHERE a.activity_version_id = e.activity_version_id
AND OAV.ACTIVITY_VERSION_ID = a.ACTIVITY_VERSION_ID
AND E.EVENT_ID=D.EVENT_ID
AND S.BOOKING_STATUS_TYPE_ID=D.BOOKING_STATUS_TYPE_ID
--AND E.BUSINESS_GROUP_ID = OTA_GENERAL.GET_BUSINESS_GROUP_ID
AND E.PARENT_OFFERING_ID=O.OFFERING_ID
AND O.DELIVERY_MODE_ID = C.CATEGORY_USAGE_ID
AND a.ACTIVITY_VERSION_ID = O.ACTIVITY_VERSION_ID
AND OAV.LANGUAGE=USERENV('LANG')
AND E.EVENT_ID = ET.EVENT_ID
AND ET.LANGUAGE=USERENV('LANG')
AND S.BOOKING_STATUS_TYPE_ID = ST.BOOKING_STATUS_TYPE_ID
AND ST.LANGUAGE=USERENV('LANG')
AND O.OFFERING_ID = OT.OFFERING_ID
AND OT.LANGUAGE=USERENV('LANG')
AND C.CATEGORY_USAGE_ID = CT.CATEGORY_USAGE_ID
AND CT.LANGUAGE=USERENV('LANG')
AND E.EVENT_TYPE IN ('SCHEDULED','SELFPACED')
AND E.BOOK_INDEPENDENT_FLAG = 'N'
AND E.EVENT_ID = EVAL.OBJECT_ID(+)
AND (EVAL.OBJECT_TYPE is null or EVAL.OBJECT_TYPE = 'E')
AND nvl(p_person_id, D.delegate_person_id) = D.delegate_person_id
AND D.delegate_person_id BETWEEN nvl(p_start_person_id, D.delegate_person_id)
AND nvl(p_end_person_id, D.delegate_person_id)
AND (( ( p_view_history = 'N' AND
((D.IS_HISTORY_FLAG IS NULL OR D.IS_HISTORY_FLAG = 'N')
AND ( E.COURSE_END_DATE IS NULL
OR TO_DATE( TO_CHAR(nvl(E.COURSE_END_DATE,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') ||
' ' || nvl(E.COURSE_END_TIME,'23:59'), 'YYYY/MM/DD HH24:MI')
>= OTA_TIMEZONE_UTIL.CONVERT_DATE(TRUNC(SYSDATE), TO_CHAR(SYSDATE, 'HH24:MI'),
OTA_TIMEZONE_UTIL.GET_SERVER_TIMEZONE_CODE, E.TIMEZONE)
)
AND ((C.ONLINE_FLAG = 'Y' AND S.TYPE IN ('A','P','E')) OR (C.ONLINE_FLAG = 'N' AND S.TYPE in( 'P','E'))))))
OR
(p_view_history = 'Y'
AND ((S.TYPE NOT IN ('R','W'))
AND ((D.IS_HISTORY_FLAG = 'Y')
OR ( E.COURSE_END_DATE IS NOT NULL
AND TO_DATE( TO_CHAR(nvl(E.COURSE_END_DATE,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD')
|| ' ' || nvl(E.COURSE_END_TIME,'23:59'), 'YYYY/MM/DD HH24:MI')
< OTA_TIMEZONE_UTIL.CONVERT_DATE(TRUNC(SYSDATE), TO_CHAR(SYSDATE, 'HH24:MI'),
OTA_TIMEZONE_UTIL.GET_SERVER_TIMEZONE_CODE, E.TIMEZONE)
)
OR (C.ONLINE_FLAG = 'N' AND S.TYPE IN ('A','C'))
OR (C.ONLINE_FLAG = 'Y' AND S.TYPE IN ('C'))
)
)));
SELECT distinct
a.activity_version_id Activity_Version_Id,
a.version_name Activity_Version_Name,
a.description Activity_Description,
a.objectives Activity_Objectives,
a.intended_audience Activity_Audience,
a.keywords Activity_Keywords,
a.tav_information_category ,
a.tav_information1,
a.tav_information2,
a.tav_information3,
a.tav_information4,
a.tav_information5,
a.tav_information6,
a.tav_information7,
a.tav_information8,
a.tav_information9,
a.tav_information10,
a.tav_information11,
a.tav_information12,
a.tav_information13,
a.tav_information14,
a.tav_information15,
a.tav_information16,
a.tav_information17,
a.tav_information18,
a.tav_information19,
a.tav_information20,
a.Version_Code Activity_Version_Code,
hr_general_utilities.get_lookup_meaning('ACTIVITY_SUCCESS_CRITERIA', a.success_criteria) Success_Criteria,
a.professional_credits,
hr_general.decode_lookup('PROFESSIONAL_CREDIT_TYPE', a.professional_credit_type) Professional_Credit_Meaning,
a.Controlling_Person_Id Controlling_Person_Id,
ST.NAME status,
DECODE(C.ONLINE_FLAG ,'Y',OTA_LO_UTILITY.get_enroll_lo_status(NVL(D.delegate_person_id, D.contact_id), DECODE(D.delegate_person_id, NULL, 'C', 'E') , E.EVENT_ID,D.BOOKING_STATUS_TYPE_ID,D.BOOKING_ID,null,'N'), null) player_status
,D.BOOKING_ID
,D.DELEGATE_PERSON_ID
,D.IS_HISTORY_FLAG
,D.DATE_STATUS_CHANGED
,D.SUCCESSFUL_ATTENDANCE_FLAG
, nvl(D.IS_MANDATORY_ENROLLMENT,'N') is_mandatory_enrollment
,E.EVENT_ID
FROM ota_activity_versions_vl a ,
OTA_EVENTS E,
OTA_EVENTS_TL ET,
OTA_DELEGATE_BOOKINGS D,
OTA_BOOKING_STATUS_TYPES S,
OTA_BOOKING_STATUS_TYPES_TL ST,
OTA_OFFERINGS O,
OTA_OFFERINGS_TL OT,
OTA_CATEGORY_USAGES C,
OTA_CATEGORY_USAGES_TL CT,
OTA_ACTIVITY_VERSIONS_TL OAV,
OTA_EVALUATIONS EVAL
WHERE a.activity_version_id = e.activity_version_id
AND OAV.ACTIVITY_VERSION_ID = a.ACTIVITY_VERSION_ID
AND E.EVENT_ID=D.EVENT_ID
AND S.BOOKING_STATUS_TYPE_ID=D.BOOKING_STATUS_TYPE_ID
--AND E.BUSINESS_GROUP_ID = OTA_GENERAL.GET_BUSINESS_GROUP_ID
AND E.PARENT_OFFERING_ID=O.OFFERING_ID
AND O.DELIVERY_MODE_ID = C.CATEGORY_USAGE_ID
AND a.ACTIVITY_VERSION_ID = O.ACTIVITY_VERSION_ID
AND OAV.LANGUAGE=USERENV('LANG')
AND E.EVENT_ID = ET.EVENT_ID
AND ET.LANGUAGE=USERENV('LANG')
AND S.BOOKING_STATUS_TYPE_ID = ST.BOOKING_STATUS_TYPE_ID
AND ST.LANGUAGE=USERENV('LANG')
AND O.OFFERING_ID = OT.OFFERING_ID
AND OT.LANGUAGE=USERENV('LANG')
AND C.CATEGORY_USAGE_ID = CT.CATEGORY_USAGE_ID
AND CT.LANGUAGE=USERENV('LANG')
AND E.EVENT_TYPE IN ('SCHEDULED','SELFPACED')
AND E.BOOK_INDEPENDENT_FLAG = 'N'
AND E.EVENT_ID = EVAL.OBJECT_ID(+)
AND (EVAL.OBJECT_TYPE is null or EVAL.OBJECT_TYPE = 'E')
AND D.BOOKING_ID = p_booking_id;
SELECT
a.activity_version_id Activity_Version_Id,
a.version_name Activity_Version_Name,
a.description Activity_Description,
a.objectives Activity_Objectives,
a.intended_audience Activity_Audience,
a.keywords Activity_Keywords,
a.tav_information_category ,
a.tav_information1,
a.tav_information2,
a.tav_information3,
a.tav_information4,
a.tav_information5,
a.tav_information6,
a.tav_information7,
a.tav_information8,
a.tav_information9,
a.tav_information10,
a.tav_information11,
a.tav_information12,
a.tav_information13,
a.tav_information14,
a.tav_information15,
a.tav_information16,
a.tav_information17,
a.tav_information18,
a.tav_information19,
a.tav_information20,
a.Version_Code Activity_Version_Code,
hr_general_utilities.get_lookup_meaning('ACTIVITY_SUCCESS_CRITERIA', a.success_criteria) Success_Criteria,
a.professional_credits,
hr_general.decode_lookup('PROFESSIONAL_CREDIT_TYPE', a.professional_credit_type) Professional_Credit_Meaning,
a.Controlling_Person_Id Controlling_Person_Id
FROM ota_activity_versions_vl a
WHERE a.activity_version_id = p_course_id;
SELECT distinct
a.activity_version_id Activity_Version_Id,
a.version_name Activity_Version_Name,
a.description Activity_Description,
a.objectives Activity_Objectives,
a.intended_audience Activity_Audience,
a.keywords Activity_Keywords,
a.tav_information_category ,
a.tav_information1,
a.tav_information2,
a.tav_information3,
a.tav_information4,
a.tav_information5,
a.tav_information6,
a.tav_information7,
a.tav_information8,
a.tav_information9,
a.tav_information10,
a.tav_information11,
a.tav_information12,
a.tav_information13,
a.tav_information14,
a.tav_information15,
a.tav_information16,
a.tav_information17,
a.tav_information18,
a.tav_information19,
a.tav_information20,
a.Version_Code Activity_Version_Code,
hr_general_utilities.get_lookup_meaning('ACTIVITY_SUCCESS_CRITERIA', a.success_criteria) Success_Criteria,
a.professional_credits,
hr_general.decode_lookup('PROFESSIONAL_CREDIT_TYPE', a.professional_credit_type) Professional_Credit_Meaning,
a.Controlling_Person_Id Controlling_Person_Id,
ST.NAME status,
DECODE(C.ONLINE_FLAG ,'Y',OTA_LO_UTILITY.get_enroll_lo_status(NVL(D.delegate_person_id, D.contact_id), DECODE(D.delegate_person_id, NULL, 'C', 'E') , E.EVENT_ID,D.BOOKING_STATUS_TYPE_ID,D.BOOKING_ID,null,'N'), null) player_status
,D.BOOKING_ID
,D.DELEGATE_PERSON_ID
,D.IS_HISTORY_FLAG
,D.DATE_STATUS_CHANGED
,D.SUCCESSFUL_ATTENDANCE_FLAG
, nvl(D.IS_MANDATORY_ENROLLMENT,'N') is_mandatory_enrollment
,E.EVENT_ID
FROM ota_activity_versions_vl a ,
OTA_EVENTS E,
OTA_EVENTS_TL ET,
OTA_DELEGATE_BOOKINGS D,
OTA_BOOKING_STATUS_TYPES S,
OTA_BOOKING_STATUS_TYPES_TL ST,
OTA_OFFERINGS O,
OTA_OFFERINGS_TL OT,
OTA_CATEGORY_USAGES C,
OTA_CATEGORY_USAGES_TL CT,
OTA_ACTIVITY_VERSIONS_TL OAV,
OTA_EVALUATIONS EVAL
WHERE a.activity_version_id = e.activity_version_id
AND OAV.ACTIVITY_VERSION_ID = a.ACTIVITY_VERSION_ID
AND E.EVENT_ID=D.EVENT_ID
AND S.BOOKING_STATUS_TYPE_ID=D.BOOKING_STATUS_TYPE_ID
--AND E.BUSINESS_GROUP_ID = OTA_GENERAL.GET_BUSINESS_GROUP_ID
AND E.PARENT_OFFERING_ID=O.OFFERING_ID
AND O.DELIVERY_MODE_ID = C.CATEGORY_USAGE_ID
AND a.ACTIVITY_VERSION_ID = O.ACTIVITY_VERSION_ID
AND OAV.LANGUAGE=USERENV('LANG')
AND E.EVENT_ID = ET.EVENT_ID
AND ET.LANGUAGE=USERENV('LANG')
AND S.BOOKING_STATUS_TYPE_ID = ST.BOOKING_STATUS_TYPE_ID
AND ST.LANGUAGE=USERENV('LANG')
AND O.OFFERING_ID = OT.OFFERING_ID
AND OT.LANGUAGE=USERENV('LANG')
AND C.CATEGORY_USAGE_ID = CT.CATEGORY_USAGE_ID
AND CT.LANGUAGE=USERENV('LANG')
AND E.EVENT_TYPE IN ('SCHEDULED','SELFPACED')
AND E.BOOK_INDEPENDENT_FLAG = 'N'
AND E.EVENT_ID = EVAL.OBJECT_ID(+)
AND (EVAL.OBJECT_TYPE is null or EVAL.OBJECT_TYPE = 'E')
AND D.SUCCESSFUL_ATTENDANCE_FLAG = 'Y'
AND D.delegate_person_id BETWEEN nvl(p_start_person_id, D.delegate_person_id)
AND nvl(p_end_person_id, D.delegate_person_id);
SELECT s.name,
s.type
FROM ota_delegate_bookings b,
ota_booking_status_types_VL s
WHERE b.booking_status_type_id = s.booking_status_type_id
AND b.booking_id = p_delegate_booking_id;