form开发------字段数据排序
1.新建参数,Name属性为SORT_RULE,Parameter Data Type属性为Char,Parameter Initial Value为NO_RULE
2.创建包BLOCK_SORT
PACKAGE BLOCK_SORT IS PROCEDURE GET_SORT(block_item IN VARCHAR2); PROCEDURE CALL_SORT(sort_rule IN VARCHAR2); END;-----包头
PACKAGE BODY BLOCK_SORT IS procedure GET_SORT(block_item IN VARCHAR2) IS block_name varchar2(80); item_name varchar2(80); order_by1 varchar2(80); i number; BEGIN i := INSTR(block_item,'_PROMPT'); -- fnd_message.debug('1'||:PARAMETER.SORT_RULE); IF i>1 THEN item_name := SUBSTR(block_item,i+8); block_name := SUBSTR(block_item,1,i-1)||'_FOLDER'; order_by1 :=GET_BLOCK_PROPERTY(block_name,ORDER_BY); IF INSTR(order_by1,item_name)>=1 THEN IF INSTR(UPPER(order_by1),'DESC')>=1 then :PARAMETER.SORT_RULE :='Y' || block_name || '|' || item_name; ELSE :PARAMETER.SORT_RULE :='Y' || block_name || '|' || item_name || ' DESC'; END IF; ELSE :PARAMETER.SORT_RULE :='Y' || 'LINE' || '|' || item_name; END IF; -- fnd_message.debug('1'); Do_Key('Execute_Query'); -- fnd_message.debug('2'); :PARAMETER.SORT_RULE :='N' || SUBSTR(:PARAMETER.SORT_RULE,2); END IF; END GET_SORT; PROCEDURE CALL_SORT(sort_rule IN VARCHAR2) IS block_name varchar2(80); sort_rule_new varchar2(80); sort_rule_old varchar2(80); i number; BEGIN -- FND_MESSAGE.DEBUG('2'||:PARAMETER.SORT_RULE); i := INSTR(sort_rule,'_'); if i=6 then sort_rule_old:=substr(sort_rule,1,5)||substr(sort_rule,13); else sort_rule_old:=:PARAMETER.SORT_RULE; end if; -- FND_MESSAGE.DEBUG('3'||sort_rule_old); i := INSTR(sort_rule_old,'|'); IF i>1 THEN block_name := SUBSTR(sort_rule_old,2,i-2)||'_FOLDER'; sort_rule_new := SUBSTR(sort_rule_old,i+1); -- FND_MESSAGE.DEBUG('4'||sort_rule_new); SET_BLOCK_PROPERTY(block_name,ORDER_BY,sort_rule_new); END IF; END CALL_SORT; END;
3.在标题数据块,需要排序的标题,比如在LINE_PROMPT数据块的OSALE_ORDER项WHEN-MOUSE-UP触发器中app_folder.event('WHEN-MOUSE-UP'); 后面增加代码
BLOCK_SORT.GET_SORT(:SYSTEM.TRIGGER_ITEM);
4.在要执行查询的数据块PRE-QUERY事件的app_folder.event('PRE-QUERY'); 代码后增加代码:
IF SUBSTR(:PARAMETER.SORT_RULE,1,1) = 'Y' THEN
BLOCK_SORT.CALL_SORT(:PARAMETER.SORT_RULE);
END IF;