[Home] [Help]
PACKAGE BODY: APPS.IBE_BI_STR_MIG_PVT
Source
1 PACKAGE BODY ibe_bi_str_mig_pvt AS
2 /* $Header: IBEBISTRMIGB.pls 120.1 2005/09/14 03:33:29 appldev ship $ */
3
4
5 g_start_dt DATE := SYSDATE;
6
7
8 type g_org_mapping is RECORD(
9 minisite_id aso_quote_headers_all.minisite_id%TYPE,
10 org_id aso_quote_headers_all.org_id%TYPE,
11 minisite_name ibe_msites_vl.msite_name%TYPE,
12 org_name hr_operating_units.name%TYPE ,
13 is_valid_org varchar2(1),
14 is_valid_str varchar2(1),
15 is_duplicate_org varchar2(1) );
16
17 type org_mapping_table is table of g_org_mapping
18 index by binary_integer;
19
20
21
22 v_mapping_tab org_mapping_table;
23
24 g_return_code NUMBER := 0;
25
26
27 -- ===========================================================
28 -- Procedure printLog uses FND_FILE.PUT_LINE to write in the
29 -- "log" file of a concurrent program
30 -- ===========================================================
31 PROCEDURE printLog(p_message IN VARCHAR2)
32 IS
33 BEGIN
34 FND_FILE.PUT_LINE(FND_FILE.LOG,p_message);
35 END printLog;
36
37 -- ===========================================================
38 -- Procedure printOutput uses FND_FILE.PUT_LINE to write in the
39 -- "Output" file of a concurrent program
40 -- ===========================================================
41 PROCEDURE printOutput(p_message IN VARCHAR2)
42 IS
43 BEGIN
44 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_message);
45 printLog(p_message);
46 END printOutput;
47
48
49
50 -- ====================================================
51 -- Procedure Clean_log_table Resets the rows in
52 -- temporary table to start execution of the program
53 -- ====================================================
54 PROCEDURE Clean_log_table
55 IS
56 BEGIN
57 printLog('Procedure Clean_log : Begin');
58
59 DELETE FROM ibe_migration_log
60 WHERE Migration_code = 'IBE_MINISITE_MIGRATION'
61 AND migration_mode = 'EVALUATE';
62
63 printLog('Procedure Clean_log : End');
64
65 EXCEPTION
66 WHEN OTHERS THEN
67 printLog('Procedure Clean_log : Exception '||sqlerrm);
68 g_return_code := 2;
69 RAISE;
70 END Clean_log_table;
71
72
73
74 -- =========================================
75 -- Procedure Parse_org_minisite_mapping
76 -- The procedure
77 -- =========================================
78
79 PROCEDURE parse_org_minisite_mapping (
80 p_string_in IN VARCHAR2,
81 p_dlim_in IN VARCHAR2
82 ) IS
83
84 l_length NUMBER;
85 l_lim NUMBER;
86 i NUMBER;
87 j NUMBER;
88 l_pos NUMBER;
89 l_position NUMBER;
90 l_start_pos NUMBER;
91 l_start_position NUMBER;
92 l_string_out VARCHAR2(255);
93 l_string_left VARCHAR2(255);
94 l_string_in VARCHAR2(255);
95 l_index NUMBER;
96 x NUMBER;
97 y NUMBER ;
98 l_temp_msg VARCHAR2(2000);
99 invalid_org_msite EXCEPTION;
100
101
102 BEGIN
103
104 printLog('Procedure Parse_org_minisite_mapping : Begin');
105
106 l_index := 1;
107
108 l_string_in := replace(p_string_in, ' ' , '');
109
110 FOR x in 1..3 LOOP
111 l_string_in := replace(l_string_in, ',,', ',');
112 END LOOP;
113 FOR x in 1..3 LOOP
114 l_string_in := replace(l_string_in, '::', ':');
115 END LOOP;
116 l_length := length(trim(l_string_in));
117
118 l_lim := l_length;
119 i := 1;
120 j := 1;
121 l_pos := 0;
122 l_position :=0;
123 l_start_pos := 1;
124 l_start_position := 1;
125
126 WHILE i <= l_lim LOOP
127 j := instr(l_string_in,p_dlim_in,i);
128
129 IF i = j THEN
130 RAISE invalid_org_msite;
131 END IF;
132 IF ( j <> 0 ) THEN
133 i := j+1;
134 l_pos := j-1;
135 END IF;
136 l_string_out := substr(l_string_in,l_start_pos,j-l_start_pos);
137 IF (l_pos=0) THEN
138 l_string_left := substr(l_string_in,l_pos+1,length(l_string_in)-l_pos);
139 ELSE
140 l_string_left := substr(l_string_in,l_pos+2,length(l_string_in)-l_pos);
141 END IF;
142
143 IF l_string_out is null THEN
144 l_string_out :=l_string_left ;
145 END IF;
146
147 l_position:= instr(l_string_out,':');
148
149 IF l_position = 0 THEN
150 l_string_out := l_string_out || ':';
151 l_position:= instr(l_string_out,':');
152 END IF ;
153
154
155 v_mapping_tab(l_index).org_id := substr(l_string_out, 1,l_position-1);
156
157 IF l_position = length(l_string_out) THEN
158 v_mapping_tab(l_index).minisite_id := NULL;
159 v_mapping_tab(l_index).is_valid_str := 'N';
160 ELSE
161 v_mapping_tab(l_index).minisite_id :=substr(l_string_out,l_position+1);
162 v_mapping_tab(l_index).is_valid_str := 'Y';
163 END IF;
164
165 v_mapping_tab(l_index).minisite_name := 'NULL';
166 v_mapping_tab(l_index).org_name := 'NULL';
167 v_mapping_tab(l_index).is_valid_org := 'Y';
168 v_mapping_tab(l_index).is_duplicate_org := 'N';
169
170 l_index := l_index+1;
171 l_start_pos := j+1;
172 EXIT WHEN j = 0;
173 END LOOP;
174
175 printLog('Procedure Parse_org_minisite_mapping : End');
176
177 EXCEPTION
178 WHEN invalid_org_msite THEN
179 printlog(' ');
180 printlog(' ');
181 printLog(rpad('*',80,'*'));
182 printlog(' ');
183 fnd_message.set_name('IBE','IBE_BI_INVALID_ORG_MSITE_ERR');
184 l_temp_msg := fnd_message.get;
185
186 printLog(l_temp_msg);
187 printlog(' ');
188 printLog(rpad('*',80,'*'));
189 printlog(' ');
190 printlog(' ');
191 -- RAISE;
192 NULL;
193 WHEN OTHERS THEN
194 printLog('Error in parse_org_minisite_mapping , Exception Others' );
195 NULL;
196 END parse_org_minisite_mapping;
197
198
199
200 -- =======================================================
201 -- Procedure Validate_params - used to validate user input
202 -- =======================================================
203
204
205 PROCEDURE Validate_params IS
206
207 l_valid_org_id HR_OPERATING_UNITS.ORGANIZATION_ID%TYPE ;
208 l_valid_minisite_id IBE_MSITES_VL.MSITE_ID%TYPE ;
209 l_valid_minisite_name ibe_msites_vl.msite_name%TYPE;
210 l_valid_org_name hr_operating_units.name%TYPE ;
211 l_temp_msg VARCHAR2(2000);
212 invalid_str_org_input EXCEPTION;
213
214
215 CURSOR c_valid_org_id(c_org_id NUMBER) IS
216 SELECT organization_id,name
217 FROM hr_operating_units ou
218 WHERE ou.organization_id = c_org_id ;
219
220 CURSOR c_valid_msite_id(c_msite_id NUMBER ) IS
221 SELECT msite_id,msite_name
222 FROM ibe_msites_vl str
223 WHERE str.msite_id = c_msite_id
224 AND str.site_type= 'I'; -- Changed as per the Bug # 4394901
225
226 m NUMBER;
227 n NUMBER;
228 o number;
229
230 BEGIN
231
232 printLog('Procedure validate_params : start');
233 o := v_mapping_tab.FIRST;
234 WHILE o IS NOT NULL LOOP
235 IF v_mapping_tab(o).org_id IS NULL AND v_mapping_tab(o).minisite_id IS NULL THEN
236 v_mapping_tab.DELETE(o);
237 END IF;
238
239 o := v_mapping_tab.NEXT(o);
240
241 END LOOP;
242
243 if v_mapping_tab.first is not null then
244 FOR n in v_mapping_tab.first ..v_mapping_tab.last LOOP
245
246 IF v_mapping_tab.EXISTS(n) THEN
247 FOR m in v_mapping_tab.first .. v_mapping_tab.last LOOP
248 IF v_mapping_tab.EXISTS(m) THEN
249 IF ( (v_mapping_tab(n).org_id = v_mapping_tab(m).org_id) AND (n <>m) AND (n>m) ) THEN
250 v_mapping_tab(n).is_duplicate_org := 'Y';
251 END IF;
252 END IF;
253 END LOOP;
254
255 OPEN c_valid_org_id(v_mapping_tab(n).org_id);
256 FETCH c_valid_org_id into l_valid_org_id,l_valid_org_name;
257
258
259 IF (c_valid_org_id%NOTFOUND) THEN
260 v_mapping_tab(n).is_valid_org :='N' ;
261 v_mapping_tab(n).org_name := 'NULL';
262 ELSE
263 v_mapping_tab(n).org_name := l_valid_org_name;
264
265 END IF ;
266
267 CLOSE c_valid_org_id;
268
269
270 OPEN c_valid_msite_id(v_mapping_tab(n).minisite_id);
271 FETCH c_valid_msite_id into l_valid_minisite_id,l_valid_minisite_name;
272
273
274
275 IF (c_valid_msite_id%NOTFOUND) then
276 v_mapping_tab(n).is_valid_str :='N' ;
277 v_mapping_tab(n).minisite_name := 'NULL';
278 ELSE
279 v_mapping_tab(n).minisite_name := l_valid_minisite_name;
280
281 END IF ;
282 CLOSE c_valid_msite_id;
283
284
285 END IF;
286
287 END LOOP;
288
289 printlog(' ');
290 printlog(' ');
291 printLog(rpad('*',80,'*'));
292 printlog(' ');
293 fnd_message.set_name('IBE','IBE_BI_OPR_NOT_VALID');
294 l_temp_msg := fnd_message.get;
295
296 FOR q in v_mapping_tab.first ..v_mapping_tab.last LOOP
297
298 IF v_mapping_tab.EXISTS(q) AND v_mapping_tab(q).is_valid_org = 'N' THEN
299 if l_temp_msg is not null then
300 printlog(l_temp_msg);
301 l_temp_msg :=NULL;
302 end if;
303 printlog(v_mapping_tab(q).org_id);
304
305 END IF;
306 END LOOP;
307
308
309 fnd_message.set_name('IBE','IBE_BI_STR_NOT_VALID');
310 l_temp_msg := fnd_message.get;
311
312 FOR r in v_mapping_tab.first ..v_mapping_tab.last LOOP
313
314 IF v_mapping_tab.EXISTS(r) AND v_mapping_tab(r).is_valid_str = 'N' THEN
315 if l_temp_msg is not null then
316 printlog(l_temp_msg);
317 l_temp_msg :=NULL;
318 end if;
319 printlog(v_mapping_tab(r).minisite_id);
320
321 END IF;
322 END LOOP;
323
324 fnd_message.set_name('IBE','IBE_BI_OPR_DUPLICATE');
325
326
327 FOR s in v_mapping_tab.first ..v_mapping_tab.last LOOP
328
329 IF v_mapping_tab.EXISTS(s) AND v_mapping_tab(s).is_duplicate_org = 'Y' THEN
330 fnd_message.set_token('1',v_mapping_tab(s).org_id);
331 l_temp_msg := fnd_message.get;
332 printlog(l_temp_msg);
333 printlog(v_mapping_tab(s).org_id || ':' ||v_mapping_tab(s).minisite_id);
334 END IF;
335 END LOOP;
336
337
338 FOR r in v_mapping_tab.first ..v_mapping_tab.last LOOP
339 IF ( v_mapping_tab.EXISTS(r) AND v_mapping_tab(r).is_valid_str = 'N') OR
340 (v_mapping_tab.EXISTS(r) AND v_mapping_tab(r).is_valid_org = 'N') THEN
341 RAISE invalid_str_org_input ;
342 END IF;
343 END LOOP;
344 end if;
345 printLog('Procedure validate_params : End');
346
347
348 EXCEPTION
349
350 WHEN invalid_str_org_input THEN
351 fnd_message.set_name('IBE','IBE_BI_INVALID_ORG_MSITE_ERR');
352 l_temp_msg := fnd_message.get;
353 printLog(l_temp_msg);
354 printlog(' ');
355 printLog(rpad('*',80,'*'));
356 printlog(' ');
357 printlog(' ');
358 g_return_code := 2;
359 RAISE;
360 WHEN OTHERS THEN
361 printLog('Procedure validate_params :' || sqlerrm);
362 g_return_code := 2;
363 RAISE;
364 END validate_params;
365
366
367
368 --=========================================================================
369 -- Load_temp_table api to load temp table with the quotes to be migrated
370 --=========================================================================
371
372
373 PROCEDURE Load_temp_table(p_override_minisite_flag IN VARCHAR2,
374 p_auto_defaulting_flag IN VARCHAR2) IS
375
376 l_append_condition varchar2(50);
377
378
379 BEGIN
380
381 printLog('Procedure Load_temp_table : Begin');
382
383 -- p_override_minisite_flag indicates whether carts/published quotes with
384 -- minisite_id need to be considered for update based on the applied rules.
385
386
387 printLog('Procedure Load_temp_table : p_override_minisite_flag = '||p_override_minisite_flag );
388 printLog('Procedure Load_tem_table: 1 or 3:' || l_append_condition);
389
390 --Insert into ibe_migration_log
391 IF p_auto_defaulting_flag = '1' OR p_auto_defaulting_flag ='3' THEN
392 printLog('Procedure defaulting_flag : 1 or 3:');
393
394 IF p_override_minisite_flag = 'Y' then
395 INSERT INTO IBE_MIGRATION_LOG (
396 mglog_id,
397 migration_code ,
398 migration_mode ,
399 run_sequence,
400 attribute1,
401 attribute_idx1,
402 attribute_idx2,
403 attribute2,
404 attribute3,
405 attribute4,
406 attribute5,
407 attribute6,
408 attribute7,
409 attribute8,
410 created_by,
411 creation_date,
412 last_updated_by ,
413 last_update_date,
414 last_update_login,
415 request_id ,
416 program_application_id,
417 program_id ,
418 program_update_date )
419 SELECT IBE_MIGRATION_LOG_S1.nextval,
420 'IBE_MINISITE_MIGRATION',
421 'EVALUATE',
422 0,
423 a.quote_header_id,
424 a.org_id,
425 a.price_list_id,
426 a.party_type,
427 a.RECORD_TYPE,
428 a.msite1,
429 a.msite2,
430 a.qtype,
431 a.currency_code,
432 decode(p_override_minisite_flag, 'Y', 'Yes', 'No'),
433 fnd_global.user_id,
434 SYSDATE,
435 fnd_global.user_id ,
436 SYSDATE,
437 fnd_global.Conc_Login_id,
438 fnd_global.conc_request_id ,
439 fnd_global.prog_appl_id,
440 fnd_global.conc_program_id ,
441 SYSDATE
442 FROM
443 (SELECT qhdr.quote_header_id,
444 qhdr.org_id,
445 qhdr.price_list_id,
446 decode(qhdr.quote_source_code,'IStore Walkin','GUEST', hp.party_type) party_type,
447 decode(UPPER(SUBSTR(qhdr.quote_source_code,1,6)), 'ISTORE', 'cart', 'Quote')
448 RECORD_TYPE,
449 qhdr.minisite_id msite1,
450 qlin.minisite_id msite2,
451 decode(NVL(qlin.minisite_id,-999) , -999, NULL, 'LINE') qtype,
452 qhdr.currency_code,
453 RANK() OVER (PARTITION BY qhdr.quote_header_id ORDER BY qlin.quote_line_id ASC NULLS LAST) RANK
454 FROM aso_quote_headers_all QHDR,
455 aso_quote_lines_all QLIN,
456 hz_parties hp
457 WHERE qhdr.quote_header_id = qlin.quote_header_id(+)
458 AND (qhdr.quote_source_code like 'IStore%' or qhdr.publish_flag = 'Y')
459 AND qhdr.party_id = hp.party_id) a
460 where a.rank <= 1;
461 ELSE
462
463 INSERT INTO IBE_MIGRATION_LOG (
464 mglog_id,
465 migration_code ,
466 migration_mode ,
467 run_sequence,
468 attribute1,
469 attribute_idx1,
470 attribute_idx2,
471 attribute2,
472 attribute3,
473 attribute4,
474 attribute5,
475 attribute6,
476 attribute7,
477 attribute8,
478 created_by,
479 creation_date,
480 last_updated_by ,
481 last_update_date,
482 last_update_login,
483 request_id ,
484 program_application_id,
485 program_id ,
486 program_update_date )
487 SELECT IBE_MIGRATION_LOG_S1.nextval,
488 'IBE_MINISITE_MIGRATION',
489 'EVALUATE',
490 0,
491 a.quote_header_id,
492 a.org_id,
493 a.price_list_id,
494 a.party_type,
495 a.RECORD_TYPE,
496 a.msite1,
497 a.msite2,
498 a.qtype,
499 a.currency_code,
500 decode(p_override_minisite_flag, 'Y', 'Yes', 'No'),
501 fnd_global.user_id,
502 SYSDATE,
503 fnd_global.user_id ,
504 SYSDATE,
505 fnd_global.Conc_Login_id,
506 fnd_global.conc_request_id ,
507 fnd_global.prog_appl_id,
508 fnd_global.conc_program_id ,
509 SYSDATE
510 FROM
511 (SELECT qhdr.quote_header_id,
512 qhdr.org_id,
513 qhdr.price_list_id,
514 decode(qhdr.quote_source_code,'IStore Walkin','GUEST', hp.party_type) party_type,
515 decode(UPPER(SUBSTR(qhdr.quote_source_code,1,6)), 'ISTORE', 'cart', 'Quote')
516 RECORD_TYPE,
517 qhdr.minisite_id msite1,
518 qlin.minisite_id msite2,
519 decode(NVL(qlin.minisite_id,-999) , -999, NULL, 'LINE') qtype,
520 qhdr.currency_code,
521 RANK() OVER (PARTITION BY qhdr.quote_header_id ORDER BY qlin.quote_line_id ASC NULLS LAST) RANK
522 FROM aso_quote_headers_all QHDR,
523 aso_quote_lines_all QLIN,
524 hz_parties hp
525 WHERE qhdr.quote_header_id = qlin.quote_header_id(+)
526 AND (qhdr.quote_source_code like 'IStore%' or qhdr.publish_flag = 'Y')
527 AND qhdr.party_id = hp.party_id) a
528 where a.rank <= 1
529 AND a.msite1 IS NULL;
530
531 END IF;
532
533
534
535 ELSE
536 IF p_override_minisite_flag = 'Y' then
537
538 INSERT INTO IBE_MIGRATION_LOG
539 (mglog_id,
540 migration_code ,
541 migration_mode ,
542 run_sequence,
543 attribute1,
544 attribute_idx1,
545 attribute_idx2,
546 attribute2,
547 attribute3,
548 attribute4,
549 attribute5,
550 attribute7,
551 attribute8,
552 created_by,
553 creation_date,
554 last_updated_by ,
555 last_update_date,
556 last_update_login,
557 request_id ,
558 program_application_id,
559 program_id ,
560 program_update_date )
561 SELECT IBE_MIGRATION_LOG_S1.nextval,
562 'IBE_MINISITE_MIGRATION',
563 'EVALUATE',
564 0,
565 qhdr.quote_header_id,
566 qhdr.org_id,
567 qhdr.price_list_id,
568 decode(qhdr.quote_source_code, 'IStore Walkin', 'GUEST', hp.party_type) party_type,
569 decode(UPPER(SUBSTR(qhdr.quote_source_code,1,6)), 'ISTORE', 'Cart', 'Quote'),
570 null,
571 null,
572 qhdr.currency_code,
573 decode(p_override_minisite_flag, 'Y', 'Yes', 'No'),
574 fnd_global.user_id,
575 SYSDATE,
576 fnd_global.user_id ,
577 SYSDATE,
578 fnd_global.Conc_Login_id,
579 fnd_global.conc_request_id ,
580 fnd_global.prog_appl_id,
581 fnd_global.conc_program_id ,
582 SYSDATE
583 from aso_quote_headers_all qhdr,
584 hz_parties hp
585 where (qhdr.quote_source_code like 'IStore%' OR qhdr.publish_flag = 'Y')
586 AND qhdr.party_id = hp.party_id ;
587 ELSE
588
589 INSERT INTO IBE_MIGRATION_LOG
590 (mglog_id,
591 migration_code ,
592 migration_mode ,
593 run_sequence,
594 attribute1,
595 attribute_idx1,
596 attribute_idx2,
597 attribute2,
598 attribute3,
599 attribute4,
600 attribute5,
601 attribute7,
602 attribute8,
603 created_by,
604 creation_date,
605 last_updated_by ,
606 last_update_date,
607 last_update_login,
608 request_id ,
609 program_application_id,
610 program_id ,
611 program_update_date )
612 SELECT IBE_MIGRATION_LOG_S1.nextval,
613 'IBE_MINISITE_MIGRATION',
614 'EVALUATE',
615 0,
616 qhdr.quote_header_id,
617 qhdr.org_id,
618 qhdr.price_list_id,
619 decode(qhdr.quote_source_code, 'IStore Walkin', 'GUEST', hp.party_type) party_type,
620 decode(UPPER(SUBSTR(qhdr.quote_source_code,1,6)), 'ISTORE', 'Cart', 'Quote'),
621 null,
622 null,
623 qhdr.currency_code,
624 decode(p_override_minisite_flag, 'Y', 'Yes', 'No'),
625 fnd_global.user_id,
626 SYSDATE,
627 fnd_global.user_id ,
628 SYSDATE,
629 fnd_global.Conc_Login_id,
630 fnd_global.conc_request_id ,
631 fnd_global.prog_appl_id,
632 fnd_global.conc_program_id ,
633 SYSDATE
634 from aso_quote_headers_all qhdr,
635 hz_parties hp
636 where (qhdr.quote_source_code like 'IStore%' OR qhdr.publish_flag = 'Y')
637 AND qhdr.party_id = hp.party_id
638 AND qhdr.minisite_id IS NULL;
639 END IF;
640
641 END IF;
642
643 -- Check the count of records need to be processed and exit if no records to migrate
644
645 g_return_code := SQL%ROWCOUNT;
646 IF g_return_code =0 THEN
647 printLog(' No Records to migrate');
648 g_return_code := 0;
649 END IF;
650
651 COMMIT;
652
653 printLog('Procedure Load_temp_table : End');
654
655
656 EXCEPTION
657 WHEN OTHERS THEN
658 printLog('Procedure Load_temp_table : Exception '||sqlerrm);
659 g_return_code := 2;
660 RAISE;
661 END Load_temp_table;
662
663
664
665 -- ============================================================================
666 -- Procedure find_org_minisite_hits api to find hits of org to minisite mapping
667 -- ============================================================================
668 PROCEDURE find_org_minisite_hits
669 IS
670
671 l_msite_from_org NUMBER;
672
673 -- Get only ORG_IDs associated with a "SINGLE" minisite
674 CURSOR c_unique_orgs is
675 SELECT org_id
676 FROM
677 (SELECT distinct msite_id,
678 to_number(fnd_profile.value_specific('ORG_ID', -99999, responsibility_id, application_id)) org_id
679 FROM ibe_msite_resps_b)
680 WHERE org_id IS NOT NULL
681 GROUP BY org_id
682 HAVING count(*) = 1;
683
684 CURSOR c_msite_org(c_org_id NUMBER) IS
685 SELECT i.msite_id FROM ibe_msite_resps_b i
686 WHERE to_number(fnd_profile.value_specific('ORG_ID', -99999, responsibility_id, application_id)) = c_org_id;
687
688 BEGIN
689
690 printLog('Procedure find_org_minisite_hits : Start');
691
692 FOR crec in c_unique_orgs loop
693
694 OPEN c_msite_org(crec.org_id);
695 FETCH c_msite_org into l_msite_from_org;
696 CLOSE c_msite_org;
697
698 UPDATE ibe_migration_log
699 SET attribute6 = 'ORG',
700 attribute5 = l_msite_from_org
701 WHERE attribute5 is null
702 AND attribute_idx1 = crec.org_id
703 AND migration_code = 'IBE_MINISITE_MIGRATION'
704 AND migration_mode = 'EVALUATE'
705 AND run_sequence = 0;
706
707 END LOOP;
708
709 printLog('Procedure find_org_minisite_hits : End');
710
711
712 EXCEPTION
713 WHEN OTHERS THEN
714 printLog('Procedure Load_temp_table : Exception '||sqlerrm);
715 g_return_code := 2;
716 RAISE;
717 END Find_org_minisite_hits ;
718
719
720
721 --=====================================
722 -- Procedure find_unique_prclst_msites
723 -- ===================================
724
725
726
727
728 PROCEDURE find_unique_prclst_msites(p_party_type IN varchar2)
729 IS
730
731 l_msite_prof_check varchar(1);
732
733 CURSOR c_get_priclist(p_party_type varchar2) IS
734 SELECT decode (p_party_type, 'PERSON', registered_prc_listid,
735 'PARTY_RELATIONSHIP', bizpartner_prc_listid,
736 'GUEST', walkin_prc_listid) price_list_id,
737 msite_id,
738 currency_code
739 FROM ibe_msite_currencies
740 WHERE decode (p_party_type, 'PERSON',registered_prc_listid,
741 'PARTY_RELATIONSHIP', bizpartner_prc_listid,
742 'GUEST', walkin_prc_listid)
743 IN
744 (SELECT decode (p_party_type, 'PERSON',registered_prc_listid,
745 'PARTY_RELATIONSHIP', bizpartner_prc_listid,
746 'GUEST', walkin_prc_listid) price_list_id
747 FROM ibe_msite_currencies
748 GROUP BY decode (p_party_type, 'PERSON',registered_prc_listid,
749 'PARTY_RELATIONSHIP', bizpartner_prc_listid,
750 'GUEST', walkin_prc_listid)
751 HAVING count(*) = 1 );
752 BEGIN
753
754 printLog('Procedure Find_unique_prclst_msites: Start');
755
756 printLog('Procedure Find_unique_prclst_msites: p_party_type'||p_party_type);
757
758 -- Need to be updating based on PRICE_LIST_ID mapping in iStore New Merchant UI,
759 -- ONLY if the profile "IBE: Use Price list associated with Specialty Store" is
760 -- enabled at the "iStore" application level.
761
762 l_msite_prof_check := FND_PROFILE.VALUE_SPECIFIC('IBE_USE_MINISITE_PRICELIST',null,null,671);
763
764 IF l_msite_prof_check = 'Y' THEN
765
766 FOR crec in c_get_priclist(p_party_type) LOOP
767 UPDATE ibe_migration_log
768 SET attribute6 = 'PRICE',
769 attribute5 = crec.msite_id
770 WHERE attribute5 IS NULL
771 AND attribute_idx2 = crec.price_list_id
772 AND attribute2 = p_party_type
773 AND attribute7 = crec.currency_code
774 AND migration_code = 'IBE_MINISITE_MIGRATION'
775 AND migration_mode = 'EVALUATE'
776 AND run_sequence = 0;
777 END LOOP;
778 END IF;
779
780 printLog('Procedure Find_unique_prclst_msites: End');
781
782 EXCEPTION
783 WHEN OTHERS THEN
784 printLog('Procedure Find_unique_prclst_msites : Exception '||sqlerrm);
785 g_return_code := 2;
786 RAISE;
787 END Find_unique_prclst_msites;
788
789
790 --==================================
791 --Procedure find_price_list_hits
792 --==================================
793 PROCEDURE find_price_list_hits IS
794 BEGIN
795
796 printLog('Procedure find_price_list_hits : Start');
797
798 Find_unique_prclst_msites('GUEST');
799 Find_unique_prclst_msites('PERSON');
800 Find_unique_prclst_msites('PARTY_RELATIONSHIP');
801
802 printLog('Procedure find_price_list_hits : End');
803
804 EXCEPTION
805 WHEN OTHERS THEN
806 printLog('Procedure find_price_list_hits : Exception '||sqlerrm);
807 g_return_code := 2;
808 RAISE;
809 END Find_price_list_hits ;
810
811
812 --=========================================================
813 -- Procedure find_ue_org_hits - used to validate user input
814 -- =======================================================
815
816 --PROCEDURE find_ue_org_hits (p_mapping_tab IN org_mapping_table) IS
817 PROCEDURE find_ue_org_hits IS
818 BEGIN
819
820 printLog('Procedure find_ue_org_hits : Start');
821
822
823 FOR i in v_mapping_tab.FIRST .. v_mapping_tab.LAST LOOP
824 IF v_mapping_tab.EXISTS(i) THEN
825
826 UPDATE ibe_migration_log
827 SET attribute6 = 'MANUAL',
828 attribute5 = v_mapping_tab(i).minisite_id
829 WHERE attribute5 IS NULL
830 AND attribute_idx1 = v_mapping_tab(i).org_id
831 AND migration_code = 'IBE_MINISITE_MIGRATION'
832 AND migration_mode = 'EVALUATE'
833 AND run_sequence = 0;
834 END IF;
835
836 END LOOP;
837
838 printLog('Procedure find_ue_org_hits : End');
839
840 EXCEPTION
841 WHEN OTHERS THEN
842 printLog('Procedure find_ue_org_hits : Exception '||sqlerrm);
843 g_return_code := 2;
844 RAISE;
845 END find_ue_org_hits ;
846
847
848
849
850
851
852
853 --====================================================================
854 -- Procedure update_quote api to update aso_quote_headers_all
855 --====================================================================
856
857 PROCEDURE update_quote (p_batch_size IN NUMBER) IS
858 CURSOR LogCursor IS
859 SELECT to_number(attribute1) header_id,
860 to_number(attribute5) minisite
861 FROM ibe_migration_log
862 WHERE migration_code = 'IBE_MINISITE_MIGRATION'
863 AND migration_mode = 'EVALUATE'
864 AND run_sequence = 0;
865
866 TYPE MsiteTab IS TABLE OF ASO_QUOTE_HEADERS_ALL.MINISITE_ID%TYPE;
867 TYPE QuoteHeaderTab IS TABLE OF ASO_QUOTE_HEADERS_ALL.QUOTE_HEADER_ID%TYPE;
868
869 MsiteTable MsiteTab;
870 QuoteHeaderTable QuoteHeaderTab;
871 k Number :=0;
872
873 BEGIN
874
875 printLog('Procedure update_quote : Start');
876
877 OPEN LogCursor;
878 LOOP
879 FETCH LogCursor BULK COLLECT INTO QuoteHeaderTable,MsiteTable
880 LIMIT p_batch_size;
881
882 /** check if above cursor fetches no rows and then exit out of loop **/
883 IF QuoteHeaderTable.COUNT = 0 THEN
884 EXIT;
885 END IF;
886
887 FORALL i in QuoteHeaderTable.First..QuoteHeaderTable.Last
888 UPDATE aso_quote_headers_all
889 SET minisite_id = MsiteTable(i)
890 WHERE quote_header_id = QuoteHeaderTable(i);
891
892 EXIT WHEN LogCursor%NOTFOUND;
893 END LOOP;
894 CLOSE LogCursor;
895
896 printLog('Procedure update_quote : End');
897
898 EXCEPTION
899 WHEN OTHERS THEN
900 printLog('Procedure update_quote : Exception '||sqlerrm);
901 g_return_code := 2;
902 RAISE;
903 END update_quote;
904
905
906 --=====================================================================
907 --Procedure migration_status api to update ibe_migration_history
908 --====================================================================
909 PROCEDURE migration_status(p_status IN VARCHAR2) IS
910
911
912 BEGIN
913
914 printLog('Procedure Migration_status : Start');
915
916
917 UPDATE IBE_MIGRATION_HISTORY
918 SET STATUS = p_status,
919 LAST_UPDATE_DATE = SYSDATE
920 WHERE MIGRATION_CODE = 'IBE_MINISITE_MIGRATION';
921
922 IF (SQL%NOTFOUND) THEN
923 printLog('Procedure Migration_status :sqlnotfound');
924
925
926 INSERT INTO IBE_MIGRATION_HISTORY(MIGRATION_CODE,
927 OBJECT_VERSION_NUMBER,
928 CREATED_BY,
929 CREATION_DATE,
930 LAST_UPDATED_BY,
931 LAST_UPDATE_DATE,
932 STATUS)
933 VALUES('IBE_MINISITE_MIGRATION',
934 0,
935 FND_GLOBAL.user_id,
936 g_start_dt,
937 FND_GLOBAL.user_id,
938 SYSDATE,
939 p_status);
940
941
942
943 END IF;
944
945
946 printLog('Procedure Migration_status : End');
947
948 EXCEPTION
949 WHEN OTHERS THEN
950 printLog('Procedure Migration_status : Exception '||sqlerrm);
951 g_return_code := 2;
952 RAISE;
953 END migration_status;
954
955
956
957 -- =====================================================================
958 -- Procedure log_updated_rows
959 -- =====================================================================
960
961 PROCEDURE log_updated_rows
962 IS
963 l_next_sequence NUMBER ;
964 BEGIN
965
966 printLog('Procedure log_updated_rows : Start');
967
968 -- Delete all records which do not have the "NEW" minisite_id after applying the rules.
969
970 DELETE FROM ibe_migration_log
971 WHERE attribute5 IS NULL
972 AND migration_code = 'IBE_MINISITE_MIGRATION'
973 AND migration_mode = 'EVALUATE';
974
975 select ibe_migration_log_s2.nextval into l_next_sequence from dual ;
976
977 UPDATE ibe_migration_log
978 SET migration_mode = 'EXECUTE',
979 run_sequence = l_next_sequence
980 WHERE Migration_code = 'IBE_MINISITE_MIGRATION'
981 AND migration_mode = 'EVALUATE'
982 AND run_sequence = 0;
983
984 printLog('Procedure log_updated_rows : End');
985
986 EXCEPTION
987 WHEN OTHERS THEN
988 printLog('Procedure log_updated_rows : Exception '||sqlerrm);
989 g_return_code := 2;
990 RAISE;
991 END log_updated_rows;
992
993
994
995
996 -- ===========================================================
997 -- Procedure printReport
998 -- ===========================================================
999 PROCEDURE printReport(p_auto_defaulting_flag IN VARCHAR2,
1000 p_override_minisite_flag IN VARCHAR2,
1001 p_running_mode IN VARCHAR2,
1002 p_string_in IN VARCHAR2
1003 ) IS
1004 l_cnt_line number;
1005 l_cnt_price number;
1006 l_cnt_org number;
1007 l_cnt_manual number;
1008 l_tot_found number;
1009 l_tot_left number;
1010 l_temp_msg VARCHAR2(2000);
1011 l_temp_msg1 VARCHAR2(2000);
1012 l_temp_msg2 VARCHAR2(2000);
1013 l_temp_msg3 VARCHAR2(2000);
1014 l_temp_rule VARCHAR2(2000);
1015 l_temp_mode VARCHAR2(2000);
1016 l_temp_yesno VARCHAR2(200);
1017
1018 CURSOR c_get_carts_count IS
1019 SELECT count(mlog.attribute1) cnt_cart,ou.organization_id,ou.name
1020 FROM ibe_migration_log mlog, hr_operating_units ou
1021 WHERE attribute5 is null
1022 AND mlog.attribute_idx1= ou.organization_id
1023 GROUP BY mlog.attribute_idx1, ou.organization_id,ou.name;
1024
1025 CURSOR c_lookup(ptype IN VARCHAR2,pcode IN VARCHAR2) IS
1026 SELECT LookUp_Code,Meaning
1027 FROM Fnd_Lookups
1028 WHERE Lookup_Type = pType
1029 AND lookup_code = pcode;
1030
1031
1032 BEGIN
1033 SELECT sum(decode(attribute6,'LINE',1,0)) line,
1034 sum(decode(attribute6,'PRICE',1,0)) price,
1035 sum(decode(attribute6,'ORG',1,0)) org,
1036 sum(decode(attribute6,'MANUAL',1,0)) manual ,
1037 SUM(decode(attribute5,null,0,1)) ,
1038 sum(decode(attribute5,null,1,0))
1039 INTO l_cnt_line,
1040 l_cnt_price ,
1041 l_cnt_org,
1042 l_cnt_manual,
1043 l_tot_found,
1044 l_tot_left
1045 FROM ibe_migration_log
1046 WHERE Migration_code = 'IBE_MINISITE_MIGRATION'
1047 AND migration_mode = 'EVALUATE'
1048 AND run_sequence = 0;
1049
1050
1051
1052 fnd_message.set_name('IBE','IBE_BI_MIGRATION_REPORT');
1053 l_temp_msg := fnd_message.get;
1054 printOutput(l_temp_msg);
1055 printOutput(rpad('=',80,'='));
1056 printOutput(' ');
1057 fnd_message.set_name('IBE','IBE_BI_CONC_PROG_PARAMETERS');
1058 l_temp_msg := fnd_message.get;
1059 printOutput(l_temp_msg);
1060 printOutput(rpad('-',60,'-'));
1061 printOutput(' ');
1062
1063 fnd_message.set_name('IBE','IBE_BI_REPORT_PARAMETERS');
1064 l_temp_msg := fnd_message.get;
1065 fnd_message.set_name('IBE','IBE_BI_REPORT_PARAMETER_VALS');
1066 l_temp_msg1 := fnd_message.get;
1067 printOutput(RPAD(l_temp_msg ,45)|| ' ' || l_temp_msg1);
1068 printOutput(RPAD('-',45,'-')|| ' ' || lPAD('-',14,'-'));
1069
1070 fnd_message.set_name('IBE','IBE_BI_RUNNING_MODE');
1071 l_temp_msg := fnd_message.get;
1072 for rec_mode_lookup in c_lookup('IBE_BI_MIG_MODE',p_running_mode) loop
1073 l_temp_mode := rec_mode_lookup.meaning;
1074 end loop;
1075 printOutput( RPAD(l_temp_msg,45,' ') || ' ' || l_temp_mode);
1076
1077 fnd_message.set_name('IBE','IBE_BI_USE_MIG_RULES');
1078 l_temp_msg := fnd_message.get;
1079 for rec_rule_lookup in c_lookup('IBE_BI_MIG_RULE',p_auto_defaulting_flag) loop
1080 l_temp_rule := rec_rule_lookup.meaning;
1081 end loop;
1082 printOutput( RPAD(l_temp_msg,45,' ') || ' ' || l_temp_rule);
1083
1084 if p_override_minisite_flag = 'Y' then
1085 fnd_message.set_name('IBE','IBE_BI_UPD_CARTS_WITH_STORE_ID');
1086 l_temp_msg := fnd_message.get;
1087 for rec_yesno_lookup in c_lookup('YES_NO',p_override_minisite_flag) loop
1088 l_temp_yesno := rec_yesno_lookup.meaning;
1089 end loop;
1090 printOutput( RPAD(l_temp_msg,45,' ') || ' ' || l_temp_yesno);
1091 end if;
1092
1093 fnd_message.set_name('IBE','IBE_BI_ORG_STR_INPUT');
1094 l_temp_msg := fnd_message.get;
1095 printOutput( RPAD(l_temp_msg,45) || ' ' || nvl(p_string_in,'< >'));
1096 printOutput(' ');
1097
1098 fnd_message.set_name('IBE','IBE_BI_USER_ORG_STR_MAP');
1099 l_temp_msg := fnd_message.get;
1100 printOutput(l_temp_msg );
1101 printOutput(rpad('-',60,'-'));
1102 fnd_message.set_name('IBE','IBE_BI_ORGANIZATION_IDENTIFIER');
1103 l_temp_msg := fnd_message.get;
1104
1105 fnd_message.set_name('IBE','IBE_BI_ORGANIZATION_NAME');
1106 l_temp_msg1 := fnd_message.get;
1107
1108 fnd_message.set_name('IBE','IBE_BI_STORE_IDENTIFIER');
1109 l_temp_msg2 := fnd_message.get;
1110
1111 fnd_message.set_name('IBE','IBE_BI_STORE_NAME');
1112 l_temp_msg3 := fnd_message.get;
1113
1114 if v_mapping_tab.first is not null then
1115
1116 printOutput(RPAD(l_temp_msg ,20) || ' ' || RPAD(l_temp_msg1 ,20) || ' ' || RPAD(l_temp_msg2 ,20) || l_temp_msg3);
1117 printOutput(RPAD('-',20,'-')||' '||RPAD('-',20,'-')||' ' ||RPAD('-',20,'-') || ' ' || lPAD('-',20,'-'));
1118
1119 FOR t in v_mapping_tab.first ..v_mapping_tab.last LOOP
1120 IF v_mapping_tab.EXISTS(t) THEN
1121
1122 printOutput( rpad(TO_CHAR(v_mapping_tab(t).org_id), 20,' ') || ' ' || rpad(v_mapping_tab(t).org_name,20,' ') || ' ' ||
1123 rpad(TO_CHAR(v_mapping_tab(t).minisite_id), 20,' ')
1124 || ' ' || v_mapping_tab(t).minisite_name);
1125
1126
1127 END IF;
1128
1129 END LOOP;
1130 end if;
1131 printOutput(' ');
1132 printOutput(' ');
1133 fnd_message.set_name('IBE','IBE_BI_MIGRATION_SUMMARY');
1134 l_temp_msg := fnd_message.get;
1135 printOutput(l_temp_msg);
1136 printOutput(rpad('=',80,'='));
1137 printOutput(' ');
1138
1139 fnd_message.set_name('IBE','IBE_BI_NO_OF_CARTS_FOUND');
1140 l_temp_msg := fnd_message.get;
1141 printOutput(l_temp_msg ||' '|| nvl(l_tot_found,0));
1142 fnd_message.set_name('IBE','IBE_BI_NO_OF_CARTS_LEFT');
1143 l_temp_msg := fnd_message.get;
1144 printOutput(l_temp_msg || ' ' ||nvl(l_tot_left,0));
1145 printOutput(' ');
1146
1147
1148 fnd_message.set_name('IBE','IBE_BI_METHOD_TO_FIND_STR');
1149 l_temp_msg := fnd_message.get;
1150 fnd_message.set_name('IBE','IBE_BI_NO_OF_CARTS');
1151 l_temp_msg1 := fnd_message.get;
1152
1153
1154 printOutput(RPAD(l_temp_msg,45,' ') || ' ' || l_temp_msg1);
1155 printOutput(RPAD('-',45,'-') || ' ' || LPAD('-',14,'-'));
1156 fnd_message.set_name('IBE','IBE_BI_MIGRATION_LINE');
1157 l_temp_msg1 := fnd_message.get;
1158 printOutput(RPAD(l_temp_msg1,45,' ') || ' ' || nvl(l_cnt_line,0));
1159 fnd_message.set_name('IBE','IBE_BI_MIGRATION_ORG');
1160 l_temp_msg1 := fnd_message.get;
1161 printOutput(RPAD(l_temp_msg1,45,' ') || ' ' || nvl(l_cnt_org,0));
1162 fnd_message.set_name('IBE','IBE_BI_MIGRATION_PRICE');
1163 l_temp_msg1 := fnd_message.get;
1164 printOutput(RPAD(l_temp_msg1,45,' ') || ' ' || nvl(l_cnt_price,0));
1165 fnd_message.set_name('IBE','IBE_BI_MIGRATION_MANUAL');
1166 l_temp_msg1 := fnd_message.get;
1167 printOutput(RPAD(l_temp_msg1,45,' ') || ' ' || nvl(l_cnt_manual,0));
1168 printOutput(' ');
1169 printOutput(' ');
1170
1171 fnd_message.set_name('IBE','IBE_BI_EXCEPTION_REPORT');
1172 l_temp_msg := fnd_message.get;
1173 printOutput(l_temp_msg );
1174 printOutput(rpad('=',80,'='));
1175 printOutput(' ');
1176 fnd_message.set_name('IBE','IBE_BI_EXCEPTION_REPORT_DETAIL');
1177 l_temp_msg := fnd_message.get;
1178 printOutput(l_temp_msg );
1179 printOutput(rpad('-',60,'-'));
1180 printOutput(' ');
1181
1182
1183 fnd_message.set_name('IBE','IBE_BI_NO_OF_CARTS');
1184 l_temp_msg := fnd_message.get;
1185 fnd_message.set_name('IBE','IBE_BI_ORGANIZATION_IDENTIFIER');
1186 l_temp_msg1 := fnd_message.get;
1187 fnd_message.set_name('IBE','IBE_BI_ORGANIZATION_NAME');
1188 l_temp_msg2 := fnd_message.get;
1189
1190 printOutput(RPAD(l_temp_msg ,20,' ') || ' ' || RPAD(l_temp_msg1,25,' ')||' '||l_temp_msg2);
1191 printOutput(RPAD('-',20,'-') || ' '||RPAD('-',25,'-') || ' ' || lPAD('-',33,'-'));
1192
1193 FOR crec IN c_get_carts_count LOOP
1194 printOutput( RPAD(crec.cnt_cart,20,' ') || ' '|| RPAD(crec.organization_id,25,' ') || ' ' || crec.name);
1195 END LOOP;
1196
1197 printOutput(' ');
1198 printOutput(' ');
1199
1200 EXCEPTION
1201
1202 WHEN OTHERS THEN
1203 printLog('Procedure printreport : Exception '||sqlerrm);
1204 g_return_code := 2;
1205 RAISE;
1206 END printReport;
1207
1208
1209
1210
1211
1212 --===========================================================
1213 -- Procedure print_org_store_identifiers
1214 --===========================================================
1215
1216 PROCEDURE print_org_store_identifiers IS
1217
1218 l_temp_msg VARCHAR2(2000);
1219 l_temp_msg1 VARCHAR2(2000);
1220
1221
1222
1223 CURSOR c_valid_orgs is
1224 SELECT organization_id, name FROM hr_operating_units OU
1225 WHERE exists (SELECT qhdr.org_id FROM aso_quote_headers_all qhdr
1226 WHERE ou.organization_id = qhdr.org_id);
1227
1228 CURSOR c_valid_str is
1229 SELECT distinct MSITE_ID,
1230 msite_name
1231 FROM ibe_msites_vl
1232 WHERE msite_id <> 1
1233 AND site_type= 'I'; -- Changed as per the Bug # 4394901
1234
1235 BEGIN
1236
1237
1238 fnd_message.set_name('IBE','IBE_BI_ORG_STR_IDENTIFIER');
1239 l_temp_msg := fnd_message.get;
1240 printOutput(l_temp_msg);
1241 printOutput(RPAD('=' ,80 , '='));
1242 printOutput(' ');
1243 fnd_message.set_name('IBE','IBE_BI_ORGANIZATION_IDENTIFIER');
1244 l_temp_msg := fnd_message.get;
1245 fnd_message.set_name('IBE','IBE_BI_ORGANIZATION_NAME');
1246 l_temp_msg1 := fnd_message.get;
1247
1248
1249
1250
1251 printOutput(RPAD(l_temp_msg1,45,' ') || ' ' || l_temp_msg);
1252 printOutput(RPAD('-',45,'-') || ' ' ||lPAD('-',24,'-') );
1253
1254 FOR r_valid_orgs in c_valid_orgs LOOP
1255 printOutput(rpad(r_valid_orgs.name,45)|| ' ' || r_valid_orgs.organization_id);
1256 END LOOP;
1257
1258
1259 printOutput(' ');
1260 fnd_message.set_name('IBE','IBE_BI_STORE_IDENTIFIER');
1261 l_temp_msg := fnd_message.get;
1262 fnd_message.set_name('IBE','IBE_BI_STORE_NAME');
1263 l_temp_msg1 := fnd_message.get;
1264
1265 printOutput(RPAD(l_temp_msg1,45,' ') || ' ' || l_temp_msg);
1266 printOutput(RPAD('-',45,'-') || ' ' ||lPAD('-',24,'-') );
1267
1268 FOR r_valid_str in c_valid_str LOOP
1269 printOutput(rpad(r_valid_str.msite_name,45)|| ' '|| r_valid_str.msite_id);
1270 END LOOP;
1271
1272 EXCEPTION
1273
1274 WHEN OTHERS THEN
1275 printLog('Error in print_org_store_identifiers '||sqlerrm );
1276 g_return_code := 2;
1277 RAISE;
1278
1279 END print_org_store_identifiers;
1280
1281
1282
1283
1284
1285
1286 --=====================================================================
1287 --Procedure "Run_migration" : Main api called from concurrent executable
1288 --=====================================================================
1289 PROCEDURE run_migration(errbuf OUT NOCOPY VARCHAR2,
1290 retcode OUT NOCOPY VARCHAR2,
1291 p_auto_defaulting_flag IN VARCHAR2 ,
1292 p_override_minisite_flag IN VARCHAR2,
1293 p_running_mode IN VARCHAR2,
1294 p_string_in IN VARCHAR2,
1295 p_batch_size IN NUMBER)
1296 IS
1297 l_status VARCHAR2(1);
1298 l_exit_flag VARCHAR2(1);
1299 user_input_null EXCEPTION;
1300
1301
1302 BEGIN
1303
1304 printLog('Proc Run_migration : Begin');
1305 printLog('Logging In Values... start');
1306 printLog ('Defaulting Flag :'|| p_auto_defaulting_flag);
1307 printLog ('Over Ride Minisite Flag :'|| p_override_minisite_flag);
1308 printLog ('Running Mode :'|| p_running_mode);
1309 printLog ('In String :'|| p_string_in);
1310 printLog('Logging In Values... end ');
1311
1312 l_exit_flag := 'N';
1313
1314 IF p_running_mode = 'GET_ORG_STR_MAP' THEN
1315 print_org_store_identifiers;
1316 retcode := 0;
1317 l_exit_flag := 'Y';
1318 END IF;
1319
1320 --Parse the entered org to minisite mapping
1321 IF l_exit_flag = 'N' THEN -- continue with the procerssing only if the running mode is not "GET_ORG_STR_MAP"
1322
1323 IF p_auto_defaulting_flag = '2' or p_auto_defaulting_flag = '3' THEN
1324 IF p_string_in is NULL THEN
1325 -- retcode := 1;
1326 -- l_exit_flag := 'Y';
1327 RAISE user_input_null;
1328 --EXIT;
1329 END IF;
1330 END IF;
1331
1332 parse_org_minisite_mapping (p_string_in, ',' );
1333 Validate_params;
1334
1335
1336 --clean the temporary log table before starting this run - just to make sure we start fresh
1337
1338
1339 Clean_log_table;
1340
1341
1342 --To load temporary table containg all the quote that needs to updated,
1343 --it will be used during the session of current run, to track any changes.
1344
1345
1346 Load_temp_table(p_override_minisite_flag ,p_auto_defaulting_flag);
1347
1348 IF g_return_code > 0 then
1349
1350 -- find hits using automatic defaulting logic
1351
1352 IF p_auto_defaulting_flag = '1' OR p_auto_defaulting_flag = '3' THEN
1353 find_org_minisite_hits;
1354 find_price_list_hits;
1355 END IF ;
1356
1357
1358 -- find hits using the manual org to minisite mapping
1359
1360 IF p_auto_defaulting_flag = '2' OR p_auto_defaulting_flag = '3' THEN
1361
1362 find_ue_org_hits;
1363 END IF;
1364
1365 --if user has selected only report only (evaluation) mode then do not call update_quote
1366 printLog('running mode is ....'||p_running_mode);
1367 IF p_running_mode = 'EXECUTE' THEN
1368 printLog('Calling update quote');
1369 Update_quote(p_batch_size);
1370 --log_updated_rows;
1371 migration_status('SUCCESS');
1372
1373 ENd IF ;
1374
1375 -- print report of hit ratio for all the carts
1376 END IF;
1377 printReport(p_auto_defaulting_flag,
1378 p_override_minisite_flag,
1379 p_running_mode,
1380 p_string_in);
1381
1382 print_org_store_identifiers();
1383
1384 IF p_running_mode = 'EXECUTE' THEN
1385 log_updated_rows;
1386 END IF;
1387
1388 IF p_running_mode = 'EVALUATE' THEN
1389 -- clean the temporary log table after reporting
1390 DELETE FROM ibe_migration_log
1391 WHERE Migration_code = 'IBE_MINISITE_MIGRATION'
1392 AND migration_mode = 'EVALUATE'
1393 AND run_sequence = 0
1394 AND attribute5 IS null;
1395
1396 END IF ;
1397 END IF ;
1398
1399 COMMIT;
1400 retcode := 0;
1401 errbuf := 'SUCCESS';
1402 printLog('retcode is :'||retcode);
1403 printLog('errbuf is :'|| errbuf);
1404
1405 printLog('Proc Run Migration : End');
1406
1407 EXCEPTION
1408 WHEN user_input_null THEN
1409 ROLLBACK;
1410 printLog('Proc Run Migration :'||'User Org:Store Input Can Not Be Null In EVALUATE MODE');
1411 printLog('SQL ERROR :'||SQLCODE||'-'||SQLERRM);
1412 retcode := -1;
1413
1414 -- RAISE;
1415 WHEN OTHERS THEN
1416 IF g_return_code = 0 THEN
1417 retcode := 0;
1418 ELSE
1419 ROLLBACK;
1420 retcode := -1;
1421 END IF;
1422
1423 printLog('Proc Run Migration'||' '||SQLCODE||'-'||SQLERRM);
1424 errbuf := ' errbuf' || ' '||SQLCODE||'-'||SQLERRM;
1425 printLog('retcode is :'||retcode || 'errbuf is :'|| errbuf);
1426
1427 END run_migration;
1428
1429 END IBE_BI_STR_MIG_PVT;