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