盒子
导航
文章目录
  1. 一、实现要求
  2. 二、实现步骤
    1. 01.创建一个表
    2. 02.创建一个视图
    3. 03.创建一个程序包
    4. 04.编写Form,实现多选操作
  3. 三、参考资料

App_Multi实现Form行选择

Oracle提供了App_Multi程序包(详见AppCore.pll)来支持在多记录行的FORM当中选择一条或者多条记录。通过按住Ctrl键来实现跨行选择记录,通过点击起始行,按住Shift键来连续选择多行,通过编辑/全选和编辑/撤销全选来实现全选和撤销全选。使用该API在选中记录时记录会高亮,Ctrl和Shift的支持是通过在WHEN-MOUSE-CLICK当中调用代码App_Multi.Event(WHEN-MOUSE-CLICK);来实现的,这里有个不好的地方就是你放开键盘按键再单击的时候前面的选择就会释放,所以在客户化的开发当中一般不这么做,换一种简单的方法就是增加一个复选框列来实现。

这是实现结果效果图:
Form line select
Form line select

下面是实现步骤和注意事项,最后奉上该例子的代码。

一、实现要求

开发一个Form来测试App_Multi API,将选择的数据保存到数据库的表当中。

二、实现步骤

01.创建一个表

CUX_TEST_TEMP,用来保存数据,代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Create table  
create table CUX_TEST_TEMP
(
ID NUMBER,
RESULT VARCHAR2(240)
)
tablespace APPS_TS_CUX_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);

02.创建一个视图

CUX_APP_MUTIL_TEST_V,代码如下:

1
2
3
4
5
6
7
CREATE OR REPLACE VIEW CUX_APP_MUTIL_TEST_V AS  
SELECT t.organization_id
,t.inventory_item_id
,t.segment1
,t.description
FROM mtl_system_items_b t
WHERE rownum < 20;

03.创建一个程序包

Cux_App_Mutil_Test_Pkg,用来处理保存按钮逻辑,实现将选择的数据保存下来,代码如下:

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
CREATE OR REPLACE PACKAGE cux_app_mutil_test_pkg IS  
-- Author : ADMINISTRATOR
-- Created : 2014/9/17 10:41:46
-- Purpose :
-- Public type declarations
TYPE rec_test IS RECORD(
organization_id NUMBER
,segment1 VARCHAR2(240));

-- Public constant declarations
TYPE tbl_test IS TABLE OF rec_test INDEX BY BINARY_INTEGER;

-- Public function and procedure declarations
PROCEDURE save_data(p_tbl IN tbl_test);

END cux_app_mutil_test_pkg;
/
CREATE OR REPLACE PACKAGE BODY cux_app_mutil_test_pkg IS
PROCEDURE save_data(p_tbl IN tbl_test) IS
BEGIN
DELETE FROM cux_test_temp;
FOR i IN 1 .. p_tbl.count LOOP
INSERT INTO cux_test_temp
VALUES
(p_tbl(i).organization_id
,p_tbl(i).segment1);
END LOOP;

COMMIT;
END;

END cux_app_mutil_test_pkg;
/

04.编写Form,实现多选操作

需要注意的是SELECT_ALL和DESELECT_ALL触发器放在BLOCK级别的话要设置执行层次为Before,即优先于FORM级别的同名触发器执行。

01、增加触发器:

触发器名称(BLOCK级别) 调用的标准代码
KEY-CLRREC APP_MULTI.EVENT(‘KEY-CLRREC’);
DESELECT_ALL APP_MULTI.EVENT(‘DESELECT_ALL’);
KEY-CLRFRM APP_MULTI.EVENT(‘KEY-CLRFRM’);
KEY-CREREC APP_MULTI.EVENT(‘KEY-CREREC’);
KEY-DELREC APP_MULTI.EVENT(‘KEY-DELREC’);
POST-BLOCK APP_MULTI.EVENT(‘POST-BLOCK’);
PRE-BLOCK APP_MULTI.EVENT(‘PRE-BLOCK’);
SELECT_ALL APP_MULTI.EVENT(‘SELECT_ALL’);
WHEN-CLEAR-BLOCK APP_MULTI.EVENT(‘WHEN-CLEAR-BLOCK’);
WHEN-MOUSE-CLICK APP_MULTI.EVENT(‘WHEN-MOUSE-CLICK’); (注释掉)
WHEN-NEW-RECORD-INSTANCE APP_MULTI.EVENT(‘WHEN-NEW-RECORD-INSTANCE’);
触发器名称(FORM级别) 调用代码
MULTI_RETURN_ACTION 自定义触发器,处理选择和取消的逻辑,一般定义在From级别

02、在FORM级别的WHEN-NEW-FORM-INSTANCE中定义记录组:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DECLARE  
x_rec_group_id recordgroup;
x_col_id_holder groupcolumn;
x_rec_group_name VARCHAR2(30);
BEGIN
x_rec_group_name := 'MUTILTST_MULTI';
x_rec_group_id := find_group(x_rec_group_name);

IF id_null(x_rec_group_id) THEN
x_rec_group_id := create_group(x_rec_group_name);
x_col_id_holder := add_group_column(x_rec_group_id
,'REC_NUM'
,number_column);
END IF;

x_col_id_holder := add_group_column(x_rec_group_id
,'ORGANIZATION_ID'
,number_column);
x_col_id_holder := add_group_column(x_rec_group_id
,'SEGMENT1'
,char_column
,30);

END;

03、实现MULTI_RETURN_ACTION触发器代码:

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
DECLARE  
x_action VARCHAR2(64);
x_blockname VARCHAR2(30);
x_rec_count VARCHAR2(3);
BEGIN

x_blockname := name_in('GLOBAL.APPCORE_MULTI_BLOCK');
x_action := name_in('GLOBAL.APPCORE_MULTI_ACTION');

--Check for all the events before checking for
--the label change event because that gets set
--for many of the other events as well.
IF (x_action = 'KEY-CLRRREC') THEN
clear_record;

IF NOT (form_success) THEN
RAISE form_trigger_failure;
END IF;
ELSIF (x_action = 'SELECT_ALL') THEN
app_multi.event('SELECT_ALL', x_blockname);

ELSIF (x_action = 'DESELECT_ALL') THEN
app_multi.event('DESELECT_ALL', x_blockname);

ELSIF (x_action = 'LABEL_CHANGE') THEN

--Number of selected has changed
fill_up_lines_rec_grp('SINGLE_CHANGE');

END IF;
END;

04、修改SELECT_ALL触发器代码(执行菜单编辑/全选时会触发):

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
DECLARE  
x_group_id recordgroup;
x_num_records NUMBER;
rec_num INTEGER;
l_groupname VARCHAR2(30);
l_block_name VARCHAR2(30);
BEGIN
app_multi.event('SELECT_ALL');

l_block_name := :system.current_block;
l_groupname := l_block_name || '_MULTI';

x_group_id := find_group(l_groupname);
x_num_records := get_group_row_count(x_group_id);

FOR i IN 1 .. x_num_records LOOP
copy('Y', l_block_name || '.SELECT_LINE');

rec_num := get_group_number_cell(l_groupname || '.REC_NUM', i);
fill_up_lines_rec_grp('ALL');

go_record(rec_num);
END LOOP;

first_record;
END;

05、修改DESELECT_ALL代码(执行菜单编辑/全选时会触发):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DECLARE  
x_group_id recordgroup;
x_num_records NUMBER;
x_curr_rec_num NUMBER;
l_groupname VARCHAR2(30);
l_block_name VARCHAR2(30);
BEGIN

l_block_name := :system.current_block;
l_groupname := l_block_name || '_MULTI';

x_group_id := find_group(l_groupname);
x_num_records := get_group_row_count(x_group_id);

FOR i IN 1 .. x_num_records LOOP
x_curr_rec_num := get_group_number_cell(l_groupname || '.REC_NUM', i);
go_record(x_curr_rec_num);
copy('N', l_block_name || '.SELECT_LINE');
END LOOP;
app_multi.event('DESELECT_ALL');
first_record;
END;

06、实现数据块勾选框ITEM的WHEN-CHECKBOX-CHANGED代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE  
cur_record NUMBER;
cur_block VARCHAR2(80);
BEGIN
cur_block := name_in('SYSTEM.CURSOR_BLOCK');
cur_record := name_in('SYSTEM.CURSOR_RECORD');

IF name_in('MUTILTST.SELECT_LINE') = 'Y' THEN
app_multi.select_records(cur_block, cur_record);
ELSE
app_multi.deselect_record(cur_block, cur_record);
END IF;
END;

07、实现保存按钮代码(将FORM的RECORDGROUP值放入我们自定义的RECORD TABLE TYPE中传给包):

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
PROCEDURE process IS  
x_num_records NUMBER;
l_step VARCHAR2(30);
x_rec_group VARCHAR2(30);
l_rec_table cux_app_mutil_test_pkg.tbl_test;
BEGIN
l_step := '001';
x_num_records := get_group_row_count(find_group('MUTILTST_MULTI'));
--app_multi.get_group_count(blockname=>'MUTILTST');
l_step := '002';
x_rec_group := 'MUTILTST_MULTI';
l_step := '003';
FOR i IN 1 .. x_num_records LOOP
l_rec_table(i).organization_id := get_group_number_cell('MUTILTST_MULTI.ORGANIZATION_ID'
,i);
l_rec_table(i).segment1 := get_group_char_cell('MUTILTST_MULTI.SEGMENT1'
,i);
END LOOP;
l_step := '004';

cux_app_mutil_test_pkg.save_data(p_tbl => l_rec_table);
EXCEPTION
WHEN OTHERS THEN
fnd_message.debug(l_step);
END;

08、fill_up_lines_rec_grp存储过程代码:

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
PROCEDURE fill_up_lines_rec_grp(event IN VARCHAR2) IS  
x_num_records NUMBER;
x_curr_rec_num NUMBER;
l_beg_index NUMBER;
rec_num INTEGER;
l_cur_block VARCHAR2(30);
l_group_name VARCHAR2(30);

BEGIN
l_cur_block := name_in('SYSTEM.CURSOR_BLOCK');
l_group_name := 'MUTILTST_MULTI';

x_num_records := get_group_row_count(find_group(l_group_name));

--fnd_message.debug(x_num_records);

IF x_num_records > 0 THEN
IF event = 'ALL' THEN
l_beg_index := 1;
ELSE
l_beg_index := x_num_records;
END IF;

FOR i IN l_beg_index .. x_num_records LOOP
x_curr_rec_num := get_group_number_cell('MUTILTST_MULTI.REC_NUM', i);
go_record(x_curr_rec_num);
set_group_number_cell(l_group_name || '.ORGANIZATION_ID'
,i
,name_in(l_cur_block || '.ORGANIZATION_ID'));
set_group_char_cell(l_group_name || '.SEGMENT1'
,i
,name_in(l_cur_block || '.SEGMENT1'));
END LOOP;
END IF;
END;

三、参考资料

  1. AppCore.pll / App_Multi
  2. 例子代码下载:App_Multi.zip
  3. 采购超级用户 > 采购订单 > 自动创建FORM源码
  4. How do I use the APP_MULTI package to support multi-selection in my Oracle Form?