[Home] [Help]
PACKAGE BODY: APPS.MSC_UNDO
Source
1 PACKAGE BODY MSC_UNDO AS
2 /* $Header: MSCUNDOB.pls 120.1 2005/06/07 18:15:40 appldev $ */
3
4 procedure UNDO (undoId undoIdTblType,
5 x_return_status OUT NOCOPY VARCHAR2,
6 x_msg_count OUT NOCOPY NUMBER,
7 x_msg_data OUT NOCOPY VARCHAR2) IS
8
9 i NUMBER;
10
11 l_undo_id NUMBER;
12 s_undo_id NUMBER;
13 l_action NUMBER;
14 l_table_changed NUMBER;
15 l_transaction_id NUMBER;
16 l_plan_id NUMBER;
17 l_sr_instance_id NUMBER;
18 l_last_update_date DATE;
19
20 l_column_changed VARCHAR2(240);
21 l_new_Value VARCHAR2(240);
22 l_old_value VARCHAR2(240);
23 l_column_type VARCHAR2(20);
24
25 count_flag number;
26
27 cursor c_mst ( v_undo_id number) is
28 select plan_id,
29 transaction_id,
30 sr_instance_id,
31 table_changed,
32 action,
33 last_update_date
34 from msc_undo_summary
35 where undo_id = v_undo_id;
36
37 cursor c_dtl(v_plan_id number, v_undo_id number) is
38 select column_changed,
39 old_value,
40 new_value,
41 column_type
42 from msc_undo_details
43 where plan_id = v_plan_id
44 and undo_id = v_undo_id;
45 cursor c_supp (v_plan_id number, v_undo_id number) is
46 select undo_id,transaction_id, sr_instance_id
47 from msc_undo_summary
48 where plan_id = v_plan_id
49 and ( undo_id = v_undo_id
50 or parent_id = v_undo_id )
51 and table_changed = 3
52 and action=2;
53 begin
54 -- initialize message list
55 FND_MSG_PUB.initialize;
56 set_Vars;
57 IF undoid.count = 0 THEN
58 x_return_status := fnd_api.g_ret_sts_success;
59 return;
60 END IF;
61 i := undoid.first;
62 LOOP
63 l_undo_id := undoid(i).undo_id;
64 l_undo_id := undo_validate(l_undo_id,
65 x_return_status,
66 x_msg_count,
67 x_msg_data);
68 if (l_undo_id > 0 ) then
69 open c_mst(l_undo_id);
70 fetch c_mst into l_plan_id,
71 l_transaction_id,
72 l_sr_instance_id,
73 l_table_changed,
74 l_action,
75 l_last_update_date;
76 close c_mst ;
77 if ( l_plan_id is not null and l_plan_id <> -1 ) then
78
79 if ( l_action = inserted ) then
80 --undo an inserted record
81 insert_table(l_undo_id , l_table_changed ,
82 l_plan_id , l_transaction_id ,
83 l_sr_instance_id, x_return_status,
84 x_msg_count, x_msg_data );
85
86 Begin
87 -- if (l_table_changed in ( 3,4) ) then
88 -- cholpon
89 if (l_table_changed in ( 3,4, 8 ) ) then
90 Delete from msc_undo_details
91 where plan_id = l_plan_id
92 and (undo_id = l_undo_id
93 or undo_id in ( select undo_id
94 from msc_undo_summary
95 where plan_id = l_plan_id
96 and parent_id = l_undo_id));
97
98 Delete from msc_undo_summary
99 where plan_id = l_plan_id
100 and (undo_id = l_undo_id
101 or parent_id = l_undo_id);
102 else
103 Delete from msc_undo_summary
104 where plan_id = l_plan_id
105 and undo_id = l_undo_id;
106
107 Delete from msc_undo_summary
108 where plan_id = l_plan_id
109 and undo_id = l_undo_id;
110 end if;
111 Exception
112 When others then
113 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
114 FND_MSG_PUB.add_exc_msg('MSC_UNDO', 'UNDO');
115 FND_MSG_PUB.count_and_get(p_count=>x_msg_count,
116 p_data=>x_msg_data);
117 End ;
118 elsif ( l_action = updated ) then
119 if (l_table_changed = 3) then
120 open c_supp(l_plan_id, l_undo_id);
121 loop
122 fetch c_supp into s_undo_id, l_transaction_id, l_sr_instance_id;
123 exit when c_supp%notfound;
124
125 open c_dtl(l_plan_id, s_undo_id);
126 loop
127 fetch c_dtl into l_column_changed, l_old_value,
128 l_new_value, l_column_type;
129 exit when c_dtl%notfound;
130
131 --undo an updated record from undo_details
132 update_table(l_table_changed,
133 l_column_changed,
134 l_old_value, l_new_value,l_column_type,
135 l_plan_id, l_sr_instance_id,
136 l_transaction_id, x_return_status,
137 x_msg_count, x_msg_data, s_undo_id );
138 end loop;
139 close c_dtl;
140 end loop;
141 close c_supp;
142 else
143 open c_dtl(l_plan_id, l_undo_id);
144 loop
145 fetch c_dtl into l_column_changed, l_old_value,
146 l_new_value , l_column_type;
147 exit when c_dtl%notfound;
148
149 --undo an updated record from undo_details
150 update_table(l_table_changed,
151 l_column_changed,
152 l_old_value, l_new_value,l_column_type,
153 l_plan_id, l_sr_instance_id,
154 l_transaction_id, x_return_status,
155 x_msg_count, x_msg_data, l_undo_id );
156 end loop;
157 close c_dtl;
158 end if;
159 --end undo update table
160 Begin
161 if (l_table_changed in (3,4) ) then
162
163 Delete from msc_undo_details
164 where plan_id = l_plan_id
165 and (undo_id = l_undo_id
166 or undo_id in ( select undo_id
167 from msc_undo_summary
168 where plan_id = l_plan_id
169 and parent_id = l_undo_id));
170
171 Delete from msc_undo_summary
172 where plan_id = l_plan_id
173 and (undo_id = l_undo_id
174 or parent_id = l_undo_id);
175 else
176 Delete from msc_undo_details
177 where plan_id = l_plan_id
178 and undo_id = l_undo_id;
179
180 Delete from msc_undo_summary
181 where plan_id = l_plan_id
182 and undo_id = l_undo_id;
183
184 end if;
185 -- end delete undo_details
186
187 Exception
188 When others then
189 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
190 FND_MSG_PUB.add_exc_msg('MSC_UNDO', 'UNDO');
191 FND_MSG_PUB.count_and_get(p_count=>x_msg_count,
192 p_data=>x_msg_data);
193 End ;
194
195 elsif (l_action in (3)) then
196 Begin
197 Delete from msc_undo_summary
198 where plan_id = l_plan_id
199 and undo_id = l_undo_id;
200 Exception
201 When others then
202 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
203 FND_MSG_PUB.add_exc_msg('MSC_UNDO', 'UNDO');
204 FND_MSG_PUB.count_and_get(p_count=>x_msg_count,
205 p_data=>x_msg_data);
206 End ;
207 end if;
208 end if;
209 end if;
210 EXIT WHEN i = undoid.last ;
211 i := undoid.next(i);
212
213 END LOOP;
214 x_return_status := fnd_api.g_ret_sts_success;
215 fnd_msg_pub.count_and_get(p_count => x_msg_count,
216 p_data=>x_msg_data);
217 EXCEPTION
218 WHEN OTHERS THEN
219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
220 FND_MSG_PUB.add_exc_msg('MSC_UNDO', 'UNDO');
221 FND_MSG_PUB.count_and_get(p_count=>x_msg_count,
222 p_data=>x_msg_data);
223 end UNDO;
224
225 procedure STORE_UNDO (table_changed NUMBER,
226 action NUMBER,
227 transaction_id NUMBER,
228 plan_id NUMBER,
229 sr_instance_id NUMBER,
230 parent_id NUMBER,
231 changed_values MSC_UNDO.ChangeRGType,
232 x_return_status OUT NOCOPY VARCHAR2,
233 x_msg_count OUT NOCOPY NUMBER,
234 x_msg_data OUT NOCOPY VARCHAR2,
235 undo_id NUMBER DEFAULT NULL) IS
236 i number;
237 l_column_changed VARCHAR2(30);
238 l_column_changed_text VARCHAR2(240);
239 l_column_type VARCHAR2(10);
240 l_old_value VARCHAR2(240);
241 l_new_value VARCHAR2(240);
242
243 v_undo_id NUMBER;
244
245 l_plan_id NUMBER := plan_id;
246 l_parent_id NUMBER := parent_id;
247 l_transaction_id NUMBER:= transaction_id;
248 begin
249 --Initializa message list
250 FND_MSG_PUB.initialize;
251
252 set_vars;
253
254 if (plan_id is null or plan_id = -1 ) then
255 return;
256 end if;
257
258 if ( action not in (inserted, updated) ) then
259 x_return_status := FND_API.G_RET_STS_ERROR;
260 FND_MSG_PUB.add_exc_msg('MSC_UNDO',
261 'DEVELOPER ERROR : Invalid action passed to STORE_UNDO');
262 FND_MSG_PUB.count_and_get(p_count=>x_msg_count,
263 p_data=>x_msg_data);
264 Return ;
265 end if;
266
267 if ( (action = inserted) or (action = updated) ) then
268 if ( action = updated) and (changed_values.count = 0) then
269 x_return_status := fnd_api.g_ret_sts_success;
270 return;
271 end if;
272 BEGIN
273 if ( table_changed in (1,2,5,6,7) ) then
274 select MSC_UNDO_SUMMARY_S.nextval
275 into v_undo_id
276 from dual;
277 else
278 v_undo_id := undo_id;
279 end if;
280 --Insert a record into MSC_UNDO_SUMMARY
281 INSERT INTO MSC_UNDO_SUMMARY (
282 undo_id,
283 plan_id,
284 sr_instance_id,
285 created_by,
286 creation_date,
287 last_updated_by,
288 last_update_date,
289 last_update_login,
290 table_changed,
291 action,
292 transaction_id,
293 bookmark_name,
294 parent_id )
295 VALUES (
296 v_undo_id,
297 plan_id,
298 sr_instance_id,
299 v_user_id,
300 SYSDATE,
301 v_user_id,
302 SYSDATE,
303 v_last_update_login,
304 table_changed,
305 action,
306 transaction_id,
307 NULL,
308 parent_id);
309 EXCEPTION
310 WHEN OTHERS THEN
311 ROLLBACK;
312 fnd_msg_pub.add_Exc_msg('MSC_UNDO', 'STORE_UNDO');
313 fnd_msg_pub.count_and_get(p_count=>x_msg_Count, p_data=>x_msg_data);
314 END ;
315 end if;
316 if ( action = updated ) then
317
318 i := changed_values.first;
319 LOOP
320 l_column_changed := changed_values(i).column_changed;
321 l_column_changed_text := changed_values(i).column_changed_text;
322 l_column_type := changed_values(i).column_type ;
323 l_old_value := changed_values(i).Old_Value ;
324 l_new_value := changed_values(i).New_Value ;
325
326 BEGIN
327 INSERT INTO MSC_UNDO_DETAILS (
328 UNDO_ID,
329 PLAN_ID,
330 COLUMN_CHANGED,
331 COLUMN_CHANGED_TEXT,
332 COLUMN_TYPE,
333 CREATED_BY,
334 CREATION_DATE,
335 LAST_UPDATED_BY,
336 LAST_UPDATE_DATE,
337 LAST_UPDATE_LOGIN,
338 OLD_VALUE,
339 NEW_VALUE )
340 VALUES (
341 v_undo_id,
342 plan_id,
343 l_column_changed,
344 l_column_changed_text,
345 l_column_type,
346 v_user_id,
347 sysdate,
348 v_user_id,
349 sysdate,
350 v_last_update_login,
351 l_old_value,
352 l_new_value );
353
354 EXIT WHEN i = changed_values.last ;
355 i := changed_values.next(i);
356
357 EXCEPTION
358 WHEN OTHERS THEN
359 --ROLLBACK;
360 fnd_msg_pub.add_Exc_msg('MSC_UNDO', 'STORE_UNDO');
361 fnd_msg_pub.count_and_get(p_count=>x_msg_Count, p_data=>x_msg_data);
362 END ;
363 END LOOP;
364 end if;
365
366 x_return_status := FND_API.G_RET_STS_SUCCESS;
367
368 end STORE_UNDO;
369
370 procedure ADD_BOOKMARK(bookmark_name VARCHAR2,
371 action NUMBER,
372 plan_id NUMBER,
373 x_return_status OUT NOCOPY VARCHAR2,
374 x_msg_count OUT NOCOPY NUMBER,
375 x_msg_data OUT NOCOPY VARCHAR2) IS
376
377 v_undo_id number;
378
379 begin
380 set_vars;
381 FND_MSG_PUB.Initialize;
382
383 if (plan_id is null or plan_id = -1) then
384 return;
385 end if;
386
387 IF ( action not in
388 (bookmark, start_online, replan_start, replan_stop, stop_online) ) THEN
389 x_return_status := FND_API.G_RET_STS_ERROR;
390 x_msg_count := 1;
391 x_msg_data := 'DEVELOPER ERROR : '
392 ||' Invalid Action passed to MSC_UNDO.add_bookmark';
393 FND_MSG_PUB.count_and_get(p_count =>x_msg_count,
394 p_data=>x_msg_data );
395 ELSE
396 SELECT MSC_UNDO_SUMMARY_S.nextval
397 INTO v_undo_id
398 from dual;
399
400 x_return_status := FND_API.G_RET_STS_SUCCESS;
401
402 --Insert a record into MSC_UNDO_SUMMARY
403 INSERT INTO MSC_UNDO_SUMMARY (
404 undo_id,
405 plan_id,
406 sr_instance_id,
407 created_by,
408 creation_date,
409 last_updated_by,
410 last_update_date,
411 last_update_login,
412 table_changed,
413 action,
414 transaction_id,
415 bookmark_name )
416 VALUES (
417 v_undo_id,
418 plan_id,
419 0,
420 v_user_id,
421 SYSDATE,
422 v_user_id,
423 SYSDATE,
424 v_last_update_login,
425 NULL,
426 action,
427 0,
428 bookmark_name );
429 END IF;
430
431 x_return_status := FND_API.G_RET_STS_SUCCESS;
432
433 EXCEPTION
434 WHEN OTHERS THEN
435 ROLLBACK;
436 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
437 FND_MSG_PUB.add_exc_msg('MSC_UNDO', 'Add_Bookmark');
438 FND_MSG_PUB.count_and_get(p_count=>x_msg_count,
439 p_data => x_msg_data);
440
441 end ADD_BOOKMARK;
442
443
444 --Private procedures
445
446 PROCEDURE insert_table (p_undo_id NUMBER,
447 p_table_changed NUMBER,
448 p_plan_id NUMBER,
449 p_transaction_id NUMBER,
450 p_sr_instance_id NUMBER,
451 x_return_status OUT NOCOPY VARCHAR2,
452 x_msg_count OUT NOCOPY NUMBER,
453 x_msg_data OUT NOCOPY VARCHAR2) IS
454
455 cursor c_net_res (l_undo_id number) is
456 select distinct a.sr_instance_id,
457 a.transaction_id,
458 b.old_value,
459 a.action,
460 b.column_changed
461 from msc_undo_summary a,
462 msc_undo_details b
463 where a.undo_id = b.undo_id (+)
464 and (a.undo_id = l_undo_id
465 or a.parent_id = l_undo_id);
466
467
468 /*
469 cursor c_demand_mds (l_plan number, l_instance number, l_trx number) is
470 select origination_type
471 from msc_demands
472 where plan_id = l_plan
473 and sr_instance_id = l_instance
474 and demand_id = l_trx;
475 */
476 l_sr_instance_id NUMBER;
477 l_transaction_id NUMBER;
478 l_old_value NUMBER;
479 l_action NUMBER;
480 ll_undo_id NUMBER;
481 l_column_name varchar2(30);
482
483 l_order_type NUMBER;
484
485 BEGIN
486 -- Initialize message list
487 FND_MSG_PUB.initialize;
488
489 --Set the User-id
490 set_vars;
491
492 if ( p_table_changed = 1 ) then
493 --Msc_supplies
494 /*
495 Delete from msc_supplies
496 where transaction_id = p_transaction_id
497 and plan_id = p_plan_id
498 and sr_instance_id = p_sr_instance_id ;
499 */
500 update msc_supplies
501 set firm_quantity = 0,
502 firm_planned_type = 0,
503 status = 0,
504 applied = 2
505 where transaction_id = p_transaction_id
506 and plan_id = p_plan_id
507 and sr_instance_id = p_sr_instance_id ;
508 elsif ( p_table_changed = 2 ) then
509 --Msc_demands
510 /*
511 open c_demand_mds(p_plan_id,p_sr_instance_id, p_transaction_id);
512 fetch c_demand_mds into l_order_type;
513 close c_demand_mds;
514 if (l_order_type = 8 ) then
515 */
516 update msc_demands
517 set firm_quantity = 0,
518 status = 0,
519 applied = 2
520 where plan_id = p_plan_id
521 and demand_id = p_transaction_id
522 and sr_instance_id = p_sr_instance_id;
523 /*
524 else
525 Delete from msc_demands
526 where demand_id = p_transaction_id
527 and plan_id = p_plan_id
528 and sr_instance_id = p_sr_instance_id ;
529 end if;
530 */
531 elsif ( p_table_changed = 3 ) then
532 --Msc_supplier_capacities
533 /*
534 Delete from msc_supplier_capacities
535 where transaction_id in (select transaction_id
536 from msc_undo_summary
537 where plan_id = p_plan_id
538 and (undo_id = p_undo_id or parent_id = p_undo_id))
539 and plan_id = p_plan_id ;
540 */
541 update msc_supplier_capacities
542 set capacity = 0,
543 status = 0,
544 applied = 2
545 where plan_id = p_plan_id
546 and transaction_id in (select transaction_id
547 from msc_undo_summary
548 where plan_id = p_plan_id
549 and (undo_id = p_undo_id or parent_id = p_undo_id));
550 elsif ( p_table_changed in ( 4, 8) ) then
551 --Msc_net_resource_Avail
552 -- bug 1314938 - typical one .. do not delete the row,
553 --instead update the capacity to zero, for this record and its parent record
554 /*
555 Delete from msc_net_resource_avail
556 where transaction_id = p_transaction_id
557 and plan_id = p_plan_id
558 and sr_instance_id = p_sr_instance_id ;
559 */
560 open c_net_res(p_undo_id);
561 loop
562 fetch c_net_res into l_sr_instance_id,
563 l_transaction_id, l_old_value,
564 l_action, l_column_name;
565 exit when c_net_res%notfound;
566 if (l_action = 1) then
567 update msc_net_resource_avail
568 set capacity_units = -1,
569 status = 0,
570 applied = 2
571 where plan_id = p_plan_id
572 and sr_instance_id = l_sr_instance_id
573 and transaction_id = l_transaction_id;
574 else
575 update msc_net_resource_avail
576 set capacity_units =
577 decode(l_column_name,'CAPACITY_UNITS',
578 l_old_value,capacity_units),
579 from_time =
580 decode(l_column_name,'FROM_TIME',
581 l_old_value,from_time),
582 to_time =
583 decode(l_column_name,'TO_TIME',
584 l_old_value,to_time),
585 status = 0,
586 applied = 2
587 where plan_id = p_plan_id
588 and sr_instance_id = l_sr_instance_id
589 and transaction_id = l_transaction_id;
590
591 --cholpon update resources as well
592 if (p_table_changed = 8) then
593 update msc_net_res_inst_avail
594 set capacity_units =
595 decode(l_column_name,'CAPACITY_UNITS',
596 l_old_value,capacity_units),
597 from_time =
598 decode(l_column_name,'FROM_TIME',
599 l_old_value,from_time),
600 to_time =
601 decode(l_column_name,'TO_TIME',
602 l_old_value,to_time),
603 status = 0,
604 applied = 2
605 where plan_id = p_plan_id
606 and sr_instance_id = l_sr_instance_id
607 and inst_transaction_id = l_transaction_id;
608 end if;
609
610 end if;
611
612 -- need to recalculate the parent records
613
614 msc_update_resource.refresh_parent_record(
615 p_plan_id,l_sr_instance_id, l_transaction_id);
616 end loop;
617 close c_net_res;
618
619
620 elsif ( p_table_changed = 5 ) then
621
622 --Msc_plans
623 Delete from msc_plans
624 where plan_id = p_plan_id ;
625 elsif ( p_table_changed = 7 ) then
626 -- Trips
627 delete from msc_shipments
628 where plan_id = p_plan_id
629 and sr_instance_id = l_sr_instance_id
630 and shipment_id = l_transaction_id;
631 end if;
632
633 x_return_status := fnd_api.g_ret_sts_success;
634
635 EXCEPTION
636 WHEN OTHERS THEN
637 ROLLBACK;
638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
639 FND_MSG_PUB.add_exc_msg('MSC_UNDO', 'INSERT_TABLE');
640 FND_MSG_PUB.count_and_get(p_count=>x_msg_count,
641 p_data => x_msg_data);
642 END insert_table ;
643
644 PROCEDURE set_vars IS
645 BEGIN
646
647 v_user_id := FND_GLOBAL.user_id;
648 v_last_update_login := FND_GLOBAL.login_id;
649
650 END set_vars;
651
652 PROCEDURE update_table(p_table_changed NUMBER,
653 p_column_changed VARCHAR2,
654 p_old_value VARCHAR2,
655 p_new_value VARCHAR2,
656 p_column_type VARCHAR2,
657 p_plan_id NUMBER,
658 p_sr_instance_id NUMBER,
659 p_transaction_id NUMBER,
660 x_return_status OUT NOCOPY VARCHAR2,
661 x_msg_count OUT NOCOPY NUMBER,
662 x_msg_data OUT NOCOPY VARCHAR2,
663 p_undo_id NUMBER) IS
664
665 update_str varchar2(500);
666 set_str varchar2(500);
667 where_str varchar2(500);
668 sql_string VARCHAR2(1000);
669 l_parent_id NUMBER;
670
671 l_sr_instance_id number;
672 l_transaction_id number;
673 l_old_value number;
674 l_action number;
675
676 ll_undo_id NUMBER;
677
678 cursor c_net_res (l_undo_id number) is select
679 distinct a.sr_instance_id,
680 a.transaction_id,
681 b.old_value,
682 a.action,
683 b.column_changed
684 from msc_undo_summary a,
685 msc_undo_details b
686 where a.undo_id = b.undo_id (+)
687 and (a.undo_id = l_undo_id
688 or a.parent_id = l_undo_id);
689 l_column_name varchar2(30);
690
691 BEGIN
692 -- Initialize message list
693 FND_MSG_PUB.Initialize;
694
695 --Set the user-id
696 set_vars;
697
698 if (p_table_changed = 1) then
699 update_str := 'UPDATE MSC_SUPPLIES SET STATUS=0, APPLIED=2, ';
700 if (p_column_type = 'DATE') then
701 set_str := p_column_changed
702 ||' = fnd_date.canonical_to_date(:p_old_value) ';
703 else
704 set_str := p_column_changed||' = :p_old_value ';
705 end if;
706 where_str := ' WHERE plan_id = :p_plan_id '
707 ||' AND transaction_id = :p_transaction_id '
708 ||' AND sr_instance_id = :p_sr_instance_id ';
709
710 sql_string := update_str||' '||set_str||' '||where_str;
711 Execute immediate sql_string
712 using p_old_value, p_plan_id,
713 p_transaction_id, p_sr_instance_id ;
714 elsif (p_table_changed = 2) then
715 update_str := 'UPDATE MSC_DEMANDS SET STATUS=0, APPLIED=2, ';
716 if (p_column_type = 'DATE') then
717 set_str := p_column_changed
718 ||' = fnd_date.canonical_to_date(:p_old_value) ';
719 else
720 set_str := p_column_changed||' = :p_old_value ';
721 end if;
722 where_str := ' WHERE plan_id = :p_plan_id '
723 ||' AND demand_id = :p_transaction_id '
724 ||' AND sr_instance_id = :p_sr_instance_id ';
725
726 sql_string := update_str||' '||set_str||' '||where_str;
727 Execute immediate sql_string
728 using p_old_value, p_plan_id,
729 p_transaction_id, p_sr_instance_id ;
730
731 elsif (p_table_changed = 3) then
732 update_str := 'UPDATE MSC_SUPPLIER_CAPACITIES SET ';
733 update_str := update_str||' STATUS=0, APPLIED=2, ';
734 if (p_column_type = 'DATE') then
735 set_str := p_column_changed
736 ||' = fnd_date.canonical_to_date(:p_old_value) ';
737 else
738 set_str := p_column_changed||' = :p_old_value ';
739 end if;
740 where_str := ' WHERE plan_id = :p_plan_id '
741 ||' AND transaction_id = :p_transaction_id ';
742
743 sql_string := update_str||' '||set_str||' '||where_str;
744 Execute immediate sql_string
745 using p_old_value, p_plan_id, p_transaction_id ;
746 elsif (p_table_changed in ( 4, 8) ) then
747 open c_net_res(p_undo_id);
748 loop
749 fetch c_net_res into l_sr_instance_id,
750 l_transaction_id, l_old_value,
751 l_action, l_column_name;
752 exit when c_net_res%notfound;
753 if (l_action = 1) then
754 update msc_net_resource_avail
755 set capacity_units = -1,
756 status = 0,
757 applied = 2
758 where plan_id = p_plan_id
759 and sr_instance_id = l_sr_instance_id
760 and transaction_id = l_transaction_id;
761 else
762 update msc_net_resource_avail
763 set capacity_units =
764 decode(l_column_name,'CAPACITY_UNITS',
765 l_old_value,capacity_units),
766 from_time =
767 decode(l_column_name,'FROM_TIME',
768 l_old_value,from_time),
769 to_time =
770 decode(l_column_name,'TO_TIME',
771 l_old_value,to_time),
772 status = 0,
773 applied = 2
774 where plan_id = p_plan_id
775 and sr_instance_id = l_sr_instance_id
776 and transaction_id = l_transaction_id;
777 end if;
778
779 -- need to recalculate the parent records
780
781 if (p_table_changed = 8) then
782 update msc_net_res_inst_avail
783 set capacity_units =
784 decode(l_column_name,'CAPACITY_UNITS',
785 l_old_value,capacity_units),
786 from_time =
787 decode(l_column_name,'FROM_TIME',
788 l_old_value,from_time),
789 to_time =
790 decode(l_column_name,'TO_TIME',
791 l_old_value,to_time),
792 status = 0,
793 applied = 2
794 where plan_id = p_plan_id
795 and sr_instance_id = l_sr_instance_id
796 and inst_transaction_id = l_transaction_id;
797
798
799 end if;
800
801 msc_update_resource.refresh_parent_record(
802 p_plan_id,l_sr_instance_id, l_transaction_id);
803 end loop;
804 close c_net_res;
805
806 elsif (p_table_changed = 5) then
807 update_str := 'UPDATE MSC_PLANS SET ';
808 set_str := p_column_changed||' = :p_old_value ';
809 where_str := ' WHERE plan_id = :p_plan_id ';
810 sql_string := update_str||' '||set_str||' '||where_str;
811 Execute immediate sql_string
812 using p_old_value, p_plan_id ;
813 elsif (p_table_changed = 6) then
814 update_str := 'UPDATE MSC_RESOURCE_REQUIREMENTS SET ';
815 update_str := update_str||' STATUS=0, APPLIED=2, ';
816 if (p_column_type = 'DATE') then
817 set_str := p_column_changed
818 ||' = fnd_date.canonical_to_date(nvl(:p_old_value,null)) ';
819 else
820 set_str := p_column_changed||' = :p_old_value ';
821 end if;
822 where_str := ' WHERE plan_id = :p_plan_id '
823 ||' AND sr_instance_id = :p_sr_instance_id '
824 ||' AND transaction_id = :p_transaction_id ';
825
826 sql_string := update_str||' '||set_str||' '||where_str;
827 Execute immediate sql_string
828 using p_old_value, p_plan_id,
829 p_sr_instance_id,p_transaction_id ;
830 elsif (p_table_changed = 7) then
831 update_str := 'UPDATE MSC_SHIPMENTS SET STATUS=0, APPLIED=2, ';
832 if (p_column_type = 'DATE') then
833 set_str := p_column_changed
834 ||' = fnd_date.canonical_to_date(:p_old_value) ';
835 else
836 set_str := p_column_changed||' = :p_old_value ';
837 end if;
838 where_str := ' WHERE plan_id = :p_plan_id '
839 ||' AND shipment_id = :p_transaction_id '
840 ||' AND sr_instance_id = :p_sr_instance_id ';
841
842 sql_string := update_str||' '||set_str||' '||where_str;
843 Execute immediate sql_string
844 using p_old_value, p_plan_id,
845 p_transaction_id, p_sr_instance_id ;
846
847 end if;
848
849 x_return_status := FND_API.G_RET_STS_SUCCESS;
850
851 EXCEPTION
852 WHEN OTHERS THEN
853 --ROLLBACK;
854 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
855 FND_MSG_PUB.add_exc_msg('MSC_UNDO', 'UPDATE_TABLE'
856 ||' '||sql_string);
857 FND_MSG_PUB.count_and_get(p_count=>x_msg_count,
858 p_data => x_msg_data);
859 END update_table;
860
861 function undo_validate (v_undo_id number,
862 x_return_status OUT NOCOPY VARCHAR2,
863 x_msg_count OUT NOCOPY NUMBER,
864 x_msg_data OUT NOCOPY VARCHAR2) return number is
865
866 cursor c_mst (l_undo_id NUMBER) is
867 select plan_id, sr_instance_id, transaction_id, table_changed, action,
868 last_updated_by, last_update_date, identifier1_name, identifier2_name,
869 identifier3_name
870 from msc_undo_summary_v
871 where undo_id = l_undo_id;
872
873 cursor c_noundo_same1 (v_plan_id number,
874 v_sr_instance_id number,
875 v_table_changed number,
876 v_user number,
877 v_date date) is
878 select count(undo_id)
879 from msc_undo_summary
880 where plan_id = v_plan_id
881 and sr_instance_id = v_sr_instance_id
882 and table_changed = v_table_changed
883 and last_updated_by = v_user
884 --and last_update_date > v_date
885 -- and parent_id is null
886 and undo_id >v_undo_id;
887
888 cursor c_noundo_same2 (v_plan_id number,
889 v_sr_instance_id number,
890 v_transaction_id number,
891 v_table_changed number,
892 v_user number,
893 v_date date) is
894 select count(undo_id)
895 from msc_undo_summary
896 where plan_id = v_plan_id
897 and sr_instance_id = v_sr_instance_id
898 and transaction_id = v_transaction_id
899 and table_changed = v_table_changed
900 and last_updated_by = v_user
901 --and last_update_date > v_date
902 -- and parent_id is null
903 and undo_id > v_undo_id;
904
905 cursor c_noundo_diff1 (v_plan_id number, v_sr_instance_id number,
906 v_table_changed number, v_user number,
907 v_date date) is
908 select count(undo_id)
909 from msc_undo_summary
910 where plan_id = v_plan_id
911 and sr_instance_id = v_sr_instance_id
912 and table_changed = v_table_changed
913 and last_updated_by <> v_user
914 --and last_update_date > v_date
915 --and parent_id is null
916 and undo_id > v_undo_id;
917
918 cursor c_noundo_diff2 (v_plan_id number, v_sr_instance_id number,
919 v_transaction_id number, v_table_changed number,
920 v_user number, v_date date) is
921 select count(undo_id)
922 from msc_undo_summary
923 where plan_id = v_plan_id
924 and sr_instance_id = v_sr_instance_id
925 and transaction_id = v_transaction_id
926 and table_changed = v_table_changed
927 and last_updated_by <> v_user
928 --and last_update_date > v_date
929 --and parent_id is null
930 and undo_id > v_undo_id;
931
932 cursor c_nofirm (v_plan_id number, v_sr_instance_id number,
933 v_transaction_id number) is
934 select firm_planned_type
935 from msc_supplies
936 where transaction_id = v_transaction_id
937 and sr_instance_id = v_sr_instance_id
938 and plan_id = v_plan_id ;
939
940 cursor c_olprun (v_plan_id number) is
941 select undo_id
942 from msc_undo_summary
943 where plan_id = v_plan_id
944 and action = 4;
945
946 v_temp number;
947 l_count number;
948 l_olprun_undo_id number;
949
950 l_plan_id number;
951 l_sr_instance_id number;
952 l_transaction_id number;
953
954 l_table_changed number;
955 l_action number;
956 l_last_updated_by number;
957 l_last_update_date date;
958 l_identifier1_name varchar2(250);
959 l_identifier2_name varchar2(250);
960 l_identifier3_name varchar2(250);
961
962 l_token Varchar2(250);
963
964 l_msg_text Varchar2(100);
965 l_diff_user number;
966 begin
967 open c_mst(v_undo_id);
968 fetch c_mst into l_plan_id,
969 l_sr_instance_id,
970 l_transaction_id,
971 l_table_changed,
972 l_action,
973 l_last_updated_by,
974 l_last_update_date,
975 l_identifier1_name,
976 l_identifier2_name,
977 l_identifier3_name ;
978 close c_mst ;
979
980 l_token := l_identifier1_name||' '||l_identifier2_name||' '||
981 l_identifier3_name;
982 if (fnd_global.user_id <> l_last_updated_by ) then
983 x_return_status := FND_API.G_RET_STS_ERROR;
984 FND_MESSAGE.SET_NAME('MSC', 'MSC_UNDO_OTHER_USERS');
985 FND_MESSAGE.SET_TOKEN('RECORD',l_token);
986 FND_MSG_PUB.ADD;
987 FND_MSG_PUB.count_and_get(p_count=>x_msg_count,
988 p_data => x_msg_data);
989 return -3;
990 end if;
991
992 if (l_table_changed = 5) then
993 open c_noundo_diff1(l_plan_id, l_sr_instance_id,
994 l_table_changed, l_last_updated_by,
995 l_last_update_date);
996 fetch c_noundo_diff1 into l_count;
997 close c_noundo_diff1 ;
998
999 if (l_count <> 0 ) then
1000 x_return_status := FND_API.G_RET_STS_ERROR;
1001 FND_MESSAGE.set_name('MSC', 'MSC_UNDO_REC_CHG_DIFF_USER');
1002 FND_MESSAGE.SET_TOKEN('RECORD',l_token);
1003 FND_MSG_PUB.ADD;
1004 FND_MSG_PUB.count_and_get(p_count=>x_msg_count,
1005 p_data => x_msg_data);
1006 return -1;
1007 end if;
1008 else
1009 open c_noundo_diff2(l_plan_id, l_sr_instance_id,
1010 l_transaction_id, l_table_changed, l_last_updated_by,
1011 l_last_update_date);
1012 fetch c_noundo_diff2 into l_count;
1013 close c_noundo_diff2 ;
1014
1015 if (l_count <> 0 ) then
1016 x_return_status := FND_API.G_RET_STS_ERROR;
1017 FND_MESSAGE.set_name('MSC', 'MSC_UNDO_REC_CHG_DIFF_USER');
1018 FND_MESSAGE.SET_TOKEN('RECORD',l_token);
1019 FND_MSG_PUB.ADD;
1020 FND_MSG_PUB.count_and_get(p_count=>x_msg_count,
1021 p_data => x_msg_data);
1022 return -1;
1023 end if;
1024 end if;
1025
1026 if ( l_table_changed = 5) then
1027 open c_noundo_same1(l_plan_id, l_sr_instance_id,
1028 l_table_changed, l_last_updated_by,
1029 l_last_update_date);
1030 fetch c_noundo_same1 into l_count;
1031 close c_noundo_same1 ;
1032
1033 if (l_count <> 0 ) then
1034 x_return_status := FND_API.G_RET_STS_ERROR;
1035 FND_MESSAGE.set_name('MSC', 'MSC_UNDO_REC_CHG_SAME_USER');
1036 FND_MESSAGE.SET_TOKEN('RECORD',l_token);
1037 FND_MSG_PUB.ADD;
1038 FND_MSG_PUB.count_and_get(p_count=>x_msg_count,
1039 p_data => x_msg_data);
1040 return -2;
1041 end if;
1042 else
1043 open c_noundo_same2(l_plan_id, l_sr_instance_id,
1044 l_transaction_id, l_table_changed, l_last_updated_by,
1045 l_last_update_date);
1046 fetch c_noundo_same2 into l_count;
1047 close c_noundo_same2 ;
1048
1049 if (l_count <> 0 ) then
1050 x_return_status := FND_API.G_RET_STS_ERROR;
1051 FND_MESSAGE.set_name('MSC', 'MSC_UNDO_REC_CHG_SAME_USER');
1052 FND_MESSAGE.SET_TOKEN('RECORD',l_token);
1053 FND_MSG_PUB.ADD;
1054 FND_MSG_PUB.count_and_get(p_count=>x_msg_count,
1055 p_data => x_msg_data);
1056 return -2;
1057 end if;
1058 end if;
1059 if (l_table_changed = 1 ) then
1060 l_olprun_undo_id := v_undo_id;
1061 open c_olprun(l_plan_id);
1062 fetch c_olprun into l_olprun_undo_id;
1063 close c_olprun;
1064 open c_nofirm(l_plan_id, l_sr_instance_id, l_transaction_id);
1065 fetch c_nofirm into l_count;
1066 if (l_olprun_undo_id > v_undo_id ) then
1067 if (l_count <> 1) then
1068 x_return_status := FND_API.G_RET_STS_ERROR;
1069 FND_MESSAGE.set_name('MSC', 'MSC_UNDO_UNFIRM');
1070 FND_MESSAGE.SET_TOKEN('RECORD',l_token);
1071 FND_MSG_PUB.ADD;
1072 FND_MSG_PUB.count_and_get(p_count=>x_msg_count,
1073 p_data => x_msg_data);
1074 return -4;
1075 end if;
1076 end if;
1077 end if;
1078 return v_undo_id;
1079 end undo_validate ;
1080 END MSC_UNDO;