[Home] [Help]
1: package body CSI_INV_HZ_PKG as
2: -- $Header: csiivtzb.pls 120.13.12020000.2 2012/07/04 10:28:08 sjawaji ship $
3:
4: l_debug NUMBER := csi_t_gen_utility_pvt.g_debug_level;
5:
6: PROCEDURE issue_to_hz_loc(p_transaction_id IN NUMBER,
7: p_message_id IN NUMBER,
8: x_return_status OUT NOCOPY VARCHAR2,
99:
100: x_return_status := l_fnd_success;
101:
102: IF (l_debug > 0) THEN
103: csi_t_gen_utility_pvt.add('*****Start of csi_inv_hz_pkg.issue_to_hz_loc Transaction procedure*****');
104: csi_t_gen_utility_pvt.add('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
105: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
106: csi_t_gen_utility_pvt.add('csiivtzb.pls 115.14');
107: END IF;
100: x_return_status := l_fnd_success;
101:
102: IF (l_debug > 0) THEN
103: csi_t_gen_utility_pvt.add('*****Start of csi_inv_hz_pkg.issue_to_hz_loc Transaction procedure*****');
104: csi_t_gen_utility_pvt.add('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
105: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
106: csi_t_gen_utility_pvt.add('csiivtzb.pls 115.14');
107: END IF;
108:
101:
102: IF (l_debug > 0) THEN
103: csi_t_gen_utility_pvt.add('*****Start of csi_inv_hz_pkg.issue_to_hz_loc Transaction procedure*****');
104: csi_t_gen_utility_pvt.add('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
105: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
106: csi_t_gen_utility_pvt.add('csiivtzb.pls 115.14');
107: END IF;
108:
109: -- This procedure queries all of the Inventory Transaction Records and
102: IF (l_debug > 0) THEN
103: csi_t_gen_utility_pvt.add('*****Start of csi_inv_hz_pkg.issue_to_hz_loc Transaction procedure*****');
104: csi_t_gen_utility_pvt.add('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
105: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
106: csi_t_gen_utility_pvt.add('csiivtzb.pls 115.14');
107: END IF;
108:
109: -- This procedure queries all of the Inventory Transaction Records and
110: -- returns them as a table.
116:
117: l_tbl_count := 0;
118: l_tbl_count := l_mtl_item_tbl.count;
119: IF (l_debug > 0) THEN
120: csi_t_gen_utility_pvt.add('Inventory Records Found: '||l_tbl_count);
121: END IF;
122:
123: IF NOT l_return_status = l_fnd_success THEN
124: IF (l_debug > 0) THEN
121: END IF;
122:
123: IF NOT l_return_status = l_fnd_success THEN
124: IF (l_debug > 0) THEN
125: csi_t_gen_utility_pvt.add('You have encountered an error in CSI_INV_TRXS_PKG.get_transaction_recs, Transaction ID: '||p_transaction_id);
126: END IF;
127: RAISE fnd_api.g_exc_error;
128: END IF;
129:
134: l_error_message);
135:
136: IF NOT l_return_status = l_fnd_success THEN
137: IF (l_debug > 0) THEN
138: 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);
139: END IF;
140: RAISE fnd_api.g_exc_error;
141: END IF;
142:
144: l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
145:
146: IF l_employee_id = -1 THEN
147: IF (l_debug > 0) THEN
148: 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');
149: END IF;
150: END IF;
151: IF (l_debug > 0) THEN
152: csi_t_gen_utility_pvt.add('The Employee that is processing this Transaction is: '||l_employee_id);
148: 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');
149: END IF;
150: END IF;
151: IF (l_debug > 0) THEN
152: csi_t_gen_utility_pvt.add('The Employee that is processing this Transaction is: '||l_employee_id);
153: END IF;
154:
155: -- See if this is a depreciable Item to set the status of the transaction record
156: csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
157: l_depreciable,
158: l_mtl_item_tbl(i).organization_id); --Added for Bug 13988660
159:
160: IF (l_debug > 0) THEN
161: csi_t_gen_utility_pvt.add('Is this Item ID: '||l_mtl_item_tbl(i).inventory_item_id||', Depreciable :'||l_depreciable);
162: END IF;
163:
164: -- Set the mfg_serial_number_flag and quantity
165: IF l_mtl_item_tbl(i).serial_number is NULL THEN
185: l_instance_status := FND_API.G_TRUE;
186: END IF;
187:
188: IF (l_debug > 0) THEN
189: csi_t_gen_utility_pvt.add('Negative Code is - 1 = Yes, 2 = No: '||l_neg_code);
190: END IF;
191:
192: IF (l_debug > 0) THEN
193: csi_t_gen_utility_pvt.add('Starting to loop through Material Transaction Records');
189: csi_t_gen_utility_pvt.add('Negative Code is - 1 = Yes, 2 = No: '||l_neg_code);
190: END IF;
191:
192: IF (l_debug > 0) THEN
193: csi_t_gen_utility_pvt.add('Starting to loop through Material Transaction Records');
194: END IF;
195:
196: -- Get Default Status ID
197: OPEN c_id;
216: END IF;
217: END IF;
218:
219: IF (l_debug > 0) THEN
220: csi_t_gen_utility_pvt.add('Location ID - Location Type; '||l_mtl_item_tbl(i).ship_to_location_id||'-'||l_location_type);
221: END IF;
222:
223: -- Create CSI Transaction to be used
224: l_txn_rec := csi_inv_trxs_pkg.init_txn_rec;
241: l_error_message,
242: l_return_status);
243:
244: IF (l_debug > 0) THEN
245: csi_t_gen_utility_pvt.add('CSI Transaction Created: '||l_txn_rec.transaction_id);
246: END IF;
247:
248: IF NOT l_return_status = l_fnd_success THEN
249: IF (l_debug > 0) THEN
246: END IF;
247:
248: IF NOT l_return_status = l_fnd_success THEN
249: IF (l_debug > 0) THEN
250: csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.create_csi_txn: '||p_transaction_id);
251: END IF;
252: RAISE fnd_api.g_exc_error;
253: END IF;
254:
254:
255: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
256:
257: IF (l_debug > 0) THEN
258: csi_t_gen_utility_pvt.add('Trans Status Code: '||l_txn_rec.transaction_status_code);
259: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
260: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
261: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
262: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
255: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
256:
257: IF (l_debug > 0) THEN
258: csi_t_gen_utility_pvt.add('Trans Status Code: '||l_txn_rec.transaction_status_code);
259: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
260: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
261: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
262: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
263: END IF;
256:
257: IF (l_debug > 0) THEN
258: csi_t_gen_utility_pvt.add('Trans Status Code: '||l_txn_rec.transaction_status_code);
259: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
260: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
261: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
262: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
263: END IF;
264:
257: IF (l_debug > 0) THEN
258: csi_t_gen_utility_pvt.add('Trans Status Code: '||l_txn_rec.transaction_status_code);
259: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
260: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
261: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
262: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
263: END IF;
264:
265: l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
258: csi_t_gen_utility_pvt.add('Trans Status Code: '||l_txn_rec.transaction_status_code);
259: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
260: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
261: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
262: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
263: END IF;
264:
265: l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
266: l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
273: --l_instance_query_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
274: l_instance_query_rec.instance_usage_code := l_in_inventory;
275:
276: IF (l_debug > 0) THEN
277: csi_t_gen_utility_pvt.add('Ship to Location ID: '||l_mtl_item_tbl(j).ship_to_location_id);
278: csi_t_gen_utility_pvt.add('Before Get Item Instance');
279: END IF;
280:
281: csi_item_instance_pub.get_item_instances(l_api_version,
274: l_instance_query_rec.instance_usage_code := l_in_inventory;
275:
276: IF (l_debug > 0) THEN
277: csi_t_gen_utility_pvt.add('Ship to Location ID: '||l_mtl_item_tbl(j).ship_to_location_id);
278: csi_t_gen_utility_pvt.add('Before Get Item Instance');
279: END IF;
280:
281: csi_item_instance_pub.get_item_instances(l_api_version,
282: l_commit,
293: l_msg_count,
294: l_msg_data);
295:
296: IF (l_debug > 0) THEN
297: csi_t_gen_utility_pvt.add('After Get Item Instance');
298: END IF;
299: l_tbl_count := 0;
300: l_tbl_count := l_src_instance_header_tbl.count;
301: IF (l_debug > 0) THEN
298: END IF;
299: l_tbl_count := 0;
300: l_tbl_count := l_src_instance_header_tbl.count;
301: IF (l_debug > 0) THEN
302: csi_t_gen_utility_pvt.add('Source Records Found: '||l_tbl_count);
303: END IF;
304: -- Check for any errors and add them to the message stack to pass out to be put into the
305: -- error log table.
306: IF NOT l_return_status = l_fnd_success then
304: -- Check for any errors and add them to the message stack to pass out to be put into the
305: -- error log table.
306: IF NOT l_return_status = l_fnd_success then
307: IF (l_debug > 0) THEN
308: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
309: END IF;
310: l_msg_index := 1;
311: WHILE l_msg_count > 0 loop
312: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
321: IF l_src_instance_header_tbl.count = 0 THEN
322: IF l_neg_code = 1 THEN -- Allow Neg Qtys on NON Serial Items ONLY
323:
324: IF (l_debug > 0) THEN
325: csi_t_gen_utility_pvt.add('No Source Records are found but negative qtys are allowed so create a negative qty source record');
326: END IF;
327:
328: l_new_src_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
329: l_new_src_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
354: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
355: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
356:
357: IF (l_debug > 0) THEN
358: csi_t_gen_utility_pvt.add('Before Create Source Item Instance');
359: END IF;
360:
361: csi_item_instance_pub.create_item_instance(l_api_version,
362: l_commit,
374: l_msg_count,
375: l_msg_data);
376:
377: IF (l_debug > 0) THEN
378: csi_t_gen_utility_pvt.add('After Create Source Item Instance');
379: END IF;
380:
381: IF (l_debug > 0) THEN
382: csi_t_gen_utility_pvt.add('After Create of Source Item Instance');
378: csi_t_gen_utility_pvt.add('After Create Source Item Instance');
379: END IF;
380:
381: IF (l_debug > 0) THEN
382: csi_t_gen_utility_pvt.add('After Create of Source Item Instance');
383: csi_t_gen_utility_pvt.add('New instance created is: '||l_new_src_instance_rec.instance_id);
384: END IF;
385:
386: -- Check for any errors and add them to the message stack to pass out to be put into the
379: END IF;
380:
381: IF (l_debug > 0) THEN
382: csi_t_gen_utility_pvt.add('After Create of Source Item Instance');
383: csi_t_gen_utility_pvt.add('New instance created is: '||l_new_src_instance_rec.instance_id);
384: END IF;
385:
386: -- Check for any errors and add them to the message stack to pass out to be put into the
387: -- error log table.
386: -- Check for any errors and add them to the message stack to pass out to be put into the
387: -- error log table.
388: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
389: IF (l_debug > 0) THEN
390: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
391: END IF;
392: l_msg_index := 1;
393: WHILE l_msg_count > 0 loop
394: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
399: END IF;
400:
401: ELSE -- No Records were found and Neg Qtys Not Allowed
402: IF (l_debug > 0) THEN
403: csi_t_gen_utility_pvt.add('No Records were found in Install Base and Neg Qtys not allowed to error');
404: END IF;
405: fnd_message.set_name('CSI','CSI_NO_NEG_BAL_ALLOWED');
406: l_error_message := fnd_message.get;
407: RAISE fnd_api.g_exc_error;
410:
411: ELSIF l_src_instance_header_tbl.count = 1 THEN
412:
413: IF (l_debug > 0) THEN
414: csi_t_gen_utility_pvt.add('You will update instance: '||l_src_instance_header_tbl(i).instance_id);
415: END IF;
416:
417: l_update_source_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
418: l_update_source_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
428: 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);
429:
430:
431: IF (l_debug > 0) THEN
432: csi_t_gen_utility_pvt.add('Before Update Item Instance');
433: END IF;
434:
435: csi_item_instance_pub.update_item_instance(l_api_version,
436: l_commit,
452: l_upd_error_instance_id := NULL;
453: l_upd_error_instance_id := l_update_source_instance_rec.instance_id;
454:
455: IF (l_debug > 0) THEN
456: csi_t_gen_utility_pvt.add('After Update Item Instance');
457: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
458: END IF;
459:
460: IF (l_debug > 0) THEN
453: l_upd_error_instance_id := l_update_source_instance_rec.instance_id;
454:
455: IF (l_debug > 0) THEN
456: csi_t_gen_utility_pvt.add('After Update Item Instance');
457: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
458: END IF;
459:
460: IF (l_debug > 0) THEN
461: csi_t_gen_utility_pvt.add('Instance Status Id: '||l_update_source_instance_rec.instance_status_id);
457: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
458: END IF;
459:
460: IF (l_debug > 0) THEN
461: csi_t_gen_utility_pvt.add('Instance Status Id: '||l_update_source_instance_rec.instance_status_id);
462: END IF;
463:
464: -- Check for any errors and add them to the message stack to pass out to be put into the
465: -- error log table.
464: -- Check for any errors and add them to the message stack to pass out to be put into the
465: -- error log table.
466: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
467: IF (l_debug > 0) THEN
468: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
469: END IF;
470: l_msg_index := 1;
471: WHILE l_msg_count > 0 loop
472: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
478:
479: ELSIF l_src_instance_header_tbl.count > 1 THEN
480: -- Multiple Instances were found so throw error
481: IF (l_debug > 0) THEN
482: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
483: END IF;
484: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
485: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
486: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
503: -- l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
504: l_instance_query_rec.instance_usage_code := l_in_service;
505:
506: IF (l_debug > 0) THEN
507: csi_t_gen_utility_pvt.add('Before Destination Get Item Instance - Neg Qty');
508: csi_t_gen_utility_pvt.add('Ship to Location ID: '||l_mtl_item_tbl(j).ship_to_location_id);
509: csi_t_gen_utility_pvt.add('Location Type Code: '||l_location_type);
510: END IF;
511:
504: l_instance_query_rec.instance_usage_code := l_in_service;
505:
506: IF (l_debug > 0) THEN
507: csi_t_gen_utility_pvt.add('Before Destination Get Item Instance - Neg Qty');
508: csi_t_gen_utility_pvt.add('Ship to Location ID: '||l_mtl_item_tbl(j).ship_to_location_id);
509: csi_t_gen_utility_pvt.add('Location Type Code: '||l_location_type);
510: END IF;
511:
512: csi_item_instance_pub.get_item_instances(l_api_version,
505:
506: IF (l_debug > 0) THEN
507: csi_t_gen_utility_pvt.add('Before Destination Get Item Instance - Neg Qty');
508: csi_t_gen_utility_pvt.add('Ship to Location ID: '||l_mtl_item_tbl(j).ship_to_location_id);
509: csi_t_gen_utility_pvt.add('Location Type Code: '||l_location_type);
510: END IF;
511:
512: csi_item_instance_pub.get_item_instances(l_api_version,
513: l_commit,
524: l_msg_count,
525: l_msg_data);
526:
527: IF (l_debug > 0) THEN
528: csi_t_gen_utility_pvt.add('After Destination Get Item Instance');
529: END IF;
530: l_tbl_count := 0;
531: l_tbl_count := l_dest_instance_header_tbl.count;
532: IF (l_debug > 0) THEN
529: END IF;
530: l_tbl_count := 0;
531: l_tbl_count := l_dest_instance_header_tbl.count;
532: IF (l_debug > 0) THEN
533: csi_t_gen_utility_pvt.add('Destination Records Found: '||l_tbl_count);
534: END IF;
535:
536: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
537: IF NOT l_return_status = l_fnd_success then
535:
536: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
537: IF NOT l_return_status = l_fnd_success then
538: IF (l_debug > 0) THEN
539: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
540: END IF;
541: l_msg_index := 1;
542: WHILE l_msg_count > 0 loop
543: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
548: END IF;
549:
550: IF l_dest_instance_header_tbl.count = 0 THEN -- Installed Base Destination Records are not found
551: IF (l_debug > 0) THEN
552: csi_t_gen_utility_pvt.add('No Destination Records were found so we will create a new destination Record using the source data');
553: END IF;
554:
555: l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
556: l_new_dest_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
583: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
584: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
585:
586: IF (l_debug > 0) THEN
587: csi_t_gen_utility_pvt.add('Before Create Item Instance - 45');
588: END IF;
589:
590: csi_item_instance_pub.create_item_instance(l_api_version,
591: l_commit,
603: l_msg_count,
604: l_msg_data);
605:
606: IF (l_debug > 0) THEN
607: csi_t_gen_utility_pvt.add('After Create Item Instance');
608: END IF;
609:
610: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
611: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
609:
610: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
611: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
612: IF (l_debug > 0) THEN
613: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
614: END IF;
615: l_msg_index := 1;
616: WHILE l_msg_count > 0 loop
617: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
623:
624: ELSIF l_dest_instance_header_tbl.count = 1 THEN-- Installed Base Destination Records Found
625:
626: IF (l_debug > 0) THEN
627: csi_t_gen_utility_pvt.add('You will update instance: '||l_dest_instance_header_tbl(i).instance_id);
628: END IF;
629:
630: l_update_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
631: l_update_dest_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
641:
642: 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);
643:
644: IF (l_debug > 0) THEN
645: csi_t_gen_utility_pvt.add('Before Update Item Instance - Neg Qty');
646: END IF;
647:
648: csi_item_instance_pub.update_item_instance(l_api_version,
649: l_commit,
665: l_upd_error_instance_id := NULL;
666: l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
667:
668: IF (l_debug > 0) THEN
669: csi_t_gen_utility_pvt.add('After Update Item Instance - Neg Qty');
670: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
671: END IF;
672:
673: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
666: l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
667:
668: IF (l_debug > 0) THEN
669: csi_t_gen_utility_pvt.add('After Update Item Instance - Neg Qty');
670: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
671: END IF;
672:
673: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
674: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
672:
673: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
674: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
675: IF (l_debug > 0) THEN
676: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
677: END IF;
678: l_msg_index := 1;
679: WHILE l_msg_count > 0 loop
680: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
686:
687: ELSIF l_dest_instance_header_tbl.count > 1 THEN
688: -- Multiple Instances were found so throw error
689: IF (l_debug > 0) THEN
690: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
691: END IF;
692: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
693: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
694: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
701: ELSIF l_mtl_item_tbl(j).serial_number is NOT NULL THEN
702: IF l_src_instance_header_tbl.count = 1 THEN -- Serialized Item
703:
704: IF (l_debug > 0) THEN
705: csi_t_gen_utility_pvt.add('You are updating a Serialized Item');
706: csi_t_gen_utility_pvt.add('Ship to Location ID Serialized: '||l_mtl_item_tbl(j).ship_to_location_id);
707: csi_t_gen_utility_pvt.add('The Transaction Status Code will be - Complete (C) or Incomplete (I): '||l_txn_rec.transaction_status_code);
708: END IF;
709:
702: IF l_src_instance_header_tbl.count = 1 THEN -- Serialized Item
703:
704: IF (l_debug > 0) THEN
705: csi_t_gen_utility_pvt.add('You are updating a Serialized Item');
706: csi_t_gen_utility_pvt.add('Ship to Location ID Serialized: '||l_mtl_item_tbl(j).ship_to_location_id);
707: csi_t_gen_utility_pvt.add('The Transaction Status Code will be - Complete (C) or Incomplete (I): '||l_txn_rec.transaction_status_code);
708: END IF;
709:
710: l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
703:
704: IF (l_debug > 0) THEN
705: csi_t_gen_utility_pvt.add('You are updating a Serialized Item');
706: csi_t_gen_utility_pvt.add('Ship to Location ID Serialized: '||l_mtl_item_tbl(j).ship_to_location_id);
707: csi_t_gen_utility_pvt.add('The Transaction Status Code will be - Complete (C) or Incomplete (I): '||l_txn_rec.transaction_status_code);
708: END IF;
709:
710: l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
711: l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
731: l_org_assignments_tbl.delete;
732: l_asset_assignment_tbl.delete;
733:
734: IF (l_debug > 0) THEN
735: csi_t_gen_utility_pvt.add('Before Update Item Instance');
736: END IF;
737:
738: csi_item_instance_pub.update_item_instance(l_api_version,
739: l_commit,
755: l_upd_error_instance_id := NULL;
756: l_upd_error_instance_id := l_update_instance_rec.instance_id;
757:
758: IF (l_debug > 0) THEN
759: csi_t_gen_utility_pvt.add('After Update Item Instance');
760: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
761: END IF;
762:
763: -- Check for any errors and add them to the message stack to pass out to be put into the
756: l_upd_error_instance_id := l_update_instance_rec.instance_id;
757:
758: IF (l_debug > 0) THEN
759: csi_t_gen_utility_pvt.add('After Update Item Instance');
760: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
761: END IF;
762:
763: -- Check for any errors and add them to the message stack to pass out to be put into the
764: -- error log table.
763: -- Check for any errors and add them to the message stack to pass out to be put into the
764: -- error log table.
765: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
766: IF (l_debug > 0) THEN
767: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
768: END IF;
769: l_msg_index := 1;
770: WHILE l_msg_count > 0 loop
771: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
776: END IF;
777:
778: ELSIF l_src_instance_header_tbl.count = 0 THEN -- Serialized Item
779: IF (l_debug > 0) THEN
780: csi_t_gen_utility_pvt.add('No Records were found in Install Base');
781: END IF;
782: fnd_message.set_name('CSI','CSI_IB_RECORD_NOTFOUND');
783: fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
784: fnd_message.set_token('SUBINVENTORY',l_mtl_item_tbl(j).subinventory_code);
789: ELSIF l_src_instance_header_tbl.count > 1 THEN -- Serialized Item
790:
791: -- Multiple Instances were found so throw error
792: IF (l_debug > 0) THEN
793: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-56');
794: END IF;
795: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
796: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
797: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
804: END IF; -- End of Serial Number count IF
805: END LOOP; -- End of For Loop
806:
807: IF (l_debug > 0) THEN
808: csi_t_gen_utility_pvt.add('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
809: csi_t_gen_utility_pvt.add('*****End of csi_inv_hz_pkg.issue_to_hz_loc Transaction procedure*****');
810: END IF;
811:
812: EXCEPTION
805: END LOOP; -- End of For Loop
806:
807: IF (l_debug > 0) THEN
808: csi_t_gen_utility_pvt.add('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
809: csi_t_gen_utility_pvt.add('*****End of csi_inv_hz_pkg.issue_to_hz_loc Transaction procedure*****');
810: END IF;
811:
812: EXCEPTION
813: WHEN fnd_api.g_exc_error THEN
811:
812: EXCEPTION
813: WHEN fnd_api.g_exc_error THEN
814: IF (l_debug > 0) THEN
815: csi_t_gen_utility_pvt.add('You have encountered a "fnd_api.g_exc_error" exception');
816: END IF;
817: x_return_status := l_fnd_error;
818:
819: IF l_mtl_item_tbl.count > 0 THEN
839: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
840:
841: WHEN others THEN
842: IF (l_debug > 0) THEN
843: csi_t_gen_utility_pvt.add('You have encountered a "others" exception');
844: END IF;
845: fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
846: fnd_message.set_token('API_NAME',l_api_name);
847: fnd_message.set_token('SQL_ERROR',SQLERRM);
974:
975: x_return_status := l_fnd_success;
976:
977: IF (l_debug > 0) THEN
978: csi_t_gen_utility_pvt.add('*****Start of csi_inv_hz_pkg.misc_receipt_hz_loc Transaction procedure*****');
979: csi_t_gen_utility_pvt.add('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
980: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
981: csi_t_gen_utility_pvt.add('csiivtzb.pls 115.14');
982: END IF;
975: x_return_status := l_fnd_success;
976:
977: IF (l_debug > 0) THEN
978: csi_t_gen_utility_pvt.add('*****Start of csi_inv_hz_pkg.misc_receipt_hz_loc Transaction procedure*****');
979: csi_t_gen_utility_pvt.add('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
980: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
981: csi_t_gen_utility_pvt.add('csiivtzb.pls 115.14');
982: END IF;
983:
976:
977: IF (l_debug > 0) THEN
978: csi_t_gen_utility_pvt.add('*****Start of csi_inv_hz_pkg.misc_receipt_hz_loc Transaction procedure*****');
979: csi_t_gen_utility_pvt.add('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
980: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
981: csi_t_gen_utility_pvt.add('csiivtzb.pls 115.14');
982: END IF;
983:
984: -- This procedure queries all of the Inventory Transaction Records
977: IF (l_debug > 0) THEN
978: csi_t_gen_utility_pvt.add('*****Start of csi_inv_hz_pkg.misc_receipt_hz_loc Transaction procedure*****');
979: csi_t_gen_utility_pvt.add('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
980: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
981: csi_t_gen_utility_pvt.add('csiivtzb.pls 115.14');
982: END IF;
983:
984: -- This procedure queries all of the Inventory Transaction Records
985: -- and returns them as a table.
991:
992: l_tbl_count := 0;
993: l_tbl_count := l_mtl_item_tbl.count;
994: IF (l_debug > 0) THEN
995: csi_t_gen_utility_pvt.add('Inventory Records Found: '||l_tbl_count);
996: END IF;
997:
998: IF NOT l_return_status = l_fnd_success THEN
999: IF (l_debug > 0) THEN
996: END IF;
997:
998: IF NOT l_return_status = l_fnd_success THEN
999: IF (l_debug > 0) THEN
1000: csi_t_gen_utility_pvt.add('You have encountered an error in CSI_INV_TRXS_PKG.get_transaction_recs, Transaction ID: '||p_transaction_id);
1001: END IF;
1002: RAISE fnd_api.g_exc_error;
1003: END IF;
1004:
1009: l_error_message);
1010:
1011: IF NOT l_return_status = l_fnd_success THEN
1012: IF (l_debug > 0) THEN
1013: 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);
1014: END IF;
1015: RAISE fnd_api.g_exc_error;
1016: END IF;
1017:
1019: l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
1020:
1021: IF l_employee_id = -1 THEN
1022: IF (l_debug > 0) THEN
1023: 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');
1024: END IF;
1025: END IF;
1026: IF (l_debug > 0) THEN
1027: csi_t_gen_utility_pvt.add('The Employee that is processing this Transaction is: '||l_employee_id);
1023: 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');
1024: END IF;
1025: END IF;
1026: IF (l_debug > 0) THEN
1027: csi_t_gen_utility_pvt.add('The Employee that is processing this Transaction is: '||l_employee_id);
1028: END IF;
1029:
1030: -- See if this is a depreciable Item to set the status of the transaction record
1031: csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
1032: l_depreciable,
1033: l_mtl_item_tbl(i).organization_id); --Added for Bug 13988660
1034:
1035: IF (l_debug > 0) THEN
1036: csi_t_gen_utility_pvt.add('Is this Item ID: '||l_mtl_item_tbl(i).inventory_item_id||', Depreciable :'||l_depreciable);
1037: END IF;
1038:
1039: -- Set the quantity
1040: IF l_mtl_item_tbl(i).serial_number IS NULL THEN
1069: --END IF;
1070:
1071: --IF NOT l_return_status = l_fnd_success THEN
1072: -- IF (l_debug > 0) THEN
1073: -- csi_t_gen_utility_pvt.add('Redeploy Flag: '||l_redeploy_flag);
1074: -- csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.get_redeploy_flag: '||l_error_message);
1075: -- END IF;
1076: -- RAISE fnd_api.g_exc_error;
1077: --END IF;
1070:
1071: --IF NOT l_return_status = l_fnd_success THEN
1072: -- IF (l_debug > 0) THEN
1073: -- csi_t_gen_utility_pvt.add('Redeploy Flag: '||l_redeploy_flag);
1074: -- csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.get_redeploy_flag: '||l_error_message);
1075: -- END IF;
1076: -- RAISE fnd_api.g_exc_error;
1077: --END IF;
1078:
1076: -- RAISE fnd_api.g_exc_error;
1077: --END IF;
1078:
1079: IF (l_debug > 0) THEN
1080: csi_t_gen_utility_pvt.add('Redeploy Flag: '||l_redeploy_flag);
1081: csi_t_gen_utility_pvt.add('Trans Status Code: '||l_txn_rec.transaction_status_code);
1082: END IF;
1083:
1084: -- Get Default Status ID
1077: --END IF;
1078:
1079: IF (l_debug > 0) THEN
1080: csi_t_gen_utility_pvt.add('Redeploy Flag: '||l_redeploy_flag);
1081: csi_t_gen_utility_pvt.add('Trans Status Code: '||l_txn_rec.transaction_status_code);
1082: END IF;
1083:
1084: -- Get Default Status ID
1085: OPEN c_id;
1104: END IF;
1105: END IF;
1106:
1107: IF (l_debug > 0) THEN
1108: csi_t_gen_utility_pvt.add('Location ID - Location Type; '||l_mtl_item_tbl(i).ship_to_location_id||'-'||l_location_type);
1109: END IF;
1110:
1111: -- Create CSI Transaction to be used
1112: l_txn_rec.source_transaction_date := l_mtl_item_tbl(i).transaction_date;
1125: l_error_message,
1126: l_return_status);
1127:
1128: IF (l_debug > 0) THEN
1129: csi_t_gen_utility_pvt.add('CSI Transaction Created: '||l_txn_rec.transaction_id);
1130: END IF;
1131:
1132: IF NOT l_return_status = l_fnd_success THEN
1133: IF (l_debug > 0) THEN
1130: END IF;
1131:
1132: IF NOT l_return_status = l_fnd_success THEN
1133: IF (l_debug > 0) THEN
1134: csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.create_csi_txn: '||p_transaction_id);
1135: END IF;
1136: RAISE fnd_api.g_exc_error;
1137: END IF;
1138:
1139: -- Now loop through the PL/SQL Table.
1140: j := 1;
1141:
1142: IF (l_debug > 0) THEN
1143: csi_t_gen_utility_pvt.add('Starting to loop through Material Transaction Records');
1144: END IF;
1145:
1146: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
1147:
1145:
1146: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
1147:
1148: IF (l_debug > 0) THEN
1149: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
1150: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
1151: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
1152: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
1153: END IF;
1146: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
1147:
1148: IF (l_debug > 0) THEN
1149: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
1150: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
1151: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
1152: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
1153: END IF;
1154:
1147:
1148: IF (l_debug > 0) THEN
1149: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
1150: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
1151: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
1152: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
1153: END IF;
1154:
1155: IF l_mtl_item_tbl(j).serial_number IS NOT NULL THEN -- Serialized
1148: IF (l_debug > 0) THEN
1149: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
1150: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
1151: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
1152: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
1153: END IF;
1154:
1155: IF l_mtl_item_tbl(j).serial_number IS NOT NULL THEN -- Serialized
1156:
1163: l_instance_query_rec.serial_number := l_mtl_item_tbl(j).serial_number;
1164: --l_instance_query_rec.instance_usage_code := l_out_of_service;
1165:
1166: IF (l_debug > 0) THEN
1167: csi_t_gen_utility_pvt.add('Before Get Item Instance');
1168: csi_t_gen_utility_pvt.add('Ship to Location ID: '||l_mtl_item_tbl(j).ship_to_location_id);
1169: END IF;
1170:
1171: csi_item_instance_pub.get_item_instances(l_api_version,
1164: --l_instance_query_rec.instance_usage_code := l_out_of_service;
1165:
1166: IF (l_debug > 0) THEN
1167: csi_t_gen_utility_pvt.add('Before Get Item Instance');
1168: csi_t_gen_utility_pvt.add('Ship to Location ID: '||l_mtl_item_tbl(j).ship_to_location_id);
1169: END IF;
1170:
1171: csi_item_instance_pub.get_item_instances(l_api_version,
1172: l_commit,
1183: l_msg_count,
1184: l_msg_data);
1185:
1186: IF (l_debug > 0) THEN
1187: csi_t_gen_utility_pvt.add('After Get Item Instance');
1188: END IF;
1189: l_tbl_count := 0;
1190: l_tbl_count := l_src_instance_header_tbl.count;
1191: IF (l_debug > 0) THEN
1188: END IF;
1189: l_tbl_count := 0;
1190: l_tbl_count := l_src_instance_header_tbl.count;
1191: IF (l_debug > 0) THEN
1192: csi_t_gen_utility_pvt.add('Source Records Found: '||l_tbl_count);
1193: END IF;
1194:
1195: -- Check for any errors and add them to the message stack to pass out to be put into the
1196: -- error log table.
1195: -- Check for any errors and add them to the message stack to pass out to be put into the
1196: -- error log table.
1197: IF NOT l_return_status = l_fnd_success then
1198: IF (l_debug > 0) THEN
1199: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
1200: END IF;
1201: l_msg_index := 1;
1202: WHILE l_msg_count > 0 loop
1203: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1210: IF l_src_instance_header_tbl.count > 0 THEN -- Added if condition for bug 13646798
1211: --Added code for bug 12536407
1212: IF l_src_instance_header_tbl(i).instance_usage_code = l_in_relationship THEN
1213: IF (l_debug > 0) THEN
1214: csi_t_gen_utility_pvt.add('Check and Break Relationship for Instance :'||l_src_instance_header_tbl(i).instance_id);
1215: END IF;
1216: csi_process_txn_pvt.check_and_break_relation(l_src_instance_header_tbl(i).instance_id,
1217: l_txn_rec,
1218: l_return_status);
1218: l_return_status);
1219:
1220: IF NOT l_return_status = l_fnd_success then
1221: IF (l_debug > 0) THEN
1222: csi_t_gen_utility_pvt.add('You encountered an error in the se_inv_trxs_pkg.check_and_break_relation');
1223: END IF;
1224: l_error_message := csi_t_gen_utility_pvt.dump_error_stack;
1225: RAISE fnd_api.g_exc_error;
1226: END IF;
1220: IF NOT l_return_status = l_fnd_success then
1221: IF (l_debug > 0) THEN
1222: csi_t_gen_utility_pvt.add('You encountered an error in the se_inv_trxs_pkg.check_and_break_relation');
1223: END IF;
1224: l_error_message := csi_t_gen_utility_pvt.dump_error_stack;
1225: RAISE fnd_api.g_exc_error;
1226: END IF;
1227: IF (l_debug > 0) THEN
1228: csi_t_gen_utility_pvt.add('Object Version originally from instance: '||l_src_instance_header_tbl(i).object_version_number);
1224: l_error_message := csi_t_gen_utility_pvt.dump_error_stack;
1225: RAISE fnd_api.g_exc_error;
1226: END IF;
1227: IF (l_debug > 0) THEN
1228: csi_t_gen_utility_pvt.add('Object Version originally from instance: '||l_src_instance_header_tbl(i).object_version_number);
1229: END IF;
1230:
1231: SELECT object_version_number
1232: into l_src_instance_header_tbl(i).object_version_number
1233: FROM csi_item_instances
1234: WHERE instance_id = l_src_instance_header_tbl(i).instance_id;
1235:
1236: IF (l_debug > 0) THEN
1237: csi_t_gen_utility_pvt.add('Current Object Version after check and break :'||l_src_instance_header_tbl(i).object_version_number);
1238: END IF;
1239: END IF;
1240: --End of code for bug 12536407
1241: END IF; -- Added if condition for bug 13646798
1263: OR (l_src_instance_header_tbl(i).instance_usage_code = 'OUT_OF_ENTERPRISE' AND l_is_internal = 'Y') THEN --Added for Bug 13460798
1264:
1265:
1266: IF (l_debug > 0) THEN
1267: csi_t_gen_utility_pvt.add('Source records found so decide which one to update using get_destination_instance procedure ');
1268: END IF;
1269:
1270: l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1271: l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
1293: l_org_assignments_tbl.delete;
1294: l_asset_assignment_tbl.delete;
1295:
1296: IF (l_debug > 0) THEN
1297: csi_t_gen_utility_pvt.add('Before Update Item Instance');
1298: END IF;
1299:
1300: csi_item_instance_pub.update_item_instance(l_api_version,
1301: l_commit,
1317: l_upd_error_instance_id := NULL;
1318: l_upd_error_instance_id := l_update_instance_rec.instance_id;
1319:
1320: IF (l_debug > 0) THEN
1321: csi_t_gen_utility_pvt.add('After Update Item Instance');
1322: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1323: END IF;
1324:
1325:
1318: l_upd_error_instance_id := l_update_instance_rec.instance_id;
1319:
1320: IF (l_debug > 0) THEN
1321: csi_t_gen_utility_pvt.add('After Update Item Instance');
1322: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1323: END IF;
1324:
1325:
1326: -- Check for any errors and add them to the message stack to pass out to be put into the
1326: -- Check for any errors and add them to the message stack to pass out to be put into the
1327: -- error log table.
1328: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1329: IF (l_debug > 0) THEN
1330: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
1331: END IF;
1332: l_msg_index := 1;
1333: WHILE l_msg_count > 0 loop
1334: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1339: END IF;
1340: ELSE
1341: l_status := 'In Inventory, Out of Service, Installed, In Service or In Process';
1342: IF (l_debug > 0) THEN
1343: 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');
1344: csi_t_gen_utility_pvt.add('Instance Usage Code is: '||l_src_instance_header_tbl(i).instance_usage_code);
1345: END IF;
1346: fnd_message.set_name('CSI','CSI_SERIALIZED_ITEM_EXISTS');
1347: fnd_message.set_token('STATUS',l_status);
1340: ELSE
1341: l_status := 'In Inventory, Out of Service, Installed, In Service or In Process';
1342: IF (l_debug > 0) THEN
1343: 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');
1344: csi_t_gen_utility_pvt.add('Instance Usage Code is: '||l_src_instance_header_tbl(i).instance_usage_code);
1345: END IF;
1346: fnd_message.set_name('CSI','CSI_SERIALIZED_ITEM_EXISTS');
1347: fnd_message.set_token('STATUS',l_status);
1348: l_error_message := fnd_message.get;
1350: RAISE fnd_api.g_exc_error;
1351: END IF;
1352:
1353: ELSIF l_src_instance_header_tbl.count = 0 THEN -- No IB Records found
1354: csi_t_gen_utility_pvt.add('No Serialized Instances are found so we need to create one that we would have received from the HZ/HR Location');
1355:
1356: l_new_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
1357: l_new_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1358: l_new_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1383: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
1384:
1385: 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);
1386:
1387: csi_t_gen_utility_pvt.add('Instance_status_id Value: '||nvl(l_new_instance_rec.instance_status_id,-1));
1388: csi_t_gen_utility_pvt.add('You will now Create a new Item Instance Record');
1389: csi_t_gen_utility_pvt.add('Serial Number: '||l_new_instance_rec.serial_number);
1390:
1391: csi_item_instance_pub.create_item_instance(l_api_version,
1384:
1385: 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);
1386:
1387: csi_t_gen_utility_pvt.add('Instance_status_id Value: '||nvl(l_new_instance_rec.instance_status_id,-1));
1388: csi_t_gen_utility_pvt.add('You will now Create a new Item Instance Record');
1389: csi_t_gen_utility_pvt.add('Serial Number: '||l_new_instance_rec.serial_number);
1390:
1391: csi_item_instance_pub.create_item_instance(l_api_version,
1392: l_commit,
1385: 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);
1386:
1387: csi_t_gen_utility_pvt.add('Instance_status_id Value: '||nvl(l_new_instance_rec.instance_status_id,-1));
1388: csi_t_gen_utility_pvt.add('You will now Create a new Item Instance Record');
1389: csi_t_gen_utility_pvt.add('Serial Number: '||l_new_instance_rec.serial_number);
1390:
1391: csi_item_instance_pub.create_item_instance(l_api_version,
1392: l_commit,
1393: l_init_msg_list,
1406:
1407: -- Check for any errors and add them to the message stack to pass out to be put into the
1408: -- error log table.
1409: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1410: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
1411: l_msg_index := 1;
1412: WHILE l_msg_count > 0 loop
1413: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1414: l_msg_index := l_msg_index + 1;
1416: END LOOP;
1417: RAISE fnd_api.g_exc_error;
1418: END IF;
1419:
1420: csi_t_gen_utility_pvt.add('Item Instance Created: '||l_new_instance_rec.instance_id);
1421:
1422: ELSIF l_src_instance_header_tbl.count > 1 THEN -- Multiple Instances Found
1423: -- Multiple Instances were found so throw error
1424: IF (l_debug > 0) THEN
1421:
1422: ELSIF l_src_instance_header_tbl.count > 1 THEN -- Multiple Instances Found
1423: -- Multiple Instances were found so throw error
1424: IF (l_debug > 0) THEN
1425: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
1426: END IF;
1427: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
1428: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
1429: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
1447: l_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1448: --l_instance_query_rec.instance_usage_code := l_out_of_service;
1449:
1450: IF (l_debug > 0) THEN
1451: csi_t_gen_utility_pvt.add('Before Get Item Instance');
1452: csi_t_gen_utility_pvt.add('Ship to Location ID Non Ser: '||l_mtl_item_tbl(j).ship_to_location_id);
1453: csi_t_gen_utility_pvt.add('Location Type Code: '||l_location_type);
1454: END IF;
1455:
1448: --l_instance_query_rec.instance_usage_code := l_out_of_service;
1449:
1450: IF (l_debug > 0) THEN
1451: csi_t_gen_utility_pvt.add('Before Get Item Instance');
1452: csi_t_gen_utility_pvt.add('Ship to Location ID Non Ser: '||l_mtl_item_tbl(j).ship_to_location_id);
1453: csi_t_gen_utility_pvt.add('Location Type Code: '||l_location_type);
1454: END IF;
1455:
1456: csi_item_instance_pub.get_item_instances(l_api_version,
1449:
1450: IF (l_debug > 0) THEN
1451: csi_t_gen_utility_pvt.add('Before Get Item Instance');
1452: csi_t_gen_utility_pvt.add('Ship to Location ID Non Ser: '||l_mtl_item_tbl(j).ship_to_location_id);
1453: csi_t_gen_utility_pvt.add('Location Type Code: '||l_location_type);
1454: END IF;
1455:
1456: csi_item_instance_pub.get_item_instances(l_api_version,
1457: l_commit,
1468: l_msg_count,
1469: l_msg_data);
1470:
1471: IF (l_debug > 0) THEN
1472: csi_t_gen_utility_pvt.add('After Get Item Instance');
1473: END IF;
1474: l_tbl_count := 0;
1475: l_tbl_count := l_src_instance_header_tbl.count;
1476: IF (l_debug > 0) THEN
1473: END IF;
1474: l_tbl_count := 0;
1475: l_tbl_count := l_src_instance_header_tbl.count;
1476: IF (l_debug > 0) THEN
1477: csi_t_gen_utility_pvt.add('Source Records Found: '||l_tbl_count);
1478: END IF;
1479:
1480: -- Check for any errors and add them to the message stack to pass out to be put into the
1481: -- error log table.
1480: -- Check for any errors and add them to the message stack to pass out to be put into the
1481: -- error log table.
1482: IF NOT l_return_status = l_fnd_success then
1483: IF (l_debug > 0) THEN
1484: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
1485: END IF;
1486: l_msg_index := 1;
1487: WHILE l_msg_count > 0 loop
1488: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1495: l_bypass_qty := 'N';
1496:
1497: IF l_src_instance_header_tbl.count > 0 THEN -- Records found so update either Serialized or Non Serialized
1498:
1499: csi_t_gen_utility_pvt.add('Since this is Non Serial decide what instances to decrement');
1500: l_curr_quantity := abs(l_mtl_item_tbl(j).primary_quantity);
1501:
1502: FOR oos IN l_src_instance_header_tbl.first .. l_src_instance_header_tbl.last LOOP
1503:
1502: FOR oos IN l_src_instance_header_tbl.first .. l_src_instance_header_tbl.last LOOP
1503:
1504: IF l_src_instance_header_tbl(oos).instance_usage_code = 'OUT_OF_SERVICE' THEN
1505:
1506: csi_t_gen_utility_pvt.add('Out of Service Instance found: '||l_src_instance_header_tbl(oos).instance_id);
1507:
1508: l_oos_found := 'Y';
1509: csi_t_gen_utility_pvt.add('l_curr_quantity: '||l_curr_quantity);
1510: -- Bug 10650152
1505:
1506: csi_t_gen_utility_pvt.add('Out of Service Instance found: '||l_src_instance_header_tbl(oos).instance_id);
1507:
1508: l_oos_found := 'Y';
1509: csi_t_gen_utility_pvt.add('l_curr_quantity: '||l_curr_quantity);
1510: -- Bug 10650152
1511: IF l_curr_quantity <= 0 THEN
1512: EXIT;
1513: END IF;
1513: END IF;
1514:
1515:
1516: IF l_src_instance_header_tbl(oos).quantity >= abs(l_mtl_item_tbl(j).primary_quantity) AND l_curr_quantity > 0 THEN
1517: csi_t_gen_utility_pvt.add('Instance found has more available quantity (or Equal) then the current quantity so subtract the entire primary quantity');
1518: l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1519: l_upd_src_dest_instance_rec.instance_id := l_src_instance_header_tbl(oos).instance_id;
1520: l_upd_src_dest_instance_rec.quantity := l_src_instance_header_tbl(oos).quantity - abs(l_mtl_item_tbl(j).primary_quantity);
1521: l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(oos).object_version_number;
1522: l_bypass_qty := 'Y';
1523: l_curr_quantity := l_curr_quantity - l_src_instance_header_tbl(oos).quantity;
1524: -- Bug 10650152
1525: ELSIF l_src_instance_header_tbl(oos).quantity < abs(l_mtl_item_tbl(j).primary_quantity) AND l_curr_quantity > 0 THEN
1526: csi_t_gen_utility_pvt.add('Instance found has less available quantity then the current quantity so set the quantity to 0');
1527: l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1528: l_upd_src_dest_instance_rec.instance_id := l_src_instance_header_tbl(oos).instance_id;
1529: l_upd_src_dest_instance_rec.quantity := 0;
1530: l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(oos).object_version_number;
1533:
1534: END IF;
1535:
1536:
1537: csi_t_gen_utility_pvt.add('Current Quantity is: '||l_curr_quantity);
1538:
1539: l_party_tbl.delete;
1540: l_account_tbl.delete;
1541: l_pricing_attrib_tbl.delete;
1542: l_org_assignments_tbl.delete;
1543: l_asset_assignment_tbl.delete;
1544:
1545: IF (l_debug > 0) THEN
1546: csi_t_gen_utility_pvt.add('Before Update Item Instance: '||l_src_instance_header_tbl(oos).instance_id);
1547: END IF;
1548:
1549: csi_item_instance_pub.update_item_instance(l_api_version,
1550: l_commit,
1566: l_upd_error_instance_id := NULL;
1567: l_upd_error_instance_id := l_upd_src_dest_instance_rec.instance_id;
1568:
1569: IF (l_debug > 0) THEN
1570: csi_t_gen_utility_pvt.add('After Update Item Instance');
1571: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1572: END IF;
1573:
1574: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1567: l_upd_error_instance_id := l_upd_src_dest_instance_rec.instance_id;
1568:
1569: IF (l_debug > 0) THEN
1570: csi_t_gen_utility_pvt.add('After Update Item Instance');
1571: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1572: END IF;
1573:
1574: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1575: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1573:
1574: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1575: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1576: IF (l_debug > 0) THEN
1577: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
1578: END IF;
1579: l_msg_index := 1;
1580: WHILE l_msg_count > 0 loop
1581: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1597: FOR ins IN l_src_instance_header_tbl.first .. l_src_instance_header_tbl.last LOOP
1598:
1599: IF l_src_instance_header_tbl(ins).instance_usage_code = 'INSTALLED' THEN
1600:
1601: csi_t_gen_utility_pvt.add('Installed Instance found: '||l_src_instance_header_tbl(ins).instance_id);
1602:
1603: l_ins_found := 'Y';
1604:
1605: IF l_oos_found = 'N' THEN
1602:
1603: l_ins_found := 'Y';
1604:
1605: IF l_oos_found = 'N' THEN
1606: csi_t_gen_utility_pvt.add('Out of Service Instance not found so setting current qty to be the primary quantity');
1607: END IF;
1608:
1609: csi_t_gen_utility_pvt.add('l_curr_quantity: '||l_curr_quantity);
1610: -- Bug 10650152
1605: IF l_oos_found = 'N' THEN
1606: csi_t_gen_utility_pvt.add('Out of Service Instance not found so setting current qty to be the primary quantity');
1607: END IF;
1608:
1609: csi_t_gen_utility_pvt.add('l_curr_quantity: '||l_curr_quantity);
1610: -- Bug 10650152
1611: IF l_curr_quantity <= 0 THEN
1612: EXIT;
1613: END IF;
1611: IF l_curr_quantity <= 0 THEN
1612: EXIT;
1613: END IF;
1614: IF l_src_instance_header_tbl(ins).quantity >= l_curr_quantity AND l_curr_quantity > 0 THEN
1615: csi_t_gen_utility_pvt.add('Instance found has more available quantity (or Equal) then the current quantity so subtract the entire primary quantity');
1616: l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1617: l_upd_src_dest_instance_rec.instance_id := l_src_instance_header_tbl(ins).instance_id;
1618: l_upd_src_dest_instance_rec.quantity := l_src_instance_header_tbl(ins).quantity - l_curr_quantity;
1619: l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(ins).object_version_number;
1621: l_curr_quantity := l_curr_quantity - l_src_instance_header_tbl(ins).quantity;
1622:
1623: -- Bug 10650152
1624: ELSIF l_src_instance_header_tbl(ins).quantity < l_curr_quantity AND l_curr_quantity > 0 THEN
1625: csi_t_gen_utility_pvt.add('Instance found has less available quantity then the current quantity so set the quantity to 0');
1626: l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1627: l_upd_src_dest_instance_rec.instance_id := l_src_instance_header_tbl(ins).instance_id;
1628: l_upd_src_dest_instance_rec.quantity := 0;
1629: l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(ins).object_version_number;
1631: l_curr_quantity := l_curr_quantity - l_src_instance_header_tbl(ins).quantity;
1632:
1633: END IF;
1634:
1635: csi_t_gen_utility_pvt.add('Current Quantity is: '||l_curr_quantity);
1636:
1637: l_party_tbl.delete;
1638: l_account_tbl.delete;
1639: l_pricing_attrib_tbl.delete;
1640: l_org_assignments_tbl.delete;
1641: l_asset_assignment_tbl.delete;
1642:
1643: IF (l_debug > 0) THEN
1644: csi_t_gen_utility_pvt.add('Before Update Item Instance: '||l_src_instance_header_tbl(ins).instance_id);
1645: END IF;
1646:
1647: csi_item_instance_pub.update_item_instance(l_api_version,
1648: l_commit,
1664: l_upd_error_instance_id := NULL;
1665: l_upd_error_instance_id := l_upd_src_dest_instance_rec.instance_id;
1666:
1667: IF (l_debug > 0) THEN
1668: csi_t_gen_utility_pvt.add('After Update Item Instance');
1669: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1670: END IF;
1671:
1672: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1665: l_upd_error_instance_id := l_upd_src_dest_instance_rec.instance_id;
1666:
1667: IF (l_debug > 0) THEN
1668: csi_t_gen_utility_pvt.add('After Update Item Instance');
1669: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1670: END IF;
1671:
1672: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1673: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1671:
1672: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1673: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1674: IF (l_debug > 0) THEN
1675: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
1676: END IF;
1677: l_msg_index := 1;
1678: WHILE l_msg_count > 0 loop
1679: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1697: FOR inser IN l_src_instance_header_tbl.first .. l_src_instance_header_tbl.last LOOP
1698:
1699: IF l_src_instance_header_tbl(inser).instance_usage_code = 'IN_SERVICE' THEN
1700:
1701: csi_t_gen_utility_pvt.add('In Service Instance found: '|| l_src_instance_header_tbl(inser).instance_id);
1702:
1703: IF l_oos_found = 'N' AND l_ins_found = 'N' THEN
1704: csi_t_gen_utility_pvt.add('Out of Service or Installed Instance not found so setting current qty to be the primary quantity');
1705: END IF;
1700:
1701: csi_t_gen_utility_pvt.add('In Service Instance found: '|| l_src_instance_header_tbl(inser).instance_id);
1702:
1703: IF l_oos_found = 'N' AND l_ins_found = 'N' THEN
1704: csi_t_gen_utility_pvt.add('Out of Service or Installed Instance not found so setting current qty to be the primary quantity');
1705: END IF;
1706: csi_t_gen_utility_pvt.add('l_curr_quantity: '||l_curr_quantity);
1707: -- Bug 10650152
1708: IF l_curr_quantity <= 0 THEN
1702:
1703: IF l_oos_found = 'N' AND l_ins_found = 'N' THEN
1704: csi_t_gen_utility_pvt.add('Out of Service or Installed Instance not found so setting current qty to be the primary quantity');
1705: END IF;
1706: csi_t_gen_utility_pvt.add('l_curr_quantity: '||l_curr_quantity);
1707: -- Bug 10650152
1708: IF l_curr_quantity <= 0 THEN
1709: EXIT;
1710: END IF;
1708: IF l_curr_quantity <= 0 THEN
1709: EXIT;
1710: END IF;
1711: IF l_src_instance_header_tbl(inser).quantity >= l_curr_quantity AND l_curr_quantity > 0 THEN
1712: csi_t_gen_utility_pvt.add('Instance found has more available quantity (or Equal) then the current quantity so subtract the entire primary quantity');
1713: l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1714: l_upd_src_dest_instance_rec.instance_id := l_src_instance_header_tbl(inser).instance_id;
1715: l_upd_src_dest_instance_rec.quantity := l_src_instance_header_tbl(inser).quantity - l_curr_quantity;
1716: l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(inser).object_version_number;
1718: l_bypass_qty := 'Y';
1719: l_curr_quantity := l_curr_quantity - l_src_instance_header_tbl(inser).quantity;
1720: -- Bug 10650152
1721: ELSIF l_src_instance_header_tbl(inser).quantity < l_curr_quantity AND l_curr_quantity > 0 THEN
1722: csi_t_gen_utility_pvt.add('Instance found has less available quantity then the current quantity so set the quantity to 0');
1723: l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1724: l_upd_src_dest_instance_rec.instance_id := l_src_instance_header_tbl(inser).instance_id;
1725: l_upd_src_dest_instance_rec.quantity := 0;
1726: l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(inser).object_version_number;
1728: l_curr_quantity := l_curr_quantity - l_src_instance_header_tbl(inser).quantity;
1729:
1730: END IF;
1731:
1732: csi_t_gen_utility_pvt.add('Current Quantity is: '||l_curr_quantity);
1733:
1734: l_party_tbl.delete;
1735: l_account_tbl.delete;
1736: l_pricing_attrib_tbl.delete;
1737: l_org_assignments_tbl.delete;
1738: l_asset_assignment_tbl.delete;
1739:
1740: IF (l_debug > 0) THEN
1741: csi_t_gen_utility_pvt.add('Before Update Item Instance: '||l_src_instance_header_tbl(inser).instance_id);
1742: END IF;
1743:
1744: csi_item_instance_pub.update_item_instance(l_api_version,
1745: l_commit,
1761: l_upd_error_instance_id := NULL;
1762: l_upd_error_instance_id := l_upd_src_dest_instance_rec.instance_id;
1763:
1764: IF (l_debug > 0) THEN
1765: csi_t_gen_utility_pvt.add('After Update Item Instance');
1766: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1767: END IF;
1768:
1769: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1762: l_upd_error_instance_id := l_upd_src_dest_instance_rec.instance_id;
1763:
1764: IF (l_debug > 0) THEN
1765: csi_t_gen_utility_pvt.add('After Update Item Instance');
1766: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1767: END IF;
1768:
1769: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1770: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1768:
1769: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1770: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1771: IF (l_debug > 0) THEN
1772: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
1773: END IF;
1774: l_msg_index := 1;
1775: WHILE l_msg_count > 0 loop
1776: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1795: l_instance_dest_query_rec.serial_number := NULL;
1796: l_instance_dest_query_rec.instance_usage_code := l_in_inventory;
1797:
1798: IF (l_debug > 0) THEN
1799: csi_t_gen_utility_pvt.add('Before Get Item Instance');
1800: END IF;
1801:
1802: csi_item_instance_pub.get_item_instances(l_api_version,
1803: l_commit,
1814: l_msg_count,
1815: l_msg_data);
1816:
1817: IF (l_debug > 0) THEN
1818: csi_t_gen_utility_pvt.add('After Get Item Instance');
1819: END IF;
1820: l_tbl_count := 0;
1821: l_tbl_count := l_dest_instance_header_tbl.count;
1822: IF (l_debug > 0) THEN
1819: END IF;
1820: l_tbl_count := 0;
1821: l_tbl_count := l_dest_instance_header_tbl.count;
1822: IF (l_debug > 0) THEN
1823: csi_t_gen_utility_pvt.add('Destination Records Found: '||l_tbl_count);
1824: END IF;
1825:
1826: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1827: IF NOT l_return_status = l_fnd_success then
1825:
1826: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1827: IF NOT l_return_status = l_fnd_success then
1828: IF (l_debug > 0) THEN
1829: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
1830: END IF;
1831: l_msg_index := 1;
1832: WHILE l_msg_count > 0 loop
1833: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1870:
1871: 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);
1872:
1873: IF (l_debug > 0) THEN
1874: csi_t_gen_utility_pvt.add('Before Create Item Instance');
1875: END IF;
1876:
1877: csi_item_instance_pub.create_item_instance(l_api_version,
1878: l_commit,
1890: l_msg_count,
1891: l_msg_data);
1892:
1893: IF (l_debug > 0) THEN
1894: csi_t_gen_utility_pvt.add('After Update Item Instance');
1895: END IF;
1896:
1897: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1898: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1896:
1897: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1898: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1899: IF (l_debug > 0) THEN
1900: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
1901: END IF;
1902: l_msg_index := 1;
1903: WHILE l_msg_count > 0 loop
1904: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1924:
1925: 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);
1926:
1927: IF (l_debug > 0) THEN
1928: csi_t_gen_utility_pvt.add('Before Update Item Instance');
1929: END IF;
1930:
1931: csi_item_instance_pub.update_item_instance(l_api_version,
1932: l_commit,
1948: l_upd_error_instance_id := NULL;
1949: l_upd_error_instance_id := l_update_instance_rec.instance_id;
1950:
1951: IF (l_debug > 0) THEN
1952: csi_t_gen_utility_pvt.add('After Update Item Instance');
1953: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1954: END IF;
1955:
1956: -- Check for any errors and add them to the message stack to pass out to be put into the
1949: l_upd_error_instance_id := l_update_instance_rec.instance_id;
1950:
1951: IF (l_debug > 0) THEN
1952: csi_t_gen_utility_pvt.add('After Update Item Instance');
1953: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1954: END IF;
1955:
1956: -- Check for any errors and add them to the message stack to pass out to be put into the
1957: -- error log table.
1956: -- Check for any errors and add them to the message stack to pass out to be put into the
1957: -- error log table.
1958: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1959: IF (l_debug > 0) THEN
1960: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
1961: END IF;
1962: l_msg_index := 1;
1963: WHILE l_msg_count > 0 loop
1964: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1971: ELSIF l_dest_instance_header_tbl.count > 1 THEN
1972:
1973: -- Multiple Instances were found so throw error
1974: IF (l_debug > 0) THEN
1975: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
1976: END IF;
1977: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
1978: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
1979: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
1986: /***
1987: ELSIF l_src_instance_header_tbl.count > 1 THEN
1988: -- Multiple Instances were found so throw error
1989: IF (l_debug > 0) THEN
1990: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
1991: END IF;
1992: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
1993: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
1994: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
1998: RAISE fnd_api.g_exc_error;
1999: ***/
2000: ELSIF l_src_instance_header_tbl.count = 0 THEN -- No IB Records found
2001:
2002: csi_t_gen_utility_pvt.add('No Source Records in a HZ/HR Location 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');
2003:
2004: l_instance_dest_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
2005: l_instance_dest_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2006: l_instance_dest_query_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
2011: l_instance_dest_query_rec.serial_number := NULL;
2012: l_instance_dest_query_rec.instance_usage_code := l_in_inventory;
2013:
2014: IF (l_debug > 0) THEN
2015: csi_t_gen_utility_pvt.add('Before Get Item Instance');
2016: END IF;
2017:
2018: csi_item_instance_pub.get_item_instances(l_api_version,
2019: l_commit,
2030: l_msg_count,
2031: l_msg_data);
2032:
2033: IF (l_debug > 0) THEN
2034: csi_t_gen_utility_pvt.add('After Get Item Instance');
2035: END IF;
2036: l_tbl_count := 0;
2037: l_tbl_count := l_dest_instance_header_tbl.count;
2038: IF (l_debug > 0) THEN
2035: END IF;
2036: l_tbl_count := 0;
2037: l_tbl_count := l_dest_instance_header_tbl.count;
2038: IF (l_debug > 0) THEN
2039: csi_t_gen_utility_pvt.add('Destination Records Found: '||l_tbl_count);
2040: END IF;
2041:
2042: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2043: IF NOT l_return_status = l_fnd_success then
2041:
2042: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2043: IF NOT l_return_status = l_fnd_success then
2044: IF (l_debug > 0) THEN
2045: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
2046: END IF;
2047: l_msg_index := 1;
2048: WHILE l_msg_count > 0 loop
2049: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2086:
2087: 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);
2088:
2089: IF (l_debug > 0) THEN
2090: csi_t_gen_utility_pvt.add('Before Create Item Instance');
2091: END IF;
2092:
2093: csi_item_instance_pub.create_item_instance(l_api_version,
2094: l_commit,
2106: l_msg_count,
2107: l_msg_data);
2108:
2109: IF (l_debug > 0) THEN
2110: csi_t_gen_utility_pvt.add('After Update Item Instance');
2111: END IF;
2112:
2113: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2114: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2112:
2113: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2114: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2115: IF (l_debug > 0) THEN
2116: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
2117: END IF;
2118: l_msg_index := 1;
2119: WHILE l_msg_count > 0 loop
2120: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2140:
2141: 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);
2142:
2143: IF (l_debug > 0) THEN
2144: csi_t_gen_utility_pvt.add('Before Update Item Instance');
2145: END IF;
2146:
2147: csi_item_instance_pub.update_item_instance(l_api_version,
2148: l_commit,
2164: l_upd_error_instance_id := NULL;
2165: l_upd_error_instance_id := l_update_instance_rec.instance_id;
2166:
2167: IF (l_debug > 0) THEN
2168: csi_t_gen_utility_pvt.add('After Update Item Instance');
2169: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2170: END IF;
2171:
2172: -- Check for any errors and add them to the message stack to pass out to be put into the
2165: l_upd_error_instance_id := l_update_instance_rec.instance_id;
2166:
2167: IF (l_debug > 0) THEN
2168: csi_t_gen_utility_pvt.add('After Update Item Instance');
2169: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2170: END IF;
2171:
2172: -- Check for any errors and add them to the message stack to pass out to be put into the
2173: -- error log table.
2172: -- Check for any errors and add them to the message stack to pass out to be put into the
2173: -- error log table.
2174: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2175: IF (l_debug > 0) THEN
2176: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
2177: END IF;
2178: l_msg_index := 1;
2179: WHILE l_msg_count > 0 loop
2180: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2186:
2187: ELSIF l_src_instance_header_tbl.count > 1 THEN
2188: -- Multiple Instances were found so throw error
2189: IF (l_debug > 0) THEN
2190: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
2191: END IF;
2192: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
2193: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
2194: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
2202: END IF; -- End of Serial Number If
2203: END LOOP; -- End of For Loop
2204:
2205: IF (l_debug > 0) THEN
2206: csi_t_gen_utility_pvt.add('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2207: csi_t_gen_utility_pvt.add('*****End of csi_inv_hz_pkg.misc_receipt_hz_loc Transaction procedure*****');
2208: END IF;
2209:
2210: EXCEPTION
2203: END LOOP; -- End of For Loop
2204:
2205: IF (l_debug > 0) THEN
2206: csi_t_gen_utility_pvt.add('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2207: csi_t_gen_utility_pvt.add('*****End of csi_inv_hz_pkg.misc_receipt_hz_loc Transaction procedure*****');
2208: END IF;
2209:
2210: EXCEPTION
2211: WHEN fnd_api.g_exc_error THEN
2209:
2210: EXCEPTION
2211: WHEN fnd_api.g_exc_error THEN
2212: IF (l_debug > 0) THEN
2213: csi_t_gen_utility_pvt.add('You have encountered a "fnd_api.g_exc_error" exception');
2214: END IF;
2215: x_return_status := l_fnd_error;
2216: x_trx_error_rec.error_text := l_error_message;
2217:
2237: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
2238:
2239: WHEN others THEN
2240: IF (l_debug > 0) THEN
2241: csi_t_gen_utility_pvt.add('You have encountered a "others" exception');
2242: END IF;
2243: fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
2244: fnd_message.set_token('API_NAME',l_api_name);
2245: fnd_message.set_token('SQL_ERROR',SQLERRM);
2365:
2366: x_return_status := l_fnd_success;
2367:
2368: IF (l_debug > 0) THEN
2369: csi_t_gen_utility_pvt.add('*****Start of csi_inv_hz_pkg.misc_issue_hz_loc Transaction procedure*****');
2370: csi_t_gen_utility_pvt.add('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2371: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
2372: csi_t_gen_utility_pvt.add('csiivtzb.pls 115.14');
2373: END IF;
2366: x_return_status := l_fnd_success;
2367:
2368: IF (l_debug > 0) THEN
2369: csi_t_gen_utility_pvt.add('*****Start of csi_inv_hz_pkg.misc_issue_hz_loc Transaction procedure*****');
2370: csi_t_gen_utility_pvt.add('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2371: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
2372: csi_t_gen_utility_pvt.add('csiivtzb.pls 115.14');
2373: END IF;
2374:
2367:
2368: IF (l_debug > 0) THEN
2369: csi_t_gen_utility_pvt.add('*****Start of csi_inv_hz_pkg.misc_issue_hz_loc Transaction procedure*****');
2370: csi_t_gen_utility_pvt.add('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2371: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
2372: csi_t_gen_utility_pvt.add('csiivtzb.pls 115.14');
2373: END IF;
2374:
2375: -- This procedure queries all of the Inventory Transaction Records
2368: IF (l_debug > 0) THEN
2369: csi_t_gen_utility_pvt.add('*****Start of csi_inv_hz_pkg.misc_issue_hz_loc Transaction procedure*****');
2370: csi_t_gen_utility_pvt.add('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2371: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
2372: csi_t_gen_utility_pvt.add('csiivtzb.pls 115.14');
2373: END IF;
2374:
2375: -- This procedure queries all of the Inventory Transaction Records
2376: -- and returns them as a table.
2382:
2383: l_tbl_count := 0;
2384: l_tbl_count := l_mtl_item_tbl.count;
2385: IF (l_debug > 0) THEN
2386: csi_t_gen_utility_pvt.add('Inventory Records Found: '||l_tbl_count);
2387: END IF;
2388:
2389: IF NOT l_return_status = l_fnd_success THEN
2390: IF (l_debug > 0) THEN
2387: END IF;
2388:
2389: IF NOT l_return_status = l_fnd_success THEN
2390: IF (l_debug > 0) THEN
2391: csi_t_gen_utility_pvt.add('You have encountered an error in CSI_INV_TRXS_PKG.get_transaction_recs, Transaction ID: '||p_transaction_id);
2392: END IF;
2393: RAISE fnd_api.g_exc_error;
2394: END IF;
2395:
2400: l_error_message);
2401:
2402: IF NOT l_return_status = l_fnd_success THEN
2403: IF (l_debug > 0) THEN
2404: 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);
2405: END IF;
2406: RAISE fnd_api.g_exc_error;
2407: END IF;
2408:
2410: l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
2411:
2412: IF l_employee_id = -1 THEN
2413: IF (l_debug > 0) THEN
2414: 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');
2415: END IF;
2416: END IF;
2417: IF (l_debug > 0) THEN
2418: csi_t_gen_utility_pvt.add('The Employee that is processing this Transaction is: '||l_employee_id);
2414: 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');
2415: END IF;
2416: END IF;
2417: IF (l_debug > 0) THEN
2418: csi_t_gen_utility_pvt.add('The Employee that is processing this Transaction is: '||l_employee_id);
2419: END IF;
2420:
2421: -- See if this is a depreciable Item to set the status of the transaction record
2422: csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
2423: l_depreciable,
2424: l_mtl_item_tbl(i).organization_id); --Added for Bug 13988660
2425:
2426: IF (l_debug > 0) THEN
2427: csi_t_gen_utility_pvt.add('Is this Item ID: '||l_mtl_item_tbl(i).inventory_item_id||', Depreciable :'||l_depreciable);
2428: END IF;
2429:
2430:
2431: -- Set the mfg_serial_number_flag and quantity
2449: l_instance_status := FND_API.G_TRUE;
2450: END IF;
2451:
2452: IF (l_debug > 0) THEN
2453: csi_t_gen_utility_pvt.add('Negative Code is - 1 = Yes, 2 = No: '||l_neg_code);
2454: END IF;
2455:
2456: -- Get Instance Status ID
2457: OPEN c_id;
2476: END IF;
2477: END IF;
2478:
2479: IF (l_debug > 0) THEN
2480: csi_t_gen_utility_pvt.add('Location ID - Location Type; '||l_mtl_item_tbl(i).ship_to_location_id||'-'||l_location_type);
2481: END IF;
2482:
2483: -- Create CSI Transaction to be used
2484: l_txn_rec := csi_inv_trxs_pkg.init_txn_rec;
2499: l_error_message,
2500: l_return_status);
2501:
2502: IF (l_debug > 0) THEN
2503: csi_t_gen_utility_pvt.add('CSI Transaction Created: '||l_txn_rec.transaction_id);
2504: END IF;
2505:
2506: IF NOT l_return_status = l_fnd_success THEN
2507: IF (l_debug > 0) THEN
2504: END IF;
2505:
2506: IF NOT l_return_status = l_fnd_success THEN
2507: IF (l_debug > 0) THEN
2508: csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.create_csi_txn: '||p_transaction_id);
2509: END IF;
2510: RAISE fnd_api.g_exc_error;
2511: END IF;
2512:
2513: -- Now loop through the PL/SQL Table.
2514: j := 1;
2515:
2516: IF (l_debug > 0) THEN
2517: csi_t_gen_utility_pvt.add('Starting to loop through Material Transaction Records');
2518: END IF;
2519:
2520: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
2521:
2519:
2520: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
2521:
2522: IF (l_debug > 0) THEN
2523: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
2524: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
2525: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
2526: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
2527: END IF;
2520: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
2521:
2522: IF (l_debug > 0) THEN
2523: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
2524: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
2525: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
2526: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
2527: END IF;
2528:
2521:
2522: IF (l_debug > 0) THEN
2523: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
2524: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
2525: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
2526: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
2527: END IF;
2528:
2529: l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
2522: IF (l_debug > 0) THEN
2523: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
2524: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
2525: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
2526: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
2527: END IF;
2528:
2529: l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
2530: l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2537: --l_instance_query_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
2538: l_instance_query_rec.instance_usage_code := l_in_inventory;
2539:
2540: IF (l_debug > 0) THEN
2541: csi_t_gen_utility_pvt.add('Before Get Item Instance');
2542: csi_t_gen_utility_pvt.add('Ship to Location ID: '||l_mtl_item_tbl(j).ship_to_location_id);
2543: END IF;
2544:
2545: csi_item_instance_pub.get_item_instances(l_api_version,
2538: l_instance_query_rec.instance_usage_code := l_in_inventory;
2539:
2540: IF (l_debug > 0) THEN
2541: csi_t_gen_utility_pvt.add('Before Get Item Instance');
2542: csi_t_gen_utility_pvt.add('Ship to Location ID: '||l_mtl_item_tbl(j).ship_to_location_id);
2543: END IF;
2544:
2545: csi_item_instance_pub.get_item_instances(l_api_version,
2546: l_commit,
2557: l_msg_count,
2558: l_msg_data);
2559:
2560: IF (l_debug > 0) THEN
2561: csi_t_gen_utility_pvt.add('After Get Item Instance');
2562: END IF;
2563: l_tbl_count := 0;
2564: l_tbl_count := l_src_instance_header_tbl.count;
2565: IF (l_debug > 0) THEN
2562: END IF;
2563: l_tbl_count := 0;
2564: l_tbl_count := l_src_instance_header_tbl.count;
2565: IF (l_debug > 0) THEN
2566: csi_t_gen_utility_pvt.add('Source Records Found: '||l_tbl_count);
2567: END IF;
2568:
2569: -- Check for any errors and add them to the message stack to pass out to be put into the
2570: -- error log table.
2569: -- Check for any errors and add them to the message stack to pass out to be put into the
2570: -- error log table.
2571: IF NOT l_return_status = l_fnd_success then
2572: IF (l_debug > 0) THEN
2573: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
2574: END IF;
2575: l_msg_index := 1;
2576: WHILE l_msg_count > 0 loop
2577: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2586: IF l_src_instance_header_tbl.count = 0 THEN
2587: IF l_neg_code = 1 THEN -- Allow Neg Qtys on NON Serial Items ONLY
2588:
2589: IF (l_debug > 0) THEN
2590: csi_t_gen_utility_pvt.add('No records were found so create a new Source Instance Record');
2591: END IF;
2592:
2593: l_new_src_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
2594: l_new_src_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2618: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2619: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2620:
2621: IF (l_debug > 0) THEN
2622: csi_t_gen_utility_pvt.add('Before Create Transaction - Neg Qty');
2623: END IF;
2624:
2625: csi_item_instance_pub.create_item_instance(l_api_version,
2626: l_commit,
2638: l_msg_count,
2639: l_msg_data);
2640:
2641: IF (l_debug > 0) THEN
2642: csi_t_gen_utility_pvt.add('After Create Transaction');
2643: csi_t_gen_utility_pvt.add('New instance created is: '||l_new_src_instance_rec.instance_id);
2644: END IF;
2645:
2646: -- Check for any errors and add them to the message stack to pass out to be put into the
2639: l_msg_data);
2640:
2641: IF (l_debug > 0) THEN
2642: csi_t_gen_utility_pvt.add('After Create Transaction');
2643: csi_t_gen_utility_pvt.add('New instance created is: '||l_new_src_instance_rec.instance_id);
2644: END IF;
2645:
2646: -- Check for any errors and add them to the message stack to pass out to be put into the
2647: -- error log table.
2646: -- Check for any errors and add them to the message stack to pass out to be put into the
2647: -- error log table.
2648: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2649: IF (l_debug > 0) THEN
2650: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
2651: END IF;
2652: l_msg_index := 1;
2653: WHILE l_msg_count > 0 loop
2654: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2659: END IF;
2660:
2661: ELSE -- No Records were found and Neg Qtys Not Allowed
2662: IF (l_debug > 0) THEN
2663: csi_t_gen_utility_pvt.add('No Records were found in Install Base andNeg Qtys not allowed to error');
2664: END IF;
2665: fnd_message.set_name('CSI','CSI_NO_NEG_BAL_ALLOWED');
2666: l_error_message := fnd_message.get;
2667: RAISE fnd_api.g_exc_error;
2670:
2671: ELSIF l_src_instance_header_tbl.count = 1 THEN
2672:
2673: IF (l_debug > 0) THEN
2674: csi_t_gen_utility_pvt.add('You will update instance: '||l_src_instance_header_tbl(i).instance_id);
2675: END IF;
2676:
2677: l_update_source_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2678: l_update_source_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
2685: l_org_assignments_tbl.delete;
2686: l_asset_assignment_tbl.delete;
2687:
2688: IF (l_debug > 0) THEN
2689: csi_t_gen_utility_pvt.add('Before Update Item Instance - 80');
2690: END IF;
2691:
2692: 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);
2693:
2691:
2692: 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);
2693:
2694: IF (l_debug > 0) THEN
2695: csi_t_gen_utility_pvt.add('Instance Status Id: '||l_update_source_instance_rec.instance_status_id);
2696: END IF;
2697:
2698: csi_item_instance_pub.update_item_instance(l_api_version,
2699: l_commit,
2715: l_upd_error_instance_id := NULL;
2716: l_upd_error_instance_id := l_update_source_instance_rec.instance_id;
2717:
2718: IF (l_debug > 0) THEN
2719: csi_t_gen_utility_pvt.add('After Update Item Instance - Neg Qty');
2720: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2721: END IF;
2722:
2723: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2716: l_upd_error_instance_id := l_update_source_instance_rec.instance_id;
2717:
2718: IF (l_debug > 0) THEN
2719: csi_t_gen_utility_pvt.add('After Update Item Instance - Neg Qty');
2720: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2721: END IF;
2722:
2723: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2724: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2722:
2723: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2724: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2725: IF (l_debug > 0) THEN
2726: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
2727: END IF;
2728: l_msg_index := 1;
2729: WHILE l_msg_count > 0 loop
2730: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2736:
2737: ELSIF l_src_instance_header_tbl.count > 1 THEN
2738: -- Multiple Instances were found so throw error
2739: IF (l_debug > 0) THEN
2740: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
2741: END IF;
2742: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
2743: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
2744: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
2763: l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2764: l_instance_query_rec.instance_usage_code := l_in_service;
2765:
2766: IF (l_debug > 0) THEN
2767: csi_t_gen_utility_pvt.add('Before Get Item Instance Dest - Neg Qty');
2768: csi_t_gen_utility_pvt.add('Ship to Location ID Non Ser: '||l_mtl_item_tbl(j).ship_to_location_id);
2769: csi_t_gen_utility_pvt.add('Location Type Code: '||l_location_type);
2770: END IF;
2771:
2764: l_instance_query_rec.instance_usage_code := l_in_service;
2765:
2766: IF (l_debug > 0) THEN
2767: csi_t_gen_utility_pvt.add('Before Get Item Instance Dest - Neg Qty');
2768: csi_t_gen_utility_pvt.add('Ship to Location ID Non Ser: '||l_mtl_item_tbl(j).ship_to_location_id);
2769: csi_t_gen_utility_pvt.add('Location Type Code: '||l_location_type);
2770: END IF;
2771:
2772: csi_item_instance_pub.get_item_instances(l_api_version,
2765:
2766: IF (l_debug > 0) THEN
2767: csi_t_gen_utility_pvt.add('Before Get Item Instance Dest - Neg Qty');
2768: csi_t_gen_utility_pvt.add('Ship to Location ID Non Ser: '||l_mtl_item_tbl(j).ship_to_location_id);
2769: csi_t_gen_utility_pvt.add('Location Type Code: '||l_location_type);
2770: END IF;
2771:
2772: csi_item_instance_pub.get_item_instances(l_api_version,
2773: l_commit,
2784: l_msg_count,
2785: l_msg_data);
2786:
2787: IF (l_debug > 0) THEN
2788: csi_t_gen_utility_pvt.add('After Get Item Instance Dest - Neg Qty');
2789: END IF;
2790: l_tbl_count := 0;
2791: l_tbl_count := l_dest_instance_header_tbl.count;
2792: IF (l_debug > 0) THEN
2789: END IF;
2790: l_tbl_count := 0;
2791: l_tbl_count := l_dest_instance_header_tbl.count;
2792: IF (l_debug > 0) THEN
2793: csi_t_gen_utility_pvt.add('Destination Records Found: '||l_tbl_count);
2794: END IF;
2795:
2796: -- Check for any errors and add them to the message stack to pass out to be put into the
2797: -- error log table.
2796: -- Check for any errors and add them to the message stack to pass out to be put into the
2797: -- error log table.
2798: IF NOT l_return_status = l_fnd_success then
2799: IF (l_debug > 0) THEN
2800: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
2801: END IF;
2802: l_msg_index := 1;
2803: WHILE l_msg_count > 0 loop
2804: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2809: END IF;
2810:
2811: IF l_dest_instance_header_tbl.count = 0 THEN -- Installed Base Destination Records are not found
2812: IF (l_debug > 0) THEN
2813: csi_t_gen_utility_pvt.add('No Destination Records were found so we will create a new destination Record using the source data');
2814: END IF;
2815:
2816: l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
2817: l_new_dest_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2843: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2844: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2845:
2846: IF (l_debug > 0) THEN
2847: csi_t_gen_utility_pvt.add('Before Create Item Instance - Neg Qty');
2848: END IF;
2849:
2850: csi_item_instance_pub.create_item_instance(l_api_version,
2851: l_commit,
2863: l_msg_count,
2864: l_msg_data);
2865:
2866: IF (l_debug > 0) THEN
2867: csi_t_gen_utility_pvt.add('After Create Item Instance - Neg Qty');
2868: END IF;
2869:
2870: -- Check for any errors and add them to the message stack to pass out to be put into the
2871: -- error log table.
2870: -- Check for any errors and add them to the message stack to pass out to be put into the
2871: -- error log table.
2872: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2873: IF (l_debug > 0) THEN
2874: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
2875: END IF;
2876: l_msg_index := 1;
2877: WHILE l_msg_count > 0 loop
2878: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2884:
2885: ELSIF l_dest_instance_header_tbl.count = 1 THEN-- Installed Base Destination Records Found
2886:
2887: IF (l_debug > 0) THEN
2888: csi_t_gen_utility_pvt.add('You will update instance: '||l_dest_instance_header_tbl(i).instance_id);
2889: END IF;
2890:
2891: l_update_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2892: l_update_dest_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
2902:
2903: 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);
2904:
2905: IF (l_debug > 0) THEN
2906: csi_t_gen_utility_pvt.add('Before Update Transaction - Neg Qty');
2907: END IF;
2908:
2909: csi_item_instance_pub.update_item_instance(l_api_version,
2910: l_commit,
2926: l_upd_error_instance_id := NULL;
2927: l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
2928:
2929: IF (l_debug > 0) THEN
2930: csi_t_gen_utility_pvt.add('After Update Transaction - Neg Qty');
2931: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2932: END IF;
2933:
2934: -- Check for any errors and add them to the message stack to pass out to be put into the
2927: l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
2928:
2929: IF (l_debug > 0) THEN
2930: csi_t_gen_utility_pvt.add('After Update Transaction - Neg Qty');
2931: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2932: END IF;
2933:
2934: -- Check for any errors and add them to the message stack to pass out to be put into the
2935: -- error log table.
2934: -- Check for any errors and add them to the message stack to pass out to be put into the
2935: -- error log table.
2936: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2937: IF (l_debug > 0) THEN
2938: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
2939: END IF;
2940: l_msg_index := 1;
2941: WHILE l_msg_count > 0 loop
2942: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2949: ELSIF l_dest_instance_header_tbl.count > 1 THEN
2950:
2951: -- Multiple Instances were found so throw error
2952: IF (l_debug > 0) THEN
2953: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-90');
2954: END IF;
2955: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
2956: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
2957: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
2988: l_org_assignments_tbl.delete;
2989: l_asset_assignment_tbl.delete;
2990:
2991: IF (l_debug > 0) THEN
2992: csi_t_gen_utility_pvt.add('Before Update Item Instance');
2993: csi_t_gen_utility_pvt.add('Ship to Location ID Serialized: '||l_mtl_item_tbl(j).ship_to_location_id);
2994: END IF;
2995:
2996: csi_item_instance_pub.update_item_instance(l_api_version,
2989: l_asset_assignment_tbl.delete;
2990:
2991: IF (l_debug > 0) THEN
2992: csi_t_gen_utility_pvt.add('Before Update Item Instance');
2993: csi_t_gen_utility_pvt.add('Ship to Location ID Serialized: '||l_mtl_item_tbl(j).ship_to_location_id);
2994: END IF;
2995:
2996: csi_item_instance_pub.update_item_instance(l_api_version,
2997: l_commit,
3013: l_upd_error_instance_id := NULL;
3014: l_upd_error_instance_id := l_update_instance_rec.instance_id;
3015:
3016: IF (l_debug > 0) THEN
3017: csi_t_gen_utility_pvt.add('After Update Item Instance');
3018: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
3019: END IF;
3020:
3021: -- Check for any errors and add them to the message stack to pass out to be put into the
3014: l_upd_error_instance_id := l_update_instance_rec.instance_id;
3015:
3016: IF (l_debug > 0) THEN
3017: csi_t_gen_utility_pvt.add('After Update Item Instance');
3018: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
3019: END IF;
3020:
3021: -- Check for any errors and add them to the message stack to pass out to be put into the
3022: -- error log table.
3021: -- Check for any errors and add them to the message stack to pass out to be put into the
3022: -- error log table.
3023: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
3024: IF (l_debug > 0) THEN
3025: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
3026: END IF;
3027: l_msg_index := 1;
3028: WHILE l_msg_count > 0 loop
3029: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
3034: END IF;
3035:
3036: ELSIF l_src_instance_header_tbl.count = 0 THEN
3037: IF (l_debug > 0) THEN
3038: csi_t_gen_utility_pvt.add('No Records were found in Install Base');
3039: END IF;
3040: fnd_message.set_name('CSI','CSI_IB_RECORD_NOTFOUND');
3041: fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
3042: fnd_message.set_token('SUBINVENTORY',l_mtl_item_tbl(j).subinventory_code);
3046:
3047: ELSIF l_src_instance_header_tbl.count > 1 THEN
3048: -- Multiple Instances were found so throw error
3049: IF (l_debug > 0) THEN
3050: csi_t_gen_utility_pvt.add('Multiple Instances were Found in InstallBase-65');
3051: END IF;
3052: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
3053: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
3054: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
3061: END IF; -- End of Source Record If
3062: END LOOP; -- End of For Loop
3063:
3064: IF (l_debug > 0) THEN
3065: csi_t_gen_utility_pvt.add('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
3066: csi_t_gen_utility_pvt.add('*****End of csi_inv_hz_pkg.misc_issue_hz_loc Transaction procedure*****');
3067: END IF;
3068:
3069: EXCEPTION
3062: END LOOP; -- End of For Loop
3063:
3064: IF (l_debug > 0) THEN
3065: csi_t_gen_utility_pvt.add('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
3066: csi_t_gen_utility_pvt.add('*****End of csi_inv_hz_pkg.misc_issue_hz_loc Transaction procedure*****');
3067: END IF;
3068:
3069: EXCEPTION
3070: WHEN fnd_api.g_exc_error THEN
3068:
3069: EXCEPTION
3070: WHEN fnd_api.g_exc_error THEN
3071: IF (l_debug > 0) THEN
3072: csi_t_gen_utility_pvt.add('You have encountered a "fnd_api.g_exc_error" exception');
3073: END IF;
3074: x_return_status := l_fnd_error;
3075:
3076: IF l_mtl_item_tbl.count > 0 THEN
3096: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
3097:
3098: WHEN others THEN
3099: IF (l_debug > 0) THEN
3100: csi_t_gen_utility_pvt.add('You have encountered a "others" exception');
3101: END IF;
3102: fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
3103: fnd_message.set_token('API_NAME',l_api_name);
3104: fnd_message.set_token('SQL_ERROR',SQLERRM);