Oracle R12: A PL/SQL Script to Report Customer OAF Personalizations Instance-Wide
Extract All OAF Personalizations Across Every Application in Oracle R12
A ready-to-run PL/SQL script that scans every installed application in your Oracle E-Business Suite R12 instance and reports only the customer-created OAF page personalizations — Oracle-seeded customizations are automatically filtered out.
If you've ever tried to answer the question "what has actually been personalized on this instance?", you already know the pain: Oracle Application Framework (OAF) personalizations are stored in MDS (Metadata Services), and by default a metadata dump mixes your team's custom personalizations together with Oracle's own seeded verticalization and translation layers.
This script walks every installed application, lists every personalized document under its MDS path, and classifies each customization layer as either seeded (excluded) or customer (kept) — so the final report only shows what your functional or technical team actually changed.
🚀 What the Script Does
The script loops through four levels to build a complete, customer-only personalization inventory:
A fourth pass (Loop 3) then calls jdr_utils.printDocument()
on each layer to read its content and apply the seeded/customer
classification described below.
🔍 How Seeded vs. Customer Is Decided
These are heuristics based on patterns observed on a live instance — not a documented Oracle distinction — so the script also logs every excluded item in an appendix at the end of the report, letting you audit or adjust the rules if something looks misclassified.
| Classification | Rule | Result |
|---|---|---|
| Seeded | Layer path contains /customizations/verticalization/ or /customizations/translation/ |
Excluded from main report, logged in appendix |
| Seeded | Printed document content contains the literal string $Header: |
Excluded from main report, logged in appendix |
| Customer | Everything else | Kept in the main report |
$Header:, check the appendix for that
application and tighten is_seeded_path() accordingly.
⚡ Quick Start
🛠️ How to Run It
Before running instance-wide, uncomment the filter line in the
c_apps cursor and list two or three application short
names (e.g. ICX, PO, POS) so
you can validate the output and timing on a small scale.
The script spools to a fixed Windows path by default. Update the
SPOOL line to a path that exists on the machine
running SQL*Plus, or to a relative filename if running from Linux.
The script relies on jdr_utils, which lives in the APPS
schema, so connect as apps (or a user with equivalent
grants) before running.
📜 The Full Script
Copy this into a .sql file and run it as described above.
SET SERVEROUTPUT ON SIZE UNLIMITED
SET LINESIZE 200
SET PAGESIZE 0
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET TERMOUT OFF
SPOOL "D:\all_apps_personalization_report_Final.txt"
DECLARE
TYPE t_str_tab IS TABLE OF VARCHAR2(4000) INDEX BY PLS_INTEGER;
CURSOR c_apps IS
SELECT fav.application_id,
fav.application_short_name,
fav.application_name
FROM fnd_application_vl fav,
fnd_product_installations fpi
WHERE fpi.application_id = fav.application_id
AND fpi.status = 'I' -- Installed; see notes above
-- AND fav.application_short_name IN ('ICX','PO','POS') -- <<< uncomment to test on a subset first
ORDER BY fav.application_short_name;
c_dry_run CONSTANT BOOLEAN := FALSE; -- dry-runs Loop 1 only, per application
l_report t_str_tab;
l_report_idx PLS_INTEGER := 0;
l_documents t_str_tab;
l_doc_idx PLS_INTEGER := 0;
l_customizations t_str_tab;
l_cust_idx PLS_INTEGER;
l_error_docs t_str_tab;
l_error_idx PLS_INTEGER := 0;
l_seeded_log t_str_tab;
l_seeded_idx PLS_INTEGER := 0;
l_doc_buffer t_str_tab;
l_doc_buffer_idx PLS_INTEGER;
l_capture DBMS_OUTPUT.CHARARR;
l_capture_count PLS_INTEGER;
v_app_count NUMBER := 0;
v_app_page_count NUMBER := 0;
v_app_cust_total NUMBER := 0;
v_app_seeded_total NUMBER := 0;
v_grand_apps_with_cust NUMBER := 0;
v_grand_page_count NUMBER := 0;
v_grand_cust_total NUMBER := 0;
v_grand_seeded_total NUMBER := 0;
v_grand_error_count NUMBER := 0;
l_app_path VARCHAR2(200);
------------------------------------------------------------------
PROCEDURE add_report(p_text IN VARCHAR2) IS
BEGIN
l_report_idx := l_report_idx + 1;
l_report(l_report_idx) := p_text;
END add_report;
PROCEDURE add_doc_buffer(p_text IN VARCHAR2) IS
BEGIN
l_doc_buffer_idx := l_doc_buffer_idx + 1;
l_doc_buffer(l_doc_buffer_idx) := p_text;
END add_doc_buffer;
------------------------------------------------------------------
PROCEDURE reset_output_buffer IS
BEGIN
DBMS_OUTPUT.DISABLE;
DBMS_OUTPUT.ENABLE(NULL);
END reset_output_buffer;
PROCEDURE capture_buffer IS
BEGIN
l_capture_count := 100000;
DBMS_OUTPUT.GET_LINES(l_capture, l_capture_count);
END capture_buffer;
------------------------------------------------------------------
FUNCTION is_seeded_path(p_layer_path IN VARCHAR2) RETURN BOOLEAN IS
BEGIN
RETURN INSTR(p_layer_path, '/customizations/verticalization/') > 0
OR INSTR(p_layer_path, '/customizations/translation/') > 0;
END is_seeded_path;
BEGIN
add_report('================================================================================');
add_report('All-Applications Personalization Report (customer customizations only)');
add_report('Generated: '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
add_report('================================================================================');
------------------------------------------------------------------
-- OUTER LOOP: every installed application
------------------------------------------------------------------
FOR app IN c_apps LOOP
v_app_count := v_app_count + 1;
v_app_page_count := 0;
v_app_cust_total := 0;
v_app_seeded_total := 0;
l_app_path := '/oracle/apps/'||LOWER(app.application_short_name);
l_documents.DELETE;
l_doc_idx := 0;
add_report(CHR(10)||'################################################################################');
add_report('APPLICATION: '||app.application_name||' ('||app.application_short_name||')');
add_report('Path scanned: '||l_app_path);
add_report('################################################################################');
------------------------------------------------------------------
-- LOOP 1
------------------------------------------------------------------
reset_output_buffer;
BEGIN
jdr_utils.listContents(l_app_path, TRUE);
EXCEPTION
WHEN OTHERS THEN
add_report(' [listContents error for this application: '||SQLERRM||']');
END;
capture_buffer;
IF c_dry_run THEN
add_report('--- DRY RUN: raw listContents output ---');
FOR i IN 1 .. l_capture_count LOOP
add_report(' ['||i||'] '||l_capture(i));
END LOOP;
ELSE
FOR i IN 1 .. l_capture_count LOOP
IF l_capture(i) IS NOT NULL
AND INSTR(l_capture(i), l_app_path) > 0 THEN
l_doc_idx := l_doc_idx + 1;
l_documents(l_doc_idx) := TRIM(l_capture(i));
END IF;
END LOOP;
add_report('Documents scanned: '||l_doc_idx);
------------------------------------------------------------------
-- LOOP 2 & 3, classifying seeded vs customer, for this application
------------------------------------------------------------------
FOR d IN 1 .. l_doc_idx LOOP
reset_output_buffer;
DECLARE
l_had_error BOOLEAN := FALSE;
v_doc_customer_cnt NUMBER := 0;
BEGIN
BEGIN
jdr_utils.listCustomizations(l_documents(d));
EXCEPTION
WHEN OTHERS THEN
l_had_error := TRUE;
l_error_idx := l_error_idx + 1;
l_error_docs(l_error_idx) := app.application_short_name||' | '||
l_documents(d)||' -- '||SQLERRM;
v_grand_error_count := v_grand_error_count + 1;
END;
capture_buffer;
l_cust_idx := 0;
IF NOT l_had_error THEN
FOR i IN 1 .. l_capture_count LOOP
IF l_capture(i) IS NOT NULL THEN
l_cust_idx := l_cust_idx + 1;
l_customizations(l_cust_idx) := l_capture(i);
END IF;
END LOOP;
END IF;
IF l_cust_idx > 0 THEN
l_doc_buffer.DELETE;
l_doc_buffer_idx := 0;
v_doc_customer_cnt := 0;
FOR c IN 1 .. l_cust_idx LOOP
DECLARE
l_is_seeded BOOLEAN := is_seeded_path(l_customizations(c));
l_has_header BOOLEAN := FALSE;
BEGIN
reset_output_buffer;
BEGIN
jdr_utils.printDocument(l_customizations(c));
EXCEPTION
WHEN OTHERS THEN
add_doc_buffer(' [printDocument error: '||SQLERRM||']');
END;
capture_buffer;
FOR i IN 1 .. l_capture_count LOOP
IF l_capture(i) IS NOT NULL AND INSTR(l_capture(i), '$Header:') > 0 THEN
l_has_header := TRUE;
END IF;
END LOOP;
IF l_is_seeded OR l_has_header THEN
v_app_seeded_total := v_app_seeded_total + 1;
v_grand_seeded_total := v_grand_seeded_total + 1;
l_seeded_idx := l_seeded_idx + 1;
l_seeded_log(l_seeded_idx) :=
app.application_short_name||' | '||l_documents(d)||' | '||l_customizations(c)||
' | reason: '||CASE WHEN l_is_seeded THEN 'seeded-path' ELSE '' END||
CASE WHEN l_is_seeded AND l_has_header THEN '+' ELSE '' END||
CASE WHEN l_has_header THEN '$Header-tag' ELSE '' END;
ELSE
v_doc_customer_cnt := v_doc_customer_cnt + 1;
add_doc_buffer(' '||l_customizations(c));
add_doc_buffer(CHR(10)||' >> Customization Detail for layer: '||l_customizations(c));
FOR i IN 1 .. l_capture_count LOOP
IF l_capture(i) IS NOT NULL THEN
add_doc_buffer(' '||l_capture(i));
END IF;
END LOOP;
END IF;
END;
END LOOP; -- Loop 3 / per-layer classification
IF v_doc_customer_cnt > 0 THEN
v_app_page_count := v_app_page_count + 1;
v_grand_page_count := v_grand_page_count + 1;
v_app_cust_total := v_app_cust_total + v_doc_customer_cnt;
v_grand_cust_total := v_grand_cust_total + v_doc_customer_cnt;
add_report(CHR(10)||'--------------------------------------------------------------------------------');
add_report('PAGE ('||v_app_page_count||'): '||l_documents(d));
add_report('--------------------------------------------------------------------------------');
add_report(' >> Customization Level(s) [customer only]:');
FOR i IN 1 .. l_doc_buffer_idx LOOP
add_report(l_doc_buffer(i));
END LOOP;
END IF;
END IF; -- l_cust_idx > 0
END;
END LOOP; -- document loop
END IF; -- c_dry_run
IF v_app_page_count > 0 THEN
v_grand_apps_with_cust := v_grand_apps_with_cust + 1;
END IF;
add_report(CHR(10)||'-- Application subtotal: '||app.application_short_name||' --');
add_report(' Documents scanned : '||l_doc_idx);
add_report(' Pages with customer customizations : '||v_app_page_count);
add_report(' Customer customization entries : '||v_app_cust_total);
add_report(' Seeded entries excluded : '||v_app_seeded_total);
END LOOP; -- OUTER application loop
------------------------------------------------------------------
-- GRAND SUMMARY, all applications
------------------------------------------------------------------
add_report(CHR(10)||'================================================================================');
add_report('GRAND SUMMARY - ALL APPLICATIONS (customer customizations only)');
add_report('================================================================================');
add_report('Applications scanned : '||v_app_count);
add_report('Applications with >=1 customer customization: '||v_grand_apps_with_cust);
add_report('Total pages w/ customer customizations : '||v_grand_page_count);
add_report('Total customer customization entries : '||v_grand_cust_total);
add_report('Total seeded entries excluded (all apps) : '||v_grand_seeded_total);
add_report('Total documents with errors (all apps) : '||v_grand_error_count);
add_report('================================================================================');
IF l_seeded_idx > 0 THEN
add_report(CHR(10)||'--- Appendix: Seeded/Oracle-delivered customizations excluded (app | document | layer | reason) ---');
FOR i IN 1 .. l_seeded_idx LOOP
add_report(' '||l_seeded_log(i));
END LOOP;
END IF;
IF l_error_idx > 0 THEN
add_report(CHR(10)||'--- Documents that errored during listCustomizations (app | doc -- error) ---');
FOR i IN 1 .. l_error_idx LOOP
add_report(' '||l_error_docs(i));
END LOOP;
END IF;
------------------------------------------------------------------
-- FINAL FLUSH
------------------------------------------------------------------
DBMS_OUTPUT.DISABLE;
DBMS_OUTPUT.ENABLE(NULL);
FOR i IN 1 .. l_report_idx LOOP
DBMS_OUTPUT.PUT_LINE(l_report(i));
END LOOP;
END;
/
SPOOL OFF
SET TERMOUT ON
PROMPT Report written to all_apps_personalization_report.txt in the current directory.
📊 Understanding the Output
- A header block per application, showing the MDS path scanned
- Documents scanned count for each application
- One block per page with customer customizations, listing every kept layer and its printed content
- A per-application subtotal (documents scanned, pages with customer customizations, customer entries, seeded entries excluded)
- A grand summary across all applications
- An appendix of every seeded item excluded, with its reason (seeded-path, $Header-tag, or both)
- An appendix of any documents that raised an error during listCustomizations
⚠️ Caution Before Running Unrestricted
c_apps to test against a handful of application short
names first.
Two assumptions worth validating on your own instance before a full run:
-
FND_PRODUCT_INSTALLATIONS.STATUS = 'I' means
"Installed" — adjust to
IN ('I','S')if you also want Shared installs included. -
MDS path prefix
'/oracle/apps/' || lower(application_short_name)holds for most seeded products; if an app'slistContentscall comes back empty, check its actual seeded page paths before assuming it has zero personalizations.
🧩 Troubleshooting
❓ The script runs for a very long time
This is expected on instances with many installed applications
and heavily personalized pages. Start with the commented-out
application filter in c_apps to test on a subset,
then remove the filter for the full run once you've confirmed
timing and output quality.
❓ An application shows zero documents scanned
Check whether that application's seeded pages actually live under
/oracle/apps/<short_name>. A handful of
products use a different MDS path convention, in which case
listContents will legitimately return nothing for
the derived path.
❓ Some documents appear in the error appendix
listCustomizations can fail on certain documents due
to permissions, corrupted metadata, or unusual document types.
The script logs the SQLERRM text for each so you can investigate
or re-run those documents individually.
❓ A page I know is customized isn't showing in the report
Check the seeded-exclusions appendix first — it may have been
classified as seeded because its layer path matched
/customizations/verticalization/ or
/customizations/translation/, or because its content
contained a $Header: tag. If it's a genuine customer
change, adjust is_seeded_path() to tighten the rule.
💡 Pro Tip: Keep the appendix, don't discard it
The seeded-exclusions appendix is what makes this report auditable. Since the classification is heuristic rather than an official Oracle rule, keeping that appendix around lets you (or the next person) double-check edge cases on products you haven't spot-checked yet.
✅ Final Checklist
- Connected as the apps schema (or equivalent) in SQL*Plus
- Spool path updated to a valid location for your OS
- Tested first against a small subset of application short names
- STATUS = 'I' filter reviewed — widen to include Shared installs if needed
- Full run completed and spool file reviewed
- Seeded-exclusions appendix spot-checked for false positives
- Error appendix reviewed for any documents that need a closer look
🚀 Ready to Audit Your OAF Personalizations?
Run the script against a small application subset first, confirm the classification looks right, then open it up to the full instance for a complete, noise-free personalization inventory.
Connect → Run → Scan → Classify → Review
Comments
Post a Comment