[Home] [Help]
PACKAGE BODY: APPS.MSC_ALLOCATION_PLAN
Source
1 PACKAGE BODY MSC_ALLOCATION_PLAN AS
2 /* $Header: MSCALPHB.pls 120.8 2007/01/17 19:29:59 hulu ship $ */
3
4 /* group by column */
5 GY_CUSTOMER CONSTANT INTEGER := 2;
6 GY_CUSTOMER_SITE CONSTANT INTEGER := 3;
7 GY_DEMAND_CLASS CONSTANT INTEGER := 4;
8
9 /* allocation plan type lookup */
10
11 HZ_UNC_DEMAND CONSTANT INTEGER := 10;
12 HZ_EXP_DEMAND CONSTANT INTEGER := 20;
13 HZ_SUPPLY CONSTANT INTEGER := 30;
14 HZ_FIRM_ALLOC CONSTANT INTEGER := 40;
15 HZ_SUGG_ALLOC CONSTANT INTEGER := 50;
16 HZ_ADJ_ALLOC CONSTANT INTEGER := 60;
17 HZ_EFFECT_ALLOC CONSTANT INTEGER := 70;
18 --HZ_ADJ_CUM_FILL_RATE CONSTANT INTEGER := 80;
19 HZ_CUM_UNC_DEMAND CONSTANT INTEGER := 90;
20 HZ_CUM_EXP_DEMAND CONSTANT INTEGER := 100;
21 HZ_CUM_SUPPLY CONSTANT INTEGER := 110;
22 HZ_CUM_SUGG_ALLOC CONSTANT INTEGER := 120;
23 HZ_CUM_FILL_RATE CONSTANT INTEGER := 130;
24
25 /* offset */
26
27 UNC_DEMAND_OFF CONSTANT INTEGER := 1;
28 EXP_DEMAND_OFF CONSTANT INTEGER := 2;
29 SUPPLY_OFF CONSTANT INTEGER := 3;
30 FIRM_ALLOC_OFF CONSTANT INTEGER := 4;
31 SUGG_ALLOC_OFF CONSTANT INTEGER := 5;
32 ADJ_ALLOC_OFF CONSTANT INTEGER := 6;
33 EFFECT_ALLOC_OFF CONSTANT INTEGER := 7;
34 --ADJ_CUM_ALLOC_OFF CONSTANT INTEGER := 7;
35 --ADJ_CUM_FILL_RATE_OFF CONSTANT INTEGER := 8;
36 CUM_UNC_DEMAND_OFF CONSTANT INTEGER := 8;
37 CUM_EXP_DEMAND_OFF CONSTANT INTEGER := 9;
38 CUM_SUPPLY_OFF CONSTANT INTEGER := 10;
39 CUM_SUGG_ALLOC_OFF CONSTANT INTEGER := 11;
40 CUM_FILL_RATE_OFF CONSTANT INTEGER := 12;
41
42 NUM_OF_TYPES CONSTANT INTEGER := 12;
43
44 /* global variable for number of buckets to display for the plan */
45 g_num_of_buckets NUMBER;
46 g_error_stmt VARCHAR2(200);
47
48 TYPE column_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
49 TYPE calendar_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
50 var_dates calendar_date; -- Holds the start dates of buckets
51 g_other_text varchar2(200);
52 g_total_text varchar2(200);
53
54 Procedure populate_allocation_plan (
55 arg_query_id IN NUMBER,
56 arg_plan_id IN NUMBER,
57 arg_org_id IN NUMBER,
58 arg_instance_id IN NUMBER,
59 arg_item_id IN NUMBER,
60 arg_group_by IN NUMBER,
61 arg_customer_id IN NUMBER DEFAULT NULL,
62 arg_customer_site_id IN NUMBER DEFAULT NULL,
63 arg_customer_list_id IN NUMBER DEFAULT NULL) IS
64
65 -- -------------------------------------------------
66 -- This cursor select number of buckets in the plan.
67 -- -------------------------------------------------
68 CURSOR plan_buckets IS
69 SELECT organization_id, sr_instance_id
70 FROM msc_plans
71 WHERE plan_id = arg_plan_id;
72
73 -- -------------------------------------------------
74 -- This cursor selects the dates for the buckets.
75 -- -------------------------------------------------
76 g_org_id number;
77 g_inst_id number;
78
79 CURSOR bucket_dates IS
80 SELECT mab.bkt_start_date, mab.bkt_end_date
81 FROM msc_allocation_buckets mab
82 WHERE mab.plan_id = arg_plan_id
83 and mab.organization_id = g_org_id
84 and mab.sr_instance_id = g_inst_id
85 order by mab.bucket_index ;
86
87 l_bucket_number NUMBER := 1;
88 l_bucket_date DATE;
89 l_bkt_end_date DATE;
90
91 last_date DATE;
92
93 CURSOR allocation_plan_activity IS
94 SELECT
95 SUPPLY_OFF offset,
96 'total' char1, -- only shown in total
97 1 sequence,
98 nvl(ms.firm_date,ms.new_schedule_date) new_date,
99 sum(nvl(ms.firm_quantity,ms.new_order_quantity)) quantity,
100 0 quantity2
101 FROM msc_supplies ms,
102 msc_form_query mfq
103 WHERE ms.plan_id = mfq.number4
104 AND ms.inventory_item_id = mfq.number1
105 AND ms.organization_id = mfq.number2
106 AND ms.sr_instance_id = mfq.number3
107 AND mfq.query_id = arg_query_id
108 AND (mfq.number6 <> -1 or
109 ( mfq.number6 = -1 and ms.order_type <> 51)) -- no internal shipments in all org
110 GROUP BY
111 nvl(ms.firm_date,ms.new_schedule_date)
112 UNION ALL
113 SELECT UNC_DEMAND_OFF offset,
114 nvl(decode(md.demand_source_type, 8, -- internal sales order
115 to_char(md.source_organization_id),
116 decode(arg_group_by, -- other demand types
117 GY_CUSTOMER, to_char(md.customer_id),
118 GY_CUSTOMER_SITE, to_char(md.customer_site_id),
119 GY_DEMAND_CLASS, md.demand_class)),
120 nvl(to_char(md.source_organization_id), '-2')) char1,
121 decode(md.demand_source_type, 8, --internal sales order
122 9, -- displayed as an org
123 decode(decode(arg_group_by,
124 GY_CUSTOMER, to_char(md.customer_id),
125 GY_CUSTOMER_SITE, to_char(md.customer_site_id),
126 GY_DEMAND_CLASS, md.demand_class),
127 null, -- if no customer_id/...
128 decode(md.source_organization_id, null, 8, 9),
129 arg_group_by)) sequence,
130 nvl(md.old_using_assembly_demand_date,
131 md.using_assembly_demand_date) new_date,
132 SUM(DECODE(md.origination_type, 29,nvl(md.probability,1),1) *
133 nvl(old_using_requirement_quantity,
134 using_requirement_quantity)) quantity,
135 sum(md.unmet_quantity) quantity2
136 FROM msc_demands md,
137 msc_form_query mfq
138 WHERE md.plan_id = mfq.number4
139 AND md.inventory_item_id = mfq.number1
140 AND md.organization_id = mfq.number2
141 AND md.sr_instance_id = mfq.number3
142 AND md.origination_type in (1,24,29,30)
143 AND mfq.query_id = arg_query_id
144 AND nvl(md.customer_id,-1) = nvl(arg_customer_id,
145 nvl(md.customer_id,-1))
146 AND nvl(md.customer_site_id,-1) = nvl(arg_customer_site_id,
147 nvl(md.customer_site_id,-1))
148 AND (arg_customer_list_id is null or
149 ( arg_customer_list_id is not null and
150 ((md.customer_id, md.customer_site_id) in (
151 select source_type, object_type
152 from msc_pq_types
153 where query_id = arg_customer_list_id
154 and object_type <> 0) or
155 md.customer_id in (
156 select source_type
157 from msc_pq_types
158 where query_id = arg_customer_list_id
159 and object_type = 0) or
160 md.customer_id is null)
161 )
162 )
163 GROUP BY
164 nvl(md.old_using_assembly_demand_date,
165 md.using_assembly_demand_date),
166 nvl(decode(md.demand_source_type, 8,
167 to_char(md.source_organization_id),
168 decode(arg_group_by,
169 GY_CUSTOMER, to_char(md.customer_id),
170 GY_CUSTOMER_SITE, to_char(md.customer_site_id),
171 GY_DEMAND_CLASS, md.demand_class)),
172 nvl(to_char(md.source_organization_id), '-2')),
173 decode(md.demand_source_type, 8,
174 9,
175 decode(decode(arg_group_by,
176 GY_CUSTOMER, to_char(md.customer_id),
177 GY_CUSTOMER_SITE, to_char(md.customer_site_id),
178 GY_DEMAND_CLASS, md.demand_class),
179 null,
180 decode(md.source_organization_id, null, 8, 9),
181 arg_group_by))
182 UNION ALL
183 SELECT
184 SUGG_ALLOC_OFF offset,
185 nvl(decode(md.demand_source_type, 8, -- internal sales order
186 to_char(md.source_organization_id),
187 decode(arg_group_by, -- other demand types
188 GY_CUSTOMER, to_char(md.customer_id),
189 GY_CUSTOMER_SITE, to_char(md.customer_site_id),
190 GY_DEMAND_CLASS, md.demand_class)),
191 nvl(to_char(md.source_organization_id), '-2')) char1,
192 decode(md.demand_source_type, 8, --internal sales order
193 9, -- displayed in last rows
194 decode(decode(arg_group_by,
195 GY_CUSTOMER, to_char(md.customer_id),
196 GY_CUSTOMER_SITE, to_char(md.customer_site_id),
197 GY_DEMAND_CLASS, md.demand_class),
198 null,
199 decode(md.source_organization_id, null, 8, 9),
200 arg_group_by)) sequence,
201 ms.new_schedule_date new_date,
202 SUM(mslp.quantity) quantity,
203 0 quantity2
204 FROM msc_demands md,
205 msc_supplies ms,
206 msc_single_lvl_peg mslp,
207 msc_form_query mfq
208 WHERE ms.organization_id = mfq.number2
209 AND ms.sr_instance_id = mfq.number3
210 AND ms.plan_id = mfq.number4
211 AND ms.inventory_item_id = mfq.number1
212 AND mfq.query_id = arg_query_id
213 and mslp.plan_id = ms.plan_id
214 and mslp.pegging_type = 2 -- supply to parent demand
215 and mslp.child_id = ms.transaction_id
216 and md.plan_id = mslp.plan_id
217 and md.demand_id = mslp.parent_id
218 AND nvl(md.customer_id,-1) = nvl(arg_customer_id,
219 nvl(md.customer_id,-1))
220 AND nvl(md.customer_site_id,-1) = nvl(arg_customer_site_id,
221 nvl(md.customer_site_id,-1))
222 AND (arg_customer_list_id is null or
223 ( arg_customer_list_id is not null and
224 ((md.customer_id, md.customer_site_id) in (
225 select source_type, object_type
226 from msc_pq_types
227 where query_id = arg_customer_list_id
228 and object_type <> 0) or
229 md.customer_id in (
230 select source_type
231 from msc_pq_types
232 where query_id = arg_customer_list_id
233 and object_type = 0) or
234 md.customer_id is null)
235 )
236 )
237 GROUP BY ms.new_schedule_date,
238 nvl(decode(md.demand_source_type, 8,
239 to_char(md.source_organization_id),
240 decode(arg_group_by,
241 GY_CUSTOMER, to_char(md.customer_id),
242 GY_CUSTOMER_SITE, to_char(md.customer_site_id),
243 GY_DEMAND_CLASS, md.demand_class)),
244 nvl(to_char(md.source_organization_id), '-2')),
245 decode(md.demand_source_type, 8,
246 9,
247 decode(decode(arg_group_by,
248 GY_CUSTOMER, to_char(md.customer_id),
249 GY_CUSTOMER_SITE, to_char(md.customer_site_id),
250 GY_DEMAND_CLASS, md.demand_class),
251 null,
252 decode(md.source_organization_id, null, 8, 9),
253 arg_group_by))
254 UNION ALL
255 SELECT
256 FIRM_ALLOC_OFF offset,
257 to_char(ms.organization_id) char1,
258 9 sequence,
259 nvl(ms.firm_ship_date,ms.new_ship_date) new_date,
260 sum(nvl(ms.firm_quantity,ms.new_order_quantity)) quantity,
261 0 quantity2
262 FROM msc_supplies ms,
263 msc_form_query mfq
264 WHERE ms.plan_id = mfq.number4
265 AND ms.inventory_item_id = mfq.number1
266 AND ms.source_organization_id = mfq.number2
267 AND ms.source_sr_instance_id = mfq.number3
268 AND mfq.query_id = arg_query_id
269 AND ms.firm_planned_type = 1
270 AND ms.source_organization_id <> ms.organization_id
271 GROUP BY to_char(ms.organization_id),
272 nvl(ms.firm_ship_date,ms.new_ship_date)
273 UNION ALL
274 --------------------------------------------------------------------
275 -- This select will ensure that all selected items get into cursor
276 -- even though they do not have any activity
277 ---------------------------------------------------------------------
278 SELECT UNC_DEMAND_OFF offset,
279 'dummy' char1,
280 -1 sequence,
281 to_date(1, 'J') new_date,
282 0 quantity,
283 0 quantity2
284 FROM msc_form_query mfq
285 WHERE mfq.query_id = arg_query_id
286 ORDER BY 3,2,4;
287
288 TYPE alloc_activity IS RECORD
289 (offset NUMBER,
290 char1 varchar2(80),
291 sequence NUMBER,
292 new_date DATE,
293 quantity NUMBER,
294 quantity2 NUMBER);
295
296 activity_rec alloc_activity;
297
298
299 last_char1 VARCHAR2(80) := '-1';
300 last_sequence number :=-2;
301
302 TYPE row_rec IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
303
304 row_detail row_rec; -- for each customer/site/priority/demand class/org
305 total_row row_rec; -- for summary row
306 row_header_type column_number;
307
308 bucket_counter BINARY_INTEGER := 0; -- Id of the current bucket
309 old_bucket_counter BINARY_INTEGER := 0;
310 counter BINARY_INTEGER := 0;
311
312 FUNCTION get_offset_location(offset IN NUMBER,
313 bucket IN NUMBER) RETURN number IS
314 BEGIN
315 return (offset -1) * g_num_of_buckets + bucket;
316 END get_offset_location;
317
318 FUNCTION get_row_detail(offset IN NUMBER,
319 bucket IN NUMBER) RETURN NUMBER IS
320 location number;
321 BEGIN
325
322 location := get_offset_location(offset ,bucket);
323 g_error_stmt := 'Debug - get_row_detail '||offset||','||bucket||','||location;
324 return row_detail(location);
326 END get_row_detail;
327
328 FUNCTION get_total_row(offset IN NUMBER,
329 bucket IN NUMBER) RETURN NUMBER IS
330 location number;
331 BEGIN
332 location := get_offset_location(offset ,bucket);
333 g_error_stmt := 'Debug - get_total_row ,'||offset||','||bucket||','||location;
334 return total_row(location);
335
336 END get_total_row;
337
338 PROCEDURE add_to_plan(bucket IN NUMBER,
339 offset IN NUMBER,
340 quantity IN NUMBER) IS
341 location number;
342 BEGIN
343 g_error_stmt := 'Debug - add_to_plan - '||bucket||','||quantity;
344
345 if nvl(quantity,0) = 0 or offset is null then
346 return;
347 end if;
348
349 location := get_offset_location(offset ,bucket);
350 row_detail(location) :=
351 row_detail(location) + quantity;
352
353
354 if offset not in
355 (CUM_FILL_RATE_OFF ) then
356 total_row(location) :=
357 total_row(location) + quantity;
358 end if;
359 END add_to_plan;
360
361 PROCEDURE calculate_cum_rows(bkt number) IS
362 BEGIN
363 if bkt = 1 then
364
365 add_to_plan(bkt,
366 CUM_UNC_DEMAND_OFF,
367 get_row_detail(UNC_DEMAND_OFF,bkt));
368 add_to_plan(bkt,
369 CUM_EXP_DEMAND_OFF,
370 get_row_detail(EXP_DEMAND_OFF,bkt));
371 add_to_plan(bkt,
372 CUM_SUPPLY_OFF,
373 get_row_detail(SUPPLY_OFF,bkt));
374 add_to_plan(bkt,
375 CUM_SUGG_ALLOC_OFF,
376 get_row_detail(SUGG_ALLOC_OFF,bkt));
377
378 else
379
380 add_to_plan(bkt,
381 CUM_UNC_DEMAND_OFF,
382 get_row_detail(CUM_UNC_DEMAND_OFF,bkt -1)+
383 get_row_detail(UNC_DEMAND_OFF,bkt));
384 add_to_plan(bkt,
385 CUM_EXP_DEMAND_OFF,
386 get_row_detail(CUM_EXP_DEMAND_OFF,bkt-1)+
387 get_row_detail(EXP_DEMAND_OFF,bkt));
388 add_to_plan(bkt,
389 CUM_SUPPLY_OFF,
390 get_row_detail(CUM_SUPPLY_OFF,bkt-1)+
391 get_row_detail(SUPPLY_OFF,bkt));
392 add_to_plan(bkt,
393 CUM_SUGG_ALLOC_OFF,
394 get_row_detail(CUM_SUGG_ALLOC_OFF,bkt-1)+
395 get_row_detail(SUGG_ALLOC_OFF,bkt));
396
397 end if;
398
399 if get_row_detail(CUM_UNC_DEMAND_OFF,bkt) <> 0 then
400 add_to_plan(bkt,
401 CUM_FILL_RATE_OFF,
402 get_row_detail(CUM_SUGG_ALLOC_OFF,bkt)/
403 get_row_detail(CUM_UNC_DEMAND_OFF,bkt)*100);
404 end if;
405
406 add_to_plan(bkt,
407 EFFECT_ALLOC_OFF,
408 greatest(get_row_detail(SUGG_ALLOC_OFF,bkt),
409 get_row_detail(FIRM_ALLOC_OFF,bkt)));
410
411 END calculate_cum_rows;
412
413
414 PROCEDURE flush_item_plan(p_char1 VARCHAR2, p_sequence NUMBER) IS
415 p_text varchar2(300);
416 p_id number;
417 BEGIN
418
419 g_error_stmt := 'Debug - flush_item_plan - 10, for '||p_char1;
420
421 FOR bkt IN 1..g_num_of_buckets LOOP
422
423 calculate_cum_rows(bkt);
424
425 if p_sequence = 1 then
426 -- calcuate TOTAL for total row
427 if bkt = 1 then
428 total_row(get_offset_location(CUM_SUPPLY_OFF ,bkt)) :=
429 get_total_row(SUPPLY_OFF ,bkt);
430 else
431 total_row(get_offset_location(CUM_SUPPLY_OFF ,bkt)) :=
432 get_total_row(SUPPLY_OFF ,bkt)+
433 get_total_row(CUM_SUPPLY_OFF ,bkt-1);
434 end if;
435
436 if get_total_row(CUM_UNC_DEMAND_OFF,bkt) <> 0 then
437 total_row(get_offset_location(CUM_FILL_RATE_OFF ,bkt)) :=
438 get_total_row(CUM_SUGG_ALLOC_OFF,bkt)/
439 get_total_row(CUM_UNC_DEMAND_OFF,bkt)*100;
440 end if;
441
442 end if;
443
444 -- get the text for group by column
445 begin
446
447 if p_sequence not in (GY_DEMAND_CLASS,1) then
448 p_id := to_number(p_char1);
449 else
450 p_id :=-2;
451 if p_sequence = GY_DEMAND_CLASS and p_char1 <> '-2' then
452 p_id := -1;
453 end if;
454 end if;
455
456 if p_char1 = '-2' then -- null column
457 p_text := g_other_text;
458 elsif p_sequence = GY_CUSTOMER then
459 p_text := msc_get_name.customer(p_char1);
460 elsif p_sequence = GY_CUSTOMER_SITE then
461 p_text := msc_get_name.customer_site(p_char1);
462 elsif p_sequence in (8,9) then -- an org
463 p_text := msc_get_name.org_code(p_char1,arg_instance_id);
464 if p_sequence = 8 then -- forecast without customer
468 p_text := g_total_text;
465 p_text := p_text ||' - '||g_other_text;
466 end if;
467 elsif p_sequence = 1 then -- total
469 else
470 p_text := p_char1;
471 end if;
472
473
474
475 if p_text is null then
476 --dbms_output.put_line('no text for '||p_char1||','||p_sequence);
477 p_text := p_char1;
478 end if;
479
480 exception when others then
481 p_text := p_char1;
482 end;
483
484 p_text := replace(p_text, '*', '&');
485 FOR a in 1 .. NUM_OF_TYPES LOOP
486 INSERT INTO msc_drp_hori_plans(
487 query_id,
488 organization_id,
489 sr_instance_id,
490 inventory_item_id,
491 row_type,
492 char1,
493 sub_org_id,
494 horizontal_plan_type,
495 bucket_date,
496 last_update_date,
497 last_updated_by,
498 creation_date,
499 created_by,
500 quantity)
501 VALUES (
502 arg_query_id,
503 arg_org_id,
504 arg_instance_id,
505 arg_item_id,
506 row_header_type(a), -- row_type
507 p_text,
508 p_id, -- store customer_id, customer_site_id, priority, to org id
509 p_sequence,
510 var_dates(bkt),
511 SYSDATE,
512 -1,
513 SYSDATE,
514 -1,
515 decode(p_sequence, 1,
516 total_row(bkt + (a-1)*g_num_of_buckets),
517 row_detail(bkt + (a-1)*g_num_of_buckets)));
518 END LOOP;
519
520 END LOOP; -- end of FOR bkt IN
521
522 g_error_stmt := 'Debug - flush_item_plan - 80';
523
524 END flush_item_plan;
525
526 BEGIN
527
528 g_error_stmt := 'Debug - populate_horizontal_plan - 10';
529
530 if g_other_text is null then
531 fnd_message.set_name('MSC', 'MSC_OTHER');
532 g_other_text := fnd_message.get;
533 fnd_message.set_name('MSC', 'MSC_TOTAL');
534 g_total_text := fnd_message.get;
535 end if;
536
537 OPEN plan_buckets;
538 FETCH plan_buckets into g_org_id, g_inst_id;
539 CLOSE plan_buckets;
540
541 -- --------------------
542 -- Get the bucket dates
543 -- --------------------
544 OPEN bucket_dates;
545 LOOP
546 FETCH bucket_dates INTO l_bucket_date, l_bkt_end_date;
547 EXIT WHEN BUCKET_DATES%NOTFOUND;
548 var_dates(l_bucket_number) := l_bucket_date;
549 l_bucket_number := l_bucket_number + 1;
550 END LOOP;
551 CLOSE bucket_dates;
552 var_dates(l_bucket_number) := l_bkt_end_date +1;
553
554 g_num_of_buckets := var_dates.count -1;
555 --dbms_output.put_line('g_num_of_buckets='||g_num_of_buckets);
556 g_error_stmt := 'Debug - populate_horizontal_plan - 20';
557
558 -- Initialize the bucket cells to 0.
559
560 FOR a IN 1..(NUM_OF_TYPES*g_num_of_buckets) LOOP
561 row_detail(a) := 0;
562 total_row(a) := 0;
563 END LOOP;
564 --dbms_output.put_line('total rows='||total_row.count);
565 -- associate offset with rowtype
566
567 row_header_type(UNC_DEMAND_OFF) := HZ_UNC_DEMAND;
568 row_header_type(EXP_DEMAND_OFF) := HZ_EXP_DEMAND;
569 row_header_type(SUPPLY_OFF) := HZ_SUPPLY;
570 row_header_type(FIRM_ALLOC_OFF) := HZ_FIRM_ALLOC;
571 row_header_type(SUGG_ALLOC_OFF) := HZ_SUGG_ALLOC;
572 row_header_type(ADJ_ALLOC_OFF) := HZ_ADJ_ALLOC;
573 row_header_type(EFFECT_ALLOC_OFF) := HZ_EFFECT_ALLOC;
574 -- row_header_type(ADJ_CUM_FILL_RATE_OFF) := HZ_ADJ_CUM_FILL_RATE;
575 row_header_type(CUM_UNC_DEMAND_OFF) := HZ_CUM_UNC_DEMAND;
576 row_header_type(CUM_EXP_DEMAND_OFF) := HZ_CUM_EXP_DEMAND;
577 row_header_type(CUM_SUPPLY_OFF) := HZ_CUM_SUPPLY;
578 row_header_type(CUM_SUGG_ALLOC_OFF) := HZ_CUM_SUGG_ALLOC;
579 row_header_type(CUM_FILL_RATE_OFF) := HZ_CUM_FILL_RATE;
580
581
582 g_error_stmt := 'Debug - populate_horizontal_plan - 40';
583 bucket_counter := 2;
584 old_bucket_counter := 2;
585 activity_rec.offset := 0;
586 activity_rec.char1 := '0';
587 activity_rec.new_date := sysdate;
588 activity_rec.quantity := 0;
589 activity_rec.quantity2 := 0;
590
591 OPEN allocation_plan_activity;
592 LOOP
593 FETCH allocation_plan_activity INTO activity_rec;
594 IF (allocation_plan_activity%NOTFOUND OR
595 activity_rec.char1 <> last_char1 OR
596 activity_rec.sequence <> last_sequence) and
597 last_char1 <> '-1' THEN
598
599 if last_char1 not in ('total','dummy') then
600 -- don't flush for summary row yet
601 --dbms_output.put_line('flush '||last_char1||','||activity_rec.sequence||','||last_sequence);
602 flush_item_plan(last_char1, last_sequence);
603 end if;
604
605 -- ------------------------------------
606 -- Initialize the bucket cells to 0.
610 FOR a IN 1..(NUM_OF_TYPES*g_num_of_buckets) LOOP
607 -- ------------------------------------
608 bucket_counter := 2;
609 old_bucket_counter := 2;
611 row_detail(a) := 0;
612 END LOOP;
613
614 END IF; -- end of activity_rec.item_id <> last_item_id
615 EXIT WHEN allocation_plan_activity%NOTFOUND;
616 --dbms_output.put_line('char1='||activity_rec.char1||','||activity_rec.new_date||','||activity_rec.quantity||','||activity_rec.offset||','||activity_rec.sequence );
617 -- find the correct bucket
618 IF activity_rec.new_date >= var_dates(bucket_counter) THEN
619 WHILE activity_rec.new_date >= var_dates(bucket_counter) AND
620 bucket_counter <= g_num_of_buckets LOOP
621 bucket_counter := bucket_counter + 1;
622 END LOOP;
623 END IF;
624
625 add_to_plan(bucket_counter -1,
626 activity_rec.offset,
627 activity_rec.quantity);
628
629 IF activity_rec.offset = UNC_DEMAND_OFF THEN
630
631 add_to_plan(bucket_counter -1,
632 EXP_DEMAND_OFF,
633 activity_rec.quantity2);
634
635 END IF;
636
637 g_error_stmt := 'Debug - populate_horizontal_plan - 42';
638 /*
639 IF activity_rec.offset = TOTAL_DEMAND_OFF and
640 activity_rec.quantity2 <> 0 then
641 WHILE activity_rec.new_date2 >= var_dates(old_bucket_counter) AND
642 old_bucket_counter <= g_num_of_buckets LOOP
643 -- ----------
644 -- move back.
645 -- ----------
646 old_bucket_counter := old_bucket_counter + 1;
647
648 END LOOP;
649
650 WHILE activity_rec.new_date2 < var_dates(old_bucket_counter - 1) AND
651 old_bucket_counter > 2 LOOP
652 -- -------------
653 -- move forward.
654 -- -------------
655 old_bucket_counter := old_bucket_counter - 1;
656 END LOOP;
657 IF activity_rec.new_date2 < var_dates(old_bucket_counter) THEN
658 add_to_plan(old_bucket_counter - 1,
659 MNUL_ALLOC_OFF,
660 activity_rec.quantity2);
661 add_to_plan(old_bucket_counter - 1,
662 PRIOR_ALLOC_OFF,
663 activity_rec.quantity3);
664 END IF;
665 END IF;
666 */
667 last_char1 := activity_rec.char1;
668 last_sequence := activity_rec.sequence;
669
670 END LOOP;
671
672 g_error_stmt := 'Debug - populate_horizontal_plan - 50';
673 CLOSE allocation_plan_activity;
674
675 flush_item_plan('total', 1); -- flush the summary row now
676
677 EXCEPTION
678
679 WHEN OTHERS THEN
680 -- dbms_output.put_line(g_error_stmt);
681 raise;
682
683 END populate_allocation_plan;
684
685 FUNCTION send_dates RETURN varchar2 IS
686 p_dates varchar2(30000);
687 BEGIN
688 FOR bkt IN 1..g_num_of_buckets LOOP
689 p_dates := p_dates ||'|'||fnd_date.date_to_displaydate(var_dates(bkt));
690 END LOOP;
691 p_dates := g_num_of_buckets||p_dates;
692 return p_dates;
693 END send_dates;
694
695 PROCEDURE create_planned_arrival(
696 p_plan_id in number, p_org_id in number,
697 p_inst_id in number, p_item_id in number,
698 p_source_org in number, p_source_inst in number,
699 p_bkt_start_date in date,
700 p_allocate_qty in number) IS
701 cursor org_c is
702 select ship_method,avg_transit_lead_time
703 from msc_item_sourcing mis
704 where mis.plan_id = p_plan_id
705 and mis.inventory_item_id = p_item_id
706 and mis.source_organization_id = p_source_org
707 and mis.sr_instance_id2 = p_source_inst
708 and mis.organization_id = p_org_id
709 and mis.sr_instance_id = p_inst_id
710 order by rank,allocation_percent desc,avg_transit_lead_time;
711
712 p_due_date date;
713 p_dock_date date;
714 p_ship_date date := p_bkt_start_date;
715 p_lead_time number;
716 p_deliver_calendar varchar2(20);
717 p_receive_calendar varchar2(20);
718 p_ship_calendar varchar2(20);
719 p_ship_method varchar2(30);
720 v_pp_lead_time number;
721 l_user_id number := fnd_global.user_id;
722 l_transaction_id number;
723 p_associate_type number;
724 supply_columns msc_undo.changeRGType;
725 x_return_sts VARCHAR2(20);
726 x_msg_count NUMBER;
727 x_msg_data VARCHAR2(2000);
728
729 BEGIN
730
731 OPEN org_c;
732 FETCH org_c INTO p_ship_method, p_lead_time;
733 CLOSE org_c;
734
735 --dbms_output.put_line(p_ship_method||', '|| p_lead_time);
736 msc_drp_util.offset_dates('SHIP_DATE',
737 p_plan_id,
738 p_source_org,
739 p_org_id,
740 p_inst_id,
741 p_item_id,
742 p_ship_method,
743 p_lead_time, p_ship_calendar,
744 p_deliver_calendar, p_receive_calendar,
745 p_ship_date,
746 p_dock_date,
750 select msc_supplies_s.nextval into l_transaction_id from dual;
747 p_due_date);
748
749 --dbms_output.put_line('dock date='||p_dock_date);
751 --dbms_output.put_line('id ='||l_transaction_id);
752 insert into msc_supplies(
753 transaction_id,
754 last_update_date,
755 last_updated_by,
756 creation_date,
757 created_by,
758 last_update_login,
759 inventory_item_id,
760 organization_id,
761 sr_instance_id,
762 plan_id,
763 new_schedule_date,
764 order_type,
765 new_order_quantity,
766 new_dock_date,
767 new_ship_date,
768 status,
769 applied,
770 firm_planned_type,
771 firm_date,
772 firm_ship_date,
773 firm_quantity,
774 source_organization_id,
775 source_sr_instance_id,
776 ship_method,
777 intransit_lead_time,
778 ship_calendar,
779 intransit_calendar,
780 receiving_calendar)
781 values (
782 l_transaction_id,
783 sysdate,
784 l_user_id,
785 sysdate,
786 l_user_id,
787 l_user_id,
788 p_item_id,
789 p_org_id,
790 p_inst_id,
791 p_plan_id,
792 p_due_date,
793 51,
794 0,
795 p_dock_date,
796 p_ship_date,
797 0,
798 2,
799 1,
800 p_due_date,
801 p_ship_date,
802 p_allocate_qty,
803 p_source_org,
804 p_source_inst,
805 p_ship_method,
806 p_lead_time,
807 p_ship_calendar,
808 p_deliver_calendar,
809 p_receive_calendar);
810 -- mark undo
811
812 msc_undo.store_undo(1, --table_changed
813 1, --insert or update
814 l_transaction_id,
815 p_plan_id,
816 p_inst_id,
817 NULL,
818 supply_columns,
819 x_return_sts,
820 x_msg_count,
821 x_msg_data,
822 null);
823
824 exception when others then
825 raise;
826 END create_planned_arrival;
827
828 PROCEDURE query_list(
829 p_query_id IN NUMBER,
830 p_plan_id IN NUMBER,
831 p_org_id IN NUMBER,
832 p_inst_id IN NUMBER,
833 p_category_set IN NUMBER,
834 p_category_name IN VARCHAR2) IS
835
836 sql_stmt VARCHAR2(5000);
837 p_org_code varchar2(80);
838 BEGIN
839 sql_stmt := 'INSERT INTO msc_form_query ( '||
840 'query_id, '||
841 'last_update_date, '||
842 'last_updated_by, '||
843 'creation_date, '||
844 'created_by, '||
845 'last_update_login, '||
846 'number1, '|| -- item_id
847 'number2, '|| -- org_id
848 'number3, '|| -- inst_id
849 'number4, '|| -- plan_id
850 'number5, '|| -- displayed item_id
851 'number6, '|| -- displayed org_id
852 'number7, '|| -- node type
853 'number8, '|| -- org sequence
854 'char1, '||
855 'char2) '||
856 ' SELECT DISTINCT :p_query_id, '||
857 'sysdate, '||
858 '1, '||
859 'sysdate, '||
860 '1, '||
861 '1, '||
862 'msi.inventory_item_id, '||
863 'msi.organization_id, '||
864 'msi.sr_instance_id, '||
865 'msi.plan_id, '||
866 '-1,'||
867 ' :p_org_id, '||
868 ' 0, ' || -- NODE_REGULAR_ITEM
869 ' 1, '|| -- org seq
870 ' :p_org_code,'||
871 'mic.category_name '||
872 ' FROM msc_system_items msi, msc_item_categories mic' ||
873 ' WHERE mic.organization_id = msi.organization_id ' ||
874 'AND mic.sr_instance_id = msi.sr_instance_id ' ||
875 'AND mic.inventory_item_id = msi.inventory_item_id ' ||
876 'AND mic.category_set_id = :p_category_set '||
877 'AND mic.category_name = :p_category_name '||
878 'AND msi.plan_id = :p_plan_id ';
879
880 if p_org_id <> -1 then
881 sql_stmt := sql_stmt ||
882 ' and msi.sr_instance_id = :p_inst_id '||
883 ' and msi.organization_id = :p_org_list ';
884 p_org_code := msc_get_name.org_code(p_org_id, p_inst_id);
885 else -- item across all orgs
886 sql_stmt := sql_stmt ||
887 ' and -1 = :p_inst_id '||
888 ' and -1 = :p_org_list ';
889 p_org_code := 'All Orgs for this Plan';
890 end if;
891 -- dbms_output.put_line(p_inst_id||','||p_org_id||','||p_category_name);
892 EXECUTE IMMEDIATE sql_stmt using p_query_id,p_org_id,p_org_code,
893 p_category_set, p_category_name, p_plan_id,
894 p_inst_id,p_org_id;
895
896 END query_list;
897
898 FUNCTION flush_suggAlloc_drillDown(p_item_query_id number,
899 p_sub_type number,
900 p_start_date date, p_end_date date)
901 return NUMBER IS
902 p_query_id number;
903 BEGIN
904
905 SELECT msc_form_query_s.nextval
906 INTO p_query_id
907 FROM dual;
908
909 if p_sub_type in (1,9) then -- total, reqular org
910 insert into msc_form_query(
911 query_id,
912 last_update_date,
913 last_updated_by,
914 creation_date,
915 created_by,
916 last_update_login,
917 number1)
918 SELECT distinct p_query_id,
919 sysdate,
920 1,
921 sysdate,
922 1,
923 1,
924 mslp.parent_id
925 from msc_supplies ms,
926 msc_supplies parent,
927 msc_single_lvl_peg mslp,
928 msc_form_query mfq
929 where ms.organization_id = mfq.number2
930 AND ms.sr_instance_id = mfq.number3
931 and ms.plan_id = mfq.number4
932 and ms.inventory_item_id = mfq.number1
933 and mslp.plan_id = ms.plan_id
934 and mslp.pegging_type = 1 -- supply to parent supply
935 and mslp.child_id = ms.transaction_id
936 and mslp.parent_id = parent.transaction_id
937 and mslp.plan_id = parent.plan_id
938 and parent.order_type = 51
939 and ms.new_schedule_date between p_start_date and p_end_date
940 and mfq.query_id = p_item_query_id;
941
942 insert into msc_form_query(
943 query_id,
944 last_update_date,
945 last_updated_by,
946 creation_date,
947 created_by,
948 last_update_login,
949 number1)
950 SELECT distinct p_query_id,
951 sysdate,
952 1,
953 sysdate,
954 1,
955 1,
956 md.demand_id
957 from msc_supplies ms,
958 msc_supplies parent,
959 msc_demands md,
960 msc_single_lvl_peg mslp,
961 msc_form_query mfq
962 where ms.organization_id = mfq.number2
963 AND ms.sr_instance_id = mfq.number3
964 and ms.plan_id = mfq.number4
965 and ms.inventory_item_id = mfq.number1
966 and mslp.plan_id = ms.plan_id
967 and mslp.pegging_type = 1 -- supply to parent supply
968 and mslp.child_id = ms.transaction_id
969 and mslp.parent_id = parent.transaction_id
970 and mslp.plan_id = parent.plan_id
971 and parent.order_type = 2
972 and md.plan_id = parent.plan_id
973 and md.disposition_id = parent.transaction_id
974 and ms.new_schedule_date between p_start_date and p_end_date
975 and mfq.query_id = p_item_query_id;
976 end if; -- if p_sub_type in (1,9) then
977
978 if p_sub_type <> 9 then -- not a reqular org
979 insert into msc_form_query(
980 query_id,
981 last_update_date,
982 last_updated_by,
983 creation_date,
984 created_by,
985 last_update_login,
986 number1)
987 SELECT distinct p_query_id,
988 sysdate,
989 1,
990 sysdate,
991 1,
992 1,
993 mslp.parent_id
994 from msc_supplies ms,
995 msc_single_lvl_peg mslp,
996 msc_demands md,
997 msc_form_query mfq
998 where ms.organization_id = mfq.number2
999 AND ms.sr_instance_id = mfq.number3
1000 and ms.plan_id = mfq.number4
1001 and ms.inventory_item_id = mfq.number1
1002 and mslp.plan_id = ms.plan_id
1003 and mslp.pegging_type = 2 -- supply to parent demand
1004 and mslp.child_id = ms.transaction_id
1005 and md.plan_id = mslp.plan_id
1006 and md.demand_id = mslp.parent_id
1007 and md.origination_type in (24,29,30)
1008 and nvl(md.demand_source_type,0) <> 8
1009 and ms.new_schedule_date between p_start_date and p_end_date
1010 and mfq.query_id = p_item_query_id;
1011 end if; -- if p_sub_type <> 9 then -- not a reqular org
1012
1013 return p_query_id;
1014 EXCEPTION when others THEN
1015 return p_query_id;
1016 END flush_suggAlloc_drillDown;
1017
1018 END MSC_ALLOCATION_PLAN;