日日是Oracle APEX: レポートのリンク列のターゲットを条件によって切り替える

2022年3月26日土曜日

レポートのリンク列のターゲットを条件によって切り替える

対話モード・レポートなどの列のタイプとして、リンクを選択できます。その際にターゲットを設定しますが、行の種類によってリンク先を切り替えるにはどうしたらよいのか、という相談がありました。例えば、行ごとに開くページを変更する、といった用途になります。

サンプルとなるアプリケーションを作成して、いくつかの実装を行なってみます。

経費の申請、承認、却下を行う、簡単なアプリケーションを作成します。

作成するアプリケーションでは、申請済みの経費をレポートします。レポートには、それぞれの申請を更新するリンクを列として含みます。サインインしたユーザーが経費の申請者であれば、リンクをクリックすると申請を更新するフォームを開きます。承認者である場合は、承認または却下をするフォームを開くようにします。

以下より、実装について紹介します。

準備作業

作成するアプリケーションで使用する従業員のデータとして、サンプル・データセットに含まれる表EMPを使用します。

SQLワークショップのユーティリティのサンプル・データセットを開き、EMP/DEPTをインストールします。アプリケーションの作成は行いません。

経費申請を保持する表EXP_EXPENSESを作成します。

SQLワークショップのユーティリティのクイックSQLを開きます。モデルとして以下を記述します。

# prefix: exp
expenses
    invoice_date date /nn
    submit_date  date /nn
    submitted_by num /nn /references emp
    amount num /nn
    purpose vc80
    comment vc80
    status /check submitted,approved,rejected

SQLの生成、SQLスクリプトを保存、レビューおよび実行を順次実行します。

レビューおよび実行の画面では、アプリケーションの作成ではなく実行をクリックします。確認画面が開いたら、即時実行をクリックします。

表EXP_EXPENSESが作成されるので、アプリケーションの作成を実行します。

確認画面が表示されるので、アプリケーションの作成のボタンをクリックします。アプリケーション作成ウィザードが起動します。

表EXP_EXPENSESを扱う対話モード・レポートとフォームのページが、デフォルトで作成されます。アプリケーションの名前をサンプル経費精算とし、アプリケーションの作成を実行します。

リンクの切り替えを実装する元となるアプリケーションが作成されました。

表EMPを使った認証スキームの作成

表EMPに登録されている従業員にてアプリケーションにサインインするために、カスタムの認証スキームを作成します。

共有コンポーネントの認証スキームを開きます。

作成済みの認証スキームが一覧されます。作成をクリックします。

スキームの作成として、ギャラリからの事前構成済スキームに基づくを選択します。

次へ進みます。

認証スキームの名前を従業員とします。スキーム・タイプとしてカスタムを選択します。

設定の認証ファンクション名としてis_employeeを指定します。ソースのPL/SQLコードに、ファンクションis_employeeを記述します。

function is_employee(
    p_username in varchar2 -- 従業員番号
    , p_password in varchar2
)
return boolean
is
    l_exists number;
begin
    -- 従業員番号が表EMPにあれば成功。パスワードは確認しない。
    select 1 into l_exists from emp where empno = p_username;
    return true;
exception
    when others then
        return false;
end;

以上を設定し、認証スキームの作成をクリックします。

新たに認証スキーム従業員が作成され、カレントのスキームになります。

ユーザー名はEMPNO、つまり数値です。ユーザー名として数値を入力するのは大変なので、サインイン時に従業員名（ENAME）を選択できるようにします。

ログイン・ページ（ページ番号９９９９）を開き、ページ・アイテムP9999_USERNAMEを選択します。

タイプを選択リストに変更し、LOVのタイプとして共有コンポーネント、LOVとしてEMP.ENAMEを選択します。追加値の表示はOFF、NULL値の表示をONにし、NULL表示値は- 従業員を選択 -とします。

LOVのEMP.ENAMEは、アプリケーションを作成する元になった表EXP_EXPENSESの列SUBMITTED_BYに表EMPへの参照制約が定義されているため、アプリケーション作成ウィザードによって自動的に作成されています。

以上で、ログイン・ページは以下のようになります。

従業員を選択してサインインします。左上のメニューの表示が従業員番号になっているので、これも従業員名となるようにします。

サインインしたユーザーの従業員名をアプリケーション・アイテムに設定します。

共有コンポーネントのアプリケーション・アイテムを開きます。

作成済みのアプリケーション・アイテムが一覧されます。作成をクリックします。

名前をG_CURRENT_EMP_NAMEとします。それ以外はデフォルトから変更せず、アプリケーション・アイテムの作成をクリックします。

アプリケーション・アイテムG_CURRENT_EMP_NAMEが作成されます。

アプリケーション・アイテムは単に入れ物です。このアプリケーション・アイテムに値を設定するために、アプリケーションの計算を作成します。

共有コンポーネントのアプリケーションの計算を開きます。

作成済みのアプリケーションの計算が一覧されます。作成をクリックします。

計算アイテムとして先ほど作成したアプリケーション・アイテムG_CURRENT_EMP_NAMEを選択します。頻度の計算ポイントとして認証後を選択します。計算の計算タイプとして、SQL問合せ(単一の値を返す)を選択し、計算として以下を記述します。

select ename from emp where empno = :APP_USER

ユーザー認証後にAPP_USERに設定された従業員番号を元に、従業員名を検索しています。ユーザー認証後に従業員名が変わることはないので、認証後一度だけ実行します。

計算の作成をクリックします。

アプリケーションの計算が作成されます。

ナビゲーション・バーの表示を、作成したアプリケーション・アイテムG_CURRENT_EMP_NAMEを使うように変更します。

共有コンポーネントのナビゲーション・バー・リストを開きます。

デスクトップ・ナビゲーション・バーを開きます。

リスト詳細の名前に&APP_USER.というのがあります。これを開きます。

リスト・エントリ・ラベルを&APP_USER.から&G_CURRENT_EMP_NAME.（最後のピリオドは忘れずに）に変更します。

変更の適用をクリックします。

デスクトップ・ナビゲーション・バーのエントリの表示が変更されました。

アプリケーションを一旦サインアウトし、再度サインインして、ナビゲーション・バーの表示を確認します。

以上で、表EMPを使ったユーザー認証ができるようになりました。

経費申請を行うフォームの調整

デフォルトで作成される表EXP_EXPENSESのフォームは以下のようになっています。

Submitted Byはつねにサインインした従業員、The Commentは上司による承認や却下時に入力されるコメントなので、申請者からは入力不可とします。Statusについては、SUBMITTED（申請中）、APPROVED（承認）、REJECTED（却下）からひとつを選ぶ選択リストに変更します。

最初にStatusに使うLOVを作成します。

共有コンポーネントのLOVを開きます。

作成済みのLOVが一覧されます。作成をクリックします。

LOVの作成は最初からのままとし、次へ進みます。

名前はLOV_STATUSとします。タイプとしてStaticを選択します。

次へ進みます。

LOVの表示値と戻り値のペアとして、申請中 - SUBMITTED、承認 - APPROVED、却下 - REJECTEDを入力します。

LOVの作成をクリックします。

LOV_STATUSが作成されます。

ページ・デザイナにて、表EXP_EXPENSESの編集を行うページ（ページ名Exp Expense、ページ番号３）を開きます。

ページ・アイテムP3_SUBMITTED_BYを選択します。

識別のタイプを非表示に変更します。デフォルトのタイプとしてアイテムを選択し、アイテムにAPP_USERを入力します。これで経費の申請者は、サインインしたユーザーになります。

ページ・アイテムP3_THE_COMMENTを選択し、識別のタイプを表示のみに変更します。

ページ・アイテムP3_STATUSを選択します。

識別のタイプを選択リストに変更します。LOVのタイプは共有コンポーネント、LOVとして先ほど作成したLOV_STATUSを指定します。追加値の表示はOFF、NULL値の表示もOFFにします。デフォルトのタイプに静的を選択し、静的値としてSUBMITTEDを入力します。

読取り専用のタイプは常時とし、このフォームからはStatusの変更はできないようにします。

以上で経費の申請フォームの調整は完了です。

レポートから作成をクリックし、申請を行なってみます。

入力する項目はInvoice Date、Submit Date、Amount、Purposeです。

作成をクリックします。

Submitted ByとStatusが、デフォルトとして設定した値になっていることが確認できます。

共有コンポーネントとしてLOV_STATUSが作成されているので、レポートの列Statusの表示にも反映されるようにします。

ページ・デザイナにてレポートのページExpenses（ページ番号２）を開き、列STATUSを選択します。

識別のタイプをプレーン・テキスト(LOVに基づく)に変更し、LOVとしてLOV_STATUSを選択します。

レポートを再表示すると、Statusが申請中に変わっていることが確認できます。

経費申請を行うフォームの調整は以上で完了です。

承認または却下を行うフォームの作成

承認または却下の操作を行うためのプロシージャEXP_UPDATE_STATUSを作成し、そのプロシージャを呼び出すフォームのページを作成します。

SQLワークショップのSQLコマンドを開き、以下のスクリプトを実行してプロシージャEXP_UPDATE_STATUSを作成します。

create or replace procedure exp_update_status(
    p_id in number,
    p_status in varchar2,
    p_comment in varchar2
)
as
begin
    update exp_expenses set status = p_status, the_comment = p_comment where id = p_id;
end;

単にUPDATE文を一行実行しているだけの簡単なプロシージャです。

一般的な表を編集するフォームを調整してビジネス・ロジックを実装するより、ビジネス・ロジック（単純ですがこの場合は、申請を承認または却下する）をプロシージャに実装して、そのプロシージャへのユーザー・インターフェースを作成する方が、実装は容易です。

プロシージャが作成されたら、ページ作成ウィザードを呼び出します。

ページの作成を実行します。

フォームを選択します。

ローカル・プロシージャのフォームを選択します。

ページ番号は４、ページ名は承認または却下とします。ページ・モードとしてモーダル・ダイアログを選択します。この後にSQLなどにページ番号を指定する箇所があるため、ページ番号は必ず４にします。

次へ進みます。

作成するページはモーダル・ダイアログなので、ナビゲーション・メニューに表示される必要はありません。ナビゲーションのプリファレンスは、このページとナビゲーション・メニュー・エントリを関連付けないから変更しません。

次へ進みます。

ストアド・プロシージャ名として、先ほど作成したEXP_UPDATE_STATUSを選択します。

プロシージャ引数が表示されます。P_IDの表示タイプを非表示に変更します。ここでの表示タイプの選択肢には選択リストが含まれていないため、P_STATUSの表示タイプはテキスト・フィールドのままにします。P_COMMENTの表示タイプもテキスト・フィールドとします。

以上を設定し、作成をクリックします。

ページが作成されます。

ページ・デザイナにて、ページ・アイテムP4_STATUSを選択します。

識別のタイプを選択リストに変更します。LOVのタイプとして共有コンポーネントを選択し、LOVとしてLOV_STATUSを指定します。追加値の表示はOFF、NULL値の表示もOFFとします。

承認または却下を行うフォームのページは以上で完成です。

作成したフォームは、レポートから呼び出せるようになった後に確認します。

レポートのSELECT文にターゲットとなる列URLを追加

ページ番号２を開き、経費申請を一覧するレポートを変更します。

ソースのタイプをSQL問合せに変更し、SQL問合せとして以下を記述します。

select 
    a.id
    , a.INVOICE_DATE
    , a.SUBMIT_DATE
    , e.ename submitter
    , m.ename approver
    , a.AMOUNT
    , a.PURPOSE
    , a.THE_COMMENT
    , a.STATUS
    ,
    case
    -- 申請者による編集フォームへのリンク
    when e.empno = :APP_USER then
        apex_page.get_url(
            p_page => 3,
            p_items => 'P3_ID',
            p_values => a.id
        )
    -- 上司による承認却下のフォームへのリンク
    when m.empno = :APP_USER then
        apex_page.get_url(
            p_page => 4,
            p_items => 'P4_ID',
            p_values => a.id
        )
    else
        null
    end url
from EMP e 
    join EXP_EXPENSES a on e.empno = a.submitted_by
    join EMP m on e.mgr = m.empno
where 
-- 申請した経費と部下の経費のみ一覧する
(
    e.empno = :APP_USER
    or
    m.empno = :APP_USER
)

編集ページへのリンクは列URLになります。

APP_USERが申請者の従業員番号(e.empno)と一致している場合は、申請を編集するページ（ページ番号３）、マネージャーの従業員番号(m.empno)と一致している場合は、承認または却下を行うページ（ページ番号４）を宛先とするURLを、APEX_PAGE.GET_URLを呼び出して生成しています。

追加された列URLをレポートに表示する必要はないため、タイプは非表示列に変更します。

列URLをリンクのターゲットにします。

対話モード・レポートのAttributesを開き、リンクのターゲットを設定します。

リンク・ビルダー・ターゲットを開き、ターゲットのタイプをURLに変更します。URLとして、列URLの値に置き換えるための置換文字列#URL#を指定します。

OKをクリックします。

以上で、条件によってリンク先を切り替える実装ができました。

結果を確認するために任意の従業員にてサインインし、経費申請を行います。申請した従業員のマネージャにてサインインし直し、編集アイコンをクリックすると、承認または却下のフォームが開きます。

アイコンなども変更する方法

リンク先だけではなく、リンクとして表示するアイコンなども変更するには、リンクのターゲットではなく、リンクとなるHTML自体をSELECTの列として生成します。

SELECT文に、アイコンを含めて切り替えるために列URLの代わりに列EDITを追加します。

select 
    a.id
    , a.INVOICE_DATE
    , a.SUBMIT_DATE
    , e.ename submitter
    , m.ename approver
    , a.AMOUNT
    , a.PURPOSE
    , a.THE_COMMENT
    , a.STATUS
    ,
    case
    -- 申請者は編集アイコンでリンクを表示する
    when e.empno = :APP_USER then
        '<a href="' ||
        apex_page.get_url(
            p_page => 3,
            p_items => 'P3_ID',
            p_values => a.id
        )
        || '"><span class="fa fa-edit" aria-hidden="true"></span></a>'
    -- 上司にはお金のアイコンでリンクを表示する
    when m.empno = :APP_USER then
        '<a href="' ||
        apex_page.get_url(
            p_page => 4,
            p_items => 'P4_ID',
            p_values => a.id
        )
        || '"><span aria-hidden="true" class="fa fa-money"></span></a>'
    else
        null
    end edit
from EMP e 
    join EXP_EXPENSES a on e.empno = a.submitted_by
    join EMP m on e.mgr = m.empno
where 
-- 申請した経費と部下の経費のみ一覧する
(
    e.empno = :APP_USER
    or
    m.empno = :APP_USER
)

対話モード・レポートのソースのSQL問合せを変更します。

新たに追加された列EDITの、セキュリティの特殊文字のエスケープをOFFにします。

対話モード・レポートが生成している編集アイコンの列は不要になります。

Attrributesのリンクのリンク列をリンク列の除外に変更します。

以上で、編集リンクを示すアイコンも切り替わるようにうなりました。

画面を確認すると、以下のようになります。

対話グリッドでのHTML式の使用

対話グリッドなどでは、テンプレート・ディレクティブを使用することができます。

作成済みの対話モード・レポートのページをコピーします。

作成されたページの対話モード・レポートのリージョンの、識別のタイプを対話グリッドに変更します。

ソースのSQL問合せは以下の記述に変更します。

select 
    a.id
    , a.INVOICE_DATE
    , a.SUBMIT_DATE
    , e.ename submitter
    , m.ename approver
    , a.AMOUNT
    , a.PURPOSE
    , a.THE_COMMENT
    , a.STATUS
    ,
    -- 画面を切り替える識別子
    case
    when m.empno = :APP_USER then
        'M' -- 承認と却下
    else
        'E' -- 編集
    end edit_mode
    , apex_page.get_url(
        p_page => 3,
        p_items => 'P3_ID',
        p_values => a.id
    ) edit_url
    , apex_page.get_url(
        p_page => 4,
        p_items => 'P4_ID',
        p_values => a.id
    ) mgr_url
from EMP e 
    join EXP_EXPENSES a on e.empno = a.submitted_by
    join EMP m on e.mgr = m.empno
where 
-- 申請した経費と部下の経費のみ一覧する
(
    e.empno = :APP_USER
    or
    m.empno = :APP_USER
)

リンクの切り替えは、HTML式のテンプレート・ディレクティブを使って記述します。HTML式の記述に使うため、列EDIT_MODE、EDIT_URL、MGR_URLを追加します。

列EDIT_MODE、EDIT_URL、MGR_URLの表示は不要なので、これらの列を選択し、識別のタイプを非表示に変更します。

SELECT文に含まれている列の中で列IDは必ずしも表示されている必要はないので、この列を編集リンクにします。

列IDを選択し、タイプをHTML式に変更します。設定のHTML式として以下を記述します。

{case EDIT_MODE/}
{when E/}
<a href="&EDIT_URL."><span class="fa fa-edit" aria-hidden="true"></span></a>
{otherwise/}
<a href="&MGR_URL."><span aria-hidden="true" class="fa fa-money"></span></a>
{endcase/}