[Home] [Help]
PACKAGE BODY: APPS.CSI_FA_INSTANCE_GRP
Source
1 PACKAGE BODY csi_fa_instance_grp AS
2 /* $Header: csigfaib.pls 120.17 2011/11/15 05:56:24 sjawaji ship $ */
3
4 PROCEDURE debug(
5 p_message IN varchar2)
6 IS
7 BEGIN
8 csi_gen_utility_pvt.put_line(p_message);
9 END debug;
10
11 FUNCTION dump_error_stack RETURN varchar2
12 IS
13 l_msg_count number;
14 l_msg_data varchar2(2000);
15 l_msg_index_out number;
16 x_msg_data varchar2(4000);
17 BEGIN
18 x_msg_data := null;
19 fnd_msg_pub.count_and_get(
20 p_count => l_msg_count,
21 p_data => l_msg_data);
22
23 FOR l_ind IN 1..l_msg_count
24 LOOP
25 fnd_msg_pub.get(
26 p_msg_index => l_ind,
27 p_encoded => fnd_api.g_false,
28 p_data => l_msg_data,
29 p_msg_index_out => l_msg_index_out);
30
31 x_msg_data := ltrim(x_msg_data||' '||l_msg_data);
32
33 IF length(x_msg_data) > 1999 THEN
34 x_msg_data := substr(x_msg_data, 1, 1999);
35 exit;
36 END IF;
37 END LOOP;
38 RETURN x_msg_data;
39 EXCEPTION
40 when others then
41 RETURN x_msg_data;
42 END dump_error_stack;
43
44 PROCEDURE validate_inst_asset(
45 px_inst_asset_rec IN OUT nocopy csi_datastructures_pub.instance_asset_rec,
46 x_return_status OUT nocopy varchar2)
47 IS
48 l_acct_class_code varchar2(30);
49 l_location_type_code varchar2(30);
50 l_inventory_item_id number;
51 l_organization_id number;
52 l_inst_num varchar2(30);
53 l_inventory_item varchar2(80);
54 l_asset_creation_code varchar2(1);
55 l_serial_number varchar2(120);
56 l_serial_code number;
57 l_pending_status varchar2(30) := 'PENDING';
58 l_pending_txn boolean := FALSE;
59 l_pending_txn_id number;
60 l_pending_mass_add boolean := FALSE;
61 l_fa_mass_add_id number;
62
63
64 CURSOR cia_cur(p_instance_id in number, p_asset_id in number) IS
65 SELECT cia.instance_asset_id,
66 cia.asset_quantity,
67 cia.object_version_number,
68 cia.fa_asset_id,
69 cia.active_end_date
70 FROM csi_i_assets cia
71 WHERE cia.instance_id = p_instance_id
72 AND cia.fa_asset_id = p_asset_id
73 AND sysdate BETWEEN nvl(cia.active_start_date, sysdate-1) AND nvl(cia.active_end_date, sysdate+1);
74
75 CURSOR uniq_fa_cur(p_instance_id in number) IS
76 SELECT cia.fa_asset_id
77 FROM csi_i_assets cia
78 WHERE instance_id = p_instance_id
79 AND sysdate BETWEEN nvl(cia.active_start_date, sysdate-1) AND nvl(cia.active_end_date, sysdate+1);
80
81 CURSOR pending_txn_cur(p_instance_id in number) is
82 SELECT ct.transaction_id
83 FROM csi_item_instances_h ciih,
84 csi_transactions ct
85 WHERE ciih.instance_id = p_instance_id
86 AND ct.transaction_id = ciih.transaction_id
87 AND ct.transaction_type_id IN (117, 129, 128, 105, 112, 118, 119)
88 AND ct.transaction_status_code = l_pending_status
89 AND ct.inv_material_transaction_id is not null;
90
91 -- eib supported transactions for fixed asset creation
92 ------------------------------------------------------------
93 -- 117 - ('MISC_RECEIPT') - depreciable items
94 -- 129 - ('ACCT_ALIAS_RECEIPT') - depreciable items
95 -- 128 - ('ACCT_RECEIPT') - depreciable items
96 -- 105 - ('PO_RECEIPT_INTO_PROJECT') - depreciable items
97 -- 112 - ('PO_RECEIPT_INTO_INVENTORY') - depreciable items
98 -- 118 - ('PHYSICAL_INVENTORY') - depreciable items
99 -- 119 - ('CYCLE_COUNT_ADJUSTMENT' - depreciable items
100 ------------------------------------------------------------
101
102 CURSOR pending_mass_add_cur(p_instance_id in number) IS
103 SELECT cia.fa_mass_addition_id
104 FROM csi_i_assets cia
105 WHERE instance_id = p_instance_id
106 AND fa_asset_id is null
107 AND sysdate BETWEEN nvl(cia.active_start_date, sysdate-1) AND nvl(cia.active_end_date, sysdate+1);
108
109 BEGIN
110
111 debug('validate_inst_asset');
112
113 x_return_status := fnd_api.g_ret_sts_success;
114
115 IF nvl(px_inst_asset_rec.instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
116 SELECT accounting_class_code,
117 location_type_code,
118 inventory_item_id,
119 last_vld_organization_id,
120 instance_number,
121 serial_number
122 INTO l_acct_class_code,
123 l_location_type_code,
124 l_inventory_item_id,
125 l_organization_id,
126 l_inst_num,
127 l_serial_number
128 FROM csi_item_instances
129 WHERE instance_id = px_inst_asset_rec.instance_id;
130
131 IF l_acct_class_code = 'CUST_PROD' THEN
132 fnd_message.set_name('CSI', 'CSI_INST_ASSET_AC_INVALID');
133 fnd_message.set_token('INST_NUM', l_inst_num);
134 fnd_message.set_token('AC_CODE', l_acct_class_code);
135 fnd_msg_pub.add;
136 RAISE fnd_api.g_exc_error;
137 END IF;
138
139 IF l_location_type_code in ('WIP', 'PROJECT', 'IN_TRANSIT') THEN
140 fnd_message.set_name('CSI', 'CSI_INST_ASSET_LOC_INVALID');
141 fnd_message.set_token('INST_NUM', l_inst_num);
142 fnd_message.set_token('LOC_CODE', l_location_type_code);
143 fnd_msg_pub.add;
144 RAISE fnd_api.g_exc_error;
145 END IF;
146
147 SELECT asset_creation_code,
148 segment1,
149 serial_number_control_code
150 INTO l_asset_creation_code,
151 l_inventory_item,
152 l_serial_code
153 FROM mtl_system_items
154 WHERE inventory_item_id = l_inventory_item_id
155 AND organization_id = l_organization_id;
156
157 IF nvl(l_asset_creation_code,'0') in ('1', 'Y') THEN
158
159 l_pending_txn := FALSE;
160 FOR pending_txn_rec in pending_txn_cur(px_inst_asset_rec.instance_id)
161 LOOP
162 l_pending_txn := TRUE;
163 l_pending_txn_id := pending_txn_rec.transaction_id;
164 exit;
165 END LOOP;
166
167 IF l_pending_txn THEN
168 fnd_message.set_name('CSI', 'CSI_DEPR_ADD_PENDING_TXN');
169 fnd_message.set_token('TXN_ID', l_pending_txn_id);
170 fnd_msg_pub.add;
171 RAISE fnd_api.g_exc_error;
172 END IF;
173
174 l_pending_mass_add := FALSE;
175 FOR pending_mass_add_rec IN pending_mass_add_cur(px_inst_asset_rec.instance_id)
176 LOOP
177 l_pending_mass_add := TRUE;
178 l_fa_mass_add_id := pending_mass_add_rec.fa_mass_addition_id;
179 exit;
180 END LOOP;
181
182 IF l_pending_mass_add THEN
183 fnd_message.set_name('CSI', 'CSI_DEPR_ADD_PENDING_MASSADD');
184 fnd_message.set_token('MASS_ADD_ID', l_fa_mass_add_id);
185 fnd_msg_pub.add;
186 RAISE fnd_api.g_exc_error;
187 END IF;
188
189 END IF;
190
191 debug('serial code : '||l_serial_code);
192 debug('inst_asset_id : '||px_inst_asset_rec.instance_asset_id);
193 debug('inst_id : '||px_inst_asset_rec.instance_id);
194 debug('fa_asset_id : '||px_inst_asset_rec.fa_asset_id);
195
196 IF l_serial_code in (2, 5) or l_serial_number is not null THEN
197
198 IF nvl(px_inst_asset_rec.instance_asset_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
199 -- create case
200 FOR uniq_fa_rec IN uniq_fa_cur(px_inst_asset_rec.instance_id)
201 LOOP
202 fnd_message.set_name('CSI', 'CSI_SRL_DUP_FA_ERROR');
203 fnd_message.set_token('INST_ID', px_inst_asset_rec.instance_id);
204 fnd_message.set_token('ASSET_ID', uniq_fa_rec.fa_asset_id);
205 fnd_msg_pub.add;
206 raise fnd_api.g_exc_error;
207 END LOOP;
208
209 ELSE
210 null;
211 -- update case
212 END IF;
213 ELSE
214 FOR cia_rec IN cia_cur(px_inst_asset_rec.instance_id, px_inst_asset_rec.fa_asset_id)
215 LOOP
216 px_inst_asset_rec.instance_asset_id := cia_rec.instance_asset_id;
217 px_inst_asset_rec.object_version_number := cia_rec.object_version_number;
218 exit;
219 END LOOP;
220 END IF;
221
222 END IF;
223
224 EXCEPTION
225 WHEN fnd_api.g_exc_error THEN
226 x_return_status := fnd_api.g_ret_sts_error;
227 END validate_inst_asset;
228
229 PROCEDURE derive_fa_missing_values(
230 p_instance_rec IN csi_datastructures_pub.instance_rec,
231 p_fixed_asset_rec IN fixed_asset_rec,
232 x_fa_location_id OUT nocopy number,
233 x_fa_quantity OUT nocopy number,
234 x_fa_book_type_code OUT nocopy varchar2,
235 x_return_status OUT nocopy varchar2)
236 IS
237
238 l_location_type_code varchar2(30);
239 l_location_id number;
240 l_instance_quantity number;
241
242 l_location_table varchar2(30);
243 l_fa_location_id number;
244 l_fa_quantity number;
245 l_fa_book_type_code varchar2(30);
246
247 l_latest_fa_location_id number;
248 l_latest_fa_quantity number;
249 l_dist_found boolean := FALSE;
250
251 CURSOR btc_cur(p_asset_id IN number) IS
252 SELECT fb.book_type_code
253 FROM fa_books fb,
254 fa_book_controls fbc
255 WHERE fb.asset_id = p_asset_id
256 AND fb.date_ineffective is null
257 AND fbc.book_type_code = fb.book_type_code
258 AND fbc.book_class = 'CORPORATE';
259
260 CURSOR a_loc_cur(p_table in varchar2, p_loc_id in number) IS
261 SELECT fa_location_id
262 FROM csi_a_locations
263 WHERE location_table = p_table
264 AND location_id = p_loc_id;
265
266 CURSOR fa_dist_cur(p_asset_id IN number, p_book_type_code in varchar2) IS
267 SELECT location_id,
268 distribution_id,
269 units_assigned
270 FROM fa_distribution_history
271 WHERE asset_id = p_asset_id
272 AND book_type_code = p_book_type_code
273 AND date_ineffective is null
274 ORDER BY date_effective desc; -- latest one first
275
276 BEGIN
277
278 x_return_status := fnd_api.g_ret_sts_success;
279
280 debug('derive fa loc');
281
282 IF nvl(p_fixed_asset_rec.book_type_code, fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
283 l_fa_book_type_code := p_fixed_asset_rec.book_type_code;
284 ELSE
285 FOR btc_rec IN btc_cur(p_fixed_asset_rec.asset_id)
286 LOOP
287 l_fa_book_type_code := btc_rec.book_type_code;
288 exit;
289 END LOOP;
290 END IF;
291
292 l_location_table := null;
293
294 IF nvl(p_instance_rec.instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
295 SELECT location_type_code,
296 location_id,
297 quantity
298 INTO l_location_type_code,
299 l_location_id,
300 l_instance_quantity
301 FROM csi_item_instances
302 WHERE instance_id = p_instance_rec.instance_id;
303 ELSE
304 l_location_type_code := p_instance_rec.location_type_code;
305 l_location_id := p_instance_rec.location_id;
306 END IF;
307
308 IF nvl(p_fixed_asset_rec.asset_location_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
309 l_fa_location_id := p_fixed_asset_rec.asset_location_id;
310 ELSE
311
312 l_location_table := null;
313
314 IF l_location_type_code = 'INVENTORY' THEN
315 l_location_table := 'HR_LOCATIONS';
316 ELSIF l_location_type_code = 'HZ_LOCATIONS' THEN
317 l_location_table := 'HZ_LOCATIONS';
318 ELSIF l_location_type_code = 'HZ_PARTY_SITES' THEN
319 l_location_table := 'HZ_LOCATIONS';
320
321 SELECT location_id
322 INTO l_location_id
323 FROM hz_party_sites
324 WHERE party_site_id = l_location_id;
325
326 ELSIF l_location_type_code = 'INTERNAL_SITE' THEN
327 l_location_table := 'HR_LOCATIONS';
328 END IF;
329
330 debug('location table :'||l_location_table);
331
332 IF l_location_table is not null THEN
333 FOR a_loc_rec IN a_loc_cur(l_location_table, l_location_id)
334 LOOP
335 l_fa_location_id := a_loc_rec.fa_location_id;
336 END LOOP;
337 END IF;
338 END IF;
339
340 debug(' l_fa_location_id : '||l_fa_location_id);
341
342 l_dist_found := FALSE;
343 -- now get the quantity from the distribution
344 FOR fa_dist_rec IN fa_dist_cur(p_fixed_asset_rec.asset_id, l_fa_book_type_code)
345 LOOP
346 IF fa_dist_cur%rowcount = 1 THEN
347 l_latest_fa_location_id := fa_dist_rec.location_id;
348 l_latest_fa_quantity := fa_dist_rec.units_assigned;
349 END IF;
350 IF fa_dist_rec.location_id = l_fa_location_id THEN
351 l_dist_found := TRUE;
352 l_fa_quantity := fa_dist_rec.units_assigned;
353 exit;
354 END IF;
355 END LOOP;
356
357 IF NOT(l_dist_found) THEN
358 l_fa_location_id := l_latest_fa_location_id;
359 l_fa_quantity := l_latest_fa_quantity;
360 END IF;
361
362 debug(' fa location id : '||l_fa_location_id);
363
364 x_fa_location_id := l_fa_location_id;
365 x_fa_quantity := least(l_fa_quantity, l_instance_quantity);
366 x_fa_book_type_code := l_fa_book_type_code;
367
368 END derive_fa_missing_values;
369
370
371 PROCEDURE create_item_instance(
372 p_fixed_asset_rec IN fixed_asset_rec,
373 p_eam_rec IN eam_rec,
374 p_instance_rec IN csi_datastructures_pub.instance_rec,
375 p_instance_serial_tbl IN instance_serial_tbl,
376 p_party_tbl IN csi_datastructures_pub.party_tbl,
377 p_party_account_tbl IN csi_datastructures_pub.party_account_tbl,
378 px_csi_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
379 x_instance_tbl OUT nocopy csi_datastructures_pub.instance_tbl,
380 x_instance_asset_tbl OUT nocopy csi_datastructures_pub.instance_asset_tbl,
381 x_return_status OUT nocopy varchar2,
382 x_error_message OUT nocopy varchar2)
383 IS
384
385 l_fa_location_id number;
386 l_fa_quantity number;
387 l_fa_book_type_code varchar2(30);
388
389 l_miss_num number := fnd_api.g_miss_num;
390 l_miss_char varchar2(200) := fnd_api.g_miss_char;
391
392 l_serial_control_code number;
393 l_eam_item_type number;
394 l_eam_item boolean := FALSE;
395
396 -- group create_item_instance variables
397 l_instance_tbl csi_datastructures_pub.instance_tbl;
398 l_ext_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
399 l_party_tbl csi_datastructures_pub.party_tbl;
400 l_account_tbl csi_datastructures_pub.party_account_tbl;
401 l_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
402 l_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
403 l_instance_asset_tbl csi_datastructures_pub.instance_asset_tbl;
404 l_csi_txn_tbl csi_datastructures_pub.transaction_tbl;
405 l_grp_error_tbl csi_datastructures_pub.grp_error_tbl;
406
407
408 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
409 l_msg_count number;
410 l_msg_data varchar2(2000);
411 l_error_message varchar2(2000);
412 l_warning_flag varchar2(1) := 'N';
413
414 --
415 g_inst_ind binary_integer := 0;
416 g_pty_ind binary_integer := 0;
417 g_pa_ind binary_integer := 0;
418 g_ia_ind binary_integer := 0;
419
420 --bug 9227016
421 l_item_inst_grp_excep EXCEPTION;
422 l_error_msg VARCHAR2(5000) := '';
423
424 BEGIN
425
426 x_return_status := fnd_api.g_ret_sts_success;
427
428 savepoint fa_grp_create_instance;
429
430 -- validate mandatory fields
431
432 -- inventory item id
433 csi_item_instance_vld_pvt.check_reqd_param_num(
434 p_number => p_instance_rec.inventory_item_id,
435 p_param_name => 'p_instance_rec.inventory_item_id',
436 p_api_name => 'csi_fa_instance_grp.create_item_instance');
437
438 -- validation organization id
439 csi_item_instance_vld_pvt.check_reqd_param_num(
440 p_number => p_instance_rec.vld_organization_id,
441 p_param_name => 'p_instance_rec.vld_organization_id',
442 p_api_name => 'csi_fa_instance_grp.create_item_instance');
443
444 -- location type code
445 csi_item_instance_vld_pvt.check_reqd_param_char(
446 p_variable => p_instance_rec.location_type_code,
447 p_param_name => 'p_instance_re.location_type_code',
448 p_api_name => 'csi_fa_instance_grp.create_item_instance');
449
450 -- location id
451 csi_item_instance_vld_pvt.check_reqd_param_num(
452 p_number => p_instance_rec.location_id,
453 p_param_name => 'p_instance_rec.location_id',
454 p_api_name => 'csi_fa_instance_grp.create_item_instance');
455
456 -- source_transaction_date in csi_transactions
457 csi_item_instance_vld_pvt.check_reqd_param_date(
458 p_date => px_csi_txn_rec.source_transaction_date,
459 p_param_name => 'px_csi_txn_rec.source_transaction_date',
460 p_api_name => 'csi_fa_instance_grp.create_item_instance');
461
462 -- transaction_type_id in csi_transactions
463 csi_item_instance_vld_pvt.check_reqd_param_num(
464 p_number => px_csi_txn_rec.transaction_type_id,
465 p_param_name => 'px_csi_txn_rec.transaction_type_id',
466 p_api_name => 'csi_fa_instance_grp.create_item_instance');
467
468 -- derive eam_item_type
469 IF nvl(p_instance_rec.inventory_item_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
470 AND
471 nvl(p_instance_rec.vld_organization_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
472 THEN
473
474 SELECT eam_item_type,
475 serial_number_control_code
476 INTO l_eam_item_type,
477 l_serial_control_code
478 FROM mtl_system_items
479 WHERE inventory_item_id = p_instance_rec.inventory_item_id
480 AND organization_id = p_instance_rec.vld_organization_id;
481
482 IF l_eam_item_type in (1, 3) AND l_serial_control_code <> 1 THEN
483 l_eam_item := TRUE;
484 END IF;
485
486 END IF;
487
488 IF nvl(p_fixed_asset_rec.asset_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
489
490 IF nvl(p_fixed_asset_rec.asset_location_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
491 OR
492 nvl(p_fixed_asset_rec.asset_quantity, fnd_api.g_miss_num) = fnd_api.g_miss_num
493 OR
494 nvl(p_fixed_asset_rec.book_type_code, fnd_api.g_miss_char) = fnd_api.g_miss_char
495 THEN
496
497 derive_fa_missing_values(
498 p_instance_rec => p_instance_rec,
499 p_fixed_asset_rec => p_fixed_asset_rec,
500 x_fa_location_id => l_fa_location_id,
501 x_fa_quantity => l_fa_quantity,
502 x_fa_book_type_code => l_fa_book_type_code,
503 x_return_status => l_return_status);
504
505 IF l_return_status <> fnd_api.g_ret_sts_success THEN
506 RAISE fnd_api.g_exc_error;
507 END IF;
508 END IF;
509 END IF;
510
511 csi_transactions_pvt.create_transaction(
512 p_api_version => 1.0,
513 p_commit => fnd_api.g_false,
514 p_init_msg_list => fnd_api.g_true,
515 p_validation_level => fnd_api.g_valid_level_full,
516 p_success_if_exists_flag => 'Y',
517 p_transaction_rec => px_csi_txn_rec,
518 x_return_status => l_return_status,
519 x_msg_count => l_msg_count,
520 x_msg_data => l_msg_data);
521
522 IF l_return_status <> fnd_api.g_ret_sts_success THEN
523 RAISE fnd_api.g_exc_error;
524 END IF;
525
526 IF p_instance_serial_tbl.COUNT > 0 THEN
527 FOR srl_ind IN p_instance_serial_tbl.FIRST .. p_instance_serial_tbl.LAST
528 LOOP
529
530 g_inst_ind := g_inst_ind + 1;
531
532 l_csi_txn_tbl(g_inst_ind) := px_csi_txn_rec;
533
534 l_instance_tbl(g_inst_ind) := p_instance_rec;
535
536 l_instance_tbl(g_inst_ind).quantity := 1;
537 --l_instance_tbl(g_inst_ind).mfg_serial_number_flag := 'Y';
538
539
540 -- override with serial attributes
541 l_instance_tbl(g_inst_ind).instance_number :=
542 nvl(p_instance_serial_tbl(srl_ind).instance_number, l_miss_char);
543 l_instance_tbl(g_inst_ind).serial_number :=
544 nvl(p_instance_serial_tbl(srl_ind).serial_number, l_miss_char);
545 l_instance_tbl(g_inst_ind).lot_number :=
546 nvl(p_instance_serial_tbl(srl_ind).lot_number, p_instance_rec.lot_number);
547 l_instance_tbl(g_inst_ind).external_reference :=
548 nvl(p_instance_serial_tbl(srl_ind).external_reference, p_instance_rec.external_reference);
549 l_instance_tbl(g_inst_ind).instance_usage_code :=
550 nvl(p_instance_serial_tbl(srl_ind).instance_usage_code, p_instance_rec.instance_usage_code);
551 l_instance_tbl(g_inst_ind).operational_status_code :=
552 nvl(p_instance_serial_tbl(srl_ind).operational_status_code,
553 p_instance_rec.operational_status_code);
554 l_instance_tbl(g_inst_ind).instance_description :=
555 nvl(p_instance_serial_tbl(srl_ind).instance_description, p_instance_rec.instance_description);
556
557 -- override with eam attributes
558 l_instance_tbl(g_inst_ind).asset_criticality_code :=
559 nvl(p_eam_rec.asset_criticality_code, l_miss_char);
560 l_instance_tbl(g_inst_ind).category_id :=
561 nvl(p_eam_rec.category_id, l_miss_num);
562
563
564 IF p_party_tbl.COUNT > 0 THEN
565 FOR pty_ind IN p_party_tbl.FIRST .. p_party_tbl.LAST
566 LOOP
567
568 g_pty_ind := g_pty_ind + 1;
569 l_party_tbl(g_pty_ind) := p_party_tbl(pty_ind);
570 l_party_tbl(g_pty_ind).parent_tbl_index := g_inst_ind;
571
572
573 IF p_party_account_tbl.COUNT > 0 THEN
574 FOR pa_ind IN p_party_account_tbl.FIRST .. p_party_account_tbl.LAST
575 LOOP
576 g_pa_ind := g_pa_ind + 1;
577 l_account_tbl(g_pa_ind) := p_party_account_tbl(pa_ind);
578 l_account_tbl(g_pa_ind).parent_tbl_index := g_pty_ind;
579 END LOOP;
580 END IF;
581
582 END LOOP;
583 END IF;
584
585 IF nvl(p_fixed_asset_rec.asset_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
586 g_ia_ind := g_ia_ind + 1;
587 l_instance_asset_tbl(g_ia_ind).parent_tbl_index := g_inst_ind;
588 l_instance_asset_tbl(g_ia_ind).fa_asset_id := p_fixed_asset_rec.asset_id;
589 l_instance_asset_tbl(g_ia_ind).fa_book_type_code := l_fa_book_type_code;
590 l_instance_asset_tbl(g_ia_ind).fa_location_id := l_fa_location_id;
591 l_instance_asset_tbl(g_ia_ind).asset_quantity := 1; -- for serialized
592 l_instance_asset_tbl(g_ia_ind).update_status := 'IN_SERVICE';
593 l_instance_asset_tbl(g_ia_ind).fa_sync_flag := p_fixed_asset_rec.fa_sync_flag;
594 l_instance_asset_tbl(g_ia_ind).fa_sync_validation_reqd :=
595 nvl(p_fixed_asset_rec.fa_sync_validation_reqd, fnd_api.g_false);
596
597 validate_inst_asset(
598 px_inst_asset_rec => l_instance_asset_tbl(g_ia_ind),
599 x_return_status => l_return_status);
600
601 IF l_return_status <> fnd_api.g_ret_sts_success THEN
602 RAISE fnd_api.g_exc_error;
603 END IF;
604
605 END IF;
606
607 END LOOP;
608 ELSE
609
610 g_inst_ind := g_inst_ind + 1;
611
612 l_csi_txn_tbl(g_inst_ind) := px_csi_txn_rec;
613
614 l_instance_tbl(g_inst_ind) := p_instance_rec;
615
616 -- override with eam attributes
617 l_instance_tbl(g_inst_ind).asset_criticality_code :=
618 nvl(p_eam_rec.asset_criticality_code, l_miss_char);
619 l_instance_tbl(g_inst_ind).category_id :=
620 nvl(p_eam_rec.category_id, l_miss_num);
621
622 IF p_party_tbl.COUNT > 0 THEN
623 FOR pty_ind IN p_party_tbl.FIRST .. p_party_tbl.LAST
624 LOOP
625
626 g_pty_ind := g_pty_ind + 1;
627 l_party_tbl(g_pty_ind) := p_party_tbl(pty_ind);
628 l_party_tbl(g_pty_ind).parent_tbl_index := g_inst_ind;
629
630 IF p_party_account_tbl.COUNT > 0 THEN
631 FOR pa_ind IN p_party_account_tbl.FIRST .. p_party_account_tbl.LAST
632 LOOP
633 g_pa_ind := g_pa_ind + 1;
634 l_account_tbl(g_pa_ind) := p_party_account_tbl(pa_ind);
635 l_account_tbl(g_pa_ind).parent_tbl_index := g_pty_ind;
636 END LOOP;
637 END IF;
638
639 END LOOP;
640 END IF;
641
642 IF nvl(p_fixed_asset_rec.asset_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
643 g_ia_ind := g_ia_ind + 1;
644 l_instance_asset_tbl(g_ia_ind).parent_tbl_index := g_inst_ind;
645 l_instance_asset_tbl(g_ia_ind).fa_asset_id := p_fixed_asset_rec.asset_id;
646 l_instance_asset_tbl(g_ia_ind).fa_book_type_code := l_fa_book_type_code;
647 l_instance_asset_tbl(g_ia_ind).fa_location_id := l_fa_location_id;
648 l_instance_asset_tbl(g_ia_ind).asset_quantity := p_instance_rec.quantity;
649 l_instance_asset_tbl(g_ia_ind).update_status := 'IN_SERVICE';
650 l_instance_asset_tbl(g_ia_ind).fa_sync_flag := p_fixed_asset_rec.fa_sync_flag;
651 l_instance_asset_tbl(g_ia_ind).fa_sync_validation_reqd :=
652 nvl(p_fixed_asset_rec.fa_sync_validation_reqd, fnd_api.g_false);
653
654 validate_inst_asset(
655 px_inst_asset_rec => l_instance_asset_tbl(g_ia_ind),
656 x_return_status => l_return_status);
657
658 IF l_return_status <> fnd_api.g_ret_sts_success THEN
659 RAISE fnd_api.g_exc_error;
660 END IF;
661
662 END IF;
663
664 END IF;
665
666 debug('instance_tbl.count : '||l_instance_tbl.count);
667 debug('instance_asset_tbl.count : '||l_instance_asset_tbl.count);
668
669 csi_item_instance_grp.create_item_instance (
670 p_api_version => 1.0,
671 p_commit => fnd_api.g_false,
672 p_init_msg_list => fnd_api.g_true,
673 p_validation_level => fnd_api.g_valid_level_full,
674 p_instance_tbl => l_instance_tbl,
675 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
676 p_party_tbl => l_party_tbl,
677 p_account_tbl => l_account_tbl,
678 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
679 p_org_assignments_tbl => l_org_assignments_tbl,
680 p_asset_assignment_tbl => l_instance_asset_tbl,
681 p_txn_tbl => l_csi_txn_tbl,
682 p_call_from_bom_expl => 'N',
683 p_grp_error_tbl => l_grp_error_tbl,
684 x_return_status => l_return_status,
685 x_msg_count => l_msg_count,
686 x_msg_data => l_msg_data);
687
688 IF l_return_status <> fnd_api.g_ret_sts_success THEN
689 --bug 9227016
690 RAISE l_item_inst_grp_excep;
691 --RAISE fnd_api.g_exc_error;
692 END IF;
693
694 IF l_grp_error_tbl.COUNT > 0 THEN
695 -- errors should be passes out as error.
696 FOR err_ind IN l_grp_error_tbl.FIRST ..l_grp_error_tbl.LAST
697 LOOP
698 IF l_grp_error_tbl(err_ind).process_status = 'E' THEN
699 --bug 9227016
700 --l_error_message := l_grp_error_tbl(err_ind).error_message;
701 --RAISE fnd_api.g_exc_error;
702 RAISE l_item_inst_grp_excep;
703 END IF;
704 END LOOP;
705
706 END IF;
707
708 IF l_instance_tbl.COUNT > 0 THEN
709 FOR inst_ind IN l_instance_tbl.FIRST .. l_instance_tbl.LAST
710 LOOP
711
712 IF l_eam_item THEN
713 -- to be uncommented later
714 eam_maint_attributes_pub.create_maint_attributes(
715 p_api_version => 1.0,
716 p_init_msg_list => fnd_api.g_true,
717 p_commit => fnd_api.g_false,
718 p_validation_level => fnd_api.g_valid_level_full,
719 p_instance_id => l_instance_tbl(inst_ind).instance_id,
720 p_owning_department_id => p_eam_rec.owning_department_id,
721 p_accounting_class_code => p_eam_rec.wip_accounting_class_code,
722 p_area_id => p_eam_rec.area_id,
723 p_parent_instance_id => p_eam_rec.parent_instance_id,
724 x_return_status => l_return_status,
725 x_msg_count => l_msg_count,
726 x_msg_data => l_msg_data);
727
728 IF l_return_status <> fnd_api.g_ret_sts_success THEN
729 RAISE fnd_api.g_exc_error;
730 END IF;
731 END IF;
732
733 END LOOP;
734 END IF;
735
736 x_instance_tbl := l_instance_tbl;
737 x_instance_asset_tbl := l_instance_asset_tbl;
738
739
740 IF l_instance_asset_tbl.COUNT > 0 THEN
741 FOR l_ind IN l_instance_asset_tbl.FIRST .. l_instance_asset_tbl.LAST
742 LOOP
743 IF l_instance_asset_tbl(l_ind).fa_sync_flag = 'N' THEN
744 l_warning_flag := 'Y';
745 END IF;
746 END LOOP;
747 END IF;
748
749 IF l_warning_flag = 'Y' THEN
750 fnd_message.set_name('CSI', 'CSI_INST_ASSET_SYNC_WARNING');
751 fnd_msg_pub.add;
752 x_return_status := 'W';
753 x_error_message := dump_error_stack;
754 END IF;
755
756 EXCEPTION
757 --bug 9227016 start
758 WHEN l_item_inst_grp_excep THEN
759 rollback to fa_grp_create_instance;
760 IF l_grp_error_tbl.COUNT > 0 THEN
761 -- errors should be passes out as error.
762 FOR err_ind IN l_grp_error_tbl.FIRST ..l_grp_error_tbl.LAST
763 LOOP
764 IF l_grp_error_tbl(err_ind).process_status = 'E' THEN
765 l_error_message := l_error_message || l_grp_error_tbl(err_ind).error_message || ' ';
766 END IF;
767 END LOOP;
768 END IF;
769 x_return_status := fnd_api.g_ret_sts_error;
770 x_error_message := l_error_message;
771 --bug 9227016 end
772 WHEN fnd_api.g_exc_error THEN
773 rollback to fa_grp_create_instance;
774 x_return_status := fnd_api.g_ret_sts_error;
775 x_error_message := dump_error_stack;
776 WHEN others THEN
777 rollback to fa_grp_create_instance;
778 x_return_status := fnd_api.g_ret_sts_unexp_error;
779 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
780 fnd_message.set_token('API_NAME','csi_fa_instance_grp.create_item_instance');
781 fnd_message.set_token('SQL_ERROR',SQLERRM);
782 fnd_msg_pub.add;
783 x_error_message := dump_error_stack;
784 END create_item_instance;
785
786 PROCEDURE copy_item_instance(
787 p_fixed_asset_rec IN fixed_asset_rec,
788 p_instance_rec IN csi_datastructures_pub.instance_rec,
789 p_instance_serial_tbl IN instance_serial_tbl,
790 p_eam_rec IN eam_rec,
791 p_copy_parties IN varchar2,
792 p_copy_accounts IN varchar2,
793 p_copy_contacts IN varchar2,
794 p_copy_org_assignments IN varchar2,
795 p_copy_asset_assignments IN varchar2,
796 p_copy_pricing_attribs IN varchar2,
797 p_copy_ext_attribs IN varchar2,
798 p_copy_inst_children IN varchar2,
799 px_csi_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
800 x_instance_tbl OUT nocopy csi_datastructures_pub.instance_tbl,
801 x_instance_asset_tbl OUT nocopy csi_datastructures_pub.instance_asset_tbl,
802 x_return_status OUT nocopy varchar2,
803 x_error_message OUT nocopy varchar2)
804 IS
805
806 TYPE copy_flags_rec IS RECORD(
807 copy_parties varchar2(1),
808 copy_accounts varchar2(1),
809 copy_contacts varchar2(1),
810 copy_org_assignments varchar2(1),
811 copy_asset_assignments varchar2(1),
812 copy_pricing_attribs varchar2(1),
813 copy_ext_attribs varchar2(1),
814 copy_inst_children varchar2(1));
815
816 l_fa_flow varchar2(1);
817 l_copy_flags_rec copy_flags_rec;
818 l_instance_rec csi_datastructures_pub.instance_rec;
819
820 l_miss_num number := fnd_api.g_miss_num;
821 l_miss_char varchar2(200) := fnd_api.g_miss_char;
822
823 l_instance_tbl csi_datastructures_pub.instance_tbl;
824
825 o_ind binary_integer := 0;
826 o_instance_tbl csi_datastructures_pub.instance_tbl;
827
828 ia_ind binary_integer := 0;
829 l_instance_asset_tbl csi_datastructures_pub.instance_asset_tbl;
830
831 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
832 l_msg_count number;
833 l_msg_data varchar2(4000);
834 l_error_message varchar2(2000);
835
836 l_serial_control_code number;
837 l_eam_item_type number;
838 l_eam_item boolean := FALSE;
839 --Commented for bug 12658776
840 --l_owning_department_id number;
841 --l_wip_accounting_class_code varchar2(200);
842 --l_parent_instance_id number;
843 --l_area_id number;
844
845 PROCEDURE do_copy(
846 p_fa_flow IN varchar2,
847 p_instance_rec IN csi_datastructures_pub.instance_rec,
848 p_copy_flags_rec IN copy_flags_rec,
849 px_csi_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
850 x_instance_tbl OUT nocopy csi_datastructures_pub.instance_tbl,
851 x_return_status OUT nocopy varchar2)
852 IS
853 l_instance_tbl csi_datastructures_pub.instance_tbl;
854 l_internal_party_id number;
855
856 l_instance_rec csi_datastructures_pub.instance_rec;
857 l_party_tbl csi_datastructures_pub.party_tbl;
858 l_party_acct_tbl csi_datastructures_pub.party_account_tbl;
859 l_inst_asset_tbl csi_datastructures_pub.instance_asset_tbl;
860 l_eav_tbl csi_datastructures_pub.extend_attrib_values_tbl;
861 l_pricing_attribs_tbl csi_datastructures_pub.pricing_attribs_tbl;
862 l_org_units_tbl csi_datastructures_pub.organization_units_tbl;
863 l_inst_id_lst csi_datastructures_pub.id_tbl;
864
865 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
866 l_msg_count number;
867 l_msg_data varchar2(4000);
868 BEGIN
869
870 x_return_status := fnd_api.g_ret_sts_success;
871
872 csi_item_instance_pvt.copy_item_instance(
873 p_api_version => 1.0,
874 p_commit => fnd_api.g_false,
875 p_init_msg_list => fnd_api.g_true,
876 p_validation_level => fnd_api.g_valid_level_full,
877 p_source_instance_rec => p_instance_rec,
878 p_copy_ext_attribs => p_copy_flags_rec.copy_ext_attribs,
879 p_copy_org_assignments => p_copy_flags_rec.copy_org_assignments,
880 p_copy_parties => p_copy_flags_rec.copy_parties,
881 p_copy_contacts => p_copy_flags_rec.copy_contacts,
882 p_copy_accounts => p_copy_flags_rec.copy_accounts,
883 p_copy_asset_assignments => p_copy_flags_rec.copy_asset_assignments,
884 p_copy_pricing_attribs => p_copy_flags_rec.copy_pricing_attribs,
885 p_copy_inst_children => p_copy_flags_rec.copy_inst_children,
886 p_call_from_split => fnd_api.g_false,
887 p_txn_rec => px_csi_txn_rec,
888 x_new_instance_tbl => l_instance_tbl,
889 x_return_status => l_return_status,
890 x_msg_count => l_msg_count,
891 x_msg_data => l_msg_data);
892
893 IF l_return_status <> fnd_api.g_ret_sts_success THEN
894 RAISE fnd_api.g_exc_error;
895 END IF;
896
897 IF nvl(p_fa_flow, 'N') = 'Y' THEN
898
899 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
900 csi_gen_utility_pvt.populate_install_param_rec;
901 END IF;
902
903 l_internal_party_id := csi_datastructures_pub.g_install_param_rec.internal_party_id;
904
905 l_party_tbl.delete;
906
907 SELECT instance_party_id,
908 object_version_number,
909 party_id
910 INTO l_party_tbl(1).instance_party_id,
911 l_party_tbl(1).object_version_number,
912 l_party_tbl(1).party_id
913 FROM csi_i_parties
914 WHERE instance_id = l_instance_tbl(1).instance_id -- for copy children case need to change
915 AND relationship_type_code = 'OWNER';
916
917 IF l_party_tbl(1).party_id <> l_internal_party_id THEN
918
919 l_party_tbl(1).instance_id := l_instance_tbl(1).instance_id;
920 l_party_tbl(1).party_source_table := 'HZ_PARTIES';
921 l_party_tbl(1).party_id := l_internal_party_id;
922 l_party_tbl(1).relationship_type_code := 'OWNER';
923 l_party_tbl(1).contact_flag := 'N';
924
925 -- change the owner to internal
926 csi_item_instance_pub.update_item_instance(
927 p_api_version => 1.0,
928 p_commit => fnd_api.g_false,
929 p_init_msg_list => fnd_api.g_true,
930 p_validation_level => fnd_api.g_valid_level_full,
931 p_instance_rec => l_instance_rec,
932 p_ext_attrib_values_tbl => l_eav_tbl,
933 p_party_tbl => l_party_tbl,
934 p_account_tbl => l_party_acct_tbl,
935 p_pricing_attrib_tbl => l_pricing_attribs_tbl,
936 p_org_assignments_tbl => l_org_units_tbl,
937 p_txn_rec => px_csi_txn_rec,
938 p_asset_assignment_tbl => l_inst_asset_tbl,
939 x_instance_id_lst => l_inst_id_lst,
940 x_return_status => l_return_status,
941 x_msg_count => l_msg_count,
942 x_msg_data => l_msg_data);
943
944 IF l_return_status <> fnd_api.g_ret_sts_success THEN
945 RAISE fnd_api.g_exc_error;
946 END IF;
947
948 END IF;
949
950 END IF;
951
952 x_instance_tbl := l_instance_tbl;
953
954 EXCEPTION
955 WHEN fnd_api.g_exc_error THEN
956 x_return_status := fnd_api.g_ret_sts_error;
957 END do_copy;
958
959 BEGIN
960
961 x_return_status := fnd_api.g_ret_sts_success;
962
963 savepoint fa_grp_copy_instance;
964
965 csi_item_instance_vld_pvt.check_reqd_param_num (
966 p_number => p_instance_rec.instance_id,
967 p_param_name => 'p_instance_rec.instance_id',
968 p_api_name => 'csi_fa_instance_rec.copy_item_instance');
969
970 csi_transactions_pvt.create_transaction(
971 p_api_version => 1.0,
972 p_commit => fnd_api.g_false,
973 p_init_msg_list => fnd_api.g_true,
974 p_validation_level => fnd_api.g_valid_level_full,
975 p_success_if_exists_flag => 'Y',
976 p_transaction_rec => px_csi_txn_rec,
977 x_return_status => l_return_status,
978 x_msg_count => l_msg_count,
979 x_msg_data => l_msg_data);
980
981 IF l_return_status <> fnd_api.g_ret_sts_success THEN
982 RAISE fnd_api.g_exc_error;
983 END IF;
984
985 IF nvl(p_fixed_asset_rec.asset_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
986 l_fa_flow := 'Y';
987 l_copy_flags_rec.copy_parties := fnd_api.g_false;
988 l_copy_flags_rec.copy_accounts := fnd_api.g_false;
989 l_copy_flags_rec.copy_contacts := fnd_api.g_false;
990 ELSE
991 l_fa_flow := 'N';
992 l_copy_flags_rec.copy_parties := p_copy_parties;
993 l_copy_flags_rec.copy_accounts := p_copy_accounts;
994 l_copy_flags_rec.copy_contacts := p_copy_contacts;
995 END IF;
996
997 l_copy_flags_rec.copy_org_assignments := p_copy_org_assignments;
998 l_copy_flags_rec.copy_pricing_attribs := p_copy_pricing_attribs;
999 l_copy_flags_rec.copy_ext_attribs := p_copy_ext_attribs;
1000 l_copy_flags_rec.copy_asset_assignments := p_copy_asset_assignments;
1001 l_copy_flags_rec.copy_inst_children := p_copy_inst_children;
1002
1003 -- derive eam_item_type
1004 IF nvl(p_instance_rec.inventory_item_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
1005 AND
1006 nvl(p_instance_rec.vld_organization_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
1007 THEN
1008
1009 SELECT eam_item_type,
1010 serial_number_control_code
1011 INTO l_eam_item_type,
1012 l_serial_control_code
1013 FROM mtl_system_items
1014 WHERE inventory_item_id = p_instance_rec.inventory_item_id
1015 AND organization_id = p_instance_rec.vld_organization_id;
1016
1017 IF l_eam_item_type in (1, 3) AND l_serial_control_code <> 1 THEN
1018 l_eam_item := TRUE;
1019 END IF;
1020
1021 END IF;
1022
1023 IF p_instance_serial_tbl.COUNT > 0 THEN
1024
1025 FOR srl_ind IN p_instance_serial_tbl.FIRST .. p_instance_serial_tbl.LAST
1026 LOOP
1027
1028 l_instance_rec := p_instance_rec;
1029
1030 -- override with serial attributes
1031 l_instance_rec.instance_number :=
1032 nvl(p_instance_serial_tbl(srl_ind).instance_number, l_miss_char);
1033 l_instance_rec.serial_number :=
1034 nvl(p_instance_serial_tbl(srl_ind).serial_number, l_miss_char);
1035 l_instance_rec.lot_number :=
1036 nvl(p_instance_serial_tbl(srl_ind).lot_number, l_miss_char);
1037 l_instance_rec.external_reference :=
1038 nvl(p_instance_serial_tbl(srl_ind).external_reference, p_instance_rec.external_reference);
1039 l_instance_rec.instance_usage_code :=
1040 nvl(p_instance_serial_tbl(srl_ind).instance_usage_code, p_instance_rec.instance_usage_code);
1041 l_instance_rec.operational_status_code :=
1042 nvl(p_instance_serial_tbl(srl_ind).operational_status_code,
1043 p_instance_rec.operational_status_code);
1044 l_instance_rec.instance_description :=
1045 nvl(p_instance_serial_tbl(srl_ind).instance_description, p_instance_rec.instance_description);
1046 l_instance_rec.quantity := 1;
1047
1048 -- override with eam attributes
1049 l_instance_rec.asset_criticality_code :=
1050 nvl(p_eam_rec.asset_criticality_code, l_miss_char);
1051 l_instance_rec.category_id :=
1052 nvl(p_eam_rec.category_id, l_miss_num);
1053
1054 do_copy(
1055 p_fa_flow => l_fa_flow,
1056 p_instance_rec => l_instance_rec,
1057 p_copy_flags_rec => l_copy_flags_rec,
1058 px_csi_txn_rec => px_csi_txn_rec,
1059 x_instance_tbl => l_instance_tbl,
1060 x_return_status => l_return_status);
1061
1062 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1063 l_error_message := dump_error_stack;
1064 RAISE fnd_api.g_exc_error;
1065 END IF;
1066
1067 IF l_instance_tbl.COUNT > 0 THEN
1068 FOR inst_ind IN l_instance_tbl.FIRST .. l_instance_tbl.LAST
1069 LOOP
1070 o_ind := o_ind + 1;
1071 o_instance_tbl(o_ind) := l_instance_tbl(inst_ind);
1072 END LOOP;
1073 END IF;
1074
1075 END LOOP;
1076
1077 ELSE
1078 l_instance_rec := p_instance_rec;
1079 -- override with eam attributes
1080 l_instance_rec.asset_criticality_code :=
1081 nvl(p_eam_rec.asset_criticality_code, l_miss_char);
1082 l_instance_rec.category_id :=
1083 nvl(p_eam_rec.category_id, l_miss_num);
1084
1085 do_copy(
1086 p_fa_flow => l_fa_flow,
1087 p_instance_rec => l_instance_rec,
1088 p_copy_flags_rec => l_copy_flags_rec,
1089 px_csi_txn_rec => px_csi_txn_rec,
1090 x_instance_tbl => l_instance_tbl,
1091 x_return_status => l_return_status);
1092
1093 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1094 l_error_message := dump_error_stack;
1095 RAISE fnd_api.g_exc_error;
1096 END IF;
1097
1098 IF l_instance_tbl.COUNT > 0 THEN
1099 FOR inst_ind IN l_instance_tbl.FIRST .. l_instance_tbl.LAST
1100 LOOP
1101 o_ind := o_ind + 1;
1102 o_instance_tbl(o_ind) := l_instance_tbl(inst_ind);
1103 END LOOP;
1104 END IF;
1105
1106 END IF;
1107
1108 IF o_instance_tbl.COUNT > 0 THEN
1109 FOR inst_ind IN o_instance_tbl.FIRST .. o_instance_tbl.LAST
1110 LOOP
1111
1112 IF nvl(p_fixed_asset_rec.asset_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
1113 ia_ind := ia_ind + 1;
1114 l_instance_asset_tbl(ia_ind).instance_id := o_instance_tbl(inst_ind).instance_id;
1115 l_instance_asset_tbl(ia_ind).fa_asset_id := p_fixed_asset_rec.asset_id;
1116 l_instance_asset_tbl(ia_ind).fa_book_type_code := p_fixed_asset_rec.book_type_code;
1117 l_instance_asset_tbl(ia_ind).fa_location_id := p_fixed_asset_rec.asset_location_id;
1118 l_instance_asset_tbl(ia_ind).asset_quantity := o_instance_tbl(inst_ind).quantity;
1119 l_instance_asset_tbl(ia_ind).update_status := 'IN_SERVICE';
1120 l_instance_asset_tbl(ia_ind).fa_sync_flag := p_fixed_asset_rec.fa_sync_flag;
1121 l_instance_asset_tbl(ia_ind).fa_sync_validation_reqd :=
1122 p_fixed_asset_rec.fa_sync_validation_reqd;
1123 END IF;
1124
1125 IF l_eam_item THEN
1126 /* Commented the code for Bug 12658776
1127 --Need to flip the EAM attributes.
1128 IF p_eam_rec.owning_department_id = FND_API.G_MISS_NUM THEN
1129 l_owning_department_id := NULL;
1130 ELSIF p_eam_rec.owning_department_id IS NULL THEN
1131 l_owning_department_id := FND_API.G_MISS_NUM;
1132 END IF;
1133
1134 IF p_eam_rec.wip_accounting_class_code = FND_API.G_MISS_CHAR THEN
1135 l_wip_accounting_class_code := NULL;
1136 ELSIF p_eam_rec.owning_department_id IS NULL THEN
1137 l_wip_accounting_class_code := FND_API.G_MISS_CHAR;
1138 END IF;
1139
1140 IF p_eam_rec.area_id = FND_API.G_MISS_NUM THEN
1141 l_area_id := NULL;
1142 ELSIF p_eam_rec.owning_department_id IS NULL THEN
1143 l_area_id := FND_API.G_MISS_NUM;
1144 END IF;
1145
1146 IF p_eam_rec.parent_instance_id = FND_API.G_MISS_NUM THEN
1147 l_parent_instance_id := NULL;
1148 ELSIF p_eam_rec.owning_department_id IS NULL THEN
1149 l_parent_instance_id := FND_API.G_MISS_NUM;
1150 END IF;
1151 */
1152
1153 eam_maint_attributes_pub.create_maint_attributes(
1154 p_api_version => 1.0,
1155 p_init_msg_list => fnd_api.g_true,
1156 p_commit => fnd_api.g_false,
1157 p_validation_level => fnd_api.g_valid_level_full,
1158 p_instance_id => l_instance_tbl(inst_ind).instance_id,
1159 p_owning_department_id => p_eam_rec.owning_department_id, --Changed for bug 12658776
1160 p_accounting_class_code => p_eam_rec.wip_accounting_class_code, --Changed for bug 12658776
1161 p_area_id => p_eam_rec.area_id, --Changed for bug 12658776
1162 p_parent_instance_id => p_eam_rec.parent_instance_id, --Changed for bug 12658776
1163 x_return_status => l_return_status,
1164 x_msg_count => l_msg_count,
1165 x_msg_data => l_msg_data);
1166
1167 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1168 RAISE fnd_api.g_exc_error;
1169 END IF;
1170 END IF;
1171
1172 END LOOP;
1173 END IF;
1174
1175 IF l_instance_asset_tbl.COUNT > 0 THEN
1176
1177 create_instance_assets(
1178 px_instance_asset_tbl => l_instance_asset_tbl,
1179 px_csi_txn_rec => px_csi_txn_rec,
1180 x_return_status => l_return_status,
1181 x_error_message => l_error_message);
1182
1183 IF l_return_status IN (fnd_api.g_ret_sts_success, 'W') THEN
1184 x_return_status := l_return_status;
1185 x_error_message := l_error_message;
1186 ELSE
1187 RAISE fnd_api.g_exc_error;
1188 END IF;
1189
1190 END IF;
1191
1192 x_instance_tbl := o_instance_tbl;
1193 x_instance_asset_tbl := l_instance_asset_tbl;
1194
1195 EXCEPTION
1196 WHEN fnd_api.g_exc_error THEN
1197 rollback to fa_grp_copy_instance;
1198 x_return_status := fnd_api.g_ret_sts_error;
1199 x_error_message := dump_error_stack;
1200 WHEN others THEN
1201 rollback to fa_grp_copy_instance;
1202 x_return_status := fnd_api.g_ret_sts_unexp_error;
1203 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
1204 fnd_message.set_token('API_NAME','csi_fa_instance_grp.copy_item_instance');
1205 fnd_message.set_token('SQL_ERROR',SQLERRM);
1206 fnd_msg_pub.add;
1207 x_error_message := dump_error_stack;
1208 END copy_item_instance;
1209
1210 --
1211 PROCEDURE associate_item_instance(
1212 p_fixed_asset_rec IN fixed_asset_rec,
1213 p_instance_tbl IN csi_datastructures_pub.instance_tbl,
1214 px_csi_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
1215 x_instance_asset_tbl OUT nocopy csi_datastructures_pub.instance_asset_tbl,
1216 x_return_status OUT nocopy varchar2,
1217 x_error_message OUT nocopy varchar2)
1218 IS
1219
1220 l_fa_location_id number;
1221 l_fa_quantity number;
1222 l_fa_book_type_code varchar2(30);
1223
1224 l_instance_asset_tbl csi_datastructures_pub.instance_asset_tbl;
1225 l_lookup_tbl csi_asset_pvt.lookup_tbl;
1226 l_asset_count_rec csi_asset_pvt.asset_count_rec;
1227 l_asset_id_tbl csi_asset_pvt.asset_id_tbl;
1228 l_asset_loc_tbl csi_asset_pvt.asset_loc_tbl;
1229
1230 g_ia_ind binary_integer := 0;
1231
1232 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1233 l_msg_count number;
1234 l_msg_data varchar2(2000);
1235
1236 l_error_message varchar2(2000);
1237
1238 BEGIN
1239 x_return_status := fnd_api.g_ret_sts_success;
1240
1241 debug('associate_item_isntance');
1242
1243 IF p_instance_tbl.COUNT > 0 THEN
1244
1245 FOR inst_ind IN p_instance_tbl.FIRST .. p_instance_tbl.LAST
1246 LOOP
1247
1248 g_ia_ind := g_ia_ind + 1;
1249 l_instance_asset_tbl(g_ia_ind).instance_id := p_instance_tbl(inst_ind).instance_id;
1250 l_instance_asset_tbl(g_ia_ind).fa_asset_id := p_fixed_asset_rec.asset_id;
1251 l_instance_asset_tbl(g_ia_ind).fa_book_type_code := p_fixed_asset_rec.book_type_code;
1252 l_instance_asset_tbl(g_ia_ind).fa_location_id := p_fixed_asset_rec.asset_location_id;
1253 l_instance_asset_tbl(g_ia_ind).asset_quantity := p_instance_tbl(inst_ind).quantity;
1254 l_instance_asset_tbl(g_ia_ind).update_status := 'IN_SERVICE';
1255
1256 IF nvl(p_fixed_asset_rec.asset_location_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
1257 OR
1258 nvl(p_fixed_asset_rec.asset_quantity, fnd_api.g_miss_num) = fnd_api.g_miss_num
1259 OR
1260 nvl(p_fixed_asset_rec.book_type_code, fnd_api.g_miss_char) = fnd_api.g_miss_char
1261 THEN
1262
1263 derive_fa_missing_values(
1264 p_instance_rec => p_instance_tbl(inst_ind),
1265 p_fixed_asset_rec => p_fixed_asset_rec,
1266 x_fa_location_id => l_fa_location_id,
1267 x_fa_quantity => l_fa_quantity,
1268 x_fa_book_type_code => l_fa_book_type_code,
1269 x_return_status => l_return_status);
1270
1271 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1272 RAISE fnd_api.g_exc_error;
1273 END IF;
1274
1275 debug('location_id :'||l_fa_location_id);
1276
1277 IF nvl(p_fixed_asset_rec.asset_location_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1278 l_instance_asset_tbl(g_ia_ind).fa_location_id := l_fa_location_id;
1279 END IF;
1280
1281 IF nvl(p_fixed_asset_rec.asset_quantity, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1282 l_instance_asset_tbl(g_ia_ind).asset_quantity := l_fa_quantity;
1283 END IF;
1284
1285 l_instance_asset_tbl(g_ia_ind).fa_book_type_code := l_fa_book_type_code;
1286 l_instance_asset_tbl(g_ia_ind).fa_sync_flag := p_fixed_asset_rec.fa_sync_flag;
1287 l_instance_asset_tbl(g_ia_ind).fa_sync_validation_reqd :=
1288 nvl(p_fixed_asset_rec.fa_sync_validation_reqd, fnd_api.g_false);
1289
1290 END IF;
1291
1292 END LOOP;
1293 END IF;
1294
1295 IF l_instance_asset_tbl.COUNT > 0 THEN
1296
1297 csi_transactions_pvt.create_transaction(
1298 p_api_version => 1.0,
1299 p_commit => fnd_api.g_false,
1300 p_init_msg_list => fnd_api.g_true,
1301 p_validation_level => fnd_api.g_valid_level_full,
1302 p_success_if_exists_flag => 'Y',
1303 p_transaction_rec => px_csi_txn_rec,
1304 x_return_status => l_return_status,
1305 x_msg_count => l_msg_count,
1306 x_msg_data => l_msg_data);
1307
1308 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1309 RAISE fnd_api.g_exc_error;
1310 END IF;
1311
1312 create_instance_assets(
1313 px_instance_asset_tbl => l_instance_asset_tbl,
1314 px_csi_txn_rec => px_csi_txn_rec,
1315 x_return_status => l_return_status,
1316 x_error_message => l_error_message);
1317
1318 IF l_return_status IN (fnd_api.g_ret_sts_success, 'W') THEN
1319 x_return_status := l_return_status;
1320 x_error_message := l_error_message;
1321 ELSE
1322 RAISE fnd_api.g_exc_error;
1323 END IF;
1324
1325 END IF;
1326
1327 EXCEPTION
1328 WHEN fnd_api.g_exc_error THEN
1329 x_return_status := fnd_api.g_ret_sts_error;
1330 x_error_message := dump_error_stack;
1331 WHEN others THEN
1332 x_return_status := fnd_api.g_ret_sts_unexp_error;
1333 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
1334 fnd_message.set_token('API_NAME','csi_fa_instance_grp.associate_item_instance');
1335 fnd_message.set_token('SQL_ERROR',SQLERRM);
1336 fnd_msg_pub.add;
1337 x_error_message := dump_error_stack;
1338 END associate_item_instance;
1339
1340 PROCEDURE update_asset_association(
1341 p_instance_asset_tbl IN csi_datastructures_pub.instance_asset_tbl,
1342 px_csi_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
1343 x_return_status OUT nocopy varchar2,
1344 x_error_message OUT nocopy varchar2)
1345 IS
1346
1347 l_instance_asset_rec csi_datastructures_pub.instance_asset_rec;
1348 l_lookup_tbl csi_asset_pvt.lookup_tbl;
1349 l_asset_count_rec csi_asset_pvt.asset_count_rec;
1350 l_asset_id_tbl csi_asset_pvt.asset_id_tbl;
1351 l_asset_loc_tbl csi_asset_pvt.asset_loc_tbl;
1352 l_warning_flag varchar2(1) := 'N';
1353
1354 l_return_status varchar2(2000) := fnd_api.g_ret_sts_success;
1355 l_msg_count number;
1356 l_msg_data varchar2(2000);
1357
1358 BEGIN
1359
1360 x_return_status := fnd_api.g_ret_sts_success;
1361 IF p_instance_asset_tbl.count > 0 THEN
1362
1363 csi_transactions_pvt.create_transaction(
1364 p_api_version => 1.0,
1365 p_commit => fnd_api.g_false,
1366 p_init_msg_list => fnd_api.g_true,
1367 p_validation_level => fnd_api.g_valid_level_full,
1368 p_success_if_exists_flag => 'Y',
1369 p_transaction_rec => px_csi_txn_rec,
1370 x_return_status => l_return_status,
1371 x_msg_count => l_msg_count,
1372 x_msg_data => l_msg_data);
1373
1374 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1375 RAISE fnd_api.g_exc_error;
1376 END IF;
1377
1378 FOR l_ind IN p_instance_asset_tbl.FIRST .. p_instance_asset_tbl.LAST
1379 LOOP
1380
1381 l_instance_asset_rec := p_instance_asset_tbl(l_ind);
1382
1383 csi_asset_pvt.update_instance_asset (
1384 p_api_version => 1.0,
1385 p_commit => fnd_api.g_false,
1386 p_init_msg_list => fnd_api.g_true,
1387 p_validation_level => fnd_api.g_valid_level_full,
1388 p_instance_asset_rec => l_instance_asset_rec,
1389 p_txn_rec => px_csi_txn_rec,
1390 x_return_status => l_return_status,
1391 x_msg_count => l_msg_count,
1392 x_msg_data => l_msg_data,
1393 p_lookup_tbl => l_lookup_tbl,
1394 p_asset_count_rec => l_asset_count_rec,
1395 p_asset_id_tbl => l_asset_id_tbl,
1396 p_asset_loc_tbl => l_asset_loc_tbl);
1397
1398 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1399 RAISE fnd_api.g_exc_error;
1400 END IF;
1401
1402 IF l_instance_asset_rec.fa_sync_flag = 'N' THEN
1403 l_warning_flag := 'Y';
1404 END IF;
1405
1406 END LOOP;
1407
1408 END IF;
1409
1410 IF l_warning_flag = 'Y' THEN
1411 fnd_message.set_name('CSI', 'CSI_INST_ASSET_SYNC_WARNING');
1412 fnd_msg_pub.add;
1413 x_return_status := 'W';
1414 x_error_message := dump_error_stack;
1415 END IF;
1416
1417 EXCEPTION
1418 WHEN fnd_api.g_exc_error THEN
1419 x_return_status := fnd_api.g_ret_sts_error;
1420 x_error_message := dump_error_stack;
1421 WHEN others THEN
1422 x_return_status := fnd_api.g_ret_sts_unexp_error;
1423 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
1424 fnd_message.set_token('API_NAME','csi_fa_instance_grp.update_asset_association');
1425 fnd_message.set_token('SQL_ERROR',SQLERRM);
1426 fnd_msg_pub.add;
1427 x_error_message := dump_error_stack;
1428 END update_asset_association;
1429
1430 PROCEDURE create_instance_assets(
1431 px_instance_asset_tbl IN OUT nocopy csi_datastructures_pub.instance_asset_tbl,
1432 px_csi_txn_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
1433 x_return_status OUT nocopy varchar2,
1434 x_error_message OUT nocopy varchar2)
1435 IS
1436
1437 l_fixed_asset_rec fixed_asset_rec;
1438 l_instance_rec csi_datastructures_pub.instance_rec;
1439 l_lookup_tbl csi_asset_pvt.lookup_tbl;
1440 l_asset_count_rec csi_asset_pvt.asset_count_rec;
1441 l_asset_id_tbl csi_asset_pvt.asset_id_tbl;
1442 l_asset_loc_tbl csi_asset_pvt.asset_loc_tbl;
1443
1444 l_asset_quantity number;
1445 l_asset_location_id number;
1446
1447 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1448 l_msg_count number;
1449 l_msg_data varchar2(2000);
1450
1451 l_warning_flag varchar2(1) := 'N';
1452
1453
1454 BEGIN
1455
1456 x_return_status := fnd_api.g_ret_sts_success;
1457
1458 debug('create_instance_assets');
1459
1460 savepoint create_instance_assets;
1461
1462 IF px_instance_asset_tbl.COUNT > 0 THEN
1463
1464 FOR ia_ind IN px_instance_asset_tbl.FIRST .. px_instance_asset_tbl.LAST
1465 LOOP
1466
1467 IF nvl(px_instance_asset_tbl(ia_ind).fa_location_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
1468 OR
1469 nvl(px_instance_asset_tbl(ia_ind).asset_quantity, fnd_api.g_miss_num) = fnd_api.g_miss_num
1470 THEN
1471
1472 l_instance_rec.instance_id := px_instance_asset_tbl(ia_ind).instance_id;
1473 l_fixed_asset_rec.asset_id := px_instance_asset_tbl(ia_ind).fa_asset_id;
1474 l_fixed_asset_rec.asset_location_id := px_instance_asset_tbl(ia_ind).fa_location_id;
1475 l_fixed_asset_rec.asset_quantity := px_instance_asset_tbl(ia_ind).asset_quantity;
1476
1477 derive_fa_missing_values(
1478 p_instance_rec => l_instance_rec,
1479 p_fixed_asset_rec => l_fixed_asset_rec,
1480 x_fa_location_id => l_asset_location_id,
1481 x_fa_quantity => l_asset_quantity,
1482 x_fa_book_type_code => px_instance_asset_tbl(ia_ind).fa_book_type_code,
1483 x_return_status => l_return_status);
1484
1485 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1486 RAISE fnd_api.g_exc_error;
1487 END IF;
1488
1489 IF nvl(px_instance_asset_tbl(ia_ind).fa_location_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
1490 THEN
1491 px_instance_asset_tbl(ia_ind).fa_location_id := l_asset_location_id;
1492 END IF;
1493
1494 IF nvl(px_instance_asset_tbl(ia_ind).asset_quantity, fnd_api.g_miss_num) = fnd_api.g_miss_num
1495 THEN
1496 px_instance_asset_tbl(ia_ind).asset_quantity := l_asset_quantity;
1497 END IF;
1498
1499 END IF;
1500
1501 validate_inst_asset(
1502 px_inst_asset_rec => px_instance_asset_tbl(ia_ind),
1503 x_return_status => l_return_status);
1504
1505 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1506 RAISE fnd_api.g_exc_error;
1507 END IF;
1508
1509 IF nvl(px_instance_asset_tbl(ia_ind).instance_asset_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1510
1511 csi_asset_pvt.create_instance_asset(
1512 p_api_version => 1.0,
1513 p_commit => fnd_api.g_false,
1514 p_init_msg_list => fnd_api.g_true,
1515 p_validation_level => fnd_api.g_valid_level_full,
1516 p_instance_asset_rec => px_instance_asset_tbl(ia_ind),
1517 p_txn_rec => px_csi_txn_rec,
1518 p_lookup_tbl => l_lookup_tbl,
1519 p_asset_count_rec => l_asset_count_rec,
1520 p_asset_id_tbl => l_asset_id_tbl,
1521 p_asset_loc_tbl => l_asset_loc_tbl,
1522 x_return_status => l_return_status,
1523 x_msg_count => l_msg_count,
1524 x_msg_data => l_msg_data);
1525
1526 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1527 RAISE fnd_api.g_exc_error;
1528 END IF;
1529
1530 ELSE
1531 null;
1532 END IF;
1533
1534 IF px_instance_asset_tbl(ia_ind).fa_sync_flag = 'N' THEN
1535 l_warning_flag := 'Y';
1536 END IF;
1537
1538 END LOOP;
1539
1540 END IF;
1541
1542 IF l_warning_flag = 'Y' THEN
1543 fnd_message.set_name('CSI', 'CSI_INST_ASSET_SYNC_WARNING');
1544 fnd_msg_pub.add;
1545 x_return_status := 'W';
1546 x_error_message := dump_error_stack;
1547 ELSE
1548 x_return_status := fnd_api.g_ret_sts_success;
1549 END IF;
1550
1551 EXCEPTION
1552 WHEN fnd_api.g_exc_error THEN
1553 rollback to create_instance_assets;
1554 x_return_status := fnd_api.g_ret_sts_error;
1555 x_error_message := dump_error_stack;
1556 WHEN others THEN
1557 rollback to create_instance_assets;
1558 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1559 x_error_message := substr(sqlerrm, 1, 2000);
1560 END create_instance_assets;
1561
1562 END csi_fa_instance_grp;