1 PACKAGE BODY AML_CATEGORY_MIGRATION AS
2 /* $Header: amlcateb.pls 120.0.12010000.1 2008/10/23 06:55:46 sariff noship $ */
3 -- This variable is used to store the application id for ASF
4 G_APPLICATION_ID NUMBER := 522;
5
6 -- Transaction date is committed in batches of this size
7
8 G_BATCH_SIZE CONSTANT NUMBER := 10000;
9 G_RET_STS_WARNING CONSTANT VARCHAR2(1) := 'W';
10 G_SCHEMA_NAME VARCHAR2(32) := null;
11 G_INDEX_SUFFIX CONSTANT VARCHAR2(4) := '_MT1';
12
13
14 /*----------------------------------------------------------------------------------------------------*
15 |
16 | PUBLIC ROUTINES
17 |
18 *----------------------------------------------------------------------------------------------------*/
19 PROCEDURE AML_DEBUG(msg IN VARCHAR2);
20
21
22 /*----------------------------------------------------------------------------------------------------*
23 | PUBLIC ROUTINE
24 | MIGRATE_LEAD_LINES
25 |
26 | PURPOSE
27 | Due to single product hierarchy architecture, lead line should move from
28 | using interest_type_id, primary_interest_code_id, secondary_interest_code_id
29 | to category_id, category_set_id.
30 |
31 | NOTES
32 |
33 | HISTORY
34 | 01/12/2004 SOLIN Created
35 |
36 | 05/24/2004 BMUTHUKR For bug# 3642822.Increased the width of desc and code variables.
37 |
38 | 06/22/2004 bmuthukr Modified the program to create temp indexes, disable triggers before
39 | migration. After the migration is done all these will be reverted.
40 | Also changed the update statements. Now udpate will be done in
41 | batches of 10000 records.
42 |
43 |
44 *----------------------------------------------------------------------------------------------------*/
45
46 PROCEDURE Load_Schema_Name IS
47 l_status VARCHAR2(2);
48 l_industry VARCHAR2(2);
49 l_oracle_schema VARCHAR2(32) := 'OSM';
50 l_schema_return BOOLEAN;
51 BEGIN
52 if (G_SCHEMA_NAME is null) then
53 l_schema_return := FND_INSTALLATION.get_app_info('AS', l_status, l_industry, l_oracle_schema);
54 G_SCHEMA_NAME := l_oracle_schema;
55 end if;
56 END;
57
58 PROCEDURE Enable_Triggers IS
59 BEGIN
60 --Enable sales lead line trigger..
61 execute immediate('alter trigger AS_SALES_LEAD_LINES_BIUD enable');
62 END;
63
64 PROCEDURE Disable_Triggers IS
65 BEGIN
66 --Disable sales lead line trigger..
67 execute immediate('alter trigger AS_SALES_LEAD_LINES_BIUD disable');
68 END;
69
70 PROCEDURE Create_temp_index(p_table IN VARCHAR2,
71 p_index_columns IN VARCHAR2) IS
72
73 l_check_tspace_exist varchar2(100);
74 l_index_tablespace varchar2(100);
75 l_sql_stmt varchar2(2000);
76 l_user varchar2(2000);
77 l_index_name varchar2(100);
78
79 begin
80 -- Temp index is created on as_sales_lead_lines table.
81 l_user := USER;
82
83 -- Name for temporary index created for migration
84 l_index_name := p_table || G_INDEX_SUFFIX;
85
86 AD_TSPACE_UTIL.get_tablespace_name('AS', 'TRANSACTION_INDEXES','N',l_check_tspace_exist,l_index_tablespace);
87
88 l_sql_stmt := 'create index ' || l_index_name || ' on '
89 || G_SCHEMA_NAME||'.'
90 || p_table || '(' || p_index_columns || ') '
91 ||' tablespace ' || l_index_tablespace || ' nologging '
92 ||'parallel 8';
93
94 execute immediate l_sql_stmt;
95
96 l_sql_stmt := 'alter index '|| l_user ||'.' || l_index_name || ' noparallel ';
97 execute immediate l_sql_stmt;
98
99 aml_debug('User is '||l_user);
100
101 -----------------
102 -- Gather Stats--
103 -----------------
104 dbms_stats.gather_index_stats(l_user,l_index_name,estimate_percent => 10);
105
106 END Create_temp_index;
107
108 PROCEDURE Drop_Temp_Index(p_table IN VARCHAR2) IS
109
110 l_sql_stmt varchar2(2000);
111 l_index_name varchar2(100);
112 l_user varchar2(2000);
113
114 begin
115 -----------------
116 -- Drop index --
117 -----------------
118 l_user := USER;
119
120 -- Name for temporary index created for migration
121 l_index_name := p_table || G_INDEX_SUFFIX;
122
123 l_sql_stmt := 'drop index ' || l_user||'.' || l_index_name || ' ';
124
125 execute immediate l_sql_stmt;
126 END Drop_Temp_Index;
127
128 PROCEDURE Display_category_mappings IS
129 l_no_data_found BOOLEAN := TRUE;
130
131 --- Cursor to check if all the interest types/codes are mapped.
132 CURSOR c_interest_codes IS
133 SELECT to_char(interest_type_id) code, description meaning
134 FROM as_interest_types_vl
135 WHERE product_category_id IS NULL
136 UNION
137 SELECT to_char(i.interest_type_id)||'/'||p.interest_code_id code,
138 i.description||'/'||p.description meaning
139 FROM as_interest_types_vl i, as_interest_codes_vl p
140 WHERE i.interest_type_id*1 = p.interest_type_id
141 AND p.parent_interest_code_id is null
142 AND p.product_category_id IS NULL
143 UNION
144 SELECT to_char(i.interest_type_id)||'/'||p.interest_code_id||'/'||
145 s.interest_code_id code, i.description||'/'||p.description||'/'||s.description meaning
146 FROM as_interest_types_vl i, as_interest_codes_vl p, as_interest_codes_vl s
147 WHERE i.interest_type_id = p.interest_type_id
148 AND p.interest_type_id = s.interest_type_id*1
149 AND s.parent_interest_code_id = p.interest_code_id
150 AND s.product_category_id IS NULL;
151
152 CURSOR c_lead_line_values IS
153 SELECT distinct to_char(interest.interest_type_id) code ,interest.description meaning
154 FROM as_sales_lead_lines line, as_interest_types_vl interest
155 WHERE line.interest_type_id = interest.interest_type_id
156 AND line.primary_interest_code_id is null
157 AND line.secondary_interest_code_id is null
158 AND interest.product_category_id is null
159 UNION
160 SELECT distinct to_char(interest.interest_type_id)||'/'||pic.interest_code_id code,
161 interest.description||'/'||pic.description meaning
162 FROM as_sales_lead_lines line, as_interest_codes_vl pic, as_interest_types_vl interest
163 WHERE line.primary_interest_code_id = pic.interest_code_id
164 AND pic.interest_type_id = interest.interest_type_id
165 AND pic.parent_interest_code_id is null
166 AND line.secondary_interest_code_id is null
167 AND pic.product_category_id is null
168 UNION
169 SELECT distinct to_char(interest.interest_type_id)||'/'||pic.interest_code_id||'/'||sic.interest_code_id code,
170 interest.description||'/'||pic.description||'/'||sic.description meaning
171 FROM as_sales_lead_lines line, as_interest_codes_vl sic, as_interest_codes_vl pic, as_interest_types_vl interest
172 WHERE line.secondary_interest_code_id = sic.interest_code_id
173 AND line.primary_interest_code_id = sic.parent_interest_code_id
174 AND sic.interest_type_id = interest.interest_type_id
175 and sic.parent_interest_code_id = pic.interest_code_id
176 and pic.product_category_id is null;
177
178 CURSOR c_sales_lead_line_int IS
179 SELECT to_char(lead.interest_type_id) code
180 FROM as_sales_lead_lines lead
181 WHERE lead.interest_type_id not in (SELECT int.interest_type_id
182 FROM as_interest_types_b int)
183 UNION
184 SELECT lead.interest_type_id||'/'||lead.primary_interest_code_id code
185 FROM as_sales_lead_lines lead
186 WHERE lead.primary_interest_code_id not in (SELECT pic.interest_code_id
187 FROM as_interest_codes_b pic
188 WHERE pic.parent_interest_code_id IS null)
189 UNION
190 SELECT lead.interest_type_id||'/'||lead.primary_interest_code_id||'/'||lead.secondary_interest_code_id code
191 FROM as_sales_lead_lines lead
192 WHERE lead.secondary_interest_code_id not in (SELECT sic.interest_code_id
193 FROM as_interest_codes_b sic
194 WHERE sic.parent_interest_code_id is not null) ;
195
196 --
197
198 Begin
199
200 -- -----------------------------------------------------------------------
201 -- Check interest_code_id
202 -- -----------------------------------------------------------------------
203 AML_DEBUG('(1). The following interest code combinations are not mapped...');
204 AML_DEBUG('-');
205
206 l_no_data_found := TRUE;
207
208 AML_DEBUG(' Code Meaning');
209 AML_DEBUG('-------------------------------------------------------------------------------------');
210
211 FOR x IN c_interest_codes LOOP
212 -- AML_DEBUG(RPAD(x.code, 11));
213 AML_DEBUG(RPAD(x.code, 30) || ' ' || RPAD(x.meaning, 100));
214
215 l_no_data_found := FALSE;
216 END LOOP;
217
218 IF (l_no_data_found) THEN
219 AML_DEBUG('No rows returned.');
220 AML_DEBUG('-');
221
222 END IF;
223
224 -- -----------------------------------------------------------------------
225 -- Check if all the data in as_sales_lead_lines have proper mapping.
226 -- -----------------------------------------------------------------------
227 l_no_data_found := TRUE;
228
229 AML_DEBUG('-');
230 AML_DEBUG('-');
231 AML_DEBUG('==============================================================');
232 AML_DEBUG('Checking data mapping in as_sales_lead_lines table...');
233 AML_DEBUG('==============================================================');
234 AML_DEBUG('-');
235 AML_DEBUG('-');
236 AML_DEBUG('(2).');
237 AML_DEBUG('The following data in as_sales_lead_lines do not have a mapping');
238 AML_DEBUG('to Single Product Hierarchy.');
239 AML_DEBUG('-');
240 AML_DEBUG(' Code Meaning');
241 AML_DEBUG('-------------------------------------------------------------------------------------');
242
243 FOR x IN c_lead_line_values LOOP
244 AML_DEBUG(RPAD(x.code, 30) || ' ' || RPAD(x.meaning, 100));
245 l_no_data_found := FALSE;
246 END LOOP;
247
248
249 IF (l_no_data_found) THEN
250 AML_DEBUG('-');
251 AML_DEBUG('-');
252 AML_DEBUG('No rows returned.');
253 AML_DEBUG('-');
254 END IF;
255
256
257 AML_DEBUG('==============================================================');
258 AML_DEBUG('Checking Stale/Invalid Interest Typed/Codes in as_sales_lead_lines table...');
259 AML_DEBUG('==============================================================');
260 AML_DEBUG('-');
261 AML_DEBUG('-');
262 AML_DEBUG('(3).');
263 AML_DEBUG('The following data in the Sales Lead Lines donot have ');
264 AML_DEBUG('enabled Interest Types/ Interest Codes.');
265 AML_DEBUG('-');
266 AML_DEBUG(' Code ');
267 AML_DEBUG('---------------------------------------------------');
268
269 FOR x IN c_sales_lead_line_int LOOP
270 AML_DEBUG(RPAD(x.code, 30));
271 l_no_data_found := FALSE;
272 END LOOP;
273
274 IF (l_no_data_found) THEN
275 AML_DEBUG('-');
276 AML_DEBUG('-');
277 AML_DEBUG('No rows returned.');
278 AML_DEBUG('-');
279 END IF;
280
281 End Display_category_mappings;
282
283 PROCEDURE MIGRATE_LEAD_LINES (
284 ERRBUF OUT NOCOPY VARCHAR2,
285 RETCODE OUT NOCOPY VARCHAR2,
286 p_trace_mode IN VARCHAR2) IS
287 l_status BOOLEAN;
288 l_no_data_found BOOLEAN := TRUE;
289
290 l_min_id NUMBER := 0;
291 l_max_id NUMBER := 0;
292 l_sales_lead_lines_biud BOOLEAN := FALSE;
293 l_trigger VARCHAR2(200) := NULL;
294 l_count NUMBER := 0;
295
296 CURSOR get_min_id IS
297 SELECT min(sales_lead_line_id)
298 FROM as_sales_lead_lines;
299
300 CURSOR get_max_id IS
301 SELECT max(sales_lead_line_id)
302 FROM as_sales_lead_lines;
303
304 CURSOR get_next_val IS
305 SELECT as_sales_lead_lines_s.nextval
306 FROM dual;
307
308 CURSOR Get_Disabled_Triggers(c_schema_name VARCHAR2) IS
309 SELECT trigger_name
310 FROM all_triggers
311 WHERE table_owner = c_schema_name
312 AND trigger_name = 'AS_SALES_LEAD_LINES_BIUD'
313 AND nvl(status,'DISABLED') = 'ENABLED';
314
315
316 BEGIN
317 IF p_trace_mode = 'Y' THEN
318 dbms_session.set_sql_trace(TRUE);
319 ELSE
320 dbms_session.set_sql_trace(FALSE);
321 END IF;
322
323 -- Schema name is loaded..
324 Load_Schema_Name;
325
326 -- First find out the existing state of the triggers
327 OPEN Get_Disabled_Triggers(G_SCHEMA_NAME);
328 FETCH Get_Disabled_Triggers INTO l_trigger;
329 IF Get_Disabled_Triggers%FOUND THEN
330 l_sales_lead_lines_biud := true;
331 END IF;
332 CLOSE Get_Disabled_Triggers;
333
334 -- Disable the sales_lead_line_biud trigger, if that is in enabled status..
335 IF l_sales_lead_lines_biud THEN
336 Disable_Triggers;
337 END IF;
338
339 --Create temp index..
340 Create_Temp_Index('AS_SALES_LEAD_LINES','SALES_LEAD_LINE_ID,INTEREST_TYPE_ID,PRIMARY_INTEREST_CODE_ID,SECONDARY_INTEREST_CODE_ID');
341
342 --Get the min sales_lead_line id..
343 OPEN Get_Min_Id;
344 FETCH Get_Min_Id into l_min_id;
345 CLOSE Get_Min_Id;
346
347 --Get the next val for sales_lead_line from the seq..
348 OPEN Get_Next_Val;
349 FETCH Get_Next_Val into l_max_id;
350 CLOSE Get_Next_Val;
351
352
353
354 --Display the mappings..
355 Display_category_mappings;
356
357 -- Migration starts here..
358 --Migration logic changed. Now we have 3 statements..
359
360 -- Initialize counter
361 l_count := l_min_id;
362
363 WHILE (l_count <= l_max_id)
364 LOOP
365 -- Update interest type
366 update as_sales_lead_lines l
367 set (category_id, category_set_id) =
368 (select int.product_category_id, int.product_cat_set_id
369 from as_interest_types_b int
370 where l.interest_type_id = int.interest_type_id)
371 where l.sales_lead_line_id >= l_count
372 and l.sales_lead_line_id < l_count+G_BATCH_SIZE
373 and l.interest_type_id is not null
374 and l.primary_interest_code_id is null
375 and l.secondary_interest_code_id is null;
376
377 -- Update primary interest code
378 update as_sales_lead_lines l
379 set (category_id, category_set_id) =
380 (select int.product_category_id, int.product_cat_set_id
381 from as_interest_codes_b int
382 where l.primary_interest_code_id = int.interest_code_id)
383 where l.sales_lead_line_id >= l_count
384 and l.sales_lead_line_id < l_count+G_BATCH_SIZE
385 and l.primary_interest_code_id is not null
386 and l.secondary_interest_code_id is null;
387
388 -- Update secondary interest code
389 update as_sales_lead_lines l
390 set (category_id, category_set_id) =
391 (select int.product_category_id, int.product_cat_set_id
392 from as_interest_codes_b int
393 where l.secondary_interest_code_id = int.interest_code_id)
394 where l.sales_lead_line_id >= l_count
398 -- commit after each batch
395 and l.sales_lead_line_id < l_count+G_BATCH_SIZE
396 and l.secondary_interest_code_id is not null;
397
399 commit;
400
401 l_count := l_count + G_BATCH_SIZE;
402 END LOOP;
403 COMMIT;
404
405 -- Drop temporary index
406 Drop_Temp_Index('AS_SALES_LEAD_LINES');
407
408 -- Enable All the triggers
409 If l_sales_lead_lines_biud then
410 Enable_Triggers;
411 End if;
412
413 EXCEPTION
414 WHEN FND_API.G_EXC_ERROR THEN
415 AML_DEBUG('Expected error');
416
417 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
418 AML_DEBUG('Unexpected error');
419
420 WHEN others THEN
421 AML_DEBUG('Exception: others in MIGRATE_LEAD_LINES');
422 AML_DEBUG('SQLCODE ' || to_char(SQLCODE) ||
423 ' SQLERRM ' || substr(SQLERRM, 1, 100));
424
425 errbuf := SQLERRM;
426 retcode := FND_API.G_RET_STS_UNEXP_ERROR;
427 --Triggers should be enabled and temp index should be dropped even
428 --in the case of exception..
429 If l_sales_lead_lines_biud then
430 Enable_Triggers;
431 End if;
432
433 Drop_Temp_Index('AS_SALES_LEAD_LINES');
434 --
435 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
436 end MIGRATE_LEAD_LINES;
437
438 /*-------------------------------------------------------------------------*
439 | PRIVATE ROUTINE
440 | AML_AML_DEBUG
441 |
442 | PURPOSE
443 | Write debug message
444 |
445 | NOTES
446 |
447 |
448 | HISTORY
449 | 01/12/2004 SOLIN Created
450 *-------------------------------------------------------------------------*/
451
452
453 PROCEDURE AML_DEBUG(msg IN VARCHAR2)
454 IS
455 l_length NUMBER;
456 l_start NUMBER := 1;
460 BEGIN
457 l_substring VARCHAR2(255);
458
459 l_base VARCHAR2(12);
461 -- IF g_debug_flag = 'Y'
462 -- THEN
463 -- chop the message to 255 long
464 l_length := length(msg);
465 WHILE l_length > 255 LOOP
466 l_substring := substr(msg, l_start, 255);
467 FND_FILE.PUT_LINE(FND_FILE.LOG, l_substring);
468 -- dbms_output.put_line(l_substring);
469
470 l_start := l_start + 255;
471 l_length := l_length - 255;
472 END LOOP;
473
474 l_substring := substr(msg, l_start);
475 FND_FILE.PUT_LINE(FND_FILE.LOG,l_substring);
476 --dbms_output.put_line(l_substring);
477 -- END IF;
478 EXCEPTION
479 WHEN others THEN
480 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception: others in AML_DEBUG');
481 FND_FILE.PUT_LINE(FND_FILE.LOG,
482 'SQLCODE ' || to_char(SQLCODE) ||
483 ' SQLERRM ' || substr(SQLERRM, 1, 100));
484 END AML_DEBUG;
485
486 end AML_CATEGORY_MIGRATION;
[Home] [Help]