[Home] [Help]
PACKAGE BODY: APPS.CSI_INV_TRANSFER_PKG
Source
1 package body CSI_INV_TRANSFER_PKG as
2 -- $Header: csiivttb.pls 120.4 2006/06/06 19:26:34 jpwilson noship $
3
4 PROCEDURE debug(p_message IN varchar2) IS
5
6 BEGIN
7 csi_t_gen_utility_pvt.add(p_message);
8 EXCEPTION
9 WHEN others THEN
10 null;
11 END debug;
12
13 PROCEDURE subinv_transfer(p_transaction_id IN NUMBER,
14 p_message_id IN NUMBER,
15 x_return_status OUT NOCOPY VARCHAR2,
16 x_trx_error_rec OUT NOCOPY CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC)
17 IS
18
19 l_mtl_item_tbl CSI_INV_TRXS_PKG.MTL_ITEM_TBL_TYPE;
20 l_api_name VARCHAR2(100) := 'CSI_INV_TRANSFER_PKG.SUBINV_TRANSFER';
21 l_api_version NUMBER := 1.0;
22 l_commit VARCHAR2(1) := FND_API.G_FALSE;
23 l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
24 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
25 l_active_instance_only VARCHAR2(10) := FND_API.G_TRUE;
26 l_inactive_instance_only VARCHAR2(10) := FND_API.G_FALSE;
27 l_resolve_id_columns VARCHAR2(10) := FND_API.G_FALSE;
28 l_transaction_id NUMBER := NULL;
29 l_object_version_number NUMBER := 1;
30 l_sysdate DATE := SYSDATE;
31 l_master_organization_id NUMBER;
32 l_depreciable VARCHAR2(1);
33 l_instance_id_lst CSI_DATASTRUCTURES_PUB.ID_TBL;
34 l_instance_query_rec CSI_DATASTRUCTURES_PUB.INSTANCE_QUERY_REC;
35 l_update_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
36 l_update_src_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
37 l_update_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
38 l_upd_src_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
39 l_dest_instance_query_rec CSI_DATASTRUCTURES_PUB.INSTANCE_QUERY_REC;
40 l_new_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
41 l_new_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
42 l_new_src_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
43 l_api_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
44 l_api_src_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
45 l_txn_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_REC;
46 l_return_status VARCHAR2(1);
47 l_error_code VARCHAR2(50);
48 l_error_message VARCHAR2(4000);
49 l_party_query_rec CSI_DATASTRUCTURES_PUB.PARTY_QUERY_REC;
50 l_account_query_rec CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_QUERY_REC;
51 l_src_instance_header_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_HEADER_TBL;
52 l_dest_instance_header_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_HEADER_TBL;
53 l_ext_attrib_values_tbl CSI_DATASTRUCTURES_PUB.EXTEND_ATTRIB_VALUES_TBL;
54 l_party_tbl CSI_DATASTRUCTURES_PUB.PARTY_TBL;
55 l_account_tbl CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_TBL;
56 l_pricing_attrib_tbl CSI_DATASTRUCTURES_PUB.PRICING_ATTRIBS_TBL;
57 l_org_assignments_tbl CSI_DATASTRUCTURES_PUB.ORGANIZATION_UNITS_TBL;
58 l_asset_assignment_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_ASSET_TBL;
59 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
60 l_fnd_warning VARCHAR2(1) := 'W';
61 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
62 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
63 l_in_inventory VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_INVENTORY;
64 l_in_process VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_PROCESS;
65 l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
66 l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
67 l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
68 l_installed VARCHAR2(25) := CSI_INV_TRXS_PKG.G_INSTALLED;
69 l_transaction_error_id NUMBER;
70 l_quantity NUMBER;
71 l_mfg_serial_number_flag VARCHAR2(1);
72 l_trans_status_code VARCHAR2(15);
73 l_ins_number VARCHAR2(100);
74 l_employee_id NUMBER;
75 l_ins_id NUMBER;
76 l_file VARCHAR2(500);
77 l_msg_count NUMBER;
78 l_msg_data VARCHAR2(2000);
79 l_sql_error VARCHAR2(2000);
80 l_msg_index NUMBER;
81 j PLS_INTEGER := 1;
82 i PLS_INTEGER := 1;
83 l_tbl_count NUMBER := 0;
84 l_neg_code NUMBER := 0;
85 l_instance_status VARCHAR2(1);
86 l_trans_type_code VARCHAR2(25);
87 l_trans_app_code VARCHAR2(5);
88 l_redeploy_flag VARCHAR2(1);
89 l_upd_error_instance_id NUMBER := NULL;
90
91 cursor c_id is
92 SELECT instance_status_id
93 FROM csi_instance_statuses
94 WHERE name = FND_PROFILE.VALUE('CSI_DEFAULT_INSTANCE_STATUS');
95
96 r_id c_id%rowtype;
97
98 -- Get the Transaction ID for the (-) quantity transaction and pass that
99 -- instead of the (+) transaction ID. This is done so that the hook will be
100 -- called after the second transaction is processed with the (+) qty and
101 -- will prevent any timing issues with the transaction manager
102
103 CURSOR c_mtl is
104 SELECT transfer_transaction_id
105 FROM mtl_material_transactions
106 WHERE transaction_id = p_transaction_id;
107
108 r_mtl c_mtl%rowtype;
109
110 CURSOR c_so_info (pc_line_id in NUMBER) is
111 SELECT oeh.header_id,
112 oel.line_id,
113 oeh.order_number,
114 oel.line_number
115 FROM oe_order_headers_all oeh,
116 oe_order_lines_all oel
117 WHERE oeh.header_id = oel.header_id
118 AND oel.line_id = pc_line_id;
119
120 r_so_info c_so_info%rowtype;
121
122 CURSOR c_loc_ids (pc_org_id IN NUMBER,
123 pc_subinv_name IN VARCHAR2) is
124 SELECT haou.location_id hr_location_id,
125 msi.location_id subinv_location_id
126 FROM hr_all_organization_units haou,
127 mtl_secondary_inventories msi
128 WHERE haou.organization_id = pc_org_id
129 AND msi.organization_id = pc_org_id
130 AND msi.secondary_inventory_name = pc_subinv_name;
131
132 r_loc_ids c_loc_ids%rowtype;
133
134 BEGIN
135
136 x_return_status := l_fnd_success;
137 x_trx_error_rec.error_text := NULL;
138
139 debug('*****Start of csi_inv_transfer_pkg.subinv_transfer Transaction *****');
140 debug('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
141 debug('csiivttb.pls 115.15');
142 debug('Transaction ID with is: '||p_transaction_id);
143
144 -- This procedure queries all of the Inventory Transaction Records
145 -- and returns them as a table.
146
147 -- This will open the cursor and fetch the (-) transaction ID
148 OPEN c_mtl;
149 FETCH c_mtl into r_mtl;
150 CLOSE c_mtl;
151
152 debug('Transaction ID with (+) is: '||p_transaction_id);
153 debug('Transaction ID with (-) is: '||r_mtl.transfer_transaction_id);
154
155 csi_inv_trxs_pkg.get_transaction_recs(r_mtl.transfer_transaction_id,
156 l_mtl_item_tbl,
157 l_return_status,
158 l_error_message);
159
160 l_tbl_count := 0;
161 l_tbl_count := l_mtl_item_tbl.count;
162
163 debug('Inventory Records Found: '||l_tbl_count);
164
165 IF NOT l_return_status = l_fnd_success THEN
166 debug('You have encountered an error in CSI_INV_TRXS_PKG.get_transaction_recs, Transaction ID: '||r_mtl.transfer_transaction_id);
167 RAISE fnd_api.g_exc_error;
168 END IF;
169
170 -- Get the Master Organization ID
171 csi_inv_trxs_pkg.get_master_organization(l_mtl_item_tbl(i).organization_id,
172 l_master_organization_id,
173 l_return_status,
174 l_error_message);
175
176 debug('Master Org is: '||l_master_organization_id);
177
178 IF NOT l_return_status = l_fnd_success THEN
179 debug('You have encountered an error in csi_inv_trxs_pkg.get_master_organization, Organization ID: '||l_mtl_item_tbl(i).organization_id);
180 RAISE fnd_api.g_exc_error;
181 END IF;
182
183 -- Call get_fnd_employee_id and get the employee id
184 l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
185
186 IF l_employee_id = -1 THEN
187 debug('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
188 END IF;
189
190 debug('The Employee that is processing this Transaction is: '||l_employee_id);
191
192 -- See if this is a depreciable Item to set the status of the transaction record
193 csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
194 l_depreciable);
195
196 debug('Is this Item ID: '||l_mtl_item_tbl(i).inventory_item_id||', Depreciable :'||l_depreciable);
197
198 -- Set the quantity
199 IF l_mtl_item_tbl(i).serial_number IS NULL THEN
200 l_quantity := l_mtl_item_tbl(i).transaction_quantity;
201 ELSE
202 l_quantity := 1;
203 END IF;
204
205 -- Determine the Transaction Type
206 IF l_mtl_item_tbl(i).transaction_type_id = 2 THEN
207 l_trans_type_code := 'SUBINVENTORY_TRANSFER';
208 l_trans_app_code := 'INV';
209 ELSIF l_mtl_item_tbl(i).transaction_type_id = 5 THEN
210 l_trans_type_code := 'CYCLE_COUNT_TRANSFER' ;
211 l_trans_app_code := 'INV';
212 ELSIF l_mtl_item_tbl(i).transaction_type_id = 9 THEN
213 l_trans_type_code := 'PHYSICAL_INV_TRANSFER' ;
214 l_trans_app_code := 'INV';
215 ELSIF l_mtl_item_tbl(i).transaction_type_id = 50 THEN
216 l_trans_type_code := 'ISO_TRANSFER' ;
217 l_trans_app_code := 'INV';
218 ELSIF l_mtl_item_tbl(i).transaction_type_id = 51 THEN
219 l_trans_type_code := 'BACKFLUSH_TRANSFER' ;
220 l_trans_app_code := 'INV';
221 ELSIF l_mtl_item_tbl(i).transaction_type_id = 53 THEN
222 l_trans_type_code := 'ISO_PICK' ;
223 l_trans_app_code := 'INV';
224 ELSIF l_mtl_item_tbl(i).transaction_type_id = 52 THEN
225 l_trans_type_code := 'SALES_ORDER_PICK' ;
226 l_trans_app_code := 'INV';
227 ELSIF l_mtl_item_tbl(i).transaction_type_id = 64 THEN
228 l_trans_type_code := 'MOVE_ORDER_TRANSFER' ;
229 l_trans_app_code := 'INV';
230 ELSIF l_mtl_item_tbl(i).transaction_type_id = 66 THEN
231 l_trans_type_code := 'PROJECT_BORROW' ;
232 l_trans_app_code := 'INV';
233 ELSIF l_mtl_item_tbl(i).transaction_type_id = 67 THEN
234 l_trans_type_code := 'PROJECT_TRANSFER' ;
235 l_trans_app_code := 'INV';
236 ELSIF l_mtl_item_tbl(i).transaction_type_id = 68 THEN
237 l_trans_type_code := 'PROJECT_PAYBACK' ;
238 l_trans_app_code := 'INV';
239 ELSE
240 l_trans_type_code := 'SUBINVENTORY_TRANSFER';
241 l_trans_app_code := 'INV';
242 END IF;
243
244 debug('Trans Type Code: '||l_trans_type_code);
245 debug('Trans App Code: '||l_trans_app_code);
246
247 -- Get the Negative Receipt Code to see if this org allows Negative
248 -- Quantity Records 1 = Yes, 2 = No
249
250 l_neg_code := csi_inv_trxs_pkg.get_neg_inv_code(
251 l_mtl_item_tbl(i).organization_id);
252
253 IF l_neg_code = 1 AND l_mtl_item_tbl(i).serial_number is NULL THEN
254 l_instance_status := FND_API.G_FALSE;
255
256 ELSE
257 l_instance_status := FND_API.G_TRUE;
258 END IF;
259
260
261 debug('Negative Code is - 1 = Yes, 2 = No: '||l_neg_code);
262
263 -- Added so that the SO_HEADER_ID and SO_LINE_ID can be added to
264 -- the transaction record.
265
266 OPEN c_so_info (l_mtl_item_tbl(i).trx_source_line_id);
267 FETCH c_so_info into r_so_info;
268 CLOSE c_so_info;
269
270 debug('Sales Order Header: '||r_so_info.header_id);
271 debug('Sales Order Line: '||r_so_info.line_id);
272 debug('Order Number: '||r_so_info.order_number);
273 debug('Line Number: '||r_so_info.line_number);
274
275 -- Initialize Transaction Record
276 l_txn_rec := csi_inv_trxs_pkg.init_txn_rec;
277
278 -- Set Status based on redeployment
279 IF l_depreciable = 'N' THEN
280 IF l_mtl_item_tbl(i).serial_number is NOT NULL THEN
281 csi_inv_trxs_pkg.get_redeploy_flag(l_mtl_item_tbl(i).inventory_item_id,
282 l_mtl_item_tbl(i).serial_number,
283 l_sysdate,
284 l_redeploy_flag,
285 l_return_status,
286 l_error_message);
287 END IF;
288 IF l_redeploy_flag = 'Y' THEN
289 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
290 ELSE
291 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_complete;
292 END IF;
293 ELSE
294 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
295 END IF;
296
297 IF NOT l_return_status = l_fnd_success THEN
298 debug('Redeploy Flag: '||l_redeploy_flag);
299 debug('You have encountered an error in csi_inv_trxs_pkg.get_redeploy_flag: '||l_error_message);
300 RAISE fnd_api.g_exc_error;
301 END IF;
302
303 debug('Redeploy Flag: '||l_redeploy_flag);
304 debug('Trans Status Code: '||l_txn_rec.transaction_status_code);
305
306 -- Get Default Status ID
307 OPEN c_id;
308 FETCH c_id into r_id;
309 CLOSE c_id;
310
311 -- Create CSI Transaction to be used
312 l_txn_rec.source_transaction_date := l_mtl_item_tbl(i).transaction_date;
313 l_txn_rec.transaction_date := l_sysdate;
314 l_txn_rec.transaction_type_id :=
315 csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
316 l_txn_rec.transaction_quantity :=
317 l_mtl_item_tbl(i).transaction_quantity;
318 l_txn_rec.transaction_uom_code := l_mtl_item_tbl(i).transaction_uom;
319 l_txn_rec.transacted_by := l_employee_id;
320 l_txn_rec.transaction_action_code := NULL;
321 l_txn_rec.message_id := p_message_id;
322 l_txn_rec.inv_material_transaction_id := p_transaction_id;
323 l_txn_rec.object_version_number := l_object_version_number;
324
325 IF l_mtl_item_tbl(i).transaction_type_id in (50,52,53) THEN
326 l_txn_rec.source_header_ref_id := r_so_info.header_id;
327 l_txn_rec.source_line_ref_id := r_so_info.line_id;
328 l_txn_rec.source_header_ref := to_char(r_so_info.order_number);
329 l_txn_rec.source_line_ref := to_char(r_so_info.line_number);
330 END IF;
331
332 -- Move Order Transfer Info on Txn Record
333 IF l_mtl_item_tbl(i).transaction_type_id = 64 THEN
334 l_txn_rec.source_header_ref_id := l_mtl_item_tbl(i).transaction_source_id;
335 l_txn_rec.source_line_ref_id := l_mtl_item_tbl(i).move_order_line_id;
336 END IF;
337
338 csi_inv_trxs_pkg.create_csi_txn(l_txn_rec,
339 l_error_message,
340 l_return_status);
341
342 debug('CSI Transaction Created: '||l_txn_rec.transaction_id);
343
344 IF NOT l_return_status = l_fnd_success THEN
345 debug('You have encountered an error in csi_inv_trxs_pkg.create_csi_txn: '||p_transaction_id);
346 RAISE fnd_api.g_exc_error;
347 END IF;
348 -- Now loop through the PL/SQL Table.
349 j := 1;
350
351 FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
352
353 debug('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
354 debug('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
355 debug('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
356 debug('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
357 debug('Serial Number : '||l_mtl_item_tbl(j).serial_number);
358 debug('Serial Number Control Code: '||l_mtl_item_tbl(j).serial_number_control_code);
359 debug('Organization ID: '||l_mtl_item_tbl(j).organization_id);
360 debug('SO_HEADER_ID is: '||r_so_info.header_id);
361 debug('SO_LINE_ID is: '||r_so_info.line_id);
362
363 -- Get the Location Ids for Receiving Org
364 OPEN c_loc_ids (l_mtl_item_tbl(j).transfer_organization_id,
365 l_mtl_item_tbl(j).transfer_subinventory);
366 FETCH c_loc_ids into r_loc_ids;
367 CLOSE c_loc_ids;
368
369 debug('Transfer Subinv Location: '||r_loc_ids.subinv_location_id);
370 debug('Transfer HR Location : '||r_loc_ids.hr_location_id);
371
372 IF l_mtl_item_tbl(j).transaction_type_id <> 50 THEN
373 debug('This is not an ISO Transfer so process as normal - Source');
374 csi_inv_trxs_pkg.set_item_attr_query_values(l_mtl_item_tbl,
375 j,
376 NULL,
377 l_instance_query_rec,
378 x_return_status);
379
380
381 IF l_mtl_item_tbl(j).serial_number IS NULL THEN -- Non Serial
382
383 l_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
384 l_instance_query_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
385 l_instance_query_rec.instance_usage_code := l_in_inventory;
386
387 END IF;
388
389 ELSE
390 debug('This is an ISO Transfer - Source');
391 IF l_mtl_item_tbl(j).serial_number_control_code IN (1,6) THEN
392
393 debug('This is an ISO Transfer - Serial Control 1 or 6');
394
395 l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
396 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
397 l_instance_query_rec.serial_number := NULL;
398 l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
399 l_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
400 l_instance_query_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
401 l_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
402 l_instance_query_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
403 l_instance_query_rec.instance_usage_code := l_in_inventory;
404 ELSE
405 debug('This is an ISO Transfer - Serial Control 2 or 5');
406 l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
407 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
408 l_instance_query_rec.serial_number := l_mtl_item_tbl(j).serial_number;
409 END IF;
410
411 END IF;
412
413 csi_t_gen_utility_pvt.dump_instance_query_rec(p_instance_query_rec => l_instance_query_rec);
414
415 debug('Before Get Item Instance');
416
417 csi_item_instance_pub.get_item_instances(l_api_version,
418 l_commit,
419 l_init_msg_list,
420 l_validation_level,
421 l_instance_query_rec,
422 l_party_query_rec,
423 l_account_query_rec,
424 l_transaction_id,
425 l_resolve_id_columns,
426 l_instance_status,
427 l_src_instance_header_tbl,
428 l_return_status,
429 l_msg_count,
430 l_msg_data);
431
432 debug('After Get Item Instance');
433
434 l_tbl_count := 0;
435 l_tbl_count := l_src_instance_header_tbl.count;
436
437 debug('Source Records Found: '||l_tbl_count);
438
439 -- Check for any errors and add them to the message stack to pass out to be put into the
440 -- error log table.
441 IF NOT l_return_status = l_fnd_success then
442 debug('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
443 l_msg_index := 1;
444 WHILE l_msg_count > 0 loop
445 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
446 l_msg_index := l_msg_index + 1;
447 l_msg_count := l_msg_count - 1;
448 END LOOP;
449 RAISE fnd_api.g_exc_error;
450 END IF;
451
452 --IF l_mtl_item_tbl(j).serial_number is NULL THEN
453 IF l_mtl_item_tbl(j).serial_number_control_code in (1,6) THEN
454 IF l_src_instance_header_tbl.count = 0 THEN
455 IF l_neg_code = 1 THEN -- Allow Neg Qtys on NON Serial Items ONLY
456
457 debug('No records were found so create a new Source Non Serialized Instance Record');
458
459 l_new_src_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
460 l_new_src_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
461 l_new_src_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
462 l_new_src_instance_rec.inv_master_organization_id := l_master_organization_id;
463 l_new_src_instance_rec.mfg_serial_number_flag := 'N';
464 l_new_src_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
465 l_new_src_instance_rec.quantity := l_mtl_item_tbl(j).transaction_quantity;
466 l_new_src_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
467 l_new_src_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
468 l_new_src_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
469 l_new_src_instance_rec.instance_usage_code := l_in_inventory;
470 l_new_src_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
471 l_new_src_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
472 l_new_src_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
473 l_new_src_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
474 l_new_src_instance_rec.customer_view_flag := 'N';
475 l_new_src_instance_rec.merchant_view_flag := 'Y';
476 l_new_src_instance_rec.object_version_number := l_object_version_number;
477 l_new_src_instance_rec.operational_status_code := 'NOT_USED';
478 l_new_src_instance_rec.active_start_date := l_sysdate;
479 l_new_src_instance_rec.active_end_date := NULL;
480 --l_new_src_instance_rec.last_oe_order_line_id := r_so_info.line_id;
481
482 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
483 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
484 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
485 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
486 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
487 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
488
489 debug('Before Create of source Instance');
490
491 csi_item_instance_pub.create_item_instance(l_api_version,
492 l_commit,
493 l_init_msg_list,
494 l_validation_level,
495 l_new_src_instance_rec,
496 l_ext_attrib_values_tbl,
497 l_party_tbl,
498 l_account_tbl,
499 l_pricing_attrib_tbl,
500 l_org_assignments_tbl,
501 l_asset_assignment_tbl,
502 l_txn_rec,
503 l_return_status,
504 l_msg_count,
505 l_msg_data);
506
507 debug('After Create of Source Item Instance');
508 debug('New instance created is: '||l_new_src_instance_rec.instance_id);
509
510 -- Check for any errors and add them to the message stack to pass out to be put into the
511 -- error log table.
512 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
513 debug('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
514 l_msg_index := 1;
515 WHILE l_msg_count > 0 loop
516 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
517 l_msg_index := l_msg_index + 1;
518 l_msg_count := l_msg_count - 1;
519 END LOOP;
520 RAISE fnd_api.g_exc_error;
521 END IF;
522
523 ELSE -- No Records were found and Neg Qtys Not Allowed
524 debug('No Records were found in Install Base and Neg Qtys not allowed to error');
525 fnd_message.set_name('CSI','CSI_NO_NEG_BAL_ALLOWED');
526 l_error_message := fnd_message.get;
527 RAISE fnd_api.g_exc_error;
528
529 END IF; -- Neg Qty If
530
531 ELSIF l_src_instance_header_tbl.count = 1 THEN
532 -- Records found so make sure that is is updated to be unexp
533 -- and subtract the quantity from source record
534
535 debug('You will update instance: '||l_src_instance_header_tbl(i).instance_id);
536 debug('End Date is: '||l_src_instance_header_tbl(i).active_end_date);
537
538 l_update_src_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
539 l_update_src_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
540 l_update_src_instance_rec.quantity := l_src_instance_header_tbl(i).quantity - abs(l_mtl_item_tbl(j).primary_quantity);
541 l_update_src_instance_rec.active_end_date := NULL;
542 --l_update_src_instance_rec.last_oe_order_line_id := r_so_info.line_id;
543 l_update_src_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
544
545 l_party_tbl.delete;
546 l_account_tbl.delete;
547 l_pricing_attrib_tbl.delete;
548 l_org_assignments_tbl.delete;
549 l_asset_assignment_tbl.delete;
550
551 debug('Before Update Source Item Instance - Neg Qty');
552
553 l_update_src_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);
554
555 debug('Instance Status Id: '||l_update_src_instance_rec.instance_status_id);
556
557 csi_item_instance_pub.update_item_instance(l_api_version,
558 l_commit,
559 l_init_msg_list,
560 l_validation_level,
561 l_update_src_instance_rec,
562 l_ext_attrib_values_tbl,
563 l_party_tbl,
564 l_account_tbl,
565 l_pricing_attrib_tbl,
566 l_org_assignments_tbl,
567 l_asset_assignment_tbl,
568 l_txn_rec,
569 l_instance_id_lst,
570 l_return_status,
571 l_msg_count,
572 l_msg_data);
573
574 l_upd_error_instance_id := NULL;
575 l_upd_error_instance_id := l_update_src_instance_rec.instance_id;
576
577 debug('After Update Source Item Instance - Neg Qty');
578 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
579
580 -- Check for any errors and add them to the message stack to pass out to be put into the
581 -- error log table.
582 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
583 debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
584 l_msg_index := 1;
585 WHILE l_msg_count > 0 loop
586 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
587 l_msg_index := l_msg_index + 1;
588 l_msg_count := l_msg_count - 1;
589 END LOOP;
590 RAISE fnd_api.g_exc_error;
591 END IF;
592
593 ELSIF l_src_instance_header_tbl.count > 1 THEN
594 -- Multiple Instances were found so throw error
595 debug('Multiple Instances were Found in Install Base-30');
596 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
597 fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
598 fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
599 fnd_message.set_token('INV_ORG_ID',l_mtl_item_tbl(j).organization_id);
600 fnd_message.set_token('LOCATOR',l_mtl_item_tbl(j).locator_id);
601 l_error_message := fnd_message.get;
602 RAISE fnd_api.g_exc_error;
603
604 END IF; -- End of Source Record If
605
606 -- Now query for the destination records
607 IF l_mtl_item_tbl(j).transaction_type_id <> 50 THEN
608 debug('This is not an ISO Transfer so process as normal - Dest');
609 csi_inv_trxs_pkg.set_item_attr_query_values(l_mtl_item_tbl,
610 j,
611 'TRANSFER',
612 l_dest_instance_query_rec,
613 x_return_status);
614
615
616 IF l_mtl_item_tbl(j).serial_number IS NULL THEN -- Non Serial
617
618 l_dest_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
619 l_dest_instance_query_rec.inv_subinventory_name := l_mtl_item_tbl(j).transfer_subinventory;
620 l_dest_instance_query_rec.instance_usage_code := l_in_inventory;
621
622 END IF;
623
624 l_mfg_serial_number_flag := 'N';
625 l_quantity := abs(l_mtl_item_tbl(j).transaction_quantity);
626
627 ELSE
628 debug('This is an ISO Transfer - Dest');
629 IF l_mtl_item_tbl(j).serial_number_control_code in (1,6) THEN
630
631 debug('This is an ISO Transfer - Dest - Serial Control is: '||l_mtl_item_tbl(j).serial_number_control_code);
632
633 l_dest_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
634 l_dest_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
635 l_dest_instance_query_rec.serial_number := NULL;
636 l_dest_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
637 l_dest_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
638 l_dest_instance_query_rec.inv_locator_id := l_mtl_item_tbl(j).transfer_locator_id;
639 l_dest_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
640 l_dest_instance_query_rec.inv_subinventory_name := l_mtl_item_tbl(j).transfer_subinventory;
641 l_dest_instance_query_rec.instance_usage_code := l_in_inventory;
642
643 l_mfg_serial_number_flag := 'N';
644 l_quantity := abs(l_mtl_item_tbl(j).transaction_quantity);
645
646 --ELSE
647 -- debug('This is an ISO Transfer - Dest - Serial Control 6');
648 -- l_dest_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
649 -- l_dest_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
650 -- l_dest_instance_query_rec.serial_number := l_mtl_item_tbl(j).serial_number;
651
652 -- l_mfg_serial_number_flag := 'Y';
653 -- l_quantity := 1;
654 END IF;
655 END IF;
656
657 csi_t_gen_utility_pvt.dump_instance_query_rec(p_instance_query_rec => l_dest_instance_query_rec);
658
659 debug('Before Dest Get Item Instance - 31');
660
661 csi_item_instance_pub.get_item_instances(l_api_version,
662 l_commit,
663 l_init_msg_list,
664 l_validation_level,
665 l_dest_instance_query_rec,
666 l_party_query_rec,
667 l_account_query_rec,
668 l_transaction_id,
669 l_resolve_id_columns,
670 l_inactive_instance_only,
671 l_dest_instance_header_tbl,
672 l_return_status,
673 l_msg_count,
674 l_msg_data);
675
676 debug('After Get Item Instance for destination records');
677
678 l_tbl_count := 0;
679 l_tbl_count := l_dest_instance_header_tbl.count;
680
681 debug('Destination Records Found: '||l_tbl_count);
682
683 -- Check for any errors and add them to the message stack to pass out to be put into the
684 -- error log table.
685 IF NOT l_return_status = l_fnd_success then
686 debug('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
687 l_msg_index := 1;
688 WHILE l_msg_count > 0 loop
689 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
690 l_msg_index := l_msg_index + 1;
691 l_msg_count := l_msg_count - 1;
692 END LOOP;
693 RAISE fnd_api.g_exc_error;
694 END IF;
695
696 IF l_dest_instance_header_tbl.count = 0 THEN -- Installed Base Destination Records are not found
697
698 debug('No Destination Records were found so create a new one - Neg Qty If Statement');
699
700 l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
701 l_new_dest_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
702 l_new_dest_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
703 l_new_dest_instance_rec.inv_master_organization_id := l_master_organization_id;
704 l_new_dest_instance_rec.mfg_serial_number_flag := l_mfg_serial_number_flag;
705 l_new_dest_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
706 l_new_dest_instance_rec.quantity := l_quantity;
707 l_new_dest_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
708 l_new_dest_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
709 --l_new_dest_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
710 l_new_dest_instance_rec.location_id := nvl(r_loc_ids.subinv_location_id,r_loc_ids.hr_location_id);
711 l_new_dest_instance_rec.instance_usage_code := l_in_inventory;
712 l_new_dest_instance_rec.inv_organization_id := l_mtl_item_tbl(j).transfer_organization_id;
713 l_new_dest_instance_rec.vld_organization_id := l_mtl_item_tbl(j).transfer_organization_id;
714 l_new_dest_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).transfer_subinventory;
715 l_new_dest_instance_rec.inv_locator_id := l_mtl_item_tbl(j).transfer_locator_id;
716 l_new_dest_instance_rec.customer_view_flag := 'N';
717 l_new_dest_instance_rec.merchant_view_flag := 'Y';
718 l_new_dest_instance_rec.object_version_number := l_object_version_number;
719 l_new_dest_instance_rec.operational_status_code := 'NOT_USED';
720 l_new_dest_instance_rec.active_start_date := l_sysdate;
721 l_new_dest_instance_rec.active_end_date := NULL;
722 --l_new_dest_instance_rec.last_oe_order_line_id := r_so_info.line_id;
723
724 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
725 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
726 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
727 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
728 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
729 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
730
731 debug('Before Create of Non Serialized Destination Item Instance');
732 debug('Location ID value: '||l_new_instance_rec.location_id);
733 debug('Subinv Location: '||l_mtl_item_tbl(j).subinv_location_id);
734 debug('HR Location: '||l_mtl_item_tbl(j).hr_location_id);
735 debug('Serial Number: '||l_mtl_item_tbl(j).serial_number);
736 debug('Mfg Flag: '||l_mfg_serial_number_flag);
737
738 csi_item_instance_pub.create_item_instance(l_api_version,
739 l_commit,
740 l_init_msg_list,
741 l_validation_level,
742 l_new_dest_instance_rec,
743 l_ext_attrib_values_tbl,
744 l_party_tbl,
745 l_account_tbl,
746 l_pricing_attrib_tbl,
747 l_org_assignments_tbl,
748 l_asset_assignment_tbl,
749 l_txn_rec,
750 l_return_status,
751 l_msg_count,
752 l_msg_data);
753
754 debug('After Create of Non Serialized Destination Item Instance');
755
756 -- Check for any errors and add them to the message stack to pass out to be put into the
757 -- error log table.
758 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
759 debug('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
760 l_msg_index := 1;
761 WHILE l_msg_count > 0 loop
762 l_error_message := fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
763 l_msg_index := l_msg_index + 1;
764 l_msg_count := l_msg_count - 1;
765 END LOOP;
766 RAISE fnd_api.g_exc_error;
767 END IF;
768
769 ELSIF l_dest_instance_header_tbl.count = 1 THEN
770
771 IF l_mtl_item_tbl(j).transaction_type_id <> 50 THEN
772
773 -- Installed Base Destination Records Found
774
775 debug('You will update instance: '||l_dest_instance_header_tbl(i).instance_id);
776
777 l_update_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
778 l_update_dest_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
779 l_update_dest_instance_rec.quantity := l_dest_instance_header_tbl(i).quantity + abs(l_mtl_item_tbl(j).primary_quantity);
780 l_update_dest_instance_rec.active_end_date := NULL;
781 --l_update_dest_instance_rec.last_oe_order_line_id := r_so_info.line_id;
782 l_update_dest_instance_rec.object_version_number := l_dest_instance_header_tbl(i).object_version_number;
783
784 ELSE -- ISO Transfer Transaction
785
786 --IF l_mtl_item_tbl(j).serial_number_control_code = 6 THEN
787 -- debug('Serialized Source records were foundo - ISO Transfer');
788 -- debug('Update the serialized item with Serial Number - ISO Transfer: '||l_src_instance_header_tbl(i).serial_number);
789
790 -- l_update_src_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
791 -- l_update_src_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
792 -- l_update_src_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).transfer_subinventory;
793 -- l_update_src_instance_rec.inv_locator_id := l_mtl_item_tbl(j).transfer_locator_id;
794 -- l_update_src_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
795 -- l_update_src_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
796
797 IF l_mtl_item_tbl(j).serial_number_control_code in (1,6) THEN
798
799 debug('You will update instance - ISO Transfer: '||l_dest_instance_header_tbl(i).instance_id);
800 debug('This is an ISO Transfer - Dest - Serial Control is: '||l_mtl_item_tbl(j).serial_number_control_code);
801
802 l_update_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
803 l_update_dest_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
804 l_update_dest_instance_rec.quantity := l_dest_instance_header_tbl(i).quantity + abs(l_mtl_item_tbl(j).primary_quantity);
805 l_update_dest_instance_rec.active_end_date := NULL;
806 --l_update_dest_instance_rec.last_oe_order_line_id := r_so_info.line_id;
807 l_update_dest_instance_rec.object_version_number := l_dest_instance_header_tbl(i).object_version_number;
808 END IF;
809
810 END IF; -- Check of Transaction Type
811
812 l_party_tbl.delete;
813 l_account_tbl.delete;
814 l_pricing_attrib_tbl.delete;
815 l_org_assignments_tbl.delete;
816 l_asset_assignment_tbl.delete;
817
818 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);
819
820 debug('Before Update Item Instance - 34');
821 debug('Instance Status Id: '||l_update_dest_instance_rec.instance_status_id);
822
823 csi_item_instance_pub.update_item_instance(l_api_version,
824 l_commit,
825 l_init_msg_list,
826 l_validation_level,
827 l_update_dest_instance_rec,
828 l_ext_attrib_values_tbl,
829 l_party_tbl,
830 l_account_tbl,
831 l_pricing_attrib_tbl,
832 l_org_assignments_tbl,
833 l_asset_assignment_tbl,
834 l_txn_rec,
835 l_instance_id_lst,
836 l_return_status,
837 l_msg_count,
838 l_msg_data);
839
840 l_upd_error_instance_id := NULL;
841 l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
842
843 debug('After Update Item Instance - Neg Qty');
844 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
845
846 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
847 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
848 debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
849 l_msg_index := 1;
850 WHILE l_msg_count > 0 loop
851 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
852 l_msg_index := l_msg_index + 1;
853 l_msg_count := l_msg_count - 1;
854 END LOOP;
855 RAISE fnd_api.g_exc_error;
856 END IF;
857
858 ELSIF l_dest_instance_header_tbl.count > 1 THEN
859 -- Multiple Instances were found so throw error
860 debug('Multiple Instances were Found in Install Base-80');
861 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
862 fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
863 fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
864 fnd_message.set_token('INV_ORG_ID',l_mtl_item_tbl(j).organization_id);
865 fnd_message.set_token('LOCATOR',l_mtl_item_tbl(j).locator_id);
866 l_error_message := fnd_message.get;
867 RAISE fnd_api.g_exc_error;
868
869 END IF; -- End of Destination Record If
870
871 --ELSIF l_mtl_item_tbl(j).serial_number is NOT NULL THEN
872 ELSIF l_mtl_item_tbl(j).serial_number_control_code in (2,5) THEN
873 -- Serialized Item
874 IF l_src_instance_header_tbl.count = 1 THEN
875 -- Update Source Record then Continue
876
877 debug('Serialized Source records were found');
878 debug('Update the serialized item with Serial Number: '||l_src_instance_header_tbl(i).serial_number);
879
880 l_update_src_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
881 l_update_src_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
882 l_update_src_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).transfer_subinventory;
883 l_update_src_instance_rec.inv_locator_id := l_mtl_item_tbl(j).transfer_locator_id;
884 l_update_src_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
885 --l_update_src_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
886 l_update_src_instance_rec.location_id := nvl(r_loc_ids.subinv_location_id,r_loc_ids.hr_location_id);
887 --l_update_src_instance_rec.last_oe_order_line_id := r_so_info.line_id;
888 l_update_src_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
889
890 l_party_tbl.delete;
891 l_account_tbl.delete;
892 l_pricing_attrib_tbl.delete;
893 l_org_assignments_tbl.delete;
894 l_asset_assignment_tbl.delete;
895
896 debug('Before Update of Serialized Item Instance');
897
898 csi_item_instance_pub.update_item_instance(l_api_version,
899 l_commit,
900 l_init_msg_list,
901 l_validation_level,
902 l_update_src_instance_rec,
903 l_ext_attrib_values_tbl,
904 l_party_tbl,
905 l_account_tbl,
906 l_pricing_attrib_tbl,
907 l_org_assignments_tbl,
908 l_asset_assignment_tbl,
909 l_txn_rec,
910 l_instance_id_lst,
911 l_return_status,
912 l_msg_count,
913 l_msg_data);
914
915
916 l_upd_error_instance_id := NULL;
917 l_upd_error_instance_id := l_update_src_instance_rec.instance_id;
918
919 debug('After Update of Serialized Item Instance');
920 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
921
922 -- Check for any errors and add them to the message stack to pass out to be put into the
923 -- error log table.
924 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
925 debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
926 l_msg_index := 1;
927 WHILE l_msg_count > 0 loop
928 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
929 l_msg_index := l_msg_index + 1;
930 l_msg_count := l_msg_count - 1;
931 END LOOP;
932 RAISE fnd_api.g_exc_error;
933 END IF;
934
935 ELSIF l_src_instance_header_tbl.count = 0 THEN
936 debug('No Records were found in Install Base');
937 fnd_message.set_name('CSI','CSI_IB_RECORD_NOTFOUND');
938 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
939 fnd_message.set_token('SUBINVENTORY',l_mtl_item_tbl(j).subinventory_code);
940 fnd_message.set_token('ORG_ID',l_mtl_item_tbl(j).organization_id);
941 l_error_message := fnd_message.get;
942 RAISE fnd_api.g_exc_error;
943
944 ELSIF l_src_instance_header_tbl.count > 1 THEN
945 -- Multiple Instances were found so throw error
946 debug('Multiple Instances were Found in Install Base-40');
947 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
948 fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
949 fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
950 fnd_message.set_token('INV_ORG_ID',l_mtl_item_tbl(j).organization_id);
951 fnd_message.set_token('LOCATOR',l_mtl_item_tbl(j).locator_id);
952 l_error_message := fnd_message.get;
953 RAISE fnd_api.g_exc_error;
954 END IF; -- End of Source Record IF for Serialized
955
956 END IF; -- End of Serial Number If
957 END LOOP; -- End of For Loop
958
959 debug('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
960 debug('*****End of csi_inv_transfer_pkg.subinv_transfer Transaction*****');
961
962 EXCEPTION
963 WHEN fnd_api.g_exc_error THEN
964 debug('You have encountered a "fnd_api.g_exc_error" exception');
965 x_return_status := l_fnd_error;
966
967 IF l_mtl_item_tbl.count > 0 THEN
968 x_trx_error_rec.serial_number := l_mtl_item_tbl(j).serial_number;
969 x_trx_error_rec.lot_number := l_mtl_item_tbl(j).lot_number;
970 x_trx_error_rec.instance_id := l_upd_error_instance_id;
971 x_trx_error_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
972 x_trx_error_rec.src_serial_num_ctrl_code := l_mtl_item_tbl(j).serial_number_control_code;
973 x_trx_error_rec.src_location_ctrl_code := l_mtl_item_tbl(j).location_control_code;
974 x_trx_error_rec.src_lot_ctrl_code := l_mtl_item_tbl(j).lot_control_code;
975 x_trx_error_rec.src_rev_qty_ctrl_code := l_mtl_item_tbl(j).revision_qty_control_code;
976 x_trx_error_rec.comms_nl_trackable_flag := l_mtl_item_tbl(j).comms_nl_trackable_flag;
977 x_trx_error_rec.transaction_error_date := l_sysdate ;
978 END IF;
979
980 x_trx_error_rec.error_text := l_error_message;
981 x_trx_error_rec.transaction_id := NULL;
982 x_trx_error_rec.source_type := 'CSISUBTR';
983 x_trx_error_rec.source_id := p_transaction_id;
984 x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
985 x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
986 x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
987 x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
988
989 WHEN others THEN
990 l_sql_error := SQLERRM;
991 debug('SQL Error: '||l_sql_error);
992 debug('You have encountered a "others" exception');
993 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
994 fnd_message.set_token('API_NAME',l_api_name);
995 fnd_message.set_token('SQL_ERROR',SQLERRM);
996 x_return_status := l_fnd_unexpected;
997
998 IF l_mtl_item_tbl.count > 0 THEN
999 x_trx_error_rec.serial_number := l_mtl_item_tbl(j).serial_number;
1000 x_trx_error_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1001 x_trx_error_rec.instance_id := l_upd_error_instance_id;
1002 x_trx_error_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1003 x_trx_error_rec.src_serial_num_ctrl_code := l_mtl_item_tbl(j).serial_number_control_code;
1004 x_trx_error_rec.src_location_ctrl_code := l_mtl_item_tbl(j).location_control_code;
1005 x_trx_error_rec.src_lot_ctrl_code := l_mtl_item_tbl(j).lot_control_code;
1006 x_trx_error_rec.src_rev_qty_ctrl_code := l_mtl_item_tbl(j).revision_qty_control_code;
1007 x_trx_error_rec.comms_nl_trackable_flag := l_mtl_item_tbl(j).comms_nl_trackable_flag;
1008 x_trx_error_rec.transaction_error_date := l_sysdate ;
1009 END IF;
1010
1011 x_trx_error_rec.error_text := fnd_message.get;
1012 x_trx_error_rec.transaction_id := NULL;
1013 x_trx_error_rec.source_type := 'CSISUBTR';
1014 x_trx_error_rec.source_id := p_transaction_id;
1015 x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
1016 x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
1017 x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
1018 x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
1019
1020 END subinv_transfer;
1021
1022 END csi_inv_transfer_pkg;