固定値に設定した値が取得できないとき規定値のSQLで取得した値を使用したいがPL/SQLを使用でしょうか？

Hi.  I foud the following information.  I hope you can translate this.

Yes, this can definitely be solved using PL/SQL.

Cause of the Issue

In IFS Data Migration (FNDMIG), when a Fixed Value (such as @10.ORDER_NO) is assigned to a Method List Attribute for Execution Order 20, the migration framework prioritizes the Fixed Value. If Execution Order 10 does not run (because HEADER_CHECK is not 1), @10.ORDER_NO evaluates to NULL. Because a Fixed Value is defined, FNDMIG passes NULL directly and bypasses evaluating the Source Mapping's Default Value / Default SQL.

Recommended PL/SQL Solutions

Approach 1: Helper PL/SQL Function in the Method List (Most Flexible)

Create a small helper PL/SQL function in a custom utility package (or standard package) that accepts HEADER_CHECK, @10.ORDER_NO, and the fallback default value as parameters.

1. Write the PL/SQL Function:

    

    

    

   FUNCTION Get_Effective_Order_No ( header_check_ IN NUMBER, order_no_10_ IN VARCHAR2, default_no_ IN VARCHAR2 ) RETURN VARCHAR2 IS BEGIN IF NVL(header_check_, 0) = 1 AND order_no_10_ IS NOT NULL THEN RETURN order_no_10_; ELSE RETURN default_no_; END IF; END Get_Effective_Order_No;

2. Add a Method List Entry (Execution Order 15):

   • Add a new entry between Order 10 and Order 20 calling your function Custom_Migration_API.Get_Effective_Order_No.

   • Pass :HEADER_CHECK, @10.ORDER_NO, and your default SQL/column as parameters in Source Mapping.

3. Configure Execution Order 20:

   • In Method List Attributes for Execution Order 20, set the Fixed Value of ORDER_NO to FUNCTION_RESULT@15.

Approach 2: Handle Logic Inside the Method Called by Execution Order 20

If Execution Order 20 invokes a custom package or modified API procedure, move the fallback check directly into the PL/SQL body of the method:

PROCEDURE Create_Detail ( header_check_ IN NUMBER, order_no_10_ IN VARCHAR2, ... ) IS loc_order_no_ VARCHAR2(50); BEGIN IF NVL(header_check_, 0) = 1 AND order_no_10_ IS NOT NULL THEN loc_order_no_ := order_no_10_; ELSE -- Execute default SQL lookup or sequence fetch SELECT default_order_seq.NEXTVAL INTO loc_order_no_ FROM DUAL; END IF; -- Proceed with order processing using loc_order_no_ ... END Create_Detail;

Approach 3: Source Mapping Default Expression (Remove Fixed Value)

Remove the Fixed Value from Method List Attribute 20 so FNDMIG falls back to the Source Mapping. Then in the Source Mapping -> Default Value for ORDER_NO, use an inline PL/SQL expression or subselect:

NVL(CASE WHEN :HEADER_CHECK = 1 THEN '@10.ORDER_NO' END, (SELECT default_val FROM ...))

Additional information -

here's a quick summary of the best approaches:

Root Cause

When a Fixed Value is set on a Method List Attribute, the framework uses it directly — even if it resolves to NULL (because Execution Order 10 didn't run). The Source Mapping default is never checked as a fallback.

Best Solution: Conditional Expression via PL/SQL Function

Add an intermediate execution order (e.g., Order 15) that calls a PL/SQL function to decide the value:

FUNCTION Get_Effective_Order_No ( header_check_ IN NUMBER, order_no_10_ IN VARCHAR2, default_no_ IN VARCHAR2 ) RETURN VARCHAR2 IS BEGIN IF NVL(header_check_, 0) = 1 AND order_no_10_ IS NOT NULL THEN RETURN order_no_10_; ELSE RETURN default_no_; -- your fallback default SQL value END IF; END;

Then in Execution Order 20, reference FUNCTION_RESULT@15 instead of @10.ORDER_NO directly.

Alternative: Remove the Fixed Value Entirely

If you remove the Fixed Value from Order 20 and instead express the conditional logic in the Source Mapping Default SQL, the framework will evaluate it naturally:

CASE WHEN :HEADER_CHECK = 1 THEN :ORDER_NO_10 ELSE (SELECT ... FROM ...) END

This avoids needing an extra execution order.

Key Takeaway

The Fixed Value → Source Mapping Default fallback gap is by design in FNDMIG. PL/SQL — either via an intermediate execution order function or directly in the Source Mapping Default SQL — is the correct workaround.

データベース上にPL/SQLを登録せず、IFS Cloud Web標準以外の機能は使用せず、IFS Cloud Web標準の画面上でPL/SQLを登録または移行ジョブから無条件で呼び出し実行させる方法は、ありますか？