BI Publisher の SQL 実践（Fusion テーブルの使い方）

この章の結論

• BIP の SQL データセットは Fusion のテーブルを直接照会できますが、OTBI と違ってデータセキュリティは自動では効きません。実行できる人を絞る、SQL で実行ユーザーに応じて絞る、OTBI の分析をデータセットにする、のいずれかで対策します。
• Fusion のテーブルは「_ALL＝BU 別」「_B／_TL＝言語別」「_F／_M＝日付有効」という規則で読めます。名称は ID から結合して取り、_TL は必ず 1 言語に絞ります。
• 性能の基本は「バインド変数」「索引列をそのまま比較」「日付は範囲で比較」「必要な列・行だけ返す」です。
• テーブル名・列名は、必ず自分の環境と同じリリースの Tables and Views で確認してから使います。

全体像

BIP のデータモデルで SQL Query データセットを作ると、Fusion のデータベースに SQL を発行できます。SaaS のため DB に直接接続する手段はなく、SQL を書いて動かせる主な場所が BIP のデータモデルです。

BIP の SQL が実行されるまで

データモデル（SQL Query、データソースを選択）
  → パラメータ（:P_LEDGER_ID など）に値が入る
  → SQL を Fusion のデータベースで実行（実行ユーザーのデータセキュリティは自動では付かない）
  → 結果を XML として生成 → レイアウトで PDF・Excel などに変換

テーブルの命名規則（接頭辞・接尾辞、Who 列、多言語、マルチ組織）は テーブル命名規則・Who 列・多言語・マルチ組織 に、モジュール別の主要テーブルは 主要テーブルマップ にまとめています。このページでは、それを 帳票の SQL として書く ときの実践を扱います。

列名の確認について

このページの SQL は、BIP のデータモデルでの利用を想定した学習用の例です。テーブル名は公式ドキュメントで存在を確認したものを使っています。列名のうち、BU のビュー FUN_ALL_BUSINESS_UNITS_V（BU_ID・BU_NAME・PRIMARY_LEDGER_ID。26C）と、主要テーブルマップ の「確認」列に 25D・26A などと書いた表の列は、公式の Tables and Views で確認しました。それ以外の列は、執筆時点（2026 年 10 月）に全件の照合ができていません。実装に使う前に、必ず自分の環境と同じリリースの「Tables and Views for Financials」で列名・データ型・コード値を確認し、テスト環境で結果を検証してください。

設定・実装の手順（SQL データセットを作る）

1. カタログで Create > Data Model を選び、データモデル・エディタを開きます。
2. Properties で既定のデータソースを確認します（Financials の業務テーブルを照会するデータソースを選びます）。
3. New Dataset > SQL Query を選び、名前とデータソースを指定します。
4. Type of SQL は通常 Standard SQL を選び、SQL を貼り付けます。
5. OK を押すと SQL が検証され、バインド変数（:P_...）からパラメータが作られます。
6. パラメータの型・既定値・値リストを設定します（BI Publisher を参照）。
7. Data タブで少数の行を実行し、結果を確認してサンプル・データとして保存します。

Fusion テーブルの基本パターン

組織名（BU・元帳）を付ける

業務テーブルには組織の ID しか入っていません。名称は組織のテーブル・ビューから結合して取ります。

取りたいもの	業務テーブル側の列（例）	名称の取得先（例）
営業単位（BU）	_ALL 表の ORG_ID	BU のビュー（FUN_ALL_BUSINESS_UNITS_V の BU 名）
元帳	LEDGER_ID	GL_LEDGERS の元帳名
仕入先	VENDOR_ID	POZ_SUPPLIERS → HZ_PARTIES の名称
顧客	CUSTOMER_ID など	HZ_CUST_ACCOUNTS → HZ_PARTIES の名称

BU 名と元帳名を付ける（AP 請求書の例）

SELECT bu.bu_name,
       gl.name            AS ledger_name,
       aia.invoice_num,
       aia.invoice_amount
FROM   ap_invoices_all aia
       JOIN fun_all_business_units_v bu ON bu.bu_id     = aia.org_id
       JOIN gl_ledgers               gl ON gl.ledger_id = bu.primary_ledger_id
WHERE  aia.org_id = :P_BU_ID

FUN_ALL_BUSINESS_UNITS_V は、公式の Tables and Views（26C）でビューとして載っており、BU の ID が BU_ID、名称が BU_NAME、主元帳の ID が PRIMARY_LEDGER_ID、法的エンティティが LEGAL_ENTITY_ID です。上の SQL が元帳を bu.primary_ledger_id で結ぶのは、この列を使っています（AP 請求書自身の SET_OF_BOOKS_ID で結ぶ方法もあります）。公式ページに載っているビューの定義は、分類が FUN_BUSINESS_UNIT で、現在の日付に有効な組織だけを返す条件になっています。過去に終了した BU は出ないため、履歴を扱う帳票では注意してください。

BU のパラメータには、ID ではなく名称を選ばせる値リストを付けると使いやすくなります。

BU の値リスト（表示名と値）

SELECT bu.bu_name AS label,
       bu.bu_id   AS value
FROM   fun_all_business_units_v bu
ORDER  BY bu.bu_name

多言語（_TL 表）を扱う

Fusion では、名称や説明を言語ごとに持つ _TL 表があります。_TL 表を結合するときは、必ず言語で 1 行に絞ります。絞らないと、行が言語の数だけ増え、金額の合計も膨らみます。

多言語表の結合パターン（<…> は表に合わせて置き換え）

SELECT b.<コード列>,
       t.<名称列>
FROM   <名前>_B  b
       JOIN <名前>_TL t
         ON t.<ID列>   = b.<ID列>
        AND t.language = USERENV('LANG')   -- セッションの言語（固定するなら 'JA' など）

帳票の言語を利用者の設定に合わせたいときは、BIP のシステム変数も使えます。:xdo_user_report_oracle_lang は利用者のアカウント設定にあるレポート言語（例：JA、US）を返します。ただし公式ドキュメントのとおり、スケジュール実行のジョブでは言語・ロケール系のシステム変数は設定されません。スケジュール実行する帳票では、言語をパラメータで受け取るか固定します。

COA セグメントを扱う

勘定科目の組合せは GL_CODE_COMBINATIONS に 1 組合せ 1 行で格納され、各セグメントは SEGMENT1〜SEGMENTn の列に入ります。どの列がどのセグメント（会社・部門・勘定）か は COA の設計によって違います。

勘定組合せを結合してセグメントを取り出す（例）

SELECT gcc.segment1 AS company,      -- 例：会社（何番目かは COA の設計による）
       gcc.segment2 AS department,   -- 例：部門
       gcc.segment3 AS account,      -- 例：勘定科目
       gjl.accounted_dr,
       gjl.accounted_cr
FROM   gl_je_lines gjl
       JOIN gl_code_combinations gcc
         ON gcc.code_combination_id = gjl.code_combination_id
WHERE  gjl.ledger_id   = :P_LEDGER_ID
AND    gjl.period_name = :P_PERIOD_NAME

実務での注意点は次のとおりです。

1. セグメント番号を設計書で確認する：SEGMENT1 が会社とは限りません。COA の定義（Manage Chart of Accounts Structures）を確認し、SQL のコメントに残します。
2. セグメントの説明（名称）：セグメント値の説明は値セットの値から取ります。値セットのテーブル構造はリリースで確認が必要なため、まずは OTBI の GL 系サブジェクト領域で名称付きの値が取れないかも検討します。
3. 複数の COA が混在する場合：CHART_OF_ACCOUNTS_ID で COA を絞ってから、セグメントの意味を解釈します。
4. 勘定の範囲指定：勘定の範囲で絞るときは、文字列比較になることに注意します（'4000' <= segment3 AND segment3 <= '4999' は桁数がそろっている前提）。

会計期間を扱う

GL の期間は名前（PERIOD_NAME、例：Apr-26）で持つのが基本です。期間名の書式は会計カレンダーの設定で決まり、並び順も文字列では正しくなりません。期間の範囲や順序が必要な場合は、期間のテーブルから開始日・終了日・年・期間番号を取って使います。

テーブル	主な用途
GL_PERIODS	会計カレンダー（PERIOD_SET_NAME）ごとの期間の定義（開始日・終了日・年・番号・調整期間かどうか）
GL_PERIOD_STATUSES	元帳・アプリケーションごとの期間の状態（オープン・クローズなど）。GL は APPLICATION_ID = 101

元帳の会計期間の値リスト（新しい順。調整期間を除く例）

SELECT gps.period_name AS label,
       gps.period_name AS value
FROM   gl_period_statuses gps
WHERE  gps.application_id = 101                -- GL
AND    gps.ledger_id      = :P_LEDGER_ID
AND    NVL(gps.adjustment_period_flag, 'N') = 'N'
ORDER  BY gps.start_date DESC

期間の範囲（例：期首から当月まで）で絞る場合は、期間名の文字列で BETWEEN を書かず、期間の開始日・終了日で範囲を決めます。

データセキュリティ（自動では効かない点と対策）

なぜ自動では効かないのか

OTBI は、Fusion のデータセキュリティ（BU・元帳などへのアクセス）を使って行を絞ります。一方、BIP の SQL データセットは 書いた SQL がそのまま実行 されます。SQL の中に条件を書かない限り、実行ユーザーが本来アクセスできない BU や元帳のデータも返ります。

同じ「AP 請求書一覧」でも結果が違う

OTBI の分析：利用者が担当する BU の請求書だけが表示される（データセキュリティを継承）
BIP の SQL ：SQL に BU の条件がなければ、すべての BU の請求書が表示される

公式に確認できる対策

公式ドキュメントで確認できる仕組みを組み合わせて対策します。

対策	仕組み	効果と注意点
1. 実行できる人を絞る	カタログのフォルダ・レポートの権限	誰がレポートを開けるかを制御。見える行は絞れない
2. ESS ジョブの実行権限で絞る	ジョブ定義の作成時に自動で作られる「Run＋ジョブ定義名」の権限をカスタムロールに付与	スケジュール済プロセスから実行できる人を限定
3. SQL で実行ユーザーに応じて絞る	システム変数 :xdo_user_name（実行ユーザー名）を SQL で使う	ユーザーとデータアクセス範囲の対応を SQL で表現する必要がある
4. OTBI の分析をデータセットにする	Analysis データセット（BI Server に論理 SQL を発行）	サブジェクト領域の範囲内で、OTBI と同じ仕組みでデータを取得する
5. パラメータの選択肢を絞る	値リストの SQL を実行ユーザーで絞る	入力の誤りを防ぐ。パラメータを直接指定されると回避できるため単独では不十分

実務では「1 または 2」で実行者を限定したうえで、必要に応じて「3」で行を絞ります。全社の経理部門だけが使う帳票なら 1・2 で十分なこともあります。BU ごとの担当者に使わせる帳票では 3 または 4 を検討します。

システム変数を使った絞り込み

BIP には、実行ユーザーの情報を返すシステム変数があります。

システム変数	内容
xdo_user_name	レポートを実行したユーザーの ID（ユーザー名）
xdo_user_roles	実行ユーザーに割り当てられたロール
xdo_user_report_oracle_lang	レポート言語（例：JA）
xdo_user_ui_oracle_lang	UI の言語

実行ユーザー名を確認する（動作確認用）

SELECT :xdo_user_name  AS user_id,
       :xdo_user_roles AS user_roles
FROM   dual

公式ドキュメントには、xdo_user_name は 常に小文字 で渡されるという注意があります。Fusion のユーザー名と比較するときは、LOWER() や UPPER() で大文字・小文字をそろえます。

実行ユーザーに許可された BU だけを返す（考え方の例）

SELECT aia.invoice_num,
       aia.invoice_amount
FROM   ap_invoices_all aia
WHERE  aia.org_id IN (
         SELECT a.bu_id
         FROM   xx_user_bu_access a          -- 架空：ユーザーと BU の対応を返す副問合せ
         WHERE  LOWER(a.user_name) = LOWER(:xdo_user_name)
       )

上の xx_user_bu_access は説明用の架空の名前です。Fusion では独自のテーブルを作れないため、実際には ユーザーとデータアクセス（BU・元帳など）の割当てを保持している Fusion のテーブル・ビュー を、Tables and Views で確認して使います。割当ての持ち方はデータアクセスの種類（BU、元帳、データアクセスセットなど）で異なるため、セキュリティ設計の担当者と一緒に確認してください（セキュリティ設計）。

スケジュール実行とユーザー

スケジュール実行やバーストでは、誰の権限で SQL が実行されるか（ジョブを登録したユーザーか、実行したユーザーか）によって :xdo_user_name の値が変わります。実行者で絞る帳票は、オンライン実行とスケジュール実行の両方で、意図したユーザー名が渡るかをテストしてください。

性能（条件の書き方・日付）

BIP の SQL は大量データを扱えますが、書き方で性能が大きく変わります。

条件の書き方

1. バインド変数を使う：WHERE ledger_id = :P_LEDGER_ID のように書きます。値を文字列連結で埋め込むと、性能面でも安全面でも不利です。
2. 索引列をそのまま比較する：WHERE TO_CHAR(aia.invoice_id) = :P_ID や WHERE UPPER(aia.invoice_num) = ... のように列側を加工すると、索引が使えなくなります。加工はパラメータ側で行います。
3. 最も絞り込める条件を必ず入れる：元帳・BU・期間などの条件を必須パラメータにして、全件を走査しないようにします。
4. 「すべて」の扱いを決める：「すべて選択」を許可する場合は、NULL を渡す設定にして (:P_SOURCE IS NULL OR gjh.je_source = :P_SOURCE) のように書きます。ただしこの形は実行計画が不利になることがあるため、全件実行が多い帳票ではパラメータを必須にするか、データ量で使い分けます。
5. 複数選択は IN で受ける：gjh.je_source IN (:P_SOURCE) のように書きます。
6. SELECT * を使わない：必要な列だけを返します。XML の生成とレイアウトの処理も軽くなります。
7. 集計はデータベースで行う：明細をすべて返してレイアウトで合計するより、GROUP BY で集計したほうが速く、出力も小さくなります。

日付の扱い

日付列には時刻を持つものがあります。TRUNC() で列側を丸めず、範囲で比較 するのが基本です。

日付は範囲で比較する（終了日を含める書き方）

-- 悪い例：列を加工しているため索引が使われにくい
-- WHERE TRUNC(aia.creation_date) BETWEEN :P_FROM_DATE AND :P_TO_DATE

-- 良い例：列はそのまま、終了日の翌日未満で比較する
WHERE aia.creation_date >= :P_FROM_DATE
AND   aia.creation_date <  :P_TO_DATE + 1

日付パラメータの書式にも注意します。ESS ジョブ（BIPJobType）から実行するレポートでは、ジョブ定義の日付書式に yyyy-MM-dd を選び、データモデル側も同じ書式にそろえます。

実行時間とデータ量

1. オンライン実行には時間とデータ量の上限があります。上限値はリリースや環境の設定で異なるため、公式ドキュメントと環境の設定を確認します。
2. 大量出力の帳票は、スケジュール実行または ESS ジョブで動かします。
3. Oracle は SaaS 環境での BI Publisher のベストプラクティスを My Oracle Support（Doc ID 2145444.1）で公開しています。設計レビューの前に確認します。

よく使う SQL パターン

以下は学習用の例です。列名・コード値は Tables and Views と実データで確認してから使ってください。パラメータ名は例です。

AP 請求書一覧

BU・計上日の範囲で請求書を一覧にし、仕入先名と未払額を付けます。未払額は支払予定（AP_PAYMENT_SCHEDULES_ALL）を請求書単位に集計して求めます。

AP 請求書一覧（仕入先名・未払額付き）

SELECT bu.bu_name,
       aia.invoice_num,
       aia.invoice_date,
       aia.gl_date,
       ps.segment1                 AS supplier_number,
       hp.party_name               AS supplier_name,
       aia.invoice_currency_code,
       aia.invoice_amount,
       NVL(sch.amount_remaining, 0) AS amount_remaining
FROM   ap_invoices_all aia
       JOIN fun_all_business_units_v bu ON bu.bu_id     = aia.org_id
       JOIN poz_suppliers            ps ON ps.vendor_id = aia.vendor_id
       JOIN hz_parties               hp ON hp.party_id  = ps.party_id
       LEFT JOIN (
         SELECT apsa.invoice_id,
                SUM(apsa.amount_remaining) AS amount_remaining
         FROM   ap_payment_schedules_all apsa
         GROUP  BY apsa.invoice_id
       ) sch ON sch.invoice_id = aia.invoice_id
WHERE  aia.org_id  = :P_BU_ID
AND    aia.gl_date >= :P_FROM_DATE
AND    aia.gl_date <  :P_TO_DATE + 1
AND    aia.cancelled_date IS NULL               -- 取消済みを除く（例）
ORDER  BY hp.party_name, aia.invoice_num

実務での拡張ポイントは次のとおりです。

• 支払予定は 1 請求書に複数行あり得るため、集計してから結合 します（そのまま結合すると請求書金額が重複します）。
• 請求書の検証状態・承認状態・保留を出したい場合は、保留のテーブルや状態の列の意味を Tables and Views で確認して追加します。
• 外貨の請求書を元帳通貨で出す場合は、換算後金額の列を確認して使います。

GL 試算表

元帳・期間を指定して、勘定組合せごとの期首残高・当期発生・期末残高を出します。残高表（GL_BALANCES）を使います。

GL 試算表（元帳通貨・実績）

SELECT gcc.segment1 AS company,             -- 例：会社
       gcc.segment3 AS account,             -- 例：勘定科目
       SUM(NVL(gb.begin_balance_dr, 0) - NVL(gb.begin_balance_cr, 0)) AS begin_balance,
       SUM(NVL(gb.period_net_dr, 0))                                  AS period_dr,
       SUM(NVL(gb.period_net_cr, 0))                                  AS period_cr,
       SUM(NVL(gb.begin_balance_dr, 0) - NVL(gb.begin_balance_cr, 0)
         + NVL(gb.period_net_dr, 0)    - NVL(gb.period_net_cr, 0))    AS end_balance
FROM   gl_balances gb
       JOIN gl_ledgers           gl  ON gl.ledger_id            = gb.ledger_id
       JOIN gl_code_combinations gcc ON gcc.code_combination_id = gb.code_combination_id
WHERE  gb.ledger_id     = :P_LEDGER_ID
AND    gb.period_name   = :P_PERIOD_NAME
AND    gb.actual_flag   = 'A'                -- 実績
AND    gb.currency_code = gl.currency_code   -- 元帳通貨の行
GROUP  BY gcc.segment1, gcc.segment3
HAVING SUM(NVL(gb.begin_balance_dr, 0) - NVL(gb.begin_balance_cr, 0)
         + NVL(gb.period_net_dr, 0)    - NVL(gb.period_net_cr, 0)) <> 0
    OR SUM(NVL(gb.period_net_dr, 0) + NVL(gb.period_net_cr, 0)) <> 0
ORDER  BY gcc.segment1, gcc.segment3

試算表の注意点は次のとおりです。

1. 通貨と換算の行：残高表には外貨建ての行や換算に関わる行も含まれます。元帳通貨の合計だけを出す条件は、通貨・換算に関する列の意味を Tables and Views で確認して決めます。
2. 期首残高の意味：損益勘定は年度の初めに繰り越されるため、期首残高の扱いが勘定の種類で異なります。
3. 公式の試算表と照合する：作った SQL の結果は、事前定義の試算表レポートや Financial Reporting Web Studio の結果と必ず照合します。財務諸表そのものは 財務レポート のツールで作るのが基本です。

AR 未収残高

顧客ごとの未決済の債権残高を、支払期日つきで出します。支払予定（AR_PAYMENT_SCHEDULES_ALL）の未決済残高を使います。

AR 未収残高（顧客別・期日別）

SELECT hp.party_name               AS customer_name,
       hca.account_number,
       aps.trx_number,
       aps.class,                            -- 請求書・クレジットメモなどの区分
       aps.due_date,
       aps.invoice_currency_code,
       aps.amount_due_remaining,
       CASE
         WHEN aps.due_date >= :P_AS_OF_DATE      THEN '期日前'
         WHEN aps.due_date >= :P_AS_OF_DATE - 30 THEN '1-30日超過'
         WHEN aps.due_date >= :P_AS_OF_DATE - 60 THEN '31-60日超過'
         ELSE '61日以上超過'
       END                         AS aging_bucket
FROM   ar_payment_schedules_all aps
       JOIN hz_cust_accounts hca ON hca.cust_account_id = aps.customer_id
       JOIN hz_parties       hp  ON hp.party_id         = hca.party_id
WHERE  aps.org_id = :P_BU_ID
AND    aps.status = 'OP'                     -- 未決済（例）
AND    aps.amount_due_remaining <> 0
ORDER  BY hp.party_name, aps.due_date

この SQL は「現時点の未決済」を出します。過去日時点の残高（例：月末時点の売掛金残高）を出すには、その日以降の入金・消込・調整を戻して計算する必要があり、難易度が上がります。月末残高の照合には、事前定義の売掛金エージング・レポートや AR と GL の照合レポートの利用を先に検討してください（期間締め・GL 照合・レポート）。

落とし穴とベストプラクティス

よくあるミス

• _TL 表を言語条件なしで結合する：行が言語の数だけ増え、合計金額が膨らみます。
• 1 対多の結合で金額を重複させる：請求書と支払予定、仕訳ヘッダーと明細などは、集計してから結合します。
• 期間名の文字列で範囲指定する：'Jan-26' < 'Mar-26' のような比較は意味を持ちません。期間の開始日・終了日を使います。
• データセキュリティが効いていると思い込む：BIP の SQL は書いた条件どおりにしか絞られません。
• SEGMENT1 を会社と決めつける：セグメントの並びは COA の設計で決まります。
• 列名を推測で書く：EBS と同じ名前に見えても、Fusion では存在しない・意味が違う列があります。Tables and Views で確認します。

ベストプラクティスは次のとおりです。

1. SQL にコメントで前提を書く：セグメントの意味、通貨の条件、コード値の意味を SQL に残すと、保守とレビューが楽になります。
2. 既存の標準レポートと突合する：作った帳票は、事前定義レポート・OTBI・財務レポートの結果と数字を合わせてから公開します。
3. 小さく作って検証する：まず 1 つの BU・1 つの期間で結果を確認し、条件を広げながら性能を測ります。
4. リリースごとに回帰テストする：四半期アップデートでテーブルや列が変わることがあります。

試験対策ポイント

• 対応する試験：GL 模擬テスト の財務レポート関連の分野。SQL そのものは試験の中心ではありませんが、「OTBI はデータセキュリティを継承し、BIP の SQL は自動では効かない」という違いは、ツール選定のシナリオで判断の根拠になります。
• 残高（GL_BALANCES）・仕訳（ヘッダーと明細）・勘定組合せ（GL_CODE_COMBINATIONS）・会計期間（GL_PERIODS／GL_PERIOD_STATUSES）の関係は、GL の仕組みの理解にも直結します。

公式ドキュメント

• Create Datasets Using SQL Queries
• Include User Information Stored in System Variables in Your Report Data
• Create a Dataset Using an Analysis
• Tables and Views for Financials（26D）
• FUN_ALL_BUSINESS_UNITS_V（26C）
• Payables Invoices - Installments Real Time（粒度：AP_PAYMENT_SCHEDULES_ALL）
• Overview of Oracle BI Publisher Best Practices for SaaS Environments

関連ページ

• テーブル命名規則・Who 列・多言語・マルチ組織
• 主要テーブルマップ
• 主要テーブル・SQL チートシート
• BI Publisher（データモデル・レイアウト・バースティング・スケジュール）
• BIP 演習：仕訳明細レポートのデータモデルを作る
• SQL 演習：AP 請求書から GL 仕訳まで追跡する