[Home] [Help]
PACKAGE BODY: APPS.MSC_GLOBAL_FORECASTING
Source
1 PACKAGE BODY MSC_GLOBAL_FORECASTING AS
2 /* $Header: MSCPHOGB.pls 120.11 2007/01/17 19:25:29 hulu ship $ */
3
4 SYS_YES CONSTANT INTEGER := 1;
5 SYS_NO CONSTANT INTEGER := 2;
6
7
8 ORIGINAL CONSTANT INTEGER := 210; /* GLOBAL FORECASTING lookup */
9 CUM_ORIGINAL CONSTANT INTEGER := 220;
10 CONSUMED CONSTANT INTEGER := 230;
11 CUM_CONSUMED CONSTANT INTEGER := 240;
12 FCST_SUBS_IN CONSTANT INTEGER := 243;
13 FCST_SUBS_OUT CONSTANT INTEGER := 246;
14 CURRENT CONSTANT INTEGER := 250;
15 CUM_CURRENT CONSTANT INTEGER := 260;
16 EXPIRED CONSTANT INTEGER := 270;
17 OVER_CONSUMED CONSTANT INTEGER := 280; -- not shown
18 SO_ORIGINAL CONSTANT INTEGER := 310;
19 SO_SUBS_IN CONSTANT INTEGER := 320;
20 SO_SUBS_OUT CONSTANT INTEGER := 330;
21 SO_CURRENT CONSTANT INTEGER := 340;
22
23 ORIGINAL_OFF CONSTANT INTEGER := 0; /* offsets */
24 CUM_ORIGINAL_OFF CONSTANT INTEGER := 1;
25 CONSUMED_OFF CONSTANT INTEGER := 2;
26 CUM_CONSUMED_OFF CONSTANT INTEGER := 3;
27 FCST_SUBS_IN_OFF CONSTANT INTEGER := 4;
28 FCST_SUBS_OUT_OFF CONSTANT INTEGER := 5;
29 CURRENT_OFF CONSTANT INTEGER := 6;
30 CUM_CURRENT_OFF CONSTANT INTEGER := 7;
31 EXPIRED_OFF CONSTANT INTEGER := 8;
32 SO_ORIGINAL_OFF CONSTANT INTEGER := 9;
33 SO_SUBS_IN_OFF CONSTANT INTEGER := 10;
34 SO_SUBS_OUT_OFF CONSTANT INTEGER := 11;
35 SO_CURRENT_OFF CONSTANT INTEGER := 12;
36 OVER_CONSUMED_OFF CONSTANT INTEGER := 13;
37
38 NUM_OF_TYPES CONSTANT INTEGER := 14;
39
40 /* global variable for number of buckets to display for the plan */
41 g_num_of_buckets NUMBER;
42
43 g_error_stmt VARCHAR2(200);
44
45
46
47 NODE_REGULAR_ITEM CONSTANT NUMBER :=0;
48 NODE_ITEM_SUPPLIER CONSTANT NUMBER := 1;
49 NODE_DEPT_RES CONSTANT NUMBER := 2;
50 NODE_LINE CONSTANT NUMBER := 3;
51 NODE_TRANS_RES CONSTANT NUMBER := 4;
52 NODE_PF_ITEM CONSTANT NUMBER := 5;
53 NODE_GL_FORECAST_ITEM CONSTANT NUMBER := 6;
54
55 TYPE column_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
56
57 Procedure populate_horizontal_plan (
58 item_list_id IN NUMBER,
59 arg_plan_id IN NUMBER,
60 arg_plan_organization_id IN NUMBER,
61 arg_plan_instance_id IN NUMBER,
62 arg_cutoff_date IN DATE,
63 enterprize_view IN BOOLEAN,
64 arg_res_level IN NUMBER DEFAULT 1,
65 arg_resval1 IN VARCHAR2 DEFAULT NULL,
66 arg_resval2 IN NUMBER DEFAULT NULL,
67 arg_ep_view_also IN BOOLEAN DEFAULT FALSE) IS
68
69 -- -------------------------------------------------
70 -- This cursor select number of buckets in the plan.
71 -- -------------------------------------------------
72 CURSOR plan_buckets IS
73 SELECT DECODE(arg_plan_id, -1, sysdate, trunc(curr_start_date)),
74 DECODE(arg_plan_id, -1, sysdate+365, trunc(curr_cutoff_date))
75 FROM msc_plans
76 WHERE plan_id = arg_plan_id;
77
78 CURSOR get_plan_type IS
79 Select plan_type
80 FROM msc_plans
81 WHERE plan_id = arg_plan_id;
82
83 -- -------------------------------------------------
84 -- This cursor selects the dates for the buckets.
85 -- -------------------------------------------------
86 CURSOR bucket_dates(p_start_date DATE, p_end_date DATE) IS
87 SELECT cal.calendar_date
88 FROM msc_calendar_dates cal,
89 msc_trading_partners tp
90 WHERE tp.sr_tp_id = arg_plan_organization_id
91 AND tp.sr_instance_id = arg_plan_instance_id
92 AND tp.calendar_exception_set_id = cal.exception_set_id
93 AND tp.partner_type = 3
94 AND tp.calendar_code = cal.calendar_code
95 AND tp.sr_instance_id = cal.sr_instance_id
96 AND cal.calendar_date BETWEEN TRUNC(p_start_date) AND TRUNC(p_end_date)
97 ORDER BY cal.calendar_date;
98
99 l_plan_start_date DATE;
100 l_plan_end_date DATE;
101
102 l_bucket_number NUMBER := 0;
103 l_bucket_date DATE;
104
105 last_date DATE;
106
107 l_plan_type NUMBER := 1;
108
109 -- --------------------------------------------
110 -- This cursor selects the snapshot activity in
111 -- MSC_DEMANDS and MSC_SUPPLIES
112 -- for the items per organizatio for a plan..
113 -- --------------------------------------------
114
115 -- Ship To Values
116 -- 7 Customer Site
117 -- 4 Customer
118 -- 9 Customer Zone
119 -- 8 Zone
120 -- 10 Demand Class
121 -- 6 Item
122 -- 2 Ship_ID
123 -- 3 Bill_ID
124
125 CURSOR mrp_snapshot_global_activity IS
126 SELECT list.number5 item_id,
127 list.number6 org_id,
128 list.number3 inst_id,
129 decode(list.number8, 4, nvl(to_char(md.customer_id), '-99'),
130 6, to_char(md.inventory_item_id),
131 7, nvl(to_char(md.customer_site_id), '-99'),
132 8, nvl(to_char(md.zone_id),'-99'),
133 9, decode(md.zone_id, null, '-99',
134 to_char(md.customer_id)||':'||
135 to_char(md.zone_id)),
136 2, nvl(to_char(md.ship_to_site_id),'-99'),
137 3, nvl(to_char(md.bill_id),'-99'),
138 10, nvl(md.demand_class,'-99'), '-1') ship_to,
139 decode(md.origination_type,6, SO_ORIGINAL, 30, SO_ORIGINAL,
140 ORIGINAL) row_type,
141 decode(md.origination_type,6, SO_ORIGINAL_OFF, 30, SO_ORIGINAL_OFF,
142 ORIGINAL_OFF) offset,
143 md.using_assembly_demand_date new_date,
144 md.using_assembly_demand_date old_date,
145 sum(md.using_requirement_quantity) new_quantity,
146 sum(nvl(md.UNMET_QUANTITY,0)) unmet_quantity
147 FROM msc_form_query list,
148 msc_demands md
149 WHERE md.plan_id = list.number4
150 AND md.inventory_item_id = list.number1
151 AND md.organization_id = list.number2
152 AND md.sr_instance_id = list.number3
153 AND list.query_id = item_list_id
154 and md.origination_type in (7, 29, 6, 30)
155 and trunc(md.using_assembly_demand_date) <= l_plan_end_date
156 and list.number7 = NODE_GL_FORECAST_ITEM -- Select only GF
157 and (md.original_item_id is null or
158 md.original_item_id = md.inventory_item_id)
159 GROUP BY
160 list.number5,
161 list.number6,
162 list.number3,
163 decode(list.number8, 4, nvl(to_char(md.customer_id), '-99'),
164 6, to_char(md.inventory_item_id),
165 7, nvl(to_char(md.customer_site_id), '-99'),
166 8, nvl(to_char(md.zone_id),'-99'),
167 9, decode(md.zone_id, null, '-99',
168 to_char(md.customer_id)||':'||
169 to_char(md.zone_id)),
170 2, nvl(to_char(md.ship_to_site_id),'-99'),
171 3, nvl(to_char(md.bill_id),'-99'),
172 10, nvl(md.demand_class,'-99'), '-1'),
173 decode(md.origination_type,6, SO_ORIGINAL, 30, SO_ORIGINAL,
174 ORIGINAL),
175 decode(md.origination_type,6, SO_ORIGINAL_OFF, 30, SO_ORIGINAL_OFF,
176 ORIGINAL_OFF),
177 md.using_assembly_demand_date
178 UNION ALL
179 SELECT list.number5 item_id,
180 list.number6 org_id,
181 list.number3 inst_id,
182 decode(list.number8, 6, to_char(forecast.inventory_item_id),
183 2, nvl(to_char(forecast.ship_id),'-99'),
184 3, nvl(to_char(forecast.bill_id),'-99'),
185 10, nvl(forecast.demand_class,'-99'),
186 MSC_GLOBAL_FORECASTING.get_ship_to(
187 list.number8,
188 forecast.plan_id,
189 forecast.sales_order_id)) ship_to,
190 CONSUMED row_type,
191 CONSUMED_OFF offset,
192 forecast.consumption_date new_date,
193 forecast.consumption_date old_date,
194 sum(nvl(forecast.consumed_qty,0)) new_quantity,
195 0 old_quantity
196 FROM msc_form_query list,
197 msc_forecast_updates forecast
198 where forecast.organization_id = list.number2
199 AND forecast.plan_id = list.number4
200 AND forecast.inventory_item_id = list.number1
201 AND forecast.sr_instance_id = list.number3
202 AND list.query_id = item_list_id
203 and trunc(forecast.consumption_date) <= l_plan_end_date
204 and list.number7 = NODE_GL_FORECAST_ITEM -- Select only GF
205 GROUP BY
206 list.number5,
207 list.number6,
208 list.number3,
209 decode(list.number8, 6, to_char(forecast.inventory_item_id),
210 2, nvl(to_char(forecast.ship_id),'-99'),
211 3, nvl(to_char(forecast.bill_id),'-99'),
212 10, nvl(forecast.demand_class,'-99'),
213 MSC_GLOBAL_FORECASTING.get_ship_to(
214 list.number8,
215 forecast.plan_id,
216 forecast.sales_order_id)),
217 CONSUMED, CONSUMED_OFF,
218 forecast.consumption_date
219 UNION ALL -- substitution in
220 SELECT list.number5 item_id,
221 list.number6 org_id,
222 list.number3 inst_id,
223 decode(list.number8, 4, nvl(to_char(md.customer_id), '-99'),
224 6, to_char(md.inventory_item_id),
225 7, nvl(to_char(md.customer_site_id), '-99'),
226 8, nvl(to_char(md.zone_id),'-99'),
227 9, decode(md.zone_id, null, '-99',
228 to_char(md.customer_id)||':'||
229 to_char(md.zone_id)),
230 2, nvl(to_char(md.ship_to_site_id),'-99'),
231 3, nvl(to_char(md.bill_id),'-99'),
232 10, nvl(md.demand_class,'-99'), '-1') ship_to,
233 decode(md.origination_type, 6, SO_SUBS_IN, 30, SO_SUBS_IN,
234 FCST_SUBS_IN) row_type,
235 decode(md.origination_type, 6, SO_SUBS_IN_OFF, 30, SO_SUBS_IN_OFF,
236 FCST_SUBS_IN_OFF) offset,
237 md.using_assembly_demand_date new_date,
238 md.using_assembly_demand_date old_date,
239 sum(nvl(md.using_requirement_quantity,0)) new_quantity,
240 sum(nvl(md.UNMET_QUANTITY,0)) unmet_quantity
241 FROM msc_form_query list,
242 msc_demands md
243 WHERE md.plan_id = list.number4
244 AND md.inventory_item_id = list.number1
245 AND md.organization_id = list.number2
246 AND md.sr_instance_id = list.number3
247 AND list.query_id = item_list_id
248 and md.origination_type in (7, 29, 6, 30)
249 and trunc(md.using_assembly_demand_date) <= l_plan_end_date
250 and md.original_item_id <> md.inventory_item_id
251 and list.number7 = NODE_GL_FORECAST_ITEM -- Select only GF
252 GROUP BY
253 list.number5,
254 list.number6,
255 list.number3,
256 decode(list.number8, 4, nvl(to_char(md.customer_id), '-99'),
257 6, to_char(md.inventory_item_id),
258 7, nvl(to_char(md.customer_site_id), '-99'),
259 8, nvl(to_char(md.zone_id),'-99'),
260 9, decode(md.zone_id, null, '-99',
261 to_char(md.customer_id)||':'||
262 to_char(md.zone_id)),
263 2, nvl(to_char(md.ship_to_site_id),'-99'),
264 3, nvl(to_char(md.bill_id),'-99'),
265 10, nvl(md.demand_class,'-99'), '-1'),
266 decode(md.origination_type, 6, SO_SUBS_IN, 30, SO_SUBS_IN,
267 FCST_SUBS_IN),
268 decode(md.origination_type, 6, SO_SUBS_IN_OFF, 30, SO_SUBS_IN_OFF,
269 FCST_SUBS_IN_OFF),
270 md.using_assembly_demand_date
271 UNION ALL -- substitution out
272 SELECT list.number5 item_id,
273 list.number6 org_id,
274 list.number3 inst_id,
275 decode(list.number8, 4, nvl(to_char(md.customer_id), '-99'),
276 6, to_char(list.number1),
277 7, nvl(to_char(md.customer_site_id), '-99'),
278 8, nvl(to_char(md.zone_id),'-99'),
279 9, decode(md.zone_id, null, '-99',
280 to_char(md.customer_id)||':'||
281 to_char(md.zone_id)),
282 2, nvl(to_char(md.ship_to_site_id),'-99'),
283 3, nvl(to_char(md.bill_id),'-99'),
284 10, nvl(md.demand_class,'-99'), '-1') ship_to,
285 decode(md.origination_type, 6, SO_SUBS_OUT, 30, SO_SUBS_OUT,
286 FCST_SUBS_OUT) row_type,
287 decode(md.origination_type, 6, SO_SUBS_OUT_OFF, 30, SO_SUBS_OUT_OFF,
288 FCST_SUBS_OUT_OFF) offset,
289 md.using_assembly_demand_date new_date,
290 md.using_assembly_demand_date old_date,
291 sum(nvl(md.using_requirement_quantity,0)) new_quantity,
292 sum(nvl(md.UNMET_QUANTITY,0)) unmet_quantity
293 FROM msc_form_query list,
294 msc_demands md
295 WHERE md.plan_id = list.number4
296 AND md.original_item_id = list.number1
297 AND md.organization_id = list.number2
298 AND md.sr_instance_id = list.number3
299 AND list.query_id = item_list_id
300 and md.origination_type in (7, 29, 6, 30)
301 and trunc(md.using_assembly_demand_date) <= l_plan_end_date
302 and md.original_item_id <> md.inventory_item_id
303 and list.number7 = NODE_GL_FORECAST_ITEM -- Select only GF
304 GROUP BY
305 list.number5,
306 list.number6,
307 list.number3,
308 decode(list.number8, 4, nvl(to_char(md.customer_id), '-99'),
309 6, to_char(list.number1),
310 7, nvl(to_char(md.customer_site_id), '-99'),
311 8, nvl(to_char(md.zone_id),'-99'),
312 9, decode(md.zone_id, null, '-99',
313 to_char(md.customer_id)||':'||
314 to_char(md.zone_id)),
315 2, nvl(to_char(md.ship_to_site_id),'-99'),
316 3, nvl(to_char(md.bill_id),'-99'),
317 10, nvl(md.demand_class,'-99'), '-1'),
318 decode(md.origination_type, 6, SO_SUBS_OUT, 30, SO_SUBS_OUT,
319 FCST_SUBS_OUT) ,
320 decode(md.origination_type, 6, SO_SUBS_OUT_OFF, 30, SO_SUBS_OUT_OFF,
321 FCST_SUBS_OUT_OFF) ,
322 md.using_assembly_demand_date
323 UNION ALL
324 --------------------------------------------------------------------
325 -- This select will ensure that all selected items get into cursor
326 -- even though they do not have any activity
327 ---------------------------------------------------------------------
328 SELECT list.number5,
329 list.number6,
330 list.number3,
331 '0',
332 ORIGINAL,
333 ORIGINAL_OFF,
334 to_date(1, 'J'),
335 to_date(1, 'J'),
336 0,
337 0
338 FROM msc_form_query list
339 WHERE list.query_id = item_list_id
340 ORDER BY
341 1, 2,4,7;
342
343 TYPE mrp_activity IS RECORD
344 (item_id NUMBER,
345 org_id NUMBER,
346 inst_id NUMBER,
347 ship_to VARCHAR2(200),
348 row_type NUMBER,
349 offset NUMBER,
350 new_date DATE,
351 old_date DATE,
352 new_quantity NUMBER,
353 unmet_quantity NUMBER);
354
355 activity_rec mrp_activity;
356
357 TYPE calendar_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
358
359 TYPE column_char IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
360
361 var_dates calendar_date; -- Holds the start dates of buckets
362 bucket_cells_tab column_number; -- Holds the quantities per bucket
363 ep_bucket_cells_tab column_number;
364 last_item_id NUMBER := -2;
365
366 last_org_id NUMBER := -2;
367 last_inst_id NUMBER := -2;
368 last_ship_to varchar2(200) := '-2';
369
370 bucket_counter BINARY_INTEGER := 0; -- Id of the current bucket
371 counter BINARY_INTEGER := 0;
372
373 -- =============================================================================
374 --
375 -- add_to_plan add the 'quantity' to the correct cell of the current bucket.
376 --
377 -- =============================================================================
378
379 PROCEDURE add_to_plan(bucket IN NUMBER,
380 offset IN NUMBER,
381 quantity IN NUMBER,
382 p_enterprise IN boolean default false) IS
383 location NUMBER;
384 BEGIN
385 g_error_stmt := 'Debug - add_to_plan - 10 '||offset||','||bucket||','||quantity;
386 /*
387 if offset = CONSUMED_OFF then
388 dbms_output.put_line(bucket||','||quantity);
389 end if;
390 */
391 if quantity = 0 then
392 return;
393 end if;
394
395 IF p_enterprise then
396 location := (bucket - 1) + offset;
397 ep_bucket_cells_tab(location) :=
398 NVL(ep_bucket_cells_tab(location),0) + quantity;
399 ELSE -- not enterprize view
400 location := ((bucket - 1) * NUM_OF_TYPES) + offset;
401 bucket_cells_tab(location) :=
402 NVL(bucket_cells_tab(location),0) + quantity;
403 END IF;
404
405 END;
406
407 -- =============================================================================
408 --
409 -- flush_item_plan inserts into MRP_MATERIAL_PLANS
410 --
411 -- =============================================================================
412
413 PROCEDURE flush_item_plan(p_item_id IN NUMBER,
414 p_org_id IN NUMBER,
415 p_inst_id IN NUMBER,
416 p_ship_to IN VARCHAR2) IS
417 loop_counter BINARY_INTEGER := 1;
418
419 l_org_cum NUMBER := 0;
420 l_consumed_cum NUMBER :=0;
421 l_current_cum NUMBER := 0;
422 l_current NUMBER := 0;
423
424 TYPE bkt_data_rec IS RECORD(
425 qty1 column_number,
426 qty2 column_number,
427 qty3 column_number,
428 qty4 column_number,
429 qty5 column_number,
430 qty6 column_number,
431 qty7 column_number,
432 qty8 column_number,
433 qty9 column_number,
434 qty10 column_number,
435 qty11 column_number,
436 qty12 column_number,
437 qty13 column_number);
438
439 bkt_data bkt_data_rec;
440
441 BEGIN
442
443 IF NOT enterprize_view THEN
444
445 FOR loop IN 1..g_num_of_buckets LOOP
446
447 -- ----------------------------
448 -- Calculate Cumulative Original
449 -- -----------------------------
450
451 -- original qty is after subs out, needs to add them back
452
453 add_to_plan(loop,
454 ORIGINAL_OFF,
455 bucket_cells_tab(((loop-1) * NUM_OF_TYPES) + FCST_SUBS_OUT_OFF)) ;
456
457 add_to_plan(loop,
458 ORIGINAL_OFF,
459 bucket_cells_tab(((loop-1) * NUM_OF_TYPES) + CONSUMED_OFF)) ;
460
461 add_to_plan(loop,
462 SO_ORIGINAL_OFF,
463 bucket_cells_tab(((loop-1) * NUM_OF_TYPES) + SO_SUBS_OUT_OFF)) ;
464
465
466 l_org_cum := l_org_cum +
467 bucket_cells_tab(((loop-1) * NUM_OF_TYPES) + ORIGINAL_OFF);
468
469 add_to_plan(loop,
470 CUM_ORIGINAL_OFF,
471 l_org_cum);
472
473 -- ----------------------------
474 -- Calculate Cumulative Consumed
475 -- -----------------------------
476
477 l_consumed_cum := l_consumed_cum +
478 bucket_cells_tab(((loop-1) * NUM_OF_TYPES) + CONSUMED_OFF);
479
480 add_to_plan(loop,
481 CUM_CONSUMED_OFF,
482 l_consumed_cum);
483
484
485 -- ----------------------------
486 -- Calculate Current current = Origina - (consumed + over consumed)
487 -- -----------------------------
488
489
490 add_to_plan(loop,
491 CURRENT_OFF,
492 bucket_cells_tab(((loop-1) * NUM_OF_TYPES) + ORIGINAL_OFF) -
493 bucket_cells_tab(((loop-1) * NUM_OF_TYPES) + CONSUMED_OFF) +
494 bucket_cells_tab(((loop-1) * NUM_OF_TYPES) + FCST_SUBS_IN_OFF) -
495 bucket_cells_tab(((loop-1) * NUM_OF_TYPES) + FCST_SUBS_OUT_OFF)
496 ) ;
497
498 add_to_plan(loop,
499 SO_CURRENT_OFF,
500 bucket_cells_tab(((loop-1) * NUM_OF_TYPES) + SO_ORIGINAL_OFF) +
501 bucket_cells_tab(((loop-1) * NUM_OF_TYPES) + SO_SUBS_IN_OFF) -
502 bucket_cells_tab(((loop-1) * NUM_OF_TYPES) + SO_SUBS_OUT_OFF)) ;
503
504 -- ----------------------------
505 -- Calculate Cumulative Current
506 -- -----------------------------
507
508 l_current_cum := l_current_cum +
509 bucket_cells_tab(((loop-1) * NUM_OF_TYPES) + CURRENT_OFF);
510
511 add_to_plan(loop,
512 CUM_CURRENT_OFF,
513 l_current_cum);
514
515 g_error_stmt := 'Debug - flush_item_plan - 30 - loop'||loop;
516
517 END LOOP;
518
519 g_error_stmt := 'Debug - flush_item_plan - 70';
520 FOR bkt IN 1..g_num_of_buckets LOOP
521 bkt_data.qty1(bkt) :=
522 bucket_cells_tab(NUM_OF_TYPES * (bkt - 1) + 0);
523 bkt_data.qty2(bkt) :=
524 bucket_cells_tab(NUM_OF_TYPES * (bkt - 1) + 1);
525 bkt_data.qty3(bkt) :=
526 bucket_cells_tab(NUM_OF_TYPES * (bkt - 1) + 2);
527 bkt_data.qty4(bkt) :=
528 bucket_cells_tab(NUM_OF_TYPES * (bkt - 1) + 3);
529 bkt_data.qty5(bkt) :=
530 bucket_cells_tab(NUM_OF_TYPES * (bkt - 1) + 4);
531 bkt_data.qty6(bkt) :=
532 bucket_cells_tab(NUM_OF_TYPES * (bkt - 1) + 5);
533 bkt_data.qty7(bkt) :=
534 bucket_cells_tab(NUM_OF_TYPES * (bkt - 1) + 6);
535 bkt_data.qty8(bkt) :=
536 bucket_cells_tab(NUM_OF_TYPES * (bkt - 1) + 7);
537 bkt_data.qty9(bkt) :=
538 bucket_cells_tab(NUM_OF_TYPES * (bkt - 1) + 8);
539 bkt_data.qty10(bkt) :=
540 bucket_cells_tab(NUM_OF_TYPES * (bkt - 1) + 9);
541 bkt_data.qty11(bkt) :=
542 bucket_cells_tab(NUM_OF_TYPES * (bkt - 1) + 10);
543 bkt_data.qty12(bkt) :=
544 bucket_cells_tab(NUM_OF_TYPES * (bkt - 1) + 11);
545 bkt_data.qty13(bkt) :=
546 bucket_cells_tab(NUM_OF_TYPES * (bkt - 1) + 12);
547 END LOOP;
548
549 FORALL bkt in 1..nvl(bkt_data.qty1.last,0)
550 INSERT INTO msc_material_plans(
551 query_id,
552 organization_id,
553 sr_instance_id,
554 plan_id,
555 plan_organization_id,
556 plan_instance_id,
557 inventory_item_id,
558 horizontal_plan_type,
559 horizontal_plan_type_text,
560 item_segments, -- store ship_to_level
561 bucket_type,
562 bucket_date,
563 last_update_date,
564 last_updated_by,
565 creation_date,
566 created_by,
567 quantity1, quantity2, quantity3, quantity4,
568 quantity5, quantity6, quantity7, quantity8,
569 quantity9, quantity10, quantity11, quantity12,
570 quantity13)
571 VALUES (
572 item_list_id,
573 p_org_id,
574 p_inst_id,
575 arg_plan_id,
576 arg_plan_organization_id,
577 arg_plan_instance_id,
578 p_item_id,
579 1,
580 'GLOBAL',
581 p_ship_to,
582 1,
583 var_dates(bkt),
584 SYSDATE,
585 -1,
586 SYSDATE,
587 -1,
588 bkt_data.qty1(bkt),
589 bkt_data.qty2(bkt),
590 bkt_data.qty3(bkt),
591 bkt_data.qty4(bkt),
592 bkt_data.qty5(bkt),
593 bkt_data.qty6(bkt),
594 bkt_data.qty7(bkt),
595 bkt_data.qty8(bkt),
596 bkt_data.qty9(bkt),
597 bkt_data.qty10(bkt),
598 bkt_data.qty11(bkt),
599 bkt_data.qty12(bkt),
600 bkt_data.qty13(bkt));
601
602 END IF; -- not enterprize view
603
604 IF enterprize_view or arg_ep_view_also then -- enterprise view
605
606 INSERT INTO msc_material_plans(
607 query_id,
608 organization_id,
609 sr_instance_id,
610 plan_id,
611 plan_organization_id,
612 plan_instance_id,
613 inventory_item_id,
614 horizontal_plan_type,
615 horizontal_plan_type_text,
616 item_segments, -- store ship_to_level
617 bucket_type,
618 bucket_date,
619 last_update_date,
620 last_updated_by,
621 creation_date,
622 created_by,
623 quantity1, quantity2, quantity3, quantity4,
624 quantity5, quantity6, quantity7, quantity8,
625 quantity9, quantity10, quantity11, quantity12,
626 quantity13)
627 VALUES (
628 item_list_id,
629 p_org_id,
630 p_inst_id,
631 arg_plan_id,
632 arg_plan_organization_id,
633 arg_plan_instance_id,
634 p_item_id,
635 10,
636 'GLOBAL',
637 p_ship_to,
638 1,
639 sysdate,
640 SYSDATE,
641 -1,
642 SYSDATE,
643 -1,
644 ep_bucket_cells_tab(0),
645 ep_bucket_cells_tab(1),
646 ep_bucket_cells_tab(2),
647 ep_bucket_cells_tab(3),
648 ep_bucket_cells_tab(4),
649 -- ep_bucket_cells_tab(0) - ep_bucket_cells_tab(2),
650 ep_bucket_cells_tab(5),
651 ep_bucket_cells_tab(6),
652 ep_bucket_cells_tab(7),
653 ep_bucket_cells_tab(8),
654 ep_bucket_cells_tab(9),
655 ep_bucket_cells_tab(10),
656 ep_bucket_cells_tab(11),
657 ep_bucket_cells_tab(12));
658 END IF;
659
660 END flush_item_plan;
661
662 -- =============================================================================
663
664 BEGIN
665
666 g_error_stmt := 'Debug - populate_horizontal_plan - 10';
667
668 OPEN plan_buckets;
669 FETCH plan_buckets into l_plan_start_date, l_plan_end_date;
670 CLOSE plan_buckets;
671
672 OPEN get_plan_type;
673 FETCH get_plan_type into l_plan_type;
674 CLOSE get_plan_type;
675
676 g_num_of_buckets := (l_plan_end_date + 1) - (l_plan_start_date - 1);
677
678 g_error_stmt := 'Debug - populate_horizontal_plan - 20';
679 -- ---------------------------------
680 -- Initialize the bucket cells to 0.
681 -- ---------------------------------
682 IF enterprize_view or arg_ep_view_also THEN
683 FOR counter IN 0..NUM_OF_TYPES LOOP
684 ep_bucket_cells_tab(counter) := 0;
685 END LOOP;
686 last_date := arg_cutoff_date;
687 END IF;
688
689 IF not (enterprize_view) THEN
690 FOR counter IN 0..(NUM_OF_TYPES * g_num_of_buckets) LOOP
691 bucket_cells_tab(counter) := 0;
692 END LOOP;
693
694 g_error_stmt := 'Debug - populate_horizontal_plan - 30';
695 -- --------------------
696 -- Get the bucket dates
697 -- --------------------
698 OPEN bucket_dates(l_plan_start_date-1, l_plan_end_date+1);
699 LOOP
700 FETCH bucket_dates INTO l_bucket_date;
701 EXIT WHEN BUCKET_DATES%NOTFOUND;
702 l_bucket_number := l_bucket_number + 1;
703 var_dates(l_bucket_number) := l_bucket_date;
704 END LOOP;
705 CLOSE bucket_dates;
706
707 last_date := arg_cutoff_date;
708 END IF;
709
710 g_error_stmt := 'Debug - populate_horizontal_plan - 40';
711 bucket_counter := 2;
712 activity_rec.item_id := 0;
713 activity_rec.ship_to := '0';
714 activity_rec.org_id := 0;
715 activity_rec.inst_id := 0;
716 activity_rec.row_type := 0;
717 activity_rec.offset := 0;
718 activity_rec.new_date := sysdate;
719 activity_rec.old_date := sysdate;
720 activity_rec.new_quantity := 0;
721 activity_rec.unmet_quantity := 0;
722
723 --dbms_output.put_line(' before loop');
724
725 OPEN mrp_snapshot_global_activity;
726 LOOP
727 FETCH mrp_snapshot_global_activity INTO activity_rec;
728 --if activity_rec.row_type = CONSUMED then
729 --dbms_output.put_line( activity_rec.item_id || ' ' || activity_rec.org_id || ' ' || activity_rec.inst_id || ' ' || activity_rec.ship_to || ' ' || activity_rec.row_type || ' ' || activity_rec.new_date || ' ' || activity_rec.new_quantity);
730 --end if;
731 --dbms_output.put_line( 'LAST ' || last_item_id || ' ' || last_org_id || ' ' || last_inst_id || ' ' || last_ship_to || ' ' );
732
733 IF ((mrp_snapshot_global_activity%NOTFOUND) OR
734 (activity_rec.item_id <> last_item_id) OR
735 (activity_rec.org_id <> last_org_id) OR
736 (activity_rec.inst_id <> last_inst_id) OR
737 (activity_rec.ship_to <> last_ship_to)) AND
738 last_item_id <> -2 THEN
739
740 -- --------------------------
741 -- Need to flush the plan for
742 -- the previous item.
743 -- --------------------------
744
745 -- dbms_output.put_line (' IN SIDE LOOP ' );
746
747 flush_item_plan(last_item_id,
748 last_org_id,
749 last_inst_id,
750 last_ship_to);
751
752 bucket_counter := 2;
753 -- ------------------------------------
754 -- Initialize the bucket cells to 0.
755 -- ------------------------------------
756 IF enterprize_view or arg_ep_view_also THEN
757 FOR counter IN 0..NUM_OF_TYPES LOOP
758 ep_bucket_cells_tab(counter) := 0;
759 END LOOP;
760 END IF;
761 IF not (enterprize_view) then
762 FOR counter IN 0..(NUM_OF_TYPES * g_num_of_buckets) LOOP
763 bucket_cells_tab(counter) := 0;
764 END LOOP;
765 END IF;
766 END IF; -- end of activity_rec.item_id <> last_item_id
767
768 EXIT WHEN mrp_snapshot_global_activity%NOTFOUND;
769
770 IF enterprize_view or arg_ep_view_also THEN
771 add_to_plan(activity_rec.offset + 1 , 0,
772 activity_rec.new_quantity,true);
773 if activity_rec.row_type = ORIGINAL then
774 add_to_plan(EXPIRED_OFF + 1 , 0,
775 activity_rec.unmet_quantity,true);
776 end if;
777 END IF;
778
779 IF not(enterprize_view) THEN
780
781 IF (bucket_counter <= g_num_of_buckets AND
782 activity_rec.new_date >= var_dates(bucket_counter)) THEN
783
784 -- -------------------------------------------------------
785 -- We got an activity falls after the current bucket. So we
786 -- will move the bucket counter forward until we find the
787 -- bucket where this activity falls. Note that we should
788 -- not advance the counter bejond g_num_of_buckets.
789 -- --------------------------------------------------------
790 WHILE (bucket_counter <= g_num_of_buckets AND
791 activity_rec.new_date >= var_dates(bucket_counter)) LOOP
792
793 bucket_counter := bucket_counter + 1;
794
795 END LOOP;
796 END IF;
797
798 IF (bucket_counter <= g_num_of_buckets AND
799 activity_rec.new_date < var_dates(bucket_counter)) THEN
800
801 add_to_plan(bucket_counter - 1,
802 activity_rec.offset,
803 activity_rec.new_quantity);
804 if activity_rec.row_type = ORIGINAL then
805
806 add_to_plan(bucket_counter - 1,
807 EXPIRED_OFF,
808 activity_rec.unmet_quantity);
809 end if;
810 END IF;
811
812 END IF; -- if not enterprise view
813
814 last_item_id := activity_rec.item_id;
815 last_ship_to := activity_rec.ship_to;
816 last_org_id := activity_rec.org_id;
817 last_inst_id := activity_rec.inst_id;
818 END LOOP;
819
820
821 g_error_stmt := 'Debug - populate_horizontal_plan - 50';
822 CLOSE mrp_snapshot_global_activity;
823
824 EXCEPTION
825
826 WHEN OTHERS THEN
827 null;
828 -- dbms_output.put_line(g_error_stmt);
829 raise;
830
831 END populate_horizontal_plan;
832
833
834 PROCEDURE query_list(
835 p_query_id IN NUMBER,
836 p_plan_id IN NUMBER,
837 p_item_list IN VARCHAR2,
838 p_org_list IN VARCHAR2) IS
839
840 p_item_id column_number;
841 p_org_id column_number;
842 p_inst_id column_number;
843
844 a number :=0;
845 l_len number;
846 one_record varchar2(100);
847 startPos number;
848 endPos number;
849 p_all_org_string varchar2(80) :='Global Org';
850
851 p_node_type number := NODE_GL_FORECAST_ITEM;
852 p_ship_to_level number :=0;
853
854 cursor ship_level is
855 select mps.ship_to
856 from msc_plan_schedules mps
857 where mps.plan_id = p_plan_id
858 and mps.organization_id =-1
859 and mps.ship_to is not null;
860
861
862 cursor local_forecasting(p_org number, p_inst number, p_item number) is
863 select mde.update_type
864 from msc_designators mde,
865 msc_demands md
866 where md.plan_id = p_plan_id
867 and md.organization_id = p_org
868 and md.sr_instance_id = p_inst
869 and md.inventory_item_id = p_item
870 and md.schedule_designator_id = mde.designator_id;
871
872 cursor org_c is
873 select organization_id, sr_instance_id
874 from msc_plan_organizations
875 where plan_id = p_plan_id;
876
877 p_display_org_id number;
878 p_display_org varchar2(100);
879 BEGIN
880
881
882 if p_org_list is null then
883 -- view item across orgs
884 OPEN org_c;
885 FETCH org_c BULK COLLECT INTO p_org_id, p_inst_id;
886 CLOSE org_c;
887 else
888 l_len := length(p_org_list);
889 WHILE l_len > 0 LOOP
890 a := a+1;
891 one_record := substr(p_org_list,instr(p_org_list,'(',1,a)+1,
892 instr(p_org_list,')',1,a)-instr(p_org_list,'(',1,a)-1);
893
894 p_inst_id(a) := to_number(substr(one_record,1,instr(one_record,',')-1));
895 p_org_id(a) := to_number(substr(one_record,instr(one_record,',')+1));
896 l_len := l_len - length(one_record)-3;
897
898 END LOOP;
899 end if; -- p_org_list is null
900
901 -- dbms_output.put_line(' given item list is ' || p_item_list);
902
903 a :=1;
904 startPos :=1;
905 endPos := instr(p_item_list||',', ',',1,a);
906 while endPos >0 loop
907 l_len := endPos - startPos;
908 p_item_id(a) := to_number(substr(p_item_list||',',startPos, l_len));
909 a := a+1;
910 startPos := endPos+1;
911 endPos := instr(p_item_list||',', ',',1,a);
912 end loop;
913 /*
914 dbms_output.put_line(' total items ' || p_item_id.count);
915 dbms_output.put_line(' org id count '|| p_org_id.count);
916 dbms_output.put_line(' item id count '|| p_item_id.count);
917 */
918
919 -- Check if its Global or Local Forecasting
920
921 OPEN ship_level;
922 FETCH ship_level INTO p_ship_to_level;
923 CLOSE ship_level;
924 -- dbms_output.put_line(' ship to ' || p_ship_to_level);
925
926 if p_ship_to_level = 0 then
927 -- no global demand sch is present, this is Local case
928
929 for a in 1..p_org_id.count loop
930 if p_org_list is null then
931 -- view item across all orgs
932 p_display_org_id := -1;
933 p_display_org := p_all_org_string ||' (Local Forecasting)';
934 else
935 p_display_org_id := p_org_id(a);
936 p_display_org :=
937 msc_get_name.org_code(p_org_id(a), p_inst_id(a)) ||
938 ' (Local Forecasting)';
939 end if;
940 for b in 1..p_item_id.count loop
941
942 OPEN local_forecasting(p_org_id(a), p_inst_id(a), p_item_id(b));
943 FETCH local_forecasting INTO p_ship_to_level;
944 CLOSE local_forecasting;
945 --dbms_output.put_line(p_org_id(a)||','|| p_inst_id(a)||','|| p_item_id(b)||','||p_ship_to_level||','||p_display_org_id);
946 begin
947 INSERT INTO msc_form_query (
948 query_id,
949 last_update_date,
950 last_updated_by,
951 creation_date,
952 created_by,
953 last_update_login,
954 number1, -- item_id
955 number2,
956 number3,
957 number4, -- plan_id
958 number5, -- displayed item_id
959 number6, -- displayed org_id
960 number7, -- node type
961 number8, -- ship_to_level
962 char1,
963 char2)
964 SELECT p_query_id,
965 sysdate,
966 1,
967 sysdate,
968 1,
969 1,
970 inventory_item_id,
971 organization_id,
972 sr_instance_id,
973 p_plan_id,
974 inventory_item_id,
975 p_display_org_id,
976 p_node_type,
977 nvl(p_ship_to_level,0),
978 p_display_org,
979 item_name
980 FROM msc_system_items
981 where plan_id = p_plan_id
982 and organization_id = p_org_id(a)
983 and sr_instance_id = p_inst_id(a)
984 and inventory_item_id = p_item_id(b);
985
986 exception when no_data_found then
987 null;
988 end;
989
990 p_ship_to_level := 0;
991
992 end loop; -- p_item_id.count
993 end loop; -- p_ord_id.count
994
995 else -- GLOBAL FORECASTING CASE
996
997 forall b in 1..p_item_id.count
998 INSERT INTO msc_form_query (
999 query_id,
1000 last_update_date,
1001 last_updated_by,
1002 creation_date,
1003 created_by,
1004 last_update_login,
1005 number1, -- item_id
1006 number2,
1007 number3,
1008 number4, -- plan_id
1009 number5, -- displayed item_id
1010 number6, -- displayed org_id
1011 number7, -- node type
1012 number8, -- ship_to_level
1013 char1,
1014 char2)
1015 SELECT p_query_id,
1016 sysdate,
1017 1,
1018 sysdate,
1019 1,
1020 1,
1021 p_item_id(b),
1022 -1, -- organization_id,
1023 p_inst_id(1),
1024 p_plan_id,
1025 p_item_id(b),
1026 -1, -- displayed org_id
1027 p_node_type,
1028 nvl(p_ship_to_level,0),
1029 p_all_org_string,
1030 msc_get_name.item_name(p_item_id(b), null,null,null)
1031 FROM dual;
1032
1033 End if; -- if p_ship_to_level = 0 then
1034
1035
1036 END query_list;
1037
1038 PROCEDURE get_detail_records(p_query_id IN NUMBER,
1039 p_node_type IN NUMBER,
1040 p_plan_id IN NUMBER,
1041 p_org_id IN NUMBER,
1042 p_inst_id IN NUMBER,
1043 p_item_id IN NUMBER,
1044 p_rowtype IN NUMBER,
1045 p_ship_level IN NUMBER,
1046 p_ship_id IN VARCHAR2,
1047 p_start_date IN DATE,
1048 p_end_date IN DATE) IS
1049
1050 sql_stmt VARCHAR2(32000);
1051 sql_stmt2 VARCHAR2(32000);
1052 l_ship_stmt varchar2(5000);
1053 BEGIN
1054
1055 if(p_ship_level = 4) then -- 4 Customer
1056 if (p_ship_id = '-99' ) then
1057 l_ship_stmt := ' and md.customer_id is null ';
1058 else
1059 l_ship_stmt := ' and md.customer_id = ' || p_ship_id ;
1060 end if;
1061 elsif(p_ship_level = 2) then -- 2 ship to
1062 if (p_ship_id = '-99' ) then
1063 l_ship_stmt := ' and md.ship_to_site_id is null ';
1064 else
1065 l_ship_stmt := ' and md.ship_to_site_id = ' || p_ship_id ;
1066 end if;
1067 elsif(p_ship_level = 3) then -- 3 bill to
1068 if (p_ship_id = '-99' ) then
1069 l_ship_stmt := ' and md.bill_id is null ';
1070 else
1071 l_ship_stmt := ' and md.bill_id = ' || p_ship_id ;
1072 end if;
1073 -- elsif(p_ship_level = 6) then -- 6 Item
1074 -- l_ship_stmt := ' and md.inventory_item_id = ' || p_ship_id ;
1075 elsif(p_ship_level = 7) then -- 7 Customer Site
1076 if (p_ship_id = '-99' ) then
1077 l_ship_stmt := ' and md.customer_site_id is null ';
1078 else
1079 l_ship_stmt := ' and md.customer_site_id = ' || p_ship_id ;
1080 end if;
1081
1082 elsif(p_ship_level in (8,9) ) then -- 8 Zone, 9 Customer Zone
1083 if (p_ship_id = '-99' ) then
1084 if p_rowtype = CONSUMED then
1085 -- consumed will show Sales Order which won't have zone
1086 l_ship_stmt := ' and mfu.zone_id is null ';
1087 else
1088 l_ship_stmt := ' and md.zone_id is null ';
1089 end if;
1090 else
1091 if p_rowtype = CONSUMED then
1092 l_ship_stmt := ' and mfu.zone_id = ' || p_ship_id ;
1093 else
1094 l_ship_stmt := ' and md.zone_id = ' || p_ship_id ;
1095 end if;
1096 end if;
1097 elsif(p_ship_level = 10 ) then -- 10 Demand Class
1098 if (p_ship_id = '-99' ) then
1099 l_ship_stmt := ' and md.demand_class is null ';
1100 else
1101 l_ship_stmt := ' and md.demand_class = ''' || p_ship_id || '''';
1102 end if;
1103 end if;
1104
1105 sql_stmt := 'INSERT INTO msc_form_query ( '||
1106 'query_id, '||
1107 'last_update_date, '||
1108 'last_updated_by, '||
1109 'creation_date, '||
1110 'created_by, '||
1111 'last_update_login, '||
1112 'number1) ' ||
1113 'SELECT distinct :p_query_id,' ||
1114 ' sysdate, '||
1115 ' 1, '||
1116 ' sysdate, '||
1117 ' 1, '||
1118 ' 1, ';
1119
1120 IF p_rowtype = CONSUMED THEN -- Forecast Consumed Row
1121
1122 sql_stmt := sql_stmt ||
1123 ' md.demand_id '||
1124 ' FROM msc_forecast_updates mfu, msc_demands md'||
1125 ' WHERE mfu.plan_id = :p_plan_id' ||
1126 ' AND mfu.sr_instance_id = :p_inst_id'||
1127 ' and mfu.organization_id =:p_org_id '||
1128 ' and mfu.inventory_item_id = :p_item_id' ||
1129 ' and mfu.plan_id = md.plan_id' ||
1130 ' and mfu.consumed_qty > 0'||
1131 ' and trunc(mfu.consumption_date) BETWEEN '''||
1132 p_start_date||''' AND '''|| p_end_date|| '''';
1133
1134 ELSIF p_rowtype in (ORIGINAL, SO_ORIGINAL,FCST_SUBS_OUT, SO_SUBS_OUT,
1135 FCST_SUBS_IN, SO_SUBS_IN) THEN
1136 if p_org_id = -1 then
1137 sql_stmt := sql_stmt ||
1138 ' md2.demand_id '||
1139 ' FROM msc_demands md,'||
1140 ' msc_demands md2 '||
1141 ' WHERE md.plan_id = :p_plan_id' ||
1142 ' AND md.sr_instance_id = :p_inst_id'||
1143 ' and md.organization_id =:p_org_id '||
1144 ' and trunc(md.using_assembly_demand_date) BETWEEN '''||
1145 p_start_date||''' AND '''|| p_end_date|| ''''||
1146 ' and md2.plan_id = md.plan_id '||
1147 ' and nvl(md2.original_demand_id,md2.demand_id) = md.demand_id ';
1148
1149 else
1150
1151 sql_stmt := sql_stmt ||
1152 ' md.demand_id ' ||
1153 ' FROM msc_demands md'||
1154 ' WHERE md.plan_id = :p_plan_id' ||
1155 ' AND md.sr_instance_id = :p_inst_id'||
1156 ' and md.organization_id =:p_org_id '||
1157 ' and trunc(md.using_assembly_demand_date) BETWEEN '''||
1158 p_start_date||''' AND '''|| p_end_date|| '''';
1159 end if;
1160 IF p_rowtype in (ORIGINAL, SO_ORIGINAL) THEN
1161 sql_stmt := sql_stmt ||
1162 ' and ((md.inventory_item_id = :p_item_id and' ||
1163 ' (md.original_item_id is null or '||
1164 ' md.original_item_id = md.inventory_item_id)) OR' ||
1165 ' (md.original_item_id = :p_item_id and '||
1166 ' md.original_item_id <> md.inventory_item_id )) ';
1167
1168 ELSIF p_rowtype in (FCST_SUBS_OUT, SO_SUBS_OUT) THEN
1169 sql_stmt := sql_stmt ||
1170 ' and md.original_item_id = :p_item_id' ||
1171 ' and md.original_item_id <> md.inventory_item_id ';
1172
1173 ELSIF p_rowtype in (FCST_SUBS_IN, SO_SUBS_IN) THEN
1174 sql_stmt := sql_stmt ||
1175 ' and md.inventory_item_id = :p_item_id' ||
1176 ' and md.original_item_id <> md.inventory_item_id ';
1177
1178 END IF;
1179
1180 ELSIF p_rowtype in (CURRENT, SO_CURRENT) THEN -- Current Row
1181 IF p_org_id = -1 then
1182 sql_stmt := sql_stmt ||
1183 ' orig_md.demand_id ' ||
1184 ' FROM msc_demands md,'||
1185 ' msc_demands orig_md'||
1186 ' WHERE md.plan_id = :p_plan_id' ||
1187 ' and md.sr_instance_id = :p_inst_id' ||
1188 ' and md.organization_id =:p_org_id '||
1189 ' and md.inventory_item_id = :p_item_id' ||
1190 ' and trunc(md.using_assembly_demand_date) BETWEEN '''||
1191 p_start_date||''' AND '''|| p_end_date|| ''''||
1192 ' and md.plan_id = orig_md.plan_id '||
1193 ' and md.demand_id = nvl(orig_md.original_demand_id, orig_md.demand_id) ';
1194 ELSE -- IF p_org_id <> -1 then
1195 sql_stmt := sql_stmt ||
1196 ' md.demand_id ' ||
1197 ' FROM msc_demands md'||
1198 ' WHERE md.plan_id = :p_plan_id' ||
1199 ' and md.sr_instance_id = :p_inst_id' ||
1200 ' and md.organization_id =:p_org_id '||
1201 ' and md.inventory_item_id = :p_item_id' ||
1202 ' and trunc(md.using_assembly_demand_date) BETWEEN '''||
1203 p_start_date||''' AND '''|| p_end_date|| '''';
1204
1205 END IF; -- IF p_org_id = -1 then
1206
1207 END IF; -- IF p_rowtype in (CURRENT, SO_CURRENT) THEN
1208
1209 IF p_rowtype in (ORIGINAL, CURRENT, FCST_SUBS_IN, FCST_SUBS_OUT) THEN
1210 -- Forecast
1211 sql_stmt := sql_stmt ||
1212 ' and md.origination_type in (7, 29) ';
1213 ELSIF p_rowtype <> CONSUMED then -- Sales Order won't have consume row
1214 sql_stmt := sql_stmt ||
1215 ' and md.origination_type in (6,30) ';
1216 END IF;
1217
1218 sql_stmt := sql_stmt || l_ship_stmt;
1219 /*
1220 dbms_output.put_line(substr(sql_stmt,1,240));
1221 dbms_output.put_line(substr(sql_stmt,241,240));
1222 dbms_output.put_line(substr(sql_stmt,481,240));
1223 dbms_output.put_line(substr(sql_stmt,721,240));
1224 */
1225
1226 IF p_rowtype = CONSUMED THEN
1227 sql_stmt2 := sql_stmt;
1228 sql_stmt := sql_stmt || ' and mfu.sales_order_id = md.demand_id ';
1229 END IF;
1230
1231 IF p_rowtype in (ORIGINAL, SO_ORIGINAL) THEN
1232
1233 EXECUTE IMMEDIATE sql_stmt using p_query_id,p_plan_id,p_inst_id,
1234 p_org_id,p_item_id,p_item_id;
1235 ELSE
1236 EXECUTE IMMEDIATE sql_stmt using p_query_id,p_plan_id,p_inst_id,
1237 p_org_id,p_item_id;
1238 END IF;
1239
1240 IF p_rowtype = CONSUMED THEN
1241 sql_stmt := sql_stmt2 ||
1242 ' and mfu.sales_order_id = md.original_demand_id ';
1243 EXECUTE IMMEDIATE sql_stmt using p_query_id,p_plan_id,p_inst_id,
1244 p_org_id,p_item_id;
1245 END IF;
1246
1247
1248 exception when others then
1249 null;
1250 END get_detail_records;
1251
1252 FUNCTION get_ship_to(p_ship_to_level number,
1253 p_plan_id number,
1254 p_sales_order_id number) return varchar2 IS
1255
1256 CURSOR ship_to_c IS
1257 select to_char(md.customer_id),
1258 to_char(md.customer_site_id),
1259 to_char(md.zone_id)
1260 from msc_demands md
1261 where plan_id = p_plan_id
1262 and demand_id = p_sales_order_id;
1263
1264 v_customer varchar2(100);
1265 v_customer_site varchar2(100);
1266 v_zone varchar2(100);
1267 BEGIN
1268
1269 OPEN ship_to_c;
1270 FETCH ship_to_c INTO v_customer, v_customer_site, v_zone;
1271 CLOSE ship_to_c;
1272
1273 if p_ship_to_level = 4 then
1274 return nvl(v_customer, '-99');
1275 elsif p_ship_to_level = 7 then
1276 return nvl(v_customer_site, '-99');
1277 elsif p_ship_to_level = 8 then
1278 return nvl(v_zone, '-99');
1279 elsif p_ship_to_level = 9 then
1280 if v_zone is not null then
1281 return v_customer||':'||v_zone;
1282 else
1283 return '-99';
1284 end if;
1285 end if;
1286
1287 return '-1';
1288 END get_ship_to;
1289
1290 END MSC_GLOBAL_FORECASTING;