[Home] [Help]
PACKAGE BODY: APPS.GMA_MIGRATION
Source
1 PACKAGE BODY gma_migration AS
2 /*$Header: GMAMIGB.pls 120.8 2006/11/21 19:46:20 txdaniel noship $*/
3 FUNCTION gma_migration_start
4 ( p_app_short_name IN VARCHAR2,
5 p_mig_name IN VARCHAR2
6 )
7 RETURN NUMBER IS
8 pragma AUTONOMOUS_TRANSACTION;
9
10 v_mig_date DATE:= SYSDATE;
11 l_pos NUMBER := 0;
12 v_run_id NUMBER;
13 v_message_token VARCHAR2(80) := 'GMA_MIGRATION_DB_ERROR';
14 raise_gma_insert_message EXCEPTION;
15 BEGIN
16
17 IF p_mig_name IS NULL OR p_app_short_name IS NULL THEN
18 v_message_token := 'GMA_MIGRATION_FAILED';
19 RAISE raise_gma_insert_message;
20 ELSE
21 l_pos := 1;
22 SELECT gma_mig_run_id_s.nextval INTO v_run_id
23 FROM dual;
24 END IF;
25
26 IF v_run_id IS NULL THEN
27 v_message_token := 'GMA_MIGRATION_FAILED';
28 RAISE raise_gma_insert_message;
29 ELSE
30 l_pos := 2;
31 INSERT INTO gma_migration_control
32 (application_short_name,
33 run_id,
34 mig_name,
35 mig_start_date
36 )
37 VALUES (p_app_short_name,
38 v_run_id,
39 p_mig_name,
40 v_mig_date
41 );
42
43 l_pos := 3;
44 END IF;
45 COMMIT;
46
47 RETURN v_run_id;
48
49 EXCEPTION
50 WHEN raise_gma_insert_message THEN
51 gma_insert_message(
52 p_run_id => v_run_id,
53 p_table_name => 'gma_migration_log',
54 p_DB_ERROR => SQLERRM,
55 p_param1 => NULL,
56 p_param2 => NULL,
57 p_param3 => NULL,
58 p_param4 => NULL,
59 p_param5 => NULL,
60 p_message_token => v_message_token,
61 p_message_type => 'D',
62 p_line_no => NULL,
63 p_position => l_pos,
64 p_base_message => ''
65 );
66 RAISE;
67 WHEN OTHERS THEN
68 gma_insert_message(
69 p_run_id => v_run_id,
70 p_table_name => 'gma_migration_log',
71 p_DB_ERROR => SQLERRM,
72 p_param1 => 'an error occurred while updating gma_migration_log',
73 p_param2 => NULL,
74 p_param3 => NULL,
75 p_param4 => NULL,
76 p_param5 => NULL,
77 p_message_token => v_message_token,
78 p_message_type => 'D',
79 p_line_no => NULL,
80 p_position => l_pos,
81 p_base_message => ''
82 );
83 RAISE;
84 COMMIT;
85 END gma_migration_start;
86
87 PROCEDURE gma_insert_message(
88 p_run_id IN NUMBER,
89 p_table_name IN VARCHAR2,
90 p_DB_ERROR IN VARCHAR2,
91 p_param1 IN VARCHAR2,
92 p_param2 IN VARCHAR2,
93 p_param3 IN VARCHAR2,
94 p_param4 IN VARCHAR2,
95 p_param5 IN VARCHAR2,
96 p_message_token IN VARCHAR2,
97 p_message_type IN VARCHAR2,
98 p_line_no IN NUMBER,
99 p_position IN NUMBER,
100 p_base_message IN VARCHAR2
101 ) IS
102 BEGIN
103 GMA_COMMON_LOGGING.Gma_Migration_CentraL_Log(
104 P_Run_Id => p_run_id,
105 P_log_level => 5,
106 P_App_short_name => 'GMA',
107 P_Message_Token => p_message_token,
108 P_context => NULL,
109 P_Table_Name => p_table_name,
110 P_Param1 => p_param1,
111 P_Param2 => p_param2,
112 P_Param3 => p_param3,
113 P_Param4 => p_param4,
114 P_Param5 => p_param5,
115 P_Db_Error => p_db_error);
116
117 END gma_insert_message;
118
119 PROCEDURE gma_migration_end(l_run_id IN NUMBER)
120 IS
121 pragma AUTONOMOUS_TRANSACTION;
122
123 v_mig_end_date DATE:= SYSDATE;
124 l_pos NUMBER := 0;
125 p_mig_name VARCHAR2(80) := NULL;
126 v_message_token VARCHAR2(80) := 'GMA_MIGRATION_DB_ERROR';
127 raise_gma_insert_message EXCEPTION;
128 BEGIN
129
130 IF l_run_id IS NULL THEN
131 v_message_token := 'GMA_MIGRATION_FAIL';
132 RAISE raise_gma_insert_message;
133
134 ELSE
135 p_mig_name := get_mig_name(l_run_id);
136 l_pos := 1;
137
138 IF p_mig_name IS NULL THEN
139 p_mig_name := '';
140 v_message_token := 'GMA_MIGRATION_FAIL';
141 RAISE raise_gma_insert_message;
142 END IF;
143
144 UPDATE gma_migration_control
145 SET mig_end_date = v_mig_end_date
146 WHERE run_id = l_run_id;
147 END IF;
148 l_pos := 2;
149 COMMIT;
150
151 EXCEPTION
152 WHEN raise_gma_insert_message THEN
153 gma_insert_message(
154 p_run_id => l_run_id,
155 p_table_name => 'gma_migration_log',
156 p_DB_ERROR => SQLERRM,
157 p_param1 => 'an error occurred while updating gma_migration_log',
158 p_param2 => NULL,
159 p_param3 => NULL,
160 p_param4 => NULL,
161 p_param5 => NULL,
162 p_message_token => v_message_token,
163 p_message_type => 'D',
164 p_line_no => NULL,
165 p_position => l_pos,
166 p_base_message => ''
167 );
168 RAISE;
169 WHEN OTHERS THEN
170 gma_insert_message(
171 p_run_id => l_run_id,
172 p_table_name => 'gma_migration_log',
173 p_DB_ERROR => SQLERRM,
174 p_param1 => NULL,
175 p_param2 => NULL,
176 p_param3 => NULL,
177 p_param4 => NULL,
178 p_param5 => NULL,
179 p_message_token => v_message_token,
180 p_message_type => 'D',
181 p_line_no => NULL,
182 p_position => l_pos,
183 p_base_message => ''
184 );
185 RAISE;
186 COMMIT;
187 END gma_migration_end;
188
189 FUNCTION get_mig_run_id(p_mig_name IN VARCHAR2) RETURN NUMBER IS
190 v_run_id NUMBER;
191
192 CURSOR c_get_mig_run_id IS
193 SELECT run_id from gma_migration_control WHERE mig_name = p_mig_name
194 AND mig_end_date is NULL
195 ORDER BY run_id desc;
196
197 BEGIN
198 OPEN c_get_mig_run_id;
199 FETCH c_get_mig_run_id into v_run_id;
200 CLOSE c_get_mig_run_id;
201
202 RETURN v_run_id;
203
204 EXCEPTION
205 WHEN OTHERS THEN
206 RAISE;
207
208 END get_mig_run_id;
209
210 FUNCTION get_mig_name(p_run_id IN NUMBER) RETURN VARCHAR2 IS
211 v_mig_name gma_migration_control.mig_name%TYPE;
212
213 CURSOR c_get_mig_name IS
214 SELECT mig_name from gma_migration_control WHERE run_id = p_run_id
215 ORDER BY run_id desc;
216
217 BEGIN
218 OPEN c_get_mig_name;
219 FETCH c_get_mig_name into v_mig_name;
220 CLOSE c_get_mig_name;
221
222 RETURN v_mig_name;
223
224 EXCEPTION
225 WHEN NO_DATA_FOUND THEN
226 RETURN NULL;
227 WHEN OTHERS THEN
228 gma_insert_message(
229 p_run_id => p_run_id,
230 p_table_name => 'gma_migration_log',
231 p_DB_ERROR => SQLERRM,
232 p_param1 => NULL,
233 p_param2 => NULL,
234 p_param3 => NULL,
235 p_param4 => NULL,
236 p_param5 => NULL,
237 p_message_token => 'GMA_MIGRATION_DB_ERROR',
238 p_message_type => 'D',
239 p_line_no => NULL,
240 p_position => NULL,
241 p_base_message => ''
242 );
243 RAISE;
244
245 END get_mig_name;
246
247 FUNCTION get_gma_mig_messages (p_name IN VARCHAR2, p_rowid IN ROWID)
248 RETURN VARCHAR2 IS
249
250 v_err_message VARCHAR2(4000) ;
251 v_table_name VARCHAR2(4000) ;
252 v_db_error VARCHAR2(4000) ;
253 v_param1 VARCHAR2(4000) ;
254 v_param2 VARCHAR2(4000) ;
255 v_param3 VARCHAR2(4000) ;
256 v_param4 VARCHAR2(4000) ;
257 v_param5 VARCHAR2(4000) ;
258
259
260 CURSOR c1
261 IS
262 SELECT *
263 FROM gma_migration_log
264 WHERE message_token = p_name AND
265 rowid = p_rowid;
266 l_rec c1%rowtype;
267 MISSING_DATA EXCEPTION;
268 BEGIN
269 OPEN c1;
270 FETCH c1 INTO l_rec;
271 IF c1%NOTFOUND THEN
272 CLOSE c1;
273 RAISE MISSING_DATA;
274 END IF;
275 CLOSE c1;
276
277 IF p_name = 'GMD_UNEXPECTED_ERROR' THEN
278 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
279 FND_MESSAGE.SET_TOKEN('ERROR', l_rec.db_error);
280 ELSE
281 FND_MESSAGE.set_name(NVL(l_rec.msg_app_short_name, 'GMA'), p_name);
282
283 IF (l_rec.param1 IS not NULL) THEN
284 FND_MESSAGE.set_token(NVL(l_rec.token1, 'PARAM1'),l_rec.param1);
285 ELSIF (p_name IN ('GMA_MIGRATION_TABLE_SUCCESS', 'GMA_MIGRATION_TABLE_SUCCESS_RW')) THEN
286 FND_MESSAGE.set_token('TABLE_NAME', l_rec.table_name);
287 END IF;
288
289 IF (l_rec.param2 IS not NULL) THEN
290 FND_MESSAGE.set_token(NVL(l_rec.token2, 'PARAM2'),l_rec.param2);
291 END IF;
292
293 IF (l_rec.param3 IS not NULL) THEN
294 FND_MESSAGE.set_token(NVL(l_rec.token3, 'PARAM3'),l_rec.param3);
295 END IF;
296
297 IF (l_rec.param4 IS not NULL) THEN
298 FND_MESSAGE.set_token(NVL(l_rec.token4, 'PARAM4'),l_rec.param4);
299 END IF;
300
301 IF (l_rec.param5 IS not NULL) THEN
302 FND_MESSAGE.set_token(NVL(l_rec.token5, 'PARAM5'),l_rec.param5);
303 END IF;
304
305 IF (l_rec.param6 IS not NULL) THEN
306 FND_MESSAGE.set_token(NVL(l_rec.token6, 'PARAM6'),l_rec.param6);
307 END IF;
308 END IF;
309
310 RETURN FND_MESSAGE.GET;
311
312 EXCEPTION
313 WHEN MISSING_DATA THEN
314 RETURN (p_name);
315 WHEN OTHERS THEN
316 RETURN(p_name);
317 END get_gma_mig_messages;
318
319 PROCEDURE run IS
320 l_pos NUMBER := 0;
321 v_run_id NUMBER;
322 new_run_id NUMBER;
323 v_mig_name gma_migration_control.mig_name%TYPE;
324 v_mig_name2 VARCHAR2(80) := NULL;
325
326 BEGIN
327 l_pos := 1;
328 v_mig_name := 'gma_mig_test';
329 v_run_id := gma_migration_start(p_app_short_name => 'GMA', p_mig_name => v_mig_name);
330
331 l_pos := 2;
332
333 new_run_id := get_mig_run_id(p_mig_name => v_mig_name);
334
335 v_mig_name2 := get_mig_name(v_run_id);
336
337 l_pos := 3;
338
339 gma_migration_end(l_run_id => v_run_id);
340
341 l_pos := 4;
342
343 EXCEPTION
344 WHEN OTHERS THEN
345 gma_insert_message(
346 p_run_id => v_run_id,
347 p_table_name => 'gma_migration_log',
348 p_DB_ERROR => SQLERRM,
349 p_param1 => NULL,
350 p_param2 => NULL,
351 p_param3 => NULL,
352 p_param4 => NULL,
353 p_param5 => NULL,
354 p_message_token => 'GMA_MIGRATION_DB_ERROR',
355 p_message_type => 'D',
356 p_line_no => NULL,
357 p_position => l_pos,
358 p_base_message => ''
359 );
360 RAISE;
361 COMMIT;
362 END;
363
364
365 END;