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