oracle8.1.6
select fs_files.file_id,
fs_files.name,
fs_files.live_revision,
fs_files.type,
to_char(fs_files.last_modified, 'Mon DD YYYY HH24:MI') as last_modified,
fs_files.content_size,
(select site_node.url(site_nodes.node_id)
from site_nodes
where site_nodes.object_id = file_storage.get_package_id(fs_files.file_id)) as url
from fs_files,
acs_permissions
where acs_permissions.object_id = fs_files.live_revision
and acs_permissions.grantee_id = :party_id
and acs_permissions.privilege = 'read'
and not exists (select 1
from dotlrn_research_papers
where dotlrn_research_papers.community_id = :community_id
and dotlrn_research_papers.file_id = fs_files.live_revision)
order by name
select fs_files.file_id,
fs_files.name,
fs_files.live_revision,
fs_files.type,
to_char(fs_files.last_modified, 'Mon DD YYYY HH24:MI') as last_modified,
fs_files.content_size,
(select site_node.url(site_nodes.node_id)
from site_nodes
where site_nodes.object_id = file_storage.get_package_id(fs_files.file_id)) as url
from dotlrn_research_papers,
fs_files
where dotlrn_research_papers.community_id = :community_id
and dotlrn_research_papers.file_id = fs_files.live_revision
order by name