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