SELECT stud_comp.stud_id,
stud_comp.cpnt_typ_id,
stud_comp.cpnt_id,
stud_comp.rev_dte,
stud_comp.cmpl_stat_id,
stud_comp.compl_dte,
stud_comp.source_file,
stud_comp.aicc_id,
stud_comp.creator,
stud_comp.environment,
stud_comp.is_active,
stud_comp.has_access,
stud_comp.child_count,
stud_comp.force_order,
stud_comp.expanded,
stud_comp.needs_review,
stud_comp.initial_date,
stud_comp.last_access,
stud_comp.total_time,
stud_comp.total_times,
stud_comp.complete,
stud_comp.finished,
stud_comp.notes,
stud_comp.is_online_only,
stud_comp.has_admin_modified,
stud_comp.create_dte,
stud_comp.is_review_mode,
stud_comp.offline_accessible,
stud_comp.content_zip_url,
stud_comp.last_download_date,
stud_comp.sync_date,
scm.module_id,
scm.module_name,
scm.total_time AS scm_total_time,
Nvl (scm.total_times, 0) AS scm_total_times,
scm.last_access AS scm_last_access,
scm.complete AS scm_complete,
scm.finished AS scm_finished,
scm.has_access AS scm_has_access,
c.rev_num,
c.rtyp_id,
c.dmn_id AS cpnt_domain,
c.del_mth_id,
c.cpnt_src_id,
c.notactive AS cpnt_notactive,
c.cpnt_title,
c.safety_rel,
c.create_dte AS cpnt_create_date,
c.reviser,
c.apprvr,
c.apprvd,
c.apprvl_dte,
c.min_enrl,
c.max_enrl,
c.cpnt_len,
c.prep_len,
c.les_plan,
c.contact,
c.credit_hrs,
c.cpe_hrs,
c.contact_hrs,
c.stud_mats,
c.inst_mats,
c.cpnt_desc,
c.tgt_audnc,
c.comments AS cpnt_comments,
c.grade_opt,
c.enrl_threshold_days,
c.shipping_required,
c.auto_fill_enrl,
c.waitlist_remdr_sent,
c.auto_competency,
c.cpnt_classification,
c.chgbck_method,
c.catalog_sku,
c.schedule_can_override_price,
c.production_ready,
c.cpnt_goals,
c.self_record_lrngevt,
c.sub_record_lrngevt,
c.tap_def_id,
c.approval_reqd,
c.init_period_type_id,
c.init_number,
c.init_basis_type_id,
c.retrng_period_type_id,
c.retrng_number,
c.retrng_basis_type_id,
c.rating,
c.self_enrl,
c.user_req_enabled,
c.user_can_waitlist,
c.cpnt_key,
c.register_online,
c.esig_enabled,
c.lst_upd_usr AS cpnt_last_upd_user,
c.lst_upd_tstmp AS cpnt_last_upd_timestamp,
c.cost_currency_code,
st.stud_id AS stud_stud_id,
st.lname,
st.fname,
st.mi,
st.emp_stat_id,
st.emp_typ_id,
st.jl_id,
st.jp_id,
st.target_jp_id,
st.job_title,
st.dmn_id AS stud_domain,
st.org_id,
st.region_id,
st.co_id,
Decode(st.notactive, 'Y', 'N',
'N', 'Y') AS stud_notactive,
st.addr,
st.city,
st.state,
st.postal,
st.cntry,
st.super,
st.coach_stud_id,
st.hire_dte,
st.term_dte,
st.email_addr,
st.resume_locn,
st.comments AS stud_comments,
st.shipping_name,
st.shipping_contact_name,
st.shipping_addr,
st.shipping_addr1,
st.shipping_city,
st.shipping_state,
st.shipping_postal,
st.shipping_cntry,
st.shipping_phon_num,
st.shipping_fax_num,
st.shipping_email_addr,
st.stud_pswd,
st.pin,
st.pin_date,
st.encrypted,
st.has_access AS stud_has_access,
st.billing_name,
st.billing_contact_name,
st.billing_addr,
st.billing_addr1,
st.billing_city,
st.billing_state,
st.billing_postal,
st.billing_cntry,
st.billing_phon_num,
st.billing_fax_num,
st.billing_email_addr,
st.self_registration,
st.self_registration_date,
st.access_to_org_fin_act,
st.notify_dev_plan_item_add,
st.notify_dev_plan_item_mod,
st.notify_dev_plan_item_remove,
st.notify_when_sub_item_complete,
st.notify_when_sub_item_failure,
st.LOCKED,
st.password_exp_date,
st.security_question,
st.security_answer,
st.role_id,
st.image_id,
st.gender,
st.past_service,
st.lst_unlock_tstmp,
st.lst_upd_usr AS stud_last_upd_user,
st.lst_upd_tstmp AS stud_last_upd_timestamp,
st.manage_sub_sp,
st.manage_own_sp,
scm.stud_cpnt_id AS scm_stud_cpnt_id,
scm.node_level,
scm.node_no,
scm.exam_name,
scm.app_id,
scm.developer_id,
scm.module_type,
scm.is_active AS scm_is_active,
scm.aicc_id AS scm_aicc_id,
scm.aicc_lesson_loc,
scm.parent_id,
scm.child_count AS scm_child_count,
scm.complete_date,
scm.initial_date AS scm_initial_date,
scm.force_order AS scm_force_order,
scm.expanded AS scm_expanded,
scm.needs_review AS scm_need_review,
scm.questions_asked,
scm.questions_correct,
scm.score,
scm.mastery_score,
scm.comments AS scm_comments,
scm.app_cmd_opt,
scm.play_all_scrns,
scm.exam_revw_enabled,
scm.exam_revw_prompt,
scm.complete_on_launch,
scm.has_admin_modified AS scm_has_admin_modified,
scm.exit_component,
scm.trigger_cpnt_pass,
scm.trigger_cpnt_fail,
scm.freeze_cpnt_objs_during_launch,
scm.freezing_other_cpnt_objects,
scm.cmi_completion_status,
scm.success_status,
scm.progress_measure,
scm.cmi_entry,
scm.lesson_location,
scm.score_raw,
scm.score_min,
scm.score_max,
scm.score_scaled,
scm.cmi_exit,
scm.activity_id,
scm.offline_accessible AS scm_offline_accessible,
scm.exam_duration,
scm.exam_review_option,
scm.aicc_data
FROM (SELECT *
FROM (SELECT sc_sub.stud_id,
sc_sub.cpnt_typ_id,
sc_sub.cpnt_id,
sc_sub.rev_dte,
sco.stud_cpnt_id,
sco.cmpl_stat_id,
sco.compl_dte,
sco.source_file,
sco.aicc_id,
sco.creator,
sco.environment,
sco.is_active,
sco.has_access,
sco.child_count,
sco.force_order,
sco.expanded,
sco.needs_review,
sco.initial_date,
sco.last_access,
sco.total_time,
sco.total_times,
sco.complete,
Nvl (sco.finished, 'N') AS finished,
sco.notes,
sco.is_online_only,
sco.has_admin_modified,
sco.create_dte,
sco.is_review_mode,
sco.offline_accessible,
sco.content_zip_url,
sco.last_download_date,
sco.sync_date,
Row_number ()
over (
PARTITION BY sc_sub.stud_id, sc_sub.cpnt_typ_id,
sc_sub.cpnt_id,
sc_sub.rev_dte
ORDER BY sco.create_dte DESC) AS rnum
FROM (SELECT sc.cpnt_typ_id,
sc.cpnt_id,
sc.rev_dte,
sc.stud_id
FROM pv_stud_course sc,
pa_cbt_cpnt cbt
WHERE cbt.cpnt_typ_id = sc.cpnt_typ_id
AND cbt.cpnt_id = sc.cpnt_id
AND cbt.rev_dte = sc.rev_dte
/** and sc.stud_id in [UserSearch] and (sc.cpnt_typ_id, sc.cpnt_id, sc.rev_dte) in [ItemSearch] */
UNION ALL
SELECT sc.cpnt_typ_id,
sc.cpnt_id,
sc.rev_dte,
sc.stud_id
FROM pa_stud_qual_cpnt sc,
pa_cbt_cpnt cbt
WHERE cbt.cpnt_typ_id = sc.cpnt_typ_id
AND cbt.cpnt_id = sc.cpnt_id
AND cbt.rev_dte = sc.rev_dte
/** and sc.stud_id in [UserSearch] and (sc.cpnt_typ_id, sc.cpnt_id, sc.rev_dte) in [ItemSearch] */
) sc_sub,
pa_cbt_stud_cpnt sco
WHERE sco.cpnt_typ_id(+) = sc_sub.cpnt_typ_id
AND sco.cpnt_id(+) = sc_sub.cpnt_id
AND sco.rev_dte(+) = sc_sub.rev_dte
AND sco.stud_id(+) = sc_sub.stud_id)
WHERE rnum = 1)stud_comp,
pv_course c,
pa_student st,
pa_cbt_stud_cpnt_mod scm
WHERE stud_comp.cpnt_typ_id = c.cpnt_typ_id
AND stud_comp.cpnt_id = c.cpnt_id
AND stud_comp.rev_dte = c.rev_dte
AND stud_comp.stud_id = st.stud_id
AND stud_comp.stud_cpnt_id = scm.stud_cpnt_id(+)
/** and stud_comp.initial_date >= [InitialDateFrom] and stud_comp.initial_date < [InitialDateTo] and stud_comp.last_access >= [LastAccessDateFrom] and stud_comp.last_access < [LastAccessDateTo] and stud_comp.compl_dte >= [CompletedDateFrom] and stud_comp.compl_dte <= [CompletedDateTo] and [security:pa_student st] */
ORDER BY c.cpnt_typ_id,
c.cpnt_id,
c.rev_dte,
st.stud_id,
scm_last_access DESC