[Home] [Help]
47: delete from msc_item_inventory_f
48: where plan_id=p_plan_id
49: and plan_run_id=p_plan_run_id
50: and (p_plan_id, sr_instance_id, organization_id, inventory_item_id) in
51: (select number1, number2, number3, number4 from msc_hub_query q where q.query_id=l_item_rn_qid);
52:
53: l_rowcount1 := l_rowcount1 + sql%rowcount;
54: msc_phub_util.log('msc_item_inventory_f, delete='||sql%rowcount||', l_rowcount1='||l_rowcount1);
55: commit;
57: delete from msc_item_orders_f
58: where plan_id=p_plan_id
59: and plan_run_id=p_plan_run_id
60: and (p_plan_id, sr_instance_id, organization_id, inventory_item_id) in
61: (select number1, number2, number3, number4 from msc_hub_query q where q.query_id=l_item_rn_qid);
62:
63: l_rowcount2 := l_rowcount2 + sql%rowcount;
64: msc_phub_util.log('msc_item_orders_f, delete='||sql%rowcount||', l_rowcount2='||l_rowcount2);
65: commit;
174: fnd_global.conc_program_id, fnd_global.conc_login_id,
175: fnd_global.prog_appl_id, fnd_global.conc_request_id
176: from
177: msc_demands_f f,
178: msc_hub_query d,
179: msc_apcc_item_d i
180: where f.plan_run_id=p_plan_run_id
181: and f.aggr_type=0
182: and d.query_id=l_qid_last_date1
189: or (p_plan_id=-1
190: and f.sr_instance_id=l_sr_instance_id
191: and (l_refresh_mode=1
192: or (l_refresh_mode=2 and (f.plan_id, f.sr_instance_id, f.organization_id, f.inventory_item_id) in
193: (select number1, number2, number3, number4 from msc_hub_query q where q.query_id=l_item_rn_qid)))))
194: group by
195: f.sr_instance_id,
196: f.organization_id,
197: f.owning_inst_id,
330: fnd_global.conc_program_id, fnd_global.conc_login_id,
331: fnd_global.prog_appl_id, fnd_global.conc_request_id
332: from
333: msc_supplies_f f,
334: msc_hub_query d,
335: msc_apcc_item_d i
336: where f.plan_run_id=p_plan_run_id
337: and f.aggr_type=0
338: and d.query_id=l_qid_last_date1
345: or (p_plan_id=-1
346: and f.sr_instance_id=l_sr_instance_id
347: and (l_refresh_mode=1
348: or (l_refresh_mode=2 and (f.plan_id, f.sr_instance_id, f.organization_id, f.inventory_item_id) in
349: (select number1, number2, number3, number4 from msc_hub_query q where q.query_id=l_item_rn_qid)))))
350: group by
351: f.sr_instance_id,
352: f.organization_id,
353: f.subinventory_code,
840: fnd_global.prog_appl_id, fnd_global.conc_request_id
841: from
842: msc_item_orders_f f,
843: msc_apcc_item_d i,
844: msc_hub_query d
845: where f.plan_run_id=p_plan_run_id
846: and f.aggr_type=0
847: and f.plan_id=i.plan_id
848: and f.owning_inst_id=i.sr_instance_id
915: mbid.pab pab_qty,
916: rank() over (partition by mbid.plan_id,
917: mbid.sr_instance_id, mbid.organization_id, mbid.inventory_item_id,
918: d.date2 order by mbid.detail_date desc, nvl(mbid.period_type,0) desc) rn
919: from msc_bis_inv_detail mbid, msc_hub_query d
920: where mbid.plan_id=p_plan_id
921: and l_plan_type=6
922: and d.query_id=l_qid_last_date1
923: and mbid.detail_date <= d.date2
1003: f.total_unpooled_safety_stock,
1004: rank() over (partition by f.plan_id,
1005: f.sr_instance_id, f.organization_id, f.inventory_item_id,
1006: d.date2 order by f.period_start_date desc) rn
1007: from msc_safety_stocks f, msc_hub_query d
1008: where f.plan_id=p_plan_id
1009: and p_plan_id <> -1
1010: and d.query_id=l_qid_last_date1
1011: and f.period_start_date <= d.date2
1108: f.target_quantity_dos,
1109: rank() over (partition by f.plan_id,
1110: f.sr_instance_id, f.organization_id, f.inventory_item_id,
1111: d.date2 order by f.inventory_date desc) rn
1112: from msc_inventory_levels f, msc_hub_query d
1113: where f.plan_id=p_plan_id
1114: and p_plan_id <> -1
1115: and d.query_id=l_qid_last_date1
1116: and f.inventory_date <= d.date2