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