[Home] [Help]
PACKAGE BODY: APPS.MSC_ATP_24X7
Source
1 PACKAGE BODY MSC_ATP_24x7 AS
2 /* $Header: MSCTATPB.pls 120.7 2011/10/27 12:01:00 vjuluri 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 col32 -- bug 8473835
964 bulk collect into
965 x_atp_rec.calling_module,
966 x_atp_rec.customer_id,
967 x_atp_rec.customer_site_id,
968 x_atp_rec.inventory_item_id,
969 x_atp_rec.source_organization_id,
970 x_atp_rec.quantity_ordered,
971 x_atp_rec.quantity_UOM,
972 x_atp_rec.requested_ship_date,
973 x_atp_rec.demand_class,
974 x_atp_rec.override_flag,
975 x_atp_rec.action,
976 x_atp_rec.instance_id,
977 x_atp_rec.identifier, -- SO Line ID
978 x_atp_rec.substitution_typ_code,
979 x_atp_rec.attribute_04, -- Refresh Number
980 x_atp_rec.delivery_lead_time,
981 x_atp_rec.attribute_08, -- Order number
982 x_atp_rec.old_source_organization_id,
983 x_atp_rec.old_demand_class,
984 x_atp_rec.attribute_03, -- demand ID
985 -- Plan by request date changes
986 x_atp_rec.original_request_date, --original request date
987 x_atp_rec.requested_arrival_date,
988 -- CTO Re-arch changes
989 x_atp_rec.ATO_Model_Line_Id,
990 x_atp_rec.Top_Model_line_id,
991 x_atp_rec.ATO_Parent_Model_Line_Id,
992 x_atp_rec.Parent_line_id,
993 x_atp_rec.attribute_06, -- ATP Flag passed as Y always.
994 x_atp_rec.attribute_11, -- Use session id for CTO Re-arch
995 x_atp_rec.ship_set_name, -- Ship Set Name
996 x_atp_rec.arrival_set_name, -- Arrival Set Name
997 x_atp_rec.insert_flag, -- Insert Flag
998 x_atp_rec.demand_source_type -- bug 8473835
999 from (
1000 select
1001 660 col1,
1002 NVL(CUST_VIEW.sr_tp_id, -999) col2,
1003 NVL(CUST_SITE_VIEW.sr_tp_site_id, -999) col3,
1004 msi.sr_inventory_item_id col4,
1005 md.organization_id col5,
1006 md.using_requirement_quantity col6,
1007 msi.uom_code col7,
1008 -- md.using_assembly_demand_date col8,
1009 decode(order_date_type_code,
1010 2, NULL,
1011 NVL(md.schedule_ship_date,
1012 md.using_assembly_demand_date)) col8, --plan by request Date, Promise Date Scheduled Date
1013 md.demand_class col9,
1014 'Y' col10, -- override flag
1015 120 col11, -- rescheudle
1016 md.sr_instance_id col12,
1017 md.sales_order_line_id col13,
1018 4 col14,
1019 md.refresh_number col15,
1020 md.intransit_lead_time col16,
1021 --0 col16,
1022 SUBSTR(md.order_number,1,30) col17,
1023 md.organization_id col18,
1024 md.demand_class col19,
1025 md.demand_id col20,
1026 decode(order_date_type_code,
1027 1, md.request_ship_date,
1028 md.request_date) col21, --added so that original request date is not lost
1029 decode(order_date_type_code,
1030 2, md.SCHEDULE_ARRIVAL_DATE,
1031 NULL) col22, --plan by request Date, Promise Date Scheduled Date
1032 -- CTO Re-arch changes
1033 md.ato_line_id col23,
1034 md.top_model_line_id col24,
1035 md.parent_model_line_id col25,
1036 md.link_to_line_id col26,
1037 'Y' col27, -- ATP Flag passed as Y always.
1038 md.atp_session_id col28, -- Use session id for CTO Re-arch
1039 md.ship_set_name col29, -- Ship Set Name
1040 md.arrival_set_name col30, -- Arrival Set Name
1041 0 col31, -- Insert Flag
1042 md.demand_source_type col32 -- bug 8473835
1043 from msc_demands md,
1044 -- Inline view for Customers
1045 ( select mtil.sr_instance_id sr_instance_id,
1046 mtil.tp_id tp_id,
1047 max(mtil.sr_tp_id) sr_tp_id
1048 from msc_tp_id_lid mtil
1049 where mtil.partner_type = 2
1050 and mtil.sr_company_id = -1
1051 group by mtil.sr_instance_id, mtil.tp_id
1052 ) CUST_VIEW,
1053 ( select mtsil.sr_instance_id sr_instance_id,
1054 mtsil.tp_site_id tp_site_id,
1055 max(mtsil.sr_tp_site_id) sr_tp_site_id
1056 from msc_tp_site_id_lid mtsil
1057 where mtsil.partner_type = 2
1058 and mtsil.sr_company_id = -1
1059 group by mtsil.sr_instance_id, mtsil.tp_site_id
1060 ) CUST_SITE_VIEW,
1061 msc_system_items msi
1062 where md.plan_id = p_old_plan_id
1063 and md.origination_type in (6,30)
1064 and (md.demand_id, md.sr_instance_id) in (
1065 select max (md1.demand_id),
1066 md1.sr_instance_id
1067 from msc_demands md1,
1068 msc_plan_refreshes mpr,
1069 msc_plan_organizations mpo
1070 where md1.plan_id = p_old_plan_id
1071 and md1.origination_type in (6,30)
1072 and mpr.plan_id = p_new_plan_id
1073 and mpr.plan_id = mpo.plan_id
1074 and md1.sr_instance_id = mpo.sr_instance_id
1075 and md1.organization_id = mpo.organization_id
1076 and md1.refresh_number > nvl(mpo.so_lrn, mpr.apps_lrn) -- Verify if mpo.so_lrn will always be populated
1077 and md1.atp_synchronization_flag = 0
1078 -- Removed inventory_item_id from group by to handle product substitution across re-scheduling of SO
1079 --group by md1.sales_order_line_id, md1.inventory_item_id, md1.sr_instance_id
1080 group by md1.sales_order_line_id, md1.sr_instance_id
1081 )
1082 and md.customer_id = CUST_VIEW.tp_id (+)
1083 and md.sr_instance_id = CUST_VIEW.sr_instance_id (+)
1084 and md.ship_to_site_id = CUST_SITE_VIEW.tp_site_id (+)
1085 and md.sr_instance_id = CUST_SITE_VIEW.sr_instance_id (+)
1086 and md.using_assembly_item_id = msi.inventory_item_id
1087 and md.sr_instance_id = msi.sr_instance_id
1088 and msi.plan_id = -1
1089 and md.organization_id = msi.organization_id
1090 order by md.last_update_date
1091 );
1092
1093 x_record_count := x_atp_rec.calling_module.COUNT;
1094
1095 if (x_record_count > 0) then
1096 conc_debug ('Extending ATP record');
1097
1098 -- CTO rearch, changed to call New_Extend_ATP to balance ATP_Rec_Typ for all
1099 -- new attributes added for CTO re-arch project.
1100
1101 --extend_atp_rec_typ (x_atp_rec, x_record_count);
1102 MSC_SATP_FUNC.New_Extend_Atp(x_atp_rec, x_record_count, x_return_status);
1103 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1105 end if;
1106 elsif (x_record_count = 0) then
1107 -- No need to progress further if count = 0
1108 RETURN;
1109 end if; -- if (x_record_count > 0) then
1110
1111 conc_debug ('Records obtained in main query : ' || x_record_count);
1112
1113 -- ngoel, 8/20/2003: Added new SQL for CTO Re-architecture to insert
1114 -- ATO model's components in msc_cto_bom table for processing.
1115
1116 l_sysdate := sysdate;
1117 G_FND_USER := FND_GLOBAL.USER_ID;
1118
1119 -- Select BOM records for ATO Models from msc_cto_bom and re-populate with new session_id
1120 FORALL i in 1..x_record_count
1121 INSERT INTO msc_cto_bom (
1122 inventory_item_id,
1123 line_id,
1124 top_model_line_id,
1125 ato_parent_model_line_id,
1126 ato_model_line_id,
1127 match_item_id,
1128 wip_supply_type,
1129 session_id,
1130 bom_item_type,
1131 quantity,
1132 parent_line_id,
1133 created_by,
1134 creation_date,
1135 last_updated_by,
1136 last_update_date,
1137 sr_instance_id,
1138 sr_inventory_item_id,
1139 refresh_number,
1140 plan_id)
1141 /*
1142 Changed to use msc_cto_bom instead to handle following cases:
1143 1. In case ATP used model's ITF to promise, demands for lower components may not exist.
1144 2. In "BUY" cases, demand for lower components may not exist for entire qty.
1145 */
1146 SELECT distinct
1147 inventory_item_id,
1148 line_id,
1149 top_model_line_id,
1150 ato_parent_model_line_id,
1151 ato_model_line_id,
1152 match_item_id,
1153 wip_supply_type,
1154 p_session_id,
1155 bom_item_type,
1156 quantity,
1157 parent_line_id,
1158 G_FND_USER,
1159 l_sysdate,
1160 G_FND_USER,
1161 l_sysdate,
1162 sr_instance_id,
1163 sr_inventory_item_id,
1164 refresh_number,
1165 NULL -- Plan ID
1166 FROM msc_cto_bom
1167 WHERE ato_model_line_id = x_atp_rec.ATO_Model_Line_Id(i)
1168 AND session_id = x_atp_rec.attribute_11(i)
1169 AND plan_id = p_old_plan_id
1170 AND x_atp_rec.quantity_ordered(i) > 0
1171 AND sr_instance_id = x_atp_rec.instance_id(i);
1172
1173 conc_debug ('No. of ATO Component records inserted: ' || SQL%ROWCOUNT);
1174
1175 -- Select OSS Sourcing records for ATO Models from msc_cto_sources and re-populate with new session_id
1176 FORALL i in 1..x_record_count
1177 INSERT INTO msc_cto_sources (
1178 line_id,
1179 organization_id,
1180 supplier_id,
1181 status_flag,
1182 inventory_item_id,
1183 sr_instance_id,
1184 ato_line_id,
1185 creation_date,
1186 created_by,
1187 last_updated_by,
1188 last_update_date,
1189 supplier_site_code,
1190 make_flag,
1191 refresh_number,
1192 plan_id,
1193 session_id)
1194 SELECT line_id,
1195 organization_id,
1196 supplier_id,
1197 status_flag,
1198 inventory_item_id,
1199 sr_instance_id,
1200 ato_line_id,
1201 l_sysdate,
1202 G_FND_USER,
1203 G_FND_USER,
1204 l_sysdate,
1205 supplier_site_code,
1206 make_flag,
1207 refresh_number,
1208 NULL, -- Plan ID
1209 p_session_id
1210 FROM msc_cto_sources
1211 WHERE ato_line_id = x_atp_rec.ATO_Model_Line_Id(i)
1212 AND session_id = x_atp_rec.attribute_11(i)
1213 AND plan_id = p_old_plan_id
1214 AND x_atp_rec.quantity_ordered(i) > 0
1215 AND sr_instance_id = x_atp_rec.instance_id(i);
1216
1217 conc_debug ('No. of OSS Sourcing records inserted: ' || SQL%ROWCOUNT);
1218
1219 conc_debug ('---------------------------------------------------------');
1220 EXCEPTION
1221 when NO_DATA_FOUND then
1222 conc_debug ('Get Records Post Coll: No data found ');
1223 x_return_status := FND_API.G_RET_STS_ERROR;
1224 when OTHERS then
1225 conc_log ('Get_Records_Post_Coll: Error while getting records from old plan');
1226 conc_log ('Error Is : ' || sqlerrm);
1227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1228 G_TFS_ERROR := sqlerrm;
1229 G_TFS_ERRCODE := MSC_POST_PRO.G_ERROR;
1230
1231 END;
1232 END Get_Records_Post_Collections;
1233
1234 ---------------------------------------------------------------------------------
1235
1236 PROCEDURE Update_Sync_flag (
1237 p_atp_table IN MRP_ATP_PUB.ATP_Rec_Typ,
1238 p_old_plan_id IN number,
1239 x_return_status OUT NOCOPY varchar2
1240 ) IS
1241
1242 l_counter number;
1243 l_sysdate date := sysdate;
1244
1245 BEGIN
1246 conc_debug ('----Update Sync Flag ----');
1247 conc_debug (' Total Records : ' || p_atp_table.calling_module.count);
1248
1249 x_return_status := FND_API.G_RET_STS_SUCCESS;
1250
1251
1252 conc_debug ('Updating Data at :' || l_sysdate);
1253
1254 forall l_counter in 1..p_atp_table.calling_module.count
1255 update msc_demands
1256 set atp_synchronization_flag = 1,
1257 last_updated_by = G_FND_USER,
1258 last_update_login = G_FND_USER,
1259 last_update_date = l_sysdate
1260 where plan_id = p_old_plan_id
1261 and sr_instance_id = p_atp_table.instance_id(l_counter)
1262 and origination_type = 30
1263 and sales_order_line_id = p_atp_table.identifier(l_counter)
1264 and refresh_number <= p_atp_table.attribute_04(l_counter)
1265 and demand_id <= p_atp_table.attribute_03(l_counter)
1266 and organization_id = p_atp_table.source_organization_id(l_counter)
1267 and NVL(atp_synchronization_flag, -1) <> 1;
1268
1269 conc_debug ('Rows Updated : ' || sql%rowcount);
1270
1271 /* Not Needed as component records aren't selected from msc_demands
1272 -- CTO Re-arch changes, need to update demands for ATO Model's options/ OC's
1273 conc_debug ('Updating CTO Component Demands');
1274
1275 forall l_counter in 1..p_atp_table.calling_module.count
1276 update msc_demands
1277 set atp_synchronization_flag = 1,
1278 last_updated_by = G_FND_USER,
1279 last_update_login = G_FND_USER,
1280 last_update_date = l_sysdate
1281 where plan_id = p_old_plan_id
1282 and sr_instance_id = p_atp_table.instance_id(l_counter)
1283 and ato_line_id = p_atp_table.identifier(l_counter)
1284 and refresh_number <= p_atp_table.attribute_04(l_counter)
1285 and p_atp_table.identifier(l_counter) <> p_atp_table.ato_model_line_id(l_counter)
1286 and NVL(atp_synchronization_flag, -1) <> 1;
1287
1288 conc_debug ('Rows Updated : ' || sql%rowcount);
1289 */
1290
1291 EXCEPTION
1292 when OTHERS then
1293 conc_log ('Error in Update Sync Flag');
1294 conc_log ('Error is : ' || sqlerrm);
1295 x_return_status := FND_API.G_RET_STS_ERROR;
1296 END Update_Sync_flag;
1297
1298 ---------------------------------------------------------------------------------
1299
1300 PROCEDURE ATP_Downtime_Init (
1301 p_old_plan_id IN number,
1302 p_new_plan_id IN number,
1303 x_return_status OUT NOCOPY varchar2
1304 ) IS
1305 BEGIN
1306 conc_debug ('------ATP_Downtime_Init--------');
1307 conc_debug (' Old Plan: ' || p_old_plan_id);
1308
1309 x_return_status := FND_API.G_RET_STS_SUCCESS;
1310 -- Update MSC Plans to null out the date.
1311 /* update msc_plans
1312 set plan_completion_date = NULL
1313 where plan_id = p_old_plan_id;
1314 */
1315 Update_Summary_Flag ( P_new_plan_id,
1316 G_SF_SYNC_DOWNTIME,
1317 x_return_status
1318 );
1319 conc_debug ('-------------------------------');
1320
1321 EXCEPTION
1322 when others then
1323 conc_log ('Error in Downtime INIT');
1324 conc_log ('Error is : ' || sqlerrm);
1325 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1326
1327 END ATP_Downtime_Init;
1328
1329 ---------------------------------------------------------------------------------
1330
1331 PROCEDURE ATP_Downtime_Complete (
1332 p_old_plan_id IN number,
1333 p_new_plan_id IN number,
1334 x_return_status OUT NOCOPY varchar2
1335 ) IS
1336 l_request_id number;
1337 l_call_status boolean;
1338 l_phase varchar2(80);
1339 l_status varchar2(80);
1340 l_dev_phase varchar2(80);
1341 l_dev_status varchar2(80);
1342 l_message varchar2(240);
1343
1344 --Bug 8301235
1345 l_mpo_so_lrn MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
1346 l_mpo_org_id MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
1347 l_mpo_sr_instance_id MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
1348
1349 BEGIN
1350
1351 conc_log ('ATP Downtime Complete');
1352
1353 SELECT so_lrn, sr_instance_id, organization_id
1354 BULK COLLECT INTO l_mpo_so_lrn, l_mpo_sr_instance_id, l_mpo_org_id
1355 FROM msc_plan_organizations
1356 WHERE plan_id = p_new_plan_id;
1357
1358 -- CALL UI API HERE
1359 BEGIN
1360 l_request_id := FND_REQUEST.SUBMIT_REQUEST (
1361 'MSC',
1362 'MSCSWPLN',
1363 NULL,
1364 NULL,
1365 FALSE,
1366 p_old_plan_id,
1367 p_new_plan_id
1368 );
1369 commit;
1370 EXCEPTION
1371 when OTHERS then
1372 conc_log ('Concurrent program error. Code : ' || sqlcode);
1373 conc_log (' Error : ' || sqlerrm);
1374 x_return_status := FND_API.G_RET_STS_ERROR;
1375 G_TFS_ERROR := 'Unable to switch plans';
1376 G_TFS_ERRCODE := MSC_POST_PRO.G_ERROR;
1377 return;
1378 END;
1379
1380 conc_log ('Switch Plans launched with request ID ' || l_request_id);
1381 l_call_status := FND_CONCURRENT.WAIT_FOR_REQUEST (
1382 l_request_id,
1383 10,
1384 10000000,
1385 l_phase,
1386 l_status,
1387 l_dev_phase,
1388 l_dev_status,
1389 l_message );
1390
1391 conc_debug ('Returned from Wait For Request');
1392 conc_debug ('Phase : ' || l_dev_phase || ' Status : ' || l_dev_status );
1393
1394 if (l_dev_status <> 'NORMAL') then
1395 conc_log ('Switch plans returned with ' || l_dev_status);
1396 x_return_status := FND_API.G_RET_STS_ERROR;
1397 G_TFS_ERROR := 'Unable to switch plans - program returned with error';
1398 G_TFS_ERRCODE := MSC_POST_PRO.G_ERROR;
1399
1400 return;
1401 end if;
1402
1403 --Reset the value of msc_plan_organizations.so_lrn to msc_instance_orgs.so_lrn
1404 --as it got changed during Plan Switch : Bug#8301235
1405 conc_debug ('Resetting the value of msc_plan_organizations.so_lrn...');
1406
1407 FORALL j IN l_mpo_org_id.FIRST.. l_mpo_org_id.LAST
1408 UPDATE msc_plan_organizations
1409 SET so_lrn = l_mpo_so_lrn(j)
1410 WHERE sr_instance_id = l_mpo_sr_instance_id(j)
1411 AND organization_id = l_mpo_org_id(j)
1412 AND plan_id = p_new_plan_id;
1413
1414 -- Call Refresh Snapshot
1415
1416 conc_log ('Updating Summary flag');
1417 Update_Summary_Flag (p_new_plan_id, G_SF_SYNC_SUCCESS, x_return_status);
1418
1419 conc_log ('Launching Analyze Plan Partition');
1420
1421 -- Bug 2809606 : Will refresh snapshot directly, instead of launching ANAPP
1422 /*
1423 BEGIN
1424 l_request_id := FND_REQUEST.SUBMIT_REQUEST
1425 (
1426 'MSC',
1427 'MSCANAPP',
1428 NULL,
1429 NULL,
1430 FALSE,
1431 0
1432 );
1433 commit;
1434
1435 conc_log ('Analyze plan_partition launched with request ID ' || l_request_id);
1436 EXCEPTION
1437 when OTHERS then
1438 conc_log ('Concurrent program error. Code : ' || sqlcode);
1439 conc_log (' Error : ' || sqlerrm);
1440 END;
1441
1442 -- Wait for Concurrent request to complete
1443 l_call_status := FND_CONCURRENT.WAIT_FOR_REQUEST (
1444 l_request_id,
1445 10,
1446 10000000,
1447 l_phase,
1448 l_status,
1449 l_dev_phase,
1450 l_dev_status,
1451 l_message );
1452
1453 conc_debug ('Returned from Wait For Analyze plan partition request Request');
1454 conc_debug ('Phase : ' || l_dev_phase || ' Status : ' || l_dev_status );
1455
1456 if (l_dev_status <> 'NORMAL') then
1457 conc_log ('Analyze plan partition returned with ' || l_dev_status);
1458 x_return_status := FND_API.G_RET_STS_ERROR;
1459 return;
1460 end if;
1461 */
1462
1463 -- Refresh_Snapshot(x_return_status);
1464 -- Bug#12926041
1465 BEGIN
1466 --msc_util.msc_log('before launching concurrent program');
1467 -- Dbms_Output.put_line('before launching concurrent program');
1468 conc_log ('Complete Refresh of Snapshot Started : MSC_ATP_PLAN_SN');
1469 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
1470 'MSC',
1471 'MSCREFMV',
1472 NULL, -- description
1473 NULL, -- start time
1474 FALSE, -- sub request
1475 'MSC_ATP_PLAN_SN',
1476 724);
1477 COMMIT;
1478
1479 --msc_util.msc_log('Request ID :' || l_request_id);
1480 --Dbms_Output.put_line('Request ID :' || l_request_id);
1481 x_return_status := FND_API.G_RET_STS_SUCCESS;
1482 conc_log ('Refresh Complete');
1483 EXCEPTION
1484 WHEN OTHERS THEN
1485 --msc_util.msc_log ('Conc. program error : ' || sqlcode || ':' || sqlerrm);
1486 --Dbms_Output.put_line('Conc. program error : ' || sqlcode || ':' || sqlerrm);
1487
1488 conc_log ('Exception in Refresh Snapshot');
1489 conc_log ('SqlCode : ' || sqlcode);
1490 conc_log ('Sql error MSG : ' || sqlerrm);
1491 x_return_status := FND_API.G_RET_STS_ERROR;
1492 END;
1493
1494 END ATP_Downtime_Complete;
1495
1496 ---------------------------------------------------------------------------------
1497
1498 PROCEDURE ATP_Sync_Error (
1499 p_old_plan_id IN number,
1500 p_new_plan_id IN number
1501 ) IS
1502 l_return_status varchar2(1);
1503
1504 BEGIN
1505 conc_log ('Sync Error: Unable to continue further');
1506 Update_Summary_Flag ( p_new_plan_id,
1507 G_SF_SYNC_ERROR,
1508 l_return_status
1509 );
1510 conc_log ('ATP Status Flag Update : ' || l_return_status);
1511
1512 END ATP_Sync_Error;
1513 ---------------------------------------------------------------------------------
1514
1515 -- Synchronization Downtime Calculation
1516 -- Values stored in Global variables defined in spec.
1517 -- The average records per unit time is calculated, and
1518 -- the total number of Sales orders that can be processed
1519 -- during this time is computed. The total number of records
1520 -- that can be processed during the synchronization downtime
1521 -- is then computed and stored.
1522 PROCEDURE Calculate_Downtime_SO_Records (
1523 p_number_of_records IN number,
1524 p_total_time IN number
1525 ) IS
1526
1527 l_time_per_record number;
1528
1529 BEGIN
1530
1531 conc_debug ('------Calculate_Downtime_SO_Records ------');
1532 conc_debug ('Time : ' || p_total_time || ' Orders : ' || p_number_of_records );
1533 conc_debug ('Current threshold : ' || G_TF7_SO_THRESHOLD);
1534
1535 G_TF7_TOTAL_RECORDS := G_TF7_TOTAL_RECORDS + p_number_of_records;
1536 G_TF7_TOTAL_TIME := G_TF7_TOTAL_TIME + p_total_time;
1537
1538 if (G_TF7_TOTAL_RECORDS = 0) then
1539 G_TF7_SO_THRESHOLD := 0;
1540 return;
1541 end if;
1542 l_time_per_record := G_TF7_TOTAL_TIME / G_TF7_TOTAL_RECORDS ;
1543
1544 if (l_time_per_record = 0) then
1545 G_TF7_SO_THRESHOLD := 0;
1546 return;
1547 end if;
1548 G_TF7_SO_THRESHOLD := G_TF7_DOWNTIME / l_time_per_record;
1549
1550 conc_debug ('New threshold SO records: ' || G_TF7_SO_THRESHOLD);
1551
1552 END Calculate_Downtime_SO_Records;
1553
1554 ---------------------------------------------------------------------------------
1555
1556 -- Function to update the summary flag for a plan.
1557 PROCEDURE Update_Summary_Flag (
1558 p_plan_id IN number,
1559 p_status IN number,
1560 x_return_status OUT NOCOPY varchar2
1561 ) IS
1562 BEGIN
1563 conc_debug ('------- Update_Summary_Flag -----');
1564 conc_debug (' Plan ID : '|| p_plan_id || ' Status : ' || p_status);
1565
1566 x_return_status := FND_API.G_RET_STS_SUCCESS;
1567
1568 update msc_plans
1569 set summary_flag = p_status
1570 where plan_id = p_plan_id;
1571
1572 EXCEPTION
1573 WHEN OTHERS THEN
1574 conc_debug ('Cannot Update. Error : ' || sqlerrm);
1575 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1576
1577 END Update_Summary_Flag;
1578
1579 ---------------------------------------------------------------------------------
1580
1581 PROCEDURE conc_log (buf IN VARCHAR2)
1582 IS
1583 BEGIN
1584 msc_util.msc_log (buf);
1585 --msc_sch_wb.mrp_timing (buf);
1586 --dbms_output.put_line (buf);
1587
1588 END conc_log;
1589 ---------------------------------------------------------------------------------
1590
1591 PROCEDURE conc_debug (buf IN VARCHAR2)
1592 IS
1593 BEGIN
1594 -- Check value for profile option
1595 --if (atp_debug_flag in ('Y','C')) then
1596 conc_log (buf);
1597 --end if;
1598
1599 END conc_debug;
1600 ---------------------------------------------------------------------------------
1601
1602 -- CTO Re-arch changes, this procedure will not be used anymore, instead MSC_SATP_FUNC.New_Extend_Atp is used.
1603 PROCEDURE extend_atp_rec_typ (
1604 p_atp_tab IN OUT NOCOPY MRP_ATP_PUB.ATP_Rec_Typ,
1605 p_size IN number
1606 ) IS
1607 BEGIN
1608
1609 p_atp_tab.Row_ID.Extend(p_size);
1610 p_atp_tab.Inventory_Item_Name.Extend(p_size);
1611 p_atp_tab.Organization_Id.Extend(p_size);
1612 p_atp_tab.Source_Organization_Code.Extend(p_size);
1613 p_atp_tab.Demand_Source_Delivery.Extend(p_size);
1614 p_atp_tab.Demand_Source_Type.Extend(p_size);
1615 p_atp_tab.Demand_Source_Header_id.Extend(p_size);
1616 p_atp_tab.Scenario_Id.Extend(p_size);
1617 p_atp_tab.Order_Number.Extend(p_size);
1618 p_atp_tab.Destination_Time_Zone.Extend(p_size);
1619 p_atp_tab.Requested_Arrival_Date.Extend(p_size);
1620 p_atp_tab.Earliest_Acceptable_Date.Extend(p_size);
1621 p_atp_tab.Latest_Acceptable_Date.Extend(p_size);
1622 p_atp_tab.Freight_Carrier.Extend(p_size);
1623 p_atp_tab.Ship_Method.Extend(p_size);
1624 p_atp_tab.Ship_Set_Name.Extend(p_size);
1625 p_atp_tab.Arrival_Set_Name.Extend(p_size);
1626 p_atp_tab.Ship_Date.Extend(p_size);
1627 p_atp_tab.Arrival_Date.Extend(p_size);
1628 p_atp_tab.Available_Quantity.Extend(p_size);
1629 p_atp_tab.Requested_Date_Quantity.Extend(p_size);
1630 p_atp_tab.Group_Ship_Date.Extend(p_size);
1631 p_atp_tab.Group_Arrival_Date.Extend(p_size);
1632 p_atp_tab.Vendor_Id.Extend(p_size);
1633 p_atp_tab.Vendor_Name.Extend(p_size);
1634 p_atp_tab.Vendor_Site_Id.Extend(p_size);
1635 p_atp_tab.Vendor_Site_Name.Extend(p_size);
1636 p_atp_tab.Insert_Flag.Extend(p_size);
1637 p_atp_tab.OE_Flag.Extend(p_size);
1638 p_atp_tab.Error_Code.Extend(p_size);
1639 p_atp_tab.Atp_Lead_Time.Extend(p_size);
1640 p_atp_tab.Message.Extend(p_size);
1641 p_atp_tab.End_Pegging_Id.Extend(p_size);
1642 p_atp_tab.Old_Source_Organization_Id.Extend(p_size);
1643 p_atp_tab.Old_Demand_Class.Extend(p_size);
1644 p_atp_tab.ato_delete_flag.Extend(p_size);
1645 p_atp_tab.attribute_05.Extend(p_size);
1646 p_atp_tab.attribute_06.Extend(p_size);
1647 p_atp_tab.attribute_07.Extend(p_size);
1648 p_atp_tab.attribute_01.Extend(p_size);
1649 p_atp_tab.customer_name.Extend(p_size);
1650 p_atp_tab.customer_class.Extend(p_size);
1651 p_atp_tab.customer_location.Extend(p_size);
1652 p_atp_tab.customer_country.Extend(p_size);
1653 p_atp_tab.customer_state.Extend(p_size);
1654 p_atp_tab.customer_city.Extend(p_size);
1655 p_atp_tab.customer_postal_code.Extend(p_size);
1656 p_atp_tab.req_item_detail_flag.Extend(p_size);
1657 p_atp_tab.request_item_id.Extend(p_size);
1658 p_atp_tab.req_item_req_date_qty.Extend(p_size);
1659 p_atp_tab.req_item_available_date.Extend(p_size);
1660 p_atp_tab.req_item_available_date_qty.Extend(p_size);
1661 p_atp_tab.request_item_name.Extend(p_size);
1662 p_atp_tab.old_inventory_item_id.Extend(p_size);
1663 p_atp_tab.sales_rep.Extend(p_size);
1664 p_atp_tab.customer_contact.Extend(p_size);
1665 p_atp_tab.subst_flag.Extend(p_size);
1666 p_atp_tab.party_site_id.Extend(p_size); --2814895
1667 p_atp_tab.part_of_set.Extend(p_size); --4500382
1668
1669
1670
1671 END extend_atp_rec_typ;
1672 ---------------------------------------------------------------------------------
1673
1674 PROCEDURE Get_Instance_Refresh_Number (
1675 p_plan_id IN number,
1676 p_instance_id IN number,
1677 x_refresh_number OUT NOCOPY number
1678 ) IS
1679
1680 BEGIN
1681 conc_debug ('------Get_Instance_Refresh_Number -------');
1682 conc_debug ('Plan ID : ' || p_plan_id);
1683 conc_debug ('Instance_ID : ' || p_instance_id );
1684
1685 x_refresh_number := -1;
1686
1687 select apps_lrn
1688 into x_refresh_number
1689 from msc_plan_refreshes
1690 where plan_id = p_plan_id
1691 and sr_instance_id = p_instance_id;
1692
1693 conc_debug ('Refresh Number : ' || x_refresh_number);
1694
1695 EXCEPTION
1696
1697 when NO_DATA_FOUND then
1698 conc_debug ('No data found for query. Returning -1');
1699 x_refresh_number := -1;
1700
1701 when OTHERS then
1702 conc_debug ('Exception: Others. Returning -1 ');
1703 conc_debug ('Error Msg : ' || sqlerrm);
1704 x_refresh_number := -1;
1705
1706 END Get_Instance_Refresh_Number;
1707 ---------------------------------------------------------------------------------
1708
1709 PROCEDURE Extended_Sync_Wait (
1710 l_time IN number,
1711 x_return_status OUT NOCOPY varchar2
1712 ) IS
1713 BEGIN
1714 conc_log (' Sleeping for ' || l_time || ' seconds');
1715 DBMS_LOCK.SLEEP(l_time);
1716 conc_log (' Back from sleep ');
1717 END Extended_Sync_Wait;
1718 ---------------------------------------------------------------------------------
1719
1720 PROCEDURE Print_Input_Rec (
1721 x_atp_rec IN MRP_ATP_PUB.ATP_Rec_Typ
1722 ) IS
1723
1724 l_counter number;
1725
1726 BEGIN
1727
1728 conc_log (' ----Printing Input Record ---- ');
1729
1730 for l_counter in 1..x_atp_rec.calling_module.count loop
1731
1732 conc_log (l_counter || '. ' ||
1733 ' Dmd ID : ' || x_atp_rec.attribute_03(l_counter) ||
1734 ' Inst : ' || x_atp_rec.instance_id (l_counter) ||
1735 ' SO Line ID : ' || x_atp_rec.identifier(l_counter) ||
1736 ' Inv Item : ' || x_atp_rec.inventory_item_id(l_counter) ||
1737 ' Qty : ' || x_atp_rec.quantity_ordered(l_counter) ||
1738 ' Date : ' || x_atp_rec.requested_ship_date(l_counter) ||
1739 ' Cust : ' || x_atp_rec.customer_id(l_counter) ||
1740 ' Cst St ID: ' || x_atp_rec.customer_site_id(l_counter) ||
1741 ' Refsh No : ' || x_atp_rec.attribute_04(l_counter) ||
1742 ' Ordr No : ' || x_atp_rec.attribute_08(l_counter) ||
1743 ' ATO Line ID : ' || x_atp_rec.ato_model_line_id(l_counter) ||
1744 ' Session ID : ' || x_atp_rec.attribute_11(l_counter) ||
1745 ' .');
1746
1747 end loop;
1748
1749 conc_log (' ----Done Printing Records ---- ');
1750
1751 END Print_Input_Rec;
1752 ---------------------------------------------------------------------------------
1753
1754 PROCEDURE Refresh_Snapshot (
1755 x_return_status OUT NOCOPY varchar2
1756 ) IS
1757
1758 lv_msc_schema VARCHAR2(30);
1759 v_snap_exist number;
1760
1761 Cursor msc_schema IS
1762 SELECT a.oracle_username
1763 FROM FND_ORACLE_USERID a, FND_PRODUCT_INSTALLATIONS b
1764 WHERE a.oracle_id = b.oracle_id
1765 AND b.application_id= 724;
1766
1767 Cursor atp_snap IS
1768 SELECT 1
1769 FROM all_objects
1770 WHERE object_name like 'MSC_ATP_PLAN_SN'
1771 AND owner = lv_msc_schema;
1772
1773 BEGIN
1774
1775 conc_log ('------- Refresh ATP Plan SN ----------');
1776
1777 OPEN msc_schema;
1778 FETCH msc_schema INTO lv_msc_schema;
1779 CLOSE msc_schema;
1780
1781 OPEN atp_snap;
1782 FETCH atp_snap INTO v_snap_exist;
1783 CLOSE atp_snap;
1784
1785 -- refresh the snapshot if it exists
1786 if v_snap_exist =1 then
1787 conc_log ('Complete Refresh of Snapshot Started');
1788 DBMS_SNAPSHOT.REFRESH( lv_msc_schema||'.MSC_ATP_PLAN_SN', 'C');
1789 conc_log ('Refresh Complete');
1790 end if;
1791
1792 x_return_status := FND_API.G_RET_STS_SUCCESS;
1793
1794 EXCEPTION
1795
1796 WHEN OTHERS THEN
1797 conc_log ('Exception in Refresh Snapshot');
1798 conc_log ('SqlCode : ' || sqlcode);
1799 conc_log ('Sql error MSG : ' || sqlerrm);
1800 x_return_status := FND_API.G_RET_STS_ERROR;
1801
1802 END Refresh_Snapshot;
1803 ---------------------------------------------------------------------------------
1804
1805 PROCEDURE Parse_Sales_Order_Number (
1806 p_order_number_string IN varchar2,
1807 p_order_number IN OUT NOCOPY number
1808 ) IS
1809
1810 l_end number;
1811 l_counter number;
1812 l_max_counter number;
1813 l_char varchar2(1);
1814 l_num_char number;
1815
1816 BEGIN
1817 msc_sch_wb.atp_debug ('------Begin Parse_Sales_Order_string-----');
1818 msc_sch_wb.atp_debug ('');
1819 msc_sch_wb.atp_debug ('String : ' || p_order_number_string);
1820 msc_sch_wb.atp_debug ('Number : ' || p_order_number);
1821
1822 BEGIN
1823 p_order_number := to_number (p_order_number_string);
1824 msc_sch_wb.atp_debug ('Order number is : ' || p_order_number);
1825 return;
1826 EXCEPTION
1827 WHEN others then
1828 msc_sch_wb.atp_debug ('Order number has to be processed');
1829 END;
1830
1831 l_end := 0;
1832 l_counter := 0;
1833 l_max_counter := length(p_order_number_string);
1834
1835 msc_sch_wb.atp_debug ('Parse SO: Length of Order Number String : '|| l_max_counter);
1836
1837 if (l_max_counter = 0) then
1838 msc_sch_wb.atp_debug ('String is null' );
1839 return;
1840 end if;
1841
1842 while (l_end <> 1) loop
1843
1844 l_counter := l_counter + 1;
1845 if (l_counter > l_max_counter ) then
1846 l_end := 1;
1847 msc_sch_wb.atp_debug ('Parse SO: Max Length reached');
1848 exit;
1849 end if;
1850
1851 l_char := substr (p_order_number_string, l_counter, 1);
1852 BEGIN
1853 l_num_char := to_number (l_char);
1854 EXCEPTION
1855 WHEN OTHERS then
1856 msc_sch_wb.atp_debug ('Parse SO: String end detected');
1857 l_end := 1;
1858 END;
1859 end loop;
1860
1861 if (l_counter > l_max_counter) then
1862 msc_sch_wb.atp_debug ('Order number string is a number');
1863
1864 BEGIN
1865 p_order_number := to_number (p_order_number_string);
1866 EXCEPTION
1867 WHEN others then
1868 msc_sch_wb.atp_debug ('Parse SO: Something wrong. Should not be here');
1869 return;
1870 END;
1871
1872 elsif (l_counter = 1) then
1873 msc_sch_wb.atp_debug ('Order Number not properly formatted.');
1874 else
1875 msc_sch_wb.atp_debug ('Parse SO: Counter is at ' || l_counter);
1876 BEGIN
1877 p_order_number := to_number (substr (p_order_number_string, 1,l_counter -1));
1878 EXCEPTION
1879 WHEN others then
1880 msc_sch_wb.atp_debug ('Parse SO: Something wrong. Should not be here');
1881 return;
1882 END;
1883 end if;
1884
1885 msc_sch_wb.atp_debug ('Order number is : ' || p_order_number);
1886
1887 EXCEPTION
1888 WHEN others THEN
1889 msc_sch_wb.atp_debug ('Something wrong in Parse Sales Order');
1890
1891 END Parse_Sales_Order_Number;
1892
1893 -----------------------------------
1894
1895
1896 END MSC_ATP_24x7;