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;-----包头

1
2
3
4
5
6
7
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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
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;