[Home] [Help]
PACKAGE BODY: APPS.CSI_INV_HZ_PKG
Source
1 package body CSI_INV_HZ_PKG as
2 -- $Header: csiivtzb.pls 120.13.12020000.2 2012/07/04 10:28:08 sjawaji ship $
3
4 l_debug NUMBER := csi_t_gen_utility_pvt.g_debug_level;
5
6 PROCEDURE issue_to_hz_loc(p_transaction_id IN NUMBER,
7 p_message_id IN NUMBER,
8 x_return_status OUT NOCOPY VARCHAR2,
9 x_trx_error_rec OUT NOCOPY CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC)
10 IS
11
12 l_mtl_item_tbl CSI_INV_TRXS_PKG.MTL_ITEM_TBL_TYPE;
13 l_api_name VARCHAR2(100) := 'CSI_INV_HZ_PKG.ISSUE_TO_HZ_LOC';
14 l_api_version NUMBER := 1.0;
15 l_commit VARCHAR2(1) := FND_API.G_FALSE;
16 l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
17 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
18 l_active_instance_only VARCHAR2(10) := FND_API.G_TRUE;
19 l_inactive_instance_only VARCHAR2(10) := FND_API.G_FALSE;
20 l_transaction_id NUMBER := NULL;
21 l_resolve_id_columns VARCHAR2(10) := FND_API.G_FALSE;
22 l_object_version_number NUMBER := 1;
23 l_sysdate DATE := SYSDATE;
24 l_master_organization_id NUMBER;
25 l_depreciable VARCHAR2(1);
26 l_instance_query_rec CSI_DATASTRUCTURES_PUB.INSTANCE_QUERY_REC;
27 l_update_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
28 l_update_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
32 l_new_src_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
29 l_update_source_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
30 l_new_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
31 l_new_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
33 l_txn_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_REC;
34 l_return_status VARCHAR2(1);
35 l_error_code VARCHAR2(50);
36 l_error_message VARCHAR2(4000);
37 l_instance_id_lst CSI_DATASTRUCTURES_PUB.ID_TBL;
38 l_party_query_rec CSI_DATASTRUCTURES_PUB.PARTY_QUERY_REC;
39 l_account_query_rec CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_QUERY_REC;
40 l_src_instance_header_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_HEADER_TBL;
41 l_dest_instance_header_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_HEADER_TBL;
42 l_ext_attrib_values_tbl CSI_DATASTRUCTURES_PUB.EXTEND_ATTRIB_VALUES_TBL;
43 l_party_tbl CSI_DATASTRUCTURES_PUB.PARTY_TBL;
44 l_account_tbl CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_TBL;
45 l_pricing_attrib_tbl CSI_DATASTRUCTURES_PUB.PRICING_ATTRIBS_TBL;
46 l_org_assignments_tbl CSI_DATASTRUCTURES_PUB.ORGANIZATION_UNITS_TBL;
47 l_asset_assignment_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_ASSET_TBL;
48 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
49 l_fnd_warning VARCHAR2(1) := 'W';
50 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
51 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
52 l_in_inventory VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_INVENTORY;
53 l_in_process VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_PROCESS;
54 l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
55 l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
56 l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
57 l_installed VARCHAR2(25) := CSI_INV_TRXS_PKG.G_INSTALLED;
58 l_hz_loc_code VARCHAR2(25) := 'HZ_LOCATIONS';
59 l_transaction_error_id NUMBER;
60 l_quantity NUMBER;
61 l_mfg_serial_flag VARCHAR2(1);
62 l_ins_number VARCHAR2(100);
63 l_ins_id NUMBER;
64 l_file VARCHAR2(500);
65 l_trx_type_id NUMBER;
66 l_msg_count NUMBER;
67 l_msg_data VARCHAR2(2000);
68 l_msg_index NUMBER;
69 l_employee_id NUMBER;
70 j PLS_INTEGER;
71 i PLS_INTEGER := 1;
72 l_tbl_count NUMBER := 0;
73 l_neg_code NUMBER := 0;
74 l_instance_status VARCHAR2(1);
75 l_redeploy_flag VARCHAR2(1);
76 l_upd_error_instance_id NUMBER := NULL;
77 l_hz_location NUMBER := NULL;
78 l_hr_location NUMBER := NULL;
79 l_location_type VARCHAR2(50);
80
81 cursor c_id is
82 SELECT instance_status_id
83 FROM csi_instance_statuses
84 WHERE name = FND_PROFILE.VALUE('CSI_DEFAULT_INSTANCE_STATUS');
85
86 r_id c_id%rowtype;
87
88 cursor c_hz_loc (pc_location_id IN NUMBER) is
89 SELECT 1
90 FROM hz_locations
91 WHERE location_id = pc_location_id;
92
93 cursor c_hr_loc (pc_location_id IN NUMBER) is
94 SELECT 1
95 FROM hr_locations
96 WHERE location_id = pc_location_id;
97
98 BEGIN
99
100 x_return_status := l_fnd_success;
101
102 IF (l_debug > 0) THEN
103 csi_t_gen_utility_pvt.add('*****Start of csi_inv_hz_pkg.issue_to_hz_loc Transaction procedure*****');
104 csi_t_gen_utility_pvt.add('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
105 csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
106 csi_t_gen_utility_pvt.add('csiivtzb.pls 115.14');
107 END IF;
108
109 -- This procedure queries all of the Inventory Transaction Records and
110 -- returns them as a table.
111
112 csi_inv_trxs_pkg.get_transaction_recs(p_transaction_id,
113 l_mtl_item_tbl,
114 l_return_status,
115 l_error_message);
116
117 l_tbl_count := 0;
118 l_tbl_count := l_mtl_item_tbl.count;
119 IF (l_debug > 0) THEN
120 csi_t_gen_utility_pvt.add('Inventory Records Found: '||l_tbl_count);
121 END IF;
122
123 IF NOT l_return_status = l_fnd_success THEN
124 IF (l_debug > 0) THEN
125 csi_t_gen_utility_pvt.add('You have encountered an error in CSI_INV_TRXS_PKG.get_transaction_recs, Transaction ID: '||p_transaction_id);
126 END IF;
127 RAISE fnd_api.g_exc_error;
128 END IF;
129
130 -- Get the Master Organization ID
131 csi_inv_trxs_pkg.get_master_organization(l_mtl_item_tbl(i).organization_id,
132 l_master_organization_id,
133 l_return_status,
134 l_error_message);
135
136 IF NOT l_return_status = l_fnd_success THEN
137 IF (l_debug > 0) THEN
138 csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.get_master_organization, Organization ID: '||l_mtl_item_tbl(i).organization_id);
139 END IF;
140 RAISE fnd_api.g_exc_error;
141 END IF;
142
146 IF l_employee_id = -1 THEN
143 -- Call get_fnd_employee_id and get the employee id
144 l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
145
147 IF (l_debug > 0) THEN
148 csi_t_gen_utility_pvt.add('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
149 END IF;
150 END IF;
151 IF (l_debug > 0) THEN
152 csi_t_gen_utility_pvt.add('The Employee that is processing this Transaction is: '||l_employee_id);
153 END IF;
154
155 -- See if this is a depreciable Item to set the status of the transaction record
156 csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
157 l_depreciable,
158 l_mtl_item_tbl(i).organization_id); --Added for Bug 13988660
159
160 IF (l_debug > 0) THEN
161 csi_t_gen_utility_pvt.add('Is this Item ID: '||l_mtl_item_tbl(i).inventory_item_id||', Depreciable :'||l_depreciable);
162 END IF;
163
164 -- Set the mfg_serial_number_flag and quantity
165 IF l_mtl_item_tbl(i).serial_number is NULL THEN
166 l_mfg_serial_flag := 'N';
167 l_quantity := l_mtl_item_tbl(i).transaction_quantity;
168 ELSE
169 l_mfg_serial_flag := 'Y';
170 l_quantity := 1;
171 END IF;
172
173 -- Now loop through the PL/SQL Table.
174 j := 1;
175
176 -- Get the Negative Receipt Code to see if this org allows Negative
177 -- Quantity Records 1 = Yes, 2 = No
178
179 l_neg_code := csi_inv_trxs_pkg.get_neg_inv_code(
180 l_mtl_item_tbl(i).organization_id);
181
182 IF l_neg_code = 1 AND l_mtl_item_tbl(i).serial_number is NULL THEN
183 l_instance_status := FND_API.G_FALSE;
184 ELSE
185 l_instance_status := FND_API.G_TRUE;
186 END IF;
187
188 IF (l_debug > 0) THEN
189 csi_t_gen_utility_pvt.add('Negative Code is - 1 = Yes, 2 = No: '||l_neg_code);
190 END IF;
191
192 IF (l_debug > 0) THEN
193 csi_t_gen_utility_pvt.add('Starting to loop through Material Transaction Records');
194 END IF;
195
196 -- Get Default Status ID
197 OPEN c_id;
198 FETCH c_id into r_id;
199 CLOSE c_id;
200
201 -- Decide if Location ID is from HZ or HR (Before txn Creation)
202
203 open c_hz_loc (l_mtl_item_tbl(i).ship_to_location_id);
204 fetch c_hz_loc into l_hz_location;
205
206 IF l_hz_location IS NOT NULL THEN
207 l_location_type := 'HZ_LOCATIONS';
208 close c_hz_loc;
209 ELSE
210 close c_hz_loc;
211 open c_hr_loc (l_mtl_item_tbl(i).ship_to_location_id);
212 fetch c_hr_loc into l_hr_location;
213 IF l_hr_location IS NOT NULL THEN
214 l_location_type := 'INTERNAL_SITE';
215 close c_hr_loc;
216 END IF;
217 END IF;
218
219 IF (l_debug > 0) THEN
220 csi_t_gen_utility_pvt.add('Location ID - Location Type; '||l_mtl_item_tbl(i).ship_to_location_id||'-'||l_location_type);
221 END IF;
222
223 -- Create CSI Transaction to be used
224 l_txn_rec := csi_inv_trxs_pkg.init_txn_rec;
225 l_txn_rec.source_transaction_date := l_mtl_item_tbl(i).transaction_date;
226 l_txn_rec.transaction_date := l_sysdate;
227 l_txn_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('ISSUE_TO_HZ_LOC','INV');
228 l_txn_rec.transaction_quantity := l_mtl_item_tbl(i).transaction_quantity;
229 l_txn_rec.transaction_uom_code := l_mtl_item_tbl(i).transaction_uom;
230 l_txn_rec.transacted_by := l_employee_id;
231 l_txn_rec.transaction_action_code := NULL;
232 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
233 l_txn_rec.message_id := p_message_id;
234 l_txn_rec.inv_material_transaction_id := p_transaction_id;
235 l_txn_rec.object_version_number := l_object_version_number;
236 l_txn_rec.source_group_ref := l_location_type;
237 l_txn_rec.source_header_ref_id := l_mtl_item_tbl(i).transaction_source_id;
238 l_txn_rec.source_line_ref_id := l_mtl_item_tbl(i).move_order_line_id;
239
240 csi_inv_trxs_pkg.create_csi_txn(l_txn_rec,
241 l_error_message,
242 l_return_status);
243
244 IF (l_debug > 0) THEN
245 csi_t_gen_utility_pvt.add('CSI Transaction Created: '||l_txn_rec.transaction_id);
246 END IF;
247
248 IF NOT l_return_status = l_fnd_success THEN
249 IF (l_debug > 0) THEN
250 csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.create_csi_txn: '||p_transaction_id);
251 END IF;
252 RAISE fnd_api.g_exc_error;
253 END IF;
254
255 FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
256
257 IF (l_debug > 0) THEN
258 csi_t_gen_utility_pvt.add('Trans Status Code: '||l_txn_rec.transaction_status_code);
259 csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
260 csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
261 csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
262 csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
263 END IF;
264
268 l_instance_query_rec.serial_number := l_mtl_item_tbl(j).serial_number;
265 l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
266 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
267 l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
269 l_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
270 l_instance_query_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
271 l_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
272 l_instance_query_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
273 --l_instance_query_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
274 l_instance_query_rec.instance_usage_code := l_in_inventory;
275
276 IF (l_debug > 0) THEN
277 csi_t_gen_utility_pvt.add('Ship to Location ID: '||l_mtl_item_tbl(j).ship_to_location_id);
278 csi_t_gen_utility_pvt.add('Before Get Item Instance');
279 END IF;
280
281 csi_item_instance_pub.get_item_instances(l_api_version,
282 l_commit,
283 l_init_msg_list,
284 l_validation_level,
285 l_instance_query_rec,
286 l_party_query_rec,
287 l_account_query_rec,
288 l_transaction_id,
289 l_resolve_id_columns,
290 l_instance_status,
291 l_src_instance_header_tbl,
292 l_return_status,
293 l_msg_count,
294 l_msg_data);
295
296 IF (l_debug > 0) THEN
297 csi_t_gen_utility_pvt.add('After Get Item Instance');
298 END IF;
299 l_tbl_count := 0;
300 l_tbl_count := l_src_instance_header_tbl.count;
301 IF (l_debug > 0) THEN
302 csi_t_gen_utility_pvt.add('Source Records Found: '||l_tbl_count);
303 END IF;
304 -- Check for any errors and add them to the message stack to pass out to be put into the
305 -- error log table.
306 IF NOT l_return_status = l_fnd_success then
307 IF (l_debug > 0) THEN
308 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
309 END IF;
310 l_msg_index := 1;
311 WHILE l_msg_count > 0 loop
312 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
313 l_msg_index := l_msg_index + 1;
314 l_msg_count := l_msg_count - 1;
315 END LOOP;
316 RAISE fnd_api.g_exc_error;
317 END IF;
318
319
320 IF l_mtl_item_tbl(j).serial_number is NULL THEN
321 IF l_src_instance_header_tbl.count = 0 THEN
322 IF l_neg_code = 1 THEN -- Allow Neg Qtys on NON Serial Items ONLY
323
324 IF (l_debug > 0) THEN
325 csi_t_gen_utility_pvt.add('No Source Records are found but negative qtys are allowed so create a negative qty source record');
326 END IF;
327
328 l_new_src_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
329 l_new_src_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
330 l_new_src_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
331 l_new_src_instance_rec.inv_master_organization_id := l_master_organization_id;
332 l_new_src_instance_rec.mfg_serial_number_flag := 'N';
333 l_new_src_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
334 l_new_src_instance_rec.quantity := l_mtl_item_tbl(j).transaction_quantity;
335 l_new_src_instance_rec.active_start_date := l_sysdate;
336 l_new_src_instance_rec.active_end_date := NULL;
337 l_new_src_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
338 l_new_src_instance_rec.instance_usage_code := l_in_inventory;
339 l_new_src_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
340 l_new_src_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
341 l_new_src_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
342 l_new_src_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
343 l_new_src_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
344 l_new_src_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
345 l_new_src_instance_rec.customer_view_flag := 'N';
346 l_new_src_instance_rec.merchant_view_flag := 'Y';
347 l_new_src_instance_rec.operational_status_code := 'NOT_USED';
348 l_new_src_instance_rec.object_version_number := l_object_version_number;
349
350 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
351 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
352 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
353 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
354 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
355 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
359 END IF;
356
357 IF (l_debug > 0) THEN
358 csi_t_gen_utility_pvt.add('Before Create Source Item Instance');
360
361 csi_item_instance_pub.create_item_instance(l_api_version,
362 l_commit,
363 l_init_msg_list,
364 l_validation_level,
365 l_new_src_instance_rec,
366 l_ext_attrib_values_tbl,
367 l_party_tbl,
368 l_account_tbl,
369 l_pricing_attrib_tbl,
370 l_org_assignments_tbl,
371 l_asset_assignment_tbl,
372 l_txn_rec,
373 l_return_status,
374 l_msg_count,
375 l_msg_data);
376
377 IF (l_debug > 0) THEN
378 csi_t_gen_utility_pvt.add('After Create Source Item Instance');
379 END IF;
380
381 IF (l_debug > 0) THEN
382 csi_t_gen_utility_pvt.add('After Create of Source Item Instance');
383 csi_t_gen_utility_pvt.add('New instance created is: '||l_new_src_instance_rec.instance_id);
384 END IF;
385
386 -- Check for any errors and add them to the message stack to pass out to be put into the
387 -- error log table.
388 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
389 IF (l_debug > 0) THEN
390 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
391 END IF;
392 l_msg_index := 1;
393 WHILE l_msg_count > 0 loop
394 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
395 l_msg_index := l_msg_index + 1;
396 l_msg_count := l_msg_count - 1;
397 END LOOP;
398 RAISE fnd_api.g_exc_error;
399 END IF;
400
401 ELSE -- No Records were found and Neg Qtys Not Allowed
402 IF (l_debug > 0) THEN
403 csi_t_gen_utility_pvt.add('No Records were found in Install Base and Neg Qtys not allowed to error');
404 END IF;
405 fnd_message.set_name('CSI','CSI_NO_NEG_BAL_ALLOWED');
406 l_error_message := fnd_message.get;
407 RAISE fnd_api.g_exc_error;
408
409 END IF; -- End of Source Record If
410
411 ELSIF l_src_instance_header_tbl.count = 1 THEN
412
413 IF (l_debug > 0) THEN
414 csi_t_gen_utility_pvt.add('You will update instance: '||l_src_instance_header_tbl(i).instance_id);
415 END IF;
416
417 l_update_source_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
418 l_update_source_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
419 l_update_source_instance_rec.quantity := l_src_instance_header_tbl(i).quantity - abs(l_mtl_item_tbl(j).primary_quantity);
420 l_update_source_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
421
422 l_party_tbl.delete;
423 l_account_tbl.delete;
424 l_pricing_attrib_tbl.delete;
425 l_org_assignments_tbl.delete;
426 l_asset_assignment_tbl.delete;
427
428 l_update_source_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
429
430
431 IF (l_debug > 0) THEN
432 csi_t_gen_utility_pvt.add('Before Update Item Instance');
433 END IF;
434
435 csi_item_instance_pub.update_item_instance(l_api_version,
436 l_commit,
437 l_init_msg_list,
438 l_validation_level,
439 l_update_source_instance_rec,
440 l_ext_attrib_values_tbl,
441 l_party_tbl,
442 l_account_tbl,
443 l_pricing_attrib_tbl,
444 l_org_assignments_tbl,
445 l_asset_assignment_tbl,
446 l_txn_rec,
447 l_instance_id_lst,
448 l_return_status,
449 l_msg_count,
450 l_msg_data);
451
452 l_upd_error_instance_id := NULL;
453 l_upd_error_instance_id := l_update_source_instance_rec.instance_id;
454
455 IF (l_debug > 0) THEN
456 csi_t_gen_utility_pvt.add('After Update Item Instance');
457 csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
458 END IF;
459
460 IF (l_debug > 0) THEN
461 csi_t_gen_utility_pvt.add('Instance Status Id: '||l_update_source_instance_rec.instance_status_id);
462 END IF;
463
467 IF (l_debug > 0) THEN
464 -- Check for any errors and add them to the message stack to pass out to be put into the
465 -- error log table.
466 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
468 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
469 END IF;
470 l_msg_index := 1;
471 WHILE l_msg_count > 0 loop
472 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
473 l_msg_index := l_msg_index + 1;
474 l_msg_count := l_msg_count - 1;
475 END LOOP;
476 RAISE fnd_api.g_exc_error;
477 END IF;
478
479 ELSIF l_src_instance_header_tbl.count > 1 THEN
480 -- Multiple Instances were found so throw error
481 IF (l_debug > 0) THEN
482 csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
483 END IF;
484 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
485 fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
486 fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
487 fnd_message.set_token('INV_ORG_ID',l_mtl_item_tbl(j).organization_id);
488 fnd_message.set_token('LOCATOR',l_mtl_item_tbl(j).locator_id);
489 l_error_message := fnd_message.get;
490 RAISE fnd_api.g_exc_error;
491
492 END IF; -- End of Source Record IF
493
494 -- Now query and get the destination record.
495 l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
496 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
497 l_instance_query_rec.location_id := l_mtl_item_tbl(j).ship_to_location_id;
498 --l_instance_query_rec.location_type_code := l_hz_loc_code;
499 l_instance_query_rec.location_type_code := l_location_type;
500 l_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
501 l_instance_query_rec.pa_project_id := NULL;
502 l_instance_query_rec.pa_project_task_id := NULL;
503 -- l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
504 l_instance_query_rec.instance_usage_code := l_in_service;
505
506 IF (l_debug > 0) THEN
507 csi_t_gen_utility_pvt.add('Before Destination Get Item Instance - Neg Qty');
508 csi_t_gen_utility_pvt.add('Ship to Location ID: '||l_mtl_item_tbl(j).ship_to_location_id);
509 csi_t_gen_utility_pvt.add('Location Type Code: '||l_location_type);
510 END IF;
511
512 csi_item_instance_pub.get_item_instances(l_api_version,
513 l_commit,
514 l_init_msg_list,
515 l_validation_level,
516 l_instance_query_rec,
517 l_party_query_rec,
518 l_account_query_rec,
519 l_transaction_id,
520 l_resolve_id_columns,
521 l_inactive_instance_only,
522 l_dest_instance_header_tbl,
523 l_return_status,
524 l_msg_count,
525 l_msg_data);
526
527 IF (l_debug > 0) THEN
528 csi_t_gen_utility_pvt.add('After Destination Get Item Instance');
529 END IF;
530 l_tbl_count := 0;
531 l_tbl_count := l_dest_instance_header_tbl.count;
532 IF (l_debug > 0) THEN
533 csi_t_gen_utility_pvt.add('Destination Records Found: '||l_tbl_count);
534 END IF;
535
536 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
537 IF NOT l_return_status = l_fnd_success then
538 IF (l_debug > 0) THEN
539 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
540 END IF;
541 l_msg_index := 1;
542 WHILE l_msg_count > 0 loop
543 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
544 l_msg_index := l_msg_index + 1;
545 l_msg_count := l_msg_count - 1;
546 END LOOP;
547 RAISE fnd_api.g_exc_error;
548 END IF;
549
550 IF l_dest_instance_header_tbl.count = 0 THEN -- Installed Base Destination Records are not found
551 IF (l_debug > 0) THEN
552 csi_t_gen_utility_pvt.add('No Destination Records were found so we will create a new destination Record using the source data');
553 END IF;
554
555 l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
556 l_new_dest_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
557 l_new_dest_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
558 l_new_dest_instance_rec.inv_master_organization_id := l_master_organization_id;
559 l_new_dest_instance_rec.mfg_serial_number_flag := 'N';
560 l_new_dest_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
561 l_new_dest_instance_rec.quantity := abs(l_mtl_item_tbl(j).transaction_quantity);
562 l_new_dest_instance_rec.active_start_date := l_sysdate;
563 l_new_dest_instance_rec.active_end_date := NULL;
564 l_new_dest_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
565 l_new_dest_instance_rec.instance_usage_code := l_in_service;
566 l_new_dest_instance_rec.inv_locator_id := NULL;
567 l_new_dest_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code(l_location_type);
568 --l_new_dest_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('hz_locations');
569 l_new_dest_instance_rec.location_id := l_mtl_item_tbl(j).ship_to_location_id;
570 l_new_dest_instance_rec.inv_organization_id := NULL;
571 l_new_dest_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
572 l_new_dest_instance_rec.pa_project_id := NULL;
573 l_new_dest_instance_rec.pa_project_task_id := NULL;
574 l_new_dest_instance_rec.customer_view_flag := 'N';
575 l_new_dest_instance_rec.merchant_view_flag := 'Y';
576 l_new_dest_instance_rec.operational_status_code := 'IN_SERVICE';
577 l_new_dest_instance_rec.object_version_number := l_object_version_number;
578
579 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
580 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
581 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
582 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
583 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
584 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
585
586 IF (l_debug > 0) THEN
587 csi_t_gen_utility_pvt.add('Before Create Item Instance - 45');
588 END IF;
589
590 csi_item_instance_pub.create_item_instance(l_api_version,
591 l_commit,
592 l_init_msg_list,
593 l_validation_level,
594 l_new_dest_instance_rec,
595 l_ext_attrib_values_tbl,
596 l_party_tbl,
597 l_account_tbl,
598 l_pricing_attrib_tbl,
599 l_org_assignments_tbl,
600 l_asset_assignment_tbl,
601 l_txn_rec,
602 l_return_status,
603 l_msg_count,
604 l_msg_data);
605
606 IF (l_debug > 0) THEN
607 csi_t_gen_utility_pvt.add('After Create Item Instance');
608 END IF;
609
610 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
611 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
612 IF (l_debug > 0) THEN
613 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
614 END IF;
615 l_msg_index := 1;
616 WHILE l_msg_count > 0 loop
617 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
618 l_msg_index := l_msg_index + 1;
619 l_msg_count := l_msg_count - 1;
620 END LOOP;
621 RAISE fnd_api.g_exc_error;
622 END IF;
623
624 ELSIF l_dest_instance_header_tbl.count = 1 THEN-- Installed Base Destination Records Found
625
626 IF (l_debug > 0) THEN
627 csi_t_gen_utility_pvt.add('You will update instance: '||l_dest_instance_header_tbl(i).instance_id);
628 END IF;
629
630 l_update_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
634 l_update_dest_instance_rec.object_version_number := l_dest_instance_header_tbl(i).object_version_number;
631 l_update_dest_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
632 l_update_dest_instance_rec.quantity := l_dest_instance_header_tbl(i).quantity + abs(l_mtl_item_tbl(j).primary_quantity);
633 l_update_dest_instance_rec.active_end_date := NULL;
635
636 l_party_tbl.delete;
637 l_account_tbl.delete;
638 l_pricing_attrib_tbl.delete;
639 l_org_assignments_tbl.delete;
640 l_asset_assignment_tbl.delete;
641
642 l_update_dest_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
643
644 IF (l_debug > 0) THEN
645 csi_t_gen_utility_pvt.add('Before Update Item Instance - Neg Qty');
646 END IF;
647
648 csi_item_instance_pub.update_item_instance(l_api_version,
649 l_commit,
650 l_init_msg_list,
651 l_validation_level,
652 l_update_dest_instance_rec,
653 l_ext_attrib_values_tbl,
654 l_party_tbl,
655 l_account_tbl,
656 l_pricing_attrib_tbl,
657 l_org_assignments_tbl,
658 l_asset_assignment_tbl,
659 l_txn_rec,
660 l_instance_id_lst,
661 l_return_status,
662 l_msg_count,
663 l_msg_data);
664
665 l_upd_error_instance_id := NULL;
666 l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
667
668 IF (l_debug > 0) THEN
669 csi_t_gen_utility_pvt.add('After Update Item Instance - Neg Qty');
670 csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
671 END IF;
672
673 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
674 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
675 IF (l_debug > 0) THEN
676 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
677 END IF;
678 l_msg_index := 1;
679 WHILE l_msg_count > 0 loop
680 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
681 l_msg_index := l_msg_index + 1;
682 l_msg_count := l_msg_count - 1;
683 END LOOP;
684 RAISE fnd_api.g_exc_error;
685 END IF;
686
687 ELSIF l_dest_instance_header_tbl.count > 1 THEN
688 -- Multiple Instances were found so throw error
689 IF (l_debug > 0) THEN
690 csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
691 END IF;
692 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
693 fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
694 fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
695 fnd_message.set_token('INV_ORG_ID',l_mtl_item_tbl(j).organization_id);
696 fnd_message.set_token('LOCATOR',l_mtl_item_tbl(j).locator_id);
697 l_error_message := fnd_message.get;
698 RAISE fnd_api.g_exc_error;
699 END IF; -- End of Destination Record If
700
701 ELSIF l_mtl_item_tbl(j).serial_number is NOT NULL THEN
702 IF l_src_instance_header_tbl.count = 1 THEN -- Serialized Item
703
704 IF (l_debug > 0) THEN
705 csi_t_gen_utility_pvt.add('You are updating a Serialized Item');
706 csi_t_gen_utility_pvt.add('Ship to Location ID Serialized: '||l_mtl_item_tbl(j).ship_to_location_id);
707 csi_t_gen_utility_pvt.add('The Transaction Status Code will be - Complete (C) or Incomplete (I): '||l_txn_rec.transaction_status_code);
708 END IF;
709
710 l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
711 l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
712 l_update_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
713 l_update_instance_rec.inv_subinventory_name := NULL;
714 -- Added for Bug 5975739
715 l_update_instance_rec.inv_master_organization_id := l_master_organization_id;
716 l_update_instance_rec.inv_organization_id := NULL;
717 l_update_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
718 l_update_instance_rec.inv_locator_id := NULL;
719 l_update_instance_rec.location_id := l_mtl_item_tbl(j).ship_to_location_id;
720 l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code(l_location_type);
724 l_update_instance_rec.instance_usage_code := l_in_service;
721 --l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('hz_locations');
722 l_update_instance_rec.pa_project_id := NULL;
723 l_update_instance_rec.pa_project_task_id := NULL;
725 l_update_instance_rec.operational_status_code := 'IN_SERVICE';
726 l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
727
728 l_party_tbl.delete;
729 l_account_tbl.delete;
730 l_pricing_attrib_tbl.delete;
731 l_org_assignments_tbl.delete;
732 l_asset_assignment_tbl.delete;
733
734 IF (l_debug > 0) THEN
735 csi_t_gen_utility_pvt.add('Before Update Item Instance');
736 END IF;
737
738 csi_item_instance_pub.update_item_instance(l_api_version,
739 l_commit,
740 l_init_msg_list,
741 l_validation_level,
742 l_update_instance_rec,
743 l_ext_attrib_values_tbl,
744 l_party_tbl,
745 l_account_tbl,
746 l_pricing_attrib_tbl,
747 l_org_assignments_tbl,
748 l_asset_assignment_tbl,
749 l_txn_rec,
750 l_instance_id_lst,
751 l_return_status,
752 l_msg_count,
753 l_msg_data);
754
755 l_upd_error_instance_id := NULL;
756 l_upd_error_instance_id := l_update_instance_rec.instance_id;
757
758 IF (l_debug > 0) THEN
759 csi_t_gen_utility_pvt.add('After Update Item Instance');
760 csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
761 END IF;
762
763 -- Check for any errors and add them to the message stack to pass out to be put into the
764 -- error log table.
765 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
766 IF (l_debug > 0) THEN
767 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
768 END IF;
769 l_msg_index := 1;
770 WHILE l_msg_count > 0 loop
771 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
772 l_msg_index := l_msg_index + 1;
773 l_msg_count := l_msg_count - 1;
774 END LOOP;
775 RAISE fnd_api.g_exc_error;
776 END IF;
777
778 ELSIF l_src_instance_header_tbl.count = 0 THEN -- Serialized Item
779 IF (l_debug > 0) THEN
780 csi_t_gen_utility_pvt.add('No Records were found in Install Base');
781 END IF;
782 fnd_message.set_name('CSI','CSI_IB_RECORD_NOTFOUND');
783 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
784 fnd_message.set_token('SUBINVENTORY',l_mtl_item_tbl(j).subinventory_code);
785 fnd_message.set_token('ORG_ID',l_mtl_item_tbl(j).organization_id);
786 l_error_message := fnd_message.get;
787 RAISE fnd_api.g_exc_error;
788
789 ELSIF l_src_instance_header_tbl.count > 1 THEN -- Serialized Item
790
791 -- Multiple Instances were found so throw error
792 IF (l_debug > 0) THEN
793 csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-56');
794 END IF;
795 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
796 fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
797 fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
798 fnd_message.set_token('INV_ORG_ID',l_mtl_item_tbl(j).organization_id);
799 fnd_message.set_token('LOCATOR',l_mtl_item_tbl(j).locator_id);
800 l_error_message := fnd_message.get;
801 RAISE fnd_api.g_exc_error;
802
803 END IF; -- End of Serial Number If
804 END IF; -- End of Serial Number count IF
805 END LOOP; -- End of For Loop
806
807 IF (l_debug > 0) THEN
808 csi_t_gen_utility_pvt.add('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
809 csi_t_gen_utility_pvt.add('*****End of csi_inv_hz_pkg.issue_to_hz_loc Transaction procedure*****');
810 END IF;
811
812 EXCEPTION
813 WHEN fnd_api.g_exc_error THEN
814 IF (l_debug > 0) THEN
815 csi_t_gen_utility_pvt.add('You have encountered a "fnd_api.g_exc_error" exception');
816 END IF;
817 x_return_status := l_fnd_error;
818
819 IF l_mtl_item_tbl.count > 0 THEN
820 x_trx_error_rec.serial_number := l_mtl_item_tbl(j).serial_number;
821 x_trx_error_rec.lot_number := l_mtl_item_tbl(j).lot_number;
822 x_trx_error_rec.instance_id := l_upd_error_instance_id;
823 x_trx_error_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
824 x_trx_error_rec.src_serial_num_ctrl_code := l_mtl_item_tbl(j).serial_number_control_code;
825 x_trx_error_rec.src_location_ctrl_code := l_mtl_item_tbl(j).location_control_code;
826 x_trx_error_rec.src_lot_ctrl_code := l_mtl_item_tbl(j).lot_control_code;
830 END IF;
827 x_trx_error_rec.src_rev_qty_ctrl_code := l_mtl_item_tbl(j).revision_qty_control_code;
828 x_trx_error_rec.comms_nl_trackable_flag := l_mtl_item_tbl(j).comms_nl_trackable_flag;
829 x_trx_error_rec.transaction_error_date := l_sysdate ;
831
832 x_trx_error_rec.error_text := l_error_message;
833 x_trx_error_rec.transaction_id := NULL;
834 x_trx_error_rec.source_type := 'CSIISUHZ';
835 x_trx_error_rec.source_id := p_transaction_id;
836 x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
837 x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('ISSUE_TO_HZ_LOC','INV');
838 x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
839 x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
840
841 WHEN others THEN
842 IF (l_debug > 0) THEN
843 csi_t_gen_utility_pvt.add('You have encountered a "others" exception');
844 END IF;
848 x_return_status := l_fnd_unexpected;
845 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
846 fnd_message.set_token('API_NAME',l_api_name);
847 fnd_message.set_token('SQL_ERROR',SQLERRM);
849
850 IF l_mtl_item_tbl.count > 0 THEN
851 x_trx_error_rec.serial_number := l_mtl_item_tbl(j).serial_number;
852 x_trx_error_rec.lot_number := l_mtl_item_tbl(j).lot_number;
853 x_trx_error_rec.instance_id := l_upd_error_instance_id;
854 x_trx_error_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
855 x_trx_error_rec.src_serial_num_ctrl_code := l_mtl_item_tbl(j).serial_number_control_code;
856 x_trx_error_rec.src_location_ctrl_code := l_mtl_item_tbl(j).location_control_code;
857 x_trx_error_rec.src_lot_ctrl_code := l_mtl_item_tbl(j).lot_control_code;
858 x_trx_error_rec.src_rev_qty_ctrl_code := l_mtl_item_tbl(j).revision_qty_control_code;
859 x_trx_error_rec.comms_nl_trackable_flag := l_mtl_item_tbl(j).comms_nl_trackable_flag;
860 x_trx_error_rec.transaction_error_date := l_sysdate ;
861 END IF;
862
863 x_trx_error_rec.error_text := fnd_message.get;
864 x_trx_error_rec.transaction_id := NULL;
865 x_trx_error_rec.source_type := 'CSIISUHZ';
866 x_trx_error_rec.source_id := p_transaction_id;
867 x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
868 x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('ISSUE_TO_HZ_LOC','INV');
869 x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
870 x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
871
872 END issue_to_hz_loc;
873
874 PROCEDURE misc_receipt_hz_loc(p_transaction_id IN NUMBER,
875 p_message_id IN NUMBER,
876 x_return_status OUT NOCOPY VARCHAR2,
877 x_trx_error_rec OUT NOCOPY CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC)
878 IS
879
880 l_mtl_item_tbl CSI_INV_TRXS_PKG.MTL_ITEM_TBL_TYPE;
881 l_api_name VARCHAR2(100) := 'CSI_INV_HZ_PKG.MISC_RECEIPT_HZ_LOC';
882 l_api_version NUMBER := 1.0;
883 l_commit VARCHAR2(1) := FND_API.G_FALSE;
884 l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
885 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
886 l_active_instance_only VARCHAR2(10) := FND_API.G_TRUE;
887 l_inactive_instance_only VARCHAR2(10) := FND_API.G_FALSE;
888 l_resolve_id_columns VARCHAR2(10) := FND_API.G_FALSE;
889 l_transaction_id NUMBER := NULL;
890 l_object_version_number NUMBER := 1;
891 l_sysdate DATE := SYSDATE;
892 l_master_organization_id NUMBER;
893 l_depreciable VARCHAR2(1);
894 l_instance_id_lst CSI_DATASTRUCTURES_PUB.ID_TBL;
895 l_txn_error_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC;
896 l_instance_query_rec CSI_DATASTRUCTURES_PUB.INSTANCE_QUERY_REC;
897 l_instance_dest_query_rec CSI_DATASTRUCTURES_PUB.INSTANCE_QUERY_REC;
898 l_update_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
899 l_upd_src_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
900 l_dest_instance_query_rec CSI_DATASTRUCTURES_PUB.INSTANCE_QUERY_REC;
901 l_new_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
902 l_new_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
903 l_update_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
904 l_txn_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_REC;
905 l_return_status VARCHAR2(1);
906 l_error_code VARCHAR2(50);
907 l_error_message VARCHAR2(4000);
908 l_party_query_rec CSI_DATASTRUCTURES_PUB.PARTY_QUERY_REC;
909 l_account_query_rec CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_QUERY_REC;
910 l_src_instance_header_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_HEADER_TBL;
911 l_dest_instance_header_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_HEADER_TBL;
912 l_ext_attrib_values_tbl CSI_DATASTRUCTURES_PUB.EXTEND_ATTRIB_VALUES_TBL;
913 l_party_tbl CSI_DATASTRUCTURES_PUB.PARTY_TBL;
914 l_account_tbl CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_TBL;
915 l_pricing_attrib_tbl CSI_DATASTRUCTURES_PUB.PRICING_ATTRIBS_TBL;
916 l_org_assignments_tbl CSI_DATASTRUCTURES_PUB.ORGANIZATION_UNITS_TBL;
917 l_asset_assignment_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_ASSET_TBL;
918 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
919 l_fnd_warning VARCHAR2(1) := 'W';
920 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
921 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
922 l_in_inventory VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_INVENTORY;
923 l_in_process VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_PROCESS;
924 l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
925 l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
926 l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
927 l_installed VARCHAR2(25) := CSI_INV_TRXS_PKG.G_INSTALLED;
928 l_in_wip VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_WIP;
929 l_hz_loc_code VARCHAR2(25) := 'HZ_LOCATIONS';
930 l_in_relationship VARCHAR2(25) := 'IN_RELATIONSHIP'; --Added for bug 12536407
931 l_transaction_error_id NUMBER;
932 l_quantity NUMBER;
933 l_mfg_serial_flag VARCHAR2(1);
934 l_ins_number VARCHAR2(100);
935 l_employee_id NUMBER;
936 l_ins_id NUMBER;
937 l_file VARCHAR2(500);
938 l_status VARCHAR2(1000);
939 l_msg_count NUMBER;
940 l_msg_data VARCHAR2(2000);
941 l_msg_index NUMBER;
942 j PLS_INTEGER;
943 i PLS_INTEGER := 1;
944 l_tbl_count NUMBER := 0;
945 l_redeploy_flag VARCHAR2(1);
946 l_upd_error_instance_id NUMBER := NULL;
947 l_hz_location NUMBER := NULL;
948 l_hr_location NUMBER := NULL;
949 l_location_type VARCHAR2(50);
950 l_oos_found VARCHAR2(1) := 'N';
951 l_ins_found VARCHAR2(1) := 'N';
952 l_bypass_qty VARCHAR2(1) := 'N';
953 l_curr_quantity NUMBER := NULL;
954 l_is_internal VARCHAR2(1) := 'N'; --Added for Bug 13460798
955
956 cursor c_id is
957 SELECT instance_status_id
958 FROM csi_instance_statuses
959 WHERE name = FND_PROFILE.VALUE('CSI_DEFAULT_INSTANCE_STATUS');
960
961 r_id c_id%rowtype;
962
963 cursor c_hz_loc (pc_location_id IN NUMBER) is
964 SELECT 1
965 FROM hz_locations
966 WHERE location_id = pc_location_id;
967
968 cursor c_hr_loc (pc_location_id IN NUMBER) is
969 SELECT 1
970 FROM hr_locations
971 WHERE location_id = pc_location_id;
972
973 BEGIN
974
975 x_return_status := l_fnd_success;
976
977 IF (l_debug > 0) THEN
978 csi_t_gen_utility_pvt.add('*****Start of csi_inv_hz_pkg.misc_receipt_hz_loc Transaction procedure*****');
979 csi_t_gen_utility_pvt.add('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
980 csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
981 csi_t_gen_utility_pvt.add('csiivtzb.pls 115.14');
982 END IF;
983
984 -- This procedure queries all of the Inventory Transaction Records
985 -- and returns them as a table.
986
987 csi_inv_trxs_pkg.get_transaction_recs(p_transaction_id,
988 l_mtl_item_tbl,
989 l_return_status,
990 l_error_message);
991
992 l_tbl_count := 0;
993 l_tbl_count := l_mtl_item_tbl.count;
994 IF (l_debug > 0) THEN
995 csi_t_gen_utility_pvt.add('Inventory Records Found: '||l_tbl_count);
996 END IF;
997
998 IF NOT l_return_status = l_fnd_success THEN
999 IF (l_debug > 0) THEN
1000 csi_t_gen_utility_pvt.add('You have encountered an error in CSI_INV_TRXS_PKG.get_transaction_recs, Transaction ID: '||p_transaction_id);
1001 END IF;
1002 RAISE fnd_api.g_exc_error;
1003 END IF;
1004
1005 -- Get the Master Organization ID
1006 csi_inv_trxs_pkg.get_master_organization(l_mtl_item_tbl(i).organization_id,
1007 l_master_organization_id,
1008 l_return_status,
1009 l_error_message);
1010
1011 IF NOT l_return_status = l_fnd_success THEN
1012 IF (l_debug > 0) THEN
1013 csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.get_master_organization, Organization ID: '||l_mtl_item_tbl(i).organization_id);
1014 END IF;
1015 RAISE fnd_api.g_exc_error;
1016 END IF;
1017
1018 -- Call get_fnd_employee_id and get the employee id
1019 l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
1020
1021 IF l_employee_id = -1 THEN
1022 IF (l_debug > 0) THEN
1023 csi_t_gen_utility_pvt.add('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
1024 END IF;
1025 END IF;
1026 IF (l_debug > 0) THEN
1027 csi_t_gen_utility_pvt.add('The Employee that is processing this Transaction is: '||l_employee_id);
1028 END IF;
1029
1030 -- See if this is a depreciable Item to set the status of the transaction record
1031 csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
1032 l_depreciable,
1033 l_mtl_item_tbl(i).organization_id); --Added for Bug 13988660
1034
1035 IF (l_debug > 0) THEN
1036 csi_t_gen_utility_pvt.add('Is this Item ID: '||l_mtl_item_tbl(i).inventory_item_id||', Depreciable :'||l_depreciable);
1037 END IF;
1038
1039 -- Set the quantity
1040 IF l_mtl_item_tbl(i).serial_number IS NULL THEN
1041 l_quantity := l_mtl_item_tbl(i).transaction_quantity;
1042 ELSE
1043 l_quantity := 1;
1044 END IF;
1045
1046 -- Initialize Transaction Record
1047 l_txn_rec := csi_inv_trxs_pkg.init_txn_rec;
1048
1049 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
1050
1051 -- Set Status based on redeployment
1052 --IF l_depreciable = 'N' THEN
1053 -- IF l_mtl_item_tbl(i).serial_number is NOT NULL THEN
1054 -- csi_inv_trxs_pkg.get_redeploy_flag(l_mtl_item_tbl(i).inventory_item_id,
1055 -- l_mtl_item_tbl(i).serial_number,
1056 -- p_transaction_id,
1057 -- l_sysdate,
1058 -- l_redeploy_flag,
1059 -- l_return_status,
1060 -- l_error_message);
1061 -- END IF;
1062 -- IF l_redeploy_flag = 'Y' THEN
1063 -- l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
1064 -- ELSE
1065 -- l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_complete;
1066 -- END IF;
1067 --ELSE
1068 -- l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
1069 --END IF;
1070
1071 --IF NOT l_return_status = l_fnd_success THEN
1072 -- IF (l_debug > 0) THEN
1073 -- csi_t_gen_utility_pvt.add('Redeploy Flag: '||l_redeploy_flag);
1077 --END IF;
1074 -- csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.get_redeploy_flag: '||l_error_message);
1075 -- END IF;
1076 -- RAISE fnd_api.g_exc_error;
1078
1079 IF (l_debug > 0) THEN
1080 csi_t_gen_utility_pvt.add('Redeploy Flag: '||l_redeploy_flag);
1081 csi_t_gen_utility_pvt.add('Trans Status Code: '||l_txn_rec.transaction_status_code);
1082 END IF;
1083
1084 -- Get Default Status ID
1085 OPEN c_id;
1086 FETCH c_id into r_id;
1087 CLOSE c_id;
1088
1089 -- Decide if Location ID is from HZ or HR (Before txn Creation)
1090
1091 open c_hz_loc (l_mtl_item_tbl(i).ship_to_location_id);
1092 fetch c_hz_loc into l_hz_location;
1093
1094 IF l_hz_location IS NOT NULL THEN
1095 l_location_type := 'HZ_LOCATIONS';
1096 close c_hz_loc;
1097 ELSE
1098 close c_hz_loc;
1099 open c_hr_loc (l_mtl_item_tbl(i).ship_to_location_id);
1100 fetch c_hr_loc into l_hr_location;
1101 IF l_hr_location IS NOT NULL THEN
1102 l_location_type := 'INTERNAL_SITE';
1103 close c_hr_loc;
1104 END IF;
1105 END IF;
1106
1107 IF (l_debug > 0) THEN
1108 csi_t_gen_utility_pvt.add('Location ID - Location Type; '||l_mtl_item_tbl(i).ship_to_location_id||'-'||l_location_type);
1109 END IF;
1110
1111 -- Create CSI Transaction to be used
1112 l_txn_rec.source_transaction_date := l_mtl_item_tbl(i).transaction_date;
1113 l_txn_rec.transaction_date := l_sysdate;
1114 l_txn_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('MISC_RECEIPT_HZ_LOC','INV');
1115 l_txn_rec.transaction_quantity := l_mtl_item_tbl(i).transaction_quantity;
1116 l_txn_rec.transaction_uom_code := l_mtl_item_tbl(i).transaction_uom;
1117 l_txn_rec.transacted_by := l_employee_id;
1118 l_txn_rec.transaction_action_code := NULL;
1119 l_txn_rec.message_id := p_message_id;
1120 l_txn_rec.inv_material_transaction_id := p_transaction_id;
1121 l_txn_rec.object_version_number := l_object_version_number;
1122 l_txn_rec.source_group_ref := l_location_type;
1123
1124 csi_inv_trxs_pkg.create_csi_txn(l_txn_rec,
1125 l_error_message,
1126 l_return_status);
1127
1128 IF (l_debug > 0) THEN
1129 csi_t_gen_utility_pvt.add('CSI Transaction Created: '||l_txn_rec.transaction_id);
1130 END IF;
1131
1132 IF NOT l_return_status = l_fnd_success THEN
1133 IF (l_debug > 0) THEN
1134 csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.create_csi_txn: '||p_transaction_id);
1135 END IF;
1136 RAISE fnd_api.g_exc_error;
1137 END IF;
1138
1139 -- Now loop through the PL/SQL Table.
1140 j := 1;
1141
1142 IF (l_debug > 0) THEN
1143 csi_t_gen_utility_pvt.add('Starting to loop through Material Transaction Records');
1144 END IF;
1145
1146 FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
1147
1148 IF (l_debug > 0) THEN
1149 csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
1150 csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
1151 csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
1152 csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
1153 END IF;
1154
1155 IF l_mtl_item_tbl(j).serial_number IS NOT NULL THEN -- Serialized
1156
1160 --l_instance_query_rec.location_type_code := l_hz_loc_code;
1157 l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
1158 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1159 --l_instance_query_rec.location_id := l_mtl_item_tbl(j).ship_to_location_id;
1161 --l_instance_query_rec.pa_project_id := NULL;
1162 --l_instance_query_rec.pa_project_task_id := NULL;
1163 l_instance_query_rec.serial_number := l_mtl_item_tbl(j).serial_number;
1164 --l_instance_query_rec.instance_usage_code := l_out_of_service;
1165
1166 IF (l_debug > 0) THEN
1167 csi_t_gen_utility_pvt.add('Before Get Item Instance');
1168 csi_t_gen_utility_pvt.add('Ship to Location ID: '||l_mtl_item_tbl(j).ship_to_location_id);
1169 END IF;
1170
1171 csi_item_instance_pub.get_item_instances(l_api_version,
1172 l_commit,
1173 l_init_msg_list,
1174 l_validation_level,
1175 l_instance_query_rec,
1176 l_party_query_rec,
1177 l_account_query_rec,
1178 l_transaction_id,
1179 l_resolve_id_columns,
1180 l_active_instance_only,
1181 l_src_instance_header_tbl,
1182 l_return_status,
1183 l_msg_count,
1184 l_msg_data);
1185
1186 IF (l_debug > 0) THEN
1187 csi_t_gen_utility_pvt.add('After Get Item Instance');
1188 END IF;
1189 l_tbl_count := 0;
1190 l_tbl_count := l_src_instance_header_tbl.count;
1191 IF (l_debug > 0) THEN
1192 csi_t_gen_utility_pvt.add('Source Records Found: '||l_tbl_count);
1193 END IF;
1194
1195 -- Check for any errors and add them to the message stack to pass out to be put into the
1196 -- error log table.
1197 IF NOT l_return_status = l_fnd_success then
1198 IF (l_debug > 0) THEN
1199 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
1200 END IF;
1201 l_msg_index := 1;
1202 WHILE l_msg_count > 0 loop
1203 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1204 l_msg_index := l_msg_index + 1;
1205 l_msg_count := l_msg_count - 1;
1206 END LOOP;
1207 RAISE fnd_api.g_exc_error;
1208 END IF;
1209
1210 IF l_src_instance_header_tbl.count > 0 THEN -- Added if condition for bug 13646798
1211 --Added code for bug 12536407
1212 IF l_src_instance_header_tbl(i).instance_usage_code = l_in_relationship THEN
1213 IF (l_debug > 0) THEN
1214 csi_t_gen_utility_pvt.add('Check and Break Relationship for Instance :'||l_src_instance_header_tbl(i).instance_id);
1215 END IF;
1216 csi_process_txn_pvt.check_and_break_relation(l_src_instance_header_tbl(i).instance_id,
1217 l_txn_rec,
1218 l_return_status);
1219
1220 IF NOT l_return_status = l_fnd_success then
1221 IF (l_debug > 0) THEN
1222 csi_t_gen_utility_pvt.add('You encountered an error in the se_inv_trxs_pkg.check_and_break_relation');
1223 END IF;
1224 l_error_message := csi_t_gen_utility_pvt.dump_error_stack;
1225 RAISE fnd_api.g_exc_error;
1226 END IF;
1227 IF (l_debug > 0) THEN
1228 csi_t_gen_utility_pvt.add('Object Version originally from instance: '||l_src_instance_header_tbl(i).object_version_number);
1229 END IF;
1230
1231 SELECT object_version_number
1232 into l_src_instance_header_tbl(i).object_version_number
1233 FROM csi_item_instances
1234 WHERE instance_id = l_src_instance_header_tbl(i).instance_id;
1235
1236 IF (l_debug > 0) THEN
1237 csi_t_gen_utility_pvt.add('Current Object Version after check and break :'||l_src_instance_header_tbl(i).object_version_number);
1238 END IF;
1239 END IF;
1243
1240 --End of code for bug 12536407
1241 END IF; -- Added if condition for bug 13646798
1242
1244 IF l_src_instance_header_tbl.count = 1 THEN -- Records found so update either Serialized or Non Serialized
1245 --Added for Bug 13460798
1246 l_is_internal := 'N';
1247 BEGIN
1248 SELECT 'Y' INTO l_is_internal FROM csi_item_instances cii, csi_install_parameters ci
1249 WHERE cii.OWNER_PARTY_ID = ci.INTERNAL_PARTY_ID
1250 AND cii.instance_id = l_src_instance_header_tbl(i).instance_id;
1251 EXCEPTION
1252 WHEN OTHERS THEN
1253 l_is_internal := 'N';
1254 END;
1255
1256 IF l_src_instance_header_tbl(i).instance_usage_code in (l_out_of_service,
1257 l_in_inventory,
1258 l_in_wip,
1259 l_installed,
1260 l_in_service,
1261 l_in_relationship, -- Added for bug 13771490 (removed in bug 13460798)
1262 l_in_process)
1263 OR (l_src_instance_header_tbl(i).instance_usage_code = 'OUT_OF_ENTERPRISE' AND l_is_internal = 'Y') THEN --Added for Bug 13460798
1264
1265
1266 IF (l_debug > 0) THEN
1267 csi_t_gen_utility_pvt.add('Source records found so decide which one to update using get_destination_instance procedure ');
1268 END IF;
1269
1270 l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1271 l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
1272 l_update_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
1273 -- Added For Bug 5975739
1274 l_update_instance_rec.inv_master_organization_id := l_master_organization_id;
1275 l_update_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1276 l_update_instance_rec.quantity := 1;
1277 l_update_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
1278 l_update_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
1279 l_update_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
1280 l_update_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1281 l_update_instance_rec.pa_project_id := NULL;
1282 l_update_instance_rec.pa_project_task_id := NULL;
1283 l_update_instance_rec.install_location_type_code := NULL;
1284 l_update_instance_rec.install_location_id := NULL;
1285 l_update_instance_rec.instance_usage_code := l_in_inventory;
1286 l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
1287 l_update_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
1288 l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
1289
1290 l_party_tbl.delete;
1291 l_account_tbl.delete;
1292 l_pricing_attrib_tbl.delete;
1293 l_org_assignments_tbl.delete;
1294 l_asset_assignment_tbl.delete;
1295
1296 IF (l_debug > 0) THEN
1297 csi_t_gen_utility_pvt.add('Before Update Item Instance');
1298 END IF;
1299
1300 csi_item_instance_pub.update_item_instance(l_api_version,
1301 l_commit,
1302 l_init_msg_list,
1303 l_validation_level,
1307 l_account_tbl,
1304 l_update_instance_rec,
1305 l_ext_attrib_values_tbl,
1306 l_party_tbl,
1308 l_pricing_attrib_tbl,
1309 l_org_assignments_tbl,
1310 l_asset_assignment_tbl,
1311 l_txn_rec,
1312 l_instance_id_lst,
1313 l_return_status,
1314 l_msg_count,
1315 l_msg_data);
1316
1317 l_upd_error_instance_id := NULL;
1318 l_upd_error_instance_id := l_update_instance_rec.instance_id;
1319
1320 IF (l_debug > 0) THEN
1321 csi_t_gen_utility_pvt.add('After Update Item Instance');
1322 csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1323 END IF;
1324
1325
1326 -- Check for any errors and add them to the message stack to pass out to be put into the
1327 -- error log table.
1328 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1329 IF (l_debug > 0) THEN
1330 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
1331 END IF;
1332 l_msg_index := 1;
1333 WHILE l_msg_count > 0 loop
1334 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1335 l_msg_index := l_msg_index + 1;
1336 l_msg_count := l_msg_count - 1;
1337 END LOOP;
1338 RAISE fnd_api.g_exc_error;
1339 END IF;
1340 ELSE
1341 l_status := 'In Inventory, Out of Service, Installed, In Service or In Process';
1342 IF (l_debug > 0) THEN
1343 csi_t_gen_utility_pvt.add('Serialized Item with Status other then Out Of Service, In Inventory, Installed, or In Process already exists in Install Base');
1344 csi_t_gen_utility_pvt.add('Instance Usage Code is: '||l_src_instance_header_tbl(i).instance_usage_code);
1345 END IF;
1346 fnd_message.set_name('CSI','CSI_SERIALIZED_ITEM_EXISTS');
1347 fnd_message.set_token('STATUS',l_status);
1348 l_error_message := fnd_message.get;
1349 l_return_status := l_fnd_error;
1350 RAISE fnd_api.g_exc_error;
1351 END IF;
1352
1353 ELSIF l_src_instance_header_tbl.count = 0 THEN -- No IB Records found
1354 csi_t_gen_utility_pvt.add('No Serialized Instances are found so we need to create one that we would have received from the HZ/HR Location');
1355
1356 l_new_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
1357 l_new_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1358 l_new_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1359 l_new_instance_rec.inv_master_organization_id := l_master_organization_id;
1360 l_new_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
1361 l_new_instance_rec.serial_number := l_mtl_item_tbl(j).serial_number;
1362 l_new_instance_rec.mfg_serial_number_flag := 'Y';
1363 l_new_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1364 l_new_instance_rec.quantity := 1;
1365 l_new_instance_rec.active_start_date := l_sysdate;
1366 l_new_instance_rec.active_end_date := NULL;
1367 l_new_instance_rec.unit_of_measure := l_mtl_item_tbl(j).primary_uom_code;
1368 l_new_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
1369 l_new_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
1370 l_new_instance_rec.instance_usage_code := l_in_inventory;
1371 l_new_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
1372 l_new_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
1373 l_new_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
1374 l_new_instance_rec.customer_view_flag := 'N';
1375 l_new_instance_rec.merchant_view_flag := 'Y';
1376 l_new_instance_rec.object_version_number := l_object_version_number;
1377 l_new_instance_rec.operational_status_code := 'NOT_USED';
1378 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
1379 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
1380 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
1381 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
1382 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
1383 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
1384
1385 l_new_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
1386
1387 csi_t_gen_utility_pvt.add('Instance_status_id Value: '||nvl(l_new_instance_rec.instance_status_id,-1));
1388 csi_t_gen_utility_pvt.add('You will now Create a new Item Instance Record');
1392 l_commit,
1389 csi_t_gen_utility_pvt.add('Serial Number: '||l_new_instance_rec.serial_number);
1390
1391 csi_item_instance_pub.create_item_instance(l_api_version,
1393 l_init_msg_list,
1394 l_validation_level,
1395 l_new_instance_rec,
1396 l_ext_attrib_values_tbl,
1397 l_party_tbl,
1398 l_account_tbl,
1399 l_pricing_attrib_tbl,
1400 l_org_assignments_tbl,
1401 l_asset_assignment_tbl,
1402 l_txn_rec,
1403 l_return_status,
1404 l_msg_count,
1405 l_msg_data);
1406
1407 -- Check for any errors and add them to the message stack to pass out to be put into the
1408 -- error log table.
1409 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1410 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
1411 l_msg_index := 1;
1412 WHILE l_msg_count > 0 loop
1413 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1414 l_msg_index := l_msg_index + 1;
1415 l_msg_count := l_msg_count - 1;
1416 END LOOP;
1417 RAISE fnd_api.g_exc_error;
1418 END IF;
1419
1420 csi_t_gen_utility_pvt.add('Item Instance Created: '||l_new_instance_rec.instance_id);
1421
1422 ELSIF l_src_instance_header_tbl.count > 1 THEN -- Multiple Instances Found
1423 -- Multiple Instances were found so throw error
1424 IF (l_debug > 0) THEN
1425 csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
1426 END IF;
1427 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
1428 fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
1429 fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
1430 fnd_message.set_token('INV_ORG_ID',l_mtl_item_tbl(j).organization_id);
1431 fnd_message.set_token('LOCATOR',l_mtl_item_tbl(j).locator_id);
1432 l_error_message := fnd_message.get;
1433 RAISE fnd_api.g_exc_error;
1434 END IF; -- Serialized Source IF
1435
1436 ELSIF l_mtl_item_tbl(j).serial_number IS NULL THEN -- Non Serialized
1437
1438 l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
1439 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1440 l_instance_query_rec.pa_project_id := NULL;
1441 l_instance_query_rec.pa_project_task_id := NULL;
1442 l_instance_query_rec.location_id := l_mtl_item_tbl(j).ship_to_location_id;
1443 --l_instance_query_rec.location_type_code := l_hz_loc_code;
1444 l_instance_query_rec.location_type_code := l_location_type;
1445 l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1446 l_instance_query_rec.serial_number := NULL;
1447 l_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1448 --l_instance_query_rec.instance_usage_code := l_out_of_service;
1449
1450 IF (l_debug > 0) THEN
1451 csi_t_gen_utility_pvt.add('Before Get Item Instance');
1452 csi_t_gen_utility_pvt.add('Ship to Location ID Non Ser: '||l_mtl_item_tbl(j).ship_to_location_id);
1453 csi_t_gen_utility_pvt.add('Location Type Code: '||l_location_type);
1454 END IF;
1455
1456 csi_item_instance_pub.get_item_instances(l_api_version,
1457 l_commit,
1458 l_init_msg_list,
1459 l_validation_level,
1460 l_instance_query_rec,
1461 l_party_query_rec,
1462 l_account_query_rec,
1463 l_transaction_id,
1464 l_resolve_id_columns,
1465 l_active_instance_only,
1466 l_src_instance_header_tbl,
1467 l_return_status,
1468 l_msg_count,
1469 l_msg_data);
1470
1471 IF (l_debug > 0) THEN
1472 csi_t_gen_utility_pvt.add('After Get Item Instance');
1473 END IF;
1474 l_tbl_count := 0;
1475 l_tbl_count := l_src_instance_header_tbl.count;
1476 IF (l_debug > 0) THEN
1477 csi_t_gen_utility_pvt.add('Source Records Found: '||l_tbl_count);
1478 END IF;
1479
1480 -- Check for any errors and add them to the message stack to pass out to be put into the
1481 -- error log table.
1482 IF NOT l_return_status = l_fnd_success then
1483 IF (l_debug > 0) THEN
1484 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
1485 END IF;
1486 l_msg_index := 1;
1490 l_msg_count := l_msg_count - 1;
1487 WHILE l_msg_count > 0 loop
1488 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1489 l_msg_index := l_msg_index + 1;
1491 END LOOP;
1492 RAISE fnd_api.g_exc_error;
1493 END IF;
1494
1495 l_bypass_qty := 'N';
1496
1497 IF l_src_instance_header_tbl.count > 0 THEN -- Records found so update either Serialized or Non Serialized
1498
1499 csi_t_gen_utility_pvt.add('Since this is Non Serial decide what instances to decrement');
1500 l_curr_quantity := abs(l_mtl_item_tbl(j).primary_quantity);
1501
1502 FOR oos IN l_src_instance_header_tbl.first .. l_src_instance_header_tbl.last LOOP
1503
1504 IF l_src_instance_header_tbl(oos).instance_usage_code = 'OUT_OF_SERVICE' THEN
1505
1506 csi_t_gen_utility_pvt.add('Out of Service Instance found: '||l_src_instance_header_tbl(oos).instance_id);
1507
1508 l_oos_found := 'Y';
1509 csi_t_gen_utility_pvt.add('l_curr_quantity: '||l_curr_quantity);
1510 -- Bug 10650152
1511 IF l_curr_quantity <= 0 THEN
1512 EXIT;
1513 END IF;
1514
1515
1516 IF l_src_instance_header_tbl(oos).quantity >= abs(l_mtl_item_tbl(j).primary_quantity) AND l_curr_quantity > 0 THEN
1517 csi_t_gen_utility_pvt.add('Instance found has more available quantity (or Equal) then the current quantity so subtract the entire primary quantity');
1518 l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1519 l_upd_src_dest_instance_rec.instance_id := l_src_instance_header_tbl(oos).instance_id;
1520 l_upd_src_dest_instance_rec.quantity := l_src_instance_header_tbl(oos).quantity - abs(l_mtl_item_tbl(j).primary_quantity);
1521 l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(oos).object_version_number;
1522 l_bypass_qty := 'Y';
1523 l_curr_quantity := l_curr_quantity - l_src_instance_header_tbl(oos).quantity;
1524 -- Bug 10650152
1525 ELSIF l_src_instance_header_tbl(oos).quantity < abs(l_mtl_item_tbl(j).primary_quantity) AND l_curr_quantity > 0 THEN
1526 csi_t_gen_utility_pvt.add('Instance found has less available quantity then the current quantity so set the quantity to 0');
1527 l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1528 l_upd_src_dest_instance_rec.instance_id := l_src_instance_header_tbl(oos).instance_id;
1529 l_upd_src_dest_instance_rec.quantity := 0;
1530 l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(oos).object_version_number;
1531
1532 l_curr_quantity := abs(l_mtl_item_tbl(j).primary_quantity) - l_src_instance_header_tbl(oos).quantity;
1533
1534 END IF;
1535
1536
1537 csi_t_gen_utility_pvt.add('Current Quantity is: '||l_curr_quantity);
1538
1539 l_party_tbl.delete;
1540 l_account_tbl.delete;
1541 l_pricing_attrib_tbl.delete;
1542 l_org_assignments_tbl.delete;
1543 l_asset_assignment_tbl.delete;
1544
1545 IF (l_debug > 0) THEN
1546 csi_t_gen_utility_pvt.add('Before Update Item Instance: '||l_src_instance_header_tbl(oos).instance_id);
1547 END IF;
1548
1549 csi_item_instance_pub.update_item_instance(l_api_version,
1550 l_commit,
1551 l_init_msg_list,
1552 l_validation_level,
1553 l_upd_src_dest_instance_rec,
1554 l_ext_attrib_values_tbl,
1555 l_party_tbl,
1556 l_account_tbl,
1557 l_pricing_attrib_tbl,
1558 l_org_assignments_tbl,
1559 l_asset_assignment_tbl,
1560 l_txn_rec,
1561 l_instance_id_lst,
1562 l_return_status,
1563 l_msg_count,
1564 l_msg_data);
1565
1566 l_upd_error_instance_id := NULL;
1567 l_upd_error_instance_id := l_upd_src_dest_instance_rec.instance_id;
1568
1569 IF (l_debug > 0) THEN
1570 csi_t_gen_utility_pvt.add('After Update Item Instance');
1571 csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1572 END IF;
1573
1574 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1575 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1576 IF (l_debug > 0) THEN
1577 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
1578 END IF;
1579 l_msg_index := 1;
1580 WHILE l_msg_count > 0 loop
1581 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1582 l_msg_index := l_msg_index + 1;
1583 l_msg_count := l_msg_count - 1;
1584 END LOOP;
1585 RAISE fnd_api.g_exc_error;
1586 END IF;
1587
1588 END IF;
1589 END LOOP; -- Out of Service
1590
1591 -- Installed Instance
1592
1596
1593 IF l_bypass_qty = 'N' THEN
1594
1595 l_curr_quantity := abs(l_mtl_item_tbl(j).primary_quantity);
1597 FOR ins IN l_src_instance_header_tbl.first .. l_src_instance_header_tbl.last LOOP
1598
1599 IF l_src_instance_header_tbl(ins).instance_usage_code = 'INSTALLED' THEN
1600
1601 csi_t_gen_utility_pvt.add('Installed Instance found: '||l_src_instance_header_tbl(ins).instance_id);
1602
1603 l_ins_found := 'Y';
1604
1605 IF l_oos_found = 'N' THEN
1606 csi_t_gen_utility_pvt.add('Out of Service Instance not found so setting current qty to be the primary quantity');
1607 END IF;
1608
1609 csi_t_gen_utility_pvt.add('l_curr_quantity: '||l_curr_quantity);
1610 -- Bug 10650152
1611 IF l_curr_quantity <= 0 THEN
1612 EXIT;
1613 END IF;
1614 IF l_src_instance_header_tbl(ins).quantity >= l_curr_quantity AND l_curr_quantity > 0 THEN
1615 csi_t_gen_utility_pvt.add('Instance found has more available quantity (or Equal) then the current quantity so subtract the entire primary quantity');
1616 l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1617 l_upd_src_dest_instance_rec.instance_id := l_src_instance_header_tbl(ins).instance_id;
1618 l_upd_src_dest_instance_rec.quantity := l_src_instance_header_tbl(ins).quantity - l_curr_quantity;
1619 l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(ins).object_version_number;
1620 l_bypass_qty := 'Y';
1621 l_curr_quantity := l_curr_quantity - l_src_instance_header_tbl(ins).quantity;
1622
1623 -- Bug 10650152
1624 ELSIF l_src_instance_header_tbl(ins).quantity < l_curr_quantity AND l_curr_quantity > 0 THEN
1625 csi_t_gen_utility_pvt.add('Instance found has less available quantity then the current quantity so set the quantity to 0');
1626 l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1627 l_upd_src_dest_instance_rec.instance_id := l_src_instance_header_tbl(ins).instance_id;
1628 l_upd_src_dest_instance_rec.quantity := 0;
1629 l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(ins).object_version_number;
1630
1631 l_curr_quantity := l_curr_quantity - l_src_instance_header_tbl(ins).quantity;
1632
1633 END IF;
1634
1635 csi_t_gen_utility_pvt.add('Current Quantity is: '||l_curr_quantity);
1636
1637 l_party_tbl.delete;
1638 l_account_tbl.delete;
1639 l_pricing_attrib_tbl.delete;
1640 l_org_assignments_tbl.delete;
1641 l_asset_assignment_tbl.delete;
1642
1643 IF (l_debug > 0) THEN
1644 csi_t_gen_utility_pvt.add('Before Update Item Instance: '||l_src_instance_header_tbl(ins).instance_id);
1645 END IF;
1646
1647 csi_item_instance_pub.update_item_instance(l_api_version,
1648 l_commit,
1649 l_init_msg_list,
1650 l_validation_level,
1651 l_upd_src_dest_instance_rec,
1652 l_ext_attrib_values_tbl,
1653 l_party_tbl,
1654 l_account_tbl,
1655 l_pricing_attrib_tbl,
1656 l_org_assignments_tbl,
1657 l_asset_assignment_tbl,
1658 l_txn_rec,
1659 l_instance_id_lst,
1660 l_return_status,
1661 l_msg_count,
1662 l_msg_data);
1663
1664 l_upd_error_instance_id := NULL;
1665 l_upd_error_instance_id := l_upd_src_dest_instance_rec.instance_id;
1666
1667 IF (l_debug > 0) THEN
1668 csi_t_gen_utility_pvt.add('After Update Item Instance');
1669 csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1670 END IF;
1671
1672 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1673 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1674 IF (l_debug > 0) THEN
1675 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
1676 END IF;
1677 l_msg_index := 1;
1678 WHILE l_msg_count > 0 loop
1679 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1680 l_msg_index := l_msg_index + 1;
1681 l_msg_count := l_msg_count - 1;
1682 END LOOP;
1683 RAISE fnd_api.g_exc_error;
1684 END IF;
1685
1686 END IF;
1687 END LOOP; -- Installed
1688 END IF;
1689
1690
1691 -- In Serivce Instance
1692
1693 IF l_bypass_qty = 'N' THEN
1694
1695 l_curr_quantity := abs(l_mtl_item_tbl(j).primary_quantity);
1696
1697 FOR inser IN l_src_instance_header_tbl.first .. l_src_instance_header_tbl.last LOOP
1698
1699 IF l_src_instance_header_tbl(inser).instance_usage_code = 'IN_SERVICE' THEN
1700
1704 csi_t_gen_utility_pvt.add('Out of Service or Installed Instance not found so setting current qty to be the primary quantity');
1701 csi_t_gen_utility_pvt.add('In Service Instance found: '|| l_src_instance_header_tbl(inser).instance_id);
1702
1703 IF l_oos_found = 'N' AND l_ins_found = 'N' THEN
1705 END IF;
1706 csi_t_gen_utility_pvt.add('l_curr_quantity: '||l_curr_quantity);
1707 -- Bug 10650152
1708 IF l_curr_quantity <= 0 THEN
1709 EXIT;
1710 END IF;
1711 IF l_src_instance_header_tbl(inser).quantity >= l_curr_quantity AND l_curr_quantity > 0 THEN
1712 csi_t_gen_utility_pvt.add('Instance found has more available quantity (or Equal) then the current quantity so subtract the entire primary quantity');
1713 l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1714 l_upd_src_dest_instance_rec.instance_id := l_src_instance_header_tbl(inser).instance_id;
1715 l_upd_src_dest_instance_rec.quantity := l_src_instance_header_tbl(inser).quantity - l_curr_quantity;
1716 l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(inser).object_version_number;
1717
1718 l_bypass_qty := 'Y';
1719 l_curr_quantity := l_curr_quantity - l_src_instance_header_tbl(inser).quantity;
1720 -- Bug 10650152
1721 ELSIF l_src_instance_header_tbl(inser).quantity < l_curr_quantity AND l_curr_quantity > 0 THEN
1722 csi_t_gen_utility_pvt.add('Instance found has less available quantity then the current quantity so set the quantity to 0');
1723 l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1724 l_upd_src_dest_instance_rec.instance_id := l_src_instance_header_tbl(inser).instance_id;
1725 l_upd_src_dest_instance_rec.quantity := 0;
1726 l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(inser).object_version_number;
1727
1728 l_curr_quantity := l_curr_quantity - l_src_instance_header_tbl(inser).quantity;
1729
1730 END IF;
1731
1732 csi_t_gen_utility_pvt.add('Current Quantity is: '||l_curr_quantity);
1733
1734 l_party_tbl.delete;
1735 l_account_tbl.delete;
1736 l_pricing_attrib_tbl.delete;
1737 l_org_assignments_tbl.delete;
1738 l_asset_assignment_tbl.delete;
1739
1740 IF (l_debug > 0) THEN
1741 csi_t_gen_utility_pvt.add('Before Update Item Instance: '||l_src_instance_header_tbl(inser).instance_id);
1742 END IF;
1743
1744 csi_item_instance_pub.update_item_instance(l_api_version,
1745 l_commit,
1746 l_init_msg_list,
1747 l_validation_level,
1748 l_upd_src_dest_instance_rec,
1749 l_ext_attrib_values_tbl,
1750 l_party_tbl,
1751 l_account_tbl,
1752 l_pricing_attrib_tbl,
1753 l_org_assignments_tbl,
1754 l_asset_assignment_tbl,
1755 l_txn_rec,
1756 l_instance_id_lst,
1757 l_return_status,
1758 l_msg_count,
1759 l_msg_data);
1760
1761 l_upd_error_instance_id := NULL;
1762 l_upd_error_instance_id := l_upd_src_dest_instance_rec.instance_id;
1763
1764 IF (l_debug > 0) THEN
1765 csi_t_gen_utility_pvt.add('After Update Item Instance');
1766 csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1767 END IF;
1768
1769 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1770 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1771 IF (l_debug > 0) THEN
1772 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
1773 END IF;
1774 l_msg_index := 1;
1775 WHILE l_msg_count > 0 loop
1776 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1777 l_msg_index := l_msg_index + 1;
1778 l_msg_count := l_msg_count - 1;
1779 END LOOP;
1780 RAISE fnd_api.g_exc_error;
1781 END IF;
1782
1783 END IF;
1784 END LOOP; -- In Service
1785 END IF;
1786
1787 -- Now query and get the destination record.
1788 l_instance_dest_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
1789 l_instance_dest_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1790 l_instance_dest_query_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
1791 l_instance_dest_query_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
1792 l_instance_dest_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1793 l_instance_dest_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1794 l_instance_dest_query_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
1795 l_instance_dest_query_rec.serial_number := NULL;
1799 csi_t_gen_utility_pvt.add('Before Get Item Instance');
1796 l_instance_dest_query_rec.instance_usage_code := l_in_inventory;
1797
1798 IF (l_debug > 0) THEN
1800 END IF;
1801
1802 csi_item_instance_pub.get_item_instances(l_api_version,
1803 l_commit,
1804 l_init_msg_list,
1805 l_validation_level,
1806 l_instance_dest_query_rec,
1807 l_party_query_rec,
1808 l_account_query_rec,
1809 l_transaction_id,
1810 l_resolve_id_columns,
1811 l_inactive_instance_only,
1812 l_dest_instance_header_tbl,
1813 l_return_status,
1814 l_msg_count,
1818 csi_t_gen_utility_pvt.add('After Get Item Instance');
1815 l_msg_data);
1816
1817 IF (l_debug > 0) THEN
1819 END IF;
1820 l_tbl_count := 0;
1821 l_tbl_count := l_dest_instance_header_tbl.count;
1822 IF (l_debug > 0) THEN
1823 csi_t_gen_utility_pvt.add('Destination Records Found: '||l_tbl_count);
1824 END IF;
1825
1826 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1827 IF NOT l_return_status = l_fnd_success then
1828 IF (l_debug > 0) THEN
1829 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
1830 END IF;
1831 l_msg_index := 1;
1832 WHILE l_msg_count > 0 loop
1833 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1834 l_msg_index := l_msg_index + 1;
1835 l_msg_count := l_msg_count - 1;
1836 END LOOP;
1837 RAISE fnd_api.g_exc_error;
1838 END IF;
1839
1840 IF l_dest_instance_header_tbl.count = 0 THEN
1841
1842 l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
1843 l_new_dest_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1844 l_new_dest_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1845 l_new_dest_instance_rec.inv_master_organization_id := l_master_organization_id;
1846 l_new_dest_instance_rec.mfg_serial_number_flag := 'N';
1847 l_new_dest_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1848 l_new_dest_instance_rec.quantity := abs(l_mtl_item_tbl(j).transaction_quantity);
1849 l_new_dest_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
1850 l_new_dest_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
1854 l_new_dest_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
1851 l_new_dest_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
1852 l_new_dest_instance_rec.instance_usage_code := l_in_inventory;
1853 l_new_dest_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
1855 l_new_dest_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
1856 l_new_dest_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
1857 l_new_dest_instance_rec.customer_view_flag := 'N';
1858 l_new_dest_instance_rec.merchant_view_flag := 'Y';
1859 l_new_dest_instance_rec.active_start_date := l_sysdate;
1860 l_new_dest_instance_rec.active_end_date := NULL;
1861 l_new_dest_instance_rec.operational_status_code := 'NOT_USED';
1862 l_new_dest_instance_rec.object_version_number := l_object_version_number;
1863
1864 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
1865 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
1866 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
1867 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
1868 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
1869 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
1870
1871 l_new_dest_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
1872
1873 IF (l_debug > 0) THEN
1874 csi_t_gen_utility_pvt.add('Before Create Item Instance');
1875 END IF;
1876
1877 csi_item_instance_pub.create_item_instance(l_api_version,
1878 l_commit,
1879 l_init_msg_list,
1880 l_validation_level,
1881 l_new_dest_instance_rec,
1882 l_ext_attrib_values_tbl,
1883 l_party_tbl,
1884 l_account_tbl,
1885 l_pricing_attrib_tbl,
1886 l_org_assignments_tbl,
1887 l_asset_assignment_tbl,
1888 l_txn_rec,
1889 l_return_status,
1890 l_msg_count,
1891 l_msg_data);
1892
1893 IF (l_debug > 0) THEN
1894 csi_t_gen_utility_pvt.add('After Update Item Instance');
1895 END IF;
1896
1897 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1898 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1899 IF (l_debug > 0) THEN
1900 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
1901 END IF;
1902 l_msg_index := 1;
1903 WHILE l_msg_count > 0 loop
1904 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1905 l_msg_index := l_msg_index + 1;
1906 l_msg_count := l_msg_count - 1;
1907 END LOOP;
1908 RAISE fnd_api.g_exc_error;
1909 END IF;
1910
1911 ELSIF l_dest_instance_header_tbl.count = 1 THEN
1912
1913 l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1914 l_update_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
1915 l_update_instance_rec.quantity := l_dest_instance_header_tbl(i).quantity + abs(l_mtl_item_tbl(j).primary_quantity);
1916 l_update_instance_rec.active_end_date := NULL;
1917 l_update_instance_rec.object_version_number := l_dest_instance_header_tbl(i).object_version_number;
1918
1919 l_party_tbl.delete;
1920 l_account_tbl.delete;
1921 l_pricing_attrib_tbl.delete;
1922 l_org_assignments_tbl.delete;
1923 l_asset_assignment_tbl.delete;
1924
1925 l_update_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
1926
1927 IF (l_debug > 0) THEN
1928 csi_t_gen_utility_pvt.add('Before Update Item Instance');
1929 END IF;
1930
1931 csi_item_instance_pub.update_item_instance(l_api_version,
1932 l_commit,
1933 l_init_msg_list,
1934 l_validation_level,
1935 l_update_instance_rec,
1936 l_ext_attrib_values_tbl,
1937 l_party_tbl,
1938 l_account_tbl,
1942 l_txn_rec,
1939 l_pricing_attrib_tbl,
1940 l_org_assignments_tbl,
1941 l_asset_assignment_tbl,
1943 l_instance_id_lst,
1944 l_return_status,
1945 l_msg_count,
1946 l_msg_data);
1947
1948 l_upd_error_instance_id := NULL;
1949 l_upd_error_instance_id := l_update_instance_rec.instance_id;
1950
1951 IF (l_debug > 0) THEN
1952 csi_t_gen_utility_pvt.add('After Update Item Instance');
1953 csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1954 END IF;
1955
1956 -- Check for any errors and add them to the message stack to pass out to be put into the
1957 -- error log table.
1958 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1959 IF (l_debug > 0) THEN
1960 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
1961 END IF;
1962 l_msg_index := 1;
1963 WHILE l_msg_count > 0 loop
1964 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1965 l_msg_index := l_msg_index + 1;
1966 l_msg_count := l_msg_count - 1;
1967 END LOOP;
1968 RAISE fnd_api.g_exc_error;
1969 END IF;
1970
1971 ELSIF l_dest_instance_header_tbl.count > 1 THEN
1972
1973 -- Multiple Instances were found so throw error
1974 IF (l_debug > 0) THEN
1975 csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
1976 END IF;
1977 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
1978 fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
1979 fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
1980 fnd_message.set_token('INV_ORG_ID',l_mtl_item_tbl(j).organization_id);
1981 fnd_message.set_token('LOCATOR',l_mtl_item_tbl(j).locator_id);
1982 l_error_message := fnd_message.get;
1983 RAISE fnd_api.g_exc_error;
1984
1985 END IF; -- End of Destination Record If
1986 /***
1987 ELSIF l_src_instance_header_tbl.count > 1 THEN
1988 -- Multiple Instances were found so throw error
1989 IF (l_debug > 0) THEN
1990 csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
1991 END IF;
1992 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
1993 fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
1994 fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
1995 fnd_message.set_token('INV_ORG_ID',l_mtl_item_tbl(j).organization_id);
1996 fnd_message.set_token('LOCATOR',l_mtl_item_tbl(j).locator_id);
1997 l_error_message := fnd_message.get;
1998 RAISE fnd_api.g_exc_error;
1999 ***/
2000 ELSIF l_src_instance_header_tbl.count = 0 THEN -- No IB Records found
2001
2002 csi_t_gen_utility_pvt.add('No Source Records in a HZ/HR Location Exist. Query for the inventory record in the Org to see if it exists. If it does then add to that instance otherwise create a new instance that is located in Inventory');
2003
2004 l_instance_dest_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
2005 l_instance_dest_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2006 l_instance_dest_query_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
2007 l_instance_dest_query_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
2008 l_instance_dest_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
2009 l_instance_dest_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2010 l_instance_dest_query_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
2011 l_instance_dest_query_rec.serial_number := NULL;
2012 l_instance_dest_query_rec.instance_usage_code := l_in_inventory;
2013
2014 IF (l_debug > 0) THEN
2015 csi_t_gen_utility_pvt.add('Before Get Item Instance');
2016 END IF;
2017
2018 csi_item_instance_pub.get_item_instances(l_api_version,
2019 l_commit,
2020 l_init_msg_list,
2021 l_validation_level,
2022 l_instance_dest_query_rec,
2023 l_party_query_rec,
2024 l_account_query_rec,
2025 l_transaction_id,
2026 l_resolve_id_columns,
2027 l_inactive_instance_only,
2028 l_dest_instance_header_tbl,
2029 l_return_status,
2030 l_msg_count,
2031 l_msg_data);
2032
2033 IF (l_debug > 0) THEN
2034 csi_t_gen_utility_pvt.add('After Get Item Instance');
2040 END IF;
2035 END IF;
2036 l_tbl_count := 0;
2037 l_tbl_count := l_dest_instance_header_tbl.count;
2038 IF (l_debug > 0) THEN
2039 csi_t_gen_utility_pvt.add('Destination Records Found: '||l_tbl_count);
2041
2042 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2043 IF NOT l_return_status = l_fnd_success then
2044 IF (l_debug > 0) THEN
2045 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
2046 END IF;
2047 l_msg_index := 1;
2048 WHILE l_msg_count > 0 loop
2049 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2050 l_msg_index := l_msg_index + 1;
2051 l_msg_count := l_msg_count - 1;
2052 END LOOP;
2053 RAISE fnd_api.g_exc_error;
2054 END IF;
2055
2056 IF l_dest_instance_header_tbl.count = 0 THEN
2057
2058 l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
2059 l_new_dest_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2060 l_new_dest_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
2061 l_new_dest_instance_rec.inv_master_organization_id := l_master_organization_id;
2062 l_new_dest_instance_rec.mfg_serial_number_flag := 'N';
2063 l_new_dest_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2064 l_new_dest_instance_rec.quantity := abs(l_mtl_item_tbl(j).transaction_quantity);
2065 l_new_dest_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
2066 l_new_dest_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
2067 l_new_dest_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
2068 l_new_dest_instance_rec.instance_usage_code := l_in_inventory;
2069 l_new_dest_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
2070 l_new_dest_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
2071 l_new_dest_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
2072 l_new_dest_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
2073 l_new_dest_instance_rec.customer_view_flag := 'N';
2074 l_new_dest_instance_rec.merchant_view_flag := 'Y';
2075 l_new_dest_instance_rec.active_start_date := l_sysdate;
2076 l_new_dest_instance_rec.active_end_date := NULL;
2077 l_new_dest_instance_rec.operational_status_code := 'NOT_USED';
2078 l_new_dest_instance_rec.object_version_number := l_object_version_number;
2079
2080 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
2081 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
2082 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
2083 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
2084 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2085 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2086
2087 l_new_dest_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
2088
2089 IF (l_debug > 0) THEN
2090 csi_t_gen_utility_pvt.add('Before Create Item Instance');
2091 END IF;
2092
2093 csi_item_instance_pub.create_item_instance(l_api_version,
2094 l_commit,
2095 l_init_msg_list,
2096 l_validation_level,
2097 l_new_dest_instance_rec,
2098 l_ext_attrib_values_tbl,
2099 l_party_tbl,
2100 l_account_tbl,
2101 l_pricing_attrib_tbl,
2102 l_org_assignments_tbl,
2103 l_asset_assignment_tbl,
2104 l_txn_rec,
2105 l_return_status,
2109 IF (l_debug > 0) THEN
2106 l_msg_count,
2107 l_msg_data);
2108
2110 csi_t_gen_utility_pvt.add('After Update Item Instance');
2111 END IF;
2112
2113 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2114 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2115 IF (l_debug > 0) THEN
2116 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
2117 END IF;
2118 l_msg_index := 1;
2119 WHILE l_msg_count > 0 loop
2120 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2121 l_msg_index := l_msg_index + 1;
2122 l_msg_count := l_msg_count - 1;
2123 END LOOP;
2124 RAISE fnd_api.g_exc_error;
2125 END IF;
2126
2127 ELSIF l_dest_instance_header_tbl.count = 1 THEN
2128
2129 l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2130 l_update_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
2131 l_update_instance_rec.quantity := l_dest_instance_header_tbl(i).quantity + abs(l_mtl_item_tbl(j).primary_quantity);
2132 l_update_instance_rec.active_end_date := NULL;
2133 l_update_instance_rec.object_version_number := l_dest_instance_header_tbl(i).object_version_number;
2134
2135 l_party_tbl.delete;
2136 l_account_tbl.delete;
2137 l_pricing_attrib_tbl.delete;
2138 l_org_assignments_tbl.delete;
2139 l_asset_assignment_tbl.delete;
2140
2141 l_update_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
2142
2143 IF (l_debug > 0) THEN
2144 csi_t_gen_utility_pvt.add('Before Update Item Instance');
2145 END IF;
2146
2147 csi_item_instance_pub.update_item_instance(l_api_version,
2148 l_commit,
2149 l_init_msg_list,
2150 l_validation_level,
2151 l_update_instance_rec,
2152 l_ext_attrib_values_tbl,
2153 l_party_tbl,
2154 l_account_tbl,
2155 l_pricing_attrib_tbl,
2156 l_org_assignments_tbl,
2157 l_asset_assignment_tbl,
2158 l_txn_rec,
2159 l_instance_id_lst,
2160 l_return_status,
2161 l_msg_count,
2162 l_msg_data);
2163
2164 l_upd_error_instance_id := NULL;
2165 l_upd_error_instance_id := l_update_instance_rec.instance_id;
2166
2167 IF (l_debug > 0) THEN
2168 csi_t_gen_utility_pvt.add('After Update Item Instance');
2169 csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2170 END IF;
2171
2172 -- Check for any errors and add them to the message stack to pass out to be put into the
2173 -- error log table.
2174 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2175 IF (l_debug > 0) THEN
2176 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
2177 END IF;
2178 l_msg_index := 1;
2179 WHILE l_msg_count > 0 loop
2180 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2181 l_msg_index := l_msg_index + 1;
2182 l_msg_count := l_msg_count - 1;
2183 END LOOP;
2184 RAISE fnd_api.g_exc_error;
2185 END IF;
2186
2187 ELSIF l_src_instance_header_tbl.count > 1 THEN
2188 -- Multiple Instances were found so throw error
2189 IF (l_debug > 0) THEN
2190 csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
2191 END IF;
2192 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
2193 fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
2194 fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
2195 fnd_message.set_token('INV_ORG_ID',l_mtl_item_tbl(j).organization_id);
2196 fnd_message.set_token('LOCATOR',l_mtl_item_tbl(j).locator_id);
2197 l_error_message := fnd_message.get;
2198 RAISE fnd_api.g_exc_error;
2199
2200 END IF; -- End of Destination Record If for checking for In Inventory Records because the Project/Source Instance was not there.
2201 END IF; -- End of No Records Found If
2202 END IF; -- End of Serial Number If
2203 END LOOP; -- End of For Loop
2204
2205 IF (l_debug > 0) THEN
2206 csi_t_gen_utility_pvt.add('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2207 csi_t_gen_utility_pvt.add('*****End of csi_inv_hz_pkg.misc_receipt_hz_loc Transaction procedure*****');
2208 END IF;
2212 IF (l_debug > 0) THEN
2209
2210 EXCEPTION
2211 WHEN fnd_api.g_exc_error THEN
2213 csi_t_gen_utility_pvt.add('You have encountered a "fnd_api.g_exc_error" exception');
2214 END IF;
2215 x_return_status := l_fnd_error;
2216 x_trx_error_rec.error_text := l_error_message;
2217
2218 IF l_mtl_item_tbl.count > 0 THEN
2219 x_trx_error_rec.serial_number := l_mtl_item_tbl(j).serial_number;
2220 x_trx_error_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2221 x_trx_error_rec.instance_id := l_upd_error_instance_id;
2222 x_trx_error_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2223 x_trx_error_rec.src_serial_num_ctrl_code := l_mtl_item_tbl(j).serial_number_control_code;
2224 x_trx_error_rec.src_location_ctrl_code := l_mtl_item_tbl(j).location_control_code;
2225 x_trx_error_rec.src_lot_ctrl_code := l_mtl_item_tbl(j).lot_control_code;
2226 x_trx_error_rec.src_rev_qty_ctrl_code := l_mtl_item_tbl(j).revision_qty_control_code;
2227 x_trx_error_rec.comms_nl_trackable_flag := l_mtl_item_tbl(j).comms_nl_trackable_flag;
2228 x_trx_error_rec.transaction_error_date := l_sysdate ;
2229 END IF;
2230
2231 x_trx_error_rec.transaction_id := NULL;
2232 x_trx_error_rec.source_type := 'CSIMSRHZ';
2233 x_trx_error_rec.source_id := p_transaction_id;
2234 x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
2235 x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('MISC_RECEIPT_HZ_LOC','INV');
2236 x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
2237 x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
2238
2239 WHEN others THEN
2240 IF (l_debug > 0) THEN
2241 csi_t_gen_utility_pvt.add('You have encountered a "others" exception');
2242 END IF;
2243 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
2244 fnd_message.set_token('API_NAME',l_api_name);
2245 fnd_message.set_token('SQL_ERROR',SQLERRM);
2246 x_return_status := l_fnd_unexpected;
2247
2248 IF l_mtl_item_tbl.count > 0 THEN
2249 x_trx_error_rec.serial_number := l_mtl_item_tbl(j).serial_number;
2250 x_trx_error_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2251 x_trx_error_rec.instance_id := l_upd_error_instance_id;
2252 x_trx_error_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2253 x_trx_error_rec.src_serial_num_ctrl_code := l_mtl_item_tbl(j).serial_number_control_code;
2254 x_trx_error_rec.src_location_ctrl_code := l_mtl_item_tbl(j).location_control_code;
2255 x_trx_error_rec.src_lot_ctrl_code := l_mtl_item_tbl(j).lot_control_code;
2256 x_trx_error_rec.src_rev_qty_ctrl_code := l_mtl_item_tbl(j).revision_qty_control_code;
2257 x_trx_error_rec.comms_nl_trackable_flag := l_mtl_item_tbl(j).comms_nl_trackable_flag;
2258 x_trx_error_rec.transaction_error_date := l_sysdate ;
2259 END IF;
2260
2261 x_trx_error_rec.error_text := fnd_message.get;
2262 x_trx_error_rec.transaction_id := NULL;
2263 x_trx_error_rec.source_type := 'CSIMSRHZ';
2264 x_trx_error_rec.source_id := p_transaction_id;
2265 x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
2266 x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('MISC_RECEIPT_HZ_LOC','INV');
2267 x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
2268 x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
2269
2270 END misc_receipt_hz_loc;
2271
2272 PROCEDURE misc_issue_hz_loc(p_transaction_id IN NUMBER,
2273 p_message_id IN NUMBER,
2274 x_return_status OUT NOCOPY VARCHAR2,
2275 x_trx_error_rec OUT NOCOPY CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC)
2276 IS
2277
2278 l_mtl_item_tbl CSI_INV_TRXS_PKG.MTL_ITEM_TBL_TYPE;
2279 l_api_name VARCHAR2(100) := 'CSI_INV_PROJECT_PKG.MISC_ISSUE_HZ_LOC';
2280 l_api_version NUMBER := 1.0;
2281 l_commit VARCHAR2(1) := FND_API.G_FALSE;
2282 l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
2283 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
2284 l_active_instance_only VARCHAR2(10) := FND_API.G_TRUE;
2285 l_inactive_instance_only VARCHAR2(10) := FND_API.G_FALSE;
2286 l_transaction_id NUMBER := NULL;
2287 l_resolve_id_columns VARCHAR2(10) := FND_API.G_FALSE;
2288 l_object_version_number NUMBER := 1;
2289 l_sysdate DATE := SYSDATE;
2290 l_master_organization_id NUMBER;
2291 l_depreciable VARCHAR2(1);
2292 l_instance_query_rec CSI_DATASTRUCTURES_PUB.INSTANCE_QUERY_REC;
2293 l_update_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
2294 l_update_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
2295 l_update_source_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
2296 l_new_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
2297 l_new_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
2298 l_new_src_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
2299 l_txn_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_REC;
2300 l_return_status VARCHAR2(1);
2301 l_error_code VARCHAR2(50);
2302 l_error_message VARCHAR2(4000);
2303 l_instance_id_lst CSI_DATASTRUCTURES_PUB.ID_TBL;
2304 l_party_query_rec CSI_DATASTRUCTURES_PUB.PARTY_QUERY_REC;
2308 l_ext_attrib_values_tbl CSI_DATASTRUCTURES_PUB.EXTEND_ATTRIB_VALUES_TBL;
2305 l_account_query_rec CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_QUERY_REC;
2306 l_src_instance_header_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_HEADER_TBL;
2307 l_dest_instance_header_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_HEADER_TBL;
2309 l_party_tbl CSI_DATASTRUCTURES_PUB.PARTY_TBL;
2310 l_account_tbl CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_TBL;
2311 l_pricing_attrib_tbl CSI_DATASTRUCTURES_PUB.PRICING_ATTRIBS_TBL;
2312 l_org_assignments_tbl CSI_DATASTRUCTURES_PUB.ORGANIZATION_UNITS_TBL;
2313 l_asset_assignment_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_ASSET_TBL;
2314 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2315 l_fnd_warning VARCHAR2(1) := 'W';
2316 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
2317 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
2318 l_in_inventory VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_INVENTORY;
2319 l_in_process VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_PROCESS;
2320 l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
2321 l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
2322 l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
2323 l_installed VARCHAR2(25) := CSI_INV_TRXS_PKG.G_INSTALLED;
2324 l_hz_loc_code VARCHAR2(25) := 'HZ_LOCATIONS';
2325 l_transaction_error_id NUMBER;
2326 l_quantity NUMBER;
2327 l_mfg_serial_flag VARCHAR2(1);
2328 l_ins_number VARCHAR2(100);
2329 l_ins_id NUMBER;
2330 l_file VARCHAR2(500);
2331 l_trx_type_id NUMBER;
2332 l_msg_count NUMBER;
2333 l_msg_data VARCHAR2(2000);
2334 l_msg_index NUMBER;
2335 l_employee_id NUMBER;
2336 j PLS_INTEGER;
2337 i PLS_INTEGER := 1;
2338 l_tbl_count NUMBER := 0;
2339 l_neg_code NUMBER := 0;
2340 l_instance_status VARCHAR2(1);
2341 l_redeploy_flag VARCHAR2(1);
2342 l_upd_error_instance_id NUMBER := NULL;
2343 l_hz_location NUMBER := NULL;
2344 l_hr_location NUMBER := NULL;
2345 l_location_type VARCHAR2(50);
2346
2347 cursor c_id is
2348 SELECT instance_status_id
2349 FROM csi_instance_statuses
2350 WHERE name = FND_PROFILE.VALUE('CSI_DEFAULT_INSTANCE_STATUS');
2351
2352 r_id c_id%rowtype;
2353
2354 cursor c_hz_loc (pc_location_id IN NUMBER) is
2355 SELECT 1
2356 FROM hz_locations
2357 WHERE location_id = pc_location_id;
2358
2359 cursor c_hr_loc (pc_location_id IN NUMBER) is
2360 SELECT 1
2361 FROM hr_locations
2362 WHERE location_id = pc_location_id;
2363
2364 BEGIN
2365
2366 x_return_status := l_fnd_success;
2367
2368 IF (l_debug > 0) THEN
2369 csi_t_gen_utility_pvt.add('*****Start of csi_inv_hz_pkg.misc_issue_hz_loc Transaction procedure*****');
2370 csi_t_gen_utility_pvt.add('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2371 csi_t_gen_utility_pvt.add('Transaction You are Processing is: '||p_transaction_id);
2372 csi_t_gen_utility_pvt.add('csiivtzb.pls 115.14');
2373 END IF;
2374
2375 -- This procedure queries all of the Inventory Transaction Records
2376 -- and returns them as a table.
2377
2378 csi_inv_trxs_pkg.get_transaction_recs(p_transaction_id,
2379 l_mtl_item_tbl,
2380 l_return_status,
2381 l_error_message);
2382
2383 l_tbl_count := 0;
2384 l_tbl_count := l_mtl_item_tbl.count;
2385 IF (l_debug > 0) THEN
2386 csi_t_gen_utility_pvt.add('Inventory Records Found: '||l_tbl_count);
2387 END IF;
2388
2389 IF NOT l_return_status = l_fnd_success THEN
2390 IF (l_debug > 0) THEN
2391 csi_t_gen_utility_pvt.add('You have encountered an error in CSI_INV_TRXS_PKG.get_transaction_recs, Transaction ID: '||p_transaction_id);
2392 END IF;
2393 RAISE fnd_api.g_exc_error;
2394 END IF;
2395
2396 -- Get the Master Organization ID
2397 csi_inv_trxs_pkg.get_master_organization(l_mtl_item_tbl(i).organization_id,
2398 l_master_organization_id,
2399 l_return_status,
2400 l_error_message);
2401
2402 IF NOT l_return_status = l_fnd_success THEN
2403 IF (l_debug > 0) THEN
2404 csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.get_master_organization, Organization ID: '||l_mtl_item_tbl(i).organization_id);
2405 END IF;
2406 RAISE fnd_api.g_exc_error;
2407 END IF;
2408
2409 -- Call get_fnd_employee_id and get the employee id
2410 l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
2411
2412 IF l_employee_id = -1 THEN
2413 IF (l_debug > 0) THEN
2414 csi_t_gen_utility_pvt.add('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
2415 END IF;
2416 END IF;
2417 IF (l_debug > 0) THEN
2418 csi_t_gen_utility_pvt.add('The Employee that is processing this Transaction is: '||l_employee_id);
2419 END IF;
2420
2424 l_mtl_item_tbl(i).organization_id); --Added for Bug 13988660
2421 -- See if this is a depreciable Item to set the status of the transaction record
2422 csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
2423 l_depreciable,
2425
2426 IF (l_debug > 0) THEN
2427 csi_t_gen_utility_pvt.add('Is this Item ID: '||l_mtl_item_tbl(i).inventory_item_id||', Depreciable :'||l_depreciable);
2428 END IF;
2429
2430
2431 -- Set the mfg_serial_number_flag and quantity
2432 IF l_mtl_item_tbl(i).serial_number is NULL THEN
2433 l_mfg_serial_flag := 'N';
2434 l_quantity := l_mtl_item_tbl(i).transaction_quantity;
2435 ELSE
2436 l_mfg_serial_flag := 'Y';
2437 l_quantity := 1;
2438 END IF;
2439
2440 -- Get the Negative Receipt Code to see if this org allows Negative
2441 -- Quantity Records 1 = Yes, 2 = No
2442
2443 l_neg_code := csi_inv_trxs_pkg.get_neg_inv_code(
2444 l_mtl_item_tbl(i).organization_id);
2445
2446 IF l_neg_code = 1 AND l_mtl_item_tbl(i).serial_number is NULL THEN
2447 l_instance_status := FND_API.G_FALSE;
2448 ELSE
2449 l_instance_status := FND_API.G_TRUE;
2450 END IF;
2451
2452 IF (l_debug > 0) THEN
2453 csi_t_gen_utility_pvt.add('Negative Code is - 1 = Yes, 2 = No: '||l_neg_code);
2454 END IF;
2455
2456 -- Get Instance Status ID
2457 OPEN c_id;
2458 FETCH c_id into r_id;
2459 CLOSE c_id;
2460
2461 -- Decide if Location ID is from HZ or HR (Before txn Creation)
2462
2463 open c_hz_loc (l_mtl_item_tbl(i).ship_to_location_id);
2464 fetch c_hz_loc into l_hz_location;
2465
2466 IF l_hz_location IS NOT NULL THEN
2467 l_location_type := 'HZ_LOCATIONS';
2468 close c_hz_loc;
2469 ELSE
2470 close c_hz_loc;
2471 open c_hr_loc (l_mtl_item_tbl(i).ship_to_location_id);
2472 fetch c_hr_loc into l_hr_location;
2473 IF l_hr_location IS NOT NULL THEN
2474 l_location_type := 'INTERNAL_SITE';
2475 close c_hr_loc;
2476 END IF;
2477 END IF;
2478
2479 IF (l_debug > 0) THEN
2480 csi_t_gen_utility_pvt.add('Location ID - Location Type; '||l_mtl_item_tbl(i).ship_to_location_id||'-'||l_location_type);
2481 END IF;
2482
2483 -- Create CSI Transaction to be used
2484 l_txn_rec := csi_inv_trxs_pkg.init_txn_rec;
2485 l_txn_rec.source_transaction_date := l_mtl_item_tbl(i).transaction_date;
2486 l_txn_rec.transaction_date := l_sysdate;
2487 l_txn_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('MISC_ISSUE_HZ_LOC','INV');
2488 l_txn_rec.transaction_quantity := l_mtl_item_tbl(i).transaction_quantity;
2489 l_txn_rec.transaction_uom_code := l_mtl_item_tbl(i).transaction_uom;
2490 l_txn_rec.transacted_by := l_employee_id;
2491 l_txn_rec.transaction_action_code := NULL;
2492 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
2493 l_txn_rec.message_id := p_message_id;
2494 l_txn_rec.inv_material_transaction_id := p_transaction_id;
2495 l_txn_rec.object_version_number := l_object_version_number;
2496 l_txn_rec.source_group_ref := l_location_type;
2497
2498 csi_inv_trxs_pkg.create_csi_txn(l_txn_rec,
2499 l_error_message,
2500 l_return_status);
2501
2502 IF (l_debug > 0) THEN
2503 csi_t_gen_utility_pvt.add('CSI Transaction Created: '||l_txn_rec.transaction_id);
2504 END IF;
2505
2506 IF NOT l_return_status = l_fnd_success THEN
2507 IF (l_debug > 0) THEN
2508 csi_t_gen_utility_pvt.add('You have encountered an error in csi_inv_trxs_pkg.create_csi_txn: '||p_transaction_id);
2509 END IF;
2510 RAISE fnd_api.g_exc_error;
2511 END IF;
2512
2513 -- Now loop through the PL/SQL Table.
2514 j := 1;
2515
2516 IF (l_debug > 0) THEN
2517 csi_t_gen_utility_pvt.add('Starting to loop through Material Transaction Records');
2518 END IF;
2519
2520 FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
2521
2522 IF (l_debug > 0) THEN
2523 csi_t_gen_utility_pvt.add('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
2524 csi_t_gen_utility_pvt.add('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
2525 csi_t_gen_utility_pvt.add('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
2526 csi_t_gen_utility_pvt.add('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
2527 END IF;
2528
2529 l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
2530 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2531 l_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
2532 l_instance_query_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
2533 l_instance_query_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
2534 l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2535 l_instance_query_rec.serial_number := l_mtl_item_tbl(j).serial_number;
2536 l_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
2537 --l_instance_query_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
2538 l_instance_query_rec.instance_usage_code := l_in_inventory;
2539
2540 IF (l_debug > 0) THEN
2544
2541 csi_t_gen_utility_pvt.add('Before Get Item Instance');
2542 csi_t_gen_utility_pvt.add('Ship to Location ID: '||l_mtl_item_tbl(j).ship_to_location_id);
2543 END IF;
2545 csi_item_instance_pub.get_item_instances(l_api_version,
2546 l_commit,
2547 l_init_msg_list,
2548 l_validation_level,
2549 l_instance_query_rec,
2550 l_party_query_rec,
2551 l_account_query_rec,
2552 l_transaction_id,
2553 l_resolve_id_columns,
2554 l_instance_status,
2555 l_src_instance_header_tbl,
2556 l_return_status,
2557 l_msg_count,
2558 l_msg_data);
2559
2560 IF (l_debug > 0) THEN
2561 csi_t_gen_utility_pvt.add('After Get Item Instance');
2562 END IF;
2563 l_tbl_count := 0;
2564 l_tbl_count := l_src_instance_header_tbl.count;
2565 IF (l_debug > 0) THEN
2566 csi_t_gen_utility_pvt.add('Source Records Found: '||l_tbl_count);
2567 END IF;
2568
2569 -- Check for any errors and add them to the message stack to pass out to be put into the
2570 -- error log table.
2571 IF NOT l_return_status = l_fnd_success then
2572 IF (l_debug > 0) THEN
2573 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
2574 END IF;
2575 l_msg_index := 1;
2576 WHILE l_msg_count > 0 loop
2577 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2578 l_msg_index := l_msg_index + 1;
2579 l_msg_count := l_msg_count - 1;
2580 END LOOP;
2581 RAISE fnd_api.g_exc_error;
2582 END IF;
2583
2584
2585 IF l_mtl_item_tbl(j).serial_number is NULL THEN
2586 IF l_src_instance_header_tbl.count = 0 THEN
2587 IF l_neg_code = 1 THEN -- Allow Neg Qtys on NON Serial Items ONLY
2588
2589 IF (l_debug > 0) THEN
2590 csi_t_gen_utility_pvt.add('No records were found so create a new Source Instance Record');
2591 END IF;
2592
2593 l_new_src_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
2594 l_new_src_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2595 l_new_src_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
2596 l_new_src_instance_rec.inv_master_organization_id := l_master_organization_id;
2597 l_new_src_instance_rec.mfg_serial_number_flag := 'N';
2598 l_new_src_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2599 l_new_src_instance_rec.quantity := l_mtl_item_tbl(j).transaction_quantity;
2600 l_new_src_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
2601 l_new_src_instance_rec.instance_usage_code := l_in_inventory;
2602 l_new_src_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
2603 l_new_src_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
2604 l_new_src_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
2605 l_new_src_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
2606 l_new_src_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
2607 l_new_src_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
2608 l_new_src_instance_rec.customer_view_flag := 'N';
2609 l_new_src_instance_rec.merchant_view_flag := 'Y';
2610 l_new_src_instance_rec.operational_status_code := 'NOT_USED';
2611 l_new_src_instance_rec.object_version_number := l_object_version_number;
2612 l_new_src_instance_rec.active_start_date := l_sysdate;
2613 l_new_src_instance_rec.active_end_date := NULL;
2614 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
2615 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
2616 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
2617 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
2618 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2619 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2620
2621 IF (l_debug > 0) THEN
2622 csi_t_gen_utility_pvt.add('Before Create Transaction - Neg Qty');
2623 END IF;
2624
2625 csi_item_instance_pub.create_item_instance(l_api_version,
2626 l_commit,
2627 l_init_msg_list,
2628 l_validation_level,
2629 l_new_src_instance_rec,
2630 l_ext_attrib_values_tbl,
2631 l_party_tbl,
2635 l_asset_assignment_tbl,
2632 l_account_tbl,
2633 l_pricing_attrib_tbl,
2634 l_org_assignments_tbl,
2636 l_txn_rec,
2637 l_return_status,
2638 l_msg_count,
2639 l_msg_data);
2640
2641 IF (l_debug > 0) THEN
2642 csi_t_gen_utility_pvt.add('After Create Transaction');
2643 csi_t_gen_utility_pvt.add('New instance created is: '||l_new_src_instance_rec.instance_id);
2644 END IF;
2645
2646 -- Check for any errors and add them to the message stack to pass out to be put into the
2647 -- error log table.
2648 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2649 IF (l_debug > 0) THEN
2650 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
2651 END IF;
2652 l_msg_index := 1;
2653 WHILE l_msg_count > 0 loop
2654 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2655 l_msg_index := l_msg_index + 1;
2656 l_msg_count := l_msg_count - 1;
2657 END LOOP;
2658 RAISE fnd_api.g_exc_error;
2659 END IF;
2660
2661 ELSE -- No Records were found and Neg Qtys Not Allowed
2662 IF (l_debug > 0) THEN
2663 csi_t_gen_utility_pvt.add('No Records were found in Install Base andNeg Qtys not allowed to error');
2664 END IF;
2665 fnd_message.set_name('CSI','CSI_NO_NEG_BAL_ALLOWED');
2666 l_error_message := fnd_message.get;
2667 RAISE fnd_api.g_exc_error;
2668
2669 END IF; -- Neg Qty IF
2670
2671 ELSIF l_src_instance_header_tbl.count = 1 THEN
2672
2673 IF (l_debug > 0) THEN
2674 csi_t_gen_utility_pvt.add('You will update instance: '||l_src_instance_header_tbl(i).instance_id);
2675 END IF;
2676
2677 l_update_source_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2678 l_update_source_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
2679 l_update_source_instance_rec.quantity := l_src_instance_header_tbl(i).quantity - abs(l_mtl_item_tbl(j).primary_quantity);
2680 l_update_source_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
2681
2682 l_party_tbl.delete;
2683 l_account_tbl.delete;
2684 l_pricing_attrib_tbl.delete;
2685 l_org_assignments_tbl.delete;
2686 l_asset_assignment_tbl.delete;
2687
2688 IF (l_debug > 0) THEN
2689 csi_t_gen_utility_pvt.add('Before Update Item Instance - 80');
2690 END IF;
2691
2692 l_update_source_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
2693
2694 IF (l_debug > 0) THEN
2695 csi_t_gen_utility_pvt.add('Instance Status Id: '||l_update_source_instance_rec.instance_status_id);
2696 END IF;
2697
2698 csi_item_instance_pub.update_item_instance(l_api_version,
2699 l_commit,
2700 l_init_msg_list,
2701 l_validation_level,
2702 l_update_source_instance_rec,
2703 l_ext_attrib_values_tbl,
2704 l_party_tbl,
2705 l_account_tbl,
2706 l_pricing_attrib_tbl,
2707 l_org_assignments_tbl,
2708 l_asset_assignment_tbl,
2709 l_txn_rec,
2710 l_instance_id_lst,
2711 l_return_status,
2712 l_msg_count,
2713 l_msg_data);
2714
2715 l_upd_error_instance_id := NULL;
2716 l_upd_error_instance_id := l_update_source_instance_rec.instance_id;
2717
2718 IF (l_debug > 0) THEN
2719 csi_t_gen_utility_pvt.add('After Update Item Instance - Neg Qty');
2720 csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2721 END IF;
2722
2723 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2724 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2725 IF (l_debug > 0) THEN
2726 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
2727 END IF;
2728 l_msg_index := 1;
2729 WHILE l_msg_count > 0 loop
2730 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2731 l_msg_index := l_msg_index + 1;
2732 l_msg_count := l_msg_count - 1;
2733 END LOOP;
2734 RAISE fnd_api.g_exc_error;
2735 END IF;
2736
2737 ELSIF l_src_instance_header_tbl.count > 1 THEN
2738 -- Multiple Instances were found so throw error
2739 IF (l_debug > 0) THEN
2743 fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
2740 csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-30');
2741 END IF;
2742 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
2744 fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
2745 fnd_message.set_token('INV_ORG_ID',l_mtl_item_tbl(j).organization_id);
2746 fnd_message.set_token('LOCATOR',l_mtl_item_tbl(j).locator_id);
2747 l_error_message := fnd_message.get;
2748 RAISE fnd_api.g_exc_error;
2749
2750 END IF; -- End of Source Record If
2751
2752 -- Now query and get the destination record.
2753 l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
2754 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2755 l_instance_query_rec.location_id := l_mtl_item_tbl(j).ship_to_location_id;
2756 l_instance_query_rec.serial_number := NULL;
2757 --l_instance_query_rec.location_type_code := l_hz_loc_code;
2758 l_instance_query_rec.location_type_code := l_location_type;
2759 l_instance_query_rec.pa_project_id := NULL;
2760 l_instance_query_rec.pa_project_task_id := NULL;
2761 l_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
2762 -- Bug 10202423
2763 l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2764 l_instance_query_rec.instance_usage_code := l_in_service;
2765
2766 IF (l_debug > 0) THEN
2767 csi_t_gen_utility_pvt.add('Before Get Item Instance Dest - Neg Qty');
2768 csi_t_gen_utility_pvt.add('Ship to Location ID Non Ser: '||l_mtl_item_tbl(j).ship_to_location_id);
2769 csi_t_gen_utility_pvt.add('Location Type Code: '||l_location_type);
2770 END IF;
2771
2772 csi_item_instance_pub.get_item_instances(l_api_version,
2773 l_commit,
2774 l_init_msg_list,
2775 l_validation_level,
2776 l_instance_query_rec,
2777 l_party_query_rec,
2778 l_account_query_rec,
2779 l_transaction_id,
2780 l_resolve_id_columns,
2781 l_inactive_instance_only,
2782 l_dest_instance_header_tbl,
2783 l_return_status,
2784 l_msg_count,
2785 l_msg_data);
2786
2787 IF (l_debug > 0) THEN
2788 csi_t_gen_utility_pvt.add('After Get Item Instance Dest - Neg Qty');
2789 END IF;
2790 l_tbl_count := 0;
2791 l_tbl_count := l_dest_instance_header_tbl.count;
2792 IF (l_debug > 0) THEN
2793 csi_t_gen_utility_pvt.add('Destination Records Found: '||l_tbl_count);
2794 END IF;
2795
2796 -- Check for any errors and add them to the message stack to pass out to be put into the
2797 -- error log table.
2798 IF NOT l_return_status = l_fnd_success then
2799 IF (l_debug > 0) THEN
2800 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
2801 END IF;
2802 l_msg_index := 1;
2803 WHILE l_msg_count > 0 loop
2804 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2805 l_msg_index := l_msg_index + 1;
2806 l_msg_count := l_msg_count - 1;
2807 END LOOP;
2808 RAISE fnd_api.g_exc_error;
2809 END IF;
2810
2811 IF l_dest_instance_header_tbl.count = 0 THEN -- Installed Base Destination Records are not found
2812 IF (l_debug > 0) THEN
2813 csi_t_gen_utility_pvt.add('No Destination Records were found so we will create a new destination Record using the source data');
2814 END IF;
2815
2816 l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
2817 l_new_dest_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2818 l_new_dest_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
2819 l_new_dest_instance_rec.inv_master_organization_id := l_master_organization_id;
2820 l_new_dest_instance_rec.mfg_serial_number_flag := 'N';
2821 l_new_dest_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2822 l_new_dest_instance_rec.quantity := abs(l_mtl_item_tbl(j).transaction_quantity);
2823 l_new_dest_instance_rec.active_start_date := l_sysdate;
2824 l_new_dest_instance_rec.active_end_date := NULL;
2825 l_new_dest_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
2826 l_new_dest_instance_rec.instance_usage_code := l_in_service;
2827 l_new_dest_instance_rec.inv_locator_id := NULL;
2828 l_new_dest_instance_rec.location_id := l_mtl_item_tbl(j).ship_to_location_id;
2829 l_new_dest_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code(l_location_type);
2833 l_new_dest_instance_rec.pa_project_id := NULL;
2830 --l_new_dest_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('hz_locations');
2831 l_new_dest_instance_rec.inv_organization_id := NULL;
2832 l_new_dest_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
2834 l_new_dest_instance_rec.pa_project_task_id := NULL;
2835 l_new_dest_instance_rec.customer_view_flag := 'N';
2836 l_new_dest_instance_rec.merchant_view_flag := 'Y';
2837 l_new_dest_instance_rec.operational_status_code := 'IN_SERVICE';
2838 l_new_dest_instance_rec.object_version_number := l_object_version_number;
2839 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
2840 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
2841 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
2842 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
2843 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2844 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2845
2846 IF (l_debug > 0) THEN
2847 csi_t_gen_utility_pvt.add('Before Create Item Instance - Neg Qty');
2848 END IF;
2849
2850 csi_item_instance_pub.create_item_instance(l_api_version,
2851 l_commit,
2852 l_init_msg_list,
2853 l_validation_level,
2854 l_new_dest_instance_rec,
2855 l_ext_attrib_values_tbl,
2856 l_party_tbl,
2857 l_account_tbl,
2858 l_pricing_attrib_tbl,
2859 l_org_assignments_tbl,
2860 l_asset_assignment_tbl,
2861 l_txn_rec,
2862 l_return_status,
2863 l_msg_count,
2864 l_msg_data);
2865
2866 IF (l_debug > 0) THEN
2867 csi_t_gen_utility_pvt.add('After Create Item Instance - Neg Qty');
2868 END IF;
2869
2870 -- Check for any errors and add them to the message stack to pass out to be put into the
2871 -- error log table.
2872 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2873 IF (l_debug > 0) THEN
2874 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
2875 END IF;
2876 l_msg_index := 1;
2877 WHILE l_msg_count > 0 loop
2878 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2879 l_msg_index := l_msg_index + 1;
2880 l_msg_count := l_msg_count - 1;
2881 END LOOP;
2882 RAISE fnd_api.g_exc_error;
2883 END IF;
2884
2885 ELSIF l_dest_instance_header_tbl.count = 1 THEN-- Installed Base Destination Records Found
2886
2887 IF (l_debug > 0) THEN
2888 csi_t_gen_utility_pvt.add('You will update instance: '||l_dest_instance_header_tbl(i).instance_id);
2889 END IF;
2890
2891 l_update_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2892 l_update_dest_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
2893 l_update_dest_instance_rec.quantity := l_dest_instance_header_tbl(i).quantity + abs(l_mtl_item_tbl(j).primary_quantity);
2894 l_update_dest_instance_rec.active_end_date := NULL;
2895 l_update_dest_instance_rec.object_version_number := l_dest_instance_header_tbl(i).object_version_number;
2896
2897 l_party_tbl.delete;
2898 l_account_tbl.delete;
2899 l_pricing_attrib_tbl.delete;
2900 l_org_assignments_tbl.delete;
2901 l_asset_assignment_tbl.delete;
2902
2903 l_update_dest_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
2904
2905 IF (l_debug > 0) THEN
2906 csi_t_gen_utility_pvt.add('Before Update Transaction - Neg Qty');
2907 END IF;
2908
2909 csi_item_instance_pub.update_item_instance(l_api_version,
2910 l_commit,
2911 l_init_msg_list,
2912 l_validation_level,
2913 l_update_dest_instance_rec,
2914 l_ext_attrib_values_tbl,
2915 l_party_tbl,
2916 l_account_tbl,
2917 l_pricing_attrib_tbl,
2918 l_org_assignments_tbl,
2922 l_return_status,
2919 l_asset_assignment_tbl,
2920 l_txn_rec,
2921 l_instance_id_lst,
2923 l_msg_count,
2924 l_msg_data);
2925
2926 l_upd_error_instance_id := NULL;
2927 l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
2928
2929 IF (l_debug > 0) THEN
2930 csi_t_gen_utility_pvt.add('After Update Transaction - Neg Qty');
2931 csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2932 END IF;
2933
2934 -- Check for any errors and add them to the message stack to pass out to be put into the
2935 -- error log table.
2936 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2937 IF (l_debug > 0) THEN
2938 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
2939 END IF;
2940 l_msg_index := 1;
2941 WHILE l_msg_count > 0 loop
2942 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2943 l_msg_index := l_msg_index + 1;
2944 l_msg_count := l_msg_count - 1;
2945 END LOOP;
2946 RAISE fnd_api.g_exc_error;
2947 END IF;
2948
2949 ELSIF l_dest_instance_header_tbl.count > 1 THEN
2950
2951 -- Multiple Instances were found so throw error
2952 IF (l_debug > 0) THEN
2953 csi_t_gen_utility_pvt.add('Multiple Instances were Found in Install Base-90');
2954 END IF;
2955 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
2956 fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
2957 fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
2958 fnd_message.set_token('INV_ORG_ID',l_mtl_item_tbl(j).organization_id);
2959 fnd_message.set_token('LOCATOR',l_mtl_item_tbl(j).locator_id);
2960 l_error_message := fnd_message.get;
2961 RAISE fnd_api.g_exc_error;
2962
2963 END IF; -- End of Destination Record If
2964
2965 ELSIF l_mtl_item_tbl(j).serial_number is NOT NULL THEN
2966 IF l_src_instance_header_tbl.count = 1 THEN -- Installed Base Records Found
2967 l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2968 l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
2969 l_update_instance_rec.lot_number := l_src_instance_header_tbl(i).lot_number;
2970 l_update_instance_rec.inv_subinventory_name := NULL;
2971 -- Added for Bug 5975739
2972 l_update_instance_rec.inv_master_organization_id := l_master_organization_id;
2973 l_update_instance_rec.inv_organization_id := NULL;
2974 l_update_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
2975 l_update_instance_rec.inv_locator_id := NULL;
2976 l_update_instance_rec.location_id := l_mtl_item_tbl(j).ship_to_location_id;
2977 l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code(l_location_type);
2978 --l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('hz_locations');
2979 l_update_instance_rec.pa_project_id := NULL;
2980 l_update_instance_rec.pa_project_task_id := NULL;
2981 l_update_instance_rec.instance_usage_code := l_in_service;
2982 l_update_instance_rec.operational_status_code := 'IN_SERVICE';
2983 l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
2984
2985 l_party_tbl.delete;
2986 l_account_tbl.delete;
2987 l_pricing_attrib_tbl.delete;
2988 l_org_assignments_tbl.delete;
2989 l_asset_assignment_tbl.delete;
2990
2991 IF (l_debug > 0) THEN
2992 csi_t_gen_utility_pvt.add('Before Update Item Instance');
2993 csi_t_gen_utility_pvt.add('Ship to Location ID Serialized: '||l_mtl_item_tbl(j).ship_to_location_id);
2994 END IF;
2995
2996 csi_item_instance_pub.update_item_instance(l_api_version,
2997 l_commit,
2998 l_init_msg_list,
2999 l_validation_level,
3000 l_update_instance_rec,
3001 l_ext_attrib_values_tbl,
3002 l_party_tbl,
3003 l_account_tbl,
3004 l_pricing_attrib_tbl,
3005 l_org_assignments_tbl,
3006 l_asset_assignment_tbl,
3007 l_txn_rec,
3008 l_instance_id_lst,
3009 l_return_status,
3010 l_msg_count,
3014 l_upd_error_instance_id := l_update_instance_rec.instance_id;
3011 l_msg_data);
3012
3013 l_upd_error_instance_id := NULL;
3015
3016 IF (l_debug > 0) THEN
3017 csi_t_gen_utility_pvt.add('After Update Item Instance');
3018 csi_t_gen_utility_pvt.add('l_upd_error_instance_id is: '||l_upd_error_instance_id);
3019 END IF;
3020
3021 -- Check for any errors and add them to the message stack to pass out to be put into the
3022 -- error log table.
3023 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
3024 IF (l_debug > 0) THEN
3025 csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
3026 END IF;
3027 l_msg_index := 1;
3028 WHILE l_msg_count > 0 loop
3029 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
3030 l_msg_index := l_msg_index + 1;
3031 l_msg_count := l_msg_count - 1;
3032 END LOOP;
3033 RAISE fnd_api.g_exc_error;
3034 END IF;
3035
3036 ELSIF l_src_instance_header_tbl.count = 0 THEN
3037 IF (l_debug > 0) THEN
3038 csi_t_gen_utility_pvt.add('No Records were found in Install Base');
3039 END IF;
3040 fnd_message.set_name('CSI','CSI_IB_RECORD_NOTFOUND');
3041 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
3042 fnd_message.set_token('SUBINVENTORY',l_mtl_item_tbl(j).subinventory_code);
3043 fnd_message.set_token('ORG_ID',l_mtl_item_tbl(j).organization_id);
3044 l_error_message := fnd_message.get;
3045 RAISE fnd_api.g_exc_error;
3046
3047 ELSIF l_src_instance_header_tbl.count > 1 THEN
3048 -- Multiple Instances were found so throw error
3049 IF (l_debug > 0) THEN
3050 csi_t_gen_utility_pvt.add('Multiple Instances were Found in InstallBase-65');
3051 END IF;
3052 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
3053 fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
3054 fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
3055 fnd_message.set_token('INV_ORG_ID',l_mtl_item_tbl(j).organization_id);
3056 fnd_message.set_token('LOCATOR',l_mtl_item_tbl(j).locator_id);
3057 l_error_message := fnd_message.get;
3058 RAISE fnd_api.g_exc_error;
3059
3060 END IF; -- End of Source Record IF for Serialized
3061 END IF; -- End of Source Record If
3062 END LOOP; -- End of For Loop
3063
3064 IF (l_debug > 0) THEN
3065 csi_t_gen_utility_pvt.add('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
3066 csi_t_gen_utility_pvt.add('*****End of csi_inv_hz_pkg.misc_issue_hz_loc Transaction procedure*****');
3067 END IF;
3068
3069 EXCEPTION
3070 WHEN fnd_api.g_exc_error THEN
3071 IF (l_debug > 0) THEN
3072 csi_t_gen_utility_pvt.add('You have encountered a "fnd_api.g_exc_error" exception');
3073 END IF;
3074 x_return_status := l_fnd_error;
3075
3076 IF l_mtl_item_tbl.count > 0 THEN
3077 x_trx_error_rec.serial_number := l_mtl_item_tbl(j).serial_number;
3078 x_trx_error_rec.lot_number := l_mtl_item_tbl(j).lot_number;
3079 x_trx_error_rec.instance_id := l_upd_error_instance_id;
3080 x_trx_error_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
3081 x_trx_error_rec.src_serial_num_ctrl_code := l_mtl_item_tbl(j).serial_number_control_code;
3082 x_trx_error_rec.src_location_ctrl_code := l_mtl_item_tbl(j).location_control_code;
3083 x_trx_error_rec.src_lot_ctrl_code := l_mtl_item_tbl(j).lot_control_code;
3084 x_trx_error_rec.src_rev_qty_ctrl_code := l_mtl_item_tbl(j).revision_qty_control_code;
3085 x_trx_error_rec.comms_nl_trackable_flag := l_mtl_item_tbl(j).comms_nl_trackable_flag;
3086 x_trx_error_rec.transaction_error_date := l_sysdate ;
3087 END IF;
3088
3089 x_trx_error_rec.error_text := l_error_message;
3090 x_trx_error_rec.transaction_id := NULL;
3091 x_trx_error_rec.source_type := 'CSIMSIHZ';
3092 x_trx_error_rec.source_id := p_transaction_id;
3093 x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
3094 x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('MISC_ISSUE_HZ_LOC','INV');
3095 x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
3096 x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
3097
3098 WHEN others THEN
3099 IF (l_debug > 0) THEN
3100 csi_t_gen_utility_pvt.add('You have encountered a "others" exception');
3101 END IF;
3102 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
3103 fnd_message.set_token('API_NAME',l_api_name);
3104 fnd_message.set_token('SQL_ERROR',SQLERRM);
3105 x_return_status := l_fnd_unexpected;
3106
3107 IF l_mtl_item_tbl.count > 0 THEN
3108 x_trx_error_rec.serial_number := l_mtl_item_tbl(j).serial_number;
3109 x_trx_error_rec.lot_number := l_mtl_item_tbl(j).lot_number;
3110 x_trx_error_rec.instance_id := l_upd_error_instance_id;
3111 x_trx_error_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
3112 x_trx_error_rec.src_serial_num_ctrl_code := l_mtl_item_tbl(j).serial_number_control_code;
3113 x_trx_error_rec.src_location_ctrl_code := l_mtl_item_tbl(j).location_control_code;
3114 x_trx_error_rec.src_lot_ctrl_code := l_mtl_item_tbl(j).lot_control_code;
3115 x_trx_error_rec.src_rev_qty_ctrl_code := l_mtl_item_tbl(j).revision_qty_control_code;
3116 x_trx_error_rec.comms_nl_trackable_flag := l_mtl_item_tbl(j).comms_nl_trackable_flag;
3117 x_trx_error_rec.transaction_error_date := l_sysdate ;
3118 END IF;
3119
3120 x_trx_error_rec.error_text := fnd_message.get;
3121 x_trx_error_rec.transaction_id := NULL;
3122 x_trx_error_rec.source_type := 'CSIMSIHZ';
3123 x_trx_error_rec.source_id := p_transaction_id;
3124 x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
3125 x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('MISC_ISSUE_HZ_LOC','INV');
3126 x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
3127 x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
3128
3129 END misc_issue_hz_loc;
3130
3131 END CSI_INV_HZ_PKG;