[Home] [Help]
1: package body CSI_INV_TRXS_PKG as
2: -- $Header: csiivtxb.pls 120.30.12020000.3 2012/11/09 10:19:21 mvaradam ship $
3:
4: l_Sysdate DATE := SYSDATE;
5:
16: x_return_status OUT NOCOPY VARCHAR2,
17: x_trx_error_rec OUT NOCOPY CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC)
18: IS
19:
20: l_mtl_item_tbl CSI_INV_TRXS_PKG.MTL_ITEM_TBL_TYPE;
21: l_api_name VARCHAR2(100) := 'CSI_INV_TRXS_PKG.MISC_RECEIPT';
22: l_api_version NUMBER := 1.0;
23: l_commit VARCHAR2(1) := FND_API.G_FALSE;
24: l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
17: x_trx_error_rec OUT NOCOPY CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC)
18: IS
19:
20: l_mtl_item_tbl CSI_INV_TRXS_PKG.MTL_ITEM_TBL_TYPE;
21: l_api_name VARCHAR2(100) := 'CSI_INV_TRXS_PKG.MISC_RECEIPT';
22: l_api_version NUMBER := 1.0;
23: l_commit VARCHAR2(1) := FND_API.G_FALSE;
24: l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
25: l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
52: l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
53: l_fnd_warning VARCHAR2(1) := 'W';
54: l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
55: l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
56: l_in_inventory VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_INVENTORY;
57: l_in_process VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_PROCESS;
58: l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
59: l_out_of_enterprise VARCHAR2(25) := 'OUT_OF_ENTERPRISE';
60: l_in_relationship VARCHAR2(25) := 'IN_RELATIONSHIP';
53: l_fnd_warning VARCHAR2(1) := 'W';
54: l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
55: l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
56: l_in_inventory VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_INVENTORY;
57: l_in_process VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_PROCESS;
58: l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
59: l_out_of_enterprise VARCHAR2(25) := 'OUT_OF_ENTERPRISE';
60: l_in_relationship VARCHAR2(25) := 'IN_RELATIONSHIP';
61: l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
54: l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
55: l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
56: l_in_inventory VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_INVENTORY;
57: l_in_process VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_PROCESS;
58: l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
59: l_out_of_enterprise VARCHAR2(25) := 'OUT_OF_ENTERPRISE';
60: l_in_relationship VARCHAR2(25) := 'IN_RELATIONSHIP';
61: l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
62: l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
57: l_in_process VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_PROCESS;
58: l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
59: l_out_of_enterprise VARCHAR2(25) := 'OUT_OF_ENTERPRISE';
60: l_in_relationship VARCHAR2(25) := 'IN_RELATIONSHIP';
61: l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
62: l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
63: l_installed VARCHAR2(25) := CSI_INV_TRXS_PKG.G_INSTALLED;
64: l_in_wip VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_WIP;
65: l_transaction_error_id NUMBER;
58: l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
59: l_out_of_enterprise VARCHAR2(25) := 'OUT_OF_ENTERPRISE';
60: l_in_relationship VARCHAR2(25) := 'IN_RELATIONSHIP';
61: l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
62: l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
63: l_installed VARCHAR2(25) := CSI_INV_TRXS_PKG.G_INSTALLED;
64: l_in_wip VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_WIP;
65: l_transaction_error_id NUMBER;
66: l_quantity NUMBER;
59: l_out_of_enterprise VARCHAR2(25) := 'OUT_OF_ENTERPRISE';
60: l_in_relationship VARCHAR2(25) := 'IN_RELATIONSHIP';
61: l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
62: l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
63: l_installed VARCHAR2(25) := CSI_INV_TRXS_PKG.G_INSTALLED;
64: l_in_wip VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_WIP;
65: l_transaction_error_id NUMBER;
66: l_quantity NUMBER;
67: l_mfg_serial_flag VARCHAR2(1);
60: l_in_relationship VARCHAR2(25) := 'IN_RELATIONSHIP';
61: l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
62: l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
63: l_installed VARCHAR2(25) := CSI_INV_TRXS_PKG.G_INSTALLED;
64: l_in_wip VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_WIP;
65: l_transaction_error_id NUMBER;
66: l_quantity NUMBER;
67: l_mfg_serial_flag VARCHAR2(1);
68: l_trans_status_code VARCHAR2(15);
134:
135: BEGIN
136: x_return_status := l_fnd_success;
137:
138: debug('*****Start of csi_inv_trxs_pkg.misc_receipt Transaction procedure*****');
139: debug('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
140: debug('csiivtxb.pls 115.25');
141: debug('Transaction You are Processing is: '||p_transaction_id);
142:
142:
143: -- This procedure queries all of the Inventory Transaction Records and returns them
144: -- as a table.
145:
146: debug('Executing csi_inv_trxs_pkg.get_transaction_recs');
147:
148: csi_inv_trxs_pkg.get_transaction_recs(p_transaction_id,
149: l_mtl_item_tbl,
150: l_return_status,
144: -- as a table.
145:
146: debug('Executing csi_inv_trxs_pkg.get_transaction_recs');
147:
148: csi_inv_trxs_pkg.get_transaction_recs(p_transaction_id,
149: l_mtl_item_tbl,
150: l_return_status,
151: l_error_message);
152:
155:
156: debug('Inventory Records Found: '||l_tbl_count);
157:
158: IF NOT l_return_status = l_fnd_success THEN
159: debug('You have encountered an error in CSI_INV_TRXS_PKG.get_transaction_recs, Transaction ID: '||p_transaction_id);
160: RAISE fnd_api.g_exc_error;
161: END IF;
162:
163: -- Determine Trasaction Type
195:
196:
197: -- Get the Master Organization ID
198:
199: debug('Executing csi_inv_trxs_pkg.get_master_organization');
200:
201: csi_inv_trxs_pkg.get_master_organization(l_mtl_item_tbl(i).organization_id,
202: l_master_organization_id,
203: l_return_status,
197: -- Get the Master Organization ID
198:
199: debug('Executing csi_inv_trxs_pkg.get_master_organization');
200:
201: csi_inv_trxs_pkg.get_master_organization(l_mtl_item_tbl(i).organization_id,
202: l_master_organization_id,
203: l_return_status,
204: l_error_message);
205:
205:
206: debug('Master Organization is: '||l_master_organization_id);
207:
208: IF NOT l_return_status = l_fnd_success THEN
209: debug('You have encountered an error in csi_inv_trxs_pkg.get_master_organization, Organization ID: '||l_mtl_item_tbl(i).organization_id);
210: RAISE fnd_api.g_exc_error;
211: END IF;
212:
213: -- Call get_fnd_employee_id and get the employee id
211: END IF;
212:
213: -- Call get_fnd_employee_id and get the employee id
214:
215: debug('Executing csi_inv_trxs_pkg.get_fnd_employee_id');
216:
217: l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
218:
219: IF l_employee_id = -1 THEN
213: -- Call get_fnd_employee_id and get the employee id
214:
215: debug('Executing csi_inv_trxs_pkg.get_fnd_employee_id');
216:
217: l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
218:
219: IF l_employee_id = -1 THEN
220: debug('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
221: END IF;
223: debug('The Employee that is processing this Transaction is: '||l_employee_id);
224:
225: -- See if this is a depreciable Item to set the status of the transaction record
226:
227: debug('Executing csi_inv_trxs_pkg.check_depreciable');
228:
229: csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
230: l_depreciable,
231: l_mtl_item_tbl(i).organization_id); --Added for Bug 13988660
225: -- See if this is a depreciable Item to set the status of the transaction record
226:
227: debug('Executing csi_inv_trxs_pkg.check_depreciable');
228:
229: csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
230: l_depreciable,
231: l_mtl_item_tbl(i).organization_id); --Added for Bug 13988660
232:
233: debug('Is this Item ID: '||l_mtl_item_tbl(i).inventory_item_id||', Depreciable :'||l_depreciable);
259:
260: debug('Instance Status from Profile: '||r_id.instance_status_id);
261:
262: -- Initialize Transaction Record
263: l_txn_rec := csi_inv_trxs_pkg.init_txn_rec;
264:
265: -- Set Status based on redeployment
266: IF l_depreciable = 'N' THEN
267: IF l_mtl_item_tbl(i).serial_number is NOT NULL THEN
264:
265: -- Set Status based on redeployment
266: IF l_depreciable = 'N' THEN
267: IF l_mtl_item_tbl(i).serial_number is NOT NULL THEN
268: csi_inv_trxs_pkg.get_redeploy_flag(l_mtl_item_tbl(i).inventory_item_id,
269: l_mtl_item_tbl(i).serial_number,
270: l_sysdate,
271: l_redeploy_flag,
272: l_return_status,
273: l_error_message);
274: END IF;
275:
276: IF l_redeploy_flag = 'Y' THEN
277: l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
278: ELSE
279: l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_complete;
280: END IF;
281: ELSE
275:
276: IF l_redeploy_flag = 'Y' THEN
277: l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
278: ELSE
279: l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_complete;
280: END IF;
281: ELSE
282: l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
283: END IF;
278: ELSE
279: l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_complete;
280: END IF;
281: ELSE
282: l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
283: END IF;
284:
285: IF NOT l_return_status = l_fnd_success THEN
286: debug('Redeploy Flag: '||l_redeploy_flag);
283: END IF;
284:
285: IF NOT l_return_status = l_fnd_success THEN
286: debug('Redeploy Flag: '||l_redeploy_flag);
287: debug('You have encountered an error in csi_inv_trxs_pkg.get_redeploy_flag: '||l_error_message);
288: RAISE fnd_api.g_exc_error;
289: END IF;
290:
291: debug('Redeploy Flag: '||l_redeploy_flag);
294: -- Create CSI Transaction to be used
295: l_txn_rec.source_transaction_date := l_mtl_item_tbl(i).transaction_date;
296: l_txn_rec.transaction_date := l_sysdate;
297: l_txn_rec.transaction_type_id :=
298: csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
299: l_txn_rec.transaction_quantity :=
300: l_mtl_item_tbl(i).transaction_quantity;
301: l_txn_rec.transaction_uom_code := l_mtl_item_tbl(i).transaction_uom;
302: l_txn_rec.transacted_by := l_employee_id;
333: debug('Cycle Count Name: '||l_txn_rec.source_header_ref);
334:
335: END IF;
336:
337: csi_inv_trxs_pkg.create_csi_txn(l_txn_rec,
338: l_error_message,
339: l_return_status);
340:
341: debug('CSI Transaction Created: '||l_txn_rec.transaction_id);
340:
341: debug('CSI Transaction Created: '||l_txn_rec.transaction_id);
342:
343: IF NOT l_return_status = l_fnd_success THEN
344: debug('You have encountered an error in csi_inv_trxs_pkg.create_csi_txn: '||p_transaction_id);
345: RAISE fnd_api.g_exc_error;
346: END IF;
347:
348: -- Now loop through the PL/SQL Table.
358: debug('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
359:
360: IF l_mtl_item_tbl(j).serial_number IS NOT NULL THEN -- Serialized
361:
362: csi_inv_trxs_pkg.set_item_attr_query_values(l_mtl_item_tbl,
363: j,
364: NULL,
365: l_instance_query_rec,
366: x_return_status);
406:
407: IF l_src_instance_header_tbl.count = 0 THEN -- No Records found so Create Serialized record
408: debug('No Records found so Create a Serialized Record');
409:
410: l_new_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
411: l_new_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
412: l_new_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
413: l_new_instance_rec.inv_master_organization_id := l_master_organization_id;
414: l_new_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
418: l_new_instance_rec.quantity := 1;
419: l_new_instance_rec.active_start_date := l_sysdate;
420: l_new_instance_rec.active_end_date := NULL;
421: l_new_instance_rec.unit_of_measure := l_mtl_item_tbl(j).primary_uom_code;
422: l_new_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
423: l_new_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
424: l_new_instance_rec.instance_usage_code := l_in_inventory;
425: l_new_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
426: l_new_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
428: l_new_instance_rec.customer_view_flag := 'N';
429: l_new_instance_rec.merchant_view_flag := 'Y';
430: l_new_instance_rec.object_version_number := l_object_version_number;
431: l_new_instance_rec.operational_status_code := 'NOT_USED';
432: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
433: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
434: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
435: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
436: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
429: l_new_instance_rec.merchant_view_flag := 'Y';
430: l_new_instance_rec.object_version_number := l_object_version_number;
431: l_new_instance_rec.operational_status_code := 'NOT_USED';
432: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
433: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
434: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
435: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
436: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
437: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
430: l_new_instance_rec.object_version_number := l_object_version_number;
431: l_new_instance_rec.operational_status_code := 'NOT_USED';
432: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
433: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
434: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
435: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
436: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
437: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
438:
431: l_new_instance_rec.operational_status_code := 'NOT_USED';
432: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
433: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
434: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
435: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
436: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
437: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
438:
439: 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);
432: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
433: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
434: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
435: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
436: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
437: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
438:
439: 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);
440:
433: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
434: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
435: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
436: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
437: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
438:
439: 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);
440:
441: debug('Instance_status_id Value: '||nvl(l_new_instance_rec.instance_status_id,-1));
435: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
436: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
437: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
438:
439: 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);
440:
441: debug('Instance_status_id Value: '||nvl(l_new_instance_rec.instance_status_id,-1));
442: debug('You will now Create a new Item Instance Record');
443: debug('Serial Number: '||l_new_instance_rec.serial_number);
485:
486: debug('Update Serialized Item which is OUT NOCOPY Of Service');
487: debug('Serial Number is: '||l_src_instance_header_tbl(i).serial_number);
488:
489: l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
490: l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
491: l_update_instance_rec.quantity := 1;
492: l_update_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
493: -- Added for bug 5975739
497: l_update_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
498: l_update_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
499: --l_update_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
500: l_update_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
501: l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
502: l_update_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
503: l_update_instance_rec.instance_usage_code := l_in_inventory;
504: l_update_instance_rec.active_end_date := NULL;
505: l_update_instance_rec.pa_project_id := NULL;
518: -- Bug 9091915
519: -- When instance status id is available for a source instance
520: -- the status id should not be updated
521: IF NVL(l_update_instance_rec.instance_status_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM OR CSI_Item_Instance_vld_pvt.val_inst_ter_flag(l_update_instance_rec.instance_status_id) THEN
522: 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);
523: END IF;
524:
525: debug('Before Update Item Instance');
526:
590:
591: debug('Update Serialized Item which is :'||l_src_instance_header_tbl(i).instance_usage_code);
592: debug('Serial Number is: '||l_src_instance_header_tbl(i).serial_number);
593:
594: l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
595: l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
596: l_update_instance_rec.quantity := 1;
597: l_update_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
598: l_update_instance_rec.mfg_serial_number_flag := 'Y';
603: l_update_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
604: l_update_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
605: --l_update_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
606: l_update_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
607: l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
608: l_update_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
609: l_update_instance_rec.instance_usage_code := l_in_inventory;
610: l_update_instance_rec.active_end_date := NULL;
611: l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
685: FOR p in l_party_header_tbl.FIRST .. l_party_header_tbl.LAST LOOP
686: IF l_party_header_tbl(p).relationship_type_code = 'OWNER' THEN
687: debug('Found the OWNER party so updating this back to the Internal Party ID');
688:
689: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
690: l_party_tbl(i).instance_id := l_src_instance_header_tbl(i).instance_id;
691: l_party_tbl(i).instance_party_id := l_party_header_tbl(p).instance_party_id;
692: l_party_tbl(i).object_version_number := l_party_header_tbl(p).object_version_number;
693: debug('After finding the OWNER party and updating this back to the Internal Party ID');
756: -- Bug 9091915
757: -- When instance status id is available for a source instance
758: -- the status id should not be updated
759: IF NVL(l_update_instance_rec.instance_status_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM OR CSI_Item_Instance_vld_pvt.val_inst_ter_flag(l_update_instance_rec.instance_status_id) THEN
760: 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);
761: END IF;
762:
763: debug('Before Update Item Instance');
764:
817: l_return_status := l_fnd_error;
818: RAISE fnd_api.g_exc_error;
819: END IF;
820: ELSE -- No API Records so create a new serialized record
821: l_new_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
822: l_new_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
823: l_new_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
824: l_new_instance_rec.inv_master_organization_id := l_master_organization_id;
825: l_new_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
829: l_new_instance_rec.quantity := 1;
830: l_new_instance_rec.active_start_date := l_sysdate;
831: l_new_instance_rec.active_end_date := NULL;
832: l_new_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
833: l_new_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
834: l_new_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
835: l_new_instance_rec.instance_usage_code := l_in_inventory;
836: l_new_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
837: l_new_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
839: l_new_instance_rec.customer_view_flag := 'N';
840: l_new_instance_rec.merchant_view_flag := 'Y';
841: l_new_instance_rec.object_version_number := l_object_version_number;
842: l_new_instance_rec.operational_status_code := 'NOT_USED';
843: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
844: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
845: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
846: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
847: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
840: l_new_instance_rec.merchant_view_flag := 'Y';
841: l_new_instance_rec.object_version_number := l_object_version_number;
842: l_new_instance_rec.operational_status_code := 'NOT_USED';
843: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
844: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
845: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
846: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
847: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
848: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
841: l_new_instance_rec.object_version_number := l_object_version_number;
842: l_new_instance_rec.operational_status_code := 'NOT_USED';
843: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
844: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
845: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
846: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
847: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
848: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
849:
842: l_new_instance_rec.operational_status_code := 'NOT_USED';
843: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
844: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
845: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
846: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
847: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
848: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
849:
850: 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);
843: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
844: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
845: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
846: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
847: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
848: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
849:
850: 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);
851:
844: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
845: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
846: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
847: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
848: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
849:
850: 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);
851:
852: debug('You will now Create a new Item Instance Record');
846: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
847: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
848: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
849:
850: 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);
851:
852: debug('You will now Create a new Item Instance Record');
853:
854: csi_item_instance_pub.create_item_instance(l_api_version,
885: END IF; -- End of Serialized Source Block
886:
887: ELSIF l_mtl_item_tbl(j).serial_number IS NULL THEN -- Non Serialized
888:
889: csi_inv_trxs_pkg.set_item_attr_query_values(l_mtl_item_tbl,
890: j,
891: NULL,
892: l_instance_query_rec,
893: x_return_status);
939:
940: IF l_src_instance_header_tbl.count = 0 THEN -- No Records found so Create either Serialized or Non Serialized
941: debug('No Records found so Create a Record for Non-Serialized');
942:
943: l_new_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
944: l_new_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
945: l_new_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
946: l_new_instance_rec.inv_master_organization_id := l_master_organization_id;
947: l_new_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
950: l_new_instance_rec.quantity := abs(l_mtl_item_tbl(j).transaction_quantity);
951: l_new_instance_rec.active_start_date := l_sysdate;
952: l_new_instance_rec.active_end_date := NULL;
953: l_new_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
954: l_new_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
955: l_new_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
956: l_new_instance_rec.instance_usage_code := l_in_inventory;
957: l_new_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
958: l_new_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
960: l_new_instance_rec.customer_view_flag := 'N';
961: l_new_instance_rec.merchant_view_flag := 'Y';
962: l_new_instance_rec.object_version_number := l_object_version_number;
963: l_new_instance_rec.operational_status_code := 'NOT_USED';
964: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
965: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
966: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
967: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
968: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
961: l_new_instance_rec.merchant_view_flag := 'Y';
962: l_new_instance_rec.object_version_number := l_object_version_number;
963: l_new_instance_rec.operational_status_code := 'NOT_USED';
964: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
965: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
966: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
967: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
968: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
969: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
962: l_new_instance_rec.object_version_number := l_object_version_number;
963: l_new_instance_rec.operational_status_code := 'NOT_USED';
964: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
965: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
966: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
967: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
968: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
969: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
970:
963: l_new_instance_rec.operational_status_code := 'NOT_USED';
964: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
965: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
966: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
967: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
968: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
969: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
970:
971: 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);
964: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
965: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
966: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
967: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
968: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
969: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
970:
971: 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);
972:
965: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
966: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
967: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
968: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
969: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
970:
971: 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);
972:
973: debug('You will now Create a new Item Instance Record');
967: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
968: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
969: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
970:
971: 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);
972:
973: debug('You will now Create a new Item Instance Record');
974:
975: csi_item_instance_pub.create_item_instance(l_api_version,
1007: debug('1 Instance Record was found');
1008:
1009: debug('Update the Non-Serialized, In-Inventory Item Instance record');
1010:
1011: l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1012: l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
1013: l_update_instance_rec.quantity := l_src_instance_header_tbl(i).quantity + abs(l_mtl_item_tbl(j).primary_quantity);
1014: l_update_instance_rec.active_end_date := NULL;
1015: l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
1031: -- Bug 9091915
1032: -- When instance status id is available for a source instance
1033: -- the status id should not be updated
1034: IF NVL(l_update_instance_rec.instance_status_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM OR CSI_Item_Instance_vld_pvt.val_inst_ter_flag(l_update_instance_rec.instance_status_id) THEN
1035: 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);
1036: END IF;
1037:
1038: debug('Before Update Item Instance');
1039:
1088: END IF; -- End of Serialized Item If
1089: END LOOP; -- End of For Loop
1090:
1091: debug('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
1092: debug('*****End of csi_inv_trxs_pkg.misc_receipt Transaction*****');
1093:
1094: EXCEPTION
1095: WHEN fnd_api.g_exc_error THEN
1096: debug('You have encountered a "fnd_api.g_exc_error" exception');
1112: x_trx_error_rec.error_text := l_error_message;
1113: x_trx_error_rec.transaction_id := NULL;
1114: x_trx_error_rec.source_type := 'CSIMSRCV';
1115: x_trx_error_rec.source_id := p_transaction_id;
1116: x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
1117: x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
1118: x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
1119: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
1120:
1113: x_trx_error_rec.transaction_id := NULL;
1114: x_trx_error_rec.source_type := 'CSIMSRCV';
1115: x_trx_error_rec.source_id := p_transaction_id;
1116: x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
1117: x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
1118: x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
1119: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
1120:
1121: WHEN others THEN
1115: x_trx_error_rec.source_id := p_transaction_id;
1116: x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
1117: x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
1118: x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
1119: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
1120:
1121: WHEN others THEN
1122: l_sql_error := SQLERRM;
1123: debug('You have encountered a "others" exception');
1143: x_trx_error_rec.error_text := fnd_message.get;
1144: x_trx_error_rec.transaction_id := NULL;
1145: x_trx_error_rec.source_type := 'CSIMSRCV';
1146: x_trx_error_rec.source_id := p_transaction_id;
1147: x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
1148: x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
1149: x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
1150: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
1151:
1144: x_trx_error_rec.transaction_id := NULL;
1145: x_trx_error_rec.source_type := 'CSIMSRCV';
1146: x_trx_error_rec.source_id := p_transaction_id;
1147: x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
1148: x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
1149: x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
1150: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
1151:
1152: END misc_receipt;
1146: x_trx_error_rec.source_id := p_transaction_id;
1147: x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
1148: x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
1149: x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
1150: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
1151:
1152: END misc_receipt;
1153:
1154: PROCEDURE receipt_inventory(p_transaction_id IN NUMBER,
1156: x_return_status OUT NOCOPY VARCHAR2,
1157: x_trx_error_rec OUT NOCOPY CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC)
1158: IS
1159:
1160: l_mtl_item_tbl CSI_INV_TRXS_PKG.MTL_ITEM_TBL_TYPE;
1161: l_api_name VARCHAR2(100) := 'CSI_INV_TRXS_PKG.RECEIPT_INVENTORY';
1162: l_api_version NUMBER := 1.0;
1163: l_commit VARCHAR2(1) := FND_API.G_FALSE;
1164: l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
1157: x_trx_error_rec OUT NOCOPY CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC)
1158: IS
1159:
1160: l_mtl_item_tbl CSI_INV_TRXS_PKG.MTL_ITEM_TBL_TYPE;
1161: l_api_name VARCHAR2(100) := 'CSI_INV_TRXS_PKG.RECEIPT_INVENTORY';
1162: l_api_version NUMBER := 1.0;
1163: l_commit VARCHAR2(1) := FND_API.G_FALSE;
1164: l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
1165: l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
1193: l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1194: l_fnd_warning VARCHAR2(1) := 'W';
1195: l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
1196: l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
1197: l_in_inventory VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_INVENTORY;
1198: l_in_process VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_PROCESS;
1199: l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
1200: l_out_of_enterprise VARCHAR2(25) := 'OUT_OF_ENTERPRISE';
1201: l_in_relationship VARCHAR2(25) := 'IN_RELATIONSHIP';
1194: l_fnd_warning VARCHAR2(1) := 'W';
1195: l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
1196: l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
1197: l_in_inventory VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_INVENTORY;
1198: l_in_process VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_PROCESS;
1199: l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
1200: l_out_of_enterprise VARCHAR2(25) := 'OUT_OF_ENTERPRISE';
1201: l_in_relationship VARCHAR2(25) := 'IN_RELATIONSHIP';
1202: l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
1195: l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
1196: l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
1197: l_in_inventory VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_INVENTORY;
1198: l_in_process VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_PROCESS;
1199: l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
1200: l_out_of_enterprise VARCHAR2(25) := 'OUT_OF_ENTERPRISE';
1201: l_in_relationship VARCHAR2(25) := 'IN_RELATIONSHIP';
1202: l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
1203: l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
1198: l_in_process VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_PROCESS;
1199: l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
1200: l_out_of_enterprise VARCHAR2(25) := 'OUT_OF_ENTERPRISE';
1201: l_in_relationship VARCHAR2(25) := 'IN_RELATIONSHIP';
1202: l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
1203: l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
1204: l_installed VARCHAR2(25) := CSI_INV_TRXS_PKG.G_INSTALLED;
1205: l_in_wip VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_WIP;
1206: l_transaction_error_id NUMBER;
1199: l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
1200: l_out_of_enterprise VARCHAR2(25) := 'OUT_OF_ENTERPRISE';
1201: l_in_relationship VARCHAR2(25) := 'IN_RELATIONSHIP';
1202: l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
1203: l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
1204: l_installed VARCHAR2(25) := CSI_INV_TRXS_PKG.G_INSTALLED;
1205: l_in_wip VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_WIP;
1206: l_transaction_error_id NUMBER;
1207: l_quantity NUMBER;
1200: l_out_of_enterprise VARCHAR2(25) := 'OUT_OF_ENTERPRISE';
1201: l_in_relationship VARCHAR2(25) := 'IN_RELATIONSHIP';
1202: l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
1203: l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
1204: l_installed VARCHAR2(25) := CSI_INV_TRXS_PKG.G_INSTALLED;
1205: l_in_wip VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_WIP;
1206: l_transaction_error_id NUMBER;
1207: l_quantity NUMBER;
1208: l_mfg_serial_flag VARCHAR2(1);
1201: l_in_relationship VARCHAR2(25) := 'IN_RELATIONSHIP';
1202: l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
1203: l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
1204: l_installed VARCHAR2(25) := CSI_INV_TRXS_PKG.G_INSTALLED;
1205: l_in_wip VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_WIP;
1206: l_transaction_error_id NUMBER;
1207: l_quantity NUMBER;
1208: l_mfg_serial_flag VARCHAR2(1);
1209: l_trans_status_code VARCHAR2(15);
1267:
1268: BEGIN
1269: x_return_status := l_fnd_success;
1270:
1271: debug('*****Start of csi_inv_trxs_pkg.receipt_inventory Transaction procedure*****');
1272: debug('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
1273: debug('Transaction You are Processing is: '||p_transaction_id);
1274:
1275: -- This procedure queries all of the Inventory Transaction Records and
1274:
1275: -- This procedure queries all of the Inventory Transaction Records and
1276: -- returns them as a table.
1277:
1278: debug('Executing csi_inv_trxs_pkg.get_transaction_recs');
1279:
1280: csi_inv_trxs_pkg.get_transaction_recs(p_transaction_id,
1281: l_mtl_item_tbl,
1282: l_return_status,
1276: -- returns them as a table.
1277:
1278: debug('Executing csi_inv_trxs_pkg.get_transaction_recs');
1279:
1280: csi_inv_trxs_pkg.get_transaction_recs(p_transaction_id,
1281: l_mtl_item_tbl,
1282: l_return_status,
1283: l_error_message);
1284:
1287:
1288: debug('Source Records Found: '||l_tbl_count);
1289:
1290: IF NOT l_return_status = l_fnd_success THEN
1291: debug('You have encountered an error in CSI_INV_TRXS_PKG.get_transaction_recs, Transaction ID: '||p_transaction_id);
1292: RAISE fnd_api.g_exc_error;
1293: END IF;
1294:
1295: -- Get the Master Organization ID
1293: END IF;
1294:
1295: -- Get the Master Organization ID
1296:
1297: debug('Executing csi_inv_trxs_pkg.get_master_organization');
1298:
1299: csi_inv_trxs_pkg.get_master_organization(l_mtl_item_tbl(i).organization_id,
1300: l_master_organization_id,
1301: l_return_status,
1295: -- Get the Master Organization ID
1296:
1297: debug('Executing csi_inv_trxs_pkg.get_master_organization');
1298:
1299: csi_inv_trxs_pkg.get_master_organization(l_mtl_item_tbl(i).organization_id,
1300: l_master_organization_id,
1301: l_return_status,
1302: l_error_message);
1303:
1301: l_return_status,
1302: l_error_message);
1303:
1304: IF NOT l_return_status = l_fnd_success THEN
1305: debug('You have encountered an error in csi_inv_trxs_pkg.get_master_organization, Organization ID: '||l_mtl_item_tbl(i).organization_id);
1306: RAISE fnd_api.g_exc_error;
1307: END IF;
1308:
1309: -- Call get_fnd_employee_id and get the employee id
1307: END IF;
1308:
1309: -- Call get_fnd_employee_id and get the employee id
1310:
1311: debug('Executing csi_inv_trxs_pkg.get_fnd_employee_id');
1312:
1313: l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
1314:
1315: IF l_employee_id = -1 THEN
1309: -- Call get_fnd_employee_id and get the employee id
1310:
1311: debug('Executing csi_inv_trxs_pkg.get_fnd_employee_id');
1312:
1313: l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
1314:
1315: IF l_employee_id = -1 THEN
1316: debug('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
1317: END IF;
1319: debug('The Employee that is processing this Transaction is: '||l_employee_id);
1320:
1321: -- See if this is a depreciable Item to set the status of the transaction record
1322:
1323: debug('Executing csi_inv_trxs_pkg.check_depreciable');
1324:
1325: csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
1326: l_depreciable,
1327: l_mtl_item_tbl(i).organization_id ); --Added for Bug 13988660
1321: -- See if this is a depreciable Item to set the status of the transaction record
1322:
1323: debug('Executing csi_inv_trxs_pkg.check_depreciable');
1324:
1325: csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
1326: l_depreciable,
1327: l_mtl_item_tbl(i).organization_id ); --Added for Bug 13988660
1328:
1329: debug('Is this Item ID: '||l_mtl_item_tbl(i).inventory_item_id||', Depreciable :'||l_depreciable);
1365: debug('PO Unit Price: '||r_po_info.unit_price);
1366: debug('PO Currency Code: '||r_po_info.currency_code);
1367:
1368: -- Initialize Transaction Record
1369: l_txn_rec := csi_inv_trxs_pkg.init_txn_rec;
1370:
1371: -- Set Status based on redeployment
1372: IF l_depreciable = 'N' THEN
1373: IF l_mtl_item_tbl(i).serial_number is NOT NULL THEN
1370:
1371: -- Set Status based on redeployment
1372: IF l_depreciable = 'N' THEN
1373: IF l_mtl_item_tbl(i).serial_number is NOT NULL THEN
1374: csi_inv_trxs_pkg.get_redeploy_flag(l_mtl_item_tbl(i).inventory_item_id,
1375: l_mtl_item_tbl(i).serial_number,
1376: l_sysdate,
1377: l_redeploy_flag,
1378: l_return_status,
1378: l_return_status,
1379: l_error_message);
1380: END IF;
1381: IF l_redeploy_flag = 'Y' THEN
1382: l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
1383: ELSE
1384: l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_complete;
1385: END IF;
1386: ELSE
1380: END IF;
1381: IF l_redeploy_flag = 'Y' THEN
1382: l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
1383: ELSE
1384: l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_complete;
1385: END IF;
1386: ELSE
1387: l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
1388: END IF;
1383: ELSE
1384: l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_complete;
1385: END IF;
1386: ELSE
1387: l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
1388: END IF;
1389:
1390: IF NOT l_return_status = l_fnd_success THEN
1391: debug('Redeploy Flag: '||l_redeploy_flag);
1388: END IF;
1389:
1390: IF NOT l_return_status = l_fnd_success THEN
1391: debug('Redeploy Flag: '||l_redeploy_flag);
1392: debug('You have encountered an error in csi_inv_trxs_pkg.get_redeploy_flag: '||l_error_message);
1393: RAISE fnd_api.g_exc_error;
1394: END IF;
1395:
1396: debug('Redeploy Flag: '||l_redeploy_flag);
1398:
1399: -- Create CSI Transaction to be used
1400: l_txn_rec.source_transaction_date := l_mtl_item_tbl(i).transaction_date;
1401: l_txn_rec.transaction_date := l_sysdate;
1402: l_txn_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('PO_RECEIPT_INTO_INVENTORY','INV');
1403: l_txn_rec.transaction_quantity := l_mtl_item_tbl(i).transaction_quantity;
1404: l_txn_rec.transaction_uom_code := l_mtl_item_tbl(i).transaction_uom;
1405: l_txn_rec.transacted_by := l_employee_id;
1406: l_txn_rec.transaction_action_code := NULL;
1413: l_txn_rec.source_line_ref_id := r_po_info.po_line_id;
1414: l_txn_rec.source_header_ref := r_po_info.po_number;
1415: l_txn_rec.source_line_ref := to_char(r_po_info.po_line_number);
1416:
1417: csi_inv_trxs_pkg.create_csi_txn(l_txn_rec,
1418: l_error_message,
1419: l_return_status);
1420:
1421: debug('CSI Transaction Created: '||l_txn_rec.transaction_id);
1420:
1421: debug('CSI Transaction Created: '||l_txn_rec.transaction_id);
1422:
1423: IF NOT l_return_status = l_fnd_success THEN
1424: debug('You have encountered an error in csi_inv_trxs_pkg.create_csi_txn: '||p_transaction_id);
1425: RAISE fnd_api.g_exc_error;
1426: END IF;
1427:
1428: -- Now loop through the PL/SQL Table.
1440: debug('PO_LINE_ID is: '||r_po_info.po_line_id);
1441:
1442: IF l_mtl_item_tbl(j).serial_number IS NOT NULL THEN -- Serialized
1443:
1444: csi_inv_trxs_pkg.set_item_attr_query_values(l_mtl_item_tbl,
1445: j,
1446: NULL,
1447: l_instance_query_rec,
1448: x_return_status);
1488:
1489: IF l_src_instance_header_tbl.count < 1 THEN -- No Records found so Create either Serialized Item
1490:
1491: debug('No source records were found so create a new one');
1492: l_new_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
1493: l_new_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1494: l_new_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1495: l_new_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
1496: l_new_instance_rec.inv_master_organization_id := l_master_organization_id;
1498: l_new_instance_rec.mfg_serial_number_flag := l_mfg_serial_flag;
1499: l_new_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1500: l_new_instance_rec.quantity := abs(l_quantity);
1501: l_new_instance_rec.unit_of_measure := l_mtl_item_tbl(j).primary_uom_code;
1502: l_new_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
1503: l_new_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
1504: l_new_instance_rec.instance_usage_code := l_in_inventory;
1505: l_new_instance_rec.last_po_po_line_id := r_po_info.po_line_id; --5184815
1506: l_new_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
1514: l_new_instance_rec.active_end_date := NULL;
1515: l_new_instance_rec.purchase_unit_price := r_po_info.unit_price;
1516: l_new_instance_rec.purchase_currency_code := r_po_info.currency_code;
1517:
1518: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
1519: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
1520: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
1521: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
1522: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
1515: l_new_instance_rec.purchase_unit_price := r_po_info.unit_price;
1516: l_new_instance_rec.purchase_currency_code := r_po_info.currency_code;
1517:
1518: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
1519: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
1520: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
1521: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
1522: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
1523: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
1516: l_new_instance_rec.purchase_currency_code := r_po_info.currency_code;
1517:
1518: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
1519: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
1520: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
1521: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
1522: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
1523: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
1524:
1517:
1518: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
1519: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
1520: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
1521: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
1522: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
1523: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
1524:
1525: 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);
1518: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
1519: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
1520: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
1521: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
1522: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
1523: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
1524:
1525: 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);
1526:
1519: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
1520: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
1521: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
1522: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
1523: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
1524:
1525: 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);
1526:
1527: debug('Before Create of new source Item Instance');
1521: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
1522: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
1523: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
1524:
1525: 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);
1526:
1527: debug('Before Create of new source Item Instance');
1528:
1529: csi_item_instance_pub.create_item_instance(l_api_version,
1573: debug('Update Serialized Item which is :'||l_src_instance_header_tbl(i).instance_usage_code);
1574: debug('Serial Number is: '||l_src_instance_header_tbl(i).serial_number);
1575: debug('Updating Item Instance: '||l_src_instance_header_tbl(i).instance_id);
1576:
1577: l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1578: l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
1579: l_update_instance_rec.quantity := 1;
1580: l_update_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
1581: --Added For Bug 5975739
1592: l_update_instance_rec.pa_project_id := NULL;
1593: l_update_instance_rec.pa_project_task_id := NULL;
1594: l_update_instance_rec.install_location_type_code := NULL;
1595: l_update_instance_rec.install_location_id := NULL;
1596: l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
1597: l_update_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
1598: l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
1599:
1600: l_party_tbl.delete;
1602: l_pricing_attrib_tbl.delete;
1603: l_org_assignments_tbl.delete;
1604: l_asset_assignment_tbl.delete;
1605:
1606: 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);
1607:
1608: debug('Right after setting instance status');
1609: debug('Before Update item instance');
1610:
1675:
1676: debug('Update Serialized Item which is :'||l_src_instance_header_tbl(i).instance_usage_code);
1677: debug('Serial Number is: '||l_src_instance_header_tbl(i).serial_number);
1678:
1679: l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1680: l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
1681: l_update_instance_rec.quantity := 1;
1682: l_update_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
1683: -- Added for Bug 5975739
1688: l_update_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1689: l_update_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1690: -- l_update_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
1691: l_update_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
1692: l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
1693: l_update_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
1694: l_update_instance_rec.instance_usage_code := l_in_inventory;
1695: l_update_instance_rec.last_po_po_line_id := r_po_info.po_line_id; --5184815
1696: l_update_instance_rec.active_end_date := NULL;
1765: FOR p in l_party_header_tbl.FIRST .. l_party_header_tbl.LAST LOOP
1766: IF l_party_header_tbl(p).relationship_type_code = 'OWNER' THEN
1767: debug('Found the OWNER party so updating this back to the Internal Party ID');
1768:
1769: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
1770: l_party_tbl(i).instance_id := l_src_instance_header_tbl(i).instance_id;
1771: l_party_tbl(i).instance_party_id := l_party_header_tbl(p).instance_party_id;
1772: l_party_tbl(i).object_version_number := l_party_header_tbl(p).object_version_number;
1773: debug('After finding the OWNER party and updating this back to the Internal Party ID');
1831: l_pricing_attrib_tbl.delete;
1832: l_org_assignments_tbl.delete;
1833: l_asset_assignment_tbl.delete;
1834:
1835: 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);
1836:
1837: debug('Right after setting instance status');
1838: debug('Before Update item instance');
1839:
1899: END IF; -- Serialized Source Records
1900:
1901: ELSIF l_mtl_item_tbl(j).serial_number IS NULL THEN -- Non Serialized
1902:
1903: csi_inv_trxs_pkg.set_item_attr_query_values(l_mtl_item_tbl,
1904: j,
1905: NULL,
1906: l_instance_query_rec,
1907: x_return_status);
1952:
1953: IF l_src_instance_header_tbl.count = 0 THEN -- No Records found so Create Non Serialized Item
1954:
1955: debug('No source records were found so create a new one');
1956: l_new_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
1957: l_new_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1958: l_new_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1959: l_new_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
1960: l_new_instance_rec.inv_master_organization_id := l_master_organization_id;
1961: l_new_instance_rec.mfg_serial_number_flag := l_mfg_serial_flag;
1962: l_new_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1963: l_new_instance_rec.quantity := abs(l_mtl_item_tbl(j).transaction_quantity);
1964: l_new_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
1965: l_new_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
1966: l_new_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
1967: l_new_instance_rec.instance_usage_code := l_in_inventory;
1968: l_new_instance_rec.last_po_po_line_id := r_po_info.po_line_id; --5184815
1969: l_new_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
1975: l_new_instance_rec.operational_status_code := 'NOT_USED';
1976: l_new_instance_rec.active_start_date := l_sysdate;
1977: l_new_instance_rec.active_end_date := NULL;
1978:
1979: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
1980: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
1981: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
1982: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
1983: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
1976: l_new_instance_rec.active_start_date := l_sysdate;
1977: l_new_instance_rec.active_end_date := NULL;
1978:
1979: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
1980: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
1981: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
1982: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
1983: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
1984: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
1977: l_new_instance_rec.active_end_date := NULL;
1978:
1979: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
1980: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
1981: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
1982: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
1983: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
1984: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
1985:
1978:
1979: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
1980: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
1981: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
1982: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
1983: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
1984: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
1985:
1986: 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);
1979: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
1980: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
1981: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
1982: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
1983: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
1984: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
1985:
1986: 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);
1987:
1980: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
1981: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
1982: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
1983: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
1984: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
1985:
1986: 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);
1987:
1988: debug('Before Create of new source Item Instance');
1982: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
1983: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
1984: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
1985:
1986: 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);
1987:
1988: debug('Before Create of new source Item Instance');
1989:
1990: csi_item_instance_pub.create_item_instance(l_api_version,
2026:
2027: debug('Non Serialized Source records found');
2028: debug('Updating Item Instance: '||l_src_instance_header_tbl(i).instance_id);
2029:
2030: l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2031: l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
2032: l_update_instance_rec.quantity := l_src_instance_header_tbl(i).quantity + abs(l_mtl_item_tbl(j).primary_quantity);
2033: l_update_instance_rec.active_end_date := NULL;
2034: l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
2039: l_pricing_attrib_tbl.delete;
2040: l_org_assignments_tbl.delete;
2041: l_asset_assignment_tbl.delete;
2042:
2043: 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);
2044:
2045: debug('Right after setting instance status');
2046: debug('Before Update item instance');
2047:
2097: END IF; -- End of Serial Number if
2098: END LOOP; -- End of For Loop
2099:
2100: debug('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2101: debug('*****End of csi_inv_trxs_pkg.receipt_inventory Transaction*****');
2102:
2103: EXCEPTION
2104: WHEN fnd_api.g_exc_error THEN
2105: debug('You have encountered a "fnd_api.g_exc_error" exception');
2121: x_trx_error_rec.error_text := l_error_message;
2122: x_trx_error_rec.transaction_id := NULL;
2123: x_trx_error_rec.source_type := 'CSIPOINV';
2124: x_trx_error_rec.source_id := p_transaction_id;
2125: x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
2126: x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('PO_RECEIPT_INTO_INVENTORY','INV');
2127: x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
2128: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
2129:
2122: x_trx_error_rec.transaction_id := NULL;
2123: x_trx_error_rec.source_type := 'CSIPOINV';
2124: x_trx_error_rec.source_id := p_transaction_id;
2125: x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
2126: x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('PO_RECEIPT_INTO_INVENTORY','INV');
2127: x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
2128: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
2129:
2130: WHEN others THEN
2124: x_trx_error_rec.source_id := p_transaction_id;
2125: x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
2126: x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('PO_RECEIPT_INTO_INVENTORY','INV');
2127: x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
2128: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
2129:
2130: WHEN others THEN
2131: l_sql_error := SQLERRM;
2132: debug('You have encountered a "others" exception');
2152: x_trx_error_rec.error_text := fnd_message.get;
2153: x_trx_error_rec.transaction_id := NULL;
2154: x_trx_error_rec.source_type := 'CSIPOINV';
2155: x_trx_error_rec.source_id := p_transaction_id;
2156: x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
2157: x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('PO_RECEIPT_INTO_INVENTORY','INV');
2158: x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
2159: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
2160:
2153: x_trx_error_rec.transaction_id := NULL;
2154: x_trx_error_rec.source_type := 'CSIPOINV';
2155: x_trx_error_rec.source_id := p_transaction_id;
2156: x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
2157: x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('PO_RECEIPT_INTO_INVENTORY','INV');
2158: x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
2159: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
2160:
2161: END receipt_inventory;
2155: x_trx_error_rec.source_id := p_transaction_id;
2156: x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
2157: x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('PO_RECEIPT_INTO_INVENTORY','INV');
2158: x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
2159: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
2160:
2161: END receipt_inventory;
2162:
2163: PROCEDURE misc_issue(p_transaction_id IN NUMBER,
2165: x_return_status OUT NOCOPY VARCHAR2,
2166: x_trx_error_rec OUT NOCOPY CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC)
2167: IS
2168:
2169: l_mtl_item_tbl CSI_INV_TRXS_PKG.MTL_ITEM_TBL_TYPE;
2170: l_api_name VARCHAR2(100) := 'CSI_INV_TRXS_PKG.MISC_ISSUE';
2171: l_api_version NUMBER := 1.0;
2172: l_commit VARCHAR2(1) := FND_API.G_FALSE;
2173: l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
2166: x_trx_error_rec OUT NOCOPY CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC)
2167: IS
2168:
2169: l_mtl_item_tbl CSI_INV_TRXS_PKG.MTL_ITEM_TBL_TYPE;
2170: l_api_name VARCHAR2(100) := 'CSI_INV_TRXS_PKG.MISC_ISSUE';
2171: l_api_version NUMBER := 1.0;
2172: l_commit VARCHAR2(1) := FND_API.G_FALSE;
2173: l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
2174: l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
2203: l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2204: l_fnd_warning VARCHAR2(1) := 'W';
2205: l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
2206: l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
2207: l_in_inventory VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_INVENTORY;
2208: l_in_process VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_PROCESS;
2209: l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
2210: l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
2211: l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
2204: l_fnd_warning VARCHAR2(1) := 'W';
2205: l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
2206: l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
2207: l_in_inventory VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_INVENTORY;
2208: l_in_process VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_PROCESS;
2209: l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
2210: l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
2211: l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
2212: l_installed VARCHAR2(25) := CSI_INV_TRXS_PKG.G_INSTALLED;
2205: l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
2206: l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
2207: l_in_inventory VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_INVENTORY;
2208: l_in_process VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_PROCESS;
2209: l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
2210: l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
2211: l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
2212: l_installed VARCHAR2(25) := CSI_INV_TRXS_PKG.G_INSTALLED;
2213: l_transaction_error_id NUMBER;
2206: l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
2207: l_in_inventory VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_INVENTORY;
2208: l_in_process VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_PROCESS;
2209: l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
2210: l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
2211: l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
2212: l_installed VARCHAR2(25) := CSI_INV_TRXS_PKG.G_INSTALLED;
2213: l_transaction_error_id NUMBER;
2214: l_quantity NUMBER;
2207: l_in_inventory VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_INVENTORY;
2208: l_in_process VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_PROCESS;
2209: l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
2210: l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
2211: l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
2212: l_installed VARCHAR2(25) := CSI_INV_TRXS_PKG.G_INSTALLED;
2213: l_transaction_error_id NUMBER;
2214: l_quantity NUMBER;
2215: l_mfg_serial_flag VARCHAR2(1);
2208: l_in_process VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_PROCESS;
2209: l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
2210: l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
2211: l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
2212: l_installed VARCHAR2(25) := CSI_INV_TRXS_PKG.G_INSTALLED;
2213: l_transaction_error_id NUMBER;
2214: l_quantity NUMBER;
2215: l_mfg_serial_flag VARCHAR2(1);
2216: l_trans_status_code VARCHAR2(15);
2268:
2269: BEGIN
2270: x_return_status := l_fnd_success;
2271:
2272: debug('*****Start of csi_inv_trxs_pkg.misc_issue Transaction procedure*****');
2273: debug('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2274: debug('csiivtxb.pls 115.25');
2275: debug('Transaction You are Processing is: '||p_transaction_id);
2276:
2275: debug('Transaction You are Processing is: '||p_transaction_id);
2276:
2277: -- This procedure queries all of the Inventory Transaction Records and returns them
2278: -- as a table.
2279: csi_inv_trxs_pkg.get_transaction_recs(p_transaction_id,
2280: l_mtl_item_tbl,
2281: l_return_status,
2282: l_error_message);
2283:
2285: l_tbl_count := l_mtl_item_tbl.count;
2286: debug('Inventory Records Found: '||l_tbl_count);
2287:
2288: IF NOT l_return_status = l_fnd_success THEN
2289: debug('You have encountered an error in CSI_INV_TRXS_PKG.get_transaction_recs, Transaction ID: '||p_transaction_id);
2290: RAISE fnd_api.g_exc_error;
2291: END IF;
2292:
2293: -- Determine Trasaction Type
2335: debug('Trans Type Code: '||l_trans_type_code);
2336: debug('Trans App Code: '||l_trans_app_code);
2337:
2338: IF l_mtl_item_tbl(i).transaction_type_id IN (36,1005) THEN
2339: csi_inv_trxs_pkg.get_rtv_transaction_recs(p_transaction_id,
2340: l_mtl_item_tbl,
2341: l_return_status,
2342: l_error_message);
2343: END IF;
2341: l_return_status,
2342: l_error_message);
2343: END IF;
2344: -- Get the Master Organization ID
2345: csi_inv_trxs_pkg.get_master_organization(l_mtl_item_tbl(i).organization_id,
2346: l_master_organization_id,
2347: l_return_status,
2348: l_error_message);
2349:
2349:
2350: debug('Master Organization is: '||l_master_organization_id);
2351:
2352: IF NOT l_return_status = l_fnd_success THEN
2353: debug('You have encountered an error in csi_inv_trxs_pkg.get_master_organization, Organization ID: '||l_mtl_item_tbl(i).organization_id);
2354: RAISE fnd_api.g_exc_error;
2355: END IF;
2356:
2357: -- Call get_fnd_employee_id and get the employee id
2354: RAISE fnd_api.g_exc_error;
2355: END IF;
2356:
2357: -- Call get_fnd_employee_id and get the employee id
2358: l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
2359:
2360: IF l_employee_id = -1 THEN
2361: debug('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
2362: END IF;
2363:
2364: debug('The Employee that is processing this Transaction is: '||l_employee_id);
2365:
2366: -- See if this is a depreciable Item to set the status of the transaction record
2367: csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
2368: l_depreciable,
2369: l_mtl_item_tbl(i).organization_id); --Added for Bug 13988660
2370:
2371: debug('Is this Item ID: '||l_mtl_item_tbl(i).inventory_item_id||', Depreciable :'||l_depreciable);
2390:
2391: -- Get the Negative Receipt Code to see if this org allows Negative
2392: -- Quantity Records 1 = Yes, 2 = No
2393:
2394: l_neg_code := csi_inv_trxs_pkg.get_neg_inv_code(
2395: l_mtl_item_tbl(i).organization_id);
2396:
2397: IF l_neg_code = 1 AND l_mtl_item_tbl(i).serial_number is NULL THEN
2398: l_instance_status := FND_API.G_FALSE;
2402:
2403: debug('Negative Code is - 1 = Yes, 2 = No: '||l_neg_code);
2404:
2405: -- Initialize Transaction Record
2406: l_txn_rec := csi_inv_trxs_pkg.init_txn_rec;
2407:
2408: -- Set Status based on redeployment
2409: IF l_depreciable = 'N' THEN
2410: IF l_mtl_item_tbl(i).serial_number is NOT NULL THEN
2407:
2408: -- Set Status based on redeployment
2409: IF l_depreciable = 'N' THEN
2410: IF l_mtl_item_tbl(i).serial_number is NOT NULL THEN
2411: csi_inv_trxs_pkg.get_redeploy_flag(l_mtl_item_tbl(i).inventory_item_id,
2412: l_mtl_item_tbl(i).serial_number,
2413: l_sysdate,
2414: l_redeploy_flag,
2415: l_return_status,
2415: l_return_status,
2416: l_error_message);
2417: END IF;
2418: IF l_redeploy_flag = 'Y' THEN
2419: l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
2420: ELSE
2421: l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_complete;
2422: END IF;
2423: ELSE
2417: END IF;
2418: IF l_redeploy_flag = 'Y' THEN
2419: l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
2420: ELSE
2421: l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_complete;
2422: END IF;
2423: ELSE
2424: l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
2425: END IF;
2420: ELSE
2421: l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_complete;
2422: END IF;
2423: ELSE
2424: l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
2425: END IF;
2426:
2427: IF NOT l_return_status = l_fnd_success THEN
2428: debug('Redeploy Flag: '||l_redeploy_flag);
2425: END IF;
2426:
2427: IF NOT l_return_status = l_fnd_success THEN
2428: debug('Redeploy Flag: '||l_redeploy_flag);
2429: debug('You have encountered an error in csi_inv_trxs_pkg.get_redeploy_flag: '||l_error_message);
2430: RAISE fnd_api.g_exc_error;
2431: END IF;
2432:
2433: debug('Redeploy Flag: '||l_redeploy_flag);
2440:
2441: -- Create CSI Transaction to be used
2442: l_txn_rec.source_transaction_date := l_mtl_item_tbl(i).transaction_date;
2443: l_txn_rec.transaction_date := l_sysdate;
2444: l_txn_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
2445: l_txn_rec.transaction_quantity := l_mtl_item_tbl(i).transaction_quantity;
2446: l_txn_rec.transaction_uom_code := l_mtl_item_tbl(i).transaction_uom;
2447: l_txn_rec.transacted_by := l_employee_id;
2448: l_txn_rec.transaction_action_code := NULL;
2485: l_txn_rec.source_header_ref_id := l_mtl_item_tbl(i).transaction_source_id;
2486: l_txn_rec.source_line_ref_id := l_mtl_item_tbl(i).move_order_line_id;
2487: END IF;
2488:
2489: csi_inv_trxs_pkg.create_csi_txn(l_txn_rec,
2490: l_error_message,
2491: l_return_status);
2492:
2493: debug('CSI Transaction Created: '||l_txn_rec.transaction_id);
2492:
2493: debug('CSI Transaction Created: '||l_txn_rec.transaction_id);
2494:
2495: IF NOT l_return_status = l_fnd_success THEN
2496: debug('You have encountered an error in csi_inv_trxs_pkg.create_csi_txn: '||p_transaction_id);
2497: RAISE fnd_api.g_exc_error;
2498: END IF;
2499:
2500: -- Now loop through the PL/SQL Table.
2508: debug('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
2509: debug('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
2510: debug('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
2511:
2512: csi_inv_trxs_pkg.set_item_attr_query_values(l_mtl_item_tbl,
2513: j,
2514: NULL,
2515: l_instance_query_rec,
2516: x_return_status);
2584: -- No Instances so check to see if Neg Qtys Allowed to create source
2585:
2586: debug('No records were found and Neg Qtys allowed so create a new Source Instance Record');
2587:
2588: l_new_src_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
2589: l_new_src_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2590: l_new_src_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
2591: l_new_src_instance_rec.inv_master_organization_id := l_master_organization_id;
2592: l_new_src_instance_rec.mfg_serial_number_flag := 'N';
2592: l_new_src_instance_rec.mfg_serial_number_flag := 'N';
2593: l_new_src_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2594: l_new_src_instance_rec.quantity := l_mtl_item_tbl(j).transaction_quantity;
2595: l_new_src_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
2596: l_new_src_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
2597: l_new_src_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
2598: l_new_src_instance_rec.instance_usage_code := l_in_inventory;
2599: l_new_src_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
2600: l_new_src_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
2606: l_new_src_instance_rec.operational_status_code := 'NOT_USED';
2607: l_new_src_instance_rec.active_start_date := l_sysdate;
2608: l_new_src_instance_rec.active_end_date := NULL;
2609:
2610: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
2611: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
2612: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
2613: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
2614: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2607: l_new_src_instance_rec.active_start_date := l_sysdate;
2608: l_new_src_instance_rec.active_end_date := NULL;
2609:
2610: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
2611: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
2612: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
2613: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
2614: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2615: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2608: l_new_src_instance_rec.active_end_date := NULL;
2609:
2610: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
2611: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
2612: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
2613: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
2614: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2615: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2616:
2609:
2610: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
2611: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
2612: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
2613: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
2614: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2615: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2616:
2617: debug('Before Create of source Instance - Neg Qty');
2610: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
2611: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
2612: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
2613: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
2614: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2615: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2616:
2617: debug('Before Create of source Instance - Neg Qty');
2618:
2611: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
2612: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
2613: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
2614: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2615: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2616:
2617: debug('Before Create of source Instance - Neg Qty');
2618:
2619: csi_item_instance_pub.create_item_instance(l_api_version,
2660:
2661: debug('Update source record for non seralized item');
2662: debug('Update Source Non Serialized item: '||l_src_instance_header_tbl(i).instance_id);
2663:
2664: l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2665: l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
2666: l_update_instance_rec.active_end_date := NULL;
2667: l_update_instance_rec.quantity := l_src_instance_header_tbl(i).quantity - abs(l_mtl_item_tbl(i).primary_quantity);
2668: l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
2674: l_asset_assignment_tbl.delete;
2675:
2676: debug('Before Update Non Serialized Item Instance');
2677:
2678: 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);
2679:
2680: debug('Instance Status Id: '||l_update_instance_rec.instance_status_id);
2681:
2682: csi_item_instance_pub.update_item_instance(l_api_version,
2722: debug('Updating Serialized Item Instance');
2723: --R12 changes,Misc issue on serialized rebuildables/asset numbers leaves the instance in active state and
2724: --all inventory attributes made as null.
2725: debug('l_mtl_item_tbl(j).eam_item_type--'||l_mtl_item_tbl(j).eam_item_type);
2726: l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2727: l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
2728: l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
2729: l_update_instance_rec.quantity := 1;
2730:
2821: -- No Instances so check to see if Neg Qtys Allowed to create source
2822:
2823: debug('No records were found and Neg Qtys allowed so create a new Source Instance Record');
2824:
2825: l_new_src_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
2826: l_new_src_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2827: l_new_src_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
2828: l_new_src_instance_rec.inv_master_organization_id := l_master_organization_id;
2829: l_new_src_instance_rec.mfg_serial_number_flag := 'N';
2829: l_new_src_instance_rec.mfg_serial_number_flag := 'N';
2830: l_new_src_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2831: l_new_src_instance_rec.quantity := l_mtl_item_tbl(j).transaction_quantity;
2832: l_new_src_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
2833: l_new_src_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
2834: l_new_src_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
2835: l_new_src_instance_rec.instance_usage_code := l_in_inventory;
2836: l_new_src_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
2837: l_new_src_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
2843: l_new_src_instance_rec.operational_status_code := 'NOT_USED';
2844: l_new_src_instance_rec.active_start_date := l_sysdate;
2845: l_new_src_instance_rec.active_end_date := NULL;
2846:
2847: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
2848: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
2849: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
2850: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
2851: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2844: l_new_src_instance_rec.active_start_date := l_sysdate;
2845: l_new_src_instance_rec.active_end_date := NULL;
2846:
2847: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
2848: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
2849: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
2850: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
2851: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2852: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2845: l_new_src_instance_rec.active_end_date := NULL;
2846:
2847: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
2848: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
2849: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
2850: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
2851: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2852: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2853:
2846:
2847: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
2848: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
2849: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
2850: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
2851: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2852: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2853:
2854: debug('Before Create of source Instance - Neg Qty');
2847: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
2848: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
2849: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
2850: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
2851: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2852: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2853:
2854: debug('Before Create of source Instance - Neg Qty');
2855:
2848: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
2849: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
2850: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
2851: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2852: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2853:
2854: debug('Before Create of source Instance - Neg Qty');
2855:
2856: csi_item_instance_pub.create_item_instance(l_api_version,
2897: --decrement source Instance
2898: debug('Update source record for serial tagged item');
2899: debug('Update Source serial tagged item: '||l_src_instance_header_tbl(i).instance_id);
2900:
2901: l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2902: l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
2903: l_update_instance_rec.active_end_date := NULL;
2904: l_update_instance_rec.quantity := l_src_instance_header_tbl(i).quantity - 1;
2905: l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
2911: l_asset_assignment_tbl.delete;
2912:
2913: debug('Before Update Serial Tagged Item Instance');
2914:
2915: 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);
2916:
2917: debug('Instance Status Id: '||l_update_instance_rec.instance_status_id);
2918:
2919: csi_item_instance_pub.update_item_instance(l_api_version,
2942:
2943: -- Setting the search param for destination instance
2944: -- Querying only with serial number as it is serial tagged
2945: -- Bug 13371469
2946: l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
2947: --l_instance_query_rec.last_vld_organization_id := l_mtl_item_tbl(j).organization_id;
2948: l_instance_query_rec.serial_number := l_mtl_item_tbl(j).serial_number;
2949: l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2950: --l_instance_query_rec.instance_usage_code := l_in_inventory;
2947: --l_instance_query_rec.last_vld_organization_id := l_mtl_item_tbl(j).organization_id;
2948: l_instance_query_rec.serial_number := l_mtl_item_tbl(j).serial_number;
2949: l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2950: --l_instance_query_rec.instance_usage_code := l_in_inventory;
2951: --csi_inv_trxs_pkg.set_item_attr_query_values(l_mtl_item_tbl,
2952: -- j,
2953: -- NULL,
2954: -- l_instance_query_rec,
2955: -- x_return_status);
2996:
2997: IF l_tbl_count = 0 THEN
2998:
2999: --Create new destination Instance
3000: l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
3001: l_new_dest_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
3002: l_new_dest_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
3003: l_new_dest_instance_rec.inv_master_organization_id := l_master_organization_id;
3004: l_new_dest_instance_rec.mfg_serial_number_flag := 'Y';
3004: l_new_dest_instance_rec.mfg_serial_number_flag := 'Y';
3005: l_new_dest_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
3006: l_new_dest_instance_rec.quantity := 1;
3007: l_new_dest_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
3008: l_new_dest_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
3009: l_new_dest_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
3010: l_new_dest_instance_rec.instance_usage_code := l_in_inventory;
3011: l_new_dest_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
3012: l_new_dest_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
3019: l_new_dest_instance_rec.active_start_date := l_sysdate;
3020: l_new_dest_instance_rec.active_end_date := NULL;
3021: l_new_dest_instance_rec.serial_number := l_mtl_item_tbl(j).serial_number;
3022:
3023: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
3024: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
3025: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
3026: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
3027: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
3020: l_new_dest_instance_rec.active_end_date := NULL;
3021: l_new_dest_instance_rec.serial_number := l_mtl_item_tbl(j).serial_number;
3022:
3023: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
3024: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
3025: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
3026: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
3027: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
3028: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
3021: l_new_dest_instance_rec.serial_number := l_mtl_item_tbl(j).serial_number;
3022:
3023: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
3024: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
3025: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
3026: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
3027: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
3028: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
3029:
3022:
3023: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
3024: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
3025: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
3026: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
3027: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
3028: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
3029:
3030: debug('Before Create of Destination Instance');
3023: l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
3024: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
3025: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
3026: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
3027: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
3028: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
3029:
3030: debug('Before Create of Destination Instance');
3031:
3024: l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
3025: l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
3026: l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
3027: l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
3028: l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
3029:
3030: debug('Before Create of Destination Instance');
3031:
3032: csi_item_instance_pub.create_item_instance(l_api_version,
3062: debug('New instance created is: '||l_new_dest_instance_rec.instance_id);
3063:
3064: ELSE
3065: -- l_new_dest_instance_rec := l_dest_instance_header_tbl(1);
3066: l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
3067: l_new_dest_instance_rec.instance_id := l_dest_instance_header_tbl(1).instance_id;
3068: --l_new_dest_instance_rec.inventory_item_id := l_dest_instance_header_tbl(1).inventory_item_id;
3069: --l_new_dest_instance_rec.inv_master_organization_id := l_dest_instance_header_tbl(1).inv_master_organization_id;
3070: -- Put the quantit as 1 but in expired status
3128: END IF; -- End of Serial IF
3129: END LOOP; -- End of For Loop
3130:
3131: debug('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
3132: debug('*****End of csi_inv_trxs_pkg.misc_issue Transaction*****');
3133:
3134: EXCEPTION
3135: WHEN fnd_api.g_exc_error THEN
3136: debug('You have encountered a "fnd_api.g_exc_error" exception');
3152: x_trx_error_rec.error_text := l_error_message;
3153: x_trx_error_rec.transaction_id := NULL;
3154: x_trx_error_rec.source_type := 'CSIMSISU';
3155: x_trx_error_rec.source_id := p_transaction_id;
3156: x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
3157: x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
3158: x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
3159: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
3160:
3153: x_trx_error_rec.transaction_id := NULL;
3154: x_trx_error_rec.source_type := 'CSIMSISU';
3155: x_trx_error_rec.source_id := p_transaction_id;
3156: x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
3157: x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
3158: x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
3159: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
3160:
3161: WHEN others THEN
3155: x_trx_error_rec.source_id := p_transaction_id;
3156: x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
3157: x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
3158: x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
3159: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
3160:
3161: WHEN others THEN
3162: l_sql_error := SQLERRM;
3163: debug('You have encountered a "others" exception');
3183: x_trx_error_rec.error_text := fnd_message.get;
3184: x_trx_error_rec.transaction_id := NULL;
3185: x_trx_error_rec.source_type := 'CSIMSISU';
3186: x_trx_error_rec.source_id := p_transaction_id;
3187: x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
3188: x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
3189: x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
3190: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
3191:
3184: x_trx_error_rec.transaction_id := NULL;
3185: x_trx_error_rec.source_type := 'CSIMSISU';
3186: x_trx_error_rec.source_id := p_transaction_id;
3187: x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
3188: x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
3189: x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
3190: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
3191:
3192: END misc_issue;
3186: x_trx_error_rec.source_id := p_transaction_id;
3187: x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
3188: x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
3189: x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
3190: x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
3191:
3192: END misc_issue;
3193:
3194: PROCEDURE cycle_count(p_transaction_id IN NUMBER,
3196: x_return_status OUT NOCOPY VARCHAR2,
3197: x_trx_error_rec OUT NOCOPY CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC)
3198: IS
3199:
3200: l_api_name VARCHAR2(100) := 'CSI_INV_TRXS_PKG.CYCLE_COUNT';
3201: l_return_status VARCHAR2(1);
3202: l_error_code VARCHAR2(50);
3203: l_error_message VARCHAR2(4000);
3204: l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3217: BEGIN
3218:
3219: x_return_status := l_fnd_success;
3220:
3221: debug('*****Start of csi_inv_trxs_pkg.cycle_count Transaction*****');
3222: debug('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
3223: debug('csiivtxb.pls 115.25');
3224: debug('Transaction You are Processing is: '||p_transaction_id);
3225:
3227: fetch C_QUANTITY into R_QUANTITY;
3228: close C_QUANTITY;
3229:
3230: if r_quantity > 0 then
3231: csi_inv_trxs_pkg.misc_receipt(p_transaction_id,
3232: p_message_id,
3233: l_return_status,
3234: l_trx_error_rec);
3235:
3233: l_return_status,
3234: l_trx_error_rec);
3235:
3236: IF NOT l_return_status = l_fnd_success THEN
3237: debug('You have encountered an error in CSI_INV_TRXS_PKG.cycle_count');
3238: RAISE fnd_api.g_exc_error;
3239: END IF;
3240: ELSIF r_quantity < 0 then
3241: csi_inv_trxs_pkg.misc_issue(p_transaction_id,
3237: debug('You have encountered an error in CSI_INV_TRXS_PKG.cycle_count');
3238: RAISE fnd_api.g_exc_error;
3239: END IF;
3240: ELSIF r_quantity < 0 then
3241: csi_inv_trxs_pkg.misc_issue(p_transaction_id,
3242: p_message_id,
3243: l_return_status,
3244: l_trx_error_rec);
3245:
3243: l_return_status,
3244: l_trx_error_rec);
3245:
3246: IF NOT l_return_status = l_fnd_success THEN
3247: debug('You have encountered an error in CSI_INV_TRXS_PKG.cycle_count');
3248: RAISE fnd_api.g_exc_error;
3249: END IF;
3250: END IF;
3251:
3249: END IF;
3250: END IF;
3251:
3252: debug('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
3253: debug('*****End of csi_inv_trxs_pkg.cycle_count Transaction*****');
3254:
3255: EXCEPTION
3256: WHEN fnd_api.g_exc_error THEN
3257: debug('You have encountered a "fnd_api.g_exc_error" exception');
3270: l_trx_error_rec.error_text := fnd_message.get;
3271: l_trx_error_rec.transaction_id := NULL;
3272: l_trx_error_rec.source_type := 'CSICYCNT';
3273: l_trx_error_rec.source_id := p_transaction_id;
3274: l_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
3275: l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('CYCLE_COUNT','INV');
3276: l_trx_error_rec.inv_material_transaction_id := p_transaction_id;
3277: l_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
3278: x_trx_error_rec := l_trx_error_rec;
3271: l_trx_error_rec.transaction_id := NULL;
3272: l_trx_error_rec.source_type := 'CSICYCNT';
3273: l_trx_error_rec.source_id := p_transaction_id;
3274: l_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
3275: l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('CYCLE_COUNT','INV');
3276: l_trx_error_rec.inv_material_transaction_id := p_transaction_id;
3277: l_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
3278: x_trx_error_rec := l_trx_error_rec;
3279:
3273: l_trx_error_rec.source_id := p_transaction_id;
3274: l_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
3275: l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('CYCLE_COUNT','INV');
3276: l_trx_error_rec.inv_material_transaction_id := p_transaction_id;
3277: l_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
3278: x_trx_error_rec := l_trx_error_rec;
3279:
3280: END cycle_count;
3281:
3284: x_return_status OUT NOCOPY VARCHAR2,
3285: x_trx_error_rec OUT NOCOPY CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC)
3286: IS
3287:
3288: l_api_name VARCHAR2(100) := 'CSI_INV_TRXS_PKG.PHYSICAL_INVENTORY';
3289: l_return_status VARCHAR2(1);
3290: l_error_code VARCHAR2(50);
3291: l_error_message VARCHAR2(4000);
3292: l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3304:
3305: BEGIN
3306: x_return_status := l_fnd_success;
3307:
3308: debug('*****Start of csi_inv_trxs_pkg.physical_inventory Transaction*****');
3309: debug('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
3310: debug('csiorgtb.pls 115.25');
3311: debug('Transaction You are Processing is: '||p_transaction_id);
3312:
3314: fetch C_QUANTITY into R_QUANTITY;
3315: close C_QUANTITY;
3316:
3317: if r_quantity > 0 then
3318: csi_inv_trxs_pkg.misc_receipt(p_transaction_id,
3319: p_message_id,
3320: l_return_status,
3321: l_trx_error_rec);
3322:
3320: l_return_status,
3321: l_trx_error_rec);
3322:
3323: IF NOT l_return_status = l_fnd_success THEN
3324: debug('You have encountered an error in CSI_INV_TRXS_PKG.physical_inventory');
3325: RAISE fnd_api.g_exc_error;
3326: END IF;
3327: ELSIF r_quantity < 0 then
3328: csi_inv_trxs_pkg.misc_issue(p_transaction_id,
3324: debug('You have encountered an error in CSI_INV_TRXS_PKG.physical_inventory');
3325: RAISE fnd_api.g_exc_error;
3326: END IF;
3327: ELSIF r_quantity < 0 then
3328: csi_inv_trxs_pkg.misc_issue(p_transaction_id,
3329: p_message_id,
3330: l_return_status,
3331: l_trx_error_rec);
3332:
3330: l_return_status,
3331: l_trx_error_rec);
3332:
3333: IF NOT l_return_status = l_fnd_success THEN
3334: debug('You have encountered an error in CSI_INV_TRXS_PKG.physical_inventory');
3335: RAISE fnd_api.g_exc_error;
3336: END IF;
3337: END IF;
3338:
3336: END IF;
3337: END IF;
3338:
3339: debug('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
3340: debug('*****End of csi_inv_trxs_pkg.physical_inventory Transaction*****');
3341:
3342: EXCEPTION
3343: WHEN fnd_api.g_exc_error THEN
3344: debug('You have encountered a "fnd_api.g_exc_error" exception');
3357: l_trx_error_rec.error_text := fnd_message.get;
3358: l_trx_error_rec.transaction_id := NULL;
3359: l_trx_error_rec.source_type := 'CSIPHYIN';
3360: l_trx_error_rec.source_id := p_transaction_id;
3361: l_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
3362: l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('PHYSICAL_INVENTORY','INV');
3363: l_trx_error_rec.inv_material_transaction_id := p_transaction_id;
3364: l_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
3365: x_trx_error_rec := l_trx_error_rec;
3358: l_trx_error_rec.transaction_id := NULL;
3359: l_trx_error_rec.source_type := 'CSIPHYIN';
3360: l_trx_error_rec.source_id := p_transaction_id;
3361: l_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
3362: l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('PHYSICAL_INVENTORY','INV');
3363: l_trx_error_rec.inv_material_transaction_id := p_transaction_id;
3364: l_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
3365: x_trx_error_rec := l_trx_error_rec;
3366:
3360: l_trx_error_rec.source_id := p_transaction_id;
3361: l_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
3362: l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('PHYSICAL_INVENTORY','INV');
3363: l_trx_error_rec.inv_material_transaction_id := p_transaction_id;
3364: l_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
3365: x_trx_error_rec := l_trx_error_rec;
3366:
3367: END physical_inventory;
3368:
3366:
3367: END physical_inventory;
3368:
3369: PROCEDURE get_rtv_transaction_recs(p_transaction_id IN NUMBER,
3370: x_mtl_item_tbl OUT NOCOPY CSI_INV_TRXS_PKG.MTL_ITEM_TBL_TYPE,
3371: x_return_status OUT NOCOPY VARCHAR2,
3372: x_error_message OUT NOCOPY VARCHAR2)
3373: IS
3374:
3371: x_return_status OUT NOCOPY VARCHAR2,
3372: x_error_message OUT NOCOPY VARCHAR2)
3373: IS
3374:
3375: l_api_name VARCHAR2(100) := 'CSI_INV_TRXS_PKG.GET_RTV_TRANSACTION_RECS';
3376: l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3377: l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
3378: l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
3379: l_sql_error VARCHAR2(2000);
3584: x_return_status := l_fnd_unexpected;
3585: END get_rtv_transaction_recs;
3586:
3587: PROCEDURE get_transaction_recs(p_transaction_id IN NUMBER,
3588: x_mtl_item_tbl OUT NOCOPY CSI_INV_TRXS_PKG.MTL_ITEM_TBL_TYPE,
3589: x_return_status OUT NOCOPY VARCHAR2,
3590: x_error_message OUT NOCOPY VARCHAR2)
3591: IS
3592:
3589: x_return_status OUT NOCOPY VARCHAR2,
3590: x_error_message OUT NOCOPY VARCHAR2)
3591: IS
3592:
3593: l_api_name VARCHAR2(100) := 'CSI_INV_TRXS_PKG.GET_TRANSACTION_RECS';
3594: l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3595: l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
3596: l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
3597: l_sql_error VARCHAR2(2000);
3810: PROCEDURE decode_message (p_msg_header IN XNP_MESSAGE.MSG_HEADER_REC_TYPE,
3811: p_msg_text IN VARCHAR2,
3812: x_return_status OUT NOCOPY VARCHAR2,
3813: x_error_message OUT NOCOPY VARCHAR2,
3814: x_mtl_trx_rec OUT NOCOPY CSI_INV_TRXS_PKG.MTL_TRX_TYPE) IS
3815:
3816: l_api_name VARCHAR2(100) := 'CSI_INV_TRXS_PKG.DECODE_MESSAGE';
3817: l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
3818: l_sql_error VARCHAR2(2000);
3812: x_return_status OUT NOCOPY VARCHAR2,
3813: x_error_message OUT NOCOPY VARCHAR2,
3814: x_mtl_trx_rec OUT NOCOPY CSI_INV_TRXS_PKG.MTL_TRX_TYPE) IS
3815:
3816: l_api_name VARCHAR2(100) := 'CSI_INV_TRXS_PKG.DECODE_MESSAGE';
3817: l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
3818: l_sql_error VARCHAR2(2000);
3819:
3820: BEGIN
3852: IS
3853: -- Enter the procedure variables here. As shown below
3854: -- variable_name datatype NOT NULL DEFAULT default_value ;
3855: l_err_text VARCHAR2(2000);
3856: l_api_name VARCHAR2(200) := 'CSI_INV_TRXS_PKG.GET_ASSET_CREATION_CODE';
3857: /*Commented for Bug 13988660
3858: CURSOR Asset_CC_Cur (P_Item_Id IN NUMBER) IS
3859: SELECT DISTINCT asset_creation_code
3860: FROM mtl_system_items
3902: -- Enter the procedure variables here. As shown below
3903: -- variable_name datatype NOT NULL DEFAULT default_value ;
3904: yes_or_no VARCHAR2(2) := 'N';
3905: l_err_text VARCHAR2(2000);
3906: l_api_name VARCHAR2(200) := 'CSI_INV_TRXS_PKG.CHECK_ITEM_TRACKABLE';
3907: CURSOR NL_TRACK_CUR(P_Item_Id IN NUMBER) IS
3908: SELECT DISTINCT 'Y'
3909: FROM mtl_system_items
3910: WHERE inventory_item_id = p_item_id
3943: -- Enter the procedure variables here. As shown below
3944: -- variable_name datatype NOT NULL DEFAULT default_value ;
3945: l_asset_creation_code VARCHAR2(1);
3946: l_err_text VARCHAR2(2000);
3947: l_api_name VARCHAR2(200) := 'CSI_INV_TRXS_PKG.CHECK_DEPRECIABLE';
3948:
3949: BEGIN
3950: csi_inv_trxs_pkg.Get_Asset_Creation_Code(
3951: p_inventory_item_id,
3946: l_err_text VARCHAR2(2000);
3947: l_api_name VARCHAR2(200) := 'CSI_INV_TRXS_PKG.CHECK_DEPRECIABLE';
3948:
3949: BEGIN
3950: csi_inv_trxs_pkg.Get_Asset_Creation_Code(
3951: p_inventory_item_id,
3952: l_asset_creation_code,
3953: p_organization_id); --Added for Bug 13988660
3954: IF l_asset_creation_code NOT IN ('1','Y') OR
3972: l_csi_installed VARCHAR2(1) := 'N' ;
3973: dummy VARCHAR2(40);
3974: ret BOOLEAN;
3975: BEGIN
3976: IF (csi_inv_trxs_pkg.x_csi_install is NULL)
3977: THEN
3978: ret := fnd_installation.get_app_info('CSI',
3979: csi_inv_trxs_pkg.x_csi_install, dummy, dummy);
3980: END IF;
3975: BEGIN
3976: IF (csi_inv_trxs_pkg.x_csi_install is NULL)
3977: THEN
3978: ret := fnd_installation.get_app_info('CSI',
3979: csi_inv_trxs_pkg.x_csi_install, dummy, dummy);
3980: END IF;
3981:
3982: IF (csi_inv_trxs_pkg.x_csi_install = 'I')
3983: THEN
3978: ret := fnd_installation.get_app_info('CSI',
3979: csi_inv_trxs_pkg.x_csi_install, dummy, dummy);
3980: END IF;
3981:
3982: IF (csi_inv_trxs_pkg.x_csi_install = 'I')
3983: THEN
3984: l_csi_installed := 'Y';
3985: ELSE
3986: l_csi_installed := 'N';
4347:
4348: FUNCTION Init_Txn_Error_Rec RETURN CSI_DATASTRUCTURES_PUB.TRANSACTION_Error_Rec IS
4349: l_Txn_Error_Rec CSI_DATASTRUCTURES_PUB.TRANSACTION_Error_Rec;
4350: BEGIN
4351: l_Txn_Error_Rec.processed_flag := CSI_INV_TRXS_PKG.G_TXN_ERROR;
4352: RETURN l_Txn_Error_Rec;
4353: END Init_Txn_Error_Rec;
4354:
4355: FUNCTION Init_Party_Query_Rec RETURN CSI_DATASTRUCTURES_PUB.Party_Query_Rec IS
4521: BEGIN
4522:
4523: l_trx_error_rec := p_trx_error_rec;
4524:
4525: l_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
4526: l_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
4527:
4528: csi_transactions_pvt.create_txn_error
4529: (l_api_version, l_init_msg_list, l_commit, l_validation_level,
4522:
4523: l_trx_error_rec := p_trx_error_rec;
4524:
4525: l_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
4526: l_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
4527:
4528: csi_transactions_pvt.create_txn_error
4529: (l_api_version, l_init_msg_list, l_commit, l_validation_level,
4530: l_trx_error_rec, l_return_status, l_msg_count,l_msg_data,
4798: l_source_type := NULL;
4799: l_type_id := NULL;
4800:
4801: -- Get CSI Txn Name for Error
4802: l_csi_txn_name := csi_inv_trxs_pkg.get_inv_name(r_mtl_data.transaction_id);
4803:
4804: -- Get Type Class Code
4805: OPEN c_type_class(r_mtl_data.transaction_type_id);
4806: FETCH c_type_class into r_type_class;
5217:
5218: END; -- valid_ib_txn
5219:
5220: PROCEDURE set_item_attr_query_values(
5221: l_mtl_item_tbl IN CSI_INV_TRXS_PKG.MTL_ITEM_TBL_TYPE,
5222: table_index IN NUMBER,
5223: p_source IN VARCHAR2,
5224: x_instance_query_rec OUT NOCOPY csi_datastructures_pub.instance_query_rec,
5225: x_return_status OUT NOCOPY varchar2)
5234: x_return_status := fnd_api.g_ret_sts_success;
5235:
5236: debug('Setting Item Control Attributes on Query Record');
5237:
5238: l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
5239: l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(table_index).inventory_item_id;
5240:
5241: -- Serial Control and if Non Serial all other controls are checked. If this is serial we just set that
5242: -- and exit since the query is just by item/serial
5313: x_return_status := fnd_api.g_ret_sts_error;
5314:
5315: END set_item_attr_query_values;
5316:
5317: END csi_inv_trxs_pkg;