To determine the RUN file system:
SELECT EXTRACTVALUE(XMLType(text),'//oa_context_file_loc')
appl_top, status
FROM fnd_oam_context_files
WHERE name NOT IN ('TEMPLATE','METADATA','config.txt')
AND CTX_TYPE='A'
AND (status IS NULL OR UPPER(status) IN ('S','F'))
AND EXTRACTVALUE(XMLType(text),'//file_edition_type') = 'run';
To determine the PATCH files system:
SELECT EXTRACTVALUE(XMLType(text),'//oa_context_file_loc')
appl_top, status
FROM fnd_oam_context_files
WHERE name NOT IN ('TEMPLATE','METADATA','config.txt')
AND CTX_TYPE='A'
AND (status IS NULL OR UPPER(status) IN ('S','F'))
AND EXTRACTVALUE(XMLType(text),'//file_edition_type') = 'patch';