[Home] [Help]
PACKAGE BODY: APPS.CSI_INV_TRXS_PKG
Source
1 package body CSI_INV_TRXS_PKG as
2 -- $Header: csiivtxb.pls 120.30.12020000.3 2012/11/09 10:19:21 mvaradam ship $
3
4 l_Sysdate DATE := SYSDATE;
5
6 PROCEDURE debug(p_message IN varchar2) IS
7 BEGIN
8 csi_t_gen_utility_pvt.add(p_message);
9 EXCEPTION
10 WHEN others THEN
11 null;
12 END debug;
13
14 PROCEDURE misc_receipt(p_transaction_id IN NUMBER,
15 p_message_id IN NUMBER,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_trx_error_rec OUT NOCOPY CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC)
18 IS
19
20 l_mtl_item_tbl CSI_INV_TRXS_PKG.MTL_ITEM_TBL_TYPE;
21 l_api_name VARCHAR2(100) := 'CSI_INV_TRXS_PKG.MISC_RECEIPT';
22 l_api_version NUMBER := 1.0;
23 l_commit VARCHAR2(1) := FND_API.G_FALSE;
24 l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
25 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
26 l_active_instance_only VARCHAR2(10) := FND_API.G_TRUE;
27 l_inactive_instance_only VARCHAR2(10) := FND_API.G_FALSE;
28 l_resolve_id_columns VARCHAR2(10) := FND_API.G_FALSE;
29 l_transaction_id NUMBER := NULL;
30 l_object_version_number NUMBER := 1;
31 l_sysdate DATE := SYSDATE;
32 l_master_organization_id NUMBER;
33 l_depreciable VARCHAR2(1);
34 l_instance_query_rec CSI_DATASTRUCTURES_PUB.INSTANCE_QUERY_REC;
35 l_update_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
36 l_api_src_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
37 l_new_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
38 l_txn_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_REC;
39 l_return_status VARCHAR2(1);
40 l_error_code VARCHAR2(50);
41 l_error_message VARCHAR2(4000);
42 l_instance_id_lst CSI_DATASTRUCTURES_PUB.ID_TBL;
43 l_party_query_rec CSI_DATASTRUCTURES_PUB.PARTY_QUERY_REC;
44 l_account_query_rec CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_QUERY_REC;
45 l_src_instance_header_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_HEADER_TBL;
46 l_ext_attrib_values_tbl CSI_DATASTRUCTURES_PUB.EXTEND_ATTRIB_VALUES_TBL;
47 l_party_tbl CSI_DATASTRUCTURES_PUB.PARTY_TBL;
48 l_account_tbl CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_TBL;
49 l_pricing_attrib_tbl CSI_DATASTRUCTURES_PUB.PRICING_ATTRIBS_TBL;
50 l_org_assignments_tbl CSI_DATASTRUCTURES_PUB.ORGANIZATION_UNITS_TBL;
51 l_asset_assignment_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_ASSET_TBL;
52 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
53 l_fnd_warning VARCHAR2(1) := 'W';
54 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
55 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
56 l_in_inventory VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_INVENTORY;
57 l_in_process VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_PROCESS;
58 l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
59 l_out_of_enterprise VARCHAR2(25) := 'OUT_OF_ENTERPRISE';
60 l_in_relationship VARCHAR2(25) := 'IN_RELATIONSHIP';
61 l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
62 l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
63 l_installed VARCHAR2(25) := CSI_INV_TRXS_PKG.G_INSTALLED;
64 l_in_wip VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_WIP;
65 l_transaction_error_id NUMBER;
66 l_quantity NUMBER;
67 l_mfg_serial_flag VARCHAR2(1);
68 l_trans_status_code VARCHAR2(15);
69 l_ins_number VARCHAR2(100);
70 l_ins_id NUMBER;
71 l_file VARCHAR2(500);
72 l_status VARCHAR2(1000);
73 l_msg_count NUMBER;
74 l_msg_data VARCHAR2(2000);
75 l_sql_error VARCHAR2(2000);
76 l_msg_index NUMBER;
77 l_employee_id NUMBER;
78 j PLS_INTEGER;
79 i PLS_INTEGER := 1;
80 p PLS_INTEGER := 1;
81 l_tbl_count NUMBER := 0;
82 b NUMBER;
83 l_trans_type_code VARCHAR2(25);
84 l_trans_app_code VARCHAR2(5);
85 l_ownership_party VARCHAR2(1);
86 l_internal_party_id NUMBER; --added code for bug #5868111
87 l_owner_party_id NUMBER; --added code for bug #5868111
88 l_redeploy_flag VARCHAR2(1);
89 l_upd_error_instance_id NUMBER := NULL;
90
91 l_instance_header_rec csi_datastructures_pub.instance_header_rec;
92 l_party_header_tbl csi_datastructures_pub.party_header_tbl;
93 l_account_header_tbl csi_datastructures_pub.party_account_header_tbl;
94 l_org_header_tbl csi_datastructures_pub.org_units_header_tbl;
95 l_pricing_header_tbl csi_datastructures_pub.pricing_attribs_tbl;
96 l_ext_attrib_header_tbl csi_datastructures_pub.extend_attrib_values_tbl;
97 l_ext_attrib_def_tbl csi_datastructures_pub.extend_attrib_tbl;
98 l_asset_header_tbl csi_datastructures_pub.instance_asset_header_tbl;
99
100 cursor c_id is
101 SELECT instance_status_id
102 FROM csi_instance_statuses
103 WHERE name = FND_PROFILE.VALUE('CSI_DEFAULT_INSTANCE_STATUS');
104
105 r_id c_id%rowtype;
106
107 CURSOR c_obj_version (pc_instance_id IN NUMBER) is
108 SELECT object_version_number
109 FROM csi_item_instances
110 WHERE instance_id = pc_instance_id;
111
112 CURSOR c_phys_inv_info (pc_physical_adjustment_id IN NUMBER) is
113 SELECT mpi.physical_inventory_id physical_inventory_id,
114 mpi.physical_inventory_name physical_inventory_name,
115 mpit.tag_number tag_number
116 FROM mtl_physical_adjustments mpa,
117 mtl_physical_inventories mpi,
118 mtl_physical_inventory_tags mpit
119 WHERE mpa.physical_inventory_id = mpi.physical_inventory_id
120 AND mpa.physical_inventory_id = mpit.physical_inventory_id
121 AND mpa.adjustment_id = mpit.adjustment_id
122 AND mpa.adjustment_id = pc_physical_adjustment_id;
123
124 r_phys_inv_info c_phys_inv_info%rowtype;
125
126 CURSOR c_cycle_count_info (pc_cycle_count_entry_id IN NUMBER) is
127 SELECT mcch.cycle_count_header_id cycle_count_header_id,
128 mcch.cycle_count_header_name cycle_count_header_name
129 FROM mtl_cycle_count_entries mcce, mtl_cycle_count_headers mcch
130 WHERE mcce.cycle_count_header_id = mcch.cycle_count_header_id
131 AND mcce.cycle_count_entry_id = pc_cycle_count_entry_id;
132
133 r_cycle_count_info c_cycle_count_info%rowtype;
134
135 BEGIN
136 x_return_status := l_fnd_success;
137
138 debug('*****Start of csi_inv_trxs_pkg.misc_receipt Transaction procedure*****');
139 debug('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
140 debug('csiivtxb.pls 115.25');
141 debug('Transaction You are Processing is: '||p_transaction_id);
142
143 -- This procedure queries all of the Inventory Transaction Records and returns them
144 -- as a table.
145
146 debug('Executing csi_inv_trxs_pkg.get_transaction_recs');
147
148 csi_inv_trxs_pkg.get_transaction_recs(p_transaction_id,
149 l_mtl_item_tbl,
150 l_return_status,
151 l_error_message);
152
153 l_tbl_count := 0;
154 l_tbl_count := l_mtl_item_tbl.count;
155
156 debug('Inventory Records Found: '||l_tbl_count);
157
158 IF NOT l_return_status = l_fnd_success THEN
159 debug('You have encountered an error in CSI_INV_TRXS_PKG.get_transaction_recs, Transaction ID: '||p_transaction_id);
160 RAISE fnd_api.g_exc_error;
161 END IF;
162
163 -- Determine Trasaction Type
164 IF l_mtl_item_tbl(i).transaction_type_id = 8 THEN
165 l_trans_type_code := 'PHYSICAL_INVENTORY';
166 l_trans_app_code := 'INV';
167 ELSIF l_mtl_item_tbl(i).transaction_type_id = 4 THEN
168 l_trans_type_code := 'CYCLE_COUNT';
169 l_trans_app_code := 'INV';
170 ELSIF l_mtl_item_tbl(i).transaction_type_id = 40 THEN
171 l_trans_type_code := 'ACCT_RECEIPT';
172 l_trans_app_code := 'INV';
173 ELSIF l_mtl_item_tbl(i).transaction_type_id = 41 THEN
174 l_trans_type_code := 'ACCT_ALIAS_RECEIPT';
175 l_trans_app_code := 'INV';
176 ELSIF l_mtl_item_tbl(i).transaction_type_id = 71 THEN
177 l_trans_type_code := 'PO_RCPT_ADJUSTMENT';
178 l_trans_app_code := 'INV';
179 ELSIF l_mtl_item_tbl(i).transaction_type_id = 72 THEN
180 l_trans_type_code := 'INT_REQ_RCPT_ADJUSTMENT';
181 l_trans_app_code := 'INV';
182 ELSIF l_mtl_item_tbl(i).transaction_type_id = 70 THEN
183 l_trans_type_code := 'SHIPMENT_RCPT_ADJUSTMENT';
184 l_trans_app_code := 'INV';
185 ELSIF l_mtl_item_tbl(i).transaction_type_id = 42 THEN
186 l_trans_type_code := 'MISC_RECEIPT';
187 l_trans_app_code := 'INV';
188 ELSE
189 l_trans_type_code := 'MISC_RECEIPT';
190 l_trans_app_code := 'INV';
191 END IF;
192
193 debug('Trans Type Code: '||l_trans_type_code);
194 debug('Trans App Code: '||l_trans_app_code);
195
196
197 -- Get the Master Organization ID
198
199 debug('Executing csi_inv_trxs_pkg.get_master_organization');
200
201 csi_inv_trxs_pkg.get_master_organization(l_mtl_item_tbl(i).organization_id,
202 l_master_organization_id,
203 l_return_status,
204 l_error_message);
205
206 debug('Master Organization is: '||l_master_organization_id);
207
208 IF NOT l_return_status = l_fnd_success THEN
209 debug('You have encountered an error in csi_inv_trxs_pkg.get_master_organization, Organization ID: '||l_mtl_item_tbl(i).organization_id);
210 RAISE fnd_api.g_exc_error;
211 END IF;
212
213 -- Call get_fnd_employee_id and get the employee id
214
215 debug('Executing csi_inv_trxs_pkg.get_fnd_employee_id');
216
217 l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
218
219 IF l_employee_id = -1 THEN
220 debug('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
221 END IF;
222
223 debug('The Employee that is processing this Transaction is: '||l_employee_id);
224
225 -- See if this is a depreciable Item to set the status of the transaction record
226
227 debug('Executing csi_inv_trxs_pkg.check_depreciable');
228
229 csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
230 l_depreciable,
231 l_mtl_item_tbl(i).organization_id); --Added for Bug 13988660
232
233 debug('Is this Item ID: '||l_mtl_item_tbl(i).inventory_item_id||', Depreciable :'||l_depreciable);
234
235 -- Set the mfg_serial_number_flag and quantity
236 IF l_mtl_item_tbl(i).serial_number IS NULL THEN
237 l_mfg_serial_flag := 'N';
238 l_quantity := l_mtl_item_tbl(i).transaction_quantity;
239 ELSE
240 l_mfg_serial_flag := 'Y';
241 l_quantity := 1;
242 END IF;
243
244 debug('The mfg_serial_flag is: '||l_mfg_serial_flag);
245 debug('The Quantity is: '||l_quantity);
246 debug('The Transaction Status will be - Complete or Pending: '||l_trans_status_code);
247
248 -- Get Party ownership Flag
249 l_ownership_party := csi_datastructures_pub.g_install_param_rec.ownership_override_at_txn;
250 l_internal_party_id := csi_datastructures_pub.g_install_param_rec.internal_party_id; --added code for bug #5868111
251
252 debug('Ownership Party FLag is: '||l_ownership_party);
253 debug('Internal Party Id is : '||l_internal_party_id); --added code for bug #5868111
254
255 -- Get Default CSI Status from Profile
256 OPEN c_id;
257 FETCH c_id into r_id;
258 CLOSE c_id;
259
260 debug('Instance Status from Profile: '||r_id.instance_status_id);
261
262 -- Initialize Transaction Record
263 l_txn_rec := csi_inv_trxs_pkg.init_txn_rec;
264
265 -- Set Status based on redeployment
266 IF l_depreciable = 'N' THEN
267 IF l_mtl_item_tbl(i).serial_number is NOT NULL THEN
268 csi_inv_trxs_pkg.get_redeploy_flag(l_mtl_item_tbl(i).inventory_item_id,
269 l_mtl_item_tbl(i).serial_number,
270 l_sysdate,
271 l_redeploy_flag,
272 l_return_status,
273 l_error_message);
274 END IF;
275
276 IF l_redeploy_flag = 'Y' THEN
277 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
278 ELSE
279 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_complete;
280 END IF;
281 ELSE
282 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
283 END IF;
284
285 IF NOT l_return_status = l_fnd_success THEN
286 debug('Redeploy Flag: '||l_redeploy_flag);
287 debug('You have encountered an error in csi_inv_trxs_pkg.get_redeploy_flag: '||l_error_message);
288 RAISE fnd_api.g_exc_error;
289 END IF;
290
291 debug('Redeploy Flag: '||l_redeploy_flag);
292 debug('Trans Status Code: '||l_txn_rec.transaction_status_code);
293
294 -- Create CSI Transaction to be used
295 l_txn_rec.source_transaction_date := l_mtl_item_tbl(i).transaction_date;
296 l_txn_rec.transaction_date := l_sysdate;
297 l_txn_rec.transaction_type_id :=
298 csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
299 l_txn_rec.transaction_quantity :=
300 l_mtl_item_tbl(i).transaction_quantity;
301 l_txn_rec.transaction_uom_code := l_mtl_item_tbl(i).transaction_uom;
302 l_txn_rec.transacted_by := l_employee_id;
303 l_txn_rec.transaction_action_code := NULL;
304 l_txn_rec.message_id := p_message_id;
305 l_txn_rec.inv_material_transaction_id := p_transaction_id;
306 l_txn_rec.object_version_number := l_object_version_number;
307
308
309 IF l_mtl_item_tbl(i).transaction_type_id = 8 THEN
310 OPEN c_phys_inv_info (l_mtl_item_tbl(i).physical_adjustment_id);
311 FETCH c_phys_inv_info into r_phys_inv_info;
312 CLOSE c_phys_inv_info;
313
314 l_txn_rec.source_header_ref_id := r_phys_inv_info.physical_inventory_id;
315 l_txn_rec.source_header_ref := r_phys_inv_info.physical_inventory_name;
316 l_txn_rec.source_line_ref := r_phys_inv_info.tag_number;
317
318 debug('MMT Phys Adj ID: '||l_mtl_item_tbl(i).physical_adjustment_id);
319 debug('Physical Inventory ID: '||l_txn_rec.source_header_ref_id);
320 debug('Physical Inventory Name: '||l_txn_rec.source_header_ref);
321
322 ELSIF l_mtl_item_tbl(i).transaction_type_id = 4 THEN
323
324 OPEN c_cycle_count_info (l_mtl_item_tbl(i).cycle_count_id);
325 FETCH c_cycle_count_info into r_cycle_count_info;
326 CLOSE c_cycle_count_info;
327
328 l_txn_rec.source_header_ref_id := r_cycle_count_info.cycle_count_header_id;
329 l_txn_rec.source_header_ref := r_cycle_count_info.cycle_count_header_name;
330
331 debug('MMT Cycle Count ID: '||l_mtl_item_tbl(i).cycle_count_id);
332 debug('Cycle Count ID: '||l_txn_rec.source_header_ref_id);
333 debug('Cycle Count Name: '||l_txn_rec.source_header_ref);
334
335 END IF;
336
337 csi_inv_trxs_pkg.create_csi_txn(l_txn_rec,
338 l_error_message,
339 l_return_status);
340
341 debug('CSI Transaction Created: '||l_txn_rec.transaction_id);
342
343 IF NOT l_return_status = l_fnd_success THEN
344 debug('You have encountered an error in csi_inv_trxs_pkg.create_csi_txn: '||p_transaction_id);
345 RAISE fnd_api.g_exc_error;
346 END IF;
347
348 -- Now loop through the PL/SQL Table.
349 j := 1;
350
351 debug('Starting to loop through Material Transaction Records');
352
353 FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
354
355 debug('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
356 debug('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
357 debug('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
358 debug('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
359
360 IF l_mtl_item_tbl(j).serial_number IS NOT NULL THEN -- Serialized
361
362 csi_inv_trxs_pkg.set_item_attr_query_values(l_mtl_item_tbl,
363 j,
364 NULL,
365 l_instance_query_rec,
366 x_return_status);
367
368 csi_t_gen_utility_pvt.dump_instance_query_rec(p_instance_query_rec => l_instance_query_rec);
369
370 debug('Calling get_item_instance');
371
372 csi_item_instance_pub.get_item_instances(l_api_version,
373 l_commit,
374 l_init_msg_list,
375 l_validation_level,
376 l_instance_query_rec,
377 l_party_query_rec,
378 l_account_query_rec,
379 l_transaction_id,
380 l_resolve_id_columns,
381 l_inactive_instance_only,
382 l_src_instance_header_tbl,
383 l_return_status,
384 l_msg_count,
385 l_msg_data);
386
387 debug('After get_item_instance');
388
389 l_tbl_count := 0;
390 l_tbl_count := l_src_instance_header_tbl.count;
391
392 debug('Source Records Found: '||l_tbl_count);
393
394 -- Check for any errors and add them to the message stack to pass out to be put into the
395 -- error log table.
396 IF NOT l_return_status = l_fnd_success then
397 debug('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
398 l_msg_index := 1;
399 WHILE l_msg_count > 0 loop
400 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
401 l_msg_index := l_msg_index + 1;
402 l_msg_count := l_msg_count - 1;
403 END LOOP;
404 RAISE fnd_api.g_exc_error;
405 END IF;
406
407 IF l_src_instance_header_tbl.count = 0 THEN -- No Records found so Create Serialized record
408 debug('No Records found so Create a Serialized Record');
409
410 l_new_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
411 l_new_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
412 l_new_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
413 l_new_instance_rec.inv_master_organization_id := l_master_organization_id;
414 l_new_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
415 l_new_instance_rec.serial_number := l_mtl_item_tbl(j).serial_number;
416 l_new_instance_rec.mfg_serial_number_flag := 'Y';
417 l_new_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
418 l_new_instance_rec.quantity := 1;
419 l_new_instance_rec.active_start_date := l_sysdate;
420 l_new_instance_rec.active_end_date := NULL;
421 l_new_instance_rec.unit_of_measure := l_mtl_item_tbl(j).primary_uom_code;
422 l_new_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
423 l_new_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
424 l_new_instance_rec.instance_usage_code := l_in_inventory;
425 l_new_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
426 l_new_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
427 l_new_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
428 l_new_instance_rec.customer_view_flag := 'N';
429 l_new_instance_rec.merchant_view_flag := 'Y';
430 l_new_instance_rec.object_version_number := l_object_version_number;
431 l_new_instance_rec.operational_status_code := 'NOT_USED';
432 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
433 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
434 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
435 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
436 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
437 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
438
439 l_new_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
440
441 debug('Instance_status_id Value: '||nvl(l_new_instance_rec.instance_status_id,-1));
442 debug('You will now Create a new Item Instance Record');
443 debug('Serial Number: '||l_new_instance_rec.serial_number);
444
445 csi_item_instance_pub.create_item_instance(l_api_version,
446 l_commit,
447 l_init_msg_list,
448 l_validation_level,
449 l_new_instance_rec,
450 l_ext_attrib_values_tbl,
451 l_party_tbl,
452 l_account_tbl,
453 l_pricing_attrib_tbl,
454 l_org_assignments_tbl,
455 l_asset_assignment_tbl,
456 l_txn_rec,
457 l_return_status,
458 l_msg_count,
459 l_msg_data);
460
461 -- Check for any errors and add them to the message stack to pass out to be put into the
462 -- error log table.
463 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
464 debug('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
465 l_msg_index := 1;
466 WHILE l_msg_count > 0 loop
467 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
468 l_msg_index := l_msg_index + 1;
469 l_msg_count := l_msg_count - 1;
470 END LOOP;
471 RAISE fnd_api.g_exc_error;
472 END IF;
473
474 debug('Item Instance Created: '||l_new_instance_rec.instance_id);
475
476 ELSIF l_src_instance_header_tbl.count = 1 THEN
477
478 debug('Records were found');
479
480 IF l_src_instance_header_tbl(i).instance_usage_code in (l_out_of_service,
481 l_in_inventory,
482 l_installed,
483 l_in_service,
484 l_in_process) THEN
485
486 debug('Update Serialized Item which is OUT NOCOPY Of Service');
487 debug('Serial Number is: '||l_src_instance_header_tbl(i).serial_number);
488
489 l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
490 l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
491 l_update_instance_rec.quantity := 1;
492 l_update_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
493 -- Added for bug 5975739
494 l_update_instance_rec.inv_master_organization_id := l_master_organization_id;
495 l_update_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
496 l_update_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
497 l_update_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
498 l_update_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
499 --l_update_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
500 l_update_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
501 l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
502 l_update_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
503 l_update_instance_rec.instance_usage_code := l_in_inventory;
504 l_update_instance_rec.active_end_date := NULL;
505 l_update_instance_rec.pa_project_id := NULL;
506 l_update_instance_rec.pa_project_task_id := NULL;
507 l_update_instance_rec.install_location_type_code := NULL;
508 l_update_instance_rec.install_location_id := NULL;
509 l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
510 l_update_instance_rec.instance_status_id := l_src_instance_header_tbl(i).instance_status_id;
511
512 l_party_tbl.delete;
513 l_account_tbl.delete;
514 l_pricing_attrib_tbl.delete;
515 l_org_assignments_tbl.delete;
516 l_asset_assignment_tbl.delete;
517
518 -- Bug 9091915
519 -- When instance status id is available for a source instance
520 -- the status id should not be updated
521 IF NVL(l_update_instance_rec.instance_status_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM OR CSI_Item_Instance_vld_pvt.val_inst_ter_flag(l_update_instance_rec.instance_status_id) THEN
522 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);
523 END IF;
524
525 debug('Before Update Item Instance');
526
527 csi_item_instance_pub.update_item_instance(l_api_version,
528 l_commit,
529 l_init_msg_list,
530 l_validation_level,
531 l_update_instance_rec,
532 l_ext_attrib_values_tbl,
533 l_party_tbl,
534 l_account_tbl,
535 l_pricing_attrib_tbl,
536 l_org_assignments_tbl,
537 l_asset_assignment_tbl,
538 l_txn_rec,
539 l_instance_id_lst,
540 l_return_status,
541 l_msg_count,
542 l_msg_data);
543
544 l_upd_error_instance_id := NULL;
545 l_upd_error_instance_id := l_update_instance_rec.instance_id;
546
547 debug('Update of Item instance that is '||l_src_instance_header_tbl(i).instance_usage_code);
548 debug('Update Item Instance is: '||l_update_instance_rec.instance_id);
549 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
550
551 -- Check for any errors and add them to the message stack to pass out to be put into the
552 -- error log table.
553 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
554 debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
555 l_msg_index := 1;
556 WHILE l_msg_count > 0 loop
557 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
558 l_msg_index := l_msg_index + 1;
559 l_msg_count := l_msg_count - 1;
560 END LOOP;
561 RAISE fnd_api.g_exc_error;
562 END IF;
563
564 ELSIF l_src_instance_header_tbl(i).instance_usage_code in (l_out_of_enterprise,l_in_relationship,l_in_wip) THEN
565
566 IF l_ownership_party = 'Y' THEN
567
568 IF l_src_instance_header_tbl(i).instance_usage_code = l_in_relationship THEN
569 debug('Check and Break Relationship for Instance :'||l_src_instance_header_tbl(i).instance_id);
570
571 csi_process_txn_pvt.check_and_break_relation(l_src_instance_header_tbl(i).instance_id,
572 l_txn_rec,
573 l_return_status);
574
575 IF NOT l_return_status = l_fnd_success then
576 debug('You encountered an error in the se_inv_trxs_pkg.check_and_break_relation');
577 l_error_message := csi_t_gen_utility_pvt.dump_error_stack;
578 RAISE fnd_api.g_exc_error;
579 END IF;
580
581 debug('Object Version originally from instance: '||l_src_instance_header_tbl(i).object_version_number);
582
583 OPEN c_obj_version (l_src_instance_header_tbl(i).instance_id);
584 FETCH c_obj_version into l_src_instance_header_tbl(i).object_version_number;
585 CLOSE c_obj_version;
586
587 debug('Current Object Version after check and break :'||l_src_instance_header_tbl(i).object_version_number);
588
589 END IF; -- Check and Break
590
591 debug('Update Serialized Item which is :'||l_src_instance_header_tbl(i).instance_usage_code);
592 debug('Serial Number is: '||l_src_instance_header_tbl(i).serial_number);
593
594 l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
595 l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
596 l_update_instance_rec.quantity := 1;
597 l_update_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
598 l_update_instance_rec.mfg_serial_number_flag := 'Y';
599 -- Added for Bug 5975739
600 l_update_instance_rec.inv_master_organization_id := l_master_organization_id;
601 l_update_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
602 l_update_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
603 l_update_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
604 l_update_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
605 --l_update_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
606 l_update_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
607 l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
608 l_update_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
609 l_update_instance_rec.instance_usage_code := l_in_inventory;
610 l_update_instance_rec.active_end_date := NULL;
611 l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
612 --bnarayan for the bug4540920
613 l_update_instance_rec.install_location_type_code := NULL;
614 l_update_instance_rec.install_location_id := NULL;
615 l_update_instance_rec.instance_status_id := l_src_instance_header_tbl(i).instance_status_id;
616
617
618 -- code added for bug #5868111....start here
619
620 IF l_ownership_party = 'Y' THEN
621
622 -- Get Owner Party ID of the Instance.
623
624 BEGIN
625 SELECT owner_party_id
626 INTO l_owner_party_id
627 FROM csi_item_instances
628 WHERE instance_id = l_src_instance_header_tbl(i).instance_id;
629
630 EXCEPTION
631 WHEN no_data_found THEN
632 l_owner_party_id := -99999;
633 END;
634
635 -- code added for bug #5868111....end here
636
637
638 -- We want to change the party of this back
639 -- to the Internal Party
640
641 debug('Usage is '||l_src_instance_header_tbl(i).instance_usage_code);
642 debug('We need to bring this back into Inventory and change the Owner Party back to the Internal Party if the Instance is not already at the Internal Party'); --added code for bug #5868111
643 debug('Current Owner Party; '||l_owner_party_id); --added code for bug #5868111
644 debug('Owner Party : '||l_owner_party_id); --added code for bug #5868111
645 debug('Internal Party: '||l_internal_party_id); --added code for bug #5868111
646
647
648 IF l_owner_party_id <> l_internal_party_id THEN --added code for bug #5868111
649
650 -- Set Instance ID so it will query the child recs for this
651 -- Instance.
652
653 l_instance_header_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
654 -- Call details to get Party Information
655 csi_item_instance_pub.get_item_instance_details
656 (l_api_version,
657 l_commit,
658 l_init_msg_list,
659 l_validation_level,
660 l_instance_header_rec,
661 fnd_api.g_true, -- Get Parties
662 l_party_header_tbl,
663 fnd_api.g_false, -- Get Accounts
664 l_account_header_tbl,
665 fnd_api.g_false, -- Get Org Assi.
666 l_org_header_tbl,
667 fnd_api.g_false, -- Get Price Att
668 l_pricing_header_tbl,
669 fnd_api.g_false, -- Get Ext Attr
670 l_ext_attrib_header_tbl,
671 l_ext_attrib_def_tbl,
672 fnd_api.g_false, -- Get Asset Assi
673 l_asset_header_tbl,
674 fnd_api.g_false, -- Resolve IDs
675 NULL, -- Time Stamp
676 l_return_status,
677 l_msg_count,
678 l_msg_data);
679
680 -- Now create a new owner record that will be used to create
681 -- the new owner party and set it back to an internal party owner
682 -- The PL/SQL Table will now be set so that it can be passed into
683 -- the next procedure.
684
685 FOR p in l_party_header_tbl.FIRST .. l_party_header_tbl.LAST LOOP
686 IF l_party_header_tbl(p).relationship_type_code = 'OWNER' THEN
687 debug('Found the OWNER party so updating this back to the Internal Party ID');
688
689 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
690 l_party_tbl(i).instance_id := l_src_instance_header_tbl(i).instance_id;
691 l_party_tbl(i).instance_party_id := l_party_header_tbl(p).instance_party_id;
692 l_party_tbl(i).object_version_number := l_party_header_tbl(p).object_version_number;
693 debug('After finding the OWNER party and updating this back to the Internal Party ID');
694 END IF;-- Owner Party
695 END LOOP;
696
697 debug('Inst Party ID :'||l_party_tbl(i).instance_party_id);
698 debug('Party Inst ID :'||l_party_tbl(i).instance_id);
699 debug('Party Source Table :'||l_party_tbl(i).party_source_table);
700 debug('Party ID :'||l_party_tbl(i).party_id);
701 debug('Rel Type Code :'||l_party_tbl(i).relationship_type_code);
702 debug('Contact Flag :'||l_party_tbl(i).contact_flag);
703 debug('Object Version Number:' ||l_party_tbl(i).object_version_number);
704
705 --code added for bug #5868111....start here
706
707 ELSE --Instance is already at Internal Party
708 l_party_tbl.delete;
709
710 END IF; -- Party Header vs Int Party Id
711
712 ELSE -- Ownership "N"
713
714 debug('Ownership Override is "N" so get the Owner Party ID and compare to the Internal Party ID');
715
716
717 BEGIN
718 SELECT owner_party_id
719 INTO l_owner_party_id
720 FROM csi_item_instances
721 WHERE instance_id = l_src_instance_header_tbl(i).instance_id;
722
723 EXCEPTION
724 WHEN no_data_found THEN
725 l_owner_party_id := -99999;
726 END;
727
728 debug('Owner Party : '||l_owner_party_id);
729 debug('Internal Party: '||l_internal_party_id);
730
731 IF l_owner_party_id <> l_internal_party_id THEN
732
733 l_status := 'In Inventory, Out of Service, Installed, In Process or In Service ';
734 debug('Serialized Item with In Inventory, Out of Service, Installed, In Process or In Service exists however the ownership_override_at_txn flag is set to N');
735 debug('The current owner party is not the Internal Party so we will NOT bring this back into inventory');
736 debug('Instance Usage Code is: '||l_src_instance_header_tbl(i).instance_usage_code);
737
738 fnd_message.set_name('CSI','CSI_SERIALIZED_ITEM_EXISTS');
739 fnd_message.set_token('STATUS',l_status);
740 l_error_message := fnd_message.get;
741 l_return_status := l_fnd_error;
742 RAISE fnd_api.g_exc_error;
743 ELSE
744 l_party_tbl.delete;
745 END IF;
746 END IF;
747
748
749 --code added for bug #5868111....end here
750
751 l_account_tbl.delete;
752 l_pricing_attrib_tbl.delete;
753 l_org_assignments_tbl.delete;
754 l_asset_assignment_tbl.delete;
755
756 -- Bug 9091915
757 -- When instance status id is available for a source instance
758 -- the status id should not be updated
759 IF NVL(l_update_instance_rec.instance_status_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM OR CSI_Item_Instance_vld_pvt.val_inst_ter_flag(l_update_instance_rec.instance_status_id) THEN
760 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);
761 END IF;
762
763 debug('Before Update Item Instance');
764
765 csi_item_instance_pub.update_item_instance(l_api_version,
766 l_commit,
767 l_init_msg_list,
768 l_validation_level,
769 l_update_instance_rec,
770 l_ext_attrib_values_tbl,
771 l_party_tbl,
772 l_account_tbl,
773 l_pricing_attrib_tbl,
774 l_org_assignments_tbl,
775 l_asset_assignment_tbl,
776 l_txn_rec,
777 l_instance_id_lst,
778 l_return_status,
779 l_msg_count,
780 l_msg_data);
781
782 l_upd_error_instance_id := NULL;
783 l_upd_error_instance_id := l_update_instance_rec.instance_id;
784
785 debug('Update of Item instance that is '||l_src_instance_header_tbl(i).instance_usage_code); --code added for bug #5868111
786 debug('Update Item Instance is: '||l_update_instance_rec.instance_id);
787 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
788
789 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
790 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
791 debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
792 l_msg_index := 1;
793 WHILE l_msg_count > 0 loop
794 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
795 l_msg_index := l_msg_index + 1;
796 l_msg_count := l_msg_count - 1;
797 END LOOP;
798 RAISE fnd_api.g_exc_error;
799 END IF;
800 ELSE
801 l_status := 'In Inventory, Out of Service, Out of Enterprise, In Relationship, Installed, In Service or In Process';
802 debug('Serialized Item with Out of Enterprise or In Relationship exists however the ownership_override_at_txn flag is set to N so we will NOT bring this back into inventory');
803 debug('Instance Usage Code is: '||l_src_instance_header_tbl(i).instance_usage_code);
804 fnd_message.set_name('CSI','CSI_SERIALIZED_ITEM_EXISTS');
805 fnd_message.set_token('STATUS',l_status);
806 l_error_message := fnd_message.get;
807 l_return_status := l_fnd_error;
808 RAISE fnd_api.g_exc_error;
809 END IF;
810 ELSE
811 l_status := 'In Inventory, Out of Service, Installed, In Service or In Process';
812 debug('Serialized Item with Status other then Out Of Service, In Inventory, Installed, or In Process already exists in Install Base');
813 debug('Instance Usage Code is: '||l_src_instance_header_tbl(i).instance_usage_code);
814 fnd_message.set_name('CSI','CSI_SERIALIZED_ITEM_EXISTS');
815 fnd_message.set_token('STATUS',l_status);
816 l_error_message := fnd_message.get;
817 l_return_status := l_fnd_error;
818 RAISE fnd_api.g_exc_error;
819 END IF;
820 ELSE -- No API Records so create a new serialized record
821 l_new_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
822 l_new_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
823 l_new_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
824 l_new_instance_rec.inv_master_organization_id := l_master_organization_id;
825 l_new_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
826 l_new_instance_rec.serial_number := l_mtl_item_tbl(j).serial_number;
827 l_new_instance_rec.mfg_serial_number_flag := 'Y';
828 l_new_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
829 l_new_instance_rec.quantity := 1;
830 l_new_instance_rec.active_start_date := l_sysdate;
831 l_new_instance_rec.active_end_date := NULL;
832 l_new_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
833 l_new_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
834 l_new_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
835 l_new_instance_rec.instance_usage_code := l_in_inventory;
836 l_new_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
837 l_new_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
838 l_new_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
839 l_new_instance_rec.customer_view_flag := 'N';
840 l_new_instance_rec.merchant_view_flag := 'Y';
841 l_new_instance_rec.object_version_number := l_object_version_number;
842 l_new_instance_rec.operational_status_code := 'NOT_USED';
843 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
844 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
845 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
846 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
847 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
848 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
849
850 l_new_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
851
852 debug('You will now Create a new Item Instance Record');
853
854 csi_item_instance_pub.create_item_instance(l_api_version,
855 l_commit,
856 l_init_msg_list,
857 l_validation_level,
858 l_new_instance_rec,
859 l_ext_attrib_values_tbl,
860 l_party_tbl,
861 l_account_tbl,
862 l_pricing_attrib_tbl,
863 l_org_assignments_tbl,
864 l_asset_assignment_tbl,
865 l_txn_rec,
866 l_return_status,
867 l_msg_count,
868 l_msg_data);
869
870 -- Check for any errors and add them to the message stack to pass out to be put into the
871 -- error log table.
872 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
873 debug('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
874 l_msg_index := 1;
875 WHILE l_msg_count > 0 loop
876 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
877 l_msg_index := l_msg_index + 1;
878 l_msg_count := l_msg_count - 1;
879 END LOOP;
880 RAISE fnd_api.g_exc_error;
881 END IF;
882
883 debug('Item Instance Created: '||l_new_instance_rec.instance_id);
884
885 END IF; -- End of Serialized Source Block
886
887 ELSIF l_mtl_item_tbl(j).serial_number IS NULL THEN -- Non Serialized
888
889 csi_inv_trxs_pkg.set_item_attr_query_values(l_mtl_item_tbl,
890 j,
891 NULL,
892 l_instance_query_rec,
893 x_return_status);
894
895 l_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
896 l_instance_query_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
897 l_instance_query_rec.instance_usage_code := l_in_inventory;
898 l_instance_query_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id; -- added Bug 11061556
899
900 csi_t_gen_utility_pvt.dump_instance_query_rec(p_instance_query_rec => l_instance_query_rec);
901
902
903 debug('Calling get_item_instance');
904
905 csi_item_instance_pub.get_item_instances(l_api_version,
906 l_commit,
907 l_init_msg_list,
908 l_validation_level,
909 l_instance_query_rec,
910 l_party_query_rec,
911 l_account_query_rec,
912 l_transaction_id,
913 l_resolve_id_columns,
914 l_inactive_instance_only,
915 l_src_instance_header_tbl,
916 l_return_status,
917 l_msg_count,
918 l_msg_data);
919
920 debug('After get_item_instance');
921
922 l_tbl_count := 0;
923 l_tbl_count := l_src_instance_header_tbl.count;
924
925 debug('Source Records Found: '||l_tbl_count);
926
927 -- Check for any errors and add them to the message stack to pass out to be put into the
928 -- error log table.
929 IF NOT l_return_status = l_fnd_success then
930 debug('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
931 l_msg_index := 1;
932 WHILE l_msg_count > 0 loop
933 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
934 l_msg_index := l_msg_index + 1;
935 l_msg_count := l_msg_count - 1;
936 END LOOP;
937 RAISE fnd_api.g_exc_error;
938 END IF;
939
940 IF l_src_instance_header_tbl.count = 0 THEN -- No Records found so Create either Serialized or Non Serialized
941 debug('No Records found so Create a Record for Non-Serialized');
942
943 l_new_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
944 l_new_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
945 l_new_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
946 l_new_instance_rec.inv_master_organization_id := l_master_organization_id;
947 l_new_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
948 l_new_instance_rec.mfg_serial_number_flag := 'N';
949 l_new_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
950 l_new_instance_rec.quantity := abs(l_mtl_item_tbl(j).transaction_quantity);
951 l_new_instance_rec.active_start_date := l_sysdate;
952 l_new_instance_rec.active_end_date := NULL;
953 l_new_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
954 l_new_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
955 l_new_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
956 l_new_instance_rec.instance_usage_code := l_in_inventory;
957 l_new_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
958 l_new_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
959 l_new_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
960 l_new_instance_rec.customer_view_flag := 'N';
961 l_new_instance_rec.merchant_view_flag := 'Y';
962 l_new_instance_rec.object_version_number := l_object_version_number;
963 l_new_instance_rec.operational_status_code := 'NOT_USED';
964 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
965 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
966 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
967 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
968 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
969 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
970
971 l_new_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
972
973 debug('You will now Create a new Item Instance Record');
974
975 csi_item_instance_pub.create_item_instance(l_api_version,
976 l_commit,
977 l_init_msg_list,
978 l_validation_level,
979 l_new_instance_rec,
980 l_ext_attrib_values_tbl,
981 l_party_tbl,
982 l_account_tbl,
983 l_pricing_attrib_tbl,
984 l_org_assignments_tbl,
985 l_asset_assignment_tbl,
986 l_txn_rec,
987 l_return_status,
988 l_msg_count,
989 l_msg_data);
990
991 -- Check for any errors and add them to the message stack to pass out to be put into the
992 -- error log table.
993 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
994 debug('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
995 l_msg_index := 1;
996 WHILE l_msg_count > 0 loop
997 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
998 l_msg_index := l_msg_index + 1;
999 l_msg_count := l_msg_count - 1;
1000 END LOOP;
1001 RAISE fnd_api.g_exc_error;
1002 END IF;
1003 debug('Item Instance Created: '||l_new_instance_rec.instance_id);
1004
1005 ELSIF l_src_instance_header_tbl.count = 1 THEN
1006 -- Update Non Serialized Item
1007 debug('1 Instance Record was found');
1008
1009 debug('Update the Non-Serialized, In-Inventory Item Instance record');
1010
1011 l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1012 l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
1013 l_update_instance_rec.quantity := l_src_instance_header_tbl(i).quantity + abs(l_mtl_item_tbl(j).primary_quantity);
1014 l_update_instance_rec.active_end_date := NULL;
1015 l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
1016 l_update_instance_rec.instance_status_id := l_src_instance_header_tbl(i).instance_status_id;
1017
1018 l_party_tbl.delete;
1019 l_account_tbl.delete;
1020 l_pricing_attrib_tbl.delete;
1021 l_org_assignments_tbl.delete;
1022 l_asset_assignment_tbl.delete;
1023
1024 -- Bug 10081555
1025 -- If l_src_instance_header_tbl(i).quantity = 0
1026 -- remove the termination status
1027 IF l_src_instance_header_tbl(i).quantity = 0 THEN
1028 l_update_instance_rec.instance_status_id := FND_API.G_MISS_NUM;
1029 END IF;
1030
1031 -- Bug 9091915
1032 -- When instance status id is available for a source instance
1033 -- the status id should not be updated
1034 IF NVL(l_update_instance_rec.instance_status_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM OR CSI_Item_Instance_vld_pvt.val_inst_ter_flag(l_update_instance_rec.instance_status_id) THEN
1035 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);
1036 END IF;
1037
1038 debug('Before Update Item Instance');
1039
1040 csi_item_instance_pub.update_item_instance(l_api_version,
1041 l_commit,
1042 l_init_msg_list,
1043 l_validation_level,
1044 l_update_instance_rec,
1045 l_ext_attrib_values_tbl,
1046 l_party_tbl,
1047 l_account_tbl,
1048 l_pricing_attrib_tbl,
1049 l_org_assignments_tbl,
1050 l_asset_assignment_tbl,
1051 l_txn_rec,
1052 l_instance_id_lst,
1053 l_return_status,
1054 l_msg_count,
1055 l_msg_data);
1056
1057 l_upd_error_instance_id := NULL;
1058 l_upd_error_instance_id := l_update_instance_rec.instance_id;
1059
1060 debug('Item Instance Updated: '||l_update_instance_rec.instance_id);
1061 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1062
1063 -- Check for any errors and add them to the message stack to pass out to be put into the
1064 -- error log table.
1065 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1066 debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
1067 l_msg_index := 1;
1068 WHILE l_msg_count > 0 loop
1069 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1070 l_msg_index := l_msg_index + 1;
1071 l_msg_count := l_msg_count - 1;
1072 END LOOP;
1073 RAISE fnd_api.g_exc_error;
1074 END IF;
1075
1076 ELSIF l_src_instance_header_tbl.count > 1 THEN
1077 -- Multiple Instances were found so throw error
1078 debug('Multiple Instances were Found in Install Base Base-20');
1079 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
1080 fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
1081 fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
1082 fnd_message.set_token('INV_ORG_ID',l_mtl_item_tbl(j).organization_id);
1083 fnd_message.set_token('LOCATOR',l_mtl_item_tbl(j).locator_id);
1084 l_error_message := fnd_message.get;
1085 RAISE fnd_api.g_exc_error;
1086
1087 END IF; -- End of Source Record If
1088 END IF; -- End of Serialized Item If
1089 END LOOP; -- End of For Loop
1090
1091 debug('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
1092 debug('*****End of csi_inv_trxs_pkg.misc_receipt Transaction*****');
1093
1094 EXCEPTION
1095 WHEN fnd_api.g_exc_error THEN
1096 debug('You have encountered a "fnd_api.g_exc_error" exception');
1097 x_return_status := l_fnd_error;
1098
1099 IF l_mtl_item_tbl.count > 0 THEN
1100 x_trx_error_rec.serial_number := l_mtl_item_tbl(j).serial_number;
1101 x_trx_error_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1102 x_trx_error_rec.instance_id := l_upd_error_instance_id;
1103 x_trx_error_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1104 x_trx_error_rec.src_serial_num_ctrl_code := l_mtl_item_tbl(j).serial_number_control_code;
1105 x_trx_error_rec.src_location_ctrl_code := l_mtl_item_tbl(j).location_control_code;
1106 x_trx_error_rec.src_lot_ctrl_code := l_mtl_item_tbl(j).lot_control_code;
1107 x_trx_error_rec.src_rev_qty_ctrl_code := l_mtl_item_tbl(j).revision_qty_control_code;
1108 x_trx_error_rec.comms_nl_trackable_flag := l_mtl_item_tbl(j).comms_nl_trackable_flag;
1109 x_trx_error_rec.transaction_error_date := l_sysdate ;
1110 END IF;
1111
1112 x_trx_error_rec.error_text := l_error_message;
1113 x_trx_error_rec.transaction_id := NULL;
1114 x_trx_error_rec.source_type := 'CSIMSRCV';
1115 x_trx_error_rec.source_id := p_transaction_id;
1116 x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
1117 x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
1118 x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
1119 x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
1120
1121 WHEN others THEN
1122 l_sql_error := SQLERRM;
1123 debug('You have encountered a "others" exception');
1124 debug('SQL Error: '||l_sql_error);
1125 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
1126 fnd_message.set_token('API_NAME',l_api_name);
1127 fnd_message.set_token('SQL_ERROR',SQLERRM);
1128 x_return_status := l_fnd_unexpected;
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.comms_nl_trackable_flag := l_mtl_item_tbl(j).comms_nl_trackable_flag;
1140 x_trx_error_rec.transaction_error_date := l_sysdate ;
1141 END IF;
1142
1143 x_trx_error_rec.error_text := fnd_message.get;
1144 x_trx_error_rec.transaction_id := NULL;
1145 x_trx_error_rec.source_type := 'CSIMSRCV';
1146 x_trx_error_rec.source_id := p_transaction_id;
1147 x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
1148 x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
1149 x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
1150 x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
1151
1152 END misc_receipt;
1153
1154 PROCEDURE receipt_inventory(p_transaction_id IN NUMBER,
1155 p_message_id IN NUMBER,
1156 x_return_status OUT NOCOPY VARCHAR2,
1157 x_trx_error_rec OUT NOCOPY CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC)
1158 IS
1159
1160 l_mtl_item_tbl CSI_INV_TRXS_PKG.MTL_ITEM_TBL_TYPE;
1161 l_api_name VARCHAR2(100) := 'CSI_INV_TRXS_PKG.RECEIPT_INVENTORY';
1162 l_api_version NUMBER := 1.0;
1163 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1164 l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
1165 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
1166 l_active_instance_only VARCHAR2(10) := FND_API.G_TRUE;
1167 l_inactive_instance_only VARCHAR2(10) := FND_API.G_FALSE;
1168 l_resolve_id_columns VARCHAR2(10) := FND_API.G_FALSE;
1169 l_transaction_id NUMBER := NULL;
1170 l_object_version_number NUMBER := 1;
1171 l_sysdate DATE := SYSDATE;
1172 l_master_organization_id NUMBER;
1173 l_depreciable VARCHAR2(1);
1174 l_instance_query_rec CSI_DATASTRUCTURES_PUB.INSTANCE_QUERY_REC;
1175 l_update_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
1176 l_new_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
1177 l_txn_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_REC;
1178 l_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
1179 l_api_src_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
1180 l_return_status VARCHAR2(1);
1181 l_error_code VARCHAR2(50);
1182 l_error_message VARCHAR2(4000);
1183 l_instance_id_lst CSI_DATASTRUCTURES_PUB.ID_TBL;
1184 l_party_query_rec CSI_DATASTRUCTURES_PUB.PARTY_QUERY_REC;
1185 l_account_query_rec CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_QUERY_REC;
1186 l_src_instance_header_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_HEADER_TBL;
1187 l_ext_attrib_values_tbl CSI_DATASTRUCTURES_PUB.EXTEND_ATTRIB_VALUES_TBL;
1188 l_party_tbl CSI_DATASTRUCTURES_PUB.PARTY_TBL;
1189 l_account_tbl CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_TBL;
1190 l_pricing_attrib_tbl CSI_DATASTRUCTURES_PUB.PRICING_ATTRIBS_TBL;
1191 l_org_assignments_tbl CSI_DATASTRUCTURES_PUB.ORGANIZATION_UNITS_TBL;
1192 l_asset_assignment_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_ASSET_TBL;
1193 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1194 l_fnd_warning VARCHAR2(1) := 'W';
1195 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
1196 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
1197 l_in_inventory VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_INVENTORY;
1198 l_in_process VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_PROCESS;
1199 l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
1200 l_out_of_enterprise VARCHAR2(25) := 'OUT_OF_ENTERPRISE';
1201 l_in_relationship VARCHAR2(25) := 'IN_RELATIONSHIP';
1202 l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
1203 l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
1204 l_installed VARCHAR2(25) := CSI_INV_TRXS_PKG.G_INSTALLED;
1205 l_in_wip VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_WIP;
1206 l_transaction_error_id NUMBER;
1207 l_quantity NUMBER;
1208 l_mfg_serial_flag VARCHAR2(1);
1209 l_trans_status_code VARCHAR2(15);
1210 l_ins_number VARCHAR2(100);
1211 l_ins_id NUMBER;
1212 l_file VARCHAR2(500);
1213 l_status VARCHAR2(1000);
1214 l_msg_count NUMBER;
1215 l_msg_data VARCHAR2(2000);
1216 l_sql_error VARCHAR2(2000);
1217 l_msg_index NUMBER;
1218 l_employee_id NUMBER;
1219 j PLS_INTEGER;
1220 i PLS_INTEGER := 1;
1221 p PLS_INTEGER := 1;
1222 l_tbl_count NUMBER :=0;
1223 l_sql VARCHAR2(2000);
1224 l_ownership_party VARCHAR2(1);
1225 l_internal_party_id NUMBER; --added code for bug #5868111
1226 l_owner_party_id NUMBER; --added code for bug #5868111
1227 l_redeploy_flag VARCHAR2(1);
1228 l_upd_error_instance_id NUMBER := NULL;
1229
1230 l_instance_header_rec csi_datastructures_pub.instance_header_rec;
1231 l_party_header_tbl csi_datastructures_pub.party_header_tbl;
1232 l_account_header_tbl csi_datastructures_pub.party_account_header_tbl;
1233 l_org_header_tbl csi_datastructures_pub.org_units_header_tbl;
1234 l_pricing_header_tbl csi_datastructures_pub.pricing_attribs_tbl;
1235 l_ext_attrib_header_tbl csi_datastructures_pub.extend_attrib_values_tbl;
1236 l_ext_attrib_def_tbl csi_datastructures_pub.extend_attrib_tbl;
1237 l_asset_header_tbl csi_datastructures_pub.instance_asset_header_tbl;
1238
1239 CURSOR c_po_info (pc_po_distribution_id in number) is
1240 SELECT pod.po_header_id po_header_id,
1241 pod.po_line_id po_line_id,
1242 pol.line_num po_line_number,
1243 poh.segment1 po_number,
1244 pol.unit_price unit_price,
1245 poh.currency_code currency_code
1246 FROM po_distributions_all pod,
1247 po_headers_all poh,
1248 po_lines_all pol
1249 WHERE pod.po_distribution_id = pc_po_distribution_id
1250 AND pod.po_header_id = poh.po_header_id
1251 AND pod.po_line_id = pol.po_line_id
1252 AND poh.po_header_id = pol.po_header_id;
1253
1254 r_po_info c_po_info%rowtype;
1255
1256 cursor c_id is
1257 SELECT instance_status_id
1258 FROM csi_instance_statuses
1259 WHERE name = FND_PROFILE.VALUE('CSI_DEFAULT_INSTANCE_STATUS');
1260
1261 r_id c_id%rowtype;
1262
1263 CURSOR c_obj_version (pc_instance_id IN NUMBER) is
1264 SELECT object_version_number
1265 FROM csi_item_instances
1266 WHERE instance_id = pc_instance_id;
1267
1268 BEGIN
1269 x_return_status := l_fnd_success;
1270
1271 debug('*****Start of csi_inv_trxs_pkg.receipt_inventory Transaction procedure*****');
1272 debug('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
1273 debug('Transaction You are Processing is: '||p_transaction_id);
1274
1275 -- This procedure queries all of the Inventory Transaction Records and
1276 -- returns them as a table.
1277
1278 debug('Executing csi_inv_trxs_pkg.get_transaction_recs');
1279
1280 csi_inv_trxs_pkg.get_transaction_recs(p_transaction_id,
1281 l_mtl_item_tbl,
1282 l_return_status,
1283 l_error_message);
1284
1285 l_tbl_count := 0;
1286 l_tbl_count := l_mtl_item_tbl.count;
1287
1288 debug('Source Records Found: '||l_tbl_count);
1289
1290 IF NOT l_return_status = l_fnd_success THEN
1291 debug('You have encountered an error in CSI_INV_TRXS_PKG.get_transaction_recs, Transaction ID: '||p_transaction_id);
1292 RAISE fnd_api.g_exc_error;
1293 END IF;
1294
1295 -- Get the Master Organization ID
1296
1297 debug('Executing csi_inv_trxs_pkg.get_master_organization');
1298
1299 csi_inv_trxs_pkg.get_master_organization(l_mtl_item_tbl(i).organization_id,
1300 l_master_organization_id,
1301 l_return_status,
1302 l_error_message);
1303
1304 IF NOT l_return_status = l_fnd_success THEN
1305 debug('You have encountered an error in csi_inv_trxs_pkg.get_master_organization, Organization ID: '||l_mtl_item_tbl(i).organization_id);
1306 RAISE fnd_api.g_exc_error;
1307 END IF;
1308
1309 -- Call get_fnd_employee_id and get the employee id
1310
1311 debug('Executing csi_inv_trxs_pkg.get_fnd_employee_id');
1312
1313 l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
1314
1315 IF l_employee_id = -1 THEN
1316 debug('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
1317 END IF;
1318
1319 debug('The Employee that is processing this Transaction is: '||l_employee_id);
1320
1321 -- See if this is a depreciable Item to set the status of the transaction record
1322
1323 debug('Executing csi_inv_trxs_pkg.check_depreciable');
1324
1325 csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
1326 l_depreciable,
1327 l_mtl_item_tbl(i).organization_id ); --Added for Bug 13988660
1328
1329 debug('Is this Item ID: '||l_mtl_item_tbl(i).inventory_item_id||', Depreciable :'||l_depreciable);
1330
1331 -- Set the mfg_serial_number_flag and quantity
1332 IF l_mtl_item_tbl(i).serial_number is NULL THEN
1333 l_mfg_serial_flag := 'N';
1334 l_quantity := l_mtl_item_tbl(i).transaction_quantity;
1335 ELSE
1336 l_mfg_serial_flag := 'Y';
1337 l_quantity := 1;
1338 END IF;
1339
1340 -- Get Party ownership Flag
1341 l_ownership_party := csi_datastructures_pub.g_install_param_rec.ownership_override_at_txn;
1342 l_internal_party_id := csi_datastructures_pub.g_install_param_rec.internal_party_id; --added code for bug #5868111
1343
1344 debug('Ownership Flag is: '||l_ownership_party);
1345 debug('Internal Party Id is : '||l_internal_party_id); --added code for bug #5868111
1346
1347 -- Get Default CSI Status from Profile
1348 OPEN c_id;
1349 FETCH c_id into r_id;
1350 CLOSE c_id;
1351
1352 debug('Instance Status from Profile: '||r_id.instance_status_id);
1353
1354 -- Added so that the PO_HEADER_ID and PO_LINE_ID can be added to
1355 -- the transaction record.
1356
1357 OPEN c_po_info (l_mtl_item_tbl(i).po_distribution_id);
1358 FETCH c_po_info into r_po_info;
1359 CLOSE c_po_info;
1360
1361 debug('PO Number: '||r_po_info.po_number);
1362 debug('PO Line Number: '||r_po_info.po_line_number);
1363 debug('PO Header ID: '||r_po_info.po_header_id);
1364 debug('PO Line ID: '||r_po_info.po_line_id);
1365 debug('PO Unit Price: '||r_po_info.unit_price);
1366 debug('PO Currency Code: '||r_po_info.currency_code);
1367
1368 -- Initialize Transaction Record
1369 l_txn_rec := csi_inv_trxs_pkg.init_txn_rec;
1370
1371 -- Set Status based on redeployment
1372 IF l_depreciable = 'N' THEN
1373 IF l_mtl_item_tbl(i).serial_number is NOT NULL THEN
1374 csi_inv_trxs_pkg.get_redeploy_flag(l_mtl_item_tbl(i).inventory_item_id,
1375 l_mtl_item_tbl(i).serial_number,
1376 l_sysdate,
1377 l_redeploy_flag,
1378 l_return_status,
1379 l_error_message);
1380 END IF;
1381 IF l_redeploy_flag = 'Y' THEN
1382 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
1383 ELSE
1384 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_complete;
1385 END IF;
1386 ELSE
1387 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
1388 END IF;
1389
1390 IF NOT l_return_status = l_fnd_success THEN
1391 debug('Redeploy Flag: '||l_redeploy_flag);
1392 debug('You have encountered an error in csi_inv_trxs_pkg.get_redeploy_flag: '||l_error_message);
1393 RAISE fnd_api.g_exc_error;
1394 END IF;
1395
1396 debug('Redeploy Flag: '||l_redeploy_flag);
1397 debug('Trans Status Code: '||l_txn_rec.transaction_status_code);
1398
1399 -- Create CSI Transaction to be used
1400 l_txn_rec.source_transaction_date := l_mtl_item_tbl(i).transaction_date;
1401 l_txn_rec.transaction_date := l_sysdate;
1402 l_txn_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('PO_RECEIPT_INTO_INVENTORY','INV');
1403 l_txn_rec.transaction_quantity := l_mtl_item_tbl(i).transaction_quantity;
1404 l_txn_rec.transaction_uom_code := l_mtl_item_tbl(i).transaction_uom;
1405 l_txn_rec.transacted_by := l_employee_id;
1406 l_txn_rec.transaction_action_code := NULL;
1407 l_txn_rec.message_id := p_message_id;
1408 l_txn_rec.inv_material_transaction_id := p_transaction_id;
1409 l_txn_rec.object_version_number := l_object_version_number;
1410 l_txn_rec.source_dist_ref_id1 := l_mtl_item_tbl(i).po_distribution_id;
1411 l_txn_rec.source_dist_ref_id2 := l_mtl_item_tbl(i).rcv_transaction_id;
1412 l_txn_rec.source_header_ref_id := r_po_info.po_header_id;
1413 l_txn_rec.source_line_ref_id := r_po_info.po_line_id;
1414 l_txn_rec.source_header_ref := r_po_info.po_number;
1415 l_txn_rec.source_line_ref := to_char(r_po_info.po_line_number);
1416
1417 csi_inv_trxs_pkg.create_csi_txn(l_txn_rec,
1418 l_error_message,
1419 l_return_status);
1420
1421 debug('CSI Transaction Created: '||l_txn_rec.transaction_id);
1422
1423 IF NOT l_return_status = l_fnd_success THEN
1424 debug('You have encountered an error in csi_inv_trxs_pkg.create_csi_txn: '||p_transaction_id);
1425 RAISE fnd_api.g_exc_error;
1426 END IF;
1427
1428 -- Now loop through the PL/SQL Table.
1429 j := 1;
1430
1431 debug('Starting to loop through Material Transaction Records');
1432
1433 FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
1434
1435 debug('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
1436 debug('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
1437 debug('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
1438 debug('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
1439 debug('PO_HEADER_ID is: '||r_po_info.po_header_id);
1440 debug('PO_LINE_ID is: '||r_po_info.po_line_id);
1441
1442 IF l_mtl_item_tbl(j).serial_number IS NOT NULL THEN -- Serialized
1443
1444 csi_inv_trxs_pkg.set_item_attr_query_values(l_mtl_item_tbl,
1445 j,
1446 NULL,
1447 l_instance_query_rec,
1448 x_return_status);
1449
1450 csi_t_gen_utility_pvt.dump_instance_query_rec(p_instance_query_rec => l_instance_query_rec);
1451
1452 debug('Before Get Item Instance');
1453
1454 csi_item_instance_pub.get_item_instances(l_api_version,
1455 l_commit,
1456 l_init_msg_list,
1457 l_validation_level,
1458 l_instance_query_rec,
1459 l_party_query_rec,
1460 l_account_query_rec,
1461 l_transaction_id,
1462 l_resolve_id_columns,
1463 l_inactive_instance_only,
1464 l_src_instance_header_tbl,
1465 l_return_status,
1466 l_msg_count,
1467 l_msg_data);
1468
1469 debug('After Get Item Instance');
1470
1471 l_tbl_count := 0;
1472 l_tbl_count := l_src_instance_header_tbl.count;
1473
1474 debug('Source Records Found: '||l_tbl_count);
1475
1476 -- Check for any errors and add them to the message stack to pass out to be put into the
1477 -- error log table.
1478 IF NOT l_return_status = l_fnd_success then
1479 debug('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
1480 l_msg_index := 1;
1481 WHILE l_msg_count > 0 loop
1482 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1483 l_msg_index := l_msg_index + 1;
1484 l_msg_count := l_msg_count - 1;
1485 END LOOP;
1486 RAISE fnd_api.g_exc_error;
1487 END IF;
1488
1489 IF l_src_instance_header_tbl.count < 1 THEN -- No Records found so Create either Serialized Item
1490
1491 debug('No source records were found so create a new one');
1492 l_new_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
1493 l_new_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1494 l_new_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1495 l_new_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
1496 l_new_instance_rec.inv_master_organization_id := l_master_organization_id;
1497 l_new_instance_rec.serial_number := l_mtl_item_tbl(j).serial_number;
1498 l_new_instance_rec.mfg_serial_number_flag := l_mfg_serial_flag;
1499 l_new_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1500 l_new_instance_rec.quantity := abs(l_quantity);
1501 l_new_instance_rec.unit_of_measure := l_mtl_item_tbl(j).primary_uom_code;
1502 l_new_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
1503 l_new_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
1504 l_new_instance_rec.instance_usage_code := l_in_inventory;
1505 l_new_instance_rec.last_po_po_line_id := r_po_info.po_line_id; --5184815
1506 l_new_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
1507 l_new_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
1508 l_new_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
1509 l_new_instance_rec.customer_view_flag := 'N';
1510 l_new_instance_rec.merchant_view_flag := 'Y';
1511 l_new_instance_rec.object_version_number := l_object_version_number;
1512 l_new_instance_rec.operational_status_code := 'NOT_USED';
1513 l_new_instance_rec.active_start_date := l_sysdate;
1514 l_new_instance_rec.active_end_date := NULL;
1515 l_new_instance_rec.purchase_unit_price := r_po_info.unit_price;
1516 l_new_instance_rec.purchase_currency_code := r_po_info.currency_code;
1517
1518 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
1519 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
1520 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
1521 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
1522 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
1523 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
1524
1525 l_new_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
1526
1527 debug('Before Create of new source Item Instance');
1528
1529 csi_item_instance_pub.create_item_instance(l_api_version,
1530 l_commit,
1531 l_init_msg_list,
1532 l_validation_level,
1533 l_new_instance_rec,
1534 l_ext_attrib_values_tbl,
1535 l_party_tbl,
1536 l_account_tbl,
1537 l_pricing_attrib_tbl,
1538 l_org_assignments_tbl,
1539 l_asset_assignment_tbl,
1540 l_txn_rec,
1541 l_return_status,
1542 l_msg_count,
1543 l_msg_data);
1544
1545
1546 debug('After Create Item Instance');
1547 debug('Item Instance Created: '||l_new_instance_rec.instance_id);
1548
1549 -- Check for any errors and add them to the message stack to pass out to be put into the
1550 -- error log table.
1551 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1552 debug('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
1553 l_msg_index := 1;
1554 WHILE l_msg_count > 0 loop
1555 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1556 l_msg_index := l_msg_index + 1;
1557 l_msg_count := l_msg_count - 1;
1558 END LOOP;
1559 RAISE fnd_api.g_exc_error;
1560 END IF;
1561
1562 --ELSIF l_src_instance_header_tbl.count > 0 THEN -- Records Found
1563 ELSIF l_src_instance_header_tbl.count = 1 THEN -- Records Found
1564
1565 IF l_src_instance_header_tbl(i).instance_usage_code in (l_out_of_service,
1566 l_in_inventory,
1567 l_installed,
1568 l_in_service,
1569 l_in_process) THEN
1570 -- Update Serialized Item
1571
1572 debug('Serialized Source records found');
1573 debug('Update Serialized Item which is :'||l_src_instance_header_tbl(i).instance_usage_code);
1574 debug('Serial Number is: '||l_src_instance_header_tbl(i).serial_number);
1575 debug('Updating Item Instance: '||l_src_instance_header_tbl(i).instance_id);
1576
1577 l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1578 l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
1579 l_update_instance_rec.quantity := 1;
1580 l_update_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
1581 --Added For Bug 5975739
1582 l_update_instance_rec.inv_master_organization_id := l_master_organization_id;
1583 l_update_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
1584 l_update_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
1585 l_update_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1586 l_update_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1587 --l_update_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
1588 l_update_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
1589 l_update_instance_rec.instance_usage_code := l_in_inventory;
1590 l_update_instance_rec.last_po_po_line_id := r_po_info.po_line_id; --5184815
1591 l_update_instance_rec.active_end_date := NULL;
1592 l_update_instance_rec.pa_project_id := NULL;
1593 l_update_instance_rec.pa_project_task_id := NULL;
1594 l_update_instance_rec.install_location_type_code := NULL;
1595 l_update_instance_rec.install_location_id := NULL;
1596 l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
1597 l_update_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
1598 l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
1599
1600 l_party_tbl.delete;
1601 l_account_tbl.delete;
1602 l_pricing_attrib_tbl.delete;
1603 l_org_assignments_tbl.delete;
1604 l_asset_assignment_tbl.delete;
1605
1606 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);
1607
1608 debug('Right after setting instance status');
1609 debug('Before Update item instance');
1610
1611 csi_item_instance_pub.update_item_instance(l_api_version,
1612 l_commit,
1613 l_init_msg_list,
1614 l_validation_level,
1615 l_update_instance_rec,
1616 l_ext_attrib_values_tbl,
1617 l_party_tbl,
1618 l_account_tbl,
1619 l_pricing_attrib_tbl,
1620 l_org_assignments_tbl,
1621 l_asset_assignment_tbl,
1622 l_txn_rec,
1623 l_instance_id_lst,
1624 l_return_status,
1625 l_msg_count,
1626 l_msg_data);
1627 l_upd_error_instance_id := NULL;
1628 l_upd_error_instance_id := l_update_instance_rec.instance_id;
1629
1630 debug('After get item instance');
1631 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1632
1633 -- Check for any errors and add them to the message stack to pass out to be put into the
1634 -- error log table.
1635 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1636 debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
1637 debug('Message Count: '||l_msg_count);
1638 debug('Return Status: '||l_return_status);
1639 l_msg_index := 1;
1640 WHILE l_msg_count > 0 loop
1641 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1642 l_msg_index := l_msg_index + 1;
1643 l_msg_count := l_msg_count - 1;
1644 END LOOP;
1645 RAISE fnd_api.g_exc_error;
1646 END IF;
1647
1648 ELSIF l_src_instance_header_tbl(i).instance_usage_code in (l_out_of_enterprise,l_in_relationship,l_in_wip) THEN
1649
1650 IF l_ownership_party = 'Y' THEN
1651
1652
1653 IF l_src_instance_header_tbl(i).instance_usage_code = l_in_relationship THEN
1654 debug('Check and Break Relationship for Instance :'||l_src_instance_header_tbl(i).instance_id);
1655
1656 csi_process_txn_pvt.check_and_break_relation(l_src_instance_header_tbl(i).instance_id,
1657 l_txn_rec,
1658 l_return_status);
1659
1660 IF NOT l_return_status = l_fnd_success then
1661 debug('You encountered an error in the se_inv_trxs_pkg.check_and_break_relation');
1662 l_error_message := csi_t_gen_utility_pvt.dump_error_stack;
1663 RAISE fnd_api.g_exc_error;
1664 END IF;
1665
1666 debug('Object Version originally from instance: '||l_src_instance_header_tbl(i).object_version_number);
1667
1668 OPEN c_obj_version (l_src_instance_header_tbl(i).instance_id);
1669 FETCH c_obj_version into l_src_instance_header_tbl(i).object_version_number;
1670 CLOSE c_obj_version;
1671
1672 debug('Current Object Version after check and break :'||l_src_instance_header_tbl(i).object_version_number);
1673
1674 END IF; -- Check and Break
1675
1676 debug('Update Serialized Item which is :'||l_src_instance_header_tbl(i).instance_usage_code);
1677 debug('Serial Number is: '||l_src_instance_header_tbl(i).serial_number);
1678
1679 l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
1680 l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
1681 l_update_instance_rec.quantity := 1;
1682 l_update_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
1683 -- Added for Bug 5975739
1684 l_update_instance_rec.inv_master_organization_id := l_master_organization_id;
1685 l_update_instance_rec.mfg_serial_number_flag := 'Y';
1686 l_update_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
1687 l_update_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
1688 l_update_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1689 l_update_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1690 -- l_update_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
1691 l_update_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
1692 l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
1693 l_update_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
1694 l_update_instance_rec.instance_usage_code := l_in_inventory;
1695 l_update_instance_rec.last_po_po_line_id := r_po_info.po_line_id; --5184815
1696 l_update_instance_rec.active_end_date := NULL;
1697 l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
1698 --bnarayan for the bug4549020
1699 l_update_instance_rec.install_location_type_code := NULL;
1700 l_update_instance_rec.install_location_id := NULL;
1701
1702
1703 IF l_ownership_party = 'Y' THEN --added code for bug #5868111
1704
1705 -- We want to change the party of this back
1706 -- to the Internal Party
1707
1708 debug('Usage is '||l_src_instance_header_tbl(i).instance_usage_code||' So we need to bring this back into Inventory and change the Owner Party back to the Internal Party');
1709
1710 -- Set Instance ID so it will query the child recs for this
1711 -- Instance.
1712
1713 l_instance_header_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
1714 /*Code changes for bug 8842177**/
1715 -- Get Owner Party ID of the Instance.
1716
1717 BEGIN
1718 SELECT owner_party_id
1719 INTO l_owner_party_id
1720 FROM csi_item_instances
1721 WHERE instance_id = l_src_instance_header_tbl(i).instance_id;
1722
1723 EXCEPTION
1724 WHEN no_data_found THEN
1725 l_owner_party_id := -99999;
1726 END;
1727
1728 IF l_owner_party_id <> l_internal_party_id THEN --added code for bug #5868111
1729
1730 -- Set Instance ID so it will query the child recs for this
1731 -- Instance.
1732
1733 l_instance_header_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
1734 -- Call details to get Party Information
1735 csi_item_instance_pub.get_item_instance_details
1736 (l_api_version,
1737 l_commit,
1738 l_init_msg_list,
1739 l_validation_level,
1740 l_instance_header_rec,
1741 fnd_api.g_true, -- Get Parties
1742 l_party_header_tbl,
1743 fnd_api.g_false, -- Get Accounts
1744 l_account_header_tbl,
1745 fnd_api.g_false, -- Get Org Assi.
1746 l_org_header_tbl,
1747 fnd_api.g_false, -- Get Price Att
1748 l_pricing_header_tbl,
1749 fnd_api.g_false, -- Get Ext Attr
1750 l_ext_attrib_header_tbl,
1751 l_ext_attrib_def_tbl,
1752 fnd_api.g_false, -- Get Asset Assi
1753 l_asset_header_tbl,
1754 fnd_api.g_false, -- Resolve IDs
1755 NULL, -- Time Stamp
1756 l_return_status,
1757 l_msg_count,
1758 l_msg_data);
1759
1760 -- Now create a new owner record that will be used to create
1761 -- the new owner party and set it back to an internal party owner
1762 -- The PL/SQL Table will now be set so that it can be passed into
1763 -- the next procedure.
1764
1765 FOR p in l_party_header_tbl.FIRST .. l_party_header_tbl.LAST LOOP
1766 IF l_party_header_tbl(p).relationship_type_code = 'OWNER' THEN
1767 debug('Found the OWNER party so updating this back to the Internal Party ID');
1768
1769 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
1770 l_party_tbl(i).instance_id := l_src_instance_header_tbl(i).instance_id;
1771 l_party_tbl(i).instance_party_id := l_party_header_tbl(p).instance_party_id;
1772 l_party_tbl(i).object_version_number := l_party_header_tbl(p).object_version_number;
1773 debug('After finding the OWNER party and updating this back to the Internal Party ID');
1774 END IF;-- Owner Party
1775 END LOOP;
1776
1777 debug('Inst Party ID :'||l_party_tbl(i).instance_party_id);
1778 debug('Party Inst ID :'||l_party_tbl(i).instance_id);
1779 debug('Party Source Table :'||l_party_tbl(i).party_source_table);
1780 debug('Party ID :'||l_party_tbl(i).party_id);
1781 debug('Rel Type Code :'||l_party_tbl(i).relationship_type_code);
1782 debug('Contact Flag :'||l_party_tbl(i).contact_flag);
1783 debug('Object Version Number:' ||l_party_tbl(i).object_version_number);
1784
1785 --code added for bug #5868111....start here
1786
1787 ELSE --Instance is already at Internal Party
1788 l_party_tbl.delete;
1789
1790 END IF; -- Party Header vs Int Party Id
1791
1792 /*Code changes for bug 8842177**/
1793
1794 -- code added for bug #5868111...starts below
1795
1796 ELSE -- Ownership "N"
1797
1798 debug('Ownership Override is "N" so get the Owner Party ID and compare to the Internal Party ID');
1799
1800 BEGIN
1801 SELECT owner_party_id
1802 INTO l_owner_party_id
1803 FROM csi_item_instances
1804 WHERE instance_id = l_src_instance_header_tbl(i).instance_id;
1805
1806 EXCEPTION
1807 WHEN no_data_found THEN
1808 l_owner_party_id := -99999;
1809 END;
1810
1811 IF l_owner_party_id <> l_internal_party_id THEN
1812
1813 l_status := 'In Inventory, Out of Service, Installed, In Process or In Service ';
1814 debug('Serialized Item with In Inventory, Out of Service, Installed, In Process or In Service exists however the ownership_override_at_txn flag is set to N');
1815 debug('The current owner party is not the Internal Party so we will NOT bring this back into inventory');
1816 debug('Instance Usage Code is: '||l_src_instance_header_tbl(i).instance_usage_code);
1817 fnd_message.set_name('CSI','CSI_SERIALIZED_ITEM_EXISTS');
1818 fnd_message.set_token('STATUS',l_status);
1819 l_error_message := fnd_message.get;
1820 l_return_status := l_fnd_error;
1821 RAISE fnd_api.g_exc_error;
1822 ELSE
1823 l_party_tbl.delete;
1824 END IF;
1825 END IF;
1826
1827 --code added for bug #5868111.....ends here
1828
1829
1830 l_account_tbl.delete;
1831 l_pricing_attrib_tbl.delete;
1832 l_org_assignments_tbl.delete;
1833 l_asset_assignment_tbl.delete;
1834
1835 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);
1836
1837 debug('Right after setting instance status');
1838 debug('Before Update item instance');
1839
1840 csi_item_instance_pub.update_item_instance(l_api_version,
1841 l_commit,
1842 l_init_msg_list,
1843 l_validation_level,
1844 l_update_instance_rec,
1845 l_ext_attrib_values_tbl,
1846 l_party_tbl,
1847 l_account_tbl,
1848 l_pricing_attrib_tbl,
1849 l_org_assignments_tbl,
1850 l_asset_assignment_tbl,
1851 l_txn_rec,
1852 l_instance_id_lst,
1853 l_return_status,
1854 l_msg_count,
1855 l_msg_data);
1856 l_upd_error_instance_id := NULL;
1857 l_upd_error_instance_id := l_update_instance_rec.instance_id;
1858
1859 debug('After get item instance');
1860 debug('After update of Out of Enterprise Item Instance');
1861 debug('Update Item Instance is: '||l_update_instance_rec.instance_id);
1862 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
1863
1864 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
1865 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
1866 debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
1867 debug('Message Count: '||l_msg_count);
1868 debug('Return Status: '||l_return_status);
1869 l_msg_index := 1;
1870 WHILE l_msg_count > 0 loop
1871 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1872 l_msg_index := l_msg_index + 1;
1873 l_msg_count := l_msg_count - 1;
1874 END LOOP;
1875 RAISE fnd_api.g_exc_error;
1876 END IF;
1877
1878 ELSE
1879 l_status := 'In Inventory, Out of Service, Out of Enterprise, In Relationship, Installed, In Service or In Process';
1880 debug('Serialized Item with Out of Enterprise or In Relationship exists however the ownership_override_at_txn flag is set to N so we will NOT bring this back into inventory');
1881 debug('Instance Usage Code is: '||l_src_instance_header_tbl(i).instance_usage_code);
1882 fnd_message.set_name('CSI','CSI_SERIALIZED_ITEM_EXISTS');
1883 fnd_message.set_token('STATUS',l_status);
1884 l_error_message := fnd_message.get;
1885 l_return_status := l_fnd_error;
1886 RAISE fnd_api.g_exc_error;
1887 END IF;
1888 ELSE
1889 l_status := 'In Inventory, Out of Service, Installed, In Service or In Process';
1890 debug('Serialized Item with Status other then Out Of Service, In Inventory, Installed, or In Process already exists in Install Base');
1891 debug('Instance Usage Code is: '||l_src_instance_header_tbl(i).instance_usage_code);
1892 fnd_message.set_name('CSI','CSI_SERIALIZED_ITEM_EXISTS');
1893 fnd_message.set_token('STATUS',l_status);
1894 l_error_message := fnd_message.get;
1895 l_return_status := l_fnd_error;
1896 RAISE fnd_api.g_exc_error;
1897 END IF; -- Usage IF
1898
1899 END IF; -- Serialized Source Records
1900
1901 ELSIF l_mtl_item_tbl(j).serial_number IS NULL THEN -- Non Serialized
1902
1903 csi_inv_trxs_pkg.set_item_attr_query_values(l_mtl_item_tbl,
1904 j,
1905 NULL,
1906 l_instance_query_rec,
1907 x_return_status);
1908
1909 l_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
1910 l_instance_query_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
1911 l_instance_query_rec.instance_usage_code := l_in_inventory;
1912
1913 csi_t_gen_utility_pvt.dump_instance_query_rec(p_instance_query_rec => l_instance_query_rec);
1914
1915
1916 debug('Before Get Item Instance');
1917
1918 csi_item_instance_pub.get_item_instances(l_api_version,
1919 l_commit,
1920 l_init_msg_list,
1921 l_validation_level,
1922 l_instance_query_rec,
1923 l_party_query_rec,
1924 l_account_query_rec,
1925 l_transaction_id,
1926 l_resolve_id_columns,
1927 l_inactive_instance_only,
1928 l_src_instance_header_tbl,
1929 l_return_status,
1930 l_msg_count,
1931 l_msg_data);
1932
1933 debug('After Get Item Instance');
1934
1935 l_tbl_count := 0;
1936 l_tbl_count := l_src_instance_header_tbl.count;
1937
1938 debug('Source Records Found: '||l_tbl_count);
1939
1940 -- Check for any errors and add them to the message stack to pass out to be put into the
1941 -- error log table.
1942 IF NOT l_return_status = l_fnd_success then
1943 debug('You encountered an error in the csi_item_instance_pub.get_item_instance API '||l_msg_data);
1944 l_msg_index := 1;
1945 WHILE l_msg_count > 0 loop
1946 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1947 l_msg_index := l_msg_index + 1;
1948 l_msg_count := l_msg_count - 1;
1949 END LOOP;
1950 RAISE fnd_api.g_exc_error;
1951 END IF;
1952
1953 IF l_src_instance_header_tbl.count = 0 THEN -- No Records found so Create Non Serialized Item
1954
1955 debug('No source records were found so create a new one');
1956 l_new_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
1957 l_new_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
1958 l_new_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
1959 l_new_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
1960 l_new_instance_rec.inv_master_organization_id := l_master_organization_id;
1961 l_new_instance_rec.mfg_serial_number_flag := l_mfg_serial_flag;
1962 l_new_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
1963 l_new_instance_rec.quantity := abs(l_mtl_item_tbl(j).transaction_quantity);
1964 l_new_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
1965 l_new_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
1966 l_new_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
1967 l_new_instance_rec.instance_usage_code := l_in_inventory;
1968 l_new_instance_rec.last_po_po_line_id := r_po_info.po_line_id; --5184815
1969 l_new_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
1970 l_new_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
1971 l_new_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
1972 l_new_instance_rec.customer_view_flag := 'N';
1973 l_new_instance_rec.merchant_view_flag := 'Y';
1974 l_new_instance_rec.object_version_number := l_object_version_number;
1975 l_new_instance_rec.operational_status_code := 'NOT_USED';
1976 l_new_instance_rec.active_start_date := l_sysdate;
1977 l_new_instance_rec.active_end_date := NULL;
1978
1979 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
1980 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
1981 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
1982 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
1983 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
1984 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
1985
1986 l_new_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
1987
1988 debug('Before Create of new source Item Instance');
1989
1990 csi_item_instance_pub.create_item_instance(l_api_version,
1991 l_commit,
1992 l_init_msg_list,
1993 l_validation_level,
1994 l_new_instance_rec,
1995 l_ext_attrib_values_tbl,
1996 l_party_tbl,
1997 l_account_tbl,
1998 l_pricing_attrib_tbl,
1999 l_org_assignments_tbl,
2000 l_asset_assignment_tbl,
2001 l_txn_rec,
2002 l_return_status,
2003 l_msg_count,
2004 l_msg_data);
2005
2006
2007 debug('After Create Item Instance');
2008 debug('Item Instance Created: '||l_new_instance_rec.instance_id);
2009
2010 -- Check for any errors and add them to the message stack to pass out to be put into the
2011 -- error log table.
2012 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2013 debug('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
2014 l_msg_index := 1;
2015 WHILE l_msg_count > 0 loop
2016 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2017 l_msg_index := l_msg_index + 1;
2018 l_msg_count := l_msg_count - 1;
2019 END LOOP;
2020 RAISE fnd_api.g_exc_error;
2021 END IF;
2022
2023 ELSIF l_src_instance_header_tbl.count = 1 THEN -- Records Found
2024
2025 -- Update Non Serialized Item
2026
2027 debug('Non Serialized Source records found');
2028 debug('Updating Item Instance: '||l_src_instance_header_tbl(i).instance_id);
2029
2030 l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2031 l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
2032 l_update_instance_rec.quantity := l_src_instance_header_tbl(i).quantity + abs(l_mtl_item_tbl(j).primary_quantity);
2033 l_update_instance_rec.active_end_date := NULL;
2034 l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
2035 l_update_instance_rec.last_po_po_line_id := r_po_info.po_line_id; --5184815
2036
2037 l_party_tbl.delete;
2038 l_account_tbl.delete;
2039 l_pricing_attrib_tbl.delete;
2040 l_org_assignments_tbl.delete;
2041 l_asset_assignment_tbl.delete;
2042
2043 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);
2044
2045 debug('Right after setting instance status');
2046 debug('Before Update item instance');
2047
2048 csi_item_instance_pub.update_item_instance(l_api_version,
2049 l_commit,
2050 l_init_msg_list,
2051 l_validation_level,
2052 l_update_instance_rec,
2053 l_ext_attrib_values_tbl,
2054 l_party_tbl,
2055 l_account_tbl,
2056 l_pricing_attrib_tbl,
2057 l_org_assignments_tbl,
2058 l_asset_assignment_tbl,
2059 l_txn_rec,
2060 l_instance_id_lst,
2061 l_return_status,
2062 l_msg_count,
2063 l_msg_data);
2064 l_upd_error_instance_id := NULL;
2065 l_upd_error_instance_id := l_update_instance_rec.instance_id;
2066
2067 debug('After Update item instance');
2068 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2069
2070 -- Check for any errors and add them to the message stack to pass out to be put into the
2071 -- error log table.
2072 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2073 debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
2074 debug('Message Count: '||l_msg_count);
2075 debug('Return Status: '||l_return_status);
2076 l_msg_index := 1;
2077 WHILE l_msg_count > 0 loop
2078 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2079 l_msg_index := l_msg_index + 1;
2080 l_msg_count := l_msg_count - 1;
2081 END LOOP;
2082 RAISE fnd_api.g_exc_error;
2083 END IF;
2084
2085 ELSIF l_src_instance_header_tbl.count > 1 THEN -- Records Found
2086 -- Multiple Instances were found so throw error
2087 debug('Multiple Instances were Found in Install Base
2088 Base-21');
2089 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
2090 fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
2091 fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
2092 fnd_message.set_token('INV_ORG_ID',l_mtl_item_tbl(j).organization_id);
2093 fnd_message.set_token('LOCATOR',l_mtl_item_tbl(j).locator_id);
2094 l_error_message := fnd_message.get;
2095 RAISE fnd_api.g_exc_error;
2096 END IF; -- End of Source Record If
2097 END IF; -- End of Serial Number if
2098 END LOOP; -- End of For Loop
2099
2100 debug('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2101 debug('*****End of csi_inv_trxs_pkg.receipt_inventory Transaction*****');
2102
2103 EXCEPTION
2104 WHEN fnd_api.g_exc_error THEN
2105 debug('You have encountered a "fnd_api.g_exc_error" exception');
2106 x_return_status := l_fnd_error;
2107
2108 IF l_mtl_item_tbl.count > 0 THEN
2109 x_trx_error_rec.serial_number := l_mtl_item_tbl(j).serial_number;
2110 x_trx_error_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2111 x_trx_error_rec.instance_id := l_upd_error_instance_id;
2112 x_trx_error_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2113 x_trx_error_rec.src_serial_num_ctrl_code := l_mtl_item_tbl(j).serial_number_control_code;
2114 x_trx_error_rec.src_location_ctrl_code := l_mtl_item_tbl(j).location_control_code;
2115 x_trx_error_rec.src_lot_ctrl_code := l_mtl_item_tbl(j).lot_control_code;
2116 x_trx_error_rec.src_rev_qty_ctrl_code := l_mtl_item_tbl(j).revision_qty_control_code;
2117 x_trx_error_rec.comms_nl_trackable_flag := l_mtl_item_tbl(j).comms_nl_trackable_flag;
2118 x_trx_error_rec.transaction_error_date := l_sysdate ;
2119 END IF;
2120
2121 x_trx_error_rec.error_text := l_error_message;
2122 x_trx_error_rec.transaction_id := NULL;
2123 x_trx_error_rec.source_type := 'CSIPOINV';
2124 x_trx_error_rec.source_id := p_transaction_id;
2125 x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
2126 x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('PO_RECEIPT_INTO_INVENTORY','INV');
2127 x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
2128 x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
2129
2130 WHEN others THEN
2131 l_sql_error := SQLERRM;
2132 debug('You have encountered a "others" exception');
2133 debug('SQL Error: '||l_sql_error);
2134 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
2135 fnd_message.set_token('API_NAME',l_api_name);
2136 fnd_message.set_token('SQL_ERROR',SQLERRM);
2137 x_return_status := l_fnd_unexpected;
2138
2139 IF l_mtl_item_tbl.count > 0 THEN
2140 x_trx_error_rec.serial_number := l_mtl_item_tbl(j).serial_number;
2141 x_trx_error_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2142 x_trx_error_rec.instance_id := l_upd_error_instance_id;
2143 x_trx_error_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2144 x_trx_error_rec.src_serial_num_ctrl_code := l_mtl_item_tbl(j).serial_number_control_code;
2145 x_trx_error_rec.src_location_ctrl_code := l_mtl_item_tbl(j).location_control_code;
2146 x_trx_error_rec.src_lot_ctrl_code := l_mtl_item_tbl(j).lot_control_code;
2147 x_trx_error_rec.src_rev_qty_ctrl_code := l_mtl_item_tbl(j).revision_qty_control_code;
2148 x_trx_error_rec.comms_nl_trackable_flag := l_mtl_item_tbl(j).comms_nl_trackable_flag;
2149 x_trx_error_rec.transaction_error_date := l_sysdate ;
2150 END IF;
2151
2152 x_trx_error_rec.error_text := fnd_message.get;
2153 x_trx_error_rec.transaction_id := NULL;
2154 x_trx_error_rec.source_type := 'CSIPOINV';
2155 x_trx_error_rec.source_id := p_transaction_id;
2156 x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
2157 x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('PO_RECEIPT_INTO_INVENTORY','INV');
2158 x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
2159 x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
2160
2161 END receipt_inventory;
2162
2163 PROCEDURE misc_issue(p_transaction_id IN NUMBER,
2164 p_message_id IN NUMBER,
2165 x_return_status OUT NOCOPY VARCHAR2,
2166 x_trx_error_rec OUT NOCOPY CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC)
2167 IS
2168
2169 l_mtl_item_tbl CSI_INV_TRXS_PKG.MTL_ITEM_TBL_TYPE;
2170 l_api_name VARCHAR2(100) := 'CSI_INV_TRXS_PKG.MISC_ISSUE';
2171 l_api_version NUMBER := 1.0;
2172 l_commit VARCHAR2(1) := FND_API.G_FALSE;
2173 l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
2174 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
2175 l_active_instance_only VARCHAR2(10) := FND_API.G_TRUE;
2176 l_resolve_id_columns VARCHAR2(10) := FND_API.G_FALSE;
2177 l_transaction_id NUMBER := NULL;
2178 l_object_version_number NUMBER := 1;
2179 l_sysdate DATE := SYSDATE;
2180 l_master_organization_id NUMBER;
2181 l_depreciable VARCHAR2(1);
2182 l_instance_query_rec CSI_DATASTRUCTURES_PUB.INSTANCE_QUERY_REC;
2183 l_update_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
2184 l_api_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
2185 l_api_src_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
2186 l_new_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
2187 l_new_src_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
2188 l_new_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
2189 l_txn_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_REC;
2190 l_return_status VARCHAR2(1);
2191 l_error_code VARCHAR2(50);
2192 l_error_message VARCHAR2(4000);
2193 l_instance_id_lst CSI_DATASTRUCTURES_PUB.ID_TBL;
2194 l_party_query_rec CSI_DATASTRUCTURES_PUB.PARTY_QUERY_REC;
2195 l_account_query_rec CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_QUERY_REC;
2196 l_src_instance_header_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_HEADER_TBL;
2197 l_ext_attrib_values_tbl CSI_DATASTRUCTURES_PUB.EXTEND_ATTRIB_VALUES_TBL;
2198 l_party_tbl CSI_DATASTRUCTURES_PUB.PARTY_TBL;
2199 l_account_tbl CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_TBL;
2200 l_pricing_attrib_tbl CSI_DATASTRUCTURES_PUB.PRICING_ATTRIBS_TBL;
2201 l_org_assignments_tbl CSI_DATASTRUCTURES_PUB.ORGANIZATION_UNITS_TBL;
2202 l_asset_assignment_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_ASSET_TBL;
2203 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2204 l_fnd_warning VARCHAR2(1) := 'W';
2205 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
2206 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
2207 l_in_inventory VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_INVENTORY;
2208 l_in_process VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_PROCESS;
2209 l_out_of_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_OUT_OF_SERVICE;
2210 l_in_service VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_SERVICE;
2211 l_in_transit VARCHAR2(25) := CSI_INV_TRXS_PKG.G_IN_TRANSIT;
2212 l_installed VARCHAR2(25) := CSI_INV_TRXS_PKG.G_INSTALLED;
2213 l_transaction_error_id NUMBER;
2214 l_quantity NUMBER;
2215 l_mfg_serial_flag VARCHAR2(1);
2216 l_trans_status_code VARCHAR2(15);
2217 l_ins_number VARCHAR2(100);
2218 l_ins_id NUMBER;
2219 l_file VARCHAR2(500);
2220 l_msg_count NUMBER;
2221 l_msg_data VARCHAR2(2000);
2222 l_sql_error VARCHAR2(2000);
2223 l_msg_index NUMBER;
2224 l_employee_id NUMBER;
2225 l_end_date DATE;
2226 j PLS_INTEGER;
2227 i PLS_INTEGER := 1;
2228 l_tbl_count NUMBER := 0;
2229 l_neg_code NUMBER := 0;
2230 l_instance_status VARCHAR2(1);
2231 l_trans_type_code VARCHAR2(25);
2232 l_trans_app_code VARCHAR2(5);
2233 l_redeploy_flag VARCHAR2(1);
2234 l_upd_error_instance_id NUMBER := NULL;
2235 l_dest_instance_header_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_HEADER_TBL;
2236
2237 l_serial_tagged NUMBER := 1;
2238 l_return_item VARCHAR2(1) := 'N';
2239 cursor c_id is
2240 SELECT instance_status_id
2241 FROM csi_instance_statuses
2242 WHERE name = FND_PROFILE.VALUE('CSI_DEFAULT_INSTANCE_STATUS');
2243
2244 r_id c_id%rowtype;
2245
2246 CURSOR c_phys_inv_info (pc_physical_adjustment_id IN NUMBER) is
2247 SELECT mpi.physical_inventory_id physical_inventory_id,
2248 mpi.physical_inventory_name physical_inventory_name,
2249 mpit.tag_number tag_number
2250 FROM mtl_physical_adjustments mpa,
2251 mtl_physical_inventories mpi,
2252 mtl_physical_inventory_tags mpit
2253 WHERE mpa.physical_inventory_id = mpi.physical_inventory_id
2254 AND mpa.physical_inventory_id = mpit.physical_inventory_id
2255 AND mpa.adjustment_id = mpit.adjustment_id
2256 AND mpa.adjustment_id = pc_physical_adjustment_id;
2257
2258 r_phys_inv_info c_phys_inv_info%rowtype;
2259
2260 CURSOR c_cycle_count_info (pc_cycle_count_entry_id IN NUMBER) is
2261 SELECT mcch.cycle_count_header_id cycle_count_header_id,
2262 mcch.cycle_count_header_name cycle_count_header_name
2263 FROM mtl_cycle_count_entries mcce, mtl_cycle_count_headers mcch
2264 WHERE mcce.cycle_count_header_id = mcch.cycle_count_header_id
2265 AND mcce.cycle_count_entry_id = pc_cycle_count_entry_id;
2266
2267 r_cycle_count_info c_cycle_count_info%rowtype;
2268
2269 BEGIN
2270 x_return_status := l_fnd_success;
2271
2272 debug('*****Start of csi_inv_trxs_pkg.misc_issue Transaction procedure*****');
2273 debug('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2274 debug('csiivtxb.pls 115.25');
2275 debug('Transaction You are Processing is: '||p_transaction_id);
2276
2277 -- This procedure queries all of the Inventory Transaction Records and returns them
2278 -- as a table.
2279 csi_inv_trxs_pkg.get_transaction_recs(p_transaction_id,
2280 l_mtl_item_tbl,
2281 l_return_status,
2282 l_error_message);
2283
2284 l_tbl_count := 0;
2285 l_tbl_count := l_mtl_item_tbl.count;
2286 debug('Inventory Records Found: '||l_tbl_count);
2287
2288 IF NOT l_return_status = l_fnd_success THEN
2289 debug('You have encountered an error in CSI_INV_TRXS_PKG.get_transaction_recs, Transaction ID: '||p_transaction_id);
2290 RAISE fnd_api.g_exc_error;
2291 END IF;
2292
2293 -- Determine Trasaction Type
2294 IF l_mtl_item_tbl(i).transaction_type_id = 8 THEN
2295 l_trans_type_code := 'PHYSICAL_INVENTORY';
2296 l_trans_app_code := 'INV';
2297 ELSIF l_mtl_item_tbl(i).transaction_type_id = 4 THEN
2298 l_trans_type_code := 'CYCLE_COUNT';
2299 l_trans_app_code := 'INV';
2300 ELSIF l_mtl_item_tbl(i).transaction_type_id = 31 THEN
2301 l_trans_type_code := 'ACCT_ALIAS_ISSUE';
2302 l_trans_app_code := 'INV';
2303 ELSIF l_mtl_item_tbl(i).transaction_type_id = 34 THEN
2304 l_trans_type_code := 'ISO_ISSUE';
2305 l_trans_app_code := 'INV';
2306 ELSIF l_mtl_item_tbl(i).transaction_type_id = 36 THEN
2307 l_trans_type_code := 'RETURN_TO_VENDOR';
2308 l_trans_app_code := 'INV';
2309 ELSIF l_mtl_item_tbl(i).transaction_type_id = 1005 THEN
2310 l_trans_type_code := 'RETURN_TO_VENDOR_WO_PO';
2311 l_trans_app_code := 'INV';
2312 ELSIF l_mtl_item_tbl(i).transaction_type_id = 1 THEN
2313 l_trans_type_code := 'ACCT_ISSUE';
2314 l_trans_app_code := 'INV';
2315 ELSIF l_mtl_item_tbl(i).transaction_type_id = 32 THEN
2316 l_trans_type_code := 'MISC_ISSUE';
2317 l_trans_app_code := 'INV';
2318 ELSIF l_mtl_item_tbl(i).transaction_type_id = 63 THEN
2319 l_trans_type_code := 'MOVE_ORDER_ISSUE';
2320 l_trans_app_code := 'INV';
2321 ELSIF l_mtl_item_tbl(i).transaction_type_id = 71 THEN
2322 l_trans_type_code := 'PO_RCPT_ADJUSTMENT';
2323 l_trans_app_code := 'INV';
2324 ELSIF l_mtl_item_tbl(i).transaction_type_id = 72 THEN
2325 l_trans_type_code := 'INT_REQ_RCPT_ADJUSTMENT';
2326 l_trans_app_code := 'INV';
2327 ELSIF l_mtl_item_tbl(i).transaction_type_id = 70 THEN
2328 l_trans_type_code := 'SHIPMENT_RCPT_ADJUSTMENT';
2329 l_trans_app_code := 'INV';
2330 ELSE
2331 l_trans_type_code := 'MISC_ISSUE';
2332 l_trans_app_code := 'INV';
2333 END IF;
2334
2335 debug('Trans Type Code: '||l_trans_type_code);
2336 debug('Trans App Code: '||l_trans_app_code);
2337
2338 IF l_mtl_item_tbl(i).transaction_type_id IN (36,1005) THEN
2339 csi_inv_trxs_pkg.get_rtv_transaction_recs(p_transaction_id,
2340 l_mtl_item_tbl,
2341 l_return_status,
2342 l_error_message);
2343 END IF;
2344 -- Get the Master Organization ID
2345 csi_inv_trxs_pkg.get_master_organization(l_mtl_item_tbl(i).organization_id,
2346 l_master_organization_id,
2347 l_return_status,
2348 l_error_message);
2349
2350 debug('Master Organization is: '||l_master_organization_id);
2351
2352 IF NOT l_return_status = l_fnd_success THEN
2353 debug('You have encountered an error in csi_inv_trxs_pkg.get_master_organization, Organization ID: '||l_mtl_item_tbl(i).organization_id);
2354 RAISE fnd_api.g_exc_error;
2355 END IF;
2356
2357 -- Call get_fnd_employee_id and get the employee id
2358 l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
2359
2360 IF l_employee_id = -1 THEN
2361 debug('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
2362 END IF;
2363
2364 debug('The Employee that is processing this Transaction is: '||l_employee_id);
2365
2366 -- See if this is a depreciable Item to set the status of the transaction record
2367 csi_inv_trxs_pkg.check_depreciable(l_mtl_item_tbl(i).inventory_item_id,
2368 l_depreciable,
2369 l_mtl_item_tbl(i).organization_id); --Added for Bug 13988660
2370
2371 debug('Is this Item ID: '||l_mtl_item_tbl(i).inventory_item_id||', Depreciable :'||l_depreciable);
2372
2373 -- See if this Item is Serial Tagged
2374 l_serial_tagged := inv_cache.get_serial_tagged (
2375 p_inventory_item_id => l_mtl_item_tbl(i).inventory_item_id,
2376 p_organization_id => l_mtl_item_tbl(i).organization_id,
2377 p_transaction_type_id => l_mtl_item_tbl(i).transaction_type_id);
2378 -- End Serial Tagging
2379
2380 -- Set the mfg_serial_number_flag
2381
2382 IF l_mtl_item_tbl(i).serial_number is NULL THEN
2383 l_mfg_serial_flag := 'N';
2384 ELSE
2385 l_mfg_serial_flag := 'Y';
2386 l_quantity := -1;
2387 END IF;
2388
2389 debug('l_mfg_serial_flag is :' || l_mfg_serial_flag);
2390
2391 -- Get the Negative Receipt Code to see if this org allows Negative
2392 -- Quantity Records 1 = Yes, 2 = No
2393
2394 l_neg_code := csi_inv_trxs_pkg.get_neg_inv_code(
2395 l_mtl_item_tbl(i).organization_id);
2396
2397 IF l_neg_code = 1 AND l_mtl_item_tbl(i).serial_number is NULL THEN
2398 l_instance_status := FND_API.G_FALSE;
2399 ELSE
2400 l_instance_status := FND_API.G_TRUE;
2401 END IF;
2402
2403 debug('Negative Code is - 1 = Yes, 2 = No: '||l_neg_code);
2404
2405 -- Initialize Transaction Record
2406 l_txn_rec := csi_inv_trxs_pkg.init_txn_rec;
2407
2408 -- Set Status based on redeployment
2409 IF l_depreciable = 'N' THEN
2410 IF l_mtl_item_tbl(i).serial_number is NOT NULL THEN
2411 csi_inv_trxs_pkg.get_redeploy_flag(l_mtl_item_tbl(i).inventory_item_id,
2412 l_mtl_item_tbl(i).serial_number,
2413 l_sysdate,
2414 l_redeploy_flag,
2415 l_return_status,
2416 l_error_message);
2417 END IF;
2418 IF l_redeploy_flag = 'Y' THEN
2419 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
2420 ELSE
2421 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_complete;
2422 END IF;
2423 ELSE
2424 l_txn_rec.transaction_status_code := csi_inv_trxs_pkg.g_pending;
2425 END IF;
2426
2427 IF NOT l_return_status = l_fnd_success THEN
2428 debug('Redeploy Flag: '||l_redeploy_flag);
2429 debug('You have encountered an error in csi_inv_trxs_pkg.get_redeploy_flag: '||l_error_message);
2430 RAISE fnd_api.g_exc_error;
2431 END IF;
2432
2433 debug('Redeploy Flag: '||l_redeploy_flag);
2434 debug('Trans Status Code: '||l_txn_rec.transaction_status_code);
2435
2436 -- Get Default Status ID
2437 OPEN c_id;
2438 FETCH c_id into r_id;
2439 CLOSE c_id;
2440
2441 -- Create CSI Transaction to be used
2442 l_txn_rec.source_transaction_date := l_mtl_item_tbl(i).transaction_date;
2443 l_txn_rec.transaction_date := l_sysdate;
2444 l_txn_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
2445 l_txn_rec.transaction_quantity := l_mtl_item_tbl(i).transaction_quantity;
2446 l_txn_rec.transaction_uom_code := l_mtl_item_tbl(i).transaction_uom;
2447 l_txn_rec.transacted_by := l_employee_id;
2448 l_txn_rec.transaction_action_code := NULL;
2449 l_txn_rec.message_id := p_message_id;
2450 l_txn_rec.inv_material_transaction_id := p_transaction_id;
2451 l_txn_rec.object_version_number := l_object_version_number;
2452 l_txn_rec.source_header_ref_id := l_mtl_item_tbl(i).transaction_source_id;
2453 l_txn_rec.source_line_ref_id := l_mtl_item_tbl(i).move_order_line_id;
2454
2455 IF l_mtl_item_tbl(i).transaction_type_id = 8 THEN
2456 OPEN c_phys_inv_info (l_mtl_item_tbl(i).physical_adjustment_id);
2457 FETCH c_phys_inv_info into r_phys_inv_info;
2458 CLOSE c_phys_inv_info;
2459
2460 l_txn_rec.source_header_ref_id := r_phys_inv_info.physical_inventory_id;
2461 l_txn_rec.source_header_ref := r_phys_inv_info.physical_inventory_name;
2462 l_txn_rec.source_line_ref := r_phys_inv_info.tag_number;
2463
2464 debug('MMT Phys Adj ID: '||l_mtl_item_tbl(i).physical_adjustment_id);
2465 debug('Physical Inventory ID: '||l_txn_rec.source_header_ref_id);
2466 debug('Physical Inventory Name: '||l_txn_rec.source_header_ref);
2467
2468 ELSIF l_mtl_item_tbl(i).transaction_type_id = 4 THEN
2469
2470 OPEN c_cycle_count_info (l_mtl_item_tbl(i).cycle_count_id);
2471 FETCH c_cycle_count_info into r_cycle_count_info;
2472 CLOSE c_cycle_count_info;
2473
2474 l_txn_rec.source_header_ref_id := r_cycle_count_info.cycle_count_header_id;
2475 l_txn_rec.source_header_ref := r_cycle_count_info.cycle_count_header_name;
2476
2477 debug('MMT Cycle Count ID: '||l_mtl_item_tbl(i).cycle_count_id);
2478 debug('Cycle Count ID: '||l_txn_rec.source_header_ref_id);
2479 debug('Cycle Count Name: '||l_txn_rec.source_header_ref);
2480
2481 END IF;
2482
2483 -- Move Order Transfer Info on Txn Record
2484 IF l_mtl_item_tbl(i).transaction_type_id = 63 THEN
2485 l_txn_rec.source_header_ref_id := l_mtl_item_tbl(i).transaction_source_id;
2486 l_txn_rec.source_line_ref_id := l_mtl_item_tbl(i).move_order_line_id;
2487 END IF;
2488
2489 csi_inv_trxs_pkg.create_csi_txn(l_txn_rec,
2490 l_error_message,
2491 l_return_status);
2492
2493 debug('CSI Transaction Created: '||l_txn_rec.transaction_id);
2494
2495 IF NOT l_return_status = l_fnd_success THEN
2496 debug('You have encountered an error in csi_inv_trxs_pkg.create_csi_txn: '||p_transaction_id);
2497 RAISE fnd_api.g_exc_error;
2498 END IF;
2499
2500 -- Now loop through the PL/SQL Table.
2501 j := 1;
2502
2503 debug('Starting to loop through Material Transaction Records');
2504
2505 FOR j in l_mtl_item_tbl.FIRST .. l_mtl_item_tbl.LAST LOOP
2506
2507 debug('Primary UOM: '||l_mtl_item_tbl(j).primary_uom_code);
2508 debug('Primary Qty: '||l_mtl_item_tbl(j).primary_quantity);
2509 debug('Transaction UOM: '||l_mtl_item_tbl(j).transaction_uom);
2510 debug('Transaction Qty: '||l_mtl_item_tbl(j).transaction_quantity);
2511
2512 csi_inv_trxs_pkg.set_item_attr_query_values(l_mtl_item_tbl,
2513 j,
2514 NULL,
2515 l_instance_query_rec,
2516 x_return_status);
2517
2518 debug('l_mtl_item_tbl(j).serial_number : '||l_mtl_item_tbl(j).serial_number);
2519
2520 BEGIN
2521 SELECT 'Y' INTO l_return_item
2522 FROM sys.dual
2523 WHERE EXISTS (
2524 SELECT 1 FROM csi_item_instances
2525 WHERE serial_number = l_mtl_item_tbl(j).serial_number
2526 AND inventory_item_id = l_mtl_item_tbl(j).inventory_item_id
2527 AND (instance_usage_code = 'RETURNED'
2528 OR (instance_usage_code = 'IN_TRANSIT' AND active_end_date IS NOT NULL)));
2529 EXCEPTION
2530 WHEN no_data_found THEN
2531 null;
2532 END;
2533
2534 debug('l_return_item : ' || l_return_item);
2535
2536 IF l_mtl_item_tbl(j).serial_number IS NULL OR l_return_item = 'N' THEN -- Non Serial or serial tagged
2537
2538 l_instance_query_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
2539 l_instance_query_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
2540 l_instance_query_rec.instance_usage_code := l_in_inventory;
2541
2542 END IF;
2543
2544 csi_t_gen_utility_pvt.dump_instance_query_rec(p_instance_query_rec => l_instance_query_rec);
2545
2546 debug('Before Get Item Instance');
2547
2548 csi_item_instance_pub.get_item_instances(l_api_version,
2549 l_commit,
2550 l_init_msg_list,
2551 l_validation_level,
2552 l_instance_query_rec,
2553 l_party_query_rec,
2554 l_account_query_rec,
2555 l_transaction_id,
2556 l_resolve_id_columns,
2557 l_instance_status,
2558 l_src_instance_header_tbl,
2559 l_return_status,
2560 l_msg_count,
2561 l_msg_data);
2562
2563 debug('After Get Item Instance');
2564
2565 l_tbl_count := 0;
2566 l_tbl_count := l_src_instance_header_tbl.count;
2567
2568 debug('Source Records Found: '||l_tbl_count);
2569
2570 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2571 IF NOT l_return_status = l_fnd_success then
2572 l_msg_index := 1;
2573 WHILE l_msg_count > 0 loop
2574 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2575 l_msg_index := l_msg_index + 1;
2576 l_msg_count := l_msg_count - 1;
2577 END LOOP;
2578 RAISE fnd_api.g_exc_error;
2579 END IF;
2580
2581 IF l_mtl_item_tbl(j).serial_number is NULL THEN
2582 IF l_src_instance_header_tbl.count = 0 THEN
2583 IF l_neg_code = 1 THEN -- Allow Neg Qtys on NON Serial Items ONLY
2584 -- No Instances so check to see if Neg Qtys Allowed to create source
2585
2586 debug('No records were found and Neg Qtys allowed so create a new Source Instance Record');
2587
2588 l_new_src_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
2589 l_new_src_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2590 l_new_src_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
2591 l_new_src_instance_rec.inv_master_organization_id := l_master_organization_id;
2592 l_new_src_instance_rec.mfg_serial_number_flag := 'N';
2593 l_new_src_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2594 l_new_src_instance_rec.quantity := l_mtl_item_tbl(j).transaction_quantity;
2595 l_new_src_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
2596 l_new_src_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
2597 l_new_src_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
2598 l_new_src_instance_rec.instance_usage_code := l_in_inventory;
2599 l_new_src_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
2600 l_new_src_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
2601 l_new_src_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
2602 l_new_src_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
2603 l_new_src_instance_rec.customer_view_flag := 'N';
2604 l_new_src_instance_rec.merchant_view_flag := 'Y';
2605 l_new_src_instance_rec.object_version_number := l_object_version_number;
2606 l_new_src_instance_rec.operational_status_code := 'NOT_USED';
2607 l_new_src_instance_rec.active_start_date := l_sysdate;
2608 l_new_src_instance_rec.active_end_date := NULL;
2609
2610 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
2611 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
2612 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
2613 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
2614 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2615 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2616
2617 debug('Before Create of source Instance - Neg Qty');
2618
2619 csi_item_instance_pub.create_item_instance(l_api_version,
2620 l_commit,
2621 l_init_msg_list,
2622 l_validation_level,
2623 l_new_src_instance_rec,
2624 l_ext_attrib_values_tbl,
2625 l_party_tbl,
2626 l_account_tbl,
2627 l_pricing_attrib_tbl,
2628 l_org_assignments_tbl,
2629 l_asset_assignment_tbl,
2630 l_txn_rec,
2631 l_return_status,
2632 l_msg_count,
2633 l_msg_data);
2634
2635 debug('After Create of Source Item Instance');
2636 debug('New instance created is: '||l_new_src_instance_rec.instance_id);
2637
2638 -- Check for any errors and add them to the message stack to pass out to be put into the
2639 -- error log table.
2640 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2641 debug('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
2642 l_msg_index := 1;
2643 WHILE l_msg_count > 0 loop
2644 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2645 l_msg_index := l_msg_index + 1;
2646 l_msg_count := l_msg_count - 1;
2647 END LOOP;
2648 RAISE fnd_api.g_exc_error;
2649 END IF;
2650
2651 ELSE -- No Records were found and Neg Qtys Not Allowed
2652 debug('No Records were found in Install Base and Neg Qtys not allowed to error');
2653 fnd_message.set_name('CSI','CSI_NO_NEG_BAL_ALLOWED');
2654 l_error_message := fnd_message.get;
2655 RAISE fnd_api.g_exc_error;
2656
2657 END IF; -- Neg Qty IF
2658
2659 ELSE -- Non Serialized Instances Found so Update
2660
2661 debug('Update source record for non seralized item');
2662 debug('Update Source Non Serialized item: '||l_src_instance_header_tbl(i).instance_id);
2663
2664 l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2665 l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
2666 l_update_instance_rec.active_end_date := NULL;
2667 l_update_instance_rec.quantity := l_src_instance_header_tbl(i).quantity - abs(l_mtl_item_tbl(i).primary_quantity);
2668 l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
2669
2670 l_party_tbl.delete;
2671 l_account_tbl.delete;
2672 l_pricing_attrib_tbl.delete;
2673 l_org_assignments_tbl.delete;
2674 l_asset_assignment_tbl.delete;
2675
2676 debug('Before Update Non Serialized Item Instance');
2677
2678 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);
2679
2680 debug('Instance Status Id: '||l_update_instance_rec.instance_status_id);
2681
2682 csi_item_instance_pub.update_item_instance(l_api_version,
2683 l_commit,
2684 l_init_msg_list,
2685 l_validation_level,
2686 l_update_instance_rec,
2687 l_ext_attrib_values_tbl,
2688 l_party_tbl,
2689 l_account_tbl,
2690 l_pricing_attrib_tbl,
2691 l_org_assignments_tbl,
2692 l_asset_assignment_tbl,
2693 l_txn_rec,
2694 l_instance_id_lst,
2695 l_return_status,
2696 l_msg_count,
2697 l_msg_data);
2698
2699 l_upd_error_instance_id := NULL;
2700 l_upd_error_instance_id := l_update_instance_rec.instance_id;
2701
2702 debug('After Update Non Serialzied Item Instance');
2703 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2704
2705 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2706 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2707 debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
2708 l_msg_index := 1;
2709 WHILE l_msg_count > 0 loop
2710 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2711 l_msg_index := l_msg_index + 1;
2712 l_msg_count := l_msg_count - 1;
2713 END LOOP;
2714 RAISE fnd_api.g_exc_error;
2715 END IF;
2716
2717 END IF; -- IF Src Non Serial Recs found
2718
2719 ELSIF (l_mtl_item_tbl(j).serial_number is NOT NULL AND l_return_item = 'Y') OR ( l_mtl_item_tbl(j).serial_number is NOT NULL AND l_serial_tagged = 1) THEN
2720 IF l_src_instance_header_tbl.count = 1 THEN -- Serialized Records found so update
2721
2722 debug('Updating Serialized Item Instance');
2723 --R12 changes,Misc issue on serialized rebuildables/asset numbers leaves the instance in active state and
2724 --all inventory attributes made as null.
2725 debug('l_mtl_item_tbl(j).eam_item_type--'||l_mtl_item_tbl(j).eam_item_type);
2726 l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2727 l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
2728 l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
2729 l_update_instance_rec.quantity := 1;
2730
2731
2732 IF l_mtl_item_tbl(j).eam_item_type in(1,3) THEN
2733 l_update_instance_rec.active_end_date := NULL;
2734 l_update_instance_rec.inv_subinventory_name := NULL;
2735 l_update_instance_rec.inv_locator_id := NULL;
2736 l_update_instance_rec.location_type_code := 'INTERNAL_SITE';
2737 l_update_instance_rec.instance_usage_code := 'OUT_OF_SERVICE';
2738 Begin
2739 SELECT nvl(location_id,NULL)
2740 INTO l_update_instance_rec.location_id
2741 FROM hr_all_organization_units
2742 WHERE organization_id = l_src_instance_header_tbl(i).vld_organization_id;
2743 Exception
2744 WHEN no_data_found THEN
2745 null;
2746 End;
2747 l_update_instance_rec.inv_organization_id := NULL;
2748 --end of R12 changes
2749 ELSE
2750 l_update_instance_rec.active_end_date := l_sysdate;
2751 END IF;
2752
2753 l_party_tbl.delete;
2754 l_account_tbl.delete;
2755 l_pricing_attrib_tbl.delete;
2756 l_org_assignments_tbl.delete;
2757 l_asset_assignment_tbl.delete;
2758
2759 debug('Before Update of Serialized Item Instance');
2760
2761 csi_item_instance_pub.update_item_instance(l_api_version,
2762 l_commit,
2763 l_init_msg_list,
2764 l_validation_level,
2765 l_update_instance_rec,
2766 l_ext_attrib_values_tbl,
2767 l_party_tbl,
2768 l_account_tbl,
2769 l_pricing_attrib_tbl,
2770 l_org_assignments_tbl,
2771 l_asset_assignment_tbl,
2772 l_txn_rec,
2773 l_instance_id_lst,
2774 l_return_status,
2775 l_msg_count,
2776 l_msg_data);
2777
2778 l_upd_error_instance_id := NULL;
2779 l_upd_error_instance_id := l_update_instance_rec.instance_id;
2780
2781 debug('After Update of Serialized Item Instance');
2782 debug('Updated Item Instance: '||l_update_instance_rec.instance_id);
2783 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2784
2785 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
2786 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2787 debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
2788 l_msg_index := 1;
2789 WHILE l_msg_count > 0 loop
2790 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2791 l_msg_index := l_msg_index + 1;
2792 l_msg_count := l_msg_count - 1;
2793 END LOOP;
2794 RAISE fnd_api.g_exc_error;
2795 END IF;
2796
2797 ELSIF l_src_instance_header_tbl.count > 1 THEN
2798 -- Multiple Instances were found so throw error
2799 debug('Multiple Instances were Found in Install Base-30');
2800 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
2801 fnd_message.set_token('INV_ITEM_ID',l_mtl_item_tbl(j).inventory_item_id);
2802 fnd_message.set_token('SUBINV',l_mtl_item_tbl(j).subinventory_code);
2803 fnd_message.set_token('INV_ORG_ID',l_mtl_item_tbl(j).organization_id);
2804 fnd_message.set_token('LOCATOR',l_mtl_item_tbl(j).locator_id);
2805 l_error_message := fnd_message.get;
2806 RAISE fnd_api.g_exc_error;
2807
2808 ELSIF l_src_instance_header_tbl.count = 0 THEN
2809 debug('No Records were found in Install Base');
2810 fnd_message.set_name('CSI','CSI_IB_RECORD_NOTFOUND');
2811 fnd_message.set_token('ITEM',l_mtl_item_tbl(j).inventory_item_id);
2812 fnd_message.set_token('SUBINVENTORY',l_mtl_item_tbl(j).subinventory_code);
2813 fnd_message.set_token('ORG_ID',l_mtl_item_tbl(j).organization_id);
2814 l_error_message := fnd_message.get;
2815 RAISE fnd_api.g_exc_error;
2816 END IF; -- End of Source Record IF
2817
2818 ELSIF l_mtl_item_tbl(j).transaction_type_id in (36,1005) AND l_serial_tagged = 2 THEN
2819 IF l_src_instance_header_tbl.count = 0 AND l_mtl_item_tbl(j).serial_number_control_code = 6 THEN
2820 IF l_neg_code = 1 THEN -- Allow Neg Qtys on NON Serial Items ONLY
2821 -- No Instances so check to see if Neg Qtys Allowed to create source
2822
2823 debug('No records were found and Neg Qtys allowed so create a new Source Instance Record');
2824
2825 l_new_src_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
2826 l_new_src_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2827 l_new_src_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
2828 l_new_src_instance_rec.inv_master_organization_id := l_master_organization_id;
2829 l_new_src_instance_rec.mfg_serial_number_flag := 'N';
2830 l_new_src_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
2831 l_new_src_instance_rec.quantity := l_mtl_item_tbl(j).transaction_quantity;
2832 l_new_src_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
2833 l_new_src_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
2834 l_new_src_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
2835 l_new_src_instance_rec.instance_usage_code := l_in_inventory;
2836 l_new_src_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
2837 l_new_src_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
2838 l_new_src_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
2839 l_new_src_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
2840 l_new_src_instance_rec.customer_view_flag := 'N';
2841 l_new_src_instance_rec.merchant_view_flag := 'Y';
2842 l_new_src_instance_rec.object_version_number := l_object_version_number;
2843 l_new_src_instance_rec.operational_status_code := 'NOT_USED';
2844 l_new_src_instance_rec.active_start_date := l_sysdate;
2845 l_new_src_instance_rec.active_end_date := NULL;
2846
2847 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
2848 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
2849 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
2850 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
2851 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
2852 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
2853
2854 debug('Before Create of source Instance - Neg Qty');
2855
2856 csi_item_instance_pub.create_item_instance(l_api_version,
2857 l_commit,
2858 l_init_msg_list,
2859 l_validation_level,
2860 l_new_src_instance_rec,
2861 l_ext_attrib_values_tbl,
2862 l_party_tbl,
2863 l_account_tbl,
2864 l_pricing_attrib_tbl,
2865 l_org_assignments_tbl,
2866 l_asset_assignment_tbl,
2867 l_txn_rec,
2868 l_return_status,
2869 l_msg_count,
2870 l_msg_data);
2871
2872 debug('After Create of Source Item Instance');
2873 debug('New instance created is: '||l_new_src_instance_rec.instance_id);
2874
2875 -- Check for any errors and add them to the message stack to pass out to be put into the
2876 -- error log table.
2877 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
2878 debug('You encountered an error in the csi_item_instance_pub.create_item_instance API '||l_msg_data);
2879 l_msg_index := 1;
2880 WHILE l_msg_count > 0 loop
2881 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
2882 l_msg_index := l_msg_index + 1;
2883 l_msg_count := l_msg_count - 1;
2884 END LOOP;
2885 RAISE fnd_api.g_exc_error;
2886 END IF;
2887
2888 ELSE -- No Records were found and Neg Qtys Not Allowed
2889 debug('No Records were found in Install Base and Neg Qtys not allowed to error');
2890 fnd_message.set_name('CSI','CSI_NO_NEG_BAL_ALLOWED');
2891 l_error_message := fnd_message.get;
2892 RAISE fnd_api.g_exc_error;
2893
2894 END IF; -- Neg Qty IF
2895
2896 ELSE
2897 --decrement source Instance
2898 debug('Update source record for serial tagged item');
2899 debug('Update Source serial tagged item: '||l_src_instance_header_tbl(i).instance_id);
2900
2901 l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
2902 l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
2903 l_update_instance_rec.active_end_date := NULL;
2904 l_update_instance_rec.quantity := l_src_instance_header_tbl(i).quantity - 1;
2905 l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
2906
2907 l_party_tbl.delete;
2908 l_account_tbl.delete;
2909 l_pricing_attrib_tbl.delete;
2910 l_org_assignments_tbl.delete;
2911 l_asset_assignment_tbl.delete;
2912
2913 debug('Before Update Serial Tagged Item Instance');
2914
2915 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);
2916
2917 debug('Instance Status Id: '||l_update_instance_rec.instance_status_id);
2918
2919 csi_item_instance_pub.update_item_instance(l_api_version,
2920 l_commit,
2921 l_init_msg_list,
2922 l_validation_level,
2923 l_update_instance_rec,
2924 l_ext_attrib_values_tbl,
2925 l_party_tbl,
2926 l_account_tbl,
2927 l_pricing_attrib_tbl,
2928 l_org_assignments_tbl,
2929 l_asset_assignment_tbl,
2930 l_txn_rec,
2931 l_instance_id_lst,
2932 l_return_status,
2933 l_msg_count,
2934 l_msg_data);
2935
2936 l_upd_error_instance_id := NULL;
2937 l_upd_error_instance_id := l_update_instance_rec.instance_id;
2938
2939 debug('After Update Serial Tagged Item Instance');
2940 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
2941 debug('Source Instance Update Completed. Check for destination Instance');
2942
2943 -- Setting the search param for destination instance
2944 -- Querying only with serial number as it is serial tagged
2945 -- Bug 13371469
2946 l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
2947 --l_instance_query_rec.last_vld_organization_id := l_mtl_item_tbl(j).organization_id;
2948 l_instance_query_rec.serial_number := l_mtl_item_tbl(j).serial_number;
2949 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
2950 --l_instance_query_rec.instance_usage_code := l_in_inventory;
2951 --csi_inv_trxs_pkg.set_item_attr_query_values(l_mtl_item_tbl,
2952 -- j,
2953 -- NULL,
2954 -- l_instance_query_rec,
2955 -- x_return_status);
2956
2957 csi_t_gen_utility_pvt.dump_instance_query_rec(p_instance_query_rec => l_instance_query_rec);
2958
2959 debug('Before Get Item Instance');
2960
2961 csi_item_instance_pub.get_item_instances(l_api_version,
2962 l_commit,
2963 l_init_msg_list,
2964 l_validation_level,
2965 l_instance_query_rec,
2966 l_party_query_rec,
2967 l_account_query_rec,
2968 l_transaction_id,
2969 l_resolve_id_columns,
2970 FND_API.G_FALSE,
2971 l_dest_instance_header_tbl,
2972 l_return_status,
2973 l_msg_count,
2974 l_msg_data);
2975
2976 /*csi_item_instance_pub.get_item_instances(l_api_version,
2977 l_commit,
2978 l_init_msg_list,
2979 l_validation_level,
2980 l_instance_query_rec,
2981 l_party_query_rec,
2982 l_account_query_rec,
2983 l_transaction_id,
2984 l_resolve_id_columns,
2985 l_instance_status,
2986 l_dest_instance_header_tbl,
2987 l_return_status,
2988 l_msg_count,
2989 l_msg_data); */
2990
2991 debug('After Get Item Instance');
2992 l_tbl_count := 0;
2993 l_tbl_count := l_dest_instance_header_tbl.count;
2994
2995 debug('No. of destination Instance Found: '||l_tbl_count);
2996
2997 IF l_tbl_count = 0 THEN
2998
2999 --Create new destination Instance
3000 l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
3001 l_new_dest_instance_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
3002 l_new_dest_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
3003 l_new_dest_instance_rec.inv_master_organization_id := l_master_organization_id;
3004 l_new_dest_instance_rec.mfg_serial_number_flag := 'Y';
3005 l_new_dest_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
3006 l_new_dest_instance_rec.quantity := 1;
3007 l_new_dest_instance_rec.unit_of_measure := l_mtl_item_tbl(j).transaction_uom;
3008 l_new_dest_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
3009 l_new_dest_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
3010 l_new_dest_instance_rec.instance_usage_code := l_in_inventory;
3011 l_new_dest_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
3012 l_new_dest_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
3013 l_new_dest_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
3014 l_new_dest_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
3015 l_new_dest_instance_rec.customer_view_flag := 'N';
3016 l_new_dest_instance_rec.merchant_view_flag := 'Y';
3017 l_new_dest_instance_rec.object_version_number := l_object_version_number;
3018 l_new_dest_instance_rec.operational_status_code := 'NOT_USED';
3019 l_new_dest_instance_rec.active_start_date := l_sysdate;
3020 l_new_dest_instance_rec.active_end_date := NULL;
3021 l_new_dest_instance_rec.serial_number := l_mtl_item_tbl(j).serial_number;
3022
3023 l_ext_attrib_values_tbl := csi_inv_trxs_pkg.init_ext_attrib_values_tbl;
3024 l_party_tbl := csi_inv_trxs_pkg.init_party_tbl;
3025 l_account_tbl := csi_inv_trxs_pkg.init_account_tbl;
3026 l_pricing_attrib_tbl := csi_inv_trxs_pkg.init_pricing_attribs_tbl;
3027 l_org_assignments_tbl := csi_inv_trxs_pkg.init_org_assignments_tbl;
3028 l_asset_assignment_tbl := csi_inv_trxs_pkg.init_asset_assignment_tbl;
3029
3030 debug('Before Create of Destination Instance');
3031
3032 csi_item_instance_pub.create_item_instance(l_api_version,
3033 l_commit,
3034 l_init_msg_list,
3035 l_validation_level,
3036 l_new_dest_instance_rec,
3037 l_ext_attrib_values_tbl,
3038 l_party_tbl,
3039 l_account_tbl,
3040 l_pricing_attrib_tbl,
3041 l_org_assignments_tbl,
3042 l_asset_assignment_tbl,
3043 l_txn_rec,
3044 l_return_status,
3045 l_msg_count,
3046 l_msg_data);
3047
3048 debug('After Create of Destination Item Instance');
3049 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
3050 -- Bug 13371469
3051 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
3052 debug('You encountered an error in the destination csi_item_instance_pub.create_item_instance API '||l_msg_data);
3053 l_msg_index := 1;
3054 WHILE l_msg_count > 0 loop
3055 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
3056 l_msg_index := l_msg_index + 1;
3057 l_msg_count := l_msg_count - 1;
3058 END LOOP;
3059 RAISE fnd_api.g_exc_error;
3060 END IF;
3061
3062 debug('New instance created is: '||l_new_dest_instance_rec.instance_id);
3063
3064 ELSE
3065 -- l_new_dest_instance_rec := l_dest_instance_header_tbl(1);
3066 l_new_dest_instance_rec := csi_inv_trxs_pkg.init_instance_create_rec;
3067 l_new_dest_instance_rec.instance_id := l_dest_instance_header_tbl(1).instance_id;
3068 --l_new_dest_instance_rec.inventory_item_id := l_dest_instance_header_tbl(1).inventory_item_id;
3069 --l_new_dest_instance_rec.inv_master_organization_id := l_dest_instance_header_tbl(1).inv_master_organization_id;
3070 -- Put the quantit as 1 but in expired status
3071 l_new_dest_instance_rec.quantity := 1;
3072 -- Skipping instance expiry check as the update is done only to track RTV history
3073 l_new_dest_instance_rec.check_for_instance_expiry := FND_API.G_FALSE;
3074 l_new_dest_instance_rec.object_version_number := l_dest_instance_header_tbl(1).object_version_number;
3075 END IF;
3076 -- End of Bug 13371469
3077
3078 --Now expire this
3079 l_new_dest_instance_rec.active_end_date := SYSDATE;
3080
3081 l_party_tbl.delete;
3082 l_account_tbl.delete;
3083 l_pricing_attrib_tbl.delete;
3084 l_org_assignments_tbl.delete;
3085 l_asset_assignment_tbl.delete;
3086
3087 debug('Before Update of Serial Tagged Item Instance');
3088
3089 csi_item_instance_pub.update_item_instance(l_api_version,
3090 l_commit,
3091 l_init_msg_list,
3092 l_validation_level,
3093 l_new_dest_instance_rec,
3094 l_ext_attrib_values_tbl,
3095 l_party_tbl,
3096 l_account_tbl,
3097 l_pricing_attrib_tbl,
3098 l_org_assignments_tbl,
3099 l_asset_assignment_tbl,
3100 l_txn_rec,
3101 l_instance_id_lst,
3102 l_return_status,
3103 l_msg_count,
3104 l_msg_data);
3105
3106 l_upd_error_instance_id := NULL;
3107 l_upd_error_instance_id := l_new_dest_instance_rec.instance_id;
3108
3109 debug('After Update of Serialized Item Instance');
3110 debug('Updated Item Instance: '||l_new_dest_instance_rec.instance_id);
3111 debug('l_upd_error_instance_id is: '||l_upd_error_instance_id);
3112
3113
3114
3115 -- Check for any errors and add them to the message stack to pass out to be put into the error log table.
3116 IF NOT l_return_status in (l_fnd_success,l_fnd_warning) then
3117 debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
3118 l_msg_index := 1;
3119 WHILE l_msg_count > 0 loop
3120 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
3121 l_msg_index := l_msg_index + 1;
3122 l_msg_count := l_msg_count - 1;
3123 END LOOP;
3124 RAISE fnd_api.g_exc_error;
3125 END IF;
3126
3127 END IF;
3128 END IF; -- End of Serial IF
3129 END LOOP; -- End of For Loop
3130
3131 debug('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
3132 debug('*****End of csi_inv_trxs_pkg.misc_issue Transaction*****');
3133
3134 EXCEPTION
3135 WHEN fnd_api.g_exc_error THEN
3136 debug('You have encountered a "fnd_api.g_exc_error" exception');
3137 x_return_status := l_fnd_error;
3138
3139 IF l_mtl_item_tbl.count > 0 THEN
3140 x_trx_error_rec.serial_number := l_mtl_item_tbl(j).serial_number;
3141 x_trx_error_rec.lot_number := l_mtl_item_tbl(j).lot_number;
3142 x_trx_error_rec.instance_id := l_upd_error_instance_id;
3143 x_trx_error_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
3144 x_trx_error_rec.src_serial_num_ctrl_code := l_mtl_item_tbl(j).serial_number_control_code;
3145 x_trx_error_rec.src_location_ctrl_code := l_mtl_item_tbl(j).location_control_code;
3146 x_trx_error_rec.src_lot_ctrl_code := l_mtl_item_tbl(j).lot_control_code;
3147 x_trx_error_rec.src_rev_qty_ctrl_code := l_mtl_item_tbl(j).revision_qty_control_code;
3148 x_trx_error_rec.comms_nl_trackable_flag := l_mtl_item_tbl(j).comms_nl_trackable_flag;
3149 x_trx_error_rec.transaction_error_date := l_sysdate ;
3150 END IF;
3151
3152 x_trx_error_rec.error_text := l_error_message;
3153 x_trx_error_rec.transaction_id := NULL;
3154 x_trx_error_rec.source_type := 'CSIMSISU';
3155 x_trx_error_rec.source_id := p_transaction_id;
3156 x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
3157 x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
3158 x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
3159 x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
3160
3161 WHEN others THEN
3162 l_sql_error := SQLERRM;
3163 debug('You have encountered a "others" exception');
3164 debug('SQL Error: '||l_sql_error);
3165 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
3166 fnd_message.set_token('API_NAME',l_api_name);
3167 fnd_message.set_token('SQL_ERROR',SQLERRM);
3168 x_return_status := l_fnd_unexpected;
3169
3170 IF l_mtl_item_tbl.count > 0 THEN
3171 x_trx_error_rec.serial_number := l_mtl_item_tbl(j).serial_number;
3172 x_trx_error_rec.lot_number := l_mtl_item_tbl(j).lot_number;
3173 x_trx_error_rec.instance_id := l_upd_error_instance_id;
3174 x_trx_error_rec.inventory_item_id := l_mtl_item_tbl(j).inventory_item_id;
3175 x_trx_error_rec.src_serial_num_ctrl_code := l_mtl_item_tbl(j).serial_number_control_code;
3176 x_trx_error_rec.src_location_ctrl_code := l_mtl_item_tbl(j).location_control_code;
3177 x_trx_error_rec.src_lot_ctrl_code := l_mtl_item_tbl(j).lot_control_code;
3178 x_trx_error_rec.src_rev_qty_ctrl_code := l_mtl_item_tbl(j).revision_qty_control_code;
3179 x_trx_error_rec.comms_nl_trackable_flag := l_mtl_item_tbl(j).comms_nl_trackable_flag;
3180 x_trx_error_rec.transaction_error_date := l_sysdate ;
3181 END IF;
3182
3183 x_trx_error_rec.error_text := fnd_message.get;
3184 x_trx_error_rec.transaction_id := NULL;
3185 x_trx_error_rec.source_type := 'CSIMSISU';
3186 x_trx_error_rec.source_id := p_transaction_id;
3187 x_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
3188 x_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id(l_trans_type_code,l_trans_app_code);
3189 x_trx_error_rec.inv_material_transaction_id := p_transaction_id;
3190 x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
3191
3192 END misc_issue;
3193
3194 PROCEDURE cycle_count(p_transaction_id IN NUMBER,
3195 p_message_id IN NUMBER,
3196 x_return_status OUT NOCOPY VARCHAR2,
3197 x_trx_error_rec OUT NOCOPY CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC)
3198 IS
3199
3200 l_api_name VARCHAR2(100) := 'CSI_INV_TRXS_PKG.CYCLE_COUNT';
3201 l_return_status VARCHAR2(1);
3202 l_error_code VARCHAR2(50);
3203 l_error_message VARCHAR2(4000);
3204 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3205 l_fnd_warning VARCHAR2(1) := 'W';
3206 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
3207 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
3208 l_sql_error VARCHAR2(2000);
3209 r_quantity NUMBER := 0;
3210 l_trx_error_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC;
3211
3212 cursor C_QUANTITY is
3213 select transaction_quantity
3214 from mtl_material_transactions
3215 where transaction_id = p_transaction_id;
3216
3217 BEGIN
3218
3219 x_return_status := l_fnd_success;
3220
3221 debug('*****Start of csi_inv_trxs_pkg.cycle_count Transaction*****');
3222 debug('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
3223 debug('csiivtxb.pls 115.25');
3224 debug('Transaction You are Processing is: '||p_transaction_id);
3225
3226 open C_QUANTITY;
3227 fetch C_QUANTITY into R_QUANTITY;
3228 close C_QUANTITY;
3229
3230 if r_quantity > 0 then
3231 csi_inv_trxs_pkg.misc_receipt(p_transaction_id,
3232 p_message_id,
3233 l_return_status,
3234 l_trx_error_rec);
3235
3236 IF NOT l_return_status = l_fnd_success THEN
3237 debug('You have encountered an error in CSI_INV_TRXS_PKG.cycle_count');
3238 RAISE fnd_api.g_exc_error;
3239 END IF;
3240 ELSIF r_quantity < 0 then
3241 csi_inv_trxs_pkg.misc_issue(p_transaction_id,
3242 p_message_id,
3243 l_return_status,
3244 l_trx_error_rec);
3245
3246 IF NOT l_return_status = l_fnd_success THEN
3247 debug('You have encountered an error in CSI_INV_TRXS_PKG.cycle_count');
3248 RAISE fnd_api.g_exc_error;
3249 END IF;
3250 END IF;
3251
3252 debug('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
3253 debug('*****End of csi_inv_trxs_pkg.cycle_count Transaction*****');
3254
3255 EXCEPTION
3256 WHEN fnd_api.g_exc_error THEN
3257 debug('You have encountered a "fnd_api.g_exc_error" exception');
3258 x_return_status := l_fnd_error;
3259 l_trx_error_rec.source_type := 'CSICYCNT';
3260 x_trx_error_rec := l_trx_error_rec;
3261
3262 WHEN others THEN
3263 l_sql_error := SQLERRM;
3264 debug('You have encountered a "others" exception');
3265 debug('SQL Error: '||l_sql_error);
3266 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
3267 fnd_message.set_token('API_NAME',l_api_name);
3268 fnd_message.set_token('SQL_ERROR',SQLERRM);
3269 x_return_status := l_fnd_unexpected;
3270 l_trx_error_rec.error_text := fnd_message.get;
3271 l_trx_error_rec.transaction_id := NULL;
3272 l_trx_error_rec.source_type := 'CSICYCNT';
3273 l_trx_error_rec.source_id := p_transaction_id;
3274 l_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
3275 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('CYCLE_COUNT','INV');
3276 l_trx_error_rec.inv_material_transaction_id := p_transaction_id;
3277 l_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
3278 x_trx_error_rec := l_trx_error_rec;
3279
3280 END cycle_count;
3281
3282 PROCEDURE physical_inventory(p_transaction_id IN NUMBER,
3283 p_message_id IN NUMBER,
3284 x_return_status OUT NOCOPY VARCHAR2,
3285 x_trx_error_rec OUT NOCOPY CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC)
3286 IS
3287
3288 l_api_name VARCHAR2(100) := 'CSI_INV_TRXS_PKG.PHYSICAL_INVENTORY';
3289 l_return_status VARCHAR2(1);
3290 l_error_code VARCHAR2(50);
3291 l_error_message VARCHAR2(4000);
3292 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3293 l_fnd_warning VARCHAR2(1) := 'W';
3294 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
3295 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
3296 l_sql_error VARCHAR2(2000);
3297 r_quantity NUMBER := 0;
3298 l_trx_error_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC;
3299
3300 cursor C_QUANTITY is
3301 select transaction_quantity
3302 from mtl_material_transactions
3303 where transaction_id = p_transaction_id;
3304
3305 BEGIN
3306 x_return_status := l_fnd_success;
3307
3308 debug('*****Start of csi_inv_trxs_pkg.physical_inventory Transaction*****');
3309 debug('Start time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
3310 debug('csiorgtb.pls 115.25');
3311 debug('Transaction You are Processing is: '||p_transaction_id);
3312
3313 open C_QUANTITY;
3314 fetch C_QUANTITY into R_QUANTITY;
3315 close C_QUANTITY;
3316
3317 if r_quantity > 0 then
3318 csi_inv_trxs_pkg.misc_receipt(p_transaction_id,
3319 p_message_id,
3320 l_return_status,
3321 l_trx_error_rec);
3322
3323 IF NOT l_return_status = l_fnd_success THEN
3324 debug('You have encountered an error in CSI_INV_TRXS_PKG.physical_inventory');
3325 RAISE fnd_api.g_exc_error;
3326 END IF;
3327 ELSIF r_quantity < 0 then
3328 csi_inv_trxs_pkg.misc_issue(p_transaction_id,
3329 p_message_id,
3330 l_return_status,
3331 l_trx_error_rec);
3332
3333 IF NOT l_return_status = l_fnd_success THEN
3334 debug('You have encountered an error in CSI_INV_TRXS_PKG.physical_inventory');
3335 RAISE fnd_api.g_exc_error;
3336 END IF;
3337 END IF;
3338
3339 debug('End time: '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
3340 debug('*****End of csi_inv_trxs_pkg.physical_inventory Transaction*****');
3341
3342 EXCEPTION
3343 WHEN fnd_api.g_exc_error THEN
3344 debug('You have encountered a "fnd_api.g_exc_error" exception');
3345 x_return_status := l_fnd_error;
3346 l_trx_error_rec.source_type := 'CSIPHYIN';
3347 x_trx_error_rec := l_trx_error_rec;
3348
3349 WHEN others THEN
3350 l_sql_error := SQLERRM;
3351 debug('You have encountered a "others" exception');
3352 debug('SQL Error: '||l_sql_error);
3353 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
3354 fnd_message.set_token('API_NAME',l_api_name);
3355 fnd_message.set_token('SQL_ERROR',SQLERRM);
3356 x_return_status := l_fnd_unexpected;
3357 l_trx_error_rec.error_text := fnd_message.get;
3358 l_trx_error_rec.transaction_id := NULL;
3359 l_trx_error_rec.source_type := 'CSIPHYIN';
3360 l_trx_error_rec.source_id := p_transaction_id;
3361 l_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
3362 l_trx_error_rec.transaction_type_id := csi_inv_trxs_pkg.get_txn_type_id('PHYSICAL_INVENTORY','INV');
3363 l_trx_error_rec.inv_material_transaction_id := p_transaction_id;
3364 l_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
3365 x_trx_error_rec := l_trx_error_rec;
3366
3367 END physical_inventory;
3368
3369 PROCEDURE get_rtv_transaction_recs(p_transaction_id IN NUMBER,
3370 x_mtl_item_tbl OUT NOCOPY CSI_INV_TRXS_PKG.MTL_ITEM_TBL_TYPE,
3371 x_return_status OUT NOCOPY VARCHAR2,
3372 x_error_message OUT NOCOPY VARCHAR2)
3373 IS
3374
3375 l_api_name VARCHAR2(100) := 'CSI_INV_TRXS_PKG.GET_RTV_TRANSACTION_RECS';
3376 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3377 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
3378 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
3379 l_sql_error VARCHAR2(2000);
3380 i PLS_INTEGER;
3381
3382 CURSOR c_items IS
3383 SELECT
3384 mmt.inventory_item_id inventory_item_id,
3385 mmt.organization_id organization_id,
3386 mmt.subinventory_code subinventory_code,
3387 mmt.transfer_organization_id transfer_organization_id,
3388 mmt.transfer_subinventory transfer_subinventory,
3389 mmt.revision revision,
3390 mmt.transaction_quantity transaction_quantity,
3391 mmt.primary_quantity primary_quantity,
3392 mmt.transaction_uom transaction_uom,
3393 msib.primary_uom_code primary_uom_code,
3394 mmt.transaction_type_id transaction_type_id,
3395 mmt.transaction_action_id transaction_action_id,
3396 mmt.transaction_source_id transaction_source_id,
3397 mmt.transaction_source_type_id transaction_source_type_id,
3398 mmt.transfer_locator_id transfer_locator_id,
3399 mmt.locator_id locator_id,
3400 mmt.source_project_id source_project_id,
3401 mmt.source_task_id source_task_id,
3402 mmt.project_id from_project_id,
3403 mmt.task_id from_task_id,
3404 mmt.to_project_id to_project_id,
3405 mmt.to_task_id to_task_id,
3406 mmt.transaction_date transaction_date,
3407 mmt.last_updated_by last_updated_by,
3408 mut.serial_number serial_number,
3409 NULL lot_number,
3410 msi.location_id subinv_location_id,
3411 rt.po_distribution_id po_distribution_id,
3412 haou.location_id hr_location_id,
3413 mmt.shipment_number shipment_number,
3414 mmt.trx_source_line_id trx_source_line_id,
3415 mmt.move_order_line_id move_order_line_id,
3416 msib.serial_number_control_code serial_number_control_code,
3417 msib.lot_control_code lot_control_code,
3418 msib.revision_qty_control_code revision_qty_control_code,
3419 msib.comms_nl_trackable_flag comms_nl_trackable_flag,
3420 msib.location_control_code location_control_code,
3421 mmt.ship_to_location_id ship_to_location_id,
3422 mmt.physical_adjustment_id physical_adjustment_id,
3423 mmt.cycle_count_id cycle_count_id,
3424 nvl(msib.eam_item_type,0) eam_item_type, --included for R12,eAM integration
3425 mmt.rcv_transaction_id rcv_transaction_id,
3426 mmt.transfer_transaction_id transfer_transaction_id
3427 FROM
3428 mtl_system_items_b msib,
3429 mtl_serial_numbers msn,
3430 mtl_unit_transactions mut,
3431 mtl_secondary_inventories msi,
3432 hr_all_organization_units haou,
3433 rcv_transactions rt,
3434 mtl_material_transactions mmt
3435 WHERE
3436 mmt.transaction_id = p_transaction_id AND
3437 mmt.inventory_item_id = msib.inventory_item_id AND
3438 mmt.organization_id = msib.organization_id AND
3439 msib.lot_control_code <> 2 AND
3440 mmt.rcv_transaction_id = rt.transaction_id(+) AND
3441 mmt.organization_id = haou.organization_id(+) AND
3442 mmt.subinventory_code = msi.secondary_inventory_name(+) AND
3443 mmt.organization_id = msi.organization_id(+) AND
3444 mmt.transaction_id = mut.transaction_id(+) AND
3445 mut.inventory_item_id = msn.inventory_item_id(+) AND
3446 mut.serial_number = msn.serial_number(+)
3447 UNION ALL
3448 SELECT
3449 mmt.inventory_item_id inventory_item_id,
3450 mmt.organization_id organization_id,
3451 mmt.subinventory_code subinventory_code,
3452 mmt.transfer_organization_id transfer_organization_id,
3453 mmt.transfer_subinventory transfer_subinventory,
3454 mmt.revision revision,
3455 mtln.transaction_quantity transaction_quantity,
3456 mtln.primary_quantity primary_quantity,
3457 mmt.transaction_uom transaction_uom,
3458 msib.primary_uom_code primary_uom_code,
3459 mmt.transaction_type_id transaction_type_id,
3460 mmt.transaction_action_id transaction_action_id,
3461 mmt.transaction_source_id transaction_source_id,
3462 mmt.transaction_source_type_id transaction_source_type_id,
3463 mmt.transfer_locator_id transfer_locator_id,
3464 mmt.locator_id locator_id,
3465 mmt.source_project_id source_project_id,
3466 mmt.source_task_id source_task_id,
3467 mmt.project_id from_project_id,
3468 mmt.task_id from_task_id,
3469 mmt.to_project_id to_project_id,
3470 mmt.to_task_id to_task_id,
3471 mmt.transaction_date transaction_date,
3472 mmt.last_updated_by last_updated_by,
3473 mut.serial_number serial_number,
3474 mtln.lot_number lot_number,
3475 msi.location_id subinv_location_id,
3476 rt.po_distribution_id po_distribution_id,
3477 haou.location_id hr_location_id,
3478 mmt.shipment_number shipment_number,
3479 mmt.trx_source_line_id trx_source_line_id,
3480 mmt.move_order_line_id move_order_line_id,
3481 msib.serial_number_control_code serial_number_control_code,
3482 msib.lot_control_code lot_control_code,
3483 msib.revision_qty_control_code revision_qty_control_code,
3484 msib.comms_nl_trackable_flag comms_nl_trackable_flag,
3485 msib.location_control_code location_control_code,
3486 mmt.ship_to_location_id ship_to_location_id,
3487 mmt.physical_adjustment_id physical_adjustment_id,
3488 mmt.cycle_count_id cycle_count_id,
3489 nvl(msib.eam_item_type,0) eam_item_type, --included for R12,eAM integration
3490 mmt.rcv_transaction_id rcv_transaction_id,
3491 mmt.transfer_transaction_id transfer_transaction_id
3492 FROM
3493 mtl_system_items_b msib,
3494 mtl_serial_numbers msn,
3495 mtl_unit_transactions mut,
3496 mtl_transaction_lot_numbers mtln,
3497 mtl_secondary_inventories msi,
3498 hr_all_organization_units haou,
3499 rcv_transactions rt,
3500 mtl_material_transactions mmt
3501 WHERE
3502 mmt.transaction_id = p_transaction_id AND
3503 mmt.inventory_item_id = msib.inventory_item_id AND
3504 mmt.organization_id = msib.organization_id AND
3505 msib.lot_control_code = 2 AND
3506 mmt.rcv_transaction_id = rt.transaction_id(+) AND
3507 mmt.organization_id = haou.organization_id(+) AND
3508 mmt.subinventory_code = msi.secondary_inventory_name(+) AND
3509 mmt.organization_id = msi.organization_id(+) AND
3510 mmt.transaction_id = mtln.transaction_id(+) AND
3511 mtln.serial_transaction_id = mut.transaction_id(+) AND
3512 mut.inventory_item_id = msn.inventory_item_id(+) AND
3513 mut.serial_number = msn.serial_number(+);
3514
3515 BEGIN
3516 i := 1;
3517 FOR r_items IN c_items LOOP
3518 x_mtl_item_tbl(i).inventory_item_id := r_items.inventory_item_id;
3519 x_mtl_item_tbl(i).organization_id := r_items.organization_id;
3520 x_mtl_item_tbl(i).subinventory_code := r_items.subinventory_code;
3521 x_mtl_item_tbl(i).revision := r_items.revision;
3522 x_mtl_item_tbl(i).transaction_quantity := r_items.transaction_quantity;
3523 x_mtl_item_tbl(i).primary_quantity := r_items.primary_quantity;
3524 x_mtl_item_tbl(i).transaction_uom := r_items.transaction_uom;
3525 x_mtl_item_tbl(i).primary_uom_code := r_items.primary_uom_code;
3526 x_mtl_item_tbl(i).transaction_type_id := r_items.transaction_type_id;
3527 x_mtl_item_tbl(i).transaction_action_id := r_items.transaction_action_id;
3528 x_mtl_item_tbl(i).transaction_source_id := r_items.transaction_source_id;
3529 x_mtl_item_tbl(i).transaction_source_type_id := r_items.transaction_source_type_id;
3530 x_mtl_item_tbl(i).transfer_locator_id := r_items.transfer_locator_id;
3531 x_mtl_item_tbl(i).transfer_organization_id := r_items.transfer_organization_id;
3532 x_mtl_item_tbl(i).transfer_subinventory := r_items.transfer_subinventory;
3533 x_mtl_item_tbl(i).locator_id := r_items.locator_id;
3534 x_mtl_item_tbl(i).source_project_id := r_items.source_project_id;
3535 x_mtl_item_tbl(i).source_task_id := r_items.source_task_id;
3536 x_mtl_item_tbl(i).from_project_id := r_items.from_project_id;
3537 x_mtl_item_tbl(i).from_task_id := r_items.from_task_id;
3538 x_mtl_item_tbl(i).to_project_id := r_items.to_project_id;
3539 x_mtl_item_tbl(i).to_task_id := r_items.to_task_id;
3540 x_mtl_item_tbl(i).transaction_date := r_items.transaction_date;
3541 x_mtl_item_tbl(i).last_updated_by := r_items.last_updated_by;
3542 x_mtl_item_tbl(i).serial_number := r_items.serial_number;
3543 x_mtl_item_tbl(i).lot_number := r_items.lot_number;
3544 x_mtl_item_tbl(i).subinv_location_id := r_items.subinv_location_id;
3545 x_mtl_item_tbl(i).po_distribution_id := r_items.po_distribution_id;
3546 x_mtl_item_tbl(i).hr_location_id := r_items.hr_location_id;
3547 x_mtl_item_tbl(i).shipment_number := r_items.shipment_number;
3548 x_mtl_item_tbl(i).trx_source_line_id := r_items.trx_source_line_id;
3549 x_mtl_item_tbl(i).move_order_line_id := r_items.move_order_line_id;
3550 x_mtl_item_tbl(i).serial_number_control_code := r_items.serial_number_control_code;
3551 x_mtl_item_tbl(i).lot_control_code := r_items.lot_control_code;
3552 x_mtl_item_tbl(i).revision_qty_control_code := r_items.revision_qty_control_code;
3553 x_mtl_item_tbl(i).comms_nl_trackable_flag := r_items.comms_nl_trackable_flag;
3554 x_mtl_item_tbl(i).location_control_code := r_items.location_control_code;
3555 x_mtl_item_tbl(i).ship_to_location_id := r_items.ship_to_location_id;
3556 x_mtl_item_tbl(i).physical_adjustment_id := r_items.physical_adjustment_id;
3557 x_mtl_item_tbl(i).cycle_count_id := r_items.cycle_count_id;
3558 x_mtl_item_tbl(i).eam_item_type := r_items.eam_item_type; --for R12,eAM integration
3559 x_mtl_item_tbl(i).rcv_transaction_id := r_items.rcv_transaction_id;
3560 x_mtl_item_tbl(i).transfer_transaction_id := r_items.transfer_transaction_id;
3561
3562 i := i + 1;
3563 END LOOP;
3564
3565 IF i = 1 then
3566 RAISE no_data_found;
3567 END IF;
3568
3569 EXCEPTION
3570 WHEN NO_DATA_FOUND THEN
3571 fnd_message.set_name('CSI','CSI_NO_INVENTORY_RECORDS');
3572 fnd_message.set_token('MTL_TRANSACTION_ID',p_transaction_id);
3573 x_error_message := fnd_message.get;
3574 x_return_status := l_fnd_error;
3575
3576 WHEN others THEN
3577 l_sql_error := SQLERRM;
3578 debug('You have encountered a "others" exception');
3579 debug('SQL Error: '||l_sql_error);
3580 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
3581 fnd_message.set_token('API_NAME',l_api_name);
3582 fnd_message.set_token('SQL_ERROR',SQLERRM);
3583 x_error_message := fnd_message.get;
3584 x_return_status := l_fnd_unexpected;
3585 END get_rtv_transaction_recs;
3586
3587 PROCEDURE get_transaction_recs(p_transaction_id IN NUMBER,
3588 x_mtl_item_tbl OUT NOCOPY CSI_INV_TRXS_PKG.MTL_ITEM_TBL_TYPE,
3589 x_return_status OUT NOCOPY VARCHAR2,
3590 x_error_message OUT NOCOPY VARCHAR2)
3591 IS
3592
3593 l_api_name VARCHAR2(100) := 'CSI_INV_TRXS_PKG.GET_TRANSACTION_RECS';
3594 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3595 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
3596 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
3597 l_sql_error VARCHAR2(2000);
3598 i PLS_INTEGER;
3599
3600 CURSOR c_items IS
3601 SELECT
3602 mmt.inventory_item_id inventory_item_id,
3603 mmt.organization_id organization_id,
3604 mmt.subinventory_code subinventory_code,
3605 mmt.transfer_organization_id transfer_organization_id,
3606 mmt.transfer_subinventory transfer_subinventory,
3607 mmt.revision revision,
3608 mmt.transaction_quantity transaction_quantity,
3609 mmt.primary_quantity primary_quantity,
3610 mmt.transaction_uom transaction_uom,
3611 msib.primary_uom_code primary_uom_code,
3612 mmt.transaction_type_id transaction_type_id,
3613 mmt.transaction_action_id transaction_action_id,
3614 mmt.transaction_source_id transaction_source_id,
3615 mmt.transaction_source_type_id transaction_source_type_id,
3616 mmt.transfer_locator_id transfer_locator_id,
3617 mmt.locator_id locator_id,
3618 mmt.source_project_id source_project_id,
3619 mmt.source_task_id source_task_id,
3620 mmt.project_id from_project_id,
3621 mmt.task_id from_task_id,
3622 mmt.to_project_id to_project_id,
3623 mmt.to_task_id to_task_id,
3624 mmt.transaction_date transaction_date,
3625 mmt.last_updated_by last_updated_by,
3626 msn.serial_number serial_number,
3627 NULL lot_number,
3628 msi.location_id subinv_location_id,
3629 rt.po_distribution_id po_distribution_id,
3630 haou.location_id hr_location_id,
3631 mmt.shipment_number shipment_number,
3632 mmt.trx_source_line_id trx_source_line_id,
3633 mmt.move_order_line_id move_order_line_id,
3634 msib.serial_number_control_code serial_number_control_code,
3635 -- msib.lot_control_code lot_control_code,---- Commented for bug#14835893
3636 nvl(csi_utl_pkg.get_lot_ctrl_code(p_transaction_id),lot_control_code) lot_control_code,-- Added for bug#14835893
3637 msib.revision_qty_control_code revision_qty_control_code,
3638 msib.comms_nl_trackable_flag comms_nl_trackable_flag,
3639 msib.location_control_code location_control_code,
3640 mmt.ship_to_location_id ship_to_location_id,
3641 mmt.physical_adjustment_id physical_adjustment_id,
3642 mmt.cycle_count_id cycle_count_id,
3643 nvl(msib.eam_item_type,0) eam_item_type, --included for R12,eAM integration
3644 mmt.rcv_transaction_id rcv_transaction_id,
3645 mmt.transfer_transaction_id transfer_transaction_id
3646 FROM
3647 mtl_system_items_b msib,
3648 mtl_serial_numbers msn,
3649 mtl_unit_transactions mut,
3650 mtl_secondary_inventories msi,
3651 hr_all_organization_units haou,
3652 rcv_transactions rt,
3653 mtl_material_transactions mmt
3654 WHERE
3655 mmt.transaction_id = p_transaction_id AND
3656 mmt.inventory_item_id = msib.inventory_item_id AND
3657 mmt.organization_id = msib.organization_id AND
3658 --msib.lot_control_code <> 2 AND
3659 nvl(csi_utl_pkg.get_lot_ctrl_code(p_transaction_id),msib.lot_control_code) <>2 AND -- Added for bug#14835893
3660 mmt.rcv_transaction_id = rt.transaction_id(+) AND
3661 mmt.organization_id = haou.organization_id(+) AND
3662 mmt.subinventory_code = msi.secondary_inventory_name(+) AND
3663 mmt.organization_id = msi.organization_id(+) AND
3664 mmt.transaction_id = mut.transaction_id(+) AND
3665 mut.inventory_item_id = msn.inventory_item_id(+) AND
3666 mut.serial_number = msn.serial_number(+)
3667 UNION ALL
3668 SELECT
3669 mmt.inventory_item_id inventory_item_id,
3670 mmt.organization_id organization_id,
3671 mmt.subinventory_code subinventory_code,
3672 mmt.transfer_organization_id transfer_organization_id,
3673 mmt.transfer_subinventory transfer_subinventory,
3674 mmt.revision revision,
3675 mtln.transaction_quantity transaction_quantity,
3676 mtln.primary_quantity primary_quantity,
3677 mmt.transaction_uom transaction_uom,
3678 msib.primary_uom_code primary_uom_code,
3679 mmt.transaction_type_id transaction_type_id,
3680 mmt.transaction_action_id transaction_action_id,
3681 mmt.transaction_source_id transaction_source_id,
3682 mmt.transaction_source_type_id transaction_source_type_id,
3683 mmt.transfer_locator_id transfer_locator_id,
3684 mmt.locator_id locator_id,
3685 mmt.source_project_id source_project_id,
3686 mmt.source_task_id source_task_id,
3687 mmt.project_id from_project_id,
3688 mmt.task_id from_task_id,
3689 mmt.to_project_id to_project_id,
3690 mmt.to_task_id to_task_id,
3691 mmt.transaction_date transaction_date,
3692 mmt.last_updated_by last_updated_by,
3693 msn.serial_number serial_number,
3694 mtln.lot_number lot_number,
3695 msi.location_id subinv_location_id,
3696 rt.po_distribution_id po_distribution_id,
3697 haou.location_id hr_location_id,
3698 mmt.shipment_number shipment_number,
3699 mmt.trx_source_line_id trx_source_line_id,
3700 mmt.move_order_line_id move_order_line_id,
3701 msib.serial_number_control_code serial_number_control_code,
3702 -- msib.lot_control_code lot_control_code,
3703 nvl(csi_utl_pkg.get_lot_ctrl_code(p_transaction_id),lot_control_code) lot_control_code,-- Added for bug#14835893
3704 msib.revision_qty_control_code revision_qty_control_code,
3705 msib.comms_nl_trackable_flag comms_nl_trackable_flag,
3706 msib.location_control_code location_control_code,
3707 mmt.ship_to_location_id ship_to_location_id,
3708 mmt.physical_adjustment_id physical_adjustment_id,
3709 mmt.cycle_count_id cycle_count_id,
3710 nvl(msib.eam_item_type,0) eam_item_type, --included for R12,eAM integration
3711 mmt.rcv_transaction_id rcv_transaction_id,
3712 mmt.transfer_transaction_id transfer_transaction_id
3713 FROM
3714 mtl_system_items_b msib,
3715 mtl_serial_numbers msn,
3716 mtl_unit_transactions mut,
3717 mtl_transaction_lot_numbers mtln,
3718 mtl_secondary_inventories msi,
3719 hr_all_organization_units haou,
3720 rcv_transactions rt,
3721 mtl_material_transactions mmt
3722 WHERE
3723 mmt.transaction_id = p_transaction_id AND
3724 mmt.inventory_item_id = msib.inventory_item_id AND
3725 mmt.organization_id = msib.organization_id AND
3726 -- msib.lot_control_code = 2 AND
3727 nvl(csi_utl_pkg.get_lot_ctrl_code(p_transaction_id),msib.lot_control_code) =2 AND -- Added for bug#14835893
3728 mmt.rcv_transaction_id = rt.transaction_id(+) AND
3729 mmt.organization_id = haou.organization_id(+) AND
3730 mmt.subinventory_code = msi.secondary_inventory_name(+) AND
3731 mmt.organization_id = msi.organization_id(+) AND
3732 mmt.transaction_id = mtln.transaction_id(+) AND
3733 mtln.serial_transaction_id = mut.transaction_id(+) AND
3734 mut.inventory_item_id = msn.inventory_item_id(+) AND
3735 mut.serial_number = msn.serial_number(+);
3736
3737
3738 BEGIN
3739 i := 1;
3740 FOR r_items IN c_items LOOP
3741 x_mtl_item_tbl(i).inventory_item_id := r_items.inventory_item_id;
3742 x_mtl_item_tbl(i).organization_id := r_items.organization_id;
3743 x_mtl_item_tbl(i).subinventory_code := r_items.subinventory_code;
3744 x_mtl_item_tbl(i).revision := r_items.revision;
3745 x_mtl_item_tbl(i).transaction_quantity := r_items.transaction_quantity;
3746 x_mtl_item_tbl(i).primary_quantity := r_items.primary_quantity;
3747 x_mtl_item_tbl(i).transaction_uom := r_items.transaction_uom;
3748 x_mtl_item_tbl(i).primary_uom_code := r_items.primary_uom_code;
3749 x_mtl_item_tbl(i).transaction_type_id := r_items.transaction_type_id;
3750 x_mtl_item_tbl(i).transaction_action_id := r_items.transaction_action_id;
3751 x_mtl_item_tbl(i).transaction_source_id := r_items.transaction_source_id;
3752 x_mtl_item_tbl(i).transaction_source_type_id := r_items.transaction_source_type_id;
3753 x_mtl_item_tbl(i).transfer_locator_id := r_items.transfer_locator_id;
3754 x_mtl_item_tbl(i).transfer_organization_id := r_items.transfer_organization_id;
3755 x_mtl_item_tbl(i).transfer_subinventory := r_items.transfer_subinventory;
3756 x_mtl_item_tbl(i).locator_id := r_items.locator_id;
3757 x_mtl_item_tbl(i).source_project_id := r_items.source_project_id;
3758 x_mtl_item_tbl(i).source_task_id := r_items.source_task_id;
3759 x_mtl_item_tbl(i).from_project_id := r_items.from_project_id;
3760 x_mtl_item_tbl(i).from_task_id := r_items.from_task_id;
3761 x_mtl_item_tbl(i).to_project_id := r_items.to_project_id;
3762 x_mtl_item_tbl(i).to_task_id := r_items.to_task_id;
3763 x_mtl_item_tbl(i).transaction_date := r_items.transaction_date;
3764 x_mtl_item_tbl(i).last_updated_by := r_items.last_updated_by;
3765 x_mtl_item_tbl(i).serial_number := r_items.serial_number;
3766 x_mtl_item_tbl(i).lot_number := r_items.lot_number;
3767 x_mtl_item_tbl(i).subinv_location_id := r_items.subinv_location_id;
3768 x_mtl_item_tbl(i).po_distribution_id := r_items.po_distribution_id;
3769 x_mtl_item_tbl(i).hr_location_id := r_items.hr_location_id;
3770 x_mtl_item_tbl(i).shipment_number := r_items.shipment_number;
3771 x_mtl_item_tbl(i).trx_source_line_id := r_items.trx_source_line_id;
3772 x_mtl_item_tbl(i).move_order_line_id := r_items.move_order_line_id;
3773 x_mtl_item_tbl(i).serial_number_control_code := r_items.serial_number_control_code;
3774 x_mtl_item_tbl(i).lot_control_code := r_items.lot_control_code;
3775 x_mtl_item_tbl(i).revision_qty_control_code := r_items.revision_qty_control_code;
3776 x_mtl_item_tbl(i).comms_nl_trackable_flag := r_items.comms_nl_trackable_flag;
3777 x_mtl_item_tbl(i).location_control_code := r_items.location_control_code;
3778 x_mtl_item_tbl(i).ship_to_location_id := r_items.ship_to_location_id;
3779 x_mtl_item_tbl(i).physical_adjustment_id := r_items.physical_adjustment_id;
3780 x_mtl_item_tbl(i).cycle_count_id := r_items.cycle_count_id;
3781 x_mtl_item_tbl(i).eam_item_type := r_items.eam_item_type; --for R12,eAM integration
3782 x_mtl_item_tbl(i).rcv_transaction_id := r_items.rcv_transaction_id;
3783 x_mtl_item_tbl(i).transfer_transaction_id := r_items.transfer_transaction_id;
3784
3785 i := i + 1;
3786 END LOOP;
3787
3788 IF i = 1 then
3789 RAISE no_data_found;
3790 END IF;
3791
3792 EXCEPTION
3793 WHEN NO_DATA_FOUND THEN
3794 fnd_message.set_name('CSI','CSI_NO_INVENTORY_RECORDS');
3795 fnd_message.set_token('MTL_TRANSACTION_ID',p_transaction_id);
3796 x_error_message := fnd_message.get;
3797 x_return_status := l_fnd_error;
3798
3799 WHEN others THEN
3800 l_sql_error := SQLERRM;
3801 debug('You have encountered a "others" exception');
3802 debug('SQL Error: '||l_sql_error);
3803 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
3804 fnd_message.set_token('API_NAME',l_api_name);
3805 fnd_message.set_token('SQL_ERROR',SQLERRM);
3806 x_error_message := fnd_message.get;
3807 x_return_status := l_fnd_unexpected;
3808 END get_transaction_recs;
3809
3810 PROCEDURE decode_message (p_msg_header IN XNP_MESSAGE.MSG_HEADER_REC_TYPE,
3811 p_msg_text IN VARCHAR2,
3812 x_return_status OUT NOCOPY VARCHAR2,
3813 x_error_message OUT NOCOPY VARCHAR2,
3814 x_mtl_trx_rec OUT NOCOPY CSI_INV_TRXS_PKG.MTL_TRX_TYPE) IS
3815
3816 l_api_name VARCHAR2(100) := 'CSI_INV_TRXS_PKG.DECODE_MESSAGE';
3817 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
3818 l_sql_error VARCHAR2(2000);
3819
3820 BEGIN
3821 xnp_xml_utils.decode(P_Msg_Text, 'MTL_TRANSACTION_ID', X_MTL_TRX_REC.MTL_TRANSACTION_ID);
3822
3823 IF (X_MTL_TRX_REC.MTL_TRANSACTION_ID is NULL) or
3824 (X_MTL_TRX_REC.MTL_TRANSACTION_ID = FND_API.G_MISS_NUM) THEN
3825 RAISE fnd_api.g_exc_error;
3826 END IF;
3827
3828 EXCEPTION
3829 WHEN fnd_api.g_exc_error THEN
3830 fnd_message.set_name('CSI','CSI_DECODE_MGS_ERROR');
3831 fnd_message.set_token('MESSAGE_ID',p_msg_header.message_id);
3832 fnd_message.set_token('MESSAGE_CODE',p_msg_header.message_code);
3833 x_error_message := fnd_message.get;
3834 x_return_status := l_fnd_unexpected;
3835
3836 WHEN others THEN
3837 l_sql_error := SQLERRM;
3838 debug('You have encountered a "others" exception');
3839 debug('SQL Error: '||l_sql_error);
3840 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
3841 fnd_message.set_token('API_NAME',l_api_name);
3842 fnd_message.set_token('SQL_ERROR',SQLERRM);
3843 x_error_message := fnd_message.get;
3844 x_return_status := l_fnd_unexpected;
3845 END decode_message;
3846
3847 PROCEDURE get_asset_creation_code(
3848 p_inventory_item_id IN NUMBER,
3849 p_asset_creation_code OUT NOCOPY VARCHAR2,
3850 p_organization_id IN NUMBER --Added for Bug 13988660
3851 )
3852 IS
3853 -- Enter the procedure variables here. As shown below
3854 -- variable_name datatype NOT NULL DEFAULT default_value ;
3855 l_err_text VARCHAR2(2000);
3856 l_api_name VARCHAR2(200) := 'CSI_INV_TRXS_PKG.GET_ASSET_CREATION_CODE';
3857 /*Commented for Bug 13988660
3858 CURSOR Asset_CC_Cur (P_Item_Id IN NUMBER) IS
3859 SELECT DISTINCT asset_creation_code
3860 FROM mtl_system_items
3861 WHERE inventory_item_id = p_inventory_item_id
3862 AND organization_id =
3863 (select organization_id
3864 from mtl_system_items
3865 where inventory_item_id=p_inventory_item_id
3866 and rownum=1)
3867 AND enabled_flag = 'Y'
3868 AND nvl (start_date_active, l_sysdate) <= l_sysdate
3869 AND nvl (end_date_active, l_sysdate+1) > l_sysdate;
3870 */
3871 --Added for Bug 13988660
3872 CURSOR Asset_CC_Cur (P_Item_Id IN NUMBER, P_Org_Id IN NUMBER) IS
3873 SELECT asset_creation_code
3874 FROM mtl_system_items
3875 WHERE inventory_item_id = p_inventory_item_id
3876 AND organization_id = p_organization_id
3877 AND enabled_flag = 'Y'
3878 AND nvl (start_date_active, l_sysdate) <= l_sysdate
3879 AND nvl (end_date_active, l_sysdate+1) > l_sysdate;
3880
3881 BEGIN
3882 P_Asset_Creation_Code := NULL;
3883 OPEN Asset_CC_Cur(P_inventory_item_id, p_organization_id);
3884 FETCH Asset_CC_Cur INTO P_Asset_Creation_Code;
3885 IF NOT Asset_CC_Cur%FOUND THEN
3886 P_Asset_Creation_Code := NULL;
3887 END IF;
3888 CLOSE Asset_CC_Cur;
3889 EXCEPTION
3890 WHEN OTHERS THEN
3891 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
3892 fnd_message.set_token('API_NAME', l_api_name);
3893 fnd_message.set_token('SQL_ERROR', sqlerrm);
3894 l_err_text := fnd_message.get;
3895 raise;
3896 END get_asset_creation_code;
3897
3898 PROCEDURE Check_item_Trackable(
3899 p_inventory_item_id IN NUMBER,
3900 p_nl_trackable_flag OUT NOCOPY VARCHAR2)
3901 IS
3902 -- Enter the procedure variables here. As shown below
3903 -- variable_name datatype NOT NULL DEFAULT default_value ;
3904 yes_or_no VARCHAR2(2) := 'N';
3905 l_err_text VARCHAR2(2000);
3906 l_api_name VARCHAR2(200) := 'CSI_INV_TRXS_PKG.CHECK_ITEM_TRACKABLE';
3907 CURSOR NL_TRACK_CUR(P_Item_Id IN NUMBER) IS
3908 SELECT DISTINCT 'Y'
3909 FROM mtl_system_items
3910 WHERE inventory_item_id = p_item_id
3911 AND organization_id =
3912 (select organization_id
3913 from mtl_system_items
3914 where inventory_item_id=P_inventory_item_id
3915 and rownum =1)
3916 AND enabled_flag = 'Y'
3917 AND nvl (start_date_active, l_sysdate) <= l_sysdate
3918 AND nvl (end_date_active, l_sysdate+1) > l_sysdate
3919 AND comms_nl_trackable_flag = 'Y';
3920 BEGIN
3921 OPEN NL_Track_Cur(P_Inventory_Item_Id);
3922 FETCH NL_Track_Cur INTO Yes_Or_No;
3923 CLOSE NL_Track_Cur;
3924 IF (yes_or_no = 'Y') THEN
3925 p_nl_trackable_flag := 'TRUE';
3926 ELSE
3927 p_nl_trackable_flag := 'FALSE';
3928 END IF;
3929 EXCEPTION
3930 WHEN OTHERS THEN
3931 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
3932 fnd_message.set_token('API_NAME', l_api_name);
3933 fnd_message.set_token('SQL_ERROR', sqlerrm);
3934 l_err_text := fnd_message.get;
3935 END check_item_trackable;
3936
3937 PROCEDURE check_depreciable(
3938 p_inventory_item_id IN NUMBER,
3939 p_depreciable OUT NOCOPY VARCHAR2,
3940 p_organization_id IN NUMBER --Added for Bug 13988660
3941 )
3942 IS
3943 -- Enter the procedure variables here. As shown below
3944 -- variable_name datatype NOT NULL DEFAULT default_value ;
3945 l_asset_creation_code VARCHAR2(1);
3946 l_err_text VARCHAR2(2000);
3947 l_api_name VARCHAR2(200) := 'CSI_INV_TRXS_PKG.CHECK_DEPRECIABLE';
3948
3949 BEGIN
3950 csi_inv_trxs_pkg.Get_Asset_Creation_Code(
3951 p_inventory_item_id,
3952 l_asset_creation_code,
3953 p_organization_id); --Added for Bug 13988660
3954 IF l_asset_creation_code NOT IN ('1','Y') OR
3955 l_asset_creation_code IS NULL
3956 THEN
3957 p_depreciable := 'N';
3958 ELSE
3959 p_depreciable := 'Y';
3960 END IF;
3961 EXCEPTION
3962 WHEN OTHERS THEN
3963 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
3964 fnd_message.set_token('API_NAME', l_api_name);
3965 fnd_message.set_token('SQL_ERROR', sqlerrm);
3966 l_err_text := fnd_message.get;
3967 raise;
3968 END check_depreciable;
3969
3970 FUNCTION is_csi_installed RETURN VARCHAR2
3971 IS
3972 l_csi_installed VARCHAR2(1) := 'N' ;
3973 dummy VARCHAR2(40);
3974 ret BOOLEAN;
3975 BEGIN
3976 IF (csi_inv_trxs_pkg.x_csi_install is NULL)
3977 THEN
3978 ret := fnd_installation.get_app_info('CSI',
3979 csi_inv_trxs_pkg.x_csi_install, dummy, dummy);
3980 END IF;
3981
3982 IF (csi_inv_trxs_pkg.x_csi_install = 'I')
3983 THEN
3984 l_csi_installed := 'Y';
3985 ELSE
3986 l_csi_installed := 'N';
3987 END IF;
3988 RETURN l_csi_installed ;
3989 END is_csi_installed ;
3990
3991 FUNCTION get_neg_inv_code (p_org_id in NUMBER) RETURN NUMBER IS
3992
3993 l_neg_code NUMBER := 0;
3994
3995 cursor c_code (pc_org_id in NUMBER) is
3996 SELECT negative_inv_receipt_Code
3997 FROM mtl_parameters
3998 WHERE organization_id = pc_org_id;
3999
4000 r_code c_code%rowtype;
4001
4002 BEGIN
4003 OPEN c_code (p_org_id);
4004 FETCH c_code into r_code;
4005 IF c_code%found THEN
4006 l_neg_code := r_code.negative_inv_receipt_code;
4007 END IF;
4008 CLOSE c_code;
4009 RETURN l_neg_code ;
4010 END get_neg_inv_code;
4011
4012 PROCEDURE get_master_organization(p_organization_id IN NUMBER,
4013 p_master_organization_id OUT NOCOPY NUMBER,
4014 x_return_status OUT NOCOPY VARCHAR2,
4015 x_error_message OUT NOCOPY VARCHAR2)
4016 IS
4017
4018 l_sql_error VARCHAR2(500);
4019 l_org_code VARCHAR2(3);
4020 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
4021 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
4022 l_fnd_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
4023 l_error_message VARCHAR2(2000);
4024 e_procedure_error EXCEPTION;
4025
4026 CURSOR c_name is
4027 SELECT organization_code
4028 FROM mtl_parameters
4029 WHERE organization_id = p_organization_id;
4030
4031 r_name c_name%rowtype;
4032
4033 CURSOR c_id IS
4034 SELECT master_organization_id
4035 FROM mtl_parameters
4036 WHERE organization_id = p_organization_id;
4037
4038 r_id c_id%rowtype;
4039
4040 BEGIN
4041
4042 l_error_message := NULL;
4043 x_return_status := l_fnd_success;
4044
4045 OPEN c_id;
4046 FETCH c_id into r_id;
4047 IF c_id%found then
4048 p_master_organization_id := r_id.master_organization_id;
4049 ELSE
4050 OPEN c_name;
4051 FETCH c_name into r_name;
4052 if c_name%found then
4053 l_org_code := r_name.organization_code;
4054 end if;
4055 RAISE e_procedure_error;
4056 END IF;
4057
4058 EXCEPTION
4059 WHEN e_procedure_error THEN
4060 fnd_message.set_name('CSI','CSI_MSTR_ORG_NOTFOUND');
4061 fnd_message.set_token('ORGANIZATION_ID',p_organization_id);
4062 fnd_message.set_token('ORGANIZATION_CODE',l_org_code);
4063 x_error_message := fnd_message.get;
4064 x_return_status := l_fnd_error;
4065
4066 WHEN others THEN
4067 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
4068 fnd_message.set_token('SQL_ERROR',SQLERRM);
4069 x_error_message := fnd_message.get;
4070 x_return_status := l_fnd_unexpected;
4071 END get_master_organization;
4072
4073 PROCEDURE build_error_string (
4074 p_string IN OUT NOCOPY VARCHAR2,
4075 p_attribute IN VARCHAR2,
4076 p_value IN VARCHAR2) IS
4077
4078 BEGIN
4079 p_string := p_string || '<' || p_attribute || '>' ;
4080 p_string := p_string || p_value ;
4081 p_string := p_string || '</' || p_attribute || '>' ;
4082
4083 END build_error_string;
4084
4085 PROCEDURE get_string_value (
4086 p_string IN VARCHAR2,
4087 p_attribute IN VARCHAR2,
4088 x_value OUT NOCOPY VARCHAR2) IS
4089
4090 tag_pos INTEGER := 0 ;
4091 token VARCHAR2(1024) := '' ;
4092 token_delimeter VARCHAR2(1024) := '' ;
4093 tag_delimeter_pos INTEGER := 0 ;
4094
4095 BEGIN
4096
4097 token := '<' || p_attribute || '>' ;
4098 token_delimeter := '</' || p_attribute || '>' ;
4099 tag_pos := INSTR( p_string, token, 1 ) ;
4100
4101 IF (tag_pos = 0)
4102 THEN
4103 x_value := NULL ;
4104 RETURN ;
4105 END IF ;
4106
4107 tag_delimeter_pos := INSTR( p_string, token_delimeter, 1 ) ;
4108
4109 IF (tag_delimeter_pos = 0)
4110 THEN
4111 x_value := NULL ;
4112 RETURN ;
4113 END IF ;
4114
4115 x_value := SUBSTR(p_string, tag_pos + LENGTH(token),
4116 tag_delimeter_pos - (tag_pos + LENGTH(token))) ;
4117
4118 END get_string_value;
4119
4120 FUNCTION Init_Instance_Query_Rec RETURN CSI_DATASTRUCTURES_PUB.Instance_Query_Rec IS
4121 l_Instance_Query_Rec CSI_DataStructures_Pub.Instance_Query_Rec;
4122 BEGIN
4123 RETURN l_Instance_Query_Rec;
4124 END Init_Instance_Query_Rec;
4125
4126 FUNCTION Init_Instance_Create_Rec RETURN CSI_DATASTRUCTURES_PUB.Instance_Rec IS
4127 l_Instance_Rec CSI_DATASTRUCTURES_PUB.Instance_Rec;
4128 BEGIN
4129 l_instance_rec.version_label := 'AS-CREATED';
4130 l_instance_rec.creation_complete_flag := NULL;
4131 RETURN l_Instance_Rec;
4132 END Init_Instance_Create_Rec;
4133
4134 FUNCTION Init_Instance_Update_Rec RETURN CSI_DATASTRUCTURES_PUB.Instance_Rec IS
4135 l_Instance_Rec CSI_DATASTRUCTURES_PUB.Instance_Rec;
4136 BEGIN
4137 RETURN l_Instance_Rec;
4138 END Init_Instance_Update_Rec;
4139
4140 FUNCTION Init_Party_Tbl RETURN CSI_DATASTRUCTURES_PUB.Party_Tbl IS
4141 l_Party_Tbl CSI_DATASTRUCTURES_PUB.Party_Tbl;
4142 l_source_table VARCHAR2(30);
4143 l_Party_Id NUMBER;
4144 l_relation_code VARCHAR2(30);
4145
4146 CURSOR Source_Table_Cur IS
4147 SELECT lookup_code
4148 FROM CSI_Lookups
4149 WHERE lookup_Type = 'CSI_PARTY_SOURCE_TABLE'
4150 AND lookup_code = 'HZ_PARTIES';
4151
4152 CURSOR Relationship_Cur IS
4153 SELECT IPA_Relation_Type_Code
4154 FROM CSI_IPA_Relation_Types
4155 WHERE Upper(IPA_Relation_Type_Code) = 'OWNER';
4156
4157 BEGIN
4158 OPEN Source_Table_Cur;
4159 FETCH Source_Table_Cur INTO l_source_table;
4160 CLOSE Source_Table_Cur;
4161
4162 l_Party_ID := csi_datastructures_pub.g_install_param_rec.internal_party_id;
4163
4164 OPEN Relationship_Cur;
4165 FETCH Relationship_Cur INTO l_relation_code;
4166 CLOSE Relationship_Cur;
4167
4168 l_Party_Tbl(1).party_source_table := l_Source_Table;
4169 l_Party_Tbl(1).party_id := l_Party_Id;
4170 l_Party_Tbl(1).relationship_type_code := l_relation_Code;
4171 l_Party_Tbl(1).contact_flag := 'N';
4172 RETURN l_Party_Tbl;
4173 END Init_Party_Tbl;
4174
4175 FUNCTION Init_Account_Tbl RETURN CSI_DATASTRUCTURES_PUB.Party_Account_Tbl IS
4176 l_Account_Tbl CSI_DATASTRUCTURES_PUB.Party_Account_Tbl;
4177 BEGIN
4178 RETURN l_Account_Tbl;
4179 END Init_Account_Tbl;
4180
4181 FUNCTION Init_ext_attrib_values_tbl RETURN CSI_DATASTRUCTURES_PUB.extend_attrib_values_tbl IS
4182 l_extend_attrib_values_tbl CSI_DATASTRUCTURES_PUB.extend_attrib_values_tbl;
4183 BEGIN
4184 RETURN l_extend_attrib_values_tbl;
4185 END Init_ext_attrib_values_tbl;
4186
4187 FUNCTION Init_Pricing_Attribs_Tbl RETURN CSI_DATASTRUCTURES_PUB.pricing_attribs_tbl IS
4188 l_Pricing_Attribs_Tbl CSI_DATASTRUCTURES_PUB.pricing_attribs_tbl;
4189 BEGIN
4190 RETURN l_Pricing_Attribs_Tbl;
4191 END Init_Pricing_Attribs_Tbl;
4192
4193 FUNCTION Init_Org_Assignments_Tbl RETURN CSI_DATASTRUCTURES_PUB.organization_units_tbl IS
4194 l_Org_Assignments_Tbl CSI_DATASTRUCTURES_PUB.organization_units_tbl;
4195 BEGIN
4196 RETURN l_Org_Assignments_Tbl;
4197 END Init_Org_Assignments_Tbl;
4198
4199 FUNCTION Init_Asset_Assignment_Tbl RETURN CSI_DATASTRUCTURES_PUB.instance_asset_tbl IS
4200 l_Asset_Assignment_Tbl CSI_DATASTRUCTURES_PUB.instance_asset_tbl;
4201 BEGIN
4202 RETURN l_Asset_Assignment_Tbl;
4203 END Init_Asset_Assignment_Tbl;
4204
4205 FUNCTION Init_Instance_Asset_Query_Rec RETURN CSI_DATASTRUCTURES_PUB.instance_asset_Query_Rec IS
4206 l_instance_asset_Query_Rec CSI_DATASTRUCTURES_PUB.instance_asset_Query_Rec;
4207 BEGIN
4208 RETURN l_instance_asset_Query_Rec;
4209 END Init_Instance_Asset_Query_Rec;
4210
4211 FUNCTION Init_Instance_Asset_Rec RETURN CSI_DATASTRUCTURES_PUB.instance_asset_Rec IS
4212 l_instance_asset_Rec CSI_DATASTRUCTURES_PUB.instance_asset_Rec;
4213 BEGIN
4214 RETURN l_instance_asset_Rec;
4215 END Init_Instance_Asset_Rec;
4216
4217 FUNCTION Get_Txn_Type_Id(P_Txn_Type IN VARCHAR2,
4218 P_App_Short_Name IN VARCHAR2) RETURN NUMBER IS
4219 l_Txn_Type_Id NUMBER;
4220 CURSOR Txn_Type_Cur IS
4221 SELECT ctt.Transaction_Type_Id Transaction_Type_Id
4222 FROM CSI_Txn_Types ctt,
4223 FND_Application fa
4224 WHERE ctt.Source_Transaction_Type = P_Txn_Type
4225 AND fa.application_id = ctt.Source_Application_ID
4226 AND fa.Application_Short_Name = P_App_Short_Name;
4227 BEGIN
4228 OPEN Txn_Type_Cur;
4229 FETCH Txn_Type_Cur INTO l_Txn_Type_Id;
4230 CLOSE Txn_Type_Cur;
4231 RETURN l_Txn_Type_Id;
4232 END Get_Txn_Type_Id;
4233
4234 FUNCTION Get_Txn_Type_Code(P_Txn_Id IN NUMBER) RETURN VARCHAR2 IS
4235 l_Txn_Type_Code VARCHAR2(100);
4236 CURSOR Txn_Type_Id_Cur IS
4237 SELECT Source_Transaction_Type
4238 FROM CSI_Txn_Types
4239 WHERE Transaction_Type_Id = P_Txn_Id;
4240 BEGIN
4241 OPEN Txn_Type_Id_Cur;
4242 FETCH Txn_Type_Id_Cur INTO l_Txn_Type_Code;
4243 CLOSE Txn_Type_Id_Cur;
4244 RETURN l_Txn_Type_Code;
4245 END Get_Txn_Type_Code;
4246
4247 FUNCTION Get_Txn_Status_Code(P_Txn_Status IN VARCHAR2) RETURN VARCHAR2 IS
4248 l_Txn_Status_Code VARCHAR2(30) DEFAULT FND_API.G_MISS_CHAR;
4249 BEGIN
4250 RETURN l_Txn_Status_Code;
4251 END Get_Txn_Status_Code;
4252
4253 FUNCTION Get_Location_Type_Code(P_Location_Meaning in VARCHAR2) RETURN VARCHAR2 IS
4254
4255 l_location_type_code VARCHAR2(50);
4256
4257 CURSOR c_code IS
4258 SELECT lookup_code
4259 FROM csi_lookups
4260 WHERE lookup_type = 'CSI_INST_LOCATION_SOURCE_CODE'
4261 AND lookup_code = upper(P_Location_Meaning);
4262
4263 r_code c_code%rowtype;
4264
4265 BEGIN
4266 OPEN c_code;
4267 FETCH c_code into r_code;
4268 IF c_code%found THEN
4269 l_location_type_code := r_code.lookup_code;
4270 ELSE
4271 l_location_type_code := NULL;
4272 END IF;
4273 CLOSE c_code;
4274 RETURN l_location_type_code;
4275 END Get_Location_Type_Code;
4276
4277 FUNCTION Get_Dflt_Project_Location_Id RETURN NUMBER IS
4278
4279 l_project_location_id NUMBER := NULL;
4280
4281 BEGIN
4282
4283 l_project_location_id := csi_datastructures_pub.g_install_param_rec.project_location_id;
4284
4285 RETURN l_project_location_id;
4286 END Get_Dflt_Project_Location_Id;
4287
4288 FUNCTION Get_Default_Status_Id (p_transaction_id in number) RETURN NUMBER IS
4289
4290 l_transaction_id NUMBER;
4291
4292 CURSOR c_id IS
4293 SELECT src_status_id
4294 FROM csi_txn_sub_types
4295 WHERE transaction_type_id = p_transaction_id
4296 AND default_flag = 'Y';
4297
4298 r_id c_id%rowtype;
4299
4300 BEGIN
4301 OPEN c_id;
4302 FETCH c_id into r_id;
4303 IF c_id%found THEN
4304 l_transaction_id := r_id.src_status_id;
4305 ELSE
4306 l_transaction_id := NULL;
4307 END IF;
4308 CLOSE c_id;
4309 RETURN l_transaction_id;
4310 END Get_Default_Status_id;
4311
4312 FUNCTION Get_Txn_Action_Code(P_Txn_Action IN VARCHAR2) RETURN VARCHAR2 IS
4313 l_Txn_Action_Code VARCHAR2(30) DEFAULT FND_API.G_MISS_CHAR;
4314
4315 BEGIN
4316 RETURN l_Txn_Action_Code;
4317 END Get_Txn_Action_Code;
4318
4319 FUNCTION Get_Fnd_Employee_Id(P_Last_Updated IN NUMBER) RETURN NUMBER IS
4320
4321 l_employee_id NUMBER;
4322
4323 CURSOR c_id IS
4324 SELECT employee_id
4325 FROM fnd_user
4326 WHERE user_id = p_last_updated;
4327
4328 r_id c_id%rowtype;
4329
4330 BEGIN
4331 OPEN c_id;
4332 FETCH c_id into r_id;
4333 IF c_id%found THEN
4334 l_employee_id := r_id.employee_id;
4335 ELSE
4336 l_employee_id := -1;
4337 END IF;
4338 CLOSE c_id;
4339 RETURN l_employee_id;
4340 END Get_Fnd_Employee_Id;
4341
4342 FUNCTION Init_Txn_Rec RETURN CSI_DATASTRUCTURES_PUB.TRANSACTION_Rec IS
4343 l_Txn_Rec CSI_DATASTRUCTURES_PUB.TRANSACTION_Rec;
4344 BEGIN
4345 RETURN l_Txn_Rec;
4346 END Init_Txn_Rec;
4347
4348 FUNCTION Init_Txn_Error_Rec RETURN CSI_DATASTRUCTURES_PUB.TRANSACTION_Error_Rec IS
4349 l_Txn_Error_Rec CSI_DATASTRUCTURES_PUB.TRANSACTION_Error_Rec;
4350 BEGIN
4351 l_Txn_Error_Rec.processed_flag := CSI_INV_TRXS_PKG.G_TXN_ERROR;
4352 RETURN l_Txn_Error_Rec;
4353 END Init_Txn_Error_Rec;
4354
4355 FUNCTION Init_Party_Query_Rec RETURN CSI_DATASTRUCTURES_PUB.Party_Query_Rec IS
4356 l_Party_Query_Rec CSI_DATASTRUCTURES_PUB.Party_Query_Rec;
4357 l_Party_Id NUMBER;
4358 l_relation_code VARCHAR2(30);
4359
4360 CURSOR Relationship_Cur IS
4361 SELECT IPA_Relation_Type_Code
4362 FROM CSI_IPA_Relation_Types
4363 WHERE Upper(IPA_Relation_Type_Code) = 'OWNER';
4364
4365 BEGIN
4366
4367
4368 l_Party_ID := csi_datastructures_pub.g_install_param_rec.internal_party_id;
4369
4370 OPEN Relationship_Cur;
4371 FETCH Relationship_Cur INTO l_relation_code;
4372 CLOSE Relationship_Cur;
4373
4374 l_Party_Query_Rec.party_id := l_Party_Id;
4375 l_Party_Query_Rec.relationship_type_code := l_relation_Code;
4376
4377 RETURN l_Party_Query_Rec;
4378 END Init_Party_Query_Rec;
4379
4380 FUNCTION get_inv_name (p_transaction_id IN NUMBER) RETURN VARCHAR2 IS
4381
4382 l_transaction_type_id NUMBER;
4383 l_inv_name VARCHAR2(30);
4384
4385 CURSOR x is
4386 SELECT transaction_type_id
4387 FROM mtl_material_transactions
4388 WHERE transaction_id = p_transaction_id;
4389
4390 BEGIN
4391
4392 OPEN x;
4393 FETCH x into l_transaction_type_id;
4394 CLOSE x;
4395
4396 IF l_transaction_type_id = 1 THEN -- Account issue
4397 l_inv_name := 'ACCT_ISSUE';
4398 ELSIF l_transaction_type_id = 2 THEN -- Subinventory Transfer
4399 l_inv_name := 'SUBINVENTORY_TRANSFER';
4400 ELSIF l_transaction_type_id = 3 THEN -- Direct Org Transfer
4401 l_inv_name := 'INTERORG_DIRECT_SHIP';
4402 ELSIF l_transaction_type_id = 4 THEN -- Cycle Count Adjust
4403 l_inv_name := 'CYCLE_COUNT';
4404 ELSIF l_transaction_type_id = 5 THEN -- Cycle Count Transfer
4405 l_inv_name := 'CYCLE_COUNT_TRANSFER';
4406 ELSIF l_transaction_type_id = 8 THEN -- Physical Inv Adjust
4407 l_inv_name := 'PHYSICAL_INVENTORY';
4408 ELSIF l_transaction_type_id = 9 THEN -- Physical Inv Transfer
4409 l_inv_name := 'PHYSICAL_INV_TRANSFER';
4410 ELSIF l_transaction_type_id = 12 THEN -- Intransit Receipt
4411 l_inv_name := 'INTERORG_TRANS_RECEIPT';
4412 ELSIF l_transaction_type_id = 15 THEN -- RMA Receipt
4413 l_inv_name := 'RMA_RECEIPT';
4414 ELSIF l_transaction_type_id = 17 THEN -- WIP Assembly Return
4415 l_inv_name := 'WIP_ISSUE';
4416 ELSIF l_transaction_type_id = 18 THEN -- PO Receipt
4417 l_inv_name := 'PO_RECEIPT_INTO_INVENTORY';
4418 ELSIF l_transaction_type_id = 21 THEN -- Intransit Shipment
4419 l_inv_name := 'INTERORG_TRANS_SHIPMENT';
4420 --ELSIF l_transaction_type_id = 25 THEN -- WIP cost update
4421 --ELSIF l_transaction_type_id = 26 THEN -- Periodic Cost Update
4422 --ELSIF l_transaction_type_id = 28 THEN -- Layer Cost Update
4423 ELSIF l_transaction_type_id = 31 THEN -- Account alias issue
4424 l_inv_name := 'ACCT_ALIAS_ISSUE';
4425 ELSIF l_transaction_type_id = 32 THEN -- Miscellaneous issue
4426 l_inv_name := 'MISC_ISSUE';
4427 ELSIF l_transaction_type_id = 33 THEN -- Sales order issue
4428 l_inv_name := 'OM_SHIPMENT';
4429 ELSIF l_transaction_type_id = 34 THEN -- Internal order issue
4430 l_inv_name := 'ISO_ISSUE';
4431 ELSIF l_transaction_type_id = 35 THEN -- WIP component issue
4432 l_inv_name := 'WIP_ISSUE';
4433 ELSIF l_transaction_type_id = 36 THEN -- Return to Vendor
4434 l_inv_name := 'RETURN_TO_VENDOR';
4435 ELSIF l_transaction_type_id = 1005 THEN -- Return to Vendor
4436 l_inv_name := 'RETURN_TO_VENDOR_WO_PO';
4437 --ELSIF l_transaction_type_id = 37 THEN -- RMA Return
4438 ELSIF l_transaction_type_id = 38 THEN -- WIP Neg Comp Issue
4439 l_inv_name := 'WIP_RECEIPT';
4440 ELSIF l_transaction_type_id = 40 THEN -- Account receipt
4441 l_inv_name := 'ACCT_RECEIPT';
4442 ELSIF l_transaction_type_id = 41 THEN -- Account alias receipt
4443 l_inv_name := 'ACCT_ALIAS_RECEIPT';
4444 ELSIF l_transaction_type_id = 42 THEN -- Miscellaneous receipt
4445 l_inv_name := 'MISC_RECEIPT';
4446 ELSIF l_transaction_type_id = 43 THEN -- WIP Component Return
4447 l_inv_name := 'WIP_RECEIPT';
4448 ELSIF l_transaction_type_id = 44 THEN -- WIP Assy Completion
4449 l_inv_name := 'WIP_ASSEMBLY_COMPLETION';
4450 ELSIF l_transaction_type_id = 48 THEN -- WIP Neg Comp Return
4451 l_inv_name := 'WIP_ISSUE';
4452 ELSIF l_transaction_type_id = 50 THEN -- Internal Order Xfer
4453 l_inv_name := 'ISO_TRANSFER';
4454 ELSIF l_transaction_type_id = 51 THEN -- Backflush Transfer
4455 l_inv_name := 'BACKFLUSH_TRANSFER';
4456 ELSIF l_transaction_type_id = 52 THEN -- Sales Order Pick
4457 l_inv_name := 'SALES_ORDER_PICK';
4458 ELSIF l_transaction_type_id = 53 THEN -- Internal Order Pick
4459 l_inv_name := 'ISO_PICK';
4460 ELSIF l_transaction_type_id = 54 THEN -- Int Order Direct Ship
4461 l_inv_name := 'ISO_DIRECT_SHIP';
4462 --ELSIF l_transaction_type_id = 55 THEN -- WIP Lot Split
4463 --ELSIF l_transaction_type_id = 56 THEN -- WIP Lot Merge
4464 --ELSIF l_transaction_type_id = 57 THEN -- Lot Bonus
4465 --ELSIF l_transaction_type_id = 58 THEN -- Lot Update Quantity
4466 ELSIF l_transaction_type_id = 61 THEN -- Int Req Intr Rcpt
4467 l_inv_name := 'ISO_REQUISITION_RECEIPT';
4468 ELSIF l_transaction_type_id = 62 THEN -- Int Order Intr Ship
4469 l_inv_name := 'ISO_SHIPMENT';
4470 ELSIF l_transaction_type_id = 63 THEN -- Move Order Issue
4471 l_inv_name := 'MOVE_ORDER_ISSUE';
4472 ELSIF l_transaction_type_id = 64 THEN -- Move Order Transfer
4473 l_inv_name := 'MOVE_ORDER_TRANSFER';
4474 ELSIF l_transaction_type_id = 66 THEN -- Project Borrow
4475 l_inv_name := 'PROJECT_BORROW';
4476 ELSIF l_transaction_type_id = 67 THEN -- Project Transfer
4477 l_inv_name := 'PROJECT_TRANSFER';
4478 ELSIF l_transaction_type_id = 68 THEN -- Project Payback
4479 l_inv_name := 'PROJECT_PAYBACK';
4480 ELSIF l_transaction_type_id = 70 THEN -- Shipment Rcpt Adjust
4481 l_inv_name := 'SHIPMENT_RCPT_ADJUSTMENT';
4482 ELSIF l_transaction_type_id = 71 THEN -- PO Rcpt Adjust
4483 l_inv_name := 'PO_RCPT_ADJUSTMENT';
4484 ELSIF l_transaction_type_id = 72 THEN -- Int Req Rcpt Adjust
4485 l_inv_name := 'INT_REQ_RCPT_ADJUSTMENT';
4486 --ELSIF l_transaction_type_id = 73 THEN -- Planning Transfer
4487 ELSIF l_transaction_type_id = 77 THEN -- ProjectContract Issue
4488 l_inv_name := 'PROJECT_CONTRACT_SHIPMENT';
4489 --ELSIF l_transaction_type_id = 80 THEN -- Average cost update
4490 --ELSIF l_transaction_type_id = 82 THEN -- Inventory Lot Split
4491 --ELSIF l_transaction_type_id = 83 THEN -- Inventory Lot Merge
4492 --ELSIF l_transaction_type_id = 84 THEN -- Inventory Lot Translate
4493 --ELSIF l_transaction_type_id = 86 THEN -- Cost Group Transfer
4494 --ELSIF l_transaction_type_id = 87 THEN -- Container Pack
4495 --ELSIF l_transaction_type_id = 88 THEN -- Container Unpack
4496 --ELSIF l_transaction_type_id = 89 THEN -- Container Split
4497 --ELSIF l_transaction_type_id = 90 THEN -- WIP assembly scrap
4498 --ELSIF l_transaction_type_id = 91 THEN -- WIP return from scrap
4499 --ELSIF l_transaction_type_id = 92 THEN -- WIP estimated scrap
4500 ELSE
4501 l_inv_name := NULL;
4502 END IF;
4503
4504 RETURN l_inv_name;
4505 END get_inv_name;
4506
4507 PROCEDURE log_csi_error(p_trx_error_rec IN CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC) IS
4508
4509 l_api_version NUMBER := 1.0;
4510 l_commit VARCHAR2(1) := FND_API.G_FALSE;
4511 l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
4512 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
4513 l_msg_count NUMBER;
4514 l_msg_data VARCHAR2(2000);
4515 l_txn_error_id NUMBER;
4516 l_return_status VARCHAR2(1);
4517 l_trx_error_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_ERROR_REC;
4518 no_error_logged EXCEPTION;
4519 x_transaction_error_id NUMBER;
4520
4521 BEGIN
4522
4523 l_trx_error_rec := p_trx_error_rec;
4524
4525 l_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
4526 l_trx_error_rec.processed_flag := csi_inv_trxs_pkg.g_txn_error;
4527
4528 csi_transactions_pvt.create_txn_error
4529 (l_api_version, l_init_msg_list, l_commit, l_validation_level,
4530 l_trx_error_rec, l_return_status, l_msg_count,l_msg_data,
4531 l_txn_error_id);
4532
4533 IF NOT l_return_status = FND_API.G_RET_STS_SUCCESS THEN
4534 raise no_error_logged;
4535 END IF;
4536
4537 EXCEPTION
4538 WHEN no_error_logged THEN
4539 BEGIN
4540 csi_txn_errors_pkg.insert_row(
4541 px_transaction_error_id => x_transaction_error_id,
4542 p_transaction_id => fnd_api.g_miss_num,
4543 p_message_id => l_trx_error_rec.message_id,
4544 p_error_text => l_trx_error_rec.error_text,
4545 p_source_type => l_trx_error_rec.source_type,
4546 p_source_id => l_trx_error_rec.source_id,
4547 p_processed_flag => l_trx_error_rec.processed_flag,
4548 p_created_by => fnd_global.user_id,
4549 p_creation_date => SYSDATE,
4550 p_last_updated_by => fnd_global.user_id,
4551 p_last_update_date => SYSDATE,
4552 p_last_update_login => fnd_global.conc_login_id,
4553 p_object_version_number => 1,
4554 p_transaction_type_id => l_trx_error_rec.transaction_type_id ,
4555 p_source_group_ref => l_trx_error_rec.source_group_ref,
4556 p_source_group_ref_id => l_trx_error_rec.source_group_ref_id ,
4557 p_source_header_ref => l_trx_error_rec.source_header_ref ,
4558 p_source_header_ref_id => l_trx_error_rec.source_header_ref_id ,
4559 p_source_line_ref => l_trx_error_rec.source_line_ref ,
4560 p_source_line_ref_id => l_trx_error_rec.source_line_ref_id ,
4561 p_source_dist_ref_id1 => l_trx_error_rec.source_dist_ref_id1 ,
4562 p_source_dist_ref_id2 => l_trx_error_rec.source_dist_ref_id2 ,
4563 p_inv_material_transaction_id => l_trx_error_rec.inv_material_transaction_id,
4564 p_error_stage => l_trx_error_rec.error_stage,
4565 p_message_string => l_trx_error_rec.message_string,
4566 p_instance_id => l_trx_error_rec.instance_id,
4567 p_inventory_item_id => l_trx_error_rec.inventory_item_id,
4568 p_serial_number => l_trx_error_rec.serial_number,
4569 p_lot_number => l_trx_error_rec.lot_number,
4570 p_transaction_error_date => l_trx_error_rec.transaction_error_date,
4571 p_src_serial_num_ctrl_code => l_trx_error_rec.src_serial_num_ctrl_code,
4572 p_src_location_ctrl_code => l_trx_error_rec.src_location_ctrl_code,
4573 p_src_lot_ctrl_code => l_trx_error_rec.src_lot_ctrl_code,
4574 p_src_rev_qty_ctrl_code => l_trx_error_rec.src_rev_qty_ctrl_code,
4575 p_dst_serial_num_ctrl_code => l_trx_error_rec.dst_serial_num_ctrl_code,
4576 p_dst_location_ctrl_code => l_trx_error_rec.dst_location_ctrl_code,
4577 p_dst_lot_ctrl_code => l_trx_error_rec.dst_lot_ctrl_code,
4578 p_dst_rev_qty_ctrl_code => l_trx_error_rec.dst_rev_qty_ctrl_code,
4579 p_comms_nl_trackable_flag => l_trx_error_rec.comms_nl_trackable_flag
4580 );
4581 EXCEPTION
4582 WHEN OTHERS THEN
4583 raise;
4584 END;
4585 WHEN OTHERS THEN
4586 BEGIN
4587 csi_txn_errors_pkg.insert_row(
4588 px_transaction_error_id => x_transaction_error_id,
4589 p_transaction_id => fnd_api.g_miss_num,
4590 p_message_id => l_trx_error_rec.message_id,
4591 p_error_text => SQLERRM,
4592 p_source_type => l_trx_error_rec.source_type,
4593 p_source_id => l_trx_error_rec.source_id,
4594 p_processed_flag => l_trx_error_rec.processed_flag,
4595 p_created_by => fnd_global.user_id,
4596 p_creation_date => SYSDATE,
4597 p_last_updated_by => fnd_global.user_id,
4598 p_last_update_date => SYSDATE,
4599 p_last_update_login => fnd_global.conc_login_id,
4600 p_object_version_number => 1,
4601 p_transaction_type_id => l_trx_error_rec.transaction_type_id ,
4602 p_source_group_ref => l_trx_error_rec.source_group_ref,
4603 p_source_group_ref_id => l_trx_error_rec.source_group_ref_id ,
4604 p_source_header_ref => l_trx_error_rec.source_header_ref ,
4605 p_source_header_ref_id => l_trx_error_rec.source_header_ref_id ,
4606 p_source_line_ref => l_trx_error_rec.source_line_ref ,
4607 p_source_line_ref_id => l_trx_error_rec.source_line_ref_id ,
4608 p_source_dist_ref_id1 => l_trx_error_rec.source_dist_ref_id1 ,
4609 p_source_dist_ref_id2 => l_trx_error_rec.source_dist_ref_id2 ,
4610 p_inv_material_transaction_id => l_trx_error_rec.inv_material_transaction_id,
4611 p_error_stage => l_trx_error_rec.error_stage,
4612 p_message_string => l_trx_error_rec.message_string,
4613 p_instance_id => l_trx_error_rec.instance_id,
4614 p_inventory_item_id => l_trx_error_rec.inventory_item_id,
4615 p_serial_number => l_trx_error_rec.serial_number,
4616 p_lot_number => l_trx_error_rec.lot_number,
4617 p_transaction_error_date => l_trx_error_rec.transaction_error_date,
4618 p_src_serial_num_ctrl_code => l_trx_error_rec.src_serial_num_ctrl_code,
4619 p_src_location_ctrl_code => l_trx_error_rec.src_location_ctrl_code,
4620 p_src_lot_ctrl_code => l_trx_error_rec.src_lot_ctrl_code,
4621 p_src_rev_qty_ctrl_code => l_trx_error_rec.src_rev_qty_ctrl_code,
4622 p_dst_serial_num_ctrl_code => l_trx_error_rec.dst_serial_num_ctrl_code,
4623 p_dst_location_ctrl_code => l_trx_error_rec.dst_location_ctrl_code,
4624 p_dst_lot_ctrl_code => l_trx_error_rec.dst_lot_ctrl_code,
4625 p_dst_rev_qty_ctrl_code => l_trx_error_rec.dst_rev_qty_ctrl_code,
4626 p_comms_nl_trackable_flag => l_trx_error_rec.comms_nl_trackable_flag
4627 );
4628 EXCEPTION
4629 WHEN OTHERS THEN
4630 raise;
4631 END;
4632 END log_csi_error;
4633
4634 PROCEDURE create_csi_txn(px_txn_rec IN OUT NOCOPY
4635 CSI_DATASTRUCTURES_PUB.TRANSACTION_REC,
4636 x_error_message OUT NOCOPY VARCHAR2,
4637 x_return_status OUT NOCOPY VARCHAR2) IS
4638
4639
4640 l_api_version NUMBER := 1.0;
4641 l_msg_count NUMBER;
4642 l_msg_index NUMBER;
4643 l_msg_data VARCHAR2(2000);
4644 l_error_message VARCHAR2(4000);
4645 l_return_status VARCHAR2(1);
4646 l_fnd_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
4647 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
4648
4649 BEGIN
4650 px_txn_rec.transaction_date := sysdate;
4651 px_txn_rec.object_version_number := 1;
4652
4653 csi_transactions_pvt.create_transaction(l_api_version,
4654 fnd_api.g_false,
4655 fnd_api.g_false,
4656 fnd_api.g_valid_level_full,
4657 'N',
4658 px_txn_rec,
4659 l_return_status,
4660 l_msg_count,
4661 l_msg_data
4662 );
4663
4664 IF NOT l_return_status = l_fnd_success then
4665 debug('You encountered an error in the csi_transactions_pvt.create_transaction API '||l_msg_data);
4666 l_msg_index := 1;
4667 WHILE l_msg_count > 0 loop
4668 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
4669 l_msg_index := l_msg_index + 1;
4670 l_msg_count := l_msg_count - 1;
4671 END LOOP;
4672 RAISE fnd_api.g_exc_error;
4673 END IF;
4674
4675 EXCEPTION
4676 WHEN fnd_api.g_exc_error THEN
4677 debug('You have encountered a "fnd_api.g_exc_error" exception');
4678 x_return_status := l_fnd_error;
4679 x_error_message := l_error_message;
4680
4681 END create_csi_txn;
4682
4683 PROCEDURE get_redeploy_flag(
4684 p_inventory_item_id IN NUMBER
4685 ,p_serial_number IN VARCHAR2
4686 ,p_transaction_date IN DATE
4687 ,x_redeploy_flag OUT NOCOPY VARCHAR2
4688 ,x_return_status OUT NOCOPY VARCHAR2
4689 ,x_error_message OUT NOCOPY VARCHAR2)
4690 IS
4691 l_out_of_sev NUMBER;
4692 l_proj_insev NUMBER;
4693 l_issue_hz NUMBER;
4694 l_misc_issue_hz NUMBER;
4695
4696 -- Reordered cursor query for bug --bug 9205166
4697
4698 /*CURSOR get_redeploy_flag_cur
4699 IS
4700 SELECT 'Y' redeploy_flag
4701 FROM csi_transactions ct
4702 ,csi_item_instances_h ciih
4703 ,csi_item_instances cii
4704 WHERE ct.transaction_id = ciih.transaction_id
4705 AND ciih.instance_id = cii.instance_id
4706 AND cii.inventory_item_id = p_inventory_item_id
4707 AND cii.serial_number = p_serial_number
4708 AND ct.transaction_date < NVL(p_transaction_date, SYSDATE)
4709 AND ct.transaction_type_id IN (l_out_of_sev, l_proj_insev,
4710 l_issue_hz, l_misc_issue_hz) ;*/
4711
4712 CURSOR get_redeploy_flag_cur
4713 IS
4714 SELECT /*+ ordered */
4715 'Y' redeploy_flag
4716 FROM csi_item_instances cii
4717 ,csi_item_instances_h ciih
4718 ,csi_transactions ct
4719 WHERE ct.transaction_id = ciih.transaction_id
4720 AND ciih.instance_id = cii.instance_id
4721 AND cii.inventory_item_id = p_inventory_item_id
4722 AND cii.serial_number = p_serial_number
4723 AND ct.transaction_date < NVL(p_transaction_date, SYSDATE)
4724 AND ct.transaction_type_id IN (l_out_of_sev, l_proj_insev,
4725 l_issue_hz, l_misc_issue_hz) ;
4726
4727
4728 BEGIN
4729 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
4730 x_redeploy_flag := 'N' ;
4731
4732 l_out_of_sev := get_txn_type_id('OUT_OF_SERVICE','CSE');
4733 l_proj_insev := get_txn_type_id('PROJECT_ITEM_IN_SERVICE','CSE');
4734 l_issue_hz := get_txn_type_id('ISSUE_TO_HZ_LOC','INV');
4735 l_misc_issue_hz := get_txn_type_id('MISC_ISSUE_HZ_LOC','INV');
4736
4737 OPEN get_redeploy_flag_cur ;
4738 FETCH get_redeploy_flag_cur INTO x_redeploy_flag ;
4739 CLOSE get_redeploy_flag_cur ;
4740
4741 EXCEPTION
4742 WHEN OTHERS THEN
4743 x_return_status := fnd_api.G_RET_STS_ERROR ;
4744 x_error_message := SQLERRM ;
4745 END get_redeploy_flag ;
4746
4747 FUNCTION valid_ib_txn (p_transaction_id IN NUMBER) RETURN BOOLEAN IS
4748
4749 l_api_version NUMBER := 1.0;
4750 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
4751 l_msg_count NUMBER;
4752 l_msg_data VARCHAR2(2000);
4753 l_logical_trx_attr_values INV_DROPSHIP_GLOBALS.logical_trx_attr_tbl;
4754 l_fnd_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
4755 l_ds_return_status VARCHAR2(30);
4756 l_source_type VARCHAR2(50) := NULL;
4757 l_type_id NUMBER := NULL;
4758 l_csi_txn_name VARCHAR2(50) := NULL;
4759 l_log_trx_action_id NUMBER := NULL;
4760 l_log_trx_source_type_id NUMBER := NULL;
4761 l_log_trx_type_code NUMBER := NULL;
4762 l_log_trx_id NUMBER := NULL;
4763 j PLS_INTEGER := 0;
4764 l_log_rec_count NUMBER := 0;
4765 l_mo_issue_hz NUMBER;
4766 l_misc_issue_hz NUMBER;
4767 l_misc_receipt_hz NUMBER;
4768
4769 CURSOR c_mtl_data is
4770 SELECT transaction_id,
4771 inventory_item_id,
4772 transaction_quantity,
4773 source_code,
4774 transaction_action_id,
4775 transaction_type_id,
4776 transaction_source_type_id,
4777 ship_to_location_id
4778 FROM mtl_material_transactions
4779 WHERE transaction_id = p_transaction_id;
4780
4781 r_mtl_data c_mtl_data%rowtype;
4782
4783 CURSOR c_type_class (pc_transaction_type_id NUMBER) is
4784 SELECT type_class,
4785 transaction_source_type_id,
4786 nvl(location_required_flag,'N') location_required_flag
4787 FROM mtl_trx_types_view
4788 WHERE transaction_type_id = pc_transaction_type_id;
4789
4790 r_type_class c_type_class%rowtype;
4791
4792 BEGIN
4793
4794 -- Get all Inventory Data.
4795
4796 FOR r_mtl_data in c_mtl_data LOOP
4797
4798 l_source_type := NULL;
4799 l_type_id := NULL;
4800
4801 -- Get CSI Txn Name for Error
4802 l_csi_txn_name := csi_inv_trxs_pkg.get_inv_name(r_mtl_data.transaction_id);
4803
4804 -- Get Type Class Code
4805 OPEN c_type_class(r_mtl_data.transaction_type_id);
4806 FETCH c_type_class into r_type_class;
4807 CLOSE c_type_class;
4808
4809 -- Get Drop Shipment Info from Inventory API
4810
4811 inv_ds_logical_trx_info_pub.get_logical_attr_values(l_ds_return_status,
4812 l_msg_count,
4813 l_msg_data,
4814 l_logical_trx_attr_values,
4815 l_api_version,
4816 l_init_msg_list,
4817 r_mtl_data.transaction_id);
4818
4819 IF l_ds_return_status = l_fnd_success AND
4820 l_logical_trx_attr_values.count > 0 THEN
4821
4822 FOR j in l_logical_trx_attr_values.first .. l_logical_trx_attr_values.last LOOP
4823
4824 IF (l_logical_trx_attr_values(j).transaction_action_id = 7 AND
4825 l_logical_trx_attr_values(j).transaction_source_type_id = 2 AND
4826 l_logical_trx_attr_values(j).logical_trx_type_code = 2) THEN
4827
4828 l_log_trx_action_id := l_logical_trx_attr_values(j).transaction_action_id;
4829 l_log_trx_source_type_id := l_logical_trx_attr_values(j).transaction_source_type_id;
4830 l_log_trx_type_code := l_logical_trx_attr_values(j).logical_trx_type_code;
4831
4832 FOR j in l_logical_trx_attr_values.first .. l_logical_trx_attr_values.last LOOP
4833
4834 IF (l_logical_trx_attr_values(j).transaction_action_id = 9 AND
4835 l_logical_trx_attr_values(j).transaction_source_type_id = 13 AND
4836 l_logical_trx_attr_values(j).logical_trx_type_code = 2) THEN
4837
4838 l_log_trx_id := l_logical_trx_attr_values(j).transaction_id;
4839 END IF;
4840 END LOOP;
4841
4842 ELSIF (l_logical_trx_attr_values(j).transaction_action_id = 11 AND
4843 l_logical_trx_attr_values(j).transaction_source_type_id = 1 AND
4844 l_logical_trx_attr_values(j).logical_trx_type_code = 2) THEN
4845
4846 l_log_trx_action_id := l_logical_trx_attr_values(j).transaction_action_id;
4847 l_log_trx_source_type_id := l_logical_trx_attr_values(j).transaction_source_type_id;
4848 l_log_trx_type_code := l_logical_trx_attr_values(j).logical_trx_type_code;
4849 l_log_trx_id := l_logical_trx_attr_values(j).transaction_id;
4850
4851 END IF;
4852 END LOOP;
4853 END IF;
4854
4855 -- Start of code to see what kind of source type this is
4856
4857 IF (r_mtl_data.transaction_action_id = 1 AND
4858 r_mtl_data.transaction_source_type_id = 4 AND
4859 r_mtl_data.transaction_type_id NOT IN (33,122,35,37,93) AND
4860 r_type_class.location_required_flag = 'Y' AND
4861 (r_type_class.type_class is null OR r_type_class.type_class <> 1))
4862 THEN
4863 RETURN(TRUE);
4864
4865 ELSIF (r_mtl_data.transaction_action_id = 27 AND
4866 r_mtl_data.transaction_source_type_id in (13,6,3) AND
4867 r_mtl_data.transaction_type_id NOT IN (15,123,43,94) AND
4868 r_type_class.location_required_flag = 'Y' AND
4869 (r_type_class.type_class is null OR r_type_class.type_class <> 1))
4870 THEN
4871 RETURN(TRUE);
4872
4873 ELSIF (r_mtl_data.transaction_action_id = 1 AND
4874 r_mtl_data.transaction_source_type_id in (13,6,3) AND
4875 r_mtl_data.transaction_type_id NOT IN (33,122,35,37,93) AND
4876 r_type_class.location_required_flag = 'Y' AND
4877 (r_type_class.type_class is null OR r_type_class.type_class <> 1))
4878 THEN
4879
4880 RETURN(TRUE);
4881
4882 ELSIF (l_log_trx_action_id = 7 AND
4883 l_log_trx_source_type_id = 2 AND
4884 l_log_trx_id IS NOT NULL)
4885 THEN
4886 ---Transactions fall in this category are :
4887 --- Type Action ID Txn Type ID
4888 ----------------------- ------------- ------------
4889 --1. Logical Sales Order Issue 7 30
4890
4891 IF l_log_trx_id <> p_transaction_id THEN
4892 RETURN(FALSE);
4893 ELSE
4894 RETURN(TRUE);
4895 END IF;
4896
4897 ELSIF (r_mtl_data.transaction_action_id = 1 AND
4898 r_mtl_data.transaction_source_type_id = 4 AND
4899 r_type_class.type_class = 1) -- Issue to Project
4900 THEN
4901 RETURN(TRUE);
4902
4903 ELSIF (r_mtl_data.transaction_action_id = 1 AND -- Misc. Issue to Project
4904 -- Acct/Acct Alias, Inv
4905 r_mtl_data.transaction_source_type_id in (3,6,13) AND
4906 r_type_class.type_class = 1)
4907 THEN
4908 RETURN(TRUE);
4909
4910 ELSIF (r_mtl_data.transaction_action_id = 27 AND
4911 r_mtl_data.transaction_source_type_id in (3,6,13) AND
4912 r_type_class.type_class = 1) -- Misc Receipt from Project
4913 -- Acct/Acct Alias, Inv
4914 THEN
4915 RETURN(TRUE);
4916
4917 ELSIF (r_mtl_data.transaction_action_id = 1 AND
4918 r_mtl_data.transaction_source_type_id = 16)
4919
4920 THEN
4921 ---Transactions fall in this category are :
4922 --- Type Action ID Txn Type ID
4923 ----------------------- ------------- ------------
4924 --1. Project Contract Issue 1 77
4925 RETURN(TRUE);
4926
4927 ELSIF (r_mtl_data.transaction_action_id = 1 AND
4928 r_mtl_data.transaction_source_type_id = 2) OR
4929 -- Changed to 2 from Txn Type ID 33
4930 (r_mtl_data.transaction_action_id = 1 AND
4931 r_mtl_data.transaction_source_type_id = 8)
4932 THEN
4933 ---Transactions fall in this category are :
4934 --- Type Action ID Txn Type ID
4935 ----------------------- ------------- ------------
4936 --1. Sales Order Issue 1 33
4937 --2. Intrnl Ord Issue(Ship Conf) 1 34
4938
4939 RETURN(TRUE);
4940
4941 ELSIF (r_mtl_data.transaction_action_id = 27 AND
4942 r_mtl_data.transaction_source_type_id = 12)
4943 -- Changed to 12 from Txn Type ID 15
4944 THEN
4945 ---Transactions fall in this category are :
4946 --- Type Action ID Txn Type ID
4947 ----------------------- ------------- ------------
4948 --1. RMA Receipt 27 15
4949
4950 RETURN(TRUE);
4951
4952 ELSIF (r_mtl_data.transaction_quantity > 0 AND -- Subinventory Transfer
4953 r_mtl_data.transaction_action_id = 2)
4954 OR (r_mtl_data.transaction_action_id = 28 AND -- Sales Order Staging
4955 r_mtl_data.transaction_source_type_id = 2 AND
4956 -- Changed to 2 from Txn ID 52
4957 r_mtl_data.transaction_quantity > 0)
4958 OR (r_mtl_data.transaction_action_id = 28 AND -- Intrnl SaleOrd Staging
4959 r_mtl_data.transaction_source_type_id = 8 AND
4960 r_mtl_data.transaction_quantity > 0)
4961 -- Changed to 8 from Txn ID 53
4962 -- changed this to > for bug 2384317
4963 THEN
4964 ---Transactions fall in this category are :
4965 --- Type Action ID Txn Type ID
4966 ----------------------- ------------- ------------
4967 --1. Subinventory Transfer 2 2
4968 --2. Cycle Count SubInv Xfer 2 5
4969 --3. Physical Inv Xfer 2 9
4970 --4. Internal Order Xfer 2 50
4971 --5. Backflush Xfer 2 51
4972 --6. Internal Order Pick 28 53
4973 --7. Sales Order Pick 28 52
4974 --8. Move Order Transfer 2 64
4975 --9. Project Borrow 2 66
4976 --10. Project Transfer 2 67
4977 --11. Project Payback 2 68
4978
4979 RETURN(TRUE);
4980
4981 ELSIF (r_mtl_data.transaction_action_id = 12 AND -- Interorg Receipt
4982 r_mtl_data.transaction_source_type_id = 13)
4983 -- Changed to 13 from Txn ID 12
4984
4985 THEN
4986 ---Transactions fall in this category are :
4987 --- Type Action ID Txn Type ID
4988 ----------------------- ------------- ------------
4989 --1. InTransit Receipt 12 12
4990
4991 RETURN(TRUE);
4992
4993 ELSIF (r_mtl_data.transaction_action_id = 21 AND
4994 r_mtl_data.transaction_source_type_id = 13) -- Interorg Shipment
4995 -- Changed to 13 from Txn ID 21
4996
4997 THEN
4998 ---Transactions fall in this category are :
4999 --- Type Action ID Txn Type ID
5000 ----------------------- ------------- ------------
5001 --1. InTransit Shipment 21 21
5002
5003 RETURN(TRUE);
5004
5005 ELSIF (r_mtl_data.transaction_action_id = 3 AND -- Direct Org Transfer
5006 r_mtl_data.transaction_source_type_id = 13 AND
5007 r_mtl_data.transaction_quantity > 0)
5008 -- Changed to 13 from Txn ID 3
5009 THEN
5010 ---Transactions fall in this category are :
5011 --- Type Action ID Txn Type ID
5012 ----------------------- ------------- ------------
5013 --1. Direct Org Transfer 3 3
5014
5015 RETURN(TRUE);
5016
5017 ELSIF (r_mtl_data.transaction_action_id = 12 AND -- Int So In Trans Receipt
5018 r_mtl_data.transaction_source_type_id = 7)
5019 THEN
5020 ---Transactions fall in this category are :
5021 --- Type Action ID Txn Type ID
5022 ----------------------- ------------- ------------
5023 --1. Int Req Intr Rcpt 12 61
5024
5025 RETURN(TRUE);
5026
5027 ELSIF (r_mtl_data.transaction_action_id = 21 AND -- Int So In Trans Ship
5028 r_mtl_data.transaction_source_type_id = 8)
5029
5030 THEN
5031 ---Transactions fall in this category are :
5032 --- Type Action ID Txn Type ID
5033 ----------------------- ------------- ------------
5034 --1. Int Order Intr Ship 21 62
5035
5036 RETURN(TRUE);
5037
5038 ELSIF (r_mtl_data.transaction_action_id = 3 AND -- ISO Direct Shipment
5039 r_mtl_data.transaction_source_type_id in (7,8) AND
5040 r_mtl_data.transaction_quantity > 0)
5041
5042 THEN
5043 ---Transactions fall in this category are :
5044 --- Type Action ID Txn Type ID
5045 ----------------------- ------------- ------------
5046 --1. Int Order Direct Ship 3 54
5047
5048 RETURN(TRUE);
5049
5050 ELSIF r_mtl_data.transaction_action_id = 27 AND
5051 r_mtl_data.transaction_source_type_id = 1
5052 -- Changed to 1 from Txn Type ID 18
5053
5054 ---Transactions fall in this category are :
5055 --- Type Action ID Txn Type ID
5056 ----------------------- ------------- ------------
5057 --1. PO Receipt 27 18
5058
5059 THEN
5060 RETURN(TRUE);
5061
5062 ELSIF r_mtl_data.transaction_action_id = 4
5063
5064 ---Transactions fall in this category are :
5065 --- Type Action ID Txn Type ID
5066 ----------------------- ------------- ------------
5067 --1. Cycle Count Adjust (-/+) 4 4
5068
5069 THEN
5070 RETURN(TRUE);
5071
5072 ELSIF r_mtl_data.transaction_action_id = 8
5073
5074 ---Transactions fall in this category are :
5075 --- Type Action ID Txn Type ID
5076 ----------------------- ------------- ------------
5077 --1. Physical Inv Adjust(-/+) 8 8
5078
5079 THEN
5080 RETURN(TRUE);
5081
5082 ELSIF (r_mtl_data.transaction_action_id = 27 AND
5083 r_mtl_data.transaction_source_type_id in (4,13,6,3) AND
5084 r_mtl_data.transaction_type_id NOT IN (15,123,43,94) AND
5085 (r_type_class.type_class is null OR r_type_class.type_class <> 1)) OR
5086 (r_mtl_data.transaction_action_id = 29 AND
5087 r_mtl_data.transaction_quantity > 0 AND
5088 r_mtl_data.transaction_source_type_id = 1) OR -- + Int Adjustment
5089 -- + PO Adjustment
5090 -- + Ship Adjustment
5091 (l_log_trx_action_id = 11 AND
5092 r_mtl_data.transaction_quantity > 0 AND
5093 l_log_trx_source_type_id = 1 AND
5094 l_log_trx_type_code = 2) -- (+) Logical PO Adjustment
5095 THEN
5096 ---Transactions fall in this category are :
5097 --- Type Action ID Txn Type ID
5098 ----------------------- ------------- ------------
5099 --1. Account Receipt 27 40
5100 --2. Account Alias receipt 27 41
5101 --3. Miscellaneous Receipt 27 42
5102 --4. + PO Adjustment 29 71
5103 --5. + Int Req Adjust 29 72
5104 --6. + Shipment Rcpt Adjust 29 70
5105
5106 RETURN(TRUE);
5107
5108 ELSIF (r_mtl_data.transaction_action_id = 1 AND
5109 r_mtl_data.transaction_source_type_id in (4,13,6,3) AND
5110 r_mtl_data.transaction_type_id NOT IN (33,122,35,37,93) AND
5111 (r_type_class.type_class is null OR r_type_class.type_class <> 1)) OR
5112 (r_mtl_data.transaction_action_id = 29 AND
5113 r_mtl_data.transaction_quantity < 0 AND
5114 r_mtl_data.transaction_source_type_id = 1) OR -- (-) PO Adjustment
5115 (r_mtl_data.transaction_action_id = 1 AND
5116 r_mtl_data.transaction_quantity < 0 AND
5117 r_mtl_data.transaction_source_type_id = 1) OR -- (-) Return to Vendor
5118 (l_log_trx_action_id = 11 AND
5119 r_mtl_data.transaction_quantity < 0 AND
5120 l_log_trx_source_type_id = 1 AND
5121 l_log_trx_type_code = 2) -- (-) Logical PO Adjustment
5122 THEN
5123 ---Transactions fall in this category are :
5124 --- Type Action ID Txn Type ID
5125 ----------------------- ------------- ------------
5126 --1. Account Alias Issue 1 31
5127 --2. Miscellaneous Issue 1 32
5128 --4. Return to Vendor (PO) 1 36
5129 --5. Account Issue 1 1
5130 --6. (-) PO Adjustment 29 71
5131 --7. (-) Int Req Adjust 29 72
5132 --8. (-) Shipment Rcp Adjust 29 70
5133 --9. Move Order Issue 1 63 (recheck)
5134
5135 --EXCLUDED TRANSACTIONS ARE
5136 -- 33 Sales order issue
5137 -- 35 WIP component issue
5138 -- 37 RMA Return
5139 -- 93 Field Service Usage
5140 -- 122 Issue to (User Defined Seeded)
5141
5142 RETURN(TRUE);
5143
5144 ELSIF (r_mtl_data.transaction_action_id = 32 AND
5145 r_mtl_data.transaction_source_type_id = 5)
5146 -- Changed to 5 from Txn Type ID 17
5147 THEN
5148 ---Transactions fall in this category are :
5149 --- Type Action ID Txn Type ID
5150 ----------------------- ------------- ------------
5151 --1. WIP Assembly Return 32 17
5152
5153 RETURN(TRUE);
5154
5155 ELSIF (r_mtl_data.transaction_action_id = 1 AND
5156 r_mtl_data.transaction_source_type_id = 5)
5157 -- Changed to 5 from Txn Type ID 35
5158 THEN
5159 ---Transactions fall in this category are :
5160 --- Type Action ID Txn Type ID
5161 ----------------------- ------------- ------------
5162 --1. WIP Component Issue 1 35
5163
5164 RETURN(TRUE);
5165
5166 ELSIF (r_mtl_data.transaction_action_id = 33 AND
5167 r_mtl_data.transaction_source_type_id = 5)
5168 -- Changed to 5 from Txn Type ID 38
5169 THEN
5170 ---Transactions fall in this category are :
5171 --- Type Action ID Txn Type ID
5172 ----------------------- ------------- ------------
5173 --1. WIP Neg Comp Issue 33 38
5174
5175 RETURN(TRUE);
5176
5177 ELSIF (r_mtl_data.transaction_action_id = 27 AND
5178 r_mtl_data.transaction_source_type_id = 5)
5179 -- Changed to 5 from Txn Type ID 43
5180 THEN
5181 ---Transactions fall in this category are :
5182 --- Type Action ID Txn Type ID
5183 ----------------------- ------------- ------------
5184 --1. WIP Component Return 27 43
5185
5186 RETURN(TRUE);
5187
5188 ELSIF (r_mtl_data.transaction_action_id = 31 AND
5189 r_mtl_data.transaction_source_type_id = 5)
5190 -- Changed to 5 from Txn Type ID 44
5191 THEN
5192 ---Transactions fall in this category are :
5193 --- Type Action ID Txn Type ID
5194 ----------------------- ------------- ------------
5195 --1. WIP Assy Completion 31 44
5196
5197 RETURN(TRUE);
5198
5199 ELSIF (r_mtl_data.transaction_action_id = 34 AND
5200 r_mtl_data.transaction_source_type_id = 5)
5201 -- Changed to 5 from Txn Type ID 48
5202 THEN
5203 ---Transactions fall in this category are :
5204 --- Type Action ID Txn Type ID
5205 ----------------------- ------------- ------------
5206 --1. WIP Neg Comp Return 34 48
5207
5208 RETURN(TRUE);
5209
5210 ELSE
5211 -- Source Type not Recognized
5212 RETURN(FALSE);
5213 END IF;
5214 END LOOP; -- End of c_mtl_data Cursor Loop
5215
5216 RETURN(FALSE);
5217
5218 END; -- valid_ib_txn
5219
5220 PROCEDURE set_item_attr_query_values(
5221 l_mtl_item_tbl IN CSI_INV_TRXS_PKG.MTL_ITEM_TBL_TYPE,
5222 table_index IN NUMBER,
5223 p_source IN VARCHAR2,
5224 x_instance_query_rec OUT NOCOPY csi_datastructures_pub.instance_query_rec,
5225 x_return_status OUT NOCOPY varchar2)
5226 IS
5227
5228 l_instance_query_rec csi_datastructures_pub.instance_query_rec;
5229 l_serial_tagged NUMBER := 1;
5230 l_returned_item varchar2(1) := 'N';
5231
5232 BEGIN
5233
5234 x_return_status := fnd_api.g_ret_sts_success;
5235
5236 debug('Setting Item Control Attributes on Query Record');
5237
5238 l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
5239 l_instance_query_rec.inventory_item_id := l_mtl_item_tbl(table_index).inventory_item_id;
5240
5241 -- Serial Control and if Non Serial all other controls are checked. If this is serial we just set that
5242 -- and exit since the query is just by item/serial
5243
5244 l_serial_tagged := inv_cache.get_serial_tagged (
5245 p_inventory_item_id => l_mtl_item_tbl(table_index).inventory_item_id,
5246 p_organization_id => l_mtl_item_tbl(table_index).organization_id,
5247 p_transaction_type_id => l_mtl_item_tbl(table_index).transaction_type_id);
5248
5249
5250 debug('serial_tagged: '||l_serial_tagged);
5251 debug('serial_number_control_code' || l_mtl_item_tbl(table_index).serial_number_control_code);
5252
5253 BEGIN
5254 SELECT 'Y' INTO l_returned_item
5255 FROM sys.dual
5256 WHERE EXISTS (
5257 SELECT 1 FROM csi_item_instances
5258 WHERE serial_number = l_mtl_item_tbl(table_index).serial_number
5259 AND inventory_item_id = l_mtl_item_tbl(table_index).inventory_item_id
5260 AND (instance_usage_code = 'RETURNED'
5261 OR (instance_usage_code = 'IN_TRANSIT' AND active_end_date IS NOT NULL)));
5262 EXCEPTION
5263 WHEN no_data_found THEN
5264 null;
5265 END;
5266
5267 debug('l_returned_item : ' || l_returned_item);
5268 -- Bug 11695798
5269 --IF l_mtl_item_tbl(table_index).serial_number_control_code in (1,6) AND l_serial_tagged = 2 AND l_returned_item = 'N' THEN
5270 IF (l_mtl_item_tbl(table_index).serial_number_control_code in (1,6) AND l_serial_tagged = 2 AND l_returned_item = 'N')
5271 OR (l_mtl_item_tbl(table_index).serial_number_control_code in (1,6) AND
5272 NVL(l_mtl_item_tbl(table_index).serial_number,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR AND
5273 l_returned_item = 'N') THEN
5274 l_instance_query_rec.serial_number := NULL;
5275
5276 -- Lot Control
5277 IF l_mtl_item_tbl(table_index).lot_control_code = 1 THEN
5278 l_instance_query_rec.lot_number := NULL;
5279 ELSE
5280 l_instance_query_rec.lot_number := l_mtl_item_tbl(table_index).lot_number;
5281 END IF;
5282
5283 -- Revision Control
5284 IF l_mtl_item_tbl(table_index).revision_qty_control_code = 1 THEN
5285 l_instance_query_rec.inventory_revision := NULL;
5286 ELSE
5287 l_instance_query_rec.inventory_revision := l_mtl_item_tbl(table_index).revision;
5288 END IF;
5289
5290 -- Locator Control
5291 -- Since Locator control can be set at Item, Org or Subinv Level just take what is there
5292 -- and do not look at the control code
5293 --IF l_mtl_item_tbl(table_index).location_control_code = 1 THEN
5294 -- l_instance_query_rec.inv_locator_id := NULL;
5295 --ELSE
5296 IF p_source = 'TRANSFER' THEN
5297 l_instance_query_rec.inv_locator_id := l_mtl_item_tbl(table_index).transfer_locator_id;
5298 ELSE
5299 l_instance_query_rec.inv_locator_id := l_mtl_item_tbl(table_index).locator_id;
5300 END IF;
5301 --END IF;
5302 ELSE
5303 l_instance_query_rec.serial_number := l_mtl_item_tbl(table_index).serial_number;
5304
5305 END IF; -- End of Serial IF
5306
5307 x_instance_query_rec := l_instance_query_rec;
5308
5309 debug('Done setting attributes in query passing out to set the rest of the values ');
5310
5311 EXCEPTION
5312 WHEN others THEN
5313 x_return_status := fnd_api.g_ret_sts_error;
5314
5315 END set_item_attr_query_values;
5316
5317 END csi_inv_trxs_pkg;