[Home] [Help]
PACKAGE BODY: APPS.CSI_MIG_SLABS_PKG
Source
1 PACKAGE BODY csi_mig_slabs_pkg AS
2 /* $Header: csislabb.pls 115.7 2004/03/30 22:30:31 srramakr ship $*/
3
4 /*Get a value from the sql statement passed*/
5 FUNCTION get_sql_value(sqlstr IN VARCHAR) RETURN NUMBER IS
6 l_cursor_handle INTEGER;
7 l_value NUMBER;
8 l_n_temp NUMBER;
9 BEGIN
10 l_cursor_handle := dbms_sql.open_cursor;
11
12 begin
13 dbms_sql.parse(l_cursor_handle,sqlstr,dbms_sql.native);
14 exception
15 when others then
16 raise_application_error(-20001,'error while parsing sql:'||sqlerrm);
17 end;
18
19 DBMS_SQL.DEFINE_COLUMN(l_cursor_handle,1,l_value);
20 l_n_temp := dbms_sql.execute(l_cursor_handle);
21
22 IF dbms_sql.fetch_rows(l_cursor_handle) > 0 THEN
23 dbms_sql.column_value(l_cursor_handle,1,l_value);
24 END IF;
25 --Close cursor
26 DBMS_SQL.close_cursor(l_cursor_handle);
27 return l_value;
28 END;
29
30
31 /* Get min and max value of next available slab*/
32 PROCEDURE get_table_slabs(p_table_name IN VARCHAR,
33 p_module in VARCHAR,
34 p_slab_number IN NUMBER,
35 x_start_slab OUT nocopy NUMBER,
36 x_end_slab OUT nocopy NUMBER) IS
37 CURSOR upg_slabs IS
38 select slab_start, slab_end
39 FROM cs_upg_slabs
40 where slab_number = p_slab_number
41 and source_table = p_table_name
42 and module = p_module;
43 BEGIN
44 for r in upg_slabs loop
45 x_start_slab := r.slab_start;
46 x_end_slab := r.slab_end;
47 exit when true;
48 end loop;
49 END;
50
51
52 /*Create slabs for a table/view */
53 PROCEDURE create_table_slabs(p_table_name IN VARCHAR,
54 p_module in VARCHAR,
55 p_min_sql IN VARCHAR,
56 p_max_sql IN VARCHAR,
57 p_no_of_slabs IN NUMBER,
58 p_min_slab_size IN NUMBER) IS
59 l_min NUMBER; --min pk
60 l_max NUMBER; -- max pk
61 l_slab_size NUMBER; --size of the slab
62 l_slab_num NUMBER;
63 l_last_slab NUMBER; --end of last slab
64 l_max_slab NUMBER;
65 l_temp NUMBER;
66 begin
67 delete from cs_upg_slabs
68 where source_table = p_table_name
69 and module = p_module;
70
71 l_min := get_sql_value(p_min_sql);
72 l_max := get_sql_value(p_max_sql);
73 l_slab_size := ceil((l_max-l_min+1) / p_no_of_slabs);
74
75 --if calculated slab size is smaller then use the min slab size
76
77 if l_slab_size < p_min_slab_size then
78 l_slab_size := p_min_slab_size;
79 end if;
80
81 l_last_slab := l_min - 1;
82 l_slab_num := 0;
83
84 /*if no available in the table then dont create slabs*/
85 if l_min is not null then
86 loop
87 l_slab_num := l_slab_num + 1;
88 l_max_slab := l_last_slab + l_slab_size;
89 if l_max_slab > l_max then
90 l_max_slab := l_max;
91 end if;
92 INSERT INTO CS_UPG_SLABS
93 (SOURCE_TABLE,
94 MODULE,
95 SLAB_NUMBER,
96 SLAB_START,
97 SLAB_END,
98 CREATED_BY,
99 CREATED_ON)
100 VALUES (p_table_name,
101 p_module,
102 l_slab_num,
103 l_last_slab + 1,
104 l_max_slab,
105 fnd_global.user_id,
106 sysdate);
107 l_last_slab := l_last_slab + l_slab_size;
108 exit when l_last_slab >= l_max ;
109 end loop;
110 end if;
111 end;
112
113 /*Create slabs for a table/view */
114 PROCEDURE create_table_slabs(
115 p_table_name IN VARCHAR,
116 p_module IN VARCHAR,
117 p_pk_column IN VARCHAR,
118 p_no_of_slabs IN NUMBER ,
119 p_min_slab_size IN NUMBER
120 ) IS
121
122 l_min_str varchar2(250);
123 l_max_str varchar2(250);
124
125 begin
126 l_min_str := 'select min('||p_pk_column||') from '||p_table_name;
127 l_max_str := 'select max('||p_pk_column||') from '||p_table_name;
128 create_table_slabs(p_table_name => p_table_name,
129 p_module => p_module,
130 p_min_sql=>l_min_str,
131 p_max_sql=>l_max_str,
132 p_no_of_slabs => p_no_of_slabs,
133 p_min_slab_size => p_min_slab_size
134 );
135 end;
136
137 end csi_mig_slabs_pkg;