1 PACKAGE BODY AS_INT_TYP_COD_MIGRATION as
2 /* $Header: asxmintb.pls 120.3 2005/12/22 22:53:31 subabu noship $ */
3
4 --*****************************************************************************
5 -- GLOBAL VARIABLES AND CONSTANTS
6 --
7
8 -- This variable is used to store the application id for ASF
9 G_APPLICATION_ID NUMBER := 522;
10
11 -- Transaction date is committed in batches of this size
12 G_BATCH_SIZE CONSTANT NUMBER := 10000;
13
14 G_RET_STS_WARNING CONSTANT VARCHAR2(1) := 'W';
15
16 G_SCHEMA_NAME VARCHAR2(32) := null;
17
18 G_INDEX_SUFFIX CONSTANT VARCHAR2(4) := '_MT1';
19
20 --*****************************************************************************
21 -- Declarations
22 --
23 PROCEDURE Process_Perz_Query_Params (
24 p_query_id IN NUMBER,
25 p_query_name IN VARCHAR2,
26 p_debug IN BOOLEAN,
27 x_ret_sts_warning OUT NOCOPY VARCHAR2
28 );
29 PROCEDURE Enable_Triggers(p_lead_lines_biud IN BOOLEAN,
30 p_lead_lines_after_biud IN BOOLEAN,
31 p_sales_credits_biud IN BOOLEAN,
32 p_sales_credits_after_biud IN BOOLEAN);
33 PROCEDURE Disable_Triggers;
34 PROCEDURE Load_Schema_Name;
35 PROCEDURE Create_Temp_Index(p_table IN VARCHAR2,
36 p_index_columns IN VARCHAR2,
37 p_debug IN BOOLEAN);
38 PROCEDURE Drop_Temp_Index(p_table IN VARCHAR2,
39 p_debug IN BOOLEAN);
40
41 --*****************************************************************************
42 -- Public API
43 --
44
45 /*
46 This procedure migrates all the perz data and transaction data
47 */
48 PROCEDURE Migrate_All(
49 ERRBUF OUT NOCOPY VARCHAR2,
50 RETCODE OUT NOCOPY VARCHAR2,
51 p_Debug_Flag IN VARCHAR2 Default 'N'
52 ) IS
53
54 l_status BOOLEAN;
55 l_warning VARCHAR2(1) := 'N';
56 l_debug BOOLEAN := false;
57 BEGIN
58 if (upper(p_Debug_Flag) = 'Y') then
59 l_debug := true;
60 end if;
61
62 FND_FILE.PUT_LINE(FND_FILE.LOG,'Migration started');
63 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
64
65 Migrate_Perz_Data(ERRBUF,
66 RETCODE,
67 p_Debug_Flag);
68 if (RETCODE = G_RET_STS_WARNING) then
69 l_warning := 'Y';
70 end if;
71
72 Migrate_AS_LEAD_LINES_ALL(ERRBUF,
73 RETCODE,
74 p_Debug_Flag);
75 Migrate_FST_SALES_CATEGORIES(ERRBUF,
76 RETCODE,
77 p_Debug_Flag);
78 Migrate_AS_LEAD_LINES_LOG(ERRBUF,
79 RETCODE,
80 p_Debug_Flag);
81 Migrate_AS_INTERESTS_ALL(ERRBUF,
82 RETCODE,
83 p_Debug_Flag);
84 Migrate_AS_SALES_C_DENORM(ERRBUF,
85 RETCODE,
86 p_Debug_Flag);
87 Migrate_AS_PRODWKS_LINES(ERRBUF,
88 RETCODE,
89 p_Debug_Flag);
90 Migrate_AS_PE_INT_CATEGORIES(ERRBUF,
91 RETCODE,
92 p_Debug_Flag);
93
94 FND_FILE.PUT_LINE(FND_FILE.LOG,'Migration finished successfully');
95 FND_FILE.PUT_LINE(FND_FILE.LOG,'End time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
96
97 if (l_warning = 'Y') then
98 l_status := fnd_concurrent.set_completion_status('WARNING',FND_MESSAGE.Get_String('AS','API_REQUEST_WARNING_STATUS'));
99 end if;
100 EXCEPTION
101 WHEN OTHERS THEN
102 Rollback;
103 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in migration '||SQLERRM);
104 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
105 IF l_status = TRUE THEN
106 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error, cannot complete Concurrent Program') ;
107 END IF ;
108 END Migrate_All;
109
110 /*
111 This is called by concurrent program 'Product Catalog Migration for Perz Data'
112
113 This procedure migrates all the perz data
114 If parameter_name is 'productCategory' or 'prodCat', the parameter name is converted
115 to the format rep.ROW0.ProdCategory and the corresponding parameter_value is converted
116 to category id (mapped to the interest type id stored previously).
117
118 Here is an example:
119 old scheme:
120 parameter name parameter value
121 -------------- ---------------
122 prodCat 207
123 prodCat 208
124
125
126 new scheme:
127 parameter name parameter value
128 -------------- ---------------
129 rep.ROW0.ProdCategory xxxxxx (where xxxxxx is the new category_id corresponding to interest type 207)
130 rep.ROW1.ProdCategory yyyyyy (where yyyyyy is the new category_id corresponding to interest type 208)
131
132 For 'Opportunity by Products Report', the perz data is stored differently
133 old scheme:
134 parameter name parameter value
135 -------------- ---------------
136 ROW0ProductCategory 207/434/435
137 ROW1ProductCategory 208
138 ROW2ProductCategory 208/460
139 ROW0.Invt CM18761
140 invItemID0 253
141
142
143 new scheme:
144 parameter name parameter value
145 -------------- ---------------
146 rep.ROW0.ProdCategory xxxxxx (where xxxxxx is the new category id corresponding to interest code 435)
147 rep.ROW1.ProdCategory yyyyyy (where yyyyyy is the new category id corresponding to interest type 208)
148 rep.ROW2.ProdCategory zzzzzz (where zzzzzz is the new category id corresponding to interest code 460)
149 rep.ROW0.invItemID 253
150 rep.ROW0.invItem CM18761
151 */
152 PROCEDURE Migrate_Perz_Data (
153 ERRBUF OUT NOCOPY VARCHAR2,
154 RETCODE OUT NOCOPY VARCHAR2,
155 p_Debug_Flag IN VARCHAR2 Default 'N'
156 ) IS
157
158 CURSOR C_Get_Query_Ids(c_application_id NUMBER) IS
159 select distinct Q.query_id, Q.query_name
160 from JTF_PERZ_QUERY_PARAM P, JTF_PERZ_QUERY Q
161 where Q.Query_Id = P.Query_Id
162 and Q.Application_Id = c_application_id
163 and P.Parameter_Type = 'condition'
164 and (P.Parameter_Name = 'productCategory' OR -- As in Group Summary Report or Advanced Search
165 P.Parameter_Name = 'prodCat' OR -- As in Summary Report or detail Report or other reports
166 P.Parameter_Name like 'ROW%ProductCategory' OR
167 P.Parameter_Name like 'ROW%Invt' OR
168 P.Parameter_Name like 'invItemID%')
169 order by Q.query_id;
170
171 l_ret_sts_warning VARCHAR2(1) := 'N';
172 l_status BOOLEAN;
173 l_debug BOOLEAN := false;
174
175 BEGIN
176 if (upper(p_Debug_Flag) = 'Y') then
177 l_debug := true;
178 end if;
179
180 If l_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
181 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Perz data migration started');
182 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
183 end if;
184
185 -- Initialize retcode to success
186 RETCODE := FND_API.G_RET_STS_SUCCESS;
187
188 for scr in C_Get_Query_Ids(G_APPLICATION_ID)
189 loop
190 Process_Perz_Query_Params(scr.query_id, scr.query_name,l_debug,l_ret_sts_warning);
191 if (l_ret_sts_warning = 'Y') then
192 RETCODE := G_RET_STS_WARNING;
193 end if;
194 end loop;
195
196 If l_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
197 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Perz data migration finished succesfully');
198 end if;
199
200 commit;
201
202 If l_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
203 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Perz data migration finished successfully');
204 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','End time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
205 end if;
206
207 if (RETCODE = G_RET_STS_WARNING) then
208 l_status := fnd_concurrent.set_completion_status('WARNING',FND_MESSAGE.Get_String('AS','API_REQUEST_WARNING_STATUS'));
209 end if;
210 EXCEPTION
211 WHEN OTHERS THEN
212 Rollback;
213 RETCODE := FND_API.G_RET_STS_ERROR;
214 ERRBUF := 'Error in perz data migration '||SQLERRM;
215 FND_FILE.PUT_LINE(FND_FILE.LOG,ERRBUF);
216 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
217 IF l_status = TRUE THEN
218 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error, cannot complete Concurrent Program') ;
219 END IF ;
220 END Migrate_Perz_Data;
221
222 PROCEDURE Process_Perz_Query_Params (
223 p_query_id IN NUMBER,
224 p_query_name IN VARCHAR2,
225 p_debug IN BOOLEAN,
226 x_ret_sts_warning OUT NOCOPY VARCHAR2
227 ) IS
228
229 l_cnt NUMBER;
230 l_cnt2 NUMBER;
231 l_index NUMBER;
232 l_index1 NUMBER;
233 l_index2 NUMBER;
234
235 l_product_cat_id NUMBER;
236 l_product_cat_set_id NUMBER;
237 l_old_query_id NUMBER;
238 l_warning VARCHAR2(1) := 'N';
239
240 l_int_type_id NUMBER;
241 l_pri_int_code_id NUMBER;
242 l_sec_int_code_id NUMBER;
243 l_debug BOOLEAN := false;
244
245 CURSOR C_Get_Query_Params(c_query_id NUMBER) IS
246 select P.query_param_id, P.parameter_name, P.parameter_type, P.parameter_value, P.parameter_condition, P.parameter_sequence, P.created_by, P.last_update_date, P.last_updated_by, P.last_update_login, P.security_group_id
247 from JTF_PERZ_QUERY_PARAM P
248 where P.query_id = c_query_id
249 and P.Parameter_Type = 'condition'
250 and (P.Parameter_Name = 'productCategory' OR -- As in Group Summary Report or Advanced Search
251 P.Parameter_Name = 'prodCat' OR -- As in Summary Report or detail Report or other reports
252 P.Parameter_Name like 'ROW%ProductCategory' OR
253 P.Parameter_Name like 'ROW%Invt' OR
254 P.Parameter_Name like 'invItemID%')
255 order by P.query_param_id;
256
257 CURSOR C_Prod_Cat_Desc(c_product_category_id NUMBER, c_product_cat_set_id NUMBER) IS
258 select concat_cat_parentage
259 from eni_prod_den_hrchy_parents_v
260 where category_id = c_product_category_id
261 and category_set_id = c_product_cat_set_id
262 and language = userenv('lang');
263
264 CURSOR C_Get_Product_Cat_Id(c_interest_type_id NUMBER) IS
265 select product_category_id, product_cat_set_id
266 from AS_INTEREST_TYPES_B
267 where interest_type_id = c_interest_type_id;
268
269 CURSOR C_Get_Product_Cat_Id2(c_interest_code_id NUMBER) IS
270 select product_category_id, product_cat_set_id
271 from AS_INTEREST_CODES_B
272 where interest_code_id = c_interest_code_id;
273
274 BEGIN
275
276 SAVEPOINT Process_Perz_Query_Params;
277
278 l_debug := p_debug;
279
280 If l_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
281 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Processing query ' || p_query_name);
282 end if;
283
284 x_ret_sts_warning := 'N';
285
286 -- Initialize count
287 l_cnt := -1;
288
289 for scr in C_Get_Query_Params(p_query_id)
290 loop
291 l_cnt := l_cnt + 1;
292
293 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) Then
294 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Query details are query_id='||p_query_id);
295 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','query_param_id:'||scr.query_param_id);
296 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','parameter_name:'||scr.parameter_name);
297 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','parameter_type:'||scr.parameter_type);
298 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','parameter_value:'||scr.parameter_value);
299 END IF;
300
301 if (scr.parameter_type = 'condition') then
302 if (scr.parameter_name = 'productCategory' or scr.parameter_name = 'prodCat') then
303 l_product_cat_id := NULL;
304 l_product_cat_set_id := NULL;
305 -- Get product category id corresponding to the interest type id
306 open C_Get_Product_Cat_Id(scr.parameter_value);
307 Fetch C_Get_Product_Cat_Id into l_product_cat_id, l_product_cat_set_id;
308 if C_Get_Product_Cat_Id%NOTFOUND THEN
309 close C_Get_Product_Cat_Id;
310
311 -- 'Warning! Ignoring query ' || p_query_name || '(Found Invalid interest type id ' || scr.parameter_value || ')'
312 FND_MESSAGE.Set_Name('AS', 'API_MIGRATION_WARNING1');
313 FND_MESSAGE.Set_Token('NAME', p_query_name);
314 FND_MESSAGE.Set_Token('ID', scr.parameter_value);
315 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get());
316
317 l_warning := 'Y';
318 exit;
322 if ((l_product_cat_id IS NULL) OR (l_product_cat_set_id IS NULL)) then
319 end if;
320
321 close C_Get_Product_Cat_Id;
323
324 -- 'Warning! Ignoring query ' || p_query_name || '(Interest type with id ' || scr.parameter_value || ' not mapped to any product category)'
325 FND_MESSAGE.Set_Name('AS', 'API_MIGRATION_WARNING2');
326 FND_MESSAGE.Set_Token('NAME', p_query_name);
327 FND_MESSAGE.Set_Token('ID', scr.parameter_value);
328 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get());
329
330 l_warning := 'Y';
331 exit;
332 end if;
333
334 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) Then
335 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','product category id=' || l_product_cat_id || ', category set id='||l_product_cat_set_id);
336 END IF;
337
338 -- Set product category id in the JTF_PERZ_QUERY_PARAM table
339 Update JTF_PERZ_QUERY_PARAM P
340 set parameter_name = 'rep.ROW' || l_cnt || '.ProdCategory',
341 parameter_value = l_product_cat_id
342 where
343 P.query_param_id = scr.query_param_id;
344 else
345 Begin
346
347 if (instr(scr.parameter_name, 'ProductCategory') > 0) then
348 -- Example: instr('Row10ProductCategory','ProductCategory') = 6
349 -- substr('Row10ProductCategory',4,2) = 10
350 l_index := instr(scr.parameter_name, 'ProductCategory');
351 l_cnt2 := substr(scr.parameter_name, 4, (l_index - 4));
352
353 -- Now find index of first slash (if any)
354 l_index1 := instr(scr.parameter_value, '/');
355 -- Find index of second slash (if any)
356 l_index2 := instr(scr.parameter_value, '/', l_index1+1);
357
358 if (l_index1 > 0) then
359 if (l_index2 > 0) then
360 l_int_type_id := substr(scr.parameter_value, 1, l_index1-1);
361 l_pri_int_code_id := substr(scr.parameter_value, l_index1+1, l_index2-l_index1-1);
362 l_sec_int_code_id := substr(scr.parameter_value, l_index2+1);
363
364 Open C_Get_Product_Cat_Id2(l_sec_int_code_id);
365 Fetch C_Get_Product_Cat_Id2 into l_product_cat_id, l_product_cat_set_id;
366 else
367 l_int_type_id := substr(scr.parameter_value, 1, l_index1-1);
368 l_pri_int_code_id := substr(scr.parameter_value, l_index1+1);
369
373
370 Open C_Get_Product_Cat_Id2(l_pri_int_code_id);
371 Fetch C_Get_Product_Cat_Id2 into l_product_cat_id, l_product_cat_set_id;
372 end if;
374 if C_Get_Product_Cat_Id2%NOTFOUND THEN
375 close C_Get_Product_Cat_Id2;
376
377 -- 'Warning! Ignoring query ' || p_query_name || '(Found Invalid interest code id ' || l_sec_int_code_id || ')'
378 FND_MESSAGE.Set_Name('AS', 'API_MIGRATION_WARNING4');
379 FND_MESSAGE.Set_Token('NAME', p_query_name);
380 FND_MESSAGE.Set_Token('ID', l_sec_int_code_id);
381 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get());
382
383 l_warning := 'Y';
384 exit;
385 end if;
386
387 close C_Get_Product_Cat_Id2;
388 if ((l_product_cat_id IS NULL) OR (l_product_cat_set_id IS NULL)) then
389
390 -- 'Warning! Ignoring query ' || p_query_name || '(Interest code with id ' || l_sec_int_code_id || ' not mapped to any product category)'
391 FND_MESSAGE.Set_Name('AS', 'API_MIGRATION_WARNING5');
392 FND_MESSAGE.Set_Token('NAME', p_query_name);
393 FND_MESSAGE.Set_Token('ID', l_sec_int_code_id);
394 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get());
395
396 l_warning := 'Y';
397 exit;
398 end if;
399 else
400 Open C_Get_Product_Cat_Id(scr.parameter_value);
401 Fetch C_Get_Product_Cat_Id into l_product_cat_id, l_product_cat_set_id;
402
403 if C_Get_Product_Cat_Id%NOTFOUND THEN
404 close C_Get_Product_Cat_Id;
405
406 -- 'Warning! Ignoring query ' || p_query_name || '(Found Invalid interest type id ' || scr.parameter_value || ')'
407 FND_MESSAGE.Set_Name('AS', 'API_MIGRATION_WARNING1');
408 FND_MESSAGE.Set_Token('NAME', p_query_name);
409 FND_MESSAGE.Set_Token('ID', scr.parameter_value);
410 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get());
411
412 l_warning := 'Y';
413 exit;
414 end if;
415
416 close C_Get_Product_Cat_Id;
417 if ((l_product_cat_id IS NULL) OR (l_product_cat_set_id IS NULL)) then
418
419 -- 'Warning! Ignoring query ' || p_query_name || '(Interest type with id ' || scr.parameter_value || ' not mapped to any product category)'
420 FND_MESSAGE.Set_Name('AS', 'API_MIGRATION_WARNING2');
421 FND_MESSAGE.Set_Token('NAME', p_query_name);
422 FND_MESSAGE.Set_Token('ID', scr.parameter_value);
423 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.Get());
424
425 l_warning := 'Y';
426 exit;
427 end if;
428 end if;
429
430 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) Then
431 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','product category id=' || l_product_cat_id || ', category set id='||l_product_cat_set_id);
432 END IF;
433
434 -- Set product category id in the JTF_PERZ_QUERY_PARAM table
435 Update JTF_PERZ_QUERY_PARAM P
436 set parameter_name = 'rep.ROW' || l_cnt2 || '.ProdCategory',
437 parameter_value = l_product_cat_id
438 where
439 P.query_param_id = scr.query_param_id;
440 elsif (instr(scr.parameter_name, '.Invt') > 0) then
441 l_index := instr(scr.parameter_name, '.Invt');
442 l_cnt2 := substr(scr.parameter_name, 4, (l_index - 4));
443
444 -- Set name as rep.ROW0.invItem
445 Update JTF_PERZ_QUERY_PARAM P
446 set parameter_name = 'rep.ROW' || l_cnt2 || '.invItem'
447 where
448 P.query_param_id = scr.query_param_id;
449 elsif (instr(scr.parameter_name, 'invItemID') > 0) then
450 l_index := length('invItemID');
451 l_cnt2 := substr(scr.parameter_name, l_index+1);
452
453 -- Set name as rep.ROW0.invItemID
454 Update JTF_PERZ_QUERY_PARAM P
455 set parameter_name = 'rep.ROW' || l_cnt2 || '.invItemID'
456 where
457 P.query_param_id = scr.query_param_id;
458 end if;
459
460 End;
461 end if;
462 end if; -- end if (scr.parameter_type = 'condition')
463
464 end loop;
468 x_ret_sts_warning := 'Y';
465
466 if (l_warning = 'Y') then
467 ROLLBACK TO Process_Perz_Query_Params;
469 end if;
470
471 EXCEPTION
472 WHEN OTHERS THEN
473 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured while processing query_id='||p_query_id);
474 RAISE;
475 END Process_Perz_Query_Params;
476
477 /*
478 Migrate product_category_id and product_cat_set_id into AS_LEAD_LINES_ALL table
479 */
480 PROCEDURE Migrate_AS_LEAD_LINES_ALL(
481 ERRBUF OUT NOCOPY VARCHAR2,
482 RETCODE OUT NOCOPY VARCHAR2,
483 p_Debug_Flag IN VARCHAR2 Default 'N'
484 ) IS
485 l_count NUMBER := 0;
486 l_min_id NUMBER := 0;
487 l_max_id NUMBER := 0;
488 l_status BOOLEAN;
489 l_debug BOOLEAN := false;
490
491 l_lead_lines_biud BOOLEAN := true;
492 l_lead_lines_after_biud BOOLEAN := true;
493 l_sales_credits_biud BOOLEAN := true;
494 l_sales_credits_after_biud BOOLEAN := true;
495
496 CURSOR Get_Min_Id IS
497 select min(lead_line_id)
498 from as_lead_lines_all;
499
500 CURSOR Get_Max_Id IS
501 select max(lead_line_id)
502 from as_lead_lines_all;
503
504 CURSOR Get_Next_Val IS
505 select AS_LEAD_LINES_S.nextval
506 from dual;
507
508 CURSOR Get_Disabled_Triggers(c_schema_name VARCHAR2) IS
509 select trigger_name
510 from all_triggers
511 where table_owner = c_schema_name
512 and trigger_name IN ('AS_LEAD_LINES_BIUD','AS_LEAD_LINES_AFTER_BIUD','AS_SALES_CREDITS_BIUD','AS_SALES_CREDITS_AFTER_BIUD')
513 and nvl(status,'DISABLED')<>'ENABLED';
514
515
516 BEGIN
517 -- First load the schema name
518 Load_Schema_Name;
519
520 if (upper(p_Debug_Flag) = 'Y') then
521 l_debug := true;
522 end if;
523
524 If l_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
525 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Migration started for Opportunity Lines');
526 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
527 end if;
528
529 -- First find out the existing state of the triggers
530 FOR scr in Get_Disabled_Triggers(G_SCHEMA_NAME)
531 LOOP
532 if (scr.trigger_name = 'AS_LEAD_LINES_BIUD') then
533 l_lead_lines_biud := false;
534 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
535 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Trigger AS_LEAD_LINES_BIUD is already disabled');
536 end if;
537 elsif (scr.trigger_name = 'AS_LEAD_LINES_AFTER_BIUD') then
538 l_lead_lines_after_biud := false;
539 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
540 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Trigger AS_LEAD_LINES_AFTER_BIUD is already disabled');
541 end if;
542 elsif (scr.trigger_name = 'AS_SALES_CREDITS_BIUD') then
543 l_sales_credits_biud := false;
544 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
545 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Trigger AS_SALES_CREDITS_BIUD is already disabled');
546 end if;
547 elsif (scr.trigger_name = 'AS_SALES_CREDITS_AFTER_BIUD') then
548 l_sales_credits_after_biud := false;
549 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
550 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Trigger AS_SALES_CREDITS_AFTER_BIUD is already disabled');
551 end if;
552 end if;
553 END LOOP;
554
555
556 -- Disable all the triggers
557 Disable_Triggers;
558
559 open Get_Min_Id;
560 fetch Get_Min_Id into l_min_id;
561 close Get_Min_Id;
562
563 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) Then
564 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Minimum Id found:' || l_min_id);
565 end if;
566
567 open Get_Next_Val;
568 fetch Get_Next_Val into l_max_id;
569 close Get_Next_Val;
570
571 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) Then
572 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Maximum Id found:' || l_max_id);
573 end if;
574
575 -- Create temporary index to improve the performance
576 Create_Temp_Index('AS_LEAD_LINES_ALL','LEAD_LINE_ID,INTEREST_TYPE_ID,PRIMARY_INTEREST_CODE_ID,SECONDARY_INTEREST_CODE_ID',l_debug);
577
578 -- Initialize counter
579 l_count := l_min_id;
580
584 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Current loop count:' || l_count);
581 while (l_count <= l_max_id)
582 loop
583 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) Then
585 end if;
586
587 -- Update interest type
588 update as_lead_lines_all l
589 set (product_category_id, product_cat_set_id) =
590 (select int.product_category_id, int.product_cat_set_id
591 from as_interest_types_b int
592 where l.interest_type_id = int.interest_type_id)
593 where l.lead_line_id >= l_count
594 and l.lead_line_id < l_count+G_BATCH_SIZE
595 and l.interest_type_id is not null
596 and l.primary_interest_code_id is null
597 and l.secondary_interest_code_id is null;
598
599 -- Update primary interest code
600 update as_lead_lines_all l
601 set (product_category_id, product_cat_set_id) =
602 (select int.product_category_id, int.product_cat_set_id
603 from as_interest_codes_b int
604 where l.primary_interest_code_id = int.interest_code_id)
605 where l.lead_line_id >= l_count
606 and l.lead_line_id < l_count+G_BATCH_SIZE
607 and l.primary_interest_code_id is not null
608 and l.secondary_interest_code_id is null;
609
610 -- Update secondary interest code
611 update as_lead_lines_all l
612 set (product_category_id, product_cat_set_id) =
613 (select int.product_category_id, int.product_cat_set_id
614 from as_interest_codes_b int
615 where l.secondary_interest_code_id = int.interest_code_id)
616 where l.lead_line_id >= l_count
617 and l.lead_line_id < l_count+G_BATCH_SIZE
618 and l.secondary_interest_code_id is not null;
619
620 -- commit after each batch
621 commit;
622
623 l_count := l_count + G_BATCH_SIZE;
624 end loop;
625 commit;
626
627 -- Drop temporary index
628 Drop_Temp_Index('AS_LEAD_LINES_ALL',l_debug);
629
630 -- Enable All the triggers
631 Enable_Triggers(l_lead_lines_biud,l_lead_lines_after_biud,l_sales_credits_biud,l_sales_credits_after_biud);
632
633
634 If l_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
635 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Migration for Opportunity Lines finished successfully');
636 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','End time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
637 end if;
638 EXCEPTION
639 WHEN OTHERS THEN
640 Rollback;
641 RETCODE := FND_API.G_RET_STS_ERROR;
642 ERRBUF := 'Error in opportunity lines data migration '||SQLERRM;
643 FND_FILE.PUT_LINE(FND_FILE.LOG,ERRBUF);
644 -- Enable All the triggers (even in case of exception)
645 Enable_Triggers(l_lead_lines_biud,l_lead_lines_after_biud,l_sales_credits_biud,l_sales_credits_after_biud);
646 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
647 IF l_status = TRUE THEN
648 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error, cannot complete Concurrent Program') ;
649 END IF ;
650 END Migrate_AS_LEAD_LINES_ALL;
651
652 /*
653 Migrate product_category_id and product_cat_set_id into AS_FST_SALES_CATEGORIES table
654 */
655 PROCEDURE Migrate_FST_SALES_CATEGORIES(
656 ERRBUF OUT NOCOPY VARCHAR2,
657 RETCODE OUT NOCOPY VARCHAR2,
658 p_Debug_Flag IN VARCHAR2 Default 'N'
659 ) IS
660 l_count NUMBER := 0;
661 l_min_id NUMBER := 0;
662 l_max_id NUMBER := 0;
663 l_status BOOLEAN;
664 l_debug BOOLEAN := false;
665
666 CURSOR Get_Min_Id IS
667 select min(fst_sales_category_id)
668 from as_fst_sales_categories;
669
670 CURSOR Get_Max_Id IS
671 select max(fst_sales_category_id)
672 from as_fst_sales_categories;
673
674 CURSOR Get_Next_Val IS
675 select AS_FST_SALES_CATEGORIES_S.nextval
676 from dual;
677
678 BEGIN
679 -- First load the schema name
680 Load_Schema_Name;
681
682 if (upper(p_Debug_Flag) = 'Y') then
683 l_debug := true;
684 end if;
685
686 If l_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
687 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Migration started for Forecast Categories');
688 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
689 end if;
690
691 open Get_Min_Id;
692 fetch Get_Min_Id into l_min_id;
693 close Get_Min_Id;
694
695 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) Then
696 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Minimum Id found:' || l_min_id);
697 end if;
698
699 open Get_Next_Val;
700 fetch Get_Next_Val into l_max_id;
701 close Get_Next_Val;
702
703 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) Then
707 -- Create temporary index to improve the performance
704 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Maximum Id found:' || l_max_id);
705 end if;
706
708 Create_Temp_Index('AS_FST_SALES_CATEGORIES','FST_SALES_CATEGORY_ID,INTEREST_TYPE_ID',l_debug);
709
710 -- Initialize counter
711 l_count := l_min_id;
712
713 while (l_count <= l_max_id)
714 loop
715 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) Then
716 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Current loop count:' || l_count);
717 end if;
718
719 -- Update interest type
720 update as_fst_sales_categories l
721 set (product_category_id, product_cat_set_id) =
722 (select int.product_category_id, int.product_cat_set_id
723 from as_interest_types_b int
724 where l.interest_type_id = int.interest_type_id)
725 where l.fst_sales_category_id >= l_count
726 and l.fst_sales_category_id < l_count+G_BATCH_SIZE
727 and l.interest_type_id is not null;
728
729 -- commit after each batch
730 commit;
731
732 l_count := l_count + G_BATCH_SIZE;
733 end loop;
734 commit;
735
736 -- Drop temporary index
737 Drop_Temp_Index('AS_FST_SALES_CATEGORIES',l_debug);
738
739 If l_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
740 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Migration for Forecast Categories finished successfully');
741 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','End time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
742 end if;
743 EXCEPTION
744 WHEN OTHERS THEN
745 Rollback;
746 RETCODE := FND_API.G_RET_STS_ERROR;
747 ERRBUF := 'Error in forecast category data migration '||SQLERRM;
748 FND_FILE.PUT_LINE(FND_FILE.LOG,ERRBUF);
749 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
750 IF l_status = TRUE THEN
751 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error, cannot complete Concurrent Program') ;
752 END IF ;
753 END Migrate_FST_SALES_CATEGORIES;
754
755 /*
756 Migrate product_category_id and product_cat_set_id into AS_INTERESTS_ALL table
757 */
758 PROCEDURE Migrate_AS_INTERESTS_ALL(
759 ERRBUF OUT NOCOPY VARCHAR2,
760 RETCODE OUT NOCOPY VARCHAR2,
761 p_Debug_Flag IN VARCHAR2 Default 'N'
762 ) IS
763 l_count NUMBER := 0;
764 l_min_id NUMBER := 0;
765 l_max_id NUMBER := 0;
766 l_status BOOLEAN;
767 l_debug BOOLEAN := false;
768
769 CURSOR Get_Min_Id IS
770 select min(interest_id)
771 from as_interests_all;
772
773 CURSOR Get_Max_Id IS
774 select max(interest_id)
775 from as_interests_all;
776
777 CURSOR Get_Next_Val IS
778 select AS_INTERESTS_S.nextval
779 from dual;
780
781 BEGIN
782 -- First load the schema name
783 Load_Schema_Name;
784
785 if (upper(p_Debug_Flag) = 'Y') then
786 l_debug := true;
787 end if;
788
789 If l_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
790 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Migration started for Product Interests');
791 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
792 end if;
793
794 open Get_Min_Id;
795 fetch Get_Min_Id into l_min_id;
796 close Get_Min_Id;
797
798 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) Then
799 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Minimum Id found:' || l_min_id);
800 end if;
801
802 open Get_Next_Val;
803 fetch Get_Next_Val into l_max_id;
804 close Get_Next_Val;
805
806 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) Then
807 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Maximum Id found:' || l_max_id);
808 end if;
809
810 -- Create temporary index to improve the performance
811 Create_Temp_Index('AS_INTERESTS_ALL','INTEREST_ID,INTEREST_TYPE_ID,PRIMARY_INTEREST_CODE_ID,SECONDARY_INTEREST_CODE_ID',l_debug);
812
813 -- Initialize counter
814 l_count := l_min_id;
815
816 while (l_count <= l_max_id)
817 loop
818 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) Then
819 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Current loop count:' || l_count);
820 end if;
821
822 -- Update interest type
823 update as_interests_all l
824 set (product_category_id, product_cat_set_id) =
825 (select int.product_category_id, int.product_cat_set_id
826 from as_interest_types_b int
827 where l.interest_type_id = int.interest_type_id)
828 where l.interest_id >= l_count
829 and l.interest_id < l_count+G_BATCH_SIZE
830 and l.interest_type_id is not null
831 and l.primary_interest_code_id is null
832 and l.secondary_interest_code_id is null;
833
837 (select int.product_category_id, int.product_cat_set_id
834 -- Update primary interest code
835 update as_interests_all l
836 set (product_category_id, product_cat_set_id) =
838 from as_interest_codes_b int
839 where l.primary_interest_code_id = int.interest_code_id)
840 where l.interest_id >= l_count
841 and l.interest_id < l_count+G_BATCH_SIZE
842 and l.primary_interest_code_id is not null
843 and l.secondary_interest_code_id is null;
844
845 -- Update secondary interest code
846 update as_interests_all l
847 set (product_category_id, product_cat_set_id) =
848 (select int.product_category_id, int.product_cat_set_id
849 from as_interest_codes_b int
850 where l.secondary_interest_code_id = int.interest_code_id)
851 where l.interest_id >= l_count
852 and l.interest_id < l_count+G_BATCH_SIZE
853 and l.secondary_interest_code_id is not null;
854
855 -- commit after each batch
856 commit;
857
858 l_count := l_count + G_BATCH_SIZE;
859 end loop;
860 commit;
861
862 -- Drop temporary index
863 Drop_Temp_Index('AS_INTERESTS_ALL',l_debug);
864
865 If l_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
866 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Migration for Product Interests finished successfully');
867 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','End time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
868 end if;
869 EXCEPTION
870 WHEN OTHERS THEN
871 Rollback;
872 RETCODE := FND_API.G_RET_STS_ERROR;
873 ERRBUF := 'Error in product interests data migration '||SQLERRM;
874 FND_FILE.PUT_LINE(FND_FILE.LOG,ERRBUF);
875 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
876 IF l_status = TRUE THEN
877 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error, cannot complete Concurrent Program') ;
878 END IF ;
879 END Migrate_AS_INTERESTS_ALL;
880
881 /*
882 Migrate product_category_id and product_cat_set_id into AS_LEAD_LINES_LOG table
883 */
884 PROCEDURE Migrate_AS_LEAD_LINES_LOG(
885 ERRBUF OUT NOCOPY VARCHAR2,
886 RETCODE OUT NOCOPY VARCHAR2,
887 p_Debug_Flag IN VARCHAR2 Default 'N'
888 ) IS
889 l_count NUMBER := 0;
890 l_min_id NUMBER := 0;
891 l_max_id NUMBER := 0;
892 l_status BOOLEAN;
893 l_debug BOOLEAN := false;
894
895 CURSOR Get_Min_Id IS
896 select min(log_id)
897 from as_lead_lines_log;
898
899 CURSOR Get_Max_Id IS
900 select max(log_id)
901 from as_lead_lines_log;
902
903 CURSOR Get_Next_Val IS
904 select AS_LEAD_LINES_LOG_S.nextval
905 from dual;
906
907 BEGIN
908 -- First load the schema name
909 Load_Schema_Name;
910
911 if (upper(p_Debug_Flag) = 'Y') then
912 l_debug := true;
913 end if;
914
915 If l_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
916 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Migration started for Opportunity Logs');
917 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
918 end if;
919
920 open Get_Min_Id;
921 fetch Get_Min_Id into l_min_id;
922 close Get_Min_Id;
923
924 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) Then
925 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Minimum Id found:' || l_min_id);
926 end if;
927
928 open Get_Next_Val;
929 fetch Get_Next_Val into l_max_id;
930 close Get_Next_Val;
931
932 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) Then
933 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Maximum Id found:' || l_max_id);
934 end if;
935
936 -- Create temporary index to improve the performance
937 Create_Temp_Index('AS_LEAD_LINES_LOG','LOG_ID,INTEREST_TYPE_ID,PRIMARY_INTEREST_CODE_ID,SECONDARY_INTEREST_CODE_ID',l_debug);
938
939 -- Initialize counter
940 l_count := l_min_id;
941
942 while (l_count <= l_max_id)
943 loop
944 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) Then
945 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Current loop count:' || l_count);
946 end if;
947
948 -- Update interest type
949 update as_lead_lines_log l
950 set (product_category_id, product_cat_set_id) =
951 (select int.product_category_id, int.product_cat_set_id
952 from as_interest_types_b int
953 where l.interest_type_id = int.interest_type_id)
954 where l.log_id >= l_count
955 and l.log_id < l_count+G_BATCH_SIZE
956 and l.interest_type_id is not null
957 and l.primary_interest_code_id is null
958 and l.secondary_interest_code_id is null;
959
960 -- Update primary interest code
964 from as_interest_codes_b int
961 update as_lead_lines_log l
962 set (product_category_id, product_cat_set_id) =
963 (select int.product_category_id, int.product_cat_set_id
965 where l.primary_interest_code_id = int.interest_code_id)
966 where l.log_id >= l_count
967 and l.log_id < l_count+G_BATCH_SIZE
968 and l.primary_interest_code_id is not null
969 and l.secondary_interest_code_id is null;
970
971 -- Update secondary interest code
972 update as_lead_lines_log l
973 set (product_category_id, product_cat_set_id) =
974 (select int.product_category_id, int.product_cat_set_id
975 from as_interest_codes_b int
976 where l.secondary_interest_code_id = int.interest_code_id)
977 where l.log_id >= l_count
978 and l.log_id < l_count+G_BATCH_SIZE
979 and l.secondary_interest_code_id is not null;
980
981 -- commit after each batch
982 commit;
983
984 l_count := l_count + G_BATCH_SIZE;
985 end loop;
986 commit;
987
988 -- Drop temporary index
989 Drop_Temp_Index('AS_LEAD_LINES_LOG',l_debug);
990
991 If l_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
992 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Migration for Opportunity Logs finished successfully');
993 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','End time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
994 end if;
995 EXCEPTION
996 WHEN OTHERS THEN
997 Rollback;
998 RETCODE := FND_API.G_RET_STS_ERROR;
999 ERRBUF := 'Error in opportunity logs data migration '||SQLERRM;
1000 FND_FILE.PUT_LINE(FND_FILE.LOG,ERRBUF);
1001 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
1002 IF l_status = TRUE THEN
1003 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error, cannot complete Concurrent Program') ;
1004 END IF ;
1005 END Migrate_AS_LEAD_LINES_LOG;
1006
1007 /*
1008 Migrate product_category_id and product_cat_set_id into AS_SALES_CREDITS_DENORM table
1009 */
1010 PROCEDURE Migrate_AS_SALES_C_DENORM(
1011 ERRBUF OUT NOCOPY VARCHAR2,
1012 RETCODE OUT NOCOPY VARCHAR2,
1013 p_Debug_Flag IN VARCHAR2 Default 'N'
1014 ) IS
1015 l_count NUMBER := 0;
1016 l_min_id NUMBER := 0;
1017 l_max_id NUMBER := 0;
1018 l_status BOOLEAN;
1019 l_debug BOOLEAN := false;
1020
1021 CURSOR Get_Min_Id IS
1022 select min(sales_credit_id)
1023 from as_sales_credits_denorm;
1024
1025 CURSOR Get_Max_Id IS
1026 select max(sales_credit_id)
1027 from as_sales_credits_denorm;
1028
1029 CURSOR Get_Next_Val IS
1030 select AS_SALES_CREDITS_S.nextval
1031 from dual;
1032
1033 BEGIN
1034 -- First load the schema name
1035 Load_Schema_Name;
1036
1037 if (upper(p_Debug_Flag) = 'Y') then
1038 l_debug := true;
1039 end if;
1040
1041 If l_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1042 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Migration started for Sales Credits');
1043 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1044 end if;
1045
1046 open Get_Min_Id;
1047 fetch Get_Min_Id into l_min_id;
1048 close Get_Min_Id;
1049
1050 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) Then
1051 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Minimum Id found:' || l_min_id);
1055 fetch Get_Next_Val into l_max_id;
1052 end if;
1053
1054 open Get_Next_Val;
1056 close Get_Next_Val;
1057
1058 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) Then
1059 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Maximum Id found:' || l_max_id);
1060 end if;
1061
1062 -- Create temporary index to improve the performance
1063 Create_Temp_Index('AS_SALES_CREDITS_DENORM','SALES_CREDIT_ID,INTEREST_TYPE_ID,PRIMARY_INTEREST_CODE_ID,SECONDARY_INTEREST_CODE_ID',l_debug);
1064
1065 -- Initialize counter
1066 l_count := l_min_id;
1067
1068 while (l_count <= l_max_id)
1069 loop
1070 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) Then
1071 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Current loop count:' || l_count);
1072 end if;
1073
1074 -- Update interest type
1075 update as_sales_credits_denorm l
1076 set (product_category_id, product_cat_set_id) =
1077 (select int.product_category_id, int.product_cat_set_id
1078 from as_interest_types_b int
1079 where l.interest_type_id = int.interest_type_id)
1080 where l.sales_credit_id >= l_count
1081 and l.sales_credit_id < l_count+G_BATCH_SIZE
1082 and nvl(l.interest_type_id,-1) <> -1
1083 and nvl(l.primary_interest_code_id,-1) = -1
1084 and nvl(l.secondary_interest_code_id,-1) = -1;
1085
1086 -- Update primary interest code
1087 update as_sales_credits_denorm l
1088 set (product_category_id, product_cat_set_id) =
1089 (select int.product_category_id, int.product_cat_set_id
1090 from as_interest_codes_b int
1091 where l.primary_interest_code_id = int.interest_code_id)
1092 where l.sales_credit_id >= l_count
1093 and l.sales_credit_id < l_count+G_BATCH_SIZE
1094 and nvl(l.primary_interest_code_id,-1) <> -1
1095 and nvl(l.secondary_interest_code_id,-1) = -1;
1096
1097 -- Update secondary interest code
1098 update as_sales_credits_denorm l
1099 set (product_category_id, product_cat_set_id) =
1100 (select int.product_category_id, int.product_cat_set_id
1101 from as_interest_codes_b int
1102 where l.secondary_interest_code_id = int.interest_code_id)
1103 where l.sales_credit_id >= l_count
1104 and l.sales_credit_id < l_count+G_BATCH_SIZE
1105 and nvl(l.secondary_interest_code_id,-1) <> -1;
1106
1107 -- commit after each batch
1108 commit;
1109
1110 l_count := l_count + G_BATCH_SIZE;
1111 end loop;
1112 commit;
1113
1114 -- Drop temporary index
1115 Drop_Temp_Index('AS_SALES_CREDITS_DENORM',l_debug);
1116
1117 If l_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1118 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Migration for Sales Credits finished successfully');
1119 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','End time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1120 end if;
1121 EXCEPTION
1122 WHEN OTHERS THEN
1123 Rollback;
1124 RETCODE := FND_API.G_RET_STS_ERROR;
1125 ERRBUF := 'Error in sales credits data migration '||SQLERRM;
1126 FND_FILE.PUT_LINE(FND_FILE.LOG,ERRBUF);
1127 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
1128 IF l_status = TRUE THEN
1129 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error, cannot complete Concurrent Program') ;
1130 END IF ;
1131 END Migrate_AS_SALES_C_DENORM;
1132
1133 /*
1134 Migrate product_category_id and product_cat_set_id into AS_OPP_WORKSHEET_LINES table
1135 Since table has been not used in R12 so we need to obsolete Migrate_AS_OPPWKS_LINES procedure
1136 */
1137
1138 PROCEDURE Migrate_AS_PRODWKS_LINES(
1139 ERRBUF OUT NOCOPY VARCHAR2,
1140 RETCODE OUT NOCOPY VARCHAR2,
1141 p_Debug_Flag IN VARCHAR2 Default 'N'
1142 ) IS
1143 l_count NUMBER := 0;
1144 l_min_id NUMBER := 0;
1145 l_max_id NUMBER := 0;
1146 l_status BOOLEAN;
1147 l_debug BOOLEAN := false;
1148
1149 CURSOR Get_Min_Id IS
1150 select min(PROD_WORKSHEET_LINE_ID)
1151 from AS_PROD_WORKSHEET_LINES;
1152
1153 CURSOR Get_Max_Id IS
1154 select max(PROD_WORKSHEET_LINE_ID)
1155 from AS_PROD_WORKSHEET_LINES;
1156
1157 CURSOR Get_Next_Val IS
1158 select AS_PROD_WORKSHEET_LINES_S.nextval
1159 from dual;
1160
1161 BEGIN
1162 -- First load the schema name
1163 Load_Schema_Name;
1164
1165 if (upper(p_Debug_Flag) = 'Y') then
1166 l_debug := true;
1167 end if;
1168
1169 If l_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1170 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Migration started for Forecast Product Worksheet');
1171 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1172 end if;
1173
1174 open Get_Min_Id;
1175 fetch Get_Min_Id into l_min_id;
1176 close Get_Min_Id;
1177
1178 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) Then
1182 open Get_Next_Val;
1179 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Minimum Id found:' || l_min_id);
1180 end if;
1181
1183 fetch Get_Next_Val into l_max_id;
1184 close Get_Next_Val;
1185
1186 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) Then
1187 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Maximum Id found:' || l_max_id);
1188 end if;
1189
1190 -- Create temporary index to improve the performance
1191 Create_Temp_Index('AS_PROD_WORKSHEET_LINES','PROD_WORKSHEET_LINE_ID,INTEREST_TYPE_ID',l_debug);
1192
1193 -- Initialize counter
1194 l_count := l_min_id;
1195
1196 while (l_count <= l_max_id)
1197 loop
1198 IF (l_debug) and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) Then
1199 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Current loop count:' || l_count);
1200 end if;
1201
1202 -- Update interest type
1203 update AS_PROD_WORKSHEET_LINES pwl
1204 set (product_category_id, product_cat_set_id) =
1205 (select int.product_category_id, int.product_cat_set_id
1206 from as_interest_types_b int
1207 where pwl.interest_type_id = int.interest_type_id)
1208 where pwl.PROD_WORKSHEET_LINE_ID >= l_count
1209 and pwl.PROD_WORKSHEET_LINE_ID < l_count+G_BATCH_SIZE
1210 and pwl.interest_type_id is not null;
1211
1212 -- commit after each batch
1213 commit;
1214
1215 l_count := l_count + G_BATCH_SIZE;
1216 end loop;
1217 commit;
1218
1219 -- Drop temporary index
1220 Drop_Temp_Index('AS_PROD_WORKSHEET_LINES',l_debug);
1221
1222 If l_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1223 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Migration for Forecast Product Worksheet finished successfully');
1224 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','End time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1225 end if;
1226 EXCEPTION
1227 WHEN OTHERS THEN
1228 Rollback;
1229 RETCODE := FND_API.G_RET_STS_ERROR;
1230 ERRBUF := 'Error in forecast product worksheet data migration '||SQLERRM;
1231 FND_FILE.PUT_LINE(FND_FILE.LOG,ERRBUF);
1232 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
1233 IF l_status = TRUE THEN
1234 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error, cannot complete Concurrent Program') ;
1235 END IF ;
1236 END Migrate_AS_PRODWKS_LINES;
1237
1238 /*
1239 Migrate product_category_id and product_cat_set_id into AS_PE_INT_CATEGORIES table
1240 */
1241 PROCEDURE Migrate_AS_PE_INT_CATEGORIES(
1242 ERRBUF OUT NOCOPY VARCHAR2,
1243 RETCODE OUT NOCOPY VARCHAR2,
1244 p_Debug_Flag IN VARCHAR2 Default 'N'
1245 ) IS
1246 l_debug BOOLEAN;
1247 l_status BOOLEAN;
1248
1249
1250 BEGIN
1251 -- Note: We are not using any detailed performance "batching" since this table
1252 -- in most implementation will contain less than 1000 rows.
1253
1254
1255 if (upper(p_Debug_Flag) = 'Y') then
1256 l_debug := true;
1257 end if;
1258
1259 If l_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1260 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Migration started for Plan Element Categories');
1261 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1262 end if;
1263
1264
1265 -- Update interest type
1266 update as_pe_int_categories l
1267 set (product_category_id, product_cat_set_id) =
1268 (select int.product_category_id, int.product_cat_set_id
1269 from as_interest_types_b int
1270 where l.interest_type_id = int.interest_type_id)
1271 where nvl(l.interest_type_id,-1) <> -1
1272 and nvl(l.pri_interest_code_id,-1) = -1
1273 and nvl(l.sec_interest_code_id,-1) = -1;
1274
1275 -- Update primary interest code
1276 update as_pe_int_categories l
1277 set (product_category_id, product_cat_set_id) =
1278 (select int.product_category_id, int.product_cat_set_id
1279 from as_interest_codes_b int
1280 where l.pri_interest_code_id = int.interest_code_id)
1281 where nvl(l.pri_interest_code_id,-1) <> -1
1282 and nvl(l.sec_interest_code_id,-1) = -1;
1283
1284 -- Update secondary interest code
1285 update as_pe_int_categories l
1286 set (product_category_id, product_cat_set_id) =
1287 (select int.product_category_id, int.product_cat_set_id
1288 from as_interest_codes_b int
1289 where l.sec_interest_code_id = int.interest_code_id)
1290 where nvl(l.sec_interest_code_id,-1) <> -1;
1291
1292 -- commit after each batch
1293 commit;
1294
1295 If l_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1296 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Migration for OIC Plan Elements finished successfully');
1300 WHEN OTHERS THEN
1297 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','End time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1298 end if;
1299 EXCEPTION
1301 Rollback;
1302 RETCODE := FND_API.G_RET_STS_ERROR;
1303 ERRBUF := 'Error in plan element categories '||SQLERRM;
1304 FND_FILE.PUT_LINE(FND_FILE.LOG,ERRBUF);
1305 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
1306 IF l_status = TRUE THEN
1307 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error, cannot complete Concurrent Program') ;
1308 END IF ;
1309 END Migrate_AS_PE_INT_CATEGORIES;
1310
1311 /*
1312 Enable the following triggers
1313 a) AS_LEAD_LINES_BIUD
1314 b) AS_LEAD_LINES_AFTER_BIUD
1315 c) AS_SALES_CREDITS_BIUD
1316 d) AS_SALES_CREDITS_AFTER_BIUD
1317 */
1318 PROCEDURE Enable_Triggers(p_lead_lines_biud IN BOOLEAN,
1319 p_lead_lines_after_biud IN BOOLEAN,
1320 p_sales_credits_biud IN BOOLEAN,
1321 p_sales_credits_after_biud IN BOOLEAN) IS
1322 BEGIN
1323 -- Enable All the triggers
1324 if (p_lead_lines_biud) then
1325 EXECUTE IMMEDIATE('Alter trigger AS_LEAD_LINES_BIUD ENABLE');
1326 end if;
1327
1328 if (p_lead_lines_after_biud) then
1329 EXECUTE IMMEDIATE('Alter trigger AS_LEAD_LINES_AFTER_BIUD ENABLE');
1330 end if;
1331
1332 if (p_sales_credits_biud) then
1333 EXECUTE IMMEDIATE('Alter trigger AS_SALES_CREDITS_BIUD ENABLE');
1334 end if;
1335
1336 if (p_sales_credits_after_biud) then
1337 EXECUTE IMMEDIATE('Alter trigger AS_SALES_CREDITS_AFTER_BIUD ENABLE');
1338 end if;
1339 END Enable_Triggers;
1340
1341 /*
1342 Disable the following triggers
1343 a) AS_LEAD_LINES_BIUD
1344 b) AS_LEAD_LINES_AFTER_BIUD
1345 c) AS_SALES_CREDITS_BIUD
1346 d) AS_SALES_CREDITS_AFTER_BIUD
1347 */
1348 PROCEDURE Disable_Triggers IS
1349 BEGIN
1350 -- Disable all the triggers
1351 EXECUTE IMMEDIATE('Alter trigger AS_LEAD_LINES_BIUD DISABLE');
1352 EXECUTE IMMEDIATE('Alter trigger AS_LEAD_LINES_AFTER_BIUD DISABLE');
1353 EXECUTE IMMEDIATE('Alter trigger AS_SALES_CREDITS_BIUD DISABLE');
1354 EXECUTE IMMEDIATE('Alter trigger AS_SALES_CREDITS_AFTER_BIUD DISABLE');
1355 END Disable_Triggers;
1356
1357 PROCEDURE Load_Schema_Name IS
1358 l_status VARCHAR2(2);
1359 l_industry VARCHAR2(2);
1360 l_oracle_schema VARCHAR2(32) := 'OSM';
1361 l_schema_return BOOLEAN;
1362 BEGIN
1363 if (G_SCHEMA_NAME is null) then
1364 l_schema_return := FND_INSTALLATION.get_app_info('AS', l_status, l_industry, l_oracle_schema);
1365 G_SCHEMA_NAME := l_oracle_schema;
1366 end if;
1367 END;
1368
1369 PROCEDURE Create_Temp_Index(p_table IN VARCHAR2,
1370 p_index_columns IN VARCHAR2,
1371 p_debug IN BOOLEAN) IS
1372 l_check_tspace_exist varchar2(100);
1373 l_index_tablespace varchar2(100);
1374 l_sql_stmt varchar2(2000);
1375 l_user varchar2(2000);
1376 l_index_name varchar2(100);
1377
1378 begin
1379 --execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
1380
1381 -----------------
1382 -- Create index--
1383 -----------------
1384
1385 l_user := USER;
1386
1387 if p_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1388 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','User is:' || l_user);
1389 end if;
1390
1391 -- Name for temporary index created for migration
1392 l_index_name := p_table || G_INDEX_SUFFIX;
1393
1394 AD_TSPACE_UTIL.get_tablespace_name('AS', 'TRANSACTION_INDEXES','N',l_check_tspace_exist,l_index_tablespace);
1395
1396 l_sql_stmt := 'create index ' || l_index_name || ' on '
1397 || G_SCHEMA_NAME||'.'
1398 || p_table || '(' || p_index_columns || ') '
1399 ||' tablespace ' || l_index_tablespace || ' nologging '
1400 ||'parallel 8';
1401 execute immediate l_sql_stmt;
1402
1403 --------------------
1404 -- convert to no||--
1405 --------------------
1406 l_sql_stmt := 'alter index '|| l_user ||'.' || l_index_name || ' noparallel ';
1407 execute immediate l_sql_stmt;
1408
1409
1410 -----------------
1411 -- Gather Stats--
1412 -----------------
1413 dbms_stats.gather_index_stats(l_user,l_index_name,estimate_percent => 10);
1414
1415 if p_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1416 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Created temporary index:' || l_index_name);
1417 end if;
1418 END Create_Temp_Index;
1419
1420 PROCEDURE Drop_Temp_Index(p_table IN VARCHAR2,
1421 p_debug IN BOOLEAN) IS
1422 l_sql_stmt varchar2(2000);
1423 l_index_name varchar2(100);
1424 l_user varchar2(2000);
1425 begin
1426 -----------------
1427 -- Drop index --
1428 -----------------
1429 l_user := USER;
1430
1431 -- Name for temporary index created for migration
1432 l_index_name := p_table || G_INDEX_SUFFIX;
1433
1434 l_sql_stmt := 'drop index ' || l_user||'.' || l_index_name || ' ';
1435
1436 execute immediate l_sql_stmt;
1437
1438 if p_debug and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1439 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'as.plsql.conc.asxmintb','Dropped temporary index:' || l_index_name);
1440 end if;
1441 END Drop_Temp_Index;
1442
1443 /* For testing purposes we can use the following statements.
1444 Change the date as per requirements.
1445 update as_lead_lines_all set product_category_id=null,product_cat_set_id=null where nvl(interest_type_id,-1)<>-1;
1446 update as_interests_all set product_category_id=null,product_cat_set_id=null where nvl(interest_type_id,-1)<>-1;
1447 update as_sales_credits_denorm set product_category_id=null,product_cat_set_id=null where nvl(interest_type_id,-1)<>-1;
1448 update as_lead_lines_log set product_category_id=null,product_cat_set_id=null where nvl(interest_type_id,-1)<>-1;
1449 update as_fst_sales_categories set product_category_id=null,product_cat_set_id=null where nvl(interest_type_id,-1)<>-1;
1450 update as_opp_worksheet_lines set product_category_id=null,product_cat_set_id=null where nvl(interest_type_id,-1)<>-1;
1451 update as_prod_worksheet_lines set product_category_id=null,product_cat_set_id=null where nvl(interest_type_id,-1)<>-1;
1452 commit;
1453 */
1454
1455 END AS_INT_TYP_COD_MIGRATION;
[Home] [Help]