1 PACKAGE BODY po_reschedule_pkg as
2 /* $Header: POXRSCHB.pls 120.7 2010/12/13 09:13:45 lswamina ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6
7 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_RESCHEDULE_PKG';
8 g_module_prefix CONSTANT VARCHAR2(40) := 'po.plsql.' || g_pkg_name || '.';
9
10 -- START Forward declarations for private procedures:
11 PROCEDURE add_error_to_msg_list (
12 p_api_name VARCHAR2,
13 p_message VARCHAR2
14 );
15 -- END Forward declarations for private procedures
16
17 -- <APS FPJ START>
18 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
19 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
20
21 -- Forward declare private procedure po_reschedule_req
22 PROCEDURE PO_RESCHEDULE_REQ (
23 p_line_location_id_tbl IN po_tbl_number,
24 p_estimated_pickup_dates IN po_tbl_date,
25 p_ship_methods IN po_tbl_varchar30,
26 x_return_status OUT NOCOPY VARCHAR2
27 ); -- Bug 5255550
28 -- <APS FPJ END>
29 -- 2279541 added new parameter x_shipment_num
30 -- bug 5255550 : Overloaded the reschedule API for backward compatibility
31 Function RESCHEDULE (
32 X_need_by_date_old date,
33 X_need_by_date date,
34 X_po_header_id number,
35 X_po_line_id number,
36 X_supply_number varchar2,
37 X_shipment_num number,
38 p_estimated_pickup_date DATE,
39 p_ship_method VARCHAR2
40 ) Return boolean IS
41
42 l_need_by_dates_old po_tbl_date;
43 l_need_by_dates po_tbl_date;
44 l_po_line_ids po_tbl_number;
45 l_shipment_nums po_tbl_number;
46 l_estimated_pickup_dates po_tbl_date;
47 l_ship_methods po_tbl_varchar30;
48 l_api_name CONSTANT VARCHAR2(30) := 'RESCHEDULE-1';
49 l_result BOOLEAN;
50
51 BEGIN
52
53 SELECT X_need_by_date_old,
54 X_need_by_date,
55 X_po_line_id
56 /* Bug 5610714 ,
57 X_shipment_num,
58 p_estimated_pickup_date,
59 p_ship_method */
60 BULK COLLECT INTO
61 l_need_by_dates_old,
62 l_need_by_dates,
63 l_po_line_ids
64 /* Bug 5610714,
65 l_shipment_nums,
66 l_estimated_pickup_dates,
67 l_ship_methods */
68 FROM DUAL;
69
70 IF X_shipment_num IS NOT NULL THEN
71 -- 5610714 Initialize the Collection with the parameter value
72 l_shipment_nums := po_tbl_number(X_shipment_num);
73 END IF;
74
75 IF p_estimated_pickup_date IS NOT NULL THEN
76 -- 5610714 Initialize the Collection with the parameter value
77 l_estimated_pickup_dates := po_tbl_date(p_estimated_pickup_date);
78 END IF;
79
80 IF p_ship_method IS NOT NULL THEN
81 -- 5610714 Initialize the Collection with the parameter value
82 l_ship_methods := po_tbl_varchar30(p_ship_method);
83 END IF;
84
85
86 l_result:= RESCHEDULE(X_need_by_dates_old => l_need_by_dates_old,
87 X_need_by_dates => l_need_by_dates,
88 X_po_header_id => X_po_header_id,
89 X_po_line_ids => l_po_line_ids,
90 X_supply_number => X_supply_number,
91 X_shipment_nums => l_shipment_nums,
92 p_estimated_pickup_dates =>l_estimated_pickup_dates,
93 p_ship_methods => l_ship_methods);
94
95 RETURN(l_result);
96
97 EXCEPTION
98 WHEN OTHERS THEN
99 add_error_to_msg_list ( p_api_name => l_api_name,
100 p_message => 'An exception Occurred ');
101 RAISE FND_API.G_EXC_ERROR;
102 END;
103
104 --bug9693292<START>
105 Function RESCHEDULE ( X_need_by_dates_old po_tbl_date,
106 X_need_by_dates po_tbl_date,
107 X_po_header_id number,
108 X_po_line_ids po_tbl_number,
109 X_supply_number varchar2,
110 X_shipment_nums po_tbl_number,
111 p_estimated_pickup_dates po_tbl_date,
112 p_ship_methods po_tbl_varchar30
113 ) Return boolean IS
114 l_result BOOLEAN;
115 l_api_name CONSTANT VARCHAR2(30) := 'RESCHEDULE-2';
116 l_error_message po_tbl_varchar2000;
117 BEGIN
118 l_result:= RESCHEDULE (X_need_by_dates_old => X_need_by_dates_old,
119 X_need_by_dates => X_need_by_dates,
120 X_po_header_id => X_po_header_id,
121 X_po_line_ids => X_po_line_ids,
122 X_supply_number => X_supply_number,
123 X_shipment_nums => X_shipment_nums,
124 p_estimated_pickup_dates =>p_estimated_pickup_dates,
125 p_ship_methods => p_ship_methods,
126 X_error_message => l_error_message);
127
128 RETURN(l_result);
129 EXCEPTION
130 WHEN OTHERS THEN
131 add_error_to_msg_list ( p_api_name => l_api_name,
132 p_message => 'An exception Occurred ');
133 RAISE FND_API.G_EXC_ERROR;
134 END;
135 --bug9693292<END>
136
137
138 -- bug 5255550 : Reschedule API rewrite
139 -- Previously, the reschedule api was called once per line by APS.
140 -- This was not only performance intensive, but also made the reschedule
141 -- program to fail, since approval launched on the first call to resched.
142 -- api might put the doc in 'IN PROCESS' state due to which the
143 -- successive calls might not be able to make any modifications to the doc.
144 -- Now APS will pass all the lines/shipments for 1 PO/Release at once.
145
146 -- 2279541 added new parameter x_shipment_num
147 Function RESCHEDULE (
148 X_need_by_dates_old po_tbl_date,
149 X_need_by_dates po_tbl_date,
150 X_po_header_id number,
151 X_po_line_ids po_tbl_number,
152 X_supply_number VARCHAR2,
153 X_shipment_nums po_tbl_number,
154 p_estimated_pickup_dates po_tbl_date,
155 p_ship_methods po_tbl_varchar30,
156 x_error_message OUT NOCOPY po_tbl_varchar2000 --bug9693292
157 ) Return boolean IS
158
159 l_po_release_id number;
160 X_po_type varchar2(25) := 'RELEASE';
161 x_progress varchar2(300);
162 x_pare_right number;
163 x_pare_left number;
164 x_release_num number;
165
166 -- <PO_CHANGE_API FPJ START>
167 -- In 115.13, modified the PO Reschedule API to call the PO Change API
168 -- instead of updating the database tables directly.
169 -- Bug 5255550
170 l_original_org_context VARCHAR2(10) := null;
171 l_document_org_id NUMBER;
172 l_changes PO_CHANGES_REC_TYPE;
173 l_shipment_changes PO_SHIPMENTS_REC_TYPE;
174 l_line_location_id_tbl PO_TBL_NUMBER;
175 l_need_by_date_tbl PO_TBL_DATE;
176 l_nbd_validate_tbl PO_TBL_VARCHAR2000:=NULL;
177 l_return_status VARCHAR2(1);
178 l_api_errors PO_API_ERRORS_REC_TYPE;
179 l_release_type PO_RELEASES_ALL.RELEASE_TYPE%TYPE;
180 l_session_gt_key NUMBER;
181 l_api_name CONSTANT VARCHAR2(30) := 'RESCHEDULE-3';
182 l_log_head CONSTANT VARCHAR2(100):= g_module_prefix||l_api_name;
183 l_progress VARCHAR2(3):='000';
184 l_promised_by_date_tbl PO_TBL_DATE; --Bug5633563
185 l_need_by_promise_def_prf VARCHAR2(1); --Bug5633563
186 -- <PO_CHANGE_API FPJ END>
187
188 --Bug9693292<START>
189 x_error_count NUMBER;
190 supply_number_tbl po_tbl_varchar30 ; --bug9787555/9830560
191 line_number_tbl po_tbl_number ;
192 shipment_number_tbl po_tbl_number;
193 ascp_date_tbl po_tbl_varchar30;
194 po_date_tbl po_tbl_varchar30;
195 --Bug9693292<END>
196
197
198 BEGIN
199 --Bug 5255550
200
201 -- Step1 get the release id from x_supply number into l_po_release_id;
202 -- debug <Start>
203 po_debug.debug_begin(l_log_head);
204 -- debug <End>
205
206 select instr(x_supply_number, '(')
207 into x_pare_left
208 from dual;
209
210 if x_pare_left <= 0 then
211 X_po_type := 'PO';
212 ELSE
213 x_po_type := 'RELEASE';
214 end if;
215 -- debug <START>
216 PO_DEBUG.debug_var(l_log_head,l_progress,'x_po_type', x_po_type);
217 -- debug <END>
218
219 l_progress:= '010';
220 if X_po_type <> 'PO' then
221 -- Get the index of ')' in the supply number
222 select instr(x_supply_number, ')')
223 into x_pare_right
224 from dual;
225
226 if x_pare_right <=0 then
227 -- No ending parentheis.
228 add_error_to_msg_list (
229 p_api_name => l_api_name,
230 p_message => 'Incorrect format for supply number: '||x_supply_number
231 );
232 RAISE FND_API.G_EXC_ERROR;
233 end if;
234
235 select to_number(substr(x_supply_number, x_pare_left+1,
236 x_pare_right - x_pare_left-1))
237 into x_release_num
238 from dual;
239
240
241 l_progress:= '020';
242 -- debug <START>
243 PO_DEBUG.debug_var(l_log_head,l_progress,'x_release_num', x_release_num);
244 -- debug <END>
245
246 begin
247 -- Get the po_release_id out of supply_number
248 select po_release_id,release_type
249 into l_po_release_id,l_release_type
250 from po_releases_all
251 where po_header_id = x_po_header_id
252 and release_num = x_release_num;
253
254 l_progress:= '030';
255 -- debug <START>
256 PO_DEBUG.debug_var(l_log_head,l_progress,'l_po_release_id', l_po_release_id);
257 -- debug <END>
258
259 exception
260 when no_data_found then
261 -- Wrong format for supply number
262 add_error_to_msg_list (
263 p_api_name => l_api_name,
264 p_message => 'Incorrect format for supply number: '||x_supply_number
265 );
266 RAISE FND_API.G_EXC_ERROR;
267 end;
268 end if;
269
270 l_progress:= '040';
271 /* Step 2: save the existing org_context. get the org_id from
272 po_header_id and set the context to this org_id */
273 l_original_org_context := PO_MOAC_UTILS_PVT.get_current_org_id ; -- <R12 MOAC>
274
275 -- debug <START>
276 PO_DEBUG.debug_var(l_log_head,l_progress,'l_original_org_context', l_original_org_context);
277 -- debug <END>
278
279 --Bug 5255550
280 BEGIN
281 -- Retrieve the document's operating unit.
282 IF (x_po_type = 'RELEASE') THEN
283 SELECT org_id
284 INTO l_document_org_id
285 FROM po_releases_all
286 WHERE po_release_id = l_po_release_id;/*Bug 5255550*/
287 ELSE -- PO
288 SELECT org_id
289 INTO l_document_org_id
290 FROM po_headers_all
291 WHERE po_header_id = x_po_header_id;
292 END IF; -- x_po_type
293
294 l_progress:= '050';
295 -- debug <START>
296 PO_DEBUG.debug_var(l_log_head,l_progress,'l_document_org_id', l_document_org_id);
297 -- debug <END>
298
299 -- Set the org context to the document's OU.
300 PO_MOAC_UTILS_PVT.set_org_context(l_document_org_id) ; -- <R12 MOAC>
301 EXCEPTION
302 WHEN NO_DATA_FOUND THEN
303 add_error_to_msg_list (
304 p_api_name => l_api_name,
305 p_message => 'Could not obtain the document operating unit.'
306 );
307 RAISE FND_API.G_EXC_ERROR;
308 END;
309 /*STEP 3: get all the shipments */
310
311 /* It is not possible to use forall, and bulk collect simultaneously.
312 Hence for performance reasons, we will use scratch pad, to insert data
313 and then use bulk collect to get all the data in plsql table*/
314
315
316 /* If l_po_release_id is present, it means that the document is a release */
317 l_session_gt_key := PO_CORE_S.get_session_gt_nextval();
318
319 l_progress:= '060';
320 -- debug <START>
321 PO_DEBUG.debug_var(l_log_head,l_progress,'l_session_gt_key', l_session_gt_key);
322 -- debug <END>
323
324 -- Bug 5610714 replacing x_shipment_nums.Count with X_po_line_ids.Count as x_shipment_nums can be Null
325 -- Bug 5610714 Added the following If Condition to handle the issue of passing x_shipment_nums as Null.
326 -- x_shipment_nums(1) will be Null if this call is routed thru overloaded RESCHEDULE api.
327
328 If x_shipment_nums is Null then
329 forall i in 1..X_po_line_ids.Count
330
331
332 /*
333 po_session_gt map:
334 KEY : unique key for the current session processing
335 NUM1 : line_location_id
336 NUM2 : shipment_num
337 DATE1: db dates in po_line_locations_all
338 DATE2: new need by dates passed in
339 CHAR1: old need by dates passed in ,
340 using the char field since session_gt has only 2 date columns
341 CHAR2: indicates a valid record
342
343 */
344
345 insert into po_session_gt (key,
346 num1,
347 num2,
348 num3, --bug9693292
349 date1,
350 date2,
351 char1, -- we're using this for date passed in.
352 char2 -- we're using this to mark a valid record
353 )
354 select l_session_gt_key,
355 pll.line_location_id,
356 pll.shipment_num,
357 X_po_line_ids(i), --bug9693292
358 -- Have picked this from the existing code. From the planning perspective
359 -- promised date becomes important and should be preferred over nbd
360 Nvl(pll.promised_date,pll.need_by_date),
361 X_need_by_dates(i),
362 To_Char(X_need_by_dates_old(i),'DD-MON-YYYY:hh:mi:ss'),
363 'Y'
364 from po_line_locations_all pll
365 where pll.po_line_id = X_po_line_ids(i)
366 and pll.po_header_id = X_po_header_id
367 and (l_po_release_id is NULL OR
368 pll.po_release_id = l_po_release_id);
369
370 ELSE
371 forall i in 1..X_po_line_ids.Count
372
373 /*
374 po_session_gt map:
375 KEY : unique key for the current session processing
376 NUM1 : line_location_id
377 NUM2 : shipment_num
378 DATE1: db dates in po_line_locations_all
379 DATE2: new need by dates passed in
380 CHAR1: old need by dates passed in ,
381 using the char field since session_gt has only 2 date columns
382 CHAR2: indicates a valid record
383
384 */
385
386 insert into po_session_gt (key,
387 num1,
388 num2,
389 num3, --bug9693292
390 date1,
391 date2,
392 char1, -- we're using this for date passed in.
393 char2 -- we're using this to mark a valid record
394 )
395 select l_session_gt_key,
396 pll.line_location_id,
397 X_shipment_nums(i),
398 X_po_line_ids(i), --bug9693292
399 -- Have picked this from the existing code. From the planning perspective
400 -- promised date becomes important and should be preferred over nbd
401 Nvl(pll.promised_date,pll.need_by_date),
402 X_need_by_dates(i),
403 To_Char(X_need_by_dates_old(i),'DD-MON-YYYY:hh:mi:ss'),
404 'Y'
405 from po_line_locations_all pll
406 where pll.po_line_id = X_po_line_ids(i)
407 and pll.shipment_num = Nvl(X_shipment_nums(i), pll.shipment_num)
408 and pll.po_header_id = X_po_header_id
409 and (l_po_release_id is NULL OR
410 pll.po_release_id = l_po_release_id);
411 End If; -- End Of Bug 5610714
412
413 l_progress:= '065';
414 --bug9693292<START>
415 --update po_session_gt with line number as the error message should contain the
416 --line numbers
417 UPDATE po_session_gt scratch
418 SET num4 = (SELECT line_num FROM po_lines_all WHERE po_line_id = scratch.num3);
419 --bug9693292<END>
420
421 --Validations <START>
422
423 -- Validation #1 : Old Need by date passed and the db need by date should
424 -- Match
425
426 l_progress:= '070';
427 UPDATE po_session_gt scratch
428 SET char2 = 'N'
429 WHERE scratch.char1 <>
430 To_char(scratch.date1,'DD-MON-YYYY:hh:mi:ss')
431 AND scratch.KEY = l_session_gt_key;
432
433 l_progress:= '080';
434 --bug9693292<START>
435 --We select the values for the tokens to be substituted in the error message
436 SELECT x_supply_number,scratch.num4,scratch.num2,scratch.char1, To_char(scratch.date1,'DD-MON-YYYY:hh:mi:ss')
437 BULK collect INTO supply_number_tbl,line_number_tbl,shipment_number_tbl,ascp_date_tbl,po_date_tbl
438 FROM po_session_gt scratch
439 WHERE scratch.KEY = l_session_gt_key
440 AND Nvl(scratch.char2,'Y') = 'N';
441
442 x_error_message := po_tbl_varchar2000();
443
444 IF (supply_number_tbl.Count > 0) THEN
445 FOR i IN 1..supply_number_tbl.Count LOOP
446 FND_MESSAGE.SET_NAME('PO','PO_NEED_BY_DATE_MISMATCH');
447 FND_MESSAGE.SET_TOKEN ('SUPPLY_NUMBER',supply_number_tbl(i));
448 FND_MESSAGE.SET_TOKEN ('LINE_NUMBER',line_number_tbl(i));
449 FND_MESSAGE.SET_TOKEN ('SHIPMENT_NUMBER',shipment_number_tbl(i));
450 FND_MESSAGE.SET_TOKEN ('ASCP_DATE',ascp_date_tbl(i));
451 FND_MESSAGE.SET_TOKEN ('PO_DATE',po_date_tbl(i));
452 x_error_message.extend;
453 x_error_message(i) := fnd_message.get;
454 add_error_to_msg_list ( p_api_name => l_api_name,
455 p_message => x_error_message(i) );
456 END LOOP;
457 END IF;
458
459 --we ll delete the contents in these collections to repopulate
460 supply_number_tbl.DELETE;
461 line_number_tbl.DELETE;
462 shipment_number_tbl.DELETE;
463
464 --bug9693292<END>
465
466 -- Validation 2: New need by date should not be NULL
467 l_progress:= '090';
468 UPDATE po_session_gt scratch
469 SET char2 = 'N'
470 WHERE scratch.date2 IS NULL
471 AND scratch.KEY = l_session_gt_key;
472
473 --bug9693292<START>
474
475 SELECT x_supply_number,scratch.num4,scratch.num2
476 BULK collect INTO supply_number_tbl,line_number_tbl,shipment_number_tbl
477 FROM po_session_gt scratch
478 WHERE scratch.KEY = l_session_gt_key
479 AND scratch.date2 IS NULL;
480 x_error_count := x_error_message.Count;
481 IF (supply_number_tbl.Count > 0) THEN
482 FND_MESSAGE.SET_NAME('PO','PO_NEED_BY_DATE_NULL');
483 FOR i IN 1..supply_number_tbl.Count LOOP
484 FND_MESSAGE.SET_TOKEN ('SUPPLY_NUMBER',supply_number_tbl(i));
485 FND_MESSAGE.SET_TOKEN ('LINE_NUMBER',line_number_tbl(i));
486 FND_MESSAGE.SET_TOKEN ('SHIPMENT_NUMBER',shipment_number_tbl(i));
487 x_error_message.extend;
488 x_error_message(x_error_count+i) := fnd_message.get;
489 add_error_to_msg_list ( p_api_name => l_api_name,
490 p_message => x_error_message(i) );
491 END LOOP;
492 END IF;
493 --bug9693292<END>
494
495 --Validations <END>
496
497 /*Step 4: construct two tables one for shipments, and one for need_by_dates*/
498 l_progress:= '100';
499
500 select num1, date2
501 bulk collect into l_line_location_id_tbl, l_need_by_date_tbl
502 from po_session_gt SCRATCH
503 WHERE KEY = l_session_gt_key
504 AND Nvl(scratch.char2,'Y') = 'Y' ;
505 /* Step 5:
506
507 if p_estimate_date, p_ship_method is not null then call reschedule req
508 -- should be in the loop,
509 -- since one po/release can be linked to different reqs.*/
510
511 -- should not do this for a scheduled release.
512 l_progress:= '110';
513
514 l_progress:= '120';
515 IF ((p_estimated_pickup_dates IS NOT NULL) OR (p_ship_methods IS NOT NULL))
516 AND (Nvl(l_release_type,'PO') <> 'SCHEDULED') THEN
517
518 l_progress:= '130';
519 PO_RESCHEDULE_REQ(
520 p_line_location_id_tbl => l_line_location_id_tbl,
521 p_estimated_pickup_dates => p_estimated_pickup_dates,
522 p_ship_methods => p_ship_methods,
523 x_return_status => l_return_status);
524
525 -- debug <START>
526 PO_DEBUG.debug_var(l_log_head,l_progress,'l_return_status', l_return_status);
527 -- debug <END>
528
529 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
530 RAISE FND_API.G_EXC_ERROR;
531 END IF;
532 END IF;
533 -- Construct the shipment changes object.
534 /*Bug 5633563
535 If profile option PO: Default PO Promise Date from Need By Date
536 is set then new need_by_date must be copied to promised_by_date
537 */
538 fnd_profile.get('PO_NEED_BY_PROMISE_DEFAULTING', l_need_by_promise_def_prf);
539 IF nvl(l_need_by_promise_def_prf,'N') = 'Y' THEN
540 l_promised_by_date_tbl := PO_TBL_DATE();
541 select date2
542 bulk collect into l_promised_by_date_tbl
543 from po_session_gt SCRATCH
544 WHERE KEY = l_session_gt_key
545 AND Nvl(scratch.char2,'Y') = 'Y' ;
546 l_shipment_changes := PO_SHIPMENTS_REC_TYPE.create_object (
547 p_po_line_location_id => l_line_location_id_tbl,
548 p_need_by_date => l_need_by_date_tbl,
549 p_promised_date=> l_promised_by_date_tbl
550 );
551 ELSE
552 l_shipment_changes := PO_SHIPMENTS_REC_TYPE.create_object (
553 p_po_line_location_id => l_line_location_id_tbl,
554 p_need_by_date => l_need_by_date_tbl
555 );
556 END IF;
557 /*Bug 5633563 :old code
558 l_shipment_changes := PO_SHIPMENTS_REC_TYPE.create_object (
559 p_po_line_location_id => l_line_location_id_tbl,
560 p_need_by_date => l_need_by_date_tbl
561 );5633563*/
562
563 -- Construct the document changes object.
564 l_changes := PO_CHANGES_REC_TYPE.create_object (
565 p_po_header_id => x_po_header_id,
566 p_po_release_id => l_po_release_id,
567 p_shipment_changes => l_shipment_changes
568 );
569
570 /*Step 7: Call update_documents; */
571 l_progress:= '150';
572 PO_DOCUMENT_UPDATE_PVT.update_document(
573 p_api_version => 1.0,
574 p_init_msg_list => FND_API.G_TRUE,
575 x_return_status => l_return_status,
576 p_changes => l_changes,
577 p_run_submission_checks => FND_API.G_FALSE,
578 p_launch_approvals_flag => FND_API.G_TRUE,
579 p_buyer_id => NULL,
580 p_update_source => NULL,
581 p_override_date => NULL,
582 x_api_errors => l_api_errors
583 );
584
585 l_progress:= '160';
586 /* Step 8: add the errors to the error list.*/
587 -- debug <START>
588 PO_DEBUG.debug_stmt(l_log_head,l_progress,'After return from update document');
589 PO_DEBUG.debug_var(l_log_head,l_progress,'l_return_status', l_return_status);
590 -- debug <END>
591
592 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
593 x_error_count := x_error_message.Count; --bug9693292
594 -- PO Change API returned some errors. Add them to the API message list.
595 FND_MSG_PUB.initialize;
596 FOR i IN 1..l_api_errors.message_text.COUNT LOOP
597 add_error_to_msg_list ( p_api_name => l_api_name,
598 p_message => l_api_errors.message_text(i) );
599 --bug9693292<START>
600 x_error_message.extend;
601 x_error_message(x_error_count+i) := l_api_errors.message_text(i);
602 --bug9693292<END>
603 END LOOP;
604 RAISE FND_API.G_EXC_ERROR;
605 END IF;
606
607 l_progress:= '160';
608 -- debug <START>
609 PO_DEBUG.debug_stmt(l_log_head,l_progress,'setting the context back to '
610 ||l_original_org_context);
611 -- debug <END>
612 /* Step 9: set the context back to the orginal context.*/
613 PO_MOAC_UTILS_PVT.set_org_context(l_original_org_context) ; -- <R12 MOAC>
614
615 /* Delete all the data from po_session_gt */
616 l_progress:= '170';
617
618 -- debug <START>
619 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Deleting data from session gt '
620 );
621 -- debug <END>
622
623 DELETE FROM po_session_gt
624 WHERE KEY = l_session_gt_key;
625
626 /*Step 10 : return from the api */
627 PO_DEBUG.debug_end(l_log_head);
628 return TRUE;
629
630
631 EXCEPTION
632 WHEN FND_API.G_EXC_ERROR THEN
633 -- Set the org context back to the original operating unit.
634 IF (l_original_org_context IS NOT NULL) THEN
635 PO_MOAC_UTILS_PVT.set_org_context(l_original_org_context) ; -- <R12 MOAC>
636 END IF;
637 DELETE FROM po_session_gt
638 WHERE KEY = l_session_gt_key;
639 RETURN FALSE;
640
641 WHEN OTHERS THEN
642 DELETE FROM po_session_gt
643 WHERE KEY = l_session_gt_key;
644 IF (l_original_org_context IS NOT NULL) THEN
645 FND_CLIENT_INFO.set_org_context(l_original_org_context);
646 END IF;
647
648 END RESCHEDULE;
649
650
651
652 -------------------------------------------------------------------------------
653 --Start of Comments
654 --Name: reschedule_req
655 --Function:
656 --Pre-reqs:
657 -- None.
658 --Modifies:
659 -- po_requistion_lines_all
660 --Locks:
661 -- None.
662 -- 1. Given release_id and possible shipment_num, query out the backing
663 -- requistion lines and update them with new estimated_pickup_date and
664 -- ship_method.
665 -- 2. Given po_line_id and possible shipment_num, query out the backing
666 -- requistion lines and update them with new estimated_pickup_date and
667 -- ship_method.
668 --IN:
669 --l_line_location_id_tbl
670 -- Specifies collection of line_location_ids
671 --p_estimated_pickup_date
672 -- Specifies new estimated_pickup_dates.
673 --p_ship_method
674 -- Sepcifies new ship_methods.
675 --OUT:
676 --x_return_status
677 -- Indicates API return status as 'S' or 'U'.
678 --End of Comments
679 -------------------------------------------------------------------------------
680 PROCEDURE PO_RESCHEDULE_REQ (
681 p_line_location_id_tbl IN po_tbl_number,
682 p_estimated_pickup_dates IN po_tbl_date,
683 p_ship_methods IN po_tbl_varchar30,
684 x_return_status OUT NOCOPY VARCHAR2
685 ) IS
686 l_api_name CONSTANT VARCHAR2(30) := 'PO_RESCHEDULE_REQ';
687 l_log_head CONSTANT VARCHAR2(200) := g_module_prefix || l_api_name;
688 l_progress VARCHAR2(3);
689 BEGIN
690 -- Bug 5255550
691 x_return_status := FND_API.G_RET_STS_SUCCESS;
692 l_progress:= '000';
693 PO_DEBUG.debug_begin(l_log_head);
694
695 --SQL What: line_location_id, query out
696 --SQL the backing requistion lines and update them with new
697 --SQL estimated_pickup_dates and ship_methods
698 --SQL Where:all req lines associated to the shipments, and having
699 --SQL estimated_pickup_dates, ship_methods.
700 --SQL Why: Same as SQL What
701
702 forall i in 1..p_line_location_id_tbl.count
703 UPDATE po_requisition_lines_all REQ
704 SET REQ.estimated_pickup_date =
705 NVL(p_estimated_pickup_dates(i), REQ.estimated_pickup_date),
706 REQ.ship_method = NVL(p_ship_methods(i), REQ.ship_method)
707 WHERE REQ.line_location_id = p_line_location_id_tbl(i);
708
709 l_progress:= '020';
710
711 PO_DEBUG.debug_end(l_log_head);
712
713 EXCEPTION
714 WHEN OTHERS THEN
715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
716 FND_MSG_PUB.initialize;
717 add_error_to_msg_list (
718 p_api_name => l_api_name,
719 p_message => 'Can not update estimated_pickup_date and ship_method on requisitions');
720
721 IF (g_debug_unexp) THEN
722 PO_DEBUG.debug_exc (
723 p_log_head => l_log_head,
724 p_progress => l_progress || 'with sqlcode' || sqlcode);
725 END IF;
726
727 END PO_RESCHEDULE_REQ;
728 -- <APS FPJ END>
729
730 -- <PO_CHANGE_API FPJ START>
731 -------------------------------------------------------------------------------
732 --Start of Comments
733 --Name: add_error_to_msg_list
734 --Function:
735 -- Adds the given error message to the standard API message list and
736 -- to the FND log, if enabled.
737 --End of Comments
738 -------------------------------------------------------------------------------
739 PROCEDURE add_error_to_msg_list (
740 p_api_name VARCHAR2,
741 p_message VARCHAR2
742 ) IS
743 BEGIN
744 -- Add a generic error to the API message list.
745 FND_MESSAGE.set_name ('PO', 'PO_GENERIC_ERROR');
746 FND_MESSAGE.set_token ('ERROR_TEXT', p_message);
747 FND_MSG_PUB.add;
748
749 -- Also add it to the FND log, if enabled.
750 IF (g_fnd_debug = 'Y') THEN
751 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
752 FND_LOG.string( FND_LOG.LEVEL_ERROR, g_module_prefix || p_api_name,
753 p_message);
754 END IF;
755 END IF;
756 END add_error_to_msg_list;
757 -- <PO_CHANGE_API FPJ END>
758
759 -- <PO_CHANGE_API FPJ START>
760 -- In 115.13, removed the following private procedures from this package,
761 -- because their functionality is now handled by the PO Change API:
762 -- update_po_tables, update_po_tables_rel, check_revision_number,
763 -- wf_approve_doc, SetupWorkflow
764 -- <PO_CHANGE_API FPJ END>
765
766 END PO_RESCHEDULE_PKG;
767
768
[Home] [Help]