[Home] [Help]
PACKAGE BODY: APPS.CSI_RMA_RECEIPT_PUB
Source
1 package body csi_rma_receipt_pub AS
2 /* $Header: csipirmb.pls 120.17.12020000.5 2012/11/09 10:43:39 mvaradam ship $*/
3
4 /* local debug procedure */
5 PROCEDURE debug(
6 p_message IN varchar2)
7 IS
8 BEGIN
9 csi_t_gen_utility_pvt.add(p_message);
10 END debug;
11
12 /* local api log procedure */
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_receipt_pub');
20 END api_log;
21
22
23 /* local debug dump routines */
24 PROCEDURE dump_item_control_rec(
25 p_item_control_rec IN item_control_rec)
26 IS
27 l_rec item_control_rec;
28 BEGIN
29
30 l_rec := p_item_control_rec;
31
32 debug('Dumping values for item_control_rec:');
33
34 debug(' inventory_item_id : '||l_rec.inventory_item_id);
35 debug(' organization_id : '||l_rec.organization_id);
36 debug(' primary_uom_code : '||l_rec.primary_uom_code);
37 debug(' serial_control_code : '||l_rec.serial_control_code);
38 debug(' lot_control_code : '||l_rec.lot_control_code);
39 debug(' rev_control_code : '||l_rec.revision_control_code);
40 debug(' bom_item_type : '||l_rec.bom_item_type);
41
42 END dump_item_control_rec;
43
44 /* local debug dump routines */
45 PROCEDURE dump_mtl_txn_rec(
46 p_mtl_txn_rec IN mtl_txn_rec)
47 IS
48 l_rec mtl_txn_rec;
49 BEGIN
50 l_rec := p_mtl_txn_rec;
51 debug('Dumping values for mtl_transaction_recs:');
52 debug(' transaction_id : '||l_rec.transaction_id);
53 debug(' transaction_type_id : '||l_rec.transaction_type_id);
54 debug(' oe_line_id : '||l_rec.oe_line_id);
55 debug(' inventory_item_id : '||l_rec.inventory_item_id);
56 debug(' item_revision : '||l_rec.revision);
57 debug(' organization_id : '||l_rec.organization_id);
58 debug(' subinventory : '||l_rec.subinventory_code);
59 debug(' locator_id : '||l_rec.locator_id);
60 debug(' mmt_primary_quantity : '||l_rec.mmt_primary_quantity);
61 debug(' serial_number : '||l_rec.serial_number);
62 debug(' lot_number : '||l_rec.lot_number);
63 debug(' lot_primary_quantity : '||l_rec.lot_primary_quantity);
64 debug(' instance_quantity : '||l_rec.instance_quantity);
65 END dump_mtl_txn_rec;
66
67 /* local debug dump routines */
68 PROCEDURE dump_txn_status_tbl(
69 p_mtl_txn_tbl IN mtl_txn_tbl)
70 IS
71 l_tbl mtl_txn_tbl;
72 BEGIN
73 l_tbl := p_mtl_txn_tbl;
74 IF l_tbl.COUNT > 0 THEN
75 debug('ITEM REV LOT SERIAL SUBINV LOC INST QTY V P');
76 debug('------- ---- ------------ --------------- --------------- ------ ------- ------- - -');
77 FOR l_ind IN l_tbl.FIRST .. l_tbl.LAST
78 LOOP
79 debug(rpad(nvl(to_char(l_tbl(l_ind).inventory_item_id), ' '), 8,' ') ||
80 rpad(nvl(l_tbl(l_ind).revision, ' '), 5, ' ') ||
81 rpad(nvl(l_tbl(l_ind).lot_number, ' '), 13, ' ') ||
82 rpad(nvl(l_tbl(l_ind).serial_number, ' '), 16, ' ') ||
83 rpad(nvl(l_tbl(l_ind).subinventory_code, ' '), 16, ' ') ||
84 rpad(nvl(to_char(l_tbl(l_ind).locator_id), ' '), 7, ' ') ||
85 rpad(nvl(to_char(l_tbl(l_ind).instance_id),' '), 8, ' ') ||
86 rpad(nvl(to_char(l_tbl(l_ind).instance_quantity), ' '), 8, ' ') ||
87 rpad(nvl(l_tbl(l_ind).verified_flag, ' '), 2, ' ') ||
88 rpad(nvl(l_tbl(l_ind).processed_flag, ' '), 2, ' ') );
89 END LOOP;
90 END IF;
91 EXCEPTION
92 WHEN others THEN
93 null;
94 END dump_txn_status_tbl;
95
96 /* local debug dump routines */
97 PROCEDURE dump_mtl_txn_tbl(
98 p_mtl_txn_tbl IN mtl_txn_tbl)
99 IS
100 BEGIN
101 IF p_mtl_txn_tbl.COUNT > 0 THEN
102 FOR l_ind IN p_mtl_txn_tbl.FIRST .. p_mtl_txn_tbl.LAST
103 LOOP
104 dump_mtl_txn_rec(p_mtl_txn_tbl(l_ind));
105 END LOOP;
106 END IF;
107 END dump_mtl_txn_tbl;
108
109 PROCEDURE get_instance_pa_dtls(
110 p_transaction_type_id IN number,
111 p_sub_type_id IN number,
112 px_inst_pa_rec IN OUT NOCOPY inst_pa_rec,
113 x_sub_type_rec OUT NOCOPY csi_txn_sub_types%rowtype,
114 x_return_status OUT NOCOPY varchar2)
115 IS
116 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
117 BEGIN
118 api_log('get_instance_pa_dtls');
119 x_return_status := fnd_api.g_ret_sts_success;
120 BEGIN
121 SELECT party_id ,
122 instance_party_id,
123 object_version_number
124 INTO px_inst_pa_rec.party_id,
125 px_inst_pa_rec.instance_party_id,
126 px_inst_pa_rec.pty_obj_version
127 FROM csi_i_parties
128 WHERE instance_id = px_inst_pa_rec.instance_id
129 AND relationship_type_code = 'OWNER'
130 AND sysdate between nvl(active_end_date,sysdate) and sysdate+1 ;
131 px_inst_pa_rec.party_rltnshp_code := 'OWNER';
132 --## brmanesh leased out internal item may not have a owner account
133 --## code enhancement required here
134 -- Added Begin , Exception and End as part of fix for Bug 2733128
135 SELECT party_account_id,
136 ip_account_id,
137 object_version_number
138 INTO px_inst_pa_rec.account_id,
139 px_inst_pa_rec.ip_account_id,
140 px_inst_pa_rec.acct_obj_version
141 FROM csi_ip_accounts
142 WHERE instance_party_id = px_inst_pa_rec.instance_party_id
143 AND relationship_type_code = 'OWNER';
144 px_inst_pa_rec.acct_rltnshp_code := 'OWNER';
145 EXCEPTION
146 WHEN no_data_found THEN
147 --## to seed some error message appropriately
148 l_return_status := fnd_api.g_ret_sts_error;
149 RAISE fnd_api.g_exc_error;
150 END;
151
152 get_sub_type_rec(
153 p_transaction_type_id => p_transaction_type_id,
154 p_sub_type_id => p_sub_type_id,
155 x_sub_type_rec => x_sub_type_rec,
156 x_return_status => l_return_status);
157
158 IF l_return_status <> fnd_api.g_ret_sts_success THEN
159 raise fnd_api.g_exc_error;
160 END IF;
161
162 debug(' Instance ID : '||px_inst_pa_rec.instance_id);
163 debug(' Internal Party ID : '||px_inst_pa_rec.internal_party_id);
164 debug(' Current Party ID : '||px_inst_pa_rec.party_id);
165 debug(' Current Party Account ID : '||px_inst_pa_rec.account_id);
166 debug(' RMA Party ID : '||px_inst_pa_rec.src_txn_party_id );
167 debug(' RMA Party Account ID : '||px_inst_pa_rec.src_txn_acct_id );
168 debug(' Party override Flag : '||px_inst_pa_rec.ownership_ovr_flag);
169
170 IF px_inst_pa_rec.party_id <> px_inst_pa_rec.internal_party_id
171 AND
172 px_inst_pa_rec.party_id <> px_inst_pa_rec.src_txn_party_id
173 AND
174 px_inst_pa_rec.ownership_ovr_flag = 'N' THEN
175
176 fnd_message.set_name('CSI','CSI_RMA_OWNER_MISMATCH'); -- need to seed a new message
177 fnd_message.set_token('INSTANCE_ID', px_inst_pa_rec.instance_id );
178 fnd_message.set_token('OLD_PARTY_ID', px_inst_pa_rec.party_id );
179 fnd_message.set_token('NEW_PARTY_ID', px_inst_pa_rec.src_txn_party_id );
180 fnd_msg_pub.add;
181 l_return_status := fnd_api.g_ret_sts_error;
182 RAISE fnd_api.g_exc_error;
183 END IF;
184
185 EXCEPTION
186 WHEN fnd_api.g_exc_error THEN
187 x_return_status := l_return_status;
188 END get_instance_pa_dtls;
189
190 PROCEDURE get_dflt_sub_type_id(
191 p_transaction_type_id IN number,
192 x_sub_type_id OUT NOCOPY number,
193 x_return_status OUT NOCOPY varchar2)
194 IS
195 BEGIN
196
197 api_log('get_dflt_sub_type_id');
198
199 x_return_status := fnd_api.g_ret_sts_success;
200
201 SELECT sub_type_id
202 INTO x_sub_type_id
203 FROM csi_source_ib_types -- SQL repository changes.
204 WHERE transaction_type_id = p_transaction_type_id
205 AND default_flag = 'Y';
206
207 debug(' Dflt Sub Type ID :'||x_sub_type_id);
208
209 EXCEPTION
210 WHEN no_data_found THEN
211 fnd_message.set_name('CSI', 'CSI_DFLT_SUB_TYPE_MISSING');
212 fnd_message.set_token('TXN_TYPE_ID', p_transaction_type_id);
213 fnd_msg_pub.add;
214 x_return_status := fnd_api.g_ret_sts_error;
215 WHEN too_many_rows THEN
216 fnd_message.set_name('CSI', 'CSI_MANY_DFLT_SUB_TYPES');
217 fnd_message.set_token('TXN_TYPE_ID', p_transaction_type_id);
218 fnd_msg_pub.add;
219 x_return_status := fnd_api.g_ret_sts_error;
220
221 END get_dflt_sub_type_id;
222
223
224 PROCEDURE get_sub_type_rec(
225 p_transaction_type_id IN number,
226 p_sub_type_id IN number,
227 x_sub_type_rec OUT NOCOPY csi_txn_sub_types%rowtype,
228 x_return_status OUT NOCOPY varchar2)
229 IS
230 BEGIN
231
232 api_log('get_sub_type_rec');
233
234 x_return_status := fnd_api.g_ret_sts_success;
235
236 SELECT *
237 INTO x_sub_type_rec
238 FROM csi_txn_sub_types
239 WHERE transaction_type_id = p_transaction_type_id
240 AND sub_type_id = p_sub_type_id;
241
242 debug(' transaction_type_id :'||x_sub_type_rec.transaction_type_id);
243 debug(' sub_type_id :'||x_sub_type_rec.sub_type_id);
244 debug(' name :'||x_sub_type_rec.name);
245 debug(' description :'||x_sub_type_rec.description);
246 debug(' src_reference_reqd :'||x_sub_type_rec.src_reference_reqd);
247 debug(' src_change_owner :'||x_sub_type_rec.src_change_owner);
248 debug(' src_owner_to_code :'||x_sub_type_rec.src_change_owner_to_code);
249 debug(' src_return_reqd :'||x_sub_type_rec.src_return_reqd);
250
251 EXCEPTION
252 WHEN no_data_found THEN
253 fnd_message.set_name('CSI', 'CSI_INT_SUB_TYPE_REC_MISSING');
254 fnd_message.set_token('SUB_TYPE_ID', p_sub_type_id);
255 fnd_message.set_token('TRANSACTION_TYPE_ID', p_transaction_type_id);
256 fnd_msg_pub.add;
257 x_return_status := fnd_api.g_ret_sts_error;
258 END get_sub_type_rec;
259
260 PROCEDURE get_item_control_rec(
261 p_mtl_txn_id IN number,
262 x_item_control_rec OUT NOCOPY item_control_rec,
263 x_return_status OUT NOCOPY varchar2)
264 IS
265
266 l_item_control_rec item_control_rec;
267 l_tmp_id1 number;
268
269 BEGIN
270
271 api_log('get_item_control_rec');
272 x_return_status := fnd_api.g_ret_sts_success;
273 BEGIN
274
275 SELECT inventory_item_id,
276 organization_id
277 INTO l_item_control_rec.inventory_item_id,
278 l_item_control_rec.organization_id
279 FROM mtl_material_transactions
280 WHERE transaction_id = p_mtl_txn_id;
281
282 EXCEPTION
283 WHEN no_data_found THEN
284 fnd_message.set_name('CSI','CSI_NO_INVENTORY_RECORDS');
285 fnd_message.set_token('MTL_TRANSACTION_ID',p_mtl_txn_id);
286 fnd_msg_pub.add;
287 RAISE fnd_api.g_exc_error;
288 END;
289
290 BEGIN
291 SELECT serial_number_control_code,
292 --lot_control_code,
293 nvl(csi_utl_pkg.get_lot_ctrl_code(p_mtl_txn_id),lot_control_code) lot_control_code,-- Added for bug#14835893
294 revision_qty_control_code,
295 bom_item_type,
296 primary_uom_code,
297 base_item_id,
298 pick_components_flag
299 INTO l_item_control_rec.serial_control_code,
300 l_item_control_rec.lot_control_code,
301 l_item_control_rec.revision_control_code,
302 l_item_control_rec.bom_item_type,
303 l_item_control_rec.primary_uom_code,
304 l_item_control_rec.model_item_id,
305 l_item_control_rec.pick_components_flag
306 FROM mtl_system_items
307 WHERE inventory_item_id = l_item_control_rec.inventory_item_id
308 AND organization_id = l_item_control_rec.organization_id;
309 EXCEPTION
310 WHEN no_data_found THEN
311 fnd_message.set_name('CSI', 'CSI_INT_ITEM_ID_MISSING');
312 fnd_message.set_token('INVENTORY_ITEM_ID', l_item_control_rec.inventory_item_id);
313 fnd_message.set_token('INV_ORGANZATION_ID', l_item_control_rec.organization_id);
314 fnd_msg_pub.add;
315 RAISE fnd_api.g_exc_error;
316 END;
317
318 BEGIN
319 SELECT distinct serial_number_control_code
320 INTO l_tmp_id1
321 FROM mtl_system_items_b
322 WHERE inventory_item_id = l_item_control_rec.inventory_item_id;
323 l_item_control_rec.mult_srl_control_flag := 'N';
324 EXCEPTION
325 WHEN too_many_rows THEN
326 l_item_control_rec.mult_srl_control_flag := 'Y';
327 WHEN no_data_found THEN
328 fnd_message.set_name('CSI', 'CSI_INT_ITEM_ID_MISSING');
329 fnd_message.set_token('INVENTORY_ITEM_ID', l_item_control_rec.inventory_item_id);
330 fnd_message.set_token('INV_ORGANZATION_ID', l_item_control_rec.organization_id);
331 fnd_msg_pub.add;
332 RAISE fnd_api.g_exc_error;
333 END;
334
335 dump_item_control_rec(
336 p_item_control_rec => l_item_control_rec);
337
338 x_item_control_rec := l_item_control_rec;
339
340 EXCEPTION
341 WHEN fnd_api.g_exc_error THEN
342 x_return_status := fnd_api.g_ret_sts_error;
343
344 END get_item_control_rec;
345
346
347
348 /* new API Added to get the details for Partner Ordering */
349 PROCEDURE get_partner_order_info(
350 p_mtl_txn_id in number,
351 x_partner_order_rec OUT NOCOPY oe_install_base_util.partner_order_rec,
352 x_end_cust_party_id OUT NOCOPY number,
353 X_return_status OUT NOCOPY varchar2)
354 IS
355
356 l_party_id number;
357 l_account_status hz_cust_accounts.status%type;
358 l_rma_line_id number;
359
360 BEGIN
361
362 x_return_status := fnd_api.g_ret_sts_success;
363
364 api_log('get_partner_order_info');
365
366 SELECT trx_source_line_id
367 INTO l_rma_line_id
368 FROM mtl_material_transactions
369 WHERE transaction_id = p_mtl_txn_id;
370
371 IF l_rma_line_id IS NOT NULL THEN
372
373 SELECT nvl(oel.IB_OWNER,oeh.IB_OWNER),
374 nvl(oel.IB_INSTALLED_AT_LOCATION,oeh.IB_INSTALLED_AT_LOCATION),
375 nvl(oel.IB_CURRENT_LOCATION,oeh.IB_CURRENT_LOCATION),
376 nvl(oel.END_CUSTOMER_ID,oeh.END_CUSTOMER_ID),
377 nvl(oel.END_CUSTOMER_CONTACT_ID,oeh.END_CUSTOMER_CONTACT_ID),
378 nvl(oel.END_CUSTOMER_SITE_USE_ID,oeh.END_CUSTOMER_SITE_USE_ID),
379 oeh.sold_to_site_use_id
380 INTO x_partner_order_rec.IB_OWNER,
381 x_partner_order_rec.IB_INSTALLED_AT_LOCATION,
382 x_partner_order_rec.IB_CURRENT_LOCATION,
383 x_partner_order_rec.END_CUSTOMER_ID,
384 x_partner_order_rec.END_CUSTOMER_CONTACT_ID,
385 x_partner_order_rec.END_CUSTOMER_SITE_USE_ID,
386 x_partner_order_rec.SOLD_TO_SITE_USE_ID
387 FROM oe_order_lines_all oel,
388 oe_order_headers_all oeh
389 WHERE oel.line_id = l_rma_line_id
390 AND oeh.header_id = oel.header_id;
391
392 IF x_partner_order_rec.IB_OWNER = 'END_CUSTOMER' THEN
393
394 IF x_partner_order_rec.END_CUSTOMER_ID is null Then
395 fnd_message.set_name('CSI','CSI_PARTNER_VAL_MISSING');
396 fnd_msg_pub.add;
397 raise fnd_api.g_exc_error;
398 ELSE
399 BEGIN
400 SELECT party_id,
401 status
402 INTO l_party_id,
403 l_account_status
404 FROM hz_cust_accounts
405 WHERE cust_account_id = x_partner_order_rec.END_CUSTOMER_ID;
406 EXCEPTION
407 WHEN no_data_found THEN
408 fnd_message.set_name('CSI','CSI_INT_INV_CUST_ACCT_ID');
409 fnd_message.set_token('CUST_ACCOUNT_ID', x_partner_order_rec.END_CUSTOMER_ID);
410 fnd_msg_pub.add;
411 RAISE fnd_api.g_exc_error;
412 END;
413
414 IF l_party_id = -1 THEN
415 raise fnd_api.g_exc_error;
416 END IF;
417
418 x_end_cust_party_id := l_party_id;
419
420 IF l_account_status <> 'A' THEN
421 debug('This cust account '||x_partner_order_rec.END_CUSTOMER_ID||' has status '||l_account_status);
422 END IF;
423
424
425 END IF; --partner order
426
427 END IF; --rma line id end if
428
429 END IF;
430
431 EXCEPTION
432 WHEN fnd_api.g_exc_error THEN
433 x_return_status := fnd_api.g_ret_sts_error;
434
435 END get_partner_order_info;
436
437
438 PROCEDURE get_src_order_info(
439 p_mtl_txn_id IN number,
440 x_src_order_rec OUT NOCOPY source_order_rec,
441 x_return_status OUT NOCOPY varchar2)
442 IS
443
444 l_ship_to_org number;
445 l_invoice_to_org number;
446 l_cust_acct_site_use_id number;
447
448 BEGIN
449
450 x_return_status := fnd_api.g_ret_sts_success;
451
452 api_log('get_src_order_info');
453
454 x_src_order_rec.transaction_id := p_mtl_txn_id;
455
456 SELECT trx_source_line_id
457 INTO x_src_order_rec.rma_line_id
458 FROM mtl_material_transactions
459 WHERE transaction_id = p_mtl_txn_id;
460
461 SELECT nvl(reference_line_id , fnd_api.g_miss_num)
462 INTO x_src_order_rec.original_order_line_id
463 FROM oe_order_lines_all
464 WHERE line_id = x_src_order_rec.rma_line_id;
465
466 IF x_src_order_rec.rma_line_id IS NOT NULL THEN
467
468 BEGIN
469 SELECT nvl(oel.sold_to_org_id ,oeh.sold_to_org_id) ,
470 nvl(oel.ship_to_org_id,oeh.ship_to_org_id),
471 nvl(oel.invoice_to_org_id,oeh.invoice_to_org_id), -- Modified SQL to add headers and to also read Invoice to since that needs to be atleast required on RMA's - Self bug. shegde
472 ordered_quantity
473 INTO x_src_order_rec.customer_account_id,
474 l_ship_to_org,
475 l_invoice_to_org,
476 x_src_order_rec.original_order_qty
477 FROM oe_order_lines_all oel, oe_order_headers_all oeh
478 WHERE line_id = x_src_order_rec.rma_line_id
479 AND oeh.header_id = oel.header_id;
480
481 debug(' Original Order Line ID: '||x_src_order_rec.original_order_line_id);
482 debug(' Original Return Quantity: '||x_src_order_rec.original_order_qty);
483 IF x_src_order_rec.customer_account_id IS NOT NULL THEN
484 SELECT party_id
485 INTO x_src_order_rec.party_id
486 FROM hz_cust_accounts
487 WHERE cust_account_id = x_src_order_rec.customer_account_id;
488 END IF;
489 l_cust_acct_site_use_id := nvl(l_ship_to_org, l_invoice_to_org); -- Invoice to is to be not null in RMA's - Self bug. shegde
490 IF l_cust_acct_site_use_id IS NOT NULL THEN
491 BEGIN
492 SELECT HCAS.party_site_id
493 INTO x_src_order_rec.customer_location_id
494 FROM hz_cust_site_uses_all HCSU,
495 hz_cust_acct_sites_all HCAS
496 WHERE HCSU.site_use_id = l_cust_acct_site_use_id
497 AND HCAS.cust_acct_site_id = HCSU.cust_acct_site_id;
498 EXCEPTION
499 WHEN no_data_found THEN
500 fnd_message.set_name('CSI','CSI_TXN_SITE_USE_INVALID');
501 fnd_message.set_token('SITE_USE_ID',l_cust_acct_site_use_id);
502 fnd_message.set_token('SITE_USE_CODE','SHIP_TO');
503 fnd_msg_pub.add;
504 raise fnd_api.g_exc_error;
505 END;
506 ELSE -- null for both ship and Invoice to org in RMA's - Self bug. Raise the error much before rather than in the API. shegde
507 fnd_message.set_name('CSI','CSI_TXN_SITE_USE_INVALID');
508 fnd_message.set_token('SITE_USE_ID',l_cust_acct_site_use_id);
509 fnd_message.set_token('SITE_USE_CODE','INVOICE_TO');
510 fnd_msg_pub.add;
511 raise fnd_api.g_exc_error;
512 END IF;
513 EXCEPTION
514 WHEN no_data_found THEN
515 null;
516 END;
517 END IF;
518 EXCEPTION
519 WHEN fnd_api.g_exc_error THEN
520 x_return_status := fnd_api.g_ret_sts_error;
521 END get_src_order_info;
522
523 PROCEDURE get_mtl_txn_recs(
524 p_mtl_txn_id IN number,
525 x_src_mtl_txn_tbl OUT NOCOPY mtl_txn_tbl,
526 x_dest_mtl_txn_tbl OUT NOCOPY mtl_txn_tbl,
527 x_item_control_rec OUT NOCOPY item_control_rec,
528 x_src_order_rec OUT NOCOPY source_order_rec,
529 x_return_status OUT NOCOPY varchar2)
530 IS
531
532 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
533 l_debug_level number;
534
535 l_s_ind binary_integer := 0;
536 l_d_ind binary_integer := 0;
537 l_src_mtl_txn_tbl mtl_txn_tbl;
538 l_dest_mtl_txn_tbl mtl_txn_tbl;
539 l_item_control_rec item_control_rec;
540 l_src_order_rec source_order_rec;
541
542 CURSOR l_txn_cur IS
543 SELECT mmt.transaction_id transaction_id,
544 mmt.inventory_item_id inventory_item_id,
545 mmt.organization_id organization_id,
546 mmt.subinventory_code subinventory_code,
547 mmt.revision revision,
548 mmt.transaction_quantity transaction_quantity,
549 mmt.transaction_uom transaction_uom,
550 mmt.locator_id locator_id,
551 mmt.transaction_date transaction_date,
552 mut.serial_number serial_number,
553 mtln.lot_number lot_number,
554 msi.location_id subinv_location_id,
555 haou.location_id hr_location_id,
556 mmt.primary_quantity mmt_primary_quantity,
557 mtln.primary_quantity lot_primary_quantity,
558 mmt.trx_source_line_id oe_line_id,
559 mmt.transaction_type_id transaction_type_id,
560 mmt.creation_date creation_date -- bug 4026148
561 FROM hr_all_organization_units haou,
562 mtl_transaction_lot_numbers mtln,
563 mtl_unit_transactions mut,
564 mtl_secondary_inventories msi,
565 mtl_material_transactions mmt
566 WHERE mmt.transaction_id = p_mtl_txn_id
567 AND mmt.transaction_id = mut.transaction_id(+)
568 AND mmt.transaction_id = mtln.transaction_id(+)
569 AND mmt.subinventory_code = msi.secondary_inventory_name
570 AND mmt.organization_id = msi.organization_id
571 AND haou.organization_id = mmt.organization_id;
572
573 CURSOR l_lotsrl_cur IS
574 SELECT mmt.transaction_id transaction_id,
575 mmt.inventory_item_id inventory_item_id,
576 mmt.organization_id organization_id,
577 mmt.subinventory_code subinventory_code,
578 mmt.revision revision,
579 mmt.transaction_quantity transaction_quantity,
580 mmt.transaction_uom transaction_uom,
581 mmt.locator_id locator_id,
582 mmt.transaction_date transaction_date,
583 mut.serial_number serial_number,
584 mtln.lot_number lot_number,
585 msi.location_id subinv_location_id,
586 haou.location_id hr_location_id,
587 mmt.primary_quantity mmt_primary_quantity,
588 mtln.primary_quantity lot_primary_quantity,
589 mmt.trx_source_line_id oe_line_id,
590 mmt.transaction_type_id transaction_type_id,
591 mmt.creation_date creation_date -- bug 4026148
592 FROM hr_all_organization_units haou,
593 mtl_transaction_lot_numbers mtln,
594 mtl_unit_transactions mut,
595 mtl_secondary_inventories msi,
596 mtl_material_transactions mmt
597 WHERE mmt.transaction_id = p_mtl_txn_id
598 AND mmt.subinventory_code = msi.secondary_inventory_name
599 AND mmt.organization_id = msi.organization_id
600 AND mtln.transaction_id = mmt.transaction_id
601 AND mut.transaction_id = mtln.serial_transaction_id
602 AND mmt.organization_id = haou.organization_id;
603
604 CURSOR l_6_cur IS
605 SELECT mmt.transaction_id transaction_id,
606 mmt.inventory_item_id inventory_item_id,
607 mmt.organization_id organization_id,
608 mmt.subinventory_code subinventory_code,
609 mmt.revision revision,
610 mmt.transaction_quantity transaction_quantity,
611 mmt.transaction_uom transaction_uom,
612 mmt.locator_id locator_id,
613 mmt.transaction_date transaction_date,
614 null serial_number,
615 mtln.lot_number lot_number,
616 msi.location_id subinv_location_id,
617 haou.location_id hr_location_id,
618 mmt.primary_quantity mmt_primary_quantity,
619 mtln.primary_quantity lot_primary_quantity,
620 mmt.trx_source_line_id oe_line_id,
621 mmt.transaction_type_id transaction_type_id,
622 mmt.creation_date creation_date -- bug 4026148
623 FROM hr_all_organization_units haou,
624 mtl_transaction_lot_numbers mtln,
625 mtl_secondary_inventories msi,
626 mtl_material_transactions mmt
627 WHERE mmt.transaction_id = p_mtl_txn_id
628 AND mmt.transaction_id = mtln.transaction_id(+)
629 AND mmt.subinventory_code = msi.secondary_inventory_name
630 AND mmt.organization_id = msi.organization_id
631 AND haou.organization_id = mmt.organization_id;
632
633 BEGIN
634
635 x_return_status := fnd_api.g_ret_sts_success;
636
637 api_log('get_mtl_txn_recs');
638
639 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
640
641 get_src_order_info(
642 p_mtl_txn_id => p_mtl_txn_id,
643 x_src_order_rec => l_src_order_rec,
644 x_return_status => l_return_status);
645
646 IF l_return_status <> fnd_api.g_ret_sts_success THEN
647 RAISE fnd_api.g_exc_error;
648 END IF;
649
650 x_src_order_rec := l_src_order_rec;
651
652 get_item_control_rec(
653 p_mtl_txn_id => p_mtl_txn_id,
654 x_item_control_rec => l_item_control_rec,
655 x_return_status => l_return_status);
656
657 IF l_return_status <> fnd_api.g_ret_sts_success THEN
658 RAISE fnd_api.g_exc_error;
659 END IF;
660
661 IF (l_item_control_rec.serial_control_code in (2, 5, 6)
662 AND
663 l_item_control_rec.lot_control_code = 1 ) -- serial control only
664 --
665 OR
666 --
667 (l_item_control_rec.serial_control_code = 1
668 AND
669 l_item_control_rec.lot_control_code = 2) -- lot control only
670 --
671 OR
672 (l_item_control_rec.serial_control_code = 1
673 AND
674 l_item_control_rec.lot_control_code = 1) -- no lot, no serial
675 THEN
676
677 FOR l_txn_rec IN l_txn_cur LOOP
678
679 l_s_ind := l_txn_cur%rowcount;
680
681 l_src_mtl_txn_tbl(l_s_ind).transaction_id := l_txn_rec.transaction_id;
682 l_src_mtl_txn_tbl(l_s_ind).inventory_item_id := l_txn_rec.inventory_item_id;
683 l_src_mtl_txn_tbl(l_s_ind).organization_id := l_txn_rec.organization_id;
684 l_src_mtl_txn_tbl(l_s_ind).subinventory_code := l_txn_rec.subinventory_code;
685 l_src_mtl_txn_tbl(l_s_ind).revision := l_txn_rec.revision;
686 l_src_mtl_txn_tbl(l_s_ind).transaction_quantity := l_txn_rec.transaction_quantity;
687 l_src_mtl_txn_tbl(l_s_ind).transaction_uom := l_txn_rec.transaction_uom;
688 l_src_mtl_txn_tbl(l_s_ind).locator_id := l_txn_rec.locator_id;
689 l_src_mtl_txn_tbl(l_s_ind).transaction_date := l_txn_rec.transaction_date;
690 l_src_mtl_txn_tbl(l_s_ind).serial_number := l_txn_rec.serial_number;
691 l_src_mtl_txn_tbl(l_s_ind).lot_number := l_txn_rec.lot_number;
692 l_src_mtl_txn_tbl(l_s_ind).inv_location_id := nvl(l_txn_rec.subinv_location_id,
693 l_txn_rec.hr_location_id);
694
695 l_src_mtl_txn_tbl(l_s_ind).primary_uom_code := l_item_control_rec.primary_uom_code;
696 l_src_mtl_txn_tbl(l_s_ind).mmt_primary_quantity := l_txn_rec.mmt_primary_quantity;
697 l_src_mtl_txn_tbl(l_s_ind).lot_primary_quantity := l_txn_rec.lot_primary_quantity;
698 l_src_mtl_txn_tbl(l_s_ind).oe_line_id := l_txn_rec.oe_line_id;
699 l_src_mtl_txn_tbl(l_s_ind).transaction_type_id := l_txn_rec.transaction_type_id;
700
701 l_src_mtl_txn_tbl(l_s_ind).original_order_line_id:= l_src_order_rec.original_order_line_id;
702 l_src_mtl_txn_tbl(l_s_ind).customer_location_id := l_src_order_rec.customer_location_id;
703 l_src_mtl_txn_tbl(l_s_ind).customer_account_id := l_src_order_rec.customer_account_id;
704 l_src_mtl_txn_tbl(l_s_ind).party_id := l_src_order_rec.party_id;
705 l_src_mtl_txn_tbl(l_s_ind).mtl_txn_creation_date := l_txn_rec.creation_date; -- bug4026148
706
707 -- no lot, no serial
708 IF (l_item_control_rec.serial_control_code = 1
709 AND
710 l_item_control_rec.lot_control_code = 1)
711 THEN
712
713 l_src_mtl_txn_tbl(l_s_ind).instance_quantity := l_txn_rec.mmt_primary_quantity;
714
715 -- lot only case
716 ELSIF (l_item_control_rec.serial_control_code = 1
717 AND
718 l_item_control_rec.lot_control_code = 2)
719 THEN
720 l_src_mtl_txn_tbl(l_s_ind).instance_quantity := l_txn_rec.lot_primary_quantity;
721 -- serial only case
722 ELSIF (l_item_control_rec.serial_control_code in (2, 5, 6)
723 AND
724 l_item_control_rec.lot_control_code = 1 )
725 THEN
726 l_src_mtl_txn_tbl(l_s_ind).instance_quantity := 1;
727 END IF;
728
729 END LOOP;
730
731 ELSIF (l_item_control_rec.serial_control_code in (2, 5, 6)
732 AND
733 l_item_control_rec.lot_control_code = 2)
734 THEN
735
736 FOR l_lotsrl_rec IN l_lotsrl_cur LOOP
737
738 l_s_ind := l_lotsrl_cur%rowcount;
739
740 l_src_mtl_txn_tbl(l_s_ind).transaction_id := l_lotsrl_rec.transaction_id;
741 l_src_mtl_txn_tbl(l_s_ind).inventory_item_id := l_lotsrl_rec.inventory_item_id;
742 l_src_mtl_txn_tbl(l_s_ind).organization_id := l_lotsrl_rec.organization_id;
743 l_src_mtl_txn_tbl(l_s_ind).subinventory_code := l_lotsrl_rec.subinventory_code;
744 l_src_mtl_txn_tbl(l_s_ind).revision := l_lotsrl_rec.revision;
745 l_src_mtl_txn_tbl(l_s_ind).transaction_quantity := l_lotsrl_rec.transaction_quantity;
746 l_src_mtl_txn_tbl(l_s_ind).transaction_uom := l_lotsrl_rec.transaction_uom;
747 l_src_mtl_txn_tbl(l_s_ind).locator_id := l_lotsrl_rec.locator_id;
748 l_src_mtl_txn_tbl(l_s_ind).transaction_date := l_lotsrl_rec.transaction_date;
749 l_src_mtl_txn_tbl(l_s_ind).serial_number := l_lotsrl_rec.serial_number;
750 l_src_mtl_txn_tbl(l_s_ind).lot_number := l_lotsrl_rec.lot_number;
751 l_src_mtl_txn_tbl(l_s_ind).inv_location_id := nvl(l_lotsrl_rec.subinv_location_id,
752 l_lotsrl_rec.hr_location_id);
753 l_src_mtl_txn_tbl(l_s_ind).primary_uom_code := l_item_control_rec.primary_uom_code;
754 l_src_mtl_txn_tbl(l_s_ind).mmt_primary_quantity := l_lotsrl_rec.mmt_primary_quantity;
755 l_src_mtl_txn_tbl(l_s_ind).lot_primary_quantity := l_lotsrl_rec.lot_primary_quantity;
756 l_src_mtl_txn_tbl(l_s_ind).oe_line_id := l_lotsrl_rec.oe_line_id;
757 l_src_mtl_txn_tbl(l_s_ind).transaction_type_id := l_lotsrl_rec.transaction_type_id;
758 l_src_mtl_txn_tbl(l_s_ind).instance_quantity := 1;
759
760 l_src_mtl_txn_tbl(l_s_ind).original_order_line_id:= l_src_order_rec.original_order_line_id;
761 l_src_mtl_txn_tbl(l_s_ind).customer_location_id := l_src_order_rec.customer_location_id;
762 l_src_mtl_txn_tbl(l_s_ind).customer_account_id := l_src_order_rec.customer_account_id;
763 l_src_mtl_txn_tbl(l_s_ind).party_id := l_src_order_rec.party_id;
764 l_src_mtl_txn_tbl(l_s_ind).mtl_txn_creation_date:= l_lotsrl_rec.creation_date;--bug 4026148
765 END LOOP;
766
767 ELSE
768 debug('This transaction has a special item control : '||p_mtl_txn_id);
769 END IF;
770
771 IF l_item_control_rec.serial_control_code = 6 THEN
772
773 FOR l_6_rec IN l_6_cur LOOP
774
775 l_d_ind := l_6_cur%rowcount;
776
777 l_dest_mtl_txn_tbl(l_d_ind).transaction_id := l_6_rec.transaction_id;
778 l_dest_mtl_txn_tbl(l_d_ind).inventory_item_id := l_6_rec.inventory_item_id;
779 l_dest_mtl_txn_tbl(l_d_ind).organization_id := l_6_rec.organization_id;
780 l_dest_mtl_txn_tbl(l_d_ind).subinventory_code := l_6_rec.subinventory_code;
781 l_dest_mtl_txn_tbl(l_d_ind).revision := l_6_rec.revision;
782 l_dest_mtl_txn_tbl(l_d_ind).transaction_quantity := l_6_rec.transaction_quantity;
783 l_dest_mtl_txn_tbl(l_d_ind).transaction_uom := l_6_rec.transaction_uom;
784 l_dest_mtl_txn_tbl(l_d_ind).locator_id := l_6_rec.locator_id;
785 l_dest_mtl_txn_tbl(l_d_ind).transaction_date := l_6_rec.transaction_date;
786 l_dest_mtl_txn_tbl(l_d_ind).serial_number := l_6_rec.serial_number;
787 l_dest_mtl_txn_tbl(l_d_ind).lot_number := l_6_rec.lot_number;
788 l_dest_mtl_txn_tbl(l_d_ind).inv_location_id := nvl(l_6_rec.subinv_location_id,
789 l_6_rec.hr_location_id);
790 l_dest_mtl_txn_tbl(l_d_ind).primary_uom_code := l_item_control_rec.primary_uom_code;
791 l_dest_mtl_txn_tbl(l_d_ind).mmt_primary_quantity := l_6_rec.mmt_primary_quantity;
792 l_dest_mtl_txn_tbl(l_d_ind).lot_primary_quantity := l_6_rec.lot_primary_quantity;
793 l_dest_mtl_txn_tbl(l_d_ind).oe_line_id := l_6_rec.oe_line_id;
794 l_dest_mtl_txn_tbl(l_d_ind).transaction_type_id := l_6_rec.transaction_type_id;
795
796 IF l_item_control_rec.lot_control_code = 2 THEN
797 l_dest_mtl_txn_tbl(l_d_ind).instance_quantity := l_6_rec.lot_primary_quantity;
798 ELSE
799 l_dest_mtl_txn_tbl(l_d_ind).instance_quantity := l_6_rec.mmt_primary_quantity;
800 END IF;
801
802 l_dest_mtl_txn_tbl(l_d_ind).original_order_line_id:= l_src_order_rec.original_order_line_id;
803 l_dest_mtl_txn_tbl(l_d_ind).customer_location_id := l_src_order_rec.customer_location_id;
804 l_dest_mtl_txn_tbl(l_d_ind).customer_account_id := l_src_order_rec.customer_account_id;
805 l_dest_mtl_txn_tbl(l_d_ind).party_id := l_src_order_rec.party_id;
806 l_dest_mtl_txn_tbl(l_d_ind).mtl_txn_creation_date:= l_6_rec.creation_date;--bug 4026148
807
808 END LOOP;
809
810 ELSE
811 l_dest_mtl_txn_tbl := l_src_mtl_txn_tbl;
812 END IF;
813
814 IF l_s_ind = 0 then
815 fnd_message.set_name('CSI','CSI_NO_INVENTORY_RECORDS');
816 fnd_message.set_token('MTL_TRANSACTION_ID',p_mtl_txn_id);
817 fnd_msg_pub.add;
818 RAISE fnd_api.g_exc_error;
819 END IF;
820
821 debug('Mtl Transaction Recs Count(Source) : '||l_src_mtl_txn_tbl.COUNT);
822 debug('Mtl Transaction Recs Count(Destination) : '||l_dest_mtl_txn_tbl.COUNT);
823
824 IF l_debug_level >= 10 THEN
825
826 debug('Dumping source material transaction recs.');
827
828 dump_mtl_txn_tbl(
829 p_mtl_txn_tbl => l_src_mtl_txn_tbl);
830
831 debug('Dumping destination material transaction recs.');
832
833 dump_mtl_txn_tbl(
834 p_mtl_txn_tbl => l_dest_mtl_txn_tbl);
835
836 END IF;
837
838 x_item_control_rec := l_item_control_rec;
839 x_src_mtl_txn_tbl := l_src_mtl_txn_tbl;
840 x_dest_mtl_txn_tbl := l_dest_mtl_txn_tbl;
841
842 EXCEPTION
843
844 WHEN fnd_api.g_exc_error THEN
845 x_return_status := fnd_api.g_ret_sts_error;
846
847 WHEN others THEN
848
849 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
850 fnd_message.set_token('API_NAME','get_mtl_txn_recs');
851 fnd_message.set_token('SQL_ERROR',substr(sqlerrm, 1, 240));
852 fnd_msg_pub.add;
853
854 x_return_status := fnd_api.g_ret_sts_unexp_error;
855
856 END get_mtl_txn_recs;
857
858 PROCEDURE build_instance_query_rec(
859 p_mtl_txn_rec IN mtl_txn_rec,
860 x_instance_query_rec OUT NOCOPY csi_datastructures_pub.instance_query_rec,
861 x_party_query_rec OUT NOCOPY csi_datastructures_pub.party_query_rec,
862 x_pty_acct_query_rec OUT NOCOPY csi_datastructures_pub.party_account_query_rec,
863 x_return_status OUT NOCOPY varchar2)
864 IS
865
866 l_inv_location_id number;
867 l_inst_query_rec csi_datastructures_pub.instance_query_rec;
868 l_party_query_rec csi_datastructures_pub.party_query_rec;
869
870 BEGIN
871
872 x_return_status := fnd_api.g_ret_sts_success;
873
874 api_log('build_instance_query_rec');
875
876 l_inst_query_rec.inventory_item_id := p_mtl_txn_rec.inventory_item_id;
877 l_inst_query_rec.lot_number := p_mtl_txn_rec.lot_number;
878 l_inst_query_rec.serial_number := p_mtl_txn_rec.serial_number;
879
880 IF p_mtl_txn_rec.serial_number IS NULL THEN -- added IF condition for Bug 10077451
881 l_inst_query_rec.instance_id := p_mtl_txn_rec.instance_id; -- added Bug 9922000
882 END IF;
883
884 --comenting this because ui allows creation of cp with other than hz_party_sites
885 --l_inst_query_rec.location_type_code := 'HZ_PARTY_SITES';
886
887 /* start of ER 2646086 + RMA for Repair with different party */
888 /* removing party from the search criteria */
889
890 --l_party_query_rec.party_id := p_mtl_txn_rec.party_id;
891 --l_party_query_rec.relationship_type_code := 'OWNER';
892
893 /* end of ER 2646086 + RMA for Repair with different party */
894
895 x_instance_query_rec := l_inst_query_rec;
896 x_party_query_rec := l_party_query_rec;
897
898 debug('Instance query criteria for the customer product.');
899
900 csi_t_gen_utility_pvt.dump_instance_query_rec(
901 p_instance_query_rec => x_instance_query_rec);
902
903 debug('party query criteria :');
904 debug(' party_id :'||l_party_query_rec.party_id);
905 debug(' relationship_type_code :'||l_party_query_rec.relationship_type_code);
906
907 EXCEPTION
908 WHEN fnd_api.g_exc_error THEN
909 x_return_status := fnd_api.g_ret_sts_error;
910 END build_instance_query_rec;
911
912
913 PROCEDURE identify_source_instance(
914 px_mtl_txn_rec IN OUT NOCOPY mtl_txn_rec,
915 p_item_control_rec IN item_control_rec, -- Added for Multi WIP Job ER
916 x_return_status OUT NOCOPY varchar2)
917 IS
918 l_instance_query_rec csi_datastructures_pub.instance_query_rec;
919 l_party_query_rec csi_datastructures_pub.party_query_rec;
920 l_pty_acct_query_rec csi_datastructures_pub.party_account_query_rec;
921 l_instance_header_tbl csi_datastructures_pub.instance_header_tbl;
922 l_assm_qty number;
923 l_qry_exp_inst varchar2(1) := fnd_api.g_false;
924 l_wip_entity_type number;
925
926 l_msg_count number;
927 l_msg_data varchar2(2000);
928 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
929 BEGIN
930
931 api_log('identify_source_instance');
932
933 x_return_status := fnd_api.g_ret_sts_success;
934
935 build_instance_query_rec(
936 p_mtl_txn_rec => px_mtl_txn_rec,
937 x_instance_query_rec => l_instance_query_rec,
938 x_party_query_rec => l_party_query_rec,
939 x_pty_acct_query_rec => l_pty_acct_query_rec,
940 x_return_status => l_return_status);
941
942 IF l_return_status <> fnd_api.g_ret_sts_success THEN
943 RAISE fnd_api.g_exc_error;
944 END IF;
945
946 -- RMA fulfillment ER, get expired instances also. Instance id is mandatory in TD entered for RMA fl.
947 -- This fix 2733128 (11.5.8) is already taken care of in 11.5.9
948 /*
949 IF nvl(px_mtl_txn_rec.instance_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
950 l_qry_exp_inst := fnd_api.g_true;
951 END IF;
952
953 debug(' Bring expired instances Flag :'||l_qry_exp_inst);
954 */ -- shegde removing this check since referencing an instance should not be a pre-requisite.
955
956 csi_t_gen_utility_pvt.dump_api_info(
957 p_api_name => 'get_item_instances',
958 p_pkg_name => 'csi_item_instance_pub');
959
960 csi_item_instance_pub.get_item_instances(
961 p_api_version => 1.0,
962 p_commit => fnd_api.g_false,
963 p_init_msg_list => fnd_api.g_true,
964 p_validation_level => fnd_api.g_valid_level_full,
965 p_instance_query_rec => l_instance_query_rec,
966 p_party_query_rec => l_party_query_rec,
967 p_account_query_rec => l_pty_acct_query_rec,
968 p_transaction_id => NULL,
969 p_resolve_id_columns => fnd_api.g_false,
970 p_active_instance_only => l_qry_exp_inst,
971 x_instance_header_tbl => l_instance_header_tbl,
972 x_return_status => l_return_status,
973 x_msg_count => l_msg_count,
974 x_msg_data => l_msg_data );
975
976 IF l_return_status <> fnd_api.g_ret_sts_success THEN
977 RAISE fnd_api.g_exc_error;
978 END IF;
979
980 IF l_instance_header_tbl.COUNT > 0 THEN
981 IF l_instance_header_tbl.COUNT = 1 THEN
982 debug('Unique source instance found. Instance ID: '||l_instance_header_tbl(1).instance_id);
983 px_mtl_txn_rec.instance_id := l_instance_header_tbl(1).instance_id;
984
985 -- Included INVENTORY in the Condition and also added the else condition as part of
986 -- fix for Bug 2733128.
987
988 IF l_instance_header_tbl(1).location_type_code
989 NOT IN ( 'INVENTORY','HZ_PARTY_SITES', 'HZ_LOCATIONS', 'VENDOR_SITE', 'INTERNAL_SITE')
990 THEN
991 IF l_instance_header_tbl(1).location_type_code = 'WIP'
992 AND l_instance_header_tbl(1).wip_job_id is NOT NULL THEN
993 -- Multi-WIP Job fallout. These are the srl. Orphan components hanging out in WIP!!
994 debug('Could be Multi-WIP Job fallout.'||l_instance_header_tbl(1).instance_id);
995 /* Commented for now to avoid select on wip entities. Assumption being any instance being RMAed with location as WIP are created only as a result of a multi wip job.
996 Begin
997 SELECT entity_type
998 INTO l_wip_entity_type
999 FROM wip_entities
1000 WHERE wip_entity_id = l_instance_header_tbl(1).wip_job_id
1001 AND organization_id = l_instance_header_tbl(1).vld_organization_id;
1002
1003 IF l_wip_entity_type in (1,3) THEN
1004 SELECT start_quantity
1005 INTO l_assm_qty
1006 FROM wip_discrete_jobs
1007 WHERE wip_entity_id = l_instance_header_tbl(1).wip_job_id
1008 AND organization_id = l_instance_header_tbl(1).vld_organization_id;
1009 END IF;
1010 IF l_assm_qty > 1
1011 AND p_item_control_rec.serial_control_code in (2,5) THEN
1012 debug('A Multi-WIP Job and Serialized instance.'||l_instance_header_tbl(1).instance_id);
1013 ELSE
1014 debug('Location type code is :'||l_instance_header_tbl(1).location_type_code);
1015 fnd_message.set_name('CSI', 'CSI_NON_RETURNABLE_INSTANCE');
1016 fnd_message.set_token('LOC_TYPE_CODE', l_instance_header_tbl(1).location_type_code);
1017 fnd_msg_pub.add;
1018 RAISE fnd_api.g_exc_error;
1019 END IF;
1020
1021 Exception when others then
1022 fnd_message.set_name('FND','FND_GENERIC_MESSAGE');
1023 fnd_message.set_token('MESSAGE',substr(sqlerrm,1,255));
1024 fnd_msg_pub.add;
1025 raise fnd_api.g_exc_error;
1026 End;
1027 */
1028 ELSE
1029 debug('Location type code is :'||l_instance_header_tbl(1).location_type_code);
1030 fnd_message.set_name('CSI', 'CSI_NON_RETURNABLE_INSTANCE');
1031 fnd_message.set_token('LOC_TYPE_CODE', l_instance_header_tbl(1).location_type_code);
1032 fnd_msg_pub.add;
1033 RAISE fnd_api.g_exc_error;
1034 END IF;
1035 -- fix for Bug 2733128.
1036 ELSE
1037 IF ( l_instance_header_tbl(1).location_type_code = 'INVENTORY'
1038 AND
1039 NVL(l_instance_header_tbl(1).active_end_date,fnd_api.g_miss_date) = fnd_api.g_miss_date
1040 )
1041 THEN
1042 debug('Location type code is :'||l_instance_header_tbl(1).location_type_code);
1043 fnd_message.set_name('CSI', 'CSI_NON_RETURNABLE_INSTANCE');
1044 fnd_message.set_token('LOC_TYPE_CODE', l_instance_header_tbl(1).location_type_code);
1045 fnd_message.set_token('INV_ORG_ID',l_instance_query_rec.inv_organization_id);
1046 fnd_msg_pub.add;
1047 raise fnd_api.g_exc_error;
1048 END IF;
1049 END IF;
1050 ELSIF (p_item_control_rec.serial_control_code <> 1
1051 AND p_item_control_rec.lot_control_code <> 2 ) THEN
1052 -- Added condition for bug 6336254, if the item is non-serialized but lot controlled,
1053 -- there are could be multiple instances found in install base
1054 debug('Multiple Source Instances Found.');
1055 fnd_message.set_name('CSI', 'CSI_TXN_MULT_INST_FOUND');
1056 fnd_message.set_token('INV_ITEM_ID',l_instance_query_rec.inventory_item_id);
1057 fnd_message.set_token('INV_ORG_ID',l_instance_query_rec.inv_organization_id);
1058 fnd_msg_pub.add;
1059 raise fnd_api.g_exc_error;
1060 END IF;
1061 ELSE
1062 debug('RMA Processor could not find the source instance.');
1063 px_mtl_txn_rec.instance_id := fnd_api.g_miss_num;
1064 END IF;
1065 EXCEPTION
1066 WHEN fnd_api.g_exc_error THEN
1067 x_return_status := fnd_api.g_ret_sts_error;
1068 END identify_source_instance;
1069
1070
1071 PROCEDURE identify_source_instances(
1072 px_mtl_txn_tbl IN OUT NOCOPY mtl_txn_tbl,
1073 p_item_control_rec IN item_control_rec, -- Added for Multi WIP Job ER
1074 x_return_status OUT NOCOPY varchar2)
1075 IS
1076 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1077 BEGIN
1078
1079 x_return_status := fnd_api.g_ret_sts_success;
1080
1081 api_log('identify_source_instances');
1082
1083 IF px_mtl_txn_tbl.COUNT > 0 THEN
1084 FOR l_ind IN px_mtl_txn_tbl.FIRST .. px_mtl_txn_tbl.LAST
1085 LOOP
1086
1087 identify_source_instance(
1088 px_mtl_txn_rec => px_mtl_txn_tbl(l_ind),
1089 p_item_control_rec => p_item_control_rec,
1090 x_return_status => l_return_status);
1091
1092 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1093 raise fnd_api.g_exc_error;
1094 END IF;
1095
1096 END LOOP;
1097 END IF;
1098
1099 EXCEPTION
1100 WHEN fnd_api.g_exc_error THEN
1101 x_return_status := fnd_api.g_ret_sts_error;
1102 END identify_source_instances;
1103
1104 PROCEDURE get_master_organization_id(
1105 p_organization_id IN number,
1106 x_master_organization_id OUT NOCOPY number,
1107 x_return_status OUT NOCOPY varchar2)
1108 IS
1109 l_master_organization_id number;
1110 BEGIN
1111 api_log('get_master_organization_id');
1112 x_return_status := fnd_api.g_ret_sts_success;
1113 SELECT master_organization_id
1114 INTO l_master_organization_id
1115 FROM mtl_parameters
1116 WHERE organization_id = p_organization_id;
1117 x_master_organization_id := l_master_organization_id;
1118 EXCEPTION
1119 WHEN no_data_found THEN
1120 null;
1121 END get_master_organization_id;
1122
1123 PROCEDURE build_process_tables_TD(
1124 p_line_dtl_tbl IN csi_t_datastructures_grp.txn_line_detail_tbl,
1125 p_mtl_txn_tbl IN mtl_txn_tbl,
1126 p_item_control_rec IN item_control_rec,
1127 x_txn_rec OUT NOCOPY csi_datastructures_pub.transaction_rec,
1128 x_instances_tbl OUT NOCOPY csi_process_txn_grp.txn_instances_tbl,
1129 x_i_parties_tbl OUT NOCOPY csi_process_txn_grp.txn_i_parties_tbl,
1130 x_ip_accounts_tbl OUT NOCOPY csi_process_txn_grp.txn_ip_accounts_tbl,
1131 x_org_units_tbl OUT NOCOPY csi_process_txn_grp.txn_org_units_tbl,
1132 x_ext_attrib_values_tbl OUT NOCOPY csi_process_txn_grp.txn_ext_attrib_values_tbl,
1133 x_pricing_attribs_tbl OUT NOCOPY csi_process_txn_grp.txn_pricing_attribs_tbl,
1134 x_instance_asset_tbl OUT NOCOPY csi_process_txn_grp.txn_instance_asset_tbl,
1135 x_ii_relationships_tbl OUT NOCOPY csi_process_txn_grp.txn_ii_relationships_tbl,
1136 x_dest_location_rec OUT NOCOPY csi_process_txn_grp.dest_location_rec,
1137 x_return_status OUT NOCOPY varchar2)
1138 IS
1139
1140 --Hard Coded Values
1141 l_transaction_type_id number := 53;
1142 l_txn_sub_type_id number := 38;
1143 l_sub_type_rec csi_txn_sub_types%rowtype;
1144
1145 l_internal_party_id number;
1146 l_master_organization_id number;
1147 l_instance_quantity number;
1148
1149 l_rma_order_rec mtl_trx_type;
1150 l_txn_rec csi_datastructures_pub.transaction_rec;
1151 l_instances_tbl csi_process_txn_grp.txn_instances_tbl;
1152 l_i_parties_tbl csi_process_txn_grp.txn_i_parties_tbl;
1153 l_ip_accounts_tbl csi_process_txn_grp.txn_ip_accounts_tbl;
1154 l_org_units_tbl csi_process_txn_grp.txn_org_units_tbl;
1155 l_ext_attrib_values_tbl csi_process_txn_grp.txn_ext_attrib_values_tbl;
1156 l_pricing_attribs_tbl csi_process_txn_grp.txn_pricing_attribs_tbl;
1157 l_instance_asset_tbl csi_process_txn_grp.txn_instance_asset_tbl;
1158 l_ii_relationships_tbl csi_process_txn_grp.txn_ii_relationships_tbl;
1159 l_dest_location_rec csi_process_txn_grp.dest_location_rec;
1160 l_mtl_txn_rec mtl_txn_rec;
1161
1162 -- get_transaction_details variables
1163
1164 l_txn_line_query_rec csi_t_datastructures_grp.txn_line_query_rec;
1165 l_txn_line_detail_query_rec csi_t_datastructures_grp.txn_line_detail_query_rec;
1166
1167 l_line_dtl_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
1168 l_pty_dtl_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
1169 l_pty_acct_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
1170 l_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
1171 l_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
1172 l_txn_eav_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
1173 l_txn_systems_tbl csi_t_datastructures_grp.txn_systems_tbl;
1174 l_csi_ea_tbl csi_t_datastructures_grp.csi_ext_attribs_tbl;
1175 l_csi_eav_tbl csi_t_datastructures_grp.csi_ext_attrib_vals_tbl;
1176
1177
1178 -- misc variables
1179 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1180 l_msg_count number;
1181 l_msg_data varchar2(2000);
1182 l_error_message varchar2(2000);
1183
1184 l_pty_ind binary_integer;
1185 l_pa_ind binary_integer;
1186 l_oa_ind binary_integer;
1187 l_ea_ind binary_integer;
1188
1189 BEGIN
1190
1191 x_return_status := fnd_api.g_ret_sts_success;
1192
1193 api_log('build_process_tables_TD');
1194
1195 get_dflt_sub_type_id(
1196 p_transaction_type_id => l_transaction_type_id,
1197 x_sub_type_id => l_txn_sub_type_id,
1198 x_return_status => l_return_status);
1199
1200 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1201 raise fnd_api.g_exc_error;
1202 END IF;
1203
1204 l_pty_ind := 1;
1205 l_pa_ind := 1;
1206
1207 IF p_mtl_txn_tbl.COUNT > 0 THEN
1208
1209
1210 FOR l_ind IN p_mtl_txn_tbl.FIRST .. p_mtl_txn_tbl.LAST
1211 LOOP
1212
1213 -- get this information only once and use it in the loop
1214 IF l_ind = 1 THEN
1215 get_rma_info(
1216 p_transaction_id => p_mtl_txn_tbl(l_ind).transaction_id,
1217 x_mtl_trx_type => l_rma_order_rec,
1218 x_error_message => l_error_message,
1219 x_return_status => l_return_status);
1220 END IF;
1221
1222 -- get_txn_line_details
1223 IF nvl(p_mtl_txn_tbl(l_ind).txn_line_detail_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
1224 THEN
1225
1226 l_txn_line_detail_query_rec.txn_line_detail_id := p_mtl_txn_tbl(l_ind).txn_line_detail_id;
1227 l_txn_line_detail_query_rec.processing_status := 'IN_PROCESS'; -- added for bug 3094905
1228
1229 csi_t_txn_details_grp.get_transaction_details(
1230 p_api_version => 1.0,
1231 p_commit => fnd_api.g_false,
1232 p_init_msg_list => fnd_api.g_true,
1233 p_validation_level => fnd_api.g_valid_level_full,
1234 p_txn_line_query_rec => l_txn_line_query_rec,
1235 p_txn_line_detail_query_rec => l_txn_line_detail_query_rec,
1236 x_txn_line_detail_tbl => l_line_dtl_tbl,
1237 p_get_parties_flag => fnd_api.g_true,
1238 x_txn_party_detail_tbl => l_pty_dtl_tbl,
1239 p_get_pty_accts_flag => fnd_api.g_true,
1240 x_txn_pty_acct_detail_tbl => l_pty_acct_tbl,
1241 p_get_ii_rltns_flag => fnd_api.g_false,
1242 x_txn_ii_rltns_tbl => l_ii_rltns_tbl,
1243 p_get_org_assgns_flag => fnd_api.g_true,
1244 x_txn_org_assgn_tbl => l_org_assgn_tbl,
1245 p_get_ext_attrib_vals_flag => fnd_api.g_true,
1246 x_txn_ext_attrib_vals_tbl => l_txn_eav_tbl,
1247 p_get_csi_attribs_flag => fnd_api.g_false,
1248 x_csi_ext_attribs_tbl => l_csi_ea_tbl,
1249 p_get_csi_iea_values_flag => fnd_api.g_false,
1250 x_csi_iea_values_tbl => l_csi_eav_tbl,
1251 p_get_txn_systems_flag => fnd_api.g_false,
1252 x_txn_systems_tbl => l_txn_systems_tbl,
1253 x_return_status => l_return_status,
1254 x_msg_count => l_msg_count,
1255 x_msg_data => l_msg_data);
1256
1257 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1258 debug('Error getting the transaction details info for the subject id');
1259 RAISE fnd_api.g_exc_error;
1260 END IF;
1261
1262 END IF;
1263
1264 IF l_ind = 1 THEN
1265
1266 get_master_organization_id(
1267 p_organization_id => p_mtl_txn_tbl(l_ind).organization_id,
1268 x_master_organization_id => l_master_organization_id,
1269 x_return_status => l_return_status);
1270
1271 l_dest_location_rec.location_type_code := 'INVENTORY';
1272 l_dest_location_rec.location_id := p_mtl_txn_tbl(l_ind).inv_location_id;
1273 l_dest_location_rec.inv_organization_id := p_mtl_txn_tbl(l_ind).organization_id;
1274 l_dest_location_rec.inv_subinventory_name := p_mtl_txn_tbl(l_ind).subinventory_code;
1275 l_dest_location_rec.inv_locator_id := p_mtl_txn_tbl(l_ind).locator_id;
1276 --
1277 --
1278 l_txn_rec.inv_material_transaction_id := p_mtl_txn_tbl(l_ind).transaction_id;
1279 l_txn_rec.transaction_quantity := p_mtl_txn_tbl(l_ind).transaction_quantity;
1280 l_txn_rec.transaction_uom_code := p_mtl_txn_tbl(l_ind).transaction_uom;
1281 l_txn_rec.source_transaction_date := p_mtl_txn_tbl(l_ind).transaction_date;
1282 l_txn_rec.transaction_date := sysdate;
1283 l_txn_rec.transaction_type_id := l_transaction_type_id;
1284 l_txn_rec.txn_sub_type_id := p_mtl_txn_tbl(l_ind).sub_type_id;
1285
1286 IF nvl(l_txn_rec.txn_sub_type_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1287 l_txn_rec.txn_sub_type_id := l_txn_sub_type_id;
1288 END IF;
1289
1290 get_sub_type_rec(
1291 p_transaction_type_id => l_txn_rec.transaction_type_id,
1292 p_sub_type_id => l_txn_rec.txn_sub_type_id,
1293 x_sub_type_rec => l_sub_type_rec,
1294 x_return_status => l_return_status);
1295
1296 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1297 raise fnd_api.g_exc_error;
1298 END IF;
1299
1300 l_txn_rec.source_header_ref := l_rma_order_rec.source_header_ref;
1301 l_txn_rec.source_header_ref_id := l_rma_order_rec.source_header_id;
1302 l_txn_rec.source_line_ref := l_rma_order_rec.source_line_ref;
1303 l_txn_rec.source_line_ref_id := p_mtl_txn_tbl(l_ind).oe_line_id;
1304 l_txn_rec.transaction_status_code := 'PENDING';
1305
1306 END IF;
1307
1308 --
1309 --
1310 debug('Building instance rec '||l_ind||' for process transaction.');
1311
1312 l_instances_tbl(l_ind).ib_txn_segment_flag := 'S';
1313 l_instances_tbl(l_ind).actual_return_date := p_mtl_txn_tbl(l_ind).transaction_date;
1314 l_instances_tbl(l_ind).return_by_date := null;
1315 /* this is because we query txn details by txn_line_detail_id */
1316 IF l_line_dtl_tbl.COUNT = 1
1317 AND nvl(p_mtl_txn_tbl(l_ind).txn_line_detail_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
1318 THEN
1319 l_instances_tbl(l_ind).instance_id := l_line_dtl_tbl(1).instance_id;
1320 END IF;
1321
1322 IF nvl(l_instances_tbl(l_ind).instance_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1323 l_instances_tbl(l_ind).instance_id := p_mtl_txn_tbl(l_ind).instance_id;
1324 END IF;
1325
1326 l_instances_tbl(l_ind).mtl_txn_creation_date := nvl(p_mtl_txn_tbl(l_ind).mtl_txn_creation_date,sysdate);--bug4026148
1327 l_instances_tbl(l_ind).quantity := p_mtl_txn_tbl(l_ind).instance_quantity;
1328
1329 IF nvl(l_instances_tbl(l_ind).instance_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
1330
1331 BEGIN
1332
1333 SELECT quantity
1334 INTO l_instance_quantity
1335 FROM csi_item_instances
1336 WHERE instance_id = l_instances_tbl(l_ind).instance_id;
1337 /* We do not do this anymore.Complete RMA cancellations are always Expire cases. Bug 3746600 .
1338 IF l_line_dtl_tbl(1).reference_source_id is NOT NULL
1339 AND l_line_dtl_tbl(1).reference_source_line_id is NOT NULL THEN
1340 -- RMA fulfillment case. A shippable item was fulfilled earlier thru RMA FL
1341 -- and now it is coming back on a std RMA receipt. So it could even be expired
1342 -- earlier but Qty was reduced so this time do NOT reduce it and unexpire it.
1343 IF NOT (WF_ENGINE.ACTIVITY_EXIST_IN_PROCESS(
1344 'OEOL'
1345 ,to_char(l_line_dtl_tbl(1).reference_source_line_id)
1346 ,'OEOL'
1347 ,'RMA_RECEIVING_SUB'
1348 ))
1349 THEN
1350 debug('This Line had No Receiving Node in the RMA fulfillment earlier. Special Processing...');
1351
1352 l_instances_tbl(l_ind).active_end_date := NULL; -- this is done in process txn also
1353 IF p_item_control_rec.serial_control_code = 1 THEN -- pass g_miss so that process txn API does not double update source instance
1354 l_instances_tbl(l_ind).instance_id := fnd_api.g_miss_num;
1355 END IF;
1356 ELSE
1357 debug('sorry!!. This Line had A Receiving Node on the RMA Order earlier. Normal Processing...');
1358 IF l_instance_quantity < l_instances_tbl(l_ind).quantity THEN
1359
1360 fnd_message.set_name('CSI', 'CSI_INT_QTY_CHK_FAILED');
1361 fnd_message.set_token('INSTANCE_ID', l_instances_tbl(l_ind).instance_id);
1362 fnd_msg_pub.add;
1363 RAISE fnd_api.g_exc_error;
1364
1365 END IF;
1366 END IF;
1367 ELSIF l_instance_quantity < l_instances_tbl(l_ind).quantity THEN
1368 Bug 3746600 */
1369 IF l_instance_quantity < l_instances_tbl(l_ind).quantity THEN
1370
1371 fnd_message.set_name('CSI', 'CSI_INT_QTY_CHK_FAILED');
1372 fnd_message.set_token('INSTANCE_ID', l_instances_tbl(l_ind).instance_id);
1373 fnd_msg_pub.add;
1374 RAISE fnd_api.g_exc_error;
1375
1376 END IF;
1377
1378 EXCEPTION
1379 WHEN no_data_found THEN
1380 null;
1381 END;
1382 ELSIF p_item_control_rec.serial_control_code <> 1 THEN
1383 l_mtl_txn_rec := p_mtl_txn_tbl(l_ind);
1384 identify_source_instance(
1385 px_mtl_txn_rec => l_mtl_txn_rec ,
1386 p_item_control_rec => p_item_control_rec,
1387 x_return_status => l_return_status);
1388
1389 IF l_mtl_txn_rec.instance_id <> fnd_api.g_miss_num THEN
1390 l_instances_tbl(l_ind).instance_id := l_mtl_txn_rec.instance_id;
1391 END IF;
1392 END IF;
1393
1394 l_instances_tbl(l_ind).inventory_item_id := p_mtl_txn_tbl(l_ind).inventory_item_id;
1395 l_instances_tbl(l_ind).vld_organization_id := p_mtl_txn_tbl(l_ind).organization_id;
1396
1397 l_instances_tbl(l_ind).inv_master_organization_id := l_master_organization_id;
1398 l_instances_tbl(l_ind).inventory_revision := p_mtl_txn_tbl(l_ind).revision;
1399
1400 l_instances_tbl(l_ind).last_oe_rma_line_id := p_mtl_txn_tbl(l_ind).oe_line_id;
1401 l_instances_tbl(l_ind).object_version_number := 1.0;
1402
1403 IF nvl(p_mtl_txn_tbl(l_ind).serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1404 l_instances_tbl(l_ind).mfg_serial_number_flag := 'N';
1405 ELSE
1406 l_instances_tbl(l_ind).mfg_serial_number_flag := 'Y';
1407 END IF;
1408
1409 l_instances_tbl(l_ind).serial_number := p_mtl_txn_tbl(l_ind).serial_number;
1410 l_instances_tbl(l_ind).lot_number := p_mtl_txn_tbl(l_ind).lot_number;
1411 --l_instances_tbl(l_ind).location_type_code := 'HZ_PARTY_SITES';
1412 --l_instances_tbl(l_ind).instance_usage_code := 'IN_INVENTORY';
1413
1414 l_instances_tbl(l_ind).unit_of_measure := p_mtl_txn_tbl(l_ind).primary_uom_code;
1415 --
1416 --
1417
1418 IF p_item_control_rec.serial_control_code not in (1,6) THEN
1419 -- Added this check to filter out serial code 1 and 6. shegde.
1420 --building td party recs here could mean association of these parties to the Inventory instance(non-serialized cases) which is not correct
1421
1422 IF l_pty_dtl_tbl.COUNT > 0 THEN
1423
1424 FOR l_pd_ind IN l_pty_dtl_tbl.FIRST .. l_pty_dtl_tbl.LAST
1425 LOOP
1426 IF l_pty_dtl_tbl(l_pd_ind).relationship_type_code <> 'OWNER' THEN
1427
1428 debug('Building TD party rec '||l_pty_ind||' for process transaction.');
1429
1430 l_i_parties_tbl(l_pty_ind).parent_tbl_index := l_ind;
1431 l_i_parties_tbl(l_pty_ind).party_source_table :=
1432 l_pty_dtl_tbl(l_pd_ind).party_source_table;
1433 l_i_parties_tbl(l_pty_ind).party_id :=
1434 l_pty_dtl_tbl(l_pd_ind).party_source_id;
1435 l_i_parties_tbl(l_pty_ind).relationship_type_code :=
1436 l_pty_dtl_tbl(l_pd_ind).relationship_type_code;
1437 l_i_parties_tbl(l_pty_ind).contact_flag :=
1438 l_pty_dtl_tbl(l_pd_ind).contact_flag;
1439
1440
1441 IF nvl(l_sub_type_rec.src_change_owner, 'N') = 'N'
1442 -- Added this If piece for the ER 2482219
1443 AND l_pty_acct_tbl.COUNT > 0 THEN
1444
1445 FOR l_pad_ind IN l_pty_acct_tbl.FIRST .. l_pty_acct_tbl.LAST
1446 LOOP
1447
1448 IF l_pty_acct_tbl(l_pad_ind).txn_party_detail_id = l_pty_dtl_tbl(l_pd_ind).txn_party_detail_id THEN
1449 IF l_pty_acct_tbl(l_pad_ind).relationship_type_code <> 'OWNER' THEN
1450
1451 debug('Building TD account rec '||l_pa_ind||' for process transaction.');
1452
1453 l_ip_accounts_tbl(l_pa_ind).parent_tbl_index := l_pty_ind;
1454 l_ip_accounts_tbl(l_pa_ind).party_account_id := l_pty_acct_tbl(l_pad_ind).account_id;
1455 l_ip_accounts_tbl(l_pa_ind).ip_account_id := l_pty_acct_tbl(l_pad_ind).ip_account_id;
1456 l_ip_accounts_tbl(l_pa_ind).relationship_type_code := l_pty_acct_tbl(l_pad_ind).relationship_type_code;
1457 l_ip_accounts_tbl(l_pa_ind).bill_to_address := l_pty_acct_tbl(l_pad_ind).bill_to_address_id;
1458 l_ip_accounts_tbl(l_pa_ind).ship_to_address := l_pty_acct_tbl(l_pad_ind).ship_to_address_id;
1459 l_ip_accounts_tbl(l_pa_ind).active_end_date := l_pty_acct_tbl(l_pad_ind).active_end_date;
1460
1461 l_pa_ind := l_pa_ind + 1;
1462 END IF;
1463 END IF;
1464 END LOOP; -- pty_acct_tbl loop
1465 END IF; -- l_pty_acct_tbl.count > 0
1466
1467 l_pty_ind := l_pty_ind + 1;
1468
1469 END IF; -- pty record <> 'OWNER'
1470 END LOOP; -- pty_dtl_tbl loop
1471 END IF; -- pty_dtl_tbl.count > 0
1472
1473 END IF; -- serial code not in 1,6
1474
1475 --
1476 --
1477
1478 -- check_and_break relation
1479 IF (nvl(l_instances_tbl(l_ind).instance_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
1480 AND
1481 l_instances_tbl(l_ind).mfg_serial_number_flag = 'Y')
1482 OR
1483 -- for non serial configured item break it from the ato model
1484 nvl(p_item_control_rec.model_item_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
1485 THEN
1486
1487 -- Moved the routine to process txn pvt to avoid circular dependancy
1488 -- introduced in that routine for bug 2373109 and also to not load rma receipt for
1489 -- Non RMA txns . shegde. Bug 2443204
1490
1491 csi_process_txn_pvt.check_and_break_relation(
1492 p_instance_id => l_instances_tbl(l_ind).instance_id,
1493 p_csi_txn_rec => l_txn_rec,
1494 x_return_status => l_return_status);
1495
1496 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1497 RAISE fnd_api.g_exc_error;
1498 END IF;
1499
1500 END IF;
1501
1502 END LOOP;
1503
1504 END IF;
1505
1506 x_txn_rec := l_txn_rec;
1507 x_instances_tbl := l_instances_tbl;
1508 x_i_parties_tbl := l_i_parties_tbl;
1509 x_ip_accounts_tbl := l_ip_accounts_tbl;
1510 x_org_units_tbl := l_org_units_tbl;
1511 x_ext_attrib_values_tbl := l_ext_attrib_values_tbl;
1512 x_pricing_attribs_tbl := l_pricing_attribs_tbl;
1513 x_instance_asset_tbl := l_instance_asset_tbl;
1514 x_ii_relationships_tbl := l_ii_relationships_tbl;
1515 x_dest_location_rec := l_dest_location_rec;
1516
1517 END;
1518
1519 PROCEDURE build_process_tables_NOTD(
1520 p_mtl_txn_tbl IN mtl_txn_tbl,
1521 p_item_control_rec IN item_control_rec,
1522 x_txn_rec OUT NOCOPY csi_datastructures_pub.transaction_rec,
1523 x_instances_tbl OUT NOCOPY csi_process_txn_grp.txn_instances_tbl,
1524 x_i_parties_tbl OUT NOCOPY csi_process_txn_grp.txn_i_parties_tbl,
1525 x_ip_accounts_tbl OUT NOCOPY csi_process_txn_grp.txn_ip_accounts_tbl,
1526 x_org_units_tbl OUT NOCOPY csi_process_txn_grp.txn_org_units_tbl,
1527 x_ext_attrib_values_tbl OUT NOCOPY csi_process_txn_grp.txn_ext_attrib_values_tbl,
1528 x_pricing_attribs_tbl OUT NOCOPY csi_process_txn_grp.txn_pricing_attribs_tbl,
1529 x_instance_asset_tbl OUT NOCOPY csi_process_txn_grp.txn_instance_asset_tbl,
1530 x_ii_relationships_tbl OUT NOCOPY csi_process_txn_grp.txn_ii_relationships_tbl,
1531 x_dest_location_rec OUT NOCOPY csi_process_txn_grp.dest_location_rec,
1532 x_return_status OUT NOCOPY varchar2)
1533 IS
1534
1535 --Hard Coded Values
1536 l_transaction_type_id number := 53;
1537 l_txn_sub_type_id number := 38;
1538 l_sub_type_rec csi_txn_sub_types%rowtype;
1539
1540 l_internal_party_id number;
1541 l_master_organization_id number;
1542 l_rma_order_rec mtl_trx_type;
1543
1544 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1545 l_error_message varchar2(2000);
1546
1547 l_txn_rec csi_datastructures_pub.transaction_rec;
1548 l_instances_tbl csi_process_txn_grp.txn_instances_tbl;
1549 l_i_parties_tbl csi_process_txn_grp.txn_i_parties_tbl;
1550 l_ip_accounts_tbl csi_process_txn_grp.txn_ip_accounts_tbl;
1551 l_org_units_tbl csi_process_txn_grp.txn_org_units_tbl;
1552 l_ext_attrib_values_tbl csi_process_txn_grp.txn_ext_attrib_values_tbl;
1553 l_pricing_attribs_tbl csi_process_txn_grp.txn_pricing_attribs_tbl;
1554 l_instance_asset_tbl csi_process_txn_grp.txn_instance_asset_tbl;
1555 l_ii_relationships_tbl csi_process_txn_grp.txn_ii_relationships_tbl;
1556 l_dest_location_rec csi_process_txn_grp.dest_location_rec;
1557
1558
1559 BEGIN
1560
1561 x_return_status := fnd_api.g_ret_sts_success;
1562
1563 api_log('build_process_tables_NOTD');
1564
1565 get_dflt_sub_type_id(
1566 p_transaction_type_id => l_transaction_type_id,
1567 x_sub_type_id => l_txn_sub_type_id,
1568 x_return_status => l_return_status);
1569
1570 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1571 raise fnd_api.g_exc_error;
1572 END IF;
1573
1574 IF p_mtl_txn_tbl.COUNT > 0 THEN
1575 FOR l_ind IN p_mtl_txn_tbl.FIRST .. p_mtl_txn_tbl.LAST
1576 LOOP
1577
1578 IF l_ind = 1 THEN
1579
1580 get_rma_info(
1581 p_transaction_id => p_mtl_txn_tbl(l_ind).transaction_id,
1582 x_mtl_trx_type => l_rma_order_rec,
1583 x_error_message => l_error_message,
1584 x_return_status => l_return_status);
1585
1586 get_master_organization_id(
1587 p_organization_id => p_mtl_txn_tbl(l_ind).organization_id,
1588 x_master_organization_id => l_master_organization_id,
1589 x_return_status => l_return_status);
1590
1591 l_dest_location_rec.location_type_code := 'INVENTORY';
1592 l_dest_location_rec.location_id := p_mtl_txn_tbl(l_ind).inv_location_id;
1593 l_dest_location_rec.inv_organization_id := p_mtl_txn_tbl(l_ind).organization_id;
1594 l_dest_location_rec.inv_subinventory_name := p_mtl_txn_tbl(l_ind).subinventory_code;
1595 l_dest_location_rec.inv_locator_id := p_mtl_txn_tbl(l_ind).locator_id;
1596 --
1597 --
1598 l_txn_rec.inv_material_transaction_id := p_mtl_txn_tbl(l_ind).transaction_id;
1599 l_txn_rec.transaction_quantity := p_mtl_txn_tbl(l_ind).transaction_quantity;
1600 l_txn_rec.transaction_uom_code := p_mtl_txn_tbl(l_ind).transaction_uom;
1601 l_txn_rec.source_transaction_date := p_mtl_txn_tbl(l_ind).transaction_date;
1602 l_txn_rec.transaction_date := sysdate;
1603 l_txn_rec.transaction_type_id := l_transaction_type_id;
1604 l_txn_rec.txn_sub_type_id := l_txn_sub_type_id;
1605
1606 get_sub_type_rec(
1607 p_transaction_type_id => l_txn_rec.transaction_type_id,
1608 p_sub_type_id => l_txn_rec.txn_sub_type_id,
1609 x_sub_type_rec => l_sub_type_rec,
1610 x_return_status => l_return_status);
1611
1612 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1613 raise fnd_api.g_exc_error;
1614 END IF;
1615
1616 l_txn_rec.source_header_ref := l_rma_order_rec.source_header_ref;
1617 l_txn_rec.source_header_ref_id := l_rma_order_rec.source_header_id;
1618 l_txn_rec.source_line_ref := l_rma_order_rec.source_line_ref;
1619 l_txn_rec.source_line_ref_id := p_mtl_txn_tbl(l_ind).oe_line_id;
1620 l_txn_rec.transaction_status_code := 'PENDING';
1621
1622 END IF;
1623 --
1624 l_instances_tbl(l_ind).ib_txn_segment_flag := 'S';
1625 l_instances_tbl(l_ind).actual_return_date := p_mtl_txn_tbl(l_ind).transaction_date;
1626 l_instances_tbl(l_ind).return_by_date := null;
1627 l_instances_tbl(l_ind).instance_id := p_mtl_txn_tbl(l_ind).instance_id;
1628 l_instances_tbl(l_ind).inventory_item_id := p_mtl_txn_tbl(l_ind).inventory_item_id;
1629 l_instances_tbl(l_ind).quantity := p_mtl_txn_tbl(l_ind).instance_quantity;
1630 l_instances_tbl(l_ind).vld_organization_id := p_mtl_txn_tbl(l_ind).organization_id;
1631 l_instances_tbl(l_ind).inventory_revision := p_mtl_txn_tbl(l_ind).revision;
1632 l_instances_tbl(l_ind).inv_master_organization_id := l_master_organization_id;
1633 l_instances_tbl(l_ind).last_oe_rma_line_id := p_mtl_txn_tbl(l_ind).oe_line_id;
1634 l_instances_tbl(l_ind).object_version_number := 1.0;
1635
1636 l_instances_tbl(l_ind).serial_number := p_mtl_txn_tbl(l_ind).serial_number;
1637 l_instances_tbl(l_ind).lot_number := p_mtl_txn_tbl(l_ind).lot_number; -- added Self Bug shegde.
1638 --l_instances_tbl(l_ind).location_type_code := 'HZ_PARTY_SITES';
1639 --l_instances_tbl(l_ind).instance_usage_code := 'IN_INVENTORY';
1640 l_instances_tbl(l_ind).mtl_txn_creation_date := nvl(p_mtl_txn_tbl(l_ind).mtl_txn_creation_date,sysdate);--bug4026148
1641 IF nvl(p_mtl_txn_tbl(l_ind).serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1642 l_instances_tbl(l_ind).mfg_serial_number_flag := 'N';
1643 ELSE
1644 l_instances_tbl(l_ind).mfg_serial_number_flag := 'Y';
1645 END IF;
1646
1647 l_instances_tbl(l_ind).unit_of_measure := p_mtl_txn_tbl(l_ind).primary_uom_code;
1648
1649 -- check_and_break relation
1650 IF (nvl(l_instances_tbl(l_ind).instance_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
1651 AND
1652 l_instances_tbl(l_ind).mfg_serial_number_flag = 'Y' )
1653 OR
1654 -- for non serial configured item break it from the ato model
1655 nvl(p_item_control_rec.model_item_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
1656 THEN
1657
1658 -- Moved the routine to process txn pvt to avoid circular dependancy
1659 -- introduced in that routine for bug 2373109 and also to not load rma
1660 -- receipt for Non RMA txns . shegde. Bug 2443204
1661
1662 csi_process_txn_pvt.check_and_break_relation(
1663 p_instance_id => l_instances_tbl(l_ind).instance_id,
1664 p_csi_txn_rec => l_txn_rec,
1665 x_return_status => l_return_status);
1666
1667 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1668 RAISE fnd_api.g_exc_error;
1669 END IF;
1670
1671 END IF;
1672 END LOOP;
1673
1674 END IF;
1675
1676 x_txn_rec := l_txn_rec;
1677 x_instances_tbl := l_instances_tbl;
1678 x_i_parties_tbl := l_i_parties_tbl;
1679 x_ip_accounts_tbl := l_ip_accounts_tbl;
1680 x_org_units_tbl := l_org_units_tbl;
1681 x_ext_attrib_values_tbl := l_ext_attrib_values_tbl;
1682 x_pricing_attribs_tbl := l_pricing_attribs_tbl;
1683 x_instance_asset_tbl := l_instance_asset_tbl;
1684 x_ii_relationships_tbl := l_ii_relationships_tbl;
1685 x_dest_location_rec := l_dest_location_rec;
1686
1687 END build_process_tables_NOTD;
1688
1689 /* corrected the following routines match_mtl_txn_for_txn_dtl, split_mtl_txn_tbl, sync_txn_dtls_and_mtl_txn
1690 for bug 3094905 . updated routines are as below */
1691
1692 PROCEDURE match_mtl_txn_for_txn_dtl(
1693 px_txn_dtl_rec IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_rec,
1694 px_mtl_txn_tbl IN OUT NOCOPY mtl_txn_tbl,
1695 px_tld_inst_tbl IN OUT NOCOPY tld_inst_tbl,
1696 p_item_control_rec IN item_control_rec,
1697 p_match_qty IN number,
1698 x_match_flag OUT NOCOPY varchar2,
1699 x_match_basis OUT NOCOPY varchar2,
1700 x_return_status OUT NOCOPY varchar2)
1701 IS
1702 l_mtl_txn_rec mtl_txn_rec;
1703 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1704 l_inst_qty number;
1705 i_index number := 0;
1706 l_found varchar2(1) := 'N';
1707 BEGIN
1708
1709 x_return_status := fnd_api.g_ret_sts_success;
1710
1711 api_log('match_mtl_txn_for_txn_dtl');
1712
1713 x_match_flag := 'N';
1714 x_match_basis := null;
1715
1716 /* try matching with lot and serial attributes */
1717 IF px_mtl_txn_tbl.COUNT > 0 THEN
1718 FOR l_ind IN px_mtl_txn_tbl.FIRST .. px_mtl_txn_tbl.LAST
1719 LOOP
1720
1721 IF nvl(px_mtl_txn_tbl(l_ind).lot_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char
1722 AND
1723 nvl(px_mtl_txn_tbl(l_ind).serial_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char
1724 THEN
1725
1726 IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
1727
1728 IF (nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char ) =
1729 nvl(px_mtl_txn_tbl(l_ind).serial_number , fnd_api.g_miss_char ) )
1730 AND
1731 (nvl(px_txn_dtl_rec.lot_number, fnd_api.g_miss_char ) =
1732 nvl(px_mtl_txn_tbl(l_ind).lot_number , fnd_api.g_miss_char ) )
1733 THEN
1734 x_match_flag := 'Y';
1735 x_match_basis := 'ALL_ATTRIBUTES';
1736 px_mtl_txn_tbl(l_ind).verified_flag := 'Y';
1737 px_mtl_txn_tbl(l_ind).instance_id := px_txn_dtl_rec.instance_id;
1738 px_mtl_txn_tbl(l_ind).txn_line_detail_id := px_txn_dtl_rec.txn_line_detail_id;
1739 px_mtl_txn_tbl(l_ind).sub_type_id := px_txn_dtl_rec.sub_type_id;
1740 l_mtl_txn_rec := px_mtl_txn_tbl(l_ind);
1741 exit;
1742 END IF;
1743 END IF;
1744 END IF;
1745 END LOOP;
1746
1747 IF x_match_flag = 'N' THEN
1748
1749 /* try matching with serial number alone */
1750 FOR l_ind IN px_mtl_txn_tbl.FIRST .. px_mtl_txn_tbl.LAST
1751 LOOP
1752 IF nvl(px_mtl_txn_tbl(l_ind).lot_number, fnd_api.g_miss_char) = fnd_api.g_miss_char
1753 AND
1754 nvl(px_mtl_txn_tbl(l_ind).serial_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char
1755 THEN
1756 IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
1757 IF nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char ) =
1758 nvl(px_mtl_txn_tbl(l_ind).serial_number , fnd_api.g_miss_char )
1759 THEN
1760 x_match_flag := 'Y';
1761 x_match_basis := 'SRL_ATTRIBUTE';
1762 px_mtl_txn_tbl(l_ind).verified_flag := 'Y';
1763 px_mtl_txn_tbl(l_ind).txn_line_detail_id := px_txn_dtl_rec.txn_line_detail_id;
1764 px_mtl_txn_tbl(l_ind).sub_type_id := px_txn_dtl_rec.sub_type_id;
1765 l_mtl_txn_rec := px_mtl_txn_tbl(l_ind);
1766 IF nvl(px_txn_dtl_rec.instance_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1767 debug('Entered txn line detail has null Instance_ID. Trying to identify one.');
1768
1769 -- Add if condition for bug 7207346, only call identify_source_instance for serial controled items
1770
1771 IF p_item_control_rec.serial_control_code <> 1 THEN
1772 identify_source_instance(
1773 px_mtl_txn_rec => px_mtl_txn_tbl(l_ind),
1774 p_item_control_rec => p_item_control_rec,
1775 x_return_status => l_return_status);
1776
1777 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1778 raise fnd_api.g_exc_error;
1779 END IF;
1780 end if;
1781 ELSE
1782 px_mtl_txn_tbl(l_ind).instance_id := px_txn_dtl_rec.instance_id;
1783 END IF;
1784 exit;
1785 END IF;
1786 END IF;
1787 END IF;
1788 END LOOP;
1789 END IF;
1790
1791 IF x_match_flag = 'N' THEN
1792
1793 /* try matching with lot number alone */
1794 FOR l_ind IN px_mtl_txn_tbl.FIRST .. px_mtl_txn_tbl.LAST
1795 LOOP
1796 IF nvl(px_mtl_txn_tbl(l_ind).lot_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char AND
1797 ( nvl(px_mtl_txn_tbl(l_ind).serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char OR -- Added for bug 4244887
1798 nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char ) <> fnd_api.g_miss_char) THEN -- Added for bug 4244887
1799 IF nvl(px_txn_dtl_rec.lot_number, fnd_api.g_miss_char ) =
1800 nvl(px_mtl_txn_tbl(l_ind).lot_number , fnd_api.g_miss_char)
1801 AND px_mtl_txn_tbl(l_ind).instance_quantity >= ABS(px_txn_dtl_rec.quantity) -- self bug. Added GT sign
1802 THEN
1803 IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
1804 IF nvl(px_txn_dtl_rec.lot_number, fnd_api.g_miss_char ) =
1805 nvl(px_mtl_txn_tbl(l_ind).lot_number , fnd_api.g_miss_char )
1806 THEN
1807 x_match_flag := 'Y';
1808 x_match_basis := 'LOT_ATTRIBUTE';
1809 IF px_mtl_txn_tbl(l_ind).transaction_quantity >= p_match_qty THEN
1810 px_mtl_txn_tbl(l_ind).verified_flag := 'Y';
1811 END IF;
1812 px_mtl_txn_tbl(l_ind).instance_id := px_txn_dtl_rec.instance_id;
1813 px_mtl_txn_tbl(l_ind).txn_line_detail_id := px_txn_dtl_rec.txn_line_detail_id;
1814 px_mtl_txn_tbl(l_ind).sub_type_id := px_txn_dtl_rec.sub_type_id;
1815 --fix for bug5159276
1816 identify_source_instance(
1817 px_mtl_txn_rec => px_mtl_txn_tbl(l_ind),
1818 p_item_control_rec => p_item_control_rec,
1819 x_return_status => l_return_status);
1820
1821 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1822 raise fnd_api.g_exc_error;
1823 END IF;
1824 l_mtl_txn_rec := px_mtl_txn_tbl(l_ind);
1825 --end of fix for bug5159276
1826 exit;
1827 END IF;
1828 END IF;
1829 END IF;
1830 END IF;
1831 END LOOP;
1832
1833 END IF;
1834
1835 IF x_match_flag = 'N' THEN
1836 /* try matching with quantity */
1837 l_found := 'N';
1838
1839 FOR l_ind IN px_mtl_txn_tbl.FIRST .. px_mtl_txn_tbl.LAST
1840 LOOP
1841 IF nvl(px_txn_dtl_rec.instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
1842 IF nvl(p_item_control_rec.mult_srl_control_flag, fnd_api.g_miss_char) = 'Y' THEN
1843 IF p_item_control_rec.serial_control_code <> 1 THEN
1844 IF nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1845 debug('Serial control codes are different. Non serial item instances referenced');
1846 IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
1847 i_index := NVL(px_tld_inst_tbl.LAST,0) + 1 ;
1848 IF px_tld_inst_tbl.count > 0 THEN
1849 debug('Transaction Details count: '||px_tld_inst_tbl.count);
1850 For i in px_tld_inst_tbl.FIRST ..px_tld_inst_tbl.LAST Loop
1851 IF px_tld_inst_tbl(i).txn_line_detail_id = px_txn_dtl_rec.txn_line_detail_id THEN
1852 l_found := 'Y';
1853 END IF;
1854 End Loop;
1855 END IF;
1856 IF l_found <> 'Y' THEN
1857 px_tld_inst_tbl(i_index).instance_id := px_txn_dtl_rec.instance_id;
1858 px_tld_inst_tbl(i_index).inventory_item_id := px_txn_dtl_rec.inventory_item_id;
1859 px_tld_inst_tbl(i_index).txn_line_detail_id := px_txn_dtl_rec.txn_line_detail_id;
1860 px_tld_inst_tbl(i_index).sub_type_id := px_txn_dtl_rec.sub_type_id;
1861 px_tld_inst_tbl(i_index).quantity := px_txn_dtl_rec.quantity;
1862 px_tld_inst_tbl(i_index).serial_number := px_txn_dtl_rec.serial_number;
1863 px_tld_inst_tbl(i_index).lot_number := px_txn_dtl_rec.lot_number;
1864 px_tld_inst_tbl(i_index).verified_flag := 'Y';
1865 END IF;
1866 END IF;
1867 END IF;
1868 ELSE
1869 -- Non serialized
1870 IF nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char
1871 THEN
1872 debug('Serial control codes are different. Serialized item instances referenced');
1873 debug('Transaction Details count: '||px_tld_inst_tbl.count);
1874 IF px_tld_inst_tbl.count > 0 THEN
1875 i_index := NVL(px_tld_inst_tbl.LAST,0) + 1 ;
1876 For i in px_tld_inst_tbl.FIRST ..px_tld_inst_tbl.LAST Loop
1877 IF px_tld_inst_tbl(i).txn_line_detail_id <> px_txn_dtl_rec.txn_line_detail_id THEN
1878 px_tld_inst_tbl(i_index).instance_id := px_txn_dtl_rec.instance_id;
1879 px_tld_inst_tbl(i_index).inventory_item_id := px_txn_dtl_rec.inventory_item_id;
1880 px_tld_inst_tbl(i_index).txn_line_detail_id := px_txn_dtl_rec.txn_line_detail_id;
1881 px_tld_inst_tbl(i_index).sub_type_id := px_txn_dtl_rec.sub_type_id;
1882 px_tld_inst_tbl(i_index).quantity := px_txn_dtl_rec.quantity;
1883 px_tld_inst_tbl(i_index).serial_number := px_txn_dtl_rec.serial_number;
1884 px_tld_inst_tbl(i_index).lot_number := px_txn_dtl_rec.lot_number;
1885 px_tld_inst_tbl(i_index).verified_flag := 'Y';
1886 x_match_flag := 'Y'; -- Added For BUG 4244887
1887 px_tld_inst_tbl(i_index).mtl_txn_creation_date := px_mtl_txn_tbl(l_ind).mtl_txn_creation_date; --bug4026148
1888 END IF;
1889 End Loop;
1890 ELSE
1891 px_tld_inst_tbl(1).instance_id := px_txn_dtl_rec.instance_id;
1892 px_tld_inst_tbl(1).inventory_item_id := px_txn_dtl_rec.inventory_item_id;
1893 px_tld_inst_tbl(1).txn_line_detail_id := px_txn_dtl_rec.txn_line_detail_id;
1894 px_tld_inst_tbl(1).sub_type_id := px_txn_dtl_rec.sub_type_id;
1895 px_tld_inst_tbl(1).quantity := px_txn_dtl_rec.quantity;
1896 px_tld_inst_tbl(1).serial_number := px_txn_dtl_rec.serial_number;
1897 px_tld_inst_tbl(1).lot_number := px_txn_dtl_rec.lot_number;
1898 px_tld_inst_tbl(1).verified_flag := 'Y';
1899 x_match_flag := 'Y'; -- Added For BUG 4244887
1900 px_tld_inst_tbl(1).mtl_txn_creation_date := px_mtl_txn_tbl(l_ind).mtl_txn_creation_date;--bug4026148
1901 END IF;
1902 END IF;
1903 END IF;
1904 END IF;
1905 IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
1906 IF nvl(abs(px_txn_dtl_rec.quantity), fnd_api.g_miss_num ) =
1907 nvl(px_mtl_txn_tbl(l_ind).instance_quantity , fnd_api.g_miss_num ) AND
1908 nvl(px_mtl_txn_tbl(l_ind).serial_number,fnd_api.g_miss_char)= fnd_api.g_miss_char THEN -- Added For BUG 4244887
1909
1910 px_mtl_txn_tbl(l_ind).instance_id := px_txn_dtl_rec.instance_id;
1911 px_mtl_txn_tbl(l_ind).txn_line_detail_id := px_txn_dtl_rec.txn_line_detail_id;
1912 px_mtl_txn_tbl(l_ind).sub_type_id := px_txn_dtl_rec.sub_type_id;
1913 IF p_item_control_rec.serial_control_code <> 1
1914 OR p_item_control_rec.lot_control_code = 2 THEN
1915 -- get the instance_id for the transaction serial / Lot number
1916 debug('Serial / Lot number specified is not the serial / Lot received. ');
1917 identify_source_instance(
1918 px_mtl_txn_rec => px_mtl_txn_tbl(l_ind),
1919 p_item_control_rec => p_item_control_rec,
1920 x_return_status => l_return_status);
1921 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1922 px_mtl_txn_tbl(l_ind).verified_flag := 'N';
1923 x_match_flag := 'N';
1924 l_mtl_txn_rec := px_mtl_txn_tbl(l_ind);
1925 ELSE
1926 x_match_flag := 'Y';
1927 x_match_basis := 'MTL_ATTRIBUTE';
1928 px_mtl_txn_tbl(l_ind).verified_flag := 'Y';
1929 END IF;
1930 IF px_mtl_txn_tbl(l_ind).instance_id = fnd_api.g_miss_num THEN
1931 px_mtl_txn_tbl(l_ind).instance_id := NULL;
1932 END IF;
1933 ELSIF nvl(px_txn_dtl_rec.serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1934 x_match_flag := 'Y';
1935 x_match_basis := 'QTY_ATTRIBUTE';
1936 IF px_mtl_txn_tbl(l_ind).transaction_quantity >= p_match_qty THEN
1937 px_mtl_txn_tbl(l_ind).verified_flag := 'Y';
1938 END IF;
1939
1940 END IF;
1941 l_mtl_txn_rec := px_mtl_txn_tbl(l_ind);
1942 exit;
1943 END IF;
1944 END IF;
1945 END IF;
1946 END LOOP;
1947 END IF;
1948 END IF;
1949
1950 IF x_match_flag = 'Y' THEN
1951 debug ('Match Basis:'||x_match_basis);
1952
1953 /*-- Added filer conditions for bug 4006563 --*/
1954 IF (p_item_control_rec.lot_control_code = 2 AND px_txn_dtl_rec.lot_number = l_mtl_txn_rec.lot_number) OR
1955 (p_item_control_rec.serial_control_code <> 1 AND px_txn_dtl_rec.serial_number = l_mtl_txn_rec.serial_number) OR
1956 (p_item_control_rec.serial_control_code = 1 AND p_item_control_rec.lot_control_code = 1)
1957 THEN
1958 px_txn_dtl_rec.processing_status := 'IN_PROCESS';
1959 END IF;
1960
1961 /*-- End: Added filer conditions for bug 4006563 --*/
1962
1963 -- px_txn_dtl_rec.quantity := l_mtl_txn_rec.instance_quantity;
1964 IF l_mtl_txn_rec.instance_id <> fnd_api.g_miss_num THEN
1965 IF p_item_control_rec.serial_control_code = 1 THEN -- non serilized / lot
1966 Begin
1967 Select quantity
1968 into l_inst_qty
1969 from csi_item_instances
1970 where instance_id = l_mtl_txn_rec.instance_id
1971 and sysdate < nvl(active_end_date, sysdate+1); -- changed for bug#14364807
1972 --and sysdate between nvl(active_end_date, sysdate-1) and sysdate+1; -- commented for bug # 14364807
1973 Exception when others then
1974 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
1975 fnd_message.set_token('API_NAME','match_mtl_txn_for_txn_dtl');
1976 fnd_message.set_token('SQL_ERROR',substr(sqlerrm, 1, 240));
1977 fnd_msg_pub.add;
1978 raise fnd_api.g_exc_error;
1979 End;
1980
1981 IF p_item_control_rec.lot_control_code = 2 THEN
1982 IF l_inst_qty < l_mtl_txn_rec.instance_quantity THEN -- self bug
1983 px_txn_dtl_rec.quantity := (-1 * l_inst_qty); -- matching for the max allowed inst qty
1984 END IF;
1985 ELSE
1986 px_txn_dtl_rec.quantity := ( -1 * l_mtl_txn_rec.transaction_quantity);
1987 END IF;
1988 ELSE
1989 px_txn_dtl_rec.quantity := -1; -- serialized item
1990 END IF;
1991 END IF;
1992 /*-- Commented as part of bug 4244887
1993 px_txn_dtl_rec.lot_number := l_mtl_txn_rec.lot_number;
1994 px_txn_dtl_rec.serial_number := l_mtl_txn_rec.serial_number;
1995 px_txn_dtl_rec.inv_mtl_transaction_id := l_mtl_txn_rec.transaction_id;
1996 px_txn_dtl_rec.instance_id := l_mtl_txn_rec.instance_id;
1997 */
1998
1999 /*-- Added filter condition for bug 4244887 --*/
2000 IF (p_item_control_rec.lot_control_code = 2 AND px_txn_dtl_rec.lot_number = l_mtl_txn_rec.lot_number) OR
2001 (p_item_control_rec.serial_control_code <> 1 AND px_txn_dtl_rec.serial_number = l_mtl_txn_rec.serial_number) OR
2002 (px_txn_dtl_rec.processing_status = 'IN_PROCESS') OR
2003 (p_item_control_rec.serial_control_code = 1 AND p_item_control_rec.lot_control_code = 1 AND x_match_flag = 'Y')
2004 THEN
2005 px_txn_dtl_rec.inv_mtl_transaction_id := l_mtl_txn_rec.transaction_id;
2006 px_txn_dtl_rec.instance_id := l_mtl_txn_rec.instance_id;
2007 --fix for bug5159276
2008 px_txn_dtl_rec.lot_number := l_mtl_txn_rec.lot_number;
2009 px_txn_dtl_rec.serial_number := l_mtl_txn_rec.serial_number;
2010 --end of fix for bug5159276
2011 END IF;
2012 /*--End: Added filter condition for bug 4244887 --*/
2013
2014 ELSE
2015 debug('Could not match the entered installation details with the material txn info.');
2016 END IF;
2017
2018 EXCEPTION
2019 WHEN fnd_api.g_exc_error THEN
2020 x_return_status := fnd_api.g_ret_sts_error;
2021 END match_mtl_txn_for_txn_dtl;
2022
2023
2024 PROCEDURE split_mtl_txn_tbl(
2025 px_line_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
2026 px_mtl_txn_tbl IN OUT NOCOPY mtl_txn_tbl,
2027 p_item_control_rec IN item_control_rec,
2028 x_return_status OUT NOCOPY varchar2)
2029 IS
2030 l_new_quantity number;
2031 l_new_ind binary_integer := 0;
2032 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
2033 BEGIN
2034
2035 api_log('split_mtl_txn_tbl');
2036
2037 IF px_line_dtl_tbl.COUNT > 0 THEN
2038 FOR l_t_ind IN px_line_dtl_tbl.FIRST .. px_line_dtl_tbl.LAST
2039 LOOP
2040
2041 IF px_mtl_txn_tbl.count > 0 THEN
2042 FOR l_ind IN px_mtl_txn_tbl.FIRST .. px_mtl_txn_tbl.LAST
2043 LOOP
2044 IF px_mtl_txn_tbl(l_ind).verified_flag <> 'Y' THEN
2045 l_new_quantity := px_mtl_txn_tbl(l_ind).instance_quantity -
2046 abs(px_line_dtl_tbl(l_t_ind).quantity);
2047 IF nvl(px_mtl_txn_tbl(l_ind).instance_id, fnd_api.g_miss_num)
2048 = fnd_api.g_miss_num
2049 AND ( p_item_control_rec.serial_control_code <> 1) THEN
2050
2051 identify_source_instance(
2052 px_mtl_txn_rec => px_mtl_txn_tbl(l_ind),
2053 p_item_control_rec => p_item_control_rec,
2054 x_return_status => l_return_status);
2055 END IF;
2056 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2057 raise fnd_api.g_exc_error;
2058 END IF;
2059 -- px_mtl_txn_tbl(l_ind).instance_id := px_line_dtl_tbl(l_t_ind).instance_id;
2060 IF p_item_control_rec.serial_control_code = 1 THEN
2061 IF p_item_control_rec.lot_control_code <> 1 THEN
2062 IF nvl(px_line_dtl_tbl(l_t_ind).lot_number, fnd_api.g_miss_char )
2063 = nvl(px_mtl_txn_tbl(l_ind).lot_number , fnd_api.g_miss_char ) THEN
2064 px_mtl_txn_tbl(l_ind).instance_id := px_line_dtl_tbl(l_t_ind).instance_id;
2065 ELSE
2066 l_return_status := fnd_api.g_ret_sts_error;
2067 debug('Lot number referenced on the transaction line detail is different from the one being received..');
2068 fnd_message.set_name('CSI','CSI_TXN_PARAM_IGNORED_WARN');
2069 fnd_message.set_token('PARAM','Lot Number');
2070 fnd_message.set_token('VALUE',px_line_dtl_tbl(l_t_ind).lot_number);
2071 fnd_message.set_token('REASON','The Lot number and/or instance referenced on the transaction details is different from the one received. Pl. correct it and reprocess the error');
2072 fnd_msg_pub.add;
2073 raise fnd_api.g_exc_error;
2074 END IF;
2075 ELSE
2076 px_mtl_txn_tbl(l_ind).instance_id := px_line_dtl_tbl(l_t_ind).instance_id;
2077 px_line_dtl_tbl(l_t_ind).processing_status := 'IN_PROCESS';
2078 END IF;
2079 END IF;
2080
2081 px_mtl_txn_tbl(l_ind).instance_quantity := abs(px_line_dtl_tbl(l_t_ind).quantity);
2082 px_mtl_txn_tbl(l_ind).txn_line_detail_id := px_line_dtl_tbl(l_t_ind).txn_line_detail_id;
2083 px_mtl_txn_tbl(l_ind).verified_flag := 'Y';
2084 IF l_new_quantity > 0 THEN
2085 l_new_ind := px_mtl_txn_tbl.count + 1;
2086 px_mtl_txn_tbl(l_new_ind) := px_mtl_txn_tbl(l_ind);
2087
2088 px_mtl_txn_tbl(l_new_ind).instance_id := null;
2089 px_mtl_txn_tbl(l_new_ind).instance_quantity := l_new_quantity;
2090 px_mtl_txn_tbl(l_new_ind).verified_flag := 'N';
2091 END IF;
2092 END IF;
2093 END LOOP;
2094 END IF;
2095 END LOOP;
2096 END IF;
2097 Exception
2098 when fnd_api.g_exc_error then
2099 debug('fnd_api.g_exc_error raised in split_mtl_txn_tbl');
2100 x_return_status := l_return_status;
2101 END split_mtl_txn_tbl;
2102
2103 PROCEDURE sync_txn_dtls_and_mtl_txn(
2104 px_mtl_txn_tbl IN OUT NOCOPY mtl_txn_tbl,
2105 px_line_dtl_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_line_detail_tbl,
2106 x_tld_inst_tbl OUT NOCOPY tld_inst_tbl,
2107 p_item_control_rec IN item_control_rec,
2108 x_return_status OUT NOCOPY varchar2)
2109 IS
2110
2111 l_mtl_txn_tbl mtl_txn_tbl;
2112 l_mtl_txn_qty number := 0;
2113 l_txn_dtl_qty number := 0;
2114 l_oe_line_id number;
2115
2116 l_line_dtl_rec csi_t_datastructures_grp.txn_line_detail_rec;
2117 l_match_flag varchar2(1) := 'N';
2118 l_match_basis varchar2(30);
2119 l_matched_quantity number := 0;
2120
2121 l_u_txn_line_rec csi_t_datastructures_grp.txn_line_rec;
2122 l_u_line_dtl_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
2123 l_u_pty_dtl_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
2124 l_u_pty_acct_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
2125 l_u_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
2126 l_u_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
2127 l_u_eav_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
2128
2129 u_td_ind binary_integer;
2130
2131 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
2132 l_msg_count number;
2133 l_msg_data varchar2(2000);
2134 l_new_ind binary_integer := 0;
2135 l_tld_inst_tbl tld_inst_tbl;
2136 l_txn_sub_type_id number; -- Added for bug 4244887
2137
2138 BEGIN
2139
2140 x_return_status := fnd_api.g_ret_sts_success;
2141
2142 api_log('sync_txn_dtls_and_mtl_txn');
2143
2144 l_mtl_txn_tbl := px_mtl_txn_tbl;
2145
2146 l_mtl_txn_qty := 0;
2147 u_td_ind := 0;
2148
2149 IF l_mtl_txn_tbl.COUNT > 0 THEN
2150 l_mtl_txn_qty := l_mtl_txn_tbl(1).mmt_primary_quantity;
2151 l_oe_line_id := l_mtl_txn_tbl(1).oe_line_id;
2152 END IF;
2153 /* this logic takes the txn details and mtl txns in case of lot controlled items and if there are many-many */
2154 /* and a mismatch in the number of records,with the number of inv txn records being more than the txn details */
2155 /* then it just splits and matches them */
2156 IF p_item_control_rec.serial_control_code = 1
2157 AND p_item_control_rec.lot_control_code = 2 THEN -- lot controlled item
2158 IF ( ( px_line_dtl_tbl.COUNT > 1 AND px_mtl_txn_tbl.count > 1)
2159 AND (px_line_dtl_tbl.COUNT <> px_mtl_txn_tbl.count)) THEN
2160 IF px_line_dtl_tbl.COUNT > px_mtl_txn_tbl.count THEN
2161 debug('Multiple Lots being received and Multiple transaction details entered');
2162 -- split the txn details upfront to match the mtl txns first.
2163 FOR l_td_ind IN px_line_dtl_tbl.FIRST .. px_line_dtl_tbl.LAST
2164 LOOP
2165 FOR l_ind IN px_mtl_txn_tbl.FIRST .. px_mtl_txn_tbl.LAST
2166 LOOP
2167 IF nvl(px_mtl_txn_tbl(l_ind).lot_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
2168 IF px_mtl_txn_tbl(l_ind).verified_flag = 'N' THEN
2169 IF nvl(px_line_dtl_tbl(l_td_ind).lot_number, fnd_api.g_miss_char )
2170 = nvl(px_mtl_txn_tbl(l_ind).lot_number , fnd_api.g_miss_char ) THEN
2171 IF px_mtl_txn_tbl(l_ind).instance_quantity > abs(px_line_dtl_tbl(l_td_ind).quantity) THEN
2172 l_new_ind := px_mtl_txn_tbl.count + 1;
2173 px_mtl_txn_tbl(l_new_ind) := px_mtl_txn_tbl(l_ind);
2174 px_mtl_txn_tbl(l_new_ind).instance_quantity :=
2175 (px_mtl_txn_tbl(l_ind).instance_quantity - abs(px_line_dtl_tbl(l_td_ind).quantity));
2176 px_mtl_txn_tbl(l_new_ind).verified_flag := 'N';
2177 px_mtl_txn_tbl(l_ind).verified_flag := 'S';
2178 px_mtl_txn_tbl(l_ind).instance_quantity := abs(px_line_dtl_tbl(l_td_ind).quantity);
2179 END IF;
2180 END IF;
2181 END IF;
2182 END IF;
2183 END LOOP;
2184 END LOOP;
2185 END IF;
2186 END IF;
2187 END IF;
2188
2189 /* this logic is to filter the txn line details for the processing quantity */
2190
2191 IF px_line_dtl_tbl.COUNT > 0 THEN
2192 FOR l_ind IN px_line_dtl_tbl.FIRST .. px_line_dtl_tbl.LAST
2193 LOOP
2194
2195 l_txn_dtl_qty := l_txn_dtl_qty + abs(px_line_dtl_tbl(l_ind).quantity);
2196
2197 IF px_line_dtl_tbl(l_ind).processing_status <>'PROCESSED' THEN --4201911
2198
2199 match_mtl_txn_for_txn_dtl(
2200 px_txn_dtl_rec => px_line_dtl_tbl(l_ind),
2201 px_mtl_txn_tbl => px_mtl_txn_tbl,
2202 px_tld_inst_tbl => x_tld_inst_tbl,
2203 p_item_control_rec => p_item_control_rec,
2204 p_match_qty => l_matched_quantity,
2205 x_match_flag => l_match_flag,
2206 x_match_basis => l_match_basis,
2207 x_return_status => l_return_status);
2208
2209 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2210 debug('Errors while matching txn del with mtl txn.');
2211 RAISE fnd_api.g_exc_error;
2212 END IF;
2213
2214 END IF;
2215
2216 IF l_match_flag = 'Y' THEN
2217
2218 l_txn_sub_type_id := px_line_dtl_tbl(l_ind).sub_type_id; -- Added for bug 4244887
2219 u_td_ind := u_td_ind + 1;
2220 l_u_line_dtl_tbl(u_td_ind).txn_line_detail_id := px_line_dtl_tbl(l_ind).txn_line_detail_id;
2221 l_u_line_dtl_tbl(u_td_ind).processing_status := px_line_dtl_tbl(l_ind).processing_status;
2222 l_u_line_dtl_tbl(u_td_ind).inventory_item_id := px_line_dtl_tbl(l_ind).inventory_item_id;
2223 l_u_line_dtl_tbl(u_td_ind).inv_organization_id := px_line_dtl_tbl(l_ind).inv_organization_id;
2224 l_u_line_dtl_tbl(u_td_ind).quantity := px_line_dtl_tbl(l_ind).quantity;
2225 l_u_line_dtl_tbl(u_td_ind).lot_number := px_line_dtl_tbl(l_ind).lot_number;
2226 l_u_line_dtl_tbl(u_td_ind).serial_number := px_line_dtl_tbl(l_ind).serial_number;
2227 l_u_line_dtl_tbl(u_td_ind).inventory_revision := px_line_dtl_tbl(l_ind).inventory_revision;
2228 l_u_line_dtl_tbl(u_td_ind).instance_id := px_line_dtl_tbl(l_ind).instance_id;
2229 l_matched_quantity := abs(l_u_line_dtl_tbl(u_td_ind).quantity) + l_matched_quantity;
2230
2231 END IF;
2232 /*-- Added for bug 4244887 --*/
2233 IF l_mtl_txn_qty = l_matched_quantity THEN
2234 EXIT;
2235 END IF;
2236 /*--End: Added for bug 4244887 --*/
2237
2238 END LOOP;
2239
2240 debug('Match Flag: '||l_match_flag);
2241
2242 IF px_mtl_txn_tbl.COUNT > 0 THEN
2243 FOR m_ind IN px_mtl_txn_tbl.FIRST .. px_mtl_txn_tbl.LAST
2244 LOOP
2245 /*-- Added for bug 4244887 --*/ -- Assigning sub-type to mtl_txn table if sub-type is null
2246 IF nvl(px_mtl_txn_tbl(m_ind).sub_type_id,fnd_api.g_miss_num) = fnd_api.g_miss_num AND
2247 l_txn_sub_type_id IS NOT NULL THEN
2248 px_mtl_txn_tbl(m_ind).sub_type_id := l_txn_sub_type_id;
2249 END IF;
2250 /*-- End: Added for bug 4244887 --*/
2251
2252 IF px_mtl_txn_tbl(m_ind).verified_flag = 'N' THEN
2253 l_match_flag := 'N';
2254 END IF;
2255 END LOOP;
2256 END IF;
2257
2258 debug('Matched Qty: '||l_matched_quantity||' Mtl txn Qty: '||l_mtl_txn_qty);
2259 /*-- Added for bug 4244887 --*/
2260 IF l_mtl_txn_qty = l_matched_quantity AND
2261 l_match_flag = 'N' THEN
2262 debug('Quantity matched setting match flag to Y');
2263 l_match_flag := 'Y'; -- This is done so that transaction details can be updated
2264 END IF;
2265 /*-- End: Added for bug 4244887 --*/
2266
2267 IF l_matched_quantity = l_mtl_txn_qty AND l_match_flag <> 'N' THEN
2268 -- update the transaction line detail table with the IN_PROCESS status
2269 l_u_txn_line_rec.transaction_line_id := px_line_dtl_tbl(1).transaction_line_id;
2270
2271 csi_t_txn_details_grp.update_txn_line_dtls(
2272 p_api_version => 1.0,
2273 p_commit => fnd_api.g_false,
2274 p_init_msg_list => fnd_api.g_true,
2275 p_validation_level => fnd_api.g_valid_level_full,
2276 p_txn_line_rec => l_u_txn_line_rec,
2277 p_txn_line_detail_tbl => l_u_line_dtl_tbl,
2278 px_txn_ii_rltns_tbl => l_u_ii_rltns_tbl,
2279 px_txn_party_detail_tbl => l_u_pty_dtl_tbl,
2280 px_txn_pty_acct_detail_tbl => l_u_pty_acct_tbl,
2281 px_txn_org_assgn_tbl => l_u_org_assgn_tbl,
2282 px_txn_ext_attrib_vals_tbl => l_u_eav_tbl,
2283 x_return_status => l_return_status,
2284 x_msg_count => l_msg_count,
2285 x_msg_data => l_msg_data);
2286
2287 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2288 debug('Update txn line dtls failed while matching txn del with mtl txn.');
2289 RAISE fnd_api.g_exc_error;
2290 END IF;
2291
2292 ELSE
2293
2294 debug('Transaction Details could not be matched with the material transaction info.');
2295
2296 IF l_txn_dtl_qty = l_mtl_txn_qty THEN
2297 IF px_mtl_txn_tbl.COUNT = 1 AND px_line_dtl_tbl.COUNT > 1 THEN
2298 px_mtl_txn_tbl(1).verified_flag := 'N';
2299 debug ('Splitting material transaction records to make in sync with txn dtls.');
2300
2301 split_mtl_txn_tbl(
2302 px_line_dtl_tbl => px_line_dtl_tbl,
2303 px_mtl_txn_tbl => px_mtl_txn_tbl,
2304 p_item_control_rec => p_item_control_rec,
2305 x_return_status => l_return_status);
2306
2307 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2308 raise fnd_api.g_exc_error;
2309 END IF;
2310 -- END IF;
2311
2312 /*
2313 dump_mtl_txn_tbl(
2314 p_mtl_txn_tbl => px_mtl_txn_tbl);
2315 */
2316
2317 ELSIF px_mtl_txn_tbl.COUNT > 1 AND px_line_dtl_tbl.COUNT = 1 THEN
2318 debug('Using the same txn line detail attribute for all the material txn records');
2319 FOR l_ind IN px_mtl_txn_tbl.FIRST .. px_mtl_txn_tbl.LAST
2320 LOOP
2321
2322 IF p_item_control_rec.serial_control_code <> 1
2323 OR p_item_control_rec.lot_control_code = 2 THEN
2324 identify_source_instance(
2325 px_mtl_txn_rec => px_mtl_txn_tbl(l_ind),
2326 p_item_control_rec => p_item_control_rec,
2327 x_return_status => l_return_status);
2328
2329 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2330 raise fnd_api.g_exc_error;
2331 END IF;
2332
2333 IF nvl(px_mtl_txn_tbl(l_ind).instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
2334 AND px_line_dtl_tbl(1).instance_id <> px_mtl_txn_tbl(l_ind).instance_id
2335 THEN
2336 --Added for bug 9446607--
2337 IF p_item_control_rec.serial_control_code = 6 THEN
2338 px_line_dtl_tbl(1).instance_id := NULL;
2339 ELSE
2340 px_line_dtl_tbl(1).instance_id := px_mtl_txn_tbl(l_ind).instance_id;
2341 END IF;
2342 --Added for bug 9446607--
2343 ELSE
2344 /*Marking the transaction details as IN_PROCESS for the cases where the user
2345 selects only the transaction sub type and source instance reference is not required
2346 for bug 4570399*/
2347 px_line_dtl_tbl(1).processing_status:='IN_PROCESS';
2348
2349 --fix for bug 5898987
2350 /* IF px_mtl_txn_tbl(1).instance_id <> fnd_api.g_miss_num AND p_item_control_rec.serial_control_code <> 6 THEN -- modified for bug 9446607
2351 px_line_dtl_tbl(1).instance_id := px_mtl_txn_tbl(1).instance_id;
2352 ELSE
2353 px_line_dtl_tbl(1).instance_id := NULL;
2354 END IF;*/
2355
2356 --px_line_dtl_tbl(1).serial_number := px_mtl_txn_tbl(1).serial_number;
2357 --px_line_dtl_tbl(1).serial_number := NULL;
2358 --end of fix for bug 5898987
2359
2360 --fix for bug 14846647
2361 IF px_line_dtl_tbl(1).instance_id is not null and px_line_dtl_tbl(1).instance_id <> fnd_api.g_miss_num
2362 and px_mtl_txn_tbl(l_ind).instance_id <> fnd_api.g_miss_num AND p_item_control_rec.serial_control_code <> 6 THEN -- modified for bug 9446607
2363 px_line_dtl_tbl(1).instance_id := px_mtl_txn_tbl(l_ind).instance_id;
2364 px_line_dtl_tbl(1).serial_number := px_mtl_txn_tbl(l_ind).serial_number;
2365 ELSE
2366 px_line_dtl_tbl(1).instance_id := NULL;
2367 END IF;
2368 --end of fix for bug 14846647
2369
2370 END IF;
2371 ELSE
2372 px_mtl_txn_tbl(l_ind).instance_id := px_line_dtl_tbl(1).instance_id;
2373 END IF;
2374 px_mtl_txn_tbl(l_ind).txn_line_detail_id := px_line_dtl_tbl(1).txn_line_detail_id;
2375 px_mtl_txn_tbl(l_ind).sub_type_id := px_line_dtl_tbl(1).sub_type_id;
2376 px_mtl_txn_tbl(l_ind).verified_flag := 'Y';
2377 END LOOP;
2378 --END IF;
2379 ELSE
2380 IF px_mtl_txn_tbl.COUNT = 1 AND px_line_dtl_tbl.COUNT = 1 THEN
2381 debug('same txn line detail and the material txn record');
2382 IF p_item_control_rec.serial_control_code <> 1
2383 OR p_item_control_rec.lot_control_code = 2 THEN
2384
2385 identify_source_instance(
2386 px_mtl_txn_rec => px_mtl_txn_tbl(1),
2387 p_item_control_rec => p_item_control_rec,
2388 x_return_status => l_return_status);
2389
2390 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2391 raise fnd_api.g_exc_error;
2392 END IF;
2393 IF nvl(px_mtl_txn_tbl(1).instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
2394 AND px_line_dtl_tbl(1).instance_id <> px_mtl_txn_tbl(1).instance_id
2395 THEN
2396 px_line_dtl_tbl(1).instance_id := px_mtl_txn_tbl(1).instance_id;
2397
2398 ELSE
2399 /*Marking the transaction details as IN_PROCESS for the cases where the user
2400 selects only the transaction sub type and source instance reference is not required
2401 for bug 4570399*/
2402 px_line_dtl_tbl(1).processing_status:='IN_PROCESS';
2403 --fix for bug 14545975
2404 IF px_mtl_txn_tbl(1).instance_id <> fnd_api.g_miss_num THEN
2405 px_line_dtl_tbl(1).instance_id := px_mtl_txn_tbl(1).instance_id;
2406 ELSE
2407 px_line_dtl_tbl(1).instance_id := NULL;
2408 END IF;
2409 px_line_dtl_tbl(1).serial_number := px_mtl_txn_tbl(1).serial_number;
2410 --end of fix for bug 14545975
2411 END IF;
2412 ELSE
2413 px_mtl_txn_tbl(1).instance_id := px_line_dtl_tbl(1).instance_id;
2414 END IF;
2415 px_mtl_txn_tbl(1).txn_line_detail_id := px_line_dtl_tbl(1).txn_line_detail_id;
2416 px_mtl_txn_tbl(1).sub_type_id := px_line_dtl_tbl(1).sub_type_id;
2417 px_mtl_txn_tbl(1).verified_flag := 'Y';
2418 ELSIF p_item_control_rec.serial_control_code = 1 THEN -- qty mismatch + txn dtl qty also mismatch with the total mmt qty!!!
2419 debug('The combination of Transaction Details entered could not be matched with the multiple material transaction records.');
2420 /* serialized items should be processed no matter what txn details
2421 are entered. hence handled separately at the end */
2422 l_return_status := fnd_api.g_ret_sts_error;
2423 RAISE fnd_api.g_exc_error;
2424 END IF;
2425 END IF;
2426 ELSE -- qty mismatch with txn dtl qty - Error!!
2427 debug('Transaction Details quantity does not be match with the material transaction');
2428 IF p_item_control_rec.lot_control_code = 2 THEN
2429 IF px_mtl_txn_tbl.COUNT = px_line_dtl_tbl.COUNT THEN --lot's match but qty does not - partial RMA?
2430 FOR t IN px_line_dtl_tbl.FIRST .. px_line_dtl_tbl.LAST
2431 LOOP
2432 l_match_flag := 'N' ;
2433 FOR m IN px_mtl_txn_tbl.FIRST .. px_mtl_txn_tbl.LAST
2434 LOOP
2435 IF ( px_mtl_txn_tbl(m).verified_flag <> 'S' and
2436 px_line_dtl_tbl(t).instance_id is not null) THEN
2437 IF nvl(px_line_dtl_tbl(t).lot_number, fnd_api.g_miss_char )
2438 = nvl(px_mtl_txn_tbl(m).lot_number , fnd_api.g_miss_char ) THEN
2439 px_line_dtl_tbl(t).quantity := -1*(px_mtl_txn_tbl(m).lot_primary_quantity);
2440 px_mtl_txn_tbl(m).verified_flag := 'S';
2441 px_mtl_txn_tbl(m).instance_quantity := abs(px_line_dtl_tbl(t).quantity);
2442 px_mtl_txn_tbl(m).instance_id := px_line_dtl_tbl(t).instance_id;
2443 px_line_dtl_tbl(t).processing_status := 'IN_PROCESS';
2444 px_mtl_txn_tbl(m).txn_line_detail_id := px_line_dtl_tbl(t).txn_line_detail_id;
2445 l_match_flag := 'Y';
2446 debug('match basis: '||'MTL_ATTRIBUTE');
2447 exit;
2448 END IF;
2449 END IF;
2450 END LOOP;
2451 END LOOP; -- match_flag = 'Y'
2452 END IF;
2453 IF l_match_flag <> 'Y' THEN
2454 l_return_status := fnd_api.g_ret_sts_error;
2455 RAISE fnd_api.g_exc_error;
2456 END IF;
2457 ELSIF p_item_control_rec.serial_control_code = 1 THEN
2458 /* serialized items should be processed no matter what txn details
2459 are entered. hence handled separately - build_process..*/
2460 /*ported for bug 3686818-Check For Partial receipt on Non-Serialized Item */
2461 IF nvl(p_item_control_rec.mult_srl_control_flag,fnd_api.g_miss_char) = 'Y' THEN
2462 IF x_tld_inst_tbl.count > 0 THEN
2463 null;-- process these in the next routine
2464 END IF;
2465 ELSIF px_mtl_txn_tbl.COUNT = 1 AND px_line_dtl_tbl.COUNT = 1 THEN
2466 IF ( px_line_dtl_tbl(1).instance_id IS NOT NULL
2467 AND nvl(px_line_dtl_tbl(1).serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char) THEN
2468 px_line_dtl_tbl(1).quantity := -1*(px_mtl_txn_tbl(1).instance_quantity);
2469 px_mtl_txn_tbl(1).verified_flag := 'S';
2470 px_mtl_txn_tbl(1).instance_quantity := abs(px_line_dtl_tbl(1).quantity);
2471 px_mtl_txn_tbl(1).instance_id := px_line_dtl_tbl(1).instance_id;
2472 px_line_dtl_tbl(1).processing_status := 'IN_PROCESS';
2473 px_mtl_txn_tbl(1).txn_line_detail_id := px_line_dtl_tbl(1).txn_line_detail_id;
2474 l_match_flag := 'Y';
2475 debug('match basis: '||'NS_ATTRIBUTE');
2476 END IF;
2477 ELSE -- modified for bug 3644297. non serial qty matches need to error out...
2478 l_return_status := fnd_api.g_ret_sts_error;
2479 RAISE fnd_api.g_exc_error;
2480 END IF;
2481
2482 --Fix for bug 4125459:To take sub transaction type from txn details in the case of partial receipt
2483 ELSIF p_item_control_rec.serial_control_code <> 1 THEN
2484 FOR m IN px_mtl_txn_tbl.FIRST .. px_mtl_txn_tbl.LAST LOOP
2485 IF px_mtl_txn_tbl(m).verified_flag = 'N' THEN
2486 px_mtl_txn_tbl(m).sub_type_id := px_line_dtl_tbl(1).sub_type_id;
2487 END IF;
2488 END LOOP;
2489 END IF;
2490 END IF;
2491 -- update the transaction line detail table with the queried instance..
2492 l_u_txn_line_rec.transaction_line_id := px_line_dtl_tbl(1).transaction_line_id;
2493
2494 csi_t_txn_details_grp.update_txn_line_dtls(
2495 p_api_version => 1.0,
2496 p_commit => fnd_api.g_false,
2497 p_init_msg_list => fnd_api.g_true,
2498 p_validation_level => fnd_api.g_valid_level_full,
2499 p_txn_line_rec => l_u_txn_line_rec,
2500 p_txn_line_detail_tbl => px_line_dtl_tbl,
2501 px_txn_ii_rltns_tbl => l_u_ii_rltns_tbl,
2502 px_txn_party_detail_tbl => l_u_pty_dtl_tbl,
2503 px_txn_pty_acct_detail_tbl => l_u_pty_acct_tbl,
2504 px_txn_org_assgn_tbl => l_u_org_assgn_tbl,
2505 px_txn_ext_attrib_vals_tbl => l_u_eav_tbl,
2506 x_return_status => l_return_status,
2507 x_msg_count => l_msg_count,
2508 x_msg_data => l_msg_data);
2509
2510 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2511 debug('Update txn line dtls failed while matching txn del with mtl txn.');
2512 RAISE fnd_api.g_exc_error;
2513 END IF;
2514
2515 END IF;
2516 END IF;
2517 Exception
2518 when fnd_api.g_exc_error then
2519 debug('fnd_api.g_exc_error raised in sync_txn_dtls_and_mtl_txn');
2520 x_return_status := l_return_status;
2521 when others then
2522 debug('when others raised in sync_txn_dtls_and_mtl_txn');
2523 x_return_status := fnd_api.g_ret_sts_unexp_error;
2524 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
2525 fnd_message.set_token('API_NAME','sync_txn_dtls_and_mtl_txn');
2526 fnd_message.set_token('SQL_ERROR',substr(sqlerrm, 1, 240));
2527 fnd_msg_pub.add;
2528 END sync_txn_dtls_and_mtl_txn;
2529
2530 PROCEDURE rma_receipt(
2531 p_mtl_txn_id IN number,
2532 p_message_id IN number,
2533 x_return_status OUT NOCOPY varchar2,
2534 px_trx_error_rec IN OUT NOCOPY csi_datastructures_pub.transaction_error_rec)
2535 IS
2536
2537 l_api_name varchar2(30) := 'rma_receipt';
2538 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
2539 l_msg_count number;
2540 l_msg_data varchar2(2000);
2541
2542 l_rma_order_rec mtl_trx_type;
2543
2544 l_src_mtl_txn_tbl mtl_txn_tbl;
2545 l_dest_mtl_txn_tbl mtl_txn_tbl;
2546 l_item_control_rec item_control_rec;
2547
2548 -- added shegde
2549 l_src_order_rec source_order_rec;
2550 l_mtl_txn_rec mtl_txn_rec;
2551 l_sub_type_rec csi_txn_sub_types%rowtype;
2552 l_owner_pty_passed varchar2(1) := 'N';
2553 l_owner_act_passed varchar2(1) := 'N';
2554 i_p_ind binary_integer;
2555 i_pa_ind binary_integer;
2556 l_internal_party_id number;
2557 l_cur_owner_party_id number;
2558 l_cur_owner_acct_id number;
2559
2560 -- added as part of fix for Bug 2733128
2561 l_chg_instance_rec csi_datastructures_pub.instance_rec;
2562 l_chg_pricing_attribs_tbl csi_datastructures_pub.pricing_attribs_tbl;
2563 l_chg_ext_attrib_val_tbl csi_datastructures_pub.extend_attrib_values_tbl;
2564 l_chg_org_units_tbl csi_datastructures_pub.organization_units_tbl;
2565 l_chg_inst_asset_tbl csi_datastructures_pub.instance_asset_tbl;
2566 l_chg_inst_id_lst csi_datastructures_pub.id_tbl;
2567
2568
2569 l_owner_pty_ip_id number;
2570 l_owner_pty_obj_ver_num number;
2571 l_owner_acct_ipa_id number;
2572 l_owner_acct_obj_ver_num number;
2573
2574 l_pty_override_flag varchar2(1) := 'N';
2575
2576 l_crt_instance_rec csi_datastructures_pub.instance_rec;
2577 l_crt_parties_tbl csi_datastructures_pub.party_tbl;
2578 l_crt_pty_accts_tbl csi_datastructures_pub.party_account_tbl;
2579 l_crt_org_units_tbl csi_datastructures_pub.organization_units_tbl;
2580 l_crt_ea_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
2581 l_crt_pricing_tbl csi_datastructures_pub.pricing_attribs_tbl;
2582 l_crt_assets_tbl csi_datastructures_pub.instance_asset_tbl;
2583 l_upd_parties_tbl csi_datastructures_pub.party_tbl;
2584 l_upd_pty_accts_tbl csi_datastructures_pub.party_account_tbl;
2585
2586 l_txn_line_rec csi_t_datastructures_grp.txn_line_rec;
2587 l_td_found boolean := FALSE;
2588 l_partial_receipt boolean := FALSE; -- Added for bug 4244887
2589 l_split_txn_line_rec csi_t_datastructures_grp.txn_line_rec; -- Added for bug 4244887
2590 -- get_transaction_details variables
2591
2592 l_txn_line_query_rec csi_t_datastructures_grp.txn_line_query_rec;
2593 l_txn_line_detail_query_rec csi_t_datastructures_grp.txn_line_detail_query_rec;
2594
2595 l_line_dtl_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
2596 l_pty_dtl_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
2597 l_pty_acct_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
2598 l_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
2599 l_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
2600 l_txn_eav_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
2601 l_txn_systems_tbl csi_t_datastructures_grp.txn_systems_tbl;
2602 l_csi_ea_tbl csi_t_datastructures_grp.csi_ext_attribs_tbl;
2603 l_csi_eav_tbl csi_t_datastructures_grp.csi_ext_attrib_vals_tbl;
2604
2605
2606 -- get_item_instance_details variables
2607
2608 l_instance_rec csi_datastructures_pub.instance_header_rec;
2609 l_party_header_tbl csi_datastructures_pub.party_header_tbl;
2610 l_account_header_tbl csi_datastructures_pub.party_account_header_tbl;
2611 l_org_assignments_tbl csi_datastructures_pub.org_units_header_tbl;
2612 l_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
2613 l_ext_attrib_tbl csi_datastructures_pub.extend_attrib_values_tbl;
2614 l_ext_attrib_def_tbl csi_datastructures_pub.extend_attrib_tbl;
2615 l_asset_assignment_tbl csi_datastructures_pub.instance_asset_header_tbl;
2616 l_time_stamp date;
2617
2618 -- process_transaction variables
2619
2620 l_api_version NUMBER := 1.0;
2621 l_commit VARCHAR2(1) := fnd_api.g_false;
2622 l_init_msg_list VARCHAR2(1) := fnd_api.g_false;
2623 l_validation_level NUMBER := fnd_api.g_valid_level_full;
2624 l_validate_only_flag VARCHAR2(1) := fnd_api.g_false;
2625 l_in_out_flag VARCHAR2(30) := 'IN';
2626
2627 l_txn_rec csi_datastructures_pub.transaction_rec;
2628 l_instances_tbl csi_process_txn_grp.txn_instances_tbl;
2629 l_i_parties_tbl csi_process_txn_grp.txn_i_parties_tbl;
2630 l_ip_accounts_tbl csi_process_txn_grp.txn_ip_accounts_tbl;
2631 l_org_units_tbl csi_process_txn_grp.txn_org_units_tbl;
2632 l_ext_attrib_values_tbl csi_process_txn_grp.txn_ext_attrib_values_tbl;
2633 l_pricing_attribs_tbl csi_process_txn_grp.txn_pricing_attribs_tbl;
2634 l_instance_asset_tbl csi_process_txn_grp.txn_instance_asset_tbl;
2635 l_ii_relationships_tbl csi_process_txn_grp.txn_ii_relationships_tbl;
2636 l_dest_location_rec csi_process_txn_grp.dest_location_rec;
2637
2638 l_error_message varchar2(4000);
2639 l_error_rec csi_datastructures_pub.transaction_error_rec;
2640
2641 l_split_src_inst_rec csi_datastructures_pub.instance_rec;
2642 l_split_src_trx_rec csi_datastructures_pub.transaction_rec;
2643 l_split_new_inst_rec csi_datastructures_pub.instance_rec;
2644 l_quantity1 NUMBER;
2645 l_quantity2 NUMBER;
2646 -- multi srl control variables
2647 l_inst_pa_rec inst_pa_rec;
2648 l_tld_inst_tbl tld_inst_tbl;
2649 l_u_instance_rec csi_datastructures_pub.instance_rec;
2650 l_u_party_tbl csi_datastructures_pub.party_tbl;
2651 l_u_party_acct_tbl csi_datastructures_pub.party_account_tbl;
2652 l_u_pricing_attribs_tbl csi_datastructures_pub.pricing_attribs_tbl;
2653 l_u_ext_attrib_val_tbl csi_datastructures_pub.extend_attrib_values_tbl;
2654 l_u_org_units_tbl csi_datastructures_pub.organization_units_tbl;
2655 l_u_inst_asset_tbl csi_datastructures_pub.instance_asset_tbl;
2656 l_u_inst_id_lst csi_datastructures_pub.id_tbl;
2657 l_u_txn_rec csi_datastructures_pub.transaction_rec;
2658 l_upd_inst_tbl csi_datastructures_pub.instance_tbl;
2659 l_split_nsrc_inst_rec csi_datastructures_pub.instance_rec;
2660 l_split_nsrc_trx_rec csi_datastructures_pub.transaction_rec;
2661 l_new_nsrc_inst_rec csi_datastructures_pub.instance_rec;
2662 l_obj_ver_num NUMBER;
2663 l_end_date DATE;
2664 u_ind number := 0;
2665 l_srl_qty NUMBER;
2666 l_nsrl_qty NUMBER;
2667 l_rem_qty NUMBER;
2668 l_i_ind number := 1;
2669 l_pi_ind number := 1;
2670 l_count NUMBER;
2671 l_active_end_date date;
2672 l_exp_instance_rec csi_datastructures_pub.instance_rec;
2673 l_literal1 VARCHAR2(30) ;
2674 l_literal2 VARCHAR2(30) ;
2675 l_instance_rev_num NUMBER;
2676 l_lock_id NUMBER;
2677 l_lock_status NUMBER;
2678 l_unlock_inst_tbl csi_cz_int.config_tbl;
2679 -- For partner prdering
2680 l_end_cust_party_id NUMBER;
2681 l_partner_order_rec oe_install_base_util.partner_order_rec;
2682
2683 BEGIN
2684
2685 savepoint rma_receipt;
2686
2687 fnd_msg_pub.initialize;
2688
2689 x_return_status := fnd_api.g_ret_sts_success;
2690
2691 csi_t_gen_utility_pvt.build_file_name(
2692 p_file_segment1 => 'csirmarc',
2693 p_file_segment2 => p_mtl_txn_id);
2694
2695 api_log('rma_receipt');
2696
2697 debug(' Transaction Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
2698 debug(' Transaction Type :RMA Receipt');
2699 debug(' Transaction ID :'||p_mtl_txn_id);
2700
2701 csi_utility_grp.check_ib_active;
2702
2703 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
2704 csi_gen_utility_pvt.populate_install_param_rec;
2705 END IF;
2706
2707 l_error_rec := px_trx_error_rec;
2708 l_error_rec.source_id := p_mtl_txn_id;
2709 l_error_rec.inv_material_transaction_id := p_mtl_txn_id;
2710
2711 get_rma_info(
2712 p_transaction_id => p_mtl_txn_id,
2713 x_mtl_trx_type => l_rma_order_rec,
2714 x_error_message => l_error_message,
2715 x_return_status => l_return_status);
2716
2717 l_error_rec.transaction_type_id := 53;
2718 l_error_rec.source_header_ref := l_rma_order_rec.source_header_ref;
2719 l_error_rec.source_header_ref_id := l_rma_order_rec.source_header_id;
2720 l_error_rec.source_line_ref := l_rma_order_rec.source_line_ref;
2721 l_error_rec.source_line_ref_id := l_rma_order_rec.source_line_id;
2722
2723 debug(' RMA Number: '||l_rma_order_rec.source_header_ref);
2724 debug(' RMA Line Number: '||l_rma_order_rec.source_line_ref);
2725 debug(' RMA Line ID: '||l_rma_order_rec.source_line_id);
2726
2727 -- get material transaction info
2728 get_mtl_txn_recs(
2729 p_mtl_txn_id => p_mtl_txn_id,
2730 x_src_mtl_txn_tbl => l_src_mtl_txn_tbl,
2731 x_dest_mtl_txn_tbl => l_dest_mtl_txn_tbl,
2732 x_item_control_rec => l_item_control_rec,
2733 x_src_order_rec => l_src_order_rec,
2734 x_return_status => l_return_status);
2735
2736 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2737 debug('get_mtl_txn_recs Failed.');
2738 RAISE fnd_api.g_exc_error;
2739 END IF;
2740
2741 /* start of ER 2646086 + RMA for Repair with different party */
2742 /* Get the value for the source of truth flag */
2743
2744 l_internal_party_id := csi_datastructures_pub.g_install_param_rec.internal_party_id;
2745 l_pty_override_flag := csi_datastructures_pub.g_install_param_rec.ownership_override_at_txn;
2746
2747 /* end of ER 2646086 + RMA for Repair with different party */
2748
2749 l_error_rec.inventory_item_id := l_item_control_rec.inventory_item_id;
2750 l_error_rec.src_serial_num_ctrl_code := l_item_control_rec.serial_control_code;
2751 l_error_rec.src_lot_ctrl_code := l_item_control_rec.lot_control_code;
2752 l_error_rec.src_rev_qty_ctrl_code := l_item_control_rec.revision_control_code;
2753 l_error_rec.src_location_ctrl_code := l_item_control_rec.locator_control_code;
2754 l_error_rec.comms_nl_trackable_flag := l_item_control_rec.ib_trackable_flag;
2755
2756 dump_txn_status_tbl(
2757 p_mtl_txn_tbl => l_src_mtl_txn_tbl);
2758
2759 l_txn_line_rec.source_transaction_table := 'OE_ORDER_LINES_ALL';
2760 l_txn_line_rec.source_transaction_id := l_src_mtl_txn_tbl(1).oe_line_id;
2761
2762 l_td_found := csi_t_txn_details_pvt.check_txn_details_exist(
2763 p_txn_line_rec => l_txn_line_rec);
2764
2765 IF l_td_found THEN
2766 debug('Transaction details found for the RMA Order.');
2767
2768 l_txn_line_query_rec.source_transaction_table := 'OE_ORDER_LINES_ALL';
2769 l_txn_line_query_rec.source_transaction_id := l_src_mtl_txn_tbl(1).oe_line_id;
2770 l_txn_line_detail_query_rec.source_transaction_flag := 'Y';
2771
2772 csi_t_txn_details_grp.get_transaction_details(
2773 p_api_version => 1.0,
2774 p_commit => fnd_api.g_false,
2775 p_init_msg_list => fnd_api.g_true,
2776 p_validation_level => fnd_api.g_valid_level_full,
2777 p_txn_line_query_rec => l_txn_line_query_rec,
2778 p_txn_line_detail_query_rec => l_txn_line_detail_query_rec,
2779 x_txn_line_detail_tbl => l_line_dtl_tbl,
2780 p_get_parties_flag => fnd_api.g_false,
2781 x_txn_party_detail_tbl => l_pty_dtl_tbl,
2782 p_get_pty_accts_flag => fnd_api.g_false,
2783 x_txn_pty_acct_detail_tbl => l_pty_acct_tbl,
2784 p_get_ii_rltns_flag => fnd_api.g_false,
2785 x_txn_ii_rltns_tbl => l_ii_rltns_tbl,
2786 p_get_org_assgns_flag => fnd_api.g_false,
2787 x_txn_org_assgn_tbl => l_org_assgn_tbl,
2788 p_get_ext_attrib_vals_flag => fnd_api.g_false,
2789 x_txn_ext_attrib_vals_tbl => l_txn_eav_tbl,
2790 p_get_csi_attribs_flag => fnd_api.g_false,
2791 x_csi_ext_attribs_tbl => l_csi_ea_tbl,
2792 p_get_csi_iea_values_flag => fnd_api.g_false,
2793 x_csi_iea_values_tbl => l_csi_eav_tbl,
2794 p_get_txn_systems_flag => fnd_api.g_false,
2795 x_txn_systems_tbl => l_txn_systems_tbl,
2796 x_return_status => l_return_status,
2797 x_msg_count => l_msg_count,
2798 x_msg_data => l_msg_data);
2799
2800 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2801 debug('Error getting transaction details for RMA Receipt to IB Interface.');
2802 raise fnd_api.g_exc_error;
2803 END IF;
2804
2805 /* check if instance reference is specified */
2806
2807 IF l_line_dtl_tbl.COUNT > 0 THEN
2808
2809 FOR l_ind IN l_line_dtl_tbl.FIRST .. l_line_dtl_tbl.LAST
2810 LOOP
2811
2812 /* bug 2291543. added the serial code check here to allow the serial
2813 installation details without instance reference
2814 */
2815 IF l_item_control_rec.serial_control_code = 1 THEN
2816 IF nvl(l_line_dtl_tbl(l_ind).instance_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
2817 debug('No instance reference in Txn Details for non serial item.');
2818 fnd_message.set_name('CSI', 'CSI_INST_REF_NOT_ENTERED');
2819 fnd_msg_pub.add;
2820 RAISE fnd_api.g_exc_error;
2821 END IF;
2822 END IF;
2823
2824 END LOOP;
2825
2826 END IF;
2827
2828 sync_txn_dtls_and_mtl_txn(
2829 px_mtl_txn_tbl => l_src_mtl_txn_tbl,
2830 px_line_dtl_tbl => l_line_dtl_tbl,
2831 x_tld_inst_tbl => l_tld_inst_tbl,
2832 p_item_control_rec => l_item_control_rec,
2833 x_return_status => l_return_status);
2834
2835 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2836 fnd_message.set_name('CSI','CSI_TXN_PARAM_IGNORED_WARN');
2837 fnd_message.set_token('PARAM','Item attributes');
2838 fnd_message.set_token('VALUE','Lot / Serial');
2839 fnd_message.set_token('REASON','The transaction details entered do not match the inventory material transaction for one or more of the attributes. Pl. correct it and reprocess the error');
2840 fnd_msg_pub.add;
2841 RAISE fnd_api.g_exc_error;
2842 END IF;
2843
2844 build_process_tables_TD(
2845 p_mtl_txn_tbl => l_src_mtl_txn_tbl,
2846 p_item_control_rec => l_item_control_rec,
2847 p_line_dtl_tbl => l_line_dtl_tbl,
2848 x_txn_rec => l_txn_rec,
2849 x_instances_tbl => l_instances_tbl,
2850 x_i_parties_tbl => l_i_parties_tbl,
2851 x_ip_accounts_tbl => l_ip_accounts_tbl,
2852 x_org_units_tbl => l_org_units_tbl,
2853 x_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
2854 x_pricing_attribs_tbl => l_pricing_attribs_tbl,
2855 x_instance_asset_tbl => l_instance_asset_tbl,
2856 x_ii_relationships_tbl => l_ii_relationships_tbl,
2857 x_dest_location_rec => l_dest_location_rec,
2858 x_return_status => l_return_status);
2859
2860 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2861 RAISE fnd_api.g_exc_error;
2862 END IF;
2863
2864 IF l_tld_inst_tbl.count > 0 THEN
2865 debug('Instance updates to be processed for Multiple serial control codes.. '||l_tld_inst_tbl.count);
2866 l_inst_pa_rec.src_txn_party_id := l_src_order_rec.party_id;
2867 l_inst_pa_rec.src_txn_acct_id := l_src_order_rec.customer_account_id;
2868 l_inst_pa_rec.internal_party_id := l_internal_party_id;
2869 l_inst_pa_rec.ownership_ovr_flag := l_pty_override_flag;
2870 l_upd_inst_tbl.delete;
2871 l_i_ind := l_tld_inst_tbl.count; -- initialize
2872
2873 IF l_item_control_rec.serial_control_code = 1
2874 AND l_item_control_rec.lot_control_code = 1
2875 THEN
2876 -- call to update the srl item instance
2877 -- update the tld rec for instance_id = g_miss so that inv instance is created/updated
2878 -- for loop with the mtl txn count..
2879 l_nsrl_qty := l_src_mtl_txn_tbl(1).transaction_quantity; -- the total non serial qty received
2880 l_srl_qty := l_tld_inst_tbl.count; -- the total serial qty referenced on txn dtls
2881 IF l_src_order_rec.original_order_qty > l_nsrl_qty THEN
2882 -- partial rcpt
2883 l_i_ind := l_srl_qty - (l_src_order_rec.original_order_qty - l_nsrl_qty);
2884 ELSE
2885 l_i_ind := l_srl_qty ;
2886 END IF;
2887 For i in 1 .. l_i_ind Loop
2888 -- this is done to ensure that the number of srl item instances updated are limited
2889 --to only the total transacted quantity for nsrl items
2890 IF nvl(l_tld_inst_tbl(i).instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
2891 AND nvl(l_tld_inst_tbl(i).serial_number, fnd_api.g_miss_char) <> fnd_api.g_miss_char
2892 THEN
2893 l_inst_pa_rec.instance_id := l_tld_inst_tbl(i).instance_id;
2894 get_instance_pa_dtls(
2895 p_transaction_type_id => l_txn_rec.transaction_type_id,
2896 p_sub_type_id => l_tld_inst_tbl(i).sub_type_id,
2897 px_inst_pa_rec => l_inst_pa_rec,
2898 x_sub_type_rec => l_sub_type_rec,
2899 x_return_status => l_return_status);
2900
2901 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2902 RAISE fnd_api.g_exc_error;
2903 END IF;
2904 Begin
2905 SELECT object_version_number, active_end_date
2906 INTO l_obj_ver_num, l_end_date
2907 FROM csi_item_instances
2908 WHERE instance_id = l_tld_inst_tbl(i).instance_id;
2909 Exception
2910 when others then
2911 FND_MESSAGE.set_name('CSI','CSI_TXN_INVALID_INST_REF');
2912 FND_MESSAGE.set_token('INSTANCE_ID', l_tld_inst_tbl(i).instance_id);
2913 FND_MSG_PUB.add;
2914 End;
2915 l_upd_inst_tbl(i).object_version_number := l_obj_ver_num;
2916 IF nvl(l_end_date , sysdate) between sysdate and sysdate +1 THEN --already expired earlier
2917 l_upd_inst_tbl(i).instance_id := l_tld_inst_tbl(i).instance_id;
2918 l_upd_inst_tbl(i).last_oe_rma_line_id := l_txn_rec.source_line_ref_id;
2919 IF nvl(l_sub_type_rec.src_change_owner,'N') = 'Y'
2920 AND l_sub_type_rec.src_change_owner_to_code = 'I' THEN
2921 --bug 4026148--
2922 -- l_upd_inst_tbl(i).active_end_date := l_txn_rec.source_transaction_date;
2923 l_upd_inst_tbl(i).active_end_date := l_tld_inst_tbl(i).mtl_txn_creation_date;
2924 --bug 4026148--
2925
2926 l_upd_inst_tbl(i).instance_status_id := nvl(l_sub_type_rec.src_status_id,1119); -- returned for credit
2927 -- should we also change owner to Internal ? leaving as is for now for an easy way out
2928 -- 'cause there seems to be too much reliance on the tld instance reference
2929 ELSE
2930 l_upd_inst_tbl(i).instance_status_id := nvl(l_sub_type_rec.src_status_id,1094);-- returned for repair
2931 END IF;
2932 END IF;
2933 END IF;
2934 l_pi_ind := l_instances_tbl.count; -- initialize
2935 IF l_src_order_rec.original_order_qty > l_nsrl_qty THEN
2936 -- partial rcpt
2937 IF l_src_order_rec.original_order_qty > l_srl_qty THEN
2938 -- non srl instances also referenced on txn dtls
2939 l_pi_ind := l_srl_qty - (l_src_order_rec.original_order_qty - l_nsrl_qty);
2940 ELSE
2941 l_pi_ind := l_nsrl_qty; -- update as many srl instances as the rcpt qty
2942 END IF;
2943 END IF;
2944 IF l_pi_ind > 0 THEN
2945 l_count := 0;
2946 For k in l_instances_tbl.first .. l_instances_tbl.last Loop
2947 --Only loop through till for the actual qty received...
2948 IF nvl(l_instances_tbl(k).instance_id,fnd_api.g_miss_num)
2949 = nvl(l_tld_inst_tbl(i).instance_id,fnd_api.g_miss_num) THEN
2950 l_instances_tbl(k).instance_id := fnd_api.g_miss_num;
2951 l_instances_tbl(k).serial_number := fnd_api.g_miss_char;
2952 l_count := l_count + 1;
2953 IF l_count >= l_pi_ind THEN
2954 exit;
2955 END IF;
2956 -- initializing the variables for the correct update of destination
2957 -- item instances in process txn API
2958 END IF;
2959 End Loop;
2960 END IF;
2961 End Loop;
2962 ELSIF l_item_control_rec.serial_control_code <> 1 THEN
2963 u_ind := 0 ;
2964 l_nsrl_qty := 0;
2965 For j in 1 .. l_instances_tbl.count Loop
2966 IF nvl(l_instances_tbl(j).instance_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
2967 l_nsrl_qty := l_nsrl_qty + 1;
2968 END IF;
2969 End Loop;
2970 l_rem_qty := l_nsrl_qty;
2971
2972 For l in 1 .. l_instances_tbl.count Loop
2973 IF nvl(l_instances_tbl(l).instance_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
2974 For m in l_tld_inst_tbl.first .. l_tld_inst_tbl.last Loop
2975 IF nvl(l_tld_inst_tbl(m).processed_flag, 'N') = 'N' THEN
2976 u_ind := u_ind + 1;
2977 IF abs(l_tld_inst_tbl(m).quantity) >= l_rem_qty THEN
2978 l_quantity2 := l_rem_qty;
2979 ELSE
2980 l_quantity2 := abs(l_tld_inst_tbl(m).quantity);
2981 END IF;
2982 Begin
2983 SELECT quantity, active_end_date, object_version_number
2984 INTO l_quantity1, l_end_date, l_obj_ver_num
2985 FROM csi_item_instances
2986 WHERE instance_id = l_tld_inst_tbl(m).instance_id;
2987 Exception
2988 when others then
2989 FND_MESSAGE.set_name('CSI','CSI_TXN_INVALID_INST_REF');
2990 FND_MESSAGE.set_token('INSTANCE_ID', l_tld_inst_tbl(m).instance_id);
2991 FND_MSG_PUB.add;
2992 End;
2993 l_upd_inst_tbl(m).object_version_number := l_obj_ver_num;
2994 IF l_rem_qty > 0 THEN
2995 IF l_quantity1 > l_quantity2 THEN -- need to split the instance
2996 l_split_nsrc_inst_rec.instance_id := l_tld_inst_tbl(m).instance_id;
2997 l_split_nsrc_inst_rec.last_txn_line_detail_id := l_tld_inst_tbl(m).txn_line_detail_id;
2998 l_split_nsrc_inst_rec.last_oe_rma_line_id := l_txn_rec.source_line_ref_id;
2999 --Active_Start_date added for bug5248037--
3000 l_split_src_inst_rec.active_start_date := l_instances_tbl(m).mtl_txn_creation_date;
3001
3002 l_txn_rec.split_reason_code := 'PARTIAL_RETURN';
3003 l_txn_rec.transaction_action_code := 'MULT_ITEM_SRL_CONTROL'; -- temporarily setting a unique identifier
3004
3005 csi_t_gen_utility_pvt.dump_api_info(
3006 p_pkg_name => 'csi_item_instance_pvt',
3007 p_api_name => 'split_item_instance');
3008
3009 csi_t_gen_utility_pvt.dump_csi_instance_rec(
3010 p_csi_instance_rec => l_split_nsrc_inst_rec);
3011
3012 csi_item_instance_pvt.split_item_instance (
3013 p_api_version => 1.0,
3014 p_commit => fnd_api.g_false,
3015 p_init_msg_list => fnd_api.g_true,
3016 p_validation_level => fnd_api.g_valid_level_full,
3017 p_source_instance_rec => l_split_nsrc_inst_rec,
3018 p_quantity1 => l_quantity1 - l_quantity2 ,
3019 p_quantity2 => l_quantity2,
3020 p_copy_ext_attribs => fnd_api.g_true,
3021 p_copy_org_assignments => fnd_api.g_true,
3022 p_copy_parties => fnd_api.g_true,
3023 p_copy_accounts => fnd_api.g_true,
3024 p_copy_asset_assignments => fnd_api.g_true,
3025 p_copy_pricing_attribs => fnd_api.g_true,
3026 p_txn_rec => l_txn_rec,
3027 x_new_instance_rec => l_new_nsrc_inst_rec,
3028 x_return_status => l_return_status,
3029 x_msg_count => l_msg_count,
3030 x_msg_data => l_msg_data);
3031
3032 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3033 debug('csi_item_instance_pvt.split_item_instance raised errors');
3034 raise fnd_api.g_exc_error;
3035 END IF;
3036
3037 debug('New Instance ID: '||l_new_nsrc_inst_rec.instance_id
3038 ||' New Instance Qty.: '||l_new_nsrc_inst_rec.quantity);
3039
3040 l_upd_inst_tbl(u_ind).instance_id := l_new_nsrc_inst_rec.instance_id ;
3041 l_upd_inst_tbl(u_ind).object_version_number := l_new_nsrc_inst_rec.object_version_number;
3042 l_upd_inst_tbl(u_ind).active_end_date := sysdate;
3043 l_upd_inst_tbl(u_ind).last_oe_rma_line_id := l_txn_rec.source_line_ref_id;
3044 l_upd_inst_tbl(u_ind).last_txn_line_detail_id := l_tld_inst_tbl(m).txn_line_detail_id;
3045 l_upd_inst_tbl(u_ind).instance_status_id := 1; -- just expiring for now
3046 l_tld_inst_tbl(u_ind).processed_flag := 'Y'; --set it to processed
3047 l_rem_qty := l_rem_qty - l_quantity2;
3048 exit;
3049 ELSE
3050 IF nvl(l_end_date , sysdate) between sysdate and sysdate +1 THEN
3051 l_upd_inst_tbl(u_ind).instance_id := l_tld_inst_tbl(m).instance_id;
3052 l_upd_inst_tbl(u_ind).active_end_date := sysdate;
3053 l_upd_inst_tbl(u_ind).last_oe_rma_line_id := l_txn_rec.source_line_ref_id;
3054 l_upd_inst_tbl(u_ind).last_txn_line_detail_id := l_tld_inst_tbl(m).txn_line_detail_id;
3055 l_upd_inst_tbl(u_ind).instance_status_id := 1; -- just expiring for now
3056 l_tld_inst_tbl(u_ind).processed_flag := 'Y'; --set it to processed
3057 l_rem_qty := l_rem_qty - l_quantity2;
3058 exit;
3059 ELSE
3060 FND_MESSAGE.set_name('CSI','CSI_TXN_INVALID_INST_REF');
3061 FND_MESSAGE.set_token('INSTANCE_ID', l_tld_inst_tbl(m).instance_id);
3062 FND_MSG_PUB.add;
3063 END IF;
3064 END IF;
3065 END IF;
3066 END IF;
3067 End Loop;
3068 END IF;
3069 End Loop;
3070 END IF;
3071 IF l_upd_inst_tbl.count > 0 THEN
3072 debug('Multiple Serial control codes. Instances for Final Update:'||l_upd_inst_tbl.count);
3073 For n in l_upd_inst_tbl.first .. l_upd_inst_tbl.last Loop
3074 l_u_instance_rec.instance_id := l_upd_inst_tbl(n).instance_id;
3075 l_u_instance_rec.active_end_date := l_upd_inst_tbl(n).active_end_date;
3076 l_u_instance_rec.instance_status_id := l_upd_inst_tbl(n).instance_status_id;
3077 l_u_instance_rec.last_oe_rma_line_id := l_upd_inst_tbl(n).last_oe_rma_line_id;
3078 l_u_instance_rec.last_txn_line_detail_id := l_upd_inst_tbl(n).last_txn_line_detail_id;
3079 l_u_instance_rec.object_version_number := l_upd_inst_tbl(n).object_version_number;
3080 l_txn_rec.transaction_action_code := 'MULT_ITEM_SRL_CONTROL'; -- temporarily setting a unique identifier
3081
3082 csi_t_gen_utility_pvt.dump_api_info(
3083 p_pkg_name => 'csi_item_instance_pub',
3084 p_api_name => 'update_item_instance');
3085
3086 csi_t_gen_utility_pvt.dump_csi_instance_rec(
3087 p_csi_instance_rec => l_u_instance_rec);
3088
3089 csi_item_instance_pub.update_item_instance(
3090 p_api_version => 1.0,
3091 p_commit => fnd_api.g_false,
3092 p_init_msg_list => fnd_api.g_true,
3093 p_validation_level => fnd_api.g_valid_level_full,
3094 p_instance_rec => l_u_instance_rec,
3095 p_ext_attrib_values_tbl => l_u_ext_attrib_val_tbl,
3096 p_party_tbl => l_u_party_tbl,
3097 p_account_tbl => l_u_party_acct_tbl,
3098 p_pricing_attrib_tbl => l_u_pricing_attribs_tbl,
3099 p_org_assignments_tbl => l_u_org_units_tbl,
3100 p_txn_rec => l_txn_rec,
3101 p_asset_assignment_tbl => l_u_inst_asset_tbl,
3102 x_instance_id_lst => l_u_inst_id_lst,
3103 x_return_status => l_return_status,
3104 x_msg_count => l_msg_count,
3105 x_msg_data => l_msg_data );
3106
3107 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3108 RAISE fnd_api.g_exc_error;
3109 END IF;
3110 End Loop;
3111 END IF;
3112 END IF;
3113 ELSE
3114
3115 debug('Transaction details NOT found for the RMA Order.');
3116
3117 IF l_item_control_rec.serial_control_code = 1 THEN
3118 debug('NON Serialized item and installation details not entered.');
3119 fnd_message.set_name('CSI', 'CSI_INST_DTLS_NOT_ENTERED');
3120 fnd_msg_pub.add;
3121 RAISE fnd_api.g_exc_error;
3122 ELSE
3123 -- serialized item, so figure out the owner's instance
3124 identify_source_instances(
3125 px_mtl_txn_tbl => l_src_mtl_txn_tbl,
3126 p_item_control_rec => l_item_control_rec,
3127 x_return_status => l_return_status);
3128
3129 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3130 RAISE fnd_api.g_exc_error;
3131 END IF;
3132 END IF;
3133
3134 -- build the process transaction pl/sql tables using the mtl txn info
3135 build_process_tables_NOTD(
3136 p_mtl_txn_tbl => l_src_mtl_txn_tbl,
3137 p_item_control_rec => l_item_control_rec,
3138 x_txn_rec => l_txn_rec,
3139 x_instances_tbl => l_instances_tbl,
3140 x_i_parties_tbl => l_i_parties_tbl,
3141 x_ip_accounts_tbl => l_ip_accounts_tbl,
3142 x_org_units_tbl => l_org_units_tbl,
3143 x_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
3144 x_pricing_attribs_tbl => l_pricing_attribs_tbl,
3145 x_instance_asset_tbl => l_instance_asset_tbl,
3146 x_ii_relationships_tbl => l_ii_relationships_tbl,
3147 x_dest_location_rec => l_dest_location_rec,
3148 x_return_status => l_return_status);
3149
3150 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3151 RAISE fnd_api.g_exc_error;
3152 END IF;
3153
3154 END IF;
3155
3156 get_sub_type_rec(
3157 p_transaction_type_id => l_txn_rec.transaction_type_id,
3158 p_sub_type_id => l_txn_rec.txn_sub_type_id,
3159 x_sub_type_rec => l_sub_type_rec,
3160 x_return_status => l_return_status);
3161
3162 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3163 raise fnd_api.g_exc_error;
3164 END IF;
3165
3166 /* start of ER 2646086 + RMA for Repair with different party */
3167 /* Get the value for the source of truth flag */
3168
3169 l_internal_party_id := csi_datastructures_pub.g_install_param_rec.internal_party_id;
3170 l_pty_override_flag := csi_datastructures_pub.g_install_param_rec.ownership_override_at_txn;
3171
3172 /* end of ER 2646086 + RMA for Repair with different party */
3173
3174 -- Added the complete FOR piece for ER 2482219. Return for repair
3175 /*added for 5456153 */
3176 get_partner_order_info(
3177 p_mtl_txn_id => p_mtl_txn_id,
3178 x_partner_order_rec => l_partner_order_rec,
3179 x_end_cust_party_id => l_end_cust_party_id,
3180 x_return_status => l_return_status);
3181
3182 FOR i_ind in l_instances_tbl.FIRST .. l_instances_tbl.LAST
3183 LOOP
3184
3185 IF l_item_control_rec.serial_control_code <> 1 THEN
3186
3187 /* start of ER 2646086 + RMA for Repair with different party */
3188 /* Added the IF piece to get required data. This is only done for the */
3189 /* serialized instances. */
3190
3191 debug('Check if owner needs to be overridden. Return from a different guy.');
3192
3193 IF nvl(l_instances_tbl(i_ind).instance_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
3194 THEN
3195
3196 l_error_rec.instance_id := l_instances_tbl(i_ind).instance_id;
3197
3198 BEGIN
3199
3200 SELECT party_id ,
3201 instance_party_id,
3202 object_version_number
3203 INTO l_cur_owner_party_id,
3204 l_owner_pty_ip_id,
3205 l_owner_pty_obj_ver_num
3206 FROM csi_i_parties
3207 WHERE instance_id = l_instances_tbl(i_ind).instance_id
3208 AND relationship_type_code = 'OWNER';
3209
3210 --## brmanesh leased out internal item may not have a owner account
3211 --## code enhancement required here
3212
3213 -- Added Begin , Exception and End as part of fix for Bug 2733128
3214 BEGIN
3215 SELECT party_account_id,
3216 ip_account_id,
3217 object_version_number
3218 INTO l_cur_owner_acct_id,
3219 l_owner_acct_ipa_id,
3220 l_owner_acct_obj_ver_num
3221 FROM csi_ip_accounts
3222 WHERE instance_party_id = l_owner_pty_ip_id
3223 AND relationship_type_code = 'OWNER';
3224
3225 EXCEPTION
3226 WHEN no_data_found THEN
3227 null;
3228 END;
3229 EXCEPTION
3230 WHEN no_data_found THEN
3231 --## to seed some error message appropriately
3232 RAISE fnd_api.g_exc_error;
3233 END;
3234
3235 debug(' Instance ID :'||l_instances_tbl(i_ind).instance_id);
3236 debug(' Internal Party ID :'||l_internal_party_id);
3237 debug(' Current Party ID :'||l_cur_owner_party_id);
3238 debug(' Current Party Account ID :'||l_cur_owner_acct_id);
3239 debug(' RMA Party ID :'||l_src_order_rec.party_id );
3240 debug(' RMA Party Account ID :'||l_src_order_rec.customer_account_id );
3241 debug(' Party override Flag :'||l_pty_override_flag);
3242 debug(' RMA Owner : :'||l_partner_order_rec.IB_OWNER);
3243 debug(' End Custmer Account ID :'||l_partner_order_rec.END_CUSTOMER_ID);
3244
3245
3246 IF l_cur_owner_party_id <> l_internal_party_id
3247 AND
3248 l_cur_owner_party_id <> l_src_order_rec.party_id
3249 AND
3250 l_pty_override_flag = 'N'
3251 THEN
3252 fnd_message.set_name('CSI','CSI_RMA_OWNER_MISMATCH'); -- need to seed a new message
3253 fnd_message.set_token('INSTANCE_ID', l_instances_tbl(i_ind).instance_id );
3254 fnd_message.set_token('OLD_PARTY_ID', l_cur_owner_party_id );
3255 fnd_message.set_token('NEW_PARTY_ID', l_src_order_rec.party_id );
3256 fnd_msg_pub.add;
3257 RAISE fnd_api.g_exc_error;
3258 END IF;
3259
3260 /* end of ER 2646086 + RMA for Repair with different party */
3261
3262 /* for serial items we do the owner change logic */
3263 IF nvl(l_sub_type_rec.src_change_owner,'N') = 'Y'
3264 AND
3265 l_sub_type_rec.src_change_owner_to_code = 'I' THEN
3266
3267 debug('Building INTERNAL OWNER party rec '||i_ind||' for process transaction.');
3268
3269 i_p_ind := nvl(l_i_parties_tbl.LAST, 0) +1;
3270
3271 l_i_parties_tbl(i_p_ind).parent_tbl_index := i_ind;
3272 l_i_parties_tbl(i_p_ind).party_source_table := 'HZ_PARTIES';
3273 l_i_parties_tbl(i_p_ind).party_id := l_internal_party_id;
3274 l_i_parties_tbl(i_p_ind).relationship_type_code := 'OWNER';
3275 l_i_parties_tbl(i_p_ind).contact_flag := 'N';
3276
3277 ELSIF nvl(l_sub_type_rec.src_change_owner,'N') = 'N' THEN
3278
3279 -- typically the return loaner transaction
3280 IF l_cur_owner_party_id = l_internal_party_id THEN
3281 l_instances_tbl(i_ind).install_location_type_code := null;
3282 l_instances_tbl(i_ind).install_location_id := null;
3283 END IF;
3284
3285 /* start of ER 2646086 + RMA for Repair with different party */
3286 /* Added the IF piece to handle repair cases */
3287
3288 IF l_cur_owner_party_id <> l_internal_party_id
3289 AND
3290 l_cur_owner_party_id <> l_src_order_rec.party_id
3291 AND
3292 l_pty_override_flag = 'Y'
3293 THEN
3294 -- Transfer the Ownership first to the new RMA Customer and then process
3295 -- the RMA as a Normal one.
3296 -- Begin code fix as part of fix for Bug 2733128.
3297
3298
3299 IF (l_partner_ORDER_rec.IB_OWNER = 'END_CUSTOMER' AND l_cur_owner_party_id =l_end_cust_party_id) THEN
3300 debug('Ownership Change not required as End Customer is the Current Owner.');
3301 ELSE
3302
3303 BEGIN
3304 SELECT object_version_number
3305 INTO l_chg_instance_rec.object_version_number
3306 FROM csi_item_instances
3307 WHERE instance_id = l_instances_tbl(i_ind).instance_id;
3308
3309 EXCEPTION
3310 WHEN no_data_found THEN
3311 NULL;
3312 END;
3313
3314 /*Added for End Customer Check 5437907 */
3315 IF (l_partner_ORDER_rec.IB_OWNER = 'END_CUSTOMER' AND l_cur_owner_party_id <>l_end_cust_party_id) THEN
3316 l_upd_parties_tbl(1).party_id := l_end_cust_party_id;
3317 l_upd_pty_accts_tbl(1).party_account_id := l_partner_ORDER_rec.END_CUSTOMER_ID;
3318 ELSE
3319 l_upd_parties_tbl(1).party_id:=l_src_order_rec.party_id;
3320 l_upd_pty_accts_tbl(1).party_account_id := l_src_order_rec.customer_account_id;
3321 END IF;
3322
3323 l_chg_instance_rec.instance_id := l_instances_tbl(i_ind).instance_id;
3324 l_chg_instance_rec.active_end_date := NUll;
3325 -- End code fix as part of fix for Bug 2733128.
3326
3327 l_upd_parties_tbl(1).instance_party_id := l_owner_pty_ip_id;
3328 l_upd_parties_tbl(1).object_version_number := l_owner_pty_obj_ver_num;
3329
3330 l_upd_parties_tbl(1).party_source_table := 'HZ_PARTIES';
3331 -- l_upd_parties_tbl(1).party_id := l_src_order_rec.party_id;
3332 l_upd_parties_tbl(1).relationship_type_code := 'OWNER';
3333 l_upd_parties_tbl(1).contact_flag := 'N';
3334 l_upd_parties_tbl(1).call_contracts := fnd_api.g_false;
3335
3336 l_upd_pty_accts_tbl(1).ip_account_id := l_owner_acct_ipa_id;
3337 l_upd_pty_accts_tbl(1).instance_party_id := l_owner_pty_ip_id;
3338 l_upd_pty_accts_tbl(1).object_version_number:= l_owner_acct_obj_ver_num;
3339 l_upd_pty_accts_tbl(1).parent_tbl_index := 1;
3340 --l_upd_pty_accts_tbl(1).party_account_id := l_src_order_rec.customer_account_id;
3341 l_upd_pty_accts_tbl(1).relationship_type_code := 'OWNER';
3342 l_upd_pty_accts_tbl(1).call_contracts := fnd_api.g_false;
3343
3344 /* Commented the call as part of fix for Bug 2733128. Added call to Update_Item_Instance instead
3345 csi_t_gen_utility_pvt.dump_api_info(
3346 p_pkg_name => 'csi_party_relationships_pub',
3347 p_api_name => 'update_inst_party_relationship');
3348
3349 csi_party_relationships_pub.update_inst_party_relationship (
3350 p_api_version => 1.0,
3351 p_commit => fnd_api.g_false,
3352 p_init_msg_list => fnd_api.g_true,
3353 p_validation_level => fnd_api.g_valid_level_full,
3354 p_party_tbl => l_upd_parties_tbl,
3355 p_party_account_tbl => l_upd_pty_accts_tbl,
3356 p_txn_rec => l_txn_rec,
3357 x_return_status => l_return_status,
3358 x_msg_count => l_msg_count,
3359 x_msg_data => l_msg_data);
3360 */
3361
3362 csi_t_gen_utility_pvt.dump_api_info(
3363 p_pkg_name => 'csi_item_instance_pub',
3364 p_api_name => 'update_item_instance');
3365
3366 csi_item_instance_pub.update_item_instance(
3367 p_api_version => 1.0,
3368 p_commit => fnd_api.g_false,
3369 p_init_msg_list => fnd_api.g_true,
3370 p_validation_level => fnd_api.g_valid_level_full,
3371 p_instance_rec => l_chg_instance_rec,
3372 p_ext_attrib_values_tbl => l_chg_ext_attrib_val_tbl,
3373 p_party_tbl => l_upd_parties_tbl,
3374 p_account_tbl => l_upd_pty_accts_tbl,
3375 p_pricing_attrib_tbl => l_chg_pricing_attribs_tbl,
3376 p_org_assignments_tbl => l_chg_org_units_tbl,
3377 p_txn_rec => l_txn_rec,
3378 p_asset_assignment_tbl => l_chg_inst_asset_tbl,
3379 x_instance_id_lst => l_chg_inst_id_lst,
3380 x_return_status => l_return_status,
3381 x_msg_count => l_msg_count,
3382 x_msg_data => l_msg_data);
3383
3384 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3385 RAISE fnd_api.g_exc_error;
3386 END IF;
3387
3388 debug('Ownership Changed Successfully.');
3389
3390 END IF; --End Customer Check
3391 END IF; -- owner override check
3392
3393 /* end of ER 2646086 + RMA for Repair with different party */
3394
3395 IF l_item_control_rec.serial_control_code = 6 THEN
3396
3397 debug('Building INTERNAL OWNER party rec '||i_ind||' for process transaction.');
3398 i_p_ind := nvl(l_i_parties_tbl.LAST, 0) +1;
3399
3400 l_i_parties_tbl(i_p_ind).parent_tbl_index := i_ind;
3401 l_i_parties_tbl(i_p_ind).party_source_table := 'HZ_PARTIES';
3402 l_i_parties_tbl(i_p_ind).party_id := l_internal_party_id;
3403 l_i_parties_tbl(i_p_ind).relationship_type_code := 'OWNER';
3404 l_i_parties_tbl(i_p_ind).contact_flag := 'N';
3405
3406 END IF;
3407
3408 END IF; -- sub_type ownerchange check
3409
3410 ELSE -- instance not found -- try creating a source
3411
3412 /* this logic is for creation of instances for a first time rma */
3413 /* receipt of serial instance in IB */
3414
3415 -- assign the values to the new src mtl rec from the l instances tbl
3416 l_mtl_txn_rec.inventory_item_id := l_instances_tbl(i_ind).inventory_item_id;
3417 l_mtl_txn_rec.serial_number := l_instances_tbl(i_ind).serial_number;
3418 l_mtl_txn_rec.lot_number := l_instances_tbl(i_ind).lot_number;
3419 l_mtl_txn_rec.transaction_quantity := 1;
3420
3421 -- try one more time to get the source instance.
3422 -- arise, awake and stop not till the goal is reached ...!
3423
3424 identify_source_instance(
3425 px_mtl_txn_rec => l_mtl_txn_rec,
3426 p_item_control_rec => l_item_control_rec,
3427 x_return_status => l_return_status);
3428
3429 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3430 RAISE fnd_api.g_exc_error;
3431 END IF;
3432
3433 IF l_mtl_txn_rec.instance_id = fnd_api.g_miss_num THEN
3434
3435 debug('Create a source serialized instance. Looks like first time IB creation.');
3436
3437 l_crt_instance_rec.instance_id := fnd_api.g_miss_num;
3438 l_crt_instance_rec.instance_number := fnd_api.g_miss_char;
3439 l_crt_instance_rec.inventory_item_id := l_instances_tbl(i_ind).inventory_item_id;
3440 l_crt_instance_rec.inventory_revision := l_instances_tbl(i_ind).inventory_revision;
3441 l_crt_instance_rec.serial_number := l_instances_tbl(i_ind).serial_number;
3442 l_crt_instance_rec.mfg_serial_number_flag := 'Y';
3443 l_crt_instance_rec.lot_number := l_instances_tbl(i_ind).lot_number;
3444 l_crt_instance_rec.quantity := 1;
3445 l_crt_instance_rec.unit_of_measure := l_instances_tbl(i_ind).unit_of_measure;
3446 l_crt_instance_rec.location_type_code := 'HZ_PARTY_SITES';
3447 l_crt_instance_rec.location_id := l_src_order_rec.customer_location_id;
3448 l_crt_instance_rec.instance_usage_code := 'OUT_OF_ENTERPRISE';
3449 l_crt_instance_rec.inv_master_organization_id := l_instances_tbl(i_ind).inv_master_organization_id;
3450 l_crt_instance_rec.vld_organization_id := l_instances_tbl(i_ind).vld_organization_id;
3451 l_crt_instance_rec.last_oe_rma_line_id := l_instances_tbl(i_ind).last_oe_rma_line_id;
3452 l_crt_instance_rec.customer_view_flag := 'N';
3453 l_crt_instance_rec.merchant_view_flag := 'Y';
3454 l_crt_instance_rec.object_version_number := 1;
3455 l_crt_instance_rec.call_contracts := fnd_api.g_false;
3456 --fix for bug5086652
3457 l_crt_instance_rec.active_start_date := l_instances_tbl(i_ind).mtl_txn_creation_date;
3458
3459 l_crt_parties_tbl(1).instance_party_id := fnd_api.g_miss_num;
3460 l_crt_parties_tbl(1).party_source_table := 'HZ_PARTIES';
3461 l_crt_parties_tbl(1).party_id := l_src_order_rec.party_id;
3462 l_crt_parties_tbl(1).relationship_type_code:= 'OWNER';
3463 l_crt_parties_tbl(1).contact_flag := 'N';
3464 l_crt_parties_tbl(1).call_contracts := fnd_api.g_false;
3465
3466 l_crt_pty_accts_tbl(1).ip_account_id := fnd_api.g_miss_num;
3467 l_crt_pty_accts_tbl(1).parent_tbl_index := 1;
3468 l_crt_pty_accts_tbl(1).party_account_id := l_src_order_rec.customer_account_id;
3469 l_crt_pty_accts_tbl(1).relationship_type_code := 'OWNER';
3470 l_crt_pty_accts_tbl(1).call_contracts := fnd_api.g_false;
3471
3472 csi_t_gen_utility_pvt.dump_csi_instance_rec(
3473 p_csi_instance_rec => l_crt_instance_rec);
3474
3475 csi_t_gen_utility_pvt.dump_api_info(
3476 p_pkg_name => 'csi_item_instance_pub',
3477 p_api_name => 'create_item_instance');
3478
3479 csi_item_instance_pub.create_item_instance(
3480 p_api_version => 1.0,
3481 p_commit => fnd_api.g_false,
3482 p_init_msg_list => fnd_api.g_true,
3483 p_validation_level => fnd_api.g_valid_level_full,
3484 p_instance_rec => l_crt_instance_rec,
3485 p_party_tbl => l_crt_parties_tbl,
3486 p_account_tbl => l_crt_pty_accts_tbl,
3487 p_org_assignments_tbl => l_crt_org_units_tbl,
3488 p_ext_attrib_values_tbl => l_crt_ea_values_tbl,
3489 p_pricing_attrib_tbl => l_crt_pricing_tbl,
3490 p_asset_assignment_tbl => l_crt_assets_tbl,
3491 p_txn_rec => l_txn_rec,
3492 x_return_status => l_return_status,
3493 x_msg_count => l_msg_count,
3494 x_msg_data => l_msg_data );
3495
3496 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3497 raise fnd_api.g_exc_error;
3498 END IF;
3499
3500 l_instances_tbl(i_ind).instance_id := l_crt_instance_rec.instance_id;
3501
3502 ELSE
3503 l_instances_tbl(i_ind).instance_id := l_mtl_txn_rec.instance_id;
3504
3505 END IF;-- instance_id = g_miss_num
3506
3507 l_error_rec.instance_id := l_instances_tbl(i_ind).instance_id;
3508 debug('Source Customer Product ID :'||l_crt_instance_rec.instance_id);
3509
3510 IF ( nvl(l_sub_type_rec.src_change_owner,'N') = 'Y'
3511 AND
3512 l_sub_type_rec.src_change_owner_to_code = 'I')
3513 OR
3514 (l_item_control_rec.serial_control_code = 6)
3515 THEN
3516
3517 debug('Building INTERNAL OWNER party rec '||i_ind||' for process transaction.');
3518
3519 i_p_ind := nvl(l_i_parties_tbl.LAST, 0) +1;
3520
3521 l_i_parties_tbl(i_p_ind).parent_tbl_index := i_ind;
3522 l_i_parties_tbl(i_p_ind).party_source_table := 'HZ_PARTIES';
3523 l_i_parties_tbl(i_p_ind).party_id := l_internal_party_id;
3524 l_i_parties_tbl(i_p_ind).relationship_type_code := 'OWNER';
3525 l_i_parties_tbl(i_p_ind).contact_flag := 'N';
3526
3527 END IF;
3528
3529 END IF; -- instance_id = fnd_api.g_miss_num
3530
3531 ELSE -- Non_Serialized Item in INV , serial code = 1
3532 IF nvl(l_instances_tbl(i_ind).instance_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
3533 /* Split the instance at source for Non serialized items */
3534 THEN
3535
3536 BEGIN
3537 SELECT quantity, active_end_date
3538 INTO l_quantity2, l_active_end_date
3539 FROM csi_item_instances
3540 WHERE instance_id = l_instances_tbl(i_ind).instance_id;
3541
3542 debug('Original Instance Quantity: '||l_quantity2);
3543
3544 EXCEPTION
3545 WHEN no_data_found THEN
3546 debug('Failed to retrieve Instance data');
3547 raise fnd_api.g_exc_error;
3548 END;
3549
3550 IF l_quantity2 > l_instances_tbl(i_ind).quantity THEN -- inst qty > txn qty
3551 IF (l_active_end_date is NOT NULL) AND (l_active_end_date <= sysdate)
3552 THEN
3553 -- we will have to unexpire and expire the instance back.
3554 -- Contracts shouldn't be called while unexpiring
3555
3556 csi_process_txn_pvt.unexpire_instance(
3557 p_instance_id => l_instances_tbl(i_ind).instance_id,
3558 p_call_contracts => fnd_api.g_false,
3559 p_transaction_rec => l_txn_rec,
3560 x_return_status => l_return_status);
3561
3562 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3563 RAISE fnd_api.g_exc_error;
3564 END IF;
3565 END IF;
3566
3567 l_quantity1 := l_quantity2 - l_instances_tbl(i_ind).quantity ;
3568 l_quantity2 := l_instances_tbl(i_ind).quantity ;
3569
3570 l_split_src_inst_rec.instance_id := l_instances_tbl(i_ind).instance_id;
3571 --active_start_date added for bug5248037--
3572 l_split_src_inst_rec.active_start_date := l_instances_tbl(i_ind).mtl_txn_creation_date;
3573
3574 l_txn_rec.split_reason_code := 'PARTIAL_RETURN';
3575
3576 csi_t_gen_utility_pvt.dump_api_info(
3577 p_pkg_name => 'csi_item_instance_pvt',
3578 p_api_name => 'split_item_instance');
3579
3580 csi_item_instance_pvt.split_item_instance (
3581 p_api_version => 1.0,
3582 p_commit => fnd_api.g_false,
3583 p_init_msg_list => fnd_api.g_true,
3584 p_validation_level => fnd_api.g_valid_level_full,
3585 p_source_instance_rec => l_split_src_inst_rec,
3586 p_quantity1 => l_quantity1,
3587 p_quantity2 => l_quantity2,
3588 p_copy_ext_attribs => fnd_api.g_true,
3589 p_copy_org_assignments => fnd_api.g_true,
3590 p_copy_parties => fnd_api.g_true,
3591 p_copy_accounts => fnd_api.g_true,
3592 p_copy_asset_assignments => fnd_api.g_true,
3593 p_copy_pricing_attribs => fnd_api.g_true,
3594 p_txn_rec => l_txn_rec,
3595 x_new_instance_rec => l_split_new_inst_rec,
3596 x_return_status => l_return_status,
3597 x_msg_count => l_msg_count,
3598 x_msg_data => l_msg_data);
3599
3600 IF NOT(l_return_status = fnd_api.g_ret_sts_success) THEN
3601 debug('csi_item_instance_pvt.split_item_instance raised errors');
3602 raise fnd_api.g_exc_error;
3603 END IF;
3604
3605 IF (l_active_end_date is NOT NULL) AND (l_active_end_date <= sysdate)
3606 THEN -- expire the instance back.
3607
3608 SELECT object_version_number
3609 INTO l_exp_instance_rec.object_version_number
3610 FROM csi_item_instances
3611 WHERE instance_id = l_instances_tbl(i_ind).instance_id;
3612
3613 l_exp_instance_rec.instance_id := l_instances_tbl(i_ind).instance_id;
3614 l_exp_instance_rec.call_contracts := fnd_api.g_false;
3615 l_exp_instance_rec.active_end_date := sysdate;
3616 l_exp_instance_rec.last_oe_rma_line_id := l_txn_rec.source_line_ref_id;
3617
3618 csi_t_gen_utility_pvt.dump_api_info(
3619 p_pkg_name => 'csi_item_instance_pub',
3620 p_api_name => 'update_item_instance');
3621
3622 csi_t_gen_utility_pvt.dump_csi_instance_rec(
3623 p_csi_instance_rec => l_exp_instance_rec);
3624
3625 csi_item_instance_pub.update_item_instance(
3626 p_api_version => 1.0,
3627 p_commit => fnd_api.g_false,
3628 p_init_msg_list => fnd_api.g_true,
3629 p_validation_level => fnd_api.g_valid_level_full,
3630 p_instance_rec => l_exp_instance_rec,
3631 p_ext_attrib_values_tbl => l_u_ext_attrib_val_tbl,
3632 p_party_tbl => l_u_party_tbl,
3633 p_account_tbl => l_u_party_acct_tbl,
3634 p_pricing_attrib_tbl => l_u_pricing_attribs_tbl,
3635 p_org_assignments_tbl => l_u_org_units_tbl,
3636 p_txn_rec => l_txn_rec,
3637 p_asset_assignment_tbl => l_u_inst_asset_tbl,
3638 x_instance_id_lst => l_u_inst_id_lst,
3639 x_return_status => l_return_status,
3640 x_msg_count => l_msg_count,
3641 x_msg_data => l_msg_data );
3642
3643 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3644 RAISE fnd_api.g_exc_error;
3645 END IF;
3646 END IF;
3647
3648 l_instances_tbl(i_ind).instance_id := l_split_new_inst_rec.instance_id ;
3649 l_instances_tbl(i_ind).object_version_number := l_split_new_inst_rec.object_version_number;
3650 debug('New Instance ID: '||l_split_new_inst_rec.instance_id
3651 ||' New Instance Qty.: '||l_split_new_inst_rec.quantity);
3652
3653 END IF;
3654 END IF;
3655
3656 /* for non serial items we do not work with the owner change logic */
3657
3658 debug('Building INTERNAL OWNER party rec '||i_ind||' for process transaction.');
3659
3660 i_p_ind := nvl(l_i_parties_tbl.LAST, 0) +1;
3661
3662 l_i_parties_tbl(i_p_ind).parent_tbl_index := i_ind;
3663 l_i_parties_tbl(i_p_ind).party_source_table := 'HZ_PARTIES';
3664 l_i_parties_tbl(i_p_ind).party_id := l_internal_party_id;
3665 l_i_parties_tbl(i_p_ind).relationship_type_code := 'OWNER';
3666 l_i_parties_tbl(i_p_ind).contact_flag := 'N';
3667
3668 END IF; -- serial_control code <> 1
3669
3670 END LOOP; -- loop thru the instances table
3671
3672 l_error_rec.instance_id := null;
3673 --
3674 -- srramakr TSO with Equipment
3675 -- RMA Receipt process should nullify the config keys
3676 IF l_instances_tbl.count > 0 THEN
3677 FOR J in l_instances_tbl.FIRST .. l_instances_tbl.LAST LOOP
3678 -- Nullify the Config Keys
3679 l_instances_tbl(J). CONFIG_INST_HDR_ID := NULL;
3680 l_instances_tbl(J). CONFIG_INST_REV_NUM := NULL;
3681 l_instances_tbl(J). CONFIG_INST_ITEM_ID := NULL;
3682 END LOOP;
3683 END IF;
3684 --
3685 debug('Calling Process Transaction Routine.');
3686
3687 -- Call process transaction
3688 csi_process_txn_grp.process_transaction(
3689 p_api_version => l_api_version,
3690 p_commit => l_commit,
3691 p_init_msg_list => l_init_msg_list,
3692 p_validation_level => l_validation_level,
3693 p_validate_only_flag => l_validate_only_flag,
3694 p_in_out_flag => l_in_out_flag,
3695 p_dest_location_rec => l_dest_location_rec,
3696 p_txn_rec => l_txn_rec,
3697 p_instances_tbl => l_instances_tbl,
3698 p_i_parties_tbl => l_i_parties_tbl,
3699 p_ip_accounts_tbl => l_ip_accounts_tbl,
3700 p_org_units_tbl => l_org_units_tbl,
3701 p_ext_attrib_vlaues_tbl => l_ext_attrib_values_tbl,
3702 p_pricing_attribs_tbl => l_pricing_attribs_tbl,
3703 p_instance_asset_tbl => l_instance_asset_tbl,
3704 p_ii_relationships_tbl => l_ii_relationships_tbl,
3705 px_txn_error_rec => l_error_rec,
3706 x_return_status => l_return_status,
3707 x_msg_count => l_msg_count,
3708 x_msg_data => l_msg_data );
3709
3710 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3711 debug('Process transaction routine failed.');
3712 RAISE fnd_api.g_exc_error;
3713 END IF;
3714
3715 --update the csi txn line details with the processed status
3716
3717 Begin
3718 --Assign the literals..
3719 l_literal1 := 'IN_PROCESS';
3720 l_literal2 := 'OE_ORDER_LINES_ALL';
3721
3722 UPDATE csi_t_txn_line_details a
3723 SET error_code = NULL,
3724 error_explanation = NULL ,
3725 processing_status = 'PROCESSED'
3726 WHERE a.processing_status = l_literal1
3727 AND a.source_transaction_flag = 'Y'
3728 AND a.inventory_item_id = l_item_control_rec.inventory_item_id
3729 AND a.transaction_line_id in (SELECT transaction_line_id
3730 FROM csi_t_transaction_lines b
3731 WHERE -- a.transaction_line_id = b.transaction_line_id AND -- Commented for Perf Bug 4311676
3732 b.source_transaction_id = l_rma_order_rec.source_line_id
3733 AND b.source_transaction_table = l_literal2 );
3734 EXCEPTION
3735 WHEN others THEN
3736 debug('Failed to Update the Transaction Details data');
3737 End;
3738
3739 debug('RMA Receipt Interface Successful for Material Transaction ID :'||p_mtl_txn_id);
3740
3741 IF l_td_found THEN
3742 debug('Processing Transaction details in case of Partial RMA receipts.');
3743 l_txn_line_rec.source_transaction_table := 'OE_ORDER_LINES_ALL';
3744 l_txn_line_rec.source_transaction_id := l_rma_order_rec.source_line_id; -- l_src_mtl_txn_tbl(1).oe_line_id;
3745 l_txn_line_rec.source_transaction_type_id := 53;
3746 l_split_txn_line_rec.source_transaction_table := 'OE_ORDER_LINES_ALL';
3747 l_split_txn_line_rec.source_transaction_type_id := 53;
3748 l_line_dtl_tbl(1).inv_mtl_transaction_id := p_mtl_txn_id;
3749 l_td_found := FALSE;
3750
3751 BEGIN
3752 SELECT line_id,
3753 header_id
3754 INTO l_split_txn_line_rec.source_transaction_id,
3755 l_split_txn_line_rec.source_txn_header_id
3756 FROM oe_order_lines_all
3757 WHERE split_from_line_id = l_rma_order_rec.source_line_id --l_src_mtl_txn_tbl(1).oe_line_id
3758 AND header_id = l_rma_order_rec.source_header_id ;
3759
3760 l_partial_receipt := TRUE;
3761 l_td_found := csi_t_txn_details_pvt.check_txn_details_exist(
3762 p_txn_line_rec => l_split_txn_line_rec);
3763 EXCEPTION
3764 WHEN TOO_MANY_ROWS THEN
3765 debug('Multiple RMA split lines found in OM for the RMA Order.');
3766 RAISE fnd_api.g_exc_error;
3767 l_partial_receipt := FALSE;
3768 l_td_found := FALSE;
3769 WHEN OTHERS THEN
3770 l_partial_receipt := FALSE;
3771 l_td_found := FALSE;
3772 END;
3773
3774 IF l_partial_receipt AND NOT l_td_found THEN
3775 csi_t_txn_details_grp.split_transaction_details(
3776 p_api_version => 1.0,
3777 p_commit => fnd_api.g_false,
3778 p_init_msg_list => fnd_api.g_true,
3779 p_validation_level => fnd_api.g_valid_level_full,
3780 p_src_txn_line_rec => l_txn_line_rec,
3781 px_split_txn_line_rec => l_split_txn_line_rec,
3782 px_line_dtl_tbl => l_line_dtl_tbl,
3783 x_pty_dtl_tbl => l_pty_dtl_tbl,
3784 x_pty_acct_tbl => l_pty_acct_tbl,
3785 x_org_assgn_tbl => l_org_assgn_tbl,
3786 x_txn_ext_attrib_vals_tbl => l_txn_eav_tbl,
3787 x_txn_systems_tbl => l_txn_systems_tbl,
3788 x_return_status => l_return_status,
3789 x_msg_count => l_msg_count,
3790 x_msg_data => l_msg_data);
3791
3792 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3793 debug(l_msg_data);
3794 Debug(' Failed to post Transaction Details to split RMA Line for partial RMA Cases.');
3795 ELSE
3796 Debug(' Transaction Details succefully posted to split RMA Line.');
3797 END IF;
3798 END IF;
3799 END IF;
3800
3801 EXCEPTION
3802 WHEN fnd_api.g_exc_error THEN
3803
3804 rollback to rma_receipt;
3805
3806 x_return_status := fnd_api.g_ret_sts_error;
3807 l_error_rec.error_text := csi_t_gen_utility_pvt.dump_error_stack;
3808 debug('Error:(E) '||l_error_rec.error_text);
3809 px_trx_error_rec := l_error_rec;
3810
3811 WHEN others THEN
3812 rollback to rma_receipt;
3813
3814 x_return_status := fnd_api.g_ret_sts_unexp_error;
3815 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
3816 fnd_message.set_token('API_NAME',l_api_name);
3817 fnd_message.set_token('SQL_ERROR',substr(sqlerrm, 1, 540));
3818 fnd_msg_pub.add;
3819 l_error_rec.error_text := csi_t_gen_utility_pvt.dump_error_stack;
3820 debug('Error:(O) '||l_error_rec.error_text);
3821 px_trx_error_rec := l_error_rec;
3822
3823 END rma_receipt;
3824
3825
3826 PROCEDURE get_rma_info(
3827 p_transaction_id IN number,
3828 x_mtl_trx_type OUT NOCOPY mtl_trx_type,
3829 x_error_message OUT NOCOPY varchar2,
3830 x_return_status OUT NOCOPY varchar2)
3831 IS
3832 BEGIN
3833
3834 x_return_status := fnd_api.g_ret_sts_success;
3835
3836 BEGIN
3837
3838 SELECT transaction_id,
3839 transaction_date,
3840 transaction_type_id,
3841 trx_source_line_id
3842 INTO x_mtl_trx_type.transaction_id,
3843 x_mtl_trx_type.transaction_date,
3844 x_mtl_trx_type.transaction_type_id,
3845 x_mtl_trx_type.source_line_id
3846 FROM mtl_material_transactions
3847 WHERE transaction_id = p_transaction_id;
3848
3849 EXCEPTION
3850 WHEN no_data_found THEN
3851 fnd_message.set_name('CSI', 'CSI_INT_MTL_TXN_ID_INVALID');
3852 fnd_message.set_token('MTL_TXN_ID', p_transaction_id);
3853 fnd_msg_pub.add;
3854 RAISE fnd_api.g_exc_error;
3855 END;
3856
3857 BEGIN
3858
3859 SELECT ooh.header_id,
3860 ooh.order_number,
3861 ool.line_id,
3862 ool.line_number||'.'||ool.shipment_number
3863 INTO x_mtl_trx_type.source_header_id,
3864 x_mtl_trx_type.source_header_ref,
3865 x_mtl_trx_type.source_line_id,
3866 x_mtl_trx_type.source_line_ref
3867 FROM oe_order_headers_all ooh,
3868 oe_order_lines_all ool
3869 WHERE ool.line_id = x_mtl_trx_type.source_line_id
3870 AND ool.header_id = ooh.header_id;
3871
3872 EXCEPTION
3873 WHEN no_data_found THEN
3874 null;
3875 END;
3876
3877 EXCEPTION
3878 WHEN fnd_api.g_exc_error THEN
3879 x_error_message := fnd_msg_pub.get;
3880 x_return_status := fnd_api.g_ret_sts_error;
3881 END get_rma_info;
3882
3883
3884 PROCEDURE decode_message (
3885 p_msg_header IN XNP_MESSAGE.MSG_HEADER_REC_TYPE,
3886 p_msg_text IN VARCHAR2,
3887 x_mtl_trx_rec OUT NOCOPY MTL_TRX_TYPE,
3888 x_error_message OUT NOCOPY VARCHAR2,
3889 x_return_status OUT NOCOPY VARCHAR2)
3890 IS
3891
3892 l_api_name VARCHAR2(100):= 'csi_wip_trxs_pkg.decode_message';
3893 l_fnd_unexpected VARCHAR2(1) := fnd_api.g_ret_sts_unexp_error;
3894 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3895 l_mtl_txn_id number;
3896
3897 BEGIN
3898
3899 xnp_xml_utils.decode(P_Msg_Text, 'MTL_TRANSACTION_ID', l_mtl_txn_id);
3900
3901 IF nvl(l_mtl_txn_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
3902
3903 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
3904 fnd_message.set_token('MESSAGE',
3905 'xnp_xml_utils.decode failed for '||p_msg_header.message_id);
3906 fnd_msg_pub.add;
3907
3908 RAISE fnd_api.g_exc_error;
3909 END IF;
3910
3911 get_rma_info(
3912 p_transaction_id => l_mtl_txn_id,
3913 x_mtl_trx_type => x_mtl_trx_rec,
3914 x_error_message => x_error_message,
3915 x_return_status => l_return_status);
3916
3917 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3918 RAISE fnd_api.g_exc_error;
3919 END IF;
3920
3921 EXCEPTION
3922
3923 WHEN fnd_api.g_exc_error THEN
3924 x_error_message := csi_t_gen_utility_pvt.dump_error_stack;
3925 x_return_status := fnd_api.g_ret_sts_error;
3926
3927 WHEN others THEN
3928 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
3929 fnd_message.set_token('API_NAME',l_api_name);
3930 fnd_message.set_token('SQL_ERROR',SQLERRM);
3931 x_error_message := fnd_msg_pub.get;
3932 x_return_status := l_fnd_unexpected;
3933
3934 END decode_message;
3935
3936 END csi_rma_receipt_pub;