[Home] [Help]
PACKAGE BODY: APPS.MSC_DRP_UTIL
Source
1 PACKAGE BODY MSC_DRP_UTIL AS
2 /* $Header: MSCDRPUB.pls 120.21.12010000.2 2008/08/27 20:21:38 eychen ship $ */
3 TYPE supply_undo_type IS RECORD (
4 transaction_id number,
5 sr_instance_id number,
6 orig_shipment_id number,
7 orig_firm_flag number,
8 orig_firm_qty number,
9 orig_firm_date date,
10 orig_ship_date date,
11 orig_dock_date date,
12 orig_lead_time number,
13 orig_ship_method varchar2(30),
14 shipment_id number,
15 firm_flag number,
16 firm_qty number,
17 firm_date date,
18 ship_date date,
19 dock_date date,
20 lead_time number,
21 ship_method varchar2(30));
22
23 TYPE supply_undo_rec IS TABLE OF supply_undo_type INDEX BY BINARY_INTEGER;
24 g_supply_undo_rec supply_undo_rec;
25
26
27 FUNCTION order_type_text(arg_lookup_type IN varchar2,
28 arg_lookup_code IN NUMBER,
29 arg_org_id IN NUMBER,
30 arg_source_org IN NUMBER,
31 arg_demand_source_type IN NUMBER default null) return varchar2 IS
32 v_new_lookup_type varchar2(80);
33 v_new_lookup_code NUMBER;
34 BEGIN
35 if arg_lookup_code is null then
36 return null;
37 end if;
38
39 v_new_lookup_type := arg_lookup_type;
40 v_new_lookup_code := arg_lookup_code;
41
42 if v_new_lookup_type = 'MRP_ORDER_TYPE' then
43 if v_new_lookup_code = 2 then -- purchase req
44 if arg_source_org <> arg_org_id then
45 v_new_lookup_code := 53; -- internal req
46 end if;
47 end if;
48 elsif v_new_lookup_type = 'MSC_DEMAND_ORIGINATION' then
49 if v_new_lookup_code = 1 then -- planned order demand
50 if arg_source_org <> arg_org_id then
51 v_new_lookup_code := 49; -- Request Shipments
52 else
53 v_new_lookup_code := 48; -- Unconstrained Kit Demand
54 end if;
55 elsif v_new_lookup_code = 30 then -- Sales Order
56 if arg_demand_source_type = 8 then
57 v_new_lookup_code := 54; -- Internal Sales Orders
58 end if;
59 end if;
60 end if;
61
62 return msc_get_name.lookup_meaning(v_new_lookup_type, v_new_lookup_code);
63
64 END order_type_text;
65
66 FUNCTION cost_under_util(p_plan_id number,
67 p_weight_cap number, p_volume_cap number,
68 p_weight number, p_volume number,
69 p_from_org_id number, p_from_inst_id number,
70 p_to_org_id number, p_to_inst_id number,
71 p_ship_method varchar2) RETURN number IS
72 CURSOR cost_c is
73 select COST_PER_WEIGHT_UNIT, COST_PER_VOLUME_UNIT,
74 SHIPMENT_WEIGHT_UOM, SHIPMENT_VOLUME_UOM,
75 WEIGHT_UOM, VOLUME_UOM
76 from msc_interorg_ship_methods
77 where from_organization_id = p_from_org_id
78 and sr_instance_id = p_from_inst_id
79 and to_organization_id = p_to_org_id
80 and sr_instance_id2 = p_to_inst_id
81 and ship_method = p_ship_method
82 and plan_id = p_plan_id;
83
84 v_weight_cost number;
85 v_volume_cost number;
86 v_shp_wt_uom varchar2(3);
87 v_shp_vl_uom varchar2(3);
88 v_wt_uom varchar2(3);
89 v_vl_uom varchar2(3);
90 BEGIN
91 IF (p_weight_cap > 0 and p_weight_cap <= p_weight) or
92 (p_volume_cap > 0 and p_volume_cap <= p_volume) then
93 -- over_utilize, return null value
94 return null;
95 END IF;
96 IF p_weight_cap > p_weight or
97 p_volume_cap > p_volume then
98
99 OPEN cost_c;
100 FETCH cost_c INTO v_weight_cost,v_volume_cost,
101 v_shp_wt_uom, v_shp_vl_uom,
102 v_wt_uom, v_vl_uom;
103 CLOSE cost_c;
104
105 --dbms_output.put_line(v_weight_cost||','||v_volume_cost||','||v_shp_wt_uom||','||v_wt_uom);
106 -- only show cost when ship_wt_uom = wt_uom
107 IF nvl(v_shp_wt_uom,v_wt_uom) = nvl(v_wt_uom,v_shp_wt_uom) and
108 p_weight_cap > p_weight and
109 v_weight_cost is not null then
110 return (p_weight_cap - p_weight) * v_weight_cost;
111 ELSIF nvl(v_shp_vl_uom,v_vl_uom) = nvl(v_vl_uom,v_shp_vl_uom) and
112 p_volume_cap > p_volume and
113 v_volume_cost is not null then
114 return (p_volume_cap - p_volume) * v_volume_cost;
115 ELSE
116 return null;
117 END IF;
118 END IF;
119
120 return null;
121
122 END cost_under_util;
123
124 FUNCTION material_avail_date(p_plan_id number, p_supply_id number)
125 RETURN date IS
126 CURSOR date_c IS
127 select ms.new_schedule_date
128 from msc_supplies ms,
129 msc_single_lvl_peg mslp
130 where mslp.plan_id = p_plan_id
131 and mslp.pegging_type = 1 -- supply to parent supply
132 and mslp.parent_id = p_supply_id
133 and mslp.child_id = ms.transaction_id
134 and mslp.plan_id = ms.plan_id;
135 v_date date;
136 BEGIN
137 IF p_plan_id is null or p_supply_id is null then
138 return null;
139 END IF;
140
141 OPEN date_c;
142 FETCH date_c INTO v_date;
143 CLOSE date_c;
144
145 return v_date;
146 END material_avail_date;
147
148 PROCEDURE offset_date(p_anchor_date in varchar2,
149 p_plan_id in number,
150 p_from_org in number, p_to_org in number,
151 p_inst_id in number,
152 p_ship_method in varchar2,
153 p_lead_time in out nocopy number,
154 p_ship_calendar in out nocopy varchar2,
155 p_deliver_calendar in out nocopy varchar2,
156 p_receive_calendar in out nocopy varchar2,
157 p_ship_date in out nocopy date,
158 p_dock_date in out nocopy date) is
159 p_associate_type number;
160 cursor lead_time_c is
161 select intransit_time
162 from msc_interorg_ship_methods
163 where from_organization_id = p_from_org
164 and to_organization_id = p_to_org
165 and sr_instance_id = p_inst_id
166 and ship_method = p_ship_method
167 and plan_id = p_plan_id;
168 p_work_date date;
169 BEGIN
170
171 if p_lead_time is null then
172 OPEN lead_time_c;
173 FETCH lead_time_c INTO p_lead_time;
174 CLOSE lead_time_c;
175 end if;
176
177 if p_ship_method is null then
178 if p_anchor_date = 'SHIP_DATE' then
179 p_dock_date := p_ship_date + nvl(p_lead_time,0);
180 else
181 p_ship_date := p_dock_date - nvl(p_lead_time,0);
182 end if;
183
184 return;
185 end if;
186
187 if p_deliver_calendar is null then
188
189 p_deliver_calendar :=
190 msc_calendar.get_calendar_code(
191 p_inst_id,
192 null,
193 null,
194 null,
195 null,
196 4,
197 null,
198 p_ship_method,
199 7, --MSC_CALENDAR.VIC,
200 p_associate_type);
201 end if;
202
203 if p_receive_calendar is null then
204 p_receive_calendar :=
205 msc_calendar.get_calendar_code(
206 p_inst_id,
207 null,
208 null,
209 null,
210 null,
211 3,
212 p_to_org,
213 p_ship_method,
214 3, --MSC_CALENDAR.ORC,
215 p_associate_type);
216 end if;
217
218 if p_ship_calendar is null then
219 p_ship_calendar :=
220 msc_calendar.get_calendar_code(
221 p_inst_id,
222 null,
223 null,
224 null,
225 null,
226 3,
227 p_from_org,
228 p_ship_method,
229 5, --MSC_CALENDAR.OSC,
230 p_associate_type);
231 end if;
232 -- dbms_output.put_line(p_lead_time||','||p_deliver_calendar||','||p_receive_calendar||','||p_ship_calendar);
233 if p_anchor_date = 'SHIP_DATE' then
234 -- dbms_output.put_line('old ship date='||to_char(p_ship_date, 'MM/DD/RR HH24:MI'));
235 p_ship_date :=
236 msc_drp_util.get_work_day( 'NEXT', p_ship_calendar,
237 p_inst_id, p_ship_date);
238 -- dbms_output.put_line('new ship date='||to_char(p_ship_date, 'MM/DD/RR HH24:MI'));
239 p_dock_date :=
240 msc_rel_wf.get_offset_date(p_deliver_calendar,
241 p_inst_id,
242 p_lead_time, p_ship_date);
243 -- dbms_output.put_line('dock date='||to_char(p_dock_date, 'MM/DD/RR HH24:MI'));
244 p_dock_date :=
245 msc_drp_util.get_work_day('NEXT', p_receive_calendar,
246 p_inst_id, p_dock_date);
247 -- dbms_output.put_line('dock date2='||to_char(p_dock_date, 'MM/DD/RR HH24:MI'));
248 else
249 p_dock_date :=
250 msc_drp_util.get_work_day('PREV',p_receive_calendar,
251 p_inst_id, p_dock_date);
252 p_ship_date :=
253 msc_rel_wf.get_offset_date(p_deliver_calendar,
254 p_inst_id,
255 -1*p_lead_time, p_dock_date);
256 p_ship_date :=
257 msc_drp_util.get_work_day('PREV', p_ship_calendar,
258 p_inst_id, p_ship_date);
259 end if;
260
261 END offset_date;
262
263 PROCEDURE offset_dates(p_anchor_date in varchar2,
264 p_plan_id in number,
265 p_from_org in number, p_to_org in number,
266 p_inst_id in number,
267 p_item_id in number,
268 p_ship_method in varchar2,
269 p_lead_time in number,
270 p_ship_calendar in varchar2,
271 p_deliver_calendar in varchar2,
272 p_receive_calendar in varchar2,
273 p_ship_date in out nocopy date,
274 p_dock_date in out nocopy date,
275 p_due_date in out nocopy date) IS
276 CURSOR pp_lt_c IS
277 SELECT nvl(postprocessing_lead_time, 0)
278 FROM msc_system_items
279 WHERE plan_id = p_plan_id
280 AND sr_instance_id = p_inst_id
281 AND ORGANIZATION_ID = p_to_org
282 AND INVENTORY_ITEM_ID = p_item_id;
283
284 v_pp_lead_time number;
285 v_lead_time number := p_lead_time;
286 v_ship_method varchar2(30) := p_ship_method;
287 v_deliver_calendar varchar2(20) := p_deliver_calendar;
288 v_receive_calendar varchar2(20) := p_receive_calendar;
289 v_ship_calendar varchar2(20) := p_ship_calendar;
290 v_anchor_date varchar2(30);
291 p_association_type number;
292
293 BEGIN
294 OPEN pp_lt_c;
295 FETCH pp_lt_c INTO v_pp_lead_time;
296 CLOSE pp_lt_c;
297
298 if v_receive_calendar is null then
299 v_receive_calendar :=
300 msc_calendar.get_calendar_code(
301 p_inst_id,
302 null,
303 null,
304 null,
305 null,
306 3,
307 p_to_org,
308 p_ship_method,
309 3, --MSC_CALENDAR.ORC,
310 p_association_type);
311 end if;
312
313 IF p_anchor_date = 'DUE_DATE' then
314 p_dock_date :=
315 msc_rel_wf.get_offset_date(v_receive_calendar,
316 p_inst_id,
317 v_pp_lead_time*-1, p_due_date);
318 v_anchor_date := 'DOCK_DATE';
319 ELSE
320 v_anchor_date := p_anchor_date;
321 END IF;
322
323 offset_date(v_anchor_date,
324 p_plan_id,
325 p_from_org, p_to_org,
326 p_inst_id,
327 v_ship_method,
328 v_lead_time,
329 v_ship_calendar,
330 v_deliver_calendar,
331 v_receive_calendar,
332 p_ship_date,
333 p_dock_date);
334
335 IF p_anchor_date <> 'DUE_DATE' then
336 p_due_date :=
337 msc_rel_wf.get_offset_date(v_receive_calendar,
338 p_inst_id,
339 v_pp_lead_time, p_dock_date);
340 END IF;
341
342 END offset_dates;
343
344 PROCEDURE IR_dates( p_plan_id in number,
345 p_inst_id in number,
346 p_transaction_id in number,
347 p_ship_date out nocopy date,
348 p_dock_date out nocopy date,
349 p_due_date out nocopy date) IS
350 CURSOR ir_c IS
351 select new_ship_date, new_dock_date, new_schedule_date
352 from msc_supplies
353 where plan_id = p_plan_id
354 and sr_instance_id = p_inst_id
355 and transaction_id = p_transaction_id;
356 BEGIN
357 OPEN ir_c;
358 FETCH ir_c INTO p_ship_date, p_dock_date, p_due_date;
359 CLOSE ir_c;
360
361 END IR_dates;
362
363 FUNCTION wt_convert_ratio(p_item_id number, p_org_id number, p_inst_id number,
364 p_uom_code varchar2) return number is
365 cursor wt_c is
366 select CONVERSION_RATE
367 from MSC_WT_UOM_CONVERSIONS_VIEW
368 where inventory_item_id = p_item_id
369 and organization_id =p_org_id
370 and sr_instance_id = p_inst_id
371 and uom_code = p_uom_code;
372 v_temp number;
373 BEGIN
374 open wt_c;
375 fetch wt_c into v_temp;
376 close wt_c;
377
378 return 1/nvl(v_temp,1);
379 END wt_convert_ratio;
380
381 FUNCTION vl_convert_ratio(p_item_id number, p_org_id number, p_inst_id number,
382 p_uom_code varchar2) return number is
383 cursor wt_c is
384 select CONVERSION_RATE
385 from MSC_VL_UOM_CONVERSIONS_VIEW
386 where inventory_item_id = p_item_id
387 and organization_id =p_org_id
388 and sr_instance_id = p_inst_id
389 and uom_code = p_uom_code;
390 v_temp number;
391 BEGIN
392 open wt_c;
393 fetch wt_c into v_temp;
394 close wt_c;
395
396
397 return 1/nvl(v_temp,1);
398 END vl_convert_ratio;
399
400 FUNCTION sourcing_rule_name(p_plan_id number, p_item_id number,
401 p_from_org_id number, p_from_org_inst_id number,
402 p_to_org_id number, p_to_org_inst_id number,
403 p_rank number) return varchar2 IS
404 cursor name_c is
405 select msr.sourcing_rule_name
406 from msc_item_sourcing mis,
407 msc_sourcing_rules msr
408 where mis.plan_id = p_plan_id
409 and mis.inventory_item_id = p_item_id
410 and mis.source_organization_id = p_from_org_id
411 and mis.sr_instance_id = p_from_org_inst_id
412 and mis.organization_id = p_to_org_id
413 and mis.sr_instance_id2 = p_to_org_inst_id
414 and nvl(mis.rank,-1) = nvl(p_rank,nvl(mis.rank,-1))
415 and mis.circular_src = 1
416 and msr.sourcing_rule_id = mis.sourcing_rule_id;
417
418 v_name varchar2(80);
419 BEGIN
420 OPEN name_c;
421 FETCH name_c INTO v_name;
422 CLOSE name_c;
423
424 return v_name;
425 END sourcing_rule_name;
426
427 FUNCTION get_pref_key(p_plan_type number,
428 p_lookup_type varchar2, p_lookup_code number,
429 p_pref_tab varchar2) RETURN varchar2 IS
430
431 v_plan_type number;
432 cursor def_pref_c is
433 select preference_key
434 from msc_user_preference_keys
435 where plan_type = v_plan_type
436 and number1 = p_lookup_code
437 and PREF_TAB = p_pref_tab
438 and prompt = p_lookup_type;
439 v_out varchar2(100);
440 BEGIN
441
442 v_plan_type := p_plan_type;
443
444 if p_lookup_type in ('MSC_SUPPLIER_PLAN_TYPE','MSC_RESOURCE_HP',
445 'TRANSPORTATION_PLAN') or
446 p_plan_type in (2,3) then
447 v_plan_type :=1;
448 end if;
449 open def_pref_c;
450 fetch def_pref_c into v_out;
451 close def_pref_c;
452
453 return v_out;
454 END get_pref_key;
455
456 FUNCTION alloc_rule_name(p_rule_id number) return varchar2 IS
457 CURSOR rule_c IS
458 select name
459 from msc_drp_alloc_rules
460 where rule_id = p_rule_id;
461 p_name varchar2(30);
462 BEGIN
463 IF p_rule_id is null then
464 return null;
465 END IF;
466
467 OPEN rule_c;
468 FETCH rule_c INTO p_name;
469 CLOSE rule_c;
470
471 return p_name;
472 END alloc_rule_name;
473
474 FUNCTION get_cal_violation(p_violated_calendars varchar2 ) return varchar2 IS
475 l_out varchar2(3000);
476 p_padded_vl_cal varchar2(10);
477 no_of_calendars number :=7;
478 BEGIN
479 p_padded_vl_cal := lpad(p_violated_calendars,no_of_calendars,'0');
480 FOR a in 1..no_of_calendars loop
481 IF substr(p_padded_vl_cal,a,1) <> '0' then
482 if l_out is null then
483 l_out :=
484 msc_get_name.lookup_meaning('MSC_CALENDAR',a);
485 else
486 l_out := l_out ||','||
487 msc_get_name.lookup_meaning('MSC_CALENDAR',a);
488 end if;
489 END IF;
490 END LOOP;
491 return l_out;
492 END get_cal_violation;
493
494 PROCEDURE update_supply_row(p_plan_id number,
495 p_transaction_id number,
496 p_shipment_id number,
497 p_firm_flag number,
498 p_ship_date date,
499 p_dock_date date,
500 p_ship_method varchar2,
501 p_lead_time number) IS
502
503 cursor sup_c is
504 select msi.postprocessing_lead_time pp_lead_time,
505 ms.firm_planned_type firm_flag,
506 ms.firm_date,
507 ms.new_ship_date ship_date,
508 ms.new_dock_date dock_date,
509 ms.ship_method,
510 ms.intransit_lead_time lead_time,
511 ms.shipment_id,
512 decode( ms.firm_planned_type, 1,
513 nvl(ms.firm_quantity,ms.new_order_quantity),
514 null) firm_qty,
515 nvl(ms.firm_quantity,ms.new_order_quantity) new_firm_qty,
516 ms.sr_instance_id
517 from msc_supplies ms,
518 msc_system_items msi
519 where ms.plan_id = p_plan_id
520 and ms.transaction_id = p_transaction_id
521 and msi.inventory_item_id = ms.INVENTORY_ITEM_ID
522 and msi.organization_id = ms.organization_id
523 and msi.sr_instance_id = ms.sr_instance_id
524 and msi.plan_id = ms.plan_id;
525 p_firm_date date;
526 p_firm_qty number;
527 sup_rec sup_c%ROWTYPE;
528 p_rec number := -1;
529
530 BEGIN
531
532 OPEN sup_c;
533 FETCH sup_c INTO sup_rec;
534 CLOSE sup_c;
535 p_firm_date := p_dock_date + sup_rec.pp_lead_time;
536
537 -- record undo first
538
539 for a in 1..nvl(g_supply_undo_rec.last,0) loop
540 if g_supply_undo_rec(a).transaction_id = p_transaction_id then
541 p_rec := a;
542 exit;
543 end if;
544 end loop;
545
546 if p_rec =-1 then
547 -- first time update this transaction_id, start to record old values
548 p_rec := nvl(g_supply_undo_rec.last,0) +1;
549 g_supply_undo_rec(p_rec).transaction_id := p_transaction_id;
550 g_supply_undo_rec(p_rec).sr_instance_id := sup_rec.sr_instance_id;
551 g_supply_undo_rec(p_rec).orig_shipment_id := sup_rec.shipment_id;
552 g_supply_undo_rec(p_rec).orig_firm_flag := sup_rec.firm_flag;
553 g_supply_undo_rec(p_rec).orig_firm_qty := sup_rec.firm_qty;
554 g_supply_undo_rec(p_rec).orig_firm_date := sup_rec.firm_date;
555 g_supply_undo_rec(p_rec).orig_ship_date := sup_rec.ship_date;
556 g_supply_undo_rec(p_rec).orig_dock_date := sup_rec.dock_date;
557 g_supply_undo_rec(p_rec).orig_ship_method := sup_rec.ship_method;
558 g_supply_undo_rec(p_rec).orig_lead_time := sup_rec.lead_time;
559 end if;
560
561 if p_firm_flag = 1 then
562 p_firm_qty := sup_rec.new_firm_qty;
563 else
564 p_firm_qty := null;
565 p_firm_date :=null;
566 end if;
567
568 g_supply_undo_rec(p_rec).shipment_id := p_shipment_id;
569 g_supply_undo_rec(p_rec).firm_flag := p_firm_flag;
570 g_supply_undo_rec(p_rec).firm_qty := p_firm_qty;
571 g_supply_undo_rec(p_rec).firm_date := p_firm_date;
572
573 g_supply_undo_rec(p_rec).ship_date := nvl(p_ship_date,sup_rec.ship_date);
574 g_supply_undo_rec(p_rec).dock_date := nvl(p_dock_date,sup_rec.dock_date);
575 g_supply_undo_rec(p_rec).ship_method :=
576 nvl(p_ship_method,sup_rec.ship_method);
577 g_supply_undo_rec(p_rec).lead_time := nvl(p_lead_time,sup_rec.lead_time);
578
579
580 -- update table
581 update msc_supplies
582 set firm_planned_type = p_firm_flag,
583 firm_quantity = p_firm_qty,
584 firm_date = p_firm_date,
585 new_ship_date = nvl(p_ship_date,new_ship_date),
586 new_dock_date = nvl(p_dock_date,new_dock_date),
587 ship_method = nvl(p_ship_method,ship_method),
588 intransit_lead_time = nvl(p_lead_time,intransit_lead_time),
589 status = 0,
590 applied = 2,
591 shipment_id = p_shipment_id
592 where plan_id = p_plan_id
593 and transaction_id = p_transaction_id;
594
595 END update_supply_row;
596
597 PROCEDURE mark_supply_undo(p_plan_id number) IS
598 supply_Columns msc_undo.changeRGType;
599 i number := 1;
600 x_return_sts VARCHAR2(20);
601 x_msg_count NUMBER;
602 x_msg_data VARCHAR2(2000);
603 BEGIN
604 for a in 1..nvl(g_supply_undo_rec.LAST,0) loop
605 if notEqual(g_supply_undo_rec(a).firm_flag,
606 g_supply_undo_rec(a).orig_firm_flag) then
607 supply_columns(i).column_changed := 'FIRM_PLANNED_TYPE';
608 supply_columns(i).column_changed_text := get_msg('MSC','FIRM');
609 supply_columns(i).old_value :=
610 g_supply_undo_rec(a).orig_firm_flag;
611 supply_columns(i).column_type := 'NUMBER';
612 supply_columns(i).new_value :=
613 g_supply_undo_rec(a).firm_flag;
614 i := i+1 ;
615 END IF;
616 if notEqual(g_supply_undo_rec(a).firm_date,
617 g_supply_undo_rec(a).orig_firm_date) then
618 supply_columns(i).column_changed := 'FIRM_DATE';
619 supply_columns(i).column_changed_text := get_msg('MSC','FIRM_DATE');
620 supply_columns(i).old_value :=
621 fnd_date.date_to_canonical(g_supply_undo_rec(a).orig_firm_date);
622 supply_columns(i).column_type := 'DATE';
623 supply_columns(i).new_value :=
624 fnd_date.date_to_canonical(g_supply_undo_rec(a).firm_date);
625 i := i+1 ;
626 END IF;
627 if notEqual(g_supply_undo_rec(a).firm_qty,
628 g_supply_undo_rec(a).orig_firm_qty) then
629 supply_columns(i).column_changed := 'FIRM_QUANTITY';
630 supply_columns(i).column_changed_text := get_msg('MSC','FIRM_QTY');
631 supply_columns(i).old_value :=
632 fnd_number.number_to_canonical(g_supply_undo_rec(a).orig_firm_qty);
633 supply_columns(i).column_type := 'NUMBER';
634 supply_columns(i).new_value :=
635 fnd_number.number_to_canonical(g_supply_undo_rec(a).firm_qty);
636 i := i+1 ;
637 END IF;
638 if notEqual(g_supply_undo_rec(a).shipment_id,
639 g_supply_undo_rec(a).orig_shipment_id) then
640 supply_columns(i).column_changed := 'SHIPMENT_ID';
641 supply_columns(i).column_changed_text := get_msg('MSC','SHIPMENT_ID');
642 supply_columns(i).old_value :=
643 to_char(g_supply_undo_rec(a).orig_shipment_id);
644 supply_columns(i).column_type := 'NUMBER';
645 supply_columns(i).new_value :=
646 to_char(g_supply_undo_rec(a).shipment_id);
647 i := i+1 ;
648 end if;
649 if notEqual(g_supply_undo_rec(a).lead_time,
650 g_supply_undo_rec(a).orig_lead_time) then
651 supply_columns(i).column_changed := 'INTRANSIT_LEAD_TIME';
652 supply_columns(i).column_changed_text := get_msg('MSC','LEAD_TIME');
653 supply_columns(i).old_value :=
654 fnd_number.number_to_canonical(g_supply_undo_rec(a).orig_lead_time);
655 supply_columns(i).column_type := 'NUMBER';
656 supply_columns(i).new_value :=
657 fnd_number.number_to_canonical(g_supply_undo_rec(a).lead_time);
658 i := i+1 ;
659 END IF;
660 if notEqual(g_supply_undo_rec(a).ship_method,
661 g_supply_undo_rec(a).orig_ship_method) then
662 supply_columns(i).column_changed := 'SHIP_METHOD';
663 supply_columns(i).column_changed_text := get_msg('MSC','MSC_EC_SHIP_METHOD');
664 supply_columns(i).old_value := g_supply_undo_rec(a).orig_ship_method;
665 supply_columns(i).column_type := 'VARCHAR2';
666 supply_columns(i).new_value := g_supply_undo_rec(a).ship_method;
667 i := i+1 ;
668 END IF;
669 if notEqual(g_supply_undo_rec(a).ship_date,
670 g_supply_undo_rec(a).orig_ship_date) then
671 supply_columns(i).column_changed := 'NEW_SHIP_DATE';
672 supply_columns(i).column_changed_text := get_msg('MSC','MSC_SHIP_DATE');
673 supply_columns(i).old_value := fnd_date.date_to_canonical(g_supply_undo_rec(a).orig_ship_date);
674 supply_columns(i).column_type := 'DATE';
675 supply_columns(i).new_value := fnd_date.date_to_canonical(g_supply_undo_rec(a).ship_date);
676 i := i+1 ;
677 END IF;
678 if notEqual(g_supply_undo_rec(a).dock_date,
679 g_supply_undo_rec(a).orig_dock_date) then
680 supply_columns(i).column_changed := 'NEW_DOCK_DATE';
681 supply_columns(i).column_changed_text := get_msg('MSC','MSC_DOCK_DATE');
682 supply_columns(i).old_value := fnd_date.date_to_canonical(g_supply_undo_rec(a).orig_dock_date);
683 supply_columns(i).column_type := 'DATE';
684 supply_columns(i).new_value := fnd_date.date_to_canonical(g_supply_undo_rec(a).dock_date);
685 i := i+1 ;
686 END IF;
687 msc_undo.store_undo(1, --table_changed
688 2, --insert or update
689 g_supply_undo_rec(a).transaction_id,
690 p_plan_id,
691 g_supply_undo_rec(a).sr_instance_id,
692 NULL,
693 supply_Columns,
694 x_return_sts,
695 x_msg_count,
696 x_msg_data,
697 NULL);
698 i := 1;
699 supply_Columns.delete;
700 end loop;
701
702 g_supply_undo_rec.delete;
703 END mark_supply_undo;
704
705
706 FUNCTION notEqual(p_value number, p_value2 number) return boolean IS
707 BEGIN
708 if p_value <> p_value2 or
709 (p_value is null and p_value2 is not null) or
710 (p_value is not null and p_value2 is null) then
711 return true;
712 else
713 return false;
714 end if;
715
716 END notEqual;
717
718 FUNCTION notEqual(p_value varchar2, p_value2 varchar2) return boolean IS
719 BEGIN
720 if p_value <> p_value2 or
721 (p_value is null and p_value2 is not null) or
722 (p_value is not null and p_value2 is null) then
723 return true;
724 else
725 return false;
726 end if;
727
728 END notEqual;
729
730 FUNCTION notEqual(p_value date, p_value2 date) return boolean IS
731 BEGIN
732 if p_value <> p_value2 or
733 (p_value is null and p_value2 is not null) or
734 (p_value is not null and p_value2 is null) then
735 return true;
736 else
737 return false;
738 end if;
739
740 END notEqual;
741
742 Function get_msg(p_product varchar2, p_name varchar2) RETURN varchar2 IS
743 Begin
744 FND_MESSAGE.set_name(p_product, p_name);
745 return FND_MESSAGE.get;
746 End get_msg;
747
748 FUNCTION get_iso_trip(p_plan_id number, p_instance_id number,
749 p_disposition_id number) return number IS
750 CURSOR trip_c IS
751 select shipment_id
752 from msc_supplies
753 where plan_id = p_plan_id
754 and sr_instance_id = p_instance_id
755 and transaction_id = p_disposition_id;
756 v_trip number;
757
758 BEGIN
759 OPEN trip_c;
760 FETCH trip_c INTO v_trip;
761 CLOSE trip_c;
762
763 return v_trip;
764 END get_iso_trip;
765
766 FUNCTION forecast_name(p_plan_id number,p_instance_id number,p_org_id number,
767 p_schedule_designator_id number,p_forecast_set_id number)
768 RETURN varchar2 IS
769 v_name varchar2(300);
770 v_name2 varchar2(300);
771 BEGIN
772 v_name := msc_get_name.scenario_designator(p_forecast_set_id,p_plan_id,p_org_id,p_instance_id);
773 v_name2 := msc_get_name.designator(p_schedule_designator_id,p_forecast_set_id);
774 if v_name is not null and v_name2 is not null then
775 return v_name ||'/'||v_name2;
776 else
777 return v_name || v_name2;
778 end if;
779 END forecast_name;
780
781 FUNCTION get_iso_name(p_plan_id number, p_instance_id number,
782 p_transaction_id number) return varchar2 IS
783 CURSOR iso_c IS
784 select order_number
785 from msc_demands
786 where plan_id = p_plan_id
787 and sr_instance_id = p_instance_id
788 and disposition_id = p_transaction_id;
789 v_order_number varchar2(100);
790
791 BEGIN
792 OPEN iso_c;
793 FETCH iso_c INTO v_order_number;
794 CLOSE iso_c;
795
796 return v_order_number;
797 END get_iso_name;
798
799 FUNCTION get_work_day( p_next_or_prev IN varchar2,
800 p_calendar_code IN varchar2,
801 p_instance_id IN number,
802 p_calendar_date IN date) return date IS
803 p_out_date date;
804 p_valid_hour date;
805 v_from_time number;
806 v_to_time number;
807 v_time number;
808
809 CURSOR time_c IS
810 select mst.from_time,mst.to_time
811 from msc_shift_times mst,
812 msc_calendar_shifts mcs
813 where mcs.calendar_code = p_calendar_code
814 and mcs.sr_instance_id = p_instance_id
815 and mst.calendar_code = mcs.calendar_code
816 and mst.sr_instance_id = mcs.sr_instance_id
817 and mst.shift_num = mcs.shift_num;
818
819 p_end_of_prev_day boolean := false;
820
821 BEGIN
822 --dbms_output.put_line('original day='||to_char(p_calendar_date,'MM-DD-RR HH24:MI')||' move type is '||p_next_or_prev);
823
824 if p_calendar_date is null then
825 return p_calendar_date;
826 end if;
827
828 if p_next_or_prev = 'NEXT' then
829 p_out_date := msc_calendar.next_work_day(p_calendar_code,
830 p_instance_id,
831 p_calendar_date);
832 else
833 p_out_date := msc_calendar.prev_work_day(p_calendar_code,
834 p_instance_id,
835 p_calendar_date);
836 end if;
837 --dbms_output.put_line('p_out_date after move day='||to_char(p_out_date,'MM-DD-RR HH24:MI')||',move type is '||p_next_or_prev);
838 if trunc(p_out_date) = trunc(p_calendar_date) then
839 -- need to preserve the timestamp
840 p_out_date := to_date(to_char(p_out_date, 'MM/DD/RR')||' '||
841 to_char(p_calendar_date,'HH24:MI'),
842 'MM/DD/RR HH24:MI');
843 elsif p_next_or_prev = 'PREV' then
844 -- need to set to end time of the last shift of previous day
845 -- set to 23:59 for now,
846 -- it will be changed again, if shift is defined
847 p_end_of_prev_day := true;
848 p_out_date := to_date(to_char(p_out_date, 'MM/DD/RR')||' 23:59',
849 'MM/DD/RR HH24:MI');
850 end if;
851 --dbms_output.put_line('p_out_date before shift time='||to_char(p_out_date,'MM-DD-RR HH24:MI'));
852
853 if p_calendar_code is null then
854 return p_out_date;
855 end if;
856
857 -- 5498765, need to check shift hours also
858
859 OPEN time_c;
860 LOOP
861 FETCH time_c INTO v_from_time, v_to_time;
862 EXIT WHEN time_c%NOTFOUND;
863 if v_from_time > v_to_time then
864 v_to_time := v_to_time + 60*60*24;
865 end if;
866 --dbms_output.put_line('shift range is '||to_char(trunc(p_out_date)+v_from_time/(60*60*24),'MM-DD-RR HH24:MI')||','||to_char(trunc(p_out_date)+v_to_time/(60*60*24),'MM-DD-RR HH24:MI'));
867 if not(p_end_of_prev_day) and
868 p_out_date >=
869 trunc(p_out_date) + v_from_time/(60*60*24) and
870 p_out_date <=
871 trunc(p_out_date) + v_to_time/(60*60*24) then
872 -- valid working hours
873 p_valid_hour := p_out_date;
874 exit;
875 else -- find the next working hour
876 if p_next_or_prev = 'NEXT' then
877 if p_out_date <
878 trunc(p_out_date) + v_from_time/(60*60*24) then
879 if p_valid_hour is null or
880 p_valid_hour >
881 trunc(p_out_date) + v_from_time/(60*60*24) then
882 p_valid_hour :=
883 trunc(p_out_date) + v_from_time/(60*60*24);
884 end if;
885 end if;
886 if v_time is null or
887 v_time > v_from_time then
888 -- find the earliest shift time
889 v_time := v_from_time;
890 end if;
891 else -- if p_next_or_prev = 'PREV' then
892 if p_out_date >
893 trunc(p_out_date) + v_to_time/(60*60*24) then
894 if p_valid_hour is null or
895 p_valid_hour <
896 trunc(p_out_date) + v_to_time/(60*60*24) then
897 p_valid_hour :=
898 trunc(p_out_date) + v_to_time/(60*60*24);
899 end if;
900 end if;
901 if v_time is null or
902 v_time < v_to_time then
903 -- find the latiest shift time
904 v_time := v_to_time;
905 end if;
906 end if; -- if p_next_or_prev = 'NEXT' then
907 end if;
908 END LOOP;
909 CLOSE time_c;
910 --dbms_output.put_line('v_time is '||to_char(trunc(p_out_date)+v_time/(60*60*24),'MM-DD-RR HH24:MI')||','||to_char(p_valid_hour,'MM-DD-RR HH24:MI'));
911 if p_end_of_prev_day then
912 p_out_date := trunc(p_out_date)+v_time/(60*60*24);
913 elsif p_valid_hour is not null then
914 p_out_date := p_valid_hour;
915 else -- have not find the valid hour yet
916 if p_next_or_prev = 'NEXT' then
917 -- move to the earliest shift time of the next working day
918 p_out_date := msc_calendar.date_offset(p_calendar_code,
919 p_instance_id,
920 p_out_date,1,null);
921 p_out_date := trunc(p_out_date) + v_time/(60*60*24);
922 else -- if p_next_or_prev = 'PREV'
923 -- move to the latest shift time of the prev working day
924 p_out_date := msc_calendar.date_offset(p_calendar_code,
925 p_instance_id,
926 p_out_date,-1,null);
927 p_out_date := trunc(p_out_date) + v_time/(60*60*24);
928
929 end if; -- -- if p_next_or_prev = 'PREV'
930 end if; -- if p_valid_hour is not null
931
932 -- dbms_output.put_line('p_out_date after shift time='||to_char(p_out_date,'MM-DD-RR HH24:MI'));
933 return p_out_date;
934 END get_work_day;
935
936 FUNCTION rel_exp_where_clause(p_exc_type number,
937 p_plan_id number, p_org_id number,
938 p_inst_id number, p_item_id number,
939 p_source_org_id number, p_source_inst_id number,
940 p_supplier_id number, p_supply_id number,
941 p_demand_id number,
942 p_due_date date, p_dmd_satisfied_date date,
943 p_start_date date, p_end_date date) RETURN varchar2 IS
944
945 p_where varchar2(32000);
946
947 v_id numberArr;
948 v_list varchar2(32000);
949 v_exc_list varchar2(32000);
950 p_related_excp_type number;
951 v_source_org_id number;
952 v_source_inst_id number;
953 p_comp_id number;
954 p_min_time number;
955 p_max_time number;
956 p_lt_window number :=
957 nvl(FND_PROFILE.value('MSC_DRP_REL_EXP_OFFSET_DAYS'),0);
958
959 cursor pegged_supply is
960 select child_id
961 from msc_single_lvl_peg
962 where plan_id = p_plan_id
963 and pegging_type = 2 -- supply to parent demand
964 and parent_id = p_demand_id;
965
966 cursor source_org_c is
967 select source_organization_id,
968 sr_instance_id2,
969 min(avg_transit_lead_time),
970 max(avg_transit_lead_time)
971 from msc_item_sourcing mis
972 where mis.plan_id = p_plan_id
973 and mis.inventory_item_id = p_item_id
974 and mis.organization_id = p_org_id
975 and mis.sr_instance_id = p_inst_id
976 and mis.source_organization_id =
977 nvl(p_source_org_id, mis.source_organization_id)
978 and mis.sr_instance_id2 = nvl(p_source_inst_id,mis.sr_instance_id2)
979 group by source_organization_id, sr_instance_id2;
980
981
982 cursor lead_time_c is
983 select nvl(fixed_lead_time,0)
984 from msc_system_items
985 where plan_id = p_plan_id
986 and inventory_item_id = p_item_id
987 and organization_id = p_org_id
988 and sr_instance_id = p_inst_id;
989
990 cursor comp_c is
991 select inventory_item_id
992 from msc_components_sc_v
993 where plan_id = p_plan_id
994 and using_assembly_id = p_item_id
995 and organization_id = p_org_id
996 and sr_instance_id = p_inst_id;
997 BEGIN
998
999 if p_exc_type in (24,26,52,95,96,111) then
1000 OPEN pegged_supply;
1001 FETCH pegged_supply BULK COLLECT INTO v_id;
1002 CLOSE pegged_supply;
1003 end if;
1004
1005 v_list := construct_list(v_id);
1006 if v_list is not null then
1007 v_list := '('||v_list ||')';
1008 end if;
1009
1010 if p_exc_type in (24,26,95,96,111) then -- late repl for SO/Forecast
1011 -- find matl and alloc const for the same item/org
1012
1013 p_max_time := 0;
1014 p_min_time := 0;
1015 p_lt_window := 0;
1016
1017 p_related_excp_type := 37; -- matl const
1018 v_id := related_excp(v_id,p_related_excp_type, p_plan_id,
1019 p_org_id, p_inst_id, p_item_id,
1020 p_due_date,p_dmd_satisfied_date,
1021 p_max_time,p_min_time,p_lt_window);
1022
1023 IF p_exc_type in (24,26) then
1024 p_related_excp_type := 82; -- alloc const
1025 v_id := related_excp(v_id,p_related_excp_type, p_plan_id,
1026 p_org_id, p_inst_id, p_item_id,
1027 p_due_date,p_dmd_satisfied_date,
1028 p_max_time,p_min_time,p_lt_window);
1029 END IF;
1030
1031 v_exc_list := construct_list(v_id);
1032
1033 if v_exc_list is null then
1034 v_exc_list := '(-1)';
1035 else
1036 v_exc_list := '('||v_exc_list||')';
1037 end if;
1038
1039 p_where := p_where ||
1040 ' and ( exception_id in '||v_exc_list || ' or ';
1041
1042 if v_list is not null then
1043 -- find order lead time const and order firm late for the related sup
1044 p_where := p_where ||
1045 ' ( exception_type in (59, 62) '||
1046 ' and inventory_item_id = '||p_item_id ||
1047 ' and organization_id = '||p_org_id ||
1048 ' and sr_instance_id = '||p_inst_id ||
1049 ' and transaction_id in '||v_list ||
1050 ') or ';
1051 end if;
1052 -- find demand qty not satisfied for this demand
1053 p_where := p_where ||
1054 ' ( exception_type =67 '||
1055 ' and inventory_item_id = '||p_item_id ||
1056 ' and organization_id = '||p_org_id ||
1057 ' and sr_instance_id = '||p_inst_id ||
1058 ' and demand_id = '||p_demand_id ||'))';
1059
1060 elsif p_exc_type =52 then -- SO/Forecast at risk
1061 if v_list is null then
1062 v_list := '(-1)';
1063 end if;
1064 p_where := p_where ||
1065 ' and exception_type in (54,57) '||
1066 ' and inventory_item_id = '||p_item_id ||
1067 ' and organization_id = '||p_org_id ||
1068 ' and sr_instance_id = '||p_inst_id ||
1069 ' and transaction_id in '||v_list;
1070
1071 end if; -- end of if p_exc_type in (24,26,95,96,111)
1072
1073 if p_exc_type in (37,2,20,73) then
1074 -- matl const, shortage, below safety, below target
1075
1076 -- find item in the source org
1077
1078 OPEN source_org_c;
1079 LOOP
1080 FETCH source_org_c INTO v_source_org_id, v_source_inst_id,
1081 p_min_time, p_max_time;
1082 EXIT WHEN source_org_c%NOTFOUND;
1083
1084 p_related_excp_type := 37; -- matl const
1085 v_id := related_excp(
1086 v_id,p_related_excp_type, p_plan_id,
1087 v_source_org_id, v_source_inst_id, p_item_id,
1088 p_start_date,p_end_date,
1089 p_max_time,p_min_time,p_lt_window);
1090
1091 p_related_excp_type := 82; -- alloc const
1092 v_id := related_excp(
1093 v_id,p_related_excp_type, p_plan_id,
1094 v_source_org_id, v_source_inst_id, p_item_id,
1095 p_start_date,p_end_date,
1096 p_max_time,p_min_time,p_lt_window);
1097
1098 if p_exc_type in (2,20,73) then
1099 p_related_excp_type := 81; -- item cons to later date
1100 v_id := related_excp(
1101 v_id,p_related_excp_type, p_plan_id,
1102 v_source_org_id, v_source_inst_id, p_item_id,
1103 p_start_date,p_end_date,
1104 p_max_time,p_min_time,p_lt_window);
1105
1106 end if;
1107
1108 END LOOP;
1109 CLOSE source_org_c;
1110
1111 if p_exc_type = 37 then
1112 -- find allocation const, dmd qty not satisfied, order lt const
1113 -- for the same item in the same org
1114 p_max_time := 0;
1115 p_min_time := 0;
1116 p_lt_window := 0;
1117
1118 p_related_excp_type := 82; -- alloc const
1119 v_id := related_excp(
1120 v_id,p_related_excp_type, p_plan_id,
1121 p_org_id, p_inst_id, p_item_id,
1122 p_start_date,p_end_date,
1123 p_max_time,p_min_time,p_lt_window);
1124
1125 p_related_excp_type := 67; -- dmd qty not satisfied
1126 v_id := related_excp(
1127 v_id,p_related_excp_type, p_plan_id,
1128 p_org_id, p_inst_id, p_item_id,
1129 p_start_date,p_end_date,
1130 p_max_time,p_min_time,p_lt_window);
1131
1132 p_related_excp_type := 59; -- order lt const
1133 v_id := related_excp(
1134 v_id,p_related_excp_type, p_plan_id,
1135 p_org_id, p_inst_id, p_item_id,
1136 p_start_date,p_end_date,
1137 p_max_time,p_min_time,p_lt_window);
1138
1139 end if; -- if p_exc_type = 37 then
1140
1141 if p_exc_type in (37,2,20,73) then
1142 -- find matl/alloc const for component in the same org
1143
1144 OPEN lead_time_c;
1145 FETCH lead_time_c INTO p_max_time;
1146 CLOSE lead_time_c;
1147
1148 p_min_time := p_max_time;
1149 p_lt_window := 0;
1150
1151 OPEN comp_c;
1152 LOOP
1153 FETCH comp_c INTO p_comp_id;
1154 EXIT WHEN comp_c%NOTFOUND;
1155
1156 p_related_excp_type := 82; -- alloc const
1157 v_id := related_excp(
1158 v_id,p_related_excp_type, p_plan_id,
1159 p_org_id, p_inst_id, p_comp_id,
1160 p_start_date,p_end_date,
1161 p_max_time,p_min_time,p_lt_window);
1162
1163 p_related_excp_type := 37; -- matl const
1164 v_id := related_excp(
1165 v_id,p_related_excp_type, p_plan_id,
1166 p_org_id, p_inst_id, p_comp_id,
1167 p_start_date,p_end_date,
1168 p_max_time,p_min_time,p_lt_window);
1169
1170 END LOOP;
1171 CLOSE comp_c;
1172
1173 end if; -- if p_exc_type in (37,2,20,73)
1174
1175
1176 if v_exc_list is null then
1177 v_exc_list := '(-1)';
1178 else
1179 v_exc_list := '('||v_exc_list||')';
1180 end if;
1181
1182 p_where := p_where ||
1183 ' and exception_id in '||v_exc_list;
1184
1185 end if; -- if p_exc_type in (37,2,20,73)
1186
1187 return p_where;
1188 END rel_exp_where_clause;
1189
1190 FUNCTION construct_list(p_id numberArr) RETURN varchar2 IS
1191 p_list varchar2(3000);
1192 p_query_id number;
1193 BEGIN
1194 for a in 1..nvl(p_id.last,0) loop
1195 if p_list is null then
1196 p_list := p_id(a);
1197 else
1198 p_list := p_list ||','||p_id(a);
1199 end if;
1200 end loop;
1201
1202 if length(p_list) > 1500 then
1203 -- 5898008, where clause in folder block has length limit < 2000
1204
1205 select msc_form_query_s.nextval
1206 into p_query_id
1207 from dual;
1208
1209 forall a in 1..nvl(p_id.last,0)
1210 insert into msc_form_query
1211 (QUERY_ID,
1212 LAST_UPDATE_DATE,
1213 LAST_UPDATED_BY,
1214 CREATION_DATE,
1215 CREATED_BY,
1216 LAST_UPDATE_LOGIN,
1217 NUMBER1)
1218 values (
1219 p_query_id,
1220 sysdate,
1221 -1,
1222 sysdate,
1223 -1,
1224 -1,
1225 p_id(a));
1226
1227 p_list := ' select number1 '||
1228 ' from msc_form_query '||
1229 ' where query_id ='||p_query_id ;
1230 end if; -- if length(p_list) > 2000 then
1231
1232 return p_list;
1233
1234 END construct_list;
1235
1236 FUNCTION related_excp(p_id numberArr,p_related_excp_type number,
1237 p_plan_id number, p_org_id number,
1238 p_inst_id number, p_item_id number,
1239 p_start_date date, p_end_date date,
1240 p_max_time number, p_min_time number,
1241 p_lt_window number) RETURN numberArr IS
1242
1243 cursor exc_id_c is
1244 select med.exception_detail_id
1245 from msc_exception_details med
1246 where med.plan_id = p_plan_id
1247 and med.inventory_item_id = p_item_id
1248 and med.organization_id = p_org_id
1249 and med.sr_instance_id = p_inst_id
1250 and med.exception_type = p_related_excp_type
1251 and (med.date1
1252 between(p_start_date - p_max_time - p_lt_window) and
1253 (p_end_date - p_min_time +1) or
1254 nvl(med.date2,med.date1)
1255 between(p_start_date - p_max_time - p_lt_window) and
1256 (p_end_date - p_min_time +1) or
1257 (med.date1 < (p_start_date - p_max_time - p_lt_window) and
1258 nvl(med.date2,med.date1) > (p_end_date - p_min_time +1)));
1259
1260 v_id numberArr;
1261 i number;
1262 BEGIN
1263
1264 OPEN exc_id_c;
1265 FETCH exc_id_c BULK COLLECT INTO v_id;
1266 CLOSE exc_id_c;
1267
1268 if p_id is not null then
1269 -- merge two arrays
1270 i := nvl(v_id.last,0);
1271 for a in 1 .. nvl(p_id.last,0) loop
1272 v_id(i+a) := p_id(a);
1273 end loop;
1274 end if;
1275
1276 return v_id;
1277
1278 END related_excp;
1279
1280 PROCEDURE update_exp_version(p_rowid rowid,
1281 p_action_taken number) IS
1282 TYPE numtab is table of Number index by binary_integer;
1283 p_action numTab;
1284 p_excp_id numTab;
1285 p_plan_id number;
1286 p_excp_type number;
1287 p_org_id number;
1288 p_inst_id number;
1289 p_item_id number;
1290 p_supplier_id number;
1291 p_supplier_site_id number;
1292 p_source_org_id number;
1293 p_action_taken_date date;
1294
1295 BEGIN
1296
1297 -- lock msc_srp_item_exceptions first
1298
1299 Select msie.plan_id,
1300 msie.organization_id,
1301 msie.sr_instance_id,
1302 msie.inventory_item_id,
1303 msie.exception_type,
1304 msie.supplier_id,
1305 msie.supplier_site_id,
1306 msie.source_org_id
1307 INTO p_plan_id, p_org_id, p_inst_id, p_item_id, p_excp_type,
1308 p_supplier_id, p_supplier_site_id, p_source_org_id
1309 From msc_exception_details med,
1310 Msc_srp_item_exceptions msie
1311 Where med.plan_id = msie.plan_id
1312 And med.organization_id = msie.organization_id
1313 And med.sr_instance_id = msie.sr_instance_id
1314 And med.inventory_item_id = msie.inventory_item_id
1315 And med.exception_type = msie.exception_type
1316 And nvl(med.supplier_id,-23453) = msie.supplier_id
1317 And nvl(med.supplier_site_id,-23453) = msie.supplier_site_id
1318 And decode(med.exception_type, 43, med.number2,-23453) =
1319 msie.source_org_id
1320 and msie.exist = 1
1321 and med.rowid = p_rowid
1322 for update of msie.action_taken_date nowait;
1323
1324 -- lock msc_exception_details
1325
1326 Select med.exception_detail_id
1327 BULK COLLECT INTO p_excp_id
1328 From msc_exception_details med
1329 Where med.plan_id = p_plan_id
1330 And med.organization_id = p_org_id
1331 And med.sr_instance_id = p_inst_id
1332 And med.inventory_item_id = p_item_id
1333 And med.exception_type = p_excp_type
1334 And nvl(med.supplier_id,-23453) = p_supplier_id
1335 And nvl(med.supplier_site_id,-23453) = p_supplier_site_id
1336 And decode(med.exception_type, 43, med.number2,-23453) = p_source_org_id
1337 for update of med.action_taken_date, med.action_taken nowait;
1338
1339 if p_action_taken = 1 then
1340 p_action_taken_date := sysdate;
1341 end if;
1342
1343 -- update all the excp within the same criteria group
1344 Forall a in 1..nvl(p_excp_id.last,0)
1345 Update msc_exception_details
1346 Set action_taken_date = p_action_taken_date,
1347 action_taken = p_action_taken
1348 Where plan_id = p_plan_id
1349 AND exception_detail_id = p_excp_id(a);
1350
1351 Update msc_srp_item_exceptions
1352 Set action_taken_date = p_action_taken_date
1353 Where plan_id = p_plan_id
1354 And organization_id = p_org_id
1355 And sr_instance_id = p_inst_id
1356 And inventory_item_id = p_item_id
1357 And exception_type = p_excp_type
1358 And supplier_id = p_supplier_id
1359 And supplier_site_id = p_supplier_site_id
1360 And source_org_id = p_source_org_id
1361 and exist = 1;
1362 EXCEPTION
1363 WHEN app_exception.record_lock_exception THEN
1364 -- dbms_output.put_line('can not lock');
1365 null;
1366 when others then
1367 -- dbms_output.put_line('error is ' ||SQLERRM);
1368 null;
1369 END update_exp_version;
1370
1371 PROCEDURE retrieve_exp_version(p_plan_id number) IS
1372 TYPE dateTab is table of Date index by binary_integer;
1373 TYPE numtab is table of Number index by binary_integer;
1374 p_action numTab;
1375 p_excp_id numTab;
1376 p_action_date dateTab;
1377 p_gen_date dateTab;
1378 p_plan_date date;
1379
1380 CURSOR plan_c IS
1381 select plan_start_date
1382 from msc_plans
1383 where plan_id = p_plan_id;
1384
1385 BEGIN
1386
1387
1388 OPEN plan_c;
1389 FETCH plan_c INTO p_plan_date;
1390 CLOSE plan_c;
1391
1392 MSC_UTIL.MSC_DEBUG('retrieve exception versions ');
1393
1394 Select med.exception_detail_id,
1395 msie.action_taken_date,
1396 msie.last_generated_date
1397 BULK COLLECT INTO p_excp_id, p_action_date, p_gen_date
1398 From msc_exception_details med,
1399 Msc_srp_item_exceptions msie
1400 Where med.plan_id = msie.plan_id
1401 And med.organization_id = msie.organization_id
1402 And med.sr_instance_id = msie.sr_instance_id
1403 And med.inventory_item_id = msie.inventory_item_id
1404 And med.exception_type = msie.exception_type
1405 And nvl(med.supplier_id,-23453) = msie.supplier_id
1406 And nvl(med.supplier_site_id,-23453) = msie.supplier_site_id
1407 And decode(med.exception_type, 43, med.number2,-23453) =
1408 msie.source_org_id
1409 and msie.exist = 1
1410 and msie.plan_id = p_plan_id;
1411
1412 Forall a in 1..nvl(p_excp_id.last,0)
1413 Update msc_exception_details
1414 Set action_taken_date = p_action_date(a),
1415 first_generated_date = p_gen_date(a),
1416 Action_taken = decode(p_action_date(a), null, 2, 1),
1417 new_exception = decode(p_gen_date(a), p_plan_date, 1, 0)
1418 Where plan_id = p_plan_id
1419 And exception_detail_id = p_excp_id(a);
1420 commit;
1421
1422 EXCEPTION
1423 WHEN app_exception.record_lock_exception THEN
1424 null; --dbms_output.put_line('can not lock');
1425 WHEN others then
1426 null; --dbms_output.put_line('error is ' ||SQLERRM);
1427 END retrieve_exp_version;
1428
1429 END MSC_DRP_UTIL;