[Home] [Help]
PACKAGE BODY: APPS.MSC_DEMAND_PKG
Source
1 package body msc_demand_pkg as
2 /* $Header: MSCHBDEB.pls 120.92.12020000.2 2012/10/11 13:52:38 wexia ship $ */
3
4
5 procedure populate_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
6 p_plan_id number, p_plan_run_id number default null) is
7
8 l_owning_currency_code varchar2(20) := msc_phub_util.get_owning_currency_code(p_plan_run_id);
9
10 l_refresh_mode number;
11 l_item_rn_qid number;
12 l_plan_start_date date;
13 l_plan_cutoff_date date;
14 l_plan_type number;
15 l_sr_instance_id number;
16 con_ods_plan_id constant number := -1;
17 l_rowcount1 number := 0;
18 l_rowcount2 number := 0;
19 l_transfer_id number := null;
20 l_start_time timestamp := systimestamp;
21 l_ddl varchar2(500);
22 l_enable_num number := nvl(fnd_profile.value('MSC_APCC_ENABLE_CUM'), 1);
23 begin
24 msc_phub_util.log('msc_demand_pkg.populate_details');
25
26 retcode := 0; -- this means successfully
27 errbuf := '';
28
29 select plan_type, sr_instance_id, plan_start_date, plan_cutoff_date, temp_transfer_id
30 into l_plan_type, l_sr_instance_id, l_plan_start_date, l_plan_cutoff_date, l_transfer_id
31 from msc_plan_runs
32 where plan_id=p_plan_id
33 and plan_run_id=p_plan_run_id;
34
35 -- ODS plan
36 if p_plan_id = con_ods_plan_id
37 then
38 -- get refresh_mode
39 select refresh_mode into l_refresh_mode
40 from msc_plan_runs
41 where plan_run_id = p_plan_run_id;
42
43 if l_refresh_mode = 2 -- targeted refesh
44 then
45 l_item_rn_qid := msc_phub_util.get_item_rn_qid(p_plan_id, p_plan_run_id);
46
47 delete from msc_demands_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_demands_f, delete='||sql%rowcount||', l_rowcount1='||l_rowcount1);
55 commit;
56
57 if (l_enable_num not in (2)) then
58 delete from msc_demands_cum_f
59 where plan_id = p_plan_id
60 and plan_run_id = p_plan_run_id
61 and (p_plan_id, sr_instance_id, organization_id, inventory_item_id) in
62 (select number1, number2, number3, number4 from msc_hub_query q where q.query_id = l_item_rn_qid);
63
64 l_rowcount2 := l_rowcount2 + sql%rowcount;
65 msc_phub_util.log('msc_demands_cum_f, delete='||sql%rowcount||', l_rowcount2='||l_rowcount2);
66 commit;
67 end if;
68 end if;
69 end if;
70
71 msc_phub_util.log('msc_demand_pkg.populate_details: '||
72 p_plan_id||','||p_plan_run_id||','||l_plan_type||','||
73 l_sr_instance_id||','||l_plan_start_date||','||l_plan_cutoff_date||','||
74 l_transfer_id||','||l_refresh_mode||','||l_item_rn_qid);
75
76 -- msc_st_demands_f:demands
77 insert /*+ append nologging */ into msc_st_demands_f (
78 st_transaction_id,
79 error_code,
80 sr_instance_id,
81 organization_id,
82 owning_inst_id,
83 owning_org_id,
84 inventory_item_id,
85 customer_id,
86 customer_site_id,
87 region_id,
88 project_id,
89 task_id,
90 original_item_id,
91 order_type,
92 order_date,
93 demand_class,
94 part_condition,
95 demand_qty,
96 qty_by_due_date,
97 net_demand,
98 constrained_fcst,
99 indep_demand_count,
100 indep_met_ontime_count,
101 indep_met_full_count,
102 indep_demand_qty,
103 indep_by_due_date_qty,
104 sales_order_qty,
105 sales_order_count,
106 sales_order_metr_count,
107 sales_order_meta_count,
108 forecast_qty,
109 io_delivered_qty,
110 io_required_qty,
111 service_level,
112 demand_fulfillment_lead_time,
113
114 created_by, creation_date,
115 last_update_date, last_updated_by, last_update_login,
116 program_id, program_login_id,
117 program_application_id, request_id)
118 select
119 l_transfer_id,
120 to_number(0),
121 -- sync sr_instance_id with organization_id
122 -- this is important since in org dimension,
123 -- it only has (inst,org)=(-23543,-23453)
124
125 decode(md.organization_id, -1, -23453, md.sr_instance_id) sr_instance_id,
126
127 -- ASCP global forecast, org leave as -1, not mapped to Org dim
128 -- SNO org=-1 change to Unassigned, mapped to Org dim
129
130 -- ASCP, order type 29(forecast) 77(Part_Demand) may have org=-1
131 -- we need to show such demand qty in order qty measure, but we
132 -- should not include global forecast into item's total demand,
133 -- total indep demand, pab measure.
134
135 decode(md.organization_id, -1, -23453, md.organization_id) organization_id,
136
137 --- we assume that the item must exist in plan's owning inst
138 decode(md.sr_instance_id, -1, l_sr_instance_id, md.sr_instance_id) owning_inst_id,
139 decode(md.organization_id,-1, msc_hub_calendar.get_item_org(p_plan_id, md.inventory_item_id,
140 decode(md.sr_instance_id,-1, l_sr_instance_id, md.sr_instance_id)),
141 md.organization_id) owning_org_id,
142 md.inventory_item_id,
143
144 (case when md.origination_type in (5,6,7,8,9,10,11,12,15,22,27,29,30,81) and sign(md.customer_id)=1
145 then md.customer_id else -23453 end) customer_id,
146 (case when md.origination_type in (5,6,7,8,9,10,11,12,15,22,27,29,30,81) and sign(md.customer_site_id)=1
147 then md.customer_site_id else -23453 end) customer_site_id,
148 (case when md.origination_type in (5,6,7,8,9,10,11,12,15,22,27,29,30,81) and sign(md.zone_id)=1
149 then md.zone_id else -23453 end) region_id,
150
151 nvl(md.project_id,-23453) project_id,
152 nvl(md.task_id, -23453) task_id,
153
154 nvl(md.original_item_id, -23453) original_item_id,
155 -1 * md.origination_type order_type,
156
157 -- drp plan and
158 decode(l_plan_type,5,decode(md.origination_type,
159 3,trunc(nvl(md.old_using_assembly_demand_date,md.using_assembly_demand_date)),
160 24,trunc(nvl(md.old_using_assembly_demand_date,md.using_assembly_demand_date)),
161 trunc(nvl(md.firm_date,md.using_assembly_demand_date))),
162 trunc(nvl(md.firm_date,md.using_assembly_demand_date)) ) order_date,
163
164 nvl(md.demand_class, '-23453') demand_class,
165
166 nvl(md.item_type_value,1) part_condition,
167
168 ---- demand qty
169 --- take care of drp demand
170 --- currently in ASCP, safety_stock demand is excluded in total demand
171 --- but in bug 6688725,we are required to show safety stock(31) demand in order qty measure
172 --- however, we will not include the safety stock demand into total demand of the item
173
174 sum(decode(l_plan_type,5,decode(md.origination_type,3,nvl(md.using_requirement_quantity,0),
175 24,nvl(md.using_requirement_quantity,0),
176 decode(md.assembly_demand_comp_date,null,
177 decode(md.origination_type,29,(nvl(md.probability,1)* md.using_requirement_quantity),
178 nvl(md.firm_quantity, md.using_requirement_quantity)),
179 decode(md.origination_type, 29,(nvl(md.probability,1)* md.daily_demand_rate),
180 md.daily_demand_rate))),
181 decode(md.assembly_demand_comp_date,null,
182 decode(md.origination_type,29,(nvl(md.probability,1)* md.using_requirement_quantity),
183 nvl(md.firm_quantity, md.using_requirement_quantity)),
184 decode(md.origination_type, 29,(nvl(md.probability,1)* md.daily_demand_rate),
185 md.daily_demand_rate)) )
186 ) /
187 decode(nvl(least(sum(decode(md.origination_type,29,nvl(md.probability,0),null)),1),1),
188 0,1,
189 nvl(least(sum(decode(md.origination_type,29,nvl(md.probability,0),null)),1),1)
190 ) demand_qty,
191
192 --------------------------------------------------------------------------------------------------
193 --- the logic for the folliwing code is.
194 --- if it is forecast demand ==> if min(sum(nvl(md.probability,0)),1) ==0, then =1, else min(sum(nvl(md.probability,0)),1)
195 --- for all other demand, it is 1
196
197 /*decode(nvl(least(sum(decode(md.origination_type,29,nvl(md.probability,0),null)),1),1),
198 0,1,
199 nvl(least(sum(decode(md.origination_type,29,nvl(md.probability,0),null)),1),1))
200 */
201
202
203
204 -- take care of forecast demand which has probability
205 --- sum(decode(md.origination_type,31,0,nvl(md.quantity_by_due_date,0))) qty_by_due_date,
206
207 --- safety stock demand is not in total demand, so it is not in qty by due date
208 --- global forecast is not in total demand, so it should not in qty_by_due_date
209 ------------------------------------------------------------------------------------------------------
210 -- bnaghi - some facts are available for drp plans:qty_by_due_date, net_demand,constrained_fcst
211 decode(l_plan_type,5,to_number(null), sum(decode(md.origination_type,31,0,
212 29, decode(md.organization_id,-1,0,nvl(md.quantity_by_due_date,0) * nvl(md.probability,1)),
213 nvl(md.quantity_by_due_date,0) )
214 ) /
215 decode(nvl(least(sum(decode(md.origination_type,29,decode(md.organization_id,-1,0,nvl(md.probability,0)),null)),1),1),
216 0,1,
217 nvl(least(sum(decode(md.origination_type,29,decode(md.organization_id,-1,0,nvl(md.probability,0)),null)),1),1)
218 )) qty_by_due_date,
219
220 decode(l_plan_type,5,to_number(null),sum(decode(md.origination_type, 81, using_requirement_quantity, 0))) net_demand,
221 decode(l_plan_type,5,to_number(null),sum(decode(md.origination_type, 81, quantity_by_due_date, 0))) constrained_fcst,
222
223 ---- indep demand count
224 sum(decode(md.origination_type,
225 5,decode(nvl(md.using_requirement_quantity,0),0,0,1),
226 6,decode(nvl(md.using_requirement_quantity,0),0,0,1),
227 7,decode(nvl(md.using_requirement_quantity,0),0,0,1),
228 8,decode(nvl(nvl(md.firm_quantity, md.using_requirement_quantity),0),0,0,1),
229 9,decode(nvl(md.using_requirement_quantity,0),0,0,1),
230 10,decode(nvl(md.using_requirement_quantity,0),0,0,1),
231 11,decode(nvl(md.using_requirement_quantity,0),0,0,1),
232 12,decode(nvl(md.using_requirement_quantity,0),0,0,1),
233 15,decode(nvl(md.using_requirement_quantity,0),0,0,1),
234 22,decode(nvl(md.using_requirement_quantity,0),0,0,1),
235 27,decode(nvl(md.using_requirement_quantity,0),0,0,1),
236 29,decode(md.organization_id,-1,0,decode((nvl(md.using_requirement_quantity,0)* nvl(md.probability,1)),0,0,1)),
237 30,decode(nvl(md.using_requirement_quantity,0),0,0,1),
238 81,decode(nvl(md.using_requirement_quantity,0),0,0,1),
239 0)) indep_demand_count,
240
241 --- indepedent demand meet on time count
242 decode(l_plan_type,5,to_number(null), sum(decode(md.origination_type,
243 5,decode(nvl(md.quantity_by_due_date,0),0,0,1),
244 6,decode(nvl(md.quantity_by_due_date,0),0,0,1),
245 7,decode(nvl(md.quantity_by_due_date,0),0,0,1),
246 8,decode(nvl(md.quantity_by_due_date,0),0,0,1),
247 9,decode(nvl(md.quantity_by_due_date,0),0,0,1),
248 10,decode(nvl(md.quantity_by_due_date,0),0,0,1),
249 11,decode(nvl(md.quantity_by_due_date,0),0,0,1),
250 12,decode(nvl(md.quantity_by_due_date,0),0,0,1),
251 15,decode(nvl(md.quantity_by_due_date,0),0,0,1),
252 22,decode(nvl(md.quantity_by_due_date,0),0,0,1),
253 27,decode(nvl(md.quantity_by_due_date,0),0,0,1),
254 29,decode(md.organization_id,-1,0,decode((nvl(md.quantity_by_due_date,0)* nvl(md.probability,1)),0,0,1)),
255 30,decode(nvl(md.quantity_by_due_date,0),0,0,1),
256 81,decode(nvl(md.quantity_by_due_date,0),0,0,1),
257 0))) indep_met_ontime_count,
258
259 --- independent demand meet full count
260 decode(l_plan_type,5,to_number(null),sum(decode(nvl(md.using_requirement_quantity,0),0,0,
261 decode(md.origination_type,
262 5,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
263 6,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
264 7,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
265 8,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
266 9,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
267 10,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
268 11,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
269 12,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
270 15,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
271 22,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
272 27,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
273 29,decode(md.organization_id,-1,0,decode(nvl(md.UNMET_QUANTITY,0),0,1,0)),
274 30,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
275 81,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
276 0)))) indep_met_full_count,
277
278
279 --- indepedent demand qty
280 sum(decode(md.assembly_demand_comp_date,null,
281 decode(md.origination_type,
282 29,decode(md.organization_id,-1,0,(nvl(md.probability,1)* md.using_requirement_quantity)),
283 5, md.using_requirement_quantity,
284 6, md.using_requirement_quantity,
285 7, md.using_requirement_quantity,
286 8, nvl(md.firm_quantity, md.using_requirement_quantity),
287 9, md.using_requirement_quantity,
288 10, md.using_requirement_quantity,
289 11, md.using_requirement_quantity,
290 12, md.using_requirement_quantity,
291 15, md.using_requirement_quantity,
292 22, md.using_requirement_quantity,
293 27, md.using_requirement_quantity,
294 30, md.using_requirement_quantity,
295 81, md.using_requirement_quantity,
296 0),
297 decode(md.origination_type,
298 29,decode(md.organization_id,-1,0,(nvl(md.probability,1)*md.daily_demand_rate)),
299 5,md.daily_demand_rate,
300 6, md.daily_demand_rate,
301 7,md.daily_demand_rate,
302 8, md.daily_demand_rate,
303 9,md.daily_demand_rate,
304 10, md.daily_demand_rate,
305 11,md.daily_demand_rate,
306 12, md.daily_demand_rate,
307 22, md.daily_demand_rate,
308 15,md.daily_demand_rate,
309 27,md.daily_demand_rate,
310 30, md.daily_demand_rate,
311 81, md.daily_demand_rate,
312 0))) /
313 decode(nvl(least(sum(decode(md.origination_type,
314 29,decode(md.organization_id,-1,0,nvl(md.probability,0)), null)), 1),1),0,1,
315 nvl(least(sum(decode(md.origination_type,
316 29,decode(md.organization_id,-1,0,nvl(md.probability,0)), null)),1),1)) indep_demand_qty,
317
318
319 --- indep_by_due_date_qty
320 sum(decode(md.origination_type,5,nvl(md.quantity_by_due_date,0),
321 6,nvl(md.quantity_by_due_date,0),
322 7,nvl(md.quantity_by_due_date,0),
323 8,nvl(md.quantity_by_due_date,0),
324 9,nvl(md.quantity_by_due_date,0),
325 10,nvl(md.quantity_by_due_date,0),
326 11,nvl(md.quantity_by_due_date,0),
327 12,nvl(md.quantity_by_due_date,0),
328 15,nvl(md.quantity_by_due_date,0),
329 22,nvl(md.quantity_by_due_date,0),
330 27,nvl(md.quantity_by_due_date,0),
331 29,decode(md.organization_id,-1,0,nvl(md.quantity_by_due_date,0) * nvl(md.probability,1)), -- take care of probability
332 30,nvl(md.quantity_by_due_date,0),
333 81,nvl(md.quantity_by_due_date,0),
334 0)) /
335 decode(nvl(least(sum(decode(md.origination_type,29,decode(md.organization_id,-1,nvl(md.probability,0)),null)),1),1),
336 0,1,
337 nvl(least(sum(decode(md.origination_type,29,decode(md.organization_id,-1,nvl(md.probability,0)),null)),1),1)
338 ) indep_by_due_date_qty,
339
340 --- sales_order_qty
341 decode(l_plan_type,5,to_number(null),sum(decode(md.assembly_demand_comp_date,null,
342 decode(md.origination_type,30,md.using_requirement_quantity,to_number(null)),
343 decode(md.origination_type,30,md.daily_demand_rate,to_number(null)))
344 )) sales_order_qty,
345
346 --- sales order count
347 decode(l_plan_type,5,to_number(null),sum(decode(md.origination_type,30,1,to_number(null)))) sales_order_count,
348
349 --- count of sales order meets require date
350 decode(l_plan_type,5,to_number(null), sum(decode(md.origination_type,30,
351 decode(sign(md.SCHEDULE_SHIP_DATE-md.request_date),-1,1,0),
352 to_number(null)))) sales_order_metr_count,
353
354 --- sales orde meets accept date
355 decode(l_plan_type,5,to_number(null), sum(decode(md.origination_type,30,
356 decode(sign(md.SCHEDULE_SHIP_DATE- md.LATEST_ACCEPTABLE_DATE),-1,1,0),
357 to_number(null)))) sales_order_meta_count,
358
359 --- forecast qty
360 decode(l_plan_type,5,to_number(null), sum(decode(md.assembly_demand_comp_date,null,
361 decode(md.origination_type,29,decode(md.organization_id,-1,0,(nvl(md.probability,1)* md.using_requirement_quantity)),
362 to_number(null)),
363 decode(md.origination_type, 29,decode(md.organization_id,-1,0,(nvl(md.probability,1)* md.daily_demand_rate)),
364 to_number(null)))
365 ) /
366 decode(nvl(least(sum(decode(md.origination_type,29,decode(md.organization_id,-1,0,nvl(md.probability,0)),null)),1),1),
367 0,1,
368 nvl(least(sum(decode(md.origination_type,29,decode(md.organization_id,-1,0,nvl(md.probability,0)),null)),1),1)
369 )) forecast_qty,
370
371 sum(case when l_plan_type in (4,9) then
372 case when md.origination_type in (5,6,7,8,9,10,11,12,15,22,27,30) then nvl(md.old_demand_quantity,0) * nvl(md.probability,1)
373 when md.origination_type in (29) then decode(md.organization_id,-1,0,nvl(md.old_demand_quantity,0) * nvl(md.probability,1))
374 end
375 end) io_delivered_qty,
376
377 decode(l_plan_type,5,to_number(null), sum(case when l_plan_type in (4,9) then
378 case when md.origination_type in (5,6,7,8,9,10,11,12,15,22,27,30) then nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1)
379 when md.origination_type in (29) then decode(md.organization_id,-1,0,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1))
380 end
381 end)) io_required_qty,
382
383 ---- indep demand service_level
384 decode(l_plan_type,5,to_number(null), min(decode(md.origination_type,
385 5,nvl(md.service_level, 50),
386 6,nvl(md.service_level, 50),
387 7,nvl(md.service_level, 50),
388 8,nvl(md.service_level, 50),
389 9,nvl(md.service_level, 50),
390 10,nvl(md.service_level, 50),
391 11,nvl(md.service_level, 50),
392 12,nvl(md.service_level, 50),
393 15,nvl(md.service_level, 50),
394 22,nvl(md.service_level, 50),
395 27,nvl(md.service_level, 50),
396 29,nvl(md.service_level, 50),
397 30,nvl(md.service_level, 50),
398 81,nvl(md.service_level, 50),
399 null))) service_level,
400 decode(l_plan_type,5,to_number(null),avg(md.demand_fulfillment_lead_time)) demand_fulfillment_lead_time,
401
402 fnd_global.user_id, sysdate,
403 sysdate, fnd_global.user_id, fnd_global.login_id,
404 fnd_global.conc_program_id, fnd_global.conc_login_id,
405 fnd_global.prog_appl_id, fnd_global.conc_request_id
406 from msc_demands md
407 where md.plan_id = p_plan_id
408 and not (md.organization_id=-1 and md.origination_type=29) -- avoid double counting global forecast
409 and md.origination_type not in (83) -- 10044668 Unconstrained Demand is not real demand
410 and (p_plan_id <> -1
411 or ( p_plan_id = -1
412 and md.sr_instance_id = l_sr_instance_id
413 and (l_refresh_mode = 1
414 or (l_refresh_mode = 2 and (p_plan_id, md.sr_instance_id, md.organization_id, md.inventory_item_id) in
415 (select number1, number2, number3, number4 from msc_hub_query q where q.query_id = l_item_rn_qid) ) )
416 and md.origination_type <> 29
417 and trunc(nvl(md.firm_date,md.using_assembly_demand_date)) between l_plan_start_date and l_plan_cutoff_date
418 )
419 )
420 group by
421 decode(md.organization_id, -1, -23453, md.sr_instance_id),
422 decode(md.organization_id, -1, -23453, md.organization_id),
423 md.inventory_item_id,
424 nvl(md.original_item_id, -23453),
425 nvl(md.project_id,-23453),
426 nvl(md.task_id, -23453),
427 (case when md.origination_type in (5,6,7,8,9,10,11,12,15,22,27,29,30,81) and sign(md.customer_id)=1
428 then md.customer_id else -23453 end),
429 (case when md.origination_type in (5,6,7,8,9,10,11,12,15,22,27,29,30,81) and sign(md.customer_site_id)=1
430 then md.customer_site_id else -23453 end),
431 (case when md.origination_type in (5,6,7,8,9,10,11,12,15,22,27,29,30,81) and sign(md.zone_id)=1
432 then md.zone_id else -23453 end),
433 nvl(md.demand_class, '-23453'),
434
435 decode(md.organization_id, -1, msc_hub_calendar.get_item_org(p_plan_id, md.inventory_item_id,
436 decode(md.sr_instance_id, -1, l_sr_instance_id, md.sr_instance_id)),
437 md.organization_id),
438
439 decode(md.sr_instance_id, -1, l_sr_instance_id, md.sr_instance_id),
440
441 decode(l_plan_type,5,decode(md.origination_type,
442 3,trunc(nvl(md.old_using_assembly_demand_date,md.using_assembly_demand_date)),
443 24,trunc(nvl(md.old_using_assembly_demand_date,md.using_assembly_demand_date)),
444 trunc(nvl(md.firm_date,md.using_assembly_demand_date))),
445 trunc(nvl(md.firm_date,md.using_assembly_demand_date)) ),
446 -1 * md.origination_type,
447 nvl(md.item_type_value,1);
448
449 msc_phub_util.log('insert into msc_st_demands_f:demands: '||sql%rowcount);
450 commit;
451
452 -- msc_st_demands_f:ods_forecast
453 insert /*+ append nologging */ into msc_st_demands_f (
454 st_transaction_id,
455 error_code,
456 sr_instance_id,
457 organization_id,
458 owning_inst_id,
459 owning_org_id,
460 inventory_item_id,
461 customer_id,
462 customer_site_id,
463 region_id,
464 project_id,
465 task_id,
466 original_item_id,
467 order_type,
468 order_date,
469 demand_class,
470 part_condition,
471
472 demand_qty,
473 indep_demand_count,
474 indep_demand_qty,
475 forecast_qty,
476 demand_fulfillment_lead_time,
477
478 created_by, creation_date,
479 last_update_date, last_updated_by, last_update_login,
480 program_id, program_login_id,
481 program_application_id, request_id)
482 select
483 l_transfer_id,
484 to_number(0),
485 decode(md.organization_id, -1, -23453, md.sr_instance_id) sr_instance_id,
486 decode(md.organization_id, -1, -23453, md.organization_id) organization_id,
487 decode(md.sr_instance_id, -1, l_sr_instance_id, md.sr_instance_id) owning_inst_id,
488 decode(md.organization_id,-1, msc_hub_calendar.get_item_org(p_plan_id, md.inventory_item_id,
489 decode(md.sr_instance_id,-1, l_sr_instance_id, md.sr_instance_id)),
490 md.organization_id) owning_org_id,
491 md.inventory_item_id,
492 decode(sign(md.customer_id), 1, md.customer_id, -23453) customer_id,
493 decode(sign(md.customer_site_id), 1, md.customer_site_id, -23453) customer_site_id,
494 decode(sign(md.zone_id), 1, md.zone_id, -23453) region_id,
495 nvl(md.project_id,-23453) project_id,
496 nvl(md.task_id, -23453) task_id,
497 nvl(md.original_item_id, -23453) original_item_id,
498 -1 * md.origination_type order_type,
499 md.order_date,
500 nvl(md.demand_class, '-23453') demand_class,
501 nvl(md.item_type_value,1) part_condition,
502
503 sum(md.using_requirement_quantity) demand_qty,
504 sum(decode(nvl(md.using_requirement_quantity,0),0,0,1)) indep_demand_count,
505 sum(md.using_requirement_quantity) indep_demand_qty,
506 sum(md.using_requirement_quantity) forecast_qty,
507 avg(md.demand_fulfillment_lead_time) demand_fulfillment_lead_time,
508
509 fnd_global.user_id, sysdate,
510 sysdate, fnd_global.user_id, fnd_global.login_id,
511 fnd_global.conc_program_id, fnd_global.conc_login_id,
512 fnd_global.prog_appl_id, fnd_global.conc_request_id
513 from
514 (select distinct
515 decode(nvl(md2.bucket_type,1), 1, d.calendar_date, 2, d.mfg_week_start_date, d.mfg_period_start_date) order_date,
516 md2.organization_id,
517 md2.sr_instance_id,
518 md2.inventory_item_id,
519 md2.original_item_id,
520 md2.project_id,
521 md2.task_id,
522 md2.customer_id,
523 md2.customer_site_id,
524 md2.zone_id,
525 md2.demand_class,
526 md2.item_type_value,
527 md2.origination_type,
528 md2.probability,
529 md2.using_requirement_quantity,
530 md2.daily_demand_rate,
531 md2.quantity_by_due_date,
532 md2.unmet_quantity,
533 md2.service_level,
534 md2.demand_fulfillment_lead_time,
535 md2.old_demand_quantity
536 from
537 msc_demands md2,
538 (select calendar_date, mfg_week_start_date, mfg_period_start_date
539 from msc_phub_dates_mv
540 where calendar_date between l_plan_start_date and l_plan_cutoff_date
541 and mfg_seq_num is not null) d
542 where md2.plan_id=-1
543 and md2.plan_id=p_plan_id
544 and md2.origination_type=29
545 and decode(nvl(md2.bucket_type,1), 1, d.calendar_date, 2, d.mfg_week_start_date, d.mfg_period_start_date)
546 between greatest(md2.using_assembly_demand_date, l_plan_start_date)
547 and least(nvl(md2.assembly_demand_comp_date, md2.using_assembly_demand_date), l_plan_cutoff_date)
548 and md2.sr_instance_id=l_sr_instance_id
549 and (l_refresh_mode = 1
550 or (l_refresh_mode = 2 and (p_plan_id, md2.sr_instance_id, md2.organization_id, md2.inventory_item_id) in
551 (select number1, number2, number3, number4 from msc_hub_query q where q.query_id = l_item_rn_qid) ) )
552 ) md
553 group by
554 decode(md.organization_id, -1, -23453, md.sr_instance_id),
555 decode(md.organization_id, -1, -23453, md.organization_id),
556 md.inventory_item_id,
557 nvl(md.original_item_id, -23453),
558 nvl(md.project_id,-23453),
559 nvl(md.task_id, -23453),
560 decode(sign(md.customer_id), 1, md.customer_id, -23453),
561 decode(sign(md.customer_site_id), 1, md.customer_site_id, -23453),
562 decode(sign(md.zone_id), 1, md.zone_id, -23453),
563 nvl(md.demand_class, '-23453'),
564
565 decode(md.organization_id, -1, msc_hub_calendar.get_item_org(p_plan_id, md.inventory_item_id,
566 decode(md.sr_instance_id, -1, l_sr_instance_id, md.sr_instance_id)),
567 md.organization_id),
568
569 decode(md.sr_instance_id, -1, l_sr_instance_id, md.sr_instance_id),
570 md.order_date,
571 -1 * md.origination_type,
572 nvl(md.item_type_value,1);
573
574 msc_phub_util.log('insert into msc_st_demands_f:ods_forecast: '||sql%rowcount);
575 commit;
576
577 -- msc_st_demands_f:ods_sales_orders
578 insert /*+ append nologging */ into msc_st_demands_f (
579 st_transaction_id,
580 error_code,
581 sr_instance_id,
582 organization_id,
583 owning_inst_id,
584 owning_org_id,
585 inventory_item_id,
586 customer_id,
587 customer_site_id,
588 region_id,
589 project_id,
590 task_id,
591 original_item_id,
592 order_type,
593 order_date,
594 demand_class,
595 part_condition,
596 demand_qty,
597 indep_demand_count,
598 indep_demand_qty,
599 sales_order_qty,
600 sales_order_count,
601 created_by, creation_date,
602 last_update_date, last_updated_by, last_update_login,
603 program_id, program_login_id,
604 program_application_id, request_id)
605 select
606 l_transfer_id,
607 to_number(0),
608 mso.sr_instance_id,
609 mso.organization_id,
610 mso.sr_instance_id owning_inst_id,
611 mso.organization_id owning_org_id,
612 mso.inventory_item_id,
613 decode(sign(mso.customer_id), 1, mso.customer_id, -23453) customer_id,
614 decode(sign(mso.ship_to_site_use_id), 1, mso.ship_to_site_use_id, -23453) customer_site_id,
615 to_number(-23453) region_id,
616 nvl(mso.project_id,-23453) project_id,
617 nvl(mso.task_id, -23453) task_id,
618 nvl(mso.original_item_id, -23453) original_item_id,
619 to_number(-30) order_type,
620 trunc(mso.requirement_date) order_date,
621 nvl(mso.demand_class, '-23453') demand_class,
622 to_number(1) part_condition,
623
624 sum(mso.primary_uom_quantity) demand_qty,
625 sum(decode(nvl(mso.primary_uom_quantity,0),0,0,1)) indep_demand_count,
626 sum(mso.primary_uom_quantity) indep_demand_qty,
627 sum(mso.primary_uom_quantity) sales_order_qty,
628 sum(decode(nvl(mso.primary_uom_quantity,0),0,0,1)) sales_order_count,
629
630 fnd_global.user_id, sysdate,
631 sysdate, fnd_global.user_id, fnd_global.login_id,
632 fnd_global.conc_program_id, fnd_global.conc_login_id,
633 fnd_global.prog_appl_id, fnd_global.conc_request_id
634 from
635 msc_sales_orders mso
636 where p_plan_id=-1
637 and mso.sr_instance_id = l_sr_instance_id
638 and (l_refresh_mode = 1
639 or (l_refresh_mode = 2 and (p_plan_id, mso.sr_instance_id, mso.organization_id, mso.inventory_item_id) in
640 (select number1, number2, number3, number4 from msc_hub_query q where q.query_id = l_item_rn_qid) ) )
641 and trunc(mso.requirement_date) between l_plan_start_date and l_plan_cutoff_date
642 group by
643 mso.sr_instance_id,
644 mso.organization_id,
645 mso.inventory_item_id,
646 nvl(mso.original_item_id, -23453),
647 nvl(mso.project_id,-23453),
648 nvl(mso.task_id, -23453),
649 decode(sign(mso.customer_id), 1, mso.customer_id, -23453),
650 decode(sign(mso.ship_to_site_use_id), 1, mso.ship_to_site_use_id, -23453),
651 nvl(mso.demand_class, '-23453'),
652 trunc(mso.requirement_date);
653
654 msc_phub_util.log('insert into msc_st_demands_f:ods_sales_orders: '||sql%rowcount);
655 commit;
656
657 -- msc_st_demands_f:exceptions
658 insert /*+ append nologging */ into msc_st_demands_f (
659 st_transaction_id,
660 error_code,
661 sr_instance_id,
662 organization_id,
663 owning_inst_id,
664 owning_org_id,
665 inventory_item_id,
666 customer_id,
667 customer_site_id,
668 region_id,
669 project_id,
670 task_id,
671 original_item_id,
672 order_type,
673 order_date,
674 demand_class,
675 part_condition,
676
677 late_dmd_stf_factor,
678 late_order_count,
679 late_order_qty,
680 service_level,
681 created_by, creation_date,
682 last_update_date, last_updated_by, last_update_login,
683 program_id, program_login_id,
684 program_application_id, request_id)
685 select
686 l_transfer_id,
687 to_number(0),
688 md1.sr_instance_id,
689 md1.organization_id,
690 md1.sr_instance_id owning_inst_id,
691 md1.organization_id owning_org_id,
692 md1.inventory_item_id,
693 decode(sign(md1.customer_id), 1, md1.customer_id, -23453) customer_id,
694 decode(sign(md1.customer_site_id), 1, md1.customer_site_id, -23453) customer_site_id,
695 decode(sign(md1.zone_id), 1, md1.zone_id, -23453) region_id,
696 nvl(md1.project_id,-23453) project_id,
697 nvl(md1.task_id, -23453) task_id,
698 nvl(md1.original_item_id, -23453) original_item_id,
699 -1 * md1.origination_type order_type,
700 trunc(nvl(md1.firm_date,md1.using_assembly_demand_date)) order_date,
701 nvl(md1.demand_class, '-23453') demand_class,
702 nvl(md1.item_type_value,1) part_condition,
703
704 --- late demand satisfaction factor
705 --
706
707 sum(decode(md1.assembly_demand_comp_date,null,
708 decode(md1.origination_type,29,(nvl(md1.probability,1)* md1.using_requirement_quantity),
709 31,0,md1.using_requirement_quantity),
710 decode(md1.origination_type, 29,(nvl(md1.probability,1)* md1.daily_demand_rate),
711 31, 0,md1.daily_demand_rate))
712 * round(decode(med1.exception_type,
713 24, decode(sign(md1.dmd_satisfied_date - md1.using_assembly_demand_date), 0,0,
714 md1.dmd_satisfied_date - md1.using_assembly_demand_date),
715 69, 0, --- only for exception 24 and 26
716 26,decode(sign(md1.dmd_satisfied_date - md1.using_assembly_demand_date), 0,0,
717 md1.dmd_satisfied_date - md1.using_assembly_demand_date),0))
718 )
719 /decode(nvl(least(sum(decode(md1.origination_type,29,nvl(md1.probability,0),null)),1),1),
720 0,1,
721 nvl(least(sum(decode(md1.origination_type,29,nvl(md1.probability,0),null)),1),1))
722
723 - sum( nvl(md1.quantity_by_due_date,0) * nvl(md1.probability,1)
724 * round(decode(med1.exception_type,
725 24, decode(sign(md1.dmd_satisfied_date - md1.using_assembly_demand_date), 0,0,
726 md1.dmd_satisfied_date - md1.using_assembly_demand_date),
727 69, 0, --- only for exception 24 and 26
728 26,decode(sign(md1.dmd_satisfied_date - md1.using_assembly_demand_date), 0,0,
729 md1.dmd_satisfied_date - md1.using_assembly_demand_date),0))
730 )
731 /decode(nvl(least(sum(decode(md1.origination_type,29,nvl(md1.probability,0),null)),1),1),
732 0,1,
733 nvl(least(sum(decode(md1.origination_type,29,nvl(md1.probability,0),null)),1),1)) late_dmd_stf_factor,
734
735
736 --- late demand count
737 sum(decode(med1.exception_type,
738 24,1,
739 26,1,
740 69,1,
741 to_number(null))) late_order_count,-- all demand type
742
743 --- late demand val
744 --- need denominator part for forecast demand qty???
745 --- simply the decode???
746 --- replace std_cost with net selling price
747
748 sum(decode(med1.exception_type,
749 24,
750 decode(md1.assembly_demand_comp_date,null,
751 decode(md1.origination_type,29,(nvl(md1.probability,1)* md1.using_requirement_quantity),
752 31,0,md1.using_requirement_quantity),
753 decode(md1.origination_type,29,(nvl(md1.probability,1)* md1.daily_demand_rate),
754 31, 0,md1.daily_demand_rate)),
755 69,
756 decode(md1.assembly_demand_comp_date,null,
757 decode(md1.origination_type,29,(nvl(md1.probability,1)* md1.using_requirement_quantity),
758 31,0,md1.using_requirement_quantity),
759 decode(md1.origination_type,29,(nvl(md1.probability,1)* md1.daily_demand_rate),
760 31, 0,md1.daily_demand_rate)),
761 26,
762 decode(md1.assembly_demand_comp_date,null,
763 decode(md1.origination_type,29,(nvl(md1.probability,1)* md1.using_requirement_quantity),
764 31,0,md1.using_requirement_quantity),
765 decode(md1.origination_type,29,(nvl(md1.probability,1)* md1.daily_demand_rate),
766 31, 0,md1.daily_demand_rate)),
767 to_number(null)) ) /
768 decode(nvl(least(sum(decode(md1.origination_type,29,nvl(md1.probability,0),
769 null)),1),1),0,1,
770 nvl(least(sum(decode(md1.origination_type,29,nvl(md1.probability,0),null)),1),1)
771 ) late_order_qty,
772 min(nvl(md1.service_level, 50)) service_level,
773
774 fnd_global.user_id, sysdate,
775 sysdate, fnd_global.user_id, fnd_global.login_id,
776 fnd_global.conc_program_id, fnd_global.conc_login_id,
777 fnd_global.prog_appl_id, fnd_global.conc_request_id
778 from msc_demands md1,msc_trading_partners mtp1,
779 msc_exception_details med1
780 where md1.plan_id=med1.plan_id
781 and md1.plan_id=p_plan_id
782 and md1.origination_type in (5,6,7,8,9,10,11,12,15,22,27,29,30) --- only for indep demand
783 and md1.sr_instance_id = med1.sr_instance_id
784 and md1.organization_id =med1.organization_id
785 and md1.inventory_item_id=med1.inventory_item_id
786 and md1.demand_id= MED1.NUMBER1
787 and med1.EXCEPTION_TYPE in (24,26,69)
788 and md1.sr_instance_id = mtp1.sr_instance_id(+)
789 and md1.organization_id = mtp1.sr_tp_id(+)
790 and mtp1.partner_type(+) = 3
791 and l_plan_type not in (5,6)
792 and md1.sr_instance_id<>-1
793 and md1.organization_id<>-1 -- exclude global f/c
794 and p_plan_id <> -1
795 group by
796 md1.sr_instance_id,
797 md1.organization_id,
798 md1.inventory_item_id,
799 nvl(md1.original_item_id, -23453),
800 nvl(md1.project_id,-23453),
801 nvl(md1.task_id, -23453),
802 decode(sign(md1.customer_id), 1, md1.customer_id, -23453),
803 decode(sign(md1.customer_site_id), 1, md1.customer_site_id, -23453),
804 decode(sign(md1.zone_id), 1, md1.zone_id, -23453),
805 nvl(md1.demand_class, '-23453'),
806
807 md1.organization_id,
808 md1.sr_instance_id,
809
810 trunc(nvl(md1.firm_date,md1.using_assembly_demand_date)),
811 -1 * md1.origination_type,
812 nvl(mtp1.currency_code, l_owning_currency_code),
813 nvl(md1.item_type_value,1);
814
815 msc_phub_util.log('insert into msc_st_demands_f:exceptions: '||sql%rowcount);
816 commit;
817
818 -- msc_st_demands_f:constrained_fcst
819 insert /*+ append nologging */ into msc_st_demands_f (
820 st_transaction_id,
821 error_code,
822 sr_instance_id,
823 organization_id,
824 owning_inst_id,
825 owning_org_id,
826 inventory_item_id,
827 customer_id,
828 customer_site_id,
829 region_id,
830 project_id,
831 task_id,
832 original_item_id,
833 order_type,
834 order_date,
835 demand_class,
836 part_condition,
837
838 constrained_fcst,
839
840 created_by, creation_date,
841 last_update_date, last_updated_by, last_update_login,
842 program_id, program_login_id,
843 program_application_id, request_id)
844 select
845 l_transfer_id,
846 to_number(0),
847 t.sr_instance_id,
848 t.organization_id,
849 t.owning_inst_id,
850 t.owning_org_id,
851 t.inventory_item_id,
852 t.customer_id,
853 t.customer_site_id,
854 t.region_id,
855 t.project_id,
856 t.task_id,
857 t.original_item_id,
858 t.order_type,
859 t.order_date,
860 t.demand_class,
861 t.part_condition,
862
863 sum(t.constrained_fcst) constrained_fcst, -- take from MSD_DEM_CONSTRAINED_FORECAST_V
864
865 fnd_global.user_id, sysdate,
866 sysdate, fnd_global.user_id, fnd_global.login_id,
867 fnd_global.conc_program_id, fnd_global.conc_login_id,
868 fnd_global.prog_appl_id, fnd_global.conc_request_id
869 from
870 (select
871 md.sr_instance_id,
872 md.organization_id,
873 nvl(msib.inventory_item_id, msi.inventory_item_id) inventory_item_id,
874 nvl(md.original_item_id, -23453) original_item_id,
875 nvl(md.project_id, -23453) project_id,
876 nvl(md.task_id, -23453) task_id,
877 decode(sign(md.customer_id), 1, md.customer_id, -23453) customer_id,
878 decode(sign(md.customer_site_id), 1, md.customer_site_id, -23453) customer_site_id,
879 decode(sign(md.zone_id), 1, md.zone_id, -23453) region_id,
880 nvl(md.demand_class, '-23453') demand_class,
881 md.organization_id owning_org_id,
882 md.sr_instance_id owning_inst_id,
883 trunc(md.using_assembly_demand_date) order_date,
884 -1 * md.origination_type order_type,
885 nvl(md.item_type_value,1) part_condition,
886 md.quantity_by_due_date constrained_fcst
887 from
888 msc_demands md,
889 msc_system_items msi,
890 msc_system_items msia,
891 msc_system_items msib
892 where md.plan_id = msia.plan_id
893 and md.sr_instance_id = msia.sr_instance_id
894 and md.organization_id = msia.organization_id
895 and md.using_assembly_item_id = msia.inventory_item_id
896 and ((md.inventory_item_id <> md.using_assembly_item_id and msia.bom_item_type = 5)
897 or (md.inventory_item_id = md.using_assembly_item_id))
898 and md.origination_type in (6, 7, 8, 9, 11, 29, 30, 42, 22)
899 and md.plan_id = msi.plan_id
900 and md.sr_instance_id = msi.sr_instance_id
901 and md.organization_id = msi.organization_id
902 and md.inventory_item_id = msi.inventory_item_id
903 and msi.mrp_planning_code <> 6
904 and nvl(md.source_organization_id, -23453) = -23453
905 and nvl(md.quantity_by_due_date, 0) <> 0
906 and decode(nvl(msi.ato_forecast_control, 3), 3, 2, 1) = 1
907 and md.plan_id = msib.plan_id(+)
908 and md.original_inst_id = msib.sr_instance_id(+)
909 and md.original_org_id = msib.organization_id(+)
910 and md.original_item_id = msib.inventory_item_id(+)
911 and msib.mrp_planning_code(+) <> 6
912 and md.plan_id=p_plan_id
913 and p_plan_id <> -1
914 and l_plan_type in (1,101,102,103,105)
915 union all
916 select
917 md.sr_instance_id,
918 md.organization_id,
919 nvl(msib.inventory_item_id, msi.inventory_item_id) inventory_item_id,
920 nvl(md.original_item_id, -23453) original_item_id,
921 nvl(md.project_id, -23453) project_id,
922 nvl(md.task_id, -23453) task_id,
923 decode(sign(md.customer_id), 1, md.customer_id, -23453) customer_id,
924 decode(sign(md.customer_site_id), 1, md.customer_site_id, -23453) customer_site_id,
925 decode(sign(md.zone_id), 1, md.zone_id, -23453) region_id,
926 nvl(md.demand_class, '-23453') demand_class,
927 md.organization_id owning_org_id,
928 md.sr_instance_id owning_inst_id,
929 trunc(md.dmd_satisfied_date) order_date,
930 -1 * md.origination_type order_type,
931 nvl(md.item_type_value,1) part_condition,
932 (md.using_requirement_quantity - nvl(md.quantity_by_due_date, 0)) constrained_fcst
933 from
934 msc_demands md,
935 msc_system_items msi,
936 msc_system_items msia,
937 msc_system_items msib
938 where md.plan_id = msia.plan_id
939 and md.sr_instance_id = msia.sr_instance_id
940 and md.organization_id = msia.organization_id
941 and md.using_assembly_item_id = msia.inventory_item_id
942 and ((md.inventory_item_id <> md.using_assembly_item_id and msia.bom_item_type = 5)
943 or (md.inventory_item_id = md.using_assembly_item_id))
944 and md.origination_type in (6, 7, 8, 9, 11, 29, 30, 42, 22)
945 and md.plan_id = msi.plan_id
946 and md.sr_instance_id = msi.sr_instance_id
947 and md.organization_id = msi.organization_id
948 and md.inventory_item_id = msi.inventory_item_id
949 and msi.mrp_planning_code <> 6
950 and nvl(md.source_organization_id, -23453) = -23453
951 and(md.using_requirement_quantity - nvl(md.quantity_by_due_date, 0) <> 0)
952 and decode(nvl(msi.ato_forecast_control, 3), 3, 2, 1) = 1
953 and md.plan_id = msib.plan_id(+)
954 and md.original_inst_id = msib.sr_instance_id(+)
955 and md.original_org_id = msib.organization_id(+)
956 and md.original_item_id = msib.inventory_item_id(+)
957 and msib.mrp_planning_code(+) <> 6
958 and md.plan_id=p_plan_id
959 and p_plan_id <> -1
960 and l_plan_type in (1,101,102,103,105)
961 and md.dmd_satisfied_date is not null) t,
962 msc_trading_partners mtp1
963 where t.sr_instance_id=mtp1.sr_instance_id(+)
964 and t.organization_id=mtp1.sr_tp_id(+)
965 and mtp1.partner_type(+)=3
966 group by
967 t.sr_instance_id,
968 t.organization_id,
969 t.inventory_item_id,
970 t.original_item_id,
971 t.project_id,
972 t.task_id,
973 t.customer_id,
974 t.customer_site_id,
975 t.region_id,
976 t.demand_class,
977 t.owning_org_id,
978 t.owning_inst_id,
979 t.order_date,
980 t.order_type,
981 nvl(mtp1.currency_code, l_owning_currency_code),
982 t.part_condition;
983
984 msc_phub_util.log('insert into msc_st_demands_f:constrained_fcst: '||sql%rowcount);
985 commit;
986
987 -- msc_st_demands_f:sales_order_sd
988 -- bnaghi : fact not available for DRP plan
989 insert /*+ append nologging */ into msc_st_demands_f (
990 st_transaction_id,
991 error_code,
992 sr_instance_id,
993 organization_id,
994 owning_inst_id,
995 owning_org_id,
996 inventory_item_id,
997 customer_id,
998 customer_site_id,
999 region_id,
1000 project_id,
1001 task_id,
1002 original_item_id,
1003 order_type,
1004 order_date,
1005 demand_class,
1006 part_condition,
1007 sales_order_sd,
1008 created_by, creation_date,
1009 last_update_date, last_updated_by, last_update_login,
1010 program_id, program_login_id,
1011 program_application_id, request_id)
1012 select
1013 l_transfer_id,
1014 to_number(0),
1015 decode(md.organization_id, -1, -23453, md.sr_instance_id) sr_instance_id,
1016 decode(md.organization_id, -1, -23453, md.organization_id) organization_id,
1017 decode(md.sr_instance_id, -1, l_sr_instance_id, md.sr_instance_id) owning_inst_id,
1018 decode(md.organization_id,-1, msc_hub_calendar.get_item_org(p_plan_id, md.inventory_item_id,
1019 decode(md.sr_instance_id,-1, l_sr_instance_id, md.sr_instance_id)),
1020 md.organization_id) owning_org_id,
1021 md.inventory_item_id,
1022 decode(sign(md.customer_id), 1, md.customer_id, -23453) customer_id,
1023 decode(sign(md.customer_site_id), 1, md.customer_site_id, -23453) customer_site_id,
1024 decode(sign(md.zone_id), 1, md.zone_id, -23453) region_id,
1025 nvl(md.project_id,-23453) project_id,
1026 nvl(md.task_id, -23453) task_id,
1027 nvl(md.original_item_id, -23453) original_item_id,
1028 to_number(-23453) order_type,
1029 -- sales_order_sd on schedule_ship_date
1030 trunc(nvl(nvl(md.dmd_satisfied_date, md.schedule_ship_date),
1031 md.using_assembly_demand_date)) order_date,
1032 nvl(md.demand_class, '-23453') demand_class,
1033 nvl(md.item_type_value, 1) part_condition,
1034
1035 sum(decode(md.assembly_demand_comp_date, null,
1036 md.using_requirement_quantity, md.daily_demand_rate)) sales_order_sd,
1037
1038 fnd_global.user_id, sysdate,
1039 sysdate, fnd_global.user_id, fnd_global.login_id,
1040 fnd_global.conc_program_id, fnd_global.conc_login_id,
1041 fnd_global.prog_appl_id, fnd_global.conc_request_id
1042 from msc_demands md
1043 where md.plan_id = p_plan_id
1044 and md.origination_type = 30
1045 and p_plan_id <> -1
1046 and l_plan_type <> 5
1047 group by
1048 decode(md.organization_id, -1, -23453, md.sr_instance_id),
1049 decode(md.organization_id, -1, -23453, md.organization_id),
1050 md.inventory_item_id,
1051 nvl(md.original_item_id, -23453),
1052 nvl(md.project_id,-23453),
1053 nvl(md.task_id, -23453),
1054 decode(sign(md.customer_id), 1, md.customer_id, -23453),
1055 decode(sign(md.customer_site_id), 1, md.customer_site_id, -23453),
1056 decode(sign(md.zone_id), 1, md.zone_id, -23453),
1057 nvl(md.demand_class, '-23453'),
1058 decode(md.organization_id,-1, msc_hub_calendar.get_item_org(p_plan_id, md.inventory_item_id,
1059 decode(md.sr_instance_id,-1, l_sr_instance_id, md.sr_instance_id)),
1060 md.organization_id),
1061 decode(md.sr_instance_id, -1, l_sr_instance_id, md.sr_instance_id),
1062 trunc(nvl(nvl(md.dmd_satisfied_date, md.schedule_ship_date),
1063 md.using_assembly_demand_date)),
1064 nvl(md.item_type_value, 1);
1065
1066 msc_phub_util.log('insert into msc_st_demands_f:sales_order_sd: '||sql%rowcount);
1067 commit;
1068
1069 -- msc_st_demands_f:sales_order_rd
1070 -- bnaghi : fact not available for DRP plan
1071 insert /*+ append nologging */ into msc_st_demands_f (
1072 st_transaction_id,
1073 error_code,
1074 sr_instance_id,
1075 organization_id,
1076 owning_inst_id,
1077 owning_org_id,
1078 inventory_item_id,
1079 customer_id,
1080 customer_site_id,
1081 region_id,
1082 project_id,
1083 task_id,
1084 original_item_id,
1085 order_type,
1086 order_date,
1087 demand_class,
1088 part_condition,
1089
1090 sales_order_rd,
1091 created_by, creation_date,
1092 last_update_date, last_updated_by, last_update_login,
1093 program_id, program_login_id,
1094 program_application_id, request_id)
1095 select
1096 l_transfer_id,
1097 to_number(0),
1098 decode(md.organization_id, -1, -23453, md.sr_instance_id) sr_instance_id,
1099 decode(md.organization_id, -1, -23453, md.organization_id) organization_id,
1100 decode(md.sr_instance_id, -1, l_sr_instance_id, md.sr_instance_id) owning_inst_id,
1101 decode(md.organization_id,-1, msc_hub_calendar.get_item_org(p_plan_id, md.inventory_item_id,
1102 decode(md.sr_instance_id,-1, l_sr_instance_id, md.sr_instance_id)),
1103 md.organization_id) owning_org_id,
1104 md.inventory_item_id,
1105 decode(sign(md.customer_id), 1, md.customer_id, -23453) customer_id,
1106 decode(sign(md.customer_site_id), 1, md.customer_site_id, -23453) customer_site_id,
1107 decode(sign(md.zone_id), 1, md.zone_id, -23453) region_id,
1108 nvl(md.project_id,-23453) project_id,
1109 nvl(md.task_id, -23453) task_id,
1110 nvl(md.original_item_id, -23453) original_item_id,
1111 to_number(-23453) order_type,
1112 -- sales_order_rd on request_ship_date
1113 trunc(nvl(md.request_ship_date, md.using_assembly_demand_date)) order_date,
1114 nvl(md.demand_class, '-23453') demand_class,
1115 nvl(md.item_type_value, 1) part_condition,
1116
1117 sum(decode(md.assembly_demand_comp_date, null,
1118 md.using_requirement_quantity, md.daily_demand_rate)) sales_order_rd,
1119
1120 fnd_global.user_id, sysdate,
1121 sysdate, fnd_global.user_id, fnd_global.login_id,
1122 fnd_global.conc_program_id, fnd_global.conc_login_id,
1123 fnd_global.prog_appl_id, fnd_global.conc_request_id
1124 from msc_demands md
1125 where md.plan_id = p_plan_id
1126 and md.origination_type = 30
1127 and p_plan_id <> -1
1128 and l_plan_type <> 5
1129 group by
1130 decode(md.organization_id, -1, -23453, md.sr_instance_id),
1131 decode(md.organization_id, -1, -23453, md.organization_id),
1132 md.inventory_item_id,
1133 nvl(md.original_item_id, -23453),
1134 nvl(md.project_id,-23453),
1135 nvl(md.task_id, -23453),
1136 decode(sign(md.customer_id), 1, md.customer_id, -23453),
1137 decode(sign(md.customer_site_id), 1, md.customer_site_id, -23453),
1138 decode(sign(md.zone_id), 1, md.zone_id, -23453),
1139 nvl(md.demand_class, '-23453'),
1140 decode(md.organization_id,-1, msc_hub_calendar.get_item_org(p_plan_id, md.inventory_item_id,
1141 decode(md.sr_instance_id,-1, l_sr_instance_id, md.sr_instance_id)),
1142 md.organization_id),
1143 decode(md.sr_instance_id, -1, l_sr_instance_id, md.sr_instance_id),
1144 trunc(nvl(md.request_ship_date, md.using_assembly_demand_date)),
1145 nvl(md.item_type_value, 1);
1146
1147 msc_phub_util.log('insert into msc_st_demands_f:sales_order_rd: '||sql%rowcount);
1148 commit;
1149
1150 -- msc_st_demands_f:sales_order_pd
1151 -- bnaghi : fact not available for DRP plan
1152 insert /*+ append nologging */ into msc_st_demands_f (
1153 st_transaction_id,
1154 error_code,
1155 sr_instance_id,
1156 organization_id,
1157 owning_inst_id,
1158 owning_org_id,
1159 inventory_item_id,
1160 customer_id,
1161 customer_site_id,
1162 region_id,
1163 project_id,
1164 task_id,
1165 original_item_id,
1166 order_type,
1167 order_date,
1168 demand_class,
1169 part_condition,
1170
1171 sales_order_pd,
1172 created_by, creation_date,
1173 last_update_date, last_updated_by, last_update_login,
1174 program_id, program_login_id,
1175 program_application_id, request_id)
1176 select
1177 l_transfer_id,
1178 to_number(0),
1179 decode(md.organization_id, -1, -23453, md.sr_instance_id) sr_instance_id,
1180 decode(md.organization_id, -1, -23453, md.organization_id) organization_id,
1181 decode(md.sr_instance_id, -1, l_sr_instance_id, md.sr_instance_id) owning_inst_id,
1182 decode(md.organization_id,-1, msc_hub_calendar.get_item_org(p_plan_id, md.inventory_item_id,
1183 decode(md.sr_instance_id,-1, l_sr_instance_id, md.sr_instance_id)),
1184 md.organization_id) owning_org_id,
1185 md.inventory_item_id,
1186 decode(sign(md.customer_id), 1, md.customer_id, -23453) customer_id,
1187 decode(sign(md.customer_site_id), 1, md.customer_site_id, -23453) customer_site_id,
1188 decode(sign(md.zone_id), 1, md.zone_id, -23453) region_id,
1189 nvl(md.project_id,-23453) project_id,
1190 nvl(md.task_id, -23453) task_id,
1191 nvl(md.original_item_id, -23453) original_item_id,
1192 to_number(-23453) order_type,
1193 trunc(nvl(md.promise_ship_date, md.using_assembly_demand_date)) order_date,
1194 nvl(md.demand_class, '-23453') demand_class,
1195 nvl(md.item_type_value, 1) part_condition,
1196
1197 -- sales_order_pd on promise_ship_date
1198 sum(decode(md.assembly_demand_comp_date, null,
1199 md.using_requirement_quantity, md.daily_demand_rate)) sales_order_pd,
1200
1201 fnd_global.user_id, sysdate,
1202 sysdate, fnd_global.user_id, fnd_global.login_id,
1203 fnd_global.conc_program_id, fnd_global.conc_login_id,
1204 fnd_global.prog_appl_id, fnd_global.conc_request_id
1205 from msc_demands md
1206 where md.plan_id = p_plan_id
1207 and md.origination_type = 30
1208 and p_plan_id <> -1
1209 and l_plan_type <> 5
1210 group by
1211 decode(md.organization_id, -1, -23453, md.sr_instance_id),
1212 decode(md.organization_id, -1, -23453, md.organization_id),
1213 md.inventory_item_id,
1214 nvl(md.original_item_id, -23453),
1215 nvl(md.project_id,-23453),
1216 nvl(md.task_id, -23453),
1217 decode(sign(md.customer_id), 1, md.customer_id, -23453),
1218 decode(sign(md.customer_site_id), 1, md.customer_site_id, -23453),
1219 decode(sign(md.zone_id), 1, md.zone_id, -23453),
1220 nvl(md.demand_class, '-23453'),
1221 decode(md.organization_id,-1, msc_hub_calendar.get_item_org(p_plan_id, md.inventory_item_id,
1222 decode(md.sr_instance_id,-1, l_sr_instance_id, md.sr_instance_id)),
1223 md.organization_id),
1224 decode(md.sr_instance_id, -1, l_sr_instance_id, md.sr_instance_id),
1225 trunc(nvl(md.promise_ship_date, md.using_assembly_demand_date)),
1226 nvl(md.item_type_value, 1);
1227 msc_phub_util.log('insert into msc_st_demands_f:sales_order_pd: '||sql%rowcount);
1228 commit;
1229
1230 -- msc_demands_f:final
1231 msc_phub_util.gather_table_stats(msc_phub_util.stats_st, 'MSC_ST_DEMANDS_F', l_transfer_id);
1232 insert into msc_demands_f (
1233 plan_id,
1234 plan_run_id,
1235 io_plan_flag,
1236 sr_instance_id,
1237 organization_id,
1238 owning_inst_id,
1239 owning_org_id,
1240 inventory_item_id,
1241 customer_id,
1242 customer_site_id,
1243 region_id,
1244 project_id,
1245 task_id,
1246 original_item_id,
1247 order_type,
1248 order_date,
1249 demand_class,
1250 part_condition,
1251 vmi_flag,
1252 aggr_type,
1253 category_set_id,
1254 sr_category_id,
1255
1256 demand_qty,
1257 qty_by_due_date,
1258 qty_by_due_date_value,
1259 qty_by_due_date_value2,
1260 indep_demand_count,
1261 indep_met_ontime_count,
1262 indep_met_full_count,
1263 indep_demand_qty,
1264 indep_demand_value,
1265 indep_demand_value2,
1266 indep_by_due_date_qty,
1267 sales_order_qty,
1268 sales_order_count,
1269 sales_order_metr_count,
1270 sales_order_meta_count,
1271 forecast_qty,
1272 late_dmd_stf_factor,
1273 late_order_count,
1274 late_order_qty,
1275 late_order_value,
1276 late_order_value2,
1277 io_delivered_qty,
1278 io_required_qty,
1279 net_demand,
1280 constrained_fcst,
1281 constrained_fcst_value,
1282 constrained_fcst_value2,
1283 service_level,
1284 annualized_cogs,
1285 demand_fulfillment_lead_time,
1286 sales_order_sd,
1287 sales_order_sd_value,
1288 sales_order_sd_value2,
1289 sales_order_rd,
1290 sales_order_rd_value,
1291 sales_order_rd_value2,
1292 sales_order_pd,
1293 sales_order_pd_value,
1294 sales_order_pd_value2,
1295 created_by, creation_date,
1296 last_update_date, last_updated_by, last_update_login,
1297 program_id, program_login_id,
1298 program_application_id, request_id)
1299 select
1300 p_plan_id,
1301 p_plan_run_id,
1302 decode(l_plan_type,4,1,9,1,0) io_plan_flag,
1303 f.sr_instance_id,
1304 f.organization_id,
1305 f.owning_inst_id,
1306 f.owning_org_id,
1307 f.inventory_item_id,
1308 f.customer_id,
1309 f.customer_site_id,
1310 f.region_id,
1311 f.project_id,
1312 f.task_id,
1313 f.original_item_id,
1314 f.order_type,
1315 f.order_date,
1316 f.demand_class,
1317 f.part_condition,
1318 f.vmi_flag,
1319 to_number(0) aggr_type,
1320 to_number(-23453) category_set_id,
1321 to_number(-23453) sr_category_id,
1322
1323 f.demand_qty,
1324 f.qty_by_due_date,
1325 f.qty_by_due_date_value,
1326 f.qty_by_due_date_value * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) qty_by_due_date_value2,
1327 f.indep_demand_count,
1328 f.indep_met_ontime_count,
1329 f.indep_met_full_count,
1330 f.indep_demand_qty,
1331 f.indep_demand_value,
1332 f.indep_demand_value * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) indep_demand_value2,
1333 f.indep_by_due_date_qty,
1334 f.sales_order_qty,
1335 f.sales_order_count,
1336 f.sales_order_metr_count,
1337 f.sales_order_meta_count,
1338 f.forecast_qty,
1339 f.late_dmd_stf_factor,
1340 f.late_order_count,
1341 f.late_order_qty,
1342 f.late_order_value,
1343 f.late_order_value * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) late_order_value2,
1344 f.io_delivered_qty,
1345 f.io_required_qty,
1346 f.net_demand,
1347 f.constrained_fcst,
1348 f.constrained_fcst_value,
1349 f.constrained_fcst_value * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) constrained_fcst_value2,
1350 f.service_level,
1351 f.annualized_cogs,
1352 f.demand_fulfillment_lead_time,
1353 f.sales_order_sd,
1354 f.sales_order_sd_value,
1355 f.sales_order_sd_value * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) sales_order_sd_value2,
1356 f.sales_order_rd,
1357 f.sales_order_rd_value,
1358 f.sales_order_rd_value * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) sales_order_rd_value2,
1359 f.sales_order_pd,
1360 f.sales_order_pd_value,
1361 f.sales_order_pd_value * decode(f.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) sales_order_pd_value2,
1362 fnd_global.user_id, sysdate,
1363 sysdate, fnd_global.user_id, fnd_global.login_id,
1364 fnd_global.conc_program_id, fnd_global.conc_login_id,
1365 fnd_global.prog_appl_id, fnd_global.conc_request_id
1366 from
1367 (select /*+ ordered */
1368 f.sr_instance_id,
1369 f.organization_id,
1370 f.owning_inst_id,
1371 f.owning_org_id,
1372 f.inventory_item_id,
1373 f.customer_id,
1374 f.customer_site_id,
1375 f.region_id,
1376 f.project_id,
1377 f.task_id,
1378 f.original_item_id,
1379 f.order_type,
1380 f.order_date,
1381 f.demand_class,
1382 f.part_condition,
1383 nvl(msi.vmi_flag, 0) vmi_flag,
1384 decode(l_plan_type, 6, l_owning_currency_code, nvl(mtp.currency_code, l_owning_currency_code)) currency_code,
1385 sum(f.demand_qty) demand_qty,
1386 sum(f.qty_by_due_date) qty_by_due_date,
1387 sum(f.qty_by_due_date * nvl(msi.list_price,0)*(1-nvl(msi.average_discount,0)/100)) qty_by_due_date_value,
1388 sum(f.indep_demand_count) indep_demand_count,
1389 sum(f.indep_met_ontime_count) indep_met_ontime_count,
1390 sum(f.indep_met_full_count) indep_met_full_count,
1391 sum(f.indep_demand_qty) indep_demand_qty,
1392 sum(f.indep_demand_qty * nvl(msi.list_price,0)*(1-nvl(msi.average_discount,0)/100)) indep_demand_value,
1393 sum(f.indep_by_due_date_qty) indep_by_due_date_qty,
1394 sum(f.sales_order_qty) sales_order_qty,
1395 sum(f.sales_order_count) sales_order_count,
1396 sum(f.sales_order_metr_count) sales_order_metr_count,
1397 sum(f.sales_order_meta_count) sales_order_meta_count,
1398 sum(f.forecast_qty) forecast_qty,
1399 sum(f.late_dmd_stf_factor) late_dmd_stf_factor,
1400 sum(f.late_order_count) late_order_count,
1401 sum(f.late_order_qty) late_order_qty,
1402 sum(f.late_order_qty * nvl(msi.list_price,0)*(1-nvl(msi.average_discount,0)/100)) late_order_value,
1403 sum(f.io_delivered_qty) io_delivered_qty,
1404 sum(f.io_required_qty) io_required_qty,
1405 sum(f.net_demand) net_demand,
1406 sum(f.constrained_fcst) constrained_fcst,
1407 sum(f.constrained_fcst * nvl(msi.list_price,0)*(1-nvl(msi.average_discount,0)/100)) constrained_fcst_value,
1408 min(f.service_level) service_level,
1409 sum(f.indep_demand_qty * nvl(msi.standard_cost,0) * 365 / nvl(l_plan_cutoff_date-l_plan_start_date+1, 365)) annualized_cogs,
1410 sum(f.demand_fulfillment_lead_time) demand_fulfillment_lead_time,
1411 sum(f.sales_order_sd) sales_order_sd,
1412 sum(f.sales_order_sd * nvl(msi.list_price,0)*(1-nvl(msi.average_discount,0)/100)) sales_order_sd_value,
1413 sum(f.sales_order_rd) sales_order_rd,
1414 sum(f.sales_order_rd * nvl(msi.list_price,0)*(1-nvl(msi.average_discount,0)/100)) sales_order_rd_value,
1415 sum(f.sales_order_pd) sales_order_pd,
1416 sum(f.sales_order_pd * nvl(msi.list_price,0)*(1-nvl(msi.average_discount,0)/100)) sales_order_pd_value
1417 from
1418 msc_st_demands_f f,
1419 msc_trading_partners mtp,
1420 msc_apcc_item_d msi
1421 where f.st_transaction_id=l_transfer_id
1422 and mtp.partner_type(+)=3
1423 and f.owning_inst_id=mtp.sr_instance_id(+)
1424 and f.owning_org_id=mtp.sr_tp_id(+)
1425 and msi.plan_id=p_plan_id
1426 and f.owning_inst_id=msi.sr_instance_id
1427 and f.owning_org_id=msi.organization_id
1428 and f.inventory_item_id=msi.inventory_item_id
1429 group by
1430 f.sr_instance_id,
1431 f.organization_id,
1432 f.owning_inst_id,
1433 f.owning_org_id,
1434 f.inventory_item_id,
1435 f.customer_id,
1436 f.customer_site_id,
1437 f.region_id,
1438 f.project_id,
1439 f.task_id,
1440 f.original_item_id,
1441 f.order_type,
1442 f.order_date,
1443 f.demand_class,
1444 f.part_condition,
1445 nvl(msi.vmi_flag, 0),
1446 decode(l_plan_type, 6, l_owning_currency_code, nvl(mtp.currency_code, l_owning_currency_code))
1447 ) f,
1448 msc_currency_conv_mv mcc
1449 where f.currency_code=mcc.from_currency(+)
1450 and fnd_profile.value('MSC_HUB_CUR_CODE_RPT')=mcc.to_currency(+)
1451 and f.order_date=mcc.calendar_date(+);
1452
1453 l_rowcount1 := l_rowcount1 + sql%rowcount;
1454 msc_phub_util.log('insert into msc_demands_f:final: '||sql%rowcount);
1455 commit;
1456
1457 msc_phub_util.gather_table_stats(msc_phub_util.stats_fact, 'MSC_DEMANDS_F', p_plan_run_id);
1458
1459 if (l_enable_num not in (2)) then
1460 msc_phub_util.unusuable_local_index('MSC_DEMANDS_CUM_F', p_plan_run_id, 1);
1461 -- msc_demands_cum_f
1462 insert into msc_demands_cum_f (
1463 plan_id,
1464 plan_run_id,
1465 sr_instance_id,
1466 organization_id,
1467 owning_inst_id,
1468 owning_org_id,
1469 inventory_item_id,
1470 customer_id,
1471 customer_site_id,
1472 region_id,
1473 original_item_id,
1474 vmi_flag,
1475 demand_class,
1476 io_plan_flag,
1477 order_date,
1478 aggr_type, category_set_id, sr_category_id,
1479 cum_constrained_fcst,
1480 cum_constrained_fcst_value,
1481 cum_constrained_fcst_value2,
1482 created_by, creation_date,
1483 last_update_date, last_updated_by, last_update_login,
1484 program_id, program_login_id,
1485 program_application_id, request_id)
1486 select
1487 f.plan_id,
1488 f.plan_run_id,
1489 f.sr_instance_id,
1490 f.organization_id,
1491 f.owning_inst_id,
1492 f.owning_org_id,
1493 f.inventory_item_id,
1494 f.customer_id,
1495 f.customer_site_id,
1496 f.region_id,
1497 f.original_item_id,
1498 f.vmi_flag,
1499 f.demand_class,
1500 f.io_plan_flag,
1501 d.calendar_date,
1502 to_number(0) aggr_type, to_number(-23453), to_number(-23453),
1503 sum(nvl(f.constrained_fcst,0)) cum_constrained_fcst,
1504 sum(nvl(f.constrained_fcst_value,0)) cum_constrained_fcst_value,
1505 sum(nvl(f.constrained_fcst_value2,0)) cum_constrained_fcst_value2,
1506 fnd_global.user_id, sysdate,
1507 sysdate, fnd_global.user_id, fnd_global.login_id,
1508 fnd_global.conc_program_id, fnd_global.conc_login_id,
1509 fnd_global.prog_appl_id, fnd_global.conc_request_id
1510 from
1511 msc_demands_f f,
1512 msc_phub_dates_mv d
1513 where f.plan_id=p_plan_id
1514 and f.plan_run_id=p_plan_run_id
1515 and f.aggr_type=0
1516 and f.constrained_fcst is not null
1517 and d.calendar_date between l_plan_start_date and l_plan_cutoff_date
1518 and d.calendar_date in (d.mfg_week_end_date, d.fis_period_end_date, d.month_end_date, l_plan_cutoff_date)
1519 and d.calendar_date >= f.order_date
1520 group by
1521 f.plan_id,
1522 f.plan_run_id,
1523 f.sr_instance_id,
1524 f.organization_id,
1525 f.owning_inst_id,
1526 f.owning_org_id,
1527 f.inventory_item_id,
1528 f.customer_id,
1529 f.customer_site_id,
1530 f.region_id,
1531 f.original_item_id,
1532 f.vmi_flag,
1533 f.demand_class,
1534 f.io_plan_flag,
1535 d.calendar_date;
1536
1537 l_rowcount2 := l_rowcount2 + sql%rowcount;
1538 msc_phub_util.log('msc_demands_cum_f, insert='||sql%rowcount||', l_rowcount2='||l_rowcount2);
1539 commit;
1540 msc_phub_util.unusuable_local_index('MSC_DEMANDS_CUM_F', p_plan_run_id, 2);
1541 msc_phub_util.gather_table_stats(msc_phub_util.stats_fact, 'MSC_DEMANDS_CUM_F', p_plan_run_id);
1542 end if;
1543
1544 if (l_rowcount1 > 0) then
1545 summarize_demands_f(errbuf, retcode, p_plan_id, p_plan_run_id);
1546 end if;
1547
1548 if (l_rowcount2 > 0) then
1549 summarize_demands_cum_f(errbuf, retcode, p_plan_id, p_plan_run_id);
1550 end if;
1551
1552 msc_phub_util.log('msc_demand_pkg.populate_details.complete, duration='||(systimestamp-l_start_time));
1553
1554 exception
1555 when others then
1556 msc_phub_util.log('msc_demand_pkg.populate_details: '||sqlerrm);
1557 raise;
1558
1559 end populate_details;
1560
1561
1562 procedure summarize_demands_f(errbuf out nocopy varchar2, retcode out nocopy varchar2,
1563 p_plan_id number, p_plan_run_id number)
1564 is
1565 l_category_set_id1 number := fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
1566 begin
1567 msc_phub_util.log('msc_demand_pkg.summarize_demands_f');
1568 retcode := 0;
1569 errbuf := '';
1570
1571 delete from msc_demands_f
1572 where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type>0;
1573 msc_phub_util.log('msc_demand_pkg.summarize_demands_f, delete='||sql%rowcount);
1574 commit;
1575
1576 -- level 1
1577 insert into msc_demands_f (
1578 plan_id, plan_run_id,
1579 io_plan_flag,
1580 sr_instance_id, organization_id, inventory_item_id,
1581 original_item_id,
1582 project_id, task_id,
1583 customer_id, customer_site_id, region_id,
1584 demand_class,
1585 owning_org_id, owning_inst_id,
1586 order_date,
1587 aggr_type, category_set_id, sr_category_id,
1588 order_type, vmi_flag,
1589 part_condition,
1590 demand_qty,
1591 qty_by_due_date,
1592 qty_by_due_date_value,
1593 qty_by_due_date_value2,
1594 net_demand,
1595 constrained_fcst,
1596 constrained_fcst_value,
1597 constrained_fcst_value2,
1598 indep_demand_count,
1599 indep_met_ontime_count,
1600 indep_met_full_count,
1601 indep_demand_value,
1602 indep_demand_value2,
1603 indep_demand_qty,
1604 annualized_cogs,
1605 indep_by_due_date_qty,
1606 sales_order_qty,
1607 sales_order_count,
1608 sales_order_metr_count,
1609 sales_order_meta_count,
1610 sales_order_sd,
1611 sales_order_sd_value,
1612 sales_order_sd_value2,
1613 sales_order_rd,
1614 sales_order_rd_value,
1615 sales_order_rd_value2,
1616 sales_order_pd,
1617 sales_order_pd_value,
1618 sales_order_pd_value2,
1619 forecast_qty,
1620 io_required_qty,
1621 io_delivered_qty,
1622 late_dmd_stf_factor,
1623 late_order_count,
1624 late_order_qty,
1625 late_order_value,
1626 late_order_value2,
1627 service_level,
1628 demand_fulfillment_lead_time,
1629 created_by, creation_date,
1630 last_update_date, last_updated_by, last_update_login,
1631 program_id, program_login_id,
1632 program_application_id, request_id)
1633 -- category (42, 43, 44)
1634 select
1635 f.plan_id, f.plan_run_id,
1636 f.io_plan_flag,
1637 f.sr_instance_id, f.organization_id,
1638 to_number(-23453) inventory_item_id,
1639 f.original_item_id,
1640 f.project_id, f.task_id,
1641 f.customer_id, f.customer_site_id, f.region_id,
1642 f.demand_class,
1643 to_number(-23453) owning_org_id, f.owning_inst_id,
1644 f.order_date,
1645 to_number(42) aggr_type,
1646 l_category_set_id1 category_set_id,
1647 nvl(q.sr_category_id, -23453),
1648 f.order_type, f.vmi_flag,
1649 f.part_condition,
1650 sum(f.demand_qty),
1651 sum(f.qty_by_due_date),
1652 sum(f.qty_by_due_date_value),
1653 sum(f.qty_by_due_date_value2),
1654 sum(f.net_demand),
1655 sum(f.constrained_fcst),
1656 sum(f.constrained_fcst_value),
1657 sum(f.constrained_fcst_value2),
1658 sum(f.indep_demand_count),
1659 sum(f.indep_met_ontime_count),
1660 sum(f.indep_met_full_count),
1661 sum(f.indep_demand_value),
1662 sum(f.indep_demand_value2),
1663 sum(f.indep_demand_qty),
1664 sum(f.annualized_cogs),
1665 sum(f.indep_by_due_date_qty),
1666 sum(f.sales_order_qty),
1667 sum(f.sales_order_count),
1668 sum(f.sales_order_metr_count),
1669 sum(f.sales_order_meta_count),
1670 sum(f.sales_order_sd),
1671 sum(f.sales_order_sd_value),
1672 sum(f.sales_order_sd_value2),
1673 sum(f.sales_order_rd),
1674 sum(f.sales_order_rd_value),
1675 sum(f.sales_order_rd_value2),
1676 sum(f.sales_order_pd),
1677 sum(f.sales_order_pd_value),
1678 sum(f.sales_order_pd_value2),
1679 sum(f.forecast_qty),
1680 sum(f.io_required_qty),
1681 sum(f.io_delivered_qty),
1682 sum(f.late_dmd_stf_factor),
1683 sum(f.late_order_count),
1684 sum(f.late_order_qty),
1685 sum(f.late_order_value),
1686 sum(f.late_order_value2),
1687 min(service_level),
1688 avg(f.demand_fulfillment_lead_time),
1689 fnd_global.user_id, sysdate,
1690 sysdate, fnd_global.user_id, fnd_global.login_id,
1691 fnd_global.conc_program_id, fnd_global.conc_login_id,
1692 fnd_global.prog_appl_id, fnd_global.conc_request_id
1693 from
1694 msc_demands_f f,
1695 msc_phub_item_categories_mv q
1696 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
1697 and f.aggr_type=0
1698 and f.owning_inst_id=q.sr_instance_id(+)
1699 and f.owning_org_id=q.organization_id(+)
1700 and f.inventory_item_id=q.inventory_item_id(+)
1701 and q.category_set_id(+)=l_category_set_id1
1702 group by
1703 f.plan_id, f.plan_run_id,
1704 f.io_plan_flag,
1705 f.sr_instance_id, f.organization_id,
1706 f.original_item_id,
1707 f.project_id, f.task_id,
1708 f.customer_id, f.customer_site_id, f.region_id,
1709 f.demand_class,
1710 f.owning_inst_id,
1711 f.order_date,
1712 nvl(q.sr_category_id, -23453),
1713 f.order_type, f.vmi_flag,
1714 f.part_condition;
1715
1716 msc_phub_util.log('msc_demand_pkg.summarize_demands_f, level1='||sql%rowcount);
1717 commit;
1718
1719 -- level 2
1720 insert into msc_demands_f (
1721 plan_id, plan_run_id, io_plan_flag,
1722 sr_instance_id, organization_id, inventory_item_id,
1723 original_item_id,
1724 project_id, task_id,
1725 customer_id, customer_site_id, region_id,
1726 demand_class, owning_org_id, owning_inst_id, order_date,
1727 aggr_type, category_set_id, sr_category_id,
1728 order_type, vmi_flag,
1729 part_condition,
1730 demand_qty,
1731 qty_by_due_date,
1732 qty_by_due_date_value,
1733 qty_by_due_date_value2,
1734 net_demand,
1735 constrained_fcst,
1736 constrained_fcst_value,
1737 constrained_fcst_value2,
1738 indep_demand_count,
1739 indep_met_ontime_count,
1740 indep_met_full_count,
1741 indep_demand_value,
1742 indep_demand_value2,
1743 indep_demand_qty,
1744 annualized_cogs,
1745 indep_by_due_date_qty,
1746 sales_order_qty,
1747 sales_order_count,
1748 sales_order_metr_count,
1749 sales_order_meta_count,
1750 sales_order_sd,
1751 sales_order_sd_value,
1752 sales_order_sd_value2,
1753 sales_order_rd,
1754 sales_order_rd_value,
1755 sales_order_rd_value2,
1756 sales_order_pd,
1757 sales_order_pd_value,
1758 sales_order_pd_value2,
1759 forecast_qty,
1760 io_required_qty,
1761 io_delivered_qty,
1762 late_dmd_stf_factor,
1763 late_order_count,
1764 late_order_qty,
1765 late_order_value,
1766 late_order_value2,
1767 service_level,
1768 demand_fulfillment_lead_time,
1769 created_by, creation_date,
1770 last_update_date, last_updated_by, last_update_login,
1771 program_id, program_login_id,
1772 program_application_id, request_id)
1773 -- category-mfg_period (1016, 1017, 1018)
1774 select
1775 f.plan_id, f.plan_run_id, f.io_plan_flag,
1776 f.sr_instance_id, f.organization_id, f.inventory_item_id,
1777 f.original_item_id,
1778 f.project_id, f.task_id,
1779 f.customer_id, f.customer_site_id, f.region_id,
1780 f.demand_class, f.owning_org_id, f.owning_inst_id,
1781 d.mfg_period_start_date order_date,
1782 decode(f.aggr_type, 42, 1016, 43, 1017, 1018) aggr_type,
1783 f.category_set_id, f.sr_category_id,
1784 f.order_type, f.vmi_flag,
1785 f.part_condition,
1786 sum(f.demand_qty),
1787 sum(f.qty_by_due_date),
1788 sum(f.qty_by_due_date_value),
1789 sum(f.qty_by_due_date_value2),
1790 sum(f.net_demand),
1791 sum(f.constrained_fcst),
1792 sum(f.constrained_fcst_value),
1793 sum(f.constrained_fcst_value2),
1794 sum(f.indep_demand_count),
1795 sum(f.indep_met_ontime_count),
1796 sum(f.indep_met_full_count),
1797 sum(f.indep_demand_value),
1798 sum(f.indep_demand_value2),
1799 sum(f.indep_demand_qty),
1800 sum(f.annualized_cogs),
1801 sum(f.indep_by_due_date_qty),
1802 sum(f.sales_order_qty),
1803 sum(f.sales_order_count),
1804 sum(f.sales_order_metr_count),
1805 sum(f.sales_order_meta_count),
1806 sum(f.sales_order_sd),
1807 sum(f.sales_order_sd_value),
1808 sum(f.sales_order_sd_value2),
1809 sum(f.sales_order_rd),
1810 sum(f.sales_order_rd_value),
1811 sum(f.sales_order_rd_value2),
1812 sum(f.sales_order_pd),
1813 sum(f.sales_order_pd_value),
1814 sum(f.sales_order_pd_value2),
1815 sum(f.forecast_qty),
1816 sum(f.io_required_qty),
1817 sum(f.io_delivered_qty),
1818 sum(f.late_dmd_stf_factor),
1819 sum(f.late_order_count),
1820 sum(f.late_order_qty),
1821 sum(f.late_order_value),
1822 sum(f.late_order_value2),
1823 min(f.service_level),
1824 avg(f.demand_fulfillment_lead_time),
1825 fnd_global.user_id, sysdate,
1826 sysdate, fnd_global.user_id, fnd_global.login_id,
1827 fnd_global.conc_program_id, fnd_global.conc_login_id,
1828 fnd_global.prog_appl_id, fnd_global.conc_request_id
1829 from
1830 msc_demands_f f,
1831 msc_phub_dates_mv d
1832 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
1833 and f.aggr_type between 42 and 44
1834 and f.order_date = d.calendar_date
1835 and d.mfg_period_start_date is not null
1836 group by
1837 f.plan_id, f.plan_run_id, f.io_plan_flag,
1838 f.sr_instance_id, f.organization_id, f.inventory_item_id,
1839 f.original_item_id,
1840 f.project_id, f.task_id,
1841 f.customer_id, f.customer_site_id, f.region_id,
1842 f.demand_class, f.owning_org_id, f.owning_inst_id,
1843 d.mfg_period_start_date,
1844 decode(f.aggr_type, 42, 1016, 43, 1017, 1018),
1845 f.category_set_id, f.sr_category_id,
1846 f.order_type, f.vmi_flag,
1847 f.part_condition
1848 union all
1849 -- category-fiscal_period (1019, 1020, 1021)
1850 select
1851 f.plan_id, f.plan_run_id, f.io_plan_flag,
1852 f.sr_instance_id, f.organization_id, f.inventory_item_id,
1853 f.original_item_id,
1854 f.project_id, f.task_id,
1855 f.customer_id, f.customer_site_id, f.region_id,
1856 f.demand_class, f.owning_org_id, f.owning_inst_id,
1857 d.fis_period_start_date order_date,
1858 decode(f.aggr_type, 42, 1019, 43, 1020, 1021) aggr_type,
1859 f.category_set_id, f.sr_category_id,
1860 f.order_type, f.vmi_flag,
1861 f.part_condition,
1862 sum(f.demand_qty),
1863 sum(f.qty_by_due_date),
1864 sum(f.qty_by_due_date_value),
1865 sum(f.qty_by_due_date_value2),
1866 sum(f.net_demand),
1867 sum(f.constrained_fcst),
1868 sum(f.constrained_fcst_value),
1869 sum(f.constrained_fcst_value2),
1870 sum(f.indep_demand_count),
1871 sum(f.indep_met_ontime_count),
1872 sum(f.indep_met_full_count),
1873 sum(f.indep_demand_value),
1874 sum(f.indep_demand_value2),
1875 sum(f.indep_demand_qty),
1876 sum(f.annualized_cogs),
1877 sum(f.indep_by_due_date_qty),
1878 sum(f.sales_order_qty),
1879 sum(f.sales_order_count),
1880 sum(f.sales_order_metr_count),
1881 sum(f.sales_order_meta_count),
1882 sum(f.sales_order_sd),
1883 sum(f.sales_order_sd_value),
1884 sum(f.sales_order_sd_value2),
1885 sum(f.sales_order_rd),
1886 sum(f.sales_order_rd_value),
1887 sum(f.sales_order_rd_value2),
1888 sum(f.sales_order_pd),
1889 sum(f.sales_order_pd_value),
1890 sum(f.sales_order_pd_value2),
1891 sum(f.forecast_qty),
1892 sum(f.io_required_qty),
1893 sum(f.io_delivered_qty),
1894 sum(f.late_dmd_stf_factor),
1895 sum(f.late_order_count),
1896 sum(f.late_order_qty),
1897 sum(f.late_order_value),
1898 sum(f.late_order_value2),
1899 min(f.service_level),
1900 avg(f.demand_fulfillment_lead_time),
1901 fnd_global.user_id, sysdate,
1902 sysdate, fnd_global.user_id, fnd_global.login_id,
1903 fnd_global.conc_program_id, fnd_global.conc_login_id,
1904 fnd_global.prog_appl_id, fnd_global.conc_request_id
1905 from
1906 msc_demands_f f,
1907 msc_phub_dates_mv d
1908 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
1909 and f.aggr_type between 42 and 44
1910 and f.order_date = d.calendar_date
1911 and d.fis_period_start_date is not null
1912 group by
1913 f.plan_id, f.plan_run_id, f.io_plan_flag,
1914 f.sr_instance_id, f.organization_id, f.inventory_item_id,
1915 f.original_item_id,
1916 f.project_id, f.task_id,
1917 f.customer_id, f.customer_site_id, f.region_id,
1918 f.demand_class, f.owning_org_id, f.owning_inst_id,
1919 d.fis_period_start_date,
1920 decode(f.aggr_type, 42, 1019, 43, 1020, 1021),
1921 f.category_set_id, f.sr_category_id,
1922 f.order_type, f.vmi_flag,
1923 f.part_condition;
1924
1925 msc_phub_util.log('msc_demand_pkg.summarize_demands_f, level2='||sql%rowcount);
1926 commit;
1927
1928 exception
1929 when others then
1930 retcode := 2;
1931 errbuf := 'msc_demand_pkg.summarize_demands_f: '||sqlerrm;
1932 raise;
1933 end summarize_demands_f;
1934
1935 procedure summarize_demands_cum_f(errbuf out nocopy varchar2, retcode out nocopy varchar2,
1936 p_plan_id number, p_plan_run_id number)
1937 is
1938 l_category_set_id1 number := fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
1939 begin
1940 msc_phub_util.log('msc_demand_pkg.summarize_demands_cum_f');
1941 retcode := 0;
1942 errbuf := '';
1943
1944 delete from msc_demands_cum_f
1945 where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type>0;
1946 msc_phub_util.log('msc_demand_pkg.summarize_demands_cum_f, delete='||sql%rowcount);
1947 commit;
1948
1949 -- level 1
1950 insert into msc_demands_cum_f (
1951 plan_id, plan_run_id,
1952 io_plan_flag,
1953 sr_instance_id, organization_id, inventory_item_id,
1954 original_item_id,
1955 vmi_flag,
1956 customer_id, customer_site_id, region_id,
1957 demand_class,
1958 owning_org_id, owning_inst_id,
1959 order_date,
1960 aggr_type, category_set_id, sr_category_id,
1961 cum_constrained_fcst,
1962 cum_constrained_fcst_value,
1963 cum_constrained_fcst_value2,
1964 created_by, creation_date,
1965 last_update_date, last_updated_by, last_update_login,
1966 program_id, program_login_id,
1967 program_application_id, request_id)
1968 -- category (42, 43, 44)
1969 select
1970 f.plan_id, f.plan_run_id,
1971 f.io_plan_flag,
1972 f.sr_instance_id, f.organization_id,
1973 to_number(-23453) inventory_item_id,
1974 f.original_item_id,
1975 f.vmi_flag,
1976 f.customer_id, f.customer_site_id, f.region_id,
1977 f.demand_class,
1978 to_number(-23453) owning_org_id, f.owning_inst_id,
1979 f.order_date,
1980 to_number(42) aggr_type,
1981 l_category_set_id1 category_set_id,
1982 nvl(q.sr_category_id, -23453),
1983 sum(f.cum_constrained_fcst),
1984 sum(f.cum_constrained_fcst_value),
1985 sum(f.cum_constrained_fcst_value2),
1986 fnd_global.user_id, sysdate,
1987 sysdate, fnd_global.user_id, fnd_global.login_id,
1988 fnd_global.conc_program_id, fnd_global.conc_login_id,
1989 fnd_global.prog_appl_id, fnd_global.conc_request_id
1990 from
1991 msc_demands_cum_f f,
1992 msc_phub_item_categories_mv q
1993 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
1994 and f.aggr_type=0
1995 and f.owning_inst_id=q.sr_instance_id(+)
1996 and f.owning_org_id=q.organization_id(+)
1997 and f.inventory_item_id=q.inventory_item_id(+)
1998 and q.category_set_id(+)=l_category_set_id1
1999 group by
2000 f.plan_id, f.plan_run_id,
2001 f.io_plan_flag,
2002 f.sr_instance_id, f.organization_id,
2003 f.original_item_id,
2004 f.vmi_flag,
2005 f.customer_id, f.customer_site_id, f.region_id,
2006 f.demand_class,
2007 f.owning_inst_id,
2008 f.order_date,
2009 nvl(q.sr_category_id, -23453);
2010
2011 msc_phub_util.log('msc_demand_pkg.summarize_demands_cum_f, level1='||sql%rowcount);
2012 commit;
2013
2014 exception
2015 when others then
2016 retcode := 2;
2017 errbuf := 'msc_demand_pkg.summarize_demands_cum_f: '||sqlerrm;
2018 raise;
2019 end summarize_demands_cum_f;
2020
2021 procedure export_demands_f (
2022 errbuf out nocopy varchar2, retcode out nocopy varchar2,
2023 p_st_transaction_id number, p_plan_run_id number,
2024 p_dblink varchar2, p_source_version varchar2)
2025 is
2026 l_sql varchar2(5000);
2027 l_suffix varchar2(32) := msc_phub_util.suffix(p_dblink);
2028 l_apps_schema varchar2(30) := msc_phub_util.apps_schema;
2029 begin
2030 msc_phub_util.log('msc_demand_pkg.export_demands_f');
2031 retcode := 0;
2032 errbuf := null;
2033
2034 delete from msc_st_demands_f where st_transaction_id=p_st_transaction_id;
2035 commit;
2036
2037 l_sql :=
2038 ' insert into msc_st_demands_f('||
2039 ' st_transaction_id,'||
2040 ' error_code,'||
2041 ' sr_instance_id,'||
2042 ' organization_id,'||
2043 ' owning_inst_id,'||
2044 ' owning_org_id,'||
2045 ' inventory_item_id,'||
2046 ' customer_id,'||
2047 ' customer_site_id,'||
2048 ' region_id,'||
2049 ' project_id,'||
2050 ' task_id,'||
2051 ' organization_code,'||
2052 ' owning_org_code,'||
2053 ' item_name,'||
2054 ' customer_name,'||
2055 ' customer_site_code,'||
2056 ' zone,'||
2057 ' project_number,'||
2058 ' task_number,'||
2059 ' order_type,';
2060 if (p_source_version >= '12.1.3') then l_sql := l_sql||
2061 ' part_condition,'||
2062 ' original_item_id,';
2063 end if;
2064 l_sql := l_sql||
2065 ' order_date,'||
2066 ' vmi_flag,'||
2067 ' demand_class,'||
2068 ' demand_qty,'||
2069 ' qty_by_due_date,'||
2070 ' indep_demand_count,'||
2071 ' indep_met_ontime_count,'||
2072 ' indep_met_full_count,'||
2073 ' indep_demand_value,'||
2074 ' indep_demand_value2,'||
2075 ' indep_demand_qty,'||
2076 ' annualized_cogs,'||
2077 ' indep_by_due_date_qty,'||
2078 ' sales_order_qty,'||
2079 ' sales_order_count,'||
2080 ' sales_order_metr_count,'||
2081 ' sales_order_meta_count,'||
2082 ' forecast_qty,'||
2083 ' late_dmd_stf_factor,'||
2084 ' late_order_count,'||
2085 ' late_order_value,'||
2086 ' late_order_value2,'||
2087 ' qty_by_due_date_value,'||
2088 ' qty_by_due_date_value2,'||
2089 ' io_delivered_qty,'||
2090 ' io_required_qty,'||
2091 ' net_demand,'||
2092 ' constrained_fcst,'||
2093 ' constrained_fcst_value,'||
2094 ' constrained_fcst_value2,'||
2095 ' service_level,';
2096 if (p_source_version >= '12.1.3') then l_sql := l_sql||
2097 ' late_order_qty,'||
2098 ' demand_fulfillment_lead_time,'||
2099 ' sales_order_sd,'||
2100 ' sales_order_sd_value,'||
2101 ' sales_order_sd_value2,'||
2102 ' sales_order_rd,'||
2103 ' sales_order_rd_value,'||
2104 ' sales_order_rd_value2,'||
2105 ' sales_order_pd,'||
2106 ' sales_order_pd_value,'||
2107 ' sales_order_pd_value2,';
2108 end if;
2109 l_sql := l_sql||
2110 ' created_by, creation_date,'||
2111 ' last_updated_by, last_update_date, last_update_login'||
2112 ' )'||
2113 ' select'||
2114 ' :p_st_transaction_id,'||
2115 ' 0,'||
2116 ' f.sr_instance_id,'||
2117 ' f.organization_id,'||
2118 ' f.owning_inst_id,'||
2119 ' f.owning_org_id,'||
2120 ' f.inventory_item_id,'||
2121 ' f.customer_id,'||
2122 ' f.customer_site_id,'||
2123 ' f.region_id,'||
2124 ' f.project_id,'||
2125 ' f.task_id,'||
2126 ' mtp.organization_code,'||
2127 ' mtp2.organization_code,'||
2128 ' mi.item_name,'||
2129 ' decode(f.customer_id, -23453, null, cmv.customer_name),'||
2130 ' decode(f.customer_site_id, -23453, null, cmv.customer_site),'||
2131 ' decode(f.region_id, -23453, null, cmv.zone),'||
2132 ' proj.project_number,'||
2133 ' proj.task_number,'||
2134 ' f.order_type,';
2135 if (p_source_version >= '12.1.3') then l_sql := l_sql||
2136 ' f.part_condition,'||
2137 ' f.original_item_id,';
2138 end if;
2139 l_sql := l_sql||
2140 ' f.order_date,'||
2141 ' f.vmi_flag,'||
2142 ' f.demand_class,'||
2143 ' f.demand_qty,'||
2144 ' f.qty_by_due_date,'||
2145 ' f.indep_demand_count,'||
2146 ' f.indep_met_ontime_count,'||
2147 ' f.indep_met_full_count,'||
2148 ' f.indep_demand_value,'||
2149 ' f.indep_demand_value2,'||
2150 ' f.indep_demand_qty,'||
2151 ' f.annualized_cogs,'||
2152 ' f.indep_by_due_date_qty,'||
2153 ' f.sales_order_qty,'||
2154 ' f.sales_order_count,'||
2155 ' f.sales_order_metr_count,'||
2156 ' f.sales_order_meta_count,'||
2157 ' f.forecast_qty,'||
2158 ' f.late_dmd_stf_factor,'||
2159 ' f.late_order_count,'||
2160 ' f.late_order_value,'||
2161 ' f.late_order_value2,'||
2162 ' f.qty_by_due_date_value,'||
2163 ' f.qty_by_due_date_value2,'||
2164 ' f.io_delivered_qty,'||
2165 ' f.io_required_qty,'||
2166 ' f.net_demand,'||
2167 ' f.constrained_fcst,'||
2168 ' f.constrained_fcst_value,'||
2169 ' f.constrained_fcst_value2,'||
2170 ' f.service_level,';
2171 if (p_source_version >= '12.1.3') then l_sql := l_sql||
2172 ' f.late_order_qty,'||
2173 ' f.demand_fulfillment_lead_time,'||
2174 ' f.sales_order_sd,'||
2175 ' f.sales_order_sd_value,'||
2176 ' f.sales_order_sd_value2,'||
2177 ' f.sales_order_rd,'||
2178 ' f.sales_order_rd_value,'||
2179 ' f.sales_order_rd_value2,'||
2180 ' f.sales_order_pd,'||
2181 ' f.sales_order_pd_value,'||
2182 ' f.sales_order_pd_value2,';
2183 end if;
2184 l_sql := l_sql||
2185 ' fnd_global.user_id, sysdate,'||
2186 ' fnd_global.user_id, sysdate, fnd_global.login_id'||
2187 ' from'||
2188 ' '||l_apps_schema||'.msc_demands_f'||l_suffix||' f,'||
2189 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
2190 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp2,'||
2191 ' '||l_apps_schema||'.msc_items'||l_suffix||' mi,'||
2192 ' '||l_apps_schema||'.msc_phub_customers_mv'||l_suffix||' cmv,';
2193 if (p_source_version >= '12.1.3') then l_sql := l_sql||
2194 ' '||l_apps_schema||'.msc_phub_projects_mv'||l_suffix||' proj';
2195 else l_sql := l_sql||
2196 ' (select p.sr_instance_id, p.organization_id,'||
2197 ' p.project_id, t.task_id, p.project_number, t.task_number'||
2198 ' from '||l_apps_schema||'.msc_projects'||l_suffix||' p, '||l_apps_schema||'.msc_project_tasks'||l_suffix||' t'||
2199 ' where p.project_id=t.project_id'||
2200 ' and p.plan_id=t.plan_id'||
2201 ' and p.sr_instance_id=t.sr_instance_id'||
2202 ' and p.organization_id=t.organization_id'||
2203 ' and p.plan_id=-1) proj';
2204 end if;
2205 l_sql := l_sql||
2206 ' where f.plan_run_id=:p_plan_run_id'||
2207 ' and f.aggr_type=0'||
2208 ' and mtp.partner_type(+)=3'||
2209 ' and mtp.sr_instance_id(+)=f.sr_instance_id'||
2210 ' and mtp.sr_tp_id(+)=f.organization_id'||
2211 ' and mtp2.partner_type(+)=3'||
2212 ' and mtp2.sr_instance_id(+)=f.owning_inst_id'||
2213 ' and mtp2.sr_tp_id(+)=f.owning_org_id'||
2214 ' and mi.inventory_item_id(+)=f.inventory_item_id'||
2215 ' and cmv.customer_id(+)=f.customer_id'||
2216 ' and cmv.customer_site_id(+)=f.customer_site_id'||
2217 ' and cmv.region_id(+)=f.region_id';
2218 if (p_source_version >= '12.1.3') then l_sql := l_sql||
2219 ' and cmv.sr_instance_id(+)=decode(f.region_id,-23453,cmv.sr_instance_id(+),f.owning_inst_id)';
2220 end if;
2221 l_sql := l_sql||
2222 ' and proj.project_id(+)=f.project_id'||
2223 ' and proj.task_id(+)=f.task_id'||
2224 ' and proj.sr_instance_id(+)=f.sr_instance_id'||
2225 ' and proj.organization_id(+)=f.organization_id';
2226
2227 --msc_phub_util.log(l_sql);
2228 execute immediate l_sql using p_st_transaction_id, p_plan_run_id;
2229 commit;
2230 msc_phub_util.log('msc_demand_pkg.export_demands_f: complete, retcode='||retcode);
2231
2232 exception
2233 when others then
2234 retcode := 2;
2235 errbuf := 'msc_demand_pkg.export_demands_f: '||sqlerrm;
2236 msc_phub_util.log(errbuf);
2237 end export_demands_f;
2238
2239 procedure export_demands_cum_f (
2240 errbuf out nocopy varchar2, retcode out nocopy varchar2,
2241 p_st_transaction_id number, p_plan_run_id number,
2242 p_dblink varchar2, p_source_version varchar2)
2243 is
2244 l_sql varchar2(5000);
2245 l_suffix varchar2(32) := msc_phub_util.suffix(p_dblink);
2246 l_apps_schema varchar2(30) := msc_phub_util.apps_schema;
2247 begin
2248 msc_phub_util.log('msc_demand_pkg.export_demands_cum_f');
2249 retcode := 0;
2250 errbuf := null;
2251
2252 delete from msc_st_demands_cum_f where st_transaction_id=p_st_transaction_id;
2253 commit;
2254
2255 l_sql :=
2256 ' insert into msc_st_demands_cum_f('||
2257 ' st_transaction_id,'||
2258 ' error_code,'||
2259 ' sr_instance_id,'||
2260 ' organization_id,'||
2261 ' owning_inst_id,'||
2262 ' owning_org_id,'||
2263 ' inventory_item_id,'||
2264 ' customer_id,'||
2265 ' customer_site_id,'||
2266 ' region_id,'||
2267 ' organization_code,'||
2268 ' owning_org_code,'||
2269 ' item_name,'||
2270 ' customer_name,'||
2271 ' customer_site_code,'||
2272 ' zone,';
2273 if (p_source_version >= '12.1.3') then l_sql := l_sql||
2274 ' original_item_id,';
2275 end if;
2276 l_sql := l_sql||
2277 ' order_date,'||
2278 ' vmi_flag,'||
2279 ' demand_class,'||
2280 ' cum_constrained_fcst,'||
2281 ' cum_constrained_fcst_value,'||
2282 ' cum_constrained_fcst_value2,'||
2283 ' created_by, creation_date,'||
2284 ' last_updated_by, last_update_date, last_update_login'||
2285 ' )'||
2286 ' select'||
2287 ' :p_st_transaction_id,'||
2288 ' 0,'||
2289 ' f.sr_instance_id,'||
2290 ' f.organization_id,'||
2291 ' f.owning_inst_id,'||
2292 ' f.owning_org_id,'||
2293 ' f.inventory_item_id,'||
2294 ' f.customer_id,'||
2295 ' f.customer_site_id,'||
2296 ' f.region_id,'||
2297 ' mtp.organization_code,'||
2298 ' mtp2.organization_code,'||
2299 ' mi.item_name,'||
2300 ' decode(f.customer_id, -23453, null, cmv.customer_name),'||
2301 ' decode(f.customer_site_id, -23453, null, cmv.customer_site),'||
2302 ' decode(f.region_id, -23453, null, cmv.zone),';
2303 if (p_source_version >= '12.1.3') then l_sql := l_sql||
2304 ' f.original_item_id,';
2305 end if;
2306 l_sql := l_sql||
2307 ' f.order_date,'||
2308 ' f.vmi_flag,'||
2309 ' f.demand_class,'||
2310 ' f.cum_constrained_fcst,'||
2311 ' f.cum_constrained_fcst_value,'||
2312 ' f.cum_constrained_fcst_value2,'||
2313 ' fnd_global.user_id, sysdate,'||
2314 ' fnd_global.user_id, sysdate, fnd_global.login_id'||
2315 ' from'||
2316 ' '||l_apps_schema||'.msc_demands_cum_f'||l_suffix||' f,'||
2317 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
2318 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp2,'||
2319 ' '||l_apps_schema||'.msc_items'||l_suffix||' mi,'||
2320 ' '||l_apps_schema||'.msc_phub_customers_mv'||l_suffix||' cmv'||
2321 ' where f.plan_run_id=:p_plan_run_id'||
2322 ' and f.aggr_type=0'||
2323 ' and mtp.partner_type(+)=3'||
2324 ' and mtp.sr_instance_id(+)=f.sr_instance_id'||
2325 ' and mtp.sr_tp_id(+)=f.organization_id'||
2326 ' and mtp2.partner_type(+)=3'||
2327 ' and mtp2.sr_instance_id(+)=f.owning_inst_id'||
2328 ' and mtp2.sr_tp_id(+)=f.owning_org_id'||
2329 ' and mi.inventory_item_id(+)=f.inventory_item_id'||
2330 ' and cmv.customer_id(+)=f.customer_id'||
2331 ' and cmv.customer_site_id(+)=f.customer_site_id'||
2332 ' and cmv.region_id(+)=f.region_id';
2333 if (p_source_version >= '12.1.3') then l_sql := l_sql||
2334 ' and cmv.sr_instance_id(+)=decode(f.region_id,-23453,cmv.sr_instance_id(+),f.owning_inst_id)';
2335 end if;
2336
2337 execute immediate l_sql using p_st_transaction_id, p_plan_run_id;
2338 commit;
2339 msc_phub_util.log('msc_demand_pkg.export_demands_cum_f: complete, retcode='||retcode);
2340
2341 exception
2342 when others then
2343 retcode := 2;
2344 errbuf := 'msc_demand_pkg.export_demands_cum_f: '||sqlerrm;
2345 msc_phub_util.log(errbuf);
2346 end export_demands_cum_f;
2347
2348 procedure import_demands_f (
2349 errbuf out nocopy varchar2, retcode out nocopy varchar2,
2350 p_st_transaction_id number, p_plan_id number, p_plan_run_id number,
2351 p_plan_type number, p_plan_start_date date, p_plan_cutoff_date date,
2352 p_upload_mode number, p_overwrite_after_date date, p_def_instance_code varchar2)
2353 is
2354 l_staging_table varchar2(30) := 'msc_st_demands_f';
2355 l_fact_table varchar2(30) := 'msc_demands_f';
2356 l_result number := 0;
2357 begin
2358 msc_phub_util.log('msc_demand_pkg.import_demands_f');
2359 retcode := 0;
2360 errbuf := null;
2361
2362 l_result := l_result + msc_phub_util.prepare_staging_dates(
2363 l_staging_table, 'order_date', p_st_transaction_id,
2364 p_upload_mode, p_overwrite_after_date,
2365 p_plan_start_date, p_plan_cutoff_date);
2366
2367 l_result := l_result + msc_phub_util.prepare_fact_dates(
2368 l_fact_table, 1, 'order_date', p_plan_id, p_plan_run_id,
2369 p_upload_mode, p_overwrite_after_date);
2370
2371 l_result := l_result + msc_phub_util.decode_organization_key(
2372 l_staging_table, p_st_transaction_id, p_def_instance_code,
2373 'sr_instance_id', 'organization_id', 'organization_code');
2374
2375 l_result := l_result + msc_phub_util.decode_organization_key(
2376 l_staging_table, p_st_transaction_id, p_def_instance_code,
2377 'owning_inst_id', 'owning_org_id', 'owning_org_code');
2378
2379 l_result := l_result + msc_phub_util.decode_item_key(
2380 l_staging_table, p_st_transaction_id, 'inventory_item_id', 'item_name');
2381
2382 l_result := l_result + msc_phub_util.decode_item_key(
2383 l_staging_table, p_st_transaction_id, 'original_item_id', 'original_item_name');
2384
2385 l_result := l_result + msc_phub_util.decode_customer_key(
2386 l_staging_table, p_st_transaction_id,
2387 'customer_id', 'customer_site_id', 'owning_inst_id', 'region_id',
2388 'customer_name', 'customer_site_code', 'zone');
2389
2390 l_result := l_result + msc_phub_util.decode_project_key(
2391 l_staging_table, p_st_transaction_id);
2392
2393 msc_phub_util.log('msc_demand_pkg.import_demands_f: insert into msc_demands_f');
2394 insert into msc_demands_f (
2395 plan_id,
2396 plan_run_id,
2397 sr_instance_id,
2398 organization_id,
2399 owning_inst_id,
2400 owning_org_id,
2401 inventory_item_id,
2402 original_item_id,
2403 customer_id,
2404 customer_site_id,
2405 region_id,
2406 project_id,
2407 task_id,
2408 order_type,
2409 part_condition,
2410 demand_class,
2411 order_date,
2412 io_plan_flag,
2413 vmi_flag,
2414 demand_qty,
2415 qty_by_due_date,
2416 indep_demand_count,
2417 indep_met_ontime_count,
2418 indep_met_full_count,
2419 indep_demand_value,
2420 indep_demand_value2,
2421 indep_demand_qty,
2422 annualized_cogs,
2423 indep_by_due_date_qty,
2424 sales_order_qty,
2425 sales_order_count,
2426 sales_order_metr_count,
2427 sales_order_meta_count,
2428 sales_order_sd,
2429 sales_order_sd_value,
2430 sales_order_sd_value2,
2431 sales_order_rd,
2432 sales_order_rd_value,
2433 sales_order_rd_value2,
2434 sales_order_pd,
2435 sales_order_pd_value,
2436 sales_order_pd_value2,
2437 forecast_qty,
2438 late_dmd_stf_factor,
2439 late_order_count,
2440 late_order_qty,
2441 late_order_value,
2442 late_order_value2,
2443 qty_by_due_date_value,
2444 qty_by_due_date_value2,
2445 io_delivered_qty,
2446 io_required_qty,
2447 net_demand,
2448 constrained_fcst,
2449 constrained_fcst_value,
2450 constrained_fcst_value2,
2451 service_level,
2452 demand_fulfillment_lead_time,
2453 aggr_type, category_set_id, sr_category_id,
2454 created_by, creation_date,
2455 last_updated_by, last_update_date, last_update_login
2456 )
2457 select
2458 p_plan_id,
2459 p_plan_run_id,
2460 nvl(sr_instance_id, -23453),
2461 nvl(organization_id, -23453),
2462 nvl(owning_inst_id, -23453),
2463 nvl(owning_org_id, -23453),
2464 nvl(inventory_item_id, -23453),
2465 -23453 original_item_id,
2466 nvl(customer_id, -23453),
2467 nvl(customer_site_id, -23453),
2468 nvl(region_id, -23453),
2469 nvl(project_id, -23453),
2470 nvl(task_id, -23453),
2471 order_type,
2472 part_condition,
2473 demand_class,
2474 order_date,
2475 decode(p_plan_type, 4, 1, 0) io_plan_flag,
2476 vmi_flag,
2477 demand_qty,
2478 qty_by_due_date,
2479 indep_demand_count,
2480 indep_met_ontime_count,
2481 indep_met_full_count,
2482 indep_demand_value,
2483 indep_demand_value2,
2484 indep_demand_qty,
2485 annualized_cogs,
2486 indep_by_due_date_qty,
2487 sales_order_qty,
2488 sales_order_count,
2489 sales_order_metr_count,
2490 sales_order_meta_count,
2491 sales_order_sd,
2492 sales_order_sd_value,
2493 sales_order_sd_value2,
2494 sales_order_rd,
2495 sales_order_rd_value,
2496 sales_order_rd_value2,
2497 sales_order_pd,
2498 sales_order_pd_value,
2499 sales_order_pd_value2,
2500 forecast_qty,
2501 late_dmd_stf_factor,
2502 late_order_count,
2503 late_order_qty,
2504 late_order_value,
2505 late_order_value2,
2506 qty_by_due_date_value,
2507 qty_by_due_date_value2,
2508 io_delivered_qty,
2509 io_required_qty,
2510 net_demand,
2511 constrained_fcst,
2512 constrained_fcst_value,
2513 constrained_fcst_value2,
2514 service_level,
2515 demand_fulfillment_lead_time,
2516 0, -23453, -23453,
2517 fnd_global.user_id, sysdate,
2518 fnd_global.user_id, sysdate, fnd_global.login_id
2519 from msc_st_demands_f
2520 where st_transaction_id=p_st_transaction_id and error_code=0;
2521
2522 msc_phub_util.log('msc_demand_pkg.import_demands_f: inserted='||sql%rowcount);
2523 commit;
2524
2525 summarize_demands_f(errbuf, retcode, p_plan_id, p_plan_run_id);
2526
2527 if (l_result > 0) then
2528 retcode := -1;
2529 end if;
2530
2531 msc_phub_util.log('msc_demand_pkg.import_demands_f: complete, retcode='||retcode);
2532
2533 exception
2534 when others then
2535 retcode := 2;
2536 errbuf := 'msc_demand_pkg.import_demands_f: '||sqlerrm;
2537 raise;
2538 end import_demands_f;
2539
2540 procedure import_demands_cum_f (
2541 errbuf out nocopy varchar2, retcode out nocopy varchar2,
2542 p_st_transaction_id number, p_plan_id number, p_plan_run_id number,
2543 p_plan_type number, p_plan_start_date date, p_plan_cutoff_date date,
2544 p_upload_mode number, p_overwrite_after_date date, p_def_instance_code varchar2)
2545 is
2546 l_staging_table varchar2(30) := 'msc_st_demands_cum_f';
2547 l_fact_table varchar2(30) := 'msc_demands_cum_f';
2548 l_result number := 0;
2549 begin
2550 msc_phub_util.log('msc_demand_pkg.import_demands_cum_f');
2551 retcode := 0;
2552 errbuf := null;
2553
2554 l_result := l_result + msc_phub_util.prepare_staging_dates(
2555 l_staging_table, 'order_date', p_st_transaction_id,
2556 p_upload_mode, p_overwrite_after_date,
2557 p_plan_start_date, p_plan_cutoff_date);
2558
2559 l_result := l_result + msc_phub_util.prepare_fact_dates(
2560 l_fact_table, 1, 'order_date', p_plan_id, p_plan_run_id,
2561 p_upload_mode, p_overwrite_after_date);
2562
2563 l_result := l_result + msc_phub_util.decode_organization_key(
2564 l_staging_table, p_st_transaction_id, p_def_instance_code,
2565 'sr_instance_id', 'organization_id', 'organization_code');
2566
2567 l_result := l_result + msc_phub_util.decode_organization_key(
2568 l_staging_table, p_st_transaction_id, p_def_instance_code,
2569 'owning_inst_id', 'owning_org_id', 'owning_org_code');
2570
2571 l_result := l_result + msc_phub_util.decode_item_key(
2572 l_staging_table, p_st_transaction_id, 'inventory_item_id', 'item_name');
2573
2574 l_result := l_result + msc_phub_util.decode_item_key(
2575 l_staging_table, p_st_transaction_id, 'original_item_id', 'original_item_name');
2576
2577 l_result := l_result + msc_phub_util.decode_customer_key(
2578 l_staging_table, p_st_transaction_id,
2579 'customer_id', 'customer_site_id', 'owning_inst_id', 'region_id',
2580 'customer_name', 'customer_site_code', 'zone');
2581
2582 msc_phub_util.log('msc_demand_pkg.import_demands_cum_f: insert into msc_demands_cum_f');
2583 insert into msc_demands_cum_f (
2584 plan_id,
2585 plan_run_id,
2586 sr_instance_id,
2587 organization_id,
2588 owning_inst_id,
2589 owning_org_id,
2590 inventory_item_id,
2591 original_item_id,
2592 customer_id,
2593 customer_site_id,
2594 region_id,
2595 demand_class,
2596 order_date,
2597 io_plan_flag,
2598 vmi_flag,
2599 cum_constrained_fcst,
2600 cum_constrained_fcst_value,
2601 cum_constrained_fcst_value2,
2602 aggr_type, category_set_id, sr_category_id,
2603 created_by, creation_date,
2604 last_updated_by, last_update_date, last_update_login
2605 )
2606 select
2607 p_plan_id,
2608 p_plan_run_id,
2609 nvl(sr_instance_id, -23453),
2610 nvl(organization_id, -23453),
2611 nvl(owning_inst_id, -23453),
2612 nvl(owning_org_id, -23453),
2613 nvl(inventory_item_id, -23453),
2614 -23453 original_item_id,
2615 nvl(customer_id, -23453),
2616 nvl(customer_site_id, -23453),
2617 nvl(region_id, -23453),
2618 demand_class,
2619 order_date,
2620 decode(p_plan_type, 4, 1, 0) io_plan_flag,
2621 vmi_flag,
2622 cum_constrained_fcst,
2623 cum_constrained_fcst_value,
2624 cum_constrained_fcst_value2,
2625 0, -23453, -23453,
2626 fnd_global.user_id, sysdate,
2627 fnd_global.user_id, sysdate, fnd_global.login_id
2628 from msc_st_demands_cum_f
2629 where st_transaction_id=p_st_transaction_id and error_code=0;
2630
2631 msc_phub_util.log('msc_demand_pkg.import_demands_cum_f: inserted='||sql%rowcount);
2632 commit;
2633
2634 summarize_demands_cum_f(errbuf, retcode, p_plan_id, p_plan_run_id);
2635
2636 if (l_result > 0) then
2637 retcode := -1;
2638 end if;
2639
2640 msc_phub_util.log('msc_demand_pkg.import_demands_cum_f: complete, retcode='||retcode);
2641
2642 exception
2643 when others then
2644 retcode := 2;
2645 errbuf := 'msc_demand_pkg.import_demands_cum_f: '||sqlerrm;
2646 raise;
2647 end import_demands_cum_f;
2648
2649 end msc_demand_pkg;