How To Determine Which File System is the Run or Patch File System Using a SQL Query?

 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';