select dc.community_id from imsld_cp_manifestsi im, dotlrn_communities dc, acs_objects ao, acs_objects ao2 where im.item_id = ao.object_id and ao.context_id = ao2.object_id and ao2.context_id = dc.package_id and im.manifest_id = :manifest_id select icm.manifest_id, ii.imsld_id, im.method_id, ir.run_id, ca.time_in_seconds, ao.creation_date from imsld_cp_manifestsi icm, imsld_cp_organizationsi ico, imsld_imsldsi ii, imsld_methodsi im, imsld_complete_actsi ca, imsld_runs ir, acs_objects ao where im.imsld_id = ii.item_id and ii.imsld_id = ir.imsld_id and ii.organization_id = ico.item_id and ico.manifest_id = icm.item_id and im.complete_act_id = ca.item_id and ca.time_in_seconds is not null and ao.object_id = ir.run_id and content_revision__is_live(ii.imsld_id) = 't' select icm.manifest_id, ii.imsld_id, im.method_id, ir.run_id, ca.time_in_seconds, ao.creation_date from imsld_cp_manifestsi icm, imsld_cp_organizationsi ico, imsld_imsldsi ii, imsld_methodsi im, imsld_complete_actsi ca, imsld_runs ir, acs_objects ao where im.imsld_id = ii.item_id and ii.imsld_id = ir.imsld_id and ii.organization_id = ico.item_id and ico.manifest_id = icm.item_id and im.complete_act_id = ca.item_id and ca.time_in_seconds is not null and ao.object_id = ir.run_id and content_revision__is_live(ii.imsld_id) = 't' and im.item_id = :activity_id select 1 where (extract(epoch from now()) - extract(epoch from timestamp :creation_date) - :time_in_seconds > 0) select u.user_id from users u, acs_rels ar, imsld_run_users_group_ext r_map where u.user_id > 0 and u.user_id=ar.object_id_two and ar.object_id_one = r_map.group_id and r_map.run_id = :run_id select u.user_id from users u, acs_rels ar, imsld_run_users_group_ext r_map where u.user_id > 0 and u.user_id=ar.object_id_two and ar.object_id_one = r_map.group_id and r_map.run_id = :run_id select icm.manifest_id, ii.imsld_id, ip.play_id, ca.time_in_seconds, ao.creation_date, ir.run_id from imsld_cp_manifestsi icm, imsld_cp_organizationsi ico, imsld_imsldsi ii, imsld_methodsi im, imsld_plays ip, imsld_complete_actsi ca, imsld_runs ir, acs_objects ao where ip.method_id = im.item_id and im.imsld_id = ii.item_id and ii.organization_id = ico.item_id and ico.manifest_id = icm.item_id and ip.complete_act_id = ca.item_id and ca.time_in_seconds is not null and ao.object_id = ir.run_id and content_revision__is_live(ii.imsld_id) = 't' and ii.imsld_id = ir.imsld_id select icm.manifest_id, ii.imsld_id, ip.play_id, ca.time_in_seconds, ao.creation_date, ir.run_id from imsld_cp_manifestsi icm, imsld_cp_organizationsi ico, imsld_imsldsi ii, imsld_methodsi im, imsld_plays ip, imsld_complete_actsi ca, imsld_runs ir, acs_objects ao where ip.method_id = im.item_id and im.imsld_id = ii.item_id and ii.organization_id = ico.item_id and ico.manifest_id = icm.item_id and ip.complete_act_id = ca.item_id and ca.time_in_seconds is not null and ao.object_id = ir.run_id and content_revision__is_live(ii.imsld_id) = 't' and ii.imsld_id = ir.imsld_id and ip.item_id = :activity_id select icm.manifest_id, ii.imsld_id, ip.play_id, ia.act_id, ca.time_in_seconds, icm.creation_date, ir.run_id from imsld_cp_manifestsi icm, imsld_cp_organizationsi ico, imsld_imsldsi ii, imsld_methodsi im, imsld_playsi ip, imsld_acts ia, imsld_complete_actsi ca, imsld_runs ir, acs_objects ao where ia.play_id = ip.item_id and ip.method_id = im.item_id and im.imsld_id = ii.item_id and ii.organization_id = ico.item_id and ico.manifest_id = icm.item_id and ia.complete_act_id = ca.item_id and ca.time_in_seconds is not null and ao.object_id = ir.run_id and content_revision__is_live(ii.imsld_id) = 't' and ii.imsld_id = ir.imsld_id select icm.manifest_id, ii.imsld_id, ip.play_id, ia.act_id, ca.time_in_seconds, icm.creation_date, ir.run_id from imsld_cp_manifestsi icm, imsld_cp_organizationsi ico, imsld_imsldsi ii, imsld_methodsi im, imsld_playsi ip, imsld_acts ia, imsld_complete_actsi ca, imsld_runs ir, acs_objects ao where ia.play_id = ip.item_id and ip.method_id = im.item_id and im.imsld_id = ii.item_id and ii.organization_id = ico.item_id and ico.manifest_id = icm.item_id and ia.complete_act_id = ca.item_id and ca.time_in_seconds is not null and ao.object_id = ir.run_id and content_revision__is_live(ii.imsld_id) = 't' and ii.imsld_id = ir.imsld_id and ia.item_id = :activity_id select sa.item_id as sa_item_id, sa.activity_id, ca.time_in_seconds from imsld_support_activitiesi sa, imsld_complete_actsi ca where sa.complete_act_id = ca.item_id and content_revision__is_live(ca.complete_act_id) = 't' and ca.time_in_seconds is not null select sa.item_id as sa_item_id, sa.activity_id, ca.time_in_seconds from imsld_support_activitiesi sa, imsld_complete_actsi ca where sa.complete_act_id = ca.item_id and content_revision__is_live(ca.complete_act_id) = 't' and ca.time_in_seconds is not null and sa.item_id = :activity_id select icm.manifest_id, irp.role_part_id, ii.imsld_id, ip.play_id, ia.act_id, ao.creation_date, ir.run_id from imsld_cp_manifestsi icm, imsld_cp_organizationsi ico, imsld_imsldsi ii, imsld_methodsi im, imsld_playsi ip, imsld_actsi ia, imsld_role_partsi irp, imsld_runs ir, acs_objects ao where irp.support_activity_id = :sa_item_id and irp.act_id = ia.item_id and ia.play_id = ip.item_id and ip.method_id = im.item_id and im.imsld_id = ii.item_id and ii.organization_id = ico.item_id and ii.imsld_id = ir.imsld_id and ao.object_id = ir.run_id and ico.manifest_id = icm.item_id and content_revision__is_live(ii.imsld_id) = 't' select icm.manifest_id, irp.role_part_id, ii.imsld_id, ip.play_id, ia.act_id, ao.creation_date, ir.run_id from imsld_cp_manifestsi icm, imsld_cp_organizationsi ico, imsld_imsldsi ii, imsld_methodsi im, imsld_playsi ip, imsld_actsi ia, imsld_role_partsi irp, imsld_runs ir, acs_objects ao where irp.support_activity_id = :sa_item_id and irp.act_id = ia.item_id and ia.play_id = ip.item_id and ip.method_id = im.item_id and im.imsld_id = ii.item_id and ii.organization_id = ico.item_id and ii.imsld_id = ir.imsld_id and ao.object_id = ir.run_id and ico.manifest_id = icm.item_id and content_revision__is_live(ii.imsld_id) = 't' select la.item_id as la_item_id, la.activity_id, ca.time_in_seconds from imsld_learning_activitiesi la, imsld_complete_actsi ca where la.complete_act_id = ca.item_id and content_revision__is_live(ca.complete_act_id) = 't' and ca.time_in_seconds is not null select la.item_id as la_item_id, la.activity_id, ca.time_in_seconds from imsld_learning_activitiesi la, imsld_complete_actsi ca where la.complete_act_id = ca.item_id and content_revision__is_live(ca.complete_act_id) = 't' and ca.time_in_seconds is not null and la.item_id = :activity_id select icm.manifest_id, irp.role_part_id, ii.imsld_id, ip.play_id, ia.act_id, ao.creation_date, ir.run_id from imsld_cp_manifestsi icm, imsld_cp_organizationsi ico, imsld_imsldsi ii, imsld_methodsi im, imsld_playsi ip, imsld_actsi ia, imsld_role_partsi irp, imsld_runs ir, acs_objects ao where irp.role_part_id = :role_part_id and irp.act_id = ia.item_id and ia.play_id = ip.item_id and ip.method_id = im.item_id and im.imsld_id = ii.item_id and ii.organization_id = ico.item_id and ii.imsld_id = ir.imsld_id and ao.object_id = ir.run_id and ico.manifest_id = icm.item_id and content_revision__is_live(ii.imsld_id) = 't' select icm.manifest_id, irp.role_part_id, ii.imsld_id, ip.play_id, ia.act_id, ao.creation_date, ir.run_id from imsld_cp_manifestsi icm, imsld_cp_organizationsi ico, imsld_imsldsi ii, imsld_methodsi im, imsld_playsi ip, imsld_actsi ia, imsld_role_partsi irp, imsld_runs ir, acs_objects ao where irp.role_part_id = :role_part_id and irp.act_id = ia.item_id and ia.play_id = ip.item_id and ip.method_id = im.item_id and im.imsld_id = ii.item_id and ii.organization_id = ico.item_id and ii.imsld_id = ir.imsld_id and ao.object_id = ir.run_id and ico.manifest_id = icm.item_id and content_revision__is_live(ii.imsld_id) = 't' select item_id as role_part_item_id from imsld_role_partsi where role_part_id = :role_part_id select 1 from imsld_status_user where run_id = :run_id and user_id = :user_id and status = 'started' and act_id = :act_id and related_id = :role_part_id insert into imsld_status_user (imsld_id, run_id, play_id, act_id, related_id, user_id, type, status_date, status) ( select :imsld_id, :run_id, :play_id, :act_id, :role_part_id, :user_id, 'act', now(), 'started' where not exists (select 1 from imsld_status_user where run_id = :run_id and user_id = :user_id and related_id = :role_part_id and status = 'started') ) insert into imsld_status_user (imsld_id, run_id, play_id, act_id, related_id, user_id, type, status_date, status) ( select :imsld_id, :run_id, :play_id, :act_id, :role_part_id, :user_id, 'act', now(), 'finished' where not exists (select 1 from imsld_status_user where run_id = :run_id and user_id = :user_id and related_id = :role_part_id and status = 'finished') ) select case when learning_activity_id is not null then 'learning' when support_activity_id is not null then 'support' when activity_structure_id is not null then 'structure' else 'none' end as type, content_item__get_live_revision(coalesce(learning_activity_id,support_activity_id,activity_structure_id)) as activity_id, coalesce(learning_activity_id, support_activity_id, activity_structure_id) as activity_item_id from imsld_role_parts where role_part_id = :role_part_id select 1 from forums_forums ff where ff.forum_id=:the_object_id select acs_object_id as the_object_id from imsld_cp_resourcesi where item_id = :the_resource_id and acs_object_id is not null select ar.object_id_two as related_cr_items from acs_rels ar where ar.object_id_one=:the_resource_id and ar.rel_type='imsld_res_files_rel' select item_id as act_item_id from imsld_actsi where act_id = :act_id insert into imsld_status_user (imsld_id, run_id, play_id, related_id, user_id, type, status_date, status) ( select :imsld_id, :run_id, :play_id, :act_id, :user_id, 'act', now(), 'finished' where not exists (select 1 from imsld_status_user where run_id = :run_id and user_id = :user_id and related_id = :act_id and status = 'finished') ) select rp.role_part_id from imsld_role_parts rp, imsld_actsi ia where rp.act_id = ia.item_id and ia.act_id = :act_id and content_revision__is_live(rp.role_part_id) = 't' insert into imsld_status_user (imsld_id, run_id, related_id, user_id, type, status_date, status) ( select :imsld_id, :run_id, :play_id, :user_id, 'play', now(), 'finished' where not exists (select 1 from imsld_status_user where run_id = :run_id and user_id = :user_id and related_id = :play_id and status = 'finished') ) select ia.act_id from imsld_acts ia, imsld_playsi ip where ia.play_id = ip.item_id and ip.play_id = :play_id and content_revision__is_live(ia.act_id) = 't' insert into imsld_status_user (imsld_id, run_id, related_id, user_id, type, status_date, status) ( select :imsld_id, :run_id, :imsld_id, :user_id, 'play', now(), 'finished' where not exists (select 1 from imsld_status_user where run_id = :run_id and user_id = :user_id and related_id = :imsld_id and status = 'finished') ) select ip.play_id from imsld_plays ip, imsld_methodsi im, imsld_imsldsi ii where ip.method_id = im.item_id and im.imsld_id = ii.item_id and ii.imsld_id = :imsld_id insert into imsld_status_user (imsld_id, run_id, related_id, user_id, type, status_date, status) ( select :imsld_id, :run_id, :method_id, :user_id, 'method', now(), 'finished' where not exists (select 1 from imsld_status_user where run_id = :run_id and user_id = :user_id and related_id = :method_id and status = 'finished') ) select ip.play_id from imsld_plays ip, imsld_methodsi im where ip.method_id = im.item_id and im.method_id = :method_id select t.table_name from acs_object_types t where t.object_type = :rel_type select r.rel_id from acs_rels r where r.rel_type = :rel_type BEGIN acs_rel_type.drop_type( rel_type => :rel_type, cascade_p => 't' ); END; select 1 from imsld_learning_activities where activity_id = :activity_id where learning_activity_id = :activity_id where support_activity_id = :activity_id select 1 from imsld_runtime_activities_rels where role_id = :role_id and activity_id = :activity_id and run_id = :run_id update imsld_attribute_instances set is_visible_p = 't' where user_id = :recipient_user_id and owner_id = :activity_id insert into imsld_notifications_history (run_id, from_user_id, notification_date, target_activity_id, to_user_id) values (:run_id, :user_id, now(), :activity_id, :recipient_user_id) select 1 from imsld_status_user where run_id = :run_id and user_id = :user_id and status = 'started' and related_id = :element_id and imsld_id = :imsld_id and play_id = :play_id and role_part_id = :role_part_id insert into imsld_status_user ( imsld_id, run_id, play_id, act_id, role_part_id, related_id, user_id, type, status_date, status ) ( select :imsld_id, :run_id, :play_id, :act_id, :role_part_id, :element_id, :user_id, :type, now(), 'started' where not exists (select 1 from imsld_status_user where run_id = :run_id and user_id = :user_id and related_id = :element_id and status = 'started') ) select on_completion_id as related_on_completion from $table_name where $element_name=:element_id and on_completion_id is not null select ar2.object_id_two as related_resource from acs_rels ar1, acs_rels ar2 where ar2.object_id_one=ar1.object_id_two and ar2.rel_type='imsld_item_res_rel' and ar1.rel_type='imsld_feedback_rel' and ar1.object_id_one=:related_on_completion select ar.object_id_two as change_prop_val_id from acs_rels ar where ar.object_id_one = :related_on_completion and ar.rel_type='imsld_on_comp_change_pv_rel' select ino.subject, ino.activity_id, ino.notification_id, ino.item_id as notification_item_id from imsld_notificationsi ino, acs_rels ar where ar.object_id_two = ino.item_id and ar.object_id_one = :related_on_completion and ar.rel_type = 'imsld_on_comp_notif_rel' select ir.role_id, data.mail_data, data.email_property_id, data.username_property_id from imsld_send_mail_datai data, acs_rels ar, imsld_rolesi ir where ar.object_id_two = data.item_id and ar.rel_type = 'imsld_notif_email_rel' and ar.object_id_one = :notification_item_id and ir.item_id = data.role_id and content_revision__is_live(ir.role_id) = 't' select property_id from imsld_propertiesi where item_id = :username_property_id and content_revision__is_live(property_id) = 't' select property_id from imsld_propertiesi where item_id = :email_property_id and content_revision__is_live(property_id) = 't' insert into imsld_status_user ( imsld_id, run_id, play_id, act_id, role_part_id, related_id, user_id, type, status_date, status ) ( select :imsld_id, :run_id, :play_id, :act_id, :role_part_id, :element_id, :user_id, :type, now(), 'finished' where not exists (select 1 from imsld_status_user where run_id = :run_id and user_id = :user_id and related_id = :element_id and status = 'finished') ) select ici.condition_xml from imsld_conditionsi ici, acs_rels ar, ${table_name}i ilai where ilai.item_id=ar.object_id_one and ar.rel_type='imsld_ilm_cond_rel' and ilai.${element_name}=:element_id and ici.item_id=ar.object_id_two order by ici.condition_id select ias.structure_id, ias.item_id as structure_item_id, coalesce(ias.number_to_select, 0) as number_to_select from acs_rels ar, imsld_activity_structuresi ias, cr_items cri where ar.object_id_one = ias.item_id and ar.object_id_two = cri.item_id and cri.live_revision = :element_id select content_item__get_live_revision(ar.object_id_two) as activity_id from acs_rels ar where ar.object_id_one = :structure_item_id and ar.rel_type in ('imsld_as_la_rel','imsld_as_sa_rel','imsld_as_as_rel') select count(*) from imsld_status_user where related_id = :activity_id and user_id = :user_id and status = 'finished' and run_id = :run_id select 1 from imsld_status_user where related_id = :role_part_id and user_id = :user_id and status = 'finished' and run_id = :run_id select ii.imsld_id, ip.play_id, ip.item_id as play_item_id, ia.act_id, ia.item_id as act_item_id, ica.when_last_act_completed_p, im.method_id, im.item_id as method_item_id from imsld_imsldsi ii, imsld_actsi ia, imsld_role_parts irp, imsld_methodsi im, imsld_playsi ip left outer join imsld_complete_actsi ica on (ip.complete_act_id = ica.item_id) where irp.role_part_id = :role_part_id and irp.act_id = ia.item_id and ia.play_id = ip.item_id and ip.method_id = im.item_id and im.imsld_id = ii.item_id and content_revision__is_live(ii.imsld_id) = 't'; select ar.object_id_two as role_part_item_id, rp.role_part_id from acs_rels ar, imsld_role_partsi rp where ar.object_id_one = :act_item_id and rp.item_id = ar.object_id_two and ar.rel_type = 'imsld_act_rp_completed_rel' and content_revision__is_live(rp.role_part_id) = 't' select irp.role_part_id from imsld_role_parts irp, imsld_rolesi iri where content_revision__is_live(irp.role_part_id)='t' and irp.act_id=:act_item_id and irp.role_id=iri.item_id and iri.role_id in ([join $user_roles_list ","]) select ia.act_id from imsld_acts ia, imsld_playsi ip where ia.play_id = :play_item_id and ip.item_id = ia.play_id and content_revision__is_live(ia.act_id) = 't' select ip.play_id from acs_rels ar, imsld_playsi ip where ar.object_id_one = :method_item_id and ip.item_id = ar.object_id_two and ar.rel_type = 'imsld_mp_completed_rel' and content_revision__is_live(ip.play_id) = 't' select ip.play_id from imsld_plays ip where ip.method_id = :method_item_id and content_revision__is_live(ip.play_id) = 't' select ar.object_id_two, ar.rel_type, ar.rel_id from acs_rels ar, imsld_activity_structuresi ias, cr_revisions r where ar.object_id_one = ias.item_id and ias.structure_id = :activity_structure_id and r.item_id = ar.object_id_two and content_revision__is_live(r.revision_id) = 't' order by ar.object_id_two select la.activity_id as learning_activity_id, complete_act_id from imsld_learning_activitiesi la where la.item_id = :object_id_two and content_revision__is_live(la.activity_id) = 't' select count(*) from imsld_status_user where related_id = :learning_activity_id and user_id = :user_id and status = 'finished' and run_id = :run_id select activity_id as support_activity_id from imsld_support_activitiesi where item_id = :object_id_two and content_revision__is_live(activity_id) = 't' select count(*) from imsld_status_user where related_id = :support_activity_id and user_id = :user_id and status = 'finished' and run_id = :run_id select structure_id, title, item_id from imsld_activity_structuresi where item_id = :object_id_two and content_revision__is_live(structure_id) = 't' select count(*) from imsld_status_user where related_id = :structure_id and user_id = :user_id and status = 'finished' and run_id = :run_id select 1 from imsld_status_user where related_id = :role_part_id and user_id = :user_id and status = 'finished' and run_id = :run_id select case when learning_activity_id is not null then 'learning' when support_activity_id is not null then 'support' when activity_structure_id is not null then 'structure' else 'none' end as type, learning_activity_id, support_activity_id, activity_structure_id from imsld_role_parts where role_part_id = :role_part_id select count(*) from imsld_status_user where related_id = content_item__get_live_revision(:learning_activity_id) and user_id = :user_id and status = 'finished' and run_id = :run_id select count(*) from imsld_status_user where related_id = content_item__get_live_revision(:support_activity_id) and user_id = :user_id and status = 'finished' and run_id = :run_id select count(*) from imsld_status_user where related_id = content_item__get_live_revision(:activity_structure_id) and user_id = :user_id and status = 'finished' and run_id = :run_id select 1 from imsld_status_user where related_id = :act_id and user_id = :user_id and status = 'finished' and run_id = :run_id select 1 from imsld_status_user where related_id = :play_id and user_id = :user_id and status = 'finished' and run_id = :run_id select 1 from imsld_status_user where related_id = :method_id and user_id = :user_id and status = 'finished' and run_id = :run_id select 1 from imsld_status_user where related_id = :imsld_id and user_id = :user_id and status = 'finished' and run_id = :run_id select serv.service_id, serv.identifier, serv.service_type, serv.title as service_title from imsld_servicesi serv where serv.item_id = :service_item_id and content_revision__is_live(serv.service_id) = 't' select conf.conference_id, conf.conference_type, conf.imsld_item_id as imsld_item_item_id, cr.live_revision as imsld_item_id, conf.title as conf_title from imsld_conference_servicesi conf, cr_items cr where conf.service_id = :service_item_id and cr.item_id = conf.imsld_item_id and content_revision__is_live(cr.live_revision) = 't' select cpr.resource_id, cpr.item_id as resource_item_id, cpr.type as resource_type from imsld_cp_resourcesi cpr, imsld_itemsi ii, acs_rels ar where ar.object_id_one = ii.item_id and ar.object_id_two = cpr.item_id and content_revision__is_live(cpr.resource_id) = 't' and (imsld_tree_sortkey between tree_left((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) and tree_right((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) or ii.imsld_item_id = :imsld_item_id) select sm.title as send_mail_title, sm.mail_id as sendmail_id from imsld_send_mail_servicesi sm, acs_rels ar where sm.service_id = :service_item_id and sm.item_id = ar.object_id_one and ar.rel_type = 'imsld_send_mail_serv_data_rel' and content_revision__is_live(sm.mail_id) = 't' select ims.title as monitor_service_title, ims.monitor_id, ims.item_id as monitor_item_id, ims.self_p, ims.role_id, cr.live_revision as imsld_item_id from imsld_monitor_servicesi ims, cr_items cr where ims.service_id = :service_item_id and cr.item_id = ims.imsld_item_id and content_revision__is_live(cr.live_revision) = 't' select icr.resource_id, cr.item_id as resource_item_id, icr.type as resource_type from imsld_cp_resources icr, imsld_items ii, cr_items ci, cr_items cr, acs_rels ar where ar.object_id_one = ci.item_id and ci.live_revision = ii.imsld_item_id and ar.object_id_two = cr.item_id and cr.live_revision = icr.resource_id and content_revision__is_live(icr.resource_id) = 't' and (imsld_tree_sortkey between tree_left((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) and tree_right((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) or ii.imsld_item_id = :imsld_item_id) select env.title as environment_title, env.environment_id from imsld_environmentsi env where env.item_id = :environment_item_id and content_revision__is_live(env.environment_id) = 't' select lo.item_id as learning_object_item_id, lo.learning_object_id, lo.identifier, coalesce(lo.title,lo.identifier) as lo_title, lo.class from imsld_learning_objectsi lo, imsld_attribute_instances attr where lo.environment_id = :environment_item_id and content_revision__is_live(lo.learning_object_id) = 't' and attr.owner_id = lo.learning_object_id and attr.run_id = :run_id and attr.user_id = :user_id and attr.type = 'isvisible' and attr.is_visible_p = 't' order by lo.creation_date select ii.imsld_item_id from imsld_items ii, cr_items cr, acs_rels ar where ar.object_id_one = :learning_object_item_id and ar.object_id_two = cr.item_id and cr.live_revision = ii.imsld_item_id select cpr.resource_id, cr2.item_id as resource_item_id, cpr.type as resource_type from imsld_cp_resources cpr, imsld_items ii, imsld_attribute_instances attr, acs_rels ar, cr_items cr1, cr_items cr2 where ar.object_id_one = cr1.item_id and ar.object_id_two = cr2.item_id and cr1.live_revision = ii.imsld_item_id and cr2.live_revision = cpr.resource_id and (imsld_tree_sortkey between tree_left((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) and tree_right((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) or ii.imsld_item_id = :imsld_item_id) and attr.owner_id = ii.imsld_item_id and attr.run_id = :run_id and attr.user_id = :user_id and attr.type = 'isvisible' and attr.is_visible_p = 't' select ise.service_id, ise.item_id as service_item_id, ise.identifier, ise.service_type, ise.title as service_title, ise.class from imsld_servicesi ise, imsld_attribute_instances attr where ise.environment_id = :environment_item_id and content_revision__is_live(ise.service_id) = 't' and attr.owner_id = ise.service_id and attr.run_id = :run_id and attr.user_id = :user_id and attr.type = 'isvisible' and attr.is_visible_p = 't' select ar.object_id_two as nested_environment_item_id from acs_rels ar where ar.object_id_one = :environment_item_id and ar.rel_type = 'imsld_env_env_rel' select learning_objective_id as learning_objective_item_id from imsld_imsldsi where item_id = :imsld_item_id and content_revision__is_live(imsld_id) = 't' select learning_objective_id as learning_objective_item_id from imsld_learning_activitiesi where item_id = :activity_item_id and content_revision__is_live(activity_id) = 't' select lo.pretty_title as objective_title, lo.learning_objective_id from imsld_learning_objectivesi lo where lo.item_id = :learning_objective_item_id and content_revision__is_live(lo.learning_objective_id) = 't' select ii.imsld_item_id from imsld_items ii, cr_items cr, acs_rels ar where ar.object_id_one = :learning_objective_item_id and ar.object_id_two = cr.item_id and cr.live_revision = ii.imsld_item_id select cpr.resource_id, cpr.item_id as resource_item_id, cpr.type as resource_type from imsld_cp_resourcesi cpr, imsld_itemsi ii, imsld_attribute_instances attr, acs_rels ar where ar.object_id_one = ii.item_id and ar.object_id_two = cpr.item_id and content_revision__is_live(cpr.resource_id) = 't' and (imsld_tree_sortkey between tree_left((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) and tree_right((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) or ii.imsld_item_id = :imsld_item_id) and attr.owner_id = ii.imsld_item_id and attr.run_id = :run_id and attr.user_id = :user_id and attr.type = 'isvisible' and attr.is_visible_p = 't' select prerequisite_id as prerequisite_item_id from imsld_imsldsi where item_id = :imsld_item_id and content_revision__is_live(imsld_id) = 't' select prerequisite_id as prerequisite_item_id from imsld_learning_activitiesi where item_id = :activity_item_id and content_revision__is_live(activity_id) = 't' select coalesce(pre.pretty_title, '') as prerequisite_title, pre.prerequisite_id from imsld_prerequisitesi pre where pre.item_id = :prerequisite_item_id and content_revision__is_live(pre.prerequisite_id) = 't' select ii.imsld_item_id from imsld_items ii, cr_items cr, acs_rels ar where ar.object_id_one = :prerequisite_item_id and ar.object_id_two = cr.item_id and cr.live_revision = ii.imsld_item_id select cpr.resource_id, cpr.item_id as resource_item_id, cpr.type as resource_type from imsld_cp_resourcesi cpr, imsld_itemsi ii, imsld_attribute_instances attr, acs_rels ar where ar.object_id_one = ii.item_id and ar.object_id_two = cpr.item_id and content_revision__is_live(cpr.resource_id) = 't' and (imsld_tree_sortkey between tree_left((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) and tree_right((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) or ii.imsld_item_id = :imsld_item_id) and attr.owner_id = ii.imsld_item_id and attr.run_id = :run_id and attr.user_id = :user_id and attr.type = 'isvisible' and attr.is_visible_p = 't' select coalesce(oc.feedback_title, oc.title) as feedback_title from imsld_on_completioni oc where oc.item_id = :on_completion_item_id and content_revision__is_live(oc.on_completion_id) = 't' select ii.imsld_item_id from imsld_items ii, cr_items cr, acs_rels ar where ar.object_id_one = :on_completion_item_id and ar.object_id_two = cr.item_id and cr.live_revision = ii.imsld_item_id select cpr.resource_id, cpr.item_id as resource_item_id, cpr.type as resource_type from imsld_cp_resourcesi cpr, imsld_itemsi ii, imsld_attribute_instances attr, acs_rels ar where ar.object_id_one = ii.item_id and ar.object_id_two = cpr.item_id and content_revision__is_live(cpr.resource_id) = 't' and (imsld_tree_sortkey between tree_left((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) and tree_right((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) or ii.imsld_item_id = :imsld_item_id) and attr.owner_id = ii.imsld_item_id and attr.run_id = :run_id and attr.user_id = :user_id and attr.type = 'isvisible' and attr.is_visible_p = 't' select identifier, resource_id, type as resource_type, title as resource_title, acs_object_id from imsld_cp_resourcesi where item_id = :resource_item_id and content_revision__is_live(resource_id) = 't' select live_revision from cr_items where item_id = :acs_object_id select acs_object__name(object_id) as object_title, object_type from acs_objects where object_id = :live_revision select acs_object__name(object_id) as object_title, object_type from acs_objects where object_id = :acs_object_id select cr.revision_id as imsld_file_id, cpf.file_name, cpf.item_id, cpf.parent_id from imsld_cp_filesx cpf, acs_rels ar, imsld_res_files_rels map, cr_revisions cr where ar.object_id_one = :resource_item_id and ar.object_id_two = cpf.item_id and cpf.item_id = cr.item_id and ar.rel_id = map.rel_id and content_revision__is_live(cr.revision_id) = 't' and map.displayable_p = 't' select ci.item_id as page_id, ci.name as file_name from acs_rels ar, imsld_res_files_rels map, cr_revisions cr, cr_items ci where ar.object_id_one = :resource_item_id and ar.object_id_two = cr.item_id and cr.item_id = ci.item_id and ar.rel_id = map.rel_id and content_revision__is_live(cr.revision_id) = 't' and map.displayable_p = 't' select content_item__get_path(:parent_id,:root_folder_id); select case when :folder_path is null then fs.file_upload_name else :folder_path || '/' || fs.file_upload_name end as file_url from fs_objects fs where fs.live_revision = :imsld_file_id select url from acs_rels ar, cr_extlinks links, imsld_res_files_rels map where ar.object_id_one = :resource_item_id and ar.object_id_two = links.extlink_id and ar.rel_id = map.rel_id and map.displayable_p = 't' select la.on_completion_id as on_completion_item_id, la.prerequisite_id as prerequisite_item_id, la.learning_objective_id as learning_objective_item_id, la.activity_id, rla.title as activity_title from imsld_attribute_instances attr, imsld_learning_activities la, cr_items cla, cr_revisions rla where cla.item_id = :activity_item_id and cla.live_revision = la.activity_id and cla.live_revision = rla.revision_id and content_revision__is_live(la.activity_id) = 't' and attr.owner_id = la.activity_id and attr.run_id = :run_id and attr.user_id = :user_id and attr.type = 'isvisible' and attr.is_visible_p = 't' select ii.imsld_item_id from imsld_items ii, imsld_activity_descs lad, imsld_learning_activitiesi la, cr_items cr1, cr_items cr2, acs_rels ar where la.item_id = :activity_item_id and la.activity_description_id = cr1.item_id and cr1.live_revision = lad.description_id and ar.object_id_one = la.activity_description_id and ar.object_id_two = cr2.item_id and cr2.live_revision = ii.imsld_item_id select icr.resource_id, cp.item_id as resource_item_id, icr.type as resource_type from imsld_cp_resources icr, imsld_items ii, imsld_attribute_instances iai, cr_items ci, cr_items cp, acs_rels ar where ii.imsld_item_id = ci.live_revision and ar.object_id_one = ci.item_id and icr.resource_id = cp.live_revision and ar.object_id_two = cp.item_id and content_revision__is_live(icr.resource_id) = 't' and (imsld_tree_sortkey between tree_left((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) and tree_right((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) or ii.imsld_item_id = :imsld_item_id) and iai.owner_id = ii.imsld_item_id and iai.run_id = :run_id and iai.user_id = :user_id and iai.type = 'isvisible' and iai.is_visible_p = 't' select 1 from imsld_status_user where user_id = :user_id and related_id = :activity_id and run_id = :run_id and status = 'finished' select count(*) from acs_rels where object_id_one = :on_completion_item_id and rel_type = 'imsld_feedback_rel' select ar.object_id_two as environment_item_id from acs_rels ar where ar.object_id_one = :activity_item_id and ar.rel_type = 'imsld_la_env_rel' order by ar.object_id_two select isa.on_completion_id as on_completion_item_id, isa.activity_id, attr.is_visible_p from imsld_support_activitiesi isa, imsld_attribute_instances attr where isa.item_id = :activity_item_id and content_revision__is_live(isa.activity_id) = 't' and attr.owner_id = isa.activity_id and attr.run_id = :run_id and attr.user_id = :user_id and attr.type = 'isvisible' and attr.is_visible_p = 't' select ii.imsld_item_id from imsld_items ii, imsld_activity_descs sad, imsld_support_activitiesi sa, cr_items cr1, cr_items cr2, acs_rels ar where sa.item_id = :activity_item_id and sa.activity_description_id = cr1.item_id and cr1.live_revision = sad.description_id and ar.object_id_one = sa.activity_description_id and ar.object_id_two = cr2.item_id and cr2.live_revision = ii.imsld_item_id select cpr.resource_id, cpr.item_id as resource_item_id, cpr.type as resource_type from imsld_cp_resourcesi cpr, imsld_itemsi ii, imsld_attribute_instances attr, acs_rels ar where ar.object_id_one = ii.item_id and ar.object_id_two = cpr.item_id and content_revision__is_live(cpr.resource_id) = 't' and (imsld_tree_sortkey between tree_left((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) and tree_right((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) or ii.imsld_item_id = :imsld_item_id) and attr.owner_id = ii.imsld_item_id and attr.run_id = :run_id and attr.user_id = :user_id and attr.type = 'isvisible' and attr.is_visible_p = 't' select 1 from imsld_status_user where user_id = :user_id and related_id = :activity_id and run_id = :run_id and status = 'finished' select ar.object_id_two as environment_item_id from acs_rels ar where ar.object_id_one = :activity_item_id and ar.rel_type = 'imsld_sa_env_rel' order by ar.object_id_two select ii.imsld_item_id from imsld_itemsi ii, acs_rels ar where ar.object_id_one = :structure_item_id and ar.rel_type = 'imsld_as_info_i_rel' and ar.object_id_two = ii.item_id and content_revision__is_live(ii.imsld_item_id) = 't' select cpr.resource_id, cpr.item_id as resource_item_id, cpr.type as resource_type from imsld_cp_resourcesi cpr, imsld_itemsi ii, imsld_attribute_instances attr, acs_rels ar where ar.object_id_one = ii.item_id and ar.object_id_two = cpr.item_id and content_revision__is_live(cpr.resource_id) = 't' and (imsld_tree_sortkey between tree_left((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) and tree_right((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) or ii.imsld_item_id = :imsld_item_id) and attr.owner_id = ii.imsld_item_id and attr.run_id = :run_id and attr.user_id = :user_id and attr.type = 'isvisible' and attr.is_visible_p = 't' select structure_id, structure_type from imsld_activity_structuresi where item_id = :structure_item_id select ar.object_id_two, ar.rel_type, ar.rel_id from acs_rels ar, imsld_activity_structuresi ias where ar.object_id_one = ias.item_id and ias.structure_id = :structure_id order by ar.object_id_two select cr.title as activity_title, ci.item_id as activity_item_id, la.activity_id, la.complete_act_id, attr.is_visible_p from imsld_learning_activities la, imsld_attribute_instances attr, cr_items ci, cr_revisions cr where ci.item_id = :object_id_two and ci.live_revision = cr.revision_id and ci.live_revision = la.activity_id and content_revision__is_live(la.activity_id) = 't' and attr.owner_id = la.activity_id and attr.run_id = :run_id and attr.user_id = :user_id and attr.type = 'isvisible' and attr.is_visible_p = 't' select 1 from imsld_status_user where related_id = :activity_id and user_id = :user_id and status = 'finished' and run_id = :run_id select sa.title as activity_title, sa.item_id as activity_item_id, sa.activity_id, sa.complete_act_id, attr.is_visible_p from imsld_support_activitiesi sa, imsld_attribute_instances attr where sa.item_id = :object_id_two and content_revision__is_live(sa.activity_id) = 't' and attr.owner_id = sa.activity_id and attr.run_id = :run_id and attr.user_id = :user_id and attr.type = 'isvisible' and attr.is_visible_p = 't' select title as activity_title, item_id as structure_item_id, structure_id, structure_type from imsld_activity_structuresi where item_id = :object_id_two and content_revision__is_live(structure_id) = 't' select 1 from imsld_status_user where related_id = :structure_id and user_id = :user_id and status = 'started' and run_id = :run_id select 1 from imsld_status_user where related_id = :structure_id and user_id = :user_id and status = 'finished' and run_id = :run_id select map.active_role_id as user_role_id from imsld_run_users_group_rels map, acs_rels ar, imsld_run_users_group_ext iruge where ar.rel_id = map.rel_id and ar.object_id_one = iruge.group_id and ar.object_id_two = :user_id and iruge.run_id = :run_id select case when irp.learning_activity_id is not null then 'learning' when irp.support_activity_id is not null then 'support' when irp.activity_structure_id is not null then 'structure' else 'none' end as type, content_item__get_live_revision(coalesce(irp.learning_activity_id, irp.support_activity_id, irp.activity_structure_id)) as activity_id, irp.role_part_id, ia.act_id, ca.item_id as act_item_id, ip.play_id from imsld_role_parts irp, imsld_acts ia, imsld_plays ip, imsld_imslds ii, imsld_attribute_instances iai, imsld_methods im, imsld_roles ir, cr_items ca, cr_items cp, cr_items cm, cr_items ci, cr_items cr where irp.act_id = ca.item_id and ca.live_revision = ia.act_id and ia.play_id = cp.item_id and cp.live_revision = ip.play_id and ip.method_id = cm.item_id and cm.live_revision = im.method_id and im.imsld_id = ci.item_id and ci.live_revision = ii.imsld_id and ii.imsld_id = :imsld_id and irp.role_id = cr.item_id and cr.live_revision = ir.role_id and ir.role_id = :user_role_id and content_revision__is_live(irp.role_part_id) = 't' and iai.owner_id = ip.play_id and iai.run_id = :run_id and iai.user_id = :user_id and iai.type = 'isvisible' and iai.is_visible_p = 't' order by ip.sort_order, ia.sort_order, irp.sort_order select la.title as activity_title, la.item_id as activity_item_id, la.activity_id, attr.is_visible_p, la.complete_act_id from imsld_learning_activitiesi la, imsld_attribute_instances attr where activity_id = :activity_id and attr.owner_id = la.activity_id and attr.run_id = :run_id and attr.user_id = :user_id and attr.type = 'isvisible' and attr.is_visible_p = 't' select sa.title as activity_title, sa.item_id as activity_item_id, sa.activity_id, attr.is_visible_p, sa.complete_act_id from imsld_support_activitiesi sa, imsld_attribute_instances attr where sa.activity_id = :activity_id and attr.owner_id = sa.activity_id and attr.run_id = :run_id and attr.user_id = :user_id and attr.type = 'isvisible' and attr.is_visible_p = 't' select title as activity_title, item_id as structure_item_id, structure_id, structure_type from imsld_activity_structuresi where structure_id = :activity_id select 1 from imsld_status_user where related_id = :activity_id and user_id = :user_id and status = 'started' and run_id = :run_id select 1 from imsld_status_user where related_id = :activity_id and user_id = :user_id and status = 'finished' and run_id = :run_id select imsld_id from imsld_runs where run_id = :run_id select map.active_role_id as user_role_id from imsld_run_users_group_rels map, acs_rels ar, imsld_run_users_group_ext iruge where ar.rel_id = map.rel_id and ar.object_id_one = iruge.group_id and ar.object_id_two = :user_id and iruge.run_id = :run_id select activity_id from imsld_runtime_activities_rels where role_id = :user_role_id and run_id = :run_id select 1 from imsld_learning_activities where activity_id = :activity_id select ia.act_id, ip.play_id from imsld_role_parts rp, imsld_actsi ia, imsld_playsi ip where rp.act_id = ia.item_id and ia.play_id = ip.item_id and rp.role_part_id = :role_part_id and content_revision__is_live(ip.play_id) = 't' select la.title as activity_title, la.item_id as activity_item_id, la.activity_id, attr.is_visible_p, la.complete_act_id from imsld_learning_activitiesi la, imsld_attribute_instances attr where activity_id = :activity_id and attr.owner_id = la.activity_id and attr.run_id = :run_id and attr.user_id = :user_id and attr.type = 'isvisible' select 1 from imsld_status_user where related_id = :activity_id and user_id = :user_id and run_id = :run_id and status = 'finished' select sa.title as activity_title, sa.item_id as activity_item_id, sa.activity_id, attr.is_visible_p, sa.complete_act_id from imsld_support_activitiesi sa, imsld_attribute_instances attr where sa.activity_id = :activity_id and attr.owner_id = sa.activity_id and attr.run_id = :run_id and attr.user_id = :user_id and attr.type = 'isvisible' select 1 from imsld_status_user where related_id = :activity_id and user_id = :user_id and run_id = :run_id and status = 'finished' select acs_object_id as assessment_id from imsld_cp_resourcesi where type='imsqti_xmlv1p0' and item_id=:resource_activity select sn.node_id as node_id from acs_objects ao, site_nodes sn where ao.package_id=sn.object_id and ao.object_id=:assessment_id; select imsld_id from imsld_imsldsi where item_id = :imsld_item_id and content_revision__is_live(imsld_id) = 't' select ila.activity_id, ila.item_id as activity_item_id from imsld_cp_resourcesi icri, acs_rels ar1, acs_rels ar2, imsld_learning_activitiesi ila where ar2.object_id_two=icri.item_id and ar1.object_id_two=ar2.object_id_one and ila.activity_description_id=ar1.object_id_one and icri.resource_id= :resource_id select isa.activity_id, isa.item_id as activity_item_id from imsld_cp_resourcesi icri, acs_rels ar1, acs_rels ar2, imsld_support_activitiesi isa where ar2.object_id_two=icri.item_id and ar1.object_id_two=ar2.object_id_one and isa.activity_description_id=ar1.object_id_one and icri.resource_id= :resource_id select ar1.object_id_one as imsld_item_item_id from imsld_cp_resourcesi icri, acs_rels ar1 where icri.item_id=ar1.object_id_two and icri.resource_id= :resource_id select 1 from imsld_conference_services where imsld_item_id=:imsld_item_item_id select isi.environment_id as environment_item_id from imsld_conference_services ics, imsld_servicesi isi where isi.item_id=ics.service_id and ics.imsld_item_id=:imsld_item_item_id select ila.activity_id, ila.item_id as activity_item_id, 'learning' from acs_rels ar, imsld_learning_activitiesi ila where ila.item_id=ar.object_id_one and ar.object_id_two=:environment_item_id select isa.activity_id, isa.item_id as activity_item_id, 'learning' from acs_rels ar, imsld_support_activitiesi isa where isa.item_id=ar.object_id_one and ar.object_id_two=:environment_item_id select 1 from acs_rels where rel_type='imsld_l_object_item_rel' and object_id_two=:imsld_item_item_id select iloi.environment_id as environment_item_id from imsld_learning_objectsi iloi, acs_rels ar where iloi.item_id=ar.object_id_one and ar.object_id_two=:imsld_item_item_id select ar1.object_id_one as resource_element_id from acs_rels ar1, acs_rels ar2, imsld_cp_resourcesi icr where ar1.object_id_two=ar2.object_id_one and ar2.object_id_two=icr.item_id and icr.resource_id = :resource_id; select 1 from imsld_prerequisitesi where item_id=:resource_element_id select activity_id, item_id as activity_item_id from imsld_learning_activitiesi where prerequisite_id=:resource_element_id select 1 from imsld_learning_objectivesi where item_id=:resource_element_id select activity_id, item_id as activity_item_id from imsld_learning_activitiesi where learning_objective_id=:resource_element_id select role_part_id from imsld_role_parts where learning_activity_id = :leaf_id select role_part_id from imsld_role_parts where support_activity_id = :leaf_id select role_part_id from imsld_role_partsi where activity_structure_id = :leaf_id select ias.structure_id, ias.item_id as leaf_id from imsld_activity_structuresi ias, acs_rels ar, imsld_learning_activitiesi la where ar.object_id_one = ias.item_id and ar.object_id_two = la.item_id and content_revision__is_live(ias.structure_id) = 't' and la.item_id = :leaf_id select ias.structure_id, ias.item_id as leaf_id from imsld_activity_structuresi ias, acs_rels ar, imsld_support_activitiesi sa where ar.object_id_one = ias.item_id and ar.object_id_two = sa.item_id and content_revision__is_live(ias.structure_id) = 't' and sa.item_id = :leaf_id select ias.structure_id, ias.item_id as leaf_id from imsld_activity_structuresi ias, acs_rels ar where ar.object_id_one = ias.item_id and ar.object_id_two = :leaf_id and content_revision__is_live(ias.structure_id) = 't' select iii.imsld_id as imsld_id from imsld_imsldsi iii, cr_items cr, cr_items cr2, imsld_learning_activitiesi ilai where ilai.activity_id=:activity_id and ilai.item_id=cr.item_id and cr2.parent_id=cr.parent_id and cr2.content_type='imsld_imsld' and iii.item_id=cr2.item_id select iii.imsld_id as imsld_id from imsld_imsldsi iii, cr_items cr, cr_items cr2, imsld_support_activitiesi isai where isai.activity_id=:activity_id and isai.item_id=cr.item_id and cr2.parent_id=cr.parent_id and cr2.content_type='imsld_imsld' and iii.item_id=cr2.item_id select iii.imsld_id as imsld_id from imsld_imsldsi iii, cr_items cr, cr_items cr2, imsld_activity_structuresi iasi where iasi.structure_id=:activity_id and iasi.item_id=cr.item_id and cr2.parent_id=cr.parent_id and cr2.content_type='imsld_imsld' and iii.item_id=cr2.item_id select resource_id from imsld_cp_resources where acs_object_id = :object_id insert into imsld_status_user ( imsld_id, run_id, related_id, user_id, type, status_date, status ) ( select :imsld_id, :run_id, :resource_id, :user_id, 'resource', now(), 'finished' where not exists (select 1 from imsld_status_user where run_id = :run_id and user_id = :user_id and related_id = :resource_id and status = 'finished') ) select 1 from imsld_status_user stat, imsld_cp_resourcesi icr where icr.item_id = :res_id and icr.resource_id = stat.related_id and user_id = :user_id and status = 'finished' and run_id = :run_id select 1 from imsld_status_user where related_id = :activity_id and user_id = :user_id and status = 'finished' and run_id = :run_id select count(*) from imsld_status_user where related_id = :resource_id and status = 'finished' and run_id = :run_id and user_id = :user_id select ii.imsld_item_id from imsld_items ii, imsld_activity_descs lad, imsld_learning_activitiesi la, cr_items cr1, cr_items cr2, acs_rels ar where la.item_id = :activity_item_id and la.activity_description_id = cr1.item_id and cr1.live_revision = lad.description_id and ar.object_id_one = la.activity_description_id and ar.object_id_two = cr2.item_id and cr2.live_revision = ii.imsld_item_id select icr.resource_id, cp.item_id as resource_item_id, icr.type as resource_type from imsld_cp_resources icr, imsld_items ii, imsld_attribute_instances iai, cr_items ci, cr_items cp, acs_rels ar where ii.imsld_item_id = ci.live_revision and ar.object_id_one = ci.item_id and icr.resource_id = cp.live_revision and ar.object_id_two = cp.item_id and content_revision__is_live(icr.resource_id) = 't' and (imsld_tree_sortkey between tree_left((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) and tree_right((select imsld_tree_sortkey from imsld_items where imsld_item_id = :imsld_item_id)) or ii.imsld_item_id = :imsld_item_id) and iai.owner_id = ii.imsld_item_id and iai.run_id = :run_id and iai.user_id = :user_id and iai.type = 'isvisible' and iai.is_visible_p = 't'