弹性域查询

大家好，

      以前在Form里做过关键性和说明性弹性域，但没有做弹性域的范围查找，最近在项目上遇到，请大家看看，也许对你有帮助。

需求：

      修改总账凭证查询Form，增加一些查询条件，使之能查询到按日记账行的科目帐户范围以及行发生额进行查询。

原来的查询条件为：

 

修改后查询条件为：

 

实现方法：

弹性域查找实现步骤：

以物料查找为例：

查找模块为QUERY_FIND,被查找模块为：MAIN

1、  在查询模块加两个Item  CATEGORY_RANGE_LOW, CATEGORY_RANGE_HIGH，子类属性为Text_item 分别为弹性域范围的起止

2、  在合适的地方定义上面定义的弹性域，比如在WHEN-NEW-FORM-INSTANCE里，定义如下：

             FND_RANGE_FLEX.DEFINE(

                     BLOCK=> 'QUERY_FIND',--查找模块

/*对应CATEGORY_RANGE_LOW、CATEGORY_RANGE_HIGH*/

                     FIELD=> ' CATEGORY _RANGE',

                     APPL_SHORT_NAME=> 'INV',

                     CODE=> 'MCAT',

                     NUM=>'101',

                     VALIDATE=> 'NONE',

                     REQUIRED=> 'N',

                     USEDBFLDS=> 'N',

                     ALLOWNULLS=> 'Y');

 

3、  分别在新建的Item里添加两个trgger: KEY-LISTVAL、WHEN-NEW-ITEM-INSTANCE，

KEY-LISTVAL：的内容为：do_key('EDIT_FIELD');

 

WHEN-NEW-ITEM-INSTANCE 的内容为：

APP_STANDARD.EVENT('WHEN-NEW-ITEM-INSTANCE');

SYNCHRONIZE;

FND_FLEX.EVENT( 'WHEN-NEW-ITEM-INSTANCE' );

 

4、  在被查找的模块里添加Pre-query触发器，内容如下：

FND_FLEX_FIND.QUERY_KFLEX_RANGE( APPL_SHORT_NAME=>'INV'

                               , CODE=>'MCAT'

                               , NUM=> 101--:CONTROL.STRUCTURE_ID

                               , LOW_SEGMENTS=>:QUERY_FIND.CATEGORY_RANGE_LOW --起

                               , HIGH_SEGMENTS=>:QUERY_FIND.CATEGORY_RANGE_HIGH -止

                               , SEGMENTS_FIELD=>'MAIN.CATEGORY_CONCAT_SEGS');--Main对应的弹性域字段

 

参考系统标准功能里的Form的账户范围查找实现后，发现它是这样的：

1、  在查找Block中加两个Item，分别为账户范围的起止。

 

下载$AU_TOP/forms/US/GLXJEENT.fmb，copy 到你自己的Form下面，这时人就可以把新建的Item的子类属性改为：

添加两个trigger:

WHEN-NEW-ITEM-INSTANCE:

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

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

WHEN-VALIDATE-ITEM

FND_FLEX.event('WHEN-VALIDATE-ITEM');

2、  在WHEN-NEW-FORM-INSTANCE里添加弹性域定义

        FND_RANGE_FLEX.define(

          block => 'FOLDER_QF',

          field => 'ACCOUNTING_FLEXFIELD',

          appl_short_name => 'SQLGL',

          code => 'GL#',

          num => to_char(l_num),--to_char(gl_sob.coa_id),

          validate =>'NONE',

          required => 'N',

          allownulls => 'Y',

          usedbflds => 'N',

          insertable => 'ALL',

          updateable => 'ALL');

3、  在被查找的模块的Pro-query加入如下代码：

                 FND_FLEX_FIND.query_kflex_range(

               'SQLGL',

               'GL#',

               gl_sob.coa_id,

               :FOLDER_QF.accounting_flexfield_low,

               :FOLDER_QF.accounting_flexfield_high,

               'GCC'）

但在项目的需求上，被查找的模块不是查找的第一个模块，这样一来

FND_FLEX_FIND.query_kflex_range 就不能用了，因为最后一个参数在被查找的模块中没有。我们需求生成一个Where条件来实现从行到头的查找，用的是exists子句。

在查看了FND_FLEX_FIND.query_kflex_range的代码之后发现它的实现逻辑按弹性域各个字段的ASCII的顺序比较，最后自己写了一个Procedure，它的前五个参数和FND_FLEX_FIND.query_kflex_range是一样的，参数table_name是gl_code_combinations的别名，segments_field是返回的where条件：

PROCEDURE query_kflex_range(appl_short_name VARCHAR2,

                             code            VARCHAR2,

                             num             NUMBER,

                             low_segments    VARCHAR2,

                             high_segments   VARCHAR2,

                             table_name      VARCHAR2,

                             segments_field  out VARCHAR2) IS

 

    start_lo               NUMBER;

    end_lo                 NUMBER;

    start_hi               NUMBER;

    end_hi                 NUMBER;

    appid                  NUMBER;

    delim                  VARCHAR2(1);

    query_condition        VARCHAR2(2000) default NULL;

    high_value             VARCHAR2(240);

    low_value              VARCHAR2(240);

    l_count                NUMBER;

 

  BEGIN

    appid := FND_UTILITIES.GET_APPLICATION_ID(appl_short_name);

    delim := FND_FLEX_APIS.GBL_GET_SEGMENT_DELIMITER(appid, code, num);

 

    start_lo := 1;

    end_lo := 1;

    start_hi := 1;

    end_hi := 1;

    query_condition := NULL;

    l_count         := 1;

 

    WHILE (end_lo != 0 AND end_hi != 0) LOOP

      end_lo := nvl(INSTR(low_segments, delim, start_lo), 0);

      IF (end_lo != 0) THEN

        low_value := SUBSTR(low_segments, start_lo, end_lo - start_lo);

      ELSE

        low_value := SUBSTR(low_segments, start_lo);

      END IF;

      start_lo := end_lo + 1;

 

      end_hi := nvl(INSTR(high_segments, delim, start_hi), 0);

      IF (end_hi != 0) THEN

        high_value := SUBSTR(high_segments, start_hi, end_hi - start_hi);

      ELSE

        high_value := SUBSTR(high_segments, start_hi);

      END IF;

      start_hi := end_hi + 1;

 

      IF ((low_value IS NOT NULL) AND (high_value IS NULL)) THEN

        query_condition := query_condition || table_name||'.SEGMENT'||l_count|| ' >= ''' || low_value||'''';

      ELSIF ((low_value IS NULL) AND (high_value IS NOT NULL)) THEN

        query_condition := query_condition || table_name||'.SEGMENT'||l_count|| ' <= ''' || high_value||'''';

      ELSIF (low_value = high_value) THEN

        query_condition := query_condition || table_name||'.SEGMENT'||l_count|| ' = ''' || low_value||'''';

      ELSIF ((low_value IS NOT NULL) AND (high_value IS NOT NULL)) THEN

        query_condition := query_condition || table_name||'.SEGMENT'||l_count|| ' BETWEEN ''' ||

                           low_value|| ''' AND ''' || high_value||'''';

      END IF;

      IF (end_lo != 0 AND end_hi != 0) THEN

             IF ((low_value IS NOT NULL) AND (high_value IS NOT NULL)) THEN

          query_condition := query_condition || ' AND ';

        END IF;

      END IF;

      l_count := l_count + 1;

    END LOOP;

    if ((Length(query_condition) - 4) = INSTR(query_condition,' AND ',-1)) then

    query_condition := SUBSTR(query_condition,0,(LENGTH(query_condition) - 5));

    end if;

    IF (query_condition IS NOT NULL) THEN

      --COPY(query_condition, segments_field);

      segments_field := query_condition;

    END IF;

  EXCEPTION

     WHEN OTHERS THEN

  fnd_flex_private.flex_exception('FND_FLEX_FIND.QUERY_KFLEX_RANGE');

  RAISE;

  END query_kflex_range;

 

4、  在被查找模块的per-query里添加如下代码：

                      CUX_FOLDER_QF.query_kflex_range(

                 'SQLGL',

                 'GL#',

                 gl_sob.coa_id,

                 :FOLDER_QF.accounting_flexfield_low,

                 :FOLDER_QF.accounting_flexfield_high,

                 'GCC',

                 new_query);

               new_query :='EXISTS (SELECT ''Y''

                                      FROM GL_JE_LINES_V GJL,GL_CODE_COMBINATIONS GCC

                                     WHERE GL_JE_BATCHES_HEADERS_V.JE_HEADER_ID = GJL.JE_HEADER_ID

                                       AND GJL.CODE_COMBINATION_ID                                                   = GCC.CODE_COMBINATION_ID

                                       AND '||new_query||')';

                       --fnd_message.debug(new_query);

                       app_query.append('FOLDER',new_query);