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