[Home] [Help]
PACKAGE BODY: APPS.MSC_SUPPLIER_HORIZONTAL_PLAN
Source
4 SYS_YES CONSTANT INTEGER := 1;
1 PACKAGE BODY msc_supplier_horizontal_plan AS
2 /* $Header: MSCHSPLB.pls 120.13 2011/12/15 23:53:44 cnazarma ship $ */
3
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;
52
49 g_designator NUMBER;
50 g_bucket_type NUMBER;
51 g_error_stmt VARCHAR2(50);
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),
163 DECODE(mr.order_type,
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),
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 -- =============================================================================
302 -- to have daily rather than total qty. We calculate # of workdays in each
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
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) +
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);
391
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
406 if (v_cum_net_available <= 0 ) then
407 v_cum_net_available := 0;
408 end if;
409
410 bucket_cells((CUM_AVAILABLE-1)*g_num_of_buckets+v_loop) := v_cum_net_available;
411
412 -- ----------------------------
413 -- Capacity Utilization
414 -- ----------------------------
415 g_error_stmt := 'Debug - calculate_cum - 40 - loop'||to_char(v_loop);
416 IF (bucket_cells((AVAILABLE_HOURS-1)*g_num_of_buckets+v_loop) <= 0) THEN
417 bucket_cells((UTILIZATION-1)*g_num_of_buckets+v_loop) := NULL;
418 ELSE
419 bucket_cells((UTILIZATION-1)*g_num_of_buckets+v_loop) := 100 *
420 bucket_cells((REQUIRED_HOURS-1)*g_num_of_buckets+v_loop) /
421 bucket_cells((AVAILABLE_HOURS-1)*g_num_of_buckets+v_loop);
422 END IF;
423
424 -- ----------------------------
425 -- Cum Capacity Utilization
426 -- ----------------------------
427 g_error_stmt := 'Debug - calculate_cum - 50 - loop'||to_char(v_loop);
428 v_cum_required := v_cum_required +
429 bucket_cells((REQUIRED_HOURS-1)*g_num_of_buckets+v_loop);
430 v_cum_available := v_cum_available +
431 bucket_cells((AVAILABLE_HOURS-1)*g_num_of_buckets+v_loop);
432
436 bucket_cells((CUM_UTILIZATION-1)*g_num_of_buckets+v_loop) := 100 *
433 IF (v_cum_available <= 0) THEN
434 bucket_cells((CUM_UTILIZATION-1)*g_num_of_buckets+v_loop) := NULL;
435 ELSE
437 v_cum_required / v_cum_available;
438 END IF;
439
440 END LOOP;
441
442 END calculate_cum;
443
444
445
446
447 -- =============================================================================
448 -- Name: flush_crp_plan
449 -- Desc: It inserts the date for 1 dept/res or line into CRP_CAPACITY_PLANS
450 -- =============================================================================
451 PROCEDURE flush_crp_plan(
452 p_item_id NUMBER,
453 p_sup_id NUMBER,
454 p_sup_site_id NUMBER) IS
455 v_loop BINARY_INTEGER := 1;
456 v_supplier_name VARCHAR2(300);
457 v_item_name VARCHAR2(250);
458
459 TYPE bkt_data_rec IS RECORD(
460 qty1 column_number,
461 qty2 column_number,
462 qty3 column_number,
463 qty4 column_number,
464 qty5 column_number,
465 qty6 column_number,
466 qty7 column_number,
467 qty8 column_number,
468 qty9 column_number);
469
470 bkt_data bkt_data_rec;
471
472 BEGIN
473
474 g_error_stmt := 'Debug - flush_crp_plan - 5';
475 SELECT partner_name
476 INTO v_supplier_name
477 FROM msc_trading_partners
478 WHERE partner_id = p_sup_id;
479
480 SELECT item_name
481 INTO v_item_name
482 FROM msc_items
483 WHERE inventory_item_id = p_item_id;
484
485
486 g_error_stmt := 'Debug - flush_crp_plan - 10';
487
488 FOR bkt IN 1..g_num_of_buckets LOOP
489 bkt_data.qty1(bkt) :=
490 bucket_cells(bkt+g_num_of_buckets*0);
491 bkt_data.qty2(bkt) :=
492 bucket_cells(bkt+g_num_of_buckets*1);
493 bkt_data.qty3(bkt) :=
494 bucket_cells(bkt+g_num_of_buckets*2);
495 bkt_data.qty4(bkt) :=
496 bucket_cells(bkt+g_num_of_buckets*3);
497 bkt_data.qty5(bkt) :=
498 bucket_cells(bkt+g_num_of_buckets*4);
499 bkt_data.qty6(bkt) :=
500 bucket_cells(bkt+g_num_of_buckets*5);
501 bkt_data.qty7(bkt) :=
502 bucket_cells(bkt+g_num_of_buckets*6);
503 bkt_data.qty8(bkt) :=
504 bucket_cells(bkt+g_num_of_buckets*7);
505 bkt_data.qty9(bkt) :=
506 bucket_cells(bkt+g_num_of_buckets*8);
507 END LOOP;
508
509 FORALL bkt in 1..nvl(bkt_data.qty1.last,0)
510 INSERT INTO msc_supplier_plans(
511 query_id,
512 last_update_date,
513 last_updated_by,
514 creation_date,
515 created_by,
516 last_update_login,
517 organization_id,
518 sr_instance_id,
519 supplier_id,
520 supplier_site_id,
521 inventory_item_id,
522 supplier_name,
523 item_name,
524 bucket_type,
525 bucket_date,
526 quantity1, quantity2, quantity3, quantity4,
527 quantity5, quantity6, quantity7, quantity8,
528 quantity9)
529 VALUES (
530 g_query_id,
531 SYSDATE,
532 -1,
533 SYSDATE,
534 -1,
535 -1,
536 NULL,
537 NULL,
538 p_sup_id,
539 p_sup_site_id,
540 p_item_id,
541 v_supplier_name,
542 v_item_name,
543 g_bucket_type,
544 g_dates(bkt),
545 bkt_data.qty1(bkt),
546 bkt_data.qty2(bkt),
547 bkt_data.qty3(bkt),
548 bkt_data.qty4(bkt),
549 bkt_data.qty5(bkt),
550 bkt_data.qty6(bkt),
551 bkt_data.qty7(bkt),
552 bkt_data.qty8(bkt),
553 bkt_data.qty9(bkt));
554
555 END flush_crp_plan;
556
557
558 -- =============================================================================
559 -- Name: re_initialize
560 -- Desc: This is called everytime we work on a new dept/resource
561 -- Initializes cost information as well as calendar code
562 -- =============================================================================
563 PROCEDURE re_initialize IS
564 v_overhead NUMBER;
565 BEGIN
566
567 g_bucket_count := 2;
568
569 g_error_stmt := 'Debug - re_initialize - 10';
570 -- ----------------------------------
571 -- Initialize the bucket cells to 0.
572 -- dbms_output.put_line(g_error_stmt);
573 -- ----------------------------------
574 FOR v_cnt IN 1..NUM_OF_TYPES*g_num_of_buckets LOOP
575 bucket_cells(v_cnt) := 0;
576 END LOOP;
577
578 g_error_stmt := 'Debug - re_initialize - 20';
579
580 END re_initialize;
581
582
583
584
585 -- =============================================================================
586 -- Name:populate_horizontal_plan
587 -- This is the main procedure. It retrieves data from database and calls
588 -- private procedures to summarize them into user defined buckets.
589 -- The argument p_current_data tells us whether to use current data
590 -- or snapshoted data for bucketing.
591 -- The p_bucket_type tells us what kind of buckets to use for summarization.
592 -- p_bucket_type 1 - ??daily buckets??
593 -- 2 - ??weekly buckets??
594 -- 3 - ??periodic buckets??
595 -- p_current_data 1 - Use current data
596 -- 2 - Use snapshot data
597 --
598 -- =============================================================================
599 FUNCTION populate_horizontal_plan(
600 p_item_list_id IN NUMBER,
604 p_bucket_type IN NUMBER,
601 p_org_id IN NUMBER,
602 p_inst_id IN NUMBER,
603 p_plan_id IN NUMBER,
605 p_cutoff_date IN DATE,
606 p_current_data IN NUMBER DEFAULT 2) RETURN NUMBER IS
607 v_no_rows BOOLEAN;
608 v_last_sup_id NUMBER := -2;
609 v_last_sup_site_id NUMBER := -2;
610 v_last_item_id NUMBER := -2;
611 v_cnt NUMBER;
612
613
614 CURSOR get_plan_type(p_plan_id NUMBER) IS
615 select plan_type,
616 decode(enforce_sup_cap_constraints,1,1,0),
617 decode(daily_material_constraints,1, 1, 0) --ascp_supplier_constraints
618 from msc_plans
619 where plan_id = p_plan_id;
620
621 l_constraints number:=0;
622 l_enforce_sup_cap_constraints number:=0;
623 l_val varchar2(2000);
624 BEGIN
625 /*
626 dbms_output.put_line(p_org_id||','||
627 p_inst_id ||','||
628 p_plan_id ||','||
629 p_bucket_type ||','||
630 p_cutoff_date ||','||
631 p_current_data);
632 */
633 -- ----------------------------
634 -- Initialize Global variables
635 -- ----------------------------
636 g_error_stmt := 'Debug - populate_horizontal_plan - 10';
637 g_item_list_id := p_item_list_id;
638 g_org_id := p_org_id;
639 g_inst_id := p_inst_id;
640 g_designator :=p_plan_id;
641 g_bucket_type := p_bucket_type;
642 g_cutoff_date := p_cutoff_date;
643
644 initialize;
645 --dbms_output.put_line(g_error_stmt);
646
647 --bug5449978
648 OPEN get_plan_type(p_plan_id);
649 FETCH get_plan_type INTO g_plan_type, l_enforce_sup_cap_constraints, l_constraints;
650 CLOSE get_plan_type;
651
652 --bug5449978--if plan is drp or unconstrained ascp or any plan where enforce_supplier capacity constraints is off, fallback to msc_supplies
653 --BUG5609299--modified previous fix so that we use msc_supplies for unconstrained DRP or unconstrained ASCP plans.
654
655 -- bug 6060647. For plan_type=4 (io plan) since
656 -- msc_supplier_requirements is not populated, we need
657 -- to get the supplier requirement from msc_supplies table
658
659 if (g_plan_type = 4) or (g_plan_type=5) or
660 (g_plan_type=1 and l_constraints=0) then
661 g_use_sup_req:=0; -- do not use msc_supplier_requirements
662 else
663 g_use_sup_req:=1; -- use msc_supplier_requirements
664 end if;
665
666 g_error_stmt := 'Debug - populate_horizontal_plan - 20';
667 -- dbms_output.put_line(g_error_stmt);
668
669 OPEN supplier_snapshot_activity;
670
671 -- ----------------------------
672 -- Fetch rows from cursor
673 -- and process them one by one
674 -- ----------------------------
675 LOOP
676 v_no_rows := FALSE;
677 g_error_stmt := 'Debug - populate_horizontal_plan - 30';
678 -- dbms_output.put_line(g_error_stmt);
679
680 FETCH supplier_snapshot_activity INTO activity_rec;
681 IF (supplier_snapshot_activity%NOTFOUND) THEN
682 v_no_rows := TRUE;
683 END IF;
684 --dbms_output.put_line(activity_rec.inventory_item_id||','||activity_rec.supplier_id||','||activity_rec.type||','||activity_rec.start_date||','||activity_rec.quantity);
685 g_error_stmt := 'Debug - populate_horizontal_plan - 40';
686 -- dbms_output.put_line(g_error_stmt);
687 IF ((v_no_rows OR
688 v_last_item_id <> activity_rec.inventory_item_id OR
689 v_last_sup_id <> activity_rec.supplier_id OR
690 v_last_sup_site_id <> activity_rec.supplier_site_id) AND
691 v_last_sup_id <> -2) THEN
692 -- ==================================================
693 -- snapshoting for the last dept/res has finished
694 -- We therefore calculate cumulative information,
695 -- flush the previous set of data and then
696 -- re-initialized for the current dept/res
697 -- ==================================================
698 g_error_stmt := 'Debug - populate_horizontal_plan - 50';
699 calculate_cum;
700
701 g_error_stmt := 'Debug - populate_horizontal_plan - 60';
702 flush_crp_plan(v_last_item_id,v_last_sup_id, v_last_sup_site_id);
703
704 g_error_stmt := 'Debug - populate_horizontal_plan - 70';
705 re_initialize;
706 END IF;
707
708 EXIT WHEN v_no_rows;
709
710 g_error_stmt := 'Debug - populate_horizontal_plan - 85';
711 -- ---------------------------------------------------------
712 -- Add the retrieved activity to the plan
713 -- dbms_output.put_line(g_error_stmt);
714 -- ---------------------------------------------------------
715 add_to_plan;
716
717 v_last_item_id := activity_rec.inventory_item_id;
718 v_last_sup_id := activity_rec.supplier_id;
719 v_last_sup_site_id := activity_rec.supplier_site_id;
720 END LOOP;
721
722 g_error_stmt := 'Debug - populate_horizontal_plan - 90';
723 -- dbms_output.put_line(g_error_stmt);
724 CLOSE supplier_snapshot_activity;
725
726 return g_query_id;
727
728 EXCEPTION WHEN others THEN
729 -- dbms_output.put_line(g_error_stmt);
730 IF (supplier_snapshot_activity%ISOPEN) THEN
731 close supplier_snapshot_activity;
732 END IF;
733 raise;
734 END populate_horizontal_plan;
735
736 PROCEDURE query_list(p_query_id IN NUMBER,
737 p_plan_id IN NUMBER,
738 p_item_list IN VARCHAR2,
739 p_org_list IN VARCHAR2,
740 p_supplier_list IN VARCHAR2,
741 p_supplier_site_list IN VARCHAR2) IS
742
743 sql_stmt VARCHAR2(4000);
744 sql_stmt1 VARCHAR2(4000);
748 NODE_ITEM_SUPPLIER CONSTANT NUMBER := 1;
745 orig_stmt VARCHAR2(4000);
746
747 l_supplier_site VARCHAR2(1000);
749 a number :=0;
750 startPos number;
751 endPos number;
752 p_item_id column_number;
753 p_supplier_id column_number;
754 l_base_item NUMBER;
755 v_item_id NUMBER;
756 l_len NUMBER;
757
758 cursor base_item is
759 select decode(bom_item_type, 4, NVL(base_item_id, inventory_item_id),
760 inventory_item_id )
761 from msc_system_items
762 where plan_id = p_plan_id
763 and inventory_item_id = v_item_id;
764
765 cursor config_list (p_base_item NUMBER,
766 l_supplier_id NUMBER)is
767 select distinct i.inventory_item_id
768 from msc_system_items i,
769 msc_item_suppliers s
770 where i.base_item_id = p_base_item
771 and i.plan_id = p_plan_id
772 and i.bom_item_type = 4
773 and i.inventory_item_id = s.inventory_item_id
774 and i.plan_id = s.plan_id
775 and i.sr_instance_id = s.sr_instance_id
776 and i.organization_id = s.organization_id
777 and s.supplier_id = l_supplier_id;
778
779 cursor base_Model_exists( p_base_item NUMBER) is
780 select 1
781 from msc_form_query
782 where number8 = p_base_item
783 and query_id = p_query_id;
784
785 model_exists NUMBER:= 2;
786 l_item_list VARCHAR2(5000);
787 l_config VARCHAR2(80);
788
789 debug_item NUMBER;
790 debug_count NUMBER;
791
792 l_order_date_type VARCHAR2(10) := '';
793
794 BEGIN
795
796 IF p_supplier_site_list IS NULL THEN
797 l_supplier_site := '-1';
798 ELSE
799 l_supplier_site := p_supplier_site_list;
800 END IF;
801
802 -- Need to go one by one through all items.
803 a :=1;
804 startPos :=1;
805 endPos := instr(p_item_list||',', ',',1,a);
806 while endPos >0 loop
807 l_len := endPos - startPos;
808 p_item_id(a) := to_number(substr(p_item_list||',',startPos, l_len));
809 a := a+1;
810 startPos := endPos+1;
811 endPos := instr(p_item_list||',', ',',1,a);
812 end loop;
813
814
815 a :=1;
816 startPos :=1;
817 endPos := instr(p_supplier_list||',' , ',' ,1,a);
818 while endPos >0 loop
819 l_len := endPos - startPos;
820 p_supplier_id(a) :=
821 to_number(substr(p_supplier_list||',',startPos, l_len));
822
823 a := a+1;
824 startPos := endPos+1;
825 endPos := instr(p_supplier_list||',', ',',1,a);
826 end loop;
827
828
829 sql_stmt1 := 'INSERT INTO msc_form_query ( '||
830 'query_id, '||
831 'last_update_date, '||
832 'last_updated_by, '||
833 'creation_date, '||
834 'created_by, '||
835 'last_update_login, '||
836 'number1, '|| -- store for standards - inv_item_id, for ato -model_id
837 'number2, '||
838 'number5, '||
839 'number7, '||
840 'number8, '|| -- store inv_item_id
841 'char1, '||
842 'char2) '||
843 ' SELECT distinct
844 '|| p_query_id || ', '||
845 'sysdate, '||
846 '1, '||
847 'sysdate, '||
848 '1, '||
849 '1, ';
850
851 FOR a in 1..p_item_id.count LOOP
852 v_item_id := p_item_id(a);
853 OPEN base_item;
854 FETCH base_item INTO l_base_item;
855 CLOSE base_item;
856
857 IF a > 1 THEN
858
859 -- when I go through the loop for the second + times
860 -- I need to make sure that we do not insert records
861 -- for configs of the same models
862 -- Let us say on the tree we have
863 -- Supplier A
864 -- Model 1
865 -- Config M11
866 -- Config M12
867 -- Model 2
868 -- Config M21
869 -- Config M22
870 -- when a = 2 (Config M11) , I do not need to insert any records
871 -- since I have already inserted the whole list of all configs
872 -- for Model 1 at a = 1
873
874 open base_Model_exists(l_base_item);
875 fetch base_Model_exists INTO model_exists;
876 close base_Model_exists;
877
878 end if;
879
880 if model_exists = 1 THEN
881 null; -- do not insert anything in msc_form_query
882 else
883
884 -- This cursor will get me the list of all config/preconfigs which
885 -- have l_base_item as a base_item_id
886
887 l_item_list := '';
888 FOR a in 1..p_supplier_id.count LOOP
889 OPEN config_list(l_base_item, p_supplier_id(a));
890 LOOP
891 FETCH config_list INTO l_config;
892 EXIT WHEN config_list%NOTFOUND;
893 l_item_list := l_config|| ', '||l_item_list;
894 END LOOP;
895 CLOSE config_list;
896 END LOOP;
897
898 IF l_item_list is not NULL THEN
899 l_item_list := l_item_list || l_base_item;
900 ELSE
901 l_item_list := to_char(l_base_item);
902 END IF;
903
904
905 sql_stmt := sql_stmt1 ||
906 'inventory_item_id, '||
907 'supplier_id, '||
908 'supplier_site_id, '||
909 NODE_ITEM_SUPPLIER ||' , ' ||
910 l_base_item|| -- base_ato in case of config
911 -- ',msc_get_name.supplier(supplier_id) || msc_supplier_horizontal_plan.get_order_type_label('||l_supplier_site || '), '||
912 ',msc_get_name.supplier(supplier_id) || msc_supplier_horizontal_plan.get_order_type_label(supplier_site_id ), '||
913 'msc_get_name.item_name(' ||l_base_item|| ' ,null,null,null) '||
914 'FROM msc_item_suppliers '||
915 'WHERE inventory_item_id in ('||l_item_list||') ' ||
916 'AND supplier_id in ('||p_supplier_list||') ' ||
917 -- 'AND NVL(supplier_site_id,-1) in ('||l_supplier_site||') ' ||
918 'and using_organization_id = -1 AND plan_id = '||p_plan_id;
919
920 orig_stmt := sql_stmt;
921
922 IF (p_org_list IS NOT NULL ) THEN
923 sql_stmt := sql_stmt ||
924 ' AND (sr_instance_id,organization_id) in ('||p_org_list||') ' ;
925 END IF;
926
927 IF l_supplier_site <> '-2' then
928 sql_stmt := sql_stmt ||
929 'AND NVL(supplier_site_id,-1) in ('||l_supplier_site||') ' ;
930 END IF;
931 /*
932 dbms_output.put_line(substr(sql_stmt,1,240));
933 dbms_output.put_line(substr(sql_stmt,241,240));
934 dbms_output.put_line(substr(sql_stmt,481,240));
935 dbms_output.put_line(substr(sql_stmt,721,240));
936 */
937 EXECUTE IMMEDIATE sql_stmt;
938
939 if SQL%ROWCOUNT =0 then
940 EXECUTE IMMEDIATE orig_stmt;
941 end if;
942
943 model_exists := 2;
944 end if; -- if model_exists
945
946 END loop;
947
948
949 END query_list;
950
951 FUNCTION get_order_type_label (p_supplier_site_id NUMBER)
952 RETURN VARCHAR2 is
953
954 l_supplier_site_label VARCHAR2(240);
955 l_shipping_control mfg_lookups.meaning%Type;
956
957 CURSOR SUPPLIER_SITE_LABEL_C(p_supplier_site_id NUMBER) IS
958 select meaning,tp_site_code
959 from msc_trading_partner_sites,mfg_lookups
960 where partner_site_id=p_supplier_site_id
961 and lookup_type = 'MSC_ORDER_DATE_TYPE'
962 and lookup_code = decode(shipping_control,'BUYER',1,2);
963
964 BEGIN
965 OPEN SUPPLIER_SITE_LABEL_C(p_supplier_site_id);
966 FETCH SUPPLIER_SITE_LABEL_C into
967 l_shipping_control,l_supplier_site_label;
968 CLOSE SUPPLIER_SITE_LABEL_C;
969 FND_MESSAGE.set_name('MSC','MSC_Order_date_type');
970 return '('||FND_MESSAGE.get||': '||l_shipping_control||')';
971 END;
972
973 END msc_supplier_horizontal_plan;