[Home] [Help]
PACKAGE BODY: APPS.MRP_PO_RESCHEDULE
Source
1 PACKAGE BODY MRP_PO_RESCHEDULE AS
2 /* $Header: MRPPOREB.pls 120.7.12010000.2 2008/10/17 12:05:22 lsindhur ship $ */
3
4 l_debug varchar2(30) := 'Y';
5 g_dblink VARCHAR2(129);
6
7 g_po_numbers po_tbl_varchar100;
8 g_po_header_ids po_tbl_number;
9 g_po_line_ids po_tbl_number;
10 g_line_location_ids po_tbl_number;
11 g_distribution_ids po_tbl_number;
12 g_qtys po_tbl_number;
13 g_promise_dates po_tbl_date;
14 g_uoms po_tbl_varchar30;
15 g_operating_units po_tbl_number;
16 g_current_rec NUMBER := 1;
17 g_current_org_id NUMBER := NULL;
18
19 /********************************************************
20 PROCEDURE : log_message
21 ********************************************************/
22
23 PROCEDURE log_message( p_user_info IN VARCHAR2) IS
24 BEGIN
25
26 FND_FILE.PUT_LINE(FND_FILE.LOG, p_user_info);
27
28 EXCEPTION
29 WHEN OTHERS THEN
30 RAISE;
31 END log_message;
32
33 PROCEDURE debug_message( p_user_info IN VARCHAR2) IS
34 fname utl_file.file_type ;
35 BEGIN
36 -- fname := utl_file.fopen('/sqlcom/log/mfgstrw','dttmp2','a');
37 -- utl_file.put(fname, p_user_info);
38 -- utl_file.fflush(fname);
39 -- utl_file.fclose(fname);
40
41 IF l_debug = 'Y' THEN
42 log_message(p_user_info);
43 --INSERT INTO dt_debug VALUES (p_user_info);
44 END IF;
45 EXCEPTION
46 WHEN OTHERS THEN
47 RAISE;
48 END debug_message;
49
50 PROCEDURE debug_number_tbl( p_msg IN VARCHAR2, p_tbl IN po_tbl_number ) IS
51 i NUMBER;
52 BEGIN
53 debug_message(p_msg || ' number of changes: ' || p_tbl.COUNT() );
54 FOR i IN 1..p_tbl.COUNT() LOOP
55 debug_message(p_msg || p_tbl(i));
56 END LOOP;
57 END debug_number_tbl;
58
59 PROCEDURE debug_date_tbl( p_msg IN VARCHAR2, p_tbl IN po_tbl_date ) IS
60 i NUMBER;
61 BEGIN
62 debug_message(p_msg || ' number of changes: ' || p_tbl.COUNT() );
63 FOR i IN 1..p_tbl.COUNT() LOOP
64 debug_message(p_msg || p_tbl(i));
65 END LOOP;
66 END debug_date_tbl;
67
68 PROCEDURE debug_varchar30_tbl( p_msg IN VARCHAR2, p_tbl IN po_tbl_varchar30 ) IS
69 i NUMBER;
70 BEGIN
71 debug_message(p_msg || ' number of changes: ' || p_tbl.COUNT() );
72 FOR i IN 1..p_tbl.COUNT() LOOP
73 debug_message(p_msg || p_tbl(i));
74 END LOOP;
75 END debug_varchar30_tbl;
76
77 /**********************************************************************
78 Initialization procedures
79 **********************************************************************/
80
81 /***********************************************************************
82 *
83 * Move data to global temporary table if this goes across a dblink
84 *
85 ***********************************************************************/
86 PROCEDURE transfer_to_temp_table (
87 p_dblink IN VARCHAR2,
88 p_instance_id IN NUMBER,
89 p_batch_id IN NUMBER
90 ) IS
91 l_sql_stmt VARCHAR2(2000);
92 BEGIN
93
94 IF( p_dblink IS NULL ) THEN
95 debug_message( 'No dblink specified in call to transfer_to_temp_table');
96 RETURN;
97 END IF;
98
99 l_sql_stmt :=
100 'INSERT INTO mrp_po_reschedule_gt (
101 purchase_order_id
102 , po_number
103 , line_id
104 , line_location_id
105 , distribution_id
106 , quantity
107 , need_by_date
108 , uom
109 , operating_unit
110 , created_by
111 , creation_date
112 , last_updated_by
113 , last_update_date
114 , last_update_login
115 ) (
116 SELECT
117 po_header_id
118 , po_number
119 , po_line_id
120 , po_line_location_id
121 , po_distribution_id
122 , po_quantity
123 , new_need_by_date
124 , uom
125 , operating_unit
126 , fnd_global.user_id
127 , Sysdate
128 , fnd_global.user_id
129 , Sysdate
130 , fnd_global.user_id
131 FROM msc_purchase_order_interface' || p_dblink ||
132 ' WHERE sr_instance_id = :p_instance_id
133 AND batch_id = :p_batch_id)';
134
135 execute immediate l_sql_stmt
136 using IN p_instance_id,
137 IN p_batch_id;
138
139 EXCEPTION
140 WHEN OTHERS THEN
141 DEBUG_MESSAGE('Error in transfer_to_temp_table : Err Others');
142 DEBUG_MESSAGE(SQLERRM);
143 RAISE;
144
145 END transfer_to_temp_table;
146
147 PROCEDURE init_instance(p_user_name IN VARCHAR2,
148 p_resp_name IN VARCHAR2
149 ) IS
150 l_user_id NUMBER;
151 l_appl_id NUMBER;
152 l_resp_id NUMBER;
153 lv_log_msg varchar2(500);
154 BEGIN
155
156 /* if user_id = -1, it means this procedure is called from a
157 remote database */
158 IF FND_GLOBAL.USER_ID = -1 THEN
159
160 BEGIN
161
162 SELECT USER_ID
163 INTO l_user_id
164 FROM FND_USER
165 WHERE USER_NAME = p_user_name;
166
167 EXCEPTION
168 WHEN NO_DATA_FOUND THEN
169 raise_application_error (-20001, 'NO_USER_DEFINED');
170 END;
171
172 IF MRP_CL_FUNCTION.validateUser(l_user_id,MSC_UTIL.TASK_RELEASE,lv_log_msg) THEN
173 MRP_CL_FUNCTION.MSC_Initialize(MSC_UTIL.TASK_RELEASE,
174 l_user_id,
175 -1, --l_resp_id,
176 -1 --l_application_id
177 );
178 ELSE
179 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_log_msg);
180 raise_application_error (-20001, lv_log_msg);
181 END IF;
182 ELSE
183 l_appl_id := 724;
184
185 SELECT USER_ID
186 INTO l_user_id
187 FROM FND_USER
188 WHERE USER_NAME = p_user_name;
189
190 SELECT responsibility_id
191 INTO l_resp_id
192 FROM FND_responsibility_vl
193 WHERE responsibility_name = p_resp_name
194 AND application_Id = l_appl_id;
195
196 fnd_global.apps_initialize(l_user_id, l_resp_id, l_appl_id);
197
198 END IF;
199
200 END init_instance;
201
202 PROCEDURE cleanup_destination( p_batch_id IN NUMBER,
203 p_dblink IN VARCHAR2 )
204 IS
205 l_sql_stmt VARCHAR2(1000);
206 BEGIN
207
208 l_sql_stmt := 'DELETE FROM msc_purchase_order_interface'||p_dblink||
209 ' WHERE batch_id = :p_batch_id';
210 execute immediate l_sql_stmt using
211 IN p_batch_id;
212 END cleanup_destination;
213
214 /***********************************************************************
215 *
216 * populate the global pl/sql tables with the POs that need to be rescheduled
217 * sorted by purchase_order_id
218 *
219 * For cross database releases, first copy the planning database info
220 * to a temp tbl on the ERP instance to utilize bulk sql
221 *
222 ***********************************************************************/
223
224 PROCEDURE init(
225 p_batch_id IN NUMBER,
226 p_instance_id IN NUMBER,
227 p_instance_code IN varchar2 ,
228 p_dblink IN varchar2
229 ) IS
230
231 CURSOR c_dblink(p_instance_id NUMBER, p_instance_code varchar2,p_dblink varchar2 ) IS
232 select DECODE( A2M_DBLINK,
233 NULL, NULL,
234 '@'||A2M_DBLINK),
235 INSTANCE_ID
236 from MRP_AP_APPS_INSTANCES_ALL
237 where ALLOW_RELEASE_FLAG=1
238 and instance_id = p_instance_id
239 and instance_code=p_instance_code
240 and nvl(A2M_DBLINK,'-1') = nvl(p_dblink,'-1');
241
242 l_dblink VARCHAR2(129);
243 l_instance_id NUMBER;
244
245 BEGIN
246
247 OPEN c_dblink(p_instance_id,p_instance_code,p_dblink);
248 FETCH c_dblink INTO l_dblink, l_instance_id;
249 IF( c_dblink%notfound ) THEN
250 debug_message('Could not find instance that is valid for release');
251 RAISE no_data_found;
252 END IF;
253
254 debug_message('p_instance_id' || p_instance_id ||'p_instance_code ' || p_instance_code);
255 debug_message('p_dblink' || p_dblink ||'l_dblink ' || l_dblink);
256
257 g_dblink := l_dblink;
258 g_current_rec := 1;
259 g_po_header_ids := po_tbl_number();
260 g_line_location_ids := po_tbl_number();
261 g_distribution_ids := po_tbl_number();
262 g_qtys := po_tbl_number();
263 g_promise_dates := po_tbl_date();
264 g_uoms := po_tbl_varchar30();
265 g_operating_units := po_tbl_number();
266
267 IF( l_dblink IS NOT NULL ) THEN
268 debug_message('Get data from dblink ' || l_dblink);
269 transfer_to_temp_table( l_dblink, l_instance_id, p_batch_id );
270
271 SELECT i.purchase_order_id
272 , i.line_id
273 , i.po_number
274 , i.line_location_id
275 , i.distribution_id
276 , i.quantity
277 , i.need_by_date
278 , u.unit_of_measure
279 , i.operating_unit
280 bulk collect into
281 g_po_header_ids
282 , g_po_line_ids
283 , g_po_numbers
284 , g_line_location_ids
285 , g_distribution_ids
286 , g_qtys
287 , g_promise_dates
288 , g_uoms
289 , g_operating_units
290 FROM mrp_po_reschedule_gt i,
291 mtl_uom_conversions u
292 WHERE i.uom = u.uom_code
293 AND u.inventory_item_id = 0
294 ORDER BY i.purchase_order_id;
295 ELSE
296 debug_message('Data is local');
297 SELECT i.po_header_id
298 , i.po_line_id
299 , i.po_number
300 , i.po_line_location_id
301 , i.po_distribution_id
302 , i.po_quantity
303 , i.new_need_by_date
304 , u.unit_of_measure
305 , i.operating_unit
306 bulk collect into
307 g_po_header_ids
308 , g_po_line_ids
309 , g_po_numbers
310 , g_line_location_ids
311 , g_distribution_ids
312 , g_qtys
313 , g_promise_dates
314 , g_uoms
315 , g_operating_units
316 FROM msc_purchase_order_interface i,
317 msc_uom_conversions u
318 WHERE i.sr_instance_id = l_instance_id
319 AND i.uom = u.uom_code
320 AND u.inventory_item_id = 0
321 AND u.sr_instance_id = i.sr_instance_id
322 AND i.batch_id = p_batch_id
323 ORDER BY i.po_header_id;
324 END IF;
325 close c_dblink;
326
327 debug_message('Lines loaded in init: ' || SQL%rowcount);
328 EXCEPTION
329 WHEN OTHERS THEN
330 IF c_dblink%ISOPEN THEN
331 close c_dblink;
332 END IF ;
333 LOG_MESSAGE('Error in init : Err Others');
334 LOG_MESSAGE(SQLERRM);
335 RAISE;
336
337 END init;
338
339 /**********************************************************************
340 *Functions/procedures used during processing
341 **********************************************************************/
342
343 FUNCTION same_record(idx1 IN NUMBER, idx2 IN NUMBER)
344 RETURN BOOLEAN IS
345 l_same BOOLEAN;
346 BEGIN
347
348 l_same := TRUE;
349 IF( g_po_header_ids(idx1) <> g_po_header_ids(idx2) ) THEN
350 l_same := FALSE;
351 END IF;
352
353 IF( l_same = TRUE AND
354 Nvl(g_po_line_ids(idx1), -1) <> Nvl(g_po_line_ids(idx2), -1) ) THEN
355 l_same := FALSE;
356 END IF;
357
358 IF( l_same = TRUE AND
359 Nvl(g_line_location_ids(idx1), -1) <> Nvl(g_line_location_ids(idx2), -1) )
360 THEN
361 l_same := FALSE;
362 END IF;
363
364 IF( l_same ) THEN
365 debug_message('Compare recs: ' || idx1 || ' ' || idx2 || ': TRUE');
366 ELSE
367 debug_message('Compare recs: ' || idx1 || ' ' || idx2 || ': TRUE');
368 END IF;
369
370 RETURN l_same;
371 END same_record;
372
373 /***********************************************************************
374 *
375 * Take the next PO we want to process from the global variables
376 * and put it into the output tables
377 *
378 * Currently each record corresponds to a po. Not a shipment or distribution
379 *
380 ***********************************************************************/
381 FUNCTION get_next_record (
382 x_po_header_id OUT nocopy NUMBER,
383 x_po_number OUT nocopy VARCHAR2,
384 x_operating_unit OUT nocopy NUMBER,
385 x_po_line_ids OUT nocopy po_tbl_number,
386 x_line_location_ids OUT nocopy po_tbl_number,
387 x_distribution_ids OUT nocopy po_tbl_number,
388 x_qtys OUT nocopy po_tbl_number,
389 x_promise_dates OUT nocopy po_tbl_date,
390 x_uoms OUT nocopy po_tbl_varchar30
391 ) RETURN BOOLEAN IS
392 /*
393 CURSOR c_po_header (p_po_number VARCHAR2) IS
394 select po_header_id
395 from po_headers
396 where segment1 = p_PO_NUMBER
397 and type_lookup_code IN ('STANDARD', 'BLANKET', 'PLANNED');
398 */
399 DISTRIBUTION_LVL CONSTANT NUMBER := 0;
400 SHIPMENT_LVL CONSTANT NUMBER := 1;
401 PO_LINE_LVL CONSTANT NUMBER := 2;
402
403 l_po_number VARCHAR2(100);
404 l_starting_rec NUMBER;
405 shipment_ctr NUMBER := 1;
406 distribution_ctr NUMBER := 1;
407 po_line_ctr NUMBER := 1;
408 l_record_lvl NUMBER := 0;
409 BEGIN
410
411 IF g_current_rec > g_po_header_ids.COUNT() THEN
412 RETURN FALSE;
413 END IF;
414
415 l_starting_rec := g_current_rec;
416 x_po_header_id := g_po_header_ids(g_current_rec);
417 x_po_number := g_po_numbers(g_current_rec);
418 x_operating_unit := g_operating_units(g_current_rec);
419
420 /***********
421 WHILE( x_po_header_id IS NULL ) LOOP
422
423 l_po_number := g_po_numbers(g_current_rec);
424
425 OPEN c_po_header(l_po_number);
426 FETCH po_header_csr
427 INTO x_po_number;
428 IF( po_header_csr%notfound ) THEN
429 -- order number is invalid. go to next record
430 g_current_rec := g_current_rec + 1;
431 END IF;
432 CLOSE po_header_csr;
433
434 END LOOP;
435 ***********/
436
437 x_po_line_ids := po_tbl_number();
438 x_line_location_ids := po_tbl_number();
439 x_distribution_ids := po_tbl_number();
440 x_qtys := po_tbl_number();
441 x_promise_dates := po_tbl_date();
442 x_uoms := po_tbl_varchar30();
443
444 IF( g_distribution_ids(l_starting_rec) IS NOT NULL ) THEN
445 l_record_lvl := DISTRIBUTION_LVL;
446 ELSIF( g_line_location_ids(l_starting_rec) IS NOT NULL ) THEN
447 l_record_lvl := SHIPMENT_LVL;
448 ELSIF( g_po_line_ids(l_starting_rec) IS NOT NULL ) THEN
449 l_record_lvl := PO_LINE_LVL;
450 ELSE
451 debug_message('GET_NEXT_RECORD: ERROR in setting record_lvl');
452 RETURN FALSE;
453 END IF;
454
455 debug_message( 'get_next_record: starting rec: ' || l_starting_rec );
456 debug_message( ' tot rec: ' || g_po_header_ids.COUNT() );
457 debug_message( ' header_id: ' || x_po_header_id );
458 WHILE( g_current_rec <= g_po_header_ids.COUNT() AND
459 same_record(l_starting_rec, g_current_rec) )
460 LOOP
461
462 -- add some better logic here
463
464 IF( l_record_lvl = DISTRIBUTION_LVL ) THEN
465 x_distribution_ids.extend();
466 x_qtys.extend();
467 x_uoms.extend();
468 x_distribution_ids(distribution_ctr) := g_distribution_ids(g_current_rec);
469 x_qtys(distribution_ctr) := g_qtys(g_current_rec);
470 x_uoms(distribution_ctr) := g_uoms(g_current_rec);
471 distribution_ctr := distribution_ctr + 1;
472 IF( shipment_ctr = 1 OR
473 x_line_location_ids( shipment_ctr - 1 ) <> g_line_location_ids(g_current_rec) )
474 THEN
475 x_promise_dates.extend();
476 x_promise_dates(shipment_ctr) := g_promise_dates(g_current_rec);
477
478 x_line_location_ids.extend();
479 x_line_location_ids(shipment_ctr) := g_line_location_ids(g_current_rec);
480 shipment_ctr := shipment_ctr + 1;
481 ELSE
482 debug_message('Skipping duplicate shipment for distribution lvl change');
483 IF( g_promise_dates(g_current_rec) <> x_promise_dates(shipment_ctr - 1) ) THEN
484 debug_message('Mismatched dates for shipment.');
485 debug_message('first date: ' || To_char(x_promise_dates(shipment_ctr - 1), 'DD-MON-YYYY HH24:MI:SS'));
486 debug_message('current date: ' || To_char(g_promise_dates(g_current_rec) , 'DD-MON-YYYY HH24:MI:SS'));
487 END IF;
488
489 END IF;
490 ELSIF( l_record_lvl = SHIPMENT_LVL ) THEN
491 x_promise_dates.extend();
492 x_qtys.extend();
493 x_uoms.extend();
494
495 x_qtys(shipment_ctr) := g_qtys(g_current_rec);
496 x_uoms(shipment_ctr) := g_uoms(g_current_rec);
497 x_promise_dates(shipment_ctr) := g_promise_dates(g_current_rec);
498
499 x_line_location_ids.extend();
500 x_line_location_ids(shipment_ctr) := g_line_location_ids(g_current_rec);
501 shipment_ctr := shipment_ctr + 1;
502 ELSE
503 x_po_line_ids.extend();
504 x_promise_dates.extend();
505 x_qtys.extend();
506 x_uoms.extend();
507 x_po_line_ids(po_line_ctr) := g_po_line_ids(g_current_rec);
508 x_promise_dates(po_line_ctr) := g_promise_dates(g_current_rec);
509 x_qtys(po_line_ctr) := g_qtys(g_current_rec);
510 x_uoms(po_line_ctr) := g_uoms(g_current_rec);
511 po_line_ctr := po_line_ctr + 1;
512 debug_message('Adding po_line change: ' || g_current_rec);
513 END IF;
514
515 g_current_rec := g_current_rec + 1;
516 END LOOP;
517 debug_message( ' ending rec: ' || g_current_rec );
518
519 debug_number_tbl('shipment: ', x_line_location_ids);
520 debug_number_tbl('dstribution: ', x_distribution_ids);
521 debug_number_tbl('po_line: ', x_po_line_ids);
522 debug_number_tbl('qty: ', x_qtys);
523 debug_varchar30_tbl('uom: ', x_uoms);
524 debug_date_tbl('date: ', x_promise_dates);
525
526 RETURN TRUE;
527
528 EXCEPTION
529 WHEN OTHERS THEN
530 LOG_MESSAGE('Error in CREATE_AND_SCHEDULE_ISO : Err Others');
531 LOG_MESSAGE(SQLERRM);
532 RAISE;
533 END get_next_record;
534
535
536
537 PROCEDURE change_operating_unit( p_org_id IN NUMBER )
538 IS
539
540 CURSOR c_security (l_org_id number,
541 l_user_id number,
542 l_appl_id number) IS
543 SELECT level_value
544 FROM fnd_profile_options opt,
545 fnd_profile_option_values opt_vals,
546 fnd_user_resp_groups user_resp
547 WHERE opt.profile_option_name = 'ORG_ID'
548 AND opt.profile_option_id = opt_vals.profile_option_id
549 AND opt_vals.profile_option_value = to_char(l_org_id)
550 AND opt_vals.level_id = 10003 -- responsibility level
551 AND user_resp.user_id = l_user_id
552 AND user_resp.responsibility_id = opt_vals.level_value
553 AND user_resp.responsibility_application_id = l_appl_id
554 AND rownum = 1;
555
556 l_user_id NUMBER;
557 l_appl_id NUMBER;
558 l_resp_id NUMBER;
559
560 BEGIN
561 IF( g_current_org_id = p_org_id ) THEN
562 RETURN;
563 END IF;
564
565 l_user_id := fnd_global.user_id();
566 l_appl_id := 724;
567
568 OPEN c_security( p_org_id,
569 l_user_id,
570 l_appl_id );
571 FETCH c_security INTO l_resp_id;
572 IF c_security%notfound THEN
573 debug_message( 'Could not find appropriate resp for operating unit: ' || p_org_id ||
574 ' and user: ' || l_user_id );
575 ELSE
576 fnd_global.apps_initialize(l_user_id, l_resp_id, l_appl_id);
577 g_current_org_id := p_org_id;
578 END IF;
579 CLOSE c_security;
580
581 EXCEPTION
582 WHEN OTHERS THEN
583 LOG_MESSAGE('Error in change_operating_unit : Err Others');
584 LOG_MESSAGE(SQLERRM);
585 RAISE;
586
587 END change_operating_unit;
588
589 /**********************************************************
590 *
591 * Procedure: MSC_RESCHED_PO
592 *
593 * Main Procedure. Structure is as follows
594 * 1) initialize global variables that store all the pos
595 * that need to be rescheduled
596 * 2) while there are still pos left, get the next po that
597 * needs to be rescheduled
598 * 3) reschedule the PO by calling the bulk change apis
599 *
600 ***********************************************************/
601 PROCEDURE msc_resched_po(
602 errbuf OUT NOCOPY VARCHAR2,
603 retcode OUT NOCOPY VARCHAR2,
604 p_batch_id IN NUMBER,
605 P_instance_id IN NUMBER,
606 p_instance_code IN varchar2,
607 p_dblink IN varchar2
608 ) IS
609 l_instance_id NUMBER;
610
611
612 l_po_header_id NUMBER;
613 l_po_number VARCHAR2(100); --bug 7144230
614 l_operating_unit NUMBER;
615 l_po_release_id NUMBER; --bug 7144230
616 l_po_release_num varchar2(100); --bug 7144230
617 l_po_release_number NUMBER :=NULL; --bug 7144230
618
619 l_po_return_status VARCHAR2(1);
620 l_po_api_errors PO_API_ERRORS_REC_TYPE;
621
622 l_po_line_ids po_tbl_number;
623 l_line_location_ids po_tbl_number;
624 l_distribution_ids po_tbl_number;
625 l_qtys po_tbl_number;
626 l_promise_dates po_tbl_date;
627 l_uoms po_tbl_varchar30;
628
629 l_po_line_changes po_lines_rec_type;
630 l_shipment_changes PO_SHIPMENTS_REC_TYPE;
631 l_distribution_changes PO_DISTRIBUTIONS_REC_TYPE;
632 l_changes PO_CHANGES_REC_TYPE;
633 l_po_operating_unit NUMBER;
634
635
636
637 i NUMBER;
638 BEGIN
639
640 init(p_batch_id,p_instance_id ,p_instance_code , p_dblink );
641
642 while get_next_record(x_po_header_id => l_po_header_id,
643 x_po_number => l_po_number,
644 x_operating_unit => l_operating_unit,
645 x_po_line_ids => l_po_line_ids,
646 x_line_location_ids => l_line_location_ids,
647 x_distribution_ids => l_distribution_ids,
648 x_qtys => l_qtys,
649 x_promise_dates => l_promise_dates,
650 x_uoms => l_uoms)
651 loop
652
653 begin
654 SAVEPOINT before_change;
655
656 select org_id
657 into l_po_operating_unit
658 from po_headers_all
659 where PO_HEADER_ID= l_po_header_id;
660
661 change_operating_unit( l_po_operating_unit );
662
663 mo_global.init('PO'); -- MOAC Change
664 mo_global.set_policy_context('S',l_po_operating_unit); -- MOAC Change
665
666 debug_message('Changing to operating unit: ' || l_po_operating_unit );
667
668 IF( l_distribution_ids.COUNT() > 0 ) THEN
669 debug_message('Creating distribution and shipment changes');
670 l_distribution_changes := PO_DISTRIBUTIONS_REC_TYPE.create_object (
671 p_po_distribution_id => l_distribution_ids,
672 p_request_unit_of_measure => l_uoms,
673 p_quantity_ordered => l_qtys);
674 l_shipment_changes := PO_SHIPMENTS_REC_TYPE.create_object (
675 p_po_line_location_id => l_line_location_ids,
676 p_need_by_date => l_promise_dates);
677 ELSIF( l_line_location_ids.COUNT() > 0 ) THEN
678 debug_message('Creating shipment changes');
679 l_shipment_changes := PO_SHIPMENTS_REC_TYPE.create_object (
680 p_po_line_location_id => l_line_location_ids,
681 p_quantity => l_qtys,
682 p_request_unit_of_measure => l_uoms,
683 p_need_by_date => l_promise_dates);
684 ELSE
685 debug_message('Creating po line changes');
686 l_po_line_changes := po_lines_rec_type.create_object(
687 p_po_line_id => l_po_line_ids,
688 p_quantity => l_qtys,
689 p_request_unit_of_measure => l_uoms,
690 p_start_date => l_promise_dates );
691 END IF;
692
693 debug_message('Header id: ' || l_po_header_id );
694 -- Create a change object for the document with the line and
695 -- shipment changes.
696 /* We have to obtain the po_release_id in case of blanket PO
697 The release Id is buried in the column order_number in MSC_SUPPLIES which comes back to MSC_PURCHASE_ORDER_INTERFACE bug 7144230*/
698 select substr(l_po_number,
699 instr(l_po_number,'(',1,1)+1,
700 instr(l_po_number,')',1,1) - instr(l_po_number,'(',1,1) -1)
701 into l_po_release_num
702 from dual;
703 if (l_po_release_num = '' or l_po_release_num = ' ') then
704 l_po_release_num := NULL;
705 else
706 l_po_release_number := to_number(l_po_release_num);
707 end if;
708 debug_message('Release num ' || l_po_release_number );
709
710 begin
711 select po_release_id
712 into l_po_release_id
713 from po_releases_all
714 where po_header_id = l_po_header_id
715 and release_num = l_po_release_number ;
716
717 exception
718 when NO_DATA_FOUND then
719 l_po_release_id := NULL;
720 end;
721
722 debug_message('Release id: ' || l_po_release_id );
723 l_changes := PO_CHANGES_REC_TYPE.create_object (p_po_header_id => l_po_header_id,
724 p_po_release_id => l_po_release_id,
725 p_line_changes => l_po_line_changes,
726 p_shipment_changes => l_shipment_changes,
727 p_distribution_changes => l_distribution_changes
728 );
729
730 debug_message('Updating document...');
731 -- Call the PO Change API.
732 PO_DOCUMENT_UPDATE_GRP.update_document (p_api_version => 1.0,
733 p_init_msg_list => FND_API.G_TRUE,
734 x_return_status => l_po_return_status,
735 p_changes => l_changes,
736 p_run_submission_checks => FND_API.G_TRUE,
737 p_launch_approvals_flag => FND_API.G_TRUE,
738 p_buyer_id => NULL,
739 p_update_source => NULL,
740 p_override_date => NULL,
741 x_api_errors => l_po_api_errors
742 );
743
744 debug_message('Return status: ' || l_po_return_status);
745
746 IF (l_po_return_status <> fnd_api.G_RET_STS_SUCCESS) THEN
747 -- handle error
748 FOR i IN 1..l_po_api_errors.message_text.COUNT LOOP
749 debug_message( l_po_api_errors.message_text(i) );
750 END LOOP;
751 retcode := MSC_UTIL.G_WARNING;
752 ROLLBACK TO SAVEPOINT before_change;
753 END IF;
754 EXCEPTION
755 WHEN OTHERS THEN
756 debug_MESSAGE('Error in updating document : Err OTHERS');
757 debug_MESSAGE(SQLERRM);
758 ROLLBACK TO SAVEPOINT before_change;
759 END;
760 end loop;
761
762 cleanup_destination(p_batch_id, g_dblink);
763 EXCEPTION
764 WHEN OTHERS THEN
765 debug_MESSAGE('Error in MSC_RESCHED_PO : Err OTHERS');
766 debug_MESSAGE(SQLERRM);
767 retcode := MSC_UTIL.G_WARNING;
768
769 END msc_resched_po;
770
771 /***********************************************************************
772 *
773 * Procedure to launch the po reschedule concurrent program
774 *
775 ***********************************************************************/
776 PROCEDURE launch_reschedule_po(
777 p_user_name IN VARCHAR2,
778 p_resp_name IN VARCHAR2,
779 p_batch_id IN NUMBER,
780 p_instance_id IN NUMBER,
781 p_instance_code IN varchar2,
782 p_dblink IN varchar2,
783 x_req_id OUT NOCOPY NUMBER
784 ) IS
785
786 l_result BOOLEAN;
787
788 errbuf VARCHAR2(10000);
789 retcode VARCHAR2(10000);
790 msg VARCHAR2(10000);
791 BEGIN
792
793 init_instance(p_user_name, p_resp_name);
794 l_result := fnd_request.set_mode(TRUE);
795
796 x_req_id := FND_REQUEST.SUBMIT_REQUEST('MSC',
797 'MSC_PO_RESCHEDULE',
798 NULL,
799 null,
800 FALSE, p_batch_id,p_instance_id,p_instance_code,p_dblink);
801
802
803 IF nvl(x_req_id,0) = 0 THEN
804 DEBUG_MESSAGE('Error in MSC_PO_RESCHEDULE');
805 fnd_message.retrieve(msg);
806 debug_message(msg);
807 ELSE
808 DEBUG_MESSAGE('Concurrent Request ID For PO Reschedule : ' || x_req_id);
809 END IF;
810
811 EXCEPTION
812 WHEN OTHERS THEN
813 x_req_id := 0;
814 DEBUG_MESSAGE('Error in launch_po_reschedule : Err OTHERS');
815 RAISE;
816
817 END launch_reschedule_po;
818
819 END mrp_po_reschedule;