[Home] [Help]
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,
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,
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
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;
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;
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);
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)
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)
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)
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:
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: */
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;