1 BEGIN
 2       SELECT param_current_index + param_countperpage
 3         INTO v_sum
 4         FROM DUAL;
 5 
 6       v_sql_count :=
 7          '(SELECT count(*) FROM item_base2@testadd a, iim b WHERE a.itemnumber = b.iprod
 8                     AND a.active = 1
 9                     AND b.iid = ''IM''';
10       v_sql :=
11          'SELECT itemnormalname, itemchinaname, itemspec, uscatvls_6,
12                         puom
13                    FROM item_base2@testadd a, iim b
14                   WHERE a.itemnumber = b.iprod
15                     AND a.active = 1
16                     AND b.iid = ''IM''';
17 
18       IF param_pretext IS NOT NULL
19       THEN
20          v_sql :=
21                v_sql
22             || ' AND (UPPER(iim.iprod)) LIKE UPPER('''
23             || param_pretext
24             || '%'')'
25             || 'OR iim.idesc LIKE N''%'
26             || param_pretext
27             || '%'') ORDER BY iim.iprod ASC ,iim.idesc ASC';
28          v_sql_count :=
29                v_sql_count
30             || ' AND (UPPER(iim.iprod)) LIKE UPPER('''
31             || param_pretext
32             || '%'')'
33             || ' OR iim.idesc LIKE N''%'
34             || param_pretext
35             || '%'')';
36          v_sql :=
37                ' SELECT * FROM (SELECT A.itemnormalname,A.itemchinaname,A.itemspec,A.uscatvls_6,A.puom,ROWNUM RN,('
38             || v_sql_count
39             || ')) totalRowsCount FROM ('
40             || v_sql
41             || ')A WHERE ROUNUM<='
42             || v_sum
43             || ') WHERE RN>'
44             || param_current_index;
45       END IF;
46 
47       OPEN o_result FOR v_sql;
48    END;