Hi,
Pls modify the query to suit your needs and also test it thoroughly..
select f.file_id, f.file_name,project_id
from
(
select f.id file_id, f.name file_name,
ZZ_GET_PROJECT_DOCUMENT(f.id) project_id,
fo.path_name PARENT_FOLDER_PATH,
fo.id folder_id,
fo.folder_type PARENT_FOLDER_TYPE,
fo.name PARENT_FOLDER_NAME,
v.byte_size BYTE_SIZE
from clb_dms_folders fo ,
clb_dms_versions v ,
clb_dms_files f
where
v.file_id = f.id and
f.parent_folder_id = fo.id
)
f
where project_id in
(
select i.id
from
inv_investments i where is_active=0 and schedule_finish <= '31-dec-2010'
)
CREATE OR REPLACE FUNCTION "ZZ_GET_PROJECT_DOCUMENT"
(F_FILEID NUMBER)
RETURN INTEGER
IS
PROJECT_ID NUMBER := 0;
BEGIN
SELECT ID INTO PROJECT_ID from inv_investments
WHERE ID IN
(
SELECT
ASSOC_OBJ_ID
FROM
CLB_DMS_FOLDERS
CONNECT BY
ID = PRIOR parent_folder_id
START WITH
ID = (select parent_folder_id from clb_dms_files where id=F_FILEID)
) ;
RETURN PROJECT_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN 1;
WHEN TOO_MANY_ROWS THEN RETURN 3;
WHEN OTHERS THEN RETURN 2;
END;
/
cheers,
sundar