大家好,
以前在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);