[Home] [Help]
PACKAGE BODY: APPS.MSC_ITEM_PKG
Source
1 package body msc_item_pkg as
2 /* $Header: MSCHBITB.pls 120.44.12010000.5 2008/09/03 14:41:24 wexia ship $ */
3
4
5
6
7
8 procedure populate_details(errbuf out nocopy varchar2,
9 retcode out nocopy varchar2,
10 p_plan_id number,
11 p_plan_run_id number default null) is
12
13
14 l_qid_bucket number;
15 l_qid_vmi_item number;
16 l_qid_last_date1 number;
17 l_qid_last_date number;
18 l_qid_sd_item number;
19 l_qid_pab number;
20 l_qid_pab_item number;
21 l_qid_mil_item number;
22 l_qid_ss_item number;
23 l_qid_ss number;
24 l_qid_others number;
25 l_qid_hub_week number;
26 l_qid_bis_week number;
27 l_qid_week_map number;
28
29 l_api_name varchar2(100);
30 l_stmt_id number ;
31
32 l_sysdate date;
33 l_user_id number;
34 l_user_login_id number;
35 l_cp_login_id number;
36 l_program_id number;
37 l_appl_id number;
38 l_request_id number;
39 l_plan_days number;
40 l_count number;
41 l_first_bkt_start_date date;
42 l_last_bkt_end_date date;
43
44 l_owning_currency_code varchar2(20) := 'XXX';
45
46 cursor c_plan_orgs is
47 select sr_instance_id, organization_id
48 from msc_plan_organizations
49 where plan_id = p_plan_id;
50
51 begin
52
53 retcode :=0; -- this means successfully
54 errbuf :='';
55
56
57 l_user_id := fnd_global.user_id;
58 l_sysdate :=sysdate;
59 l_user_login_id :=fnd_global.login_id;
60 l_cp_login_id :=FND_GLOBAL.CONC_LOGIN_ID;
61 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
62 l_appl_id := FND_GLOBAL.PROG_APPL_ID;
63 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
64
65
66 select min(bkt_start_date), max(bkt_end_date)
67 into l_first_bkt_start_date, l_last_bkt_end_date
68 from msc_plan_buckets
69 where plan_id=p_plan_id;
70
71 l_api_name := 'msc_item_f_pkg.populate_summaries';
72
73 -- save owning organization's functional currency
74 begin
75 select o.currency_code
76 into l_owning_currency_code
77 from msc_trading_partners o, msc_plans p
78 where o.sr_instance_id=p.sr_instance_id
79 and o.sr_tp_id=p.organization_id
80 and o.partner_type=3
81 and p.plan_id=p_plan_id;
82 exception
83 when others then
84 null;
85 end;
86
87
88 -----------------------------------------------------------------
89 --- get the total days in the plan into l_plan_days
90 --- l_plan_days will be used later to calculate avg_daily_demand
91 -----------------------------------------------------------------
92
93 l_stmt_id :=0;
94 select curr_cutoff_date - curr_start_date +1 into l_plan_days
95 from msc_plans where plan_id = p_plan_id;
96
97
98 ---------------------------------------------------------------
99 -- insert vmi item in this plan into msc_hub_query by l_qid_vmi_item;
100 -- max possible rows insert =100;
101 -- we can verify the query with plan_id=63
102 -- l_qid_vim_item result will be used later to populate vmi_flag
103 ---------------------------------------------------------------
104
105 l_stmt_id :=10;
106 select msc_hub_query_s.nextval into l_qid_vmi_item from dual;
107
108 insert into msc_hub_query(
109 query_id,
110 last_update_date,
111 last_updated_by,
112 creation_date,
113 created_by,
114 last_update_login,
115 number1, -- plan_id
116 number2, -- plan_run_id
117 number3, -- sr_instance_id
118 number4, -- organization_id
119 number5, -- inventory_item_id
120 number6 -- vmi flag
121 )
122 select
123 unique l_qid_vmi_item,l_sysdate,1,l_sysdate,1,1,
124 p_plan_id,
125 p_plan_run_id,
126 msi.sr_instance_id,
127 msi.organization_id,
128 msi.inventory_item_id,
129 nvl(mis.vmi_flag,0)
130 from msc_item_suppliers mis,
131 msc_system_items msi
132 where msi.plan_id = mis.plan_id
133 and msi.sr_instance_id = mis.sr_instance_id
134 and msi.organization_id = mis.organization_id
135 and msi.inventory_item_id = mis.inventory_item_id
136 and msi.plan_id = p_plan_id
137 and nvl(mis.vmi_flag,0)=1;
138
139 --dbms_output.put_line(l_stmt_id||', l_qid_vmi_item='||l_qid_vmi_item||', count='||sql%rowcount);
140 commit;
141
142 ----------------------------------------------------------------------------
143 -- get plan bucket information+ (curr_start_date-1) + (curr_cutoff_date+1)
144 -- we need to find out the last working
145 -- day in the bucket since engine put supply/demand during that bucket
146 -- in bucket's last working day
147 -- about 200 rows
148 ----------------------------------------------------------------------------
149 l_stmt_id:=20;
150 select msc_hub_query_s.nextval into l_qid_bucket from dual;
151 insert into msc_hub_query(
152 query_id,
153 last_update_date,
154 last_updated_by,
155 creation_date,
156 created_by,
157 last_update_login,
158 number1, -- plan_id
159 number2, -- plan_run_id
160 number3, -- sr_instance_id
161 number4, -- organization_id
162 date1, -- bkt_start_date
163 date2, --- bkt_end_date
164 date3, --- last working day NOTE: for day bucket, this could be null
165 date4, -- safety_Stock period date in msc_safety_Stock table,
166 -- this may not at bucket_start date
167 date5, -- working day bkt start date
168 number10, -- bucket_type
169 number11 -- days in bucket
170 )
171 select
172 l_qid_bucket,l_sysdate,1,l_sysdate,1,1,
173 p_plan_id,
174 p_plan_run_id,
175 mpb.sr_instance_id,
176 mpb.organization_id,
177 mpb.bkt_start_date,
178 mpb.bkt_end_date,
179 decode(mpb.bucket_type,1,mpb.bkt_start_date,
180 msc_hub_calendar.last_work_date(p_plan_id,mpb.sr_instance_id,
181 mpb.bucket_type,mpb.bkt_start_date,
182 mpb.bkt_end_date) )last_work_date,
183
184 -- day bucket always has its self as last_work_date
185 -- no matter it is actually a working day or not
186
187 msc_hub_calendar.ss_date(p_plan_id,mpb.bkt_start_date,mpb.bkt_end_date) ss_date,
188
189 decode(mpb.bucket_type,1,
190 msc_hub_calendar.working_day_bkt_start_date(p_plan_id,
191 mpb.sr_instance_id,
192 mpb.bucket_type,
193 mpb.bkt_start_date,
194 mpb.bkt_end_date) ,
195 mpb.bkt_start_date) working_day_bkt_start_date,
196
197 mpb.bucket_type,
198 mpb.days_in_bkt
199 from msc_plan_buckets mpb,
200 msc_plans mp
201 where mpb.plan_id =p_plan_id
202 and mp.plan_id=mpb.plan_id
203 and mpb.sr_instance_id = mp.sr_instance_id
204 and mpb.organization_id = mp.organization_id
205 and mpb.curr_flag=1;
206
207 --dbms_output.put_line(l_stmt_id||', l_qid_bucket='||l_qid_bucket||', count='||sql%rowcount);
208 commit;
209
210
211 ------------------------------------------------------------------------
212 --- get week end date/week start date from msc_phub_mfg_cal_weeks_mv
213 --- so that we can merge msc_item_kpi_f with msc_item_inventory_f
214 -------------------------------------------------------------------
215 l_stmt_id:=25;
216 select msc_hub_query_s.nextval into l_qid_hub_week from dual;
217 insert into msc_hub_query (
218 query_id,
219 last_update_date,
220 last_updated_by,
221 creation_date,
222 created_by,
223 last_update_login,
224 date1 , -- hub week start date
225 date2 -- hub week end date
226 )
227 select unique l_qid_hub_week,
228 l_sysdate,1,l_sysdate,1,1,
229 trunc(mw.week_start_date),
230 trunc(mw.WEEK_END_DATE) -- need trunc since end_date is in time stamp 23:59:59
231 from msc_phub_mfg_cal_weeks_mv mw,
232 msc_plans mp
233 where mp.curr_start_date<=mw.week_end_date
234 and mp.curr_cutoff_date >=mw.week_start_date -- curr_cutoff_date is in 00:00:00,
235 -- cutoff date >=week_start_date instead of week_end date
236 -- this is important to include the last week where
237 --- week end date may > cutoff date
238 and mp.plan_id=p_plan_id;
239
240
241 --dbms_output.put_line(l_stmt_id||', l_qid_hub_week='||l_qid_hub_week||', count='||sql%rowcount);
242 commit;
243
244
245
246 l_stmt_id:=26;
247 select msc_hub_query_s.nextval into l_qid_bis_week from dual;
248 insert into msc_hub_query (
249 query_id,
250 last_update_date,
251 last_updated_by,
252 creation_date,
253 created_by,
254 last_update_login,
255 date1
256 )
257 select unique l_qid_bis_week,
258 l_sysdate,1,l_sysdate,1,1,
259 trunc(mbid.detail_date)
260 from msc_bis_inv_detail mbid, msc_plans mp
261 where mbid.plan_id=p_plan_id
262 and mbid.plan_id=mp.plan_id
263 and (nvl(mbid.detail_level,0)=1 or mp.plan_type=6)
264 and nvl(mbid.period_type,0)=1;
265
266 --dbms_output.put_line(l_stmt_id||', l_qid_bis_week='||l_qid_bis_week||', count='||sql%rowcount);
267 commit;
268
269
270 ----------------------------------------------------------------------------
271 --- find the mapping between bis week and mfg week
272 ----------------------------------------------------------------------------
273
274 l_stmt_id:=27;
275 select msc_hub_query_s.nextval into l_qid_week_map from dual;
276 insert into msc_hub_query (
277 query_id,
278 last_update_date,
279 last_updated_by,
280 creation_date,
281 created_by,
282 last_update_login,
283 date1 , -- hub week start date
284 date2 , -- hub week end date
285 date3 -- bis week start date=msc_bis_inv_detail.detail_date
286 )
287 select unique l_qid_week_map,
288 l_sysdate,1,l_sysdate,1,1,
289 mfg.date1 hub_week_start_date,
290 mfg.date2 hub_week_end_date,
291 bis.date1
292 from msc_hub_query mfg,
293 msc_hub_query bis
294 where mfg.query_id=l_qid_hub_week
295 and bis.query_id=l_qid_bis_week
296 and bis.date1>=mfg.date1
297 and bis.date1<=mfg.date2;
298
299
300 --dbms_output.put_line(l_stmt_id||', l_qid_week_map='||l_qid_week_map||', count='||sql%rowcount);
301 commit;
302
303
304
305
306 -------------------------------------------------------------------------
307 -- get the
308 -- last date of week(for mfg calendar)
309 -- last date of period(for fiscal calendar)
310 -- last date of the month(for Greg calendar)
311 -- about 100 rows max
312 --------------------------------------------------------------------
313
314 l_stmt_id :=30;
315 select msc_hub_query_s.nextval into l_qid_last_date1 from dual;
316
317 insert into msc_hub_query (
318 query_id,
319 last_update_date,
320 last_updated_by,
321 creation_date,
322 created_by,
323 last_update_login,
324 date1
325 )
326 select unique l_qid_last_date1, l_sysdate, 1, l_sysdate, 1, 1, trunc(mcd.month_end_date)
327 from msc_calendar_dtl mcd
328 where mcd.month_end_date between l_first_bkt_start_date and l_last_bkt_end_date
329 union
330 select unique l_qid_last_date1, l_sysdate, 1, l_sysdate, 1, 1, trunc(mw.week_end_date)
331 from msc_phub_mfg_cal_weeks_mv mw
332 where mw.week_end_date between l_first_bkt_start_date and l_last_bkt_end_date
333 union
334 select unique l_qid_last_date1, l_sysdate, 1, l_sysdate, 1, 1, trunc(bp.end_date)
335 from msc_phub_fiscal_periods_mv bp
336 where bp.end_date between l_first_bkt_start_date and l_last_bkt_end_date
337 union
338 select l_qid_last_date1, l_sysdate, 1, l_sysdate, 1, 1, trunc(l_last_bkt_end_date)
339 from dual
340 order by 1;
341
342 --dbms_output.put_line(l_stmt_id||', l_qid_last_Date1='||l_qid_last_Date1||', count='||sql%rowcount);
343 commit;
344
345
346 -------------------------------------------------------------------
347 -- insert last date of the week/period and month as
348 -- well as its corresonding bkt_start_date and last_work_date
349 -- this is required to move ss to last date of the week/bis period/month
350 -- from bkt_start_date and pab from last_work_date to last_date of
351 -- week/period/month
352 -- about 100 rows
353 -----------------------------------------------------------------
354
355
356 l_stmt_id :=35;
357 select msc_hub_query_s.nextval into l_qid_last_date from dual;
358
359
360 insert into msc_hub_query (
361 query_id,
362 last_update_date,
363 last_updated_by,
364 creation_date,
365 created_by,
366 last_update_login,
367 date1, -- last_date
368 date2, -- bkt_start_date, for day bucket, it may not be a working day
369 date3 -- last_work_date,
370
371 )
372 select l_qid_last_date,
373 l_sysdate,1,l_sysdate,1,1,
374 f1.date1 last_date,
375 (select max(f2.date1) from msc_hub_query f2
376 where f2.date1<=f1.date1 and f2.query_id = l_qid_bucket) ,
377
378 (select max(f3.date3) from msc_hub_query f3
379 where f3.date3<=f1.date1 and f3.query_id =l_qid_bucket)
380
381 from msc_hub_query f1 where f1.query_id = l_qid_last_date1;
382
383 --dbms_output.put_line(l_stmt_id||', l_qid_last_date='||l_qid_last_date||', count='||sql%rowcount);
384 commit;
385
386
387
388 ----------------------------------------------------------
389 -- get the item/plan_bucket combination
390 -- only item which has activity(supply/demand/safety_stock)
391 -- is included
392 -----------------------------------------------------------
393
394 l_stmt_id :=40;
395 select msc_hub_query_s.nextval into l_qid_mil_item from dual;
396
397 for c in c_plan_orgs
398 loop
399 insert into msc_hub_query (
400 query_id,
401 last_update_date,
402 last_updated_by,
403 creation_date,
404 created_by,
405 last_update_login,
406 number1, -- plan_id
407 number2, -- plan_run_id
408 number3, -- sr_instance_id
409 number4, -- org_id
410 number5, -- inventory_item_id
411 number6, -- vmi_flag
412 date1, -- bkt_start_date
413 date2, -- bkt end date,
414 date3, -- last work date
415 date4, -- ss date
416 date5, -- working day bkt start date
417 number10, -- bkt_type
418 number11 -- days in bucket
419 )
420 select
421 l_qid_mil_item, l_sysdate, 1, l_sysdate, 1, 1,
422 p_plan_id, p_plan_run_id,
423 t.sr_instance_id,
424 t.organization_id,
425 t.inventory_item_id,
426 t.vmi_flag,
427 f.date1, -- bkt_start_date,
428 f.date2, -- bkt_end_date
429 f.date3, -- last work date
430 f.date4,
431 f.date5,
432 f.number10, --bkt_type
433 f.number11 --days in bucket
434 from
435 (select distinct sr_instance_id, organization_id, inventory_item_id,
436 nvl(vmi_flag, 0) vmi_flag
437 from msc_demands_f
438 where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type=0
439 and sr_instance_id=c.sr_instance_id and organization_id=c.organization_id
440 union
441 select distinct sr_instance_id, organization_id, inventory_item_id,
442 nvl(vmi_flag, 0) vmi_flag
443 from msc_supplies_f
444 where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type=0
445 and sr_instance_id=c.sr_instance_id and organization_id=c.organization_id
446 union
447 select distinct mss.sr_instance_id, mss.organization_id, mss.inventory_item_id,
448 nvl(vmi.number6, 0) vmi_flag
449 from msc_safety_stocks mss, msc_hub_query vmi
450 where mss.plan_id=p_plan_id
451 and mss.plan_id=vmi.number1(+)
452 and mss.sr_instance_id=vmi.number3(+)
453 and mss.organization_id=vmi.number4(+)
454 and mss.inventory_item_id=vmi.number5(+)
455 and vmi.query_id(+)=l_qid_vmi_item
456 and mss.sr_instance_id=c.sr_instance_id
457 and mss.organization_id=c.organization_id) t,
458
459 (select distinct
460 t_bucket.date1, -- bkt_start_date,
461 t_bucket.date2, -- bkt_end_date
462 t_bucket.date3, -- last work date
463 t_bucket.date4,
464 t_bucket.date5,
465 t_bucket.number10, --bkt_type
466 t_bucket.number11 --days in bucket
467 from msc_hub_query t_bucket, msc_hub_query t_last_date
468 where t_bucket.query_id=l_qid_bucket
469 and t_last_date.query_id=l_qid_last_date
470 and t_bucket.date1=t_last_date.date2) f;
471
472 --dbms_output.put_line(l_stmt_id||', l_qid_mil_item='||l_qid_mil_item||', count='||sql%rowcount);
473 commit;
474 end loop;
475
476
477 ----------------------------------------------------------
478 -- get the item which has supply/demand
479 -- only item which has activity(supply/demand)
480 -- is included
481 -----------------------------------------------------------
482
483 l_stmt_id :=50;
484 select msc_hub_query_s.nextval into l_qid_sd_item from dual;
485
486
487 insert into msc_hub_query
488 (query_id,
489 last_update_date,
490 last_updated_by,
491 creation_date,
492 created_by,
493 last_update_login,
494 number1, -- plan_id
495 number2, --- plan_run_id
496 number3, -- sr_instance_id
497 number4, -- org_id
498 number5, -- inventory_item_id
499 number6, -- vmi_flag
500 number7, -- owning_org_id
501 number8, -- owning_inst_id
502 date1, -- bkt_start_date
503 date2, -- bkt_end_date
504 date3 , -- activity date
505 number10, --bkt_type
506 number11 -- days_in_bkt
507 )
508 select
509 l_qid_sd_item,
510 l_sysdate,1,l_sysdate,1,1,
511 p_plan_id,
512 p_plan_run_id,
513 sd.sr_instance_id,
514 sd.organization_id,
515 sd.inventory_item_id,
516 sd.vmi_flag,
517 decode(sign(sd.organization_id), -1, msc_hub_calendar.get_item_org(p_plan_id, sd.inventory_item_id,
518 decode(sign(sd.sr_instance_id), -1, mp.sr_instance_id, sd.sr_instance_id)),
519 sd.organization_id),
520 decode(sign(sd.sr_instance_id), -1, mp.sr_instance_id, sd.sr_instance_id),
521 f.date1,
522 f.date2,
523 sd.activity_date,
524 f.number10,
525 f.number11
526 from msc_hub_query f,
527 (select unique
528 mdf.plan_id,
529 mdf.sr_instance_id ,
530 mdf.organization_id,
531 mdf.inventory_item_id,
532 nvl(mdf.vmi_flag,0) vmi_flag,
533 mdf.order_date activity_date
534 from msc_demands_f mdf
535 where mdf.plan_id= p_plan_id
536 and mdf.plan_run_id = p_plan_run_id
537 and mdf.aggr_type=0
538 union
539 select unique
540 msf.plan_id,
541 msf.sr_instance_id,
542 msf.organization_id,
543 msf.inventory_item_id,
544 nvl(msf.vmi_flag,0) vmi_flag,
545 msf.supply_date activity_date
546 from msc_supplies_f msf
547 where msf.plan_id = p_plan_id
548 and msf.plan_run_id = p_plan_run_id
549 and msf.aggr_type=0) sd,
550 msc_plans mp
551 where sd.activity_date = f.date3
552 and sd.plan_id = mp.plan_id
553 and f.query_id =l_qid_bucket;
554
555 --dbms_output.put_line(l_stmt_id||', l_qid_sd_item='||l_qid_sd_item||', count='||sql%rowcount);
556 commit;
557
558
559
560 -----------------------------------------------------------------------------
561 l_stmt_id :=55;
562 select msc_hub_query_s.nextval into l_qid_pab_item from dual;
563
564 insert into msc_hub_query
565 (query_id,
566 last_update_date,
567 last_updated_by,
568 creation_date,
569 created_by,
570 last_update_login,
571 number1, -- plan_id
572 number2, --- plan_run_id
573 number3, -- sr_instance_id
574 number4, -- org_id
575 number5, -- inventory_item_id
576 number6, -- vmi_flag
577 number7, -- owning_org_id
578 number8, -- owning_inst_id
579 date3 -- activity_date
580 )
581 select
582 l_qid_pab_item,
583 l_sysdate,1,l_sysdate,1,1,
584 p_plan_id,
585 p_plan_run_id,
586 sd.sr_instance_id,
587 sd.organization_id,
588 sd.inventory_item_id,
589 sd.vmi_flag,
590 decode(sign(sd.organization_id), -1, msc_hub_calendar.get_item_org(p_plan_id, sd.inventory_item_id,
591 decode(sign(sd.sr_instance_id), -1, mp.sr_instance_id, sd.sr_instance_id)),
592 sd.organization_id),
593 decode(sign(sd.sr_instance_id), -1, mp.sr_instance_id, sd.sr_instance_id),
594 l.date3
595 from msc_hub_query l,
596 (select unique
597 mdf.plan_id,
598 mdf.sr_instance_id,
599 mdf.organization_id,
600 mdf.inventory_item_id,
601 nvl(mdf.vmi_flag,0) vmi_flag
602 from msc_demands_f mdf
603 where mdf.plan_id= p_plan_id
604 and mdf.plan_run_id = p_plan_run_id
605 and mdf.aggr_type=0
606 union
607 select unique
608 msf.plan_id,
609 msf.sr_instance_id,
610 msf.organization_id,
611 msf.inventory_item_id,
612 nvl(msf.vmi_flag,0) vmi_flag
613 from msc_supplies_f msf
614 where msf.plan_id = p_plan_id
615 and msf.plan_run_id = p_plan_run_id
616 and msf.aggr_type=0) sd,
617 msc_plans mp
618 where l.query_id = l_qid_last_date
619 and sd.plan_id = mp.plan_id
620 union
621 select l_qid_pab_item,
622 l_sysdate,1,l_sysdate,1,1,
623 p_plan_id,
624 p_plan_run_id,
625 f.number3,
626 f.number4,
627 f.number5,
628 f.number6,
629 f.number7,
630 f.number8,
631 f.date3 --- activity date
632 from msc_hub_query f where f.query_id=l_qid_sd_item;
633
634 --dbms_output.put_line(l_stmt_id||', l_qid_pab_item='||l_qid_pab_item||', count='||sql%rowcount);
635 commit;
636
637 --------------------------------------------------------------------
638 --- now, calculate pab and supply/demand
639 --- pab and supply/demand is put at the last work date of the bucket
640 --- we will later move pab to last date of the bucket
641 --------------------------------------------------------------------
642
643
644 l_stmt_id :=60;
645 select msc_hub_query_s.nextval into l_qid_pab from dual;
646
647
648 insert into msc_hub_query (
649 query_id,
650 last_update_date,
651 last_updated_by,
652 creation_date,
653 created_by,
654 last_update_login,
655 number1, -- plan_id
656 number2, --- plan_run_id
657 number3, -- sr_instance_id
658 number4, -- organization_id
659 number5, --- inventory_item_id
660 number6, -- vmi flag
661 date3 , -- last work date,
662 --------------------------------------------------
663 number10, -- pab
664 -----------------------------------------
665 number11, -- total demand
666 number12, -- total supply
667 number13, -- planned order qty
668 number14, -- indep_demand_qty
669 number15 , --- indep_demand_value
670 number16, -- total dep demand
671 number17, --sales order value
672 number18, -- return order value
673 number19, -- make order qty
674 number20, -- make order leadtime
675 number21, -- make order count
676 number23, -- item leadtime
677 number24, -- on hand
678 number25 , -- Scheduled_rept_qty
679 number22 , --- forecast qty,
680 number9 -- in drp, some supply (1,2,51) is also a demand
681 )
682 select
683 l_qid_pab,
684 l_sysdate,1,l_sysdate,1,1,
685 p_plan_id,
686 p_plan_run_id,
687 s.sr_instance_id,
688 s.organization_id,
689 s.inventory_item_id,
690 s.vmi_flag,
691 s.last_work_date,
692 ------------------------------------------------
693 SUM(nvl(s.pab_supply ,0)- nvl(d.pab_demand ,0)-nvl(s.drp_supply_as_demand,0)) --- drp case
694 OVER (PARTITION BY s.plan_id,s.plan_run_id,
695 s.sr_instance_id,s.organization_id,s.inventory_item_id
696 ORDER by s.last_work_date) pab_qty ,
697 -------------------------------------------------------
698 d.total_demand,
699 s.total_supply,
700 s.planned_order_qty,
701 d.total_indep_Demand_qty,
702
703 ---- make sure this indep_demand_value is qty * std_cost. this is used to calculate
704 ---- cogs =item std cost x sum of Indep dem qty
705 ---d.total_indep_demand_value, -- qty * std_Cost
706 d.total_indep_Demand_qty * msi.standard_cost,
707
708 d.total_dep_demand_qty,
709 d.sales_order_Qty * (nvl(msi.list_price,0) -(1-nvl(msi.AVERAGE_DISCOUNT,0)/100)) sales_order_value,
710 s.return_order_qty * (nvl(msi.list_price,0) -(1-nvl(msi.AVERAGE_DISCOUNT,0)/100)) return_order_value,
711 s.make_order_qty,
712 s.work_order_leadtime,
713 s.work_order_count,
714 msi.FIXED_LEAD_TIME ,
715 s.onhand_Qty,
716 s.Scheduled_rept_qty,
717 d.forecast_qty,
718 s.drp_supply_as_demand
719 from
720 (select mfq.number1 plan_id,
721 mfq.number2 plan_run_id,
722 mfq.number3 sr_instance_id,
723 mfq.number4 organization_id,
724 mfq.number5 inventory_item_id,
725 mfq.number6 vmi_flag , ---- nvl(msf.vmi_flag,0) vmi_flag,
726 mfq.number7 owning_org_id,
727 mfq.number8 owning_inst_id,
728 mfq.date3 last_work_date,
729 sum(decode(nvl(msf.supply_type,0),
730 4,0,
731 0,0,
732 nvl(msf.supply_qty,0))) pab_supply,
733
734 --- exclude onhand from total supply for drp
735 sum(decode(mps.plan_type,5,decode(msf.supply_type,18,0,nvl(msf.supply_qty,0)) ,
736 nvl(msf.supply_qty,0)) ) total_supply,
737
738
739 /* ms.source_organization_id <> ms.organization_id
740 and (ms.order_type <> PURCH_REQ or
741 (ms.order_type = PURCH_REQ and ms.supplier_id is not null))*/
742
743
744 sum(nvl(msf.drp_supply_as_demand,0)) drp_supply_as_demand,
745
746 sum(mfq.number11) days_in_bkt,
747 sum(decode(nvl(msf.supply_type,0),
748 5,nvl(msf.supply_qty,0),
749 0)) planned_order_qty,
750
751 sum(nvl(msf.work_order_Qty,0)) make_order_qty,
752 -- return order in srp is defined
753 -- as order_type in (1,2,18) and nvl(item_type_id,401) = 401 and nvl(item_type_value,1) = 2
754
755 sum(nvl(msf.return_order_qty,0)) return_order_qty,
756 sum(nvl(msf.work_order_leadtime,0)) work_order_leadtime,
757 sum(nvl(msf.work_order_count,0)) work_order_count,
758 sum(decode(nvl(msf.supply_type,0),
759 18, nvl(msf.supply_qty,0),
760 0)) onhand_qty,
761 sum(decode(nvl(msf.supply_type,0),
762 1,nvl(msf.supply_qty,0),
763 2,nvl(msf.supply_qty,0),
764 3,nvl(msf.supply_qty,0),
765 8,nvl(msf.supply_qty,0),
766 11,nvl(msf.supply_qty,0),
767 12,nvl(msf.supply_qty,0),
768 14,nvl(msf.supply_qty,0), --- bug 6797566 include work oder co/by product
769 0)) Scheduled_rept_qty
770 from msc_supplies_f msf,msc_hub_query mfq,msc_plans mps
771 where mfq.number1 = msf.plan_id(+)
772 and mfq.number2 = msf.plan_run_id(+)
773 and mfq.number3 = msf.sr_instance_id(+)
774 and mfq.number4 = msf.organization_id(+)
775 and mfq.number5 = msf.inventory_item_id(+)
776 and mfq.date3 = msf.supply_date(+)
777 and msf.aggr_type(+)=0
778 and mfq.query_id =l_qid_pab_item --- calculate at activity date and last work day
779 and mps.plan_id= mfq.number1
780 group by
781 mfq.number1,
782 mfq.number2,
783 mfq.number3,
784 mfq.number4,
785 mfq.number5,
786 mfq.number6,
787 mfq.number7,
788 mfq.number8,
789 mfq.date3
790 ) s,
791 (select mfq1.number1 plan_id,
792 mfq1.number2 plan_run_id,
793 mfq1.number3 sr_instance_id,
794 mfq1.number4 organization_id,
795 mfq1.number5 inventory_item_id,
796 mfq1.number6 vmi_flag , ---- nvl(mdf.vmi_flag,0) vmi_flag,
797 mfq1.number7 owning_org_id,
798 mfq1.number8 owning_inst_id,
799 mfq1.date3 last_work_date,
800
801 sum( decode(mpd.plan_type,5,
802 decode(nvl(mdf.order_type,0),
803 0,0,
804 -1,0,
805 -29,decode(mfq1.number4,-23453,0,nvl(mdf.demand_qty,0)), -- exclude global f/c
806 -31,0,
807 nvl(mdf.demand_qty,0)),
808 decode(nvl(mdf.order_type,0),
809 0,0,
810 -5,0,
811 -22,0,
812 -29,decode(mfq1.number4,-23453,0,nvl(mdf.demand_qty,0)), -- exclude global f/c
813 -31,0, --exclude safety stock demand
814 nvl(mdf.demand_qty,0)))) pab_demand,
815
816 -- for drp plan
817 ---
818 -- work order,INTER_ORG_DEMAND (based on order_date), exclude planned order
819 -- supply , exclude onhand(18)
820 --- supply type 1,2,51 also count as demand
821 -- decode(ms.order_type, PLANNED_ARRIVAL, PLANNED_SHIPMENT_OFF,
822 -- PURCHASE_ORDER,PLANNED_SHIPMENT_OFF,
823 -- PURCH_REQ,PLANNED_SHIPMENT_OFF)
824
825 --pab= total_suply+onhand-total_demand
826
827 sum(decode(mpd.plan_type,5,
828 decode(nvl(mdf.order_type,0),
829 0,0,
830 -1,0,
831 -29,decode(mfq1.number4,-23453,0,nvl(mdf.demand_qty,0)),
832 -31,0,
833 nvl(mdf.demand_qty,0)),
834 decode(nvl(mdf.order_type,0),
835 0,0,
836 -29,decode(mfq1.number4,-23453,0,nvl(mdf.demand_qty,0)),
837 -31,0,
838 nvl(mdf.demand_qty,0)))) total_demand,
839
840
841 sum(nvl(mdf.INDEP_DEMAND_QTY,0) ) total_indep_demand_qty,
842 ---- make sure this indep_demand_value is qty * std_cost. this is used to calculate
843 ---- cogs =item std cost x sum of Indep dem qty
844
845 --sum(nvl(INDEP_DEMAND_QTY,0) * msi.standard_cost) total_indep_demand_value,
846 sum(decode(nvl(mdf.order_type,0),
847 -1,decode(mpd.plan_type,5,0,nvl(mdf.demand_qty,0)), -- exclude drp planned demand from dep demand
848 -2,nvl(mdf.demand_qty,0),
849 -3,nvl(mdf.demand_qty,0),
850 -4,nvl(mdf.demand_qty,0),
851 -24,nvl(mdf.demand_qty,0),
852 -25,nvl(mdf.demand_qty,0),
853 0)) total_dep_demand_qty,
854 sum(decode(nvl(mdf.order_type,0),
855 -30,nvl(mdf.demand_qty,0),
856 0)) sales_order_Qty,
857 sum(decode(nvl(mdf.order_type,0),
858 -29,nvl(mdf.demand_qty,0),
859 0)) forecast_Qty
860 from msc_demands_f mdf,msc_hub_query mfq1,
861 msc_plans mpd
862 where mfq1.number1 = mdf.plan_id(+)
863 and mfq1.number2 = mdf.plan_run_id(+)
864 and mfq1.number3 = mdf.sr_instance_id(+)
865 and mfq1.number4 = mdf.organization_id(+)
866 and mfq1.number5 = mdf.inventory_item_id(+)
867 and mfq1.date3 = mdf.order_date(+)
868 and mdf.aggr_type(+)=0
869 and mfq1.query_id = l_qid_pab_item --- calculate at activity date and last work day
870 and mpd.plan_id=mfq1.number1 --- plan_id
871 group by
872 mfq1.number1,
873 mfq1.number2,
874 mfq1.number3,
875 mfq1.number4,
876 mfq1.number5,
877 mfq1.number6,
878 mfq1.number7,
879 mfq1.number8,
880 mfq1.date3
881 ) d,
882 msc_system_items msi
883 where d.plan_id = s.plan_id
884 and d.plan_run_id = s.plan_run_id
885 and d.sr_instance_id = s.sr_instance_id
886 and d.organization_id = s.organization_id
887 and d.inventory_item_id = s.inventory_item_id
888 and d.last_work_date = s.last_work_date
889 and d.plan_id = msi.plan_id
890 and d.owning_inst_id = msi.sr_instance_id
891 and d.owning_org_id = msi.organization_id
892 and d.inventory_item_id = msi.inventory_item_id;
893
894 --dbms_output.put_line(l_stmt_id||', l_qid_pab='||l_qid_pab||', count='||sql%rowcount);
895 commit;
896
897 -----------------------------------------------------------
898 -- get safety stock item
899 -- we only calculate ss for items in msc_safety_stocks
900 --
901 ------------------------------------------------------------
902
903 l_stmt_id :=70;
904 select msc_hub_query_s.nextval into l_qid_ss_item from dual;
905
906 insert into msc_hub_query
907 (query_id,
908 last_update_date,
909 last_updated_by,
910 creation_date,
911 created_by,
912 last_update_login,
913 number1, -- plan_id
914 number2, --- plan_run_id
915 number3, -- sr_instance_id
916 number4, -- org_id
917 number5, -- inventory_item_id
918 number6,
919 date1, ---- bkt start date,
920 date2, -- bkt end date,
921 date3, --- last work date,
922 date4, -- ss period
923 date5,
924 number10,
925 number11
926 )
927 select unique
928 l_qid_ss_item,
929 l_sysdate,1,l_sysdate,1,1,
930 p_plan_id,
931 p_plan_run_id,
932 mss.sr_instance_id,
933 mss.organization_id,
934 mss.inventory_item_id,
935 nvl(vmi.number6,0) vmi_flag,
936 f2.date1, -- bkt_start_date
937 f2.date2, -- bkt_end_date,
938 f2.date3,
939 f2.date4,
940 f2.date5,
941 f2.number10,
942 f2.number11
943 from msc_safety_stocks mss,
944 (select distinct
945 t_bucket.date1, -- bkt_start_date,
946 t_bucket.date2, -- bkt_end_date
947 t_bucket.date3, -- last work date
948 t_bucket.date4,
949 t_bucket.date5,
950 t_bucket.number10, --bkt_type
951 t_bucket.number11 --days in bucket
952 from msc_hub_query t_bucket, msc_hub_query t_last_date
953 where t_bucket.query_id=l_qid_bucket
954 and t_last_date.query_id=l_qid_last_date
955 and t_bucket.date1=t_last_date.date2) f2,
956 msc_hub_query vmi
957 where mss.plan_id =p_plan_id
958 and mss.plan_id = vmi.number1(+)
959 and mss.sr_instance_id = vmi.number3(+)
960 and mss.organization_id = vmi.number4(+)
961 and mss.inventory_item_id = vmi.number5(+)
962 and vmi.query_id (+) =l_qid_vmi_item;
963
964 --dbms_output.put_line(l_stmt_id||', l_qid_ss_item='||l_qid_ss_item||', count='||sql%rowcount);
965 commit;
966
967 --------------------------------------------------------------------
968 --- now calculate ss, ss is distributed to bucket start date based on
969 -- rows in msc_safety_stocks
970 -- inventory_level,ss,il is then moved to last date of week/period/month
971 --------------------------------------------------------------------
972
973
974 l_stmt_id :=80;
975 select msc_hub_query_s.nextval into l_qid_ss from dual;
976
977
978 insert into msc_hub_query (
979 query_id,
980 last_update_date,
981 last_updated_by,
982 creation_date,
983 created_by,
984 last_update_login,
985 number1, -- plan_id
986 number2, --- plan_run_id
987 number3, -- sr_instance_id
988 number4, -- organization_id
989 number5, --- inventory_item_id
990 number6, -- vmi flag
991 date1,
992 date2,
993 date3,
994 number10, -- ss
995 number11 -- user enter ss
996 )
997
998
999 select
1000 l_qid_ss,
1001 l_sysdate,1,l_sysdate,1,1,
1002 p_plan_id,
1003 p_plan_run_id,
1004 f.number3 sr_instance_id,
1005 f.number4 organization_id,
1006 f.number5 inventory_item_id,
1007 f.number6 vmi_flag,
1008 f.date1 bkt_start_date,
1009 f.date2 bkt_end_date,
1010 f.date3 last_work_date,
1011 LAST_VALUE(mss.SAFETY_STOCK_QUANTITY ignore nulls)
1012 OVER (PARTITION BY f.number1,f.number3,f.number4,f.number5
1013 ORDER by f.date1) safety_stock_qty ,
1014 LAST_VALUE(mss.user_defined_safety_stocks ignore nulls)
1015 OVER (PARTITION BY f.number1,f.number3,f.number4,f.number5
1016 ORDER by f.date1) user_defined_safety_stock_qty
1017 from msc_safety_stocks mss,
1018 msc_hub_query f
1019 where f.query_id=l_qid_ss_item
1020 and f.number1 = mss.plan_id(+)
1021 and f.number3 = mss.sr_instance_id(+)
1022 and f.number4 = mss.organization_id(+)
1023 and f.number5 = mss.inventory_item_id(+)
1024 and f.date4 = mss.period_start_date(+) ;
1025
1026 --dbms_output.put_line(l_stmt_id||', l_qid_ss='||l_qid_ss||', count='||sql%rowcount);
1027 commit;
1028
1029 ---------------------------------------------------------------------------
1030 --- calculate daily demand
1031 ---------------------------------------------------------------------------
1032 l_stmt_id :=90;
1033 select msc_hub_query_s.nextval into l_qid_others from dual;
1034
1035
1036 insert into msc_hub_query (
1037 query_id,
1038 last_update_date,
1039 last_updated_by,
1040 creation_date,
1041 created_by,
1042 last_update_login,
1043 number1, -- plan_id
1044 number2, --- plan_run_id
1045 number3, -- sr_instance_id
1046 number4, -- organization_id
1047 number5, -- inventory_item_id
1048 number10 --- average daily demand
1049 )
1050 select
1051 l_qid_others,
1052 l_sysdate,1,l_sysdate,1,1,
1053 p_plan_id,
1054 p_plan_run_id,
1055 mdf.sr_instance_id,
1056 mdf.organization_id,
1057 mdf.inventory_item_id,
1058 sum(nvl(mdf.demand_qty,0)) / l_plan_days
1059 from msc_Demands_f mdf
1060 where mdf.plan_id = p_plan_id
1061 and mdf.plan_run_id = p_plan_run_id
1062 group by
1063 l_qid_others,
1064 l_sysdate,1,l_sysdate,1,1,
1065 p_plan_id,
1066 p_plan_run_id,
1067 mdf.sr_instance_id,
1068 mdf.organization_id,
1069 mdf.inventory_item_id;
1070
1071 --dbms_output.put_line(l_stmt_id||', l_qid_others='||l_qid_others||', count='||sql%rowcount);
1072 commit;
1073
1074 --------------------------------------------------------------------------------
1075 --- insert pab,ss,min/max inventory into msc_item_inventory_f table
1076 -------------------------------------------------------------------------------
1077
1078 l_stmt_id :=100;
1079
1080 insert into msc_item_inventory_f (
1081 CREATED_BY,
1082 CREATION_DATE,
1083 LAST_UPDATED_BY,
1084 LAST_UPDATE_DATE,
1085 LAST_UPDATE_LOGIN,
1086 PROGRAM_ID,
1087 PROGRAM_LOGIN_ID,
1088 PROGRAM_APPLICATION_ID,
1089 REQUEST_ID,
1090 ----------------------------
1091 plan_id,
1092 plan_run_id,
1093 io_plan_flag,
1094 sr_instance_id,
1095 organization_id,
1096 owning_inst_id,
1097 owning_org_id,
1098 inventory_item_id,
1099 ship_method,
1100 vmi_flag,
1101 order_date,
1102 aggr_type, category_set_id, sr_category_id,
1103 pab_qty,
1104 pab_value,
1105 pab_value2,
1106 safety_Stock_qty,
1107 min_inventory_level,
1108 max_inventory_level,
1109 avg_daily_demand,
1110 -----------------------------------
1111 SUPPLY_CHAIN_COST ,
1112 SUPPLY_CHAIN_COST2,
1113 REVENUE ,
1114 REVENUE2,
1115 MANUFACTURING_COST ,
1116 MANUFACTURING_COST2 ,
1117 TRANSPORTATION_COST,
1118 TRANSPORTATION_COST2 ,
1119 purchasing_cost ,
1120 purchasing_cost2,
1121 carrying_cost,
1122 carrying_cost2,
1123 GROSS_MARGIN,
1124 GROSS_MARGIN2,
1125 inv_build_target)
1126 select
1127 l_user_id,
1128 l_sysdate,
1129 l_user_id,
1130 l_sysdate,
1131 l_user_login_id,
1132 l_program_id,
1133 l_cp_login_id,
1134 l_appl_id,
1135 l_request_id,
1136 pab_tbl.plan_id,
1137 pab_tbl.plan_run_id,
1138 decode(mp_tbl.plan_type,4,1,9,1,0) io_plan_flag,
1139
1140 decode(pab_tbl.organization_id, -1, -23453, pab_tbl.sr_instance_id) sr_instance_id,
1141 decode(pab_tbl.organization_id, -1, -23453, pab_tbl.organization_id) organization_id,
1142
1143 decode(sign(pab_tbl.sr_instance_id), -1, mp_tbl.sr_instance_id, pab_tbl.sr_instance_id) owing_inst_id,
1144 decode(sign(pab_tbl.organization_id),
1145 -1, msc_hub_calendar.get_item_org(p_plan_id, pab_tbl.inventory_item_id,
1146 decode(sign(pab_tbl.sr_instance_id), -1, mp_tbl.sr_instance_id, pab_tbl.sr_instance_id)),
1147 pab_tbl.organization_id) owing_inst_id,
1148
1149 pab_tbl.inventory_item_id,
1150 pab_tbl.ship_method,
1151 pab_tbl.vmi_flag,
1152 pab_tbl.order_date,
1153 to_number(0) aggr_type,
1154 to_number(-23453) category_set_id,
1155 to_number(-23453) sr_category_id,
1156 sum(pab_tbl.pab_qty) pab_qty, --- sum(decode(sign(pab_tbl.pab_qty),-1,0,pab_tbl.pab_qty)),
1157 sum(pab_tbl.pab_value) pab_value, -- sum(decode(sign(pab_tbl.pab_qty),-1,0,pab_tbl.pab_value)),
1158 sum((pab_tbl.pab_value) *decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
1159 nvl(mcc.CONV_RATE,0))) pab_value2,
1160 sum(pab_tbl.safety_Stock_qty) safety_Stock_qty ,
1161 sum(pab_tbl.min_inventory_level) min_inventory_level,
1162 sum(pab_tbl.max_inventory_level) max_inventory_level,
1163 sum(pab_tbl.avg_daily_demand) avg_daily_demand,
1164 ---------------------------------------------------------------------
1165
1166 sum(pab_tbl.total_cost),
1167 sum(pab_tbl.total_cost * decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
1168 nvl(mcc.CONV_RATE,0))),
1169 sum(pab_tbl.revenue),
1170 sum(pab_tbl.revenue * decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
1171 nvl(mcc.CONV_RATE,0))),
1172 sum(pab_tbl.mfg_cost),
1173 sum(pab_tbl.mfg_cost* decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
1174 nvl(mcc.CONV_RATE,0))),
1175 sum(pab_tbl.tp_cost),
1176 sum(pab_tbl.tp_cost * decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
1177 nvl(mcc.CONV_RATE,0))),
1178 sum(pab_tbl.po_cost) ,
1179 sum(pab_tbl.po_cost * decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
1180 nvl(mcc.CONV_RATE,0))),
1181 sum(pab_tbl.carrying_cost),
1182 sum(pab_tbl.carrying_cost * decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
1183 nvl(mcc.CONV_RATE,0))),
1184 sum(pab_tbl.revenue- pab_tbl.total_cost),
1185 sum((pab_tbl.revenue- pab_tbl.total_cost) *decode(pab_tbl.currency_code,fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),1,
1186 nvl(mcc.CONV_RATE,0))),
1187
1188 sum(pab_tbl.inv_build_target)
1189 from
1190 (select
1191 pab.plan_id,
1192 pab.plan_run_id,
1193 pab.sr_instance_id,
1194 pab.organization_id,
1195 pab.inventory_item_id,
1196 '-23453' ship_method,
1197 pab.vmi_flag,
1198 nvl(mtp.currency_code, l_owning_currency_code) currency_code,
1199
1200 pab.order_date,
1201 --------------------------------------------------------------
1202 pab.pab_qty,
1203 pab.pab_qty*m1.standard_cost pab_value,
1204 --------------------------------------------------------------
1205 to_number(null) safety_stock_qty, -- ss
1206 to_number(null) min_inventory_level, -- min level
1207 to_number(null) max_inventory_level,
1208 -------------------------------------------------------------
1209 to_number(null) avg_daily_demand,
1210
1211 to_number(null) revenue,
1212 to_number(null) mfg_cost,
1213 to_number(null) po_cost,
1214 to_number(null) tp_cost,
1215 to_number(null) carrying_cost,
1216 to_number(null) total_cost,
1217 to_number(null) inv_build_target
1218
1219 from
1220 (select
1221 p.number1 plan_id,
1222 p.number2 plan_run_id,
1223 p.number3 sr_instance_id,
1224 p.number4 organization_id,
1225 p.number5 inventory_item_id,
1226 p.number6 vmi_flag,
1227 l.date1 order_date,
1228 p.date3 pab_acvivity_date,
1229 LAST_VALUE(p.number10 ignore nulls)
1230 OVER (PARTITION BY p.number1,p.number2,p.number3,
1231 p.number4,p.number5
1232 ORDER by p.date3) pab_qty
1233 from msc_hub_query l,msc_hub_query p
1234 where l.query_id =l_qid_last_date and p.query_id=l_qid_pab
1235 and l.date3 = p.date3) pab,
1236 msc_system_items m1,
1237 msc_trading_partners mtp,
1238 msc_plans mp --- bug
1239 where pab.plan_id = m1.plan_id(+)
1240 and pab.sr_instance_id = m1.sr_instance_id(+)
1241 and pab.organization_id = m1.organization_id(+)
1242 and pab.inventory_item_id = m1.inventory_item_id(+)
1243 and pab.sr_instance_id = mtp.sr_instance_id(+)
1244 and pab.organization_id = mtp.sr_tp_id(+)
1245 and mtp.partner_type(+) = 3
1246 and pab.plan_id= mp.plan_id --- exclude sno plan since sno plan, pab is from msc
1247 and mp.plan_type<>6 --- msc_bis_inv_detail.pab column
1248
1249 union all
1250 select
1251 ss.number1 plan_id,
1252 ss.number2 lan_run_id,
1253 ss.number3 sr_instance_id,
1254 ss.number4 organization_id,
1255 ss.number5 inventory_item_id,
1256 '-23453' ship_method,
1257 ss.number6 vmi_flag,
1258 nvl(ss_mtp.currency_code, l_owning_currency_code) currency_code,
1259 ss_last.date1 order_date,
1260 ----------------------------------------------------------------------------------------
1261 to_number(null) pab_qty,
1262 to_number(null) pab_value,
1263
1264 LAST_VALUE(ss.number10 ignore nulls)
1265 OVER (PARTITION BY ss.number1,ss.number3,
1266 ss.number4,ss.number5
1267 ORDER by ss.date1) safety_stock_qty,
1268
1269
1270 to_number(null) min_inventory_level,
1271 to_number(null) max_inventory_level,
1272 ---------------------------------------------------------------------------------
1273 to_number(null) avg_daily_demand,
1274
1275 to_number(null) revenue,
1276 to_number(null) mfg_cost,
1277 to_number(null) po_cost,
1278 to_number(null) tp_cost,
1279 to_number(null) carrying_cost,
1280 to_number(null) total_cost,
1281 to_number(null) inv_build_target
1282 from msc_hub_query ss,
1283 msc_hub_query ss_last,
1284 msc_trading_partners ss_mtp
1285 where ss.query_id = l_qid_ss
1286 and ss_last.query_id =l_qid_last_date
1287 and ss_last.date2 = ss.date1 -- for each last day,pick out its bkt_start_date
1288 and ss.number3 = ss_mtp.sr_instance_id
1289 and ss.number4 = ss_mtp.sr_tp_id
1290 and ss_mtp.partner_type = 3
1291
1292 union all
1293
1294 ------------------------------------------------------------------------------------
1295 --- in msc_inventory_level, even if it is day bucket, if it is
1296 --- not a working day, there is no row for it. in such case
1297 --- we use the previous working day's value of the no-working day bucket inventory value
1298 --- see bug 6706755
1299 --- attention: with this, we will not pick up inventory value on not working day in
1300 --- msc_inventory_level ???
1301 /*
1302
1303 here is the very trick part. for min inventory level. in msc_inventory_level table
1304 if it is not a working day, even if it is a day bucket, there is no value for the
1305 day in msc_inventory_level. however, there could be safety stock value for the day
1306 since msc_safety_stock.period_start_date is not aligned with bkt start date
1307 so for min inventory level (day bucket, non working day), we first get min_quantity
1308 from msc_inventory_level for the previous working day and if specified, use it
1309 otherwise, get the ss qty for the bucket day
1310 */
1311 ---------------------------------------------------------------------------------------
1312 select
1313 mil1.plan_id plan_id,
1314 mil1.plan_run_id plan_run_id,
1315 mil1.sr_instance_id sr_instance_id,
1316 mil1.organization_id organization_id,
1317 mil1.inventory_item_id inventory_item_id,
1318 '-23453' ship_method,
1319 mil1.vmi_flag vmi_flag,
1320 nvl(mil_mtp.currency_code, l_owning_currency_code) currency_code,
1321 mil_last.date1 order_date,
1322 ----------------------------------------------------------------------------
1323 to_number(null) pab_qty,
1324 to_number(null) pab_value,
1325 ----------------------------------------------------------------------------
1326 to_number(null) safety_stock_qty, -- ss
1327 nvl(mil1.min_inventory_level,mil_ss.number10) min_inventory_level,
1328 mil1.max_inventory_level max_inventory_level,
1329 ---------------------------------------------------------------------------------
1330 to_number(null) avg_daily_demand,
1331
1332 to_number(null) revenue,
1333 to_number(null) mfg_cost,
1334 to_number(null) po_cost,
1335 to_number(null) tp_cost,
1336 to_number(null) carrying_cost,
1337 to_number(null) total_cost,
1338 to_number(null) inv_build_target
1339
1340 from msc_hub_query mil_ss,
1341 msc_hub_query mil_last,
1342 msc_trading_partners mil_mtp,
1343 (select
1344 item.number1 plan_id,
1345 item.number2 plan_run_id,
1346 item.number3 sr_instance_id,
1347 item.number4 organization_id,
1348 item.number5 inventory_item_id,
1349 item.number6 vmi_flag,
1350 item.date1 order_date, -- move to bkt start date,may not a working day
1351 nvl(mil.MIN_QUANTITY,mil_msi.MIN_MINMAX_QUANTITY) min_inventory_level,
1352 nvl(mil.max_quantity, mil_msi.MAX_MINMAX_QUANTITY) max_inventory_level
1353 from msc_inventory_levels mil,
1354 msc_system_items mil_msi,
1355 msc_hub_query item
1356 where item.query_id = l_qid_mil_item
1357 and item.number1 = mil.plan_id(+)
1358 and item.number3 = mil.sr_instance_id(+)
1359 and item.number4 = mil.organization_id(+)
1360 and item.number5 = mil.inventory_item_id(+)
1361 and item.date5 = mil.inventory_date(+) -- if inventory_level is at 07/13,
1362 --- move to 07/15(not working day) for day bucket only
1363 and mil_msi.plan_id = item.number1
1364 and mil_msi.sr_instance_id =item.number3
1365 and mil_msi.organization_id = item.number4
1366 and mil_msi.inventory_item_id = item.number5 ) mil1
1367
1368 where mil_ss.query_id (+)= l_qid_ss
1369 and mil_ss.number1 (+)= mil1.plan_id
1370 and mil_ss.number2 (+) = mil1.plan_run_id
1371 and mil_ss.number3(+) = mil1.sr_instance_id
1372 and mil_ss.number4(+) = mil1.organization_id
1373 and mil_ss.number5(+) = mil1.inventory_item_id
1374 and mil_ss.number6(+) = mil1.vmi_flag
1375 and mil_ss.date1 (+) = mil1.order_date --- pick the ss value for the bkt start date
1376 and mil_last.query_id = l_qid_last_date
1377 and mil_last.date2 = mil1.order_date
1378 and mil1.sr_instance_id = mil_mtp.sr_instance_id
1379 and mil1.organization_id = mil_mtp.sr_tp_id
1380 and mil_mtp.partner_type = 3
1381
1382 union all
1383 select
1384 others.number1 plan_id, -- plan_id
1385 others.number2 plan_run_id, --- plan_run_id
1386 others.number3 sr_instance_id, -- sr_instance_id
1387 others.number4 organization_id, -- organization_id
1388 others.number5 inventory_item_id, --- inventory_item_id
1389 '-23453' ship_method,
1390 nvl(others_vmi.number6,0) vmi_flag, -- vmi flag
1391 nvl(mtp2.currency_code, l_owning_currency_code) currency_code,
1392 last_date1.date1 order_date, -- end date
1393 ----------------------------------------------------------------------------
1394 to_number(null) pab_qty,
1395 to_number(null) pab_value,
1396 ----------------------------------------------------------------------------
1397
1398 to_number(null) safety_stock_qty, -- ss
1399 to_number(null) min_inventory_level, -- min level
1400 to_number(null) max_inventory_level,
1401 ----------------------------------------------------------------------------
1402 others.number10 avg_daily_demand,
1403 ----------------------------------------------------------------------------
1404
1405 to_number(null) revenue,
1406 to_number(null) mfg_cost,
1407 to_number(null) po_cost,
1408 to_number(null) tp_cost,
1409 to_number(null) carrying_cost,
1410 to_number(null) total_cost,
1411 to_number(null) inv_build_target
1412
1413 from msc_hub_query others,
1414 msc_hub_query others_vmi,
1415 msc_hub_query last_date1,
1416 msc_trading_partners mtp2
1417 where last_date1.query_id =l_qid_last_date
1418 and others_vmi.query_id(+) =l_qid_vmi_item
1419 and others.query_id = l_qid_others
1420 and others_vmi.number1(+) =others.number1
1421 and others_vmi.number2(+) =others.number2
1422 and others_vmi.number3(+) =others.number3
1423 and others_vmi.number4(+) =others.number4
1424 and others_vmi.number5(+) =others.number5
1425 and others.number3 = mtp2.sr_instance_id
1426 and others.number4 = mtp2.sr_tp_id
1427 and mtp2.partner_type = 3
1428
1429 union all
1430 select
1431 p_plan_id plan_id,
1432 p_plan_run_id plan_run_id,
1433 mbid.sr_instance_id,
1434 mbid.organization_id,
1435 mbid.inventory_item_id,
1436 nvl(mbid.ship_method, '-23453') ship_method,
1437 nvl(vmi.number6,0) vmi_flag,
1438 nvl(bis_mtp.currency_code, l_owning_currency_code) currency_code,
1439 map.date2 order_date, --- hub week end date
1440 ----------------------------------------------------------------------------
1441 to_number(null) pab_qty,
1442 to_number(null) pab_value,
1443 ----------------------------------------------------------------------------
1444
1445 to_number(null) safety_stock_qty, -- ss
1446 to_number(null) min_inventory_level, -- min level
1447 to_number(null) max_inventory_level,
1448 ----------------------------------------------------------------------------
1449 to_number(null) avg_daily_demand,
1450 -----------------------------------------------------------------------------
1451 decode(mp.plan_type,
1452 6, decode(sign(mbid.mds_price), -1, -mbid.mds_price, 0),
1453 mbid.mds_price) revenue, -- SNO mds_price negative means revenue, positive means demand cost
1454
1455 nvl(mbid.PRODUCTION_COST,0) + nvl(mbid.mds_cost,0) mfg_cost, -- bug 6784517
1456 mbid.purchasing_cost po_cost,
1457 mbid.TRANSPORTATION_COST tp_cost,
1458 mbid.carrying_cost carrying_cost,
1459 nvl(mbid.PRODUCTION_COST,0) + nvl(mbid.mds_cost,0)+ nvl(mbid.purchasing_cost,0) +
1460 nvl(mbid.carrying_cost,0) + nvl(mbid.TRANSPORTATION_COST,0) total_cost,
1461 to_number(null) inv_build_target
1462 ----------------------------------------------------------------------------
1463 from msc_bis_inv_detail mbid,
1464 msc_hub_query vmi,
1465 msc_hub_query map,
1466 msc_trading_partners bis_mtp,
1467 msc_plans mp
1468 where vmi.query_id(+) =l_qid_vmi_item
1469 and map.query_id = l_qid_week_map
1470 and map.date3 = trunc(mbid.detail_date)
1471 and nvl(mbid.detail_level,0)=1 and mp.plan_type<>6
1472 and nvl(mbid.period_type,0)=1
1473 and mbid.plan_id=p_plan_id
1474 and vmi.query_id(+) =l_qid_vmi_item
1475 and vmi.number1(+) = mbid.plan_id
1476 and vmi.number3(+) = mbid.sr_instance_id
1477 and vmi.number4(+) = mbid.organization_id
1478 and vmi.number5(+) = mbid.inventory_item_id
1479 and mbid.sr_instance_id = bis_mtp.sr_instance_id(+)
1480 and mbid.organization_id = bis_mtp.sr_tp_id(+)
1481 and bis_mtp.partner_type(+) = 3
1482 and mbid.plan_id=mp.plan_id
1483
1484 union all
1485 select
1486 p_plan_id plan_id,
1487 p_plan_run_id plan_run_id,
1488 mbid.sr_instance_id,
1489 mbid.organization_id,
1490 mbid.inventory_item_id,
1491 nvl(mbid.ship_method, '-23453') ship_method,
1492 nvl(msi_bis.vmi_flag ,0) vmi_flag,
1493 nvl(bis_mtp.currency_code, l_owning_currency_code) currency_code,
1494 map.date2 order_date, --- hub week end date
1495 ----------------------------------------------------------------------------
1496 ----- inventory build target from sno is not cum value yet.
1497 ----- msc_bis_inv_detail.pab is not a cumulative value. It is calculated for each bucket independently
1498
1499 last_value(mbid.pab ignore nulls) over (partition by mbid.plan_id, mbid.sr_instance_id,
1500 mbid.organization_id, mbid.inventory_item_id,
1501 mbid.ship_method
1502 order by map.date2) pab_qty,
1503 --- assume that msc_bis_inv_detail.pab is only
1504 --- populated in sno plan
1505 last_value(mbid.pab ignore nulls) over (partition by mbid.plan_id, mbid.sr_instance_id,
1506 mbid.organization_id, mbid.inventory_item_id,
1507 mbid.ship_method
1508 order by map.date2) * msi_bis.standard_cost pab_value,
1509 ----------------------------------------------------------------------------
1510
1511 to_number(null) safety_stock_qty, -- ss
1512 to_number(null) min_inventory_level, -- min level
1513 to_number(null) max_inventory_level,
1514 ----------------------------------------------------------------------------
1515 to_number(null) avg_daily_demand,
1516 -----------------------------------------------------------------------------
1517 decode(mp.plan_type,
1518 6, decode(sign(mbid.mds_price), -1, -mbid.mds_price, 0),
1519 mbid.mds_price) revenue, -- SNO mds_price negative means revenue, positive means demand cost
1520
1521 nvl(mbid.PRODUCTION_COST,0) + nvl(mbid.mds_cost,0) mfg_cost, -- bug 6784517
1522 mbid.purchasing_cost po_cost,
1523 mbid.TRANSPORTATION_COST tp_cost,
1524 mbid.carrying_cost carrying_cost,
1525 nvl(mbid.PRODUCTION_COST,0) + nvl(mbid.mds_cost,0)+ nvl(mbid.purchasing_cost,0) +
1526 nvl(mbid.carrying_cost,0) + nvl(mbid.TRANSPORTATION_COST,0) total_cost,
1527 ----- inventory build target from sno is not cum value yet.
1528 ----- msc_bis_inv_detail.pab is not a cumulative value. It is calculated for each bucket independently
1529
1530 last_value(mbid.pab ignore nulls) over (partition by mbid.plan_id, mbid.sr_instance_id,
1531 mbid.organization_id, mbid.inventory_item_id,
1532 mbid.ship_method
1533 order by map.date2) inv_build_target
1534
1535 ----------------------------------------------------------------------------
1536 from
1537 (select p_plan_id plan_id,
1538 mbid1.sr_instance_id,
1539 mbid1.organization_id,
1540 mbid1.inventory_item_id,
1541 nvl(mbid1.ship_method, '-23453') ship_method,
1542 mbid1.detail_date,
1543 sum(nvl(mbid1.mds_price,0)) mds_price,
1544 sum(nvl(mbid1.pab,0)) pab,
1545 sum(nvl(mbid1.PRODUCTION_COST,0)) PRODUCTION_COST,
1546 sum(nvl(mbid1.mds_cost,0) ) mds_cost,
1547 sum(nvl(mbid1.purchasing_cost,0) ) purchasing_cost,
1548 sum(nvl(mbid1.TRANSPORTATION_COST,0) ) TRANSPORTATION_COST,
1549 sum(nvl(mbid1.carrying_cost,0)) carrying_cost
1550 from msc_bis_inv_detail mbid1 where mbid1.plan_id=p_plan_id
1551 group by
1552 p_plan_id,
1553 mbid1.sr_instance_id,
1554 mbid1.organization_id,
1555 mbid1.inventory_item_id,
1556 nvl(mbid1.ship_method, '-23453'),
1557 mbid1.detail_date) mbid,
1558 (select msi_1.plan_id,
1559 msi_1.sr_instance_id,
1560 msi_1.organization_id,
1561 msi_1.inventory_item_id,
1562 msi_1.standard_cost,
1563 nvl(vmi.number6,0) vmi_flag
1564 from msc_system_items msi_1,
1565 msc_hub_query vmi
1566 where vmi.query_id(+) =l_qid_vmi_item
1567 and vmi.number1(+) = msi_1.plan_id
1568 and vmi.number3(+) = msi_1.sr_instance_id
1569 and vmi.number4(+) = msi_1.organization_id
1570 and vmi.number5(+) = msi_1.inventory_item_id) msi_bis ,
1571 msc_hub_query map,
1572 msc_trading_partners bis_mtp,
1573 msc_plans mp
1574 where
1575 --vmi.query_id(+) =l_qid_vmi_item
1576 map.query_id = l_qid_week_map
1577 and map.date3 = trunc(mbid.detail_date)
1578 and mp.plan_type=6
1579 and mbid.plan_id=p_plan_id
1580 and msi_bis.plan_id (+)= mbid.plan_id
1581 and msi_bis.sr_instance_id (+)= mbid.sr_instance_id --- make sure it is out join for customer demand in sno
1582 and msi_bis.organization_id(+) = mbid.organization_id
1583 and msi_bis.inventory_item_id(+) = mbid.inventory_item_id
1584 and mbid.sr_instance_id = bis_mtp.sr_instance_id(+)
1585 and mbid.organization_id = bis_mtp.sr_tp_id(+)
1586 and bis_mtp.partner_type(+) = 3
1587 and mbid.plan_id=mp.plan_id
1588
1589 ) pab_tbl,msc_plans mp_tbl,
1590 MSC_CURRENCY_CONV_MV mcc
1591 where mcc.FROM_CURRENCY(+) =pab_tbl.currency_code --- make sure 'xxx' is not a valid currency code
1592 and mcc.TO_CURRENCY(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
1593 and mcc.CALENDAR_DATE(+) = pab_tbl.order_date
1594 and mp_tbl.plan_id = pab_tbl.plan_id ---
1595 group by
1596 pab_tbl.plan_id,
1597 pab_tbl.plan_run_id,
1598 decode(mp_tbl.plan_type,4,1,9,1,0),
1599 decode(pab_tbl.organization_id, -1, -23453, pab_tbl.sr_instance_id),
1600 decode(pab_tbl.organization_id, -1, -23453, pab_tbl.organization_id),
1601 decode(sign(pab_tbl.sr_instance_id), -1, mp_tbl.sr_instance_id, pab_tbl.sr_instance_id),
1602 decode(sign(pab_tbl.organization_id),
1603 -1, msc_hub_calendar.get_item_org(p_plan_id, pab_tbl.inventory_item_id,
1604 decode(sign(pab_tbl.sr_instance_id), -1, mp_tbl.sr_instance_id, pab_tbl.sr_instance_id)),
1605 pab_tbl.organization_id),
1606 pab_tbl.inventory_item_id,
1607 pab_tbl.ship_method,
1608 pab_tbl.vmi_flag,
1609 pab_tbl.order_date;
1610
1611 --dbms_output.put_line('msc_item_inventory_f, count='||sql%rowcount);
1612 commit;
1613
1614 --------------------------------------------------------------------------------
1615 --- insert supply, demand activity into msc_item_orders_f table
1616 -------------------------------------------------------------------------------
1617
1618 l_stmt_id :=110;
1619
1620 insert into msc_item_orders_f (
1621 CREATED_BY,
1622 CREATION_DATE,
1623 LAST_UPDATED_BY,
1624 LAST_UPDATE_DATE,
1625 LAST_UPDATE_LOGIN,
1626 PROGRAM_ID,
1627 PROGRAM_LOGIN_ID,
1628 PROGRAM_APPLICATION_ID,
1629 REQUEST_ID,
1630 ------------------------------------------------------------------------
1631 PLAN_ID,
1632 PLAN_RUN_ID,
1633 io_plan_flag,
1634 SR_INSTANCE_ID,
1635 ORGANIZATION_ID,
1636 owning_inst_id,
1637 owning_org_id,
1638 INVENTORY_ITEM_ID,
1639 vmi_flag,
1640 ORDER_DATE,
1641 aggr_type, category_set_id, sr_category_id,
1642 ------------------------------------------------------------------------
1643 DEMAND_QTY,
1644 SUPPLY_QTY,
1645 PEGGED_TO_EXCESS_QTY ,
1646 PLANNED_ORDER_QTY,
1647 INDEP_DEMAND_QTY,
1648 INDEP_DEMAND_VALUE,
1649 DEP_DEMAND_QTY,
1650 SALES_ORDER_VALUE,
1651 RETURN_ORDER_VALUE,
1652 MAKE_ORDER_QTY,
1653 MAKE_ORDER_LEADTIME,
1654 MAKE_ORDER_COUNT,
1655 STOCK_OUTS_COUNT,
1656 NO_ACTIVITY_ITEM_COUNT,
1657 DAYS_IN_BKT,
1658 item_leadtime,
1659 avg_daily_demand,
1660 onhand_qty,
1661 Scheduled_rept_qty,
1662 forecast_qty)
1663 select
1664 l_user_id,
1665 l_sysdate,
1666 l_user_id,
1667 l_sysdate,
1668 l_user_login_id,
1669 l_program_id,
1670 l_cp_login_id,
1671 l_appl_id,
1672 l_request_id,
1673 ---------------------------------------------------
1674 p_plan_id,
1675 p_plan_run_id,
1676 decode(mp_tbl.plan_type,4,1,9,1,0) io_plan_flag,
1677 order_tbl.sr_instance_id,
1678 order_tbl.organization_id,
1679 decode(order_tbl.sr_instance_id,-23453, mp_tbl.sr_instance_id,order_tbl.sr_instance_id) owing_inst_id,
1680 decode(order_tbl.organization_id,
1681 -23453,msc_hub_calendar.get_item_org(p_plan_id,order_tbl.inventory_item_id,
1682 decode(order_tbl.sr_instance_id,-23453,mp_tbl.sr_instance_id,
1683 order_tbl.sr_instance_id)),
1684 order_tbl.organization_id) owning_org_id,
1685 order_tbl.inventory_item_id,
1686 order_tbl.vmi_flag,
1687 order_tbl.order_date,
1688 to_number(0) aggr_type,
1689 to_number(-23453) category_set_id,
1690 to_number(-23453) sr_category_id,
1691 --------------------------------------------------
1692 sum(order_tbl.demand_qty),
1693 sum(order_tbl.supply_qty),
1694 sum(order_tbl.qty_pegged_to_excess),
1695 sum(order_tbl.planned_order_qty),
1696 sum(order_tbl.indep_demand_qty),
1697 sum(order_tbl.indep_demand_value),
1698 sum(order_tbl.dep_demand_qty),
1699 sum(order_tbl.sales_order_value),
1700 sum(order_tbl.return_order_value),
1701 sum(order_tbl.make_order_qty),
1702 sum(order_tbl.make_order_leadtime),
1703 sum(order_tbl.make_order_count),
1704 sum(order_tbl.stock_outs_count),
1705 sum(order_tbl.no_activity_item_count),
1706 sum(order_tbl.days_in_bkt),
1707 sum(order_tbl.item_leadtime),
1708 sum(order_tbl.avg_daily_demand),
1709 sum(order_tbl.onhand_Qty),
1710 sum(order_tbl.Scheduled_rept_qty),
1711 sum(order_tbl.forecast_qty)
1712 from
1713 (
1714 select
1715 sd.number1 plan_id, -- plan_id
1716 sd.number2 plan_run_id, --- plan_run_id
1717 sd.number3 sr_instance_id, -- sr_instance_id
1718 sd.number4 organization_id, -- organization_id
1719 sd.number5 inventory_item_id, --- inventory_item_id
1720 sd.number6 vmi_flag, -- vmi flag
1721 sd.date3 order_date,
1722 -------------------------------------------------
1723 sd.number11 + nvl(sd.number9,0) demand_qty, -- total demand -- in drp, supply(1,2,51) is demand
1724 sd.number12 supply_qty, -- total supply
1725 sd.number13 planned_order_qty, -- planned order qty
1726 sd.number14 indep_demand_qty, -- indep_demand_qty
1727 sd.number15 indep_demand_value, --- indep_demand_value
1728 sd.number16 dep_demand_qty, -- total dep demand
1729 sd.number17 sales_order_value, --sales order value
1730 sd.number18 return_order_value, -- return order value
1731 sd.number19 make_order_qty, -- make order qty
1732 sd.number20 make_order_leadtime, -- make order leadtime
1733 sd.number21 make_order_count, -- make order count
1734 sd_item.number11 days_in_bkt , -- days in bucket
1735 sd.number23 item_leadtime,
1736 sd.number24 onhand_Qty,
1737 sd.number25 Scheduled_rept_qty,
1738 sd.number22 forecast_qty, --- forecast qty
1739 ----------------------------------------
1740 nvl(others.number10,0) avg_daily_demand,
1741 ---------------------------------------------------------------
1742 to_number(null) qty_pegged_to_excess,
1743 --------------------------------------------------------------
1744 to_number(null) no_activity_item_count,
1745 to_number(null) stock_outs_count
1746
1747 from msc_hub_query sd,
1748 msc_hub_query sd_item,
1749 msc_hub_query others
1750 where sd.query_id =l_qid_pab
1751 and sd_item.query_id =l_qid_sd_item
1752 and sd.number1 = sd_item.number1
1753 and sd.number2 = sd_item.number2
1754 and sd.number3 = sd_item.number3
1755 and sd.number4 = sd_item.number4
1756 and sd.number5 = sd_item.number5
1757 and sd.date3 = sd_item.date3
1758 and others.query_id(+) = l_qid_others
1759 and others.number1(+)= sd.number1
1760 and others.number2(+)= sd.number2
1761 and others.number3 (+)=sd.number3
1762 and others.number4(+)= sd.number4
1763 and others.number5 (+)= sd.number5 --- note, need outer join since some item may do not have demand(only supply)
1764
1765 union all
1766
1767 select
1768 mfp.plan_id plan_id,
1769 p_plan_run_id plan_run_id,
1770 mfp.sr_instance_id sr_instance_id,
1771 mfp.organization_id organization_id,
1772 mfp.inventory_item_id inventory_item_id,
1773 nvl(peg_vmi.number6,0) vmi_flag,
1774 trunc(nvl(mfp.supply_date,nvl(ms.firm_date,ms.new_schedule_date))) order_date,
1775 -----------------------------------------------------------------------
1776 to_number(null) demand_qty, -- total demand
1777 to_number(null) supply_qty, -- total supply
1778 to_number(null) planned_order_qty, -- planned order qty
1779 to_number(null) indep_demand_qty, -- indep_demand_qty
1780 to_number(null) indep_demand_value, --- indep_demand_value
1781 to_number(null) dep_demand_qty, -- total dep demand
1782 to_number(null) sales_order_value, --sales order value
1783 to_number(null) return_order_value, -- return order value
1784 to_number(null) make_order_qty, -- make order qty
1785 to_number(null) make_order_leadtime, -- make order leadtime
1786 to_number(null) make_order_count, -- make order count
1787 to_number(null) days_in_bkt , -- days in bucket
1788 to_number(null) item_leadtime,
1789 to_number(null) onhand_Qty,
1790 to_number(null) Scheduled_rept_qty,
1791 to_number(null) forecast_qty,
1792 to_number(null) avg_daily_demand,
1793
1794 ------------------------------------------------------------------------------
1795 sum(mfp.allocated_quantity) qty_pegged_to_excess,
1796 ------------------------------------------------------------------------------
1797 to_number(null) no_activity_item_count ,
1798 to_number(null) stock_outs_count
1799
1800 from msc_full_pegging mfp,
1801 msc_hub_query peg_vmi,
1802 msc_supplies ms
1803 where ms.plan_id=mfp.plan_id
1804 and ms.TRANSACTION_ID = mfp.TRANSACTION_ID
1805 and ms.sr_instance_id = mfp.sr_instance_id
1806 and mfp.plan_id =p_plan_id
1807 and mfp.demand_id=-1
1808 and mfp.plan_id = peg_vmi.number1(+)
1809 and mfp.sr_instance_id = peg_vmi.number3(+)
1810 and mfp.organization_id = peg_vmi.number4(+)
1811 and mfp.inventory_item_id = peg_vmi.number5(+)
1812 and peg_vmi.query_id (+) =l_qid_vmi_item
1813 group by
1814 mfp.plan_id ,
1815 p_plan_run_id ,
1816 mfp.sr_instance_id ,
1817 mfp.organization_id ,
1818 mfp.inventory_item_id ,
1819 nvl(peg_vmi.number6,0) ,
1820 trunc(nvl(mfp.supply_date,nvl(ms.firm_date,ms.new_schedule_date)))
1821 union all
1822 select
1823 plan_id,
1824 plan_run_id,
1825 me.sr_instance_id,
1826 me.ORGANIZATION_ID,
1827 me.INVENTORY_ITEM_ID,
1828 nvl(vmi1.number6,0) vmi_flag,
1829 me.ANALYSIS_DATE order_date, --- bkt_start_date
1830 -----------------------------------------------------------------------
1831 to_number(null) demand_qty, -- total demand
1832 to_number(null) supply_qty, -- total supply
1833 to_number(null) planned_order_qty, -- planned order qty
1834 to_number(null) indep_demand_qty, -- indep_demand_qty
1835 to_number(null) indep_demand_value, --- indep_demand_value
1836 to_number(null) dep_demand_qty, -- total dep demand
1837 to_number(null) sales_order_value, --sales order value
1838 to_number(null) return_order_value, -- return order value
1839 to_number(null) make_order_qty, -- make order qty
1840 to_number(null) make_order_leadtime, -- make order leadtime
1841 to_number(null) make_order_count, -- make order count
1842 to_number(null) days_in_bkt , -- days in bucket
1843 to_number(null) item_leadtime,
1844 to_number(null) onhand_Qty,
1845 to_number(null) Scheduled_rept_qty,
1846 to_number(null) forecast_qty,
1847 to_number(null) avg_daily_demand,
1848
1849 ------------------------------------------------------------------------------
1850 to_number(null) qty_pegged_to_excess,
1851 ----------------------------------------------------------
1852 sum(decode(EXCEPTION_TYPE,5,EXCEPTION_COUNT,0) )no_activity_item_count,
1853 sum(decode(EXCEPTION_TYPE,2,exception_count,0)) stock_outs_count
1854 from msc_exceptions_f me, msc_hub_query vmi1
1855 where me.EXCEPTION_TYPE in (5,2)
1856 and me.plan_id = p_plan_id
1857 and me.plan_run_id = p_plan_run_id
1858 and me.aggr_type=0
1859 and vmi1.number1(+) = me.plan_id
1860 and vmi1.number3(+) = me.sr_instance_id
1861 and vmi1.number4(+) = me.organization_id
1862 and vmi1.number5(+) = me.inventory_item_id
1863 and vmi1.number2(+) = me.plan_run_id
1864 and vmi1.query_id(+)=l_qid_vmi_item
1865 group by
1866 plan_id,
1867 plan_run_id,
1868 me.sr_instance_id,
1869 me.ORGANIZATION_ID,
1870 me.INVENTORY_ITEM_ID,
1871 nvl(vmi1.number6,0),
1872 me.ANALYSIS_DATE
1873
1874 ) order_tbl,msc_plans mp_tbl
1875 where mp_tbl.plan_id = p_plan_id
1876 group by
1877 l_user_id,
1878 l_sysdate,
1879 l_user_id,
1880 l_sysdate,
1881 l_user_login_id,
1882 l_program_id,
1883 l_cp_login_id,
1884 l_appl_id,
1885 l_request_id,
1886 p_plan_id,
1887 p_plan_run_id,
1888 decode(mp_tbl.plan_type,4,1,9,1,0),
1889 order_tbl.sr_instance_id,
1890 order_tbl.organization_id,
1891 decode(order_tbl.sr_instance_id,-23453, mp_tbl.sr_instance_id,order_tbl.sr_instance_id),
1892 decode(order_tbl.organization_id,
1893 -23453,msc_hub_calendar.get_item_org(p_plan_id,order_tbl.inventory_item_id,
1894 decode(order_tbl.sr_instance_id,-23453,mp_tbl.sr_instance_id,
1895 order_tbl.sr_instance_id)),
1896 order_tbl.organization_id) ,
1897 order_tbl.inventory_item_id,
1898 order_tbl.vmi_flag,
1899 order_tbl.order_date;
1900
1901 --dbms_output.put_line('msc_item_orders_f, count='||sql%rowcount);
1902 commit;
1903
1904 populate_summary(errbuf, retcode, p_plan_id, p_plan_run_id);
1905
1906
1907 exception
1908 when no_data_found then
1909
1910 retcode :=2;
1911 --dbms_output.put_line(to_char(SQLCODE) || ':' || SQLERRM || ' in stmt_id=' || l_stmt_id);
1912 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1913 fnd_log.string(fnd_log.LEVEL_UNEXPECTED,
1914 l_api_name,
1915 to_char(SQLCODE) || ':' || SQLERRM || ' in stmt_id=' || l_stmt_id);
1916 end if;
1917
1918 errbuf := SQLERRM;
1919 when others then
1920 --dbms_output.put_line(to_char(SQLCODE) || ':' || SQLERRM || ' in stmt_id=' || l_stmt_id);
1921 retcode :=2;
1922 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1923 fnd_log.string(fnd_log.LEVEL_UNEXPECTED,
1924 l_api_name,
1925 to_char(SQLCODE) || ':' || SQLERRM || ' in stmt_id=' || l_stmt_id);
1926
1927 end if;
1928
1929
1930
1931 errbuf := SQLERRM;
1932 end populate_details;
1933
1934
1935 procedure populate_summary(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 -- level 1
1941 insert into msc_item_inventory_f (
1942 plan_id, plan_run_id, io_plan_flag,
1943 sr_instance_id, organization_id, owning_inst_id, owning_org_id,
1944 inventory_item_id,
1945 ship_method, vmi_flag, order_date,
1946 aggr_type, category_set_id, sr_category_id,
1947 pab_qty,
1948 pab_value,
1949 pab_value2,
1950 safety_stock_qty,
1951 min_inventory_level,
1952 max_inventory_level,
1953 avg_daily_demand,
1954 supply_chain_cost,
1955 supply_chain_cost2,
1956 revenue,
1957 revenue2,
1958 manufacturing_cost,
1959 manufacturing_cost2,
1960 transportation_cost,
1961 transportation_cost2,
1962 purchasing_cost,
1963 purchasing_cost2,
1964 carrying_cost,
1965 carrying_cost2,
1966 gross_margin,
1967 gross_margin2,
1968 inv_build_target,
1969 created_by, creation_date,
1970 last_update_date, last_updated_by, last_update_login,
1971 program_id, program_login_id,
1972 program_application_id, request_id)
1973 -- category (42, 43, 44)
1974 select
1975 f.plan_id, f.plan_run_id, f.io_plan_flag,
1976 f.sr_instance_id, f.organization_id, f.owning_inst_id, f.owning_org_id,
1977 to_number(-23453) inventory_item_id,
1978 f.ship_method, f.vmi_flag, f.order_date,
1979 to_number(42) aggr_type,
1980 l_category_set_id1 category_set_id,
1981 nvl(q.sr_category_id, -23453),
1982 sum(f.pab_qty),
1983 sum(f.pab_value),
1984 sum(f.pab_value2),
1985 sum(f.safety_stock_qty),
1986 sum(f.min_inventory_level),
1987 sum(f.max_inventory_level),
1988 sum(f.avg_daily_demand),
1989 sum(f.supply_chain_cost),
1990 sum(f.supply_chain_cost2),
1991 sum(f.revenue),
1992 sum(f.revenue2),
1993 sum(f.manufacturing_cost),
1994 sum(f.manufacturing_cost2),
1995 sum(f.transportation_cost),
1996 sum(f.transportation_cost2),
1997 sum(f.purchasing_cost),
1998 sum(f.purchasing_cost2),
1999 sum(f.carrying_cost),
2000 sum(f.carrying_cost2),
2001 sum(f.gross_margin),
2002 sum(f.gross_margin2),
2003 sum(f.inv_build_target),
2004 fnd_global.user_id, sysdate,
2005 sysdate, fnd_global.user_id, fnd_global.login_id,
2006 fnd_global.conc_program_id, fnd_global.conc_login_id,
2007 fnd_global.prog_appl_id, fnd_global.conc_request_id
2008 from
2009 msc_item_inventory_f f,
2010 msc_phub_item_categories_mv q
2011 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
2012 and f.aggr_type=0
2013 and f.owning_inst_id=q.sr_instance_id(+)
2014 and f.owning_org_id=q.organization_id(+)
2015 and f.inventory_item_id=q.inventory_item_id(+)
2016 and q.category_set_id(+)=l_category_set_id1
2017 group by
2018 f.plan_id, f.plan_run_id, f.io_plan_flag,
2019 f.sr_instance_id, f.organization_id, f.owning_inst_id, f.owning_org_id,
2020 f.ship_method, f.vmi_flag, f.order_date,
2021 nvl(q.sr_category_id, -23453);
2022
2023 commit;
2024
2025 -- level 1
2026 insert into msc_item_orders_f (
2027 plan_id, plan_run_id, io_plan_flag,
2028 sr_instance_id, organization_id, owning_inst_id, owning_org_id,
2029 inventory_item_id,
2030 vmi_flag, order_date,
2031 aggr_type, category_set_id, sr_category_id,
2032 demand_qty,
2033 supply_qty,
2034 pegged_to_excess_qty ,
2035 planned_order_qty,
2036 indep_demand_qty,
2037 indep_demand_value,
2038 dep_demand_qty,
2039 sales_order_value,
2040 return_order_value,
2041 make_order_qty,
2042 make_order_leadtime,
2043 make_order_count,
2044 stock_outs_count,
2045 no_activity_item_count,
2046 days_in_bkt,
2047 item_leadtime,
2048 avg_daily_demand,
2049 onhand_qty,
2050 scheduled_rept_qty,
2051 forecast_qty,
2052 created_by, creation_date,
2053 last_update_date, last_updated_by, last_update_login,
2054 program_id, program_login_id,
2055 program_application_id, request_id)
2056 -- category (42, 43, 44)
2057 select
2058 f.plan_id, f.plan_run_id, f.io_plan_flag,
2059 f.sr_instance_id, f.organization_id, f.owning_inst_id, f.owning_org_id,
2060 to_number(-23453) inventory_item_id,
2061 f.vmi_flag, f.order_date,
2062 to_number(42) aggr_type,
2063 l_category_set_id1 category_set_id,
2064 nvl(q.sr_category_id, -23453),
2065 sum(f.demand_qty),
2066 sum(f.supply_qty),
2067 sum(f.pegged_to_excess_qty ),
2068 sum(f.planned_order_qty),
2069 sum(f.indep_demand_qty),
2070 sum(f.indep_demand_value),
2071 sum(f.dep_demand_qty),
2072 sum(f.sales_order_value),
2073 sum(f.return_order_value),
2074 sum(f.make_order_qty),
2075 sum(f.make_order_leadtime),
2076 sum(f.make_order_count),
2077 sum(f.stock_outs_count),
2078 sum(f.no_activity_item_count),
2079 sum(f.days_in_bkt),
2080 sum(f.item_leadtime),
2081 sum(f.avg_daily_demand),
2082 sum(f.onhand_qty),
2083 sum(f.scheduled_rept_qty),
2084 sum(f.forecast_qty),
2085 fnd_global.user_id, sysdate,
2086 sysdate, fnd_global.user_id, fnd_global.login_id,
2087 fnd_global.conc_program_id, fnd_global.conc_login_id,
2088 fnd_global.prog_appl_id, fnd_global.conc_request_id
2089 from
2090 msc_item_orders_f f,
2091 msc_phub_item_categories_mv q
2092 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
2093 and f.aggr_type=0
2094 and f.owning_inst_id=q.sr_instance_id(+)
2095 and f.owning_org_id=q.organization_id(+)
2096 and f.inventory_item_id=q.inventory_item_id(+)
2097 and q.category_set_id(+)=l_category_set_id1
2098 group by
2099 f.plan_id, f.plan_run_id, f.io_plan_flag,
2100 f.sr_instance_id, f.organization_id, f.owning_inst_id, f.owning_org_id,
2101 f.vmi_flag, f.order_date,
2102 nvl(q.sr_category_id, -23453);
2103
2104 commit;
2105
2106 exception
2107 when dup_val_on_index then
2108 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_DUPLICATE_DATA')||
2109 SQLCODE||' -ERROR- '||SQLERRM;
2110 retcode := 2;
2111 --dbms_output.put_line(errbuf);
2112 when others then
2113 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_POPULATE_ERROR')||
2114 SQLCODE||' -ERROR- '||SQLERRM;
2115 retcode := 2;
2116 --dbms_output.put_line(errbuf);
2117 end populate_summary;
2118
2119 procedure purge_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
2120 p_plan_id number, p_plan_run_id number default null,
2121 p_commit_size in number default 1000) is
2122
2123
2124 l_num number;
2125 l_api_name varchar2(100);
2126 l_stmt_id number;
2127 begin
2128
2129 retcode :=0; -- this means successfully
2130 errbuf :='';
2131
2132 l_api_name := 'msc_item_f_pkg.purge_details';
2133
2134
2135 while true loop
2136 l_stmt_id :=1;
2137 delete /*+ PARALLEL(mos) */ from msc_item_inventory_f
2138 where rownum<=p_commit_size and plan_id=p_plan_id and plan_run_id=nvl(p_plan_run_id,plan_run_id);
2139 l_stmt_id:=2;
2140 commit;
2141 -- exit when there are no more rows to delete.
2142 l_stmt_id:=3;
2143 select count(1) into l_num from msc_item_inventory_f
2144 where plan_id=p_plan_id and plan_run_id=nvl(p_plan_run_id,plan_run_id);
2145 if (l_num=0) then
2146 commit;
2147 exit;
2148 end if;
2149 end loop;
2150
2151
2152 while true loop
2153 l_stmt_id :=1;
2154 delete /*+ PARALLEL(mos) */ from msc_item_inventory_f
2155 where rownum<=p_commit_size and plan_id=p_plan_id and plan_run_id=nvl(p_plan_run_id,plan_run_id);
2156 l_stmt_id:=2;
2157 commit;
2158 -- exit when there are no more rows to delete.
2159 l_stmt_id:=3;
2160 select count(1) into l_num from msc_item_inventory_f
2161 where plan_id=p_plan_id and plan_run_id=nvl(p_plan_run_id,plan_run_id);
2162 if (l_num=0) then
2163 commit;
2164 exit;
2165 end if;
2166 end loop;
2167
2168
2169 while true loop
2170 l_stmt_id :=11;
2171 delete /*+ PARALLEL(mos) */ from msc_item_orders_f
2172 where rownum<=p_commit_size and plan_id=p_plan_id and plan_run_id=nvl(p_plan_run_id,plan_run_id);
2173 l_stmt_id:=12;
2174 commit;
2175 -- exit when there are no more rows to delete.
2176 l_stmt_id:=13;
2177 select count(1) into l_num from msc_item_orders_f
2178 where plan_id=p_plan_id and plan_run_id=nvl(p_plan_run_id,plan_run_id);
2179 if (l_num=0) then
2180 commit;
2181 exit;
2182 end if;
2183 end loop;
2184
2185
2186
2187
2188 exception
2189 when others then
2190 retcode :=2;
2191 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_PURGE_ERROR')||SQLCODE||' -ERROR- '||SQLERRM;
2192 if( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2193 fnd_log.string(fnd_log.LEVEL_UNEXPECTED,
2194 l_api_name,
2195 to_char(SQLCODE) || ':' || SQLERRM || ' in stmt_id=' || l_stmt_id);
2196 end if;
2197
2198 return;
2199 end purge_details;
2200
2201
2202 end msc_item_pkg;