242: * Now we do summarization for all the organizations in one go*/
243: IF (p_collect_type = 1) OR (p_collect_type = 3) THEN
244: msc_util.msc_log('Sales Order, should be full collection');
245:
246: INSERT INTO MSC_TEMP_SUMM_SO (
247: organization_id,
248: inventory_item_id,
249: demand_class,
250: sd_date,
521: IF (P_COLLECT_TYPE = 1) OR (P_COLLECT_TYPE = 3) THEN
522: msc_util.msc_log('Swap partition for Slaes Orders. Only for full collection');
523: ---- Create index on MSC_ATP_SUMMARY_SO
524: BEGIN
525: msc_util.msc_log('Create index on MSC_TEMP_SUMM_SO');
526: l_sql_stmt_1 := 'create UNIQUE index MSC_TEMP_SUMM_SO_N1 on MSC_TEMP_SUMM_SO ' ||
527: '
528: -- NOLOGGING
529: (sr_instance_id, organization_id, inventory_item_id, sd_date, demand_class)
522: msc_util.msc_log('Swap partition for Slaes Orders. Only for full collection');
523: ---- Create index on MSC_ATP_SUMMARY_SO
524: BEGIN
525: msc_util.msc_log('Create index on MSC_TEMP_SUMM_SO');
526: l_sql_stmt_1 := 'create UNIQUE index MSC_TEMP_SUMM_SO_N1 on MSC_TEMP_SUMM_SO ' ||
527: '
528: -- NOLOGGING
529: (sr_instance_id, organization_id, inventory_item_id, sd_date, demand_class)
530: storage(INITIAL 40K NEXT 2M PCTINCREASE 0)'; --tablespace ' || l_tbspace(i);
532: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
533: APPLICATION_SHORT_NAME => 'MSC',
534: STATEMENT_TYPE => ad_ddl.create_index,
535: STATEMENT => l_sql_stmt_1,
536: OBJECT_NAME => 'MSC_TEMP_SUMM_SO');
537: END;
538: --analyze temp table
539: fnd_stats.gather_table_stats(l_msc_schema, 'MSC_TEMP_SUMM_SO', granularity => 'ALL');
540:
535: STATEMENT => l_sql_stmt_1,
536: OBJECT_NAME => 'MSC_TEMP_SUMM_SO');
537: END;
538: --analyze temp table
539: fnd_stats.gather_table_stats(l_msc_schema, 'MSC_TEMP_SUMM_SO', granularity => 'ALL');
540:
541: l_partition_name := 'ATP_SUMMARY_SO__' || to_char(p_instance_id);
542: msc_util.msc_log('Sales order partition name := ' || l_partition_name);
543: l_sql_stmt := 'ALTER TABLE MSC_ATP_SUMMARY_SO exchange partition ' || l_partition_name ||
540:
541: l_partition_name := 'ATP_SUMMARY_SO__' || to_char(p_instance_id);
542: msc_util.msc_log('Sales order partition name := ' || l_partition_name);
543: l_sql_stmt := 'ALTER TABLE MSC_ATP_SUMMARY_SO exchange partition ' || l_partition_name ||
544: ' with table MSC_TEMP_SUMM_SO'||
545: ' including indexes without validation';
546:
547: BEGIN
548: msc_util.msc_log('Before alter table MSC_ATP_SUMMARY_SO: ');
1590: l_sql_stmt varchar2(300);
1591: BEGIN
1592: --- clean up the tables
1593: msc_util.msc_log('Inside clean_tables_procedure ');
1594: l_sql_stmt := 'TRUNCATE TABLE MSC_TEMP_SUMM_SO';
1595:
1596: BEGIN
1597: msc_util.msc_log('Tuncate Table MSC_TEMP_SUMM_SO');
1598: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
1593: msc_util.msc_log('Inside clean_tables_procedure ');
1594: l_sql_stmt := 'TRUNCATE TABLE MSC_TEMP_SUMM_SO';
1595:
1596: BEGIN
1597: msc_util.msc_log('Tuncate Table MSC_TEMP_SUMM_SO');
1598: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
1599: APPLICATION_SHORT_NAME => 'MSC',
1600: STATEMENT_TYPE => ad_ddl.truncate_table,
1601: STATEMENT => l_sql_stmt,
1598: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
1599: APPLICATION_SHORT_NAME => 'MSC',
1600: STATEMENT_TYPE => ad_ddl.truncate_table,
1601: STATEMENT => l_sql_stmt,
1602: OBJECT_NAME => 'MSC_TEMP_SUMM_SO');
1603: EXCEPTION
1604: WHEN OTHERS THEN
1605: msc_util.msc_log(sqlerrm);
1606: msc_util.msc_log('Truncate table MSC_TEMP_SUMM_SO failed');
1602: OBJECT_NAME => 'MSC_TEMP_SUMM_SO');
1603: EXCEPTION
1604: WHEN OTHERS THEN
1605: msc_util.msc_log(sqlerrm);
1606: msc_util.msc_log('Truncate table MSC_TEMP_SUMM_SO failed');
1607: END;
1608: l_sql_stmt := 'TRUNCATE TABLE MSC_TEMP_SUMM_SD';
1609:
1610: BEGIN
1618: WHEN OTHERS THEN
1619: msc_util.msc_log(sqlerrm);
1620: msc_util.msc_log('Truncate table MSC_TEMP_SUMM_SD failed');
1621: END;
1622: l_sql_stmt := 'DROP INDEX MSC_TEMP_SUMM_SO_N1';
1623:
1624: BEGIN
1625: msc_util.msc_log('Drop Index MSC_TEMP_SUMM_SO_N1');
1626: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
1621: END;
1622: l_sql_stmt := 'DROP INDEX MSC_TEMP_SUMM_SO_N1';
1623:
1624: BEGIN
1625: msc_util.msc_log('Drop Index MSC_TEMP_SUMM_SO_N1');
1626: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
1627: APPLICATION_SHORT_NAME => 'MSC',
1628: STATEMENT_TYPE => ad_ddl.drop_index,
1629: STATEMENT => l_sql_stmt,
1626: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
1627: APPLICATION_SHORT_NAME => 'MSC',
1628: STATEMENT_TYPE => ad_ddl.drop_index,
1629: STATEMENT => l_sql_stmt,
1630: OBJECT_NAME => 'MSC_TEMP_SUMM_SO_N1');
1631: EXCEPTION
1632: WHEN OTHERS THEN
1633: msc_util.msc_log(sqlerrm);
1634: msc_util.msc_log('Drop Index MSC_TEMP_SUMM_SO_N1 failed');
1630: OBJECT_NAME => 'MSC_TEMP_SUMM_SO_N1');
1631: EXCEPTION
1632: WHEN OTHERS THEN
1633: msc_util.msc_log(sqlerrm);
1634: msc_util.msc_log('Drop Index MSC_TEMP_SUMM_SO_N1 failed');
1635: END;
1636:
1637: l_sql_stmt := 'DROP INDEX MSC_TEMP_SUMM_SD_N1';
1638: