[Home] [Help]
1: package body CSI_INV_HZ_PKG as
2: -- $Header: csiivtzb.pls 120.8.12000000.2 2007/07/06 12:20:47 syenaman 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,
156: csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
157: l_depreciable);
158:
159: IF (l_debug > 0) THEN
160: csi_t_gen_utility_pvt.add('Is this Item ID: '||l_mtl_item_tbl(i).inventory_item_id||', Depreciable :'||l_depreciable);
161: END IF;
162:
163: -- Set the mfg_serial_number_flag and quantity
164: IF l_mtl_item_tbl(i).serial_number is NULL THEN
184: l_instance_status := FND_API.G_TRUE;
185: END IF;
186:
187: IF (l_debug > 0) THEN
188: csi_t_gen_utility_pvt.add('Negative Code is - 1 = Yes, 2 = No: '||l_neg_code);
189: END IF;
190:
191: IF (l_debug > 0) THEN
192: csi_t_gen_utility_pvt.add('Starting to loop through Material Transaction Records');
188: csi_t_gen_utility_pvt.add('Negative Code is - 1 = Yes, 2 = No: '||l_neg_code);
189: END IF;
190:
191: IF (l_debug > 0) THEN
192: csi_t_gen_utility_pvt.add('Starting to loop through Material Transaction Records');
193: END IF;
194:
195: -- Get Default Status ID
196: OPEN c_id;
215: END IF;
216: END IF;
217:
218: IF (l_debug > 0) THEN
219: csi_t_gen_utility_pvt.add('Location ID - Location Type; '||l_mtl_item_tbl(i).ship_to_location_id||'-'||l_location_type);
220: END IF;
221:
222: -- Create CSI Transaction to be used
223: l_txn_rec := csi_inv_trxs_pkg.init_txn_rec;
240: l_error_message,
241: l_return_status);
242:
243: IF (l_debug > 0) THEN
244: csi_t_gen_utility_pvt.add('CSI Transaction Created: '||l_txn_rec.transaction_id);
245: END IF;
246:
247: IF NOT l_return_status = l_fnd_success THEN
248: IF (l_debug > 0) THEN
245: END IF;
246:
247: IF NOT l_return_status = l_fnd_success THEN
248: IF (l_debug > 0) THEN
249: csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.create_csi_txn: '||p_transaction_id);
250: END IF;
251: RAISE fnd_api.g_exc_error;
252: END IF;
253:
253:
254: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
255:
256: IF (l_debug > 0) THEN
257: csi_t_gen_utility_pvt.add('Trans Status Code: '||l_txn_rec.transaction_status_code);
258: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
259: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
260: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
261: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
254: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
255:
256: IF (l_debug > 0) THEN
257: csi_t_gen_utility_pvt.add('Trans Status Code: '||l_txn_rec.transaction_status_code);
258: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
259: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
260: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
261: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
262: END IF;
255:
256: IF (l_debug > 0) THEN
257: csi_t_gen_utility_pvt.add('Trans Status Code: '||l_txn_rec.transaction_status_code);
258: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
259: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
260: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
261: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
262: END IF;
263:
256: IF (l_debug > 0) THEN
257: csi_t_gen_utility_pvt.add('Trans Status Code: '||l_txn_rec.transaction_status_code);
258: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
259: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
260: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
261: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
262: END IF;
263:
264: l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
257: csi_t_gen_utility_pvt.add('Trans Status Code: '||l_txn_rec.transaction_status_code);
258: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
259: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
260: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
261: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
262: END IF;
263:
264: l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
265: l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
272: --l_instance_query_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
273: l_instance_query_rec.instance_usage_code := l_in_inventory;
274:
275: IF (l_debug > 0) THEN
276: csi_t_gen_utility_pvt.add('Ship to Location ID: '||l_mtl_item_tbl(j).ship_to_location_id);
277: csi_t_gen_utility_pvt.add('Before Get Item Instance');
278: END IF;
279:
280: csi_item_instance_pub.get_item_instances(l_api_version,
273: l_instance_query_rec.instance_usage_code := l_in_inventory;
274:
275: IF (l_debug > 0) THEN
276: csi_t_gen_utility_pvt.add('Ship to Location ID: '||l_mtl_item_tbl(j).ship_to_location_id);
277: csi_t_gen_utility_pvt.add('Before Get Item Instance');
278: END IF;
279:
280: csi_item_instance_pub.get_item_instances(l_api_version,
281: l_commit,
292: l_msg_count,
293: l_msg_data);
294:
295: IF (l_debug > 0) THEN
296: csi_t_gen_utility_pvt.add('After Get Item Instance');
297: END IF;
298: l_tbl_count := 0;
299: l_tbl_count := l_src_instance_header_tbl.count;
300: IF (l_debug > 0) THEN
297: END IF;
298: l_tbl_count := 0;
299: l_tbl_count := l_src_instance_header_tbl.count;
300: IF (l_debug > 0) THEN
301: csi_t_gen_utility_pvt.add('Source Records Found: '||l_tbl_count);
302: END IF;
303: -- Check for any errors and add them to the message stack to pass out to be put into the
304: -- error log table.
305: IF NOT l_return_status = l_fnd_success then
303: -- Check for any errors and add them to the message stack to pass out to be put into the
304: -- error log table.
305: IF NOT l_return_status = l_fnd_success then
306: IF (l_debug > 0) THEN
307: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
308: END IF;
309: l_msg_index := 1;
310: WHILE l_msg_count > 0 loop
311: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
320: IF l_src_instance_header_tbl.count = 0 THEN
321: IF l_neg_code = 1 THEN -- Allow Neg Qtys on NON Serial Items ONLY
322:
323: IF (l_debug > 0) THEN
324: csi_t_gen_utility_pvt.add('No Source Records are found but negative qtys are allowed so create a negative qty source record');
325: END IF;
326:
327: l_new_src_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
328: l_new_src_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
353: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
354: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
355:
356: IF (l_debug > 0) THEN
357: csi_t_gen_utility_pvt.add('Before Create Source Item Instance');
358: END IF;
359:
360: csi_item_instance_pub.create_item_instance(l_api_version,
361: l_commit,
373: l_msg_count,
374: l_msg_data);
375:
376: IF (l_debug > 0) THEN
377: csi_t_gen_utility_pvt.add('After Create Source Item Instance');
378: END IF;
379:
380: IF (l_debug > 0) THEN
381: csi_t_gen_utility_pvt.add('After Create of Source Item Instance');
377: csi_t_gen_utility_pvt.add('After Create Source Item Instance');
378: END IF;
379:
380: IF (l_debug > 0) THEN
381: csi_t_gen_utility_pvt.add('After Create of Source Item Instance');
382: csi_t_gen_utility_pvt.add('New instance created is: '||l_new_src_instance_rec.instance_id);
383: END IF;
384:
385: -- Check for any errors and add them to the message stack to pass out to be put into the
378: END IF;
379:
380: IF (l_debug > 0) THEN
381: csi_t_gen_utility_pvt.add('After Create of Source Item Instance');
382: csi_t_gen_utility_pvt.add('New instance created is: '||l_new_src_instance_rec.instance_id);
383: END IF;
384:
385: -- Check for any errors and add them to the message stack to pass out to be put into the
386: -- error log table.
385: -- Check for any errors and add them to the message stack to pass out to be put into the
386: -- error log table.
387: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
388: IF (l_debug > 0) THEN
389: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
390: END IF;
391: l_msg_index := 1;
392: WHILE l_msg_count > 0 loop
393: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
398: END IF;
399:
400: ELSE -- No Records were found and Neg Qtys Not Allowed
401: IF (l_debug > 0) THEN
402: csi_t_gen_utility_pvt.add('No Records were found in Install Base and Neg Qtys not allowed to error');
403: END IF;
404: fnd_message.set_name('CSI','CSI_NO_NEG_BAL_ALLOWED');
405: l_error_message := fnd_message.get;
406: RAISE fnd_api.g_exc_error;
409:
410: ELSIF l_src_instance_header_tbl.count = 1 THEN
411:
412: IF (l_debug > 0) THEN
413: csi_t_gen_utility_pvt.add('You will update instance: '||l_src_instance_header_tbl(i).instance_id);
414: END IF;
415:
416: l_update_source_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
417: l_update_source_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
427: 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);
428:
429:
430: IF (l_debug > 0) THEN
431: csi_t_gen_utility_pvt.add('Before Update Item Instance');
432: END IF;
433:
434: csi_item_instance_pub.update_item_instance(l_api_version,
435: l_commit,
451: l_upd_error_instance_id := NULL;
452: l_upd_error_instance_id := l_update_source_instance_rec.instance_id;
453:
454: IF (l_debug > 0) THEN
455: csi_t_gen_utility_pvt.add('After Update Item Instance');
456: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
457: END IF;
458:
459: IF (l_debug > 0) THEN
452: l_upd_error_instance_id := l_update_source_instance_rec.instance_id;
453:
454: IF (l_debug > 0) THEN
455: csi_t_gen_utility_pvt.add('After Update Item Instance');
456: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
457: END IF;
458:
459: IF (l_debug > 0) THEN
460: csi_t_gen_utility_pvt.add('Instance Status Id: '||l_update_source_instance_rec.instance_status_id);
456: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
457: END IF;
458:
459: IF (l_debug > 0) THEN
460: csi_t_gen_utility_pvt.add('Instance Status Id: '||l_update_source_instance_rec.instance_status_id);
461: END IF;
462:
463: -- Check for any errors and add them to the message stack to pass out to be put into the
464: -- error log table.
463: -- Check for any errors and add them to the message stack to pass out to be put into the
464: -- error log table.
465: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
466: IF (l_debug > 0) THEN
467: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
468: END IF;
469: l_msg_index := 1;
470: WHILE l_msg_count > 0 loop
471: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
477:
478: ELSIF l_src_instance_header_tbl.count > 1 THEN
479: -- Multiple Instances were found so throw error
480: IF (l_debug > 0) THEN
481: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
482: END IF;
483: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
484: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
485: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
502: -- l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
503: l_instance_query_rec.instance_usage_code := l_in_service;
504:
505: IF (l_debug > 0) THEN
506: csi_t_gen_utility_pvt.add('Before Destination Get Item Instance - Neg Qty');
507: csi_t_gen_utility_pvt.add('Ship to Location ID: '||l_mtl_item_tbl(j).ship_to_location_id);
508: csi_t_gen_utility_pvt.add('Location Type Code: '||l_location_type);
509: END IF;
510:
503: l_instance_query_rec.instance_usage_code := l_in_service;
504:
505: IF (l_debug > 0) THEN
506: csi_t_gen_utility_pvt.add('Before Destination Get Item Instance - Neg Qty');
507: csi_t_gen_utility_pvt.add('Ship to Location ID: '||l_mtl_item_tbl(j).ship_to_location_id);
508: csi_t_gen_utility_pvt.add('Location Type Code: '||l_location_type);
509: END IF;
510:
511: csi_item_instance_pub.get_item_instances(l_api_version,
504:
505: IF (l_debug > 0) THEN
506: csi_t_gen_utility_pvt.add('Before Destination Get Item Instance - Neg Qty');
507: csi_t_gen_utility_pvt.add('Ship to Location ID: '||l_mtl_item_tbl(j).ship_to_location_id);
508: csi_t_gen_utility_pvt.add('Location Type Code: '||l_location_type);
509: END IF;
510:
511: csi_item_instance_pub.get_item_instances(l_api_version,
512: l_commit,
523: l_msg_count,
524: l_msg_data);
525:
526: IF (l_debug > 0) THEN
527: csi_t_gen_utility_pvt.add('After Destination Get Item Instance');
528: END IF;
529: l_tbl_count := 0;
530: l_tbl_count := l_dest_instance_header_tbl.count;
531: IF (l_debug > 0) THEN
528: END IF;
529: l_tbl_count := 0;
530: l_tbl_count := l_dest_instance_header_tbl.count;
531: IF (l_debug > 0) THEN
532: csi_t_gen_utility_pvt.add('Destination Records Found: '||l_tbl_count);
533: END IF;
534:
535: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
536: IF NOT l_return_status = l_fnd_success then
534:
535: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
536: IF NOT l_return_status = l_fnd_success then
537: IF (l_debug > 0) THEN
538: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
539: END IF;
540: l_msg_index := 1;
541: WHILE l_msg_count > 0 loop
542: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
547: END IF;
548:
549: IF l_dest_instance_header_tbl.count = 0 THEN -- Installed Base Destination Records are not found
550: IF (l_debug > 0) THEN
551: csi_t_gen_utility_pvt.add('No Destination Records were found so we will create a new destination Record using the source data');
552: END IF;
553:
554: l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
555: l_new_dest_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
582: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
583: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
584:
585: IF (l_debug > 0) THEN
586: csi_t_gen_utility_pvt.add('Before Create Item Instance - 45');
587: END IF;
588:
589: csi_item_instance_pub.create_item_instance(l_api_version,
590: l_commit,
602: l_msg_count,
603: l_msg_data);
604:
605: IF (l_debug > 0) THEN
606: csi_t_gen_utility_pvt.add('After Create Item Instance');
607: END IF;
608:
609: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
610: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
608:
609: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
610: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
611: IF (l_debug > 0) THEN
612: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
613: END IF;
614: l_msg_index := 1;
615: WHILE l_msg_count > 0 loop
616: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
622:
623: ELSIF l_dest_instance_header_tbl.count = 1 THEN-- Installed Base Destination Records Found
624:
625: IF (l_debug > 0) THEN
626: csi_t_gen_utility_pvt.add('You will update instance: '||l_dest_instance_header_tbl(i).instance_id);
627: END IF;
628:
629: l_update_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
630: l_update_dest_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
640:
641: 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);
642:
643: IF (l_debug > 0) THEN
644: csi_t_gen_utility_pvt.add('Before Update Item Instance - Neg Qty');
645: END IF;
646:
647: csi_item_instance_pub.update_item_instance(l_api_version,
648: l_commit,
664: l_upd_error_instance_id := NULL;
665: l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
666:
667: IF (l_debug > 0) THEN
668: csi_t_gen_utility_pvt.add('After Update Item Instance - Neg Qty');
669: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
670: END IF;
671:
672: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
665: l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
666:
667: IF (l_debug > 0) THEN
668: csi_t_gen_utility_pvt.add('After Update Item Instance - Neg Qty');
669: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
670: END IF;
671:
672: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
673: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
671:
672: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
673: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
674: IF (l_debug > 0) THEN
675: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
676: END IF;
677: l_msg_index := 1;
678: WHILE l_msg_count > 0 loop
679: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
685:
686: ELSIF l_dest_instance_header_tbl.count > 1 THEN
687: -- Multiple Instances were found so throw error
688: IF (l_debug > 0) THEN
689: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
690: END IF;
691: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
692: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
693: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
700: ELSIF l_mtl_item_tbl(j).serial_number is NOT NULL THEN
701: IF l_src_instance_header_tbl.count = 1 THEN -- Serialized Item
702:
703: IF (l_debug > 0) THEN
704: csi_t_gen_utility_pvt.add('You are updating a Serialized Item');
705: csi_t_gen_utility_pvt.add('Ship to Location ID Serialized: '||l_mtl_item_tbl(j).ship_to_location_id);
706: csi_t_gen_utility_pvt.add('The Transaction Status Code will be - Complete (C) or Incomplete (I): '||l_txn_rec.transaction_status_code);
707: END IF;
708:
701: IF l_src_instance_header_tbl.count = 1 THEN -- Serialized Item
702:
703: IF (l_debug > 0) THEN
704: csi_t_gen_utility_pvt.add('You are updating a Serialized Item');
705: csi_t_gen_utility_pvt.add('Ship to Location ID Serialized: '||l_mtl_item_tbl(j).ship_to_location_id);
706: csi_t_gen_utility_pvt.add('The Transaction Status Code will be - Complete (C) or Incomplete (I): '||l_txn_rec.transaction_status_code);
707: END IF;
708:
709: l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
702:
703: IF (l_debug > 0) THEN
704: csi_t_gen_utility_pvt.add('You are updating a Serialized Item');
705: csi_t_gen_utility_pvt.add('Ship to Location ID Serialized: '||l_mtl_item_tbl(j).ship_to_location_id);
706: csi_t_gen_utility_pvt.add('The Transaction Status Code will be - Complete (C) or Incomplete (I): '||l_txn_rec.transaction_status_code);
707: END IF;
708:
709: l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
710: l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
730: l_org_assignments_tbl.delete;
731: l_asset_assignment_tbl.delete;
732:
733: IF (l_debug > 0) THEN
734: csi_t_gen_utility_pvt.add('Before Update Item Instance');
735: END IF;
736:
737: csi_item_instance_pub.update_item_instance(l_api_version,
738: l_commit,
754: l_upd_error_instance_id := NULL;
755: l_upd_error_instance_id := l_update_instance_rec.instance_id;
756:
757: IF (l_debug > 0) THEN
758: csi_t_gen_utility_pvt.add('After Update Item Instance');
759: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
760: END IF;
761:
762: -- Check for any errors and add them to the message stack to pass out to be put into the
755: l_upd_error_instance_id := l_update_instance_rec.instance_id;
756:
757: IF (l_debug > 0) THEN
758: csi_t_gen_utility_pvt.add('After Update Item Instance');
759: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
760: END IF;
761:
762: -- Check for any errors and add them to the message stack to pass out to be put into the
763: -- error log table.
762: -- Check for any errors and add them to the message stack to pass out to be put into the
763: -- error log table.
764: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
765: IF (l_debug > 0) THEN
766: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
767: END IF;
768: l_msg_index := 1;
769: WHILE l_msg_count > 0 loop
770: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
775: END IF;
776:
777: ELSIF l_src_instance_header_tbl.count = 0 THEN -- Serialized Item
778: IF (l_debug > 0) THEN
779: csi_t_gen_utility_pvt.add('No Records were found in Install Base');
780: END IF;
781: fnd_message.set_name('CSI','CSI_IB_RECORD_NOTFOUND');
782: fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
783: fnd_message.set_token('SUBINVENTORY',l_mtl_item_tbl(j).subinventory_code);
788: ELSIF l_src_instance_header_tbl.count > 1 THEN -- Serialized Item
789:
790: -- Multiple Instances were found so throw error
791: IF (l_debug > 0) THEN
792: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-56');
793: END IF;
794: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
795: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
796: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
803: END IF; -- End of Serial Number count IF
804: END LOOP; -- End of For Loop
805:
806: IF (l_debug > 0) THEN
807: csi_t_gen_utility_pvt.add('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
808: csi_t_gen_utility_pvt.add('*****End of csi_inv_hz_pkg.issue_to_hz_loc Transaction procedure*****');
809: END IF;
810:
811: EXCEPTION
804: END LOOP; -- End of For Loop
805:
806: IF (l_debug > 0) THEN
807: csi_t_gen_utility_pvt.add('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
808: csi_t_gen_utility_pvt.add('*****End of csi_inv_hz_pkg.issue_to_hz_loc Transaction procedure*****');
809: END IF;
810:
811: EXCEPTION
812: WHEN fnd_api.g_exc_error THEN
810:
811: EXCEPTION
812: WHEN fnd_api.g_exc_error THEN
813: IF (l_debug > 0) THEN
814: csi_t_gen_utility_pvt.add('You have encountered a "fnd_api.g_exc_error" exception');
815: END IF;
816: x_return_status := l_fnd_error;
817:
818: IF l_mtl_item_tbl.count > 0 THEN
838: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
839:
840: WHEN others THEN
841: IF (l_debug > 0) THEN
842: csi_t_gen_utility_pvt.add('You have encountered a "others" exception');
843: END IF;
844: fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
845: fnd_message.set_token('API_NAME',l_api_name);
846: fnd_message.set_token('SQL_ERROR',SQLERRM);
971:
972: x_return_status := l_fnd_success;
973:
974: IF (l_debug > 0) THEN
975: csi_t_gen_utility_pvt.add('*****Start of csi_inv_hz_pkg.misc_receipt_hz_loc Transaction procedure*****');
976: csi_t_gen_utility_pvt.add('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
977: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
978: csi_t_gen_utility_pvt.add('csiivtzb.pls 115.14');
979: END IF;
972: x_return_status := l_fnd_success;
973:
974: IF (l_debug > 0) THEN
975: csi_t_gen_utility_pvt.add('*****Start of csi_inv_hz_pkg.misc_receipt_hz_loc Transaction procedure*****');
976: csi_t_gen_utility_pvt.add('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
977: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
978: csi_t_gen_utility_pvt.add('csiivtzb.pls 115.14');
979: END IF;
980:
973:
974: IF (l_debug > 0) THEN
975: csi_t_gen_utility_pvt.add('*****Start of csi_inv_hz_pkg.misc_receipt_hz_loc Transaction procedure*****');
976: csi_t_gen_utility_pvt.add('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
977: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
978: csi_t_gen_utility_pvt.add('csiivtzb.pls 115.14');
979: END IF;
980:
981: -- This procedure queries all of the Inventory Transaction Records
974: IF (l_debug > 0) THEN
975: csi_t_gen_utility_pvt.add('*****Start of csi_inv_hz_pkg.misc_receipt_hz_loc Transaction procedure*****');
976: csi_t_gen_utility_pvt.add('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
977: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
978: csi_t_gen_utility_pvt.add('csiivtzb.pls 115.14');
979: END IF;
980:
981: -- This procedure queries all of the Inventory Transaction Records
982: -- and returns them as a table.
988:
989: l_tbl_count := 0;
990: l_tbl_count := l_mtl_item_tbl.count;
991: IF (l_debug > 0) THEN
992: csi_t_gen_utility_pvt.add('Inventory Records Found: '||l_tbl_count);
993: END IF;
994:
995: IF NOT l_return_status = l_fnd_success THEN
996: IF (l_debug > 0) THEN
993: END IF;
994:
995: IF NOT l_return_status = l_fnd_success THEN
996: IF (l_debug > 0) THEN
997: csi_t_gen_utility_pvt.add('You have encountered an error in CSI_INV_TRXS_PKG.get_transaction_recs, Transaction ID: '||p_transaction_id);
998: END IF;
999: RAISE fnd_api.g_exc_error;
1000: END IF;
1001:
1006: l_error_message);
1007:
1008: IF NOT l_return_status = l_fnd_success THEN
1009: IF (l_debug > 0) THEN
1010: 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);
1011: END IF;
1012: RAISE fnd_api.g_exc_error;
1013: END IF;
1014:
1016: l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
1017:
1018: IF l_employee_id = -1 THEN
1019: IF (l_debug > 0) THEN
1020: 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');
1021: END IF;
1022: END IF;
1023: IF (l_debug > 0) THEN
1024: csi_t_gen_utility_pvt.add('The Employee that is processing this Transaction is: '||l_employee_id);
1020: 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');
1021: END IF;
1022: END IF;
1023: IF (l_debug > 0) THEN
1024: csi_t_gen_utility_pvt.add('The Employee that is processing this Transaction is: '||l_employee_id);
1025: END IF;
1026:
1027: -- See if this is a depreciable Item to set the status of the transaction record
1028: csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
1028: csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
1029: l_depreciable);
1030:
1031: IF (l_debug > 0) THEN
1032: csi_t_gen_utility_pvt.add('Is this Item ID: '||l_mtl_item_tbl(i).inventory_item_id||', Depreciable :'||l_depreciable);
1033: END IF;
1034:
1035: -- Set the quantity
1036: IF l_mtl_item_tbl(i).serial_number IS NULL THEN
1065: --END IF;
1066:
1067: --IF NOT l_return_status = l_fnd_success THEN
1068: -- IF (l_debug > 0) THEN
1069: -- csi_t_gen_utility_pvt.add('Redeploy Flag: '||l_redeploy_flag);
1070: -- csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.get_redeploy_flag: '||l_error_message);
1071: -- END IF;
1072: -- RAISE fnd_api.g_exc_error;
1073: --END IF;
1066:
1067: --IF NOT l_return_status = l_fnd_success THEN
1068: -- IF (l_debug > 0) THEN
1069: -- csi_t_gen_utility_pvt.add('Redeploy Flag: '||l_redeploy_flag);
1070: -- csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.get_redeploy_flag: '||l_error_message);
1071: -- END IF;
1072: -- RAISE fnd_api.g_exc_error;
1073: --END IF;
1074:
1072: -- RAISE fnd_api.g_exc_error;
1073: --END IF;
1074:
1075: IF (l_debug > 0) THEN
1076: csi_t_gen_utility_pvt.add('Redeploy Flag: '||l_redeploy_flag);
1077: csi_t_gen_utility_pvt.add('Trans Status Code: '||l_txn_rec.transaction_status_code);
1078: END IF;
1079:
1080: -- Get Default Status ID
1073: --END IF;
1074:
1075: IF (l_debug > 0) THEN
1076: csi_t_gen_utility_pvt.add('Redeploy Flag: '||l_redeploy_flag);
1077: csi_t_gen_utility_pvt.add('Trans Status Code: '||l_txn_rec.transaction_status_code);
1078: END IF;
1079:
1080: -- Get Default Status ID
1081: OPEN c_id;
1100: END IF;
1101: END IF;
1102:
1103: IF (l_debug > 0) THEN
1104: csi_t_gen_utility_pvt.add('Location ID - Location Type; '||l_mtl_item_tbl(i).ship_to_location_id||'-'||l_location_type);
1105: END IF;
1106:
1107: -- Create CSI Transaction to be used
1108: l_txn_rec.source_transaction_date := l_mtl_item_tbl(i).transaction_date;
1121: l_error_message,
1122: l_return_status);
1123:
1124: IF (l_debug > 0) THEN
1125: csi_t_gen_utility_pvt.add('CSI Transaction Created: '||l_txn_rec.transaction_id);
1126: END IF;
1127:
1128: IF NOT l_return_status = l_fnd_success THEN
1129: IF (l_debug > 0) THEN
1126: END IF;
1127:
1128: IF NOT l_return_status = l_fnd_success THEN
1129: IF (l_debug > 0) THEN
1130: csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.create_csi_txn: '||p_transaction_id);
1131: END IF;
1132: RAISE fnd_api.g_exc_error;
1133: END IF;
1134:
1135: -- Now loop through the PL/SQL Table.
1136: j := 1;
1137:
1138: IF (l_debug > 0) THEN
1139: csi_t_gen_utility_pvt.add('Starting to loop through Material Transaction Records');
1140: END IF;
1141:
1142: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
1143:
1141:
1142: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
1143:
1144: IF (l_debug > 0) THEN
1145: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
1146: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
1147: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
1148: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
1149: END IF;
1142: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
1143:
1144: IF (l_debug > 0) THEN
1145: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
1146: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
1147: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
1148: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
1149: END IF;
1150:
1143:
1144: IF (l_debug > 0) THEN
1145: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
1146: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
1147: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
1148: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
1149: END IF;
1150:
1151: IF l_mtl_item_tbl(j).serial_number IS NOT NULL THEN -- Serialized
1144: IF (l_debug > 0) THEN
1145: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
1146: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
1147: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
1148: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
1149: END IF;
1150:
1151: IF l_mtl_item_tbl(j).serial_number IS NOT NULL THEN -- Serialized
1152:
1159: l_instance_query_rec.serial_number := l_mtl_item_tbl(j).serial_number;
1160: --l_instance_query_rec.instance_usage_code := l_out_of_service;
1161:
1162: IF (l_debug > 0) THEN
1163: csi_t_gen_utility_pvt.add('Before Get Item Instance');
1164: csi_t_gen_utility_pvt.add('Ship to Location ID: '||l_mtl_item_tbl(j).ship_to_location_id);
1165: END IF;
1166:
1167: csi_item_instance_pub.get_item_instances(l_api_version,
1160: --l_instance_query_rec.instance_usage_code := l_out_of_service;
1161:
1162: IF (l_debug > 0) THEN
1163: csi_t_gen_utility_pvt.add('Before Get Item Instance');
1164: csi_t_gen_utility_pvt.add('Ship to Location ID: '||l_mtl_item_tbl(j).ship_to_location_id);
1165: END IF;
1166:
1167: csi_item_instance_pub.get_item_instances(l_api_version,
1168: l_commit,
1179: l_msg_count,
1180: l_msg_data);
1181:
1182: IF (l_debug > 0) THEN
1183: csi_t_gen_utility_pvt.add('After Get Item Instance');
1184: END IF;
1185: l_tbl_count := 0;
1186: l_tbl_count := l_src_instance_header_tbl.count;
1187: IF (l_debug > 0) THEN
1184: END IF;
1185: l_tbl_count := 0;
1186: l_tbl_count := l_src_instance_header_tbl.count;
1187: IF (l_debug > 0) THEN
1188: csi_t_gen_utility_pvt.add('Source Records Found: '||l_tbl_count);
1189: END IF;
1190:
1191: -- Check for any errors and add them to the message stack to pass out to be put into the
1192: -- error log table.
1191: -- Check for any errors and add them to the message stack to pass out to be put into the
1192: -- error log table.
1193: IF NOT l_return_status = l_fnd_success then
1194: IF (l_debug > 0) THEN
1195: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
1196: END IF;
1197: l_msg_index := 1;
1198: WHILE l_msg_count > 0 loop
1199: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1211: l_in_service,
1212: l_in_process) THEN
1213:
1214: IF (l_debug > 0) THEN
1215: csi_t_gen_utility_pvt.add('Source records found so decide which one to update using get_destination_instance procedure ');
1216: END IF;
1217:
1218: l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1219: l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
1241: l_org_assignments_tbl.delete;
1242: l_asset_assignment_tbl.delete;
1243:
1244: IF (l_debug > 0) THEN
1245: csi_t_gen_utility_pvt.add('Before Update Item Instance');
1246: END IF;
1247:
1248: csi_item_instance_pub.update_item_instance(l_api_version,
1249: l_commit,
1265: l_upd_error_instance_id := NULL;
1266: l_upd_error_instance_id := l_update_instance_rec.instance_id;
1267:
1268: IF (l_debug > 0) THEN
1269: csi_t_gen_utility_pvt.add('After Update Item Instance');
1270: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1271: END IF;
1272:
1273:
1266: l_upd_error_instance_id := l_update_instance_rec.instance_id;
1267:
1268: IF (l_debug > 0) THEN
1269: csi_t_gen_utility_pvt.add('After Update Item Instance');
1270: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1271: END IF;
1272:
1273:
1274: -- Check for any errors and add them to the message stack to pass out to be put into the
1274: -- Check for any errors and add them to the message stack to pass out to be put into the
1275: -- error log table.
1276: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1277: IF (l_debug > 0) THEN
1278: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
1279: END IF;
1280: l_msg_index := 1;
1281: WHILE l_msg_count > 0 loop
1282: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1287: END IF;
1288: ELSE
1289: l_status := 'In Inventory, Out of Service, Installed, In Service or In Process';
1290: IF (l_debug > 0) THEN
1291: 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');
1292: csi_t_gen_utility_pvt.add('Instance Usage Code is: '||l_src_instance_header_tbl(i).instance_usage_code);
1293: END IF;
1294: fnd_message.set_name('CSI','CSI_SERIALIZED_ITEM_EXISTS');
1295: fnd_message.set_token('STATUS',l_status);
1288: ELSE
1289: l_status := 'In Inventory, Out of Service, Installed, In Service or In Process';
1290: IF (l_debug > 0) THEN
1291: 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');
1292: csi_t_gen_utility_pvt.add('Instance Usage Code is: '||l_src_instance_header_tbl(i).instance_usage_code);
1293: END IF;
1294: fnd_message.set_name('CSI','CSI_SERIALIZED_ITEM_EXISTS');
1295: fnd_message.set_token('STATUS',l_status);
1296: l_error_message := fnd_message.get;
1298: RAISE fnd_api.g_exc_error;
1299: END IF;
1300:
1301: ELSIF l_src_instance_header_tbl.count = 0 THEN -- No IB Records found
1302: 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');
1303:
1304: l_new_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
1305: l_new_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1306: l_new_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1331: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
1332:
1333: 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);
1334:
1335: csi_t_gen_utility_pvt.add('Instance_status_id Value: '||nvl(l_new_instance_rec.instance_status_id,-1));
1336: csi_t_gen_utility_pvt.add('You will now Create a new Item Instance Record');
1337: csi_t_gen_utility_pvt.add('Serial Number: '||l_new_instance_rec.serial_number);
1338:
1339: csi_item_instance_pub.create_item_instance(l_api_version,
1332:
1333: 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);
1334:
1335: csi_t_gen_utility_pvt.add('Instance_status_id Value: '||nvl(l_new_instance_rec.instance_status_id,-1));
1336: csi_t_gen_utility_pvt.add('You will now Create a new Item Instance Record');
1337: csi_t_gen_utility_pvt.add('Serial Number: '||l_new_instance_rec.serial_number);
1338:
1339: csi_item_instance_pub.create_item_instance(l_api_version,
1340: l_commit,
1333: 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);
1334:
1335: csi_t_gen_utility_pvt.add('Instance_status_id Value: '||nvl(l_new_instance_rec.instance_status_id,-1));
1336: csi_t_gen_utility_pvt.add('You will now Create a new Item Instance Record');
1337: csi_t_gen_utility_pvt.add('Serial Number: '||l_new_instance_rec.serial_number);
1338:
1339: csi_item_instance_pub.create_item_instance(l_api_version,
1340: l_commit,
1341: l_init_msg_list,
1354:
1355: -- Check for any errors and add them to the message stack to pass out to be put into the
1356: -- error log table.
1357: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1358: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
1359: l_msg_index := 1;
1360: WHILE l_msg_count > 0 loop
1361: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1362: l_msg_index := l_msg_index + 1;
1364: END LOOP;
1365: RAISE fnd_api.g_exc_error;
1366: END IF;
1367:
1368: csi_t_gen_utility_pvt.add('Item Instance Created: '||l_new_instance_rec.instance_id);
1369:
1370: ELSIF l_src_instance_header_tbl.count > 1 THEN -- Multiple Instances Found
1371: -- Multiple Instances were found so throw error
1372: IF (l_debug > 0) THEN
1369:
1370: ELSIF l_src_instance_header_tbl.count > 1 THEN -- Multiple Instances Found
1371: -- Multiple Instances were found so throw error
1372: IF (l_debug > 0) THEN
1373: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
1374: END IF;
1375: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
1376: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
1377: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
1395: l_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1396: --l_instance_query_rec.instance_usage_code := l_out_of_service;
1397:
1398: IF (l_debug > 0) THEN
1399: csi_t_gen_utility_pvt.add('Before Get Item Instance');
1400: csi_t_gen_utility_pvt.add('Ship to Location ID Non Ser: '||l_mtl_item_tbl(j).ship_to_location_id);
1401: csi_t_gen_utility_pvt.add('Location Type Code: '||l_location_type);
1402: END IF;
1403:
1396: --l_instance_query_rec.instance_usage_code := l_out_of_service;
1397:
1398: IF (l_debug > 0) THEN
1399: csi_t_gen_utility_pvt.add('Before Get Item Instance');
1400: csi_t_gen_utility_pvt.add('Ship to Location ID Non Ser: '||l_mtl_item_tbl(j).ship_to_location_id);
1401: csi_t_gen_utility_pvt.add('Location Type Code: '||l_location_type);
1402: END IF;
1403:
1404: csi_item_instance_pub.get_item_instances(l_api_version,
1397:
1398: IF (l_debug > 0) THEN
1399: csi_t_gen_utility_pvt.add('Before Get Item Instance');
1400: csi_t_gen_utility_pvt.add('Ship to Location ID Non Ser: '||l_mtl_item_tbl(j).ship_to_location_id);
1401: csi_t_gen_utility_pvt.add('Location Type Code: '||l_location_type);
1402: END IF;
1403:
1404: csi_item_instance_pub.get_item_instances(l_api_version,
1405: l_commit,
1416: l_msg_count,
1417: l_msg_data);
1418:
1419: IF (l_debug > 0) THEN
1420: csi_t_gen_utility_pvt.add('After Get Item Instance');
1421: END IF;
1422: l_tbl_count := 0;
1423: l_tbl_count := l_src_instance_header_tbl.count;
1424: IF (l_debug > 0) THEN
1421: END IF;
1422: l_tbl_count := 0;
1423: l_tbl_count := l_src_instance_header_tbl.count;
1424: IF (l_debug > 0) THEN
1425: csi_t_gen_utility_pvt.add('Source Records Found: '||l_tbl_count);
1426: END IF;
1427:
1428: -- Check for any errors and add them to the message stack to pass out to be put into the
1429: -- error log table.
1428: -- Check for any errors and add them to the message stack to pass out to be put into the
1429: -- error log table.
1430: IF NOT l_return_status = l_fnd_success then
1431: IF (l_debug > 0) THEN
1432: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
1433: END IF;
1434: l_msg_index := 1;
1435: WHILE l_msg_count > 0 loop
1436: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1443: l_bypass_qty := 'N';
1444:
1445: IF l_src_instance_header_tbl.count > 0 THEN -- Records found so update either Serialized or Non Serialized
1446:
1447: csi_t_gen_utility_pvt.add('Since this is Non Serial decide what instances to decrement');
1448:
1449: FOR oos IN l_src_instance_header_tbl.first .. l_src_instance_header_tbl.last LOOP
1450:
1451: IF l_src_instance_header_tbl(oos).instance_usage_code = 'OUT_OF_SERVICE' THEN
1449: FOR oos IN l_src_instance_header_tbl.first .. l_src_instance_header_tbl.last LOOP
1450:
1451: IF l_src_instance_header_tbl(oos).instance_usage_code = 'OUT_OF_SERVICE' THEN
1452:
1453: csi_t_gen_utility_pvt.add('Out of Service Instance found: '||l_src_instance_header_tbl(oos).instance_id);
1454:
1455: l_oos_found := 'Y';
1456:
1457: IF l_src_instance_header_tbl(oos).quantity >= abs(l_mtl_item_tbl(j).primary_quantity) THEN
1454:
1455: l_oos_found := 'Y';
1456:
1457: IF l_src_instance_header_tbl(oos).quantity >= abs(l_mtl_item_tbl(j).primary_quantity) THEN
1458: csi_t_gen_utility_pvt.add('Instance found has more available quantity (or Equal) then the current quantity so subtract the entire primary quantity');
1459: l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1460: l_upd_src_dest_instance_rec.instance_id := l_src_instance_header_tbl(oos).instance_id;
1461: l_upd_src_dest_instance_rec.quantity := l_src_instance_header_tbl(oos).quantity - abs(l_mtl_item_tbl(j).primary_quantity);
1462: l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(oos).object_version_number;
1462: l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(oos).object_version_number;
1463: l_bypass_qty := 'Y';
1464:
1465: ELSIF l_src_instance_header_tbl(oos).quantity < abs(l_mtl_item_tbl(j).primary_quantity) THEN
1466: csi_t_gen_utility_pvt.add('Instance found has less available quantity then the current quantity so set the quantity to 0');
1467: l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1468: l_upd_src_dest_instance_rec.instance_id := l_src_instance_header_tbl(oos).instance_id;
1469: l_upd_src_dest_instance_rec.quantity := 0;
1470: l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(oos).object_version_number;
1473:
1474: END IF;
1475:
1476:
1477: csi_t_gen_utility_pvt.add('Current Quantity is: '||l_curr_quantity);
1478:
1479: l_party_tbl.delete;
1480: l_account_tbl.delete;
1481: l_pricing_attrib_tbl.delete;
1482: l_org_assignments_tbl.delete;
1483: l_asset_assignment_tbl.delete;
1484:
1485: IF (l_debug > 0) THEN
1486: csi_t_gen_utility_pvt.add('Before Update Item Instance: '||l_src_instance_header_tbl(oos).instance_id);
1487: END IF;
1488:
1489: csi_item_instance_pub.update_item_instance(l_api_version,
1490: l_commit,
1506: l_upd_error_instance_id := NULL;
1507: l_upd_error_instance_id := l_upd_src_dest_instance_rec.instance_id;
1508:
1509: IF (l_debug > 0) THEN
1510: csi_t_gen_utility_pvt.add('After Update Item Instance');
1511: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1512: END IF;
1513:
1514: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1507: l_upd_error_instance_id := l_upd_src_dest_instance_rec.instance_id;
1508:
1509: IF (l_debug > 0) THEN
1510: csi_t_gen_utility_pvt.add('After Update Item Instance');
1511: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1512: END IF;
1513:
1514: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1515: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1513:
1514: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1515: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1516: IF (l_debug > 0) THEN
1517: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
1518: END IF;
1519: l_msg_index := 1;
1520: WHILE l_msg_count > 0 loop
1521: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1535: FOR ins IN l_src_instance_header_tbl.first .. l_src_instance_header_tbl.last LOOP
1536:
1537: IF l_src_instance_header_tbl(ins).instance_usage_code = 'INSTALLED' THEN
1538:
1539: csi_t_gen_utility_pvt.add('Installed Instance found: '||l_src_instance_header_tbl(ins).instance_id);
1540:
1541: l_ins_found := 'Y';
1542:
1543: IF l_oos_found = 'N' THEN
1540:
1541: l_ins_found := 'Y';
1542:
1543: IF l_oos_found = 'N' THEN
1544: csi_t_gen_utility_pvt.add('Out of Service Instance not found so setting current qty to be the primary quantity');
1545: l_curr_quantity := abs(l_mtl_item_tbl(j).primary_quantity);
1546: END IF;
1547:
1548: IF l_src_instance_header_tbl(ins).quantity >= l_curr_quantity THEN
1545: l_curr_quantity := abs(l_mtl_item_tbl(j).primary_quantity);
1546: END IF;
1547:
1548: IF l_src_instance_header_tbl(ins).quantity >= l_curr_quantity THEN
1549: csi_t_gen_utility_pvt.add('Instance found has more available quantity (or Equal) then the current quantity so subtract the entire primary quantity');
1550: l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1551: l_upd_src_dest_instance_rec.instance_id := l_src_instance_header_tbl(ins).instance_id;
1552: l_upd_src_dest_instance_rec.quantity := l_src_instance_header_tbl(ins).quantity - l_curr_quantity;
1553: l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(ins).object_version_number;
1553: l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(ins).object_version_number;
1554: l_bypass_qty := 'Y';
1555:
1556: ELSIF l_src_instance_header_tbl(ins).quantity < l_curr_quantity THEN
1557: csi_t_gen_utility_pvt.add('Instance found has less available quantity then the current quantity so set the quantity to 0');
1558: l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1559: l_upd_src_dest_instance_rec.instance_id := l_src_instance_header_tbl(ins).instance_id;
1560: l_upd_src_dest_instance_rec.quantity := 0;
1561: l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(ins).object_version_number;
1563: l_curr_quantity := l_curr_quantity - l_src_instance_header_tbl(ins).quantity;
1564:
1565: END IF;
1566:
1567: csi_t_gen_utility_pvt.add('Current Quantity is: '||l_curr_quantity);
1568:
1569: l_party_tbl.delete;
1570: l_account_tbl.delete;
1571: l_pricing_attrib_tbl.delete;
1572: l_org_assignments_tbl.delete;
1573: l_asset_assignment_tbl.delete;
1574:
1575: IF (l_debug > 0) THEN
1576: csi_t_gen_utility_pvt.add('Before Update Item Instance: '||l_src_instance_header_tbl(ins).instance_id);
1577: END IF;
1578:
1579: csi_item_instance_pub.update_item_instance(l_api_version,
1580: l_commit,
1596: l_upd_error_instance_id := NULL;
1597: l_upd_error_instance_id := l_upd_src_dest_instance_rec.instance_id;
1598:
1599: IF (l_debug > 0) THEN
1600: csi_t_gen_utility_pvt.add('After Update Item Instance');
1601: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1602: END IF;
1603:
1604: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1597: l_upd_error_instance_id := l_upd_src_dest_instance_rec.instance_id;
1598:
1599: IF (l_debug > 0) THEN
1600: csi_t_gen_utility_pvt.add('After Update Item Instance');
1601: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1602: END IF;
1603:
1604: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1605: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1603:
1604: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1605: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1606: IF (l_debug > 0) THEN
1607: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
1608: END IF;
1609: l_msg_index := 1;
1610: WHILE l_msg_count > 0 loop
1611: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1627: FOR inser IN l_src_instance_header_tbl.first .. l_src_instance_header_tbl.last LOOP
1628:
1629: IF l_src_instance_header_tbl(inser).instance_usage_code = 'IN_SERVICE' THEN
1630:
1631: csi_t_gen_utility_pvt.add('In Service Instance found: '|| l_src_instance_header_tbl(inser).instance_id);
1632:
1633: IF l_oos_found = 'N' AND l_ins_found = 'N' THEN
1634: csi_t_gen_utility_pvt.add('Out of Service or Installed Instance not found so setting current qty to be the primary quantity');
1635: l_curr_quantity := abs(l_mtl_item_tbl(j).primary_quantity);
1630:
1631: csi_t_gen_utility_pvt.add('In Service Instance found: '|| l_src_instance_header_tbl(inser).instance_id);
1632:
1633: IF l_oos_found = 'N' AND l_ins_found = 'N' THEN
1634: csi_t_gen_utility_pvt.add('Out of Service or Installed Instance not found so setting current qty to be the primary quantity');
1635: l_curr_quantity := abs(l_mtl_item_tbl(j).primary_quantity);
1636: END IF;
1637:
1638: IF l_src_instance_header_tbl(inser).quantity >= l_curr_quantity THEN
1635: l_curr_quantity := abs(l_mtl_item_tbl(j).primary_quantity);
1636: END IF;
1637:
1638: IF l_src_instance_header_tbl(inser).quantity >= l_curr_quantity THEN
1639: csi_t_gen_utility_pvt.add('Instance found has more available quantity (or Equal) then the current quantity so subtract the entire primary quantity');
1640: l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1641: l_upd_src_dest_instance_rec.instance_id := l_src_instance_header_tbl(inser).instance_id;
1642: l_upd_src_dest_instance_rec.quantity := l_src_instance_header_tbl(inser).quantity - l_curr_quantity;
1643: l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(inser).object_version_number;
1643: l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(inser).object_version_number;
1644: l_bypass_qty := 'Y';
1645:
1646: ELSIF l_src_instance_header_tbl(inser).quantity < l_curr_quantity THEN
1647: csi_t_gen_utility_pvt.add('Instance found has less available quantity then the current quantity so set the quantity to 0');
1648: l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1649: l_upd_src_dest_instance_rec.instance_id := l_src_instance_header_tbl(inser).instance_id;
1650: l_upd_src_dest_instance_rec.quantity := 0;
1651: l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(inser).object_version_number;
1653: l_curr_quantity := l_curr_quantity - l_src_instance_header_tbl(inser).quantity;
1654:
1655: END IF;
1656:
1657: csi_t_gen_utility_pvt.add('Current Quantity is: '||l_curr_quantity);
1658:
1659: l_party_tbl.delete;
1660: l_account_tbl.delete;
1661: l_pricing_attrib_tbl.delete;
1662: l_org_assignments_tbl.delete;
1663: l_asset_assignment_tbl.delete;
1664:
1665: IF (l_debug > 0) THEN
1666: csi_t_gen_utility_pvt.add('Before Update Item Instance: '||l_src_instance_header_tbl(inser).instance_id);
1667: END IF;
1668:
1669: csi_item_instance_pub.update_item_instance(l_api_version,
1670: l_commit,
1686: l_upd_error_instance_id := NULL;
1687: l_upd_error_instance_id := l_upd_src_dest_instance_rec.instance_id;
1688:
1689: IF (l_debug > 0) THEN
1690: csi_t_gen_utility_pvt.add('After Update Item Instance');
1691: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1692: END IF;
1693:
1694: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1687: l_upd_error_instance_id := l_upd_src_dest_instance_rec.instance_id;
1688:
1689: IF (l_debug > 0) THEN
1690: csi_t_gen_utility_pvt.add('After Update Item Instance');
1691: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1692: END IF;
1693:
1694: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1695: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1693:
1694: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1695: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1696: IF (l_debug > 0) THEN
1697: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
1698: END IF;
1699: l_msg_index := 1;
1700: WHILE l_msg_count > 0 loop
1701: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1720: l_instance_dest_query_rec.serial_number := NULL;
1721: l_instance_dest_query_rec.instance_usage_code := l_in_inventory;
1722:
1723: IF (l_debug > 0) THEN
1724: csi_t_gen_utility_pvt.add('Before Get Item Instance');
1725: END IF;
1726:
1727: csi_item_instance_pub.get_item_instances(l_api_version,
1728: l_commit,
1739: l_msg_count,
1740: l_msg_data);
1741:
1742: IF (l_debug > 0) THEN
1743: csi_t_gen_utility_pvt.add('After Get Item Instance');
1744: END IF;
1745: l_tbl_count := 0;
1746: l_tbl_count := l_dest_instance_header_tbl.count;
1747: IF (l_debug > 0) THEN
1744: END IF;
1745: l_tbl_count := 0;
1746: l_tbl_count := l_dest_instance_header_tbl.count;
1747: IF (l_debug > 0) THEN
1748: csi_t_gen_utility_pvt.add('Destination Records Found: '||l_tbl_count);
1749: END IF;
1750:
1751: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1752: IF NOT l_return_status = l_fnd_success then
1750:
1751: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1752: IF NOT l_return_status = l_fnd_success then
1753: IF (l_debug > 0) THEN
1754: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
1755: END IF;
1756: l_msg_index := 1;
1757: WHILE l_msg_count > 0 loop
1758: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1795:
1796: 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);
1797:
1798: IF (l_debug > 0) THEN
1799: csi_t_gen_utility_pvt.add('Before Create Item Instance');
1800: END IF;
1801:
1802: csi_item_instance_pub.create_item_instance(l_api_version,
1803: l_commit,
1815: l_msg_count,
1816: l_msg_data);
1817:
1818: IF (l_debug > 0) THEN
1819: csi_t_gen_utility_pvt.add('After Update Item Instance');
1820: END IF;
1821:
1822: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1823: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1821:
1822: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1823: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1824: IF (l_debug > 0) THEN
1825: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
1826: END IF;
1827: l_msg_index := 1;
1828: WHILE l_msg_count > 0 loop
1829: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1849:
1850: 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);
1851:
1852: IF (l_debug > 0) THEN
1853: csi_t_gen_utility_pvt.add('Before Update Item Instance');
1854: END IF;
1855:
1856: csi_item_instance_pub.update_item_instance(l_api_version,
1857: l_commit,
1873: l_upd_error_instance_id := NULL;
1874: l_upd_error_instance_id := l_update_instance_rec.instance_id;
1875:
1876: IF (l_debug > 0) THEN
1877: csi_t_gen_utility_pvt.add('After Update Item Instance');
1878: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1879: END IF;
1880:
1881: -- Check for any errors and add them to the message stack to pass out to be put into the
1874: l_upd_error_instance_id := l_update_instance_rec.instance_id;
1875:
1876: IF (l_debug > 0) THEN
1877: csi_t_gen_utility_pvt.add('After Update Item Instance');
1878: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1879: END IF;
1880:
1881: -- Check for any errors and add them to the message stack to pass out to be put into the
1882: -- error log table.
1881: -- Check for any errors and add them to the message stack to pass out to be put into the
1882: -- error log table.
1883: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1884: IF (l_debug > 0) THEN
1885: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
1886: END IF;
1887: l_msg_index := 1;
1888: WHILE l_msg_count > 0 loop
1889: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1896: ELSIF l_dest_instance_header_tbl.count > 1 THEN
1897:
1898: -- Multiple Instances were found so throw error
1899: IF (l_debug > 0) THEN
1900: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
1901: END IF;
1902: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
1903: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
1904: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
1911: /***
1912: ELSIF l_src_instance_header_tbl.count > 1 THEN
1913: -- Multiple Instances were found so throw error
1914: IF (l_debug > 0) THEN
1915: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
1916: END IF;
1917: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
1918: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
1919: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
1923: RAISE fnd_api.g_exc_error;
1924: ***/
1925: ELSIF l_src_instance_header_tbl.count = 0 THEN -- No IB Records found
1926:
1927: 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');
1928:
1929: l_instance_dest_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
1930: l_instance_dest_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1931: l_instance_dest_query_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
1936: l_instance_dest_query_rec.serial_number := NULL;
1937: l_instance_dest_query_rec.instance_usage_code := l_in_inventory;
1938:
1939: IF (l_debug > 0) THEN
1940: csi_t_gen_utility_pvt.add('Before Get Item Instance');
1941: END IF;
1942:
1943: csi_item_instance_pub.get_item_instances(l_api_version,
1944: l_commit,
1955: l_msg_count,
1956: l_msg_data);
1957:
1958: IF (l_debug > 0) THEN
1959: csi_t_gen_utility_pvt.add('After Get Item Instance');
1960: END IF;
1961: l_tbl_count := 0;
1962: l_tbl_count := l_dest_instance_header_tbl.count;
1963: IF (l_debug > 0) THEN
1960: END IF;
1961: l_tbl_count := 0;
1962: l_tbl_count := l_dest_instance_header_tbl.count;
1963: IF (l_debug > 0) THEN
1964: csi_t_gen_utility_pvt.add('Destination Records Found: '||l_tbl_count);
1965: END IF;
1966:
1967: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1968: IF NOT l_return_status = l_fnd_success then
1966:
1967: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1968: IF NOT l_return_status = l_fnd_success then
1969: IF (l_debug > 0) THEN
1970: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
1971: END IF;
1972: l_msg_index := 1;
1973: WHILE l_msg_count > 0 loop
1974: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2011:
2012: 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);
2013:
2014: IF (l_debug > 0) THEN
2015: csi_t_gen_utility_pvt.add('Before Create Item Instance');
2016: END IF;
2017:
2018: csi_item_instance_pub.create_item_instance(l_api_version,
2019: l_commit,
2031: l_msg_count,
2032: l_msg_data);
2033:
2034: IF (l_debug > 0) THEN
2035: csi_t_gen_utility_pvt.add('After Update Item Instance');
2036: END IF;
2037:
2038: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2039: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2037:
2038: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2039: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2040: IF (l_debug > 0) THEN
2041: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
2042: END IF;
2043: l_msg_index := 1;
2044: WHILE l_msg_count > 0 loop
2045: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2065:
2066: 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);
2067:
2068: IF (l_debug > 0) THEN
2069: csi_t_gen_utility_pvt.add('Before Update Item Instance');
2070: END IF;
2071:
2072: csi_item_instance_pub.update_item_instance(l_api_version,
2073: l_commit,
2089: l_upd_error_instance_id := NULL;
2090: l_upd_error_instance_id := l_update_instance_rec.instance_id;
2091:
2092: IF (l_debug > 0) THEN
2093: csi_t_gen_utility_pvt.add('After Update Item Instance');
2094: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2095: END IF;
2096:
2097: -- Check for any errors and add them to the message stack to pass out to be put into the
2090: l_upd_error_instance_id := l_update_instance_rec.instance_id;
2091:
2092: IF (l_debug > 0) THEN
2093: csi_t_gen_utility_pvt.add('After Update Item Instance');
2094: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2095: END IF;
2096:
2097: -- Check for any errors and add them to the message stack to pass out to be put into the
2098: -- error log table.
2097: -- Check for any errors and add them to the message stack to pass out to be put into the
2098: -- error log table.
2099: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2100: IF (l_debug > 0) THEN
2101: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
2102: END IF;
2103: l_msg_index := 1;
2104: WHILE l_msg_count > 0 loop
2105: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2111:
2112: ELSIF l_src_instance_header_tbl.count > 1 THEN
2113: -- Multiple Instances were found so throw error
2114: IF (l_debug > 0) THEN
2115: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
2116: END IF;
2117: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
2118: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
2119: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
2127: END IF; -- End of Serial Number If
2128: END LOOP; -- End of For Loop
2129:
2130: IF (l_debug > 0) THEN
2131: csi_t_gen_utility_pvt.add('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2132: csi_t_gen_utility_pvt.add('*****End of csi_inv_hz_pkg.misc_receipt_hz_loc Transaction procedure*****');
2133: END IF;
2134:
2135: EXCEPTION
2128: END LOOP; -- End of For Loop
2129:
2130: IF (l_debug > 0) THEN
2131: csi_t_gen_utility_pvt.add('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2132: csi_t_gen_utility_pvt.add('*****End of csi_inv_hz_pkg.misc_receipt_hz_loc Transaction procedure*****');
2133: END IF;
2134:
2135: EXCEPTION
2136: WHEN fnd_api.g_exc_error THEN
2134:
2135: EXCEPTION
2136: WHEN fnd_api.g_exc_error THEN
2137: IF (l_debug > 0) THEN
2138: csi_t_gen_utility_pvt.add('You have encountered a "fnd_api.g_exc_error" exception');
2139: END IF;
2140: x_return_status := l_fnd_error;
2141: x_trx_error_rec.error_text := l_error_message;
2142:
2162: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
2163:
2164: WHEN others THEN
2165: IF (l_debug > 0) THEN
2166: csi_t_gen_utility_pvt.add('You have encountered a "others" exception');
2167: END IF;
2168: fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
2169: fnd_message.set_token('API_NAME',l_api_name);
2170: fnd_message.set_token('SQL_ERROR',SQLERRM);
2290:
2291: x_return_status := l_fnd_success;
2292:
2293: IF (l_debug > 0) THEN
2294: csi_t_gen_utility_pvt.add('*****Start of csi_inv_hz_pkg.misc_issue_hz_loc Transaction procedure*****');
2295: csi_t_gen_utility_pvt.add('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2296: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
2297: csi_t_gen_utility_pvt.add('csiivtzb.pls 115.14');
2298: END IF;
2291: x_return_status := l_fnd_success;
2292:
2293: IF (l_debug > 0) THEN
2294: csi_t_gen_utility_pvt.add('*****Start of csi_inv_hz_pkg.misc_issue_hz_loc Transaction procedure*****');
2295: csi_t_gen_utility_pvt.add('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2296: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
2297: csi_t_gen_utility_pvt.add('csiivtzb.pls 115.14');
2298: END IF;
2299:
2292:
2293: IF (l_debug > 0) THEN
2294: csi_t_gen_utility_pvt.add('*****Start of csi_inv_hz_pkg.misc_issue_hz_loc Transaction procedure*****');
2295: csi_t_gen_utility_pvt.add('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2296: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
2297: csi_t_gen_utility_pvt.add('csiivtzb.pls 115.14');
2298: END IF;
2299:
2300: -- This procedure queries all of the Inventory Transaction Records
2293: IF (l_debug > 0) THEN
2294: csi_t_gen_utility_pvt.add('*****Start of csi_inv_hz_pkg.misc_issue_hz_loc Transaction procedure*****');
2295: csi_t_gen_utility_pvt.add('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2296: csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
2297: csi_t_gen_utility_pvt.add('csiivtzb.pls 115.14');
2298: END IF;
2299:
2300: -- This procedure queries all of the Inventory Transaction Records
2301: -- and returns them as a table.
2307:
2308: l_tbl_count := 0;
2309: l_tbl_count := l_mtl_item_tbl.count;
2310: IF (l_debug > 0) THEN
2311: csi_t_gen_utility_pvt.add('Inventory Records Found: '||l_tbl_count);
2312: END IF;
2313:
2314: IF NOT l_return_status = l_fnd_success THEN
2315: IF (l_debug > 0) THEN
2312: END IF;
2313:
2314: IF NOT l_return_status = l_fnd_success THEN
2315: IF (l_debug > 0) THEN
2316: csi_t_gen_utility_pvt.add('You have encountered an error in CSI_INV_TRXS_PKG.get_transaction_recs, Transaction ID: '||p_transaction_id);
2317: END IF;
2318: RAISE fnd_api.g_exc_error;
2319: END IF;
2320:
2325: l_error_message);
2326:
2327: IF NOT l_return_status = l_fnd_success THEN
2328: IF (l_debug > 0) THEN
2329: 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);
2330: END IF;
2331: RAISE fnd_api.g_exc_error;
2332: END IF;
2333:
2335: l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
2336:
2337: IF l_employee_id = -1 THEN
2338: IF (l_debug > 0) THEN
2339: 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');
2340: END IF;
2341: END IF;
2342: IF (l_debug > 0) THEN
2343: csi_t_gen_utility_pvt.add('The Employee that is processing this Transaction is: '||l_employee_id);
2339: 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');
2340: END IF;
2341: END IF;
2342: IF (l_debug > 0) THEN
2343: csi_t_gen_utility_pvt.add('The Employee that is processing this Transaction is: '||l_employee_id);
2344: END IF;
2345:
2346: -- See if this is a depreciable Item to set the status of the transaction record
2347: csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
2347: csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
2348: l_depreciable);
2349:
2350: IF (l_debug > 0) THEN
2351: csi_t_gen_utility_pvt.add('Is this Item ID: '||l_mtl_item_tbl(i).inventory_item_id||', Depreciable :'||l_depreciable);
2352: END IF;
2353:
2354:
2355: -- Set the mfg_serial_number_flag and quantity
2373: l_instance_status := FND_API.G_TRUE;
2374: END IF;
2375:
2376: IF (l_debug > 0) THEN
2377: csi_t_gen_utility_pvt.add('Negative Code is - 1 = Yes, 2 = No: '||l_neg_code);
2378: END IF;
2379:
2380: -- Get Instance Status ID
2381: OPEN c_id;
2400: END IF;
2401: END IF;
2402:
2403: IF (l_debug > 0) THEN
2404: csi_t_gen_utility_pvt.add('Location ID - Location Type; '||l_mtl_item_tbl(i).ship_to_location_id||'-'||l_location_type);
2405: END IF;
2406:
2407: -- Create CSI Transaction to be used
2408: l_txn_rec := csi_inv_trxs_pkg.init_txn_rec;
2423: l_error_message,
2424: l_return_status);
2425:
2426: IF (l_debug > 0) THEN
2427: csi_t_gen_utility_pvt.add('CSI Transaction Created: '||l_txn_rec.transaction_id);
2428: END IF;
2429:
2430: IF NOT l_return_status = l_fnd_success THEN
2431: IF (l_debug > 0) THEN
2428: END IF;
2429:
2430: IF NOT l_return_status = l_fnd_success THEN
2431: IF (l_debug > 0) THEN
2432: csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.create_csi_txn: '||p_transaction_id);
2433: END IF;
2434: RAISE fnd_api.g_exc_error;
2435: END IF;
2436:
2437: -- Now loop through the PL/SQL Table.
2438: j := 1;
2439:
2440: IF (l_debug > 0) THEN
2441: csi_t_gen_utility_pvt.add('Starting to loop through Material Transaction Records');
2442: END IF;
2443:
2444: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
2445:
2443:
2444: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
2445:
2446: IF (l_debug > 0) THEN
2447: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
2448: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
2449: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
2450: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
2451: END IF;
2444: FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
2445:
2446: IF (l_debug > 0) THEN
2447: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
2448: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
2449: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
2450: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
2451: END IF;
2452:
2445:
2446: IF (l_debug > 0) THEN
2447: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
2448: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
2449: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
2450: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
2451: END IF;
2452:
2453: l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
2446: IF (l_debug > 0) THEN
2447: csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
2448: csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
2449: csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
2450: csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
2451: END IF;
2452:
2453: l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
2454: l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2461: --l_instance_query_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
2462: l_instance_query_rec.instance_usage_code := l_in_inventory;
2463:
2464: IF (l_debug > 0) THEN
2465: csi_t_gen_utility_pvt.add('Before Get Item Instance');
2466: csi_t_gen_utility_pvt.add('Ship to Location ID: '||l_mtl_item_tbl(j).ship_to_location_id);
2467: END IF;
2468:
2469: csi_item_instance_pub.get_item_instances(l_api_version,
2462: l_instance_query_rec.instance_usage_code := l_in_inventory;
2463:
2464: IF (l_debug > 0) THEN
2465: csi_t_gen_utility_pvt.add('Before Get Item Instance');
2466: csi_t_gen_utility_pvt.add('Ship to Location ID: '||l_mtl_item_tbl(j).ship_to_location_id);
2467: END IF;
2468:
2469: csi_item_instance_pub.get_item_instances(l_api_version,
2470: l_commit,
2481: l_msg_count,
2482: l_msg_data);
2483:
2484: IF (l_debug > 0) THEN
2485: csi_t_gen_utility_pvt.add('After Get Item Instance');
2486: END IF;
2487: l_tbl_count := 0;
2488: l_tbl_count := l_src_instance_header_tbl.count;
2489: IF (l_debug > 0) THEN
2486: END IF;
2487: l_tbl_count := 0;
2488: l_tbl_count := l_src_instance_header_tbl.count;
2489: IF (l_debug > 0) THEN
2490: csi_t_gen_utility_pvt.add('Source Records Found: '||l_tbl_count);
2491: END IF;
2492:
2493: -- Check for any errors and add them to the message stack to pass out to be put into the
2494: -- error log table.
2493: -- Check for any errors and add them to the message stack to pass out to be put into the
2494: -- error log table.
2495: IF NOT l_return_status = l_fnd_success then
2496: IF (l_debug > 0) THEN
2497: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
2498: END IF;
2499: l_msg_index := 1;
2500: WHILE l_msg_count > 0 loop
2501: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2510: IF l_src_instance_header_tbl.count = 0 THEN
2511: IF l_neg_code = 1 THEN -- Allow Neg Qtys on NON Serial Items ONLY
2512:
2513: IF (l_debug > 0) THEN
2514: csi_t_gen_utility_pvt.add('No records were found so create a new Source Instance Record');
2515: END IF;
2516:
2517: l_new_src_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
2518: l_new_src_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2542: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2543: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2544:
2545: IF (l_debug > 0) THEN
2546: csi_t_gen_utility_pvt.add('Before Create Transaction - Neg Qty');
2547: END IF;
2548:
2549: csi_item_instance_pub.create_item_instance(l_api_version,
2550: l_commit,
2562: l_msg_count,
2563: l_msg_data);
2564:
2565: IF (l_debug > 0) THEN
2566: csi_t_gen_utility_pvt.add('After Create Transaction');
2567: csi_t_gen_utility_pvt.add('New instance created is: '||l_new_src_instance_rec.instance_id);
2568: END IF;
2569:
2570: -- Check for any errors and add them to the message stack to pass out to be put into the
2563: l_msg_data);
2564:
2565: IF (l_debug > 0) THEN
2566: csi_t_gen_utility_pvt.add('After Create Transaction');
2567: csi_t_gen_utility_pvt.add('New instance created is: '||l_new_src_instance_rec.instance_id);
2568: END IF;
2569:
2570: -- Check for any errors and add them to the message stack to pass out to be put into the
2571: -- error log table.
2570: -- Check for any errors and add them to the message stack to pass out to be put into the
2571: -- error log table.
2572: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2573: IF (l_debug > 0) THEN
2574: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
2575: END IF;
2576: l_msg_index := 1;
2577: WHILE l_msg_count > 0 loop
2578: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2583: END IF;
2584:
2585: ELSE -- No Records were found and Neg Qtys Not Allowed
2586: IF (l_debug > 0) THEN
2587: csi_t_gen_utility_pvt.add('No Records were found in Install Base andNeg Qtys not allowed to error');
2588: END IF;
2589: fnd_message.set_name('CSI','CSI_NO_NEG_BAL_ALLOWED');
2590: l_error_message := fnd_message.get;
2591: RAISE fnd_api.g_exc_error;
2594:
2595: ELSIF l_src_instance_header_tbl.count = 1 THEN
2596:
2597: IF (l_debug > 0) THEN
2598: csi_t_gen_utility_pvt.add('You will update instance: '||l_src_instance_header_tbl(i).instance_id);
2599: END IF;
2600:
2601: l_update_source_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2602: l_update_source_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
2609: l_org_assignments_tbl.delete;
2610: l_asset_assignment_tbl.delete;
2611:
2612: IF (l_debug > 0) THEN
2613: csi_t_gen_utility_pvt.add('Before Update Item Instance - 80');
2614: END IF;
2615:
2616: 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);
2617:
2615:
2616: 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);
2617:
2618: IF (l_debug > 0) THEN
2619: csi_t_gen_utility_pvt.add('Instance Status Id: '||l_update_source_instance_rec.instance_status_id);
2620: END IF;
2621:
2622: csi_item_instance_pub.update_item_instance(l_api_version,
2623: l_commit,
2639: l_upd_error_instance_id := NULL;
2640: l_upd_error_instance_id := l_update_source_instance_rec.instance_id;
2641:
2642: IF (l_debug > 0) THEN
2643: csi_t_gen_utility_pvt.add('After Update Item Instance - Neg Qty');
2644: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2645: END IF;
2646:
2647: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2640: l_upd_error_instance_id := l_update_source_instance_rec.instance_id;
2641:
2642: IF (l_debug > 0) THEN
2643: csi_t_gen_utility_pvt.add('After Update Item Instance - Neg Qty');
2644: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2645: END IF;
2646:
2647: -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2648: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2646:
2647: -- Check for any errors and add them to the message stack to pass out to be put into the 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.update_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);
2660:
2661: ELSIF l_src_instance_header_tbl.count > 1 THEN
2662: -- Multiple Instances were found so throw error
2663: IF (l_debug > 0) THEN
2664: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
2665: END IF;
2666: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
2667: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
2668: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
2686: -- l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2687: l_instance_query_rec.instance_usage_code := l_in_service;
2688:
2689: IF (l_debug > 0) THEN
2690: csi_t_gen_utility_pvt.add('Before Get Item Instance Dest - Neg Qty');
2691: csi_t_gen_utility_pvt.add('Ship to Location ID Non Ser: '||l_mtl_item_tbl(j).ship_to_location_id);
2692: csi_t_gen_utility_pvt.add('Location Type Code: '||l_location_type);
2693: END IF;
2694:
2687: l_instance_query_rec.instance_usage_code := l_in_service;
2688:
2689: IF (l_debug > 0) THEN
2690: csi_t_gen_utility_pvt.add('Before Get Item Instance Dest - Neg Qty');
2691: csi_t_gen_utility_pvt.add('Ship to Location ID Non Ser: '||l_mtl_item_tbl(j).ship_to_location_id);
2692: csi_t_gen_utility_pvt.add('Location Type Code: '||l_location_type);
2693: END IF;
2694:
2695: csi_item_instance_pub.get_item_instances(l_api_version,
2688:
2689: IF (l_debug > 0) THEN
2690: csi_t_gen_utility_pvt.add('Before Get Item Instance Dest - Neg Qty');
2691: csi_t_gen_utility_pvt.add('Ship to Location ID Non Ser: '||l_mtl_item_tbl(j).ship_to_location_id);
2692: csi_t_gen_utility_pvt.add('Location Type Code: '||l_location_type);
2693: END IF;
2694:
2695: csi_item_instance_pub.get_item_instances(l_api_version,
2696: l_commit,
2707: l_msg_count,
2708: l_msg_data);
2709:
2710: IF (l_debug > 0) THEN
2711: csi_t_gen_utility_pvt.add('After Get Item Instance Dest - Neg Qty');
2712: END IF;
2713: l_tbl_count := 0;
2714: l_tbl_count := l_dest_instance_header_tbl.count;
2715: IF (l_debug > 0) THEN
2712: END IF;
2713: l_tbl_count := 0;
2714: l_tbl_count := l_dest_instance_header_tbl.count;
2715: IF (l_debug > 0) THEN
2716: csi_t_gen_utility_pvt.add('Destination Records Found: '||l_tbl_count);
2717: END IF;
2718:
2719: -- Check for any errors and add them to the message stack to pass out to be put into the
2720: -- error log table.
2719: -- Check for any errors and add them to the message stack to pass out to be put into the
2720: -- error log table.
2721: IF NOT l_return_status = l_fnd_success then
2722: IF (l_debug > 0) THEN
2723: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
2724: END IF;
2725: l_msg_index := 1;
2726: WHILE l_msg_count > 0 loop
2727: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2732: END IF;
2733:
2734: IF l_dest_instance_header_tbl.count = 0 THEN -- Installed Base Destination Records are not found
2735: IF (l_debug > 0) THEN
2736: csi_t_gen_utility_pvt.add('No Destination Records were found so we will create a new destination Record using the source data');
2737: END IF;
2738:
2739: l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
2740: l_new_dest_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2766: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2767: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2768:
2769: IF (l_debug > 0) THEN
2770: csi_t_gen_utility_pvt.add('Before Create Item Instance - Neg Qty');
2771: END IF;
2772:
2773: csi_item_instance_pub.create_item_instance(l_api_version,
2774: l_commit,
2786: l_msg_count,
2787: l_msg_data);
2788:
2789: IF (l_debug > 0) THEN
2790: csi_t_gen_utility_pvt.add('After Create Item Instance - Neg Qty');
2791: END IF;
2792:
2793: -- Check for any errors and add them to the message stack to pass out to be put into the
2794: -- error log table.
2793: -- Check for any errors and add them to the message stack to pass out to be put into the
2794: -- error log table.
2795: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2796: IF (l_debug > 0) THEN
2797: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
2798: END IF;
2799: l_msg_index := 1;
2800: WHILE l_msg_count > 0 loop
2801: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2807:
2808: ELSIF l_dest_instance_header_tbl.count = 1 THEN-- Installed Base Destination Records Found
2809:
2810: IF (l_debug > 0) THEN
2811: csi_t_gen_utility_pvt.add('You will update instance: '||l_dest_instance_header_tbl(i).instance_id);
2812: END IF;
2813:
2814: l_update_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2815: l_update_dest_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
2825:
2826: 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);
2827:
2828: IF (l_debug > 0) THEN
2829: csi_t_gen_utility_pvt.add('Before Update Transaction - Neg Qty');
2830: END IF;
2831:
2832: csi_item_instance_pub.update_item_instance(l_api_version,
2833: l_commit,
2849: l_upd_error_instance_id := NULL;
2850: l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
2851:
2852: IF (l_debug > 0) THEN
2853: csi_t_gen_utility_pvt.add('After Update Transaction - Neg Qty');
2854: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2855: END IF;
2856:
2857: -- Check for any errors and add them to the message stack to pass out to be put into the
2850: l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
2851:
2852: IF (l_debug > 0) THEN
2853: csi_t_gen_utility_pvt.add('After Update Transaction - Neg Qty');
2854: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2855: END IF;
2856:
2857: -- Check for any errors and add them to the message stack to pass out to be put into the
2858: -- error log table.
2857: -- Check for any errors and add them to the message stack to pass out to be put into the
2858: -- error log table.
2859: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2860: IF (l_debug > 0) THEN
2861: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
2862: END IF;
2863: l_msg_index := 1;
2864: WHILE l_msg_count > 0 loop
2865: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2872: ELSIF l_dest_instance_header_tbl.count > 1 THEN
2873:
2874: -- Multiple Instances were found so throw error
2875: IF (l_debug > 0) THEN
2876: csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-90');
2877: END IF;
2878: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
2879: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
2880: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
2911: l_org_assignments_tbl.delete;
2912: l_asset_assignment_tbl.delete;
2913:
2914: IF (l_debug > 0) THEN
2915: csi_t_gen_utility_pvt.add('Before Update Item Instance');
2916: csi_t_gen_utility_pvt.add('Ship to Location ID Serialized: '||l_mtl_item_tbl(j).ship_to_location_id);
2917: END IF;
2918:
2919: csi_item_instance_pub.update_item_instance(l_api_version,
2912: l_asset_assignment_tbl.delete;
2913:
2914: IF (l_debug > 0) THEN
2915: csi_t_gen_utility_pvt.add('Before Update Item Instance');
2916: csi_t_gen_utility_pvt.add('Ship to Location ID Serialized: '||l_mtl_item_tbl(j).ship_to_location_id);
2917: END IF;
2918:
2919: csi_item_instance_pub.update_item_instance(l_api_version,
2920: l_commit,
2936: l_upd_error_instance_id := NULL;
2937: l_upd_error_instance_id := l_update_instance_rec.instance_id;
2938:
2939: IF (l_debug > 0) THEN
2940: csi_t_gen_utility_pvt.add('After Update Item Instance');
2941: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2942: END IF;
2943:
2944: -- Check for any errors and add them to the message stack to pass out to be put into the
2937: l_upd_error_instance_id := l_update_instance_rec.instance_id;
2938:
2939: IF (l_debug > 0) THEN
2940: csi_t_gen_utility_pvt.add('After Update Item Instance');
2941: csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2942: END IF;
2943:
2944: -- Check for any errors and add them to the message stack to pass out to be put into the
2945: -- error log table.
2944: -- Check for any errors and add them to the message stack to pass out to be put into the
2945: -- error log table.
2946: IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2947: IF (l_debug > 0) THEN
2948: csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
2949: END IF;
2950: l_msg_index := 1;
2951: WHILE l_msg_count > 0 loop
2952: l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2957: END IF;
2958:
2959: ELSIF l_src_instance_header_tbl.count = 0 THEN
2960: IF (l_debug > 0) THEN
2961: csi_t_gen_utility_pvt.add('No Records were found in Install Base');
2962: END IF;
2963: fnd_message.set_name('CSI','CSI_IB_RECORD_NOTFOUND');
2964: fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
2965: fnd_message.set_token('SUBINVENTORY',l_mtl_item_tbl(j).subinventory_code);
2969:
2970: ELSIF l_src_instance_header_tbl.count > 1 THEN
2971: -- Multiple Instances were found so throw error
2972: IF (l_debug > 0) THEN
2973: csi_t_gen_utility_pvt.add('Multiple Instances were Found in InstallBase-65');
2974: END IF;
2975: fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
2976: fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
2977: fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
2984: END IF; -- End of Source Record If
2985: END LOOP; -- End of For Loop
2986:
2987: IF (l_debug > 0) THEN
2988: csi_t_gen_utility_pvt.add('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2989: csi_t_gen_utility_pvt.add('*****End of csi_inv_hz_pkg.misc_issue_hz_loc Transaction procedure*****');
2990: END IF;
2991:
2992: EXCEPTION
2985: END LOOP; -- End of For Loop
2986:
2987: IF (l_debug > 0) THEN
2988: csi_t_gen_utility_pvt.add('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2989: csi_t_gen_utility_pvt.add('*****End of csi_inv_hz_pkg.misc_issue_hz_loc Transaction procedure*****');
2990: END IF;
2991:
2992: EXCEPTION
2993: WHEN fnd_api.g_exc_error THEN
2991:
2992: EXCEPTION
2993: WHEN fnd_api.g_exc_error THEN
2994: IF (l_debug > 0) THEN
2995: csi_t_gen_utility_pvt.add('You have encountered a "fnd_api.g_exc_error" exception');
2996: END IF;
2997: x_return_status := l_fnd_error;
2998:
2999: IF l_mtl_item_tbl.count > 0 THEN
3019: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
3020:
3021: WHEN others THEN
3022: IF (l_debug > 0) THEN
3023: csi_t_gen_utility_pvt.add('You have encountered a "others" exception');
3024: END IF;
3025: fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
3026: fnd_message.set_token('API_NAME',l_api_name);
3027: fnd_message.set_token('SQL_ERROR',SQLERRM);