[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('MSC', '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('MSC', '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; --anurodh, 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: --anurodh, 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: --anurodh, 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: --anurodh, 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 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=>'MSC',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=>'MSC',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=>'MSC',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('MSC', '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('MSC', 'MSC_TEMP_ALLOC_DEM_' || to_char(l_plan_id), granularity => 'ALL');
6127: fnd_stats.gather_table_stats('MSC', 'MSC_TEMP_ALLOC_SUP_' || to_char(l_plan_id), granularity => 'ALL');
6128:
6125:
6126: fnd_stats.gather_table_stats('MSC', 'MSC_TEMP_ALLOC_DEM_' || to_char(l_plan_id), granularity => 'ALL');
6127: fnd_stats.gather_table_stats('MSC', '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,
6215: where plan_id = p_plan_id
6216: and origination_type in (30,6)
6217: and RESERVED_QUANTITY <> 0
6218: );
6219: msc_util.msc_log('inserted reservation rows in msc_alloc_demands :' || SQL%ROWCOUNT);
6220:
6221: BEGIN
6222: update msc_plans
6223: set summary_flag = 3
6226:
6227: RETCODE := G_SUCCESS;
6228: commit;
6229:
6230: msc_util.msc_log('End procedure post_plan_allocation');
6231:
6232: EXCEPTION
6233: WHEN OTHERS THEN
6234: msc_util.msc_log('Inside main exception of post_plan_allocation');
6230: msc_util.msc_log('End procedure post_plan_allocation');
6231:
6232: EXCEPTION
6233: WHEN OTHERS THEN
6234: msc_util.msc_log('Inside main exception of post_plan_allocation');
6235: msc_util.msc_log(sqlerrm);
6236: ERRBUF := sqlerrm;
6237:
6238: BEGIN
6231:
6232: EXCEPTION
6233: WHEN OTHERS THEN
6234: msc_util.msc_log('Inside main exception of post_plan_allocation');
6235: msc_util.msc_log(sqlerrm);
6236: ERRBUF := sqlerrm;
6237:
6238: BEGIN
6239: update msc_plans
6245: RETCODE := G_ERROR;
6246: IF (l_share_partition = 'Y') THEN
6247: ROLLBACK;
6248: ELSE
6249: msc_util.msc_log('Call procedure clean_temp_tables in exception');
6250:
6251: -- clean temp tables after exchanging partitions
6252: IF l_plan_id IS NOT NULL THEN
6253: clean_temp_tables(l_applsys_schema, l_plan_id, p_plan_id, NULL);
6252: IF l_plan_id IS NOT NULL THEN
6253: clean_temp_tables(l_applsys_schema, l_plan_id, p_plan_id, NULL);
6254: END IF;
6255:
6256: msc_util.msc_log('After procedure clean_temp_tables in exception');
6257: END IF;
6258: END post_plan_allocation;
6259:
6260: -- ngoel 5/7/2002, added new API to be called from planning process to launch concurrent program
6281: -- 24x7 ATP
6282: l_copy_plan_id NUMBER;
6283:
6284: BEGIN
6285: msc_util.msc_log('Begin procedure atp_post_plan_proc');
6286: msc_util.msc_log('plan : ' || p_plan_id);
6287: msc_util.msc_log('Allocation Mode : ' || p_alloc_mode);
6288: msc_util.msc_log('Summary Mode : ' || p_summary_mode);
6289:
6282: l_copy_plan_id NUMBER;
6283:
6284: BEGIN
6285: msc_util.msc_log('Begin procedure atp_post_plan_proc');
6286: msc_util.msc_log('plan : ' || p_plan_id);
6287: msc_util.msc_log('Allocation Mode : ' || p_alloc_mode);
6288: msc_util.msc_log('Summary Mode : ' || p_summary_mode);
6289:
6290: x_retcode := G_SUCCESS;
6283:
6284: BEGIN
6285: msc_util.msc_log('Begin procedure atp_post_plan_proc');
6286: msc_util.msc_log('plan : ' || p_plan_id);
6287: msc_util.msc_log('Allocation Mode : ' || p_alloc_mode);
6288: msc_util.msc_log('Summary Mode : ' || p_summary_mode);
6289:
6290: x_retcode := G_SUCCESS;
6291:
6284: BEGIN
6285: msc_util.msc_log('Begin procedure atp_post_plan_proc');
6286: msc_util.msc_log('plan : ' || p_plan_id);
6287: msc_util.msc_log('Allocation Mode : ' || p_alloc_mode);
6288: msc_util.msc_log('Summary Mode : ' || p_summary_mode);
6289:
6290: x_retcode := G_SUCCESS;
6291:
6292: l_inv_ctp := NVL(FND_PROFILE.value('INV_CTP'), 5);
6290: x_retcode := G_SUCCESS;
6291:
6292: l_inv_ctp := NVL(FND_PROFILE.value('INV_CTP'), 5);
6293:
6294: msc_util.msc_log('inv_ctp := ' || l_inv_ctp);
6295:
6296: /* time_phased_atp changes begin
6297: Always call atp post plan processing conc prog if PDS
6298: IF l_enable_summary_mode = 'Y' AND l_inv_ctp = 4 AND l_alloc_atp = 'N' THEN
6324: AND plans.data_completion_date is not null
6325: -- IO Perf:3693983: Don't Launch ATP Post Plan Processes for IO Plans
6326: AND plans.plan_type <> 4;
6327:
6328: msc_util.msc_log('Count for plan : ' || l_count);
6329:
6330: IF l_count > 0 THEN
6331: msc_util.msc_log('Before refreshing MSC_ATP_PLAN_SN');
6332: l_request_id := FND_REQUEST.SUBMIT_REQUEST(
6327:
6328: msc_util.msc_log('Count for plan : ' || l_count);
6329:
6330: IF l_count > 0 THEN
6331: msc_util.msc_log('Before refreshing MSC_ATP_PLAN_SN');
6332: l_request_id := FND_REQUEST.SUBMIT_REQUEST(
6333: 'MSC',
6334: 'MSCREFMV',
6335: NULL, -- description
6336: NULL, -- start time
6337: FALSE, -- sub request
6338: 'MSC_ATP_PLAN_SN',
6339: 724);
6340: msc_util.msc_log('Request id for refreshing snapshot := ' || l_request_id);
6341: msc_util.msc_log('After refreshing MSC_ATP_PLAN_SN');
6342: END IF; --IF l_count = 0 THEN
6343: END IF;
6344:
6337: FALSE, -- sub request
6338: 'MSC_ATP_PLAN_SN',
6339: 724);
6340: msc_util.msc_log('Request id for refreshing snapshot := ' || l_request_id);
6341: msc_util.msc_log('After refreshing MSC_ATP_PLAN_SN');
6342: END IF; --IF l_count = 0 THEN
6343: END IF;
6344:
6345: -- Bug 3491498, moved prior to call to refresh MSC_ATP_PLAN_SN
6355: AND plans.organization_id = desig.organization_id
6356: AND plans.plan_completion_date is not null
6357: AND plans.data_completion_date is not null;
6358:
6359: msc_util.msc_log('Count for plan : ' || l_count);
6360: msc_util.msc_log('l_submit_request : ' || l_submit_request);
6361: */
6362:
6363: -- 24x7 ATP
6356: AND plans.plan_completion_date is not null
6357: AND plans.data_completion_date is not null;
6358:
6359: msc_util.msc_log('Count for plan : ' || l_count);
6360: msc_util.msc_log('l_submit_request : ' || l_submit_request);
6361: */
6362:
6363: -- 24x7 ATP
6364: BEGIN
6375:
6376: if (l_copy_plan_id > 0) then
6377: -- plan copy exists . force the execution of other things.
6378: l_count := 1;
6379: msc_util.msc_log ('Plan determined to be a 24x7 plan');
6380: end if;
6381:
6382: IF l_count = 0 THEN
6383: UPDATE msc_plans
6388: RETURN;
6389: END IF;
6390:
6391: BEGIN
6392: msc_util.msc_log('before launching concurrent program');
6393: -- Bug 3292949
6394: UPDATE msc_plans
6395: SET summary_flag = 1
6396: WHERE plan_id = p_plan_id;
6393: -- Bug 3292949
6394: UPDATE msc_plans
6395: SET summary_flag = 1
6396: WHERE plan_id = p_plan_id;
6397: msc_util.msc_log('Reset summary flag back to default:1 before conc prg launch');
6398: -- End Bug 3292949
6399: l_request_id := FND_REQUEST.SUBMIT_REQUEST(
6400: 'MSC',
6401: 'MSC_ATP_PDS_SUMM',
6411: WHERE plan_id = p_plan_id;
6412:
6413: COMMIT;
6414:
6415: msc_util.msc_log('Request ID :' || l_request_id);
6416:
6417: EXCEPTION
6418: WHEN OTHERS THEN
6419: msc_util.msc_log ('Conc. program error : ' || sqlcode || ':' || sqlerrm);
6415: msc_util.msc_log('Request ID :' || l_request_id);
6416:
6417: EXCEPTION
6418: WHEN OTHERS THEN
6419: msc_util.msc_log ('Conc. program error : ' || sqlcode || ':' || sqlerrm);
6420: x_retcode := G_ERROR;
6421: x_errbuf := sqlerrm;
6422: END;
6423: END atp_post_plan_proc;
6446: -- Deleted Related Code.
6447:
6448: RETCODE := G_SUCCESS;
6449:
6450: msc_util.msc_log('********** MRP_ATP_Purge_Temp **********');
6451: msc_util.msc_log('p_hours: ' || p_hours );
6452:
6453: IF NVL(p_hours,0) > 0 THEN
6454:
6447:
6448: RETCODE := G_SUCCESS;
6449:
6450: msc_util.msc_log('********** MRP_ATP_Purge_Temp **********');
6451: msc_util.msc_log('p_hours: ' || p_hours );
6452:
6453: IF NVL(p_hours,0) > 0 THEN
6454:
6455: l_retain_date := sysdate - p_hours/24;
6453: IF NVL(p_hours,0) > 0 THEN
6454:
6455: l_retain_date := sysdate - p_hours/24;
6456:
6457: msc_util.msc_log('Delete records older than l_retain_date ' ||
6458: to_char(l_retain_date, 'DD:MM:YYYY hh24:mi:ss'));
6459: msc_util.msc_log('Now sysdate: ' ||
6460: to_char(sysdate, 'DD:MM:YYYY hh24:mi:ss'));
6461:
6455: l_retain_date := sysdate - p_hours/24;
6456:
6457: msc_util.msc_log('Delete records older than l_retain_date ' ||
6458: to_char(l_retain_date, 'DD:MM:YYYY hh24:mi:ss'));
6459: msc_util.msc_log('Now sysdate: ' ||
6460: to_char(sysdate, 'DD:MM:YYYY hh24:mi:ss'));
6461:
6462:
6463: DELETE FROM mrp_atp_schedule_temp
6462:
6463: DELETE FROM mrp_atp_schedule_temp
6464: WHERE last_update_date < l_retain_date;
6465:
6466: msc_util.msc_log('Records Deleted from mrp_atp_schedule_temp : ' ||
6467: SQL%ROWCOUNT);
6468:
6469: --3670695: issue commit so that rollback segment is freed
6470: commit;
6471:
6472: DELETE FROM mrp_atp_details_temp
6473: WHERE last_update_date < l_retain_date;
6474:
6475: msc_util.msc_log('Records Deleted from mrp_atp_details_temp : ' ||
6476: SQL%ROWCOUNT);
6477: --bug3940999
6478: DELETE FROM msc_atp_src_profile_temp
6479: WHERE last_update_date < l_retain_date;
6477: --bug3940999
6478: DELETE FROM msc_atp_src_profile_temp
6479: WHERE last_update_date < l_retain_date;
6480:
6481: msc_util.msc_log('Records Deleted from msc_atp_src_profile_temp : ' ||
6482: SQL%ROWCOUNT);
6483:
6484: ELSE
6485: msc_util.msc_log('completely purging temp tables');
6481: msc_util.msc_log('Records Deleted from msc_atp_src_profile_temp : ' ||
6482: SQL%ROWCOUNT);
6483:
6484: ELSE
6485: msc_util.msc_log('completely purging temp tables');
6486: --bug3545959 start
6487: SELECT a.oracle_username
6488: INTO l_mrp_schema
6489: FROM FND_ORACLE_USERID a,
6490: FND_PRODUCT_INSTALLATIONS b
6491: WHERE a.oracle_id = b.oracle_id
6492: AND b.application_id = 704;
6493:
6494: msc_util.msc_log('l_mrp_schema: ' || l_mrp_schema );
6495: --bug3545959 end
6496: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_mrp_schema ||'.mrp_atp_schedule_temp';
6497: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_mrp_schema ||'.mrp_atp_details_temp';
6498:
6503: FND_PRODUCT_INSTALLATIONS b
6504: WHERE a.oracle_id = b.oracle_id
6505: AND b.application_id = 724;
6506:
6507: msc_util.msc_log('l_msc_schema: ' || l_msc_schema );
6508:
6509: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_msc_schema ||'.msc_atp_src_profile_temp';
6510:
6511: END IF;
6512:
6513: commit;
6514: EXCEPTION
6515: WHEN others THEN
6516: msc_util.msc_log('Error while purging temp tables : ' ||
6517: sqlcode || ' : ' || sqlerrm);
6518: rollback;
6519: END ATP_Purge_MRP_Temp;
6520:
6545:
6546: -- ATPR4drp
6547: l_plan_type NUMBER;
6548: BEGIN
6549: msc_util.msc_log('************ LOAD_PLAN_SUMMARY_SD begin *************');
6550: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_plan_id - ' || p_plan_id);
6551: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_share_partition - ' || p_share_partition);
6552: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_optimized_plan - ' || p_optimized_plan);
6553: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_full_refresh - ' || p_full_refresh);
6546: -- ATPR4drp
6547: l_plan_type NUMBER;
6548: BEGIN
6549: msc_util.msc_log('************ LOAD_PLAN_SUMMARY_SD begin *************');
6550: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_plan_id - ' || p_plan_id);
6551: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_share_partition - ' || p_share_partition);
6552: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_optimized_plan - ' || p_optimized_plan);
6553: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_full_refresh - ' || p_full_refresh);
6554:
6547: l_plan_type NUMBER;
6548: BEGIN
6549: msc_util.msc_log('************ LOAD_PLAN_SUMMARY_SD begin *************');
6550: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_plan_id - ' || p_plan_id);
6551: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_share_partition - ' || p_share_partition);
6552: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_optimized_plan - ' || p_optimized_plan);
6553: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_full_refresh - ' || p_full_refresh);
6554:
6555: -- ATP4drp changes begin
6548: BEGIN
6549: msc_util.msc_log('************ LOAD_PLAN_SUMMARY_SD begin *************');
6550: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_plan_id - ' || p_plan_id);
6551: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_share_partition - ' || p_share_partition);
6552: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_optimized_plan - ' || p_optimized_plan);
6553: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_full_refresh - ' || p_full_refresh);
6554:
6555: -- ATP4drp changes begin
6556: -- print plan_type
6549: msc_util.msc_log('************ LOAD_PLAN_SUMMARY_SD begin *************');
6550: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_plan_id - ' || p_plan_id);
6551: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_share_partition - ' || p_share_partition);
6552: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_optimized_plan - ' || p_optimized_plan);
6553: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'p_full_refresh - ' || p_full_refresh);
6554:
6555: -- ATP4drp changes begin
6556: -- print plan_type
6557:
6554:
6555: -- ATP4drp changes begin
6556: -- print plan_type
6557:
6558: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Plan Type p_plan_type -> ' || p_plan_type);
6559: msc_sch_wb.atp_debug('----- ATP4drp Specific Debug Messages -----');
6560:
6561: -- END ATP4drp
6562:
6561: -- END ATP4drp
6562:
6563:
6564: IF p_full_refresh = 1 THEN
6565: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside full summation');
6566:
6567: -- first delete existing data. p_share_partition = 'N' data has already been deleted
6568: IF p_share_partition = 'Y' THEN
6569:
6566:
6567: -- first delete existing data. p_share_partition = 'N' data has already been deleted
6568: IF p_share_partition = 'Y' THEN
6569:
6570: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside shared part_partition');
6571: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'before deleteing data from the table');
6572:
6573: DELETE MSC_ATP_SUMMARY_SD where plan_id = p_plan_id;
6574: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After deleting data from the table');
6567: -- first delete existing data. p_share_partition = 'N' data has already been deleted
6568: IF p_share_partition = 'Y' THEN
6569:
6570: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside shared part_partition');
6571: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'before deleteing data from the table');
6572:
6573: DELETE MSC_ATP_SUMMARY_SD where plan_id = p_plan_id;
6574: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After deleting data from the table');
6575:
6570: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside shared part_partition');
6571: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'before deleteing data from the table');
6572:
6573: DELETE MSC_ATP_SUMMARY_SD where plan_id = p_plan_id;
6574: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After deleting data from the table');
6575:
6576: END IF; --- IF share_partition = 'Y'
6577:
6578: -- Now insert new data
6576: END IF; --- IF share_partition = 'Y'
6577:
6578: -- Now insert new data
6579: IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6580: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside demand priority allocated ATP');
6581: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6582:
6583: load_sd_full_alloc(p_plan_id, p_sys_date);
6584:
6577:
6578: -- Now insert new data
6579: IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6580: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside demand priority allocated ATP');
6581: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6582:
6583: load_sd_full_alloc(p_plan_id, p_sys_date);
6584:
6585: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
6581: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6582:
6583: load_sd_full_alloc(p_plan_id, p_sys_date);
6584:
6585: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
6586: ELSE -- IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6587: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Unallocated ATP');
6588: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6589:
6583: load_sd_full_alloc(p_plan_id, p_sys_date);
6584:
6585: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
6586: ELSE -- IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6587: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Unallocated ATP');
6588: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6589:
6590: IF nvl(p_optimized_plan, 2) <> 1 THEN
6591: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Unconstrained plan');
6584:
6585: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
6586: ELSE -- IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6587: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Unallocated ATP');
6588: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6589:
6590: IF nvl(p_optimized_plan, 2) <> 1 THEN
6591: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Unconstrained plan');
6592: IF p_time_phased_pf = 1 THEN
6587: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Unallocated ATP');
6588: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6589:
6590: IF nvl(p_optimized_plan, 2) <> 1 THEN
6591: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Unconstrained plan');
6592: IF p_time_phased_pf = 1 THEN
6593: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup exists.');
6594: load_sd_full_unalloc_unopt_pf(p_plan_id, p_sys_date);
6595: ELSE
6589:
6590: IF nvl(p_optimized_plan, 2) <> 1 THEN
6591: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Unconstrained plan');
6592: IF p_time_phased_pf = 1 THEN
6593: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup exists.');
6594: load_sd_full_unalloc_unopt_pf(p_plan_id, p_sys_date);
6595: ELSE
6596: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup does not exist.');
6597: load_sd_full_unalloc_unopt(p_plan_id, p_sys_date);
6592: IF p_time_phased_pf = 1 THEN
6593: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup exists.');
6594: load_sd_full_unalloc_unopt_pf(p_plan_id, p_sys_date);
6595: ELSE
6596: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup does not exist.');
6597: load_sd_full_unalloc_unopt(p_plan_id, p_sys_date);
6598: END IF;
6599: ELSE
6600: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Constrained plan');
6596: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup does not exist.');
6597: load_sd_full_unalloc_unopt(p_plan_id, p_sys_date);
6598: END IF;
6599: ELSE
6600: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Constrained plan');
6601: IF p_time_phased_pf = 1 THEN
6602: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup exists.');
6603: load_sd_full_unalloc_opt_pf(p_plan_id, p_sys_date);
6604: ELSE
6598: END IF;
6599: ELSE
6600: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Constrained plan');
6601: IF p_time_phased_pf = 1 THEN
6602: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup exists.');
6603: load_sd_full_unalloc_opt_pf(p_plan_id, p_sys_date);
6604: ELSE
6605: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup does not exist.');
6606: -- ATP4drp Call DRP specific summary
6601: IF p_time_phased_pf = 1 THEN
6602: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup exists.');
6603: load_sd_full_unalloc_opt_pf(p_plan_id, p_sys_date);
6604: ELSE
6605: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup does not exist.');
6606: -- ATP4drp Call DRP specific summary
6607: IF (p_plan_type = 5) THEN
6608: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Call FULL Summary for DRP plan.');
6609: msc_sch_wb.atp_debug('----- ATP4drp Specific Debug Messages -----');
6604: ELSE
6605: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Time phased pf setup does not exist.');
6606: -- ATP4drp Call DRP specific summary
6607: IF (p_plan_type = 5) THEN
6608: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Call FULL Summary for DRP plan.');
6609: msc_sch_wb.atp_debug('----- ATP4drp Specific Debug Messages -----');
6610: MSC_ATP_DRP.load_sd_full_drp(p_plan_id, p_sys_date);
6611: ELSE -- Call rest summary
6612: load_sd_full_unalloc_opt(p_plan_id, p_sys_date);
6614: -- End ATP4drp
6615: END IF;
6616: END IF;
6617:
6618: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
6619: END IF; -- IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6620:
6621: ELSE --- IF p_full_refresh = 1 THEN
6622: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside incremental summation');
6618: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
6619: END IF; -- IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6620:
6621: ELSE --- IF p_full_refresh = 1 THEN
6622: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside incremental summation');
6623:
6624: IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6625: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside demand priority allocated ATP');
6626: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6621: ELSE --- IF p_full_refresh = 1 THEN
6622: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside incremental summation');
6623:
6624: IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6625: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside demand priority allocated ATP');
6626: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6627:
6628: load_sd_net_alloc(p_plan_id, p_last_refresh_number, p_new_refresh_number, p_sys_date);
6629:
6622: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside incremental summation');
6623:
6624: IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6625: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside demand priority allocated ATP');
6626: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6627:
6628: load_sd_net_alloc(p_plan_id, p_last_refresh_number, p_new_refresh_number, p_sys_date);
6629:
6630: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
6626: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6627:
6628: load_sd_net_alloc(p_plan_id, p_last_refresh_number, p_new_refresh_number, p_sys_date);
6629:
6630: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
6631: ELSE -- IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6632: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside unallocated ATP');
6633: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6634:
6628: load_sd_net_alloc(p_plan_id, p_last_refresh_number, p_new_refresh_number, p_sys_date);
6629:
6630: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
6631: ELSE -- IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6632: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside unallocated ATP');
6633: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6634:
6635: -- ATP4drp Call DRP specific summary
6636: IF (p_plan_type = 5) THEN
6629:
6630: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
6631: ELSE -- IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6632: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Inside unallocated ATP');
6633: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6634:
6635: -- ATP4drp Call DRP specific summary
6636: IF (p_plan_type = 5) THEN
6637: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Call NET Summary for DRP plan.');
6633: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
6634:
6635: -- ATP4drp Call DRP specific summary
6636: IF (p_plan_type = 5) THEN
6637: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Call NET Summary for DRP plan.');
6638: MSC_ATP_DRP.load_sd_net_drp(p_plan_id, p_last_refresh_number, p_new_refresh_number, p_sys_date);
6639: ELSE -- Call rest summary
6640: load_sd_net_unalloc(p_plan_id, p_last_refresh_number, p_new_refresh_number, p_time_phased_pf, p_sys_date);
6641: END IF;
6640: load_sd_net_unalloc(p_plan_id, p_last_refresh_number, p_new_refresh_number, p_time_phased_pf, p_sys_date);
6641: END IF;
6642: -- ATP4drp
6643:
6644: msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
6645: END IF; -- IF G_ALLOC_ATP = 'Y' AND G_CLASS_HRCHY = 1 AND G_ALLOC_METHOD = 1 THEN
6646:
6647: END IF; --- IF p_full_refresh = 1 THEN
6648:
6656: IS
6657: l_user_id NUMBER;
6658: BEGIN
6659:
6660: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_UNOPT Begin ********');
6661:
6662: l_user_id := FND_GLOBAL.USER_ID;
6663:
6664: INSERT INTO MSC_ATP_SUMMARY_SD (
6781: )
6782: GROUP BY plan_id, inventory_item_id,organization_id, sr_instance_id,demand_class, sd_date,
6783: last_update_date, last_updated_by, creation_date, created_by );
6784:
6785: msc_util.msc_log('LOAD_SD_FULL_UNALLOC_UNOPT: ' || 'Records inserted : ' || SQL%ROWCOUNT);
6786: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_UNOPT End ********');
6787:
6788: END LOAD_SD_FULL_UNALLOC_UNOPT;
6789:
6782: GROUP BY plan_id, inventory_item_id,organization_id, sr_instance_id,demand_class, sd_date,
6783: last_update_date, last_updated_by, creation_date, created_by );
6784:
6785: msc_util.msc_log('LOAD_SD_FULL_UNALLOC_UNOPT: ' || 'Records inserted : ' || SQL%ROWCOUNT);
6786: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_UNOPT End ********');
6787:
6788: END LOAD_SD_FULL_UNALLOC_UNOPT;
6789:
6790:
6796: IS
6797: l_user_id NUMBER;
6798: BEGIN
6799:
6800: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_UNOPT_PF Begin ********');
6801:
6802: l_user_id := FND_GLOBAL.USER_ID;
6803:
6804: INSERT INTO MSC_ATP_SUMMARY_SD (
6957: )
6958: GROUP BY plan_id, inventory_item_id,organization_id, sr_instance_id,demand_class, sd_date,
6959: last_update_date, last_updated_by, creation_date, created_by );
6960:
6961: msc_util.msc_log('LOAD_SD_FULL_UNALLOC_UNOPT_PF: ' || 'Records inserted : ' || SQL%ROWCOUNT);
6962: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_UNOPT_PF End ********');
6963:
6964: END LOAD_SD_FULL_UNALLOC_UNOPT_PF;
6965:
6958: GROUP BY plan_id, inventory_item_id,organization_id, sr_instance_id,demand_class, sd_date,
6959: last_update_date, last_updated_by, creation_date, created_by );
6960:
6961: msc_util.msc_log('LOAD_SD_FULL_UNALLOC_UNOPT_PF: ' || 'Records inserted : ' || SQL%ROWCOUNT);
6962: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_UNOPT_PF End ********');
6963:
6964: END LOAD_SD_FULL_UNALLOC_UNOPT_PF;
6965:
6966:
6972: IS
6973: l_user_id NUMBER;
6974: BEGIN
6975:
6976: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_OPT Begin ********');
6977:
6978: l_user_id := FND_GLOBAL.USER_ID;
6979:
6980: INSERT INTO MSC_ATP_SUMMARY_SD (
7065: )
7066: GROUP BY plan_id, inventory_item_id,organization_id, sr_instance_id, demand_class, sd_date,
7067: last_update_date, last_updated_by, creation_date, created_by );
7068:
7069: msc_util.msc_log('LOAD_SD_FULL_UNALLOC_OPT: ' || 'Records inserted : ' || SQL%ROWCOUNT);
7070: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_OPT End ********');
7071:
7072: END LOAD_SD_FULL_UNALLOC_OPT;
7073:
7066: GROUP BY plan_id, inventory_item_id,organization_id, sr_instance_id, demand_class, sd_date,
7067: last_update_date, last_updated_by, creation_date, created_by );
7068:
7069: msc_util.msc_log('LOAD_SD_FULL_UNALLOC_OPT: ' || 'Records inserted : ' || SQL%ROWCOUNT);
7070: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_OPT End ********');
7071:
7072: END LOAD_SD_FULL_UNALLOC_OPT;
7073:
7074:
7080: IS
7081: l_user_id NUMBER;
7082: BEGIN
7083:
7084: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_OPT_PF Begin ********');
7085:
7086: l_user_id := FND_GLOBAL.USER_ID;
7087:
7088: INSERT INTO MSC_ATP_SUMMARY_SD (
7213: )
7214: GROUP BY plan_id, inventory_item_id,organization_id, sr_instance_id, demand_class, sd_date,
7215: last_update_date, last_updated_by, creation_date, created_by );
7216:
7217: msc_util.msc_log('LOAD_SD_FULL_UNALLOC_OPT_PF: ' || 'Records inserted : ' || SQL%ROWCOUNT);
7218: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_OPT_PF End ********');
7219:
7220: END LOAD_SD_FULL_UNALLOC_OPT_PF;
7221:
7214: GROUP BY plan_id, inventory_item_id,organization_id, sr_instance_id, demand_class, sd_date,
7215: last_update_date, last_updated_by, creation_date, created_by );
7216:
7217: msc_util.msc_log('LOAD_SD_FULL_UNALLOC_OPT_PF: ' || 'Records inserted : ' || SQL%ROWCOUNT);
7218: msc_util.msc_log('******** LOAD_SD_FULL_UNALLOC_OPT_PF End ********');
7219:
7220: END LOAD_SD_FULL_UNALLOC_OPT_PF;
7221:
7222:
7232: IS
7233: l_user_id number;
7234: BEGIN
7235:
7236: msc_util.msc_log('******** LOAD_SD_FULL_ALLOC Begin ********');
7237:
7238: l_user_id := FND_GLOBAL.USER_ID;
7239:
7240: INSERT INTO MSC_ATP_SUMMARY_SD (
7299: GROUP BY plan_id, inventory_item_id, organization_id, sr_instance_id,demand_class, sd_date,
7300: last_update_date, last_updated_by, creation_date, created_by
7301: HAVING sum(SD_QTY) <> 0);
7302:
7303: msc_util.msc_log('LOAD_SD_FULL_ALLOC: ' || 'Records inserted : ' || SQL%ROWCOUNT);
7304: msc_util.msc_log('******** LOAD_SD_FULL_ALLOC End ********');
7305:
7306: END LOAD_SD_FULL_ALLOC;
7307:
7300: last_update_date, last_updated_by, creation_date, created_by
7301: HAVING sum(SD_QTY) <> 0);
7302:
7303: msc_util.msc_log('LOAD_SD_FULL_ALLOC: ' || 'Records inserted : ' || SQL%ROWCOUNT);
7304: msc_util.msc_log('******** LOAD_SD_FULL_ALLOC End ********');
7305:
7306: END LOAD_SD_FULL_ALLOC;
7307:
7308:
7467: GROUP BY inventory_item_id, organization_id, sr_instance_id, sd_date;
7468:
7469: BEGIN
7470:
7471: msc_util.msc_log('******** LOAD_SD_NET_UNALLOC Begin ********');
7472: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_last_refresh_number - ' || p_last_refresh_number);
7473: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_new_refresh_number - ' || p_new_refresh_number);
7474: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_time_phased_pf - ' || p_time_phased_pf);
7475:
7468:
7469: BEGIN
7470:
7471: msc_util.msc_log('******** LOAD_SD_NET_UNALLOC Begin ********');
7472: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_last_refresh_number - ' || p_last_refresh_number);
7473: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_new_refresh_number - ' || p_new_refresh_number);
7474: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_time_phased_pf - ' || p_time_phased_pf);
7475:
7476: l_user_id := FND_GLOBAL.USER_ID;
7469: BEGIN
7470:
7471: msc_util.msc_log('******** LOAD_SD_NET_UNALLOC Begin ********');
7472: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_last_refresh_number - ' || p_last_refresh_number);
7473: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_new_refresh_number - ' || p_new_refresh_number);
7474: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_time_phased_pf - ' || p_time_phased_pf);
7475:
7476: l_user_id := FND_GLOBAL.USER_ID;
7477:
7470:
7471: msc_util.msc_log('******** LOAD_SD_NET_UNALLOC Begin ********');
7472: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_last_refresh_number - ' || p_last_refresh_number);
7473: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_new_refresh_number - ' || p_new_refresh_number);
7474: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'p_time_phased_pf - ' || p_time_phased_pf);
7475:
7476: l_user_id := FND_GLOBAL.USER_ID;
7477:
7478: IF p_time_phased_pf = 2 THEN
7494: END IF;
7495:
7496: IF l_inventory_item_id_tab IS NOT NULL AND l_inventory_item_id_tab.COUNT > 0 THEN
7497:
7498: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'l_inventory_item_id_tab.COUNT := ' || l_inventory_item_id_tab.COUNT);
7499:
7500: forall j IN l_inventory_item_id_tab.first.. l_inventory_item_id_tab.last
7501: UPDATE MSC_ATP_SUMMARY_SD
7502: SET sd_qty = sd_qty + l_sd_quantity_tab(j),
7507: AND inventory_item_id = l_inventory_item_id_tab(j)
7508: AND organization_id = l_organization_id_tab(j)
7509: AND sd_date = l_sd_date_tab(j);
7510:
7511: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'After FORALL UPDATE');
7512:
7513: FOR j IN l_inventory_item_id_tab.first.. l_inventory_item_id_tab.last LOOP
7514: IF SQL%BULK_ROWCOUNT(j) = 0 THEN
7515: l_ins_sr_instance_id_tab.EXTEND;
7527: END LOOP;
7528:
7529: IF l_ins_inventory_item_id_tab IS NOT NULL AND l_ins_inventory_item_id_tab.COUNT > 0 THEN
7530:
7531: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'l_ins_inventory_item_id_tab.COUNT := ' || l_ins_inventory_item_id_tab.COUNT);
7532:
7533: forall j IN l_ins_inventory_item_id_tab.first.. l_ins_inventory_item_id_tab.last
7534: INSERT INTO MSC_ATP_SUMMARY_SD (
7535: plan_id,
7554: l_user_id,
7555: p_sys_date,
7556: l_user_id);
7557:
7558: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'After FORALL INSERT');
7559:
7560: ELSE
7561: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'No records to be inserted');
7562: END IF;
7557:
7558: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'After FORALL INSERT');
7559:
7560: ELSE
7561: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'No records to be inserted');
7562: END IF;
7563: ELSE
7564: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'No records fetched in the net cursor');
7565: END IF;
7560: ELSE
7561: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'No records to be inserted');
7562: END IF;
7563: ELSE
7564: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'No records fetched in the net cursor');
7565: END IF;
7566:
7567: msc_util.msc_log('******** LOAD_SD_NET_UNALLOC End ********');
7568:
7563: ELSE
7564: msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'No records fetched in the net cursor');
7565: END IF;
7566:
7567: msc_util.msc_log('******** LOAD_SD_NET_UNALLOC End ********');
7568:
7569: END LOAD_SD_NET_UNALLOC;
7570:
7571:
7631: GROUP BY inventory_item_id, organization_id, sr_instance_id, demand_class, sd_date;
7632:
7633: BEGIN
7634:
7635: msc_util.msc_log('******** LOAD_SD_NET_ALLOC Begin ********');
7636:
7637: l_user_id := FND_GLOBAL.USER_ID;
7638:
7639: OPEN c_net_sd(p_plan_id, p_last_refresh_number, p_new_refresh_number);
7646: CLOSE c_net_sd;
7647:
7648: IF l_inventory_item_id_tab IS NOT NULL AND l_inventory_item_id_tab.COUNT > 0 THEN
7649:
7650: msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'l_inventory_item_id_tab.COUNT := ' || l_inventory_item_id_tab.COUNT);
7651:
7652: forall j IN l_inventory_item_id_tab.first.. l_inventory_item_id_tab.last
7653: UPDATE MSC_ATP_SUMMARY_SD
7654: SET sd_qty = sd_qty + l_sd_quantity_tab(j),
7660: AND organization_id = l_organization_id_tab(j)
7661: AND sd_date = l_sd_date_tab(j)
7662: AND demand_class = l_demand_class_tab(j);
7663:
7664: msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'After FORALL UPDATE');
7665:
7666: FOR j IN l_inventory_item_id_tab.first.. l_inventory_item_id_tab.last LOOP
7667: IF SQL%BULK_ROWCOUNT(j) = 0 THEN
7668: l_ins_sr_instance_id_tab.EXTEND;
7682: END LOOP;
7683:
7684: IF l_ins_inventory_item_id_tab IS NOT NULL AND l_ins_inventory_item_id_tab.COUNT > 0 THEN
7685:
7686: msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'l_ins_inventory_item_id_tab.COUNT := ' || l_ins_inventory_item_id_tab.COUNT);
7687:
7688: forall j IN l_ins_inventory_item_id_tab.first.. l_ins_inventory_item_id_tab.last
7689: INSERT INTO MSC_ATP_SUMMARY_SD (
7690: plan_id,
7709: l_user_id,
7710: p_sys_date,
7711: l_user_id);
7712:
7713: msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'After FORALL INSERT');
7714:
7715: ELSE
7716: msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'No records to be inserted');
7717: END IF;
7712:
7713: msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'After FORALL INSERT');
7714:
7715: ELSE
7716: msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'No records to be inserted');
7717: END IF;
7718: ELSE
7719: msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'No records fetched in the net cursor');
7720: END IF;
7715: ELSE
7716: msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'No records to be inserted');
7717: END IF;
7718: ELSE
7719: msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'No records fetched in the net cursor');
7720: END IF;
7721:
7722: msc_util.msc_log('******** LOAD_SD_NET_ALLOC End ********');
7723:
7718: ELSE
7719: msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'No records fetched in the net cursor');
7720: END IF;
7721:
7722: msc_util.msc_log('******** LOAD_SD_NET_ALLOC End ********');
7723:
7724: END LOAD_SD_NET_ALLOC;
7725:
7726:
7738: l_user_id number;
7739: -- l_org_code VARCHAR2(7); -- Bug 3912422
7740: BEGIN
7741:
7742: msc_util.msc_log('******** LOAD_SUP_DATA_FULL Begin ********');
7743:
7744: SELECT trunc(p.plan_start_date),
7745: p.sr_instance_id,
7746: p.organization_id,
7757: AND p.organization_id = tp.sr_tp_id
7758: AND p.sr_instance_id = tp.sr_instance_id
7759: AND tp.partner_type = 3;
7760:
7761: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_plan_start_date = ' || l_plan_start_date);
7762: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_instance_id = ' || l_instance_id);
7763: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_org_id = ' || l_org_id);
7764: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_calendar_code = ' || l_calendar_code);
7765:
7758: AND p.sr_instance_id = tp.sr_instance_id
7759: AND tp.partner_type = 3;
7760:
7761: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_plan_start_date = ' || l_plan_start_date);
7762: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_instance_id = ' || l_instance_id);
7763: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_org_id = ' || l_org_id);
7764: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_calendar_code = ' || l_calendar_code);
7765:
7766: l_user_id := FND_GLOBAL.USER_ID;
7759: AND tp.partner_type = 3;
7760:
7761: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_plan_start_date = ' || l_plan_start_date);
7762: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_instance_id = ' || l_instance_id);
7763: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_org_id = ' || l_org_id);
7764: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_calendar_code = ' || l_calendar_code);
7765:
7766: l_user_id := FND_GLOBAL.USER_ID;
7767:
7760:
7761: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_plan_start_date = ' || l_plan_start_date);
7762: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_instance_id = ' || l_instance_id);
7763: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_org_id = ' || l_org_id);
7764: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'l_calendar_code = ' || l_calendar_code);
7765:
7766: l_user_id := FND_GLOBAL.USER_ID;
7767:
7768: INSERT INTO MSC_ATP_SUMMARY_SUP(
7922: group by plan_id,inventory_item_id, supplier_id, supplier_site_id, sr_instance_id,
7923: sd_date, demand_class, last_update_date, last_updated_by, creation_date, created_by
7924: );
7925:
7926: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'Records inserted : ' || SQL%ROWCOUNT);
7927: msc_util.msc_log('******** LOAD_SUP_DATA_FULL End ********');
7928:
7929: END LOAD_SUP_DATA_FULL;
7930:
7923: sd_date, demand_class, last_update_date, last_updated_by, creation_date, created_by
7924: );
7925:
7926: msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'Records inserted : ' || SQL%ROWCOUNT);
7927: msc_util.msc_log('******** LOAD_SUP_DATA_FULL End ********');
7928:
7929: END LOAD_SUP_DATA_FULL;
7930:
7931:
8008:
8009:
8010: BEGIN
8011:
8012: msc_util.msc_log('******** LOAD_SUP_DATA_NET Begin ********');
8013:
8014: l_user_id := FND_GLOBAL.USER_ID;
8015:
8016: OPEN c_net_sup(p_plan_id, p_last_refresh_number, p_new_refresh_number);
8023: CLOSE c_net_sup;
8024:
8025: IF l_inventory_item_id_tab IS NOT NULL AND l_inventory_item_id_tab.COUNT > 0 THEN
8026:
8027: msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'l_inventory_item_id_tab.COUNT := ' || l_inventory_item_id_tab.COUNT);
8028:
8029: forall j IN l_inventory_item_id_tab.first.. l_inventory_item_id_tab.last
8030: UPDATE MSC_ATP_SUMMARY_SUP
8031: SET sd_qty = sd_qty + l_sd_quantity_tab(j),
8037: AND supplier_id = l_supplier_id_tab(j)
8038: AND supplier_site_id = l_supplier_site_id_tab(j)
8039: AND sd_date = l_sd_date_tab(j);
8040:
8041: msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'After FORALL UPDATE');
8042:
8043: FOR j IN l_inventory_item_id_tab.first.. l_inventory_item_id_tab.last LOOP
8044: IF SQL%BULK_ROWCOUNT(j) = 0 THEN
8045: l_ins_sr_instance_id_tab.EXTEND;
8059: END LOOP;
8060:
8061: IF l_ins_inventory_item_id_tab IS NOT NULL AND l_ins_inventory_item_id_tab.COUNT > 0 THEN
8062:
8063: msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'l_ins_inventory_item_id_tab.COUNT := ' || l_ins_inventory_item_id_tab.COUNT);
8064:
8065: forall j IN l_ins_inventory_item_id_tab.first.. l_ins_inventory_item_id_tab.last
8066: INSERT INTO MSC_ATP_SUMMARY_SUP (
8067: plan_id,
8088: l_user_id,
8089: p_sys_date,
8090: l_user_id);
8091:
8092: msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'After FORALL INSERT');
8093:
8094: ELSE
8095: msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'No records to be inserted');
8096: END IF;
8091:
8092: msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'After FORALL INSERT');
8093:
8094: ELSE
8095: msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'No records to be inserted');
8096: END IF;
8097: ELSE
8098: msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'No records fetched in the net cursor');
8099: END IF;
8094: ELSE
8095: msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'No records to be inserted');
8096: END IF;
8097: ELSE
8098: msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'No records fetched in the net cursor');
8099: END IF;
8100:
8101: msc_util.msc_log('******** LOAD_SUP_DATA_NET End ********');
8102:
8097: ELSE
8098: msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'No records fetched in the net cursor');
8099: END IF;
8100:
8101: msc_util.msc_log('******** LOAD_SUP_DATA_NET End ********');
8102:
8103: END LOAD_SUP_DATA_NET;
8104:
8105:
8109: IS
8110: l_user_id number;
8111: BEGIN
8112:
8113: msc_util.msc_log('******** LOAD_RES_FULL_UNOPT_BATCH Begin ********');
8114:
8115: l_user_id := FND_GLOBAL.USER_ID;
8116:
8117: -- summary enhancement - made changes to make it consistent with regular SQLs in MSCRATPB
8248: ) group by plan_id, department_id, resource_id, organization_id, sr_instance_id, sd_date,
8249: last_update_date,last_updated_by, creation_date, created_by
8250: );
8251:
8252: msc_util.msc_log('LOAD_RES_FULL_UNOPT_BATCH: ' || 'Records inserted : ' || SQL%ROWCOUNT);
8253: msc_util.msc_log('******** LOAD_RES_FULL_UNOPT_BATCH End ********');
8254:
8255: END LOAD_RES_FULL_UNOPT_BATCH;
8256:
8249: last_update_date,last_updated_by, creation_date, created_by
8250: );
8251:
8252: msc_util.msc_log('LOAD_RES_FULL_UNOPT_BATCH: ' || 'Records inserted : ' || SQL%ROWCOUNT);
8253: msc_util.msc_log('******** LOAD_RES_FULL_UNOPT_BATCH End ********');
8254:
8255: END LOAD_RES_FULL_UNOPT_BATCH;
8256:
8257:
8261: IS
8262: l_user_id number;
8263: BEGIN
8264:
8265: msc_util.msc_log('******** LOAD_RES_FULL_OPT_BATCH Begin ********');
8266:
8267: l_user_id := FND_GLOBAL.USER_ID;
8268:
8269: -- summary enhancement - made changes to make it consistent with regular SQLs in MSCRATPB
8392: group by plan_id, department_id, resource_id, organization_id, sr_instance_id, sd_date,
8393: last_update_date,last_updated_by, creation_date, created_by
8394: );
8395:
8396: msc_util.msc_log('LOAD_RES_FULL_OPT_BATCH: ' || 'Records inserted : ' || SQL%ROWCOUNT);
8397: msc_util.msc_log('******** LOAD_RES_FULL_OPT_BATCH End ********');
8398:
8399: END LOAD_RES_FULL_OPT_BATCH;
8400:
8393: last_update_date,last_updated_by, creation_date, created_by
8394: );
8395:
8396: msc_util.msc_log('LOAD_RES_FULL_OPT_BATCH: ' || 'Records inserted : ' || SQL%ROWCOUNT);
8397: msc_util.msc_log('******** LOAD_RES_FULL_OPT_BATCH End ********');
8398:
8399: END LOAD_RES_FULL_OPT_BATCH;
8400:
8401:
8405: IS
8406: l_user_id number;
8407: BEGIN
8408:
8409: msc_util.msc_log('******** LOAD_RES_FULL_UNOPT_NOBATCH Begin ********');
8410:
8411: l_user_id := FND_GLOBAL.USER_ID;
8412:
8413: -- summary enhancement - made changes to make it consistent with regular SQLs in MSCRATPB
8509: group by plan_id, department_id, resource_id, organization_id, sr_instance_id, sd_date,
8510: last_update_date,last_updated_by, creation_date, created_by
8511: );
8512:
8513: msc_util.msc_log('LOAD_RES_FULL_UNOPT_NOBATCH: ' || 'Records inserted : ' || SQL%ROWCOUNT);
8514: msc_util.msc_log('******** LOAD_RES_FULL_UNOPT_NOBATCH End ********');
8515:
8516: END LOAD_RES_FULL_UNOPT_NOBATCH;
8517:
8510: last_update_date,last_updated_by, creation_date, created_by
8511: );
8512:
8513: msc_util.msc_log('LOAD_RES_FULL_UNOPT_NOBATCH: ' || 'Records inserted : ' || SQL%ROWCOUNT);
8514: msc_util.msc_log('******** LOAD_RES_FULL_UNOPT_NOBATCH End ********');
8515:
8516: END LOAD_RES_FULL_UNOPT_NOBATCH;
8517:
8518:
8522: IS
8523: l_user_id number;
8524: BEGIN
8525:
8526: msc_util.msc_log('******** LOAD_RES_FULL_OPT_NOBATCH Begin ********');
8527:
8528: l_user_id := FND_GLOBAL.USER_ID;
8529:
8530: -- summary enhancement - made changes to make it consistent with regular SQLs in MSCRATPB
8614: group by plan_id, department_id, resource_id, organization_id, sr_instance_id, sd_date,
8615: last_update_date,last_updated_by, creation_date, created_by
8616: );
8617:
8618: msc_util.msc_log('LOAD_RES_FULL_OPT_NOBATCH: ' || 'Records inserted : ' || SQL%ROWCOUNT);
8619: msc_util.msc_log('******** LOAD_RES_FULL_OPT_NOBATCH End ********');
8620:
8621: END LOAD_RES_FULL_OPT_NOBATCH;
8622:
8615: last_update_date,last_updated_by, creation_date, created_by
8616: );
8617:
8618: msc_util.msc_log('LOAD_RES_FULL_OPT_NOBATCH: ' || 'Records inserted : ' || SQL%ROWCOUNT);
8619: msc_util.msc_log('******** LOAD_RES_FULL_OPT_NOBATCH End ********');
8620:
8621: END LOAD_RES_FULL_OPT_NOBATCH;
8622:
8623:
8668: -- Bug 3348095 Only ATP created records, so use end_date.
8669:
8670: BEGIN
8671:
8672: msc_util.msc_log('******** LOAD_RES_DATA_NET Begin ********');
8673:
8674: l_user_id := FND_GLOBAL.USER_ID;
8675:
8676: OPEN c_net_res(p_plan_id, p_last_refresh_number, p_new_refresh_number);
8683: CLOSE c_net_res;
8684:
8685: IF l_resource_id_tab IS NOT NULL AND l_resource_id_tab.COUNT > 0 THEN
8686:
8687: msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'l_resource_id_tab.COUNT := ' || l_resource_id_tab.COUNT);
8688:
8689: forall j IN l_resource_id_tab.first.. l_resource_id_tab.last
8690: UPDATE MSC_ATP_SUMMARY_RES
8691: SET sd_qty = sd_qty + l_sd_quantity_tab(j),
8697: AND resource_id = l_resource_id_tab(j)
8698: AND department_id = l_department_id_tab(j)
8699: AND sd_date = l_sd_date_tab(j);
8700:
8701: msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'After FORALL UPDATE');
8702:
8703: FOR j IN l_resource_id_tab.first.. l_resource_id_tab.last LOOP
8704: IF SQL%BULK_ROWCOUNT(j) = 0 THEN
8705: l_ins_department_id_tab.EXTEND;
8719: END LOOP;
8720:
8721: IF l_ins_resource_id_tab IS NOT NULL AND l_ins_resource_id_tab.COUNT > 0 THEN
8722:
8723: msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'l_ins_resource_id_tab.COUNT := ' || l_ins_resource_id_tab.COUNT);
8724:
8725: forall j IN l_ins_resource_id_tab.first.. l_ins_resource_id_tab.last
8726: INSERT INTO MSC_ATP_SUMMARY_RES (
8727: plan_id,
8746: l_user_id,
8747: p_sys_date,
8748: l_user_id);
8749:
8750: msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'After FORALL INSERT');
8751:
8752: ELSE
8753: msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'No records to be inserted');
8754: END IF;
8749:
8750: msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'After FORALL INSERT');
8751:
8752: ELSE
8753: msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'No records to be inserted');
8754: END IF;
8755: ELSE
8756: msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'No records fetched in the net cursor');
8757: END IF;
8752: ELSE
8753: msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'No records to be inserted');
8754: END IF;
8755: ELSE
8756: msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'No records fetched in the net cursor');
8757: END IF;
8758:
8759: msc_util.msc_log('******** LOAD_RES_DATA_NET End ********');
8760:
8755: ELSE
8756: msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'No records fetched in the net cursor');
8757: END IF;
8758:
8759: msc_util.msc_log('******** LOAD_RES_DATA_NET End ********');
8760:
8761: END LOAD_RES_DATA_NET;
8762:
8763:
8766: IS
8767: l_partition_name varchar2(30);
8768: l_sql_stmt varchar2(300);
8769: BEGIN
8770: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'p_plan_id - ' || p_plan_id);
8771: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'p_applsys_schema - ' || p_applsys_schema);
8772:
8773: ------------------------------------------------
8774:
8767: l_partition_name varchar2(30);
8768: l_sql_stmt varchar2(300);
8769: BEGIN
8770: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'p_plan_id - ' || p_plan_id);
8771: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'p_applsys_schema - ' || p_applsys_schema);
8772:
8773: ------------------------------------------------
8774:
8775: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'truncate partition for sd');
8771: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'p_applsys_schema - ' || p_applsys_schema);
8772:
8773: ------------------------------------------------
8774:
8775: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'truncate partition for sd');
8776: l_partition_name := 'ATP_SUMMARY_SD_' || to_char(p_plan_id);
8777: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'Partition name : ' || l_partition_name);
8778: l_sql_stmt := 'ALTER TABLE MSC_ATP_SUMMARY_SD TRUNCATE PARTITION ' ||
8779: l_partition_name || ' DROP STORAGE';
8773: ------------------------------------------------
8774:
8775: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'truncate partition for sd');
8776: l_partition_name := 'ATP_SUMMARY_SD_' || to_char(p_plan_id);
8777: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'Partition name : ' || l_partition_name);
8778: l_sql_stmt := 'ALTER TABLE MSC_ATP_SUMMARY_SD TRUNCATE PARTITION ' ||
8779: l_partition_name || ' DROP STORAGE';
8780:
8781: BEGIN
8778: l_sql_stmt := 'ALTER TABLE MSC_ATP_SUMMARY_SD TRUNCATE PARTITION ' ||
8779: l_partition_name || ' DROP STORAGE';
8780:
8781: BEGIN
8782: msc_util.msc_log('Before alter table MSC_ATP_SUMMARY_SD');
8783: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
8784: APPLICATION_SHORT_NAME => 'MSC',
8785: STATEMENT_TYPE => ad_ddl.alter_table,
8786: STATEMENT => l_sql_stmt,
8785: STATEMENT_TYPE => ad_ddl.alter_table,
8786: STATEMENT => l_sql_stmt,
8787: OBJECT_NAME => 'MSC_ATP_SUMMARY_SD');
8788: END;
8789: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'After truncating SD partition');
8790:
8791: ------------------------------------------------
8792:
8793: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'truncate partition for sup');
8789: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'After truncating SD partition');
8790:
8791: ------------------------------------------------
8792:
8793: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'truncate partition for sup');
8794: l_partition_name := 'ATP_SUMMARY_SUP_' || to_char(p_plan_id);
8795: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'Partition name : ' || l_partition_name);
8796: l_sql_stmt := 'ALTER TABLE MSC_ATP_SUMMARY_SUP TRUNCATE PARTITION ' ||
8797: l_partition_name || ' DROP STORAGE';
8791: ------------------------------------------------
8792:
8793: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'truncate partition for sup');
8794: l_partition_name := 'ATP_SUMMARY_SUP_' || to_char(p_plan_id);
8795: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'Partition name : ' || l_partition_name);
8796: l_sql_stmt := 'ALTER TABLE MSC_ATP_SUMMARY_SUP TRUNCATE PARTITION ' ||
8797: l_partition_name || ' DROP STORAGE';
8798:
8799: BEGIN
8796: l_sql_stmt := 'ALTER TABLE MSC_ATP_SUMMARY_SUP TRUNCATE PARTITION ' ||
8797: l_partition_name || ' DROP STORAGE';
8798:
8799: BEGIN
8800: msc_util.msc_log('Before alter table MSC_ATP_SUMMARY_SUP');
8801: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
8802: APPLICATION_SHORT_NAME => 'MSC',
8803: STATEMENT_TYPE => ad_ddl.alter_table,
8804: STATEMENT => l_sql_stmt,
8803: STATEMENT_TYPE => ad_ddl.alter_table,
8804: STATEMENT => l_sql_stmt,
8805: OBJECT_NAME => 'MSC_ATP_SUMMARY_SUP');
8806: END;
8807: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'After truncating sup partition');
8808:
8809: ------------------------------------------------
8810:
8811: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'truncate partition for res');
8807: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'After truncating sup partition');
8808:
8809: ------------------------------------------------
8810:
8811: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'truncate partition for res');
8812: l_partition_name := 'ATP_SUMMARY_RES_' || to_char(p_plan_id);
8813: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'Partition name : ' || l_partition_name);
8814: l_sql_stmt := 'ALTER TABLE MSC_ATP_SUMMARY_RES TRUNCATE PARTITION ' ||
8815: l_partition_name || ' DROP STORAGE';
8809: ------------------------------------------------
8810:
8811: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'truncate partition for res');
8812: l_partition_name := 'ATP_SUMMARY_RES_' || to_char(p_plan_id);
8813: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'Partition name : ' || l_partition_name);
8814: l_sql_stmt := 'ALTER TABLE MSC_ATP_SUMMARY_RES TRUNCATE PARTITION ' ||
8815: l_partition_name || ' DROP STORAGE';
8816:
8817: BEGIN
8814: l_sql_stmt := 'ALTER TABLE MSC_ATP_SUMMARY_RES TRUNCATE PARTITION ' ||
8815: l_partition_name || ' DROP STORAGE';
8816:
8817: BEGIN
8818: msc_util.msc_log('Before alter table MSC_ATP_SUMMARY_RES');
8819: ad_ddl.do_ddl(APPLSYS_SCHEMA => p_applsys_schema,
8820: APPLICATION_SHORT_NAME => 'MSC',
8821: STATEMENT_TYPE => ad_ddl.alter_table,
8822: STATEMENT => l_sql_stmt,
8821: STATEMENT_TYPE => ad_ddl.alter_table,
8822: STATEMENT => l_sql_stmt,
8823: OBJECT_NAME => 'MSC_ATP_SUMMARY_RES');
8824: END;
8825: msc_util.msc_log('Truncate_Summ_Plan_Partition: ' || 'After truncating res partition');
8826:
8827: ------------------------------------------------
8828:
8829: END Truncate_Summ_Plan_Partition;
8835: l_partition_name varchar2(30);
8836: BEGIN
8837:
8838: ------------------------------------------------
8839: msc_util.msc_log('Gather_Summ_Plan_Stats: ' || 'Gather Table Stats for S/D Tables');
8840: IF p_share_partition = 'Y' THEN
8841: l_partition_name := 'ATP_SUMMARY_SD_999999';
8842: ELSE
8843: l_partition_name := 'ATP_SUMMARY_SD_' || to_char(p_plan_id);
8847: tabname=>'MSC_ATP_SUMMARY_SD',
8848: partname=>l_partition_name,
8849: granularity=>'PARTITION',
8850: percent =>10);
8851: msc_util.msc_log('Gather_Summ_Plan_Stats: ' || 'After gathering stats for S/D tables');
8852: ------------------------------------------------
8853: msc_util.msc_log('Gather_Summ_Plan_Stats: ' || 'Gather Table Stats for Sup Tables');
8854: IF p_share_partition = 'Y' THEN
8855: l_partition_name := 'ATP_SUMMARY_SUP_999999';
8849: granularity=>'PARTITION',
8850: percent =>10);
8851: msc_util.msc_log('Gather_Summ_Plan_Stats: ' || 'After gathering stats for S/D tables');
8852: ------------------------------------------------
8853: msc_util.msc_log('Gather_Summ_Plan_Stats: ' || 'Gather Table Stats for Sup Tables');
8854: IF p_share_partition = 'Y' THEN
8855: l_partition_name := 'ATP_SUMMARY_SUP_999999';
8856: ELSE
8857: l_partition_name := 'ATP_SUMMARY_SUP_' || to_char(p_plan_id);
8861: tabname=>'MSC_ATP_SUMMARY_SUP',
8862: partname=>l_partition_name,
8863: granularity=>'PARTITION',
8864: percent =>10);
8865: msc_util.msc_log('Gather_Summ_Plan_Stats: ' || 'After gathering stats for Sup tables');
8866: ------------------------------------------------
8867: msc_util.msc_log('Gather_Summ_Plan_Stats: ' || 'Gather Table Stats for Res Tables');
8868: IF p_share_partition = 'Y' THEN
8869: l_partition_name := 'ATP_SUMMARY_RES_999999';
8863: granularity=>'PARTITION',
8864: percent =>10);
8865: msc_util.msc_log('Gather_Summ_Plan_Stats: ' || 'After gathering stats for Sup tables');
8866: ------------------------------------------------
8867: msc_util.msc_log('Gather_Summ_Plan_Stats: ' || 'Gather Table Stats for Res Tables');
8868: IF p_share_partition = 'Y' THEN
8869: l_partition_name := 'ATP_SUMMARY_RES_999999';
8870: ELSE
8871: l_partition_name := 'ATP_SUMMARY_RES_' || to_char(p_plan_id);
8875: tabname=>'MSC_ATP_SUMMARY_RES',
8876: partname=>l_partition_name,
8877: granularity=>'PARTITION',
8878: percent =>10);
8879: msc_util.msc_log('Gather_Summ_Plan_Stats: ' || 'After gathering stats for Res tables');
8880: ------------------------------------------------
8881:
8882:
8883: END Gather_Summ_Plan_Stats;
8895: l_count NUMBER := 0;
8896: --bug3663487 end
8897: Begin
8898:
8899: msc_util.msc_log('Begin procedure atp_snapshot_hook');
8900:
8901: SELECT count(*)
8902: INTO l_count
8903: FROM msc_plans plans,
8909: AND plans.organization_id = desig.organization_id
8910: AND (desig.inventory_atp_flag = 1
8911: OR plans.copy_plan_id IS NOT NULL);
8912:
8913: msc_util.msc_log('atp_snapshot_hook: l_count: '|| nvl(l_count,0));
8914:
8915: IF ( NVL(l_count,0) > 0) THEN
8916: msc_util.msc_log('atp_snapshot_hook: Updating msc_plan_organizations..');
8917: UPDATE msc_plan_organizations mpo
8912:
8913: msc_util.msc_log('atp_snapshot_hook: l_count: '|| nvl(l_count,0));
8914:
8915: IF ( NVL(l_count,0) > 0) THEN
8916: msc_util.msc_log('atp_snapshot_hook: Updating msc_plan_organizations..');
8917: UPDATE msc_plan_organizations mpo
8918: SET so_lrn =(SELECT so_lrn
8919: FROM msc_instance_orgs mio
8920: WHERE mio.sr_instance_id=mpo.sr_instance_id
8922: )
8923: WHERE plan_id=p_plan_id;
8924: --RETURNING organization_id, sr_instance_id
8925: --BULK COLLECT INTO l_organization_id, l_sr_instance_id;
8926: msc_util.msc_log('atp_snapshot_hook: No. of Rows updated: '|| SQL%ROWCOUNT );
8927: END IF;
8928:
8929: --bug3663487 start
8930: /*
8963: commit;
8964:
8965: EXCEPTION
8966: WHEN others THEN
8967: msc_util.msc_log('Error in atp_snapshot_hook: ' || SQLCODE || '-' || SQLERRM);
8968: END atp_snapshot_hook;
8969:
8970: -- 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
8971: -- This API will be called by "Purge Plan" conc program.
8973: Procedure Delete_CTO_BOM_OSS(
8974: p_plan_id IN NUMBER)
8975: IS
8976: BEGIN
8977: msc_util.msc_log('Begin Delete_CTO_BOM_OSS for plan_id: ' || p_plan_id);
8978: msc_util.msc_log('Before Delete data for CTO BOM');
8979:
8980: DELETE msc_cto_bom
8981: WHERE nvl(plan_id, p_plan_id) = p_plan_id;
8974: p_plan_id IN NUMBER)
8975: IS
8976: BEGIN
8977: msc_util.msc_log('Begin Delete_CTO_BOM_OSS for plan_id: ' || p_plan_id);
8978: msc_util.msc_log('Before Delete data for CTO BOM');
8979:
8980: DELETE msc_cto_bom
8981: WHERE nvl(plan_id, p_plan_id) = p_plan_id;
8982:
8979:
8980: DELETE msc_cto_bom
8981: WHERE nvl(plan_id, p_plan_id) = p_plan_id;
8982:
8983: msc_util.msc_log('After Delete data for CTO BOM: ' || SQL%ROWCOUNT);
8984:
8985: msc_util.msc_log('Before Delete data for CTO OSS');
8986:
8987: DELETE msc_cto_sources
8981: WHERE nvl(plan_id, p_plan_id) = p_plan_id;
8982:
8983: msc_util.msc_log('After Delete data for CTO BOM: ' || SQL%ROWCOUNT);
8984:
8985: msc_util.msc_log('Before Delete data for CTO OSS');
8986:
8987: DELETE msc_cto_sources
8988: WHERE nvl(plan_id, p_plan_id) = p_plan_id;
8989:
8986:
8987: DELETE msc_cto_sources
8988: WHERE nvl(plan_id, p_plan_id) = p_plan_id;
8989:
8990: msc_util.msc_log('After Delete data for CTO OSS: ' || SQL%ROWCOUNT);
8991:
8992: commit;
8993:
8994: msc_util.msc_log('End Delete_CTO_BOM_OSS');
8990: msc_util.msc_log('After Delete data for CTO OSS: ' || SQL%ROWCOUNT);
8991:
8992: commit;
8993:
8994: msc_util.msc_log('End Delete_CTO_BOM_OSS');
8995: EXCEPTION
8996: WHEN others THEN
8997: msc_util.msc_log('Exception in Delete_CTO_BOM_OSS :' || SQLCODE || '-' || SQLERRM);
8998: END Delete_CTO_BOM_OSS;
8993:
8994: msc_util.msc_log('End Delete_CTO_BOM_OSS');
8995: EXCEPTION
8996: WHEN others THEN
8997: msc_util.msc_log('Exception in Delete_CTO_BOM_OSS :' || SQLCODE || '-' || SQLERRM);
8998: END Delete_CTO_BOM_OSS;
8999:
9000: END MSC_POST_PRO;