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