[Home] [Help]
PACKAGE BODY: APPS.HRI_UTL_STAGE_TABLE
Source
1 PACKAGE BODY HRI_UTL_STAGE_TABLE AS
2 /* $Header: hriutstg.pkb 120.0 2006/01/20 02:05 jtitmas noship $ */
3
4 g_rtn VARCHAR2(30) := '
5 ';
6
7 -- -----------------------------------------------------------------------------
8 -- Returns name for the staging table generated from the master table name
9 -- -----------------------------------------------------------------------------
10 FUNCTION get_staging_table_name(p_master_table_name IN VARCHAR2)
11 RETURN VARCHAR2 IS
12
13 l_staging_table_name VARCHAR2(30);
14
15 BEGIN
16
17 -- Make a unique prefix
18 l_staging_table_name := SUBSTR(p_master_table_name, 1, 26) || '_STT';
19
20 -- Return staging table name
21 RETURN l_staging_table_name;
22
23 END get_staging_table_name;
24
25
26 -- -----------------------------------------------------------------------------
27 -- Creates staging table
28 -- -----------------------------------------------------------------------------
29 PROCEDURE create_staging_table(p_owner IN VARCHAR2,
30 p_master_table_name IN VARCHAR2) IS
31
32 -- Storage definition of master table
33 CURSOR storage_csr IS
34 SELECT
35 tablespace_name
36 ,initial_extent
37 ,next_extent
38 ,ini_trans
39 ,max_trans
40 ,min_extent
41 ,max_extent
42 ,pct_increase
43 ,'1' || partition_name order_by
44 FROM all_tab_partitions
45 WHERE table_owner = p_owner
46 AND table_name = p_master_table_name
47 UNION ALL
48 SELECT
49 tablespace_name
50 ,initial_extent
51 ,next_extent
52 ,ini_trans
53 ,max_trans
54 ,min_extents
55 ,max_extents
56 ,pct_increase
57 ,'2'
58 FROM all_tables
59 WHERE owner = p_owner
60 AND table_name = p_master_table_name
61 ORDER BY 9;
62
63 l_sql_stmt VARCHAR2(3000);
64 l_fnd_schema VARCHAR2(40);
65 l_dummy1 VARCHAR2(2000);
66 l_dummy2 VARCHAR2(2000);
67 l_tablespace_name VARCHAR2(100);
68 l_initial_extent VARCHAR2(100);
69 l_next_extent VARCHAR2(100);
70 l_ini_trans VARCHAR2(100);
71 l_max_trans VARCHAR2(100);
72 l_min_extents VARCHAR2(100);
73 l_max_extents VARCHAR2(100);
74 l_pct_increase VARCHAR2(100);
75 l_order_by VARCHAR2(100);
76 l_staging_table_name VARCHAR2(30);
77
78 BEGIN
79
80 -- Get staging table name
81 l_staging_table_name := get_staging_table_name
82 (p_master_table_name);
83
84 -- Get storage parameters of master table
85 OPEN storage_csr;
86 FETCH storage_csr INTO
87 l_tablespace_name,
88 l_initial_extent,
89 l_next_extent,
90 l_ini_trans,
91 l_max_trans,
92 l_min_extents,
93 l_max_extents,
94 l_pct_increase,
95 l_order_by;
96 CLOSE storage_csr;
97
98 -- Create staging table with a single worker column
99 -- and a single partition (more will be added in next step)
100 -- using the storage parameters of the master table
101 l_sql_stmt := 'CREATE TABLE ' || l_staging_table_name ||
102 ' (WORKER_ID NUMBER) ' ||
103 'PARTITION BY LIST(WORKER_ID)' ||
104 ' (PARTITION p1 VALUES(1)) ' ||
105 'TABLESPACE ' || l_tablespace_name || ' ' ||
106 'INITRANS ' || l_ini_trans || ' ' ||
107 'MAXTRANS ' || l_max_trans || ' ' ||
108 'STORAGE (INITIAL ' || l_initial_extent || ' ' ||
109 'NEXT ' || l_next_extent || ' ' ||
110 'MINEXTENTS ' || l_min_extents || ' ' ||
111 'MAXEXTENTS ' || l_max_extents || ' ' ||
112 'PCTINCREASE ' || l_pct_increase || ')';
113
114 -- Use AD API to create table
115 IF (fnd_installation.get_app_info('FND',l_dummy1, l_dummy2, l_fnd_schema)) THEN
116 ad_ddl.do_ddl(applsys_schema => l_fnd_schema,
117 application_short_name => 'HRI',
118 statement_type => ad_ddl.create_table,
119 statement => l_sql_stmt,
120 object_name => l_staging_table_name);
121 END IF;
122
123 END create_staging_table;
124
125
126 -- -----------------------------------------------------------------------------
127 -- Truncates staging table
128 -- -----------------------------------------------------------------------------
129 PROCEDURE empty_staging_table(p_owner IN VARCHAR2,
130 p_master_table_name IN VARCHAR2) IS
131
132 l_sql_stmt VARCHAR2(3000);
133
134 BEGIN
135
136 l_sql_stmt := 'TRUNCATE TABLE ' || p_owner || '.' || get_staging_table_name
137 (p_master_table_name);
138
139 EXECUTE IMMEDIATE l_sql_stmt;
140
141 END empty_staging_table;
142
143
144 -- -----------------------------------------------------------------------------
145 -- Adds in any new columns
146 -- Modifies any updated columns
147 -- Adds any required partitions
148 -- -----------------------------------------------------------------------------
149 PROCEDURE maintain_staging_table(p_owner IN VARCHAR2,
150 p_master_table_name IN VARCHAR2) IS
151
152 CURSOR add_column_csr(v_table_name VARCHAR2) IS
153 SELECT
154 column_name
155 ,DECODE(data_type,
156 'VARCHAR2', data_type || '(' || data_length || ')',
157 data_type) data_type
158 FROM all_tab_cols a
159 WHERE table_name = p_master_table_name
160 AND owner = p_owner
161 AND NOT EXISTS
162 (SELECT
163 null
164 FROM all_tab_cols b
165 WHERE b.table_name = v_table_name
166 AND b.owner = p_owner
167 AND b.column_name = a.column_name);
168
169 CURSOR modify_column_csr(v_table_name VARCHAR2) IS
170 SELECT
171 column_name
172 ,DECODE(data_type,
173 'VARCHAR2', data_type || '(' || data_length || ')',
174 data_type) data_type
175 FROM all_tab_cols a
176 WHERE table_name = p_master_table_name
177 AND owner = p_owner
178 AND data_type = 'VARCHAR2'
179 AND EXISTS
180 (SELECT
181 null
182 FROM all_tab_cols b
183 WHERE b.table_name = v_table_name
184 AND b.owner = p_owner
185 AND b.column_name = a.column_name
186 AND b.data_length < a.data_length);
187
188 l_count PLS_INTEGER;
189 l_staging_table_name VARCHAR2(30);
190 l_sql_stmt VARCHAR2(32000);
191 l_fnd_schema VARCHAR2(40);
192 l_dummy1 VARCHAR2(2000);
193 l_dummy2 VARCHAR2(2000);
194
195 BEGIN
196
197 -- Get staging table name
198 l_staging_table_name := get_staging_table_name
199 (p_master_table_name);
200
201 -- Add the required number of partitions
202 hri_utl_ddl.maintain_mthd_partitions
203 (p_table_name => l_staging_table_name,
204 p_table_owner => p_owner);
205
206 -- Add in any missing columns
207 l_sql_stmt := 'ALTER TABLE ' || p_owner || '.' || l_staging_table_name ||
208 ' ADD' || g_rtn ||
209 ' (' || g_rtn;
210
211 -- Loop through any columns that are to be added
212 l_count := 0;
213 FOR col_rec IN add_column_csr(l_staging_table_name) LOOP
214 l_sql_stmt := l_sql_stmt || ' ' || col_rec.column_name || ' ' ||
215 col_rec.data_type || ',' || g_rtn;
216 l_count := l_count + 1;
217 END LOOP;
218
219 -- Replace trailing comma with a close bracket
220 l_sql_stmt := RTRIM(l_sql_stmt, g_rtn);
221 l_sql_stmt := RTRIM(l_sql_stmt, ',') || ' )';
222
223 -- Execute if any columns need adding
224 IF (l_count > 0) THEN
225 EXECUTE IMMEDIATE l_sql_stmt;
226 END IF;
227
228 -- Modify any changed columns
229 l_sql_stmt := 'ALTER TABLE ' || p_owner || '.' || l_staging_table_name ||
230 ' MODIFY' || g_rtn ||
231 ' (' || g_rtn;
232
233 -- Loop through any changed columns
234 l_count := 0;
235 FOR col_rec IN modify_column_csr(l_staging_table_name) LOOP
236 l_sql_stmt := l_sql_stmt || ' ' || col_rec.column_name || ' ' ||
237 col_rec.data_type || ',' || g_rtn;
238 l_count := l_count + 1;
239 END LOOP;
240
241 -- Replace trailing comma with a close bracket
242 l_sql_stmt := RTRIM(l_sql_stmt, g_rtn);
243 l_sql_stmt := RTRIM(l_sql_stmt, ' ,') || ')';
244
245 -- Execute if any columns need adding
246 IF (l_count > 0) THEN
247 EXECUTE IMMEDIATE l_sql_stmt;
248 END IF;
249
250 END maintain_staging_table;
251
252 -- -----------------------------------------------------------------------------
253 -- Setup staging table
254 -- -----------------------------------------------------------------------------
255 PROCEDURE set_up(p_owner IN VARCHAR2,
256 p_master_table_name IN VARCHAR2) IS
257
258 CURSOR object_csr(v_table_name VARCHAR2) IS
259 SELECT count(*)
260 FROM all_tables
261 WHERE owner = p_owner
262 AND table_name = v_table_name;
263
264 l_staging_table_name VARCHAR2(30);
265 staging_table_exists_ind PLS_INTEGER;
266
267 BEGIN
268
269 -- Get staging table name
270 l_staging_table_name := get_staging_table_name
271 (p_master_table_name);
272
273 -- Open cursor to check if table exists
274 OPEN object_csr(l_staging_table_name);
275 FETCH object_csr INTO staging_table_exists_ind;
276 CLOSE object_csr;
277
278 IF (staging_table_exists_ind = 0 OR staging_table_exists_ind IS NULL) THEN
279
280 -- Create staging table if it doesn't exist
281 create_staging_table
282 (p_owner => p_owner,
283 p_master_table_name => p_master_table_name);
284
285 ELSE
286
287 -- Otherwise truncate staging table
288 empty_staging_table
289 (p_owner => p_owner,
290 p_master_table_name => p_master_table_name);
291
292 END IF;
293
294 -- Check for columns to add/modify and check partitions
295 maintain_staging_table
296 (p_owner => p_owner,
297 p_master_table_name => p_master_table_name);
298
299 END set_up;
300
301 -- -----------------------------------------------------------------------------
302 -- Moves data from the staging table to the master table
303 -- -----------------------------------------------------------------------------
304 PROCEDURE load_master_table(p_owner IN VARCHAR2,
305 p_master_table_name IN VARCHAR2) IS
306
307 l_sql_stmt VARCHAR2(32000);
308 l_col_list VARCHAR2(32000);
309
310 CURSOR column_csr IS
311 SELECT column_name
312 FROM all_tab_cols
313 WHERE table_name = p_master_table_name
314 AND owner = p_owner;
315
316 BEGIN
317
318 -- Enable parallel insert
319 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
320 EXECUTE IMMEDIATE 'ALTER SESSION FORCE PARALLEL QUERY';
321 EXECUTE IMMEDIATE 'ALTER TABLE ' || p_owner || '.' || p_master_table_name ||
322 ' PARALLEL';
323
324 -- Build up list of columns
325 FOR col_rec IN column_csr LOOP
326 l_col_list := l_col_list || ' ' || col_rec.column_name || ',' || g_rtn;
327 END LOOP;
328
329 -- Remove trailing comma
330 l_col_list := RTRIM(l_col_list, g_rtn);
331 l_col_list := RTRIM(l_col_list, ',');
332
333 -- Build insert statement
334 l_sql_stmt := 'INSERT /*+ APPEND */ INTO ' || p_master_table_name || g_rtn ||
335 ' (' || l_col_list || ' ) ' || g_rtn ||
336 'SELECT' || g_rtn ||
337 l_col_list || g_rtn ||
338 'FROM ' || get_staging_table_name
339 (p_master_table_name);
340
341 -- Load the table
342 EXECUTE IMMEDIATE l_sql_stmt;
343
344 END load_master_table;
345
346
347 -- -----------------------------------------------------------------------------
348 -- Clean up staging table
349 -- -----------------------------------------------------------------------------
350 PROCEDURE clean_up(p_owner IN VARCHAR2,
351 p_master_table_name IN VARCHAR2) IS
352
353 BEGIN
354
355 -- Move data to master table
356 load_master_table
357 (p_owner => p_owner,
358 p_master_table_name => p_master_table_name);
359
360 -- Truncate table
361 empty_staging_table
362 (p_owner => p_owner,
363 p_master_table_name => p_master_table_name);
364
365 END clean_up;
366
367
368 END HRI_UTL_STAGE_TABLE;