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