1 PACKAGE BODY AHL_OSP_RCV_PVT AS
2 /* $Header: AHLVORCB.pls 120.16.12020000.3 2013/02/12 04:59:04 jrotich ship $ */
3
4 -- Global variable containing package name for debugs messages
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_OSP_RCV_PVT';
6 G_TRANSACTION_TABLE CONSTANT VARCHAR2(30) := 'OE_ORDER_LINES_ALL';
7 G_CSI_T_SOURCE_LINE_REF CONSTANT VARCHAR2(50) := 'AHL_OSP_ORDER_LINES';
8 -- Ship-only transaction type
9 G_OM_ORDER CONSTANT VARCHAR2(30) := 'OM_SHIPMENT';
10 -- Return transaction type
11 G_OM_RETURN CONSTANT VARCHAR2(30) := 'RMA_RECEIPT';
12
13 --Shipment Group API parameters
14 x_header_rec OE_ORDER_PUB.Header_Rec_Type;
15 x_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type;
16 x_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;
17 x_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type;
18 x_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
19 x_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type ;
20 x_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
21 x_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;
22 x_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;
23 x_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
24 x_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type;
25 x_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type;
26 x_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;
27 x_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
28 x_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type ;
29 x_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
30 x_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;
31 x_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type;
32 x_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type;
33 x_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;
34 x_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
35
36 ---------------------------------------------------------------------
37 -- Declare local/private APIs defined in the package later --
38 ---------------------------------------------------------------------
39
40 PROCEDURE Validate_Receiving_Params (
41 p_rma_receipt_rec IN RMA_Receipt_Rec_Type
42 );
43
44 -- SALOGAN added validate_create_locator for 9496606 Start
45 PROCEDURE validate_create_locator (
46 p_rma_receipt_rec IN RMA_Receipt_Rec_Type,
47 x_receiving_locator_id OUT NOCOPY NUMBER,
48 x_return_status OUT NOCOPY VARCHAR2,
49 x_msg_count OUT NOCOPY NUMBER,
50 x_msg_data OUT NOCOPY VARCHAR2);
51 -- SALOGAN added validate_create_locator for 9496606 end
52
53 PROCEDURE Update_OSP_Line_Exch_Instance(
54 p_osp_order_id IN NUMBER,
55 p_osp_line_id IN NUMBER,
56 p_exchange_instance_id IN NUMBER);
57
58 PROCEDURE Update_OSP_Order_Lines(
59 p_osp_order_id IN NUMBER,
60 p_osp_line_id IN NUMBER,
61 p_oe_ship_line_id IN NUMBER,
62 p_oe_return_line_id IN NUMBER);
63
64 ---------------------------------------------------------------------
65 -- Define the APIs for the package --
66 ---------------------------------------------------------------------
67
68 -- Start of Comments --
69 -- Function name : Can_Receive_Against_OSP
70 -- Type : Public
71 -- Functionality : Function to determine if an OSP Order is 'ready for receipt'.
72 -- It returns FND_API.G_TRUE if a receipt can be done. Otherwise, it returns FND_API.G_FALSE.
73 -- Pre-reqs :
74 --
75 -- Parameters:
76 --
77 -- p_osp_order_id IN NUMBER OSP Order Id
78 --
79 -- Version:
80 --
81 -- Initial Version 1.0
82 --
83 -- End of Comments --
84
85 FUNCTION Can_Receive_Against_OSP (
86 p_osp_order_id IN NUMBER
87 )
88 RETURN VARCHAR2 IS
89
90 -- Cursor to get the shipment header id, i.e. oe_header_id, for the given OSP order id.
91 CURSOR get_oe_header_id (c_osp_order_id NUMBER) IS
92 SELECT oe_header_id
93 FROM AHL_OSP_ORDERS_B
94 WHERE osp_order_id = c_osp_order_id;
95
96 -- Cursor to check whether the shipment, i.e. oe_header_id is booked or not.
97 CURSOR chk_shipment_booked (c_oe_header_id NUMBER) IS
98 SELECT 'X'
99 FROM OE_ORDER_HEADERS_ALL
100 WHERE header_id = c_oe_header_id
101 AND booked_flag = 'Y';
102
103 -- Cursor to get all the RMA type lines for the given sales order.
104 CURSOR get_rma_lines (c_oe_header_id NUMBER) IS
105 SELECT OLA.line_id
106 FROM OE_ORDER_LINES_ALL OLA, OE_LINE_TYPES_V OLT
107 WHERE OLA.header_id = c_oe_header_id
108 AND OLT.line_type_id = OLA.line_type_id
109 AND OLT.order_category_code = 'RETURN';
110
111 --
112 l_api_name CONSTANT VARCHAR2(30) := 'Can_Receive_Against_OSP';
113 l_debug_key CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
114
115 l_oe_header_id AHL_OSP_ORDERS_B.oe_header_id%TYPE;
116 l_oe_line_id OE_ORDER_LINES_ALL.line_id%TYPE;
117 l_dummy VARCHAR2(1);
118 --
119
120 BEGIN
121 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
122 FND_LOG.string(FND_LOG.level_procedure, l_debug_key||'.begin', 'API called with p_osp_order_id: '||p_osp_order_id);
123 END IF;
124
125 -- Check for the given OSP order id. If NULL, return FND_API.G_FALSE.
126 IF (p_osp_order_id IS NULL) THEN
127 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
128 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
129 'OSP order id is NULL. Returning False.');
130 END IF;
131
132 RETURN FND_API.G_FALSE;
133 END IF;
134
135 -- Get the shipment header id, i.e. oe_header_id, for the given OSP order id.
136 OPEN get_oe_header_id(p_osp_order_id);
137 FETCH get_oe_header_id INTO l_oe_header_id;
138 CLOSE get_oe_header_id;
139
140 -- Check for the oe_header_id. If NULL, it means no shipment has been created. Return FND_API.G_FALSE.
141 IF (l_oe_header_id IS NULL) THEN
142 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
143 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
144 'oe_header_id is NULL for the OSP order: '||p_osp_order_id||'. Returning False.');
145 END IF;
146
147 RETURN FND_API.G_FALSE;
148 END IF;
149
150 -- Check whether the shipment, i.e. oe_header_id is booked or not.
151 OPEN chk_shipment_booked(l_oe_header_id);
152 FETCH chk_shipment_booked INTO l_dummy;
153 IF (chk_shipment_booked%NOTFOUND) THEN
154 -- The shipment is not booked. Return FND_API.G_FALSE.
155 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
156 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
157 'Shipment '||l_oe_header_id||' is not booked. Returning False.');
158 END IF;
159
160 CLOSE chk_shipment_booked;
161 RETURN FND_API.G_FALSE;
162 END IF;
163 CLOSE chk_shipment_booked;
164
165 -- For a booked shipment, get all the RMA type lines.
166 OPEN get_rma_lines(l_oe_header_id);
167 LOOP
168 FETCH get_rma_lines INTO l_oe_line_id;
169 EXIT WHEN get_rma_lines%NOTFOUND;
170
171 -- Check whether the receipt can be done against the RMA line id or not.
172 IF (Can_Receive_Against_RMA(l_oe_line_id) = FND_API.G_TRUE) THEN
173 -- Receipt against RMA can be done for this line. Return FND_API.G_TRUE.
174 CLOSE get_rma_lines;
175 RETURN FND_API.G_TRUE;
176 ELSIF (Can_Receive_Against_PO(l_oe_line_id) = FND_API.G_TRUE) THEN
177 -- Receipt against PO can be done for this line. Return FND_API.G_TRUE.
178 CLOSE get_rma_lines;
179 RETURN FND_API.G_TRUE;
180 END IF;
181 END LOOP;
182 CLOSE get_rma_lines;
183
184 -- If none of the shipment/PO lines can be received, return FND_API.G_FALSE.
185 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
186 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
187 'None of the shipment/PO lines can be received. Returnig False.');
188 END IF;
189
190 RETURN FND_API.G_FALSE;
191 END Can_Receive_Against_OSP;
192
193
194 -- Start of Comments --
195 -- Function name : Can_Receive_Against_PO
196 -- Type : Public
197 -- Functionality : Function to determine if a receipt against PO can be done given an RMA line.
198 -- It returns FND_API.G_TRUE if a receipt can be done. Otherwise, it returns FND_API.G_FALSE.
199 -- Pre-reqs :
200 --
201 -- Parameters:
202 --
203 -- p_return_line_id IN NUMBER RMA Line Id
204 --
205 -- Version:
206 --
207 -- Initial Version 1.0
208 --
209 -- End of Comments --
210
211 FUNCTION Can_Receive_Against_PO (
212 p_return_line_id IN NUMBER
213 )
214 RETURN VARCHAR2 IS
215
216 -- Cursor to check whether the given return line is valid or not.
217 CURSOR chk_return_line (c_oe_line_id NUMBER) IS
218 SELECT 'X'
219 FROM OE_ORDER_LINES_ALL
220 WHERE line_id = c_oe_line_id
221 AND line_type_id = FND_PROFILE.VALUE('AHL_OSP_OE_RETURN_ID');
222
223 -- SATHAPLI::Bug 6877509 - changes start, 02-Apr-08
224 -- Cursor to get the ordered and shipped quantities of the given return line.
225 -- If the return line has been split (like in partial qty receive for non-serialized items), we have to look at
226 -- the cumulative qty of all the split lines so as to decide that the shipped qty is equal to or less than the ordered qty.
227 /*
228 CURSOR get_oe_quantities (c_oe_line_id NUMBER) IS
229 SELECT ordered_quantity, shipped_quantity
230 FROM OE_ORDER_LINES_ALL
231 WHERE line_id = c_oe_line_id;
232 */
233 CURSOR get_oe_quantities (c_oe_header_id NUMBER, c_oe_line_number NUMBER) IS
234 SELECT SUM(ordered_quantity), SUM(shipped_quantity)
235 FROM OE_ORDER_LINES_ALL
236 WHERE header_id = c_oe_header_id
237 AND line_number = c_oe_line_number
238 AND NVL(cancelled_flag, 'X') <> 'Y';
239
240 /*
241 NOTE: Instead of using the line_number, we can use split_from_line_id and get the cumulative qty using a hierarchical query.
242 But there is no index present on the column split_from_line_id in the table OE_ORDER_LINES_ALL, and the hierarchical query is
243 performance intensive. This approach may be pursued in the future, if need be.
244 */
245
246 -- Cursor to get the header_id and line_number of the given return line. This is mainly for split lines.
247 CURSOR get_oe_split_line_details (c_oe_line_id NUMBER) IS
248 SELECT header_id, line_number
249 FROM OE_ORDER_LINES_ALL
250 WHERE line_id = c_oe_line_id;
251 -- SATHAPLI::Bug 6877509 - changes end, 02-Apr-08
252
253 -- Cursor to get the PO header id for the OSP order corresponding to the given return line id.
254 -- Only those OSP orders need to be considered, which have the status as PO_CREATED or REQ_CREATED.
255 CURSOR get_po_header_id (c_oe_return_line_id NUMBER) IS
256 SELECT AOB.po_header_id,
257 AOB.po_req_header_id,
258 AOL.osp_order_id
259 FROM AHL_OSP_ORDER_LINES AOL, AHL_OSP_ORDERS_B AOB
260 WHERE AOL.oe_return_line_id = c_oe_return_line_id
261 AND AOL.osp_order_id = AOB.osp_order_id
262 AND AOB.status_code IN ('PO_CREATED', 'REQ_CREATED')
263 AND ROWNUM = 1;
264
265 -- Cursor to check whether the given Purchase order is approved or not.
266 CURSOR chk_po_header_approved (c_po_header_id NUMBER) IS
267 SELECT 'X'
268 FROM PO_HEADERS_ALL
269 WHERE NVL(approved_flag, 'N') = 'Y'
270 AND po_header_id = c_po_header_id;
271
272 -- Cursor to check whether the given Requisition is approved or not.
273 CURSOR chk_po_req_approved (c_po_req_header_id NUMBER) IS
274 SELECT 'X'
275 FROM PO_REQUISITION_HEADERS_ALL
276 WHERE NVL(authorization_status, 'X') = 'APPROVED'
277 AND requisition_header_id = c_po_req_header_id;
278
279 -- Cursor to get the PO line quantity details for all the OSP order lines, corresponding to the given return line id.
280 -- Only those OSP order lines need to be considered, which doesn't have the status as PO_DELETED or PO_CANCELLED, or
281 -- REQ_DELETED or REQ_CANCELLED.
282 CURSOR get_po_line_quantity1 (c_oe_return_line_id NUMBER) IS
283 SELECT AOL.po_line_id,
284 AOL.po_req_line_id,
285 POL.quantity,
286 (SELECT SUM(PLL.quantity_received)
287 FROM PO_LINE_LOCATIONS_ALL PLL
288 WHERE PLL.po_line_id = POL.po_line_id) quantity_received
289 FROM AHL_OSP_ORDER_LINES AOL, PO_LINES_ALL POL
290 WHERE AOL.oe_return_line_id = c_oe_return_line_id
291 AND POL.po_line_id(+) = AOL.po_line_id
292 AND NVL(AOL.status_code, 'X') <> 'PO_DELETED'
293 AND NVL(AOL.status_code, 'X') <> 'PO_CANCELLED'
294 AND NVL(AOL.status_code, 'X') <> 'REQ_DELETED'
295 AND NVL(AOL.status_code, 'X') <> 'REQ_CANCELLED';
296
297 -- Cursor to get the PO line quantity details for the given PO line id.
298 -- This will be used for those PO lines, which have been derived from Requisition lines.
299 CURSOR get_po_line_quantity2 (c_po_line_id NUMBER) IS
300 SELECT POL.quantity,
301 (SELECT SUM(PLL.quantity_received)
302 FROM PO_LINE_LOCATIONS_ALL PLL
303 WHERE PLL.po_line_id = POL.po_line_id) quantity_received
304 FROM PO_LINES_ALL POL
305 WHERE POL.po_line_id = c_po_line_id;
306
307 -- Cursor to get the PO line for the given Requisition line id. Only approved POs will be considered.
308 CURSOR get_po_line (c_po_req_line_id NUMBER) IS
309 SELECT PLL.po_line_id
310 FROM PO_LINE_LOCATIONS_ALL PLL, PO_REQUISITION_LINES REQ,
311 PO_HEADERS_ALL POH
312 WHERE REQ.requisition_line_id = c_po_req_line_id
313 AND PLL.line_location_id = REQ.line_location_id
314 AND PLL.po_header_id = POH.po_header_id
315 AND NVL(POH.approved_flag, 'N') = 'Y';
316
317 --
318 l_api_name CONSTANT VARCHAR2(30) := 'Can_Receive_Against_PO';
319 l_debug_key CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
320
321 l_osp_order_id AHL_OSP_ORDERS_B.osp_order_id%TYPE;
322 l_po_header_id AHL_OSP_ORDERS_B.po_header_id%TYPE;
323 l_po_req_header_id AHL_OSP_ORDERS_B.po_req_header_id%TYPE;
324 l_po_line_id AHL_OSP_ORDER_LINES.po_line_id%TYPE;
325 l_po_req_line_id AHL_OSP_ORDER_LINES.po_req_line_id%TYPE;
326 l_oe_ordered_qty OE_ORDER_LINES_ALL.ordered_quantity%TYPE;
327 l_oe_shipped_qty OE_ORDER_LINES_ALL.shipped_quantity%TYPE;
328 l_po_line_qty NUMBER;
329 l_po_line_tot_qty NUMBER;
330 l_dummy VARCHAR2(1);
331
332 l_oe_hdr_id OE_ORDER_LINES_ALL.header_id%TYPE;
333 l_oe_line_no OE_ORDER_LINES_ALL.line_number%TYPE;
334
335 TYPE PO_LINE_TBL_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
336 l_po_line_tbl PO_LINE_TBL_TYPE;
337 l_merged_req_line BOOLEAN := FALSE;
338 --
339
340 BEGIN
341 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
342 FND_LOG.string(FND_LOG.level_procedure, l_debug_key||'.begin', 'API called with p_return_line_id: '||p_return_line_id);
343 END IF;
344
345 -- Check whether the given return line is valid or not.
346 OPEN chk_return_line(p_return_line_id);
347 FETCH chk_return_line INTO l_dummy;
348 IF (chk_return_line%NOTFOUND) THEN
349 -- The given return line is invalid. Return FND_API.G_FALSE.
350 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
351 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
352 'Return line: '||p_return_line_id||' is invalid. Returnig False.');
353 END IF;
354
355 CLOSE chk_return_line;
356 RETURN FND_API.G_FALSE;
357 END IF;
358 CLOSE chk_return_line;
359
360 -- SATHAPLI::Bug 6877509 - changes start, 02-Apr-08
361 -- Get the header_id and line_number of the given return line.
362 OPEN get_oe_split_line_details(p_return_line_id);
363 FETCH get_oe_split_line_details INTO l_oe_hdr_id, l_oe_line_no;
364 CLOSE get_oe_split_line_details;
365
366 -- Get the ordered and shipped quantities of the given return line.
367 -- OPEN get_oe_quantities(p_return_line_id);
368 OPEN get_oe_quantities(l_oe_hdr_id, l_oe_line_no);
369 FETCH get_oe_quantities INTO l_oe_ordered_qty, l_oe_shipped_qty;
370 CLOSE get_oe_quantities;
371 -- SATHAPLI::Bug 6877509 - changes end, 02-Apr-08
372
373 -- PO receipt should be enabled only after RMA receipt is complete. For this, check for the ordered and
374 -- shipped quantities of the given return line. If the shipped quantity is less than the
375 -- ordered quantity, then it means there is still quantity left to be returned. Return FND_API.G_FALSE.
376 IF (l_oe_shipped_qty IS NULL OR l_oe_shipped_qty < l_oe_ordered_qty) THEN
377 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
378 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
379 'Shipped quantity is NULL, or less than the ordered quantity for the return line: '||p_return_line_id||
380 '. As receipt against RMA is not complete yet, returning False.');
381 END IF;
382
383 RETURN FND_API.G_FALSE;
384 END IF;
385
386 -- Get the PO header id for the OSP order corresponding to the given return line id.
387 OPEN get_po_header_id(p_return_line_id);
388 FETCH get_po_header_id INTO l_po_header_id, l_po_req_header_id, l_osp_order_id;
389 IF (get_po_header_id%FOUND) THEN
390 IF (l_po_header_id IS NULL) THEN
391 -- As the PO header id is NULL, check for the requisition header id.
392 IF (l_po_req_header_id IS NULL) THEN
393 -- Even the requisition header id is NULL. Return FND_API.G_FALSE.
394 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
395 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
396 'Both PO and requisition headers are NULL for the OSP order: '||l_osp_order_id||
397 '. Returning False.');
398 END IF;
399
400 CLOSE get_po_header_id;
401 RETURN FND_API.G_FALSE;
402 ELSE
403 -- Check whether the Requisition is approved or not.
404 -- If not approved, return FND_API.G_FALSE.
405 OPEN chk_po_req_approved(l_po_req_header_id);
406 FETCH chk_po_req_approved INTO l_dummy;
407 IF (chk_po_req_approved%NOTFOUND) THEN
408 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
409 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
410 'Requisition: '||l_po_header_id||' is not approved. Returning False.');
411 END IF;
412
413 CLOSE chk_po_req_approved;
414 CLOSE get_po_header_id;
415 RETURN FND_API.G_FALSE;
416 END IF;
417 CLOSE chk_po_req_approved;
418 END IF; -- if l_po_req_header_id IS NULL
419 ELSE
420 -- Check whether the Purchase order is approved or not.
421 -- If not approved, return FND_API.G_FALSE.
422 OPEN chk_po_header_approved(l_po_header_id);
423 FETCH chk_po_header_approved INTO l_dummy;
424 IF (chk_po_header_approved%NOTFOUND) THEN
425 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
426 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
427 'Purchase order: '||l_po_header_id||' is not approved. Returning False.');
428 END IF;
429
430 CLOSE chk_po_header_approved;
431 CLOSE get_po_header_id;
432 RETURN FND_API.G_FALSE;
433 END IF;
434 CLOSE chk_po_header_approved;
435 END IF; -- if l_po_header_id IS NULL
436 ELSE
437 -- This means that the OSP order is not in the PO_CREATED or REQ_CREATED status. Return FND_API.G_FALSE.
438 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
439 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
440 'OSP order: '||l_osp_order_id||' is not in status PO_CREATED or REQ_CREATED. Returnig False.');
441 END IF;
442
443 CLOSE get_po_header_id;
444 RETURN FND_API.G_FALSE;
445 END IF; -- if get_po_header_id%FOUND
446 CLOSE get_po_header_id;
447
448 -- After the OSP order and Purchase order or Requisition checks above, check for the PO line quantity for each OSP order line.
449 OPEN get_po_line_quantity1(p_return_line_id);
450 LOOP
451 FETCH get_po_line_quantity1 INTO l_po_line_id, l_po_req_line_id, l_po_line_qty, l_po_line_tot_qty;
452 EXIT WHEN get_po_line_quantity1%NOTFOUND;
453
454 -- If PO line is NULL, get it from the Requisition line.
455 l_merged_req_line := FALSE; -- Set merged Requisition line flag as FALSE at the start of the loop
456 IF (l_po_line_id IS NULL) THEN
457 OPEN get_po_line(l_po_req_line_id);
458 FETCH get_po_line INTO l_po_line_id;
459 IF (get_po_line%FOUND) THEN
460 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
461 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
462 'PO line id was NULL. Got it as '||l_po_line_id||' from the Requisition line: '||l_po_req_line_id);
463 END IF;
464
465 -- Check for merged Requisition lines, i.e. whether this PO line has already been checked or not.
466 IF (l_po_line_tbl.EXISTS(l_po_line_id)) THEN
467 l_merged_req_line := TRUE;
468 ELSE
469 l_merged_req_line := FALSE;
470 l_po_line_tbl(l_po_line_id) := l_po_line_id;
471
472 -- Get the PO line quantity and the total received quantity.
473 OPEN get_po_line_quantity2(l_po_line_id);
474 FETCH get_po_line_quantity2 INTO l_po_line_qty, l_po_line_tot_qty;
475 CLOSE get_po_line_quantity2;
476 END IF;
477 ELSE
478 -- Either PO is not created or its not approved.
479 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
480 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
481 'Either PO is not created or its not approved. Returning False');
482 END IF;
483
484 CLOSE get_po_line;
485 CLOSE get_po_line_quantity1;
486 RETURN FND_API.G_FALSE;
487 END IF; -- if get_po_line%FOUND
488 CLOSE get_po_line;
489 END IF;
490
491 -- For any of the PO lines, if the total received quantity is not less than the line quantity, return FND_API.G_FALSE.
492 -- No need to check this for merged Requisition lines, this check would already have happened for the first Requisition line.
493 IF NOT l_merged_req_line AND NOT (l_po_line_tot_qty < l_po_line_qty) THEN
494 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
495 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
496 'Total received quantity is not less than the line quantity for PO line id: '||l_po_line_id||'.'||
497 ' Returning False.');
498 END IF;
499
500 CLOSE get_po_line_quantity1;
501 RETURN FND_API.G_FALSE;
502 END IF;
503 END LOOP;
504 CLOSE get_po_line_quantity1;
505
506 -- If all the checks have been validated, return FND_API.G_TRUE.
507 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
508 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
509 'All checks validated. Returnig True.');
510 END IF;
511
512 RETURN FND_API.G_TRUE;
513 END Can_Receive_Against_PO;
514
515
516 -- Start of Comments --
517 -- Function name : Can_Receive_Against_RMA
518 -- Type : Public
519 -- Functionality : Function to determine if a receipt can be done against a given RMA line.
520 -- It returns FND_API.G_TRUE if a receipt can be done. Otherwise, it returns FND_API.G_FALSE.
521 -- Pre-reqs :
522 --
523 -- Parameters:
524 --
525 -- p_return_line_id IN NUMBER RMA Line Id
526 --
527 -- Version:
528 --
529 -- Initial Version 1.0
530 --
531 -- End of Comments --
532
533 FUNCTION Can_Receive_Against_RMA (
534 p_return_line_id IN NUMBER
535 )
536 RETURN VARCHAR2 IS
537
538 -- Cursor to check whether the given return line is valid or not.
539 CURSOR chk_return_line (c_oe_line_id NUMBER) IS
540 SELECT 'X'
541 FROM OE_ORDER_LINES_ALL
542 WHERE line_id = c_oe_line_id
543 AND line_type_id = FND_PROFILE.VALUE('AHL_OSP_OE_RETURN_ID');
544
545 -- Cursor to check whether the shipment is booked or not.
546 CURSOR chk_shipment_booked (c_oe_line_id NUMBER) IS
547 SELECT OHA.header_id
548 FROM OE_ORDER_LINES_ALL OLA, OE_ORDER_HEADERS_ALL OHA
549 WHERE OLA.line_id = c_oe_line_id
550 AND OHA.header_id = OLA.header_id
551 AND OHA.booked_flag = 'Y';
552
553 -- Cursor to get the ship line id of the OSP order lines, that correspond to the given return line.
554 CURSOR get_osp_ship_line_id (c_oe_return_line_id NUMBER) IS
555 SELECT oe_ship_line_id
556 FROM AHL_OSP_ORDER_LINES
557 WHERE oe_return_line_id = c_oe_return_line_id
558 AND ROWNUM = 1;
559
560 -- Cursor to get the ordered and shipped quantities of the given return line.
561 CURSOR get_oe_quantities (c_oe_line_id NUMBER) IS
562 SELECT ordered_quantity, shipped_quantity
563 FROM OE_ORDER_LINES_ALL
564 WHERE line_id = c_oe_line_id;
565
566 --
567 l_api_name CONSTANT VARCHAR2(30) := 'Can_Receive_Against_RMA';
568 l_debug_key CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
569
570 l_oe_header_id OE_ORDER_HEADERS_ALL.header_id%TYPE;
571 l_oe_ship_line_id AHL_OSP_ORDER_LINES.oe_ship_line_id%TYPE;
572 l_ship_line_qty_rec get_oe_quantities%ROWTYPE;
573 l_return_line_qty_rec get_oe_quantities%ROWTYPE;
574 l_dummy VARCHAR2(1);
575 --
576
577 BEGIN
578 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
579 FND_LOG.string(FND_LOG.level_procedure, l_debug_key||'.begin', 'API called with p_return_line_id: '||p_return_line_id);
580 END IF;
581
582 -- Check whether the given return line is valid or not.
583 OPEN chk_return_line(p_return_line_id);
584 FETCH chk_return_line INTO l_dummy;
585 IF (chk_return_line%NOTFOUND) THEN
586 -- The given return line is invalid. Return FND_API.G_FALSE.
587 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
588 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
589 'Return line: '||p_return_line_id||' is invalid. Returnig False.');
590 END IF;
591
592 CLOSE chk_return_line;
593 RETURN FND_API.G_FALSE;
594 END IF;
595 CLOSE chk_return_line;
596
597 -- Check whether the shipment is booked or not.
598 OPEN chk_shipment_booked(p_return_line_id);
599 FETCH chk_shipment_booked INTO l_oe_header_id;
600 IF (chk_shipment_booked%NOTFOUND) THEN
601 -- The shipment is not booked. Return FND_API.G_FALSE.
602 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
603 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
604 'Shipment order: '||l_oe_header_id||' is not booked. Returnig False.');
605 END IF;
606
607 CLOSE chk_shipment_booked;
608 RETURN FND_API.G_FALSE;
609 END IF;
610 CLOSE chk_shipment_booked;
611
612
613 -- Get the ship line id of the OSP order lines, that correspond to the given return line.
614 OPEN get_osp_ship_line_id(p_return_line_id);
615 FETCH get_osp_ship_line_id INTO l_oe_ship_line_id;
616 IF (get_osp_ship_line_id%FOUND) THEN
617 -- If the ship line id is NULL, return FND_API.G_FALSE.
618 IF (l_oe_ship_line_id IS NULL) THEN
619 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
620 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
621 'Ship line id for the return line: '||p_return_line_id||' is NULL. Returning False.');
622 END IF;
623
624 CLOSE get_osp_ship_line_id;
625 RETURN FND_API.G_FALSE;
626 ELSE
627 -- Get the ordered and shipped quantities of the ship line.
628 OPEN get_oe_quantities(l_oe_ship_line_id);
629 FETCH get_oe_quantities INTO l_ship_line_qty_rec;
630 CLOSE get_oe_quantities;
631
632 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
633 FND_LOG.string(FND_LOG.level_statement, l_debug_key, 'l_oe_ship_line_id: '||l_oe_ship_line_id);
634 FND_LOG.string(FND_LOG.level_statement, l_debug_key, 'l_ship_line_qty_rec.shipped_quantity: '||l_ship_line_qty_rec .shipped_quantity);
635 END IF;
636
637 -- Shipment should have been done for any receipt to take place. For this, check the shipped quantity.
638 -- If the shipped quantity is NULL or zero, it means shipment hasn't been done yet. Return FND_API.G_FALSE.
639 IF (l_ship_line_qty_rec.shipped_quantity IS NULL OR l_ship_line_qty_rec.shipped_quantity = 0) THEN
640 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
641 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
642 'Shipment for the return line: '||p_return_line_id||' has not been done yet. Returning False.');
643 END IF;
644
645 CLOSE get_osp_ship_line_id;
646 RETURN FND_API.G_FALSE;
647 END IF;
648 END IF;
649 END IF;
650 CLOSE get_osp_ship_line_id;
651
652 -- Get the ordered and shipped quantities of the given return line.
653 OPEN get_oe_quantities(p_return_line_id);
654 FETCH get_oe_quantities INTO l_return_line_qty_rec;
655 CLOSE get_oe_quantities;
656
657 -- Check for the ordered and shipped quantities of the given return line. If the shipped quantity is not less than the
658 -- ordered quantity, then it means there is no quantity left to be returned. Return FND_API.G_FALSE.
659 IF NOT (nvl(l_return_line_qty_rec.shipped_quantity,0) < l_return_line_qty_rec.ordered_quantity) THEN
660 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
661 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
662 'Shipped quantity is not less than the ordered quantity for the return line: '||p_return_line_id||'.'||
663 ' Returning False.');
664 END IF;
665
666 RETURN FND_API.G_FALSE;
667 END IF;
668
669 -- If all the checks have been validated, return FND_API.G_TRUE.
670 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
671 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
672 'All checks validated. Returnig True.');
673 END IF;
674
675 RETURN FND_API.G_TRUE;
676 END Can_Receive_Against_RMA;
677
678 --salogan added the following for supplier warranty starts
679 -- Start of Comments --
680 -- Function name : Can_Nav_to_Warranty_WB
681 -- Type : Public
682 -- Functionality : Function to determine if a the user can navigate to warranty workbench against a given RMA line.
683 -- It returns 'NoWorkbench' and 'EditWorkbench' based on different scenarios.
684 -- Pre-reqs :
685 --
686 -- Parameters:
687 --
688 -- p_return_line_id IN NUMBER RMA Line Id
689 -- p_osp_order_id IN NUMBER OSP Line Id
690 --
691 -- Version:
692 --
693 -- Initial Version 1.0
694 --
695 -- End of Comments --
696
697 FUNCTION Can_Nav_to_Warranty_WB (
698 p_return_line_id IN NUMBER,
699 p_osp_order_id IN NUMBER
700 )
701 RETURN VARCHAR2 IS
702
703 -- Cursor to check whether the given return line is valid or not.
704 CURSOR chk_return_line (c_oe_line_id NUMBER) IS
705 SELECT 'X'
706 FROM OE_ORDER_LINES_ALL
707 WHERE line_id = c_oe_line_id
708 AND line_type_id = FND_PROFILE.VALUE('AHL_OSP_OE_RETURN_ID');
709
710 -- Cursor to check whether the shipment is booked or not.
711 CURSOR chk_shipment_booked (c_oe_line_id NUMBER) IS
712 SELECT OHA.header_id
713 FROM OE_ORDER_LINES_ALL OLA, OE_ORDER_HEADERS_ALL OHA
714 WHERE OLA.line_id = c_oe_line_id
715 AND OHA.header_id = OLA.header_id
716 AND OHA.booked_flag = 'Y';
717
718 -- Cursor to get the ship line id of the OSP order lines, that correspond to the given return line.
719 CURSOR get_osp_ship_line_id (c_oe_return_line_id NUMBER) IS
720 SELECT oe_ship_line_id
721 FROM AHL_OSP_ORDER_LINES
722 WHERE oe_return_line_id = c_oe_return_line_id
723 AND ROWNUM = 1;
724
725 -- Cursor to get the ordered and shipped quantities of the given return line.
726 CURSOR get_oe_quantities (c_oe_line_id NUMBER) IS
727 SELECT ordered_quantity, shipped_quantity
728 FROM OE_ORDER_LINES_ALL
729 WHERE line_id = c_oe_line_id;
730
731 -- Cursor to get inventory item details
732 CURSOR get_inv_details (c_oe_line_id NUMBER) IS
733 SELECT INVENTORY_ITEM_ID, SHIP_FROM_ORG_ID
734 FROM OE_ORDER_LINES_ALL
735 WHERE line_id = c_oe_line_id;
736
737 -- Cursor to check whether the given vendor is warranty enabled
738 CURSOR is_warranty_vendor (c_osp_order_id NUMBER) IS
739 SELECT AVCV.Warranty_flag
740 FROM ahl_osp_orders_b OSP, AHL_VENDOR_CERTIFICATIONS_V AVCV
741 WHERE OSP.VENDOR_ID = AVCV.VENDOR_ID
742 AND OSP.OSP_ORDER_ID = c_osp_order_id;
743
744 -- Cursor to check whether the item is trackable
745 CURSOR c_is_trackable_item(c_inventory_item_id NUMBER, c_inventory_org_id NUMBER) IS
746 SELECT COMMS_NL_TRACKABLE_FLAG
747 FROM MTL_SYSTEM_ITEMS_KFV
748 WHERE INVENTORY_ITEM_ID = c_inventory_item_id
749 AND ORGANIZATION_ID = c_inventory_org_id;
750
751 --
752 l_api_name CONSTANT VARCHAR2(30) := 'Can_Nav_to_Warranty_WB';
753 l_debug_key CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
754
755 l_oe_header_id OE_ORDER_HEADERS_ALL.header_id%TYPE;
756 l_oe_ship_line_id AHL_OSP_ORDER_LINES.oe_ship_line_id%TYPE;
757 l_ship_line_qty_rec get_oe_quantities%ROWTYPE;
758 --l_return_line_qty_rec get_oe_quantities%ROWTYPE;
759 l_dummy VARCHAR2(1);
760 L_NO_WORKBENCH VARCHAR2(15) := 'NoWorkbench';
761 L_EDIT_WORKBENCH VARCHAR2(15) := 'EditWorkbench';
762 l_inventory_item_id NUMBER;
763 l_organization_id NUMBER;
764 --
765
766 BEGIN
767 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
768 FND_LOG.string(FND_LOG.level_procedure, l_debug_key||'.begin', 'API called with p_return_line_id: '||p_return_line_id);
769 END IF;
770
771 -- Check whether the given return line is valid or not.
772 OPEN chk_return_line(p_return_line_id);
773 FETCH chk_return_line INTO l_dummy;
774 IF (chk_return_line%NOTFOUND) THEN
775 -- The given return line is invalid. Return L_NO_WORKBENCH.
776 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
777 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
778 'Return line: '||p_return_line_id||' is invalid. Returning L_NO_WORKBENCH.');
779 END IF;
780
781 CLOSE chk_return_line;
782 RETURN L_NO_WORKBENCH;
783 END IF;
784 CLOSE chk_return_line;
785
786 OPEN get_inv_details(p_return_line_id);
787 FETCH get_inv_details into l_inventory_item_id, l_organization_id;
788 CLOSE get_inv_details;
789
790 OPEN c_is_trackable_item(l_inventory_item_id, l_organization_id);
791 FETCH c_is_trackable_item into l_dummy; -- check whether the inv item is trackable
792 IF NOT (c_is_trackable_item%FOUND AND l_dummy = 'Y') THEN -- If the warranty template id at the MR level is not found
793 -- The given inbentory item is not trackable. Return no warranty navigation.
794 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
795 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
796 'Item : '||l_inventory_item_id||' is not trackable. Returning L_NO_WORKBENCH.');
797 END IF;
798
799 CLOSE c_is_trackable_item;
800 RETURN L_NO_WORKBENCH;
801 END IF;
802 CLOSE c_is_trackable_item;
803
804 -- Check whether the shipment is booked or not.
805 OPEN chk_shipment_booked(p_return_line_id);
806 FETCH chk_shipment_booked INTO l_oe_header_id;
807 IF (chk_shipment_booked%NOTFOUND) THEN
808 -- The shipment is not booked. Return L_NO_WORKBENCH.
809 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
810 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
811 'Shipment order: '||l_oe_header_id||' is not booked. Returning L_NO_WORKBENCH.');
812 END IF;
813
814 CLOSE chk_shipment_booked;
815 RETURN L_NO_WORKBENCH;
816 END IF;
817 CLOSE chk_shipment_booked;
818
819
820 -- Get the ship line id of the OSP order lines, that correspond to the given return line.
821 OPEN get_osp_ship_line_id(p_return_line_id);
822 FETCH get_osp_ship_line_id INTO l_oe_ship_line_id;
823 IF (get_osp_ship_line_id%FOUND) THEN
824 -- If the ship line id is NULL, return L_NO_WORKBENCH.
825 IF (l_oe_ship_line_id IS NULL) THEN
826 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
827 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
828 'Ship line id for the return line: '||p_return_line_id||' is NULL. Returning L_NO_WORKBENCH.');
829 END IF;
830
831 CLOSE get_osp_ship_line_id;
832 RETURN L_NO_WORKBENCH;
833 ELSE
834 -- Get the ordered and shipped quantities of the ship line.
835 OPEN get_oe_quantities(l_oe_ship_line_id);
836 FETCH get_oe_quantities INTO l_ship_line_qty_rec;
837 CLOSE get_oe_quantities;
838
839 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
840 FND_LOG.string(FND_LOG.level_statement, l_debug_key, 'l_oe_ship_line_id: '||l_oe_ship_line_id);
841 FND_LOG.string(FND_LOG.level_statement, l_debug_key, 'l_ship_line_qty_rec.shipped_quantity: '||l_ship_line_qty_rec .shipped_quantity);
842 END IF;
843
844 -- Shipment should have been done for any receipt to take place. For this, check the shipped quantity.
845 -- If the shipped quantity is NULL or zero, it means shipment hasn't been done yet. Return L_NO_WORKBENCH.
846 IF (l_ship_line_qty_rec.shipped_quantity IS NULL OR l_ship_line_qty_rec.shipped_quantity = 0) THEN
847 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
848 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
849 'Shipment for the return line: '||p_return_line_id||' has not been done yet. Returning L_NO_WORKBENCH.');
850 END IF;
851
852 CLOSE get_osp_ship_line_id;
853 RETURN L_NO_WORKBENCH;
854 END IF;
855 END IF;
856 END IF;
857 CLOSE get_osp_ship_line_id;
858
859 /* Commented the following code in order to enable Warranty workbench icon even after an Item is received.
860 Since the instance received can be a different one and can have different set of Warranty
861 Just commented the code as this piece might be of use later.
862 -- Get the ordered and shipped quantities of the given return line.
863 OPEN get_oe_quantities(p_return_line_id);
864 FETCH get_oe_quantities INTO l_return_line_qty_rec;
865 CLOSE get_oe_quantities;
866 -- Check for the ordered and shipped quantities of the given return line.
867 -- If the shipped quantity is greater than or equal to the ordered quantity,
868 -- then it means the item is received. Return L_NO_WORKBENCH.
869 IF (nvl(l_return_line_qty_rec.shipped_quantity,0) >= l_return_line_qty_rec.ordered_quantity) THEN
870 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
871 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
872 'Shipped quantity is less than the ordered quantity for the return line: '||p_return_line_id||'.'||
873 ' Returning L_NO_WORKBENCH.');
874 END IF;
875 RETURN L_NO_WORKBENCH;
876 END IF;*/
877
878 -- if the vendor is warranty enabled then he can navigate to edit warranty workbench
879 OPEN is_warranty_vendor(p_osp_order_id);
880 FETCH is_warranty_vendor into l_dummy;
881 IF(is_warranty_vendor%FOUND AND l_dummy = 'Y') THEN
882 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
883 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
884 'All checks validated. Returnig L_EDIT_WORKBENCH.');
885 END IF;
886 RETURN L_EDIT_WORKBENCH;
887 END IF;
888
889 -- If all the checks have been validated, return L_NO_WORKBENCH.
890 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
891 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
892 'All checks validated. Returnig L_NO_WORKBENCH.');
893 END IF;
894
895 RETURN L_NO_WORKBENCH;
896 END Can_Nav_to_Warranty_WB;
897 --salogan added the following for supplier warranty ends
898
899
900 -- Start of Comments --
901 -- Procedure name : Receive_Against_PO
902 -- Type : Public
903 -- Functionality : Procedure to receive against PO lines given an RMA line.
904 -- Pre-reqs :
905 --
906 -- Parameters:
907 --
908 -- Standard IN Parameters:
909 -- p_api_version IN NUMBER Required
910 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
911 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
912 -- p_validation_level IN NUMBER Required
913 -- p_module_type IN VARCHAR2 Default NULL
914 --
915 -- Standard OUT Parameters:
916 -- x_return_status OUT VARCHAR2 Required
917 -- x_msg_count OUT NUMBER Required
918 -- x_msg_data OUT VARCHAR2 Required
919 --
920 -- Receive_Against_PO Parameters:
921 -- p_return_line_id IN NUMBER RMA Line Id
922 -- x_request_id OUT NUMBER Request id of the call request of the concurrent program, i.e. 'RVCTP'.
923 --
924 -- Version:
925 --
926 -- Initial Version 1.0
927 --
928 -- End of Comments --
929
930 PROCEDURE Receive_Against_PO (
931 p_api_version IN NUMBER,
932 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
933 p_commit IN VARCHAR2 := FND_API.G_FALSE,
934 p_validation_level IN NUMBER,
935 p_module_type IN VARCHAR2 := NULL,
936 x_return_status OUT NOCOPY VARCHAR2,
937 x_msg_count OUT NOCOPY NUMBER,
938 x_msg_data OUT NOCOPY VARCHAR2,
939 p_return_line_id IN NUMBER,
940 x_request_id OUT NOCOPY NUMBER
941 ) IS
942
943 -- Cursor to get the PO header id for the OSP order corresponding to the given return line id.
944 -- Only those OSP orders need to be considered, which have the status as PO_CREATED or REQ_CREATED.
945 CURSOR get_po_header_id (c_oe_return_line_id NUMBER) IS
946 SELECT AOB.po_header_id,
947 AOB.po_req_header_id,
948 AOB.osp_order_number
949 FROM AHL_OSP_ORDER_LINES AOL, AHL_OSP_ORDERS_B AOB
950 WHERE AOL.oe_return_line_id = c_oe_return_line_id
951 AND AOL.osp_order_id = AOB.osp_order_id
952 AND AOB.status_code IN ('PO_CREATED', 'REQ_CREATED')
953 AND ROWNUM = 1;
954
955 -- Cursor to get the PO details.
956 CURSOR get_po_header_details1 (c_po_header_id NUMBER) IS
957 SELECT vendor_id, vendor_site_id
958 FROM PO_HEADERS_ALL
959 WHERE NVL(approved_flag, 'N') = 'Y'
960 AND po_header_id = c_po_header_id;
961
962 -- Cursor to get the PO details. This will be used only for those PO headers, which are derived from the Requisition.
963 CURSOR get_po_header_details2 (c_po_line_id NUMBER) IS
964 SELECT POH.vendor_id, POH.vendor_site_id
965 FROM PO_HEADERS_ALL POH, PO_LINES_ALL POL
966 WHERE POL.po_line_id = c_po_line_id
967 AND POH.po_header_id = POL.po_header_id
968 AND ROWNUM = 1;
969
970 -- Cursor to check whether the given Requisition is approved or not.
971 CURSOR chk_po_req_approved (c_po_req_header_id NUMBER) IS
972 SELECT 'X'
973 FROM PO_REQUISITION_HEADERS_V
974 WHERE NVL(authorization_status, 'X') = 'APPROVED'
975 AND requisition_header_id = c_po_req_header_id;
976
977 -- Cursor to get the PO line quantity details for all the OSP order lines, corresponding to the given return line id.
978 -- Only those OSP order lines need to be considered, which doesn't have the status as PO_DELETED or PO_CANCELLED, or
979 -- REQ_DELETED or REQ_CANCELLED.
980 CURSOR get_po_line_quantity1 (c_oe_return_line_id NUMBER) IS
981 SELECT AOL.po_line_id,
982 AOL.po_req_line_id,
983 POL.quantity,
984 (SELECT SUM(PLL.quantity_received)
985 FROM PO_LINE_LOCATIONS_ALL PLL
986 WHERE PLL.po_line_id = POL.po_line_id) quantity_received
987 FROM AHL_OSP_ORDER_LINES AOL, PO_LINES_ALL POL
988 WHERE AOL.oe_return_line_id = c_oe_return_line_id
989 AND POL.po_line_id(+) = AOL.po_line_id
990 AND NVL(AOL.status_code, 'X') <> 'PO_DELETED'
991 AND NVL(AOL.status_code, 'X') <> 'PO_CANCELLED'
992 AND NVL(AOL.status_code, 'X') <> 'REQ_DELETED'
993 AND NVL(AOL.status_code, 'X') <> 'REQ_CANCELLED';
994
995 -- Cursor to get the PO line quantity details for the given PO line id.
996 -- This will be used for those PO lines, which have been derived from Requisition lines.
997 CURSOR get_po_line_quantity2 (c_po_line_id NUMBER) IS
998 SELECT POL.quantity,
999 (SELECT SUM(PLL.quantity_received)
1000 FROM PO_LINE_LOCATIONS_ALL PLL
1001 WHERE PLL.po_line_id = POL.po_line_id) quantity_received
1002 FROM PO_LINES_ALL POL, PO_LINE_LOCATIONS_ALL PLL
1003 WHERE POL.po_line_id = c_po_line_id;
1004
1005 -- Cursor to get the PO line for the given Requisition line id. Only approved POs will be considered.
1006 CURSOR get_po_line (c_po_req_line_id NUMBER) IS
1007 SELECT PLL.po_line_id
1008 FROM PO_LINE_LOCATIONS_ALL PLL, PO_REQUISITION_LINES REQ,
1009 PO_HEADERS_ALL POH
1010 WHERE REQ.requisition_line_id = c_po_req_line_id
1011 AND PLL.line_location_id = REQ.line_location_id
1012 AND PLL.po_header_id = POH.po_header_id
1013 AND NVL(POH.approved_flag, 'N') = 'Y';
1014
1015 -- Cursor to check whether a pending receipt transaction exists for a given PO line id.
1016 CURSOR chk_pending_transaction (c_po_line_id NUMBER) IS
1017 SELECT 'X'
1018 FROM RCV_TRANSACTIONS_INTERFACE
1019 WHERE po_line_id = c_po_line_id
1020 AND processing_status_code = 'PENDING';
1021
1022 -- Cursor to get the 'ship to org id' for a given PO line id.
1023 CURSOR get_ship_to_org_id (c_po_line_id NUMBER) IS
1024 SELECT ship_to_organization_id
1025 FROM PO_LINE_LOCATIONS_ALL
1026 WHERE po_line_id = c_po_line_id;
1027
1028 -- Cursor to get the 'ship to location id' for a given PO line id.
1029 CURSOR get_ship_to_loc_id (c_po_line_id NUMBER) IS
1030 SELECT ship_to_location_id
1031 FROM PO_LINE_LOCATIONS_ALL
1032 WHERE po_line_id = c_po_line_id;
1033
1034 --
1035 l_api_version CONSTANT NUMBER := 1.0;
1036 l_api_name CONSTANT VARCHAR2(30) := 'Receive_Against_PO';
1037 l_debug_key CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1038
1039 l_osp_order_number AHL_OSP_ORDERS_B.osp_order_number%TYPE;
1040 l_po_header_id AHL_OSP_ORDERS_B.po_header_id%TYPE;
1041 l_po_req_header_id AHL_OSP_ORDERS_B.po_req_header_id%TYPE;
1042 l_po_vendor_id AHL_OSP_ORDERS_B.vendor_id%TYPE;
1043 l_po_vendor_site_id AHL_OSP_ORDERS_B.vendor_site_id%TYPE;
1044 l_po_line_id AHL_OSP_ORDER_LINES.po_line_id%TYPE;
1045 l_po_req_line_id AHL_OSP_ORDER_LINES.po_req_line_id%TYPE;
1046 l_ship_to_org_id PO_LINE_LOCATIONS_ALL.ship_to_organization_id%TYPE;
1047 l_ship_to_loc_id PO_LINE_LOCATIONS_ALL.ship_to_location_id%TYPE;
1048 l_po_line_qty NUMBER;
1049 l_po_line_tot_qty NUMBER;
1050 l_po_diff_qty NUMBER;
1051 l_req_id NUMBER;
1052 l_dummy VARCHAR2(1);
1053 l_temp NUMBER;
1054 l_hdr_inserted BOOLEAN := FALSE;
1055 l_po_lines_exist BOOLEAN := FALSE;
1056
1057 TYPE PO_LINE_TBL_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1058 l_po_line_tbl PO_LINE_TBL_TYPE;
1059 l_merged_req_line BOOLEAN := FALSE;
1060 --
1061
1062 BEGIN
1063 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1064 FND_LOG.string(FND_LOG.level_procedure, l_debug_key||'.begin', 'Start of the API. p_return_line_id: '||p_return_line_id);
1065 END IF;
1066
1067 -- Standard start of API savepoint.
1068 SAVEPOINT Receive_Against_PO_Pvt;
1069
1070 -- Initialize Procedure return status to success.
1071 x_return_status := FND_API.G_RET_STS_SUCCESS;
1072
1073 -- Standard call to check for call compatibility.
1074 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1075 l_api_name, G_PKG_NAME) THEN
1076 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1077 END IF;
1078
1079 -- Initialize message list if p_init_msg_list is set to TRUE.
1080 IF FND_API.To_Boolean(p_init_msg_list) THEN
1081 FND_MSG_PUB.Initialize;
1082 END IF;
1083
1084 -- Get the PO header id for the OSP order corresponding to the given return line id.
1085 OPEN get_po_header_id(p_return_line_id);
1086 FETCH get_po_header_id INTO l_po_header_id, l_po_req_header_id, l_osp_order_number;
1087 IF (get_po_header_id%FOUND) THEN
1088 IF (l_po_header_id IS NULL) THEN
1089 -- As the PO header id is NULL, check for the requisition header id.
1090 IF (l_po_req_header_id IS NULL) THEN
1091 -- Even the requisition header id is NULL. Raise an exception.
1092 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1093 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
1094 'Both PO and requisition headers are NULL for the OSP order: '||l_osp_order_number||
1095 '. Raising exception.');
1096 END IF;
1097
1098 CLOSE get_po_header_id;
1099 FND_MESSAGE.set_name('AHL', 'AHL_OSP_PO_REQ_NULL'); -- Receipt cannot be done as neither the purchase order nor the requisition exist for this OSP order.
1100 FND_MSG_PUB.ADD;
1101 RAISE FND_API.G_EXC_ERROR;
1102 ELSE
1103 -- Check whether the Requisition is approved or not.
1104 -- If not approved, raise an exception.
1105 OPEN chk_po_req_approved(l_po_req_header_id);
1106 FETCH chk_po_req_approved INTO l_dummy;
1107 IF (chk_po_req_approved%NOTFOUND) THEN
1108 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1109 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
1110 'Requisition: '||l_po_header_id||' is not approved. Raising exception.');
1111 END IF;
1112
1113 CLOSE chk_po_req_approved;
1114 CLOSE get_po_header_id;
1115 FND_MESSAGE.set_name('AHL', 'AHL_OSP_REQ_NOT_APRVD'); -- Receipt cannot be done as the requisition is not approved.
1116 FND_MSG_PUB.ADD;
1117 RAISE FND_API.G_EXC_ERROR;
1118 END IF;
1119 CLOSE chk_po_req_approved;
1120 END IF; -- if l_po_req_header_id IS NULL
1121 ELSE
1122 -- Get the Purchase order details. If not approved, raise an exception.
1123 OPEN get_po_header_details1(l_po_header_id);
1124 FETCH get_po_header_details1 INTO l_po_vendor_id, l_po_vendor_site_id;
1125 IF (get_po_header_details1%NOTFOUND) THEN
1126 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1127 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
1128 'Purchase order: '||l_po_header_id||' is not approved. Raising exception.');
1129 END IF;
1130
1131 CLOSE get_po_header_details1;
1132 CLOSE get_po_header_id;
1133 FND_MESSAGE.set_name('AHL', 'AHL_OSP_PO_NOT_APRVD'); -- Receipt cannot be done as the purchase order is not approved.
1134 FND_MSG_PUB.ADD;
1135 RAISE FND_API.G_EXC_ERROR;
1136 END IF;
1137 CLOSE get_po_header_details1;
1138 END IF; -- if l_po_header_id IS NULL
1139 ELSE
1140 -- This means that the OSP order is not in the PO_CREATED or REQ_CREATED status. Raise an exception.
1141 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1142 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
1143 'OSP order: '||l_osp_order_number||' is not in status PO_CREATED or REQ_CREATED. Raising exception.');
1144 END IF;
1145
1146 CLOSE get_po_header_id;
1147 FND_MESSAGE.set_name('AHL', 'AHL_OSP_ORDER_INVALID'); -- The status of the OSP order (ORDER_NUM) is not valid for receiving.
1148 FND_MESSAGE.set_token('ORDER_NUM', l_osp_order_number);
1149 FND_MSG_PUB.ADD;
1150 RAISE FND_API.G_EXC_ERROR;
1151 END IF; -- if get_po_header_id%FOUND
1152 CLOSE get_po_header_id;
1153
1154 -- Get the PO line quantity and the total received quantity.
1155 OPEN get_po_line_quantity1(p_return_line_id);
1156 LOOP
1157 FETCH get_po_line_quantity1 INTO l_po_line_id, l_po_req_line_id, l_po_line_qty, l_po_line_tot_qty;
1158 EXIT WHEN get_po_line_quantity1%NOTFOUND;
1159
1160 -- If PO line is NULL, get it from the Requisition line.
1161 l_merged_req_line := FALSE; -- Set merged Requisition line flag as FALSE at the start of the loop
1162 IF (l_po_line_id IS NULL) THEN
1163 OPEN get_po_line(l_po_req_line_id);
1164 FETCH get_po_line INTO l_po_line_id;
1165 IF (get_po_line%FOUND) THEN
1166 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1167 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
1168 'PO line id was NULL. Got it as '||l_po_line_id||' from the Requisition line: '||l_po_req_line_id);
1169 END IF;
1170
1171 -- Check for merged Requisition lines, i.e. whether this PO line has already been checked or not.
1172 IF (l_po_line_tbl.EXISTS(l_po_line_id)) THEN
1173 l_merged_req_line := TRUE;
1174 ELSE
1175 l_merged_req_line := FALSE;
1176 l_po_line_tbl(l_po_line_id) := l_po_line_id;
1177
1178 -- Get the PO line quantity and the total received quantity.
1179 OPEN get_po_line_quantity2(l_po_line_id);
1180 FETCH get_po_line_quantity2 INTO l_po_line_qty, l_po_line_tot_qty;
1181 CLOSE get_po_line_quantity2;
1182
1183 -- Get the Vendor details for the derived PO line.
1184 OPEN get_po_header_details2(l_po_line_id);
1185 FETCH get_po_header_details2 INTO l_po_vendor_id, l_po_vendor_site_id;
1186 CLOSE get_po_header_details2;
1187 END IF;
1188 ELSE
1189 -- Either PO is not created or its not approved. Raise exception.
1190 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1191 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
1192 'Either PO is not created or its not approved. Raising Exception');
1193 END IF;
1194
1195 CLOSE get_po_line;
1196 CLOSE get_po_line_quantity1;
1197 FND_MESSAGE.set_name('AHL', 'AHL_OSP_PO_NULL_OR_INVLD'); -- Either the purchase order is not created or it is not approved.
1198 FND_MSG_PUB.ADD;
1199 RAISE FND_API.G_EXC_ERROR;
1200 END IF; -- if get_po_line%FOUND
1201 CLOSE get_po_line;
1202 END IF;
1203
1204 -- Do the rest of the processing only for those PO lines for which the total received quantity is less than the line quantity.
1205 -- Do not process merged Requisition lines, i.e. PO lines already processed.
1206 IF NOT l_merged_req_line AND (l_po_line_tot_qty < l_po_line_qty) THEN
1207 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1208 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
1209 'Processing for PO line id: '||l_po_line_id);
1210 END IF;
1211
1212 -- Set the l_po_lines_exist flag.
1213 IF (NOT l_po_lines_exist) THEN
1214 l_po_lines_exist := TRUE;
1215 END IF;
1216
1217 -- Check whether a pending receipt transaction exists for this PO line id.
1218 OPEN chk_pending_transaction(l_po_line_id);
1219 FETCH chk_pending_transaction INTO l_dummy;
1220 IF (chk_pending_transaction%FOUND) THEN
1221 -- Pending receipt transaction exists for this PO line id. Raise an exception.
1222 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1223 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
1224 'Pending transactions exist for PO line id: '||l_po_line_id);
1225 END IF;
1226
1227 CLOSE chk_pending_transaction;
1228 CLOSE get_po_line_quantity1;
1229 FND_MESSAGE.set_name('AHL', 'AHL_OSP_PO_PENDING_TRNSCTN'); -- Some of the purchase order lines for this return line have pending transactions.
1230 FND_MSG_PUB.ADD;
1231 RAISE FND_API.G_EXC_ERROR;
1232 END IF;
1233 CLOSE chk_pending_transaction;
1234
1235 -- Insert a record in RCV_HEADERS_INTERFACE table as a header of these PO lines.
1236 IF (NOT l_hdr_inserted) THEN
1237 -- Set the l_hdr_inserted flag.
1238 l_hdr_inserted := TRUE;
1239
1240 -- Get the 'ship to org id' for this PO line id.
1241 OPEN get_ship_to_org_id(l_po_line_id);
1242 FETCH get_ship_to_org_id INTO l_ship_to_org_id;
1243 CLOSE get_ship_to_org_id;
1244
1245 INSERT INTO RCV_HEADERS_INTERFACE(
1246 HEADER_INTERFACE_ID,
1247 GROUP_ID,
1248 PROCESSING_STATUS_CODE,
1249 RECEIPT_SOURCE_CODE,
1250 TRANSACTION_TYPE,
1251 LAST_UPDATE_DATE,
1252 LAST_UPDATED_BY,
1253 LAST_UPDATE_LOGIN,
1254 CREATION_DATE,
1255 CREATED_BY,
1256 VENDOR_ID,
1257 VENDOR_SITE_ID,
1258 SHIP_TO_ORGANIZATION_ID
1259 ) VALUES (
1260 PO.RCV_HEADERS_INTERFACE_S.NEXTVAL,
1261 PO.RCV_INTERFACE_GROUPS_S.NEXTVAL,
1262 'PENDING',
1263 'VENDOR',
1264 'NEW',
1265 SYSDATE,
1266 FND_GLOBAL.USER_ID,
1267 FND_GLOBAL.LOGIN_ID,
1268 SYSDATE,
1269 FND_GLOBAL.USER_ID,
1270 l_po_vendor_id,
1271 l_po_vendor_site_id,
1272 l_ship_to_org_id
1273 );
1274
1275 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1276 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
1277 'Transaction header inserted.');
1278 END IF;
1279 END IF; -- l_hdr_inserted check
1280
1281 -- Get the 'ship to location id' for this PO line id.
1282 OPEN get_ship_to_loc_id(l_po_line_id);
1283 FETCH get_ship_to_loc_id INTO l_ship_to_loc_id;
1284 CLOSE get_ship_to_loc_id;
1285
1286 -- Get the difference between the PO line quantity and the total received quantity.
1287 l_po_diff_qty := l_po_line_qty - l_po_line_tot_qty;
1288
1289 -- Insert a record in RCV_TRANSACTIONS_INTERFACE table corresponsing to this PO line.
1290 INSERT INTO RCV_TRANSACTIONS_INTERFACE(
1291 INTERFACE_TRANSACTION_ID,
1292 HEADER_INTERFACE_ID,
1293 GROUP_ID,
1294 LAST_UPDATE_DATE,
1295 LAST_UPDATED_BY,
1296 LAST_UPDATE_LOGIN,
1297 CREATION_DATE,
1298 CREATED_BY,
1299 TRANSACTION_TYPE,
1300 TRANSACTION_DATE,
1301 PROCESSING_STATUS_CODE,
1302 PROCESSING_MODE_CODE,
1303 TRANSACTION_STATUS_CODE,
1304 QUANTITY,
1305 AUTO_TRANSACT_CODE,
1306 RECEIPT_SOURCE_CODE,
1307 SOURCE_DOCUMENT_CODE,
1308 VALIDATION_FLAG,
1309 PO_HEADER_ID,
1310 PO_LINE_ID,
1311 SHIP_TO_LOCATION_ID
1312 ) VALUES (
1313 PO.RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL,
1314 PO.RCV_HEADERS_INTERFACE_S.CURRVAL,
1315 PO.RCV_INTERFACE_GROUPS_S.CURRVAL,
1316 SYSDATE,
1317 FND_GLOBAL.USER_ID,
1318 FND_GLOBAL.LOGIN_ID,
1319 SYSDATE,
1320 FND_GLOBAL.USER_ID,
1321 'RECEIVE',
1322 SYSDATE,
1323 'PENDING',
1324 'BATCH',
1325 'PENDING',
1326 l_po_diff_qty,
1327 'RECEIVE',
1328 'VENDOR',
1329 'PO',
1330 'Y',
1331 l_po_header_id,
1332 l_po_line_id,
1333 l_ship_to_loc_id
1334 );
1335
1336 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1337 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
1338 'Transaction record inserted for PO line id: '||l_po_line_id);
1339 END IF;
1340 END IF; -- (l_po_line_tot_qty < l_po_line_qty)
1341 END LOOP; -- PO lines loop
1342 CLOSE get_po_line_quantity1;
1343
1344 -- Check for the l_po_lines_exist flag. If not set, raise an exception.
1345 IF (NOT l_po_lines_exist) THEN
1346 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1347 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
1348 'No PO lines could be found to do a receipt against. Raising exception.');
1349 END IF;
1350
1351 FND_MESSAGE.set_name('AHL', 'AHL_OSP_ALL_PO_LINES_RCVD'); -- All the purchase order lines for this return line have been received.
1352 FND_MSG_PUB.ADD;
1353 RAISE FND_API.G_EXC_ERROR;
1354 END IF;
1355
1356 -- After the interface tables been populated above, submit request for calling the Concurrent Program 'RVCTP'.
1357 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1358 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
1359 'Submitting the request for calling the Concurrent Program RVCTP.');
1360 END IF;
1361
1362 -- Get the current value of the sequence PO.RCV_INTERFACE_GROUPS_S, required for submitting the request.
1363 SELECT PO.RCV_INTERFACE_GROUPS_S.CURRVAL INTO l_temp FROM DUAL;
1364
1365 l_req_id := FND_REQUEST.SUBMIT_REQUEST(
1366 application => 'PO',
1367 program => 'RVCTP',
1368 --Modified by mpothuku on 04-Mar-2007 for the Bug 6862891
1369 argument1 => 'BATCH',
1370 argument2 => l_temp,
1371 argument3 => MO_GLOBAL.get_current_org_id()
1372 );
1373
1374 IF (l_req_id = 0) THEN
1375 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1376 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
1377 'Concurrent request failed.');
1378 END IF;
1379 ELSE
1380 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1381 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
1382 'Concurrent request successful. Request id: '||l_req_id);
1383 END IF;
1384 END IF;
1385
1386 -- Set the OUT parameter x_request_id with l_req_id.
1387 x_request_id := l_req_id;
1388
1389 -- Standard call to get message count and initialise the OUT parameters.
1390 FND_MSG_PUB.Count_And_Get
1391 ( p_count => x_msg_count,
1392 p_data => x_msg_data,
1393 p_encoded => FND_API.G_FALSE
1394 );
1395
1396 -- Commit work if p_commit is TRUE.
1397 IF FND_API.TO_BOOLEAN(p_commit) THEN
1398 COMMIT WORK;
1399 END IF;
1400
1401 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1402 FND_LOG.string(FND_LOG.level_procedure, l_debug_key||'.end', 'End of the API. x_request_id: '||x_request_id);
1403 END IF;
1404 EXCEPTION
1405 WHEN FND_API.G_EXC_ERROR THEN
1406 ROLLBACK TO Receive_Against_PO_Pvt;
1407 x_return_status := FND_API.G_RET_STS_ERROR;
1408 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1409 p_data => x_msg_data,
1410 p_encoded => FND_API.G_FALSE);
1411
1412 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1413 ROLLBACK TO Receive_Against_PO_Pvt;
1414 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1415 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1416 p_data => x_msg_data,
1417 p_encoded => FND_API.G_FALSE);
1418
1419 WHEN OTHERS THEN
1420 ROLLBACK TO Receive_Against_PO_Pvt;
1421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1422 FND_MSG_PUB.Add_Exc_Msg( p_pkg_name => G_PKG_NAME,
1423 p_procedure_name => l_api_name,
1424 p_error_text => SQLERRM);
1425
1426 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1427 p_data => x_msg_data,
1428 p_encoded => FND_API.G_FALSE);
1429 END Receive_Against_PO;
1430
1431
1432 -- Start of Comments --
1433 -- Procedure name : Receive_Against_RMA
1434 -- Type : Public
1435 -- Functionality : Procedure to receive against a given RMA line.
1436 -- Also does any Part Number/Serial Number change or an Exchange prior to doing the receipt.
1437 -- Pre-reqs :
1438 --
1439 -- Parameters:
1440 --
1441 -- Standard IN Parameters:
1442 -- p_api_version IN NUMBER Required
1443 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1444 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1445 -- p_validation_level IN NUMBER Required
1446 -- p_module_type IN VARCHAR2 Default NULL
1447 --
1448 -- Standard OUT Parameters:
1449 -- x_return_status OUT VARCHAR2 Required
1450 -- x_msg_count OUT NUMBER Required
1451 -- x_msg_data OUT VARCHAR2 Required
1452 --
1453 -- Receive_Against_PO Parameters:
1454 -- p_rma_receipt_rec IN RMA_Receipt_Rec_Type RMA receipt record
1455 -- x_request_id OUT NUMBER Request id of the call request of the concurrent program, i.e. 'RVCTP'.
1456 -- x_return_line_id OUT NUMBER New RMA Line id against which the receipt has been done.
1457 --
1458 -- Version:
1459 --
1460 -- Initial Version 1.0
1461 --
1462 -- End of Comments --
1463
1464 PROCEDURE Receive_Against_RMA (
1465 p_api_version IN NUMBER,
1466 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1467 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1468 p_validation_level IN NUMBER,
1469 p_module_type IN VARCHAR2 := NULL,
1470 x_return_status OUT NOCOPY VARCHAR2,
1471 x_msg_count OUT NOCOPY NUMBER,
1472 x_msg_data OUT NOCOPY VARCHAR2,
1473 p_rma_receipt_rec IN RMA_Receipt_Rec_Type,
1474 x_request_id OUT NOCOPY NUMBER,
1475 x_return_line_id OUT NOCOPY NUMBER
1476 ) IS
1477
1478 -- Cursor to get the sales order details.
1479 CURSOR oe_order_line_details_csr (c_oe_line_id NUMBER) IS
1480 SELECT inventory_item_id,
1481 sold_to_org_id customer_id,
1482 ship_to_org_id customer_site_id,
1483 ship_from_org_id organization_id,
1484 subinventory,
1485 header_id oe_order_header_id,
1486 line_id oe_order_line_id
1487 FROM oe_order_lines_all
1488 WHERE line_id = c_oe_line_id;
1489
1490 CURSOR inv_item_ctrls_csr (c_inv_item_id NUMBER,c_org_id NUMBER) IS
1491 SELECT serial_number_control_code,
1492 lot_control_code,
1493 nvl(comms_nl_trackable_flag,'N')
1494 FROM mtl_system_items_b
1495 WHERE inventory_item_id = c_inv_item_id
1496 AND organization_id = c_org_id;
1497
1498 CURSOR get_IB_subtrns_inst_dtls_csr (c_oe_line_id NUMBER) IS
1499 SELECT tld.instance_id,
1500 csi.inventory_item_id,
1501 csi.serial_number,
1502 csi.lot_number
1503 FROM csi_t_transaction_lines tl,
1504 csi_t_txn_line_details tld,
1505 csi_item_instances csi
1506 WHERE tl.source_transaction_id = c_oe_line_id
1507 AND tl.source_transaction_table = 'OE_ORDER_LINES_ALL'
1508 AND tl.transaction_line_id = tld.transaction_line_id
1509 AND tld.instance_id = csi.instance_id;
1510
1511 CURSOR get_osp_order_dtls(c_oe_line_id NUMBER) IS
1512 SELECT osp.osp_order_id,
1513 osp.order_type_code,
1514 oel.source_document_line_id osp_line_id,
1515 osp.object_version_number
1516 FROM oe_order_lines_all oel,
1517 ahl_osp_orders_b osp
1518 WHERE oel.header_id = osp.oe_header_id
1519 AND oel.line_id = c_oe_line_id;
1520
1521 CURSOR get_osp_order_line_dtls(c_osp_line_id NUMBER) IS
1522 SELECT inventory_item_id,
1523 serial_number,
1524 lot_number,
1525 exchange_instance_id
1526 FROM ahl_osp_order_lines
1527 WHERE osp_order_line_id = c_osp_line_id;
1528
1529 CURSOR ahl_oe_lot_serial_id_csr (c_oe_line_id NUMBER) IS
1530 SELECT lot_number,
1531 from_serial_number serial_number
1532 FROM oe_lot_serial_numbers
1533 WHERE line_id = c_oe_line_id;
1534
1535 CURSOR get_item_number(c_inv_item_id NUMBER) IS
1536 SELECT concatenated_segments
1537 FROM mtl_system_items_kfv
1538 WHERE inventory_item_id = c_inv_item_id
1539 AND rownum = 1;
1540
1541 CURSOR get_oe_line_id(c_osp_line_id NUMBER) IS
1542 SELECT oe_return_line_id
1543 FROM ahl_osp_order_lines
1544 WHERE osp_order_line_id = c_osp_line_id;
1545
1546 CURSOR get_instance_id(c_inv_item_id NUMBER, c_serial_number VARCHAR2) IS
1547 SELECT instance_id
1548 FROM csi_item_instances
1549 WHERE inventory_item_id = c_inv_item_id
1550 AND serial_number = c_serial_number;
1551
1552 -- Cursor to check whether a pending receipt transaction exists for a given order line id.
1553 CURSOR chk_pending_transaction (c_oe_line_id NUMBER) IS
1554 SELECT 'X'
1555 FROM RCV_TRANSACTIONS_INTERFACE
1556 WHERE oe_order_line_id = c_oe_line_id
1557 AND processing_status_code = 'PENDING';
1558
1559 CURSOR get_err_tranansaction_dtls(c_oe_line_id NUMBER) IS
1560 SELECT interface_transaction_id, header_interface_id
1561 FROM RCV_TRANSACTIONS_INTERFACE
1562 WHERE oe_order_line_id = c_oe_line_id
1563 AND processing_status_code = 'ERROR';
1564
1565 CURSOR get_same_phyitem_order_lines(c_osp_order_line_id IN NUMBER) IS
1566 SELECT matched_ol.osp_order_line_id
1567 FROM ahl_osp_order_lines matched_ol,
1568 ahl_osp_order_lines passed_ol
1569 WHERE passed_ol.osp_order_line_id = c_osp_order_line_id
1570 AND passed_ol.inventory_item_id = matched_ol.inventory_item_id
1571 AND passed_ol.serial_number = matched_ol.serial_number
1572 --modfication by jrotich 08/22/2012 in order to fix bug 14526440
1573 --modification restricts search to same osp order
1574 AND passed_ol.osp_order_id = matched_ol.osp_order_id;
1575
1576 --
1577 l_api_version CONSTANT NUMBER := 1.0;
1578 l_api_name CONSTANT VARCHAR2(30) := 'Receive_Against_RMA';
1579 l_debug_key CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1580 --
1581 l_part_num_change_flag BOOLEAN := FALSE;
1582 l_exchange_flag BOOLEAN := FALSE;
1583 l_return_line_id NUMBER;
1584 l_intf_hdr_id NUMBER;
1585 l_group_id NUMBER;
1586 l_intf_transaction_id NUMBER;
1587 l_mtl_transaction_id NUMBER;
1588 l_user_id NUMBER;
1589 l_login_id NUMBER;
1590 l_employee_id NUMBER;
1591 l_serial_control_code NUMBER;
1592 l_lot_control_code NUMBER;
1593 l_is_ib_trackable VARCHAR2(1);
1594 l_IB_subtrns_inst_rec get_IB_subtrns_inst_dtls_csr%ROWTYPE;
1595 l_osp_order_id NUMBER;
1596 l_osp_order_type VARCHAR2(30);
1597 l_trans_serial_number mtl_serial_numbers.serial_number%TYPE;
1598 l_trans_lot_number mtl_lot_numbers.lot_number%TYPE;
1599 l_osp_line_id NUMBER;
1600 l_oe_order_line_rec oe_order_line_details_csr%ROWTYPE;
1601 l_oe_lot_serial_rec ahl_oe_lot_serial_id_csr%ROWTYPE;
1602 l_osp_order_line_rec get_osp_order_line_dtls%ROWTYPE;
1603 l_curr_org_id NUMBER;
1604 l_serialnum_change_rec AHL_OSP_SHIPMENT_PUB.Sernum_Change_Rec_Type;
1605 l_new_item_number VARCHAR2(40);
1606 l_new_oe_line_id NUMBER;
1607 l_osp_ord_obj_ver NUMBER;
1608 l_osp_order_rec AHL_OSP_ORDERS_PVT.osp_order_rec_type;
1609 l_oe_line_tbl OE_ORDER_PUB.LINE_TBL_TYPE;
1610 l_oe_lot_serial_tbl OE_ORDER_PUB.LOT_SERIAL_TBL_TYPE;
1611 l_derived_instance_id NUMBER;
1612 l_del_oe_lines_tbl AHL_OSP_SHIPMENT_PUB.SHIP_ID_TBL_TYPE;
1613 l_osp_order_lines_tbl AHL_OSP_ORDERS_PVT.OSP_ORDER_LINES_TBL_TYPE;
1614 l_request_id NUMBER;
1615
1616 l_return_status VARCHAR2(1);
1617 l_msg_count NUMBER;
1618 l_msg_data VARCHAR2(2000);
1619 l_msg_index_out NUMBER;
1620 l_oe_line_rec OE_ORDER_PUB.line_rec_type;
1621 l_rma_line_canceled boolean;
1622 l_ib_trans_deleted boolean;
1623 l_dummy VARCHAR2(1);
1624 l_err_intf_trans_id NUMBER;
1625 l_err_intf_hdr_id NUMBER;
1626 l_same_ser_ospline_id NUMBER;
1627 -- SALOGAN added variable for 9496606
1628 l_receiving_locator_id NUMBER;
1629 --
1630
1631 BEGIN
1632 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1633 FND_LOG.string(FND_LOG.level_procedure, l_debug_key ||'.begin', 'Start of the API.');
1634 END IF;
1635
1636 -- Standard start of API savepoint.
1637 SAVEPOINT Receive_Against_RMA_Pvt;
1638
1639 -- Initialize Procedure return status to success.
1640 x_return_status := FND_API.G_RET_STS_SUCCESS;
1641
1642 -- Standard call to check for call compatibility.
1643 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1644 l_api_name, G_PKG_NAME) THEN
1645 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1646 END IF;
1647
1648 -- Initialize message list if p_init_msg_list is set to TRUE.
1649 IF FND_API.To_Boolean(p_init_msg_list) THEN
1650 FND_MSG_PUB.Initialize;
1651 END IF;
1652
1653 x_return_line_id := null;
1654
1655 --Log the input parameters
1656 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1657 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'RETURN_LINE_ID:'||p_rma_receipt_rec.RETURN_LINE_ID);
1658 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'RECEIVING_ORG_ID:'||p_rma_receipt_rec.RECEIVING_ORG_ID);
1659 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'RECEIVING_SUBINVENTORY:'||p_rma_receipt_rec.RECEIVING_SUBINVENTORY);
1660 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'RECEIVING_LOCATOR_ID:'||p_rma_receipt_rec.RECEIVING_LOCATOR_ID);
1661 -- SALOGAN added to log Locator name attribute for 9496606
1662 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'RECEIVING_LOCATOR_NAME:'||p_rma_receipt_rec.RECEIVING_LOCATOR_NAME);
1663 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'RECEIPT_QUANTITY:'||p_rma_receipt_rec.RECEIPT_QUANTITY);
1664 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'RECEIPT_UOM_CODE:'||p_rma_receipt_rec.RECEIPT_UOM_CODE);
1665 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'RECEIPT_DATE:'||p_rma_receipt_rec.RECEIPT_DATE);
1666 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'NEW_SERIAL_NUMBER:'||p_rma_receipt_rec.NEW_SERIAL_NUMBER);
1667 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'NEW_SERIAL_TAG_CODE:'||p_rma_receipt_rec.NEW_SERIAL_TAG_CODE);
1668 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'NEW_LOT_NUMBER:'||p_rma_receipt_rec.NEW_LOT_NUMBER);
1669 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'NEW_ITEM_REV_NUMBER:'||p_rma_receipt_rec.NEW_ITEM_REV_NUMBER);
1670 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'EXCHANGE_ITEM_ID:'||p_rma_receipt_rec.EXCHANGE_ITEM_ID);
1671 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'EXCHANGE_SERIAL_NUMBER:'||p_rma_receipt_rec.EXCHANGE_SERIAL_NUMBER);
1672 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'EXCHANGE_LOT_NUMBER:'||p_rma_receipt_rec.EXCHANGE_LOT_NUMBER);
1673 END IF;
1674
1675 -- Check whether a pending receipt transaction exists for this return line id.
1676 OPEN chk_pending_transaction(p_rma_receipt_rec.RETURN_LINE_ID);
1677 FETCH chk_pending_transaction INTO l_dummy;
1678 IF (chk_pending_transaction%FOUND) THEN
1679 -- Pending receipt transaction exists for this PO line id. Raise an exception.
1680 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1681 FND_LOG.string(FND_LOG.level_statement, l_debug_key, 'Pending transactions exist for oe line id: '||p_rma_receipt_rec.RETURN_LINE_ID);
1682 END IF;
1683 CLOSE chk_pending_transaction;
1684 FND_MESSAGE.set_name('AHL', 'AHL_OSP_OE_TRANS_PENDING'); -- The return line has pending transactions.
1685 FND_MSG_PUB.ADD;
1686 RAISE FND_API.G_EXC_ERROR;
1687 END IF;
1688 CLOSE chk_pending_transaction;
1689
1690 /* Validate the input parameters. This api will throw any validation errors */
1691 validate_receiving_params(p_rma_receipt_rec);
1692 -- SALOGAN added to call prcedure to validate and create a new locator if needed for 9496606 START
1693 validate_create_locator(p_rma_receipt_rec => p_rma_receipt_rec,
1694 x_receiving_locator_id => l_receiving_locator_id,
1695 x_return_status => l_return_status,
1696 x_msg_count => l_msg_count,
1697 x_msg_data => l_msg_data);
1698
1699 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1700 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'After calling validate_create_locator');
1701 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_return_status: '||l_return_status);
1702 END IF;
1703
1704 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1705 RAISE FND_API.G_EXC_ERROR;
1706 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1707 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1708 END IF;
1709 -- SALOGAN added to call prcedure to validate and create a new locator if needed for 9496606 END
1710
1711 -- Check for part number change attributes.
1712 IF (p_rma_receipt_rec.new_item_id IS NOT NULL OR p_rma_receipt_rec.new_serial_number IS NOT NULL) THEN
1713 -- Set the l_part_num_change_flag.
1714 l_part_num_change_flag := TRUE;
1715 END IF;
1716
1717 -- Check for exchange attributes.
1718 IF (p_rma_receipt_rec.exchange_item_id IS NOT NULL OR p_rma_receipt_rec.exchange_serial_number IS NOT NULL OR
1719 p_rma_receipt_rec.exchange_lot_number IS NOT NULL) THEN
1720 -- Set the l_exchange_flag.
1721 l_exchange_flag := TRUE;
1722 END IF;
1723
1724 --Derive the osp_line_id for which the part number/serial change is being performed
1725 OPEN get_osp_order_dtls(p_rma_receipt_rec.return_line_id);
1726 FETCH get_osp_order_dtls INTO l_osp_order_id,l_osp_order_type,l_osp_line_id,l_osp_ord_obj_ver;
1727 CLOSE get_osp_order_dtls;
1728
1729 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1730 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_osp_order_id:'||l_osp_order_id);
1731 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_osp_order_type:'||l_osp_order_type);
1732 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_osp_line_id:'|| l_osp_line_id);
1733 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_osp_ord_obj_ver:'|| l_osp_ord_obj_ver);
1734 END IF;
1735
1736 --Retrieve the Return Line details
1737 OPEN oe_order_line_details_csr(p_rma_receipt_rec.return_line_id);
1738 FETCH oe_order_line_details_csr INTO l_oe_order_line_rec;
1739 CLOSE oe_order_line_details_csr;
1740
1741 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1742 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_oe_order_line_rec.inventory_item_id:'||l_oe_order_line_rec.inventory_item_id);
1743 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_oe_order_line_rec.customer_id:'||l_oe_order_line_rec.customer_id);
1744 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_oe_order_line_rec.customer_site_id:'||l_oe_order_line_rec.customer_site_id);
1745 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_oe_order_line_rec.organization_id:'||l_oe_order_line_rec.organization_id);
1746 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_oe_order_line_rec.subinventory:'||l_oe_order_line_rec.subinventory);
1747 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_oe_order_line_rec.oe_order_header_id:'||l_oe_order_line_rec.oe_order_header_id);
1748 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_oe_order_line_rec.oe_order_line_id:'||l_oe_order_line_rec.oe_order_line_id);
1749 END IF;
1750
1751 --Part Number change is being performed
1752 IF (l_part_num_change_flag) THEN
1753
1754 IF(l_osp_line_id is not null AND AHL_OSP_SHIPMENT_PUB.Is_part_chg_valid_for_ospline(l_osp_line_id) = 'Y')
1755 THEN
1756 OPEN get_item_number(p_rma_receipt_rec.new_item_id);
1757 FETCH get_item_number INTO l_new_item_number;
1758 CLOSE get_item_number;
1759
1760 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1761 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_new_item_number:'||l_new_item_number);
1762 END IF;
1763 l_serialnum_change_rec.OSP_LINE_ID := l_osp_line_id;
1764 l_serialnum_change_rec.NEW_ITEM_NUMBER := l_new_item_number;
1765 l_serialnum_change_rec.NEW_ITEM_REV_NUMBER := p_rma_receipt_rec.NEW_ITEM_REV_NUMBER;
1766 l_serialnum_change_rec.NEW_LOT_NUMBER := p_rma_receipt_rec.NEW_LOT_NUMBER;
1767 l_serialnum_change_rec.NEW_SERIAL_NUMBER := p_rma_receipt_rec.NEW_SERIAL_NUMBER;
1768 l_serialnum_change_rec.NEW_SERIAL_TAG_CODE := p_rma_receipt_rec.NEW_SERIAL_TAG_CODE;
1769
1770 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1771 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'Before calling AHL_OSP_SHIPMENT_PUB.Process_Osp_SerialNum_Change');
1772 END IF;
1773
1774 AHL_OSP_SHIPMENT_PUB.Process_Osp_SerialNum_Change
1775 (
1776 p_api_version => 1.0,
1777 p_init_msg_list => FND_API.G_FALSE,
1778 p_commit => FND_API.G_FALSE,
1779 p_serialnum_change_rec => l_serialnum_change_rec,
1780 x_return_status => l_return_status,
1781 x_msg_count => l_msg_count,
1782 x_msg_data => l_msg_data
1783 );
1784
1785 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1786 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'After calling AHL_OSP_SHIPMENT_PUB.Process_Osp_SerialNum_Change');
1787 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_return_status: '||l_return_status);
1788 END IF;
1789
1790 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1791 RAISE FND_API.G_EXC_ERROR;
1792 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1793 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1794 END IF;
1795 --Retrieve the new return line id
1796 OPEN get_oe_line_id(l_osp_line_id);
1797 FETCH get_oe_line_id INTO l_new_oe_line_id;
1798 CLOSE get_oe_line_id;
1799
1800 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1801 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_new_oe_line_id: '||l_new_oe_line_id);
1802 END IF;
1803
1804 x_return_line_id := l_new_oe_line_id;
1805
1806 ELSE
1807 --Part number change cannot be performed for the return line.
1808 FND_MESSAGE.Set_Name('AHL','AHL_OSP_CHG_OSPL_INV');
1809 FND_MSG_PUB.ADD;
1810 RAISE FND_API.G_EXC_ERROR;
1811 END IF;
1812
1813 ELSIF (l_exchange_flag) THEN
1814 --Exchange is being performed for the return line
1815 l_rma_line_canceled := false;
1816 l_ib_trans_deleted := false;
1817 IF(l_osp_order_type = AHL_OSP_ORDERS_PVT.G_OSP_ORDER_TYPE_SERVICE) THEN
1818 l_osp_order_rec.OSP_ORDER_ID := l_osp_order_id;
1819 l_osp_order_rec.ORDER_TYPE_CODE := AHL_OSP_ORDERS_PVT.G_OSP_ORDER_TYPE_EXCHANGE;
1820 l_osp_order_rec.OBJECT_VERSION_NUMBER := l_osp_ord_obj_ver;
1821 l_osp_order_rec.OPERATION_FLAG := 'U';
1822
1823 --Save the OE Return Line information, before deletion
1824 l_oe_line_rec := OE_LINE_UTIL.QUERY_ROW(p_line_id => p_rma_receipt_rec.RETURN_LINE_ID);
1825 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1826 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Queried the OM Line Record');
1827 END IF;
1828
1829 /* Convert the Service Order into Exchange Order. All the existing RMA lines will be cancelled if there was no
1830 receipt performed on any of the lines. */
1831 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1832 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'Before calling AHL_OSP_ORDERS_PVT.process_osp_order for order conversion');
1833 END IF;
1834 AHL_OSP_ORDERS_PVT.process_osp_order(
1835 p_api_version => 1.0,
1836 p_init_msg_list => FND_API.G_FALSE,
1837 p_commit => FND_API.G_FALSE,
1838 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1839 p_module_type => NULL,
1840 p_x_osp_order_rec => l_osp_order_rec,
1841 p_x_osp_order_lines_tbl => l_osp_order_lines_tbl,
1842 x_return_status => l_return_status,
1843 x_msg_count => l_msg_count,
1844 x_msg_data => l_msg_data);
1845
1846 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1847 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'After calling AHL_OSP_ORDERS_PVT.process_osp_order for order conversion');
1848 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_return_status: '||l_return_status);
1849 END IF;
1850
1851 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1852 RAISE FND_API.G_EXC_ERROR;
1853 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1854 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1855 END IF;
1856 l_rma_line_canceled := true;
1857 --Need to change the order type, if the conversion is indeed successful. Depending on the order type, the serial number/lot
1858 --number are dervived for receipt.
1859 l_osp_order_type := AHL_OSP_ORDERS_PVT.G_OSP_ORDER_TYPE_EXCHANGE;
1860
1861 --change by jrotich 08/22/2012 for handling service to exchange order conversion bug 14526569
1862 --we set both the l_is_ib_trackable and l_derived_instance_id for IB Txns to be created begin
1863 OPEN inv_item_ctrls_csr(p_rma_receipt_rec.EXCHANGE_ITEM_ID, p_rma_receipt_rec.receiving_org_id);
1864 FETCH inv_item_ctrls_csr INTO l_serial_control_code,l_lot_control_code,l_is_ib_trackable;
1865 CLOSE inv_item_ctrls_csr;
1866
1867 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1868 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_serial_control_code:'||l_serial_control_code);
1869 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_lot_control_code:'||l_lot_control_code);
1870 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_is_ib_trackable:'|| l_is_ib_trackable);
1871 END IF;
1872 IF(l_is_ib_trackable = 'Y') THEN
1873 l_derived_instance_id := null;
1874 OPEN get_instance_id(p_rma_receipt_rec.EXCHANGE_ITEM_ID,p_rma_receipt_rec.exchange_serial_number);
1875 FETCH get_instance_id INTO l_derived_instance_id;
1876 CLOSE get_instance_id;
1877 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1878 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_derived_instance_id:'||l_derived_instance_id);
1879 END IF;
1880 END IF;
1881 --change by jrotich 8/22/2012 end
1882
1883 ELSE--Existing Order is already an Exchange Order
1884 --Derive the exchange item properties
1885 OPEN inv_item_ctrls_csr(p_rma_receipt_rec.EXCHANGE_ITEM_ID, p_rma_receipt_rec.receiving_org_id);
1886 FETCH inv_item_ctrls_csr INTO l_serial_control_code,l_lot_control_code,l_is_ib_trackable;
1887 CLOSE inv_item_ctrls_csr;
1888
1889 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1890 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_serial_control_code:'||l_serial_control_code);
1891 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_lot_control_code:'||l_lot_control_code);
1892 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_is_ib_trackable:'|| l_is_ib_trackable);
1893 END IF;
1894 IF(l_is_ib_trackable = 'Y') THEN
1895 l_derived_instance_id := null;
1896 OPEN get_instance_id(p_rma_receipt_rec.EXCHANGE_ITEM_ID,p_rma_receipt_rec.exchange_serial_number);
1897 FETCH get_instance_id INTO l_derived_instance_id;
1898 CLOSE get_instance_id;
1899 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1900 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_derived_instance_id:'||l_derived_instance_id);
1901 END IF;
1902 END IF;
1903
1904 --First compare the exchange_item_id with the item on the return line.
1905 IF(l_oe_order_line_rec.inventory_item_id = p_rma_receipt_rec.EXCHANGE_ITEM_ID) THEN
1906 /*
1907 Note that, even if the item is same, the serial entered by the user may be different from
1908 the one on the return line. For non-IB tracked items this serial may be matched with the one on the
1909 oe_lot_serial table. But we are not accounting such differences in the current logic
1910 */
1911 --Use the entered serial to derive the instance_id
1912 IF(l_is_ib_trackable = 'Y') THEN
1913 OPEN get_osp_order_line_dtls(l_osp_line_id);
1914 FETCH get_osp_order_line_dtls INTO l_osp_order_line_rec;
1915 CLOSE get_osp_order_line_dtls;
1916 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1917 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_osp_order_line_rec.exchange_instance_id:'||l_osp_order_line_rec.exchange_instance_id);
1918 END IF;
1919 IF(nvl(l_derived_instance_id,-1) <> nvl(l_osp_order_line_rec.exchange_instance_id,-1)) THEN
1920 AHL_OSP_SHIPMENT_PUB.Delete_IB_Transaction(
1921 p_init_msg_list => FND_API.G_FALSE, --p_init_msg_list,
1922 p_commit => FND_API.G_FALSE,
1923 p_validation_level => p_validation_level,
1924 x_return_status => x_return_status,
1925 x_msg_count => x_msg_count,
1926 x_msg_data => x_msg_data,
1927 p_oe_line_id => p_rma_receipt_rec.return_line_id);
1928
1929 l_ib_trans_deleted := true;
1930 END IF;
1931 END IF;--IF(l_is_ib_trackable = 'Y') THEN
1932 ELSE--item on the oe order line is different from that of the exchange instance entered by the user
1933 --Cancel the RMA line
1934 --Save the OE Return Line information, before deletion
1935 l_oe_line_rec := OE_LINE_UTIL.QUERY_ROW(p_line_id => p_rma_receipt_rec.RETURN_LINE_ID);
1936 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1937 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Queried the OM Line Record');
1938 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Before calling the Delete_Cancel_Order');
1939 END IF;
1940
1941 l_del_oe_lines_tbl(1) := p_rma_receipt_rec.return_line_id;
1942 AHL_OSP_SHIPMENT_PUB.Delete_Cancel_Order (
1943 p_api_version => 1.0,
1944 p_init_msg_list => FND_API.G_FALSE, -- Don't initialize the Message List
1945 p_commit => FND_API.G_FALSE, -- Don't commit independently
1946 p_oe_header_id => null, -- Not deleting the shipment header: Only the lines
1947 p_oe_lines_tbl => l_del_oe_lines_tbl, -- Lines to be deleted/Cancelled
1948 p_cancel_flag => FND_API.G_FALSE, -- Do Deletes if possible, Cancels if not
1949 x_return_status => l_return_status ,
1950 x_msg_count => l_msg_count ,
1951 x_msg_data => l_msg_data
1952 );
1953 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1954 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Returned from Delete_Cancel_Order, l_return_status = ' || l_return_status);
1955 END IF;
1956 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1957 RAISE FND_API.G_EXC_ERROR;
1958 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1959 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1960 END IF;
1961 l_rma_line_canceled := true;
1962 END IF;--IF(l_oe_order_line_rec.inventory_item_id = p_rma_receipt_rec.EXCHANGE_ITEM_ID) THEN
1963 END IF;--IF(l_osp_order_type = AHL_OSP_ORDERS_PVT.G_OSP_ORDER_TYPE_SERVICE) THEN
1964
1965 --The following holds good for both service orders and exchange orders
1966 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1967 IF(l_rma_line_canceled) THEN
1968 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_rma_line_canceled: true');
1969 ELSE
1970 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_rma_line_canceled: false');
1971 END IF;
1972 IF(l_ib_trans_deleted) THEN
1973 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_ib_trans_deleted: true');
1974 ELSE
1975 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_ib_trans_deleted: false');
1976 END IF;
1977 END IF;
1978
1979 IF(l_rma_line_canceled) THEN
1980 l_oe_line_tbl := OE_ORDER_PUB.G_MISS_LINE_TBL;
1981 l_oe_lot_serial_tbl := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;
1982
1983 /* Create a new RMA Line, corresponding to the receipt */
1984 l_oe_line_tbl(1) := l_oe_line_rec;
1985 l_oe_line_tbl(1).inventory_item_id := p_rma_receipt_rec.EXCHANGE_ITEM_ID;
1986 l_oe_line_tbl(1).line_id := FND_API.G_MISS_NUM;
1987 l_oe_line_tbl(1).line_number := FND_API.G_MISS_NUM;
1988 l_oe_line_tbl(1).operation := OE_GLOBALS.G_OPR_CREATE;
1989
1990 IF(p_rma_receipt_rec.EXCHANGE_SERIAL_NUMBER is not NULL OR p_rma_receipt_rec.EXCHANGE_LOT_NUMBER is not NULL) THEN
1991 --populate the lot_serial_rec
1992 l_oe_lot_serial_tbl(1).lot_serial_id := FND_API.G_MISS_NUM;
1993 l_oe_lot_serial_tbl(1).lot_number := p_rma_receipt_rec.exchange_lot_number;
1994 l_oe_lot_serial_tbl(1).from_serial_number := p_rma_receipt_rec.exchange_serial_number;
1995 l_oe_lot_serial_tbl(1).quantity := l_oe_line_rec.ordered_quantity;
1996 l_oe_lot_serial_tbl(1).line_index := 1;
1997 l_oe_lot_serial_tbl(1).operation := OE_GLOBALS.G_OPR_CREATE;
1998 END IF;
1999 /*Create the new RMA line*/
2000 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2001 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call OE_ORDER_GRP.PROCESS_ORDER');
2002 END IF;
2003
2004 OE_ORDER_GRP.PROCESS_ORDER(
2005 p_api_version_number => 1.0,
2006 p_init_msg_list => FND_API.G_TRUE,
2007 x_return_status => x_return_status,
2008 x_msg_count => x_msg_count,
2009 x_msg_data => x_msg_data,
2010 p_header_rec => x_header_rec,
2011 p_header_val_rec => x_header_val_rec,
2012 p_line_tbl => l_oe_line_tbl,
2013 p_line_val_tbl => x_line_val_tbl,
2014 p_lot_serial_tbl => l_oe_lot_serial_tbl,
2015 x_header_rec => x_header_rec,
2016 x_header_val_rec => x_header_val_rec,
2017 x_Header_Adj_tbl => x_Header_Adj_tbl,
2018 x_Header_Adj_val_tbl => x_Header_Adj_val_tbl,
2019 x_Header_price_Att_tbl => x_Header_price_Att_tbl,
2020 x_Header_Adj_Att_tbl => x_Header_Adj_Att_tbl,
2021 x_Header_Adj_Assoc_tbl => x_Header_Adj_Assoc_tbl,
2022 x_Header_Scredit_tbl => x_Header_Scredit_tbl,
2023 x_Header_Scredit_val_tbl => x_Header_Scredit_val_tbl,
2024 x_line_tbl => x_line_tbl ,
2025 x_line_val_tbl => x_line_val_tbl ,
2026 x_Line_Adj_tbl => x_Line_Adj_tbl ,
2027 x_Line_Adj_val_tbl => x_Line_Adj_val_tbl,
2028 x_Line_price_Att_tbl => x_Line_price_Att_tbl,
2029 x_Line_Adj_Att_tbl => x_Line_Adj_Att_tbl ,
2030 x_Line_Adj_Assoc_tbl => x_Line_Adj_Assoc_tbl,
2031 x_Line_Scredit_tbl => x_Line_Scredit_tbl ,
2032 x_Line_Scredit_val_tbl => x_Line_Scredit_val_tbl,
2033 x_Lot_Serial_tbl => x_Lot_Serial_tbl ,
2034 x_Lot_Serial_val_tbl => x_Lot_Serial_val_tbl ,
2035 x_action_request_tbl => x_action_request_tbl );
2036
2037 --populate the return_line_id with the one that was created in the OM API call.
2038 x_return_line_id := x_line_tbl(1).line_id;
2039
2040 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2041 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Completed OE_ORDER_GRP.PROCESS_ORDER, x_return_status = ' || x_return_status);
2042 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'x_return_line_id = ' || x_return_line_id);
2043 END IF;
2044
2045 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2046 FOR i IN 1..x_msg_count LOOP
2047 OE_MSG_PUB.Get(p_msg_index => i,
2048 p_encoded => FND_API.G_FALSE,
2049 p_data => l_msg_data,
2050 p_msg_index_out => l_msg_index_out);
2051 fnd_msg_pub.add_exc_msg(p_pkg_name => 'OE_ORDER_PUB',
2052 p_procedure_name => 'processOrder',
2053 p_error_text => substr(l_msg_data,1,240));
2054 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2055 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'OE:Err Msg '||i||'.' || l_msg_data);
2056 END IF;
2057
2058 END LOOP;
2059 END IF;
2060
2061 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2062 RAISE FND_API.G_EXC_ERROR;
2063 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2064 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2065 END IF;
2066
2067 /* Update the osp_line with the new RMA line id*/
2068 OPEN get_same_phyitem_order_lines(l_osp_line_id);
2069 LOOP
2070 FETCH get_same_phyitem_order_lines INTO l_same_ser_ospline_id;
2071 EXIT WHEN get_same_phyitem_order_lines%NOTFOUND;
2072 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2073 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_same_ser_ospline_id: ' || l_same_ser_ospline_id);
2074 END IF;
2075 Update_OSP_Order_Lines(
2076 p_osp_order_id => l_osp_order_id,
2077 p_osp_line_id => l_same_ser_ospline_id,
2078 p_oe_ship_line_id => FND_API.G_MISS_NUM ,
2079 p_oe_return_line_id => x_return_line_id);
2080 END LOOP;
2081 CLOSE get_same_phyitem_order_lines;
2082 END IF;--IF(l_rma_line_canceled) THEN
2083
2084 IF(l_is_ib_trackable = 'Y' AND (l_rma_line_canceled OR l_ib_trans_deleted)) THEN
2085 /* Update the osp_line with the new exchange instance id*/
2086 OPEN get_same_phyitem_order_lines(l_osp_line_id);
2087 LOOP
2088 FETCH get_same_phyitem_order_lines INTO l_same_ser_ospline_id;
2089 EXIT WHEN get_same_phyitem_order_lines%NOTFOUND;
2090 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2091 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_same_ser_ospline_id: ' || l_same_ser_ospline_id);
2092 END IF;
2093 Update_OSP_Line_Exch_Instance(
2094 p_osp_order_id => l_osp_order_id,
2095 p_osp_line_id => l_same_ser_ospline_id,
2096 p_exchange_instance_id => l_derived_instance_id);
2097 END LOOP;
2098 CLOSE get_same_phyitem_order_lines;
2099
2100 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2101 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Before calling Create_IB_Transaction ' );
2102 END IF;
2103
2104 /* Create the new IB transaction */
2105 AHL_OSP_SHIPMENT_PUB.Create_IB_Transaction(
2106 p_init_msg_list => FND_API.G_FALSE, --p_init_msg_list,
2107 p_commit => FND_API.G_FALSE,
2108 p_validation_level => p_validation_level,
2109 x_return_status => l_return_status,
2110 x_msg_count => l_msg_count,
2111 x_msg_data => l_msg_data,
2112 p_osp_order_type => AHL_OSP_ORDERS_PVT.G_OSP_ORDER_TYPE_EXCHANGE,
2113 p_oe_line_type => 'RETURN',
2114 p_oe_line_id => nvl(x_return_line_id,p_rma_receipt_rec.return_line_id),
2115 p_csi_instance_id => l_derived_instance_id);
2116
2117 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2118 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Return status from Create_IB_Transaction: ' || l_return_status);
2119 END IF;
2120
2121 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2122 RAISE FND_API.G_EXC_ERROR;
2123 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2124 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2125 END IF;
2126
2127 END IF;--IF(l_is_ib_trackable = 'Y' AND (l_rma_line_canceled OR l_ib_trans_deleted)) THEN
2128
2129 END IF;--IF (l_part_num_change_flag) THEN
2130
2131 l_return_line_id := nvl(x_return_line_id,p_rma_receipt_rec.return_line_id);
2132 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2133 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_return_line_id:'||l_return_line_id);
2134 END IF;
2135
2136 IF(x_return_line_id is not null) THEN
2137 --New return line has been created. Retrieve the Return Line details
2138 OPEN oe_order_line_details_csr(x_return_line_id);
2139 FETCH oe_order_line_details_csr INTO l_oe_order_line_rec;
2140 CLOSE oe_order_line_details_csr;
2141
2142 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2143 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_oe_order_line_rec.inventory_item_id:'||l_oe_order_line_rec.inventory_item_id);
2144 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_oe_order_line_rec.customer_id:'||l_oe_order_line_rec.customer_id);
2145 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_oe_order_line_rec.customer_site_id:'||l_oe_order_line_rec.customer_site_id);
2146 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_oe_order_line_rec.organization_id:'||l_oe_order_line_rec.organization_id);
2147 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_oe_order_line_rec.subinventory:'||l_oe_order_line_rec.subinventory);
2148 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_oe_order_line_rec.oe_order_header_id:'||l_oe_order_line_rec.oe_order_header_id);
2149 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_oe_order_line_rec.oe_order_line_id:'||l_oe_order_line_rec.oe_order_line_id);
2150 END IF;
2151
2152 END IF;
2153
2154 --Delete any errored transactions before creating a new receipt.
2155 OPEN get_err_tranansaction_dtls(p_rma_receipt_rec.RETURN_LINE_ID);
2156 FETCH get_err_tranansaction_dtls INTO l_err_intf_trans_id,l_err_intf_hdr_id;
2157 CLOSE get_err_tranansaction_dtls;
2158
2159 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2160 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_err_intf_trans_id:'||l_err_intf_trans_id);
2161 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_err_intf_hdr_id:'||l_err_intf_hdr_id);
2162 END IF;
2163
2164 IF(l_err_intf_trans_id is not NULL) THEN
2165 --Delete the pending transactions.
2166 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2167 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'Deleting the pending transactions');
2168 END IF;
2169
2170 DELETE FROM PO_INTERFACE_ERRORS
2171 WHERE INTERFACE_LINE_ID = l_err_intf_trans_id
2172 AND INTERFACE_HEADER_ID = l_err_intf_hdr_id;
2173
2174 DELETE FROM MTL_SERIAL_NUMBERS_INTERFACE
2175 WHERE PRODUCT_TRANSACTION_ID = l_err_intf_trans_id;
2176
2177 DELETE FROM MTL_TRANSACTION_LOTS_INTERFACE
2178 WHERE PRODUCT_TRANSACTION_ID = l_err_intf_trans_id;
2179
2180 DELETE FROM RCV_TRANSACTIONS_INTERFACE
2181 WHERE INTERFACE_TRANSACTION_ID = l_err_intf_trans_id;
2182
2183 DELETE FROM RCV_HEADERS_INTERFACE
2184 WHERE HEADER_INTERFACE_ID = l_err_intf_hdr_id;
2185
2186 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2187 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'Deleted the pending transactions');
2188 END IF;
2189 END IF;
2190
2191 --Initialize the sequences that are to be used.
2192 SELECT PO.RCV_HEADERS_INTERFACE_S.NEXTVAL INTO l_intf_hdr_id FROM sys.dual;
2193 SELECT PO.RCV_INTERFACE_GROUPS_S.NEXTVAL INTO l_group_id FROM sys.dual;
2194 SELECT PO.RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL INTO l_intf_transaction_id from sys.dual;
2195 SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL INTO l_mtl_transaction_id from sys.dual;
2196
2197 --select the user related ids
2198 SELECT FND_GLOBAL.USER_ID INTO l_user_id from sys.dual;
2199 SELECT FND_GLOBAL.LOGIN_ID INTO l_login_id from sys.dual;
2200 SELECT FND_GLOBAL.EMPLOYEE_ID INTO l_employee_id from sys.dual;
2201
2202 --Insert values into the RCV_HEADERS_INTERFACE
2203 INSERT INTO RCV_HEADERS_INTERFACE
2204 (
2205 HEADER_INTERFACE_ID,
2206 GROUP_ID,
2207 PROCESSING_STATUS_CODE,
2208 RECEIPT_SOURCE_CODE,
2209 TRANSACTION_TYPE,
2210 LAST_UPDATE_DATE,
2211 LAST_UPDATED_BY,
2212 LAST_UPDATE_LOGIN,
2213 CREATION_DATE,
2214 CREATED_BY,
2215 CUSTOMER_ID,
2216 CUSTOMER_SITE_ID,
2217 SHIP_TO_ORGANIZATION_ID,
2218 AUTO_TRANSACT_CODE,
2219 EMPLOYEE_ID
2220 )
2221 VALUES
2222 (
2223 l_intf_hdr_id, --HEADER_INTERFACE_ID,
2224 l_group_id, --GROUP_ID,
2225 'PENDING', --PROCESSING_STATUS_CODE,
2226 'CUSTOMER', --RECEIPT_SOURCE_CODE,
2227 'NEW', --TRANSACTION_TYPE,
2228 SYSDATE, --LAST_UPDATE_DATE,
2229 l_user_id, --LAST_UPDATED_BY,
2230 l_login_id, --LAST_UPDATE_LOGIN,
2231 SYSDATE, --CREATION_DATE,
2232 l_user_id, --CREATED_BY,
2233 l_oe_order_line_rec.customer_id, --CUSTOMER_ID,
2234 l_oe_order_line_rec.customer_site_id, --CUSTOMER_SITE_ID
2235 l_oe_order_line_rec.organization_id, --SHIP_TO_ORGANIZATION_ID
2236 'DELIVER', --AUTO_TRANSACT_CODE
2237 l_employee_id --EMPLOYEE_ID
2238 );
2239
2240 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2241 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_intf_hdr_id:'||l_intf_hdr_id);
2242 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_group_id:'||l_group_id);
2243 END IF;
2244
2245 INSERT INTO RCV_TRANSACTIONS_INTERFACE
2246 (
2247 INTERFACE_TRANSACTION_ID,
2248 HEADER_INTERFACE_ID,
2249 GROUP_ID,
2250 LAST_UPDATE_DATE,
2251 LAST_UPDATED_BY,
2252 CREATION_DATE,
2253 CREATED_BY,
2254 LAST_UPDATE_LOGIN,
2255 TRANSACTION_TYPE,
2256 TRANSACTION_DATE,
2257 PROCESSING_STATUS_CODE,
2258 PROCESSING_MODE_CODE,
2259 TRANSACTION_STATUS_CODE,
2260 QUANTITY,
2261 AUTO_TRANSACT_CODE,
2262 RECEIPT_SOURCE_CODE,
2263 SOURCE_DOCUMENT_CODE,
2264 VALIDATION_FLAG,
2265 OE_ORDER_HEADER_ID,
2266 OE_ORDER_LINE_ID,
2267 TO_ORGANIZATION_ID,
2268 SUBINVENTORY,
2269 LOCATOR_ID,
2270 INTERFACE_SOURCE_CODE,
2271 UOM_CODE
2272 )
2273 VALUES
2274 (
2275 l_intf_transaction_id, --INTERFACE_TRANSACTION_ID,
2276 l_intf_hdr_id, --HEADER_INTERFACE_ID,
2277 l_group_id, --GROUP_ID,
2278 SYSDATE, --LAST_UPDATE_DATE,
2279 l_user_id, --LAST_UPDATED_BY,
2280 SYSDATE, --CREATION_DATE,
2281 l_user_id, --CREATED_BY,
2282 l_login_id, --LAST_UPDATE_LOGIN,
2283 'RECEIVE', --TRANSACTION_TYPE,
2284 p_rma_receipt_rec.receipt_date, --TRANSACTION_DATE,
2285 'PENDING', --PROCESSING_STATUS_CODE,
2286 --Modified by mpothuku on 04-Mar-2007 for the Bug 6862891
2287 'BATCH', --PROCESSING_MODE_CODE,
2288 'PENDING', --TRANSACTION_STATUS_CODE,
2289 p_rma_receipt_rec.receipt_quantity, --QUANTITY,
2290 'DELIVER', --AUTO_TRANSACT_CODE: 'DELIVER' is needed to ensure delivery of the receipt
2291 'CUSTOMER', --RECEIPT_SOURCE_CODE,
2292 'RMA', --SOURCE_DOCUMENT_CODE,
2293 'Y', --VALIDATION_FLAG,
2294 l_oe_order_line_rec.oe_order_header_id, --OE_ORDER_HEADER_ID,
2295 l_oe_order_line_rec.oe_order_line_id, --OE_ORDER_LINE_ID,
2296 l_oe_order_line_rec.organization_id, --TO_ORGANIZATION_ID
2297 p_rma_receipt_rec.receiving_subinventory,--SUBINVENTORY
2298 -- SALOGAN commeted old and added local variable instead of IN param for 9496606
2299 --p_rma_receipt_rec.receiving_locator_id, --LOCATOR_ID
2300 l_receiving_locator_id,
2301 'AHL', --INTERFACE_SOURCE_CODE
2302 p_rma_receipt_rec.receipt_uom_code --UOM_CODE
2303 );
2304
2305 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2306 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_intf_transaction_id:'||l_intf_transaction_id);
2307 END IF;
2308
2309 IF(l_osp_line_id is NULL) THEN
2310 --The return is being performed for a spare part. Retrieve the lot and serial from the oe_lot_serial record
2311 OPEN ahl_oe_lot_serial_id_csr(p_rma_receipt_rec.return_line_id);
2312 FETCH ahl_oe_lot_serial_id_csr INTO l_oe_lot_serial_rec;
2313 CLOSE ahl_oe_lot_serial_id_csr;
2314 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2315 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_oe_lot_serial_rec.lot_number:'||l_oe_lot_serial_rec.lot_number);
2316 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_oe_lot_serial_rec.serial_number:'||l_oe_lot_serial_rec.serial_number);
2317 END IF;
2318 ELSE
2319 OPEN get_osp_order_line_dtls(l_osp_line_id);
2320 FETCH get_osp_order_line_dtls INTO l_osp_order_line_rec;
2321 CLOSE get_osp_order_line_dtls;
2322 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2323 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_osp_order_line_rec.inventory_item_id:'
2324 ||l_osp_order_line_rec.inventory_item_id);
2325 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_osp_order_line_rec.lot_number:'||l_osp_order_line_rec.lot_number);
2326 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_osp_order_line_rec.serial_number:'||l_osp_order_line_rec.serial_number);
2327 END IF;
2328 END IF;
2329
2330 OPEN inv_item_ctrls_csr(l_oe_order_line_rec.inventory_item_id, p_rma_receipt_rec.receiving_org_id);
2331 FETCH inv_item_ctrls_csr INTO l_serial_control_code,l_lot_control_code,l_is_ib_trackable;
2332 CLOSE inv_item_ctrls_csr;
2333
2334 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2335 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_serial_control_code:'||l_serial_control_code);
2336 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_lot_control_code:'||l_lot_control_code);
2337 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_is_ib_trackable:'|| l_is_ib_trackable);
2338 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_osp_order_type:'|| l_osp_order_type);
2339 END IF;
2340
2341 IF(l_is_ib_trackable = 'Y') THEN
2342 OPEN get_IB_subtrns_inst_dtls_csr(l_return_line_id);
2343 FETCH get_IB_subtrns_inst_dtls_csr INTO l_IB_subtrns_inst_rec;
2344 CLOSE get_IB_subtrns_inst_dtls_csr;
2345 END IF;
2346
2347 IF (l_serial_control_code IN (2,5,6)) THEN
2348 IF(l_osp_order_type = AHL_OSP_ORDERS_PVT.G_OSP_ORDER_TYPE_SERVICE) THEN
2349 --Item is serial controlled
2350 IF(l_is_ib_trackable = 'Y') THEN
2351 --Derive the serial number from the instance, which may have undergone serial number changes.
2352 l_trans_serial_number := l_IB_subtrns_inst_rec.serial_number;
2353 ELSE
2354 IF(l_osp_line_id is NULL) THEN
2355 --For spare parts use the lot serial record
2356 l_trans_serial_number := l_oe_lot_serial_rec.serial_number;
2357 ELSE
2358 --For non-IB tracked serialized items use the serial_number from the osp order line
2359 l_trans_serial_number := l_osp_order_line_rec.serial_number;
2360 END IF;
2361 END IF;--IF(l_is_ib_trackable = 'Y')
2362 ELSIF(l_osp_order_type = AHL_OSP_ORDERS_PVT.G_OSP_ORDER_TYPE_EXCHANGE) THEN
2363 IF(l_osp_line_id is NULL) THEN
2364 l_trans_serial_number := l_oe_lot_serial_rec.serial_number;
2365 ELSE
2366 l_trans_serial_number := p_rma_receipt_rec.exchange_serial_number;
2367 END IF;
2368 END IF;--IF(l_osp_order_type = 'SERVICE')
2369 END IF;--IF (l_serial_control_code IN (2,5,6))
2370
2371 IF (l_lot_control_code = 2) THEN
2372 IF(l_osp_order_type = AHL_OSP_ORDERS_PVT.G_OSP_ORDER_TYPE_SERVICE) THEN
2373 --Item is serial controlled
2374 IF(l_is_ib_trackable = 'Y') THEN
2375 --Derive the serial number from the instance, which may have undergone serial number changes.
2376 l_trans_lot_number := l_IB_subtrns_inst_rec.lot_number;
2377 ELSE
2378 IF(l_osp_line_id is NULL) THEN
2379 --For spare parts use the lot serial record
2380 l_trans_lot_number := l_oe_lot_serial_rec.lot_number;
2381 ELSE
2382 --For non-IB tracked lot controlled items use the serial_number from the osp order line
2383 l_trans_lot_number := l_osp_order_line_rec.lot_number;
2384 END IF;
2385 END IF;--IF(l_is_ib_trackable = 'Y')
2386 ELSIF(l_osp_order_type = AHL_OSP_ORDERS_PVT.G_OSP_ORDER_TYPE_EXCHANGE) THEN
2387 IF(l_osp_line_id is NULL) THEN
2388 l_trans_lot_number := l_oe_lot_serial_rec.lot_number;
2389 ELSE
2390 l_trans_lot_number := p_rma_receipt_rec.exchange_lot_number;
2391 END IF;
2392 END IF;--IF(l_osp_order_type = 'SERVICE')
2393 END IF;--IF (l_lot_control_code = 1)
2394
2395 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2396 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_trans_serial_number:'||l_trans_serial_number);
2397 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_trans_lot_number:'||l_trans_lot_number);
2398 END IF;
2399
2400 IF(l_trans_lot_number is not null) THEN
2401 INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
2402 (
2403 TRANSACTION_INTERFACE_ID,
2404 LAST_UPDATE_DATE,
2405 LAST_UPDATED_BY,
2406 CREATION_DATE,
2407 CREATED_BY,
2408 LAST_UPDATE_LOGIN,
2409 LOT_NUMBER,
2410 TRANSACTION_QUANTITY,
2411 PRIMARY_QUANTITY,
2412 PRODUCT_CODE,
2413 PRODUCT_TRANSACTION_ID
2414 )
2415 VALUES
2416 (
2417 l_mtl_transaction_id, --TRANSACTION_INTERFACE_ID,
2418 SYSDATE, --LAST_UPDATE_DATE,
2419 FND_GLOBAL.USER_ID, --LAST_UPDATED_BY,
2420 SYSDATE, --CREATION_DATE,
2421 FND_GLOBAL.USER_ID, --CREATED_BY,
2422 FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN,
2423 l_trans_lot_number, --LOT_NUMBER,
2424 p_rma_receipt_rec.RECEIPT_QUANTITY, --TRANSACTION_QUANTITY
2425 p_rma_receipt_rec.RECEIPT_QUANTITY, --PRIMARY_QUANTITY
2426 'RCV', --PRODUCT_CODE,
2427 l_intf_transaction_id --PRODUCT_TRANSACTION_ID
2428 );
2429 END IF;
2430
2431
2432 IF(l_trans_serial_number is not null) THEN
2433 INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE
2434 (
2435 TRANSACTION_INTERFACE_ID,
2436 LAST_UPDATE_DATE,
2437 LAST_UPDATED_BY,
2438 CREATION_DATE,
2439 CREATED_BY,
2440 LAST_UPDATE_LOGIN,
2441 FM_SERIAL_NUMBER,
2442 TO_SERIAL_NUMBER,
2443 PRODUCT_CODE,
2444 PRODUCT_TRANSACTION_ID
2445 )
2446 VALUES
2447 (
2448 l_mtl_transaction_id, --TRANSACTION_INTERFACE_ID,
2449 SYSDATE, --LAST_UPDATE_DATE,
2450 FND_GLOBAL.USER_ID, --LAST_UPDATED_BY,
2451 SYSDATE, --CREATION_DATE,
2452 FND_GLOBAL.USER_ID, --CREATED_BY,
2453 FND_GLOBAL.LOGIN_ID, --LAST_UPDATE_LOGIN,
2454 l_trans_serial_number, --FM_SERIAL_NUMBER,
2455 l_trans_serial_number, --TO_SERIAL_NUMBER,
2456 'RCV', --PRODUCT_CODE,
2457 l_intf_transaction_id --PRODUCT_TRANSACTION_ID
2458 );
2459 END IF;
2460
2461 l_curr_org_id := MO_GLOBAL.get_current_org_id();
2462 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2463 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'l_curr_org_id:'||l_curr_org_id);
2464 END IF;
2465 FND_REQUEST.SET_ORG_ID(l_curr_org_id);
2466 --Invoke the 'Receiving Transaction Processor' Concurrent Program
2467 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
2468 application => 'PO',
2469 program => 'RVCTP',
2470 --Modified by mpothuku on 04-Mar-2007 for the Bug 6862891
2471 argument1 => 'BATCH', -- mode
2472 argument2 => l_group_id, -- group_id
2473 argument3 => l_curr_org_id -- Operating Unit (Vision Project Manufacturing USD)
2474 );
2475
2476 IF (l_request_id = 0) THEN
2477 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2478 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
2479 'Concurrent request failed.');
2480 END IF;
2481 ELSE
2482 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2483 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
2484 'Concurrent request successful. Request id: '||l_request_id);
2485 END IF;
2486 END IF;
2487
2488 -- Set the OUT parameter x_request_id with l_req_id.
2489 x_request_id := l_request_id;
2490
2491 -- Standard call to get message count and initialise the OUT parameters.
2492 FND_MSG_PUB.Count_And_Get
2493 ( p_count => x_msg_count,
2494 p_data => x_msg_data,
2495 p_encoded => FND_API.G_FALSE
2496 );
2497
2498 -- Commit work if p_commit is TRUE.
2499 IF FND_API.TO_BOOLEAN(p_commit) THEN
2500 COMMIT WORK;
2501 END IF;
2502
2503 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
2504 FND_LOG.string(FND_LOG.level_procedure, l_debug_key || '.end', 'End of the API.'||
2505 ' x_request_id: '||x_request_id||
2506 ', x_return_line_id: '||x_return_line_id);
2507 END IF;
2508 EXCEPTION
2509 WHEN FND_API.G_EXC_ERROR THEN
2510 ROLLBACK TO Receive_Against_RMA_Pvt;
2511 x_return_status := FND_API.G_RET_STS_ERROR;
2512 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2513 p_data => x_msg_data,
2514 p_encoded => FND_API.G_FALSE);
2515
2516 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2517 ROLLBACK TO Receive_Against_RMA_Pvt;
2518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2519 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2520 p_data => x_msg_data,
2521 p_encoded => FND_API.G_FALSE);
2522
2523 WHEN OTHERS THEN
2524 ROLLBACK TO Receive_Against_RMA_Pvt;
2525 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2526 FND_MSG_PUB.Add_Exc_Msg( p_pkg_name => G_PKG_NAME,
2527 p_procedure_name => l_api_name,
2528 p_error_text => SQLERRM);
2529
2530 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2531 p_data => x_msg_data,
2532 p_encoded => FND_API.G_FALSE);
2533 END Receive_Against_RMA;
2534
2535
2536 -- Start of Comments --
2537 -- Procedure name : Validate_Receiving_Params
2538 -- Type : Private
2539 -- Functionality : Local helper procedure to validate user entered values. In case of any invalid values,
2540 -- this API will raise an appropriate exception which will be handled by the calling API.
2541 -- Pre-reqs :
2542 --
2543 -- Parameters:
2544 --
2545 -- Validate_Receiving_Params Parameters:
2546 -- p_rma_receipt_rec IN RMA_Receipt_Rec_Type RMA receipt record
2547 --
2548 -- Version:
2549 --
2550 -- Initial Version 1.0
2551 --
2552 -- End of Comments --
2553
2554 PROCEDURE Validate_Receiving_Params (
2555 p_rma_receipt_rec IN RMA_Receipt_Rec_Type
2556 ) IS
2557
2558 -- Cursor to check whether the given return line is valid or not.
2559 CURSOR chk_return_line (c_oe_line_id NUMBER) IS
2560 SELECT 'X'
2561 FROM OE_ORDER_LINES_ALL
2562 WHERE line_id = c_oe_line_id
2563 AND line_type_id = FND_PROFILE.VALUE('AHL_OSP_OE_RETURN_ID');
2564
2565 -- Cursor to check whether the shipment is booked or not.
2566 CURSOR chk_shipment_booked (c_oe_line_id NUMBER) IS
2567 SELECT OHA.header_id
2568 FROM OE_ORDER_LINES_ALL OLA, OE_ORDER_HEADERS_ALL OHA
2569 WHERE OLA.line_id = c_oe_line_id
2570 AND OHA.header_id = OLA.header_id
2571 AND OHA.booked_flag = 'Y';
2572
2573 -- Cursor to get the ship line id of the OSP order lines, that correspond to the given return line.
2574 CURSOR get_osp_ship_line_id (c_oe_return_line_id NUMBER) IS
2575 SELECT oe_ship_line_id
2576 FROM AHL_OSP_ORDER_LINES
2577 WHERE oe_return_line_id = c_oe_return_line_id
2578 AND ROWNUM = 1;
2579
2580 -- Cursor to get the ordered and shipped quantities of the given return line.
2581 CURSOR get_oe_quantities (c_oe_line_id NUMBER) IS
2582 SELECT ordered_quantity, shipped_quantity
2583 FROM OE_ORDER_LINES_ALL
2584 WHERE line_id = c_oe_line_id;
2585
2586 -- Cursor to check that the 'ship from org id' is same as the 'receiving org id' for a given return line.
2587 CURSOR chk_org_id (c_oe_line_id NUMBER, c_rcv_org_id NUMBER) IS
2588 SELECT 'X'
2589 FROM OE_ORDER_LINES_ALL
2590 WHERE line_id = c_oe_line_id
2591 AND NVL(ship_from_org_id, -1) = c_rcv_org_id;
2592
2593 --
2594 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Receiving_Params';
2595 l_debug_key CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
2596
2597 l_oe_header_id OE_ORDER_HEADERS_ALL.header_id%TYPE;
2598 l_oe_ship_line_id AHL_OSP_ORDER_LINES.oe_ship_line_id%TYPE;
2599 l_oe_ordered_qty OE_ORDER_LINES_ALL.ordered_quantity%TYPE;
2600 l_oe_shipped_qty OE_ORDER_LINES_ALL.shipped_quantity%TYPE;
2601 l_valid_flag BOOLEAN := TRUE;
2602 l_part_num_change_flag BOOLEAN := FALSE;
2603 l_exchange_flag BOOLEAN := FALSE;
2604 l_dummy VARCHAR2(1);
2605 l_ship_line_qty_rec get_oe_quantities%ROWTYPE;
2606 l_return_line_qty_rec get_oe_quantities%ROWTYPE;
2607 --
2608
2609 BEGIN
2610 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
2611 FND_LOG.string(FND_LOG.level_procedure, l_debug_key||'.begin', 'Start of the API.');
2612 END IF;
2613
2614 -- Check for necessary/mandatory fields.
2615 IF (p_rma_receipt_rec.return_line_id IS NULL OR p_rma_receipt_rec.receiving_org_id IS NULL OR
2616 p_rma_receipt_rec.receiving_subinventory IS NULL OR p_rma_receipt_rec.receipt_quantity IS NULL OR
2617 p_rma_receipt_rec.receipt_uom_code IS NULL OR p_rma_receipt_rec.receipt_date IS NULL) THEN
2618 -- Add an error message to the FND stack.
2619 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2620 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
2621 'Mandatory fields have not been entered.');
2622 END IF;
2623
2624 FND_MESSAGE.set_name('AHL', 'AHL_COM_REQD_PARAM_MISSING'); -- Required parameter is missing.
2625 FND_MSG_PUB.ADD;
2626
2627 -- Set the l_valid_flag as FALSE.
2628 l_valid_flag := FALSE;
2629 END IF;
2630
2631 -- Check for part number change attributes.
2632 IF (p_rma_receipt_rec.new_item_id IS NOT NULL OR p_rma_receipt_rec.new_serial_number IS NOT NULL) THEN
2633 -- Set the l_part_num_change_flag.
2634 l_part_num_change_flag := TRUE;
2635 END IF;
2636
2637 -- Check for exchange attributes.
2638 IF (p_rma_receipt_rec.exchange_item_id IS NOT NULL OR p_rma_receipt_rec.exchange_serial_number IS NOT NULL OR
2639 p_rma_receipt_rec.exchange_lot_number IS NOT NULL) THEN
2640 -- Set the l_exchange_flag.
2641 l_exchange_flag := TRUE;
2642 END IF;
2643
2644 -- Check for the flags l_part_num_change_flag and l_exchange_flag.
2645 IF (l_part_num_change_flag AND l_exchange_flag) THEN
2646 -- Add an error message to the FND stack as part number change and exchange cannot be done together.
2647 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2648 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
2649 'Part number change and exchange cannot be done together.');
2650 END IF;
2651
2652 FND_MESSAGE.set_name('AHL', 'AHL_OSP_NO_PN_CHG_EXCHG_TGTHR'); -- Part number change and Exchange cannot be done together.
2653 FND_MSG_PUB.ADD;
2654
2655 -- Set the l_valid_flag as FALSE.
2656 l_valid_flag := FALSE;
2657 END IF;
2658
2659 -- Check whether the return line id is valid or not.
2660 OPEN chk_return_line(p_rma_receipt_rec.return_line_id);
2661 FETCH chk_return_line INTO l_dummy;
2662 IF (chk_return_line%NOTFOUND) THEN
2663 -- Add an error message as the given return line is invalid.
2664 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2665 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
2666 'Return line: '||p_rma_receipt_rec.return_line_id||' is invalid.');
2667 END IF;
2668
2669 FND_MESSAGE.set_name('AHL', 'AHL_OSP_RMA_LINE_INVALID'); -- Return line is invalid.
2670 FND_MSG_PUB.ADD;
2671
2672 -- Set the l_valid_flag as FALSE.
2673 l_valid_flag := FALSE;
2674 END IF;
2675 CLOSE chk_return_line;
2676
2677 -- Check whether the shipment is booked or not.
2678 OPEN chk_shipment_booked(p_rma_receipt_rec.return_line_id);
2679 FETCH chk_shipment_booked INTO l_oe_header_id;
2680 IF (chk_shipment_booked%NOTFOUND) THEN
2681 -- Add an error message as the shipment is not booked.
2682 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2683 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
2684 'Shipment order: '||l_oe_header_id||' is not booked.');
2685 END IF;
2686
2687 FND_MESSAGE.set_name('AHL', 'AHL_OSP_RMA_SHPMNT_NOT_BKD'); -- Shipment is not booked.
2688 FND_MSG_PUB.ADD;
2689
2690 -- Set the l_valid_flag as FALSE.
2691 l_valid_flag := FALSE;
2692 END IF;
2693 CLOSE chk_shipment_booked;
2694
2695
2696 -- Get the ship line id of the OSP order lines, that correspond to the given return line.
2697 OPEN get_osp_ship_line_id(p_rma_receipt_rec.return_line_id);
2698 FETCH get_osp_ship_line_id INTO l_oe_ship_line_id;
2699 IF (get_osp_ship_line_id%FOUND) THEN
2700 -- Add an error message if the ship line id is NULL.
2701 IF (l_oe_ship_line_id IS NULL) THEN
2702 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2703 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
2704 'Ship line id for the return line: '||p_rma_receipt_rec.return_line_id||' is NULL.');
2705 END IF;
2706
2707 FND_MESSAGE.set_name('AHL', 'AHL_OSP_SHIP_LINE_NULL'); -- Shipment line does not exist for this return line.
2708 FND_MSG_PUB.ADD;
2709
2710 -- Set the l_valid_flag as FALSE.
2711 l_valid_flag := FALSE;
2712 ELSE
2713 -- Get the ordered and shipped quantities of the ship line.
2714 OPEN get_oe_quantities(l_oe_ship_line_id);
2715 FETCH get_oe_quantities INTO l_ship_line_qty_rec;
2716 CLOSE get_oe_quantities;
2717
2718 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2719 FND_LOG.string(FND_LOG.level_statement, l_debug_key, 'l_oe_ship_line_id: '||l_oe_ship_line_id);
2720 FND_LOG.string(FND_LOG.level_statement, l_debug_key, 'l_ship_line_qty_rec.shipped_quantity: '||l_ship_line_qty_rec.shipped_quantity);
2721 END IF;
2722
2723 -- Shipment should have been done for any receipt to take place. For this, check the shipped quantity.
2724 -- If the shipped quantity is NULL or zero, it means shipment hasn't been done yet. Add an error message.
2725 IF (l_ship_line_qty_rec.shipped_quantity IS NULL OR l_ship_line_qty_rec.shipped_quantity = 0) THEN
2726 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2727 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
2728 'Shipment for the return line: '||p_rma_receipt_rec.return_line_id||' has not been done yet.');
2729 END IF;
2730
2731 FND_MESSAGE.set_name('AHL', 'AHL_OSP_SHIPMENT_NOT_DONE'); -- Shipping has not been done for the ship line corresponding to this return line.
2732 FND_MSG_PUB.ADD;
2733
2734 -- Set the l_valid_flag as FALSE.
2735 l_valid_flag := FALSE;
2736 END IF;
2737 END IF;
2738 END IF;
2739 CLOSE get_osp_ship_line_id;
2740
2741 -- Get the ordered and shipped quantities of the given return line.
2742 OPEN get_oe_quantities(p_rma_receipt_rec.return_line_id);
2743 FETCH get_oe_quantities INTO l_return_line_qty_rec;
2744 CLOSE get_oe_quantities;
2745
2746 -- Check for the ordered and shipped quantities of the given return line. If the shipped quantity is not less than the
2747 -- ordered quantity, then it means there is no quantity left to be returned. Add an error message.
2748 IF NOT (l_return_line_qty_rec.shipped_quantity < l_return_line_qty_rec.ordered_quantity) THEN
2749 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2750 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
2751 'Shipped qty is not less than the ordered qty for the return line: '||p_rma_receipt_rec.return_line_id);
2752 END IF;
2753
2754 FND_MESSAGE.set_name('AHL', 'AHL_OSP_RECEIPT_CMPLT'); -- Receipt is complete for this return line.
2755 FND_MSG_PUB.ADD;
2756
2757 -- Set the l_valid_flag as FALSE.
2758 l_valid_flag := FALSE;
2759 END IF;
2760
2761 -- Check for the receipt date. If it is in future, add an error message.
2762 IF (TRUNC(p_rma_receipt_rec.receipt_date) > TRUNC(SYSDATE)) THEN
2763 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2764 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
2765 'Receipt date: '||p_rma_receipt_rec.receipt_date||' is in future.');
2766 END IF;
2767
2768 FND_MESSAGE.set_name('AHL', 'AHL_OSP_RECEIPT_DATE_INVALID'); -- Receipt cannot be done for a future date.
2769 FND_MSG_PUB.ADD;
2770
2771 -- Set the l_valid_flag as FALSE.
2772 l_valid_flag := FALSE;
2773 END IF;
2774
2775 -- Check that the 'ship from org id' is same as the 'receiving org id' for a given return line.
2776 OPEN chk_org_id(p_rma_receipt_rec.return_line_id, p_rma_receipt_rec.receiving_org_id);
2777 FETCH chk_org_id INTO l_dummy;
2778 IF (chk_org_id%NOTFOUND) THEN
2779 -- Add an error message.
2780 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2781 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
2782 'Receipt org is different from the one set in the return line.');
2783 END IF;
2784
2785 FND_MESSAGE.set_name('AHL', 'AHL_OSP_RECEIPT_ORG_INVALID'); -- Receiving organization is different from the one set for the return line.
2786 FND_MSG_PUB.ADD;
2787
2788 -- Set the l_valid_flag as FALSE.
2789 l_valid_flag := FALSE;
2790 END IF;
2791 CLOSE chk_org_id;
2792
2793 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
2794 IF (l_valid_flag) THEN
2795 FND_LOG.string(FND_LOG.level_procedure, l_debug_key||'.end', 'End of the API.'||
2796 ' l_valid_flag is TRUE, no exception raised.');
2797 ELSE
2798 FND_LOG.string(FND_LOG.level_procedure, l_debug_key||'.end', 'End of the API.'||
2799 ' l_valid_flag is FALSE, exception raised.');
2800 END IF;
2801 END IF;
2802
2803 -- Check l_valid_flag. If FALSE, raise an exception which will be handled in the calling API.
2804 IF (NOT l_valid_flag) THEN
2805 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2806 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
2807 'As l_valid_flag is FALSE, raising an exception.');
2808 END IF;
2809
2810 RAISE FND_API.G_EXC_ERROR;
2811 END IF;
2812 END Validate_Receiving_Params;
2813
2814
2815 -- Procedure name : Update_OSP_Line_Exch_Instance
2816 -- Type : Private
2817 -- Functionality : Local helper procedure to update exchange instance of the osp order lines
2818 -- Pre-reqs :
2819
2820 PROCEDURE Update_OSP_Line_Exch_Instance(
2821 p_osp_order_id IN NUMBER,
2822 p_osp_line_id IN NUMBER,
2823 p_exchange_instance_id IN NUMBER
2824 )IS
2825
2826
2827 -- Check if the instance is a valid IB instance
2828 -- Also not part of relationship
2829
2830 CURSOR val_exg_instance_id_csr(p_instance_id IN NUMBER) IS
2831 SELECT 'x' FROM csi_item_instances csi
2832 WHERE instance_id = p_instance_id
2833 AND nvl(csi.active_end_date, sysdate + 1) > sysdate
2834 AND NOT EXISTS
2835 (select subject_id
2836 from csi_ii_relationships
2837 where subject_id = p_instance_id
2838 and relationship_type_code = 'COMPONENT-OF'
2839 and NVL(ACTIVE_START_DATE, SYSDATE - 1) < SYSDATE
2840 AND NVL(ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE) ;
2841
2842 l_exist VARCHAR2(1);
2843 -- retrieve order line details
2844
2845 CURSOR ahl_osp_lines_csr(p_osp_id IN NUMBER, p_osp_line_id IN NUMBER) IS
2846
2847 SELECT a.OSP_ORDER_LINE_ID,
2848 a.OBJECT_VERSION_NUMBER,
2849 a.LAST_UPDATE_DATE,
2850 a.LAST_UPDATED_BY,
2851 a.LAST_UPDATE_LOGIN,
2852 a.OSP_ORDER_ID,
2853 a.OSP_LINE_NUMBER,
2854 a.STATUS_CODE,
2855 a.PO_LINE_TYPE_ID,
2856 a.SERVICE_ITEM_ID,
2857 a.SERVICE_ITEM_DESCRIPTION,
2858 a.SERVICE_ITEM_UOM_CODE,
2859 a.NEED_BY_DATE,
2860 a.SHIP_BY_DATE,
2861 a.PO_LINE_ID,
2862 a.OE_SHIP_LINE_ID,
2863 a.OE_RETURN_LINE_ID,
2864 a.WORKORDER_ID,
2865 a.OPERATION_ID,
2866 a.EXCHANGE_INSTANCE_ID,
2867 a.INVENTORY_ITEM_ID,
2868 a.INVENTORY_ORG_ID,
2869 a.SERIAL_NUMBER,
2870 a.LOT_NUMBER,
2871 a.INVENTORY_ITEM_UOM,
2872 a.INVENTORY_ITEM_QUANTITY,
2873 a.SUB_INVENTORY,
2874 a.QUANTITY,
2875 a.ATTRIBUTE_CATEGORY,
2876 a.ATTRIBUTE1,
2877 a.ATTRIBUTE2,
2878 a.ATTRIBUTE3,
2879 a.ATTRIBUTE4,
2880 a.ATTRIBUTE5,
2881 a.ATTRIBUTE6,
2882 a.ATTRIBUTE7,
2883 a.ATTRIBUTE8,
2884 a.ATTRIBUTE9,
2885 a.ATTRIBUTE10,
2886 a.ATTRIBUTE11,
2887 a.ATTRIBUTE12,
2888 a.ATTRIBUTE13,
2889 a.ATTRIBUTE14,
2890 a.ATTRIBUTE15,
2891 a.PO_REQ_LINE_ID
2892 FROM AHL_OSP_ORDER_LINES a
2893 WHERE a.osp_order_id = p_osp_id
2894 AND a.osp_order_line_id = p_osp_line_id;
2895
2896 --
2897 l_row_check VARCHAR2(1):='N';
2898 --
2899 BEGIN
2900
2901 -- Validate exchange instance
2902 IF(p_exchange_instance_id is not null) THEN
2903 OPEN val_exg_instance_id_csr(p_exchange_instance_id);
2904 FETCH val_exg_instance_id_csr INTO l_exist;
2905 IF (val_exg_instance_id_csr %NOTFOUND) THEN
2906 FND_MESSAGE.Set_Name('AHL','AHL_OSP_SHIP_COMPONENT');
2907 FND_MSG_PUB.ADD;
2908 CLOSE val_exg_instance_id_csr;
2909 RAISE Fnd_Api.g_exc_error;
2910 END IF;
2911 CLOSE val_exg_instance_id_csr;
2912 END IF;
2913
2914 FOR l_osp_line_rec IN ahl_osp_lines_csr(p_osp_order_id, p_osp_line_id)
2915 LOOP
2916 l_row_check := 'Y';
2917
2918 AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (
2919 P_OSP_ORDER_LINE_ID => l_osp_line_rec.OSP_ORDER_LINE_ID,
2920 P_OBJECT_VERSION_NUMBER => l_osp_line_rec.OBJECT_VERSION_NUMBER+1,
2921 P_LAST_UPDATE_DATE => l_osp_line_rec.LAST_UPDATE_DATE,
2922 P_LAST_UPDATED_BY => l_osp_line_rec.LAST_UPDATED_BY,
2923 P_LAST_UPDATE_LOGIN => l_osp_line_rec.LAST_UPDATE_LOGIN,
2924 P_OSP_ORDER_ID => l_osp_line_rec.OSP_ORDER_ID,
2925 P_OSP_LINE_NUMBER => l_osp_line_rec.OSP_LINE_NUMBER,
2926 P_STATUS_CODE => l_osp_line_rec.STATUS_CODE,
2927 P_PO_LINE_TYPE_ID => l_osp_line_rec.PO_LINE_TYPE_ID,
2928 P_SERVICE_ITEM_ID => l_osp_line_rec.SERVICE_ITEM_ID,
2929 P_SERVICE_ITEM_DESCRIPTION => l_osp_line_rec.SERVICE_ITEM_DESCRIPTION,
2930 P_SERVICE_ITEM_UOM_CODE => l_osp_line_rec.SERVICE_ITEM_UOM_CODE,
2931 P_NEED_BY_DATE => l_osp_line_rec.NEED_BY_DATE,
2932 P_SHIP_BY_DATE => l_osp_line_rec.SHIP_BY_DATE,
2933 P_PO_LINE_ID => l_osp_line_rec.PO_LINE_ID,
2934 P_OE_SHIP_LINE_ID => l_osp_line_rec.OE_SHIP_LINE_ID,
2935 P_OE_RETURN_LINE_ID => l_osp_line_rec.OE_RETURN_LINE_ID,
2936 P_WORKORDER_ID => l_osp_line_rec.WORKORDER_ID,
2937 P_OPERATION_ID => l_osp_line_rec.OPERATION_ID,
2938 P_QUANTITY => l_osp_line_rec.QUANTITY,
2939 P_EXCHANGE_INSTANCE_ID => p_exchange_instance_id,
2940 P_INVENTORY_ITEM_ID => l_osp_line_rec.INVENTORY_ITEM_ID,
2941 P_INVENTORY_ORG_ID => l_osp_line_rec.INVENTORY_ORG_ID,
2942 P_INVENTORY_ITEM_UOM => l_osp_line_rec.INVENTORY_ITEM_UOM,
2943 P_INVENTORY_ITEM_QUANTITY => l_osp_line_rec.INVENTORY_ITEM_QUANTITY,
2944 P_SUB_INVENTORY => l_osp_line_rec.SUB_INVENTORY,
2945 P_LOT_NUMBER => l_osp_line_rec.LOT_NUMBER,
2946 P_SERIAL_NUMBER => l_osp_line_rec.SERIAL_NUMBER,
2947 P_PO_REQ_LINE_ID => l_osp_line_rec.PO_REQ_LINE_ID,
2948 P_ATTRIBUTE_CATEGORY => l_osp_line_rec.ATTRIBUTE_CATEGORY,
2949 P_ATTRIBUTE1 => l_osp_line_rec.ATTRIBUTE1,
2950 P_ATTRIBUTE2 => l_osp_line_rec.ATTRIBUTE2,
2951 P_ATTRIBUTE3 => l_osp_line_rec.ATTRIBUTE3,
2952 P_ATTRIBUTE4 => l_osp_line_rec.ATTRIBUTE4,
2953 P_ATTRIBUTE5 => l_osp_line_rec.ATTRIBUTE5,
2954 P_ATTRIBUTE6 => l_osp_line_rec.ATTRIBUTE6,
2955 P_ATTRIBUTE7 => l_osp_line_rec.ATTRIBUTE7,
2956 P_ATTRIBUTE8 => l_osp_line_rec.ATTRIBUTE8,
2957 P_ATTRIBUTE9 => l_osp_line_rec.ATTRIBUTE9,
2958 P_ATTRIBUTE10 => l_osp_line_rec.ATTRIBUTE10,
2959 P_ATTRIBUTE11 => l_osp_line_rec.ATTRIBUTE11,
2960 P_ATTRIBUTE12 => l_osp_line_rec.ATTRIBUTE12,
2961 P_ATTRIBUTE13 => l_osp_line_rec.ATTRIBUTE13,
2962 P_ATTRIBUTE14 => l_osp_line_rec.ATTRIBUTE14,
2963 P_ATTRIBUTE15 => l_osp_line_rec.ATTRIBUTE15 );
2964 END LOOP;
2965
2966 IF l_row_check = 'N' THEN
2967 Fnd_Message.set_name('AHL', 'AHL_OSP_INVALID_LINE_ITEM');
2968 Fnd_Msg_Pub.ADD;
2969 RAISE Fnd_Api.g_exc_error;
2970 END IF;
2971
2972 END Update_OSP_Line_Exch_Instance;
2973
2974
2975 -- Procedure name : Update_OSP_Order_Lines
2976 -- Type : Private
2977 -- Functionality : Local helper procedure to update shipment line id and return line id of the osp order lines
2978 -- Pre-reqs :
2979
2980 PROCEDURE Update_OSP_Order_Lines(
2981 p_osp_order_id IN NUMBER,
2982 p_osp_line_id IN NUMBER,
2983 p_oe_ship_line_id IN NUMBER,
2984 p_oe_return_line_id IN NUMBER
2985 ) IS
2986 --
2987 CURSOR ahl_osp_lines_csr(p_osp_id IN NUMBER, p_osp_line_id IN NUMBER) IS
2988 SELECT a.OSP_ORDER_LINE_ID,
2989 a.OBJECT_VERSION_NUMBER,
2990 a.LAST_UPDATE_DATE,
2991 a.LAST_UPDATED_BY,
2992 a.LAST_UPDATE_LOGIN,
2993 a.OSP_ORDER_ID,
2994 a.OSP_LINE_NUMBER,
2995 a.STATUS_CODE,
2996 a.PO_LINE_TYPE_ID,
2997 a.SERVICE_ITEM_ID,
2998 a.SERVICE_ITEM_DESCRIPTION,
2999 a.SERVICE_ITEM_UOM_CODE,
3000 a.NEED_BY_DATE,
3001 a.SHIP_BY_DATE,
3002 a.PO_LINE_ID,
3003 a.OE_SHIP_LINE_ID,
3004 a.OE_RETURN_LINE_ID,
3005 a.WORKORDER_ID,
3006 a.OPERATION_ID,
3007 a.EXCHANGE_INSTANCE_ID,
3008 a.INVENTORY_ITEM_ID,
3009 a.INVENTORY_ORG_ID,
3010 a.SERIAL_NUMBER,
3011 a.LOT_NUMBER,
3012 a.INVENTORY_ITEM_UOM,
3013 a.INVENTORY_ITEM_QUANTITY,
3014 a.SUB_INVENTORY,
3015 a.QUANTITY,
3016 a.ATTRIBUTE_CATEGORY,
3017 a.ATTRIBUTE1,
3018 a.ATTRIBUTE2,
3019 a.ATTRIBUTE3,
3020 a.ATTRIBUTE4,
3021 a.ATTRIBUTE5,
3022 a.ATTRIBUTE6,
3023 a.ATTRIBUTE7,
3024 a.ATTRIBUTE8,
3025 a.ATTRIBUTE9,
3026 a.ATTRIBUTE10,
3027 a.ATTRIBUTE11,
3028 a.ATTRIBUTE12,
3029 a.ATTRIBUTE13,
3030 a.ATTRIBUTE14,
3031 a.ATTRIBUTE15,
3032 a.PO_REQ_LINE_ID
3033 FROM AHL_OSP_ORDER_LINES a
3034 WHERE a.osp_order_id = p_osp_id
3035 AND a.osp_order_line_id = p_osp_line_id;
3036 --
3037 l_oe_ship_line_id NUMBER;
3038 l_oe_return_line_id NUMBER;
3039 l_row_check VARCHAR2(1):='N';
3040 --
3041 BEGIN
3042
3043 FOR l_osp_line_rec IN ahl_osp_lines_csr(p_osp_order_id, p_osp_line_id)
3044 LOOP
3045 l_row_check := 'Y';
3046 IF ( p_oe_ship_line_id IS NOT NULL
3047 AND p_oe_ship_line_id <> FND_API.G_MISS_NUM) THEN
3048 l_oe_ship_line_id := p_oe_ship_line_id;
3049 ELSE
3050 l_oe_ship_line_id := l_osp_line_rec.oe_ship_line_id;
3051 END IF;
3052
3053 IF (p_oe_return_line_id IS NOT NULL
3054 AND p_oe_return_line_id <> FND_API.G_MISS_NUM) THEN
3055 l_oe_return_line_id := p_oe_return_line_id;
3056 ELSE
3057 l_oe_return_line_id := l_osp_line_rec.oe_return_line_id;
3058 END IF;
3059
3060 AHL_OSP_ORDER_LINES_PKG.UPDATE_ROW (
3061 P_OSP_ORDER_LINE_ID => l_osp_line_rec.OSP_ORDER_LINE_ID,
3062 P_OBJECT_VERSION_NUMBER => l_osp_line_rec.OBJECT_VERSION_NUMBER+1,
3063 P_LAST_UPDATE_DATE => l_osp_line_rec.LAST_UPDATE_DATE,
3064 P_LAST_UPDATED_BY => l_osp_line_rec.LAST_UPDATED_BY,
3065 P_LAST_UPDATE_LOGIN => l_osp_line_rec.LAST_UPDATE_LOGIN,
3066 P_OSP_ORDER_ID => l_osp_line_rec.OSP_ORDER_ID,
3067 P_OSP_LINE_NUMBER => l_osp_line_rec.OSP_LINE_NUMBER,
3068 P_STATUS_CODE => l_osp_line_rec.STATUS_CODE,
3069 P_PO_LINE_TYPE_ID => l_osp_line_rec.PO_LINE_TYPE_ID,
3070 P_SERVICE_ITEM_ID => l_osp_line_rec.SERVICE_ITEM_ID,
3071 P_SERVICE_ITEM_DESCRIPTION => l_osp_line_rec.SERVICE_ITEM_DESCRIPTION,
3072 P_SERVICE_ITEM_UOM_CODE => l_osp_line_rec.SERVICE_ITEM_UOM_CODE,
3073 P_NEED_BY_DATE => l_osp_line_rec.NEED_BY_DATE,
3074 P_SHIP_BY_DATE => l_osp_line_rec.SHIP_BY_DATE,
3075 P_PO_LINE_ID => l_osp_line_rec.PO_LINE_ID,
3076 P_OE_SHIP_LINE_ID => l_oe_ship_line_id,
3077 P_OE_RETURN_LINE_ID => l_oe_return_line_id,
3078 P_WORKORDER_ID => l_osp_line_rec.WORKORDER_ID,
3079 P_OPERATION_ID => l_osp_line_rec.OPERATION_ID,
3080 P_QUANTITY => l_osp_line_rec.QUANTITY,
3081 P_EXCHANGE_INSTANCE_ID => l_osp_line_rec.EXCHANGE_INSTANCE_ID,
3082 P_INVENTORY_ITEM_ID => l_osp_line_rec.INVENTORY_ITEM_ID,
3083 P_INVENTORY_ORG_ID => l_osp_line_rec.INVENTORY_ORG_ID,
3084 P_INVENTORY_ITEM_UOM => l_osp_line_rec.INVENTORY_ITEM_UOM,
3085 P_INVENTORY_ITEM_QUANTITY => l_osp_line_rec.INVENTORY_ITEM_QUANTITY,
3086 P_SUB_INVENTORY => l_osp_line_rec.SUB_INVENTORY,
3087 P_LOT_NUMBER => l_osp_line_rec.LOT_NUMBER,
3088 P_SERIAL_NUMBER => l_osp_line_rec.SERIAL_NUMBER,
3089 P_PO_REQ_LINE_ID => l_osp_line_rec.PO_REQ_LINE_ID,
3090 P_ATTRIBUTE_CATEGORY => l_osp_line_rec.ATTRIBUTE_CATEGORY,
3091 P_ATTRIBUTE1 => l_osp_line_rec.ATTRIBUTE1,
3092 P_ATTRIBUTE2 => l_osp_line_rec.ATTRIBUTE2,
3093 P_ATTRIBUTE3 => l_osp_line_rec.ATTRIBUTE3,
3094 P_ATTRIBUTE4 => l_osp_line_rec.ATTRIBUTE4,
3095 P_ATTRIBUTE5 => l_osp_line_rec.ATTRIBUTE5,
3096 P_ATTRIBUTE6 => l_osp_line_rec.ATTRIBUTE6,
3097 P_ATTRIBUTE7 => l_osp_line_rec.ATTRIBUTE7,
3098 P_ATTRIBUTE8 => l_osp_line_rec.ATTRIBUTE8,
3099 P_ATTRIBUTE9 => l_osp_line_rec.ATTRIBUTE9,
3100 P_ATTRIBUTE10 => l_osp_line_rec.ATTRIBUTE10,
3101 P_ATTRIBUTE11 => l_osp_line_rec.ATTRIBUTE11,
3102 P_ATTRIBUTE12 => l_osp_line_rec.ATTRIBUTE12,
3103 P_ATTRIBUTE13 => l_osp_line_rec.ATTRIBUTE13,
3104 P_ATTRIBUTE14 => l_osp_line_rec.ATTRIBUTE14,
3105 P_ATTRIBUTE15 => l_osp_line_rec.ATTRIBUTE15 );
3106 END LOOP;
3107
3108 IF l_row_check = 'N' THEN
3109 Fnd_Message.set_name('AHL', 'AHL_OSP_INVALID_LINE_ITEM');
3110 Fnd_Msg_Pub.ADD;
3111 RAISE Fnd_Api.g_exc_error;
3112 END IF;
3113
3114 END Update_OSP_Order_Lines;
3115
3116 -- SALOGAN added validate_create_locator for 9496606 Start
3117 -- Start of Comments --
3118 -- Procedure name : validate_create_locator
3119 -- Type : Private
3120 -- Functionality : Local helper procedure to validate user entered locator values. In case of any invalid values,
3121 -- this API will raise an appropriate exception which will be handled by the calling API.
3122 -- If the receiving sub inventory is dynamic locator controlled, a new location is created if needed.
3123 -- Pre-reqs :
3124 --
3125 -- Parameters:
3126 --
3127 -- validate_create_locator Parameters:
3128 -- p_rma_receipt_rec IN RMA_Receipt_Rec_Type RMA receipt record
3129 -- x_receiving_locator_id OUT NOCOPY NUMBER Locator ID
3130 -- x_return_status OUT NOCOPY VARCHAR2
3131 -- x_msg_count OUT NOCOPY NUMBER
3132 -- x_msg_data OUT NOCOPY VARCHAR2
3133 --
3134 -- Version:
3135 --
3136 -- Initial Version 1.0
3137 --
3138 -- End of Comments --
3139
3140 PROCEDURE validate_create_locator (
3141 p_rma_receipt_rec IN RMA_Receipt_Rec_Type,
3142 x_receiving_locator_id OUT NOCOPY NUMBER,
3143 x_return_status OUT NOCOPY VARCHAR2,
3144 x_msg_count OUT NOCOPY NUMBER,
3145 x_msg_data OUT NOCOPY VARCHAR2)
3146 IS
3147
3148 -- Cursor to check whether the given locator number is valid or not.
3149 --jrotich modified cursor to use only mtl_item_locations_kfv for bug 14301820
3150 --change done on 09/02/2012
3151 /*cursor modified by jrotich on 11-FEB-2013 for bug 16304850.
3152 This change reverts the performance fix done on 09/02/2012. However,
3153 we will use the entire unique index on organization_id and inventory_location id to improve performance
3154 Cursor has been modified to use the mtl_item_locations table again */
3155
3156 CURSOR is_valid_locator (c_org_id NUMBER, c_locator_name VARCHAR2) IS
3157 SELECT MIL.INVENTORY_LOCATION_ID
3158 ,MIL_kfv.CONCATENATED_SEGMENTS
3159 ,MIL.SUBINVENTORY_CODE
3160 FROM MTL_ITEM_LOCATIONS_KFV MIL_kfv
3161 ,MTL_ITEM_LOCATIONS MIL
3162 WHERE MIL_kfv.INVENTORY_LOCATION_ID = MIL.INVENTORY_LOCATION_ID AND
3163 MIL_kfv.ORGANIZATION_ID = MIL.ORGANIZATION_ID AND
3164 MIL_kfv.ORGANIZATION_ID = c_org_id
3165 AND UPPER(DECODE(MIL.segment19, NULL, MIL_kfv.concatenated_segments, INV_PROJECT.GET_LOCSEGS(MIL_kfv.concatenated_segments)
3166 || fnd_flex_ext.get_delimiter('INV', 'MTLL', 101)
3167 || INV_ProjectLocator_PUB.get_project_number(MIL.segment19)
3168 || FND_FLEX_EXT.GET_DELIMITER('INV', 'MTLL', 101)
3169 || INV_ProjectLocator_PUB.get_task_number(MIL.segment20))) = UPPER(c_locator_name)
3170 AND ((MIL.START_DATE_ACTIVE IS NULL) OR (MIL.START_DATE_ACTIVE <= SYSDATE))
3171 AND ((MIL.END_DATE_ACTIVE IS NULL) OR (MIL.END_DATE_ACTIVE > SYSDATE));
3172
3173 CURSOR is_dyn_locator_sub_inv (c_org_id NUMBER, c_sub_inv VARCHAR2) IS
3174 SELECT 'X' FROM MTL_SECONDARY_INVENTORIES
3175 WHERE ORGANIZATION_ID = c_org_id
3176 AND SECONDARY_INVENTORY_NAME like c_sub_inv
3177 AND LOCATOR_TYPE = 3;
3178
3179 --
3180 l_api_name CONSTANT VARCHAR2(30) := 'validate_create_locator';
3181 l_debug_key CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
3182 l_receiving_locator_name VARCHAR2(240);
3183 l_locator_id NUMBER;
3184 l_sub_inv_code VARCHAR2(10);
3185 l_dummy VARCHAR2(1);
3186 --
3187
3188 BEGIN
3189 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
3190 FND_LOG.string(FND_LOG.level_procedure, l_debug_key||'.begin', 'Start of the API.');
3191 FND_LOG.STRING(FND_LOG.level_procedure, l_debug_key, 'RECEIVING_ORG_ID:'||p_rma_receipt_rec.RECEIVING_ORG_ID);
3192 FND_LOG.STRING(FND_LOG.level_procedure, l_debug_key, 'RECEIVING_SUBINVENTORY:'||p_rma_receipt_rec.RECEIVING_SUBINVENTORY);
3193 FND_LOG.STRING(FND_LOG.level_procedure, l_debug_key, 'RECEIVING_LOCATOR_ID:'||p_rma_receipt_rec.RECEIVING_LOCATOR_ID);
3194 FND_LOG.STRING(FND_LOG.level_procedure, l_debug_key, 'RECEIVING_LOCATOR_NAME:'||p_rma_receipt_rec.RECEIVING_LOCATOR_NAME);
3195 END IF;
3196
3197 -- Initialize Procedure return status to success.
3198 x_return_status := FND_API.G_RET_STS_SUCCESS;
3199
3200 -- Check for necessary fields.
3201 IF (p_rma_receipt_rec.receiving_org_id IS NULL OR p_rma_receipt_rec.receiving_subinventory IS NULL) THEN
3202 -- Add an error message to the FND stack.
3203 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3204 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
3205 'Mandatory fields have not been entered.');
3206 END IF;
3207 FND_MESSAGE.set_name('AHL', 'AHL_COM_REQD_PARAM_MISSING'); -- Required parameter is missing.
3208 FND_MSG_PUB.ADD;
3209 x_return_status := FND_API.G_RET_STS_ERROR;
3210 RETURN;
3211 END IF;
3212
3213 l_receiving_locator_name := p_rma_receipt_rec.receiving_locator_name;
3214 x_receiving_locator_id := p_rma_receipt_rec.receiving_locator_id;
3215 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3216 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
3217 'Return value for Locator id is set using the record, IN param : '||x_receiving_locator_id);
3218 END IF;
3219
3220 IF (l_receiving_locator_name IS NOT NULL
3221 AND l_receiving_locator_name <> FND_API.G_MISS_CHAR) THEN -- locator name
3222 OPEN is_valid_locator(p_rma_receipt_rec.receiving_org_id
3223 ,l_receiving_locator_name);
3224 FETCH is_valid_locator INTO x_receiving_locator_id,l_receiving_locator_name, l_sub_inv_code;
3225 IF (is_valid_locator%FOUND) THEN -- locator exists
3226 IF (p_rma_receipt_rec.receiving_subinventory <> l_sub_inv_code) THEN
3227 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3228 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
3229 'The receiving locator '||l_receiving_locator_name||' already exists in another Sub Inventory '
3230 ||l_sub_inv_code);
3231 END IF;
3232 FND_MESSAGE.set_name('AHL', 'AHL_OSP_RMA_INV_LOC'); -- The receiving locator is invalid.
3233 FND_MSG_PUB.ADD;
3234 x_return_status := FND_API.G_RET_STS_ERROR;
3235 CLOSE is_valid_locator;
3236 RETURN;
3237 ELSE
3238 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3239 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
3240 'The receiving locator '||l_receiving_locator_name||' already exists in the Org in '
3241 || l_sub_inv_code || ' subinventory');
3242 END IF;
3243 END IF;
3244 ELSE -- locator does not exist hence try to create a new locator
3245 OPEN is_dyn_locator_sub_inv(p_rma_receipt_rec.receiving_org_id
3246 ,p_rma_receipt_rec.receiving_subinventory);
3247 FETCH is_dyn_locator_sub_inv INTO l_dummy;
3248 IF (is_dyn_locator_sub_inv%FOUND) THEN -- dynamic locator enabled Sub Inv
3249 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3250 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
3251 'The receiving Sub Inventor '||p_rma_receipt_rec.receiving_subinventory||' is dynamic locator controlled');
3252 END IF;
3253
3254 inv_loc_wms_pub.create_locator(
3255 x_return_status => x_return_status,
3256 x_msg_count => x_msg_count,
3257 x_msg_data => x_msg_data,
3258 x_inventory_location_id => l_locator_id,
3259 x_locator_exists => l_dummy,
3260 p_organization_id => p_rma_receipt_rec.receiving_org_id,
3261 p_organization_code => NULL,
3262 p_concatenated_segments => l_receiving_locator_name,
3263 p_description => NULL,
3264 p_inventory_location_type => NULL,
3265 p_picking_order => NULL,
3266 p_location_maximum_units => NULL,
3267 p_subinventory_code => p_rma_receipt_rec.receiving_subinventory,
3268 p_location_weight_uom_code => NULL,
3269 p_max_weight => NULL,
3270 p_volume_uom_code => NULL,
3271 p_max_cubic_area => NULL,
3272 p_x_coordinate => NULL,
3273 p_y_coordinate => NULL,
3274 p_z_coordinate => NULL,
3275 p_physical_location_id => NULL,
3276 p_pick_uom_code => NULL,
3277 p_dimension_uom_code => NULL,
3278 p_length => NULL,
3279 p_width => NULL,
3280 p_height => NULL,
3281 p_status_id => NULL,
3282 p_dropping_order => NULL);
3283
3284 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3285 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'After calling inv_loc_wms_pub.create_locator');
3286 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'x_return_status: '||x_return_status);
3287 END IF;
3288
3289 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3290 x_receiving_locator_id := l_locator_id;
3291 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3292 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key, 'New Locator'|| l_locator_id || ' created in '
3293 || p_rma_receipt_rec.receiving_subinventory);
3294 END IF;
3295 END IF;
3296 ELSE -- not dynamic locator enabled
3297 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3298 FND_LOG.string(FND_LOG.level_statement, l_debug_key,
3299 'The receiving locator '||p_rma_receipt_rec.receiving_locator_name||' does not exist in Sub Inventory '
3300 ||p_rma_receipt_rec.receiving_subinventory);
3301 END IF;
3302 FND_MESSAGE.set_name('AHL', 'AHL_OSP_RMA_INV_LOC'); -- The receiving locator is invalid.
3303 FND_MSG_PUB.ADD;
3304 x_return_status := FND_API.G_RET_STS_ERROR;
3305 CLOSE is_dyn_locator_sub_inv;
3306 CLOSE is_valid_locator;
3307 RETURN;
3308 END IF; -- dynamic locator Sub Inv
3309 CLOSE is_dyn_locator_sub_inv;
3310 END IF; -- locator exists
3311 CLOSE is_valid_locator;
3312 END IF; -- locator name
3313
3314 END validate_create_locator;
3315 -- SALOGAN added validate_create_locator for 9496606 end
3316
3317 END AHL_OSP_RCV_PVT;
[Home] [Help]