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