[Home] [Help]
123: -- Bug 3304390 Disable Trace
124: -- Deleted Related code.
125:
126: l_inv_ctp := NVL(FND_PROFILE.value('INV_CTP'), 5);
127: msc_util.msc_log('inv_ctp := ' || l_inv_ctp);
128:
129: l_enable_summary_mode := NVL(FND_PROFILE.value('MSC_ENABLE_ATP_SUMMARY'), 'N');
130: msc_util.msc_log(' l_enable_summary_mode := ' || l_enable_summary_mode);
131: IF l_enable_summary_mode <> 'Y' THEN
126: l_inv_ctp := NVL(FND_PROFILE.value('INV_CTP'), 5);
127: msc_util.msc_log('inv_ctp := ' || l_inv_ctp);
128:
129: l_enable_summary_mode := NVL(FND_PROFILE.value('MSC_ENABLE_ATP_SUMMARY'), 'N');
130: msc_util.msc_log(' l_enable_summary_mode := ' || l_enable_summary_mode);
131: IF l_enable_summary_mode <> 'Y' THEN
132: msc_util.msc_log('Summary Mode is not enabled. Please enable Summary mode to run this program');
133: RETCODE := G_WARNING;
134: RETURN;
128:
129: l_enable_summary_mode := NVL(FND_PROFILE.value('MSC_ENABLE_ATP_SUMMARY'), 'N');
130: msc_util.msc_log(' l_enable_summary_mode := ' || l_enable_summary_mode);
131: IF l_enable_summary_mode <> 'Y' THEN
132: msc_util.msc_log('Summary Mode is not enabled. Please enable Summary mode to run this program');
133: RETCODE := G_WARNING;
134: RETURN;
135: END IF;
136:
135: END IF;
136:
137: IF l_inv_ctp = 4 THEN
138: -- we are not doing ODS ATP so we wont continue
139: msc_util.msc_log('Not Doing ODS ATP. Please check profile - INV: Capable to Promise. Will Exit ');
140: RETCODE := G_WARNING;
141: RETURN;
142: ELSIF l_inv_ctp <> 5 THEN
143: l_inv_ctp := 5;
150: into l_summary_flag, l_instance_code
151: FROM msc_apps_instances
152: where instance_id = p_instance_id;
153:
154: msc_util.msc_log('l_summary_flag := ' || l_summary_flag);
155: -- 2301524: Summary is not supported for sites using backlog workbench
156: IF l_summary_flag = 200 THEN
157: msc_util.msc_log('Site is Using backlog workbench');
158: msc_util.msc_log('Summary Approach is not supported for sites using backlog workbench');
153:
154: msc_util.msc_log('l_summary_flag := ' || l_summary_flag);
155: -- 2301524: Summary is not supported for sites using backlog workbench
156: IF l_summary_flag = 200 THEN
157: msc_util.msc_log('Site is Using backlog workbench');
158: msc_util.msc_log('Summary Approach is not supported for sites using backlog workbench');
159: RETCODE := G_WARNING;
160: RETURN;
161: ELSIF l_summary_flag = 2 THEN
154: msc_util.msc_log('l_summary_flag := ' || l_summary_flag);
155: -- 2301524: Summary is not supported for sites using backlog workbench
156: IF l_summary_flag = 200 THEN
157: msc_util.msc_log('Site is Using backlog workbench');
158: msc_util.msc_log('Summary Approach is not supported for sites using backlog workbench');
159: RETCODE := G_WARNING;
160: RETURN;
161: ELSIF l_summary_flag = 2 THEN
162: msc_util.msc_log('Full summary is in progress for the same instance by other session');
158: msc_util.msc_log('Summary Approach is not supported for sites using backlog workbench');
159: RETCODE := G_WARNING;
160: RETURN;
161: ELSIF l_summary_flag = 2 THEN
162: msc_util.msc_log('Full summary is in progress for the same instance by other session');
163: RETCODE := G_ERROR;
164: RETURN;
165: ELSIF ((P_COLLECT_TYPE = 1) OR (P_COLLECT_TYPE = 2)) and (NVL(l_summary_flag, 1) <> 3) THEN
166: msc_util.msc_log('Tables have not been succefully summarized. Net Change/ Targeted summarization'
162: msc_util.msc_log('Full summary is in progress for the same instance by other session');
163: RETCODE := G_ERROR;
164: RETURN;
165: ELSIF ((P_COLLECT_TYPE = 1) OR (P_COLLECT_TYPE = 2)) and (NVL(l_summary_flag, 1) <> 3) THEN
166: msc_util.msc_log('Tables have not been succefully summarized. Net Change/ Targeted summarization'
167: || ' can not be run without sucessfully running complete summarization ');
168: RETCODE := G_ERROR;
169: RETURN;
170: END IF;
168: RETCODE := G_ERROR;
169: RETURN;
170: END IF;
171:
172: msc_util.msc_log('sr_instance_id := ' || P_INSTANCE_ID);
173: msc_util.msc_log('Collection _type := ' || P_COLLECT_TYPE);
174: RETCODE := G_SUCCESS;
175: msc_util.msc_log('Begin Post Processing');
176: l_retval := FND_INSTALLATION.GET_APP_INFO('FND', dummy1, dummy2, l_applsys_schema);
169: RETURN;
170: END IF;
171:
172: msc_util.msc_log('sr_instance_id := ' || P_INSTANCE_ID);
173: msc_util.msc_log('Collection _type := ' || P_COLLECT_TYPE);
174: RETCODE := G_SUCCESS;
175: msc_util.msc_log('Begin Post Processing');
176: l_retval := FND_INSTALLATION.GET_APP_INFO('FND', dummy1, dummy2, l_applsys_schema);
177: SELECT a.oracle_username
171:
172: msc_util.msc_log('sr_instance_id := ' || P_INSTANCE_ID);
173: msc_util.msc_log('Collection _type := ' || P_COLLECT_TYPE);
174: RETCODE := G_SUCCESS;
175: msc_util.msc_log('Begin Post Processing');
176: l_retval := FND_INSTALLATION.GET_APP_INFO('FND', dummy1, dummy2, l_applsys_schema);
177: SELECT a.oracle_username
178: INTO l_msc_schema
179: FROM FND_ORACLE_USERID a,
180: FND_PRODUCT_INSTALLATIONS b
181: WHERE a.oracle_id = b.oracle_id
182: AND b.application_id = 724;
183:
184: msc_util.msc_log('l_applsys_schema ;= ' || l_applsys_schema);
185: msc_util.msc_log('dummy1 := ' || dummy1);
186: msc_util.msc_log('dummy2 := ' || dummy2);
187: msc_util.msc_log('l_msc_schema := ' || l_msc_schema);
188:
181: WHERE a.oracle_id = b.oracle_id
182: AND b.application_id = 724;
183:
184: msc_util.msc_log('l_applsys_schema ;= ' || l_applsys_schema);
185: msc_util.msc_log('dummy1 := ' || dummy1);
186: msc_util.msc_log('dummy2 := ' || dummy2);
187: msc_util.msc_log('l_msc_schema := ' || l_msc_schema);
188:
189: --check whether the partitions exist in tables or not. In not then error out
182: AND b.application_id = 724;
183:
184: msc_util.msc_log('l_applsys_schema ;= ' || l_applsys_schema);
185: msc_util.msc_log('dummy1 := ' || dummy1);
186: msc_util.msc_log('dummy2 := ' || dummy2);
187: msc_util.msc_log('l_msc_schema := ' || l_msc_schema);
188:
189: --check whether the partitions exist in tables or not. In not then error out
190:
183:
184: msc_util.msc_log('l_applsys_schema ;= ' || l_applsys_schema);
185: msc_util.msc_log('dummy1 := ' || dummy1);
186: msc_util.msc_log('dummy2 := ' || dummy2);
187: msc_util.msc_log('l_msc_schema := ' || l_msc_schema);
188:
189: --check whether the partitions exist in tables or not. In not then error out
190:
191: IF (P_COLLECT_TYPE = 3) THEN
204: AND PARTITION_NAME = l_partition_name
205: AND table_owner = l_msc_schema;
206: EXCEPTION
207: WHEN OTHERS THEN
208: msc_util.msc_log('Inside Exception');
209: l_count := 0;
210: END;
211: IF (l_count = 0) THEN
212: -- Bug 2516506
212: -- Bug 2516506
213: FND_MESSAGE.SET_NAME('MSC', 'MSC_ATP_INS_PARTITION_MISSING');
214: FND_MESSAGE.SET_TOKEN('INSTANCE_CODE', l_instance_code);
215: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'MSC_' || atp_summ_tab(i));
216: msc_util.msc_log(FND_MESSAGE.GET);
217: RETCODE := G_ERROR;
218: RETURN;
219: END IF;
220: END LOOP;
234: END IF;
235:
236: l_sys_date := sysdate;
237: l_user_id := FND_GLOBAL.USER_ID;
238: msc_util.msc_log('l_sys_date := ' || l_sys_date);
239: msc_util.msc_log('l_user_id := ' || l_user_id);
240:
241: /* rajjain 02/17/2003 GOP Performance Improvement - ODS Summary changes begin
242: * Now we do summarization for all the organizations in one go*/
235:
236: l_sys_date := sysdate;
237: l_user_id := FND_GLOBAL.USER_ID;
238: msc_util.msc_log('l_sys_date := ' || l_sys_date);
239: msc_util.msc_log('l_user_id := ' || l_user_id);
240:
241: /* rajjain 02/17/2003 GOP Performance Improvement - ODS Summary changes begin
242: * Now we do summarization for all the organizations in one go*/
243: IF (p_collect_type = 1) OR (p_collect_type = 3) THEN
240:
241: /* rajjain 02/17/2003 GOP Performance Improvement - ODS Summary changes begin
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,
331: GROUP BY so.inventory_item_id, so.organization_id, so.demand_class,
332: so.sd_date, -1, p_instance_id, l_sys_date, l_user_id);
333:
334: END IF;
335: msc_util.msc_log('Load SD details in msc_atp_summary_sd table');
336:
337: IF (P_COLLECT_TYPE= 2 ) OR (P_COLLECT_TYPE= 3 ) THEN
338: msc_util.msc_log('Load SD Details, ODS Case');
339:
334: END IF;
335: msc_util.msc_log('Load SD details in msc_atp_summary_sd table');
336:
337: IF (P_COLLECT_TYPE= 2 ) OR (P_COLLECT_TYPE= 3 ) THEN
338: msc_util.msc_log('Load SD Details, ODS Case');
339:
340: INSERT INTO MSC_TEMP_SUMM_SD (
341: organization_id,
342: inventory_item_id,
518: -- rajjain 02/17/2003 GOP Performance Improvement - ODS Summary changes end
519:
520: ---exchange partition
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 ' ||
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)
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);
531: msc_util.msc_log('Before create index on MSC_atp_summary_so: ');
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,
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 ||
544: ' with table MSC_TEMP_SUMM_SO'||
545: ' including indexes without validation';
546:
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: ');
549: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
550: APPLICATION_SHORT_NAME => 'MSC',
551: STATEMENT_TYPE => ad_ddl.alter_table,
552: STATEMENT => l_sql_stmt,
555: END IF;
556:
557: IF (P_COLLECT_TYPE = 2) OR (P_COLLECT_TYPE = 3) THEN
558: ----swap partiton for supplies and demand part
559: msc_util.msc_log('swap partition for supply-demand');
560: BEGIN
561: l_sql_stmt_1 := 'create unique index MSC_TEMP_SUMM_SD_N1 on MSC_TEMP_SUMM_SD ' ||
562: '
563: -- NOLOGGING
562: '
563: -- NOLOGGING
564: (plan_id, sr_instance_id, organization_id,inventory_item_id,sd_date, demand_class)
565: storage(INITIAL 40K NEXT 2M PCTINCREASE 0)'; --tablespace ' || l_tbspace(i);
566: msc_util.msc_log('Before create index on MSC_atp_summary_sd: ');
567: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
568: APPLICATION_SHORT_NAME => 'MSC',
569: STATEMENT_TYPE => ad_ddl.create_index,
570: STATEMENT => l_sql_stmt_1,
574: fnd_stats.gather_table_stats(l_msc_schema, 'MSC_TEMP_SUMM_SD', granularity => 'ALL');
575:
576: l_partition_name := 'ATP_SUMMARY_SD__' || to_char(p_instance_id) ;
577:
578: msc_util.msc_log('Partition name for msc_atp_summary table sd part := ' || l_partition_name);
579:
580: l_sql_stmt := 'ALTER TABLE MSC_ATP_SUMMARY_SD exchange partition ' || l_partition_name ||
581: ' with table MSC_TEMP_SUMM_SD'||
582: ' including indexes without validation';
581: ' with table MSC_TEMP_SUMM_SD'||
582: ' including indexes without validation';
583:
584: BEGIN
585: msc_util.msc_log('Before alter table MSC_ATP_SUMMARY_sd: ');
586: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
587: APPLICATION_SHORT_NAME => 'MSC',
588: STATEMENT_TYPE => ad_ddl.alter_table,
589: STATEMENT => l_sql_stmt,
609: commit;
610:
611: EXCEPTION
612: WHEN OTHERS THEN
613: msc_util.msc_log('Inside main exception');
614: msc_util.msc_log(sqlerrm);
615: ERRBUF := sqlerrm;
616: RETCODE := G_ERROR;
617: --- clean tables
610:
611: EXCEPTION
612: WHEN OTHERS THEN
613: msc_util.msc_log('Inside main exception');
614: msc_util.msc_log(sqlerrm);
615: ERRBUF := sqlerrm;
616: RETCODE := G_ERROR;
617: --- clean tables
618: MSC_POST_PRO.CLEAN_TABLES(l_applsys_schema);
694: l_sr_instance_id MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
695: j pls_integer;
696: l_is_cmro number := 0; --bug 7209209
697: BEGIN
698: msc_util.msc_log ('LOAD_PLAN_SD: ' || 'Calling Module: ' || p_calling_module);
699:
700: -- Bug 3304390 Disable Trace
701: -- Commented out
702: -- rajjain 12/20/2002 begin
705: -- INTO l_spid
706: -- FROM v$process
707: -- WHERE addr = (SELECT paddr FROM v$session
708: -- WHERE audsid=userenv('SESSIONID'));
709: -- msc_util.msc_log('LOAD_PLAN_SD: ' || 'spid: ' || l_spid);
710: -- dbms_session.set_sql_trace(true);
711: -- G_TRACE := 'Y';
712: -- END IF;
713: -- rajjain 12/20/2002 end
716: -- For summary enhancement - initiallizing retcode with success
717: RETCODE := G_SUCCESS;
718:
719: l_inv_ctp := NVL(FND_PROFILE.value('INV_CTP'), 5);
720: msc_util.msc_log('LOAD_PLAN_SD: ' || 'inv_ctp := ' || l_inv_ctp);
721: IF l_inv_ctp <> 4 THEN
722: -- we are not doing PDS ATP so we wont continue
723: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Not Doing PDS ATP. Please check profile - INV: Capable to Promise". Will Exit ');
724: RETCODE := G_WARNING;
719: l_inv_ctp := NVL(FND_PROFILE.value('INV_CTP'), 5);
720: msc_util.msc_log('LOAD_PLAN_SD: ' || 'inv_ctp := ' || l_inv_ctp);
721: IF l_inv_ctp <> 4 THEN
722: -- we are not doing PDS ATP so we wont continue
723: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Not Doing PDS ATP. Please check profile - INV: Capable to Promise". Will Exit ');
724: RETCODE := G_WARNING;
725: RETURN;
726: ELSE
727:
725: RETURN;
726: ELSE
727:
728: -- 24x7 Switch
729: msc_util.msc_log ('LOAD_PLAN_SD: ' || 'Trying to see if this is a 24x7 run');
730: msc_util.msc_log ('LOAD_PLAN_SD: ' || 'Plan ID : ' || p_plan_id);
731: BEGIN
732:
733: -- 2859130
726: ELSE
727:
728: -- 24x7 Switch
729: msc_util.msc_log ('LOAD_PLAN_SD: ' || 'Trying to see if this is a 24x7 run');
730: msc_util.msc_log ('LOAD_PLAN_SD: ' || 'Plan ID : ' || p_plan_id);
731: BEGIN
732:
733: -- 2859130
734: select newp.plan_id, NVL(newp.copy_plan_id, -1),
752: where newp.plan_id = p_plan_id;
753:
754: EXCEPTION
755: WHEN NO_DATA_FOUND THEN
756: msc_util.msc_log('Unable to find plan data');
757: RETCODE := G_ERROR;
758: ERRBUF := sqlerrm;
759: RETURN;
760: END;
758: ERRBUF := sqlerrm;
759: RETURN;
760: END;
761:
762: msc_util.msc_log ('LOAD_PLAN_SD: ' || 'Plan Type : ' || l_plan_type);
763: IF l_plan_type = 4 THEN
764: IF PG_DEBUG in ('Y', 'C') THEN
765: msc_util.msc_log('LOAD_PLAN_SD: Do not Launch process for IO Plan');
766: END IF;
761:
762: msc_util.msc_log ('LOAD_PLAN_SD: ' || 'Plan Type : ' || l_plan_type);
763: IF l_plan_type = 4 THEN
764: IF PG_DEBUG in ('Y', 'C') THEN
765: msc_util.msc_log('LOAD_PLAN_SD: Do not Launch process for IO Plan');
766: END IF;
767: RETCODE := G_SUCCESS;
768: RETURN;
769: END IF;
793: --bug 3713374: Missing brackets was making OR condition to be stand alone filtering criteria
794: AND (mst1.atp_flag <> 'N' OR mst1.atp_components_flag <> 'N');
795:
796: --5027568
797: msc_util.msc_log('LOAD_PLAN_SD: deleting reservation records from msc_demands'); --5027568
798:
799: Delete MSC_DEMANDS
800: where origination_type = -100
801: and plan_id = p_plan_id
801: and plan_id = p_plan_id
802: and ORGANIZATION_ID = l_organization_id(j)
803: and sr_instance_id = l_sr_instance_id(j);
804:
805: msc_util.msc_log('LOAD_PLAN_SD: no of records deleted: '|| SQL%ROWCOUNT);
806: --5027568, to insert a record for hard reservation in msc_demands.
807:
808: msc_util.msc_log('LOAD_PLAN_SD: populating msc_demands with reservation records');
809:
804:
805: msc_util.msc_log('LOAD_PLAN_SD: no of records deleted: '|| SQL%ROWCOUNT);
806: --5027568, to insert a record for hard reservation in msc_demands.
807:
808: msc_util.msc_log('LOAD_PLAN_SD: populating msc_demands with reservation records');
809:
810: INSERT INTO MSC_DEMANDS(
811: DEMAND_ID,
812: USING_REQUIREMENT_QUANTITY,
878: and sr_instance_id = l_sr_instance_id(j)
879: and origination_type in (30,6)
880: );
881:
882: msc_util.msc_log('LOAD_PLAN_SD: no of records updated: '|| SQL%ROWCOUNT);
883: END LOOP;
884: --5027568
885:
886: l_plan_to_use := -1;
930: l_plan_to_use := l_old_plan_id;
931: l_old_plan := p_plan_id;
932: l_24_plan := 1;
933: l_optimized_plan := l_old_optimized_plan; -- 2859130
934: msc_util.msc_log ('---- A Copy plan found ----');
935: msc_util.msc_log (' A copy of the plan for a 24x7 plan run was found');
936: msc_util.msc_log (' Switching to that plan ID for future processing');
937: msc_util.msc_log (' The plan ID that will be used : ' || l_plan_to_use);
938:
931: l_old_plan := p_plan_id;
932: l_24_plan := 1;
933: l_optimized_plan := l_old_optimized_plan; -- 2859130
934: msc_util.msc_log ('---- A Copy plan found ----');
935: msc_util.msc_log (' A copy of the plan for a 24x7 plan run was found');
936: msc_util.msc_log (' Switching to that plan ID for future processing');
937: msc_util.msc_log (' The plan ID that will be used : ' || l_plan_to_use);
938:
939: else
932: l_24_plan := 1;
933: l_optimized_plan := l_old_optimized_plan; -- 2859130
934: msc_util.msc_log ('---- A Copy plan found ----');
935: msc_util.msc_log (' A copy of the plan for a 24x7 plan run was found');
936: msc_util.msc_log (' Switching to that plan ID for future processing');
937: msc_util.msc_log (' The plan ID that will be used : ' || l_plan_to_use);
938:
939: else
940: l_plan_to_use := p_plan_id;
933: l_optimized_plan := l_old_optimized_plan; -- 2859130
934: msc_util.msc_log ('---- A Copy plan found ----');
935: msc_util.msc_log (' A copy of the plan for a 24x7 plan run was found');
936: msc_util.msc_log (' Switching to that plan ID for future processing');
937: msc_util.msc_log (' The plan ID that will be used : ' || l_plan_to_use);
938:
939: else
940: l_plan_to_use := p_plan_id;
941: end if;
939: else
940: l_plan_to_use := p_plan_id;
941: end if;
942: end if;
943: msc_util.msc_log ('After processing the plan ID');
944: msc_util.msc_log ('The following plan ID will be used for further post processing');
945: msc_util.msc_log ('Using plan : ' || l_plan_to_use);
946:
947: --bug 7209209 start
940: l_plan_to_use := p_plan_id;
941: end if;
942: end if;
943: msc_util.msc_log ('After processing the plan ID');
944: msc_util.msc_log ('The following plan ID will be used for further post processing');
945: msc_util.msc_log ('Using plan : ' || l_plan_to_use);
946:
947: --bug 7209209 start
948: /* Find out if this is CMRO scenario */
941: end if;
942: end if;
943: msc_util.msc_log ('After processing the plan ID');
944: msc_util.msc_log ('The following plan ID will be used for further post processing');
945: msc_util.msc_log ('Using plan : ' || l_plan_to_use);
946:
947: --bug 7209209 start
948: /* Find out if this is CMRO scenario */
949: FOR j IN l_organization_id.first.. l_organization_id.last LOOP
959: end if;
960: END LOOP;
961:
962: if(l_is_cmro > 0) then
963: msc_util.msc_log ('This is a CMRO scenario...');
964: FORALL j IN l_organization_id.first.. l_organization_id.last
965: update msc_demands dem
966: set demand_source_type = (select distinct demand_source_type
967: from msc_sales_orders so
984: G_ALLOC_METHOD := 2;
985: -- Do not Call Populate_ATF_Dates for PF ATP.
986: IF PG_DEBUG in ('Y', 'C') THEN
987: msc_sch_wb.atp_debug('----- ATP4drp Specific Debug Messages -----');
988: msc_util.msc_log('G_ALLOC_ATP := ' || G_ALLOC_ATP);
989: msc_util.msc_log('LOAD_PLAN_SD: DRP Plan Populate ATF_Dates not called');
990: msc_util.msc_log('LOAD_PLAN_SD: G_ALLOC_ATP ' || G_ALLOC_ATP);
991: msc_util.msc_log('LOAD_PLAN_SD: DRP Plan Allocation not supported');
992: msc_util.msc_log('LOAD_PLAN_SD: DRP Plan, Hence Post Plan Pegging not called');
985: -- Do not Call Populate_ATF_Dates for PF ATP.
986: IF PG_DEBUG in ('Y', 'C') THEN
987: msc_sch_wb.atp_debug('----- ATP4drp Specific Debug Messages -----');
988: msc_util.msc_log('G_ALLOC_ATP := ' || G_ALLOC_ATP);
989: msc_util.msc_log('LOAD_PLAN_SD: DRP Plan Populate ATF_Dates not called');
990: msc_util.msc_log('LOAD_PLAN_SD: G_ALLOC_ATP ' || G_ALLOC_ATP);
991: msc_util.msc_log('LOAD_PLAN_SD: DRP Plan Allocation not supported');
992: msc_util.msc_log('LOAD_PLAN_SD: DRP Plan, Hence Post Plan Pegging not called');
993: msc_sch_wb.atp_debug('----- ATP4drp Specific Debug Messages -----');
986: IF PG_DEBUG in ('Y', 'C') THEN
987: msc_sch_wb.atp_debug('----- ATP4drp Specific Debug Messages -----');
988: msc_util.msc_log('G_ALLOC_ATP := ' || G_ALLOC_ATP);
989: msc_util.msc_log('LOAD_PLAN_SD: DRP Plan Populate ATF_Dates not called');
990: msc_util.msc_log('LOAD_PLAN_SD: G_ALLOC_ATP ' || G_ALLOC_ATP);
991: msc_util.msc_log('LOAD_PLAN_SD: DRP Plan Allocation not supported');
992: msc_util.msc_log('LOAD_PLAN_SD: DRP Plan, Hence Post Plan Pegging not called');
993: msc_sch_wb.atp_debug('----- ATP4drp Specific Debug Messages -----');
994: END IF;
987: msc_sch_wb.atp_debug('----- ATP4drp Specific Debug Messages -----');
988: msc_util.msc_log('G_ALLOC_ATP := ' || G_ALLOC_ATP);
989: msc_util.msc_log('LOAD_PLAN_SD: DRP Plan Populate ATF_Dates not called');
990: msc_util.msc_log('LOAD_PLAN_SD: G_ALLOC_ATP ' || G_ALLOC_ATP);
991: msc_util.msc_log('LOAD_PLAN_SD: DRP Plan Allocation not supported');
992: msc_util.msc_log('LOAD_PLAN_SD: DRP Plan, Hence Post Plan Pegging not called');
993: msc_sch_wb.atp_debug('----- ATP4drp Specific Debug Messages -----');
994: END IF;
995: ELSE -- Carry out processing for non-DRP plan.
988: msc_util.msc_log('G_ALLOC_ATP := ' || G_ALLOC_ATP);
989: msc_util.msc_log('LOAD_PLAN_SD: DRP Plan Populate ATF_Dates not called');
990: msc_util.msc_log('LOAD_PLAN_SD: G_ALLOC_ATP ' || G_ALLOC_ATP);
991: msc_util.msc_log('LOAD_PLAN_SD: DRP Plan Allocation not supported');
992: msc_util.msc_log('LOAD_PLAN_SD: DRP Plan, Hence Post Plan Pegging not called');
993: msc_sch_wb.atp_debug('----- ATP4drp Specific Debug Messages -----');
994: END IF;
995: ELSE -- Carry out processing for non-DRP plan.
996: G_ALLOC_ATP := NVL(FND_PROFILE.value('MSC_ALLOCATED_ATP'),'N');
996: G_ALLOC_ATP := NVL(FND_PROFILE.value('MSC_ALLOCATED_ATP'),'N');
997: G_CLASS_HRCHY := NVL(FND_PROFILE.VALUE('MSC_CLASS_HIERARCHY'), 2);
998: G_ALLOC_METHOD := NVL(FND_PROFILE.VALUE('MSC_ALLOCATION_METHOD'), 2);
999:
1000: msc_util.msc_log('G_ALLOC_ATP := ' || G_ALLOC_ATP);
1001: msc_util.msc_log('G_CLASS_HRCHY := ' || G_CLASS_HRCHY);
1002: msc_util.msc_log('G_ALLOC_METHOD := ' || G_ALLOC_METHOD);
1003: -- time_phased_atp changes begin
1004: /* Populate ATF dates*/
997: G_CLASS_HRCHY := NVL(FND_PROFILE.VALUE('MSC_CLASS_HIERARCHY'), 2);
998: G_ALLOC_METHOD := NVL(FND_PROFILE.VALUE('MSC_ALLOCATION_METHOD'), 2);
999:
1000: msc_util.msc_log('G_ALLOC_ATP := ' || G_ALLOC_ATP);
1001: msc_util.msc_log('G_CLASS_HRCHY := ' || G_CLASS_HRCHY);
1002: msc_util.msc_log('G_ALLOC_METHOD := ' || G_ALLOC_METHOD);
1003: -- time_phased_atp changes begin
1004: /* Populate ATF dates*/
1005: MSC_ATP_PF.Populate_ATF_Dates(l_plan_to_use, l_member_count, l_return_status);
998: G_ALLOC_METHOD := NVL(FND_PROFILE.VALUE('MSC_ALLOCATION_METHOD'), 2);
999:
1000: msc_util.msc_log('G_ALLOC_ATP := ' || G_ALLOC_ATP);
1001: msc_util.msc_log('G_CLASS_HRCHY := ' || G_CLASS_HRCHY);
1002: msc_util.msc_log('G_ALLOC_METHOD := ' || G_ALLOC_METHOD);
1003: -- time_phased_atp changes begin
1004: /* Populate ATF dates*/
1005: MSC_ATP_PF.Populate_ATF_Dates(l_plan_to_use, l_member_count, l_return_status);
1006:
1005: MSC_ATP_PF.Populate_ATF_Dates(l_plan_to_use, l_member_count, l_return_status);
1006:
1007: /* Print error in conc log file if return status is not success*/
1008: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1009: msc_util.msc_log('Return status after call to Populate_ATF_Dates is ' || l_return_status);
1010: msc_util.msc_log(' ');
1011: msc_util.msc_log('******************************************************************');
1012: msc_util.msc_log('* WARNING *');
1013: msc_util.msc_log('* Please note that the results of post plan processing may not *');
1006:
1007: /* Print error in conc log file if return status is not success*/
1008: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1009: msc_util.msc_log('Return status after call to Populate_ATF_Dates is ' || l_return_status);
1010: msc_util.msc_log(' ');
1011: msc_util.msc_log('******************************************************************');
1012: msc_util.msc_log('* WARNING *');
1013: msc_util.msc_log('* Please note that the results of post plan processing may not *');
1014: msc_util.msc_log('* be accurate for product family/product family member items. *');
1007: /* Print error in conc log file if return status is not success*/
1008: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1009: msc_util.msc_log('Return status after call to Populate_ATF_Dates is ' || l_return_status);
1010: msc_util.msc_log(' ');
1011: msc_util.msc_log('******************************************************************');
1012: msc_util.msc_log('* WARNING *');
1013: msc_util.msc_log('* Please note that the results of post plan processing may not *');
1014: msc_util.msc_log('* be accurate for product family/product family member items. *');
1015: msc_util.msc_log('* Please re-run ATP Post Plan Processing seperately to ensure *');
1008: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1009: msc_util.msc_log('Return status after call to Populate_ATF_Dates is ' || l_return_status);
1010: msc_util.msc_log(' ');
1011: msc_util.msc_log('******************************************************************');
1012: msc_util.msc_log('* WARNING *');
1013: msc_util.msc_log('* Please note that the results of post plan processing may not *');
1014: msc_util.msc_log('* be accurate for product family/product family member items. *');
1015: msc_util.msc_log('* Please re-run ATP Post Plan Processing seperately to ensure *');
1016: msc_util.msc_log('* correct ATP results *');
1009: msc_util.msc_log('Return status after call to Populate_ATF_Dates is ' || l_return_status);
1010: msc_util.msc_log(' ');
1011: msc_util.msc_log('******************************************************************');
1012: msc_util.msc_log('* WARNING *');
1013: msc_util.msc_log('* Please note that the results of post plan processing may not *');
1014: msc_util.msc_log('* be accurate for product family/product family member items. *');
1015: msc_util.msc_log('* Please re-run ATP Post Plan Processing seperately to ensure *');
1016: msc_util.msc_log('* correct ATP results *');
1017: msc_util.msc_log('******************************************************************');
1010: msc_util.msc_log(' ');
1011: msc_util.msc_log('******************************************************************');
1012: msc_util.msc_log('* WARNING *');
1013: msc_util.msc_log('* Please note that the results of post plan processing may not *');
1014: msc_util.msc_log('* be accurate for product family/product family member items. *');
1015: msc_util.msc_log('* Please re-run ATP Post Plan Processing seperately to ensure *');
1016: msc_util.msc_log('* correct ATP results *');
1017: msc_util.msc_log('******************************************************************');
1018: msc_util.msc_log(' ');
1011: msc_util.msc_log('******************************************************************');
1012: msc_util.msc_log('* WARNING *');
1013: msc_util.msc_log('* Please note that the results of post plan processing may not *');
1014: msc_util.msc_log('* be accurate for product family/product family member items. *');
1015: msc_util.msc_log('* Please re-run ATP Post Plan Processing seperately to ensure *');
1016: msc_util.msc_log('* correct ATP results *');
1017: msc_util.msc_log('******************************************************************');
1018: msc_util.msc_log(' ');
1019: END IF;
1012: msc_util.msc_log('* WARNING *');
1013: msc_util.msc_log('* Please note that the results of post plan processing may not *');
1014: msc_util.msc_log('* be accurate for product family/product family member items. *');
1015: msc_util.msc_log('* Please re-run ATP Post Plan Processing seperately to ensure *');
1016: msc_util.msc_log('* correct ATP results *');
1017: msc_util.msc_log('******************************************************************');
1018: msc_util.msc_log(' ');
1019: END IF;
1020:
1013: msc_util.msc_log('* Please note that the results of post plan processing may not *');
1014: msc_util.msc_log('* be accurate for product family/product family member items. *');
1015: msc_util.msc_log('* Please re-run ATP Post Plan Processing seperately to ensure *');
1016: msc_util.msc_log('* correct ATP results *');
1017: msc_util.msc_log('******************************************************************');
1018: msc_util.msc_log(' ');
1019: END IF;
1020:
1021: /* Call pf_post_plan_proc procedure only if:
1014: msc_util.msc_log('* be accurate for product family/product family member items. *');
1015: msc_util.msc_log('* Please re-run ATP Post Plan Processing seperately to ensure *');
1016: msc_util.msc_log('* correct ATP results *');
1017: msc_util.msc_log('******************************************************************');
1018: msc_util.msc_log(' ');
1019: END IF;
1020:
1021: /* Call pf_post_plan_proc procedure only if:
1022: o There are finite member items having ATF set up. This is to make sure
1022: o There are finite member items having ATF set up. This is to make sure
1023: that the customers not using time phased ATP are not affected.
1024: */
1025: IF l_member_count > 0 THEN
1026: msc_util.msc_log('Found finite member items having ATF.');
1027:
1028: IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
1029: msc_util.msc_log('Calling pf_post_plan_proc for pre-allocation, supplies rollup and bucketing');
1030: l_demand_priority := 'Y';
1025: IF l_member_count > 0 THEN
1026: msc_util.msc_log('Found finite member items having ATF.');
1027:
1028: IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
1029: msc_util.msc_log('Calling pf_post_plan_proc for pre-allocation, supplies rollup and bucketing');
1030: l_demand_priority := 'Y';
1031: ELSE
1032: msc_util.msc_log('Calling pf_post_plan_proc for supplies rollup and bucketing');
1033: l_demand_priority := 'N';
1028: IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
1029: msc_util.msc_log('Calling pf_post_plan_proc for pre-allocation, supplies rollup and bucketing');
1030: l_demand_priority := 'Y';
1031: ELSE
1032: msc_util.msc_log('Calling pf_post_plan_proc for supplies rollup and bucketing');
1033: l_demand_priority := 'N';
1034: END IF;
1035: msc_atp_pf.pf_post_plan_proc(ERRBUF, RETCODE, l_plan_to_use, l_demand_priority);
1036: -- time_phased_atp changes end
1035: msc_atp_pf.pf_post_plan_proc(ERRBUF, RETCODE, l_plan_to_use, l_demand_priority);
1036: -- time_phased_atp changes end
1037:
1038: -- Begin CTO ODR Simplified Pegging Generation
1039: msc_util.msc_log('Calling post_plan_pegging to generate ATP pegging ' );
1040:
1041: MSC_ATP_PEG.post_plan_pegging(ERRBUF, RETCODE, l_plan_to_use);
1042:
1043: msc_util.msc_log('After Call to Post_Plan_Pegging ' );
1039: msc_util.msc_log('Calling post_plan_pegging to generate ATP pegging ' );
1040:
1041: MSC_ATP_PEG.post_plan_pegging(ERRBUF, RETCODE, l_plan_to_use);
1042:
1043: msc_util.msc_log('After Call to Post_Plan_Pegging ' );
1044: IF RETCODE = G_SUCCESS THEN
1045: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed successfully');
1046: ELSIF RETCODE = G_WARNING THEN
1047: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed with WARNING');
1041: MSC_ATP_PEG.post_plan_pegging(ERRBUF, RETCODE, l_plan_to_use);
1042:
1043: msc_util.msc_log('After Call to Post_Plan_Pegging ' );
1044: IF RETCODE = G_SUCCESS THEN
1045: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed successfully');
1046: ELSIF RETCODE = G_WARNING THEN
1047: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed with WARNING');
1048: ELSE
1049: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging failed. Will exit');
1043: msc_util.msc_log('After Call to Post_Plan_Pegging ' );
1044: IF RETCODE = G_SUCCESS THEN
1045: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed successfully');
1046: ELSIF RETCODE = G_WARNING THEN
1047: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed with WARNING');
1048: ELSE
1049: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging failed. Will exit');
1050: -- RETURN;
1051: END IF;
1045: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed successfully');
1046: ELSIF RETCODE = G_WARNING THEN
1047: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed with WARNING');
1048: ELSE
1049: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging failed. Will exit');
1050: -- RETURN;
1051: END IF;
1052: -- End CTO ODR Simplified Pegging Generation
1053:
1053:
1054: -- for summary enhancement
1055: ELSIF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
1056: -- we are doing Allocated ATP so we need to call post_plan_allocation
1057: msc_util.msc_log('Doing demand priority allocated ATP. Will call post_plan_allocation ');
1058:
1059: msc_post_pro.post_plan_allocation(ERRBUF, RETCODE, l_plan_to_use);
1060:
1061: IF RETCODE <> G_SUCCESS THEN
1058:
1059: msc_post_pro.post_plan_allocation(ERRBUF, RETCODE, l_plan_to_use);
1060:
1061: IF RETCODE <> G_SUCCESS THEN
1062: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan allocation failed. Will exit');
1063: RETURN;
1064: END IF;
1065: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan allocation completed successfully');
1066:
1061: IF RETCODE <> G_SUCCESS THEN
1062: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan allocation failed. Will exit');
1063: RETURN;
1064: END IF;
1065: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan allocation completed successfully');
1066:
1067: -- Begin CTO ODR Simplified Pegging Generation
1068: msc_util.msc_log('Calling post_plan_pegging to generate ATP pegging ' );
1069:
1064: END IF;
1065: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan allocation completed successfully');
1066:
1067: -- Begin CTO ODR Simplified Pegging Generation
1068: msc_util.msc_log('Calling post_plan_pegging to generate ATP pegging ' );
1069:
1070: MSC_ATP_PEG.post_plan_pegging(ERRBUF, RETCODE, l_plan_to_use);
1071:
1072: msc_util.msc_log('After Call to Post_Plan_Pegging ' );
1068: msc_util.msc_log('Calling post_plan_pegging to generate ATP pegging ' );
1069:
1070: MSC_ATP_PEG.post_plan_pegging(ERRBUF, RETCODE, l_plan_to_use);
1071:
1072: msc_util.msc_log('After Call to Post_Plan_Pegging ' );
1073: IF RETCODE = G_SUCCESS THEN
1074: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed successfully');
1075: ELSIF RETCODE = G_WARNING THEN
1076: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed with WARNING');
1070: MSC_ATP_PEG.post_plan_pegging(ERRBUF, RETCODE, l_plan_to_use);
1071:
1072: msc_util.msc_log('After Call to Post_Plan_Pegging ' );
1073: IF RETCODE = G_SUCCESS THEN
1074: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed successfully');
1075: ELSIF RETCODE = G_WARNING THEN
1076: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed with WARNING');
1077: ELSE
1078: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging failed. Will exit');
1072: msc_util.msc_log('After Call to Post_Plan_Pegging ' );
1073: IF RETCODE = G_SUCCESS THEN
1074: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed successfully');
1075: ELSIF RETCODE = G_WARNING THEN
1076: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed with WARNING');
1077: ELSE
1078: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging failed. Will exit');
1079: -- RETURN;
1080: END IF;
1074: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed successfully');
1075: ELSIF RETCODE = G_WARNING THEN
1076: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed with WARNING');
1077: ELSE
1078: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging failed. Will exit');
1079: -- RETURN;
1080: END IF;
1081: -- End CTO ODR Simplified Pegging Generation
1082:
1084: -- 24x7 Hooks
1085: /*
1086: if (l_24_plan > 0) then
1087: -- call 24x7 ATP Synchronize
1088: msc_util.msc_log ('Calling 24x7 Synchronization');
1089: MSC_ATP_24X7.Call_Synchronize (ERRBUF, RETCODE, l_old_plan);
1090: end if;
1091: RETURN;
1092: */
1092: */
1093: ELSE -- Always carry out post_plan_pegging.
1094:
1095: -- Begin CTO ODR Simplified Pegging Generation
1096: msc_util.msc_log('Calling post_plan_pegging to generate ATP pegging ' );
1097:
1098: MSC_ATP_PEG.post_plan_pegging(ERRBUF, RETCODE, l_plan_to_use);
1099:
1100: msc_util.msc_log('After Call to Post_Plan_Pegging ' );
1096: msc_util.msc_log('Calling post_plan_pegging to generate ATP pegging ' );
1097:
1098: MSC_ATP_PEG.post_plan_pegging(ERRBUF, RETCODE, l_plan_to_use);
1099:
1100: msc_util.msc_log('After Call to Post_Plan_Pegging ' );
1101: IF RETCODE = G_SUCCESS THEN
1102: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed successfully');
1103: ELSIF RETCODE = G_WARNING THEN
1104: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed with WARNING');
1098: MSC_ATP_PEG.post_plan_pegging(ERRBUF, RETCODE, l_plan_to_use);
1099:
1100: msc_util.msc_log('After Call to Post_Plan_Pegging ' );
1101: IF RETCODE = G_SUCCESS THEN
1102: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed successfully');
1103: ELSIF RETCODE = G_WARNING THEN
1104: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed with WARNING');
1105: ELSE
1106: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging failed. Will exit');
1100: msc_util.msc_log('After Call to Post_Plan_Pegging ' );
1101: IF RETCODE = G_SUCCESS THEN
1102: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed successfully');
1103: ELSIF RETCODE = G_WARNING THEN
1104: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed with WARNING');
1105: ELSE
1106: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging failed. Will exit');
1107: --RETURN;
1108: END IF;
1102: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed successfully');
1103: ELSIF RETCODE = G_WARNING THEN
1104: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging completed with WARNING');
1105: ELSE
1106: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Post plan Pegging failed. Will exit');
1107: --RETURN;
1108: END IF;
1109: END IF;
1110: END IF;
1116: AND (G_ALLOC_ATP = 'N' -- After summary ehancement summary will be
1117: OR (G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1) -- supported fot demand priority based
1118: ) -- allocated ATP
1119: ) THEN
1120: msc_util.msc_log('begin Loading data for plan ' || l_plan_to_use);
1121:
1122: share_partition := fnd_profile.value('MSC_SHARE_PARTITIONS');
1123: msc_util.msc_log('share_partition := ' || share_partition);
1124:
1119: ) THEN
1120: msc_util.msc_log('begin Loading data for plan ' || l_plan_to_use);
1121:
1122: share_partition := fnd_profile.value('MSC_SHARE_PARTITIONS');
1123: msc_util.msc_log('share_partition := ' || share_partition);
1124:
1125: -- for summary enhancement - backlog workbench will be supported with summary
1126: /*
1127: SELECT NVL(SUMMARY_FLAG,1)
1129: from msc_apps_instances
1130: where rownum = 1;
1131:
1132: IF NVL(l_summary_flag,1) = 200 THEN
1133: msc_util.msc_log('Summary Approach is not supported for sites using backlog workbench');
1134: RETCODE := G_WARNING;
1135: RETURN;
1136: END IF;
1137: */
1143: where plan_id = l_plan_to_use;
1144:
1145: -- for summary enhancement
1146: IF NVL(l_summary_flag,1) NOT IN (G_SF_SUMMARY_NOT_RUN, G_SF_PREALLOC_COMPLETED, G_SF_ATPPEG_COMPLETED, G_SF_SUMMARY_COMPLETED) THEN
1147: msc_util.msc_log('LOAD_PLAN_SD: ' || 'Another session is running post-plan processing for this plan');
1148: RETCODE := G_ERROR;
1149: RETURN;
1150: END IF;
1151:
1165: l_plan_id := l_plan_to_use;
1166: END IF;
1167:
1168: l_partition_name := atp_summ_tab(i)|| '_' || l_plan_id;
1169: msc_util.msc_log('l_partition_name := ' || l_partition_name);
1170:
1171: BEGIN
1172: SELECT count(*)
1173: INTO l_count
1178: AND PARTITION_NAME = l_partition_name
1179: AND table_owner = l_msc_schema;
1180: EXCEPTION
1181: WHEN OTHERS THEN
1182: msc_util.msc_log('Inside Exception');
1183: l_count := 0;
1184: END;
1185: IF (l_count = 0) THEN
1186: -- Bug 2516506
1186: -- Bug 2516506
1187: FND_MESSAGE.SET_NAME('MSC', 'MSC_ATP_PLAN_PARTITION_MISSING');
1188: FND_MESSAGE.SET_TOKEN('PLAN_NAME', l_plan_name);
1189: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'MSC_' || atp_summ_tab(i));
1190: msc_util.msc_log(FND_MESSAGE.GET);
1191: RETCODE := G_ERROR;
1192: RETURN;
1193: END IF;
1194: END LOOP;
1205: RETURN;
1206: END;
1207:
1208:
1209: msc_util.msc_log('LOAD_PLAN_SD: ' || 'share_partition := ' || share_partition);
1210: l_sysdate := sysdate;
1211: l_user_id := FND_GLOBAL.USER_ID;
1212:
1213: BEGIN -- Enclose summary specific operations within BEGIN-EXCEPTION-END block for summary enhancement
1251: l_sysdate);
1252:
1253: -- refresh number should be updated before gather stats as gathering stats causes implicit commit
1254: BEGIN
1255: msc_util.msc_log ('LOAD_PLAN_SD: ' || 'updating summary flag and refresh number');
1256: update msc_plans
1257: set summary_flag = G_SF_SUMMARY_COMPLETED, -- For summary enhancement
1258: latest_refresh_number = (SELECT apps_lrn
1259: FROM MSC_PLAN_REFRESHES
1290: -- is launched from plan
1291: if p_calling_module = 2 then
1292: -- Reset the ATP_SYNCHRONIZATION_FLAG to 0 for the original plan.
1293: -- Moved this from MSCPLAPB.pls
1294: msc_util.msc_debug('Update atp_synchronization_flag for 24x7 plan to support re-run after sync failure');
1295:
1296: update msc_demands
1297: set atp_synchronization_flag = 0 -- null
1298: where (plan_id, sr_instance_id, organization_id) IN
1304: COMMIT;
1305: end if;
1306:
1307: -- call 24x7 ATP Synchronize
1308: msc_util.msc_log ('Calling 24x7 Synchronization');
1309: MSC_ATP_24X7.Call_Synchronize (ERRBUF, RETCODE, l_old_plan);
1310: end if;
1311: EXCEPTION
1312: WHEN OTHERS THEN
1319: commit;
1320: END;
1321: */
1322:
1323: msc_util.msc_log('Inside main exception');
1324: msc_util.msc_log(sqlerrm);
1325: ERRBUF := sqlerrm;
1326: RETCODE := G_ERROR;
1327:
1320: END;
1321: */
1322:
1323: msc_util.msc_log('Inside main exception');
1324: msc_util.msc_log(sqlerrm);
1325: ERRBUF := sqlerrm;
1326: RETCODE := G_ERROR;
1327:
1328: -- For summary enhancement - Need to rollback incomplete changes
1355:
1356: RETCODE := G_SUCCESS;
1357: l_sysdate := sysdate;
1358:
1359: msc_util.msc_log ('Load_Net_Plan: ' || 'Plan ID : ' || p_plan_id);
1360:
1361: l_inv_ctp := NVL(FND_PROFILE.value('INV_CTP'), 5);
1362: msc_util.msc_log('Load_Net_Plan: ' || 'inv_ctp := ' || l_inv_ctp);
1363: IF l_inv_ctp <> 4 THEN
1358:
1359: msc_util.msc_log ('Load_Net_Plan: ' || 'Plan ID : ' || p_plan_id);
1360:
1361: l_inv_ctp := NVL(FND_PROFILE.value('INV_CTP'), 5);
1362: msc_util.msc_log('Load_Net_Plan: ' || 'inv_ctp := ' || l_inv_ctp);
1363: IF l_inv_ctp <> 4 THEN
1364: -- we are not doing PDS ATP so we wont continue
1365: msc_util.msc_log('Load_Net_Plan: ' || 'Not Doing PDS ATP. Please check profile - INV: Capable to Promise". Will Exit ');
1366: RETCODE := G_WARNING;
1361: l_inv_ctp := NVL(FND_PROFILE.value('INV_CTP'), 5);
1362: msc_util.msc_log('Load_Net_Plan: ' || 'inv_ctp := ' || l_inv_ctp);
1363: IF l_inv_ctp <> 4 THEN
1364: -- we are not doing PDS ATP so we wont continue
1365: msc_util.msc_log('Load_Net_Plan: ' || 'Not Doing PDS ATP. Please check profile - INV: Capable to Promise". Will Exit ');
1366: RETCODE := G_WARNING;
1367: RETURN;
1368: END IF;
1369:
1367: RETURN;
1368: END IF;
1369:
1370: l_enable_summary_mode := NVL(FND_PROFILE.value('MSC_ENABLE_ATP_SUMMARY'), 'N');
1371: msc_util.msc_log('Load_Net_Plan: ' || 'l_enable_summary_mode := ' || l_enable_summary_mode);
1372: IF l_enable_summary_mode = 'N' THEN
1373: -- summary is not enabled so we wont continue
1374: msc_util.msc_log('Load_Net_Plan: ' || 'Not Doing Summary ATP. Please check profile - MSC: Enable ATP Summary Mode. Will Exit ');
1375: RETCODE := G_WARNING;
1370: l_enable_summary_mode := NVL(FND_PROFILE.value('MSC_ENABLE_ATP_SUMMARY'), 'N');
1371: msc_util.msc_log('Load_Net_Plan: ' || 'l_enable_summary_mode := ' || l_enable_summary_mode);
1372: IF l_enable_summary_mode = 'N' THEN
1373: -- summary is not enabled so we wont continue
1374: msc_util.msc_log('Load_Net_Plan: ' || 'Not Doing Summary ATP. Please check profile - MSC: Enable ATP Summary Mode. Will Exit ');
1375: RETCODE := G_WARNING;
1376: RETURN;
1377: END IF;
1378:
1388: from msc_plans
1389: where plan_id = p_plan_id;
1390: EXCEPTION
1391: WHEN NO_DATA_FOUND THEN
1392: msc_util.msc_log('Unable to find plan data');
1393: RETCODE := G_ERROR;
1394: ERRBUF := sqlerrm;
1395: RETURN;
1396: END;
1395: RETURN;
1396: END;
1397:
1398: IF l_plan_completion_date IS NULL THEN
1399: msc_util.msc_log('Load_Net_Plan: ' || 'Either the plan is currently running or it ' ||
1400: 'did not complete successfully or it was never run. Will exit');
1401: RETCODE := G_WARNING;
1402: RETURN;
1403: END IF;
1403: END IF;
1404:
1405: --IF NVL(l_summary_flag,1) <> G_SF_SUMMARY_COMPLETED THEN 4754549 excluded 6 meaning 24X7 synch completed successfully.
1406: IF NVL(l_summary_flag,1) not in ( G_SF_SUMMARY_COMPLETED,G_SF_SYNC_SUCCESS)THEN
1407: msc_util.msc_log('Load_Net_Plan: ' || 'Full summary was not run. l_summary_flag is ' || l_summary_flag || '. Will Exit');
1408: RETCODE := G_ERROR;
1409: RETURN;
1410: END IF;
1411:
1418: G_ALLOC_METHOD := 2;
1419: IF PG_DEBUG in ('Y', 'C') THEN
1420: msc_sch_wb.atp_debug('----- ATP4drp Specific Debug Messages -----');
1421: msc_sch_wb.atp_debug('Load_Net_Plan: ' || 'PF and Allocated ATP not applicable for DRP plans');
1422: msc_util.msc_log('G_ALLOC_ATP := ' || G_ALLOC_ATP);
1423: msc_util.msc_log('G_CLASS_HRCHY := ' || G_CLASS_HRCHY);
1424: msc_util.msc_log('G_ALLOC_METHOD := ' || G_ALLOC_METHOD);
1425: END IF;
1426: ELSE -- ATP4drp Execute rest for only for non-DRP plans
1419: IF PG_DEBUG in ('Y', 'C') THEN
1420: msc_sch_wb.atp_debug('----- ATP4drp Specific Debug Messages -----');
1421: msc_sch_wb.atp_debug('Load_Net_Plan: ' || 'PF and Allocated ATP not applicable for DRP plans');
1422: msc_util.msc_log('G_ALLOC_ATP := ' || G_ALLOC_ATP);
1423: msc_util.msc_log('G_CLASS_HRCHY := ' || G_CLASS_HRCHY);
1424: msc_util.msc_log('G_ALLOC_METHOD := ' || G_ALLOC_METHOD);
1425: END IF;
1426: ELSE -- ATP4drp Execute rest for only for non-DRP plans
1427: G_ALLOC_ATP := NVL(FND_PROFILE.value('MSC_ALLOCATED_ATP'),'N');
1420: msc_sch_wb.atp_debug('----- ATP4drp Specific Debug Messages -----');
1421: msc_sch_wb.atp_debug('Load_Net_Plan: ' || 'PF and Allocated ATP not applicable for DRP plans');
1422: msc_util.msc_log('G_ALLOC_ATP := ' || G_ALLOC_ATP);
1423: msc_util.msc_log('G_CLASS_HRCHY := ' || G_CLASS_HRCHY);
1424: msc_util.msc_log('G_ALLOC_METHOD := ' || G_ALLOC_METHOD);
1425: END IF;
1426: ELSE -- ATP4drp Execute rest for only for non-DRP plans
1427: G_ALLOC_ATP := NVL(FND_PROFILE.value('MSC_ALLOCATED_ATP'),'N');
1428: G_CLASS_HRCHY := NVL(FND_PROFILE.VALUE('MSC_CLASS_HIERARCHY'), 2);
1427: G_ALLOC_ATP := NVL(FND_PROFILE.value('MSC_ALLOCATED_ATP'),'N');
1428: G_CLASS_HRCHY := NVL(FND_PROFILE.VALUE('MSC_CLASS_HIERARCHY'), 2);
1429: G_ALLOC_METHOD := NVL(FND_PROFILE.VALUE('MSC_ALLOCATION_METHOD'), 2);
1430:
1431: msc_util.msc_log('G_ALLOC_ATP := ' || G_ALLOC_ATP);
1432: msc_util.msc_log('G_CLASS_HRCHY := ' || G_CLASS_HRCHY);
1433: msc_util.msc_log('G_ALLOC_METHOD := ' || G_ALLOC_METHOD);
1434:
1435: IF G_ALLOC_ATP = 'Y' AND (G_CLASS_HRCHY <> 1 OR G_ALLOC_METHOD <> 1) THEN
1428: G_CLASS_HRCHY := NVL(FND_PROFILE.VALUE('MSC_CLASS_HIERARCHY'), 2);
1429: G_ALLOC_METHOD := NVL(FND_PROFILE.VALUE('MSC_ALLOCATION_METHOD'), 2);
1430:
1431: msc_util.msc_log('G_ALLOC_ATP := ' || G_ALLOC_ATP);
1432: msc_util.msc_log('G_CLASS_HRCHY := ' || G_CLASS_HRCHY);
1433: msc_util.msc_log('G_ALLOC_METHOD := ' || G_ALLOC_METHOD);
1434:
1435: IF G_ALLOC_ATP = 'Y' AND (G_CLASS_HRCHY <> 1 OR G_ALLOC_METHOD <> 1) THEN
1436: msc_util.msc_log('Load_Net_Plan: ' || 'Summary not supported for User-defined allocation. Will Exit ');
1429: G_ALLOC_METHOD := NVL(FND_PROFILE.VALUE('MSC_ALLOCATION_METHOD'), 2);
1430:
1431: msc_util.msc_log('G_ALLOC_ATP := ' || G_ALLOC_ATP);
1432: msc_util.msc_log('G_CLASS_HRCHY := ' || G_CLASS_HRCHY);
1433: msc_util.msc_log('G_ALLOC_METHOD := ' || G_ALLOC_METHOD);
1434:
1435: IF G_ALLOC_ATP = 'Y' AND (G_CLASS_HRCHY <> 1 OR G_ALLOC_METHOD <> 1) THEN
1436: msc_util.msc_log('Load_Net_Plan: ' || 'Summary not supported for User-defined allocation. Will Exit ');
1437: RETCODE := G_WARNING;
1432: msc_util.msc_log('G_CLASS_HRCHY := ' || G_CLASS_HRCHY);
1433: msc_util.msc_log('G_ALLOC_METHOD := ' || G_ALLOC_METHOD);
1434:
1435: IF G_ALLOC_ATP = 'Y' AND (G_CLASS_HRCHY <> 1 OR G_ALLOC_METHOD <> 1) THEN
1436: msc_util.msc_log('Load_Net_Plan: ' || 'Summary not supported for User-defined allocation. Will Exit ');
1437: RETCODE := G_WARNING;
1438: RETURN;
1439: END IF;
1440:
1501: );
1502:
1503: IF l_new_refresh_number IS NULL THEN
1504: -- No data to summarize
1505: msc_util.msc_log('Load_Net_Plan: ' || 'No data to summarize. Will Exit');
1506: RETCODE := G_WARNING;
1507: RETURN;
1508: END IF;
1509:
1508: END IF;
1509:
1510: IF l_new_refresh_number = l_last_refresh_number THEN
1511: -- No change since last summary
1512: msc_util.msc_log('Load_Net_Plan: ' || 'No change since last summary');
1513: msc_util.msc_log('Load_Net_Plan: ' || 'updating summary flag and refresh number');
1514: update msc_plans
1515: set summary_flag = G_SF_SUMMARY_COMPLETED,
1516: latest_refresh_number = l_new_refresh_number
1509:
1510: IF l_new_refresh_number = l_last_refresh_number THEN
1511: -- No change since last summary
1512: msc_util.msc_log('Load_Net_Plan: ' || 'No change since last summary');
1513: msc_util.msc_log('Load_Net_Plan: ' || 'updating summary flag and refresh number');
1514: update msc_plans
1515: set summary_flag = G_SF_SUMMARY_COMPLETED,
1516: latest_refresh_number = l_new_refresh_number
1517: where plan_id = p_plan_id;
1550: l_last_refresh_number,
1551: l_new_refresh_number);
1552:
1553: BEGIN
1554: msc_util.msc_log ('Load_Net_Plan: ' || 'updating summary flag and refresh number');
1555: update msc_plans
1556: set summary_flag = G_SF_SUMMARY_COMPLETED, -- For summary enhancement
1557: latest_refresh_number = l_new_refresh_number
1558: where plan_id = p_plan_id;
1559: END;
1560:
1561: -- All stats should be gathered in one go as that also causes imlpicit commit - for summary enhancement
1562: l_share_partition := fnd_profile.value('MSC_SHARE_PARTITIONS');
1563: msc_util.msc_log('Load_Net_Plan: ' || 'l_share_partition := ' || l_share_partition);
1564: Gather_Summ_Plan_Stats(p_plan_id, l_share_partition);
1565:
1566: RETCODE := G_SUCCESS;
1567: commit;
1568:
1569:
1570: EXCEPTION
1571: WHEN OTHERS THEN
1572: msc_util.msc_log('Load_Net_Plan: Inside main exception');
1573: msc_util.msc_log(sqlerrm);
1574: ERRBUF := sqlerrm;
1575: RETCODE := G_ERROR;
1576:
1569:
1570: EXCEPTION
1571: WHEN OTHERS THEN
1572: msc_util.msc_log('Load_Net_Plan: Inside main exception');
1573: msc_util.msc_log(sqlerrm);
1574: ERRBUF := sqlerrm;
1575: RETCODE := G_ERROR;
1576:
1577: -- For summary enhancement - Need to rollback incomplete changes
1589: IS
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');
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,
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');
1607: END;
1608: l_sql_stmt := 'TRUNCATE TABLE MSC_TEMP_SUMM_SD';
1609:
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
1607: END;
1608: l_sql_stmt := 'TRUNCATE TABLE MSC_TEMP_SUMM_SD';
1609:
1610: BEGIN
1611: msc_util.msc_log('Tuncate Table MSC_TEMP_SUMM_SD');
1612: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
1613: APPLICATION_SHORT_NAME => 'MSC',
1614: STATEMENT_TYPE => ad_ddl.truncate_table,
1615: STATEMENT => l_sql_stmt,
1615: STATEMENT => l_sql_stmt,
1616: OBJECT_NAME => 'MSC_TEMP_SUMM_SD');
1617: EXCEPTION
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:
1616: OBJECT_NAME => 'MSC_TEMP_SUMM_SD');
1617: EXCEPTION
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
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,
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');
1635: END;
1636:
1637: l_sql_stmt := 'DROP INDEX MSC_TEMP_SUMM_SD_N1';
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:
1636:
1637: l_sql_stmt := 'DROP INDEX MSC_TEMP_SUMM_SD_N1';
1638:
1639: BEGIN
1640: msc_util.msc_log('Drop Index MSC_TEMP_SUMM_SD_N1');
1641: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
1642: APPLICATION_SHORT_NAME => 'MSC',
1643: STATEMENT_TYPE => ad_ddl.drop_index,
1644: STATEMENT => l_sql_stmt,
1644: STATEMENT => l_sql_stmt,
1645: OBJECT_NAME => 'MSC_TEMP_SUMM_SD_N1');
1646: EXCEPTION
1647: WHEN OTHERS THEN
1648: msc_util.msc_log(sqlerrm);
1649: msc_util.msc_log('Drop index MSC_TEMP_SUMM_SD_N1 failed');
1650: END;
1651:
1652: END CLEAN_TABLES;
1645: OBJECT_NAME => 'MSC_TEMP_SUMM_SD_N1');
1646: EXCEPTION
1647: WHEN OTHERS THEN
1648: msc_util.msc_log(sqlerrm);
1649: msc_util.msc_log('Drop index MSC_TEMP_SUMM_SD_N1 failed');
1650: END;
1651:
1652: END CLEAN_TABLES;
1653:
1675: IF p_full_refresh = 1 THEN -- Full refresh - for summary enhancement
1676:
1677: -- Data for share_partition='N' has already been deleted
1678: IF p_share_partition = 'Y' THEN
1679: msc_util.msc_log('INSERT_SUPPLIER_DATA: ' || 'Share partition ');
1680: --- first delete data from the table
1681: msc_util.msc_log('INSERT_SUPPLIER_DATA: ' || 'Delete Data from msc_atp_summary_sup');
1682: delete MSC_ATP_SUMMARY_SUP where plan_id = p_plan_id;
1683: END IF;
1677: -- Data for share_partition='N' has already been deleted
1678: IF p_share_partition = 'Y' THEN
1679: msc_util.msc_log('INSERT_SUPPLIER_DATA: ' || 'Share partition ');
1680: --- first delete data from the table
1681: msc_util.msc_log('INSERT_SUPPLIER_DATA: ' || 'Delete Data from msc_atp_summary_sup');
1682: delete MSC_ATP_SUMMARY_SUP where plan_id = p_plan_id;
1683: END IF;
1684:
1685: msc_util.msc_log('INSERT_SUPPLIER_DATA: ' || 'Loading complete summary in MSC_ATP_SUMMARY_SUP');
1681: msc_util.msc_log('INSERT_SUPPLIER_DATA: ' || 'Delete Data from msc_atp_summary_sup');
1682: delete MSC_ATP_SUMMARY_SUP where plan_id = p_plan_id;
1683: END IF;
1684:
1685: msc_util.msc_log('INSERT_SUPPLIER_DATA: ' || 'Loading complete summary in MSC_ATP_SUMMARY_SUP');
1686: -- The actual SQL is moved to new procedure for summary enhancement
1687: LOAD_SUP_DATA_FULL(p_plan_id, p_sys_date);
1688: msc_util.msc_log('INSERT_SUPPLIER_DATA: ' || 'After loading complete summary in MSC_ATP_SUMMARY_SUP');
1689:
1684:
1685: msc_util.msc_log('INSERT_SUPPLIER_DATA: ' || 'Loading complete summary in MSC_ATP_SUMMARY_SUP');
1686: -- The actual SQL is moved to new procedure for summary enhancement
1687: LOAD_SUP_DATA_FULL(p_plan_id, p_sys_date);
1688: msc_util.msc_log('INSERT_SUPPLIER_DATA: ' || 'After loading complete summary in MSC_ATP_SUMMARY_SUP');
1689:
1690: -- Code to gather stats was here. Removed for summary enhancement as stats are gathered right in the end.
1691:
1692: ELSE
1690: -- Code to gather stats was here. Removed for summary enhancement as stats are gathered right in the end.
1691:
1692: ELSE
1693:
1694: msc_util.msc_log('INSERT_SUPPLIER_DATA: ' || 'Loading net summary in MSC_ATP_SUMMARY_SUP');
1695: LOAD_SUP_DATA_NET(p_plan_id, p_last_refresh_number, p_new_refresh_number, p_sys_date);
1696: msc_util.msc_log('INSERT_SUPPLIER_DATA: ' || 'After loading net summary in MSC_ATP_SUMMARY_SUP');
1697:
1698:
1692: ELSE
1693:
1694: msc_util.msc_log('INSERT_SUPPLIER_DATA: ' || 'Loading net summary in MSC_ATP_SUMMARY_SUP');
1695: LOAD_SUP_DATA_NET(p_plan_id, p_last_refresh_number, p_new_refresh_number, p_sys_date);
1696: msc_util.msc_log('INSERT_SUPPLIER_DATA: ' || 'After loading net summary in MSC_ATP_SUMMARY_SUP');
1697:
1698:
1699: END IF;
1700:
1750: l_user_id number;
1751: BEGIN
1752:
1753: l_MSO_Batch_flag := NVL(fnd_profile.value('MSO_BATCHABLE_FLAG'),'N');
1754: msc_util.msc_log('LOAD_RESOURCES: ' || 'mso batchable flag := ' || l_MSO_Batch_flag );
1755: Begin
1756: SELECT decode(designator_type, 2, 1, 0),
1757: DECODE(plans.plan_type, 4, 2,
1758: DECODE(daily_material_constraints, 1, 1,
1787: AND desig.designator = plans.compile_designator
1788: AND desig.sr_instance_id = plans.sr_instance_id
1789: AND desig.organization_id = plans.organization_id;
1790: EXCEPTION WHEN NO_DATA_FOUND THEN
1791: msc_util.msc_log('LOAD_RESOURCES: ' || 'In Exception : ' || sqlcode || ': ' || sqlerrm);
1792: l_use_bor := 0;
1793: l_optimized_plan := 2;
1794: l_constraint_plan := 0;
1795: END;
1793: l_optimized_plan := 2;
1794: l_constraint_plan := 0;
1795: END;
1796:
1797: msc_util.msc_log('LOAD_RESOURCES: ' || 'l_use_bor := ' || l_use_bor);
1798: msc_util.msc_log('LOAD_RESOURCES: ' || 'l_optimized_plan := ' || l_optimized_plan);
1799: msc_util.msc_log('LOAD_RESOURCES: ' || 'l_constraint_plan := ' || l_constraint_plan);
1800:
1801: IF (l_MSO_Batch_Flag = 'Y') and (l_use_bor = 0) and (l_constraint_plan = 1) THEN
1794: l_constraint_plan := 0;
1795: END;
1796:
1797: msc_util.msc_log('LOAD_RESOURCES: ' || 'l_use_bor := ' || l_use_bor);
1798: msc_util.msc_log('LOAD_RESOURCES: ' || 'l_optimized_plan := ' || l_optimized_plan);
1799: msc_util.msc_log('LOAD_RESOURCES: ' || 'l_constraint_plan := ' || l_constraint_plan);
1800:
1801: IF (l_MSO_Batch_Flag = 'Y') and (l_use_bor = 0) and (l_constraint_plan = 1) THEN
1802: msc_util.msc_log('LOAD_RESOURCES: ' || 'Do Batching');
1795: END;
1796:
1797: msc_util.msc_log('LOAD_RESOURCES: ' || 'l_use_bor := ' || l_use_bor);
1798: msc_util.msc_log('LOAD_RESOURCES: ' || 'l_optimized_plan := ' || l_optimized_plan);
1799: msc_util.msc_log('LOAD_RESOURCES: ' || 'l_constraint_plan := ' || l_constraint_plan);
1800:
1801: IF (l_MSO_Batch_Flag = 'Y') and (l_use_bor = 0) and (l_constraint_plan = 1) THEN
1802: msc_util.msc_log('LOAD_RESOURCES: ' || 'Do Batching');
1803: l_use_batching := 1;
1798: msc_util.msc_log('LOAD_RESOURCES: ' || 'l_optimized_plan := ' || l_optimized_plan);
1799: msc_util.msc_log('LOAD_RESOURCES: ' || 'l_constraint_plan := ' || l_constraint_plan);
1800:
1801: IF (l_MSO_Batch_Flag = 'Y') and (l_use_bor = 0) and (l_constraint_plan = 1) THEN
1802: msc_util.msc_log('LOAD_RESOURCES: ' || 'Do Batching');
1803: l_use_batching := 1;
1804: ELSE
1805: msc_util.msc_log('LOAD_RESOURCES: ' || 'No Batching');
1806: l_use_batching := 0;
1801: IF (l_MSO_Batch_Flag = 'Y') and (l_use_bor = 0) and (l_constraint_plan = 1) THEN
1802: msc_util.msc_log('LOAD_RESOURCES: ' || 'Do Batching');
1803: l_use_batching := 1;
1804: ELSE
1805: msc_util.msc_log('LOAD_RESOURCES: ' || 'No Batching');
1806: l_use_batching := 0;
1807: END IF;
1808: l_sysdate := sysdate;
1809: l_user_id := FND_GLOBAL.USER_ID;
1811: IF p_full_refresh = 1 THEN -- Full refresh - for summary enhancement
1812:
1813: -- Data for share_partition='N' has already been deleted
1814: IF p_share_partition = 'Y' THEN
1815: msc_util.msc_log('LOAD_RESOURCES: ' || 'Share partition ');
1816: --- first delete data from the table
1817: msc_util.msc_log('LOAD_RESOURCES: ' || 'Delete Data from MSC_ATP_SUMMARY_RES');
1818: DELETE MSC_ATP_SUMMARY_RES where plan_id = p_plan_id;
1819: msc_util.msc_log('LOAD_RESOURCES: ' || 'After deleting old resources info');
1813: -- Data for share_partition='N' has already been deleted
1814: IF p_share_partition = 'Y' THEN
1815: msc_util.msc_log('LOAD_RESOURCES: ' || 'Share partition ');
1816: --- first delete data from the table
1817: msc_util.msc_log('LOAD_RESOURCES: ' || 'Delete Data from MSC_ATP_SUMMARY_RES');
1818: DELETE MSC_ATP_SUMMARY_RES where plan_id = p_plan_id;
1819: msc_util.msc_log('LOAD_RESOURCES: ' || 'After deleting old resources info');
1820: END IF;
1821:
1815: msc_util.msc_log('LOAD_RESOURCES: ' || 'Share partition ');
1816: --- first delete data from the table
1817: msc_util.msc_log('LOAD_RESOURCES: ' || 'Delete Data from MSC_ATP_SUMMARY_RES');
1818: DELETE MSC_ATP_SUMMARY_RES where plan_id = p_plan_id;
1819: msc_util.msc_log('LOAD_RESOURCES: ' || 'After deleting old resources info');
1820: END IF;
1821:
1822: -- The actual SQLs moved to private procedures for modularity : summary enhancement
1823: IF l_use_batching = 1 THEN
1820: END IF;
1821:
1822: -- The actual SQLs moved to private procedures for modularity : summary enhancement
1823: IF l_use_batching = 1 THEN
1824: msc_util.msc_log('LOAD_RESOURCES: ' || 'Doing Batching');
1825:
1826: --2859130
1827: IF nvl(l_optimized_plan, 2) <> 1 THEN
1828: msc_util.msc_log('LOAD_RESOURCES: ' || 'Unconstrained plan.');
1824: msc_util.msc_log('LOAD_RESOURCES: ' || 'Doing Batching');
1825:
1826: --2859130
1827: IF nvl(l_optimized_plan, 2) <> 1 THEN
1828: msc_util.msc_log('LOAD_RESOURCES: ' || 'Unconstrained plan.');
1829: msc_util.msc_log('LOAD_RESOURCES: ' || 'Insert data into res table');
1830:
1831: load_res_full_unopt_batch(p_plan_id, p_plan_start_date, p_sys_date);
1832:
1825:
1826: --2859130
1827: IF nvl(l_optimized_plan, 2) <> 1 THEN
1828: msc_util.msc_log('LOAD_RESOURCES: ' || 'Unconstrained plan.');
1829: msc_util.msc_log('LOAD_RESOURCES: ' || 'Insert data into res table');
1830:
1831: load_res_full_unopt_batch(p_plan_id, p_plan_start_date, p_sys_date);
1832:
1833: msc_util.msc_log('LOAD_RESOURCES: ' || 'After inserting into MSC_ATP_SUMMARY_RES');
1829: msc_util.msc_log('LOAD_RESOURCES: ' || 'Insert data into res table');
1830:
1831: load_res_full_unopt_batch(p_plan_id, p_plan_start_date, p_sys_date);
1832:
1833: msc_util.msc_log('LOAD_RESOURCES: ' || 'After inserting into MSC_ATP_SUMMARY_RES');
1834: ELSE
1835: -- constrained plan
1836: msc_util.msc_log('LOAD_RESOURCES: ' || 'Constrained plan.');
1837: msc_util.msc_log('LOAD_RESOURCES: ' || 'Insert data into res table');
1832:
1833: msc_util.msc_log('LOAD_RESOURCES: ' || 'After inserting into MSC_ATP_SUMMARY_RES');
1834: ELSE
1835: -- constrained plan
1836: msc_util.msc_log('LOAD_RESOURCES: ' || 'Constrained plan.');
1837: msc_util.msc_log('LOAD_RESOURCES: ' || 'Insert data into res table');
1838:
1839: load_res_full_opt_batch(p_plan_id, p_plan_start_date, p_sys_date);
1840:
1833: msc_util.msc_log('LOAD_RESOURCES: ' || 'After inserting into MSC_ATP_SUMMARY_RES');
1834: ELSE
1835: -- constrained plan
1836: msc_util.msc_log('LOAD_RESOURCES: ' || 'Constrained plan.');
1837: msc_util.msc_log('LOAD_RESOURCES: ' || 'Insert data into res table');
1838:
1839: load_res_full_opt_batch(p_plan_id, p_plan_start_date, p_sys_date);
1840:
1841: msc_util.msc_log('LOAD_RESOURCES: ' || 'After inserting into MSC_ATP_SUMMARY_RES');
1837: msc_util.msc_log('LOAD_RESOURCES: ' || 'Insert data into res table');
1838:
1839: load_res_full_opt_batch(p_plan_id, p_plan_start_date, p_sys_date);
1840:
1841: msc_util.msc_log('LOAD_RESOURCES: ' || 'After inserting into MSC_ATP_SUMMARY_RES');
1842: END IF;
1843:
1844: ELSE --- if l_use_batching =1
1845: msc_util.msc_log('LOAD_RESOURCES: ' || 'Not doing Batching');
1841: msc_util.msc_log('LOAD_RESOURCES: ' || 'After inserting into MSC_ATP_SUMMARY_RES');
1842: END IF;
1843:
1844: ELSE --- if l_use_batching =1
1845: msc_util.msc_log('LOAD_RESOURCES: ' || 'Not doing Batching');
1846:
1847: --2859130
1848: IF nvl(l_optimized_plan, 2) <> 1 THEN
1849: msc_util.msc_log('LOAD_RESOURCES: ' || 'Unconstrained plan.');
1845: msc_util.msc_log('LOAD_RESOURCES: ' || 'Not doing Batching');
1846:
1847: --2859130
1848: IF nvl(l_optimized_plan, 2) <> 1 THEN
1849: msc_util.msc_log('LOAD_RESOURCES: ' || 'Unconstrained plan.');
1850: msc_util.msc_log('LOAD_RESOURCES: ' || 'Insert data into res table');
1851:
1852: load_res_full_unopt_nobatch(p_plan_id, p_plan_start_date, p_sys_date);
1853:
1846:
1847: --2859130
1848: IF nvl(l_optimized_plan, 2) <> 1 THEN
1849: msc_util.msc_log('LOAD_RESOURCES: ' || 'Unconstrained plan.');
1850: msc_util.msc_log('LOAD_RESOURCES: ' || 'Insert data into res table');
1851:
1852: load_res_full_unopt_nobatch(p_plan_id, p_plan_start_date, p_sys_date);
1853:
1854: msc_util.msc_log('LOAD_RESOURCES: ' || 'After inserting into MSC_ATP_SUMMARY_RES');
1850: msc_util.msc_log('LOAD_RESOURCES: ' || 'Insert data into res table');
1851:
1852: load_res_full_unopt_nobatch(p_plan_id, p_plan_start_date, p_sys_date);
1853:
1854: msc_util.msc_log('LOAD_RESOURCES: ' || 'After inserting into MSC_ATP_SUMMARY_RES');
1855: ELSE
1856: -- 2859130 constrained plan
1857: msc_util.msc_log('LOAD_RESOURCES: ' || 'Constrained plan.');
1858: msc_util.msc_log('LOAD_RESOURCES: ' || 'Insert data into res table');
1853:
1854: msc_util.msc_log('LOAD_RESOURCES: ' || 'After inserting into MSC_ATP_SUMMARY_RES');
1855: ELSE
1856: -- 2859130 constrained plan
1857: msc_util.msc_log('LOAD_RESOURCES: ' || 'Constrained plan.');
1858: msc_util.msc_log('LOAD_RESOURCES: ' || 'Insert data into res table');
1859:
1860: load_res_full_opt_nobatch(p_plan_id, p_plan_start_date, p_sys_date);
1861:
1854: msc_util.msc_log('LOAD_RESOURCES: ' || 'After inserting into MSC_ATP_SUMMARY_RES');
1855: ELSE
1856: -- 2859130 constrained plan
1857: msc_util.msc_log('LOAD_RESOURCES: ' || 'Constrained plan.');
1858: msc_util.msc_log('LOAD_RESOURCES: ' || 'Insert data into res table');
1859:
1860: load_res_full_opt_nobatch(p_plan_id, p_plan_start_date, p_sys_date);
1861:
1862: msc_util.msc_log('LOAD_RESOURCES: ' || 'After inserting into MSC_ATP_SUMMARY_RES');
1858: msc_util.msc_log('LOAD_RESOURCES: ' || 'Insert data into res table');
1859:
1860: load_res_full_opt_nobatch(p_plan_id, p_plan_start_date, p_sys_date);
1861:
1862: msc_util.msc_log('LOAD_RESOURCES: ' || 'After inserting into MSC_ATP_SUMMARY_RES');
1863: END IF;
1864:
1865: END IF; --- if l_use_batching = 1
1866:
1868:
1869: ELSE -- Net refresh - for summary enhancement
1870: -- IF p_full_refresh = 1 THEN -- Full refresh - for summary enhancement
1871:
1872: msc_util.msc_log('LOAD_RESOURCES: ' || 'Loading net summary in MSC_ATP_SUMMARY_RES');
1873: -- since at this point we need to bother only about ATP generated records we dont look for opt/unopt or batching
1874: load_res_data_net(p_plan_id, p_last_refresh_number, p_new_refresh_number, p_sys_date);
1875: msc_util.msc_log('LOAD_RESOURCES: ' || 'After loading net summary in MSC_ATP_SUMMARY_RES');
1876:
1871:
1872: msc_util.msc_log('LOAD_RESOURCES: ' || 'Loading net summary in MSC_ATP_SUMMARY_RES');
1873: -- since at this point we need to bother only about ATP generated records we dont look for opt/unopt or batching
1874: load_res_data_net(p_plan_id, p_last_refresh_number, p_new_refresh_number, p_sys_date);
1875: msc_util.msc_log('LOAD_RESOURCES: ' || 'After loading net summary in MSC_ATP_SUMMARY_RES');
1876:
1877: END IF;
1878: END LOAD_RESOURCES;
1879:
1885:
1886: l_sql_stmt := 'TRUNCATE TABLE MSC_TEMP_SUMM_SD';
1887:
1888: BEGIN
1889: msc_util.msc_log('Tuncate Table MSC_TEMP_SUMM_SD');
1890: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
1891: APPLICATION_SHORT_NAME => 'MSC',
1892: STATEMENT_TYPE => ad_ddl.truncate_table,
1893: STATEMENT => l_sql_stmt,
1893: STATEMENT => l_sql_stmt,
1894: OBJECT_NAME => 'MSC_TEMP_SUMM_SD');
1895: EXCEPTION
1896: WHEN OTHERS THEN
1897: msc_util.msc_log(sqlerrm);
1898: msc_util.msc_log('Truncate table failed');
1899: END;
1900:
1901: l_sql_stmt := 'DROP INDEX MSC_TEMP_SUMM_SD_N1';
1894: OBJECT_NAME => 'MSC_TEMP_SUMM_SD');
1895: EXCEPTION
1896: WHEN OTHERS THEN
1897: msc_util.msc_log(sqlerrm);
1898: msc_util.msc_log('Truncate table failed');
1899: END;
1900:
1901: l_sql_stmt := 'DROP INDEX MSC_TEMP_SUMM_SD_N1';
1902:
1900:
1901: l_sql_stmt := 'DROP INDEX MSC_TEMP_SUMM_SD_N1';
1902:
1903: BEGIN
1904: msc_util.msc_log('Drop Index MSC_TEMP_SUMM_SD_N1');
1905: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
1906: APPLICATION_SHORT_NAME => 'MSC',
1907: STATEMENT_TYPE => ad_ddl.drop_index,
1908: STATEMENT => l_sql_stmt,
1908: STATEMENT => l_sql_stmt,
1909: OBJECT_NAME => 'MSC_TEMP_SUMM_SD_N1');
1910: EXCEPTION
1911: WHEN OTHERS THEN
1912: msc_util.msc_log(sqlerrm);
1913: msc_util.msc_log('Truncate table failed');
1914: END;
1915:
1916: l_sql_stmt := 'TRUNCATE TABLE MSC_TEMP_SUMM_SUP';
1909: OBJECT_NAME => 'MSC_TEMP_SUMM_SD_N1');
1910: EXCEPTION
1911: WHEN OTHERS THEN
1912: msc_util.msc_log(sqlerrm);
1913: msc_util.msc_log('Truncate table failed');
1914: END;
1915:
1916: l_sql_stmt := 'TRUNCATE TABLE MSC_TEMP_SUMM_SUP';
1917:
1915:
1916: l_sql_stmt := 'TRUNCATE TABLE MSC_TEMP_SUMM_SUP';
1917:
1918: BEGIN
1919: msc_util.msc_log('Tuncate Table MSC_TEMP_SUMM_SUP');
1920: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
1921: APPLICATION_SHORT_NAME => 'MSC',
1922: STATEMENT_TYPE => ad_ddl.truncate_table,
1923: STATEMENT => l_sql_stmt,
1923: STATEMENT => l_sql_stmt,
1924: OBJECT_NAME => 'MSC_TEMP_SUMM_SUP');
1925: EXCEPTION
1926: WHEN OTHERS THEN
1927: msc_util.msc_log(sqlerrm);
1928: msc_util.msc_log('Truncate table MSC_TEMP_SUMM_SUP failed');
1929: END;
1930:
1931: l_sql_stmt := 'DROP INDEX MSC_TEMP_SUMM_SUP_N1';
1924: OBJECT_NAME => 'MSC_TEMP_SUMM_SUP');
1925: EXCEPTION
1926: WHEN OTHERS THEN
1927: msc_util.msc_log(sqlerrm);
1928: msc_util.msc_log('Truncate table MSC_TEMP_SUMM_SUP failed');
1929: END;
1930:
1931: l_sql_stmt := 'DROP INDEX MSC_TEMP_SUMM_SUP_N1';
1932:
1930:
1931: l_sql_stmt := 'DROP INDEX MSC_TEMP_SUMM_SUP_N1';
1932:
1933: BEGIN
1934: msc_util.msc_log('Drop Index MSC_TEMP_SUMM_SUP_N1');
1935: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
1936: APPLICATION_SHORT_NAME => 'MSC',
1937: STATEMENT_TYPE => ad_ddl.drop_index,
1938: STATEMENT => l_sql_stmt,
1938: STATEMENT => l_sql_stmt,
1939: OBJECT_NAME => 'MSC_TEMP_SUMM_SUP_N1');
1940: EXCEPTION
1941: WHEN OTHERS THEN
1942: msc_util.msc_log(sqlerrm);
1943: msc_util.msc_log('Drop Index MSC_TEMP_SUMM_SUP_N1 failed');
1944: END;
1945:
1946: l_sql_stmt := 'TRUNCATE TABLE MSC_TEMP_SUMM_RES';
1939: OBJECT_NAME => 'MSC_TEMP_SUMM_SUP_N1');
1940: EXCEPTION
1941: WHEN OTHERS THEN
1942: msc_util.msc_log(sqlerrm);
1943: msc_util.msc_log('Drop Index MSC_TEMP_SUMM_SUP_N1 failed');
1944: END;
1945:
1946: l_sql_stmt := 'TRUNCATE TABLE MSC_TEMP_SUMM_RES';
1947:
1945:
1946: l_sql_stmt := 'TRUNCATE TABLE MSC_TEMP_SUMM_RES';
1947:
1948: BEGIN
1949: msc_util.msc_log('Tuncate Table MSC_TEMP_SUMM_RES');
1950: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
1951: APPLICATION_SHORT_NAME => 'MSC',
1952: STATEMENT_TYPE => ad_ddl.truncate_table,
1953: STATEMENT => l_sql_stmt,
1953: STATEMENT => l_sql_stmt,
1954: OBJECT_NAME => 'MSC_TEMP_SUMM_RES');
1955: EXCEPTION
1956: WHEN OTHERS THEN
1957: msc_util.msc_log(sqlerrm);
1958: msc_util.msc_log('Truncate table MSC_TEMP_SUMM_RES failed');
1959: END;
1960:
1961: l_sql_stmt := 'DROP INDEX MSC_TEMP_SUMM_RES_N1';
1954: OBJECT_NAME => 'MSC_TEMP_SUMM_RES');
1955: EXCEPTION
1956: WHEN OTHERS THEN
1957: msc_util.msc_log(sqlerrm);
1958: msc_util.msc_log('Truncate table MSC_TEMP_SUMM_RES failed');
1959: END;
1960:
1961: l_sql_stmt := 'DROP INDEX MSC_TEMP_SUMM_RES_N1';
1962:
1960:
1961: l_sql_stmt := 'DROP INDEX MSC_TEMP_SUMM_RES_N1';
1962:
1963: BEGIN
1964: msc_util.msc_log('Drop Index MSC_TEMP_SUMM_RES_N1');
1965: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
1966: APPLICATION_SHORT_NAME => 'MSC',
1967: STATEMENT_TYPE => ad_ddl.drop_index,
1968: STATEMENT => l_sql_stmt,
1968: STATEMENT => l_sql_stmt,
1969: OBJECT_NAME => 'MSC_TEMP_SUMM_RES_N1');
1970: EXCEPTION
1971: WHEN OTHERS THEN
1972: msc_util.msc_log(sqlerrm);
1973: msc_util.msc_log('Drop Index MSC_TEMP_SUMM_RES_N1 failed');
1974: END;
1975: END Clean_Plan_Tables;
1976:
1969: OBJECT_NAME => 'MSC_TEMP_SUMM_RES_N1');
1970: EXCEPTION
1971: WHEN OTHERS THEN
1972: msc_util.msc_log(sqlerrm);
1973: msc_util.msc_log('Drop Index MSC_TEMP_SUMM_RES_N1 failed');
1974: END;
1975: END Clean_Plan_Tables;
1976:
1977:
2338: l_user_id := FND_GLOBAL.USER_ID;
2339: l_inv_ctp := NVL(FND_PROFILE.value('INV_CTP'), 5);
2340: l_enable_summary_mode := NVL(FND_PROFILE.value('MSC_ENABLE_ATP_SUMMARY'), 'N');
2341:
2342: msc_util.msc_log('inv_ctp := ' || l_inv_ctp);
2343: msc_util.msc_log(' l_enable_summary_mode := ' || l_enable_summary_mode);
2344:
2345: IF l_enable_summary_mode <> 'Y' THEN
2346: msc_util.msc_log('Summary Mode is not enabled. Please enable Summary mode to run this program');
2339: l_inv_ctp := NVL(FND_PROFILE.value('INV_CTP'), 5);
2340: l_enable_summary_mode := NVL(FND_PROFILE.value('MSC_ENABLE_ATP_SUMMARY'), 'N');
2341:
2342: msc_util.msc_log('inv_ctp := ' || l_inv_ctp);
2343: msc_util.msc_log(' l_enable_summary_mode := ' || l_enable_summary_mode);
2344:
2345: IF l_enable_summary_mode <> 'Y' THEN
2346: msc_util.msc_log('Summary Mode is not enabled. Please enable Summary mode to run this program');
2347: RETCODE := G_WARNING;
2342: msc_util.msc_log('inv_ctp := ' || l_inv_ctp);
2343: msc_util.msc_log(' l_enable_summary_mode := ' || l_enable_summary_mode);
2344:
2345: IF l_enable_summary_mode <> 'Y' THEN
2346: msc_util.msc_log('Summary Mode is not enabled. Please enable Summary mode to run this program');
2347: RETCODE := G_WARNING;
2348: RETURN;
2349: END IF;
2350:
2349: END IF;
2350:
2351: IF l_inv_ctp = 4 THEN
2352: -- we are not doing ODS ATP so we wont continue
2353: msc_util.msc_log('Not Doing ODS ATP. Please check profile - INV: Capable to Promise. Will Exit ');
2354: RETCODE := G_WARNING;
2355: RETURN;
2356: ELSIF l_inv_ctp <> 5 THEN
2357: l_inv_ctp := 5;
2362: from msc_apps_instances
2363: where instance_id = p_instance_id;
2364:
2365: IF NVL(l_summary_flag, 1) = 2 THEN
2366: msc_util.msc_log('Another session is running Complete summary for this instance');
2367: RETCODE := G_ERROR;
2368: RETURN;
2369: ELSIF (NVL(l_summary_flag, 1) = 1) THEN
2370: msc_util.msc_log('Partial summary of tables can be done only after complete summary has been run successfuly');
2366: msc_util.msc_log('Another session is running Complete summary for this instance');
2367: RETCODE := G_ERROR;
2368: RETURN;
2369: ELSIF (NVL(l_summary_flag, 1) = 1) THEN
2370: msc_util.msc_log('Partial summary of tables can be done only after complete summary has been run successfuly');
2371: RETCODE := G_ERROR;
2372: RETURN;
2373: END IF;
2374:
2371: RETCODE := G_ERROR;
2372: RETURN;
2373: END IF;
2374:
2375: msc_util.msc_log('l_refresh_number := ' || l_refresh_number);
2376:
2377: /* SELECT sr_tp_id
2378: BULK COLLECT INTO l_org_ids
2379: FROM msc_trading_partners
2385: WHERE sr_instance_id = p_instance_id
2386: and org_lrn=l_apps_lrn
2387: and enabled_flag=1;
2388:
2389: msc_util.msc_log(' org count := ' || l_org_ids.count);
2390:
2391: -- Update summary record per organization
2392: FOR i in 1..l_org_ids.count LOOP
2393: l_organization_id := l_org_ids(i);
2390:
2391: -- Update summary record per organization
2392: FOR i in 1..l_org_ids.count LOOP
2393: l_organization_id := l_org_ids(i);
2394: msc_util.msc_log('processing org '|| i ||' := '||l_organization_id);
2395:
2396: MSC_ATP_PROC.get_org_default_info(p_instance_id,
2397: l_organization_id,
2398: l_default_atp_rule_id,
2400: l_calendar_exception_set_id,
2401: l_default_demand_class,
2402: l_org_code);
2403:
2404: msc_util.msc_log('l_calendar_code := ' || l_calendar_code);
2405: msc_util.msc_log('l_calendar_exception_set_id := ' || l_calendar_exception_set_id);
2406: msc_util.msc_log('l_default_atp_rule_id := ' || l_default_atp_rule_id);
2407: msc_util.msc_log('l_default_demand_class := ' || l_default_demand_class);
2408: BEGIN
2401: l_default_demand_class,
2402: l_org_code);
2403:
2404: msc_util.msc_log('l_calendar_code := ' || l_calendar_code);
2405: msc_util.msc_log('l_calendar_exception_set_id := ' || l_calendar_exception_set_id);
2406: msc_util.msc_log('l_default_atp_rule_id := ' || l_default_atp_rule_id);
2407: msc_util.msc_log('l_default_demand_class := ' || l_default_demand_class);
2408: BEGIN
2409: SELECT cal.next_date
2402: l_org_code);
2403:
2404: msc_util.msc_log('l_calendar_code := ' || l_calendar_code);
2405: msc_util.msc_log('l_calendar_exception_set_id := ' || l_calendar_exception_set_id);
2406: msc_util.msc_log('l_default_atp_rule_id := ' || l_default_atp_rule_id);
2407: msc_util.msc_log('l_default_demand_class := ' || l_default_demand_class);
2408: BEGIN
2409: SELECT cal.next_date
2410: INTO l_sys_next_date
2403:
2404: msc_util.msc_log('l_calendar_code := ' || l_calendar_code);
2405: msc_util.msc_log('l_calendar_exception_set_id := ' || l_calendar_exception_set_id);
2406: msc_util.msc_log('l_default_atp_rule_id := ' || l_default_atp_rule_id);
2407: msc_util.msc_log('l_default_demand_class := ' || l_default_demand_class);
2408: BEGIN
2409: SELECT cal.next_date
2410: INTO l_sys_next_date
2411: FROM msc_calendar_dates cal
2422: l_instance_id ,
2423: l_organization_id ,
2424: l_calendar_code ,
2425: l_calendar_exception_set_id );
2426: msc_util.msc_log('after opening cursor NET_CHANGE_SO_NEW');
2427:
2428: LOOP
2429: FETCH NET_CHANGE_SO_NEW INTO
2430: l_organization_id,
2434: l_sd_qty;
2435:
2436: EXIT WHEN NET_CHANGE_SO_NEW%NOTFOUND;
2437:
2438: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2439: msc_util.msc_log('l_demand_class := ' || l_organization_id);
2440: msc_util.msc_log('l_sd_date := ' || l_sd_date);
2441: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
2442: msc_util.msc_log('l_old_sd_date := ' || l_old_sd_date);
2435:
2436: EXIT WHEN NET_CHANGE_SO_NEW%NOTFOUND;
2437:
2438: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2439: msc_util.msc_log('l_demand_class := ' || l_organization_id);
2440: msc_util.msc_log('l_sd_date := ' || l_sd_date);
2441: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
2442: msc_util.msc_log('l_old_sd_date := ' || l_old_sd_date);
2443: msc_util.msc_log('l_old_sd_qty := ' || l_old_sd_qty);
2436: EXIT WHEN NET_CHANGE_SO_NEW%NOTFOUND;
2437:
2438: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2439: msc_util.msc_log('l_demand_class := ' || l_organization_id);
2440: msc_util.msc_log('l_sd_date := ' || l_sd_date);
2441: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
2442: msc_util.msc_log('l_old_sd_date := ' || l_old_sd_date);
2443: msc_util.msc_log('l_old_sd_qty := ' || l_old_sd_qty);
2444: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
2437:
2438: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2439: msc_util.msc_log('l_demand_class := ' || l_organization_id);
2440: msc_util.msc_log('l_sd_date := ' || l_sd_date);
2441: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
2442: msc_util.msc_log('l_old_sd_date := ' || l_old_sd_date);
2443: msc_util.msc_log('l_old_sd_qty := ' || l_old_sd_qty);
2444: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
2445:
2438: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2439: msc_util.msc_log('l_demand_class := ' || l_organization_id);
2440: msc_util.msc_log('l_sd_date := ' || l_sd_date);
2441: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
2442: msc_util.msc_log('l_old_sd_date := ' || l_old_sd_date);
2443: msc_util.msc_log('l_old_sd_qty := ' || l_old_sd_qty);
2444: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
2445:
2446: --- With 9i the entire set can be accomplished in one MERGE statement.
2439: msc_util.msc_log('l_demand_class := ' || l_organization_id);
2440: msc_util.msc_log('l_sd_date := ' || l_sd_date);
2441: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
2442: msc_util.msc_log('l_old_sd_date := ' || l_old_sd_date);
2443: msc_util.msc_log('l_old_sd_qty := ' || l_old_sd_qty);
2444: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
2445:
2446: --- With 9i the entire set can be accomplished in one MERGE statement.
2447: --- Insert the new record
2440: msc_util.msc_log('l_sd_date := ' || l_sd_date);
2441: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
2442: msc_util.msc_log('l_old_sd_date := ' || l_old_sd_date);
2443: msc_util.msc_log('l_old_sd_qty := ' || l_old_sd_qty);
2444: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
2445:
2446: --- With 9i the entire set can be accomplished in one MERGE statement.
2447: --- Insert the new record
2448: BEGIN
2484:
2485: END LOOP; --- end of fetch loop
2486:
2487: CLOSE NET_CHANGE_SO_NEW;
2488: msc_util.msc_log('l_refresh_number := ' || l_refresh_number);
2489: msc_util.msc_log('l_instance_id := ' || l_instance_id);
2490: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2491:
2492: ----bug 5357370,We will remove the unreserved quantity from sysdate and add the quantity to the schedule date
2485: END LOOP; --- end of fetch loop
2486:
2487: CLOSE NET_CHANGE_SO_NEW;
2488: msc_util.msc_log('l_refresh_number := ' || l_refresh_number);
2489: msc_util.msc_log('l_instance_id := ' || l_instance_id);
2490: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2491:
2492: ----bug 5357370,We will remove the unreserved quantity from sysdate and add the quantity to the schedule date
2493: OPEN NET_CHANGE_UNRESRV(l_refresh_number ,
2486:
2487: CLOSE NET_CHANGE_SO_NEW;
2488: msc_util.msc_log('l_refresh_number := ' || l_refresh_number);
2489: msc_util.msc_log('l_instance_id := ' || l_instance_id);
2490: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2491:
2492: ----bug 5357370,We will remove the unreserved quantity from sysdate and add the quantity to the schedule date
2493: OPEN NET_CHANGE_UNRESRV(l_refresh_number ,
2494: l_instance_id ,
2493: OPEN NET_CHANGE_UNRESRV(l_refresh_number ,
2494: l_instance_id ,
2495: l_organization_id
2496: ) ;
2497: msc_util.msc_log('after opening cursor NET_CHANGE_UNRESRV');
2498: msc_util.msc_log('l_refresh_number := ' || l_refresh_number);
2499: msc_util.msc_log('l_instance_id := ' || l_instance_id);
2500: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2501:
2494: l_instance_id ,
2495: l_organization_id
2496: ) ;
2497: msc_util.msc_log('after opening cursor NET_CHANGE_UNRESRV');
2498: msc_util.msc_log('l_refresh_number := ' || l_refresh_number);
2499: msc_util.msc_log('l_instance_id := ' || l_instance_id);
2500: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2501:
2502: LOOP
2495: l_organization_id
2496: ) ;
2497: msc_util.msc_log('after opening cursor NET_CHANGE_UNRESRV');
2498: msc_util.msc_log('l_refresh_number := ' || l_refresh_number);
2499: msc_util.msc_log('l_instance_id := ' || l_instance_id);
2500: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2501:
2502: LOOP
2503: FETCH NET_CHANGE_UNRESRV INTO
2496: ) ;
2497: msc_util.msc_log('after opening cursor NET_CHANGE_UNRESRV');
2498: msc_util.msc_log('l_refresh_number := ' || l_refresh_number);
2499: msc_util.msc_log('l_instance_id := ' || l_instance_id);
2500: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2501:
2502: LOOP
2503: FETCH NET_CHANGE_UNRESRV INTO
2504: l_organization_id,
2508: l_sd_qty;
2509:
2510: EXIT WHEN NET_CHANGE_UNRESRV%NOTFOUND;
2511:
2512: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2513: msc_util.msc_log('l_demand_class := ' || l_demand_class);
2514: msc_util.msc_log('l_sd_date := ' || l_sd_date);
2515: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
2516: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
2509:
2510: EXIT WHEN NET_CHANGE_UNRESRV%NOTFOUND;
2511:
2512: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2513: msc_util.msc_log('l_demand_class := ' || l_demand_class);
2514: msc_util.msc_log('l_sd_date := ' || l_sd_date);
2515: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
2516: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
2517:
2510: EXIT WHEN NET_CHANGE_UNRESRV%NOTFOUND;
2511:
2512: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2513: msc_util.msc_log('l_demand_class := ' || l_demand_class);
2514: msc_util.msc_log('l_sd_date := ' || l_sd_date);
2515: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
2516: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
2517:
2518: UPDATE MSC_ATP_SUMMARY_SO
2511:
2512: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2513: msc_util.msc_log('l_demand_class := ' || l_demand_class);
2514: msc_util.msc_log('l_sd_date := ' || l_sd_date);
2515: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
2516: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
2517:
2518: UPDATE MSC_ATP_SUMMARY_SO
2519: SET sd_qty = sd_qty + l_sd_qty, -- APPLY THE DELTA
2512: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2513: msc_util.msc_log('l_demand_class := ' || l_demand_class);
2514: msc_util.msc_log('l_sd_date := ' || l_sd_date);
2515: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
2516: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
2517:
2518: UPDATE MSC_ATP_SUMMARY_SO
2519: SET sd_qty = sd_qty + l_sd_qty, -- APPLY THE DELTA
2520: last_update_date = l_sysdate,
2634: l_instance_id ,
2635: l_organization_id
2636: ) ;
2637:
2638: msc_util.msc_log('after opening cursor NET_CHANGE_RESRV');
2639: msc_util.msc_log('l_refresh_number := ' || l_refresh_number);
2640: msc_util.msc_log('l_instance_id := ' || l_instance_id);
2641: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2642:
2635: l_organization_id
2636: ) ;
2637:
2638: msc_util.msc_log('after opening cursor NET_CHANGE_RESRV');
2639: msc_util.msc_log('l_refresh_number := ' || l_refresh_number);
2640: msc_util.msc_log('l_instance_id := ' || l_instance_id);
2641: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2642:
2643: LOOP
2636: ) ;
2637:
2638: msc_util.msc_log('after opening cursor NET_CHANGE_RESRV');
2639: msc_util.msc_log('l_refresh_number := ' || l_refresh_number);
2640: msc_util.msc_log('l_instance_id := ' || l_instance_id);
2641: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2642:
2643: LOOP
2644: FETCH NET_CHANGE_RESRV INTO
2637:
2638: msc_util.msc_log('after opening cursor NET_CHANGE_RESRV');
2639: msc_util.msc_log('l_refresh_number := ' || l_refresh_number);
2640: msc_util.msc_log('l_instance_id := ' || l_instance_id);
2641: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2642:
2643: LOOP
2644: FETCH NET_CHANGE_RESRV INTO
2645: l_organization_id,
2649: l_sd_qty;
2650:
2651: EXIT WHEN NET_CHANGE_RESRV%NOTFOUND;
2652:
2653: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2654: msc_util.msc_log('l_demand_class := ' || l_demand_class);
2655: msc_util.msc_log('l_sd_date := ' || l_sd_date);
2656: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
2657: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
2650:
2651: EXIT WHEN NET_CHANGE_RESRV%NOTFOUND;
2652:
2653: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2654: msc_util.msc_log('l_demand_class := ' || l_demand_class);
2655: msc_util.msc_log('l_sd_date := ' || l_sd_date);
2656: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
2657: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
2658:
2651: EXIT WHEN NET_CHANGE_RESRV%NOTFOUND;
2652:
2653: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2654: msc_util.msc_log('l_demand_class := ' || l_demand_class);
2655: msc_util.msc_log('l_sd_date := ' || l_sd_date);
2656: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
2657: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
2658:
2659: UPDATE MSC_ATP_SUMMARY_SO
2652:
2653: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2654: msc_util.msc_log('l_demand_class := ' || l_demand_class);
2655: msc_util.msc_log('l_sd_date := ' || l_sd_date);
2656: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
2657: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
2658:
2659: UPDATE MSC_ATP_SUMMARY_SO
2660: SET sd_qty = sd_qty + l_sd_qty, -- APPLY THE DELTA
2653: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2654: msc_util.msc_log('l_demand_class := ' || l_demand_class);
2655: msc_util.msc_log('l_sd_date := ' || l_sd_date);
2656: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
2657: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
2658:
2659: UPDATE MSC_ATP_SUMMARY_SO
2660: SET sd_qty = sd_qty + l_sd_qty, -- APPLY THE DELTA
2661: last_update_date = l_sysdate,
2719: l_instance_id,
2720: l_organization_id,
2721: l_calendar_code,
2722: l_calendar_exception_set_id );
2723: msc_util.msc_log('after opening cursor NET_CHANGE_SO_RSRV');
2724: msc_util.msc_log('l_calendar_code := ' || l_calendar_code);
2725: msc_util.msc_log('l_calendar_exception_set_id := ' ||
2726: TO_CHAR(l_calendar_exception_set_id));
2727:
2720: l_organization_id,
2721: l_calendar_code,
2722: l_calendar_exception_set_id );
2723: msc_util.msc_log('after opening cursor NET_CHANGE_SO_RSRV');
2724: msc_util.msc_log('l_calendar_code := ' || l_calendar_code);
2725: msc_util.msc_log('l_calendar_exception_set_id := ' ||
2726: TO_CHAR(l_calendar_exception_set_id));
2727:
2728:
2721: l_calendar_code,
2722: l_calendar_exception_set_id );
2723: msc_util.msc_log('after opening cursor NET_CHANGE_SO_RSRV');
2724: msc_util.msc_log('l_calendar_code := ' || l_calendar_code);
2725: msc_util.msc_log('l_calendar_exception_set_id := ' ||
2726: TO_CHAR(l_calendar_exception_set_id));
2727:
2728:
2729: LOOP
2735: l_sd_qty;
2736:
2737: EXIT WHEN NET_CHANGE_SO_RSRV%NOTFOUND;
2738:
2739: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2740: msc_util.msc_log('l_demand_class := ' || l_demand_class);
2741: msc_util.msc_log('l_sd_date := ' || l_sd_date);
2742: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
2743: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
2736:
2737: EXIT WHEN NET_CHANGE_SO_RSRV%NOTFOUND;
2738:
2739: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2740: msc_util.msc_log('l_demand_class := ' || l_demand_class);
2741: msc_util.msc_log('l_sd_date := ' || l_sd_date);
2742: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
2743: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
2744:
2737: EXIT WHEN NET_CHANGE_SO_RSRV%NOTFOUND;
2738:
2739: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2740: msc_util.msc_log('l_demand_class := ' || l_demand_class);
2741: msc_util.msc_log('l_sd_date := ' || l_sd_date);
2742: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
2743: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
2744:
2745:
2738:
2739: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2740: msc_util.msc_log('l_demand_class := ' || l_demand_class);
2741: msc_util.msc_log('l_sd_date := ' || l_sd_date);
2742: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
2743: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
2744:
2745:
2746: --- With 9i the entire set can be accomplished in one MERGE statement.
2739: msc_util.msc_log('l_organization_id := ' || l_organization_id);
2740: msc_util.msc_log('l_demand_class := ' || l_demand_class);
2741: msc_util.msc_log('l_sd_date := ' || l_sd_date);
2742: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
2743: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
2744:
2745:
2746: --- With 9i the entire set can be accomplished in one MERGE statement.
2747: --- Try to update it first and then
2823: --- update summary flag in msc_apps_instances so that summary not available to use
2824: update msc_apps_instances
2825: set summary_flag = 1
2826: where instance_id = p_instance_id;
2827: msc_util.msc_log('An error occured while running net change on Sales Orders');
2828: msc_util.msc_log('Complete refresh would need to be run to activate Summary ATP');
2829: msc_util.msc_log('Inside main exception');
2830: msc_util.msc_log(sqlerrm);
2831: ERRBUF := sqlerrm;
2824: update msc_apps_instances
2825: set summary_flag = 1
2826: where instance_id = p_instance_id;
2827: msc_util.msc_log('An error occured while running net change on Sales Orders');
2828: msc_util.msc_log('Complete refresh would need to be run to activate Summary ATP');
2829: msc_util.msc_log('Inside main exception');
2830: msc_util.msc_log(sqlerrm);
2831: ERRBUF := sqlerrm;
2832: RETCODE := G_ERROR;
2825: set summary_flag = 1
2826: where instance_id = p_instance_id;
2827: msc_util.msc_log('An error occured while running net change on Sales Orders');
2828: msc_util.msc_log('Complete refresh would need to be run to activate Summary ATP');
2829: msc_util.msc_log('Inside main exception');
2830: msc_util.msc_log(sqlerrm);
2831: ERRBUF := sqlerrm;
2832: RETCODE := G_ERROR;
2833: END LOAD_NET_SO;
2826: where instance_id = p_instance_id;
2827: msc_util.msc_log('An error occured while running net change on Sales Orders');
2828: msc_util.msc_log('Complete refresh would need to be run to activate Summary ATP');
2829: msc_util.msc_log('Inside main exception');
2830: msc_util.msc_log(sqlerrm);
2831: ERRBUF := sqlerrm;
2832: RETCODE := G_ERROR;
2833: END LOAD_NET_SO;
2834:
3215: l_sysdate := sysdate;
3216: l_user_id := FND_GLOBAL.USER_ID;
3217: l_enable_summary_mode := NVL(FND_PROFILE.value('MSC_ENABLE_ATP_SUMMARY'), 'N');
3218:
3219: msc_util.msc_log('inv_ctp := ' || l_inv_ctp);
3220: msc_util.msc_log('l_enable_summary_mode := ' || l_enable_summary_mode);
3221:
3222: IF l_enable_summary_mode <> 'Y' THEN
3223: msc_util.msc_log('Summary Mode is not enabled. Please enable Summary mode to run this program');
3216: l_user_id := FND_GLOBAL.USER_ID;
3217: l_enable_summary_mode := NVL(FND_PROFILE.value('MSC_ENABLE_ATP_SUMMARY'), 'N');
3218:
3219: msc_util.msc_log('inv_ctp := ' || l_inv_ctp);
3220: msc_util.msc_log('l_enable_summary_mode := ' || l_enable_summary_mode);
3221:
3222: IF l_enable_summary_mode <> 'Y' THEN
3223: msc_util.msc_log('Summary Mode is not enabled. Please enable Summary mode to run this program');
3224: RETCODE := G_WARNING;
3219: msc_util.msc_log('inv_ctp := ' || l_inv_ctp);
3220: msc_util.msc_log('l_enable_summary_mode := ' || l_enable_summary_mode);
3221:
3222: IF l_enable_summary_mode <> 'Y' THEN
3223: msc_util.msc_log('Summary Mode is not enabled. Please enable Summary mode to run this program');
3224: RETCODE := G_WARNING;
3225: RETURN;
3226: END IF;
3227:
3226: END IF;
3227:
3228: IF l_inv_ctp <> 5 THEN
3229: -- we are not doing ODS ATP so we wont continue
3230: msc_util.msc_log('Not Doing ODS ATP. Will Exit ');
3231: RETCODE := G_WARNING;
3232: RETURN;
3233: END IF;
3234:
3241: INTO l_summary_flag,l_refresh_number,l_apps_lrn ---bug3049003
3242: from msc_apps_instances
3243: where instance_id = p_instance_id;
3244:
3245: msc_util.msc_log('l_refresh_number := ' || l_refresh_number);
3246:
3247: IF l_summary_flag = 2 THEN
3248: msc_util.msc_log('Another session is running full summary for this instance');
3249: RETCODE := G_ERROR;
3244:
3245: msc_util.msc_log('l_refresh_number := ' || l_refresh_number);
3246:
3247: IF l_summary_flag = 2 THEN
3248: msc_util.msc_log('Another session is running full summary for this instance');
3249: RETCODE := G_ERROR;
3250: RETURN;
3251: ELSIF (NVL(l_summary_flag, 0) = 2 ) THEN
3252: msc_util.msc_log('Partial summary of tables can be done only after complete summary has been run successfuly');
3248: msc_util.msc_log('Another session is running full summary for this instance');
3249: RETCODE := G_ERROR;
3250: RETURN;
3251: ELSIF (NVL(l_summary_flag, 0) = 2 ) THEN
3252: msc_util.msc_log('Partial summary of tables can be done only after complete summary has been run successfuly');
3253: RETCODE := G_ERROR;
3254: RETURN;
3255: END IF;
3256:
3266: WHERE sr_instance_id = p_instance_id
3267: and org_lrn=l_apps_lrn
3268: and enabled_flag=1;
3269:
3270: msc_util.msc_log(' org count := ' || l_org_ids.count);
3271:
3272: -- Update summary record per organization
3273: FOR i in 1..l_org_ids.count LOOP
3274: l_organization_id := l_org_ids(i);
3271:
3272: -- Update summary record per organization
3273: FOR i in 1..l_org_ids.count LOOP
3274: l_organization_id := l_org_ids(i);
3275: msc_util.msc_log('processing org '|| i ||' := '||l_organization_id);
3276:
3277: MSC_ATP_PROC.get_org_default_info ( p_instance_id,
3278: l_organization_id,
3279: l_default_atp_rule_id,
3281: l_calendar_exception_set_id,
3282: l_default_demand_class,
3283: l_org_code);
3284:
3285: msc_util.msc_log('l_calendar_code := ' || l_calendar_code);
3286: msc_util.msc_log('l_calendar_exception_set_id := ' || l_calendar_exception_set_id);
3287: msc_util.msc_log('l_default_atp_rule_id := ' || l_default_atp_rule_id);
3288: msc_util.msc_log('l_default_demand_class := ' || l_default_demand_class);
3289:
3282: l_default_demand_class,
3283: l_org_code);
3284:
3285: msc_util.msc_log('l_calendar_code := ' || l_calendar_code);
3286: msc_util.msc_log('l_calendar_exception_set_id := ' || l_calendar_exception_set_id);
3287: msc_util.msc_log('l_default_atp_rule_id := ' || l_default_atp_rule_id);
3288: msc_util.msc_log('l_default_demand_class := ' || l_default_demand_class);
3289:
3290: BEGIN
3283: l_org_code);
3284:
3285: msc_util.msc_log('l_calendar_code := ' || l_calendar_code);
3286: msc_util.msc_log('l_calendar_exception_set_id := ' || l_calendar_exception_set_id);
3287: msc_util.msc_log('l_default_atp_rule_id := ' || l_default_atp_rule_id);
3288: msc_util.msc_log('l_default_demand_class := ' || l_default_demand_class);
3289:
3290: BEGIN
3291: SELECT cal.next_seq_num
3284:
3285: msc_util.msc_log('l_calendar_code := ' || l_calendar_code);
3286: msc_util.msc_log('l_calendar_exception_set_id := ' || l_calendar_exception_set_id);
3287: msc_util.msc_log('l_default_atp_rule_id := ' || l_default_atp_rule_id);
3288: msc_util.msc_log('l_default_demand_class := ' || l_default_demand_class);
3289:
3290: BEGIN
3291: SELECT cal.next_seq_num
3292: INTO l_sysdate_seq_num
3308: l_calendar_code,
3309: l_default_demand_class,
3310: l_calendar_exception_set_id);
3311:
3312: msc_util.msc_log('after opening cursor C_NET_SUPPLY_DEMAND');
3313:
3314: LOOP
3315: FETCH C_NET_SUPPLY_DEMAND INTO
3316: l_inventory_item_id,
3319: l_sd_qty;
3320:
3321: EXIT WHEN C_NET_SUPPLY_DEMAND%NOTFOUND;
3322:
3323: msc_util.msc_log('l_demand_class := ' || l_demand_class);
3324: msc_util.msc_log('l_sd_date := ' || l_sd_date);
3325: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
3326: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
3327:
3320:
3321: EXIT WHEN C_NET_SUPPLY_DEMAND%NOTFOUND;
3322:
3323: msc_util.msc_log('l_demand_class := ' || l_demand_class);
3324: msc_util.msc_log('l_sd_date := ' || l_sd_date);
3325: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
3326: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
3327:
3328: --- With 9i the entire set can be accomplished in one MERGE statement.
3321: EXIT WHEN C_NET_SUPPLY_DEMAND%NOTFOUND;
3322:
3323: msc_util.msc_log('l_demand_class := ' || l_demand_class);
3324: msc_util.msc_log('l_sd_date := ' || l_sd_date);
3325: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
3326: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
3327:
3328: --- With 9i the entire set can be accomplished in one MERGE statement.
3329: --- Try to update record first and then
3322:
3323: msc_util.msc_log('l_demand_class := ' || l_demand_class);
3324: msc_util.msc_log('l_sd_date := ' || l_sd_date);
3325: msc_util.msc_log('l_sd_qty := ' || l_sd_qty);
3326: msc_util.msc_log('l_inventory_item_id := ' || l_inventory_item_id);
3327:
3328: --- With 9i the entire set can be accomplished in one MERGE statement.
3329: --- Try to update record first and then
3330: UPDATE MSC_ATP_SUMMARY_SD
3401: --- update summary flag in msc_apps_instances so that summary not available to use
3402: update msc_apps_instances
3403: set summary_flag = 1
3404: where instance_id = p_instance_id;
3405: msc_util.msc_log('An error occured while running net change on Sales Orders');
3406: msc_util.msc_log('Complete refresh would need to be run to activate Summary ATP');
3407:
3408: msc_util.msc_log('Inside main exception');
3409: msc_util.msc_log(sqlerrm);
3402: update msc_apps_instances
3403: set summary_flag = 1
3404: where instance_id = p_instance_id;
3405: msc_util.msc_log('An error occured while running net change on Sales Orders');
3406: msc_util.msc_log('Complete refresh would need to be run to activate Summary ATP');
3407:
3408: msc_util.msc_log('Inside main exception');
3409: msc_util.msc_log(sqlerrm);
3410: ERRBUF := sqlerrm;
3404: where instance_id = p_instance_id;
3405: msc_util.msc_log('An error occured while running net change on Sales Orders');
3406: msc_util.msc_log('Complete refresh would need to be run to activate Summary ATP');
3407:
3408: msc_util.msc_log('Inside main exception');
3409: msc_util.msc_log(sqlerrm);
3410: ERRBUF := sqlerrm;
3411: RETCODE := G_ERROR;
3412:
3405: msc_util.msc_log('An error occured while running net change on Sales Orders');
3406: msc_util.msc_log('Complete refresh would need to be run to activate Summary ATP');
3407:
3408: msc_util.msc_log('Inside main exception');
3409: msc_util.msc_log(sqlerrm);
3410: ERRBUF := sqlerrm;
3411: RETCODE := G_ERROR;
3412:
3413: END LOAD_NET_SD;
3441: AND NVL(SUMMARY_FLAG, 0) <> 0
3442: ORDER BY plan_id;
3443:
3444: BEGIN
3445: msc_util.msc_log('p_plan_id := ' || p_plan_id);
3446: msc_util.msc_log('p_applsys_schema := ' || p_applsys_schema);
3447: msc_util.msc_log('p_share_partition := ' || p_share_partition);
3448: msc_util.msc_log('p_owner := ' || p_owner);
3449:
3442: ORDER BY plan_id;
3443:
3444: BEGIN
3445: msc_util.msc_log('p_plan_id := ' || p_plan_id);
3446: msc_util.msc_log('p_applsys_schema := ' || p_applsys_schema);
3447: msc_util.msc_log('p_share_partition := ' || p_share_partition);
3448: msc_util.msc_log('p_owner := ' || p_owner);
3449:
3450: p_ret_code := G_SUCCESS;
3443:
3444: BEGIN
3445: msc_util.msc_log('p_plan_id := ' || p_plan_id);
3446: msc_util.msc_log('p_applsys_schema := ' || p_applsys_schema);
3447: msc_util.msc_log('p_share_partition := ' || p_share_partition);
3448: msc_util.msc_log('p_owner := ' || p_owner);
3449:
3450: p_ret_code := G_SUCCESS;
3451: p_err_msg := null;
3444: BEGIN
3445: msc_util.msc_log('p_plan_id := ' || p_plan_id);
3446: msc_util.msc_log('p_applsys_schema := ' || p_applsys_schema);
3447: msc_util.msc_log('p_share_partition := ' || p_share_partition);
3448: msc_util.msc_log('p_owner := ' || p_owner);
3449:
3450: p_ret_code := G_SUCCESS;
3451: p_err_msg := null;
3452:
3459: l_partition_name := atp_summ_tab(i) || '_' || p_plan_id;
3460: l_plan_id := p_plan_id;
3461: END IF;
3462: l_table_name := 'MSC_' || atp_summ_tab(i);
3463: msc_util.msc_log('table := ' || l_table_name);
3464: msc_util.msc_log('partition_name : '|| l_partition_name);
3465: BEGIN
3466: IF p_share_partition = 'Y' then
3467: BEGIN
3460: l_plan_id := p_plan_id;
3461: END IF;
3462: l_table_name := 'MSC_' || atp_summ_tab(i);
3463: msc_util.msc_log('table := ' || l_table_name);
3464: msc_util.msc_log('partition_name : '|| l_partition_name);
3465: BEGIN
3466: IF p_share_partition = 'Y' then
3467: BEGIN
3468: SELECT partition_name
3472: WHERE table_name = l_table_name
3473: AND table_owner = p_owner
3474: AND partition_name = l_partition_name;
3475:
3476: msc_util.msc_log('found partition_name : '|| l_name);
3477: EXCEPTION
3478: WHEN no_data_found THEN
3479: l_summary_flag := 0;
3480: msc_util.msc_log('before create partition_name : '|| l_partition_name);
3476: msc_util.msc_log('found partition_name : '|| l_name);
3477: EXCEPTION
3478: WHEN no_data_found THEN
3479: l_summary_flag := 0;
3480: msc_util.msc_log('before create partition_name : '|| l_partition_name);
3481: END;
3482: ELSE
3483: SELECT NVL(summary_flag, 0)
3484: INTO l_summary_flag
3485: FROM msc_plans
3486: WHERE plan_id = p_plan_id;
3487: END IF;
3488:
3489: msc_util.msc_log('summary_flag for plan : '|| p_plan_id || ' : '|| l_summary_flag);
3490:
3491: IF l_summary_flag <> 0 THEN
3492: msc_util.msc_log('found partition for plan_id : '|| p_plan_id);
3493: p_ret_code := G_SUCCESS;
3488:
3489: msc_util.msc_log('summary_flag for plan : '|| p_plan_id || ' : '|| l_summary_flag);
3490:
3491: IF l_summary_flag <> 0 THEN
3492: msc_util.msc_log('found partition for plan_id : '|| p_plan_id);
3493: p_ret_code := G_SUCCESS;
3494: p_err_msg := null;
3495: RETURN;
3496: ELSIF l_summary_flag = 0 THEN
3493: p_ret_code := G_SUCCESS;
3494: p_err_msg := null;
3495: RETURN;
3496: ELSIF l_summary_flag = 0 THEN
3497: msc_util.msc_log('l_plan_id:= ' || l_plan_id);
3498:
3499: OPEN C_PLAN;
3500: FETCH C_PLAN INTO l_higher_plan;
3501: msc_util.msc_log('l_higher_plan : ' || l_higher_plan);
3497: msc_util.msc_log('l_plan_id:= ' || l_plan_id);
3498:
3499: OPEN C_PLAN;
3500: FETCH C_PLAN INTO l_higher_plan;
3501: msc_util.msc_log('l_higher_plan : ' || l_higher_plan);
3502:
3503: IF C_PLAN%NOTFOUND THEN
3504: l_sql_stmt := 'alter table ' || l_table_name || ' add partition '
3505: || l_partition_name
3515: || ' INTO ( PARTITION ' || l_partition_name || ','
3516: || ' PARTITION ' || l_split_partition || ')';
3517: END IF; -- C_PLAN%NOTFOUND THEN
3518:
3519: msc_util.msc_log('l_sql_stmt := ' || l_sql_stmt);
3520: CLOSE C_PLAN;
3521:
3522: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
3523: APPLICATION_SHORT_NAME => 'MSC',
3523: APPLICATION_SHORT_NAME => 'MSC',
3524: STATEMENT_TYPE => ad_ddl.alter_table,
3525: STATEMENT => l_sql_stmt,
3526: OBJECT_NAME => l_table_name);
3527: msc_util.msc_log('Create Partition successful');
3528: END IF; -- l_summary_flag <> 0 THEN
3529: EXCEPTION
3530: WHEN no_data_found THEN
3531: msc_util.msc_log('Plan Not Found : ' || p_plan_id );
3527: msc_util.msc_log('Create Partition successful');
3528: END IF; -- l_summary_flag <> 0 THEN
3529: EXCEPTION
3530: WHEN no_data_found THEN
3531: msc_util.msc_log('Plan Not Found : ' || p_plan_id );
3532: p_ret_code := G_ERROR;
3533: p_err_msg := sqlerrm;
3534: RETURN;
3535: END;
3537: END LOOP;
3538:
3539: EXCEPTION
3540: WHEN OTHERS THEN
3541: msc_util.msc_log('In exception of CREATE_PLAN_PARTITIONS');
3542: p_ret_code := G_ERROR;
3543: p_err_msg := sqlerrm;
3544: END CREATE_PLAN_PARTITIONS;
3545:
3568: AND NVL(summary_flag, 0) <> 0
3569: ORDER BY instance_id;
3570:
3571: BEGIN
3572: msc_util.msc_log('p_instance_id := ' || p_instance_id);
3573: msc_util.msc_log('p_applsys_schema := ' || p_applsys_schema);
3574: msc_util.msc_log('p_owner := ' || p_owner);
3575:
3576: p_ret_code := G_SUCCESS;
3569: ORDER BY instance_id;
3570:
3571: BEGIN
3572: msc_util.msc_log('p_instance_id := ' || p_instance_id);
3573: msc_util.msc_log('p_applsys_schema := ' || p_applsys_schema);
3574: msc_util.msc_log('p_owner := ' || p_owner);
3575:
3576: p_ret_code := G_SUCCESS;
3577: p_err_msg := null;
3570:
3571: BEGIN
3572: msc_util.msc_log('p_instance_id := ' || p_instance_id);
3573: msc_util.msc_log('p_applsys_schema := ' || p_applsys_schema);
3574: msc_util.msc_log('p_owner := ' || p_owner);
3575:
3576: p_ret_code := G_SUCCESS;
3577: p_err_msg := null;
3578:
3580:
3581: l_partition_name := atp_summ_tab(i) || '__' || p_instance_id;
3582:
3583: l_table_name := 'MSC_' || atp_summ_tab(i);
3584: msc_util.msc_log('table := ' || l_table_name);
3585: msc_util.msc_log('partition_name : '|| l_partition_name);
3586: BEGIN
3587: SELECT NVL(summary_flag, 0)
3588: INTO l_summary_flag
3581: l_partition_name := atp_summ_tab(i) || '__' || p_instance_id;
3582:
3583: l_table_name := 'MSC_' || atp_summ_tab(i);
3584: msc_util.msc_log('table := ' || l_table_name);
3585: msc_util.msc_log('partition_name : '|| l_partition_name);
3586: BEGIN
3587: SELECT NVL(summary_flag, 0)
3588: INTO l_summary_flag
3589: FROM msc_apps_instances
3588: INTO l_summary_flag
3589: FROM msc_apps_instances
3590: WHERE instance_id = p_instance_id;
3591:
3592: msc_util.msc_log('summary_flag for instance : '|| p_instance_id || ' : '|| l_summary_flag);
3593:
3594: IF l_summary_flag <> 0 THEN
3595: msc_util.msc_log('found partition for instance_id : '|| p_instance_id);
3596: p_ret_code := G_SUCCESS;
3591:
3592: msc_util.msc_log('summary_flag for instance : '|| p_instance_id || ' : '|| l_summary_flag);
3593:
3594: IF l_summary_flag <> 0 THEN
3595: msc_util.msc_log('found partition for instance_id : '|| p_instance_id);
3596: p_ret_code := G_SUCCESS;
3597: p_err_msg := null;
3598: RETURN;
3599: ELSIF l_summary_flag = 0 THEN
3596: p_ret_code := G_SUCCESS;
3597: p_err_msg := null;
3598: RETURN;
3599: ELSIF l_summary_flag = 0 THEN
3600: msc_util.msc_log('p_instance_id : ' || p_instance_id);
3601:
3602: OPEN C_INST;
3603: FETCH C_INST INTO l_higher_instance;
3604:
3622: || to_char(p_instance_id +1) || ')'
3623: || ' INTO ( PARTITION ' || l_partition_name || ','
3624: || ' PARTITION ' ||l_split_partition || ')';
3625: END IF; -- l_table_name = 'MSC_ATP_SUMMARY_SO' THEN
3626: msc_util.msc_log('l_sql_stmt := ' || l_sql_stmt);
3627: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
3628: APPLICATION_SHORT_NAME => 'MSC',
3629: STATEMENT_TYPE => ad_ddl.alter_table,
3630: STATEMENT => l_sql_stmt,
3628: APPLICATION_SHORT_NAME => 'MSC',
3629: STATEMENT_TYPE => ad_ddl.alter_table,
3630: STATEMENT => l_sql_stmt,
3631: OBJECT_NAME => l_table_name);
3632: msc_util.msc_log('Create Partition successful');
3633: END IF; -- l_summary_flag <> 0 THEN
3634: EXCEPTION
3635: WHEN no_data_found THEN
3636: msc_util.msc_log('Instance Not Found : ' || p_instance_id );
3632: msc_util.msc_log('Create Partition successful');
3633: END IF; -- l_summary_flag <> 0 THEN
3634: EXCEPTION
3635: WHEN no_data_found THEN
3636: msc_util.msc_log('Instance Not Found : ' || p_instance_id );
3637: p_ret_code := G_ERROR;
3638: p_err_msg := sqlerrm;
3639: RETURN;
3640: END;
3642: END LOOP;
3643:
3644: EXCEPTION
3645: WHEN OTHERS THEN
3646: msc_util.msc_log('In exception of CREATE_INST_PARTITIONS');
3647: p_ret_code := G_ERROR;
3648: p_err_msg := sqlerrm;
3649: END CREATE_INST_PARTITIONS;
3650:
3714: --- create Instance partitions
3715:
3716: FOR j in 1..INSTANCE_IDS.COUNT LOOP
3717:
3718: msc_util.msc_log('j := ' || j);
3719: For i in 1..2 LOOP --- loop for only first two enteries of the atp_summ_tab array
3720:
3721: l_partition_name := atp_summ_tab(i) || '__' || INSTANCE_IDS(j);
3722: l_table_name := 'MSC_' || atp_summ_tab(i);
3720:
3721: l_partition_name := atp_summ_tab(i) || '__' || INSTANCE_IDS(j);
3722: l_table_name := 'MSC_' || atp_summ_tab(i);
3723: l_split_partition := atp_summ_tab(i) || '_0';
3724: msc_util.msc_log('table := ' || l_table_name);
3725: msc_util.msc_log('partition_name : '|| l_partition_name);
3726: BEGIN
3727: BEGIN
3728: SELECT count(*)
3721: l_partition_name := atp_summ_tab(i) || '__' || INSTANCE_IDS(j);
3722: l_table_name := 'MSC_' || atp_summ_tab(i);
3723: l_split_partition := atp_summ_tab(i) || '_0';
3724: msc_util.msc_log('table := ' || l_table_name);
3725: msc_util.msc_log('partition_name : '|| l_partition_name);
3726: BEGIN
3727: BEGIN
3728: SELECT count(*)
3729: INTO l_count
3734: AND PARTITION_NAME = l_partition_name
3735: AND table_owner = l_msc_schema;
3736: EXCEPTION
3737: WHEN OTHERS THEN
3738: msc_util.msc_log('Inside Exception');
3739: l_count := 1;
3740: END;
3741: msc_util.msc_log('l_count := ' || l_count);
3742: IF l_count = 0 THEN
3737: WHEN OTHERS THEN
3738: msc_util.msc_log('Inside Exception');
3739: l_count := 1;
3740: END;
3741: msc_util.msc_log('l_count := ' || l_count);
3742: IF l_count = 0 THEN
3743: ---partition doesn't exist
3744:
3745:
3755: || to_char(instance_ids(j) +1) || ')'
3756: || ' INTO ( PARTITION ' || l_partition_name || ','
3757: || ' PARTITION ' ||l_split_partition || ')';
3758: END IF; -- l_table_name = 'MSC_ATP_SUMMARY_SO' THEN
3759: msc_util.msc_log('l_sql_stmt := ' || l_sql_stmt);
3760: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
3761: APPLICATION_SHORT_NAME => 'MSC',
3762: STATEMENT_TYPE => ad_ddl.alter_table,
3763: STATEMENT => l_sql_stmt,
3761: APPLICATION_SHORT_NAME => 'MSC',
3762: STATEMENT_TYPE => ad_ddl.alter_table,
3763: STATEMENT => l_sql_stmt,
3764: OBJECT_NAME => l_table_name);
3765: msc_util.msc_log('Create Partition successful');
3766: ELSE
3767: msc_util.msc_log('Partition for instance ' || instance_ids(j) || ' already exists');
3768: END IF;
3769: END;
3763: STATEMENT => l_sql_stmt,
3764: OBJECT_NAME => l_table_name);
3765: msc_util.msc_log('Create Partition successful');
3766: ELSE
3767: msc_util.msc_log('Partition for instance ' || instance_ids(j) || ' already exists');
3768: END IF;
3769: END;
3770:
3771: END LOOP;
3771: END LOOP;
3772: END LOOP;
3773:
3774: l_share_partition := fnd_profile.value('MSC_SHARE_PARTITIONS');
3775: msc_util.msc_log('l_share_partition := ' || l_share_partition);
3776:
3777: IF l_share_partition = 'Y' then
3778: msc_util.msc_log('Share partition is on');
3779: ---first we check if _999999 partition exists on MSC_SYSTEM_ITEMS or not.
3774: l_share_partition := fnd_profile.value('MSC_SHARE_PARTITIONS');
3775: msc_util.msc_log('l_share_partition := ' || l_share_partition);
3776:
3777: IF l_share_partition = 'Y' then
3778: msc_util.msc_log('Share partition is on');
3779: ---first we check if _999999 partition exists on MSC_SYSTEM_ITEMS or not.
3780: ---1) If it exists then the _999999 partition might or might not exist om
3781: --- the atp summary tables. In this case we check and create it if it doesn't exist
3782: ---2) If partition on msc_system_items doesn't exist then that means customer is running
3797: EXCEPTION
3798: WHEN OTHERS THEN
3799: l_count := 1;
3800: END;
3801: msc_util.msc_log('Count of partition om msc_system_items := ' || l_count);
3802: IF l_count > 0 THEN --- patiton on msc_system items exist
3803:
3804:
3805:
3807:
3808: l_partition_name := atp_summ_tab(i) || '_' || MAXVALUE;
3809: l_plan_id := MAXVALUE;
3810: l_table_name := 'MSC_' || atp_summ_tab(i);
3811: msc_util.msc_log('table := ' || l_table_name);
3812: msc_util.msc_log('partition_name : '|| l_partition_name);
3813:
3814: BEGIN
3815: select count(*)
3808: l_partition_name := atp_summ_tab(i) || '_' || MAXVALUE;
3809: l_plan_id := MAXVALUE;
3810: l_table_name := 'MSC_' || atp_summ_tab(i);
3811: msc_util.msc_log('table := ' || l_table_name);
3812: msc_util.msc_log('partition_name : '|| l_partition_name);
3813:
3814: BEGIN
3815: select count(*)
3816: into l_count
3836: STATEMENT_TYPE => ad_ddl.alter_table,
3837: STATEMENT => l_sql_stmt,
3838: OBJECT_NAME => atp_summ_tab(i));
3839:
3840: msc_util.msc_log('Create Partition successful');
3841: ELSE
3842: msc_util.msc_log('Plan partition for plan ' || l_plan_id || ' already exists');
3843: END IF; -- l_summary_flag <> 0 THEN
3844:
3838: OBJECT_NAME => atp_summ_tab(i));
3839:
3840: msc_util.msc_log('Create Partition successful');
3841: ELSE
3842: msc_util.msc_log('Plan partition for plan ' || l_plan_id || ' already exists');
3843: END IF; -- l_summary_flag <> 0 THEN
3844:
3845: END LOOP; --- For i in 2..atp_summ_tab.COUNT LOOP
3846: ELSE
3843: END IF; -- l_summary_flag <> 0 THEN
3844:
3845: END LOOP; --- For i in 2..atp_summ_tab.COUNT LOOP
3846: ELSE
3847: msc_util.msc_log(' No partition on msc_system_items exist.');
3848: msc_util.msc_log('Partitions will be created by the main program');
3849: END IF; -- if l_count > 0
3850:
3851: ELSE --- IF l_share_partition = 'Y' then
3844:
3845: END LOOP; --- For i in 2..atp_summ_tab.COUNT LOOP
3846: ELSE
3847: msc_util.msc_log(' No partition on msc_system_items exist.');
3848: msc_util.msc_log('Partitions will be created by the main program');
3849: END IF; -- if l_count > 0
3850:
3851: ELSE --- IF l_share_partition = 'Y' then
3852: --- create plan partitions
3860: order by plan_id;
3861:
3862: END;
3863:
3864: msc_util.msc_log('plan count := ' || plan_ids.count);
3865: FOR j in 1..plan_ids.count LOOP
3866: msc_util.msc_log('j := ' || j );
3867: msc_util.msc_log('plan_ids := ' || plan_ids(j));
3868:
3862: END;
3863:
3864: msc_util.msc_log('plan count := ' || plan_ids.count);
3865: FOR j in 1..plan_ids.count LOOP
3866: msc_util.msc_log('j := ' || j );
3867: msc_util.msc_log('plan_ids := ' || plan_ids(j));
3868:
3869: For i in 2..atp_summ_tab.COUNT LOOP
3870:
3863:
3864: msc_util.msc_log('plan count := ' || plan_ids.count);
3865: FOR j in 1..plan_ids.count LOOP
3866: msc_util.msc_log('j := ' || j );
3867: msc_util.msc_log('plan_ids := ' || plan_ids(j));
3868:
3869: For i in 2..atp_summ_tab.COUNT LOOP
3870:
3871: l_partition_name := atp_summ_tab(i) || '_' || ABS(plan_ids(j));
3870:
3871: l_partition_name := atp_summ_tab(i) || '_' || ABS(plan_ids(j));
3872: l_plan_id := plan_ids(j);
3873: l_table_name := 'MSC_' || atp_summ_tab(i);
3874: msc_util.msc_log('table := ' || l_table_name);
3875: msc_util.msc_log('partition_name : '|| l_partition_name);
3876:
3877: BEGIN
3878: select count(*)
3871: l_partition_name := atp_summ_tab(i) || '_' || ABS(plan_ids(j));
3872: l_plan_id := plan_ids(j);
3873: l_table_name := 'MSC_' || atp_summ_tab(i);
3874: msc_util.msc_log('table := ' || l_table_name);
3875: msc_util.msc_log('partition_name : '|| l_partition_name);
3876:
3877: BEGIN
3878: select count(*)
3879: into l_count
3899: STATEMENT_TYPE => ad_ddl.alter_table,
3900: STATEMENT => l_sql_stmt,
3901: OBJECT_NAME => atp_summ_tab(i));
3902:
3903: msc_util.msc_log('Create Partition successful');
3904: ELSE
3905: msc_util.msc_log('Plan partition for plan ' || l_plan_id || ' already exists');
3906: END IF; -- l_summary_flag <> 0 THEN
3907:
3901: OBJECT_NAME => atp_summ_tab(i));
3902:
3903: msc_util.msc_log('Create Partition successful');
3904: ELSE
3905: msc_util.msc_log('Plan partition for plan ' || l_plan_id || ' already exists');
3906: END IF; -- l_summary_flag <> 0 THEN
3907:
3908: END LOOP;
3909: END LOOP;
3907:
3908: END LOOP;
3909: END LOOP;
3910: END IF; -- IF l_share_partition = 'Y' then
3911: msc_util.msc_log('After Create Partitions');
3912:
3913: EXCEPTION
3914: WHEN OTHERS THEN
3915: msc_util.msc_log('In exception of CREATE_PARTITIONS');
3911: msc_util.msc_log('After Create Partitions');
3912:
3913: EXCEPTION
3914: WHEN OTHERS THEN
3915: msc_util.msc_log('In exception of CREATE_PARTITIONS');
3916: msc_util.msc_log('sqlerrm := ' || sqlerrm);
3917: RETCODE := G_ERROR;
3918: ERRBUF := sqlerrm;
3919: END CREATE_PARTITIONS;
3912:
3913: EXCEPTION
3914: WHEN OTHERS THEN
3915: msc_util.msc_log('In exception of CREATE_PARTITIONS');
3916: msc_util.msc_log('sqlerrm := ' || sqlerrm);
3917: RETCODE := G_ERROR;
3918: ERRBUF := sqlerrm;
3919: END CREATE_PARTITIONS;
3920:
4007: IF p_demand_priority = 'Y' THEN
4008: l_sql_stmt := 'DROP TABLE MSC_ALLOC_TEMP_' || to_char(p_plan_id2);
4009:
4010: BEGIN
4011: msc_util.msc_log(l_sql_stmt);
4012: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
4013: APPLICATION_SHORT_NAME => 'MSC',
4014: STATEMENT_TYPE => ad_ddl.drop_table,
4015: STATEMENT => l_sql_stmt,
4015: STATEMENT => l_sql_stmt,
4016: OBJECT_NAME => 'MSC_ALLOC_TEMP_' || to_char(p_plan_id2));
4017: EXCEPTION
4018: WHEN OTHERS THEN
4019: msc_util.msc_log(sqlcode || ': ' || sqlerrm);
4020: msc_util.msc_log(l_sql_stmt || ' failed');
4021: END;
4022: END IF;
4023:
4016: OBJECT_NAME => 'MSC_ALLOC_TEMP_' || to_char(p_plan_id2));
4017: EXCEPTION
4018: WHEN OTHERS THEN
4019: msc_util.msc_log(sqlcode || ': ' || sqlerrm);
4020: msc_util.msc_log(l_sql_stmt || ' failed');
4021: END;
4022: END IF;
4023:
4024: IF p_plan_id = p_plan_id2 THEN -- Means share plan partition is No
4024: IF p_plan_id = p_plan_id2 THEN -- Means share plan partition is No
4025: l_sql_stmt := 'DROP TABLE MSC_TEMP_ALLOC_DEM_' || to_char(p_plan_id);
4026:
4027: BEGIN
4028: msc_util.msc_log(l_sql_stmt);
4029: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
4030: APPLICATION_SHORT_NAME => 'MSC',
4031: STATEMENT_TYPE => ad_ddl.drop_table,
4032: STATEMENT => l_sql_stmt,
4032: STATEMENT => l_sql_stmt,
4033: OBJECT_NAME => 'MSC_TEMP_ALLOC_DEM_' || to_char(p_plan_id));
4034: EXCEPTION
4035: WHEN OTHERS THEN
4036: msc_util.msc_log(sqlcode || ': ' || sqlerrm);
4037: msc_util.msc_log(l_sql_stmt || ' failed');
4038: END;
4039:
4040: l_sql_stmt := 'DROP TABLE MSC_TEMP_ALLOC_SUP_' || to_char(p_plan_id);
4033: OBJECT_NAME => 'MSC_TEMP_ALLOC_DEM_' || to_char(p_plan_id));
4034: EXCEPTION
4035: WHEN OTHERS THEN
4036: msc_util.msc_log(sqlcode || ': ' || sqlerrm);
4037: msc_util.msc_log(l_sql_stmt || ' failed');
4038: END;
4039:
4040: l_sql_stmt := 'DROP TABLE MSC_TEMP_ALLOC_SUP_' || to_char(p_plan_id);
4041:
4039:
4040: l_sql_stmt := 'DROP TABLE MSC_TEMP_ALLOC_SUP_' || to_char(p_plan_id);
4041:
4042: BEGIN
4043: msc_util.msc_log(l_sql_stmt);
4044: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
4045: APPLICATION_SHORT_NAME => 'MSC',
4046: STATEMENT_TYPE => ad_ddl.drop_table,
4047: STATEMENT => l_sql_stmt,
4047: STATEMENT => l_sql_stmt,
4048: OBJECT_NAME => 'MSC_TEMP_ALLOC_SUP_' || to_char(p_plan_id));
4049: EXCEPTION
4050: WHEN OTHERS THEN
4051: msc_util.msc_log(sqlcode || ': ' || sqlerrm);
4052: msc_util.msc_log(l_sql_stmt || ' failed');
4053: END;
4054: END IF;
4055: END clean_temp_tables;
4048: OBJECT_NAME => 'MSC_TEMP_ALLOC_SUP_' || to_char(p_plan_id));
4049: EXCEPTION
4050: WHEN OTHERS THEN
4051: msc_util.msc_log(sqlcode || ': ' || sqlerrm);
4052: msc_util.msc_log(l_sql_stmt || ' failed');
4053: END;
4054: END IF;
4055: END clean_temp_tables;
4056:
4114: -- project atp
4115: l_excess_supply_by_dc varchar2(1);
4116:
4117: BEGIN
4118: msc_util.msc_log('Begin procedure post_plan_allocation');
4119:
4120: l_inv_ctp := FND_PROFILE.value('INV_CTP');
4121: msc_util.msc_log('inv_ctp := ' || l_inv_ctp);
4122:
4117: BEGIN
4118: msc_util.msc_log('Begin procedure post_plan_allocation');
4119:
4120: l_inv_ctp := FND_PROFILE.value('INV_CTP');
4121: msc_util.msc_log('inv_ctp := ' || l_inv_ctp);
4122:
4123: IF l_inv_ctp <> 4 THEN
4124: -- we are not doing PDS ATP so we wont continue
4125: msc_util.msc_log('Not Doing PDS ATP. Will Exit ');
4121: msc_util.msc_log('inv_ctp := ' || l_inv_ctp);
4122:
4123: IF l_inv_ctp <> 4 THEN
4124: -- we are not doing PDS ATP so we wont continue
4125: msc_util.msc_log('Not Doing PDS ATP. Will Exit ');
4126: RETCODE := G_ERROR;
4127: RETURN;
4128: END IF;
4129:
4127: RETURN;
4128: END IF;
4129:
4130: l_alloc_atp := NVL(FND_PROFILE.value('MSC_ALLOCATED_ATP'),'N');
4131: msc_util.msc_log('l_alloc_atp := ' || l_alloc_atp);
4132:
4133: IF l_alloc_atp <> 'Y' THEN
4134: -- we are not doing Allocated ATP so we wont continue
4135: msc_util.msc_log('Not Doing Allocated ATP. Will Exit ');
4131: msc_util.msc_log('l_alloc_atp := ' || l_alloc_atp);
4132:
4133: IF l_alloc_atp <> 'Y' THEN
4134: -- we are not doing Allocated ATP so we wont continue
4135: msc_util.msc_log('Not Doing Allocated ATP. Will Exit ');
4136: RETCODE := G_ERROR;
4137: RETURN;
4138: END IF;
4139:
4137: RETURN;
4138: END IF;
4139:
4140: l_class_hrchy := NVL(FND_PROFILE.VALUE('MSC_CLASS_HIERARCHY'), 2);
4141: msc_util.msc_log('l_class_hrchy := ' || l_class_hrchy);
4142:
4143: IF l_class_hrchy <> 1 THEN
4144: -- we are not doing Demand Class based AATP so we wont continue
4145: msc_util.msc_log('Not Doing Demand Class based AATP. Will Exit ');
4141: msc_util.msc_log('l_class_hrchy := ' || l_class_hrchy);
4142:
4143: IF l_class_hrchy <> 1 THEN
4144: -- we are not doing Demand Class based AATP so we wont continue
4145: msc_util.msc_log('Not Doing Demand Class based AATP. Will Exit ');
4146: RETCODE := G_ERROR;
4147: RETURN;
4148: END IF;
4149:
4147: RETURN;
4148: END IF;
4149:
4150: l_alloc_method := NVL(FND_PROFILE.VALUE('MSC_ALLOCATION_METHOD'), 2);
4151: msc_util.msc_log('l_alloc_method := ' || l_alloc_method);
4152:
4153: IF l_alloc_method <> 1 THEN
4154: -- we are not doing Demand Class based AATP using pegging from planning so we wont continue
4155: msc_util.msc_log('Not Doing Demand Class based AATP using pegging from planning. Will Exit ');
4151: msc_util.msc_log('l_alloc_method := ' || l_alloc_method);
4152:
4153: IF l_alloc_method <> 1 THEN
4154: -- we are not doing Demand Class based AATP using pegging from planning so we wont continue
4155: msc_util.msc_log('Not Doing Demand Class based AATP using pegging from planning. Will Exit ');
4156: RETCODE := G_ERROR;
4157: RETURN;
4158: END IF;
4159:
4158: END IF;
4159:
4160: -- rajjain project atp changes 07/24/2003 begin
4161: l_excess_supply_by_dc := NVL(FND_PROFILE.VALUE('MSC_EXCESS_SUPPLY_BY_DC'), 'N');
4162: msc_util.msc_log('l_excess_supply_by_dc := ' || l_excess_supply_by_dc);
4163:
4164: BEGIN
4165: msc_util.msc_log('Calling custom procedure MSC_ATP_CUSTOM.Custom_Pre_Allocation...');
4166: MSC_ATP_CUSTOM.Custom_Pre_Allocation(p_plan_id);
4161: l_excess_supply_by_dc := NVL(FND_PROFILE.VALUE('MSC_EXCESS_SUPPLY_BY_DC'), 'N');
4162: msc_util.msc_log('l_excess_supply_by_dc := ' || l_excess_supply_by_dc);
4163:
4164: BEGIN
4165: msc_util.msc_log('Calling custom procedure MSC_ATP_CUSTOM.Custom_Pre_Allocation...');
4166: MSC_ATP_CUSTOM.Custom_Pre_Allocation(p_plan_id);
4167: msc_util.msc_log('End MSC_ATP_CUSTOM.Custom_Pre_Allocation.');
4168: EXCEPTION
4169: WHEN OTHERS THEN
4163:
4164: BEGIN
4165: msc_util.msc_log('Calling custom procedure MSC_ATP_CUSTOM.Custom_Pre_Allocation...');
4166: MSC_ATP_CUSTOM.Custom_Pre_Allocation(p_plan_id);
4167: msc_util.msc_log('End MSC_ATP_CUSTOM.Custom_Pre_Allocation.');
4168: EXCEPTION
4169: WHEN OTHERS THEN
4170: msc_util.msc_log('Error in custom procedure call');
4171: msc_util.msc_log('Error Code: '|| sqlerrm);
4166: MSC_ATP_CUSTOM.Custom_Pre_Allocation(p_plan_id);
4167: msc_util.msc_log('End MSC_ATP_CUSTOM.Custom_Pre_Allocation.');
4168: EXCEPTION
4169: WHEN OTHERS THEN
4170: msc_util.msc_log('Error in custom procedure call');
4171: msc_util.msc_log('Error Code: '|| sqlerrm);
4172: END;
4173: -- rajjain project atp changes 07/24/2003 end
4174:
4167: msc_util.msc_log('End MSC_ATP_CUSTOM.Custom_Pre_Allocation.');
4168: EXCEPTION
4169: WHEN OTHERS THEN
4170: msc_util.msc_log('Error in custom procedure call');
4171: msc_util.msc_log('Error Code: '|| sqlerrm);
4172: END;
4173: -- rajjain project atp changes 07/24/2003 end
4174:
4175: msc_util.msc_log('begin Loading pre-allocation demand/supply data for plan: ' || p_plan_id);
4171: msc_util.msc_log('Error Code: '|| sqlerrm);
4172: END;
4173: -- rajjain project atp changes 07/24/2003 end
4174:
4175: msc_util.msc_log('begin Loading pre-allocation demand/supply data for plan: ' || p_plan_id);
4176: RETCODE := G_SUCCESS;
4177:
4178: l_share_partition := fnd_profile.value('MSC_SHARE_PARTITIONS');
4179:
4176: RETCODE := G_SUCCESS;
4177:
4178: l_share_partition := fnd_profile.value('MSC_SHARE_PARTITIONS');
4179:
4180: msc_util.msc_log('l_share_partition := ' || l_share_partition);
4181:
4182: -- Bug 2516506 - select plan name also
4183: -- SELECT NVL(summary_flag,1)
4184: -- INTO l_summary_flag
4187: FROM msc_plans
4188: WHERE plan_id = p_plan_id;
4189:
4190: IF NVL(l_summary_flag,1) = 2 THEN
4191: msc_util.msc_log('Another session is running post-plan allocation program for this plan');
4192: RETCODE := G_ERROR;
4193: RETURN;
4194: END IF;
4195:
4215: l_plan_id := p_plan_id;
4216: END IF;
4217:
4218: l_partition_name := atp_summ_tab(i)|| '_' || l_plan_id;
4219: msc_util.msc_log('l_partition_name := ' || l_partition_name);
4220:
4221: BEGIN
4222: SELECT count(*)
4223: INTO l_count
4228: AND partition_name = l_partition_name
4229: AND table_owner = l_msc_schema;
4230: EXCEPTION
4231: WHEN OTHERS THEN
4232: msc_util.msc_log('Inside Exception');
4233: l_count := 0;
4234: END;
4235:
4236: IF (l_count = 0) THEN
4237: -- Bug 2516506
4238: FND_MESSAGE.SET_NAME('MSC', 'MSC_ATP_PLAN_PARTITION_MISSING');
4239: FND_MESSAGE.SET_TOKEN('PLAN_NAME', l_plan_name);
4240: FND_MESSAGE.SET_TOKEN('TABLE_NAME', 'MSC_' || atp_summ_tab(i));
4241: msc_util.msc_log(FND_MESSAGE.GET);
4242: RETCODE := G_ERROR;
4243: RETURN;
4244: END IF;
4245: END LOOP;
4255: RETCODE := G_ERROR;
4256: RETURN;
4257: END;
4258:
4259: msc_util.msc_log('l_share_partition := ' || l_share_partition);
4260:
4261: -- performance bug 2566795. dsting: forward port from 1157
4262:
4263: BEGIN
4270: FROM msc_atp_parameters
4271: WHERE rownum = 1;
4272: EXCEPTION
4273: WHEN others THEN
4274: msc_util.msc_log('Error getting performance param: ' || sqlcode || ': ' || sqlerrm);
4275: l_hash_size := -1;
4276: l_sort_size := -1;
4277: l_parallel_degree := 1;
4278: END;
4276: l_sort_size := -1;
4277: l_parallel_degree := 1;
4278: END;
4279:
4280: msc_util.msc_log('Hash: ' || l_hash_size || ' Sort: ' || l_sort_size || ' Parallel: ' || l_parallel_degree);
4281:
4282: IF NVL(l_hash_size, -1) <> -1 THEN
4283: l_sql_stmt_1 := 'alter session set hash_area_size = ' || to_char(l_hash_size);
4284: msc_util.msc_log('l_sql_stmt : ' || l_sql_stmt_1);
4280: msc_util.msc_log('Hash: ' || l_hash_size || ' Sort: ' || l_sort_size || ' Parallel: ' || l_parallel_degree);
4281:
4282: IF NVL(l_hash_size, -1) <> -1 THEN
4283: l_sql_stmt_1 := 'alter session set hash_area_size = ' || to_char(l_hash_size);
4284: msc_util.msc_log('l_sql_stmt : ' || l_sql_stmt_1);
4285: execute immediate l_sql_stmt_1;
4286: END IF;
4287:
4288: IF NVL(l_sort_size, -1) <> -1 THEN
4286: END IF;
4287:
4288: IF NVL(l_sort_size, -1) <> -1 THEN
4289: l_sql_stmt_1 := 'alter session set sort_area_size = ' || to_char(l_sort_size);
4290: msc_util.msc_log('l_sql_stmt : ' || l_sql_stmt_1);
4291: execute immediate l_sql_stmt_1;
4292: END IF;
4293:
4294: IF l_share_partition = 'Y' THEN
4292: END IF;
4293:
4294: IF l_share_partition = 'Y' THEN
4295:
4296: msc_util.msc_log('Inside shared partition');
4297:
4298: -- first delete the existing data from tables
4299: msc_util.msc_log('before deleteing data from the table');
4300:
4295:
4296: msc_util.msc_log('Inside shared partition');
4297:
4298: -- first delete the existing data from tables
4299: msc_util.msc_log('before deleteing data from the table');
4300:
4301: DELETE MSC_ALLOC_DEMANDS where plan_id = p_plan_id;
4302: msc_util.msc_log('After deleting data from MSC_ALLOC_DEMANDS table');
4303:
4298: -- first delete the existing data from tables
4299: msc_util.msc_log('before deleteing data from the table');
4300:
4301: DELETE MSC_ALLOC_DEMANDS where plan_id = p_plan_id;
4302: msc_util.msc_log('After deleting data from MSC_ALLOC_DEMANDS table');
4303:
4304: DELETE MSC_ALLOC_SUPPLIES where plan_id = p_plan_id;
4305: msc_util.msc_log('After deleting data from MSC_ALLOC_SUPPLIES table');
4306:
4301: DELETE MSC_ALLOC_DEMANDS where plan_id = p_plan_id;
4302: msc_util.msc_log('After deleting data from MSC_ALLOC_DEMANDS table');
4303:
4304: DELETE MSC_ALLOC_SUPPLIES where plan_id = p_plan_id;
4305: msc_util.msc_log('After deleting data from MSC_ALLOC_SUPPLIES table');
4306:
4307: /* --------------------------------------------------------------- */
4308: -- 2566795
4309: -- 2623646 Modified to join with msc_trading_partners/ msc_calendar_dates
4468: -- performance bug 2566795
4469: -- parallel hint can't be used with union all. Use two queries instead
4470:
4471: -- UNION ALL
4472: msc_util.msc_log('After Generating the sql');
4473:
4474: -- Obtain cursor handler for sql_stmt
4475: cur_handler := DBMS_SQL.OPEN_CURSOR;
4476:
4474: -- Obtain cursor handler for sql_stmt
4475: cur_handler := DBMS_SQL.OPEN_CURSOR;
4476:
4477: DBMS_SQL.PARSE(cur_handler, l_sql_stmt_1, DBMS_SQL.NATIVE);
4478: msc_util.msc_log('After parsing the sql');
4479:
4480: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_user_id', l_user_id);
4481: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_sysdate', l_sysdate);
4482: DBMS_SQL.BIND_VARIABLE(cur_handler, ':p_plan_id', p_plan_id);
4480: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_user_id', l_user_id);
4481: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_sysdate', l_sysdate);
4482: DBMS_SQL.BIND_VARIABLE(cur_handler, ':p_plan_id', p_plan_id);
4483: DBMS_SQL.BIND_VARIABLE(cur_handler, ':def_num', '-1');
4484: msc_util.msc_log('after binding the variables');
4485:
4486: -- Execute the cursor
4487: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
4488: msc_util.msc_log('After executing the cursor');
4484: msc_util.msc_log('after binding the variables');
4485:
4486: -- Execute the cursor
4487: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
4488: msc_util.msc_log('After executing the cursor');
4489:
4490: msc_util.msc_log('rows processed: ' || rows_processed);
4491: msc_util.msc_log('After inserting in msc_alloc_demands part 1');
4492:
4486: -- Execute the cursor
4487: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
4488: msc_util.msc_log('After executing the cursor');
4489:
4490: msc_util.msc_log('rows processed: ' || rows_processed);
4491: msc_util.msc_log('After inserting in msc_alloc_demands part 1');
4492:
4493: -- 2623646 Modified to join with msc_trading_partners/ msc_calendar_dates
4494: -- to move demand on non-working day to prior working day.
4487: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
4488: msc_util.msc_log('After executing the cursor');
4489:
4490: msc_util.msc_log('rows processed: ' || rows_processed);
4491: msc_util.msc_log('After inserting in msc_alloc_demands part 1');
4492:
4493: -- 2623646 Modified to join with msc_trading_partners/ msc_calendar_dates
4494: -- to move demand on non-working day to prior working day.
4495:
4719: )';
4720: END IF;
4721: -- time_phased_atp - project atp forward port
4722:
4723: msc_util.msc_log('After Generating the sql');
4724:
4725: -- Parse cursor handler for sql_stmt: Don't open as its already opened
4726:
4727: DBMS_SQL.PARSE(cur_handler, l_sql_stmt_1, DBMS_SQL.NATIVE);
4724:
4725: -- Parse cursor handler for sql_stmt: Don't open as its already opened
4726:
4727: DBMS_SQL.PARSE(cur_handler, l_sql_stmt_1, DBMS_SQL.NATIVE);
4728: msc_util.msc_log('After parsing the sql');
4729:
4730: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_user_id', l_user_id);
4731: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_sysdate', l_sysdate);
4732: DBMS_SQL.BIND_VARIABLE(cur_handler, ':p_plan_id', p_plan_id);
4730: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_user_id', l_user_id);
4731: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_sysdate', l_sysdate);
4732: DBMS_SQL.BIND_VARIABLE(cur_handler, ':p_plan_id', p_plan_id);
4733: DBMS_SQL.BIND_VARIABLE(cur_handler, ':def_num', '-1');
4734: msc_util.msc_log('after binding the variables');
4735:
4736: -- Execute the cursor
4737: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
4738: msc_util.msc_log('After executing the cursor');
4734: msc_util.msc_log('after binding the variables');
4735:
4736: -- Execute the cursor
4737: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
4738: msc_util.msc_log('After executing the cursor');
4739:
4740: msc_util.msc_log('rows processed: ' || rows_processed);
4741: msc_util.msc_log('after inserting item data into MSC_ALLOC_DEMANDS tables');
4742:
4736: -- Execute the cursor
4737: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
4738: msc_util.msc_log('After executing the cursor');
4739:
4740: msc_util.msc_log('rows processed: ' || rows_processed);
4741: msc_util.msc_log('after inserting item data into MSC_ALLOC_DEMANDS tables');
4742:
4743: /* ------------------------------------------------------------------ */
4744:
4737: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
4738: msc_util.msc_log('After executing the cursor');
4739:
4740: msc_util.msc_log('rows processed: ' || rows_processed);
4741: msc_util.msc_log('after inserting item data into MSC_ALLOC_DEMANDS tables');
4742:
4743: /* ------------------------------------------------------------------ */
4744:
4745: -- 2623646 Modified to join with msc_trading_partners/ msc_calendar_dates
4849: :l_sysdate,
4850: :l_user_id,
4851: :l_sysdate)';
4852:
4853: msc_util.msc_log('After Generating first supplies sql');
4854:
4855: -- Parse cursor handler for sql_stmt: Don't open as its already opened
4856:
4857: DBMS_SQL.PARSE(cur_handler, l_sql_stmt_1, DBMS_SQL.NATIVE);
4854:
4855: -- Parse cursor handler for sql_stmt: Don't open as its already opened
4856:
4857: DBMS_SQL.PARSE(cur_handler, l_sql_stmt_1, DBMS_SQL.NATIVE);
4858: msc_util.msc_log('After parsing first supplies sql');
4859:
4860: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_user_id', l_user_id);
4861: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_sysdate', l_sysdate);
4862: DBMS_SQL.BIND_VARIABLE(cur_handler, ':p_plan_id', p_plan_id);
4860: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_user_id', l_user_id);
4861: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_sysdate', l_sysdate);
4862: DBMS_SQL.BIND_VARIABLE(cur_handler, ':p_plan_id', p_plan_id);
4863: DBMS_SQL.BIND_VARIABLE(cur_handler, ':def_num', '-1');
4864: msc_util.msc_log('after binding the variables');
4865:
4866: -- Execute the cursor
4867: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
4868: msc_util.msc_log('After executing first supplies cursor');
4864: msc_util.msc_log('after binding the variables');
4865:
4866: -- Execute the cursor
4867: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
4868: msc_util.msc_log('After executing first supplies cursor');
4869:
4870: msc_util.msc_log('rows processed: ' || rows_processed);
4871:
4872: msc_util.msc_log('After inserting in msc_alloc_supplies part 1');
4866: -- Execute the cursor
4867: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
4868: msc_util.msc_log('After executing first supplies cursor');
4869:
4870: msc_util.msc_log('rows processed: ' || rows_processed);
4871:
4872: msc_util.msc_log('After inserting in msc_alloc_supplies part 1');
4873:
4874: -- parallel hint can't be used with union all. Use two query instead
4868: msc_util.msc_log('After executing first supplies cursor');
4869:
4870: msc_util.msc_log('rows processed: ' || rows_processed);
4871:
4872: msc_util.msc_log('After inserting in msc_alloc_supplies part 1');
4873:
4874: -- parallel hint can't be used with union all. Use two query instead
4875:
4876: --UNION ALL
5052: :l_sysdate)';
5053: END IF;
5054: -- time_phased_atp - project atp forward port
5055:
5056: msc_util.msc_log('After Generating second supplies sql');
5057:
5058: -- Parse cursor handler for sql_stmt: Don't open as its already opened
5059:
5060: DBMS_SQL.PARSE(cur_handler, l_sql_stmt_1, DBMS_SQL.NATIVE);
5057:
5058: -- Parse cursor handler for sql_stmt: Don't open as its already opened
5059:
5060: DBMS_SQL.PARSE(cur_handler, l_sql_stmt_1, DBMS_SQL.NATIVE);
5061: msc_util.msc_log('After parsing second supplies sql');
5062:
5063: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_user_id', l_user_id);
5064: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_sysdate', l_sysdate);
5065: DBMS_SQL.BIND_VARIABLE(cur_handler, ':p_plan_id', p_plan_id);
5063: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_user_id', l_user_id);
5064: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_sysdate', l_sysdate);
5065: DBMS_SQL.BIND_VARIABLE(cur_handler, ':p_plan_id', p_plan_id);
5066: DBMS_SQL.BIND_VARIABLE(cur_handler, ':def_num', '-1');
5067: msc_util.msc_log('after binding the variables');
5068:
5069: -- Execute the cursor
5070: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
5071: msc_util.msc_log('After executing second supplies cursor');
5067: msc_util.msc_log('after binding the variables');
5068:
5069: -- Execute the cursor
5070: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
5071: msc_util.msc_log('After executing second supplies cursor');
5072:
5073: msc_util.msc_log('rows processed: ' || rows_processed);
5074: msc_util.msc_log('After inserting in msc_alloc_supplies part 2');
5075:
5069: -- Execute the cursor
5070: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
5071: msc_util.msc_log('After executing second supplies cursor');
5072:
5073: msc_util.msc_log('rows processed: ' || rows_processed);
5074: msc_util.msc_log('After inserting in msc_alloc_supplies part 2');
5075:
5076: msc_util.msc_log('after inserting item data into MSC_ALLOC_SUPPLIES tables');
5077:
5070: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
5071: msc_util.msc_log('After executing second supplies cursor');
5072:
5073: msc_util.msc_log('rows processed: ' || rows_processed);
5074: msc_util.msc_log('After inserting in msc_alloc_supplies part 2');
5075:
5076: msc_util.msc_log('after inserting item data into MSC_ALLOC_SUPPLIES tables');
5077:
5078: msc_util.msc_log('Analyze Plan partition for MSC_ALLOC_DEMANDS');
5072:
5073: msc_util.msc_log('rows processed: ' || rows_processed);
5074: msc_util.msc_log('After inserting in msc_alloc_supplies part 2');
5075:
5076: msc_util.msc_log('after inserting item data into MSC_ALLOC_SUPPLIES tables');
5077:
5078: msc_util.msc_log('Analyze Plan partition for MSC_ALLOC_DEMANDS');
5079: fnd_stats.gather_table_stats(ownname=>l_msc_schema,tabname=>'MSC_ALLOC_DEMANDS',
5080: partname=>'ALLOC_DEMANDS_999999',
5074: msc_util.msc_log('After inserting in msc_alloc_supplies part 2');
5075:
5076: msc_util.msc_log('after inserting item data into MSC_ALLOC_SUPPLIES tables');
5077:
5078: msc_util.msc_log('Analyze Plan partition for MSC_ALLOC_DEMANDS');
5079: fnd_stats.gather_table_stats(ownname=>l_msc_schema,tabname=>'MSC_ALLOC_DEMANDS',
5080: partname=>'ALLOC_DEMANDS_999999',
5081: granularity=>'PARTITION',
5082: percent =>10);
5080: partname=>'ALLOC_DEMANDS_999999',
5081: granularity=>'PARTITION',
5082: percent =>10);
5083:
5084: msc_util.msc_log('Analyze Plan partition for MSC_ALLOC_SUPPLIES');
5085: fnd_stats.gather_table_stats(ownname=>l_msc_schema,tabname=>'MSC_ALLOC_SUPPLIES',
5086: partname=>'ALLOC_SUPPLIES_999999',
5087: granularity=>'PARTITION',
5088: percent =>10);
5087: granularity=>'PARTITION',
5088: percent =>10);
5089: ELSE
5090: -- IF l_share_partition = 'Y' THEN
5091: msc_util.msc_log('not a shared plan partition, insert data into temp tables');
5092:
5093: l_temp_table := 'MSC_TEMP_ALLOC_DEM_' || to_char(l_plan_id);
5094:
5095: msc_util.msc_log('temp table : ' || l_temp_table);
5091: msc_util.msc_log('not a shared plan partition, insert data into temp tables');
5092:
5093: l_temp_table := 'MSC_TEMP_ALLOC_DEM_' || to_char(l_plan_id);
5094:
5095: msc_util.msc_log('temp table : ' || l_temp_table);
5096:
5097: SELECT t.tablespace_name, NVL(i.def_tablespace_name, t.tablespace_name)
5098: INTO l_tbspace, l_ind_tbspace
5099: --bug 2495962: Change refrence from dba_xxx to all_xxx tables
5107: AND i.owner (+) = t.table_owner
5108: AND i.table_name (+) = t.table_name
5109: AND rownum = 1;
5110:
5111: msc_util.msc_log('tb space : ' || l_tbspace);
5112: msc_util.msc_log('ind tbspace : ' || l_ind_tbspace);
5113:
5114: --bug 6113544
5115: l_insert_stmt := 'CREATE TABLE ' || l_temp_table
5108: AND i.table_name (+) = t.table_name
5109: AND rownum = 1;
5110:
5111: msc_util.msc_log('tb space : ' || l_tbspace);
5112: msc_util.msc_log('ind tbspace : ' || l_ind_tbspace);
5113:
5114: --bug 6113544
5115: l_insert_stmt := 'CREATE TABLE ' || l_temp_table
5116: || ' TABLESPACE ' || l_tbspace
5152: TABLESPACE ' || l_tbspace || '
5153: -- NOLOGGING
5154: PCTFREE 0 STORAGE(INITIAL 40K NEXT 5M PCTINCREASE 0)';
5155: */
5156: msc_util.msc_log('before creating table : ' || l_temp_table);
5157: BEGIN
5158: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
5159: APPLICATION_SHORT_NAME => 'MSC',
5160: STATEMENT_TYPE => ad_ddl.create_table,
5159: APPLICATION_SHORT_NAME => 'MSC',
5160: STATEMENT_TYPE => ad_ddl.create_table,
5161: STATEMENT => l_insert_stmt,
5162: OBJECT_NAME => l_temp_table);
5163: msc_util.msc_log('after creating table : ' || l_temp_table);
5164:
5165: EXCEPTION
5166: WHEN others THEN
5167: msc_util.msc_log(sqlcode || ': ' || sqlerrm);
5163: msc_util.msc_log('after creating table : ' || l_temp_table);
5164:
5165: EXCEPTION
5166: WHEN others THEN
5167: msc_util.msc_log(sqlcode || ': ' || sqlerrm);
5168: msc_util.msc_log('Exception of create table : ' || l_temp_table);
5169:
5170: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
5171: APPLICATION_SHORT_NAME => 'MSC',
5164:
5165: EXCEPTION
5166: WHEN others THEN
5167: msc_util.msc_log(sqlcode || ': ' || sqlerrm);
5168: msc_util.msc_log('Exception of create table : ' || l_temp_table);
5169:
5170: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
5171: APPLICATION_SHORT_NAME => 'MSC',
5172: STATEMENT_TYPE => ad_ddl.drop_table,
5172: STATEMENT_TYPE => ad_ddl.drop_table,
5173: STATEMENT => 'DROP TABLE ' || l_temp_table,
5174: OBJECT_NAME => l_temp_table);
5175:
5176: msc_util.msc_log('After Drop table : ' ||l_temp_table);
5177: msc_util.msc_log('Before exception create table : ' ||l_temp_table);
5178:
5179: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
5180: APPLICATION_SHORT_NAME => 'MSC',
5173: STATEMENT => 'DROP TABLE ' || l_temp_table,
5174: OBJECT_NAME => l_temp_table);
5175:
5176: msc_util.msc_log('After Drop table : ' ||l_temp_table);
5177: msc_util.msc_log('Before exception create table : ' ||l_temp_table);
5178:
5179: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
5180: APPLICATION_SHORT_NAME => 'MSC',
5181: STATEMENT_TYPE => ad_ddl.create_table,
5180: APPLICATION_SHORT_NAME => 'MSC',
5181: STATEMENT_TYPE => ad_ddl.create_table,
5182: STATEMENT => l_insert_stmt,
5183: OBJECT_NAME => l_temp_table);
5184: msc_util.msc_log('After exception create table : ' ||l_temp_table);
5185: END;
5186:
5187: -- 2623646 Modified to join with msc_trading_partners/ msc_calendar_dates
5188: -- to move demand on non-working day to prior working day.
5329: --bug3684383
5330: peg1.customer_id,
5331: peg1.ship_to_site_id)';
5332:
5333: msc_util.msc_log('After Generating the sql');
5334:
5335: -- Obtain cursor handler for sql_stmt
5336: cur_handler := DBMS_SQL.OPEN_CURSOR;
5337:
5335: -- Obtain cursor handler for sql_stmt
5336: cur_handler := DBMS_SQL.OPEN_CURSOR;
5337:
5338: DBMS_SQL.PARSE(cur_handler, l_insert_stmt, DBMS_SQL.NATIVE);
5339: msc_util.msc_log('After parsing the sql');
5340:
5341: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_user_id', l_user_id);
5342: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_sysdate', l_sysdate);
5343: DBMS_SQL.BIND_VARIABLE(cur_handler, ':p_plan_id', p_plan_id);
5342: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_sysdate', l_sysdate);
5343: DBMS_SQL.BIND_VARIABLE(cur_handler, ':p_plan_id', p_plan_id);
5344: DBMS_SQL.BIND_VARIABLE(cur_handler, ':def_num', '-1');
5345:
5346: msc_util.msc_log('after binding the variables');
5347:
5348: -- Execute the cursor
5349: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
5350: msc_util.msc_log('After executing the cursor');
5346: msc_util.msc_log('after binding the variables');
5347:
5348: -- Execute the cursor
5349: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
5350: msc_util.msc_log('After executing the cursor');
5351:
5352: -- parallel hint can't be used with union all. Use two query instead */
5353:
5354: -- UNION ALL
5570: )';
5571: END IF;
5572: -- time_phased_atp - project atp forward port
5573:
5574: msc_util.msc_log('After Generating the sql');
5575:
5576: -- Parse cursor handler for sql_stmt: Don't open as its already opened
5577:
5578: DBMS_SQL.PARSE(cur_handler, l_insert_stmt, DBMS_SQL.NATIVE);
5575:
5576: -- Parse cursor handler for sql_stmt: Don't open as its already opened
5577:
5578: DBMS_SQL.PARSE(cur_handler, l_insert_stmt, DBMS_SQL.NATIVE);
5579: msc_util.msc_log('After parsing the sql');
5580:
5581: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_user_id', l_user_id);
5582: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_sysdate', l_sysdate);
5583: DBMS_SQL.BIND_VARIABLE(cur_handler, ':p_plan_id', p_plan_id);
5581: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_user_id', l_user_id);
5582: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_sysdate', l_sysdate);
5583: DBMS_SQL.BIND_VARIABLE(cur_handler, ':p_plan_id', p_plan_id);
5584: DBMS_SQL.BIND_VARIABLE(cur_handler, ':def_num', '-1');
5585: msc_util.msc_log('after binding the variables');
5586:
5587: -- Execute the cursor
5588: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
5589: msc_util.msc_log('After executing the cursor');
5585: msc_util.msc_log('after binding the variables');
5586:
5587: -- Execute the cursor
5588: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
5589: msc_util.msc_log('After executing the cursor');
5590:
5591: msc_util.msc_log('after inserting item data into MSC_TEMP_ALLOC_DEMANDS table');
5592:
5593: commit;
5587: -- Execute the cursor
5588: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
5589: msc_util.msc_log('After executing the cursor');
5590:
5591: msc_util.msc_log('after inserting item data into MSC_TEMP_ALLOC_DEMANDS table');
5592:
5593: commit;
5594:
5595: msc_util.msc_log('before creating indexes on temp demand table');
5591: msc_util.msc_log('after inserting item data into MSC_TEMP_ALLOC_DEMANDS table');
5592:
5593: commit;
5594:
5595: msc_util.msc_log('before creating indexes on temp demand table');
5596: l_sql_stmt_1 := 'CREATE INDEX ' || l_temp_table || '_N1 ON ' || l_temp_table || '
5597: --NOLOGGING
5598: (plan_id, inventory_item_id, organization_id, sr_instance_id, demand_class, demand_date)
5599: STORAGE(INITIAL 40K NEXT 2M PCTINCREASE 0) tablespace ' || l_ind_tbspace;
5597: --NOLOGGING
5598: (plan_id, inventory_item_id, organization_id, sr_instance_id, demand_class, demand_date)
5599: STORAGE(INITIAL 40K NEXT 2M PCTINCREASE 0) tablespace ' || l_ind_tbspace;
5600:
5601: msc_util.msc_log('Before index : ' || l_temp_table || '.' || l_temp_table || '_N1');
5602:
5603: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
5604: APPLICATION_SHORT_NAME => 'MSC',
5605: STATEMENT_TYPE => ad_ddl.create_index,
5605: STATEMENT_TYPE => ad_ddl.create_index,
5606: STATEMENT => l_sql_stmt_1,
5607: OBJECT_NAME => l_temp_table);
5608:
5609: msc_util.msc_log('After index : ' || l_temp_table || '.' || l_temp_table || '_N1');
5610:
5611: l_sql_stmt_1 := 'CREATE INDEX ' || l_temp_table || '_N2 ON ' || l_temp_table || '
5612: -- NOLOGGING
5613: --Bug 3629191
5614: (plan_id,
5615: sales_order_line_id)
5616: STORAGE(INITIAL 40K NEXT 2M PCTINCREASE 0) tablespace ' || l_ind_tbspace;
5617:
5618: msc_util.msc_log('Before index : ' || l_temp_table || '.' || l_temp_table || '_N2');
5619:
5620: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
5621: APPLICATION_SHORT_NAME => 'MSC',
5622: STATEMENT_TYPE => ad_ddl.create_index,
5622: STATEMENT_TYPE => ad_ddl.create_index,
5623: STATEMENT => l_sql_stmt_1,
5624: OBJECT_NAME => l_temp_table);
5625:
5626: msc_util.msc_log('After index : ' || l_temp_table || '.' || l_temp_table || '_N2');
5627:
5628: l_sql_stmt_1 := 'CREATE INDEX ' || l_temp_table || '_N3 ON ' || l_temp_table || '
5629: -- NOLOGGING
5630: --Bug 3629191
5631: (plan_id,
5632: parent_demand_id)
5633: STORAGE(INITIAL 40K NEXT 2M PCTINCREASE 0) tablespace ' || l_ind_tbspace;
5634:
5635: msc_util.msc_log('Before index : ' || l_temp_table || '.' || l_temp_table || '_N3');
5636:
5637: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
5638: APPLICATION_SHORT_NAME => 'MSC',
5639: STATEMENT_TYPE => ad_ddl.create_index,
5639: STATEMENT_TYPE => ad_ddl.create_index,
5640: STATEMENT => l_sql_stmt_1,
5641: OBJECT_NAME => l_temp_table);
5642:
5643: msc_util.msc_log('After index : ' || l_temp_table || '.' || l_temp_table || '_N3');
5644: msc_util.msc_log('Done creating indexes on temp demand table');
5645:
5646:
5647: l_temp_table := 'MSC_TEMP_ALLOC_SUP_' || to_char(l_plan_id);
5640: STATEMENT => l_sql_stmt_1,
5641: OBJECT_NAME => l_temp_table);
5642:
5643: msc_util.msc_log('After index : ' || l_temp_table || '.' || l_temp_table || '_N3');
5644: msc_util.msc_log('Done creating indexes on temp demand table');
5645:
5646:
5647: l_temp_table := 'MSC_TEMP_ALLOC_SUP_' || to_char(l_plan_id);
5648:
5659: AND i.owner (+) = t.table_owner
5660: AND i.table_name (+) = t.table_name
5661: AND rownum = 1;
5662:
5663: msc_util.msc_log('tb space : ' || l_tbspace);
5664: msc_util.msc_log('ind tbspace : ' || l_ind_tbspace);
5665:
5666: --bug 6113544
5667: l_insert_stmt := 'CREATE TABLE ' || l_temp_table
5660: AND i.table_name (+) = t.table_name
5661: AND rownum = 1;
5662:
5663: msc_util.msc_log('tb space : ' || l_tbspace);
5664: msc_util.msc_log('ind tbspace : ' || l_ind_tbspace);
5665:
5666: --bug 6113544
5667: l_insert_stmt := 'CREATE TABLE ' || l_temp_table
5668: || ' TABLESPACE ' || l_tbspace
5705: -- NOLOGGING
5706: PCTFREE 0 STORAGE(INITIAL 40K NEXT 5M PCTINCREASE 0)';
5707: */
5708:
5709: msc_util.msc_log('before creating table : ' || l_temp_table);
5710: BEGIN
5711: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
5712: APPLICATION_SHORT_NAME => 'MSC',
5713: STATEMENT_TYPE => ad_ddl.create_table,
5712: APPLICATION_SHORT_NAME => 'MSC',
5713: STATEMENT_TYPE => ad_ddl.create_table,
5714: STATEMENT => l_insert_stmt,
5715: OBJECT_NAME => l_temp_table);
5716: msc_util.msc_log('after creating table : ' || l_temp_table);
5717:
5718: EXCEPTION
5719: WHEN others THEN
5720: msc_util.msc_log(sqlcode || ': ' || sqlerrm);
5716: msc_util.msc_log('after creating table : ' || l_temp_table);
5717:
5718: EXCEPTION
5719: WHEN others THEN
5720: msc_util.msc_log(sqlcode || ': ' || sqlerrm);
5721: msc_util.msc_log('Exception of create table : ' || l_temp_table);
5722:
5723: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
5724: APPLICATION_SHORT_NAME => 'MSC',
5717:
5718: EXCEPTION
5719: WHEN others THEN
5720: msc_util.msc_log(sqlcode || ': ' || sqlerrm);
5721: msc_util.msc_log('Exception of create table : ' || l_temp_table);
5722:
5723: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
5724: APPLICATION_SHORT_NAME => 'MSC',
5725: STATEMENT_TYPE => ad_ddl.drop_table,
5725: STATEMENT_TYPE => ad_ddl.drop_table,
5726: STATEMENT => 'DROP TABLE ' || l_temp_table,
5727: OBJECT_NAME => l_temp_table);
5728:
5729: msc_util.msc_log('After Drop table : ' ||l_temp_table);
5730: msc_util.msc_log('Before exception create table : ' ||l_temp_table);
5731:
5732: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
5733: APPLICATION_SHORT_NAME => 'MSC',
5726: STATEMENT => 'DROP TABLE ' || l_temp_table,
5727: OBJECT_NAME => l_temp_table);
5728:
5729: msc_util.msc_log('After Drop table : ' ||l_temp_table);
5730: msc_util.msc_log('Before exception create table : ' ||l_temp_table);
5731:
5732: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
5733: APPLICATION_SHORT_NAME => 'MSC',
5734: STATEMENT_TYPE => ad_ddl.create_table,
5733: APPLICATION_SHORT_NAME => 'MSC',
5734: STATEMENT_TYPE => ad_ddl.create_table,
5735: STATEMENT => l_insert_stmt,
5736: OBJECT_NAME => l_temp_table);
5737: msc_util.msc_log('After exception create table : ' ||l_temp_table);
5738: END;
5739:
5740: -- 2623646 Modified to join with msc_trading_partners/ msc_calendar_dates
5741: -- to move demand on non-working day to prior working day.
5846: :l_sysdate,
5847: :l_user_id,
5848: :l_sysdate)';
5849:
5850: msc_util.msc_log('After Generating first supplies sql');
5851:
5852: -- Parse cursor handler for sql_stmt: Don't open as its already opened
5853:
5854: DBMS_SQL.PARSE(cur_handler, l_insert_stmt, DBMS_SQL.NATIVE);
5851:
5852: -- Parse cursor handler for sql_stmt: Don't open as its already opened
5853:
5854: DBMS_SQL.PARSE(cur_handler, l_insert_stmt, DBMS_SQL.NATIVE);
5855: msc_util.msc_log('After parsing first supplies sql');
5856:
5857: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_user_id', l_user_id);
5858: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_sysdate', l_sysdate);
5859: DBMS_SQL.BIND_VARIABLE(cur_handler, ':p_plan_id', p_plan_id);
5857: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_user_id', l_user_id);
5858: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_sysdate', l_sysdate);
5859: DBMS_SQL.BIND_VARIABLE(cur_handler, ':p_plan_id', p_plan_id);
5860: DBMS_SQL.BIND_VARIABLE(cur_handler, ':def_num', '-1');
5861: msc_util.msc_log('after binding the variables');
5862:
5863: -- Execute the cursor
5864: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
5865: msc_util.msc_log('After executing first supplies cursor');
5861: msc_util.msc_log('after binding the variables');
5862:
5863: -- Execute the cursor
5864: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
5865: msc_util.msc_log('After executing first supplies cursor');
5866:
5867: msc_util.msc_log('After inserting in msc_alloc_supplies part 1');
5868:
5869: -- parallel hint can't be used with union all. Use two query instead */
5863: -- Execute the cursor
5864: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
5865: msc_util.msc_log('After executing first supplies cursor');
5866:
5867: msc_util.msc_log('After inserting in msc_alloc_supplies part 1');
5868:
5869: -- parallel hint can't be used with union all. Use two query instead */
5870:
5871: -- UNION ALL
6049: :l_sysdate)';
6050: END IF;
6051: -- time_phased_atp - project atp forward port
6052:
6053: msc_util.msc_log('After Generating second supplies sql');
6054:
6055: -- Parse cursor handler for sql_stmt: Don't open as its already opened
6056:
6057: DBMS_SQL.PARSE(cur_handler, l_insert_stmt, DBMS_SQL.NATIVE);
6054:
6055: -- Parse cursor handler for sql_stmt: Don't open as its already opened
6056:
6057: DBMS_SQL.PARSE(cur_handler, l_insert_stmt, DBMS_SQL.NATIVE);
6058: msc_util.msc_log('After parsing second supplies sql');
6059:
6060: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_user_id', l_user_id);
6061: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_sysdate', l_sysdate);
6062: DBMS_SQL.BIND_VARIABLE(cur_handler, ':p_plan_id', p_plan_id);
6060: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_user_id', l_user_id);
6061: DBMS_SQL.BIND_VARIABLE(cur_handler, ':l_sysdate', l_sysdate);
6062: DBMS_SQL.BIND_VARIABLE(cur_handler, ':p_plan_id', p_plan_id);
6063: DBMS_SQL.BIND_VARIABLE(cur_handler, ':def_num', '-1');
6064: msc_util.msc_log('after binding the variables');
6065:
6066: -- Execute the cursor
6067: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
6068: msc_util.msc_log('After executing second supplies cursor');
6064: msc_util.msc_log('after binding the variables');
6065:
6066: -- Execute the cursor
6067: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
6068: msc_util.msc_log('After executing second supplies cursor');
6069:
6070: msc_util.msc_log('After inserting in msc_alloc_supplies part 2');
6071: commit;
6072:
6066: -- Execute the cursor
6067: rows_processed := DBMS_SQL.EXECUTE(cur_handler);
6068: msc_util.msc_log('After executing second supplies cursor');
6069:
6070: msc_util.msc_log('After inserting in msc_alloc_supplies part 2');
6071: commit;
6072:
6073: msc_util.msc_log('before creating indexes on temp supply table');
6074: l_sql_stmt_1 := 'CREATE INDEX ' || l_temp_table || '_N1 ON ' || l_temp_table || '
6069:
6070: msc_util.msc_log('After inserting in msc_alloc_supplies part 2');
6071: commit;
6072:
6073: msc_util.msc_log('before creating indexes on temp supply table');
6074: l_sql_stmt_1 := 'CREATE INDEX ' || l_temp_table || '_N1 ON ' || l_temp_table || '
6075: -- NOLOGGING
6076: (plan_id, inventory_item_id, organization_id, sr_instance_id, demand_class, supply_date)
6077: STORAGE(INITIAL 40K NEXT 2M PCTINCREASE 0) tablespace ' || l_ind_tbspace;
6075: -- NOLOGGING
6076: (plan_id, inventory_item_id, organization_id, sr_instance_id, demand_class, supply_date)
6077: STORAGE(INITIAL 40K NEXT 2M PCTINCREASE 0) tablespace ' || l_ind_tbspace;
6078:
6079: msc_util.msc_log('Before index : ' || l_temp_table || '.' || l_temp_table || '_N1');
6080:
6081: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
6082: APPLICATION_SHORT_NAME => 'MSC',
6083: STATEMENT_TYPE => ad_ddl.create_index,
6083: STATEMENT_TYPE => ad_ddl.create_index,
6084: STATEMENT => l_sql_stmt_1,
6085: OBJECT_NAME => l_temp_table);
6086:
6087: msc_util.msc_log('After index : ' || l_temp_table || '.' || l_temp_table || '_N1');
6088:
6089: l_sql_stmt_1 := 'CREATE INDEX ' || l_temp_table || '_N2 ON ' || l_temp_table || '
6090: -- NOLOGGING
6091: --Bug 3629191
6092: (plan_id,
6093: parent_transaction_id)
6094: STORAGE(INITIAL 40K NEXT 2M PCTINCREASE 0) tablespace ' || l_ind_tbspace;
6095:
6096: msc_util.msc_log('Before index : ' || l_temp_table || '.' || l_temp_table || '_N2');
6097:
6098: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
6099: APPLICATION_SHORT_NAME => 'MSC',
6100: STATEMENT_TYPE => ad_ddl.create_index,
6100: STATEMENT_TYPE => ad_ddl.create_index,
6101: STATEMENT => l_sql_stmt_1,
6102: OBJECT_NAME => l_temp_table);
6103:
6104: msc_util.msc_log('After index : ' || l_temp_table || '.' || l_temp_table || '_N2');
6105:
6106: -- 2623646
6107: l_sql_stmt_1 := 'CREATE INDEX ' || l_temp_table || '_N3 ON ' || l_temp_table || '
6108: -- NOLOGGING
6110: (plan_id,
6111: sales_order_line_id)
6112: STORAGE(INITIAL 40K NEXT 2M PCTINCREASE 0) tablespace ' || l_ind_tbspace;
6113:
6114: msc_util.msc_log('Before index : ' || l_temp_table || '.' || l_temp_table || '_N3');
6115:
6116: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
6117: APPLICATION_SHORT_NAME => 'MSC',
6118: STATEMENT_TYPE => ad_ddl.create_index,
6118: STATEMENT_TYPE => ad_ddl.create_index,
6119: STATEMENT => l_sql_stmt_1,
6120: OBJECT_NAME => l_temp_table);
6121:
6122: msc_util.msc_log('After index : ' || l_temp_table || '.' || l_temp_table || '_N3');
6123:
6124: msc_util.msc_log('Gather Table Stats for Allocated S/D Tables');
6125:
6126: fnd_stats.gather_table_stats(l_msc_schema, 'MSC_TEMP_ALLOC_DEM_' || to_char(l_plan_id), granularity => 'ALL');
6120: OBJECT_NAME => l_temp_table);
6121:
6122: msc_util.msc_log('After index : ' || l_temp_table || '.' || l_temp_table || '_N3');
6123:
6124: msc_util.msc_log('Gather Table Stats for Allocated S/D Tables');
6125:
6126: fnd_stats.gather_table_stats(l_msc_schema, 'MSC_TEMP_ALLOC_DEM_' || to_char(l_plan_id), granularity => 'ALL');
6127: fnd_stats.gather_table_stats(l_msc_schema, 'MSC_TEMP_ALLOC_SUP_' || to_char(l_plan_id), granularity => 'ALL');
6128:
6125:
6126: fnd_stats.gather_table_stats(l_msc_schema, 'MSC_TEMP_ALLOC_DEM_' || to_char(l_plan_id), granularity => 'ALL');
6127: fnd_stats.gather_table_stats(l_msc_schema, 'MSC_TEMP_ALLOC_SUP_' || to_char(l_plan_id), granularity => 'ALL');
6128:
6129: msc_util.msc_log('swap partition for demands');
6130: l_partition_name := 'ALLOC_DEMANDS_' || to_char(l_plan_id);
6131:
6132: msc_util.msc_log('Partition name for msc_alloc_demands table : ' || l_partition_name);
6133:
6128:
6129: msc_util.msc_log('swap partition for demands');
6130: l_partition_name := 'ALLOC_DEMANDS_' || to_char(l_plan_id);
6131:
6132: msc_util.msc_log('Partition name for msc_alloc_demands table : ' || l_partition_name);
6133:
6134: -- swap partiton for supplies and demand part
6135:
6136: l_sql_stmt := 'ALTER TABLE msc_alloc_demands EXCHANGE PARTITION ' || l_partition_name ||
6137: ' with table MSC_TEMP_ALLOC_DEM_'|| to_char(l_plan_id) ||
6138: ' including indexes without validation';
6139:
6140: BEGIN
6141: msc_util.msc_log('Before alter table msc_alloc_demands');
6142: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
6143: APPLICATION_SHORT_NAME => 'MSC',
6144: STATEMENT_TYPE => ad_ddl.alter_table,
6145: STATEMENT => l_sql_stmt,
6145: STATEMENT => l_sql_stmt,
6146: OBJECT_NAME => 'MSC_ALLOC_DEMANDS');
6147: END;
6148:
6149: msc_util.msc_log('swap partition for supplies');
6150: l_partition_name := 'ALLOC_SUPPLIES_' || to_char(l_plan_id);
6151:
6152: msc_util.msc_log('Partition name for msc_alloc_supplies table : ' || l_partition_name);
6153:
6148:
6149: msc_util.msc_log('swap partition for supplies');
6150: l_partition_name := 'ALLOC_SUPPLIES_' || to_char(l_plan_id);
6151:
6152: msc_util.msc_log('Partition name for msc_alloc_supplies table : ' || l_partition_name);
6153:
6154: l_sql_stmt := 'ALTER TABLE msc_alloc_supplies EXCHANGE PARTITION ' || l_partition_name ||
6155: ' with table MSC_TEMP_ALLOC_SUP_'|| to_char(l_plan_id) ||
6156: ' including indexes without validation';
6155: ' with table MSC_TEMP_ALLOC_SUP_'|| to_char(l_plan_id) ||
6156: ' including indexes without validation';
6157:
6158: BEGIN
6159: msc_util.msc_log('Before alter table msc_alloc_supplies');
6160: ad_ddl.do_ddl(APPLSYS_SCHEMA => l_applsys_schema,
6161: APPLICATION_SHORT_NAME => 'MSC',
6162: STATEMENT_TYPE => ad_ddl.alter_table,
6163: STATEMENT => l_sql_stmt,
6163: STATEMENT => l_sql_stmt,
6164: OBJECT_NAME => 'MSC_ALLOC_SUPPLIES');
6165: END;
6166:
6167: msc_util.msc_log('Call procedure clean_temp_tables');
6168:
6169: -- clean temp tables after exchanging partitions
6170: clean_temp_tables(l_applsys_schema, l_plan_id, p_plan_id, NULL);
6171:
6168:
6169: -- clean temp tables after exchanging partitions
6170: clean_temp_tables(l_applsys_schema, l_plan_id, p_plan_id, NULL);
6171:
6172: msc_util.msc_log('After procedure clean_temp_tables');
6173:
6174: END IF; -- IF l_share_partition = 'Y'
6175:
6176: --5027568
6174: END IF; -- IF l_share_partition = 'Y'
6175:
6176: --5027568
6177: --insert reservation_records to msc_alloc_demands.
6178: msc_util.msc_log('inserting reservation rows in msc_alloc_demands');
6179: INSERT INTO MSC_ALLOC_DEMANDS(
6180: plan_id,
6181: inventory_item_id,
6182: organization_id,
6223: where plan_id = p_plan_id
6224: and origination_type = -100
6225: and using_requirement_quantity <> 0
6226: );
6227: msc_util.msc_log('inserted reservation rows in msc_alloc_demands :' || SQL%ROWCOUNT);
6228:
6229: BEGIN
6230: update msc_plans
6231: set summary_flag = 3
6234:
6235: RETCODE := G_SUCCESS;
6236: commit;
6237:
6238: msc_util.msc_log('End procedure post_plan_allocation');
6239:
6240: EXCEPTION
6241: WHEN OTHERS THEN
6242: msc_util.msc_log('Inside main exception of post_plan_allocation');
6238: msc_util.msc_log('End procedure post_plan_allocation');
6239:
6240: EXCEPTION
6241: WHEN OTHERS THEN
6242: msc_util.msc_log('Inside main exception of post_plan_allocation');
6243: msc_util.msc_log(sqlerrm);
6244: ERRBUF := sqlerrm;
6245:
6246: BEGIN
6239:
6240: EXCEPTION
6241: WHEN OTHERS THEN
6242: msc_util.msc_log('Inside main exception of post_plan_allocation');
6243: msc_util.msc_log(sqlerrm);
6244: ERRBUF := sqlerrm;
6245:
6246: BEGIN
6247: update msc_plans
6253: RETCODE := G_ERROR;
6254: IF (l_share_partition = 'Y') THEN
6255: ROLLBACK;
6256: ELSE
6257: msc_util.msc_log('Call procedure clean_temp_tables in exception');
6258:
6259: -- clean temp tables after exchanging partitions
6260: IF l_plan_id IS NOT NULL THEN
6261: clean_temp_tables(l_applsys_schema, l_plan_id, p_plan_id, NULL);
6260: IF l_plan_id IS NOT NULL THEN
6261: clean_temp_tables(l_applsys_schema, l_plan_id, p_plan_id, NULL);
6262: END IF;
6263:
6264: msc_util.msc_log('After procedure clean_temp_tables in exception');
6265: END IF;
6266: END post_plan_allocation;
6267:
6268: -- ngoel 5/7/2002, added new API to be called from planning process to launch concurrent program
6290: -- 24x7 ATP
6291: l_copy_plan_id NUMBER;
6292:
6293: BEGIN
6294: msc_util.msc_log('Begin procedure atp_post_plan_proc');
6295: msc_util.msc_log('plan : ' || p_plan_id);
6296: msc_util.msc_log('Allocation Mode : ' || p_alloc_mode);
6297: msc_util.msc_log('Summary Mode : ' || p_summary_mode);
6298:
6291: l_copy_plan_id NUMBER;
6292:
6293: BEGIN
6294: msc_util.msc_log('Begin procedure atp_post_plan_proc');
6295: msc_util.msc_log('plan : ' || p_plan_id);
6296: msc_util.msc_log('Allocation Mode : ' || p_alloc_mode);
6297: msc_util.msc_log('Summary Mode : ' || p_summary_mode);
6298:
6299: x_retcode := G_SUCCESS;
6292:
6293: BEGIN
6294: msc_util.msc_log('Begin procedure atp_post_plan_proc');
6295: msc_util.msc_log('plan : ' || p_plan_id);
6296: msc_util.msc_log('Allocation Mode : ' || p_alloc_mode);
6297: msc_util.msc_log('Summary Mode : ' || p_summary_mode);
6298:
6299: x_retcode := G_SUCCESS;
6300:
6293: BEGIN
6294: msc_util.msc_log('Begin procedure atp_post_plan_proc');
6295: msc_util.msc_log('plan : ' || p_plan_id);
6296: msc_util.msc_log('Allocation Mode : ' || p_alloc_mode);
6297: msc_util.msc_log('Summary Mode : ' || p_summary_mode);
6298:
6299: x_retcode := G_SUCCESS;
6300:
6301: l_inv_ctp := NVL(FND_PROFILE.value('INV_CTP'), 5);
6299: x_retcode := G_SUCCESS;
6300:
6301: l_inv_ctp := NVL(FND_PROFILE.value('INV_CTP'), 5);
6302:
6303: msc_util.msc_log('inv_ctp := ' || l_inv_ctp);
6304:
6305: /* time_phased_atp changes begin
6306: Always call atp post plan processing conc prog if PDS
6307: IF l_enable_summary_mode = 'Y' AND l_inv_ctp = 4 AND l_alloc_atp = 'N' THEN
6333: AND plans.data_completion_date is not null
6334: -- IO Perf:3693983: Don't Launch ATP Post Plan Processes for IO Plans
6335: AND plans.plan_type <> 4;
6336:
6337: msc_util.msc_log('Count for plan : ' || l_count);
6338:
6339: IF l_count > 0 THEN
6340: msc_util.msc_log('Before refreshing MSC_ATP_PLAN_SN');
6341: l_request_id := FND_REQUEST.SUBMIT_REQUEST(
6336:
6337: msc_util.msc_log('Count for plan : ' || l_count);
6338:
6339: IF l_count > 0 THEN
6340: msc_util.msc_log('Before refreshing MSC_ATP_PLAN_SN');
6341: l_request_id := FND_REQUEST.SUBMIT_REQUEST(
6342: 'MSC',
6343: 'MSCREFMV',
6344: NULL, -- description
6345: NULL, -- start time
6346: FALSE, -- sub request
6347: 'MSC_ATP_PLAN_SN',
6348: 724);
6349: msc_util.msc_log('Request id for refreshing snapshot := ' || l_request_id);
6350: msc_util.msc_log('After refreshing MSC_ATP_PLAN_SN');
6351: END IF; --IF l_count = 0 THEN
6352: END IF;
6353:
6346: FALSE, -- sub request
6347: 'MSC_ATP_PLAN_SN',
6348: 724);
6349: msc_util.msc_log('Request id for refreshing snapshot := ' || l_request_id);
6350: msc_util.msc_log('After refreshing MSC_ATP_PLAN_SN');
6351: END IF; --IF l_count = 0 THEN
6352: END IF;
6353:
6354: -- Bug 3491498, moved prior to call to refresh MSC_ATP_PLAN_SN
6364: AND plans.organization_id = desig.organization_id
6365: AND plans.plan_completion_date is not null
6366: AND plans.data_completion_date is not null;
6367:
6368: msc_util.msc_log('Count for plan : ' || l_count);
6369: msc_util.msc_log('l_submit_request : ' || l_submit_request);
6370: */
6371:
6372: -- 24x7 ATP
6365: AND plans.plan_completion_date is not null
6366: AND plans.data_completion_date is not null;
6367:
6368: msc_util.msc_log('Count for plan : ' || l_count);
6369: msc_util.msc_log('l_submit_request : ' || l_submit_request);
6370: */
6371:
6372: -- 24x7 ATP
6373: BEGIN
6381: when others then
6382: l_copy_plan_id := -1;
6383: END;
6384:
6385: msc_util.msc_log ('l_copy_plan_id = '||l_copy_plan_id);
6386: -- bug 10173431
6387: IF l_copy_plan_id > 0 THEN
6388:
6389: SELECT count(*)
6400: AND plans.plan_type <> 4;
6401:
6402: END IF;
6403:
6404: msc_util.msc_log ('Count for l_count1 = '||l_count1);
6405: if (l_copy_plan_id > 0 and l_count1 > 0) then
6406: -- plan copy exists . force the execution of other things.
6407: l_count := 1;
6408: msc_util.msc_log ('Plan determined to be a 24x7 plan');
6404: msc_util.msc_log ('Count for l_count1 = '||l_count1);
6405: if (l_copy_plan_id > 0 and l_count1 > 0) then
6406: -- plan copy exists . force the execution of other things.
6407: l_count := 1;
6408: msc_util.msc_log ('Plan determined to be a 24x7 plan');
6409: end if;
6410:
6411: IF l_count = 0 THEN
6412: UPDATE msc_plans
6417: RETURN;
6418: END IF;
6419:
6420: BEGIN
6421: msc_util.msc_log('before launching concurrent program');
6422: -- Bug 3292949
6423: UPDATE msc_plans
6424: SET summary_flag = 1
6425: WHERE plan_id = p_plan_id;
6422: -- Bug 3292949
6423: UPDATE msc_plans
6424: SET summary_flag = 1
6425: WHERE plan_id = p_plan_id;
6426: msc_util.msc_log('Reset summary flag back to default:1 before conc prg launch');
6427: -- End Bug 3292949
6428: l_request_id := FND_REQUEST.SUBMIT_REQUEST(
6429: 'MSC',
6430: 'MSC_ATP_PDS_SUMM',
6440: WHERE plan_id = p_plan_id;
6441:
6442: COMMIT;
6443:
6444: msc_util.msc_log('Request ID :' || l_request_id);
6445:
6446: EXCEPTION
6447: WHEN OTHERS THEN
6448: msc_util.msc_log ('Conc. program error : ' || sqlcode || ':' || sqlerrm);
6444: msc_util.msc_log('Request ID :' || l_request_id);
6445:
6446: EXCEPTION
6447: WHEN OTHERS THEN
6448: msc_util.msc_log ('Conc. program error : ' || sqlcode || ':' || sqlerrm);
6449: x_retcode := G_ERROR;
6450: x_errbuf := sqlerrm;
6451: END;
6452: END atp_post_plan_proc;
6475: -- Deleted Related Code.
6476:
6477: RETCODE := G_SUCCESS;
6478:
6479: msc_util.msc_log('********** MRP_ATP_Purge_Temp **********');
6480: msc_util.msc_log('p_hours: ' || p_hours );
6481:
6482: IF NVL(p_hours,0) > 0 THEN
6483:
6476:
6477: RETCODE := G_SUCCESS;
6478:
6479: msc_util.msc_log('********** MRP_ATP_Purge_Temp **********');
6480: msc_util.msc_log('p_hours: ' || p_hours );
6481:
6482: IF NVL(p_hours,0) > 0 THEN
6483:
6484: l_retain_date := sysdate - p_hours/24;
6482: IF NVL(p_hours,0) > 0 THEN
6483:
6484: l_retain_date := sysdate - p_hours/24;
6485:
6486: msc_util.msc_log('Delete records older than l_retain_date ' ||
6487: to_char(l_retain_date, 'DD:MM:YYYY hh24:mi:ss'));
6488: msc_util.msc_log('Now sysdate: ' ||
6489: to_char(sysdate, 'DD:MM:YYYY hh24:mi:ss'));
6490:
6484: l_retain_date := sysdate - p_hours/24;
6485:
6486: msc_util.msc_log('Delete records older than l_retain_date ' ||
6487: to_char(l_retain_date, 'DD:MM:YYYY hh24:mi:ss'));
6488: msc_util.msc_log('Now sysdate: ' ||
6489: to_char(sysdate, 'DD:MM:YYYY hh24:mi:ss'));
6490:
6491:
6492: DELETE FROM mrp_atp_schedule_temp
6491:
6492: DELETE FROM mrp_atp_schedule_temp
6493: WHERE last_update_date < l_retain_date;
6494:
6495: msc_util.msc_log('Records Deleted from mrp_atp_schedule_temp : ' ||
6496: SQL%ROWCOUNT);
6497:
6498: --3670695: issue commit so that rollback segment is freed
6499: commit;
6500:
6501: DELETE FROM mrp_atp_details_temp
6502: WHERE last_update_date < l_retain_date;
6503:
6504: msc_util.msc_log('Records Deleted from mrp_atp_details_temp : ' ||
6505: SQL%ROWCOUNT);
6506: --bug3940999
6507: DELETE FROM msc_atp_src_profile_temp
6508: WHERE last_update_date < l_retain_date;
6506: --bug3940999
6507: DELETE FROM msc_atp_src_profile_temp
6508: WHERE last_update_date < l_retain_date;
6509:
6510: msc_util.msc_log('Records Deleted from msc_atp_src_profile_temp : ' ||
6511: SQL%ROWCOUNT);
6512:
6513: ELSE
6514: msc_util.msc_log('completely purging temp tables');
6510: msc_util.msc_log('Records Deleted from msc_atp_src_profile_temp : ' ||
6511: SQL%ROWCOUNT);
6512:
6513: ELSE
6514: msc_util.msc_log('completely purging temp tables');
6515: --bug3545959 start
6516: SELECT a.oracle_username
6517: INTO l_mrp_schema
6518: FROM FND_ORACLE_USERID a,
6519: FND_PRODUCT_INSTALLATIONS b
6520: WHERE a.oracle_id = b.oracle_id
6521: AND b.application_id = 704;
6522:
6523: msc_util.msc_log('l_mrp_schema: ' || l_mrp_schema );
6524: --bug3545959 end
6525: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_mrp_schema ||'.mrp_atp_schedule_temp';
6526: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_mrp_schema ||'.mrp_atp_details_temp';
6527:
6532: FND_PRODUCT_INSTALLATIONS b
6533: WHERE a.oracle_id = b.oracle_id
6534: AND b.application_id = 724;
6535:
6536: msc_util.msc_log('l_msc_schema: ' || l_msc_schema );
6537:
6538: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_msc_schema ||'.msc_atp_src_profile_temp';
6539:
6540: END IF;
6541:
6542: commit;
6543: EXCEPTION
6544: WHEN others THEN
6545: msc_util.msc_log('Error while purging temp tables : ' ||
6546: sqlcode || ' : ' || sqlerrm);
6547: rollback;
6548: END ATP_Purge_MRP_Temp;
6549:
6574:
6575: -- ATPR4drp
6576: l_plan_type NUMBER;
6577: BEGIN
6578: msc_util.msc_log('************ LOAD_PLAN_SUMMARY_SD begin *************');
6579: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_plan_id - ' || p_plan_id);
6580: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_share_partition - ' || p_share_partition);
6581: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_optimized_plan - ' || p_optimized_plan);
6582: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_full_refresh - ' || p_full_refresh);
6575: -- ATPR4drp
6576: l_plan_type NUMBER;
6577: BEGIN
6578: msc_util.msc_log('************ LOAD_PLAN_SUMMARY_SD begin *************');
6579: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_plan_id - ' || p_plan_id);
6580: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_share_partition - ' || p_share_partition);
6581: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_optimized_plan - ' || p_optimized_plan);
6582: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_full_refresh - ' || p_full_refresh);
6583:
6576: l_plan_type NUMBER;
6577: BEGIN
6578: msc_util.msc_log('************ LOAD_PLAN_SUMMARY_SD begin *************');
6579: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_plan_id - ' || p_plan_id);
6580: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_share_partition - ' || p_share_partition);
6581: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_optimized_plan - ' || p_optimized_plan);
6582: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_full_refresh - ' || p_full_refresh);
6583:
6584: -- ATP4drp changes begin
6577: BEGIN
6578: msc_util.msc_log('************ LOAD_PLAN_SUMMARY_SD begin *************');
6579: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_plan_id - ' || p_plan_id);
6580: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_share_partition - ' || p_share_partition);
6581: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_optimized_plan - ' || p_optimized_plan);
6582: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_full_refresh - ' || p_full_refresh);
6583:
6584: -- ATP4drp changes begin
6585: -- print plan_type
6578: msc_util.msc_log('************ LOAD_PLAN_SUMMARY_SD begin *************');
6579: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_plan_id - ' || p_plan_id);
6580: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_share_partition - ' || p_share_partition);
6581: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_optimized_plan - ' || p_optimized_plan);
6582: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_full_refresh - ' || p_full_refresh);
6583:
6584: -- ATP4drp changes begin
6585: -- print plan_type
6586:
6583:
6584: -- ATP4drp changes begin
6585: -- print plan_type
6586:
6587: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Plan Type p_plan_type -> ' || p_plan_type);
6588: msc_sch_wb.atp_debug('----- ATP4drp Specific Debug Messages -----');
6589:
6590: -- END ATP4drp
6591:
6590: -- END ATP4drp
6591:
6592:
6593: IF p_full_refresh = 1 THEN
6594: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside full summation');
6595:
6596: -- first delete existing data. p_share_partition = 'N' data has already been deleted
6597: IF p_share_partition = 'Y' THEN
6598:
6595:
6596: -- first delete existing data. p_share_partition = 'N' data has already been deleted
6597: IF p_share_partition = 'Y' THEN
6598:
6599: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside shared part_partition');
6600: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'before deleteing data from the table');
6601:
6602: DELETE MSC_ATP_SUMMARY_SD where plan_id = p_plan_id;
6603: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After deleting data from the table');
6596: -- first delete existing data. p_share_partition = 'N' data has already been deleted
6597: IF p_share_partition = 'Y' THEN
6598:
6599: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside shared part_partition');
6600: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'before deleteing data from the table');
6601:
6602: DELETE MSC_ATP_SUMMARY_SD where plan_id = p_plan_id;
6603: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After deleting data from the table');
6604:
6599: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside shared part_partition');
6600: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'before deleteing data from the table');
6601:
6602: DELETE MSC_ATP_SUMMARY_SD where plan_id = p_plan_id;
6603: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After deleting data from the table');
6604:
6605: END IF; --- IF share_partition = 'Y'
6606:
6607: -- Now insert new data
6605: END IF; --- IF share_partition = 'Y'
6606:
6607: -- Now insert new data
6608: IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6609: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside demand priority allocated ATP');
6610: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6611:
6612: load_sd_full_alloc(p_plan_id, p_sys_date);
6613:
6606:
6607: -- Now insert new data
6608: IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6609: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside demand priority allocated ATP');
6610: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6611:
6612: load_sd_full_alloc(p_plan_id, p_sys_date);
6613:
6614: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
6610: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6611:
6612: load_sd_full_alloc(p_plan_id, p_sys_date);
6613:
6614: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
6615: ELSE -- IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6616: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Unallocated ATP');
6617: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6618:
6612: load_sd_full_alloc(p_plan_id, p_sys_date);
6613:
6614: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
6615: ELSE -- IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6616: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Unallocated ATP');
6617: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6618:
6619: IF nvl(p_optimized_plan, 2) <> 1 THEN
6620: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Unconstrained plan');
6613:
6614: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
6615: ELSE -- IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6616: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Unallocated ATP');
6617: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6618:
6619: IF nvl(p_optimized_plan, 2) <> 1 THEN
6620: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Unconstrained plan');
6621: IF p_time_phased_pf = 1 THEN
6616: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Unallocated ATP');
6617: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6618:
6619: IF nvl(p_optimized_plan, 2) <> 1 THEN
6620: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Unconstrained plan');
6621: IF p_time_phased_pf = 1 THEN
6622: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup exists.');
6623: load_sd_full_unalloc_unopt_pf(p_plan_id, p_sys_date);
6624: ELSE
6618:
6619: IF nvl(p_optimized_plan, 2) <> 1 THEN
6620: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Unconstrained plan');
6621: IF p_time_phased_pf = 1 THEN
6622: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup exists.');
6623: load_sd_full_unalloc_unopt_pf(p_plan_id, p_sys_date);
6624: ELSE
6625: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup does not exist.');
6626: load_sd_full_unalloc_unopt(p_plan_id, p_sys_date);
6621: IF p_time_phased_pf = 1 THEN
6622: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup exists.');
6623: load_sd_full_unalloc_unopt_pf(p_plan_id, p_sys_date);
6624: ELSE
6625: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup does not exist.');
6626: load_sd_full_unalloc_unopt(p_plan_id, p_sys_date);
6627: END IF;
6628: ELSE
6629: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Constrained plan');
6625: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup does not exist.');
6626: load_sd_full_unalloc_unopt(p_plan_id, p_sys_date);
6627: END IF;
6628: ELSE
6629: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Constrained plan');
6630: IF p_time_phased_pf = 1 THEN
6631: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup exists.');
6632: load_sd_full_unalloc_opt_pf(p_plan_id, p_sys_date);
6633: ELSE
6627: END IF;
6628: ELSE
6629: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Constrained plan');
6630: IF p_time_phased_pf = 1 THEN
6631: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup exists.');
6632: load_sd_full_unalloc_opt_pf(p_plan_id, p_sys_date);
6633: ELSE
6634: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup does not exist.');
6635: -- ATP4drp Call DRP specific summary
6630: IF p_time_phased_pf = 1 THEN
6631: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup exists.');
6632: load_sd_full_unalloc_opt_pf(p_plan_id, p_sys_date);
6633: ELSE
6634: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup does not exist.');
6635: -- ATP4drp Call DRP specific summary
6636: IF (p_plan_type = 5) THEN
6637: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Call FULL Summary for DRP plan.');
6638: msc_sch_wb.atp_debug('----- ATP4drp Specific Debug Messages -----');
6633: ELSE
6634: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup does not exist.');
6635: -- ATP4drp Call DRP specific summary
6636: IF (p_plan_type = 5) THEN
6637: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Call FULL Summary for DRP plan.');
6638: msc_sch_wb.atp_debug('----- ATP4drp Specific Debug Messages -----');
6639: MSC_ATP_DRP.load_sd_full_drp(p_plan_id, p_sys_date);
6640: ELSE -- Call rest summary
6641: load_sd_full_unalloc_opt(p_plan_id, p_sys_date);
6643: -- End ATP4drp
6644: END IF;
6645: END IF;
6646:
6647: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
6648: END IF; -- IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6649:
6650: ELSE --- IF p_full_refresh = 1 THEN
6651: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside incremental summation');
6647: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
6648: END IF; -- IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6649:
6650: ELSE --- IF p_full_refresh = 1 THEN
6651: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside incremental summation');
6652:
6653: IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6654: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside demand priority allocated ATP');
6655: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6650: ELSE --- IF p_full_refresh = 1 THEN
6651: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside incremental summation');
6652:
6653: IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6654: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside demand priority allocated ATP');
6655: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6656:
6657: load_sd_net_alloc(p_plan_id, p_last_refresh_number, p_new_refresh_number, p_sys_date);
6658:
6651: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside incremental summation');
6652:
6653: IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6654: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside demand priority allocated ATP');
6655: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6656:
6657: load_sd_net_alloc(p_plan_id, p_last_refresh_number, p_new_refresh_number, p_sys_date);
6658:
6659: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
6655: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6656:
6657: load_sd_net_alloc(p_plan_id, p_last_refresh_number, p_new_refresh_number, p_sys_date);
6658:
6659: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
6660: ELSE -- IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6661: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside unallocated ATP');
6662: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6663:
6657: load_sd_net_alloc(p_plan_id, p_last_refresh_number, p_new_refresh_number, p_sys_date);
6658:
6659: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
6660: ELSE -- IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6661: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside unallocated ATP');
6662: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6663:
6664: -- ATP4drp Call DRP specific summary
6665: IF (p_plan_type = 5) THEN
6658:
6659: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
6660: ELSE -- IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6661: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside unallocated ATP');
6662: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6663:
6664: -- ATP4drp Call DRP specific summary
6665: IF (p_plan_type = 5) THEN
6666: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Call NET Summary for DRP plan.');
6662: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6663:
6664: -- ATP4drp Call DRP specific summary
6665: IF (p_plan_type = 5) THEN
6666: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Call NET Summary for DRP plan.');
6667: MSC_ATP_DRP.load_sd_net_drp(p_plan_id, p_last_refresh_number, p_new_refresh_number, p_sys_date);
6668: ELSE -- Call rest summary
6669: load_sd_net_unalloc(p_plan_id, p_last_refresh_number, p_new_refresh_number, p_time_phased_pf, p_sys_date);
6670: END IF;
6669: load_sd_net_unalloc(p_plan_id, p_last_refresh_number, p_new_refresh_number, p_time_phased_pf, p_sys_date);
6670: END IF;
6671: -- ATP4drp
6672:
6673: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
6674: END IF; -- IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6675:
6676: END IF; --- IF p_full_refresh = 1 THEN
6677:
6685: IS
6686: l_user_id NUMBER;
6687: BEGIN
6688:
6689: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_UNOPT Begin ********');
6690:
6691: l_user_id := FND_GLOBAL.USER_ID;
6692:
6693: INSERT INTO MSC_ATP_SUMMARY_SD (
6810: )
6811: GROUP BY plan_id, inventory_item_id,organization_id, sr_instance_id,demand_class, sd_date,
6812: last_update_date, last_updated_by, creation_date, created_by );
6813:
6814: msc_util.msc_log('LOAD_SD_FULL_UNALLOC_UNOPT: ' || 'Records inserted : ' || SQL%ROWCOUNT);
6815: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_UNOPT End ********');
6816:
6817: END LOAD_SD_FULL_UNALLOC_UNOPT;
6818:
6811: GROUP BY plan_id, inventory_item_id,organization_id, sr_instance_id,demand_class, sd_date,
6812: last_update_date, last_updated_by, creation_date, created_by );
6813:
6814: msc_util.msc_log('LOAD_SD_FULL_UNALLOC_UNOPT: ' || 'Records inserted : ' || SQL%ROWCOUNT);
6815: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_UNOPT End ********');
6816:
6817: END LOAD_SD_FULL_UNALLOC_UNOPT;
6818:
6819:
6825: IS
6826: l_user_id NUMBER;
6827: BEGIN
6828:
6829: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_UNOPT_PF Begin ********');
6830:
6831: l_user_id := FND_GLOBAL.USER_ID;
6832:
6833: INSERT INTO MSC_ATP_SUMMARY_SD (
6986: )
6987: GROUP BY plan_id, inventory_item_id,organization_id, sr_instance_id,demand_class, sd_date,
6988: last_update_date, last_updated_by, creation_date, created_by );
6989:
6990: msc_util.msc_log('LOAD_SD_FULL_UNALLOC_UNOPT_PF: ' || 'Records inserted : ' || SQL%ROWCOUNT);
6991: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_UNOPT_PF End ********');
6992:
6993: END LOAD_SD_FULL_UNALLOC_UNOPT_PF;
6994:
6987: GROUP BY plan_id, inventory_item_id,organization_id, sr_instance_id,demand_class, sd_date,
6988: last_update_date, last_updated_by, creation_date, created_by );
6989:
6990: msc_util.msc_log('LOAD_SD_FULL_UNALLOC_UNOPT_PF: ' || 'Records inserted : ' || SQL%ROWCOUNT);
6991: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_UNOPT_PF End ********');
6992:
6993: END LOAD_SD_FULL_UNALLOC_UNOPT_PF;
6994:
6995:
7001: IS
7002: l_user_id NUMBER;
7003: BEGIN
7004:
7005: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_OPT Begin ********');
7006:
7007: l_user_id := FND_GLOBAL.USER_ID;
7008:
7009: INSERT INTO MSC_ATP_SUMMARY_SD (
7094: )
7095: GROUP BY plan_id, inventory_item_id,organization_id, sr_instance_id, demand_class, sd_date,
7096: last_update_date, last_updated_by, creation_date, created_by );
7097:
7098: msc_util.msc_log('LOAD_SD_FULL_UNALLOC_OPT: ' || 'Records inserted : ' || SQL%ROWCOUNT);
7099: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_OPT End ********');
7100:
7101: END LOAD_SD_FULL_UNALLOC_OPT;
7102:
7095: GROUP BY plan_id, inventory_item_id,organization_id, sr_instance_id, demand_class, sd_date,
7096: last_update_date, last_updated_by, creation_date, created_by );
7097:
7098: msc_util.msc_log('LOAD_SD_FULL_UNALLOC_OPT: ' || 'Records inserted : ' || SQL%ROWCOUNT);
7099: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_OPT End ********');
7100:
7101: END LOAD_SD_FULL_UNALLOC_OPT;
7102:
7103:
7109: IS
7110: l_user_id NUMBER;
7111: BEGIN
7112:
7113: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_OPT_PF Begin ********');
7114:
7115: l_user_id := FND_GLOBAL.USER_ID;
7116:
7117: INSERT INTO MSC_ATP_SUMMARY_SD (
7242: )
7243: GROUP BY plan_id, inventory_item_id,organization_id, sr_instance_id, demand_class, sd_date,
7244: last_update_date, last_updated_by, creation_date, created_by );
7245:
7246: msc_util.msc_log('LOAD_SD_FULL_UNALLOC_OPT_PF: ' || 'Records inserted : ' || SQL%ROWCOUNT);
7247: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_OPT_PF End ********');
7248:
7249: END LOAD_SD_FULL_UNALLOC_OPT_PF;
7250:
7243: GROUP BY plan_id, inventory_item_id,organization_id, sr_instance_id, demand_class, sd_date,
7244: last_update_date, last_updated_by, creation_date, created_by );
7245:
7246: msc_util.msc_log('LOAD_SD_FULL_UNALLOC_OPT_PF: ' || 'Records inserted : ' || SQL%ROWCOUNT);
7247: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_OPT_PF End ********');
7248:
7249: END LOAD_SD_FULL_UNALLOC_OPT_PF;
7250:
7251:
7261: IS
7262: l_user_id number;
7263: BEGIN
7264:
7265: msc_util.msc_log('******** LOAD_SD_FULL_ALLOC Begin ********');
7266:
7267: l_user_id := FND_GLOBAL.USER_ID;
7268:
7269: INSERT INTO MSC_ATP_SUMMARY_SD (
7328: GROUP BY plan_id, inventory_item_id, organization_id, sr_instance_id,demand_class, sd_date,
7329: last_update_date, last_updated_by, creation_date, created_by
7330: HAVING sum(SD_QTY) <> 0);
7331:
7332: msc_util.msc_log('LOAD_SD_FULL_ALLOC: ' || 'Records inserted : ' || SQL%ROWCOUNT);
7333: msc_util.msc_log('******** LOAD_SD_FULL_ALLOC End ********');
7334:
7335: END LOAD_SD_FULL_ALLOC;
7336:
7329: last_update_date, last_updated_by, creation_date, created_by
7330: HAVING sum(SD_QTY) <> 0);
7331:
7332: msc_util.msc_log('LOAD_SD_FULL_ALLOC: ' || 'Records inserted : ' || SQL%ROWCOUNT);
7333: msc_util.msc_log('******** LOAD_SD_FULL_ALLOC End ********');
7334:
7335: END LOAD_SD_FULL_ALLOC;
7336:
7337:
7496: GROUP BY inventory_item_id, organization_id, sr_instance_id, sd_date;
7497:
7498: BEGIN
7499:
7500: msc_util.msc_log('******** LOAD_SD_NET_UNALLOC Begin ********');
7501: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_last_refresh_number - ' || p_last_refresh_number);
7502: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_new_refresh_number - ' || p_new_refresh_number);
7503: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_time_phased_pf - ' || p_time_phased_pf);
7504:
7497:
7498: BEGIN
7499:
7500: msc_util.msc_log('******** LOAD_SD_NET_UNALLOC Begin ********');
7501: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_last_refresh_number - ' || p_last_refresh_number);
7502: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_new_refresh_number - ' || p_new_refresh_number);
7503: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_time_phased_pf - ' || p_time_phased_pf);
7504:
7505: l_user_id := FND_GLOBAL.USER_ID;
7498: BEGIN
7499:
7500: msc_util.msc_log('******** LOAD_SD_NET_UNALLOC Begin ********');
7501: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_last_refresh_number - ' || p_last_refresh_number);
7502: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_new_refresh_number - ' || p_new_refresh_number);
7503: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_time_phased_pf - ' || p_time_phased_pf);
7504:
7505: l_user_id := FND_GLOBAL.USER_ID;
7506:
7499:
7500: msc_util.msc_log('******** LOAD_SD_NET_UNALLOC Begin ********');
7501: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_last_refresh_number - ' || p_last_refresh_number);
7502: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_new_refresh_number - ' || p_new_refresh_number);
7503: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_time_phased_pf - ' || p_time_phased_pf);
7504:
7505: l_user_id := FND_GLOBAL.USER_ID;
7506:
7507: IF p_time_phased_pf = 2 THEN
7523: END IF;
7524:
7525: IF l_inventory_item_id_tab IS NOT NULL AND l_inventory_item_id_tab.COUNT > 0 THEN
7526:
7527: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'l_inventory_item_id_tab.COUNT := ' || l_inventory_item_id_tab.COUNT);
7528:
7529: forall j IN l_inventory_item_id_tab.first.. l_inventory_item_id_tab.last
7530: UPDATE MSC_ATP_SUMMARY_SD
7531: SET sd_qty = sd_qty + l_sd_quantity_tab(j),
7536: AND inventory_item_id = l_inventory_item_id_tab(j)
7537: AND organization_id = l_organization_id_tab(j)
7538: AND sd_date = l_sd_date_tab(j);
7539:
7540: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'After FORALL UPDATE');
7541:
7542: FOR j IN l_inventory_item_id_tab.first.. l_inventory_item_id_tab.last LOOP
7543: IF SQL%BULK_ROWCOUNT(j) = 0 THEN
7544: l_ins_sr_instance_id_tab.EXTEND;
7556: END LOOP;
7557:
7558: IF l_ins_inventory_item_id_tab IS NOT NULL AND l_ins_inventory_item_id_tab.COUNT > 0 THEN
7559:
7560: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'l_ins_inventory_item_id_tab.COUNT := ' || l_ins_inventory_item_id_tab.COUNT);
7561:
7562: forall j IN l_ins_inventory_item_id_tab.first.. l_ins_inventory_item_id_tab.last
7563: INSERT INTO MSC_ATP_SUMMARY_SD (
7564: plan_id,
7583: l_user_id,
7584: p_sys_date,
7585: l_user_id);
7586:
7587: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'After FORALL INSERT');
7588:
7589: ELSE
7590: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'No records to be inserted');
7591: END IF;
7586:
7587: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'After FORALL INSERT');
7588:
7589: ELSE
7590: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'No records to be inserted');
7591: END IF;
7592: ELSE
7593: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'No records fetched in the net cursor');
7594: END IF;
7589: ELSE
7590: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'No records to be inserted');
7591: END IF;
7592: ELSE
7593: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'No records fetched in the net cursor');
7594: END IF;
7595:
7596: msc_util.msc_log('******** LOAD_SD_NET_UNALLOC End ********');
7597:
7592: ELSE
7593: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'No records fetched in the net cursor');
7594: END IF;
7595:
7596: msc_util.msc_log('******** LOAD_SD_NET_UNALLOC End ********');
7597:
7598: END LOAD_SD_NET_UNALLOC;
7599:
7600:
7660: GROUP BY inventory_item_id, organization_id, sr_instance_id, demand_class, sd_date;
7661:
7662: BEGIN
7663:
7664: msc_util.msc_log('******** LOAD_SD_NET_ALLOC Begin ********');
7665:
7666: l_user_id := FND_GLOBAL.USER_ID;
7667:
7668: OPEN c_net_sd(p_plan_id, p_last_refresh_number, p_new_refresh_number);
7675: CLOSE c_net_sd;
7676:
7677: IF l_inventory_item_id_tab IS NOT NULL AND l_inventory_item_id_tab.COUNT > 0 THEN
7678:
7679: msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'l_inventory_item_id_tab.COUNT := ' || l_inventory_item_id_tab.COUNT);
7680:
7681: forall j IN l_inventory_item_id_tab.first.. l_inventory_item_id_tab.last
7682: UPDATE MSC_ATP_SUMMARY_SD
7683: SET sd_qty = sd_qty + l_sd_quantity_tab(j),
7689: AND organization_id = l_organization_id_tab(j)
7690: AND sd_date = l_sd_date_tab(j)
7691: AND demand_class = l_demand_class_tab(j);
7692:
7693: msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'After FORALL UPDATE');
7694:
7695: FOR j IN l_inventory_item_id_tab.first.. l_inventory_item_id_tab.last LOOP
7696: IF SQL%BULK_ROWCOUNT(j) = 0 THEN
7697: l_ins_sr_instance_id_tab.EXTEND;
7711: END LOOP;
7712:
7713: IF l_ins_inventory_item_id_tab IS NOT NULL AND l_ins_inventory_item_id_tab.COUNT > 0 THEN
7714:
7715: msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'l_ins_inventory_item_id_tab.COUNT := ' || l_ins_inventory_item_id_tab.COUNT);
7716:
7717: forall j IN l_ins_inventory_item_id_tab.first.. l_ins_inventory_item_id_tab.last
7718: INSERT INTO MSC_ATP_SUMMARY_SD (
7719: plan_id,
7738: l_user_id,
7739: p_sys_date,
7740: l_user_id);
7741:
7742: msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'After FORALL INSERT');
7743:
7744: ELSE
7745: msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'No records to be inserted');
7746: END IF;
7741:
7742: msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'After FORALL INSERT');
7743:
7744: ELSE
7745: msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'No records to be inserted');
7746: END IF;
7747: ELSE
7748: msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'No records fetched in the net cursor');
7749: END IF;
7744: ELSE
7745: msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'No records to be inserted');
7746: END IF;
7747: ELSE
7748: msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'No records fetched in the net cursor');
7749: END IF;
7750:
7751: msc_util.msc_log('******** LOAD_SD_NET_ALLOC End ********');
7752:
7747: ELSE
7748: msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'No records fetched in the net cursor');
7749: END IF;
7750:
7751: msc_util.msc_log('******** LOAD_SD_NET_ALLOC End ********');
7752:
7753: END LOAD_SD_NET_ALLOC;
7754:
7755:
7767: l_user_id number;
7768: -- l_org_code VARCHAR2(7); -- Bug 3912422
7769: BEGIN
7770:
7771: msc_util.msc_log('******** LOAD_SUP_DATA_FULL Begin ********');
7772:
7773: SELECT trunc(p.plan_start_date),
7774: p.sr_instance_id,
7775: p.organization_id,
7786: AND p.organization_id = tp.sr_tp_id
7787: AND p.sr_instance_id = tp.sr_instance_id
7788: AND tp.partner_type = 3;
7789:
7790: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_plan_start_date = ' || l_plan_start_date);
7791: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_instance_id = ' || l_instance_id);
7792: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_org_id = ' || l_org_id);
7793: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_calendar_code = ' || l_calendar_code);
7794:
7787: AND p.sr_instance_id = tp.sr_instance_id
7788: AND tp.partner_type = 3;
7789:
7790: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_plan_start_date = ' || l_plan_start_date);
7791: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_instance_id = ' || l_instance_id);
7792: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_org_id = ' || l_org_id);
7793: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_calendar_code = ' || l_calendar_code);
7794:
7795: l_user_id := FND_GLOBAL.USER_ID;
7788: AND tp.partner_type = 3;
7789:
7790: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_plan_start_date = ' || l_plan_start_date);
7791: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_instance_id = ' || l_instance_id);
7792: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_org_id = ' || l_org_id);
7793: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_calendar_code = ' || l_calendar_code);
7794:
7795: l_user_id := FND_GLOBAL.USER_ID;
7796:
7789:
7790: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_plan_start_date = ' || l_plan_start_date);
7791: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_instance_id = ' || l_instance_id);
7792: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_org_id = ' || l_org_id);
7793: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_calendar_code = ' || l_calendar_code);
7794:
7795: l_user_id := FND_GLOBAL.USER_ID;
7796:
7797: INSERT INTO MSC_ATP_SUMMARY_SUP(
7951: group by plan_id,inventory_item_id, supplier_id, supplier_site_id, sr_instance_id,
7952: sd_date, demand_class, last_update_date, last_updated_by, creation_date, created_by
7953: );
7954:
7955: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'Records inserted : ' || SQL%ROWCOUNT);
7956: msc_util.msc_log('******** LOAD_SUP_DATA_FULL End ********');
7957:
7958: END LOAD_SUP_DATA_FULL;
7959:
7952: sd_date, demand_class, last_update_date, last_updated_by, creation_date, created_by
7953: );
7954:
7955: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'Records inserted : ' || SQL%ROWCOUNT);
7956: msc_util.msc_log('******** LOAD_SUP_DATA_FULL End ********');
7957:
7958: END LOAD_SUP_DATA_FULL;
7959:
7960:
8037:
8038:
8039: BEGIN
8040:
8041: msc_util.msc_log('******** LOAD_SUP_DATA_NET Begin ********');
8042:
8043: l_user_id := FND_GLOBAL.USER_ID;
8044:
8045: OPEN c_net_sup(p_plan_id, p_last_refresh_number, p_new_refresh_number);
8052: CLOSE c_net_sup;
8053:
8054: IF l_inventory_item_id_tab IS NOT NULL AND l_inventory_item_id_tab.COUNT > 0 THEN
8055:
8056: msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'l_inventory_item_id_tab.COUNT := ' || l_inventory_item_id_tab.COUNT);
8057:
8058: forall j IN l_inventory_item_id_tab.first.. l_inventory_item_id_tab.last
8059: UPDATE MSC_ATP_SUMMARY_SUP
8060: SET sd_qty = sd_qty + l_sd_quantity_tab(j),
8066: AND supplier_id = l_supplier_id_tab(j)
8067: AND supplier_site_id = l_supplier_site_id_tab(j)
8068: AND sd_date = l_sd_date_tab(j);
8069:
8070: msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'After FORALL UPDATE');
8071:
8072: FOR j IN l_inventory_item_id_tab.first.. l_inventory_item_id_tab.last LOOP
8073: IF SQL%BULK_ROWCOUNT(j) = 0 THEN
8074: l_ins_sr_instance_id_tab.EXTEND;
8088: END LOOP;
8089:
8090: IF l_ins_inventory_item_id_tab IS NOT NULL AND l_ins_inventory_item_id_tab.COUNT > 0 THEN
8091:
8092: msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'l_ins_inventory_item_id_tab.COUNT := ' || l_ins_inventory_item_id_tab.COUNT);
8093:
8094: forall j IN l_ins_inventory_item_id_tab.first.. l_ins_inventory_item_id_tab.last
8095: INSERT INTO MSC_ATP_SUMMARY_SUP (
8096: plan_id,
8117: l_user_id,
8118: p_sys_date,
8119: l_user_id);
8120:
8121: msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'After FORALL INSERT');
8122:
8123: ELSE
8124: msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'No records to be inserted');
8125: END IF;
8120:
8121: msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'After FORALL INSERT');
8122:
8123: ELSE
8124: msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'No records to be inserted');
8125: END IF;
8126: ELSE
8127: msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'No records fetched in the net cursor');
8128: END IF;
8123: ELSE
8124: msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'No records to be inserted');
8125: END IF;
8126: ELSE
8127: msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'No records fetched in the net cursor');
8128: END IF;
8129:
8130: msc_util.msc_log('******** LOAD_SUP_DATA_NET End ********');
8131:
8126: ELSE
8127: msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'No records fetched in the net cursor');
8128: END IF;
8129:
8130: msc_util.msc_log('******** LOAD_SUP_DATA_NET End ********');
8131:
8132: END LOAD_SUP_DATA_NET;
8133:
8134:
8138: IS
8139: l_user_id number;
8140: BEGIN
8141:
8142: msc_util.msc_log('******** LOAD_RES_FULL_UNOPT_BATCH Begin ********');
8143:
8144: l_user_id := FND_GLOBAL.USER_ID;
8145:
8146: -- summary enhancement - made changes to make it consistent with regular SQLs in MSCRATPB
8277: ) group by plan_id, department_id, resource_id, organization_id, sr_instance_id, sd_date,
8278: last_update_date,last_updated_by, creation_date, created_by
8279: );
8280:
8281: msc_util.msc_log('LOAD_RES_FULL_UNOPT_BATCH: ' || 'Records inserted : ' || SQL%ROWCOUNT);
8282: msc_util.msc_log('******** LOAD_RES_FULL_UNOPT_BATCH End ********');
8283:
8284: END LOAD_RES_FULL_UNOPT_BATCH;
8285:
8278: last_update_date,last_updated_by, creation_date, created_by
8279: );
8280:
8281: msc_util.msc_log('LOAD_RES_FULL_UNOPT_BATCH: ' || 'Records inserted : ' || SQL%ROWCOUNT);
8282: msc_util.msc_log('******** LOAD_RES_FULL_UNOPT_BATCH End ********');
8283:
8284: END LOAD_RES_FULL_UNOPT_BATCH;
8285:
8286:
8290: IS
8291: l_user_id number;
8292: BEGIN
8293:
8294: msc_util.msc_log('******** LOAD_RES_FULL_OPT_BATCH Begin ********');
8295:
8296: l_user_id := FND_GLOBAL.USER_ID;
8297:
8298: -- summary enhancement - made changes to make it consistent with regular SQLs in MSCRATPB
8421: group by plan_id, department_id, resource_id, organization_id, sr_instance_id, sd_date,
8422: last_update_date,last_updated_by, creation_date, created_by
8423: );
8424:
8425: msc_util.msc_log('LOAD_RES_FULL_OPT_BATCH: ' || 'Records inserted : ' || SQL%ROWCOUNT);
8426: msc_util.msc_log('******** LOAD_RES_FULL_OPT_BATCH End ********');
8427:
8428: END LOAD_RES_FULL_OPT_BATCH;
8429:
8422: last_update_date,last_updated_by, creation_date, created_by
8423: );
8424:
8425: msc_util.msc_log('LOAD_RES_FULL_OPT_BATCH: ' || 'Records inserted : ' || SQL%ROWCOUNT);
8426: msc_util.msc_log('******** LOAD_RES_FULL_OPT_BATCH End ********');
8427:
8428: END LOAD_RES_FULL_OPT_BATCH;
8429:
8430:
8434: IS
8435: l_user_id number;
8436: BEGIN
8437:
8438: msc_util.msc_log('******** LOAD_RES_FULL_UNOPT_NOBATCH Begin ********');
8439:
8440: l_user_id := FND_GLOBAL.USER_ID;
8441:
8442: -- summary enhancement - made changes to make it consistent with regular SQLs in MSCRATPB
8538: group by plan_id, department_id, resource_id, organization_id, sr_instance_id, sd_date,
8539: last_update_date,last_updated_by, creation_date, created_by
8540: );
8541:
8542: msc_util.msc_log('LOAD_RES_FULL_UNOPT_NOBATCH: ' || 'Records inserted : ' || SQL%ROWCOUNT);
8543: msc_util.msc_log('******** LOAD_RES_FULL_UNOPT_NOBATCH End ********');
8544:
8545: END LOAD_RES_FULL_UNOPT_NOBATCH;
8546:
8539: last_update_date,last_updated_by, creation_date, created_by
8540: );
8541:
8542: msc_util.msc_log('LOAD_RES_FULL_UNOPT_NOBATCH: ' || 'Records inserted : ' || SQL%ROWCOUNT);
8543: msc_util.msc_log('******** LOAD_RES_FULL_UNOPT_NOBATCH End ********');
8544:
8545: END LOAD_RES_FULL_UNOPT_NOBATCH;
8546:
8547:
8551: IS
8552: l_user_id number;
8553: BEGIN
8554:
8555: msc_util.msc_log('******** LOAD_RES_FULL_OPT_NOBATCH Begin ********');
8556:
8557: l_user_id := FND_GLOBAL.USER_ID;
8558:
8559: -- summary enhancement - made changes to make it consistent with regular SQLs in MSCRATPB
8643: group by plan_id, department_id, resource_id, organization_id, sr_instance_id, sd_date,
8644: last_update_date,last_updated_by, creation_date, created_by
8645: );
8646:
8647: msc_util.msc_log('LOAD_RES_FULL_OPT_NOBATCH: ' || 'Records inserted : ' || SQL%ROWCOUNT);
8648: msc_util.msc_log('******** LOAD_RES_FULL_OPT_NOBATCH End ********');
8649:
8650: END LOAD_RES_FULL_OPT_NOBATCH;
8651:
8644: last_update_date,last_updated_by, creation_date, created_by
8645: );
8646:
8647: msc_util.msc_log('LOAD_RES_FULL_OPT_NOBATCH: ' || 'Records inserted : ' || SQL%ROWCOUNT);
8648: msc_util.msc_log('******** LOAD_RES_FULL_OPT_NOBATCH End ********');
8649:
8650: END LOAD_RES_FULL_OPT_NOBATCH;
8651:
8652:
8697: -- Bug 3348095 Only ATP created records, so use end_date.
8698:
8699: BEGIN
8700:
8701: msc_util.msc_log('******** LOAD_RES_DATA_NET Begin ********');
8702:
8703: l_user_id := FND_GLOBAL.USER_ID;
8704:
8705: OPEN c_net_res(p_plan_id, p_last_refresh_number, p_new_refresh_number);
8712: CLOSE c_net_res;
8713:
8714: IF l_resource_id_tab IS NOT NULL AND l_resource_id_tab.COUNT > 0 THEN
8715:
8716: msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'l_resource_id_tab.COUNT := ' || l_resource_id_tab.COUNT);
8717:
8718: forall j IN l_resource_id_tab.first.. l_resource_id_tab.last
8719: UPDATE MSC_ATP_SUMMARY_RES
8720: SET sd_qty = sd_qty + l_sd_quantity_tab(j),
8726: AND resource_id = l_resource_id_tab(j)
8727: AND department_id = l_department_id_tab(j)
8728: AND sd_date = l_sd_date_tab(j);
8729:
8730: msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'After FORALL UPDATE');
8731:
8732: FOR j IN l_resource_id_tab.first.. l_resource_id_tab.last LOOP
8733: IF SQL%BULK_ROWCOUNT(j) = 0 THEN
8734: l_ins_department_id_tab.EXTEND;
8748: END LOOP;
8749:
8750: IF l_ins_resource_id_tab IS NOT NULL AND l_ins_resource_id_tab.COUNT > 0 THEN
8751:
8752: msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'l_ins_resource_id_tab.COUNT := ' || l_ins_resource_id_tab.COUNT);
8753:
8754: forall j IN l_ins_resource_id_tab.first.. l_ins_resource_id_tab.last
8755: INSERT INTO MSC_ATP_SUMMARY_RES (
8756: plan_id,
8775: l_user_id,
8776: p_sys_date,
8777: l_user_id);
8778:
8779: msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'After FORALL INSERT');
8780:
8781: ELSE
8782: msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'No records to be inserted');
8783: END IF;
8778:
8779: msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'After FORALL INSERT');
8780:
8781: ELSE
8782: msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'No records to be inserted');
8783: END IF;
8784: ELSE
8785: msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'No records fetched in the net cursor');
8786: END IF;
8781: ELSE
8782: msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'No records to be inserted');
8783: END IF;
8784: ELSE
8785: msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'No records fetched in the net cursor');
8786: END IF;
8787:
8788: msc_util.msc_log('******** LOAD_RES_DATA_NET End ********');
8789:
8784: ELSE
8785: msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'No records fetched in the net cursor');
8786: END IF;
8787:
8788: msc_util.msc_log('******** LOAD_RES_DATA_NET End ********');
8789:
8790: END LOAD_RES_DATA_NET;
8791:
8792:
8795: IS
8796: l_partition_name varchar2(30);
8797: l_sql_stmt varchar2(300);
8798: BEGIN
8799: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'p_plan_id - ' || p_plan_id);
8800: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'p_applsys_schema - ' || p_applsys_schema);
8801:
8802: ------------------------------------------------
8803:
8796: l_partition_name varchar2(30);
8797: l_sql_stmt varchar2(300);
8798: BEGIN
8799: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'p_plan_id - ' || p_plan_id);
8800: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'p_applsys_schema - ' || p_applsys_schema);
8801:
8802: ------------------------------------------------
8803:
8804: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'truncate partition for sd');
8800: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'p_applsys_schema - ' || p_applsys_schema);
8801:
8802: ------------------------------------------------
8803:
8804: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'truncate partition for sd');
8805: l_partition_name := 'ATP_SUMMARY_SD_' || to_char(p_plan_id);
8806: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'Partition name : ' || l_partition_name);
8807: l_sql_stmt := 'ALTER TABLE MSC_ATP_SUMMARY_SD TRUNCATE PARTITION ' ||
8808: l_partition_name || ' DROP STORAGE';
8802: ------------------------------------------------
8803:
8804: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'truncate partition for sd');
8805: l_partition_name := 'ATP_SUMMARY_SD_' || to_char(p_plan_id);
8806: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'Partition name : ' || l_partition_name);
8807: l_sql_stmt := 'ALTER TABLE MSC_ATP_SUMMARY_SD TRUNCATE PARTITION ' ||
8808: l_partition_name || ' DROP STORAGE';
8809:
8810: BEGIN
8807: l_sql_stmt := 'ALTER TABLE MSC_ATP_SUMMARY_SD TRUNCATE PARTITION ' ||
8808: l_partition_name || ' DROP STORAGE';
8809:
8810: BEGIN
8811: msc_util.msc_log('Before alter table MSC_ATP_SUMMARY_SD');
8812: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
8813: APPLICATION_SHORT_NAME => 'MSC',
8814: STATEMENT_TYPE => ad_ddl.alter_table,
8815: STATEMENT => l_sql_stmt,
8814: STATEMENT_TYPE => ad_ddl.alter_table,
8815: STATEMENT => l_sql_stmt,
8816: OBJECT_NAME => 'MSC_ATP_SUMMARY_SD');
8817: END;
8818: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'After truncating SD partition');
8819:
8820: ------------------------------------------------
8821:
8822: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'truncate partition for sup');
8818: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'After truncating SD partition');
8819:
8820: ------------------------------------------------
8821:
8822: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'truncate partition for sup');
8823: l_partition_name := 'ATP_SUMMARY_SUP_' || to_char(p_plan_id);
8824: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'Partition name : ' || l_partition_name);
8825: l_sql_stmt := 'ALTER TABLE MSC_ATP_SUMMARY_SUP TRUNCATE PARTITION ' ||
8826: l_partition_name || ' DROP STORAGE';
8820: ------------------------------------------------
8821:
8822: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'truncate partition for sup');
8823: l_partition_name := 'ATP_SUMMARY_SUP_' || to_char(p_plan_id);
8824: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'Partition name : ' || l_partition_name);
8825: l_sql_stmt := 'ALTER TABLE MSC_ATP_SUMMARY_SUP TRUNCATE PARTITION ' ||
8826: l_partition_name || ' DROP STORAGE';
8827:
8828: BEGIN
8825: l_sql_stmt := 'ALTER TABLE MSC_ATP_SUMMARY_SUP TRUNCATE PARTITION ' ||
8826: l_partition_name || ' DROP STORAGE';
8827:
8828: BEGIN
8829: msc_util.msc_log('Before alter table MSC_ATP_SUMMARY_SUP');
8830: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
8831: APPLICATION_SHORT_NAME => 'MSC',
8832: STATEMENT_TYPE => ad_ddl.alter_table,
8833: STATEMENT => l_sql_stmt,
8832: STATEMENT_TYPE => ad_ddl.alter_table,
8833: STATEMENT => l_sql_stmt,
8834: OBJECT_NAME => 'MSC_ATP_SUMMARY_SUP');
8835: END;
8836: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'After truncating sup partition');
8837:
8838: ------------------------------------------------
8839:
8840: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'truncate partition for res');
8836: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'After truncating sup partition');
8837:
8838: ------------------------------------------------
8839:
8840: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'truncate partition for res');
8841: l_partition_name := 'ATP_SUMMARY_RES_' || to_char(p_plan_id);
8842: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'Partition name : ' || l_partition_name);
8843: l_sql_stmt := 'ALTER TABLE MSC_ATP_SUMMARY_RES TRUNCATE PARTITION ' ||
8844: l_partition_name || ' DROP STORAGE';
8838: ------------------------------------------------
8839:
8840: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'truncate partition for res');
8841: l_partition_name := 'ATP_SUMMARY_RES_' || to_char(p_plan_id);
8842: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'Partition name : ' || l_partition_name);
8843: l_sql_stmt := 'ALTER TABLE MSC_ATP_SUMMARY_RES TRUNCATE PARTITION ' ||
8844: l_partition_name || ' DROP STORAGE';
8845:
8846: BEGIN
8843: l_sql_stmt := 'ALTER TABLE MSC_ATP_SUMMARY_RES TRUNCATE PARTITION ' ||
8844: l_partition_name || ' DROP STORAGE';
8845:
8846: BEGIN
8847: msc_util.msc_log('Before alter table MSC_ATP_SUMMARY_RES');
8848: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
8849: APPLICATION_SHORT_NAME => 'MSC',
8850: STATEMENT_TYPE => ad_ddl.alter_table,
8851: STATEMENT => l_sql_stmt,
8850: STATEMENT_TYPE => ad_ddl.alter_table,
8851: STATEMENT => l_sql_stmt,
8852: OBJECT_NAME => 'MSC_ATP_SUMMARY_RES');
8853: END;
8854: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'After truncating res partition');
8855:
8856: ------------------------------------------------
8857:
8858: END Truncate_Summ_Plan_Partition;
8865: l_msc_schema VARCHAR2(30);
8866: BEGIN
8867:
8868: ------------------------------------------------
8869: msc_util.msc_log('Gather_Summ_Plan_Stats: ' || 'Gather Table Stats for S/D Tables');
8870:
8871: SELECT a.oracle_username
8872: INTO l_msc_schema
8873: FROM FND_ORACLE_USERID a,
8885: tabname=>'MSC_ATP_SUMMARY_SD',
8886: partname=>l_partition_name,
8887: granularity=>'PARTITION',
8888: percent =>10);
8889: msc_util.msc_log('Gather_Summ_Plan_Stats: ' || 'After gathering stats for S/D tables');
8890: ------------------------------------------------
8891: msc_util.msc_log('Gather_Summ_Plan_Stats: ' || 'Gather Table Stats for Sup Tables');
8892: IF p_share_partition = 'Y' THEN
8893: l_partition_name := 'ATP_SUMMARY_SUP_999999';
8887: granularity=>'PARTITION',
8888: percent =>10);
8889: msc_util.msc_log('Gather_Summ_Plan_Stats: ' || 'After gathering stats for S/D tables');
8890: ------------------------------------------------
8891: msc_util.msc_log('Gather_Summ_Plan_Stats: ' || 'Gather Table Stats for Sup Tables');
8892: IF p_share_partition = 'Y' THEN
8893: l_partition_name := 'ATP_SUMMARY_SUP_999999';
8894: ELSE
8895: l_partition_name := 'ATP_SUMMARY_SUP_' || to_char(p_plan_id);
8899: tabname=>'MSC_ATP_SUMMARY_SUP',
8900: partname=>l_partition_name,
8901: granularity=>'PARTITION',
8902: percent =>10);
8903: msc_util.msc_log('Gather_Summ_Plan_Stats: ' || 'After gathering stats for Sup tables');
8904: ------------------------------------------------
8905: msc_util.msc_log('Gather_Summ_Plan_Stats: ' || 'Gather Table Stats for Res Tables');
8906: IF p_share_partition = 'Y' THEN
8907: l_partition_name := 'ATP_SUMMARY_RES_999999';
8901: granularity=>'PARTITION',
8902: percent =>10);
8903: msc_util.msc_log('Gather_Summ_Plan_Stats: ' || 'After gathering stats for Sup tables');
8904: ------------------------------------------------
8905: msc_util.msc_log('Gather_Summ_Plan_Stats: ' || 'Gather Table Stats for Res Tables');
8906: IF p_share_partition = 'Y' THEN
8907: l_partition_name := 'ATP_SUMMARY_RES_999999';
8908: ELSE
8909: l_partition_name := 'ATP_SUMMARY_RES_' || to_char(p_plan_id);
8913: tabname=>'MSC_ATP_SUMMARY_RES',
8914: partname=>l_partition_name,
8915: granularity=>'PARTITION',
8916: percent =>10);
8917: msc_util.msc_log('Gather_Summ_Plan_Stats: ' || 'After gathering stats for Res tables');
8918: ------------------------------------------------
8919:
8920:
8921: END Gather_Summ_Plan_Stats;
8933: l_count NUMBER := 0;
8934: --bug3663487 end
8935: Begin
8936:
8937: msc_util.msc_log('Begin procedure atp_snapshot_hook');
8938:
8939: SELECT count(*)
8940: INTO l_count
8941: FROM msc_plans plans,
8947: AND plans.organization_id = desig.organization_id
8948: AND (desig.inventory_atp_flag = 1
8949: OR plans.copy_plan_id IS NOT NULL);
8950:
8951: msc_util.msc_log('atp_snapshot_hook: l_count: '|| nvl(l_count,0));
8952:
8953: IF ( NVL(l_count,0) > 0) THEN
8954: msc_util.msc_log('atp_snapshot_hook: Updating msc_plan_organizations..');
8955: UPDATE msc_plan_organizations mpo
8950:
8951: msc_util.msc_log('atp_snapshot_hook: l_count: '|| nvl(l_count,0));
8952:
8953: IF ( NVL(l_count,0) > 0) THEN
8954: msc_util.msc_log('atp_snapshot_hook: Updating msc_plan_organizations..');
8955: UPDATE msc_plan_organizations mpo
8956: SET so_lrn =(SELECT so_lrn
8957: FROM msc_instance_orgs mio
8958: WHERE mio.sr_instance_id=mpo.sr_instance_id
8960: )
8961: WHERE plan_id=p_plan_id;
8962: --RETURNING organization_id, sr_instance_id
8963: --BULK COLLECT INTO l_organization_id, l_sr_instance_id;
8964: msc_util.msc_log('atp_snapshot_hook: No. of Rows updated: '|| SQL%ROWCOUNT );
8965: END IF;
8966:
8967: --bug3663487 start
8968: /*
9001: commit;
9002:
9003: EXCEPTION
9004: WHEN others THEN
9005: msc_util.msc_log('Error in atp_snapshot_hook: ' || SQLCODE || '-' || SQLERRM);
9006: END atp_snapshot_hook;
9007:
9008: -- NGOEL 1/15/2004, API to delete CTO BOM and OSS data from ATP temp tables for standalone and post 24x7 plan run plan purging
9009: -- This API will be called by "Purge Plan" conc program.
9011: Procedure Delete_CTO_BOM_OSS(
9012: p_plan_id IN NUMBER)
9013: IS
9014: BEGIN
9015: msc_util.msc_log('Begin Delete_CTO_BOM_OSS for plan_id: ' || p_plan_id);
9016: msc_util.msc_log('Before Delete data for CTO BOM');
9017:
9018: DELETE msc_cto_bom
9019: WHERE nvl(plan_id, p_plan_id) = p_plan_id;
9012: p_plan_id IN NUMBER)
9013: IS
9014: BEGIN
9015: msc_util.msc_log('Begin Delete_CTO_BOM_OSS for plan_id: ' || p_plan_id);
9016: msc_util.msc_log('Before Delete data for CTO BOM');
9017:
9018: DELETE msc_cto_bom
9019: WHERE nvl(plan_id, p_plan_id) = p_plan_id;
9020:
9017:
9018: DELETE msc_cto_bom
9019: WHERE nvl(plan_id, p_plan_id) = p_plan_id;
9020:
9021: msc_util.msc_log('After Delete data for CTO BOM: ' || SQL%ROWCOUNT);
9022:
9023: msc_util.msc_log('Before Delete data for CTO OSS');
9024:
9025: DELETE msc_cto_sources
9019: WHERE nvl(plan_id, p_plan_id) = p_plan_id;
9020:
9021: msc_util.msc_log('After Delete data for CTO BOM: ' || SQL%ROWCOUNT);
9022:
9023: msc_util.msc_log('Before Delete data for CTO OSS');
9024:
9025: DELETE msc_cto_sources
9026: WHERE nvl(plan_id, p_plan_id) = p_plan_id;
9027:
9024:
9025: DELETE msc_cto_sources
9026: WHERE nvl(plan_id, p_plan_id) = p_plan_id;
9027:
9028: msc_util.msc_log('After Delete data for CTO OSS: ' || SQL%ROWCOUNT);
9029:
9030: commit;
9031:
9032: msc_util.msc_log('End Delete_CTO_BOM_OSS');
9028: msc_util.msc_log('After Delete data for CTO OSS: ' || SQL%ROWCOUNT);
9029:
9030: commit;
9031:
9032: msc_util.msc_log('End Delete_CTO_BOM_OSS');
9033: EXCEPTION
9034: WHEN others THEN
9035: msc_util.msc_log('Exception in Delete_CTO_BOM_OSS :' || SQLCODE || '-' || SQLERRM);
9036: END Delete_CTO_BOM_OSS;
9031:
9032: msc_util.msc_log('End Delete_CTO_BOM_OSS');
9033: EXCEPTION
9034: WHEN others THEN
9035: msc_util.msc_log('Exception in Delete_CTO_BOM_OSS :' || SQLCODE || '-' || SQLERRM);
9036: END Delete_CTO_BOM_OSS;
9037:
9038: END MSC_POST_PRO;