Define a KFF step by step

BaseTable 表增加一个外键列

在Base Table 中定义一个字段 XXX_ID ，这个用于保存 KFF表记录的主键（对于 Base Table 就是外键列），比如对于本例，如果要增加一个 Account 的KFF ，需要在 Base Table 上增加一个 XXX_ID外键字段用于保存捕获的 CODE_COMBINATION_ID

Block 中创建一个 Flexfield的隐藏 item

Block中定义一个隐藏字段用于保存上边一步创建的 FK 字段，设置 canvas为 NULL ，TEXT_ITEM property class

Block 中创建一个 Flexfield显示字段

创建了隐藏字段，当然要创建一个显示的字段让用户来输入，这里我们需要创建一个 non-database 字段用来展示合并的科目值， subclass Information 继续使用TEXT_ITEM, 设置合适 canvas 来显示，LOV 设置为 \'ENABLE_LIST_LAMP\'， \'Validate from list\' is set to No

When-New-Form-Instance 中加入FlexField 的定义

在When-New-Form-Instance 中加入 FlexField的定义，当然好的戏，代码类似于

 SELECT CHART_OF_ACCOUNTS_ID INTO :PARAMETER.CHART_OF_ACCOUNTS_ID

         FROM ORG_ORGANIZATION_DEFINITIONS

         WHERE ORGANIZATION_ID = :PARAMETER.ORG_ID;

         FND_KEY_FLEX.DEFINE(

         BLOCK => \'TOMAI_MAIN_HEADER_BLK\',

          FIELD => \'TO_ACCOUNT_PT\',

          CODE => \'GL#\',

          APPL_SHORT_NAME =>\'SQLGL\',

          NUM => \':PARAMETER.CHART_OF_ACCOUNTS_ID\',

          ID => \'TO_ACCOUNT_PT_id\',

          VRULE => \'\\nSUMMARY_FLAG\\nI\\nAPPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\\nN\\0GL_GLOBAL\\nDETAIL_POSTING_ALLOWED\\nE\\nAPPL=INV;NAME=INV_VRULE_POSTING\\nN\',

           REQUIRED => \'N\',

           DINSERT  => \'Y\',

          VALIDATE => \'FULL\',

          USEDBFLDS => \'N\');

FND_KEY_FLEX.DEFINE 的用法

FND_KEY_FLEX.DEFINE( 
block => \'Custom block\',
Field => \'BTL_KFF\',-- 第三步创建的显示 item
ID => \'XXX_ID\',-- 第二步创建的隐藏 item
Appl_short_name => \'SQLGL\',
Code => \'GL#\',--ID_FLEX_CODE 
Num => \'101\',--Chart of account
Vrule => \'GL_GLOBAL\nDETAIL_POSTING_ALLOWED \nE\nAPPL=\'\'SQLGL\'\';
name=Parent Values are not allowed\nN\'
);

Trigger 中调用FlexField 标准的方法

Trigger包括

    PRE-QUERY

    POST-QUERY

    PRE-INSERT

    PRE-UPDATE

    WHEN-VALIDATE-RECORD

    WHEN-NEW-ITEM-INSTANCE

WHEN-VALIDATE-ITEM

代码类似于：

    if ( :system.mode = \'NORMAL\' ) then
       fnd_flex.event( \'WHEN-VALIDATE-ITEM\' );
    end if;

    app_standard.event(\'WHEN-NEW-ITEM-INSTANCE\');
    fnd_flex.event(\'WHEN-NEW-ITEM-INSTANCE\' );

--Loads the flexfields (in our case, it populates
--the concatenated field on execute query).
    FND_FLEX.EVENT(\'POST-QUERY\');

--If you don\'t do this, whatever query criteria you may enter in
-- the concatenated flex field, it is not taken into account.
    FND_FLEX.EVENT(\'PRE-QUERY\' );

    APP_STANDARD.EVENT(\'KEY-LISTVAL\');
    FND_FLEX.EVENT(\'KEY-LISTVAL\' );

一般情况下，我们会把 fnd_flex.event这样的代码放在 Form 级别，这样我们新加的 Trigger只要不是 Override 模式, 那么 fnd_flex.event都会被执行到，当然如果你的 trigger 为override 模式或者需要比较复杂的 Flexfield 的业务逻辑，那么你就必须手工添加 fnd_flex.event 代码到你的 Trigger中。对于本例 Account 的KFF 来说，因为要校验 Account ID 是否有效，所以需要在 When-Validate-Item中加入一些其他校验

   FND_FLEX.EVENT(\'WHEN-VALIDATE-ITEM\');

    IF :BLOCK.XXX_ID = -1 THEN

    FND_MESSAGE.SET_STRING(\'You Have Selected An Undefined Code Combination !\');

    FND_MESSAGE.SHOW;

    RAISE FORM_TRIGGER_FAILURE;

END IF;

Flexfield 相关的API

fnd_flex.event的代码位于 FNDSQF.pll

  procedure event(event_name varchar2) is

  begin

    fnd_flex_private.flex_debug(\'BEGIN FND_FLEX.EVENT(\'||event_name||\')\');

    if ((event_name = \'WHEN-VALIDATE-ITEM\') AND

        (name_in(\'system.mode\') = \'ENTER-QUERY\')) then

       GOTO lbl_return;

    end if;

    --

    -- Synchronize call seems to solve some problems in

    -- event handling in Forms side.

    -- According to Peter this call does nothing, but let\'s

    -- call it. G.Olgun

    --

    -- Per Peter\'s request commenting out the code.

    --

    --IF (event_name = \'WHEN-NEW-ITEM-INSTANCE\') THEN

    --   synchronize;

    --END IF;

    --

    user_exit(\'FND FFLEX \' || event_name);

    if (NOT Form_Success) then

       fnd_flex_private.flex_failure(\'user_exit(FND FFLEX \' ||

                                          event_name || \') is failed.\');

       copy(NULL, \'GLOBAL.FND_FLEX_NAVIGATE\');

       copy(NULL, \'GLOBAL.FND_FLEX_NAVIGATE_PUBLIC\');

       copy(NULL, \'GLOBAL.FND_FLEX_READONLY\');

       copy(NULL, \'GLOBAL.FND_FLEX_ENABLELOV\');

       raise FORM_TRIGGER_FAILURE;

    end if;

    -- if (event_name = \'POST-QUERY\') then

    --   set_record_property(name_in(\'SYSTEM.TRIGGER_RECORD\'),

    --                     name_in(\'SYSTEM.TRIGGER_BLOCK\'),

    --                     STATUS, QUERY_STATUS);

    -- end if;

    fnd_flex_private.navigate_from_flex;

    if (event_name = \'WHEN-NEW-ITEM-INSTANCE\') then

      fnd_flex_private.set_flex_item_properties;

    end if;

    --

    -- This step was asked for by Peter Wallack to enable localizations

    -- This would eventually be moved to APPCORE.

    --

    if (event_name = \'WHEN-VALIDATE-RECORD\') then

      copy(\'Y\', \'GLOBAL.APPCORE_WVR_ZOOM\');

      execute_trigger(\'ZOOM\');

      if not form_success then

        raise form_trigger_failure;

       end if;

    end if;

    <>

      fnd_flex_private.flex_debug(\'END FND_FLEX.EVENT(\'||event_name||\')\');

      RETURN;

  EXCEPTION

     WHEN OTHERS THEN

         fnd_flex_private.flex_exception(\'FND_FLEX.EVENT\');

         RAISE;

  end event;

FlexField 的相关表

This table captures the information of all the Key FlexFields. The main columns in this table are:

    APPLICATION_ID ‐ Column consists of Application ID

    ID_FLEX_CODE ‐ Column KFF Code (like ‘ GL#’ , ‘AR# ’ etc.)

    ID_FLEX_NAME  -  KFF Name (like ‘Accounting Flexfield’, ‘Category Flexfield’..etc.)

    APPLICATION_TABLE_NAME – Name of combination table (like ‘GL_CODE_COMBINATIONS’ , ‘FA_LOCATIONS’ etc.)

This table stores structure information about key Flexfields. Each Structure is uniquely identified by

    APPLICATION_ID – Module Code

    ID_FLEX_CODE  – Code of KFF

    ID_FLEX_NUM – Number of a Structure

It captures the information of Segments. Each Segment is Uniquely identified by

    APPLICATION_ID – Module Code

    ID_FLEX_CODE – Key Flexfield code

    ID_FLEX_NUM – Key flexfield structure number

    SEG_NUM – Segment number

    FLEX_VALUE_SET_ID – Flexfield value set identifier

This table captures the information of each Segment’s Value Set. Each Value Set is Uniquely identified by FLEX_VALUE_SET_ID as Foreign Key of FND_ID_FLEX_SEGMENTS Table.

This table captures the information each Value codes of a Value Set of a Segment. Each Value Code is uniquely identified by

    FLEX_VALUE_SET_ID

    FLEX_VALUE_ID

This table captures the information of each Value Description of a Value Set of a Segment. Each Value Description is uniquely identified by FLEX_VALUE_ID.