[Home] [Help]
PACKAGE BODY: APPS.MSC_ATP_24X7
Source
1 PACKAGE BODY MSC_ATP_24x7 AS
2 /* $Header: MSCTATPB.pls 120.3 2007/12/12 10:42:07 sbnaik ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MSC_ATP_24x7';
5 G_TFS_ERROR VARCHAR2(70);
6 G_TFS_ERRCODE NUMBER;
7 G_FND_USER NUMBER;
8
9 ---------------------------------------------------------------------------------
10 -- Function Declarations
11
12
13 PROCEDURE ATP_SYNCHRONIZE (
14 p_old_plan_id IN NUMBER,
15 p_new_plan_id IN NUMBER,
16 x_return_status OUT NOCOPY VARCHAR2,
17 ERRBUF OUT NOCOPY VARCHAR2
18 );
19
20 PROCEDURE Synchronize_Call_To_ATP (
21 p_atp_table IN MRP_ATP_PUB.ATP_Rec_Typ,
22 p_session_id IN number,
23 x_total_time OUT NOCOPY number,
24 x_return_status OUT NOCOPY varchar2
25 );
26
27 -- This procedure is not used anymore as 24x7 doesn't process pre-collection records since
28 -- MDS is not supported for 24x7 ATP plans.
29
30 PROCEDURE Get_Records_Pre_Collections (
31 p_old_plan_id IN number,
32 p_new_plan_id IN number,
33 x_atp_rec OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
34 x_record_count OUT NOCOPY number,
35 x_return_status OUT NOCOPY varchar2
36 );
37
38 PROCEDURE Get_Records_Post_Collections (
39 p_old_plan_id IN number,
40 p_new_plan_id IN number,
41 p_session_id IN number,
42 x_atp_rec OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
43 x_record_count OUT NOCOPY number,
44 x_return_status OUT NOCOPY varchar2
45 );
46
47 PROCEDURE Update_Sync_flag (
48 p_atp_table IN MRP_ATP_PUB.ATP_Rec_Typ,
49 p_old_plan_id IN number,
50 x_return_status OUT NOCOPY varchar2
51 );
52
53 PROCEDURE ATP_Downtime_Init (
54 p_old_plan_id IN number,
55 p_new_plan_id IN number,
56 x_return_status OUT NOCOPY varchar2
57 );
58
59
60 PROCEDURE ATP_Downtime_Complete (
61 p_old_plan_id IN number,
62 p_new_plan_id IN number,
63 x_return_status OUT NOCOPY varchar2
64 );
65
66
67 PROCEDURE ATP_Sync_Error (
68 p_old_plan_id IN number,
69 p_new_plan_id IN number
70 );
71
72 PROCEDURE Calculate_Downtime_SO_Records (
73 p_number_of_records IN number,
74 p_total_time IN number
75 );
76
77
78 PROCEDURE Update_Summary_Flag (
79 p_plan_id IN number,
80 p_status IN number,
81 x_return_status OUT NOCOPY varchar2
82 );
83
84 -- CTO Re-arch changes, this procedure will not be used anymore, instead MSC_SATP_FUNC.New_Extend_Atp is used.
85 PROCEDURE extend_atp_rec_typ (
86 p_atp_tab IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
87 p_size IN number
88 );
89
90 PROCEDURE Extended_Sync_Wait (
91 l_time IN number,
92 x_return_status OUT NOCOPY varchar2
93 );
94
95 PROCEDURE Print_Input_Rec (
96 x_atp_rec IN MRP_ATP_PUB.ATP_Rec_Typ
97 );
98
99 PROCEDURE Refresh_Snapshot (
100 x_return_status OUT NOCOPY varchar2
101 );
102
103 ---------------------------------------------------------------------------------
104 -- Main Procedures
105
106 PROCEDURE Call_Synchronize (
107 ERRBUF OUT NOCOPY VARCHAR2,
108 RETCODE OUT NOCOPY NUMBER,
109 p_old_plan_id IN NUMBER
110 ) IS
111
112 l_return_status varchar2(1);
113 l_old_plan_id number;
114 l_new_plan_id number;
115
116 BEGIN
117
118 RETCODE := MSC_POST_PRO.G_SUCCESS;
119 G_TFS_ERROR := null;
120 G_TFS_ERRCODE := MSC_POST_PRO.G_SUCCESS;
121
122
123 conc_log ('---------------Call_Synchronize -------------------');
124
125 conc_log (' Old Plan ID : ' || p_old_plan_id);
126 conc_log (' ');
127
128 if (p_old_plan_id is NULL) then
129 conc_log ('Plan ID is NULL. Please specify a plan ID');
130 RETCODE := MSC_POST_PRO.G_WARNING;
131 RETURN;
132 end if;
133
134 conc_debug ('Fetching New PLAN ID from the database ...');
135 BEGIN
136 select plan_id
137 into l_new_plan_id
138 from msc_plans
139 where copy_plan_id = p_old_plan_id;
140 EXCEPTION
141 when OTHERS then
142 conc_log ('Unable to retrieve the copy plan ID for the old plan');
143 RETCODE := MSC_POST_PRO.G_ERROR;
144 ERRBUF := sqlerrm;
145 RETURN;
146 END;
147
148 conc_log (' New plan ID ' || l_new_plan_id );
149
150 ATP_SYNCHRONIZE (
151 p_old_plan_id,
152 l_new_plan_id,
153 l_return_status,
154 ERRBUF);
155
156 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
157 RETCODE := G_TFS_ERRCODE;
158 ERRBUF := G_TFS_ERROR;
159 end if;
160
161 conc_log ('----------------End Call Synchronize-------------');
162
163 END Call_Synchronize;
164
165
166 ---------------------------------------------------------------------------------
167 PROCEDURE ATP_SYNCHRONIZE (
168 p_old_plan_id IN NUMBER,
169 p_new_plan_id IN NUMBER,
170 x_return_status OUT NOCOPY VARCHAR2,
171 ERRBUF OUT NOCOPY VARCHAR2
172 ) IS
173
174 l_atp_rec MRP_ATP_PUB.ATP_Rec_Typ;
175 l_atp_rec1 MRP_ATP_PUB.ATP_Rec_Typ;
176 l_session_id number;
177 l_atp_period MRP_ATP_PUB.ATP_Period_Typ;
178 l_atp_supply_demand MRP_ATP_PUB.ATP_Supply_Demand_Typ;
179 l_atp_details MRP_ATP_PUB.ATP_Details_Typ;
180 l_msg_count number;
181 l_msg_data varchar2(3000);
182 l_return_status varchar2(1);
183 l_record_count number;
184 l_sync_done number;
185 l_to_call_ATP number;
186 l_total_time number;
187 l_downtime_start number;
188 l_downtime_end number;
189 l_downtime_total number;
190 l_counter number;
191
192 l_demand_id_arr MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
193 l_demand_id_arr_nl MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
194
195 BEGIN
196
197 conc_log ('-------ATP 24x7 Synchronization ------------');
198 conc_log (' ');
199 conc_log (' Old Plan ID : ' || p_old_plan_id );
200 conc_log (' New Plan ID : ' || p_new_plan_id );
201
202 -- Set the global to indicate ATP Synchronization
203 conc_debug ('Setting ATP Synchronization flag');
204 MSC_ATP_PVT.G_SYNC_ATP_CHECK := 'Y';
205
206 -- Set status in MSC_PLANS - summary_flag
207 conc_debug ('Updating summary flag ');
208 Update_Summary_Flag ( p_new_plan_id,
209 G_SF_SYNC_RUNNING,
210 l_return_status);
211
212 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
213 conc_log (' Cannot update ATP Summary status flag.');
214 ATP_Sync_Error(p_old_plan_id, p_new_plan_id);
215 G_TFS_ERROR := 'Unable to update Plan Information';
216 G_TFS_ERRCODE := MSC_POST_PRO.G_ERROR;
217 commit;
218 return;
219 end if;
220
221 -- Commit First to set status flags
222 COMMIT;
223
224 l_sync_done := 0;
225 l_to_call_ATP := 1;
226
227 while (l_sync_done <> 1) loop
228
229 -- We set this to one when it's the final iteration of the loop.
230 if (l_sync_done = 2) then
231 l_sync_done := 1;
232 end if;
233
234 -- Null out l_atp_rec
235 l_atp_rec := l_atp_rec1;
236 l_demand_id_arr := l_demand_id_arr_nl;
237
238 l_session_id := -1;
239
240 -- CTO Re-arch, need to get session_id and pass to Get_Records_Post_Collections
241 -- Get Session ID
242 begin
243 select MRP_ATP_SCHEDULE_TEMP_S.NEXTVAL
244 into l_session_id
245 from dual;
246 exception
247 when others then
248 conc_log ('Unable to get a sequence number for ATP Call');
249 conc_log ('Error is : ' || sqlerrm);
250 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
251 end;
252
253 conc_log ('Getting Records based on refresh number with session_id : ' || l_session_id);
254
255 -- Set Session_ID
256 order_sch_wb.set_session_id (l_session_id);
257
258 -- CTO Re-arch changes, added session_id as an IN param, as it will now be retrieved in ATP_Synchronize
259 Get_Records_Post_Collections (
260 p_old_plan_id,
261 p_new_plan_id,
262 l_session_id,
263 l_atp_rec,
264 l_record_count,
265 l_return_status
266 );
267
268 conc_log ('Number of records : ' || l_record_count);
269
270 -- Copy demand IDs
271 l_demand_id_arr := l_atp_rec.attribute_03;
272
273 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
274 ATP_Sync_Error (p_old_plan_id, p_new_plan_id);
275 G_TFS_ERROR := 'Unable to get records for synchronization';
276 G_TFS_ERRCODE := MSC_POST_PRO.G_ERROR;
277
278 commit;
279 return;
280 end if;
281
282 if (l_return_status = FND_API.G_RET_STS_ERROR) or (l_record_count <= 0) then
283 l_to_call_ATP := 0;
284 end if;
285
286 if (l_record_count <= G_TF7_SO_THRESHOLD) and l_sync_done = 0 then
287
288 l_sync_done := 2; -- Indicate downtime. Will use on next loop
289 conc_log ('Number of records to synchronize has reached threshold');
290
291 -- Get Downtime Start Time
292 begin
293 select hsecs / 100
294 into l_downtime_start
295 from v$timer;
296 exception
297 when others then
298 conc_log ('Unable to access timer');
299 conc_log ('Error is: ' || sqlerrm);
300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
301 end;
302
303 -- Signal ATP Downtime
304 ATP_Downtime_Init (p_old_plan_id, p_new_plan_id, l_return_status);
305
306 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
307 conc_log ('Something wrong in call to ATP Downtime Init');
308 -- Rollback changes made by ATP downtim INIt
309 rollback;
310 G_TFS_ERROR := 'Unable to Initiate ATP Downtime';
311 G_TFS_ERRCODE := MSC_POST_PRO.G_ERROR;
312
313 ATP_Sync_Error (p_old_plan_id, p_new_plan_id);
314 commit;
315 return;
316 end if;
317
318 -- Commit chages made by downtime INIT
319 commit;
320 end if;
321
322 if (l_to_call_ATP = 1 ) then
323
324 conc_log ('Calling ATP to Synchronize first set of records');
325 Synchronize_Call_To_ATP ( l_atp_rec,
326 l_session_id,
327 l_total_time,
328 l_return_status
329 );
330
331 if (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
332 ROLLBACK;
333 G_TFS_ERROR := 'Call to ATP API for Synchronization failed';
334 G_TFS_ERRCODE := MSC_POST_PRO.G_ERROR;
335 ATP_Sync_Error (p_old_plan_id, p_new_plan_id);
336 commit;
337 return;
338 end if;
339
340 -- Set timers
341 conc_log (l_record_count||' records synchronized in '||l_total_time|| ' seconds');
342 Calculate_Downtime_SO_Records ( l_record_count,
343 l_total_time
344 );
345
346 -- Copy over demand ID
347 l_atp_rec.attribute_03 := l_demand_id_arr;
348
349 Update_Sync_flag ( l_atp_rec,
350 p_old_plan_id,
351 l_return_status
352 );
353
354 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
355 conc_log ('Unable to update sync flag ');
356 ROLLBACK;
357 G_TFS_ERROR := 'Unable to update Plan Information';
358 G_TFS_ERRCODE := MSC_POST_PRO.G_ERROR;
359
360 ATP_Sync_Error (p_old_plan_id, p_new_plan_id);
361 commit;
362 return;
363 end if;
364
365 -- Commit changes so far
366 commit;
367
368 end if;
369 end loop;
370
371 ATP_Downtime_Complete (p_old_plan_id, p_new_plan_id, l_return_status);
372 commit;
373
374 -- Get Downtime Start Time
375 begin
376 select hsecs / 100
377 into l_downtime_end
378 from v$timer;
379 exception
380 when others then
381 conc_log ('Unable to access timer');
382 conc_log ('Error is: ' || sqlerrm);
383 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
384 end;
385
386 l_downtime_total := (l_downtime_end - l_downtime_start ) / 60;
387 conc_log (' ');
388 conc_log ('**********--------24x7 Synchronization Summary ---------********');
389 conc_log (' ');
390 conc_log (' Total Records Synchronized : ' || G_TF7_TOTAL_RECORDS );
391 conc_log (' Total Time Taken (minutes) : ' || G_TF7_TOTAL_TIME / 60);
392 conc_log (' Total ATP downtime (minutes) : ' || l_downtime_total / 60 );
393 conc_log (' ');
394 conc_log ('**********----------------------------------------------********');
395
396 -- Extended Synchronization Starts here
397 -- This collects all stray records in the old plan.
398
399 -- Set time to wait
400 conc_log (' ');
401 if (G_TF7_DOWNTIME > G_TF7_MAX_EXTEND_SYNC) then
402 conc_log (' Downtime specified is greater ');
403 G_TF7_EXTEND_SYNC := G_TF7_DOWNTIME;
404 else
405 G_TF7_EXTEND_SYNC := G_TF7_MAX_EXTEND_SYNC;
406 end if;
407
408 conc_log (' Extended Sync process duration (seconds) : ' || G_TF7_EXTEND_SYNC);
409
410 -- We are going to do the extended sync in two iterations. The first
411 -- try is at time T/2 where T is the total extended Sync time. To
412 -- do the sync twice, we loop twice.
413
414 -- Block 1 Start
415 --
416 for counter in 1..2 loop
417
418 -- Wait first for time T/2
419 --Extended_Sync_Wait (G_TF7_EXTEND_SYNC/2, l_return_status);
420
421 -- Null out l_atp_rec
422 l_atp_rec := l_atp_rec1;
423 l_demand_id_arr := l_demand_id_arr_nl;
424
425 -- Initialize the to_call counter
426 l_to_call_ATP := 1;
427
428 conc_log ('Getting Records based on refresh number - first extended sync query');
429
430 l_session_id := -1;
431
432 -- CTO Re-arch, need to get session_id and pass to Get_Records_Post_Collections
433 -- Get Session ID
434 begin
435 select MRP_ATP_SCHEDULE_TEMP_S.NEXTVAL
436 into l_session_id
437 from dual;
438 exception
439 when others then
440 conc_log ('Unable to get a sequence number for ATP Call');
441 conc_log ('Error is : ' || sqlerrm);
442 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
443 end;
444
445 conc_log ('Getting Records based on refresh number with session_id : ' || l_session_id);
446
447 -- Set Session_ID
448 order_sch_wb.set_session_id (l_session_id);
449
450 -- CTO Re-arch changes, added session_id as an IN param, as it will now be retrieved in ATP_Synchronize
451 Get_Records_Post_Collections (
452 p_old_plan_id,
453 p_new_plan_id,
454 l_session_id,
455 l_atp_rec,
456 l_record_count,
457 l_return_status
458 );
459
460 conc_log ('Number of records : ' || l_record_count);
461
462 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) or (l_record_count <= 0) then
463 conc_log ('Nothing to synchronize');
464 l_to_call_ATP := 0;
465 end if;
466
467 if (l_to_call_ATP = 1 ) then
468
469 l_demand_id_arr := l_atp_rec.attribute_03;
470
471 conc_log ('Calling ATP to Synchronize first set of extended sync records');
472 Synchronize_Call_To_ATP ( l_atp_rec,
473 l_session_id,
474 l_total_time,
475 l_return_status
476 );
477
478 if (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
479 ROLLBACK;
480 conc_log ('Something wrong in call to ATP');
481 conc_log ('Stray records do exist, and may not be synchronized');
482 G_TFS_ERROR := 'Error in call to ATP API for Extended Synchronization';
483 G_TFS_ERRCODE := MSC_POST_PRO.G_WARNING;
484 return;
485 end if;
486
487 l_atp_rec.attribute_03 := l_demand_id_arr;
488 Update_Sync_flag ( l_atp_rec,
489 p_old_plan_id,
490 l_return_status
491 );
492
493 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
494 conc_log ('Unable to update sync flag ');
495 -- Commit whatever we have done so far
496 end if;
497
498 -- Commit changes so far
499 commit;
500 end if;
501 end loop;
502 -- Block 1 End
503
504 conc_log ('------***** All Done! *******--------------');
505
506 EXCEPTION
507 when OTHERS then
508 conc_log ('Exception in Atp_synchronize');
509 conc_log ('Error is : ' || sqlerrm);
510
511 G_TFS_ERROR := sqlerrm;
512 G_TFS_ERRCODE := MSC_POST_PRO.G_ERROR;
513 x_return_status := FND_API.G_RET_STS_ERROR;
514
515 END ATP_SYNCHRONIZE;
516
517 ---------------------------------------------------------------------------------
518
519 -- CTO Re-arch changes, changed session_id to an IN param, as it will now be retrieved in ATP_Synchronize
520 PROCEDURE Synchronize_Call_To_ATP (
521 p_atp_table IN MRP_ATP_PUB.ATP_Rec_Typ,
522 p_session_id IN number,
523 x_total_time OUT NOCOPY number,
524 x_return_status OUT NOCOPY varchar2
525 ) IS
526 --PRAGMA AUTONOMOUS_TRANSACTION;
527
528 -- CTO Re-arch, don't need anymore as its passed as IN param.
529 --l_session_id number;
530 l_timer_start number;
531 l_timer_end number;
532 l_atp_table MRP_ATP_PUB.Atp_Rec_Typ;
533 l_atp_supply_demand MRP_ATP_PUB.ATP_Supply_Demand_Typ;
534 l_atp_period MRP_ATP_PUB.ATP_Period_Typ;
535 l_atp_details MRP_ATP_PUB.ATP_Details_Typ;
536 l_return_status varchar2(1);
537 l_msg_data varchar2(100);
538 l_msg_count number;
539 l_instance_id number;
540 l_refresh_number number;
541
542 BEGIN
543
544 conc_log ('---------------Synchronize Call to ATP---------------');
545
546 Print_Input_Rec (p_atp_table);
547
548 -- Initialize
549 x_return_status := FND_API.G_RET_STS_SUCCESS;
550
551 -- CTO Re-arch changes, changed session_id to an IN param, as it will now be retrieved in ATP_Synchronize
552 /*
553 x_session_id := -1;
554 l_session_id := -1;
555
556 -- Get Session ID
557 begin
558 select MRP_ATP_SCHEDULE_TEMP_S.NEXTVAL
559 into l_session_id
560 from dual;
561 exception
562 when others then
563 conc_log ('Unable to get a sequence number for ATP Call');
564 conc_log ('Error is : ' || sqlerrm);
565 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
566 end;
567 x_session_id := l_session_id;
568 conc_log (' Session ID for ATP Call : ' || x_session_id);
569
570 -- Set Session_ID
571 order_sch_wb.set_session_id (x_session_id);
572 */
573
574 -- Get Start Time
575 begin
576 select hsecs / 100
577 into l_timer_start
578 from v$timer;
579 exception
580 when others then
581 conc_log ('Unable to access timer');
582 conc_log ('Error is: ' || sqlerrm);
583 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
584 end;
585
586 l_refresh_number := p_atp_table.attribute_04(1);
587 l_instance_id := NULL;
588
589 -- Now Call ATP
590 begin
591 conc_log ('---------Before calling schedule---------');
592
593 -- CTO Re-arch, modified to use p_session_id instead of x_session_id
594
595 MSC_ATP_PVT.Call_Schedule( p_session_id, -- session ID
596 p_atp_table, -- ATP REC Table
597 l_instance_id, -- instance ID
598 NULL, -- assignment set ID
599 l_refresh_number, -- refresh number
600 l_atp_table, -- output table
601 l_return_status, -- return status
602 l_msg_data, -- msg data
603 l_msg_count, -- msg count
604 l_atp_supply_demand, -- supply demand details
605 l_atp_period, -- period info
606 l_atp_details -- atp details
607 );
608 conc_log ('After calling schedule: l_return_status : ' || l_return_status);
609
610 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
611 conc_log ('Error in call to Call_Schedule');
612 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
613 END IF;
614
615 exception
616 when MSC_ATP_PUB.ATP_INVALID_OBJECTS_FOUND then
617 conc_log ('Invalid Objects found in call to Call_Schedule');
618 conc_log ('Return Status : ' || l_return_status);
619 conc_log ('Error Message : ' || sqlerrm);
620 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
621 when others then
622 conc_log ('Exception in call to Call_Schedule');
623 conc_log ('Return Status : ' || l_return_status);
624 conc_log ('Error Message : ' || sqlerrm);
625 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
626 end;
627
628
629 -- Get End Time
630 begin
631 select hsecs / 100
632 into l_timer_end
633 from v$timer;
634 exception
635 when others then
636 conc_log ('Unable to access timer');
637 conc_log ('Error is: ' || sqlerrm);
638 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
639 end;
640
641 -- Calculate Total Time
642 x_total_time := l_timer_end - l_timer_start;
643
644 if (x_total_time < 0) then
645 x_total_time := G_ATP_TIMER_MAX_VALUE / 100 - l_timer_start + l_timer_end;
646 end if;
647
648 conc_debug ('Total time taken for this ATP call : ' || x_total_time);
649
650
651 EXCEPTION
652 when FND_API.G_EXC_UNEXPECTED_ERROR then
653 conc_log ('Unexpected Error. Sync Call To ATP failed');
654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
655
656 when FND_API.G_EXC_ERROR then
657 conc_log ('Expected Error. Sync Call To ATP failed');
658 x_return_status := FND_API.G_RET_STS_ERROR;
659
660 when others then
661 conc_log ('Exception Others. Sync Call To ATP failed');
662 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
663
664 END Synchronize_Call_To_ATP;
665
666 ---------------------------------------------------------------------------------
667 -- Get records that have been modified (rescheduled) between
668 -- the two plans with a refresh number smaller than the
669 -- collections refresh number
670
671 -- This procedure is not used anymore as 24x7 doesn't process pre-collection records since
672 -- MDS is not supported for 24x7 ATP plans.
673
674 PROCEDURE Get_Records_Pre_Collections (
675 p_old_plan_id IN number,
676 p_new_plan_id IN number,
677 x_atp_rec OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
678 x_record_count OUT NOCOPY number,
679 x_return_status OUT NOCOPY varchar2
680 ) IS
681 BEGIN
682
683 conc_debug ('------------------Get_Records_Pre_Collections------------------');
684 x_return_status := FND_API.G_RET_STS_SUCCESS;
685 x_record_count := 0;
686
687 BEGIN
688 select
689 col1,
690 col2,
691 col3,
692 col4,
693 col5,
694 col6,
695 col7,
696 col8,
697 col9,
698 col10,
699 col11,
700 col12,
701 col13,
702 col14,
703 col15,
704 col16,
705 col17,
706 col18,
707 col19,
708 col20
709 bulk collect into
710 x_atp_rec.calling_module,
711 x_atp_rec.customer_id,
712 x_atp_rec.customer_site_id,
713 x_atp_rec.inventory_item_id,
714 x_atp_rec.source_organization_id,
715 x_atp_rec.quantity_ordered,
716 x_atp_rec.quantity_UOM,
717 x_atp_rec.requested_ship_date,
718 x_atp_rec.demand_class,
719 x_atp_rec.override_flag,
720 x_atp_rec.action,
721 x_atp_rec.instance_id,
722 x_atp_rec.identifier, -- SO Line ID
723 x_atp_rec.substitution_typ_code,
724 x_atp_rec.attribute_04, -- Refresh Number
725 x_atp_rec.delivery_lead_time,
726 x_atp_rec.attribute_08, -- Order Number
727 x_atp_rec.old_source_organization_id,
728 x_atp_rec.old_demand_class,
729 x_atp_rec.attribute_03 -- Temporarily store Demand_ID
730 from (
731 select
732 660 col1,
733 CUST_VIEW.sr_tp_id col2,
734 CUST_SITE_VIEW.sr_tp_site_id col3,
735 msi.sr_inventory_item_id col4,
736 md.organization_id col5,
737 md.using_requirement_quantity col6,
738 msi.uom_code col7,
739 md.using_assembly_demand_date col8,
740 md.demand_class col9,
741 'Y' col10, -- override flag
742 120 col11, -- rescheudle
743 md.sr_instance_id col12,
744 md.sales_order_line_id col13,
745 4 col14,
746 md.refresh_number col15,
747 0 col16,
748 SUBSTR(md.order_number,1,30) col17,
749 md.organization_id col18,
750 md.demand_class col19,
751 md.demand_id col20
752 from msc_demands md,
753 -- Inline view for Customers
754 ( select mtil.sr_instance_id sr_instance_id,
755 mtil.tp_id tp_id,
756 max(mtil.sr_tp_id) sr_tp_id
757 from msc_tp_id_lid mtil
758 where mtil.partner_type = 2
759 and mtil.sr_company_id = -1
760 group by mtil.sr_instance_id, mtil.tp_id
761 ) CUST_VIEW,
762 ( select mtsil.sr_instance_id sr_instance_id,
763 mtsil.tp_site_id tp_site_id,
764 max(mtsil.sr_tp_site_id) sr_tp_site_id
765 from msc_tp_site_id_lid mtsil
766 where mtsil.partner_type = 2
767 and mtsil.sr_company_id = -1
768 group by mtsil.tp_site_id, mtsil.sr_instance_id
769 ) CUST_SITE_VIEW,
770 msc_system_items msi
771 where md.plan_id = p_old_plan_id
772 and md.origination_type in (6,30)
773 and (md.demand_id, md.sr_instance_id) in (
774 (
775 -- Sales orders in old plan not in New plan
776 select max (oldp.demand_id),
777 oldp.sr_instance_id
778 from msc_demands oldp,
779 msc_plan_refreshes mpr
780 where oldp.plan_id = p_old_plan_id
781 and oldp.origination_type in (6,30)
782 and NVL(oldp.atp_synchronization_flag,-1) <> 1
783 and mpr.plan_id = p_new_plan_id
784 and oldp.refresh_number is not NULL
785 and oldp.refresh_number < mpr.apps_lrn
786 and oldp.sr_instance_id = mpr.sr_instance_id
787 and not exists (
788 select sales_order_line_id
789 from msc_demands md1
790 where md1.plan_id = p_new_plan_id
791 and md1.origination_type in (6,30)
792 and md1.sr_instance_id = oldp.sr_instance_id
793 and md1.sales_order_line_id = oldp.sales_order_line_id
794 and md1.using_assembly_item_id = oldp.using_assembly_item_id
795 )
796 group by oldp.sales_order_line_id,
797 oldp.using_assembly_item_id,
798 oldp.sr_instance_id
799 )
800 UNION
801 (
802 -- Demands in both plans but have been rescheduled
803 select max(oldp.demand_id),
804 oldp.sr_instance_id
805 from msc_demands oldp,
806 msc_demands newp
807 where oldp.plan_id = p_old_plan_id
808 and newp.plan_id = p_new_plan_id
809 and oldp.origination_type in (6,30)
810 and newp.origination_type in (6,30)
811 and oldp.sr_instance_id = newp.sr_instance_id
812 and oldp.sales_order_line_id = newp.sales_order_line_id
813 and oldp.using_assembly_item_id = newp.using_assembly_item_id
814 and oldp.demand_id = (
815 select max (md1.demand_id)
816 from msc_demands md1,
817 msc_plan_refreshes mpr1
818 where md1.origination_type in (6,30)
819 and md1.plan_id = p_old_plan_id
820 and md1.sr_instance_id = oldp.sr_instance_id
821 and NVL(md1.atp_synchronization_flag,-1) <> 1
822 and md1.refresh_number is not NULL
823 and md1.refresh_number < mpr1.apps_lrn
824 and md1.sr_instance_id = mpr1.sr_instance_id
825 and mpr1.plan_id = p_new_plan_id
826 and md1.sales_order_line_id = oldp.sales_order_line_id
827 and md1.using_assembly_item_id = oldp.using_assembly_item_id
828 )
829 and newp.demand_id = (
830 -- select demand id for a particular sales order line ID
831 select max (md2.demand_id)
832 from msc_demands md2
833 where md2.origination_type in (6,30)
834 and md2.plan_id = p_new_plan_id
835 and md2.sales_order_line_id = oldp.sales_order_line_id
836 and md2.using_assembly_item_id = oldp.using_assembly_item_id
837 and md2.sr_instance_id = oldp.sr_instance_id
838 )
839 -- Main check to see if order rescheduled goes here.
840 and ( oldp.demand_class <> newp.demand_class
841 or oldp.using_requirement_quantity <> newp.using_requirement_quantity
842 or oldp.organization_id <> newp.organization_id
843
844 or TRUNC ( NVL(oldp.old_demand_date, oldp.using_assembly_demand_date))
845 <>
846 TRUNC( NVL(newp.old_demand_date, newp.using_assembly_demand_date))
847 )
848 group by oldp.sales_order_line_id, oldp.using_assembly_item_id,
849 oldp.sr_instance_id
850 ) -- Union
851 ) -- Demand_ID in
852 and md.customer_id = CUST_VIEW.tp_id (+)
853 and md.sr_instance_id = CUST_VIEW.sr_instance_id (+)
854 and md.ship_to_site_id = CUST_SITE_VIEW.tp_site_id (+)
855 and md.sr_instance_id = CUST_SITE_VIEW.sr_instance_id (+)
856 and md.using_assembly_item_id = msi.inventory_item_id
857 and md.sr_instance_id = msi.sr_instance_id
858 and msi.plan_id = -1
859 and md.organization_id = msi.organization_id
860 and not exists (
861 select demand_source_header_id
862 from msc_sales_orders mso
863 where mso.sr_instance_id = md.sr_instance_id
864 and mso.demand_source_line = to_char(md.sales_order_line_id)
865 and mso.reservation_type = 1
866 and mso.inventory_item_id = md.using_assembly_item_id
867 and mso.completed_quantity > 0
868 )
869 order by md.last_update_date
870 ); --select BULK COLLECT from
871
872 x_record_count := x_atp_rec.calling_module.COUNT;
873
874 if (x_record_count > 0) then
875 conc_debug ('Extending ATP record');
876
877 -- CTO rearch, changed to call New_Extend_ATP to balance ATP_Rec_Typ for all
878 -- new attributes added for CTO re-arch project.
879
880 --extend_atp_rec_typ (x_atp_rec, x_record_count);
881 MSC_SATP_FUNC.New_Extend_Atp(x_atp_rec, x_record_count, x_return_status);
882 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
883 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
884 end if;
885 end if;
886
887 conc_debug ('Records obtained in query : ' || x_record_count);
888 conc_debug ('---------------------------------------------------------');
889
890 EXCEPTION
891 when NO_DATA_FOUND then
892 conc_debug ('Get Records Pre Coll: No data found ');
893 when OTHERS then
894 conc_log ('Get_Records_Pre_Coll: Error while getting records from old plan');
895 conc_log ('Error Is : ' || sqlerrm);
896 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
897 G_TFS_ERROR := sqlerrm;
898 G_TFS_ERRCODE := MSC_POST_PRO.G_ERROR;
899
900 END;
901 END Get_Records_Pre_Collections;
902
903 ---------------------------------------------------------------------------------
904 -- Get records that have been inserted or modified after the start of collections.
905 -- These records will have a refresh number > the refresh number populated by
906 -- collections and suitably transferred by planning to MSC_PLAN_REFRESHES.
907
908 -- CTO Re-arch changes, added session_id as an IN param, as it will now be retrieved in ATP_Synchronize
909 PROCEDURE Get_Records_Post_Collections (
910 p_old_plan_id IN number,
911 p_new_plan_id IN number,
912 p_session_id IN number,
913 x_atp_rec OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
914 x_record_count OUT NOCOPY number,
915 x_return_status OUT NOCOPY varchar2
916 ) IS
917
918 l_sysdate DATE;
919 BEGIN
920
921 conc_debug ('-----------------Get_Records_Post_Collections -----------------');
922 -- Initialize Return Variables
923 x_return_status := FND_API.G_RET_STS_SUCCESS;
924 x_record_count := 0;
925
926 -- Main query
927 BEGIN
928 select
929 col1,
930 col2,
931 col3,
932 col4,
933 col5,
934 col6,
935 col7,
936 col8,
937 col9,
938 col10,
939 col11,
940 col12,
941 col13,
942 col14,
943 col15,
944 col16,
945 col17,
946 col18,
947 col19,
948 col20,
949 -- Plan by request date changes
950 col21,
951 col22,
952 -- CTO Re-arch changes
953 col23,
954 col24,
955 col25,
956 col26,
957 col27, -- ATP Flag passed as Y always.
958 col28, -- Use session id for CTO Re-arch
959 col29, -- Ship Set Name
960 col30, -- Arrival Set Name
961 --col31, -- Insert FLag
962 col31 -- Insert FLag bug3330835
963 bulk collect into
964 x_atp_rec.calling_module,
965 x_atp_rec.customer_id,
966 x_atp_rec.customer_site_id,
967 x_atp_rec.inventory_item_id,
968 x_atp_rec.source_organization_id,
969 x_atp_rec.quantity_ordered,
970 x_atp_rec.quantity_UOM,
971 x_atp_rec.requested_ship_date,
972 x_atp_rec.demand_class,
973 x_atp_rec.override_flag,
974 x_atp_rec.action,
975 x_atp_rec.instance_id,
976 x_atp_rec.identifier, -- SO Line ID
977 x_atp_rec.substitution_typ_code,
978 x_atp_rec.attribute_04, -- Refresh Number
979 x_atp_rec.delivery_lead_time,
980 x_atp_rec.attribute_08, -- Order number
981 x_atp_rec.old_source_organization_id,
982 x_atp_rec.old_demand_class,
983 x_atp_rec.attribute_03, -- demand ID
984 -- Plan by request date changes
985 x_atp_rec.original_request_date, --original request date
986 x_atp_rec.requested_arrival_date,
987 -- CTO Re-arch changes
988 x_atp_rec.ATO_Model_Line_Id,
989 x_atp_rec.Top_Model_line_id,
990 x_atp_rec.ATO_Parent_Model_Line_Id,
991 x_atp_rec.Parent_line_id,
992 x_atp_rec.attribute_06, -- ATP Flag passed as Y always.
993 x_atp_rec.attribute_11, -- Use session id for CTO Re-arch
994 x_atp_rec.ship_set_name, -- Ship Set Name
995 x_atp_rec.arrival_set_name, -- Arrival Set Name
996 x_atp_rec.insert_flag -- Insert Flag
997 from (
998 select
999 660 col1,
1000 NVL(CUST_VIEW.sr_tp_id, -999) col2,
1001 NVL(CUST_SITE_VIEW.sr_tp_site_id, -999) col3,
1002 msi.sr_inventory_item_id col4,
1003 md.organization_id col5,
1004 md.using_requirement_quantity col6,
1005 msi.uom_code col7,
1006 -- md.using_assembly_demand_date col8,
1007 decode(order_date_type_code,
1008 2, NULL,
1009 NVL(md.schedule_ship_date,
1010 md.using_assembly_demand_date)) col8, --plan by request Date, Promise Date Scheduled Date
1011 md.demand_class col9,
1012 'Y' col10, -- override flag
1013 120 col11, -- rescheudle
1014 md.sr_instance_id col12,
1015 md.sales_order_line_id col13,
1016 4 col14,
1017 md.refresh_number col15,
1018 md.intransit_lead_time col16,
1019 --0 col16,
1020 SUBSTR(md.order_number,1,30) col17,
1021 md.organization_id col18,
1022 md.demand_class col19,
1023 md.demand_id col20,
1024 decode(order_date_type_code,
1025 1, md.request_ship_date,
1026 md.request_date) col21, --added so that original request date is not lost
1027 decode(order_date_type_code,
1028 2, md.SCHEDULE_ARRIVAL_DATE,
1029 NULL) col22, --plan by request Date, Promise Date Scheduled Date
1030 -- CTO Re-arch changes
1031 md.ato_line_id col23,
1032 md.top_model_line_id col24,
1033 md.parent_model_line_id col25,
1034 md.link_to_line_id col26,
1035 'Y' col27, -- ATP Flag passed as Y always.
1036 md.atp_session_id col28, -- Use session id for CTO Re-arch
1037 md.ship_set_name col29, -- Ship Set Name
1038 md.arrival_set_name col30, -- Arrival Set Name
1039 0 col31 -- Insert Flag
1040 from msc_demands md,
1041 -- Inline view for Customers
1042 ( select mtil.sr_instance_id sr_instance_id,
1043 mtil.tp_id tp_id,
1044 max(mtil.sr_tp_id) sr_tp_id
1045 from msc_tp_id_lid mtil
1046 where mtil.partner_type = 2
1047 and mtil.sr_company_id = -1
1048 group by mtil.sr_instance_id, mtil.tp_id
1049 ) CUST_VIEW,
1050 ( select mtsil.sr_instance_id sr_instance_id,
1051 mtsil.tp_site_id tp_site_id,
1052 max(mtsil.sr_tp_site_id) sr_tp_site_id
1053 from msc_tp_site_id_lid mtsil
1054 where mtsil.partner_type = 2
1055 and mtsil.sr_company_id = -1
1056 group by mtsil.sr_instance_id, mtsil.tp_site_id
1057 ) CUST_SITE_VIEW,
1058 msc_system_items msi
1059 where md.plan_id = p_old_plan_id
1060 and md.origination_type in (6,30)
1061 and (md.demand_id, md.sr_instance_id) in (
1062 select max (md1.demand_id),
1063 md1.sr_instance_id
1064 from msc_demands md1,
1065 msc_plan_refreshes mpr,
1066 msc_plan_organizations mpo
1067 where md1.plan_id = p_old_plan_id
1068 and md1.origination_type in (6,30)
1069 and mpr.plan_id = p_new_plan_id
1070 and mpr.plan_id = mpo.plan_id
1071 and md1.sr_instance_id = mpo.sr_instance_id
1072 and md1.organization_id = mpo.organization_id
1073 and md1.refresh_number > nvl(mpo.so_lrn, mpr.apps_lrn) -- Verify if mpo.so_lrn will always be populated
1074 and md1.atp_synchronization_flag = 0
1075 -- Removed inventory_item_id from group by to handle product substitution across re-scheduling of SO
1076 --group by md1.sales_order_line_id, md1.inventory_item_id, md1.sr_instance_id
1077 group by md1.sales_order_line_id, md1.sr_instance_id
1078 )
1079 and md.customer_id = CUST_VIEW.tp_id (+)
1080 and md.sr_instance_id = CUST_VIEW.sr_instance_id (+)
1081 and md.ship_to_site_id = CUST_SITE_VIEW.tp_site_id (+)
1082 and md.sr_instance_id = CUST_SITE_VIEW.sr_instance_id (+)
1083 and md.using_assembly_item_id = msi.inventory_item_id
1084 and md.sr_instance_id = msi.sr_instance_id
1085 and msi.plan_id = -1
1086 and md.organization_id = msi.organization_id
1087 order by md.last_update_date
1088 );
1089
1090 x_record_count := x_atp_rec.calling_module.COUNT;
1091
1092 if (x_record_count > 0) then
1093 conc_debug ('Extending ATP record');
1094
1095 -- CTO rearch, changed to call New_Extend_ATP to balance ATP_Rec_Typ for all
1096 -- new attributes added for CTO re-arch project.
1097
1098 --extend_atp_rec_typ (x_atp_rec, x_record_count);
1099 MSC_SATP_FUNC.New_Extend_Atp(x_atp_rec, x_record_count, x_return_status);
1100 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1102 end if;
1103 elsif (x_record_count = 0) then
1104 -- No need to progress further if count = 0
1105 RETURN;
1106 end if; -- if (x_record_count > 0) then
1107
1108 conc_debug ('Records obtained in main query : ' || x_record_count);
1109
1110 -- ngoel, 8/20/2003: Added new SQL for CTO Re-architecture to insert
1111 -- ATO model's components in msc_cto_bom table for processing.
1112
1113 l_sysdate := sysdate;
1114 G_FND_USER := FND_GLOBAL.USER_ID;
1115
1116 -- Select BOM records for ATO Models from msc_cto_bom and re-populate with new session_id
1117 FORALL i in 1..x_record_count
1118 INSERT INTO msc_cto_bom (
1119 inventory_item_id,
1120 line_id,
1121 top_model_line_id,
1122 ato_parent_model_line_id,
1123 ato_model_line_id,
1124 match_item_id,
1125 wip_supply_type,
1126 session_id,
1127 bom_item_type,
1128 quantity,
1129 parent_line_id,
1130 created_by,
1131 creation_date,
1132 last_updated_by,
1133 last_update_date,
1134 sr_instance_id,
1135 sr_inventory_item_id,
1136 refresh_number,
1137 plan_id)
1138 /*
1139 Changed to use msc_cto_bom instead to handle following cases:
1140 1. In case ATP used model's ITF to promise, demands for lower components may not exist.
1141 2. In "BUY" cases, demand for lower components may not exist for entire qty.
1142 */
1143 SELECT distinct
1144 inventory_item_id,
1145 line_id,
1146 top_model_line_id,
1147 ato_parent_model_line_id,
1148 ato_model_line_id,
1149 match_item_id,
1150 wip_supply_type,
1151 p_session_id,
1152 bom_item_type,
1153 quantity,
1154 parent_line_id,
1155 G_FND_USER,
1156 l_sysdate,
1157 G_FND_USER,
1158 l_sysdate,
1159 sr_instance_id,
1160 sr_inventory_item_id,
1161 refresh_number,
1162 NULL -- Plan ID
1163 FROM msc_cto_bom
1164 WHERE ato_model_line_id = x_atp_rec.ATO_Model_Line_Id(i)
1165 AND session_id = x_atp_rec.attribute_11(i)
1166 AND plan_id = p_old_plan_id
1167 AND x_atp_rec.quantity_ordered(i) > 0
1168 AND sr_instance_id = x_atp_rec.instance_id(i);
1169
1170 conc_debug ('No. of ATO Component records inserted: ' || SQL%ROWCOUNT);
1171
1172 -- Select OSS Sourcing records for ATO Models from msc_cto_sources and re-populate with new session_id
1173 FORALL i in 1..x_record_count
1174 INSERT INTO msc_cto_sources (
1175 line_id,
1176 organization_id,
1177 supplier_id,
1178 status_flag,
1179 inventory_item_id,
1180 sr_instance_id,
1181 ato_line_id,
1182 creation_date,
1183 created_by,
1184 last_updated_by,
1185 last_update_date,
1186 supplier_site_code,
1187 make_flag,
1188 refresh_number,
1189 plan_id,
1190 session_id)
1191 SELECT line_id,
1192 organization_id,
1193 supplier_id,
1194 status_flag,
1195 inventory_item_id,
1196 sr_instance_id,
1197 ato_line_id,
1198 l_sysdate,
1199 G_FND_USER,
1200 G_FND_USER,
1201 l_sysdate,
1202 supplier_site_code,
1203 make_flag,
1204 refresh_number,
1205 NULL, -- Plan ID
1206 p_session_id
1207 FROM msc_cto_sources
1208 WHERE ato_line_id = x_atp_rec.ATO_Model_Line_Id(i)
1209 AND session_id = x_atp_rec.attribute_11(i)
1210 AND plan_id = p_old_plan_id
1211 AND x_atp_rec.quantity_ordered(i) > 0
1212 AND sr_instance_id = x_atp_rec.instance_id(i);
1213
1214 conc_debug ('No. of OSS Sourcing records inserted: ' || SQL%ROWCOUNT);
1215
1216 conc_debug ('---------------------------------------------------------');
1217 EXCEPTION
1218 when NO_DATA_FOUND then
1219 conc_debug ('Get Records Post Coll: No data found ');
1220 x_return_status := FND_API.G_RET_STS_ERROR;
1221 when OTHERS then
1222 conc_log ('Get_Records_Post_Coll: Error while getting records from old plan');
1223 conc_log ('Error Is : ' || sqlerrm);
1224 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1225 G_TFS_ERROR := sqlerrm;
1226 G_TFS_ERRCODE := MSC_POST_PRO.G_ERROR;
1227
1228 END;
1229 END Get_Records_Post_Collections;
1230
1231 ---------------------------------------------------------------------------------
1232
1233 PROCEDURE Update_Sync_flag (
1234 p_atp_table IN MRP_ATP_PUB.ATP_Rec_Typ,
1235 p_old_plan_id IN number,
1236 x_return_status OUT NOCOPY varchar2
1237 ) IS
1238
1239 l_counter number;
1240 l_sysdate date := sysdate;
1241
1242 BEGIN
1243 conc_debug ('----Update Sync Flag ----');
1244 conc_debug (' Total Records : ' || p_atp_table.calling_module.count);
1245
1246 x_return_status := FND_API.G_RET_STS_SUCCESS;
1247
1248
1249 conc_debug ('Updating Data at :' || l_sysdate);
1250
1251 forall l_counter in 1..p_atp_table.calling_module.count
1252 update msc_demands
1253 set atp_synchronization_flag = 1,
1254 last_updated_by = G_FND_USER,
1255 last_update_login = G_FND_USER,
1256 last_update_date = l_sysdate
1257 where plan_id = p_old_plan_id
1258 and sr_instance_id = p_atp_table.instance_id(l_counter)
1259 and origination_type = 30
1260 and sales_order_line_id = p_atp_table.identifier(l_counter)
1261 and refresh_number <= p_atp_table.attribute_04(l_counter)
1262 and demand_id <= p_atp_table.attribute_03(l_counter)
1263 and organization_id = p_atp_table.source_organization_id(l_counter)
1264 and NVL(atp_synchronization_flag, -1) <> 1;
1265
1266 conc_debug ('Rows Updated : ' || sql%rowcount);
1267
1268 /* Not Needed as component records aren't selected from msc_demands
1269 -- CTO Re-arch changes, need to update demands for ATO Model's options/ OC's
1270 conc_debug ('Updating CTO Component Demands');
1271
1272 forall l_counter in 1..p_atp_table.calling_module.count
1273 update msc_demands
1274 set atp_synchronization_flag = 1,
1275 last_updated_by = G_FND_USER,
1276 last_update_login = G_FND_USER,
1277 last_update_date = l_sysdate
1278 where plan_id = p_old_plan_id
1279 and sr_instance_id = p_atp_table.instance_id(l_counter)
1280 and ato_line_id = p_atp_table.identifier(l_counter)
1281 and refresh_number <= p_atp_table.attribute_04(l_counter)
1282 and p_atp_table.identifier(l_counter) <> p_atp_table.ato_model_line_id(l_counter)
1283 and NVL(atp_synchronization_flag, -1) <> 1;
1284
1285 conc_debug ('Rows Updated : ' || sql%rowcount);
1286 */
1287
1288 EXCEPTION
1289 when OTHERS then
1290 conc_log ('Error in Update Sync Flag');
1291 conc_log ('Error is : ' || sqlerrm);
1292 x_return_status := FND_API.G_RET_STS_ERROR;
1293 END Update_Sync_flag;
1294
1295 ---------------------------------------------------------------------------------
1296
1297 PROCEDURE ATP_Downtime_Init (
1298 p_old_plan_id IN number,
1299 p_new_plan_id IN number,
1300 x_return_status OUT NOCOPY varchar2
1301 ) IS
1302 BEGIN
1303 conc_debug ('------ATP_Downtime_Init--------');
1304 conc_debug (' Old Plan: ' || p_old_plan_id);
1305
1306 x_return_status := FND_API.G_RET_STS_SUCCESS;
1307 -- Update MSC Plans to null out the date.
1308 /* update msc_plans
1309 set plan_completion_date = NULL
1310 where plan_id = p_old_plan_id;
1311 */
1312 Update_Summary_Flag ( P_new_plan_id,
1313 G_SF_SYNC_DOWNTIME,
1314 x_return_status
1315 );
1316 conc_debug ('-------------------------------');
1317
1318 EXCEPTION
1319 when others then
1320 conc_log ('Error in Downtime INIT');
1321 conc_log ('Error is : ' || sqlerrm);
1322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1323
1324 END ATP_Downtime_Init;
1325
1326 ---------------------------------------------------------------------------------
1327
1328 PROCEDURE ATP_Downtime_Complete (
1329 p_old_plan_id IN number,
1330 p_new_plan_id IN number,
1331 x_return_status OUT NOCOPY varchar2
1332 ) IS
1333 l_request_id number;
1334 l_call_status boolean;
1335 l_phase varchar2(80);
1336 l_status varchar2(80);
1337 l_dev_phase varchar2(80);
1338 l_dev_status varchar2(80);
1339 l_message varchar2(240);
1340
1341 BEGIN
1342
1343 conc_log ('ATP Downtime Complete');
1344 -- CALL UI API HERE
1345 BEGIN
1346 l_request_id := FND_REQUEST.SUBMIT_REQUEST (
1347 'MSC',
1348 'MSCSWPLN',
1349 NULL,
1350 NULL,
1351 FALSE,
1352 p_old_plan_id,
1353 p_new_plan_id
1354 );
1355 commit;
1356 EXCEPTION
1357 when OTHERS then
1358 conc_log ('Concurrent program error. Code : ' || sqlcode);
1359 conc_log (' Error : ' || sqlerrm);
1360 x_return_status := FND_API.G_RET_STS_ERROR;
1361 G_TFS_ERROR := 'Unable to switch plans';
1362 G_TFS_ERRCODE := MSC_POST_PRO.G_ERROR;
1363 return;
1364 END;
1365
1366 conc_log ('Switch Plans launched with request ID ' || l_request_id);
1367 l_call_status := FND_CONCURRENT.WAIT_FOR_REQUEST (
1368 l_request_id,
1369 10,
1370 10000000,
1371 l_phase,
1372 l_status,
1373 l_dev_phase,
1374 l_dev_status,
1375 l_message );
1376
1377 conc_debug ('Returned from Wait For Request');
1378 conc_debug ('Phase : ' || l_dev_phase || ' Status : ' || l_dev_status );
1379
1380 if (l_dev_status <> 'NORMAL') then
1381 conc_log ('Switch plans returned with ' || l_dev_status);
1382 x_return_status := FND_API.G_RET_STS_ERROR;
1383 G_TFS_ERROR := 'Unable to switch plans - program returned with error';
1384 G_TFS_ERRCODE := MSC_POST_PRO.G_ERROR;
1385
1386 return;
1387 end if;
1388 -- Call Refresh Snapshot
1389
1390 conc_log ('Updating Summary flag');
1391 Update_Summary_Flag (p_new_plan_id, G_SF_SYNC_SUCCESS, x_return_status);
1392
1393 conc_log ('Launching Analyze Plan Partition');
1394
1395 -- Bug 2809606 : Will refresh snapshot directly, instead of launching ANAPP
1396 /*
1397 BEGIN
1398 l_request_id := FND_REQUEST.SUBMIT_REQUEST
1399 (
1400 'MSC',
1401 'MSCANAPP',
1402 NULL,
1403 NULL,
1404 FALSE,
1405 0
1406 );
1407 commit;
1408
1409 conc_log ('Analyze plan_partition launched with request ID ' || l_request_id);
1410 EXCEPTION
1411 when OTHERS then
1412 conc_log ('Concurrent program error. Code : ' || sqlcode);
1413 conc_log (' Error : ' || sqlerrm);
1414 END;
1415
1416 -- Wait for Concurrent request to complete
1417 l_call_status := FND_CONCURRENT.WAIT_FOR_REQUEST (
1418 l_request_id,
1419 10,
1420 10000000,
1421 l_phase,
1422 l_status,
1423 l_dev_phase,
1424 l_dev_status,
1425 l_message );
1426
1427 conc_debug ('Returned from Wait For Analyze plan partition request Request');
1428 conc_debug ('Phase : ' || l_dev_phase || ' Status : ' || l_dev_status );
1429
1430 if (l_dev_status <> 'NORMAL') then
1431 conc_log ('Analyze plan partition returned with ' || l_dev_status);
1432 x_return_status := FND_API.G_RET_STS_ERROR;
1433 return;
1434 end if;
1435 */
1436
1437 Refresh_Snapshot(x_return_status);
1438
1439
1440 END ATP_Downtime_Complete;
1441
1442 ---------------------------------------------------------------------------------
1443
1444 PROCEDURE ATP_Sync_Error (
1445 p_old_plan_id IN number,
1446 p_new_plan_id IN number
1447 ) IS
1448 l_return_status varchar2(1);
1449
1450 BEGIN
1451 conc_log ('Sync Error: Unable to continue further');
1452 Update_Summary_Flag ( p_new_plan_id,
1453 G_SF_SYNC_ERROR,
1454 l_return_status
1455 );
1456 conc_log ('ATP Status Flag Update : ' || l_return_status);
1457
1458 END ATP_Sync_Error;
1459 ---------------------------------------------------------------------------------
1460
1461 -- Synchronization Downtime Calculation
1462 -- Values stored in Global variables defined in spec.
1463 -- The average records per unit time is calculated, and
1464 -- the total number of Sales orders that can be processed
1465 -- during this time is computed. The total number of records
1466 -- that can be processed during the synchronization downtime
1467 -- is then computed and stored.
1468 PROCEDURE Calculate_Downtime_SO_Records (
1469 p_number_of_records IN number,
1470 p_total_time IN number
1471 ) IS
1472
1473 l_time_per_record number;
1474
1475 BEGIN
1476
1477 conc_debug ('------Calculate_Downtime_SO_Records ------');
1478 conc_debug ('Time : ' || p_total_time || ' Orders : ' || p_number_of_records );
1479 conc_debug ('Current threshold : ' || G_TF7_SO_THRESHOLD);
1480
1481 G_TF7_TOTAL_RECORDS := G_TF7_TOTAL_RECORDS + p_number_of_records;
1482 G_TF7_TOTAL_TIME := G_TF7_TOTAL_TIME + p_total_time;
1483
1484 if (G_TF7_TOTAL_RECORDS = 0) then
1485 G_TF7_SO_THRESHOLD := 0;
1486 return;
1487 end if;
1488 l_time_per_record := G_TF7_TOTAL_TIME / G_TF7_TOTAL_RECORDS ;
1489
1490 if (l_time_per_record = 0) then
1491 G_TF7_SO_THRESHOLD := 0;
1492 return;
1493 end if;
1494 G_TF7_SO_THRESHOLD := G_TF7_DOWNTIME / l_time_per_record;
1495
1496 conc_debug ('New threshold SO records: ' || G_TF7_SO_THRESHOLD);
1497
1498 END Calculate_Downtime_SO_Records;
1499
1500 ---------------------------------------------------------------------------------
1501
1502 -- Function to update the summary flag for a plan.
1503 PROCEDURE Update_Summary_Flag (
1504 p_plan_id IN number,
1505 p_status IN number,
1506 x_return_status OUT NOCOPY varchar2
1507 ) IS
1508 BEGIN
1509 conc_debug ('------- Update_Summary_Flag -----');
1510 conc_debug (' Plan ID : '|| p_plan_id || ' Status : ' || p_status);
1511
1512 x_return_status := FND_API.G_RET_STS_SUCCESS;
1513
1514 update msc_plans
1515 set summary_flag = p_status
1516 where plan_id = p_plan_id;
1517
1518 EXCEPTION
1519 WHEN OTHERS THEN
1520 conc_debug ('Cannot Update. Error : ' || sqlerrm);
1521 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1522
1523 END Update_Summary_Flag;
1524
1525 ---------------------------------------------------------------------------------
1526
1527 PROCEDURE conc_log (buf IN VARCHAR2)
1528 IS
1529 BEGIN
1530 msc_util.msc_log (buf);
1531 --msc_sch_wb.mrp_timing (buf);
1532 --dbms_output.put_line (buf);
1533
1534 END conc_log;
1535 ---------------------------------------------------------------------------------
1536
1537 PROCEDURE conc_debug (buf IN VARCHAR2)
1538 IS
1539 BEGIN
1540 -- Check value for profile option
1541 --if (atp_debug_flag in ('Y','C')) then
1542 conc_log (buf);
1543 --end if;
1544
1545 END conc_debug;
1546 ---------------------------------------------------------------------------------
1547
1548 -- CTO Re-arch changes, this procedure will not be used anymore, instead MSC_SATP_FUNC.New_Extend_Atp is used.
1549 PROCEDURE extend_atp_rec_typ (
1550 p_atp_tab IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
1551 p_size IN number
1552 ) IS
1553 BEGIN
1554
1555 p_atp_tab.Row_ID.Extend(p_size);
1556 p_atp_tab.Inventory_Item_Name.Extend(p_size);
1557 p_atp_tab.Organization_Id.Extend(p_size);
1558 p_atp_tab.Source_Organization_Code.Extend(p_size);
1559 p_atp_tab.Demand_Source_Delivery.Extend(p_size);
1560 p_atp_tab.Demand_Source_Type.Extend(p_size);
1561 p_atp_tab.Demand_Source_Header_id.Extend(p_size);
1562 p_atp_tab.Scenario_Id.Extend(p_size);
1563 p_atp_tab.Order_Number.Extend(p_size);
1564 p_atp_tab.Destination_Time_Zone.Extend(p_size);
1565 p_atp_tab.Requested_Arrival_Date.Extend(p_size);
1566 p_atp_tab.Earliest_Acceptable_Date.Extend(p_size);
1567 p_atp_tab.Latest_Acceptable_Date.Extend(p_size);
1568 p_atp_tab.Freight_Carrier.Extend(p_size);
1569 p_atp_tab.Ship_Method.Extend(p_size);
1570 p_atp_tab.Ship_Set_Name.Extend(p_size);
1571 p_atp_tab.Arrival_Set_Name.Extend(p_size);
1572 p_atp_tab.Ship_Date.Extend(p_size);
1573 p_atp_tab.Arrival_Date.Extend(p_size);
1574 p_atp_tab.Available_Quantity.Extend(p_size);
1575 p_atp_tab.Requested_Date_Quantity.Extend(p_size);
1576 p_atp_tab.Group_Ship_Date.Extend(p_size);
1577 p_atp_tab.Group_Arrival_Date.Extend(p_size);
1578 p_atp_tab.Vendor_Id.Extend(p_size);
1579 p_atp_tab.Vendor_Name.Extend(p_size);
1580 p_atp_tab.Vendor_Site_Id.Extend(p_size);
1581 p_atp_tab.Vendor_Site_Name.Extend(p_size);
1582 p_atp_tab.Insert_Flag.Extend(p_size);
1583 p_atp_tab.OE_Flag.Extend(p_size);
1584 p_atp_tab.Error_Code.Extend(p_size);
1585 p_atp_tab.Atp_Lead_Time.Extend(p_size);
1586 p_atp_tab.Message.Extend(p_size);
1587 p_atp_tab.End_Pegging_Id.Extend(p_size);
1588 p_atp_tab.Old_Source_Organization_Id.Extend(p_size);
1589 p_atp_tab.Old_Demand_Class.Extend(p_size);
1590 p_atp_tab.ato_delete_flag.Extend(p_size);
1591 p_atp_tab.attribute_05.Extend(p_size);
1592 p_atp_tab.attribute_06.Extend(p_size);
1593 p_atp_tab.attribute_07.Extend(p_size);
1594 p_atp_tab.attribute_01.Extend(p_size);
1595 p_atp_tab.customer_name.Extend(p_size);
1596 p_atp_tab.customer_class.Extend(p_size);
1597 p_atp_tab.customer_location.Extend(p_size);
1598 p_atp_tab.customer_country.Extend(p_size);
1599 p_atp_tab.customer_state.Extend(p_size);
1600 p_atp_tab.customer_city.Extend(p_size);
1601 p_atp_tab.customer_postal_code.Extend(p_size);
1602 p_atp_tab.req_item_detail_flag.Extend(p_size);
1603 p_atp_tab.request_item_id.Extend(p_size);
1604 p_atp_tab.req_item_req_date_qty.Extend(p_size);
1605 p_atp_tab.req_item_available_date.Extend(p_size);
1606 p_atp_tab.req_item_available_date_qty.Extend(p_size);
1607 p_atp_tab.request_item_name.Extend(p_size);
1608 p_atp_tab.old_inventory_item_id.Extend(p_size);
1609 p_atp_tab.sales_rep.Extend(p_size);
1610 p_atp_tab.customer_contact.Extend(p_size);
1611 p_atp_tab.subst_flag.Extend(p_size);
1612 p_atp_tab.party_site_id.Extend(p_size); --2814895
1613 p_atp_tab.part_of_set.Extend(p_size); --4500382
1614
1615
1616
1617 END extend_atp_rec_typ;
1618 ---------------------------------------------------------------------------------
1619
1620 PROCEDURE Get_Instance_Refresh_Number (
1621 p_plan_id IN number,
1622 p_instance_id IN number,
1623 x_refresh_number OUT NOCOPY number
1624 ) IS
1625
1626 BEGIN
1627 conc_debug ('------Get_Instance_Refresh_Number -------');
1628 conc_debug ('Plan ID : ' || p_plan_id);
1629 conc_debug ('Instance_ID : ' || p_instance_id );
1630
1631 x_refresh_number := -1;
1632
1633 select apps_lrn
1634 into x_refresh_number
1635 from msc_plan_refreshes
1636 where plan_id = p_plan_id
1637 and sr_instance_id = p_instance_id;
1638
1639 conc_debug ('Refresh Number : ' || x_refresh_number);
1640
1641 EXCEPTION
1642
1643 when NO_DATA_FOUND then
1644 conc_debug ('No data found for query. Returning -1');
1645 x_refresh_number := -1;
1646
1647 when OTHERS then
1648 conc_debug ('Exception: Others. Returning -1 ');
1649 conc_debug ('Error Msg : ' || sqlerrm);
1650 x_refresh_number := -1;
1651
1652 END Get_Instance_Refresh_Number;
1653 ---------------------------------------------------------------------------------
1654
1655 PROCEDURE Extended_Sync_Wait (
1656 l_time IN number,
1657 x_return_status OUT NOCOPY varchar2
1658 ) IS
1659 BEGIN
1660 conc_log (' Sleeping for ' || l_time || ' seconds');
1661 DBMS_LOCK.SLEEP(l_time);
1662 conc_log (' Back from sleep ');
1663 END Extended_Sync_Wait;
1664 ---------------------------------------------------------------------------------
1665
1666 PROCEDURE Print_Input_Rec (
1667 x_atp_rec IN MRP_ATP_PUB.ATP_Rec_Typ
1668 ) IS
1669
1670 l_counter number;
1671
1672 BEGIN
1673
1674 conc_log (' ----Printing Input Record ---- ');
1675
1676 for l_counter in 1..x_atp_rec.calling_module.count loop
1677
1678 conc_log (l_counter || '. ' ||
1679 ' Dmd ID : ' || x_atp_rec.attribute_03(l_counter) ||
1680 ' Inst : ' || x_atp_rec.instance_id (l_counter) ||
1681 ' SO Line ID : ' || x_atp_rec.identifier(l_counter) ||
1682 ' Inv Item : ' || x_atp_rec.inventory_item_id(l_counter) ||
1683 ' Qty : ' || x_atp_rec.quantity_ordered(l_counter) ||
1684 ' Date : ' || x_atp_rec.requested_ship_date(l_counter) ||
1685 ' Cust : ' || x_atp_rec.customer_id(l_counter) ||
1686 ' Cst St ID: ' || x_atp_rec.customer_site_id(l_counter) ||
1687 ' Refsh No : ' || x_atp_rec.attribute_04(l_counter) ||
1688 ' Ordr No : ' || x_atp_rec.attribute_08(l_counter) ||
1689 ' ATO Line ID : ' || x_atp_rec.ato_model_line_id(l_counter) ||
1690 ' Session ID : ' || x_atp_rec.attribute_11(l_counter) ||
1691 ' .');
1692
1693 end loop;
1694
1695 conc_log (' ----Done Printing Records ---- ');
1696
1697 END Print_Input_Rec;
1698 ---------------------------------------------------------------------------------
1699
1700 PROCEDURE Refresh_Snapshot (
1701 x_return_status OUT NOCOPY varchar2
1702 ) IS
1703
1704 lv_msc_schema VARCHAR2(30);
1705 v_snap_exist number;
1706
1707 Cursor msc_schema IS
1708 SELECT a.oracle_username
1709 FROM FND_ORACLE_USERID a, FND_PRODUCT_INSTALLATIONS b
1710 WHERE a.oracle_id = b.oracle_id
1711 AND b.application_id= 724;
1712
1713 Cursor atp_snap IS
1714 SELECT 1
1715 FROM all_objects
1716 WHERE object_name like 'MSC_ATP_PLAN_SN'
1717 AND owner = lv_msc_schema;
1718
1719 BEGIN
1720
1721 conc_log ('------- Refresh ATP Plan SN ----------');
1722
1723 OPEN msc_schema;
1724 FETCH msc_schema INTO lv_msc_schema;
1725 CLOSE msc_schema;
1726
1727 OPEN atp_snap;
1728 FETCH atp_snap INTO v_snap_exist;
1729 CLOSE atp_snap;
1730
1731 -- refresh the snapshot if it exists
1732 if v_snap_exist =1 then
1733 conc_log ('Complete Refresh of Snapshot Started');
1734 DBMS_SNAPSHOT.REFRESH( lv_msc_schema||'.MSC_ATP_PLAN_SN', 'C');
1735 conc_log ('Refresh Complete');
1736 end if;
1737
1738 x_return_status := FND_API.G_RET_STS_SUCCESS;
1739
1740 EXCEPTION
1741
1742 WHEN OTHERS THEN
1743 conc_log ('Exception in Refresh Snapshot');
1744 conc_log ('SqlCode : ' || sqlcode);
1745 conc_log ('Sql error MSG : ' || sqlerrm);
1746 x_return_status := FND_API.G_RET_STS_ERROR;
1747
1748 END Refresh_Snapshot;
1749 ---------------------------------------------------------------------------------
1750
1751 PROCEDURE Parse_Sales_Order_Number (
1752 p_order_number_string IN varchar2,
1753 p_order_number IN OUT NOCOPY number
1754 ) IS
1755
1756 l_end number;
1757 l_counter number;
1758 l_max_counter number;
1759 l_char varchar2(1);
1760 l_num_char number;
1761
1762 BEGIN
1763 msc_sch_wb.atp_debug ('------Begin Parse_Sales_Order_string-----');
1764 msc_sch_wb.atp_debug ('');
1765 msc_sch_wb.atp_debug ('String : ' || p_order_number_string);
1766 msc_sch_wb.atp_debug ('Number : ' || p_order_number);
1767
1768 BEGIN
1769 p_order_number := to_number (p_order_number_string);
1770 msc_sch_wb.atp_debug ('Order number is : ' || p_order_number);
1771 return;
1772 EXCEPTION
1773 WHEN others then
1774 msc_sch_wb.atp_debug ('Order number has to be processed');
1775 END;
1776
1777 l_end := 0;
1778 l_counter := 0;
1779 l_max_counter := length(p_order_number_string);
1780
1781 msc_sch_wb.atp_debug ('Parse SO: Length of Order Number String : '|| l_max_counter);
1782
1783 if (l_max_counter = 0) then
1784 msc_sch_wb.atp_debug ('String is null' );
1785 return;
1786 end if;
1787
1788 while (l_end <> 1) loop
1789
1790 l_counter := l_counter + 1;
1791 if (l_counter > l_max_counter ) then
1792 l_end := 1;
1793 msc_sch_wb.atp_debug ('Parse SO: Max Length reached');
1794 exit;
1795 end if;
1796
1797 l_char := substr (p_order_number_string, l_counter, 1);
1798 BEGIN
1799 l_num_char := to_number (l_char);
1800 EXCEPTION
1801 WHEN OTHERS then
1802 msc_sch_wb.atp_debug ('Parse SO: String end detected');
1803 l_end := 1;
1804 END;
1805 end loop;
1806
1807 if (l_counter > l_max_counter) then
1808 msc_sch_wb.atp_debug ('Order number string is a number');
1809
1810 BEGIN
1811 p_order_number := to_number (p_order_number_string);
1812 EXCEPTION
1813 WHEN others then
1814 msc_sch_wb.atp_debug ('Parse SO: Something wrong. Should not be here');
1815 return;
1816 END;
1817
1818 elsif (l_counter = 1) then
1819 msc_sch_wb.atp_debug ('Order Number not properly formatted.');
1820 else
1821 msc_sch_wb.atp_debug ('Parse SO: Counter is at ' || l_counter);
1822 BEGIN
1823 p_order_number := to_number (substr (p_order_number_string, 1,l_counter -1));
1824 EXCEPTION
1825 WHEN others then
1826 msc_sch_wb.atp_debug ('Parse SO: Something wrong. Should not be here');
1827 return;
1828 END;
1829 end if;
1830
1831 msc_sch_wb.atp_debug ('Order number is : ' || p_order_number);
1832
1833 EXCEPTION
1834 WHEN others THEN
1835 msc_sch_wb.atp_debug ('Something wrong in Parse Sales Order');
1836
1837 END Parse_Sales_Order_Number;
1838
1839 -----------------------------------
1840
1841
1842 END MSC_ATP_24x7;