[Home] [Help]
PACKAGE BODY: APPS.CSI_INV_INTERORG_PKG
Source
1 package body CSI_INV_INTERORG_PKG as
2 -- $Header: csiorgtb.pls 120.3.12000000.5 2007/07/06 15:31:23 ngoutam ship $
3
4 PROCEDURE debug(p_message IN varchar2) IS
5
6 BEGIN
7 csi_t_gen_utility_pvt.add(p_message);
8 EXCEPTION
9 WHEN others THEN
10 null;
11 END debug;
12
13 PROCEDURE intransit_shipment(p_transaction_id IN NUMBER,
14 p_message_id IN NUMBER,
15 x_return_status OUT NOCOPY VARCHAR2,
16 x_trx_error_rec OUT NOCOPY CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC)
17 IS
18
19 l_mtl_item_tbl CSI_INV_TRXS_PKG.MTL_ITEM_TBL_TYPE;
20 l_api_name VARCHAR2(100) := 'CSI_INV_INTERORG_PKG.INTRANSIT_SHIPMENT';
21 l_api_version NUMBER := 1.0;
22 l_commit VARCHAR2(1) := FND_API.G_FALSE;
23 l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
24 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
25 l_active_instance_only VARCHAR2(10) := FND_API.G_TRUE;
26 l_inactive_instance_only VARCHAR2(10) := FND_API.G_FALSE;
27 l_transaction_id NUMBER := NULL;
28 l_resolve_id_columns VARCHAR2(10) := FND_API.G_FALSE;
29 l_object_version_number NUMBER := 1;
30 l_sysdate DATE := SYSDATE;
31 l_master_organization_id NUMBER;
32 l_depreciable VARCHAR2(1);
33 l_txn_error_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC;
34 l_instance_query_rec CSI_DATASTRUCTURES_PUB.INSTANCE_QUERY_REC;
35 l_dest_instance_query_rec CSI_DATASTRUCTURES_PUB.INSTANCE_QUERY_REC;
36 l_update_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
37 l_upd_src_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
38 l_update_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
39 l_new_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
40 l_new_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
41 l_new_src_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
42 l_txn_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_REC;
43 l_return_status VARCHAR2(1);
44 l_error_code VARCHAR2(50);
45 l_error_message VARCHAR2(4000);
46 l_instance_id_lst CSI_DATASTRUCTURES_PUB.ID_TBL;
47 l_party_query_rec CSI_DATASTRUCTURES_PUB.PARTY_QUERY_REC;
48 l_account_query_rec CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_QUERY_REC;
49 l_instance_header_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_HEADER_TBL;
50 l_src_instance_header_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_HEADER_TBL;
51 l_dest_instance_header_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_HEADER_TBL;
52 l_ext_attrib_values_tbl CSI_DATASTRUCTURES_PUB.EXTEND_ATTRIB_VALUES_TBL;
53 l_party_tbl CSI_DATASTRUCTURES_PUB.PARTY_TBL;
54 l_account_tbl CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_TBL;
55 l_pricing_attrib_tbl CSI_DATASTRUCTURES_PUB.PRICING_ATTRIBS_TBL;
56 l_org_assignments_tbl CSI_DATASTRUCTURES_PUB.ORGANIZATION_UNITS_TBL;
57 l_asset_assignment_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_ASSET_TBL;
58 l_sub_inventory VARCHAR2(10);
59 l_location_type VARCHAR2(20);
60 l_trx_action_type VARCHAR2(50);
61 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
62 l_fnd_warning VARCHAR2(1) := 'W';
63 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
64 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
65 l_fnd_g_num NUMBER := FND_API.G_MISS_NUM;
66 l_fnd_g_char VARCHAR2(1) := FND_API.G_MISS_CHAR;
67 l_fnd_g_date DATE := FND_API.G_MISS_DATE;
68 l_in_inventory VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_INVENTORY;
69 l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
70 l_returned VARCHAR2(25) := 'RETURNED';
71 l_out_of_enterprise VARCHAR2(25) := 'OUT_OF_ENTERPRISE';
72 l_instance_usage_code VARCHAR2(25);
73 l_organization_id NUMBER;
74 l_subinventory_name VARCHAR2(10);
75 l_locator_id NUMBER;
76 l_transaction_error_id NUMBER;
77 l_trx_type_id NUMBER;
78 l_mfg_serial_flag VARCHAR2(1);
79 l_trans_type_code VARCHAR2(25);
80 l_trans_app_code VARCHAR2(5);
81 l_employee_id NUMBER;
82 l_file VARCHAR2(500);
83 l_msg_count NUMBER;
84 l_msg_data VARCHAR2(2000);
85 l_sql_error VARCHAR2(2000);
86 l_msg_index NUMBER;
87 j PLS_INTEGER;
88 i PLS_INTEGER :=1;
89 l_tbl_count NUMBER := 0;
90 l_neg_code NUMBER := 0;
91 l_instance_status VARCHAR2(1);
92 l_redeploy_flag VARCHAR2(1);
93 l_upd_error_instance_id NUMBER := NULL;
94 l_mfg_flag VARCHAR2(1) := NULL;
95 l_serial_number VARCHAR2(30) := NULL;
96 l_quantity NUMBER := 0;
97 l_def_in_transit_loc_id NUMBER := NULL;
98
99 cursor c_id is
100 SELECT instance_status_id
101 FROM csi_instance_statuses
102 WHERE name = FND_PROFILE.VALUE('CSI_DEFAULT_INSTANCE_STATUS');
103
104 r_id c_id%rowtype;
105
106 CURSOR c_item_control (pc_item_id in number,
107 pc_org_id in number) is
108 SELECT serial_number_control_code,
109 lot_control_code,
110 revision_qty_control_code,
111 location_control_code,
112 comms_nl_trackable_flag
113 FROM mtl_system_items_b
114 WHERE inventory_item_id = pc_item_id
115 AND organization_id = pc_org_id;
116
117 r_item_control c_item_control%rowtype;
118
119 BEGIN
120
121 x_return_status := l_fnd_success;
122 l_error_message := NULL;
123
124 debug('******Start of csi_inv_interorg_pkg.intransit_shipment Transaction procedure******');
125 debug('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
126 debug('csiorgtb.pls 115.23');
127 debug('Transaction ID with is: '||p_transaction_id);
128
129 -- Get the default in transit location id
130 l_def_in_transit_loc_id := csi_datastructures_pub.g_install_param_rec.in_transit_location_id;
131
132 debug('Default In Transit Loc IDs: '||l_def_in_transit_loc_id);
133
134 -- This procedure queries all of the Inventory Transaction Records and
135 -- returns them as a table.
136
137 csi_inv_trxs_pkg.get_transaction_recs(p_transaction_id,
138 l_mtl_item_tbl,
139 l_return_status,
140 l_error_message);
141
142 l_tbl_count := 0;
143 l_tbl_count := l_mtl_item_tbl.count;
144
145 debug('Inventory Records Found: '||l_tbl_count);
146
147 IF NOT l_return_status = l_fnd_success THEN
148 debug('You have encountered an error in CSI_INV_TRXS_PKG.get_transaction_recs, Transaction ID: '||p_transaction_id);
149 RAISE fnd_api.g_exc_error;
150 END IF;
151
152 debug('Transaction Action ID: '||l_mtl_item_tbl(i).transaction_action_id);
153 debug('Transaction Source Type ID: '||l_mtl_item_tbl(i).transaction_source_type_id);
154 debug('Transaction Quantity: '||l_mtl_item_tbl(i).transaction_quantity);
155
156 -- Get the Master Organization ID
157 csi_inv_trxs_pkg.get_master_organization(l_mtl_item_tbl(i).organization_id,
158 l_master_organization_id,
159 l_return_status,
160 l_error_message);
161
162 IF NOT l_return_status = l_fnd_success THEN
163 debug('You have encountered an error in csi_inv_trxs_pkg.get_master_organization, Organization ID: '||l_mtl_item_tbl(i).organization_id);
164 RAISE fnd_api.g_exc_error;
165 END IF;
166
167 -- Call get_fnd_employee_id and get the employee id
168 l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
169
170 IF l_employee_id = -1 THEN
171 debug('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
172 END IF;
173
174 debug('The Employee that is processing this Transaction is: '||l_employee_id);
175
176 -- See if this is a depreciable Item to set the status of the transaction record
177 csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
178 l_depreciable);
179
180 debug('Is this Item ID: '||l_mtl_item_tbl(i).inventory_item_id||', Depreciable :'||l_depreciable);
181
182 -- Get the Negative Receipt Code to see if this org allows Negative
183 -- Quantity Records 1 = Yes, 2 = No
184
185 l_neg_code := csi_inv_trxs_pkg.get_neg_inv_code(
186 l_mtl_item_tbl(i).organization_id);
187
188 IF l_neg_code = 1 AND l_mtl_item_tbl(i).serial_number_control_code in (1,6) THEN
189 l_instance_status := FND_API.G_FALSE;
190 ELSE
191 l_instance_status := FND_API.G_TRUE;
192 END IF;
193
194 debug('Negative Code is - 1 = Yes, 2 = No: '||l_neg_code);
195
196 -- Determine Transaction Type for this
197
198 l_trans_type_code := 'INTERORG_TRANS_SHIPMENT';
199 l_trans_app_code := 'INV';
200
201 debug('Trans Type Code: '||l_trans_type_code);
202 debug('Trans App Code: '||l_trans_app_code);
203
204 -- Initialize Transaction Record
205 l_txn_rec := csi_inv_trxs_pkg.init_txn_rec;
206
207 -- Set Status based on redeployment
208 IF l_depreciable = 'N' THEN
209 IF l_mtl_item_tbl(i).serial_number is NOT NULL THEN
210 csi_inv_trxs_pkg.get_redeploy_flag(l_mtl_item_tbl(i).inventory_item_id,
211 l_mtl_item_tbl(i).serial_number,
212 l_sysdate,
213 l_redeploy_flag,
214 l_return_status,
215 l_error_message);
216 END IF;
217 IF l_redeploy_flag = 'Y' THEN
218 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
219 ELSE
220 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_complete;
221 END IF;
222 ELSE
223 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
224 END IF;
225
226 IF NOT l_return_status = l_fnd_success THEN
227 debug('Redeploy Flag: '||l_redeploy_flag);
228 debug('You have encountered an error in csi_inv_trxs_pkg.get_redeploy_flag: '||l_error_message);
229 RAISE fnd_api.g_exc_error;
230 END IF;
231
232 debug('Redeploy Flag: '||l_redeploy_flag);
233 debug('Trans Status Code: '||l_txn_rec.transaction_status_code);
234
235 -- Create CSI Transaction to be used
236 l_txn_rec.source_transaction_date := l_mtl_item_tbl(i).transaction_date;
237 l_txn_rec.transaction_date := l_sysdate;
238 l_txn_rec.transaction_type_id :=
239 csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
240 l_txn_rec.transaction_quantity :=
241 l_mtl_item_tbl(i).transaction_quantity;
242 l_txn_rec.transaction_uom_code := l_mtl_item_tbl(i).transaction_uom;
243 l_txn_rec.transacted_by := l_employee_id;
244 l_txn_rec.transaction_action_code := NULL;
245 l_txn_rec.message_id := p_message_id;
246 l_txn_rec.inv_material_transaction_id := p_transaction_id;
247 l_txn_rec.object_version_number := l_object_version_number;
248 l_txn_rec.source_line_ref := l_mtl_item_tbl(i).shipment_number;
249
250 csi_inv_trxs_pkg.create_csi_txn(l_txn_rec,
251 l_error_message,
252 l_return_status);
253
254 debug('CSI Transaction Created: '||l_txn_rec.transaction_id);
255
256 IF NOT l_return_status = l_fnd_success THEN
257 debug('You have encountered an error in csi_inv_trxs_pkg.create_csi_txn: '||p_transaction_id);
258 RAISE fnd_api.g_exc_error;
259 END IF;
260
261 -- Now loop through the PL/SQL Table.
262 j := 1;
263
264 debug('Starting to loop through Material Transaction Records');
265
266 -- Get Default Profile Instance Status
267 OPEN c_id;
268 FETCH c_id into r_id;
269 CLOSE c_id;
270
271 debug('Default Profile Status: '||r_id.instance_status_id);
272
273 FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
274
275 -- Get Receiving Organization Item Master Control Codes
276 OPEN c_item_control (l_mtl_item_tbl(j).inventory_item_id,
277 l_mtl_item_tbl(j).transfer_organization_id);
278 FETCH c_item_control into r_item_control;
279 CLOSE c_item_control;
280
281 debug('Serial Number : '||l_mtl_item_tbl(j).serial_number);
282 debug('Shipping Org Serial Number Control Code: '||l_mtl_item_tbl(j).serial_number_control_code);
283 debug('Receiving Org Serial Number Control Code: '||r_item_control.serial_number_control_code);
284 debug('Shipping Org Lot Control Code: '||l_mtl_item_tbl(j).lot_control_code);
285 debug('Receiving Org Lot Control Code: '||r_item_control.lot_control_code);
286 debug('Shipping Org Loction Control Code: '||l_mtl_item_tbl(j).location_control_code);
287 debug('Receiving Org Location Control Code: '||r_item_control.location_control_code);
288 debug('Shipping Org Revision Control Code: '||l_mtl_item_tbl(j).revision_qty_control_code);
289 debug('Receiving Org Revision Control Code: '||r_item_control.revision_qty_control_code);
290 debug('Receiving Org Trackable Flag: '||r_item_control.comms_nl_trackable_flag);
291 debug('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
292 debug('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
293 debug('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
294 debug('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
295 debug('Organization ID: '||l_mtl_item_tbl(j).organization_id);
296 debug('Transfer Org ID: '||l_mtl_item_tbl(j).transfer_organization_id);
297
298 l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
299 l_instance_usage_code := l_fnd_g_char;
300
301 IF l_mtl_item_tbl(j).serial_number_control_code in (1,6) THEN
302 --In Transit Shipment
303 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
304 l_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
305 l_instance_query_rec.serial_number := NULL;
306 l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
307 l_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
308 l_instance_query_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
309 l_instance_query_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
310 l_instance_query_rec.instance_usage_code := l_in_inventory;
311 l_sub_inventory := NULL;
312 l_trx_action_type := 'IN_TRANSIT_SHIPMENT';
313 l_instance_usage_code := l_instance_query_rec.instance_usage_code;
314
315 debug('Set Serial Number to NULL');
316
317 ELSIF l_mtl_item_tbl(j).serial_number_control_code in (2,5) THEN
318 --In Transit Shipment
319 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
320 l_instance_query_rec.serial_number := l_mtl_item_tbl(j).serial_number;
321 l_sub_inventory := NULL;
322 l_trx_action_type := 'IN_TRANSIT_SHIPMENT';
323 l_instance_usage_code := l_instance_query_rec.instance_usage_code;
324
325 debug('Set Serial Number to: '||l_instance_query_rec.serial_number);
326
327 END IF;
328
329 debug('Transaction Action Type:'|| l_trx_action_type);
330 debug('Before Get Item Instance - 1');
331
335 l_validation_level,
332 csi_item_instance_pub.get_item_instances(l_api_version,
333 l_commit,
334 l_init_msg_list,
336 l_instance_query_rec,
337 l_party_query_rec,
338 l_account_query_rec,
339 l_transaction_id,
340 l_resolve_id_columns,
341 l_instance_status,
342 l_src_instance_header_tbl,
343 l_return_status,
344 l_msg_count,
345 l_msg_data);
346
347 debug('After Get Item Instance - 2');
348
349 l_tbl_count := 0;
350 l_tbl_count := l_src_instance_header_tbl.count;
351
352 debug('Source Records Found: '||l_tbl_count);
353
354 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
355 IF NOT l_return_status = l_fnd_success then
356 debug('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
357 l_msg_index := 1;
358 WHILE l_msg_count > 0 loop
359 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
360 l_msg_index := l_msg_index + 1;
361 l_msg_count := l_msg_count - 1;
362 END LOOP;
363 RAISE fnd_api.g_exc_error;
364 END IF;
365
366 debug('Before checking to see if Source records Exist - 3');
367
368 IF l_mtl_item_tbl(j).serial_number_control_code in (2,5) THEN
369 IF l_src_instance_header_tbl.count = 1 THEN
370
371 IF r_item_control.serial_number_control_code <> 1 THEN -- Do Regular Processing
372
373 IF l_src_instance_header_tbl(i).instance_usage_code IN (l_in_transit,l_in_inventory,l_returned) THEN
374
375 debug('Updating Serialized Instance: '||l_mtl_item_tbl(j).serial_number);
376 debug('Shipping Serial Code is 2,5');
377
378 l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
379 l_update_instance_rec.inv_subinventory_name := NULL;
380 l_update_instance_rec.inv_locator_id := NULL;
381 -- Added for Bug 5975739
382 l_update_instance_rec.inv_master_organization_id := l_master_organization_id;
383 l_update_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
384 l_update_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
385 l_update_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
386 l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
387 l_update_instance_rec.instance_usage_code := l_in_transit;
388 l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
389
390 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);
391
392 debug('Instance Status - 4: '||l_update_instance_rec.instance_status_id);
393 debug('After you initialize the Transaction Record Values- 4');
394
395 l_party_tbl.delete;
396 l_account_tbl.delete;
397 l_pricing_attrib_tbl.delete;
398 l_org_assignments_tbl.delete;
399 l_asset_assignment_tbl.delete;
400
401 debug('Before Update Item Instance - 5');
402
403 csi_item_instance_pub.update_item_instance(l_api_version,
404 l_commit,
405 l_init_msg_list,
406 l_validation_level,
407 l_update_instance_rec,
408 l_ext_attrib_values_tbl,
409 l_party_tbl,
410 l_account_tbl,
411 l_pricing_attrib_tbl,
412 l_org_assignments_tbl,
413 l_asset_assignment_tbl,
414 l_txn_rec,
415 l_instance_id_lst,
416 l_return_status,
417 l_msg_count,
418 l_msg_data);
419
420 l_upd_error_instance_id := NULL;
421 l_upd_error_instance_id := l_update_instance_rec.instance_id;
422
423 debug('After Update Item Instance - 6');
424 debug('You are updating Instance: '||l_update_instance_rec.instance_id);
425 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
426
427 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
431 WHILE l_msg_count > 0 loop
428 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
429 debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
430 l_msg_index := 1;
432 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
433 l_msg_index := l_msg_index + 1;
434 l_msg_count := l_msg_count - 1;
435 END LOOP;
436 RAISE fnd_api.g_exc_error;
437 END IF;
438
439 ELSE -- No Serialized Instances found so Error.
440
441 debug('No Records were found in Install Base - 7');
442
443 fnd_message.set_name('CSI','CSI_IB_RECORD_NOTFOUND');
444 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
445 fnd_message.set_token('SUBINVENTORY',l_mtl_item_tbl(j).subinventory_code);
446 fnd_message.set_token('ORG_ID',l_mtl_item_tbl(j).organization_id);
447 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
448 l_error_message := fnd_message.get;
449 RAISE fnd_api.g_exc_error;
450 END IF; -- End of Usage Code Check
451
452 ELSE -- -- Serial Control is 1 ( No Control ) so set to Out Of Enterprise
453
454 IF l_src_instance_header_tbl(i).instance_usage_code IN (l_in_transit,l_in_inventory,l_returned) THEN
455
456 debug('Updating Serialized Instance to Out of Enterprise: '||l_mtl_item_tbl(j).serial_number);
457 debug('Shipping Serial Code is 2,5 and Receiving is 1');
458
459 l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
460 l_update_instance_rec.inv_subinventory_name := NULL;
461 l_update_instance_rec.inv_locator_id := NULL;
462 -- Added for Bug 5975739
463 l_update_instance_rec.inv_master_organization_id := l_master_organization_id;
464 l_update_instance_rec.inv_organization_id := NULL;
465 l_update_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
466 l_update_instance_rec.location_id := l_def_in_transit_loc_id;
467 l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('hz_locations');
468 l_update_instance_rec.active_end_date := l_sysdate;
469 l_update_instance_rec.instance_usage_code := l_out_of_enterprise;
470 l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
471
472 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);
473
474 debug('Instance Status - 8: '||l_update_instance_rec.instance_status_id);
475 debug('After you initialize the Transaction Record Values- 8');
476
477 l_party_tbl.delete;
478 l_account_tbl.delete;
479 l_pricing_attrib_tbl.delete;
480 l_org_assignments_tbl.delete;
481 l_asset_assignment_tbl.delete;
482
483 debug('Before Update Item Instance - 9');
484
485 csi_item_instance_pub.update_item_instance(l_api_version,
486 l_commit,
487 l_init_msg_list,
488 l_validation_level,
489 l_update_instance_rec,
490 l_ext_attrib_values_tbl,
491 l_party_tbl,
492 l_account_tbl,
493 l_pricing_attrib_tbl,
494 l_org_assignments_tbl,
495 l_asset_assignment_tbl,
496 l_txn_rec,
497 l_instance_id_lst,
498 l_return_status,
499 l_msg_count,
500 l_msg_data);
501
502 l_upd_error_instance_id := NULL;
503 l_upd_error_instance_id := l_update_instance_rec.instance_id;
504
505 debug('After Update Item Instance - 10');
506 debug('You are updating Instance: '||l_update_instance_rec.instance_id);
507 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
508
509 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
510 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
511 debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
512 l_msg_index := 1;
513 WHILE l_msg_count > 0 loop
514 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
515 l_msg_index := l_msg_index + 1;
516 l_msg_count := l_msg_count - 1;
517 END LOOP;
518 RAISE fnd_api.g_exc_error;
519 END IF;
520
521 IF j = 1 THEN -- Look for IN Transit Non Serial If not there create or Update only 1 time
522 l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
526 l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
523 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
524 l_instance_query_rec.serial_number := NULL;
525 l_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
527 l_instance_query_rec.in_transit_order_line_id := NULL;
528 l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
529 l_instance_query_rec.instance_usage_code := l_in_transit;
530
531 -- 5639896 next 3 lines
532 l_instance_query_rec.inv_subinventory_name := NULL;
533 l_instance_query_rec.inv_organization_id := NULL;
534 l_instance_query_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
535
536 l_instance_usage_code := l_in_transit;
537 l_subinventory_name := NULL;
538 l_organization_id := l_mtl_item_tbl(j).organization_id;
539 l_locator_id := NULL;
540
541 l_mfg_flag := NULL;
542 l_serial_number := NULL;
543 l_quantity := abs(l_mtl_item_tbl(j).transaction_quantity);
544
545 debug('Since the Shipping Code is 2 or 5 and the Receiving is 1 Look for Non Serial In Transit');
546 csi_t_gen_utility_pvt.dump_instance_query_rec(l_instance_query_rec);
547
548 debug('Before Get Item Instance for Dest Non Serialized Instance-11');
549
550 csi_item_instance_pub.get_item_instances(l_api_version,
551 l_commit,
552 l_init_msg_list,
553 l_validation_level,
554 l_instance_query_rec,
555 l_party_query_rec,
556 l_account_query_rec,
557 l_transaction_id,
558 l_resolve_id_columns,
559 l_inactive_instance_only,
560 l_dest_instance_header_tbl,
561 l_return_status,
562 l_msg_count,
563 l_msg_data);
564
565 debug('After Get Item Instance-12');
566
567 l_tbl_count := 0;
568 l_tbl_count := l_dest_instance_header_tbl.count;
569
570 debug('Source Records Found: '||l_tbl_count);
571
572 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
573 IF NOT l_return_status = l_fnd_success then
574 debug('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
575 l_msg_index := 1;
576 WHILE l_msg_count > 0 loop
577 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
578 l_msg_index := l_msg_index + 1;
579 l_msg_count := l_msg_count - 1;
580 END LOOP;
581 RAISE fnd_api.g_exc_error;
582 END IF;
583
584 IF l_dest_instance_header_tbl.count < 1 THEN -- Installed Base Destination Records are not found so create a new record
585
586 debug('Creating New Dest dest Instance-13');
587
588 l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
589 l_new_dest_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
590 l_new_dest_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
591 l_new_dest_instance_rec.inv_master_organization_id := l_master_organization_id;
592 l_new_dest_instance_rec.mfg_serial_number_flag := l_mfg_flag;
593 l_new_dest_instance_rec.serial_number := l_serial_number;
594 l_new_dest_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
595 l_new_dest_instance_rec.quantity := l_quantity;
596 l_new_dest_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
597 -- l_new_dest_instance_rec.location_id := l_def_in_transit_loc_id;
598 l_new_dest_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
599 l_new_dest_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
600 --l_new_dest_instance_rec.in_transit_order_line_id := r_so_info.line_id;
601 l_new_dest_instance_rec.instance_usage_code := l_instance_usage_code;
602 l_new_dest_instance_rec.vld_organization_id := l_mtl_item_tbl(j).transfer_organization_id;
603 l_new_dest_instance_rec.inv_subinventory_name := l_subinventory_name;
604 l_new_dest_instance_rec.inv_locator_id := l_locator_id;
605 l_new_dest_instance_rec.customer_view_flag := 'N';
606 l_new_dest_instance_rec.merchant_view_flag := 'Y';
607 l_new_dest_instance_rec.operational_status_code := 'NOT_USED';
611
608 l_new_dest_instance_rec.object_version_number := l_object_version_number;
609 l_new_dest_instance_rec.active_start_date := l_sysdate;
610 l_new_dest_instance_rec.active_end_date := NULL;
612 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
613 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
614 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
615 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
616 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
617 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
618
619 debug('Before Create Item Instance-14');
620
621 csi_item_instance_pub.create_item_instance(l_api_version,
622 l_commit,
623 l_init_msg_list,
624 l_validation_level,
625 l_new_dest_instance_rec,
626 l_ext_attrib_values_tbl,
627 l_party_tbl,
628 l_account_tbl,
629 l_pricing_attrib_tbl,
630 l_org_assignments_tbl,
631 l_asset_assignment_tbl,
632 l_txn_rec,
633 l_return_status,
634 l_msg_count,
635 l_msg_data);
636
637 debug('After Create Item Instance-15');
638 debug('You are Creating Instance: '||l_new_dest_instance_rec.instance_id);
639
640 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
641
642 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
643 debug('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
644 l_msg_index := 1;
645 WHILE l_msg_count > 0 loop
646 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
647 l_msg_index := l_msg_index + 1;
648 l_msg_count := l_msg_count - 1;
649 END LOOP;
650 RAISE fnd_api.g_exc_error;
651 END IF;
652
653 ELSIF l_dest_instance_header_tbl.count = 1 THEN -- Installed Base Destination Records Found
654
655 debug('Instance Usage Code: '||l_dest_instance_header_tbl(i).instance_usage_code);
656 debug('Item ID: '||l_dest_instance_header_tbl(i).inventory_item_id);
657 debug('Instance ID: '||l_dest_instance_header_tbl(i).instance_id);
658
659 l_update_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
660 l_update_dest_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
661 l_update_dest_instance_rec.quantity := l_dest_instance_header_tbl(i).quantity + l_quantity;
662 --l_update_dest_instance_rec.location_id := l_def_in_transit_loc_id;
663 l_update_dest_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
664 l_update_dest_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
665 --l_update_dest_instance_rec.in_transit_order_line_id := r_so_info.line_id;
666 -- Added for Bug 5975739
667 l_update_dest_instance_rec.inv_master_organization_id := l_master_organization_id;
668 l_update_dest_instance_rec.inv_organization_id := NULL;
669 l_update_dest_instance_rec.inv_subinventory_name := l_subinventory_name;
670 l_update_dest_instance_rec.inv_locator_id := l_locator_id;
671 l_update_dest_instance_rec.instance_usage_code := l_in_transit;
672 l_update_dest_instance_rec.active_end_date := NULL;
673 l_update_dest_instance_rec.active_end_date := NULL;
674 l_update_dest_instance_rec.object_version_number := l_dest_instance_header_tbl(i).object_version_number;
675
676 l_party_tbl.delete;
677 l_account_tbl.delete;
678 l_pricing_attrib_tbl.delete;
679 l_org_assignments_tbl.delete;
680 l_asset_assignment_tbl.delete;
681
682 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);
683
684 debug('Before Update Item Instance-16');
685 debug('Instance Status-11: '||l_update_dest_instance_rec.instance_status_id);
686
687 csi_item_instance_pub.update_item_instance(l_api_version,
688 l_commit,
689 l_init_msg_list,
690 l_validation_level,
694 l_account_tbl,
691 l_update_dest_instance_rec,
692 l_ext_attrib_values_tbl,
693 l_party_tbl,
695 l_pricing_attrib_tbl,
696 l_org_assignments_tbl,
697 l_asset_assignment_tbl,
698 l_txn_rec,
699 l_instance_id_lst,
700 l_return_status,
701 l_msg_count,
702 l_msg_data);
703
704 l_upd_error_instance_id := NULL;
705 l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
706
707 debug('After Update Item Instance-17');
708 debug('You are updating Instance: '||l_update_dest_instance_rec.instance_id);
709 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
710
711 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
712 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
713 debug('You encountered an error in the csi_item_instance_pub.c API '||l_msg_data);
714 l_msg_index := 1;
715 WHILE l_msg_count > 0 loop
716 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
717 l_msg_index := l_msg_index + 1;
718 l_msg_count := l_msg_count - 1;
719 END LOOP;
720 RAISE fnd_api.g_exc_error;
721 END IF;
722
723 ELSE -- Error No dest non serial recs round
724 debug('No Records were found in Install Base but the usage is not correct-14, The Usage is: '||l_dest_instance_header_tbl(i).instance_usage_code);
725 fnd_message.set_name('CSI','CSI_IB_RECORD_NOTFOUND');
726 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
727 fnd_message.set_token('SUBINVENTORY',l_mtl_item_tbl(j).subinventory_code);
728 fnd_message.set_token('ORG_ID',l_mtl_item_tbl(j).organization_id);
729 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
730 l_error_message := fnd_message.get;
731 RAISE fnd_api.g_exc_error;
732 END IF; -- End of Destination Record If
733
734 END IF; -- End of j=1 for Control Code 1
735
736 ELSE -- No Serialized Instances found so Error.
737 debug('No Records were found in Install Base - 18');
738
739 fnd_message.set_name('CSI','CSI_IB_RECORD_NOTFOUND');
740 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
741 fnd_message.set_token('SUBINVENTORY',l_mtl_item_tbl(j).subinventory_code);
742 fnd_message.set_token('ORG_ID',l_mtl_item_tbl(j).organization_id);
743 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
744 l_error_message := fnd_message.get;
745 RAISE fnd_api.g_exc_error;
746 END IF; -- End of Usage Code Check if Ship is 2,5 and Rec is 1
747
748 END IF; -- End of If for Rec Serial Code Check
749
750
751 ELSE -- No Serialized Instances found so Error.
752 debug('No Records were found in Install Base - 19');
753
754 fnd_message.set_name('CSI','CSI_IB_RECORD_NOTFOUND');
755 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
756 fnd_message.set_token('SUBINVENTORY',l_mtl_item_tbl(j).subinventory_code);
757 fnd_message.set_token('ORG_ID',l_mtl_item_tbl(j).organization_id);
758 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
759 l_error_message := fnd_message.get;
760 RAISE fnd_api.g_exc_error;
761
762 END IF; -- End of 2,5 Serial Control
763
764 ELSIF l_mtl_item_tbl(j).serial_number_control_code in (1,6) THEN
765 IF l_src_instance_header_tbl.count = 0 THEN
766 IF l_neg_code = 1 THEN -- Negative Records Allowed so Create/Update
767
768 debug('No records were found and Inventory Allows Negative Quantities so create a new Source Instance Record - 8');
769
770 l_new_src_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
771 l_new_src_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
772 l_new_src_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
773 l_new_src_instance_rec.inv_master_organization_id := l_master_organization_id;
774 l_new_src_instance_rec.mfg_serial_number_flag := 'N';
775 l_new_src_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
776 l_new_src_instance_rec.quantity := l_mtl_item_tbl(j).transaction_quantity;
777 l_new_src_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
778 l_new_src_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
779 l_new_src_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
780 l_new_src_instance_rec.instance_usage_code := l_instance_usage_code;
784 l_new_src_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
781 l_new_src_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
782 l_new_src_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
783 l_new_src_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
785 l_new_src_instance_rec.customer_view_flag := 'N';
786 l_new_src_instance_rec.merchant_view_flag := 'Y';
787 l_new_src_instance_rec.operational_status_code := 'NOT_USED';
788 l_new_src_instance_rec.object_version_number := l_object_version_number;
789 l_new_src_instance_rec.active_start_date := l_sysdate;
790 l_new_src_instance_rec.active_end_date := NULL;
791
792 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
793 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
794 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
795 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
796 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
797 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
798
799 debug('Before Create Source Item Instance - 9');
800
801 csi_item_instance_pub.create_item_instance(l_api_version,
802 l_commit,
803 l_init_msg_list,
804 l_validation_level,
805 l_new_src_instance_rec,
806 l_ext_attrib_values_tbl,
807 l_party_tbl,
808 l_account_tbl,
809 l_pricing_attrib_tbl,
810 l_org_assignments_tbl,
811 l_asset_assignment_tbl,
812 l_txn_rec,
813 l_return_status,
814 l_msg_count,
815 l_msg_data);
816
817 debug('After Create Source Item Instance - 10');
818 debug('You are Creating Instance: '||l_new_src_instance_rec.instance_id);
819
820 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
821
822 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
823 debug('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
824 l_msg_index := 1;
825 WHILE l_msg_count > 0 loop
826 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
827 l_msg_index := l_msg_index + 1;
828 l_msg_count := l_msg_count - 1;
829 END LOOP;
830 RAISE fnd_api.g_exc_error;
831 END IF;
832
833 ELSE -- Neg Code is <> 1 so Neg Qtys are not allowed so error
834 debug('No Records were found in Install Base - 11');
835
836 fnd_message.set_name('CSI','CSI_IB_RECORD_NOTFOUND');
837 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
838 fnd_message.set_token('SUBINVENTORY',l_mtl_item_tbl(j).subinventory_code);
839 fnd_message.set_token('ORG_ID',l_mtl_item_tbl(j).organization_id);
840 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
841 l_error_message := fnd_message.get;
842 RAISE fnd_api.g_exc_error;
843
844 END IF; -- End of Neg Qty If
845
846 ELSIF l_src_instance_header_tbl.count = 1 THEN
847
848 debug('You will update instance: '||l_src_instance_header_tbl(i).instance_id);
849
850 l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
851 l_upd_src_dest_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
852 l_upd_src_dest_instance_rec.active_end_date := NULL;
853 l_upd_src_dest_instance_rec.quantity := l_src_instance_header_tbl(i).quantity - abs(l_mtl_item_tbl(j).primary_quantity);
854 l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
855
856 l_party_tbl.delete;
857 l_account_tbl.delete;
858 l_pricing_attrib_tbl.delete;
859 l_org_assignments_tbl.delete;
860 l_asset_assignment_tbl.delete;
861
862 debug('Before Update Source Item Instance - 13');
863
864 l_upd_src_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);
865
866 debug('Instance Status Id: '||l_upd_src_dest_instance_rec.instance_status_id);
867
868 csi_item_instance_pub.update_item_instance(l_api_version,
872 l_upd_src_dest_instance_rec,
869 l_commit,
870 l_init_msg_list,
871 l_validation_level,
873 l_ext_attrib_values_tbl,
874 l_party_tbl,
875 l_account_tbl,
876 l_pricing_attrib_tbl,
877 l_org_assignments_tbl,
878 l_asset_assignment_tbl,
879 l_txn_rec,
880 l_instance_id_lst,
881 l_return_status,
882 l_msg_count,
883 l_msg_data);
884
885 l_upd_error_instance_id := NULL;
886 l_upd_error_instance_id := l_upd_src_dest_instance_rec.instance_id;
887
888 debug('After Update Item Instance - 14');
889 debug('You are updating Instance: '||l_upd_src_dest_instance_rec.instance_id);
890 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
891
892 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
893 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
894 debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
895 l_msg_index := 1;
896 WHILE l_msg_count > 0 loop
897 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
898 l_msg_index := l_msg_index + 1;
899 l_msg_count := l_msg_count - 1;
900 END LOOP;
901 RAISE fnd_api.g_exc_error;
902 END IF;
903
904 ELSIF l_src_instance_header_tbl.count > 1 THEN
905 -- Multiple Instances were found so throw error
906 debug('Multiple Instances were Found in Install Base-30');
907 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
908 fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
909 fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
910 fnd_message.set_token('INV_ORG_ID',l_mtl_item_tbl(j).organization_id);
911 fnd_message.set_token('LOCATOR',l_mtl_item_tbl(j).locator_id);
912 l_error_message := fnd_message.get;
913 RAISE fnd_api.g_exc_error;
914 END IF; -- End of If for Source Count
915
916 -- Get Destination Records
917
918 debug('Before Getting Dest Instances - 16 ');
919
920 l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
921 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
922 l_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
923 l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
924 l_instance_query_rec.serial_number := NULL;
925 l_instance_query_rec.instance_usage_code := l_in_transit;
926 l_instance_query_rec.inv_subinventory_name := NULL;
927
928 -- Bug 5639896
929 l_instance_query_rec.inv_organization_id := NULL;
930
931 l_instance_query_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
932 l_instance_query_rec.in_transit_order_line_id := NULL;
933 --JPW JUNE21
934 --l_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
935 l_instance_query_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id; --fix for bug5704500/bug6036067
936 l_instance_usage_code := l_instance_query_rec.instance_usage_code;
937
938 l_subinventory_name := NULL;
939 --JPW JUNE21
940 l_organization_id := NULL;
941 --l_organization_id := l_mtl_item_tbl(j).organization_id;
942 l_locator_id := NULL;
943
944 debug('Before Get Item Instance - 17');
945
946 csi_item_instance_pub.get_item_instances(l_api_version,
947 l_commit,
948 l_init_msg_list,
949 l_validation_level,
950 l_instance_query_rec,
951 l_party_query_rec,
952 l_account_query_rec,
953 l_transaction_id,
954 l_resolve_id_columns,
955 l_inactive_instance_only,
956 l_dest_instance_header_tbl,
957 l_return_status,
958 l_msg_count,
959 l_msg_data);
963 l_tbl_count := 0;
960
961 debug('After Get Item Instance - 18');
962
964 l_tbl_count := l_dest_instance_header_tbl.count;
965 debug('Source Records Found: '||l_tbl_count);
966
967 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
968 IF NOT l_return_status = l_fnd_success then
969 debug('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
970 l_msg_index := 1;
971 WHILE l_msg_count > 0 loop
972 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
973 l_msg_index := l_msg_index + 1;
974 l_msg_count := l_msg_count - 1;
975 END LOOP;
976 RAISE fnd_api.g_exc_error;
977 END IF;
978
979 IF l_dest_instance_header_tbl.count = 0 THEN -- Installed Base Destination Records are not found so create a new record
980
981 debug('Creating New Dest dest Instance - 19');
982
983 l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
984 l_new_dest_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
985 l_new_dest_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
986 l_new_dest_instance_rec.inv_master_organization_id := l_master_organization_id;
987 l_new_dest_instance_rec.mfg_serial_number_flag := 'N';
988 l_new_dest_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
989 l_new_dest_instance_rec.quantity := abs(l_mtl_item_tbl(j).transaction_quantity);
990 l_new_dest_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
991 l_new_dest_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
992 l_new_dest_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
993 l_new_dest_instance_rec.instance_usage_code := l_instance_usage_code;
994 l_new_dest_instance_rec.inv_organization_id := l_organization_id;
995 l_new_dest_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
996 l_new_dest_instance_rec.inv_subinventory_name := l_subinventory_name;
997 l_new_dest_instance_rec.inv_locator_id := l_locator_id;
998 l_new_dest_instance_rec.customer_view_flag := 'N';
999 l_new_dest_instance_rec.merchant_view_flag := 'Y';
1000 l_new_dest_instance_rec.operational_status_code := 'NOT_USED';
1001 l_new_dest_instance_rec.object_version_number := l_object_version_number;
1002 l_new_dest_instance_rec.active_start_date := l_sysdate;
1003 l_new_dest_instance_rec.active_end_date := NULL;
1004
1005 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
1006 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
1007 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
1008 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
1009 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
1010 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
1011
1012 debug('Before Create Item Instance - 20');
1013
1014 csi_item_instance_pub.create_item_instance(l_api_version,
1015 l_commit,
1016 l_init_msg_list,
1017 l_validation_level,
1018 l_new_dest_instance_rec,
1019 l_ext_attrib_values_tbl,
1020 l_party_tbl,
1021 l_account_tbl,
1022 l_pricing_attrib_tbl,
1023 l_org_assignments_tbl,
1024 l_asset_assignment_tbl,
1025 l_txn_rec,
1026 l_return_status,
1027 l_msg_count,
1028 l_msg_data);
1029
1030 debug('After Create Item Instance - 21');
1031 debug('You are Creating Instance: '||l_new_dest_instance_rec.instance_id);
1032
1033 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1034
1035 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1036 debug('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
1037 l_msg_index := 1;
1038 WHILE l_msg_count > 0 loop
1039 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1040 l_msg_index := l_msg_index + 1;
1041 l_msg_count := l_msg_count - 1;
1042 END LOOP;
1043 RAISE fnd_api.g_exc_error;
1047
1044 END IF;
1045
1046 ELSIF l_dest_instance_header_tbl.count = 1 THEN -- Installed Base Destination Records Found
1048 l_update_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1049 l_update_dest_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
1050 l_update_dest_instance_rec.quantity := l_dest_instance_header_tbl(i).quantity + abs(l_mtl_item_tbl(j).primary_quantity);
1051 l_update_dest_instance_rec.active_end_date := NULL;
1052 l_update_dest_instance_rec.object_version_number := l_dest_instance_header_tbl(i).object_version_number;
1053
1054 l_party_tbl.delete;
1055 l_account_tbl.delete;
1056 l_pricing_attrib_tbl.delete;
1057 l_org_assignments_tbl.delete;
1058 l_asset_assignment_tbl.delete;
1059
1060 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);
1061
1062 debug('Instance Status Id: '||l_update_dest_instance_rec.instance_status_id);
1063 debug('Before Update Item Instance - 23');
1064
1065 csi_item_instance_pub.update_item_instance(l_api_version,
1066 l_commit,
1067 l_init_msg_list,
1068 l_validation_level,
1069 l_update_dest_instance_rec,
1070 l_ext_attrib_values_tbl,
1071 l_party_tbl,
1072 l_account_tbl,
1073 l_pricing_attrib_tbl,
1074 l_org_assignments_tbl,
1075 l_asset_assignment_tbl,
1076 l_txn_rec,
1077 l_instance_id_lst,
1078 l_return_status,
1079 l_msg_count,
1080 l_msg_data);
1081
1082 l_upd_error_instance_id := NULL;
1083 l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
1084
1085 debug('After Update Item Instance - 24');
1086 debug('You are updating Instance: '||l_update_dest_instance_rec.instance_id);
1087 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1088
1089 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1090 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1091 debug('You encountered an error in the csi_item_instance_pub.c API '||l_msg_data);
1092 l_msg_index := 1;
1093 WHILE l_msg_count > 0 loop
1094 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1095 l_msg_index := l_msg_index + 1;
1096 l_msg_count := l_msg_count - 1;
1097 END LOOP;
1098 RAISE fnd_api.g_exc_error;
1099 END IF;
1100
1101 ELSIF l_dest_instance_header_tbl.count > 1 THEN
1102 -- Multiple Instances were found so throw error
1103 debug('Multiple Instances were Found in Install Base-30');
1104 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
1105 fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
1106 fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
1107 fnd_message.set_token('INV_ORG_ID',l_mtl_item_tbl(j).organization_id);
1108 fnd_message.set_token('LOCATOR',l_mtl_item_tbl(j).locator_id);
1109 l_error_message := fnd_message.get;
1110 RAISE fnd_api.g_exc_error;
1111
1112 END IF; -- End of Destination Record If
1113 END IF; -- End of Serial Control If
1114 END LOOP; -- End of For Loop
1115
1116 debug('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
1117 debug('******End of csi_inv_interorg_pkg.intransit_shipment Transaction******');
1118
1119 EXCEPTION
1120 WHEN fnd_api.g_exc_error THEN
1121 debug('You have encountered a "fnd_api.g_exc_error" exception in the Inter-Organization Transaction - In Transit Shipment');
1122 x_return_status := l_fnd_error;
1123
1124 IF l_mtl_item_tbl.count > 0 THEN
1125 x_trx_error_rec.serial_number := l_mtl_item_tbl(j).serial_number;
1126 x_trx_error_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1127 x_trx_error_rec.instance_id := l_upd_error_instance_id;
1128 x_trx_error_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1129 x_trx_error_rec.src_serial_num_ctrl_code := l_mtl_item_tbl(j).serial_number_control_code;
1130 x_trx_error_rec.src_location_ctrl_code := l_mtl_item_tbl(j).location_control_code;
1131 x_trx_error_rec.src_lot_ctrl_code := l_mtl_item_tbl(j).lot_control_code;
1132 x_trx_error_rec.src_rev_qty_ctrl_code := l_mtl_item_tbl(j).revision_qty_control_code;
1133 x_trx_error_rec.dst_serial_num_ctrl_code := r_item_control.serial_number_control_code;
1134 x_trx_error_rec.dst_location_ctrl_code := r_item_control.location_control_code;
1138 x_trx_error_rec.transaction_error_date := l_sysdate ;
1135 x_trx_error_rec.dst_lot_ctrl_code := r_item_control.lot_control_code;
1136 x_trx_error_rec.dst_rev_qty_ctrl_code := r_item_control.revision_qty_control_code;
1137 x_trx_error_rec.comms_nl_trackable_flag := l_mtl_item_tbl(j).comms_nl_trackable_flag;
1139 END IF;
1140
1141 x_trx_error_rec.error_text := l_error_message;
1142 x_trx_error_rec.transaction_id := NULL;
1143 x_trx_error_rec.source_type := 'CSIORGTS';
1144 x_trx_error_rec.source_id := p_transaction_id;
1145 x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
1146 x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
1147 x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
1148 x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
1149
1150 WHEN others THEN
1151 l_sql_error := SQLERRM;
1152
1153 debug('You have encountered a "when others" exception in the Inter-Organization Transaction - In Transit Shipment');
1154 debug('SQL Error: '||l_sql_error);
1155
1156 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
1157 fnd_message.set_token('API_NAME',l_api_name);
1158 fnd_message.set_token('SQL_ERROR',SQLERRM);
1159 x_return_status := l_fnd_unexpected;
1160
1161 IF l_mtl_item_tbl.count > 0 THEN
1162 x_trx_error_rec.serial_number := l_mtl_item_tbl(j).serial_number;
1163 x_trx_error_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1164 x_trx_error_rec.instance_id := l_upd_error_instance_id;
1165 x_trx_error_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1166 x_trx_error_rec.src_serial_num_ctrl_code := l_mtl_item_tbl(j).serial_number_control_code;
1167 x_trx_error_rec.src_location_ctrl_code := l_mtl_item_tbl(j).location_control_code;
1168 x_trx_error_rec.src_lot_ctrl_code := l_mtl_item_tbl(j).lot_control_code;
1169 x_trx_error_rec.src_rev_qty_ctrl_code := l_mtl_item_tbl(j).revision_qty_control_code;
1170 x_trx_error_rec.dst_serial_num_ctrl_code := r_item_control.serial_number_control_code;
1171 x_trx_error_rec.dst_location_ctrl_code := r_item_control.location_control_code;
1172 x_trx_error_rec.dst_lot_ctrl_code := r_item_control.lot_control_code;
1173 x_trx_error_rec.dst_rev_qty_ctrl_code := r_item_control.revision_qty_control_code;
1174 x_trx_error_rec.comms_nl_trackable_flag := l_mtl_item_tbl(j).comms_nl_trackable_flag;
1175 x_trx_error_rec.transaction_error_date := l_sysdate ;
1176 END IF;
1177
1178 x_trx_error_rec.error_text := fnd_message.get;
1179 x_trx_error_rec.transaction_id := NULL;
1180 x_trx_error_rec.source_type := 'CSIORGTS';
1181 x_trx_error_rec.source_id := p_transaction_id;
1182 x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
1183 x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
1184 x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
1185 x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
1186
1187 END intransit_shipment;
1188
1189
1190 PROCEDURE intransit_receipt(p_transaction_id IN NUMBER,
1191 p_message_id IN NUMBER,
1192 x_return_status OUT NOCOPY VARCHAR2,
1193 x_trx_error_rec OUT NOCOPY CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC)
1194 IS
1195
1196 l_mtl_item_tbl CSI_INV_TRXS_PKG.MTL_ITEM_TBL_TYPE;
1197 l_api_name VARCHAR2(100) := 'CSI_INV_INTERORG_PKG.INTRANSIT_RECEIPT';
1198 l_api_version NUMBER := 1.0;
1199 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1200 l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
1201 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
1202 l_active_instance_only VARCHAR2(10) := FND_API.G_TRUE;
1203 l_inactive_instance_only VARCHAR2(10) := FND_API.G_FALSE;
1204 l_transaction_id NUMBER := NULL;
1205 l_resolve_id_columns VARCHAR2(10) := FND_API.G_FALSE;
1206 l_object_version_number NUMBER := 1;
1207 l_sysdate DATE := SYSDATE;
1208 l_master_organization_id NUMBER;
1209 l_depreciable VARCHAR2(1);
1210 l_txn_error_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC;
1211 l_instance_query_rec CSI_DATASTRUCTURES_PUB.INSTANCE_QUERY_REC;
1212 l_dest_instance_query_rec CSI_DATASTRUCTURES_PUB.INSTANCE_QUERY_REC;
1213 l_update_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
1214 l_upd_src_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
1215 l_update_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
1216 l_new_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
1217 l_new_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
1218 l_new_src_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
1219 l_txn_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_REC;
1220 l_return_status VARCHAR2(1);
1221 l_error_code VARCHAR2(50);
1222 l_error_message VARCHAR2(4000);
1223 l_instance_id_lst CSI_DATASTRUCTURES_PUB.ID_TBL;
1224 l_party_query_rec CSI_DATASTRUCTURES_PUB.PARTY_QUERY_REC;
1225 l_account_query_rec CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_QUERY_REC;
1229 l_ext_attrib_values_tbl CSI_DATASTRUCTURES_PUB.EXTEND_ATTRIB_VALUES_TBL;
1226 l_instance_header_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_HEADER_TBL;
1227 l_src_instance_header_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_HEADER_TBL;
1228 l_dest_instance_header_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_HEADER_TBL;
1230 l_party_tbl CSI_DATASTRUCTURES_PUB.PARTY_TBL;
1231 l_account_tbl CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_TBL;
1232 l_pricing_attrib_tbl CSI_DATASTRUCTURES_PUB.PRICING_ATTRIBS_TBL;
1233 l_org_assignments_tbl CSI_DATASTRUCTURES_PUB.ORGANIZATION_UNITS_TBL;
1234 l_asset_assignment_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_ASSET_TBL;
1235 l_sub_inventory VARCHAR2(10);
1236 l_location_type VARCHAR2(20);
1237 l_trx_action_type VARCHAR2(50);
1238 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1239 l_fnd_warning VARCHAR2(1) := 'W';
1240 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
1241 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
1242 l_fnd_g_num NUMBER := FND_API.G_MISS_NUM;
1243 l_fnd_g_char VARCHAR2(1) := FND_API.G_MISS_CHAR;
1244 l_fnd_g_date DATE := FND_API.G_MISS_DATE;
1245 l_in_inventory VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_INVENTORY;
1246 l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
1247 l_out_of_enterprise VARCHAR2(25) := 'OUT_OF_ENTERPRISE';
1248 l_returned VARCHAR2(25) := 'RETURNED';
1249 l_instance_usage_code VARCHAR2(25);
1250 l_organization_id NUMBER;
1251 l_subinventory_name VARCHAR2(10);
1252 l_locator_id NUMBER;
1253 l_transaction_error_id NUMBER;
1254 l_trx_type_id NUMBER;
1255 l_mfg_serial_flag VARCHAR2(1);
1256 l_serial_number VARCHAR2(30);
1257 l_trans_type_code VARCHAR2(25);
1258 l_trans_app_code VARCHAR2(5);
1259 l_employee_id NUMBER;
1260 l_file VARCHAR2(500);
1261 l_msg_count NUMBER;
1262 l_msg_data VARCHAR2(2000);
1263 l_sql_error VARCHAR2(2000);
1264 l_msg_index NUMBER;
1265 j PLS_INTEGER;
1266 i PLS_INTEGER :=1;
1267 k PLS_INTEGER :=1;
1268 p PLS_INTEGER :=1;
1269 l_tbl_count NUMBER := 0;
1270 l_neg_code NUMBER := 0;
1271 l_instance_status VARCHAR2(1);
1272 l_inv_org_iso NUMBER;
1273 l_sr_control NUMBER := 0;
1274 l_12_loop NUMBER := 0;
1275 l_status VARCHAR2(50);
1276 l_ownership_party VARCHAR2(1);
1277 l_redeploy_flag VARCHAR2(1);
1278 l_upd_error_instance_id NUMBER := NULL;
1279
1280 l_instance_header_rec csi_datastructures_pub.instance_header_rec;
1281 l_party_header_tbl csi_datastructures_pub.party_header_tbl;
1282 l_account_header_tbl csi_datastructures_pub.party_account_header_tbl;
1283 l_org_header_tbl csi_datastructures_pub.org_units_header_tbl;
1284 l_pricing_header_tbl csi_datastructures_pub.pricing_attribs_tbl;
1285 l_ext_attrib_header_tbl csi_datastructures_pub.extend_attrib_values_tbl;
1286 l_ext_attrib_def_tbl csi_datastructures_pub.extend_attrib_tbl;
1287 l_asset_header_tbl csi_datastructures_pub.instance_asset_header_tbl;
1288
1289 cursor c_id is
1290 SELECT instance_status_id
1291 FROM csi_instance_statuses
1292 WHERE name = FND_PROFILE.VALUE('CSI_DEFAULT_INSTANCE_STATUS');
1293
1294 r_id c_id%rowtype;
1295
1296 CURSOR c_item_control (pc_item_id in number,
1297 pc_org_id in number) is
1298 SELECT serial_number_control_code,
1299 lot_control_code,
1300 revision_qty_control_code,
1301 location_control_code,
1302 comms_nl_trackable_flag
1303 FROM mtl_system_items_b
1304 WHERE inventory_item_id = pc_item_id
1305 AND organization_id = pc_org_id;
1306
1307 r_item_control c_item_control%rowtype;
1308
1309 BEGIN
1310
1311 x_return_status := l_fnd_success;
1312 l_error_message := NULL;
1313
1314 debug('******Start of csi_inv_interorg_pkg.intransit_receipt Transaction procedure******');
1315 debug('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
1316 debug('csiorgtb.pls 115.23');
1317 debug('Transaction ID with is: '||p_transaction_id);
1318
1319 -- This procedure queries all of the Inventory Transaction Records and
1320 -- returns them as a table.
1321
1322 csi_inv_trxs_pkg.get_transaction_recs(p_transaction_id,
1323 l_mtl_item_tbl,
1324 l_return_status,
1325 l_error_message);
1326
1327 l_tbl_count := 0;
1328 l_tbl_count := l_mtl_item_tbl.count;
1329
1330 debug('Inventory Records Found: '||l_tbl_count);
1331
1332 IF NOT l_return_status = l_fnd_success THEN
1333 debug('You have encountered an error in CSI_INV_TRXS_PKG.get_transaction_recs, Transaction ID: '||p_transaction_id);
1334 RAISE fnd_api.g_exc_error;
1335 END IF;
1336
1340
1337 debug('Transaction Action ID: '||l_mtl_item_tbl(i).transaction_action_id);
1338 debug('Transaction Source Type ID: '||l_mtl_item_tbl(i).transaction_source_type_id);
1339 debug('Transaction Quantity: '||l_mtl_item_tbl(i).transaction_quantity);
1341 -- Get the Master Organization ID
1342 csi_inv_trxs_pkg.get_master_organization(l_mtl_item_tbl(i).organization_id,
1343 l_master_organization_id,
1344 l_return_status,
1345 l_error_message);
1346
1347 IF NOT l_return_status = l_fnd_success THEN
1348 debug('You have encountered an error in csi_inv_trxs_pkg.get_master_organization, Organization ID: '||l_mtl_item_tbl(i).organization_id);
1349 RAISE fnd_api.g_exc_error;
1350 END IF;
1351
1352 -- Call get_fnd_employee_id and get the employee id
1353 l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
1354
1355 IF l_employee_id = -1 THEN
1356 debug('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
1357 END IF;
1358
1359 debug('The Employee that is processing this Transaction is: '||l_employee_id);
1360
1361 -- See if this is a depreciable Item to set the status of the transaction record
1362 csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
1363 l_depreciable);
1364
1365 debug('Is this Item ID: '||l_mtl_item_tbl(i).inventory_item_id||', Depreciable :'||l_depreciable);
1366
1367 -- Get Party ownership Flag
1368 l_ownership_party := csi_datastructures_pub.g_install_param_rec.ownership_override_at_txn;
1369
1370 debug('Ownership Party FLag is: '||l_ownership_party)
1371 ;
1372
1373 -- Determine Transaction Type for this
1374
1375 l_trans_type_code := 'INTERORG_TRANS_RECEIPT';
1376 l_trans_app_code := 'INV';
1377
1378 debug('Trans Type Code: '||l_trans_type_code);
1379 debug('Trans App Code: '||l_trans_app_code);
1380
1381 -- Initialize Transaction Record
1382 l_txn_rec := csi_inv_trxs_pkg.init_txn_rec;
1383
1384 -- Set Status based on redeployment
1385 IF l_depreciable = 'N' THEN
1386 IF l_mtl_item_tbl(i).serial_number is NOT NULL THEN
1387 csi_inv_trxs_pkg.get_redeploy_flag(l_mtl_item_tbl(i).inventory_item_id,
1388 l_mtl_item_tbl(i).serial_number,
1389 l_sysdate,
1390 l_redeploy_flag,
1391 l_return_status,
1392 l_error_message);
1393 END IF;
1394 IF l_redeploy_flag = 'Y' THEN
1395 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
1396 ELSE
1397 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_complete;
1398 END IF;
1399 ELSE
1400 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
1401 END IF;
1402
1403 IF NOT l_return_status = l_fnd_success THEN
1404 debug('Redeploy Flag: '||l_redeploy_flag);
1405 debug('You have encountered an error in csi_inv_trxs_pkg.get_redeploy_flag: '||l_error_message);
1406 RAISE fnd_api.g_exc_error;
1407 END IF;
1408
1409 debug('Redeploy Flag: '||l_redeploy_flag);
1410 debug('Trans Status Code: '||l_txn_rec.transaction_status_code);
1411
1412 -- Create CSI Transaction to be used
1413 l_txn_rec.source_transaction_date := l_mtl_item_tbl(i).transaction_date;
1414 l_txn_rec.transaction_date := l_sysdate;
1415 l_txn_rec.transaction_type_id :=
1416 csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
1417 l_txn_rec.transaction_quantity :=
1418 l_mtl_item_tbl(i).transaction_quantity;
1419 l_txn_rec.transaction_uom_code := l_mtl_item_tbl(i).transaction_uom;
1420 l_txn_rec.transacted_by := l_employee_id;
1421 l_txn_rec.transaction_action_code := NULL;
1422 l_txn_rec.message_id := p_message_id;
1423 l_txn_rec.inv_material_transaction_id := p_transaction_id;
1424 l_txn_rec.object_version_number := l_object_version_number;
1425 l_txn_rec.source_line_ref := l_mtl_item_tbl(i).shipment_number;
1426
1427 csi_inv_trxs_pkg.create_csi_txn(l_txn_rec,
1428 l_error_message,
1429 l_return_status);
1430
1431 debug('CSI Transaction Created: '||l_txn_rec.transaction_id);
1432
1433 IF NOT l_return_status = l_fnd_success THEN
1434 debug('You have encountered an error in csi_inv_trxs_pkg.create_csi_txn: '||p_transaction_id);
1435 RAISE fnd_api.g_exc_error;
1436 END IF;
1437
1438 -- Now loop through the PL/SQL Table.
1439 j := 1;
1440
1441 debug('Starting to loop through Material Transaction Records');
1442
1443 -- Get Default Profile Instance Status
1444
1445 OPEN c_id;
1446 FETCH c_id into r_id;
1447 CLOSE c_id;
1448
1449 debug('Default Profile Status: '||r_id.instance_status_id);
1450
1451 l_neg_code := csi_inv_trxs_pkg.get_neg_inv_code(l_mtl_item_tbl(i).organization_id); --fix for bug5704500/bug6036067
1452 debug('Negative Code is - 1 = Yes, 2 = No: '||l_neg_code); --fix for bug6036067
1453
1454 FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
1455
1459 ELSE
1456 IF (l_neg_code = 1 AND l_mtl_item_tbl(j).serial_number_control_code in (1,6)) OR
1457 (l_mtl_item_tbl(j).serial_number_control_code in (2,5)) THEN
1458 l_instance_status := FND_API.G_FALSE;
1460 l_instance_status := FND_API.G_TRUE;
1461 END IF;
1462
1463 debug('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
1464 debug('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
1465 debug('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
1466 debug('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
1467 debug('Organization ID: '||l_mtl_item_tbl(j).organization_id);
1468 debug('Transfer Org ID: '||l_mtl_item_tbl(j).transfer_organization_id);
1469
1470 -- Get Shipping Organization Item Master Controls
1471 OPEN c_item_control (l_mtl_item_tbl(j).inventory_item_id,
1472 l_mtl_item_tbl(j).transfer_organization_id);
1473 FETCH c_item_control into r_item_control;
1474 CLOSE c_item_control;
1475
1476 l_sr_control := r_item_control.serial_number_control_code;
1477
1478 debug('Serial Number : '||l_mtl_item_tbl(j).serial_number);
1479 debug('l_sr_control is: '||l_sr_control);
1480 debug('Serial Number Control Code: '||l_mtl_item_tbl(j).serial_number_control_code);
1481 debug('Receiving Org Serial Number Control Code: '||l_mtl_item_tbl(j).serial_number_control_code);
1482 debug('Shipping Org Serial Number Control Code: '||r_item_control.serial_number_control_code);
1483 debug('Receiving Org Lot Control Code: '||l_mtl_item_tbl(j).lot_control_code);
1484 debug('Shipping Org Lot Control Code: '||r_item_control.lot_control_code);
1485 debug('Receiving Org Loction Control Code: '||l_mtl_item_tbl(j).location_control_code);
1486 debug('Shipping Org Location Control Code: '||r_item_control.location_control_code);
1487 debug('Receiving Org Revision Control Code: '||l_mtl_item_tbl(j).revision_qty_control_code);
1488 debug('Shipping Org Revision Control Code: '||r_item_control.revision_qty_control_code);
1489 debug('Shipping Org Trackable Flag: '||r_item_control.comms_nl_trackable_flag);
1490 debug('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
1491
1492 l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
1493 l_instance_usage_code := l_fnd_g_char;
1494
1495 IF (l_mtl_item_tbl(j).serial_number_control_code = 6 AND
1496 l_sr_control in (2,5)) THEN
1497 --In Transit Receipt
1498 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1499 --l_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
1500 l_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).transfer_organization_id;
1501 l_instance_query_rec.serial_number := l_fnd_g_char;
1502 l_instance_query_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
1503 l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1504 l_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1505 l_instance_query_rec.instance_usage_code := l_in_transit;
1506 l_trx_action_type := 'IN_TRANSIT_RECEIPT';
1507 l_instance_usage_code := l_instance_query_rec.instance_usage_code;
1508
1509 debug('Set Serial Number to G MISS');
1510
1511 ELSIF (l_mtl_item_tbl(j).serial_number_control_code = 1 AND
1512 l_sr_control in (2,5)) OR
1513 (l_mtl_item_tbl(j).serial_number_control_code in (2,5) AND
1514 l_sr_control in (6,1)) THEN
1515 --In Transit Receipt
1516 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1517 --l_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
1518 --l_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).transfer_organization_id;
1519 l_instance_query_rec.inv_organization_id := NULL;
1520 l_instance_query_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
1521 l_instance_query_rec.in_transit_order_line_id := NULL;
1522 l_instance_query_rec.serial_number := NULL;
1523 l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1524 l_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1525 l_instance_query_rec.instance_usage_code := l_in_transit;
1526 l_trx_action_type := 'IN_TRANSIT_RECEIPT';
1527 l_instance_usage_code := l_instance_query_rec.instance_usage_code;
1528
1529 debug('Set Serial Number to NULL-1');
1530
1531 ELSIF (l_mtl_item_tbl(j).serial_number_control_code in (6,1) AND
1532 l_sr_control in (6,1)) THEN
1533 --In Transit Receipt
1534 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1535 --l_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
1536 --l_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).transfer_organization_id;
1537 l_instance_query_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
1538 l_instance_query_rec.in_transit_order_line_id := NULL;
1539 l_instance_query_rec.serial_number := NULL;
1540 l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1544 l_instance_usage_code := l_instance_query_rec.instance_usage_code;
1541 l_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1542 l_instance_query_rec.instance_usage_code := l_in_transit;
1543 l_trx_action_type := 'IN_TRANSIT_RECEIPT';
1545
1546 debug('Set Serial Number NULL-1.1');
1547
1548 ELSIF (l_mtl_item_tbl(j).serial_number_control_code in (2,5) AND
1549 l_sr_control in (2,5)) THEN
1550 --In Transit Receipt
1551 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1552 l_instance_query_rec.serial_number := l_mtl_item_tbl(j).serial_number;
1553 l_trx_action_type := 'IN_TRANSIT_RECEIPT';
1554 l_instance_usage_code := l_instance_query_rec.instance_usage_code;
1555
1556 debug('Set Serial Number to the serial number from Inv');
1557
1558 END IF;
1559
1560
1561 debug('l_12_loop is:'|| l_12_loop);
1562 debug('If Count is 1 then bypass Get Item Instance');
1563
1564 IF l_12_loop = 0 THEN
1565
1566 debug('Transaction Action Type:'|| l_trx_action_type);
1567 debug('Before Get Item Instance - 1');
1568
1569 csi_item_instance_pub.get_item_instances(l_api_version,
1570 l_commit,
1571 l_init_msg_list,
1572 l_validation_level,
1573 l_instance_query_rec,
1574 l_party_query_rec,
1575 l_account_query_rec,
1576 l_transaction_id,
1577 l_resolve_id_columns,
1578 l_instance_status,
1579 l_src_instance_header_tbl,
1580 l_return_status,
1581 l_msg_count,
1582 l_msg_data);
1583 END IF; -- End of l_12_loop IF
1584
1585 debug('After Get Item Instance - 2');
1586
1587 l_tbl_count := 0;
1588 l_tbl_count := l_src_instance_header_tbl.count;
1589
1590 debug('Source Records Found: '||l_tbl_count);
1591
1592 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1593 IF NOT l_return_status = l_fnd_success then
1594 debug('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
1595 l_msg_index := 1;
1596 WHILE l_msg_count > 0 loop
1597 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1598 l_msg_index := l_msg_index + 1;
1599 l_msg_count := l_msg_count - 1;
1600 END LOOP;
1601 RAISE fnd_api.g_exc_error;
1602 END IF;
1603
1604 IF l_src_instance_header_tbl.count > 0 OR
1605 l_12_loop = 1 THEN -- Installed Base Records Found
1606
1607 debug('Records exists so now check both Shipping and Rec Serial Control');
1608
1609 IF l_mtl_item_tbl(j).serial_number_control_code in (2,5) AND
1610 l_sr_control in (2,5) THEN
1611
1612 debug('Serial Control at Shipping and Receiving are both 2,5');
1613 debug('Instance being updated: '||l_src_instance_header_tbl(i).instance_id);
1614
1615 l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
1616 -- Added for Bug 5975739
1617 l_update_instance_rec.inv_master_organization_id := l_master_organization_id;
1618 l_update_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
1619 l_update_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
1620 l_update_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
1621 l_update_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
1622 l_update_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
1623 l_update_instance_rec.in_transit_order_line_id := NULL;
1624 l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
1625 l_update_instance_rec.instance_usage_code := l_in_inventory;
1626 l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
1627 --Code start for bug 6137231--
1628 IF r_item_control.lot_control_code = 2 AND l_mtl_item_tbl(j).lot_control_code = 1 THEN
1629 l_update_instance_rec.lot_number := NULL;
1630 debug('Lot control 2 and 1');
1631 ELSIF r_item_control.lot_control_code = 2 AND l_mtl_item_tbl(j).lot_control_code = 2 THEN
1632 l_update_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1633 debug('Lot control 2 and 2');
1634 ELSIF r_item_control.lot_control_code = 1 AND l_mtl_item_tbl(j).lot_control_code = 2 THEN
1635 l_update_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1636 debug('Lot control 1 and 2');
1637 END IF;---lot check
1638 --Code end for bug 6137231--
1639
1643
1640 debug('After you initialize the Update Record Values - 2');
1641
1642 debug('After you initialize the Transaction Record Values - 3');
1644 l_party_tbl.delete;
1645 l_account_tbl.delete;
1646 l_pricing_attrib_tbl.delete;
1647 l_org_assignments_tbl.delete;
1648 l_asset_assignment_tbl.delete;
1649
1650 debug('Before Update Item Instance - 4');
1651
1652 csi_item_instance_pub.update_item_instance(l_api_version,
1653 l_commit,
1654 l_init_msg_list,
1655 l_validation_level,
1656 l_update_instance_rec,
1657 l_ext_attrib_values_tbl,
1658 l_party_tbl,
1659 l_account_tbl,
1660 l_pricing_attrib_tbl,
1661 l_org_assignments_tbl,
1662 l_asset_assignment_tbl,
1663 l_txn_rec,
1664 l_instance_id_lst,
1665 l_return_status,
1666 l_msg_count,
1667 l_msg_data);
1668
1669 l_upd_error_instance_id := NULL;
1670 l_upd_error_instance_id := l_update_instance_rec.instance_id;
1671
1672 debug('After Update Item Instance - 5');
1673 debug('You are updating Instance: '||l_update_instance_rec.instance_id);
1674 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1675
1676 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1677 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1678 debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
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
1689 ELSIF l_mtl_item_tbl(j).serial_number_control_code in (2,5) AND
1690 l_sr_control in (1,6) THEN
1691
1692 l_12_loop := 1;
1693
1694 debug('Setting l_12_loop: '||l_12_loop);
1695 debug('Serial Control at Shipping is 1,6 and Receiving is 2,5');
1696
1697 IF j = 1 THEN -- Update Source Since its Non Serialized 1 Time
1698
1699 debug('Update Source 1 time with Transaction Quantity');
1700 debug('Instance being updated: '||l_src_instance_header_tbl(i).instance_id);
1701
1702 l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1703 l_upd_src_dest_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
1704 l_upd_src_dest_instance_rec.quantity := l_src_instance_header_tbl(i).quantity - abs(l_mtl_item_tbl(j).primary_quantity);
1705 l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
1706
1707 l_party_tbl.delete;
1708 l_account_tbl.delete;
1709 l_pricing_attrib_tbl.delete;
1710 l_org_assignments_tbl.delete;
1711 l_asset_assignment_tbl.delete;
1712
1713 debug('Before Update Item Instance - 6');
1714
1715 csi_item_instance_pub.update_item_instance(l_api_version,
1716 l_commit,
1717 l_init_msg_list,
1718 l_validation_level,
1719 l_upd_src_dest_instance_rec,
1720 l_ext_attrib_values_tbl,
1721 l_party_tbl,
1722 l_account_tbl,
1723 l_pricing_attrib_tbl,
1724 l_org_assignments_tbl,
1725 l_asset_assignment_tbl,
1726 l_txn_rec,
1727 l_instance_id_lst,
1728 l_return_status,
1729 l_msg_count,
1730 l_msg_data);
1731
1732 l_upd_error_instance_id := NULL;
1733 l_upd_error_instance_id := l_upd_src_dest_instance_rec.instance_id;
1734
1735 debug('After Update Item Instance - 7');
1736 debug('You are updating Instance: '||l_upd_src_dest_instance_rec.instance_id);
1737 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1738
1739 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1740 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1744 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1741 debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
1742 l_msg_index := 1;
1743 WHILE l_msg_count > 0 loop
1745 l_msg_index := l_msg_index + 1;
1746 l_msg_count := l_msg_count - 1;
1747 END LOOP;
1748 RAISE fnd_api.g_exc_error;
1749 END IF;
1750 END IF; -- End of J = 1 If to update Source 1 time
1751
1752 -- Now Query for Dest Serialized Instances and Update (Unexpire)/ Create Instances
1753 -- New Code added JPW
1754 l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
1755 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1756 --l_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1757 --l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1758 l_instance_query_rec.serial_number := l_mtl_item_tbl(j).serial_number;
1759 --l_instance_query_rec.instance_usage_code := l_in_inventory;
1760 --l_instance_query_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
1761 --l_instance_query_rec.inv_subinventory_name := NULL;
1762 --l_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
1763 --l_instance_query_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
1764 l_instance_usage_code := l_in_inventory;
1765 l_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
1766 l_organization_id := l_mtl_item_tbl(j).organization_id;
1767 l_locator_id := l_mtl_item_tbl(j).locator_id;
1768
1769 debug('Item ID: '||l_instance_query_rec.inventory_item_id);
1770 debug('Revision: '||l_instance_query_rec.inventory_revision);
1771 debug('Lot Number: '||l_instance_query_rec.lot_number);
1772 debug('Serial Number: '||l_instance_query_rec.serial_number);
1773 debug('Sub Inv: '||l_instance_query_rec.inv_subinventory_name);
1774 debug('Org ID: '||l_instance_query_rec.inv_organization_id);
1775 debug('Locator ID: '||l_instance_query_rec.inv_locator_id);
1776
1777 debug('Before Get Dest Item Instance - 8');
1778
1779 csi_item_instance_pub.get_item_instances(l_api_version,
1780 l_commit,
1781 l_init_msg_list,
1782 l_validation_level,
1783 l_instance_query_rec,
1784 l_party_query_rec,
1785 l_account_query_rec,
1786 l_transaction_id,
1787 l_resolve_id_columns,
1788 l_inactive_instance_only,
1789 l_dest_instance_header_tbl,
1790 l_return_status,
1791 l_msg_count,
1792 l_msg_data);
1793
1794 debug('After Get Item Instance - 9');
1795
1796 l_tbl_count := 0;
1797 l_tbl_count := l_dest_instance_header_tbl.count;
1798
1799 debug('Source Records Found: '||l_tbl_count);
1800
1801 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1802 IF NOT l_return_status = l_fnd_success then
1803 debug('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
1804 l_msg_index := 1;
1805 WHILE l_msg_count > 0 loop
1806 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1807 l_msg_index := l_msg_index + 1;
1808 l_msg_count := l_msg_count - 1;
1809 END LOOP;
1810 RAISE fnd_api.g_exc_error;
1811 END IF;
1812
1813 IF l_dest_instance_header_tbl.count < 1 THEN -- Installed Base Destination Records are not found so create a new record
1814
1815 l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
1816 l_new_dest_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1817 l_new_dest_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1818 l_new_dest_instance_rec.inv_master_organization_id := l_master_organization_id;
1819 l_new_dest_instance_rec.mfg_serial_number_flag := 'Y';
1820 l_new_dest_instance_rec.serial_number := l_mtl_item_tbl(j).serial_number;
1821 l_new_dest_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1822 l_new_dest_instance_rec.quantity := 1;
1823 l_new_dest_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
1824 l_new_dest_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
1828 l_new_dest_instance_rec.vld_organization_id := l_organization_id;
1825 l_new_dest_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
1826 l_new_dest_instance_rec.instance_usage_code := l_instance_usage_code;
1827 l_new_dest_instance_rec.inv_organization_id := l_organization_id;
1829 l_new_dest_instance_rec.inv_subinventory_name := l_subinventory_name;
1830 l_new_dest_instance_rec.inv_locator_id := l_locator_id;
1831 l_new_dest_instance_rec.customer_view_flag := 'N';
1832 l_new_dest_instance_rec.merchant_view_flag := 'Y';
1833 l_new_dest_instance_rec.operational_status_code := 'NOT_USED';
1834 l_new_dest_instance_rec.object_version_number := l_object_version_number;
1835 l_new_dest_instance_rec.active_start_date := l_sysdate;
1836 l_new_dest_instance_rec.active_end_date := NULL;
1837
1838 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
1839 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
1840 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
1841 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
1842 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
1843 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
1844
1845 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);
1846
1847 debug('Instance Status Id: '||l_new_dest_instance_rec.instance_status_id);
1848 debug('Before Create Item Instance - 10');
1849
1850 csi_item_instance_pub.create_item_instance(l_api_version,
1851 l_commit,
1852 l_init_msg_list,
1853 l_validation_level,
1854 l_new_dest_instance_rec,
1855 l_ext_attrib_values_tbl,
1856 l_party_tbl,
1857 l_account_tbl,
1858 l_pricing_attrib_tbl,
1859 l_org_assignments_tbl,
1860 l_asset_assignment_tbl,
1861 l_txn_rec,
1862 l_return_status,
1863 l_msg_count,
1864 l_msg_data);
1865
1866 debug('After Create Item Instance - 11');
1867 debug('You are Creating Instance: '||l_new_dest_instance_rec.instance_id);
1868
1869 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1870
1871 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1872 debug('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
1873 l_msg_index := 1;
1874 WHILE l_msg_count > 0 loop
1875 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1876 l_msg_index := l_msg_index + 1;
1877 l_msg_count := l_msg_count - 1;
1878 END LOOP;
1879 RAISE fnd_api.g_exc_error;
1880 END IF;
1881
1882 ELSIF l_dest_instance_header_tbl.count > 0 THEN
1883 IF l_dest_instance_header_tbl(i).instance_usage_code in (l_in_inventory,l_in_transit,l_returned) THEN -- Installed Base Destination Records Found
1884
1885 l_update_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1886 l_update_dest_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
1887 l_update_dest_instance_rec.quantity := 1;
1888 l_update_dest_instance_rec.active_end_date := NULL;
1889 l_update_dest_instance_rec.object_version_number := l_dest_instance_header_tbl(i).object_version_number;
1890
1891 -- START OF NEW CODE
1892 --Added for Bug 5975739
1893 l_update_dest_instance_rec.inv_master_organization_id := l_master_organization_id;
1894 l_update_dest_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
1895 l_update_dest_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
1896 l_update_dest_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
1897 l_update_dest_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1898 l_update_dest_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
1899 l_update_dest_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
1900 l_update_dest_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
1901 l_update_dest_instance_rec.instance_usage_code := l_in_inventory;
1902
1903 debug('Setting In Transit Serialized Instance to be IN INVENTORY usage');
1907 debug('Subinv Code: '||l_update_dest_instance_rec.inv_subinventory_name);
1904 debug('Usage: '||l_update_dest_instance_rec.instance_usage_code);
1905 debug('VLD Org: '||l_update_dest_instance_rec.vld_organization_id);
1906 debug('INV Org: '||l_update_dest_instance_rec.inv_organization_id);
1908
1909 -- END OF NEW CODE
1910 l_party_tbl.delete;
1911 l_account_tbl.delete;
1912 l_pricing_attrib_tbl.delete;
1913 l_org_assignments_tbl.delete;
1914 l_asset_assignment_tbl.delete;
1915
1916 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);
1917
1918 debug('Instance Status Id: '||l_update_dest_instance_rec.instance_status_id);
1919 debug('Before Update Item Instance - 13');
1920
1921 csi_item_instance_pub.update_item_instance(l_api_version,
1922 l_commit,
1923 l_init_msg_list,
1924 l_validation_level,
1925 l_update_dest_instance_rec,
1926 l_ext_attrib_values_tbl,
1927 l_party_tbl,
1928 l_account_tbl,
1929 l_pricing_attrib_tbl,
1930 l_org_assignments_tbl,
1931 l_asset_assignment_tbl,
1932 l_txn_rec,
1933 l_instance_id_lst,
1934 l_return_status,
1935 l_msg_count,
1936 l_msg_data);
1937
1938 l_upd_error_instance_id := NULL;
1939 l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
1940
1941 debug('After Update Item Instance - 14');
1942 debug('You are updating Instance: '||l_update_dest_instance_rec.instance_id);
1943 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1944
1945 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1946 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1947 debug('You encountered an error in the csi_item_instance_pub.c API '||l_msg_data);
1948 l_msg_index := 1;
1949 WHILE l_msg_count > 0 loop
1950 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1951 l_msg_index := l_msg_index + 1;
1952 l_msg_count := l_msg_count - 1;
1953 END LOOP;
1954 RAISE fnd_api.g_exc_error;
1955 END IF;
1956
1957 ELSIF l_dest_instance_header_tbl(i).instance_usage_code = l_out_of_enterprise THEN
1958
1959 IF l_ownership_party = 'Y' THEN
1960 debug('Update Serialized Item which is :'||l_dest_instance_header_tbl(i).instance_usage_code);
1961 debug('Serial Number is: '||l_dest_instance_header_tbl(i).serial_number);
1962
1963 l_update_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1964 l_update_dest_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
1965 l_update_dest_instance_rec.quantity := 1;
1966 l_update_dest_instance_rec.active_end_date := NULL;
1967 l_update_dest_instance_rec.object_version_number := l_dest_instance_header_tbl(i).object_version_number;
1968
1969 -- START OF NEW CODE
1970 -- Added for Bug 5975739
1971 l_update_dest_instance_rec.inv_master_organization_id := l_master_organization_id;
1972 l_update_dest_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
1973 l_update_dest_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
1974 l_update_dest_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
1975 l_update_dest_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1976 l_update_dest_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
1977 l_update_dest_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
1978 l_update_dest_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
1979 l_update_dest_instance_rec.instance_usage_code := l_in_inventory;
1980
1981 debug('Setting OUT OF ENTERPRISE Serialized Instance to be IN INVENTORY usage because ownership flag is set to Y');
1982 debug('Usage: '||l_update_dest_instance_rec.instance_usage_code);
1983 debug('VLD Org: '||l_update_dest_instance_rec.vld_organization_id);
1984 debug('INV Org: '||l_update_dest_instance_rec.inv_organization_id);
1985 debug('Subinv Code: '||l_update_dest_instance_rec.inv_subinventory_name);
1986
1987 -- END OF NEW CODE
1988
1989 -- We want to change the party of this back
1990 -- to the Internal Party
1991
1992 debug('Usage is Out of Enterprise So we need to bring this back into Inventory and change the Owner Party back to the Internal Party');
1993
1994 -- Set Instance ID so it will query the child recs for this
1995 -- Instance.
1996
2000 (l_api_version,
1997 l_instance_header_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
1998 -- Call details to get Party Information
1999 csi_item_instance_pub.get_item_instance_details
2001 l_commit,
2002 l_init_msg_list,
2003 l_validation_level,
2004 l_instance_header_rec,
2005 fnd_api.g_true, -- Get Parties
2006 l_party_header_tbl,
2007 fnd_api.g_false, -- Get Accounts
2008 l_account_header_tbl,
2009 fnd_api.g_false, -- Get Org Assi.
2010 l_org_header_tbl,
2011 fnd_api.g_false, -- Get Price Att
2012 l_pricing_header_tbl,
2013 fnd_api.g_false, -- Get Ext Attr
2014 l_ext_attrib_header_tbl,
2015 l_ext_attrib_def_tbl,
2016 fnd_api.g_false, -- Get Asset Assi
2017 l_asset_header_tbl,
2018 fnd_api.g_false, -- Resolve IDs
2019 NULL, -- Time Stamp
2020 l_return_status,
2021 l_msg_count,
2022 l_msg_data);
2023
2024 -- Now create a new owner record that will be used to create
2025 -- the new owner party and set it back to an internal party owner
2026 -- The PL/SQL Table will now be set so that it can be passed into
2027 -- the next procedure.
2028
2029 FOR p in l_party_header_tbl.FIRST .. l_party_header_tbl.LAST LOOP
2030 IF l_party_header_tbl(p).relationship_type_code = 'OWNER' THEN
2031
2032 debug('Found the OWNER party so updating this back to the Internal Party ID');
2033
2034 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
2035 l_party_tbl(i).instance_id := l_dest_instance_header_tbl(i).instance_id;
2036 l_party_tbl(i).instance_party_id := l_party_header_tbl(p).instance_party_id;
2037 l_party_tbl(i).object_version_number := l_party_header_tbl(p).object_version_number;
2038
2039 debug('After finding the OWNER party and updating this back to the Internal Party ID');
2040
2041 END IF;
2042 END LOOP;
2043
2044 debug('Inst Party ID :'||l_party_tbl(i).instance_party_id);
2045 debug('Party Inst ID :'||l_party_tbl(i).instance_id);
2046 debug('Party Source Table :'||l_party_tbl(i).party_source_table);
2047 debug('Party ID :'||l_party_tbl(i).party_id);
2048 debug('Rel Type Code :'||l_party_tbl(i).relationship_type_code);
2049 debug('Contact Flag :'||l_party_tbl(i).contact_flag);
2050 debug('Object Version Number:' ||l_party_tbl(i).object_version_number);
2051
2052 l_account_tbl.delete;
2053 l_pricing_attrib_tbl.delete;
2054 l_org_assignments_tbl.delete;
2055 l_asset_assignment_tbl.delete;
2056
2057
2058 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);
2059
2060 debug('Instance Status Id: '||l_update_dest_instance_rec.instance_status_id);
2061 debug('Before Update Item Instance - 13');
2062
2063 csi_item_instance_pub.update_item_instance(l_api_version,
2064 l_commit,
2065 l_init_msg_list,
2066 l_validation_level,
2067 l_update_dest_instance_rec,
2068 l_ext_attrib_values_tbl,
2069 l_party_tbl,
2070 l_account_tbl,
2071 l_pricing_attrib_tbl,
2072 l_org_assignments_tbl,
2073 l_asset_assignment_tbl,
2074 l_txn_rec,
2075 l_instance_id_lst,
2076 l_return_status,
2077 l_msg_count,
2078 l_msg_data);
2079
2080 l_upd_error_instance_id := NULL;
2081 l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
2082
2083 debug('After update of Out of Enterprise Item Instance');
2084 debug('You are updating Instance: '||l_update_dest_instance_rec.instance_id);
2085 debug('After Update Item Instance - 14');
2086 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2087
2091 l_msg_index := 1;
2088 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2089 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2090 debug('You encountered an error in the csi_item_instance_pub.c API '||l_msg_data);
2092 WHILE l_msg_count > 0 loop
2093 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2094 l_msg_index := l_msg_index + 1;
2095 l_msg_count := l_msg_count - 1;
2096 END LOOP;
2097 RAISE fnd_api.g_exc_error;
2098 END IF;
2099 ELSE
2100 l_status := 'In Inventory, Out of Service or Out of Enterprise';
2101 debug('Serialized Item with Out of Enterprise exists however the ownership_override_at_txn flag is set to N so we will NOT bring this back into inventory');
2102 debug('Instance Usage Code is: '||l_dest_instance_header_tbl(i).instance_usage_code);
2103 fnd_message.set_name('CSI','CSI_SERIALIZED_ITEM_EXISTS');
2104 fnd_message.set_token('STATUS',l_status);
2105 l_error_message := fnd_message.get;
2106 l_return_status := l_fnd_error;
2107 RAISE fnd_api.g_exc_error;
2108 END IF;
2109
2110 ELSE
2111 l_status := 'IN_INVENTORY OR IN_TRANSIT';
2112 debug('Serialized Item with a usage other then IN INVENTORY or IN TRANSIT exists.');
2113 debug('Instance Usage Code is: '||l_dest_instance_header_tbl(i).instance_usage_code);
2114 fnd_message.set_name('CSI','CSI_SERIALIZED_ITEM_EXISTS');
2115 fnd_message.set_token('STATUS',l_status);
2116 l_error_message := fnd_message.get;
2117 RAISE fnd_api.g_exc_error;
2118 END IF;
2119 END IF; -- End of Destination Record If
2120
2121 ELSIF l_mtl_item_tbl(j).serial_number_control_code = 1 AND
2122 l_sr_control in (2,5) THEN
2123
2124 debug('Serial Control at Shipping is 2,5 and Receiving is 1');
2125 debug('Subtract Trans Qty from Existing Quantity First');
2126 debug('Instance being updated: '||l_src_instance_header_tbl(k).instance_id);
2127
2128 l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2129 l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
2130 l_update_instance_rec.quantity := l_src_instance_header_tbl(i).quantity - abs(l_mtl_item_tbl(j).primary_quantity);
2131 l_update_instance_rec.active_end_date := l_sysdate;
2132 l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
2133
2134 debug('After you initialize the Update Record Values');
2135 debug('Instance Updated: '||l_update_instance_rec.instance_id);
2136 debug('End Date Passed in: '||to_char(l_update_instance_rec.active_end_date,'DD-MON-YYYY HH24:MI:SS'));
2137 debug('Object Version: '||l_update_instance_rec.object_version_number);
2138 debug('After you initialize the Update Record Values - 19');
2139
2140 l_party_tbl.delete;
2141 l_account_tbl.delete;
2142 l_pricing_attrib_tbl.delete;
2143 l_org_assignments_tbl.delete;
2144 l_asset_assignment_tbl.delete;
2145
2146 debug('Before Update Item Instance - 21');
2147
2148 csi_item_instance_pub.update_item_instance(l_api_version,
2149 l_commit,
2150 l_init_msg_list,
2151 l_validation_level,
2152 l_update_instance_rec,
2153 l_ext_attrib_values_tbl,
2154 l_party_tbl,
2155 l_account_tbl,
2156 l_pricing_attrib_tbl,
2157 l_org_assignments_tbl,
2158 l_asset_assignment_tbl,
2159 l_txn_rec,
2160 l_instance_id_lst,
2161 l_return_status,
2162 l_msg_count,
2163 l_msg_data);
2164
2165 l_upd_error_instance_id := NULL;
2166 l_upd_error_instance_id := l_update_instance_rec.instance_id;
2167
2168 debug('After Update Item Instance - 22');
2169 debug('You are updating Instance: '||l_update_instance_rec.instance_id);
2170 debug('You are updating Serial Number: '||l_update_instance_rec.serial_number);
2171 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2172
2173 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2174 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2175 debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
2176 l_msg_index := 1;
2177 WHILE l_msg_count > 0 loop
2178 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2179 l_msg_index := l_msg_index + 1;
2180 l_msg_count := l_msg_count - 1;
2184
2181 END LOOP;
2182 RAISE fnd_api.g_exc_error;
2183 END IF;
2185 -- Now Query for Non Serialized In Inventory Record 1 Time Only
2186 debug('J is 1 so query for Non Serialized item 1 time - 23');
2187
2188 l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
2189 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2190 l_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
2191 l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2192 l_instance_query_rec.serial_number := NULL;
2193 l_instance_query_rec.instance_usage_code := l_in_inventory;
2194 l_instance_query_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
2195 l_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
2196 l_instance_query_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
2197 l_instance_usage_code := l_instance_query_rec.instance_usage_code;
2198 l_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
2199 l_organization_id := l_mtl_item_tbl(j).organization_id;
2200 l_locator_id := l_mtl_item_tbl(j).locator_id;
2201
2202 debug('Before Get Dest Item Instance - 24');
2203
2204 csi_item_instance_pub.get_item_instances(l_api_version,
2205 l_commit,
2206 l_init_msg_list,
2207 l_validation_level,
2208 l_instance_query_rec,
2209 l_party_query_rec,
2210 l_account_query_rec,
2211 l_transaction_id,
2212 l_resolve_id_columns,
2213 l_inactive_instance_only,
2214 l_dest_instance_header_tbl,
2215 l_return_status,
2216 l_msg_count,
2217 l_msg_data);
2218
2219 debug('After Get Item Instance - 25');
2220
2221 l_tbl_count := 0;
2222 l_tbl_count := l_dest_instance_header_tbl.count;
2223
2224 debug('Source Records Found: '||l_tbl_count);
2225
2226 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2227 IF NOT l_return_status = l_fnd_success then
2228 debug('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
2229 l_msg_index := 1;
2230 WHILE l_msg_count > 0 loop
2231 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2232 l_msg_index := l_msg_index + 1;
2233 l_msg_count := l_msg_count - 1;
2234 END LOOP;
2235 RAISE fnd_api.g_exc_error;
2236 END IF;
2237
2238 IF l_dest_instance_header_tbl.count = 0 THEN -- Installed Base Destination Records are not found so create a new record
2239
2240 l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
2241 l_new_dest_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2242 l_new_dest_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
2243 l_new_dest_instance_rec.inv_master_organization_id := l_master_organization_id;
2244 l_new_dest_instance_rec.mfg_serial_number_flag := 'N';
2245 l_new_dest_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2246 l_new_dest_instance_rec.quantity := abs(l_mtl_item_tbl(j).transaction_quantity);
2247 l_new_dest_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
2248 l_new_dest_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
2249 l_new_dest_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
2250 l_new_dest_instance_rec.instance_usage_code := l_instance_usage_code;
2251 l_new_dest_instance_rec.inv_organization_id := l_organization_id;
2252 l_new_dest_instance_rec.vld_organization_id := l_organization_id;
2253 l_new_dest_instance_rec.inv_subinventory_name := l_subinventory_name;
2254 l_new_dest_instance_rec.inv_locator_id := l_locator_id;
2255 l_new_dest_instance_rec.customer_view_flag := 'N';
2256 l_new_dest_instance_rec.merchant_view_flag := 'Y';
2257 l_new_dest_instance_rec.operational_status_code := 'NOT_USED';
2258 l_new_dest_instance_rec.object_version_number := l_object_version_number;
2259 l_new_dest_instance_rec.active_start_date := l_sysdate;
2260 l_new_dest_instance_rec.active_end_date := NULL;
2261
2262 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
2266 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2263 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
2264 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
2265 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
2267 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2268
2269 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);
2270
2271 debug('Instance Status Id: '||l_new_dest_instance_rec.instance_status_id);
2272 debug('Before Create Item Instance - 26');
2273
2274 csi_item_instance_pub.create_item_instance(l_api_version,
2275 l_commit,
2276 l_init_msg_list,
2277 l_validation_level,
2278 l_new_dest_instance_rec,
2279 l_ext_attrib_values_tbl,
2280 l_party_tbl,
2281 l_account_tbl,
2282 l_pricing_attrib_tbl,
2283 l_org_assignments_tbl,
2284 l_asset_assignment_tbl,
2285 l_txn_rec,
2286 l_return_status,
2287 l_msg_count,
2288 l_msg_data);
2289
2290 debug('After Create Item Instance - 27');
2291 debug('You are Creating Instance: '||l_new_dest_instance_rec.instance_id);
2292
2293 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2294 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2295 debug('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
2296 l_msg_index := 1;
2297 WHILE l_msg_count > 0 loop
2298 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2299 l_msg_index := l_msg_index + 1;
2300 l_msg_count := l_msg_count - 1;
2301 END LOOP;
2302 RAISE fnd_api.g_exc_error;
2303 END IF;
2304
2305 ELSIF l_dest_instance_header_tbl.count = 1 THEN -- Installed Base Destination Records Found
2306
2307 l_update_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2308 l_update_dest_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
2309 l_update_dest_instance_rec.quantity := l_dest_instance_header_tbl(i).quantity + abs(l_mtl_item_tbl(j).primary_quantity);
2310 l_update_dest_instance_rec.active_end_date := NULL;
2311 l_update_dest_instance_rec.object_version_number := l_dest_instance_header_tbl(i).object_version_number;
2312
2313 l_party_tbl.delete;
2314 l_account_tbl.delete;
2315 l_pricing_attrib_tbl.delete;
2316 l_org_assignments_tbl.delete;
2317 l_asset_assignment_tbl.delete;
2318
2319 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);
2320
2321 debug('Instance Status Id: '||l_update_dest_instance_rec.instance_status_id);
2322 debug('Before Update Item Instance - 29');
2323 debug('Transaction Type ID: '||l_txn_rec.transaction_type_id);
2324
2325 csi_item_instance_pub.update_item_instance(l_api_version,
2326 l_commit,
2327 l_init_msg_list,
2328 l_validation_level,
2329 l_update_dest_instance_rec,
2330 l_ext_attrib_values_tbl,
2331 l_party_tbl,
2332 l_account_tbl,
2333 l_pricing_attrib_tbl,
2334 l_org_assignments_tbl,
2335 l_asset_assignment_tbl,
2336 l_txn_rec,
2337 l_instance_id_lst,
2338 l_return_status,
2339 l_msg_count,
2340 l_msg_data);
2341
2342 l_upd_error_instance_id := NULL;
2343 l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
2344
2345 debug('After Update Item Instance - 30');
2346 debug('You are updating Instance: '||l_update_dest_instance_rec.instance_id);
2347 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2348
2352 l_msg_index := 1;
2349 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2350 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2351 debug('You encountered an error in the csi_item_instance_pub.c API '||l_msg_data);
2353 WHILE l_msg_count > 0 loop
2354 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2355 l_msg_index := l_msg_index + 1;
2356 l_msg_count := l_msg_count - 1;
2357 END LOOP;
2358 RAISE fnd_api.g_exc_error;
2359 END IF;
2360
2361 ELSIF l_dest_instance_header_tbl.count > 1 THEN
2362 -- Multiple Instances were found so throw error
2363 debug('Multiple Instances were Found in Install Base-30');
2364 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
2365 fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
2366 fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
2367 fnd_message.set_token('INV_ORG_ID',l_mtl_item_tbl(j).organization_id);
2368 fnd_message.set_token('LOCATOR',l_mtl_item_tbl(j).locator_id);
2369 l_error_message := fnd_message.get;
2370 RAISE fnd_api.g_exc_error;
2371
2372 END IF; -- End of Destination Record If
2373
2374 ELSIF l_mtl_item_tbl(j).serial_number_control_code = 6 AND
2375 l_sr_control in (2,5) THEN
2376
2377 FOR k in l_src_instance_header_tbl.FIRST .. l_mtl_item_tbl(j).primary_quantity LOOP
2378 debug('Serial Control at Shipping is 2,5 and Receiving is 1,6');
2379 debug('Expire The Serialized Instance First');
2380 debug('Instance being updated: '||l_src_instance_header_tbl(k).instance_id);
2381
2382 l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2383 l_update_instance_rec.instance_id := l_src_instance_header_tbl(k).instance_id;
2384 l_update_instance_rec.active_end_date := l_sysdate;
2385 l_update_instance_rec.object_version_number := l_src_instance_header_tbl(k).object_version_number;
2386
2387 debug('After you initialize the Update Record Values');
2388 debug('Instance Updated: '||l_update_instance_rec.instance_id);
2389 debug('End Date Passed in: '||to_char(l_update_instance_rec.active_end_date,'DD-MON-YYYY HH24:MI:SS'));
2390 debug('Object Version: '||l_update_instance_rec.object_version_number);
2391 debug('After you initialize the Update Record Values - 19');
2392
2393 l_party_tbl.delete;
2394 l_account_tbl.delete;
2395 l_pricing_attrib_tbl.delete;
2396 l_org_assignments_tbl.delete;
2397 l_asset_assignment_tbl.delete;
2398
2399 debug('Before Update Item Instance - 21');
2400
2401 csi_item_instance_pub.update_item_instance(l_api_version,
2402 l_commit,
2403 l_init_msg_list,
2404 l_validation_level,
2405 l_update_instance_rec,
2406 l_ext_attrib_values_tbl,
2407 l_party_tbl,
2408 l_account_tbl,
2409 l_pricing_attrib_tbl,
2410 l_org_assignments_tbl,
2411 l_asset_assignment_tbl,
2412 l_txn_rec,
2413 l_instance_id_lst,
2414 l_return_status,
2415 l_msg_count,
2416 l_msg_data);
2417
2418 l_upd_error_instance_id := NULL;
2419 l_upd_error_instance_id := l_update_instance_rec.instance_id;
2420
2421 debug('After Update Item Instance - 22');
2422 debug('You are updating Instance: '||l_update_instance_rec.instance_id);
2423 debug('You are updating Serial Number: '||l_update_instance_rec.serial_number);
2424 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2425
2426 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2427 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2428 debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
2429 l_msg_index := 1;
2430 WHILE l_msg_count > 0 loop
2431 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2432 l_msg_index := l_msg_index + 1;
2433 l_msg_count := l_msg_count - 1;
2434 END LOOP;
2435 RAISE fnd_api.g_exc_error;
2436 END IF;
2437 END LOOP; -- End Header Tbl and Trans Qty Loop
2438
2439 -- Now Query for Non Serialized In Inventory Record 1 Time Only
2440 IF j = 1 THEN
2441 debug('J is 1 so query for Non Serialized item 1 time - 23');
2442
2443 l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
2444 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2448 l_instance_query_rec.instance_usage_code := l_in_inventory;
2445 l_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
2446 l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2447 l_instance_query_rec.serial_number := NULL;
2449 l_instance_query_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
2450 l_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
2451 l_instance_query_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
2452 l_instance_usage_code := l_instance_query_rec.instance_usage_code;
2453 l_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
2454 l_organization_id := l_mtl_item_tbl(j).organization_id;
2455 l_locator_id := l_mtl_item_tbl(j).locator_id;
2456
2457 debug('Before Get Dest Item Instance - 24');
2458
2459 csi_item_instance_pub.get_item_instances(l_api_version,
2460 l_commit,
2461 l_init_msg_list,
2462 l_validation_level,
2463 l_instance_query_rec,
2464 l_party_query_rec,
2465 l_account_query_rec,
2466 l_transaction_id,
2467 l_resolve_id_columns,
2468 l_inactive_instance_only,
2469 l_dest_instance_header_tbl,
2470 l_return_status,
2471 l_msg_count,
2472 l_msg_data);
2473
2474 debug('After Get Item Instance - 25');
2475
2476 l_tbl_count := 0;
2477 l_tbl_count := l_dest_instance_header_tbl.count;
2478
2479 debug('Source Records Found: '||l_tbl_count);
2480
2481 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2482 IF NOT l_return_status = l_fnd_success then
2483 debug('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
2484 l_msg_index := 1;
2485 WHILE l_msg_count > 0 loop
2486 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2487 l_msg_index := l_msg_index + 1;
2488 l_msg_count := l_msg_count - 1;
2489 END LOOP;
2490 RAISE fnd_api.g_exc_error;
2491 END IF;
2492
2493 IF l_dest_instance_header_tbl.count = 0 THEN -- Installed Base Destination Records are not found so create a new record
2494
2495 l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
2496 l_new_dest_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2497 l_new_dest_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
2498 l_new_dest_instance_rec.inv_master_organization_id := l_master_organization_id;
2499 l_new_dest_instance_rec.mfg_serial_number_flag := 'N';
2500 l_new_dest_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2501 l_new_dest_instance_rec.quantity := abs(l_mtl_item_tbl(j).transaction_quantity);
2502 l_new_dest_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
2503 l_new_dest_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
2504 l_new_dest_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
2505 l_new_dest_instance_rec.instance_usage_code := l_instance_usage_code;
2506 l_new_dest_instance_rec.inv_organization_id := l_organization_id;
2507 l_new_dest_instance_rec.vld_organization_id := l_organization_id;
2508 l_new_dest_instance_rec.inv_subinventory_name := l_subinventory_name;
2509 l_new_dest_instance_rec.inv_locator_id := l_locator_id;
2510 l_new_dest_instance_rec.customer_view_flag := 'N';
2511 l_new_dest_instance_rec.merchant_view_flag := 'Y';
2512 l_new_dest_instance_rec.operational_status_code := 'NOT_USED';
2513 l_new_dest_instance_rec.object_version_number := l_object_version_number;
2514 l_new_dest_instance_rec.active_start_date := l_sysdate;
2515 l_new_dest_instance_rec.active_end_date := NULL;
2516
2517 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
2518 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
2519 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
2520 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
2521 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2525
2522 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2523
2524 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);
2526 debug('Instance Status Id: '||l_new_dest_instance_rec.instance_status_id);
2527 debug('Before Create Item Instance - 26');
2528
2529 csi_item_instance_pub.create_item_instance(l_api_version,
2530 l_commit,
2531 l_init_msg_list,
2532 l_validation_level,
2533 l_new_dest_instance_rec,
2534 l_ext_attrib_values_tbl,
2535 l_party_tbl,
2536 l_account_tbl,
2537 l_pricing_attrib_tbl,
2538 l_org_assignments_tbl,
2539 l_asset_assignment_tbl,
2540 l_txn_rec,
2541 l_return_status,
2542 l_msg_count,
2543 l_msg_data);
2544
2545 debug('After Create Item Instance - 27');
2546 debug('You are Creating Instance: '||l_new_dest_instance_rec.instance_id);
2547
2548 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2549 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2550 debug('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
2551 l_msg_index := 1;
2552 WHILE l_msg_count > 0 loop
2553 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2554 l_msg_index := l_msg_index + 1;
2555 l_msg_count := l_msg_count - 1;
2556 END LOOP;
2557 RAISE fnd_api.g_exc_error;
2558 END IF;
2559
2560 ELSIF l_dest_instance_header_tbl.count = 1 THEN -- Installed Base Destination Records Found
2561
2562 l_update_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2563 l_update_dest_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
2564 l_update_dest_instance_rec.quantity := l_dest_instance_header_tbl(i).quantity + abs(l_mtl_item_tbl(j).primary_quantity);
2565 l_update_dest_instance_rec.active_end_date := NULL;
2566 l_update_dest_instance_rec.object_version_number := l_dest_instance_header_tbl(i).object_version_number;
2567
2568 l_party_tbl.delete;
2569 l_account_tbl.delete;
2570 l_pricing_attrib_tbl.delete;
2571 l_org_assignments_tbl.delete;
2572 l_asset_assignment_tbl.delete;
2573
2574 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);
2575
2576 debug('Instance Status Id: '||l_update_dest_instance_rec.instance_status_id);
2577 debug('Before Update Item Instance - 29');
2578 debug('Transaction Type ID: '||l_txn_rec.transaction_type_id);
2579
2580 csi_item_instance_pub.update_item_instance(l_api_version,
2581 l_commit,
2582 l_init_msg_list,
2583 l_validation_level,
2584 l_update_dest_instance_rec,
2585 l_ext_attrib_values_tbl,
2586 l_party_tbl,
2587 l_account_tbl,
2588 l_pricing_attrib_tbl,
2589 l_org_assignments_tbl,
2590 l_asset_assignment_tbl,
2591 l_txn_rec,
2592 l_instance_id_lst,
2593 l_return_status,
2594 l_msg_count,
2595 l_msg_data);
2596
2597 l_upd_error_instance_id := NULL;
2598 l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
2599
2600 debug('After Update Item Instance - 30');
2601 debug('You are updating Instance: '||l_update_dest_instance_rec.instance_id);
2602 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2603
2604 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2605 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2606 debug('You encountered an error in the csi_item_instance_pub.c API '||l_msg_data);
2607 l_msg_index := 1;
2608 WHILE l_msg_count > 0 loop
2612 END LOOP;
2609 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2610 l_msg_index := l_msg_index + 1;
2611 l_msg_count := l_msg_count - 1;
2613 RAISE fnd_api.g_exc_error;
2614 END IF;
2615
2616 ELSIF l_dest_instance_header_tbl.count > 1 THEN
2617 -- Multiple Instances were found so throw error
2618 debug('Multiple Instances were Found in Install Base-30');
2619 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
2620 fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
2621 fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
2622 fnd_message.set_token('INV_ORG_ID',l_mtl_item_tbl(j).organization_id);
2623 fnd_message.set_token('LOCATOR',l_mtl_item_tbl(j).locator_id);
2624 l_error_message := fnd_message.get;
2625 RAISE fnd_api.g_exc_error;
2626
2627 END IF; -- End of Destination Record If
2628 END IF; -- End of J Index Loop
2629
2630 ELSIF l_mtl_item_tbl(j).serial_number_control_code in (1,6) AND
2631 l_sr_control in (1,6) THEN
2632
2633 debug('Serial Control at Shipping and Receiving are both 1,6');
2634 debug('Instance being updated: '||l_src_instance_header_tbl(i).instance_id);
2635
2636 l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2637 l_upd_src_dest_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
2638 l_upd_src_dest_instance_rec.quantity := l_src_instance_header_tbl(i).quantity - abs(l_mtl_item_tbl(j).primary_quantity);
2639 l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
2640
2641 l_party_tbl.delete;
2642 l_account_tbl.delete;
2643 l_pricing_attrib_tbl.delete;
2644 l_org_assignments_tbl.delete;
2645 l_asset_assignment_tbl.delete;
2646
2647 debug('Before Update Item Instance - 35');
2648
2649 csi_item_instance_pub.update_item_instance(l_api_version,
2650 l_commit,
2651 l_init_msg_list,
2652 l_validation_level,
2653 l_upd_src_dest_instance_rec,
2654 l_ext_attrib_values_tbl,
2655 l_party_tbl,
2656 l_account_tbl,
2657 l_pricing_attrib_tbl,
2658 l_org_assignments_tbl,
2659 l_asset_assignment_tbl,
2660 l_txn_rec,
2661 l_instance_id_lst,
2662 l_return_status,
2663 l_msg_count,
2664 l_msg_data);
2665
2666 l_upd_error_instance_id := NULL;
2667 l_upd_error_instance_id := l_upd_src_dest_instance_rec.instance_id;
2668
2669 debug('After Update Item Instance - 36');
2670 debug('You are updating Instance: '||l_upd_src_dest_instance_rec.instance_id);
2671 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2672
2673 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2674 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2675 debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
2676 l_msg_index := 1;
2677 WHILE l_msg_count > 0 loop
2678 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2679 l_msg_index := l_msg_index + 1;
2680 l_msg_count := l_msg_count - 1;
2681 END LOOP;
2682 RAISE fnd_api.g_exc_error;
2683 END IF;
2684
2685 -- Get Destination Record
2686
2687 l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
2688 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2689 l_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
2690 l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2691 l_instance_query_rec.serial_number := NULL;
2692 l_instance_query_rec.instance_usage_code := l_in_inventory;
2693 l_instance_query_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
2694 l_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
2695 l_instance_query_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
2696 l_instance_usage_code := l_instance_query_rec.instance_usage_code;
2697 l_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
2698 l_organization_id := l_mtl_item_tbl(j).organization_id;
2699 l_locator_id := l_mtl_item_tbl(j).locator_id;
2700
2701 debug('Before Get Dest Item Instance - 37');
2702
2706 l_validation_level,
2703 csi_item_instance_pub.get_item_instances(l_api_version,
2704 l_commit,
2705 l_init_msg_list,
2707 l_instance_query_rec,
2708 l_party_query_rec,
2709 l_account_query_rec,
2710 l_transaction_id,
2711 l_resolve_id_columns,
2712 l_inactive_instance_only,
2713 l_dest_instance_header_tbl,
2714 l_return_status,
2715 l_msg_count,
2716 l_msg_data);
2717
2718 debug('After Get Item Instance - 38');
2719
2720 l_tbl_count := 0;
2721 l_tbl_count := l_dest_instance_header_tbl.count;
2722
2723 debug('Source Records Found: '||l_tbl_count);
2724
2725 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2726 IF NOT l_return_status = l_fnd_success then
2727 debug('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
2728 l_msg_index := 1;
2729 WHILE l_msg_count > 0 loop
2730 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2731 l_msg_index := l_msg_index + 1;
2732 l_msg_count := l_msg_count - 1;
2733 END LOOP;
2734 RAISE fnd_api.g_exc_error;
2735 END IF;
2736
2737 IF l_dest_instance_header_tbl.count = 0 THEN -- Installed Base Destination Records are not found so create a new record
2738
2739 l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
2740 l_new_dest_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2741 l_new_dest_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
2742 l_new_dest_instance_rec.inv_master_organization_id := l_master_organization_id;
2743 l_new_dest_instance_rec.mfg_serial_number_flag := 'N';
2744 l_new_dest_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2745 l_new_dest_instance_rec.quantity := abs(l_mtl_item_tbl(j).transaction_quantity);
2746 l_new_dest_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
2747 l_new_dest_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
2748 l_new_dest_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
2749 l_new_dest_instance_rec.instance_usage_code := l_instance_usage_code;
2750 l_new_dest_instance_rec.inv_organization_id := l_organization_id;
2751 l_new_dest_instance_rec.vld_organization_id := l_organization_id;
2752 l_new_dest_instance_rec.inv_subinventory_name := l_subinventory_name;
2753 l_new_dest_instance_rec.inv_locator_id := l_locator_id;
2754 l_new_dest_instance_rec.customer_view_flag := 'N';
2755 l_new_dest_instance_rec.merchant_view_flag := 'Y';
2756 l_new_dest_instance_rec.operational_status_code := 'NOT_USED';
2757 l_new_dest_instance_rec.object_version_number := l_object_version_number;
2758 l_new_dest_instance_rec.active_start_date := l_sysdate;
2759 l_new_dest_instance_rec.active_end_date := NULL;
2760
2761 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
2762 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
2763 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
2764 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
2765 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2766 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2767
2768 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);
2769
2770 debug('Instance Status Id: '||l_new_dest_instance_rec.instance_status_id);
2771 debug('Before Create Item Instance - 39');
2772
2773 csi_item_instance_pub.create_item_instance(l_api_version,
2774 l_commit,
2775 l_init_msg_list,
2776 l_validation_level,
2777 l_new_dest_instance_rec,
2778 l_ext_attrib_values_tbl,
2779 l_party_tbl,
2780 l_account_tbl,
2781 l_pricing_attrib_tbl,
2785 l_return_status,
2782 l_org_assignments_tbl,
2783 l_asset_assignment_tbl,
2784 l_txn_rec,
2786 l_msg_count,
2787 l_msg_data);
2788
2789 debug('After Create Item Instance - 40');
2790 debug('You are Creating Instance: '||l_new_dest_instance_rec.instance_id);
2791
2792 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2793 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2794 debug('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
2795 l_msg_index := 1;
2796 WHILE l_msg_count > 0 loop
2797 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2798 l_msg_index := l_msg_index + 1;
2799 l_msg_count := l_msg_count - 1;
2800 END LOOP;
2801 RAISE fnd_api.g_exc_error;
2802 END IF;
2803
2804 ELSIF l_dest_instance_header_tbl.count = 1 THEN -- Installed Base Destination Records Found
2805
2806 l_update_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2807 l_update_dest_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
2808 l_update_dest_instance_rec.quantity := l_dest_instance_header_tbl(i).quantity + abs(l_mtl_item_tbl(j).primary_quantity);
2809 l_update_dest_instance_rec.active_end_date := NULL;
2810 l_update_dest_instance_rec.object_version_number := l_dest_instance_header_tbl(i).object_version_number;
2811
2812 l_party_tbl.delete;
2813 l_account_tbl.delete;
2814 l_pricing_attrib_tbl.delete;
2815 l_org_assignments_tbl.delete;
2816 l_asset_assignment_tbl.delete;
2817
2818 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);
2819
2820 debug('Instance Status Id: '||l_update_dest_instance_rec.instance_status_id);
2821 debug('Before Update Item Instance - 42');
2822
2823 csi_item_instance_pub.update_item_instance(l_api_version,
2824 l_commit,
2825 l_init_msg_list,
2826 l_validation_level,
2827 l_update_dest_instance_rec,
2828 l_ext_attrib_values_tbl,
2829 l_party_tbl,
2830 l_account_tbl,
2831 l_pricing_attrib_tbl,
2832 l_org_assignments_tbl,
2833 l_asset_assignment_tbl,
2834 l_txn_rec,
2835 l_instance_id_lst,
2836 l_return_status,
2837 l_msg_count,
2838 l_msg_data);
2839
2840 l_upd_error_instance_id := NULL;
2841 l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
2842
2843 debug('After Update Item Instance - 43');
2844 debug('You are updating Instance: '||l_update_dest_instance_rec.instance_id);
2845 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2846
2847 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2848 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2849 debug('You encountered an error in the csi_item_instance_pub.c API '||l_msg_data);
2850 l_msg_index := 1;
2851 WHILE l_msg_count > 0 loop
2852 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2853 l_msg_index := l_msg_index + 1;
2854 l_msg_count := l_msg_count - 1;
2855 END LOOP;
2856 RAISE fnd_api.g_exc_error;
2857 END IF;
2858
2859 ELSIF l_dest_instance_header_tbl.count > 1 THEN
2860 -- Multiple Instances were found so throw error
2861 debug('Multiple Instances were Found in Install Base-30');
2862 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
2863 fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
2864 fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
2865 fnd_message.set_token('INV_ORG_ID',l_mtl_item_tbl(j).organization_id);
2866 fnd_message.set_token('LOCATOR',l_mtl_item_tbl(j).locator_id);
2867 l_error_message := fnd_message.get;
2868 RAISE fnd_api.g_exc_error;
2869 END IF; -- End of Destination Record If
2870
2871 ELSE -- No Records Found So throw Error
2872 debug('No Records were found in Install Base - 48');
2873
2877 fnd_message.set_token('ORG_ID',l_mtl_item_tbl(j).organization_id);
2874 fnd_message.set_name('CSI','CSI_IB_RECORD_NOTFOUND');
2875 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
2876 fnd_message.set_token('SUBINVENTORY',l_mtl_item_tbl(j).subinventory_code);
2878 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
2879 l_error_message := fnd_message.get;
2880 RAISE fnd_api.g_exc_error;
2881
2882 END IF; -- Serial Control IF
2883
2884 ELSE
2885 debug('No Records were found in Install Base - 49');
2886
2887 fnd_message.set_name('CSI','CSI_IB_RECORD_NOTFOUND');
2888 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
2889 fnd_message.set_token('SUBINVENTORY',l_mtl_item_tbl(j).subinventory_code);
2890 fnd_message.set_token('ORG_ID',l_mtl_item_tbl(j).organization_id);
2891 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
2892 l_error_message := fnd_message.get;
2893 RAISE fnd_api.g_exc_error;
2894
2895 END IF; -- End of Main Source Header Tbl IF
2896 END LOOP; -- End of For Loop
2897
2898
2899 debug('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2900 debug('******End of csi_inv_interorg_pkg.intransit_receipt Transaction******');
2901
2902 EXCEPTION
2903 WHEN fnd_api.g_exc_error THEN
2904 debug('You have encountered a "fnd_api.g_exc_error" exception in the Inter-Organization Transaction In Transit Receipt');
2905 x_return_status := l_fnd_error;
2906
2907 IF l_mtl_item_tbl.count > 0 THEN
2908 x_trx_error_rec.serial_number := l_mtl_item_tbl(j).serial_number;
2909 x_trx_error_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2910 x_trx_error_rec.instance_id := l_upd_error_instance_id;
2911 x_trx_error_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2912 x_trx_error_rec.dst_serial_num_ctrl_code := l_mtl_item_tbl(j).serial_number_control_code;
2913 x_trx_error_rec.dst_location_ctrl_code := l_mtl_item_tbl(j).location_control_code;
2914 x_trx_error_rec.dst_lot_ctrl_code := l_mtl_item_tbl(j).lot_control_code;
2915 x_trx_error_rec.dst_rev_qty_ctrl_code := l_mtl_item_tbl(j).revision_qty_control_code;
2916 x_trx_error_rec.src_serial_num_ctrl_code := r_item_control.serial_number_control_code;
2917 x_trx_error_rec.src_location_ctrl_code := r_item_control.location_control_code;
2918 x_trx_error_rec.src_lot_ctrl_code := r_item_control.lot_control_code;
2919 x_trx_error_rec.src_rev_qty_ctrl_code := r_item_control.revision_qty_control_code;
2920 x_trx_error_rec.comms_nl_trackable_flag := l_mtl_item_tbl(j).comms_nl_trackable_flag;
2921 x_trx_error_rec.transaction_error_date := l_sysdate ;
2922 END IF;
2923
2924 x_trx_error_rec.error_text := l_error_message;
2925 x_trx_error_rec.transaction_id := NULL;
2926 x_trx_error_rec.source_type := 'CSIORGTR';
2927 x_trx_error_rec.source_id := p_transaction_id;
2928 x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
2929 x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
2930 x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
2931 x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
2932
2933 WHEN others THEN
2934 l_sql_error := SQLERRM;
2935 debug('You have encountered a "when others" exception in the Inter-Organization Transaction In Transit Receipt');
2936 debug('SQL Error: '||l_sql_error);
2937 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
2938 fnd_message.set_token('API_NAME',l_api_name);
2939 fnd_message.set_token('SQL_ERROR',SQLERRM);
2940 x_return_status := l_fnd_unexpected;
2941
2942 IF l_mtl_item_tbl.count > 0 THEN
2943 x_trx_error_rec.serial_number := l_mtl_item_tbl(j).serial_number;
2944 x_trx_error_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2945 x_trx_error_rec.instance_id := l_upd_error_instance_id;
2946 x_trx_error_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2947 x_trx_error_rec.dst_serial_num_ctrl_code := l_mtl_item_tbl(j).serial_number_control_code;
2948 x_trx_error_rec.dst_location_ctrl_code := l_mtl_item_tbl(j).location_control_code;
2949 x_trx_error_rec.dst_lot_ctrl_code := l_mtl_item_tbl(j).lot_control_code;
2950 x_trx_error_rec.dst_rev_qty_ctrl_code := l_mtl_item_tbl(j).revision_qty_control_code;
2951 x_trx_error_rec.src_serial_num_ctrl_code := r_item_control.serial_number_control_code;
2952 x_trx_error_rec.src_location_ctrl_code := r_item_control.location_control_code;
2953 x_trx_error_rec.src_lot_ctrl_code := r_item_control.lot_control_code;
2954 x_trx_error_rec.src_rev_qty_ctrl_code := r_item_control.revision_qty_control_code;
2955 x_trx_error_rec.comms_nl_trackable_flag := l_mtl_item_tbl(j).comms_nl_trackable_flag;
2956 x_trx_error_rec.transaction_error_date := l_sysdate ;
2957 END IF;
2958
2959 x_trx_error_rec.error_text := fnd_message.get;
2960 x_trx_error_rec.transaction_id := NULL;
2961 x_trx_error_rec.source_type := 'CSIORGTR';
2962 x_trx_error_rec.source_id := p_transaction_id;
2963 x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
2964 x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
2965 x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
2969
2966 x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
2967
2968 END intransit_receipt;
2970
2971 PROCEDURE direct_shipment(p_transaction_id IN NUMBER,
2972 p_message_id IN NUMBER,
2973 x_return_status OUT NOCOPY VARCHAR2,
2974 x_trx_error_rec OUT NOCOPY CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC)
2975 IS
2976
2977 l_mtl_item_tbl CSI_INV_TRXS_PKG.MTL_ITEM_TBL_TYPE;
2978 l_api_name VARCHAR2(100) := 'CSI_INV_INTERORG_PKG.DIRECT_SHIPMENT';
2979 l_api_version NUMBER := 1.0;
2980 l_commit VARCHAR2(1) := FND_API.G_FALSE;
2981 l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
2982 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
2983 l_active_instance_only VARCHAR2(10) := FND_API.G_TRUE;
2984 l_inactive_instance_only VARCHAR2(10) := FND_API.G_FALSE;
2985 l_transaction_id NUMBER := NULL;
2986 l_resolve_id_columns VARCHAR2(10) := FND_API.G_FALSE;
2987 l_object_version_number NUMBER := 1;
2988 l_sysdate DATE := SYSDATE;
2989 l_master_organization_id NUMBER;
2990 l_depreciable VARCHAR2(1);
2991 l_txn_error_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC;
2992 l_instance_query_rec CSI_DATASTRUCTURES_PUB.INSTANCE_QUERY_REC;
2993 l_dest_instance_query_rec CSI_DATASTRUCTURES_PUB.INSTANCE_QUERY_REC;
2994 l_update_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
2995 l_upd_src_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
2996 l_update_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
2997 l_new_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
2998 l_new_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
2999 l_new_src_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
3000 l_txn_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_REC;
3001 l_return_status VARCHAR2(1);
3002 l_error_code VARCHAR2(50);
3003 l_error_message VARCHAR2(4000);
3004 l_instance_id_lst CSI_DATASTRUCTURES_PUB.ID_TBL;
3005 l_party_query_rec CSI_DATASTRUCTURES_PUB.PARTY_QUERY_REC;
3006 l_account_query_rec CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_QUERY_REC;
3007 l_instance_header_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_HEADER_TBL;
3008 l_src_instance_header_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_HEADER_TBL;
3009 l_dest_instance_header_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_HEADER_TBL;
3010 l_ext_attrib_values_tbl CSI_DATASTRUCTURES_PUB.EXTEND_ATTRIB_VALUES_TBL;
3011 l_party_tbl CSI_DATASTRUCTURES_PUB.PARTY_TBL;
3012 l_account_tbl CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_TBL;
3013 l_pricing_attrib_tbl CSI_DATASTRUCTURES_PUB.PRICING_ATTRIBS_TBL;
3014 l_org_assignments_tbl CSI_DATASTRUCTURES_PUB.ORGANIZATION_UNITS_TBL;
3015 l_asset_assignment_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_ASSET_TBL;
3016 l_sub_inventory VARCHAR2(10);
3017 l_location_type VARCHAR2(20);
3018 l_trx_action_type VARCHAR2(50);
3019 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3020 l_fnd_warning VARCHAR2(1) := 'W';
3021 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
3022 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
3023 l_in_inventory VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_INVENTORY;
3024 l_in_process VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_PROCESS;
3025 l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
3026 l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
3027 l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
3028 l_installed VARCHAR2(25) := CSI_INV_TRXS_PKG.G_INSTALLED;
3029 l_fnd_g_num NUMBER := FND_API.G_MISS_NUM;
3030 l_fnd_g_char VARCHAR2(1) := FND_API.G_MISS_CHAR;
3031 l_fnd_g_date DATE := FND_API.G_MISS_DATE;
3032 l_instance_usage_code VARCHAR2(25);
3033 l_organization_id NUMBER;
3034 l_subinventory_name VARCHAR2(10);
3035 l_locator_id NUMBER;
3036 l_transaction_error_id NUMBER;
3037 l_quantity NUMBER;
3038 l_trx_type_id NUMBER;
3039 l_mfg_serial_flag VARCHAR2(1);
3040 l_serial_number VARCHAR2(30);
3041 l_trans_type_code VARCHAR2(25);
3042 l_trans_app_code VARCHAR2(5);
3043 l_employee_id NUMBER;
3044 l_file VARCHAR2(500);
3045 l_msg_count NUMBER;
3046 l_msg_data VARCHAR2(2000);
3047 l_sql_error VARCHAR2(2000);
3048 l_msg_index NUMBER;
3049 j PLS_INTEGER;
3050 k PLS_INTEGER := 1;
3051 i PLS_INTEGER :=1;
3052 l_tbl_count NUMBER := 0;
3053 l_neg_code NUMBER := 0;
3054 l_instance_status VARCHAR2(1);
3055 l_sr_control NUMBER := 0;
3056 l_redeploy_flag VARCHAR2(1);
3057 l_upd_error_instance_id NUMBER := NULL;
3058
3059 cursor c_id is
3060 SELECT instance_status_id
3061 FROM csi_instance_statuses
3062 WHERE name = FND_PROFILE.VALUE('CSI_DEFAULT_INSTANCE_STATUS');
3063
3067 -- instead of the (+) transaction ID. This is done so that the hook will be
3064 r_id c_id%rowtype;
3065
3066 -- Get the Transaction ID for the (-) quantity transaction and pass that
3068 -- called after the second transaction is processed with the (+) qty and
3069 -- will prevent any timing issues with the transaction manager
3070
3071 CURSOR c_mtl is
3072 SELECT transfer_transaction_id,
3073 transaction_action_id,
3074 transaction_type_id,
3075 transaction_source_type_id,
3076 transaction_quantity
3077 FROM mtl_material_transactions
3078 WHERE transaction_id = p_transaction_id;
3079
3080 r_mtl c_mtl%rowtype;
3081
3082 CURSOR c_item_control (pc_item_id in number,
3083 pc_org_id in number) is
3084 SELECT serial_number_control_code,
3085 lot_control_code,
3086 revision_qty_control_code,
3087 location_control_code,
3088 comms_nl_trackable_flag
3089 FROM mtl_system_items_b
3090 WHERE inventory_item_id = pc_item_id
3091 AND organization_id = pc_org_id;
3092
3093 r_item_control c_item_control%rowtype;
3094
3095 CURSOR c_loc_ids (pc_org_id IN NUMBER,
3096 pc_subinv_name IN VARCHAR2) is
3097 SELECT haou.location_id hr_location_id,
3098 msi.location_id subinv_location_id
3099 FROM hr_all_organization_units haou,
3100 mtl_secondary_inventories msi
3101 WHERE haou.organization_id = pc_org_id
3102 AND msi.organization_id = pc_org_id
3103 AND msi.secondary_inventory_name = pc_subinv_name;
3104
3105 r_loc_ids c_loc_ids%rowtype;
3106
3107 BEGIN
3108
3109 x_return_status := l_fnd_success;
3110 l_error_message := NULL;
3111
3112 debug('******Start of csi_inv_interorg_pkg.direct_shipment Transaction procedure******');
3113 debug('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
3114 debug('csiorgtb.pls 115.23');
3115 debug('Transaction ID with is: '||p_transaction_id);
3116
3117 -- This will open the cursor and fetch the (-) transaction ID
3118 OPEN c_mtl;
3119 FETCH c_mtl into r_mtl;
3120 CLOSE c_mtl;
3121
3122 debug('Direct Interorg Transfer using Trasfer Trans ID');
3123 debug('Transaction ID with (+) is: '||p_transaction_id);
3124 debug('Transaction ID with (-) is: '||r_mtl.transfer_transaction_id);
3125
3126
3127 -- This procedure queries all of the Inventory Transaction Records and
3128 -- returns them as a table.
3129
3130 csi_inv_trxs_pkg.get_transaction_recs(r_mtl.transfer_transaction_id,
3131 l_mtl_item_tbl,
3132 l_return_status,
3133 l_error_message);
3134
3135 l_tbl_count := 0;
3136 l_tbl_count := l_mtl_item_tbl.count;
3137 debug('Inventory Records Found: '||l_tbl_count);
3138
3139 IF NOT l_return_status = l_fnd_success THEN
3140 debug('You have encountered an error in CSI_INV_TRXS_PKG.get_transaction_recs, Transaction ID: '||r_mtl.transfer_transaction_id);
3141 RAISE fnd_api.g_exc_error;
3142 END IF;
3143
3144 -- Get the Master Organization ID
3145 csi_inv_trxs_pkg.get_master_organization(l_mtl_item_tbl(i).organization_id,
3146 l_master_organization_id,
3147 l_return_status,
3148 l_error_message);
3149
3150 IF NOT l_return_status = l_fnd_success THEN
3151 debug('You have encountered an error in csi_inv_trxs_pkg.get_master_organization, Organization ID: '||l_mtl_item_tbl(i).organization_id);
3152 RAISE fnd_api.g_exc_error;
3153 END IF;
3154
3155 -- Call get_fnd_employee_id and get the employee id
3156 l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
3157
3158 IF l_employee_id = -1 THEN
3159 debug('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
3160 END IF;
3161
3162 debug('The Employee that is processing this Transaction is: '||l_employee_id);
3163
3164 -- See if this is a depreciable Item to set the status of the transaction record
3165 csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
3166 l_depreciable);
3167
3168 debug('Is this Item ID: '||l_mtl_item_tbl(i).inventory_item_id||', Depreciable :'||l_depreciable);
3169
3170 -- Set the quantity
3171 IF l_mtl_item_tbl(i).serial_number IS NULL THEN
3172 l_quantity := l_mtl_item_tbl(i).transaction_quantity;
3173 ELSE
3174 l_quantity := 1;
3175 END IF;
3176
3177 -- Get the Negative Receipt Code to see if this org allows Negative
3178 -- Quantity Records 1 = Yes, 2 = No
3179
3180 l_neg_code := csi_inv_trxs_pkg.get_neg_inv_code(
3181 l_mtl_item_tbl(i).organization_id);
3182
3183
3184 debug('Negative Code is - 1 = Yes, 2 = No: '||l_neg_code);
3185
3186 -- Determine Transaction Type for this
3187
3188 l_trans_type_code := 'INTERORG_DIRECT_SHIP';
3189 l_trans_app_code := 'INV';
3190
3191 debug('Trans Type Code: '||l_trans_type_code);
3192 debug('Trans App Code: '||l_trans_app_code);
3193
3194 -- Initialize Transaction Record
3198 IF l_depreciable = 'N' THEN
3195 l_txn_rec := csi_inv_trxs_pkg.init_txn_rec;
3196
3197 -- Set Status based on redeployment
3199 IF l_mtl_item_tbl(i).serial_number is NOT NULL THEN
3200 csi_inv_trxs_pkg.get_redeploy_flag(l_mtl_item_tbl(i).inventory_item_id,
3201 l_mtl_item_tbl(i).serial_number,
3202 l_sysdate,
3203 l_redeploy_flag,
3204 l_return_status,
3205 l_error_message);
3206 END IF;
3207 IF l_redeploy_flag = 'Y' THEN
3208 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
3209 ELSE
3210 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_complete;
3211 END IF;
3212 ELSE
3213 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
3214 END IF;
3215
3216 IF NOT l_return_status = l_fnd_success THEN
3217 debug('Redeploy Flag: '||l_redeploy_flag);
3218 debug('You have encountered an error in csi_inv_trxs_pkg.get_redeploy_flag: '||l_error_message);
3219 RAISE fnd_api.g_exc_error;
3220 END IF;
3221
3222 debug('Redeploy Flag: '||l_redeploy_flag);
3223 debug('Trans Status Code: '||l_txn_rec.transaction_status_code);
3224
3225 -- Get Default Profile Instance Status
3226 OPEN c_id;
3227 FETCH c_id into r_id;
3228 CLOSE c_id;
3229
3230 debug('Default Profile Status: '||r_id.instance_status_id);
3231
3232 -- Create CSI Transaction to be used
3233 l_txn_rec.source_transaction_date := l_mtl_item_tbl(i).transaction_date;
3234 l_txn_rec.transaction_date := l_sysdate;
3235 l_txn_rec.transaction_type_id :=
3236 csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
3237 l_txn_rec.transaction_quantity :=
3238 l_mtl_item_tbl(i).transaction_quantity;
3239 l_txn_rec.transaction_uom_code := l_mtl_item_tbl(i).transaction_uom;
3240 l_txn_rec.transacted_by := l_employee_id;
3241 l_txn_rec.transaction_action_code := NULL;
3242 l_txn_rec.message_id := p_message_id;
3243 l_txn_rec.inv_material_transaction_id := p_transaction_id;
3244 l_txn_rec.object_version_number := l_object_version_number;
3245
3246 csi_inv_trxs_pkg.create_csi_txn(l_txn_rec,
3247 l_error_message,
3248 l_return_status);
3249
3250 debug('CSI Transaction Created: '||l_txn_rec.transaction_id);
3251
3252 IF NOT l_return_status = l_fnd_success THEN
3253 debug('You have encountered an error in csi_inv_trxs_pkg.create_csi_txn: '||p_transaction_id);
3254 RAISE fnd_api.g_exc_error;
3255 END IF;
3256
3257 -- Now loop through the PL/SQL Table.
3258 j := 1;
3259
3260 debug('Starting to loop through Material Transaction Records');
3261
3262 FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
3263
3264 debug('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
3265 debug('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
3266 debug('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
3267 debug('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
3268 debug('Organization ID: '||l_mtl_item_tbl(j).organization_id);
3269 debug('Transfer Org ID: '||l_mtl_item_tbl(j).transfer_organization_id);
3270 debug('Transfer Subinv: '||l_mtl_item_tbl(j).transfer_subinventory);
3271
3272 -- Get Receiving Organization Serial Control Code
3273 OPEN c_item_control (l_mtl_item_tbl(j).inventory_item_id,
3274 l_mtl_item_tbl(j).transfer_organization_id);
3275 FETCH c_item_control into r_item_control;
3276 CLOSE c_item_control;
3277
3278 l_sr_control := r_item_control.serial_number_control_code;
3279
3280 debug('Serial Number : '||l_mtl_item_tbl(j).serial_number);
3281 debug('l_sr_control is: '||l_sr_control);
3282 debug('Shipping Org Serial Number Control Code: '||l_mtl_item_tbl(j).serial_number_control_code);
3283 debug('Receiving Org Serial Number Control Code: '||r_item_control.serial_number_control_code);
3284 debug('Shipping Org Lot Control Code: '||l_mtl_item_tbl(j).lot_control_code);
3285 debug('Receiving Org Lot Control Code: '||r_item_control.lot_control_code);
3286 debug('Shipping Org Loction Control Code: '||l_mtl_item_tbl(j).location_control_code);
3287 debug('Receiving Org Location Control Code: '||r_item_control.location_control_code);
3288 debug('Shipping Org Revision Control Code: '||l_mtl_item_tbl(j).revision_qty_control_code);
3289 debug('Receiving Org Revision Control Code: '||r_item_control.revision_qty_control_code);
3290 debug('Receiving Org Trackable Flag: '||r_item_control.comms_nl_trackable_flag);
3291
3292 -- Set Query Instance Status
3293 IF l_neg_code = 1 AND l_mtl_item_tbl(j).serial_number_control_code in (1,6) THEN
3294 l_instance_status := FND_API.G_FALSE;
3295 ELSE
3296 l_instance_status := FND_API.G_TRUE;
3297 END IF;
3298
3299 debug('Query Inst Status : '||l_instance_status);
3300
3301 -- Get the Location Ids for Receiving Org
3302 OPEN c_loc_ids (l_mtl_item_tbl(j).transfer_organization_id,
3303 l_mtl_item_tbl(j).transfer_subinventory);
3304 FETCH c_loc_ids into r_loc_ids;
3305 CLOSE c_loc_ids;
3306
3310 l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
3307 debug('Transfer Subinv Location: '||r_loc_ids.subinv_location_id);
3308 debug('Transfer HR Location : '||r_loc_ids.hr_location_id);
3309
3311 l_instance_usage_code := l_fnd_g_char;
3312
3313 --Direct Shipment Item
3314
3315 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
3316 l_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
3317 l_instance_query_rec.serial_number := l_mtl_item_tbl(j).serial_number;
3318 l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
3319 l_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
3320 l_instance_query_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
3321 --l_instance_query_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
3322 l_instance_query_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
3323 l_instance_query_rec.instance_usage_code := l_in_inventory;
3324 l_trx_action_type := 'DIRECT_SHIPMENT';
3325 l_instance_usage_code:= l_instance_query_rec.instance_usage_code;
3326
3327 --Setting Query for Shipping Org Serial Control
3328 IF l_mtl_item_tbl(j).serial_number_control_code in (1,6) THEN
3329 l_instance_query_rec.serial_number := l_fnd_g_char;
3330 debug('Shipping org is 1,6 so set to NULL');
3331 END IF;
3332
3333 debug('Transaction Action Type:'|| l_trx_action_type);
3334 debug('Before Get Item Instance');
3335
3336 csi_item_instance_pub.get_item_instances(l_api_version,
3337 l_commit,
3338 l_init_msg_list,
3339 l_validation_level,
3340 l_instance_query_rec,
3341 l_party_query_rec,
3342 l_account_query_rec,
3343 l_transaction_id,
3344 l_resolve_id_columns,
3345 l_instance_status,
3346 l_src_instance_header_tbl,
3347 l_return_status,
3348 l_msg_count,
3349 l_msg_data);
3350
3351 debug('After Get Item Instance');
3352
3353 l_tbl_count := 0;
3354 l_tbl_count := l_src_instance_header_tbl.count;
3355
3356 debug('Source Records Found: '||l_tbl_count);
3357
3358 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
3359 IF NOT l_return_status = l_fnd_success then
3360 debug('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
3361 l_msg_index := 1;
3362 WHILE l_msg_count > 0 loop
3363 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
3364 l_msg_index := l_msg_index + 1;
3365 l_msg_count := l_msg_count - 1;
3366 END LOOP;
3367 RAISE fnd_api.g_exc_error;
3368 END IF;
3369
3370 debug('Before checking to see if Source records Exist - 1');
3371
3372 IF l_mtl_item_tbl(j).serial_number_control_code in (2,5) AND -- Ship
3373 l_sr_control in (2,5) THEN -- Rec
3374 IF l_src_instance_header_tbl.count > 0 THEN
3375
3376 debug('Shipping Serial Control is 5 and Rec Serial Control 2,5');
3377 debug('Updating Serialized Instance: '||l_mtl_item_tbl(j).serial_number);
3378 debug('After you determine this is a Direct Shipment');
3379 debug('Instance being updated: '||l_src_instance_header_tbl(i).instance_id);
3380
3381 l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
3382 -- Added for Bug 5975739
3383 l_update_instance_rec.inv_master_organization_id := l_master_organization_id;
3384 l_update_instance_rec.inv_organization_id := l_mtl_item_tbl(j).transfer_organization_id;
3385 l_update_instance_rec.vld_organization_id := l_mtl_item_tbl(j).transfer_organization_id;
3386 l_update_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).transfer_subinventory;
3387 l_update_instance_rec.inv_locator_id := l_mtl_item_tbl(j).transfer_locator_id;
3388 l_update_instance_rec.location_id := nvl(r_loc_ids.subinv_location_id,r_loc_ids.hr_location_id);
3389 l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
3390 l_update_instance_rec.instance_usage_code := l_in_inventory;
3391 l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
3392
3393 debug('After you initialize the Transaction Record Values - 2');
3394 debug('After the update for Direct Shipment is set.');
3395 debug('Transfer Org: '||l_update_instance_rec.inv_organization_id);
3396 debug('Source Org: '||l_mtl_item_tbl(j).organization_id);
3397
3398 l_party_tbl.delete;
3399 l_account_tbl.delete;
3400 l_pricing_attrib_tbl.delete;
3401 l_org_assignments_tbl.delete;
3402 l_asset_assignment_tbl.delete;
3403
3407 l_commit,
3404 debug('Before Update Item Instance - 3');
3405
3406 csi_item_instance_pub.update_item_instance(l_api_version,
3408 l_init_msg_list,
3409 l_validation_level,
3410 l_update_instance_rec,
3411 l_ext_attrib_values_tbl,
3412 l_party_tbl,
3413 l_account_tbl,
3414 l_pricing_attrib_tbl,
3415 l_org_assignments_tbl,
3416 l_asset_assignment_tbl,
3417 l_txn_rec,
3418 l_instance_id_lst,
3419 l_return_status,
3420 l_msg_count,
3421 l_msg_data);
3422
3423 l_upd_error_instance_id := NULL;
3424 l_upd_error_instance_id := l_update_instance_rec.instance_id;
3425
3426 debug('After Update Item Instance - 4');
3427 debug('You are updating Instance: '||l_update_instance_rec.instance_id);
3428 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
3429
3430 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
3431 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
3432 debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
3433 l_msg_index := 1;
3434 WHILE l_msg_count > 0 loop
3435 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
3436 l_msg_index := l_msg_index + 1;
3437 l_msg_count := l_msg_count - 1;
3438 END LOOP;
3439 RAISE fnd_api.g_exc_error;
3440 END IF;
3441
3442 ELSE -- No Src Records found so error
3443
3444 debug('No Records were found in Install Base - 5');
3445 fnd_message.set_name('CSI','CSI_IB_RECORD_NOTFOUND');
3446 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
3447 fnd_message.set_token('SUBINVENTORY',l_mtl_item_tbl(j).subinventory_code);
3448 fnd_message.set_token('ORG_ID',l_mtl_item_tbl(j).organization_id);
3449 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
3450 l_error_message := fnd_message.get;
3451 RAISE fnd_api.g_exc_error;
3452 END IF; -- End of 5 and 2,5 IF
3453
3454 ELSIF (l_mtl_item_tbl(j).serial_number_control_code = 6 AND -- Ship
3455 l_sr_control = 6) OR -- Rec
3456 (l_mtl_item_tbl(j).serial_number_control_code = 6 AND -- Ship
3457 l_sr_control = 1) OR -- Rec
3458 (l_mtl_item_tbl(j).serial_number_control_code = 1 AND -- Ship
3459 l_sr_control = 1) THEN -- Rec
3460
3461 debug('Shipping and Rec Serial Control are both 1,6');
3462
3463 IF l_src_instance_header_tbl.count = 0 THEN
3464 IF l_neg_code = 1 THEN -- Allow Neg Qtys on NON Serial Items ONLY
3465
3466 debug('No records were found so create a new Source Instance Record - 6');
3467
3468 l_new_src_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
3469 l_new_src_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
3470 l_new_src_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
3471 l_new_src_instance_rec.inv_master_organization_id := l_master_organization_id;
3472 l_new_src_instance_rec.mfg_serial_number_flag := 'N';
3473 l_new_src_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
3474 l_new_src_instance_rec.quantity := l_mtl_item_tbl(j).transaction_quantity;
3475 l_new_src_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
3476 l_new_src_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
3477 l_new_src_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
3478 l_new_src_instance_rec.instance_usage_code := l_instance_usage_code;
3479 l_new_src_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
3480 l_new_src_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
3481 l_new_src_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
3482 l_new_src_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
3483 l_new_src_instance_rec.customer_view_flag := 'N';
3484 l_new_src_instance_rec.merchant_view_flag := 'Y';
3485 l_new_src_instance_rec.operational_status_code := 'NOT_USED';
3486 l_new_src_instance_rec.object_version_number := l_object_version_number;
3487 l_new_src_instance_rec.active_start_date := l_sysdate;
3488 l_new_src_instance_rec.active_end_date := NULL;
3489
3490 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
3491 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
3495 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
3492 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
3493 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
3494 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
3496
3497 debug('Before Create Source Item Instance - 7');
3498
3499 csi_item_instance_pub.create_item_instance(l_api_version,
3500 l_commit,
3501 l_init_msg_list,
3502 l_validation_level,
3503 l_new_src_instance_rec,
3504 l_ext_attrib_values_tbl,
3505 l_party_tbl,
3506 l_account_tbl,
3507 l_pricing_attrib_tbl,
3508 l_org_assignments_tbl,
3509 l_asset_assignment_tbl,
3510 l_txn_rec,
3511 l_return_status,
3512 l_msg_count,
3513 l_msg_data);
3514
3515
3516 debug('After Create Source Item Instance - 8');
3517 debug('You are Creating Instance: '||l_new_src_instance_rec.instance_id);
3518
3519 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
3520
3521 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
3522 debug('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
3523 l_msg_index := 1;
3524 WHILE l_msg_count > 0 loop
3525 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
3526 l_msg_index := l_msg_index + 1;
3527 l_msg_count := l_msg_count - 1;
3528 END LOOP;
3529 RAISE fnd_api.g_exc_error;
3530 END IF;
3531 ELSE -- Inv Does not allowe neg qty and source is not found
3532 debug('No Records were found in Install Base - 9');
3533 fnd_message.set_name('CSI','CSI_IB_RECORD_NOTFOUND');
3534 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
3535 fnd_message.set_token('SUBINVENTORY',l_mtl_item_tbl(j).subinventory_code);
3536 fnd_message.set_token('ORG_ID',l_mtl_item_tbl(j).organization_id);
3537 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
3538 l_error_message := fnd_message.get;
3539 RAISE fnd_api.g_exc_error;
3540
3541 END IF; -- End of Neg Qty IF
3542 ELSIF l_src_instance_header_tbl.count = 1 THEN -- Source Records are found
3543
3544 debug('Source Recs found so update or unexpire existing Non Serial Instance ');
3545
3546 -- Source Records are there so update and unexpire
3547
3548 debug('You will update instance: '||l_src_instance_header_tbl(i).instance_id);
3549
3550 l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
3551 l_upd_src_dest_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
3552 l_upd_src_dest_instance_rec.active_end_date := NULL;
3553 l_upd_src_dest_instance_rec.quantity := l_src_instance_header_tbl(i).quantity - abs(l_mtl_item_tbl(j).primary_quantity);
3554 l_upd_src_dest_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
3555
3556 l_party_tbl.delete;
3557 l_account_tbl.delete;
3558 l_pricing_attrib_tbl.delete;
3559 l_org_assignments_tbl.delete;
3560 l_asset_assignment_tbl.delete;
3561
3562 debug('Before Update Source Item Instance - 10');
3563 debug(r_id.instance_status_id);
3564 debug('Before Update Source Item Instance - 11');
3565
3566 l_upd_src_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);
3567
3568 debug('Before Update Source Item Instance - 11');
3569 debug(l_upd_src_dest_instance_rec.instance_status_id);
3570
3571 debug('Instance Status Id: '||l_upd_src_dest_instance_rec.instance_status_id);
3572
3573 csi_item_instance_pub.update_item_instance(l_api_version,
3574 l_commit,
3575 l_init_msg_list,
3576 l_validation_level,
3577 l_upd_src_dest_instance_rec,
3578 l_ext_attrib_values_tbl,
3579 l_party_tbl,
3580 l_account_tbl,
3581 l_pricing_attrib_tbl,
3582 l_org_assignments_tbl,
3583 l_asset_assignment_tbl,
3584 l_txn_rec,
3585 l_instance_id_lst,
3589
3586 l_return_status,
3587 l_msg_count,
3588 l_msg_data);
3590 l_upd_error_instance_id := NULL;
3591 l_upd_error_instance_id := l_upd_src_dest_instance_rec.instance_id;
3592
3593 debug('After Update Item Instance - 11');
3594 debug('You are updating Instance: '||l_upd_src_dest_instance_rec.instance_id);
3595 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
3596
3597 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
3598 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
3599 debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
3600 l_msg_index := 1;
3601 WHILE l_msg_count > 0 loop
3602 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
3603 l_msg_index := l_msg_index + 1;
3604 l_msg_count := l_msg_count - 1;
3605 END LOOP;
3606 RAISE fnd_api.g_exc_error;
3607 END IF;
3608
3609 ELSE -- Error No Src Recs and Inv Does not allow neg qtys
3610 debug('No Records were found in Install Base - 12');
3611 fnd_message.set_name('CSI','CSI_IB_RECORD_NOTFOUND');
3612 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
3613 fnd_message.set_token('SUBINVENTORY',l_mtl_item_tbl(j).subinventory_code);
3614 fnd_message.set_token('ORG_ID',l_mtl_item_tbl(j).organization_id);
3615 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
3616 l_error_message := fnd_message.get;
3617 RAISE fnd_api.g_exc_error;
3618 END IF; -- End of If for Main Source
3619
3620 -- Get Destination Records
3621
3622 l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
3623
3624 l_instance_query_rec.instance_usage_code := l_in_inventory;
3625 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
3626 l_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
3627 l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
3628 l_instance_query_rec.serial_number := NULL;
3629 l_instance_query_rec.inv_subinventory_name := l_mtl_item_tbl(j).transfer_subinventory;
3630 l_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).transfer_organization_id;
3631 l_instance_query_rec.inv_locator_id := l_mtl_item_tbl(j).transfer_locator_id;
3632 l_instance_usage_code := l_instance_query_rec.instance_usage_code;
3633 l_subinventory_name := l_mtl_item_tbl(j).transfer_subinventory;
3634 l_organization_id := l_mtl_item_tbl(j).transfer_organization_id;
3635 l_locator_id := l_mtl_item_tbl(j).transfer_locator_id;
3636
3637 debug('Before Get Item Instance - 13');
3638
3639 csi_item_instance_pub.get_item_instances(l_api_version,
3640 l_commit,
3641 l_init_msg_list,
3642 l_validation_level,
3643 l_instance_query_rec,
3644 l_party_query_rec,
3645 l_account_query_rec,
3646 l_transaction_id,
3647 l_resolve_id_columns,
3648 l_inactive_instance_only,
3649 l_dest_instance_header_tbl,
3650 l_return_status,
3651 l_msg_count,
3652 l_msg_data);
3653
3654 debug('After Get Item Instance - 14');
3655
3656 l_tbl_count := 0;
3657 l_tbl_count := l_dest_instance_header_tbl.count;
3658
3659 debug('Source Records Found: '||l_tbl_count);
3660
3661 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
3662 IF NOT l_return_status = l_fnd_success then
3663 debug('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
3664 l_msg_index := 1;
3665 WHILE l_msg_count > 0 loop
3666 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
3667 l_msg_index := l_msg_index + 1;
3668 l_msg_count := l_msg_count - 1;
3669 END LOOP;
3670 RAISE fnd_api.g_exc_error;
3671 END IF;
3672
3673 IF l_dest_instance_header_tbl.count = 0 THEN -- Installed Base Destination Records are not found so create a new record
3674
3675 debug('Creating New Dest dest Instance - 15');
3676
3677 l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
3678 l_new_dest_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
3682 l_new_dest_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
3679 l_new_dest_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
3680 l_new_dest_instance_rec.inv_master_organization_id := l_master_organization_id;
3681 l_new_dest_instance_rec.mfg_serial_number_flag := 'N';
3683 l_new_dest_instance_rec.quantity := abs(l_mtl_item_tbl(j).transaction_quantity);
3684 l_new_dest_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
3685 l_new_dest_instance_rec.location_id := nvl(r_loc_ids.subinv_location_id,r_loc_ids.hr_location_id);
3686 l_new_dest_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
3687 l_new_dest_instance_rec.instance_usage_code := l_instance_usage_code;
3688 l_new_dest_instance_rec.inv_organization_id := l_organization_id;
3689 l_new_dest_instance_rec.vld_organization_id := l_organization_id;
3690 l_new_dest_instance_rec.inv_subinventory_name := l_subinventory_name;
3691 l_new_dest_instance_rec.inv_locator_id := l_locator_id;
3692 l_new_dest_instance_rec.customer_view_flag := 'N';
3693 l_new_dest_instance_rec.merchant_view_flag := 'Y';
3694 l_new_dest_instance_rec.operational_status_code := 'NOT_USED';
3695 l_new_dest_instance_rec.object_version_number := l_object_version_number;
3696 l_new_dest_instance_rec.active_start_date := l_sysdate;
3697 l_new_dest_instance_rec.active_end_date := NULL;
3698
3699 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
3700 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
3701 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
3702 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
3703 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
3704 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
3705
3706 debug('Before Create Item Instance - 16');
3707
3708 csi_item_instance_pub.create_item_instance(l_api_version,
3709 l_commit,
3710 l_init_msg_list,
3711 l_validation_level,
3712 l_new_dest_instance_rec,
3713 l_ext_attrib_values_tbl,
3714 l_party_tbl,
3715 l_account_tbl,
3716 l_pricing_attrib_tbl,
3717 l_org_assignments_tbl,
3718 l_asset_assignment_tbl,
3719 l_txn_rec,
3720 l_return_status,
3721 l_msg_count,
3722 l_msg_data);
3723
3724 debug('After Create Item Instance - 17');
3725 debug('You are Creating Instance: '||l_new_dest_instance_rec.instance_id);
3726
3727 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
3728
3729 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
3730 debug('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
3731 l_msg_index := 1;
3732 WHILE l_msg_count > 0 loop
3733 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
3734 l_msg_index := l_msg_index + 1;
3735 l_msg_count := l_msg_count - 1;
3736 END LOOP;
3737 RAISE fnd_api.g_exc_error;
3738 END IF;
3739
3740 ELSIF l_dest_instance_header_tbl.count = 1 THEN -- Installed Base Destination Records Found
3741
3742 l_update_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
3743 l_update_dest_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
3744 l_update_dest_instance_rec.quantity := l_dest_instance_header_tbl(i).quantity + abs(l_mtl_item_tbl(j).primary_quantity);
3745 l_update_dest_instance_rec.active_end_date := NULL;
3746 l_update_dest_instance_rec.object_version_number := l_dest_instance_header_tbl(i).object_version_number;
3747
3748 l_party_tbl.delete;
3749 l_account_tbl.delete;
3750 l_pricing_attrib_tbl.delete;
3751 l_org_assignments_tbl.delete;
3752 l_asset_assignment_tbl.delete;
3753
3754 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);
3755
3756 debug('Instance Status Id: '||l_update_dest_instance_rec.instance_status_id);
3757 debug('Before Update Item Instance - 19');
3758
3759
3763 l_validation_level,
3760 csi_item_instance_pub.update_item_instance(l_api_version,
3761 l_commit,
3762 l_init_msg_list,
3764 l_update_dest_instance_rec,
3765 l_ext_attrib_values_tbl,
3766 l_party_tbl,
3767 l_account_tbl,
3768 l_pricing_attrib_tbl,
3769 l_org_assignments_tbl,
3770 l_asset_assignment_tbl,
3771 l_txn_rec,
3772 l_instance_id_lst,
3773 l_return_status,
3774 l_msg_count,
3775 l_msg_data);
3776
3777 l_upd_error_instance_id := NULL;
3778 l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
3779
3780 debug('After Update Item Instance - 20');
3781 debug('You are updating Instance: '||l_update_dest_instance_rec.instance_id);
3782 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
3783
3784 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
3785 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
3786 debug('You encountered an error in the csi_item_instance_pub.c API '||l_msg_data);
3787 l_msg_index := 1;
3788 WHILE l_msg_count > 0 loop
3789 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
3790 l_msg_index := l_msg_index + 1;
3791 l_msg_count := l_msg_count - 1;
3792 END LOOP;
3793 RAISE fnd_api.g_exc_error;
3794 END IF;
3795
3796 ELSIF l_dest_instance_header_tbl.count > 1 THEN
3797 -- Multiple Instances were found so throw error
3798 debug('Multiple Instances were Found in Install Base-30');
3799 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
3800 fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
3801 fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
3802 fnd_message.set_token('INV_ORG_ID',l_mtl_item_tbl(j).organization_id);
3803 fnd_message.set_token('LOCATOR',l_mtl_item_tbl(j).locator_id);
3804 l_error_message := fnd_message.get;
3805 RAISE fnd_api.g_exc_error;
3806 END IF; -- End of Destination Record If
3807
3808 ELSIF (l_mtl_item_tbl(j).serial_number_control_code = 5 AND -- Ship
3809 l_sr_control = 1) OR -- Rec
3810 (l_mtl_item_tbl(j).serial_number_control_code = 2 AND -- Ship
3811 l_sr_control = 1) THEN -- Rec
3812
3813 debug('Shipping is 2,5 and and Rec Serial Control is 1');
3814 --HERE TODAY
3815 -- FOR k in l_src_instance_header_tbl.FIRST .. abs(l_mtl_item_tbl(j).transaction_quantity) LOOP
3816 -- debug('k is: '||k);
3817 -- debug('You will loop: '||abs(l_mtl_item_tbl(j).transaction_quantity)||' times');
3818
3819 debug('Serial Control at Shipping is 2,5 and Receiving is 1,6');
3820 debug('Expire The Serialized Instance First');
3821 debug('Instance being updated: '||l_src_instance_header_tbl(i).instance_id);
3822
3823 l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
3824 l_update_instance_rec.active_end_date := l_sysdate;
3825 l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
3826
3827 debug('After you initialize the Update Record Values');
3828 debug('After you initialize the Transaction Record Values');
3829
3830 l_party_tbl.delete;
3831 l_account_tbl.delete;
3832 l_pricing_attrib_tbl.delete;
3833 l_org_assignments_tbl.delete;
3834 l_asset_assignment_tbl.delete;
3835
3836 debug('Before Update Item Instance - 25');
3837
3838 csi_item_instance_pub.update_item_instance(l_api_version,
3839 l_commit,
3840 l_init_msg_list,
3841 l_validation_level,
3842 l_update_instance_rec,
3843 l_ext_attrib_values_tbl,
3844 l_party_tbl,
3845 l_account_tbl,
3846 l_pricing_attrib_tbl,
3847 l_org_assignments_tbl,
3848 l_asset_assignment_tbl,
3849 l_txn_rec,
3850 l_instance_id_lst,
3851 l_return_status,
3852 l_msg_count,
3856 l_upd_error_instance_id := l_update_instance_rec.instance_id;
3853 l_msg_data);
3854
3855 l_upd_error_instance_id := NULL;
3857
3858 debug('After Update Item Instance - 26');
3859 debug('You are updating Instance: '||l_update_instance_rec.instance_id);
3860 debug('You are updating Serial Number: '||l_update_instance_rec.serial_number);
3861 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
3862
3863 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
3864 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
3865 debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
3866 l_msg_index := 1;
3867 WHILE l_msg_count > 0 loop
3868 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
3869 l_msg_index := l_msg_index + 1;
3870 l_msg_count := l_msg_count - 1;
3871 END LOOP;
3872 RAISE fnd_api.g_exc_error;
3873 END IF;
3874 -- END LOOP; -- End For Loop for Update of Sr Instances
3875
3876 -- Now Query for Non Serialized In Inventory Record 1 Time Only
3877 IF j = 1 THEN
3878
3879 l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
3880
3881 l_instance_query_rec.instance_usage_code := l_in_inventory;
3882 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
3883 l_instance_query_rec.inventory_revision := l_mtl_item_tbl(j).revision;
3884 l_instance_query_rec.lot_number := l_mtl_item_tbl(j).lot_number;
3885 l_instance_query_rec.inv_subinventory_name := l_mtl_item_tbl(j).transfer_subinventory;
3886 l_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).transfer_organization_id;
3887 l_instance_query_rec.inv_locator_id := l_mtl_item_tbl(j).transfer_locator_id;
3888 l_instance_usage_code := l_instance_query_rec.instance_usage_code;
3889 l_subinventory_name := l_mtl_item_tbl(j).transfer_subinventory;
3890 l_organization_id := l_mtl_item_tbl(j).transfer_organization_id;
3891 l_locator_id := l_mtl_item_tbl(j).transfer_locator_id;
3892
3893 debug('Before Get Item Instance - 27');
3894
3895 csi_item_instance_pub.get_item_instances(l_api_version,
3896 l_commit,
3897 l_init_msg_list,
3898 l_validation_level,
3899 l_instance_query_rec,
3900 l_party_query_rec,
3901 l_account_query_rec,
3902 l_transaction_id,
3903 l_resolve_id_columns,
3904 l_inactive_instance_only,
3905 l_dest_instance_header_tbl,
3906 l_return_status,
3907 l_msg_count,
3908 l_msg_data);
3909
3910 debug('After Get Item Instance - 28');
3911
3912 l_tbl_count := 0;
3913 l_tbl_count := l_dest_instance_header_tbl.count;
3914
3915 debug('Source Records Found: '||l_tbl_count);
3916
3917 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
3918 IF NOT l_return_status = l_fnd_success then
3919 debug('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
3920 l_msg_index := 1;
3921 WHILE l_msg_count > 0 loop
3922 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
3923 l_msg_index := l_msg_index + 1;
3924 l_msg_count := l_msg_count - 1;
3925 END LOOP;
3926 RAISE fnd_api.g_exc_error;
3927 END IF;
3928
3929 IF l_dest_instance_header_tbl.count = 0 THEN -- Installed Base Destination Records are not found so create a new record
3930
3931 debug('Creating New Dest dest Instance - 29');
3932
3933 l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
3934 l_new_dest_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
3935 l_new_dest_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
3936 l_new_dest_instance_rec.inv_master_organization_id := l_master_organization_id;
3937 l_new_dest_instance_rec.mfg_serial_number_flag := 'N';
3938 l_new_dest_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
3939 l_new_dest_instance_rec.quantity := abs(l_mtl_item_tbl(j).transaction_quantity);
3940 l_new_dest_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
3944 l_new_dest_instance_rec.inv_organization_id := l_organization_id;
3941 l_new_dest_instance_rec.location_id := nvl(r_loc_ids.subinv_location_id,r_loc_ids.hr_location_id);
3942 l_new_dest_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
3943 l_new_dest_instance_rec.instance_usage_code := l_instance_usage_code;
3945 l_new_dest_instance_rec.vld_organization_id := l_organization_id;
3946 l_new_dest_instance_rec.inv_subinventory_name := l_subinventory_name;
3947 l_new_dest_instance_rec.inv_locator_id := l_locator_id;
3948 l_new_dest_instance_rec.customer_view_flag := 'N';
3949 l_new_dest_instance_rec.merchant_view_flag := 'Y';
3950 l_new_dest_instance_rec.operational_status_code := 'NOT_USED';
3951 l_new_dest_instance_rec.object_version_number := l_object_version_number;
3952 l_new_dest_instance_rec.active_start_date := l_sysdate;
3953 l_new_dest_instance_rec.active_end_date := NULL;
3954
3955 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
3956 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
3957 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
3958 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
3959 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
3960 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
3961
3962 debug('Before Create Item Instance - 30');
3963
3964 csi_item_instance_pub.create_item_instance(l_api_version,
3965 l_commit,
3966 l_init_msg_list,
3967 l_validation_level,
3968 l_new_dest_instance_rec,
3969 l_ext_attrib_values_tbl,
3970 l_party_tbl,
3971 l_account_tbl,
3972 l_pricing_attrib_tbl,
3973 l_org_assignments_tbl,
3974 l_asset_assignment_tbl,
3975 l_txn_rec,
3976 l_return_status,
3977 l_msg_count,
3978 l_msg_data);
3979
3980 debug('After Create Item Instance - 31');
3981 debug('You are Creating Instance: '||l_new_dest_instance_rec.instance_id);
3982
3983 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
3984
3985 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
3986 debug('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
3987 l_msg_index := 1;
3988 WHILE l_msg_count > 0 loop
3989 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
3990 l_msg_index := l_msg_index + 1;
3991 l_msg_count := l_msg_count - 1;
3992 END LOOP;
3993 RAISE fnd_api.g_exc_error;
3994 END IF;
3995
3996 ELSIF l_dest_instance_header_tbl.count = 1 THEN -- Installed Base Destination Records Found
3997
3998 l_update_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
3999 l_update_dest_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
4000 l_update_dest_instance_rec.quantity := l_dest_instance_header_tbl(i).quantity + abs(l_mtl_item_tbl(j).primary_quantity);
4001 l_update_dest_instance_rec.active_end_date := NULL;
4002 l_update_dest_instance_rec.object_version_number := l_dest_instance_header_tbl(i).object_version_number;
4003
4004 l_party_tbl.delete;
4005 l_account_tbl.delete;
4006 l_pricing_attrib_tbl.delete;
4007 l_org_assignments_tbl.delete;
4008 l_asset_assignment_tbl.delete;
4009
4010 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);
4011
4012 debug('Instance Status Id: '||l_update_dest_instance_rec.instance_status_id);
4013 debug('Before Update Item Instance - 32');
4014
4015 csi_item_instance_pub.update_item_instance(l_api_version,
4016 l_commit,
4017 l_init_msg_list,
4018 l_validation_level,
4019 l_update_dest_instance_rec,
4020 l_ext_attrib_values_tbl,
4021 l_party_tbl,
4025 l_asset_assignment_tbl,
4022 l_account_tbl,
4023 l_pricing_attrib_tbl,
4024 l_org_assignments_tbl,
4026 l_txn_rec,
4027 l_instance_id_lst,
4028 l_return_status,
4029 l_msg_count,
4030 l_msg_data);
4031
4032 l_upd_error_instance_id := NULL;
4033 l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
4034
4035 debug('After Update Item Instance - 33');
4036 debug('You are updating Instance: '||l_update_dest_instance_rec.instance_id);
4037 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
4038
4039 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
4040 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
4041 debug('You encountered an error in the csi_item_instance_pub.c API '||l_msg_data);
4042 l_msg_index := 1;
4043 WHILE l_msg_count > 0 loop
4044 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
4045 l_msg_index := l_msg_index + 1;
4046 l_msg_count := l_msg_count - 1;
4047 END LOOP;
4048 RAISE fnd_api.g_exc_error;
4049 END IF;
4050
4051 ELSIF l_dest_instance_header_tbl.count > 1 THEN
4052 -- Multiple Instances were found so throw error
4053 debug('Multiple Instances were Found in Install Base-30');
4054 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
4055 fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
4056 fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
4057 fnd_message.set_token('INV_ORG_ID',l_mtl_item_tbl(j).organization_id);
4058 fnd_message.set_token('LOCATOR',l_mtl_item_tbl(j).locator_id);
4059 l_error_message := fnd_message.get;
4060 RAISE fnd_api.g_exc_error;
4061 END IF; -- End of Destination Record If
4062 END IF; -- End of J Index Loop
4063
4064 ELSIF (l_mtl_item_tbl(j).serial_number_control_code = 5 AND -- Ship
4065 l_sr_control = 6) OR -- Rec
4066 (l_mtl_item_tbl(j).serial_number_control_code = 2 AND -- Ship
4067 l_sr_control = 5) OR -- Rec
4068 (l_mtl_item_tbl(j).serial_number_control_code = 2 AND -- Ship
4069 l_sr_control = 6) OR -- Rec
4070 (l_mtl_item_tbl(j).serial_number_control_code = 6 AND -- Ship
4071 l_sr_control = 5) OR -- Rec
4072 (l_mtl_item_tbl(j).serial_number_control_code = 6 AND -- Ship
4073 l_sr_control = 2) OR -- Rec
4074 (l_mtl_item_tbl(j).serial_number_control_code = 1 AND -- Ship
4075 l_sr_control = 2) OR -- Rec
4076 (l_mtl_item_tbl(j).serial_number_control_code = 1 AND -- Ship
4077 l_sr_control = 5) OR -- Rec
4078 (l_mtl_item_tbl(j).serial_number_control_code = 1 AND -- Ship
4079 l_sr_control = 6) THEN -- Rec
4080
4081 debug('This Shipping and Receiving Serial Control combination is not supported');
4082 debug('Shipping Serial Control is: '||l_mtl_item_tbl(j).serial_number_control_code);
4083 debug('Receiving Serial Control is: '||l_sr_control);
4084
4085 debug('This is a NON Supported Transaction Combination in Inventory - 38');
4086 fnd_message.set_name('CSI','CSI_INV_NOT_SUPPORTED');
4087 fnd_message.set_token('SHIP_ORG',l_mtl_item_tbl(j).organization_id);
4088 fnd_message.set_token('SHIP_SC',l_mtl_item_tbl(j).serial_number_control_code);
4089 fnd_message.set_token('REC_ORG',l_mtl_item_tbl(j).transfer_organization_id);
4090 fnd_message.set_token('REC_SC',l_sr_control);
4091 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
4092 l_error_message := fnd_message.get;
4093 RAISE fnd_api.g_exc_error;
4094 END IF; -- End of Serial Control IF
4095 END LOOP; -- End of main For Inv Loop
4096
4097 debug('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
4098 debug('******End of csi_inv_interorg_pkg.direct_shipment Transaction******');
4099
4100 EXCEPTION
4101 WHEN fnd_api.g_exc_error THEN
4102 debug('You have encountered a "fnd_api.g_exc_error" exception in the Direct Inter Org Transaction');
4103 x_return_status := l_fnd_error;
4104
4105 IF l_mtl_item_tbl.count > 0 THEN
4106 x_trx_error_rec.serial_number := l_mtl_item_tbl(j).serial_number;
4107 x_trx_error_rec.lot_number := l_mtl_item_tbl(j).lot_number;
4108 x_trx_error_rec.instance_id := l_upd_error_instance_id;
4109 x_trx_error_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
4110 x_trx_error_rec.src_serial_num_ctrl_code := l_mtl_item_tbl(j).serial_number_control_code;
4111 x_trx_error_rec.src_location_ctrl_code := l_mtl_item_tbl(j).location_control_code;
4112 x_trx_error_rec.src_lot_ctrl_code := l_mtl_item_tbl(j).lot_control_code;
4113 x_trx_error_rec.src_rev_qty_ctrl_code := l_mtl_item_tbl(j).revision_qty_control_code;
4114 x_trx_error_rec.dst_serial_num_ctrl_code := r_item_control.serial_number_control_code;
4115 x_trx_error_rec.dst_location_ctrl_code := r_item_control.location_control_code;
4116 x_trx_error_rec.dst_lot_ctrl_code := r_item_control.lot_control_code;
4117 x_trx_error_rec.dst_rev_qty_ctrl_code := r_item_control.revision_qty_control_code;
4118 x_trx_error_rec.comms_nl_trackable_flag := l_mtl_item_tbl(j).comms_nl_trackable_flag;
4119 x_trx_error_rec.transaction_error_date := l_sysdate ;
4120 END IF;
4121
4122 x_trx_error_rec.error_text := l_error_message;
4123 x_trx_error_rec.transaction_id := NULL;
4124 x_trx_error_rec.source_type := 'CSIORGDS';
4125 x_trx_error_rec.source_id := p_transaction_id;
4126 x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
4127 x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
4128 x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
4129 x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
4130
4131 WHEN others THEN
4132 l_sql_error := SQLERRM;
4133 debug('You have encountered a "when others" exception in the Direct Inter Org Transaction');
4134 debug('SQL Error: '||l_sql_error);
4135 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
4136 fnd_message.set_token('API_NAME',l_api_name);
4137 fnd_message.set_token('SQL_ERROR',SQLERRM);
4138 x_return_status := l_fnd_unexpected;
4139
4140 IF l_mtl_item_tbl.count > 0 THEN
4141 x_trx_error_rec.serial_number := l_mtl_item_tbl(j).serial_number;
4142 x_trx_error_rec.lot_number := l_mtl_item_tbl(j).lot_number;
4143 x_trx_error_rec.instance_id := l_upd_error_instance_id;
4144 x_trx_error_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
4145 x_trx_error_rec.src_serial_num_ctrl_code := l_mtl_item_tbl(j).serial_number_control_code;
4146 x_trx_error_rec.src_location_ctrl_code := l_mtl_item_tbl(j).location_control_code;
4147 x_trx_error_rec.src_lot_ctrl_code := l_mtl_item_tbl(j).lot_control_code;
4148 x_trx_error_rec.src_rev_qty_ctrl_code := l_mtl_item_tbl(j).revision_qty_control_code;
4149 x_trx_error_rec.dst_serial_num_ctrl_code := r_item_control.serial_number_control_code;
4150 x_trx_error_rec.dst_location_ctrl_code := r_item_control.location_control_code;
4151 x_trx_error_rec.dst_lot_ctrl_code := r_item_control.lot_control_code;
4152 x_trx_error_rec.dst_rev_qty_ctrl_code := r_item_control.revision_qty_control_code;
4153 x_trx_error_rec.comms_nl_trackable_flag := l_mtl_item_tbl(j).comms_nl_trackable_flag;
4154 x_trx_error_rec.transaction_error_date := l_sysdate ;
4155 END IF;
4156
4157 x_trx_error_rec.error_text := fnd_message.get;
4158 x_trx_error_rec.transaction_id := NULL;
4159 x_trx_error_rec.source_type := 'CSIORGDS';
4160 x_trx_error_rec.source_id := p_transaction_id;
4161 x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
4162 x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
4163 x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
4164 x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
4165
4166 END direct_shipment;
4167
4168
4169 END csi_inv_interorg_pkg;