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