[Home] [Help]
1: package body CSI_INV_PROJECT_PKG as
2: -- $Header: csiivtpb.pls 120.3.12020000.3 2012/09/18 06:48:08 dsingire ship $
3:
4: l_debug NUMBER := csi_t_gen_utility_pvt.g_debug_level;
5:
6: PROCEDURE issue_to_project(p_transaction_id IN NUMBER,
7: p_message_id IN NUMBER,
8: x_return_status OUT NOCOPY VARCHAR2,
86:
87: x_return_status := l_fnd_success;
88:
89: IF (l_debug > 0) THEN
90: csi_t_gen_utility_pvt.add('*****Start of csi_inv_trxs_pkg.issue_to_project Transaction procedure*****');
91: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
92: END IF;
93:
94: -- This procedure queries all of the Inventory Transaction Records and returns them
87: x_return_status := l_fnd_success;
88:
89: IF (l_debug > 0) THEN
90: csi_t_gen_utility_pvt.add('*****Start of csi_inv_trxs_pkg.issue_to_project Transaction procedure*****');
91: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
92: END IF;
93:
94: -- This procedure queries all of the Inventory Transaction Records and returns them
95: -- as a table.
100:
101: l_tbl_count := 0;
102: l_tbl_count := l_mtl_item_tbl.count;
103: IF (l_debug > 0) THEN
104: csi_t_gen_utility_pvt.add('Inventory Records Found: '||l_tbl_count);
105: END IF;
106:
107: IF NOT l_return_status = l_fnd_success THEN
108: IF (l_debug > 0) THEN
105: END IF;
106:
107: IF NOT l_return_status = l_fnd_success THEN
108: IF (l_debug > 0) THEN
109: csi_t_gen_utility_pvt.add('You have encountered an error in CSI_INV_TRXS_PKG.get_transaction_recs, Transaction ID: '||p_transaction_id);
110: END IF;
111: RAISE fnd_api.g_exc_error;
112: END IF;
113:
118: l_error_message);
119:
120: IF NOT l_return_status = l_fnd_success THEN
121: IF (l_debug > 0) THEN
122: csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.get_master_organization, Organization ID: '||l_mtl_item_tbl(i).organization_id);
123: END IF;
124: RAISE fnd_api.g_exc_error;
125: END IF;
126:
128: l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
129:
130: IF l_employee_id = -1 THEN
131: IF (l_debug > 0) THEN
132: csi_t_gen_utility_pvt.add('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
133: END IF;
134: END IF;
135: IF (l_debug > 0) THEN
136: csi_t_gen_utility_pvt.add('The Employee that is processing this Transaction is: '||l_employee_id);
132: csi_t_gen_utility_pvt.add('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
133: END IF;
134: END IF;
135: IF (l_debug > 0) THEN
136: csi_t_gen_utility_pvt.add('The Employee that is processing this Transaction is: '||l_employee_id);
137: END IF;
138:
139: -- See if this is a depreciable Item to set the status of the transaction record
140: csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
141: l_depreciable,
142: l_mtl_item_tbl(i).organization_id); --Added for Bug 13988660
143:
144: IF (l_debug > 0) THEN
145: csi_t_gen_utility_pvt.add('Is this Item ID: '||l_mtl_item_tbl(i).inventory_item_id||', Depreciable :'||l_depreciable);
146: END IF;
147:
148: -- Set the mfg_serial_number_flag and quantity
149: IF l_mtl_item_tbl(i).serial_number is NULL THEN
169: l_instance_status := FND_API.G_TRUE;
170: END IF;
171:
172: IF (l_debug > 0) THEN
173: csi_t_gen_utility_pvt.add('Negative Code is - 1 = Yes, 2 = No: '||l_neg_code);
174: END IF;
175:
176: IF (l_debug > 0) THEN
177: csi_t_gen_utility_pvt.add('Starting to loop through Material Transaction Records');
173: csi_t_gen_utility_pvt.add('Negative Code is - 1 = Yes, 2 = No: '||l_neg_code);
174: END IF;
175:
176: IF (l_debug > 0) THEN
177: csi_t_gen_utility_pvt.add('Starting to loop through Material Transaction Records');
178: END IF;
179:
180: -- Initialize Transaction Record
181: l_txn_rec := csi_inv_trxs_pkg.init_txn_rec;
200: END IF;
201:
202: IF NOT l_return_status = l_fnd_success THEN
203: IF (l_debug > 0) THEN
204: csi_t_gen_utility_pvt.add('Redeploy Flag: '||l_redeploy_flag);
205: csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.get_redeploy_flag: '||l_error_message);
206: END IF;
207: RAISE fnd_api.g_exc_error;
208: END IF;
201:
202: IF NOT l_return_status = l_fnd_success THEN
203: IF (l_debug > 0) THEN
204: csi_t_gen_utility_pvt.add('Redeploy Flag: '||l_redeploy_flag);
205: csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.get_redeploy_flag: '||l_error_message);
206: END IF;
207: RAISE fnd_api.g_exc_error;
208: END IF;
209:
207: RAISE fnd_api.g_exc_error;
208: END IF;
209:
210: IF (l_debug > 0) THEN
211: csi_t_gen_utility_pvt.add('Redeploy Flag: '||l_redeploy_flag);
212: csi_t_gen_utility_pvt.add('Trans Status Code: '||l_txn_rec.transaction_status_code);
213: END IF;
214:
215: -- Get Default Status ID
208: END IF;
209:
210: IF (l_debug > 0) THEN
211: csi_t_gen_utility_pvt.add('Redeploy Flag: '||l_redeploy_flag);
212: csi_t_gen_utility_pvt.add('Trans Status Code: '||l_txn_rec.transaction_status_code);
213: END IF;
214:
215: -- Get Default Status ID
216: OPEN c_id;
235: l_error_message,
236: l_return_status);
237:
238: IF (l_debug > 0) THEN
239: csi_t_gen_utility_pvt.add('CSI Transaction Created: '||l_txn_rec.transaction_id);
240: END IF;
241:
242: IF NOT l_return_status = l_fnd_success THEN
243: IF (l_debug > 0) THEN
240: END IF;
241:
242: IF NOT l_return_status = l_fnd_success THEN
243: IF (l_debug > 0) THEN
244: csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.create_csi_txn: '||p_transaction_id);
245: END IF;
246: RAISE fnd_api.g_exc_error;
247: END IF;
248:
248:
249: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
250:
251: IF (l_debug > 0) THEN
252: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
253: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
254: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
255: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
256: END IF;
249: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
250:
251: IF (l_debug > 0) THEN
252: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
253: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
254: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
255: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
256: END IF;
257:
250:
251: IF (l_debug > 0) THEN
252: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
253: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
254: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
255: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
256: END IF;
257:
258: l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
251: IF (l_debug > 0) THEN
252: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
253: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
254: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
255: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
256: END IF;
257:
258: l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
259: l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
265: l_instance_query_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
266: l_instance_query_rec.instance_usage_code := l_in_inventory;
267:
268: IF (l_debug > 0) THEN
269: csi_t_gen_utility_pvt.add('Before Get Item Instance');
270: END IF;
271:
272: csi_item_instance_pub.get_item_instances(l_api_version,
273: l_commit,
284: l_msg_count,
285: l_msg_data);
286:
287: IF (l_debug > 0) THEN
288: csi_t_gen_utility_pvt.add('After Get Item Instance');
289: END IF;
290: l_tbl_count := 0;
291: l_tbl_count := l_src_instance_header_tbl.count;
292: IF (l_debug > 0) THEN
289: END IF;
290: l_tbl_count := 0;
291: l_tbl_count := l_src_instance_header_tbl.count;
292: IF (l_debug > 0) THEN
293: csi_t_gen_utility_pvt.add('Source Records Found: '||l_tbl_count);
294: END IF;
295: -- Check for any errors and add them to the message stack to pass out to be put into the
296: -- error log table.
297: IF NOT l_return_status = l_fnd_success then
295: -- Check for any errors and add them to the message stack to pass out to be put into the
296: -- error log table.
297: IF NOT l_return_status = l_fnd_success then
298: IF (l_debug > 0) THEN
299: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
300: END IF;
301: l_msg_index := 1;
302: WHILE l_msg_count > 0 loop
303: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
312: IF l_src_instance_header_tbl.count = 0 THEN
313: IF l_neg_code = 1 THEN -- Allow Neg Qtys on NON Serial Items ONLY
314:
315: IF (l_debug > 0) THEN
316: csi_t_gen_utility_pvt.add('No records were found so create a new Source Instance Record');
317: END IF;
318:
319: l_new_src_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
320: l_new_src_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
345: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
346: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
347:
348: IF (l_debug > 0) THEN
349: csi_t_gen_utility_pvt.add('Before Create Source Item Instance');
350: END IF;
351:
352: csi_item_instance_pub.create_item_instance(l_api_version,
353: l_commit,
365: l_msg_count,
366: l_msg_data);
367:
368: IF (l_debug > 0) THEN
369: csi_t_gen_utility_pvt.add('After Create Source Item Instance');
370: csi_t_gen_utility_pvt.add('After Create of Source Item Instance');
371: csi_t_gen_utility_pvt.add('New instance created is: '||l_new_src_instance_rec.instance_id);
372: END IF;
373:
366: l_msg_data);
367:
368: IF (l_debug > 0) THEN
369: csi_t_gen_utility_pvt.add('After Create Source Item Instance');
370: csi_t_gen_utility_pvt.add('After Create of Source Item Instance');
371: csi_t_gen_utility_pvt.add('New instance created is: '||l_new_src_instance_rec.instance_id);
372: END IF;
373:
374: -- Check for any errors and add them to the message stack to pass out to be put into the
367:
368: IF (l_debug > 0) THEN
369: csi_t_gen_utility_pvt.add('After Create Source Item Instance');
370: csi_t_gen_utility_pvt.add('After Create of Source Item Instance');
371: csi_t_gen_utility_pvt.add('New instance created is: '||l_new_src_instance_rec.instance_id);
372: END IF;
373:
374: -- Check for any errors and add them to the message stack to pass out to be put into the
375: -- error log table.
374: -- Check for any errors and add them to the message stack to pass out to be put into the
375: -- error log table.
376: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
377: IF (l_debug > 0) THEN
378: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
379: END IF;
380: l_msg_index := 1;
381: WHILE l_msg_count > 0 loop
382: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
387: END IF;
388:
389: ELSE -- No Records were found and Neg Qtys Not Allowed
390: IF (l_debug > 0) THEN
391: csi_t_gen_utility_pvt.add('No Records were found in Install Base andNeg Qtys not allowed to error');
392: END IF;
393: fnd_message.set_name('CSI','CSI_NO_NEG_BAL_ALLOWED');
394: l_error_message := fnd_message.get;
395: RAISE fnd_api.g_exc_error;
400: -- Records found so make sure that is is updated to be unexp
401: -- and subtract the quantity from source record
402:
403: IF (l_debug > 0) THEN
404: csi_t_gen_utility_pvt.add('You will update instance: '||l_src_instance_header_tbl(i).instance_id);
405: csi_t_gen_utility_pvt.add('End Date is: '||l_src_instance_header_tbl(i).active_end_date);
406: END IF;
407: l_update_source_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
408: l_update_source_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
401: -- and subtract the quantity from source record
402:
403: IF (l_debug > 0) THEN
404: csi_t_gen_utility_pvt.add('You will update instance: '||l_src_instance_header_tbl(i).instance_id);
405: csi_t_gen_utility_pvt.add('End Date is: '||l_src_instance_header_tbl(i).active_end_date);
406: END IF;
407: l_update_source_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
408: l_update_source_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
409: l_update_source_instance_rec.quantity := l_src_instance_header_tbl(i).quantity - abs(l_mtl_item_tbl(j).primary_quantity);
418:
419: l_update_source_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
420:
421: IF (l_debug > 0) THEN
422: csi_t_gen_utility_pvt.add('Before Update Item Instance');
423: END IF;
424:
425: csi_item_instance_pub.update_item_instance(l_api_version,
426: l_commit,
442: l_upd_error_instance_id := NULL;
443: l_upd_error_instance_id := l_update_source_instance_rec.instance_id;
444:
445: IF (l_debug > 0) THEN
446: csi_t_gen_utility_pvt.add('After Update Item Instance');
447: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
448: END IF;
449:
450: IF (l_debug > 0) THEN
443: l_upd_error_instance_id := l_update_source_instance_rec.instance_id;
444:
445: IF (l_debug > 0) THEN
446: csi_t_gen_utility_pvt.add('After Update Item Instance');
447: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
448: END IF;
449:
450: IF (l_debug > 0) THEN
451: csi_t_gen_utility_pvt.add('Instance Status Id: '||l_update_source_instance_rec.instance_status_id);
447: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
448: END IF;
449:
450: IF (l_debug > 0) THEN
451: csi_t_gen_utility_pvt.add('Instance Status Id: '||l_update_source_instance_rec.instance_status_id);
452: END IF;
453:
454: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
455: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
453:
454: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
455: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
456: IF (l_debug > 0) THEN
457: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
458: END IF;
459: l_msg_index := 1;
460: WHILE l_msg_count > 0 loop
461: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
467:
468: ELSIF l_src_instance_header_tbl.count > 1 THEN
469: -- Multiple Instances were found so throw error
470: IF (l_debug > 0) THEN
471: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
472: END IF;
473: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
474: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
475: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
488: l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
489: l_instance_query_rec.instance_usage_code := l_in_process;
490:
491: IF (l_debug > 0) THEN
492: csi_t_gen_utility_pvt.add('Before Destination Get Item Instance - Neg Qty');
493: END IF;
494:
495: csi_item_instance_pub.get_item_instances(l_api_version,
496: l_commit,
507: l_msg_count,
508: l_msg_data);
509:
510: IF (l_debug > 0) THEN
511: csi_t_gen_utility_pvt.add('After Destination Get Item Instance');
512: END IF;
513: l_tbl_count := 0;
514: l_tbl_count := l_dest_instance_header_tbl.count;
515: IF (l_debug > 0) THEN
512: END IF;
513: l_tbl_count := 0;
514: l_tbl_count := l_dest_instance_header_tbl.count;
515: IF (l_debug > 0) THEN
516: csi_t_gen_utility_pvt.add('Destination Records Found: '||l_tbl_count);
517: END IF;
518:
519: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
520: IF NOT l_return_status = l_fnd_success then
518:
519: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
520: IF NOT l_return_status = l_fnd_success then
521: IF (l_debug > 0) THEN
522: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
523: END IF;
524: l_msg_index := 1;
525: WHILE l_msg_count > 0 loop
526: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
531: END IF;
532:
533: IF l_dest_instance_header_tbl.count = 0 THEN -- Installed Base Destination Records are not found
534: IF (l_debug > 0) THEN
535: csi_t_gen_utility_pvt.add('No Destination Records were found so we will create a new destination Record using the source data');
536: END IF;
537:
538: l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
539: l_new_dest_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
565: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
566: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
567:
568: IF (l_debug > 0) THEN
569: csi_t_gen_utility_pvt.add('Before Create Item Instance - Neg Qty');
570: END IF;
571:
572: csi_item_instance_pub.create_item_instance(l_api_version,
573: l_commit,
585: l_msg_count,
586: l_msg_data);
587:
588: IF (l_debug > 0) THEN
589: csi_t_gen_utility_pvt.add('After Create Item Instance');
590: csi_t_gen_utility_pvt.add('Item Instance Created: '||l_new_dest_instance_rec.instance_id);
591: END IF;
592:
593: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
586: l_msg_data);
587:
588: IF (l_debug > 0) THEN
589: csi_t_gen_utility_pvt.add('After Create Item Instance');
590: csi_t_gen_utility_pvt.add('Item Instance Created: '||l_new_dest_instance_rec.instance_id);
591: END IF;
592:
593: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
594: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
592:
593: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
594: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
595: IF (l_debug > 0) THEN
596: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
597: END IF;
598: l_msg_index := 1;
599: WHILE l_msg_count > 0 loop
600: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
606:
607: ELSIF l_dest_instance_header_tbl.count = 1 THEN-- Installed Base Destination Records Found
608:
609: IF (l_debug > 0) THEN
610: csi_t_gen_utility_pvt.add('You will update instance: '||l_dest_instance_header_tbl(i).instance_id);
611: END IF;
612:
613: l_update_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
614: l_update_dest_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
624:
625: l_update_dest_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
626:
627: IF (l_debug > 0) THEN
628: csi_t_gen_utility_pvt.add('Before Update Item Instance - Neg Qty');
629: END IF;
630:
631: csi_item_instance_pub.update_item_instance(l_api_version,
632: l_commit,
648: l_upd_error_instance_id := NULL;
649: l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
650:
651: IF (l_debug > 0) THEN
652: csi_t_gen_utility_pvt.add('After Update Item Instance - Neg Qty');
653: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
654: END IF;
655:
656: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
649: l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
650:
651: IF (l_debug > 0) THEN
652: csi_t_gen_utility_pvt.add('After Update Item Instance - Neg Qty');
653: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
654: END IF;
655:
656: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
657: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
655:
656: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
657: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
658: IF (l_debug > 0) THEN
659: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
660: END IF;
661: l_msg_index := 1;
662: WHILE l_msg_count > 0 loop
663: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
669:
670: ELSIF l_dest_instance_header_tbl.count > 1 THEN
671: -- Multiple Instances were found so throw error
672: IF (l_debug > 0) THEN
673: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
674: END IF;
675: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
676: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
677: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
683:
684: ELSIF l_mtl_item_tbl(j).serial_number is NOT NULL THEN
685: IF l_src_instance_header_tbl.count = 1 THEN -- Installed Base Records Found
686: IF (l_debug > 0) THEN
687: csi_t_gen_utility_pvt.add('You are updating an Item Instance Record');
688: csi_t_gen_utility_pvt.add('You are updating a Serialized Item: '||l_src_instance_header_tbl(i).instance_id);
689: csi_t_gen_utility_pvt.add('The Transaction Status Code will be - Complete (C) or Incomplete (I): '||l_trans_status_code);
690: END IF;
691:
684: ELSIF l_mtl_item_tbl(j).serial_number is NOT NULL THEN
685: IF l_src_instance_header_tbl.count = 1 THEN -- Installed Base Records Found
686: IF (l_debug > 0) THEN
687: csi_t_gen_utility_pvt.add('You are updating an Item Instance Record');
688: csi_t_gen_utility_pvt.add('You are updating a Serialized Item: '||l_src_instance_header_tbl(i).instance_id);
689: csi_t_gen_utility_pvt.add('The Transaction Status Code will be - Complete (C) or Incomplete (I): '||l_trans_status_code);
690: END IF;
691:
692: l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
685: IF l_src_instance_header_tbl.count = 1 THEN -- Installed Base Records Found
686: IF (l_debug > 0) THEN
687: csi_t_gen_utility_pvt.add('You are updating an Item Instance Record');
688: csi_t_gen_utility_pvt.add('You are updating a Serialized Item: '||l_src_instance_header_tbl(i).instance_id);
689: csi_t_gen_utility_pvt.add('The Transaction Status Code will be - Complete (C) or Incomplete (I): '||l_trans_status_code);
690: END IF;
691:
692: l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
693: l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
711: l_org_assignments_tbl.delete;
712: l_asset_assignment_tbl.delete;
713:
714: IF (l_debug > 0) THEN
715: csi_t_gen_utility_pvt.add('Before Update Item Instance');
716: END IF;
717:
718: csi_item_instance_pub.update_item_instance(l_api_version,
719: l_commit,
735: l_upd_error_instance_id := NULL;
736: l_upd_error_instance_id := l_update_instance_rec.instance_id;
737:
738: IF (l_debug > 0) THEN
739: csi_t_gen_utility_pvt.add('After Update Item Instance');
740: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
741: END IF;
742:
743: -- Check for any errors and add them to the message stack to pass out to be put into the
736: l_upd_error_instance_id := l_update_instance_rec.instance_id;
737:
738: IF (l_debug > 0) THEN
739: csi_t_gen_utility_pvt.add('After Update Item Instance');
740: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
741: END IF;
742:
743: -- Check for any errors and add them to the message stack to pass out to be put into the
744: -- error log table.
743: -- Check for any errors and add them to the message stack to pass out to be put into the
744: -- error log table.
745: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
746: IF (l_debug > 0) THEN
747: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
748: END IF;
749: l_msg_index := 1;
750: WHILE l_msg_count > 0 loop
751: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
755: RAISE fnd_api.g_exc_error;
756: END IF;
757: ELSIF l_src_instance_header_tbl.count = 0 THEN
758: IF (l_debug > 0) THEN
759: csi_t_gen_utility_pvt.add('No Records were found in Install Base');
760: END IF;
761: fnd_message.set_name('CSI','CSI_IB_RECORD_NOTFOUND');
762: fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
763: fnd_message.set_token('SUBINVENTORY',l_mtl_item_tbl(j).subinventory_code);
767:
768: ELSIF l_src_instance_header_tbl.count > 1 THEN
769: -- Multiple Instances were found so throw error
770: IF (l_debug > 0) THEN
771: csi_t_gen_utility_pvt.add('Multiple Instances were Found in InstallBase-40');
772: END IF;
773: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
774: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
775: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
781: END IF; -- End of Serial Number If
782: END LOOP; -- End of For Loop
783:
784: IF (l_debug > 0) THEN
785: csi_t_gen_utility_pvt.add('*****End of csi_inv_trxs_pkg.issue_to_project Transaction procedure*****');
786: END IF;
787:
788: EXCEPTION
789: WHEN fnd_api.g_exc_error THEN
787:
788: EXCEPTION
789: WHEN fnd_api.g_exc_error THEN
790: IF (l_debug > 0) THEN
791: csi_t_gen_utility_pvt.add('You have encountered a "fnd_api.g_exc_error" exception');
792: END IF;
793: x_return_status := l_fnd_error;
794:
795: IF l_mtl_item_tbl.count > 0 THEN
815: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
816:
817: WHEN others THEN
818: IF (l_debug > 0) THEN
819: csi_t_gen_utility_pvt.add('You have encountered a "others" exception');
820: END IF;
821: fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
822: fnd_message.set_token('API_NAME',l_api_name);
823: fnd_message.set_token('SQL_ERROR',SQLERRM);
931:
932: x_return_status := l_fnd_success;
933:
934: IF (l_debug > 0) THEN
935: csi_t_gen_utility_pvt.add('*****Start of csi_inv_trxs_pkg.misc_receipt_projtask Transaction procedure*****');
936: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
937: END IF;
938:
939: -- This procedure queries all of the Inventory Transaction Records and returns them
932: x_return_status := l_fnd_success;
933:
934: IF (l_debug > 0) THEN
935: csi_t_gen_utility_pvt.add('*****Start of csi_inv_trxs_pkg.misc_receipt_projtask Transaction procedure*****');
936: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
937: END IF;
938:
939: -- This procedure queries all of the Inventory Transaction Records and returns them
940: -- as a table.
945:
946: l_tbl_count := 0;
947: l_tbl_count := l_mtl_item_tbl.count;
948: IF (l_debug > 0) THEN
949: csi_t_gen_utility_pvt.add('Inventory Records Found: '||l_tbl_count);
950: END IF;
951:
952: IF NOT l_return_status = l_fnd_success THEN
953: IF (l_debug > 0) THEN
950: END IF;
951:
952: IF NOT l_return_status = l_fnd_success THEN
953: IF (l_debug > 0) THEN
954: csi_t_gen_utility_pvt.add('You have encountered an error in CSI_INV_TRXS_PKG.get_transaction_recs, Transaction ID: '||p_transaction_id);
955: END IF;
956: RAISE fnd_api.g_exc_error;
957: END IF;
958:
963: l_error_message);
964:
965: IF NOT l_return_status = l_fnd_success THEN
966: IF (l_debug > 0) THEN
967: csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.get_master_organization, Organization ID: '||l_mtl_item_tbl(i).organization_id);
968: END IF;
969: RAISE fnd_api.g_exc_error;
970: END IF;
971:
973: l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
974:
975: IF l_employee_id = -1 THEN
976: IF (l_debug > 0) THEN
977: csi_t_gen_utility_pvt.add('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
978: END IF;
979: END IF;
980: IF (l_debug > 0) THEN
981: csi_t_gen_utility_pvt.add('The Employee that is processing this Transaction is: '||l_employee_id);
977: csi_t_gen_utility_pvt.add('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
978: END IF;
979: END IF;
980: IF (l_debug > 0) THEN
981: csi_t_gen_utility_pvt.add('The Employee that is processing this Transaction is: '||l_employee_id);
982: END IF;
983:
984: -- See if this is a depreciable Item to set the status of the transaction record
985: csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
986: l_depreciable,
987: l_mtl_item_tbl(i).organization_id); --Added for Bug 13988660
988:
989: IF (l_debug > 0) THEN
990: csi_t_gen_utility_pvt.add('Is this Item ID: '||l_mtl_item_tbl(i).inventory_item_id||', Depreciable :'||l_depreciable);
991: END IF;
992:
993: -- Set the quantity
994: IF l_mtl_item_tbl(i).serial_number IS NULL THEN
1020: END IF;
1021:
1022: IF NOT l_return_status = l_fnd_success THEN
1023: IF (l_debug > 0) THEN
1024: csi_t_gen_utility_pvt.add('Redeploy Flag: '||l_redeploy_flag);
1025: csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.get_redeploy_flag: '||l_error_message);
1026: END IF;
1027: RAISE fnd_api.g_exc_error;
1028: END IF;
1021:
1022: IF NOT l_return_status = l_fnd_success THEN
1023: IF (l_debug > 0) THEN
1024: csi_t_gen_utility_pvt.add('Redeploy Flag: '||l_redeploy_flag);
1025: csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.get_redeploy_flag: '||l_error_message);
1026: END IF;
1027: RAISE fnd_api.g_exc_error;
1028: END IF;
1029:
1027: RAISE fnd_api.g_exc_error;
1028: END IF;
1029:
1030: IF (l_debug > 0) THEN
1031: csi_t_gen_utility_pvt.add('Redeploy Flag: '||l_redeploy_flag);
1032: csi_t_gen_utility_pvt.add('Trans Status Code: '||l_txn_rec.transaction_status_code);
1033: END IF;
1034:
1035: -- Get Default Status ID
1028: END IF;
1029:
1030: IF (l_debug > 0) THEN
1031: csi_t_gen_utility_pvt.add('Redeploy Flag: '||l_redeploy_flag);
1032: csi_t_gen_utility_pvt.add('Trans Status Code: '||l_txn_rec.transaction_status_code);
1033: END IF;
1034:
1035: -- Get Default Status ID
1036: OPEN c_id;
1053: l_error_message,
1054: l_return_status);
1055:
1056: IF (l_debug > 0) THEN
1057: csi_t_gen_utility_pvt.add('CSI Transaction Created: '||l_txn_rec.transaction_id);
1058: END IF;
1059:
1060: IF NOT l_return_status = l_fnd_success THEN
1061: IF (l_debug > 0) THEN
1058: END IF;
1059:
1060: IF NOT l_return_status = l_fnd_success THEN
1061: IF (l_debug > 0) THEN
1062: csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.create_csi_txn: '||p_transaction_id);
1063: END IF;
1064: RAISE fnd_api.g_exc_error;
1065: END IF;
1066:
1067: -- Now loop through the PL/SQL Table.
1068: j := 1;
1069:
1070: IF (l_debug > 0) THEN
1071: csi_t_gen_utility_pvt.add('Starting to loop through Material Transaction Records');
1072: END IF;
1073:
1074: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
1075:
1073:
1074: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
1075:
1076: IF (l_debug > 0) THEN
1077: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
1078: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
1079: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
1080: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
1081: END IF;
1074: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
1075:
1076: IF (l_debug > 0) THEN
1077: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
1078: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
1079: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
1080: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
1081: END IF;
1082:
1075:
1076: IF (l_debug > 0) THEN
1077: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
1078: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
1079: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
1080: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
1081: END IF;
1082:
1083: IF l_mtl_item_tbl(j).serial_number IS NOT NULL THEN -- Serialized
1076: IF (l_debug > 0) THEN
1077: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
1078: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
1079: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
1080: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
1081: END IF;
1082:
1083: IF l_mtl_item_tbl(j).serial_number IS NOT NULL THEN -- Serialized
1084:
1089: l_instance_query_rec.serial_number := l_mtl_item_tbl(j).serial_number;
1090: --l_instance_query_rec.instance_usage_code := l_in_process;
1091:
1092: IF (l_debug > 0) THEN
1093: csi_t_gen_utility_pvt.add('Before Get Item Instance');
1094: END IF;
1095:
1096: csi_item_instance_pub.get_item_instances(l_api_version,
1097: l_commit,
1108: l_msg_count,
1109: l_msg_data);
1110:
1111: IF (l_debug > 0) THEN
1112: csi_t_gen_utility_pvt.add('After Update Item Instance');
1113: END IF;
1114: l_tbl_count := 0;
1115: l_tbl_count := l_src_instance_header_tbl.count;
1116: IF (l_debug > 0) THEN
1113: END IF;
1114: l_tbl_count := 0;
1115: l_tbl_count := l_src_instance_header_tbl.count;
1116: IF (l_debug > 0) THEN
1117: csi_t_gen_utility_pvt.add('Source Records Found: '||l_tbl_count);
1118: END IF;
1119:
1120: -- Check for any errors and add them to the message stack to pass out to be put into the
1121: -- error log table.
1120: -- Check for any errors and add them to the message stack to pass out to be put into the
1121: -- error log table.
1122: IF NOT l_return_status = l_fnd_success then
1123: IF (l_debug > 0) THEN
1124: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
1125: END IF;
1126: l_msg_index := 1;
1127: WHILE l_msg_count > 0 loop
1128: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1135: --Added code for bug 13801081
1136: IF l_src_instance_header_tbl.count > 0 THEN -- Bug 14631198
1137: IF l_src_instance_header_tbl(i).instance_usage_code = l_in_relationship THEN
1138: IF (l_debug > 0) THEN
1139: csi_t_gen_utility_pvt.add('Check and Break Relationship for Instance :'||l_src_instance_header_tbl(i).instance_id);
1140: END IF;
1141: csi_process_txn_pvt.check_and_break_relation(l_src_instance_header_tbl(i).instance_id,
1142: l_txn_rec,
1143: l_return_status);
1143: l_return_status);
1144:
1145: IF NOT l_return_status = l_fnd_success then
1146: IF (l_debug > 0) THEN
1147: csi_t_gen_utility_pvt.add('You encountered an error in the se_inv_trxs_pkg.check_and_break_relation');
1148: END IF;
1149: l_error_message := csi_t_gen_utility_pvt.dump_error_stack;
1150: RAISE fnd_api.g_exc_error;
1151: END IF;
1145: IF NOT l_return_status = l_fnd_success then
1146: IF (l_debug > 0) THEN
1147: csi_t_gen_utility_pvt.add('You encountered an error in the se_inv_trxs_pkg.check_and_break_relation');
1148: END IF;
1149: l_error_message := csi_t_gen_utility_pvt.dump_error_stack;
1150: RAISE fnd_api.g_exc_error;
1151: END IF;
1152: IF (l_debug > 0) THEN
1153: csi_t_gen_utility_pvt.add('Object Version originally from instance: '||l_src_instance_header_tbl(i).object_version_number);
1149: l_error_message := csi_t_gen_utility_pvt.dump_error_stack;
1150: RAISE fnd_api.g_exc_error;
1151: END IF;
1152: IF (l_debug > 0) THEN
1153: csi_t_gen_utility_pvt.add('Object Version originally from instance: '||l_src_instance_header_tbl(i).object_version_number);
1154: END IF;
1155:
1156: SELECT object_version_number
1157: into l_src_instance_header_tbl(i).object_version_number
1158: FROM csi_item_instances
1159: WHERE instance_id = l_src_instance_header_tbl(i).instance_id;
1160:
1161: IF (l_debug > 0) THEN
1162: csi_t_gen_utility_pvt.add('Current Object Version after check and break :'||l_src_instance_header_tbl(i).object_version_number);
1163: END IF;
1164: END IF;
1165: END IF; -- IF l_src_instance_header_tbl.count > 0 THEN -- Bug 14631198
1166: --End of code for bug 13801081
1175: l_in_relationship, -- Added for bug 13801081
1176: l_in_process) THEN
1177:
1178: IF (l_debug > 0) THEN
1179: csi_t_gen_utility_pvt.add('Source records found so decide which one to update');
1180: END IF;
1181:
1182: l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1183: l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
1205: l_org_assignments_tbl.delete;
1206: l_asset_assignment_tbl.delete;
1207:
1208: IF (l_debug > 0) THEN
1209: csi_t_gen_utility_pvt.add('Before Update Item Instance');
1210: END IF;
1211:
1212: csi_item_instance_pub.update_item_instance(l_api_version,
1213: l_commit,
1229: l_upd_error_instance_id := NULL;
1230: l_upd_error_instance_id := l_update_instance_rec.instance_id;
1231:
1232: IF (l_debug > 0) THEN
1233: csi_t_gen_utility_pvt.add('After Update Item Instance');
1234: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1235: END IF;
1236:
1237: -- Check for any errors and add them to the message stack to pass out to be put into the
1230: l_upd_error_instance_id := l_update_instance_rec.instance_id;
1231:
1232: IF (l_debug > 0) THEN
1233: csi_t_gen_utility_pvt.add('After Update Item Instance');
1234: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1235: END IF;
1236:
1237: -- Check for any errors and add them to the message stack to pass out to be put into the
1238: -- error log table.
1237: -- Check for any errors and add them to the message stack to pass out to be put into the
1238: -- error log table.
1239: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1240: IF (l_debug > 0) THEN
1241: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
1242: END IF;
1243: l_msg_index := 1;
1244: WHILE l_msg_count > 0 loop
1245: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1250: END IF;
1251: ELSE
1252: l_status := 'In Inventory, Out of Service, Installed, In Service or In Process';
1253: IF (l_debug > 0) THEN
1254: csi_t_gen_utility_pvt.add('Serialized Item with Status other then Out Of Service, In Inventory, Installed, or In Process already exists in Install Base');
1255: csi_t_gen_utility_pvt.add('Instance Usage Code is: '||l_src_instance_header_tbl(i).instance_usage_code);
1256: END IF;
1257: fnd_message.set_name('CSI','CSI_SERIALIZED_ITEM_EXISTS');
1258: fnd_message.set_token('STATUS',l_status);
1251: ELSE
1252: l_status := 'In Inventory, Out of Service, Installed, In Service or In Process';
1253: IF (l_debug > 0) THEN
1254: csi_t_gen_utility_pvt.add('Serialized Item with Status other then Out Of Service, In Inventory, Installed, or In Process already exists in Install Base');
1255: csi_t_gen_utility_pvt.add('Instance Usage Code is: '||l_src_instance_header_tbl(i).instance_usage_code);
1256: END IF;
1257: fnd_message.set_name('CSI','CSI_SERIALIZED_ITEM_EXISTS');
1258: fnd_message.set_token('STATUS',l_status);
1259: l_error_message := fnd_message.get;
1262: END IF;
1263:
1264: ELSIF l_src_instance_header_tbl.count = 0 THEN
1265:
1266: csi_t_gen_utility_pvt.add('No Serialized Instances are found so we need to create one that we would have received from the project and task');
1267:
1268: l_new_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
1269: l_new_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1270: l_new_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1295: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
1296:
1297: l_new_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
1298:
1299: csi_t_gen_utility_pvt.add('Instance_status_id Value: '||nvl(l_new_instance_rec.instance_status_id,-1));
1300: csi_t_gen_utility_pvt.add('You will now Create a new Item Instance Record');
1301: csi_t_gen_utility_pvt.add('Serial Number: '||l_new_instance_rec.serial_number);
1302:
1303: csi_item_instance_pub.create_item_instance(l_api_version,
1296:
1297: l_new_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
1298:
1299: csi_t_gen_utility_pvt.add('Instance_status_id Value: '||nvl(l_new_instance_rec.instance_status_id,-1));
1300: csi_t_gen_utility_pvt.add('You will now Create a new Item Instance Record');
1301: csi_t_gen_utility_pvt.add('Serial Number: '||l_new_instance_rec.serial_number);
1302:
1303: csi_item_instance_pub.create_item_instance(l_api_version,
1304: l_commit,
1297: l_new_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
1298:
1299: csi_t_gen_utility_pvt.add('Instance_status_id Value: '||nvl(l_new_instance_rec.instance_status_id,-1));
1300: csi_t_gen_utility_pvt.add('You will now Create a new Item Instance Record');
1301: csi_t_gen_utility_pvt.add('Serial Number: '||l_new_instance_rec.serial_number);
1302:
1303: csi_item_instance_pub.create_item_instance(l_api_version,
1304: l_commit,
1305: l_init_msg_list,
1318:
1319: -- Check for any errors and add them to the message stack to pass out to be put into the
1320: -- error log table.
1321: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1322: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
1323: l_msg_index := 1;
1324: WHILE l_msg_count > 0 loop
1325: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1326: l_msg_index := l_msg_index + 1;
1328: END LOOP;
1329: RAISE fnd_api.g_exc_error;
1330: END IF;
1331:
1332: csi_t_gen_utility_pvt.add('Item Instance Created: '||l_new_instance_rec.instance_id);
1333:
1334: -- Bug 4378656 - If the instance is not there then we still need to create one that is In Inventory.
1335: -- IF (l_debug > 0) THEN
1336: -- csi_t_gen_utility_pvt.add('No Records were found in Install Base');
1332: csi_t_gen_utility_pvt.add('Item Instance Created: '||l_new_instance_rec.instance_id);
1333:
1334: -- Bug 4378656 - If the instance is not there then we still need to create one that is In Inventory.
1335: -- IF (l_debug > 0) THEN
1336: -- csi_t_gen_utility_pvt.add('No Records were found in Install Base');
1337: -- END IF;
1338: -- fnd_message.set_name('CSI','CSI_IB_RECORD_NOTFOUND');
1339: -- fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
1340: -- fnd_message.set_token('SUBINVENTORY',l_mtl_item_tbl(j).subinventory_code);
1344:
1345: ELSIF l_src_instance_header_tbl.count > 1 THEN
1346: -- Multiple Instances were found so throw error
1347: IF (l_debug > 0) THEN
1348: csi_t_gen_utility_pvt.add('Multiple Instances were Found in InstallBase-55');
1349: END IF;
1350: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
1351: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
1352: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
1366: l_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1367: l_instance_query_rec.instance_usage_code := l_in_process;
1368:
1369: IF (l_debug > 0) THEN
1370: csi_t_gen_utility_pvt.add('Before Get Item Instance');
1371: END IF;
1372:
1373: csi_item_instance_pub.get_item_instances(l_api_version,
1374: l_commit,
1385: l_msg_count,
1386: l_msg_data);
1387:
1388: IF (l_debug > 0) THEN
1389: csi_t_gen_utility_pvt.add('After Get Item Instance');
1390: END IF;
1391: l_tbl_count := 0;
1392: l_tbl_count := l_src_instance_header_tbl.count;
1393: IF (l_debug > 0) THEN
1390: END IF;
1391: l_tbl_count := 0;
1392: l_tbl_count := l_src_instance_header_tbl.count;
1393: IF (l_debug > 0) THEN
1394: csi_t_gen_utility_pvt.add('Source Records Found: '||l_tbl_count);
1395: END IF;
1396:
1397: -- Check for any errors and add them to the message stack to pass out to be put into the
1398: -- error log table.
1397: -- Check for any errors and add them to the message stack to pass out to be put into the
1398: -- error log table.
1399: IF NOT l_return_status = l_fnd_success then
1400: IF (l_debug > 0) THEN
1401: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
1402: END IF;
1403: l_msg_index := 1;
1404: WHILE l_msg_count > 0 loop
1405: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1423: l_org_assignments_tbl.delete;
1424: l_asset_assignment_tbl.delete;
1425:
1426: IF (l_debug > 0) THEN
1427: csi_t_gen_utility_pvt.add('Before Update Item Instance');
1428: END IF;
1429:
1430: csi_item_instance_pub.update_item_instance(l_api_version,
1431: l_commit,
1447: l_upd_error_instance_id := NULL;
1448: l_upd_error_instance_id := l_upd_src_dest_instance_rec.instance_id;
1449:
1450: IF (l_debug > 0) THEN
1451: csi_t_gen_utility_pvt.add('After Update Item Instance');
1452: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1453: END IF;
1454:
1455: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1448: l_upd_error_instance_id := l_upd_src_dest_instance_rec.instance_id;
1449:
1450: IF (l_debug > 0) THEN
1451: csi_t_gen_utility_pvt.add('After Update Item Instance');
1452: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1453: END IF;
1454:
1455: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1456: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1454:
1455: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1456: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1457: IF (l_debug > 0) THEN
1458: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
1459: END IF;
1460: l_msg_index := 1;
1461: WHILE l_msg_count > 0 loop
1462: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1477: l_instance_dest_query_rec.serial_number := NULL;
1478: l_instance_dest_query_rec.instance_usage_code := l_in_inventory;
1479:
1480: IF (l_debug > 0) THEN
1481: csi_t_gen_utility_pvt.add('Before Get Item Instance');
1482: END IF;
1483:
1484: csi_item_instance_pub.get_item_instances(l_api_version,
1485: l_commit,
1496: l_msg_count,
1497: l_msg_data);
1498:
1499: IF (l_debug > 0) THEN
1500: csi_t_gen_utility_pvt.add('After Get Item Instance');
1501: END IF;
1502: l_tbl_count := 0;
1503: l_tbl_count := l_dest_instance_header_tbl.count;
1504: IF (l_debug > 0) THEN
1501: END IF;
1502: l_tbl_count := 0;
1503: l_tbl_count := l_dest_instance_header_tbl.count;
1504: IF (l_debug > 0) THEN
1505: csi_t_gen_utility_pvt.add('Destination Records Found: '||l_tbl_count);
1506: END IF;
1507:
1508: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1509: IF NOT l_return_status = l_fnd_success then
1507:
1508: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1509: IF NOT l_return_status = l_fnd_success then
1510: IF (l_debug > 0) THEN
1511: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
1512: END IF;
1513: l_msg_index := 1;
1514: WHILE l_msg_count > 0 loop
1515: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1553:
1554: l_new_dest_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
1555:
1556: IF (l_debug > 0) THEN
1557: csi_t_gen_utility_pvt.add('Before Create Item Instance');
1558: END IF;
1559:
1560: csi_item_instance_pub.create_item_instance(l_api_version,
1561: l_commit,
1573: l_msg_count,
1574: l_msg_data);
1575:
1576: IF (l_debug > 0) THEN
1577: csi_t_gen_utility_pvt.add('After Update Item Instance');
1578: csi_t_gen_utility_pvt.add('Item Instance Created: '||l_new_dest_instance_rec.instance_id);
1579: END IF;
1580:
1581: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1574: l_msg_data);
1575:
1576: IF (l_debug > 0) THEN
1577: csi_t_gen_utility_pvt.add('After Update Item Instance');
1578: csi_t_gen_utility_pvt.add('Item Instance Created: '||l_new_dest_instance_rec.instance_id);
1579: END IF;
1580:
1581: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1582: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1580:
1581: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1582: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1583: IF (l_debug > 0) THEN
1584: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
1585: END IF;
1586: l_msg_index := 1;
1587: WHILE l_msg_count > 0 loop
1588: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1608:
1609: l_update_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
1610:
1611: IF (l_debug > 0) THEN
1612: csi_t_gen_utility_pvt.add('Before Update Item Instance');
1613: END IF;
1614:
1615: csi_item_instance_pub.update_item_instance(l_api_version,
1616: l_commit,
1632: l_upd_error_instance_id := NULL;
1633: l_upd_error_instance_id := l_update_instance_rec.instance_id;
1634:
1635: IF (l_debug > 0) THEN
1636: csi_t_gen_utility_pvt.add('After Update Item Instance');
1637: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1638: END IF;
1639:
1640: -- Check for any errors and add them to the message stack to pass out to be put into the
1633: l_upd_error_instance_id := l_update_instance_rec.instance_id;
1634:
1635: IF (l_debug > 0) THEN
1636: csi_t_gen_utility_pvt.add('After Update Item Instance');
1637: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1638: END IF;
1639:
1640: -- Check for any errors and add them to the message stack to pass out to be put into the
1641: -- error log table.
1640: -- Check for any errors and add them to the message stack to pass out to be put into the
1641: -- error log table.
1642: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1643: IF (l_debug > 0) THEN
1644: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
1645: END IF;
1646: l_msg_index := 1;
1647: WHILE l_msg_count > 0 loop
1648: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1654:
1655: ELSIF l_dest_instance_header_tbl.count > 1 THEN
1656: -- Multiple Instances were found so throw error
1657: IF (l_debug > 0) THEN
1658: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-50');
1659: END IF;
1660: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
1661: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
1662: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
1667: END IF; -- End of Destination Record If
1668:
1669: ELSIF l_src_instance_header_tbl.count = 0 THEN
1670:
1671: csi_t_gen_utility_pvt.add('No Source Records in a Project and Task Exist. Query for the inventory record in the Org to see if it exists. If it does then add to that instance otherwise create a new instance that is located in Inventory');
1672:
1673: -- No Project and Task Records exist so check if there are any in Inventory. If not we will create otherwise update
1674: l_instance_dest_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
1675: l_instance_dest_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1681: l_instance_dest_query_rec.serial_number := NULL;
1682: l_instance_dest_query_rec.instance_usage_code := l_in_inventory;
1683:
1684: IF (l_debug > 0) THEN
1685: csi_t_gen_utility_pvt.add('Before Get Item Instance');
1686: END IF;
1687:
1688: csi_item_instance_pub.get_item_instances(l_api_version,
1689: l_commit,
1700: l_msg_count,
1701: l_msg_data);
1702:
1703: IF (l_debug > 0) THEN
1704: csi_t_gen_utility_pvt.add('After Get Item Instance');
1705: END IF;
1706: l_tbl_count := 0;
1707: l_tbl_count := l_dest_instance_header_tbl.count;
1708: IF (l_debug > 0) THEN
1705: END IF;
1706: l_tbl_count := 0;
1707: l_tbl_count := l_dest_instance_header_tbl.count;
1708: IF (l_debug > 0) THEN
1709: csi_t_gen_utility_pvt.add('Destination Records Found: '||l_tbl_count);
1710: END IF;
1711:
1712: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1713: IF NOT l_return_status = l_fnd_success then
1711:
1712: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1713: IF NOT l_return_status = l_fnd_success then
1714: IF (l_debug > 0) THEN
1715: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
1716: END IF;
1717: l_msg_index := 1;
1718: WHILE l_msg_count > 0 loop
1719: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1724: END IF;
1725:
1726: IF l_dest_instance_header_tbl.count = 0 THEN
1727:
1728: csi_t_gen_utility_pvt.add('No Records existed so we will create a new one in Inventory that is Non Serialized');
1729:
1730: l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
1731: l_new_dest_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1732: l_new_dest_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1759:
1760: l_new_dest_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
1761:
1762: IF (l_debug > 0) THEN
1763: csi_t_gen_utility_pvt.add('Before Create Item Instance');
1764: END IF;
1765:
1766: csi_item_instance_pub.create_item_instance(l_api_version,
1767: l_commit,
1779: l_msg_count,
1780: l_msg_data);
1781:
1782: IF (l_debug > 0) THEN
1783: csi_t_gen_utility_pvt.add('After Update Item Instance');
1784: csi_t_gen_utility_pvt.add('Item Instance Created: '||l_new_dest_instance_rec.instance_id);
1785: END IF;
1786:
1787: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1780: l_msg_data);
1781:
1782: IF (l_debug > 0) THEN
1783: csi_t_gen_utility_pvt.add('After Update Item Instance');
1784: csi_t_gen_utility_pvt.add('Item Instance Created: '||l_new_dest_instance_rec.instance_id);
1785: END IF;
1786:
1787: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1788: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1786:
1787: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1788: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1789: IF (l_debug > 0) THEN
1790: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
1791: END IF;
1792: l_msg_index := 1;
1793: WHILE l_msg_count > 0 loop
1794: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1799: END IF;
1800:
1801: ELSIF l_dest_instance_header_tbl.count = 1 THEN
1802:
1803: csi_t_gen_utility_pvt.add('Records existed so we will update the existing Instance: '||l_dest_instance_header_tbl(i).instance_id);
1804:
1805: l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1806: l_update_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
1807: l_update_instance_rec.quantity := l_dest_instance_header_tbl(i).quantity + abs(l_mtl_item_tbl(j).primary_quantity);
1816:
1817: l_update_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
1818:
1819: IF (l_debug > 0) THEN
1820: csi_t_gen_utility_pvt.add('Before Update Item Instance');
1821: END IF;
1822:
1823: csi_item_instance_pub.update_item_instance(l_api_version,
1824: l_commit,
1840: l_upd_error_instance_id := NULL;
1841: l_upd_error_instance_id := l_update_instance_rec.instance_id;
1842:
1843: IF (l_debug > 0) THEN
1844: csi_t_gen_utility_pvt.add('After Update Item Instance');
1845: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1846: END IF;
1847:
1848: -- Check for any errors and add them to the message stack to pass out to be put into the
1841: l_upd_error_instance_id := l_update_instance_rec.instance_id;
1842:
1843: IF (l_debug > 0) THEN
1844: csi_t_gen_utility_pvt.add('After Update Item Instance');
1845: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1846: END IF;
1847:
1848: -- Check for any errors and add them to the message stack to pass out to be put into the
1849: -- error log table.
1848: -- Check for any errors and add them to the message stack to pass out to be put into the
1849: -- error log table.
1850: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1851: IF (l_debug > 0) THEN
1852: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
1853: END IF;
1854: l_msg_index := 1;
1855: WHILE l_msg_count > 0 loop
1856: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1862:
1863: ELSIF l_dest_instance_header_tbl.count > 1 THEN
1864: -- Multiple Instances were found so throw error
1865: IF (l_debug > 0) THEN
1866: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base so error-90');
1867: END IF;
1868: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
1869: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
1870: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
1875: END IF; -- End of Destination Record If for checking for In Inventory Records because the Project/Source Instance was not there.
1876:
1877: -- Bug 4378656 - If the instance is not there then we still need to create one that is In Inventory.
1878: -- IF (l_debug > 0) THEN
1879: -- csi_t_gen_utility_pvt.add('No Records were found in Install Base');
1880: -- END IF;
1881: -- fnd_message.set_name('CSI','CSI_IB_RECORD_NOTFOUND');
1882: -- fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
1883: -- fnd_message.set_token('SUBINVENTORY',l_mtl_item_tbl(j).subinventory_code);
1887:
1888: ELSIF l_src_instance_header_tbl.count > 1 THEN
1889: -- Multiple Instances were found so throw error
1890: IF (l_debug > 0) THEN
1891: csi_t_gen_utility_pvt.add('Multiple Instances were Found in InstallBase-75');
1892: END IF;
1893: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
1894: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
1895: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
1901: END IF; -- End of Serial Number If
1902: END LOOP; -- End of For Loop
1903:
1904: IF (l_debug > 0) THEN
1905: csi_t_gen_utility_pvt.add('*****End of csi_inv_trxs_pkg.misc_receipt_projtask Transaction procedure*****');
1906: END IF;
1907:
1908: EXCEPTION
1909: WHEN fnd_api.g_exc_error THEN
1907:
1908: EXCEPTION
1909: WHEN fnd_api.g_exc_error THEN
1910: IF (l_debug > 0) THEN
1911: csi_t_gen_utility_pvt.add('You have encountered a "fnd_api.g_exc_error" exception');
1912: END IF;
1913: x_return_status := l_fnd_error;
1914:
1915: IF l_mtl_item_tbl.count > 0 THEN
1935: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
1936:
1937: WHEN others THEN
1938: IF (l_debug > 0) THEN
1939: csi_t_gen_utility_pvt.add('You have encountered a "others" exception');
1940: END IF;
1941: fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
1942: fnd_message.set_token('API_NAME',l_api_name);
1943: fnd_message.set_token('SQL_ERROR',SQLERRM);
2050:
2051: x_return_status := l_fnd_success;
2052:
2053: IF (l_debug > 0) THEN
2054: csi_t_gen_utility_pvt.add('*****Start of csi_inv_trxs_pkg.misc_issue_projtask Transaction procedure*****');
2055: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
2056: END IF;
2057:
2058: -- This procedure queries all of the Inventory Transaction Records and returns them
2051: x_return_status := l_fnd_success;
2052:
2053: IF (l_debug > 0) THEN
2054: csi_t_gen_utility_pvt.add('*****Start of csi_inv_trxs_pkg.misc_issue_projtask Transaction procedure*****');
2055: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
2056: END IF;
2057:
2058: -- This procedure queries all of the Inventory Transaction Records and returns them
2059: -- as a table.
2064:
2065: l_tbl_count := 0;
2066: l_tbl_count := l_mtl_item_tbl.count;
2067: IF (l_debug > 0) THEN
2068: csi_t_gen_utility_pvt.add('Inventory Records Found: '||l_tbl_count);
2069: END IF;
2070:
2071: IF NOT l_return_status = l_fnd_success THEN
2072: IF (l_debug > 0) THEN
2069: END IF;
2070:
2071: IF NOT l_return_status = l_fnd_success THEN
2072: IF (l_debug > 0) THEN
2073: csi_t_gen_utility_pvt.add('You have encountered an error in CSI_INV_TRXS_PKG.get_transaction_recs, Transaction ID: '||p_transaction_id);
2074: END IF;
2075: RAISE fnd_api.g_exc_error;
2076: END IF;
2077:
2082: l_error_message);
2083:
2084: IF NOT l_return_status = l_fnd_success THEN
2085: IF (l_debug > 0) THEN
2086: csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.get_master_organization, Organization ID: '||l_mtl_item_tbl(i).organization_id);
2087: END IF;
2088: RAISE fnd_api.g_exc_error;
2089: END IF;
2090:
2092: l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
2093:
2094: IF l_employee_id = -1 THEN
2095: IF (l_debug > 0) THEN
2096: csi_t_gen_utility_pvt.add('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
2097: END IF;
2098: END IF;
2099: IF (l_debug > 0) THEN
2100: csi_t_gen_utility_pvt.add('The Employee that is processing this Transaction is: '||l_employee_id);
2096: csi_t_gen_utility_pvt.add('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
2097: END IF;
2098: END IF;
2099: IF (l_debug > 0) THEN
2100: csi_t_gen_utility_pvt.add('The Employee that is processing this Transaction is: '||l_employee_id);
2101: END IF;
2102:
2103: -- See if this is a depreciable Item to set the status of the transaction record
2104: csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
2105: l_depreciable,
2106: l_mtl_item_tbl(i).organization_id); --Added for Bug 13988660
2107:
2108: IF (l_debug > 0) THEN
2109: csi_t_gen_utility_pvt.add('Is this Item ID: '||l_mtl_item_tbl(i).inventory_item_id||', Depreciable :'||l_depreciable);
2110: END IF;
2111:
2112:
2113: -- Set the mfg_serial_number_flag and quantity
2131: l_instance_status := FND_API.G_TRUE;
2132: END IF;
2133:
2134: IF (l_debug > 0) THEN
2135: csi_t_gen_utility_pvt.add('Negative Code is - 1 = Yes, 2 = No: '||l_neg_code);
2136: END IF;
2137:
2138: -- Initialize Transaction Record
2139: l_txn_rec := csi_inv_trxs_pkg.init_txn_rec;
2158: END IF;
2159:
2160: IF NOT l_return_status = l_fnd_success THEN
2161: IF (l_debug > 0) THEN
2162: csi_t_gen_utility_pvt.add('Redeploy Flag: '||l_redeploy_flag);
2163: csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.get_redeploy_flag: '||l_error_message);
2164: END IF;
2165: RAISE fnd_api.g_exc_error;
2166: END IF;
2159:
2160: IF NOT l_return_status = l_fnd_success THEN
2161: IF (l_debug > 0) THEN
2162: csi_t_gen_utility_pvt.add('Redeploy Flag: '||l_redeploy_flag);
2163: csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.get_redeploy_flag: '||l_error_message);
2164: END IF;
2165: RAISE fnd_api.g_exc_error;
2166: END IF;
2167:
2165: RAISE fnd_api.g_exc_error;
2166: END IF;
2167:
2168: IF (l_debug > 0) THEN
2169: csi_t_gen_utility_pvt.add('Redeploy Flag: '||l_redeploy_flag);
2170: csi_t_gen_utility_pvt.add('Trans Status Code: '||l_txn_rec.transaction_status_code);
2171: END IF;
2172:
2173: -- Get Default Status ID
2166: END IF;
2167:
2168: IF (l_debug > 0) THEN
2169: csi_t_gen_utility_pvt.add('Redeploy Flag: '||l_redeploy_flag);
2170: csi_t_gen_utility_pvt.add('Trans Status Code: '||l_txn_rec.transaction_status_code);
2171: END IF;
2172:
2173: -- Get Default Status ID
2174: OPEN c_id;
2191: l_error_message,
2192: l_return_status);
2193:
2194: IF (l_debug > 0) THEN
2195: csi_t_gen_utility_pvt.add('CSI Transaction Created: '||l_txn_rec.transaction_id);
2196: END IF;
2197:
2198: IF NOT l_return_status = l_fnd_success THEN
2199: IF (l_debug > 0) THEN
2196: END IF;
2197:
2198: IF NOT l_return_status = l_fnd_success THEN
2199: IF (l_debug > 0) THEN
2200: csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.create_csi_txn: '||p_transaction_id);
2201: END IF;
2202: RAISE fnd_api.g_exc_error;
2203: END IF;
2204:
2205: -- Now loop through the PL/SQL Table.
2206: j := 1;
2207:
2208: IF (l_debug > 0) THEN
2209: csi_t_gen_utility_pvt.add('Starting to loop through Material Transaction Records');
2210: END IF;
2211:
2212: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
2213:
2211:
2212: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
2213:
2214: IF (l_debug > 0) THEN
2215: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
2216: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
2217: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
2218: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
2219: END IF;
2212: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
2213:
2214: IF (l_debug > 0) THEN
2215: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
2216: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
2217: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
2218: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
2219: END IF;
2220:
2213:
2214: IF (l_debug > 0) THEN
2215: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
2216: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
2217: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
2218: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
2219: END IF;
2220:
2221: l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
2214: IF (l_debug > 0) THEN
2215: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
2216: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
2217: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
2218: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
2219: END IF;
2220:
2221: l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
2222: l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2228: l_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
2229: l_instance_query_rec.instance_usage_code := l_in_inventory;
2230:
2231: IF (l_debug > 0) THEN
2232: csi_t_gen_utility_pvt.add('Before Get Item Instance');
2233: END IF;
2234:
2235: csi_item_instance_pub.get_item_instances(l_api_version,
2236: l_commit,
2247: l_msg_count,
2248: l_msg_data);
2249:
2250: IF (l_debug > 0) THEN
2251: csi_t_gen_utility_pvt.add('After Get Item Instance');
2252: END IF;
2253: l_tbl_count := 0;
2254: l_tbl_count := l_src_instance_header_tbl.count;
2255: IF (l_debug > 0) THEN
2252: END IF;
2253: l_tbl_count := 0;
2254: l_tbl_count := l_src_instance_header_tbl.count;
2255: IF (l_debug > 0) THEN
2256: csi_t_gen_utility_pvt.add('Source Records Found: '||l_tbl_count);
2257: END IF;
2258:
2259: -- Check for any errors and add them to the message stack to pass out to be put into the
2260: -- error log table.
2259: -- Check for any errors and add them to the message stack to pass out to be put into the
2260: -- error log table.
2261: IF NOT l_return_status = l_fnd_success then
2262: IF (l_debug > 0) THEN
2263: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
2264: END IF;
2265: l_msg_index := 1;
2266: WHILE l_msg_count > 0 loop
2267: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2276: IF l_src_instance_header_tbl.count = 0 THEN
2277: IF l_neg_code = 1 THEN -- Allow Neg Qtys on NON Serial Items ONLY
2278:
2279: IF (l_debug > 0) THEN
2280: csi_t_gen_utility_pvt.add('No records were found so create a new Source Instance Record');
2281: END IF;
2282:
2283: l_new_src_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
2284: l_new_src_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2309: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2310: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2311:
2312: IF (l_debug > 0) THEN
2313: csi_t_gen_utility_pvt.add('Before Create Transaction - Neg Qty');
2314: END IF;
2315:
2316: csi_item_instance_pub.create_item_instance(l_api_version,
2317: l_commit,
2329: l_msg_count,
2330: l_msg_data);
2331:
2332: IF (l_debug > 0) THEN
2333: csi_t_gen_utility_pvt.add('After Create Transaction');
2334: csi_t_gen_utility_pvt.add('New instance created is: '||l_new_src_instance_rec.instance_id);
2335: END IF;
2336:
2337: -- Check for any errors and add them to the message stack to pass out to be put into the
2330: l_msg_data);
2331:
2332: IF (l_debug > 0) THEN
2333: csi_t_gen_utility_pvt.add('After Create Transaction');
2334: csi_t_gen_utility_pvt.add('New instance created is: '||l_new_src_instance_rec.instance_id);
2335: END IF;
2336:
2337: -- Check for any errors and add them to the message stack to pass out to be put into the
2338: -- error log table.
2337: -- Check for any errors and add them to the message stack to pass out to be put into the
2338: -- error log table.
2339: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2340: IF (l_debug > 0) THEN
2341: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
2342: END IF;
2343: l_msg_index := 1;
2344: WHILE l_msg_count > 0 loop
2345: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2350: END IF;
2351:
2352: ELSE -- No Records were found and Neg Qtys Not Allowed
2353: IF (l_debug > 0) THEN
2354: csi_t_gen_utility_pvt.add('No Records were found in Install Base andNeg Qtys not allowed to error');
2355: END IF;
2356: fnd_message.set_name('CSI','CSI_NO_NEG_BAL_ALLOWED');
2357: l_error_message := fnd_message.get;
2358: RAISE fnd_api.g_exc_error;
2363: -- Records found so make sure that is is updated to be unexp
2364: -- and subtract the quantity from source record
2365:
2366: IF (l_debug > 0) THEN
2367: csi_t_gen_utility_pvt.add('You will update instance: '||l_src_instance_header_tbl(i).instance_id);
2368: csi_t_gen_utility_pvt.add('End Date is: '||l_src_instance_header_tbl(i).active_end_date);
2369: END IF;
2370:
2371: l_update_source_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2364: -- and subtract the quantity from source record
2365:
2366: IF (l_debug > 0) THEN
2367: csi_t_gen_utility_pvt.add('You will update instance: '||l_src_instance_header_tbl(i).instance_id);
2368: csi_t_gen_utility_pvt.add('End Date is: '||l_src_instance_header_tbl(i).active_end_date);
2369: END IF;
2370:
2371: l_update_source_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2372: l_update_source_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
2379: l_org_assignments_tbl.delete;
2380: l_asset_assignment_tbl.delete;
2381:
2382: IF (l_debug > 0) THEN
2383: csi_t_gen_utility_pvt.add('Before Update Item Instance - Neg Qty');
2384: END IF;
2385:
2386: l_update_source_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
2387:
2385:
2386: l_update_source_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
2387:
2388: IF (l_debug > 0) THEN
2389: csi_t_gen_utility_pvt.add('Instance Status Id: '||l_update_source_instance_rec.instance_status_id);
2390: END IF;
2391:
2392: csi_item_instance_pub.update_item_instance(l_api_version,
2393: l_commit,
2409: l_upd_error_instance_id := NULL;
2410: l_upd_error_instance_id := l_update_source_instance_rec.instance_id;
2411:
2412: IF (l_debug > 0) THEN
2413: csi_t_gen_utility_pvt.add('After Update Item Instance - Neg Qty');
2414: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2415: END IF;
2416:
2417: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2410: l_upd_error_instance_id := l_update_source_instance_rec.instance_id;
2411:
2412: IF (l_debug > 0) THEN
2413: csi_t_gen_utility_pvt.add('After Update Item Instance - Neg Qty');
2414: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2415: END IF;
2416:
2417: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2418: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2416:
2417: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2418: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2419: IF (l_debug > 0) THEN
2420: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
2421: END IF;
2422: l_msg_index := 1;
2423: WHILE l_msg_count > 0 loop
2424: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2430:
2431: ELSIF l_src_instance_header_tbl.count > 1 THEN
2432: -- Multiple Instances were found so throw error
2433: IF (l_debug > 0) THEN
2434: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
2435: END IF;
2436: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
2437: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
2438: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
2452: l_instance_query_rec.serial_number := NULL;
2453: l_instance_query_rec.instance_usage_code := l_in_process;
2454:
2455: IF (l_debug > 0) THEN
2456: csi_t_gen_utility_pvt.add('Before Get Item Instance Dest - 100');
2457: END IF;
2458:
2459: csi_item_instance_pub.get_item_instances(l_api_version,
2460: l_commit,
2471: l_msg_count,
2472: l_msg_data);
2473:
2474: IF (l_debug > 0) THEN
2475: csi_t_gen_utility_pvt.add('After Get Item Instance Dest - Neg Qty');
2476: END IF;
2477: l_tbl_count := 0;
2478: l_tbl_count := l_dest_instance_header_tbl.count;
2479: IF (l_debug > 0) THEN
2476: END IF;
2477: l_tbl_count := 0;
2478: l_tbl_count := l_dest_instance_header_tbl.count;
2479: IF (l_debug > 0) THEN
2480: csi_t_gen_utility_pvt.add('Destination Records Found: '||l_tbl_count);
2481: END IF;
2482:
2483: -- Check for any errors and add them to the message stack to pass out to be put into the
2484: -- error log table.
2483: -- Check for any errors and add them to the message stack to pass out to be put into the
2484: -- error log table.
2485: IF NOT l_return_status = l_fnd_success then
2486: IF (l_debug > 0) THEN
2487: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
2488: END IF;
2489: l_msg_index := 1;
2490: WHILE l_msg_count > 0 loop
2491: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2496: END IF;
2497:
2498: IF l_dest_instance_header_tbl.count = 0 THEN -- Installed Base Destination Records are not found
2499: IF (l_debug > 0) THEN
2500: csi_t_gen_utility_pvt.add('No Destination Records were found so we will create a new destination Record using the source data');
2501: END IF;
2502:
2503: l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
2504: l_new_dest_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2530: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2531: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2532:
2533: IF (l_debug > 0) THEN
2534: csi_t_gen_utility_pvt.add('Before Create Item Instance - Neg Qty');
2535: END IF;
2536:
2537: csi_item_instance_pub.create_item_instance(l_api_version,
2538: l_commit,
2550: l_msg_count,
2551: l_msg_data);
2552:
2553: IF (l_debug > 0) THEN
2554: csi_t_gen_utility_pvt.add('After Create Item Instance - Neg Qty');
2555: csi_t_gen_utility_pvt.add('Item Instance Created: '||l_new_dest_instance_rec.instance_id);
2556: END IF;
2557:
2558: -- Check for any errors and add them to the message stack to pass out to be put into the
2551: l_msg_data);
2552:
2553: IF (l_debug > 0) THEN
2554: csi_t_gen_utility_pvt.add('After Create Item Instance - Neg Qty');
2555: csi_t_gen_utility_pvt.add('Item Instance Created: '||l_new_dest_instance_rec.instance_id);
2556: END IF;
2557:
2558: -- Check for any errors and add them to the message stack to pass out to be put into the
2559: -- error log table.
2558: -- Check for any errors and add them to the message stack to pass out to be put into the
2559: -- error log table.
2560: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2561: IF (l_debug > 0) THEN
2562: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
2563: END IF;
2564: l_msg_index := 1;
2565: WHILE l_msg_count > 0 loop
2566: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2573: ELSIF l_dest_instance_header_tbl.count = 1 THEN
2574: -- Installed Base Destination Records Found
2575:
2576: IF (l_debug > 0) THEN
2577: csi_t_gen_utility_pvt.add('You will update instance: '||l_dest_instance_header_tbl(i).instance_id);
2578: END IF;
2579:
2580: l_update_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2581: l_update_dest_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
2591:
2592: l_update_dest_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
2593:
2594: IF (l_debug > 0) THEN
2595: csi_t_gen_utility_pvt.add('Before Update Transaction - 101');
2596: END IF;
2597:
2598: csi_item_instance_pub.update_item_instance(l_api_version,
2599: l_commit,
2615: l_upd_error_instance_id := NULL;
2616: l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
2617:
2618: IF (l_debug > 0) THEN
2619: csi_t_gen_utility_pvt.add('After Update Transaction - 102');
2620: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2621: END IF;
2622:
2623: -- Check for any errors and add them to the message stack to pass out to be put into the
2616: l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
2617:
2618: IF (l_debug > 0) THEN
2619: csi_t_gen_utility_pvt.add('After Update Transaction - 102');
2620: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2621: END IF;
2622:
2623: -- Check for any errors and add them to the message stack to pass out to be put into the
2624: -- error log table.
2623: -- Check for any errors and add them to the message stack to pass out to be put into the
2624: -- error log table.
2625: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2626: IF (l_debug > 0) THEN
2627: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
2628: END IF;
2629: l_msg_index := 1;
2630: WHILE l_msg_count > 0 loop
2631: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2637:
2638: ELSIF l_dest_instance_header_tbl.count > 1 THEN
2639: -- Multiple Instances were found so throw error
2640: IF (l_debug > 0) THEN
2641: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-60');
2642: END IF;
2643: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
2644: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
2645: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
2673: l_org_assignments_tbl.delete;
2674: l_asset_assignment_tbl.delete;
2675:
2676: IF (l_debug > 0) THEN
2677: csi_t_gen_utility_pvt.add('Before Update Item Instance - 103');
2678: END IF;
2679:
2680: csi_item_instance_pub.update_item_instance(l_api_version,
2681: l_commit,
2697: l_upd_error_instance_id := NULL;
2698: l_upd_error_instance_id := l_update_instance_rec.instance_id;
2699:
2700: IF (l_debug > 0) THEN
2701: csi_t_gen_utility_pvt.add('After Update Item Instance - 104');
2702: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2703: END IF;
2704:
2705: -- Check for any errors and add them to the message stack to pass out to be put into the
2698: l_upd_error_instance_id := l_update_instance_rec.instance_id;
2699:
2700: IF (l_debug > 0) THEN
2701: csi_t_gen_utility_pvt.add('After Update Item Instance - 104');
2702: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2703: END IF;
2704:
2705: -- Check for any errors and add them to the message stack to pass out to be put into the
2706: -- error log table.
2705: -- Check for any errors and add them to the message stack to pass out to be put into the
2706: -- error log table.
2707: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2708: IF (l_debug > 0) THEN
2709: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
2710: END IF;
2711: l_msg_index := 1;
2712: WHILE l_msg_count > 0 loop
2713: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2718: END IF;
2719:
2720: ELSIF l_src_instance_header_tbl.count = 0 THEN
2721: IF (l_debug > 0) THEN
2722: csi_t_gen_utility_pvt.add('No Records were found in Install Base');
2723: END IF;
2724: fnd_message.set_name('CSI','CSI_IB_RECORD_NOTFOUND');
2725: fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
2726: fnd_message.set_token('SUBINVENTORY',l_mtl_item_tbl(j).subinventory_code);
2730:
2731: ELSIF l_src_instance_header_tbl.count > 1 THEN
2732: -- Multiple Instances were found so throw error
2733: IF (l_debug > 0) THEN
2734: csi_t_gen_utility_pvt.add('Multiple Instances were Found in InstallBase-65');
2735: END IF;
2736: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
2737: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
2738: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
2743: END IF; -- End of Serial Number If
2744: END LOOP; -- End of For Loop
2745:
2746: IF (l_debug > 0) THEN
2747: csi_t_gen_utility_pvt.add('*****End of csi_inv_trxs_pkg.misc_issue_projtask Transaction procedure*****');
2748: END IF;
2749:
2750: EXCEPTION
2751: WHEN fnd_api.g_exc_error THEN
2749:
2750: EXCEPTION
2751: WHEN fnd_api.g_exc_error THEN
2752: IF (l_debug > 0) THEN
2753: csi_t_gen_utility_pvt.add('You have encountered a "fnd_api.g_exc_error" exception');
2754: END IF;
2755: x_return_status := l_fnd_error;
2756:
2757: IF l_mtl_item_tbl.count > 0 THEN
2777: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
2778:
2779: WHEN others THEN
2780: IF (l_debug > 0) THEN
2781: csi_t_gen_utility_pvt.add('You have encountered a "others" exception');
2782: END IF;
2783: fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
2784: fnd_message.set_token('API_NAME',l_api_name);
2785: fnd_message.set_token('SQL_ERROR',SQLERRM);