1 PACKAGE BODY OE_RMA_GRP AS
2 /* $Header: OEXGRMAB.pls 120.1 2006/03/10 12:07:08 mchavan noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_RMA_GRP';
5
6 /*
7 ** Submit_Ordeer() will progress the return order header forward
8 ** from Awaiting Submission once the user submits the order.
9 */
10 PROCEDURE Submit_Order(
11 p_api_version IN NUMBER
12 , p_header_id IN NUMBER
13 , x_return_status OUT NOCOPY VARCHAR2
14 , x_msg_count OUT NOCOPY NUMBER
15 , x_msg_data OUT NOCOPY VARCHAR2
16 )
17 IS
18 l_api_version CONSTANT NUMBER := 1.0;
19 l_api_name CONSTANT VARCHAR2(30):= 'Submit_Order';
20
21 l_header_rec OE_Order_PUB.Header_Rec_Type;
22
23 --
24 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
25 --
26 BEGIN
27
28 x_return_status := FND_API.G_RET_STS_SUCCESS;
29
30 IF l_debug_level > 0 THEN
31 oe_debug_pub.add('OEXGRMAB: ENTERING SUBMIT_ORDER, HEADER ID: '||P_HEADER_ID) ;
32 END IF;
33
34 /* Standard call to check for call compatibility */
35
36 IF NOT FND_API.Compatible_API_Call
37 ( l_api_version
38 , p_api_version
39 , l_api_name
40 , G_PKG_NAME
41 )
42 THEN
43 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
44 END IF;
45
46 OE_Header_Util.Query_Row(p_header_id => p_header_id,
47 x_header_rec => l_header_rec);
48
49 IF l_debug_level > 0 THEN
50 oe_debug_pub.add('OEXGRMAB: AFTER CALLING QUERY ROW') ;
51 END IF;
52
53 /* Complete the Wait for Submission activity */
54
55 WF_ENGINE.CompleteActivityInternalName( 'OEOH'
56 , to_char(p_header_id)
57 , 'RMA_WAIT_FOR_SUBMISSION'
58 , 'COMPLETE'
59 );
60
61 IF l_debug_level > 0 THEN
62 oe_debug_pub.add('OEXGRMAB: AFTER COMPLETING THE WAIT FOR SUBMISSION ACTIVITY') ;
63 END IF;
64
65 /* Update the ordered_date with the system date */
66
67 l_header_rec.ordered_date := SYSDATE;
68 l_header_rec.last_updated_by := FND_GLOBAL.USER_ID;
69 l_header_rec.last_update_date := SYSDATE;
70 l_header_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
71
72 UPDATE oe_order_headers
73 SET ordered_date = l_header_rec.ordered_date
74 , last_updated_by = l_header_rec.last_updated_by
75 , last_update_date = l_header_rec.last_update_date
76 , last_update_login = l_header_rec.last_update_login
77 WHERE header_id = p_header_id;
78
79 IF l_debug_level > 0 THEN
80 oe_debug_pub.add('OEXGRMAB: AFTER UPDATING ORDERED DATE ON HEADER');
81 END IF;
82
83 /* Update the Header Cache with the new information */
84
85 OE_Order_Cache.Set_Order_Header(l_header_rec);
86
87 IF l_debug_level > 0 THEN
88 oe_debug_pub.add('OEXGRMAB: AFTER CALLING UPDATE FLOW STATUS CODE, RETURN STATUS: '||X_RETURN_STATUS);
89 END IF;
90
91 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
92 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
93 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
94 RAISE FND_API.G_EXC_ERROR;
95 END IF;
96
97 IF l_debug_level > 0 THEN
98 oe_debug_pub.add('OEXGRMAB: EXITING SUBMIT_ORDER');
99 END IF;
100
101 EXCEPTION
102
103 WHEN FND_API.G_EXC_ERROR THEN
104 x_return_status := FND_API.G_RET_STS_ERROR;
105 OE_MSG_PUB.Get
106 ( p_msg_index => OE_MSG_PUB.G_LAST
107 , p_encoded => FND_API.G_FALSE
108 , p_data => x_msg_data
109 , p_msg_index_out => x_msg_count
110 );
111
112 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
113 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
114 OE_MSG_PUB.Get
115 ( p_msg_index => OE_MSG_PUB.G_LAST
116 , p_encoded => FND_API.G_FALSE
117 , p_data => x_msg_data
118 , p_msg_index_out => x_msg_count
119 );
120
121 WHEN OTHERS THEN
122 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
123 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
124 THEN
125 FND_MSG_PUB.Add_Exc_Msg
126 ( G_PKG_NAME
127 , 'Submit_Order'
128 );
129 END IF;
130 OE_MSG_PUB.Get
131 ( p_msg_index => OE_MSG_PUB.G_LAST
132 , p_encoded => FND_API.G_FALSE
133 , p_data => x_msg_data
134 , p_msg_index_out => x_msg_count
135 );
136 END Submit_Order;
137
138 /*
139 ** Is_Over_Return() will check if returned qty matches the original
140 ** ordered qty. If returned qty exceeds the original ordered qty, the
141 ** API will raise an error.
142 **
143 ** NOTE that this api even looks at Unbooked Return Lines, which is
144 ** different from the Is_Over_Return() procedure in OE_LINE_UTIL Package.
145 */
146 PROCEDURE Is_Over_Return(
147 p_api_version IN NUMBER
148 , p_line_tbl IN OE_ORDER_PUB.LINE_TBL_TYPE
149 , x_error_tbl OUT NOCOPY OE_RMA_GRP.OVER_RETURN_ERR_TBL_TYPE
150 , x_return_status OUT NOCOPY VARCHAR2
151 , x_msg_count OUT NOCOPY NUMBER
152 , x_msg_data OUT NOCOPY VARCHAR2
153 )
154 IS
155 l_api_version CONSTANT NUMBER := 1.0;
156 l_api_name CONSTANT VARCHAR2(30):= 'Is_Over_Return';
157
158 l_index NUMBER;
159 l_ctr NUMBER := 0;
160 l_already_returned NUMBER;
161 l_original_ordered NUMBER;
162
163 --
164 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
165 --
166 BEGIN
167
168 x_return_status := FND_API.G_RET_STS_SUCCESS;
169
170 IF l_debug_level > 0 THEN
171 oe_debug_pub.add('OEXGRMAB: ENTERING IS_OVER_RETURN') ;
172 END IF;
173
174 /* Standard call to check for call compatibility */
175
176 IF NOT FND_API.Compatible_API_Call
177 ( l_api_version
178 , p_api_version
179 , l_api_name
180 , G_PKG_NAME
181 )
182 THEN
183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
184 END IF;
185
186 /* Validate returned qty for each line id in the Line Table */
187 l_index := p_line_tbl.FIRST;
188
189 WHILE l_index IS NOT NULL LOOP
190
191 IF l_debug_level > 0 THEN
192 oe_debug_pub.add('OEXGRMAB: VALIDATE LINE ID/ITEM ID: '||p_line_tbl(l_index).line_id||'/'
193 ||p_line_tbl(l_index).inventory_item_id) ;
194 END IF;
195
196 /* Get the total qty already returned for the referenced SO Line */
197
198 SELECT nvl(sum(ordered_quantity), 0)
199 INTO l_already_returned
200 FROM oe_order_lines
201 WHERE reference_line_id = p_line_tbl(l_index).reference_line_id
202 AND cancelled_flag <> 'Y'
203 AND line_category_code = 'RETURN'
204 AND line_id <> p_line_tbl(l_index).line_id;
205
206 /* Get the originally ordered qty on referenced SO line */
207
208 SELECT nvl(ordered_quantity, 0)
209 INTO l_original_ordered
210 FROM oe_order_lines
211 WHERE line_id = p_line_tbl(l_index).reference_line_id;
212
213 /* Check if the quantity is Over Returned */
214
215 IF (l_already_returned + p_line_tbl(l_index).ordered_quantity) > l_original_ordered THEN
216
217 IF l_debug_level > 0 THEN
218 oe_debug_pub.add('OEXGRMAB: LINE OVER RETURNED ERROR');
219 END IF;
220
221 FND_MESSAGE.Set_Name('ONT','OE_LINE_OVER_RETURNED');
222 FND_MESSAGE.Set_Token('LINE', p_line_tbl(l_index).line_number);
223 FND_MESSAGE.Set_Token('PREVIOUS', l_already_returned);
224 FND_MESSAGE.Set_Token('CURRENT', p_line_tbl(l_index).ordered_quantity);
225 FND_MESSAGE.Set_Token('ORIGINAL', l_original_ordered);
226 OE_MSG_PUB.Add;
227 /* No Need to Raise the Exception, Just Set the Return Status */
228 --Raise FND_API.G_EXC_ERROR;
229 x_return_status := FND_API.G_RET_STS_ERROR;
230
231 /*
232 ** Populate the Return Error Table to let iStore know
233 ** of the Return Lines which actually errored out.
234 */
235 l_ctr := l_ctr + 1;
236
237 x_error_tbl(l_ctr).line_id := p_line_tbl(l_index).line_id;
238 x_error_tbl(l_ctr).previous_quantity := l_already_returned;
239 x_error_tbl(l_ctr).current_quantity := p_line_tbl(l_index).ordered_quantity;
240 x_error_tbl(l_ctr).original_quantity := l_original_ordered;
241 x_error_tbl(l_ctr).return_status := FND_API.G_RET_STS_ERROR;
242
243 OE_MSG_PUB.Get
244 ( p_msg_index => OE_MSG_PUB.G_LAST
245 , p_encoded => FND_API.G_FALSE
246 , p_data => x_error_tbl(l_ctr).msg_data
247 , p_msg_index_out => x_error_tbl(l_ctr).msg_count
248 );
249
250 END IF;
251
252 l_index := p_line_tbl.NEXT(l_index);
253
254 END LOOP;
255
256 IF l_debug_level > 0 THEN
257 oe_debug_pub.add('OEXGRMAB: EXITING IS_OVER_RETURN');
258 END IF;
259
260 EXCEPTION
261
262 WHEN FND_API.G_EXC_ERROR THEN
263 x_return_status := FND_API.G_RET_STS_ERROR;
264 OE_MSG_PUB.Get
265 ( p_msg_index => OE_MSG_PUB.G_LAST
266 , p_encoded => FND_API.G_FALSE
267 , p_data => x_msg_data
268 , p_msg_index_out => x_msg_count
269 );
270
271 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
272 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
273 OE_MSG_PUB.Get
274 ( p_msg_index => OE_MSG_PUB.G_LAST
275 , p_encoded => FND_API.G_FALSE
276 , p_data => x_msg_data
277 , p_msg_index_out => x_msg_count
278 );
279
280 WHEN OTHERS THEN
281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
282 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
283 THEN
284 FND_MSG_PUB.Add_Exc_Msg
285 ( G_PKG_NAME
286 , 'Is_Over_Return'
287 );
288 END IF;
289 OE_MSG_PUB.Get
290 ( p_msg_index => OE_MSG_PUB.G_LAST
291 , p_encoded => FND_API.G_FALSE
292 , p_data => x_msg_data
293 , p_msg_index_out => x_msg_count
294 );
295
296 END Is_Over_Return;
297
298 /*
299 ** Post_Approval_Process() will be called from workflow activyt to do
300 ** some post approval steps. For example it will call iStore API that
301 ** sends the notification on approval of the return order to the end user.
302 */
303 PROCEDURE Post_Approval_Process(
304 itemtype IN VARCHAR2
305 , itemkey IN VARCHAR2
306 , actid IN NUMBER
307 , funcmode IN VARCHAR2
308 , resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
309 )
310 IS
311 l_order_source_id NUMBER;
312 l_notification_id NUMBER;
313 l_ntf_exists VARCHAR2(1) := 'Y';
314 l_comments VARCHAR2(4000);
315 l_block_str VARCHAR2(2000);
316 l_return_status VARCHAR2(1);
317 l_msg_count NUMBER;
318 l_msg_data VARCHAR2(2000);
319
320 CURSOR ntf
321 IS
322 SELECT wv.NOTIFICATION_ID
323 FROM
324 (
325 select IAS.NOTIFICATION_ID NOTIFICATION_ID, IAS.END_DATE END_DATE
326 from WF_PROCESS_ACTIVITIES PA,
327 WF_ITEM_ACTIVITY_STATUSES IAS
328 where IAS.ITEM_TYPE = itemtype
329 and IAS.ITEM_KEY = itemkey
330 and PA.ACTIVITY_NAME = 'RMA_ORDER_APPROVAL_NTF'
331 and IAS.NOTIFICATION_ID is not null
332 and IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
333 UNION ALL
334 select IAS.NOTIFICATION_ID, IAS.END_DATE
335 from WF_PROCESS_ACTIVITIES PA,
336 WF_ITEM_ACTIVITY_STATUSES_H IAS
337 where IAS.ITEM_TYPE = itemtype
338 and IAS.ITEM_KEY = itemkey
339 and PA.ACTIVITY_NAME = 'RMA_ORDER_APPROVAL_NTF'
340 and IAS.NOTIFICATION_ID is not null
341 and IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
342 ) wv
343 ORDER BY wv.end_date DESC ;
344
345 --
346 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
347 --
348 BEGIN
349
350 IF l_debug_level > 0 THEN
351 oe_debug_pub.add('OEXGRMAB: ENTERING POST_APPROVAL_PROCESS, ITEM TYPE/KEY: '||itemtype||'/'||itemkey) ;
352 END IF;
353
354 IF (FUNCMODE = 'RUN') THEN
355
356 IF itemtype = OE_GLOBALS.G_WFI_HDR THEN
357
358 /* Get the Order Source ID of the Order */
359
360 SELECT order_source_id
361 INTO l_order_source_id
362 FROM oe_order_headers
363 WHERE header_id = to_number(itemkey);
364
365 IF l_order_source_id = 13 THEN
366
367 /* Get Notification ID for Return Order Approval NTF */
368
369 OPEN ntf;
370
371 FETCH ntf INTO l_notification_id;
372 IF ntf%NOTFOUND THEN
373 l_ntf_exists := 'N';
374 END IF;
375
376 CLOSE ntf;
377
378 IF l_debug_level > 0 THEN
379 oe_debug_pub.add('OEXGRMAB: NOTIFICATION ID: '||l_notification_id) ;
380 END IF;
381
382 /* Get Approver Comments */
383
384 IF l_ntf_exists = 'Y' THEN
385 l_comments := WF_NOTIFICATION.GetAttrText(l_notification_id, 'WF_NOTE');
386 END IF;
387
388 /* Prepare dynamic SQL to call iStore API */
389
390 l_block_str :=
391 'DECLARE '||
392 'BEGIN '||
393 'IBE_OM_INTEGRATION_GRP.Notify_RMA_Request_Action(
394 p_api_version_number => 1.0
395 , p_order_header_id => :header_id
396 , p_notif_context => IBE_OM_INTEGRATION_GRP.G_RETURN_APPROVAL
397 , p_comments => :comment
398 , p_reject_reason_code => null
399 , x_return_status => :return_status
400 , x_msg_count => :msg_count
401 , x_msg_data => :msg_data
402 );'||
403 'END;';
404
405 IF l_debug_level > 0 THEN
406 oe_debug_pub.add('OEXGRMAB: BEFORE CALLING IBE_OM_INTEGRATION_GRP.NOTIFY_RMA_REQUEST_ACTION');
407 END IF;
408
409 BEGIN
410 EXECUTE IMMEDIATE l_block_str
411 USING IN to_number(itemkey)
412 , IN l_comments
413 , OUT l_return_status
414 , OUT l_msg_count
415 , OUT l_msg_data;
416 EXCEPTION
417 WHEN OTHERS THEN
418 /*
419 ** Ignore the following Exceptions
420 ** 6550: PL/SQL Compilation Error - Raised when above package does not exist.
421 ** 4067: Not executed, <Object> does not exist - Raised when just the spec exists.
422 */
423 IF SQLCODE in ('-6550', '-4067') THEN
424 OE_DEBUG_PUB.ADD('OEXGRMAB: ISTORE PACKAGE DOES NOT EXIST '||SQLCODE);
425 NULL;
426 ELSE
427 OE_DEBUG_PUB.ADD('OEXGRMAB: PACKAGE EXIST, SOME OTHER EXCEPTION '||SQLCODE);
428 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
429 END IF;
430 END;
431
432 IF l_debug_level > 0 THEN
433 oe_debug_pub.add('OEXGRMAB: AFTER CALLING IBE_OM_INTEGRATION_GRP.NOTIFY_RMA_REQUEST_ACTION');
434 END IF;
435
436 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
437 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
438 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
439 RAISE FND_API.G_EXC_ERROR;
440 END IF;
441
442 END IF; -- If iStore Return
443
444 ELSE -- Not Header Item Type
445 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
446 END IF;
447
448 RESULTOUT := 'COMPLETE:COMPLETE';
449
450 END IF;
451
452 IF l_debug_level > 0 THEN
453 oe_debug_pub.add('OEXGRMAB: EXITING POST_APPROVAL_PROCESS');
454 END IF;
455
456 EXCEPTION
457 WHEN OTHERS THEN
458 WF_CORE.Context(G_PKG_NAME, 'Post_Approval_Process', itemtype, itemkey, to_char(actid), funcmode);
459 Raise;
460
461 END Post_Approval_Process;
462
463 /*
464 ** Post_Rejection_Process() will set the reason for rejecting the RMA
465 ** into OE_REASONS table. It will also call iStore API that sends the
466 ** notification on rejection of the return order to the end user.
467 */
468 PROCEDURE Post_Rejection_Process(
469 itemtype IN VARCHAR2
470 , itemkey IN VARCHAR2
471 , actid IN NUMBER
472 , funcmode IN VARCHAR2
473 , resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
474 )
475 IS
476 l_order_source_id NUMBER;
477 l_notification_id NUMBER;
478 l_ntf_exists VARCHAR2(1) := 'Y';
479 l_reason_exists VARCHAR2(1);
480 l_reason_code VARCHAR2(30);
481 l_reason_id NUMBER;
482 l_comments VARCHAR2(4000);
483 l_block_str VARCHAR2(2000);
484 l_return_status VARCHAR2(1);
485 l_msg_count NUMBER;
486 l_msg_data VARCHAR2(2000);
487
488 CURSOR ntf
489 IS
490 SELECT wv.NOTIFICATION_ID
491 FROM
492 (
493 select IAS.NOTIFICATION_ID NOTIFICATION_ID, IAS.END_DATE END_DATE
494 from WF_PROCESS_ACTIVITIES PA,
495 WF_ITEM_ACTIVITY_STATUSES IAS
496 where IAS.ITEM_TYPE = itemtype
497 and IAS.ITEM_KEY = itemkey
498 and PA.ACTIVITY_NAME = 'RMA_ORDER_APPROVAL_NTF'
499 and IAS.NOTIFICATION_ID is not null
500 and IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
501 UNION ALL
502 select IAS.NOTIFICATION_ID, IAS.END_DATE
503 from WF_PROCESS_ACTIVITIES PA,
504 WF_ITEM_ACTIVITY_STATUSES_H IAS
505 where IAS.ITEM_TYPE = itemtype
506 and IAS.ITEM_KEY = itemkey
507 and PA.ACTIVITY_NAME = 'RMA_ORDER_APPROVAL_NTF'
508 and IAS.NOTIFICATION_ID is not null
509 and IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
510 ) wv
511 ORDER BY wv.end_date DESC ;
512
513 --
514 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
515 --
516 BEGIN
517
518 IF l_debug_level > 0 THEN
519 oe_debug_pub.add('OEXGRMAB: ENTERING POST_REJECTION_PROCESS, ITEM TYPE/KEY: '||itemtype||'/'||itemkey) ;
520 END IF;
521
522 IF (FUNCMODE = 'RUN') THEN
523
524 IF itemtype = OE_GLOBALS.G_WFI_HDR THEN
525
526 /* Get Notification ID for Return Order Approval NTF */
527
528 OPEN ntf;
529
530 FETCH ntf INTO l_notification_id;
531 IF ntf%NOTFOUND THEN
532 l_ntf_exists := 'N';
533 END IF;
534
535 CLOSE ntf;
536
537 IF l_debug_level > 0 THEN
538 oe_debug_pub.add('OEXGRMAB: NOTIFICATION ID: '||l_notification_id) ;
539 END IF;
540
541 /* Get Rejection Reason and Comments */
542
543 IF l_ntf_exists = 'Y' THEN
544 l_reason_code := WF_NOTIFICATION.GetAttrText(l_notification_id, 'REJECTION_REASON');
545 l_comments := WF_NOTIFICATION.GetAttrText(l_notification_id, 'WF_NOTE');
546 END IF;
547
548 /* Update the Rejection Reason */
549
550 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
551
552 /* Check if Rejection Reason has been setup in RMA_REJECTION_REASON lookup */
553 BEGIN
554 SELECT 'Y'
555 INTO l_reason_exists
556 FROM oe_lookups
557 WHERE lookup_type = 'RMA_REJECTION_REASON'
558 AND lookup_code = l_reason_code;
559 EXCEPTION
560 WHEN NO_DATA_FOUND THEN
561 l_reason_exists := 'N';
562 END;
563
564 IF l_reason_exists = 'Y' THEN
565
566 IF l_debug_level > 0 THEN
567 oe_debug_pub.add('OEXGRMAB: BEFORE CALLING OE_REASONS_UTIL.APPLY_REASON');
568 END IF;
569
570 /* Set the Reason in the OE_REASONS Table */
571
572 OE_REASONS_UTIL.Apply_Reason(
573 p_entity_code => 'HEADER'
574 , p_entity_id => to_number(itemkey)
575 , p_version_number => 1.0
576 , p_reason_type => 'RMA_REJECTION_REASON'
577 , p_reason_code => l_reason_code
578 , p_reason_comments=> l_comments
579 , x_reason_id => l_reason_id
580 , x_return_status => l_return_status
581 );
582
583 IF l_debug_level > 0 THEN
584 oe_debug_pub.add('OEXGRMAB: AFTER CALLING OE_REASONS_UTIL.APPLY_REASON');
585 END IF;
586
587 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
588 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
589 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
590 RAISE FND_API.G_EXC_ERROR;
591 END IF;
592
593 END IF;
594 END IF;
595
596 /* Get the Order Source ID of the Order */
597
598 SELECT order_source_id
599 INTO l_order_source_id
600 FROM oe_order_headers
601 WHERE header_id = to_number(itemkey);
602
603 /* Check if this Return Order originated from iStore */
604
605 IF l_order_source_id = 13 THEN
606
607 /* Prepare dynamic SQL to call iStore API */
608
609 l_block_str :=
610 'DECLARE '||
611 'BEGIN '||
612 'IBE_OM_INTEGRATION_GRP.Notify_RMA_Request_Action(
613 p_api_version_number => 1.0
614 , p_order_header_id => :header_id
615 , p_notif_context => IBE_OM_INTEGRATION_GRP.G_RETURN_REJECT
616 , p_comments => :comment
617 , p_reject_reason_code => :reason
618 , x_return_status => :return_status
619 , x_msg_count => :msg_count
620 , x_msg_data => :msg_data
621 );'||
622 'END;';
623
624 IF l_debug_level > 0 THEN
625 oe_debug_pub.add('OEXGRMAB: BEFORE CALLING IBE_OM_INTEGRATION_GRP.NOTIFY_RMA_REQUEST_ACTION');
626 END IF;
627
628 BEGIN
629 EXECUTE IMMEDIATE l_block_str
630 USING IN to_number(itemkey)
631 , IN l_comments
632 , IN l_reason_code
633 , OUT l_return_status
634 , OUT l_msg_count
635 , OUT l_msg_data;
636 EXCEPTION
637 WHEN OTHERS THEN
638 /*
639 ** Ignore the following Exceptions
640 ** 6550: PL/SQL Compilation Error - Raised when above package does not exist.
641 ** 4067: Not executed, <Object> does not exist - Raised when just the spec exists.
642 */
643 IF SQLCODE in ('-6550', '-4067') THEN
644 OE_DEBUG_PUB.ADD('OEXGRMAB: ISTORE PACKAGE DOES NOT EXIST '||SQLCODE);
645 NULL;
646 ELSE
647 OE_DEBUG_PUB.ADD('OEXGRMAB: PACKAGE EXIST, SOME OTHER EXCEPTION '||SQLCODE);
648 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
649 END IF;
650 END;
651
652 IF l_debug_level > 0 THEN
653 oe_debug_pub.add('OEXGRMAB: AFTER CALLING IBE_OM_INTEGRATION_GRP.NOTIFY_RMA_REQUEST_ACTION');
654 END IF;
655
656 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
657 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
658 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
659 RAISE FND_API.G_EXC_ERROR;
660 END IF;
661
662 END IF; -- If iStore Return
663
664 ELSE -- Not Header Item Type
665 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
666 END IF;
667
668 RESULTOUT := 'COMPLETE:COMPLETE';
669
670 END IF;
671
672 IF l_debug_level > 0 THEN
673 oe_debug_pub.add('OEXGRMAB: EXITING POST_REJECTION_PROCESS');
674 END IF;
675
676 EXCEPTION
677 WHEN OTHERS THEN
678 WF_CORE.Context(G_PKG_NAME, 'Post_Rejection_Process', itemtype, itemkey, to_char(actid), funcmode);
679 Raise;
680
681 END Post_Rejection_Process;
682
683 END OE_RMA_GRP;
[Home] [Help]