Oracle Apps R12——報表入門2：單表—

☆開發思路

? ? ? ? 開發表報代碼流程中有幾個重要的組件和重要的知識點需要搞懂，才能得心應手。報表通常是通過表格的形式來存在的，我們一般在開發代碼的時候在【輸出】中打印HTML,Css格式的表格，并把查詢到的數據插入其中，即可完成一個報表的生成，至于這個表格是如何生成和到處為csv等等，其實像這樣的很多細節，系統都是自動化的，我們只需要在EBS系統中簡單的設置一下即可，后面娓娓道來。

????????開發報表代碼主要有3個重要組件，開發中需要定義：

①臨時表：ptian_table :是一個臨時表（或中間表），用來存儲從主表查詢出來的符合條件的XX數據。
②游標： cur_items：定義了一個用于遍歷 ptian_table 中的數據。
③存儲過程：自己定義名字，接收ERP參數數據和編寫業務邏輯用的。主要用于接收來自EBS ERP界面輸入的參數（定義存儲過程的時候，可以自定義傳入的參數。），并且可以根據參數從主表查詢數據和一系列業務邏輯編寫，插入臨時表 ptian_table。

數據流處理步驟：

存儲過程接收參數。
根據參數從主表 ?查詢符合條件的數據。
將查詢結果插入臨時表 ptian_table。
游標 cur_items 查詢臨時表數據。
逐條輸出HTML表格行。

步驟1 定義存儲過程（Procedure）

1.1 ptian_table模板?

-- 刪除舊表（如果存在）
BEGINEXECUTE IMMEDIATE 'DROP TABLE ptian_table PURGE';
EXCEPTIONWHEN OTHERS THENIF SQLCODE != -942 THEN -- 忽略表不存在錯誤RAISE;END IF;
END;
/-- 創建新表
CREATE TABLE ptian_table (description            VARCHAR2(4000),segment1               VARCHAR2(100),long_description       VARCHAR2(4000),primary_unit_of_measure VARCHAR2(100),creation_date          DATE
);

?1.2 核心代碼模板

-- 創建或替換存儲過程 QUERY_INVENTORY_ITEMS
-- 功能：查詢庫存物料信息，并將結果生成HTML格式輸出
-- 參數說明：
--   errbuf  : 輸出參數，用于返回錯誤信息
--   retcode : 輸出參數，返回狀態碼（0成功，1失敗）
--   p_segment1 : 輸入參數，物料編碼
--   p_item_type : 輸入參數，物料類型
CREATE OR REPLACE PROCEDURE QUERY_INV_ITEMS (errbuf               OUT VARCHAR2,retcode              OUT VARCHAR2,p_segment1           IN  VARCHAR2,p_item_type          IN  VARCHAR2
) IS-- 定義常量lv_api_name，用于記錄當前API名稱（用于日志標識）lv_api_name CONSTANT VARCHAR2(30) := 'QUERY_INV_ITEMS ';-- 定義變量lv_count，用于統計插入臨時表的記錄數lv_count NUMBER := 0;-- 定義游標cur_items，用于從臨時表ptian_table中讀取數據-- 查詢字段：物料說明、編碼、長描述、主單位、創建時間CURSOR cur_items ISSELECT description, segment1, long_description, primary_unit_of_measure, creation_date FROM ptian_table;BEGIN-- 記錄開始執行的日志（包含時間戳和API名稱）fnd_file.put_line(fnd_file.log, to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ssxff6') || chr(9) || lv_api_name || ' 開始執行');-- 清空臨時表ptian_table，確保每次查詢都是最新數據DELETE FROM ptian_table;-- 記錄清空表操作的日志fnd_file.put_line(fnd_file.log, to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ssxff6') || chr(9) || '清空ptian_table表數據');-- 將MTL_SYSTEM_ITEMS_FVL表中的數據插入臨時表ptian_table-- 篩選條件：物料編碼、物料類型，創建日期范圍（當前被注釋，可按需啟用）INSERT INTO ptian_table (description, segment1, long_description, primary_unit_of_measure, creation_date)SELECTDESCRIPTION,SEGMENT1,LONG_DESCRIPTION,PRIMARY_UNIT_OF_MEASURE,CREATION_DATEFROMMTL_SYSTEM_ITEMS_FVL MSIFWHERESEGMENT1 = p_segment1               -- 按物料編碼過濾AND MSIF.ITEM_TYPE = p_item_type;   -- 按物料類型過濾-- AND CREATION_DATE BETWEEN p_creation_date_from AND p_creation_date_to; -- 日期范圍條件（已注釋）-- 獲取插入的記錄數，并記錄到日志中lv_count := SQL%ROWCOUNT;fnd_file.put_line(fnd_file.log, to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ssxff6') || chr(9) || '插入ptian_table記錄數: ' || lv_count);-- 生成HTML輸出（以下為HTML頭部結構）-- 輸出HTML文檔聲明與基礎結構fnd_file.put_line(fnd_file.output, '<html xmlns:v="urn:schemas-microsoft-com:vml" ' ||'xmlns:o="urn:schemas-microsoft-com:office:office" ' ||'xmlns:x="urn:schemas-microsoft-com:office:excel">');fnd_file.put_line(fnd_file.output, '<head>');-- 設置字符集為UTF-8，確保中文顯示正常fnd_file.put_line(fnd_file.output, '<meta http-equiv="Content-Language" content="zh-cn">');fnd_file.put_line(fnd_file.output, '<meta http-equiv="Content-Type" content="text/html;charset=utf-8">');fnd_file.put_line(fnd_file.output, '<title>物料信息查詢結果</title>');-- 定義CSS樣式，美化表格顯示fnd_file.put_line(fnd_file.output, '<style>');fnd_file.put_line(fnd_file.output, 'body {background-color:#FFFFFF; font-family:Verdana; font-size:10pt; color:#000000;}');fnd_file.put_line(fnd_file.output, 'table {border-collapse:collapse; width:100%;}');fnd_file.put_line(fnd_file.output, 'th, td {border:1px solid #000000; padding:5px; text-align:left;}');fnd_file.put_line(fnd_file.output, 'th {background-color:#BBBBBB;}');  -- 表頭灰色背景fnd_file.put_line(fnd_file.output, '</style>');fnd_file.put_line(fnd_file.output, '</head>');fnd_file.put_line(fnd_file.output, '<body>');-- 輸出標題“物料信息查詢結果”，居中顯示fnd_file.put_line(fnd_file.output, '<h3 align="center">物料信息查詢結果</h3>');-- 輸出表格結構fnd_file.put_line(fnd_file.output, '<table>');-- 輸出表頭行，包含5列：物料、說明、長描述、計量單位、創建時間fnd_file.put_line(fnd_file.output, '<tr>');fnd_file.put_line(fnd_file.output, '<th>物料</th>');fnd_file.put_line(fnd_file.output, '<th>說明</th>');fnd_file.put_line(fnd_file.output, '<th>長描述</th>');fnd_file.put_line(fnd_file.output, '<th>計量單位</th>');fnd_file.put_line(fnd_file.output, '<th>創建時間</th>');fnd_file.put_line(fnd_file.output, '</tr>');-- 遍歷游標cur_items中的每一條記錄，生成表格行FOR rec IN cur_items LOOPfnd_file.put_line(fnd_file.output, '<tr>');-- 輸出物料編碼（segment1）fnd_file.put_line(fnd_file.output, '<td>' || rec.segment1 || '</td>');-- 輸出物料說明（description）fnd_file.put_line(fnd_file.output, '<td>' || rec.description || '</td>');-- 輸出長描述（long_description）fnd_file.put_line(fnd_file.output, '<td>' || rec.long_description || '</td>');-- 輸出主單位（primary_unit_of_measure）fnd_file.put_line(fnd_file.output, '<td>' || rec.primary_unit_of_measure || '</td>');-- 輸出創建時間（格式化為yyyy-mm-dd hh24:mi:ss）fnd_file.put_line(fnd_file.output, '<td>' || TO_CHAR(rec.creation_date, 'yyyy-mm-dd hh24:mi:ss') || '</td>');fnd_file.put_line(fnd_file.output, '</tr>');END LOOP;-- 結束HTML表格和文檔fnd_file.put_line(fnd_file.output, '</table>');fnd_file.put_line(fnd_file.output, '</body></html>');-- 設置成功狀態errbuf := NULL;     -- 錯誤信息置空retcode := '0';     -- 返回碼設為0表示成功-- 記錄成功結束的日志fnd_file.put_line(fnd_file.log, to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ssxff6') || chr(9) || lv_api_name || ' 執行成功結束');EXCEPTION-- 異常處理塊WHEN OTHERS THENerrbuf := SQLERRM;          -- 捕獲錯誤信息retcode := '1';             -- 返回碼設為1表示失敗-- 記錄異常日志，包含錯誤詳情fnd_file.put_line(fnd_file.log, to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ssxff6') || chr(9) || lv_api_name || ' 異常：' || errbuf);-- 在HTML輸出中顯示錯誤信息fnd_file.put_line(fnd_file.output, '執行失敗，錯誤信息：' || errbuf);
END QUERY_INV_ITEMS;
/

? ? ? ? 上面提供一個了模板，包含以下幾個步驟，我們一一分析和解釋：
(1) 建立臨時表★★★★★

-- 刪除舊表（如果存在）
BEGINEXECUTE IMMEDIATE 'DROP TABLE ptian_table PURGE';
EXCEPTIONWHEN OTHERS THENIF SQLCODE != -942 THEN -- 忽略表不存在錯誤RAISE;END IF;
END;
/-- 創建新表
CREATE TABLE ptian_table (description            VARCHAR2(4000),segment1               VARCHAR2(100),long_description       VARCHAR2(4000),primary_unit_of_measure VARCHAR2(100),creation_date          DATE
);

(2)數據準備：★★★★★

清空臨時表 ptian_table，確保每次查詢的數據都是最新的，避免歷史數據干擾。
建立存儲過程代碼,用于接收參數和傳出參數，以及編寫業務邏輯（報表）給ERP系統
記錄清空操作日志。

-- 創建或替換存儲過程 QUERY_INVENTORY_ITEMS
-- 功能：查詢庫存物料信息，并將結果生成HTML格式輸出
-- 參數說明：
--   errbuf  : 輸出參數，用于返回錯誤信息
--   retcode : 輸出參數，返回狀態碼（0成功，1失敗）
--   p_segment1 : 輸入參數，物料編碼
--   p_item_type : 輸入參數，物料類型
CREATE OR REPLACE PROCEDURE QUERY_INV_ITEMS (errbuf               OUT VARCHAR2,retcode              OUT VARCHAR2,p_segment1           IN  VARCHAR2,p_item_type          IN  VARCHAR2
) IS
-- 定義常量lv_api_name，用于日志標識
lv_api_name CONSTANT VARCHAR2(30) := 'QUERY_INV_ITEMS';
-- 定義變量lv_count，用于統計插入記錄數
lv_count NUMBER := 0;-- 記錄存儲過程開始執行的日志（包含時間戳和API名稱）
fnd_file.put_line(fnd_file.log, to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ssxff6') || chr(9) || lv_api_name || ' 開始執行');-- 清空臨時表ptian_table，確保每次查詢都是最新數據
DELETE FROM ptian_table;-- 記錄清空表操作的日志
fnd_file.put_line(fnd_file.log, to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ssxff6') || chr(9) || '清空ptian_table表數據');

(3)數據查詢與插入★★★★★

從主表 MTL_SYSTEM_ITEMS_FVL 根據輸入參數（物料編碼、物料類型）篩選數據。
將篩選結果插入臨時表 ptian_table。
統計插入的記錄數，并寫入日志。

-- 將MTL_SYSTEM_ITEMS_FVL表中的數據插入臨時表ptian_table
INSERT INTO ptian_table (description, segment1, long_description, primary_unit_of_measure, creation_date)
SELECT
-- 查詢到數據后想要展示的列名，這些都是要查詢的數據DESCRIPTION,    SEGMENT1,LONG_DESCRIPTION,PRIMARY_UNIT_OF_MEASURE,CREATION_DATE
FROMMTL_SYSTEM_ITEMS_FVL MSIF
WHERESEGMENT1 = p_segment1               -- 按物料編碼過濾AND MSIF.ITEM_TYPE = p_item_type;  -- 按物料類型過濾-- AND CREATION_DATE BETWEEN p_creation_date_from AND p_creation_date_to; -- 日期范圍條件（已注釋）-- 獲取插入的記錄數
lv_count := SQL%ROWCOUNT;-- 記錄插入記錄數日志
fnd_file.put_line(fnd_file.log, to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ssxff6') || chr(9) || '插入ptian_table記錄數: ' || lv_count);

(4)生成HTML格式的查詢結果

輸出HTML文檔的頭部信息，包括字符集、標題和樣式，確保頁面美觀且支持中文。
輸出表格結構和表頭，定義展示字段（物料、說明、長描述、計量單位、創建時間）。
遍歷臨時表數據，逐條生成表格行，填充對應字段內容。
關閉表格和HTML文檔標簽。

-- 輸出HTML文檔聲明與基礎結構
fnd_file.put_line(fnd_file.output, '<html xmlns:v="urn:schemas-microsoft-com:vml" ' ||'xmlns:o="urn:schemas-microsoft-com:office:office" ' ||'xmlns:x="urn:schemas-microsoft-com:office:excel">');
fnd_file.put_line(fnd_file.output, '<head>');
-- 設置字符集為UTF-8，確保中文顯示正常
fnd_file.put_line(fnd_file.output, '<meta http-equiv="Content-Language" content="zh-cn">');
fnd_file.put_line(fnd_file.output, '<meta http-equiv="Content-Type" content="text/html;charset=utf-8">');
fnd_file.put_line(fnd_file.output, '<title>物料信息查詢結果</title>');
-- 定義CSS樣式，美化表格顯示
fnd_file.put_line(fnd_file.output, '<style>');
fnd_file.put_line(fnd_file.output, 'body {background-color:#FFFFFF; font-family:Verdana; font-size:10pt; color:#000000;}');
fnd_file.put_line(fnd_file.output, 'table {border-collapse:collapse; width:100%;}');
fnd_file.put_line(fnd_file.output, 'th, td {border:1px solid #000000; padding:5px; text-align:left;}');
fnd_file.put_line(fnd_file.output, 'th {background-color:#BBBBBB;}');  -- 表頭灰色背景
fnd_file.put_line(fnd_file.output, '</style>');
fnd_file.put_line(fnd_file.output, '</head>');
fnd_file.put_line(fnd_file.output, '<body>');
-- 輸出標題“物料信息查詢結果”，居中顯示
fnd_file.put_line(fnd_file.output, '<h3 align="center">物料信息查詢結果</h3>');-- 輸出表格結構和表頭
fnd_file.put_line(fnd_file.output, '<table>');
fnd_file.put_line(fnd_file.output, '<tr>');
fnd_file.put_line(fnd_file.output, '<th>物料</th>');
fnd_file.put_line(fnd_file.output, '<th>說明</th>');
fnd_file.put_line(fnd_file.output, '<th>長描述</th>');
fnd_file.put_line(fnd_file.output, '<th>計量單位</th>');
fnd_file.put_line(fnd_file.output, '<th>創建時間</th>');
fnd_file.put_line(fnd_file.output, '</tr>');
-- ，逐條生成表格行，填充對應字段內容。
FOR rec IN cur_items LOOPfnd_file.put_line(fnd_file.output, '<tr>');fnd_file.put_line(fnd_file.output, '<td>' || rec.segment1 || '</td>');                 -- 物料編碼fnd_file.put_line(fnd_file.output, '<td>' || rec.description || '</td>');              -- 物料說明fnd_file.put_line(fnd_file.output, '<td>' || rec.long_description || '</td>');         -- 長描述fnd_file.put_line(fnd_file.output, '<td>' || rec.primary_unit_of_measure || '</td>');  -- 計量單位fnd_file.put_line(fnd_file.output, '<td>' || TO_CHAR(rec.creation_date, 'yyyy-mm-dd hh24:mi:ss') || '</td>');  -- 創建時間fnd_file.put_line(fnd_file.output, '</tr>');
END LOOP;

(5)?結束處理與狀態返回

設置輸出參數 errbuf 為空，retcode 為0，表示成功。
記錄成功結束的日志。

-- 關閉HTML表格和文檔標簽
fnd_file.put_line(fnd_file.output, '</table>');
fnd_file.put_line(fnd_file.output, '</body></html>');-- 設置輸出參數，表示存儲過程執行成功，錯誤信息為空。寫日志記錄成功結束。
-- 設置成功狀態
errbuf := NULL;     -- 錯誤信息置空
retcode := '0';     -- 返回碼設為0表示成功-- 記錄成功結束的日志
fnd_file.put_line(fnd_file.log, to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ssxff6') || chr(9) || lv_api_name || ' 執行成功結束');

(6)?異常處理

捕獲所有異常，獲取錯誤信息。
設置錯誤返回碼和錯誤信息輸出。
記錄異常日志，方便定位問題。
在HTML輸出中顯示錯誤提示，保證調用方能看到錯誤信息。

EXCEPTIONWHEN OTHERS THENerrbuf := SQLERRM;          -- 捕獲錯誤信息retcode := '1';             -- 返回碼設為1表示失敗-- 記錄異常日志，包含錯誤詳情fnd_file.put_line(fnd_file.log, to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ssxff6') || chr(9) || lv_api_name || ' 異常：' || errbuf);-- 在HTML輸出中顯示錯誤信息fnd_file.put_line(fnd_file.output, '執行失敗，錯誤信息：' || errbuf);
END QUERY_INVENTORY_ITEMS;

1.3 查詢具體報錯信息的代碼

?????????當你在Oracle數據庫中創建或編譯存儲過程（Procedure）、函數（Function）、包（Package）等PL/SQL對象時，Oracle會對代碼進行語法和編譯檢查。

????????如果代碼中存在語法錯誤或編譯錯誤，Oracle不會直接報錯阻止創建，但會將錯誤信息記錄在user_errors視圖中。

????????這時，存儲過程雖然“創建”成功，但實際上是無效的，無法正常執行。

????????因此，必須查詢user_errors視圖，查看是否存在編譯錯誤，才能確認代碼是否正確。

SELECT line, position, text
FROM user_errors
WHERE name = 'QUERY_INV_ITEMS'
ORDER BY line, position;

步驟2 定義Executable（可執行文件/程序）

? ? ? ? 切換到職責：”應用開發”職責，【并發->可執行】，重點都用箭頭標明和解釋了：

?步驟3 定義Concurrent Program（并發程序）

3.1 填寫Program基本信息

? ? ? ? 還是“應用開發”這個職責，【并發->程序】

3.2 Program參數定義

? ? ? ? 還記得我們寫的Procedure存儲過程的代碼嗎，我們定義了一個存儲過程：

-- 創建或替換存儲過程 QUERY_INVENTORY_ITEMS
-- 功能：查詢庫存物料信息，并將結果生成HTML格式輸出
-- 參數說明：
--   errbuf  : 輸出參數，用于返回錯誤信息
--   retcode : 輸出參數，返回狀態碼（0成功，1失敗）
--   p_segment1 : 輸入參數，物料編碼
--   p_item_type : 輸入參數，物料類型
CREATE OR REPLACE PROCEDURE QUERY_INV_ITEMS (errbuf               OUT VARCHAR2,retcode              OUT VARCHAR2,p_segment1           IN  VARCHAR2,p_item_type          IN  VARCHAR2,
) IS
... 下面的無關ERP的傳參

? ? ? ? ?只有輸入參數需要設定，標紅的設置是必須要填入的字段，不然無法創建第二個參數，

??點擊序號前面的豎杠即可創建下一個參數，例如創建第三個參數：

步驟4：綁定Concurrent Program到Request Group?

?切換職責到“系統用戶”，【安全性->責任->請求】，選中【組】這個文本框，然后點擊左上角的手電筒：

? ? ? ? 找到想要把Program分配給的請求組，例如我想把剛剛定義的Program分配給INV reports請求組（一個專門管理庫存模塊報表的請求組）。?

? ? ? ? ?直接回車，就會自動跳轉到：

? ? ? ? 選中“請求”下面的表格任意一行后，再次點擊“手電筒”左邊的“新建”功能，是一個文件帶著一個+號：

? ? ? ? ?成功把新的程序加入到對應的請求組中，成為了一個新的請求，并且分配給了INV reports請求組，任意一個擁有該請求組權限的“責任”，都可以運行這個請求。

步驟5：切換到對應責任，運行Program

? ? ? ? 這里就是根據自己的需求來切換了，切換到擁有權限——你把程序綁定的請求組的權限的“責任”，

%制造%項目%

-- 個人需求所用，參考者忽略?

然后【請求->運行】，運行程序：?

? ? ? ? ? ? ? ? ?繼續選中名稱，然后“手電筒”，搜索我們的Program名稱，他現在由于綁定到了請求組中，已經成為了一個請求，但是名字不變。

? ? ? ? 彈出我們設定的2個輸入參數，我們輸入我們想要查詢的指定數據，比如我的數據源頭表可以從代碼看到的是，我們輸入的兩個值p_segment1和p_item_type被用作于篩選的條件。只有數據表中SEGENT1 = p_segment1 和 ITEM_TYPE=p_item_types才會查詢，返回指定的數據。而我們現在要做的就是填寫這兩個輸入參數。

  INSERT INTO ptian_table (description, segment1, long_description, primary_unit_of_measure, creation_date)SELECTDESCRIPTION,SEGMENT1,LONG_DESCRIPTION,PRIMARY_UNIT_OF_MEASURE,CREATION_DATEFROMMTL_SYSTEM_ITEMS_FVL MSIFWHERESEGMENT1 = p_segment1               -- 按物料編碼過濾AND MSIF.ITEM_TYPE = p_item_type;   -- 按物料類型過濾