[Home] [Help]
PACKAGE BODY: APPS.CSI_RMA_FULFILL_PUB
Source
1 PACKAGE BODY CSI_RMA_FULFILL_PUB AS
2 /* $Header: csipirfb.pls 120.10 2006/02/08 13:50:59 srramakr noship $ */
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
713 BEGIN
714 x_return_status := fnd_api.g_ret_sts_success;
715 savepoint fulfill_rma_line;
716 fnd_msg_pub.initialize;
717
718 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
719
720 csi_t_gen_utility_pvt.dump_api_info(
721 p_api_name => 'fulfill_rma_line',
722 p_pkg_name => 'csi_rma_fulfill_pub');
723
724 BEGIN
725 --commented SQL below to make changes for the bug 4028827
726 /*
727 SELECT internal_party_id, ownership_override_at_txn
728 INTO l_internal_party_id, l_pty_override_flag
729 FROM csi_install_parameters;
730 */
731 l_internal_party_id := csi_datastructures_pub.g_install_param_rec.internal_party_id;
732 l_pty_override_flag := csi_datastructures_pub.g_install_param_rec.ownership_override_at_txn;
733
734 SELECT party_id
735 INTO l_src_txn_owner_pty_id
736 FROM hz_cust_accounts_all
737 WHERE cust_account_id = p_rma_line_rec.sold_to_org_id;
738
739 EXCEPTION
740 WHEN others THEN
741 fnd_message.set_name('FND','FND_GENERIC_MESSAGE');
742 fnd_message.set_token('MESSAGE',substr(sqlerrm,1,255));
743 fnd_msg_pub.add;
744 RAISE fnd_api.g_exc_error;
745 END;
746
747 l_csi_trxn_rec := p_csi_txn_rec;
748
749 IF p_line_dtl_tbl.COUNT > 0 THEN
750 FOR l_td_ind IN p_line_dtl_tbl.FIRST ..p_line_dtl_tbl.LAST
751 LOOP
752 IF p_line_dtl_tbl(l_td_ind).instance_id is NOT NULL THEN
753
754 px_trx_error_rec.instance_id := p_line_dtl_tbl(l_td_ind).instance_id;
755 px_trx_error_rec.serial_number := p_line_dtl_tbl(l_td_ind).serial_number;
756 px_trx_error_rec.lot_number := p_line_dtl_tbl(l_td_ind).lot_number;
757 l_txn_line_detail_query_rec.txn_line_detail_id :=
758 p_line_dtl_tbl(l_td_ind).txn_line_detail_id;
759
760 csi_t_txn_details_grp.get_transaction_details(
761 p_api_version => 1,
762 p_commit => fnd_api.g_false,
763 p_init_msg_list => fnd_api.g_true,
764 p_validation_level => fnd_api.g_valid_level_full,
765 p_txn_line_query_rec => l_txn_line_query_rec,
766 p_txn_line_detail_query_rec => l_txn_line_detail_query_rec,
767 x_txn_line_detail_tbl => l_line_dtl_tbl,
768 p_get_parties_flag => fnd_api.g_true,
769 x_txn_party_detail_tbl => l_pty_dtl_tbl,
770 p_get_pty_accts_flag => fnd_api.g_true,
771 x_txn_pty_acct_detail_tbl => l_pty_acct_tbl,
772 p_get_ii_rltns_flag => fnd_api.g_true,
773 x_txn_ii_rltns_tbl => l_ii_rltns_tbl,
774 p_get_org_assgns_flag => fnd_api.g_true,
775 x_txn_org_assgn_tbl => l_org_assgn_tbl,
776 p_get_ext_attrib_vals_flag => fnd_api.g_true,
777 x_txn_ext_attrib_vals_tbl => l_txn_ext_attrib_tbl,
778 p_get_csi_attribs_flag => fnd_api.g_false,
779 x_csi_ext_attribs_tbl => l_csi_ea_tbl,
780 p_get_csi_iea_values_flag => fnd_api.g_false,
781 x_csi_iea_values_tbl => l_csi_eav_tbl,
782 p_get_txn_systems_flag => fnd_api.g_false,
783 x_txn_systems_tbl => l_txn_systems_tbl,
784 x_return_status => l_return_status,
785 x_msg_count => l_msg_count,
786 x_msg_data => l_msg_data);
787
788 IF l_return_status <> fnd_api.g_ret_sts_success THEN
789 debug('Get transaction details failed for Fulfill RMA Line.');
790 RAISE fnd_api.g_exc_error;
791 END IF;
792
793 l_txn_line_dtl_rec := l_line_dtl_tbl(1);--always one record. Query by TLD ID. bug 3230999. changed all references
794 l_txn_line_dtl_rec.quantity := p_line_dtl_tbl(l_td_ind).quantity; -- changes for bug 3684010
795
796 debug(' txn_line_detail_tbl.count :'||l_line_dtl_tbl.count );
797 debug(' txn_party_detail_tbl.count :'||l_pty_dtl_tbl.count );
798 debug(' txn_pty_acct_dtl_tbl.count :'||l_pty_acct_tbl.count);
799 debug(' txn_org_assgn_tbl.count :'||l_org_assgn_tbl.count);
800 debug(' txn_ii_rltns_tbl.count :'||l_ii_rltns_tbl.count);
801 debug(' txn_ext_attrib_vals_tbl.count :'||l_txn_ext_attrib_tbl.count);
802 debug(' txn_systems_tbl.count :'||l_txn_systems_tbl.count);
803
804 IF l_debug_level >= 10 THEN
805
806 debug( 'Dumping all the processing tables...');
807
808 csi_t_gen_utility_pvt.dump_txn_tables(
809 p_ids_or_index_based => 'I',
810 p_line_detail_tbl => l_line_dtl_tbl,
811 p_party_detail_tbl => l_pty_dtl_tbl,
812 p_pty_acct_tbl => l_pty_acct_tbl,
813 p_ii_rltns_tbl => l_ii_rltns_tbl,
814 p_org_assgn_tbl => l_org_assgn_tbl,
815 p_ea_vals_tbl => l_txn_ext_attrib_tbl);
816
817 END IF;
818
819 IF l_ii_rltns_tbl.count > 0 THEN
820 FOR j in l_ii_rltns_tbl.first..l_ii_rltns_tbl.last LOOP
821
822 debug('Validating txn ii_relationships .. ' );
823
824 IF l_txn_line_dtl_rec.txn_line_detail_id = l_ii_rltns_tbl(j).object_id then
825 l_object_inst_id := l_txn_line_dtl_rec.instance_id;
826 exit;
827 END IF;
828 END LOOP;
829
830 Begin
831 Select object_id
832 Into l_curr_object_id
833 from csi_ii_relationships
834 Where object_id = l_object_inst_id
835 And sysdate between nvl(active_end_date, sysdate) and sysdate+1;
836 Exception
837 when no_data_found THEN
838 l_curr_object_id := -9999;
839 when others THEN
840 fnd_message.set_name('FND','FND_GENERIC_MESSAGE');
841 fnd_message.set_token('MESSAGE',substr(sqlerrm,1,255));
842 fnd_msg_pub.add;
843 raise fnd_api.g_exc_error;
844 End;
845 --Check if the object id is being updated, if so raise error
846
847 IF l_curr_object_id <> -9999 THEN
848 IF l_curr_object_id <> l_object_inst_id THEN
849 fnd_message.set_name('CSI','CSI_INT_OBJ_ID_NOT_ALLOW_UPD');
850 fnd_message.set_token('OBJECT_ID',l_object_inst_id);
851 fnd_msg_pub.add;
852 raise fnd_api.g_exc_error;
853 END IF;
854 END IF;
855 END IF;
856
857 /* Get the sub type information for each TLD */
858
859 csi_utl_pkg.get_sub_type_rec(
860 p_sub_type_id => l_txn_line_dtl_rec.sub_type_id,
861 p_trx_type_id => l_txn_type_id,
862 x_trx_sub_type_rec => l_txn_sub_type_rec,
863 x_return_status => l_return_status) ;
864
865 IF l_return_status <> fnd_api.g_ret_sts_success THEN
866 fnd_message.set_name('FND','FND_GENERIC_MESSAGE');
867 fnd_message.set_token('MESSAGE','Call to the routine csi_utl_pkg.get_sub_type_rec Failed.');
868 fnd_msg_pub.add;
869 raise fnd_api.g_exc_error;
870 END IF;
871
872 debug(' transaction_type_id :'||l_txn_sub_type_rec.trx_type_id );
873 debug(' default sub_type_id :'||l_txn_sub_type_rec.sub_type_id );
874
875 --Fix for bug 4243512
876 IF l_txn_sub_type_rec.src_change_owner = 'Y' THEN
877 fnd_message.set_name('CSI','CSI_SUB_TYPE_INVALID');
878 fnd_msg_pub.add;
879 raise fnd_api.g_exc_error;
880 END IF;
881 --end of fix.
882
883
884 IF l_pty_dtl_tbl.COUNT > 0 THEN
885 FOR l_ind IN l_pty_dtl_tbl.FIRST..l_pty_dtl_tbl.LAST
886 LOOP
887 IF l_pty_dtl_tbl(l_ind).relationship_type_code = 'OWNER' THEN
888 l_td_owner_id := l_pty_dtl_tbl(l_ind).party_source_id;
889 exit;
890 END IF;
891 END LOOP;
892 END IF;
893
894 l_instance_rec.instance_id := l_txn_line_dtl_rec.instance_id;
895
896 csi_item_instance_pub.get_item_instance_details(
897 p_api_version => 1.0,
898 p_commit => fnd_api.g_false,
899 p_init_msg_list => fnd_api.g_true,
900 p_validation_level => fnd_api.g_valid_level_full,
901 p_instance_rec => l_instance_rec,
902 p_get_parties => fnd_api.g_true,
903 p_party_header_tbl => l_party_header_tbl,
904 p_get_accounts => fnd_api.g_true,
905 p_account_header_tbl => l_account_header_tbl,
906 p_get_org_assignments => fnd_api.g_false,
907 p_org_header_tbl => l_org_assignments_tbl,
908 p_get_pricing_attribs => fnd_api.g_false,
909 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
910 p_get_ext_attribs => fnd_api.g_false,
911 p_ext_attrib_tbl => l_ext_attrib_tbl,
912 p_ext_attrib_def_tbl => l_ext_attrib_def_tbl,
913 p_get_asset_assignments => fnd_api.g_false,
914 p_asset_header_tbl => l_asset_assignment_tbl,
915 p_time_stamp => l_time_stamp,
916 x_return_status => l_return_status,
917 x_msg_count => l_msg_count,
918 x_msg_data => l_msg_data);
919
920 IF l_return_status <> fnd_api.g_ret_sts_success THEN
921 debug('Get item instance details failed for Fulfill RMA Line.');
922 RAISE fnd_api.g_exc_error;
923 END IF;
924
925 --Initialize the variable
926 l_upd_inst_qty := 'Y';
927
928 IF l_party_header_tbl.COUNT > 0 THEN
929 FOR p_ind IN l_party_header_tbl.FIRST..l_party_header_tbl.LAST
930 LOOP
931 IF l_party_header_tbl(p_ind).relationship_type_code = 'OWNER' THEN
932 l_inst_owner_pty_id := l_party_header_tbl(p_ind).party_id;
933 l_owner_pty_ip_id := l_party_header_tbl(p_ind).instance_party_id;
934 l_owner_pty_obj_ver_num := l_party_header_tbl(p_ind).object_version_number;
935 exit;
936 END IF;
937 END LOOP;
938 ELSE
939 debug('Instance Party not found. Instance:'||l_instance_rec.instance_id);
940 fnd_message.set_name('FND','FND_GENERIC_MESSAGE');
941 fnd_message.set_token('MESSAGE','Instance Party not found. Instance:'||l_instance_rec.instance_id);
942 fnd_msg_pub.add;
943 RAISE fnd_api.g_exc_error;
944 END IF;
945
946
947 -- Bug 4997771
948 IF ( l_instance_rec.location_type_code = 'INVENTORY'
949 AND
950 NVL(l_instance_rec.active_end_date,fnd_api.g_miss_date) = fnd_api.g_miss_date )
951 THEN
952
953 IF l_inst_owner_pty_id = l_internal_party_id THEN
954 fnd_message.set_name('CSI','CSI_INT_INST_REF_INVALID');
955 fnd_message.set_token('INSTANCE_ID',l_instance_rec.instance_id);
956 fnd_msg_pub.add;
957 RAISE fnd_api.g_exc_error;
958 ELSE
959 debug('Location type code is :'||l_instance_rec.location_type_code);
960 fnd_message.set_name('CSI', 'CSI_NON_RETURNABLE_INSTANCE');
961 fnd_message.set_token('LOC_TYPE_CODE', l_instance_rec.location_type_code);
962 fnd_msg_pub.add;
963 raise fnd_api.g_exc_error;
964 END IF;
965 END IF;
966
967
968
969 IF l_account_header_tbl.COUNT > 0 THEN
970 FOR a_ind IN l_account_header_tbl.FIRST..l_account_header_tbl.LAST
971 LOOP
972 IF l_account_header_tbl(a_ind).relationship_type_code = 'OWNER'
973 AND l_account_header_tbl(a_ind).instance_party_id = l_owner_pty_ip_id
974 THEN
975 l_inst_owner_acct_id := l_account_header_tbl(a_ind).party_account_id;
976 l_owner_acct_ipa_id := l_account_header_tbl(a_ind).ip_account_id;
977 l_owner_acct_obj_ver_num := l_account_header_tbl(a_ind).object_version_number;
978 l_inst_owner_acct_a_date := l_account_header_tbl(a_ind).active_end_date;
979 exit;
980 END IF;
981 END LOOP;
982 ELSE
983 debug('Instance Party Account not found. Instance:'||l_instance_rec.instance_id);
984 END IF;
985
986 debug('Instance owner party : '||l_inst_owner_pty_id);
987 debug('Instance owner account : '||l_inst_owner_acct_id);
988 debug('Source Txn. owner party : '||l_src_txn_owner_pty_id);
989 debug('Source Txn. owner account: '||p_rma_line_rec.sold_to_org_id);
990 debug('Txn detail owner party : '||l_td_owner_id);
991 debug('Internal party : '||l_internal_party_id);
992
993 IF l_inst_owner_pty_id = l_internal_party_id THEN
994 IF nvl(l_txn_sub_type_rec.src_change_owner, 'N') = 'Y' THEN
995 fnd_message.set_name('CSI','CSI_INT_INST_REF_INVALID');
996 fnd_message.set_token('INSTANCE_ID',l_instance_rec.instance_id);
997 fnd_msg_pub.add;
998 RAISE fnd_api.g_exc_error;
999 END IF;
1000 ELSIF l_inst_owner_pty_id <> l_src_txn_owner_pty_id THEN
1001 IF l_pty_override_flag = 'Y' THEN
1002
1003 l_upd_parties_tbl(1).instance_party_id := l_owner_pty_ip_id;
1004 l_upd_parties_tbl(1).object_version_number := l_owner_pty_obj_ver_num;
1005
1006
1007 l_upd_parties_tbl(1).party_source_table := 'HZ_PARTIES';
1008 l_upd_parties_tbl(1).party_id := l_src_txn_owner_pty_id;
1009 l_upd_parties_tbl(1).relationship_type_code := 'OWNER';
1010 l_upd_parties_tbl(1).contact_flag := 'N';
1011 l_upd_parties_tbl(1).call_contracts := fnd_api.g_false;
1012
1013 l_upd_pty_accts_tbl(1).ip_account_id := l_owner_acct_ipa_id;
1014 l_upd_pty_accts_tbl(1).object_version_number:= l_owner_acct_obj_ver_num;
1015 l_upd_pty_accts_tbl(1).parent_tbl_index := 1;
1016 l_upd_pty_accts_tbl(1).party_account_id := p_rma_line_rec.sold_to_org_id; -- bug 3693594
1017 l_upd_pty_accts_tbl(1).relationship_type_code := 'OWNER';
1018 l_upd_pty_accts_tbl(1).call_contracts := fnd_api.g_false;
1019
1020 /* Commented the call as part of fix for Bug 2733128. Added call to Update_Item_Instance instead
1021 csi_t_gen_utility_pvt.dump_api_info(
1022 p_pkg_name => 'csi_party_relationships_pub',
1023 p_api_name => 'update_inst_party_relationship');
1024
1025 csi_party_relationships_pub.update_inst_party_relationship (
1026 p_api_version => 1.0,
1027 p_commit => fnd_api.g_false,
1028 p_init_msg_list => fnd_api.g_true,
1029 p_validation_level => fnd_api.g_valid_level_full,
1030 p_party_tbl => l_upd_parties_tbl,
1031 p_party_account_tbl => l_upd_pty_accts_tbl,
1032 p_txn_rec => l_csi_trxn_rec,
1033 x_return_status => l_return_status,
1034 x_msg_count => l_msg_count,
1035 x_msg_data => l_msg_data);
1036 */
1037 -- Transfer the Ownership first to the new RMA Customer and then process
1038 -- the RMA as a Normal one.
1039 -- Begin code fix as part of fix for Bug 2733128.
1040
1041 l_chg_instance_rec.instance_id := l_instance_rec.instance_id;
1042 l_chg_instance_rec.object_version_number := l_instance_rec.object_version_number;
1043 l_chg_instance_rec.active_end_date := NUll;
1044 -- End code fix as part of fix for Bug 2733128.
1045
1046 csi_t_gen_utility_pvt.dump_api_info(
1047 p_pkg_name => 'csi_item_instance_pub',
1048 p_api_name => 'update_item_instance');
1049
1050 csi_item_instance_pub.update_item_instance(
1051 p_api_version => 1.0,
1052 p_commit => fnd_api.g_false,
1053 p_init_msg_list => fnd_api.g_true,
1054 p_validation_level => fnd_api.g_valid_level_full,
1055 p_instance_rec => l_chg_instance_rec,
1056 p_ext_attrib_values_tbl => l_chg_ext_attrib_val_tbl,
1057 p_party_tbl => l_upd_parties_tbl,
1058 p_account_tbl => l_upd_pty_accts_tbl,
1059 p_pricing_attrib_tbl => l_chg_pricing_attribs_tbl,
1060 p_org_assignments_tbl => l_chg_org_units_tbl,
1061 p_txn_rec => l_csi_trxn_rec,
1062 p_asset_assignment_tbl => l_chg_inst_asset_tbl,
1063 x_instance_id_lst => l_chg_inst_id_lst,
1064 x_return_status => l_return_status,
1065 x_msg_count => l_msg_count,
1066 x_msg_data => l_msg_data);
1067
1068 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1069 RAISE fnd_api.g_exc_error;
1070 END IF;
1071
1072 debug('Ownership Changed Successfully.');
1073 ELSE
1074 fnd_message.set_name('CSI','CSI_RMA_OWNER_MISMATCH');
1075 fnd_message.set_token('INSTANCE_ID', l_instance_rec.instance_id );
1076 fnd_message.set_token('OLD_PARTY_ID', l_inst_owner_pty_id );
1077 fnd_message.set_token('NEW_PARTY_ID', l_src_txn_owner_pty_id );
1078 fnd_msg_pub.add;
1079 RAISE fnd_api.g_exc_error;
1080 END IF;
1081 END IF;
1082 /* Bug 3746600. Since Cancellation is always a expiry now, we do not need the code below. commenting.
1083 ELSE -- inst party = source txn party AND instance is valid - Normal case
1084 IF ( l_txn_line_dtl_rec.reference_source_id is NOT NULL
1085 AND l_txn_line_dtl_rec.reference_source_line_id is NOT NULL)
1086 AND nvl(p_rma_line_rec.shippable_flag, 'N') = 'Y'
1087 THEN
1088 Begin
1089 Select inventory_item_id, flow_status_code, sold_to_org_id
1090 Into l_orig_rma_item_id, l_orig_rma_status, l_orig_rma_owner_id
1091 From oe_order_lines_all
1092 Where line_id = l_txn_line_dtl_rec.reference_source_line_id
1093 And header_id = l_txn_line_dtl_rec.reference_source_id;
1094 Exception When others Then
1095 l_orig_rma_ref_valid := 'N';
1096 End;
1097
1098 IF l_orig_rma_ref_valid = 'Y' THEN
1099 IF ( l_orig_rma_owner_id = l_inst_owner_acct_id)
1100 AND (l_orig_rma_item_id = l_instance_rec.inventory_item_id)
1101 THEN
1102 IF (WF_ENGINE.ACTIVITY_EXIST_IN_PROCESS(
1103 'OEOL' -- ITEM_TYPE
1104 ,to_char(l_txn_line_dtl_rec.reference_source_line_id) -- ITEM_KEY
1105 ,'OEOL' -- ACTIVITY_ITEM_TYPE
1106 ,'RMA_RECEIVING_SUB' -- ACTIVITY
1107 ))
1108 THEN
1109 l_upd_inst_qty := 'N';
1110 debug('This Line had a Receiving Node and hence would have had updated IB...');
1111 debug('Not updating IB Quantity ...');
1112
1113 END IF;
1114 ELSE
1115 debug('Ref. RMA attributes do not match. Updating IB anyway ...');
1116 END IF;
1117 ELSE
1118 debug('Could not fime Ref. RMA details . Updating IB anyway ...');
1119 END IF;
1120
1121 IF NOT (sysdate between nvl(l_instance_rec.active_end_date, sysdate)
1122 AND sysdate+1) THEN
1123 IF l_instance_rec.quantity <= 0 THEN
1124 IF l_instance_rec.quantity = 0 THEN
1125 l_u_instance_rec.active_end_date := NULL;
1126 l_u_instance_rec.quantity := ABS(l_txn_line_dtl_rec.quantity);
1127 Begin
1128 SELECT object_version_number
1129 INTO l_u_instance_rec.object_version_number
1130 FROM csi_item_instances
1131 WHERE instance_id = l_instance_rec.instance_id;
1132 Exception when others then
1133 debug('Fetch instance details failed ...');
1134 fnd_message.set_name('FND','FND_GENERIC_MESSAGE');
1135 fnd_message.set_token('MESSAGE',substr(sqlerrm,1,255));
1136 fnd_msg_pub.add;
1137 raise fnd_api.g_exc_unexpected_error;
1138 End;
1139
1140 csi_t_gen_utility_pvt.dump_api_info(
1141 p_pkg_name => 'csi_item_instance_pub',
1142 p_api_name => 'update_item_instance');
1143
1144 csi_item_instance_pub.update_item_instance(
1145 p_api_version => 1.0,
1146 p_commit => fnd_api.g_false,
1147 p_init_msg_list => fnd_api.g_true,
1148 p_validation_level => fnd_api.g_valid_level_full,
1149 p_instance_rec => l_u_instance_rec,
1150 p_party_tbl => l_u_parties_tbl,
1151 p_account_tbl => l_u_pty_accts_tbl,
1152 p_org_assignments_tbl => l_u_org_units_tbl,
1153 p_ext_attrib_values_tbl => l_u_ea_values_tbl,
1154 p_pricing_attrib_tbl => l_u_pricing_tbl,
1155 p_asset_assignment_tbl => l_u_assets_tbl,
1156 p_txn_rec => l_csi_trxn_rec,
1157 x_instance_id_lst => l_u_instance_ids_list,
1158 x_return_status => l_return_status,
1159 x_msg_count => l_msg_count,
1160 x_msg_data => l_msg_data);
1161
1162 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1163 RAISE fnd_api.g_exc_error;
1164 END IF;
1165 ELSE
1166 debug('Instance Quantity is -ve already. Error??');
1167 l_upd_inst_qty := 'N';
1168 --RAISE fnd_api.g_exc_error;
1169 END IF;
1170 ELSE --Not sure about the case??
1171 l_p_instances_tbl(l_td_ind).active_end_date := NULL;
1172 l_upd_inst_qty := 'N';
1173 END IF;
1174 END IF; -- end dated instance
1175
1176 END IF;
1177 END IF;
1178 Bug 3746600 Changes End. */
1179
1180 IF p_rma_line_rec.ship_from_org_id is NOT NULL THEN
1181 l_dest_location_rec.inv_organization_id := p_rma_line_rec.ship_from_org_id;
1182 ELSIF p_rma_line_rec.sold_from_org_id is NOT NULL THEN
1183 l_dest_location_rec.inv_organization_id := p_rma_line_rec.sold_from_org_id;
1184 ELSIF l_txn_line_dtl_rec.inv_organization_id is NOT NULL THEN
1185 l_dest_location_rec.inv_organization_id := l_txn_line_dtl_rec.inv_organization_id;
1186 ELSE
1187 l_dest_location_rec.inv_organization_id := l_instance_rec.vld_organization_id;
1188 END IF;
1189
1190 Begin
1191 Select serial_number_control_code
1192 into l_item_srl_code
1193 from mtl_system_items_b
1194 where inventory_item_id = l_txn_line_dtl_rec.inventory_item_id
1195 and organization_id = l_dest_location_rec.inv_organization_id;--l_txn_line_dtl_rec.inv_organization_id;
1196 -- bug 3230999. since OM is always passing master inv.
1197 Exception when others Then
1198 debug('Could not determine serial control policy?');
1199 fnd_message.set_name('FND','FND_GENERIC_MESSAGE');
1200 fnd_message.set_token('MESSAGE',substr(sqlerrm,1,255));
1201 fnd_msg_pub.add;
1202 RAISE fnd_api.g_exc_unexpected_error;
1203 End;
1204
1205 l_p_instances_tbl(l_td_ind).instance_id := l_txn_line_dtl_rec.instance_id;
1206 IF ( nvl(l_instance_rec.active_end_date,fnd_api.g_miss_date) <> fnd_api.g_miss_date
1207 AND l_instance_rec.active_end_date < sysdate ) THEN -- Added this new check, IF as part of 3746600
1208 IF l_instance_rec.instance_usage_code <> 'IN_RELATIONSHIP' THEN
1209 -- ONLY excluding Components since they get expired along with their parent in a config cancellation
1210 fnd_message.set_name('CSI','CSI_TXN_INVALID_INST_REF');
1211 fnd_message.set_token('INSTANCE_ID',l_instance_rec.instance_id);
1212 fnd_msg_pub.add;
1213 RAISE fnd_api.g_exc_error;
1214 END IF;
1215 ELSIF l_item_srl_code = 1 THEN
1216 IF abs(l_txn_line_dtl_rec.quantity) > l_instance_rec.quantity THEN
1217 -- check if the quantity is greater than the instance quantity
1218 fnd_message.set_name('CSI','CSI_INT_QTY_CHK_FAILED');
1219 fnd_message.set_token('INSTANCE_ID',l_instance_rec.instance_id);
1220 fnd_msg_pub.add;
1221 RAISE fnd_api.g_exc_error;
1222 ELSIF l_instance_rec.quantity > abs(l_txn_line_dtl_rec.quantity) THEN -- need to split the source cust prod first
1223 debug('Original Instance Quantity: '||l_instance_rec.quantity);
1224 l_quantity1 := l_instance_rec.quantity - abs(l_txn_line_dtl_rec.quantity) ;
1225
1226 l_split_src_inst_rec.instance_id := l_instance_rec.instance_id;
1227 l_csi_trxn_rec.split_reason_code := 'PARTIAL_RETURN';
1228
1229 csi_t_gen_utility_pvt.dump_api_info(
1230 p_pkg_name => 'csi_item_instance_pvt',
1231 p_api_name => 'split_item_instance');
1232
1233 csi_item_instance_pvt.split_item_instance (
1234 p_api_version => 1.0,
1235 p_commit => fnd_api.g_false,
1236 p_init_msg_list => fnd_api.g_true,
1237 p_validation_level => fnd_api.g_valid_level_full,
1238 p_source_instance_rec => l_split_src_inst_rec,
1239 p_quantity1 => l_quantity1,
1240 p_quantity2 => abs(l_txn_line_dtl_rec.quantity),
1241 p_copy_ext_attribs => fnd_api.g_true,
1242 p_copy_org_assignments => fnd_api.g_true,
1243 p_copy_parties => fnd_api.g_true,
1244 p_copy_accounts => fnd_api.g_true,
1245 p_copy_asset_assignments => fnd_api.g_true,
1246 p_copy_pricing_attribs => fnd_api.g_true,
1247 p_txn_rec => l_csi_trxn_rec,
1248 x_new_instance_rec => l_split_new_inst_rec,
1249 x_return_status => l_return_status,
1250 x_msg_count => l_msg_count,
1251 x_msg_data => l_msg_data);
1252
1253 IF NOT(l_return_status = fnd_api.g_ret_sts_success) THEN
1254 debug('csi_item_instance_pvt.split_item_instance raised errors');
1255 raise fnd_api.g_exc_error;
1256 END IF;
1257
1258 l_p_instances_tbl(l_td_ind).instance_id := l_split_new_inst_rec.instance_id ;
1259 l_p_instances_tbl(l_td_ind).object_version_number := l_split_new_inst_rec.object_version_number;
1260 debug('New Instance ID: '||l_split_new_inst_rec.instance_id
1261 ||' New Instance Qty.: '||l_split_new_inst_rec.quantity);
1262 ELSE -- bug 3746600
1263 debug('Complete Cancellation, Expiring: '||l_instance_rec.instance_id);
1264 END IF;
1265 END IF;
1266 -- update the transaction line detail table with the inprocess status
1267 l_u_txn_line_rec.transaction_line_id := p_line_dtl_tbl(l_td_ind).transaction_line_id;
1268
1269 l_u_line_dtl_tbl(l_td_ind).txn_line_detail_id := p_line_dtl_tbl(l_td_ind).txn_line_detail_id;
1270 l_u_line_dtl_tbl(l_td_ind).transaction_line_id := p_line_dtl_tbl(l_td_ind).transaction_line_id;
1271 l_u_line_dtl_tbl(l_td_ind).processing_status := 'IN_PROCESS';
1272
1273 END IF; -- l_td.instance_id is not null
1274
1275 -- Building process txn tables
1276 l_csi_trxn_rec.txn_sub_type_id := l_txn_line_dtl_rec.sub_type_id;
1277 l_p_instances_tbl(l_td_ind).inventory_item_id := l_txn_line_dtl_rec.inventory_item_id;
1278 l_p_instances_tbl(l_td_ind).vld_organization_id := l_dest_location_rec.inv_organization_id;
1279 l_p_instances_tbl(l_td_ind).last_oe_rma_line_id := p_rma_line_rec.line_id;
1280
1281 IF l_txn_line_dtl_rec.source_transaction_flag = 'Y' THEN
1282 l_p_instances_tbl(l_td_ind).ib_txn_segment_flag := 'S';
1283 l_p_instances_tbl(l_td_ind).last_txn_line_detail_id := l_txn_line_dtl_rec.txn_line_detail_id;
1284 ELSE
1285 l_p_instances_tbl(l_td_ind).ib_txn_segment_flag := 'N';
1286 END IF;
1287
1288 -- debug('l_upd_inst_qty :'||l_upd_inst_qty); bug 3746600
1289
1290 IF l_item_srl_code <> 1 THEN
1291 -- serialized. Not sure of a case of serialized and non shippable item!!
1292 l_p_instances_tbl(l_td_ind).quantity := 1; -- the quantity is always 1
1293 l_p_instances_tbl(l_td_ind).active_end_date := sysdate; -- so that the serialized instance is expired
1294 -- ELSIF l_upd_inst_qty = 'N' THEN bug 3746600
1295 ELSE
1296 l_p_instances_tbl(l_td_ind).quantity := 0; -- so that the quantity is not reduced to 0
1297 l_p_instances_tbl(l_td_ind).active_end_date := sysdate; -- so that the instance is expired
1298 -- ELSE bug 3746600
1299 -- l_p_instances_tbl(l_td_ind).quantity := ABS(l_txn_line_dtl_rec.quantity);
1300 END IF;
1301
1302 -- this is to make sure RMA fulfillment does NOT ever change the location to INV
1303 l_p_instances_tbl(l_td_ind).inv_organization_id := NULL;
1304 l_p_instances_tbl(l_td_ind).inv_subinventory_name := NULL;
1305 l_p_instances_tbl(l_td_ind).inv_locator_id := NULL;
1306
1307 l_pty_ind := 1;
1308 l_pa_ind := 1;
1309
1310 IF l_pty_dtl_tbl.COUNT > 0 THEN
1311 FOR l_pd_ind IN l_pty_dtl_tbl.FIRST .. l_pty_dtl_tbl.LAST
1312 LOOP
1313 debug('Building TD party rec '||l_pty_ind||' for process transaction.');
1314
1315 l_p_parties_tbl(l_pty_ind).parent_tbl_index := l_td_ind;
1316 l_p_parties_tbl(l_pty_ind).party_source_table :=
1317 l_pty_dtl_tbl(l_pd_ind).party_source_table;
1318 l_p_parties_tbl(l_pty_ind).party_id :=
1319 l_pty_dtl_tbl(l_pd_ind).party_source_id;
1320 l_p_parties_tbl(l_pty_ind).instance_party_id :=
1321 l_pty_dtl_tbl(l_pd_ind).instance_party_id;
1322 l_p_parties_tbl(l_pty_ind).relationship_type_code :=
1323 l_pty_dtl_tbl(l_pd_ind).relationship_type_code;
1324 l_p_parties_tbl(l_pty_ind).contact_flag :=
1325 l_pty_dtl_tbl(l_pd_ind).contact_flag;
1326
1327 IF l_pty_acct_tbl.COUNT > 0 THEN
1328
1329 FOR l_pad_ind IN l_pty_acct_tbl.FIRST .. l_pty_acct_tbl.LAST
1330 LOOP
1331 IF l_pty_acct_tbl(l_pad_ind).txn_party_detail_id = l_pty_dtl_tbl(l_pd_ind).txn_party_detail_id THEN
1332
1333 debug('Building TD account rec '||l_pa_ind||' for process transaction.');
1334
1335 l_p_ip_accounts_tbl(l_pa_ind).parent_tbl_index := l_pty_ind;
1336 l_p_ip_accounts_tbl(l_pa_ind).party_account_id := l_pty_acct_tbl(l_pad_ind).account_id;
1337 l_p_ip_accounts_tbl(l_pa_ind).ip_account_id := l_pty_acct_tbl(l_pad_ind).ip_account_id;
1338 l_p_ip_accounts_tbl(l_pa_ind).relationship_type_code := l_pty_acct_tbl(l_pad_ind).relationship_type_code;
1339 l_p_ip_accounts_tbl(l_pa_ind).bill_to_address := l_pty_acct_tbl(l_pad_ind).bill_to_address_id;
1340 l_p_ip_accounts_tbl(l_pa_ind).ship_to_address := l_pty_acct_tbl(l_pad_ind).ship_to_address_id;
1341 l_p_ip_accounts_tbl(l_pa_ind).active_end_date := l_pty_acct_tbl(l_pad_ind).active_end_date;
1342
1343 l_pa_ind := l_pa_ind + 1;
1344 END IF;
1345 END LOOP; -- pty_acct_tbl loop
1346 END IF; -- l_pty_acct_tbl.count > 0
1347
1348 l_pty_ind := l_pty_ind + 1;
1349 END LOOP; -- pty_dtl_tbl loop
1350 END IF; -- pty_dtl_tbl.count > 0
1351 END LOOP; -- td loop
1352
1353 -- updating txn dtls to IN_PROCESS. moved this code down here from inside the loop
1354 csi_t_txn_details_grp.update_txn_line_dtls(
1355 p_api_version => 1.0,
1356 p_commit => fnd_api.g_false,
1357 p_init_msg_list => fnd_api.g_true,
1358 p_validation_level => fnd_api.g_valid_level_full,
1359 p_txn_line_rec => l_u_txn_line_rec,
1360 p_txn_line_detail_tbl => l_u_line_dtl_tbl,
1361 px_txn_ii_rltns_tbl => l_u_ii_rltns_tbl,
1362 px_txn_party_detail_tbl => l_u_pty_dtl_tbl,
1363 px_txn_pty_acct_detail_tbl => l_u_pty_acct_tbl,
1364 px_txn_org_assgn_tbl => l_u_org_assgn_tbl,
1365 px_txn_ext_attrib_vals_tbl => l_u_eav_tbl,
1366 x_return_status => l_return_status,
1367 x_msg_count => l_msg_count,
1368 x_msg_data => l_msg_data);
1369
1370 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1371 debug('Update txn line dtls failed for Fulfill RMA Line.');
1372 RAISE fnd_api.g_exc_error;
1373 END IF;
1374 --
1375 -- srramakr TSO with Equipment
1376 -- For Shippable Items, if RMA fulfillment is performed then we need to remove the config keys
1377 -- of the underlying instance
1378 --
1379 IF NVL(p_rma_line_rec.shippable_flag,'N') = 'Y' THEN
1380 IF l_p_instances_tbl.count > 0 THEN
1381 FOR J in l_p_instances_tbl.FIRST .. l_p_instances_tbl.LAST LOOP
1382 IF l_p_instances_tbl(J).instance_id IS NOT NULL AND
1383 l_p_instances_tbl(J).instance_id <> FND_API.G_MISS_NUM THEN
1384 l_lock_id := NULL;
1385 l_lock_status := NULL;
1386 l_locked_inst_rev_num := NULL;
1387 l_p_instances_tbl(J).config_inst_hdr_id := null;
1388 l_p_instances_tbl(J).config_inst_item_id := null;
1389 l_p_instances_tbl(J).config_inst_rev_num := null;
1390 l_locked := FALSE;
1391 --
1392 Begin
1393 select cil.lock_id,cil.lock_status,
1394 cil.config_inst_rev_num
1395 into l_lock_id,l_lock_status,
1396 l_locked_inst_rev_num
1397 from CSI_ITEM_INSTANCE_LOCKS cil
1398 where cil.instance_id = l_p_instances_tbl(J).instance_id
1399 and cil.lock_status <> 0;
1400 --
1401 l_locked := TRUE;
1402 Exception
1403 when no_data_found then
1404 l_locked := FALSE;
1405 End;
1406 --
1407 select config_inst_hdr_id,config_inst_item_id,config_inst_rev_num,
1408 instance_usage_code,active_end_date
1409 into l_p_instances_tbl(J).config_inst_hdr_id,
1410 l_p_instances_tbl(J).config_inst_item_id,
1411 l_p_instances_tbl(J).config_inst_rev_num,
1412 l_p_instances_tbl(J).instance_usage_code,
1413 l_p_instances_tbl(J).active_end_date
1414 from CSI_ITEM_INSTANCES
1415 where instance_id = l_p_instances_tbl(J).instance_id;
1416 --
1417 IF l_locked = TRUE THEN
1418 -- Instance is in Locked Status
1419 l_unlock_inst_tbl.DELETE;
1420 l_unlock_inst_tbl(1).source_application_id := 542;
1421 l_unlock_inst_tbl(1).lock_id := l_lock_id;
1422 l_unlock_inst_tbl(1).lock_status := l_lock_status;
1423 l_unlock_inst_tbl(1).instance_id := l_p_instances_tbl(J).instance_id;
1424 l_unlock_inst_tbl(1).source_txn_header_ref := l_csi_trxn_rec.source_header_ref_id;
1425 l_unlock_inst_tbl(1).source_txn_line_ref1 := l_csi_trxn_rec.source_line_ref_id;
1426 --
1427 debug('Calling Unlock Item Instances for Instance Id '||to_char(l_p_instances_tbl(J).instance_id));
1428 CSI_ITEM_INSTANCE_GRP.unlock_item_instances
1429 (
1430 p_api_version => 1.0
1431 ,p_commit => l_commit
1432 ,p_init_msg_list => l_init_msg_list
1433 ,p_validation_level => l_validation_level
1434 ,p_config_tbl => l_unlock_inst_tbl
1435 ,x_return_status => l_return_status
1436 ,x_msg_count => l_msg_count
1437 ,x_msg_data => l_msg_data
1438 );
1439 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1440 debug('Unlock Item Instances routine failed.');
1441 RAISE fnd_api.g_exc_error;
1442 END IF;
1443 --
1444 -- Update any pending TLD for the same config keys (fetched from lock table)
1445 -- with the instance_id so that when regular fulfillment happens for this
1446 -- tangible item (DISCONNECT), only the order line_id will be updated in the item instance
1447 Update CSI_T_TXN_LINE_DETAILS
1448 Set changed_instance_id = l_p_instances_tbl(J).instance_id
1449 ,overriding_csi_txn_id = l_csi_trxn_rec.transaction_id
1450 Where config_inst_hdr_id = l_p_instances_tbl(J).config_inst_hdr_id
1451 and config_inst_item_id = l_p_instances_tbl(J).config_inst_item_id
1452 and config_inst_rev_num = l_locked_inst_rev_num
1453 and nvl(processing_status,'$#$') = 'SUBMIT';
1454 END IF; -- if Locked
1455 --
1456 IF nvl(l_p_instances_tbl(J).instance_usage_code,'$#$') = 'IN_RELATIONSHIP' AND
1457 nvl(l_p_instances_tbl(J).active_end_date,(sysdate+1)) > sysdate AND
1458 l_p_instances_tbl(J).config_inst_hdr_id IS NOT NULL AND
1459 l_p_instances_tbl(J).config_inst_item_id IS NOT NULL AND
1460 l_p_instances_tbl(J).config_inst_rev_num IS NOT NULL THEN
1461 -- Call CZ API for Notification
1462 debug('Calling CZ_IB_TSO_GRP.Remove_Returned_Config_Item...');
1463 CZ_IB_TSO_GRP.Remove_Returned_Config_Item
1464 ( p_instance_hdr_id => l_p_instances_tbl(J).config_inst_hdr_id,
1465 p_instance_rev_nbr => l_p_instances_tbl(J).config_inst_rev_num,
1466 p_returned_config_item_id => l_p_instances_tbl(J).config_inst_item_id,
1467 p_locked_instance_rev_nbr => l_locked_inst_rev_num,
1468 p_application_id => 542,
1469 p_config_eff_date => sysdate,
1470 x_validation_status => l_validation_status,
1471 x_return_status => l_return_status,
1472 x_msg_count => l_msg_count,
1473 x_msg_data => l_msg_data
1474 );
1475 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1476 debug('Remove_Returned_Config_Item routine failed.');
1477 RAISE fnd_api.g_exc_error;
1478 END IF;
1479 END IF;
1480 END IF;
1481 --
1482 -- Nullify the Config Keys
1483 l_p_instances_tbl(J). CONFIG_INST_HDR_ID := NULL;
1484 l_p_instances_tbl(J). CONFIG_INST_REV_NUM := NULL;
1485 l_p_instances_tbl(J). CONFIG_INST_ITEM_ID := NULL;
1486 END LOOP;
1487 END IF;
1488 END IF; -- End of shippable_flag check
1489 --
1490 csi_process_txn_grp.process_transaction (
1491 p_api_version => l_api_version,
1492 p_commit => l_commit,
1493 p_init_msg_list => l_init_msg_list,
1494 p_validation_level => l_validation_level,
1495 p_validate_only_flag => l_validate_only_flag,
1496 p_in_out_flag => l_in_out_flag,
1497 p_dest_location_rec => l_dest_location_rec,
1498 p_txn_rec => l_csi_trxn_rec,
1499 p_instances_tbl => l_p_instances_tbl,
1500 p_i_parties_tbl => l_p_parties_tbl,
1501 p_ip_accounts_tbl => l_p_ip_accounts_tbl,
1502 p_org_units_tbl => l_p_org_units_tbl,
1503 p_ext_attrib_vlaues_tbl => l_p_ext_attrib_values_tbl,
1504 p_pricing_attribs_tbl => l_p_pricing_attribs_tbl,
1505 p_instance_asset_tbl => l_p_instance_asset_tbl,
1506 p_ii_relationships_tbl => l_p_ii_relationships_tbl,
1507 px_txn_error_rec => px_trx_error_rec,
1508 x_return_status => l_return_status,
1509 x_msg_count => l_msg_count,
1510 x_msg_data => l_msg_data );
1511
1512 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1513 debug('Process Transaction call failed for Fulfill RMA Line.');
1514 RAISE fnd_api.g_exc_error;
1515 END IF;
1516 Begin
1517
1518 --Assign the literals.. bug 4311676
1519 l_literal1 := 'IN_PROCESS';
1520 l_literal2 := 'OE_ORDER_LINES_ALL';
1521
1522 UPDATE csi_t_txn_line_details a
1523 SET error_code = NULL,
1524 error_explanation = NULL,
1525 processing_status = 'PROCESSED',
1526 csi_transaction_id = l_csi_trxn_rec.transaction_id
1527 WHERE a.processing_status = l_literal1
1528 AND a.transaction_line_id in (SELECT b.transaction_line_id
1529 FROM csi_t_transaction_lines b
1530 WHERE -- a.transaction_line_id = b.transaction_line_id AND -- Commented for Perf Bug 4311676
1531 b.source_transaction_id = p_rma_line_rec.line_id
1532 AND b.source_transaction_table = l_literal2);
1533 Exception when others Then
1534 debug('Txn details update failed');
1535 raise fnd_api.g_exc_unexpected_error;
1536 End;
1537
1538 END IF; -- td_tbl count > 0
1539
1540 EXCEPTION
1541 WHEN fnd_api.g_exc_error THEN
1542 rollback to fulfill_rma_line;
1543 x_return_status := fnd_api.g_ret_sts_error;
1544 l_error_message := csi_t_gen_utility_pvt.dump_error_stack;
1545 debug(l_error_message);
1546 WHEN fnd_api.g_exc_unexpected_error THEN
1547 rollback to fulfill_rma_line;
1548 fnd_message.set_name('FND','FND_GENERIC_MESSAGE');
1549 fnd_message.set_token('MESSAGE',substr(sqlerrm,1,255));
1550 fnd_msg_pub.add;
1551 x_return_status := fnd_api.g_ret_sts_error;
1552 l_error_message := csi_t_gen_utility_pvt.dump_error_stack;
1553 debug(l_error_message);
1554 END fulfill_rma_line;
1555
1556 END csi_rma_fulfill_pub;