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