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