[Home] [Help]
PACKAGE BODY: APPS.OE_RMA_RECEIVING
Source
1 Package body OE_RMA_RECEIVING As
2 /* $Header: OEXRMARB.pls 120.6.12010000.2 2008/11/14 21:33:44 shrgupta ship $ */
3
4 -- Push_Receiving_Info is an OM procedure that is called by Oracle Purchasing
5 -- to push receiving information to Oracle Order Management.
6 --
7 -- Pseudo Code of the possible combination of the parent trx types and
8 -- trx types in PO when calling this API.
9 -- This API assumes that the workflow always have Inspection activity
10 -- after Receiving activity. This is the seeded sub process.
11 -- If this is not the case, the logic has to be rewritten.
12 -- Also, it is assumed that PO will be calling this API with the correct trx types and quantity.
13 --
14 /*
15 IF (p_parent_transaction_type is NULL or p_parent_transaction_type = G_RMA_NO_PARENT) THEN
16 IF (p_transaction_type = G_RMA_RECEIVE) THEN
17 - shipped_quantity := p_quantity;
18 - shipping_uom := ordered_uom;
19 - IF p_quantity < ordered_quantity (partial receipt) THEN
20 split order line
21 - complete wf activity for Receiving.
22 ELSE
23 no processing.
24 return succesfull.
25 END IF;
26
27 ELSIF (p_parent_transaction_type = G_RMA_RECEIVE) THEN
28 IF (p_transaction_type = G_RMA_DELIVER) THEN
29
30 - fulfilled_quantity := nvl(fulfilled_quantity, 0) + p_quantity;
31 - IF fulfilled_quantity = shipped_quantity THEN
32 complete wf activity for Inspection.
33 END IF;
34
35 ELSIF (p_transaction_type = G_RMA_RETURN_TO_CUSTOMER) THEN
36 - shipped_quantity := shipped_quantity - p_quantity;
37
38 ELSIF (p_transaction_type = G_RMA_CORRECT) THEN
39 - shipped_quantity := shipped_quantity - p_quantity;
40 - adjust remaining open quantity or split line.
41
42 ELSE
43 no processing.
44 return successfull.
45 END IF;
46 ELSIF (p_parent_transaction_type in (G_RMA_ACCEPT,G_RMA_REJECT)) THEN
47 IF ( p_transaction_type = G_RMA_DELIVER) THEN
48 - fulfilled_quantity := nvl(fulfilled_quantity, 0) + p_quantity;
49 - IF fulfilled_quantity = shipped_quantity THEN
50 complete wf activity for Inspection.
51 END IF;
52 ELSIF (p_transaction_type = G_RMA_RETURN_TO_CUSTOMER) THEN
53 - shipped_quantity := shipped_quantity - p_quantity;
54 - IF fulfilled_quantity = shipped_quantity THEN
55 complete wf activity for Inspection.
56 END IF;
57 ELSE
58 no processing.
59 return successfull.
60 END IF;
61
62 ELSIF (p_parent_transaction_type = G_RMA_DELIVER) THEN
63 IF (p_transaction_type = G_RMA_RETURN_TO_CUSTOMER) THEN
64 - shipped_quantity := shipped_quantity - p_quantity;
65 - fulfilled_quantity := fulfilled_quantity - p_quantity;
66
67 ELSIF (p_transaction_type = G_RMA_RETURN_TO_RECEIVING) THEN
68 - fulfilled_quantity := fulfilled_quantity - p_quantity;
69
70 ELSIF (p_transaction_type = G_RMA_CORRECT) THEN
71 - fulfilled_quantity := fulfilled_quantity + p_quantity;
72
73 ELSE
74 no processing.
75 return successfull.
76 END IF;
77
78 ELSIF (p_parent_transaction_type = G_RMA_RETURN_TO_CUSTOMER) THEN
79 IF (p_transaction_type = G_RMA_CORRECT) THEN
80 - shipped_quantity := shipped_quantity - p_quantity;
81 - fulfilled_quantity := fulfilled_quantity - p_quantity;
82 (check if fulfilled is not null)
83
84 ELSE
85 no processing.
86 return successfull.
87 END IF;
88
89 ELSIF (p_parent_transaction_type = G_RMA_RETURN_TO_RECEIVING) THEN
90 IF (p_transaction_type = G_RMA_CORRECT) THEN
91 - fulfilled_quantity := fulfilled_quantity - p_quantity;
92
93 ELSE
94 no processing.
95 return successfull.
96 END IF;
97
98 ELSIF (p_parent_transaction_type = G_RMA_UNMATCHED_ORDER) THEN
99 IF (p_transaction_type = G_RMA_MATCH) THEN
100 - shipped_quantity := p_quantity;
101 - shipping_uom := ordered_uom;
102 - if partial receipt, split line;
103 - complete wf activity for receiving;
104
105 ELSE
106 no processing
107 return successfull;
108
109 END IF;
110
111 ELSE (for other parent transaction type)
112 no processing.
113 return successfull.
114 END IF;
115
116 */
117
118 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_RMA_RECEIVING';
119
120 Procedure Push_Receiving_Info(
121 p_RMA_Line_ID IN NUMBER,
122 p_Quantity IN NUMBER,
123 p_Parent_Transaction_Type IN VARCHAR2,
124 p_Transaction_Type IN VARCHAR2,
125 p_Mismatch_Flag IN VARCHAR2,
126 x_Return_Status OUT NOCOPY VARCHAR2,
127 x_Msg_Count OUT NOCOPY NUMBER,
128 x_MSG_Data OUT NOCOPY VARCHAR2,
129 p_Quantity2 IN NUMBER DEFAULT NULL,
130 p_R2Cust_Parent_Trn_Type IN VARCHAR2 DEFAULT NULL
131 )
132 IS
133 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type;
134 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
135 l_item_key varchar2(30);
136 l_control_rec OE_GLOBALS.Control_Rec_Type;
137 l_x_header_rec OE_Order_PUB.Header_Rec_Type;
138 l_x_Header_Adj_rec OE_Order_PUB.Header_Adj_Rec_Type;
139 l_x_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
140 l_x_Header_Scredit_rec OE_Order_PUB.Header_Scredit_Rec_Type;
141 l_x_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
142 l_x_Line_Adj_rec OE_Order_PUB.Line_Adj_Rec_Type;
143 l_x_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
144 l_x_Line_Scredit_rec OE_Order_PUB.Line_Scredit_Rec_Type;
145 l_x_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
146 l_x_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
147 l_x_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type;
148 l_x_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type;
149 l_x_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type;
150 l_x_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type;
151 l_x_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type;
152 l_x_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type;
153 l_x_Action_Request_tbl OE_Order_PUB.Request_Tbl_Type;
154 l_return_status VARCHAR2(30);
155 l_tolerance_below NUMBER;
156 l_tolerance_above NUMBER;
157 l_open_line_id NUMBER;
158 l_open_line_rec OE_Order_PUB.Line_Rec_Type;
159 l_temp_open_line_rec OE_Order_PUB.Line_Rec_Type;
160 l_updated_quantity NUMBER;
161 b_om_processing boolean := FALSE;
162 /* to indicate if om processing is needed*/
163 l_updated_quantity2 NUMBER; -- 04/20/2001 OPM
164 b_dual_qty boolean := FALSE; -- 04/20/2001 OPM
165 b_adjust_open_quantity boolean := FALSE;
166 b_complete_receiving boolean := FALSE;
167 b_complete_inspection boolean := FALSE;
168 b_negative_correction boolean := FALSE;
169 b_neg_corr_zero_qty boolean := FALSE;
170 b_positive_correction boolean := FALSE;
171 l_temp_var VARCHAR2(2000) := NULL;
172 --serla begin
173 l_x_Header_Payment_tbl OE_Order_PUB.Header_Payment_Tbl_Type;
174 l_x_Line_Payment_tbl OE_Order_PUB.Line_Payment_Tbl_Type;
175 --serla end
176 --
177 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
178 --
179 l_max_qty_to_adjust NUMBER;
180 l_line_number VARCHAR2(30);
181 l_credit_rejected_qty VARCHAR2(1); -- Added for bug 6052676
182 BEGIN
183
184 -- Initialize API return status to success
185 x_return_status := FND_API.G_RET_STS_SUCCESS;
186
187 -- OM: Debug Level. Just remove the comments
188 IF To_number(Nvl(fnd_profile.value('ONT_DEBUG_LEVEL'), '0')) > 0 THEN
189 oe_debug_pub.initialize;
190 l_temp_var := oe_debug_pub.set_debug_mode('FILE');
191 oe_debug_pub.debug_on;
192 END IF;
193
194 -- Return success if there is no parent and unordered receipt
195 IF (p_parent_transaction_type is NULL OR
196 p_parent_transaction_type = OE_RMA_RECEIVING.G_RMA_NO_PARENT) AND
197 (p_transaction_type = OE_RMA_RECEIVING.G_RMA_UNMATCHED_ORDER) THEN
198 IF l_debug_level > 0 THEN
199 oe_debug_pub.add( 'PARENT IS NULL OR NO PARENT , TRX TYPE IS UNORDERED' ) ;
200 END IF;
201 return;
202 END IF;
203
204 -- This API is called directly from PO, need to set the oe global context
205 OE_GLOBALS.set_context;
206
207 -- This if statement is to allow debug generation by setting the profile option
208
209 IF l_debug_level > 0 THEN
210 oe_debug_pub.add( 'ENTERING OE_RMA_RECEIVING.PUSH_RECEIVING_INFO' , 1 ) ;
211 END IF;
212 IF l_debug_level > 0 THEN
213 oe_debug_pub.add( 'RMA_LINE_ID: ' || P_RMA_LINE_ID , 1 ) ;
214 END IF;
215 IF l_debug_level > 0 THEN
216 oe_debug_pub.add( 'QUANTITY: ' || P_QUANTITY , 1 ) ;
217 END IF;
218 IF l_debug_level > 0 THEN
219 oe_debug_pub.add( 'PARENT_TRANSACTION_TYPE: ' || P_PARENT_TRANSACTION_TYPE , 1 ) ;
220 END IF;
221 IF l_debug_level > 0 THEN
222 oe_debug_pub.add( 'TRANSACTION_TYPE: ' || P_TRANSACTION_TYPE , 1 ) ;
223 END IF;
224 IF l_debug_level > 0 THEN
225 oe_debug_pub.add( 'MISMATCH_FLAG: ' || P_MISMATCH_FLAG , 1 ) ;
226 END IF;
227
228
229 l_item_key := to_char(p_RMA_Line_id);
230
231 -- Query line from p_RMA_Line_ID
232
233 OE_Line_Util.Lock_Rows
234 ( p_line_id => p_RMA_Line_id
235 , x_line_tbl => l_old_line_tbl
236 , x_return_status => l_return_status
237 );
238 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
239 RAISE FND_API.G_EXC_ERROR;
240 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
241 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
242 END IF;
243
244 l_old_line_tbl(1).db_flag := FND_API.G_TRUE;
245 l_line_tbl(1) := l_old_line_tbl(1);
246
247 -- Set control flags.
248
249 l_control_rec.controlled_operation := TRUE;
250 l_control_rec.validate_entity := TRUE;
251 l_control_rec.write_to_DB := TRUE;
252
253 l_control_rec.default_attributes := TRUE;
254 l_control_rec.change_attributes := TRUE;
255 l_control_rec.clear_dependents := TRUE;
256
257 -- Instruct API to retain its caches
258
259 l_control_rec.clear_api_cache := FALSE;
260 l_control_rec.clear_api_requests := FALSE;
261 -- Fix for bug #1168866. Our internal calls to process order
262 -- should suppress security
263 l_control_rec.check_security := FALSE;
264
265 -- setting ship tolerances
266
267 IF (l_line_tbl(1).ship_tolerance_below = FND_API.G_MISS_NUM)
268 OR (l_line_tbl(1).ship_tolerance_below is NULL) THEN
269 l_tolerance_below := 0.0;
270 ELSE
271 l_tolerance_below := l_line_tbl(1).ship_tolerance_below * l_line_tbl(1).ordered_quantity/100;
272 END IF;
273
274 IF (l_line_tbl(1).ship_tolerance_above = FND_API.G_MISS_NUM)
275 OR (l_line_tbl(1).ship_tolerance_above is NULL) THEN
276 l_tolerance_above := 0.0;
277 ELSE
278 l_tolerance_above := l_line_tbl(1).ship_tolerance_above * l_line_tbl(1).ordered_quantity/100;
279 END IF;
280
281
282 -- set boolean for presence of quantity2 for OPM
283 IF (p_quantity2 = FND_API.G_MISS_NUM)
284 OR (p_quantity2 = 0 )
285 OR (p_quantity2 is NULL) THEN
286 b_dual_qty := FALSE;
287 ELSE
288 b_dual_qty := TRUE;
289 END IF;
290
291 IF l_debug_level > 0 THEN
292 oe_debug_pub.add( 'The Tolerance above is '||l_tolerance_above ) ;
293 oe_debug_pub.add( 'The Tolerance below is '||l_tolerance_below ) ;
294 END IF;
295
296 -- Fix for Bug #1140815. Receiving module passes us the string 'NO PARENT'
297 -- when receiving goods for the first time
298 IF (p_parent_transaction_type is NULL OR
299 p_parent_transaction_type = OE_RMA_RECEIVING.G_RMA_NO_PARENT) THEN
300 IF (p_transaction_type = OE_RMA_RECEIVING.G_RMA_RECEIVE) THEN
301 IF l_debug_level > 0 THEN
302 oe_debug_pub.add( 'PARENT IS NULL , TRX TYPE IS RECEIVE' ) ;
303 END IF;
304
305 -- update shipped_quantity
306 b_om_processing := TRUE;
307 b_complete_receiving := TRUE;
308
309 -- should not be receiving more than the ordered quantity
310
311 IF ((l_line_tbl(1).shipped_quantity = FND_API.G_MISS_NUM) OR
312 (l_line_tbl(1).shipped_quantity is NULL)) THEN
313 l_updated_quantity := p_quantity;
314 IF b_dual_qty THEN
315 l_updated_quantity2 := p_quantity2; -- 04/20/2001 OPM
316 END IF;
317 ELSE
318 l_updated_quantity := l_line_tbl(1).shipped_quantity + p_quantity;
319 IF b_dual_qty THEN
320 l_updated_quantity2 := l_line_tbl(1).shipped_quantity2 + p_quantity2; -- 04/20/2001 OPM
321 END IF;
322 END IF;
323
324 -- received quantity should be within tolerance
325 IF (l_updated_quantity >
326 l_line_tbl(1).ordered_quantity + l_tolerance_above) THEN
327 FND_MESSAGE.Set_Name('ONT', 'OE_RETURN_INVALID_RCVD_QTY');
328 OE_MSG_PUB.Add;
329 x_return_status := FND_API.G_RET_STS_ERROR ;
330 RAISE FND_API.G_EXC_ERROR;
331 END IF;
332
333 -- under return, split the line
334 IF (l_updated_quantity <
335 l_line_tbl(1).ordered_quantity - l_tolerance_below) then
336
337 IF l_debug_level > 0 THEN
338 oe_debug_pub.add( 'UNDER RETURN:'||TO_CHAR ( P_QUANTITY ) ||'<'||TO_CHAR ( L_LINE_TBL ( 1 ) .ORDERED_QUANTITY+L_TOLERANCE_BELOW ) , 1 ) ;
339 END IF;
340
341 l_open_line_rec.db_flag := FND_API.G_FALSE;
342 l_open_line_rec.ordered_quantity
343 := l_old_line_tbl(1).ordered_quantity - p_quantity;
344 IF b_dual_qty THEN
345 l_open_line_rec.ordered_quantity2
346 := l_old_line_tbl(1).ordered_quantity2 - p_quantity2; -- 04/20/2001 OPM
347 END IF;
348 l_open_line_rec.split_from_line_id := p_RMA_Line_ID;
349 l_opeN_line_rec.split_by := 'SYSTEM';
350 l_open_line_rec.operation := OE_GLOBALS.G_OPR_CREATE;
351 l_line_tbl(1).ordered_quantity := p_quantity;
352 IF b_dual_qty THEN
353 l_line_tbl(1).ordered_quantity2 := p_quantity2; -- 04/20/2001 OPM
354 END IF;
355 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
356 l_line_tbl(1).shipped_quantity := p_quantity;
357 IF b_dual_qty THEN
358 l_line_tbl(1).shipped_quantity2 := p_quantity2; -- 04/20/2001 OPM
359 l_line_tbl(1).shipping_quantity_uom2 := l_old_line_tbl(1).ordered_quantity_uom2;
360 END IF;
361 l_line_tbl(1).shipping_quantity_uom := l_old_line_tbl(1).order_quantity_uom;
362 l_line_tbl(1).split_action_code := 'SPLIT';
363 l_line_tbl(1).split_by := 'SYSTEM';
364 b_adjust_open_quantity := TRUE;
365
366 ELSE -- full return
367
368 l_line_tbl(1).shipped_quantity := p_quantity;
369 If b_dual_qty THEN
370 l_line_tbl(1).shipped_quantity2 := p_quantity2; -- 04/20/2001 OPM
371 l_line_tbl(1).shipping_quantity_uom2 := l_old_line_tbl(1).ordered_quantity_uom2;
372 END IF;
373 l_line_tbl(1).shipping_quantity_uom := l_old_line_tbl(1).order_quantity_uom;
374 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
375
376 END IF;
377 --set the actual shipment date on the RMA line
378
379 SELECT MAX(transaction_date)
380 INTO l_line_tbl(1).actual_shipment_date
381 FROM rcv_transactions
382 WHERE transaction_type = 'RECEIVE'
383 AND oe_order_line_id = l_line_tbl(1).line_id;
384
385 IF l_line_tbl(1).actual_shipment_date IS NULL THEN
386 l_line_tbl(1).actual_shipment_date := sysdate;
387 END IF;
388
389 END IF;
390
391 ELSIF (p_parent_transaction_type = OE_RMA_RECEIVING.G_RMA_RECEIVE OR
392 p_parent_transaction_type = OE_RMA_RECEIVING.G_RMA_MATCH) THEN
393 IF (p_transaction_type = G_RMA_DELIVER) THEN
394
395 IF l_debug_level > 0 THEN
396 oe_debug_pub.add( 'PARENT IS RECEIVE , TRX TYPE IS ACCEPT/REJECT/DELIVER' ) ;
397 END IF;
398
399 -- update fulfilled_quantity, and possibility to complete inspection.
400 b_om_processing := TRUE;
401
402 -- get the current fulfilled quantity
403 IF ((l_line_tbl(1).fulfilled_quantity = FND_API.G_MISS_NUM) OR
404 (l_line_tbl(1).fulfilled_quantity is NULL)) THEN
405 l_updated_quantity := p_quantity;
406 IF b_dual_qty THEN
407 l_updated_quantity2 := p_quantity2; -- 04/20/2001 OPM
408 END IF;
409 ELSE
410 l_updated_quantity := l_line_tbl(1).fulfilled_quantity + p_quantity;
411 IF b_dual_qty THEN
412 l_updated_quantity2 := l_line_tbl(1).fulfilled_quantity2 + p_quantity2; -- 04/20/2001 OPM
413 END IF;
414 END IF;
415
416 -- over quantity being passed
417 IF (l_line_tbl(1).shipped_quantity < l_updated_quantity ) THEN
418 FND_MESSAGE.Set_Name('ONT', 'OE_RETURN_INVALID_DLVR_QTY');
419 OE_MSG_PUB.Add;
420 x_return_status := FND_API.G_RET_STS_ERROR ;
421 RAISE FND_API.G_EXC_ERROR;
422 END IF;
423
424 l_line_tbl(1).fulfilled_quantity := l_updated_quantity;
425 IF b_dual_qty THEN
426 l_line_tbl(1).fulfilled_quantity2 := l_updated_quantity2; -- 04/20/2001 OPM
427 END IF;
428 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
429
430 IF (l_line_tbl(1).shipped_quantity = l_updated_quantity) THEN
431 b_complete_inspection := TRUE;
432 END IF;
433
434 ELSIF (p_transaction_type = OE_RMA_RECEIVING.G_RMA_RETURN_TO_CUSTOMER) THEN
435 IF l_debug_level > 0 THEN
436 oe_debug_pub.add( 'PARENT IS RECEIVE , TRX TYPE IS RETURN TO CUSTOMER' ) ;
437 END IF;
438
439 -- update shipped_quantity
440 b_om_processing := TRUE;
441 IF l_debug_level > 0 THEN
442 oe_debug_pub.add( 'PARENT IS RECEIVE , TRX TYPE IS RETURN TO CUSTOMER' ) ;
443 END IF;
444
445 l_updated_quantity := l_line_tbl(1).shipped_quantity - p_quantity;
446 IF b_dual_qty THEN
447 l_updated_quantity2 := l_line_tbl(1).shipped_quantity2 - p_quantity2; -- 04/20/2001 OPM
448 l_line_tbl(1).shipped_quantity2 := l_updated_quantity2;
449 END IF;
450 l_line_tbl(1).shipped_quantity := l_updated_quantity;
451 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
452 l_control_rec.check_security := FALSE;
453
454 IF (l_line_tbl(1).shipped_quantity = l_line_tbl(1).fulfilled_quantity)
455 THEN
456 b_complete_inspection := TRUE;
457 END IF;
458
459
460 ELSIF (p_transaction_type = OE_RMA_RECEIVING.G_RMA_CORRECT) THEN
461 IF l_debug_level > 0 THEN
462 oe_debug_pub.add( 'PARENT IS RECEIVE , TRX TYPE IS CORRECT' ) ;
463 END IF;
464
465 -- update shipped_quantity and adjust remaining open quantity
466 b_om_processing := TRUE;
467
468 -- check whether it is an upgrade or downgrade
469 -- if p_quantity is positive, we should check whether we can receive
470 -- on the current line. Otherwise give error..
471 -- if p_quantity is negative, we should adjust the ordered
472 -- quantity and shipped quantity of the line, and adjust
473 -- the remaining open line or split the current line.
474
475 l_open_line_id := Get_Open_Line_Id(l_line_tbl(1));
476
477 IF (l_open_line_id is NOT NULL) THEN
478 IF l_debug_level > 0 THEN
479 oe_debug_pub.add('There is an Open Line Id' ) ;
480 END IF;
481 OE_Line_Util.Lock_Row
482 (p_line_id => l_open_line_id
483 ,p_x_line_rec => l_temp_open_line_rec
484 ,x_return_status => l_return_status
485 );
486 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
487 RAISE FND_API.G_EXC_ERROR;
488 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
489 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
490 END IF;
491 END IF;
492
493 IF ( p_quantity > 0 ) THEN -- received more
494 IF l_debug_level > 0 THEN
495 oe_debug_pub.add( 'QUANTITY IS POSITIVE' ) ;
496 END IF;
497
498 l_updated_quantity := l_line_tbl(1).shipped_quantity + p_quantity;
499 IF b_dual_qty THEN
500 l_updated_quantity2 := l_line_tbl(1).shipped_quantity2 +
501 p_quantity2; -- 04/20/2001 OPM
502 END IF;
503 -- Check if updated qty is within + tolerance
504 IF l_updated_quantity <=
505 (l_line_tbl(1).ordered_quantity + l_tolerance_above) THEN
506 IF l_debug_level > 0 THEN
507 oe_debug_pub.add('Change is within tolerance' ) ;
508 END IF;
509 -- adjust the shipped_quantity and ordered_quantity
510 l_line_tbl(1).shipped_quantity := l_updated_quantity;
511 IF b_dual_qty THEN
512 l_line_tbl(1).shipped_quantity2 := l_updated_quantity2; -- 04/20/2001 OPM
513 END IF;
514 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
515 ELSE
516 l_max_qty_to_adjust := l_line_tbl(1).ordered_quantity +
517 l_tolerance_above -
518 l_line_tbl(1).shipped_quantity;
519 IF l_debug_level > 0 THEN
520 oe_debug_pub.add( 'Above Tolerance, raise ERROR' ) ;
521 END IF;
522 IF l_open_line_id is NOT NULL THEN
523 l_line_number := l_temp_open_line_rec.line_number||'.'||
524 l_temp_open_line_rec.shipment_number;
525 FND_MESSAGE.Set_Name('ONT', 'OE_RECEIVE_ON_OPEN_LINE');
526 FND_MESSAGE.Set_Token('LINENUMBER', l_line_number);
527 ELSE
528 IF l_max_qty_to_adjust > 0 THEN
529 FND_MESSAGE.Set_Name('ONT', 'OE_MAX_CORRECTION_QTY');
530 FND_MESSAGE.Set_Token('CORRECTIONQTY',
531 to_char(l_max_qty_to_adjust)||' '||
532 l_line_tbl(1).ORDER_QUANTITY_UOM);
533 ELSE
534 FND_MESSAGE.Set_Name('ONT', 'OE_RETURN_INVALID_RCVD_QTY');
535 END IF;
536 END IF;
537 OE_MSG_PUB.Add;
538 x_return_status := FND_API.G_RET_STS_ERROR ;
539 RAISE FND_API.G_EXC_ERROR;
540 END IF;
541 -- Never receive on open line as this line will never have any receipt
542 -- associated with it.
543
544 ELSE -- received less. adjust the qty of the current line,
545 -- and adjust the quantity of the open line.
546
547 IF l_debug_level > 0 THEN
548 oe_debug_pub.add('For -ve correction ');
549 END IF;
550 -- adjust the shipped_quantity and ordered_quantity
551 l_updated_quantity := l_line_tbl(1).shipped_quantity + p_quantity;
552 l_line_tbl(1).shipped_quantity := l_updated_quantity;
553 IF b_dual_qty THEN
554 l_updated_quantity2 := l_line_tbl(1).shipped_quantity2 + p_quantity2; -- 04/20/2001 OPM
555 l_line_tbl(1).shipped_quantity := l_updated_quantity;
556 END IF;
557
558 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
559 l_control_rec.check_security := FALSE;
560
561 IF (l_open_line_id is NULL) THEN -- no open line
562
563 IF l_debug_level > 0 THEN
564 oe_debug_pub.add('No Open Line: l_updated QTY is '
565 ||l_updated_quantity);
566 END IF;
567 IF ( l_updated_quantity not between
568 (l_line_tbl(1).ordered_quantity - l_tolerance_below) and
569 (l_line_tbl(1).ordered_quantity + l_tolerance_above)) OR
570 ( l_updated_quantity = 0 ) THEN
571 -- split line if the l_updated_quantity is > 0.
572 IF l_updated_quantity > 0 THEN
573 l_open_line_rec.db_flag := FND_API.G_FALSE;
574 l_open_line_rec.ordered_quantity := -p_quantity;
575 IF b_dual_qty THEN
576 l_open_line_rec.ordered_quantity2 := -p_quantity2;
577 -- 04/20/2001 OPM
578 END IF;
579 IF l_debug_level > 0 THEN
580 oe_debug_pub.add('Split the current line ' );
581 END IF;
582 l_open_line_rec.split_from_line_id := p_RMA_Line_ID;
583 l_open_line_rec.split_by := 'SYSTEM';
584 l_open_line_rec.operation := OE_GLOBALS.G_OPR_CREATE;
585 b_adjust_open_quantity := TRUE;
586
587 l_line_tbl(1).ordered_quantity := l_updated_quantity;
588 IF b_dual_qty THEN
589 l_line_tbl(1).ordered_quantity2 := l_updated_quantity2;
590 -- 04/20/2001 OPM
591 END IF;
592 l_line_tbl(1).split_action_code := 'SPLIT';
593 l_line_tbl(1).split_by := 'SYSTEM';
594
595 b_negative_correction := TRUE;
596 ELSIF l_updated_quantity = 0 THEN
597 -- Take the received line back to AWAITING_RETURN
598 IF l_debug_level > 0 THEN
599 oe_debug_pub.add('Take the line back to AWAITING_RETURN' );
600 END IF;
601 b_neg_corr_zero_qty := TRUE;
602 l_line_tbl(1).shipped_quantity := NULL;
603 IF b_dual_qty THEN
604 l_line_tbl(1).shipped_quantity2 := NULL; -- 04/20/2001 OPM
605 END IF;
606 l_line_tbl(1).flow_status_code := 'AWAITING_RETURN';
607 ELSE
608 FND_MESSAGE.Set_Name('ONT', 'OE_RETURN_INVALID_RCVD_QTY');
609 OE_MSG_PUB.Add;
610 x_return_status := FND_API.G_RET_STS_ERROR ;
611 RAISE FND_API.G_EXC_ERROR;
612 END IF;
613
614 END IF; -- IF l_updated_quantity not between
615
616 -- Added fix for issue 5078844
617 IF l_line_tbl(1).ordered_quantity = l_line_tbl(1).fulfilled_quantity
618 and l_line_tbl(1).flow_status_code='AWAITING_RETURN_DISPOSITION'
619 THEN
620 b_complete_inspection := TRUE;
621 END IF;
622
623
624 ELSE -- IF (l_open_line_id is NOT NULL) THEN
625
626 IF l_updated_quantity > 0 THEN
627 -- Correct the quantity on original line
628 l_line_tbl(1).ordered_quantity := l_updated_quantity;
629 IF b_dual_qty THEN
630 l_line_tbl(1).ordered_quantity2 := l_updated_quantity2;
631 -- 04/20/2001 OPM
632 END IF;
633 IF l_debug_level > 0 THEN
634 oe_debug_pub.add('Adjusting ordered qty on open line' );
635 END IF;
636
637 -- adjust the open ordered_quantity
638 l_open_line_rec := l_temp_open_line_rec;
639 l_updated_quantity := l_open_line_rec.ordered_quantity -
640 p_quantity;
641 l_open_line_rec.ordered_quantity := l_updated_quantity;
642 IF b_dual_qty THEN
643 l_updated_quantity2 := l_open_line_rec.ordered_quantity2 -
644 p_quantity2; -- 04/20/2001 OPM
645 l_open_line_rec.ordered_quantity2 := l_updated_quantity2;
646 END IF;
647 l_open_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
648 b_adjust_open_quantity := TRUE;
649 ELSIF l_updated_quantity = 0 THEN
650 -- Take the received line back to AWAITING_RETURN
651 IF l_debug_level > 0 THEN
652 oe_debug_pub.add('Taking the line back to AWAITING_RETURN');
653 END IF;
654 b_neg_corr_zero_qty := TRUE;
655 l_line_tbl(1).shipped_quantity := NULL;
656 IF b_dual_qty THEN
657 l_line_tbl(1).shipped_quantity2 := NULL; -- 04/20/2001 OPM
658 END IF;
659 l_line_tbl(1).flow_status_code := 'AWAITING_RETURN';
660 ELSE
661 -- Give error that it is a wrong correction
662 FND_MESSAGE.Set_Name('ONT', 'OE_RETURN_INVALID_RCVD_QTY');
663 OE_MSG_PUB.Add;
664 x_return_status := FND_API.G_RET_STS_ERROR ;
665 RAISE FND_API.G_EXC_ERROR;
666 END IF;
667 END IF; -- IF (l_open_line_id is NULL) THEN
668
669 END IF;
670
671 END IF;
672 ELSIF (p_parent_transaction_type in (G_RMA_ACCEPT,G_RMA_REJECT)) THEN
673 IF ( p_transaction_type = G_RMA_DELIVER) THEN
674 IF l_debug_level > 0 THEN
675 oe_debug_pub.add( 'PARENT IS ACCEPT/REJECT , TRX TYPE IS DELIVER' ) ;
676 END IF;
677
678 -- update fulfilled_quantity, and possibility to complete inspection.
679 b_om_processing := TRUE;
680
681 -- get the current fulfilled quantity
682 IF ((l_line_tbl(1).fulfilled_quantity = FND_API.G_MISS_NUM) OR
683 (l_line_tbl(1).fulfilled_quantity is NULL)) THEN
684 l_updated_quantity := p_quantity;
685 IF b_dual_qty THEN
686 l_updated_quantity2 := p_quantity2; -- 04/20/2001 OPM
687 END IF;
688 ELSE
689 l_updated_quantity := l_line_tbl(1).fulfilled_quantity + p_quantity;
690 IF b_dual_qty THEN
691 l_updated_quantity2 := l_line_tbl(1).fulfilled_quantity2 + p_quantity2; -- 04/20/2001 OPM
692 END IF;
693 END IF;
694
695 -- over quantity being passed
696 IF (l_line_tbl(1).shipped_quantity < l_updated_quantity ) THEN
697 FND_MESSAGE.Set_Name('ONT', 'OE_RETURN_INVALID_DLVR_QTY');
698 OE_MSG_PUB.Add;
699 x_return_status := FND_API.G_RET_STS_ERROR ;
700 RAISE FND_API.G_EXC_ERROR;
701 END IF;
702
703 /* Commented for Bug 5501949
704 l_line_tbl(1).fulfilled_quantity := l_updated_quantity;
705 IF b_dual_qty THEN
706 l_line_tbl(1).fulfilled_quantity2 := l_updated_quantity2; -- 04/20/2001 OPM
707 END IF;
708 */
709
710
711 l_credit_rejected_qty := nvl(FND_PROFILE.VALUE('ONT_GENERATE_CREDIT_REJECTED_RETURNS'), 'N'); -- Added for bug 6052676
712
713 IF l_debug_level > 0 THEN
714 oe_debug_pub.add(' Profile : ONT_GENERATE_CREDIT_REJECTED_RETURNS : ' || l_credit_rejected_qty, 5);
715 END IF;
716
717
718 /* Modified below IF and ELSIF conditions for bug 6052676 */
719
720 /* Start bug 5501949 */
721
722 /* Start bug 6052676 */
723
724 IF p_parent_transaction_type = G_RMA_REJECT AND l_credit_rejected_qty ='N'
725 THEN
726 l_line_tbl(1).shipped_quantity := l_line_tbl(1).shipped_quantity - p_quantity;
727 IF b_dual_qty THEN
728 l_line_tbl(1).shipped_quantity2 := l_line_tbl(1).shipped_quantity2 - p_quantity2;
729 END IF;
730 ELSIF p_parent_transaction_type = G_RMA_ACCEPT OR ( p_parent_transaction_type = G_RMA_REJECT AND l_credit_rejected_qty ='Y' ) THEN
731 l_line_tbl(1).fulfilled_quantity := l_updated_quantity;
732 IF b_dual_qty THEN
733 l_line_tbl(1).fulfilled_quantity2 := l_updated_quantity2;
734 END IF;
735 END IF;
736 /* End Bug 5501949 */
737 /* End Bug 6052676 */
738
739
740 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
741
742 -- Modified for Bug 5501949 IF (l_line_tbl(1).shipped_quantity = l_updated_quantity) THEN
743 --Added NVL for bug 6110517
744 IF nvl(l_line_tbl(1).shipped_quantity,0) = nvl(l_line_tbl(1).fulfilled_quantity,0) THEN
745 b_complete_inspection := TRUE;
746 END IF;
747
748 ELSIF (p_transaction_type = G_RMA_RETURN_TO_CUSTOMER) THEN
749 IF l_debug_level > 0 THEN
750 oe_debug_pub.add( 'PARENT IS ACCEPT/REJECT , TRX TYPE IS RETURN TO CUSTOMER' ) ;
751 END IF;
752
753 -- update shipped_quantity
754 b_om_processing := TRUE;
755 IF l_debug_level > 0 THEN
756 oe_debug_pub.add( 'PARENT IS RECEIVE , TRX TYPE IS RETURN TO CUSTOMER' ) ;
757 END IF;
758
759 l_updated_quantity := l_line_tbl(1).shipped_quantity - p_quantity;
760 IF b_dual_qty THEN
761 l_updated_quantity2 := l_line_tbl(1).shipped_quantity2 - p_quantity2; -- 04/20/2001 OPM
762 l_line_tbl(1).shipped_quantity2 := l_updated_quantity2;
763 END IF;
764 l_line_tbl(1).shipped_quantity := l_updated_quantity;
765 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
766 l_control_rec.check_security := FALSE;
767
768 -- For the bug fix 5099112
769 -- FOR the bug fix 7571804
770 IF NVL(l_line_tbl(1).shipped_quantity,0) = NVL(l_line_tbl(1).fulfilled_quantity,0)
771 AND l_line_tbl(1).flow_status_code='AWAITING_RETURN_DISPOSITION'
772 THEN
773 b_complete_inspection := TRUE;
774 END IF;
775 END IF;
776 ELSIF (p_parent_transaction_type = OE_RMA_RECEIVING.G_RMA_DELIVER) THEN
777
778 IF (p_transaction_type = OE_RMA_RECEIVING.G_RMA_RETURN_TO_CUSTOMER) THEN
779 IF l_debug_level > 0 THEN
780 oe_debug_pub.add( 'PARENT IS DELIVER , TRX TYPE IS RETURN TO CUSTOMER' ) ;
781 END IF;
782
783 -- update shipped_quantity and fulfilled_quantity
784 b_om_processing := TRUE;
785
786 l_line_tbl(1).shipped_quantity
787 := l_line_tbl(1).shipped_quantity - p_quantity;
788 l_line_tbl(1).fulfilled_quantity
789 := l_line_tbl(1).fulfilled_quantity - p_quantity;
790 IF b_dual_qty THEN
791 l_line_tbl(1).shipped_quantity2
792 := l_line_tbl(1).shipped_quantity2 - p_quantity2; -- 04/20/2001 OPM
793 l_line_tbl(1).fulfilled_quantity2
794 := l_line_tbl(1).fulfilled_quantity2 - p_quantity2;
795 END IF;
796 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
797 l_control_rec.check_security := FALSE;
798
799
800 ELSIF (p_transaction_type = OE_RMA_RECEIVING.G_RMA_RETURN_TO_RECEIVING) THEN
801 IF l_debug_level > 0 THEN
802 oe_debug_pub.add( 'PARENT IS DELIVER , TRX TYPE IS RETURN TO CUSTOMER' ) ;
803 END IF;
804
805 -- update fulfilled_quantity
806 b_om_processing := TRUE;
807
808 l_updated_quantity := l_line_tbl(1).fulfilled_quantity - p_quantity;
809 l_line_tbl(1).fulfilled_quantity := l_updated_quantity;
810 IF b_dual_qty THEN
811 l_updated_quantity2 := l_line_tbl(1).fulfilled_quantity2 - p_quantity2; -- 04/20/2001 OPM
812 l_line_tbl(1).fulfilled_quantity2 := l_updated_quantity2;
813 END IF;
814 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
815 l_control_rec.check_security := FALSE;
816
817
818 ELSIF (p_transaction_type = OE_RMA_RECEIVING.G_RMA_CORRECT) THEN
819 IF l_debug_level > 0 THEN
820 oe_debug_pub.add( 'PARENT IS DELIVER , TRX TYPE IS RETURN TO CUSTOMER' ) ;
821 END IF;
822
823 -- update fulfilled_quantity
824 b_om_processing := TRUE;
825
826 l_updated_quantity := l_line_tbl(1).fulfilled_quantity + p_quantity;
827 l_line_tbl(1).fulfilled_quantity := l_updated_quantity;
828 IF b_dual_qty THEN
829 l_updated_quantity2 := l_line_tbl(1).fulfilled_quantity2 + p_quantity2; -- 04/20/2001 OPM
830 l_line_tbl(1).fulfilled_quantity2 := l_updated_quantity2;
831 END IF;
832 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
833 l_control_rec.check_security := FALSE;
834
835 -- do we want to check if the line gets fulfilled at this point?
836
837 END IF;
838
839 ELSIF (p_parent_transaction_type =
840 OE_RMA_RECEIVING.G_RMA_RETURN_TO_CUSTOMER) THEN
841
842 IF (p_transaction_type = OE_RMA_RECEIVING.G_RMA_CORRECT) THEN
843 IF l_debug_level > 0 THEN
844 oe_debug_pub.add( 'PARENT IS RETURN TO CUST , TRX TYPE IS CORRECT' ) ;
845 END IF;
846
847 -- update shipped_quantity and fulfilled_quantity
848 b_om_processing := TRUE;
849
850 l_line_tbl(1).shipped_quantity
851 := l_line_tbl(1).shipped_quantity - p_quantity;
852
853 -- No need to update fulfilled_quantity as this Return To Customer has
854 -- happen before delivery transaction. Fix for bug 2811397
855 -- l_line_tbl(1).fulfilled_quantity
856 -- := l_line_tbl(1).fulfilled_quantity - p_quantity;
857
858 -- PO can specify the Trn that happened before return to customer we
859 -- can now reduce the fulfilled qty if the item was delivered before
860 -- return to customer.
861
862 IF p_R2Cust_Parent_Trn_Type = G_RMA_DELIVER AND
863 l_line_tbl(1).fulfilled_quantity > 0
864 THEN
865 l_line_tbl(1).fulfilled_quantity
866 := l_line_tbl(1).fulfilled_quantity - p_quantity;
867
868 IF b_dual_qty and l_line_tbl(1).fulfilled_quantity2 > 0 THEN
869 l_line_tbl(1).fulfilled_quantity2
870 := l_line_tbl(1).fulfilled_quantity2 - p_quantity2;
871 END IF;
872 END IF;
873
874 IF b_dual_qty THEN
875 l_line_tbl(1).shipped_quantity2
876 := l_line_tbl(1).shipped_quantity2 - p_quantity2; -- 04/20/2001 OPM
877 END IF;
878
879 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
880 l_control_rec.check_security := FALSE;
881
882 IF l_line_tbl(1).shipped_quantity = l_line_tbl(1).fulfilled_quantity
883 AND l_line_tbl(1).flow_status_code='AWAITING_RETURN_DISPOSITION'
884 THEN
885 b_complete_inspection := TRUE;
886 END IF;
887
888
889 END IF;
890
891 ELSIF (p_parent_transaction_type =
892 OE_RMA_RECEIVING.G_RMA_RETURN_TO_RECEIVING) THEN
893
894 IF (p_transaction_type = OE_RMA_RECEIVING.G_RMA_CORRECT) THEN
895 IF l_debug_level > 0 THEN
896 oe_debug_pub.add( 'PARENT IS RETURN TO RECEIVING , TRX TYPE IS CORRECT' ) ;
897 END IF;
898
899 -- update fulfilled_quantity
900 b_om_processing := TRUE;
901
902 l_line_tbl(1).fulfilled_quantity
903 := l_line_tbl(1).fulfilled_quantity - p_quantity;
904 IF b_dual_qty THEN
905 l_line_tbl(1).fulfilled_quantity2
906 := l_line_tbl(1).fulfilled_quantity2 - p_quantity2; -- 04/20/2001 OPM
907 END IF;
908 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
909
910 END IF;
911
912 ELSIF (p_parent_transaction_type =
913 OE_RMA_RECEIVING.G_RMA_UNMATCHED_ORDER) THEN
914
915 IF (p_transaction_type = OE_RMA_RECEIVING.G_RMA_MATCH) THEN
916 IF l_debug_level > 0 THEN
917 oe_debug_pub.add( 'PARENT IS UNMATCHED ORDER , TRX TYPE IS MATCH ' ) ;
918 END IF;
919
920 -- update shipped_quantity
921 b_om_processing := TRUE;
922 b_complete_receiving := TRUE;
923
924 -- should not be receiving more than the ordered quantity
925
926 IF ((l_line_tbl(1).shipped_quantity = FND_API.G_MISS_NUM) OR
927 (l_line_tbl(1).shipped_quantity is NULL)) THEN
928 l_updated_quantity := p_quantity;
929 IF b_dual_qty THEN
930 l_updated_quantity2 := p_quantity2; -- 04/20/2001 OPM
931 END IF;
932 ELSE
933 l_updated_quantity := l_line_tbl(1).shipped_quantity + p_quantity;
934 IF b_dual_qty THEN
935 l_updated_quantity2 := l_line_tbl(1).shipped_quantity2 + p_quantity2; -- 04/20/2001 OPM
936 END IF;
937 END IF;
938
939 -- received quantity should be within tolerance
940 IF (l_updated_quantity >
941 l_line_tbl(1).ordered_quantity + l_tolerance_above) THEN
942 FND_MESSAGE.Set_Name('ONT', 'OE_RETURN_INVALID_RCVD_QTY');
943 OE_MSG_PUB.Add;
944 x_return_status := FND_API.G_RET_STS_ERROR ;
945 RAISE FND_API.G_EXC_ERROR;
946 END IF;
947
948 -- under return, split the line
949 IF (l_updated_quantity <
950 l_line_tbl(1).ordered_quantity - l_tolerance_below) then
951
952 IF l_debug_level > 0 THEN
953 oe_debug_pub.add( 'UNDER RETURN:'||TO_CHAR ( P_QUANTITY ) ||'<'||TO_CHAR ( L_LINE_TBL ( 1 ) .ORDERED_QUANTITY+L_TOLERANCE_BELOW ) , 1 ) ;
954 END IF;
955
956 l_open_line_rec.db_flag := FND_API.G_FALSE;
957 l_open_line_rec.ordered_quantity
958 := l_old_line_tbl(1).ordered_quantity - p_quantity;
959 l_open_line_rec.split_from_line_id := p_RMA_Line_ID;
960 l_open_line_rec.split_by := 'SYSTEM';
961 l_open_line_rec.operation := OE_GLOBALS.G_OPR_CREATE;
962 l_line_tbl(1).ordered_quantity := p_quantity;
963 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
964 l_line_tbl(1).shipped_quantity := p_quantity;
965
966 IF b_dual_qty THEN
967 l_open_line_rec.ordered_quantity2
968 := l_old_line_tbl(1).ordered_quantity2 - p_quantity2;
969 l_line_tbl(1).ordered_quantity2 := p_quantity2; -- 04/20/2001 OPM
970 l_line_tbl(1).shipped_quantity2 := p_quantity2;
971 END IF;
972 l_line_tbl(1).split_action_code := 'SPLIT';
973 l_line_tbl(1).split_by := 'SYSTEM';
974 b_adjust_open_quantity := TRUE;
975
976 ELSE -- full return
977
978 l_line_tbl(1).shipped_quantity := p_quantity;
979 IF b_dual_qty THEN
980 l_line_tbl(1).shipped_quantity2 := p_quantity2; -- 04/20/2001 OPM
981 END IF;
982
983
984 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
985
986 END IF;
987
988 -- Set the actual shipment date on the RMA line
989 -- We will need to look at UNORDERED transaction to figure out
990 -- the actual shipment date.
991 SELECT MAX(transaction_date)
992 INTO l_line_tbl(1).actual_shipment_date
993 FROM rcv_transactions
994 WHERE transaction_type = 'UNORDERED'
995 AND oe_order_line_id = l_line_tbl(1).line_id;
996
997 IF l_line_tbl(1).actual_shipment_date IS NULL THEN
998 l_line_tbl(1).actual_shipment_date := sysdate;
999 END IF;
1000
1001 END IF;
1002
1003 END IF; -- p_parent_transaction_type
1004
1005 IF (b_om_processing) THEN
1006 IF l_debug_level > 0 THEN
1007 oe_debug_pub.add( 'OM_PROCESSING , CALLING PROCESS ORDER' ) ;
1008 END IF;
1009
1010 IF (b_adjust_open_quantity) THEN
1011
1012 l_line_tbl(2) := l_open_line_rec;
1013
1014 END IF;
1015
1016 -- Call OE_Order_PVT.Process_order
1017
1018 OE_Order_PVT.Process_order
1019 ( p_api_version_number => 1.0
1020 , p_init_msg_list => FND_API.G_TRUE
1021 , x_return_status => x_return_status
1022 , x_msg_count => x_msg_count
1023 , x_msg_data => x_msg_data
1024 , p_control_rec => l_control_rec
1025 , p_x_line_tbl => l_line_tbl
1026 , p_old_line_tbl => l_old_line_tbl
1027 , p_x_header_rec => l_x_header_rec
1028 , p_x_Header_Adj_tbl => l_x_Header_Adj_tbl
1029 , p_x_header_price_att_tbl => l_x_header_price_att_tbl
1030 , p_x_Header_Adj_att_tbl => l_x_Header_Adj_att_tbl
1031 , p_x_Header_Adj_Assoc_tbl => l_x_Header_Adj_Assoc_tbl
1032 , p_x_Header_Scredit_tbl => l_x_Header_Scredit_tbl
1033 --serla begin
1034 , p_x_Header_Payment_tbl => l_x_Header_Payment_tbl
1035 --serla end
1036 , p_x_Line_Adj_tbl => l_x_Line_Adj_tbl
1037 , p_x_Line_Scredit_tbl => l_x_Line_Scredit_tbl
1038 --serla begin
1039 , p_x_Line_Payment_tbl => l_x_Line_Payment_tbl
1040 --serla end
1041 , p_x_Line_Price_att_tbl => l_x_Line_Price_att_tbl
1042 , p_x_Line_Adj_att_tbl => l_x_Line_Adj_att_tbl
1043 , p_x_Line_Adj_Assoc_tbl => l_x_Line_Adj_Assoc_tbl
1044 , p_x_Lot_Serial_tbl => l_x_Lot_Serial_tbl
1045 , p_x_action_request_tbl => l_x_Action_Request_tbl
1046 );
1047
1048 IF l_debug_level > 0 THEN
1049 oe_debug_pub.add( 'PROCESS ORDER Return Status IS '||x_return_status);
1050 END IF;
1051 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1052 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1053 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1054 RAISE FND_API.G_EXC_ERROR;
1055 END IF;
1056
1057 -- complete workflow activity
1058
1059 IF (b_complete_receiving) THEN
1060 IF l_debug_level > 0 THEN
1061 oe_debug_pub.add( 'In b_complete_receiving ');
1062 END IF;
1063
1064 WF_ENGINE.CompleteActivityInternalName('OEOL', l_item_key,
1065 'RMA_WAIT_FOR_RECEIVING', 'COMPLETE');
1066
1067 ELSIF (b_complete_inspection) THEN
1068
1069 IF l_debug_level > 0 THEN
1070 oe_debug_pub.add( 'In b_complete_inspection ');
1071 END IF;
1072 WF_ENGINE.CompleteActivityInternalName('OEOL', l_item_key,
1073 'RMA_WAIT_FOR_INSPECTION', 'COMPLETE');
1074
1075 END IF; -- complete wf activity
1076
1077
1078 IF (b_neg_corr_zero_qty) THEN
1079 l_item_key := to_char(l_line_tbl(1).line_id);
1080
1081 IF l_debug_level > 0 THEN
1082 oe_debug_pub.add( 'In b_neg_corr_zero_qty ');
1083 END IF;
1084 WF_ENGINE.CompleteActivityInternalName('OEOL', l_item_key,
1085 'RMA_WAIT_FOR_INSPECTION', 'CORRECT_RECEIVING');
1086 END IF;
1087
1088 IF (b_negative_correction) THEN
1089 l_item_key := to_char(l_line_tbl(2).line_id);
1090
1091 IF l_debug_level > 0 THEN
1092 oe_debug_pub.add( 'In b_negative_correction ');
1093 END IF;
1094 WF_ENGINE.CompleteActivityInternalName('OEOL', l_item_key,
1095 'RMA_WAIT_FOR_INSPECTION', 'CORRECT_RECEIVING');
1096 ELSIF (b_positive_correction) THEN
1097 IF l_debug_level > 0 THEN
1098 oe_debug_pub.add( 'In b_positive_correction ');
1099 END IF;
1100 l_item_key := to_char(l_line_tbl(1).line_id);
1101
1102 WF_ENGINE.CompleteActivityInternalName('OEOL', l_item_key,
1103 'RMA_WAIT_FOR_RECEIVING', 'COMPLETE');
1104 END IF;
1105
1106 -- Set the line Flow status
1107
1108 IF b_complete_receiving or b_positive_correction THEN
1109 IF l_debug_level > 0 THEN
1110 oe_debug_pub.add( 'CALLING FLOW STATUS API 1 ' ||l_item_key ) ;
1111 END IF;
1112
1113 OE_Order_WF_Util.Update_Flow_Status_Code
1114 (p_line_id => to_number(l_item_key),
1115 p_flow_status_code => 'AWAITING_RETURN_DISPOSITION',
1116 x_return_status => l_return_status
1117 );
1118
1119 IF l_debug_level > 0 THEN
1120 oe_debug_pub.add( 'RETURN STATUS FROM FLOW STATUS API 1 '|| L_RETURN_STATUS , 1 ) ;
1121 END IF;
1122
1123 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1124 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1125 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1126 RAISE FND_API.G_EXC_ERROR;
1127 END IF;
1128
1129 END IF;
1130
1131 IF b_complete_inspection THEN
1132 IF l_debug_level > 0 THEN
1133 oe_debug_pub.add( 'CALLING FLOW STATUS API 2 ' ||l_item_key);
1134 END IF;
1135
1136 OE_Order_WF_Util.Update_Flow_Status_Code
1137 (p_line_id => to_number(l_item_key),
1138 p_flow_status_code => 'RETURNED',
1139 x_return_status => l_return_status
1140 );
1141
1142 IF l_debug_level > 0 THEN
1143 oe_debug_pub.add( 'RETURN STATUS FROM FLOW STATUS API 2'|| L_RETURN_STATUS , 1 ) ;
1144 END IF;
1145
1146 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1147 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1148 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1149 RAISE FND_API.G_EXC_ERROR;
1150 END IF;
1151
1152 END IF;
1153
1154 END IF; -- if b_om_processing.
1155
1156 IF l_debug_level > 0 THEN
1157 oe_debug_pub.add('Returning From Push_Receiving_Info '|| x_return_status);
1158 END IF;
1159
1160 EXCEPTION
1161
1162 WHEN FND_API.G_EXC_ERROR THEN
1163 x_return_status := FND_API.G_RET_STS_ERROR;
1164
1165 OE_MSG_PUB.Get
1166 ( p_msg_index => OE_MSG_PUB.G_LAST
1167 , p_encoded => FND_API.G_FALSE
1168 , p_data => x_msg_data
1169 , p_msg_index_out => x_msg_count
1170 );
1171
1172 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1173 IF OE_MSG_PUB.Check_MSg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1174 OE_MSG_PUB.Add_Exc_Msg
1175 (G_PKG_NAME
1176 ,'Push_Receiving_Info'
1177 );
1178 END IF;
1179 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1180
1181 OE_MSG_PUB.Get
1182 ( p_msg_index => OE_MSG_PUB.G_LAST
1183 , p_encoded => FND_API.G_FALSE
1184 , p_data => x_msg_data
1185 , p_msg_index_out => x_msg_count
1186 );
1187
1188 WHEN OTHERS THEN
1189
1190 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1191 THEN
1192 OE_MSG_PUB.Add_Exc_Msg
1193 ( G_PKG_NAME
1194 , 'Push_Receiving_Info'
1195 );
1196 END IF;
1197
1198 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1199
1200 OE_MSG_PUB.Get
1201 ( p_msg_index => OE_MSG_PUB.G_LAST
1202 , p_encoded => FND_API.G_FALSE
1203 , p_data => x_msg_data
1204 , p_msg_index_out => x_msg_count
1205 );
1206
1207 END Push_Receiving_Info;
1208
1209
1210 FUNCTION Get_Open_Line_Id (p_line_rec IN OE_ORDER_PUB.line_rec_type)
1211 RETURN NUMBER
1212 IS
1213
1214 l_open_line_id NUMBER;
1215
1216 --
1217 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1218 --
1219 BEGIN
1220
1221 SELECT l.line_id
1222 INTO l_open_line_id
1223 FROM oe_order_lines l,
1224 wf_item_activity_statuses wf,
1225 wf_process_activities wpa
1226 WHERE l.open_flag = 'Y'
1227 and wpa.activity_item_type='OEOL'
1228 and wpa.activity_name='RMA_WAIT_FOR_RECEIVING'
1229 and wf.item_type='OEOL'
1230 and wf.process_activity=wpa.instance_id
1231 and wf.activity_status='NOTIFIED'
1232 -- and l.line_id=to_number(wf.item_key)
1233 and to_char(l.line_id) = wf.item_key --FP bug#5758850
1234 and l.line_set_id = p_line_rec.line_set_id
1235 and l.shipped_quantity is null
1236 and l.line_id<>p_line_rec.line_id
1237 and rownum = 1;
1238
1239 RETURN l_open_line_id;
1240
1241 EXCEPTION
1242
1243 WHEN NO_DATA_FOUND THEN
1244 RETURN NULL;
1245
1246 END Get_Open_Line_Id;
1247
1248 /* This procedure is for PO to find remaining open quantity from OE.
1249 After a line is under-received, OE split the line into two lines,
1250 one with the received qty, another with the remaining qty. This API
1251 shields PO from the splitting */
1252 Procedure Get_RMA_Available_Quantity(p_RMA_Line_ID In Number,
1253 x_Quantity out nocopy Number,
1254
1255 x_Return_Status out nocopy Varchar2,
1256
1257 x_Msg_Count out nocopy Number,
1258
1259 x_MSG_Data out nocopy Varchar2)
1260
1261 IS
1262 l_quantity NUMBER;
1263 l_open_flag VARCHAR2(1);
1264 l_inv_interface_status_code VARCHAR2(30);
1265 l_ordered_quantity NUMBER;
1266 l_shipped_quantity NUMBER;
1267 l_line_set_id NUMBER;
1268 --
1269 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1270 --
1271 BEGIN
1272 -- OE_GLOBALS.set_context; -- removed for MOAC.
1273 IF l_debug_level > 0 THEN
1274 oe_debug_pub.add( 'G_ORG_ID='||TO_CHAR ( OE_GLOBALS.G_ORG_ID ) , 1 ) ;
1275 END IF;
1276 SELECT open_flag,
1277 invoice_interface_status_code,
1278 ordered_quantity,
1279 shipped_quantity,
1280 line_set_id
1281 INTO l_open_flag,
1282 l_inv_interface_status_code,
1283 l_ordered_quantity,
1284 l_shipped_quantity,
1285 l_line_set_id
1286 FROM OE_ORDER_LINES_ALL
1287 WHERE line_id = p_rma_line_id;
1288
1289 IF (l_open_flag = 'N' OR
1290 l_inv_interface_status_code IS NOT NULL OR
1291 l_ordered_quantity = l_shipped_quantity) THEN
1292 /* this line is closed, or AR interfaced, or fully received */
1293 select l.ordered_quantity
1294 into x_quantity
1295 from oe_order_lines_all l,
1296 wf_item_activity_statuses wf,
1297 wf_process_activities wpa
1298 where l.open_flag = 'Y'
1299 and wpa.activity_item_type='OEOL'
1300 and wpa.activity_name='RMA_WAIT_FOR_RECEIVING'
1301 and wf.item_type='OEOL'
1302 and wf.process_activity=wpa.instance_id
1303 and wf.activity_status='NOTIFIED'
1304 -- and l.line_id=to_number(wf.item_key)
1305 and to_char(l.line_id) = wf.item_key -- FP bug#5758850
1306 and l.line_set_id = l_line_set_id
1307 and l.shipped_quantity is null and l.line_id<>p_RMA_line_id;
1308 ELSE
1309 x_quantity := l_ordered_quantity - nvl(l_shipped_quantity,0);
1310 END IF;
1311 EXCEPTION
1312
1313 WHEN NO_DATA_FOUND THEN
1314 x_quantity := 0;
1315
1316 WHEN OTHERS THEN
1317
1318 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_UNEXP_ERROR)
1319 THEN
1320 oe_msg_pub.Add_Exc_Msg
1321 ( G_PKG_NAME
1322 , 'GET_RMA_Available_Quantity'
1323 );
1324 END IF;
1325 END Get_RMA_Available_Quantity;
1326
1327 /* This procedure is used by PO to get over and under return tolerances
1328 for a RMA Line. */
1329
1330 Procedure Get_RMA_Tolerances(
1331 p_RMA_Line_ID In Number,
1332 x_Under_Return_Tolerance out nocopy Number,
1333
1334 x_Over_Return_Tolerance out nocopy Number,
1335
1336 x_Return_Status out nocopy Varchar2,
1337
1338 x_Msg_Count out nocopy Number,
1339
1340 x_MSG_Data out nocopy Varchar2
1341
1342 )
1343 IS
1344 --
1345 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1346 --
1347 BEGIN
1348
1349 x_return_status := FND_API.G_RET_STS_SUCCESS;
1350
1351
1352 Select SHIP_TOLERANCE_BELOW, SHIP_TOLERANCE_ABOVE
1353 into x_Under_Return_Tolerance,x_Over_Return_Tolerance
1354 from oe_order_lines_all
1355 Where line_id = p_RMA_Line_ID;
1356
1357 EXCEPTION
1358
1359 WHEN OTHERS THEN
1360
1361 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_UNEXP_ERROR)
1362 THEN
1363 oe_msg_pub.Add_Exc_Msg
1364 ( G_PKG_NAME
1365 , 'Get_RMA_Tolerances'
1366 );
1367 END IF;
1368
1369 -- Get message count and data
1370
1371 OE_MSG_PUB.Count_And_Get
1372 ( p_count => x_msg_count
1373 , p_data => x_msg_data
1374 );
1375
1376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1377
1378 END Get_RMA_Tolerances;
1379
1380 END OE_RMA_RECEIVING;