[Home] [Help]
1: PACKAGE BODY msc_phub_pkg AS
2: /* $Header: MSCHBPBB.pls 120.131.12020000.4 2012/12/21 16:58:27 wexia ship $ */
3:
4: partition_add constant number := 1;
5: partition_drop constant number := 2;
137: return number is
138: l_req_id number := null;
139: begin
140: if (has_etl(p_package, p_plan_type)) then
141: msc_phub_util.log('msc_phub_pkg.submit_each('||p_package||','||p_plan_id||','||p_plan_run_id||')');
142: l_req_id := fnd_request.submit_request('MSC', 'MSCHUBA2', null,
143: null, false, p_package, p_plan_id, p_plan_run_id);
144: msc_phub_util.log('msc_phub_pkg.submit_each: l_req_id='||l_req_id);
145: commit;
140: if (has_etl(p_package, p_plan_type)) then
141: msc_phub_util.log('msc_phub_pkg.submit_each('||p_package||','||p_plan_id||','||p_plan_run_id||')');
142: l_req_id := fnd_request.submit_request('MSC', 'MSCHUBA2', null,
143: null, false, p_package, p_plan_id, p_plan_run_id);
144: msc_phub_util.log('msc_phub_pkg.submit_each: l_req_id='||l_req_id);
145: commit;
146: end if;
147: return l_req_id;
148: end submit_each;
192: end if;
193: -- dbms_lock.sleep(10);
194: end loop;
195:
196: msc_phub_util.log('msc_phub_pkg.msc_wait_for_request: failed: '||p_request_id||','||
197: l_phase||','||l_status||','||l_dev_phase||','||l_dev_status||','||l_message);
198: raise e_msc_wait_for_request;
199: end msc_wait_for_request;
200:
212: l_custom_req_id number := null;
213:
214: l_max_wait_time number := 999999;
215: begin
216: msc_phub_util.log('msc_phub_pkg.populate_facts('||p_plan_id||','||p_plan_run_id||','||p_plan_type||')');
217: l_exceptions_req_id := submit_each('msc_exception_pkg', p_plan_id, p_plan_run_id, p_plan_type);
218: l_supplies_req_id := submit_each('msc_supply_pkg', p_plan_id, p_plan_run_id, p_plan_type);
219: l_demands_req_id := submit_each('msc_demand_pkg', p_plan_id, p_plan_run_id, p_plan_type);
220: l_costs_req_id := submit_each('msc_phub_cost_pkg', p_plan_id, p_plan_run_id, p_plan_type);
261: errbuf varchar2(2000);
262: pragma exception_init (e_partition_not_exist, -2149);
263: pragma exception_init (e_deadlock, -60);
264: begin
265: msc_phub_util.log('msc_phub_pkg.manage_partitions('||
266: p_tables.count||', '||p_partition_id||', '||
267: p_mode||', '||p_partition_type||')');
268: if (p_partition_id is null or p_tables.count=0) then
269: return;
328: end loop;
329: end if;
330: end loop;
331:
332: msc_phub_util.log('msc_phub_pkg.manage_partitions: complete');
333:
334: exception
335: when others then
336: if (retcode = 0) then
336: if (retcode = 0) then
337: retcode := -1;
338: errbuf := sqlerrm;
339: end if;
340: msc_phub_util.log('msc_phub_pkg.manage_partitions.exception: '||errbuf);
341: raise;
342:
343: end manage_partitions;
344:
402: return l_pi;
403:
404: exception
405: when others then
406: msc_phub_util.log('msc_phub_pkg.get_plan_info.exception: '||sqlerrm);
407: raise;
408: end get_plan_info;
409:
410: function make_demantra_plan_info(p_plan_id number, p_publisher varchar2, p_plan_name varchar2) return plan_info
430: l_max_wait_time number := 999999;
431: l_request_id number;
432: l_plan_name varchar2(50);
433: begin
434: msc_phub_util.log('msc_phub_pkg.purge_previous_plan_run('||p_plan_run_id||')');
435:
436: select plan_name into l_plan_name from msc_plan_runs where plan_run_id=p_plan_run_id;
437:
438: select max(plan_run_id)
446: l_request_id := fnd_request.submit_request('MSC','MSCHUBP',
447: null, null, false, l_plan_name, l_prev_plan_run_id);
448: commit;
449:
450: msc_phub_util.log('msc_phub_pkg.purge_previous_plan_run, MSCHUBP('||
451: l_plan_name||','||l_prev_plan_run_id||'), l_request_id='||l_request_id);
452:
453: if (l_request_id = 0) then
454: purge_details(l_plan_name, l_prev_plan_run_id);
453: if (l_request_id = 0) then
454: purge_details(l_plan_name, l_prev_plan_run_id);
455: end if;
456: else
457: msc_phub_util.log('msc_phub_pkg.purge_previous_plan_run, l_prev_plan_run_id='||l_prev_plan_run_id);
458: end if;
459:
460: exception
461: when others then
458: end if;
459:
460: exception
461: when others then
462: msc_phub_util.log('msc_phub_pkg.purge_previous_plan_run.exception: '||sqlerrm);
463: raise;
464: end purge_previous_plan_run;
465:
466: function remote_context_sql(p_dblink varchar2, p_sql varchar2) return varchar2
496: ' end;';
497:
498: exception
499: when others then
500: msc_phub_util.log('msc_phub_pkg.remote_context_sql.exception: '||sqlerrm);
501: fnd_message.set_name('MSC', 'MSC_APCC_DBLINK_E01');
502: fnd_message.set_token('DBLINK', p_dblink);
503: msc_phub_util.log(fnd_message.get);
504: raise;
517: l_upload_mode number := 3;
518: begin
519: retcode := 0;
520: errbuf := null;
521: msc_phub_util.log('msc_phub_pkg.publish_to_central_apcc('||p_plan_id
522: ||','||p_plan_run_id||','||p_target_plan_name
523: ||','||p_dblink||','||p_include_ods||','||p_archive_flag||')');
524:
525: if (l_db_name is null) then
524:
525: if (l_db_name is null) then
526: select value into l_db_name from v$parameter where name='db_name';
527: end if;
528: msc_phub_util.log('msc_phub_pkg.publish_to_central_apcc:l_db_name='||l_db_name);
529:
530: select
531: plan_id,
532: plan_name,
572: exception
573: when others then
574: if (retcode = 0) then
575: retcode := -1;
576: errbuf := 'msc_phub_pkg.publish_to_central_apcc.exception: '||sqlerrm;
577: end if;
578: msc_phub_util.log(errbuf);
579: end publish_to_central_apcc;
580:
636:
637: msc_phub_util.log(fnd_message.get_string('MSC','MSC_HUB_POPULATE_STARTS'));
638: execute immediate 'alter session set ddl_lock_timeout=18000';
639:
640: msc_phub_util.log('msc_phub_pkg.populate_details_fn('||
641: p_plan_id||','||
642: p_plan_run_id||','||
643: p_archive_flag||','||
644: p_target_plan_name||','||
660: l_demantra_flag := sys_yes;
661: l_plan_type := 10;
662:
663: if (msc_phub_util.demantra_schema is null) then
664: msc_phub_util.log('msc_phub_pkg.populate_details_fn: (msc_phub_util.demantra_schema is null)');
665: return null;
666: end if;
667: end if;
668:
674: end if;
675:
676: -- validate inputs
677: if (l_pi.plan_name is null and p_include_ods=sys_no) then
678: msc_phub_util.log('msc_phub_pkg.populate_details_fn: (l_pi.plan_name is null and p_include_ods=sys_no)');
679: return null;
680: end if;
681:
682: -- validate inputs
686: from msc_plans
687: where plan_id=l_pi.plan_id;
688:
689: if (l_display_kpi=2) then
690: msc_phub_util.log('msc_phub_pkg.populate_details_fn: (l_pi.plan_type in (1,4,8) and l_display_kpi=2)');
691: return null;
692: end if;
693: end if;
694:
701:
702: if (l_n > 0) then
703: fnd_message.set_name('MSC', 'MSC_APCC_PLAN_INFO_E01');
704: retcode := -1;
705: errbuf := 'msc_phub_pkg.populate_details_fn: '||fnd_message.get;
706: msc_phub_util.log(errbuf);
707: raise e_populate_details;
708: end if;
709:
718: fnd_message.set_name('MSC', 'MSC_APCC_PLAN_INFO_E03');
719: fnd_message.set_token('PLAN', l_pi.plan_name);
720: fnd_message.set_token('PLAN_TYPE', get_plan_type_meaning(l_plan_type));
721: retcode := -1;
722: errbuf := 'msc_phub_pkg.populate_details_fn: '||fnd_message.get;
723: msc_phub_util.log(errbuf);
724: raise e_populate_details;
725: exception
726: when others then
761:
762: l_request_id := fnd_request.submit_request('MSC', 'MSCHUBP', null, null, false, l_pi.plan_name, l_plan_run_id);
763: commit;
764:
765: msc_phub_util.log('msc_phub_pkg.populate_details_fn, MSCHUBP('||
766: l_pi.plan_name||','||l_plan_run_id||'), l_request_id='||l_request_id);
767: l_return_status := sys_no;
768: end if;
769: end if;
802: where plan_run_id=l_plan_run_id;
803: commit;
804: end if;
805: retcode := -1;
806: errbuf := 'msc_phub_pkg.populate_details_fn: '||sqlerrm;
807: msc_phub_util.log(errbuf);
808: return l_plan_run_id;
809:
810: end populate_details_fn;
816: l_plan_name varchar2(50);
817: l_plan_id number;
818: l_rowcount number := 0;
819: begin
820: msc_phub_util.log('msc_phub_pkg.purge_details('||p_plan_name||','||p_plan_run_id||')');
821: if (l_purge_list.count = 0) then
822: msc_phub_util.log('msc_phub_pkg.purge_details: (l_purge_list.count = 0)');
823: return;
824: end if;
818: l_rowcount number := 0;
819: begin
820: msc_phub_util.log('msc_phub_pkg.purge_details('||p_plan_name||','||p_plan_run_id||')');
821: if (l_purge_list.count = 0) then
822: msc_phub_util.log('msc_phub_pkg.purge_details: (l_purge_list.count = 0)');
823: return;
824: end if;
825:
826: for r in c loop
873: end if;
874: end if;
875:
876: msc_phub_custom_pkg.purge_details(p_plan_name, p_plan_run_id);
877: msc_phub_util.log('msc_phub_pkg.purge_details: complete');
878: end purge_details;
879:
880: procedure refresh_mvs(p_refresh_mode varchar2)
881: is
901: l_name varchar2(50);
902: p_return_status number;
903: p_error_message varchar2(2000);
904: begin
905: msc_phub_util.log('msc_phub_pkg.refresh_mvs('||p_refresh_mode||')');
906: retcode := 0;
907: errbuf := null;
908:
909: --1 ods, 2 pds, 3 both
962:
963: exception
964: when others then
965: retcode := -1;
966: errbuf := 'msc_phub_pkg.refresh_mvs.exception: '||sqlerrm;
967: msc_phub_util.log(errbuf);
968:
969: end refresh_mvs;
970:
999: l_transfer_id number;
1000: l_plan_type number;
1001: l_plan_name varchar(50);
1002: begin
1003: msc_phub_util.log('msc_phub_pkg.finalize_plan_run('||
1004: p_plan_id||','||p_plan_run_id||','||p_include_ods||','||
1005: p_archive_flag||','||p_success||','||p_keep_previous||')');
1006:
1007: if (p_plan_run_id is not null) then
1044: end if;
1045:
1046: exception
1047: when others then
1048: msc_phub_util.log('msc_phub_pkg.finalize_plan_run.exception: '||sqlerrm);
1049: raise;
1050: end finalize_plan_run;
1051:
1052: function create_staging_partitions(p_plan_type number, p_include_ods number) return number
1056: l_transfer_id number := null;
1057: e_create_staging_partitions exception;
1058: l_staging_tables object_names := list_staging_tables(p_plan_type, p_include_ods);
1059: begin
1060: msc_phub_util.log('msc_phub_pkg.create_staging_partitions('||p_plan_type||','||p_include_ods||')');
1061:
1062: if (l_staging_tables.count > 0) then
1063: -- add partitions synchronuously
1064: dbms_lock.allocate_unique('msc_apcc_upload_s', l_lock);
1061:
1062: if (l_staging_tables.count > 0) then
1063: -- add partitions synchronuously
1064: dbms_lock.allocate_unique('msc_apcc_upload_s', l_lock);
1065: msc_phub_util.log('msc_phub_pkg.create_staging_partitions: dbms_lock.request');
1066: l_error := dbms_lock.request(l_lock);
1067: if (l_error <> 0) then
1068: msc_phub_util.log('msc_phub_pkg.create_staging_partitions: dbms_lock.request='||l_error);
1069: raise e_create_staging_partitions;
1064: dbms_lock.allocate_unique('msc_apcc_upload_s', l_lock);
1065: msc_phub_util.log('msc_phub_pkg.create_staging_partitions: dbms_lock.request');
1066: l_error := dbms_lock.request(l_lock);
1067: if (l_error <> 0) then
1068: msc_phub_util.log('msc_phub_pkg.create_staging_partitions: dbms_lock.request='||l_error);
1069: raise e_create_staging_partitions;
1070: end if;
1071:
1072: select msc_apcc_upload_s.nextval into l_transfer_id from dual;
1076: return l_transfer_id;
1077:
1078: exception
1079: when others then
1080: msc_phub_util.log('msc_phub_pkg.create_staging_partitions.exception: '||sqlerrm);
1081: l_error := dbms_lock.release(l_lock);
1082: msc_phub_util.log('msc_phub_pkg.create_staging_partitions.exception: dbms_lock.release='||l_error);
1083: rollback;
1084: raise;
1078: exception
1079: when others then
1080: msc_phub_util.log('msc_phub_pkg.create_staging_partitions.exception: '||sqlerrm);
1081: l_error := dbms_lock.release(l_lock);
1082: msc_phub_util.log('msc_phub_pkg.create_staging_partitions.exception: dbms_lock.release='||l_error);
1083: rollback;
1084: raise;
1085: end create_staging_partitions;
1086:
1087: procedure drop_staging_partitions(p_transfer_id number, p_plan_type number, p_include_ods number)
1088: is
1089: l_staging_tables object_names := list_staging_tables(p_plan_type, p_include_ods);
1090: begin
1091: msc_phub_util.log('msc_phub_pkg.drop_staging_partitions('||p_transfer_id||','||p_plan_type||','||p_include_ods||')');
1092: manage_partitions(l_staging_tables, p_transfer_id, partition_drop, partition_range);
1093: end drop_staging_partitions;
1094:
1095: function create_fact_partitions(
1111:
1112: if (l_fact_tables.count > 0) then
1113: -- create fact partitions synchronuously
1114: dbms_lock.allocate_unique('msc_plan_runs_s', l_lock);
1115: msc_phub_util.log('msc_phub_pkg.create_fact_partitions: dbms_lock.request');
1116: l_error := dbms_lock.request(l_lock);
1117: if (l_error <> 0) then
1118: msc_phub_util.log('msc_phub_pkg.create_fact_partitions: dbms_lock.request='||l_error);
1119: raise e_create_fact_partitions;
1114: dbms_lock.allocate_unique('msc_plan_runs_s', l_lock);
1115: msc_phub_util.log('msc_phub_pkg.create_fact_partitions: dbms_lock.request');
1116: l_error := dbms_lock.request(l_lock);
1117: if (l_error <> 0) then
1118: msc_phub_util.log('msc_phub_pkg.create_fact_partitions: dbms_lock.request='||l_error);
1119: raise e_create_fact_partitions;
1120: end if;
1121:
1122: select msc_plan_runs_s.nextval into l_plan_run_id from dual;
1127:
1128: exception
1129: when others then
1130: l_error := dbms_lock.release(l_lock);
1131: msc_phub_util.log('msc_phub_pkg.create_fact_partitions.exception: dbms_lock.release='||l_error);
1132: raise;
1133: end create_fact_partitions;
1134:
1135: function create_plan_run(
1143: l_plan_version number;
1144: l_transfer_id number := null;
1145: e_create_plan_run exception;
1146: begin
1147: msc_phub_util.log('msc_phub_pkg.create_plan_run('||
1148: p_scenario_name||','||p_include_ods||','||p_local_archive_flag||','||
1149: p_pi.plan_id||','||p_pi.plan_type||')');
1150:
1151: if (p_pi.plan_name is null) then
1163: when others then null;
1164: end;
1165:
1166: if (l_error = 1) then
1167: msc_phub_util.log('msc_phub_pkg.create_plan_run: p_local_archive_flag mismatch');
1168: raise e_create_plan_run;
1169: end if;
1170:
1171: l_plan_run_id := create_fact_partitions(p_local_archive_flag, p_pi.plan_type);
1202: fnd_global.conc_program_id, fnd_global.conc_login_id, fnd_global.prog_appl_id, fnd_global.conc_request_id
1203: );
1204: commit;
1205:
1206: msc_phub_util.log('msc_phub_pkg.create_plan_run('||p_pi.plan_id||', '||l_plan_run_id||
1207: ', '''||l_plan_run_name||''')');
1208: end if;
1209: return l_plan_run_id;
1210:
1209: return l_plan_run_id;
1210:
1211: exception
1212: when others then
1213: msc_phub_util.log('msc_phub_pkg.create_plan_run.exception: '||sqlerrm);
1214: rollback;
1215: raise;
1216:
1217: end create_plan_run;
1228: p_package varchar2, p_plan_id number, p_plan_run_id number)
1229: is
1230: l_sql varchar2(200);
1231: begin
1232: msc_phub_util.log('msc_phub_pkg.populate_each: ('||p_package||','||p_plan_id||','||p_plan_run_id||')');
1233: l_sql := 'begin '||p_package||'.populate_details(:errbuf, :retcode, :p_plan_id, :p_plan_run_id); end;';
1234: execute immediate l_sql using out errbuf, out retcode, p_plan_id, p_plan_run_id;
1235: exception
1236: when others then
1235: exception
1236: when others then
1237: if (retcode = 0) then
1238: retcode := -1;
1239: errbuf := 'msc_phub_pkg.populate_each: '||sqlerrm;
1240: msc_phub_util.log(errbuf);
1241: end if;
1242: raise;
1243: end populate_each;
1245: procedure build_null_items(p_plan_id number)
1246: is
1247: l_plan_type number := null;
1248: begin
1249: msc_phub_util.log('msc_phub_pkg.build_null_items('||p_plan_id||')');
1250:
1251: begin
1252: select plan_type into l_plan_type
1253: from msc_plan_runs
1435: fnd_global.conc_program_id, fnd_global.conc_login_id,
1436: fnd_global.prog_appl_id, fnd_global.conc_request_id
1437: from dual;
1438:
1439: msc_phub_util.log('msc_phub_pkg.build_null_items: insert='||sql%rowcount);
1440: commit;
1441:
1442: exception
1443: when others then
1440: commit;
1441:
1442: exception
1443: when others then
1444: msc_phub_util.log('msc_phub_pkg.build_null_items.exception:'||sqlerrm);
1445: raise;
1446: end build_null_items;
1447:
1448: procedure build_items_from_pds(p_plan_id number)
1447:
1448: procedure build_items_from_pds(p_plan_id number)
1449: is
1450: begin
1451: msc_phub_util.log('msc_phub_pkg.build_items_from_pds('||p_plan_id||')');
1452: manage_partitions(item_dim_table, p_plan_id, partition_init, partition_list);
1453: insert into msc_apcc_item_d (
1454: plan_id,
1455: sr_instance_id,
1599: and i.sr_instance_id = mis.sr_instance_id(+)
1600: and i.organization_id = mis.organization_id(+)
1601: and i.inventory_item_id = mis.inventory_item_id(+);
1602:
1603: msc_phub_util.log('msc_phub_pkg.build_items_from_pds: insert='||sql%rowcount);
1604: commit;
1605:
1606: build_null_items(p_plan_id);
1607: msc_phub_util.gather_table_stats(msc_phub_util.stats_fact, 'MSC_APCC_ITEM_D', p_plan_id);
1604: commit;
1605:
1606: build_null_items(p_plan_id);
1607: msc_phub_util.gather_table_stats(msc_phub_util.stats_fact, 'MSC_APCC_ITEM_D', p_plan_id);
1608: msc_phub_util.log('msc_phub_pkg.build_items_from_pds: complete');
1609:
1610: exception
1611: when others then
1612: msc_phub_util.log('msc_phub_pkg.build_items_from_pds.exception:'||sqlerrm);
1608: msc_phub_util.log('msc_phub_pkg.build_items_from_pds: complete');
1609:
1610: exception
1611: when others then
1612: msc_phub_util.log('msc_phub_pkg.build_items_from_pds.exception:'||sqlerrm);
1613: raise;
1614: end build_items_from_pds;
1615:
1616: procedure gather_items(p_query_id number, p_fact_type number, p_plan_id number, p_plan_run_id number)
1651: commit;
1652:
1653: exception
1654: when others then
1655: msc_phub_util.log('msc_phub_pkg.gather_items.exception:'||sqlerrm);
1656: raise;
1657: end gather_items;
1658:
1659: procedure build_items_from_apcc(p_plan_id number, p_plan_run_id number)
1660: is
1661: l_qid_plan_item number;
1662: cursor c is select fact_type, item_dim from table(meta_info) where item_dim in (1,2) and fact_type<>4;
1663: begin
1664: msc_phub_util.log('msc_phub_pkg.build_items_from_apcc('||
1665: p_plan_id||','||p_plan_run_id||')');
1666:
1667: if (nvl(p_plan_id, -1) <= 0) then
1668: return;
1671: select msc_hub_query_s.nextval into l_qid_plan_item from dual;
1672: for r in c loop
1673: gather_items(l_qid_plan_item, r.fact_type, p_plan_id, p_plan_run_id);
1674: end loop;
1675: --msc_phub_util.log('msc_phub_pkg.build_items_from_apcc: l_qid_plan_item='||l_qid_plan_item);
1676:
1677: -- prepare partition
1678: manage_partitions(item_dim_table, p_plan_id, partition_init, partition_list);
1679:
1685: where q.query_id=l_qid_plan_item
1686: and q.number3=d.sr_instance_id
1687: and q.number4=d.organization_id
1688: and q.number5=d.inventory_item_id);
1689: msc_phub_util.log('msc_phub_pkg.build_items_from_apcc: delete='||sql%rowcount);
1690: commit;
1691:
1692: -- delete existing items
1693: delete from msc_apcc_item_d d
1692: -- delete existing items
1693: delete from msc_apcc_item_d d
1694: where d.plan_id=p_plan_id
1695: and d.inventory_item_id<0;
1696: msc_phub_util.log('msc_phub_pkg.build_items_from_apcc: delete='||sql%rowcount);
1697: commit;
1698:
1699: -- insert items
1700: insert into msc_apcc_item_d (
1798: where d.sr_instance_id=q.sr_instance_id
1799: and d.organization_id=q.organization_id
1800: and d.inventory_item_id=q.inventory_item_id
1801: and d.plan_id=-1;
1802: msc_phub_util.log('msc_phub_pkg.build_items_from_apcc: insert='||sql%rowcount);
1803: commit;
1804:
1805: build_null_items(p_plan_id);
1806:
1805: build_null_items(p_plan_id);
1806:
1807: exception
1808: when others then
1809: msc_phub_util.log('msc_phub_pkg.build_items_from_apcc.exception:'||sqlerrm);
1810: raise;
1811: end build_items_from_apcc;
1812:
1813: procedure populate_ods_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
1829: errbuf := null;
1830:
1831: msc_phub_util.log(fnd_message.get_string('MSC','MSC_HUB_POPULATE_STARTS'));
1832:
1833: msc_phub_util.log('msc_phub_pkg.populate_ods_details('||
1834: p_sr_instance_id||','||p_refresh_mode||')');
1835:
1836: -- prepare plan info
1837: select compile_designator, description
1947: where plan_id = con_ods_plan_id
1948: and sr_instance_id=p_sr_instance_id
1949: and archive_flag=1;
1950:
1951: msc_phub_util.log('msc_phub_pkg.populate_ods_details('||l_plan_run_id||
1952: ', '''||l_plan_run_name||''')');
1953:
1954: if (p_refresh_mode = 1) then
1955: manage_partitions(list_fact_tables(-1), l_plan_run_id, partition_truncate, partition_range);
1983: commit;
1984:
1985: exception
1986: when no_data_found then
1987: msc_phub_util.log('msc_phub_pkg.populate_ods_details.exception: l_plan_run_id is null');
1988:
1989: select instance_code into l_instance_code
1990: from msc_apps_instances where instance_id=p_sr_instance_id;
1991:
2011: procedure clean_up_customer_keys
2012: is
2013: l_n number := 0;
2014: begin
2015: msc_phub_util.log('msc_phub_pkg.clean_up_customer_keys');
2016:
2017: select count(*) into l_n from msc_demands_f where customer_id>0 and region_id>0 and rownum=1;
2018: if (l_n > 0) then
2019: msc_phub_util.log('update msc_demands_f set region_id=-23453 where customer_id>0');
2057: end if;
2058:
2059: exception
2060: when others then
2061: msc_phub_util.log('msc_phub_pkg.clean_up_customer_keys.exception:'||sqlerrm);
2062: raise;
2063: end clean_up_customer_keys;
2064:
2065: procedure clean_up_fact_partitions
2081:
2082: e_partition_not_exist exception;
2083: pragma exception_init (e_partition_not_exist, -2149);
2084: begin
2085: msc_phub_util.log('msc_phub_pkg.clean_up_fact_partitions');
2086:
2087: l_applsys_schema := msc_phub_util.applsys_schema;
2088: l_msc_schema := msc_phub_util.msc_schema;
2089: l_tables := list_fact_tables(null);
2095: msc_phub_util.log(l_ddl);
2096: ad_ddl.do_ddl(l_applsys_schema, l_msc_schema, ad_ddl.alter_table, l_ddl, r.table_name);
2097: exception
2098: when e_partition_not_exist then
2099: msc_phub_util.log('msc_phub_pkg.clean_up_fact_partitions.exception: '||sqlerrm);
2100: end;
2101: end loop;
2102: end loop;
2103: msc_phub_util.log('msc_phub_pkg.clean_up_fact_partitions: complete');
2099: msc_phub_util.log('msc_phub_pkg.clean_up_fact_partitions.exception: '||sqlerrm);
2100: end;
2101: end loop;
2102: end loop;
2103: msc_phub_util.log('msc_phub_pkg.clean_up_fact_partitions: complete');
2104:
2105: exception
2106: when others then
2107: msc_phub_util.log('msc_phub_pkg.clean_up_fact_partitions.exception: '||sqlerrm);
2103: msc_phub_util.log('msc_phub_pkg.clean_up_fact_partitions: complete');
2104:
2105: exception
2106: when others then
2107: msc_phub_util.log('msc_phub_pkg.clean_up_fact_partitions.exception: '||sqlerrm);
2108: raise;
2109: end clean_up_fact_partitions;
2110:
2111: procedure migrate_item_d
2114: l_plan_id number;
2115: c sys_refcursor;
2116: l_msc_schema varchar2(100) := msc_phub_util.msc_schema;
2117: begin
2118: msc_phub_util.log('msc_phub_pkg.migrate_item_d');
2119: open c for
2120: ' select distinct plan_id'||
2121: ' from msc_apcc_item_d_bak'||
2122: ' where plan_id>0'||
2223: msc_phub_util.log('insert into msc_apcc_item_d: '||sql%rowcount);
2224: commit;
2225: exception
2226: when others then
2227: msc_phub_util.log('msc_phub_pkg.migrate_item_d.exception:'||sqlerrm);
2228: end migrate_item_d;
2229:
2230: procedure migrate_scenario_names is
2231: begin
2228: end migrate_item_d;
2229:
2230: procedure migrate_scenario_names is
2231: begin
2232: msc_phub_util.log('msc_phub_pkg.migrate_scenario_names');
2233: update msc_plan_runs set scenario_name = regexp_substr(plan_run_name, '\[.*\]', 1, 1);
2234: update msc_plan_runs set scenario_name = substr(scenario_name, 2, length(scenario_name)-2);
2235: commit;
2236:
2258: p_cleanup_facts number default null, p_operation_code varchar2 default null)
2259: is
2260: l_n number := 0;
2261: begin
2262: msc_phub_util.log('msc_phub_pkg.maintain_data_model('||
2263: p_cleanup_facts||','||p_operation_code||')');
2264: retcode := 0;
2265: errbuf := null;
2266:
2282:
2283: exception
2284: when others then
2285: retcode := -1;
2286: errbuf := 'msc_phub_pkg.maintain_data_model.exception:'||sqlerrm;
2287: msc_phub_util.log(errbuf);
2288: raise;
2289: end maintain_data_model;
2290:
2292: is
2293: l_need_migrate number := 0;
2294: l_request_id number := 0;
2295: begin
2296: msc_phub_util.log('msc_phub_pkg.check_migrate');
2297:
2298: -- check migration flag
2299: select count(*) into l_need_migrate
2300: from msc_plan_runs
2299: select count(*) into l_need_migrate
2300: from msc_plan_runs
2301: where plan_id=0 and plan_run_id=-1;
2302:
2303: msc_phub_util.log('msc_phub_pkg.check_migrate, l_need_migrate='||l_need_migrate);
2304:
2305: if (l_need_migrate > 0) then
2306: l_request_id := fnd_request.submit_request('MSC','MSCHUBM', null, null, false);
2307: msc_phub_util.log('msc_phub_pkg.check_migrate: l_request_id='||l_request_id);
2303: msc_phub_util.log('msc_phub_pkg.check_migrate, l_need_migrate='||l_need_migrate);
2304:
2305: if (l_need_migrate > 0) then
2306: l_request_id := fnd_request.submit_request('MSC','MSCHUBM', null, null, false);
2307: msc_phub_util.log('msc_phub_pkg.check_migrate: l_request_id='||l_request_id);
2308: commit;
2309:
2310: msc_wait_for_request(l_request_id);
2311: end if;
2312:
2313: exception
2314: when others then
2315: retcode := -1;
2316: errbuf := 'msc_phub_pkg.check_migrate.exception:'||sqlerrm;
2317: msc_phub_util.log(errbuf);
2318: raise;
2319: end check_migrate;
2320:
2365: exception
2366: when others then
2367: retcode := -1;
2368: errbuf := sqlerrm;
2369: msc_phub_util.log('msc_phub_pkg.sync_demantra.exception: '||errbuf);
2370: end sync_demantra;
2371:
2372: END msc_phub_pkg;
2368: errbuf := sqlerrm;
2369: msc_phub_util.log('msc_phub_pkg.sync_demantra.exception: '||errbuf);
2370: end sync_demantra;
2371:
2372: END msc_phub_pkg;