1 PACKAGE BODY IGI_SLS_UPG_PKG AS
2 -- $Header: igislsub.pls 120.0 2008/01/17 21:52:27 vspuli noship $
3
4 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5 l_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
6 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
7 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
8 l_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 l_error_level NUMBER := FND_LOG.LEVEL_ERROR;
10 l_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
11
12 l_path VARCHAR2(50) := 'PLSQL.igi_sls_upg_pkg.';
13
14 /*-----------------------------------------------------------------
15 This procedure writes to the error log.
16 -----------------------------------------------------------------*/
17 PROCEDURE Write_To_Log (p_level IN NUMBER, p_path IN VARCHAR2, p_mesg IN VARCHAR2) IS
18 BEGIN
19 IF (p_level >= l_debug_level ) THEN
20 FND_LOG.STRING (p_level , l_path || p_path , p_mesg );
21 END IF;
22 END Write_To_Log;
23
24
25 /*********** This procedure populates the temp table with all the info needed by us.....***********/
26 procedure populate_temp_table_old is
27 begin
28
29 /*** Note the where clause.. we are taking care of tables that are secured and allocated to
30 security groups, and not tables that are seucred and have no allocations.. ***/
31 INSERT INTO igi_sls_upg_itf(SELECT a.table_name, a.owner , a.sls_table_name, NULL,NULL,NULL,get_sls_grps(a.table_name),NULL,NULL
32 FROM igi_sls_secure_tables a
33 WHERE a.table_name IN( 'PO_VENDORS','PO_VENDOR_CONTACTS', 'PO_VENDOR_SITES_ALL')
34 AND A.table_name NOT IN (SELECT old_table_name FROM igi_sls_upg_itf)
35 AND a.table_name IN (SELECT sls_allocation FROM igi_sls_allocations));
36
37 END populate_temp_table_old;
38
39
40 FUNCTION get_changed_secured_list RETURN list_of_old_tables AS
41 l_list list_of_old_tables;
42 BEGIN
43
44 SELECT distinct old_table_name BULK COLLECT INTO l_list
45 FROM igi_sls_upg_itf ORDER BY old_table_name;
46
47 write_to_log (l_excep_level, 'get_changed_secured_list',SQL%ROWCOUNT ||' rows picked and ' );
48 -- Now work with data in the collections
49 RETURN l_list;
50
51 END get_changed_secured_list;
52
53 FUNCTION get_new_secured_list RETURN list_of_old_tables AS
54 l_list list_of_old_tables;
55 BEGIN
56 SELECT new_table_name BULK COLLECT INTO l_list FROM igi_sls_upg_itf ORDER BY new_table_name;
57
58 write_to_log (l_excep_level, 'get_new_secured_list',SQL%ROWCOUNT ||' rows picked and ' );
59
60 -- Now work with data in the collections
61 RETURN l_list;
62 END get_new_secured_list;
63
64 /********* Function to form the security groups list dynamically.. ****/
65 FUNCTION get_sls_grps(p_table_name varchar2) RETURN VARCHAR2 IS
66 l_count NUMBER;
67 l_dummy VARCHAR2(2000);
68 BEGIN
69 SELECT COUNT(*) INTO l_count FROM igi_sls_allocations WHERE sls_allocation = p_table_name ;
70
71 FOR sec_grp_rec IN
72 (SELECT sls_group FROM igi_sls_allocations WHERE sls_allocation = p_table_name)
73 LOOP
74 IF l_count=1 THEN
75 l_dummy:= l_dummy || sec_grp_rec.sls_group;
76 RETURN l_dummy;
77 ELSIF l_count>1 THEN
78 l_dummy:= l_dummy || sec_grp_rec.sls_group;
79 l_dummy:= l_dummy || ', ';
80 l_count:=l_count-1;
81 ELSE /** This will never occur **/
82 return NULL;
83
84 END IF;
85 END LOOP;
86
87 EXCEPTION WHEN others THEN
88 RETURN NULL;
89 /**
90 This is an error condition..
91 **/
92
93 END get_sls_grps;
94
95
96 /********* Function to form the security groups list dynamically.. ****/
97 /********** Procedure to retrieve the stored security groups list *****/
98 PROCEDURE get_security_groups_list(param1 IN list_of_old_tables, param2 OUT NOCOPY list_of_secured_groups) IS
99 BEGIN
100 FOR indx IN param1.FIRST .. param1.LAST
101 LOOP
102
103 SELECT sls_groups INTO param2(indx) FROM igi_sls_upg_itf WHERE old_table_name=param1(indx);
104
105 END LOOP;
106
107
108 END get_security_groups_list;
109
110 /********** Procedure to retrieve the stored security groups list *****/
111
112
113 /******** This proc. popluates the remaining entries of the interface table ****/
114 PROCEDURE populate_temp_table_new(param1 IN list_of_old_tables, param2 IN list_of_new_tables, ret_code OUT NOCOPY NUMBER ) is
115 BEGIN
116 ret_code:=0;
117 set_sls_tables_data(param1, param2);
118 set_sls_allocations_data(param1, param2);
119
120
121 EXCEPTION WHEN OTHERS THEN
122 ret_code:=1;
123 write_to_log (l_excep_level, 'set_sls_tables_data',SQLCODE );
124 write_to_log (l_excep_level, 'set_sls_tables_data',SQLERRM );
125 write_to_log (l_excep_level, 'set_sls_tables_data','Error in insertion... Please note.. :)' );
126
127
128 END populate_temp_table_new;
129
130
131 /******** This proc. popluates the remaining entries of the interface table ****/
132 /*********** This procedure sets data in the table IGI_SLS_SECURE_TABLES***********/
133
134 PROCEDURE set_sls_tables_data(param1 IN list_of_old_tables,
135 param2 IN list_of_new_tables)
136 IS
137 l_owner VARCHAR2(5) ;
138 l_count NUMBER;
139 l_old_table_name VARCHAR2(50);
140 l_table_name VARCHAR2(50);
141 l_sls_table_name VARCHAR2(50) ;
142 l_optimise_sql VARCHAR2(1);
143 BEGIN
144
145 FOR indx IN param2.FIRST .. param2.LAST
146 LOOP
147 l_table_name:=param2(indx);
148 l_old_table_name := param1(indx);
149
150 /* To check if this entry is already there in the igi_sls_secure_tables table
151 So as to prevent duplicate entry*/
152 SELECT COUNT(*) INTO l_count FROM igi_sls_secure_tables WHERE table_name =l_table_name ;
153 write_to_log (l_excep_level, 'set_sls_tables_data','Count: '||l_count );
154
155
156 IF l_count=0 THEN
157
158 /** First select the owner of this table **/
159 SELECT owner INTO l_owner FROM all_objects WHERE object_name=UPPER(l_table_name)
160 AND object_type='TABLE' AND owner IN('AP', 'AR', 'PO', 'XLA','ICX','IBY');
161 write_to_log (l_excep_level, 'set_sls_tables_data','Owner: '||l_owner );
162
163
164 SELECT 'IGI_SLS_' || TO_CHAR(igi_sls_extended_table_s.nextval)
165 INTO l_sls_table_name FROM dual;
166
167 write_to_log (l_excep_level, 'set_sls_tables_data','l_sls_table_name: '||l_sls_table_name );
168
169
170 SELECT OPTIMISE_SQL INTO l_optimise_sql FROM igi_sls_secure_tables WHERE table_name =l_old_table_name;
171 write_to_log (l_excep_level, 'set_sls_tables_data','l_optimise_sql: '||l_optimise_sql );
172
173
174 write_to_log (l_excep_level, 'set_sls_tables_data','Before insert' );
175
176 /* insert statement... core to this procedure..*/
177 INSERT INTO igi_sls_secure_tables(owner, table_name, sls_table_name, update_allowed, creation_date, created_by,
178 last_update_login,last_update_date, last_updated_by, optimise_sql) VALUES(l_owner, l_table_name,l_sls_table_name, 'N', sysdate,1,1,sysdate,1, l_optimise_sql );
179
180 write_to_log (l_excep_level, 'set_sls_tables_data','After Insert..' ||SQL%ROWCOUNT ||'row inserted');
181
182 UPDATE igi_sls_upg_itf SET new_table_name=l_table_name,new_owner=l_owner, new_allocation=l_sls_table_name WHERE old_table_name = l_old_table_name;
183
184 END IF;
185 END LOOP;
186
187
188 END set_sls_tables_data;
189
190 /*********** This procedure sets data in the table IGI_SLS_SECURE_TABLES***********/
191
192
193
194
195 /***** Procedure to set data in IGI_SLS_ALLOCATIONS ***/
196 PROCEDURE set_sls_allocations_data( param1 IN list_of_old_tables, param2 IN list_of_new_tables) is
197 l_old_table_name VARCHAR2(50);
198 l_table_name VARCHAR2(50);
199 l_sls_group VARCHAR2(50);
200 l_list row_id_list;
201 l_rowid ROWID;
202 l_count NUMBER;
203 BEGIN
204
205 FOR indx IN param2.FIRST .. param2.LAST
206 LOOP
207 l_table_name:=param2(indx);
208 l_old_table_name := param1(indx);
209
210
211 /** This table has a index attached on 4 columns and no primary key.. hence using rowid to
212 uniquely picking up rows */
213
214 SELECT ROWID BULK COLLECT INTO l_list FROM igi_sls_allocations WHERE sls_allocation =l_old_table_name;
215
216 FOR indx1 IN l_list.FIRST .. l_list.LAST
217 LOOP
218 l_rowid:=l_list(indx1);
219 SELECT sls_group INTO l_sls_group FROM igi_sls_allocations WHERE ROWID=l_rowid;
220
221 /** Check if this entry is already there in the table **/
222 SELECT COUNT(*) INTO l_count FROM igi_sls_allocations WHERE sls_group=l_sls_group
223 AND sls_allocation = l_table_name;
224
225 /** Insert only if the entry is not present.. */
226 IF(l_count =0)THEN
227 INSERT INTO igi_sls_allocations (SELECT SLS_GROUP ,SLS_GROUP_TYPE ,l_table_name ,SLS_ALLOCATION_TYPE,
228 SYSDATE ,null,null,null,SYSDATE ,CREATED_BY,1,sysdate,1 FROM
229 igi_sls_allocations WHERE ROWID=l_rowid) ;
230
231 END IF;
232 END LOOP;
233
234 END LOOP;
235 END set_sls_allocations_data;
236
237 /***** Procedure to set data in IGI_SLS_ALLOCATIONS ***/
238
239
240
241 /********** Very imp. procedure... Final Steps that are to be done...******/
242 /**** Make a few changes needed in R12*************/
243 PROCEDURE disable_old_tables IS
244 BEGIN
245
246 update igi_sls_secure_tables set table_name='PO_VENDORS_OBS'
247 where table_name='PO_VENDORS';
248 update igi_sls_secure_tables set table_name='PO_VENDOR_CONTACTS_OBS'
249 where table_name='PO_VENDOR_CONTACTS';
250 update igi_sls_secure_tables set table_name='PO_VENDOR_SITES_OBS'
251 where table_name='PO_VENDOR_SITES_ALL';
252 update igi_sls_allocations set sls_allocation='PO_VENDORS_OBS'
253 where sls_allocation='PO_VENDORS';
254 update igi_sls_allocations set sls_allocation='PO_VENDOR_CONTACTS_OBS'
255 where sls_allocation='PO_VENDOR_CONTACTS';
256 update igi_sls_allocations set sls_allocation='PO_VENDOR_SITES_OBS'
257 where sls_allocation='PO_VENDOR_SITES_ALL';
258
259 delete from IGI_GCC_INST_OPTIONS_ALL where OPTION_NAME='SLS';
260
261 END disable_old_tables;
262 /********** Very imp. procedure... Disabling the security for the old tables...******/
263
264
265
266
267 /******** Set data useful in forming main query ********/
268 PROCEDURE set_query_data(param1 in list_of_old_tables , param2 in list_of_from, param3 in list_of_where) IS
269 BEGIN
270 FOR indx IN param1.FIRST .. param2.LAST
271 LOOP
272 UPDATE igi_sls_upg_itf SET from_clause=param2(indx) , where_clause=param3(indx)
273 WHERE old_table_name=param1(indx) AND param3(indx) IS NOT NULL;
274
275 write_to_log (l_excep_level, 'set_query_data',SQL%ROWCOUNT ||' rows updated' );
276
277 END LOOP;
278
279 END;
280
281 /******** Set data useful in forming main query ********/
282
283
284 /*********** Procedure that migrates data ************/
285 PROCEDURE migrate_data(param1 IN list_of_old_tables, param2 IN list_of_new_tables,
286 param3 IN list_of_from, param4 IN list_of_where) IS
287 l_sls_tab_old VARCHAR2(50);
288 l_sls_tab_new VARCHAR2(50);
289 l_old_table VARCHAR2(50);
290 l_new_table VARCHAR2(50);
291
292 l_query VARCHAR2(3500);
293 l_select VARCHAR2(3000);
294 l_from VARCHAR2(500);
295
296 BEGIN
297
298 FOR indx IN param1.FIRST .. param1.LAST
299 LOOP
300
301 SELECT old_allocation, new_allocation INTO l_sls_tab_old, l_sls_tab_new FROM igi_sls_upg_itf
302 WHERE old_table_name=param1(indx);
303
304 l_old_table := param1(indx);
305 l_new_table := param2(indx);
306
307
308 IF l_old_table = 'PO_VENDORS' THEN
309 l_old_table := 'PO_VENDORS_OBS';
310 ELSIF l_old_table = 'PO_VENDOR_CONTACTS' THEN
311 l_old_table := 'PO_VENDOR_CONTACTS_OBS';
312 ELSIF l_old_table = 'PO_VENDOR_SITES_ALL' THEN
313 l_old_table := 'PO_VENDOR_SITES_OBS';
314 END IF;
315
316
317
318 l_from:=param3(indx);
319
320 IF(l_from IS NULL) THEN
321 l_from:=l_old_table ||' a , '|| l_new_table || ' b , '||l_sls_tab_old ||' c ';
322 ELSE
323 l_from:= l_old_table ||' a , '|| l_new_table || ' b , '||l_sls_tab_old ||' c '||', '||l_from;
327 ' c.PREV_SLS_SEC_GRP , c.CHANGE_DATE FROM '|| l_from || ' WHERE a.ROWID=c.SLS_ROWID AND '|| param4(indx) ||
324 END IF;
325
326 l_select := ' SELECT DISTINCT b.ROWID , c.SLS_SEC_GRP , ' ||
328 ' AND NOT EXISTS (SELECT ''X''' || ' FROM ' || l_sls_tab_new ||' e '|| ' WHERE b.rowid = e.sls_rowid )';
329
330 write_to_log (l_excep_level, 'migrate_data','Select Statement Executed'||l_select );
331
332 l_query:= ' INSERT INTO '|| l_sls_tab_new ||'(' || l_select ||')';
333
334 write_to_log (l_excep_level, 'migrate_data','Query Executed'||l_query );
335
336
337
338 /*
339 INSERT INTO new_allocation( SELECT DISTINCT b.ROWID , c.sls_sec_group,
340 c.PREV_SLS_SEC_GRP , c.CHANGE_DATE FROM
341 param1(indx) a ,param2(indx) b , old_allocation c
342 WHERE a.ROWID=c.row_id) AND param3(indx));
343 */
344 EXECUTE IMMEDIATE l_query;
345 write_to_log (l_excep_level, 'migrate_data',SQL%ROWCOUNT ||' rows inserted' );
346
347 END LOOP;
348
349 EXCEPTION WHEN OTHERS THEN
350 write_to_log (l_excep_level, 'migrate_data',SQLCODE );
351 write_to_log (l_excep_level, 'migrate_data',SQLERRM );
352 write_to_log (l_excep_level, 'migrate_data','Error in insertion... Please note.. :)' );
353 END migrate_data;
354 /*********** Procedure that migrates data ************/
355
356
357
358 /*********** Procedure for providing concurrency ******/
359
360 PROCEDURE fnd_wait_for_request(req_id IN NUMBER, dev_status OUT NOCOPY VARCHAR2, dev_phase OUT NOCOPY VARCHAR2) IS
361 l_ret BOOLEAN;
362 l_reqid NUMBER;
363 l_phase VARCHAR2(500);
364 l_status VARCHAR2(500);
365 l_devphase VARCHAR2(500);
366 l_devstatus VARCHAR2(500);
367 l_message VARCHAR2(500);
368 l_count NUMBER;
369 BEGIN
370 l_reqid:=req_id;
371
372 l_ret := fnd_concurrent.wait_for_request(l_reqid,5,0,l_phase,l_status,l_devphase,l_devstatus,l_message);
373
374 IF l_ret THEN
375 write_to_log (l_excep_level, 'fnd_wait_for_request','l_ret: True' );
376 ELSE
377 write_to_log (l_excep_level, 'fnd_wait_for_request','l_ret: False' );
378 END IF;
379 l_count:=0;
380
381
382 LOOP
383
384 dev_status := l_devstatus;
385 dev_phase := l_devphase;
386
387 EXIT WHEN UPPER(dev_phase)='COMPLETE' AND UPPER(dev_status)='NORMAL' ;
388 l_count:=l_count +1;
389 END LOOP;
390
391 write_to_log (l_excep_level, 'fnd_wait_for_request','l_count: '||l_count );
392 write_to_log (l_excep_level, 'fnd_wait_for_request','l_req: '||l_reqid );
393 write_to_log (l_excep_level, 'fnd_wait_for_request','l_phase: ' ||l_phase );
394 write_to_log (l_excep_level, 'fnd_wait_for_request','l_status: '||l_status );
395 write_to_log (l_excep_level, 'fnd_wait_for_request','l_devphase: '||l_devphase );
396 write_to_log (l_excep_level, 'fnd_wait_for_request','l_devstatus: '||l_devstatus );
397 write_to_log (l_excep_level, 'fnd_wait_for_request','l_message: '||l_message );
398
399
400 END fnd_wait_for_request;
401
402
403 /*********** Procedure for providing concurrency ******/
404
405 END igi_sls_upg_pkg ;
406
[Home] [Help]