Oracle EBS 11i 環境下利用 BOM API 實現物料清單展開

應用場景

在 Oracle E-Business Suite 11i 中，若需以程式化方式展開物料清單（BOM），取得多層次組件結構，可呼叫標準 API BOM_BOM_PUB.GET_BOM_STRUCTURE 或直接操作底層視圖。本文提供一個完整的 SQL*Plus 腳本範例，示範如何設定參數、呼叫 API 並將結果匯出至文字檔，供後續分析或介接使用。

完整執行腳本

-- BOM Explosion with Oracle API
SET HEADING ON
SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET PAUSE OFF
SET PAGESIZE 3000
SET LINESIZE 5000
SET COLSEP ';'
SET HEADSEP ';'
SET SERVEROUTPUT ON SIZE 1000000
SPOOL C:\bom_explosion_output.txt

DECLARE
  -- 輸入參數：組織代碼與成品料號（由 SQL*Plus 替換變數 &1, &2 傳入）
  v_org_code     MTL_PARAMETERS.ORGANIZATION_CODE%TYPE := '&1';
  v_item         MTL_SYSTEM_ITEMS.SEGMENT1%TYPE       := '&2';

  -- API 所需變數
  l_grp_id               NUMBER;
  l_session_id           NUMBER;
  l_org_id_s             NUMBER;
  l_levels_to_explode    NUMBER DEFAULT 20;   -- 最大展開層數
  l_module               NUMBER DEFAULT 2;     -- 2 = BOM 模組
  l_cst_type_id          NUMBER DEFAULT 0;     -- 成本類型：0=標準, -1=全部
  l_item_id              NUMBER;
  l_bom_or_eng           NUMBER;
  l_using_req_quantity   NUMBER;

  -- API 回傳集合
  l_bom_structure_tbl    BOM_BOM_PUB.BOM_STRUCTURE_TBL_TYPE;
  l_return_status        VARCHAR2(1);
  l_msg_count            NUMBER;
  l_msg_data             VARCHAR2(2000);
BEGIN
  -- 1. 取得 Inventory Item ID
  SELECT INVENTORY_ITEM_ID
  INTO   l_item_id
  FROM   MTL_SYSTEM_ITEMS_B
  WHERE  SEGMENT1 = v_item
  AND    ORGANIZATION_ID = (SELECT ORGANIZATION_ID FROM MTL_PARAMETERS WHERE ORGANIZATION_CODE = v_org_code);

  -- 2. 初始化 BOM API Session
  BOM_BOM_PUB.INITIALIZE_BOM_API(
    P_API_VERSION       => 1.0,
    P_INIT_MSG_LIST     => FND_API.G_TRUE,
    P_COMMIT            => FND_API.G_FALSE,
    X_RETURN_STATUS     => l_return_status,
    X_MSG_COUNT         => l_msg_count,
    X_MSG_DATA          => l_msg_data
  );

  -- 3. 呼叫 BOM 結構展開 API
  BOM_BOM_PUB.GET_BOM_STRUCTURE(
    P_API_VERSION           => 1.0,
    P_ORGANIZATION_ID       => (SELECT ORGANIZATION_ID FROM MTL_PARAMETERS WHERE ORGANIZATION_CODE = v_org_code),
    P_INVENTORY_ITEM_ID     => l_item_id,
    P_BOM_TYPE              => 1,               -- 1 = 標準 BOM
    P_BOM_ALTERNATE         => '1',
    P_LEVELS_TO_EXPLODE     => l_levels_to_explode,
    P_MODULE                => l_module,
    P_COST_TYPE_ID          => l_cst_type_id,
    P_USING_REQ_QUANTITY    => l_using_req_quantity,
    X_BOM_STRUCTURE_TBL     => l_bom_structure_tbl,
    X_RETURN_STATUS         => l_return_status,
    X_MSG_COUNT             => l_msg_count,
    X_MSG_DATA              => l_msg_data
  );

  -- 4. 輸出結果
  IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
    FOR i IN 1 .. l_bom_structure_tbl.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(
        l_bom_structure_tbl(i).COMPONENT_SEGMENT1 || ';' ||
        l_bom_structure_tbl(i).COMPONENT_DESCRIPTION || ';' ||
        l_bom_structure_tbl(i).QUANTITY_PER_ASSEMBLY || ';' ||
        l_bom_structure_tbl(i).OPERATION_SEQ_NUM || ';' ||
        l_bom_structure_tbl(i).LEVEL_NUM
      );
    END LOOP;
  ELSE
    DBMS_OUTPUT.PUT_LINE('API Error: ' || l_msg_data);
  END IF;

  -- 5. 提交/回滾
  IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
    COMMIT;
  ELSE
    ROLLBACK;
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Exception: ' || SQLERRM);
END;
/

SPOOL OFF
EXIT

執行方式

sqlplus apps/apps_pwd @bom_explosion.sql "M1" "FINISHED_GOOD_001"

其中 M1 為組織代碼，FINISHED_GOOD_001 為成品料號。

結果檔案說明

輸出檔 C:\bom_explosion_output.txt 以分號分隔，欄位依序為：

1. 元件料號
2. 元件描述
3. 單用量
4. 作業順序
5. 層級

可直接匯入 Excel 或後續 ETL 流程。

補充說明

• API 版本請依實際 EBS 版本調整（11i 常用 1.0）。
• 若需包含工程 BOM，將 P_BOM_TYPE 改為 2。
• 大量資料建議改用 PL/SQL Table 寫入暫存表，再以 UTL_FILE 分批匯出，避免 DBMS_OUTPUT 緩衝區溢位。