[Home] [Help]
PACKAGE BODY: APPS.MSC_DEMAND_PKG
Source
1 package body msc_demand_pkg as
2 /* $Header: MSCHBDEB.pls 120.40.12010000.5 2008/09/03 14:38:50 wexia ship $ */
3
4
5 /*
6 create table msc.msc_demands_cum_f (
7 PLAN_ID number NOT NULL,
8 PLAN_RUN_ID number NOT NULL,
9 SR_INSTANCE_ID number NOT NULL,
10 ORGANIZATION_ID number NOT NULL,
11 INVENTORY_ITEM_ID number NOT NULL,
12 VMI_FLAG number not null,
13 CUSTOMER_ID number NOT NULL,
14 CUSTOMER_SITE_ID number NOT NULL,
15 DEMAND_CLASS number NOT NULL,
16 ORDER_DATE date NOT NULL,
17 backlog_qty number,
18 cum_sales_order_qty number,
19 cum_forecast_qty number);
20
21
22 create unique index msc.msc_demands_cum_f_n1 on msc.msc_demands_cum_f(
23 plan_id,plan_run_id,sr_instance_id,organization_id,inventory_item_id,
24 customer_id,customer_site_id,demand_class,order_date);
25
26
27 */
28
29 procedure populate_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
30 p_plan_id number, p_plan_run_id number default null) is
31
32
33
34 l_api_name varchar2(100);
35 l_stmt_id number ;
36 l_sysdate date;
37 l_user_id number;
38 l_user_login_id number;
39 l_cp_login_id number;
40 l_program_id number;
41 l_appl_id number;
42 l_request_id number;
43
44 l_curr_start_date date;
45 l_curr_cutoff_date date;
46 l_first_bkt_start_date date;
47 l_last_bkt_end_date date;
48
49 l_qid_vmi number;
50 l_qid_last_date number;
51 l_qid_dl_relation number;
52 l_qid_demand_date number;
53 l_qid_demand_item number;
54
55 l_owning_currency_code varchar2(20) := 'XXX';
56
57 begin
58
59
60
61 retcode :=0; -- this means successfully
62 errbuf :='';
63 --Successfully populated msc_demands_f table for plan_id =' || p_plan_id || ',plan_run_id=' || p_plan_run_id;
64 -- initial there is no error message
65 l_api_name := 'msc_demand_f_pkg.populate_details';
66 l_stmt_id :=1;
67
68 l_user_id := fnd_global.user_id;
69 l_sysdate :=sysdate;
70 l_user_login_id :=fnd_global.login_id;
71 l_cp_login_id :=FND_GLOBAL.CONC_LOGIN_ID;
72 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
73 l_appl_id := FND_GLOBAL.PROG_APPL_ID;
74 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
75
76 l_stmt_id :=5;
77 select trunc(mp.curr_start_date),trunc(mp.curr_cutoff_date)
78 into l_curr_start_date,l_curr_cutoff_date
79 from msc_plans mp where mp.plan_id=p_plan_id;
80
81 select min(bkt_start_date), max(bkt_end_date)
82 into l_first_bkt_start_date, l_last_bkt_end_date
83 from msc_plan_buckets
84 where plan_id=p_plan_id;
85
86 -- save owning organization's functional currency
87 begin
88 select o.currency_code
89 into l_owning_currency_code
90 from msc_trading_partners o, msc_plans p
91 where o.sr_instance_id=p.sr_instance_id
92 and o.sr_tp_id=p.organization_id
93 and o.partner_type=3
94 and p.plan_id=p_plan_id;
95 exception
96 when others then
97 null;
98 end;
99
100
101
102 l_stmt_id :=10;
103 ----------------------------------------------------
104
105
106 select msc_hub_query_s.nextval into l_qid_vmi from dual;
107
108 insert into msc_hub_query(
109
110 query_id,
111 last_update_date,
112 last_updated_by,
113 creation_date,
114 created_by,
115 last_update_login,
116 number1, -- plan_id
117 number3, -- sr_instance_id
118 number4, -- organization_id
119 number5, -- inventory_item_id
120 number10 -- vmi flag
121 )
122 select
123 unique l_qid_vmi,l_sysdate,1,l_sysdate,1,1,
124 p_plan_id,
125 msi.sr_instance_id,
126 msi.organization_id,
127 msi.inventory_item_id,
128 nvl(mis.vmi_flag,0)
129 from msc_item_suppliers mis,
130 msc_system_items msi
131 where msi.plan_id = mis.plan_id
132 and msi.sr_instance_id = mis.sr_instance_id
133 and msi.organization_id = mis.organization_id
134 and msi.inventory_item_id = mis.inventory_item_id
135 and msi.plan_id=p_plan_id
136 and nvl(mis.vmi_flag,0)=1;
137
138
139 --dbms_output.put_line('l_stmt_id='|| l_stmt_id||' count=' || SQL%ROWCOUNT);
140 commit;
141
142 --------------------------------------------------------------------
143
144 --- for drp(5), it total demand should not include
145 --- planned order demand (1)
146 --- for work order (3) and Interorganization_Demand(24), it should use old date
147 --
148
149 l_stmt_id :=20;
150 insert into msc_demands_f (
151 created_by,
152 creation_date,
153 last_updated_by,
154 last_update_date,
155 last_update_login,
156 PROGRAM_ID,
157 PROGRAM_LOGIN_ID,
158 PROGRAM_APPLICATION_ID,
159 REQUEST_ID,
160 plan_id,
161 plan_run_id,
162 io_plan_flag,
163 sr_instance_id,
164 organization_id,
165 inventory_item_id,
166 project_id,
167 task_id,
168 customer_id,
169 customer_site_id,
170 region_id,
171 demand_class,
172 owning_org_id,
173 owning_inst_id,
174 order_date,
175 aggr_type, category_set_id, sr_category_id,
176 order_type,
177 vmi_flag,
178 demand_qty,
179 qty_by_due_date,
180 net_demand,
181 constrained_fcst,
182 constrained_fcst_value,
183 constrained_fcst_value2,
184 indep_demand_count,
185 indep_met_ontime_count,
186 indep_met_full_count,
187 indep_demand_value,
188 indep_demand_value2,
189 indep_demand_qty,
190 indep_by_due_date_qty,
191 Sales_order_qty,
192 Sales_order_count,
193 Sales_order_metr_count,
194 Sales_order_meta_count,
195 Forecast_qty,
196 --qty_by_due_date_with_p,
197 IO_REQUIRED_QTY,
198 IO_DELIVERED_QTY,
199 late_dmd_stf_factor,
200 late_order_count,
201 late_order_value,
202 late_order_value2,
203 service_level)
204 select
205 l_user_id,
206 l_sysdate,
207 l_user_id,
208 l_sysdate,
209 l_user_login_id,
210 l_program_id,
211 l_cp_login_id,
212 l_appl_id,
213 l_request_id,
214
215 ------------------------------------------------------
216 p_plan_id,
217 p_plan_run_id,
218 decode(mp_tbl.plan_type,4,1,9,1,0) io_plan_flag,
219 demand_tbl.sr_instance_id,
220 demand_tbl.organization_id,
221 demand_tbl.inventory_item_id,
222 demand_tbl.project_id,
223 demand_tbl.task_id,
224 --- we should not populate customer_id/customer_site_id
225 --- at all for dependent demand.
226 --- we do not want show dependent demand in customer dimension
227 --- bug 6797611
228 decode(demand_tbl.order_type,-5,cmv.customer_id,
229 -6,cmv.customer_id,
230 -7,cmv.customer_id,
231 -8,cmv.customer_id,
232 -9,cmv.customer_id,
233 -10,cmv.customer_id,
234 -11,cmv.customer_id,
235 -12,cmv.customer_id,
236 -15,cmv.customer_id,
237 -22,cmv.customer_id,
238 -24,cmv.customer_id,
239 -27,cmv.customer_id,
240 -29,cmv.customer_id,
241 -30,cmv.customer_id,
242 -81,cmv.customer_id,
243 -23453),
244
245 decode(demand_tbl.order_type,-5,cmv.customer_site_id,
246 -6,cmv.customer_site_id,
247 -7,cmv.customer_site_id,
248 -8,cmv.customer_site_id,
249 -9,cmv.customer_site_id,
250 -10,cmv.customer_site_id,
251 -11,cmv.customer_site_id,
252 -12,cmv.customer_site_id,
253 -15,cmv.customer_site_id,
254 -22,cmv.customer_site_id,
255 -24,cmv.customer_site_id,
256 -27,cmv.customer_site_id,
257 -29,cmv.customer_site_id,
258 -30,cmv.customer_site_id,
259 -81,cmv.customer_site_id,
260 -23453),
261 cmv.region_id,
262 demand_tbl.demand_class,
263 demand_tbl.owning_org_id,
264 demand_tbl.owning_inst_id,
265 ----- we an not just put it in curr_start_date
266 ----- need to put it in last working day of the bucket where plan start date in
267 decode(sign(to_number(demand_tbl.order_date-l_curr_start_date)),-1,
268 msc_hub_calendar.last_work_date(p_plan_id,l_curr_start_date),
269 demand_tbl.order_date),
270 to_number(0) aggr_type,
271 to_number(-23453) category_set_id,
272 to_number(-23453) sr_category_id,
273 demand_tbl.order_type,
274 demand_tbl.vmi_flag,
275 sum(demand_tbl.demand_qty),
276 sum(demand_tbl.qty_by_due_date),
277 sum(demand_tbl.net_demand),
278 sum(demand_tbl.constrained_fcst),
279 sum(demand_tbl.constrained_fcst_value),
280 sum(demand_tbl.constrained_fcst_value * decode(demand_tbl.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))) constrained_fcst_value2,
281 sum(demand_tbl.indep_demand_count),
282 sum(demand_tbl.indep_met_ontime_count),
283 sum(demand_tbl.indep_met_full_count),
284 sum(demand_tbl.indep_demand_value),
285 sum(demand_tbl.indep_demand_value * decode(demand_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
286 nvl(mcc.CONV_RATE,0))), -- rate converation
287 sum(demand_tbl.indep_demand_qty),
288 sum(demand_tbl.indep_by_due_date_qty),
289 sum(demand_tbl.sales_order_qty),
290 sum(demand_tbl.sales_order_count),
291 sum(demand_tbl.sales_order_metr_count),
292 sum(demand_tbl.sales_order_meta_count),
293 sum(demand_tbl.forecast_qty),
294 sum(demand_tbl.io_required_qty),
295 sum(demand_tbl.io_delivered_qty),
296 sum(demand_tbl.late_dmd_stf_factor),
297 sum(demand_tbl.late_order_count),
298 sum(demand_tbl.late_order_value),
299 sum(demand_tbl.late_order_value * decode(demand_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
300 nvl(mcc.CONV_RATE,0))), --- rate
301 min(demand_tbl.service_level)
302
303 from(
304 select
305 -- sync sr_instance_id with organization_id
306 -- this is important since in org dimension,
307 -- it only has (inst,org)=(-23543,-23453)
308
309 decode(md.organization_id, -1, -23453, md.sr_instance_id) sr_instance_id,
310
311 -- ASCP global forecast, org leave as -1, not mapped to Org dim
312 -- SNO org=-1 change to Unassigned, mapped to Org dim
313
314 -- ASCP, order type 29(forecast) 77(Part_Demand) may have org=-1
315 -- we need to show such demand qty in order qty measure, but we
316 -- should not include global forecast into item's total demand,
317 -- total indep demand, pab measure.
318
319 decode(md.organization_id, -1, -23453, md.organization_id) organization_id,
320
321 md.inventory_item_id,
322 nvl(md.project_id,-23453) project_id,
323 nvl(md.task_id, -23453) task_id,
324 nvl(md.customer_id,-23453) customer_id,
325 nvl(md.customer_site_id, -23453) customer_site_id,
326
327 nvl(md.zone_id, -23453) region_id,
328 nvl(md.demand_class, -23453) demand_class,
329
330 decode(md.organization_id,-1, msc_hub_calendar.get_item_org(p_plan_id, md.inventory_item_id,
331 decode(md.sr_instance_id,-1, mp.sr_instance_id, md.sr_instance_id)),
332 md.organization_id) owning_org_id,
333
334 --- we assume that the item must exist in plan's owning inst
335 decode(md.sr_instance_id, -1, mp.sr_instance_id, md.sr_instance_id) owning_inst_id,
336
337 -- drp plan and
338 decode(mp.plan_type,5,decode(md.origination_type,
339 3,trunc(nvl(md.old_using_assembly_demand_date,md.using_assembly_demand_date)),
340 24,trunc(nvl(md.old_using_assembly_demand_date,md.using_assembly_demand_date)),
341 trunc(nvl(md.firm_date,md.using_assembly_demand_date))),
342 trunc(nvl(md.firm_date,md.using_assembly_demand_date)) ) order_date,
343
344 -1 * md.origination_type order_Type,
345 msi.vmi_flag,
346 nvl(mtp.currency_code, l_owning_currency_code) currency_code,
347
348 ---- demand qty
349 --- take care of drp demand
350 --- currently in ASCP, safety_stock demand is excluded in total demand
351 --- but in bug 6688725,we are required to show safety stock(31) demand in order qty measure
352 --- however, we will not include the safety stock demand into total demand of the item
353
354 sum(decode(mp.plan_type,5,decode(md.origination_type,3,nvl(md.using_requirement_quantity,0),
355 24,nvl(md.using_requirement_quantity,0),
359 decode(md.origination_type, 29,(nvl(md.probability,1)* md.daily_demand_rate),
356 decode(md.assembly_demand_comp_date,null,
357 decode(md.origination_type,29,(nvl(md.probability,1)* md.using_requirement_quantity),
358 md.using_requirement_quantity),
360 md.daily_demand_rate))),
361 decode(md.assembly_demand_comp_date,null,
362 decode(md.origination_type,29,(nvl(md.probability,1)* md.using_requirement_quantity),
363 md.using_requirement_quantity),
364 decode(md.origination_type, 29,(nvl(md.probability,1)* md.daily_demand_rate),
365 md.daily_demand_rate)) )
366 ) /
367 decode(nvl(least(sum(decode(md.origination_type,29,nvl(md.probability,0),null)),1),1),
368 0,1,
369 nvl(least(sum(decode(md.origination_type,29,nvl(md.probability,0),null)),1),1)
370 ) demand_qty,
371
372
373 --------------------------------------------------------------------------------------------------
374 --- the logic for the folliwing code is.
375 --- if it is forecast demand ==> if min(sum(nvl(md.probability,0)),1) ==0, then =1, else min(sum(nvl(md.probability,0)),1)
376 --- for all other demand, it is 1
377
378 /*decode(nvl(least(sum(decode(md.origination_type,29,nvl(md.probability,0),null)),1),1),
379 0,1,
380 nvl(least(sum(decode(md.origination_type,29,nvl(md.probability,0),null)),1),1))
381 */
382
383
384
385 -- take care of forecast demand which has probability
386 --- sum(decode(md.origination_type,31,0,nvl(md.quantity_by_due_date,0))) qty_by_due_date,
387
388 --- safety stock demand is not in total demand, so it is not in qty by due date
389 --- global forecast is not in total demand, so it should not in qty_by_due_date
390 ------------------------------------------------------------------------------------------------------
391 sum(decode(md.origination_type,31,0,
392 29, decode(md.organization_id,-1,0,nvl(md.quantity_by_due_date,0) * nvl(md.probability,1)),
393 nvl(md.quantity_by_due_date,0) )
394 ) /
395 decode(nvl(least(sum(decode(md.origination_type,29,decode(md.organization_id,-1,0,nvl(md.probability,0)),null)),1),1),
396 0,1,
397 nvl(least(sum(decode(md.origination_type,29,decode(md.organization_id,-1,0,nvl(md.probability,0)),null)),1),1)
398 ) qty_by_due_date,
399
400 sum(decode(md.origination_type, 81, using_requirement_quantity, 0)) net_demand,
401 sum(decode(md.origination_type, 81, quantity_by_due_date, 0)) constrained_fcst,
402 sum(decode(md.origination_type, 81, quantity_by_due_date * (nvl(msi.list_price,0) * (1- nvl(msi.average_discount,0)/100)), 0)) constrained_fcst_value,
403
404 ---- indep demand count
405 sum(decode(md.origination_type,
406 5,decode(nvl(md.using_requirement_quantity,0),0,0,1),
407 6,decode(nvl(md.using_requirement_quantity,0),0,0,1),
408 7,decode(nvl(md.using_requirement_quantity,0),0,0,1),
409 8,decode(nvl(md.using_requirement_quantity,0),0,0,1),
410 9,decode(nvl(md.using_requirement_quantity,0),0,0,1),
411 10,decode(nvl(md.using_requirement_quantity,0),0,0,1),
412 11,decode(nvl(md.using_requirement_quantity,0),0,0,1),
413 12,decode(nvl(md.using_requirement_quantity,0),0,0,1),
414 15,decode(nvl(md.using_requirement_quantity,0),0,0,1),
415 22,decode(nvl(md.using_requirement_quantity,0),0,0,1),
416 24,decode(nvl(md.using_requirement_quantity,0),0,0,1),
417 27,decode(nvl(md.using_requirement_quantity,0),0,0,1),
418 29,decode(md.organization_id,-1,0,decode((nvl(md.using_requirement_quantity,0)* nvl(md.probability,1)),0,0,1)),
419 30,decode(nvl(md.using_requirement_quantity,0),0,0,1),
420 81,decode(nvl(md.using_requirement_quantity,0),0,0,1),
421 0)) indep_demand_count,
422
423 --- indepedent demand meet on time count
424 sum(decode(md.origination_type,
425 5,decode(nvl(md.quantity_by_due_date,0),0,0,1),
426 6,decode(nvl(md.quantity_by_due_date,0),0,0,1),
427 7,decode(nvl(md.quantity_by_due_date,0),0,0,1),
428 8,decode(nvl(md.quantity_by_due_date,0),0,0,1),
429 9,decode(nvl(md.quantity_by_due_date,0),0,0,1),
430 10,decode(nvl(md.quantity_by_due_date,0),0,0,1),
431 11,decode(nvl(md.quantity_by_due_date,0),0,0,1),
432 12,decode(nvl(md.quantity_by_due_date,0),0,0,1),
433 15,decode(nvl(md.quantity_by_due_date,0),0,0,1),
434 22,decode(nvl(md.quantity_by_due_date,0),0,0,1),
435 24,decode(nvl(md.quantity_by_due_date,0),0,0,1),
436 27,decode(nvl(md.quantity_by_due_date,0),0,0,1),
437 29,decode(md.organization_id,-1,0,decode((nvl(md.quantity_by_due_date,0)* nvl(md.probability,1)),0,0,1)),
438 30,decode(nvl(md.quantity_by_due_date,0),0,0,1),
442 --- independent demand meet full count
439 81,decode(nvl(md.quantity_by_due_date,0),0,0,1),
440 0)) indep_met_ontime_count,
441
443 sum(decode(nvl(md.using_requirement_quantity,0),0,0,
444 decode(md.origination_type,
445 5,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
446 6,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
447 7,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
448 8,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
449 9,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
450 10,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
451 11,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
452 12,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
453 15,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
454 22,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
455 24,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
456 27,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
457 29,decode(md.organization_id,-1,0,decode(nvl(md.UNMET_QUANTITY,0),0,1,0)),
458 30,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
459 81,decode(nvl(md.UNMET_QUANTITY,0),0,1,0),
460 0))) indep_met_full_count,
461
462
463 ---- indepndent demand value
464 --- using net selling price to replace std_cost
465 sum(decode(md.assembly_demand_comp_date,null,
466 decode(md.origination_type,
467 29,decode(md.organization_id,-1,0,
468 (nvl(md.probability,1)* md.using_requirement_quantity) * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100))),
469 5, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
470 6, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
471 7, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
472 8, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
473 9, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
474 10, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
475 11, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
476 12, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
477 15, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
478 22, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
479 24, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
480 27, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
481 30, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
482 81, md.using_requirement_quantity * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
483 0),
484 decode(md.origination_type,
485 29,decode(md.organization_id,-1,0,
486 (nvl(md.probability,1)*md.daily_demand_rate) * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100))),
487 5,md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
488 6, md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
489 7,md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
490 8, md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
491 9,md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
492 10, md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
493 11,md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
494 12, md.daily_demand_rate *(nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
495 22, md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
496 15,md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
497 24, md.daily_demand_rate *(nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
498 27,md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
499 30, md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
500 81, md.daily_demand_rate * (nvl(msi.list_price,0) * (1- nvl(msi.AVERAGE_DISCOUNT,0)/100)),
501 0))) /
502 decode(nvl(least(sum(decode(md.origination_type,
503 29,decode(md.organization_id,-1,0,nvl(md.probability,0)), null)), 1),1),0,1,
504 nvl(least(sum(decode(md.origination_type,
505 29,decode(md.organization_id,-1,0,nvl(md.probability,0)), null)),1),1)) indep_demand_value,
506
507 --- indepedent demand qty
508 sum(decode(md.assembly_demand_comp_date,null,
509 decode(md.origination_type,
513 7, md.using_requirement_quantity,
510 29,decode(md.organization_id,-1,0,(nvl(md.probability,1)* md.using_requirement_quantity)),
511 5, md.using_requirement_quantity,
512 6, md.using_requirement_quantity,
514 8, md.using_requirement_quantity,
515 9, md.using_requirement_quantity,
516 10, md.using_requirement_quantity,
517 11, md.using_requirement_quantity,
518 12, md.using_requirement_quantity,
519 15, md.using_requirement_quantity,
520 22, md.using_requirement_quantity,
521 24, md.using_requirement_quantity,
522 27, md.using_requirement_quantity,
523 30, md.using_requirement_quantity,
524 81, md.using_requirement_quantity,
525 0),
526 decode(md.origination_type,
527 29,decode(md.organization_id,-1,0,(nvl(md.probability,1)*md.daily_demand_rate)),
528 5,md.daily_demand_rate,
529 6, md.daily_demand_rate,
530 7,md.daily_demand_rate,
531 8, md.daily_demand_rate,
532 9,md.daily_demand_rate,
533 10, md.daily_demand_rate,
534 11,md.daily_demand_rate,
535 12, md.daily_demand_rate,
536 22, md.daily_demand_rate,
537 15,md.daily_demand_rate,
538 24, md.daily_demand_rate,
539 27,md.daily_demand_rate,
540 30, md.daily_demand_rate,
541 81, md.daily_demand_rate,
542 0))) /
543 decode(nvl(least(sum(decode(md.origination_type,
544 29,decode(md.organization_id,-1,0,nvl(md.probability,0)), null)), 1),1),0,1,
545 nvl(least(sum(decode(md.origination_type,
546 29,decode(md.organization_id,-1,0,nvl(md.probability,0)), null)),1),1)) indep_demand_qty,
547
548
549 --- indep_by_due_date_qty
550 sum(decode(md.origination_type,5,nvl(md.quantity_by_due_date,0),
551 6,nvl(md.quantity_by_due_date,0),
552 7,nvl(md.quantity_by_due_date,0),
553 8,nvl(md.quantity_by_due_date,0),
554 9,nvl(md.quantity_by_due_date,0),
555 10,nvl(md.quantity_by_due_date,0),
556 11,nvl(md.quantity_by_due_date,0),
557 12,nvl(md.quantity_by_due_date,0),
558 15,nvl(md.quantity_by_due_date,0),
559 22,nvl(md.quantity_by_due_date,0),
560 24,nvl(md.quantity_by_due_date,0),
561 27,nvl(md.quantity_by_due_date,0),
562 29,decode(md.organization_id,-1,0,nvl(md.quantity_by_due_date,0) * nvl(md.probability,1)), -- take care of probability
563 30,nvl(md.quantity_by_due_date,0),
564 81,nvl(md.quantity_by_due_date,0),
565 0)) /
566 decode(nvl(least(sum(decode(md.origination_type,29,decode(md.organization_id,-1,nvl(md.probability,0)),null)),1),1),
567 0,1,
568 nvl(least(sum(decode(md.origination_type,29,decode(md.organization_id,-1,nvl(md.probability,0)),null)),1),1)
569 ) indep_by_due_date_qty,
570
571 --- sales order qty
572 sum(decode(md.assembly_demand_comp_date,null,
573 decode(md.origination_type,30,md.using_requirement_quantity,to_number(null)),
574 decode(md.origination_type,30,md.daily_demand_rate,to_number(null)))
575 ) sales_order_qty,
576
577 --- sales order count
578 sum(decode(md.origination_type,30,1,to_number(null))) sales_order_count,
579
580 --- count of sales order meets require date
581 sum(decode(md.origination_type,30,
582 decode(sign(md.SCHEDULE_SHIP_DATE-md.request_date),-1,1,0),
583 to_number(null))) sales_order_metr_count,
584
585 --- sales orde meets accept date
586 sum(decode(md.origination_type,30,
587 decode(sign(md.SCHEDULE_SHIP_DATE- md.LATEST_ACCEPTABLE_DATE),-1,1,0),
588 to_number(null))) sales_order_meta_count,
589
590 --- forecast qty
591 sum(decode(md.assembly_demand_comp_date,null,
592 decode(md.origination_type,29,decode(md.organization_id,-1,0,(nvl(md.probability,1)* md.using_requirement_quantity)),
593 to_number(null)),
594 decode(md.origination_type, 29,decode(md.organization_id,-1,0,(nvl(md.probability,1)* md.daily_demand_rate)),
595 to_number(null)))
596 ) /
597 decode(nvl(least(sum(decode(md.origination_type,29,decode(md.organization_id,-1,0,nvl(md.probability,0)),null)),1),1),
598 0,1,
599 nvl(least(sum(decode(md.origination_type,29,decode(md.organization_id,-1,0,nvl(md.probability,0)),null)),1),1)
600 ) forecast_qty,
601
602 sum(decode(mp.plan_type,4,decode(md.origination_type,
603 5,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
604 6,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
605 7,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
606 8,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
610 12,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
607 9,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
608 10,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
609 11,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
611 15,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
612 22,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
613 24,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
614 27,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
615 29,decode(md.organization_id,-1,0,nvl(md.old_demand_quantity,0) * nvl(md.probability,1)),
616 30,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
617 0),
618 9,decode(md.origination_type,
619 5,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
620 6,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
621 7,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
622 8,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
623 9,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
624 10,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
625 11,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
626 12,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
627 15,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
628 22,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
629 24,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
630 27,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
631 29,decode(md.organization_id,-1,0,nvl(md.old_demand_quantity,0) * nvl(md.probability,1)),
632 30,nvl(md.old_demand_quantity,0) * nvl(md.probability,1),
633 0),
634 0)) io_delivered_qty,
635
636 sum(decode(mp.plan_type,4,decode(md.origination_type,
637 5,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
638 6,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
639 7,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
640 8,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
641 9,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
642 10,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
643 11,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
644 12,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
645 15,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
646 22,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
647 24,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
648 27,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
649 29,decode(md.organization_id,-1,0,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1)),
650 30,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
651 0),
652 9,decode(md.origination_type,
653 5,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
654 6,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
655 7,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
656 8,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
657 9,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
658 10,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
659 11,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
660 12,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
661 15,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
662 22,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
663 24,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
664 27,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
665 29,decode(md.organization_id,-1,0,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1)),
666 30,nvl(md.using_requirement_quantity, 0) * nvl(md.probability,1),
667 0),
668 0)) io_required_qty,
669
670 -----------------------------------------------------------------------------------------------------
671 to_number(null) late_dmd_stf_factor,
672 to_number(null) late_order_count,
673 to_number(null) late_order_value,
674 ---- indep demand service_level
675 min(decode(md.origination_type,
676 5,nvl(md.service_level, 50),
677 6,nvl(md.service_level, 50),
678 7,nvl(md.service_level, 50),
679 8,nvl(md.service_level, 50),
680 9,nvl(md.service_level, 50),
681 10,nvl(md.service_level, 50),
682 11,nvl(md.service_level, 50),
683 12,nvl(md.service_level, 50),
684 15,nvl(md.service_level, 50),
685 22,nvl(md.service_level, 50),
686 24,nvl(md.service_level, 50),
687 27,nvl(md.service_level, 50),
688 29,nvl(md.service_level, 50),
689 30,nvl(md.service_level, 50),
690 81,nvl(md.service_level, 50),
691 null)) service_level
692
696 msi_1.organization_id,
693 from msc_demands md, msc_trading_partners mtp, msc_plans mp,
694 (select msi_1.plan_id,
695 msi_1.sr_instance_id,
697 msi_1.inventory_item_id,
698 msi_1.standard_cost,
699 msi_1.list_price,
700 msi_1.AVERAGE_DISCOUNT,
701 nvl(f_1.number10,0) vmi_flag
702 from msc_system_items msi_1,
703 msc_hub_query f_1
704 where f_1.query_id(+) = l_qid_vmi
705 and f_1.number1(+) = msi_1.plan_id
706 and f_1.number3(+) = msi_1.sr_instance_id
707 and f_1.number4(+) = msi_1.organization_id
708 and f_1.number5(+) = msi_1.inventory_item_id) msi
709 where md.plan_id = p_plan_id
710 and msi.plan_id = md.plan_id
711 and msi.sr_instance_id = decode(md.sr_instance_id, -1, mp.sr_instance_id, md.sr_instance_id)
712 and msi.organization_id = decode(md.organization_id,-1,
713 msc_hub_calendar.get_item_org(p_plan_id, md.inventory_item_id,
714 decode(md.sr_instance_id, -1, mp.sr_instance_id, md.sr_instance_id)),
715 md.organization_id)
716
717 and msi.inventory_item_id = md.inventory_item_id
718 and md.sr_instance_id = mtp.sr_instance_id(+)
719 and md.organization_id = mtp.sr_tp_id(+)
720 and mtp.partner_type(+) = 3
721 and mp.plan_id=md.plan_id
722 group by
723 decode(md.organization_id, -1, -23453, md.sr_instance_id),
724 decode(md.organization_id, -1, -23453, md.organization_id),
725 md.inventory_item_id,
726 nvl(md.project_id,-23453),
727 nvl(md.task_id, -23453),
728 nvl(md.customer_id,-23453),
729 nvl(md.customer_site_id, -23453),
730 nvl(md.zone_id, -23453),
731 nvl(md.demand_class, -23453),
732
733 decode(md.organization_id, -1, msc_hub_calendar.get_item_org(p_plan_id, md.inventory_item_id,
734 decode(md.sr_instance_id, -1, mp.sr_instance_id, md.sr_instance_id)),
735 md.organization_id),
736
737 decode(md.sr_instance_id, -1, mp.sr_instance_id, md.sr_instance_id),
738
739 decode(mp.plan_type,5,decode(md.origination_type,
740 3,trunc(nvl(md.old_using_assembly_demand_date,md.using_assembly_demand_date)),
741 24,trunc(nvl(md.old_using_assembly_demand_date,md.using_assembly_demand_date)),
742 trunc(nvl(md.firm_date,md.using_assembly_demand_date))),
743 trunc(nvl(md.firm_date,md.using_assembly_demand_date)) ),
744 -1 * md.origination_type,
745 msi.vmi_flag,
746 nvl(mtp.currency_code, l_owning_currency_code)
747 union all
748 select
749 md1.sr_instance_id,
750 md1.organization_id,
751 md1.inventory_item_id,
752 nvl(md1.project_id,-23453) project_id,
753 nvl(md1.task_id, -23453) task_id,
754 nvl(md1.customer_id,-23453) customer_id,
755 nvl(md1.customer_site_id,-23453) customer_site_id,
756 nvl(md1.zone_id,-23453) region_id,
757 nvl(md1.demand_class,-23453) demand_class,
758
759 md1.organization_id owning_org_id,
760 md1.sr_instance_id owning_inst_id,
761
762
763 trunc(nvl(md1.firm_date,md1.using_assembly_demand_date)) order_date,
764 -1 * md1.origination_type order_type,
765 msi1.vmi_flag,
766 nvl(mtp1.currency_code, l_owning_currency_code) currency_code,
767 to_number(null) demand_Qty,
768 to_number(null) qty_by_due_date,
769 to_number(null) net_demand,
770 to_number(null) constrained_fcst,
771 to_number(null) constrained_fcst_value,
772 to_number(null) indep_demand_count,
773 to_number(null) indep_met_ontime_count,
774 to_number(null) indep_met_full_count,
775 to_number(null) indep_demand_value,
776 to_number(null) indep_demand_qty,
777 to_number(null) indep_by_due_date_qty,
778 to_number(null) sales_order_qty,
779 to_number(null) sales_order_count,
780 to_number(null) sales_order_metr_count,
781 to_number(null) sales_order_meta_count,
782 to_number(null) forecast_qty,
783 to_number(null) io_delivered_qty,
784 to_number(null) io_required_qty,
785
786
787
788
789 --- late demand satisfaction factor
790 --
791
792 sum(decode(md1.assembly_demand_comp_date,null,
793 decode(md1.origination_type,29,(nvl(md1.probability,1)* md1.using_requirement_quantity),
794 31,0,md1.using_requirement_quantity),
795 decode(md1.origination_type, 29,(nvl(md1.probability,1)* md1.daily_demand_rate),
796 31, 0,md1.daily_demand_rate))
797 * round(decode(med1.exception_type,
798 24, decode(sign(md1.dmd_satisfied_date - md1.using_assembly_demand_date), 0,0,
799 md1.dmd_satisfied_date - md1.using_assembly_demand_date),
800 69, 0, --- only for exception 24 and 26
801 26,decode(sign(md1.dmd_satisfied_date - md1.using_assembly_demand_date), 0,0,
802 md1.dmd_satisfied_date - md1.using_assembly_demand_date),0))
803 )
804 /decode(nvl(least(sum(decode(md1.origination_type,29,nvl(md1.probability,0),null)),1),1),
805 0,1,
806 nvl(least(sum(decode(md1.origination_type,29,nvl(md1.probability,0),null)),1),1))
807
811 md1.dmd_satisfied_date - md1.using_assembly_demand_date),
808 - sum( nvl(md1.quantity_by_due_date,0) * nvl(md1.probability,1)
809 * round(decode(med1.exception_type,
810 24, decode(sign(md1.dmd_satisfied_date - md1.using_assembly_demand_date), 0,0,
812 69, 0, --- only for exception 24 and 26
813 26,decode(sign(md1.dmd_satisfied_date - md1.using_assembly_demand_date), 0,0,
814 md1.dmd_satisfied_date - md1.using_assembly_demand_date),0))
815 )
816 /decode(nvl(least(sum(decode(md1.origination_type,29,nvl(md1.probability,0),null)),1),1),
817 0,1,
818 nvl(least(sum(decode(md1.origination_type,29,nvl(md1.probability,0),null)),1),1)) late_dmd_stf_factor,
819
820
821 --- late demand count
822 sum(decode(med1.exception_type,
823 24,1,
824 26,1,
825 69,1,
826 to_number(null))) late_order_count,-- all demand type
827
828 --- late demand val
829 --- need denominator part for forecast demand qty???
830 --- simply the decode???
831 --- replace std_cost with net selling price
832
833 sum(decode(med1.exception_type,
834 24,
835 decode(md1.assembly_demand_comp_date,null,
836 decode(md1.origination_type,29,(nvl(md1.probability,1)* md1.using_requirement_quantity),
837 31,0,md1.using_requirement_quantity),
838 decode(md1.origination_type,29,(nvl(md1.probability,1)* md1.daily_demand_rate),
839 31, 0,md1.daily_demand_rate)),
840 69,
841 decode(md1.assembly_demand_comp_date,null,
842 decode(md1.origination_type,29,(nvl(md1.probability,1)* md1.using_requirement_quantity),
843 31,0,md1.using_requirement_quantity),
844 decode(md1.origination_type,29,(nvl(md1.probability,1)* md1.daily_demand_rate),
845 31, 0,md1.daily_demand_rate)),
846 26,
847 decode(md1.assembly_demand_comp_date,null,
848 decode(md1.origination_type,29,(nvl(md1.probability,1)* md1.using_requirement_quantity),
849 31,0,md1.using_requirement_quantity),
850 decode(md1.origination_type,29,(nvl(md1.probability,1)* md1.daily_demand_rate),
851 31, 0,md1.daily_demand_rate)),
852 to_number(null)) * (nvl(msi1.list_price,0) * (1- nvl(msi1.AVERAGE_DISCOUNT,0)/100))
853
854 ) /
855 decode(nvl(least(sum(decode(md1.origination_type,29,nvl(md1.probability,0),
856 null)),1),1),0,1,
857 nvl(least(sum(decode(md1.origination_type,29,nvl(md1.probability,0),null)),1),1)
858 ) late_order_value,
859 min(nvl(md1.service_level, 50))
860
861
862 from msc_demands md1,msc_trading_partners mtp1,
863 msc_exception_details med1,
864 msc_plans mp1,
865 (select msi_2.plan_id,
866 msi_2.sr_instance_id,
867 msi_2.organization_id,
868 msi_2.inventory_item_id,
869 msi_2.standard_cost,
870 msi_2.list_price,
871 msi_2.AVERAGE_DISCOUNT,
872 nvl(f_2.number10,0) vmi_flag
873 from msc_system_items msi_2,
874 msc_hub_query f_2
875 where f_2.query_id(+) = l_qid_vmi
876 and f_2.number1(+) = msi_2.plan_id
877 and f_2.number3(+) = msi_2.sr_instance_id
878 and f_2.number4(+) = msi_2.organization_id
879 and f_2.number5(+) = msi_2.inventory_item_id) msi1
880 where md1.plan_id=med1.plan_id
881 and md1.plan_id=p_plan_id
882 and md1.origination_type in (5,6,7,8,9,10,11,12,15,22,24,27,29,30) --- only for indep demand
883 and md1.sr_instance_id = med1.sr_instance_id
884 and md1.organization_id =med1.organization_id
885 and md1.inventory_item_id=med1.inventory_item_id
886 and md1.demand_id= MED1.NUMBER1
887 and md1.plan_id=msi1.plan_id
888 and md1.sr_instance_id = msi1.sr_instance_id
889 and md1.organization_id = msi1.organization_id
890 and md1.inventory_item_id = msi1.inventory_item_id
891 and med1.EXCEPTION_TYPE in (24,26,69)
892 and md1.sr_instance_id = mtp1.sr_instance_id(+)
893 and md1.organization_id = mtp1.sr_tp_id(+)
894 and mtp1.partner_type(+) = 3
895 and mp1.plan_id = md1.plan_id
896 and mp1.plan_type <> 6
897 and md1.sr_instance_id<>-1
898 and md1.organization_id<>-1 -- exclude global f/c
899 group by
900 md1.sr_instance_id,
901 md1.organization_id,
902 md1.inventory_item_id,
903 nvl(md1.project_id,-23453),
904 nvl(md1.task_id, -23453),
905 nvl(md1.customer_id,-23453),
906 nvl(md1.customer_site_id,-23453),
907 nvl(md1.zone_id,-23453),
908 nvl(md1.demand_class,-23453),
909
910 md1.organization_id,
911 md1.sr_instance_id,
912
913 trunc(nvl(md1.firm_date,md1.using_assembly_demand_date)),
914 -1 * md1.origination_type,
915 msi1.vmi_flag,
916 nvl(mtp1.currency_code, l_owning_currency_code)
917 order by 1,2,3,4,5,6,7,8,9,10,11,12,13) demand_tbl,msc_plans mp_tbl,
918 MSC_CURRENCY_CONV_MV mcc,msc_phub_customers_mv cmv
919 where
923 and mp_tbl.plan_id = p_plan_id
920 mcc.FROM_CURRENCY(+) =demand_tbl.currency_code -- make sure 'XXX' is not a valid currency code
921 and mcc.TO_CURRENCY(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
922 and mcc.CALENDAR_DATE (+)= demand_tbl.order_date
924 and cmv.customer_id = nvl(demand_tbl.customer_id,-23453)
925 and cmv.customer_site_id = nvl(demand_tbl.customer_site_id,-23453)
926 and cmv.region_id = decode(nvl(demand_tbl.region_id,-23453),
927 -23453,decode(nvl(demand_tbl.customer_id,-23453),-23453,-23453,cmv.region_id),
928 demand_tbl.region_id)
929
930 group by
931 decode(mp_tbl.plan_type,4,1,9,1,0),
932 demand_tbl.sr_instance_id,
933 demand_tbl.organization_id,
934 demand_tbl.inventory_item_id,
935 demand_tbl.project_id,
936 demand_tbl.task_id,
937 decode(demand_tbl.order_type,-5,cmv.customer_id,
938 -6,cmv.customer_id,
939 -7,cmv.customer_id,
940 -8,cmv.customer_id,
941 -9,cmv.customer_id,
942 -10,cmv.customer_id,
943 -11,cmv.customer_id,
944 -12,cmv.customer_id,
945 -15,cmv.customer_id,
946 -22,cmv.customer_id,
947 -24,cmv.customer_id,
948 -27,cmv.customer_id,
949 -29,cmv.customer_id,
950 -30,cmv.customer_id,
951 -81,cmv.customer_id,
952 -23453),
953
954 decode(demand_tbl.order_type,-5,cmv.customer_site_id,
955 -6,cmv.customer_site_id,
956 -7,cmv.customer_site_id,
957 -8,cmv.customer_site_id,
958 -9,cmv.customer_site_id,
959 -10,cmv.customer_site_id,
960 -11,cmv.customer_site_id,
961 -12,cmv.customer_site_id,
962 -15,cmv.customer_site_id,
963 -22,cmv.customer_site_id,
964 -24,cmv.customer_site_id,
965 -27,cmv.customer_site_id,
966 -29,cmv.customer_site_id,
967 -30,cmv.customer_site_id,
968 -81,cmv.customer_site_id,
969 -23453),
970 cmv.region_id,
971 demand_tbl.demand_class,
972 demand_tbl.owning_org_id,
973 demand_tbl.owning_inst_id,
974 decode(sign(to_number(demand_tbl.order_date-l_curr_start_date)),-1,
975 msc_hub_calendar.last_work_date(p_plan_id,l_curr_start_date),
976 demand_tbl.order_date),
977 demand_tbl.order_type,
978 demand_tbl.vmi_flag;
979
980 --dbms_output.put_line('l_stmt_id='|| l_stmt_id||' count=' || SQL%ROWCOUNT);
981 commit;
982
983
984
985
986 l_stmt_id :=30;
987 select msc_hub_query_s.nextval into l_qid_last_date from dual;
988
989 insert into msc_hub_query (
990 query_id,
991 last_update_date,
992 last_updated_by,
993 creation_date,
994 created_by,
995 last_update_login,
996 date1
997 )
998 select unique l_qid_last_date, l_sysdate, 1, l_sysdate, 1, 1, trunc(mcd.month_end_date)
999 from msc_calendar_dtl mcd
1000 where mcd.month_end_date between l_first_bkt_start_date and l_last_bkt_end_date
1001 union
1002 select unique l_qid_last_date, l_sysdate, 1, l_sysdate, 1, 1, trunc(mw.week_end_date)
1003 from msc_phub_mfg_cal_weeks_mv mw
1004 where mw.week_end_date between l_first_bkt_start_date and l_last_bkt_end_date
1005 union
1006 select unique l_qid_last_date, l_sysdate, 1, l_sysdate, 1, 1, trunc(bp.end_date)
1007 from msc_phub_fiscal_periods_mv bp
1008 where bp.end_date between l_first_bkt_start_date and l_last_bkt_end_date
1009 union
1010 select l_qid_last_date, l_sysdate, 1, l_sysdate, 1, 1, trunc(l_last_bkt_end_date)
1011 from dual
1012 order by 1;
1013
1014 --dbms_output.put_line('l_stmt_id='|| l_stmt_id||' count=' || SQL%ROWCOUNT || ' last_date=' || l_qid_last_date);
1015 commit;
1016
1017
1018
1019
1020 l_stmt_id :=40;
1021 select msc_hub_query_s.nextval into l_qid_demand_date from dual;
1022
1023
1024 insert into msc_hub_query (
1025 query_id,
1026 last_update_date,
1027 last_updated_by,
1028 creation_date,
1029 created_by,
1030 last_update_login,
1031 date1
1032 )
1033 select
1034 unique l_qid_demand_date,
1035 l_sysdate,1,l_sysdate,1,1,
1036 mdf.order_date
1037 from msc_demands_f mdf
1038 where mdf.plan_id=p_plan_id
1039 and mdf.plan_run_id=p_plan_run_id
1040 and mdf.aggr_type=0;
1041
1042 --dbms_output.put_line('l_stmt_id='|| l_stmt_id||' count=' || SQL%ROWCOUNT || ' demand_date=' || l_qid_demand_date);
1043 commit;
1044
1045
1046 l_stmt_id :=45;
1047 select msc_hub_query_s.nextval into l_qid_demand_item from dual;
1048 insert into msc_hub_query (
1049 query_id,
1050 last_update_date,
1051 last_updated_by,
1052 creation_date,
1053 created_by,
1054 last_update_login,
1055 number1,
1056 number2,
1057 number3,
1058 number4,
1062 number8, -- customer_site_id
1059 number5,
1060 number6,
1061 number7, -- customer_id
1063 number11, -- region_id
1064 char1,
1065 number9, -- owning_org_id
1066 number10 -- owning_inst_id
1067 )
1068 select
1069 unique l_qid_demand_item,
1070 l_sysdate,1,l_sysdate,1,1,
1071 mdf.plan_id,
1072 mdf.plan_run_id,
1073 mdf.sr_instance_id,
1074 mdf.organization_id,
1075 mdf.inventory_item_id,
1076 mdf.vmi_flag,
1077 mdf.customer_id,
1078 mdf.customer_site_id,
1079 mdf.region_id,
1080 mdf.demand_class,
1081 mdf.owning_org_id,
1082 mdf.owning_inst_id
1083 from msc_demands_f mdf
1084 where mdf.plan_id=p_plan_id
1085 and mdf.plan_run_id=p_plan_run_id
1086 and mdf.aggr_type=0;
1087
1088 --dbms_output.put_line('l_stmt_id='|| l_stmt_id||' count=' || SQL%ROWCOUNT || ' demand_item=' || l_qid_demand_item);
1089 commit;
1090
1091
1092
1093
1094
1095
1096
1097
1098 l_stmt_id :=50;
1099 select msc_hub_query_s.nextval into l_qid_dl_relation from dual;
1100
1101 insert into msc_hub_query (
1102 query_id,
1103 last_update_date,
1104 last_updated_by,
1105 creation_date,
1106 created_by,
1107 last_update_login,
1108 date1, --- last date
1109 date2
1110 )
1111 select
1112 unique l_qid_dl_relation,
1113 l_sysdate,1,l_sysdate,1,1,
1114 l.date1,
1115 (select max(d.date1) from msc_hub_query d
1116 where d.query_id=l_qid_demand_date
1117 and d.date1<=l.date1)
1118 from msc_hub_query l where l.query_id=l_qid_last_date;
1119
1120
1121
1122 --dbms_output.put_line('l_stmt_id='|| l_stmt_id||' count=' || SQL%ROWCOUNT || ' dl=' || l_qid_dl_relation);
1123 commit;
1124
1125
1126 l_stmt_id :=60;
1127 insert into msc_demands_cum_f (
1128 CREATED_BY,
1129 CREATION_DATE,
1130 LAST_UPDATED_BY,
1131 LAST_UPDATE_DATE,
1132 LAST_UPDATE_LOGIN,
1133 PROGRAM_ID,
1134 PROGRAM_LOGIN_ID,
1135 PROGRAM_APPLICATION_ID,
1136 REQUEST_ID,
1137 ---------------------------------------------------
1138 plan_id,
1139 plan_run_id,
1140 io_plan_flag,
1141 sr_instance_id,
1142 organization_id,
1143 inventory_item_id,
1144 vmi_flag,
1145 customer_id,
1146 customer_site_id,
1147 region_id,
1148 demand_class,
1149 owning_org_id,
1150 owning_inst_id,
1151 order_date,
1152 aggr_type, category_set_id, sr_category_id,
1153 backlog_qty,
1154 cum_sales_order_qty,
1155 cum_forecast_qty,
1156 cum_constrained_fcst,
1157 cum_constrained_fcst_value,
1158 cum_constrained_fcst_value2)
1159 select
1160 l_user_id,
1161 l_sysdate,
1162 l_user_id,
1163 l_sysdate,
1164 l_user_login_id,
1165 l_program_id,
1166 l_cp_login_id,
1167 l_appl_id,
1168 l_request_id,
1169 ------------------------------------------------
1170 demand_cum_tbl.plan_id,
1171 demand_cum_tbl.plan_run_id,
1172 decode(mp_cum_tbl.plan_type,4,1,9,1,0) io_plan_flag,
1173 demand_cum_tbl.sr_instance_id,
1174 demand_cum_tbl.organization_id,
1175 demand_cum_tbl.inventory_item_id,
1176 demand_cum_tbl.vmi_flag,
1177 demand_cum_tbl.customer_id,
1178 demand_cum_tbl.customer_site_id,
1179 demand_cum_tbl.region_id,
1180 demand_cum_tbl.demand_Class,
1181 demand_cum_tbl.owning_org_id,
1182 demand_cum_tbl.owning_inst_id,
1183 demand_cum_tbl.order_date,
1184 to_number(0) aggr_type,
1185 to_number(-23453) category_set_id,
1186 to_number(-23453) sr_category_id,
1187 nvl(demand_cum_tbl.cum_indep_request_qty,0) - nvl(demand_cum_tbl.cum_indep_qty_by_due_date,0),
1188 nvl(demand_cum_tbl.cum_sales_order_qty,0),
1189 nvl(demand_cum_tbl.cum_forecast_qty,0),
1190 nvl(demand_cum_tbl.cum_constrained_fcst,0),
1191 nvl(demand_cum_tbl.cum_constrained_fcst_value,0),
1192 nvl(demand_cum_tbl.cum_constrained_fcst_value2,0)
1193
1194 from
1195
1196 (select
1197 cum.plan_id,
1198 cum.plan_run_id,
1199 cum.sr_instance_id,
1200 cum.organization_id,
1201 cum.inventory_item_id,
1202 cum.vmi_flag,
1203 cum.customer_id,
1204 cum.customer_site_id,
1205 cum.region_id,
1206 cum.demand_class,
1207 cum.owning_org_id,
1208 cum.owning_inst_id,
1209 last_date.date1 order_date,
1210 LAST_VALUE(cum.cum_indep_request_qty ignore nulls )
1211 OVER (PARTITION BY cum.plan_id,cum.plan_run_id,cum.sr_instance_id,
1212 cum.organization_id,cum.inventory_item_id,
1213 cum.demand_class,cum.customer_id,cum.customer_site_id,cum.region_id
1214 order by last_date.date1) cum_indep_request_qty,
1215
1216
1217 LAST_VALUE(cum.cum_indep_qty_by_due_date ignore nulls )
1218 OVER (PARTITION BY cum.plan_id,cum.plan_run_id,cum.sr_instance_id,
1219 cum.organization_id,cum.inventory_item_id,
1220 cum.demand_class,cum.customer_id,cum.customer_site_id,cum.region_id
1221 order by last_date.date1) cum_indep_qty_by_due_date,
1225 OVER (PARTITION BY cum.plan_id,cum.plan_run_id,cum.sr_instance_id,
1222
1223
1224 LAST_VALUE(cum.cum_FORECAST_QTY ignore nulls )
1226 cum.organization_id,cum.inventory_item_id,
1227 cum.demand_class,cum.customer_id,cum.customer_site_id,cum.region_id
1228 order by last_date.date1) cum_FORECAST_QTY,
1229
1230 LAST_VALUE(cum.cum_SALES_ORDER_QTY ignore nulls )
1231 OVER (PARTITION BY cum.plan_id,cum.plan_run_id,cum.sr_instance_id,
1232 cum.organization_id,cum.inventory_item_id,
1233 cum.demand_class,cum.customer_id,cum.customer_site_id,cum.region_id
1234 order by last_date.date1) cum_SALES_ORDER_QTY,
1235
1236 LAST_VALUE(cum.cum_constrained_fcst ignore nulls )
1237 OVER (PARTITION BY cum.plan_id,cum.plan_run_id,cum.sr_instance_id,
1238 cum.organization_id,cum.inventory_item_id,
1239 cum.demand_class,cum.customer_id,cum.customer_site_id,cum.region_id
1240 order by last_date.date1) cum_constrained_fcst,
1241
1242 LAST_VALUE(cum.cum_constrained_fcst_value ignore nulls )
1243 OVER (PARTITION BY cum.plan_id,cum.plan_run_id,cum.sr_instance_id,
1244 cum.organization_id,cum.inventory_item_id,
1245 cum.demand_class,cum.customer_id,cum.customer_site_id,cum.region_id
1246 order by last_date.date1) cum_constrained_fcst_value,
1247
1248 LAST_VALUE(cum.cum_constrained_fcst_value2 ignore nulls )
1249 OVER (PARTITION BY cum.plan_id,cum.plan_run_id,cum.sr_instance_id,
1250 cum.organization_id,cum.inventory_item_id,
1251 cum.demand_class,cum.customer_id,cum.customer_site_id,cum.region_id
1252 order by last_date.date1) cum_constrained_fcst_value2
1253
1254 from
1255 msc_hub_query last_date,
1256 (
1257 select
1258 demand_item.number1 plan_id,
1259 demand_item.number2 plan_run_id,
1260 demand_item.number3 sr_instance_id,
1261 demand_item.number4 organization_id,
1262 demand_item.number5 inventory_item_id,
1263 demand_item.number6 vmi_flag,
1264 demand_item.number7 customer_id,
1265 demand_item.number8 customer_site_id,
1266 demand_item.number11 region_id,
1267 demand_item.char1 demand_class,
1268 demand_item.date1 order_date,
1269 demand_item.number9 owning_org_id,
1270 demand_item.number10 owning_inst_id,
1271 ------------------------------------------------------------
1272 sum( t1.indep_demand_qty) over(partition by
1273 demand_item.number1,demand_item.number2,
1274 demand_item.number3,demand_item.number4,
1275 demand_item.number5,demand_item.char1,
1276 demand_item.number7,demand_item.number8,demand_item.number9
1277 order by demand_item.date1) cum_indep_request_qty,
1278
1279 sum(t1.INDEP_BY_DUE_DATE_QTY) over(partition by
1280 demand_item.number1,demand_item.number2,
1281 demand_item.number3,demand_item.number4,
1282 demand_item.number5,demand_item.char1,
1283 demand_item.number7,demand_item.number8,demand_item.number9
1284 order by demand_item.date1) cum_indep_qty_by_due_date,
1285
1286 sum(t1.FORECAST_QTY) over(partition by
1287 demand_item.number1,demand_item.number2,
1288 demand_item.number3,demand_item.number4,
1289 demand_item.number5,demand_item.char1,
1290 demand_item.number7,demand_item.number8,demand_item.number9
1291 order by demand_item.date1) cum_FORECAST_QTY,
1292
1293 sum(t1.SALES_ORDER_QTY) over(partition by
1294 demand_item.number1,demand_item.number2,
1295 demand_item.number3,demand_item.number4,
1296 demand_item.number5,demand_item.char1,
1297 demand_item.number7,demand_item.number8,demand_item.number9
1298 order by demand_item.date1) cum_SALES_ORDER_QTY,
1299
1300 sum(t1.constrained_fcst) over(partition by
1301 demand_item.number1,demand_item.number2,
1302 demand_item.number3,demand_item.number4,
1303 demand_item.number5,demand_item.char1,
1304 demand_item.number7,demand_item.number8,demand_item.number9
1305 order by demand_item.date1) cum_constrained_fcst,
1306
1307 sum(t1.constrained_fcst_value) over(partition by
1308 demand_item.number1,demand_item.number2,
1309 demand_item.number3,demand_item.number4,
1310 demand_item.number5,demand_item.char1,
1311 demand_item.number7,demand_item.number8,demand_item.number9
1312 order by demand_item.date1) cum_constrained_fcst_value,
1313
1314 sum(t1.constrained_fcst_value2) over(partition by
1315 demand_item.number1,demand_item.number2,
1316 demand_item.number3,demand_item.number4,
1317 demand_item.number5,demand_item.char1,
1318 demand_item.number7,demand_item.number8,demand_item.number9
1319 order by demand_item.date1) cum_constrained_fcst_value2
1320 from msc_demands_f t1,
1321 (select item.number1,
1322 item.number2,
1323 item.number3,
1324 item.number4,
1325 item.number5,
1326 item.number6,
1327 item.number7,
1328 item.number8,
1329 item.number11,
1330 item.char1,
1331 demand_date.date1,
1332 item.number9,
1333 item.number10
1334 from msc_hub_query item,
1335 msc_hub_query demand_date
1336 where item.query_id=l_qid_Demand_item
1337 and demand_date.query_id = l_qid_demand_date) demand_item
1338 where demand_item.date1 = t1.order_date(+)
1339 and demand_item.number1 = t1.plan_id(+)
1340 and demand_item.number2 = t1.plan_run_id(+)
1341 and demand_item.number3 = t1.sr_instance_id(+)
1342 and demand_item.number4 = t1.organization_id(+)
1343 and demand_item.number5 = t1.inventory_item_id(+)
1344 and demand_item.number7 = t1.customer_id(+)
1345 and demand_item.number8 = t1.customer_site_id(+)
1346 and demand_item.number11 = t1.region_id(+)
1347 and demand_item.char1=t1.demand_class(+)
1348 and t1.aggr_type(+) = 0) cum
1349
1350 where last_date.date2 = cum.order_Date
1351 and last_date.query_id = l_qid_dl_relation ) demand_cum_tbl,msc_plans mp_cum_tbl
1352 where mp_cum_tbl.plan_id = demand_cum_tbl.plan_id;
1353
1354
1355
1356
1357 --dbms_output.put_line('l_stmt_id='|| l_stmt_id||' count=' || SQL%ROWCOUNT);
1358 commit;
1359
1360 populate_summary(errbuf, retcode, p_plan_id, p_plan_run_id);
1361
1362 exception
1363 when no_data_found then
1364
1365 retcode :=2;
1366 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_DUPLICATE_DATA')||SQLCODE||' -ERROR- '||SQLERRM;
1367
1368 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1369 fnd_log.string(fnd_log.LEVEL_UNEXPECTED,
1370 l_api_name,
1371 to_char(SQLCODE) || ':' || SQLERRM || ' in stmt_id=' || l_stmt_id);
1372 end if;
1373
1374
1375 when dup_val_on_index then
1376 retcode :=2;
1377 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_DUPLICATE_DATA')||SQLCODE||' -ERROR- '||SQLERRM;
1378 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1379 fnd_log.string(fnd_log.LEVEL_UNEXPECTED,
1380 l_api_name,
1381 to_char(SQLCODE) || ':' || SQLERRM || ' in stmt_id=' || l_stmt_id);
1382 end if;
1383
1384 when others then
1385 retcode :=2;
1386 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_POPULATE_ERROR')||SQLCODE||' -ERROR- '||SQLERRM;
1387 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1388 fnd_log.string(fnd_log.LEVEL_UNEXPECTED,
1389 l_api_name,
1390 to_char(SQLCODE) || ':' || SQLERRM || ' in stmt_id=' || l_stmt_id);
1391 end if;
1392 --dbms_output.put_line(to_char(SQLCODE) || ':' || SQLERRM || ' in stmt_id=' || l_stmt_id);
1393
1394 end populate_details;
1395
1396 procedure populate_summary(errbuf out nocopy varchar2, retcode out nocopy varchar2,
1397 p_plan_id number, p_plan_run_id number)
1398 is
1399 l_category_set_id1 number := fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
1400 begin
1401 -- level 1
1402 insert into msc_demands_f (
1403 plan_id, plan_run_id,
1404 io_plan_flag,
1405 sr_instance_id, organization_id, inventory_item_id,
1406 project_id, task_id,
1407 customer_id, customer_site_id, region_id,
1408 demand_class,
1409 owning_org_id, owning_inst_id,
1410 order_date,
1411 aggr_type, category_set_id, sr_category_id,
1412 order_type, vmi_flag,
1413 demand_qty,
1414 qty_by_due_date,
1415 net_demand,
1416 constrained_fcst,
1417 constrained_fcst_value,
1418 constrained_fcst_value2,
1419 indep_demand_count,
1423 indep_demand_value2,
1420 indep_met_ontime_count,
1421 indep_met_full_count,
1422 indep_demand_value,
1424 indep_demand_qty,
1425 indep_by_due_date_qty,
1426 sales_order_qty,
1427 sales_order_count,
1428 sales_order_metr_count,
1429 sales_order_meta_count,
1430 forecast_qty,
1431 io_required_qty,
1432 io_delivered_qty,
1433 late_dmd_stf_factor,
1434 late_order_count,
1435 late_order_value,
1436 late_order_value2,
1437 service_level,
1438 created_by, creation_date,
1439 last_update_date, last_updated_by, last_update_login,
1440 program_id, program_login_id,
1441 program_application_id, request_id)
1442 -- category (42, 43, 44)
1443 select
1444 f.plan_id, f.plan_run_id,
1445 f.io_plan_flag,
1446 f.sr_instance_id, f.organization_id,
1447 to_number(-23453) inventory_item_id,
1448 f.project_id, f.task_id,
1449 f.customer_id, f.customer_site_id, f.region_id,
1450 f.demand_class,
1451 f.owning_org_id, f.owning_inst_id,
1452 f.order_date,
1453 to_number(42) aggr_type,
1454 l_category_set_id1 category_set_id,
1455 nvl(q.sr_category_id, -23453),
1456 f.order_type, f.vmi_flag,
1457 sum(f.demand_qty),
1458 sum(f.qty_by_due_date),
1459 sum(f.net_demand),
1460 sum(f.constrained_fcst),
1461 sum(f.constrained_fcst_value),
1462 sum(f.constrained_fcst_value2),
1463 sum(f.indep_demand_count),
1464 sum(f.indep_met_ontime_count),
1465 sum(f.indep_met_full_count),
1466 sum(f.indep_demand_value),
1467 sum(f.indep_demand_value2),
1468 sum(f.indep_demand_qty),
1469 sum(f.indep_by_due_date_qty),
1470 sum(f.sales_order_qty),
1471 sum(f.sales_order_count),
1472 sum(f.sales_order_metr_count),
1473 sum(f.sales_order_meta_count),
1474 sum(f.forecast_qty),
1475 sum(f.io_required_qty),
1476 sum(f.io_delivered_qty),
1477 sum(f.late_dmd_stf_factor),
1478 sum(f.late_order_count),
1479 sum(f.late_order_value),
1480 sum(f.late_order_value2),
1481 min(service_level),
1482 fnd_global.user_id, sysdate,
1483 sysdate, fnd_global.user_id, fnd_global.login_id,
1484 fnd_global.conc_program_id, fnd_global.conc_login_id,
1485 fnd_global.prog_appl_id, fnd_global.conc_request_id
1486 from
1487 msc_demands_f f,
1488 msc_phub_item_categories_mv q
1489 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
1490 and f.aggr_type=0
1491 and f.owning_inst_id=q.sr_instance_id(+)
1492 and f.owning_org_id=q.organization_id(+)
1493 and f.inventory_item_id=q.inventory_item_id(+)
1494 and q.category_set_id(+)=l_category_set_id1
1495 group by
1496 f.plan_id, f.plan_run_id,
1497 f.io_plan_flag,
1498 f.sr_instance_id, f.organization_id,
1499 f.project_id, f.task_id,
1500 f.customer_id, f.customer_site_id, f.region_id,
1501 f.demand_class,
1502 f.owning_org_id, f.owning_inst_id,
1503 f.order_date,
1504 nvl(q.sr_category_id, -23453),
1505 f.order_type, f.vmi_flag;
1506
1507 commit;
1508
1509 -- level 1
1510 insert into msc_demands_cum_f (
1511 plan_id, plan_run_id,
1512 io_plan_flag,
1513 sr_instance_id, organization_id, inventory_item_id,
1514 vmi_flag,
1515 customer_id, customer_site_id, region_id,
1516 demand_class,
1517 owning_org_id, owning_inst_id,
1518 order_date,
1519 aggr_type, category_set_id, sr_category_id,
1520 backlog_qty,
1521 cum_sales_order_qty,
1522 cum_forecast_qty,
1523 cum_constrained_fcst,
1524 cum_constrained_fcst_value,
1525 cum_constrained_fcst_value2,
1526 created_by, creation_date,
1527 last_update_date, last_updated_by, last_update_login,
1528 program_id, program_login_id,
1529 program_application_id, request_id)
1530 -- category (42, 43, 44)
1531 select
1532 f.plan_id, f.plan_run_id,
1533 f.io_plan_flag,
1534 f.sr_instance_id, f.organization_id,
1535 to_number(-23453) inventory_item_id,
1536 f.vmi_flag,
1537 f.customer_id, f.customer_site_id, f.region_id,
1538 f.demand_class,
1539 f.owning_org_id, f.owning_inst_id,
1540 f.order_date,
1541 to_number(42) aggr_type,
1542 l_category_set_id1 category_set_id,
1543 nvl(q.sr_category_id, -23453),
1544 sum(f.backlog_qty),
1545 sum(f.cum_sales_order_qty),
1546 sum(f.cum_forecast_qty),
1547 sum(f.cum_constrained_fcst),
1548 sum(f.cum_constrained_fcst_value),
1549 sum(f.cum_constrained_fcst_value2),
1550 fnd_global.user_id, sysdate,
1551 sysdate, fnd_global.user_id, fnd_global.login_id,
1552 fnd_global.conc_program_id, fnd_global.conc_login_id,
1553 fnd_global.prog_appl_id, fnd_global.conc_request_id
1554 from
1555 msc_demands_cum_f f,
1559 and f.owning_inst_id=q.sr_instance_id(+)
1556 msc_phub_item_categories_mv q
1557 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
1558 and f.aggr_type=0
1560 and f.owning_org_id=q.organization_id(+)
1561 and f.inventory_item_id=q.inventory_item_id(+)
1562 and q.category_set_id(+)=l_category_set_id1
1563 group by
1564 f.plan_id, f.plan_run_id,
1565 f.io_plan_flag,
1566 f.sr_instance_id, f.organization_id,
1567 f.vmi_flag,
1568 f.customer_id, f.customer_site_id, f.region_id,
1569 f.demand_class,
1570 f.owning_org_id, f.owning_inst_id,
1571 f.order_date,
1572 nvl(q.sr_category_id, -23453);
1573
1574 commit;
1575
1576 -- level 2
1577 insert into msc_demands_f (
1578 plan_id, plan_run_id, io_plan_flag,
1579 sr_instance_id, organization_id, inventory_item_id,
1580 project_id, task_id,
1581 customer_id, customer_site_id, region_id,
1582 demand_class, owning_org_id, owning_inst_id, order_date,
1583 aggr_type, category_set_id, sr_category_id,
1584 order_type, vmi_flag,
1585 demand_qty,
1586 qty_by_due_date,
1587 net_demand,
1588 constrained_fcst,
1589 constrained_fcst_value,
1590 constrained_fcst_value2,
1591 indep_demand_count,
1592 indep_met_ontime_count,
1593 indep_met_full_count,
1594 indep_demand_value,
1595 indep_demand_value2,
1596 indep_demand_qty,
1597 indep_by_due_date_qty,
1598 sales_order_qty,
1599 sales_order_count,
1600 sales_order_metr_count,
1601 sales_order_meta_count,
1602 forecast_qty,
1603 io_required_qty,
1604 io_delivered_qty,
1605 late_dmd_stf_factor,
1606 late_order_count,
1607 late_order_value,
1608 late_order_value2,
1609 service_level,
1610 created_by, creation_date,
1611 last_update_date, last_updated_by, last_update_login,
1612 program_id, program_login_id,
1613 program_application_id, request_id)
1614 -- category-mfg_period (1016, 1017, 1018)
1615 select
1616 f.plan_id, f.plan_run_id, f.io_plan_flag,
1617 f.sr_instance_id, f.organization_id, f.inventory_item_id,
1618 f.project_id, f.task_id,
1619 f.customer_id, f.customer_site_id, f.region_id,
1620 f.demand_class, f.owning_org_id, f.owning_inst_id,
1621 mp.period_start_date order_date,
1622 decode(f.aggr_type, 42, 1016, 43, 1017, 1018) aggr_type,
1623 f.category_set_id, f.sr_category_id,
1624 f.order_type, f.vmi_flag,
1625 sum(f.demand_qty),
1626 sum(f.qty_by_due_date),
1627 sum(f.net_demand),
1628 sum(f.constrained_fcst),
1629 sum(f.constrained_fcst_value),
1630 sum(f.constrained_fcst_value2),
1631 sum(f.indep_demand_count),
1632 sum(f.indep_met_ontime_count),
1633 sum(f.indep_met_full_count),
1634 sum(f.indep_demand_value),
1635 sum(f.indep_demand_value2),
1636 sum(f.indep_demand_qty),
1637 sum(f.indep_by_due_date_qty),
1638 sum(f.sales_order_qty),
1639 sum(f.sales_order_count),
1640 sum(f.sales_order_metr_count),
1641 sum(f.sales_order_meta_count),
1642 sum(f.forecast_qty),
1643 sum(f.io_required_qty),
1644 sum(f.io_delivered_qty),
1645 sum(f.late_dmd_stf_factor),
1646 sum(f.late_order_count),
1647 sum(f.late_order_value),
1648 sum(f.late_order_value2),
1649 min(f.service_level),
1650 fnd_global.user_id, sysdate,
1651 sysdate, fnd_global.user_id, fnd_global.login_id,
1652 fnd_global.conc_program_id, fnd_global.conc_login_id,
1653 fnd_global.prog_appl_id, fnd_global.conc_request_id
1654 from
1655 msc_demands_f f,
1656 msc_phub_mfg_cal_periods_mv mp
1657 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
1658 and f.aggr_type between 42 and 44
1659 and f.order_date between mp.period_start_date and mp.period_end_date
1660 group by
1661 f.plan_id, f.plan_run_id, f.io_plan_flag,
1662 f.sr_instance_id, f.organization_id, f.inventory_item_id,
1663 f.project_id, f.task_id,
1664 f.customer_id, f.customer_site_id, f.region_id,
1665 f.demand_class, f.owning_org_id, f.owning_inst_id,
1666 mp.period_start_date,
1667 decode(f.aggr_type, 42, 1016, 43, 1017, 1018),
1668 f.category_set_id, f.sr_category_id,
1669 f.order_type, f.vmi_flag
1670 union all
1671 -- category-fiscal_period (1019, 1020, 1021)
1672 select
1673 f.plan_id, f.plan_run_id, f.io_plan_flag,
1674 f.sr_instance_id, f.organization_id, f.inventory_item_id,
1675 f.project_id, f.task_id,
1676 f.customer_id, f.customer_site_id, f.region_id,
1677 f.demand_class, f.owning_org_id, f.owning_inst_id,
1678 fp.start_date order_date,
1679 decode(f.aggr_type, 42, 1019, 43, 1020, 1021) aggr_type,
1680 f.category_set_id, f.sr_category_id,
1681 f.order_type, f.vmi_flag,
1682 sum(f.demand_qty),
1683 sum(f.qty_by_due_date),
1684 sum(f.net_demand),
1685 sum(f.constrained_fcst),
1689 sum(f.indep_met_ontime_count),
1686 sum(f.constrained_fcst_value),
1687 sum(f.constrained_fcst_value2),
1688 sum(f.indep_demand_count),
1690 sum(f.indep_met_full_count),
1691 sum(f.indep_demand_value),
1692 sum(f.indep_demand_value2),
1693 sum(f.indep_demand_qty),
1694 sum(f.indep_by_due_date_qty),
1695 sum(f.sales_order_qty),
1696 sum(f.sales_order_count),
1697 sum(f.sales_order_metr_count),
1698 sum(f.sales_order_meta_count),
1699 sum(f.forecast_qty),
1700 sum(f.io_required_qty),
1701 sum(f.io_delivered_qty),
1702 sum(f.late_dmd_stf_factor),
1703 sum(f.late_order_count),
1704 sum(f.late_order_value),
1705 sum(f.late_order_value2),
1706 min(f.service_level),
1707 fnd_global.user_id, sysdate,
1708 sysdate, fnd_global.user_id, fnd_global.login_id,
1709 fnd_global.conc_program_id, fnd_global.conc_login_id,
1710 fnd_global.prog_appl_id, fnd_global.conc_request_id
1711 from
1712 msc_demands_f f,
1713 msc_phub_fiscal_periods_mv fp
1714 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
1715 and f.aggr_type between 42 and 44
1716 and f.order_date between fp.start_date and fp.end_date
1717 group by
1718 f.plan_id, f.plan_run_id, f.io_plan_flag,
1719 f.sr_instance_id, f.organization_id, f.inventory_item_id,
1720 f.project_id, f.task_id,
1721 f.customer_id, f.customer_site_id, f.region_id,
1722 f.demand_class, f.owning_org_id, f.owning_inst_id,
1723 fp.start_date,
1724 decode(f.aggr_type, 42, 1019, 43, 1020, 1021),
1725 f.category_set_id, f.sr_category_id,
1726 f.order_type, f.vmi_flag;
1727
1728 commit;
1729
1730 exception
1731 when dup_val_on_index then
1732 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_DUPLICATE_DATA')||
1733 SQLCODE||' -ERROR- '||SQLERRM;
1734 retcode := 2;
1735 --dbms_output.put_line(errbuf);
1739 retcode := 2;
1736 when others then
1737 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_POPULATE_ERROR')||
1738 SQLCODE||' -ERROR- '||SQLERRM;
1740 --dbms_output.put_line(errbuf);
1741
1742 end populate_summary;
1743
1744
1745
1746 procedure purge_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
1747 p_plan_id number, p_plan_run_id number default null,
1748 p_commit_size in number default 1000) is
1749
1750 l_num number;
1751 l_api_name varchar2(100);
1752 l_stmt_id number;
1753 begin
1754
1755 retcode:=0 ; -- this means successfully
1756 errbuf :='';
1757 --Successfully populated msc_demands_f table for plan_id =' || p_plan_id || ',plan_run_id=' || p_plan_run_id;
1758 -- initial there is no error message
1759 l_api_name := 'msc_demand_f_pkg.purge_details';
1760
1761
1762 while true loop
1763 l_stmt_id:=1;
1764 delete /*+ PARALLEL(mos) */ from msc_demands_f
1765 where rownum<=p_commit_size and plan_id=p_plan_id and plan_run_id=nvl(p_plan_run_id,plan_run_id);
1766 l_stmt_id:=2;
1767 commit;
1768 -- exit when there are no more rows to delete.
1769 l_stmt_id:=3;
1770 select count(1) into l_num from msc_demands_f
1771 where plan_id=p_plan_id and plan_run_id=nvl(p_plan_run_id,plan_run_id);
1772 if (l_num=0) then
1773 commit;
1774 exit;
1775 end if;
1776 end loop;
1777 exception
1778 when others then
1779
1780 retcode :=2;
1781 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_PURGE_ERROR')||SQLCODE||' -ERROR- '||SQLERRM;
1782 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1783 fnd_log.string(fnd_log.LEVEL_UNEXPECTED,
1784 l_api_name,
1785 to_char(SQLCODE) || ':' || SQLERRM || ' in stmt_id=' || l_stmt_id);
1786 end if;
1787
1788 errbuf := SQLERRM;
1789
1790 return;
1791 end purge_details;
1792
1793 end msc_demand_pkg;