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