[Home] [Help]
PACKAGE BODY: APPS.MSC_SUPPLIER_HORIZONTAL_PLAN
Source
1 PACKAGE BODY msc_supplier_horizontal_plan AS
2 /* $Header: MSCHSPLB.pls 120.9 2007/01/17 19:20:32 hulu ship $ */
3
4 SYS_YES CONSTANT INTEGER := 1;
5 SYS_NO CONSTANT INTEGER := 2;
6
7 NUM_OF_TYPES CONSTANT INTEGER := 10;
8
9 PURCHASE_ORDER CONSTANT INTEGER := 1;
10 PURCHASE_REQ CONSTANT INTEGER := 2;
11 PLANNED_ORDER CONSTANT INTEGER := 3;
12 REQUIRED_HOURS CONSTANT INTEGER := 4;
13 AVAILABLE_HOURS CONSTANT INTEGER := 5;
14 NET_AVAILABLE CONSTANT INTEGER := 6;
15 CUM_AVAILABLE CONSTANT INTEGER := 7;
16 UTILIZATION CONSTANT INTEGER := 8;
17 CUM_UTILIZATION CONSTANT INTEGER := 9;
18 PO_CONSUMPTION CONSTANT INTEGER := 10;
19
20 M_PLANNED_ORDER CONSTANT INTEGER := 5;
21 M_PURCHASE_ORDER CONSTANT INTEGER := 1;
22 M_PURCHASE_REQ CONSTANT INTEGER := 2;
23 M_PLANNED_ARRIVAL CONSTANT INTEGER := 51;
24
25 PROMISE_DATE CONSTANT INTEGER := 1;
26
27 TYPE calendar_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
28 TYPE column_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
29 TYPE column_char IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
30 TYPE supplier_activity IS RECORD
31 (inventory_item_id NUMBER,
32 supplier_id NUMBER,
33 supplier_site_id NUMBER,
34 type NUMBER,
35 start_date DATE,
36 end_date DATE,
37 quantity NUMBER);
38
39 g_bucket_count NUMBER := 2;
40 g_num_of_buckets NUMBER;
41 g_item_list_id NUMBER;
42 g_query_id NUMBER;
43 g_org_id NUMBER;
44 g_inst_id NUMBER;
45 g_plan_start_date DATE;
46 g_plan_end_date DATE;
47 g_bucket_date DATE;
48 g_cutoff_date DATE;
49 g_designator NUMBER;
50 g_bucket_type NUMBER;
51 g_error_stmt VARCHAR2(50);
52
53 g_plan_type number := 2;
54
55 g_use_sup_req number :=0;--5449978
56
57 g_dates calendar_date;
58 bucket_cells column_number;
59 activity_rec supplier_activity;
60 l_dock_date_prof number := nvl(FND_PROFILE.Value('MSC_PO_DOCK_DATE_CALC_PREF'),1);
61
62
63 --5220804bugfix, msc_supplier_requirements table added to snapshot to get consumption date
64
65 CURSOR supplier_snapshot_activity IS
66 -- ===================================
67 -- Supplier Availability
68 -- ===================================
69 SELECT DISTINCT
70 list.number8,
71 cap.supplier_id,
72 cap.supplier_site_id,
73 AVAILABLE_HOURS,
74 cal.calendar_date,
75 null,
76 cap.capacity
77 FROM
78 msc_trading_partners mtp,
79 msc_calendar_dates cal,
80 msc_supplier_capacities cap,
81 msc_item_suppliers items,
82 msc_form_query list
83 WHERE
84 cap.supplier_id = list.number2
85 AND NVL(cap.supplier_site_id,-1) = NVL(list.number5,-1)
86 AND cap.plan_id = g_designator
87 AND cap.inventory_item_id = list.number8
88 AND cap.capacity > 0
89 AND items.plan_id = cap.plan_id
90 AND items.sr_instance_id = g_inst_id
91 AND items.supplier_id = cap.supplier_id
92 AND items.inventory_item_id = cap.inventory_item_id
93 AND nvl(items.supplier_site_id, -1) = NVL(list.number5,-1)
94 AND cal.calendar_date BETWEEN trunc(cap.from_date) AND trunc(nvl(cap.to_date,g_cutoff_date))
95 AND cal.calendar_date >= decode(g_plan_type, 4, trunc(g_plan_start_date+2), nvl(trunc(items.supplier_lead_time_date+1),trunc(g_plan_start_date+2)))
96 AND cal.calendar_date <= trunc(g_cutoff_date)
97 AND (((items.delivery_calendar_code is not null and cal.seq_num IS NOT NULL)
98 or (items.delivery_calendar_code is null and g_plan_type <> 4))
99 or (g_plan_type = 4 and cal.seq_num is not null ))
100 AND mtp.sr_tp_id = g_org_id
101 AND mtp.sr_instance_id = g_inst_id
102 AND cal.calendar_code = nvl(items.delivery_calendar_code,mtp.calendar_code)
103 AND cal.exception_set_id = mtp.calendar_exception_set_id
104 AND cal.sr_instance_id = mtp.sr_instance_id
105 AND list.query_id = g_item_list_id
106 UNION ALL
107 -- ============================================
108 -- Supplier Requirements
109 -- ============================================
110 SELECT
111 list.number8,
112 DECODE(mr.order_type,M_PLANNED_ORDER,mr.source_supplier_id,
113 M_PLANNED_ARRIVAL, mr.source_supplier_id,
114 mr.supplier_id),
115 DECODE(mr.order_type,M_PLANNED_ORDER,mr.source_supplier_site_id,
116 M_PLANNED_ARRIVAL, mr.source_supplier_site_id,
117 mr.supplier_site_id),
118 decode(mr.order_type,
119 M_PLANNED_ORDER, PLANNED_ORDER,
120 M_PLANNED_ARRIVAL, PLANNED_ORDER,
121 M_PURCHASE_ORDER, PURCHASE_ORDER,
122 M_PURCHASE_REQ, PURCHASE_REQ),
123 decode(g_use_sup_req,
124 0,mr.new_dock_date,
125 1,msr.consumption_date),
126 null,
127 decode(g_use_sup_req,
128 0, sum(mr.new_order_quantity),
129 1, sum(msr.consumed_quantity)
130 )
131 FROM msc_form_query list,
132 msc_supplies mr,
133 msc_supplier_requirements msr,
134 msc_trading_partner_sites mtp
135 WHERE mr.order_type in (M_PLANNED_ORDER, M_PURCHASE_ORDER, M_PURCHASE_REQ, M_PLANNED_ARRIVAL)
136 AND mr.disposition_status_type <> 2
137 AND decode(mtp.shipping_control,'BUYER',mr.new_ship_date,mr.new_dock_date) <= trunc(g_cutoff_date)
138 AND decode(mtp.shipping_control,'BUYER',mr.new_ship_date,mr.new_dock_date) >= trunc(g_plan_start_date+1)
139 AND mr.plan_id = g_designator
140 AND DECODE(mr.order_type,M_PLANNED_ORDER,mr.source_supplier_id,
141 M_PLANNED_ARRIVAL,mr.source_supplier_id,
142 mr.supplier_id) = list.number2
143 AND DECODE(mr.order_type,
144 M_PLANNED_ORDER,NVL(mr.source_supplier_site_id,-1),
145 M_PLANNED_ARRIVAL,NVL(mr.source_supplier_site_id,-1),
146 NVL(mr.supplier_site_id,-1)) = NVL(list.number5,-1)
147 AND mr.inventory_item_id = list.number1
148 AND list.query_id = g_item_list_id
149 AND mtp.partner_site_id =
150 DECODE(mr.order_type,M_PLANNED_ORDER,mr.source_supplier_site_id,
151 M_PLANNED_ARRIVAL,mr.source_supplier_site_id,
152 mr.supplier_site_id)
153 AND mr.plan_id = msr.plan_id(+)
154 AND mr.sr_instance_id = msr.sr_instance_id(+)
155 AND mr.transaction_id = msr.supply_id(+)
156 GROUP BY list.number8,
157 DECODE(mr.order_type,M_PLANNED_ORDER,mr.source_supplier_id,
158 M_PLANNED_ARRIVAL, mr.source_supplier_id,
159 mr.supplier_id),
160 DECODE(mr.order_type,M_PLANNED_ORDER,mr.source_supplier_site_id,
161 M_PLANNED_ARRIVAL, mr.source_supplier_site_id,
162 mr.supplier_site_id),
163 DECODE(mr.order_type,
164 M_PLANNED_ORDER, PLANNED_ORDER,
165 M_PLANNED_ARRIVAL, PLANNED_ORDER,
166 M_PURCHASE_ORDER, PURCHASE_ORDER,
167 M_PURCHASE_REQ, PURCHASE_REQ),
168 DECODE(g_use_sup_req,
169 0,mr.new_dock_date,
170 1,msr.consumption_date)
171 /*
172 UNION ALL
173 -- ============================================
174 -- Purchase Orders Consumption
175 -- ============================================
176 SELECT
177 list.number8,
178 mr.supplier_id,
179 mr.supplier_site_id,
180 PO_CONSUMPTION,
181 mr.new_dock_date,
182 null,
183 to_number(
184 decode ( mr.order_type,
185 M_PURCHASE_ORDER, decode ( l_dock_date_prof,
186 PROMISE_DATE, decode ( mr.promised_date,
187 NULL, mr.new_order_quantity,
188 0),
189 0 ),
190 0)) po_consumption_quantity
191 FROM msc_form_query list,
192 msc_supplies mr,
193 msc_trading_partner_sites mtp
194 WHERE mr.order_type = M_PURCHASE_ORDER
195 AND mr.disposition_status_type <> 2
196 AND decode(mtp.shipping_control,'BUYER',mr.new_ship_date,mr.new_dock_date) <= trunc(g_cutoff_date)
197 AND decode(mtp.shipping_control,'BUYER',mr.new_ship_date,mr.new_dock_date) >= trunc(g_plan_start_date+1)
198 AND mr.plan_id = g_designator
199 AND mr.supplier_id = list.number2
200 AND NVL(mr.supplier_site_id,-1) = NVL(list.number5,-1)
201 AND mr.inventory_item_id = list.number1
202 AND list.query_id = g_item_list_id
203 AND mtp.partner_site_id = mr.supplier_site_id
204 */
205 UNION ALL
206 SELECT
207 list.number8,
208 list.number2,
209 list.number5,
210 AVAILABLE_HOURS,
211 g_plan_start_date,
212 null,
213 0
214 FROM msc_form_query list
215 WHERE list.query_id = g_item_list_id
216 ORDER BY 1,2,3,5,4;
217
218 -- =============================================================================
219 -- Name: initialize
220 -- Desc: initializes most of the global variables in the package
221 -- g_date() - is the structure that holds the beginning of each bucket
222 -- =============================================================================
223 PROCEDURE initialize IS
224 -- -----------------------------------------
225 -- This cursor selects row type information.
226 -- -----------------------------------------
227 v_sid NUMBER;
228 v_counter NUMBER;
229
230 CURSOR plan_buckets IS
231 SELECT DECODE(g_designator, -1, trunc(sysdate), trunc(curr_start_date)) - 1,
232 DECODE(g_designator, -1, trunc(sysdate+365), trunc(curr_cutoff_date))
233 FROM msc_plans
234 WHERE plan_id = g_designator;
235
236 CURSOR bucket_dates(p_start_date DATE, p_end_date DATE) IS
237 SELECT cal.calendar_date
238 FROM msc_trading_partners tp,
239 msc_calendar_dates cal
240 WHERE tp.sr_tp_id = g_org_id
241 AND tp.sr_instance_id = g_inst_id
242 AND tp.calendar_exception_set_id = cal.exception_set_id
243 AND tp.partner_type = 3
244 AND tp.calendar_code = cal.calendar_code
245 AND tp.sr_instance_id = cal.sr_instance_id
246 AND cal.calendar_date BETWEEN p_start_date AND p_end_date
247 ORDER BY cal.calendar_date;
248
249 l_bucket_date DATE;
250 l_bucket_number NUMBER := 0;
251
252 BEGIN
253
254 -- --------------------------
255 -- initialize query id
256 -- --------------------------
257 g_error_stmt := 'Debug - initialize - 10';
258 SELECT msc_supplier_plans_s.nextval
259 INTO g_query_id
260 FROM dual;
261
262 -- --------------------------------------------------------
263 -- Start and End date of plan, find total number of buckets
264 -- --------------------------------------------------------
265 g_error_stmt := 'Debug - initialize - 20';
266 OPEN plan_buckets;
267 FETCH plan_buckets into g_plan_start_date, g_plan_end_date;
268 CLOSE plan_buckets;
269
270 g_num_of_buckets := (g_plan_end_date + 1) - g_plan_start_date;
271
272
273 -- --------------------
274 -- Get the bucket dates
275 -- --------------------
276 g_error_stmt := 'Debug - initialize - 30';
277 OPEN bucket_dates(g_plan_start_date, g_plan_end_date+1);
278 LOOP
279 FETCH bucket_dates INTO l_bucket_date;
280 EXIT WHEN BUCKET_DATES%NOTFOUND;
281 l_bucket_number := l_bucket_number + 1;
282 g_dates(l_bucket_number) := l_bucket_date;
283 END LOOP;
284 CLOSE bucket_dates;
285
286 -- ----------------------------------
287 -- Initialize the bucket cells to 0.
288 -- ----------------------------------
289 g_error_stmt := 'Debug - initialize - 50';
290 FOR v_counter IN 1..(NUM_OF_TYPES * g_num_of_buckets) LOOP
291 bucket_cells(v_counter) := 0;
292 END LOOP;
293
294 g_error_stmt := 'Debug - initialize - 80';
295
296 END initialize;
297
298 -- =============================================================================
299 -- Name: add_to_plan
300 -- Desc: adds 'quantity' to the correct type and correct bucket cell.
301 -- If the end_date of the record is populated, then the qty is assumed
302 -- to have daily rather than total qty. We calculate # of workdays in each
303 -- bucket in the range of start_date-end_date, and populate each
304 -- bucket accordingly
305 -- =============================================================================
306 PROCEDURE add_to_plan IS
307 v_location NUMBER;
308 v_bucket_start DATE;
309 v_counter NUMBER;
310 v_bucket_size NUMBER;
311 v_res_cost NUMBER := 0;
312 BEGIN
313
314 g_error_stmt := 'Debug - add_to_plan - 10';
315 IF (activity_rec.start_date >= g_dates(g_bucket_count)) THEN
316 -- -------------------------------------------------------
317 -- We got an activity which falls after the current bucket. So we
318 -- will move the bucket counter forward until we find the
319 -- bucket where this activity falls. Note that we should
320 -- not advance the counter beyond g_num_of_buckets.
321 -- --------------------------------------------------------
322 WHILE ((activity_rec.start_date >= g_dates(g_bucket_count)) AND
323 (g_bucket_count <= g_num_of_buckets))
324 LOOP
325 g_bucket_count := g_bucket_count + 1;
326 END LOOP;
327
328 ---------------------------------------------------------------
329 --- If the activity start date is outside the last bucket there
330 --- is no need to add this activity to any bucket.
331 ---------------------------------------------------------------
332 if(activity_rec.start_date >= g_dates(g_bucket_count)) THEN
333 return;
334 end if;
335
336 END IF;
337
338
339 IF (activity_rec.end_date is null) THEN
340 -- -------------------------------------------------------
341 -- end date is null, we assume that the quantity
342 -- stands for total quantity and we dump the total
343 -- quantity on the first bucket
344 -- --------------------------------------------------------
345 g_error_stmt := 'Debug - add_to_plan - 20';
346 v_location := ((activity_rec.type-1) * g_num_of_buckets) +
347 g_bucket_count - 1;
348 bucket_cells(v_location) := bucket_cells(v_location) +
349 activity_rec.quantity;
350 END IF;
351
352 END add_to_plan;
353
354
355
356 -- =============================================================================
357 -- Name: calculate_cum
358 -- Desc: Some types of data need to be calculated or cumulated across dates. This
359 -- procedure takes care of that
360 -- =============================================================================
361 PROCEDURE calculate_cum IS
362
363 v_loop BINARY_INTEGER := 1;
364 v_cum_net_available NUMBER := 0;
365 v_cum_available NUMBER := 0;
366 v_cum_required NUMBER := 0;
367 BEGIN
368
369 -- ---------------------------------
370 -- The following will be calculated:
371 -- REQUIRED_HOURS
372 -- NET_AVAILABLE
373 -- CUM_AVAILABLE
374 -- UTILIZATION
375 -- CUM_UTILIZATION
376 -- -----------------------------
377 v_cum_available := 0;
378 v_cum_required := 0;
379
380 g_error_stmt := 'Debug - calculate_cum - 10';
381 FOR v_loop IN 1..g_num_of_buckets LOOP
382 -- -------------------
383 -- Required Hours
384 -- -------------------
385 g_error_stmt := 'Debug - calculate_cum - 20 - loop'||to_char(v_loop);
386 bucket_cells((REQUIRED_HOURS-1)*g_num_of_buckets+v_loop) :=
387 bucket_cells((PLANNED_ORDER-1)*g_num_of_buckets+v_loop) +
391
388 -- bucket_cells((PO_CONSUMPTION-1)*g_num_of_buckets+v_loop) +
389 bucket_cells((PURCHASE_ORDER-1)*g_num_of_buckets+v_loop) +
390 bucket_cells((PURCHASE_REQ-1)*g_num_of_buckets+v_loop);
392 -- -------------------
393 -- Net Available Hours
394 -- -------------------
395 g_error_stmt := 'Debug - calculate_cum - 30 - loop'||to_char(v_loop);
396 bucket_cells((NET_AVAILABLE-1)*g_num_of_buckets+v_loop) :=
397 bucket_cells((AVAILABLE_HOURS-1)*g_num_of_buckets+v_loop) -
398 bucket_cells((REQUIRED_HOURS-1)*g_num_of_buckets+v_loop);
399
400 -- ----------------------------
401 -- Cumulatitive Available Hours
402 -- ----------------------------
403 v_cum_net_available := v_cum_net_available +
404 bucket_cells((NET_AVAILABLE-1)*g_num_of_buckets+v_loop);
405 bucket_cells((CUM_AVAILABLE-1)*g_num_of_buckets+v_loop) := v_cum_net_available;
406
407 -- ----------------------------
408 -- Capacity Utilization
409 -- ----------------------------
410 g_error_stmt := 'Debug - calculate_cum - 40 - loop'||to_char(v_loop);
411 IF (bucket_cells((AVAILABLE_HOURS-1)*g_num_of_buckets+v_loop) <= 0) THEN
412 bucket_cells((UTILIZATION-1)*g_num_of_buckets+v_loop) := NULL;
413 ELSE
414 bucket_cells((UTILIZATION-1)*g_num_of_buckets+v_loop) := 100 *
415 bucket_cells((REQUIRED_HOURS-1)*g_num_of_buckets+v_loop) /
416 bucket_cells((AVAILABLE_HOURS-1)*g_num_of_buckets+v_loop);
417 END IF;
418
419 -- ----------------------------
420 -- Cum Capacity Utilization
421 -- ----------------------------
422 g_error_stmt := 'Debug - calculate_cum - 50 - loop'||to_char(v_loop);
423 v_cum_required := v_cum_required +
424 bucket_cells((REQUIRED_HOURS-1)*g_num_of_buckets+v_loop);
425 v_cum_available := v_cum_available +
426 bucket_cells((AVAILABLE_HOURS-1)*g_num_of_buckets+v_loop);
427
428 IF (v_cum_available <= 0) THEN
429 bucket_cells((CUM_UTILIZATION-1)*g_num_of_buckets+v_loop) := NULL;
430 ELSE
431 bucket_cells((CUM_UTILIZATION-1)*g_num_of_buckets+v_loop) := 100 *
432 v_cum_required / v_cum_available;
433 END IF;
434
435 END LOOP;
436
437 END calculate_cum;
438
439
440
441
442 -- =============================================================================
443 -- Name: flush_crp_plan
444 -- Desc: It inserts the date for 1 dept/res or line into CRP_CAPACITY_PLANS
445 -- =============================================================================
446 PROCEDURE flush_crp_plan(
447 p_item_id NUMBER,
448 p_sup_id NUMBER,
449 p_sup_site_id NUMBER) IS
450 v_loop BINARY_INTEGER := 1;
451 v_supplier_name VARCHAR2(100);
452 v_item_name VARCHAR2(250);
453
454 TYPE bkt_data_rec IS RECORD(
455 qty1 column_number,
456 qty2 column_number,
457 qty3 column_number,
458 qty4 column_number,
459 qty5 column_number,
460 qty6 column_number,
461 qty7 column_number,
462 qty8 column_number,
463 qty9 column_number);
464
465 bkt_data bkt_data_rec;
466
467 BEGIN
468
469 g_error_stmt := 'Debug - flush_crp_plan - 5';
470 SELECT partner_name
471 INTO v_supplier_name
472 FROM msc_trading_partners
473 WHERE partner_id = p_sup_id;
474
475 SELECT item_name
476 INTO v_item_name
477 FROM msc_items
478 WHERE inventory_item_id = p_item_id;
479
480
481 g_error_stmt := 'Debug - flush_crp_plan - 10';
482
483 FOR bkt IN 1..g_num_of_buckets LOOP
484 bkt_data.qty1(bkt) :=
485 bucket_cells(bkt+g_num_of_buckets*0);
486 bkt_data.qty2(bkt) :=
487 bucket_cells(bkt+g_num_of_buckets*1);
488 bkt_data.qty3(bkt) :=
489 bucket_cells(bkt+g_num_of_buckets*2);
490 bkt_data.qty4(bkt) :=
491 bucket_cells(bkt+g_num_of_buckets*3);
492 bkt_data.qty5(bkt) :=
493 bucket_cells(bkt+g_num_of_buckets*4);
494 bkt_data.qty6(bkt) :=
495 bucket_cells(bkt+g_num_of_buckets*5);
496 bkt_data.qty7(bkt) :=
497 bucket_cells(bkt+g_num_of_buckets*6);
498 bkt_data.qty8(bkt) :=
499 bucket_cells(bkt+g_num_of_buckets*7);
500 bkt_data.qty9(bkt) :=
501 bucket_cells(bkt+g_num_of_buckets*8);
502 END LOOP;
503
504 FORALL bkt in 1..nvl(bkt_data.qty1.last,0)
505 INSERT INTO msc_supplier_plans(
506 query_id,
507 last_update_date,
508 last_updated_by,
509 creation_date,
510 created_by,
511 last_update_login,
512 organization_id,
513 sr_instance_id,
514 supplier_id,
515 supplier_site_id,
516 inventory_item_id,
517 supplier_name,
518 item_name,
519 bucket_type,
520 bucket_date,
521 quantity1, quantity2, quantity3, quantity4,
522 quantity5, quantity6, quantity7, quantity8,
523 quantity9)
524 VALUES (
525 g_query_id,
526 SYSDATE,
527 -1,
528 SYSDATE,
529 -1,
530 -1,
531 NULL,
535 p_item_id,
532 NULL,
533 p_sup_id,
534 p_sup_site_id,
536 v_supplier_name,
537 v_item_name,
538 g_bucket_type,
539 g_dates(bkt),
540 bkt_data.qty1(bkt),
541 bkt_data.qty2(bkt),
542 bkt_data.qty3(bkt),
543 bkt_data.qty4(bkt),
544 bkt_data.qty5(bkt),
545 bkt_data.qty6(bkt),
546 bkt_data.qty7(bkt),
547 bkt_data.qty8(bkt),
548 bkt_data.qty9(bkt));
549
550 END flush_crp_plan;
551
552
553 -- =============================================================================
554 -- Name: re_initialize
555 -- Desc: This is called everytime we work on a new dept/resource
556 -- Initializes cost information as well as calendar code
557 -- =============================================================================
558 PROCEDURE re_initialize IS
559 v_overhead NUMBER;
560 BEGIN
561
562 g_bucket_count := 2;
563
564 g_error_stmt := 'Debug - re_initialize - 10';
565 -- ----------------------------------
566 -- Initialize the bucket cells to 0.
567 -- dbms_output.put_line(g_error_stmt);
568 -- ----------------------------------
569 FOR v_cnt IN 1..NUM_OF_TYPES*g_num_of_buckets LOOP
570 bucket_cells(v_cnt) := 0;
571 END LOOP;
572
573 g_error_stmt := 'Debug - re_initialize - 20';
574
575 END re_initialize;
576
577
578
579
580 -- =============================================================================
581 -- Name:populate_horizontal_plan
582 -- This is the main procedure. It retrieves data from database and calls
583 -- private procedures to summarize them into user defined buckets.
584 -- The argument p_current_data tells us whether to use current data
585 -- or snapshoted data for bucketing.
586 -- The p_bucket_type tells us what kind of buckets to use for summarization.
587 -- p_bucket_type 1 - ??daily buckets??
588 -- 2 - ??weekly buckets??
589 -- 3 - ??periodic buckets??
590 -- p_current_data 1 - Use current data
591 -- 2 - Use snapshot data
592 --
593 -- =============================================================================
594 FUNCTION populate_horizontal_plan(
595 p_item_list_id IN NUMBER,
596 p_org_id IN NUMBER,
597 p_inst_id IN NUMBER,
598 p_plan_id IN NUMBER,
599 p_bucket_type IN NUMBER,
600 p_cutoff_date IN DATE,
601 p_current_data IN NUMBER DEFAULT 2) RETURN NUMBER IS
602 v_no_rows BOOLEAN;
603 v_last_sup_id NUMBER := -2;
604 v_last_sup_site_id NUMBER := -2;
605 v_last_item_id NUMBER := -2;
606 v_cnt NUMBER;
607
608
609 CURSOR get_plan_type(p_plan_id NUMBER) IS
610 select plan_type,
611 decode(enforce_sup_cap_constraints,1,1,0),
612 decode(daily_material_constraints,1, 1, 0) --ascp_supplier_constraints
613 from msc_plans
614 where plan_id = p_plan_id;
615
616 l_constraints number:=0;
617 l_enforce_sup_cap_constraints number:=0;
618 l_val varchar2(2000);
619 BEGIN
620 /*
621 dbms_output.put_line(p_org_id||','||
622 p_inst_id ||','||
623 p_plan_id ||','||
624 p_bucket_type ||','||
625 p_cutoff_date ||','||
626 p_current_data);
627 */
628 -- ----------------------------
629 -- Initialize Global variables
630 -- ----------------------------
631 g_error_stmt := 'Debug - populate_horizontal_plan - 10';
632 g_item_list_id := p_item_list_id;
633 g_org_id := p_org_id;
634 g_inst_id := p_inst_id;
635 g_designator :=p_plan_id;
636 g_bucket_type := p_bucket_type;
637 g_cutoff_date := p_cutoff_date;
638
639 initialize;
640 --dbms_output.put_line(g_error_stmt);
641
642 --bug5449978
643 OPEN get_plan_type(p_plan_id);
644 FETCH get_plan_type INTO g_plan_type, l_enforce_sup_cap_constraints, l_constraints;
645 CLOSE get_plan_type;
646
647 --bug5449978--if plan is drp or unconstrained ascp or any plan where enforce_supplier capacity constraints is off, fallback to msc_supplies
648 --BUG5609299--modified previous fix so that we use msc_supplies for unconstrained DRP or unconstrained ASCP plans.
649 if (g_plan_type=5) or (g_plan_type=1 and l_constraints=0) then
650 g_use_sup_req:=0; -- do not use msc_supplier_requirements
651 else
652 g_use_sup_req:=1; -- use msc_supplier_requirements
653 end if;
654
655 g_error_stmt := 'Debug - populate_horizontal_plan - 20';
656 -- dbms_output.put_line(g_error_stmt);
657
658 OPEN supplier_snapshot_activity;
659
660 -- ----------------------------
661 -- Fetch rows from cursor
662 -- and process them one by one
663 -- ----------------------------
664 LOOP
665 v_no_rows := FALSE;
666 g_error_stmt := 'Debug - populate_horizontal_plan - 30';
667 -- dbms_output.put_line(g_error_stmt);
668
669 FETCH supplier_snapshot_activity INTO activity_rec;
670 IF (supplier_snapshot_activity%NOTFOUND) THEN
671 v_no_rows := TRUE;
672 END IF;
676 IF ((v_no_rows OR
673 --dbms_output.put_line(activity_rec.inventory_item_id||','||activity_rec.supplier_id||','||activity_rec.type||','||activity_rec.start_date||','||activity_rec.quantity);
674 g_error_stmt := 'Debug - populate_horizontal_plan - 40';
675 -- dbms_output.put_line(g_error_stmt);
677 v_last_item_id <> activity_rec.inventory_item_id OR
678 v_last_sup_id <> activity_rec.supplier_id OR
679 v_last_sup_site_id <> activity_rec.supplier_site_id) AND
680 v_last_sup_id <> -2) THEN
681 -- ==================================================
682 -- snapshoting for the last dept/res has finished
683 -- We therefore calculate cumulative information,
684 -- flush the previous set of data and then
685 -- re-initialized for the current dept/res
686 -- ==================================================
687 g_error_stmt := 'Debug - populate_horizontal_plan - 50';
688 calculate_cum;
689
690 g_error_stmt := 'Debug - populate_horizontal_plan - 60';
691 flush_crp_plan(v_last_item_id,v_last_sup_id, v_last_sup_site_id);
692
693 g_error_stmt := 'Debug - populate_horizontal_plan - 70';
694 re_initialize;
695 END IF;
696
697 EXIT WHEN v_no_rows;
698
699 g_error_stmt := 'Debug - populate_horizontal_plan - 85';
700 -- ---------------------------------------------------------
701 -- Add the retrieved activity to the plan
702 -- dbms_output.put_line(g_error_stmt);
703 -- ---------------------------------------------------------
704 add_to_plan;
705
706 v_last_item_id := activity_rec.inventory_item_id;
707 v_last_sup_id := activity_rec.supplier_id;
708 v_last_sup_site_id := activity_rec.supplier_site_id;
709 END LOOP;
710
711 g_error_stmt := 'Debug - populate_horizontal_plan - 90';
712 -- dbms_output.put_line(g_error_stmt);
713 CLOSE supplier_snapshot_activity;
714
715 return g_query_id;
716
717 EXCEPTION WHEN others THEN
718 -- dbms_output.put_line(g_error_stmt);
719 IF (supplier_snapshot_activity%ISOPEN) THEN
720 close supplier_snapshot_activity;
721 END IF;
722 raise;
723 END populate_horizontal_plan;
724
725 PROCEDURE query_list(p_query_id IN NUMBER,
726 p_plan_id IN NUMBER,
727 p_item_list IN VARCHAR2,
728 p_org_list IN VARCHAR2,
729 p_supplier_list IN VARCHAR2,
730 p_supplier_site_list IN VARCHAR2) IS
731
732 sql_stmt VARCHAR2(4000);
733 sql_stmt1 VARCHAR2(4000);
734 orig_stmt VARCHAR2(4000);
735
736 l_supplier_site VARCHAR2(240);
737 NODE_ITEM_SUPPLIER CONSTANT NUMBER := 1;
738 a number :=0;
739 startPos number;
740 endPos number;
741 p_item_id column_number;
742 p_supplier_id column_number;
743 l_base_item NUMBER;
744 v_item_id NUMBER;
745 l_len NUMBER;
746
747 cursor base_item is
748 select decode(bom_item_type, 4, NVL(base_item_id, inventory_item_id),
749 inventory_item_id )
750 from msc_system_items
751 where plan_id = p_plan_id
752 and inventory_item_id = v_item_id;
753
754 cursor config_list (p_base_item NUMBER,
755 l_supplier_id NUMBER)is
756 select distinct i.inventory_item_id
757 from msc_system_items i,
758 msc_item_suppliers s
759 where i.base_item_id = p_base_item
760 and i.plan_id = p_plan_id
761 and i.bom_item_type = 4
762 and i.inventory_item_id = s.inventory_item_id
763 and i.plan_id = s.plan_id
764 and i.sr_instance_id = s.sr_instance_id
765 and i.organization_id = s.organization_id
766 and s.supplier_id = l_supplier_id;
767
768 cursor base_Model_exists( p_base_item NUMBER) is
769 select 1
770 from msc_form_query
771 where number8 = p_base_item
772 and query_id = p_query_id;
773
774 model_exists NUMBER:= 2;
775 l_item_list VARCHAR2(5000);
776 l_config VARCHAR2(80);
777
778 debug_item NUMBER;
779 debug_count NUMBER;
780
781 l_order_date_type VARCHAR2(10) := '';
782
783 BEGIN
784
785 IF p_supplier_site_list IS NULL THEN
786 l_supplier_site := '-1';
787 ELSE
788 l_supplier_site := p_supplier_site_list;
789 END IF;
790
791 -- Need to go one by one through all items.
792 a :=1;
793 startPos :=1;
794 endPos := instr(p_item_list||',', ',',1,a);
795 while endPos >0 loop
796 l_len := endPos - startPos;
797 p_item_id(a) := to_number(substr(p_item_list||',',startPos, l_len));
798 a := a+1;
799 startPos := endPos+1;
800 endPos := instr(p_item_list||',', ',',1,a);
801 end loop;
802
803
804 a :=1;
805 startPos :=1;
806 endPos := instr(p_supplier_list||',' , ',' ,1,a);
807 while endPos >0 loop
808 l_len := endPos - startPos;
809 p_supplier_id(a) :=
810 to_number(substr(p_supplier_list||',',startPos, l_len));
811
812 a := a+1;
813 startPos := endPos+1;
817
814 endPos := instr(p_supplier_list||',', ',',1,a);
815 end loop;
816
818 sql_stmt1 := 'INSERT INTO msc_form_query ( '||
819 'query_id, '||
820 'last_update_date, '||
821 'last_updated_by, '||
822 'creation_date, '||
823 'created_by, '||
824 'last_update_login, '||
825 'number1, '|| -- store for standards - inv_item_id, for ato -model_id
826 'number2, '||
827 'number5, '||
828 'number7, '||
829 'number8, '|| -- store inv_item_id
830 'char1, '||
831 'char2) '||
832 ' SELECT distinct
833 '|| p_query_id || ', '||
834 'sysdate, '||
835 '1, '||
836 'sysdate, '||
837 '1, '||
838 '1, ';
839
840 FOR a in 1..p_item_id.count LOOP
841 v_item_id := p_item_id(a);
842 OPEN base_item;
843 FETCH base_item INTO l_base_item;
844 CLOSE base_item;
845
846 IF a > 1 THEN
847
848 -- when I go through the loop for the second + times
849 -- I need to make sure that we do not insert records
850 -- for configs of the same models
851 -- Let us say on the tree we have
852 -- Supplier A
853 -- Model 1
854 -- Config M11
855 -- Config M12
856 -- Model 2
857 -- Config M21
858 -- Config M22
859 -- when a = 2 (Config M11) , I do not need to insert any records
860 -- since I have already inserted the whole list of all configs
861 -- for Model 1 at a = 1
862
863 open base_Model_exists(l_base_item);
864 fetch base_Model_exists INTO model_exists;
865 close base_Model_exists;
866
867 end if;
868
869 if model_exists = 1 THEN
870 null; -- do not insert anything in msc_form_query
871 else
872
873 -- This cursor will get me the list of all config/preconfigs which
874 -- have l_base_item as a base_item_id
875
876 l_item_list := '';
877 FOR a in 1..p_supplier_id.count LOOP
878 OPEN config_list(l_base_item, p_supplier_id(a));
879 LOOP
880 FETCH config_list INTO l_config;
881 EXIT WHEN config_list%NOTFOUND;
882 l_item_list := l_config|| ', '||l_item_list;
883 END LOOP;
884 CLOSE config_list;
885 END LOOP;
886
887 IF l_item_list is not NULL THEN
888 l_item_list := l_item_list || l_base_item;
889 ELSE
890 l_item_list := to_char(l_base_item);
891 END IF;
892
893
894 sql_stmt := sql_stmt1 ||
895 'inventory_item_id, '||
896 'supplier_id, '||
897 'supplier_site_id, '||
898 NODE_ITEM_SUPPLIER ||' , ' ||
899 l_base_item|| -- base_ato in case of config
900 -- ',msc_get_name.supplier(supplier_id) || msc_supplier_horizontal_plan.get_order_type_label('||l_supplier_site || '), '||
901 ',msc_get_name.supplier(supplier_id) || msc_supplier_horizontal_plan.get_order_type_label(supplier_site_id ), '||
902 'msc_get_name.item_name(' ||l_base_item|| ' ,null,null,null) '||
903 'FROM msc_item_suppliers '||
904 'WHERE inventory_item_id in ('||l_item_list||') ' ||
905 'AND supplier_id in ('||p_supplier_list||') ' ||
906 -- 'AND NVL(supplier_site_id,-1) in ('||l_supplier_site||') ' ||
907 'and using_organization_id = -1 AND plan_id = '||p_plan_id;
908
909 orig_stmt := sql_stmt;
910
911 IF (p_org_list IS NOT NULL ) THEN
912 sql_stmt := sql_stmt ||
913 ' AND (sr_instance_id,organization_id) in ('||p_org_list||') ' ;
914 END IF;
915
916 IF l_supplier_site <> '-2' then
917 sql_stmt := sql_stmt ||
918 'AND NVL(supplier_site_id,-1) in ('||l_supplier_site||') ' ;
919 END IF;
920 /*
921 dbms_output.put_line(substr(sql_stmt,1,240));
922 dbms_output.put_line(substr(sql_stmt,241,240));
923 dbms_output.put_line(substr(sql_stmt,481,240));
924 dbms_output.put_line(substr(sql_stmt,721,240));
925 */
926 EXECUTE IMMEDIATE sql_stmt;
927
928 if SQL%ROWCOUNT =0 then
929 EXECUTE IMMEDIATE orig_stmt;
930 end if;
931
932 model_exists := 2;
933 end if; -- if model_exists
934
935 END loop;
936
937
938 END query_list;
939
940 FUNCTION get_order_type_label (p_supplier_site_id NUMBER)
941 RETURN VARCHAR2 is
942
943 l_supplier_site_label VARCHAR2(240);
944 l_shipping_control mfg_lookups.meaning%Type;
945
946 CURSOR SUPPLIER_SITE_LABEL_C(p_supplier_site_id NUMBER) IS
947 select meaning,tp_site_code
948 from msc_trading_partner_sites,mfg_lookups
949 where partner_site_id=p_supplier_site_id
950 and lookup_type = 'MSC_ORDER_DATE_TYPE'
951 and lookup_code = decode(shipping_control,'BUYER',1,2);
952
953 BEGIN
954 OPEN SUPPLIER_SITE_LABEL_C(p_supplier_site_id);
955 FETCH SUPPLIER_SITE_LABEL_C into
956 l_shipping_control,l_supplier_site_label;
957 CLOSE SUPPLIER_SITE_LABEL_C;
958 FND_MESSAGE.set_name('MSC','MSC_Order_date_type');
959 return '('||FND_MESSAGE.get||': '||l_shipping_control||')';
960 END;
961
962 END msc_supplier_horizontal_plan;