[Home] [Help]
57: if l_refresh_mode = 2 -- targeted refesh
58: then
59: l_item_rn_qid := msc_phub_util.get_item_rn_qid(p_plan_id, p_plan_run_id);
60:
61: delete from msc_supplies_f
62: where plan_id = p_plan_id
63: and plan_run_id = p_plan_run_id
64: and (p_plan_id, sr_instance_id, organization_id, inventory_item_id) in
65: (select number1, number2, number3, number4 from msc_hub_query q where q.query_id = l_item_rn_qid);
64: and (p_plan_id, sr_instance_id, organization_id, inventory_item_id) in
65: (select number1, number2, number3, number4 from msc_hub_query q where q.query_id = l_item_rn_qid);
66:
67: l_rowcount1 := l_rowcount1 + sql%rowcount;
68: msc_phub_util.log('msc_supplies_f, delete='||sql%rowcount||', l_rowcount1='||l_rowcount1);
69: commit;
70:
71: delete from msc_item_wips_f
72: where plan_id = p_plan_id
79: commit;
80: end if;
81: end if;
82:
83: -- Successfully populated msc_supplies_f table for plan_id =' || p_plan_id || ',plan_run_id=' || p_plan_run_id;
84: -- initial there is no error message
85:
86: select plan_type, sr_instance_id
87: into l_plan_type, l_sr_instance_id
104: l_sr_instance_id||','||l_plan_start_date||','||l_plan_cutoff_date||','||
105: l_refresh_mode||','||l_item_rn_qid||','||l_pegging_granularity);
106:
107: ----------------------------------------------------
108: msc_phub_util.gather_table_stats(msc_phub_util.stats_pds, 'MSC_SUPPLIES');
109: msc_phub_util.gather_table_stats(msc_phub_util.stats_pds, 'MSC_FULL_PEGGING');
110: insert into msc_supplies_f (
111: plan_id, -- plan_id
112: plan_run_id,
106:
107: ----------------------------------------------------
108: msc_phub_util.gather_table_stats(msc_phub_util.stats_pds, 'MSC_SUPPLIES');
109: msc_phub_util.gather_table_stats(msc_phub_util.stats_pds, 'MSC_FULL_PEGGING');
110: insert into msc_supplies_f (
111: plan_id, -- plan_id
112: plan_run_id,
113: io_plan_flag, --- this flag indidate whether it is an io plan
114: sr_instance_id,
356: (ms.order_type in (5) and msi.purchasing_enabled_flag=1))
357: then 1 else 0 end) buy_order_count
358:
359: from
360: msc_supplies ms,
361: (select
362: mfp.transaction_id,
363: mfp.sr_instance_id,
364: mfp2.sr_instance_id end_item_inst_id,
474: supply_tbl.supply_type,
475: supply_tbl.vmi_flag;
476:
477: l_rowcount1 := l_rowcount1 + sql%rowcount;
478: msc_phub_util.log('msc_supplies_f, insert='||sql%rowcount||', l_rowcount1='||l_rowcount1);
479: commit;
480: msc_phub_util.gather_table_stats(msc_phub_util.stats_fact, 'MSC_SUPPLIES_F', p_plan_run_id);
481:
482:
476:
477: l_rowcount1 := l_rowcount1 + sql%rowcount;
478: msc_phub_util.log('msc_supplies_f, insert='||sql%rowcount||', l_rowcount1='||l_rowcount1);
479: commit;
480: msc_phub_util.gather_table_stats(msc_phub_util.stats_fact, 'MSC_SUPPLIES_F', p_plan_run_id);
481:
482:
483: --- populate wip start qty
484: --- msc_item_wip_f
534: and (msi.base_item_id is not null or ms.disposition_status_type<>2)
535: then decode(ms.last_unit_completion_date, null, ms.new_order_quantity, ms.daily_rate)
536: else null end) wip_qty
537: from
538: msc_supplies ms,
539: msc_apcc_item_d msi
540: where ms.plan_id = msi.plan_id
541: and ms.sr_instance_id = msi.sr_instance_id
542: and ms.organization_id =msi.organization_id
612: msc_phub_util.log('msc_supply_pkg.summarize_supplies_f');
613: retcode := 0;
614: errbuf := '';
615:
616: delete from msc_supplies_f
617: where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type>0;
618: msc_phub_util.log('msc_supply_pkg.summarize_supplies_f, delete='||sql%rowcount);
619: commit;
620:
618: msc_phub_util.log('msc_supply_pkg.summarize_supplies_f, delete='||sql%rowcount);
619: commit;
620:
621: -- level 1
622: insert into msc_supplies_f (
623: plan_id, plan_run_id, io_plan_flag,
624: sr_instance_id, organization_id, subinventory_code,
625: owning_inst_id, owning_org_id, inventory_item_id,
626: source_org_instance_id, source_organization_id,
697: sysdate, fnd_global.user_id, fnd_global.login_id,
698: fnd_global.conc_program_id, fnd_global.conc_login_id,
699: fnd_global.prog_appl_id, fnd_global.conc_request_id
700: from
701: msc_supplies_f f,
702: msc_phub_item_categories_mv ic1,
703: msc_phub_item_categories_mv ic2,
704: msc_phub_item_categories_mv ic3
705: where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
735: msc_phub_util.log('msc_supply_pkg.summarize_supplies_f, level1='||sql%rowcount);
736: commit;
737:
738: -- level 2
739: insert into msc_supplies_f (
740: plan_id, plan_run_id, io_plan_flag,
741: sr_instance_id, organization_id, subinventory_code,
742: owning_inst_id, owning_org_id, inventory_item_id,
743: source_org_instance_id, source_organization_id,
812: sysdate, fnd_global.user_id, fnd_global.login_id,
813: fnd_global.conc_program_id, fnd_global.conc_login_id,
814: fnd_global.prog_appl_id, fnd_global.conc_request_id
815: from
816: msc_supplies_f f,
817: msc_phub_dates_mv d
818: where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
819: and f.aggr_type between 42 and 44
820: and f.supply_date = d.calendar_date
878: sysdate, fnd_global.user_id, fnd_global.login_id,
879: fnd_global.conc_program_id, fnd_global.conc_login_id,
880: fnd_global.prog_appl_id, fnd_global.conc_request_id
881: from
882: msc_supplies_f f,
883: msc_phub_dates_mv d
884: where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
885: and f.aggr_type between 42 and 44
886: and f.supply_date = d.calendar_date
1201: l_sql := l_sql||
1202: ' fnd_global.user_id, sysdate,'||
1203: ' fnd_global.user_id, sysdate, fnd_global.login_id'||
1204: ' from'||
1205: ' '||l_apps_schema||'.msc_supplies_f'||l_suffix||' f,'||
1206: ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,';
1207: if (p_source_version >= '12.1.3') then l_sql := l_sql||
1208: ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp2,'||
1209: ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp3,'||
1343: p_plan_type number, p_plan_start_date date, p_plan_cutoff_date date,
1344: p_upload_mode number, p_overwrite_after_date date, p_def_instance_code varchar2)
1345: is
1346: l_staging_table varchar2(30) := 'msc_st_supplies_f';
1347: l_fact_table varchar2(30) := 'msc_supplies_f';
1348: l_result number := 0;
1349: begin
1350: msc_phub_util.log('msc_supply_pkg.import_supplies_f');
1351: retcode := 0;
1397:
1398: l_result := l_result + msc_phub_util.decode_item_key(
1399: l_staging_table, p_st_transaction_id, 'parent_model_item_id', 'parent_model_item_name');
1400:
1401: msc_phub_util.log('msc_supply_pkg.import_supplies_f: insert into msc_supplies_f');
1402: insert into msc_supplies_f (
1403: plan_id,
1404: plan_run_id,
1405: sr_instance_id,
1398: l_result := l_result + msc_phub_util.decode_item_key(
1399: l_staging_table, p_st_transaction_id, 'parent_model_item_id', 'parent_model_item_name');
1400:
1401: msc_phub_util.log('msc_supply_pkg.import_supplies_f: insert into msc_supplies_f');
1402: insert into msc_supplies_f (
1403: plan_id,
1404: plan_run_id,
1405: sr_instance_id,
1406: organization_id,