[Home] [Help]
PACKAGE BODY: APPS.CSI_RMA_FULFILL_PUB
Source
1 PACKAGE BODY CSI_RMA_FULFILL_PUB AS
2 /* $Header: csipirfb.pls 120.15 2011/10/03 11:44:32 dsingire ship $ */
3
4 g_pkg_name varchar2(30) := 'csi_rma_fulfill_pub';
5
6 procedure debug(
7 p_message IN varchar2)
8 IS
9 BEGIN
10 csi_t_gen_utility_pvt.add(p_message);
11 END debug;
12
13 PROCEDURE api_log(
14 p_api_name IN varchar2)
15 IS
16 BEGIN
17 csi_t_gen_utility_pvt.dump_api_info(
18 p_api_name => p_api_name,
19 p_pkg_name => 'csi_rma_fulfill_pub');
20 END api_log;
21
22
23 PROCEDURE get_rma_info(
24 p_rma_line_id IN number,
25 x_rma_line_rec OUT NOCOPY csi_order_ship_pub.mtl_txn_rec,
26 x_error_message OUT NOCOPY varchar2,
27 x_return_status OUT NOCOPY varchar2)
28 IS
29 BEGIN
30
31
32 x_return_status := fnd_api.g_ret_sts_success;
33
34 csi_t_gen_utility_pvt.dump_api_info(
35 p_api_name => 'get_rma_info',
36 p_pkg_name => 'csi_rma_fulfill_pub');
37
38 x_rma_line_rec.source_line_id := p_rma_line_id;
39
40 BEGIN
41
42 SELECT oel.line_number ,
43 oel.line_id ,
44 oeh.order_number,
45 oeh.header_id
46 INTO x_rma_line_rec.source_line_ref,
47 x_rma_line_rec.source_line_ref_id,
48 x_rma_line_rec.source_header_ref,
49 x_rma_line_rec.source_header_ref_id
50 FROM oe_order_lines_all oel ,
51 oe_order_headers_all oeh
52 WHERE oeh.header_id = oel.header_id
53 AND oel.line_id = p_rma_line_id;
54
55 EXCEPTION
56 WHEN no_data_found THEN
57 fnd_message.set_name('CSI', 'CSI_INT_OE_LINE_ID_INVALID');
58 fnd_message.set_token('OE_LINE_ID', p_rma_line_id);
59 fnd_msg_pub.add;
60 RAISE fnd_api.g_exc_error;
61 END;
62
63 EXCEPTION
64
65 WHEN fnd_api.g_exc_error THEN
66 x_error_message := fnd_msg_pub.get;
67 x_return_status := fnd_api.g_ret_sts_error;
68
69 WHEN others THEN
70 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
71 fnd_message.set_token('MESSAGE', substr(sqlerrm, 240));
72 fnd_msg_pub.add;
73
74 x_error_message := fnd_msg_pub.get;
75 x_return_status := fnd_api.g_ret_sts_unexp_error;
76
77 END get_rma_info;
78
79
80 /*
81 */
82 PROCEDURE decode_message(
83 p_msg_header IN xnp_message.msg_header_rec_type,
84 p_msg_text IN varchar2,
85 x_return_status OUT NOCOPY varchar2,
86 x_error_message OUT NOCOPY varchar2,
87 x_rma_line_rec OUT NOCOPY csi_order_ship_pub.mtl_txn_rec)
88 IS
89
90 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
91 l_api_name varchar2(100) := 'decode_message';
92 l_rma_line_id number;
93
94 BEGIN
95
96 x_return_status := fnd_api.g_ret_sts_success;
97
98 csi_t_gen_utility_pvt.dump_api_info(
99 p_api_name => l_api_name,
100 p_pkg_name => 'csi_rma_fulfill_pub');
101
102 xnp_xml_utils.decode(p_msg_text, 'RMA_LINE_ID', l_rma_line_id);
103
104 IF nvl(l_rma_line_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
105 fnd_message.set_name('CSI','CSI_DECODE_MGS_ERROR');
106 fnd_message.set_token('MESSAGE_id', p_msg_header.message_id);
107 fnd_message.set_token('MESSAGE_CODE', p_msg_header.message_code);
108 fnd_msg_pub.add;
109 RAISE fnd_api.g_exc_error;
110 END IF;
111
112 get_rma_info(
113 p_rma_line_id => l_rma_line_id,
114 x_rma_line_rec => x_rma_line_rec,
115 x_error_message => x_error_message,
116 x_return_status => l_return_status);
117
118 IF l_return_status <> fnd_api.g_ret_sts_success THEN
119 debug('CSI_Rma_Fulfill_Pub.Get_rma_Info Failed.');
120 RAISE fnd_api.g_exc_error;
121 END IF;
122
123 debug('CSI_Rma_Fulfill_Pub.Decode_Message Successful');
124
125 EXCEPTION
126
127 WHEN fnd_api.g_exc_error THEN
128
129 x_error_message := fnd_msg_pub.get;
130 x_return_status := fnd_api.g_ret_sts_error;
131
132 debug(x_error_message);
133
134 WHEN others THEN
135
136 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
137 fnd_message.set_token('API_NAME', g_pkg_name||'.'||l_api_name);
138 fnd_message.set_token('SQL_ERROR', substr(sqlerrm, 1, 255));
139 fnd_msg_pub.add;
140
141 x_error_message := fnd_msg_pub.get;
142 x_return_status := fnd_api.g_ret_sts_unexp_error;
143
144 debug(x_error_message);
145
146 END decode_message;
147
148 PROCEDURE rma_fulfillment(
149 p_rma_line_id IN number,
150 p_message_id IN number,
151 x_return_status OUT NOCOPY varchar2,
152 px_trx_error_rec IN OUT NOCOPY csi_datastructures_pub.transaction_error_rec)
153 IS
154
155 l_api_name varchar2(30) := 'rma_fulfillment';
156
157 l_txn_line_id number;
158 l_txn_sub_type_id number ;
159 l_src_txn_table varchar2(30) := 'OE_ORDER_LINES_ALL';
160 l_txn_type_id number := 54;
161 l_csi_txn_rec csi_datastructures_pub.transaction_rec;
162
163 l_g_txn_line_query_rec csi_t_datastructures_grp.txn_line_query_rec;
164 l_g_txn_line_detail_query_rec csi_t_datastructures_grp.txn_line_detail_query_rec;
165
166 l_g_txn_line_rec csi_t_datastructures_grp.txn_line_rec;
167 l_g_line_dtl_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
168 l_g_pty_dtl_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
169 l_g_pty_acct_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
170 l_g_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
171 l_g_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
172 l_g_ext_attrib_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
173 l_g_txn_systems_tbl csi_t_datastructures_grp.txn_systems_tbl;
174 l_g_csi_ea_tbl csi_t_datastructures_grp.csi_ext_attribs_tbl;
175 l_g_csi_eav_tbl csi_t_datastructures_grp.csi_ext_attrib_vals_tbl;
176
177 l_party_site_id number;
178
179 l_rma_line_rec oe_order_lines_all%rowtype;
180 l_rma_header_rec oe_order_headers_all%rowtype;
181
182 l_processing_status varchar2(30);
183
184 l_found boolean := FALSE;
185 l_inst_ref_found boolean := TRUE;
186 l_shippable_item_flag varchar2(1) := 'N';
187
188 l_debug_level number;
189 l_error_message varchar2(2000);
190 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
191 l_msg_data varchar2(32767);
192 l_msg_dummy varchar2(32767);
193 l_msg_count number;
194 do_not_process exception;
195
196 l_error_rec csi_datastructures_pub.transaction_error_rec;
197 l_orgn_id number;
198 l_tld_quantity number;
199 l_canceled_qty NUMBER;
200
201 BEGIN
202
203 savepoint rma_fulfillment;
204
205 x_return_status := fnd_api.g_ret_sts_success;
206 l_error_rec := px_trx_error_rec;
207
208 fnd_msg_pub.initialize;
209
210 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
211
212 api_log(l_api_name);
213
214 csi_t_gen_utility_pvt.build_file_name(
215 p_file_segment1 => 'csirmafl',
216 p_file_segment2 => p_rma_line_id);
217
218 debug(' Transaction Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
219 debug(' Transaction Type : RMA Fulfillment');
220 debug(' RMA Order Line ID :'||p_rma_line_id);
221
222 l_error_rec.source_id := p_rma_line_id;
223
224 BEGIN
225
226 SELECT oel.line_id, oel.header_id, oeh.order_number,
227 nvl(oel.sold_from_org_id, oeh.sold_from_org_id),
228 nvl(oel.sold_to_org_id, oeh.sold_to_org_id),
229 nvl(oel.ship_from_org_id, oeh.ship_from_org_id),
230 nvl(oel.invoice_to_contact_id, oeh.invoice_to_contact_id ),
231 nvl(oel.ship_to_contact_id, oeh.ship_to_contact_id ),
232 oel.line_number, oel.option_number, oel.shipment_number,
233 oel.inventory_item_id, oel.item_type_code, oel.shippable_flag,
234 oel.org_id, oel.ordered_quantity, oel.fulfilled_quantity,
235 oel.fulfillment_date, oel.line_category_code
236 INTO l_rma_line_rec.line_id, l_rma_line_rec.header_id,
237 l_rma_header_rec.order_number, l_rma_line_rec.sold_from_org_id,
238 l_rma_line_rec.sold_to_org_id, l_rma_line_rec.ship_from_org_id,
239 l_rma_line_rec.invoice_to_contact_id, l_rma_line_rec.ship_to_contact_id,
240 l_rma_line_rec.line_number, l_rma_line_rec.option_number,
241 l_rma_line_rec.shipment_number, l_rma_line_rec.inventory_item_id,
242 l_rma_line_rec.item_type_code, l_rma_line_rec.shippable_flag,
243 l_rma_line_rec.org_id, l_rma_line_rec.ordered_quantity,
244 l_rma_line_rec.fulfilled_quantity, l_rma_line_rec.fulfillment_date,
245 l_rma_line_rec.line_category_code
246 FROM oe_order_lines_all oel, oe_order_headers_all oeh
247 WHERE line_id = p_rma_line_id
248 AND oel.header_id = oeh.header_id;
249 EXCEPTION
250 WHEN no_data_found THEN
251 debug('Invalid RMA Order Line ID');
252 fnd_message.set_name('CSI','CSI_INT_OE_LINE_ID_INVALID');
253 fnd_message.set_token('OE_LINE_ID', p_rma_line_id);
254 fnd_msg_pub.add;
255 RAISE fnd_api.g_exc_error;
256 END;
257
258 l_error_rec.source_line_ref_id := l_rma_line_rec.line_id;
259 l_error_rec.source_line_ref := l_rma_line_rec.line_number||'.'||
260 l_rma_line_rec.shipment_number||'.'||
261 l_rma_line_rec.option_number;
262 l_error_rec.source_header_ref_id := l_rma_line_rec.header_id;
263 l_error_rec.source_header_ref := l_rma_header_rec.order_number;
264
265 IF l_rma_line_rec.ship_from_org_id is NULL THEN
266 IF l_rma_line_rec.sold_from_org_id is NULL THEN
267 Begin
268 l_orgn_id := oe_sys_parameters.value(
269 param_name => 'MASTER_ORGANIZATION_ID',
270 p_org_id => l_rma_line_rec.org_id);
271 Exception when others then
272 debug('Invalid Order line details - org_id: '||l_rma_line_rec.org_id);
273 fnd_message.set_name('CSI','CSI_INT_OE_LINE_ID_INVALID');
274 fnd_message.set_token('OE_LINE_ID', p_rma_line_id);
275 fnd_msg_pub.add;
276 RAISE fnd_api.g_exc_error;
277 End;
278 ELSE
279 l_orgn_id := l_rma_line_rec.sold_from_org_id;
280 END IF;
281 ELSE
282 l_orgn_id := l_rma_line_rec.ship_from_org_id;
283 END IF;
284 IF l_orgn_id is not null THEN
285 Begin
286
287 SELECT nvl(shippable_item_flag ,'N'),
288 serial_number_control_code,
289 lot_control_code,
290 revision_qty_control_code,
291 location_control_code,
292 comms_nl_trackable_flag
293 INTO l_shippable_item_flag,
294 l_error_rec.src_serial_num_ctrl_code,
295 l_error_rec.src_lot_ctrl_code ,
296 l_error_rec.src_rev_qty_ctrl_code,
297 l_error_rec.src_location_ctrl_code,
298 l_error_rec.comms_nl_trackable_flag
299 FROM mtl_system_items
300 WHERE inventory_item_id = l_rma_line_rec.inventory_item_id
301 AND organization_id = l_orgn_id;
302 Exception when others then
303 fnd_message.set_name('CSI', 'CSI_INT_ITEM_ID_MISSING');
304 fnd_message.set_token('INVENTORY_ITEM_ID', l_rma_line_rec.inventory_item_id);
305 fnd_message.set_token('INV_ORGANZATION_ID', l_orgn_id);
306 fnd_msg_pub.add;
307 End;
308 ELSE
309 debug('Invalid Organization ID - l_orgn_id: '||l_orgn_id);
310 fnd_message.set_name('CSI','CSI_INT_OE_LINE_ID_INVALID');
311 fnd_message.set_token('OE_LINE_ID', p_rma_line_id);
312 fnd_msg_pub.add;
313 RAISE fnd_api.g_exc_error;
314 END IF;
315
316 l_error_rec.inventory_item_id := l_rma_line_rec.inventory_item_id;
317
318
319 debug('RMA Order Information :');
320 debug(' Order_Number :'||l_rma_header_rec.order_number);
321 debug(' Line_Number :'||l_rma_line_rec.line_number||'.'||l_rma_line_rec.option_number);
322 debug(' Sold_to_org_ID :'||l_rma_line_rec.sold_to_org_id);
323 debug(' Inventory_Item_ID :'||l_rma_line_rec.inventory_item_id);
324 debug(' Shippable_flag :'||l_shippable_item_flag);
325 debug(' Ship_From_Org_ID :'||l_rma_line_rec.ship_from_org_id);
326 debug(' Item_Type_Code :'||l_rma_line_rec.item_type_code);
327 debug(' Ordered_Quantity :'||l_rma_line_rec.ordered_quantity);
328 debug(' Fulfilled_Quantity :'||l_rma_line_rec.fulfilled_quantity);
329 debug(' Operating_Unit_ID :'||l_rma_line_rec.org_id);
330
331 dbms_application_info.set_client_info(l_rma_line_rec.org_id);
332
333 IF l_shippable_item_flag is NULL THEN
334 debug('Could not determine if the line item is shippable or not for the RMA Fulfillment Line.');
335 fnd_message.set_name('FND','FND_GENERIC_MESSAGE');
336 fnd_message.set_token('MESSAGE','Could not determine if the line item is shippable or not for the RMA Fulfillment Line.');
337 fnd_msg_pub.add;
338 raise fnd_api.g_exc_error;
339 ELSIF l_shippable_item_flag = 'Y' THEN
340 IF (WF_ENGINE.ACTIVITY_EXIST_IN_PROCESS(
341 'OEOL' -- ITEM_TYPE
342 ,to_char(l_rma_line_rec.line_id) -- ITEM_KEY
343 ,'OEOL' -- ACTIVITY_ITEM_TYPE
344 ,'RMA_RECEIVING_SUB' -- ACTIVITY
345 )) THEN
346 debug('This Line has Receiving Node. Ignoring this line...');
347 raise do_not_process;
348 END IF;
349 -- ELSIF l_rma_line_rec.line_category_code = 'ORDER' THEN commented for testing
350 -- raise do_not_process;
351 END IF;
352
353 l_rma_line_rec.shippable_flag := l_shippable_item_flag;
354
355 -- check transaction details exist
356 l_g_txn_line_rec.source_transaction_table := l_src_txn_table;
357 l_g_txn_line_rec.source_transaction_id := p_rma_line_id;
358
359 l_found := csi_t_txn_details_pvt.check_txn_details_exist(
360 p_txn_line_rec => l_g_txn_line_rec);
361
362 l_g_txn_line_rec.source_transaction_type_id := l_txn_type_id;
363
364 IF NOT(l_found) THEN
365 debug('Transaction detail is Mandatory for RMA Fulfillment and was NOT found for the line.');
366 fnd_message.set_name('CSI', 'CSI_RMA_TXN_DTLS_REQD');
367 fnd_message.set_token('SRC_TXN_ID', p_rma_line_id);
368 fnd_msg_pub.add;
369 raise fnd_api.g_exc_error;
370 -- call the get api
371 ELSE
372 debug('Transaction Detail Found for the RMA line.');
373 BEGIN
374 SELECT processing_status
375 INTO l_processing_status
376 FROM csi_t_transaction_lines
377 WHERE source_transaction_table = l_src_txn_table
378 AND source_transaction_id = p_rma_line_id;
379
380 IF l_processing_status = 'PROCESSED' THEN
381 debug('This transaction detail is already PROCESSED.');
382 fnd_message.set_name('CSI', 'CSI_TXN_SRC_ALREADY_PROCESSED');
383 fnd_message.set_token('SRC_TBL', l_src_txn_table);
384 fnd_message.set_token('SRC_ID', p_rma_line_id);
385 fnd_msg_pub.add;
386 RAISE fnd_api.g_exc_error;
387 END IF;
388
389 UPDATE csi_t_transaction_lines
390 SET processing_status = 'IN_PROCESS'
391 WHERE source_transaction_table = l_src_txn_table
392 AND source_transaction_id = p_rma_line_id;
393
394 END;
395
396 -- build the txn_query_rec
397
398 l_g_txn_line_query_rec.source_transaction_table := l_src_txn_table;
399 l_g_txn_line_query_rec.source_transaction_id := p_rma_line_id;
400 l_g_txn_line_detail_query_rec.source_transaction_flag := 'Y';
401
402 csi_t_txn_details_grp.get_transaction_details(
403 p_api_version => 1,
404 p_commit => fnd_api.g_false,
405 p_init_msg_list => fnd_api.g_true,
406 p_validation_level => fnd_api.g_valid_level_full,
407 p_txn_line_query_rec => l_g_txn_line_query_rec,
408 p_txn_line_detail_query_rec => l_g_txn_line_detail_query_rec,
409 x_txn_line_detail_tbl => l_g_line_dtl_tbl,
410 p_get_parties_flag => fnd_api.g_false,
411 x_txn_party_detail_tbl => l_g_pty_dtl_tbl,
412 p_get_pty_accts_flag => fnd_api.g_false,
413 x_txn_pty_acct_detail_tbl => l_g_pty_acct_tbl,
414 p_get_ii_rltns_flag => fnd_api.g_false,
415 x_txn_ii_rltns_tbl => l_g_ii_rltns_tbl,
416 p_get_org_assgns_flag => fnd_api.g_false,
417 x_txn_org_assgn_tbl => l_g_org_assgn_tbl,
418 p_get_ext_attrib_vals_flag => fnd_api.g_false,
419 x_txn_ext_attrib_vals_tbl => l_g_ext_attrib_tbl,
420 p_get_csi_attribs_flag => fnd_api.g_false,
421 x_csi_ext_attribs_tbl => l_g_csi_ea_tbl,
422 p_get_csi_iea_values_flag => fnd_api.g_false,
423 x_csi_iea_values_tbl => l_g_csi_eav_tbl,
424 p_get_txn_systems_flag => fnd_api.g_false,
425 x_txn_systems_tbl => l_g_txn_systems_tbl,
426 x_return_status => l_return_status,
427 x_msg_count => l_msg_count,
428 x_msg_data => l_msg_data);
429
430 IF l_return_status <> fnd_api.g_ret_sts_success THEN
431 debug('Get transaction details for the RMA line fulfillment failed.');
432 raise fnd_api.g_exc_error;
433 END IF;
434 /*
435 SELECT transaction_line_id
436 INTO l_g_txn_line_rec.transaction_line_id
437 FROM csi_t_transaction_lines
438 WHERE source_transaction_table = l_src_txn_table
439 AND source_transaction_id = p_rma_line_id;
440 */
441
442 /* check if instance reference is found in the user created txn details
443 this is mandatory for a RMA Fulfillment.
444 */
445
446 IF l_g_line_dtl_tbl.COUNT > 0 THEN
447 l_tld_quantity := 0;
448 FOR l_ind in l_g_line_dtl_tbl.FIRST..l_g_line_dtl_tbl.LAST
449 LOOP
450 IF l_g_line_dtl_tbl(l_ind).instance_id is NULL THEN
451 l_inst_ref_found := FALSE;
452 exit;
453 END IF;
454 IF l_g_line_dtl_tbl(l_ind).source_transaction_flag = 'Y' THEN -- changes for bug 3684010
455 l_tld_quantity := l_tld_quantity + ABS(l_g_line_dtl_tbl(l_ind).quantity);
456 END IF;
457 END LOOP;
458 END IF;
459 -- changes for bug 3684010.Ensuring that Post de fact sales order Qty changes are taken care of
460 IF nvl(l_rma_line_rec.fulfilled_quantity,fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
461 l_canceled_qty := l_rma_line_rec.fulfilled_quantity;
462 ELSE
463 l_canceled_qty := l_rma_line_rec.ordered_quantity;
464 END IF;
465
466 IF l_tld_quantity <> l_canceled_qty THEN
467 IF l_g_line_dtl_tbl.COUNT > 1 THEN
468 fnd_message.set_name('CSI', 'CSI_TXN_LINE_DTL_QTY_INVALID');
469 fnd_msg_pub.add;
470 RAISE fnd_api.g_exc_error;
471 ELSE
472 debug('Canceled Qty as on the Sales Order: '||l_canceled_qty);
473 debug('Total Source Txn Details Qty : '||l_tld_quantity);
474 l_g_line_dtl_tbl(1).quantity := -1 * l_canceled_qty;
475
476 END IF;
477 END IF;
478 END IF;
479 IF (l_inst_ref_found) THEN
480 debug('Instance reference found. RMA fulfillment.');
481
482 --assign the values for the csi_txn_rec
483 debug( 'Creating CSI Transaction for the Fulfill RMA Line.');
484 l_csi_txn_rec.source_line_ref_id := l_rma_line_rec.line_id;
485 l_csi_txn_rec.source_line_ref := l_rma_line_rec.line_number||'.'||
486 l_rma_line_rec.shipment_number||'.'||
487 l_rma_line_rec.option_number;
488 l_csi_txn_rec.source_header_ref_id := l_rma_line_rec.header_id;
489 l_csi_txn_rec.source_header_ref := l_rma_header_rec.order_number;
490 l_csi_txn_rec.transaction_type_id := l_txn_type_id;
491 l_csi_txn_rec.transaction_date := nvl(l_rma_line_rec.fulfillment_date, sysdate);
492 l_csi_txn_rec.source_transaction_date := nvl(l_rma_line_rec.fulfillment_date, sysdate);
493
494
495 csi_t_gen_utility_pvt.dump_api_info(
496 p_api_name => 'create_transaction',
497 p_pkg_name => 'csi_transactions_pvt');
498
499 csi_transactions_pvt.create_transaction(
500 p_api_version => 1.0,
501 p_commit => fnd_api.g_false,
502 p_init_msg_list => fnd_api.g_true,
503 p_validation_level => fnd_api.g_valid_level_full,
504 p_success_if_exists_flag => 'Y',
505 p_transaction_rec => l_csi_txn_rec,
506 x_return_status => l_return_status,
507 x_msg_count => l_msg_count,
508 x_msg_data => l_msg_data);
509
510 IF l_return_status <> fnd_api.g_ret_sts_success THEN
511 debug('Create CSI transaction failed for Fulfill RMA Line.');
512 RAISE fnd_api.g_exc_error;
513 END IF;
514
515 fulfill_rma_line(
516 p_rma_line_rec => l_rma_line_rec,
517 p_csi_txn_rec => l_csi_txn_rec,
518 p_line_dtl_tbl => l_g_line_dtl_tbl,
519 px_trx_error_rec => l_error_rec,
520 x_msg_count => l_msg_count,
521 x_msg_data => l_msg_data,
522 x_return_status => l_return_status);
523
524 IF l_return_status <> fnd_api.g_ret_sts_success THEN
525 debug('Fulfill RMA Line routine failed.');
526 RAISE fnd_api.g_exc_error;
527 END IF;
528 ELSE
529 debug('Instance reference on the Transaction detail is Mandatory for a RMA Fulfillment and was NOT found for the line.');
530 fnd_message.set_name('CSI', 'CSI_RMA_INST_REF_REQD');
531 fnd_message.set_token('SRC_TXN_ID', p_rma_line_id);
532 fnd_msg_pub.add;
533 raise fnd_api.g_exc_error;
534 END IF;
535
536 EXCEPTION
537 WHEN do_not_process THEN
538 x_return_status := fnd_api.g_ret_sts_success;
539 WHEN fnd_api.g_exc_error THEN
540 rollback to rma_fulfillment;
541 x_return_status := fnd_api.g_ret_sts_error;
542 l_error_message := csi_t_gen_utility_pvt.dump_error_stack;
543 l_error_rec.error_text := l_error_message;
544 debug('Error :'||l_error_rec.error_text);
545 px_trx_error_rec := l_error_rec;
546
547 UPDATE csi_t_transaction_lines
548 SET processing_status = 'ERROR'
549 WHERE source_transaction_id = p_rma_line_id
550 AND source_transaction_table = 'OE_ORDER_LINES_ALL';
551
552 csi_utl_pkg.update_txn_line_dtl (
553 p_source_trx_id => p_rma_line_id,
554 p_source_trx_table => 'OE_ORDER_LINES_ALL',
555 p_api_name => l_api_name,
556 p_error_message => l_error_message );
557
558 WHEN others THEN
559 rollback to rma_fulfillment;
560 fnd_message.set_name('FND','FND_GENERIC_MESSAGE');
561 fnd_message.set_token('MESSAGE',substr(sqlerrm,1,255));
562 fnd_msg_pub.add;
563
564 x_return_status := fnd_api.g_ret_sts_error;
565 l_error_message := csi_t_gen_utility_pvt.dump_error_stack;
566
567 l_error_rec.error_text := l_error_message;
568 debug('Error :'||l_error_rec.error_text);
569 px_trx_error_rec := l_error_rec;
570
571 UPDATE csi_t_transaction_lines
572 SET processing_status = 'ERROR'
573 WHERE source_transaction_id = p_rma_line_id
574 AND source_transaction_table = 'OE_ORDER_LINES_ALL';
575
576 csi_utl_pkg.update_txn_line_dtl (
577 p_source_trx_id => p_rma_line_id,
578 p_source_trx_table => 'OE_ORDER_LINES_ALL',
579 p_api_name => l_api_name,
580 p_error_message => l_error_message );
581
582 End rma_fulfillment;
583
584 PROCEDURE fulfill_rma_line(
585 p_rma_line_rec IN oe_order_lines_all%rowtype,
586 p_csi_txn_rec IN csi_datastructures_pub.transaction_rec,
587 p_line_dtl_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
588 px_trx_error_rec IN OUT NOCOPY csi_datastructures_pub.transaction_error_rec,
589 x_msg_count OUT NOCOPY number,
590 x_msg_data OUT NOCOPY varchar2,
591 x_return_status OUT NOCOPY varchar2)
592 IS
593
594 l_api_name varchar2(30) := 'fulfill_rma_line';
595 l_txn_type_id number := 54;
596 l_csi_trxn_rec csi_datastructures_pub.transaction_rec;
597 l_txn_sub_type_rec csi_order_ship_pub.txn_sub_type_rec;
598 l_src_line_rec csi_order_ship_pub.order_line_rec;
599
600 l_txn_line_query_rec csi_t_datastructures_grp.txn_line_query_rec;
601 l_txn_line_detail_query_rec csi_t_datastructures_grp.txn_line_detail_query_rec;
602
603 l_txn_line_rec csi_t_datastructures_grp.txn_line_rec;
604 l_txn_line_dtl_rec csi_t_datastructures_grp.txn_line_detail_rec; -- Added bug 3230999
605 l_line_dtl_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
606 l_pty_dtl_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
607 l_pty_acct_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
608 l_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
609 l_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
610 l_txn_ext_attrib_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
611 l_txn_systems_tbl csi_t_datastructures_grp.txn_systems_tbl;
612 l_csi_ea_tbl csi_t_datastructures_grp.csi_ext_attribs_tbl;
613 l_csi_eav_tbl csi_t_datastructures_grp.csi_ext_attrib_vals_tbl;
614
615 l_instance_rec csi_datastructures_pub.instance_header_rec;
616 l_party_header_tbl csi_datastructures_pub.party_header_tbl;
617 l_account_header_tbl csi_datastructures_pub.party_account_header_tbl;
618 l_org_assignments_tbl csi_datastructures_pub.org_units_header_tbl;
619 l_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
620 l_ext_attrib_tbl csi_datastructures_pub.extend_attrib_values_tbl;
621 l_ext_attrib_def_tbl csi_datastructures_pub.extend_attrib_tbl;
622 l_asset_assignment_tbl csi_datastructures_pub.instance_asset_header_tbl;
623 l_upd_parties_tbl csi_datastructures_pub.party_tbl;
624 l_upd_pty_accts_tbl csi_datastructures_pub.party_account_tbl;
625
626 -- update_item_instance variables
627 l_u_instance_rec csi_datastructures_pub.instance_rec;
628 l_u_parties_tbl csi_datastructures_pub.party_tbl;
629 l_u_pty_accts_tbl csi_datastructures_pub.party_account_tbl;
630 l_u_org_units_tbl csi_datastructures_pub.organization_units_tbl;
631 l_u_ea_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
632 l_u_pricing_tbl csi_datastructures_pub.pricing_attribs_tbl;
633 l_u_assets_tbl csi_datastructures_pub.instance_asset_tbl;
634 l_u_instance_ids_list csi_datastructures_pub.id_tbl;
635
636 l_u_txn_line_rec csi_t_datastructures_grp.txn_line_rec;
637 l_u_line_dtl_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
638 l_u_pty_dtl_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
639 l_u_pty_acct_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
640 l_u_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
641 l_u_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
642 l_u_eav_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
643
644 --Processs txn grp variables
645
646 l_p_instances_tbl csi_process_txn_grp.txn_instances_tbl;
647 l_p_parties_tbl csi_process_txn_grp.txn_i_parties_tbl;
648 l_p_ip_accounts_tbl csi_process_txn_grp.txn_ip_accounts_tbl;
649 l_p_org_units_tbl csi_process_txn_grp.txn_org_units_tbl;
650 l_p_ext_attrib_values_tbl csi_process_txn_grp.txn_ext_attrib_values_tbl;
651 l_p_pricing_attribs_tbl csi_process_txn_grp.txn_pricing_attribs_tbl;
652 l_p_instance_asset_tbl csi_process_txn_grp.txn_instance_asset_tbl;
653 l_p_ii_relationships_tbl csi_process_txn_grp.txn_ii_relationships_tbl;
654 l_dest_location_rec csi_process_txn_grp.dest_location_rec;
655 l_api_version NUMBER := 1.0;
656 l_commit VARCHAR2(1) := fnd_api.g_false;
657 l_init_msg_list VARCHAR2(1) := fnd_api.g_false;
658 l_validation_level NUMBER := fnd_api.g_valid_level_full;
659 l_validate_only_flag VARCHAR2(1) := fnd_api.g_false;
660 l_in_out_flag VARCHAR2(30) := 'NONE';
661 l_pty_ind binary_integer;
662 l_pa_ind binary_integer;
663 l_oa_ind binary_integer;
664 l_ea_ind binary_integer;
665
666 l_error_message varchar2(32767);
667 l_time_stamp date;
668 l_item_srl_code number;
669 l_td_owner_id number;
670 l_inst_owner_pty_id number;
671 l_inst_owner_acct_id number;
672 l_src_txn_owner_pty_id number;
673 l_internal_party_id number;
674 l_owner_pty_ip_id number;
675 l_owner_pty_obj_ver_num number;
676 l_owner_acct_ipa_id number;
677 l_owner_acct_obj_ver_num number;
678 l_inst_owner_acct_a_date date;
679 l_curr_object_id number;
680 l_object_inst_id number;
681 l_orig_rma_item_id number;
682 l_orig_rma_owner_id number;
683 l_orig_rma_status varchar2(30);
684 l_orig_rma_ref_valid varchar2(1):= 'Y';
685
686 l_debug_level number;
687 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
688 l_pty_override_flag varchar2(1) := 'N';
689 l_upd_inst_qty varchar2(1) := 'Y';
690 l_msg_data varchar2(32767);
691 l_msg_count number;
692 l_literal1 VARCHAR2(30) ;
693 l_literal2 VARCHAR2(30) ;
694
695 -- added as part of fix for Bug 2733128
696 l_chg_instance_rec csi_datastructures_pub.instance_rec;
697 l_chg_pricing_attribs_tbl csi_datastructures_pub.pricing_attribs_tbl;
698 l_chg_ext_attrib_val_tbl csi_datastructures_pub.extend_attrib_values_tbl;
699 l_chg_org_units_tbl csi_datastructures_pub.organization_units_tbl;
700 l_chg_inst_asset_tbl csi_datastructures_pub.instance_asset_tbl;
701 l_chg_inst_id_lst csi_datastructures_pub.id_tbl;
702 -- split item instance variables
703 l_split_src_inst_rec csi_datastructures_pub.instance_rec;
704 l_split_new_inst_rec csi_datastructures_pub.instance_rec;
705 l_quantity1 NUMBER;
706 l_locked_inst_rev_num NUMBER;
707 l_lock_id NUMBER;
708 l_lock_status NUMBER;
709 l_locked BOOLEAN;
710 l_unlock_inst_tbl csi_cz_int.config_tbl;
711 l_validation_status VARCHAR2(1);
712 do_not_process exception; --bug no. 10007311
713 l_has_rma_receipt VARCHAR2(1); --Added for bug 12417905
714 l_last_txn_type_id NUMBER; --Added for bug 12417905
715 BEGIN
716 x_return_status := fnd_api.g_ret_sts_success;
717 savepoint fulfill_rma_line;
718 fnd_msg_pub.initialize;
719
720 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
721
722 csi_t_gen_utility_pvt.dump_api_info(
723 p_api_name => 'fulfill_rma_line',
724 p_pkg_name => 'csi_rma_fulfill_pub');
725
726 BEGIN
727 --commented SQL below to make changes for the bug 4028827
728 /*
729 SELECT internal_party_id, ownership_override_at_txn
730 INTO l_internal_party_id, l_pty_override_flag
731 FROM csi_install_parameters;
732 */
733 l_internal_party_id := csi_datastructures_pub.g_install_param_rec.internal_party_id;
734 l_pty_override_flag := csi_datastructures_pub.g_install_param_rec.ownership_override_at_txn;
735
736 SELECT party_id
737 INTO l_src_txn_owner_pty_id
738 FROM hz_cust_accounts_all
739 WHERE cust_account_id = p_rma_line_rec.sold_to_org_id;
740
741 EXCEPTION
742 WHEN others THEN
743 fnd_message.set_name('FND','FND_GENERIC_MESSAGE');
744 fnd_message.set_token('MESSAGE',substr(sqlerrm,1,255));
745 fnd_msg_pub.add;
746 RAISE fnd_api.g_exc_error;
747 END;
748
749 l_csi_trxn_rec := p_csi_txn_rec;
750
751 IF p_line_dtl_tbl.COUNT > 0 THEN
752 FOR l_td_ind IN p_line_dtl_tbl.FIRST ..p_line_dtl_tbl.LAST
753 LOOP
754 IF p_line_dtl_tbl(l_td_ind).instance_id is NOT NULL THEN
755
756 px_trx_error_rec.instance_id := p_line_dtl_tbl(l_td_ind).instance_id;
757 px_trx_error_rec.serial_number := p_line_dtl_tbl(l_td_ind).serial_number;
758 px_trx_error_rec.lot_number := p_line_dtl_tbl(l_td_ind).lot_number;
759 l_txn_line_detail_query_rec.txn_line_detail_id :=
760 p_line_dtl_tbl(l_td_ind).txn_line_detail_id;
761
762 csi_t_txn_details_grp.get_transaction_details(
763 p_api_version => 1,
764 p_commit => fnd_api.g_false,
765 p_init_msg_list => fnd_api.g_true,
766 p_validation_level => fnd_api.g_valid_level_full,
767 p_txn_line_query_rec => l_txn_line_query_rec,
768 p_txn_line_detail_query_rec => l_txn_line_detail_query_rec,
769 x_txn_line_detail_tbl => l_line_dtl_tbl,
770 p_get_parties_flag => fnd_api.g_true,
771 x_txn_party_detail_tbl => l_pty_dtl_tbl,
772 p_get_pty_accts_flag => fnd_api.g_true,
773 x_txn_pty_acct_detail_tbl => l_pty_acct_tbl,
774 p_get_ii_rltns_flag => fnd_api.g_true,
775 x_txn_ii_rltns_tbl => l_ii_rltns_tbl,
776 p_get_org_assgns_flag => fnd_api.g_true,
777 x_txn_org_assgn_tbl => l_org_assgn_tbl,
778 p_get_ext_attrib_vals_flag => fnd_api.g_true,
779 x_txn_ext_attrib_vals_tbl => l_txn_ext_attrib_tbl,
780 p_get_csi_attribs_flag => fnd_api.g_false,
781 x_csi_ext_attribs_tbl => l_csi_ea_tbl,
782 p_get_csi_iea_values_flag => fnd_api.g_false,
783 x_csi_iea_values_tbl => l_csi_eav_tbl,
784 p_get_txn_systems_flag => fnd_api.g_false,
785 x_txn_systems_tbl => l_txn_systems_tbl,
786 x_return_status => l_return_status,
787 x_msg_count => l_msg_count,
788 x_msg_data => l_msg_data);
789
790 IF l_return_status <> fnd_api.g_ret_sts_success THEN
791 debug('Get transaction details failed for Fulfill RMA Line.');
792 RAISE fnd_api.g_exc_error;
793 END IF;
794
795 l_txn_line_dtl_rec := l_line_dtl_tbl(1);--always one record. Query by TLD ID. bug 3230999. changed all references
796 l_txn_line_dtl_rec.quantity := p_line_dtl_tbl(l_td_ind).quantity; -- changes for bug 3684010
797
798 debug(' txn_line_detail_tbl.count :'||l_line_dtl_tbl.count );
799 debug(' txn_party_detail_tbl.count :'||l_pty_dtl_tbl.count );
800 debug(' txn_pty_acct_dtl_tbl.count :'||l_pty_acct_tbl.count);
801 debug(' txn_org_assgn_tbl.count :'||l_org_assgn_tbl.count);
802 debug(' txn_ii_rltns_tbl.count :'||l_ii_rltns_tbl.count);
803 debug(' txn_ext_attrib_vals_tbl.count :'||l_txn_ext_attrib_tbl.count);
804 debug(' txn_systems_tbl.count :'||l_txn_systems_tbl.count);
805
806 IF l_debug_level >= 10 THEN
807
808 debug( 'Dumping all the processing tables...');
809
810 csi_t_gen_utility_pvt.dump_txn_tables(
811 p_ids_or_index_based => 'I',
812 p_line_detail_tbl => l_line_dtl_tbl,
813 p_party_detail_tbl => l_pty_dtl_tbl,
814 p_pty_acct_tbl => l_pty_acct_tbl,
815 p_ii_rltns_tbl => l_ii_rltns_tbl,
816 p_org_assgn_tbl => l_org_assgn_tbl,
817 p_ea_vals_tbl => l_txn_ext_attrib_tbl);
818
819 END IF;
820
821 IF l_ii_rltns_tbl.count > 0 THEN
822 FOR j in l_ii_rltns_tbl.first..l_ii_rltns_tbl.last LOOP
823
824 debug('Validating txn ii_relationships .. ' );
825
826 IF l_txn_line_dtl_rec.txn_line_detail_id = l_ii_rltns_tbl(j).object_id then
827 l_object_inst_id := l_txn_line_dtl_rec.instance_id;
828 exit;
829 END IF;
830 END LOOP;
831
832 Begin
833 Select object_id
834 Into l_curr_object_id
835 from csi_ii_relationships
836 Where object_id = l_object_inst_id
837 And sysdate between nvl(active_end_date, sysdate) and sysdate+1;
838 Exception
839 when no_data_found THEN
840 l_curr_object_id := -9999;
841 when others THEN
842 fnd_message.set_name('FND','FND_GENERIC_MESSAGE');
843 fnd_message.set_token('MESSAGE',substr(sqlerrm,1,255));
844 fnd_msg_pub.add;
845 raise fnd_api.g_exc_error;
846 End;
847 --Check if the object id is being updated, if so raise error
848
849 IF l_curr_object_id <> -9999 THEN
850 IF l_curr_object_id <> l_object_inst_id THEN
851 fnd_message.set_name('CSI','CSI_INT_OBJ_ID_NOT_ALLOW_UPD');
852 fnd_message.set_token('OBJECT_ID',l_object_inst_id);
853 fnd_msg_pub.add;
854 raise fnd_api.g_exc_error;
855 END IF;
856 END IF;
857 END IF;
858
859 /* Get the sub type information for each TLD */
860
861 csi_utl_pkg.get_sub_type_rec(
862 p_sub_type_id => l_txn_line_dtl_rec.sub_type_id,
863 p_trx_type_id => l_txn_type_id,
864 x_trx_sub_type_rec => l_txn_sub_type_rec,
865 x_return_status => l_return_status) ;
866
867 IF l_return_status <> fnd_api.g_ret_sts_success THEN
868 fnd_message.set_name('FND','FND_GENERIC_MESSAGE');
869 fnd_message.set_token('MESSAGE','Call to the routine csi_utl_pkg.get_sub_type_rec Failed.');
870 fnd_msg_pub.add;
871 raise fnd_api.g_exc_error;
872 END IF;
873
874 debug(' transaction_type_id :'||l_txn_sub_type_rec.trx_type_id );
875 debug(' default sub_type_id :'||l_txn_sub_type_rec.sub_type_id );
876
877 --Fix for bug 4243512
878 IF l_txn_sub_type_rec.src_change_owner = 'Y' THEN
879 fnd_message.set_name('CSI','CSI_SUB_TYPE_INVALID');
880 fnd_msg_pub.add;
881 raise fnd_api.g_exc_error;
882 END IF;
883 --end of fix.
884
885
886 IF l_pty_dtl_tbl.COUNT > 0 THEN
887 FOR l_ind IN l_pty_dtl_tbl.FIRST..l_pty_dtl_tbl.LAST
888 LOOP
889 IF l_pty_dtl_tbl(l_ind).relationship_type_code = 'OWNER' THEN
890 l_td_owner_id := l_pty_dtl_tbl(l_ind).party_source_id;
891 exit;
892 END IF;
893 END LOOP;
894 END IF;
895
896 l_instance_rec.instance_id := l_txn_line_dtl_rec.instance_id;
897
898 csi_item_instance_pub.get_item_instance_details(
899 p_api_version => 1.0,
900 p_commit => fnd_api.g_false,
901 p_init_msg_list => fnd_api.g_true,
902 p_validation_level => fnd_api.g_valid_level_full,
903 p_instance_rec => l_instance_rec,
904 p_get_parties => fnd_api.g_true,
905 p_party_header_tbl => l_party_header_tbl,
906 p_get_accounts => fnd_api.g_true,
907 p_account_header_tbl => l_account_header_tbl,
908 p_get_org_assignments => fnd_api.g_false,
909 p_org_header_tbl => l_org_assignments_tbl,
910 p_get_pricing_attribs => fnd_api.g_false,
911 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
912 p_get_ext_attribs => fnd_api.g_false,
913 p_ext_attrib_tbl => l_ext_attrib_tbl,
914 p_ext_attrib_def_tbl => l_ext_attrib_def_tbl,
915 p_get_asset_assignments => fnd_api.g_false,
916 p_asset_header_tbl => l_asset_assignment_tbl,
917 p_time_stamp => l_time_stamp,
918 x_return_status => l_return_status,
919 x_msg_count => l_msg_count,
920 x_msg_data => l_msg_data);
921
922 IF l_return_status <> fnd_api.g_ret_sts_success THEN
923 debug('Get item instance details failed for Fulfill RMA Line.');
924 RAISE fnd_api.g_exc_error;
925 END IF;
926
927 --Initialize the variable
928 l_upd_inst_qty := 'Y';
929
930 IF l_party_header_tbl.COUNT > 0 THEN
931 FOR p_ind IN l_party_header_tbl.FIRST..l_party_header_tbl.LAST
932 LOOP
933 IF l_party_header_tbl(p_ind).relationship_type_code = 'OWNER' THEN
934 l_inst_owner_pty_id := l_party_header_tbl(p_ind).party_id;
935 l_owner_pty_ip_id := l_party_header_tbl(p_ind).instance_party_id;
936 l_owner_pty_obj_ver_num := l_party_header_tbl(p_ind).object_version_number;
937 exit;
938 END IF;
939 END LOOP;
940 ELSE
941 debug('Instance Party not found. Instance:'||l_instance_rec.instance_id);
942 fnd_message.set_name('FND','FND_GENERIC_MESSAGE');
943 fnd_message.set_token('MESSAGE','Instance Party not found. Instance:'||l_instance_rec.instance_id);
944 fnd_msg_pub.add;
945 RAISE fnd_api.g_exc_error;
946 END IF;
947
948
949 -- Bug 4997771
950 IF ( l_instance_rec.location_type_code = 'INVENTORY'
951 AND
952 NVL(l_instance_rec.active_end_date,fnd_api.g_miss_date) = fnd_api.g_miss_date )
953 THEN
954 --Added for bug 12417905
955 l_has_rma_receipt := 'N';
956 BEGIN
957 SELECT ct.TRANSACTION_TYPE_ID
958 INTO l_last_txn_type_id
959 FROM csi_transactions ct
960 WHERE ct.TRANSACTION_ID = (SELECT Max(ct2.TRANSACTION_ID)
961 FROM csi_item_instances_h cih, csi_transactions ct2
962 WHERE cih.transaction_id = ct2.transaction_id
963 AND cih.instance_id = l_instance_rec.instance_id
964 AND ct2.transaction_type_id = 53
965 AND ct2.source_header_ref_id = p_rma_line_rec.header_id);
966
967 EXCEPTION
968 WHEN no_data_found THEN
969 l_has_rma_receipt := 'N';
970 END;
971
972 IF l_last_txn_type_id = 53 THEN
973 l_has_rma_receipt := 'Y';
974 END IF;
975
976
977 IF l_inst_owner_pty_id = l_internal_party_id THEN
978 --Added for bug 12417905
979 IF(l_has_rma_receipt = 'N') THEN
980 fnd_message.set_name('CSI','CSI_INT_INST_REF_INVALID');
981 fnd_message.set_token('INSTANCE_ID',l_instance_rec.instance_id);
982 fnd_msg_pub.add;
983 RAISE fnd_api.g_exc_error;
984 END IF;
985 ELSE
986 --STARTS , bug no. 10007311 --modified the fix of bug 10007311, while making the changes for bug 12417905
987 IF(l_has_rma_receipt = 'N') THEN
988 fnd_message.set_name('CSI', 'CSI_NON_RETURNABLE_INSTANCE');
989 fnd_message.set_token('LOC_TYPE_CODE', l_instance_rec.location_type_code);
990 fnd_msg_pub.add;
991 raise fnd_api.g_exc_error;
992 END IF;
993 --ENDS bug no. 10007311
994 END IF;
995 END IF;
996
997 IF l_account_header_tbl.COUNT > 0 THEN
998 FOR a_ind IN l_account_header_tbl.FIRST..l_account_header_tbl.LAST
999 LOOP
1000 IF l_account_header_tbl(a_ind).relationship_type_code = 'OWNER'
1001 AND l_account_header_tbl(a_ind).instance_party_id = l_owner_pty_ip_id
1002 THEN
1003 l_inst_owner_acct_id := l_account_header_tbl(a_ind).party_account_id;
1004 l_owner_acct_ipa_id := l_account_header_tbl(a_ind).ip_account_id;
1005 l_owner_acct_obj_ver_num := l_account_header_tbl(a_ind).object_version_number;
1006 l_inst_owner_acct_a_date := l_account_header_tbl(a_ind).active_end_date;
1007 exit;
1008 END IF;
1009 END LOOP;
1010 ELSE
1011 debug('Instance Party Account not found. Instance:'||l_instance_rec.instance_id);
1012 END IF;
1013
1014 debug('Instance owner party : '||l_inst_owner_pty_id);
1015 debug('Instance owner account : '||l_inst_owner_acct_id);
1016 debug('Source Txn. owner party : '||l_src_txn_owner_pty_id);
1017 debug('Source Txn. owner account: '||p_rma_line_rec.sold_to_org_id);
1018 debug('Txn detail owner party : '||l_td_owner_id);
1019 debug('Internal party : '||l_internal_party_id);
1020
1021 IF l_inst_owner_pty_id = l_internal_party_id THEN
1022 IF nvl(l_txn_sub_type_rec.src_change_owner, 'N') = 'Y' THEN
1023 fnd_message.set_name('CSI','CSI_INT_INST_REF_INVALID');
1024 fnd_message.set_token('INSTANCE_ID',l_instance_rec.instance_id);
1025 fnd_msg_pub.add;
1026 RAISE fnd_api.g_exc_error;
1027 END IF;
1028 ELSIF l_inst_owner_pty_id <> l_src_txn_owner_pty_id THEN
1029 IF l_pty_override_flag = 'Y' THEN
1030
1031 l_upd_parties_tbl(1).instance_party_id := l_owner_pty_ip_id;
1032 l_upd_parties_tbl(1).object_version_number := l_owner_pty_obj_ver_num;
1033
1034
1035 l_upd_parties_tbl(1).party_source_table := 'HZ_PARTIES';
1036 l_upd_parties_tbl(1).party_id := l_src_txn_owner_pty_id;
1037 l_upd_parties_tbl(1).relationship_type_code := 'OWNER';
1038 l_upd_parties_tbl(1).contact_flag := 'N';
1039 l_upd_parties_tbl(1).call_contracts := fnd_api.g_false;
1040
1041 l_upd_pty_accts_tbl(1).ip_account_id := l_owner_acct_ipa_id;
1042 l_upd_pty_accts_tbl(1).object_version_number:= l_owner_acct_obj_ver_num;
1043 l_upd_pty_accts_tbl(1).parent_tbl_index := 1;
1044 l_upd_pty_accts_tbl(1).party_account_id := p_rma_line_rec.sold_to_org_id; -- bug 3693594
1045 l_upd_pty_accts_tbl(1).relationship_type_code := 'OWNER';
1046 l_upd_pty_accts_tbl(1).call_contracts := fnd_api.g_false;
1047
1048 /* Commented the call as part of fix for Bug 2733128. Added call to Update_Item_Instance instead
1049 csi_t_gen_utility_pvt.dump_api_info(
1050 p_pkg_name => 'csi_party_relationships_pub',
1051 p_api_name => 'update_inst_party_relationship');
1052
1053 csi_party_relationships_pub.update_inst_party_relationship (
1054 p_api_version => 1.0,
1055 p_commit => fnd_api.g_false,
1056 p_init_msg_list => fnd_api.g_true,
1057 p_validation_level => fnd_api.g_valid_level_full,
1058 p_party_tbl => l_upd_parties_tbl,
1059 p_party_account_tbl => l_upd_pty_accts_tbl,
1060 p_txn_rec => l_csi_trxn_rec,
1061 x_return_status => l_return_status,
1062 x_msg_count => l_msg_count,
1063 x_msg_data => l_msg_data);
1064 */
1065 -- Transfer the Ownership first to the new RMA Customer and then process
1066 -- the RMA as a Normal one.
1067 -- Begin code fix as part of fix for Bug 2733128.
1068
1069 l_chg_instance_rec.instance_id := l_instance_rec.instance_id;
1070 l_chg_instance_rec.object_version_number := l_instance_rec.object_version_number;
1071 l_chg_instance_rec.active_end_date := NUll;
1072 -- End code fix as part of fix for Bug 2733128.
1073
1074 csi_t_gen_utility_pvt.dump_api_info(
1075 p_pkg_name => 'csi_item_instance_pub',
1076 p_api_name => 'update_item_instance');
1077
1078 csi_item_instance_pub.update_item_instance(
1079 p_api_version => 1.0,
1080 p_commit => fnd_api.g_false,
1081 p_init_msg_list => fnd_api.g_true,
1082 p_validation_level => fnd_api.g_valid_level_full,
1083 p_instance_rec => l_chg_instance_rec,
1084 p_ext_attrib_values_tbl => l_chg_ext_attrib_val_tbl,
1085 p_party_tbl => l_upd_parties_tbl,
1086 p_account_tbl => l_upd_pty_accts_tbl,
1087 p_pricing_attrib_tbl => l_chg_pricing_attribs_tbl,
1088 p_org_assignments_tbl => l_chg_org_units_tbl,
1089 p_txn_rec => l_csi_trxn_rec,
1090 p_asset_assignment_tbl => l_chg_inst_asset_tbl,
1091 x_instance_id_lst => l_chg_inst_id_lst,
1092 x_return_status => l_return_status,
1093 x_msg_count => l_msg_count,
1094 x_msg_data => l_msg_data);
1095
1096 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1097 RAISE fnd_api.g_exc_error;
1098 END IF;
1099
1100 debug('Ownership Changed Successfully.');
1101 ELSE
1102 fnd_message.set_name('CSI','CSI_RMA_OWNER_MISMATCH');
1103 fnd_message.set_token('INSTANCE_ID', l_instance_rec.instance_id );
1104 fnd_message.set_token('OLD_PARTY_ID', l_inst_owner_pty_id );
1105 fnd_message.set_token('NEW_PARTY_ID', l_src_txn_owner_pty_id );
1106 fnd_msg_pub.add;
1107 RAISE fnd_api.g_exc_error;
1108 END IF;
1109 END IF;
1110 /* Bug 3746600. Since Cancellation is always a expiry now, we do not need the code below. commenting.
1111 ELSE -- inst party = source txn party AND instance is valid - Normal case
1112 IF ( l_txn_line_dtl_rec.reference_source_id is NOT NULL
1113 AND l_txn_line_dtl_rec.reference_source_line_id is NOT NULL)
1114 AND nvl(p_rma_line_rec.shippable_flag, 'N') = 'Y'
1115 THEN
1116 Begin
1117 Select inventory_item_id, flow_status_code, sold_to_org_id
1118 Into l_orig_rma_item_id, l_orig_rma_status, l_orig_rma_owner_id
1119 From oe_order_lines_all
1120 Where line_id = l_txn_line_dtl_rec.reference_source_line_id
1121 And header_id = l_txn_line_dtl_rec.reference_source_id;
1122 Exception When others Then
1123 l_orig_rma_ref_valid := 'N';
1124 End;
1125
1126 IF l_orig_rma_ref_valid = 'Y' THEN
1127 IF ( l_orig_rma_owner_id = l_inst_owner_acct_id)
1128 AND (l_orig_rma_item_id = l_instance_rec.inventory_item_id)
1129 THEN
1130 IF (WF_ENGINE.ACTIVITY_EXIST_IN_PROCESS(
1131 'OEOL' -- ITEM_TYPE
1132 ,to_char(l_txn_line_dtl_rec.reference_source_line_id) -- ITEM_KEY
1133 ,'OEOL' -- ACTIVITY_ITEM_TYPE
1134 ,'RMA_RECEIVING_SUB' -- ACTIVITY
1135 ))
1136 THEN
1137 l_upd_inst_qty := 'N';
1138 debug('This Line had a Receiving Node and hence would have had updated IB...');
1139 debug('Not updating IB Quantity ...');
1140
1141 END IF;
1142 ELSE
1143 debug('Ref. RMA attributes do not match. Updating IB anyway ...');
1144 END IF;
1145 ELSE
1146 debug('Could not fime Ref. RMA details . Updating IB anyway ...');
1147 END IF;
1148
1149 IF NOT (sysdate between nvl(l_instance_rec.active_end_date, sysdate)
1150 AND sysdate+1) THEN
1151 IF l_instance_rec.quantity <= 0 THEN
1152 IF l_instance_rec.quantity = 0 THEN
1153 l_u_instance_rec.active_end_date := NULL;
1154 l_u_instance_rec.quantity := ABS(l_txn_line_dtl_rec.quantity);
1155 Begin
1156 SELECT object_version_number
1157 INTO l_u_instance_rec.object_version_number
1158 FROM csi_item_instances
1159 WHERE instance_id = l_instance_rec.instance_id;
1160 Exception when others then
1161 debug('Fetch instance details failed ...');
1162 fnd_message.set_name('FND','FND_GENERIC_MESSAGE');
1163 fnd_message.set_token('MESSAGE',substr(sqlerrm,1,255));
1164 fnd_msg_pub.add;
1165 raise fnd_api.g_exc_unexpected_error;
1166 End;
1167
1168 csi_t_gen_utility_pvt.dump_api_info(
1169 p_pkg_name => 'csi_item_instance_pub',
1170 p_api_name => 'update_item_instance');
1171
1172 csi_item_instance_pub.update_item_instance(
1173 p_api_version => 1.0,
1174 p_commit => fnd_api.g_false,
1175 p_init_msg_list => fnd_api.g_true,
1176 p_validation_level => fnd_api.g_valid_level_full,
1177 p_instance_rec => l_u_instance_rec,
1178 p_party_tbl => l_u_parties_tbl,
1179 p_account_tbl => l_u_pty_accts_tbl,
1180 p_org_assignments_tbl => l_u_org_units_tbl,
1181 p_ext_attrib_values_tbl => l_u_ea_values_tbl,
1182 p_pricing_attrib_tbl => l_u_pricing_tbl,
1183 p_asset_assignment_tbl => l_u_assets_tbl,
1184 p_txn_rec => l_csi_trxn_rec,
1185 x_instance_id_lst => l_u_instance_ids_list,
1186 x_return_status => l_return_status,
1187 x_msg_count => l_msg_count,
1188 x_msg_data => l_msg_data);
1189
1190 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1191 RAISE fnd_api.g_exc_error;
1192 END IF;
1193 ELSE
1194 debug('Instance Quantity is -ve already. Error??');
1195 l_upd_inst_qty := 'N';
1196 --RAISE fnd_api.g_exc_error;
1197 END IF;
1198 ELSE --Not sure about the case??
1199 l_p_instances_tbl(l_td_ind).active_end_date := NULL;
1200 l_upd_inst_qty := 'N';
1201 END IF;
1202 END IF; -- end dated instance
1203
1204 END IF;
1205 END IF;
1206 Bug 3746600 Changes End. */
1207
1208 IF p_rma_line_rec.ship_from_org_id is NOT NULL THEN
1209 l_dest_location_rec.inv_organization_id := p_rma_line_rec.ship_from_org_id;
1210 ELSIF p_rma_line_rec.sold_from_org_id is NOT NULL THEN
1211 l_dest_location_rec.inv_organization_id := p_rma_line_rec.sold_from_org_id;
1212 ELSIF l_txn_line_dtl_rec.inv_organization_id is NOT NULL THEN
1213 l_dest_location_rec.inv_organization_id := l_txn_line_dtl_rec.inv_organization_id;
1214 ELSE
1215 l_dest_location_rec.inv_organization_id := l_instance_rec.vld_organization_id;
1216 END IF;
1217
1218 Begin
1219 Select serial_number_control_code
1220 into l_item_srl_code
1221 from mtl_system_items_b
1222 where inventory_item_id = l_txn_line_dtl_rec.inventory_item_id
1223 and organization_id = l_dest_location_rec.inv_organization_id;--l_txn_line_dtl_rec.inv_organization_id;
1224 -- bug 3230999. since OM is always passing master inv.
1225 Exception when others Then
1226 debug('Could not determine serial control policy?');
1227 fnd_message.set_name('FND','FND_GENERIC_MESSAGE');
1228 fnd_message.set_token('MESSAGE',substr(sqlerrm,1,255));
1229 fnd_msg_pub.add;
1230 RAISE fnd_api.g_exc_unexpected_error;
1231 End;
1232
1233 l_p_instances_tbl(l_td_ind).instance_id := l_txn_line_dtl_rec.instance_id;
1234 IF ( nvl(l_instance_rec.active_end_date,fnd_api.g_miss_date) <> fnd_api.g_miss_date
1235 AND l_instance_rec.active_end_date < sysdate ) THEN -- Added this new check, IF as part of 3746600
1236 IF l_instance_rec.instance_usage_code <> 'IN_RELATIONSHIP' THEN
1237 -- ONLY excluding Components since they get expired along with their parent in a config cancellation
1238 fnd_message.set_name('CSI','CSI_TXN_INVALID_INST_REF');
1239 fnd_message.set_token('INSTANCE_ID',l_instance_rec.instance_id);
1240 fnd_msg_pub.add;
1241 RAISE fnd_api.g_exc_error;
1242 END IF;
1243 ELSIF l_item_srl_code = 1 THEN
1244 IF abs(l_txn_line_dtl_rec.quantity) > l_instance_rec.quantity THEN
1245 -- check if the quantity is greater than the instance quantity
1246 fnd_message.set_name('CSI','CSI_INT_QTY_CHK_FAILED');
1247 fnd_message.set_token('INSTANCE_ID',l_instance_rec.instance_id);
1248 fnd_msg_pub.add;
1249 RAISE fnd_api.g_exc_error;
1250 ELSIF l_instance_rec.quantity > abs(l_txn_line_dtl_rec.quantity) THEN -- need to split the source cust prod first
1251 debug('Original Instance Quantity: '||l_instance_rec.quantity);
1252 l_quantity1 := l_instance_rec.quantity - abs(l_txn_line_dtl_rec.quantity) ;
1253
1254 l_split_src_inst_rec.instance_id := l_instance_rec.instance_id;
1255 l_csi_trxn_rec.split_reason_code := 'PARTIAL_RETURN';
1256
1257 csi_t_gen_utility_pvt.dump_api_info(
1258 p_pkg_name => 'csi_item_instance_pvt',
1259 p_api_name => 'split_item_instance');
1260
1261 csi_item_instance_pvt.split_item_instance (
1262 p_api_version => 1.0,
1263 p_commit => fnd_api.g_false,
1264 p_init_msg_list => fnd_api.g_true,
1265 p_validation_level => fnd_api.g_valid_level_full,
1266 p_source_instance_rec => l_split_src_inst_rec,
1267 p_quantity1 => l_quantity1,
1268 p_quantity2 => abs(l_txn_line_dtl_rec.quantity),
1269 p_copy_ext_attribs => fnd_api.g_true,
1270 p_copy_org_assignments => fnd_api.g_true,
1271 p_copy_parties => fnd_api.g_true,
1272 p_copy_accounts => fnd_api.g_true,
1273 p_copy_asset_assignments => fnd_api.g_true,
1274 p_copy_pricing_attribs => fnd_api.g_true,
1275 p_txn_rec => l_csi_trxn_rec,
1276 x_new_instance_rec => l_split_new_inst_rec,
1277 x_return_status => l_return_status,
1278 x_msg_count => l_msg_count,
1279 x_msg_data => l_msg_data);
1280
1281 IF NOT(l_return_status = fnd_api.g_ret_sts_success) THEN
1282 debug('csi_item_instance_pvt.split_item_instance raised errors');
1283 raise fnd_api.g_exc_error;
1284 END IF;
1285
1286 l_p_instances_tbl(l_td_ind).instance_id := l_split_new_inst_rec.instance_id ;
1287 l_p_instances_tbl(l_td_ind).object_version_number := l_split_new_inst_rec.object_version_number;
1288 debug('New Instance ID: '||l_split_new_inst_rec.instance_id
1289 ||' New Instance Qty.: '||l_split_new_inst_rec.quantity);
1290 ELSE -- bug 3746600
1291 debug('Complete Cancellation, Expiring: '||l_instance_rec.instance_id);
1292 END IF;
1293 END IF;
1294 -- update the transaction line detail table with the inprocess status
1295 l_u_txn_line_rec.transaction_line_id := p_line_dtl_tbl(l_td_ind).transaction_line_id;
1296
1297 l_u_line_dtl_tbl(l_td_ind).txn_line_detail_id := p_line_dtl_tbl(l_td_ind).txn_line_detail_id;
1298 l_u_line_dtl_tbl(l_td_ind).transaction_line_id := p_line_dtl_tbl(l_td_ind).transaction_line_id;
1299 l_u_line_dtl_tbl(l_td_ind).processing_status := 'IN_PROCESS';
1300
1301 END IF; -- l_td.instance_id is not null
1302
1303 -- Building process txn tables
1304 l_csi_trxn_rec.txn_sub_type_id := l_txn_line_dtl_rec.sub_type_id;
1305 l_p_instances_tbl(l_td_ind).inventory_item_id := l_txn_line_dtl_rec.inventory_item_id;
1306 l_p_instances_tbl(l_td_ind).vld_organization_id := l_dest_location_rec.inv_organization_id;
1307 l_p_instances_tbl(l_td_ind).last_oe_rma_line_id := p_rma_line_rec.line_id;
1308
1309 IF l_txn_line_dtl_rec.source_transaction_flag = 'Y' THEN
1310 l_p_instances_tbl(l_td_ind).ib_txn_segment_flag := 'S';
1311 l_p_instances_tbl(l_td_ind).last_txn_line_detail_id := l_txn_line_dtl_rec.txn_line_detail_id;
1312 ELSE
1313 l_p_instances_tbl(l_td_ind).ib_txn_segment_flag := 'N';
1314 END IF;
1315
1316 -- debug('l_upd_inst_qty :'||l_upd_inst_qty); bug 3746600
1317
1318 IF l_item_srl_code <> 1 THEN
1319 -- serialized. Not sure of a case of serialized and non shippable item!!
1320 l_p_instances_tbl(l_td_ind).quantity := 1; -- the quantity is always 1
1321 l_p_instances_tbl(l_td_ind).active_end_date := sysdate; -- so that the serialized instance is expired
1322 -- ELSIF l_upd_inst_qty = 'N' THEN bug 3746600
1323 ELSE
1324 l_p_instances_tbl(l_td_ind).quantity := 0; -- so that the quantity is not reduced to 0
1325 l_p_instances_tbl(l_td_ind).active_end_date := sysdate; -- so that the instance is expired
1326 -- ELSE bug 3746600
1327 -- l_p_instances_tbl(l_td_ind).quantity := ABS(l_txn_line_dtl_rec.quantity);
1328 END IF;
1329
1330 -- this is to make sure RMA fulfillment does NOT ever change the location to INV
1331 l_p_instances_tbl(l_td_ind).inv_organization_id := NULL;
1332 l_p_instances_tbl(l_td_ind).inv_subinventory_name := NULL;
1333 l_p_instances_tbl(l_td_ind).inv_locator_id := NULL;
1334
1335 l_pty_ind := 1;
1336 l_pa_ind := 1;
1337
1338 IF l_pty_dtl_tbl.COUNT > 0 THEN
1339 FOR l_pd_ind IN l_pty_dtl_tbl.FIRST .. l_pty_dtl_tbl.LAST
1340 LOOP
1341 debug('Building TD party rec '||l_pty_ind||' for process transaction.');
1342
1343 l_p_parties_tbl(l_pty_ind).parent_tbl_index := l_td_ind;
1344 l_p_parties_tbl(l_pty_ind).party_source_table :=
1345 l_pty_dtl_tbl(l_pd_ind).party_source_table;
1346 l_p_parties_tbl(l_pty_ind).party_id :=
1347 l_pty_dtl_tbl(l_pd_ind).party_source_id;
1348 l_p_parties_tbl(l_pty_ind).instance_party_id :=
1349 l_pty_dtl_tbl(l_pd_ind).instance_party_id;
1350 l_p_parties_tbl(l_pty_ind).relationship_type_code :=
1351 l_pty_dtl_tbl(l_pd_ind).relationship_type_code;
1352 l_p_parties_tbl(l_pty_ind).contact_flag :=
1353 l_pty_dtl_tbl(l_pd_ind).contact_flag;
1354
1355 IF l_pty_acct_tbl.COUNT > 0 THEN
1356
1357 FOR l_pad_ind IN l_pty_acct_tbl.FIRST .. l_pty_acct_tbl.LAST
1358 LOOP
1359 IF l_pty_acct_tbl(l_pad_ind).txn_party_detail_id = l_pty_dtl_tbl(l_pd_ind).txn_party_detail_id THEN
1360
1361 debug('Building TD account rec '||l_pa_ind||' for process transaction.');
1362
1363 l_p_ip_accounts_tbl(l_pa_ind).parent_tbl_index := l_pty_ind;
1364 l_p_ip_accounts_tbl(l_pa_ind).party_account_id := l_pty_acct_tbl(l_pad_ind).account_id;
1365 l_p_ip_accounts_tbl(l_pa_ind).ip_account_id := l_pty_acct_tbl(l_pad_ind).ip_account_id;
1366 l_p_ip_accounts_tbl(l_pa_ind).relationship_type_code := l_pty_acct_tbl(l_pad_ind).relationship_type_code;
1367 l_p_ip_accounts_tbl(l_pa_ind).bill_to_address := l_pty_acct_tbl(l_pad_ind).bill_to_address_id;
1368 l_p_ip_accounts_tbl(l_pa_ind).ship_to_address := l_pty_acct_tbl(l_pad_ind).ship_to_address_id;
1369 l_p_ip_accounts_tbl(l_pa_ind).active_end_date := l_pty_acct_tbl(l_pad_ind).active_end_date;
1370
1371 l_pa_ind := l_pa_ind + 1;
1372 END IF;
1373 END LOOP; -- pty_acct_tbl loop
1374 END IF; -- l_pty_acct_tbl.count > 0
1375
1376 l_pty_ind := l_pty_ind + 1;
1377 END LOOP; -- pty_dtl_tbl loop
1378 END IF; -- pty_dtl_tbl.count > 0
1379 END LOOP; -- td loop
1380
1381 -- updating txn dtls to IN_PROCESS. moved this code down here from inside the loop
1382 csi_t_txn_details_grp.update_txn_line_dtls(
1383 p_api_version => 1.0,
1384 p_commit => fnd_api.g_false,
1385 p_init_msg_list => fnd_api.g_true,
1386 p_validation_level => fnd_api.g_valid_level_full,
1387 p_txn_line_rec => l_u_txn_line_rec,
1388 p_txn_line_detail_tbl => l_u_line_dtl_tbl,
1389 px_txn_ii_rltns_tbl => l_u_ii_rltns_tbl,
1390 px_txn_party_detail_tbl => l_u_pty_dtl_tbl,
1391 px_txn_pty_acct_detail_tbl => l_u_pty_acct_tbl,
1392 px_txn_org_assgn_tbl => l_u_org_assgn_tbl,
1393 px_txn_ext_attrib_vals_tbl => l_u_eav_tbl,
1394 x_return_status => l_return_status,
1395 x_msg_count => l_msg_count,
1396 x_msg_data => l_msg_data);
1397
1398 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1399 debug('Update txn line dtls failed for Fulfill RMA Line.');
1400 RAISE fnd_api.g_exc_error;
1401 END IF;
1402 --
1403 -- srramakr TSO with Equipment
1404 -- For Shippable Items, if RMA fulfillment is performed then we need to remove the config keys
1405 -- of the underlying instance
1406 --
1407 IF NVL(p_rma_line_rec.shippable_flag,'N') = 'Y' THEN
1408 IF l_p_instances_tbl.count > 0 THEN
1409 FOR J in l_p_instances_tbl.FIRST .. l_p_instances_tbl.LAST LOOP
1410 IF l_p_instances_tbl(J).instance_id IS NOT NULL AND
1411 l_p_instances_tbl(J).instance_id <> FND_API.G_MISS_NUM THEN
1412 l_lock_id := NULL;
1413 l_lock_status := NULL;
1414 l_locked_inst_rev_num := NULL;
1415 l_p_instances_tbl(J).config_inst_hdr_id := null;
1416 l_p_instances_tbl(J).config_inst_item_id := null;
1417 l_p_instances_tbl(J).config_inst_rev_num := null;
1418 l_locked := FALSE;
1419 --
1420 Begin
1421 select cil.lock_id,cil.lock_status,
1422 cil.config_inst_rev_num
1423 into l_lock_id,l_lock_status,
1424 l_locked_inst_rev_num
1425 from CSI_ITEM_INSTANCE_LOCKS cil
1426 where cil.instance_id = l_p_instances_tbl(J).instance_id
1427 and cil.lock_status <> 0;
1428 --
1429 l_locked := TRUE;
1430 Exception
1431 when no_data_found then
1432 l_locked := FALSE;
1433 End;
1434 --
1435 select config_inst_hdr_id,config_inst_item_id,config_inst_rev_num,
1436 instance_usage_code,active_end_date
1437 into l_p_instances_tbl(J).config_inst_hdr_id,
1438 l_p_instances_tbl(J).config_inst_item_id,
1439 l_p_instances_tbl(J).config_inst_rev_num,
1440 l_p_instances_tbl(J).instance_usage_code,
1441 l_p_instances_tbl(J).active_end_date
1442 from CSI_ITEM_INSTANCES
1443 where instance_id = l_p_instances_tbl(J).instance_id;
1444 --
1445 IF l_locked = TRUE THEN
1446 -- Instance is in Locked Status
1447 l_unlock_inst_tbl.DELETE;
1448 l_unlock_inst_tbl(1).source_application_id := 542;
1449 l_unlock_inst_tbl(1).lock_id := l_lock_id;
1450 l_unlock_inst_tbl(1).lock_status := l_lock_status;
1451 l_unlock_inst_tbl(1).instance_id := l_p_instances_tbl(J).instance_id;
1452 l_unlock_inst_tbl(1).source_txn_header_ref := l_csi_trxn_rec.source_header_ref_id;
1453 l_unlock_inst_tbl(1).source_txn_line_ref1 := l_csi_trxn_rec.source_line_ref_id;
1454 --
1455 debug('Calling Unlock Item Instances for Instance Id '||to_char(l_p_instances_tbl(J).instance_id));
1456 CSI_ITEM_INSTANCE_GRP.unlock_item_instances
1457 (
1458 p_api_version => 1.0
1459 ,p_commit => l_commit
1460 ,p_init_msg_list => l_init_msg_list
1461 ,p_validation_level => l_validation_level
1462 ,p_config_tbl => l_unlock_inst_tbl
1463 ,x_return_status => l_return_status
1464 ,x_msg_count => l_msg_count
1465 ,x_msg_data => l_msg_data
1466 );
1467 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1468 debug('Unlock Item Instances routine failed.');
1469 RAISE fnd_api.g_exc_error;
1470 END IF;
1471 --
1472 -- Update any pending TLD for the same config keys (fetched from lock table)
1473 -- with the instance_id so that when regular fulfillment happens for this
1474 -- tangible item (DISCONNECT), only the order line_id will be updated in the item instance
1475 Update CSI_T_TXN_LINE_DETAILS
1476 Set changed_instance_id = l_p_instances_tbl(J).instance_id
1477 ,overriding_csi_txn_id = l_csi_trxn_rec.transaction_id
1478 Where config_inst_hdr_id = l_p_instances_tbl(J).config_inst_hdr_id
1479 and config_inst_item_id = l_p_instances_tbl(J).config_inst_item_id
1480 and config_inst_rev_num = l_locked_inst_rev_num
1481 and nvl(processing_status,'$#$') = 'SUBMIT';
1482 END IF; -- if Locked
1483 --
1484 IF nvl(l_p_instances_tbl(J).instance_usage_code,'$#$') = 'IN_RELATIONSHIP' AND
1485 nvl(l_p_instances_tbl(J).active_end_date,(sysdate+1)) > sysdate AND
1486 l_p_instances_tbl(J).config_inst_hdr_id IS NOT NULL AND
1487 l_p_instances_tbl(J).config_inst_item_id IS NOT NULL AND
1488 l_p_instances_tbl(J).config_inst_rev_num IS NOT NULL THEN
1489 -- Call CZ API for Notification
1490 debug('Calling CZ_IB_TSO_GRP.Remove_Returned_Config_Item...');
1491 CZ_IB_TSO_GRP.Remove_Returned_Config_Item
1492 ( p_instance_hdr_id => l_p_instances_tbl(J).config_inst_hdr_id,
1493 p_instance_rev_nbr => l_p_instances_tbl(J).config_inst_rev_num,
1494 p_returned_config_item_id => l_p_instances_tbl(J).config_inst_item_id,
1495 p_locked_instance_rev_nbr => l_locked_inst_rev_num,
1496 p_application_id => 542,
1497 p_config_eff_date => sysdate,
1498 x_validation_status => l_validation_status,
1499 x_return_status => l_return_status,
1500 x_msg_count => l_msg_count,
1501 x_msg_data => l_msg_data
1502 );
1503 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1504 debug('Remove_Returned_Config_Item routine failed.');
1505 RAISE fnd_api.g_exc_error;
1506 END IF;
1507 END IF;
1508 END IF;
1509 --
1510 -- Nullify the Config Keys
1511 l_p_instances_tbl(J). CONFIG_INST_HDR_ID := NULL;
1512 l_p_instances_tbl(J). CONFIG_INST_REV_NUM := NULL;
1513 l_p_instances_tbl(J). CONFIG_INST_ITEM_ID := NULL;
1514 END LOOP;
1515 END IF;
1516 END IF; -- End of shippable_flag check
1517 --
1518 csi_process_txn_grp.process_transaction (
1519 p_api_version => l_api_version,
1520 p_commit => l_commit,
1521 p_init_msg_list => l_init_msg_list,
1522 p_validation_level => l_validation_level,
1523 p_validate_only_flag => l_validate_only_flag,
1524 p_in_out_flag => l_in_out_flag,
1525 p_dest_location_rec => l_dest_location_rec,
1526 p_txn_rec => l_csi_trxn_rec,
1527 p_instances_tbl => l_p_instances_tbl,
1528 p_i_parties_tbl => l_p_parties_tbl,
1529 p_ip_accounts_tbl => l_p_ip_accounts_tbl,
1530 p_org_units_tbl => l_p_org_units_tbl,
1531 p_ext_attrib_vlaues_tbl => l_p_ext_attrib_values_tbl,
1532 p_pricing_attribs_tbl => l_p_pricing_attribs_tbl,
1533 p_instance_asset_tbl => l_p_instance_asset_tbl,
1534 p_ii_relationships_tbl => l_p_ii_relationships_tbl,
1535 px_txn_error_rec => px_trx_error_rec,
1536 x_return_status => l_return_status,
1537 x_msg_count => l_msg_count,
1538 x_msg_data => l_msg_data );
1539
1540 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1541 debug('Process Transaction call failed for Fulfill RMA Line.');
1542 RAISE fnd_api.g_exc_error;
1543 END IF;
1544 Begin
1545
1546 --Assign the literals.. bug 4311676
1547 l_literal1 := 'IN_PROCESS';
1548 l_literal2 := 'OE_ORDER_LINES_ALL';
1549
1550 UPDATE csi_t_txn_line_details a
1551 SET error_code = NULL,
1552 error_explanation = NULL,
1553 processing_status = 'PROCESSED',
1554 csi_transaction_id = l_csi_trxn_rec.transaction_id
1555 WHERE a.processing_status = l_literal1
1556 AND a.transaction_line_id in (SELECT b.transaction_line_id
1557 FROM csi_t_transaction_lines b
1558 WHERE -- a.transaction_line_id = b.transaction_line_id AND -- Commented for Perf Bug 4311676
1559 b.source_transaction_id = p_rma_line_rec.line_id
1560 AND b.source_transaction_table = l_literal2);
1561 Exception when others Then
1562 debug('Txn details update failed');
1563 raise fnd_api.g_exc_unexpected_error;
1564 End;
1565
1566 END IF; -- td_tbl count > 0
1567
1568 EXCEPTION
1569 WHEN do_not_process THEN
1570 x_return_status := fnd_api.g_ret_sts_success; --bug no. 10007311
1571 WHEN fnd_api.g_exc_error THEN
1572 rollback to fulfill_rma_line;
1573 x_return_status := fnd_api.g_ret_sts_error;
1574 l_error_message := csi_t_gen_utility_pvt.dump_error_stack;
1575 debug(l_error_message);
1576 WHEN fnd_api.g_exc_unexpected_error THEN
1577 rollback to fulfill_rma_line;
1578 fnd_message.set_name('FND','FND_GENERIC_MESSAGE');
1579 fnd_message.set_token('MESSAGE',substr(sqlerrm,1,255));
1580 fnd_msg_pub.add;
1581 x_return_status := fnd_api.g_ret_sts_error;
1582 l_error_message := csi_t_gen_utility_pvt.dump_error_stack;
1583 debug(l_error_message);
1584 END fulfill_rma_line;
1585
1586 END csi_rma_fulfill_pub;