-- check_patch.sql : STRICTLY READ-ONLY inventory queries for Oracle E-Business Suite CVE-2026-101767
-- (Oracle Application Object Library, component Core; Oracle Critical Patch Update October 2026).
--
-- Run as a read-only account with SELECT on the APPS-owned objects below (or as APPS in a change window).
-- SELECT statements only: no INSERT, UPDATE, DELETE, MERGE, DDL, PL/SQL blocks, SPOOL or HOST commands.
-- Save the output with your SQL client's own export (for example SQL Developer "Export", or sqlplus -s
-- redirected by the shell outside this file) and pass the AD_BUGS / AD_APPLIED_PATCHES result to
-- check-oracle-ebs.sh --patch-list or Check-OracleEbs.ps1 -PatchList.
--
-- Oracle's public CPU text gives NO patch number for this CVE. Find the patch for your release in
-- My Oracle Support Note KA923 (Oracle E-Business Suite Release 12 Critical Patch Update Knowledge Document,
-- October 2026) and put it in fix_patch below. 00000000 is a PLACEHOLDER that matches nothing.
-- 39800355 is the Oracle bug ID that Oracle's CSAF file gives for CVE-2026-101767.
-- ASSUMPTION: the CPU patch records bug 39800355 in AD_BUGS. Treat a hit as a hint, not proof.

SET VERIFY OFF
DEFINE fix_patch = '00000000'

PROMPT == 1. EBS release (FND_PRODUCT_GROUPS.release_name) ==
SELECT release_name FROM apps.fnd_product_groups;

PROMPT == 2. AD and TXK code levels (context for KA923 prerequisites) ==
SELECT abbreviation, codelevel, last_update_date
  FROM apps.ad_trackable_entities
 WHERE abbreviation IN ('ad', 'txk', 'fnd', 'atg_pf')
 ORDER BY abbreviation;

PROMPT == 3. AD_BUGS: Oracle bug 39800355 and your KA923 fix patch ==
SELECT bug_number, application_short_name, creation_date, last_update_date
  FROM apps.ad_bugs
 WHERE bug_number IN ('39800355', '&fix_patch')
 ORDER BY creation_date;

PROMPT == 4. AD_APPLIED_PATCHES: your KA923 fix patch ==
SELECT patch_name, patch_type, creation_date
  FROM apps.ad_applied_patches
 WHERE patch_name LIKE '&fix_patch%'
 ORDER BY creation_date;

PROMPT == 5. Full AD_BUGS export for --patch-list / -PatchList (bug numbers only) ==
SELECT bug_number FROM apps.ad_bugs ORDER BY bug_number;

-- ======================================================================================
-- OPTIONAL HUNT QUERIES (read-only). Review against change records; none is proof alone.
-- Window: the last 30 days. Adjust SYSDATE - 30 as needed.
-- ======================================================================================

PROMPT == H1. Host-type concurrent executables created or changed recently (EXECUTION_METHOD_CODE = H) ==
SELECT e.executable_name, e.execution_file_name, e.execution_method_code, e.creation_date, e.last_update_date,
       e.created_by, e.last_updated_by
  FROM apps.fnd_executables e
 WHERE e.execution_method_code = 'H'
   AND (e.creation_date > SYSDATE - 30 OR e.last_update_date > SYSDATE - 30)
 ORDER BY e.last_update_date DESC;

PROMPT == H2. Concurrent programs created or changed recently ==
SELECT p.concurrent_program_name, p.executable_id, p.creation_date, p.last_update_date, p.created_by
  FROM apps.fnd_concurrent_programs p
 WHERE p.creation_date > SYSDATE - 30 OR p.last_update_date > SYSDATE - 30
 ORDER BY p.last_update_date DESC;

PROMPT == H3. Recent requests for host-type programs ==
SELECT r.request_id, p.concurrent_program_name, r.requested_by, r.request_date, r.actual_start_date,
       r.phase_code, r.status_code, r.argument_text
  FROM apps.fnd_concurrent_requests r
  JOIN apps.fnd_concurrent_programs p
    ON p.concurrent_program_id = r.concurrent_program_id AND p.application_id = r.program_application_id
  JOIN apps.fnd_executables e
    ON e.executable_id = p.executable_id AND e.application_id = p.executable_application_id
 WHERE e.execution_method_code = 'H'
   AND r.request_date > SYSDATE - 30
 ORDER BY r.request_date DESC;

PROMPT == H4. Application users created or changed recently ==
SELECT user_name, creation_date, last_update_date, start_date, end_date, last_logon_date
  FROM apps.fnd_user
 WHERE creation_date > SYSDATE - 30 OR last_update_date > SYSDATE - 30
 ORDER BY last_update_date DESC;

PROMPT == H5. Database directory objects (recon target reported by public research; unverified by Oracle) ==
SELECT d.directory_name, d.directory_path, o.created, o.last_ddl_time
  FROM dba_directories d
  LEFT JOIN dba_objects o ON o.object_name = d.directory_name AND o.object_type = 'DIRECTORY'
 ORDER BY o.last_ddl_time DESC NULLS LAST;

PROMPT == H6. Objects created or changed in the APPS schema recently ==
SELECT object_name, object_type, created, last_ddl_time, status
  FROM dba_objects
 WHERE owner = 'APPS' AND (created > SYSDATE - 30 OR last_ddl_time > SYSDATE - 30)
 ORDER BY last_ddl_time DESC;

UNDEFINE fix_patch