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